SF_OP_HRS_VW(SQL View) |
Index Back |
|---|---|
OpList Hours View |
| SELECT A.BUSINESS_UNIT ,A.PRODUCTION_ID ,E.NEW_PRDN_QTY ,A.OP_SEQUENCE ,B.WORK_CENTER_CODE ,B.CONVERSION_CODE ,C.DAILY_CAPACITY ,B.CREWSIZE ,B.MACH_RESOURCES ,EE.AOQ ,A.TIME_TYPE ,A.OP_TIME ,A.OP_RATE ,A.OP_TIME_UNIT ,A.OP_RATE_UNIT ,%Round((%DecMult( ( (%DecMult(%DecDiv(A.OP_TIME,60),%DecDiv(D.CE_MIN,EE.AOQ))) + (%DecMult(A.OP_TIME,%DecMult(C.DAILY_CAPACITY,%DecDiv(D.CE_DAY,EE.AOQ)))) + (%DecMult(A.OP_TIME,%DecDiv(D.CE_HR,EE.AOQ))) + (%DecDiv(D.CE_INV_MIN,(%DecMult((A.OP_TIME + A.OP_RATE),60)))) + (%DecDiv(D.CE_INV_HR,(A.OP_TIME +A.OP_RATE))) + (%DecMult(%DecDiv(C.DAILY_CAPACITY,(A.OP_TIME+A.OP_RATE)),D.CE_INV_DY)) ),F.CE_AOQ_YES) + (%DecMult(( ( %DecMult(%DecDiv(A.OP_TIME,60),D.CE_MIN)) + (%DecMult(A.OP_TIME, %DecMult(C.DAILY_CAPACITY,D.CE_DAY))) + (%DecMult(A.OP_TIME,D.CE_HR)) + (%DecDiv(D.CE_INV_MIN,(%DecMult((A.OP_TIME + A.OP_RATE),60)))) + (%DecDiv(D.CE_INV_HR,(A.OP_TIME +A.OP_RATE))) + (%DecMult(%DecDiv(C.DAILY_CAPACITY,(A.OP_TIME+A.OP_RATE)),D.CE_INV_DY))) ,F.CE_AOQ_NO )) ),15) FROM PS_SF_OP_TIME A , PS_SF_OP_LIST B , PS_EN_WORK_CENTER C , PS_CE_HR_CONV D , PS_SF_PRDNID_HEADR E , PS_CE_HR_CONV F , PS_BU_ITEMS_INV EE WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PRODUCTION_ID = B.PRODUCTION_ID AND A.OP_SEQUENCE = B.OP_SEQUENCE AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.WORK_CENTER_CODE = C.WORK_CENTER_CODE AND A.OP_TIME_UNIT = D.OP_TIME_UNIT AND A.OP_RATE_UNIT = D.OP_RATE_UNIT AND D.CE_SOURCE = 'HOURS' AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.PRODUCTION_ID = E.PRODUCTION_ID AND F.CE_SOURCE = 'CREW' AND A.TIME_TYPE = F.CONVERSION_CODE AND E.BUSINESS_UNIT=EE.BUSINESS_UNIT AND E.INV_ITEM_ID=EE.INV_ITEM_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | PRODUCTION_ID | Character(10) | VARCHAR2(10) NOT NULL | Production ID |
| 3 | NEW_PRDN_QTY | Number(12,4) | DECIMAL(11,4) NOT NULL | Production Start Qty |
| 4 | OP_SEQUENCE | Number(4,0) | SMALLINT NOT NULL | Operation Sequence |
| 5 | WORK_CENTER_CODE | Character(10) | VARCHAR2(10) NOT NULL | Work Center |
| 6 | CONVERSION_CODE | Character(4) | VARCHAR2(4) NOT NULL | Conversion Code |
| 7 | DAILY_CAPACITY | Number(5,2) | DECIMAL(4,2) NOT NULL | Average Daily Hours |
| 8 | CREWSIZE | Number(7,2) | DECIMAL(6,2) NOT NULL | Crew Size |
| 9 | MACH_RESOURCES | Number(7,2) | DECIMAL(6,2) NOT NULL | Machine Resources |
| 10 | AOQ | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Average Order Qty |
| 11 | TIME_TYPE | Character(4) | VARCHAR2(4) NOT NULL |
Time / Resource Type
0100=Planning Labor Setup 0200=Planning Labor Fixed Run 0300=Planning Labor Run 0400=Planning Labor Post Prod 0500=Planning Machine Setup 0600=Planning Machine Fixed Run 0700=Planning Machine Run 0800=Planning Machine Post Prod 1100=Costing Labor Setup 1200=Costing Labor Fixed Run 1300=Costing Labor Run 1400=Costing Labor Post Prod 1500=Costing Machine Setup 1600=Costing Machine Fixed Run 1700=Costing Machine Run 1800=Costing Machine Post Prod 2000=Queue Time 3000=Intransit Time |
| 12 | OP_TIME | Number(9,2) | DECIMAL(8,2) NOT NULL | Op Time |
| 13 | OP_RATE | Number(9,2) | DECIMAL(8,2) NOT NULL | Op Rate |
| 14 | OP_TIME_UNIT | Character(2) | VARCHAR2(2) NOT NULL |
Time Unit
DY=Days HR=Hours MN=Minutes |
| 15 | OP_RATE_UNIT | Character(2) | VARCHAR2(2) NOT NULL |
Op Rate Unit
UD=Units/Day UH=Units/Hour UM=Units/Min |
| 16 | CE_HOURS_SPENT | Signed Number(33,15) | DECIMAL(31,15) NOT NULL | Hours Spent |