GCS Intercompany Matching Base Data Source for Consolidation Entities

Description
Categories: BI Publisher
Application: Financial Consolidation Hub (Obsolete)
Source:
Short Name: GCS_CONS_INTER_COMP_SOURCE_BASE
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*/
       /*selectLiteral*/
       /*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*/ 
       /*fromLiteral*/ 
       /*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*/
    /*whereLiteral*/
    /*endWhereClause*/
 ORDER BY hierarchy_name,
          geh.entry_id,
          rule_name
Ask a question