JL Mexican Journal Entries - XML File

➜ Back to Library
Description
Categories: BI Publisher
Application: Latin America Localizations
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
Request Type Dummy1 
 
 Number
Request Type Dummy 
 
 Number
Posting Status 
 
 LOV Oracle
P_GL_DATE_TO 
 
 Date
P_GL_DATE_FROM 
 
 Date
Period To 
 
 LOV Oracle
JGSLA_CHART_OF_ACCOUNTS 
 
 LOV Oracle
P_JE_SOURCE_NAME 
 
P_LEGAL_ENTITY 
 
P_LEDGER 
 
Include Expenses Fiscal Information 
 
 LOV Oracle
Certificate 
 
Certificate Number 
 
Seal 
 
Audit Process Number 
 
Audit Request Number 
 
Request Type 
 
 LOV Oracle
Include Zero Amount 
 
 LOV Oracle
Period 
 
 LOV Oracle
Show Adjustment Periods 
 
 LOV Oracle
Subaccount Segment Name 
 
 LOV Oracle
Accounting Flexfield To 
 
Accounting Flexfield From 
 
Journal Entry Source 
 
 LOV Oracle
Journals Report Version 
 
Legal Entity 
 
 LOV Oracle
Ledger 
 
 LOV Oracle
Download
Related Blitz Reports
CE Bank Statement Cashflow Creation Execution - draft
CE Cash In Transit - draft
CE Transactions Available for Reconciliation - draft
CE Cleared Transactions - draft
IGI Standing Charges: Listing - draft
JL Brazilian Payables Non-Associated Trade Notes and Bank Collection Documents - draft
CE Cash Forecast Template Listing - draft
AR Bank Risk
AR Direct Debit Pre-Notification Letter
Blitz Report In Action