CM_CST_PPVA_VW

(SQL View)
 Index Back

Costed PPV (Avg Cst Itms) View

This view select PPV from cm_cost_adj for Average Costed Items. Average Cosed items are excluded from CM_CST_PPV_VW, which selects from COST_INV. PSG 11/3/97 CN# CM750-10.0 added trans_line_type to differentiate between PPV and ERV. 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. PSG 2/11/98: Added transaction_group. GMG 10/23/98 R-VCECEN-KN717; Added process_flag. It needs to be reset to 'N' if an accounting line could not be created from the row. KDN CM800-7.1: Added ALTACCT field. 07/21/99 ebn CN#CM800-9.0 Incorporated table CM_COST_ADJB into view.

SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_BOOK , A.COST_ELEMENT , A.TRANSACTION_GROUP , B.QTY_BASE , C.CM_UNIT_COST_POVO , B.UNIT_MEASURE_STD , B.DISTRIB_TYPE , B.ACCOUNT , B.ALTACCT , B.DEPTID , %subrec(CF16_AN_SBR,B) , %subrec(PC_CF1_N_SBR,B) , B.STORAGE_AREA , B.ADJ_CODE , A.POSTED_FLAG , A.PROCESS_INSTANCE , A.PROCESS_FLAG , G.BUSINESS_UNIT_GL , G.BASE_CURRENCY , G.BASE_CURRENCY , G.APPL_JRNL_ID , %DatePart(B.DT_TIMESTAMP) , %subrec(CM_COSTADJ_SBR,B) FROM PS_CM_COST_ADJB A , PS_CM_COST_ADJ B , PS_CM_PERPADJ_TMP C , PS_BUS_UNIT_TBL_IN G WHERE %Join(COMMON_KEYS, CM_COST_ADJB A, CM_COST_ADJ B) AND %Join(COMMON_KEYS, CM_COST_ADJ B, BUS_UNIT_TBL_IN G) AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.DT_TIMESTAMP = C.CM_DT_TIMESTAMP_P AND A.COST_ELEMENT = C.COST_ELEMENT AND A.CM_BOOK = C.CM_BOOK AND B.STORAGE_AREA = C.STORAGE_AREA AND B.UNIT_MEASURE_STD = C.UNIT_MEASURE_STD AND A.TRANSACTION_GROUP = B.TRANSACTION_GROUP AND A.POSTED_FLAG = 'N' AND A.TRANSACTION_GROUP = '401' AND A.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 DT_TIMESTAMP DateTime(26) TIMESTAMP NOT NULL Date Timestamp
4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
5 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book
6 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
7 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
8 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
9 CE_EXTENDED_COST Signed Number(32,15) DECIMAL(30,15) NOT NULL Extended Cost
10 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
11 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

Prompt Table: CM_DISTR_TYPE

12 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
13 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL_VW

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

Prompt Table: OPERUNT_BUGL_VW

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

Prompt Table: PRODUCT_BUGL_VW

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

Prompt Table: FUND_BUGL_VW

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

Prompt Table: CLASSCF_BUGL_VW

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

Prompt Table: PROGRAM_BUGL_VW

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

Prompt Table: BUD_REF_BUGL_VW

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: CF1_BUGL_VW

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

Prompt Table: CF2_BUGL_VW

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

Prompt Table: CF3_BUGL_VW

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

Prompt Table: %EDIT_BU_PC

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

Prompt Table: %EDIT_PROJECT

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

Prompt Table: %EDIT_ACTIVITY

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

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

34 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
35 ADJ_CODE Character(1) VARCHAR2(1) NOT NULL Adjustment Method
D=Cost Adj - Decrease
I=Cost Adj - Increase
N=Cost Adj - New Cost
P=PPV Update
W=Write Off
36 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
37 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
38 PROCESS_FLAG Character(1) VARCHAR2(1) NOT NULL Process Flag
A=Purge All
S=Only purge items with at least
39 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
40 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
41 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
42 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
43 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
44 AP_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Payables Sequence Number
45 TRANS_LINE_TYPE Character(3) VARCHAR2(3) NOT NULL Transaction Line Type
0=Regular
1=Interunit Transfer
2=Gain
3=Loss
4=PPV (Favorable)
5=PPV (Unfavorable)
6=ERV (Favorable)
7=ERV (Unfavorable)
8=InterCompany Transfer