[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_PO_PKG
Source
1 PACKAGE BODY PON_AUCTION_PO_PKG as
2 /* $Header: PONAUPOB.pls 120.6 2006/03/21 23:33:33 ukottama noship $ */
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 x_number_of_pos := 0;
36
37 SELECT count(*)
38 INTO x_number_of_pos
39 FROM pon_bid_headers pbh, pon_auction_headers_all pah
40 WHERE pbh.order_number = po_number and
41 nvl(pah.org_id, -9999) = nvl(org_id,-9999) and
42 pbh.auction_header_id = pah.auction_header_id;
43
44
45 IF (x_number_of_pos = 0) THEN
46 status := 'SUCCESS';
47 ELSE
48 status := 'FAILURE';
49 END IF;
50
51 END check_unique;
52
53 PROCEDURE GET_ATTACHMENT(pk1 IN NUMBER,
54 pk2 IN NUMBER,
55 pk3 IN NUMBER,
56 attachmentType IN VARCHAR2,
57 attachmentDesc OUT NOCOPY VARCHAR2,
58 attachment OUT NOCOPY LONG,
59 error_code OUT NOCOPY VARCHAR2,
60 error_msg OUT NOCOPY VARCHAR2) IS
61
62 x_progress VARCHAR2(4000);
63
64
65 BEGIN
66 error_code := 'SUCCESS';
67 error_msg := '';
68
69
70 IF (attachmentType = 'PON_BID_ATTRIBUTES') THEN
71 PON_AUCTION_PO_PKG.GET_ATTRIBUTE_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
72 ELSIF (attachmentType = 'PON_BID_HEADER_ATTRIBUTES') THEN
73 PON_AUCTION_PO_PKG.GET_HDR_ATTRIBUTE_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
74 ELSIF (attachmentType = 'PON_BID_BUYER_NOTES') THEN
75 PON_AUCTION_PO_PKG.GET_NOTE_TO_BUYER_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
76 ELSIF (attachmentType = 'PON_AUCTION_SUPPLIER_NOTES') THEN
77 PON_AUCTION_PO_PKG.GET_NOTE_TO_SUPP_ATTACHMENT(pk1, pk3, attachmentDesc, attachment, error_code, error_msg, 'BOTH');
78 ELSIF (attachmentType = 'PON_AUC_SUPPLIER_LINE_NOTES') THEN
79 PON_AUCTION_PO_PKG.GET_NOTE_TO_SUPP_ATTACHMENT(pk1, pk3, attachmentDesc, attachment, error_code, error_msg, 'LINE');
80 ELSIF (attachmentType = 'PON_AUC_SUPPLIER_HEADER_NOTES') THEN
81 PON_AUCTION_PO_PKG.GET_NOTE_TO_SUPP_ATTACHMENT(pk1, pk3, attachmentDesc, attachment, error_code, error_msg, 'HEADER');
82 ELSIF (attachmentType = 'PON_BID_TOTAL_COST') THEN
83 PON_AUCTION_PO_PKG.GET_TOTAL_COST_ATTACHMENT(pk1, pk2, pk3, attachmentDesc, attachment, error_code, error_msg);
84 ELSIF (attachmentType = 'PON_JOB_DETAILS') THEN
85 PON_AUCTION_PO_PKG.GET_JOB_DETAILS_ATTACHMENT(pk1, pk3,
86 attachmentDesc, attachment,
87 error_code, error_msg);
88 ELSIF(attachmentType = 'PON_AUC_PYMNT_SHIP_SUPP_NOTES') and (pk1 is not null) THEN
89 GET_PAYMENT_NOTE_TO_SUPP(pk1, attachmentDesc, attachment, error_code, error_msg);
90
91
92 END IF;
93
94 IF (error_code = 'FAILURE') THEN
95 x_progress := 'PON_AUCTION_PO_PKG: GET_ATTACHMENT: EXCEPTION handling pk1:' || pk1 || ' pk2: ' || pk2 || ' pk3: ' || pk3 || ' attachmentType: ' || attachmentType;
96 log_message(x_progress);
97 END IF;
98
99 EXCEPTION
100
101 when others then
102 error_code := 'FAILURE';
103 error_msg := SQLERRM;
104 x_progress := 'PON_AUCTION_PO_PKG: GET_ATTACHMENT: EXCEPTION handling pk1: ' || pk1 || ' pk2: ' || pk2 || ' pk3: ' || pk3 || ' attachmentType: ' || attachmentType;
105 log_message(x_progress);
106
107 END GET_ATTACHMENT;
108
109 PROCEDURE GET_ATTRIBUTE_ATTACHMENT(p_auction_header_id IN NUMBER,
110 p_bid_number IN NUMBER,
111 p_line_number IN NUMBER,
112 p_attachmentDesc OUT NOCOPY VARCHAR2,
113 p_attachment OUT NOCOPY LONG,
114 p_error_code OUT NOCOPY VARCHAR2,
115 p_error_msg OUT NOCOPY VARCHAR2) IS
116
117 x_item_description pon_auction_item_prices_all.item_description%TYPE;
118 x_attribute_name pon_bid_attribute_values.attribute_name%TYPE;
119 x_bidValue pon_bid_attribute_values.value%TYPE;
120 newline varchar2(256);
121 tab varchar2(256);
122 x_has_attributes_flag pon_auction_item_prices_all.has_attributes_flag%TYPE;
123 x_msg_suffix VARCHAR2(3) := '';
124 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
125 x_has_real_attr VARCHAR2(1);
126
127 CURSOR attribute_info IS
128
129 SELECT replace(pbav.attribute_name, fnd_global.local_chr(13)), pbav.value
130 FROM pon_bid_attribute_values pbav,
131 pon_auction_attributes paa
132 WHERE pbav.auction_header_id = p_auction_header_id and
133 pbav.bid_number = p_bid_number and
134 pbav.line_number = p_line_number and
135 pbav.sequence_number > 0 and
136 pbav.auction_header_id = paa.auction_header_id and
137 pbav.line_number = paa.line_number and
138 pbav.sequence_number = paa.sequence_number and
139 paa.ip_category_id is null
140 ORDER BY pbav.sequence_number;
141
142
143 BEGIN
144 p_attachment := null;
145 newline := fnd_global.newline;
146 tab := fnd_global.tab;
147 p_error_code := 'SUCCESS';
148 p_error_msg := '';
149
150 SELECT dt.doctype_group_name
151 INTO x_doctype_group_name
152 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
153 WHERE auh.auction_header_id = p_auction_header_id and
154 auh.doctype_id = dt.doctype_id;
155
156 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
157
158 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_ATTR_ATTACH_DESC', x_msg_suffix);
159
160 SELECT item_description, has_attributes_flag
161 INTO x_item_description, x_has_attributes_flag
162 FROM pon_auction_item_prices_all
163 WHERE auction_header_id = p_auction_header_id and
164 line_number = p_line_number;
165
166 IF (x_has_attributes_flag = 'N') THEN
167 p_attachment := null;
168 return;
169 END IF;
170
171
172
173 x_has_real_attr := 'N';
174 OPEN attribute_info;
175 LOOP
176 FETCH attribute_info INTO x_attribute_name, x_bidValue;
177 EXIT WHEN attribute_info%NOTFOUND;
178 x_has_real_attr := 'Y';
179 p_attachment := p_attachment || x_attribute_name || ' = ' || x_bidValue
180 || newline || newline;
181 END LOOP;
182 CLOSE attribute_info;
183
184 IF (x_has_real_attr = 'Y') THEN
185 p_attachment := x_item_description || newline || newline || p_attachment;
186 ELSE
187 p_attachment := null;
188 END IF;
189
190 EXCEPTION
191
192 when others then
193 p_error_code := 'FAILURE';
194 p_error_msg := SQLERRM;
195
196 IF (attribute_info%ISOPEN) THEN
197 close attribute_info;
198 END IF;
199
200 END GET_ATTRIBUTE_ATTACHMENT;
201
202
203 PROCEDURE GET_HDR_ATTRIBUTE_ATTACHMENT(p_auction_header_id IN NUMBER,
204 p_bid_number IN NUMBER,
205 p_line_number IN NUMBER,
206 p_attachmentDesc OUT NOCOPY VARCHAR2,
207 p_attachment OUT NOCOPY LONG,
208 p_error_code OUT NOCOPY VARCHAR2,
209 p_error_msg OUT NOCOPY VARCHAR2) IS
210
211 x_auction_title pon_auction_headers_all.auction_title%TYPE;
212 x_attribute_name pon_bid_attribute_values.attribute_name%TYPE;
213 x_bidValue pon_bid_attribute_values.value%TYPE;
214 x_sequenceNumber pon_bid_attribute_values.sequence_number%TYPE;
215 x_attachment_title varchar2(256);
216 x_document_number varchar2(240);
217 newline varchar2(256);
218 tab varchar2(256);
219 x_has_attributes_flag pon_auction_headers_all.has_hdr_attr_flag%TYPE;
220 x_msg_suffix VARCHAR2(3) := '';
221 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
222 x_has_real_attr VARCHAR2(1);
223
224 /* have to retrieve display only attributes separately as they are only
225 stored in pon_bid_attribute_values after scoring has happened */
226 CURSOR attribute_info IS
227
228 SELECT replace(pbav.attribute_name, fnd_global.local_chr(13)),
229 pbav.value, paa.sequence_number
230 FROM pon_bid_attribute_values pbav, pon_auction_attributes paa
231 WHERE pbav.auction_header_id = p_auction_header_id and
232 pbav.bid_number = p_bid_number and
233 pbav.line_number = -1 and
234 paa.auction_header_id = pbav.auction_header_id and
235 paa.line_number = -1 and
236 paa.sequence_number = pbav.sequence_number and
237 nvl(paa.internal_attr_flag, 'N') = 'N' and
238 nvl(paa.display_only_flag, 'N') = 'N'
239 UNION
240 SELECT replace(paa.attribute_name, fnd_global.local_chr(13)),
241 paa.value, paa.sequence_number
242 FROM pon_auction_attributes paa
243 WHERE paa.auction_header_id = p_auction_header_id and
244 paa.line_number = -1 and
245 nvl(paa.display_only_flag, 'N') = 'Y'
246 ORDER BY 3;
247
248
249 BEGIN
250 p_attachment := null;
251 newline := fnd_global.newline;
252 tab := fnd_global.tab;
253 p_error_code := 'SUCCESS';
254 p_error_msg := '';
255
256 SELECT dt.doctype_group_name
257 INTO x_doctype_group_name
258 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
259 WHERE auh.auction_header_id = p_auction_header_id and
260 auh.doctype_id = dt.doctype_id;
261
262 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
263
264 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_HDR_ATTR_ATTACH_DESC', x_msg_suffix);
265
266 SELECT auction_title, document_number, has_hdr_attr_flag
267 INTO x_auction_title, x_document_number, x_has_attributes_flag
268 FROM pon_auction_headers_all
269 WHERE auction_header_id = p_auction_header_id;
270
271 IF (x_has_attributes_flag = 'N') THEN
272 p_attachment := null;
273 return;
274 END IF;
275
276 x_has_real_attr := 'N';
277 OPEN attribute_info;
278 LOOP
279 FETCH attribute_info INTO x_attribute_name, x_bidValue, x_sequenceNumber;
280 EXIT WHEN attribute_info%NOTFOUND;
281 x_has_real_attr := 'Y';
282 p_attachment := p_attachment || x_attribute_name || ' = ' || x_bidValue
283 || newline || newline;
284 END LOOP;
285 CLOSE attribute_info;
286
287 x_attachment_title := PON_AUCTION_PKG.getMessage('PON_ATTR_ATTACH_TITLE',
288 x_msg_suffix,
289 'NUMBER', x_document_number,
290 'TITLE', PON_AUCTION_PKG.replaceHtmlChars(x_auction_title));
291
292 IF (x_has_real_attr = 'Y') THEN
293 p_attachment := x_attachment_title || newline || newline || p_attachment;
294 ELSE
295 p_attachment := null;
296 END IF;
297
298 EXCEPTION
299
300 when others then
301 p_error_code := 'FAILURE';
302 p_error_msg := SQLERRM;
303
304 IF (attribute_info%ISOPEN) THEN
305 close attribute_info;
306 END IF;
307
308 END GET_HDR_ATTRIBUTE_ATTACHMENT;
309
310
311 PROCEDURE GET_NOTE_TO_BUYER_ATTACHMENT(p_auction_header_id IN NUMBER,
312 p_bid_number IN NUMBER,
313 p_line_number IN NUMBER,
314 p_attachmentDesc OUT NOCOPY VARCHAR2,
315 p_attachment OUT NOCOPY LONG,
316 p_error_code OUT NOCOPY VARCHAR2,
317 p_error_msg OUT NOCOPY VARCHAR2) IS
318
319 newline varchar2(256);
320 tab varchar2(256);
321 header_note pon_bid_headers.note_to_auction_owner%TYPE;
322 line_note pon_bid_item_prices.note_to_auction_owner%TYPE;
323 msgBidHeaderNote varchar2(2000);
324 msgBidLineNote varchar2(2000);
325 x_msg_suffix VARCHAR2(3) := '';
326 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
327 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
328 BEGIN
329 p_attachment := null;
330 newline := fnd_global.newline;
331 tab := fnd_global.tab;
332
333 SELECT dt.doctype_group_name, nvl(auh.contract_type,'NO_DATA_FOUND')
334 INTO x_doctype_group_name, l_contract_type
335 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
336 WHERE auh.auction_header_id = p_auction_header_id and
337 auh.doctype_id = dt.doctype_id;
338
339 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
340
341 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_NOTE_BUYER_DESC', x_msg_suffix);
342
343 msgBidHeaderNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID_HEADER_NOTE', x_msg_suffix);
344 msgBidLineNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_BID_LINE_NOTE', x_msg_suffix);
345 p_error_code := 'SUCCESS';
346 p_error_msg := '';
347
348 IF l_contract_type <> 'CONTRACT' THEN
349 SELECT replace(pbh.note_to_auction_owner, fnd_global.local_chr(13)), replace(pbip.note_to_auction_owner, fnd_global.local_chr(13))
350 INTO header_note, line_note
351 FROM pon_bid_headers pbh, pon_bid_item_prices pbip
352 WHERE pbh.auction_header_id = p_auction_header_id and
353 pbh.bid_number = p_bid_number and
354 pbip.bid_number = pbh.bid_number and
355 pbip.line_number = p_line_number;
356 ELSE
357 SELECT replace(pbh.note_to_auction_owner, fnd_global.local_chr(13))
358 INTO header_note
359 FROM pon_bid_headers pbh
360 WHERE pbh.auction_header_id = p_auction_header_id and
361 pbh.bid_number = p_bid_number;
362 END IF; -- if contractType <> 'CONTRACT
363
364 IF (header_note IS NOT null) THEN
365 p_attachment := msgBidHeaderNote || newline || newline || tab || header_note || newline || newline;
366 END IF;
367
368 IF (line_note IS NOT null) THEN
369 p_attachment := p_attachment || msgBidLineNote || newline || newline || tab || line_note;
370 END IF;
371
372 EXCEPTION
373
374 when others then
375 p_error_code := 'FAILURE';
376 p_error_msg := SQLERRM;
377
378 END GET_NOTE_TO_BUYER_ATTACHMENT;
379
380
384 p_attachment OUT NOCOPY LONG,
381 PROCEDURE GET_NOTE_TO_SUPP_ATTACHMENT(p_auction_header_id IN NUMBER,
382 p_line_number IN NUMBER,
383 p_attachmentDesc OUT NOCOPY VARCHAR2,
385 p_error_code OUT NOCOPY VARCHAR2,
386 p_error_msg OUT NOCOPY VARCHAR2,
387 p_line_or_header IN VARCHAR2) IS
388
389 newline varchar2(256);
390 tab varchar2(256);
391 header_note pon_auction_headers_all.note_to_bidders%TYPE;
392 line_note pon_auction_item_prices_all.note_to_bidders%TYPE;
393 msgNegHeaderNote varchar2(2000);
394 msgNegLineNote varchar2(2000);
395 x_msg_suffix VARCHAR2(3) := '';
396 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
397 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
398
399 BEGIN
400 p_attachment := null;
401 newline := fnd_global.newline;
402 tab := fnd_global.tab;
403 msgNegHeaderNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NEG_HEADER_NOTE');
404 msgNegLineNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NEG_LINE_NOTE');
405 p_error_code := 'SUCCESS';
406 p_error_msg := '';
407
408 SELECT dt.doctype_group_name, nvl(auh.contract_type,'NO_DATA_FOUND')
409 INTO x_doctype_group_name, l_contract_type
410 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
411 WHERE auh.auction_header_id = p_auction_header_id and
412 auh.doctype_id = dt.doctype_id;
413
414 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
415
416 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_NOTE_SUPP_DESC', x_msg_suffix);
417
418 IF l_contract_type <> 'CONTRACT' THEN
419 SELECT replace(pah.note_to_bidders, fnd_global.local_chr(13)), replace(paip.note_to_bidders, fnd_global.local_chr(13))
420 INTO header_note, line_note
421 FROM pon_auction_headers_all pah, pon_auction_item_prices_all paip
422 WHERE pah.auction_header_id = p_auction_header_id and
423 paip.auction_header_id = pah.auction_header_id and
424 paip.line_number = p_line_number;
425 ELSE
426
427 SELECT replace(pah.note_to_bidders, fnd_global.local_chr(13))
428 INTO header_note
429 FROM pon_auction_headers_all pah
430 WHERE pah.auction_header_id = p_auction_header_id;
431
432 END IF;
433
434 IF (p_line_or_header = 'BOTH') THEN
435 IF (header_note IS NOT null) THEN
436 p_attachment := msgNegHeaderNote || newline || newline || tab || header_note || newline || newline;
437 END IF;
438
439 IF (line_note IS NOT null) THEN
440 p_attachment := p_attachment || msgNegLineNote || newline || newline || tab || line_note;
441 END IF;
442 ELSIF (p_line_or_header = 'LINE') THEN
443 IF (line_note IS NOT null) THEN
444 p_attachment := msgNegLineNote || newline || newline || tab || line_note;
445 END IF;
446 ELSIF (p_line_or_header = 'HEADER') THEN
447 IF (header_note IS NOT null) THEN
448 p_attachment := msgNegHeaderNote || newline || newline || tab || header_note;
449 END IF;
450 END IF;
451
452 EXCEPTION
453
454 when others then
455 p_error_code := 'FAILURE';
456 p_error_msg := SQLERRM;
457
458
459 END GET_NOTE_TO_SUPP_ATTACHMENT;
460
461
462 PROCEDURE GET_TOTAL_COST_ATTACHMENT(p_auction_header_id IN NUMBER,
463 p_bid_number IN NUMBER,
464 p_line_number IN NUMBER,
465 p_attachmentDesc OUT NOCOPY VARCHAR2,
466 p_attachment OUT NOCOPY LONG,
467 p_error_code OUT NOCOPY VARCHAR2,
468 p_error_msg OUT NOCOPY VARCHAR2) IS
469
470 newline varchar2(256);
471 tab varchar2(256);
472 x_item_description pon_auction_item_prices_all.item_description%TYPE;
473 x_has_price_elements_flag pon_auction_item_prices_all.has_price_elements_flag%TYPE;
474 x_price_element_name pon_price_element_types_tl.name%TYPE;
475 x_bidValue pon_bid_price_elements.bid_currency_value%TYPE;
476 x_pricing_basis_display varchar2(2000);
477 x_msg_suffix VARCHAR2(3) := '';
478 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
479
480 CURSOR total_cost_info IS
481
482 SELECT ppet.name,
483 flv.meaning,
484 pbpe.bid_currency_value
485 FROM pon_bid_price_elements pbpe, pon_price_element_types_tl ppet, fnd_lookup_values flv
486 WHERE pbpe.bid_number = p_bid_number and
487 pbpe.auction_header_id = p_auction_header_id and
488 pbpe.line_number = p_line_number and
489 pbpe.price_element_type_id <> -10 and
490 pbpe.pf_type = 'SUPPLIER' and
491 pbpe.price_element_type_id = ppet.price_element_type_id and
492 ppet.language = PON_AUCTION_PKG.SessionLanguage and
493 flv.lookup_type = 'PON_PRICING_BASIS' and
494 flv.language = PON_AUCTION_PKG.SessionLanguage and
495 flv.view_application_id = 0 and
496 flv.security_group_id = 0 and
497 pbpe.pricing_basis = flv.lookup_code
501 p_attachment := null;
498 ORDER BY sequence_number;
499
500 BEGIN
502 newline := fnd_global.newline;
503 tab := fnd_global.tab;
504
505 p_error_code := 'SUCCESS';
506 p_error_msg := '';
507
508 SELECT dt.doctype_group_name
509 INTO x_doctype_group_name
510 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
511 WHERE auh.auction_header_id = p_auction_header_id and
512 auh.doctype_id = dt.doctype_id;
513
514 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
515
516 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_TOTAL_COST_DESC', x_msg_suffix);
517
518 SELECT item_description, has_price_elements_flag
519 INTO x_item_description, x_has_price_elements_flag
520 FROM pon_auction_item_prices_all
521 WHERE auction_header_id = p_auction_header_id and
522 line_number = p_line_number;
523
524 IF (x_has_price_elements_flag = 'N') THEN
525 p_attachment := null;
526 return;
527 END IF;
528
529
530 p_attachment := x_item_description || newline || newline;
531
532 OPEN total_cost_info;
533 LOOP
534 FETCH total_cost_info INTO x_price_element_name, x_pricing_basis_display, x_bidValue;
535 EXIT WHEN total_cost_info%NOTFOUND;
536
537 p_attachment := p_attachment || x_price_element_name || ' = ' || x_bidValue || ' (' || x_pricing_basis_display || ')' || newline || newline;
538 END LOOP;
539 CLOSE total_cost_info;
540
541 EXCEPTION
542
543 when others then
544 p_error_code := 'FAILURE';
545 p_error_msg := SQLERRM;
546
547 IF (total_cost_info%ISOPEN) THEN
548 close total_cost_info;
549 END IF;
550
551 END GET_TOTAL_COST_ATTACHMENT;
552
553
554 PROCEDURE GET_JOB_DETAILS_ATTACHMENT (p_auction_header_id IN NUMBER,
555 p_line_number IN NUMBER,
556 p_attachmentDesc OUT NOCOPY VARCHAR2,
557 p_attachment OUT NOCOPY LONG,
558 p_error_code OUT NOCOPY VARCHAR2,
559 p_error_msg OUT NOCOPY VARCHAR2) IS
560
561 newline varchar2(256);
562 tab varchar2(256);
563 job_details pon_auction_item_prices_all.additional_job_details%TYPE;
564 x_doctype_group_name pon_auc_doctypes.doctype_group_name%TYPE;
565 x_msg_suffix VARCHAR2(3) := '';
566
567 BEGIN
568 p_attachment := null;
569 newline := fnd_global.newline;
570 tab := fnd_global.tab;
571
572 SELECT dt.doctype_group_name
573 INTO x_doctype_group_name
574 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
575 WHERE auh.auction_header_id = p_auction_header_id and
576 auh.doctype_id = dt.doctype_id;
577
578 x_msg_suffix := PON_AUCTION_PKG.GET_MESSAGE_SUFFIX (x_doctype_group_name);
579
580 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_JOB_DETAILS_DESC',
581 x_msg_suffix);
582
583 p_error_code := 'SUCCESS';
584 p_error_msg := '';
585
586 SELECT replace(paip.additional_job_details, fnd_global.local_chr(13))
587 INTO job_details
588 FROM pon_auction_item_prices_all paip
589 WHERE paip.auction_header_id = p_auction_header_id and
590 paip.line_number = p_line_number;
591
592 p_attachment := job_details;
593
594 EXCEPTION
595
596 when others then
597 p_error_code := 'FAILURE';
598 p_error_msg := SQLERRM;
599
600 END GET_JOB_DETAILS_ATTACHMENT;
601
602 --Complex work- This method creates fnd attachments out of Buyer notes on Payments
603 -- These attachments are put on corresponding PO payments
604 PROCEDURE GET_PAYMENT_NOTE_TO_SUPP ( p_auction_payment_id IN NUMBER,
605 p_attachmentDesc OUT NOCOPY VARCHAR2,
606 p_attachment OUT NOCOPY LONG,
607 p_error_code OUT NOCOPY VARCHAR2,
608 p_error_msg OUT NOCOPY VARCHAR2)
609 IS
610
611 newline varchar2(256);
612 tab varchar2(256);
613 pymt_note pon_auc_payments_shipments.note_to_bidders%TYPE;
614 msgNegPymntNote varchar2(2000);
615
616 BEGIN
617 p_attachment := null;
618 newline := fnd_global.newline;
619 tab := fnd_global.tab;
620 msgNegPymntNote := PON_AUCTION_PKG.getMessage('PON_AUC_WF_NEG_PYMNT_NOTE');
621 p_error_code := 'SUCCESS';
622 p_error_msg := '';
623
624 p_attachmentDesc := PON_AUCTION_PKG.getMessage('PON_AUC_PYMNT_NOTE_SUPP_DESC');
625
626 SELECT replace(pys.note_to_bidders, fnd_global.local_chr(13))
627 INTO pymt_note
628 FROM pon_auc_payments_shipments pys
629 WHERE payment_id= p_auction_payment_id;
630
631
632 IF (pymt_note IS NOT null) THEN
633 p_attachment := msgNegPymntNote || newline || newline || tab || pymt_note;
634 END IF;
635
636 EXCEPTION
637
638 when others then
639 p_error_code := 'FAILURE';
640 p_error_msg := SQLERRM;
641
642
643 END GET_PAYMENT_NOTE_TO_SUPP;
644
645
646
647 END PON_AUCTION_PO_PKG;