JG Regional Invoice Format Program - draft

Description
Categories: BI Publisher
Application: Regional Localizations
Source: Regional Invoice Format Program (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZARIN_XML
DB package: JG_JGZZARIN_XMLP_PKG
select	t.bill_to_customer_id,    
	t.bill_to_site_use_id,
	t.ship_to_customer_id,
	t.ship_to_site_use_id, 
	t.remit_to_address_id,
	t.receipt_method_id,
	f.territory_short_name country,
                	hcsu.tax_reference,
                	t.customer_trx_id,
                	t.trx_date,
                	t.trx_number,
                	t.comments,
	t.purchase_order	po,
                	to_date(t.global_attribute1, 'yyyy/mm/dd hh24:mi:ss') tax_date,
                	l.line_number,l.description,
                	nvl( (select nvl(product_fisc_classification, product_category)
                                           from zx_lines_det_factors
                                           where trx_number = t.trx_number 
                                           and internal_organization_id = trans.org_id
                                           and trx_id = t.customer_trx_id and trx_line_id = l.customer_trx_line_id),
                                nvl( (select mc.segment1 from mtl_categories mc, mtl_item_categories mic 
                                where mic.inventory_item_id=msi.inventory_item_id 
                                and mic.category_set_id = (select category_set_id from mtl_category_sets where category_set_name = 'STATISTICAL_CODE') 
                                and mic.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',trans.org_id))
                                and mc.category_id=mic.category_id) ,  m.tax_product_category)
                                ) stat_code,
                           	l.uom_code, l.quantity_invoiced,
                	l.unit_selling_price, l.extended_amount,
                	decode(p.tax_rate,0,tax.tax_rate_code,to_char(p.tax_rate)) tax_rate,
                	p.vat_tax_id,
                	p.extended_amount tax,
                	trans.description type,
                	t.invoice_currency_code currency,
                	type.description p_type,
                	l.extended_amount+p.extended_amount total,
                 	t.global_attribute3 bank_acc_num,
                	DECODE(:P_ORDER_BY,
                               'TRX_NUMBER',                    t.TRX_NUMBER, 
                               'CUSTOMER',                      hp.PARTY_NAME,
                               'POSTAL_CODE',                  hl.POSTAL_CODE,
                                                                              t.TRX_NUMBER)   ORDER_BY,
JG_JGZZARIN_XMLP_PKG.s_sformula(:INVOICE_TOTAL,t.invoice_currency_code) S_SAY_FORMULA, 
	&trx_date CF_TRX_DATE, 
	&tax_date CF_TAX_DATE, 
	JG_JGZZARIN_XMLP_PKG.cf_1formula() CF_1,
	JG_JGZZARIN_XMLP_PKG.S_SAY_p S_SAY,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Cust_Name_p CP_Ship_Cust_Name,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_1_p CP_Ship_Add_1,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_2_p CP_Ship_Add_2,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_3_p CP_Ship_Add_3,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_4_p CP_Ship_Add_4,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_City_p CP_Ship_City,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Postal_Code_p CP_Ship_Postal_Code,
	JG_JGZZARIN_XMLP_PKG.CP_Ship_Country_p CP_Ship_Country,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Cust_Name_p CP_Bill_Cust_Name,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_1_p CP_Bill_Add_1,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_2_p CP_Bill_Add_2,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_3_p CP_Bill_Add_3,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_4_p CP_Bill_Add_4,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_City_p CP_Bill_City,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Postal_Code_p CP_Bill_Postal_Code,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Country_p CP_Bill_Country,
	JG_JGZZARIN_XMLP_PKG.CP_Bill_Tax_Reference_p CP_Bill_Tax_Reference,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Cust_p CP_Remit_Cust,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_1_p CP_Remit_Add_1,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_2_p CP_Remit_Add_2,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_3_p CP_Remit_Add_3,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_4_p CP_Remit_Add_4,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_City_p CP_Remit_City,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Country_p CP_Remit_Country,
	JG_JGZZARIN_XMLP_PKG.CP_1_p CP_1,
	JG_JGZZARIN_XMLP_PKG.CP_Remit_Postal_Code_p CP_Remit_Postal_Code
from        ra_customer_trx_lines_all l, ra_customer_trx_lines_all p,
                	ra_cust_trx_line_gl_dist_all g,
                hz_cust_acct_sites_all hcas,
	hz_party_sites hps,
	hz_locations hl,  				   	 
                hz_cust_site_uses_all hcsu,				 
                hz_cust_accounts hca,				 
	hz_parties  hp,
                ra_customer_trx t,
                zx_rates_b tax,	  	 	   	         
                ra_cust_trx_types_all trans,
                ra_terms type,
                ar_memo_lines m,
                mtl_system_items msi,
                fnd_territories_vl f
           where      l.customer_trx_line_id	= p.link_to_cust_trx_line_id
and          l.customer_trx_id 	= t.customer_trx_id
and          p.customer_trx_id 	= t.customer_trx_id
and          g.customer_trx_id 	= t.customer_trx_id
and          g.account_class 	= 'REC'
and          g.latest_rec_flag 	= 'Y'
and          hcsu.site_use_id        	=  t.bill_to_site_use_id
and          hcas.cust_acct_site_id     =  hcsu.cust_acct_site_id
and          hcas.cust_account_id       =  hca.cust_account_id
and          hca.party_id = hp.party_id
and          hcas.party_site_id     = hps.party_site_id
and          hps.location_id   = hl.location_id
and          hl.country               	=  f.territory_code(+)  
and          trans.cust_trx_type_id	= t.cust_trx_type_id
and          t.term_id 		= type.term_id(+)
and          p.vat_tax_id 	= tax.tax_rate_id
and          l.inventory_item_id	= msi.inventory_item_id(+)
and          l.memo_line_id	= m.memo_line_id(+)
and         decode(:P_BATCH_ID,null,1,:P_BATCH_ID) 	= decode(:P_BATCH_ID,null,1,t.batch_id)
and          t.set_of_books_id	=:P_SET_OF_BOOKS_ID
and          t.trx_date between nvl(:P_INVOICE_DATE_LOW,t.trx_date) 
	    and nvl(:P_INVOICE_DATE_HIGH,t.trx_date)
and          t.complete_flag	= 'Y'
&PRINT_TYPE
and          t.cust_trx_type_id=decode( :P_DOCUMENT_TYPE,null,t.cust_trx_type_id,:P_DOCUMENT_TYPE)
and          t.trx_number between 	
	decode(:P_INVOICE_NUMBER_LOW,null,:TRX_NUMBER_LOW,:P_INVOICE_NUMBER_LOW)
                	and decode(:P_INVOICE_NUMBER_HIGH,null,:TRX_NUMBER_HIGH,:P_INVOICE_NUMBER_HIGH)
and          hca.cust_account_id 	= decode(:P_CUST_ID,null,hca.cust_account_id,:P_CUST_ID)  
and	     l.org_id=trans.org_id 
and 	      p.org_id= trans.org_id
and 	     g.org_id=trans.org_id
and 	     nvl(msi.organization_id,to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',trans.org_id)))
 = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',trans.org_id))
Parameter Name SQL text Validation
Order By
 
LOV Oracle
Batch
 
LOV Oracle
Document Type
 
LOV Oracle
DUPLICATE
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Invoice Number High
 
LOV Oracle
Invoice Date Low
 
Date
Invoice Date High
 
Date
Customer Name
 
LOV Oracle
GL_SRS_NULL_NUM
 
Number
p_dummy1