LOC_TBL_EF_VW

(SQL View)
 Index Back

Location Tbl Eff Dt View

View on LOCATION_TBL. Used for Current Effective Dated Pub/Sub. This view includes all the fields from LOCATION_TBL Record. Should LOCATION_TBL change, this view definition needs to be checked. 3/31/00 Added field REG_REGION

SELECT A.SETID , A.LOCATION , A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCR_AC , A.DESCRSHORT , A.BUILDING , A.FLOOR , A.SECTOR , A.JURISDICTION , A.ATTN_TO , %subrec(address_sbr,a) , %subrec(bus_phone_sbr,a) , A.SETID_SALARY , A.SAL_ADMIN_PLAN , A.LANG_CD , A.HOLIDAY_SCHEDULE , %subrec(loc_tblusa_sbr,a) , %subrec(loc_tblcan_sbr,a) , %subrec(wrks_cncl_sbr,a) , %subrec(loc_tblger_sbr,a) , %subrec(loc_tbluk_sbr,a) , %subrec(loc_tbl_fed_sbr,a) , %subrec(loc_tblesp_sbr,a) , A.LABEL_FORMAT_ID2 , A.LABEL_FORMAT_ID3 , A.USG_LBL_FORMAT_ID ,A.REG_REGION , A.COMMENTS_2000 FROM PS_LOCATION_TBL A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_LOCATION_TBL B WHERE B.SETID = A.SETID AND B.LOCATION = A.LOCATION AND B.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

Prompt Table: SP_SETID_NONVW

2 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
3 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Description
7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
8 BUILDING Character(10) VARCHAR2(10) NOT NULL Building #
9 FLOOR Character(10) VARCHAR2(10) NOT NULL Floor #
10 SECTOR Character(10) VARCHAR2(10) NOT NULL Sector
11 JURISDICTION Character(5) VARCHAR2(5) NOT NULL Jurisdiction
12 ATTN_TO Character(30) VARCHAR2(30) NOT NULL Attention
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: %EDIT_STATE

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 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
31 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
32 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
33 FAX Character(24) VARCHAR2(24) NOT NULL Fax Number
34 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary SetID
35 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
36 LANG_CD Character(3) VARCHAR2(3) NOT NULL Language Code
CFR=Canadian French
DUT=Dutch
ENG=English
ESP=Spanish
FRA=French
GER=German
INE=International English
ITA=Italian
JPN=Japanese
POR=Portuguese
37 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL Holiday Schedule
38 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
39 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality

Prompt Table: LOCAL_TAX_TBL

40 CAN_CMA Character(2) VARCHAR2(2) NOT NULL Canadian Census Metropol Area
41 CAN_OEE_AREACD Character(2) VARCHAR2(2) NOT NULL Canadian Area Codes
01=Toronto Management Area
02=Hamilton Management Area
03=Niagra Management Area
04=Brant Haldimand, Norfolk Area
05=Windsor Management Area
06=London Management Area
07=Kitchener Management Area
08=Mississauga Management Area
09=Brampton Management Area
10=York Management Area
11=Georgian Bay Management Area
12=Kenora Management Area
13=Timmins Management Area
14=Slt.St.Marie management Area
15=Sudbury Management Area
16=North Bay Management Area
17=Pembroke Management Area
18=Ottawa Management Area
19=Cornwall Management Area
20=Kingston Management Area
21=Belleville Management Area
22=Peterborough Management Area
23=Oshawa Management Area
42 GEOLOC_CODE Character(10) VARCHAR2(10) NOT NULL Geographical Location Code
43 OFFICE_TYPE Character(1) VARCHAR2(1) NOT NULL Office Type
44 NCR_SW_CAN Character(1) VARCHAR2(1) NOT NULL National Capital Region

Y/N Table Edit

45 TBS_OFFICE_CD_CAN Character(10) VARCHAR2(10) NOT NULL Treasury Board Office Code
46 WRKS_CNCL_ID_LCL Character(9) VARCHAR2(9) NOT NULL Works Council ID
47 SPK_COMM_ID_GER Character(9) VARCHAR2(9) NOT NULL Spokesman Committee ID
48 TARIFF_AREA_GER Character(3) VARCHAR2(3) NOT NULL Tarif Area
49 TARIFF_GER Character(2) VARCHAR2(2) NOT NULL Tariff
50 INDUST_INSP_ID_GER Character(4) VARCHAR2(4) NOT NULL Industrial Inspection ID
51 NI_REPORT_SW_UK Character(1) VARCHAR2(1) NOT NULL Northern Ireland report Locati

Y/N Table Edit

52 GVT_GEOLOC_CD Character(9) VARCHAR2(9) NOT NULL Geog Location Code
53 GVT_DESIG_AGENT Character(4) VARCHAR2(4) NOT NULL GVT Designated Agent Code
54 SOC_SEC_WRK_CTR Character(3) VARCHAR2(3) NOT NULL Social Security Work Center
55 MATRICULA_NBR Number(3,0) SMALLINT NOT NULL Matricula Number
56 LABEL_FORMAT_ID2 Character(30) VARCHAR2(30) NOT NULL Carton Label Format ID

Prompt Table: BCT_SHL_INV_VW
Set Control Field: LABEL_FORMAT_ID2

57 LABEL_FORMAT_ID3 Character(30) VARCHAR2(30) NOT NULL Ship Container Label Format ID

Prompt Table: BCT_SHC_INV_VW
Set Control Field: LABEL_FORMAT_ID3

58 USG_LBL_FORMAT_ID Character(30) VARCHAR2(30) NOT NULL Item Usage Label Format

Prompt Table: BCT_USG_INV_VW
Set Control Field: USG_LBL_FORMAT_ID

59 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
60 COMMENTS_2000 Long Character CLOB Comment Text