JL Argentine Purchasing Flat File - draft
Description
Categories: BI Publisher
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
Source: Argentine Purchasing Flat File (XML) - Not Supported: Reserved For Future Use
Short Name: JLARPPFF_XML
DB package: JL_JLARPPFF_XMLP_PKG
Run
JL Argentine Purchasing Flat File - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 , ' ' ), 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 , ' ' ), NVL ( det.tax_rate , 0 ), JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) , 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 ) ))), 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 ) ))), 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 ) ))), 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 ) ))) , 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 ) ))) , 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 ) ))), LPAD ( NVL ( ext.attribute22 , '00' ) , 2 , '0' ), NVL ( SUBSTR ( det.GDF_FND_CURRENCIES_ATT1 , 1 , 3 ) , ' ' ), JL_JLARPPFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.CURRENCY_CONVERSION_RATE , 1 )), JL_JLARPPFF_XMLP_PKG.cf_vat_rate_qtyformula(det.trx_id), DECODE ( ext.ATTRIBUTE4 , NULL , ' ' , LPAD ( ext.ATTRIBUTE4 , 1 , ' ' ) ), 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 ) ))) , JL_JLARPPFF_XMLP_PKG.cf_vat_rateformula(NVL ( det.tax_rate , 0 )), 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 ) ))) , JL_JLARPPFF_XMLP_PKG.cf_vat_countformula(det.trx_number), JL_JLARPPFF_XMLP_PKG.cf_doc_amt_chrformula(JL_JLARPPFF_XMLP_PKG.cf_doc_amt_nonformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) , JL_JLARPPFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )), JL_JLARPPFF_XMLP_PKG.cf_rate_qtyformula(det.trx_number), JL_JLARPPFF_XMLP_PKG.cf_dgi_tran_codeformula(det.trx_number)) CF_rec_type1, JL_JLARPPFF_XMLP_PKG.cf_vat_rateformula(NVL ( det.tax_rate , 0 )) CF_vat_rate, JL_JLARPPFF_XMLP_PKG.cf_doc_amt_chrformula(JL_JLARPPFF_XMLP_PKG.cf_doc_amt_nonformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) CF_doc_amt_chr, JL_JLARPPFF_XMLP_PKG.cf_non_blankformula() CF_non_blank, JL_JLARPPFF_XMLP_PKG.cf_point_of_saleformula(det.trx_number) CF_point_of_sale, JL_JLARPPFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.CURRENCY_CONVERSION_RATE , 1 )) CF_exchange_rate, JL_JLARPPFF_XMLP_PKG.cf_doc_numformula(det.trx_number) CF_doc_num, JL_JLARPPFF_XMLP_PKG.cf_fiscal_printerformula() CF_fiscal_printer, JL_JLARPPFF_XMLP_PKG.cf_cust_num_valdigitformula(det.tax_invoice_number) CF_cust_num_valdigit, JL_JLARPPFF_XMLP_PKG.cf_tot_vat_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( abs ( NVL ( ext.NUMERIC9 , 0 ) ) )) CF_tot_vat_amount, JL_JLARPPFF_XMLP_PKG.cf_non_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amount, JL_JLARPPFF_XMLP_PKG.cf_excise_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) )) CF_excise_amount, JL_JLARPPFF_XMLP_PKG.cf_pro_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) )) CF_pro_amount, JL_JLARPPFF_XMLP_PKG.cf_mun_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) )) CF_mun_amount, JL_JLARPPFF_XMLP_PKG.cf_fed_per_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) )) CF_FED_PER_AMOUNT, JL_JLARPPFF_XMLP_PKG.cf_vat_per_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) )) CF_vat_per_amount, JL_JLARPPFF_XMLP_PKG.cf_exempt_amountformula(sum ( NVL ( ext.NUMERIC2 , 0 ) )) CF_exempt_amount, JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amountformula() CF_tot_doc_amount, JL_JLARPPFF_XMLP_PKG.cf_rate_qtyformula(det.trx_number) CF_rate_qty, JL_JLARPPFF_XMLP_PKG.cf_vat_countformula(det.trx_number) CF_vat_count, JL_JLARPPFF_XMLP_PKG.cf_dgi_tran_codeformula(det.trx_number) CF_DGI_TRAN_CODE, JL_JLARPPFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_chr, JL_JLARPPFF_XMLP_PKG.cf_doc_amt_nonformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) )) CF_doc_amt_non, JL_JLARPPFF_XMLP_PKG.cf_rec_countformula() CF_rec_count, JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) CF_tot_doc_amt 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_number, det.tax_rate, ext.ATTRIBUTE4, det.trx_date, det.gl_date, det.tax_invoice_number, ext.attribute19, det.port_of_entry_code, ext.gdf_ap_invoices_att13, det.BILLING_TP_TAXPAYER_ID, det.BILLING_TP_NAME, det.GDF_FND_CURRENCIES_ATT1, det.CURRENCY_CONVERSION_RATE, det.SUPPLIER_TAX_INVOICE_NUMBER, det.SUPPLIER_TAX_INVOICE_DATE, det.REGISTRATION_STATUS_CODE, ext.attribute20, ext.attribute21, ext.attribute22, ext.attribute23, det.cancel_flag, det.tax_type_code order by trx_number,tax_rate |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Reporting Level |
|
LOV Oracle | |
Reporting Context |
|
LOV Oracle | |
Company Name |
|
LOV Oracle | |
From Date |
|
Date | |
To Date |
|
Date | |
VAT Tax Type |
|
LOV Oracle | |
Non Taxable Tax Type |
|
LOV Oracle | |
VAT Perception Tax Type From |
|
LOV Oracle | |
VAT Perception Tax Type To |
|
LOV Oracle | |
Other Federal Perception Tax Type From |
|
LOV Oracle | |
Other Federal Perception Tax Type To |
|
LOV Oracle | |
Provincial Tax Type From |
|
LOV Oracle | |
Provincial Tax Type To |
|
LOV Oracle | |
Municipal Tax Type From |
|
LOV Oracle | |
Municipal Tax Type To |
|
LOV Oracle | |
Excise Tax Type From |
|
LOV Oracle | |
Excise Tax Type To |
|
LOV Oracle | |