AUC_DISP_ACP_VW

(SQL View)

SELECT A.BUSINESS_UNIT ,A.AUC_ID ,A.AUC_ROUND ,A.AUC_VERSION ,A.BIDDER_SETID ,A.BIDDER_ID ,A.BIDDER_TYPE ,A.BIDDER_LOC ,A.AUC_DISPATCH_MTHD ,A.AUC_INVITE_ALL ,A.BID_INV_SOURCE ,A.BID_INV_RESPONSE ,A.BID_INV_SEND_EMAIL ,B.AUC_BID_STATUS FROM PS_AUC_DISP_HDR A , PS_RESP_HDR B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AUC_ID = B.AUC_ID AND A.AUC_ROUND= B.AUC_ROUND AND A.AUC_VERSION = B.AUC_VERSION AND A.BIDDER_SETID = B.BIDDER_SETID AND A.BIDDER_ID = B.BIDDER_ID AND A.BIDDER_TYPE = B.BIDDER_TYPE AND A.BIDDER_LOC = B.BIDDER_LOC AND A.BIDDER_TYPE <> 'P' AND A.BID_INV_RESPONSE = 'A' AND B.AUC_BID_STATUS = 'P' UNION SELECT A.BUSINESS_UNIT ,A.AUC_ID ,A.AUC_ROUND ,A.AUC_VERSION ,A.BIDDER_SETID ,A.BIDDER_ID ,A.BIDDER_TYPE ,A.BIDDER_LOC ,A.AUC_DISPATCH_MTHD ,A.AUC_INVITE_ALL ,A.BID_INV_SOURCE ,A.BID_INV_RESPONSE ,A.BID_INV_SEND_EMAIL ,' ' FROM PS_AUC_DISP_HDR A WHERE A.BIDDER_TYPE <> 'P' AND A.BID_INV_RESPONSE = 'A' AND NOT EXISTS ( SELECT 'X' FROM PS_RESP_HDR X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.AUC_ID = A.AUC_ID AND X.AUC_ROUND= A.AUC_ROUND AND X.AUC_VERSION = A.AUC_VERSION AND X.BIDDER_SETID = A.BIDDER_SETID AND X.BIDDER_ID = A.BIDDER_ID AND X.BIDDER_TYPE = A.BIDDER_TYPE AND X.BIDDER_LOC = A.BIDDER_LOC AND X.AUC_BID_STATUS = 'P')

# 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_TBL_A1

2 AUC_ID Character(10) VARCHAR2(10) NOT NULL Event ID

3 AUC_ROUND Number(5,0) INTEGER NOT NULL Event Round
Default Value: 1

4 AUC_VERSION Number(5,0) INTEGER NOT NULL Event Version
Default Value: 1

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 AUC_DISPATCH_MTHD Character(1) VARCHAR2(1) NOT NULL Dispatch Method
E=Email
F=Fax
P=Print

10 AUC_INVITE_ALL Character(1) VARCHAR2(1) NOT NULL Invite to Event
Default Value: Y

11 BID_INV_SOURCE Character(1) VARCHAR2(1) NOT NULL Invitation Source
S=Self Invited
U=Invited by User

12 BID_INV_RESPONSE Character(1) VARCHAR2(1) NOT NULL Invitation Accepted/Declined
A=Accepted
D=Declined
N=---

13 BID_INV_SEND_EMAIL Character(1) VARCHAR2(1) NOT NULL Receive Updates to Event
N=Don't send updates
Y=Send updates

14 AUC_BID_STATUS Character(1) VARCHAR2(1) NOT NULL Bid Status
D=Disqualified
P=Posted
S=Saved
W=Disallowed
X=Cancelled

#	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_TBL_A1
2	AUC_ID	Character(10)	VARCHAR2(10) NOT NULL	Event ID
3	AUC_ROUND	Number(5,0)	INTEGER NOT NULL	Event Round Default Value: 1
4	AUC_VERSION	Number(5,0)	INTEGER NOT NULL	Event Version Default Value: 1
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	AUC_DISPATCH_MTHD	Character(1)	VARCHAR2(1) NOT NULL	Dispatch Method E=Email F=Fax P=Print
10	AUC_INVITE_ALL	Character(1)	VARCHAR2(1) NOT NULL	Invite to Event Default Value: Y
11	BID_INV_SOURCE	Character(1)	VARCHAR2(1) NOT NULL	Invitation Source S=Self Invited U=Invited by User
12	BID_INV_RESPONSE	Character(1)	VARCHAR2(1) NOT NULL	Invitation Accepted/Declined A=Accepted D=Declined N=---
13	BID_INV_SEND_EMAIL	Character(1)	VARCHAR2(1) NOT NULL	Receive Updates to Event N=Don't send updates Y=Send updates
14	AUC_BID_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Bid Status D=Disqualified P=Posted S=Saved W=Disallowed X=Cancelled