CST_WO_COST_ELEMENTS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
CST_WORK_ORDER_ID COST_ORG_ID COST_BOOK_ID COST_ELEMENT_ID COST_ELEMENT_CODE COST_ELEMENT_TYPE WORK_CENTER_ID WORK_CENTER_NAME WORK_ORDER_OPERATION_ID OPERATION_NAME OUTPUT_COST BALANCE_IN_WIP VARIANCE_COST SCRAP_COST SCRAP_EXP_COST SCRAP_IN_INV_COST CURRENCY_CODE ANALYSIS_GROUP_ID ANALYSIS_CODE

Query

SQL_Statement

SELECT CWOC.CST_WORK_ORDER_ID, CWOC.COST_ORG_ID, CWOC.COST_BOOK_ID, CCEB.COST_ELEMENT_ID, CCEB.COST_ELEMENT_CODE, CCEB.COST_ELEMENT_TYPE, CWOO.WORK_CENTER_ID, WWCV.WORK_CENTER_NAME, CWOO.WORK_ORDER_OPERATION_ID, CASE WHEN CWO.WORK_ORDER_LESS_FLAG = 'Y' THEN WDOV.OPERATION_NAME ELSE WWOV.OPERATION_NAME END OPERATION_NAME, ABS( SUM( WIP_TXN_SIGN * QUANTITY * ( CASE WHEN WIP_COST_TYPE='PRODUCT' THEN UNIT_COST else 0 end)) ) as OUTPUT_COST, ABS( SUM( WIP_TXN_SIGN * QUANTITY * UNIT_COST ) - SUM(WIP_TXN_SIGN * QUANTITY * ( CASE WHEN WIP_COST_TYPE ='SCRAP' AND ABSORPTION_TYPE=1 THEN UNIT_COST else 0 END) ) ) AS BALANCE_IN_WIP, ABS( SUM( CWOC.QUANTITY * DECODE(CWOC.WIP_COST_TYPE,'VARIANCE',CWOC.UNIT_COST,0) * CWOC.WIP_TXN_SIGN )) AS VARIANCE_COST, ABS ( SUM( CWOC.QUANTITY * DECODE(CWOC.WIP_COST_TYPE,'SCRAP',CWOC.UNIT_COST,0) * CWOC.WIP_TXN_SIGN ) ) AS SCRAP_COST, ABS( SUM( WIP_TXN_SIGN * QUANTITY * ( CASE WHEN WIP_COST_TYPE ='SCRAP' AND ABSORPTION_TYPE=2 THEN UNIT_COST else 0 end)) )as SCRAP_EXP_COST, ABS ( SUM( WIP_TXN_SIGN * QUANTITY * ( CASE when WIP_COST_TYPE ='SCRAP' AND ABSORPTION_TYPE=1 THEN UNIT_COST else 0 END))) AS SCRAP_IN_INV_COST, CWOC.CURRENCY_CODE, -1 as ANALYSIS_GROUP_ID, null as ANALYSIS_CODE FROM CST_WORK_ORDER_COSTS CWOC, CST_WORK_ORDERS CWO, CST_COST_ELEMENTS_VL CCEB, CST_WORK_ORDER_OPERATIONS CWOO, WIS_WORK_CENTERS_VL WWCV, WIE_WO_OPERATIONS_V WWOV, WIS_WD_OPERATIONS_V WDOV WHERE CWO.CST_WORK_ORDER_ID = CWOC.CST_WORK_ORDER_ID AND CWOC.COST_ELEMENT_ID = CCEB.COST_ELEMENT_ID AND CWOC.CST_WORK_ORDER_ID = CWOO.CST_WORK_ORDER_ID AND CWOC.CST_WORK_ORDER_OPERATION_ID = CWOO.CST_WORK_ORDER_OPERATION_ID AND CWOO.WORK_CENTER_ID = WWCV.WORK_CENTER_ID and CWOO.WORK_ORDER_OPERATION_ID = WWOV.WO_OPERATION_ID(+) and CWOO.WORK_ORDER_OPERATION_ID = WDOV.WD_OPERATION_ID(+) group by CWOC.CST_WORK_ORDER_ID, CWOC.COST_ORG_ID, CWOC.COST_BOOK_ID, CCEB.COST_ELEMENT_ID, CCEB.COST_ELEMENT_CODE, CCEB.COST_ELEMENT_TYPE, CWOO.WORK_CENTER_ID, WWCV.WORK_CENTER_NAME, CWOO.WORK_ORDER_OPERATION_ID, WWOV.OPERATION_NAME, WDOV.OPERATION_NAME, CWO.WORK_ORDER_LESS_FLAG, CWOC.CURRENCY_CODE UNION ALL SELECT X.CST_WORK_ORDER_ID, x.COST_ORG_ID, x.COST_BOOK_ID, x.COST_ELEMENT_ID, x.COST_ELEMENT_CODE, x.COST_ELEMENT_TYPE, x.WORK_CENTER_ID, x.WORK_CENTER_NAME, x.WORK_ORDER_OPERATION_ID, x.OPERATION_NAME, x.OUTPUT_COST, x.BALANCE_IN_WIP, x.VARIANCE_COST, x.SCRAP_COST, x.SCRAP_EXP_COST, x.SCRAP_IN_INV_COST, x.CURRENCY_CODE, z.ANALYSIS_GROUP_ID, z.ANALYSIS_CODE FROM (SELECT CWOC.CST_WORK_ORDER_ID, CWOC.COST_ORG_ID, CWOC.COST_BOOK_ID, CCEB.COST_ELEMENT_ID, CCEB.COST_ELEMENT_CODE, CCEB.COST_ELEMENT_TYPE, CWOO.WORK_CENTER_ID, WWCV.WORK_CENTER_NAME, CWOO.WORK_ORDER_OPERATION_ID, CASE WHEN CWO.WORK_ORDER_LESS_FLAG = 'Y' THEN WDOV.OPERATION_NAME ELSE WWOV.OPERATION_NAME END OPERATION_NAME, ABS( SUM( WIP_TXN_SIGN * QUANTITY * ( CASE WHEN WIP_COST_TYPE='PRODUCT' THEN UNIT_COST else 0 end)) ) as OUTPUT_COST, ABS( SUM( WIP_TXN_SIGN * QUANTITY * UNIT_COST ) - SUM(WIP_TXN_SIGN * QUANTITY * ( CASE WHEN WIP_COST_TYPE ='SCRAP' AND ABSORPTION_TYPE=1 THEN UNIT_COST else 0 END) ) ) AS BALANCE_IN_WIP, ABS( SUM( CWOC.QUANTITY * DECODE(CWOC.WIP_COST_TYPE,'VARIANCE',CWOC.UNIT_COST,0) * CWOC.WIP_TXN_SIGN )) AS VARIANCE_COST, ABS ( SUM( CWOC.QUANTITY * DECODE(CWOC.WIP_COST_TYPE,'SCRAP',CWOC.UNIT_COST,0) * CWOC.WIP_TXN_SIGN ) ) AS SCRAP_COST, ABS( SUM( WIP_TXN_SIGN * QUANTITY * ( CASE WHEN WIP_COST_TYPE ='SCRAP' AND ABSORPTION_TYPE=2 THEN UNIT_COST else 0 end)) )as SCRAP_EXP_COST, ABS ( SUM( WIP_TXN_SIGN * QUANTITY * ( CASE when WIP_COST_TYPE ='SCRAP' AND ABSORPTION_TYPE=1 THEN UNIT_COST else 0 END))) AS SCRAP_IN_INV_COST, CWOC.CURRENCY_CODE, CACB.ANALYSIS_GROUP_ID, CACB.ANALYSIS_ID FROM CST_WORK_ORDER_COSTS CWOC, CST_WORK_ORDERS CWO, CST_COST_ELEMENTS_VL CCEB, CST_WORK_ORDER_OPERATIONS CWOO, WIS_WORK_CENTERS_VL WWCV, WIE_WO_OPERATIONS_V WWOV, WIS_WD_OPERATIONS_V WDOV, CST_ANALYSIS_CODES_B CACB WHERE CWO.CST_WORK_ORDER_ID = CWOC.CST_WORK_ORDER_ID AND CWOC.COST_ELEMENT_ID = CCEB.COST_ELEMENT_ID AND CWOC.CST_WORK_ORDER_ID = CWOO.CST_WORK_ORDER_ID AND CWOC.CST_WORK_ORDER_OPERATION_ID = CWOO.CST_WORK_ORDER_OPERATION_ID AND CWOO.WORK_CENTER_ID = WWCV.WORK_CENTER_ID AND CWOO.WORK_ORDER_OPERATION_ID = WWOV.WO_OPERATION_ID(+) and CWOO.WORK_ORDER_OPERATION_ID = WDOV.WD_OPERATION_ID(+) and CACB.DEFAULT_ANALYSIS_CODE_FLAG = 'Y' group by CWOC.CST_WORK_ORDER_ID, CWOC.COST_ORG_ID, CWOC.COST_BOOK_ID, CCEB.COST_ELEMENT_ID, CCEB.COST_ELEMENT_CODE, CCEB.COST_ELEMENT_TYPE, CWOO.WORK_CENTER_ID, WWCV.WORK_CENTER_NAME, CWOO.WORK_ORDER_OPERATION_ID, WWOV.OPERATION_NAME, WDOV.OPERATION_NAME, CWO.WORK_ORDER_LESS_FLAG, CWOC.CURRENCY_CODE, CACB.ANALYSIS_GROUP_ID, CACB.ANALYSIS_ID ) x, CST_ELEMENT_ANALYSIS_GROUPS y, CST_ANALYSIS_CODES_B z WHERE x.COST_ELEMENT_ID = y.COST_ELEMENT_ID (+) and X.ANALYSIS_GROUP_ID = Y.ANALYSIS_GROUP_ID(+) and Z.ANALYSIS_ID = NVL(Y.ANALYSIS_ID,X.ANALYSIS_ID)