PL_CAP_USG_DTVW

(SQL View)
 Index Back

Capacity Usage Detail View

Capacity Usage Detail View for production ID or planned order ID.

SELECT DISTINCT CP.PROCESS_INSTANCE , CP.PROBINST , CP.BUSINESS_UNIT , CP.PL_RESOURCE_CODE , CP.PERIOD , %NumToChar(PLN.PL_SEQ_NBR) , CPD.OP_SEQUENCE , CPD.PL_SC_START_DATE , CPD.PL_SC_END_DATE , CPD.PL_RSRC_LD , %Truncate(%DecMult(%decdiv(CPD.PL_RSRC_LD , CP.QTY_REQUIRED ) , 100 ) , 2) , CPD.PL_ORDER_TYPE , PLH.INV_ITEM_ID , PLH.PL_PLAN_STATUS , PLN.PLAN_FROZEN_FLG , 'Y' , B.XLATLONGNAME , C.XLATLONGNAME , D.XLATLONGNAME FROM PS_PL_CAP_USG_TAO CP , PS_PL_CAP_USG_DTAO CPD , PS_PL_PLNPRD_OP PLN , PS_PL_PLNPRD_HEADR PLH , PSXLATITEM B , PSXLATITEM C , PSXLATITEM D WHERE CP.PROBINST = PLN.PROBINST AND PLN.PROBINST = PLH.PROBINST AND CP.PROBINST = CPD.PROBINST AND CP.BUSINESS_UNIT = PLN.BUSINESS_UNIT AND PLN.BUSINESS_UNIT = PLH.BUSINESS_UNIT AND CP.BUSINESS_UNIT = CPD.BUSINESS_UNIT AND CPD.ORDER_NO = %NumToChar (PLN.PL_SEQ_NBR) AND PLN.PL_SEQ_NBR = PLH.PL_SEQ_NBR AND PLN.OP_SEQUENCE = CPD.OP_SEQUENCE AND CP.PL_RESOURCE_CODE = CPD.PL_RESOURCE_CODE AND CP.START_DTTM = CPD.START_DTTM AND CP.PROCESS_INSTANCE = CPD.PROCESS_INSTANCE AND CPD.PL_ORDER_TYPE = 'PLN' AND B.FIELDNAME='PL_PLAN_STATUS' AND B.FIELDVALUE = PLH.PL_PLAN_STATUS AND C.FIELDNAME='PLAN_FROZEN_FLG' AND C.FIELDVALUE = PLN.PLAN_FROZEN_FLG AND D.FIELDNAME='YES_OR_NO' AND D.FIELDVALUE = 'Y' AND CP.QTY_REQUIRED > 0 UNION SELECT DISTINCT CP.PROCESS_INSTANCE , CP.PROBINST , CP.BUSINESS_UNIT , CP.PL_RESOURCE_CODE , CP.PERIOD , PRD.PRODUCTION_ID , CPD.OP_SEQUENCE , CPD.PL_SC_START_DATE , CPD.PL_SC_END_DATE , CPD.PL_RSRC_LD , %Truncate( %DecMult(%decdiv(CPD.PL_RSRC_LD , CP.QTY_REQUIRED ) , 100 ) ,2) , CPD.PL_ORDER_TYPE , PRH.INV_ITEM_ID , PRH.PROD_STATUS , PRD.PLAN_FROZEN_FLG , 'N' , B.XLATLONGNAME , C.XLATLONGNAME , D.XLATLONGNAME FROM PS_PL_CAP_USG_TAO CP , PS_PL_CAP_USG_DTAO CPD , PS_PL_PR_OP_LIST PRD , PS_PL_PRDNID_HEADR PRH , PSXLATITEM B , PSXLATITEM C , PSXLATITEM D WHERE CP.PROBINST = PRD.PROBINST AND PRD.PROBINST = PRH.PROBINST AND CP.PROBINST = CPD.PROBINST AND CP.BUSINESS_UNIT = PRD.BUSINESS_UNIT AND PRD.BUSINESS_UNIT = PRH.BUSINESS_UNIT AND CP.BUSINESS_UNIT = CPD.BUSINESS_UNIT AND CPD.ORDER_NO = PRD.PRODUCTION_ID AND PRD.PRODUCTION_ID = PRH.PRODUCTION_ID AND PRD.OP_SEQUENCE = CPD.OP_SEQUENCE AND CP.PL_RESOURCE_CODE = CPD.PL_RESOURCE_CODE AND CP.START_DTTM = CPD.START_DTTM AND CP.PROCESS_INSTANCE = CPD.PROCESS_INSTANCE AND CPD.PL_ORDER_TYPE = 'PDN' AND B.FIELDNAME='PROD_STATUS' AND B.FIELDVALUE = PRH.PROD_STATUS AND C.FIELDNAME='PLAN_FROZEN_FLG' AND C.FIELDVALUE = PRD.PLAN_FROZEN_FLG AND D.FIELDNAME='YES_OR_NO' AND D.FIELDVALUE = 'N' AND CP.QTY_REQUIRED > 0 UNION SELECT DISTINCT CP.PROCESS_INSTANCE , CP.PROBINST , CP.BUSINESS_UNIT , CP.PL_RESOURCE_CODE , CP.PERIOD , %NumToChar(PLN.PL_SEQ_NBR) , CPD.OP_SEQUENCE , CPD.PL_SC_START_DATE , CPD.PL_SC_END_DATE , CPD.PL_RSRC_LD , 0 , CPD.PL_ORDER_TYPE , PLH.INV_ITEM_ID , PLH.PL_PLAN_STATUS , PLN.PLAN_FROZEN_FLG , 'Y' , B.XLATLONGNAME , C.XLATLONGNAME , D.XLATLONGNAME FROM PS_PL_CAP_USG_TAO CP , PS_PL_CAP_USG_DTAO CPD , PS_PL_PLNPRD_OP PLN , PS_PL_PLNPRD_HEADR PLH , PSXLATITEM B , PSXLATITEM C , PSXLATITEM D WHERE CP.PROBINST = PLN.PROBINST AND PLN.PROBINST = PLH.PROBINST AND CP.PROBINST = CPD.PROBINST AND CP.BUSINESS_UNIT = PLN.BUSINESS_UNIT AND PLN.BUSINESS_UNIT = PLH.BUSINESS_UNIT AND CP.BUSINESS_UNIT = CPD.BUSINESS_UNIT AND CPD.ORDER_NO = %NumToChar (PLN.PL_SEQ_NBR) AND PLN.PL_SEQ_NBR = PLH.PL_SEQ_NBR AND PLN.OP_SEQUENCE = CPD.OP_SEQUENCE AND CP.PL_RESOURCE_CODE = CPD.PL_RESOURCE_CODE AND CP.START_DTTM = CPD.START_DTTM AND CP.PROCESS_INSTANCE = CPD.PROCESS_INSTANCE AND CPD.PL_ORDER_TYPE = 'PLN' AND B.FIELDNAME='PL_PLAN_STATUS' AND B.FIELDVALUE = PLH.PL_PLAN_STATUS AND C.FIELDNAME='PLAN_FROZEN_FLG' AND C.FIELDVALUE = PLN.PLAN_FROZEN_FLG AND D.FIELDNAME='YES_OR_NO' AND D.FIELDVALUE = 'Y' AND CP.QTY_REQUIRED = 0 UNION SELECT DISTINCT CP.PROCESS_INSTANCE , CP.PROBINST , CP.BUSINESS_UNIT , CP.PL_RESOURCE_CODE , CP.PERIOD , PRD.PRODUCTION_ID , CPD.OP_SEQUENCE , CPD.PL_SC_START_DATE , CPD.PL_SC_END_DATE , CPD.PL_RSRC_LD , 0 , CPD.PL_ORDER_TYPE , PRH.INV_ITEM_ID , PRH.PROD_STATUS , PRD.PLAN_FROZEN_FLG , 'N' , B.XLATLONGNAME , C.XLATLONGNAME , D.XLATLONGNAME FROM PS_PL_CAP_USG_TAO CP , PS_PL_CAP_USG_DTAO CPD , PS_PL_PR_OP_LIST PRD , PS_PL_PRDNID_HEADR PRH , PSXLATITEM B , PSXLATITEM C , PSXLATITEM D WHERE CP.PROBINST = PRD.PROBINST AND PRD.PROBINST = PRH.PROBINST AND CP.PROBINST = CPD.PROBINST AND CP.BUSINESS_UNIT = PRD.BUSINESS_UNIT AND PRD.BUSINESS_UNIT = PRH.BUSINESS_UNIT AND CP.BUSINESS_UNIT = CPD.BUSINESS_UNIT AND CPD.ORDER_NO = PRD.PRODUCTION_ID AND PRD.PRODUCTION_ID = PRH.PRODUCTION_ID AND PRD.OP_SEQUENCE = CPD.OP_SEQUENCE AND CP.PL_RESOURCE_CODE = CPD.PL_RESOURCE_CODE AND CP.START_DTTM = CPD.START_DTTM AND CP.PROCESS_INSTANCE = CPD.PROCESS_INSTANCE AND CPD.PL_ORDER_TYPE = 'PDN' AND B.FIELDNAME='PROD_STATUS' AND B.FIELDVALUE = PRH.PROD_STATUS AND C.FIELDNAME='PLAN_FROZEN_FLG' AND C.FIELDVALUE = PRD.PLAN_FROZEN_FLG AND D.FIELDNAME='YES_OR_NO' AND D.FIELDVALUE = 'N' AND CP.QTY_REQUIRED = 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
2 PROBINST Character(20) VARCHAR2(20) NOT NULL Optimization Problem Instance
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 PL_RESOURCE_CODE Character(10) VARCHAR2(10) NOT NULL 01/14/03 JC (CN#PL880-1): Added label SPL_WORK_CENTER
5 PERIOD Number(3,0) SMALLINT NOT NULL Period
6 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
7 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
8 PL_SC_START_DATE DateTime(26) TIMESTAMP PL_SC_START_DATE
9 PL_SC_END_DATE DateTime(26) TIMESTAMP PL_SC_END_DATE
10 PL_RSRC_LD Number(16,4) DECIMAL(15,4) NOT NULL PL_RSRC_LD
11 PL_RSRC_LD_PCT Number(16,4) DECIMAL(15,4) NOT NULL PL_RSRC_LD_PCT
12 PL_ORDER_TYPE Character(3) VARCHAR2(3) NOT NULL RAG# Added filed for Planning Phoneix.
BA=Buying Agreement
NFC=Net Forecast
PDN=Production Order
PLN=Planned Order
PPO=Planned Purchase Order
PPR=Planned Production
PSS=Strategic Sourcing Event
PTR=Planned Transfer
PUR=Purchase Order
SO=Sales Order
SOH=Starting On Hand Qty
SR=Material Stock Request
TFR=Transfer
XD=Extra Demand
13 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
14 PL_SC_ORDER_STATUS Character(2) VARCHAR2(2) NOT NULL PL_SC_ORDER_STATUS
05=Planned
10=Entered
15=Firmed
20=Released
30=In Process
40=Pending Complete
50=Completed
60=Open
70=Active
75=Picked
77=Received
80=Intransit
85=Dispatched
90=Canceled
95=Closed
15 PLAN_FROZEN_FLG Character(1) VARCHAR2(1) NOT NULL 11/01/01 PCL (CN#PL900-1): Updated
N=No
Y=Yes
16 FLAG_PLNPROD Character(1) VARCHAR2(1) NOT NULL FLAG_PLNPROD
17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
18 DESCR_VALUE_1 Character(30) VARCHAR2(30) NOT NULL Description
19 DESCR_VALUE_2 Character(30) VARCHAR2(30) NOT NULL Description