GCS Data Submission Trial Balance Base Data Source

Description
Categories: BI Publisher
Application: Financial Consolidation Hub (Obsolete)
Source:
Short Name: GCS_DS_TB_SOURCE_BASE
DB package: GCS_XML_DT_UTILITY_PKG
SELECT  gdsd.load_id,
					   gdsd.load_name,
					   gdsd.entity_id,
					   fet.entity_name,
					   gdtct.data_type_name balance_type,
					   gdsd.amount_type_code,
					   amt.meaning amount_type_name,
					   gdsd.currency_type_code,
					   curr.meaning curr_type_name,
					   gdsd.currency_code,
					   fct.name currency_name,
					   gdsd.cal_period_id,
					   fcpt.cal_period_name,
					   gdsd.measure_type_code,
					   measure.meaning measure_type_name,
					   to_char(gdsd.start_time,'DD-MON-YYYY HH24.MI.SS AM') start_time,
					   to_char(gdsd.end_time,'DD-MON-YYYY HH24.MI.SS AM') end_time,
					   gdsd.status_code,
					   gdsd.balance_type_code,
					   flt.ledger_id,
					   flt.ledger_name,
					   gea.source_system_code,
					   fla_comp.dim_attribute_varchar_member entity_currency_code,
					   fla_gvsc.dim_attribute_numeric_member sub_global_vs_combo_id,
					   fnd_currency.safe_get_format_mask(gdsd.currency_code,50) format_mask,
					   gcs_utility_pkg.get_associated_value_set_id('COMPANY_COST_CENTER_ORG_ID') org_value_set_id, gdtcb.data_type_code 
				  FROM gcs_data_sub_dtls     gdsd,
					   fem_entities_tl       fet,
					   fnd_currencies_tl     fct,
					   fem_cal_periods_tl    fcpt,
					   gcs_data_type_codes_b gdtcb,
					   gcs_data_type_codes_tl gdtct,
					   fnd_lookup_values     amt,
					   fnd_lookup_values     curr,
					   fnd_lookup_values     measure,
					   fem_ledgers_tl        flt,
					   gcs_entities_attr     gea,
					   fem_ledgers_attr      fla_comp,
					   fem_ledgers_attr      fla_gvsc,
					   fem_cal_periods_attr  fcpa_end_date
				 WHERE gdsd.load_id = :pXmlFileId
				   AND gdsd.entity_id = fet.entity_id
				   AND fet.language = :pLanguageCode
				   AND gdsd.currency_code = fct.currency_code(+)
				   AND fct.language(+) = :pLanguageCode
				   AND gdsd.cal_period_id = fcpt.cal_period_id
				   AND fcpt.language = :pLanguageCode
				   AND gdsd.balance_type_code = gdtcb.data_type_code
				   AND gdtct.language = :pLanguageCode
				   AND gdtct.data_type_id = gdtcb.data_type_id
				   AND gdsd.amount_type_code = amt.lookup_code
				   AND amt.lookup_type = 'GCS_AMOUNT_TYPE_CODES'
				   AND amt.LANGUAGE = :pLanguageCode
				   AND amt.view_application_id = 266
				   AND gdsd.currency_type_code = curr.lookup_code
				   AND curr.lookup_type = 'GCS_CURRENCY_TYPES'
				   AND curr.LANGUAGE = :pLanguageCode
				   AND curr.view_application_id = 266
				   AND gdsd.measure_type_code = measure.lookup_code
				   AND measure.lookup_type = 'GCS_MEASURE_TYPE_CODES'
				   AND measure.LANGUAGE = :pLanguageCode
				   AND measure.view_application_id = 266
				   AND gea.entity_id = gdsd.entity_id
				   AND gea.data_type_code = gdsd.balance_type_code
				   AND flt.ledger_id = gea.ledger_id
				   AND flt.language = :pLanguageCode
				   AND fla_comp.ledger_id = gea.ledger_id
				   AND fla_comp.attribute_id = :pLedgerCurrAttr
				   AND fla_comp.version_id = :pLedgerCurrVersion
				   AND fla_gvsc.ledger_id = gea.ledger_id
				   AND fla_gvsc.attribute_id = :pLedgerVsComboAttr
				   AND fla_gvsc.version_id = :pLedgerVsComboVersion
				   AND gdsd.cal_period_id = fcpa_end_date.cal_period_id
				   AND fcpa_end_date.attribute_id = :pCalPeriodEndDateAttr
				   AND fcpa_end_date.version_id = :pCalPeriodEndDateVersion
				   AND fcpa_end_date.date_assign_value between gea.effective_start_date and nvl(gea.effective_end_date, fcpa_end_date.date_assign_value)
Ask a question