CE_CONV_CST_VW

(SQL View)
 Index Back

Conversion Costs

from ce_rtg_hrs_vw2

SELECT A.BUSINESS_UNIT ,'CNV' ,A.INV_ITEM_ID ,A.RTG_CODE ,A.RTG_TYPE ,A.RTG_STATE ,A.CONVERSION_CODE ,A.OP_SEQUENCE ,A.TIME_TYPE ,B.CE_COST_TYPE ,B.COST_VERSION ,C.COST_ELEMENT ,A.CE_TOTAL_HOURS ,C.RATE_AMOUNT ,C.RATE_TYPE ,A.AOQ ,%DecDiv(%DecMult(A.CE_TOTAL_HOURS,%DecMult(C.RATE_AMOUNT,%DecMult(D.CE_RATE_HR,100)))+%DecMult(C.RATE_AMOUNT,%DecMult(D.CE_RATE_UN,100)),100) FROM ( SELECT G.BUSINESS_UNIT ,G.INV_ITEM_ID ,G.RTG_CODE ,G.OP_SEQUENCE ,G.RTG_TYPE ,G.RTG_STATE ,G.WORK_CENTER_CODE ,G.CONVERSION_CODE ,G.DAILY_CAPACITY ,G.CREWSIZE ,G.MACH_RESOURCES ,G.AOQ ,G.TIME_TYPE ,G.OP_TIME ,G.OP_RATE ,G.OP_TIME_UNIT ,G.OP_RATE_UNIT ,G.CE_HOURS_SPENT ,%DecMult(G.CE_HOURS_SPENT,%DecMult(G.CREWSIZE,H.CE_CREW))+%DecMult(G.CE_HOURS_SPENT,%DecMult(G.MACH_RESOURCES,H.CE_MACH))+%DecMult(G.CE_HOURS_SPENT,H.CE_NOCALC_CREWMACH) AS CE_TOTAL_HOURS FROM ( SELECT A.BUSINESS_UNIT ,A.INV_ITEM_ID ,A.RTG_CODE ,A.RTG_TYPE ,A.RTG_STATE ,A.OP_SEQUENCE ,B.WORK_CENTER_CODE ,B.CONVERSION_CODE ,C.DAILY_CAPACITY ,B.CREWSIZE ,B.MACH_RESOURCES ,E.AOQ ,A.TIME_TYPE ,A.OP_TIME ,A.OP_RATE ,A.OP_TIME_UNIT ,A.OP_RATE_UNIT ,(%DecMult( ( (%DecMult(%DecDiv(A.OP_TIME,60),%DecDiv(D.CE_MIN,E.AOQ))) + (%DecMult(A.OP_TIME,%DecMult(C.DAILY_CAPACITY,%DecDiv(D.CE_DAY,E.AOQ)))) + (%DecMult(A.OP_TIME,%DecDiv(D.CE_HR,E.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 )) ) AS CE_HOURS_SPENT FROM PS_EN_RTG_TIME A , PS_EN_RTG_OPS B , PS_EN_WORK_CENTER C , PS_CE_HR_CONV D , PS_BU_ITEMS_INV E , PS_CE_HR_CONV F WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.RTG_TYPE = B.RTG_TYPE AND A.RTG_CODE = B.RTG_CODE AND A.RTG_STATE = B.RTG_STATE 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.INV_ITEM_ID = E.INV_ITEM_ID AND F.CE_SOURCE = 'CREW' AND A.TIME_TYPE = F.CONVERSION_CODE AND A.OP_TIME > 0 ) G , PS_CE_HR_CONV H WHERE H.CE_SOURCE = 'CREW' AND G.TIME_TYPE = H.CONVERSION_CODE ) A , PS_CE_VERSION B , PS_CE_CONRATE C , PS_CE_HR_CONV D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.CE_COST_TYPE = C.CE_COST_TYPE AND B.COST_VERSION = C.COST_VERSION AND A.CONVERSION_CODE = C.CONVERSION_CODE AND A.TIME_TYPE = C.CONRATE_TYPE AND D.CE_SOURCE = 'RATES' AND C.RATE_TYPE = D.RATE_TYPE

# 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 CONVERSION_CODE Character(4) VARCHAR2(4) NOT NULL Conversion Code
8 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
9 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
10 CE_COST_TYPE Character(4) VARCHAR2(4) NOT NULL Cost Type
11 COST_VERSION Character(10) VARCHAR2(10) NOT NULL Cost Version
12 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
13 CE_TOTAL_HOURS Signed Number(33,15) DECIMAL(31,15) NOT NULL Total Hours
14 RATE_AMOUNT Number(11,4) DECIMAL(10,4) NOT NULL Rate Amount
15 RATE_TYPE Character(2) VARCHAR2(2) NOT NULL Rate Per
HR=Hourly
UN=Per Unit
16 AOQ Signed Number(17,4) DECIMAL(15,4) NOT NULL Average Order Qty
17 CE_EXTENDED_COST Signed Number(32,15) DECIMAL(30,15) NOT NULL Extended Cost