1 PACKAGE BODY PON_UNSOL_AWARD_PKG AS
2 --$Header: PON_UNSOL_AWARD_PKG.plb 120.4 2012/01/06 05:02:54 sgulkota noship $
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_UNSOL_AWARD_PKG.';
5
6 TYPE integerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7
8 PROCEDURE clear_unsol_draft_awards(p_auc_header_id IN NUMBER,
9 p_auctioneer_id IN NUMBER,
10 x_status OUT NOCOPY VARCHAR2) IS
11
12 l_has_unsol_lines VARCHAR2(1);
13 BEGIN
14 x_status := 'SUCCESS';
15 IF(PON_UNSOL_UTIL_PKG.HAS_UNSOL_LINES(p_auc_header_id) = 'Y') THEN
16 --Unsolicited Lines Exist
17 UPDATE pon_bid_item_prices
18 SET award_status = 'REJECTED',
19 award_quantity = NULL,
20 award_date = sysdate,
21 last_update_date = sysdate,
22 last_updated_by = p_auctioneer_id,
23 award_shipment_number = NULL,
24 award_price = NULL
25 WHERE bid_number IN ( SELECT bid_number
26 FROM pon_bid_headers
27 WHERE auction_header_id = p_auc_header_id
28 AND bid_status = 'ACTIVE')
29 AND auction_line_number = -1;
30
31 -- Delete All Unsol line Awards since it is a header-level awarding
32 DELETE FROM pon_acceptances
33 WHERE auction_header_id = p_auc_header_id
34 AND auction_line_number = -1;
35
36 PON_AWARD_PKG.update_bid_headers(p_auction_id => p_auc_header_id,
37 p_auctioneer_id => p_auctioneer_id,
38 p_neg_has_lines => 'Y');
39 ELSE
40 --Unsolicited Lines donot Exist
41 x_status := 'NO_LINE';
42 END IF;
43
44 END clear_unsol_draft_awards;
45
46 PROCEDURE clear_unsol_award_for_bid(p_bid_number IN NUMBER,
47 p_auctioneer_id IN NUMBER,
48 p_award_date IN DATE) IS
49
50 BEGIN
51 UPDATE pon_bid_item_prices
52 SET award_status = 'REJECTED',
53 award_quantity = NULL,
54 award_date = p_award_date,
55 last_update_date = p_award_date,
56 last_updated_by = p_auctioneer_id,
57 award_shipment_number = NULL,
58 award_price = NULL
59 WHERE bid_number = p_bid_number
60 AND auction_line_number = -1;
61
62 END clear_unsol_award_for_bid;
63
64 PROCEDURE clear_loosing_unsol_awards(p_auc_header_id IN NUMBER,
65 p_auctioneer_id IN NUMBER,
66 p_award_table IN PON_AWARD_TABLE,
67 p_award_date IN DATE) IS
68
69 l_size NUMBER;
70 TYPE bid_number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
71 l_winning_bids_tbl bid_number_type;
72 l_bid_index NUMBER;
73 l_rec PON_AWARD_REC;
74
75 CURSOR c_unsol_active_bids (c_auction_id NUMBER) is
76 SELECT bh.bid_number
77 FROM pon_bid_headers bh,
78 pon_bid_item_prices pbip
79 WHERE bh.auction_header_id = c_auction_id
80 AND bid_status = 'ACTIVE'
81 AND pbip.bid_number = bh.bid_number
82 AND pbip.auction_line_number = -1;
83
84 l_unsol_active_bids_rec c_unsol_active_bids%ROWTYPE;
85 l_winning_bid NUMBER;
86 l_clear_unsol_award VARCHAR2(1);
87
88 BEGIN
89 l_size := p_award_table.COUNT;
90 l_bid_index := 0;
91 --Iterate through i/p award table and get all the winning bid numbers.
92 FOR l_index IN 1..l_size LOOP
93 l_rec := p_award_table(l_index);
94 IF l_rec.award_outcome = 'WIN' THEN
95 l_bid_index := l_bid_index + 1;
96 l_winning_bids_tbl(l_bid_index) := l_rec.bid_number;
97 END IF;
98 END LOOP;
99
100 --Fetch all active bids with unsolicited lines.
101 OPEN c_unsol_active_bids (p_auc_header_id);
102 LOOP
103 FETCH c_unsol_active_bids into l_unsol_active_bids_rec;
104 EXIT WHEN c_unsol_active_bids%NOTFOUND;
105 l_clear_unsol_award := 'N';
106 --Find out if this bid is in winning bid list.
107 FOR k IN 1..l_winning_bids_tbl.last LOOP
108 IF l_unsol_active_bids_rec.bid_number = l_winning_bids_tbl(k) THEN
109 l_clear_unsol_award := 'Y';
110 EXIT;
111 END IF;
112 END LOOP; --End l_winning_bids_tbl loop.
113
114 --If this bid is not in winning bids list, clear unsolicited lines awarded.
115 IF l_clear_unsol_award = 'N' THEN
116 clear_unsol_award_for_bid(l_unsol_active_bids_rec.bid_number,p_auctioneer_id,p_award_date);
117 END IF;
118 END LOOP; --End c_unsol_active_bids loop
119 CLOSE c_unsol_active_bids;
120
121 END clear_loosing_unsol_awards;
122
123 PROCEDURE award_unsol_lines(p_auction_header_id IN NUMBER,
124 p_auctioneer_id IN NUMBER,
125 p_award_table IN PON_AWARD_TABLE,
126 p_mode IN VARCHAR2,
127 x_status OUT NOCOPY VARCHAR2) IS
128
129 l_counter BINARY_INTEGER;
130 l_size NUMBER;
131 l_index BINARY_INTEGER;
132 l_rec PON_AWARD_REC;
133
134 l_award_date DATE;
135
136 l_neg_contract_type pon_auction_headers_all.contract_type%TYPE;
137 l_award_quantity pon_bid_item_prices.award_quantity%TYPE;
138 l_award_status pon_bid_item_prices.award_status%TYPE;
139
140 BEGIN
141
142 l_award_date := SYSDATE;
143 -- Need to expand all the AWARDED bids to build the lines
144 l_size := p_award_table.COUNT;
145 FOR l_index IN 1..l_size LOOP -- Loop through all the bids
146 l_rec := p_award_table(l_index);
147
148 IF(l_rec.award_outcome = 'WIN') THEN
149 l_award_status := 'AWARDED';
150 ELSE
151 l_award_status := 'REJECTED';
152 END IF;
153
154 --IF(p_mode = 'AWARD_LINE') THEN
155 --Always set the award quantity as the logic is taken care in java layer
156 l_award_quantity := l_rec.award_quantity;
157 --ELSE
158 -- l_award_quantity := NULL;
159 --END IF;
160
161 UPDATE PON_BID_ITEM_PRICES
162 SET
163 award_status = l_award_status,
164 award_quantity = l_award_quantity,
165 award_date = l_award_date,
166 last_update_date = l_award_date,
167 last_updated_by = p_auctioneer_id,
168 award_price = Decode(l_award_status, 'AWARDED', price, NULL)
169 WHERE bid_number = l_rec.bid_number
170 AND line_number = l_rec.line_number;
171
172 END LOOP;
173
174 PON_AWARD_PKG.update_bid_headers(p_auction_id => p_auction_header_id,
175 p_auctioneer_id => p_auctioneer_id,
176 p_neg_has_lines => 'Y');
177 PON_AWARD_PKG.update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
178
179 x_status := 'SUCCESS';
180
181 END award_unsol_lines;
182
183
184 /* This procedure should be called only when there are unsolicited lines
185 * on any of the offers.
186 */
187 PROCEDURE reject_unawarded_unsol_bids(p_auction_header_id IN NUMBER,
188 p_user_id IN NUMBER,
189 p_note_to_rejected IN VARCHAR2) IS
190
191 --This cursor will fetch all the active bids.
192 CURSOR c_active_bids IS
193 SELECT bid_number, Decode((SELECT Count(*) FROM pon_bid_item_prices pbip WHERE pbip.bid_number = bh.bid_number),0,'N','Y') has_lines
194 FROM pon_bid_headers bh
195 WHERE bh.auction_header_id = p_auction_header_id
196 AND bid_status = 'ACTIVE';
197
198 --This cursor will fetch all the bid lines with no award decision.
199 CURSOR c_bid_lines_with_no_award(c_bid_number NUMBER) IS
200 (SELECT al.line_number,
201 al.line_origination_code,
202 nvl(al.award_status,'NO') award_status,
203 bl.bid_number,
204 bl.auction_line_number,
205 bl.order_number,
206 bl.award_quantity
207 FROM pon_bid_headers bh,
208 pon_bid_item_prices bl,
209 pon_auction_item_prices_all al
210 where bh.bid_number = c_bid_number
211 AND bl.bid_number = bh.bid_number
212 AND bl.auction_line_number <> -1
213 AND al.auction_header_id = bh.auction_header_id
214 AND al.line_number = bl.line_number
215 AND nvl(bl.award_status,'NO') = 'NO')
216 UNION
217 (SELECT bl.line_number,
218 '' AS line_origination_code,
219 Nvl(bl.award_status,'NO') award_status,
220 bl.bid_number,
221 bl.auction_line_number,
222 bl.order_number,
223 bl.award_quantity
224 FROM pon_bid_item_prices bl
225 WHERE bl.bid_number = c_bid_number
226 AND bl.auction_line_number = -1
227 AND nvl(bl.award_status,'NO') = 'NO');
228
229 l_active_bids_rec c_active_bids%ROWTYPE;
230 l_lines_rec c_bid_lines_with_no_award%ROWTYPE;
231 l_update_auction_headers VARCHAR2(1);
232 l_update_bid_header VARCHAR2(1);
233 l_rejected_bid_numbers integerList;
234 l_line_number_list integerList;
235 l_line_number_found BOOLEAN;
236 l_count NUMBER;
237 l_stored_note_to_rejected pon_acceptances.reason%type;
238 l_error_code VARCHAR2(20);
239
240 BEGIN
241 l_update_auction_headers := 'N';
242 OPEN c_active_bids;
243 LOOP
244 FETCH c_active_bids INTO l_active_bids_rec;
245 EXIT WHEN c_active_bids%NOTFOUND;
246 l_update_bid_header := 'N';
247 IF l_active_bids_rec.has_lines = 'Y' THEN
248 OPEN c_bid_lines_with_no_award(l_active_bids_rec.bid_number);
249 LOOP
250 FETCH c_bid_lines_with_no_award INTO l_lines_rec;
251 EXIT WHEN c_bid_lines_with_no_award%NOTFOUND;
252
253 l_update_bid_header := 'Y';
254
255 IF l_lines_rec.auction_line_number = -1 THEN
256 -- This is an unsolicited line with no award decision
257 UPDATE PON_BID_ITEM_PRICES
258 SET
259 award_status = 'REJECTED',
260 award_quantity = null,
261 award_date = sysdate,
262 last_update_date = sysdate,
263 last_updated_by = p_user_id,
264 award_price = null
265 WHERE bid_number = l_active_bids_rec.bid_number
266 AND line_number = l_lines_rec.line_number;
267 ELSE
268 -- This is an neg line with no award decision
269
270 --If line with no award decision is backed up by req,put it back to the pool.
271 if (l_lines_rec.line_origination_code = 'REQUISITION' AND l_lines_rec.award_status = 'NO') then
272 PON_AUCTION_PKG.CANCEL_NEGOTIATION_REF_BY_LINE(p_auction_header_id, l_lines_rec.line_number, l_error_code);
273 end if;
274
275 pon_award_pkg.update_single_bid_item_prices
276 (
277 l_active_bids_rec.bid_number,
278 l_lines_rec.line_number,
279 'REJECTED',
280 l_lines_rec.award_quantity,
281 sysdate,
282 p_user_id
283 );
284
285 l_line_number_found := FALSE;
286
287 FOR i IN 1 .. l_line_number_list.COUNT LOOP
288 IF l_lines_rec.line_number = l_line_number_list(i) THEN
289 l_line_number_found := TRUE;
290 EXIT;
291 END IF;
292 END LOOP;
293
294 -- if not, add it to the list
295 IF NOT(l_line_number_found) THEN
296 l_line_number_list(l_line_number_list.COUNT + 1) := l_lines_rec.line_number;
297 IF (l_lines_rec.award_status = 'NO') THEN
298 -- Update acceptances for the lines with no award decision made
299 pon_award_pkg.update_unawarded_acceptances(
300 p_auction_header_id, -- auction header id
301 l_lines_rec.line_number, -- line number
302 p_note_to_rejected, --note to rejected suppliers
303 SYSDATE, -- award_date
304 p_user_id);
305 ELSE
306 -- Update acceptances for the lines with award decision already made
307 l_stored_note_to_rejected := null;
308 l_count := 0;
309 SELECT count(*) INTO l_count FROM pon_acceptances
310 WHERE auction_header_id = p_auction_header_id
311 AND line_number = l_lines_rec.line_number
312 AND ACCEPTANCE_TYPE = 'REJECTED';
313 --
314 IF l_count > 0 THEN
315 -- rejection note exists and carried over for rejected suppliers
316 SELECT distinct REASON INTO l_stored_note_to_rejected
317 FROM pon_acceptances
318 WHERE auction_header_id = p_auction_header_id
319 AND line_number = l_lines_rec.line_number
320 AND ACCEPTANCE_TYPE = 'REJECTED';
321
322 END IF; --End if x_count > 0
323 --
324 pon_award_pkg.update_unawarded_acceptances(
325 p_auction_header_id, -- auction header id
326 l_lines_rec.line_number, -- line number
327 l_stored_note_to_rejected, --note to rejected suppliers
328 SYSDATE, -- award_date
329 p_user_id);
330
331 END IF; -- end if award_status = 'NO'
332
333 pon_award_pkg.award_item_disposition (p_auction_header_id, l_lines_rec.line_number, 0);
334
335 END IF; -- End if x_line_number_found
336 --END IF;
337
338
339 END IF; -- End if auction_line_number = -1
340
341 END LOOP; -- End c_bid_lines_with_no_award loop.
342 CLOSE c_bid_lines_with_no_award;
343
344 -- Update bid header if there are lines with no award decision.
345 IF l_update_bid_header = 'Y' THEN
346 pon_award_pkg.update_single_bid_header(l_active_bids_rec.bid_number, p_user_id);
347
348 --If there is atleast one bid line with no award decision,
349 --then call update_auction_headers procedure
350 l_update_auction_headers := 'Y';
351 END IF;
352 ELSE
353 /* This means that no lines exist on this bid.
354 * If some offers have lines and some donot, then reject the bids with no lines.
355 * These bids might have got awarded during Award By Quote mode.
356 */
357 l_update_auction_headers := 'Y';
358 l_rejected_bid_numbers(l_rejected_bid_numbers.Count + 1) := l_active_bids_rec.bid_number;
359
360 END IF; --End if bid has lines.
361
362 END LOOP; --Active bids loop
363 CLOSE c_active_bids;
364
365 FORALL k IN 1..l_rejected_bid_numbers.COUNT
366
367 UPDATE PON_BID_HEADERS
368 SET AWARD_STATUS = 'REJECTED',
369 AWARD_DATE = SYSDATE,
370 last_update_date = SYSDATE,
371 last_updated_by = p_user_id
372 WHERE bid_number = l_rejected_bid_numbers(k);
373 -- update the award status for the auction that was bidded on
374 -- and no award decision made
375 IF l_update_auction_headers = 'Y' THEN
376 PON_AWARD_PKG.update_auction_headers(p_auction_header_id, PON_AWARD_PKG.g_AWARD_LINE, SYSDATE,
377 p_user_id, 'Y');
378 END IF;
379
380
381 END reject_unawarded_unsol_bids;
382
383 END PON_UNSOL_AWARD_PKG;
[Home] [Help]