[Home] [Help]
PACKAGE BODY: APPS.PON_UNSOL_UTIL_PKG
Source
1 PACKAGE BODY PON_UNSOL_UTIL_PKG AS
2 --$Header: PON_UNSOL_UTIL_PKG.plb 120.6.12020000.4 2013/02/09 07:59:24 hvutukur ship $
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_UNSOL_UTIL_PKG.';
5
6 PROCEDURE delete_line(p_auction_header_id IN NUMBER,
7 p_bid_number IN NUMBER,
8 p_line_number IN NUMBER)
9 IS
10
11 l_module_name VARCHAR2 (30);
12 l_doctype_id pon_auction_headers_all.doctype_id%TYPE;
13
14 BEGIN
15 l_module_name := 'DELETE_LINE';
16
17 SELECT doctype_id
18 INTO l_doctype_id
19 FROM pon_auction_headers_all
20 WHERE auction_header_id = p_auction_header_id;
21
22 --Delete item references.
23 DELETE FROM
24 pon_bid_item_references
25 WHERE
26 bid_number = p_bid_number AND
27 line_number = p_line_number;
28
29 IF PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id) = 1 THEN
30 DELETE FROM
31 pon_bid_item_prices_ext_b
32 WHERE
33 bid_number = p_bid_number AND
34 line_number = p_line_number;
35
36 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
37 FND_LOG.string (log_level => FND_LOG.level_statement,
38 module => g_module_prefix || l_module_name,
39 message => 'UDA deletion complete');
40 END IF;
41 END IF;
42
43 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
44 x_entity_name => 'PON_BID_ITEM_PRICES',
45 x_pk1_value => p_auction_header_id,
46 x_pk2_value => p_bid_number,
47 x_pk3_value => p_line_number);
48
49 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
50 FND_LOG.string (log_level => FND_LOG.level_statement,
51 module => g_module_prefix || l_module_name,
52 message => 'Deleted the line attachments');
53 END IF;
54
55 DELETE FROM
56 pon_bid_item_prices
57 WHERE
58 bid_number = p_bid_number AND
59 line_number = p_line_number;
60
61 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
62 FND_LOG.string (log_level => FND_LOG.level_statement,
63 module => g_module_prefix || l_module_name,
64 message => 'Deleted the line.');
65 END IF;
66
67 END delete_line;
68
69
70 PROCEDURE DELETE_SINGLE_LINE(p_auction_header_id IN NUMBER,
71 p_bid_number IN NUMBER,
72 p_line_number IN NUMBER,
73 x_result OUT NOCOPY VARCHAR2,
74 x_error_code OUT NOCOPY VARCHAR2,
75 x_error_message OUT NOCOPY VARCHAR2)
76
77 IS
78
79 l_module_name VARCHAR2 (30);
80 l_line_number pon_bid_item_prices.line_number%TYPE;
81
82 CURSOR SlinLines IS
83 SELECT
84 line_number
85 FROM pon_bid_item_prices
86 WHERE auction_header_id = p_auction_header_id
87 AND bid_number = p_bid_number
88 AND group_line_id = p_line_number;
89
90
91 BEGIN
92 l_module_name := 'DELETE_SINGLE_LINE';
93
94 x_result := 'S';
95
96 BEGIN
97
98 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
99 FND_LOG.string (log_level => FND_LOG.level_statement,
100 module => g_module_prefix || l_module_name,
101 message => 'Checking if the line still exists');
102 END IF;
103
104 --Checking if the line still exists in the database
105 --We are doing this because the user might have selected a LOT and its
106 --LOT_LINE for deletion and the LOT_LINE has already been deleted as
107 --part of the LOT deletion. Simply return with number of lines deleted
108 --set to zero
109
110 SELECT
111 line_number
112 INTO
113 l_line_number
114 FROM
115 pon_bid_item_prices
116 WHERE
117 auction_header_id = p_auction_header_id and
118 bid_number = p_bid_number and
119 line_number = p_line_number;
120
121 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
122 FND_LOG.string (log_level => FND_LOG.level_statement,
123 module => g_module_prefix || l_module_name,
124 message => 'Line exists');
125 END IF;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
129 FND_LOG.string (log_level => FND_LOG.level_statement,
130 module => g_module_prefix || l_module_name,
131 message => 'Line no longer exists');
132 END IF;
133 RETURN;
134 END;
135
136 FOR sl_line IN SlinLines LOOP
137
138 delete_line(p_auction_header_id,p_bid_number,sl_line.line_number);
139
140 END LOOP;
141
142 /* Delete Parent Line */
143 delete_line(p_auction_header_id,p_bid_number,p_line_number);
144
145 END DELETE_SINGLE_LINE;
146
147 PROCEDURE DELETE_ALL_LINES(p_auction_header_id IN NUMBER,
148 p_bid_number IN NUMBER,
149 x_result OUT NOCOPY VARCHAR2,
150 x_error_code OUT NOCOPY VARCHAR2,
151 x_error_message OUT NOCOPY VARCHAR2)
152 IS
153
154 l_module_name VARCHAR2 (30);
155 l_doctype_id pon_auction_headers_all.doctype_id%TYPE;
156
157 CURSOR all_lines IS
158 SELECT line_number
159 FROM pon_bid_item_prices
160 WHERE auction_header_id = p_auction_header_id
161 AND bid_number = p_bid_number
162 AND auction_line_number = -1;
163
164 BEGIN
165 l_module_name := 'DELETE_LINE';
166
167 x_result := 'S';
168
169 SELECT doctype_id
170 INTO l_doctype_id
171 FROM pon_auction_headers_all
172 WHERE auction_header_id = p_auction_header_id;
173
174 --Delete item references.
175 DELETE FROM
176 pon_bid_item_references
177 WHERE
178 bid_number = p_bid_number;
179
180 IF PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id) = 1 THEN
181 DELETE FROM
182 pon_bid_item_prices_ext_b ext
183 WHERE
184 ext.bid_number = p_bid_number
185 AND ext.line_number IN
186 (SELECT bid_item.line_number FROM pon_bid_item_prices bid_item
187 WHERE bid_item.bid_number = ext.bid_number AND bid_item.auction_line_number = -1);
188
189 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
190 FND_LOG.string (log_level => FND_LOG.level_statement,
191 module => g_module_prefix || l_module_name,
192 message => 'UDA deletion complete');
193 END IF;
194 END IF;
195
196 FOR line IN all_lines LOOP
197 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
198 x_entity_name => 'PON_BID_ITEM_PRICES',
199 x_pk1_value => p_auction_header_id,
200 x_pk2_value => p_bid_number,
201 x_pk3_value => line.line_number);
202
203 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
204 FND_LOG.string (log_level => FND_LOG.level_statement,
205 module => g_module_prefix || l_module_name,
206 message => 'Deleted the line attachments');
207 END IF;
208 END LOOP;
209
210 DELETE FROM
211 pon_bid_item_prices
212 WHERE
213 bid_number = p_bid_number AND
214 auction_line_number = -1;
215
216 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
217 FND_LOG.string (log_level => FND_LOG.level_statement,
218 module => g_module_prefix || l_module_name,
219 message => 'Deleted all the lines.');
220 END IF;
221
222 END DELETE_ALL_LINES;
223
224 FUNCTION HAS_SOL_LINE_REFERENCES(p_bid_number IN NUMBER,
225 p_line_number IN NUMBER) RETURN VARCHAR2
226 IS
227
228 l_has_ref VARCHAR2(1);
229 BEGIN
230 SELECT 'Y'
231 INTO l_has_ref
232 FROM dual
233 WHERE EXISTS
234 (SELECT 1 FROM pon_bid_item_references WHERE bid_number = p_bid_number AND line_number = p_line_number);
235
236 RETURN l_has_ref;
237
238 EXCEPTION
239 WHEN No_Data_Found THEN
240 RETURN 'N';
241 WHEN OTHERS THEN
242 RETURN 'N';
243
244 END HAS_SOL_LINE_REFERENCES;
245
246 FUNCTION HAS_UNSOL_LINES(p_auc_header_id IN NUMBER) RETURN VARCHAR2
247 IS
248 l_has_unsol_lines VARCHAR2(1);
249 BEGIN
250 l_has_unsol_lines := 'N';
251
252 SELECT 'Y'
253 INTO l_has_unsol_lines
254 FROM dual
255 WHERE EXISTS
256 (SELECT 1 FROM pon_bid_item_prices WHERE auction_header_id = p_auc_header_id AND auction_line_number = -1);
257
258 RETURN l_has_unsol_lines;
259
260 EXCEPTION
261 WHEN No_Data_Found THEN
262 RETURN 'N';
263 WHEN OTHERS THEN
264 RETURN 'N';
265
266 END HAS_UNSOL_LINES;
267
268 FUNCTION HAS_RESPONSE_UNSOL_LINES(p_bid_number IN NUMBER) RETURN VARCHAR2
269 IS
270 l_has_unsol_lines VARCHAR2(1);
271 BEGIN
272 l_has_unsol_lines := 'N';
273
274 SELECT 'Y'
275 INTO l_has_unsol_lines
276 FROM dual
277 WHERE EXISTS
278 (SELECT 1 FROM pon_bid_item_prices WHERE bid_number = p_bid_number AND auction_line_number = -1);
279
280 RETURN l_has_unsol_lines;
281
282 EXCEPTION
283 WHEN No_Data_Found THEN
284 RETURN 'N';
285 WHEN OTHERS THEN
286 RETURN 'N';
287
288 END HAS_RESPONSE_UNSOL_LINES;
289
290 FUNCTION ARE_UNSOL_LINES_ALLOWED(p_auc_header_id IN NUMBER) RETURN VARCHAR2
291 IS
292 l_unsol_lines_allowed VARCHAR2(1);
293 BEGIN
294 l_unsol_lines_allowed := 'N';
295
296 SELECT Nvl(ALLOW_UNSOL_OFFER_LINES,'N')
297 INTO l_unsol_lines_allowed
298 FROM pon_auction_headers_all
299 WHERE auction_header_id = p_auc_header_id;
300
301 RETURN l_unsol_lines_allowed;
302
303 EXCEPTION
304 WHEN No_Data_Found THEN
305 RETURN 'N';
306 WHEN OTHERS THEN
307 RETURN 'N';
308
309 END ARE_UNSOL_LINES_ALLOWED;
310
311 PROCEDURE INSERT_INTO_BID_ITEMS(p_auction_id IN NUMBER,
312 p_source_bid_num IN NUMBER,
313 p_bid_number IN NUMBER,
314 p_uda_template_id IN NUMBER,
315 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
316 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
317 p_userid IN pon_bid_headers.created_by%TYPE,
318 p_vensid IN pon_bid_headers.vendor_site_id%TYPE)
319 IS
320 l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
321 BEGIN
322 SELECT ah.trading_partner_id
323 INTO l_auctpid
324 FROM pon_auction_headers_all ah
325 WHERE ah.auction_header_id = p_auction_id;
326
327 INSERT INTO pon_bid_item_prices
328 (
329 AUCTION_HEADER_ID,
330 AUCTION_LINE_NUMBER,
331 BID_NUMBER,
332 LINE_NUMBER,
333 ITEM_DESCRIPTION,
334 CATEGORY_ID,
335 CATEGORY_NAME,
336 UOM,
337 QUANTITY,
338 PRICE,
339 MINIMUM_BID_PRICE,
340 PROMISED_DATE,
341 NOTE_TO_AUCTION_OWNER,
342 LANGUAGE_CODE,
343 CREATION_DATE,
344 CREATED_BY,
345 LAST_UPDATE_DATE,
346 LAST_UPDATED_BY,
347 AUCTION_CREATION_DATE,
348 SHIP_TO_LOCATION_ID,
349 PUBLISH_DATE,
350 PROXY_BID_LIMIT_PRICE,
351 PROXY_BID_LIMIT_PRICE_DATE,
352 BID_CURRENCY_PRICE,
353 BID_CURRENCY_LIMIT_PRICE,
354 PROXY_BID_FLAG,
355 FIRST_BID_PRICE,
356 UNIT_OF_MEASURE,
357 HAS_ATTRIBUTES_FLAG,
358 FREIGHT_TERMS_CODE,
359 TBD_PRICING_FLAG,
360 AUC_TRADING_PARTNER_ID,
361 BID_TRADING_PARTNER_ID,
362 TOTAL_WEIGHTED_SCORE,
363 RANK,
364 PO_MIN_REL_AMOUNT,
365 PO_BID_MIN_REL_AMOUNT,
366 PRICE_BREAK_TYPE,
367 HAS_SHIPMENTS_FLAG,
368 IS_CHANGED_LINE_FLAG,
369 HAS_PRICE_DIFFERENTIALS_FLAG,
370 PRICE_DIFF_SHIPMENT_NUMBER,
371 BID_CURRENCY_TRANS_PRICE,
372 UNIT_PRICE,
373 BID_CURRENCY_UNIT_PRICE,
374 GROUP_AMOUNT,
375 HAS_BID_PAYMENTS_FLAG,
376 ADVANCE_AMOUNT,
377 BID_CURR_ADVANCE_AMOUNT,
378 RECOUPMENT_RATE_PERCENT,
379 PROGRESS_PYMT_RATE_PERCENT,
380 RETAINAGE_RATE_PERCENT,
381 MAX_RETAINAGE_AMOUNT,
382 BID_CURR_MAX_RETAINAGE_AMT,
383 OLD_NO_OF_PAYMENTS,
384 OLD_PRICE,
385 OLD_BID_CURRENCY_UNIT_PRICE,
386 OLD_BID_CURRENCY_PRICE,
387 OLD_BID_CURRENCY_LIMIT_PRICE,
388 OLD_PO_BID_MIN_REL_AMOUNT,
389 OLD_QUANTITY,
390 OLD_PUBLISH_DATE,
391 OLD_PROMISED_DATE,
392 OLD_NOTE_TO_AUCTION_OWNER,
393 HAS_BID_FLAG,
394 /*OLD_BID_CURR_ADVANCE_AMOUNT,
395 OLD_RECOUPMENT_RATE_PERCENT,
396 OLD_PROGRESS_PYMT_RATE_PERCENT,
397 OLD_RETAINAGE_RATE_PERCENT,
398 OLD_BID_CURR_MAX_RETAINAGE_AMT,
399 COPY_PRICE_FOR_PROXY_FLAG, */
400 BID_START_PRICE,
401 HAS_QUANTITY_TIERS,
402 /***********************************************************
403 * CLM - Clin Slin project Changes Start.
404 *********************************************************/
405 --- default the CLM attributes
406 LINE_NUM_DISPLAY ,
407 GROUP_LINE_ID,
408 CLM_INFO_FLAG,
409 CLM_OPTION_INDICATOR,
410 CLM_BASE_LINE_NUM,
411 CLM_OPTION_NUM,
412 CLM_OPTION_FROM_DATE,
413 CLM_OPTION_TO_DATE,
414 CLM_FUNDED_FLAG,
415
416 /***********************************************************
417 * CLM - Clin Slin project Changes End.
418 *********************************************************/
419 /***********************************************************
420 * CLM - Complex Pricing project Changes Start.
421 *********************************************************/
422 CLM_COST_CONSTRAINT,
423 CLM_CONTRACT_TYPE,
424 CLM_IDC_TYPE,
425 UDA_TEMPLATE_ID,
426 /***********************************************************
427 * CLM - Complex Pricing project Changes End.
428 *********************************************************/
429 LINE_TYPE_ID,
430 ORDER_TYPE_LOOKUP_CODE,
431 PURCHASE_BASIS,
432 -- Event Based Delivery Project
433 CLM_DELIVERY_EVENT_CODE,
434 CLM_PROMISE_PERIOD,
435 CLM_PROMISE_PERIOD_UOM,
436 CLM_PROMISE_POP_DURATION,
437 CLM_PROMISE_POP_DURATION_UOM
438 )
439 (SELECT
440 bl.auction_header_id, -- AUCTION_HEADER_ID
441 -1, -- AUCTION_LINE_NUMBER
442 p_bid_number, -- BID_NUMBER
443 bl.line_number, -- LINE_NUMBER
444 bl.item_description, -- ITEM_DESCRIPTION
445 bl.category_id, -- CATEGORY_ID
446 bl.category_name, -- CATEGORY_NAME
447 bl.uom, -- UOM
448 bl.quantity, -- QUANTITY
449 bl.price, -- PRICE
450 null, -- MINIMUM_BID_PRICE
451 bl.promised_date, -- PROMISED_DATE
452 bl.note_to_auction_owner, -- NOTE_TO_AUCTION_OWNER
453 userenv('LANG'), -- LANGUAGE_CODE
454 SYSDATE, -- CREATION_DATE
455 p_userid, -- CREATED_BY
456 SYSDATE, -- LAST_UPDATE_DATE
457 p_userid, -- LAST_UPDATED_BY
458 null, -- AUCTION_CREATION_DATE
459 bl.ship_to_location_id, -- SHIP_TO_LOCATION_ID
460 bl.publish_date, -- PUBLISH_DATE
461 bl.proxy_bid_limit_price, -- PROXY_BID_LIMIT_PRICE
462 bl.proxy_bid_limit_price_date, -- PROXY_BID_LIMIT_PRICE_DATE
463 bl.bid_currency_price, -- BID_CURRENCY_PRICE
464 bl.bid_currency_limit_price, -- BID_CURRENCY_LIMIT_PRICE
465 'N', -- PROXY_BID_FLAG
466 bl.first_bid_price, -- FIRST_BID_PRICE
467 bl.unit_of_measure, -- UNIT_OF_MEASURE
468 bl.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
469 bl.freight_terms_code, -- FREIGHT_TERMS_CODE
470 'N', -- TBD_PRICING_FLAG
471 l_auctpid, -- AUC_TRADING_PARTNER_ID
472 p_tpid, -- BID_TRADING_PARTNER_ID
473 bl.total_weighted_score, -- TOTAL_WEIGHTED_SCORE
474 bl.rank, -- RANK
475 bl.po_min_rel_amount, -- PO_MIN_REL_AMOUNT
476 bl.po_bid_min_rel_amount, -- PO_BID_MIN_REL_AMOUNT
477 bl.price_break_type, -- PRICE_BREAK_TYPE
478 bl.has_shipments_flag, -- HAS_SHIPMENTS_FLAG
479 -- Rebid: set changed_line to N
480 --CLM QA Bug : 9835426 : NC,NSP lines, assume as always modified
481 Decode(Nvl(bl.clm_cost_constraint,'X'),'NC','Y',
482 decode(nvl(bl.clm_cost_constraint,'X'),'NSP','Y','N')),
483 /*decode(p_rebid_flag, 'Y', 'N',
484 decode(al.modified_date-old_al.modified_date, 0,
485 nvl(bl.has_bid_flag, 'N'), 'N')),*/ -- IS_CHANGED_LINE_FLAG
486 bl.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
487 bl.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
488 bl.bid_currency_trans_price, -- BID_CURRENCY_TRANS_PRICE
489 bl.unit_price, -- UNIT_PRICE
490 bl.bid_currency_unit_price, -- BID_CURRENCY_UNIT_PRICE
491 bl.group_amount, -- GROUP_AMOUNT
492 bl.has_bid_payments_flag, --HAS_BID_PAYMENTS_FLAG
493 bl.advance_amount, --ADVANCE_AMOUNT
494 bl.bid_curr_advance_amount, --BID_CURR_ADVANCE_AMOUNT
495 bl.recoupment_rate_percent, --RECOUPMENT_RATE_PERCENT
496 bl.progress_pymt_rate_percent, --PROGRESS_PYMT_RATE_PERCENT
497 bl.retainage_rate_percent, --RETAINAGE_RATE_PERCENT
498 bl.max_retainage_amount, --MAX_RETAINAGE_AMOUNT
499 bl.bid_curr_max_retainage_amt, --BID_CURR_MAX_RETAINAGE_AMT
500 /*decode(p_rebid_flag, 'Y', (select count(1) from pon_bid_payments_shipments
501 where bid_number=bl.bid_number and bid_line_number=bl.line_number)
502 ,null
503 ), --OLD_NO_OF_PAYMENTS */
504 NULL, --OLD_NO_OF_PAYMENTS
505 /*decode(p_rebid_flag, 'Y', bl.price, null), -- OLD_PRICE
506 decode(p_rebid_flag, 'Y', bl.bid_currency_unit_price, null),-- OLD_BID_CURRENCY_UNIT_PRICE
507 decode(p_rebid_flag, 'Y', bl.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
508 decode(p_rebid_flag, 'Y', bl.bid_currency_limit_price, null), -- OLD_BID_CURRENCY_LIMIT_PRICE
509 decode(p_rebid_flag, 'Y', bl.po_bid_min_rel_amount, null), -- OLD_PO_BID_MIN_REL_AMOUNT
510 decode(p_rebid_flag, 'Y', bl.quantity, null), -- OLD_QUANTITY
511 decode(p_rebid_flag, 'Y', bl.publish_Date, null), -- OLD_PUBLISH_DATE
512 decode(p_rebid_flag, 'Y', bl.publish_Date, null), -- OLD_PROMISED_DATE
513 decode(p_rebid_flag, 'Y', bl.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER */
514 bl.price,
515 bl.bid_currency_unit_price,
516 bl.bid_currency_price,
517 bl.bid_currency_limit_price,
518 bl.po_bid_min_rel_amount,
519 bl.quantity,
520 bl.publish_Date,
521 bl.publish_Date,
522 bl.note_to_auction_owner,
523 'Y', -- HAS_BID_FLAG
524 /*decode(nvl(bl.clm_cost_constraint,'X'),'NC','Y',
525 decode(nvl(bl.clm_cost_constraint,'X'),'NSP','Y',nvl(bl.has_bid_flag, 'N'))),*/
526 /*decode(al.modified_date-old_al.modified_date, 0,
527 nvl(bl.has_bid_flag, 'N'), 'N'),*/ -- HAS_BID_FLAG
528 /*decode(p_rebid_flag, 'Y', bl.bid_curr_advance_amount, null), -- OLD_BID_CURR_ADVANCE_AMOUNT
529 decode(p_rebid_flag, 'Y', bl.recoupment_rate_percent, null), -- OLD_RECOUPMENT_RATE_PERCENT
530 decode(p_rebid_flag, 'Y', bl.progress_pymt_rate_percent, null), -- OLD_PROGRESS_PYMT_RATE_PERCENT
531 decode(p_rebid_flag, 'Y', bl.retainage_rate_percent, null), -- OLD_RETAINAGE_RATE_PERCENT
532 decode(p_rebid_flag, 'Y', bl.bid_curr_max_retainage_amt, null), -- OLD_BID_CURR_MAX_RETAINAGE_AMT
533 decode(p_rebid_flag, 'Y',
534 decode(sign(bl.proxy_bid_limit_price-bl.price), -1, 'Y', 'N'), 'N'), -- COPY_PRICE_FOR_PROXY_FLAG */
535 bl.bid_start_price,
536 bl.has_quantity_tiers, -- HAS_quantity_tiers
537 /***********************************************************
538 * CLM - Clin Slin project Changes Start.
539 *********************************************************/
540 bl.LINE_NUM_DISPLAY ,
541 bl.GROUP_LINE_ID,
542 bl.CLM_INFO_FLAG,
543 bl.CLM_OPTION_INDICATOR,
544 bl.CLM_BASE_LINE_NUM,
545 bl.CLM_OPTION_NUM,
546 bl.CLM_OPTION_FROM_DATE,
547 bl.CLM_OPTION_TO_DATE,
548 bl.CLM_FUNDED_FLAG,
549
550 /***********************************************************
551 * CLM - Clin Slin project Changes End.
552 *********************************************************/
553 /***********************************************************
554 * CLM - Complex Pricing project Changes Start.
555 *********************************************************/
556 bl.CLM_COST_CONSTRAINT,
557 bl.CLM_CONTRACT_TYPE,
558 bl.CLM_IDC_TYPE,
559 -- Decode(al.CLM_CONTRACT_TYPE, NULL, NULL, l_uda_template_id)
560 p_uda_template_id,
561 /***********************************************************
562 * CLM - Complex Pricing project Changes End.
563 *********************************************************/
564 bl.LINE_TYPE_ID,
565 bl.ORDER_TYPE_LOOKUP_CODE,
566 bl.PURCHASE_BASIS,
567 -- Event Based Delivery Project
568 bl.CLM_DELIVERY_EVENT_CODE,
569 bl.CLM_PROMISE_PERIOD,
570 bl.CLM_PROMISE_PERIOD_UOM,
571 bl.CLM_PROMISE_POP_DURATION,
572 bl.CLM_PROMISE_POP_DURATION_UOM
573 FROM pon_bid_item_prices bl
574 WHERE bl.bid_number = p_source_bid_num
575 AND bl.auction_line_number = -1);
576
577 END INSERT_INTO_BID_ITEMS;
578
579 PROCEDURE COPY_ITEM_REFERENCES(p_source_bid_num IN NUMBER,
580 p_bid_number IN NUMBER,
581 p_user_id IN NUMBER)
582 IS
583
584 BEGIN
585 INSERT INTO pon_bid_item_references
586 (BID_NUMBER,
587 LINE_NUMBER ,
588 AUCTION_HEADER_ID,
589 AUCTION_LINE_NUMBER,
590 LINK_TYPE,
591 LINK_DONE_BY,
592 CREATION_DATE,
593 CREATED_BY,
594 LAST_UPDATE_DATE,
595 LAST_UPDATED_BY,
596 LAST_UPDATE_LOGIN)
597 (SELECT
598 p_bid_number,
599 reference.line_number,
600 reference.auction_header_id,
601 reference.auction_line_number,
602 reference.link_type,
603 'BOTH',
604 SYSDATE,
605 p_user_id,
606 SYSDATE,
607 p_user_id,
608 p_user_id
609 FROM
610 pon_bid_item_references reference
611 WHERE reference.bid_number = p_source_bid_num);
612
613 END COPY_ITEM_REFERENCES;
614
615 PROCEDURE COPY_UNSOL_LINE_UDA(p_auction_header_id IN NUMBER,
616 p_bid_number IN NUMBER,
617 p_source_bid_num IN NUMBER,
618 p_bid_template_id IN NUMBER,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_msg_count OUT NOCOPY NUMBER,
621 x_msg_data OUT NOCOPY VARCHAR2)
622 IS
623
624 CURSOR unsol_lines IS
625 SELECT line_number,uda_template_id
626 FROM pon_bid_item_prices
627 WHERE bid_number=p_source_bid_num
628 AND auction_line_number = -1;
629
630 BEGIN
631
632 for line in unsol_lines loop
633
634 /*insert into vhk_debug_msg values('Calling COPY_BID_LINE_UDA');*/
635
636 pon_copy_udas_grp.COPY_BID_LINE_UDA(p_auction_header_id => p_auction_header_id,
637 p_bid_number => p_bid_number,
638 p_source_bid_number => p_source_bid_num,
639 p_line_number => line.line_number,
640 p_auction_template_id => line.uda_template_id,
641 p_bid_template_id => p_bid_template_id,
642 p_copyfrom => 'B',
643 x_return_status => x_return_status,
644 x_msg_count => x_msg_count,
645 x_msg_data => x_msg_data);
646
647 /*insert into vhk_debug_msg values('line_number :'||line.line_number
648 ||' uda_template_id : '||line.uda_template_id
649 ||' bid_uda_template_id : '||p_bid_template_id
650 ||' copyfrom : '||line.copyfrom
651 ||' x_return_status : '||x_return_status);*/
652 end loop;
653
654 END COPY_UNSOL_LINE_UDA;
655
656 PROCEDURE copy_unsol_line_attachments
657 (
658 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
659 p_bid_number IN pon_bid_headers.bid_number%TYPE,
660 p_source_header_id IN pon_bid_headers.auction_header_id%TYPE,
661 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
662 p_userid IN pon_bid_headers.created_by%TYPE
663 ) IS
664
665 CURSOR unsol_lines_with_attachments IS
666 SELECT DISTINCT ad.pk3_value
667 FROM fnd_attached_documents ad, pon_bid_item_prices bl
668 WHERE ad.entity_name = 'PON_BID_ITEM_PRICES'
669 AND ad.pk1_value = p_source_header_id
670 AND ad.pk2_value = p_source_bid_num
671 AND ad.pk3_value IS NOT null
672 AND bl.bid_number = p_bid_number
673 AND bl.line_number = to_number(ad.pk3_value)
674 AND bl.auction_line_number = -1;
675
676 BEGIN
677 FOR line IN unsol_lines_with_attachments LOOP
678 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
679 (x_from_entity_name => 'PON_BID_ITEM_PRICES',
680 x_from_pk1_value => p_source_header_id,
681 x_from_pk2_value => p_source_bid_num,
682 x_from_pk3_value => line.pk3_value,
683 x_to_entity_name => 'PON_BID_ITEM_PRICES',
684 x_to_pk1_value => p_auc_header_id,
685 x_to_pk2_value => p_bid_number,
686 x_to_pk3_value => line.pk3_value,
687 x_created_by => p_userid,
688 x_last_update_login => fnd_global.login_id);
689 END LOOP;
690
691 END copy_unsol_line_attachments;
692
693 PROCEDURE COPY_UNSOL_OFFER_LINES(p_auction_id IN NUMBER,
694 p_source_bid_num IN NUMBER,
695 p_bid_number IN NUMBER,
696 p_uda_template_id IN NUMBER,
697 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
698 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
699 p_userid IN pon_bid_headers.created_by%TYPE,
700 p_vensid IN pon_bid_headers.vendor_site_id%TYPE)
701 IS
702
703 l_has_unsol_lines VARCHAR2(1);
704 l_return_status VARCHAR2(1);
705 l_msg_count NUMBER;
706 l_msg_data VARCHAR2(32767);
707 BEGIN
708 l_has_unsol_lines := PON_UNSOL_UTIL_PKG.HAS_RESPONSE_UNSOL_LINES(p_source_bid_num);
709
710 IF l_has_unsol_lines = 'Y' THEN
711 --call INSERT_INTO_BID_ITEMS.
712 INSERT_INTO_BID_ITEMS(p_auction_id,
713 p_source_bid_num,
714 p_bid_number,
715 p_uda_template_id,
716 p_tpid,
717 p_tpcid,
718 p_userid,
719 p_vensid);
720
721 --Copy item references
722 COPY_ITEM_REFERENCES(p_source_bid_num,p_bid_number, p_userid);
723
724 --Copy UDA's
725 COPY_UNSOL_LINE_UDA(p_auction_header_id => p_auction_id,
726 p_bid_number => p_bid_number,
727 p_source_bid_num => p_source_bid_num,
728 p_bid_template_id => p_uda_template_id,
729 x_return_status => l_return_status,
730 x_msg_count => l_msg_count,
731 x_msg_data => l_msg_data);
732
733 -- Copy Attachments
734 copy_unsol_line_attachments(p_auc_header_id => p_auction_id,
735 p_bid_number => p_bid_number,
736 p_source_header_id => p_auction_id,
737 p_source_bid_num => p_source_bid_num,
738 p_userid => p_userid);
739
740
741 END IF;
742
743 END COPY_UNSOL_OFFER_LINES;
744
745 PROCEDURE DUPLICATE_UNSOL_AWARDED_LINES(p_auction_id IN NUMBER)
746
747 IS
748
749 l_has_buyer_data NUMBER ;
750 l_has_supplier_data NUMBER;
751
752 l_user_id NUMBER;
753 l_login_id NUMBER;
754
755 BEGIN
756
757 l_has_buyer_data := 0;
758 l_has_supplier_data := 0;
759
760
761 BEGIN
762 SELECT 1
763 INTO l_has_supplier_data
764 FROM pon_bid_item_references
765 WHERE
766 AUCTION_HEADER_ID = p_auction_id
767 AND LINK_DONE_BY = 'SUPPLIER'
768 AND ROWNUM = 1;
769 EXCEPTION WHEN No_Data_Found THEN
770 l_has_supplier_data := 0;
771 END;
772
773
774 IF(l_has_supplier_data = 1)
775 THEN
776 l_has_buyer_data := 1;
777 ELSE
778 l_has_buyer_data := 0;
779 END IF;
780
781
782 --RETURN if the buyer data is already present
783 IF(l_has_buyer_data <> 1) THEN
784 BEGIN
785
786 --Mark all unsol awarded lines as supplier as link_done_by
787 UPDATE pon_bid_item_references
788 SET LINK_DONE_BY = 'SUPPLIER'
789 WHERE AUCTION_HEADER_ID = p_auction_id
790 AND LINE_NUMBER IN
791 (SELECT LINE_NUMBER FROM pon_bid_item_prices
792 WHERE AUCTION_HEADER_ID = p_auction_id
793 AND AUCTION_LINE_NUMBER = -1)
794 --Fix for bug 13643475
795 --Need to mark all the unsolicited lines linked as Supplier
796 --AND AWARD_STATUS = 'AWARDED')
797
798 ;
799
800 l_user_id := fnd_global.user_id;
801 l_login_id := fnd_global.login_id;
802
803 --Create duplicate records of unsol awarded lines with buyer as link_done_by
804 INSERT INTO pon_bid_item_references (BID_NUMBER,
805 LINE_NUMBER,
806 AUCTION_HEADER_ID,
807 AUCTION_LINE_NUMBER,
808 LINK_TYPE,
809 LINK_DONE_BY,
810 CREATION_DATE,
811 CREATED_BY,
812 LAST_UPDATE_DATE,
813 LAST_UPDATED_BY,
814 LAST_UPDATE_LOGIN)
815
816 (SELECT BID_NUMBER,
817 LINE_NUMBER,
818 AUCTION_HEADER_ID,
819 AUCTION_LINE_NUMBER,
820 LINK_TYPE,
821 'BOTH',
822 sysdate,
823 CREATED_BY,
824 sysdate,
825 l_user_id,
826 l_login_id
827 FROM pon_bid_item_references
828 WHERE AUCTION_HEADER_ID = p_auction_id
829 AND LINE_NUMBER IN
830 (SELECT LINE_NUMBER FROM pon_bid_item_prices
831 WHERE AUCTION_HEADER_ID = p_auction_id
832 AND AUCTION_LINE_NUMBER = -1
833 AND AWARD_STATUS = 'AWARDED'));
834 --Fix for bug 13643475
835 --Need to mark all the awarded unsolicited lines linked as Both
836 --AND LINK_DONE_BY = 'SUPPLIER');
837
838 END;
839 END IF;
840
841 END DUPLICATE_UNSOL_AWARDED_LINES;
842
843 --Added for bug 13565622
844 --Need to check if the offer has only unsol info lines and throw error
845 FUNCTION HAS_PRICED_UNSOL_RESP_LINES(p_bid_number IN NUMBER) RETURN VARCHAR2
846 IS
847 l_has_unsol_lines VARCHAR2(1);
848 BEGIN
849 l_has_unsol_lines := 'N';
850
851 SELECT 'Y'
852 INTO l_has_unsol_lines
853 FROM dual
854 WHERE EXISTS
855 (SELECT 1 FROM pon_bid_item_prices
856 WHERE bid_number = p_bid_number
857 AND auction_line_number = -1
858 AND Nvl(CLM_INFO_FLAG, 'N') = 'N'
859 AND Nvl(clm_cost_constraint, 'X') NOT IN ('NSP', 'NC'));
860
861 RETURN l_has_unsol_lines;
862
863 EXCEPTION
864 WHEN No_Data_Found THEN
865 RETURN 'N';
866 WHEN OTHERS THEN
867 RETURN 'N';
868
869 END HAS_PRICED_UNSOL_RESP_LINES;
870
871 FUNCTION CAN_PUT_REQ_IN_POOL(p_auction_header_id IN NUMBER,
872 p_line_number IN NUMBER,
873 p_is_line_type_enabled IN VARCHAR2) RETURN VARCHAR2
874 IS
875
876 l_has_awarded_ref VARCHAR2(1);
877
878 BEGIN
879 l_has_awarded_ref := 'N';
880
881 SELECT 'Y'
882 INTO l_has_awarded_ref
883 FROM dual
884 WHERE EXISTS
885 (SELECT 1
886 FROM pon_bid_item_prices pbip,
887 pon_bid_item_references pbir
888 WHERE pbip.auction_header_id = p_auction_header_id
889 AND pbip.auction_line_number = -1
890 AND Nvl(pbip.award_status,'NO') = 'AWARDED'
891 AND pbir.auction_header_id = pbip.auction_header_id
892 AND pbir.line_number = pbip.line_number
893 AND pbir.auction_line_number = p_line_number
894 AND pbir.link_done_by = 'BOTH');
895
896 IF p_is_line_type_enabled = 'Y' AND l_has_awarded_ref = 'Y' THEN
897 RETURN 'N';
898 ELSE
899 RETURN 'Y';
900 END IF;
901
902
903 EXCEPTION
904 WHEN No_Data_Found THEN
905 RETURN 'N';
906 WHEN OTHERS THEN
907 RETURN 'N';
908
909 END CAN_PUT_REQ_IN_POOL;
910
911 END PON_UNSOL_UTIL_PKG;