SPM_BU_SNW_M_V (SQL View)	Index Back
Supplier Info by BU SELECT A.BUSINESS_UNIT, B.VENDOR_ID, B.NAME1, A.NOTIFY_BY, A.PERSON_ID, A.SPM_SOURCETYPE, A.SUBMITAL_LIMIT FROM PS_SPM_BU_SUPPLIER A, PS_VENDOR_SPRO_VW B WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_SPM_BU_SUPPLIER A_ED WHERE A.BUSINESS_UNIT = A_ED.BUSINESS_UNIT AND A.VENDOR_ID = A_ED.VENDOR_ID AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) AND A.VENDOR_ID = B.VENDOR_ID AND A.EFF_STATUS = 'A' Date Initials # Reference Description 10/23/12 MRAD 12993090 Ensured that Supplier's with Multi Resource checked are sourced to Multi Resource Req's only

SPM_BU_SNW_M_V

(SQL View)

Supplier Info by BU

SELECT A.BUSINESS_UNIT, B.VENDOR_ID, B.NAME1, A.NOTIFY_BY, A.PERSON_ID, A.SPM_SOURCETYPE, A.SUBMITAL_LIMIT FROM PS_SPM_BU_SUPPLIER A, PS_VENDOR_SPRO_VW B WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_SPM_BU_SUPPLIER A_ED WHERE A.BUSINESS_UNIT = A_ED.BUSINESS_UNIT AND A.VENDOR_ID = A_ED.VENDOR_ID AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) AND A.VENDOR_ID = B.VENDOR_ID AND A.EFF_STATUS = 'A' Date Initials # Reference Description 10/23/12 MRAD 12993090 Ensured that Supplier's with Multi Resource checked are sourced to Multi Resource Req's only

SELECT A.BUSINESS_UNIT , B.VENDOR_ID , B.NAME1 , A.NOTIFY_BY , A.PERSON_ID ,A.VNDR_LOC , A.SPM_SOURCETYPE , A.SUBMITAL_LIMIT ,B.SP_SCORE ,B.SPB_SUPPLIER_TYPE ,B.SP_MULTI_RES_SPLR FROM PS_SPM_BU_SUPPLIER A , PS_VENDOR_SPRO_VW B , PS_SET_CNTRL_GROUP S WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_SPM_BU_SUPPLIER A_ED WHERE A.BUSINESS_UNIT = A_ED.BUSINESS_UNIT AND A.VENDOR_ID = A_ED.VENDOR_ID AND A_ED.EFFDT <= %CurrentDateIn) AND A.VENDOR_ID = B.VENDOR_ID AND A.EFF_STATUS = 'A' AND A.BUSINESS_UNIT = S.SETCNTRLVALUE AND S.REC_GROUP_ID = 'FS_38' AND B.SETID = S.SETID AND B.SP_MULTI_RES_SPLR ='Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: BUS_UNIT_SP_VW

2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
Prompt Table: VENDOR_SP_VW
Set Control Field: BUSINESS_UNIT

3 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

4 NOTIFY_BY Character(4) VARCHAR2(4) NOT NULL Notification Method
EM=Email and Worklist
WL=WorkList Only
Default Value: EM

5 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID
Prompt Table: SPM_SPLR_CNT_VW
Set Control Field: BUSINESS_UNIT

6 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

7 SPM_SOURCETYPE Character(4) VARCHAR2(4) NOT NULL Supplier Network Source Type
0=Internal
1=Primary
2=Secondary
3=Tertiary

8 SUBMITAL_LIMIT Number(2,0) SMALLINT NOT NULL Submittal Limit
Default Value: 5

9 SP_SCORE Number(7,2) DECIMAL(6,2) NOT NULL Score

10 SPB_SUPPLIER_TYPE Character(1) VARCHAR2(1) NOT NULL Supplier Type
B=Both
D=Deliverable
R=Resource

11 SP_MULTI_RES_SPLR Character(1) VARCHAR2(1) NOT NULL Multi Resource supplier
Y/N Table Edit
Default Value: N

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: BUS_UNIT_SP_VW
2	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier Prompt Table: VENDOR_SP_VW Set Control Field: BUSINESS_UNIT
3	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
4	NOTIFY_BY	Character(4)	VARCHAR2(4) NOT NULL	Notification Method EM=Email and Worklist WL=WorkList Only Default Value: EM
5	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID Prompt Table: SPM_SPLR_CNT_VW Set Control Field: BUSINESS_UNIT
6	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
7	SPM_SOURCETYPE	Character(4)	VARCHAR2(4) NOT NULL	Supplier Network Source Type 0=Internal 1=Primary 2=Secondary 3=Tertiary
8	SUBMITAL_LIMIT	Number(2,0)	SMALLINT NOT NULL	Submittal Limit Default Value: 5
9	SP_SCORE	Number(7,2)	DECIMAL(6,2) NOT NULL	Score
10	SPB_SUPPLIER_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Supplier Type B=Both D=Deliverable R=Resource
11	SP_MULTI_RES_SPLR	Character(1)	VARCHAR2(1) NOT NULL	Multi Resource supplier Y/N Table Edit Default Value: N