GL Reconciliation - Unreconciled Transactions

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Reconciliation - Unreconciled Transactions (XML)
Short Name: GLXRCUNR_XML
DB package: GL_GLXRCTRS_XMLP_PKG
SELECT	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex', 'SQLGL', 'GL#', C.CHART_OF_ACCOUNTS_ID, NULL, C.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')       C_FLEX,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acc_desc', 'SQLGL', 'GL#', C.CHART_OF_ACCOUNTS_ID, NULL, C.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION')   C_ACC_DESC,
		c.code_combination_id  CODE_COMBINATION_ID,
	 	c.chart_of_accounts_id CHART_OF_ACCOUNTS_ID, 
		b.name BATCH,
		h.name	JOURNAL,
		h.period_name	PERIOD,
		l.effective_date	EFFECTIVE_DATE,
		h.currency_code       CURRENCY_CODE,
		l.entered_dr	ENTERED_DR,
		l.entered_cr	ENTERED_CR,
		l.accounted_dr	ACCOUNTED_DR,
		l.accounted_cr	ACCOUNTED_CR,
		l.je_header_id           JE_HEADER_ID,
		r.je_line_num            JE_LINE_NUM,
                               NVL(r.jgzz_recon_id,0)	RECONCILIATION_ID,
		NVL(r.jgzz_recon_date ,SYSDATE)  RECONCILIATION_DATE,
	 	r.jgzz_recon_ref  REFERENCE
	FROM	gl_je_lines_recon r,
 	                gl_je_lines l,
		gl_je_headers h,
		gl_je_batches b,
		gl_period_statuses ps,
		gl_code_combinations c
	WHERE	c.code_combination_id = l.code_combination_id
	AND	r.je_header_id = l.je_header_id 
	AND	r.je_line_num = l.je_line_num
	AND	r.ledger_id = :P_LEDGER_ID
	AND	l.ledger_id = :P_LEDGER_ID
	AND	l.je_header_id = h.je_header_id
	AND	h.je_batch_id = b.je_batch_id
	AND	l.period_name = ps.period_name
	AND	ps.application_id = 101
	AND     	ps.ledger_id = :P_LEDGER_ID
	AND	c.chart_of_accounts_id = :C_STRUCT_NUM
	                &C_WHERE_REC_STATUS
	AND  	l.status = 'P'
	AND	h.status = 'P'
                AND         :P_LEDGER_CURRENCY IS NOT NULL
	AND	&C_WHERE_FLEX
		&C_WHERE_CURRENCY
		&C_WHERE_DAS
	                &C_JGZZ_RECON_FLAG 
                AND l.effective_date
                       BETWEEN  greatest(:CP_PERIOD_START,NVL(:CP_start_date,:CP_PERIOD_START))        
                       AND  least(:CP_PERIOD_END,NVL(:CP_end_date,:CP_PERIOD_END))  
            	Order by 1, h.currency_code, l.effective_date,h.name,l.je_line_num
Parameter Name SQL text Validation
Reconcile
 
Charts Of Accounts ID
 
Number
Access Set Id
 
Number
Account To
 
Account From
 
End Date
 
Date
Start Date
 
Date
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Currency
 
LOV Oracle
Ledger
 
LOV Oracle