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