DMD_CNC_INV_VW5

(SQL View)
 Index Back

Cnc/Hold Vw - Inv Dmd Cfg Prod

08/30/04 tw Updated to use IN_DEMAND.

SELECT DISTINCT DII.BUSINESS_UNIT ,DEMAND_SOURCE ,SOURCE_BUS_UNIT ,DII.ORDER_NO ,DII.ORDER_INT_LINE_NO ,SCHED_LINE_NBR ,DII.INV_ITEM_ID ,DEMAND_LINE_NO ,AUTO_TYPE ,DEMAND_DATE ,REQUEST_DATE ,CANCEL_DTTM ,%DatePart(ORIG_SCHED_DTTM) ,%TimePart(ORIG_SCHED_DTTM) ,%DatePart(SCHED_DTTM) ,%TimePart(SCHED_DTTM) ,SCHED_DTTM ,PICK_DTTM ,ORIG_BU ,DESTIN_BU ,TRANSFER_YIELD ,DATE_LAST_MAINT ,LAST_MAINT_OPRID ,LOAD_ID ,CARRIER_ID ,SHIP_TYPE_ID ,%DatePart(SHIP_EARLY_DTTM) ,%TimePart(SHIP_EARLY_DTTM) ,%DatePart(SHIP_LATE_DTTM) ,%TimePart(SHIP_LATE_DTTM) ,DII.SCHED_ARRV_DTTM ,FREIGHT_TERMS ,PRIORITY_NBR ,DISTRIB_TYPE ,PICK_BATCH_ID ,PICKLIST_LINE_NO ,DII.REQ_ID ,DII.REQ_LINE_NBR ,DII.REQ_SCHED_NBR ,REQ_DISTRIB_NBR ,DII.PRODUCT_ID ,PARENT_PROD_ID ,KIT_LINE_NBR ,CHARGE_FLAG ,CNTRCT_ID ,DESTIN_GL_BU ,FERC_CD ,INTERUNIT_ID ,INTERUNIT_LINE ,INTERUNIT_STAT ,INTERUNIT_FLG ,INV_ITEM_ALIAS ,PRICE_MARKUP_PCT ,CUST_ID ,CUST_NAME ,SHIP_TO_CUST_ID ,SHIP_CUST_NAME1 ,SHIP_CUST_NAME2 ,LOCATION ,ADDRESS_SEQ_NUM ,TAX_EXEMPT_FLG ,EXP_RETURN_DATE ,ORIG_OPRID ,TRANSFER_COST ,PACKAGING_CD ,CURRENCY_CD ,QTY_REQUESTED ,QTY_REQUESTED_BASE ,QTY_ALLOCATED ,QTY_ALLOC_BASE ,QTY_BACKORDER ,QTY_BACKORDER_BASE ,QTY_PICK_OVRIDE ,FULFILL_FLAG ,RESERVE_STATUS ,APPROVED_FLAG ,SHIP_PRIOR_FLG ,BCKORDR_CNCL_FLAG ,BCKORDER_FLAG ,CUSTODIAN ,CHARGE_CODE ,PARTIAL_QTY_FLAG ,PARTIAL_ORDER_FLAG ,FROZEN_FLG ,DII.UNIT_OF_MEASURE ,LOT_ALLOC_FLG ,LAST_PROCESS_INST ,DT_TIMESTAMP ,DII.CONFIG_CODE ,DIRECT_INVOICING ,HOLD_FLAG ,HOLD_CD ,ROUTE_CD ,ROUTE_STOP_NBR ,RESERVED_DTTM ,BUDGET_DT ,EXPORT ,REPLACEMENT_FLG ,IST_RETURN_FLG_IUT ,IST_TXN_FLG ,ORIG_INV_ITEM_ID ,ORIG_UOM ,ALLOW_SUB_ITEM_FLG ,FRT_CHRG_OVERRIDE ,FRT_CHRG_METHOD ,FRT_RULE_CD ,SHIP_ID_EST ,NET_UNIT_PRICE ,ALLOW_OVERPICK_FLG ,MAX_PICK_TOLERANCE ,SINGLE_SHIP_FLAG ,REASON_CD ,TRANSFER_CALC_TYPE ,UPDATE_RESTRCT_LVL FROM PS_IN_DEMAND DII , PS_CP_OPT_HDR COH WHERE DII.IN_FULFILL_STATE <= '20' AND DII.BUSINESS_UNIT = COH.BUSINESS_UNIT_IN AND DII.ORDER_NO = COH.ORDER_NO AND DII.ORDER_INT_LINE_NO = COH.ORDER_INT_LINE_NO AND COH.CP_MODE = 'S' AND DII.PRODUCT_ID = COH.PRODUCT_ID AND COH.MFG_PURCH_FLG = 'K' AND COH.OPTIONAL_SHIP = 'Y' AND DII.DEMAND_SOURCE = 'OM' AND DII.BCKORDER_FLAG = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BUIN_NONVW

