|
SELECT aa ,bb ,cc ,dd ,SUM(ee) ,ff ,gg FROM ( SELECT a.auc_id aa ,a.business_unit bb ,a.auc_version cc ,a.auc_round dd ,%DecMult(a.auc_ext_price,( SELECT DISTINCT z.cur_exchng_rt FROM ps_cur_rt_tbl z ,PS_AUC_HDR q WHERE q.auc_id=a.auc_id AND q.auc_version=a.auc_version AND q.auc_round=a.auc_round AND q.business_unit=a.business_unit AND z.from_cur=a.currency_cd AND z.to_cur='USD' AND z.cur_rt_type = q.rt_type AND z.effdt=( SELECT MAX(r.effdt) FROM ps_cur_rt_tbl r WHERE r.from_cur=z.from_cur AND r.to_cur=z.to_cur AND r.cur_rt_type=q.rt_type AND r.effdt<=%currentdatein AND r.eff_status='A'))) ee , b.bidder_id ff ,b.bidder_setid gg FROM ps_auc_line a ,PS_RESP_HDR 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 a.auc_version=( SELECT MAX(c.auc_version) FROM ps_auc_hdr c WHERE c.auc_id=b.auc_id AND c.auc_round=b.auc_round AND c.business_unit=b.business_unit ) AND a.auc_round=( SELECT MAX(d.auc_round) FROM ps_auc_hdr d WHERE d.auc_id=b.auc_id AND d.business_unit=b.business_unit ) ) ttt GROUP BY aa,bb,cc,dd,ff,gg
|