DP_IPM_VIEW

(SQL View)
 Index Back

IPM build using Row Lvl Sec

SELECT DISTINCT(B.OPRID) , A.DP_POLICYSET , A.DP_ITEMCODE ,E.DP_LOCATION ,E.DP_DESCR ,E.DP_POLICYCONTROL ,E.DP_INVENTORYUOM ,E.DP_STANDARDUOM ,E.DP_LEADTIME ,E.DP_LEADTIMEUOM ,E.DP_WEIGHT ,E.DP_WEIGHTUOM ,E.DP_VOLUME ,E.DP_VOLUMEUOM ,E.DP_INHERITCONTROLS ,E.DP_RUNOUTDATE ,E.DP_SAFETYSTOCK ,E.DP_REORDERPOINT ,E.DP_ORDERQUANTITY ,E.DP_MINIMUM ,E.DP_MAXIMUM ,E.DP_AVGINVENTORY ,E.DP_SERVICEFILL ,E.DP_TURNRATE ,E.DP_OQMETHOD ,E.DP_OQARGUMENT ,E.DP_OQLIMITMETHOD ,E.DP_OQLOWERLIMIT ,E.DP_OQUPPERLIMIT ,E.DP_SSMETHOD ,E.DP_SSARGUMENT ,E.DP_SSLIMITMETHOD ,E.DP_SSLOWERLIMIT ,E.DP_SSUPPERLIMIT ,E.DP_ROPMETHOD ,E.DP_ROPARGUMENT ,E.DP_MINMETHOD ,E.DP_MINARGUMENT ,E.DP_MAXMETHOD ,E.DP_MAXARGUMENT ,E.DP_ORDERCOST ,E.DP_CARRYINGCOST ,E.DP_STANDARDPRICE ,E.DP_STANDARDCOST ,E.DP_ANNUALCOST ,E.DP_ANNUALINVEST ,E.DP_ORDERMULT ,E.DP_STATICCALCFLG ,E.DP_STATICCALCARG ,E.DP_LASTSTATICCHG ,E.DP_LASTCALCDATE ,E.DP_LASTANNLCOSTUPD ,E.DP_MAXLTUSAGEMETH ,E.DP_HISTMAXLTUSAGE ,E.DP_FCPERIODS ,E.DP_FCSTDDEV ,E.DP_SIMFCSTDDEV ,E.DP_EFFDEMANDPDS ,E.DP_EXPLODEDITEM ,E.DP_LOWLEVELCD ,E.DP_ORDERCOUNT ,E.UTILIZ_CD ,E.UTILIZ_GROUP ,E.DP_LASTTRANSDATE ,E.DP_PUBLISHNAME ,E.DP_PUBLISH_DATE ,E.DP_CORPSTARTPD ,E.DP_CORPSTARTYR ,E.DP_CORPENDPD ,E.DP_CORPENDYR ,E.DP_UD01 ,E.DP_UD02 ,E.DP_UD03 ,E.DP_UD04 ,E.DP_UD05 ,E.DP_UD06 ,E.DP_UD07 ,E.DP_UD08 ,E.DP_UD09 ,E.DP_UD10 ,E.DP_UD11 ,E.DP_UD12 ,E.DP_UD13 ,E.DP_UD14 ,E.DP_UD15 ,E.DP_UD16 ,E.DP_UD17 ,E.DP_UD18 ,E.DP_UD19 ,E.DP_UD20 ,E.DP_UD21 ,E.DP_UD22 ,E.DP_UD23 ,E.DP_UD24 ,E.DP_UD25 ,E.DP_UD26 ,E.DP_UD27 ,E.DP_UD28 ,E.DP_UD29 ,E.DP_UD30 ,E.DP_UD31 ,E.DP_UD32 ,E.DP_UD33 ,E.DP_UD34 ,E.DP_UD35 ,E.DP_UD36 ,E.DP_UD37 ,E.DP_UD38 ,E.DP_UD39 ,E.DP_UD40 ,E.DP_UD41 ,E.DP_UD42 ,E.DP_UD43 ,E.DP_UD44 ,E.DP_UD45 ,E.DP_UD46 ,E.DP_UD47 ,E.DP_UD48 ,E.DP_UD49 ,E.DP_UD50 FROM PS_DP_ROLEIPITEM A , PSOPRDEFN B , PSROLEUSER C , PS_DP_POLS1USER_VW D , PS_DP_IPMASTER E WHERE A.ROLENAME = C.ROLENAME AND C.ROLEUSER = B.OPRID AND A.DP_POLICYSET = D.DP_POLICYSET AND B.OPRID = D.OPRID AND E.DP_POLICYSET = A.DP_POLICYSET AND E.DP_ITEMCODE = A.DP_ITEMCODE

SELECT DISTINCT(B.OPRID) , A.DP_POLICYSET , A.DP_ITEMCODE ,E.DP_LOCATION ,E.DP_DESCR ,E.DP_POLICYCONTROL ,E.DP_INVENTORYUOM ,E.DP_STANDARDUOM ,E.DP_LEADTIME ,E.DP_LEADTIMEUOM ,E.DP_WEIGHT ,E.DP_WEIGHTUOM ,E.DP_VOLUME ,E.DP_VOLUMEUOM ,E.DP_INHERITCONTROLS ,E.DP_RUNOUTDATE ,E.DP_SAFETYSTOCK ,E.DP_REORDERPOINT ,E.DP_ORDERQUANTITY ,E.DP_MINIMUM ,E.DP_MAXIMUM ,E.DP_AVGINVENTORY ,E.DP_SERVICEFILL ,E.DP_TURNRATE ,E.DP_OQMETHOD ,E.DP_OQARGUMENT ,E.DP_OQLIMITMETHOD ,E.DP_OQLOWERLIMIT ,E.DP_OQUPPERLIMIT ,E.DP_SSMETHOD ,E.DP_SSARGUMENT ,E.DP_SSLIMITMETHOD ,E.DP_SSLOWERLIMIT ,E.DP_SSUPPERLIMIT ,E.DP_ROPMETHOD ,E.DP_ROPARGUMENT ,E.DP_MINMETHOD ,E.DP_MINARGUMENT ,E.DP_MAXMETHOD ,E.DP_MAXARGUMENT ,E.DP_ORDERCOST ,E.DP_CARRYINGCOST ,E.DP_STANDARDPRICE ,E.DP_STANDARDCOST ,E.DP_ANNUALCOST ,E.DP_ANNUALINVEST ,E.DP_ORDERMULT ,E.DP_STATICCALCFLG ,E.DP_STATICCALCARG ,E.DP_LASTSTATICCHG ,E.DP_LASTCALCDATE ,E.DP_LASTANNLCOSTUPD ,E.DP_MAXLTUSAGEMETH ,E.DP_HISTMAXLTUSAGE ,E.DP_FCPERIODS ,E.DP_FCSTDDEV ,E.DP_SIMFCSTDDEV ,E.DP_EFFDEMANDPDS ,E.DP_EXPLODEDITEM ,E.DP_LOWLEVELCD ,E.DP_ORDERCOUNT ,E.UTILIZ_CD ,E.UTILIZ_GROUP ,E.DP_LASTTRANSDATE ,E.DP_PUBLISHNAME ,E.DP_PUBLISH_DATE ,E.DP_CORPSTARTPD ,E.DP_CORPSTARTYR ,E.DP_CORPENDPD ,E.DP_CORPENDYR ,E.DP_UD01 ,E.DP_UD02 ,E.DP_UD03 ,E.DP_UD04 ,E.DP_UD05 ,E.DP_UD06 ,E.DP_UD07 ,E.DP_UD08 ,E.DP_UD09 ,E.DP_UD10 ,E.DP_UD11 ,E.DP_UD12 ,E.DP_UD13 ,E.DP_UD14 ,E.DP_UD15 ,E.DP_UD16 ,E.DP_UD17 ,E.DP_UD18 ,E.DP_UD19 ,E.DP_UD20 ,E.DP_UD21 ,E.DP_UD22 ,E.DP_UD23 ,E.DP_UD24 ,E.DP_UD25 ,E.DP_UD26 ,E.DP_UD27 ,E.DP_UD28 ,E.DP_UD29 ,E.DP_UD30 ,E.DP_UD31 ,E.DP_UD32 ,E.DP_UD33 ,E.DP_UD34 ,E.DP_UD35 ,E.DP_UD36 ,E.DP_UD37 ,E.DP_UD38 ,E.DP_UD39 ,E.DP_UD40 ,E.DP_UD41 ,E.DP_UD42 ,E.DP_UD43 ,E.DP_UD44 ,E.DP_UD45 ,E.DP_UD46 ,E.DP_UD47 ,E.DP_UD48 ,E.DP_UD49 ,E.DP_UD50 FROM PS_DP_ROLEIPITEM A , PSOPRDEFN B , PSROLEUSER C , PS_DP_POLS1USER_VW D , PS_DP_IPMASTER E WHERE A.ROLENAME = C.ROLENAME AND C.ROLEUSER = B.OPRID AND A.DP_POLICYSET = D.DP_POLICYSET AND B.OPRID = D.OPRID AND E.DP_POLICYSET = A.DP_POLICYSET AND E.DP_ITEMCODE = A.DP_ITEMCODE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 DP_POLICYSET Character(10) VARCHAR2(10) NOT NULL DP_POLICYSET

Prompt Table: DP_POLICYSETS

3 DP_ITEMCODE Character(18) VARCHAR2(18) NOT NULL DP_ITEMCODE
4 DP_LOCATION Character(5) VARCHAR2(5) NOT NULL DP_LOCATION
5 DP_DESCR Character(30) VARCHAR2(30) NOT NULL DP_DESCR
6 DP_POLICYCONTROL Character(20) VARCHAR2(20) NOT NULL DP_POLICYCONTROL

Prompt Table: DP_POLICYCTRLS

7 DP_INVENTORYUOM Character(3) VARCHAR2(3) NOT NULL DP_INVENTORYUOM

Prompt Table: UNITS_TBL

8 DP_STANDARDUOM Character(3) VARCHAR2(3) NOT NULL DP_STANDARDUOM
9 DP_LEADTIME Number(4,0) SMALLINT NOT NULL DP_LEADTIME
10 DP_LEADTIMEUOM Character(3) VARCHAR2(3) NOT NULL DP_LEADTIMEUOM

Default Value: DAY

Prompt Table: UNITS_TBL

11 DP_WEIGHT Number(13,2) DECIMAL(12,2) NOT NULL DP_WEIGHT
12 DP_WEIGHTUOM Character(3) VARCHAR2(3) NOT NULL DP_WEIGHTUOM

Prompt Table: UNITS_TBL

13 DP_VOLUME Number(13,2) DECIMAL(12,2) NOT NULL DP_VOLUME
14 DP_VOLUMEUOM Character(3) VARCHAR2(3) NOT NULL DP_VOLUMEUOM

Prompt Table: UNITS_TBL

15 DP_INHERITCONTROLS Character(1) VARCHAR2(1) NOT NULL DP_INHERITCONTROLS

Y/N Table Edit

Default Value: Y

16 DP_RUNOUTDATE Date(10) DATE DP_RUNOUTDATE
17 DP_SAFETYSTOCK Number(15,4) DECIMAL(14,4) NOT NULL DP_SAFETYSTOCK
18 DP_REORDERPOINT Number(15,4) DECIMAL(14,4) NOT NULL DP_REORDERPOINT
19 DP_ORDERQUANTITY Number(15,4) DECIMAL(14,4) NOT NULL DP_ORDERQUANTITY
20 DP_MINIMUM Number(15,4) DECIMAL(14,4) NOT NULL DP_MINIMUM
21 DP_MAXIMUM Number(15,4) DECIMAL(14,4) NOT NULL DP_MAXIMUM
22 DP_AVGINVENTORY Number(15,4) DECIMAL(14,4) NOT NULL DP_AVGINVENTORY
23 DP_SERVICEFILL Number(13,2) DECIMAL(12,2) NOT NULL DP_SERVICEFILL
24 DP_TURNRATE Number(13,2) DECIMAL(12,2) NOT NULL DP_TURNRATE
25 DP_OQMETHOD Character(4) VARCHAR2(4) NOT NULL DP_OQMETHOD
0=None
1=Days Supply
2=Fixed Quantity
3=Economic Order Quantity
4=Lot for Lot

Default Value: 0

26 DP_OQARGUMENT Number(13,2) DECIMAL(12,2) NOT NULL DP_OQARGUMENT
27 DP_OQLIMITMETHOD Character(4) VARCHAR2(4) NOT NULL DP_OQLIMITMETHOD
0=None
1=Days Supply
2=Fixed Quantity

Default Value: 0

28 DP_OQLOWERLIMIT Number(13,2) DECIMAL(12,2) NOT NULL DP_OQLOWERLIMIT
29 DP_OQUPPERLIMIT Number(13,2) DECIMAL(12,2) NOT NULL DP_OQUPPERLIMIT
30 DP_SSMETHOD Character(4) VARCHAR2(4) NOT NULL DP_SSMETHOD
0=None
1=Days Supply
2=Fixed Quantity
3=Percentage Demand Fill
4=% Cycles Without Shortage
5=Maximum Lead Time Usage

Default Value: 0

31 DP_SSARGUMENT Number(13,2) DECIMAL(12,2) NOT NULL DP_SSARGUMENT
32 DP_SSLIMITMETHOD Character(4) VARCHAR2(4) NOT NULL DP_SSLIMITMETHOD
0=None
1=Days Supply
2=Fixed Quantity

Default Value: 0

