SCR_A_PHN_CMP_V

(SQL View)
 Index Back

SCR Address Phone 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 ,a.PHONE_TYPE ,a.COUNTRY_CODE ,a.PHONE ,a.EXTENSION ,c.EFFDT ,a.SCR_EFFDT , (CASE WHEN a.location_descr <> b.location_descr THEN a.location_descr ELSE c.location_descr END) , a.SCR_PHONE_TYPE , a.SCR_COUNTRY_CODE , a.SCR_PHONE , a.SCR_EXTENSION ,a.SCR_CHG_EFF_WHEN ,a.SCR_CHNG_TYPE ,a.SETID ,a.VENDOR_ID , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_ADR_PHN_DFT a , PS_SCR_ADR_PHN_MST b , ps_VENDOR_ADDR_PHN c WHERE a.SCR_ID = b.SCR_ID AND a.SETID = b.SETID AND a.VENDOR_ID = b.VENDOR_ID AND a.address_SEQ_NUM = b.address_SEQ_NUM AND a.EFFDT = b.EFFDT AND a.PHONE_TYPE = b.PHONE_TYPE AND a.COUNTRY_CODE = b.COUNTRY_CODE AND a.PHONE = b.PHONE AND a.EXTENSION = b.EXTENSION AND a.SCR_CHNG_TYPE IN ('U','D') AND b.SETID = c.SETID AND b.VENDOR_ID = c.VENDOR_ID AND b.address_SEQ_NUM = c.address_SEQ_NUM AND b.PHONE_TYPE = c.PHONE_TYPE AND b.COUNTRY_CODE = c.COUNTRY_CODE AND b.PHONE = c.PHONE AND b.EXTENSION = c.EXTENSION AND %EffdtCheck(VENDOR_ADDR_PHN d, c, %currentdatein) UNION SELECT a.SCR_ID ,a.SCR_SEQ_NUM ,a.address_SEQ_NUM ,a.PHONE_TYPE ,a.COUNTRY_CODE ,a.PHONE ,a.EXTENSION ,a.EFFDT ,a.SCR_EFFDT , a.location_descr , a.SCR_PHONE_TYPE , a.SCR_COUNTRY_CODE , a.SCR_PHONE , a.SCR_EXTENSION ,a.SCR_CHG_EFF_WHEN ,a.SCR_CHNG_TYPE ,a.SETID ,a.VENDOR_ID , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_ADR_PHN_DFT a WHERE a.SCR_CHNG_TYPE = 'A' AND a.msg_severity <> 'E'

# 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 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business Phone
CAMP=Campus Phone
CELL=Cellular Phone
DORM=Dormitory Phone
FAX=FAX
HOME=Home Phone
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex

Default Value: BUSN

5 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
6 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
7 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
8 EFFDT Date(10) DATE Effective Date

Default Value: %date

9 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
10 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
11 SCR_PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business Phone
CAMP=Campus Phone
CELL=Cellular Phone
DORM=Dormitory Phone
FAX=FAX
HOME=Home Phone
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
12 SCR_COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
13 SCR_PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
14 SCR_EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
15 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
16 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
17 SETID Character(5) VARCHAR2(5) NOT NULL SetID
18 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
19 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
20 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
21 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
22 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
23 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
24 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.