ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F1583869E053B46B63588994 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 Y JL JLARPPFF_XML US JL Argentine Purchasing Flat File - draft 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 BI Publisher :c_base_currency_code :c_base_description :c_base_min_acct_unit :c_base_precision :c_company_name_header :cf_cust_num_valdigit :cp_comp_tax_id :cp_non_count :cp_rec_count :cp_tot_rec :p_conc_request_id :p_debug_switch :p_errbuf :p_exc_tax_type_from :p_exc_tax_type_to :p_excluded_trx_letter :p_gl_date_from :p_gl_date_to :p_legal_entity_id :p_legal_entity_name :p_location_id :p_min_precision :p_mun_tax_type_from :p_mun_tax_type_to :p_non_taxable_tax_type :p_other_fed_per_from :p_other_fed_per_to :p_per_tax_type_from :p_per_tax_type_to :p_pro_tax_type_from :p_pro_tax_type_to :p_reporting_entity_id :p_reporting_level :p_retcode :p_set_of_books_id :p_vat_tax_type 1 10 :p_reporting_level LOV Oracle XLE_LEGAL_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' order by value,description Y US Reporting Level 2 20 :p_reporting_entity_id LOV Oracle XLE_LEGAL_REPORTING_CONTEXT Y select entity_id id, entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.xle_legal_reporting_level order by value,description Y US Reporting Context 3 30 :p_legal_entity_id LOV Oracle ZX_COMPANY_NAME Y select legal_entity_id id, legal_entity_name value, null description from gl_ledger_le_v where :$flex$.xle_legal_reporting_level='1000' and ledger_id=:$flex$.xle_legal_reporting_context order by value,description US Company Name 4 40 :p_gl_date_from Date Y US From Date 5 50 :p_gl_date_to Date Y US To Date 6 60 :p_vat_tax_type LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code Y US VAT Tax Type 7 70 :p_non_taxable_tax_type LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US Non Taxable Tax Type 8 80 :p_per_tax_type_from LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US VAT Perception Tax Type From 9 90 :p_per_tax_type_to LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code :$FLEX$.VAT_Perception_Tax_Type_From US VAT Perception Tax Type To 10 100 :p_other_fed_per_from LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US Other Federal Perception Tax Type From 11 110 :p_other_fed_per_to LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US Other Federal Perception Tax Type To 12 120 :p_pro_tax_type_from LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US Provincial Tax Type From 13 130 :p_pro_tax_type_to LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code :$FLEX$.Provincial_Tax_Type_From US Provincial Tax Type To 14 140 :p_mun_tax_type_from LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US Municipal Tax Type From 15 150 :p_mun_tax_type_to LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code :$FLEX$.Municipal_Tax_Type_From US Municipal Tax Type To 16 160 :p_exc_tax_type_from LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code US Excise Tax Type From 17 170 :p_exc_tax_type_to LOV Oracle JLZZ_AP_TAX_TYPE Y select lookup_code id, lookup_code value, displayed_field description from ap_lookup_codes where lookup_type='TAX TYPE' order by lookup_code :$FLEX$.Excise_Tax_Type_From US Excise Tax Type To 18 180 :p_excluded_trx_letter LOV Oracle JLAR_DOCUMENT_LETTER Y select lookup_code id, lookup_code value, meaning description from fnd_lookups where lookup_type='JLAR_DOCUMENT_LETTER' and nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate and enabled_flag='Y' order by lookup_code US Transaction Letter For Excluded Transactions