CM_DRILL_ERN_VW

(SQL View)
 Index Back

Earned Conversion Cost Detail

GMG CN# CM750-8.0 Drilldown; View joins Earned Conversion Cost transactions 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_earnconcost keys. 07/27/99 ej CN#CM800-9.0 Added MG_PROC_INSTANCE field 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.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.REVALUE_FLAG , A.COST_ELEMENT , A.PRODUCTION_ID , A.OP_SEQUENCE , A.CONVERSION_TYPE , 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.MG_PROC_INSTANCE , B.CONVERSION_AMT , B.RATE_AMOUNT , B.RATE_TYPE , B.OP_TIME_RATE , B.OP_TIME_RATE_UNIT , B.OVERHEAD_RATE , B.OVERHEAD_PERCENT , B.OH_RATE_TYPE , B.OH_LAB_MACH_AMT , B.EARN_COST_QTY , B.PRORATE_QTY , B.PERCENT_COMP , B.DAILY_CAPACITY , B.CREWSIZE , B.MACH_RESOURCES , B.QTY_BASE , A.TRANS_DATE , A.TRANS_TIME , A.CM_BOOK FROM PS_CM_ACCTG_LINE A , PS_SF_EARNCONCOST B WHERE A.MG_PROC_INSTANCE = B.MG_PROC_INSTANCE AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PRODUCTION_ID = B.PRODUCTION_ID AND A.OP_SEQUENCE = B.OP_SEQUENCE AND A.CONVERSION_TYPE = B.CONVERSION_TYPE AND A.COST_ELEMENT = B.COST_ELEMENT AND A.TRANS_DATE = B.TRANS_DATE AND A.TRANS_TIME = B.TRANS_TIME AND A.SEQ_NBR = B.SEQ_NBR AND A.REVALUE_FLAG = B.REVALUE_FLAG AND A.TRANSACTION_GROUP IN ( SELECT TRANSACTION_GROUP FROM PS_CM_ACCTG_GRP_D WHERE CM_SOURCE_RECORD = 'SF_EARNCONCOST')

# 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 REVALUE_FLAG Character(1) VARCHAR2(1) NOT NULL Can be either 'Y' or 'N'. This field is located on the ITEM Table and signifies whether or not an item will be considered by the Revaluation Process to be revaluated.

Y/N Table Edit

14 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
15 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
16 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
17 CONVERSION_TYPE Character(4) VARCHAR2(4) NOT NULL Conversion Type
1100=Lab Setup
1200=Lab Fixed
1300=Labor Run
1400=Lab PostPr
1500=Mach Setup
1600=Mach Fixed
1700=Mach Run
1800=MC PostPrd
2100=Ovhd 1
2200=Ovhd 2
2300=Ovhd 3
2400=Ovhd 4
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
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

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 MG_PROC_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
58 CONVERSION_AMT Signed Number(12,4) DECIMAL(10,4) NOT NULL Conversion Amount
59 RATE_AMOUNT Number(11,4) DECIMAL(10,4) NOT NULL Rate Amount
60 RATE_TYPE Character(2) VARCHAR2(2) NOT NULL Rate Per
HR=Hourly
UN=Per Unit
61 OP_TIME_RATE Number(9,2) DECIMAL(8,2) NOT NULL Operation Time/Rate
62 OP_TIME_RATE_UNIT Character(2) VARCHAR2(2) NOT NULL Operation Time/Rate Unit
DY=Days
HR=Hours
MN=Minutes
UD=Units per Day
UH=Units per Hour
UM=Units per Minute
63 OVERHEAD_RATE Number(11,4) DECIMAL(10,4) NOT NULL Conversion Overhead Rate
64 OVERHEAD_PERCENT Number(5,1) DECIMAL(4,1) NOT NULL Conversion Overhead %
65 OH_RATE_TYPE Character(2) VARCHAR2(2) NOT NULL Conversion Ovhd Rate Type
%L=% of Labor Amt
%M=% of Mach Amt
PL=Amt Per Labor Hr
PM=Amt Per Mach Hr
PU=Per Unit Amt
66 OH_LAB_MACH_AMT Signed Number(12,4) DECIMAL(10,4) NOT NULL Lab/Mach Amt for Ovhd Cal
67 EARN_COST_QTY Signed Number(13,4) DECIMAL(11,4) NOT NULL Earned Cost Quantity
68 PRORATE_QTY Signed Number(13,4) DECIMAL(11,4) NOT NULL Maximum Quantity
69 PERCENT_COMP Number(6,2) DECIMAL(5,2) NOT NULL Scrap Prdn Pct
70 DAILY_CAPACITY Number(5,2) DECIMAL(4,2) NOT NULL Average Daily Hours
71 CREWSIZE Number(7,2) DECIMAL(6,2) NOT NULL Crew Size
72 MACH_RESOURCES Number(7,2) DECIMAL(6,2) NOT NULL Machine Resources
73 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
74 TRANS_DATE Date(10) DATE Transaction Date
75 TRANS_TIME Time(15) TIMESTAMP Transaction Time
76 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book

Prompt Table: CM_BU_BOOK_VW