CM_DRILL_VAR_VW(SQL View) |
Index Back |
---|---|
Variance Transaction DetailGMG CN# CM750-8.0 Drilldown; View joins Variance transaction details to Accounting Lines for a view of all details. 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. 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.TRANSACTION_GROUP , A.ACCOUNTING_DT , A.ACCTG_LINE_NO , A.BUSINESS_UNIT_GL , A.JOURNAL_ID , A.JOURNAL_DATE , A.JOURNAL_LINE , A.LEDGER , A.INV_ITEM_ID , A.DT_TIMESTAMP , A.SEQ_NBR , A.COST_ELEMENT , A.PRODUCTION_ID , A.OP_SEQUENCE , A.VARIANCE_TYPE , A.VARIANCE_SOURCE , A.PRDN_AREA_CODE , A.DISTRIB_TYPE , A.PRODUCTION_TYPE , A.DESTIN_BU , A.INV_ITEM_GROUP , A.DEBIT_CREDIT , A.LEDGER_GROUP , A.ACCOUNT , A.ALTACCT , A.DEPTID , %subrec(cf16_an_sbr,a) , %subrec(pc_cf1_n_sbr,a) , A.CURRENCY_CD , A.ACCOUNTING_PERIOD , A.FISCAL_YEAR , A.MONETARY_AMOUNT , A.STATISTIC_AMOUNT , A.STATISTICS_CODE , A.COMBO_VALID_FLG , A.GL_DISTRIB_STATUS , A.LINE_DESCR , A.PROCESS_INSTANCE , A.TRANS_DATE , A.TRANS_TIME , A.CM_BOOK , B.VARIANCE_AMOUNT 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 | TRANSACTION_GROUP | Character(3) | VARCHAR2(3) NOT NULL | 11/7/02 LLR - Added the new (600's) transactions groups for 8.8. |
3 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
4 | ACCTG_LINE_NO | Number(15,0) | DECIMAL(15) NOT NULL | Accounting Line Number |
5 | BUSINESS_UNIT_GL | Character(5) | VARCHAR2(5) NOT NULL | GL Business Unit |
6 | 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. |
7 | JOURNAL_DATE | Date(10) | DATE | Specifies the date the journal was created. |
8 | 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. |
9 | LEDGER | Character(10) | VARCHAR2(10) NOT NULL | Ledger |
10 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
11 | DT_TIMESTAMP | DateTime(26) | TIMESTAMP | Date Timestamp |
12 | SEQ_NBR | Number(15,0) | DECIMAL(15) NOT NULL | Sequence Number |
13 | COST_ELEMENT | Character(4) | VARCHAR2(4) NOT NULL | Cost Element |
14 | PRODUCTION_ID | Character(10) | VARCHAR2(10) NOT NULL | Production ID |
15 | OP_SEQUENCE | Number(4,0) | SMALLINT NOT NULL | Operation Sequence |
16 | 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 |
17 | VARIANCE_SOURCE | Character(2) | VARCHAR2(2) NOT NULL |
Variance Source
C=Close O=Re-Open R=Report |
18 | PRDN_AREA_CODE | Character(10) | VARCHAR2(10) NOT NULL | Production Area |
19 | DISTRIB_TYPE | Character(10) | VARCHAR2(10) NOT NULL | Distrib. Type |
20 | 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 |
21 | DESTIN_BU | Character(5) | VARCHAR2(5) NOT NULL | Destination Unit |
22 | INV_ITEM_GROUP | Character(15) | VARCHAR2(15) NOT NULL | Item Group |
23 | DEBIT_CREDIT | Character(2) | VARCHAR2(2) NOT NULL |
Debit / Credit Indicator
CR=Credit DR=Debit |
24 | LEDGER_GROUP | Character(10) | VARCHAR2(10) NOT NULL | Ledger Group |
25 | ACCOUNT | Character(10) | VARCHAR2(10) NOT NULL | Account |
26 | ALTACCT | Character(10) | VARCHAR2(10) NOT NULL | Alternate Account |
27 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPTID_BUGL_VW |
28 | OPERATING_UNIT | Character(8) | VARCHAR2(8) NOT NULL |
Operating Unit ChartField
Prompt Table: OPERUNT_BUGL_VW |
29 | PRODUCT | Character(6) | VARCHAR2(6) NOT NULL |
Product ChartField
Prompt Table: PRODUCT_BUGL_VW |
30 | FUND_CODE | Character(5) | VARCHAR2(5) NOT NULL |
Fund Code
Prompt Table: FUND_BUGL_VW |
31 | CLASS_FLD | Character(5) | VARCHAR2(5) NOT NULL |
Class Field
Prompt Table: CLASSCF_BUGL_VW |
32 | PROGRAM_CODE | Character(5) | VARCHAR2(5) NOT NULL |
Program Code ChartField
Prompt Table: PROGRAM_BUGL_VW |
33 | BUDGET_REF | Character(8) | VARCHAR2(8) NOT NULL |
Budget Reference
Prompt Table: BUD_REF_BUGL_VW |
34 | AFFILIATE | Character(5) | VARCHAR2(5) NOT NULL |
Affiliate
Prompt Table: AFFILIATE_VW |
35 | AFFILIATE_INTRA1 | Character(10) | VARCHAR2(10) NOT NULL |
IntraUnit Affiliate1
Prompt Table: %EDIT_INTRA01 |
36 | AFFILIATE_INTRA2 | Character(10) | VARCHAR2(10) NOT NULL |
Operating Unit Affiliate
Prompt Table: %EDIT_INTRA02 |
37 | CHARTFIELD1 | Character(10) | VARCHAR2(10) NOT NULL |
Expansion chartfield 1
Prompt Table: CF1_BUGL_VW |
38 | CHARTFIELD2 | Character(10) | VARCHAR2(10) NOT NULL |
Expansion Chartfield 2
Prompt Table: CF2_BUGL_VW |
39 | CHARTFIELD3 | Character(10) | VARCHAR2(10) NOT NULL |
Expansion Chartfield 3
Prompt Table: CF3_BUGL_VW |
40 | BUSINESS_UNIT_PC | Character(5) | VARCHAR2(5) NOT NULL |
PC Business Unit
Prompt Table: %EDIT_BU_PC |
41 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL |
Project Id ChartField
Prompt Table: %EDIT_PROJECT |
42 | ACTIVITY_ID | Character(15) | VARCHAR2(15) NOT NULL |
Activity ID
Prompt Table: %EDIT_ACTIVITY |
43 | RESOURCE_TYPE | Character(5) | VARCHAR2(5) NOT NULL |
Source Type
Prompt Table:
PROJ_RESTYPE_FS
|
44 | RESOURCE_CATEGORY | Character(5) | VARCHAR2(5) NOT NULL |
Category
Prompt Table:
%EDIT_RES_CAT
|
45 | RESOURCE_SUB_CAT | Character(5) | VARCHAR2(5) NOT NULL |
Subcategory
Prompt Table:
%EDIT_RES_SUB
|
46 | ANALYSIS_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Analysis Type
Prompt Table:
%EDIT_ANALYSIS
|
47 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
48 | ACCOUNTING_PERIOD | Number(3,0) | SMALLINT NOT NULL | Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table. |
49 | FISCAL_YEAR | Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
50 | 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. |
51 | 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. |
52 | STATISTICS_CODE | Character(3) | VARCHAR2(3) NOT NULL | Statistics Code |
53 | COMBO_VALID_FLG | Character(1) | VARCHAR2(1) NOT NULL |
ChartField Combinatn Valid Flg
?=Validation in Progress V=Valid ChartField Combination X=Invalid ChartField Combo |
54 | GL_DISTRIB_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Distribution Status
C=Available to Contracts D=Distributed G=Generated H=Hold I=Ignored J=Creating Journals M=Modified N=None P=To be processed X=Waiting for Reversal |
55 | LINE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Describes the nature of a journal line. It is defaulted from the description of the Account ChartField or manually entered. |
56 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
57 | TRANS_DATE | Date(10) | DATE | Transaction Date |
58 | TRANS_TIME | Time(15) | TIMESTAMP | Transaction Time |
59 | CM_BOOK | Character(10) | VARCHAR2(10) NOT NULL |
Cost Book
Prompt Table: CM_BU_BOOK_VW |
60 | VARIANCE_AMOUNT | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Variance Amount |