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