GCS Intercompany Matching Data Source

Description
Categories: BI Publisher
Application: Financial Consolidation Hub (Obsolete)
Source:
Short Name: GCS_INTER_COMP_SOURCE
DB package: GCS_XML_DT_UTILITY_PKG
SELECT ghv.hierarchy_name hierarchy_name,
       geh.suspense_exceeded_flag suspense_exceeded_flag,
       fcpv.cal_period_name calname,
       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,
       fev.entity_name entity_name,
       fev1.entity_name oper_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_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,
       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.most_recent_flag = 'Y'
  AND geh.currency_code     = fcrtl.currency_code
  AND fcrtl.LANGUAGE        = userenv('lang')
  AND gcer.hierarchy_id     = :phierarchy
  AND gcer.run_entity_id    = gcerd.consolidation_entity_id
  AND gcer.run_name         = gcerd.run_name
  AND gcerd.category_code IN('INTERCOMPANY', 'INTRACOMPANY')
  AND(gcerd.child_entity_id        = :pentity 
      OR 
      gcerd.contra_child_entity_id = :pentity)
  AND gcer.balance_type_code       = :pbalanceTypeCode
  &counterEntityIdLiteral
  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 = :pperiod
  AND gcer.cal_period_id = fcpv.cal_period_id
  AND fev.entity_id = decode(gcerd.child_entity_id, :pentity, gcerd.contra_child_entity_id, gcerd.child_entity_id)
  AND fev1.entity_id = :pentity
  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,
          entity_name,
          rule_name