CM_CST_RTVV_VW

(SQL View)
 Index Back

Costed PPV View

CN# CM750-10.0 PSG 11/10/97 - multiply PxQ in COBOL instead of the view to aviod error when there are too many decimals. (removed field total_ap_cost) Add conversion_rate. PGaynor 4/5/98: added '020' and '013' to WHERE. dbe 7/21/1999 CN# R-VRAJU--PR0Q1 - Performance enhancement. 07/21/99 ebn CN#CM800-9.0 Changed view for LIFO/FIFO Enhancement to use CM_VARIANC_COST instead of COST_INV. 08/05/99 ebn CN#CM800-1.0 Added budget_dt for commitment control. 07/04 dw Bundle:4458 Res:158561 ICE: 682035000. Get chartfields from RTV_LN_DISTRIB instead of CM_COST_VARIANC 05/02/00 tw CN#R-ANURUD-6X2J6 Added chartfields & cost category. 08/23/05 tw Res627690/Inc1325732002 Fixes related to rtv variances.

SELECT B.BUSINESS_UNIT , B.INV_ITEM_ID , B.CM_BOOK , B.DT_TIMESTAMP , B.SEQ_NBR , B.CM_DT_TIMESTAMP , B.CM_SEQ_NBR , B.CM_DT_TIMESTAMP_A , B.CM_SEQ_NBR_A , B.CM_SEQ_COST , B.TRANSACTION_GROUP , B.COST_ELEMENT , A.SOURCE_BUS_UNIT , E.QTY_BASE , %Round(-(B.CM_UNIT_COST - B.CM_UNIT_COST_STD - B.CM_UNIT_COST_VSUM),15) , A.UNIT_MEASURE_STD , A.DISTRIB_TYPE , A.BUDGET_DT , F.ACCOUNT , F.ALTACCT , F.DEPTID , %subrec(CF16_AN_SBR,F) , %subrec(PC_CF1_N_SBR,F) , 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_DEP_VAR_COST B , PS_SET_CNTRL_REC C , PS_CM_ELEMENT D , PS_CM_DEP_VAR E , PS_RTV_LN_DISTRIB F , 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.DT_TIMESTAMP AND A.SEQ_NBR = B.SEQ_NBR AND A.BUSINESS_UNIT_PO = F.BUSINESS_UNIT AND A.RTV_ID = F.RTV_ID AND A.RTV_LN_NBR = F.RTV_LN_NBR AND A.RTV_DISTRIB_NUM = F.RTV_DISTRIB_NUM AND B.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME='CM_ELEMENT' AND D.SETID=C.SETID AND D.COST_ELEMENT = B.COST_ELEMENT AND %Join(COMMON_KEYS, CM_DEP_VAR_COST B, CM_DEP_VAR E) AND %Join(COMMON_KEYS, TRANSACTION_INV A, BUS_UNIT_TBL_IN G) AND B.POSTED_FLAG = 'N' AND B.TRANSACTION_GROUP = '415' 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 DateTime(26) TIMESTAMP NOT NULL Receipt dttm.
7 CM_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
8 CM_DT_TIMESTAMP_A DateTime(26) TIMESTAMP NOT NULL 07/99 GMG CN800-9.0: Original putaway dttm when bin-to-bin'd.
9 CM_SEQ_NBR_A Number(15,0) DECIMAL(15) NOT NULL 07/99 GMG CN800-9.0:
10 CM_SEQ_COST Number(15,0) DECIMAL(15) NOT NULL Cost Sequence Number. Incremented each time the actual cost is updated.
11 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
12 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
13 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
14 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
15 CE_EXTENDED_COST Signed Number(32,15) DECIMAL(30,15) NOT NULL Extended Cost
16 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
17 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

Prompt Table: CM_DISTR_TYPE

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

Prompt Table: DEPTID_BUGL_VW

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

Prompt Table: OPERUNT_BUGL_VW

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

Prompt Table: PRODUCT_BUGL_VW

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

Prompt Table: FUND_BUGL_VW

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

Prompt Table: CLASSCF_BUGL_VW

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

Prompt Table: PROGRAM_BUGL_VW

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

Prompt Table: BUD_REF_BUGL_VW

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: CF1_BUGL_VW

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

Prompt Table: CF2_BUGL_VW

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

Prompt Table: CF3_BUGL_VW

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

Prompt Table: %EDIT_BU_PC

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

Prompt Table: %EDIT_PROJECT

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

Prompt Table: %EDIT_ACTIVITY

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

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

41 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
42 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
43 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
44 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
45 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
46 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
47 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
48 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
49 FROM_PRDN_ID Character(10) VARCHAR2(10) NOT NULL From Production ID
50 TO_PRDN_ID Character(10) VARCHAR2(10) NOT NULL To Production ID
51 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
52 DESTIN_GL_BU Character(5) VARCHAR2(5) NOT NULL Destination GL Business Unit
53 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
54 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
55 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID
56 COUNTING_EVENT_ID Number(15,0) DECIMAL(15) NOT NULL Counting Event ID
57 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
58 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
59 FROM_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL From Production Area
60 FROM_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL From Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
61 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
62 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
63 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
64 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
65 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
66 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
67 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
68 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
69 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
70 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
71 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
72 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
73 STAGED_DATE Date(10) DATE Staged Date
74 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
75 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
76 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
77 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
78 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
79 TO_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL To Production Area
80 TO_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL To Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
81 TO_STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
82 TO_STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
83 TO_STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
84 TO_STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
85 TO_STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
86 TRANSACTION_SOURCE Character(2) VARCHAR2(2) NOT NULL Transaction Source
87 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
88 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID