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) |
