SELECT
FSP.segment_num SEGMENT_NUM,
FSP.segment_name TARGET_SEGMENT,
decode(max(CSM.segment_map_type),
'', 'N',
'C', 'C',
'S', 'S',
'R', 'R',
'P', 'R',
'V', 'R',
'U', 'R',
'N')
ACTION,
decode(CSM.segment_map_type,
'', '-',
'S', CSM.single_value,
FSS.segment_name) SOURCE_SEGMENT,
decode(CSM.segment_map_type,
'S', '',
CSM.single_value) TARGET_VALUE,
decode(CSM.segment_map_type,
'R', 'D',
'P', 'D',
'V', 'S',
'U', 'S',
'')
TRANSFER_LEVEL,
decode(CSM.segment_map_type,
'R', 'D',
'P', 'P',
'V', 'P',
'U', 'R',
'')
TRANSFER_LEVEL_USING,
CSM.parent_rollup_value TARGET_ROLLUP_VALUE,
CRR.child_flex_value_low SEGMENT_RANGE_FROM,
CRR.child_flex_value_high SEGMENT_RANGE_TO
FROM
GL_CONS_ROLLUP_RANGES_V CRR,
GL_COA_MAPPINGS GC,
FND_ID_FLEX_SEGMENTS FSS,
GL_CONS_SEGMENT_MAP CSM,
FND_ID_FLEX_SEGMENTS FSP
WHERE
FSP.application_id = 101
AND FSP.id_flex_code = 'GL#'
AND FSP.id_flex_num = :TO_CHART_OF_ACCOUNTS_ID
AND FSP.enabled_flag = 'Y'
AND CSM.to_value_set_id(+) = FSP.flex_value_set_id
AND CSM.to_application_column_name(+) = FSP.application_column_name
AND csm.coa_mapping_id = GC.coa_mapping_id
AND GC.coa_mapping_id = :P_COA_MAPPING_ID
AND FSS.application_id(+) = 101
AND FSS.id_flex_code(+) = 'GL#'
AND FSS.id_flex_num(+) = :FROM_CHART_OF_ACCOUNTS_ID
AND FSS.enabled_flag(+) = 'Y'
AND FSS.application_column_name(+) = CSM.from_application_column_name
AND CRR.segment_map_id(+) = decode(CSM.segment_map_type,
'R', CSM.segment_map_id,
'U', CSM.segment_map_id,
-1)
AND CRR.parent_flex_value(+) = decode(CSM.segment_map_type,
'R', CSM.single_value,
'U', CSM.single_value,
'P', CSM.parent_rollup_value,
'-1')
AND CRR.flex_value_set_id(+) = decode(CSM.segment_map_type,
'P', CSM.from_value_set_id,
-1)
GROUP BY FSP.segment_num, FSP.segment_name, FSS.segment_name, CSM.single_value, CSM.segment_map_type, CSM.segment_map_type,
CSM.parent_rollup_value, CRR.child_flex_value_low, CRR.child_flex_value_high
order by 1, 3, 4, 5, 6, 7, 8 |