VIEW: APPS.SO_MFG_STATUS_VIEW

Source

View Text - Preformatted

SELECT SL.LINE_ID LINE_ID, DECODE(NVL(SL.ORDERED_QUANTITY,-999), -999, -1, SL.CANCELLED_QUANTITY,NULL, DECODE(NVL(SL.ATO_FLAG,'N'), 'N', SL.S27, DECODE(COUNT(SLD.ROWID), 0, DECODE(SL.S27,19,4,20,4,21,4,23,4,SL.S27), DECODE(SL.S27, NULL, NULL, 18,18, 8,8, DECODE( SUM(DECODE(SLD.RELEASED_FLAG, 'Y',SLD.QUANTITY, DECODE(SLD.SCHEDULE_STATUS_CODE, 'RESERVED', SLD.QUANTITY,0)) ) + SUM(DECODE(SLD.SCHEDULE_STATUS_CODE, 'SUPPLY RESERVED', NVL(SLD.WIP_COMPLETED_QUANTITY,0), 0) ), SUM(SLD.QUANTITY), 19, 0, DECODE(SIGN(SUM(DECODE(SLD.SCHEDULE_STATUS_CODE, 'SUPPLY RESERVED', NVL(SLD.WIP_RESERVED_QUANTITY,0), 0))), 1,21, 0, DECODE(SL.ATO_LINE_ID, NULL, DECODE(SL.ITEM_TYPE_CODE,'MODEL',23,4), 23)), 20) ) )) ) ACTUAL_MFG_STATUS , SL.S27 CURRENT_MFG_STATUS FROM SO_LINE_DETAILS SLD,SO_LINES SL WHERE SLD.LINE_ID(+) = NVL(SL.ATO_LINE_ID,SL.LINE_ID) AND NVL(SLD.CONFIGURATION_ITEM_FLAG(+),'N') = DECODE(NVL(SL.ATO_FLAG,'N'),'N','Y', DECODE(SL.ATO_LINE_ID,NULL,DECODE(SL.ITEM_TYPE_CODE,'MODEL','Y', 'N'), 'Y')) GROUP BY SL.LINE_ID, SL.ATO_LINE_ID, SL.ATO_FLAG, SL.ITEM_TYPE_CODE, SL.ORDERED_QUANTITY, SL.CANCELLED_QUANTITY, SL.S27

View Text - HTML Formatted

SELECT SL.LINE_ID LINE_ID
, DECODE(NVL(SL.ORDERED_QUANTITY
, -999)
, -999
, -1
, SL.CANCELLED_QUANTITY
, NULL
, DECODE(NVL(SL.ATO_FLAG
, 'N')
, 'N'
, SL.S27
, DECODE(COUNT(SLD.ROWID)
, 0
, DECODE(SL.S27
, 19
, 4
, 20
, 4
, 21
, 4
, 23
, 4
, SL.S27)
, DECODE(SL.S27
, NULL
, NULL
, 18
, 18
, 8
, 8
, DECODE( SUM(DECODE(SLD.RELEASED_FLAG
, 'Y'
, SLD.QUANTITY
, DECODE(SLD.SCHEDULE_STATUS_CODE
, 'RESERVED'
, SLD.QUANTITY
, 0)) ) + SUM(DECODE(SLD.SCHEDULE_STATUS_CODE
, 'SUPPLY RESERVED'
, NVL(SLD.WIP_COMPLETED_QUANTITY
, 0)
, 0) )
, SUM(SLD.QUANTITY)
, 19
, 0
, DECODE(SIGN(SUM(DECODE(SLD.SCHEDULE_STATUS_CODE
, 'SUPPLY RESERVED'
, NVL(SLD.WIP_RESERVED_QUANTITY
, 0)
, 0)))
, 1
, 21
, 0
, DECODE(SL.ATO_LINE_ID
, NULL
, DECODE(SL.ITEM_TYPE_CODE
, 'MODEL'
, 23
, 4)
, 23))
, 20) ) )) ) ACTUAL_MFG_STATUS
, SL.S27 CURRENT_MFG_STATUS
FROM SO_LINE_DETAILS SLD
, SO_LINES SL
WHERE SLD.LINE_ID(+) = NVL(SL.ATO_LINE_ID
, SL.LINE_ID)
AND NVL(SLD.CONFIGURATION_ITEM_FLAG(+)
, 'N') = DECODE(NVL(SL.ATO_FLAG
, 'N')
, 'N'
, 'Y'
, DECODE(SL.ATO_LINE_ID
, NULL
, DECODE(SL.ITEM_TYPE_CODE
, 'MODEL'
, 'Y'
, 'N')
, 'Y')) GROUP BY SL.LINE_ID
, SL.ATO_LINE_ID
, SL.ATO_FLAG
, SL.ITEM_TYPE_CODE
, SL.ORDERED_QUANTITY
, SL.CANCELLED_QUANTITY
, SL.S27