BUSINESS_UNITBIDDER_IDBIDDER_TYPEB=Bidder
C=Customer
P=Public
U=Uploaded
V=Supplier
AUC_AWD_P1_VW(SQL View) |
Index Back |
|---|
| SELECT a.business_unit ,a.bidder_id ,a.bidder_type ,COUNT(a.auc_id) AS number1 ,(CASE WHEN SUM(rp.number2) IS NOT NULL THEN SUM(rp.number2) ELSE 0 END) AS number2 ,(CASE WHEN SUM(awl.number3) IS NOT NULL THEN SUM(awl.number3) ELSE 0 END) AS number3 ,(CASE WHEN SUM(awl.amount) IS NOT NULL THEN SUM(awl.amount) ELSE 0 END) AS amount ,(CASE WHEN SUM(rp.number2) IS NOT NULL THEN (%DecMult(%DecDiv(sum(awl.number3),SUM(rp.number2)),100)) ELSE 0 END) ,'USD' AS currency_cd FROM ps_auc_disp_hdr a ,ps_auc_disp_hdr a1 LEFT OUTER JOIN ps_auc_resp_p1_vw rp ON a1.business_unit = rp.business_unit AND a1.bidder_id = rp.bidder_id AND a1.bidder_setid =rp.bidder_setid AND a1.bidder_type =rp.bidder_type AND a1.auc_id = rp.auc_id AND a1.auc_round = rp.auc_round AND a1.auc_version =rp.auc_version ,ps_auc_disp_hdr a2 LEFT OUTER JOIN ps_auc_award_p1_vw awl ON a2.business_unit = awl.business_unit AND a2.bidder_id = awl.bidder_id AND a2.bidder_setid =awl.bidder_setid AND a2.bidder_type =awl.bidder_type AND a2.auc_id = awl.auc_id AND a2.auc_round = awl.auc_round AND a2.auc_version =awl.auc_version WHERE a.business_unit = a1.business_unit AND a.bidder_id = a1.bidder_id AND a.bidder_setid = a1.bidder_setid AND a.bidder_type = a1.bidder_type AND a.auc_id = a1.auc_id AND a.auc_round = a1.auc_round AND a.auc_version = a1.auc_version AND a.business_unit = a2.business_unit AND a.bidder_id = a2.bidder_id AND a.bidder_setid = a2.bidder_setid AND a.bidder_type = a2.bidder_type AND a.auc_id = a2.auc_id AND a.auc_round = a2.auc_round AND a.auc_version = a2.auc_version AND EXISTS( SELECT 'X' FROM PS_AUC_LINE b WHERE a.auc_id= b.auc_id AND a.auc_version= b.auc_version AND a.auc_round= b.auc_round AND a.business_unit= b.business_unit AND b.auc_version=( SELECT MAX(c.auc_version) FROM PS_AUC_LINE c WHERE c.auc_id= b.auc_id AND c.auc_round= b.auc_round AND c.business_unit= b.business_unit) AND b.auc_round=( SELECT MAX(d.auc_round) FROM PS_AUC_LINE d WHERE d.auc_id= b.auc_id AND d.business_unit= b.business_unit)) GROUP BY a.business_unit,a.bidder_id,a.bidder_type |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | BIDDER_ID |
Character(15) | VARCHAR2(15) NOT NULL | Bidder ID |
| 3 | BIDDER_TYPE |
Character(1) | VARCHAR2(1) NOT NULL |
Bidder Type
B=Bidder C=Customer P=Public U=Uploaded V=Supplier |
| 4 | NUMBER1 | Number(10,0) | DECIMAL(10) NOT NULL | Number |
| 5 | NUMBER2 | Number(10,0) | DECIMAL(10) NOT NULL | Number |
| 6 | NUMBER3 | Number(10,0) | DECIMAL(10) NOT NULL | Number |
| 7 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 8 | PERCENTAGE | Signed Number(7,2) | DECIMAL(5,2) NOT NULL | Percentage |
| 9 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |