EN_ITEMCOST1_VW

(SQL View)
 Index Back

Sum of Item Cost by Cost Type

/* Fix bug EPNT084-VQ0OQ , eyz 2/17/98 This view is for non-configured items only and should not contain config code. IT should not contain Floor Stock Item either.*/ 05/17/99 Lee CN# EN800-4.0 exclude source_code 3 (expensed) and 6 (planning). 6/3/99 cel Added bom_code and rtg_code as fields/keys 09/19/00 cel Modified the view to use the tlp and llp cost columns instead of the tl, ll cost columns. Cost rollup now uses these new columns which normally match the other columns except for phantom costs which can have a higher precision.

SELECT A.BUSINESS_UNIT , A.CE_COST_TYPE , A.COST_VERSION , A.BOM_CODE , A.RTG_CODE , A.INV_ITEM_ID , SUM(A.TL_COST) , SUM(A.TL_COST)+SUM(A.LL_COST) , SUM(A.TLP_COST) , SUM(A.TLP_COST)+SUM(A.LLP_COST) FROM PS_CE_ITEMCOST_DET A , PS_BU_ITEMS_INV B WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.CONFIG_CODE = ' ' AND B.SOURCE_CODE NOT IN('3', '5','6') GROUP BY A.BUSINESS_UNIT, A.CE_COST_TYPE, A.COST_VERSION, A.BOM_CODE, A.RTG_CODE,A.INV_ITEM_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_IN

2 CE_COST_TYPE Character(4) VARCHAR2(4) NOT NULL Cost Type

Prompt Table: CE_TYPE

3 COST_VERSION Character(10) VARCHAR2(10) NOT NULL Cost Version
4 BOM_CODE Number(2,0) SMALLINT NOT NULL CN# EN800-3.0, anna 09/21 added new field to database
5 RTG_CODE Number(2,0) SMALLINT NOT NULL Routing Code
6 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: MG_ITEM_OWN_VW

7 TL_COST Number(15,4) DECIMAL(14,4) NOT NULL This Level Cost
8 TL_LL_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL This/Lower Level Costs
9 TLP_COST Number(19,8) DECIMAL(18,8) NOT NULL This Level Cost
10 TLP_LLP_COST Signed Number(20,8) DECIMAL(18,8) NOT NULL This/Lower Level Costs