PRORD_NSTK_ORD

(SQL View)
 Index Back

Nonstocked Order Lines

Daniel Beaulne - 03/11/98 This view displays all the orders that can be marked for billing. It is a combination of PRORD_NSTK_SRCH and PRORD_NSTKKSRCH

SELECT DISTINCT OH.BUSINESS_UNIT , OH.ORDER_NO , OH.SOLD_TO_CUST_ID , OH.BILL_TO_CUST_ID FROM PS_ORD_SCHEDULE S , PS_ORD_HEADER OH , PS_PROD_ITEM P , PS_ORD_LINE O WHERE P.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = O.BUSINESS_UNIT AND RECNAME = 'PROD_ITEM') AND O.PRODUCT_ID = P.PRODUCT_ID 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 (S.DROP_SHIP_FLAG = 'Y' OR OH.BILL_ONLY = 'Y' OR EXISTS( SELECT 'x' FROM PS_MASTER_ITEM_TBL WHERE SETID = P.SETID AND INV_ITEM_ID = P.INV_ITEM_ID AND INVENTORY_ITEM = 'N') OR EXISTS( SELECT 'x' FROM PS_PRODKIT_COMP_VW PKC , PS_PROD_ITEM P2 WHERE PKC.SETID = P.SETID AND PKC.PRODUCT_ID = O.PRODUCT_ID AND PKC.DTTM_IN_EFFECT <= S.SCHED_SHIP_DTTM AND PKC.DTTM_OBSOLETE >= S.SCHED_SHIP_DTTM AND PKC.SETID = P2.SETID AND PKC.PROD_COMPONENT_ID = P2.PRODUCT_ID AND (P2.DROP_SHIP_FLAG = 'Y' OR PKC.INVENTORY_ITEM = 'N' )) OR EXISTS( SELECT 'x' FROM PS_CP_OPT_HDR CP , PS_PROD_ITEM P3 , PS_MASTER_ITEM_TBL M WHERE CP.BUSINESS_UNIT = O.BUSINESS_UNIT AND CP.ORDER_NO = O.ORDER_NO AND CP.ORDER_INT_LINE_NO = O.ORDER_INT_LINE_NO AND CP.CP_MODE = 'S' AND CP.MFG_PURCH_FLG = 'K' AND CP.PARENT_ITEM_ID = P.PRODUCT_ID AND P.SETID = P3.SETID AND CP.PRODUCT_ID = P3.PRODUCT_ID AND M.SETID = P3.SETID AND M.INV_ITEM_ID = P3.INV_ITEM_ID AND (P3.DROP_SHIP_FLAG = 'Y' OR M.INVENTORY_ITEM = 'N') AND P.PRODUCT_KIT_FLAG = 'Y'))

# 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 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
4 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer