AUC_ERN_VW

(SQL View)
 Index Back

Event Reminder Notification

Event Reminder Notification View

SELECT H.BUSINESS_UNIT , H.AUC_ID , H.AUC_ROUND , H.AUC_VERSION , H.BIDDER_SETID , H.BIDDER_ID , H.BIDDER_TYPE , H.BIDDER_LOC , 0 , H.BID_INV_RESPONSE , E.SEQ_NBR , E.AUC_EVNT_END_DAYS , E.AUC_END_DATE /*, E.AUC_AUDIENCE */ , CASE BID_INV_RESPONSE WHEN 'N' THEN 'N' WHEN 'A' THEN 'C' END , E.AUC_MSG_PRIORITY , A.AUC_DTTM_START , A.AUC_DTTM_FINISH , A.TIMEZONE , A.AUC_EVNT_REMIND_FL , A.AUC_EC_NOTIFY_DAYS FROM PS_AUC_DISP_HDR H , PS_AUC_EVNT_ERN E , PS_AUC_HDR A WHERE H.BID_INV_RESPONSE IN ('A', 'N') AND H.BUSINESS_UNIT=E.BUSINESS_UNIT AND H.AUC_ID=E.AUC_ID AND H.AUC_ROUND=E.AUC_ROUND AND H.AUC_VERSION=E.AUC_VERSION AND A.BUSINESS_UNIT=E.BUSINESS_UNIT AND A.AUC_ID=E.AUC_ID AND A.AUC_ROUND=E.AUC_ROUND AND A.AUC_VERSION = E.AUC_VERSION AND A.AUC_EVNT_REMIND_FL = 'Y' AND A.AUC_STATUS = 'P' AND NOT EXISTS ( SELECT 'X' FROM PS_RESP_HDR R WHERE R.BUSINESS_UNIT=H.BUSINESS_UNIT AND R.AUC_ID=H.AUC_ID AND R.AUC_ROUND=H.AUC_ROUND AND R.AUC_VERSION=H.AUC_VERSION AND R.BIDDER_ID=H.BIDDER_ID) UNION SELECT H.BUSINESS_UNIT , H.AUC_ID , H.AUC_ROUND , H.AUC_VERSION , H.BIDDER_SETID , H.BIDDER_ID , H.BIDDER_TYPE , H.BIDDER_LOC , H.BIDNUM , H.AUC_BID_STATUS , E.SEQ_NBR , E.AUC_EVNT_END_DAYS , E.AUC_END_DATE /* , E.AUC_AUDIENCE */ , CASE AUC_BID_STATUS WHEN 'S' THEN 'I' END , E.AUC_MSG_PRIORITY , A.AUC_DTTM_START , A.AUC_DTTM_FINISH , A.TIMEZONE , A.AUC_EVNT_REMIND_FL , A.AUC_EC_NOTIFY_DAYS FROM PS_RESP_HDR H , PS_AUC_EVNT_ERN E , PS_AUC_HDR A WHERE AUC_BID_STATUS = 'S' AND H.BUSINESS_UNIT=E.BUSINESS_UNIT AND H.AUC_ID=E.AUC_ID AND H.AUC_ROUND=E.AUC_ROUND AND H.AUC_VERSION=E.AUC_VERSION AND A.BUSINESS_UNIT=E.BUSINESS_UNIT AND A.AUC_ID=E.AUC_ID AND A.AUC_ROUND=E.AUC_ROUND AND A.AUC_VERSION = E.AUC_VERSION AND A.AUC_EVNT_REMIND_FL = 'Y' AND A.AUC_STATUS = 'P' AND H.BIDNUM = ( SELECT MAX(B.BIDNUM) FROM PS_RESP_HDR B WHERE B.BUSINESS_UNIT=H.BUSINESS_UNIT AND B.AUC_ID=H.AUC_ID AND B.AUC_ROUND=H.AUC_ROUND AND B.AUC_VERSION=H.AUC_VERSION AND B.BIDDER_ID = H.BIDDER_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 AUC_ID Character(10) VARCHAR2(10) NOT NULL Event ID
3 AUC_ROUND Number(5,0) INTEGER NOT NULL Event Round
4 AUC_VERSION Number(5,0) INTEGER NOT NULL Event Version
5 BIDDER_SETID Character(5) VARCHAR2(5) NOT NULL Bidder Setid
6 BIDDER_ID Character(15) VARCHAR2(15) NOT NULL Bidder ID
7 BIDDER_TYPE Character(1) VARCHAR2(1) NOT NULL Bidder Type
B=Bidder
C=Customer
P=Public
U=Uploaded
V=Supplier
8 BIDDER_LOC Character(10) VARCHAR2(10) NOT NULL Bidder Location
9 BIDNUM Signed Number(6,0) DECIMAL(5) NOT NULL Bid ID
10 BID_INV_RESPONSE Character(1) VARCHAR2(1) NOT NULL Invitation Accepted/Declined
A=Accepted
D=Declined
N=---
11 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
12 AUC_EVNT_END_DAYS Number(3,0) SMALLINT NOT NULL Day(s) Prior to Event End Date
13 AUC_END_DATE Date(10) DATE End Date
14 AUC_AUDIENCE Character(1) VARCHAR2(1) NOT NULL Audience
A=All
C=Accepted
I=In Progress
N=Not Started
15 AUC_MSG_PRIORITY Character(1) VARCHAR2(1) NOT NULL Message Priority
H=High
N=Normal
16 AUC_DTTM_START DateTime(26) TIMESTAMP Date Time Start
17 AUC_DTTM_FINISH DateTime(26) TIMESTAMP Date Time Finish
18 TIMEZONE Character(9) VARCHAR2(9) NOT NULL Time Zone
19 AUC_EVNT_REMIND_FL Character(1) VARCHAR2(1) NOT NULL Allow Event Reminder Notificat
20 AUC_EC_NOTIFY_DAYS Number(3,0) SMALLINT NOT NULL Notification to Event Creator