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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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