33 DP_SSLOWERLIMIT Number(13,2) DECIMAL(12,2) NOT NULL DP_SSLOWERLIMIT
34 DP_SSUPPERLIMIT Number(13,2) DECIMAL(12,2) NOT NULL DP_SSUPPERLIMIT
35 DP_ROPMETHOD Character(4) VARCHAR2(4) NOT NULL DP_ROPMETHOD
0=None
1=Days Supply
2=Fixed Quantity
3=Lead Time Demand
4=Lead Time + Safety Stock

Default Value: 0

36 DP_ROPARGUMENT Number(13,2) DECIMAL(12,2) NOT NULL DP_ROPARGUMENT
37 DP_MINMETHOD Character(4) VARCHAR2(4) NOT NULL 4 & 5 Translate values not used as they were not used in old system
0=None
1=Days Supply
2=Fixed Quantity
3=Safety Stock

Default Value: 0

38 DP_MINARGUMENT Number(13,2) DECIMAL(12,2) NOT NULL DP_MINARGUMENT
39 DP_MAXMETHOD Character(4) VARCHAR2(4) NOT NULL DP_MAXMETHOD
0=None
1=Days Supply
2=Fixed Quantity
3=Safety Stock + Order Quantity

Default Value: 0

40 DP_MAXARGUMENT Number(13,2) DECIMAL(12,2) NOT NULL DP_MAXARGUMENT
41 DP_ORDERCOST Number(15,4) DECIMAL(14,4) NOT NULL DP_ORDERCOST
42 DP_CARRYINGCOST Number(15,4) DECIMAL(14,4) NOT NULL DP_CARRYINGCOST
43 DP_STANDARDPRICE Number(15,4) DECIMAL(14,4) NOT NULL DP_STANDARDPRICE
44 DP_STANDARDCOST Number(15,4) DECIMAL(14,4) NOT NULL DP_STANDARDCOST
45 DP_ANNUALCOST Number(15,4) DECIMAL(14,4) NOT NULL DP_ANNUALCOST
46 DP_ANNUALINVEST Number(15,4) DECIMAL(14,4) NOT NULL DP_ANNUALINVEST
47 DP_ORDERMULT Number(15,4) DECIMAL(14,4) NOT NULL DP_ORDERMULT
48 DP_STATICCALCFLG Character(4) VARCHAR2(4) NOT NULL DP_STATICCALCFLG
0=Period Number
1=Periods to Average

Default Value: 1

49 DP_STATICCALCARG Number(2,0) SMALLINT NOT NULL DP_STATICCALCARG
50 DP_LASTSTATICCHG Date(10) DATE DP_LASTSTATICCHG
51 DP_LASTCALCDATE DateTime(26) TIMESTAMP DP_LASTCALCDATE
52 DP_LASTANNLCOSTUPD Date(10) DATE DP_LASTANNLCOSTUPD
53 DP_MAXLTUSAGEMETH Character(4) VARCHAR2(4) NOT NULL Max LT Usage Method
0=Static
1=Lead Time Daily Usage
2=Lead Time Period Usage
3=Maximum Possible Usage

Default Value: 1

54 DP_HISTMAXLTUSAGE Number(13,2) DECIMAL(12,2) NOT NULL DP_HISTMAXLTUSAGE
55 DP_FCPERIODS Number(4,0) SMALLINT NOT NULL DP_FCPERIODS
56 DP_FCSTDDEV Number(22,2) DECIMAL(21,2) NOT NULL DP_FCSTDDEV
57 DP_SIMFCSTDDEV Number(13,2) DECIMAL(12,2) NOT NULL DP_SIMFCSTDDEV
58 DP_EFFDEMANDPDS Number(3,0) SMALLINT NOT NULL DP_EFFDEMANDPDS
59 DP_EXPLODEDITEM Character(1) VARCHAR2(1) NOT NULL DP_EXPLODEDITEM

Y/N Table Edit

60 DP_LOWLEVELCD Number(3,0) SMALLINT NOT NULL DP_LOWLEVELCD
61 DP_ORDERCOUNT Number(13,2) DECIMAL(12,2) NOT NULL DP_ORDERCOUNT
62 UTILIZ_CD Character(4) VARCHAR2(4) NOT NULL Utilization Type

Prompt Table: DP_UTILIZ_CD_VW

