DCM_SHIPTO_LOC

(SQL View)
 Index Back

View of Shipto Locations

Ship to locations

SELECT a.business_unit , b.shipto_id , b.descr , b.descrshort , e.building , e.floor , e.sector , e.jurisdiction , e.attn_to , %subrec(ADDRESS_SBR, e) FROM ps_ap_dm_bu a , ps_shipto_tbl b , ps_set_cntrl_rec c , PS_LOCATION_TBL e , ps_set_cntrl_rec f WHERE a.business_unit = c.setcntrlvalue AND c.rec_group_id = 'PO_04' AND c.recname = 'SHIPTO_TBL' AND c.setid = b.setid AND b.shipto_id = e.location AND b.setid = f.setcntrlvalue AND b.eff_status = 'A' AND b.effdt = ( SELECT MAX(bb.effdt) FROM ps_shipto_tbl bb WHERE b.setid = bb.setid AND b.shipto_id = bb.shipto_id) AND f.rec_group_id = 'FS_26' AND f.recname = 'LOCATION_TBL' AND f.setid = e.setid AND e.eff_status = 'A' AND e.effdt = ( SELECT MAX(ee.effdt) FROM PS_LOCATION_TBL ee WHERE ee.setid = e.setid AND ee.location = e.location AND ee.effdt <= b.effdt)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
4 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
5 BUILDING Character(10) VARCHAR2(10) NOT NULL Building #
6 FLOOR Character(10) VARCHAR2(10) NOT NULL Floor #
7 SECTOR Character(10) VARCHAR2(10) NOT NULL Sector
8 JURISDICTION Character(5) VARCHAR2(5) NOT NULL Jurisdiction
9 ATTN_TO Character(30) VARCHAR2(30) NOT NULL Attention
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: %EDIT_STATE

24 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
25 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
26 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit