IN_DEL_ADDR_VW

(SQL View)
 Index Back

Delivery address

Address for delivery demand records Field DELIVERY_ID is included so the dynamic SQL built in SCM_IN_DELIVERY will work properly.

SELECT D.BUSINESS_UNIT , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , D.ORDER_INT_LINE_NO , D.SCHED_LINE_NBR , D.INV_ITEM_ID , D.DEMAND_LINE_NO , C.SETID_CUST_SHIPTO , L.SETID , D.SHIP_TO_CUST_ID , D.LOCATION , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.COUNTRY) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.COUNTRY) ELSE %Upper(L.COUNTRY) END ELSE %Upper(OA.COUNTRY) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDRESS1) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDRESS1) ELSE %Upper(L.ADDRESS1) END ELSE %Upper(OA.ADDRESS1) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDRESS2) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDRESS2) ELSE %Upper(L.ADDRESS2) END ELSE %Upper(OA.ADDRESS2) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDRESS3) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDRESS3) ELSE %Upper(L.ADDRESS3) END ELSE %Upper(OA.ADDRESS3) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDRESS4) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDRESS4) ELSE %Upper(L.ADDRESS4) END ELSE %Upper(OA.ADDRESS4) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.CITY) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.CITY) ELSE %Upper(L.CITY) END ELSE %Upper(OA.CITY) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.NUM1) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.NUM1) ELSE %Upper(L.NUM1) END ELSE %Upper(OA.NUM1) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.NUM2) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.NUM2) ELSE %Upper(L.NUM2) END ELSE %Upper(OA.NUM2) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.HOUSE_TYPE) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.HOUSE_TYPE) ELSE %Upper(L.HOUSE_TYPE) END ELSE %Upper(OA.HOUSE_TYPE) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDR_FIELD1) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDR_FIELD1) ELSE %Upper(L.ADDR_FIELD1) END ELSE %Upper(OA.ADDR_FIELD1) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDR_FIELD2) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDR_FIELD2) ELSE %Upper(L.ADDR_FIELD2) END ELSE %Upper(OA.ADDR_FIELD2) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.ADDR_FIELD3) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.ADDR_FIELD3) ELSE %Upper(L.ADDR_FIELD3) END ELSE %Upper(OA.ADDR_FIELD3) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.COUNTY) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.COUNTY) ELSE %Upper(L.COUNTY) END ELSE %Upper(OA.COUNTY) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.STATE) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.STATE) ELSE %Upper(L.STATE) END ELSE %Upper(OA.STATE) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.POSTAL) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.POSTAL) ELSE %Upper(L.POSTAL) END ELSE %Upper(OA.POSTAL) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.GEO_CODE) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.GEO_CODE) ELSE %Upper(L.GEO_CODE) END ELSE %Upper(OA.GEO_CODE) END , CASE D.ADDR_OVRD_LEVEL WHEN 'D' THEN %Upper(DA.IN_CITY_LIMIT) WHEN ' ' THEN CASE D.LOCATION WHEN ' ' THEN %Upper(C.IN_CITY_LIMIT) ELSE %Upper(L.IN_CITY_LIMIT) END ELSE %Upper(OA.IN_CITY_LIMIT) END , D.SHIP_CUST_NAME1 , D.SHIP_CUST_NAME2 , D.DELIVERY_ID FROM PS_IN_DEMAND D LEFT OUTER JOIN PS_IN_DEL_CADR_VW C ON %Join(COMMON_KEYS, IN_DEL_CADR_VW C, IN_DEMAND D) AND C.SHIP_TO_CUST_ID = D.SHIP_TO_CUST_ID AND C.ADDRESS_SEQ_NUM = D.ADDRESS_SEQ_NUM LEFT OUTER JOIN PS_IN_DEMAND_ADDR DA ON %Join(COMMON_KEYS, IN_DEMAND D, IN_DEMAND_ADDR DA) AND D.ADDR_OVRD_LEVEL = 'D' LEFT OUTER JOIN PS_LOCATION_TMP_VW L ON L.LOCATION = D.LOCATION AND D.LOCATION > ' ' LEFT OUTER JOIN PS_ORD_ADDR OA ON OA.BUSINESS_UNIT = D.SOURCE_BUS_UNIT AND OA.ORDER_NO = D.ORDER_NO AND D.ADDR_OVRD_LEVEL > ' ' AND OA.CUST_ADDR_TYPE = 'H' AND ((D.ADDR_OVRD_LEVEL = 'H' AND OA.ORDER_INT_LINE_NO = 0 AND OA.SCHED_LINE_NBR = 0) OR (D.ADDR_OVRD_LEVEL = 'L' AND OA.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO AND OA.SCHED_LINE_NBR = 0) OR (D.ADDR_OVRD_LEVEL = 'S' AND OA.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO AND OA.SCHED_LINE_NBR = D.SCHED_LINE_NBR)) WHERE D.IN_FULFILL_STATE BETWEEN '10' AND '60' AND D.IN_PROCESS_DATE IS NOT NULL AND D.APPROVED_FLAG = 'Y' AND D.RESERVE_STATUS < '99'

# 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

Prompt Table: SP_BU_FS_NONVW

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
8 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
9 SETID_CUST_SHIPTO Character(5) VARCHAR2(5) NOT NULL Shipto Cust SetID
10 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
11 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
13 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

14 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
15 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
16 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
17 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
18 CITY Character(30) VARCHAR2(30) NOT NULL City
19 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
20 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
21 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
22 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
23 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
24 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
25 COUNTY Character(30) VARCHAR2(30) NOT NULL County
26 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

27 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
28 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
29 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

30 SHIP_CUST_NAME1 Character(40) VARCHAR2(40) NOT NULL Ship To Name
31 SHIP_CUST_NAME2 Character(40) VARCHAR2(40) NOT NULL Ship To Name 2
32 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID