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,<