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