[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_PO_PKG
Source
1 PACKAGE BODY PON_AUCTION_PO_PKG as
2 /* $Header: PONAUPOB.pls 120.8.12020000.3 2013/02/09 11:08:08 hvutukur ship $ */
3
4 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_AUCTION_PO_PKG';
6 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
7 g_module VARCHAR2(200) := 'PON.PLSQL.PON_AUCTION_PO_PKG';
8
9 PROCEDURE log_message(p_message IN VARCHAR2)
10
11 IS
12
13 BEGIN
14
15 IF (g_fnd_debug = 'Y') THEN
16 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
17 FND_LOG.string(log_level => FND_LOG.level_statement,
18 module => g_module_prefix,
19 message => p_message);
20 END IF;
21 END IF;
22
23 END;
24
25
26 PROCEDURE check_unique(org_id IN NUMBER,
27 po_number IN VARCHAR2,
28 status OUT NOCOPY VARCHAR2) IS
29
30
31 x_number_of_pos NUMBER;
32
33 BEGIN
34
35 check_unique(org_id, po_number, null, status);
36
37 END check_unique;
38
39 /*-----------------------------------------------------------------
40 * check_unique: This procedure will check for the uniquesness of
41 * the po_number in pon_bid_headers table for a given org_id,po_number
42 * and bid_number. Added p_bid_number for bug 12344470 fix
43 *----------------------------------------------------------------*/
44
45 PROCEDURE check_unique(org_id IN NUMBER,
46 po_number IN VARCHAR2,
47 p_bid_number IN NUMBER,
48 status OUT NOCOPY VARCHAR2) IS
49
50
51 x_number_of_pos NUMBER;
52
53 BEGIN
54 x_number_of_pos := 0;
55
56 SELECT count(*)
57 INTO x_number_of_pos
58 FROM pon_bid_headers pbh, pon_auction_headers_all pah
59 WHERE pbh.order_number = po_number and
60 nvl(pah.org_id, -9999) = nvl(org_id,-9999) and
61 pbh.auction_header_id = pah.auction_header_id and
62 pbh.bid_number <> nvl(p_bid_number, -1);
63
64
65 IF (x_number_of_pos = 0) THEN
66 status := 'SUCCESS';
67 ELSE
68 status := 'FAILURE';
69 END IF;
70
71 END check_unique;
72
73 PROCEDURE GET_ATTACHMENT(pk1 IN NUMBER,
74 pk2 IN NUMBER,
75 pk3 IN NUMBER,
76 attachmentType IN VARCHAR2,
77 attachmentDesc OUT NOCOPY VARCHAR2,
78 attachment OUT NOCOPY LONG,
79 error_code OUT NOCOPY VARCHAR2,
80 error_msg OUT NOCOPY VARCHAR2) IS
81
82 x_progress VARCHAR2(4000);
83
84
85 BEGIN
86 error_code := 'SUCCESS';
87 error_msg := '';
88
89
90 IF (attachmentType = 'PON_BID_ATTRIBUTES') THEN
91 PON_AUCTION_PO_PKG.GET_ATTRIBUTE_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
92 ELSIF (attachmentType = 'PON_BID_HEADER_ATTRIBUTES') THEN
93 PON_AUCTION_PO_PKG.GET_HDR_ATTRIBUTE_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
94 ELSIF (attachmentType = 'PON_BID_BUYER_NOTES') THEN
95 PON_AUCTION_PO_PKG.GET_NOTE_TO_BUYER_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
96 ELSIF (attachmentType = 'PON_AUCTION_SUPPLIER_NOTES') THEN
97 PON_AUCTION_PO_PKG.GET_NOTE_TO_SUPP_ATTACHMENT(pk1, pk3, attachmentDesc, attachment, error_code, error_msg, 'BOTH');
98 ELSIF (attachmentType = 'PON_AUC_SUPPLIER_LINE_NOTES') THEN
99 PON_AUCTION_PO_PKG.GET_NOTE_TO_SUPP_ATTACHMENT(pk1, pk3, attachmentDesc, attachment, error_code, error_msg, 'LINE');
100 ELSIF (attachmentType = 'PON_AUC_SUPPLIER_HEADER_NOTES') THEN
101 PON_AUCTION_PO_PKG.GET_NOTE_TO_SUPP_ATTACHMENT(pk1, pk3, attachmentDesc, attachment, error_code, error_msg, 'HEADER');
102 ELSIF (attachmentType = 'PON_BID_TOTAL_COST') THEN
103 PON_AUCTION_PO_PKG.GET_TOTAL_COST_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
104 ELSIF (attachmentType = 'PON_JOB_DETAILS') THEN
105 PON_AUCTION_PO_PKG.GET_JOB_DETAILS_ATTACHMENT(pk1, pk3,
106 attachmentDesc, attachment,
107 error_code, error_msg);
108 ELSIF(attachmentType = 'PON_AUC_PYMNT_SHIP_SUPP_NOTES') and (pk1 is not null) THEN
109 GET_PAYMENT_NOTE_TO_SUPP(pk1, attachmentDesc, attachment, error_code, error_msg);
110
111
112 END IF;
113
114 IF (error_code = 'FAILURE') THEN
115 x_progress := 'PON_AUCTION_PO_PKG: GET_ATTACHMENT: EXCEPTION handling pk1:' || pk1 || ' pk2: ' || pk2 || ' pk3: ' || pk3 || ' attachmentType: ' || attachmentType;
116 log_message(x_progress);
117 END IF;
118
119 EXCEPTION
120
121 when others then
122 error_code := 'FAILURE';
123 error_msg := SQLERRM;
124 x_progress := 'PON_AUCTION_PO_PKG: GET_ATTACHMENT: EXCEPTION handling pk1: ' || pk1 || ' pk2: ' || pk2 || ' pk3: ' || pk3 || ' attachmentType: ' || attachmentType;
125 log_message(x_progress);
126
127 END GET_ATTACHMENT;
128
129 PROCEDURE GET_ATTRIBUTE_ATTACHMENT(p_auction_header_id IN NUMBER,
130 p_bid_number IN NUMBER,
131 p_line_number IN NUMBER,
132 p_attachmentDesc OUT NOCOPY VARCHAR2,
133 p_attachment OUT NOCOPY LONG,
134 p_error_code OUT NOCOPY VARCHAR2,
135 p_error_msg OUT NOCOPY VARCHAR2) IS
136
137 x_item_description pon_auction_item_prices_all.item_description%TYPE;
138 x_attribute_name pon_bid_attribute_values.attribute_name%TYPE;
139 x_bidValue pon_bid_attribute_values.value%TYPE;
140 newline varchar2(256);
141 tab varchar2(256);
142 x_has_attributes_flag pon_auction_item_prices_all.has_attributes_flag%TYPE;
143 x_msg_suffix VARCHAR2(3) := '';
144 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
145 x_has_real_attr VARCHAR2(1);
146
147 CURSOR attribute_info IS
148
149 SELECT replace(pbav.attribute_name, fnd_global.local_chr(13)), pbav.value
150 FROM pon_bid_attribute_values pbav,
151 pon_auction_attributes paa
152 WHERE pbav.auction_header_id = p_auction_header_id and
153 pbav.bid_number = p_bid_number and
154 pbav.line_number = p_line_number and
155 pbav.sequence_number > 0 and
156 pbav.auction_header_id = paa.auction_header_id and
157 pbav.line_number = paa.line_number and
158 pbav.sequence_number = paa.sequence_number and
159 paa.ip_category_id is null
160 ORDER BY pbav.sequence_number;
161
162
163 BEGIN
164 p_attachment := null;
165 newline := fnd_global.newline;
166 tab := fnd_global.tab;
167 p_error_code := 'SUCCESS';
168 p_error_msg := '';
169
170 SELECT dt.doctype_group_name
171 INTO x_doctype_group_name
172 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
173 WHERE auh.auction_header_id = p_auction_header_id and
174 auh.doctype_id = dt.doctype_id;
175
176 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
177
178 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_ATTR_ATTACH_DESC', x_msg_suffix);
179
180 SELECT item_description, has_attributes_flag
181 INTO x_item_description, x_has_attributes_flag
182 FROM pon_auction_item_prices_all
183 WHERE auction_header_id = p_auction_header_id and
184 line_number = p_line_number;
185
186 IF (x_has_attributes_flag = 'N') THEN
187 p_attachment := null;
188 return;
189 END IF;
190
191
192
193 x_has_real_attr := 'N';
194 OPEN attribute_info;
195 LOOP
196 FETCH attribute_info INTO x_attribute_name, x_bidValue;
197 EXIT WHEN attribute_info%NOTFOUND;
198 x_has_real_attr := 'Y';
199 p_attachment := p_attachment || x_attribute_name || ' = ' || x_bidValue
200 || newline || newline;
201 END LOOP;
202 CLOSE attribute_info;
203
204 -- commented for bug 13840042
205
206 /*
207 IF (x_has_real_attr = 'Y') THEN
208 p_attachment := x_item_description || newline || newline || p_attachment;
209 ELSE
210 p_attachment := null;
211 END IF;
212 */
213
214 IF (x_has_real_attr = 'N') THEN
215 p_attachment := null;
216 END IF;
217
218 EXCEPTION
219 /* Unsolicited Lines Project : sql on pon_auction_item_prices_all table
220 * throws no data exception for unsolicited lines.
221 */
222 WHEN No_Data_Found THEN
223 p_error_code := 'SUCCESS';
224 p_error_msg := '';
225
226 IF (attribute_info%ISOPEN) THEN
227 close attribute_info;
228 END IF;
229 -- End Unsolicited Lines Project
230 when others then
231 p_error_code := 'FAILURE';
232 p_error_msg := SQLERRM;
233
234 IF (attribute_info%ISOPEN) THEN
235 close attribute_info;
236 END IF;
237
238 END GET_ATTRIBUTE_ATTACHMENT;
239
240
241 PROCEDURE GET_HDR_ATTRIBUTE_ATTACHMENT(p_auction_header_id IN NUMBER,
242 p_bid_number IN NUMBER,
243 p_line_number IN NUMBER,
244 p_attachmentDesc OUT NOCOPY VARCHAR2,
245 p_attachment OUT NOCOPY LONG,
246 p_error_code OUT NOCOPY VARCHAR2,
247 p_error_msg OUT NOCOPY VARCHAR2) IS
248
249 x_auction_title pon_auction_headers_all.auction_title%TYPE;
250 x_attribute_name pon_bid_attribute_values.attribute_name%TYPE;
251 x_bidValue pon_bid_attribute_values.value%TYPE;
252 x_sequenceNumber pon_bid_attribute_values.sequence_number%TYPE;
253 x_attachment_title varchar2(256);
254 x_document_number varchar2(240);
255 newline varchar2(256);
256 tab varchar2(256);
257 x_has_attributes_flag pon_auction_headers_all.has_hdr_attr_flag%TYPE;
258 x_msg_suffix VARCHAR2(3) := '';
259 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
260 x_has_real_attr VARCHAR2(1);
261
262 /* have to retrieve display only attributes separately as they are only
263 stored in pon_bid_attribute_values after scoring has happened */
264 CURSOR attribute_info IS
265
266 SELECT replace(pbav.attribute_name, fnd_global.local_chr(13)),
267 pbav.value, paa.sequence_number
268 FROM pon_bid_attribute_values pbav, pon_auction_attributes paa
269 WHERE pbav.auction_header_id = p_auction_header_id and
270 pbav.bid_number = p_bid_number and
271 pbav.line_number = -1 and
272 paa.auction_header_id = pbav.auction_header_id and
273 paa.line_number = -1 and
274 paa.sequence_number = pbav.sequence_number and
275 nvl(paa.internal_attr_flag, 'N') = 'N' and
276 nvl(paa.display_only_flag, 'N') = 'N'
277 UNION
278 SELECT replace(paa.attribute_name, fnd_global.local_chr(13)),
279 paa.value, paa.sequence_number
280 FROM pon_auction_attributes paa
281 WHERE paa.auction_header_id = p_auction_header_id and
282 paa.line_number = -1 and
283 nvl(paa.display_only_flag, 'N') = 'Y'
284 ORDER BY 3;
285
286
287 BEGIN
288 p_attachment := null;
289 newline := fnd_global.newline;
290 tab := fnd_global.tab;
291 p_error_code := 'SUCCESS';
292 p_error_msg := '';
293
294 SELECT dt.doctype_group_name
295 INTO x_doctype_group_name
296 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
297 WHERE auh.auction_header_id = p_auction_header_id and
298 auh.doctype_id = dt.doctype_id;
299
300 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
301
302 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_HDR_ATTR_ATTACH_DESC', x_msg_suffix);
303
304 SELECT auction_title, document_number, has_hdr_attr_flag
305 INTO x_auction_title, x_document_number, x_has_attributes_flag
306 FROM pon_auction_headers_all
307 WHERE auction_header_id = p_auction_header_id;
308
309 IF (x_has_attributes_flag = 'N') THEN
310 p_attachment := null;
311 return;
312 END IF;
313
314 x_has_real_attr := 'N';
315 OPEN attribute_info;
316 LOOP
317 FETCH attribute_info INTO x_attribute_name, x_bidValue, x_sequenceNumber;
318 EXIT WHEN attribute_info%NOTFOUND;
319 x_has_real_attr := 'Y';
320 p_attachment := p_attachment || x_attribute_name || ' = ' || x_bidValue
321 || newline || newline;
322 END LOOP;
323 CLOSE attribute_info;
324 x_attachment_title := PON_AUCTION_PKG.getMessage('PON_ATTR_ATTACH_TITLE',
325 x_msg_suffix,
326 'NUMBER', x_document_number,
327 'TITLE', PON_AUCTION_PKG.replaceHtmlChars(x_auction_title));
328 IF (x_has_real_attr = 'Y') THEN
329 p_attachment := x_attachment_title || newline || newline || p_attachment;
330 ELSE
331 p_attachment := null;
332 END IF;
333 EXCEPTION
334 when others then
335 p_error_code := 'FAILURE';
336 p_error_msg := SQLERRM;
337 IF (attribute_info%ISOPEN) THEN
338 close attribute_info;
339 END IF;
340 END GET_HDR_ATTRIBUTE_ATTACHMENT;
341 PROCEDURE GET_HDR_ATTRIBUTE_ATTACH_CLOB(p_auction_header_id IN NUMBER,
342 p_bid_number IN NUMBER,
343 p_line_number IN NUMBER,
344 p_attachmentDesc OUT NOCOPY VARCHAR2,
345 p_attachment OUT NOCOPY CLOB,
346 p_error_code OUT NOCOPY VARCHAR2,
347 p_error_msg OUT NOCOPY VARCHAR2) IS
348
349 x_auction_title pon_auction_headers_all.auction_title%TYPE;
350 x_attribute_name pon_bid_attribute_values.attribute_name%TYPE;
351 x_bidValue pon_bid_attribute_values.value%TYPE;
352 x_sequenceNumber pon_bid_attribute_values.sequence_number%TYPE;
353 x_attachment_title varchar2(256);
354 x_document_number varchar2(240);
355 newline varchar2(256);
356 tab varchar2(256);
357 x_has_attributes_flag pon_auction_headers_all.has_hdr_attr_flag%TYPE;
358 x_msg_suffix VARCHAR2(3) := '';
359 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
360 x_has_real_attr VARCHAR2(1);
361
362 /* have to retrieve display only attributes separately as they are only
363 stored in pon_bid_attribute_values after scoring has happened */
364 CURSOR attribute_info IS
365
366 SELECT replace(pbav.attribute_name, fnd_global.local_chr(13)),
367 pbav.value, paa.sequence_number
368 FROM pon_bid_attribute_values pbav, pon_auction_attributes paa
369 WHERE pbav.auction_header_id = p_auction_header_id and
370 pbav.bid_number = p_bid_number and
371 pbav.line_number = -1 and
372 paa.auction_header_id = pbav.auction_header_id and
373 paa.line_number = -1 and
374 paa.sequence_number = pbav.sequence_number and
375 nvl(paa.internal_attr_flag, 'N') = 'N' and
376 nvl(paa.display_only_flag, 'N') = 'N'
377 UNION
378 SELECT replace(paa.attribute_name, fnd_global.local_chr(13)),
379 paa.value, paa.sequence_number
380 FROM pon_auction_attributes paa
381 WHERE paa.auction_header_id = p_auction_header_id and
382 paa.line_number = -1 and
383 nvl(paa.display_only_flag, 'N') = 'Y'
384 ORDER BY 3;
385
386
387 BEGIN
388 p_attachment := null;
389 newline := fnd_global.newline;
390 tab := fnd_global.tab;
391 p_error_code := 'SUCCESS';
392 p_error_msg := '';
393
394 SELECT dt.doctype_group_name
395 INTO x_doctype_group_name
396 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
397 WHERE auh.auction_header_id = p_auction_header_id and
398 auh.doctype_id = dt.doctype_id;
399
400 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
401
402 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_HDR_ATTR_ATTACH_DESC', x_msg_suffix);
403
404 SELECT auction_title, document_number, has_hdr_attr_flag
405 INTO x_auction_title, x_document_number, x_has_attributes_flag
406 FROM pon_auction_headers_all
407 WHERE auction_header_id = p_auction_header_id;
408
409 IF (x_has_attributes_flag = 'N') THEN
410 p_attachment := null;
411 return;
412 END IF;
413
414 x_has_real_attr := 'N';
415 OPEN attribute_info;
416 LOOP
417 FETCH attribute_info INTO x_attribute_name, x_bidValue, x_sequenceNumber;
418 EXIT WHEN attribute_info%NOTFOUND;
419 x_has_real_attr := 'Y';
420 p_attachment := p_attachment || x_attribute_name || ' = ' || x_bidValue
421 || newline || newline;
422 END LOOP;
423 CLOSE attribute_info;
424 x_attachment_title := PON_AUCTION_PKG.getMessage('PON_ATTR_ATTACH_TITLE',
425 x_msg_suffix,
426 'NUMBER', x_document_number,
427 'TITLE', PON_AUCTION_PKG.replaceHtmlChars(x_auction_title));
428 IF (x_has_real_attr = 'Y') THEN
429 p_attachment := x_attachment_title || newline || newline || p_attachment;
430 --p_attachment := dbms_xmlgen.Convert(p_attachment);
431 ELSE
432 p_attachment := null;
433 END IF;
434 EXCEPTION
435 when others then
436 p_error_code := 'FAILURE';
437 p_error_msg := SQLERRM;
438 IF (attribute_info%ISOPEN) THEN
439 close attribute_info;
440 END IF;
441 END GET_HDR_ATTRIBUTE_ATTACH_CLOB;
442
443 PROCEDURE GET_NOTE_TO_BUYER_ATTACHMENT(p_auction_header_id IN NUMBER,
444 p_bid_number IN NUMBER,
445 p_line_number IN NUMBER,
446 p_attachmentDesc OUT NOCOPY VARCHAR2,
447 p_attachment OUT NOCOPY LONG,
448 p_error_code OUT NOCOPY VARCHAR2,
449 p_error_msg OUT NOCOPY VARCHAR2) IS
450
451 newline varchar2(256);
452 tab varchar2(256);
453 header_note pon_bid_headers.note_to_auction_owner%TYPE;
454 line_note pon_bid_item_prices.note_to_auction_owner%TYPE;
455 msgBidHeaderNote varchar2(2000);
456 msgBidLineNote varchar2(2000);
457 x_msg_suffix VARCHAR2(3) := '';
458 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
459 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
460 BEGIN
461 p_attachment := null;
462 newline := fnd_global.newline;
463 tab := fnd_global.tab;
464
465 SELECT dt.doctype_group_name, nvl(auh.contract_type,'NO_DATA_FOUND')
466 INTO x_doctype_group_name, l_contract_type
467 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
468 WHERE auh.auction_header_id = p_auction_header_id and
469 auh.doctype_id = dt.doctype_id;
470
471 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
472
473 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_NOTE_BUYER_DESC', x_msg_suffix);
474
475 msgBidHeaderNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID_HEADER_NOTE', x_msg_suffix);
476 msgBidLineNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID_LINE_NOTE', x_msg_suffix);
477 p_error_code := 'SUCCESS';
478 p_error_msg := '';
479
480 IF l_contract_type <> 'CONTRACT' THEN
481 SELECT replace(pbh.note_to_auction_owner, fnd_global.local_chr(13)), replace(pbip.note_to_auction_owner, fnd_global.local_chr(13))
482 INTO header_note, line_note
483 FROM pon_bid_headers pbh, pon_bid_item_prices pbip
484 WHERE pbh.auction_header_id = p_auction_header_id and
485 pbh.bid_number = p_bid_number and
486 pbip.bid_number = pbh.bid_number and
487 pbip.line_number = p_line_number;
488 ELSE
489 SELECT replace(pbh.note_to_auction_owner, fnd_global.local_chr(13))
490 INTO header_note
491 FROM pon_bid_headers pbh
492 WHERE pbh.auction_header_id = p_auction_header_id and
493 pbh.bid_number = p_bid_number;
494 END IF; -- if contractType <> 'CONTRACT
495
496 IF (header_note IS NOT null) THEN
497 p_attachment := msgBidHeaderNote || newline || newline || tab || header_note || newline || newline;
498 END IF;
499
500 IF (line_note IS NOT null) THEN
501 p_attachment := p_attachment || msgBidLineNote || newline || newline || tab || line_note;
502 END IF;
503
504 EXCEPTION
505
506 when others then
507 p_error_code := 'FAILURE';
508 p_error_msg := SQLERRM;
509
510 END GET_NOTE_TO_BUYER_ATTACHMENT;
511
512
513 PROCEDURE GET_NOTE_TO_SUPP_ATTACHMENT(p_auction_header_id IN NUMBER,
514 p_line_number IN NUMBER,
515 p_attachmentDesc OUT NOCOPY VARCHAR2,
516 p_attachment OUT NOCOPY LONG,
517 p_error_code OUT NOCOPY VARCHAR2,
518 p_error_msg OUT NOCOPY VARCHAR2,
519 p_line_or_header IN VARCHAR2) IS
520
521 newline varchar2(256);
522 tab varchar2(256);
523 header_note pon_auction_headers_all.note_to_bidders%TYPE;
524 line_note pon_auction_item_prices_all.note_to_bidders%TYPE;
525 msgNegHeaderNote varchar2(2000);
526 msgNegLineNote varchar2(2000);
527 x_msg_suffix VARCHAR2(3) := '';
528 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
529 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
530
531 BEGIN
532 p_attachment := null;
533 newline := fnd_global.newline;
534 tab := fnd_global.tab;
535 msgNegHeaderNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NEG_HEADER_NOTE');
536 msgNegLineNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NEG_LINE_NOTE');
537 p_error_code := 'SUCCESS';
538 p_error_msg := '';
539
540 SELECT dt.doctype_group_name, nvl(auh.contract_type,'NO_DATA_FOUND')
541 INTO x_doctype_group_name, l_contract_type
542 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
543 WHERE auh.auction_header_id = p_auction_header_id and
544 auh.doctype_id = dt.doctype_id;
545
546 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
547
548 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_NOTE_SUPP_DESC', x_msg_suffix);
549
550 IF l_contract_type <> 'CONTRACT' THEN
551 SELECT replace(pah.note_to_bidders, fnd_global.local_chr(13)), replace(paip.note_to_bidders, fnd_global.local_chr(13))
552 INTO header_note, line_note
553 FROM pon_auction_headers_all pah, pon_auction_item_prices_all paip
554 WHERE pah.auction_header_id = p_auction_header_id and
555 paip.auction_header_id = pah.auction_header_id and
556 paip.line_number = p_line_number;
557 ELSE
558
559 SELECT replace(pah.note_to_bidders, fnd_global.local_chr(13))
560 INTO header_note
561 FROM pon_auction_headers_all pah
562 WHERE pah.auction_header_id = p_auction_header_id;
563
564 END IF;
565
566 IF (p_line_or_header = 'BOTH') THEN
567 IF (header_note IS NOT null) THEN
568 p_attachment := msgNegHeaderNote || newline || newline || tab || header_note || newline || newline;
569 END IF;
570
571 IF (line_note IS NOT null) THEN
572 p_attachment := p_attachment || msgNegLineNote || newline || newline || tab || line_note;
573 END IF;
574 ELSIF (p_line_or_header = 'LINE') THEN
575 IF (line_note IS NOT null) THEN
576 p_attachment := msgNegLineNote || newline || newline || tab || line_note;
577 END IF;
578 ELSIF (p_line_or_header = 'HEADER') THEN
579 IF (header_note IS NOT null) THEN
580 p_attachment := msgNegHeaderNote || newline || newline || tab || header_note;
581 END IF;
582 END IF;
583
584 EXCEPTION
585 /* Unsolicited Lines Project : sql on pon_auction_item_prices_all table
586 * throws no data exception for unsolicited lines.
587 */
588 WHEN No_Data_Found THEN
589 p_error_code := 'SUCCESS';
590 p_error_msg := '';
591 -- End Unsolicited Lines Project
592 when others then
593 p_error_code := 'FAILURE';
594 p_error_msg := SQLERRM;
595
596
597 END GET_NOTE_TO_SUPP_ATTACHMENT;
598
599
600 PROCEDURE GET_TOTAL_COST_ATTACHMENT(p_auction_header_id IN NUMBER,
601 p_bid_number IN NUMBER,
602 p_line_number IN NUMBER,
603 p_attachmentDesc OUT NOCOPY VARCHAR2,
604 p_attachment OUT NOCOPY LONG,
605 p_error_code OUT NOCOPY VARCHAR2,
606 p_error_msg OUT NOCOPY VARCHAR2) IS
607
608 newline varchar2(256);
609 tab varchar2(256);
610 x_item_description pon_auction_item_prices_all.item_description%TYPE;
611 x_has_price_elements_flag pon_auction_item_prices_all.has_price_elements_flag%TYPE;
612 x_price_element_name pon_price_element_types_tl.name%TYPE;
613 x_bidValue pon_bid_price_elements.bid_currency_value%TYPE;
614 x_negative_cost_factor_flag pon_bid_price_elements.NEGATIVE_COST_FACTOR_FLAG%TYPE;
615 x_pricing_basis_display varchar2(2000);
616 x_msg_suffix VARCHAR2(3) := '';
617 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
618
619 CURSOR total_cost_info IS
620
621 SELECT ppet.name,
622 flv.meaning,
623 pbpe.bid_currency_value,
624 pbpe.NEGATIVE_COST_FACTOR_FLAG
625 FROM pon_bid_price_elements pbpe, pon_price_element_types_tl ppet, fnd_lookup_values flv
626 WHERE pbpe.bid_number = p_bid_number and
627 pbpe.auction_header_id = p_auction_header_id and
628 pbpe.line_number = p_line_number and
629 pbpe.price_element_type_id <> -10 and
630 pbpe.pf_type = 'SUPPLIER' and
631 pbpe.price_element_type_id = ppet.price_element_type_id and
632 ppet.language = PON_AUCTION_PKG.SessionLanguage and
633 flv.lookup_type = 'PON_PRICING_BASIS' and
634 flv.language = PON_AUCTION_PKG.SessionLanguage and
635 flv.view_application_id = 0 and
636 flv.security_group_id = 0 and
637 pbpe.pricing_basis = flv.lookup_code
638 ORDER BY sequence_number;
639
640 BEGIN
641 p_attachment := null;
642 newline := fnd_global.newline;
643 tab := fnd_global.tab;
644
645 p_error_code := 'SUCCESS';
646 p_error_msg := '';
647
648 SELECT dt.doctype_group_name
649 INTO x_doctype_group_name
650 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
651 WHERE auh.auction_header_id = p_auction_header_id and
652 auh.doctype_id = dt.doctype_id;
653
654 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
655
656 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_TOTAL_COST_DESC', x_msg_suffix);
657
658 SELECT item_description, has_price_elements_flag
659 INTO x_item_description, x_has_price_elements_flag
660 FROM pon_auction_item_prices_all
661 WHERE auction_header_id = p_auction_header_id and
662 line_number = p_line_number;
663
664 IF (x_has_price_elements_flag = 'N') THEN
665 p_attachment := null;
666 return;
667 END IF;
668
669
670 -- commented for bug 13840042
671 -- p_attachment := x_item_description || newline || newline;
672
673 OPEN total_cost_info;
674 LOOP
675 FETCH total_cost_info INTO x_price_element_name, x_pricing_basis_display, x_bidValue,x_negative_cost_factor_flag;
676 EXIT WHEN total_cost_info%NOTFOUND;
677
678 p_attachment := p_attachment || x_price_element_name || ' = ' || x_bidValue || ' (' || x_pricing_basis_display;
679 IF ( x_negative_cost_factor_flag = 'Y' ) THEN
680 p_attachment := p_attachment || ',Negative Cost Factor)' || newline || newline;
681 ELSE
682 p_attachment := p_attachment || ')' || newline || newline;
683 END IF;
684 END LOOP;
685 CLOSE total_cost_info;
686
687 EXCEPTION
688 /* Unsolicited Lines Project : sql on pon_auction_item_prices_all table
689 * throws no data exception for unsolicited lines.
690 */
691 WHEN No_Data_Found THEN
692 p_error_code := 'SUCCESS';
693 p_error_msg := '';
694
695 IF (total_cost_info%ISOPEN) THEN
696 close total_cost_info;
697 END IF;
698 -- End Unsolicited Lines Project
699 when others then
700 p_error_code := 'FAILURE';
701 p_error_msg := SQLERRM;
702
703 IF (total_cost_info%ISOPEN) THEN
704 close total_cost_info;
705 END IF;
706
707 END GET_TOTAL_COST_ATTACHMENT;
708
709
710 PROCEDURE GET_JOB_DETAILS_ATTACHMENT (p_auction_header_id IN NUMBER,
711 p_line_number IN NUMBER,
712 p_attachmentDesc OUT NOCOPY VARCHAR2,
713 p_attachment OUT NOCOPY LONG,
714 p_error_code OUT NOCOPY VARCHAR2,
715 p_error_msg OUT NOCOPY VARCHAR2) IS
716
717 newline varchar2(256);
718 tab varchar2(256);
719 job_details pon_auction_item_prices_all.additional_job_details%TYPE;
720 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
721 x_msg_suffix VARCHAR2(3) := '';
722
723 BEGIN
724 p_attachment := null;
725 newline := fnd_global.newline;
726 tab := fnd_global.tab;
727
728 SELECT dt.doctype_group_name
729 INTO x_doctype_group_name
730 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
731 WHERE auh.auction_header_id = p_auction_header_id and
732 auh.doctype_id = dt.doctype_id;
733
734 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
735
736 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_JOB_DETAILS_DESC',
737 x_msg_suffix);
738
739 p_error_code := 'SUCCESS';
740 p_error_msg := '';
741
742 SELECT replace(paip.additional_job_details, fnd_global.local_chr(13))
743 INTO job_details
744 FROM pon_auction_item_prices_all paip
745 WHERE paip.auction_header_id = p_auction_header_id and
746 paip.line_number = p_line_number;
747
748 p_attachment := job_details;
749
750 EXCEPTION
751 /* Unsolicited Lines Project : sql on pon_auction_item_prices_all table
752 * throws no data exception for unsolicited lines.
753 */
754 WHEN No_Data_Found THEN
755 p_error_code := 'SUCCESS';
756 p_error_msg := '';
757 -- End Unsolicited Lines Project
758 when others then
759 p_error_code := 'FAILURE';
760 p_error_msg := SQLERRM;
761
762 END GET_JOB_DETAILS_ATTACHMENT;
763
764 --Complex work- This method creates fnd attachments out of Buyer notes on Payments
765 -- These attachments are put on corresponding PO payments
766 PROCEDURE GET_PAYMENT_NOTE_TO_SUPP ( p_auction_payment_id IN NUMBER,
767 p_attachmentDesc OUT NOCOPY VARCHAR2,
768 p_attachment OUT NOCOPY LONG,
769 p_error_code OUT NOCOPY VARCHAR2,
770 p_error_msg OUT NOCOPY VARCHAR2)
771 IS
772
773 newline varchar2(256);
774 tab varchar2(256);
775 pymt_note pon_auc_payments_shipments.note_to_bidders%TYPE;
776 msgNegPymntNote varchar2(2000);
777
778 BEGIN
779 p_attachment := null;
780 newline := fnd_global.newline;
781 tab := fnd_global.tab;
782 msgNegPymntNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NEG_PYMNT_NOTE');
783 p_error_code := 'SUCCESS';
784 p_error_msg := '';
785
786 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_PYMNT_NOTE_SUPP_DESC');
787
788 SELECT replace(pys.note_to_bidders, fnd_global.local_chr(13))
789 INTO pymt_note
790 FROM pon_auc_payments_shipments pys
791 WHERE payment_id= p_auction_payment_id;
792
793
794 IF (pymt_note IS NOT null) THEN
795 p_attachment := msgNegPymntNote || newline || newline || tab || pymt_note;
796 END IF;
797
798 EXCEPTION
799
800 when others then
801 p_error_code := 'FAILURE';
802 p_error_msg := SQLERRM;
803
804
805 END GET_PAYMENT_NOTE_TO_SUPP;
806
807
808
809 END PON_AUCTION_PO_PKG;