SCP_PUB_NOIA_VW

(SQL View)
 Index Back

Bidder Pagelet View

Bidder Pagelet View

SELECT DISTINCT A.BUSINESS_UNIT , F.DESCR , F.DESCRSHORT , A.AUC_ID , A.AUC_ROUND , A.AUC_VERSION , A.BIDDER_SETID , A.BIDDER_ID , A.BIDDER_TYPE , A.BIDDER_LOC , B.AUC_FORMAT , B.AUC_TYPE , B.AUC_NAME , B.AUC_NOIA , C.AUC_NOIA_BID_RESP , H.AUC_NOIA_PROTEST , %CurrentDateIn - %DatePart (C. AUC_LAST_NTFY_DTTM) , %DatePart (C. AUC_LAST_NTFY_DTTM ) , C.AUC_INVITATION_TYP , C.AUC_NOIA_VERSION FROM PS_BUS_UNIT_TBL_FS F , PS_AUC_DISP_HDR A , PS_AUC_HDR B , PS_AUC_NOIA_BIDDER C , PS_AUC_NOIA_HDR H WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.AUC_ID = B.AUC_ID AND A.AUC_ROUND = B.AUC_ROUND AND A.AUC_VERSION = B.AUC_VERSION AND B.AUC_NOIA = 'Y' AND A.BIDDER_TYPE = 'P' AND B.AUC_TYPE IN ('E','S') AND B.AUC_FORMAT IN ('F','R') AND B.AUC_STATUS IN ('T','A') AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.AUC_ID = C.AUC_ID AND B.AUC_ROUND = C.AUC_ROUND AND B.AUC_VERSION = C.AUC_VERSION AND B.AUC_ID = H.AUC_ID AND B.AUC_ROUND= H.AUC_ROUND AND B.AUC_VERSION = H.AUC_VERSION AND H.AUC_NOIA_SENT = 'Y' AND C.AUC_NOIA_VERSION = ( SELECT MAX(AUC_NOIA_VERSION) FROM PS_AUC_NOIA_BIDDER N WHERE N.BUSINESS_UNIT = C.BUSINESS_UNIT AND N.AUC_ID = C.AUC_ID AND N.AUC_ROUND = C.AUC_ROUND AND N.AUC_VERSION = C.AUC_VERSION ) AND C.AUC_INVITATION_TYP = 'I' AND NOT EXISTS ( SELECT 'X' FROM PS_AUC_NOIA_BIDDER X1 WHERE X1.BUSINESS_UNIT = C.BUSINESS_UNIT AND X1.AUC_ID = C.AUC_ID AND X1.AUC_ROUND = C.AUC_ROUND AND X1.AUC_VERSION = C.AUC_VERSION AND X1.AUC_NOIA_VERSION = C.AUC_NOIA_VERSION AND X1.AUC_INVITATION_TYP = 'P' ) UNION SELECT DISTINCT A.BUSINESS_UNIT , F.DESCR , F.DESCRSHORT , A.AUC_ID , A.AUC_ROUND , A.AUC_VERSION , A.BIDDER_SETID , A.BIDDER_ID , A.BIDDER_TYPE , A.BIDDER_LOC , B.AUC_FORMAT , B.AUC_TYPE , B.AUC_NAME , B.AUC_NOIA , C.AUC_NOIA_BID_RESP , H.AUC_NOIA_PROTEST , %CurrentDateIn - %DatePart (C. AUC_LAST_NTFY_DTTM) , %DatePart (C. AUC_LAST_NTFY_DTTM ) , C.AUC_INVITATION_TYP , C.AUC_NOIA_VERSION FROM PS_BUS_UNIT_TBL_FS F , PS_AUC_DISP_HDR A , PS_AUC_HDR B , PS_AUC_NOIA_BIDDER C , PS_AUC_NOIA_HDR H WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.AUC_ID = B.AUC_ID AND A.AUC_ROUND = B.AUC_ROUND AND A.AUC_VERSION = B.AUC_VERSION AND B.AUC_NOIA = 'Y' AND A.BIDDER_TYPE = 'P' AND B.AUC_TYPE IN ('E','S') AND B.AUC_FORMAT IN ('F','R') AND B.AUC_STATUS IN ('T','A') AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.AUC_ID = C.AUC_ID AND B.AUC_ROUND = C.AUC_ROUND AND B.AUC_VERSION = C.AUC_VERSION AND B.AUC_ID = H.AUC_ID AND B.AUC_ROUND= H.AUC_ROUND AND B.AUC_VERSION = H.AUC_VERSION AND H.AUC_NOIA_SENT = 'Y' AND C.AUC_NOIA_VERSION = ( SELECT MAX(AUC_NOIA_VERSION) FROM PS_AUC_NOIA_BIDDER N WHERE N.BUSINESS_UNIT = C.BUSINESS_UNIT AND N.AUC_ID = C.AUC_ID AND N.AUC_ROUND = C.AUC_ROUND AND N.AUC_VERSION = C.AUC_VERSION ) AND C.AUC_INVITATION_TYP = 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 DESCR Character(30) VARCHAR2(30) NOT NULL Description
3 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
4 AUC_ID Character(10) VARCHAR2(10) NOT NULL Event ID
5 AUC_ROUND Number(5,0) INTEGER NOT NULL Event Round
6 AUC_VERSION Number(5,0) INTEGER NOT NULL Event Version
7 BIDDER_SETID Character(5) VARCHAR2(5) NOT NULL Bidder Setid
8 BIDDER_ID Character(15) VARCHAR2(15) NOT NULL Bidder ID
9 BIDDER_TYPE Character(1) VARCHAR2(1) NOT NULL Bidder Type
B=Bidder
C=Customer
P=Public
U=Uploaded
V=Supplier
10 BIDDER_LOC Character(10) VARCHAR2(10) NOT NULL Bidder Location
11 AUC_FORMAT Character(1) VARCHAR2(1) NOT NULL Event Format
F=Sell
I=RFI
R=Buy
S=Service
12 AUC_TYPE Character(1) VARCHAR2(1) NOT NULL Event Type
E=Auction
S=RFx
13 AUC_NAME Character(50) VARCHAR2(50) NOT NULL Event Name
14 AUC_NOIA Character(1) VARCHAR2(1) NOT NULL Allow Send NOIA Notification
15 AUC_NOIA_BID_RESP Character(1) VARCHAR2(1) NOT NULL Responded
A=Yes
D=No
N=No
16 AUC_NOIA_PROTEST Date(10) DATE Notice End Date
17 NUM_DAYS_NOIA Number(4,0) SMALLINT NOT NULL Display NOIA Sent in Last
18 SCHED_DT_TO Date(10) DATE To Schedule/Ship
19 AUC_INVITATION_TYP Character(1) VARCHAR2(1) NOT NULL Invitation Type
B=Both
I=Invited
P=Public
20 AUC_NOIA_VERSION Number(3,0) SMALLINT NOT NULL NOIA Version