JL Mexican Journal Entries - XML File
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Latin America Localizations
Source: Mexican Journal Entries - XML File
Short Name: JLMXJENT_XMLP
DB package: JL_MX_JOURNAL_ENTRIES_PKG
Application: Latin America Localizations
Source: Mexican Journal Entries - XML File
Short Name: JLMXJENT_XMLP
DB package: JL_MX_JOURNAL_ENTRIES_PKG
Run
JL Mexican Journal Entries - XML File and other Oracle EBS reports with Blitz Report™ on our demo environment
select jl.payment_method_code payment_method_code, jl.payment_type_flag payment_type_flag, jl.processing_type processing_type, jl.payment_id payment_id, substr(jl.payment_number,1,20) payment_number, trim(TO_CHAR(nvl(jl.payment_amount,0),'9999999999999999999990.00')) payment_amount, to_char(jl.payment_date,'YYYY-MM-DD') payment_date, DECODE(jl.PAYMENT_CUR_CODE,'MXN',null,jl.PAYMENT_CUR_CODE) PAYMENT_CUR_CODE, nvl(DECODE(jl.PAYMENT_CUR_CODE,'MXN',null,jl.PAYMENT_EXCHANGE_RATE),1) PAYMENT_EXCHANGE_RATE, substr(jl.source_acct_num,1,50) source_acct_num, decode(jl.foreign_source_bank_flag,'Y','999',substr(ce.bank_number,1,3)) source_nat_bank, decode(jl.foreign_source_bank_flag,'Y',substr(jl.source_bank_name,1,150),' ') source_frn_bank, substr(jl.dest_acct_num,1,50) dest_acct_num, decode(jl.foreign_dest_bank_flag,'Y','999', decode(:entity_code||'+'||:event_class_code,'CE_CASHFLOWS+BAT_CLASS', substr(ce2.bank_number,1,3), --bug 27946436 substr(iby.bank_number,1,3))) dest_nat_bank, decode(jl.foreign_dest_bank_flag,'Y',substr(jl.dest_bank_name,1,150),' ') dest_frn_bank, -- jl.pay_sched_num pay_sched_num, -- jl.pay_sched_cfdi pay_sched_cfdi, -- jl.pay_sched_cfd_cbb_series pay_sched_cfd_cbb_series, -- jl.pay_sched_cfd_cbb_number pay_sched_cfd_cbb_number, -- jl.PAY_SCHED_AMOUNT pay_sched_amount, jl.invoice_id invoice_id, jl.invoice_cfdi invoice_cfdi1, jl.invoice_cfd_cbb_series invoice_cfd_cbb_series1, jl.invoice_cfd_cbb_number invoice_cfd_cbb_number1, jl.foreign_invoice_number foreign_invoice_number1, trim(TO_CHAR(nvl(jl.invoice_amount,0),'9999999999999999999990.00')) inv_amt, DECODE(jl.INV_CUR_CODE,'MXN',null,jl.INV_CUR_CODE) INV_CUR_CODE1, nvl(DECODE(jl.INV_CUR_CODE,'MXN',null,jl.INV_EXCHANGE_RATE),1) INV_EXCHANGE_RATE1, jl.addit_info_1 MERCHANT_TP_ID, -- merchant_taxpayer_id of an expense line substr(jl.third_party_name,1,300) third_party_name, jl.third_party_taxpayer_id third_party_taxpayer_id1, :entity_code entity_code, NVL(ADDIT_INFO_5,'99') payment_method, ADDIT_INFO_4 payment_cfdi -- payment CFDI. from jl_mx_je_line_details_t jl, ce_banks_v ce, ce_banks_v ce2, --bug 27946436 iby_external_bank_accounts_v iby where jl.request_id = :g_con_request_id and jl.je_line_id = :je_line_id and jl.source_bank_id = ce.bank_party_id (+) and jl.dest_bank_id = ce2.bank_party_id (+) --bug 27946436 and jl.dest_acct_id = iby.bank_account_id (+) and (:entity_code in ('AP_PAYMENTS','CE_CASHFLOWS') and :event_class_code in ('PAYMENTS','RECONCILED PAYMENTS','FUTURE DATED PAYMENTS','REFUNDS','BAT_CLASS')) Union all select jl.payment_method_code payment_method_code, jl.payment_type_flag payment_type_flag, jl.processing_type processing_type, jl.payment_id payment_id, --cash_receipt_id substr(jl.addit_info_2,1,20) payment_number, -- receipt_number trim(TO_CHAR(nvl(jl.payment_amount,0),'9999999999999999999990.00')) payment_amount, -- receipt_amount to_char(jl.payment_date,'YYYY-MM-DD') payment_date, -- receipt_date DECODE(jl.PAYMENT_CUR_CODE,'MXN',null,jl.PAYMENT_CUR_CODE) PAYMENT_CUR_CODE, -- receipt_currency_code nvl(DECODE(jl.PAYMENT_CUR_CODE,'MXN',null,jl.PAYMENT_EXCHANGE_RATE),1) PAYMENT_EXCHANGE_RATE, -- receipt_exchange_Rate substr(jl.source_acct_num,1,50) source_acct_num, decode(jl.foreign_source_bank_flag,'Y','999',substr(iby.bank_number,1,3)) source_nat_bank, decode(jl.foreign_source_bank_flag,'Y',substr(jl.source_bank_name,1,150),' ') source_frn_bank, substr(jl.dest_acct_num,1,50) dest_acct_num, decode(jl.foreign_dest_bank_flag,'Y','999',substr(ce.bank_number,1,3)) dest_nat_bank, decode(jl.foreign_dest_bank_flag,'Y',substr(jl.dest_bank_name,1,150),' ') dest_frn_bank, jl.invoice_id invoice_id, jl.invoice_cfdi invoice_cfdi1, jl.invoice_cfd_cbb_series invoice_cfd_cbb_series1, jl.invoice_cfd_cbb_number invoice_cfd_cbb_number1, jl.foreign_invoice_number foreign_invoice_number1, trim(TO_CHAR(nvl(jl.invoice_amount,0),'9999999999999999999990.00')) inv_amt, DECODE(jl.INV_CUR_CODE,'MXN',null,jl.INV_CUR_CODE) INV_CUR_CODE1, nvl(DECODE(jl.INV_CUR_CODE,'MXN',null,jl.INV_EXCHANGE_RATE),1) INV_EXCHANGE_RATE1, jl.addit_info_1 MERCHANT_TP_ID, -- merchant_taxpayer_id of an expense line substr(jl.third_party_name,1,300) third_party_name, jl.third_party_taxpayer_id third_party_taxpayer_id1, :entity_code entity_code, NVL(ADDIT_INFO_5,'99') payment_method, ADDIT_INFO_4 payment_cfdi -- payment CFDI. from jl_mx_je_line_details_t jl, ce_banks_v ce, iby_external_bank_accounts_v iby where jl.request_id = :g_con_request_id and jl.je_line_id = :je_line_id and jl.DEST_BANK_ID = ce.bank_party_id (+) and jl.SOURCE_ACCT_ID = iby.bank_account_id (+) and (:entity_code ='RECEIPTS' and :event_class_code in ('RECEIPT','MISC_RECEIPT')) |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Ledger |
|
LOV Oracle | |
| Legal Entity |
|
LOV Oracle | |
| Journals Report Version |
|
Char | |
| Journal Entry Source |
|
LOV Oracle | |
| Accounting Flexfield From |
|
Char | |
| Accounting Flexfield To |
|
Char | |
| Subaccount Segment Name |
|
LOV Oracle | |
| Show Adjustment Periods |
|
LOV Oracle | |
| Period |
|
LOV Oracle | |
| Include Zero Amount |
|
LOV Oracle | |
| Request Type |
|
LOV Oracle | |
| Audit Request Number |
|
Char | |
| Audit Process Number |
|
Char | |
| Seal |
|
Char | |
| Certificate Number |
|
Char | |
| Certificate |
|
Char | |
| Include Expenses Fiscal Information |
|
LOV Oracle |
