VIEW: APPS.GHG_SOURCE_REPORT_COMB_V

Source

View Text - Preformatted

SELECT src.org_id, fac.SOURCE_COMBINATION_ID, fac.SOURCE_COMBINATION_CODE, nvl(fac.SOURCE_COMBINATION_DESCRIPTION, fac.SOURCE_COMBINATION_CODE) SOURCE_COMB_DESCRIPTION, src.source_id SOURCE_INFO_CODE, src.source_name SOURCE_INFO_DESC, rcc.segment1 GRANDPARENT_SOURCE_CODE, (SELECT fltl.description FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl , fnd_flex_values val , fnd_flex_values_tl fltl WHERE idf.ID_FLEX_CODE = 'GHG#' AND idf.id_flex_num = rcc.chart_of_accounts_id and idf.flex_value_set_id = val.flex_value_set_id and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE and idf.application_column_name = tl.APPLICATION_COLUMN_NAME AND idf.id_flex_num = tl.id_flex_num and val.flex_value_id = fltl.flex_value_id and val.flex_value = rcc.segment1 and tl.language= (SELECT USERENV('LANG') from dual) and fltl.language= (SELECT USERENV('LANG') from dual) and idf.application_column_name = 'SEGMENT1' ) GRANDPARENT_SOURCE_DESC, rcc.segment2 PARENT_SOURCE_CODE, (SELECT fltl.description FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl , fnd_flex_values val , fnd_flex_values_tl fltl WHERE idf.ID_FLEX_CODE = 'GHG#' AND idf.id_flex_num = rcc.chart_of_accounts_id and idf.flex_value_set_id = val.flex_value_set_id and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE and idf.application_column_name = tl.APPLICATION_COLUMN_NAME AND idf.id_flex_num = tl.id_flex_num and val.flex_value_id = fltl.flex_value_id and val.flex_value = rcc.segment2 and tl.language= (SELECT USERENV('LANG') from dual) and fltl.language= (SELECT USERENV('LANG') from dual) and idf.application_column_name = 'SEGMENT2' ) PARENT_SOURCE_DESC, rcc.segment3 SOURCE_TYPE_CODE, (SELECT fltl.description FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl , fnd_flex_values val , fnd_flex_values_tl fltl WHERE idf.ID_FLEX_CODE = 'GHG#' AND idf.id_flex_num = rcc.chart_of_accounts_id and idf.flex_value_set_id = val.flex_value_set_id and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE and idf.application_column_name = tl.APPLICATION_COLUMN_NAME AND idf.id_flex_num = tl.id_flex_num and val.flex_value_id = fltl.flex_value_id and val.flex_value = rcc.segment3 and tl.language= (SELECT USERENV('LANG') from dual) and fltl.language= (SELECT USERENV('LANG') from dual) and idf.application_column_name = 'SEGMENT3' ) SOURCE_TYPE_DESC,src.energy_type from GHG_SOURCES_all src , GHG_SOURCE_COMBINATIONS_ALL fac , GHG_REPORT_COMBINATIONS rcc WHERE fac.source_id = src.source_id and fac.code_combination_id = rcc.code_combination_id (+)

View Text - HTML Formatted

SELECT SRC.ORG_ID
, FAC.SOURCE_COMBINATION_ID
, FAC.SOURCE_COMBINATION_CODE
, NVL(FAC.SOURCE_COMBINATION_DESCRIPTION
, FAC.SOURCE_COMBINATION_CODE) SOURCE_COMB_DESCRIPTION
, SRC.SOURCE_ID SOURCE_INFO_CODE
, SRC.SOURCE_NAME SOURCE_INFO_DESC
, RCC.SEGMENT1 GRANDPARENT_SOURCE_CODE
, (SELECT FLTL.DESCRIPTION
FROM FND_ID_FLEX_SEGMENTS IDF
, FND_ID_FLEX_SEGMENTS_TL TL
, FND_FLEX_VALUES VAL
, FND_FLEX_VALUES_TL FLTL
WHERE IDF.ID_FLEX_CODE = 'GHG#'
AND IDF.ID_FLEX_NUM = RCC.CHART_OF_ACCOUNTS_ID
AND IDF.FLEX_VALUE_SET_ID = VAL.FLEX_VALUE_SET_ID
AND IDF.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND IDF.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
AND IDF.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND VAL.FLEX_VALUE_ID = FLTL.FLEX_VALUE_ID
AND VAL.FLEX_VALUE = RCC.SEGMENT1
AND TL.LANGUAGE= (SELECT USERENV('LANG')
FROM DUAL)
AND FLTL.LANGUAGE= (SELECT USERENV('LANG')
FROM DUAL)
AND IDF.APPLICATION_COLUMN_NAME = 'SEGMENT1' ) GRANDPARENT_SOURCE_DESC
, RCC.SEGMENT2 PARENT_SOURCE_CODE
, (SELECT FLTL.DESCRIPTION
FROM FND_ID_FLEX_SEGMENTS IDF
, FND_ID_FLEX_SEGMENTS_TL TL
, FND_FLEX_VALUES VAL
, FND_FLEX_VALUES_TL FLTL
WHERE IDF.ID_FLEX_CODE = 'GHG#'
AND IDF.ID_FLEX_NUM = RCC.CHART_OF_ACCOUNTS_ID
AND IDF.FLEX_VALUE_SET_ID = VAL.FLEX_VALUE_SET_ID
AND IDF.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND IDF.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
AND IDF.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND VAL.FLEX_VALUE_ID = FLTL.FLEX_VALUE_ID
AND VAL.FLEX_VALUE = RCC.SEGMENT2
AND TL.LANGUAGE= (SELECT USERENV('LANG')
FROM DUAL)
AND FLTL.LANGUAGE= (SELECT USERENV('LANG')
FROM DUAL)
AND IDF.APPLICATION_COLUMN_NAME = 'SEGMENT2' ) PARENT_SOURCE_DESC
, RCC.SEGMENT3 SOURCE_TYPE_CODE
, (SELECT FLTL.DESCRIPTION
FROM FND_ID_FLEX_SEGMENTS IDF
, FND_ID_FLEX_SEGMENTS_TL TL
, FND_FLEX_VALUES VAL
, FND_FLEX_VALUES_TL FLTL
WHERE IDF.ID_FLEX_CODE = 'GHG#'
AND IDF.ID_FLEX_NUM = RCC.CHART_OF_ACCOUNTS_ID
AND IDF.FLEX_VALUE_SET_ID = VAL.FLEX_VALUE_SET_ID
AND IDF.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND IDF.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
AND IDF.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND VAL.FLEX_VALUE_ID = FLTL.FLEX_VALUE_ID
AND VAL.FLEX_VALUE = RCC.SEGMENT3
AND TL.LANGUAGE= (SELECT USERENV('LANG')
FROM DUAL)
AND FLTL.LANGUAGE= (SELECT USERENV('LANG')
FROM DUAL)
AND IDF.APPLICATION_COLUMN_NAME = 'SEGMENT3' ) SOURCE_TYPE_DESC
, SRC.ENERGY_TYPE
FROM GHG_SOURCES_ALL SRC
, GHG_SOURCE_COMBINATIONS_ALL FAC
, GHG_REPORT_COMBINATIONS RCC
WHERE FAC.SOURCE_ID = SRC.SOURCE_ID
AND FAC.CODE_COMBINATION_ID = RCC.CODE_COMBINATION_ID (+)