ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F15F3869E053B46B63588994 SELECT det.trx_id customer_trx_id, to_char(det.trx_date,'RRRRMMDD') doc_date , decode(det.event_class_code,'INVOICE','01','CREDIT_MEMO','03','DEBIT_MEMO','04') dgi_codes, decode(substr(ext.gdf_ra_batch_sources_att7,1,1), 'Y', 'C',' ') fiscal_printer, lpad(substr(det.trx_number,3,4),4,'0') || lpad(substr(det.trx_number,8,20),20,'0') || lpad(substr(det.trx_number,8,20),20,'0') || rpad(nvl(substr(nvl(det.gdf_ra_cust_bill_att10,det.gdf_ra_cust_ship_att10),1,2),'0'),2,'0') || decode(NVL(det.billing_tp_taxpayer_id,det.shipping_tp_taxpayer_id),NULL,'0000000000', rpad(substr(nvl(det.billing_tp_taxpayer_id,det.shipping_tp_taxpayer_id),1,10),10,'0')) || rpad(nvl(substr(nvl(det.gdf_ra_cust_bill_att12,det.gdf_ra_cust_ship_att12),1,1),'0'), 1,'0') || rpad(substr(nvl(det.billing_tp_name,det.shipping_tp_name),1,30),30,' ') string1, sum(nvl(ext.numeric12,0)) doc_amt, sum(NVL(ext.numeric8,0)) non_txbl_amt, SUM(NVL(ext.numeric10,0)) txbl_amt, det.tax_rate vat_rate, SUM(NVL(ext.numeric9,0)) vat_amount, SUM(NVL(ext.numeric1,0)) non_reg_amt, SUM(NVL(ext.numeric2,0)) exmpt_amt, SUM(NVL(ext.numeric3,0)) fed_percep_amt, SUM(NVL(ext.numeric4,0)) prov_precep_amt, SUM(NVL(ext.numeric5,0)) munic_percep_amt, SUM(NVL(ext.numeric6,0)) excise_amt, nvl(lpad(substr(ext.Attribute8,1,2),2,'0'),'00') cust_vat_reg_code, nvl(rpad(nvl(substr(det.gdf_fnd_currencies_att1,1,3),' '),3,' '),' ') cur_code, nvl(det.currency_conversion_rate,1) exchange_rate, nvl(ext.numeric11,1) vat_rate_qty, nvl(ext.attribute4,' ') dgi_trx_code, lpad(nvl(substr(ext.attribute19,1,14),'0'),14,'0')|| DECODE(ext.attribute23, NULL,'00000000', TO_CHAR(TO_DATE(ext.attribute23, 'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD')) CAI_info, decode(ext.gdf_ra_cust_trx_att19,NULL,' ', to_char(ext.gdf_ra_cust_trx_att19,'YYYYMMDD')) doc_void_date, decode(det.application_doc_status, 'VD','VD',NULL) void_trx, JL_JLARTSFF_XMLP_PKG.cf_doc_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), sum ( nvl ( ext.numeric12 , 0 ) )) CF_doc_amt_num, JL_JLARTSFF_XMLP_PKG.cf_doc_amt_chrformula(sum ( nvl ( ext.numeric12 , 0 ) )) CF_doc_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_vat_rateformula(det.tax_rate) CF_vat_rate, JL_JLARTSFF_XMLP_PKG.cf_non_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_num, JL_JLARTSFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric10 , 0 ) )) CF_taxable_amt_num, JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_chrformula(SUM ( NVL ( ext.numeric10 , 0 ) )) CF_taxable_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_num, JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_chrformula(SUM ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric1 , 0 ) )) CF_non_reg_tax_amt_num, JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_chrformula(SUM ( NVL ( ext.numeric1 , 0 ) )) CF_non_reg_tax_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric2 , 0 ) )) CF_exmpt_amt_num, JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_chrformula(SUM ( NVL ( ext.numeric2 , 0 ) )) CF_exmpt_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric3 , 0 ) )) CF_fed_per_amt_num, JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_chrformula(SUM ( NVL ( ext.numeric3 , 0 ) )) CF_fed_per_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric4 , 0 ) )) CF_pro_per_amt_num, JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_chrformula(SUM ( NVL ( ext.numeric4 , 0 ) )) CF_pro_per_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric5 , 0 ) )) CF_mun_per_amt_num, JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_chrformula(SUM ( NVL ( ext.numeric5 , 0 ) )) CF_mun_per_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_exc_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_num, JL_JLARTSFF_XMLP_PKG.cf_exc_amt_chrformula(SUM ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_chr, JL_JLARTSFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.currency_conversion_rate , 1 )) CF_exchange_rate, JL_JLARTSFF_XMLP_PKG.cf_rec_countformula(det.trx_id) CF_rec_count, JL_JLARTSFF_XMLP_PKG.cf_vat_non_recformula(sum ( NVL ( ext.numeric8 , 0 ) ), decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), to_char ( det.trx_date , 'RRRRMMDD' ), decode ( det.event_class_code , 'INVOICE' , '01' , 'CREDIT_MEMO' , '03' , 'DEBIT_MEMO' , '04' ), decode ( substr ( ext.gdf_ra_batch_sources_att7 , 1 , 1 ) , 'Y' , 'C' , ' ' ), lpad ( substr ( det.trx_number , 3 , 4 ) , 4 , '0' ) || lpad ( substr ( det.trx_number , 8 , 20 ) , 20 , '0' ) || lpad ( substr ( det.trx_number , 8 , 20 ) , 20 , '0' ) || rpad ( nvl ( substr ( nvl ( det.gdf_ra_cust_bill_att10 , det.gdf_ra_cust_ship_att10 ) , 1 , 2 ) , '0' ) , 2 , '0' ) || decode ( NVL ( det.billing_tp_taxpayer_id , det.shipping_tp_taxpayer_id ) , NULL , '0000000000' , rpad ( substr ( nvl ( det.billing_tp_taxpayer_id , det.shipping_tp_taxpayer_id ) , 1 , 10 ) , 10 , '0' ) ) || rpad ( nvl ( substr ( nvl ( det.gdf_ra_cust_bill_att12 , det.gdf_ra_cust_ship_att12 ) , 1 , 1 ) , '0' ) , 1 , '0' ) || rpad ( substr ( nvl ( det.billing_tp_name , det.shipping_tp_name ) , 1 , 30 ) , 30 , ' ' ), lpad ( nvl ( substr ( ext.attribute19 , 1 , 14 ) , '0' ) , 14 , '0' ) || DECODE ( ext.attribute23 , NULL , '00000000' , TO_CHAR ( TO_DATE ( ext.attribute23 , 'YYYY/MM/DD HH24:MI:SS' ) , 'YYYYMMDD' ) ), decode ( ext.gdf_ra_cust_trx_att19 , NULL , ' ' , to_char ( ext.gdf_ra_cust_trx_att19 , 'YYYYMMDD' ) ), JL_JLARTSFF_XMLP_PKG.cf_rec_countformula(det.trx_id), JL_JLARTSFF_XMLP_PKG.cf_doc_amt_chrformula(sum ( nvl ( ext.numeric12 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )), nvl ( lpad ( substr ( ext.Attribute8 , 1 , 2 ) , 2 , '0' ) , '00' ), nvl ( rpad ( nvl ( substr ( det.gdf_fnd_currencies_att1 , 1 , 3 ) , ' ' ) , 3 , ' ' ) , ' ' ), JL_JLARTSFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.currency_conversion_rate , 1 )), nvl ( ext.numeric11 , 1 ), nvl ( ext.attribute4 , ' ' ), det.tax_rate, JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_chrformula(SUM ( NVL ( ext.numeric2 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_chrformula(SUM ( NVL ( ext.numeric10 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_vat_rateformula(det.tax_rate), JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_chrformula(SUM ( NVL ( ext.numeric9 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_chrformula(SUM ( NVL ( ext.numeric1 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_chrformula(SUM ( NVL ( ext.numeric3 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_chrformula(SUM ( NVL ( ext.numeric4 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_chrformula(SUM ( NVL ( ext.numeric5 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_exc_amt_chrformula(SUM ( NVL ( ext.numeric6 , 0 ) ))) CF_vat_non_rec, JL_JLARTSFF_XMLP_PKG.cf_blank_chrformula() CF_blank_chr 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_date , det.event_class_code, ext.gdf_ra_batch_sources_att7, det.trx_number, det.gdf_ra_cust_bill_att10, det.gdf_ra_cust_ship_att10, det.billing_tp_taxpayer_id, det.shipping_tp_taxpayer_id, det.billing_tp_taxpayer_id, det.shipping_tp_taxpayer_id, det.gdf_ra_cust_bill_att12, det.gdf_ra_cust_ship_att12, det.billing_tp_name, det.shipping_tp_name, det.tax_rate, ext.Attribute8, det.gdf_fnd_currencies_att1, det.currency_conversion_rate, ext.numeric11, ext.attribute4, ext.attribute19, ext.attribute23, gdf_ra_cust_trx_att19, det.application_doc_status, det.trx_type_id ORDER BY det.trx_date, det.trx_type_id, det.trx_number Y JL JLARTSFF_XML US JL Argentine Sales Flat File - draft Application: Latin America Localizations Source: Argentine Sales Flat File (XML) - Not Supported: Reserved For Future Use Short Name: JLARTSFF_XML DB package: JL_JLARTSFF_XMLP_PKG BI Publisher :c_base_currency_code :c_base_description :c_base_min_acct_unit :c_base_precision :c_company_name_header :cp_comp_tax_id :cp_non_tot :cp_rec_count :cp_tot :p_conc_request_id :p_debug_switch :p_errbuf :p_exc_id :p_excise :p_from_date :p_legal_entity_id :p_legal_entity_name :p_min_precision :p_mun_tax_regime :p_non_tax_id :p_pro_tax_regime :p_reporting_entity_id :p_reporting_level :p_retcode :p_set_of_books_id :p_tax_regime :p_to_date :p_trace_switch :p_vat :p_vat_id :p_vat_non_taxable :p_vataddl :p_vataddl_id :p_vatnot :p_vatnot_id :p_vatperc :p_vatperc_id 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_from_date Date Y US From Date 5 50 :p_to_date Date Y US To Date 6 60 :p_tax_regime LOV Oracle ZX_TRL_TAX_REGIME_CODE Y select tax_regime_code id, tax_regime_code value, tax_regime_name description from zx_regimes_vl where has_sub_regime_flag='N' order by value,description Y US Tax Regime Code 7 70 :p_vat LOV Oracle JLZZ_AR_TAX_REP_CATEGORY Y select tax_category id, tax_category value, description description from jl_zz_ar_tx_categry where nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by tax_category Y US VAT Tax Category 8 80 :p_vataddl LOV Oracle JLZZ_AR_TAX_REP_CATEGORY Y select tax_category id, tax_category value, description description from jl_zz_ar_tx_categry where nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by tax_category US VAT Additional Tax Category 9 90 :p_vatnot LOV Oracle JLZZ_AR_TAX_REP_CATEGORY Y select tax_category id, tax_category value, description description from jl_zz_ar_tx_categry where nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by tax_category US VAT Not Categorized Tax Category 10 100 :p_vatnon LOV Oracle JLZZ_AR_TAX_REP_CATEGORY Y select tax_category id, tax_category value, description description from jl_zz_ar_tx_categry where nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by tax_category US VAT Non Taxable Tax Category 11 110 :p_vatperc LOV Oracle JLZZ_AR_TAX_REP_CATEGORY Y select tax_category id, tax_category value, description description from jl_zz_ar_tx_categry where nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by tax_category US VAT Perception Tax Category 12 120 :p_mun_tax_regime LOV Oracle JLZZ_AR_TAX_REGIME Y select lookup_code id, lookup_code value, meaning description from fnd_lookups where lookup_type='JLZZ_AR_TAX_REGIME' and nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate and enabled_flag='Y' order by value,description US Municipal Tax Category Regime 13 130 :p_pro_tax_regime LOV Oracle JLZZ_AR_TAX_REGIME Y select lookup_code id, lookup_code value, meaning description from fnd_lookups where lookup_type='JLZZ_AR_TAX_REGIME' and nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate and enabled_flag='Y' order by value,description US Provincial Tax Category Regime 14 140 :p_excise LOV Oracle JLZZ_AR_TAX_REP_CATEGORY Y select tax_category id, tax_category value, description description from jl_zz_ar_tx_categry where nvl(start_date_active,sysdate)<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by tax_category US Excise Tax Category