SCR_ADDR_CMP_VW

(SQL View)
 Index Back

SCR Address Compare View

This view was created to eliminate the possibility of stale data. It compares the Master, Copy of Master at time of save, and the Draft tables to create data for reloading the pages. Each field will populated from the Draft table if changed or the Master record if it hasn't been changed.

SELECT a.scr_id , a.scr_seq_num , a.address_seq_num , c.effdt , a.scr_effdt , a.scr_chg_eff_when , a.scr_chng_type , (CASE WHEN a.emailid <> b.emailid THEN a.emailid ELSE c.emailid END) , (CASE WHEN a.descr <> b.descr THEN a.descr ELSE d.descr END) , (CASE WHEN a.country <> b.country THEN a.country ELSE c.country END) , (CASE WHEN a.address1 <> b.address1 THEN a.address1 ELSE c.address1 END) , (CASE WHEN a.address2 <> b.address2 THEN a.address2 ELSE c.address2 END) , (CASE WHEN a.address3 <> b.address3 THEN a.address3 ELSE c.address3 END) , (CASE WHEN a.address4 <> b.address4 THEN a.address4 ELSE c.address4 END) , (CASE WHEN a.city <> b.city THEN a.city ELSE c.city END) , (CASE WHEN a.num1 <> b.num1 THEN a.num1 ELSE c.num1 END) , (CASE WHEN a.num2 <> b.num2 THEN a.num2 ELSE c.num2 END) , (CASE WHEN a.house_type <> b.house_type THEN a.house_type ELSE c.house_type END) , (CASE WHEN a.addr_field1 <> b.addr_field1 THEN a.addr_field1 ELSE c.addr_field1 END) , (CASE WHEN a.addr_field2 <> b.addr_field2 THEN a.addr_field2 ELSE c.addr_field2 END) , (CASE WHEN a.addr_field3 <> b.addr_field3 THEN a.addr_field3 ELSE c.addr_field3 END) , (CASE WHEN a.county <> b.county THEN a.county ELSE c.county END) , (CASE WHEN a.state <> b.state THEN a.state ELSE c.state END) , (CASE WHEN a.postal <> b.postal THEN a.postal ELSE c.postal END) , (CASE WHEN a.geo_code <> b.geo_code THEN a.geo_code ELSE c.geo_code END) , (CASE WHEN a.in_city_limit <> b.in_city_limit THEN a.in_city_limit ELSE c.in_city_limit END) , a.ERR_MSG_NBR , a.MSG_SEVERITY , a.setid , a.vendor_id , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_ADDR_DFT a , ps_scr_addr_mst b , PS_VENDOR_ADDR c , PS_VNDR_ADDR_SCROL d WHERE a.scr_id = b.scr_id AND a.scr_seq_num = b.scr_seq_num AND a.address_seq_num = b.address_seq_num AND a.effdt = b.effdt AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.scr_chng_type = b.scr_chng_type AND a.scr_chng_type IN ( 'U', 'D') AND a.MSG_SEVERITY <> 'E' AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.address_seq_num = c.address_seq_num AND %EffdtCheck(VENDOR_ADDR d, c, %currentdatein) AND c.setid = d.setid AND c.vendor_id = d.vendor_id AND c.address_seq_num = d.address_seq_num UNION SELECT a.scr_id , a.scr_seq_num , a.address_seq_num , a.effdt , a.scr_effdt , a.scr_chg_eff_when , a.scr_chng_type , a.emailid , a.descr , a.country , a.address1 , a.address2 , a.address3 , a.address4 , a.city , a.num1 , a.num2 , a.house_type , a.addr_field1 , a.addr_field2 , a.addr_field3 , a.county , a.state , a.postal , a.geo_code , a.in_city_limit , a.ERR_MSG_NBR , a.MSG_SEVERITY , a.setid , a.vendor_id , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_ADDR_DFT a WHERE a.scr_chng_type = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SCR_ID Character(15) VARCHAR2(15) NOT NULL Supplier Change Request ID
2 SCR_SEQ_NUM Number(5,0) INTEGER NOT NULL Update Sequence Number
3 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
4 EFFDT Date(10) DATE Effective Date
5 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
6 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
7 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
8 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
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

27 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
28 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
29 SETID Character(5) VARCHAR2(5) NOT NULL SetID
30 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
31 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
32 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
33 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
34 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.