GCS Intercompany Matching Data Source for Consolidation Entities

Description
Categories: BI Publisher
Application: Financial Consolidation Hub (Obsolete)
Source:
Short Name: GCS_CONS_INTER_COMP_SOURCE
DB package: GCS_XML_DT_UTILITY_PKG
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