CM_DRILL_NSS_VW (SQL View)	Index Back
Non Stock Shipment Detail GMG CN# CM750-8.0 Drilldown; View joins Non Stock Shipment transaction details to Accounting Lines for a view of all details. KDN CM800-7.1: Added ALTACCT & AFFILIATE fields. 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 12/08 F-MLIZI-L7QX0: add DELIVERY_ID for 9.1 freight project

CM_DRILL_NSS_VW

(SQL View)

Non Stock Shipment Detail

GMG CN# CM750-8.0 Drilldown; View joins Non Stock Shipment transaction details to Accounting Lines for a view of all details. KDN CM800-7.1: Added ALTACCT & AFFILIATE fields. 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 12/08 F-MLIZI-L7QX0: add DELIVERY_ID for 9.1 freight project

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.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NBR , A.SHIP_ID , A.DELIVERY_ID , A.CHARGE_TYPE , A.BUSINESS_UNIT_OM , 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.QTY_BASE , B.UNIT_MEASURE_STD , B.UNIT_COST FROM PS_CM_ACCTG_LINE A , PS_CM_CST_NSSHIP B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ORDER_NO = B.ORDER_NO AND A.ORDER_INT_LINE_NO = B.ORDER_INT_LINE_NO AND A.SCHED_LINE_NBR = B.SCHED_LINE_NBR AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.SEQ_NBR = B.SEQ_NBR AND A.SHIP_ID = B.SHIP_ID AND A.DELIVERY_ID = B.DELIVERY_ID AND A.CHARGE_TYPE = B.CHARGE_TYPE AND A.BUSINESS_UNIT_OM = B.BUSINESS_UNIT_OM AND A.TRANSACTION_GROUP IN ( SELECT TRANSACTION_GROUP FROM PS_CM_ACCTG_GRP_D WHERE CM_SOURCE_RECORD = 'CM_CST_NSSHIP')

# 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 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

15 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line

16 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number

17 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID

18 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID

19 CHARGE_TYPE Character(1) VARCHAR2(1) NOT NULL Shipping Charge Type
F=Freight
M=Misc Chrgs
N=Non-stock

20 BUSINESS_UNIT_OM Character(5) VARCHAR2(5) NOT NULL Order Management Business Unit

21 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area

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

23 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

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

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

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

27 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group

28 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

29 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account

30 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPTID_BUGL_VW

31 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
Prompt Table: OPERUNT_BUGL_VW

32 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
Prompt Table: PRODUCT_BUGL_VW

33 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
Prompt Table: FUND_BUGL_VW

34 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
Prompt Table: CLASSCF_BUGL_VW

35 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
Prompt Table: PROGRAM_BUGL_VW

36 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
Prompt Table: BUD_REF_BUGL_VW

37 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
Prompt Table: AFFILIATE_VW

38 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
Prompt Table: %EDIT_INTRA01

39 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
Prompt Table: %EDIT_INTRA02

40 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
Prompt Table: CF1_BUGL_VW

41 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
Prompt Table: CF2_BUGL_VW

42 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
Prompt Table: CF3_BUGL_VW

43 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
Prompt Table: %EDIT_BU_PC

44 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
Prompt Table: %EDIT_PROJECT

45 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
Prompt Table: %EDIT_ACTIVITY

46 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

47 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

48 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

49 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

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

51 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.

52 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year

53 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.

54 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.

55 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code

56 COMBO_VALID_FLG Character(1) VARCHAR2(1) NOT NULL ChartField Combinatn Valid Flg
?=Validation in Progress
V=Valid ChartField Combination
X=Invalid ChartField Combo

57 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

58 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.

59 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

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

61 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity

62 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure

63 UNIT_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Unit Cost

#	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	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
15	ORDER_INT_LINE_NO	Number(5,0)	INTEGER NOT NULL	Order Line
16	SCHED_LINE_NBR	Number(6,0)	INTEGER NOT NULL	Schedule Line Number
17	SHIP_ID	Character(10)	VARCHAR2(10) NOT NULL	Shipping ID
18	DELIVERY_ID	Character(10)	VARCHAR2(10) NOT NULL	Delivery ID
19	CHARGE_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Shipping Charge Type F=Freight M=Misc Chrgs N=Non-stock
20	BUSINESS_UNIT_OM	Character(5)	VARCHAR2(5) NOT NULL	Order Management Business Unit
21	PRDN_AREA_CODE	Character(10)	VARCHAR2(10) NOT NULL	Production Area
22	DISTRIB_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Distrib. Type
23	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
24	DESTIN_BU	Character(5)	VARCHAR2(5) NOT NULL	Destination Unit
25	INV_ITEM_GROUP	Character(15)	VARCHAR2(15) NOT NULL	Item Group
26	DEBIT_CREDIT	Character(2)	VARCHAR2(2) NOT NULL	Debit / Credit Indicator CR=Credit DR=Debit
27	LEDGER_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Ledger Group
28	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
29	ALTACCT	Character(10)	VARCHAR2(10) NOT NULL	Alternate Account
30	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPTID_BUGL_VW
31	OPERATING_UNIT	Character(8)	VARCHAR2(8) NOT NULL	Operating Unit ChartField Prompt Table: OPERUNT_BUGL_VW
32	PRODUCT	Character(6)	VARCHAR2(6) NOT NULL	Product ChartField Prompt Table: PRODUCT_BUGL_VW
33	FUND_CODE	Character(5)	VARCHAR2(5) NOT NULL	Fund Code Prompt Table: FUND_BUGL_VW
34	CLASS_FLD	Character(5)	VARCHAR2(5) NOT NULL	Class Field Prompt Table: CLASSCF_BUGL_VW
35	PROGRAM_CODE	Character(5)	VARCHAR2(5) NOT NULL	Program Code ChartField Prompt Table: PROGRAM_BUGL_VW
36	BUDGET_REF	Character(8)	VARCHAR2(8) NOT NULL	Budget Reference Prompt Table: BUD_REF_BUGL_VW
37	AFFILIATE	Character(5)	VARCHAR2(5) NOT NULL	Affiliate Prompt Table: AFFILIATE_VW
38	AFFILIATE_INTRA1	Character(10)	VARCHAR2(10) NOT NULL	IntraUnit Affiliate1 Prompt Table: %EDIT_INTRA01
39	AFFILIATE_INTRA2	Character(10)	VARCHAR2(10) NOT NULL	Operating Unit Affiliate Prompt Table: %EDIT_INTRA02
40	CHARTFIELD1	Character(10)	VARCHAR2(10) NOT NULL	Expansion chartfield 1 Prompt Table: CF1_BUGL_VW
41	CHARTFIELD2	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 2 Prompt Table: CF2_BUGL_VW
42	CHARTFIELD3	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 3 Prompt Table: CF3_BUGL_VW
43	BUSINESS_UNIT_PC	Character(5)	VARCHAR2(5) NOT NULL	PC Business Unit Prompt Table: %EDIT_BU_PC
44	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField Prompt Table: %EDIT_PROJECT
45	ACTIVITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Activity ID Prompt Table: %EDIT_ACTIVITY
46	RESOURCE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Source Type Prompt Table: PROJ_RESTYPE_FS Set Control Field: BUSINESS_UNIT_PC
47	RESOURCE_CATEGORY	Character(5)	VARCHAR2(5) NOT NULL	Category Prompt Table: %EDIT_RES_CAT Set Control Field: BUSINESS_UNIT_PC
48	RESOURCE_SUB_CAT	Character(5)	VARCHAR2(5) NOT NULL	Subcategory Prompt Table: %EDIT_RES_SUB Set Control Field: BUSINESS_UNIT_PC
49	ANALYSIS_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Analysis Type Prompt Table: %EDIT_ANALYSIS Set Control Field: BUSINESS_UNIT_PC
50	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
51	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.
52	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
53	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.
54	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.
55	STATISTICS_CODE	Character(3)	VARCHAR2(3) NOT NULL	Statistics Code
56	COMBO_VALID_FLG	Character(1)	VARCHAR2(1) NOT NULL	ChartField Combinatn Valid Flg ?=Validation in Progress V=Valid ChartField Combination X=Invalid ChartField Combo
57	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
58	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.
59	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
60	CM_BOOK	Character(10)	VARCHAR2(10) NOT NULL	Cost Book Prompt Table: CM_BU_BOOK_VW
61	QTY_BASE	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Base Quantity
62	UNIT_MEASURE_STD	Character(3)	VARCHAR2(3) NOT NULL	Standard Unit of Measure
63	UNIT_COST	Signed Number(16,4)	DECIMAL(14,4) NOT NULL	Unit Cost