select null C_FLEXDATAAB,
decode(upper(:P_PERIOD_TYPE),
'PATD', gdb.period_average_to_date_num,
'YATD', gdb.year_average_to_date_num,
'QATD', gdb.quarter_average_to_date_num,
'EOD', gdb.end_of_date_balance_num,
0) from_amountAB,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FlexfieldAB,
fnd_flex_xml_publisher_apis.process_kff_combination_1('description', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'FULL_DESCRIPTION') DescriptionAB
from
gl_code_combinations GLCC,
gl_daily_balances_v gdb,
gl_period_statuses gps
where (:P_PERIOD_TYPE = 'PATD' or
:P_PERIOD_TYPE = 'QATD' or
:P_PERIOD_TYPE = 'YATD' or
:P_PERIOD_TYPE = 'EOD')
AND NOT EXISTS
( SELECT 'Consolidated Detail Accounts Exists'
FROM GL_CONSOLIDATION_AUDIT GLCA,
GL_CODE_COMBINATIONS GLCC1
WHERE GLCA.consolidation_id = :P_CONSOLIDATION_ID
AND GLCA.from_period_name = :P_PERIOD_NAME
AND GLCA.average_consolidation_flag = :P_Y
AND GLCA.actual_flag = :P_A
AND GLCA.from_code_combination_id = GLCC1.code_combination_id
AND GLCC1.summary_flag = :P_N
AND GLCC1.code_combination_id = GLCC.code_combination_id
AND :PZ = 'ZZ')
AND NOT EXISTS
( SELECT 'Detail Accounts consolidated through summary accounts exists'
FROM GL_CONSOLIDATION_AUDIT GLCA,
GL_CODE_COMBINATIONS GLCC2,
GL_ACCOUNT_HIERARCHIES GAH
WHERE GLCA.consolidation_id = :P_CONSOLIDATION_ID
AND GLCA.from_period_name = :P_PERIOD_NAME
AND GLCA.average_consolidation_flag = 'Y'
AND GLCA.actual_flag = 'A'
AND GLCA.from_code_combination_id = GLCC2.code_combination_id
AND GLCC2.summary_flag = 'Y'
AND GLCC2.code_combination_id = GAH.summary_code_combination_id
AND GAH.detail_code_combination_id = GLCC.code_combination_id)
and GLCC.chart_of_accounts_id = to_number(:STRUCT_NUM)
and gdb.code_combination_id = glcc.code_combination_id
and gdb.actual_flag = 'A'
and gdb.period_name = :P_PERIOD_NAME
and gdb.currency_type in ('T', 'O', 'U')
and gdb.currency_code = :ConsCurrencyCode
and gdb.ledger_currency = :FromCurrencyCode
and gdb.template_id is NULL
and gdb.ledger_id = :SubsidLedgerId
and gdb.period_set_name = :PeriodSetName
and gps.application_id = 101
and gps.ledger_id = gdb.ledger_id
and gps.period_name = gdb.period_name
and gps.end_date = gdb.accounting_date
and gps.period_type = gdb.period_type
and &P_Accounts_ADB
and &P_BALJOINAB
order by
&P_ORDERBY
--C_FLEXDATAAB,from_amountAB,&P_ORDERBY |