RTE_CST_INV_VW (SQL View)	Index Back
Route Customer View This view will return all the effective routes and their associated Ship Tos or Locations.

RTE_CST_INV_VW

(SQL View)

Route Customer View

This view will return all the effective routes and their associated Ship Tos or Locations.

SELECT DISTINCT A.BUSINESS_UNIT , A.ROUTE_CD , A.CARRIER_ID , A.SHIP_TYPE_ID , B.ROUTE_SCHED_DATE , B.ROUTE_CAL_CURR_YR , C.ROUTE_STOP_NBR , C.SETID , C.SHIP_TO_CUST_ID , C.ADDRESS_SEQ_NUM , C.LOCATION FROM PS_ROUTE_INV A , PS_ROUTE_CAL_INV B , PS_ROUTE_STOP_INV C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ROUTE_CD = B.ROUTE_CD AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ROUTE_CD = C.ROUTE_CD AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.ROUTE_CD = C.ROUTE_CD AND B.ROUTE_CLOSED_FLG = 'N' AND B.ROUTE_SCHED_DATE >= %CurrentDateIn AND C.DATE_IN_EFFECT = ( SELECT MAX(D.DATE_IN_EFFECT) FROM PS_ROUTE_STOP_INV D WHERE C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.ROUTE_CD = D.ROUTE_CD AND C.ROUTE_STOP_NBR = D.ROUTE_STOP_NBR AND D.DATE_IN_EFFECT <= %CurrentDateIn) AND C.EFF_STATUS = 'A'

# 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_FS.BUSINESS_UNIT
Prompt Table: SP_BUIN_NONVW

2 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

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

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

5 ROUTE_SCHED_DATE Date(10) DATE Date on which a delivery route code can be used to fulfill orders.

6 ROUTE_CAL_CURR_YR Number(4,0) SMALLINT NOT NULL Year for which the route calendar dates are being maintained.

7 ROUTE_STOP_NBR Number(5,0) INTEGER NOT NULL An identifier used to sequence customer addresses/location for a given route.

8 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SP_SETID_NONVW

9 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
Prompt Table: INV_CUST_SHP_VW
Set Control Field: SETID

10 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
Prompt Table: INV_CUST_ADR_VW

11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
Prompt Table: INV_LOCATION_VW
Set Control Field: BUSINESS_UNIT

#	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_FS.BUSINESS_UNIT Prompt Table: SP_BUIN_NONVW
2	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
3	CARRIER_ID	Character(10)	VARCHAR2(10) NOT NULL	Carrier ID
4	SHIP_TYPE_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship Via Code
5	ROUTE_SCHED_DATE	Date(10)	DATE	Date on which a delivery route code can be used to fulfill orders.
6	ROUTE_CAL_CURR_YR	Number(4,0)	SMALLINT NOT NULL	Year for which the route calendar dates are being maintained.
7	ROUTE_STOP_NBR	Number(5,0)	INTEGER NOT NULL	An identifier used to sequence customer addresses/location for a given route.
8	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SP_SETID_NONVW
9	SHIP_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Ship To Customer Prompt Table: INV_CUST_SHP_VW Set Control Field: SETID
10	ADDRESS_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Address Sequence Number Prompt Table: INV_CUST_ADR_VW
11	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code Prompt Table: INV_LOCATION_VW Set Control Field: BUSINESS_UNIT