JL Mexican Journal Entries - XML File

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