Kindly refer the attached queries namely
1)REORDER
2)PENDING WORKORDER

I want to calculate the work order requirement on basis of
Stock, Pending sales order, re-order level and already entered workorders. Now i want to add the first column of 2nd query in the report of 1st query. For that i tried the below statements

AND ITEM_CODE = LCS_ITEM_CODE (+)
AND ITEM_CODE = SOI_ITEM_CODE (+)
AND ITEM_CODE = PWH_ITEM_CODE (+)

LCS_ITEM_CODE is Stock table item_code,
SOI_ITEM_CODE is Sales order item_code,
PWH_ITEM_CODE is Production work order item_code,

When the above statments used while combining the 2 queries does display the result, but the details comes only for the ietm_codes whose work orders are present.

Kindly guide me how can i get the desired output.



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_ST K_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_ST K_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_ST K_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_ST K_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_ST K_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


Yogesh

__________________
Yogesh