2 DEMAND_SOURCE Character(2) VARCHAR2(2) NOT NULL Demand Source
IN=Material Request
OM=Sales Order
PL=Planning Requisition
PO=Purchase Order
PR=Purchasing Requisition
RT=Material Return
SF=Production Request
WM=Work Order
3 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
4 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
5 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
6 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
7 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: BU_ITM_INV_VW2

8 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No

Default Value: 1

9 AUTO_TYPE Character(3) VARCHAR2(3) NOT NULL Number Type
10 DEMAND_DATE Date(10) DATE Demand Date
11 REQUEST_DATE Date(10) DATE Requested
12 CANCEL_DTTM DateTime(26) TIMESTAMP Dttm demand line was canceled
13 ORIG_SCHED_DATE Date(10) DATE Orig Sched Date
14 ORIG_SCHED_TIME Time(15) TIMESTAMP Original Schedule Time
15 SCHED_DATE Date(10) DATE NOT NULL Schedule Date
16 SCHED_TIME Time(15) TIMESTAMP Schedule Time
17 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
18 PICK_DTTM DateTime(26) TIMESTAMP Picked Date/Time
19 ORIG_BU Character(5) VARCHAR2(5) NOT NULL Original Business Unit
20 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
21 TRANSFER_YIELD Number(5,1) DECIMAL(4,1) NOT NULL Transfer Yield
22 DATE_LAST_MAINT Date(10) DATE Date Last Maintained
23 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By Operator ID
24 LOAD_ID Character(10) VARCHAR2(10) NOT NULL Load ID

Prompt Table: LOAD_INV

25 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID

Prompt Table: CARRIER_ID

26 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code

Prompt Table: SHIP_METHOD

27 SHIP_EARLY_DATE Date(10) DATE Earliest Ship Date
28 SHIP_EARLY_TIME Time(15) TIMESTAMP Earliest Ship Time
29 SHIP_LATE_DATE Date(10) DATE Latest Ship Date
30 SHIP_LATE_TIME Time(15) TIMESTAMP Latest Ship Time
31 SCHED_ARRV_DTTM DateTime(26) TIMESTAMP Schedule Arrival Dt/Tm
32 FREIGHT_TERMS Character(10) VARCHAR2(10) NOT NULL Freight Terms Code

Prompt Table: FREIGHT_TERMS

33 PRIORITY_NBR Number(3,0) SMALLINT NOT NULL Priority
34 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

Prompt Table: CM_DISTR_TYPE

35 PICK_BATCH_ID Number(10,0) DECIMAL(10) NOT NULL Pick Batch ID
36 PICKLIST_LINE_NO Number(7,0) INTEGER NOT NULL Pick List Sorting Line Number
37 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
38 REQ_LINE_NBR Number(5,0) INTEGER NOT NULL Requisition Line Number
39 REQ_SCHED_NBR Number(3,0) SMALLINT NOT NULL Requisition Schedule Number
40 REQ_DISTRIB_NBR Number(5,0) INTEGER NOT NULL Requisition Distrib Number
41 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
42 PARENT_PROD_ID Character(18) VARCHAR2(18) NOT NULL Parent Product Id
43 KIT_LINE_NBR Number(3,0) SMALLINT NOT NULL Kit Line Number
44 CHARGE_FLAG Character(1) VARCHAR2(1) NOT NULL Charged Out

Y/N Table Edit

Default Value: N

45 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
46 DESTIN_GL_BU Character(5) VARCHAR2(5) NOT NULL Destination GL Business Unit
47 FERC_CD Character(10) VARCHAR2(10) NOT NULL FERC Code

Prompt Table: FERC_CD_TBL

48 INTERUNIT_ID Character(10) VARCHAR2(10) NOT NULL InterUnit Order ID
49 INTERUNIT_LINE Number(5,0) INTEGER NOT NULL InterUnit Order Line
50 INTERUNIT_STAT Character(1) VARCHAR2(1) NOT NULL InterUnit Status
C=Cancelled
I=In-Transit
O=Open
P=Picked
R=Received

Default Value: O

51 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes

Y/N Table Edit

Default Value: N

52 INV_ITEM_ALIAS Character(18) VARCHAR2(18) NOT NULL Item Alias
53 PRICE_MARKUP_PCT Signed Number(16,2) DECIMAL(14,2) NOT NULL Price Markup %
54 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
55 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
56 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
57 SHIP_CUST_NAME1 Character(40) VARCHAR2(40) NOT NULL Ship To Name
58 SHIP_CUST_NAME2 Character(40) VARCHAR2(40) NOT NULL Ship To Name 2
59 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
60 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
61 TAX_EXEMPT_FLG Character(1) VARCHAR2(1) NOT NULL Tax Exempt Flag

Y/N Table Edit

62 EXP_RETURN_DATE Date(10) DATE Return Date
63 ORIG_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
64 TRANSFER_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price
65 PACKAGING_CD Character(4) VARCHAR2(4) NOT NULL Packaging Code

Prompt Table: PACKAGNG_CD_INV

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

Prompt Table: CURRENCY_CD_TBL

67 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested
68 QTY_REQUESTED_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested Base
69 QTY_ALLOCATED Signed Number(17,4) DECIMAL(15,4) NOT NULL Allocated quantity
70 QTY_ALLOC_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity allocation
71 QTY_BACKORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Qty
72 QTY_BACKORDER_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Base Qty
73 QTY_PICK_OVRIDE Signed Number(17,4) DECIMAL(15,4) NOT NULL Field used to override the net requested quantity when printing the pick play for non-soft reservation items.
74 FULFILL_FLAG Character(1) VARCHAR2(1) NOT NULL OK to Pick

Y/N Table Edit

Default Value: N

75 RESERVE_STATUS Character(2) VARCHAR2(2) NOT NULL Reservation Status
10=Pending
11=Unreserved
12=Shortage, Rules Not Met
15=Reserved, Rules Not Met
21=Shortage, Line Rule Met
22=Reserved, Line Rule Met
31=Shortage, All Rules Met
32=Reserved, All Rules Met
40=Reserve Run
99=Invalid
76 APPROVED_FLAG Character(1) VARCHAR2(1) NOT NULL Approved
B=Both
N=No
Y=Yes

Y/N Table Edit

Default Value: N

77 SHIP_PRIOR_FLG Character(1) VARCHAR2(1) NOT NULL Ship Prior to Sched Date

Y/N Table Edit

Default Value: N

78 BCKORDR_CNCL_FLAG Character(1) VARCHAR2(1) NOT NULL Cancel Backorder
N=Cancel Backorder No
Y=Cancel Backorder Yes

Y/N Table Edit

Default Value: N

79 BCKORDER_FLAG Character(1) VARCHAR2(1) NOT NULL Backorder Flag
N=No
Y=Yes

Y/N Table Edit

Default Value: N

80 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
81 CHARGE_CODE Character(20) VARCHAR2(20) NOT NULL Charge Code
82 PARTIAL_QTY_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Qtys Can Ship

Y/N Table Edit

Default Value: N

83 PARTIAL_ORDER_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Orders Can Ship
N=No
Y=Yes

Y/N Table Edit

Default Value: N

