DEM_INF_PRI2_VW

(SQL View)
 Index Back

Demand Priority View 2

This view, used in the Reservations COBOL process, takes the fair share prioritization rules set up by the user to sort DEMAND_INF_INV rows in priority rank order. This is a copy of DEM_INF_PRI_VW, so any change made to the view text there should be made here as well. This copy was necessary because of limitations on the number of GROUP BY fields you can have in Sybase.

SELECT MIN(B.PRIORITY_RANK) , A.BUSINESS_UNIT , A.DEMAND_SOURCE , A.SOURCE_BUS_UNIT , A.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NBR , A.INV_ITEM_ID , A.DEMAND_LINE_NO , A.SCHED_DTTM , A.QTY_REQUESTED , A.QTY_REQUESTED_BASE , A.QTY_ALLOCATED , A.QTY_ALLOC_BASE , A.QTY_BACKORDER , A.QTY_BACKORDER_BASE , A.UNIT_OF_MEASURE , A.PRIORITY_NBR , A.PARTIAL_QTY_FLAG , A.PARTIAL_ORDER_FLAG , A.BCKORDR_CNCL_FLAG , A.INTERUNIT_FLG , A.LOT_ALLOC_FLG , A.DISTRIB_TYPE , A.RESERVED_DTTM , D.PROCESS_INSTANCE FROM PS_IN_DEMAND A , PS_INV_PRIO_RULE B , PS_SET_CNTRL_REC C , PS_IN_DEMAND_LOCK D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.RECNAME = 'CUST_CGRP_LNK' AND A.BUSINESS_UNIT = C.SETCNTRLVALUE AND A.IN_FULFILL_STATE = '20' AND %Join(COMMON_KEYS, IN_DEMAND A, IN_DEMAND_LOCK D) AND ((EXISTS ( SELECT 'X' FROM PS_CUST_CGRP_LNK G WHERE (G.SETID = C.SETID AND G.CUST_GRP_TYPE = 'ALOC' AND G.CUST_ID = A.CUST_ID AND (G.CUSTOMER_GROUP = B.CUSTOMER_GROUP OR B.CUSTOMER_GROUP = ' ')))) OR (NOT EXISTS ( SELECT 'X' FROM PS_CUST_CGRP_LNK G2 WHERE (G2.SETID = C.SETID AND G2.CUST_GRP_TYPE = 'ALOC' AND G2.CUST_ID = A.CUST_ID)) AND B.CUSTOMER_GROUP = ' ')) AND((A.DEMAND_SOURCE = 'OM' AND B.PRIO_DEMAND_SOURCE = 'OM') OR (A.DEMAND_SOURCE <> 'OM' AND A.INTERUNIT_FLG = 'Y' AND B.PRIO_DEMAND_SOURCE = 'IU') OR (A.DEMAND_SOURCE <> 'OM' AND A.INTERUNIT_FLG = 'N' AND B.PRIO_DEMAND_SOURCE = 'IN') OR (A.DEMAND_SOURCE <> ' ' AND B.PRIO_DEMAND_SOURCE = ' ')) AND ((A.BCKORDER_FLAG <> 'Y' AND B.PRIO_ORDER_TYPE_CD = '1') OR (A.BCKORDER_FLAG = 'Y' AND B.PRIO_ORDER_TYPE_CD = '2') OR (A.DEMAND_LINE_NO <> 0 AND B.PRIO_ORDER_TYPE_CD = ' ')) AND ((A.PRIORITY_NBR <= B.PRIORITY_NBR) OR (A.PRIORITY_NBR >= 0 AND B.PRIORITY_NBR = 0)) AND ((A.DESTIN_BU = B.DESTIN_BU) OR (A.DESTIN_BU <> ' ' AND B.DESTIN_BU = ' ')) GROUP BY A.BUSINESS_UNIT, A.DEMAND_SOURCE, A.SOURCE_BUS_UNIT, A.ORDER_NO, A.ORDER_INT_LINE_NO, A.SCHED_LINE_NBR, A.INV_ITEM_ID, A.DEMAND_LINE_NO, A.SCHED_DTTM, A.QTY_REQUESTED, A.QTY_REQUESTED_BASE, A.QTY_ALLOCATED, A.QTY_ALLOC_BASE, A.QTY_BACKORDER, A.QTY_BACKORDER_BASE, A.UNIT_OF_MEASURE, A.PRIORITY_NBR, A.PARTIAL_QTY_FLAG, A.PARTIAL_ORDER_FLAG, A.BCKORDR_CNCL_FLAG, A.INTERUNIT_FLG, A.LOT_ALLOC_FLG, A.DISTRIB_TYPE, A.RESERVED_DTTM, D.PROCESS_INSTANCE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PRIORITY_RANK Number(3,0) SMALLINT NOT NULL Priority rank value of demand prioritization rule
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 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
4 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
5 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
6 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
7 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
8 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
9 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
10 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
11 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested
12 QTY_REQUESTED_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested Base
13 QTY_ALLOCATED Signed Number(17,4) DECIMAL(15,4) NOT NULL Allocated quantity
14 QTY_ALLOC_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity allocation
15 QTY_BACKORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Qty
16 QTY_BACKORDER_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Base Qty
17 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
18 PRIORITY_NBR Number(3,0) SMALLINT NOT NULL Priority
19 PARTIAL_QTY_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Qtys Can Ship
20 PARTIAL_ORDER_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Orders Can Ship
N=No
Y=Yes
21 BCKORDR_CNCL_FLAG Character(1) VARCHAR2(1) NOT NULL Cancel Backorder
N=Cancel Backorder No
Y=Cancel Backorder Yes
22 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
23 LOT_ALLOC_FLG Character(1) VARCHAR2(1) NOT NULL Allow Lot Allocation
24 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type
25 RESERVED_DTTM DateTime(26) TIMESTAMP Reserved Date/Time
26 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance