SCR_C_PHN_CMP_V

(SQL View)
 Index Back

SCR Contact 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.cntct_seq_num ,a.phone_type ,a.country_code ,a.phone , a.extension ,c.effdt ,a.scr_effdt ,a.setid ,a.vendor_id , a.scr_phone_type , a.scr_country_code , a.scr_phone , a.scr_extension ,a.scr_chg_eff_when ,a.scr_chng_type ,a.descr30 , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(fms_who_sbr, a) FROM ps_scr_cnt_phn_dft a , PS_SCR_CNT_PHN_MST b , PS_VNDR_CNTCT_PHN c WHERE a.scr_id = b.scr_id AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.cntct_seq_num = b.cntct_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 b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.cntct_seq_num = c.cntct_seq_num AND c.phone_type = b.phone_type AND c.country_code = b.country_code AND c.phone = b.phone AND c.extension = b.extension AND a.scr_chng_type IN ('U', 'D') AND %EffdtCheck(VNDR_CNTCT_PHN d, c, %currentdatein) UNION SELECT a.scr_id ,a.scr_seq_num ,a.cntct_seq_num ,a.phone_type ,a.country_code ,a.phone , a.extension ,a.effdt ,a.scr_effdt ,a.setid ,a.vendor_id , a.scr_phone_type , a.scr_country_code , a.scr_phone , a.scr_extension ,a.scr_chg_eff_when ,a.scr_chng_type ,a.descr30 , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(fms_who_sbr, a) FROM ps_scr_cnt_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 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact 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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
11 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
12 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
13 SCR_COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
14 SCR_PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
15 SCR_EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
16 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
17 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
18 DESCR30 Character(30) VARCHAR2(30) NOT NULL Description
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.