63 UTILIZ_GROUP Character(4) VARCHAR2(4) NOT NULL Utilization Group
CYCL=Cycle Count
64 DP_LASTTRANSDATE DateTime(26) TIMESTAMP DP_LASTTRANSDATE
65 DP_PUBLISHNAME Character(20) VARCHAR2(20) NOT NULL Publish Name
66 DP_PUBLISH_DATE Date(10) DATE DP_PUBLISH_DATE
67 DP_CORPSTARTPD Number(2,0) SMALLINT NOT NULL DP_CORPSTARTPD
68 DP_CORPSTARTYR Number(4,0) SMALLINT NOT NULL DP_CORPSTARTYR
69 DP_CORPENDPD Number(2,0) SMALLINT NOT NULL DP_CORPENDPD
70 DP_CORPENDYR Number(4,0) SMALLINT NOT NULL DP_CORPENDYR
71 DP_UD01 Character(30) VARCHAR2(30) NOT NULL DP_UD01
72 DP_UD02 Character(30) VARCHAR2(30) NOT NULL DP_UD02
73 DP_UD03 Character(30) VARCHAR2(30) NOT NULL DP_UD03
74 DP_UD04 Character(30) VARCHAR2(30) NOT NULL DP_UD04
75 DP_UD05 Character(30) VARCHAR2(30) NOT NULL DP_UD05
76 DP_UD06 Character(30) VARCHAR2(30) NOT NULL DP_UD06
77 DP_UD07 Character(30) VARCHAR2(30) NOT NULL DP_UD07
78 DP_UD08 Character(30) VARCHAR2(30) NOT NULL DP_UD08
79 DP_UD09 Character(30) VARCHAR2(30) NOT NULL DP_UD09
80 DP_UD10 Character(30) VARCHAR2(30) NOT NULL DP_UD10
81 DP_UD11 Character(20) VARCHAR2(20) NOT NULL DP_UD11
82 DP_UD12 Character(20) VARCHAR2(20) NOT NULL DP_UD12
83 DP_UD13 Character(20) VARCHAR2(20) NOT NULL DP_UD13
84 DP_UD14 Character(20) VARCHAR2(20) NOT NULL DP_UD14
85 DP_UD15 Character(20) VARCHAR2(20) NOT NULL DP_UD15
86 DP_UD16 Character(20) VARCHAR2(20) NOT NULL DP_UD16
87 DP_UD17 Character(20) VARCHAR2(20) NOT NULL DP_UD17
88 DP_UD18 Character(20) VARCHAR2(20) NOT NULL DP_UD18
89 DP_UD19 Character(20) VARCHAR2(20) NOT NULL DP_UD19
90 DP_UD20 Character(20) VARCHAR2(20) NOT NULL DP_UD20
91 DP_UD21 Character(10) VARCHAR2(10) NOT NULL DP_UD21
92 DP_UD22 Character(10) VARCHAR2(10) NOT NULL DP_UD22
93 DP_UD23 Character(10) VARCHAR2(10) NOT NULL DP_UD23
94 DP_UD24 Character(10) VARCHAR2(10) NOT NULL DP_UD24
95 DP_UD25 Character(10) VARCHAR2(10) NOT NULL DP_UD25
96 DP_UD26 Character(10) VARCHAR2(10) NOT NULL DP_UD26
97 DP_UD27 Character(10) VARCHAR2(10) NOT NULL DP_UD27
98 DP_UD28 Character(10) VARCHAR2(10) NOT NULL DP_UD28
99 DP_UD29 Character(10) VARCHAR2(10) NOT NULL DP_UD29
100 DP_UD30 Character(10) VARCHAR2(10) NOT NULL DP_UD30
101 DP_UD31 Character(10) VARCHAR2(10) NOT NULL DP_UD31
102 DP_UD32 Character(10) VARCHAR2(10) NOT NULL DP_UD32
103 DP_UD33 Character(10) VARCHAR2(10) NOT NULL DP_UD33
104 DP_UD34 Character(10) VARCHAR2(10) NOT NULL DP_UD34
105 DP_UD35 Character(10) VARCHAR2(10) NOT NULL DP_UD35
106 DP_UD36 Character(10) VARCHAR2(10) NOT NULL DP_UD36
107 DP_UD37 Character(10) VARCHAR2(10) NOT NULL DP_UD37
108 DP_UD38 Character(10) VARCHAR2(10) NOT NULL DP_UD38
109 DP_UD39 Character(10) VARCHAR2(10) NOT NULL DP_UD39
110 DP_UD40 Character(10) VARCHAR2(10) NOT NULL DP_UD40
111 DP_UD41 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD41
112 DP_UD42 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD42
113 DP_UD43 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD43
114 DP_UD44 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD44
115 DP_UD45 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD45
116 DP_UD46 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD46
117 DP_UD47 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD47
118 DP_UD48 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD48
119 DP_UD49 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD49
120 DP_UD50 Number(21,4) DECIMAL(20,4) NOT NULL DP_UD50