WM_WO_MTRDY_VW2

(SQL View)
 Index Back

Work Order Mat Ready View #2

Work Order Material Readiness View #2 -- selects Work Order tasks analyzed for material readiness, calculates task's overall readiness for Inventory and/or Procured items

SELECT T1.PROCESS_INSTANCE , R.OPRID , R.BUSINESS_UNIT , T1.WO_ID , T1.WO_TASK_ID , R.WM_MTRDY_REPT_OPT , CASE WHEN COUNT(M.RES_LN_NBR) IS NULL THEN 0 ELSE COUNT(M.RES_LN_NBR) END , CASE WHEN SUM(M.NR_REASON_CNT) > 0 THEN '20' WHEN SUM(M.UD_REASON_CNT) > 0 THEN '30' WHEN SUM(M.NR_REASON_CNT) = 0 AND SUM(M.UD_REASON_CNT) = 0 THEN '10' WHEN R.WM_MTRDY_REPT_OPT IN ('1' ,'3') AND SUM(M.NR_REASON_CNT) IS NULL THEN '00' ELSE ' ' END , CASE WHEN COUNT(P.RES_LN_NBR) IS NULL THEN 0 ELSE COUNT(P.RES_LN_NBR) END , CASE WHEN SUM(P.REASON_CNT) > 0 THEN '20' WHEN SUM(P.REASON_CNT) = 0 THEN '10' WHEN R.WM_MTRDY_REPT_OPT IN ('2' ,'3') AND SUM(P.REASON_CNT) IS NULL THEN '00' ELSE ' ' END FROM PS_WM_WO_MTRDY_RUN R , PS_WM_WO_MTRDY_VW T1 , PS_WM_WO_MAT_MR M , PS_WM_WO_MTRDY_VW T2 , PS_WM_WO_PO_MR P WHERE T1.PROCESS_INSTANCE = R.PROCESS_INSTANCE AND T1.OPRID = R.OPRID AND T1.BUSINESS_UNIT = R.BUSINESS_UNIT AND T1.PROCESS_INSTANCE = M.PROCESS_INSTANCE (+) AND T1.OPRID = M.OPRID (+) AND T1.BUSINESS_UNIT = M.BUSINESS_UNIT (+) AND T1.WO_ID = M.WO_ID (+) AND T1.WO_TASK_ID = M.WO_TASK_ID (+) AND T2.PROCESS_INSTANCE = T1.PROCESS_INSTANCE AND T2.OPRID = T1.OPRID AND T2.BUSINESS_UNIT = T1.BUSINESS_UNIT AND T2.WO_ID = T1.WO_ID AND T2.WO_TASK_ID = T1.WO_TASK_ID AND T2.PROCESS_INSTANCE = P.PROCESS_INSTANCE (+) AND T2.OPRID = P.OPRID (+) AND T2.BUSINESS_UNIT = P.BUSINESS_UNIT (+) AND T2.WO_ID = P.WO_ID (+) AND T2.WO_TASK_ID = P.WO_TASK_ID (+) GROUP BY T1.PROCESS_INSTANCE , R.OPRID , R.BUSINESS_UNIT , T1.WO_ID , T1.WO_TASK_ID, R.WM_MTRDY_REPT_OPT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 WO_ID Character(10) VARCHAR2(10) NOT NULL Work Order Identification
5 WO_TASK_ID Number(5,0) INTEGER NOT NULL Task Number
6 WM_MTRDY_REPT_OPT Character(1) VARCHAR2(1) NOT NULL Analyze Material Readiness for
1=Inventory Items Only
2=Procured Items Only
3=Inventory and Procured Items

Default Value: 3

7 WM_INV_MR_COUNT Number(5,0) INTEGER NOT NULL Inventory Items Count
8 WM_INV_MR_STATUS Character(2) VARCHAR2(2) NOT NULL Inventory Item Readiness
00=*****
10=Ready
20=Not Ready
30=Undetermined
9 WM_PO_MR_COUNT Number(5,0) INTEGER NOT NULL Procured Items Count
10 WM_PO_MR_STATUS Character(2) VARCHAR2(2) NOT NULL Procured Item Readiness
00=*****
10=Ready
20=Not Ready
30=Undetermined