CM_DRILL_NGL_VW

(SQL View)
 Index Back

Journal Line detail

GMG CN# CM750-8.0 Drilldown; Journal Line details, lines NOT posted to GL. KDN CM800-7.1: Added ALTACCT & AFFILIATE fields. 11/30/01 8.4 for row level security, change the bu prompt table from CM_DRILL_BU_VW to SP_CMACTG_NONVW 12/09/2001 8.4 add keys to view to sort lines due to cm_acctg_line rewrite.

SELECT BUSINESS_UNIT , TRANSACTION_GROUP , ACCOUNTING_DT , INV_ITEM_ID , DT_TIMESTAMP , SEQ_NBR , CM_BOOK , PRODUCTION_ID , OP_SEQUENCE , COST_ELEMENT , TRANS_DATE , TRANS_TIME , CONVERSION_TYPE , VARIANCE_TYPE , VARIANCE_SOURCE , FROM_PRDN_ID , TO_PRDN_ID , STORAGE_AREA , CONFIG_CODE , REVALUE_TYPE , ORDER_NO , ORDER_INT_LINE_NO , SCHED_LINE_NBR , SHIP_ID , DELIVERY_ID , CHARGE_TYPE ,BUSINESS_UNIT_OM , ACCTG_LINE_NO , JOURNAL_ID , JOURNAL_DATE , JOURNAL_LINE , LEDGER , BUSINESS_UNIT_GL , TRANS_LINE_TYPE , AP_SEQ_NBR , MG_PROC_INSTANCE , STAGED_DATE , INV_LOT_ID , SERIAL_ID , STOR_LEVEL_1 , STOR_LEVEL_2 , STOR_LEVEL_3 , STOR_LEVEL_4 , CONTAINER_ID , UNIT_OF_MEASURE , PRDN_AREA_CODE , DISTRIB_TYPE , PRODUCTION_TYPE , DESTIN_BU , INV_ITEM_GROUP , DEBIT_CREDIT , LEDGER_GROUP , ACCOUNT , ALTACCT , DEPTID , %subrec(cf16_an_sbr,a) , %subrec(pc_cf1_n_sbr,a) , CURRENCY_CD , ACCOUNTING_PERIOD , FISCAL_YEAR , MONETARY_AMOUNT , STATISTIC_AMOUNT , STATISTICS_CODE , COMBO_VALID_FLG , GL_DISTRIB_STATUS , LINE_DESCR , PROCESS_INSTANCE , BUDGET_HDR_STATUS , CE_COST_CATEGORY , CE_RESOURCE , CE_SEQ_NBR , CE_CODE FROM PS_CM_ACCTG_LINE A WHERE JOURNAL_LINE = 0 AND JOURNAL_ID = ' '

# 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 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
5 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
6 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
7 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book

Prompt Table: CM_BU_BOOK_VW

8 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
9 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
10 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
11 TRANS_DATE Date(10) DATE Transaction Date
12 TRANS_TIME Time(15) TIMESTAMP Transaction Time
13 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
14 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
15 VARIANCE_SOURCE Character(2) VARCHAR2(2) NOT NULL Variance Source
C=Close
O=Re-Open
R=Report
16 FROM_PRDN_ID Character(10) VARCHAR2(10) NOT NULL From Production ID
17 TO_PRDN_ID Character(10) VARCHAR2(10) NOT NULL To Production ID
18 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
19 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
20 REVALUE_TYPE Character(2) VARCHAR2(2) NOT NULL 1/25/99 - CRC EN800-6.0 : added revalue type = '05' for co/by product
01=Inventory
02=Inspection
03=WIP - Comp
04=WIP - Assy
05=WIP - Waste
21 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
22 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
23 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
24 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
25 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID
26 CHARGE_TYPE Character(1) VARCHAR2(1) NOT NULL Shipping Charge Type
F=Freight
M=Misc Chrgs
N=Non-stock
27 BUSINESS_UNIT_OM Character(5) VARCHAR2(5) NOT NULL Order Management Business Unit
28 ACCTG_LINE_NO Number(15,0) DECIMAL(15) NOT NULL Accounting Line Number
29 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.
30 JOURNAL_DATE Date(10) DATE Specifies the date the journal was created.
31 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.
32 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger
33 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
34 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
35 AP_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Payables Sequence Number
36 MG_PROC_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
37 STAGED_DATE Date(10) DATE Staged Date
38 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
39 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
40 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
41 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
42 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
43 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
44 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID
45 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
46 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
47 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type
48 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
49 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
50 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group
51 DEBIT_CREDIT Character(2) VARCHAR2(2) NOT NULL Debit / Credit Indicator
CR=Credit
DR=Debit
52 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group
53 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
54 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
55 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL_VW

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

Prompt Table: OPERUNT_BUGL_VW

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

Prompt Table: PRODUCT_BUGL_VW

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

Prompt Table: FUND_BUGL_VW

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

Prompt Table: CLASSCF_BUGL_VW

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

Prompt Table: PROGRAM_BUGL_VW

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

Prompt Table: BUD_REF_BUGL_VW

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: CF1_BUGL_VW

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

Prompt Table: CF2_BUGL_VW

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

Prompt Table: CF3_BUGL_VW

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

Prompt Table: %EDIT_BU_PC

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

Prompt Table: %EDIT_PROJECT

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

Prompt Table: %EDIT_ACTIVITY

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

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

75 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
76 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.
77 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
78 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.
79 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.
80 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
81 COMBO_VALID_FLG Character(1) VARCHAR2(1) NOT NULL ChartField Combinatn Valid Flg
?=Validation in Progress
V=Valid ChartField Combination
X=Invalid ChartField Combo
82 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
83 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.
84 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
85 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
86 CE_COST_CATEGORY Character(4) VARCHAR2(4) NOT NULL 10/07/02 - LLR Added field CE_COST_CATEGORY for Debits to WIP.
%L=% Labor Overhead
%M=% Material Overhead
ADD=Additional Cost
ADJ=Adjustments
CO=Co Product
LAB=Labor Cost
MACH=Machine Cost
MAT=Material Cost
OTH=Other Cost
OVHD=Standard Cost Overhead
PL=Per Labor Overhead
PM=Per Machine Overhead
PO=Subcontracted PO-STD Cost
PRIM=Primary Product
PU=Per Unit Overhead
RB=Recycle By-Products
SCRP=Scrap Cost
STD=Subcontracted Standard Cost
SUBC=Subcontracted Cost
VCHR=Subcontracted VCHR - PO Cost
WB=Waste By-Products
WO=Write-off no cost adj
87 CE_RESOURCE Character(18) VARCHAR2(18) NOT NULL Resource
88 CE_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
89 CE_CODE Character(4) VARCHAR2(4) NOT NULL Conversion Code