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