JL Argentine Purchasing Flat File - draft

➜ Back to Library
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
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