CE_TOTOHLB_VW(SQL View) |
Index Back |
|---|---|
Labor oh from ce_conv_cost_vw |
| SELECT A.BUSINESS_UNIT ,'OVH' ,A.INV_ITEM_ID ,A.RTG_CODE ,A.RTG_TYPE ,A.RTG_STATE ,A.CE_COST_TYPE ,A.COST_VERSION ,A.OP_SEQUENCE ,B.OVHD_RATE_CODE ,B.COST_ELEMENT ,SUM(A.CE_TOTAL_HOURS) ,SUM(A.CE_EXTENDED_COST) ,B.OH_RATE_TYPE ,B.OVERHEAD_RATE ,B.OVERHEAD_PERCENT ,A.AOQ ,(%DecMult(SUM(A.CE_EXTENDED_COST), %DecMult(%DecDiv(B.OVERHEAD_PERCENT,100),D.CE_PERCENT_LAB)) + (%DecMult(SUM(A.CE_TOTAL_HOURS), %DecMult(B.OVERHEAD_RATE, D.CE_LAB_HR)))+ %DecMult(B.OVERHEAD_RATE,D.CE_PER_UNIT)) FROM PS_CE_CONV_COST_VW A ,PS_CE_CONOH_RATE B ,PS_EN_RTG_OPS C ,PS_CE_OH_CONV D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.CE_COST_TYPE = B.CE_COST_TYPE AND A.COST_VERSION = B.COST_VERSION AND B.OH_RATE_TYPE IN ('%L', 'PL', 'PU') AND A.TIME_TYPE IN ('1100','1200','1300','1400') AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.RTG_CODE = C.RTG_CODE AND A.RTG_TYPE = C.RTG_TYPE AND A.RTG_STATE = C.RTG_STATE AND A.OP_SEQUENCE = C.OP_SEQUENCE AND ( C.CON_OH_CODE_1 = B.OVHD_RATE_CODE OR C.CON_OH_CODE_2 = B.OVHD_RATE_CODE OR C.CON_OH_CODE_3 = B.OVHD_RATE_CODE OR C.CON_OH_CODE_4 = B.OVHD_RATE_CODE) AND D.CE_SOURCE = 'OH' AND B.OH_RATE_TYPE = D.OH_RATE_TYPE GROUP BY A.BUSINESS_UNIT ,A.INV_ITEM_ID ,A.RTG_CODE ,A.RTG_TYPE ,A.RTG_STATE ,A.CE_COST_TYPE ,A.COST_VERSION ,B.COST_ELEMENT ,B.OH_RATE_TYPE ,B.OVERHEAD_RATE ,B.OVERHEAD_PERCENT ,A.OP_SEQUENCE ,B.OVHD_RATE_CODE ,D.CE_PERCENT_LAB ,D.CE_LAB_HR, A.AOQ, D.CE_PER_UNIT, D.CE_PERCENT_MACH, D.CE_MACH_HR |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | CE_COST_CAT | Character(3) | VARCHAR2(3) NOT NULL |
Costing Catagory
ADD=Mat Add'l Cost CNV=Direct Conversion Costs LAN=Landed Costs MAT=Materials Catagory OVH=Conversion Overhead |
| 3 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 4 | RTG_CODE |
Number(2,0) | SMALLINT NOT NULL | Routing Code |
| 5 | RTG_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Routing Type
PR=Production RW=Rework TD=Teardown |
| 6 | RTG_STATE | Character(2) | VARCHAR2(2) NOT NULL |
Routing State
EN=Engineering PR=Manufacturing |
| 7 | CE_COST_TYPE | Character(4) | VARCHAR2(4) NOT NULL | Cost Type |
| 8 | COST_VERSION | Character(10) | VARCHAR2(10) NOT NULL | Cost Version |
| 9 | OP_SEQUENCE |
Number(4,0) | SMALLINT NOT NULL | Operation Sequence |
| 10 | OVHD_RATE_CODE | Character(4) | VARCHAR2(4) NOT NULL | Conversion Ovhd Rate Code |
| 11 | COST_ELEMENT | Character(4) | VARCHAR2(4) NOT NULL | Cost Element |
| 12 | CE_TOTAL_HOURS | Signed Number(33,15) | DECIMAL(31,15) NOT NULL | Total Hours |
| 13 | CE_CONV_COST | Signed Number(33,15) | DECIMAL(31,15) NOT NULL | Conversion Cost |
| 14 | OH_RATE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Conversion Ovhd Rate Type
%L=% of Labor Amt %M=% of Mach Amt PL=Amt Per Labor Hr PM=Amt Per Mach Hr PU=Per Unit Amt |
| 15 | OVERHEAD_RATE | Number(11,4) | DECIMAL(10,4) NOT NULL | Conversion Overhead Rate |
| 16 | OVERHEAD_PERCENT | Number(5,1) | DECIMAL(4,1) NOT NULL | Conversion Overhead % |
| 17 | AOQ | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Average Order Qty |
| 18 | CE_EXTENDED_COST | Signed Number(32,15) | DECIMAL(30,15) NOT NULL | Extended Cost |