84 FROZEN_FLG Character(1) VARCHAR2(1) NOT NULL Frozen Flag
B=Both
N=No
Y=Yes

Y/N Table Edit

Default Value: N

85 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: INV_ORDR_UOM_VW

86 LOT_ALLOC_FLG Character(1) VARCHAR2(1) NOT NULL Allow Lot Allocation

Y/N Table Edit

Default Value: N

87 LAST_PROCESS_INST Number(10,0) DECIMAL(10) NOT NULL Process Instance
88 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
89 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
90 DIRECT_INVOICING Character(1) VARCHAR2(1) NOT NULL Direct Invoicing

Y/N Table Edit

Default Value: N

91 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold

Y/N Table Edit

Default Value: N

92 HOLD_CD Character(6) VARCHAR2(6) NOT NULL Hold Code

Prompt Table: HOLD_CD

93 ROUTE_CD Character(6) VARCHAR2(6) NOT NULL A unique identifier assigned to a delivery route. Route codes can be associated with customer addresses for external orders or locations for internal orders. Route codes can be used to identify the appropriate delivery schedule for a customer/location if you are managing product shipments with your own vehicles.

Prompt Table: ROUTE_INV_VW

94 ROUTE_STOP_NBR Number(5,0) INTEGER NOT NULL An identifier used to sequence customer addresses/location for a given route.
95 RESERVED_DTTM DateTime(26) TIMESTAMP Reserved Date/Time
96 BUDGET_DT Date(10) DATE Budget Date
97 EXPORT Character(1) VARCHAR2(1) NOT NULL Export Analytic Instance
N=No
Y=Yes

Y/N Table Edit

Default Value: N

98 REPLACEMENT_FLG Character(2) VARCHAR2(2) NOT NULL Replacement Flag
NA=Not Applicable
RN=Replace Goods Not Returned
RR=Replacement of Returned Goods

Default Value: NA

99 IST_RETURN_FLG_IUT Character(1) VARCHAR2(1) NOT NULL Intrastat InterUnit Returns

Y/N Table Edit

Default Value: N

100 IST_TXN_FLG Character(1) VARCHAR2(1) NOT NULL Intrastat Transaction Flag
N=No
Y=Yes

Default Value: N

101 ORIG_INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Used to identify the original item ordered, in case an item substitution has taken place.
102 ORIG_UOM Character(3) VARCHAR2(3) NOT NULL Original Unit of Measure
103 ALLOW_SUB_ITEM_FLG Character(1) VARCHAR2(1) NOT NULL Used to indicate whether item substitutions are allowed on the order schedule.

Y/N Table Edit

Default Value: N

104 FRT_CHRG_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Freight Charge Override

Y/N Table Edit

Default Value: N

105 FRT_CHRG_METHOD Character(2) VARCHAR2(2) NOT NULL Freight Charge Method
01=Order Value
02=Weight
03=Volume
04=Order Quantity
106 FRT_RULE_CD Character(10) VARCHAR2(10) NOT NULL Freight Rule Code
107 SHIP_ID_EST Character(10) VARCHAR2(10) NOT NULL Estimated Ship ID
108 NET_UNIT_PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price
109 ALLOW_OVERPICK_FLG Character(1) VARCHAR2(1) NOT NULL Used to indicate whether or not overpicking is allowed for a customer or business unit.

Y/N Table Edit

Default Value: N

110 MAX_PICK_TOLERANCE Number(6,2) DECIMAL(5,2) NOT NULL Used to indicate the allowable percentage of overshipment for a customer or business unit.
111 SINGLE_SHIP_FLAG Character(1) VARCHAR2(1) NOT NULL Single Ship Flag
N=No
Y=Yes

Y/N Table Edit

Default Value: N

112 REASON_CD Character(10) VARCHAR2(10) NOT NULL Reason Code

Prompt Table: REASON_SHIP_VW

113 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

114 UPDATE_RESTRCT_LVL Character(1) VARCHAR2(1) NOT NULL This is a translate field that indicates the restriction level and is checked by online processes when the row is being updated by a background process.