SCR_RPTL_CMP_VW (SQL View)	Index Back
SCR Report Element Compare Vw 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.

SCR_RPTL_CMP_VW

(SQL View)

SCR Report Element Compare Vw

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.SEQ_NUM , a.scr_chng_type , a.SETID , a.VENDOR_ID , (CASE WHEN a.VNDR_TYPE <> b.VNDR_TYPE THEN a.VNDR_TYPE ELSE c.VNDR_TYPE END) , (CASE WHEN a.WOMEN_OWNED <> b.WOMEN_OWNED THEN a.WOMEN_OWNED ELSE c.WOMEN_OWNED END) , (CASE WHEN a.HUBZONE_PROG <> b.HUBZONE_PROG THEN a.HUBZONE_PROG ELSE c.HUBZONE_PROG END) , (CASE WHEN a.SMALL_DISADV <> b.SMALL_DISADV THEN a.SMALL_DISADV ELSE c.SMALL_DISADV END) , (CASE WHEN a.OTHER_PREF_PROG <> b.OTHER_PREF_PROG THEN a.OTHER_PREF_PROG ELSE c.OTHER_PREF_PROG END) , (CASE WHEN a.COMMON_PARENT_NAME <> b.COMMON_PARENT_NAME THEN a.COMMON_PARENT_NAME ELSE c.COMMON_PARENT_NAME END) , (CASE WHEN a.COMMON_PARENT_TIN <> b.COMMON_PARENT_TIN THEN a.COMMON_PARENT_TIN ELSE c.COMMON_PARENT_TIN END) , (CASE WHEN a.VETERAN_OWNED <> b.VETERAN_OWNED THEN a.VETERAN_OWNED ELSE c.VETERAN_OWNED END) , (CASE WHEN a.EMERGING_SM_BUS <> b.EMERGING_SM_BUS THEN a.EMERGING_SM_BUS ELSE c.EMERGING_SM_BUS END) , (CASE WHEN a.SMALL_BUS_SIZE <> b.SMALL_BUS_SIZE THEN a.SMALL_BUS_SIZE ELSE c.SMALL_BUS_SIZE END) , (CASE WHEN a.VETERAN_FLG <> b.VETERAN_FLG THEN a.VETERAN_FLG ELSE c.VETERAN_FLG END) , (CASE WHEN a.DISABLED <> b.DISABLED THEN a.DISABLED ELSE c.DISABLED END) , (CASE WHEN a.ETHNICITY2 <> b.ETHNICITY2 THEN a.ETHNICITY2 ELSE c.ETHNICITY2 END) , (CASE WHEN a.ETHNICITY_LNG <> b.ETHNICITY_LNG THEN a.ETHNICITY_LNG ELSE c.ETHNICITY_LNG END) , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM ps_scr_rpt_ele_dft a , PS_SCR_RPT_ELE_MST b , ps_VNDR_RPT_ELEM 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.seq_num = b.seq_num AND a.scr_chng_type = b.scr_chng_type AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.seq_num = c.seq_num UNION SELECT a.SCR_ID , a.SCR_SEQ_NUM , a.SEQ_NUM , 'U' , a.SETID , a.VENDOR_ID , (CASE WHEN a.VNDR_TYPE <> ' ' THEN a.VNDR_TYPE ELSE c.VNDR_TYPE END) , (CASE WHEN a.WOMEN_OWNED <> ' ' THEN a.WOMEN_OWNED ELSE c.WOMEN_OWNED END) , (CASE WHEN a.HUBZONE_PROG <> ' ' THEN a.HUBZONE_PROG ELSE c.HUBZONE_PROG END) , (CASE WHEN a.SMALL_DISADV <> ' ' THEN a.SMALL_DISADV ELSE c.SMALL_DISADV END) , (CASE WHEN a.OTHER_PREF_PROG <> ' ' THEN a.OTHER_PREF_PROG ELSE c.OTHER_PREF_PROG END) , (CASE WHEN a.COMMON_PARENT_NAME <> ' ' THEN a.COMMON_PARENT_NAME ELSE c.COMMON_PARENT_NAME END) , (CASE WHEN a.COMMON_PARENT_TIN <> 0 THEN a.COMMON_PARENT_TIN ELSE c.COMMON_PARENT_TIN END) , (CASE WHEN a.VETERAN_OWNED <> ' ' THEN a.VETERAN_OWNED ELSE c.VETERAN_OWNED END) , (CASE WHEN a.EMERGING_SM_BUS <> ' ' THEN a.EMERGING_SM_BUS ELSE c.EMERGING_SM_BUS END) , (CASE WHEN a.SMALL_BUS_SIZE <> ' ' THEN a.SMALL_BUS_SIZE ELSE c.SMALL_BUS_SIZE END) , (CASE WHEN a.VETERAN_FLG <> ' ' THEN a.VETERAN_FLG ELSE c.VETERAN_FLG END) , (CASE WHEN a.DISABLED <> ' ' THEN a.DISABLED ELSE c.DISABLED END) , (CASE WHEN a.ETHNICITY2 <> ' ' THEN a.ETHNICITY2 ELSE c.ETHNICITY2 END) , (CASE WHEN a.ETHNICITY_LNG <> ' ' THEN a.ETHNICITY_LNG ELSE c.ETHNICITY_LNG END) , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM ps_scr_rpt_ele_dft a , ps_VNDR_RPT_ELEM c WHERE a.scr_chng_type = 'A' AND a.setid = c.setid AND a.vendor_id = c.vendor_id AND a.seq_num = c.seq_num UNION SELECT a.SCR_ID , a.SCR_SEQ_NUM , a.SEQ_NUM , a.scr_chng_type , a.SETID , a.VENDOR_ID , a.VNDR_TYPE , a.WOMEN_OWNED , a.HUBZONE_PROG , a.SMALL_DISADV , a.OTHER_PREF_PROG , a.COMMON_PARENT_NAME , a.COMMON_PARENT_TIN , a.VETERAN_OWNED , a.EMERGING_SM_BUS , a.SMALL_BUS_SIZE , a.VETERAN_FLG , a.DISABLED , a.ETHNICITY2 , a.ETHNICITY_LNG , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM ps_scr_rpt_ele_dft a WHERE a.scr_chng_type = 'A' AND NOT EXISTS ( SELECT 'x' FROM ps_VNDR_RPT_ELEM c WHERE a.setid = c.setid AND a.vendor_id = c.vendor_id AND a.seq_num = c.seq_num)

# 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 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence

4 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update

5 SETID Character(5) VARCHAR2(5) NOT NULL SetID

6 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

7 VNDR_TYPE Character(1) VARCHAR2(1) NOT NULL VNDR_TYPE
A=Small Disadvantaged Business
B=Other Small Business
C=Large Business
D=JWOD Nonprofit Agency
E=Educational Institution
F=Hospital
G=Nonprofit Organization
H=Reserved
J=Reserved
K=State/Local Government
L=Foreign Contractor
M=Domestic Contractor Outside US
U=Minority Institution

8 WOMEN_OWNED Character(1) VARCHAR2(1) NOT NULL WOMEN_OWNED
N=No
Y=Yes

9 HUBZONE_PROG Character(1) VARCHAR2(1) NOT NULL HUBZONE_PROG
A=HUBZone Sole Source
B=HUBZone Set-Aside
C=HUBZone Price Evaluation Pref
D=Combined HUBZone Price Adjust
E=Not Applicable
X=: If applicable, select one

10 SMALL_DISADV Character(1) VARCHAR2(1) NOT NULL SMALL_DISADV
A=8(a) Contract Award
B=8(a) with HUBZone Priority
C=SDB Set-Aside
D=SDB Price Evaluation Adjust
E=SDB Participating Program
F=Not Applicable
X=: If applicable, select one

11 OTHER_PREF_PROG Character(1) VARCHAR2(1) NOT NULL OTHER_PREF_PROG
A=Directed to JWOD Nonprofit
B=Small Business Set-Aside
C=Buy Indian
D=No Preference/Not listed
E=Very Small Business Set-Aside
X=: If applicable, select one

12 COMMON_PARENT_NAME Character(30) VARCHAR2(30) NOT NULL COMMON_PARENT_NAME

13 COMMON_PARENT_TIN Number(9,0) DECIMAL(9) NOT NULL Common Parent's TIN

14 VETERAN_OWNED Character(1) VARCHAR2(1) NOT NULL VETERAN_OWNED
A=Service Disabled VOSB
B=Other Veteran Owned Sm Bus
C=Not Veteran Owned Sm Business
X=: If applicable, select one

15 EMERGING_SM_BUS Character(1) VARCHAR2(1) NOT NULL EMERGING_SM_BUS
N=No
Y=Yes

16 SMALL_BUS_SIZE Character(1) VARCHAR2(1) NOT NULL Included the value with the longname for proper ordering in the listbox.
A=A) 50 or less
B=B) 51 - 100
C=C) 101 - 250
D=D) 251 - 500
E=E) 501 - 750
F=F) 751 - 1,000
G=G) Over 1,000
M=M) 1,000,000 or less
N=N) 1,000,001 - 2,000,000
P=P) 2,000,001 - 3,500,000
R=R) 3,500,001 - 5,000,000
S=S) 5,000,001 - 10,000,000
T=T) 10,000,001 - 17,000,000
X=: If applicable, select one
Z=Z) Over 17,000,000

