GL Chart of Accounts - Mapping Rules

Description

Application: General Ledger
Source: Chart of Accounts - Mapping Rules (XML)
Short Name: GLXCOCRR_XML
DB package: GL_GLXCOCRR_XMLP_PKG

GL Chart of Accounts - Mapping Rules and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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