SCR_VND_CMP_VW

(SQL View)
 Index Back

SCR Supplier 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.setid , a.vendor_id , (CASE WHEN a.vndr_name_shrt_usr <> b.vndr_name_shrt_usr THEN a.vndr_name_shrt_usr ELSE c.vndr_name_shrt_usr END) , (CASE WHEN a.vendor_class <> b.vendor_class THEN a.vendor_class ELSE c.vendor_class END) , (CASE WHEN a.hrms_class <> b.hrms_class THEN a.hrms_class ELSE c.hrms_class END) , (CASE WHEN a.vendor_persistence <> b.vendor_persistence THEN a.vendor_persistence ELSE c.vendor_persistence END) , (CASE WHEN a.vendor_status <> b.vendor_status THEN a.vendor_status ELSE c.vendor_status END) , (CASE WHEN a.vndr_ccr_status <> b.vndr_ccr_status THEN a.vndr_ccr_status ELSE c.vndr_ccr_status END) , (CASE WHEN a.name1 <> b.name1 THEN a.name1 ELSE c.name1 END) , (CASE WHEN a.name2 <> b.name2 THEN a.name2 ELSE c.name2 END) , (CASE WHEN a.corporate_setid <> b.corporate_setid THEN a.corporate_setid ELSE c.corporate_setid END) , (CASE WHEN a.corporate_vendor <> b.corporate_vendor THEN a.corporate_vendor ELSE c.corporate_vendor END) , (CASE WHEN a.vndr_affiliate <> b.vndr_affiliate THEN a.vndr_affiliate ELSE c.vndr_affiliate END) , (CASE WHEN a.interunit_vndr_flg <> b.interunit_vndr_flg THEN a.interunit_vndr_flg ELSE c.interunit_vndr_flg END) , (CASE WHEN a.supplier_rating <> b.supplier_rating THEN a.supplier_rating ELSE c.supplier_rating END) , (CASE WHEN a.cust_setid <> b.cust_setid THEN a.cust_setid ELSE c.cust_setid END) , (CASE WHEN a.cust_id <> b.cust_id THEN a.cust_id ELSE c.cust_id END) , (CASE WHEN a.ar_num <> b.ar_num THEN a.ar_num ELSE c.ar_num END) , (CASE WHEN (a.eeo_certif_dt IS NULL AND b.eeo_certif_dt IS NOT NULL) THEN a.eeo_certif_dt WHEN (a.eeo_certif_dt IS NOT NULL AND b.eeo_certif_dt IS NULL) THEN a.eeo_certif_dt WHEN a.eeo_certif_dt <> b.eeo_certif_dt THEN a.eeo_certif_dt ELSE c.eeo_certif_dt END) , (CASE WHEN a.hub_zone <> b.hub_zone THEN a.hub_zone ELSE c.hub_zone END) , (CASE WHEN a.wthd_sw <> b.wthd_sw THEN a.wthd_sw ELSE c.wthd_sw END) , (CASE WHEN a.vndr_status_po <> b.vndr_status_po THEN a.vndr_status_po ELSE c.vndr_status_po END) , (CASE WHEN a.vat_sw <> b.vat_sw THEN a.vat_sw ELSE c.vat_sw END) , a.scr_chng_type , a.err_msg_nbr , a.msg_severity , a.comments , a.default_loc FROM ps_scr_vendr_dft a , PS_SCR_VENDR_MST b , ps_vendor c WHERE a.scr_id = b.scr_id AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.scr_seq_num = b.scr_seq_num AND a.scr_chng_type = b.scr_chng_type AND a.scr_chng_type = 'U' AND b.setid = c.setid AND b.vendor_id = c.vendor_id

# 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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
5 VNDR_NAME_SHRT_USR Character(10) VARCHAR2(10) NOT NULL Short Vendor Name
6 VENDOR_CLASS Character(1) VARCHAR2(1) NOT NULL Vendor Classification
A=Attorney
E=Employee
H=HCM
R=Outside Party
7 HRMS_CLASS Character(1) VARCHAR2(1) NOT NULL Human Resources Classification
D=General Deduction
G=Garn Payee
H=National Health Serv Provider
I=Industrial Ins Board Provider
M=Imp Partner Provider
P=General Provider
T=Tax Collector
8 VENDOR_PERSISTENCE Character(1) VARCHAR2(1) NOT NULL Persistence
O=One Time
P=Permanent
R=Regular
S=Single Payment Supplier
9 VENDOR_STATUS Character(1) VARCHAR2(1) NOT NULL Vendor Status
A=Approved
D=Denied
E=Unapproved
I=Inactive
X=To Be Archived
10 VNDR_CCR_STATUS Character(1) VARCHAR2(1) NOT NULL SAM Status of a Vendor
A=Active
E=Expired
U=Unregistered
X=Exempt
11 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
12 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
13 CORPORATE_SETID Character(5) VARCHAR2(5) NOT NULL SetID
14 CORPORATE_VENDOR Character(10) VARCHAR2(10) NOT NULL Corporate Vendor
15 VNDR_AFFILIATE Character(5) VARCHAR2(5) NOT NULL Vendor Affiliate
16 INTERUNIT_VNDR_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit Vendor
N=No
Y=Yes
17 SUPPLIER_RATING Character(1) VARCHAR2(1) NOT NULL Supplier Rating
E=Excellent
F=Fair
G=Good
N=None
P=Poor
18 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
19 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
20 AR_NUM Character(15) VARCHAR2(15) NOT NULL Our Customer Number
21 EEO_CERTIF_DT Date(10) DATE EEO Certification Date
22 HUB_ZONE Character(1) VARCHAR2(1) NOT NULL HUB Zone
N=No
Y=Yes
23 WTHD_SW Character(1) VARCHAR2(1) NOT NULL Withholding Applicable Flag
B=Global and 1099
G=Global
T=1099
24 VNDR_STATUS_PO Character(1) VARCHAR2(1) NOT NULL Open For Ordering
N=No
Y=Yes
25 VAT_SW Character(1) VARCHAR2(1) NOT NULL Display VAT Flag
H=Hide VAT checkbox
N=Display VAT checkbox off
Y=Display VAT checkbox on
26 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
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 COMMENTS Long Character CLOB Comment
30 DEFAULT_LOC Character(10) VARCHAR2(10) NOT NULL Default Location