JL Mexican Journal Entries - XML File

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