JL Argentine Purchasing Flat File - draft

Description
Categories: BI Publisher
Columns: Trx Id, Invoice Id, Tax Rate, Dgi Transaction Code, Vat Tax Amt, Taxable Amt, Vat Per Amt, Fed Per Amt, Exmpt Amt, Excs Amt ...
Application: Latin America Localizations
Source: Argentine Purchasing Flat File (XML) - Not Supported: Reserved For Future Use
Short Name: JLARPPFF_XML
DB package: JL_JLARPPFF_XMLP_PKG
SELECT det.trx_id                                      trx_id,
          det.trx_number                                            Invoice_ID,
        NVL(det.tax_rate, 0)                                        Tax_Rate,
        DECODE(ext.ATTRIBUTE4, NULL, ' ', 
                      LPAD(ext.ATTRIBUTE4, 1, ' '))          DGI_Transaction_code   ,                
            sum(NVL(ext.numeric9,0))                                              VAT_TAX_AMT                           ,
	sum(NVL(ext.NUMERIC10,0))                                      taxable_amt,
              sum(NVL(ext.NUMERIC3,0))                                               vat_per_amt,
              sum(NVL(ext.NUMERIC7,0))                                                fed_per_amt,
	sum(NVL(ext.NUMERIC2,0))	                              exmpt_amt,
                sum(NVL(ext.numeric6,0))                                           excs_amt,
	sum(NVL(ext.numeric8,0))                                          non_taxable_amount,
	sum(NVL(ext.numeric12,0))                                         doc_amt_non,
	TO_CHAR(det.trx_date, 'YYYYMMDD')                     Doc_Date,
	LPAD(substr(nvl(ext.gdf_ap_invoices_att13,'0'),1,2),2, '0')       DGI_doc_Type_code,
		rpad(to_char(det.gl_date,'YYYYMMDD'),8,' ')  
            ||lpad(nvl(det.port_of_entry_code,'0'),3,'0')  
            ||'    '         Cust_issue_dtl,   
	 det.tax_invoice_number                                         Custom_issue_num, 
	  lpad(NVL(ext.attribute21,'0'),2,'0')
           ||LPAD(det.BILLING_TP_TAXPAYER_ID,11,'0')
          ||RPAD(det.BILLING_TP_NAME, 30, ' ')                          Supplier_dtl,
	NVL(SUBSTR(det.GDF_FND_CURRENCIES_ATT1, 1, 3), '   ')           Currency_Code,
          nvl(det.CURRENCY_CONVERSION_RATE,1)                                         Exchange_Rate ,
          DECODE(ext.attribute19, NULL, '00000000000000',
                      LPAD(ext.attribute19, 14, '0'))
         ||lpad(NVL(TO_CHAR(TO_DATE(ext.attribute23,
                      'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD'),' '),8,' ')                 CAI_INFO,
                      LPAD(NVL(ext.attribute22,'00'), 2, '0')     Sup_VAT_Regn_status_code ,
	 sum(NVL(ext.NUMERIC5,0))                                                                   mun_amt,
       sum(abs(NVL(ext.NUMERIC9,0)))                                                                             tot_vat,
            decode(ext.attribute20,'Y', 'C',' ')                                                                    fisc_prn,
           NVL(det.cancel_flag,'N')    status,
       sum(NVL(ext.NUMERIC4,0))	pro_amt,
          det.tax_type_code tax_type_code, 
	JL_JLARPPFF_XMLP_PKG.cf_vat_amtformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) )) CF_vat_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) )) CF_tot_doc_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_exmpt_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC2 , 0 ) )) CF_exmpt_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_fed_per_amt_numformula0004(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) )) CF_fed_per_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_fed_per_amtformula0006(JL_JLARPPFF_XMLP_PKG.cf_fed_per_amt_numformula0004(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) ))) CF_fed_per_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_taxable_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC10 , 0 ) )) CF_taxable_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_vat_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) )) CF_VAT_per_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_exc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_mun_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) )) CF_mun_per_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_pro_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) )) CF_pro_per_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amtformula(JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) ))) CF_vat_tax_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_num, 
	JL_JLARPPFF_XMLP_PKG.cf_exmpt_amtformula(JL_JLARPPFF_XMLP_PKG.cf_exmpt_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC2 , 0 ) ))) CF_exmpt_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_fed_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_vat_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) ))) CF_vat_per_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_mun_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_mun_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) ))) CF_mun_per_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_pro_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_pro_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) ))) CF_pro_per_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_exc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_exc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) ))) CF_exc_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_taxable_amtformula(JL_JLARPPFF_XMLP_PKG.cf_taxable_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC10 , 0 ) ))) CF_taxable_amt, 
	JL_JLARPPFF_XMLP_PKG.cf_blank_chrformula() CF_blank_chr, 
	JL_JLARPPFF_XMLP_PKG.cf_vat_rate_qtyformula(det.trx_id) CF_vat_rate_qty, 
	JL_JLARPPFF_XMLP_PKG.cf_rec_type1formula(sum ( NVL ( ext.numeric8 , 0 ) ),
	JL_JLARPPFF_XMLP_PKG.cf_rec_countformula(),
	NVL ( det.cancel_flag , 'N' ), TO_CHAR ( det.trx_date , 'YYYYMMDD' ), LPAD ( substr ( nvl ( ext.gdf_ap_invoices_att13 , '0' ) , 1 , 2 ) , 2 , '0' ), decode ( ext.attribute20 , 'Y' , 'C' , ' ' ),
	JL_JLARPPFF_XMLP_PKG.cf_point_of_saleformula(det.trx_number),
	JL_JLARPPFF_XMLP_PKG.cf_doc_numformula(det.trx_number), 
	rpad ( to_char ( det.gl_date , 'YYYYMMDD' ) , 8 , ' ' ) || lpad ( nvl ( det.port_of_entry_code , '0' ) , 3 , '0' ) || '    ', :CF_cust_num_valdigit, 
	lpad ( NVL ( ext.attribute21 , '0' ) , 2 , '0' ) || LPAD ( det.BILLING_TP_TAXPAYER_ID , 11 , '0' ) || RPAD ( det.BILLING_TP_NAME , 30