GCS A&D Trial Balance Base Data Source

Description
Categories: BI Publisher
Application: Financial Consolidation Hub (Obsolete)
Source:
Short Name: GCS_AD_TB_SOURCE_BASE
DB package:
  SELECT 
         flv.meaning category_name,
         to_char(treatments.transaction_date,'DD-MON-YYYY') transaction_date,
         ght.hierarchy_name hierarchy_name,
         fet1.entity_name consolidation_entity_name,
         fet2.entity_name child_entity_name,
         treatments.total_consideration,
         treatments.entry_name,
         treatments.description,
		 fct.NAME currency_name,
		 fnd_currency.safe_get_format_mask(fcb.currency_code,50) format_mask
   FROM 
         fnd_lookup_values flv,
         fem_entities_tl fet1,
         fem_entities_tl fet2,
         gcs_entity_cons_attrs geca,
         gcs_hierarchies_tl ght,
         fnd_currencies_tl fct,
         fnd_currencies fcb,
         (select gtt_to.treatment_id to_treatment_id,
                 gtt_from.treatment_id from_treatment_id,
                 gtt_to.treatment_name to_treatment,
                 gtt_from.treatment_name from_treatment,
                 fcr.status_code,
                 gat.post_cons_relationship_id,
                 gat.pre_cons_relationship_id,
                 gat.intermediate_treatment_id,
                 gat.transaction_type_code,
                 gat.total_consideration,
                 fcr.request_id,
                 gat.ad_transaction_id,
                 gat.transaction_date,
                 nvl(gcr.parent_entity_id, gcr_pre.parent_entity_id) parent_entity_id,
                 nvl(gcr.child_entity_id, gcr_pre.child_entity_id) child_entity_id,
                 nvl(gcr.hierarchy_id, gcr_pre.hierarchy_id) hierarchy_id,
                 NVL (gcr.ownership_percent, 0) to_percent,
                 NVL (gcr_pre.ownership_percent, 0) from_percent,
                 geh.entry_name entry_name,
                 geh.description,
		 geh.rule_id
          from   gcs_treatments_tl gtt_from,
                 gcs_treatments_tl gtt_to,
                 gcs_ad_transactions gat,
                 gcs_entry_headers geh,
                 fnd_concurrent_requests fcr,
                 gcs_cons_relationships gcr,
                 gcs_cons_relationships gcr_pre
          where 
                 gtt_from.LANGUAGE = userenv('lang') AND
                 gtt_to.LANGUAGE = userenv('lang') AND
                 gat.assoc_entry_id = geh.entry_id AND
                 gat.request_id = fcr.request_id (+) AND
                 gat.ad_transaction_id = :pXmlFileId AND
                 gat.post_cons_relationship_id = gcr.cons_relationship_id (+) AND
                 gat.pre_cons_relationship_id = gcr_pre.cons_relationship_id(+) AND
                 nvl(gcr.treatment_id, gat.intermediate_treatment_id) = gtt_to.treatment_id AND
                 nvl(gcr_pre.treatment_id, gat.intermediate_treatment_id) = gtt_from.treatment_id 
		 ) treatments,  	   
		 (select grb.to_treatment_id, 
		         grb.from_treatment_id,  	   		   
		         grb.rule_id, grt.rule_name, 
		  		 grb.transaction_type_code  	   
		  from   gcs_elim_rules_tl grt,         
		         gcs_elim_rules_b grb  	   
		  where  grt.LANGUAGE =userenv('lang') 
          AND
                 grb.rule_id = grt.rule_id
         ) rules   
   WHERE treatments.to_treatment_id = rules.to_treatment_id (+) AND
         treatments.from_treatment_id = rules.from_treatment_id (+) AND
	 treatments.rule_id = rules.rule_id (+) AND
         rules.transaction_type_code (+)= treatments.transaction_type_code AND
         fet1.entity_id = treatments.parent_entity_id AND
         fet1.LANGUAGE = userenv('lang') AND
         fet2.entity_id = treatments.child_entity_id AND
         fet2.LANGUAGE = userenv('lang') AND
         geca.entity_id = treatments.parent_entity_id AND
         geca.hierarchy_id = ght.hierarchy_id AND
         ght.language = userenv('lang') AND
         geca.currency_code = fcb.currency_code AND
         treatments.hierarchy_id = ght.hierarchy_id AND
         treatments.transaction_type_code = flv.lookup_code AND
         flv.lookup_type = 'TRANSACTION_TYPE_CODE' AND
         flv.LANGUAGE = userenv('lang') AND
         flv.view_application_id = 266 AND
         fct.currency_code = fcb.currency_code AND
         fct.LANGUAGE = userenv('lang')
Ask a question