JL Chilean General Ledger Cash Ledger - draft

Description
Categories: BI Publisher
Application: Latin America Localizations
Source: Chilean General Ledger Cash Ledger (XML) - Not Supported: Reserved For Future Use
Short Name: JLCLGCAL_XML
DB package: JL_JLCLGCAL_XMLP_PKG
SELECT 
       	nvl(src.user_je_source_name, '**********')
                                       		SOURCE,
	src.je_source_name		SOURCE_ID,
       	ps.period_name                	PERIOD_NAME,
                ps.effective_period_num           PERIOD_EFF_NUM,
       	jeh.currency_code               	CURRENCY1,
	cc.code_combination_id	CCID,
	null		ACCOUNT,
  	TO_CHAR(jel.effective_date, 'DD-MON-YYYY')    EFFECTIVE_DATE,      
        	jel.description                 	DESCRIPTION,
        	jeb.name                       	BATCH_NAME,
        	jeh.name                        	HEADER_NAME,
        	jel.entered_dr                  	DEBITS,
        	jel.entered_cr                  	CREDITS,
	nvl(jel.reference_1,' ')               REFERENCE_1,
          	jel.reference_2                          REFERENCE_2,
          	jel.reference_3                          REFERENCE_3,
          	jel.reference_4                          REFERENCE_4,
          	nvl(jel.reference_5,' ')               REFERENCE_5,
          	jel.reference_6                           REFERENCE_6,
          	jel.reference_7                           REFERENCE_7,
          	jel.reference_8                           REFERENCE_8,
          	jel.reference_9                           REFERENCE_9,
          	jel.reference_10                         REFERENCE_10, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_DESC_ALL, 
	JL_JLCLGCAL_XMLP_PKG.c_doc_numformula(nvl ( src.user_je_source_name , '**********' ), jel.reference_6, jel.reference_4, nvl ( jel.reference_5 , ' ' ), jel.reference_8) C_Doc_Num, 
	JL_JLCLGCAL_XMLP_PKG.c_documentformula(src.je_source_name, jel.reference_10, jel.reference_9) C_Document
FROM    
	gl_code_combinations            	cc,
        	gl_je_lines                     	jel,
        	gl_je_headers                   	jeh,
        	gl_je_batches                   	jeb,
        	gl_sets_of_books                	sob,
	gl_period_statuses		ps,
        	gl_je_sources                   	src
WHERE      
               sob.set_of_books_id                   = :P_SET_OF_BOOKS_ID
AND	ps.set_of_books_id		= sob.set_of_books_id
AND	ps.application_id		= 101
AND	ps.effective_period_num	BETWEEN
		:START_EFFECTIVE_PERIOD_NUM AND
		:END_EFFECTIVE_PERIOD_NUM
AND	ps.closing_status 		IN ('C', 'O', 'P')
AND	ps.period_name		= jel.period_name
AND     	sob.chart_of_accounts_id         	= cc.chart_of_accounts_id
AND     	jel.code_combination_id         	= cc.code_combination_id
AND           jel.status || ''                           = 'P'
AND     	(jel.entered_dr != 0 OR jel.entered_cr != 0)
AND          jel.ledger_id                              = sob.set_of_books_id
AND     	jeh.je_header_id                	= jel.je_header_id
AND     	jeh.actual_flag                 	= 'A'
AND     	jeb.je_batch_id                 	= jeh.je_batch_id
AND          jeb.average_journal_flag            = 'N'
AND     	src.je_source_name              	= jeh.je_source
&P_DYNAMIC_WHERE
ORDER BY
	jeh.currency_code,
	ps.period_year,
	ps.period_num,
	TO_NUMBER(TO_CHAR(jel.effective_date, 'MM')),
	TO_NUMBER(TO_CHAR(jel.effective_date, 'DD'))
Parameter Name SQL text Validation
Chart of Accounts ID
 
LOV Oracle
GL_SET_OF_BOOKS_ID
 
Number
XLE_LEGAL_REPORTING_LEVEL
 
LOV Oracle
Print Legal Entity Information
 
LOV Oracle
Account Model
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Company Name
 
LOV Oracle