JL Argentine Sales Flat File - draft

Description
Categories: BI Publisher
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 ) , 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 , ' ' ), 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' ) ), decode ( ext.gdf_ra_cust_trx_att19 , NULL , '        ' , to_char ( ext.gdf_ra_cust_trx_att19 , 'YYYYMMDD' ) ), JL_JLARTSFF_XMLP_PKG.cf_rec_countformula(det.trx_id), JL_JLARTSFF_XMLP_PKG.cf_doc_amt_chrformula(sum ( nvl ( ext.numeric12 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )), nvl ( lpad ( substr ( ext.Attribute8 , 1 , 2 ) , 2 , '0' ) , '00' ), nvl ( rpad ( nvl ( substr ( det.gdf_fnd_currencies_att1 , 1 , 3 ) , ' ' ) , 3 , ' ' ) , '   ' ), JL_JLARTSFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.currency_conversion_rate , 1 )), nvl ( ext.numeric11 , 1 ), nvl ( ext.attribute4 , ' ' ), det.tax_rate, JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_chrformula(SUM ( NVL ( ext.numeric2 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_chrformula(SUM ( NVL ( ext.numeric10 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_vat_rateformula(det.tax_rate), JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_chrformula(SUM ( NVL ( ext.numeric9 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_chrformula(SUM ( NVL ( ext.numeric1 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_chrformula(SUM ( NVL ( ext.numeric3 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_chrformula(SUM ( NVL ( ext.numeric4 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_chrformula(SUM ( NVL ( ext.numeric5 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_exc_amt_chrformula(SUM ( NVL ( ext.numeric6 , 0 ) ))) CF_vat_non_rec, 
	JL_JLARTSFF_XMLP_PKG.cf_blank_chrformula() CF_blank_chr
FROM    zx_rep_trx_jx_ext_t ext,
        zx_rep_context_t con,
        zx_rep_trx_detail_t det
WHERE   con.request_id = :p_conc_request_id
AND     det.request_id = con.request_id
AND     det.rep_context_id = con.rep_context_id
AND     ext.detail_tax_line_id = det.detail_tax_line_id
GROUP BY  det.trx_id,
        det.trx_date ,
        det.event_class_code,
        ext.gdf_ra_batch_sources_att7,
        det.trx_number,
        det.gdf_ra_cust_bill_att10,
        det.gdf_ra_cust_ship_att10,
        det.billing_tp_taxpayer_id,
        det.shipping_tp_taxpayer_id,
	det.billing_tp_taxpayer_id,
        det.shipping_tp_taxpayer_id,
        det.gdf_ra_cust_bill_att12,
        det.gdf_ra_cust_ship_att12,
        det.billing_tp_name,
        det.shipping_tp_name,
        det.tax_rate,
        ext.Attribute8,
        det.gdf_fnd_currencies_att1,
        det.currency_conversion_rate,
        ext.numeric11,
        ext.attribute4,
        ext.attribute19,
         ext.attribute23,
        gdf_ra_cust_trx_att19,
        det.application_doc_status,
        det.trx_type_id
ORDER BY det.trx_date, det.trx_type_id, det.trx_number
Parameter Name SQL text Validation
Excise Tax Category
 
LOV Oracle
Provincial Tax Category Regime
 
LOV Oracle
Municipal Tax Category Regime
 
LOV Oracle
VAT Perception Tax Category
 
LOV Oracle
VAT Non Taxable Tax Category
 
LOV Oracle
VAT Not Categorized Tax Category
 
LOV Oracle
VAT Additional Tax Category
 
LOV Oracle
VAT Tax Category
 
LOV Oracle
Tax Regime Code
 
LOV Oracle
To Date
 
Date
From Date
 
Date
Company Name
 
LOV Oracle
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle