CM_DRILL_INV_VW

(SQL View)
 Index Back

Inventory Transaction Detail

GMG CN# CM750-8.0 Drilldown; View joins Inventory transaction details to Accounting Lines for a view of all details. MMT R-JROJAS-6T8T9 Changed view to select INV_LOT_ID and SERIAL_ID from PS_TRANSACTION_INV instead of from PS_CM_ACCTG_LINE KDN CM800-7.1: Added ALTACCT & AFFILIATE fields. 08/18/99 ej CN#CM800-9.0 Removed join of record.COST_INV and columns from COST_INV; VOUCHER_ID, VOUCHER_LINE_NUM, BUSINESS_UNIT_AP, QTY_BASE_AP, UOM_PO, TL_COST, LL_COST, and POSTED_FLAG 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 10/06/05 tw Res6284456/Ice1365365001 trans group 035 dest BU

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.TRANS_LINE_TYPE , A.AP_SEQ_NBR , A.PRODUCTION_ID , A.OP_SEQUENCE , A.CONVERSION_TYPE , A.VARIANCE_TYPE , A.VARIANCE_SOURCE , A.MG_PROC_INSTANCE , A.CONFIG_CODE , A.REVALUE_TYPE , A.STAGED_DATE , B.INV_LOT_ID , B.SERIAL_ID , A.STORAGE_AREA , A.STOR_LEVEL_1 , A.STOR_LEVEL_2 , A.STOR_LEVEL_3 , A.STOR_LEVEL_4 , A.CONTAINER_ID , A.UNIT_OF_MEASURE , A.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NBR , A.SHIP_ID , A.CHARGE_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.CM_BOOK , B.SOURCE_BUS_UNIT , B.DEMAND_LINE_NO , B.RECEIVER_ID , B.RECV_LN_NBR , B.COUNTING_EVENT_ID , B.TRANSACTION_DATE , B.QTY_BASE , B.QTY , B.UNIT_MEASURE_STD , B.TO_STORAGE_AREA , B.TO_STOR_LEVEL_1 , B.TO_STOR_LEVEL_2 , B.TO_STOR_LEVEL_3 , B.TO_STOR_LEVEL_4 , B.TO_UOM , B.ADJUST_TYPE , B.REASON_CD , B.DOCUMENT_ID , B.NON_COST_FLAG , B.NON_OWN_FLAG , B.COSTED_FLAG , B.WT_AVE_COST , B.OPRID , B.PO_ID , B.DISTRIB_LINE_NUM , B.DISTRIB_SEQ_NUM , B.RECV_SHIP_SEQ_NBR , B.PRICE_PO , B.REQ_ID , B.REQ_LINE_NBR , B.REQ_SCHED_NBR , B.REQ_DISTRIB_NBR , B.INTERUNIT_ID , B.INTERUNIT_LINE , B.INSPECTION_ID , B.LAST_PURCH_DATE , B.TRANSFER_COST , B.PURCH_LOT_ID , B.VENDOR_ID , B.MA_PRDN_ID_FLG , B.STAGED_TYPE , B.PO_DT , B.STORAGE_CODE , B.INV_STOCK_TYPE , B.RECEIPT_TYPE , B.BUSINESS_UNIT_PO , B.TRANSACTION_SOURCE , B.JOB_INSTANCE , B.TRANSFER_CALC_TYPE , A.CM_DT_TIMESTAMP , A.CM_SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.CM_SEQ_COST FROM PS_CM_ACCTG_LINE A , PS_TRANSACTION_INV B WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT OR A.SOURCE_BUS_UNIT = B.BUSINESS_UNIT) AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DT_TIMESTAMP = B.DT_TIMESTAMP 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 = 'TRANSACTION_INV')

# 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 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
15 AP_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Payables Sequence Number
16 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
17 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
18 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
19 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
20 VARIANCE_SOURCE Character(2) VARCHAR2(2) NOT NULL Variance Source
C=Close
O=Re-Open
R=Report
21 MG_PROC_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
22 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
23 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
24 STAGED_DATE Date(10) DATE Staged Date
25 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
26 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
27 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
28 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
29 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
30 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
31 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
32 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID
33 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
34 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
35 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
36 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
37 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
38 CHARGE_TYPE Character(1) VARCHAR2(1) NOT NULL Shipping Charge Type
F=Freight
M=Misc Chrgs
N=Non-stock
39 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
40 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type
41 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
42 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
43 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group
44 DEBIT_CREDIT Character(2) VARCHAR2(2) NOT NULL Debit / Credit Indicator
CR=Credit
DR=Debit
45 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group
46 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
47 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
48 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL_VW

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

Prompt Table: OPERUNT_BUGL_VW

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

Prompt Table: PRODUCT_BUGL_VW

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

Prompt Table: FUND_BUGL_VW

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

Prompt Table: CLASSCF_BUGL_VW

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

Prompt Table: PROGRAM_BUGL_VW

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

Prompt Table: BUD_REF_BUGL_VW

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: CF1_BUGL_VW

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

Prompt Table: CF2_BUGL_VW

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

Prompt Table: CF3_BUGL_VW

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

Prompt Table: %EDIT_BU_PC

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

Prompt Table: %EDIT_PROJECT

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

Prompt Table: %EDIT_ACTIVITY

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

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

68 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
69 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.
70 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
71 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.
72 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.
73 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
74 COMBO_VALID_FLG Character(1) VARCHAR2(1) NOT NULL ChartField Combinatn Valid Flg
?=Validation in Progress
V=Valid ChartField Combination
X=Invalid ChartField Combo
75 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
76 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.
77 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
78 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book

Prompt Table: CM_BU_BOOK_VW

79 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
80 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
81 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
82 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
83 COUNTING_EVENT_ID Number(15,0) DECIMAL(15) NOT NULL Counting Event ID
84 TRANSACTION_DATE Date(10) DATE Transaction Date
85 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
86 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity
87 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
88 TO_STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
89 TO_STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
90 TO_STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
91 TO_STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
92 TO_STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
93 TO_UOM Character(3) VARCHAR2(3) NOT NULL Unit of Measure
94 ADJUST_TYPE Character(1) VARCHAR2(1) NOT NULL Adjustment Type
0=CM Only
A=VMI Return
D=Decrease
I=Increase
M=Misc Issue
R=Misc Return
S=Scrap
V=Scrap RTV
95 REASON_CD Character(10) VARCHAR2(10) NOT NULL Reason Code
96 DOCUMENT_ID Character(12) VARCHAR2(12) NOT NULL Document
97 NON_COST_FLAG Character(1) VARCHAR2(1) NOT NULL Non Cost
98 NON_OWN_FLAG Character(1) VARCHAR2(1) NOT NULL 07/30/1998 WHS CN#ENH8.0: Created Translate Values 'Y'es and 'N'o
N=No
Y=Yes
99 COSTED_FLAG Character(1) VARCHAR2(1) NOT NULL Cost status of the transaction. Contains two values: 'N' cost have not been calculated for the transaction and 'Y' cost have been calculated for the transaction
E=Error; Cannot process
N=Transaction is Not Costed
P=Qualified for Purging
X=Transaction Won't be Costed
Y=Transaction is Costed
100 WT_AVE_COST Number(15,4) DECIMAL(14,4) NOT NULL Weighted Average Cost
101 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
102 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
103 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
104 DISTRIB_SEQ_NUM Number(5,0) INTEGER NOT NULL Distribution Sequence
105 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
106 PRICE_PO Signed Number(17,5) DECIMAL(15,5) NOT NULL Purchase Order Price
107 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
108 REQ_LINE_NBR Number(5,0) INTEGER NOT NULL Requisition Line Number
109 REQ_SCHED_NBR Number(3,0) SMALLINT NOT NULL Requisition Schedule Number
110 REQ_DISTRIB_NBR Number(5,0) INTEGER NOT NULL Requisition Distrib Number
111 INTERUNIT_ID Character(10) VARCHAR2(10) NOT NULL InterUnit Order ID
112 INTERUNIT_LINE Number(5,0) INTEGER NOT NULL InterUnit Order Line
113 INSPECTION_ID Character(10) VARCHAR2(10) NOT NULL Item Inspection ID
114 LAST_PURCH_DATE Date(10) DATE Last Purchase Date
115 TRANSFER_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price
116 PURCH_LOT_ID Character(15) VARCHAR2(15) NOT NULL Purchase Lot
117 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
118 MA_PRDN_ID_FLG Character(1) VARCHAR2(1) NOT NULL 10/18/99 SCC CN#IC800: Add label MAINTAIN_PID
N=No
Y=Yes
119 STAGED_TYPE Character(1) VARCHAR2(1) NOT NULL Staging Type
1=
2=* Cancellation
3=* Short Shipment
120 PO_DT Date(10) DATE Date
121 STORAGE_CODE Character(4) VARCHAR2(4) NOT NULL Storage Code
122 INV_STOCK_TYPE Character(4) VARCHAR2(4) NOT NULL Stock Type
123 RECEIPT_TYPE Character(4) VARCHAR2(4) NOT NULL Receipt Type
IUT=Interunit Transfer
RMA=Return Material Authorization
124 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
125 TRANSACTION_SOURCE Character(2) VARCHAR2(2) NOT NULL Transaction Source
126 JOB_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Job Instance
127 TRANSFER_CALC_TYPE Character(1) VARCHAR2(1) NOT NULL Holds information regarding when the transfer price is calculated.
0=Calculate Cost at Depletions
1=Transfer at Zero Cost

Default Value: 0

128 CM_DT_TIMESTAMP DateTime(26) TIMESTAMP Receipt dttm.
129 CM_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
130 CM_DT_TIMESTAMP_A DateTime(26) TIMESTAMP 07/99 GMG CN800-9.0: Original putaway dttm when bin-to-bin'd.
131 CM_SEQ_NBR_A Number(15,0) DECIMAL(15) NOT NULL 07/99 GMG CN800-9.0:
132 CM_SEQ_COST Number(15,0) DECIMAL(15) NOT NULL Cost Sequence Number. Incremented each time the actual cost is updated.