SCR_GVCT_CMP_VW

(SQL View)
 Index Back

SCR Gov Classification Compare

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 , c.certif_source , c.gov_class , c.effdt , a.scr_effdt , a.scr_chg_eff_when , a.scr_chng_type , a.setid , a.vendor_id , (CASE WHEN a.gov_certif_nbr <> b.gov_certif_nbr THEN a.gov_certif_nbr ELSE c.gov_certif_nbr END) , (CASE WHEN a.certificate_exp_dt <> b.certificate_exp_dt THEN a.certificate_exp_dt ELSE c.certificate_exp_dt END) , (CASE WHEN a.certificate_beg_dt <> b.certificate_beg_dt THEN a.certificate_beg_dt ELSE c.certificate_beg_dt END) , a.scr_certif_source , a.scr_gov_class , a.scr_remove , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_GVCT_DFT a , ps_scr_gvct_mst b , ps_VNDR_GOV_CERT c WHERE a.scr_id = b.scr_id AND a.scr_seq_num = b.scr_seq_num AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.certif_source = b.certif_source AND a.gov_class = b.gov_class AND a.effdt = b.effdt AND a.scr_chng_type = b.scr_chng_type AND a.scr_chng_type IN ( 'U', 'D') AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.certif_source = c.certif_source AND b.gov_class = c.gov_class AND %EffdtCheck(VNDR_GOV_CERT d, c, %currentdatein) UNION SELECT a.scr_id , a.scr_seq_num , a.certif_source , a.gov_class , a.effdt , a.scr_effdt , a.scr_chg_eff_when , a.scr_chng_type , a.setid , a.vendor_id , a.gov_certif_nbr , a.certificate_exp_dt , a.certificate_beg_dt , a.scr_certif_source , a.scr_gov_class , a.scr_remove , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_GVCT_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 CERTIF_SOURCE Character(10) VARCHAR2(10) NOT NULL Certification Source

Prompt Table: GOV_CERTSRC_VW
Set Control Field: SETID

4 GOV_CLASS Character(10) VARCHAR2(10) NOT NULL Government Classification

Prompt Table: GOV_CERT_TBL
Set Control Field: SETID

5 EFFDT Date(10) DATE Effective Date
6 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
7 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
8 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
9 SETID Character(5) VARCHAR2(5) NOT NULL SetID
10 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
11 GOV_CERTIF_NBR Character(50) VARCHAR2(50) NOT NULL Certification Number
12 CERTIFICATE_EXP_DT Date(10) DATE Certificate Expiration
13 CERTIFICATE_BEG_DT Date(10) DATE Certificate Begin Date
14 SCR_CERTIF_SOURCE Character(10) VARCHAR2(10) NOT NULL Certification Source
15 SCR_GOV_CLASS Character(10) VARCHAR2(10) NOT NULL Government Classification
16 SCR_REMOVE Character(1) VARCHAR2(1) NOT NULL Remove Contact
17 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
18 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
19 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
20 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
21 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
22 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.