[Home] [Help]
PACKAGE BODY: APPS.PON_BUYER_SUMMARY_PKG
Source
1 PACKAGE BODY PON_BUYER_SUMMARY_PKG AS
2 -- $Header: PONBUSUB.pls 120.1 2005/07/27 09:05:28 rpatel noship $
3
4 PROCEDURE calculate_summary
5 (
6 P_AUCTION_ID in NUMBER,
7 P_BATCH_ID OUT NOCOPY NUMBER
8 )
9 IS
10 -- select only awardable group types for autoaward selection
11 CURSOR c_auction_item (v_auction_header_id number) IS
12 SELECT line_number, nvl(quantity,1) quantity, current_price
13 FROM pon_auction_item_prices_all
14 WHERE auction_header_id = v_auction_header_id
15 AND group_type IN ('LOT', 'LINE', 'GROUP_LINE');
16 --
17 CURSOR c_bid_item (v_auction_header_id number, v_auction_line_number number) IS
18 SELECT bl.price,
19 bl.quantity quantity,
20 bh.bid_number,
21 bh.trading_partner_name,
22 bh.trading_partner_id,
23 bh.trading_partner_contact_id,
24 bl.rank
25 FROM pon_bid_headers bh,
26 pon_bid_item_prices bl,
27 pon_auction_headers_all ah,
28 po_vendors pv,
29 hz_parties hp
30 WHERE ah.auction_header_id = bh.auction_header_id and
31 bh.auction_header_id = bl.auction_header_id
32 and bh.bid_number = bl.bid_number
33 and bh.bid_status = 'ACTIVE'
34 and bh.auction_header_id = v_auction_header_id
35 and bl.auction_line_number = v_auction_line_number
36 and nvl(bh.SHORTLIST_FLAG, 'Y') = 'Y'
37 and bh.trading_partner_id = hp.party_id
38 and hp.party_type = 'ORGANIZATION'
39 and hp.party_id = pv.party_id
40 and (pv.end_date_active IS NULL OR pv.end_date_active > SYSDATE)
41 ORDER BY decode(ah.bid_ranking, 'PRICE_ONLY', 1/bl.price, nvl(bl.total_weighted_score,0)/bl.price) desc ,bl.publish_date asc;
42
43 v_qty_remaining NUMBER;
44 v_price NUMBER;
45 v_quantity NUMBER;
46 v_tp_id NUMBER;
47 v_tp_name VARCHAR2(255);
48 v_auction_type VARCHAR2(25);
49
50 v_bid_number NUMBER;
51 v_qty_award NUMBER;
52 v_batch_id NUMBER;
53
54 v_tp_contact_id NUMBER;
55 v_rank NUMBER;
56 --
57 BEGIN
58 --
59 select pon_auction_summary_s.nextval
60 into v_batch_id
61 from dual;
62 --
63 SELECT nvl(auction_type, 'REVERSE') INTO v_auction_type
64 FROM pon_auction_headers_all
65 WHERE auction_header_id = P_AUCTION_ID;
66 --
67 --
68 FOR auction_item IN c_auction_item (p_auction_id) LOOP
69 v_qty_remaining := auction_item.quantity;
70
71 OPEN c_bid_item (p_auction_id, auction_item.line_number);
72
73 fetch c_bid_item into
74 v_price,
75 v_quantity,
76 v_bid_number,
77 v_tp_name,
78 v_tp_id,
79 v_tp_contact_id,
80 v_rank;
81 --
82 while (c_bid_item%FOUND) loop
83
84 if (v_qty_remaining > v_quantity ) then
85 v_qty_award := nvl(v_quantity,1);
86 else
87 v_qty_award := v_qty_remaining;
88 end if;
89 --
90 insert into pon_auction_summary
91 (batch_id,
92 auction_id,
93 line_number,
94 bid_number,
95 trading_partner_name,
96 trading_partner_id,
97 trading_partner_contact_id,
98 award_quantity,
99 bid_price,
100 auction_price,
101 response_quantity,
102 rank)
103 values (v_batch_id,
104 p_auction_id,
105 auction_item.line_number,
106 v_bid_number,
107 v_tp_name,
108 v_tp_id,
109 v_tp_contact_id,
110 v_qty_award,
111 v_price,
112 auction_item.current_price,
113 v_quantity,
114 v_rank);
115 --
116 v_qty_remaining := v_qty_remaining - v_qty_award;
117 --
118 fetch c_bid_item into v_price,
119 v_quantity,
120 v_bid_number,
121 v_tp_name,
122 v_tp_id,
123 v_tp_contact_id,
124 v_rank;
125 --
126 end loop;
127 --
128 close c_bid_item;
129 --
130 END LOOP;
131 --
132 insert into pon_auction_summary
133 (batch_id,
134 auction_id,
135 line_number,
136 bid_number,
137 trading_partner_name,
138 trading_partner_id,
139 trading_partner_contact_id,
140 award_quantity,
141 bid_price,
142 auction_price,
143 response_quantity)
144 select v_batch_id,
145 p_auction_id,
146 0,
147 0,
148 nvl(bp.trading_partner_name,bp.new_supplier_name),
149 bp.trading_partner_id,
150 bp.trading_partner_contact_id,
151 0,
152 0,
153 0,
154 0
155 from pon_bidding_parties bp
156 where auction_header_id = p_auction_id and
157 not exists (select 'exist' from pon_auction_summary where
158 trading_partner_id = bp.trading_partner_id and
159 batch_id = v_batch_id and
160 auction_header_id = p_auction_id);
161 --
162 commit;
163 --
164 P_BATCH_ID := v_batch_id;
165 END;
166
167 PROCEDURE bid_count_info (P_AUCTION_ID IN NUMBER,
168 P_NO_BID_OPEN OUT NOCOPY NUMBER,
169 P_NO_BID_CLOSED OUT NOCOPY NUMBER,
170 P_PART_BID_OPEN OUT NOCOPY NUMBER,
171 P_PART_BID_CLOSED OUT NOCOPY NUMBER,
172 P_FULL_BID_OPEN OUT NOCOPY NUMBER,
173 P_FULL_BID_CLOSED OUT NOCOPY NUMBER) IS
174
175
176 BEGIN
177
178 select count(*)
179 into p_no_bid_open
180 from pon_auction_item_prices_all poi, pon_auction_headers_all poh
181 where poi.auction_header_id = p_auction_id
182 and poh.auction_header_id = poi.auction_header_id
183 and poh.open_bidding_date < sysdate
184 and nvl(poi.close_bidding_date, poh.close_bidding_date) > sysdate
185 and nvl(poi.number_of_bids,0) = 0 ;
186
187 select count(*)
188 into p_no_bid_closed
189 from pon_auction_item_prices_all poi, pon_auction_headers_all poh
190 where poi.auction_header_id = p_auction_id
191 and poh.auction_header_id = poi.auction_header_id
192 and poh.open_bidding_date < sysdate
193 and nvl(poi.close_bidding_date, poh.close_bidding_date) <= sysdate
194 and nvl(poi.number_of_bids,0) = 0 ;
195
196 select count(*)
197 into p_part_bid_open
198 from pon_auction_item_prices_all poi, pon_auction_headers_all poh
199 where poi.auction_header_id = p_auction_id
200 and poh.auction_header_id = poi.auction_header_id
201 and poh.open_bidding_date < sysdate
202 and nvl(poi.close_bidding_date, poh.close_bidding_date) > sysdate
203 and nvl(poi.number_of_bids,0) > 0
204 and poi.quantity >
205 (select sum(pbi.quantity)
206 from pon_bid_item_prices pbi
207 where pbi.auction_header_id = p_auction_id
208 and pbi.line_number = poi.line_number);
209
210 select count(*)
211 into p_part_bid_closed
212 from pon_auction_item_prices_all poi, pon_auction_headers_all poh
213 where poi.auction_header_id = p_auction_id
214 and poh.auction_header_id = poi.auction_header_id
215 and poh.open_bidding_date < sysdate
216 and nvl(poi.close_bidding_date, poh.close_bidding_date) <= sysdate
217 and nvl(poi.number_of_bids,0) > 0
218 and poi.quantity >
219 (select sum(pbi.quantity)
220 from pon_bid_item_prices pbi
221 where pbi.auction_header_id = p_auction_id
222 and pbi.line_number = poi.line_number);
223
224 select count(*)
225 into p_full_bid_open
226 from pon_auction_item_prices_all poi, pon_auction_headers_all poh
227 where poi.auction_header_id = p_auction_id
228 and poh.auction_header_id = poi.auction_header_id
229 and poh.open_bidding_date < sysdate
230 and nvl(poi.close_bidding_date, poh.close_bidding_date) > sysdate
231 and nvl(poi.number_of_bids,0) > 0
232 and poi.quantity <=
233 (select sum(pbi.quantity)
234 from pon_bid_item_prices pbi
235 where pbi.auction_header_id = p_auction_id
236 and pbi.line_number = poi.line_number);
237
238
239
240 select count(*)
241 into p_full_bid_closed
242 from pon_auction_item_prices_all poi, pon_auction_headers_all poh
243 where poi.auction_header_id = p_auction_id
244 and poh.open_bidding_date < sysdate
245 and poh.auction_header_id = poi.auction_header_id
246 and nvl(poi.close_bidding_date, poh.close_bidding_date) <= sysdate
247 and nvl(poi.number_of_bids,0) > 0
248 and poi.quantity <=
249 (select sum(pbi.quantity)
250 from pon_bid_item_prices pbi
251 where pbi.auction_header_id = p_auction_id
252 and pbi.line_number = poi.line_number);
253
254 END;
255 --
256 END PON_BUYER_SUMMARY_PKG;