CM_CST_PPVC_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. 05/02/00 tw CN#R-ANURUD-6X2J6 Added chartfields & cost category. 07/02 MXS B1280/RES102536/INC70181002 - When a receipt is cancelled, PPV transaction is not reversed 04/16/2013 - VID 16465791 - When Insp item is Return to Vendor, PPV transaction not Reversed

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 , B.COST_ELEMENT ,'C' , B.CM_DT_TIMESTAMP , B.CM_SEQ_NBR , A.SOURCE_BUS_UNIT , E.QTY_BASE , 0-(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.DESTIN_GL_BU , A.FROM_PRDN_ID , A.TO_PRDN_ID , A.DESTIN_BU , A.STORAGE_AREA , A.CONSIGNED_FLAG , %subrec(CM_TRANS_IN_SBR, A) , A.SHIP_ID FROM PS_BUS_UNIT_TBL_IN G , PS_SET_CNTRL_REC C , PS_TRANSACTION_INV A , PS_CM_DEP_VAR_COST B , PS_CM_ELEMENT D , PS_CM_DEPLETE E WHERE G.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME='CM_ELEMENT' AND %Join(COMMON_KEYS, BUS_UNIT_TBL_IN G, CM_DEP_VAR_COST B) AND 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 D.SETID=C.SETID AND D.COST_ELEMENT = B.COST_ELEMENT AND %Join(COMMON_KEYS, CM_DEP_VAR_COST B, CM_DEPLETE E) AND E.CM_COST_PROC_GROUP IN ('DEPLETES', 'RTV') AND E.TRANSACTION_GROUP IN ('020', '010', '012','013') AND B.POSTED_FLAG = 'N' AND B.TRANSACTION_GROUP = '400' 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 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
11 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
12 CM_DT_TIMESTAMP DateTime(26) TIMESTAMP NOT NULL Receipt dttm.
13 CM_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
14 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
15 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
16 CE_EXTENDED_COST Signed Number(32,15) DECIMAL(30,15) NOT NULL Extended Cost
17 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
18 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

Prompt Table: CM_DISTR_TYPE

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

Prompt Table: DEPTID_BUGL_VW

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

Prompt Table: OPERUNT_BUGL_VW

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

Prompt Table: PRODUCT_BUGL_VW

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

Prompt Table: FUND_BUGL_VW

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

Prompt Table: CLASSCF_BUGL_VW

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

Prompt Table: PROGRAM_BUGL_VW

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

Prompt Table: BUD_REF_BUGL_VW

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: CF1_BUGL_VW

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

Prompt Table: CF2_BUGL_VW

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

Prompt Table: CF3_BUGL_VW

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

Prompt Table: %EDIT_BU_PC

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

Prompt Table: %EDIT_PROJECT

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

Prompt Table: %EDIT_ACTIVITY

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

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

42 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
43 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
44 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
45 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
46 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
47 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
48 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
49 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
50 DESTIN_GL_BU Character(5) VARCHAR2(5) NOT NULL Destination GL Business Unit
51 FROM_PRDN_ID Character(10) VARCHAR2(10) NOT NULL From Production ID
52 TO_PRDN_ID Character(10) VARCHAR2(10) NOT NULL To Production ID
53 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
54 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
55 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag

Default Value: N

56 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID
57 COUNTING_EVENT_ID Number(15,0) DECIMAL(15) NOT NULL Counting Event ID
58 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
59 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
60 FROM_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL From Production Area
61 FROM_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL From Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
62 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
63 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
64 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
65 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
66 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
67 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
68 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
69 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
70 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
71 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
72 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
73 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
74 STAGED_DATE Date(10) DATE Staged Date
75 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
76 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
77 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
78 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
79 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
80 TO_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL To Production Area
81 TO_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL To Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
82 TO_STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
83 TO_STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
84 TO_STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
85 TO_STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
86 TO_STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
87 TRANSACTION_SOURCE Character(2) VARCHAR2(2) NOT NULL Transaction Source
88 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
89 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID