GL Consolidation - Unmapped Subsidiary Accounts

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Consolidation - Unmapped Subsidiary Accounts (XML)
Short Name: GLXUSA_XML
DB package: GL_GLXUSA_XMLP_PKG
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
Parameter Name SQL text Validation
Amount Type
 
LOV Oracle
Subsidiary Period
 
LOV Oracle
Consolidation Definition
 
LOV Oracle
Ask a question