SPB_SUPBYBU_RVW (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'

SPB_SUPBYBU_RVW

(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'

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 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.SPB_SUPPLIER_TYPE = 'R' OR B.SPB_SUPPLIER_TYPE = 'B' OR B.SPB_SUPPLIER_TYPE = 'D')

# 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

#	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