CM_ACCT_VAR_VW (SQL View)	Index Back
Variance Transaction Entries GMG CN# CM750-8.0 Drilldown; View joins Variance transactions to Accounting Lines to show the Entries caused by a transaction. KDN CM800-7.1: Added ALTACCT & AFFILIATE fields. 07/21/99 ebn CN#CM800-9.0 Changed join to reflect changes made to sf_variances keys. 07/29/99 ej CN#CM800-9.0 Added TRANS_DATE, TRANS_TIME and changed SQL accordingly 10/07/99 ej CN#CM800-9.0 Replace CM_ACCTG_GRP with CM_ACCTG_GRP_D 11/30/01 8.4 for row level security, change the bu prompt table from CM_DRILL_BU_VW to SP_CMACTG_NONVW

CM_ACCT_VAR_VW

(SQL View)

Variance Transaction Entries

GMG CN# CM750-8.0 Drilldown; View joins Variance transactions to Accounting Lines to show the Entries caused by a transaction. KDN CM800-7.1: Added ALTACCT & AFFILIATE fields. 07/21/99 ebn CN#CM800-9.0 Changed join to reflect changes made to sf_variances keys. 07/29/99 ej CN#CM800-9.0 Added TRANS_DATE, TRANS_TIME and changed SQL accordingly 10/07/99 ej CN#CM800-9.0 Replace CM_ACCTG_GRP with CM_ACCTG_GRP_D 11/30/01 8.4 for row level security, change the bu prompt table from CM_DRILL_BU_VW to SP_CMACTG_NONVW

SELECT A.BUSINESS_UNIT , A.PRODUCTION_ID , A.VARIANCE_TYPE , A.COST_ELEMENT , A.VARIANCE_SOURCE , A.TRANS_DATE , A.TRANS_TIME , A.SEQ_NBR , A.INV_ITEM_ID , A.DT_TIMESTAMP , A.TRANSACTION_GROUP , A.ACCOUNTING_DT , A.ACCTG_LINE_NO , A.DISTRIB_TYPE , A.INV_ITEM_GROUP , A.TRANS_LINE_TYPE , A.AP_SEQ_NBR , A.DESTIN_BU , A.DEBIT_CREDIT , A.ACCOUNT , A.ALTACCT , A.DEPTID , %subrec(CF16_AN_SBR,a) , %subrec(PC_CF1_N_SBR,a) , A.STATISTICS_CODE , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.STATISTIC_AMOUNT , A.BUSINESS_UNIT_GL , A.LEDGER , A.JOURNAL_ID , A.JOURNAL_DATE , A.JOURNAL_LINE , A.CM_BOOK FROM PS_CM_ACCTG_LINE A , PS_SF_VARIANCES B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PRODUCTION_ID = B.PRODUCTION_ID AND A.VARIANCE_TYPE = B.VARIANCE_TYPE AND A.COST_ELEMENT = B.COST_ELEMENT AND A.VARIANCE_SOURCE = B.VARIANCE_SOURCE AND A.TRANS_DATE = B.TRANS_DATE AND A.TRANS_TIME = B.TRANS_TIME AND A.SEQ_NBR = B.SEQ_NBR AND A.TRANSACTION_GROUP IN ( SELECT TRANSACTION_GROUP FROM PS_CM_ACCTG_GRP_D WHERE CM_SOURCE_RECORD = 'SF_VARIANCES')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
Prompt Table: SP_CMACTG_NONVW

2 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
Prompt Table: CM_DRILL_PID_VW

3 VARIANCE_TYPE Character(2) VARCHAR2(2) NOT NULL 9/9/02 CRC: added variance type '15' for Yield Variance
01=Configuration Variance
02=Usage Variance
03=Component Yield Variance
04=Labor Lot Size Variance
05=Process Routing Variance
06=Efficiency Variance
07=Rate Variance
08=Material Lot Size Variance
09=Rework Expense
10=Rework Labor Expense
11=Outside Processing PPV
12=Mix Variance
13=Teardown Material Expense
14=Teardown Conversion Expense
15=Yield Variance
16=Co-Product Rate Var
17=Material Rate Variance

4 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element

5 VARIANCE_SOURCE Character(2) VARCHAR2(2) NOT NULL Variance Source
C=Close
O=Re-Open
R=Report

6 TRANS_DATE Date(10) DATE Transaction Date

7 TRANS_TIME Time(15) TIMESTAMP Transaction Time

8 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

9 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

10 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp

11 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.

12 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

13 ACCTG_LINE_NO Number(15,0) DECIMAL(15) NOT NULL Accounting Line Number

14 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

15 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group

16 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

17 AP_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Payables Sequence Number

18 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit

19 DEBIT_CREDIT Character(2) VARCHAR2(2) NOT NULL Debit / Credit Indicator
CR=Credit
DR=Debit

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 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code

43 MONETARY_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.

44 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

45 STATISTIC_AMOUNT Signed Number(17,2) DECIMAL(15,2) NOT NULL Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.

46 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

47 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger

48 JOURNAL_ID Character(10) VARCHAR2(10) NOT NULL Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier.

49 JOURNAL_DATE Date(10) DATE Specifies the date the journal was created.

50 JOURNAL_LINE Number(9,0) DECIMAL(9) NOT NULL Uniquely identifies a journal line with a sequence number. Within a single journal entry, the line sequence begins with 1 and automatically increments by 1 for each succeeding journal line. There can be any number of lines associated with a journal header.

51 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book
Prompt Table: CM_BU_BOOK_VW

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_CMACTG_NONVW
2	PRODUCTION_ID	Character(10)	VARCHAR2(10) NOT NULL	Production ID Prompt Table: CM_DRILL_PID_VW
3	VARIANCE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	9/9/02 CRC: added variance type '15' for Yield Variance 01=Configuration Variance 02=Usage Variance 03=Component Yield Variance 04=Labor Lot Size Variance 05=Process Routing Variance 06=Efficiency Variance 07=Rate Variance 08=Material Lot Size Variance 09=Rework Expense 10=Rework Labor Expense 11=Outside Processing PPV 12=Mix Variance 13=Teardown Material Expense 14=Teardown Conversion Expense 15=Yield Variance 16=Co-Product Rate Var 17=Material Rate Variance
4	COST_ELEMENT	Character(4)	VARCHAR2(4) NOT NULL	Cost Element
5	VARIANCE_SOURCE	Character(2)	VARCHAR2(2) NOT NULL	Variance Source C=Close O=Re-Open R=Report
6	TRANS_DATE	Date(10)	DATE	Transaction Date
7	TRANS_TIME	Time(15)	TIMESTAMP	Transaction Time
8	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
9	INV_ITEM_ID	Character(18)	VARCHAR2(18) NOT NULL	Item ID
10	DT_TIMESTAMP	DateTime(26)	TIMESTAMP	Date Timestamp
11	TRANSACTION_GROUP	Character(3)	VARCHAR2(3) NOT NULL	11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
12	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
13	ACCTG_LINE_NO	Number(15,0)	DECIMAL(15) NOT NULL	Accounting Line Number
14	DISTRIB_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Distrib. Type
15	INV_ITEM_GROUP	Character(15)	VARCHAR2(15) NOT NULL	Item Group
16	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
17	AP_SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Payables Sequence Number
18	DESTIN_BU	Character(5)	VARCHAR2(5) NOT NULL	Destination Unit
19	DEBIT_CREDIT	Character(2)	VARCHAR2(2) NOT NULL	Debit / Credit Indicator CR=Credit DR=Debit
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	STATISTICS_CODE	Character(3)	VARCHAR2(3) NOT NULL	Statistics Code
43	MONETARY_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
44	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
45	STATISTIC_AMOUNT	Signed Number(17,2)	DECIMAL(15,2) NOT NULL	Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.
46	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
47	LEDGER	Character(10)	VARCHAR2(10) NOT NULL	Ledger
48	JOURNAL_ID	Character(10)	VARCHAR2(10) NOT NULL	Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier.
49	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
50	JOURNAL_LINE	Number(9,0)	DECIMAL(9) NOT NULL	Uniquely identifies a journal line with a sequence number. Within a single journal entry, the line sequence begins with 1 and automatically increments by 1 for each succeeding journal line. There can be any number of lines associated with a journal header.
51	CM_BOOK	Character(10)	VARCHAR2(10) NOT NULL	Cost Book Prompt Table: CM_BU_BOOK_VW