PRORD_NSTK_SRCH

(SQL View)
 Index Back

Nonstocked Order Line Search

Date Initials Issue Description 111506 dms 1576506002 Add IN_PROCESS_FLG

SELECT DISTINCT O.BUSINESS_UNIT , O.ORDER_NO , O.ORDER_INT_LINE_NO , S.SCHED_LINE_NBR , I.INV_ITEM_ID , O.PRODUCT_ID , O.PRODUCT_ID_ORIG , O.ORD_LINE_STATUS , O.QTY_ORDERED , O.UNIT_OF_MEASURE , OK.IN_PROCESS_FLG FROM PS_ORD_SCHEDULE S , PS_ORD_HEADER OH , PS_ORD_HEADER_LOCK OK , PS_MASTER_ITEM_TBL P , PS_ORD_LINE O , PS_IN_DEMAND I WHERE P.SETID = ( SELECT SCR.SETID FROM PS_SET_CNTRL_REC SCR WHERE SCR.SETCNTRLVALUE = O.BUSINESS_UNIT AND SCR.RECNAME = 'PROD_ITEM') AND I.INV_ITEM_ID = P.INV_ITEM_ID AND (P.INVENTORY_ITEM = 'N' OR S.DROP_SHIP_FLAG = 'Y' OR oh.bill_only = 'Y') AND OH.BUSINESS_UNIT = O.BUSINESS_UNIT AND OH.ORDER_NO = O.ORDER_NO AND S.BUSINESS_UNIT = O.BUSINESS_UNIT AND S.ORDER_NO = O.ORDER_NO AND S.ORDER_INT_LINE_NO = O.ORDER_INT_LINE_NO AND S.ORD_SCH_STATUS = 'O' AND OH.SOLD_TO_CUST_ID <> ' ' AND O.SHIP_TO_CUST_ID <> ' ' AND S.SHIP_TO_CUST_ID <> ' ' AND I.BUSINESS_UNIT = O.SHIP_FROM_BU AND I.ORDER_NO = O.ORDER_NO AND I.ORDER_INT_LINE_NO = O.ORDER_INT_LINE_NO AND I.SCHED_LINE_NBR = S.SCHED_LINE_NBR AND OH.ORDER_TYPE_CD BETWEEN 'SO' AND 'SO' AND %Join(COMMON_KEYS, ORD_HEADER OH, ORD_HEADER_LOCK OK) AND OK.BACKGROUND_REQD = 'N' AND OK.HOLD_REQD = 'N' AND OK.CREDIT_REQD = 'N'

# 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_OM.BUSINESS_UNIT

Prompt Table: SP_BU_OM_NONVW

2 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
3 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
4 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
5 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
6 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
7 PRODUCT_ID_ORIG Character(18) VARCHAR2(18) NOT NULL Original Product
8 ORD_LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Line Status
C=Closed
O=Open
P=Pending
X=Canceled
9 QTY_ORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/21/04 EGS: Created
10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
11 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag