[Home] [Help]
PACKAGE BODY: APPS.PON_PRINTING_PKG
Source
1 PACKAGE BODY PON_PRINTING_PKG as
2 /* $Header: PONPRNB.pls 120.72.12010000.2 2008/10/30 10:03:38 jianliu ship $ */
3
4 -------------------------------------------------------------------------------
5
6 -------------------------- HELPER FUNCTIONS ----------------------------------
7
8 -------------------------------------------------------------------------------
9
10 FUNCTION GET_SUFFIX_FOR_MESSAGES(p_auction_header_id in VARCHAR2) return VARCHAR2;
11
12 PROCEDURE SET_AUCTION_MASKS(p_currency_code IN VARCHAR2,
13 p_price_precision IN NUMBER,
14 p_price_mask OUT NOCOPY VARCHAR2,
15 p_amount_mask OUT NOCOPY VARCHAR2);
16
17 FUNCTION GET_MASK(p_precision in NUMBER) return VARCHAR2;
18
19 -------------------------------------------------------------------------------
20
21 -------------------------- END HELPER FUNCTIONS ------------------------------
22
23 -------------------------------------------------------------------------------
24
25
26 ----------------------------------------------------------------
27 -- Returns the suffix for document specific messages for a --
28 -- particular negotiation id --
29 ----------------------------------------------------------------
30 FUNCTION GET_SUFFIX_FOR_MESSAGES(p_auction_header_id in VARCHAR2) return VARCHAR2 is
31 l_message_suffix varchar2(2);
32 BEGIN
33
34 select d.MESSAGE_SUFFIX into l_message_suffix
35 from pon_auc_doctypes d , pon_auction_headers_all pah
36 where pah.auction_header_id = p_auction_header_id
37 and pah.DOCTYPE_ID = d.DOCTYPE_ID;
38
39 l_message_suffix := '_'||l_message_suffix;
40
41 return l_message_suffix;
42
43 END GET_SUFFIX_FOR_MESSAGES;
44
45 ----------------------------------------------------------------
46 -- Sets the price mask and amount mask for a particular --
47 -- negotiation --
48 ----------------------------------------------------------------
49 PROCEDURE SET_AUCTION_MASKS(p_currency_code IN VARCHAR2,
50 p_price_precision IN NUMBER,
51 p_price_mask OUT NOCOPY VARCHAR2,
52 p_amount_mask OUT NOCOPY VARCHAR2) IS
53 l_currency_precision NUMBER;
54 l_ext_precision NUMBER;
55 l_min_acct_unit NUMBER;
56 BEGIN
57
58 p_price_mask := GET_MASK(p_price_precision);
59
60 fnd_currency.get_info(p_currency_code,l_currency_precision,l_ext_precision,l_min_acct_unit);
61 p_amount_mask := GET_MASK(l_currency_precision);
62
63 END SET_AUCTION_MASKS;
64
65 ----------------------------------------------------------------
66 -- Adds the suffix to the message to generate the document --
67 -- specific messages name --
68 ----------------------------------------------------------------
69 FUNCTION GET_DOCUMENT_MESSAGE_NAME(p_message_name in VARCHAR2,
70 p_message_suffix in VARCHAR2)
71 return VARCHAR2 is
72 begin
73
74 return p_message_name||'_'||p_message_suffix;
75 end;
76
77
78 ----------------------------------------------------------------
79 -- Returns the corresponding value of the mesage name after --
80 -- substituting it with the token --
81 ----------------------------------------------------------------
82 FUNCTION GET_MESSAGES(p_message_name in VARCHAR2,
83 p_token_name in VARCHAR2,
84 p_token_value in VARCHAR2) return VARCHAR2 is
85
86 l_message fnd_new_messages.message_text%TYPE;
87
88 BEGIN
89
90 fnd_message.set_name('PON',p_message_name);
91 fnd_message.set_token(p_token_name,p_token_value);
92 l_message := fnd_message.get;
93
94 return l_message;
95
96 END;
97
98 ----------------------------------------------------------------
99 -- Returns the corresponding value of the mesage name after --
100 -- substituting it with the the two token --
101 ----------------------------------------------------------------
102 FUNCTION GET_MESSAGES(p_message_name in VARCHAR2,
103 p_token_name1 in VARCHAR2,
104 p_token_value1 in VARCHAR2,
105 p_token_name2 in VARCHAR2,
106 p_token_value2 in VARCHAR2) return VARCHAR2 is
107
108 l_message fnd_new_messages.message_text%TYPE;
109
110 BEGIN
111
112 fnd_message.set_name('PON',p_message_name);
113 fnd_message.set_token(p_token_name1,p_token_value1);
114 fnd_message.set_token(p_token_name2,p_token_value2);
115 l_message := fnd_message.get;
116
117 return l_message;
118
119 END;
120
121 ----------------------------------------------------------------
122 -- Returns the corresponding value of the mesage name after --
123 -- substituting it with the the three token --
124 ----------------------------------------------------------------
125 FUNCTION GET_MESSAGES(p_message_name in VARCHAR2,
126 p_token_name1 in VARCHAR2,
127 p_token_value1 in VARCHAR2,
128 p_token_name2 in VARCHAR2,
129 p_token_value2 in VARCHAR2,
130 p_token_name3 in VARCHAR2,
131 p_token_value3 in VARCHAR2) return VARCHAR2 is
132
133 l_message fnd_new_messages.message_text%TYPE;
134
135 BEGIN
136
137 fnd_message.set_name('PON',p_message_name);
138 fnd_message.set_token(p_token_name1,p_token_value1);
139 fnd_message.set_token(p_token_name2,p_token_value2);
140 fnd_message.set_token(p_token_name3,p_token_value3);
141 l_message := fnd_message.get;
142
143 return l_message;
144
145 END;
146
147 ----------------------------------------------------------------
148 -- Returns the corresponding value of the mesage name after --
149 -- substituting it with the token. The message name is formed --
150 -- by joining the message name and message suffix parameters --
151 ----------------------------------------------------------------
152 FUNCTION GET_MESSAGES(p_message_name in VARCHAR2,
153 p_message_suffix in VARCHAR2,
154 p_token_name in VARCHAR2,
155 p_token_value in VARCHAR2) return VARCHAR2 is
156
157 l_message fnd_new_messages.message_text%TYPE;
158
159 BEGIN
160
161 l_message := get_messages(p_message_name||p_message_suffix,
162 p_token_name,
163 p_token_value);
164
165 return l_message;
166
167 END;
168
169 ----------------------------------------------------------------
170 -- Calculate Response Total for supplier view. --
171 ----------------------------------------------------------------
172 FUNCTION get_supplier_bid_total
173 (p_auction_header_id IN NUMBER,
174 p_bid_number IN NUMBER,
175 p_buyer_bid_total IN NUMBER,
176 p_outcome IN pon_auction_headers_all.contract_type%TYPE,
177 p_doctype_group_name IN VARCHAR2,
178 p_bid_status IN VARCHAR2
179 ) RETURN NUMBER
180
181 IS
182
183 v_bid_total NUMBER;
184 v_has_est_qty_on_all_bid_lines VARCHAR2(1);
185
186 BEGIN
187
188 IF p_buyer_bid_total is null OR p_buyer_bid_total = '' OR p_buyer_bid_total < 0 THEN
189 IF (p_bid_status = 'DRAFT') THEN
190 IF ((p_doctype_group_name = 'REQUEST_FOR_INFORMATION') OR (p_outcome = 'BLANKET' OR p_outcome = 'CONTRACT')) THEN
191 v_has_est_qty_on_all_bid_lines := PON_TRANSFORM_BIDDING_PKG.check_est_qty_on_all_bid_lines(p_auction_header_id, p_bid_number);
192 IF (v_has_est_qty_on_all_bid_lines = 'N') THEN
193 RETURN null;
194 END IF;
195 END IF;
196 ELSE
197 return null;
198 END IF;
199 END IF;
200
201 SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,
202 decode(p_outcome, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)) *
203 nvl(pbip.bid_currency_price,0)) bid_total
204 INTO v_bid_total
205 FROM pon_bid_item_prices pbip,
206 pon_auction_item_prices_all paip
207 WHERE pbip.auction_header_id = p_auction_header_id AND
208 pbip.bid_number = p_bid_number AND
209 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
210 pbip.auction_header_id = paip.auction_header_id AND
211 pbip.line_number = paip.line_number AND
212 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE');
213
214 RETURN v_bid_total;
215
216 END get_supplier_bid_total;
217
218 ----------------------------------------------------------------
219 -- Formats the number based on the precision pased. If the --
220 -- precision is passed as any then no formatting is done. --
221 ----------------------------------------------------------------
222 FUNCTION GET_MASK(p_precision in NUMBER) return VARCHAR2 is
223
224 l_mask varchar2(80);
225
226 BEGIN
227 if (p_precision = 10000) then
228 --{
229 l_mask := 'FM999G999G999G999G999G999G999G999G999G999G999G999G990D0999999999'; -- consider a big mask to accomodate big numbers
230 return l_mask;
231 --}
232 elsif (p_precision = 0) then
233 --{ For 0 precision we need to hide the decimal seperator
234 l_mask := 'FM999G999G999G999G999G999G999G999G999G999G999G999G999G999G999G999'; -- consider a big mask to accomodate big numbers
235 return l_mask;
236 --}
237 else
238 --{
239 l_mask := 'FM999G999G999G999G999G999G999G999G999G999G999G999G990D'; -- consider a big mask to accomodate big numbers
240 l_mask := rpad(l_mask, (length(l_mask) + p_precision), '0');
241 return l_mask;
242 --}
243 end if;
244 END;
245
246 ----------------------------------------------------------------
247 -- Formats the number based passed. If the number does not --
248 -- decimal part then the decimal separator will not be --
249 -- displayed. If the number is less that 0 then 0 will --
250 -- be displayed before the decimal separator --
251 ----------------------------------------------------------------
252 FUNCTION FORMAT_NUMBER(p_number in NUMBER) return VARCHAR2 is
253
254 l_mask varchar2(80);
255
256 BEGIN
257 if (p_number is null) then
258 --{
259 return null;
260 --}
261 elsif ((ceil(p_number) - p_number) >0) then
262 --{ if number does not have decimal seperator then the decimal will not be displayed
263 l_mask := 'FM999G999G999G999G999G999G999G999G999G999G990D9999999999999999'; -- consider a big mask to accomodate big numbers
264 --}
265 else
266 --{
267 l_mask := 'FM999G999G999G999G999G999G999G999G999G999G999G999G999G999G999G999'; -- consider a big mask to accomodate big numbers
268 --}
269 end if;
270
271 return to_char(p_number,l_mask);
272 END;
273
274 ----------------------------------------------------------------
275 -- Formats the number which is a varchar. --
276 ----------------------------------------------------------------
277 FUNCTION FORMAT_NUMBER_STRING(p_value in VARCHAR2) return VARCHAR2 is
278
279 BEGIN
280 if (p_value is null) then
281 return null;
282
283 elsif (instr(p_value, '.')>0) then
284 return pon_printing_pkg.format_number(to_number(p_value,'9999999999999999999999999999999999999999.9999999999999999'));
285 else
286 return pon_printing_pkg.format_number(to_number(p_value,'9999999999999999999999999999999999999999'));
287 end if;
288
289 EXCEPTION
290 when others then
291 return p_value;
292
293 END;
294 -----------------------------------------------------------------
295 -- For Bug 4373655
296 -- Returns the carrier name based on the carrier code and --
297 -- inventory org id corresponding to the org_id passed a --
298 -- parameter --
299 -----------------------------------------------------------------
300 FUNCTION get_carrier_description(p_org_id in NUMBER,p_carrier_code in VARCHAR2)
301 return varchar2
302 is
303
304 l_carrier org_freight.description%TYPE;
305
306 begin
307
308 select
309 orgf.description carrier
310 into
311 l_carrier
312 from
313 financials_system_params_all fsp,
314 org_freight orgf
315 where
316 nvl(fsp.org_id, -9999)= nvl(p_org_id, -9999)
317 and orgf.organization_id = fsp.inventory_organization_id
318 and orgf.freight_code = p_carrier_code
319 and orgf.language = userenv('lang');
320
321 return l_carrier;
322
323 exception
324 when NO_DATA_FOUND then
325 l_carrier := null;
326 return l_carrier;
327
328 END get_carrier_description;
329
330
331 ----------------------------------------------------------------
332 -- Formats the price passed based on the format passed. --
333 -- If the price does have a decimal part then the decimal --
334 -- separator will not be displayed. If the price is less --
335 -- that 0 then 0 will be displayed before the decimal --
336 -- separator --
337 ----------------------------------------------------------------
338 FUNCTION FORMAT_PRICE(p_price in NUMBER,
339 p_format_mask in VARCHAR2,
340 p_precision IN NUMBER)
341 return VARCHAR2 is
342 l_mask varchar2(80);
343
344 BEGIN
345 if (p_price is null) then
346 --{
347 return null;
348 --}
349 elsif ((ceil(p_price) - p_price) =0 and p_precision = 10000) then
350 --{ if price does not have decimal seperator and precision is 'Any' then
351 -- the decimal will not be displayed
352 l_mask := 'FM999G999G999G999G999G999G999G999G999G999G999G999G999G999G999G999'; -- consider a big mask to accomodate big numbers
353 --}
354 else
355 --{
356 l_mask := p_format_mask; -- consider the original mask
357 --}
358 end if;
359
360 return to_char(p_price,l_mask);
361
362 END;
363
364 ----------------------------------------------------------------
365 -- Returns Y if XDO is installed --
366 -- NOTES : valid installation status: --
367 -- I - Product is installed --
368 -- S - Product is partially installed --
369 -- N - Product is not installed --
370 -- L - Product is a local (custom) application --
371 ----------------------------------------------------------------
372
373 FUNCTION is_xdo_installed RETURN VARCHAR2 IS
374 x_progress VARCHAR2(3) := NULL;
375 x_app_id NUMBER;
376 x_install BOOLEAN;
377 x_status VARCHAR2(1);
378 x_org VARCHAR2(1);
379 x_temp_product_name varchar2(10);
380 x_is_xdo_installed varchar2(1);
381 begin
382 --Retreive product id from fnd_application based on product name
383 x_progress := 10;
384
385 select application_id
386 into x_app_id
387 from fnd_application
388 where application_short_name = 'XDO' ;
389
390 --get product installation status
391 x_progress := 20;
392 x_install := fnd_installation.get(x_app_id,x_app_id,x_status,x_org);
393
394 if x_status = 'I' then
395 x_is_xdo_installed := 'Y';
396 else
397 x_is_xdo_installed := 'N';
398 end if;
399
400 RETURN(x_is_xdo_installed);
401
402 EXCEPTION
403 WHEN NO_DATA_FOUND then
404 RETURN('N');
405 end is_xdo_installed;
406
407 ----------------------------------------------------------------
408 -- Formats the response value of the attributes --
409 ----------------------------------------------------------------
410
411 FUNCTION PRINT_ATTRIBUTE_RESPONSE_VALUE(
412 p_value in VARCHAR2,
413 p_datatype in VARCHAR2,
414 p_client_time_zone in VARCHAR2,
415 p_server_time_zone in VARCHAR2,
416 p_date_format in VARCHAR2,
417 p_attribute_sequence_number in NUMBER
418 )
419 RETURN VARCHAR2 is
420 l_datetime_flag VARCHAR2(1);
421 begin
422
423 if (p_datatype='DAT') then
424 if (p_attribute_sequence_number = -10) then
425 l_datetime_flag := 'Y'; -- need-by date is datetime
426 else
427 l_datetime_flag := 'N';
428 end if;
429 return pon_resp_scores_pkg.display_db_date_string(p_value, p_client_time_zone, p_server_time_zone, l_datetime_flag, p_date_format);
430
431 elsif (p_datatype='NUM') then
432
433 return format_number_string(p_value);
434
435 else
436
437 return p_value;
438
439 end if;
440 END PRINT_ATTRIBUTE_RESPONSE_VALUE;
441 ----------------------------------------------------------------
442 -- Formats the target value of the attributes --
443 ----------------------------------------------------------------
444 FUNCTION PRINT_ATTRIBUTE_TARGET_VALUE(p_show_target_value in VARCHAR2,
445 p_value in VARCHAR2,
446 p_datatype in VARCHAR2,
447 p_sequence_number in VARCHAR2,
448 p_client_time_zone in VARCHAR2,
449 p_server_time_zone in VARCHAR2,
450 p_date_format in VARCHAR2,
451 p_user_view_type IN VARCHAR2)
452 RETURN VARCHAR2 is
453 begin
454
455 if ((p_show_target_value = 'N' and p_user_view_type <> 'BUYER') or p_value is null) then
456
457 return NULL;
458
459 elsif (p_datatype='DAT' and p_sequence_number <> -10) then
460
461 return pon_resp_scores_pkg.display_db_date_string(p_value, p_client_time_zone, p_server_time_zone, 'N', p_date_format);
462
463 elsif (p_datatype='NUM') then
464
465 return format_number_string(p_value);
466
467 else
468
469 return p_value;
470
471 end if;
472
473 END PRINT_ATTRIBUTE_TARGET_VALUE;
474
475
476 -----------------------------------------------------------------
477 -- Returns the email of the user. Creating this method instead --
478 -- of an outer join as in hz contact points table same email --
479 -- record with active status of type MAILHTML existed. This was--
480 -- leading 2 records for all the queries. --
481 -----------------------------------------------------------------
482 FUNCTION GET_USER_EMAIL(p_user_party_id in NUMBER)
483 return VARCHAR2 is
484
485 x_usermail per_all_people_f.email_address%type;
486
487 begin
488
489
490 Select
491 papf.email_address into
492 x_usermail
493 from
494 per_all_people_f papf, fnd_user
495 where
496 fnd_user.person_party_id = p_user_party_id
497 and fnd_user.employee_id = papf.person_id
498 and papf.effective_end_date = (SELECT MAX(per1.effective_end_date)
499 FROM per_all_people_f per1
500 WHERE papf.person_id = per1.person_id)
501 and rownum = 1;
502
503 return x_usermail;
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND then
507 RETURN('');
508
509 end;
510
511
512 ----------------------------------------------------------------
513 -- Retunrs the range / value of a for an attribute along with --
514 -- the score. The score will be displayed only if --
515 -- p_show_bidder_scores is 'Y' --
516 ----------------------------------------------------------------
517 FUNCTION GET_ACCEPTABLE_VALUE(p_show_bidder_scores in VARCHAR2,
518 p_attribute_sequence_number NUMBER,
519 p_score_data_type in VARCHAR2,
520 p_from_range in VARCHAR2,
521 p_to_range in VARCHAR2,
522 p_value in VARCHAR2,
523 p_score in VARCHAR2,
524 p_client_time_zone in VARCHAR2,
525 p_server_time_zone in VARCHAR2,
526 p_date_format in VARCHAR2,
527 p_user_view_score IN VARCHAR2)
528 RETURN VARCHAR2 is
529
530
531 x_acc_values varchar2(2000);
532
533 -- set up all the translatable msgs to
534 -- be inserted in the returned string
535
536 msgAtMost VARCHAR2(2000); -- PON_AUC_AT_MOST
537 msgAtLeast VARCHAR2(2000); -- PON_AUC_AT_LEAST
538 msgOnOrBefore VARCHAR2(2000); -- PON_AUC_ON_OR_BEFORE
539 msgOnOrAfter VARCHAR2(2000); -- PON_AUC_ON_OR_AFTER
540 msgTo VARCHAR2(2000); -- PON_AUC_TO
541 msgFrom VARCHAR2(2000); -- PON_AUCTS_FROM
542 msgGreaterThan VARCHAR2(200); -- PON_AUC_GREATER_THAN
543 msgUpTo VARCHAR2(200); -- PON_AUC_UP_TO
544
545 msgSeparator VARCHAR2(2) := ' ';
546
547 dateFmtMask VARCHAR2(24);
548 dateFmtSave VARCHAR2(24);
549 l_datetime_flag VARCHAR2(1);
550
551 begin
552
553 --need to get this based on user language
554 --NEED TO SEE IF PON_PRINTING_PKG.UNSET_SESSION_LAGUAGE is to be called
555 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, USERENV('LANG'));
556
557 msgAtMost := PON_AUCTION_PKG.getMessage('PON_AUC_AT_MOST');
558 msgAtLeast := PON_AUCTION_PKG.getMessage('PON_AUC_AT_LEAST');
559 msgOnOrBefore := PON_AUCTION_PKG.getMessage('PON_AUC_ON_OR_BEFORE');
560 msgOnOrAfter := PON_AUCTION_PKG.getMessage('PON_AUC_ON_OR_AFTER');
561 msgTo := PON_AUCTION_PKG.getMessage('PON_AUC_TO');
562 msgFrom := PON_AUCTION_PKG.getMessage('PON_AUCTS_FROM');
563 msgGreaterThan := PON_AUCTION_PKG.getMessage('PON_AUC_GREATER_THAN');
564 msgUpTo := PON_AUCTION_PKG.getMessage('PON_AUC_UP_TO');
565
566 dateFmtMask := p_date_format;
567 dateFmtSave := 'DD-MM-RRRR';
568
569
570 if (p_score_data_type = 'TXT') then
571
572 -- if the attribute datatype is text, then
573 -- simply print the acceptable values
574 -- no need to add to, from etc.
575
576 --{
577
578 x_acc_values := p_value || msgSeparator;
579
580 --}
581
582 elsif(p_score_data_type = 'NUM' OR p_score_data_type = 'INT') then
583
584 --{
585
586 if(p_from_range = '' OR p_from_range is null ) then
587
588 x_acc_values := msgUpTo || msgSeparator || format_number_string(p_to_range) || msgSeparator;
589
590 elsif (p_to_range = '' OR p_to_range is null) then
591
592 x_acc_values := msgGreaterThan || msgSeparator || format_number_string(p_from_range) || msgSeparator;
593
594 else
595
596 x_acc_values := format_number_string(p_from_range) || msgSeparator || msgTo || msgSeparator || format_number_string(p_to_range) || msgSeparator;
597
598 end if;
599
600 --}
601
602 elsif(p_score_data_type = 'DAT') then
603
604 --{
605
606 if (p_attribute_sequence_number = -10) then
607 l_datetime_flag := 'Y'; -- need-by date is datetime
608 else
609 l_datetime_flag := 'N';
610 end if;
611
612 if(p_from_range = '' OR p_from_range is null) then
613
614 x_acc_values := x_acc_values || msgOnOrBefore || msgSeparator || pon_resp_scores_pkg.display_db_date_string(p_to_range, p_client_time_zone, p_server_time_zone, l_datetime_flag, p_date_format) || msgSeparator;
615
616 elsif (p_to_range = '' OR p_to_range is null) then
617
618 x_acc_values := x_acc_values || msgOnOrAfter || msgSeparator || pon_resp_scores_pkg.display_db_date_string(p_from_range, p_client_time_zone, p_server_time_zone, l_datetime_flag, p_date_format) || msgSeparator;
619
620 else
621
622 x_acc_values := x_acc_values || msgFrom || msgSeparator || pon_resp_scores_pkg.display_db_date_string(p_from_range, p_client_time_zone, p_server_time_zone, l_datetime_flag, p_date_format) || msgSeparator || msgTo
623 || msgSeparator || pon_resp_scores_pkg.display_db_date_string(p_to_range, p_client_time_zone, p_server_time_zone, l_datetime_flag, p_date_format) || msgSeparator;
624
625 end if;
626
627 --}
628 end if;
629
630
631 if((p_user_view_score = 'Y' OR p_show_bidder_scores = 'Y' OR p_show_bidder_scores = 'SCORE_WEIGHT') and p_score is not null) then
632 --{
633
634 x_acc_values := x_acc_values || '(' || p_score || ')';
635
636 end if;
637
638 --}
639
640 --dbms_output.put_line('Returning AccValues = ' || x_acc_values || ' Scores = ' || p_score||' p_show_bidder_scores='||p_show_bidder_scores ||' p_score ='||p_score);
641
642 return x_acc_values;
643
644
645 END GET_ACCEPTABLE_VALUE;
646
647
648 ----------------------------------------------------------------
649 -- Returns the rate to be displayed --
650 ----------------------------------------------------------------
651 FUNCTION GET_DISPLAY_RATE(p_rate_dsp in NUMBER,
652 p_rate_type in VARCHAR2,
653 p_rate_date in DATE,
654 p_auction_currency_code in VARCHAR2,
655 p_bid_currency_code in VARCHAR2) return VARCHAR2 is
656
657 l_display_rate NUMBER;
658 l_printing_text VARCHAR2(200);
659
660 BEGIN
661
662 begin
663
664 if (p_rate_type= 'User') then
665
666 l_display_rate := p_rate_dsp;
667
668 else
669
670 l_display_rate := (1/PON_AUCTION_PKG.getClosestRate(p_auction_currency_code,p_bid_currency_code,p_rate_date,p_rate_type,0));
671
672 end if;
673
674 l_printing_text := pon_printing_pkg.get_messages('PON_AUC_DISPLAY_RATE','AUCTION_CURRENCY',p_auction_currency_code,'RATE',pon_printing_pkg.format_number(l_display_rate),'BID_CURRENCY',p_bid_currency_code);
675
676 exception
677
678 when others then
679
680 select message_text
681 into l_printing_text
682 from fnd_new_messages
683 where message_name = 'PON_AUC_CONTACT_BUYER'
684 and language_code = USERENV('LANG')
685 and application_id = 396;
686
687 end;
688
689 return l_printing_text;
690
691 END GET_DISPLAY_RATE;
692
693 ----------------------------------------------------------------------
694 -- Returns whether the buyer has price visibility in scoring team --
695 ----------------------------------------------------------------------
696 FUNCTION is_price_visible( p_auction_header_id IN NUMBER,
697 p_user_id IN NUMBER
698 ) RETURN VARCHAR2 IS
699 l_has_scoring_teams_flag VARCHAR2(1) := NULL;
700 l_is_price_visible VARCHAR2(1) := 'Y';
701 l_is_scorer VARCHAR2(1) := 'N';
702
703 begin
704 --The logic comes from: java/response/inquiry/webui/ViewBidCO.java
705 --java/schema/server/ScoringTeamPriceVisibilityVVO, java/schema/server/STMemberAccessVVO
706
707 select has_scoring_teams_flag
708 into l_has_scoring_teams_flag
709 from pon_auction_headers_all
710 where auction_header_id = p_auction_header_id;
711
712 if l_has_scoring_teams_flag = 'Y' then
713 BEGIN
714 SELECT 'Y'
715 into l_is_scorer
716 FROM pon_neg_team_members
717 WHERE menu_name = 'PON_SOURCING_SCORENEG'
718 AND auction_header_id = p_auction_header_id
719 AND user_id = p_user_id;
720 EXCEPTION
721 WHEN NO_DATA_FOUND then
722 l_is_scorer := 'N';
723 END;
724 if (l_is_scorer = 'Y') then
725 BEGIN
726 SELECT pst.price_visible_flag
727 INTO l_is_price_visible
728 FROM pon_scoring_team_members pstm,
729 pon_scoring_teams pst
730 WHERE pstm.auction_header_id = p_auction_header_id
731 AND pstm.user_id = p_user_id
732 AND pst.auction_header_id = pstm.auction_header_id
733 AND pstm.team_id = pst.team_id;
734 EXCEPTION
735 WHEN NO_DATA_FOUND then
736 l_is_price_visible := 'N';
737 END;
738 end if;
739 end if;
740 RETURN (l_is_price_visible);
741 end is_price_visible;
742
743 -- Overloaded version of the following procedure without trading_partner_contact_id
744 -- Kept here for backward compatibility - can be removed once all the dependent
745 -- changes are done.
746
747 FUNCTION generate_auction_xml(p_auction_header_id IN NUMBER,
748 p_client_time_zone IN VARCHAR2,
749 p_server_time_zone IN VARCHAR2,
750 p_date_format IN VARCHAR2,
751 p_trading_partner_id IN NUMBER,
752 p_trading_partner_name IN VARCHAR2,
753 p_vendor_site_id IN NUMBER,
754 p_user_view_type IN VARCHAR2,
755 p_printing_warning_flag IN VARCHAR2 DEFAULT 'N',
756 p_neg_printed_with_contracts IN VARCHAR2 DEFAULT 'N',
757 p_requested_supplier_id IN NUMBER,
758 p_requested_supplier_name IN VARCHAR2)
759 RETURN CLOB IS
760
761 result CLOB;
762
763 BEGIN
764
765 result := generate_auction_xml(p_auction_header_id => p_auction_header_id,
766 p_client_time_zone => p_client_time_zone,
767 p_server_time_zone => p_server_time_zone,
768 p_date_format => p_date_format,
769 p_trading_partner_id => p_trading_partner_id,
770 p_trading_partner_name => p_trading_partner_name,
771 p_vendor_site_id => p_vendor_site_id,
772 p_user_view_type => p_user_view_type,
773 p_printing_warning_flag => p_printing_warning_flag,
774 p_neg_printed_with_contracts => p_neg_printed_with_contracts,
775 p_requested_supplier_id => p_requested_supplier_id,
776 p_requested_supplier_name => p_requested_supplier_name,
777 p_trading_partner_contact_id => NULL);
778 RETURN result;
779 END;
780
781 FUNCTION generate_auction_xml(p_auction_header_id IN NUMBER,
782 p_client_time_zone IN VARCHAR2,
783 p_server_time_zone IN VARCHAR2,
784 p_date_format IN VARCHAR2,
785 p_trading_partner_id IN NUMBER,
786 p_trading_partner_name IN VARCHAR2,
787 p_vendor_site_id IN NUMBER,
788 p_user_view_type IN VARCHAR2,
789 p_printing_warning_flag IN VARCHAR2 DEFAULT 'N',
790 p_neg_printed_with_contracts IN VARCHAR2 DEFAULT 'N',
791 p_requested_supplier_id IN NUMBER,
792 p_requested_supplier_name IN VARCHAR2,
793 p_trading_partner_contact_id IN NUMBER)
794 RETURN CLOB IS
795
796 result CLOB;
797
798 BEGIN
799
800 result := generate_auction_xml(p_auction_header_id => p_auction_header_id,
801 p_client_time_zone => p_client_time_zone,
802 p_server_time_zone => p_server_time_zone,
803 p_date_format => p_date_format,
804 p_trading_partner_id => p_trading_partner_id,
805 p_trading_partner_name => p_trading_partner_name,
806 p_vendor_site_id => p_vendor_site_id,
807 p_user_view_type => p_user_view_type,
808 p_printing_warning_flag => p_printing_warning_flag,
809 p_neg_printed_with_contracts => p_neg_printed_with_contracts,
810 p_requested_supplier_id => p_requested_supplier_id,
811 p_requested_supplier_name => p_requested_supplier_name,
812 p_trading_partner_contact_id => p_trading_partner_contact_id,
813 p_bid_number => -1,
814 p_user_id => null);
815 RETURN result;
816 END;
817
818 -------------------------------------------------------------------------------
819 -- If p_bid_number > 0 , Creates a xml file for the bid number and returns --
820 -- it as a clob. If p_bid_number <= 0, creates a xml file for the auction id --
821 -------------------------------------------------------------------------------
822 FUNCTION generate_auction_xml(p_auction_header_id IN NUMBER,
823 p_client_time_zone IN VARCHAR2,
824 p_server_time_zone IN VARCHAR2,
825 p_date_format IN VARCHAR2,
826 p_trading_partner_id IN NUMBER,
827 p_trading_partner_name IN VARCHAR2,
828 p_vendor_site_id IN NUMBER,
829 p_user_view_type IN VARCHAR2,
830 p_printing_warning_flag IN VARCHAR2 DEFAULT 'N',
831 p_neg_printed_with_contracts IN VARCHAR2 DEFAULT 'N',
832 p_requested_supplier_id IN NUMBER,
833 p_requested_supplier_name IN VARCHAR2,
834 p_trading_partner_contact_id IN NUMBER,
835 p_bid_number IN NUMBER,
836 p_user_id IN NUMBER)
837 RETURN CLOB IS
838 result CLOB;
839 l_suffix varchar2(2);
840 l_resultOffset number;
841 l_xml_header varchar2(100);
842 l_xml_header_length number;
843 TYPE auction_header_cursor_type IS REF CURSOR;
844 xml_query_cursor auction_header_cursor_type;
845 xml_stmt varchar2(500);
846 l_neg_tp_id pon_auction_headers_all.trading_partner_id%type;
847 l_doc_type_id pon_auction_headers_all.DOCTYPE_ID%TYPE;
848 l_doc_type VARCHAR2(50);
849 l_currency_code pon_auction_headers_all.currency_code%type;
850 l_price_precision pon_auction_headers_all.number_price_decimals%type;
851 l_price_mask varchar2(80); -- mask based on the negotiation price precision
852 l_amount_mask varchar2(80); -- mask based on the negotiation currency precision
853
854 l_contracts_installed VARCHAR2(1);
855 l_cont_attach_doc_flag VARCHAR2(1);
856 l_cont_nonmerge_flag VARCHAR2(1);
857 l_printing_language VARCHAR2(3);
858 l_supplier_sequence_number NUMBER;
859 l_award_approval_enabled VARCHAR2(1);
860 l_neg_has_price_breaks VARCHAR2(1);
861
862 l_start_time DATE;
863 l_end_time DATE;
864 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
865 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
866 l_module_name VARCHAR2(80) := 'pon.plsql.PON_PRINTING_PKG.GENERATE_AUCTION_XML';
867
868
869 l_enfrc_prevrnd_bid_price_flag pon_auction_headers_all.enforce_prevrnd_bid_price_flag%TYPE;
870 l_auction_header_id_prev_round pon_auction_headers_all.auction_header_id_prev_round%TYPE;
871 l_start_price_from_prev_rnd VARCHAR2(1);
872 l_prev_rnd_bid_number pon_bid_headers.bid_number%TYPE;
873 l_contract_type pon_auction_headers_all.contract_type%TYPE;
874 l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
875 l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
876
877 --bidpdf:
878 l_is_bidpdf VARCHAR2(1) := 'Y';
879 l_is_supplier_bidpdf VARCHAR2(1) := 'N';
880 l_is_buyer_negpdf VARCHAR2(1) := 'N';
881
882 l_bid_currency_code pon_bid_headers.bid_currency_code%type;
883 l_bid_price_precision pon_bid_headers.number_price_decimals%type;
884 -- bidpdf: address of supplier:
885 l_supplier_address_line1 hz_parties.address1%type;
886 l_supplier_address_line2 hz_parties.address2%type;
887 l_supplier_address_line3 hz_parties.address3%type;
888 l_supplier_address_city hz_parties.city%type;
889 l_supplier_address_state hz_parties.state%type;
890 l_supplier_postal_code hz_parties.postal_code%type;
891 l_supplier_country_code hz_parties.country%type;
892 l_supplier_country fnd_territories_tl.territory_short_name%type;
893 l_vendor_site_id NUMBER;
894 l_vendor_id NUMBER;
895 -- bidpdf: contact details:
896 l_contact_details_name varchar2(600);
897 l_trading_partner_id NUMBER := p_trading_partner_id;
898 -- bidpdf: on buyer side, check whether buyer in scoring team that cannot see price
899 l_price_visibility VARCHAR2(1) := 'Y';
900 l_is_section_restricted VARCHAR2(1) := 'N';
901 l_proxybid_display_flag VARCHAR2(1) := 'N';
902 -- bidpdf: currency change rate
903 l_rate NUMBER := 1;
904 l_bid_rate NUMBER := 1;
905 l_is_super_large_neg VARCHAR2(1) := 'N';
906 -- previous round doc type, used to decide whether to display
907 -- control "enforce supplie's previous round price as bid start price"
908 l_prev_rnd_doctype pon_auc_doctypes.internal_name%TYPE;
909 -- two-part RFQ
910 l_hide_comm_part VARCHAR2(1) := 'N';
911 l_two_part_flag pon_auction_headers_all.two_part_flag%TYPE; -- two-part flag
912 -- commercial lock status
913 l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%TYPE;
914 -- technical shortlist status
915 l_tech_shortlist_flag pon_bid_headers.technical_shortlist_flag%type;
916 --added by Allen Yang for Surrogate Bid 2008/09/04
917 ---------------------------------------------------------------
918 l_tech_evaluation_status PON_AUCTION_HEADERS_ALL.Technical_Evaluation_Status%TYPE;
919 l_surrogate_bid_flag PON_BID_HEADERS.Surrog_Bid_Flag%TYPE;
920 CURSOR tech_surrogate_bid_cur IS
921 SELECT
922 paha.Technical_Evaluation_Status
923 , pbh.SURROG_BID_FLAG
924 FROM
925 pon_auction_headers_all paha, pon_bid_headers pbh
926 WHERE paha.auction_header_id=pbh.auction_header_id
927 AND paha.auction_header_id=p_auction_header_id
928 AND pbh.bid_number = p_bid_number;
929 ----------------------------------------------------------------
930
931 -- used to set what categories are shown in quote pdf.
932 -- this option can have 3 values:
933 -- 1 (`FromSupplier')
934 -- 2 (`FromSupplierTechnical')
935 -- 3 (`FromSupplierTechnical', `FromSupplierCommercial')
936 l_attach_categ_option NUMBER := 1;
937
938 -- three variables to store two-part messages.
939 l_two_part_general_msg fnd_new_messages.message_text%TYPE;
940 l_two_part_tech_msg fnd_new_messages.message_text%TYPE;
941 l_two_part_comm_msg fnd_new_messages.message_text%TYPE;
942
943 BEGIN
944 IF p_bid_number <= 0 THEN
945 l_is_bidpdf := 'N';
946 ELSE
947 l_is_bidpdf := 'Y';
948 BEGIN
949 select
950 bid_currency_code,
951 number_price_decimals,
952 vendor_site_id,
953 vendor_id,
954 trading_partner_id,
955 rate,
956 technical_shortlist_flag
957 into
958 l_bid_currency_code,
959 l_bid_price_precision,
960 l_vendor_site_id,
961 l_vendor_id,
962 l_trading_partner_id,
963 l_bid_rate,
964 l_tech_shortlist_flag
965 from pon_bid_headers
966 where bid_number = p_bid_number;
967 EXCEPTION
968 WHEN no_data_found THEN
969 l_is_bidpdf := 'N';
970 END;
971 END IF;
972 select
973 trading_partner_id,
974 doctype_id,
975 currency_code,
976 number_price_decimals,
977 enforce_prevrnd_bid_price_flag,
978 auction_header_id_prev_round,
979 contract_type,
980 supplier_view_type,
981 pf_type_allowed,
982 nvl(two_part_flag, 'N'),
983 nvl(sealed_auction_status,' ')
984 into
985 l_neg_tp_id,
986 l_doc_type_id,
987 l_currency_code,
988 l_price_precision,
989 l_enfrc_prevrnd_bid_price_flag,
990 l_auction_header_id_prev_round,
991 l_contract_type,
992 l_supplier_view_type,
993 l_pf_type_allowed,
994 l_two_part_flag,
995 l_commercial_lock_status
996 from pon_auction_headers_all
997 where auction_header_id = p_auction_header_id;
998
999 IF l_auction_header_id_prev_round IS NOT NULL
1000 THEN
1001 BEGIN
1002 SELECT doctypes.internal_name
1003 INTO l_prev_rnd_doctype
1004 FROM pon_auction_headers_all pah, pon_auc_doctypes doctypes
1005 WHERE pah.auction_header_id = l_auction_header_id_prev_round
1006 and pah.doctype_id = doctypes.doctype_id;
1007 EXCEPTION
1008 WHEN NO_DATA_FOUND
1009 THEN
1010 l_prev_rnd_doctype := '';
1011 END;
1012 END IF;
1013
1014 IF l_statement_log_level >= l_current_log_level THEN
1015 FND_LOG.string(l_statement_log_level, l_module_name, 'Two-Part related variables: l_two_part_flag: ' || l_two_part_flag || '; l_commercial_lock_status: '||l_commercial_lock_status);
1016 END IF;
1017
1018 l_printing_language := userenv('lang') ;
1019
1020 if (PON_LARGE_AUCTION_UTIL_PKG.is_super_large_neg(p_auction_header_id)) then
1021 l_is_super_large_neg := 'Y';
1022 else
1023 l_is_super_large_neg := 'N';
1024 end if;
1025
1026 -- bidpdf: get address and contact name
1027 -- If the negotiation did not invite any supplier, get the company address
1028 -- Otherwise, get the site address
1029 IF l_is_bidpdf = 'Y' THEN
1030
1031 IF p_user_view_type = 'SUPPLIER' THEN
1032 l_is_supplier_bidpdf := 'Y';
1033 l_currency_code := l_bid_currency_code;
1034 l_price_precision := l_bid_price_precision;
1035 l_rate := l_bid_rate;
1036 ELSE
1037 l_price_visibility := is_price_visible(p_auction_header_id, p_user_id);
1038 --To decide whether need to check which sections in requirement should be displayed
1039 --As in ViewBidCO, hasScoringTeamsFlag && !isScoringLocked isScorer
1040 BEGIN
1041 SELECT 'Y'
1042 INTO l_is_section_restricted
1043 FROM pon_neg_team_members pntm, pon_auction_headers_all pah
1044 WHERE pah.auction_header_id = p_auction_header_id
1045 AND pntm.menu_name = 'PON_SOURCING_SCORENEG'
1046 AND pntm.auction_header_id = pah.auction_header_id
1047 AND pntm.user_id = p_user_id
1048 AND pah.has_scoring_teams_flag = 'Y'
1049 AND pah.scoring_lock_date is null;
1050 EXCEPTION
1051 WHEN NO_DATA_FOUND then
1052 l_is_section_restricted := 'N';
1053 END;
1054 END IF;
1055
1056 IF l_vendor_site_id <= 0 THEN
1057 BEGIN
1058 -- bidpdf: address/contact of supplier company
1059 select hz_parties.address1, hz_parties.address2, hz_parties.address3, hz_parties.city, hz_parties.state, hz_parties.postal_code, hz_parties.country, nvl(entity_terr.territory_short_name,hz_parties.country)
1060 ,PON_LOCALE_PKG.get_party_display_name(pon_bid_headers.trading_partner_contact_id)
1061 into l_supplier_address_line1,l_supplier_address_line2,l_supplier_address_line3,l_supplier_address_city,l_supplier_address_state,l_supplier_postal_code,l_supplier_country_code,l_supplier_country
1062 ,l_contact_details_name
1063 from hz_parties, pon_bid_headers, fnd_territories_tl entity_terr
1064 where pon_bid_headers.trading_partner_id = hz_parties.party_id
1065 and pon_bid_headers.bid_number = p_bid_number
1066 and entity_terr.territory_code(+) = hz_parties.country
1067 and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
1068 and entity_terr.language(+) = l_printing_language
1069 and rownum = 1;
1070 EXCEPTION
1071 WHEN no_data_found THEN
1072 l_supplier_address_line1 := '';
1073 END;
1074 ELSE
1075 BEGIN
1076 -- get supplier site address/contact, reference from java/poplist/server/VendorSitesAllVO.xml
1077 SELECT pvsa.address_line1,pvsa.address_line2,pvsa.address_line3,pvsa.city,pvsa.state,pvsa.zip,pvsa.country, nvl(entity_terr.territory_short_name,pvsa.country),
1078 decode(pbp.trading_partner_contact_id, null, pbp.requested_supp_contact_name, PON_LOCALE_PKG.get_party_display_name(pbp.trading_partner_contact_id)) contact_name
1079 into l_supplier_address_line1,l_supplier_address_line2,l_supplier_address_line3,l_supplier_address_city,l_supplier_address_state,l_supplier_postal_code,l_supplier_country_code,l_supplier_country,l_contact_details_name
1080 FROM PO_VENDOR_SITES_ALL pvsa, pon_auction_headers_all pah, pon_bidding_parties pbp, fnd_territories_tl entity_terr
1081 WHERE
1082 pah.auction_header_id = p_auction_header_id
1083 AND pvsa.org_id = pah.org_id
1084 AND PURCHASING_SITE_FLAG = 'Y'
1085 AND SYSDATE< NVL(INACTIVE_DATE, SYSDATE + 1)
1086 AND vendor_id=l_vendor_id
1087 AND nvl(rfq_only_site_flag, 'N')='N'
1088 AND pvsa.vendor_site_id = l_vendor_site_id
1089 AND pbp.auction_header_id = pah.auction_header_id
1090 AND pbp.vendor_site_id = pvsa.vendor_site_id
1091 and entity_terr.territory_code(+) = pvsa.country
1092 and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
1093 and entity_terr.language(+) = l_printing_language
1094 and rownum = 1;
1095 EXCEPTION
1096 WHEN no_data_found THEN
1097 l_supplier_address_line1 := '';
1098 END;
1099 END IF;
1100 ELSIF p_user_view_type = 'BUYER' THEN
1101 l_is_buyer_negpdf := 'Y';
1102 END IF;
1103 --dbms_application_info.set_client_info(204);
1104 SET_AUCTION_MASKS(l_currency_code, l_price_precision, l_price_mask, l_amount_mask);
1105
1106 l_suffix := GET_SUFFIX_FOR_MESSAGES(p_auction_header_id);
1107
1108 -- determine if there are price breaks
1109 BEGIN
1110 SELECT 'Y'
1111 INTO l_neg_has_price_breaks
1112 FROM pon_auction_shipments_all
1113 WHERE auction_header_id = p_auction_header_id
1114 AND ROWNUM = 1;
1115 EXCEPTION
1116 WHEN no_data_found THEN
1117 l_neg_has_price_breaks := 'N';
1118 END;
1119
1120 -- bidpdf: add l_is_bidpdf='Y' for case when on buyer side, the buyer cost factor values
1121 -- can be displayed. If not added, pf_values.value in cursor PRICE_FACTORS will be null
1122 -- because of condition pf_values.supplier_seq_number(+) = l_supplier_sequence_number
1123 IF (p_user_view_type = 'SUPPLIER' or l_is_bidpdf='Y') AND (l_trading_partner_id IS NOT NULL
1124 OR p_requested_supplier_id IS NOT NULL) THEN
1125 BEGIN
1126 SELECT sequence
1127 INTO l_supplier_sequence_number
1128 FROM pon_bidding_parties
1129 WHERE
1130 auction_header_id = p_auction_header_id
1131 AND ((trading_partner_id = l_trading_partner_id AND
1132 vendor_site_id = p_vendor_site_id) OR
1133 requested_supplier_id = p_requested_supplier_id);
1134 EXCEPTION
1135 WHEN no_data_found THEN
1136 l_supplier_sequence_number := NULL;
1137 END;
1138 ELSE
1139 l_supplier_sequence_number := NULL;
1140 END IF;
1141
1142 l_award_approval_enabled := fnd_profile.value('PON_AWARD_APPROVAL_ENABLED');
1143 l_contracts_installed := fnd_profile.value('POC_ENABLED');
1144
1145 --We do not want the warning about attached document in case the user does not
1146 --have privilege to view contract terms
1147 if (p_printing_warning_flag = 'Y' OR nvl(l_contracts_installed, 'N') = 'N') then
1148 l_cont_attach_doc_flag := 'N';
1149 else
1150 l_doc_type := PON_CONTERMS_UTL_PVT.get_negotiation_doc_type(l_doc_type_id);
1151 if (PON_CONTERMS_UTL_PVT.isDocumentMergeable(l_doc_type, to_number(p_auction_header_id)) = 'N') then
1152 l_cont_nonmerge_flag := 'Y';
1153 else
1154 l_cont_nonmerge_flag := 'N';
1155 end if;
1156
1157 if (PON_CONTERMS_UTL_PVT.isAttachedDocument (l_doc_type, to_number(p_auction_header_id)) = 'Y') then
1158 l_cont_attach_doc_flag := 'Y';
1159 else
1160 l_cont_attach_doc_flag := 'N';
1161 end if;
1162 end if;
1163
1164 -- If the Enforce previous round flag is set, then there may be a
1165 -- change to the way the start price is displayed
1166 -- Rules:
1167 -- Show start price from the negotiation if:
1168 -- : this is a buyer
1169 -- : this is a supplier who does not have a bid in the previous round
1170 -- If the supplier has a bid in the previous round and the flag is
1171 -- set, then
1172 -- Start price = Previous round active bid line price
1173 -- + effect of previous round supplier price factors
1174 -- + effect of current round buyer price factors
1175 -- Since it will not be possible or performant to determine this for
1176 -- every line, we will call a PL/SQL function. In order to save the
1177 -- function the trouble of determining information common to every
1178 -- row, we will derive the necessary parameters here and pass it in
1179
1180 l_start_price_from_prev_rnd := 'N';
1181
1182
1183 IF (l_is_buyer_negpdf = 'N') AND
1184 l_auction_header_id_prev_round IS NOT NULL AND
1185 l_enfrc_prevrnd_bid_price_flag = 'Y' AND
1186 p_trading_partner_id IS NOT NULL AND
1187 p_trading_partner_contact_id IS NOT NULL --{
1188 THEN
1189 -- Check if the supplier had an active bid in the previous round
1190
1191 BEGIN
1192 SELECT pbh.bid_number
1193 INTO l_prev_rnd_bid_number
1194 FROM pon_bid_headers pbh
1195 WHERE pbh.auction_header_id = l_auction_header_id_prev_round
1196 AND pbh.bid_status = 'ACTIVE'
1197 AND pbh.trading_partner_id = p_trading_partner_id
1198 AND pbh.trading_partner_contact_id = p_trading_partner_contact_id
1199 AND pbh.vendor_site_id = p_vendor_site_id;
1200
1201 l_start_price_from_prev_rnd := 'Y';
1202
1203 EXCEPTION
1204 WHEN NO_DATA_FOUND
1205 THEN
1206 l_start_price_from_prev_rnd := 'N';
1207
1208 END;
1209
1210 END IF; -- }
1211
1212 -- here the rules are checked to determine whether the commercial part is to be hidden
1213 -- check if it is a two-part RFQ, and that this is a bid pdf call.
1214 IF l_two_part_flag = 'Y' THEN -- {
1215 -- initially for two-part, need to show both attachment categories
1216 l_attach_categ_option := 3;
1217
1218 IF l_is_bidpdf = 'Y' THEN -- {
1219 -- make sure it is not the bidder himself
1220 IF p_trading_partner_id <> l_trading_partner_id THEN -- {
1221 -- is it commercially unlocked (buyer view) or unsealed (supplier view)?
1222 IF (p_user_view_type = 'BUYER' and l_commercial_lock_status <> 'LOCKED')
1223 or (p_user_view_type = 'SUPPLIER' and l_commercial_lock_status = 'ACTIVE') THEN -- {
1224
1225 -- since it is unlocked, check if bid is shortlisted
1226 IF l_tech_shortlist_flag <> 'Y' THEN -- {
1227 -- hide commercial part
1228 l_hide_comm_part := 'Y';
1229 -- show only technical attachments
1230 l_attach_categ_option := 2;
1231 END IF; -- }
1232 ELSE
1233 -- it is commercially locked, hide commercial part
1234 l_hide_comm_part := 'Y';
1235 -- show technical attachments
1236 l_attach_categ_option := 2;
1237 END IF; -- }
1238 END IF; -- }
1239 --added by Allen Yang for Surrogate Bid 2008/09/04
1240 --------------------------------------------------
1241 BEGIN
1242 OPEN tech_surrogate_bid_cur;
1243 FETCH
1244 tech_surrogate_bid_cur
1245 INTO
1246 l_tech_evaluation_status
1247 , l_surrogate_bid_flag;
1248 IF (l_tech_evaluation_status = 'NOT_COMPLETED' AND
1249 l_surrogate_bid_flag = 'Y' AND
1250 l_two_part_flag = 'Y')
1251 THEN
1252 l_hide_comm_part := 'Y';
1253 l_attach_categ_option :=2;
1254 END IF;
1255 CLOSE tech_surrogate_bid_cur;
1256 END;
1257 --------------------------------------------------
1258 END IF; -- }
1259 END IF; -- }
1260
1261 IF l_statement_log_level >= l_current_log_level THEN
1262 FND_LOG.string(l_statement_log_level, l_module_name, 'Two-Part related variables: l_hide_comm_part: '||l_hide_comm_part||'; l_attach_categ_option: '|| l_attach_categ_option);
1263 END IF;
1264
1265 -- Two-Part project (adsahay): fetch messages into variables, this is much more efficient than getting them in the query.
1266 l_two_part_general_msg := pon_printing_pkg.get_messages('PON_TWO_PART_INFO','TECHNICAL',pon_auction_pkg.get_technical_meaning, 'COMMERCIAL',pon_auction_pkg.get_commercial_meaning);
1267 l_two_part_tech_msg := pon_printing_pkg.get_messages('PON_TWO_PART_SECTION','PART',pon_auction_pkg.get_technical_meaning);
1268 l_two_part_comm_msg := pon_printing_pkg.get_messages('PON_TWO_PART_SECTION','PART',pon_auction_pkg.get_commercial_meaning);
1269
1270 OPEN xml_query_cursor FOR
1271 SELECT
1272 pah.auction_header_id,
1273 pah.auction_title,
1274 pah.auction_status,
1275 pah.auction_status_name,
1276 pah.auction_type,
1277 pah.contract_type,
1278 pah.trading_partner_contact_name,
1279 pah.trading_partner_contact_id,
1280 pah.trading_partner_name,
1281 pah.trading_partner_name_upper,
1282 nvl(pah.two_part_flag,'N') two_part_flag,
1283 l_hide_comm_part hide_comm_part,
1284 l_is_super_large_neg is_super_large_neg,
1285 pah.proxy_bidding_enabled_flag,
1286 PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pah.trading_partner_contact_id) auctioneer_display_name,
1287 pah.bill_to_location_id,
1288 bill_territories_tl.territory_short_name bill_country_name,
1289 loc_bill.location_code bill_address_name,
1290 loc_bill.address_line_1 bill_address1,
1291 loc_bill.address_line_2 bill_address2,
1292 loc_bill.address_line_3 bill_address3,
1293 loc_bill.town_or_city bill_city,
1294 loc_bill.region_2 bill_state,
1295 loc_bill.region_3 bill_province_or_region,
1296 loc_bill.postal_code bill_zip_code,
1297 loc_bill.postal_code bill_postal_code,
1298 loc_bill.country bill_country,
1299 loc_bill.region_1 bill_county,
1300 pah.ship_to_location_id,
1301 ship_territories_tl.territory_short_name ship_country_name,
1302 loc_ship.location_code ship_address_name,
1303 loc_ship.address_line_1 ship_address1,
1304 loc_ship.address_line_2 ship_address2,
1305 loc_ship.address_line_3 ship_address3,
1306 loc_ship.town_or_city ship_city,
1307 loc_ship.region_2 ship_state,
1308 loc_ship.region_3 ship_province_or_region,
1309 loc_ship.postal_code ship_zip_code,
1310 loc_ship.postal_code ship_postal_code,
1311 loc_ship.country ship_country,
1312 loc_ship.region_1 ship_county,
1313 entitytl.name entity,
1314 entity_loc.style entity_address_style,
1315 entity_loc.address_line_1 entity_address_line_1,
1316 entity_loc.address_line_2 entity_address_line_2,
1317 entity_loc.address_line_3 entity_address_line_3,
1318 entity_loc.town_or_city entity_city,
1319 entity_loc.postal_code entity_postal_code,
1320 nvl(entity_terr.territory_short_name, entity_loc.country) entity_country,
1321 entity_loc.country entity_country_code,
1322 entity_loc.region_1 entity_region_1,
1323 entity_loc.region_2 entity_region_2,
1324 entity_loc.region_3 entity_region_3,
1325 pon_oa_util_pkg.display_date_time(pah.open_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') open_bidding_date,
1326 pon_oa_util_pkg.display_date_time(pah.close_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') close_bidding_date,
1327 pon_oa_util_pkg.display_date_time(pah.original_close_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') original_close_bidding_date,
1328 pon_oa_util_pkg.display_date_time(pah.view_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') view_by_date,
1329 pon_oa_util_pkg.display_date_time(pah.award_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') award_by_date,
1330 pon_oa_util_pkg.display_date_time(pah.publish_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') publish_date,
1331 pon_oa_util_pkg.display_date_time(pah.close_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') close_date,
1332 pon_oa_util_pkg.display_date_time(pah.cancel_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') cancel_date,
1333 pah.time_zone,
1334 pon_auction_pkg.get_timezone_description(p_client_time_zone,l_printing_language) display_time_zone,
1335 pah.open_auction_now_flag,
1336 pah.publish_auction_now_flag,
1337 fl.meaning pon_bid_visibility_display,
1338 pah.bid_visibility_code,
1339 pah.bid_list_type,
1340 pah.bid_frequency_code,
1341 pah.bid_scope_code,
1342 pah.auto_extend_flag,
1343 pah.auto_extend_min_trigger_rank,
1344 pah.auto_extend_number,
1345 pah.auto_extend_enabled_flag,
1346 pah.number_of_extensions,
1347 pah.min_bid_decrement,
1348 decode(pah.min_bid_change_type, 'PERCENTAGE', pon_printing_pkg.format_number(pah.min_bid_decrement), pon_printing_pkg.format_price(pah.min_bid_decrement*l_rate, l_price_mask, l_price_precision)) min_bid_decrement_disp,
1349 pah.price_driven_auction_flag,
1350 pah.payment_terms_id,
1351 ap.name payment_terms,
1352 pah.freight_terms_code,
1353 fl_freight_terms.meaning freight_terms,
1354 pah.fob_code,
1355 fl_fob.meaning fob,
1356 pah.carrier_code,
1357 pah.currency_code,
1358 l_currency_code l_currency_code,
1359 pon_printing_pkg.get_carrier_description(pah.org_id,pah.carrier_code) carrier,
1360 currency_tl.name currency_name,
1361 -- bidpdf: whether this is for a bid pdf
1362 l_is_bidpdf is_bidpdf,
1363 l_price_visibility price_visibility,
1364 pah.rate_type,
1365 pah.rate_date,
1366 pah.rate,
1367 pah.note_to_bidders,
1368 pah.attachment_flag,
1369 pah.language_code,
1370 pah.auto_extend_all_lines_flag,
1371 pah.min_bid_increment,
1372 pah.allow_other_bid_currency_flag,
1373 pah.shipping_terms_code,
1374 pah.shipping_terms,
1375 pah.auto_extend_duration,
1376 pah.proxy_bid_allowed_flag,
1377 pah.publish_rates_to_bidders_flag,
1378 pah.attributes_exist,
1379 pah.order_number,
1380 pah.event_title,
1381 pah.sealed_auction_status,
1382 pah.sealed_actual_unlock_date,
1383 pah.sealed_actual_unseal_date,
1384 pah.mode_of_transport,
1385 pah.mode_of_transport_code,
1386 pon_oa_util_pkg.display_date(pah.po_start_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') po_start_date,
1387 pon_oa_util_pkg.display_date(pah.po_end_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') po_end_date,
1388 to_char(pah.po_agreed_amount*l_rate, l_amount_mask) po_agreed_amount,
1389 pah.min_bid_change_type,
1390 pah.full_quantity_bid_code,
1391 pah.number_price_decimals,
1392 pah.auto_extend_type_flag,
1393 pah.auction_origination_code,
1394 pah.multiple_rounds_flag,
1395 pah.auction_header_id_orig_round,
1396 pah.auction_header_id_prev_round,
1397 pah.auction_round_number,
1398 pah.manual_close_flag,
1399 pah.manual_extend_flag,
1400 pah.autoextend_changed_flag,
1401 pah.doctype_id,
1402 pah.approval_required_flag,
1403 pah.max_response_iterations,
1404 pah.payment_terms_neg_flag,
1405 pah.mode_of_transport_neg_flag,
1406 pah.contract_id,
1407 pah.contract_version_num,
1408 pah.show_bidder_notes,
1409 pah.derive_type,
1410 pah.bid_ranking,
1411 flbr.meaning bid_ranking_display,
1412 pah.rank_indicator,
1413 pah.show_bidder_scores,
1414 pah.org_id,
1415 pah.buyer_id,
1416 pah.has_pe_for_all_items,
1417 pah.has_price_elements,
1418 to_char(pah.po_min_rel_amount*l_rate, l_amount_mask) po_min_rel_amount,
1419 pah.global_agreement_flag,
1420 pah.document_number,
1421 pah.amendment_number ,
1422 pah.amendment_description ,
1423 pah.auction_header_id_orig_amend ,
1424 pah.auction_header_id_prev_amend ,
1425 pah.document_number ,
1426 pah.hdr_attr_enable_weights ,
1427 pah.hdr_attr_display_score ,
1428 pah.hdr_attr_maximum_score ,
1429 pah.attribute_line_number ,
1430 pah.conterms_exist_flag ,
1431 pah.award_mode ,
1432 pah.has_hdr_attr_flag ,
1433 nvl(pah.has_items_flag,'Y') has_items_flag,
1434 decode(pah.staggered_closing_interval, null, 'N', 'Y') staggered_closing_enabled,
1435 pah.staggered_closing_interval,
1436 pon_oa_util_pkg.display_date_time(pah.first_line_close_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') first_line_close_date,
1437 doctypes.internal_name,
1438 l_prev_rnd_doctype prev_rnd_internal_name,
1439 po_setup_s1.get_services_enabled_flag() is_services_enabled,
1440 l_contracts_installed as is_contracts_installed,
1441 p_printing_warning_flag print_warning_flag,
1442 l_cont_attach_doc_flag contract_attached_doc,
1443 l_cont_nonmerge_flag contract_non_mergeable,
1444 p_neg_printed_with_contracts neg_printed_with_contracts,
1445 pon_printing_pkg.get_messages('PON_AUCTS_START_CUR_PRICE','CURRENCY_CODE', l_currency_code) start_price_msg,
1446 pon_printing_pkg.get_messages('PON_AUCTS_TARGET_PRICE_CURR','CURRENCY_CODE', l_currency_code) target_price_msg,
1447 pon_printing_pkg.get_messages('PON_AUC_CURRENT_PRICE', 'AUCTION_CURRENCY', l_currency_code) current_price_msg,
1448 pon_printing_pkg.get_messages('PON_AUCTS_MIN_RELEASE_CURR','AUCTION_CURRENCY', l_currency_code) min_release_amt_msg,
1449 pon_printing_pkg.get_messages('PON_AUCTS_AGREEMENT_AMOUNT_CUR','CURRENCY', l_currency_code) agreement_amount_msg,
1450 pon_printing_pkg.get_messages('PON_MAX_RTNGE_AMT_WITH_CURR','AUCTION_CURRENCY', l_currency_code) max_retainage_amt_curr_msg,
1451 pon_printing_pkg.get_messages('PON_ADVANCE_AMT_WITH_CURR','AUCTION_CURRENCY', l_currency_code) advance_amount_curr_msg,
1452 pon_printing_pkg.get_messages('PON_ESTIMATED_TOTAL_AMT_CURR','CURRENCY_CODE', l_currency_code) estimated_amt_msg,
1453 pon_printing_pkg.get_messages('PON_AUC_PRN_LEGAL_CONSEQUENCES','LEGAL_ENTITY_NAME',entitytl.name) legal_consequences_msg,
1454 pon_printing_pkg.get_messages('PON_AUC_INTERVAL_MIN','MINUTES',pah.staggered_closing_interval) stagger_auc_interval_min,
1455 -- two-part project messages
1456 l_two_part_general_msg two_part_general_info_msg,
1457 l_two_part_tech_msg two_part_technical_msg,
1458 l_two_part_comm_msg two_part_commercial_msg,
1459 -- bidpdf: doc title and footer
1460 decode(l_is_bidpdf, 'Y',
1461 get_messages(pon_printing_pkg.get_document_message_name('PON_BID_PRN_PAGE_HEADING',doctypes.message_suffix),'DOCUMENT_NUMBER',pah.document_number,'BID_NUMBER',p_bid_number),
1462 pon_printing_pkg.get_messages(pon_printing_pkg.get_document_message_name('PON_AUCTS_PRN_PAGE_HEADING',doctypes.message_suffix),'DOCUMENT_NUMBER',pah.document_number)
1463 ) page_heading_msg,
1464 pbhs.bid_status,
1465 -- bidpdf: document type
1466 doctypes.doctype_group_name,
1467 -- bidpdf: response status
1468 fl_bid.meaning response_status,
1469 -- bidpdf: Response Valid Until
1470 decode(pbhs.bid_expiration_date, null, '', pon_oa_util_pkg.display_date(pbhs.bid_expiration_date, p_client_time_zone, p_server_time_zone, p_date_format,'N')) response_valid_until,
1471 -- bidpdf: supplier address
1472 l_supplier_address_line1 supplier_address_line1,
1473 l_supplier_address_line2 supplier_address_line2,
1474 l_supplier_address_line3 supplier_address_line3,
1475 l_supplier_address_city supplier_address_city,
1476 l_supplier_address_state supplier_address_state,
1477 l_supplier_postal_code supplier_postal_code,
1478 l_supplier_country_code supplier_country_code,
1479 l_supplier_country supplier_address_country,
1480 -- bidpdf: supplier site:
1481 pbhs.vendor_site_code,
1482 -- bidpdf: supplier contact name:
1483 l_contact_details_name contact_details_name,
1484 pbhs.bid_currency_code bid_currency_selected, --Response Currency
1485 pbhs.bidders_bid_number reference_number, --Reference Number
1486 pbhs.note_to_auction_owner note_to_buyer, --Note to Buyer
1487 --bidpdf: Response Received Time value
1488 pon_oa_util_pkg.display_date_time(pbhs.surrog_bid_receipt_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') bid_received_time,
1489 pbhs.surrog_bid_flag surrog_bid_flag, --Surrogate Bid Flag
1490 pon_printing_pkg.get_user_email(hp1.party_id) email,
1491 pah.abstract_details,
1492 fl_security.meaning security_level,
1493 pah.approval_status,
1494 ps.display_name outcome,
1495 nvl(gdct.description, gdct.user_conversion_type) rate_type_display,
1496 pon_oa_util_pkg.display_date(pah.rate_date,
1497 p_client_time_zone,
1498 p_server_time_zone,
1499 p_date_format, 'N') rate_date_display,
1500 pah.award_approval_flag,
1501 fl_rank_ind.meaning rank_indicator_display,
1502 pah.pf_type_allowed,
1503 fl_pf_type_allowed.meaning pf_type_allowed_display,
1504 pah.supplier_view_type,
1505 nvl2(pah.source_doc_msg_app, nvl2(pah.source_doc_msg, fnd_message.get_string(pah.source_doc_msg_app, pah.source_doc_msg), null), null) source_doc_msg_text,
1506 nvl2(pah.source_doc_msg_app, nvl2(pah.source_doc_line_msg, fnd_message.get_string(pah.source_doc_msg_app, pah.source_doc_line_msg), null), null) source_doc_msg_line_text,
1507 fpg.multi_org_flag,
1508 p_user_view_type as user_view_type,
1509 -- for bidpdf, the Company Name comes from pon_bid_headers.trading_partner_name
1510 decode(l_is_bidpdf, 'Y', pbhs.trading_partner_name,decode(p_trading_partner_id, null, p_requested_supplier_name, p_trading_partner_name)) as user_trading_partner_name,
1511 l_award_approval_enabled as award_approval_enabled,
1512 ns.style_name,
1513 pah.progress_payment_type,
1514 pah.advance_negotiable_flag,
1515 pah.recoupment_negotiable_flag,
1516 pah.progress_pymt_negotiable_flag,
1517 pah.retainage_negotiable_flag,
1518 pah.max_retainage_negotiable_flag,
1519 pah.supplier_enterable_pymt_flag,
1520 pah.project_id sourcing_project_id,
1521 pah.bid_decrement_method,
1522 proj.segment1 sourcing_project_number,
1523 DECODE(pah.contract_type, 'STANDARD', DECODE(progress_payment_type,'NONE','N','Y'),'N') complex_services_enabled,
1524 postyl.advances_flag,
1525 postyl.retainage_flag,
1526 postyl.progress_payment_flag,
1527 postyl.contract_financing_flag,
1528 NVL((SELECT pdsv.enabled_flag FROM po_doc_style_values pdsv WHERE pdsv.style_id = pah.po_style_id AND pdsv.style_attribute_name = 'PAY_ITEM_TYPES' AND pdsv.style_allowed_value = 'RATE'), 'N') rate_payments_allowed_flag,
1529 pon_auction_pkg.GetPAOUInstalled(pah.org_id) projects_installed_flag,
1530 pon_auction_pkg.GetGMSOUInstalled(pah.org_id) grants_installed_flag,
1531 pah.large_neg_enabled_flag,
1532 pah.team_scoring_enabled_flag,
1533 pah.has_scoring_teams_flag,
1534 NVL(pah.enforce_prevrnd_bid_price_flag, 'N') enforce_prevrnd_bid_price_flag,
1535 nvl(pah.DISPLAY_BEST_PRICE_BLIND_FLAG,'N') DISPLAY_BEST_PRICE_BLIND_FLAG,
1536 pah.neg_team_enabled_flag,
1537 pah.price_element_enabled_flag,
1538 buyer_phone.phone_number,
1539 buyer_fax.phone_number fax_number,
1540 -- bidpdf: Proxy response decrement
1541 decode(pah.min_bid_change_type, 'PERCENTAGE', pon_printing_pkg.format_number(pbhs.min_bid_change)||'%', pon_printing_pkg.format_price(pbhs.min_bid_change*l_rate, l_price_mask, l_price_precision)) min_bid_currency_change,
1542 pon_printing_pkg.get_messages('PON_AUCTS_CUR_PROXY_DEC','CURRENCY_CODE',l_currency_code) supplier_proxy_dec_msg,
1543 -- bidpdf: response total
1544 decode(p_user_view_type, 'BUYER', to_char(pbhs.buyer_bid_total, l_amount_mask),
1545 to_char(get_supplier_bid_total(pah.auction_header_id, pbhs.bid_number, pbhs.buyer_bid_total, pah.contract_type, doctypes.doctype_group_name,pbhs.bid_status), l_amount_mask)
1546 ) supplier_bid_total,
1547 pon_printing_pkg.get_messages('PON_BID_CUR_TOTAL','CURRENCY_CODE', l_currency_code) supplier_response_total_msg,
1548 pah.price_tiers_indicator,
1549
1550 cursor (
1551 Select
1552 paip.item_number|| nvl2(paip.item_revision, ', ', '') || paip.item_revision || jobs.name item,
1553 paip.line_number,
1554 paip.item_description,
1555 pon_oa_util_pkg.display_date_time(paip.close_bidding_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') line_close_bidding_date,
1556 paip.category_id,
1557 paip.category_name,
1558 paip.ip_category_id,
1559 icx.category_name ip_category_name,
1560 paip.uom_code,
1561 units.unit_of_measure_tl,
1562 pon_printing_pkg.format_number(paip.quantity) quantity,
1563 -- bidpdf: Note to Buyer
1564 pbip.note_to_auction_owner,
1565 -- bidpdf: add bid price info
1566 decode(p_user_view_type, 'BUYER', pon_printing_pkg.format_price(pbip.price, l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbip.bid_currency_price, l_price_mask, l_price_precision)) bid_currency_price,
1567 pon_printing_pkg.format_number(pbip.quantity) bid_quantity,
1568 pon_oa_util_pkg.display_date_time(pbip.promised_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') bid_promised_date,
1569 --in MAS case, pbip.quantity is null, use paip.quantity instead
1570 to_char(decode(p_user_view_type, 'BUYER',pbip.price, pbip.bid_currency_price)*decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,decode(pah.contract_type, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)), l_amount_mask) bid_amount,
1571 --response/inquiry/server/ViewBidItemsVORowImpl.java:getBidTotalDisplay(): exchange_rate * PON_TRANSFORM_BIDDING_PKG.calculate_quote_amount (paip.auction_header_id, pbip.line_number, pbip.bid_number, 'TRANSFORMED', 12637, 12438, -1) bid_amount,
1572 --bidpdf: Bid Minimum Release Amount
1573 decode(p_user_view_type, 'BUYER', to_char(pbip.po_min_rel_amount, l_amount_mask), to_char(pbip.po_bid_min_rel_amount, l_amount_mask)) bid_min_rel_amount,
1574 --bidpdf: MAS Score
1575 pbip.total_weighted_score,
1576 --bidpdf: Proxy Minimum
1577 decode(p_user_view_type, 'BUYER', pon_printing_pkg.format_price(pbip.proxy_bid_limit_price, l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbip.bid_currency_limit_price, l_price_mask, l_price_precision)) bid_currency_limit_price,
1578 paip.ship_to_location_id,
1579
1580 pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(pah.auction_header_id,
1581 paip.line_number, paip.target_price*l_rate, paip.quantity, p_trading_partner_id,
1582 p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),l_price_mask, l_price_precision)
1583 target_price,
1584 -- Start price comes from the earlier bid for a supplier if he had bid
1585 -- on the earlier round for the line and if the control for enforcing
1586 -- previous round start price is set. If he did not bid on the line or
1587 -- if it is buyer or other supplier, then we fall back upon the
1588 -- auction start price
1589 --untransform_one_price
1590
1591 DECODE(l_is_supplier_bidpdf, 'Y',
1592 pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, paip.line_number,
1593 nvl(pbip.bid_start_price, paip.bid_start_price)*l_rate, paip.quantity,
1594 p_trading_partner_id,
1595 p_trading_partner_contact_id,
1596 p_vendor_site_id,
1597 p_requested_supplier_id),l_price_mask, l_price_precision),
1598 DECODE(l_start_price_from_prev_rnd, 'N',
1599 pon_printing_pkg.format_price(
1600 pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, paip.line_number, paip.bid_start_price, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),
1601 l_price_mask, l_price_precision),
1602 pon_printing_pkg.format_price(
1603 NVL(pon_auction_headers_pkg.apply_price_factors(p_auction_header_id ,l_prev_rnd_bid_number,paip.line_number, l_contract_type, l_supplier_view_type, l_pf_type_allowed, 'Y'),paip.bid_start_price),
1604 l_price_mask, l_price_precision)
1605 )
1606 ) bid_start_price,
1607
1608 paip.note_to_bidders,
1609 paip.display_target_price_flag,
1610 paip.type,
1611 to_char(paip.po_min_rel_amount*l_rate, l_amount_mask) po_min_rel_amount,
1612 paip.unit_of_measure,
1613 paip.has_attributes_flag,
1614 paip.org_id,
1615 paip.has_price_elements_flag,
1616 paip.line_type_id,
1617 paip.order_type_lookup_code,
1618 paip.item_revision,
1619 paip.item_id,
1620 paip.item_number,
1621 paip.price_break_type,
1622 paip.price_break_neg_flag,
1623 paip.has_shipments_flag,
1624 paip.price_disabled_flag,
1625 paip.quantity_disabled_flag,
1626 paip.disp_line_number,
1627 paip.is_quantity_scored,
1628 paip.is_need_by_date_scored,
1629 paip.job_id,
1630 paip.additional_job_details,
1631 to_char(paip.po_agreed_amount*l_rate, l_amount_mask) po_agreed_amount,
1632 paip.has_price_differentials_flag,
1633 paip.price_diff_shipment_number,
1634 paip.differential_response_type,
1635 paip.purchase_basis,
1636 pon_auction_pkg.getNeedByDatesToPrint(paip.auction_header_id,paip.line_number,p_date_format) as need_by_dates_to_print,
1637 paip.document_disp_line_number,
1638 paip.group_type,
1639 decode(paip.parent_line_number, null,to_char(null),(select paip2.item_description from pon_auction_item_prices_all paip2 where paip2.auction_header_id = paip.auction_header_id and paip2.line_number = paip.parent_line_number)) parent_line_description,
1640 tl.territory_short_name country_name,
1641 hl.location_code address_name,
1642 hl.address_line_1 address1,
1643 hl.address_line_2 address2,
1644 hl.address_line_3 address3,
1645 hl.town_or_city city,
1646 hl.region_2 state,
1647 hl.region_3 province_or_region,
1648 hl.postal_code zip_code,
1649 hl.postal_code postal_code,
1650 hl.country country,
1651 hl.region_1 county,
1652 paip.requisition_number,
1653 paip.line_origination_code,
1654 nvl2(paip.source_doc_number, paip.source_doc_number || nvl2(paip.source_line_number, ' / ' || paip.source_line_number, null), null) source_doc_line_display,
1655 lt.line_type,
1656 pon_printing_pkg.format_price(paip.current_price, l_price_mask, l_price_precision) current_price,
1657 pon_printing_pkg.format_price(paip.unit_target_price, l_price_mask, l_price_precision) unit_target_price,
1658 paip.unit_display_target_flag
1659 ,paip.has_payments_flag
1660 ,to_char(paip.advance_amount*l_rate, l_amount_mask) advance_amount
1661 ,decode(p_user_view_type, 'BUYER', to_char(pbip.advance_amount, l_amount_mask), to_char(pbip.bid_curr_advance_amount, l_amount_mask)) bid_advance_amount
1662 --bidpdf:remove "," after paip.recoupment_rate_percent and paip.progress_pymt_rate_percent
1663 ,paip.recoupment_rate_percent recoupment_rate_percent
1664 ,pbip.recoupment_rate_percent bid_recoupment_rate_percent
1665 ,paip.progress_pymt_rate_percent progress_pymt_rate_percent
1666 ,pbip.progress_pymt_rate_percent bid_progress_pymt_rate_percent
1667 ,paip.retainage_rate_percent retainage_rate_percent
1668 ,pbip.retainage_rate_percent bid_retainage_rate_percent
1669 ,to_char(paip.max_retainage_amount*l_rate, l_amount_mask) max_retainage_amount
1670 ,decode(p_user_view_type, 'BUYER', to_char(pbip.max_retainage_amount, l_amount_mask), to_char(pbip.bid_curr_max_retainage_amt, l_amount_mask)) bid_curr_max_retainage_amt
1671 ,paip.project_id project_id
1672 ,proj.segment1 project_number
1673 ,paip.project_task_id project_task_id
1674 ,task.task_number project_task_number
1675 ,paip.project_award_id project_award_id
1676 ,awrd.award_number project_award_number
1677 ,paip.project_expenditure_type project_expenditure_type
1678 ,paip.project_exp_organization_id project_exp_organization_id
1679 ,hrorg.name project_exp_organization_name
1680 ,pon_oa_util_pkg.display_date(paip.project_expenditure_item_date, p_client_time_zone,
1681 p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
1682 ,NVL2(paip.work_approver_user_id, (SELECT per.full_name
1683 FROM per_all_people_f per
1684 WHERE per.person_id = fuser.employee_id
1685 AND per.effective_end_date =
1686 (SELECT MAX(per1.effective_end_date)
1687 FROM per_all_people_f per1
1688 WHERE per.person_id = per1.person_id)
1689 ), NULL) work_approver_name
1690 ,paip.has_quantity_tiers negline_has_quantity_tiers
1691 ,pbip.has_quantity_tiers bidline_has_quantity_tiers
1692 from
1693 pon_auction_item_prices_all paip ,
1694 hr_locations_all hl,
1695 fnd_territories_tl tl,
1696 per_jobs_vl jobs,
1697 icx_cat_categories_v icx,
1698 mtl_units_of_measure_tl units,
1699 po_line_types_tl lt
1700 ,pa_projects_all proj
1701 ,pa_tasks task
1702 ,gms_awards_all awrd
1703 ,hr_all_organization_units hrorg
1704 ,fnd_user fuser
1705 ,pon_bid_item_prices pbip
1706 where
1707 paip.auction_header_id = pah.auction_header_id
1708 and pbip.auction_header_id(+) = paip.auction_header_id
1709 and pbip.bid_number(+) = p_bid_number
1710 and pbip.line_number(+)=paip.line_number
1711 and hl.location_id(+) = paip.ship_to_location_id
1712 and tl.territory_code(+) = hl.country
1713 and tl.language(+) = l_printing_language
1714 and hl.ship_to_site_flag(+)='Y'
1715 and sysdate < nvl(hl.inactive_date(+), sysdate + 1)
1716 and paip.uom_code = units.uom_code(+)
1717 and units.language(+) = l_printing_language
1718 and jobs.job_id(+) = paip.job_id
1719 and paip.ip_category_id = icx.rt_category_id(+)
1720 and icx.language(+) = l_printing_language
1721 and lt.line_type_id(+) = paip.line_type_id
1722 and lt.language(+) = l_printing_language
1723 and nvl(hl.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
1724 = nvl(hr_general.get_business_group_id, nvl(hl.business_group_id(+), -99))
1725 and (l_is_buyer_negpdf = 'Y'
1726 or
1727 (not exists (select 'x'
1728 from pon_bidding_parties bp
1729 where bp.auction_header_id = paip.auction_header_id
1730 and ((bp.trading_partner_id = l_trading_partner_id
1731 and bp.vendor_site_id = p_vendor_site_id)
1732 OR bp.requested_supplier_id = p_requested_supplier_id)
1733 and bp.access_type = 'RESTRICTED')
1734 or
1735 nvl(paip.parent_line_number, paip.line_number) not in (
1736 select line_number
1737 from pon_party_line_exclusions pple
1738 where pple.auction_header_id = paip.auction_header_id
1739 and ((pple.trading_partner_id = l_trading_partner_id
1740 and pple.vendor_site_id = p_vendor_site_id)
1741 OR pple.requested_supplier_id = p_requested_supplier_id))))
1742 AND paip.project_id = proj.project_id(+)
1743 AND paip.project_task_id = task.task_id(+)
1744 AND paip.project_award_id = awrd.award_id(+)
1745 AND paip.project_exp_organization_id = hrorg.organization_id(+)
1746 AND paip.work_approver_user_id = fuser.user_id(+)
1747 order by paip.disp_line_number) as LINES,
1748 cursor (
1749 SELECT
1750 TM.AUCTION_HEADER_ID,
1751 P.full_name,
1752 S.NAME position_name,
1753 tm.approver_flag,
1754 tm.menu_name,
1755 flkp.meaning member_access_type,
1756 tm.task_name,
1757 pon_oa_util_pkg.display_date(tm.target_date,
1758 p_client_time_zone,
1759 p_server_time_zone,
1760 p_date_format, 'N') target_date
1761 FROM
1762 PON_NEG_TEAM_MEMBERS TM,
1763 FND_USER U,
1764 PER_ALL_PEOPLE_F P,
1765 PER_ALL_ASSIGNMENTS_F A,
1766 PER_ALL_POSITIONS S,
1767 FND_LOOKUPS flkp
1768 WHERE
1769 TM.AUCTION_HEADER_ID = pah.auction_header_id
1770 AND l_is_buyer_negpdf = 'Y'
1771 AND pah.neg_team_enabled_flag = 'Y'
1772 AND TM.LAST_AMENDMENT_UPDATE <= pah.amendment_number
1773 AND tm.menu_name = flkp.lookup_code
1774 AND flkp.lookup_type = 'PON_NEG_TEAM_MEMBER_ACCESS'
1775 AND U.USER_ID = TM.USER_ID
1776 AND U.EMPLOYEE_ID = P.PERSON_ID
1777 AND P.EFFECTIVE_END_DATE =
1778 (SELECT MAX(PP.EFFECTIVE_END_DATE)
1779 FROM PER_ALL_PEOPLE_F PP
1780 WHERE PP.PERSON_ID = U.EMPLOYEE_ID)
1781 AND A.PERSON_ID = P.PERSON_ID
1782 AND A.PRIMARY_FLAG = 'Y'
1783 AND ((A.ASSIGNMENT_TYPE = 'E' AND P.CURRENT_EMPLOYEE_FLAG = 'Y')
1784 OR
1785 (A.ASSIGNMENT_TYPE = 'C' AND P.CURRENT_NPW_FLAG = 'Y'))
1786 AND A.EFFECTIVE_END_DATE =
1787 (SELECT MAX(AA.EFFECTIVE_END_DATE)
1788 FROM PER_ALL_ASSIGNMENTS_F AA
1789 WHERE AA.PRIMARY_FLAG = 'Y'
1790 AND AA.ASSIGNMENT_TYPE in ('E', 'C')
1791 AND AA.PERSON_ID = P.PERSON_ID)
1792 AND A.POSITION_ID = S.POSITION_ID(+)
1793 AND TM.AUCTION_HEADER_ID = pah.auction_header_id
1794 AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
1795 ORDER BY P.FULL_NAME, U.USER_NAME) as COLLABORATION_TEAM,
1796 CURSOR (
1797 SELECT sctm.team_id
1798 ,sctm.team_name
1799 ,sctm.price_visible_flag
1800 ,sctm.instruction_text
1801 FROM pon_scoring_teams sctm
1802 WHERE sctm.auction_header_id = pah.auction_header_id
1803 AND pah.has_scoring_teams_flag = 'Y' -- teams present only if flag present
1804 ORDER BY sctm.team_name
1805 ) AS SCORING_TEAMS,
1806 CURSOR (
1807 SELECT DISTINCT -- Distinct added because sometimes an employee may have
1808 -- multipler user ids resulting in more rows being returned
1809 stmem.team_id
1810 ,stmem.user_id
1811 ,per.full_name member_name
1812 FROM pon_scoring_team_members stmem
1813 ,fnd_user fuser
1814 ,per_all_people_f per
1815 WHERE stmem.auction_header_id = pah.auction_header_id
1816 AND stmem.user_id = fuser.user_id
1817 AND fuser.employee_id = per.person_id
1818 AND pah.has_scoring_teams_flag = 'Y' -- members present only if teams present
1819 AND per.effective_end_date = (select max(pp.effective_end_date) from per_all_people_f pp where pp.person_id = per.person_id)
1820 ) AS SCORING_TEAM_MEMBERS,
1821 CURSOR (
1822 SELECT
1823 team_sections.section_id
1824 ,sections.section_name
1825 ,team_sections.auction_header_id
1826 ,team_sections.team_id
1827 FROM pon_scoring_team_sections team_sections
1828 ,pon_auction_sections sections
1829 WHERE team_sections.auction_header_id = pah.auction_header_id
1830 AND sections.section_id = team_sections.section_id
1831 AND sections.auction_header_id = team_sections.auction_header_id
1832 AND pah.has_scoring_teams_flag = 'Y' -- sections present only if teams present
1833 ) AS SCORING_TEAM_SECTIONS,
1834 CURSOR (
1835 SELECT
1836 forms_tl.form_name,
1837 forms.form_version,
1838 forms.form_id,
1839 forms.form_code
1840 FROM
1841 pon_forms_instances form_instances,
1842 pon_forms_sections forms,
1843 pon_forms_sections_tl forms_tl
1844 WHERE
1845 form_instances.entity_code = 'PON_AUCTION_HEADERS_ALL'
1846 AND form_instances.entity_pk1 = TO_CHAR(pah.auction_header_id)
1847 AND l_is_buyer_negpdf = 'Y'
1848 AND forms_tl.language = l_printing_language
1849 AND form_instances.form_id = forms.form_id
1850 AND forms.form_id = forms_tl.form_id
1851 ORDER BY form_name) AS ABSTRACT_AND_FORMS,
1852 cursor (
1853 select
1854 pacr.bid_currency_code,
1855 ftl.name bid_currency_name,
1856 pacr.number_price_decimals,
1857 pon_printing_pkg.get_display_rate(pacr.rate_dsp,pah.rate_type,pah.rate_date,pah.currency_code,bid_currency_code) display_rate
1858 from
1859 pon_auction_currency_rates pacr ,
1860 fnd_currencies_tl ftl
1861 where
1862 pacr.auction_header_id = pah.auction_header_id
1863 and ftl.currency_code = pacr.bid_currency_code
1864 and ftl.language = l_printing_language)
1865 as CURRENCY,
1866 CURSOR(
1867 SELECT
1868 pbp.bid_currency_code,
1869 ftl.name bid_currency_name,
1870 pbp.number_price_decimals,
1871 nvl2(pbp.rate_dsp, pon_printing_pkg.format_number(pbp.rate_dsp), null) as display_rate
1872 FROM
1873 pon_bidding_parties pbp,
1874 fnd_currencies_tl ftl
1875 WHERE
1876 pbp.auction_header_id = pah.auction_header_id
1877 AND (l_is_buyer_negpdf = 'N')
1878 AND ftl.currency_code = pbp.bid_currency_code
1879 AND ftl.language = l_printing_language
1880 AND ((pbp.trading_partner_id = l_trading_partner_id
1881 AND pbp.vendor_site_id = p_vendor_site_id)
1882 OR pbp.requested_supplier_id = p_requested_supplier_id)
1883 ORDER BY sequence asc) AS INVITED_SUPPLIER_CURRENCY,
1884 cursor (
1885 select sum(nvl(paa.weight,0)) weight,
1886 sum(nvl(paa.attr_max_score,0)) score,
1887 pass.section_name,
1888 nvl(pass.two_part_section_type,'') two_part_section_type,
1889 cursor (select
1890 pa.auction_header_id,
1891 pa.line_number,
1892 pa.attribute_name as header_attribute_name,
1893 pa.description,
1894 pa.datatype,
1895 pa.mandatory_flag,
1896 print_attribute_target_value(pa.display_target_flag, pa.value, pa.datatype,10, p_client_time_zone, p_server_time_zone, p_date_format, p_user_view_type) value,
1897 pa.display_prompt,
1898 pa.display_target_flag,
1899 pa.display_only_flag,
1900 pa.sequence_number,
1901 nvl(pa.weight,0) weight,
1902 pa.scoring_type,
1903 pa.attr_level,
1904 pa.attr_group,
1905 pa.attr_max_score,
1906 pa.internal_attr_flag,
1907 pa.attr_group_seq_number,
1908 pa.attr_disp_seq_number,
1909 pa.knockout_score,
1910 pa.scoring_method,
1911 print_attribute_response_value(pbav.value, pbav.datatype, p_client_time_zone, p_server_time_zone, p_date_format, pbav.sequence_number) attribute_bid_value,
1912 pbav.score attribute_bid_score,
1913 cursor( select
1914 pas.auction_header_id,
1915 pas.line_number,
1916 pas.attribute_sequence_number,
1917 pas.value,
1918 pas.from_range,
1919 pas.to_range,
1920 pas.score,
1921 pas.sequence_number,
1922 pon_printing_pkg.get_acceptable_value(pah.HDR_ATTR_DISPLAY_SCORE,pas.attribute_sequence_number,pa.datatype,pas.from_range,pas.to_range,pas.value,pas.score, p_client_time_zone, p_server_time_zone, p_date_format, l_is_buyer_negpdf) display_score
1923 from
1924 pon_attribute_scores pas
1925 where
1926 pas.auction_header_id = pa.auction_header_id
1927 and pas.line_number = -1
1928 and pas.attribute_sequence_number = pa.sequence_number
1929 order by pas.attribute_sequence_number,pas.sequence_number
1930 ) as HEADER_ATTRIBUTE_SCORES
1931 from
1932 pon_auction_attributes pa, pon_bid_attribute_values pbav
1933 where
1934 pa.auction_header_id = paa.auction_header_id
1935 --bidpdf: add bid values for attributes from table pon_bid_attribute_values
1936 -- The table has index on bid_number, line_number, sequence_number
1937 and pbav.auction_header_id(+) = pa.auction_header_id
1938 and pbav.bid_number(+) = p_bid_number
1939 and pbav.line_number(+) = pa.line_number
1940 and pbav.sequence_number(+) = pa.sequence_number
1941 and pa.section_name = paa.section_name
1942 and pa.line_number = -1
1943 and pa.attr_level='HEADER'
1944 and (l_is_buyer_negpdf = 'Y' or pa.internal_attr_flag <> 'Y')
1945 order by pa.attr_disp_seq_number) as HEADER_ATTRIBUTES_DETAILS
1946 from
1947 pon_auction_attributes paa,pon_auction_sections pass
1948 where
1949 pass.auction_header_id = pah.auction_header_id
1950 and pass.auction_header_id = paa.auction_header_id(+)
1951 and pass.section_name = paa.section_name(+)
1952 and paa.attr_level(+)='HEADER'
1953 and paa.line_number(+) = -1
1954 and (l_is_buyer_negpdf = 'Y' or paa.internal_attr_flag <> 'Y')
1955 and (l_is_section_restricted = 'N'
1956 or l_is_section_restricted = 'Y'
1957 and paa.attr_group_seq_number in (
1958 select pas.attr_group_seq_number
1959 from pon_scoring_team_members pstm, pon_scoring_team_sections psts, pon_auction_sections pas
1960 where pstm.auction_header_id = pah.auction_header_id
1961 and pstm.user_id = p_user_id
1962 and psts.auction_header_id = pstm.auction_header_id
1963 and psts.team_id = pstm.team_id
1964 and psts.section_id = pas.section_id
1965 and psts.auction_header_id = pas.auction_header_id
1966 )
1967 )
1968 and ((l_hide_comm_part = 'Y' and pass.two_part_section_type = 'TECHNICAL') or l_hide_comm_part <> 'Y')
1969 group by(pass.section_name,paa.auction_header_id,paa.section_name,pass.auction_header_id,pass.attr_group_seq_number, two_part_section_type)
1970 order by pass.attr_group_seq_number) as GROUP_HEADER_ATTRIBUTES ,
1971 CURSOR(
1972 SELECT
1973 decode(pbp.trading_partner_id, null, pbp.requested_supplier_name, pbp.trading_partner_name) trading_partner_name,
1974 pbp.vendor_site_code,
1975 decode(pbp.trading_partner_contact_id, null, pbp.requested_supp_contact_name, PON_LOCALE_PKG.get_party_display_name(pbp.trading_partner_contact_id)) contact_name,
1976 pbp.additional_contact_email,
1977 pbp.bid_currency_code,
1978 pbp.rate_dsp,
1979 nvl2(pbp.rate_dsp, pon_printing_pkg.format_number(pbp.rate_dsp), null) as rate_dsp_display,
1980 pbp.number_price_decimals,
1981 pbp.access_type,
1982 pbp.auction_header_id,
1983 pbp.trading_partner_id,
1984 pbp.trading_partner_contact_id,
1985 pbp.sequence
1986 FROM pon_bidding_parties pbp
1987 WHERE
1988 pbp.auction_header_id = pah.auction_header_id
1989 AND l_is_buyer_negpdf = 'Y'
1990 ORDER BY sequence asc) AS INVITED_SUPPLIERS,
1991 cursor (
1992 select
1993 attrGrpFlv.meaning,
1994 pal.auction_header_id,
1995 pal.line_number,
1996 pal.attribute_name,
1997 pal.description,
1998 pal.datatype,
1999 pal.mandatory_flag,
2000 print_attribute_target_value(pal.display_target_flag, pal.value, pal.datatype,pal.sequence_number, p_client_time_zone, p_server_time_zone, p_date_format, p_user_view_type) value,
2001 pal.display_prompt,
2002 pal.display_target_flag,
2003 pal.display_only_flag,
2004 pal.sequence_number,
2005 pal.weight,
2006 pal.scoring_type,
2007 NVL(pal.attr_level,'LINE') attr_level,
2008 NVL(pal.attr_group,'GENERAL') attr_group,
2009 pal.attr_max_score,
2010 pal.internal_attr_flag,
2011 NVL(pal.attr_group_seq_number,10) attr_group_seq_number,
2012 pal.attr_disp_seq_number,
2013 -- bidpdf: add attribute response value
2014 print_attribute_response_value(pbav.value, pbav.datatype, p_client_time_zone, p_server_time_zone, p_date_format, pbav.sequence_number) attr_bid_value
2015 from
2016 pon_auction_attributes pal,
2017 pon_bid_attribute_values pbav,
2018 fnd_lookups attrGrpFlv
2019 where
2020 pal.auction_header_id = pah.auction_header_id
2021 and pbav.auction_header_id(+) = pal.auction_header_id
2022 and pbav.bid_number(+) = pbhs.bid_number
2023 and pbav.line_number(+) = pal.line_number
2024 and pbav.sequence_number(+) = pal.sequence_number
2025 and NVL(pal.attr_group,'GENERAL') = attrGrpFlv.lookup_code
2026 and NVL(pal.attr_level,'LINE')='LINE'
2027 and NVL(pal.internal_attr_flag,'N') <> 'Y'
2028 and attrGrpFlv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS'
2029 and attrGrpFlv.enabled_flag = 'Y'
2030 and nvl(attrGrpFlv.start_date_active,sysdate) <= sysdate
2031 and nvl(attrGrpFlv.end_date_active,sysdate) > sysdate-1
2032 order by pal.line_number,NVL(pal.attr_group_seq_number,10),pal.attr_disp_seq_number) as LINE_ATTRIBUTES,
2033 cursor( select
2034 pas.auction_header_id,
2035 pas.line_number,
2036 pas.attribute_sequence_number,
2037 pas.value,
2038 pas.from_range,
2039 pas.to_range,
2040 pas.score,
2041 pas.sequence_number,
2042 get_acceptable_value(pah.show_bidder_scores,pas.attribute_sequence_number,paa.datatype,pas.from_range,pas.to_range,pas.value,pas.score, p_client_time_zone, p_server_time_zone, p_date_format, l_is_buyer_negpdf) display_score
2043 from
2044 pon_attribute_scores pas,
2045 pon_auction_attributes paa
2046 where
2047 pas.auction_header_id = pah.auction_header_id
2048 and paa.auction_header_id = pah.auction_header_id
2049 and paa.line_number = pas.line_number
2050 and paa.sequence_number = pas.attribute_sequence_number
2051 and NVL(paa.attr_level,'LINE')='LINE'
2052 order by pas.line_number,pas.attribute_sequence_number,pas.sequence_number
2053 ) as LINE_ATTRIBUTE_SCORES,
2054 CURSOR (
2055 SELECT
2056 pe.auction_header_id,
2057 pe.line_number,
2058 pet.name,
2059 pe.pricing_basis,
2060 flv.meaning pricing_basis_display,
2061 pe.value value,
2062 --only in supplier bid pdf, the target value is in supplier currency and number format
2063 --in neg pdf and buyer side bid pdf, the target value is in buyer currency
2064 nvl2(pe.value, decode(pe.pricing_basis, 'PER_UNIT', pon_printing_pkg.format_price(pe.value*l_rate, l_price_mask, l_price_precision) ||' ('||l_currency_code||')',
2065 'FIXED_AMOUNT', to_char(pe.value*l_rate, l_amount_mask) ||' ('||l_currency_code||')',
2066 pon_printing_pkg.format_number(pe.value)),
2067 null) target_value_display,
2068 -- bidpdf: response value
2069 nvl2(pbpe.bid_currency_value,
2070 decode(pe.pricing_basis,
2071 'PER_UNIT', decode(p_user_view_type,
2072 'BUYER', pon_printing_pkg.format_price(pbpe.auction_currency_value, l_price_mask, l_price_precision)||' ('||l_currency_code||')',
2073 pon_printing_pkg.format_price(pbpe.bid_currency_value, l_price_mask, l_price_precision)||' ('||l_currency_code||')'),
2074 'FIXED_AMOUNT', decode(p_user_view_type, 'BUYER',to_char(pbpe.auction_currency_value, l_amount_mask)||' ('||l_currency_code||')',to_char(pbpe.bid_currency_value, l_amount_mask)||' ('||l_currency_code||')'),
2075 pon_printing_pkg.format_number(pbpe.bid_currency_value)),
2076 null) bid_value_display,
2077 pe.price_element_type_id,
2078 pe.sequence_number,
2079 pe.display_target_flag,
2080 pet.description,
2081 pe.pf_type,
2082 pe.display_to_suppliers_flag,
2083 flv2.meaning pf_type_display,
2084 --only in supplier bid pdf, the buyer response value is in supplier currency and number format
2085 --in neg pdf and buyer side bid pdf, the buyer response value is in buyer currency
2086 nvl2(pf_values.value, decode(pe.pf_type, 'BUYER', decode(pe.pricing_basis, 'PER_UNIT', pon_printing_pkg.format_price(pf_values.value*l_rate, l_price_mask, l_price_precision)||' ('||l_currency_code||')',
2087 'FIXED_AMOUNT', to_char(pf_values.value*l_rate, l_amount_mask)||' ('||l_currency_code||')',
2088 pon_printing_pkg.format_number(pf_values.value)),
2089 null),
2090 null) buyer_pf_value_display,
2091 decode(pah.trading_partner_id,
2092 p_trading_partner_id, 'Y',
2093 decode(pe.pf_type,
2094 'SUPPLIER', 'Y',
2095 decode(pe.display_to_suppliers_flag,
2096 'N', 'N',
2097 PON_TRANSFORM_BIDDING_PKG.has_pf_values_defined(pe.auction_header_id, pe.line_number, pe.sequence_number, p_trading_partner_id, p_vendor_site_id, p_requested_supplier_id)))) can_view_pf_flag
2098 FROM
2099 pon_price_elements pe,
2100 pon_price_element_types_tl pet,
2101 pon_auction_item_prices_all itm,
2102 fnd_lookup_values flv,
2103 fnd_lookup_values flv2,
2104 pon_pf_supplier_values pf_values,
2105 -- bidpdf: add bid value for cost factor
2106 pon_bid_price_elements pbpe
2107 WHERE
2108 pe.auction_header_id = pah.auction_header_id
2109 AND pbpe.auction_header_id(+) = pe.auction_header_id
2110 AND pbpe.bid_number(+) = pbhs.bid_number
2111 AND pbpe.line_number(+) = pe.line_number
2112 AND pbpe.price_element_type_id(+) = pe.price_element_type_id
2113 AND itm.auction_header_id = pe.auction_header_id
2114 AND itm.line_number = pe.line_number
2115 AND pe.price_element_type_id = pet.price_element_type_id
2116 AND pet.language = l_printing_language
2117 AND flv.lookup_type = 'PON_PRICING_BASIS'
2118 AND flv.language = l_printing_language
2119 AND flv.lookup_code = pe.pricing_basis
2120 AND flv.view_application_id = 0
2121 AND flv.security_group_id = 0
2122 AND flv2.lookup_type = 'PON_PRICE_FACTOR_TYPE'
2123 AND flv2.language = l_printing_language
2124 AND flv2.lookup_code = pe.pf_type
2125 AND flv2.view_application_id = 0
2126 AND flv2.security_group_id = 0
2127 AND decode(pe.price_element_type_id, -10, itm.has_price_elements_flag, 'Y') = 'Y'
2128 AND pf_values.auction_header_id(+) = pe.auction_header_id
2129 AND pf_values.line_number(+) = pe.line_number
2130 AND pf_values.pf_seq_number(+) = pe.sequence_number
2131 AND pf_values.supplier_seq_number(+) = l_supplier_sequence_number
2132 order by pe.line_number, pe.sequence_number asc) AS PRICE_FACTORS,
2133 CURSOR (
2134 SELECT
2135 pet.name,
2136 flv.meaning pricing_basis_display,
2137 pet.description,
2138 flv2.meaning pf_type_display
2139 FROM
2140 pon_price_element_types_tl pet,
2141 fnd_lookup_values flv,
2142 fnd_lookup_values flv2
2143 WHERE
2144 pet.language = l_printing_language
2145 AND pet.price_element_type_id = -10
2146 AND flv.lookup_type = 'PON_PRICING_BASIS'
2147 AND flv.language = l_printing_language
2148 AND flv.lookup_code = 'PER_UNIT'
2149 AND flv.view_application_id = 0
2150 AND flv.security_group_id = 0
2151 AND flv2.lookup_type = 'PON_PRICE_FACTOR_TYPE'
2152 AND flv2.language = l_printing_language
2153 AND flv2.lookup_code = 'SUPPLIER'
2154 AND flv2.view_application_id = 0
2155 AND flv2.security_group_id = 0
2156 ) AS LINE_PRICE_PF_DETAILS,
2157 CURSOR (
2158 SELECT
2159 pf_values.auction_header_id,
2160 pf_values.line_number,
2161 pf_values.pf_seq_number,
2162 pf_values.supplier_seq_number,
2163 pf_values.value,
2164 pfs.price_element_type_id,
2165 pfs.pricing_basis
2166 FROM
2167 pon_price_elements pfs,
2168 pon_pf_supplier_values pf_values
2169 WHERE
2170 pf_values.auction_header_id = pah.auction_header_id
2171 AND pah.large_neg_enabled_flag = 'N'
2172 AND l_is_buyer_negpdf = 'Y'
2173 AND pf_values.auction_header_id = pfs.auction_header_id
2174 AND pf_values.line_number = pfs.line_number
2175 AND pf_values.pf_seq_number = pfs.sequence_number
2176 ORDER BY pf_values.supplier_seq_number, pf_values.line_number, pf_values.pf_seq_number) AS BUYER_PF_VALUES,
2177 CURSOR (
2178 SELECT DISTINCT
2179 ppe.price_element_type_id,
2180 ppe.pricing_basis,
2181 ppett.name,
2182 fl.meaning as pricing_basis_meaning
2183 FROM
2184 pon_price_elements ppe,
2185 pon_price_element_types_tl ppett,
2186 fnd_lookups fl
2187 WHERE
2188 ppe.auction_header_id = pah.auction_header_id
2189 AND pah.large_neg_enabled_flag = 'N'
2190 AND l_is_buyer_negpdf = 'Y'
2191 AND ppe.pf_type = 'BUYER'
2192 AND ppe.price_element_type_id = ppett.price_element_type_id
2193 AND ppett.language = l_printing_language
2194 AND ppe.pricing_basis = fl.lookup_code
2195 AND fl.lookup_type = 'PON_PRICING_BASIS'
2196 ORDER BY name, pricing_basis_meaning) as DISTINCT_BUYER_PFS,
2197 cursor (
2198 select
2199 pon_large_neg_pf_values.supplier_seq_number,
2200 priceelementtypesvl.name||'('||lookuptable.meaning||')' pf_name,
2201 pon_large_neg_pf_values.value
2202 from
2203 pon_large_neg_pf_values pon_large_neg_pf_values,
2204 pon_price_element_types_vl priceelementtypesvl,
2205 fnd_lookups lookuptable
2206 WHERE pon_large_neg_pf_values.auction_header_id = pah.auction_header_id
2207 and pah.large_neg_enabled_flag = 'Y'
2208 AND l_is_buyer_negpdf = 'Y'
2209 AND priceelementtypesvl.price_element_type_id = pon_large_neg_pf_values.price_element_type_id
2210 AND lookuptable.lookup_code = pon_large_neg_pf_values.pricing_basis
2211 AND lookuptable.lookup_type = 'PON_PRICING_BASIS'
2212 AND pon_large_neg_pf_values.value is not null
2213 order by pon_large_neg_pf_values.supplier_seq_number,pf_name) as LARGE_NEG_BUYER_PF_VALUES,
2214 CURSOR (
2215 (
2216 SELECT pbsm.auction_header_id,
2217 pbsm.line_number,
2218 pbsm.auction_shipment_number shipment_number,
2219 pbsm.shipment_number bid_shipment_number,
2220 pbsm.ship_to_organization_id,
2221 mp.organization_code ship_to_organization,
2222 pbsm.ship_to_location_id,
2223 loc.location_code ship_to_location,
2224 pon_printing_pkg.format_number(pbsm.quantity) quantity,
2225 -- in case when supplier add new shipments, there's no target price
2226 decode(pbsm.auction_shipment_number, null, null,
2227 pon_printing_pkg.format_price(
2228 pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),
2229 l_price_mask, l_price_precision)
2230 ) price,
2231
2232 PON_OA_UTIL_PKG.DISPLAY_DATE(pbsm.effective_start_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_start_date,
2233 PON_OA_UTIL_PKG.DISPLAY_DATE(pbsm.effective_end_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_end_date,
2234 nvl2(pbsm.ship_to_location_id, loc.location_code, mp.organization_code) ship_to,
2235 pbsm.has_price_differentials_flag,
2236 pas.differential_response_type,
2237 decode(p_user_view_type, 'BUYER',pon_printing_pkg.format_price(pbsm.price,l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbsm.bid_currency_price,l_price_mask, l_price_precision)) bid_currency_price,
2238 pbsm.price_type,
2239 pbsm.price_discount
2240 FROM pon_auction_shipments_all pas,
2241 pon_auction_item_prices_all paip,
2242 hr_locations_all loc,
2243 mtl_parameters mp,
2244 -- bidpdf: add response price for price breaks
2245 pon_bid_shipments pbsm
2246 WHERE pbsm.bid_number = pbhs.bid_number
2247 and pbsm.auction_header_id = pas.auction_header_id(+)
2248 and pbsm.line_number = pas.line_number(+)
2249 and pbsm.auction_shipment_number = pas.shipment_number(+)
2250 AND l_neg_has_price_breaks = 'Y'
2251 AND paip.auction_header_id = pbsm.auction_header_id
2252 AND paip.line_number = pbsm.line_number
2253 AND pbsm.shipment_type = 'PRICE BREAK'
2254 AND mp.organization_id(+) = pbsm.ship_to_organization_id
2255 AND loc.location_id(+) = pbsm.ship_to_location_id
2256 and exists (select 1 from pon_bid_item_prices where bid_number=pbsm.bid_number and line_number=pbsm.line_number)
2257
2258 UNION ALL
2259
2260 SELECT pas.auction_header_id,
2261 pas.line_number,
2262 pas.shipment_number,
2263 pas.shipment_number bid_shipment_number,
2264 pas.ship_to_organization_id,
2265 mp.organization_code ship_to_organization,
2266 pas.ship_to_location_id,
2267 loc.location_code ship_to_location,
2268 pon_printing_pkg.format_number(pas.quantity) quantity,
2269 pon_printing_pkg.format_price(
2270 pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id,p_trading_partner_contact_id,p_vendor_site_id, p_requested_supplier_id),
2271 l_price_mask,
2272 l_price_precision
2273 ) price,
2274 PON_OA_UTIL_PKG.DISPLAY_DATE(pas.effective_start_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_start_date,
2275 PON_OA_UTIL_PKG.DISPLAY_DATE(pas.effective_end_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') effective_end_date,
2276 nvl2(pas.ship_to_location_id, loc.location_code, mp.organization_code) ship_to,
2277 pas.has_price_differentials_flag,
2278 pas.differential_response_type,
2279 null bid_currency_price,
2280 null price_type,
2281 null price_discount
2282 FROM pon_auction_shipments_all pas,
2283 pon_auction_item_prices_all paip,
2284 hr_locations_all loc,
2285 mtl_parameters mp
2286 WHERE pas.auction_header_id = pah.auction_header_id
2287 AND l_neg_has_price_breaks = 'Y'
2288 AND paip.auction_header_id = pas.auction_header_id
2289 AND paip.line_number = pas.line_number
2290 AND pas.shipment_type = 'PRICE BREAK'
2291 AND mp.organization_id(+) = pas.ship_to_organization_id
2292 AND loc.location_id(+) = pas.ship_to_location_id
2293 and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number)
2294 ) ORDER BY line_number,bid_shipment_number asc
2295 ) as ITEM_PRICE_BREAKS,
2296 CURSOR (
2297 (
2298 SELECT pbsm.auction_header_id,
2299 pbsm.line_number,
2300 pbsm.auction_shipment_number shipment_number,
2301 pbsm.shipment_number bid_shipment_number,
2302 pon_printing_pkg.format_number(pbsm.quantity) quantity,
2303 pon_printing_pkg.format_number(pbsm.max_quantity) max_quantity,
2304 -- in case when supplier add new shipments, there's no target price
2305 nvl2(pbsm.auction_shipment_number,
2306 pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(pah.auction_header_id,
2307 pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id,
2308 p_requested_supplier_id),l_price_mask, l_price_precision)
2309 , null
2310 ) price,
2311 decode(p_user_view_type, 'BUYER',pon_printing_pkg.format_price(pbsm.unit_price,l_price_mask, l_price_precision), pon_printing_pkg.format_price(pbsm.bid_currency_unit_price,l_price_mask, l_price_precision)) bid_currency_unit_price
2312 FROM pon_auction_shipments_all pas,
2313 pon_auction_item_prices_all paip,
2314 pon_bid_shipments pbsm
2315 WHERE pbsm.bid_number = pbhs.bid_number
2316 and pbsm.auction_header_id = pas.auction_header_id(+)
2317 and pbsm.line_number = pas.line_number(+)
2318 and pbsm.auction_shipment_number = pas.shipment_number(+)
2319 AND paip.auction_header_id = pbsm.auction_header_id
2320 AND paip.line_number = pbsm.line_number
2321 AND pbsm.shipment_type = 'QUANTITY BASED'
2322 and exists (select 1 from pon_bid_item_prices where bid_number=pbsm.bid_number and line_number=pbsm.line_number)
2323
2324 UNION ALL
2325
2326 SELECT pas.auction_header_id,
2327 pas.line_number,
2328 pas.shipment_number,
2329 pas.shipment_number bid_shipment_number,
2330 pon_printing_pkg.format_number(pas.quantity) quantity,
2331 pon_printing_pkg.format_number(pas.max_quantity) max_quantity,
2332 pon_printing_pkg.format_price(
2333 pon_transform_bidding_pkg.calculate_price(pah.auction_header_id, pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id,p_trading_partner_contact_id,p_vendor_site_id, p_requested_supplier_id),
2334 l_price_mask,
2335 l_price_precision
2336 ) price,
2337 null bid_currency_unit_price
2338 FROM pon_auction_shipments_all pas,
2339 pon_auction_item_prices_all paip
2340 WHERE pas.auction_header_id = pah.auction_header_id
2341 AND paip.auction_header_id = pas.auction_header_id
2342 AND paip.line_number = pas.line_number
2343 AND pas.shipment_type = 'QUANTITY BASED'
2344 and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number)
2345 ) ORDER BY line_number,bid_shipment_number asc
2346 ) as ITEM_QUANTITY_TIERS,
2347 CURSOR (
2348 (SELECT
2349 pay.auction_header_id
2350 ,pay.payment_id
2351 ,pay.line_number
2352 ,pay.payment_display_number payment_display_number
2353 ,pay.ship_to_location_id
2354 ,terr.territory_short_name shipto_country_name
2355 ,hrl.location_code shipto_address_name
2356 ,hrl.address_line_1 shipto_address1
2357 ,hrl.address_line_2 shipto_address2
2358 ,hrl.address_line_3 shipto_address3
2359 ,hrl.town_or_city shipto_city
2360 ,hrl.region_2 shipto_state
2361 ,hrl.region_3 shipto_province_or_region
2362 ,hrl.postal_code shipto_zip_code
2363 ,hrl.postal_code shipto_postal_code
2364 ,hrl.country shipto_country
2365 ,hrl.region_1 shipto_county
2366 ,pay.payment_description
2367 ,pay.payment_type_code
2368 ,lkp1.displayed_field payment_type_disp
2369 ,pay.quantity
2370 ,pay.uom_code
2371 ,uom_tl.unit_of_measure_tl unit_of_measure_tl
2372 ,pon_printing_pkg.format_price(pay.target_price*l_rate, l_price_mask, l_price_precision) target_price
2373 ,pon_oa_util_pkg.display_date_time(pay.need_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') need_by_date
2374 ,pay.work_approver_user_id
2375 ,NVL2(pay.work_approver_user_id, (SELECT per.full_name
2376 FROM per_all_people_f per
2377 WHERE per.person_id = fuser.employee_id
2378 AND per.effective_end_date =
2379 (SELECT MAX(per1.effective_end_date)
2380 FROM per_all_people_f per1
2381 WHERE per.person_id = per1.person_id)
2382 ), NULL) work_approver_name
2383 ,pay.note_to_bidders
2384 ,pay.project_id project_id
2385 ,proj.segment1 project_number
2386 ,pay.project_task_id project_task_id
2387 ,task.task_number project_task_number
2388 ,pay.project_award_id project_award_id
2389 ,awrd.award_number project_award_number
2390 ,pay.project_expenditure_type project_expenditure_type
2391 ,pay.project_exp_organization_id project_exp_organization_id
2392 ,hrorg.name project_exp_organization_name
2393 ,pon_oa_util_pkg.display_date(pay.project_expenditure_item_date, p_client_time_zone,
2394 p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
2395 ,null pay_item_price
2396 ,null amount_display
2397 ,null bid_promised_date
2398
2399 FROM
2400 pon_auc_payments_shipments pay,
2401 pa_projects_all proj,
2402 pa_tasks task,
2403 gms_awards_all awrd,
2404 hr_locations_all hrl,
2405 hr_all_organization_units hrorg,
2406 fnd_user fuser,
2407 po_lookup_codes lkp1,
2408 fnd_territories_tl terr,
2409 mtl_units_of_measure_tl uom_tl
2410 WHERE pay.auction_header_id = p_auction_header_id
2411 AND pay.project_id = proj.project_id(+)
2412 AND pay.project_task_id = task.task_id(+)
2413 AND pay.project_award_id = awrd.award_id(+)
2414 AND pay.ship_to_location_id = hrl.location_id(+)
2415 AND terr.territory_code(+) = hrl.country
2416 AND terr.language(+) = l_printing_language
2417 AND pay.project_exp_organization_id = hrorg.organization_id(+)
2418 AND pay.payment_type_code = lkp1.lookup_code(+)
2419 AND lkp1.lookup_type(+) = 'PAYMENT TYPE'
2420 AND pay.uom_code = uom_tl.uom_code(+)
2421 AND uom_tl.language(+) = l_printing_language
2422 AND fuser.user_id(+) = pay.work_approver_user_id
2423 AND not exists (select 1 from pon_bid_item_prices where bid_number = p_bid_number and line_number=pay.line_number)
2424 UNION ALL
2425 SELECT
2426 pbp.auction_header_id
2427 ,pbp.BID_PAYMENT_ID payment_id
2428 ,pbp.BID_LINE_NUMBER line_number
2429 ,pbp.payment_display_number payment_display_number
2430 ,pay.ship_to_location_id
2431 ,terr.territory_short_name shipto_country_name
2432 ,hrl.location_code shipto_address_name
2433 ,hrl.address_line_1 shipto_address1
2434 ,hrl.address_line_2 shipto_address2
2435 ,hrl.address_line_3 shipto_address3
2436 ,hrl.town_or_city shipto_city
2437 ,hrl.region_2 shipto_state
2438 ,hrl.region_3 shipto_province_or_region
2439 ,hrl.postal_code shipto_zip_code
2440 ,hrl.postal_code shipto_postal_code
2441 ,hrl.country shipto_country
2442 ,hrl.region_1 shipto_county
2443 ,pbp.payment_description
2444 ,pbp.payment_type_code
2445 ,lkp1.displayed_field payment_type_disp
2446 ,pay.quantity
2447 ,pay.uom_code
2448 ,uom_tl.unit_of_measure_tl unit_of_measure_tl
2449 ,pon_printing_pkg.format_price(pay.target_price*l_rate, l_price_mask, l_price_precision) target_price
2450 ,pon_oa_util_pkg.display_date_time(pay.need_by_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') need_by_date
2451 ,pay.work_approver_user_id
2452 ,NVL2(pay.work_approver_user_id, (SELECT per.full_name
2453 FROM per_all_people_f per
2454 WHERE per.person_id = fuser.employee_id
2455 AND per.effective_end_date =
2456 (SELECT MAX(per1.effective_end_date)
2457 FROM per_all_people_f per1
2458 WHERE per.person_id = per1.person_id)
2459 ), NULL) work_approver_name
2460 ,pay.note_to_bidders
2461 ,pay.project_id project_id
2462 ,proj.segment1 project_number
2463 ,pay.project_task_id project_task_id
2464 ,task.task_number project_task_number
2465 ,pay.project_award_id project_award_id
2466 ,awrd.award_number project_award_number
2467 ,pay.project_expenditure_type project_expenditure_type
2468 ,pay.project_exp_organization_id project_exp_organization_id
2469 ,hrorg.name project_exp_organization_name
2470 ,pon_oa_util_pkg.display_date(pay.project_expenditure_item_date, p_client_time_zone,
2471 p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
2472 ,decode(p_user_view_type, 'BUYER', pon_printing_pkg.format_price(pbp.price, l_price_mask,l_price_precision), pon_printing_pkg.format_price(pbp.bid_currency_price,l_price_mask,l_price_precision)) pay_item_price
2473 ,to_char(decode(pbp.quantity,null,decode(pbip.quantity, null, 1,pbip.quantity),pbp.quantity)*decode(p_user_view_type, 'BUYER',pbp.price,pbp.bid_currency_price), l_amount_mask) amount_display
2474 ,pon_oa_util_pkg.display_date_time(pbp.promised_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') bid_promised_date
2475 FROM
2476 pon_auc_payments_shipments pay,
2477 pa_projects_all proj,
2478 pa_tasks task,
2479 gms_awards_all awrd,
2480 hr_locations_all hrl,
2481 hr_all_organization_units hrorg,
2482 fnd_user fuser,
2483 po_lookup_codes lkp1,
2484 fnd_territories_tl terr,
2485 mtl_units_of_measure_tl uom_tl,
2486 pon_bid_payments_shipments pbp,
2487 pon_bid_item_prices pbip
2488 WHERE
2489 pbp.bid_number = pbhs.bid_number
2490 AND pbp.auction_header_id = pay.auction_header_id(+)
2491 AND pbip.bid_number = pbp.bid_number
2492 AND pbip.line_number = pbp.bid_line_number
2493 AND pbp.bid_line_number = pay.line_number(+)
2494 AND pbp.auction_payment_id = pay.payment_id(+)
2495 AND pay.project_id = proj.project_id(+)
2496 AND pay.project_task_id = task.task_id(+)
2497 AND pay.project_award_id = awrd.award_id(+)
2498 AND pay.ship_to_location_id = hrl.location_id(+)
2499 AND terr.territory_code(+) = hrl.country
2500 AND terr.language(+) = l_printing_language
2501 AND pay.project_exp_organization_id = hrorg.organization_id(+)
2502 AND pbp.payment_type_code = lkp1.lookup_code(+)
2503 AND lkp1.lookup_type(+) = 'PAYMENT TYPE'
2504 AND pay.uom_code = uom_tl.uom_code(+)
2505 AND uom_tl.language(+) = l_printing_language
2506 AND fuser.user_id(+) = pay.work_approver_user_id
2507 )ORDER BY line_number, payment_display_number
2508 ) AS PAY_ITEMS,
2509 cursor(
2510 ((select
2511 distinct loc.location_id id,
2512 loc.location_code name,
2513 ship_territories_tl.territory_short_name country_name,
2514 loc.location_code address_name,
2515 loc.address_line_1 address1,
2516 loc.address_line_2 address2,
2517 loc.address_line_3 address3,
2518 loc.town_or_city city,
2519 loc.region_2 state,
2520 loc.region_3 province_or_region,
2521 loc.postal_code zip_code,
2522 loc.postal_code postal_code,
2523 loc.country country,
2524 loc.region_1 county
2525 from
2526 hr_locations_all loc,
2527 pon_auction_shipments_all pas,
2528 fnd_territories_tl ship_territories_tl
2529 WHERE
2530 pas.auction_header_id = pah.auction_header_id
2531 and l_is_buyer_negpdf = 'N'
2532 and l_neg_has_price_breaks = 'Y'
2533 and pas.shipment_type = 'PRICE BREAK'
2534 and loc.ship_to_site_flag='Y'
2535 and sysdate < nvl(loc.inactive_date, sysdate + 1)
2536 and loc.location_id = pas.ship_to_location_id
2537 and ship_territories_tl.territory_code(+) = loc.country
2538 and ship_territories_tl.language(+) = l_printing_language
2539 and nvl(loc.business_group_id, nvl(hr_general.get_business_group_id, -99))
2540 = nvl(hr_general.get_business_group_id, nvl(loc.business_group_id, -99)))
2541 union
2542 (select
2543 mp.organization_id id,
2544 mp.organization_code name,
2545 ship_territories_tl.territory_short_name country_name,
2546 loc.location_code address_name,
2547 loc.address_line_1 address1,
2548 loc.address_line_2 address2,
2549 loc.address_line_3 address3,
2550 loc.town_or_city city,
2551 loc.region_2 state,
2552 loc.region_3 province_or_region,
2553 loc.postal_code zip_code,
2554 loc.postal_code postal_code,
2555 loc.country country,
2556 loc.region_1 county
2557 from
2558 hr_locations_all loc,
2559 hr_all_organization_units haou,
2560 fnd_territories_tl ship_territories_tl,
2561 mtl_parameters mp ,
2562 ( SELECT
2563 distinct pas.ship_to_organization_id
2564 FROM pon_auction_shipments_all pas
2565 WHERE
2566 pas.auction_header_id = p_auction_header_id
2567 AND l_is_buyer_negpdf = 'N'
2568 AND l_neg_has_price_breaks = 'Y'
2569 AND pas.shipment_type = 'PRICE BREAK'
2570 and pas.ship_to_location_id is null) pb_organizations
2571 where
2572 l_is_buyer_negpdf = 'N'
2573 and l_neg_has_price_breaks = 'Y'
2574 and haou.organization_id = mp.organization_id
2575 and haou.organization_id = pb_organizations.ship_to_organization_id
2576 and loc.ship_to_site_flag = 'Y'
2577 and (loc.inventory_organization_id is null or nvl(loc.inventory_organization_id, -1) = nvl(pb_organizations.ship_to_organization_id,-1))
2578 and sysdate < nvl(loc.inactive_date, sysdate + 1)
2579 and ship_territories_tl.territory_code(+) = loc.country
2580 and ship_territories_tl.language(+) = l_printing_language
2581 and nvl(loc.business_group_id, nvl(haou.business_group_id, -99))
2582 = nvl(haou.business_group_id, nvl(loc.business_group_id, -99))
2583 ))
2584 order
2585 by name) as PRICE_BREAK_LOCATIONS,
2586 CURSOR (
2587 (
2588 SELECT
2589 ppd.auction_header_id,
2590 ppd.line_number,
2591 ppd.shipment_number,
2592 ppd.price_differential_number,
2593 ppd.price_type,
2594 pon_printing_pkg.format_number(ppd.multiplier) as target_multiplier,
2595 pon_printing_pkg.format_number(pbpd.multiplier) as multiplier
2596 FROM pon_price_differentials ppd,
2597 -- bidpdf: add response multiplier for price differentials
2598 pon_bid_price_differentials pbpd
2599 WHERE ppd.auction_header_id = pah.auction_header_id
2600 and pbpd.auction_header_id(+) = ppd.auction_header_id
2601 and pbpd.bid_number (+) = pbhs.bid_number
2602 and pbpd.line_number (+) = ppd.line_number
2603 and pbpd.shipment_number(+) = ppd.shipment_number
2604 and ppd.shipment_number = -1
2605 and pbpd.price_differential_number(+) = ppd.price_differential_number
2606 UNION ALL
2607 SELECT
2608 ppd.auction_header_id,
2609 ppd.line_number,
2610 ppd.shipment_number,
2611 ppd.price_differential_number,
2612 ppd.price_type,
2613 pon_printing_pkg.format_number(ppd.multiplier) as target_multiplier,
2614 pon_printing_pkg.format_number(pbpd.multiplier) as multiplier
2615 FROM pon_price_differentials ppd,
2616 -- bidpdf: add response multiplier for price differentials
2617 pon_bid_price_differentials pbpd
2618 WHERE ppd.auction_header_id = pah.auction_header_id
2619 and pbpd.auction_header_id(+) = ppd.auction_header_id
2620 and pbpd.bid_number (+) = pbhs.bid_number
2621 and pbpd.line_number (+) = ppd.line_number
2622 and pbpd.shipment_number(+) = ppd.shipment_number + 1
2623 and ppd.shipment_number <> -1
2624 and pbpd.price_differential_number(+) = ppd.price_differential_number
2625 )ORDER BY auction_header_id, line_number, shipment_number, price_differential_number
2626 ) as PRICE_DIFFERENTIALS,
2627 CURSOR (
2628 SELECT DISTINCT
2629 pov.price_differential_dsp,
2630 pov.price_differential_desc,
2631 pov.price_differential_type
2632 FROM po_price_diff_lookups_v pov) as PRICE_DIFFERENTIAL_TYPES,
2633 cursor(
2634 select ad.attached_document_id,
2635 d.datatype_name,
2636 d.file_name file_name,
2637 d.description,
2638 d.title as attachment_title,
2639 d.url,
2640 'PON_AUCTION_ITEM_PRICES_ALL' as entity_name,
2641 to_char(paip.auction_header_id) pk1_value,
2642 to_char(paip.line_number) pk2_value,
2643 ad.pk3_value,
2644 categories_tl.user_name category_name
2645 from fnd_documents_vl d,
2646 fnd_attached_documents ad,
2647 fnd_document_categories categories,
2648 fnd_document_categories_tl categories_tl,
2649 pon_auction_item_prices_all paip,
2650 financials_system_params_all fsp
2651 where d.document_id = ad.document_id
2652 and
2653 ad.entity_name = 'MTL_SYSTEM_ITEMS'
2654 AND paip.auction_header_id = pah.auction_header_id
2655 and fsp.org_id = pah.org_id
2656 and ad.pk1_value = to_char(fsp.inventory_organization_id)
2657 AND ad.pk2_value = to_char(paip.item_id)
2658 and categories.name='Vendor'
2659 and categories.category_id = d.category_id
2660 and categories.category_id = categories_tl.category_id
2661 and categories_tl.language = l_printing_language
2662 UNION ALL
2663 select ad.attached_document_id,
2664 d.datatype_name,
2665 d.file_name file_name,
2666 d.description,
2667 d.title,
2668 d.url,
2669 ad.entity_name,
2670 ad.pk1_value,
2671 ad.pk2_value,
2672 ad.pk3_value,
2673 categories_tl.user_name category_name
2674 from fnd_documents_vl d,
2675 fnd_attached_documents ad,
2676 fnd_document_categories categories,
2677 fnd_document_categories_tl categories_tl
2678 where d.document_id = ad.document_id
2679 and
2680 ad.entity_name IN ('PON_AUCTION_ITEM_PRICES_ALL',
2681 'PON_AUCTION_HEADERS_ALL')
2682 and ad.pk1_value = to_char(p_auction_header_id)
2683 and (l_is_buyer_negpdf = 'Y' or categories.name='Vendor')
2684 and categories.category_id = d.category_id
2685 and categories.category_id = categories_tl.category_id
2686 and categories_tl.language = l_printing_language
2687 --bidpdf:attachments in bid
2688 UNION ALL
2689 select ad.attached_document_id,
2690 d.datatype_name,
2691 d.file_name file_name,
2692 d.description,
2693 d.title,
2694 d.url,
2695 ad.entity_name,
2696 ad.pk1_value,
2697 ad.pk2_value,
2698 ad.pk3_value,
2699 categories_tl.user_name category_name
2700 from fnd_documents_vl d,
2701 fnd_attached_documents ad,
2702 fnd_document_categories categories,
2703 fnd_document_categories_tl categories_tl
2704 where d.document_id = ad.document_id
2705 and
2706 ad.entity_name IN ('PON_BID_HEADERS',
2707 'PON_BID_ITEM_PRICES')
2708 and ad.pk1_value = to_char(p_auction_header_id)
2709 and ad.pk2_value = to_char(pbhs.bid_number)
2710 --and categories.name=pon_auction_pkg.g_supplier_attachment
2711 and ((l_attach_categ_option = 1 AND categories.name = pon_auction_pkg.g_supplier_attachment)
2712 or (l_attach_categ_option = 2 and categories.name = pon_auction_pkg.g_technical_attachment)
2713 or (l_attach_categ_option = 3 and categories.name in (pon_auction_pkg.g_technical_attachment,pon_auction_pkg.g_commercial_attachment)))
2714 and categories.category_id = d.category_id
2715 and categories.category_id = categories_tl.category_id
2716 and categories_tl.language = l_printing_language
2717 --bidpdf:pay item attachments in bid
2718 UNION ALL
2719 select ad.attached_document_id,
2720 d.datatype_name,
2721 d.file_name file_name,
2722 d.description,
2723 d.title,
2724 d.url,
2725 ad.entity_name,
2726 ad.pk1_value,
2727 ad.pk2_value,
2728 ad.pk3_value,
2729 categories_tl.user_name category_name
2730 from fnd_documents_vl d,
2731 fnd_attached_documents ad,
2732 fnd_document_categories categories,
2733 fnd_document_categories_tl categories_tl
2734 where d.document_id = ad.document_id
2735 and
2736 ad.entity_name IN ('PON_BID_PAYMENTS_SHIPMENTS')
2737 and ad.pk1_value = to_char(pbhs.bid_number)
2738 and categories.name = pon_auction_pkg.g_supplier_attachment
2739 and categories.category_id = d.category_id
2740 and categories.category_id = categories_tl.category_id
2741 and categories_tl.language = l_printing_language
2742 UNION ALL
2743 select ad.attached_document_id,
2744 d.datatype_name,
2745 d.file_name file_name,
2746 d.description,
2747 d.title,
2748 d.url,
2749 ad.entity_name,
2750 ad.pk1_value,
2751 ad.pk2_value,
2752 --for bid pdf, it should be bid_payment_id instead of auction_payment_id,
2753 decode(l_is_bidpdf, 'Y',
2754 (select to_char(bid_payment_id) from PON_BID_PAYMENTS_SHIPMENTS pby where bid_number = p_bid_number and pby.auction_payment_id = to_number(ad.pk3_value)),
2755 ad.pk3_value) pk3_value,
2756 categories_tl.user_name category_name
2757 from fnd_documents_vl d,
2758 fnd_attached_documents ad,
2759 fnd_document_categories categories,
2760 fnd_document_categories_tl categories_tl
2761 where d.document_id = ad.document_id
2762 and
2763 ad.entity_name IN ('PON_AUC_PAYMENTS_SHIPMENTS')
2764 and ad.pk1_value = to_char(p_auction_header_id)
2765 and (l_is_buyer_negpdf = 'Y' or categories.name = 'Vendor')
2766 and categories.category_id = d.category_id
2767 and categories.category_id = categories_tl.category_id
2768 and categories_tl.language = l_printing_language
2769 ) as ATTACHMENTS,
2770 cursor( select
2771 bizrules.name
2772 from
2773 pon_auc_doctype_rules doctype_rules
2774 , pon_auc_bizrules bizrules
2775 where doctype_rules.bizrule_id = bizrules.bizrule_id
2776 and doctype_rules.doctype_id = doctypes.doctype_id
2777 and doctype_rules.display_flag = 'Y'
2778 and doctype_rules.validity_flag = 'Y'
2779 and bizrules.name in (
2780 'BID_LIST_TYPE',
2781 'SHOW_BIDDER_NOTES',
2782 'ALLOW_MULTIPLE_ROUNDS',
2783 'BID_SCOPE',
2784 'BID_QUANTITY_SCOPE',
2785 'BID_FREQUENCY',
2786 'MIN_BID_DECREMENT',
2787 'MANUAL_CLOSE',
2788 'MANUAL_EXTEND',
2789 'AUTO_EXTENSION',
2790 'RANK_INDICATOR',
2791 'BID_RANKING',
2792 'ALLOW_PRICE_ELEMENT',
2793 'AWARD_APPROVAL_REQUIRED',
2794 'DISPLAY_REQ_LINE_INTEGRATION_SOURCE',
2795 'DISPLAY_LINE_INTEGRATION_SOURCE',
2796 'GLOBAL_AGREEMENT',
2797 'ALLOW_COLLABORATION_TEAM',
2798 'START_PRICE',
2799 'TARGET_PRICE',
2800 'CURRENT_PRICE',
2801 'CONTRACT_TYPE',
2802 'ALLOW_PROXYBID',
2803 'MIN_RELEASE_AMOUNT',
2804 'BEST_PRICE'
2805 )) as DOCUMENT_TYPE_RULES,
2806 cursor (
2807 select message_name,
2808 message_text
2809 from
2810 fnd_new_messages
2811 where message_name in ('PON_AUC_TITLE', --title
2812 'PON_AUCTS_OPEN', -- Open Date
2813 'PON_AUCTS_CLOSE', -- Close Date
2814 'PON_AUCTS_PREVIEW', -- Preview Date
2815 'PON_AUCTS_AWARD', -- Award Date
2816 'PON_AUC_IMMEDIATELY', -- Immediately
2817 'PON_AUC_STYLE', -- Style
2818 'PON_EFFECTIVE_START_DATE', -- Effective Start Date
2819 'PON_EFFECTIVE_END_DATE', -- Effective End Date
2820 'PON_ACCTS_BUYER', -- Buyer
2821 'PON_AUCTS_SHIP_TO_ADDRESS', --Ship-To Address
2822 'PON_AUCTS_BILL_TO_ADDRESS', --Bill-To Address
2823 'PON_AUCTS_PAYMENT_TERMS', --Payment Terms
2824 'PON_AUCTS_CARRIER', --Carrier
2825 'PON_AUCTS_FOB', --FOB
2826 'PON_AUCTS_FREIGHT_TERMS', --Freight Terms
2827 'PON_INTEL_AMOUNT', --Amount
2828 'PON_AUCTS_PRICE_PREC', -- Price Precision
2829 'PON_AUCTS_LINE_NO', --Line No.
2830 'PON_AUCTS_UNIT_PRICE', --Unit Price
2831 'PON_AUCTS_NUMBER_OF_UNITS', --Number of Units
2832 'PON_AUCTS_PRN_GENERAL_INFO', -- L.1. General Information
2833 'PON_AUCTS_PRN_TERMS', --I.2 Terms
2834 'PON_AUCTS_PRN_PRICE_SCHEDULE', --2 Price Schedule
2835 'PON_AUCTS_PRN_LINE_INFO', --2.1 Line Information
2836 'PON_AUCTS_EXCHANGE_RATE', --Exchange Rate
2837 'PON_AUCTS_HEADER_INFORMATION', --1 Header Information
2838 'PON_AUC_WEIGHT', --Weight
2839 'PON_AUC_REQUIRES_NO_RESP', --This requires no response.
2840 'PON_AUCTS_OPTIONAL_RESP', --The response is optional.
2841 'PON_AUCTS_MUST_PROVIDE_RESP', --You must provide a response.
2842 'PON_AUCTS_RESP_MUST_BE_NUMERIC', --The response must be a numeric value.
2843 'PON_AUCTS_RESP_MUST_BE_DATE', --The response must be a date value.
2844 'PON_AUC_RESPONSE_VALUE', --Response Value
2845 'PON_AUC_PRN_LINE_ATTR_NOTE', --You must provide a response unless otherwise indicated.
2846 'PON_AUC_ACCEPTABLE_VALUES', --Acceptable Values
2847 'PON_AUC_ATTRIBUTES', --Attributes
2848 'PON_AUC_PRN_REF_ONLY_NO_RESP', --This is for reference only and your response is not required.
2849 'PON_AUC_ANY', --Any
2850 'PON_AUC_NOT_SPECIFIED', --Not Specified
2851 'PON_AUCTION_CURRENCY', --Currency
2852 'PON_AUC_CURRENCY_DESCRIPTION', --Currency escription
2853 'PON_AUC_SHIP_TO', --Ship To
2854 'PON_AUCTS_NEED_BY_DATE', --Need-By Date
2855 'PON_AUC_TARGET_VALUE', --Target value
2856 'PON_AUC_ENSURE_CURR_SELECTED', --Please ensure that you have selected a currency in Section I.2
2857 'PON_AUC_TIME_ZONE', --Time Zone
2858 'PON_AUCTS_EMAIL', --Email
2859 'PON_AUCTS_PHONE', --Phone
2860 'PON_AUCTS_FAX', --Fax
2861 'PON_AUC_CONTACT_DETAILS', --Contact Details
2862 'PON_AUC_YOUR_COMPANY_NAME', --Your Company Name
2863 'PON_AUC_NOTE_TO_SUPPLIER', --Note to Supplier
2864 'PON_AMEND_DESCRIPTION', --Amendment Description
2865 'PON_AMEND_DATE', --Amendment Date
2866 'PON_AUC_RULES_FOR_REFERENCE', --These rules are for your reference. Please do not check any checkboxes.
2867 'PON_AUC_OPTIONAL_PB', --It is optional for you to enter a price for each line in the table. You may propose price breaks in the space provided or on a separate sheet of paper.
2868 'PON_AUC_OPTIONAL_PB_2', -- It is optional for suppliers to enter a price for each line in the table. Suppliers may propose price breaks.
2869 'PON_AUC_OPTIONAL_PB_3', -- You may propose price breaks in the space provided or on a separate sheet of paper.
2870 'PON_AUC_OPTIONAL_PB_4', -- Suppliers may propose price breaks.
2871 'PON_AUC_REQUIRED_PB', --You must enter a price for each line in the table.
2872 'PON_AUC_REQUIRED_PB_2', -- Suppliers must enter a price for each line in the table.
2873 'PON_AUC_CUMULATIVE_PB', --The break quantity is cumulative.
2874 'PON_AUC_NON_CUMULATVE_PB', --The break quantity is non-cumulative.
2875 'PON_AUC_PB_VIEW_SHIP_TO', --To view Ship-To addresses, refer to section
2876 'PON_AUC_LOCATION_PRICING', --Location Pricing
2877 'PON_AUC_REFER_ATTACH_PE', --Please refer to the attachments for price elements included in this line.
2878 'PON_AUC_REFER_ATTACH_PD', --Please refer to the attachments for price differentials included in this line.
2879 'PON_AUC_RFR_ATTACH_PD_LOC_PRC', --Please refer to the attachments for price differentials included in the location pricing for this line.
2880 'PON_AUC_PART_I_HEADER_INFO_C', --PART I: HEADER INFORMATION
2881 'PON_AUC_HEADER_ATTRIBUTES', --Header Attributes
2882 'PON_AUC_PRN_PRICE_SCHEDULE_C', --2 Price Schedule
2883 'PON_ITEM_DETAILS', --Line Details
2884 'PON_AUC_TABLE_OF_CONTENTS_C', --TABLE OF CONTENTS
2885 'PON_AUC_RESPONSE_RULES', --Response Rules
2886 'PON_AUC_NR_CONTROL_MSG_1', --Negotiation is restricted to invited suppliers
2887 'PON_AUC_NR_CONTROL_MSG_2', --Suppliers are allowed to view other suppliers notes and attachments
2888 'PON_AUC_NR_CONTROL_MSG_2A', --Suppliers are allowed to view other suppliers' contract terms, notes and attachments
2889 'PON_AUC_NR_CONTROL_MSG_9', --Buyer may create multiple rounds of negotiations
2890 'PON_AUC_NR_CONTROL_MSG_3', --Suppliers are allowed to respond to selected lines
2891 'PON_AUC_NR_CONTROL_MSG_4', --Suppliers are required to respond with full quantity on each line
2892 'PON_AUC_NR_CONTROL_MSG_5', --Allow multiple responses
2893 'PON_AUC_NR_CONTROL_MSG_14', --Suppliers are required to lower the line price when submitting a revised response
2894 'PON_AUC_NR_CONTROL_MSG_7', --Buyer may close the negotiation before the Close Date
2895 'PON_AUC_PRN_ALLOW_MANUAL_EXT', --Buyer may manually extend the negotiation while it is open
2896 'PON_AUCTION_PROMISE_DATE', --Promise Date
2897 'PON_AUCTS_DESCRIPTION', --Description
2898 'PON_AUCTS_NAME', --Name
2899 'PON_AUC_DATA_TYPE', --Data Type
2900 'PON_AUC_UNDEFINED', --Undefined
2901 'PON_AUC_GROUP_WEIGHT', --Group Weight
2902 'PON_AUCTS_ITEM_DESC', -- Description
2903 'PON_ITEM_REV', -- Item, Rev
2904 'PON_ITEM_REV_JOB', --Item, Rev / Job
2905 'PON_AUCTS_CATEGORY', --Category
2906 'PON_SHOPPING_CAT', -- Shopping Category
2907 'PON_AUCTS_UOM', -- Unit
2908 'PON_AUC_CICRLE_RESP_BELOW', --Circle one from the response values below:
2909 'PON_AUC_CIRCLE_RESPONSE_BELOW', -- (Circle one from the response values below):
2910 'PON_AUC_UP_TO', --Up to
2911 'PON_AUC_OPTIONAL_RESP', --It is optional for you to provide a response.
2912 'PON_AUC_MAX_SCORE', --Maximum Score
2913 'PON_AUCTS_RESP_MUST_BE_URL', --The response must be an URL value.
2914 'PON_AUC_PRICE_TYPE_CIRCLE_VAL', --Price Type (Circle one value)
2915 'PON_AUC_EFFECTIVE_FROM_DATE', --Effective From Date
2916 'PON_AUC_EFFECTIVE_TO_DATE', --Effective To Date
2917 'PON_BIDS_PRICE', --Price
2918 'PON_AUC_DISCOUNT_PERCENTAGE', --Discount %
2919 'PON_AUC_ADDRESS', --Address
2920 'PON_AUC_LEAVE_BLANK', --Leave blank
2921 'PON_AUC_ENTER_IN_ATTR_TABLE', --Enter in the Attributes Table below
2922 'PON_AUC_SCORE_FOR_RESPONSE', --(Score for the response)
2923 'PON_AUCTION_QUANTITY', --Quantity
2924 'PON_AUC_PRN_CONTRACT_WARNING', --Note: This document does not include Contract Terms because the buyer does not have permission to view them.
2925 'PON_AUCTS_COMPANY', --Company
2926 'PON_AUC_LOCATION', --Location
2927 'PON_JOB_DETAILS', --Job Details
2928 'PON_AUCTION_LOT', --Lot
2929 'PON_AUCTION_GROUP', --Group
2930 'PON_FO_PROPRIETARY_INFORMATION', --Proprietary and Confidential
2931 'PON_PAGE', -- Page PAGE_NUM of END_PAGE
2932 'PON_AUC_SUBMIT_UR_RESPOSE_TO', -- Please submit your response to:
2933 'PON_AUC_INCLD_FOLLOWING_INFO', -- When submitting your response, please include the following information.
2934 'PON_AUC_BID_VALID_UNTIL', -- Response Valid Until
2935 'PON_AUC_SECURITY_LEVEL', -- Security Level
2936 'PON_AUC_APPROVAL_STATUS', -- Approval Status
2937 'PON_OPERATING_UNIT', -- Operating Unit
2938 'PON_AUC_OUTCOME', -- Outcome
2939 'PON_AUC_NEGOTIATION_STYLE', -- Negotiation Style
2940 'PON_AUCTS_AUCTION_EVENT', -- Event
2941 'PON_SOURCING_PROJECT', -- Sourcing Project
2942 'PON_AUC_APPROVAL_REQUIRED', -- Requires Approval
2943 'PON_AUC_APPROVAL_NOT_REQUIRED', -- Requires No Approval
2944 'PON_AUC_APPROVAL_APPROVED', -- Approved
2945 'PON_AUC_APPROVAL_REJECTED', -- Rejected
2946 'PON_AUC_APPROVAL_INPROCESS', -- In Process
2947 'PON_AUC_COLLABORATION_TEAM', -- Collaboration Team
2948 'PON_AUC_MEMBER_ROLE', -- Member
2949 'PON_AUC_POSITION', -- Position
2950 'PON_AUC_APPROVER', -- Approver
2951 'PON_AUC_ACCESS', -- Access
2952 'PON_AUC_TASK', -- Task
2953 'PON_AUC_TARGET_DATE', -- Target Date
2954 'PON_CORE_YES', -- Yes
2955 'PON_CORE_NO', -- No
2956 'PON_AUCTS_GLOBAL_AGREEMENT', -- Global Agreement
2957 'PON_AUC_ELIGIBLE_RESP_CURR', -- Eligible Response Currencies
2958 'PON_AUC_CHECK_RESP_CURR', -- Check the one currency in which you will enter your response.
2959 'PON_AUC_EX_RATE_TYPE', -- Exchange Rate Type
2960 'PON_AUC_EX_RATE_DATE', -- Exchange Rate Date
2961 'PON_DISP_TO_SUPPLIERS', -- Display To Suppliers
2962 'PON_DO_NOT_DISP_TO_SUPPLIERS', -- Do Not Display to Suppliers
2963 'PON_AUC_DISPLAY_SCORE_2', -- Display scoring criteria to Suppliers
2964 'PON_ABSTRACT_FORMS', -- Abstract and Forms
2965 'PON_AUC_VERSION', -- Version
2966 'PON_AUC_NR_CONTROL_MSG_6', -- Buyer is required to obtain approval of award decisions
2967 'PON_AUC_NR_CONTROL_MSG_13', -- Negotiation is allowed to AutoExtend
2968 'PON_AUC_NR_CONTROL_MSG_15', -- Negotiation is allowed to AutoExtend based on the following settings
2969 'PON_AUC_NR_CONTROL_MSG_16', -- Show best price to a supplier in a blind negotiation
2970 'PON_AUC_NR_CONTROL_MSG_18', -- Enforce supplier's previous round price as start price for this round
2971 'PON_AUTO_EXTEND_SETTINGS', -- AutoExtend Settings
2972 'PON_START_TIME_EXTEND', -- Start Time of Extensions
2973 'PON_NUMBER_OF_EXTENSIONS', -- Number of Extensions
2974 'PON_AUTO_EXTEND_PERIOD', -- AutoExtend Period
2975 'PON_LINES_TO_AUTO_EXTEND', -- Lines to AutoExtend
2976 'PON_AUCTS_CLOSE_DATE', -- Close Date
2977 'PON_AUTOEXT_TIME_2', -- Receipt time of the triggering winning response
2978 'PON_AUTOEXT_ITEM_2', -- Lines that have received winning responses during the AutoExtend period
2979 'PON_TRIGGERING_RESPONSE', -- Triggering Response
2980 'PON_LOW_TRIGG_RESP_RANK', -- Lowest Triggering Response Rank
2981 'PON_AUTOEXT_RESPONSE_1', -- Response with winning lines
2982 'PON_AUTOEXT_RESPONSE_2', -- Any Response
2983 'PON_AUC_UNLIMITED', -- Unlimited
2984 'PON_AUC_MINUTES', -- Minutes
2985 'PON_AUCTS_ALL_ITEMS', -- All Lines
2986 'PON_AUTOEXT_ITEM_3', -- Lines that have received responses during the AutoExtend period
2987 'PON_AUC_DISPLAY_RANK', -- Display Rank As
2988 'PON_AUC_RANKING', -- Ranking
2989 'PON_AUC_PRICE_ELEMENTS', -- Price Factors
2990 'PON_AUC_SUPPLIER_VIEW', -- Suppliers see their response price transformed
2991 'PON_AUC_ENTER_IN_PF_TABLE', -- Enter in the Cost Factors table below
2992 'PON_AUC_REQUISITION', -- Requisition
2993 'PON_AUC_MULTIPLE', -- Multiple
2994 'PON_AUC_LINE_TYPE', -- Line Type
2995 'PON_AUC_PRICE_ELEMENT', -- Price Factor
2996 'PON_AUC_PRICE_ELEMENT_DESC', -- Description
2997 'PON_AUCTS_TYPE', -- Type
2998 'PON_AUCTS_DISP_TO_BIDDER', -- Display To Suppliers
2999 'PON_AUC_PRICING_BASIS', -- Pricing Basis
3000 'PON_AUCTS_ATTR_D_TARGET', -- Display Target
3001 'PON_AUCTS_BID_VALUE', -- Response Value
3002 'PON_AUCTION_ITEM_PRICE', -- Line Price
3003 'PON_AUC_PRICE_FACTOR_NOTE_1', -- It is required for you to enter a response value for the Supplier Price Factors.
3004 'PON_PRICE_DIFFERENTIAL_DESC', -- Description
3005 'PON_TARGET_MULTIPLIER', -- Target Multiplier
3006 'PON_AUC_RESP_MULTIPLIER', -- Response Multiplier
3007 'PON_PRICE_DIFFERENTIALS', -- Price Differentials
3008 'PON_AUC_PRICE_DIFF_NOTE_1', -- Suppliers must enter a response multiplier for each line in the table.
3009 'PON_AUC_PRICE_DIFF_NOTE_2', -- It is optional for suppliers to enter a response multiplier for each line in the table.
3010 'PON_AUC_PRICE_DIFF_NOTE_3', -- You must enter a response multiplier for each line in the table.
3011 'PON_AUC_PRICE_DIFF_NOTE_4', -- It is optional for you to enter a response multiplier for each line in the table.
3012 'PON_AUCTS_PRICE_BREAKS', -- Price Breaks
3013 'PON_AUCTS_PRICE_BREAK', -- Price Break
3014 'PON_AUCTS_TARGET_PRICE', -- Target Price
3015 'PON_AUC_SHIP_TO_ADDRESSES', -- Ship-To Addresses
3016 'PON_INVITED_SUPPLIERS', -- Invited Suppliers
3017 'PON_ACCTS_SUPPLIER', -- Supplier
3018 'PON_AUCTS_SUPPLIER_SITE', -- Supplier Site
3019 'PON_AUCTS_CONTACT', -- Contact
3020 'PON_AUC_ADDNL_EMAIL', -- Additional Contact Email
3021 'PON_AUC_RESPONSE_CURR', -- Response Currency
3022 'PON_ANY_RESPONSE_CURRENCY', -- Any Response Currencies
3023 'PON_NEG_FULL', -- Full
3024 'PON_NEG_RESTRICTED', -- Restricted
3025 'PON_AUC_BUYER_PF_VALUES', -- Buyer Price Factor Values
3026 'PON_HEADER_INFORMATION', -- Header Information
3027 'PON_PRICE_SCHEDULE', -- Price Schedule
3028 'PON_AUCTS_ATTACHMENTS', -- Attachments
3029 'PON_LINE_BID_OPTIONAL', -- It is optional for you to respond to this line.
3030 'PON_AUC_BIDDER_ADDRESS' --Address
3031 ,'PON_DECREMENT_METHOD_MSG' -- Suppliers are required to lower the line price from the best response
3032 ,'PON_ADVANCE_AMOUNT_PROMPT' -- Advance Amount
3033 ,'PON_FINANCING' -- Financing
3034 ,'PON_RETAINAGE' -- Retainage
3035 ,'PON_DEFAULT_PROJECT_INFO' -- Default Project Information
3036 ,'PON_DEFAULT_OWNER' -- Default Owner
3037 ,'PON_DESCRIPTION' -- Description
3038 ,'PON_EXPENDITURE_ITEM_DATE' -- Expenditure Item Date
3039 ,'PON_EXPENDITURE_ORGANIZATION' -- Expenditure Organization
3040 ,'PON_EXPENDITURE_TYPE' -- Expenditure Type
3041 ,'PON_FLAG_DISPLAY_NO' -- Yes
3042 ,'PON_FLAG_DISPLAY_YES' -- No
3043 ,'PON_GOODS_LINE_PAY_ITEM_MSG' -- Unit Price for each pay item is based on the Number of Units quoted for this line
3044 ,'PON_MAXIMUM_RETAINAGE_AMOUNT' -- Maximum Retainage Amount
3045 ,'PON_PAYMENT_INFORMATION' -- Pay Item Information
3046 ,'PON_PAYMENT_TIP_FINANCE' -- Total pay item amount may not add up to the line amt
3047 ,'PON_PAYMENT_TIP_ACTUAL' -- Total pay item amount must add up to the line amt
3048 ,'PON_PAY_ITEM' -- Pay Item
3049 ,'PON_NEGOTIABLE' -- Negotiable
3050 ,'PON_FINANCING_ATTRIBUTES' -- Financing Attributes
3051 ,'PON_RETAINAGE_ATTRIBUTES' -- Retainage Attributes
3052 ,'PON_PROGRESS_PAYMENT_RATE' -- Progress Payment Rate
3053 ,'PON_PROJECT' -- Project
3054 ,'PON_PROJECT_INFORMATION' -- Project Information
3055 ,'PON_RECOUPMENT_RATE' -- Recoupment Rate
3056 ,'PON_RETAINAGE_RATE' -- Retainage Rate
3057 ,'PON_SUPP_ENTERABLE_PYMT_FLAG' -- Supplier can modify Pay Items
3058 ,'PON_SUPP_UPD_PAY_ITEMS_1' -- Suppliers may propose pay items.
3059 ,'PON_SUPP_UPD_PAY_ITEMS_2' -- You may propose pay items in the space provided or on a separate sheet of paper.
3060 ,'PON_SUPP_UPD_PAY_ITEMS_3' -- Suppliers may propose different pay items.
3061 ,'PON_SUPP_UPD_PAY_ITEMS_4' -- You may propose different pay items in the space provided or on a separate sheet of paper.
3062 ,'PON_TASK' -- Task
3063 ,'PON_UNITS' -- Units
3064 ,'PON_OWNER' -- Owner
3065 ,'PON_TEAM_SCORING' -- Team Scoring
3066 ,'PON_TEAM_SCORING_ENABLED' -- Team Scoring enabled
3067 ,'PON_TEAM' -- Team
3068 ,'PON_MEMBERS' -- Members
3069 ,'PON_TEAM_INSTRUCTIONS' -- Team Instructions
3070 ,'PON_PRICE_VISIBILITY' -- Price Visibility
3071 ,'PON_SECTION_ASSIGNMENT' -- Section Assignment
3072 ,'PON_AUC_REQUIREMENTS' -- Requirements
3073 ,'PON_AUC_SECTION_WEIGHT' -- Section Weight
3074 ,'PON_AUC_KO_SCORE' -- Knockout Score
3075 ,'PON_AUC_INTERNAL' -- Internal
3076 ,'PON_AUC_AUTOMATIC' -- Automatic
3077 ,'PON_AUC_NONE' -- None
3078 ,'PON_AUC_MANUAL' -- Manual
3079 ,'PON_AUCTS_ATTR_DATATYPE' -- Value Type
3080 ,'PON_AUC_SCORING' -- Scoring
3081 ,'PON_AUC_SCORE_DISPLAYED' -- score displayed in brackets
3082 ,'PON_PROVIDE_ANSWER' -- Provide your answer below
3083 ,'PON_STAGGERED_CLOSING_MSG' -- Staggered Closing
3084 ,'PON_AUCTS_STAG_FIRST_CLOSE_DAT' -- First Line Close date
3085 ,'PON_STAGGERED_CLOSE_INTERVAL' -- Staggered Closing Interval
3086 ,'PON_BID_RESPONSE_STATUS' --Response Status
3087 ,'PON_BID_RESPONSE_SUBMITTED' --Your response has been submitted to:
3088 ,'PON_BID_RESPONSE_WILLSUBMITTED' --Your response will be submitted to:
3089 ,'PON_BID_YOUR_INFO' --Your information is:
3090 ,'PON_AUCTS_YOUR_BID_NUMBER' --Reference Number
3091 ,'PON_AUCTS_NOTE_TO_BUYER' --Note to Buyer
3092 ,'PON_BID_YOUR_REQ_RESPONSE' -- Your response value:
3093 ,'PON_AUC_SURROG_RECVD_TIME' -- Response Received Time
3094 ,'PON_BID_BUYER_ATTACHMENTS' -- Buyer Attachments
3095 ,'PON_BID_SUP_ATTACHMENTS' -- Supplier Attachments
3096 ,'PON_AUCTS_BID_MIN_REL_AMT' -- Bid Minimum Release Amount
3097 ,'PON_BIDS_RESPONSE_PRICE' -- Response Price
3098 ,'PON_AUC_PRICE_SCORE' -- Price/Total Score
3099 ,'PON_AUCTS_PROXY_MIN' --Proxy Minimum
3100 ,'PON_AUCTS_PROXY_DEC' --Proxy Response Decrement
3101 ,'PON_BID_TOTAL_WARNING' --Cannot be displayed because quantity is not available on all lines
3102 ,'PON_AUCTS_PRICE_TYPE' --Price Type
3103 ,'PON_BIDS_PRICE_OR_DISCOUNT' --Response Price or Discount%
3104 ,'PON_BID_BUYER_PI_ATTACHMENTS' -- Buyer Pay Item Attachments
3105 ,'PON_BID_SUP_PI_ATTACHMENTS' -- Supplier Pay Item Attachments
3106 ,'PON_BID_YOUR_RESPONSE_BRACKET' -- Your response value (score displayed in brackets):
3107 ,'PON_BID_YOUR_SITE' -- Your Company Site
3108 ,'PON_BIDS_NO_RESPONSE' -- No Response
3109 ,'PON_BUYER_PDF_TXT' -- Message for buyer view pdf
3110 ,'PON_SUPPLIER_PDF_TXT' -- Message for supplier view pdf
3111 ,'PON_AUCTS_PRICE_TIERS' -- Quantity based price tiers
3112 ,'PON_TIERS_MIN_QUANTITY' -- Minimum Quantity
3113 ,'PON_TIERS_MAX_QUANTITY' -- Maximum Quantity
3114 ,'PON_AUC_TARGET_QUANTITY' --Target Quantity
3115 ,'PON_AUCTS_RESP_QUANTITY' --Response Quantity
3116 ,'PON_AUCTION_PRICE' -- Price
3117 ) and application_id =396
3118 and language_code = l_printing_language) as GENERIC_MESSAGES ,
3119 cursor ( -- this will have all messages that are specific to sourcing type
3120 select substr(message_name,1,length(message_name)-2) message_name,
3121 message_text
3122 from
3123 fnd_new_messages
3124 where message_name in (
3125 pon_printing_pkg.get_document_message_name('PON_CONT_MERGE_WARNING',doctypes.message_suffix), -- Note: There are contract terms associated to the RFQ that are not included in this document. The contract terms are an inseparable part of this RFQ.
3126 pon_printing_pkg.get_document_message_name('PON_RESPONSE_STYLE',doctypes.message_suffix) -- Response Style
3127 ) and application_id =396
3128 and language_code = l_printing_language) as DOCUMENT_SPECIFIC_MESSAGES
3129 from
3130 pon_auction_headers_all pah ,
3131 fnd_lookups fl,
3132 fnd_lookups fl2,
3133 fnd_lookups flbr ,
3134 fnd_lookups fl_rank_ind,
3135 fnd_lookups fl_pf_type_allowed,
3136 fnd_lookup_values fl_freight_terms ,
3137 ap_terms ap ,
3138 fnd_lookup_values fl_fob ,
3139 hr_locations_all loc_bill,
3140 fnd_territories_tl bill_territories_tl,
3141 hr_locations_all loc_ship,
3142 fnd_territories_tl ship_territories_tl,
3143 fnd_currencies_tl currency_tl ,
3144 pon_auc_doctypes doctypes,
3145 hz_parties hp1,
3146 hr_operating_units ou,
3147 hr_all_organization_units entity,
3148 hr_all_organization_units_tl entitytl,
3149 hr_locations_all entity_loc,
3150 fnd_territories_tl entity_terr,
3151 fnd_lookups fl_security,
3152 gl_daily_conversion_types gdct,
3153 fnd_product_groups fpg,
3154 pon_negotiation_styles_vl ns,
3155 PO_ALL_DOC_STYLE_LINES ps,
3156 po_doc_style_headers postyl,
3157 pa_projects_all proj,
3158 fnd_user buyer_user,
3159 per_phones buyer_phone,
3160 per_phones buyer_fax,
3161 pon_bid_headers pbhs,
3162 fnd_lookup_values fl_bid
3163 where pah.auction_header_id = p_auction_header_id
3164 and pbhs.auction_header_id (+) = pah.auction_header_id
3165 and pbhs.bid_number (+) = p_bid_number
3166 and fl_bid.lookup_type(+) = 'PON_BID_STATUS'
3167 and fl_bid.lookup_code(+) = pbhs.bid_status
3168 and fl_bid.language(+) = l_printing_language
3169 and currency_tl.currency_code = pah.currency_code
3170 and currency_tl.language = l_printing_language
3171 and fl.lookup_type = 'PON_BID_VISIBILITY_CODE'
3172 and fl.lookup_code = pah.bid_visibility_code
3173 and flbr.lookup_type = 'PON_BID_RANKING_CODE'
3174 and flbr.lookup_code = pah.bid_ranking
3175 and pah.sealed_auction_status = fl2.lookup_code (+)
3176 and fl2.lookup_type(+) = 'PON_SEALED_AUCTION_STATUS'
3177 and fl_freight_terms.lookup_type(+) = 'FREIGHT TERMS'
3178 and fl_freight_terms.lookup_code(+) = pah.freight_terms_code
3179 and fl_security.lookup_type = 'PON_SECURITY_LEVEL_CODE'
3180 and fl_security.lookup_code = pah.security_level_code
3181 and fl_rank_ind.lookup_type = 'PON_RANK_INDICATOR_CODE'
3182 and fl_rank_ind.lookup_code = pah.rank_indicator
3183 and fl_pf_type_allowed.lookup_type = 'PON_PF_TYPE_ALLOWED'
3184 and fl_pf_type_allowed.lookup_code = pah.pf_type_allowed
3185 and fl_freight_terms.language(+) = l_printing_language
3186 and fl_freight_terms.view_application_id(+) = 201
3187 and fl_freight_terms.security_group_id(+) = 0
3188 and ap.term_id(+) = pah.payment_terms_id
3189 and fl_fob.lookup_type(+) = 'FOB'
3190 and fl_fob.lookup_code(+) = pah.fob_code
3191 and fl_fob.language(+) = l_printing_language
3192 and fl_fob.view_application_id(+) = 201
3193 and fl_fob.security_group_id (+) = 0
3194 and loc_bill.location_id(+) = pah.bill_to_location_id
3195 and bill_territories_tl.territory_code(+) = loc_bill.country
3196 and bill_territories_tl.language(+) = l_printing_language
3197 and loc_bill.bill_to_site_flag(+)='Y'
3198 and sysdate < nvl(loc_bill.inactive_date(+), sysdate + 1)
3199 and nvl(loc_bill.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
3200 = nvl(hr_general.get_business_group_id, nvl(loc_bill.business_group_id(+), -99))
3201 and loc_ship.location_id(+) = pah.ship_to_location_id
3202 and ship_territories_tl.territory_code(+) = loc_ship.country
3203 and ship_territories_tl.language(+) = l_printing_language
3204 and loc_ship.ship_to_site_flag(+)='Y'
3205 and sysdate < nvl(loc_ship.inactive_date(+), sysdate + 1)
3206 and nvl(loc_ship.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
3207 = nvl(hr_general.get_business_group_id, nvl(loc_ship.business_group_id(+), -99))
3208 and pah.org_id = ou.organization_id(+)
3209 and nvl(ou.date_from(+),sysdate-1) < sysdate
3210 and nvl(ou.date_to(+),sysdate+1) > sysdate
3211 and pon_conterms_utl_pvt.get_legal_entity_id(pah.org_id) = entity.organization_id(+)
3212 and entity.organization_id = entitytl.organization_id(+)
3213 and entitytl.language(+) = l_printing_language
3214 and entity.location_id = entity_loc.location_id(+)
3215 and nvl(entity_loc.inactive_date(+), sysdate+1) > sysdate
3216 and entity_terr.territory_code(+) = entity_loc.country
3217 and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
3218 and entity_terr.language(+) = l_printing_language
3219 and gdct.conversion_type(+) = pah.rate_type
3220 and hp1.party_id = pah.trading_partner_contact_id
3221 and pah.doctype_id = doctypes.doctype_id
3222 and pah.style_id = ns.style_id
3223 and pah.po_style_id = postyl.style_id(+)
3224 and pah.project_id = proj.project_id(+)
3225 and pah.po_style_id = ps.style_id(+)
3226 and pah.contract_type = ps.document_subtype(+)
3227 and ps.language(+) = l_printing_language
3228 and pah.trading_partner_contact_name = buyer_user.user_name
3229 and buyer_phone.parent_table(+) = 'PER_ALL_PEOPLE_F'
3230 and buyer_phone.parent_id(+) = buyer_user.employee_id
3231 and buyer_phone.phone_type(+) = 'W1'
3232 and nvl(buyer_phone.date_from(+), trunc(sysdate)) <= trunc(sysdate)
3233 and nvl(buyer_phone.date_to(+), trunc(sysdate)) >= trunc(sysdate)
3234 and buyer_fax.parent_table(+) = 'PER_ALL_PEOPLE_F'
3235 and buyer_fax.parent_id(+) = buyer_user.employee_id
3236 and buyer_fax.phone_type(+) = 'WF'
3237 and nvl(buyer_fax.date_from(+), trunc(sysdate)) <= trunc(sysdate)
3238 and nvl(buyer_fax.date_to(+), trunc(sysdate)) >= trunc(sysdate);
3239
3240 dbms_lob.createtemporary(result, TRUE);
3241
3242 SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
3243
3244 xml_stmt := 'DECLARE
3245 queryCtx DBMS_XMLGEN.ctxHandle;
3246 BEGIN
3247 queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
3248 DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
3249 DBMS_XMLGEN.closeContext (queryCtx);
3250 exception when others then
3251 DBMS_XMLGEN.closeContext (queryCtx);
3252 RAISE;
3253 END;';
3254 execute immediate xml_stmt USING IN OUT xml_query_cursor, IN OUT result;
3255
3256 SELECT CURRENT_DATE INTO l_end_time FROM DUAL;
3257
3258 CLOSE xml_query_cursor;
3259
3260 IF l_statement_log_level >= l_current_log_level THEN
3261 FND_LOG.string(l_statement_log_level, l_module_name, 'PDF: generating XML time: ' || (l_end_time - l_start_time) * 24 * 60 * 60);
3262 END IF;
3263
3264 return result;
3265
3266 END GENERATE_AUCTION_XML;
3267
3268
3269
3270 END PON_PRINTING_PKG;