CA_LOCTBL_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. Add EFF_STATUS check -- GL View did not have this in their code

CA_LOCTBL_EF_VW

(SQL View)

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. Add EFF_STATUS check -- GL View did not have this in their code

SELECT A.SETID , A.LOCATION , A.EFFDT , A.EFF_STATUS , %subrec(address_sbr,a) 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 AND B.EFF_STATUS = 'A')

# 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 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

6 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

7 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

8 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

9 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

10 CITY Character(30) VARCHAR2(30) NOT NULL City

11 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1

12 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2

13 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer

14 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1

15 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2

16 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3

17 COUNTY Character(30) VARCHAR2(30) NOT NULL County

18 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: %EDIT_STATE

19 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

20 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code

21 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
Y/N Table Edit

#	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	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
6	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
7	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
8	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
9	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
10	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
11	NUM1	Character(6)	VARCHAR2(6) NOT NULL	Number 1
12	NUM2	Character(6)	VARCHAR2(6) NOT NULL	Number 2
13	HOUSE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	House Type AB=House Boat WW=Trailer
14	ADDR_FIELD1	Character(2)	VARCHAR2(2) NOT NULL	Address Field 1
15	ADDR_FIELD2	Character(4)	VARCHAR2(4) NOT NULL	Address Field 2
16	ADDR_FIELD3	Character(4)	VARCHAR2(4) NOT NULL	Address Field 3
17	COUNTY	Character(30)	VARCHAR2(30) NOT NULL	County
18	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: %EDIT_STATE
19	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
20	GEO_CODE	Character(11)	VARCHAR2(11) NOT NULL	Geo Code
21	IN_CITY_LIMIT	Character(1)	VARCHAR2(1) NOT NULL	In City Limit Y/N Table Edit