SELECT ghv.hierarchy_name hierarchy_name,
gcerd.child_entity_id entity_id,
geh.suspense_exceeded_flag suspense_exceeded_flag,
fcpv.cal_period_name calname,
geh.currency_code,
fcrtl.name currency,
fcov.company_cost_center_org_name organization,
fliv.line_item_name lineitem,
/*startSelectList*/ ffeb.financial_elem_name ,
fpb.product_name ,
fud1.user_dim1_name ,
/*endSelectList*/
fciv.company_cost_center_org_name intercompany,
gdtct.data_type_name,
fev.entity_name entity_name,
fev1.entity_name counter_entity_name,
fev2.entity_name cons_entity_name,
gicrv.rule_name rule_name,
gicrv.description description,
gel.description suspenseline,
gel.ytd_debit_balance_e debit,
gel.ytd_credit_balance_e credit,
gel.ytd_balance_e balance,
gcs_drilldown_util_pkg.url_encode(gel.rowid) rowids
FROM gcs_cons_eng_runs gcer,
gcs_cons_eng_run_dtls gcerd,
gcs_data_type_codes_b gdtcb,
gcs_data_type_codes_tl gdtct,
gcs_entry_headers geh,
gcs_entry_lines gel,
gcs_interco_rules_vl gicrv,
gcs_hierarchies_vl ghv,
fem_cal_periods_vl fcpv,
fem_entities_vl fev,
fem_entities_vl fev1,
fem_entities_vl fev2,
fnd_currencies_tl fcrtl,
fem_cctr_orgs_tl fcov,
fem_ln_items_tl fliv,
/*startFromList*/ fem_fin_elems_tl ffeb,
fem_products_tl fpb,
FEM_USER_DIM1_TL fud1,
/*endFromList*/
fem_cctr_orgs_tl fciv
WHERE gcer.hierarchy_id = :pHierarchy
AND gcer.cal_period_id = :pPeriod
AND gcer.balance_type_code = :pBalanceTypeCode
AND gcer.most_recent_flag = 'Y'
AND gcer.run_name = gcerd.run_name
AND gcer.run_entity_id = gcerd.consolidation_entity_id
AND gcerd.category_code IN ('INTERCOMPANY', 'INTRACOMPANY')
AND gcerd.consolidation_entity_id in (SELECT distinct parent_entity_id
FROM gcs_cons_relationships gcr
START WITH parent_entity_id = :pEntity
AND hierarchy_id = :pHierarchy
CONNECT BY prior child_entity_id = PARENT_ENTITY_ID
AND hierarchy_id = :pHierarchy)
AND fev.entity_id = gcerd.child_entity_id
AND fev1.entity_id = gcerd.contra_child_entity_id
AND fev2.entity_id = :pEntity
AND gdtcb.data_type_code = gcer.balance_type_code
AND gdtcb.data_type_id = gdtct.data_type_id
AND gdtct.language = userenv('lang')
AND gcerd.entry_id = geh.entry_id
AND geh.entry_id = gel.entry_id
&suspenseExFlagLiteral
AND ghv.hierarchy_id = :pHierarchy
AND gcerd.rule_id = gicrv.rule_id
&ruleIdLiteral
AND gcer.cal_period_id = fcpv.cal_period_id
AND geh.currency_code = fcrtl.currency_code
AND fcrtl.LANGUAGE = userenv('lang')
AND gel.company_cost_center_org_id = fcov.company_cost_center_org_id
AND fcov.LANGUAGE = userenv('lang')
AND fliv.line_item_id = gel.line_item_id
AND fliv.LANGUAGE = userenv('lang')
AND gel.intercompany_id = fciv.company_cost_center_org_id
AND fciv.LANGUAGE = userenv('lang')
/*startWhereClause*/ AND ffeb.financial_elem_id = gel.financial_elem_id
AND ffeb.language = :pLanguageCode
AND fpb.product_id = gel.product_id
AND fpb.language = :pLanguageCode
AND fud1.USER_DIM1_ID = gel.USER_DIM1_ID
AND fud1.language = :pLanguageCode
/*endWhereClause*/
ORDER BY hierarchy_name,
geh.entry_id,
rule_name |
