JL Argentine Sales Flat File - draft

Description
Categories: BI Publisher
Columns: Customer Trx Id, Doc Date, Dgi Codes, Fiscal Printer, String1, Doc Amt, Non Txbl Amt, Txbl Amt, Vat Rate, Vat Amount ...
Application: Latin America Localizations
Source: Argentine Sales Flat File (XML) - Not Supported: Reserved For Future Use
Short Name: JLARTSFF_XML
DB package: JL_JLARTSFF_XMLP_PKG
SELECT  det.trx_id                                            customer_trx_id,
       to_char(det.trx_date,'RRRRMMDD')                      doc_date ,
        decode(det.event_class_code,'INVOICE','01','CREDIT_MEMO','03','DEBIT_MEMO','04') dgi_codes,
        decode(substr(ext.gdf_ra_batch_sources_att7,1,1), 'Y', 'C',' ')    fiscal_printer,
        lpad(substr(det.trx_number,3,4),4,'0') ||
        lpad(substr(det.trx_number,8,20),20,'0') ||
        lpad(substr(det.trx_number,8,20),20,'0') ||
        rpad(nvl(substr(nvl(det.gdf_ra_cust_bill_att10,det.gdf_ra_cust_ship_att10),1,2),'0'),2,'0') ||
        decode(NVL(det.billing_tp_taxpayer_id,det.shipping_tp_taxpayer_id),NULL,'0000000000',
	    rpad(substr(nvl(det.billing_tp_taxpayer_id,det.shipping_tp_taxpayer_id),1,10),10,'0'))  ||
        rpad(nvl(substr(nvl(det.gdf_ra_cust_bill_att12,det.gdf_ra_cust_ship_att12),1,1),'0'), 1,'0') ||
        rpad(substr(nvl(det.billing_tp_name,det.shipping_tp_name),1,30),30,' ')         string1,
        sum(nvl(ext.numeric12,0))                                         doc_amt,            
        sum(NVL(ext.numeric8,0))                                          non_txbl_amt,       
        SUM(NVL(ext.numeric10,0))                                         txbl_amt,           
        det.tax_rate                                          vat_rate,
        SUM(NVL(ext.numeric9,0))                                          vat_amount,         
        SUM(NVL(ext.numeric1,0))                                          non_reg_amt,        
        SUM(NVL(ext.numeric2,0))                                          exmpt_amt,          
        SUM(NVL(ext.numeric3,0))                                          fed_percep_amt,     
        SUM(NVL(ext.numeric4,0))                                          prov_precep_amt,    
        SUM(NVL(ext.numeric5,0))                                          munic_percep_amt,   
        SUM(NVL(ext.numeric6,0))                                          excise_amt,         
        nvl(lpad(substr(ext.Attribute8,1,2),2,'0'),'00')      cust_vat_reg_code,
        nvl(rpad(nvl(substr(det.gdf_fnd_currencies_att1,1,3),' '),3,' '),'   ')  cur_code,
        nvl(det.currency_conversion_rate,1) 	              exchange_rate,      
        nvl(ext.numeric11,1)                                  vat_rate_qty,
        nvl(ext.attribute4,' ')                               dgi_trx_code,
        lpad(nvl(substr(ext.attribute19,1,14),'0'),14,'0')||
        DECODE(ext.attribute23, NULL,'00000000',
	        TO_CHAR(TO_DATE(ext.attribute23,
                      'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD'))  CAI_info,
        decode(ext.gdf_ra_cust_trx_att19,NULL,'        ',
               to_char(ext.gdf_ra_cust_trx_att19,'YYYYMMDD')) doc_void_date,
             decode(det.application_doc_status, 'VD','VD',NULL) void_trx, 
	JL_JLARTSFF_XMLP_PKG.cf_doc_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), sum ( nvl ( ext.numeric12 , 0 ) )) CF_doc_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_doc_amt_chrformula(sum ( nvl ( ext.numeric12 , 0 ) )) CF_doc_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_vat_rateformula(det.tax_rate) CF_vat_rate, 
	JL_JLARTSFF_XMLP_PKG.cf_non_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric10 , 0 ) )) CF_taxable_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_chrformula(SUM ( NVL ( ext.numeric10 , 0 ) )) CF_taxable_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_chrformula(SUM ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric1 , 0 ) )) CF_non_reg_tax_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_chrformula(SUM ( NVL ( ext.numeric1 , 0 ) )) CF_non_reg_tax_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric2 , 0 ) )) CF_exmpt_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_chrformula(SUM ( NVL ( ext.numeric2 , 0 ) )) CF_exmpt_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric3 , 0 ) )) CF_fed_per_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_chrformula(SUM ( NVL ( ext.numeric3 , 0 ) )) CF_fed_per_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric4 , 0 ) )) CF_pro_per_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_chrformula(SUM ( NVL ( ext.numeric4 , 0 ) )) CF_pro_per_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric5 , 0 ) )) CF_mun_per_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_chrformula(SUM ( NVL ( ext.numeric5 , 0 ) )) CF_mun_per_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_exc_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_num, 
	JL_JLARTSFF_XMLP_PKG.cf_exc_amt_chrformula(SUM ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_chr, 
	JL_JLARTSFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.currency_conversion_rate , 1 )) CF_exchange_rate, 
	JL_JLARTSFF_XMLP_PKG.cf_rec_countformula(det.trx_id) CF_rec_count, 
	JL_JLARTSFF_XMLP_PKG.cf_vat_non_recformula(sum ( NVL ( ext.numeric8 , 0 ) ), decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), to_char ( det.trx_date , 'RRRRMMDD' ), decode ( det.event_class_code , 'INVOICE' , '01' , 'CREDIT_MEMO' , '03' , 'DEBIT_MEMO' , '04' ), decode ( substr ( ext.gdf_ra_batch_sources_att7 , 1 , 1 ) , 'Y' , 'C' , ' ' ), lpad ( substr ( det.trx_number , 3 , 4 ) , 4 , '0' ) || lpad ( substr ( det.trx_number , 8 , 20 )