CTO_MATCH_V

Details

• Schema: FUSION

• Object owner: CTO

• Object type: VIEW

Columns

Name
BASE_CONFIG_ITEM_ID CONFIG_ITEM_ID BASE_MODEL_ID PARENT_INVENTORY_ITEM_ID CONFIG_BASE_MODEL_ID OPTION_CLASS_ID INVENTORY_ITEM_ID HEADER_ID LINE_ID PARENT_LINE_ID LINKED_ATO_LINE_ID SUB_ITEM_TYPE UNIT_QTY UNIT_UOM CREATION_DATE ITEM_HIERARCHY MATCH_QTY ROOT_HEADER_ID HEADER_PATH PARENT_ITEM_PATH PARENT_SUB_ITEM_TYPE PARENT_MATCH_QTY ATO_QTY

Query

SQL_Statement

SELECT /*+ opt_param('_recursive_with_branch_iterations',1) */ MH.CONFIG_ITEM_ID BASE_CONFIG_ITEM_ID, MT.CONFIG_ITEM_ID, MH.BASE_MODEL_ID BASE_MODEL_ID, DECODE (LEVELN , 1, MT.INVENTORY_ITEM_ID, PARENT_INVENTORY_ITEM_ID) PARENT_INVENTORY_ITEM_ID, DECODE (LEVELN , 1, MT.BASE_MODEL_ID, CONFIG_BASE_MODEL_ID) CONFIG_BASE_MODEL_ID, MT.OPTION_CLASS_ID, MT.INVENTORY_ITEM_ID, MT.HEADER_ID, MT.LINE_ID, MT.PARENT_LINE_ID, MT.LINKED_ATO_LINE_ID, MT.SUB_ITEM_TYPE, MT.UNIT_QTY,MT.UNIT_UOM, MH.CREATION_DATE, INVENTORY_ITEM_ID_P ITEM_HIERARCHY, UNIT_QTY MATCH_QTY, ROOT_HEADER_ID, HEADER_PATH, RTRIM(REPLACE(LTRIM(PARENT_ITEM_PATH, '> '), '> >', '>'),'> ') PARENT_ITEM_PATH ,PARENT_SUB_ITEM_TYPE,PARENT_MATCH_QTY,ATO_QTY FROM CTO_MATCH_HEADERS MH, (WITH CONFG_ITEM_N ( config_item_id, BASE_MODEL_ID,INVENTORY_ITEM_ID_P,BASE_MODEL_ID_P,parent_line_id,LEVELN ,ROOT_HEADER_ID,header_id,line_id,PARENT_INVENTORY_ITEM_ID,CONFIG_BASE_MODEL_ID,LINKED_ATO_LINE_ID,INVENTORY_ITEM_ID,SUB_ITEM_TYPE,UNIT_UOM,UNIT_QTY,CREATION_DATE,OPTION_CLASS_ID,HEADER_PATH,PARENT_SUB_ITEM_TYPE,PARENT_MATCH_QTY,PARENT_ITEM_PATH,ITEM_ID,ATO_QTY) AS (SELECT MH.CONFIG_ITEM_ID CONFIG_ITEM_ID, MH.BASE_MODEL_ID BASE_MODEL_ID, '/' || MH.CONFIG_ITEM_ID INVENTORY_ITEM_ID_P, '/' || BASE_MODEL_ID BASE_MODEL_ID_P, parent_line_id, 1 AS LEVELN, MH.HEADER_ID ROOT_HEADER_ID, ML.HEADER_ID, ML.LINE_ID, to_number(NULL) PARENT_INVENTORY_ITEM_ID, to_number(NULL) CONFIG_BASE_MODEL_ID, to_number(NULL) LINKED_ATO_LINE_ID, MH.CONFIG_ITEM_ID INVENTORY_ITEM_ID, ML.SUB_ITEM_TYPE, ML.UNIT_UOM, ML.UNIT_QTY, ML.CREATION_DATE, to_number(NULL) OPTION_CLASS_ID, '/' ||ML.HEADER_ID HEADER_PATH, NULL PARENT_SUB_ITEM_TYPE, NULL PARENT_MATCH_QTY, NULL PARENT_ITEM_PATH, ML.INVENTORY_ITEM_ID ITEM_ID , 1 ATO_QTY FROM CTO_MATCH_HEADERS MH , CTO_MATCH_LINES ML WHERE mh.header_id =ml.header_id AND PARENT_LINE_ID IS NULL UNION ALL SELECT DECODE(CONFG_ITEM_N.LINKED_ATO_LINE_ID, NULL, MH.CONFIG_ITEM_ID, CONFG_ITEM_N.CONFIG_ITEM_ID) AS CONFIG_ITEM_ID, MH.BASE_MODEL_ID BASE_MODEL_ID, CONFG_ITEM_N.INVENTORY_ITEM_ID_P ||'/' || DECODE(ML.LINKED_ATO_LINE_ID, NULL, DECODE(ML.SUB_ITEM_TYPE, 1, MH.CONFIG_ITEM_ID, ML.INVENTORY_ITEM_ID), NULL) INVENTORY_ITEM_ID_P, CONFG_ITEM_N.BASE_MODEL_ID_P || '/' || MH.BASE_MODEL_ID BASE_MODEL_ID_P, DECODE(ML.PARENT_LINE_ID, NULL, CONFG_ITEM_N.PARENT_LINE_ID, ML.PARENT_LINE_ID) PARENT_LINE_ID, CONFG_ITEM_N.LEVELN+1 AS LEVELN, CONFG_ITEM_N.ROOT_HEADER_ID ROOT_HEADER_ID, ML.HEADER_ID, ML.LINE_ID, DECODE(CONFG_ITEM_N.LINKED_ATO_LINE_ID,NULL,CONFG_ITEM_N.INVENTORY_ITEM_ID,CONFG_ITEM_N.PARENT_INVENTORY_ITEM_ID) PARENT_INVENTORY_ITEM_ID, CONFG_ITEM_N.BASE_MODEL_ID CONFIG_BASE_MODEL_ID, ML.LINKED_ATO_LINE_ID, DECODE(ML.PARENT_LINE_ID, NULL, MH.CONFIG_ITEM_ID, ML.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, ML.SUB_ITEM_TYPE, ML.UNIT_UOM, ML.UNIT_QTY, ML.CREATION_DATE, DECODE (CONFG_ITEM_N.SUB_ITEM_TYPE, 2, CONFG_ITEM_N.INVENTORY_ITEM_ID, NULL) OPTION_CLASS_ID, CONFG_ITEM_N.HEADER_PATH ||'/' ||ML.HEADER_ID HEADER_PATH, CONFG_ITEM_N.SUB_ITEM_TYPE PARENT_SUB_ITEM_TYPE, CONFG_ITEM_N.UNIT_QTY PARENT_MATCH_QTY, CONFG_ITEM_N.PARENT_ITEM_PATH || '> ' || DECODE(CONFG_ITEM_N.LINKED_ATO_LINE_ID, NULL, CONFG_ITEM_N.ITEM_ID, NULL) PARENT_ITEM_PATH, ML.INVENTORY_ITEM_ID ITEM_ID, NVL2(ML.LINKED_ATO_LINE_ID,ML.UNIT_QTY*CONFG_ITEM_N.ATO_QTY,CONFG_ITEM_N.ATO_QTY) ATO_QTY FROM CTO_MATCH_HEADERS MH , CTO_MATCH_LINES ML, CONFG_ITEM_N WHERE mh.header_id = ml.header_id AND (CONFG_ITEM_N.line_id = ml.parent_line_id OR CONFG_ITEM_N.LINKED_ATO_LINE_ID=ML.LINE_ID) ) SELECT * FROM CONFG_ITEM_N) MT WHERE MH.HEADER_ID = MT.ROOT_HEADER_ID