CM_CST_ERVI_VW

(SQL View)
 Index Back

Costed ERV View for IFRS

This view is to extract ERV for Actual costed items for IFRS feature. (Transaction group - 406)

SELECT B.BUSINESS_UNIT , B.INV_ITEM_ID , B.CM_BOOK , B.DT_TIMESTAMP , B.SEQ_NBR , B.CM_DT_TIMESTAMP_A , B.CM_SEQ_NBR_A , B.CM_SEQ_COST , B.TRANSACTION_GROUP ,'R' , B.COST_ELEMENT , A.SOURCE_BUS_UNIT , E.QTY_BASE , (B.CM_UNIT_COST - B.CM_UNIT_COST_STD - B.CM_UNIT_COST_VSUM) , A.UNIT_MEASURE_STD , A.DISTRIB_TYPE , A.BUDGET_DT , A.ACCOUNT , A.ALTACCT , A.DEPTID , %subrec(CF16_AN_SBR,A) , %subrec(pc_cf1_n_SBR, A) , D.COST_CATEGORY , B.POSTED_FLAG , B.PROCESS_INSTANCE , G.BUSINESS_UNIT_GL , G.BASE_CURRENCY , G.BASE_CURRENCY , G.APPL_JRNL_ID , %DatePart(B.DT_TIMESTAMP) , A.FROM_PRDN_ID , A.TO_PRDN_ID , A.CONSIGNED_FLAG , A.DESTIN_GL_BU , A.DESTIN_BU , A.STORAGE_AREA , %subrec(CM_TRANS_IN_SBR, A) , A.SHIP_ID FROM PS_TRANSACTION_INV A , PS_CM_VARIANC_COST B , PS_SET_CNTRL_REC C , PS_CM_ELEMENT D , PS_CM_VARIANCES E , PS_BUS_UNIT_TBL_IN G WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DT_TIMESTAMP = B.CM_DT_TIMESTAMP_A AND A.SEQ_NBR = B.CM_SEQ_NBR_A AND B.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME='CM_ELEMENT' AND D.SETID=C.SETID AND D.COST_ELEMENT = B.COST_ELEMENT AND B.INV_ITEM_ID = E.INV_ITEM_ID AND B.CM_BOOK = E.CM_BOOK AND B.DT_TIMESTAMP = E.DT_TIMESTAMP AND B.SEQ_NBR = E.SEQ_NBR AND B.CM_DT_TIMESTAMP_A = E.CM_DT_TIMESTAMP_A AND B.CM_SEQ_NBR_A = E.CM_SEQ_NBR_A AND %Join(COMMON_KEYS, TRANSACTION_INV A, BUS_UNIT_TBL_IN G) AND B.POSTED_FLAG = 'N' AND B.TRANSACTION_GROUP = '406' AND E.TRANSACTION_GROUP = '406' AND B.CM_COST_MODE = 'L'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
3 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book
4 DT_TIMESTAMP DateTime(26) TIMESTAMP NOT NULL Date Timestamp
5 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
6 CM_DT_TIMESTAMP_A DateTime(26) TIMESTAMP NOT NULL 07/99 GMG CN800-9.0: Original putaway dttm when bin-to-bin'd.
7 CM_SEQ_NBR_A Number(15,0) DECIMAL(15) NOT NULL 07/99 GMG CN800-9.0:
8 CM_SEQ_COST Number(15,0) DECIMAL(15) NOT NULL Cost Sequence Number. Incremented each time the actual cost is updated.
9 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
10 ADJUST_TYPE Character(1) VARCHAR2(1) NOT NULL Adjustment Type
0=CM Only
A=VMI Return
D=Decrease
I=Increase
M=Misc Issue
R=Misc Return
S=Scrap
V=Scrap RTV
11 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
12 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
13 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
14 CE_EXTENDED_COST Signed Number(32,15) DECIMAL(30,15) NOT NULL Extended Cost
15 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
16 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

Prompt Table: CM_DISTR_TYPE

17 BUDGET_DT Date(10) DATE Budget Date
18 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
19 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL_VW

21 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPERUNT_BUGL_VW

22 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_BUGL_VW

23 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_BUGL_VW

24 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASSCF_BUGL_VW

25 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_BUGL_VW

26 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_BUGL_VW

27 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

28 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

29 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

30 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CF1_BUGL_VW

31 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CF2_BUGL_VW

32 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CF3_BUGL_VW

33 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: %EDIT_BU_PC

34 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: %EDIT_PROJECT

35 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: %EDIT_ACTIVITY

36 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

37 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

38 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

39 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

40 COST_CATEGORY Character(3) VARCHAR2(3) NOT NULL 1/12/99 ebn CN#CM800-2.0 : Added Landed Cost translate value
ATC=Addl Trans
COH=Con Ovhd
CON=Conversion
INB=Inbound
LND=Landed
MAT=Material
OTH=Other
OUB=Outbound
41 POSTED_FLAG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a Receivables item has been posted. This field is automatically updated by the Receivables Update program.
N=Not Posted
X=Don't Post
Y=Posted
42 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
43 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
44 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
45 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
46 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
47 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
48 FROM_PRDN_ID Character(10) VARCHAR2(10) NOT NULL From Production ID
49 TO_PRDN_ID Character(10) VARCHAR2(10) NOT NULL To Production ID
50 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
51 DESTIN_GL_BU Character(5) VARCHAR2(5) NOT NULL Destination GL Business Unit
52 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
53 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
54 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID
55 COUNTING_EVENT_ID Number(15,0) DECIMAL(15) NOT NULL Counting Event ID
56 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
57 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
58 FROM_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL From Production Area
59 FROM_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL From Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
60 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
61 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
62 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
63 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
64 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
65 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
66 PRODUCTION_TYPE Character(2) VARCHAR2(2) NOT NULL JNW 11/1/98 (CN#SF800-13) activated teardown xlat
PR=Production
RW=Rework
SV=Service
TD=Teardown
67 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
68 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
69 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
70 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
71 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
72 STAGED_DATE Date(10) DATE Staged Date
73 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
74 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
75 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
76 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
77 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
78 TO_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL To Production Area
79 TO_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL To Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
80 TO_STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
81 TO_STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
82 TO_STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
83 TO_STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
84 TO_STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
85 TRANSACTION_SOURCE Character(2) VARCHAR2(2) NOT NULL Transaction Source
86 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
87 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID