JL Argentine Sales Flat File - draft

➜ Back to Library
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
Run JL Argentine Sales Flat File - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Reporting Level 
 
 LOV Oracle
Reporting Context 
 
 LOV Oracle
Company Name 
 
 LOV Oracle
From Date 
 
 Date
To Date 
 
 Date
Tax Regime Code 
 
 LOV Oracle
VAT Tax Category 
 
 LOV Oracle
VAT Additional Tax Category 
 
 LOV Oracle
VAT Not Categorized Tax Category 
 
 LOV Oracle
VAT Non Taxable Tax Category 
 
 LOV Oracle
VAT Perception Tax Category 
 
 LOV Oracle
Municipal Tax Category Regime 
 
 LOV Oracle
Provincial Tax Category Regime 
 
 LOV Oracle
Excise Tax Category 
 
 LOV Oracle
Download
Blitz Report™