17 VETERAN_FLG Character(1) VARCHAR2(1) NOT NULL VETERAN flag indicator
N=No
Y=Yes
Y/N Table Edit
Default Value: N

18 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.
N=No
Y=Yes
Y/N Table Edit
Default Value: N

19 ETHNICITY2 Character(1) VARCHAR2(1) NOT NULL Ethnicity
A=Asian American
B=African American
H=Hispanic American
N=Native American
O=Other

20 ETHNICITY_LNG Character(30) VARCHAR2(30) NOT NULL ETHNICITY LONG DESCR

21 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number

22 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning

23 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.

24 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.

25 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.

26 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.

#	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	SEQ_NUM	Number(3,0)	SMALLINT NOT NULL	Sequence
4	SCR_CHNG_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Request for Change Type A=Add D=Delete I=Inactivate U=Update
5	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
6	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
7	VNDR_TYPE	Character(1)	VARCHAR2(1) NOT NULL	VNDR_TYPE A=Small Disadvantaged Business B=Other Small Business C=Large Business D=JWOD Nonprofit Agency E=Educational Institution F=Hospital G=Nonprofit Organization H=Reserved J=Reserved K=State/Local Government L=Foreign Contractor M=Domestic Contractor Outside US U=Minority Institution
8	WOMEN_OWNED	Character(1)	VARCHAR2(1) NOT NULL	WOMEN_OWNED N=No Y=Yes
9	HUBZONE_PROG	Character(1)	VARCHAR2(1) NOT NULL	HUBZONE_PROG A=HUBZone Sole Source B=HUBZone Set-Aside C=HUBZone Price Evaluation Pref D=Combined HUBZone Price Adjust E=Not Applicable X=: If applicable, select one
10	SMALL_DISADV	Character(1)	VARCHAR2(1) NOT NULL	SMALL_DISADV A=8(a) Contract Award B=8(a) with HUBZone Priority C=SDB Set-Aside D=SDB Price Evaluation Adjust E=SDB Participating Program F=Not Applicable X=: If applicable, select one
11	OTHER_PREF_PROG	Character(1)	VARCHAR2(1) NOT NULL	OTHER_PREF_PROG A=Directed to JWOD Nonprofit B=Small Business Set-Aside C=Buy Indian D=No Preference/Not listed E=Very Small Business Set-Aside X=: If applicable, select one
12	COMMON_PARENT_NAME	Character(30)	VARCHAR2(30) NOT NULL	COMMON_PARENT_NAME
13	COMMON_PARENT_TIN	Number(9,0)	DECIMAL(9) NOT NULL	Common Parent's TIN
14	VETERAN_OWNED	Character(1)	VARCHAR2(1) NOT NULL	VETERAN_OWNED A=Service Disabled VOSB B=Other Veteran Owned Sm Bus C=Not Veteran Owned Sm Business X=: If applicable, select one
15	EMERGING_SM_BUS	Character(1)	VARCHAR2(1) NOT NULL	EMERGING_SM_BUS N=No Y=Yes
16	SMALL_BUS_SIZE	Character(1)	VARCHAR2(1) NOT NULL	Included the value with the longname for proper ordering in the listbox. A=A) 50 or less B=B) 51 - 100 C=C) 101 - 250 D=D) 251 - 500 E=E) 501 - 750 F=F) 751 - 1,000 G=G) Over 1,000 M=M) 1,000,000 or less N=N) 1,000,001 - 2,000,000 P=P) 2,000,001 - 3,500,000 R=R) 3,500,001 - 5,000,000 S=S) 5,000,001 - 10,000,000 T=T) 10,000,001 - 17,000,000 X=: If applicable, select one Z=Z) Over 17,000,000
17	VETERAN_FLG	Character(1)	VARCHAR2(1) NOT NULL	VETERAN flag indicator N=No Y=Yes Y/N Table Edit Default Value: N
18	DISABLED	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not (Y or N) a dependent is a disabled. N=No Y=Yes Y/N Table Edit Default Value: N
19	ETHNICITY2	Character(1)	VARCHAR2(1) NOT NULL	Ethnicity A=Asian American B=African American H=Hispanic American N=Native American O=Other
20	ETHNICITY_LNG	Character(30)	VARCHAR2(30) NOT NULL	ETHNICITY LONG DESCR
21	ERR_MSG_NBR	Number(5,0)	INTEGER NOT NULL	Service Purchase Error Number
22	MSG_SEVERITY	Character(1)	VARCHAR2(1) NOT NULL	Message Severity C=Cancel E=Error M=Message W=Warning
23	FMS_DTTM_STAMP	DateTime(26)	TIMESTAMP	Specifies the date and time of the original entry.
24	FMS_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the original entry.
25	FMS_LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry.
26	FMS_LASTUPDOPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the last update to an entry.