RESP_TOT_LNY_VW

(SQL View)
 Index Back

Response Line

Used to store Response Line details

SELECT A.BUSINESS_UNIT , A.AUC_ID , A.AUC_ROUND , A.AUC_VERSION , A.BIDNUM , A.LINE_NBR , A.BIDDER_SETID , A.BIDDER_ID , A.BIDDER_TYPE , A.BIDDER_LOC , E.BID_CNTR_ACTION , E.IS_NOBID , %Round(SUM( (%DecDiv ( (%DecMult ( (%DecMult(A.BID_SCORE , B.WEIGHTING) ) , C.WEIGHTING )) , 100 ) )), 3) FROM PS_RESP_LN_FACTOR A , PS_AUC_LN_BID_FCTR B , PS_AUC_LINE C , PS_RESP_HDR D , PS_RESP_LN E WHERE B.AUC_ID = A.AUC_ID AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.AUC_ROUND = A.AUC_ROUND AND B.AUC_VERSION = A.AUC_VERSION AND B.LINE_NBR = A.LINE_NBR AND B.BID_FACTOR_NBR = A.BID_FACTOR_NBR AND B.SETID = A.SETID AND B.BID_FACTOR_CD = A.BID_FACTOR_CD AND B.BID_FACTOR_TYPE = A.BID_FACTOR_TYPE AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.AUC_ID = A.AUC_ID AND C.AUC_ROUND = A.AUC_ROUND AND C.AUC_VERSION = A.AUC_VERSION AND C.LINE_NBR = A.LINE_NBR AND E.BUSINESS_UNIT = A.BUSINESS_UNIT AND E.AUC_ID = A.AUC_ID AND E.AUC_ROUND = A.AUC_ROUND AND E.AUC_VERSION = A.AUC_VERSION AND E.BIDNUM = A.BIDNUM AND E.BIDDER_SETID = A.BIDDER_SETID AND E.BIDDER_ID = A.BIDDER_ID AND E.BIDDER_TYPE = A.BIDDER_TYPE AND E.BIDDER_LOC = A.BIDDER_LOC AND E.LINE_NBR = A.LINE_NBR AND D.BUSINESS_UNIT = A.BUSINESS_UNIT AND D.AUC_ID = A.AUC_ID AND D.AUC_ROUND = A.AUC_ROUND AND D.AUC_VERSION = A.AUC_VERSION AND D.BIDNUM = A.BIDNUM AND D.BIDDER_SETID = A.BIDDER_SETID AND D.BIDDER_ID = A.BIDDER_ID AND D.BIDDER_TYPE = A.BIDDER_TYPE AND D.BIDDER_LOC = A.BIDDER_LOC AND (D.AUC_BID_STATUS = 'P' OR d.auc_bid_status = 'D') AND A.BIDNUM = ( SELECT MAX(Y.BIDNUM) FROM PS_RESP_LN_FACTOR Y ,PS_RESP_HDR HY WHERE HY.BUSINESS_UNIT = Y.BUSINESS_UNIT AND HY.AUC_ID = Y.AUC_ID AND HY.AUC_ROUND = Y.AUC_ROUND AND HY.AUC_VERSION = Y.AUC_VERSION AND HY.BIDNUM = Y.BIDNUM AND HY.BIDDER_SETID = Y.BIDDER_SETID AND HY.BIDDER_ID = Y.BIDDER_ID AND HY.BIDDER_TYPE = Y.BIDDER_TYPE AND HY.BIDDER_LOC = Y.BIDDER_LOC AND HY.AUC_BID_STATUS = 'P' AND Y.BUSINESS_UNIT = A.BUSINESS_UNIT AND Y.AUC_ID = A.AUC_ID AND Y.AUC_ROUND = B.AUC_ROUND AND Y.AUC_VERSION = A.AUC_VERSION AND Y.BIDDER_SETID = A.BIDDER_SETID AND Y.BIDDER_ID = A.BIDDER_ID AND Y.BIDDER_TYPE = A.BIDDER_TYPE AND Y.BIDDER_LOC = A.BIDDER_LOC AND Y.LINE_NBR = A.LINE_NBR AND Y.BID_SCORE = ( SELECT MAX(X.BID_SCORE) FROM PS_RESP_LN_FACTOR X ,PS_RESP_HDR HX WHERE HX.BUSINESS_UNIT = X.BUSINESS_UNIT AND HX.AUC_ID = X.AUC_ID AND HX.AUC_ROUND = X.AUC_ROUND AND HX.AUC_VERSION = X.AUC_VERSION AND HX.BIDNUM = X.BIDNUM AND HX.BIDDER_SETID = X.BIDDER_SETID AND HX.BIDDER_ID = X.BIDDER_ID AND HX.BIDDER_TYPE = X.BIDDER_TYPE AND HX.BIDDER_LOC = X.BIDDER_LOC AND HX.AUC_BID_STATUS = 'P' AND 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.LINE_NBR = A.LINE_NBR )) GROUP BY A.BUSINESS_UNIT, A.AUC_ID, A.AUC_ROUND, A.AUC_VERSION, A.BIDNUM, A.LINE_NBR , A.BIDDER_SETID , A.BIDDER_ID, A.BIDDER_TYPE, A.BIDDER_LOC , E.BID_CNTR_ACTION, E.IS_NOBID HAVING SUM(C.WEIGHTING) > 0 UNION SELECT A.BUSINESS_UNIT , A.AUC_ID , A.AUC_ROUND , A.AUC_VERSION , A.BIDNUM , A.LINE_NBR , A.BIDDER_SETID , A.BIDDER_ID , A.BIDDER_TYPE , A.BIDDER_LOC , E.BID_CNTR_ACTION , E.IS_NOBID , %Round(SUM((%DecDiv ((%DecMult(A.BID_SCORE , B.WEIGHTING)) , (%DecMult(D.AUC_NUM_LINES , 100)) ))), 3) FROM PS_RESP_LN_FACTOR A , PS_AUC_LN_BID_FCTR B , PS_AUC_LINE C , PS_AUC_RESP_HDR_VW D , PS_RESP_LN E WHERE B.AUC_ID = A.AUC_ID AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.AUC_ROUND = A.AUC_ROUND AND B.AUC_VERSION = A.AUC_VERSION AND B.LINE_NBR = A.LINE_NBR AND B.BID_FACTOR_NBR = A.BID_FACTOR_NBR AND B.SETID = A.SETID AND B.BID_FACTOR_CD = A.BID_FACTOR_CD AND B.BID_FACTOR_TYPE = A.BID_FACTOR_TYPE AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.AUC_ID = A.AUC_ID AND C.AUC_ROUND = A.AUC_ROUND AND C.AUC_VERSION = A.AUC_VERSION AND C.LINE_NBR = A.LINE_NBR AND E.BUSINESS_UNIT = A.BUSINESS_UNIT AND E.AUC_ID = A.AUC_ID AND E.AUC_ROUND = A.AUC_ROUND AND E.AUC_VERSION = A.AUC_VERSION AND E.BIDNUM = A.BIDNUM AND E.BIDDER_SETID = A.BIDDER_SETID AND E.BIDDER_ID = A.BIDDER_ID AND E.BIDDER_TYPE = A.BIDDER_TYPE AND E.BIDDER_LOC = A.BIDDER_LOC AND E.LINE_NBR = A.LINE_NBR AND D.BUSINESS_UNIT = A.BUSINESS_UNIT AND D.AUC_ID = A.AUC_ID AND D.AUC_ROUND = A.AUC_ROUND AND D.AUC_VERSION = A.AUC_VERSION AND D.BIDNUM = A.BIDNUM AND D.BIDDER_SETID = A.BIDDER_SETID AND D.BIDDER_ID = A.BIDDER_ID AND D.BIDDER_TYPE = A.BIDDER_TYPE AND D.BIDDER_LOC = A.BIDDER_LOC AND (D.AUC_BID_STATUS = 'P' OR d.auc_bid_status = 'D') GROUP BY A.BUSINESS_UNIT, A.AUC_ID, A.AUC_ROUND, A.AUC_VERSION, A.BIDNUM, A.LINE_NBR , A.BIDDER_SETID , A.BIDDER_ID, A.BIDDER_TYPE, A.BIDDER_LOC, E.BID_CNTR_ACTION , E.IS_NOBID, D.AUC_NUM_LINES HAVING SUM(C.WEIGHTING) = 0

# 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

Default Value: 1

4 AUC_VERSION Number(5,0) INTEGER NOT NULL Event Version

Default Value: 1

5 BIDNUM Signed Number(6,0) DECIMAL(5) NOT NULL Bid ID
6 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
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 BID_CNTR_ACTION Character(1) VARCHAR2(1) NOT NULL Bid Counter Action
A=Accept
C=Counter
W=Withdrawal
12 IS_NOBID Character(1) VARCHAR2(1) NOT NULL No Bid

Y/N Table Edit

13 AUC_LN_BF_SCR_TTL Number(11,7) DECIMAL(10,7) NOT NULL Total Line Bid Factor Score