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 |