JL Mexican Journal Entries - XML File
Description
Categories: BI Publisher
Columns: Payment Method Code, Payment Type Flag, Processing Type, Payment Id, Payment Number, Payment Amount, Payment Date, Payment Cur Code, Payment Exchange Rate, Source Acct Num ...
Columns: Payment Method Code, Payment Type Flag, Processing Type, Payment Id, Payment Number, Payment Amount, Payment Date, Payment Cur Code, Payment Exchange Rate, Source Acct Num ...
Application: Latin America Localizations
Source: Mexican Journal Entries - XML File
Short Name: JLMXJENT_XMLP
DB package: JL_MX_JOURNAL_ENTRIES_PKG
Source: Mexican Journal Entries - XML File
Short Name: JLMXJENT_XMLP
DB package: JL_MX_JOURNAL_ENTRIES_PKG
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',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, 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_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 |
|
||
Journal Entry Source |
|
LOV Oracle | |
Accounting Flexfield From |
|
||
Accounting Flexfield To |
|
||
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 |
|
||
Audit Process Number |
|
||
Seal |
|
||
Certificate Number |
|
||
Certificate |
|
||
Include Expenses Fiscal Information |
|
LOV Oracle | |
P_LEDGER |
|
||
P_LEGAL_ENTITY |
|
||
P_JE_SOURCE_NAME |
|
||
JGSLA_CHART_OF_ACCOUNTS |
|
LOV Oracle | |
Period To |
|
LOV Oracle | |
P_GL_DATE_FROM |
|
Date | |
P_GL_DATE_TO |
|
Date | |
Posting Status |
|
LOV Oracle | |
Request Type Dummy |
|
Number | |
Request Type Dummy1 |
|
Number |