1)RE-ORDER QUERY : SELECT DISTINCT ITEM_CODE, ITEM_NAME, (CASE WHEN SUBSTR(ITEM_CODE,11,3) = '000' THEN 'LOOSE' ELSE SUBSTR(ITEM_CODE,11,4) END) PKG_SIZE, ((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3)) ||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) STOCK, (CASE WHEN SUM(TO_NUMBER(SOI_QTY ||'.' ||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) < 0 THEN 0 ELSE SUM(TO_NUMBER(SOI_QTY ||'.' ||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) END) PNDG, ((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3)) ||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY ||'.' ||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) NETSTK, ITEM_RORD_LVL REORD_LVL, (CASE WHEN ((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3)) ||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY ||'.' ||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) > 0 AND ITEM_RORD_LVL > 0 THEN ((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3)) ||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY ||'.' ||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) ELSE NVL(ITEM_RORD_LVL,0) - (((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3)) ||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY ||'.' ||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1))) END) REQ, DECODE(ITEM_RORD_QTY_LS,NULL,ITEM_RORD_QTY, (ITEM_RORD_QTY ||'.' ||ITEM_RORD_QTY_LS)) BTCHSZE FROM OM_ITEM, OT_SO_HEAD, OT_SO_ITEM, OM_ITEM_UOM, OS_LOCN_CURR_STK WHERE (ITEM_UOM_CODE = IU_UOM_CODE AND ITEM_CODE = IU_ITEM_CODE) AND ITEM_CODE = LCS_ITEM_CODE (+) AND ITEM_CODE = SOI_ITEM_CODE (+) AND LCS_LOCN_CODE = 'FG' AND LCS_ITEM_CODE = SOI_ITEM_CODE AND SOI_SOH_SYS_ID = SOH_SYS_ID AND SOH_TXN_CODE IN ('SORLLOC', 'SORLCT3', 'SORLNPL', 'EXPFABLK') AND NVL(SOH_CLO_STATUS,0) = 0 AND NVL(SOI_SHORT_CLO_STATUS,2) = 2 GROUP BY ITEM_CODE, ITEM_NAME, LCS_STK_QTY_BU, LCS_RCVD_QTY_BU, LCS_ISSD_QTY_BU, IU_MAX_LOOSE_1, ITEM_RORD_LVL_LS, ITEM_RORD_LVL, ITEM_RORD_QTY_LS, ITEM_RORD_QTY, LCS_ITEM_CODE ORDER BY ITEM_CODE ASC ---------------------------------------------------------------------------------------------------------------------------- 2) WORK-ORDERS QUERY : SELECT DISTINCT PWH_NO PWO_NO, PWH_DT PWO_DT, (CASE WHEN PWFGPI_QTY = 0 THEN PWH_ITEM_CODE ELSE PWFGPI_ITEM_CODE END) ITEMCODE, ITEM_NAME PRODUCT_NAME, (CASE WHEN PWFGPI_QTY = 0 THEN SUM(PWH_QTY) ELSE PWFGPI_QTY * SUBSTR(PWFGPI_ITEM_CODE,11,3) END) PWO_QTY, PSD_PR_ACCP_QTY_BU / 1000 PROD_REP, PWH_PS_QTY_BU / 1000 PROD_SLIP_PENDING FROM OT_PWO_HEAD, OM_ITEM, OT_PWO_STAGE_DETAIL, OT_PWO_FG_PACKED_ITEM WHERE PWH_ITEM_CODE = ITEM_CODE AND PWFGPI_PWH_SYS_ID = PWH_SYS_ID AND NVL(PWH_CLO_STATUS,0) = 0 AND PSD_PWH_SYS_ID = PWH_SYS_ID AND PWH_PS_QTY_BU / 1000 IS NULL GROUP BY PWH_NO, PWH_DT, PWH_ITEM_CODE, ITEM_NAME, PWFGPI_ITEM_CODE, PWFGPI_QTY, PSD_PR_ACCP_QTY_BU, PWH_PS_QTY_BU, ITEM_CODE ORDER BY PWH_NO