[Home] [Help]
PACKAGE BODY: APPS.PON_PRINTING_PKG
Source
1 PACKAGE BODY PON_PRINTING_PKG as
2 /* $Header: PONPRNB.pls 120.93.12020000.4 2013/05/31 07:17:50 sgulkota 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 --Function: GENERATE_EMD_XML for negotiation emd report
820 --12-Nov-2008 Chaoqun Create
821 -----------------------------------------------------------------------------------------
822 FUNCTION generate_emd_xml(p_auction_header_id IN NUMBER) RETURN CLOB IS
823 result CLOB;
824 TYPE emd_header_cursor_type IS REF CURSOR;
825 xml_query_cursor emd_header_cursor_type;
826 xml_stmt varchar2(500);
827 l_printing_language VARCHAR2(3) := userenv('lang');
828 l_start_time DATE;
829 l_end_time DATE;
830 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
831 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
832 l_module_name VARCHAR2(80) := 'pon.plsql.PON_PRINTING_PKG.GENERATE_EMD_XML';
833
834 BEGIN
835
836 OPEN xml_query_cursor FOR
837 SELECT pah.AUCTION_HEADER_ID AS NEGOTIATION_NUMBER,
838 pah.AUCTION_TITLE AS NEGOTIATION_TITLE,
839 (SELECT COUNT(*)
840 FROM pon_bidding_parties pbp
841 WHERE pbp.AUCTION_HEADER_ID = pah.AUCTION_HEADER_ID
842 ) AS NUMBER_OF_SUPPLIERS,
843 pah.CLOSE_BIDDING_DATE AS NEGOTIATION_CLOSE_DATE, --Modify by Chaoqun 04-Mar-2009
844 pah.CURRENCY_CODE as EMD_QUOTE_CURRENCY,
845 pah.EMD_ENABLE_FLAG,
846 pah.EMD_AMOUNT,
847 pah.EMD_DUE_DATE,
848 pah.EMD_TYPE,
849 flv.MEANING AS EMD_TYPE_MEANING,
850 pah.EMD_GUARANTEE_EXPIRY_DATE AS EMD_GUARANTEE_EXPIRY_DAYS,
851 CURSOR (SELECT ROWNUM AS SERIAL_NUM,
852 pbp.trading_partner_name AS SUPPLIER_NAME,
853 pbp.trading_partner_contact_name AS SUPPLIER_USER,
854 pbp.vendor_site_code AS SUPPLIER_SITE,
855 decode(pbp.exempt_flag, null, 'N',pbp.exempt_flag) as EMD_EXEMPTED,
856 decode(decode(pet.status_lookup_code,
857 null,
858 decode(pbp.exempt_flag,
859 null, 'NOT_PAID',
860 'N', 'NOT_PAID',
861 'Y', 'EXEMPTED'),
862 pet.status_lookup_code),
863 'NOT_PAID',
864 'N',
865 'EXEMPTED',
866 'N',
867 'RECEIVING', --Modify by Chaoqun 05-Mar-2009
868 'N',
869 'RECEIVE_ERROR',
870 'N',
871 'Y') as EMD_RECEIVED,
872 (select petr.amount
873 from pon_emd_transactions petr
874 where petr.auction_header_id = p_auction_header_id
875 and petr.supplier_sequence= pbp.sequence
876 and pbp.AUCTION_HEADER_ID = petr.AUCTION_HEADER_ID
877 and (petr.status_lookup_code = 'RECEIVING'
878 or petr.status_lookup_code = 'RECEIVED'
879 or petr.status_lookup_code = 'RECEIVE_ERROR')
880 ) as EMD_RECEIVED_AMOUNT,
881 (select petr.TRANSACTION_DATE
882 from pon_emd_transactions petr
883 where petr.auction_header_id = p_auction_header_id
884 and petr.supplier_sequence= pbp.sequence
885 and pbp.AUCTION_HEADER_ID = petr.AUCTION_HEADER_ID
886 and (petr.status_lookup_code = 'RECEIVING'
887 or petr.status_lookup_code = 'RECEIVED'
888 or petr.status_lookup_code = 'RECEIVE_ERROR')
889 ) as EMD_RECEIVED_DATE,
890 decode(decode(pet.status_lookup_code,
891 null,
892 decode(pbp.exempt_flag,
893 null, 'NOT_PAID',
894 'N', 'NOT_PAID',
895 'Y', 'EXEMPTED'),
896 pet.status_lookup_code),
897 'REFUNDING', pet.amount,
898 'REFUNDED', pet.amount,
899 'REFUND_ERROR', pet.amount,
900 'FORFEITING', pet.amount,
901 'FORFEITED', pet.amount,
902 'FORFEIT_ERROR', pet.amount,
903 null) as EMD_RF_AMOUNT,
904 decode(decode(pet.status_lookup_code,
905 null,
906 decode(pbp.exempt_flag,
907 null, 'NOT_PAID',
908 'N', 'NOT_PAID',
909 'Y', 'EXEMPTED'),
910 pet.status_lookup_code),
911 'REFUNDING', pet.TRANSACTION_DATE,
912 'REFUNDED', pet.TRANSACTION_DATE,
913 'REFUND_ERROR', pet.TRANSACTION_DATE,
914 'FORFEITING', pet.TRANSACTION_DATE,
915 'FORFEITED', pet.TRANSACTION_DATE,
916 'FORFEIT_ERROR', pet.TRANSACTION_DATE,
917 null) as EMD_RF_DATE,
918 decode(decode(pet.status_lookup_code, --Modify by Chaoqun 05-Mar-2009
919 null,
920 decode(pbp.exempt_flag,
921 null, 'NOT_PAID',
922 'N', 'NOT_PAID',
923 'Y', 'EXEMPTED'),
924 pet.status_lookup_code),
925 'NOT_PAID',null,
926 'EXEMPTED',null,
927 'RECEIVING',pet.transaction_currency_code,
928 'RECEIVE_ERROR',pet.transaction_currency_code,
929 'RECEIVED',pet.transaction_currency_code,
930 (select petr.transaction_currency_code
931 from pon_emd_transactions petr
932 where petr.auction_header_id = p_auction_header_id
933 and petr.supplier_sequence= pbp.sequence
934 and pbp.AUCTION_HEADER_ID = petr.AUCTION_HEADER_ID
935 and petr.status_lookup_code = 'RECEIVED')
936 ) AS EMD_RECEIVED_CURRENCY,
937 (select flv.meaning --Modify by Chaoqun 05-Mar-2009
938 from fnd_lookup_values flv
939 where flv.lookup_type = 'PON_EMD_SUPPLIER_STATUS'
940 AND flv.language = USERENV('LANG')
941 AND flv.lookup_code = decode(pet.status_lookup_code,
942 null,
943 decode(pbp.exempt_flag,
944 null, 'NOT_PAID',
945 'N', 'NOT_PAID',
946 'Y', 'EXEMPTED'),
947 pet.status_lookup_code)
948 ) as EMD_CURRENT_STATUS,
949 pet.Justification as EMD_JUSTIFICATION
950 FROM PON_bidding_parties pbp,
951 PON_EMD_TRANSACTIONS pet
952 WHERE pbp.AUCTION_HEADER_ID = p_auction_header_id
953 AND pbp.AUCTION_HEADER_ID = pet.AUCTION_HEADER_ID(+) --Modify by Chaoqun 05-Mar-2009
954 AND pbp.sequence = pet.supplier_sequence(+)
955 AND decode(pet.current_row_flag,null,'Y',pet.current_row_flag) = 'Y') AS EMD_SUMMARY,
956 CURSOR (select message_name, message_text
957 from fnd_new_messages
958 where message_name in
959 ('PON_NEGOTIATION_NUMBER',
960 'PON_NEGOTIATION_TITLE', 'PON_NEG_CLOSE_DATE',
961 'PON_EMD_QUOTE_CURRENCY', 'PON_EMD_TYPE' -- EMD Type
962 , 'PON_EMD_DUE_DATE' -- EMD Due Date
963 , 'PON_EMD_AMOUNT' -- EMD Amount
964 , 'PON_EMD_GUARANTEE_EXPIRY_DATE' -- Bank Guarantee Expiry Date
965 , 'PON_EMD_SUMMARY', 'PON_EMD_SERIAL_NUM',
966 'PON_EMD_SUPPLIER_NAME', 'PON_EMD_SUPPLIER_USER',
967 'PON_EMD_SUPPLIER_SITE',
968 'PON_EMD_NUM_OF_SUPPLIERS', 'PON_EMD_EXEMPTED',
969 'PON_EMD_RECEIVED', 'PON_EMD_RECEIVED_CURRENCY',
970 'PON_EMD_RECEIVED_AMOUNT',
971 'PON_EMD_RECEIVED_DATE',
972 'PON_EMD_RF_AMOUNT', 'PON_EMD_RF_DATE',
973 'PON_EMD_CURRENT_STATUS',
974 'PON_EMD_JUSTIFICATION',
975 'PON_CREATED_BY', 'PON_EMD_REPORT_HEADING' --Added by Chaoqun on 16-Apr-2009 for UI Change
976 )
977 and application_id = 396
978 and language_code = l_printing_language) as GENERIC_MESSAGES
979 FROM pon_auction_headers_all pah
980 , fnd_lookup_values flv
981 -- , FND_USER fu
982 WHERE pah.auction_header_id = p_auction_header_id --Using the variable p_auction_header_id
983 and flv.lookup_type(+) = 'PON_AUCTION_EMD_TYPE'
984 and flv.language(+) = USERENV('LANG')
985 and flv.lookup_code(+) = pah.EMD_TYPE;
986 -- and fu.USER_ID = pah.CREATED_BY;
987
988 dbms_lob.createtemporary(result, TRUE);
989
990 SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
991
992 xml_stmt := 'DECLARE
993 queryCtx DBMS_XMLGEN.ctxHandle;
994 BEGIN
995 queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
996 DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
997 DBMS_XMLGEN.closeContext (queryCtx);
998 exception when others then
999 DBMS_XMLGEN.closeContext (queryCtx);
1000 RAISE;
1001 END;';
1002 execute immediate xml_stmt
1003 USING IN OUT xml_query_cursor, IN OUT result;
1004
1005 SELECT CURRENT_DATE INTO l_end_time FROM DUAL;
1006
1007 CLOSE xml_query_cursor;
1008
1009 IF l_statement_log_level >= l_current_log_level THEN
1010 FND_LOG.string(l_statement_log_level,
1011 l_module_name,
1012 'PDF: generating XML time: ' ||
1013 (l_end_time - l_start_time) * 24 * 60 * 60);
1014 END IF;
1015
1016 return result;
1017
1018 END GENERATE_EMD_XML;
1019
1020 -------------------------------------------------------------------------*\
1021 -- 13-Nov-2008 Yao Zhang Create |
1022 -- Function Name: GENERATE_SUPPLIER_XML |
1023 -- This function is used to query data for individual supplier report |
1024 -------------------------------------------------------------------------*/
1025
1026 FUNCTION generate_supplier_xml(p_auction_header_id IN NUMBER,
1027 p_supplier_sequence IN NUMBER) RETURN CLOB IS
1028 result CLOB;
1029 TYPE emd_supplier_cursor_type IS REF CURSOR;
1030 xml_query_cursor emd_supplier_cursor_type;
1031 xml_stmt varchar2(500);
1032 l_printing_language VARCHAR2(3) := userenv('lang');
1033 l_start_time DATE;
1034 l_end_time DATE;
1035 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1036 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1037 l_module_name VARCHAR2(80) := 'pon.plsql.PON_PRINTING_PKG.GENERATE_SUPPLIER_XML';
1038 l_emd_current_status varchar2(20);
1039 --Begin: Added by Chaoqun on 11-Jan_2009
1040 l_cust_trx_id NUMBER;
1041 l_cust_trx_num VARCHAR2(20);
1042 x_rec_rec_num VARCHAR2(50);
1043 l_org_id NUMBER;
1044 x_cash_receipt_id NUMBER;
1045 x_receivable_app_id NUMBER;
1046 x_receipt_status VARCHAR2(20);
1047 x_receipt_status_code VARCHAR2(20);
1048 x_return_status VARCHAR2(20);
1049 --End: Added by Chaoqun on 11-Jan_2009
1050
1051 BEGIN
1052
1053 BEGIN
1054 select decode(petr.status_lookup_code,
1055 null,
1056 decode(pbp.exempt_flag,
1057 null, 'NOT_PAID',
1058 'N', 'NOT_PAID',
1059 'Y', 'EXEMPTED'),
1060 petr.status_lookup_code)
1061 into l_emd_current_status
1062 from pon_emd_transactions petr,
1063 pon_bidding_parties pbp
1064 where pbp.sequence=p_supplier_sequence
1065 and pbp.auction_header_id= p_auction_header_id
1066 and petr.auction_header_id(+) = pbp.auction_header_id
1067 and petr.supplier_sequence(+) = pbp.sequence
1068 and decode(petr.current_row_flag,null,'Y',petr.current_row_flag) = 'Y';
1069 --Begin: Added by Chaoqun on 11-Jan_2009
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 l_emd_current_status := 'NOT_PAID';
1073 END;
1074
1075 BEGIN
1076 select petr.cust_trx_id,
1077 petr.Cust_Trx_Number,
1078 petr.Org_Id
1079 into l_cust_trx_id,
1080 l_cust_trx_num,
1081 l_org_id
1082 from pon_emd_transactions petr
1083 where petr.auction_header_id = p_auction_header_id
1084 and petr.status_lookup_code = 'RECEIVED'
1085 and petr.supplier_sequence = p_supplier_sequence;
1086 exception
1087 when NO_DATA_FOUND then
1088 l_cust_trx_id := null;
1089 l_cust_trx_num := null;
1090 END;
1091
1092 IF l_cust_trx_id IS NOT NULL AND l_cust_trx_num IS NOT NULL THEN
1093 PON_EMD_VALIDATION_PKG.getReceiptInfoOfTrx(l_cust_trx_id,
1094 l_cust_trx_num,
1095 l_org_id,
1096 x_rec_rec_num,
1097 x_cash_receipt_id,
1098 x_receivable_app_id,
1099 x_receipt_status,
1100 x_receipt_status_code,
1101 x_return_status
1102 );
1103 END IF;
1104 --End: Added by Chaoqun on 11-Jan_2009
1105
1106 --Begin: Add by Chaoqun on 05-Mar-2009
1107 IF l_emd_current_status = 'NOT_PAID' OR l_emd_current_status = 'EXEMPTED' THEN
1108 OPEN xml_query_cursor FOR
1109 select 'Y' as SUPPLIER_REPORT,
1110 paha.document_number as NEGOTIATION_NUMBER,
1111 paha.auction_title as NEGOTIATION_TITLE,
1112 pbp.trading_partner_name as SUPPLIER_NAME,
1113 pbp.vendor_site_code as SUPPLIER_SITE,
1114 paha.emd_amount as EMD_AMOUNT,
1115 paha.emd_due_date as EMD_DUE_DATE,
1116 paha.emd_type as EMD_TYPE,
1117 fl.meaning as EMD_TYPE_MEANING,
1118 paha.emd_guarantee_expiry_date as EMD_GUARANTEE_EXPIRY_DATE,
1119 paha.currency_code as EMD_CURR_CODE,
1120 (select flv.meaning
1121 from fnd_lookup_values flv
1122 where flv.lookup_type = 'PON_EMD_SUPPLIER_STATUS'
1123 AND flv.language = USERENV('LANG')
1124 AND flv.lookup_code = l_emd_current_status
1125 ) as EMD_CURRENT_STATUS,
1126 PON_LOCALE_PKG.party_display_name(hz.person_first_name,
1127 hz.PERSON_LAST_NAME,
1128 hz.person_middle_name,
1129 fl1.MEANING,
1130 hz.PERSON_NAME_SUFFIX,
1131 userenv('LANG')) AS CREATED_BY,
1132 decode(pbp.exempt_flag,
1133 null, 'N',
1134 pbp.exempt_flag) as EMD_EXEMPTED_FLAG,
1135 'N' as EMD_RECEIVED_FLAG,
1136 'N' as EMD_REFUNDED_FLAG,
1137 'N' as EMD_FORFEITED_FLAG,
1138 cursor (select message_name, message_text
1139 from fnd_new_messages
1140 where message_name in
1141 ('PON_EMD_NEGOTIATION_NO',
1142 'PON_EMD_NEGOTIATION_TITLE',
1143 'PON_EMD_SUPPLIER_NAME', 'PON_EMD_SUPPLIER_SITE',
1144 'PON_EMD_AMOUNT', 'PON_EMD_DUE_DATE',
1145 'PON_EMD_TYPE', 'PON_EMD_GUARANTEE_EXPIRY_DATE',
1146 'PON_EMD_QUOTE_CURRENCY',
1147 'PON_EMD_CURRENT_STATUS', 'PON_EMD_SUMMARY',
1148 'PON_EMD_PAYMENT_DETAILS',
1149 'PON_EMD_REFUND_DETAILS',
1150 'PON_EMD_FORFEIT_DETAILS', 'PON_EMD_EXEMPTED',
1151 'PON_EMD_RECEIVED', 'PON_EMD_RECEIVED_AMOUNT',
1152 'PON_EMD_RECEIVED_DATE', 'PON_EMD_REFUNDED',
1153 'PON_EMD_REFUNDED_AMOUNT',
1154 'PON_EMD_REFUNDED_DATE', 'PON_EMD_FORFEITED',
1155 'PON_EMD_FORFEIT_AMOUNT', 'PON_EMD_FORFEIT_DATE',
1156 'PON_EMD_BANK_NAME', 'PON_EMD_BRANCH_NAME',
1157 'PON_EMD_DETAIL_BANK_ACCOUNT',
1158 'PON_EMD_PAYMENT_TYPE', 'PON_EMD_CURRENCY',
1159 'PON_EMD_PAYMENT_DATE', 'PON_EMD_DETAIL_AMOUNT',
1160 'PON_EMD_CREATED_BY', 'PON_EMD_SUPPLIER_HEADING' --Added by Chaoqun on 15-Mar-2009 for UI Change
1161 )
1162 and application_id = 396
1163 and language_code = l_printing_language) as GENERIC_MESSAGES
1164 from pon_auction_headers_all paha,
1165 pon_bidding_parties pbp,
1166 fnd_lookup_values fl,
1167 fnd_lookups fl1,
1168 pon_auc_doctypes doc,
1169 HZ_PARTIES hz
1170 where paha.auction_header_id = p_auction_header_id
1171 and pbp.auction_header_id = paha.auction_header_id
1172 and pbp.sequence = p_supplier_sequence
1173 and fl.lookup_type(+) = 'PON_AUCTION_EMD_TYPE'
1174 and fl.language(+) = USERENV('LANG')
1175 and fl.LOOKUP_CODE(+) = paha.EMD_TYPE
1176 and fl1.lookup_type = 'PON_AUCTION_DOC_TYPES'
1177 and fl1.lookup_code = doc.internal_name
1178 and paha.doctype_id = doc.doctype_id
1179 and HZ.party_id(+) = paha.trading_partner_contact_id
1180 and rownum = 1;
1181 ELSE
1182 --END: Add by Chaoqun on 05-Mar-2009
1183 OPEN xml_query_cursor FOR
1184 select 'Y' as SUPPLIER_REPORT,
1185 paha.document_number as NEGOTIATION_NUMBER,
1186 paha.auction_title as NEGOTIATION_TITLE,
1187 pbp.trading_partner_name as SUPPLIER_NAME,
1188 pbp.vendor_site_code as SUPPLIER_SITE,
1189 paha.emd_amount as EMD_AMOUNT,
1190 paha.emd_due_date as EMD_DUE_DATE,
1191 paha.emd_type as EMD_TYPE,
1192 fl.meaning as EMD_TYPE_MEANING,
1193 paha.emd_guarantee_expiry_date as EMD_GUARANTEE_EXPIRY_DATE,
1194 paha.currency_code as EMD_CURR_CODE,
1195 (select flv.meaning
1196 from fnd_lookup_values flv
1197 where flv.lookup_type = 'PON_EMD_SUPPLIER_STATUS'
1198 AND flv.language = USERENV('LANG')
1199 AND flv.lookup_code = l_emd_current_status
1200 ) as EMD_CURRENT_STATUS, --Modify by Chaoqun 09-Mar-2009
1201 PON_LOCALE_PKG.party_display_name(hz.person_first_name,
1202 hz.PERSON_LAST_NAME,
1203 hz.person_middle_name,
1204 fl1.MEANING,
1205 hz.PERSON_NAME_SUFFIX,
1206 userenv('LANG')) AS CREATED_BY, --Modify by Chaoqun 09-Mar-2009
1207 decode(pbp.exempt_flag,
1208 null, 'N',
1209 pbp.exempt_flag) as EMD_EXEMPTED_FLAG,
1210 decode(l_emd_current_status,
1211 'NOT_PAID',
1212 'N',
1213 'EXEMPTED',
1214 'N',
1215 'RECEIVING', --Modify by Chaoqun 04-Mar-2009
1216 'N',
1217 'RECEIVE_ERROR',
1218 'N',
1219 'Y') as EMD_RECEIVED_FLAG,
1220 (select petr.amount
1221 from pon_emd_transactions petr
1222 where petr.auction_header_id = p_auction_header_id
1223 and petr.supplier_sequence= p_supplier_sequence
1224 and (petr.status_lookup_code = 'RECEIVING'
1225 or petr.status_lookup_code = 'RECEIVED'
1226 or petr.status_lookup_code = 'RECEIVE_ERROR')) as EMD_RECEIVED_AMOUNT,
1227 (select petr.TRANSACTION_DATE
1228 from pon_emd_transactions petr
1229 where petr.auction_header_id = p_auction_header_id
1230 and petr.supplier_sequence= p_supplier_sequence
1231 and (petr.status_lookup_code = 'RECEIVING'
1232 or petr.status_lookup_code = 'RECEIVED'
1233 or petr.status_lookup_code = 'RECEIVE_ERROR')) as EMD_RECEIVED_DATE,
1234 (select flv.meaning
1235 from PON_EMD_TRANSACTIONS petr,
1236 fnd_lookup_values flv
1237 where petr.AUCTION_HEADER_ID = p_auction_header_id
1238 AND petr.SUPPLIER_SEQUENCE = p_supplier_sequence
1239 AND flv.lookup_type = 'PON_EMD_PAYMENT_METHOD'
1240 AND flv.language = USERENV('LANG')
1241 AND flv.lookup_code = petr.PAYMENT_TYPE_CODE
1242 and (petr.status_lookup_code = 'RECEIVING'
1243 or petr.status_lookup_code = 'RECEIVED'
1244 or petr.status_lookup_code = 'RECEIVE_ERROR')) as EMD_PAYMENT_TYPE,
1245 decode(l_emd_current_status, 'REFUNDED', 'Y', 'N') as EMD_REFUNDED_FLAG,
1246 (select petr.amount
1247 from pon_emd_transactions petr
1248 where petr.auction_header_id = p_auction_header_id
1249 and petr.supplier_sequence= p_supplier_sequence
1250 and (petr.status_lookup_code = 'REFUNDING'
1251 or petr.status_lookup_code = 'REFUNDED'
1252 or petr.status_lookup_code = 'REFUND_ERROR')) as EMD_REFUNDED_AMOUNT,
1253 (select petr.TRANSACTION_DATE
1254 from pon_emd_transactions petr
1255 where petr.auction_header_id = p_auction_header_id
1256 and petr.supplier_sequence= p_supplier_sequence
1257 and (petr.status_lookup_code = 'REFUNDING'
1258 or petr.status_lookup_code = 'REFUNDED'
1259 or petr.status_lookup_code = 'REFUND_ERROR')) as EMD_REFUNDED_DATE,
1260 decode(l_emd_current_status, 'FORFEITED', 'Y', 'N') as EMD_FORFEITED_FLAG,
1261 (select petr.amount
1262 from pon_emd_transactions petr
1263 where petr.auction_header_id = p_auction_header_id
1264 and petr.supplier_sequence= p_supplier_sequence
1265 and (petr.status_lookup_code = 'FORFEITING'
1266 or petr.status_lookup_code = 'FORFEITED'
1267 or petr.status_lookup_code = 'FORFEIT_ERROR')) as EMD_FORFEITED_AMOUNT,
1268 (select petr.TRANSACTION_DATE
1269 from pon_emd_transactions petr
1270 where petr.auction_header_id = p_auction_header_id
1271 and petr.supplier_sequence= p_supplier_sequence
1272 and (petr.status_lookup_code = 'FORFEITING'
1273 or petr.status_lookup_code = 'FORFEITED'
1274 or petr.status_lookup_code = 'FORFEIT_ERROR')) as EMD_FORFEITED_DATE,
1275 cursor (select petr.status_lookup_code as EMD_DETAIL_STATUS,
1276 petr.bank_name as EMD_DETAIL_BANKNAME,
1277 petr.bank_branch_name as EMD_DETAIL_BRANCHNAME,
1278 petr.transaction_currency_code as EMD_DETAIL_CURRCODE,
1279 petr.amount as EMD_DETAIL_AMOUNT,
1280 petr.TRANSACTION_DATE as EMD_DETAIL_TRXDATE,
1281 petr.justification as EMD_DETAIL_JUSTIFICATION,
1282 petr.cust_trx_number as EMD_DETAIL_TRX_NO,
1283 --Begin: Addde by Chaoqun on 22-DEC-2008
1284 paha.emd_type as EMD_TYPE,
1285 cc.masked_cc_number as EMD_DETAIL_CRE_NO,
1286 petr.payment_type_code as EMD_PAYMENT_TYPE_CODE,
1287 petr.CHEQUE_NUMBER as EMD_CHEQUE_NUM,
1288 decode(petr.status_lookup_code,
1289 'REFUNDED',
1290 petr.emd_transaction_id,
1291 null) as EMD_REFUND_ID,
1292 decode(petr.status_lookup_code,
1293 'RECEIVED',
1294 petr.DOCUMENT_NUMBER,
1295 null) as EMD_REC_DOCUMENT_NUM,
1296 decode(petr.status_lookup_code,
1297 'REFUNDED',
1298 petr.DOCUMENT_NUMBER,
1299 null) as EMD_REF_DOCUMENT_NUM,
1300 decode(petr.status_lookup_code,
1301 'RECEIVED',
1302 petr.bank_account_num,
1303 null) as EMD_REC_BANK_ACCOUNT_NUM,
1304 decode(petr.status_lookup_code,
1305 'REFUNDED',
1306 petr.bank_account_num,
1307 null) as EMD_REF_BANK_ACCOUNT_NUM,
1308 petr.CASH_BEARER_NAME as EMD_CASH_BEARER_NAME,
1309 petr.DEMAND_DRAFT_NUM as EMD_DEMAND_DRAFT_NUM,
1310 petr.PAYABLE_AT as EMD_PAYABLE_AT,
1311 petr.BANK_GURANTEE_NUMBER as EMD_BANK_GUR_NUM,
1312 petr.In_Favor_Of as EMD_IN_FAVOR_OF,
1313 petr.NAME_ON_CARD as EMD_CARD_HOLDER_NAME,
1314 petr.EXPIRY_DATE as EMD_EXPIRATION_DATE,
1315 petr.TYPE_OF_CARD as EMD_TYPE_OF_CARD,
1316 flv.meaning as EMD_DETAIL_PAYTYPE,
1317 decode(petr.status_lookup_code,
1318 'RECEIVED',
1319 petr.Cust_Trx_Number,
1320 null) as EMD_REC_TRAN_NUM,
1321 decode(petr.status_lookup_code,
1322 'RECEIVED',
1323 x_rec_rec_num,
1324 null) as EMD_REC_REC_NUM,
1325 decode(petr.status_lookup_code,
1326 'REFUNDED',
1327 petr.Cust_Trx_Number,
1328 null) as EMD_CREDIT_MEMO_NUM,
1329 decode(petr.status_lookup_code,
1330 'FORFEITED',
1331 petr.Cust_Trx_Number,
1332 null) as EMD_FORFEIT_TRANS_NUM,
1333 decode(petr.status_lookup_code,
1334 'REFUNDED',
1335 (select aia.invoice_num
1336 from ap_invoices_all aia
1337 where aia.invoice_id = petr.application_ref_id),
1338 null) as EMD_PAY_INV_NUM,
1339 decode(petr.status_lookup_code,
1340 'REFUNDED',
1341 (select apsa.payment_num
1342 from ap_payment_schedules_all apsa
1343 where apsa.invoice_id = petr.application_ref_id),
1344 null) as EMD_PAY_PAY_NUM,
1345 decode(petr.status_lookup_code,
1346 'REFUNDED',
1347 petr.JUSTIFICATION,
1348 null) as EMD_REFUND_JUSTIFICATION,
1349 decode(petr.status_lookup_code,
1350 'FORFEITED',
1351 petr.JUSTIFICATION,
1352 null) as EMD_FORFEIT_JUSTIFICATION,
1353 --End: Addde by Chaoqun on 22-DEC-2008
1354 petr.emd_transaction_id as TRX_ID
1355 from pon_emd_transactions petr,
1356 pon_bidding_parties pbp,
1357 fnd_lookup_values flv,
1358 IBY_CREDITCARD cc
1359 where petr.auction_header_id = p_auction_header_id
1360 and pbp.auction_header_id= p_auction_header_id
1361 and pbp.sequence= p_supplier_sequence
1362 and petr.supplier_sequence(+)=pbp.sequence
1363 --Added by Chaoqun on 22-DEC-2008
1364 and cc.CARD_OWNER_ID(+) = petr.card_owner_id
1365 and cc.CARD_ISSUER_CODE(+) = petr.CARD_ISSUER_CODE
1366 and cc.CHNAME(+) = petr.NAME_ON_CARD
1367 and cc.CCNUMBER(+) = petr.credit_card_num
1368 and flv.lookup_type(+) = 'PON_EMD_PAYMENT_METHOD'
1369 and flv.language(+) = USERENV('LANG')
1370 and flv.LOOKUP_CODE(+) = petr.Payment_Type_Code
1371 -----------------------------------
1372 order by TRX_ID) as EMD_DETAILS,
1373 cursor (select message_name, message_text
1374 from fnd_new_messages
1375 where message_name in
1376 ('PON_EMD_NEGOTIATION_NO',
1377 'PON_EMD_NEGOTIATION_TITLE',
1378 'PON_EMD_SUPPLIER_NAME', 'PON_EMD_SUPPLIER_SITE',
1379 'PON_EMD_AMOUNT', 'PON_EMD_DUE_DATE',
1380 'PON_EMD_TYPE', 'PON_EMD_GUARANTEE_EXPIRY_DATE',
1381 'PON_EMD_QUOTE_CURRENCY',
1382 'PON_EMD_CURRENT_STATUS', 'PON_EMD_SUMMARY',
1383 'PON_EMD_PAYMENT_DETAILS',
1384 'PON_EMD_REFUND_DETAILS',
1385 'PON_EMD_FORFEIT_DETAILS', 'PON_EMD_EXEMPTED',
1386 'PON_EMD_RECEIVED', 'PON_EMD_RECEIVED_AMOUNT',
1387 'PON_EMD_RECEIVED_DATE', 'PON_EMD_REFUNDED',
1388 'PON_EMD_REFUNDED_AMOUNT',
1389 'PON_EMD_REFUNDED_DATE', 'PON_EMD_FORFEITED',
1390 'PON_EMD_FORFEIT_AMOUNT', 'PON_EMD_FORFEIT_DATE',
1391 'PON_EMD_BANK_NAME', 'PON_EMD_BRANCH_NAME',
1392 'PON_EMD_DETAIL_BANK_ACCOUNT',
1393 'PON_EMD_PAYMENT_TYPE', 'PON_EMD_CURRENCY',
1394 'PON_EMD_PAYMENT_DATE', 'PON_EMD_DETAIL_AMOUNT',
1395 --Added by Chaoqun on 22-DEC-2008
1396 'PON_EMD_CHEQUE_NUM','PON_EMD_CASH_BEARER_NAME',
1397 'PON_EMD_DEMAND_DRAFT_NUM', 'PON_EMD_PAYABLE_AT' ,
1398 'PON_EMD_BANK_GUR_NUM','PON_EMD_IN_FAVOR_OF',
1399 'PON_EMD_CARD_HOLDER_NAME','PON_EMD_EXPIRATION_DATE',
1400 'PON_EMD_TYPE_OF_CARD', 'PON_EMD_REC_TRAN_NUM',
1401 'PON_EMD_REC_REC_NUM', 'PON_EMD_CREDIT_CARD_NUM',
1402 'PON_EMD_DOCUMENT_NUM', 'PON_EMD_JUSTIFICATION',
1403 'PON_EMD_REC_CREDIT_NUM',
1404 'PON_EMD_PAY_INV_NUM', 'PON_EMD_PAY_PAY_NUM',
1405 'PON_EMD_REFUND_ID', 'PON_EMD_CREATED_BY' ,
1406 'PON_EMD_SUPPLIER_HEADING' --Added by Chaoqun on 15-Mar-2009 for UI Change
1407 ---------------------------------
1408 )
1409 and application_id = 396
1410 and language_code = l_printing_language) as GENERIC_MESSAGES
1411 from pon_auction_headers_all paha,
1412 pon_emd_transactions petr,
1413 pon_bidding_parties pbp,
1414 fnd_lookup_values fl,
1415 fnd_lookups fl1,
1416 pon_auc_doctypes doc,
1417 HZ_PARTIES hz
1418 where paha.auction_header_id = p_auction_header_id
1419 and pbp.auction_header_id = paha.auction_header_id
1420 --Begin: Added by Chaoqun on 22-DEC-2008
1421 and pbp.sequence = petr.supplier_sequence
1422 and petr.auction_header_id = paha.auction_header_id
1423 and petr.supplier_sequence = p_supplier_sequence
1424 and decode(petr.current_row_flag,null,'Y',petr.current_row_flag) = 'Y'
1425 and fl.lookup_type(+) = 'PON_AUCTION_EMD_TYPE'
1426 and fl.language(+) = USERENV('LANG')
1427 and fl.LOOKUP_CODE(+) = paha.EMD_TYPE
1428 and fl1.lookup_type = 'PON_AUCTION_DOC_TYPES'
1429 and fl1.lookup_code = doc.internal_name
1430 and paha.doctype_id = doc.doctype_id
1431 and HZ.party_id(+) = paha.trading_partner_contact_id;
1432 --END: Added by Chaoqun on 22-DEC-2008
1433 END IF;
1434
1435 dbms_lob.createtemporary(result, TRUE);
1436
1437 SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
1438
1439 xml_stmt := 'DECLARE
1440 queryCtx DBMS_XMLGEN.ctxHandle;
1441 BEGIN
1442 queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1443 DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1444 DBMS_XMLGEN.closeContext (queryCtx);
1445 exception when others then
1446 DBMS_XMLGEN.closeContext (queryCtx);
1447 RAISE;
1448 END;';
1449 execute immediate xml_stmt
1450 USING IN OUT xml_query_cursor, IN OUT result;
1451
1452 SELECT CURRENT_DATE INTO l_end_time FROM DUAL;
1453
1454 CLOSE xml_query_cursor;
1455
1456 IF l_statement_log_level >= l_current_log_level THEN
1457 FND_LOG.string(l_statement_log_level,
1458 l_module_name,
1459 'PDF: generating XML time: ' ||
1460 (l_end_time - l_start_time) * 24 * 60 * 60);
1461 END IF;
1462
1463 return result;
1464 END generate_supplier_xml;
1465
1466 -------------------------------------------------------------------------*\
1467 -- 10-Dec-2008 Lion Li Create |
1468 -- Function Name: GENERATE_EMD_FORFEIT_XML |
1469 -- This function is used to query data for receipt to individual supplier |
1470 -------------------------------------------------------------------------*/
1471
1472 FUNCTION generate_receipt_xml(p_auction_header_id IN NUMBER,
1473 p_supplier_sequence IN NUMBER) RETURN CLOB IS
1474 result CLOB;
1475 TYPE emd_supplier_cursor_type IS REF CURSOR;
1476 xml_query_cursor emd_supplier_cursor_type;
1477 xml_stmt varchar2(500);
1478 l_printing_language VARCHAR2(3) := userenv('lang');
1479 l_start_time DATE;
1480 l_end_time DATE;
1481 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1482 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1483 l_module_name VARCHAR2(80) := 'pon.plsql.PON_PRINTING_PKG.GENERATE_RECEIPT_XML';
1484 --p_supplier_id := 12438;
1485 --l_emd_current_status pon_emd_transactions.status_lookup_code%type;
1486 TYPE emd_current_status_type is ref cursor;
1487 emd_current_status emd_current_status_type;
1488
1489 BEGIN
1490 --Begin: Deleted by Chaoqun on 19-Mar-2009
1491
1492 /* open emd_current_status for
1493 select petr.status_lookup_code
1494 from pon_emd_transactions petr
1495 where petr.auction_header_id = p_auction_header_id
1496 and petr.supplier_sequence= p_supplier_sequence
1497 order by petr.emd_transaction_id;
1498
1499 loop
1500 fetch emd_current_status
1501 into l_emd_current_status;
1502 exit when emd_current_status%notfound;
1503 end loop;
1504 close emd_current_status;*/
1505
1506 --End: Deleted by Chaoqun on 19-Mar-2009
1507
1508
1509 OPEN xml_query_cursor FOR
1510 select 'Y' as SUPPLIER_REPORT,
1511 paha.document_number as NEGOTIATION_NUMBER,
1512 paha.auction_title as NEGOTIATION_TITLE,
1513 pbp.trading_partner_name as SUPPLIER_NAME,
1514 pbp.vendor_site_code as SUPPLIER_SITE,
1515 paha.emd_amount as EMD_AMOUNT,
1516 paha.emd_due_date as EMD_DUE_DATE,
1517 paha.emd_type as EMD_TYPE,
1518 flv.meaning as EMD_TYPE_MEANING,
1519 paha.emd_guarantee_expiry_date as EMD_GUARANTEE_EXPIRY_DATE,
1520 --pbp.bid_currency_code as EMD_CURR_CODE,
1521 paha.currency_code as EMD_CURR_CODE, --Modify by Chaoqun on 19-Mar-2009
1522 --l_emd_current_status as EMD_CURRENT_STATUS,
1523 pbp.exempt_flag as EMD_EXEMPTED_FLAG,
1524 PON_LOCALE_PKG.party_display_name(hz.person_first_name,
1525 hz.PERSON_LAST_NAME,
1526 hz.person_middle_name,
1527 f1.MEANING,
1528 hz.PERSON_NAME_SUFFIX,
1529 userenv('LANG'))
1530 as EMD_CREATED_BY ,
1531 --Begin: Added by Chaoqun on 20-Mar-2009
1532 'Y' as EMD_RECEIVED_FLAG,
1533 cursor (select petr.status_lookup_code as EMD_DETAIL_STATUS,
1534 petr.bank_name as EMD_DETAIL_BANKNAME,
1535 petr.bank_branch_name as EMD_DETAIL_BRANCHNAME,
1536 petr.transaction_currency_code as EMD_DETAIL_CURRCODE,
1537 petr.amount as EMD_DETAIL_AMOUNT,
1538 petr.TRANSACTION_DATE as EMD_DETAIL_TRXDATE,
1539 petr.justification as EMD_DETAIL_JUSTIFICATION,
1540 petr.cust_trx_number as EMD_DETAIL_TRX_NO,
1541 paha.emd_type as EMD_TYPE,
1542 cc.masked_cc_number as EMD_DETAIL_CRE_NO,
1543 petr.payment_type_code as EMD_PAYMENT_TYPE_CODE,
1544 petr.CHEQUE_NUMBER as EMD_CHEQUE_NUM,
1545 petr.bank_account_num as EMD_REC_BANK_ACCOUNT_NUM,
1546 petr.CASH_BEARER_NAME as EMD_CASH_BEARER_NAME,
1547 petr.DEMAND_DRAFT_NUM as EMD_DEMAND_DRAFT_NUM,
1548 petr.PAYABLE_AT as EMD_PAYABLE_AT,
1549 petr.BANK_GURANTEE_NUMBER as EMD_BANK_GUR_NUM,
1550 petr.In_Favor_Of as EMD_IN_FAVOR_OF,
1551 petr.NAME_ON_CARD as EMD_CARD_HOLDER_NAME,
1552 petr.EXPIRY_DATE as EMD_EXPIRATION_DATE,
1553 petr.TYPE_OF_CARD as EMD_TYPE_OF_CARD,
1554 flv.meaning as EMD_DETAIL_PAYTYPE,
1555 petr.emd_transaction_id as TRX_ID
1556 from pon_emd_transactions petr,
1557 fnd_lookup_values flv,
1558 IBY_CREDITCARD cc
1559 where petr.auction_header_id = p_auction_header_id
1560 and petr.supplier_sequence = p_supplier_sequence
1561 and petr.status_lookup_code = 'RECEIVED'
1562 and cc.CARD_OWNER_ID(+) = petr.card_owner_id
1563 and cc.CARD_ISSUER_CODE(+) = petr.CARD_ISSUER_CODE
1564 and cc.CHNAME(+) = petr.NAME_ON_CARD
1565 and cc.CCNUMBER(+) = petr.credit_card_num
1566 and flv.lookup_type(+) = 'PON_EMD_PAYMENT_METHOD'
1567 and flv.language(+) = USERENV('LANG')
1568 and flv.LOOKUP_CODE(+) = petr.Payment_Type_Code
1569 order by TRX_ID) as EMD_DETAILS,
1570 --End: Added by Chaoqun on 20-Mar-2009
1571
1572 --Begin: Deleted by Chaoqun on 19-Mar-2009
1573 /*cursor (select decode(petr.status_lookup_code,
1574 'RECEIVED','Y',
1575 'N') as EMD_RECEIVED_FLAG,
1576 petr.status_lookup_code as EMD_DETAIL_STATUS,
1577 petr.bank_name as EMD_DETAIL_BANKNAME,
1578 petr.bank_branch_name as EMD_DETAIL_BRANCHNAME,
1579 petr.bank_account_num as EMD_DETAIL_ACCOUNTNO,
1580 petr.transaction_currency_code as EMD_DETAIL_CURRENCY,
1581 petr.amount as EMD_DETAIL_AMOUNT,
1582 petr.TRANSACTION_DATE as EMD_DETAIL_TRXDATE,
1583 petr.Payment_Type_Code as EMD_DETAIL_PAYTYPE,
1584 petr.justification as EMD_DETAIL_JUSTIFICATION,
1585 petr.document_number as EMD_DETAIL_DOC_NO,
1586 petr.cust_trx_number as EMD_DETAIL_TRX_NO,
1587 petr.credit_card_num as EMD_DETAIL_CRE_NO,
1588 petr.emd_transaction_id as TRX_ID
1589 from pon_emd_transactions petr
1590 where petr.auction_header_id = p_auction_header_id
1591 and petr.supplier_sequence= p_supplier_sequence
1592 and (petr.status_lookup_code = 'RECEIVED'
1593 or petr.status_lookup_code = 'RECEIVE_ERROR'
1594 or petr.status_lookup_code = 'RECEIVING'
1595 )
1596 ) as EMD_DETAILS,*/
1597 --End: Deleted by Chaoqun on 19-Mar-2009
1598
1599 cursor (select message_name, message_text
1600 from fnd_new_messages
1601 where message_name in
1602 ('PON_EMD_NEGOTIATION_NO',
1603 'PON_EMD_NEGOTIATION_TITLE',
1604 'PON_EMD_SUPPLIER_NAME', 'PON_EMD_SUPPLIER_SITE',
1605 'PON_EMD_AMOUNT', 'PON_EMD_DUE_DATE',
1606 'PON_EMD_TYPE', 'PON_EMD_GUARANTEE_EXPIRY_DATE',
1607 'PON_EMD_QUOTE_CURRENCY',
1608 'PON_EMD_CURRENT_STATUS', 'PON_EMD_SUMMARY',
1609 'PON_EMD_PAYMENT_DETAILS', 'PON_EMD_EXEMPTED',
1610 'PON_EMD_RECEIVED', 'PON_EMD_RECEIVED_AMOUNT',
1611 'PON_EMD_RECEIVED_DATE', 'PON_EMD_RECEIVED_CURRENCY',
1612 'PON_EMD_BANK_NAME', 'PON_EMD_BRANCH_NAME',
1613 'PON_EMD_DETAIL_BANK_ACCOUNT',
1614 'PON_EMD_PAYMENT_TYPE', 'PON_EMD_CURRENCY',
1615 'PON_EMD_PAYMENT_DATE', 'PON_EMD_DETAIL_AMOUNT',
1616 --Added by Chaoqun on 19-Mar-2008
1617 'PON_EMD_CHEQUE_NUM','PON_EMD_CASH_BEARER_NAME',
1618 'PON_EMD_DEMAND_DRAFT_NUM', 'PON_EMD_PAYABLE_AT' ,
1619 'PON_EMD_BANK_GUR_NUM','PON_EMD_IN_FAVOR_OF',
1620 'PON_EMD_CARD_HOLDER_NAME','PON_EMD_EXPIRATION_DATE',
1621 'PON_EMD_TYPE_OF_CARD', 'PON_EMD_REC_TRAN_NUM',
1622 'PON_EMD_REC_REC_NUM', 'PON_EMD_CREDIT_CARD_NUM',
1623 'PON_EMD_CREATED_BY', 'PON_EMD_PAY_REC_BY',
1624 ---------------------------------
1625 'PON_EMD_RECEIPT_HEADING' --Added by Chaoqun on 16-Apr-2009 for UI Change
1626 )
1627 and application_id = 396
1628 and language_code = l_printing_language) as GENERIC_MESSAGES
1629 from pon_auction_headers_all paha,
1630 pon_emd_transactions petr,
1631 pon_bidding_parties pbp,
1632 fnd_lookup_values flv,
1633 HR_ALL_ORGANIZATION_UNITS_TL ou,
1634 pon_auc_doctypes doc,
1635 fnd_lookups f1,
1636 HZ_PARTIES hz
1637 where paha.auction_header_id = p_auction_header_id
1638 and petr.auction_header_id = paha.auction_header_id
1639 and petr.supplier_sequence = p_supplier_sequence
1640 and pbp.auction_header_id = paha.auction_header_id
1641 and pbp.sequence = petr.supplier_sequence
1642 --Begin: Added by Chaoqun on 19-Mar-2009
1643 and petr.status_lookup_code = 'RECEIVED'
1644 and flv.lookup_type(+) = 'PON_AUCTION_EMD_TYPE'
1645 and flv.language(+) = USERENV('LANG')
1646 and flv.LOOKUP_CODE(+) = paha.EMD_TYPE
1647 --End: Added by Chaoqun on 19-Mar-2009
1648 and ou.ORGANIZATION_ID = paha.org_id
1649 and ou.language = userenv('lang')
1650 and doc.doctype_id = paha.doctype_id
1651 and f1.lookup_type = 'PON_AUCTION_DOC_TYPES'
1652 and f1.lookup_code = doc.internal_name
1653 and hz.party_id(+) = paha.trading_partner_contact_id;
1654
1655 dbms_lob.createtemporary(result, TRUE);
1656
1657 SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
1658
1659 xml_stmt := 'DECLARE
1660 queryCtx DBMS_XMLGEN.ctxHandle;
1661 BEGIN
1662 queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1663 DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1664 DBMS_XMLGEN.closeContext (queryCtx);
1665 exception when others then
1666 DBMS_XMLGEN.closeContext (queryCtx);
1667 RAISE;
1668 END;';
1669 execute immediate xml_stmt
1670 USING IN OUT xml_query_cursor, IN OUT result;
1671
1672 SELECT CURRENT_DATE INTO l_end_time FROM DUAL;
1673
1674 CLOSE xml_query_cursor;
1675
1676 IF l_statement_log_level >= l_current_log_level THEN
1677 FND_LOG.string(l_statement_log_level,
1678 l_module_name,
1679 'PDF: generating XML time: ' ||
1680 (l_end_time - l_start_time) * 24 * 60 * 60);
1681 END IF;
1682
1683 return result;
1684 END generate_receipt_xml;
1685
1686 -------------------------------------------------------------------------------
1687 -- If p_bid_number > 0 , Creates a xml file for the bid number and returns --
1688 -- it as a clob. If p_bid_number <= 0, creates a xml file for the auction id --
1689 -------------------------------------------------------------------------------
1690 FUNCTION generate_auction_xml(p_auction_header_id IN NUMBER,
1691 p_client_time_zone IN VARCHAR2,
1692 p_server_time_zone IN VARCHAR2,
1693 p_date_format IN VARCHAR2,
1694 p_trading_partner_id IN NUMBER,
1695 p_trading_partner_name IN VARCHAR2,
1696 p_vendor_site_id IN NUMBER,
1697 p_user_view_type IN VARCHAR2,
1698 p_printing_warning_flag IN VARCHAR2 DEFAULT 'N',
1699 p_neg_printed_with_contracts IN VARCHAR2 DEFAULT 'N',
1700 p_requested_supplier_id IN NUMBER,
1701 p_requested_supplier_name IN VARCHAR2,
1702 p_trading_partner_contact_id IN NUMBER,
1703 p_bid_number IN NUMBER,
1704 p_user_id IN NUMBER)
1705 RETURN CLOB IS
1706 result CLOB;
1707 l_suffix varchar2(2);
1708 l_resultOffset number;
1709 l_xml_header varchar2(100);
1710 l_xml_header_length number;
1711 TYPE auction_header_cursor_type IS REF CURSOR;
1712 xml_query_cursor auction_header_cursor_type;
1713
1714 queryCtx DBMS_XMLGEN.ctxHandle;
1715
1716 attachments_cursor auction_header_cursor_type;
1717 doc_rules_cursor auction_header_cursor_type;
1718 generic_msgs_cursor auction_header_cursor_type;
1719 doc_msgs_cursor auction_header_cursor_type;
1720 lines_cursor auction_header_cursor_type;
1721 collab_team_cursor auction_header_cursor_type;
1722 scoring_team_cursor auction_header_cursor_type;
1723 scoring_mems_cursor auction_header_cursor_type;
1724 scoring_secs_cursor auction_header_cursor_type;
1725 abstracts_cursor auction_header_cursor_type;
1726 currency_cursor auction_header_cursor_type;
1727 invited_supp_cur_cursor auction_header_cursor_type;
1728 header_attr_cursor auction_header_cursor_type;
1729 invited_supp_cursor auction_header_cursor_type;
1730 line_attr_cursor auction_header_cursor_type;
1731 line_attr_score_cursor auction_header_cursor_type;
1732 pf_cursor auction_header_cursor_type;
1733 line_pf_cursor auction_header_cursor_type;
1734 buyer_pf_cursor auction_header_cursor_type;
1735 dist_buyer_pf_cursor auction_header_cursor_type;
1736 large_neg_bur_pf_cursor auction_header_cursor_type;
1737 item_pb_cursor auction_header_cursor_type;
1738 item_quan_cursor auction_header_cursor_type;
1739 pay_items_cursor auction_header_cursor_type;
1740 pb_loc_cursor auction_header_cursor_type;
1741 line_price_diff_cursor auction_header_cursor_type;
1742 item_price_diff_cursor auction_header_cursor_type;
1743 price_diff_types_cursor auction_header_cursor_type;
1744
1745
1746 xml_clob CLOB;
1747
1748 xml_res XMLType;
1749 attachments_res XMLType;
1750 doc_rules_res XMLType;
1751 generic_msgs_res XMLType;
1752 doc_msgs_res XMLType;
1753 lines_res XMLType;
1754 collab_team_res XMLType;
1755 scoring_team_res XMLType;
1756 scoring_mems_res XMLType;
1757 scoring_secs_res XMLType;
1758 abstracts_res XMLType;
1759 currency_res XMLType;
1760 invited_supp_cur_res XMLType;
1761 header_attr_res XMLType;
1762 invited_supp_res XMLType;
1763 line_attr_res XMLType;
1764 line_attr_score_res XMLType;
1765 pf_res XMLType;
1766 line_pf_res XMLType;
1767 buyer_pf_res XMLType;
1768 dist_buyer_pf_res XMLType;
1769 large_neg_bur_pf_res XMLType;
1770 item_pb_res XMLType;
1771 item_quan_res XMLType;
1772 pay_items_res XMLType;
1773 pb_loc_res XMLType;
1774 line_price_diff_res XMLType;
1775 item_price_diff_res XMLType;
1776 price_diff_types_res XMLType;
1777
1778 res CLOB;
1779
1780 xml_stmt varchar2(500);
1781 l_neg_tp_id pon_auction_headers_all.trading_partner_id%type;
1782 l_doc_type_id pon_auction_headers_all.DOCTYPE_ID%TYPE;
1783 l_doc_type VARCHAR2(50);
1784 l_currency_code pon_auction_headers_all.currency_code%type;
1785 l_price_precision pon_auction_headers_all.number_price_decimals%type;
1786 l_price_mask varchar2(80); -- mask based on the negotiation price precision
1787 l_amount_mask varchar2(80); -- mask based on the negotiation currency precision
1788
1789 l_contracts_installed VARCHAR2(1);
1790 l_cont_attach_doc_flag VARCHAR2(1);
1791 l_cont_nonmerge_flag VARCHAR2(1);
1792 l_printing_language VARCHAR2(3);
1793 l_supplier_sequence_number NUMBER;
1794 l_award_approval_enabled VARCHAR2(1);
1795 l_neg_has_price_breaks VARCHAR2(1);
1796
1797 l_start_time DATE;
1798 l_end_time DATE;
1799 l_current_log_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1800 l_statement_log_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1801 l_module_name VARCHAR2(80) := 'pon.plsql.PON_PRINTING_PKG.GENERATE_AUCTION_XML';
1802
1803
1804 l_enfrc_prevrnd_bid_price_flag pon_auction_headers_all.enforce_prevrnd_bid_price_flag%TYPE;
1805 l_auction_header_id_prev_round pon_auction_headers_all.auction_header_id_prev_round%TYPE;
1806 l_start_price_from_prev_rnd VARCHAR2(1);
1807 l_prev_rnd_bid_number pon_bid_headers.bid_number%TYPE;
1808 l_contract_type pon_auction_headers_all.contract_type%TYPE;
1809 l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
1810 l_pf_type_allowed pon_auction_headers_all.pf_type_allowed%TYPE;
1811
1812 --bidpdf:
1813 l_is_bidpdf VARCHAR2(1) := 'Y';
1814 l_is_supplier_bidpdf VARCHAR2(1) := 'N';
1815 l_is_buyer_negpdf VARCHAR2(1) := 'N';
1816
1817 l_bid_currency_code pon_bid_headers.bid_currency_code%type;
1818 l_bid_price_precision pon_bid_headers.number_price_decimals%type;
1819 -- bidpdf: address of supplier:
1820 l_supplier_address_line1 hz_parties.address1%type;
1821 l_supplier_address_line2 hz_parties.address2%type;
1822 l_supplier_address_line3 hz_parties.address3%type;
1823 l_supplier_address_city hz_parties.city%type;
1824 l_supplier_address_state hz_parties.state%type;
1825 l_supplier_postal_code hz_parties.postal_code%type;
1826 l_supplier_country_code hz_parties.country%type;
1827 l_supplier_country fnd_territories_tl.territory_short_name%type;
1828 l_vendor_site_id NUMBER;
1829 l_vendor_id NUMBER;
1830 -- bidpdf: contact details:
1831 l_contact_details_name varchar2(600);
1832 l_trading_partner_id NUMBER := p_trading_partner_id;
1833 -- bidpdf: on buyer side, check whether buyer in scoring team that cannot see price
1834 l_price_visibility VARCHAR2(1) := 'Y';
1835 l_is_section_restricted VARCHAR2(1) := 'N';
1836 l_proxybid_display_flag VARCHAR2(1) := 'N';
1837 -- bidpdf: currency change rate
1838 l_rate NUMBER := 1;
1839 l_bid_rate NUMBER := 1;
1840 l_is_super_large_neg VARCHAR2(1) := 'N';
1841 -- previous round doc type, used to decide whether to display
1842 -- control "enforce supplie's previous round price as bid start price"
1843 l_prev_rnd_doctype pon_auc_doctypes.internal_name%TYPE;
1844 -- two-part RFQ
1845 l_hide_comm_part VARCHAR2(1) := 'N';
1846 l_two_part_flag pon_auction_headers_all.two_part_flag%TYPE; -- two-part flag
1847 -- commercial lock status
1848 l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%TYPE;
1849 -- technical shortlist status
1850 l_tech_shortlist_flag pon_bid_headers.technical_shortlist_flag%type;
1851 --added by Allen Yang for Surrogate Bid 2008/09/04
1852 ---------------------------------------------------------------
1853 l_tech_evaluation_status PON_AUCTION_HEADERS_ALL.Technical_Evaluation_Status%TYPE;
1854 l_surrogate_bid_flag PON_BID_HEADERS.Surrog_Bid_Flag%TYPE;
1855 CURSOR tech_surrogate_bid_cur IS
1856 SELECT
1857 paha.Technical_Evaluation_Status
1858 , pbh.SURROG_BID_FLAG
1859 FROM
1860 pon_auction_headers_all paha, pon_bid_headers pbh
1861 WHERE paha.auction_header_id=pbh.auction_header_id
1862 AND paha.auction_header_id=p_auction_header_id
1863 AND pbh.bid_number = p_bid_number;
1864 ----------------------------------------------------------------
1865
1866 -- used to set what categories are shown in quote pdf.
1867 -- this option can have 3 values:
1868 -- 1 (`FromSupplier')
1869 -- 2 (`FromSupplierTechnical')
1870 -- 3 (`FromSupplierTechnical', `FromSupplierCommercial')
1871 l_attach_categ_option NUMBER := 1;
1872
1873 -- three variables to store two-part messages.
1874 l_two_part_general_msg fnd_new_messages.message_text%TYPE;
1875 l_two_part_tech_msg fnd_new_messages.message_text%TYPE;
1876 l_two_part_comm_msg fnd_new_messages.message_text%TYPE;
1877
1878 linesCtx DBMS_XMLGEN.ctxHandle;
1879 attrsCtx DBMS_XMLGEN.ctxHandle;
1880 payItemsCtx DBMS_XMLGEN.ctxHandle;
1881 linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882 itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883 itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884 quanTiersCtx DBMS_XMLGEN.ctxHandle;
1885 pfCtx DBMS_XMLGEN.ctxHandle;
1886
1887 CURSOR line_num_cur IS (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id);
1888 line_num NUMBER;
1889
1890 BEGIN
1891 IF p_bid_number <= 0 THEN
1892 l_is_bidpdf := 'N';
1893 ELSE
1894 l_is_bidpdf := 'Y';
1895 BEGIN
1896 select
1897 bid_currency_code,
1898 number_price_decimals,
1899 vendor_site_id,
1900 vendor_id,
1901 trading_partner_id,
1902 rate,
1903 technical_shortlist_flag
1904 into
1905 l_bid_currency_code,
1906 l_bid_price_precision,
1907 l_vendor_site_id,
1908 l_vendor_id,
1909 l_trading_partner_id,
1910 l_bid_rate,
1911 l_tech_shortlist_flag
1912 from pon_bid_headers
1913 where bid_number = p_bid_number;
1914 EXCEPTION
1915 WHEN no_data_found THEN
1916 l_is_bidpdf := 'N';
1917 END;
1918 END IF;
1919 select
1920 trading_partner_id,
1921 doctype_id,
1922 currency_code,
1923 number_price_decimals,
1924 enforce_prevrnd_bid_price_flag,
1925 auction_header_id_prev_round,
1926 contract_type,
1927 supplier_view_type,
1928 pf_type_allowed,
1929 nvl(two_part_flag, 'N'),
1930 nvl(sealed_auction_status,' ')
1931 into
1932 l_neg_tp_id,
1933 l_doc_type_id,
1934 l_currency_code,
1935 l_price_precision,
1936 l_enfrc_prevrnd_bid_price_flag,
1937 l_auction_header_id_prev_round,
1938 l_contract_type,
1939 l_supplier_view_type,
1940 l_pf_type_allowed,
1941 l_two_part_flag,
1942 l_commercial_lock_status
1943 from pon_auction_headers_all
1944 where auction_header_id = p_auction_header_id;
1945
1946 IF l_auction_header_id_prev_round IS NOT NULL
1947 THEN
1948 BEGIN
1949 SELECT doctypes.internal_name
1950 INTO l_prev_rnd_doctype
1951 FROM pon_auction_headers_all pah, pon_auc_doctypes doctypes
1952 WHERE pah.auction_header_id = l_auction_header_id_prev_round
1953 and pah.doctype_id = doctypes.doctype_id;
1954 EXCEPTION
1955 WHEN NO_DATA_FOUND
1956 THEN
1957 l_prev_rnd_doctype := '';
1958 END;
1959 END IF;
1960
1961 IF l_statement_log_level >= l_current_log_level THEN
1962 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);
1963 END IF;
1964
1965 l_printing_language := userenv('lang') ;
1966
1967 if (PON_LARGE_AUCTION_UTIL_PKG.is_super_large_neg(p_auction_header_id)) then
1968 l_is_super_large_neg := 'Y';
1969 else
1970 l_is_super_large_neg := 'N';
1971 end if;
1972
1973 -- bidpdf: get address and contact name
1974 -- If the negotiation did not invite any supplier, get the company address
1975 -- Otherwise, get the site address
1976 IF l_is_bidpdf = 'Y' THEN
1977
1978 IF p_user_view_type = 'SUPPLIER' THEN
1979 l_is_supplier_bidpdf := 'Y';
1980 l_currency_code := l_bid_currency_code;
1981 l_price_precision := l_bid_price_precision;
1982 l_rate := l_bid_rate;
1983 ELSE
1984 l_price_visibility := is_price_visible(p_auction_header_id, p_user_id);
1985 --To decide whether need to check which sections in requirement should be displayed
1986 --As in ViewBidCO, hasScoringTeamsFlag !isScoringLocked isScorer
1987 BEGIN
1988 SELECT 'Y'
1989 INTO l_is_section_restricted
1990 FROM pon_neg_team_members pntm, pon_auction_headers_all pah
1991 WHERE pah.auction_header_id = p_auction_header_id
1992 AND pntm.menu_name = 'PON_SOURCING_SCORENEG'
1993 AND pntm.auction_header_id = pah.auction_header_id
1994 AND pntm.user_id = p_user_id
1995 AND pah.has_scoring_teams_flag = 'Y'
1996 AND pah.scoring_lock_date is null;
1997 EXCEPTION
1998 WHEN NO_DATA_FOUND then
1999 l_is_section_restricted := 'N';
2000 END;
2001 END IF;
2002
2003 IF l_vendor_site_id <= 0 THEN
2004 BEGIN
2005 -- bidpdf: address/contact of supplier company
2006 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)
2007 ,PON_LOCALE_PKG.get_party_display_name(pon_bid_headers.trading_partner_contact_id)
2008 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
2009 ,l_contact_details_name
2010 from hz_parties, pon_bid_headers, fnd_territories_tl entity_terr
2011 where pon_bid_headers.trading_partner_id = hz_parties.party_id
2012 and pon_bid_headers.bid_number = p_bid_number
2013 and entity_terr.territory_code(+) = hz_parties.country
2014 and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
2015 and entity_terr.language(+) = l_printing_language
2016 and rownum = 1;
2017 EXCEPTION
2018 WHEN no_data_found THEN
2019 l_supplier_address_line1 := '';
2020 END;
2021 ELSE
2022 BEGIN
2023 -- get supplier site address/contact, reference from java/poplist/server/VendorSitesAllVO.xml
2024 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),
2025 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
2026 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
2027 FROM PO_VENDOR_SITES_ALL pvsa, pon_auction_headers_all pah, pon_bidding_parties pbp, fnd_territories_tl entity_terr
2028 WHERE
2029 pah.auction_header_id = p_auction_header_id
2030 AND pvsa.org_id = pah.org_id
2031 AND PURCHASING_SITE_FLAG = 'Y'
2032 AND SYSDATE< NVL(INACTIVE_DATE, SYSDATE + 1)
2033 AND vendor_id=l_vendor_id
2034 AND nvl(rfq_only_site_flag, 'N')='N'
2035 AND pvsa.vendor_site_id = l_vendor_site_id
2036 AND pbp.auction_header_id = pah.auction_header_id
2037 AND pbp.vendor_site_id = pvsa.vendor_site_id
2038 and entity_terr.territory_code(+) = pvsa.country
2039 and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
2040 and entity_terr.language(+) = l_printing_language
2041 and rownum = 1;
2042 EXCEPTION
2043 WHEN no_data_found THEN
2044 l_supplier_address_line1 := '';
2045 END;
2046 END IF;
2047 ELSIF p_user_view_type = 'BUYER' THEN
2048 l_is_buyer_negpdf := 'Y';
2049 END IF;
2050 --dbms_application_info.set_client_info(204);
2051 SET_AUCTION_MASKS(l_currency_code, l_price_precision, l_price_mask, l_amount_mask);
2052
2053 l_suffix := GET_SUFFIX_FOR_MESSAGES(p_auction_header_id);
2054
2055 -- determine if there are price breaks
2056 BEGIN
2057 SELECT 'Y'
2058 INTO l_neg_has_price_breaks
2059 FROM pon_auction_shipments_all
2060 WHERE auction_header_id = p_auction_header_id
2061 AND ROWNUM = 1;
2062 EXCEPTION
2063 WHEN no_data_found THEN
2064 l_neg_has_price_breaks := 'N';
2065 END;
2066
2067 -- bidpdf: add l_is_bidpdf='Y' for case when on buyer side, the buyer cost factor values
2068 -- can be displayed. If not added, pf_values.value in cursor PRICE_FACTORS will be null
2069 -- because of condition pf_values.supplier_seq_number(+) = l_supplier_sequence_number
2070 IF (p_user_view_type = 'SUPPLIER' or l_is_bidpdf='Y') AND (l_trading_partner_id IS NOT NULL
2071 OR p_requested_supplier_id IS NOT NULL) THEN
2072 BEGIN
2073 SELECT sequence
2074 INTO l_supplier_sequence_number
2075 FROM pon_bidding_parties
2076 WHERE
2077 auction_header_id = p_auction_header_id
2078 AND ((trading_partner_id = l_trading_partner_id AND
2079 vendor_site_id = p_vendor_site_id) OR
2080 requested_supplier_id = p_requested_supplier_id);
2081 EXCEPTION
2082 WHEN no_data_found THEN
2083 l_supplier_sequence_number := NULL;
2084 END;
2085 ELSE
2086 l_supplier_sequence_number := NULL;
2087 END IF;
2088
2089 l_award_approval_enabled := fnd_profile.value('PON_AWARD_APPROVAL_ENABLED');
2090 l_contracts_installed := fnd_profile.value('POC_ENABLED');
2091
2092 --We do not want the warning about attached document in case the user does not
2093 --have privilege to view contract terms
2094 if (p_printing_warning_flag = 'Y' OR nvl(l_contracts_installed, 'N') = 'N') then
2095 l_cont_attach_doc_flag := 'N';
2096 else
2097 l_doc_type := PON_CONTERMS_UTL_PVT.get_negotiation_doc_type(l_doc_type_id);
2098 if (PON_CONTERMS_UTL_PVT.isDocumentMergeable(l_doc_type, to_number(p_auction_header_id)) = 'N') then
2099 l_cont_nonmerge_flag := 'Y';
2100 else
2101 l_cont_nonmerge_flag := 'N';
2102 end if;
2103
2104 if (PON_CONTERMS_UTL_PVT.isAttachedDocument (l_doc_type, to_number(p_auction_header_id)) = 'Y') then
2105 l_cont_attach_doc_flag := 'Y';
2106 else
2107 l_cont_attach_doc_flag := 'N';
2108 end if;
2109 end if;
2110
2111 -- If the Enforce previous round flag is set, then there may be a
2112 -- change to the way the start price is displayed
2113 -- Rules:
2114 -- Show start price from the negotiation if:
2115 -- : this is a buyer
2116 -- : this is a supplier who does not have a bid in the previous round
2117 -- If the supplier has a bid in the previous round and the flag is
2118 -- set, then
2119 -- Start price = Previous round active bid line price
2120 -- + effect of previous round supplier price factors
2121 -- + effect of current round buyer price factors
2122 -- Since it will not be possible or performant to determine this for
2123 -- every line, we will call a PL/SQL function. In order to save the
2124 -- function the trouble of determining information common to every
2125 -- row, we will derive the necessary parameters here and pass it in
2126
2127 l_start_price_from_prev_rnd := 'N';
2128
2129
2130 IF (l_is_buyer_negpdf = 'N') AND
2131 l_auction_header_id_prev_round IS NOT NULL AND
2132 l_enfrc_prevrnd_bid_price_flag = 'Y' AND
2133 p_trading_partner_id IS NOT NULL AND
2134 p_trading_partner_contact_id IS NOT NULL --{
2135 THEN
2136 -- Check if the supplier had an active bid in the previous round
2137
2138 BEGIN
2139 SELECT pbh.bid_number
2140 INTO l_prev_rnd_bid_number
2141 FROM pon_bid_headers pbh
2142 WHERE pbh.auction_header_id = l_auction_header_id_prev_round
2143 AND pbh.bid_status = 'ACTIVE'
2144 AND pbh.trading_partner_id = p_trading_partner_id
2145 AND pbh.trading_partner_contact_id = p_trading_partner_contact_id
2146 AND pbh.vendor_site_id = p_vendor_site_id;
2147
2148 l_start_price_from_prev_rnd := 'Y';
2149
2150 EXCEPTION
2151 WHEN NO_DATA_FOUND
2152 THEN
2153 l_start_price_from_prev_rnd := 'N';
2154
2155 END;
2156
2157 END IF; -- }
2158
2159 -- here the rules are checked to determine whether the commercial part is to be hidden
2160 -- check if it is a two-part RFQ, and that this is a bid pdf call.
2161 IF l_two_part_flag = 'Y' THEN -- {
2162 -- initially for two-part, need to show both attachment categories
2163 l_attach_categ_option := 3;
2164
2165 IF l_is_bidpdf = 'Y' THEN -- {
2166 -- make sure it is not the bidder himself
2167 IF p_trading_partner_id <> l_trading_partner_id THEN -- {
2168 -- is it commercially unlocked (buyer view) or unsealed (supplier view)?
2169 IF (p_user_view_type = 'BUYER' and l_commercial_lock_status <> 'LOCKED')
2170 or (p_user_view_type = 'SUPPLIER' and l_commercial_lock_status = 'ACTIVE') THEN -- {
2171
2172 -- since it is unlocked, check if bid is shortlisted
2173 IF l_tech_shortlist_flag <> 'Y' THEN -- {
2174 -- hide commercial part
2175 l_hide_comm_part := 'Y';
2176 -- show only technical attachments
2177 l_attach_categ_option := 2;
2178 END IF; -- }
2179 ELSE
2180 -- it is commercially locked, hide commercial part
2181 l_hide_comm_part := 'Y';
2182 -- show technical attachments
2183 l_attach_categ_option := 2;
2184 END IF; -- }
2185 END IF; -- }
2186 --added by Allen Yang for Surrogate Bid 2008/09/04
2187 --------------------------------------------------
2188 BEGIN
2189 OPEN tech_surrogate_bid_cur;
2190 FETCH
2191 tech_surrogate_bid_cur
2192 INTO
2193 l_tech_evaluation_status
2194 , l_surrogate_bid_flag;
2195 IF (l_tech_evaluation_status = 'NOT_COMPLETED' AND
2196 l_surrogate_bid_flag = 'Y' AND
2197 l_two_part_flag = 'Y')
2198 THEN
2199 l_hide_comm_part := 'Y';
2200 l_attach_categ_option :=2;
2201 END IF;
2202 CLOSE tech_surrogate_bid_cur;
2203 END;
2204 --------------------------------------------------
2205 END IF; -- }
2206 END IF; -- }
2207
2208 IF l_statement_log_level >= l_current_log_level THEN
2209 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);
2210 END IF;
2211
2212 -- Two-Part project (adsahay): fetch messages into variables, this is much more efficient than getting them in the query.
2213 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);
2214 l_two_part_tech_msg := pon_printing_pkg.get_messages('PON_TWO_PART_SECTION','PART',pon_auction_pkg.get_technical_meaning);
2215 l_two_part_comm_msg := pon_printing_pkg.get_messages('PON_TWO_PART_SECTION','PART',pon_auction_pkg.get_commercial_meaning);
2216
2217 --Fix for bug 12655380
2218 --Splitting the cursor query and concatenating the xmls from each query to reduce sharable memory usage
2219 OPEN xml_query_cursor FOR
2220 SELECT
2221 pah.auction_header_id,
2222 pah.auction_title,
2223 pah.auction_status,
2224 pah.auction_status_name,
2225 pah.auction_type,
2226 pah.contract_type,
2227 pah.trading_partner_contact_name,
2228 pah.trading_partner_contact_id,
2229 pah.trading_partner_name,
2230 pah.trading_partner_name_upper,
2231 nvl(pah.two_part_flag,'N') two_part_flag,
2232 l_hide_comm_part hide_comm_part,
2233 l_is_super_large_neg is_super_large_neg,
2234 pah.proxy_bidding_enabled_flag,
2235 PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(pah.trading_partner_contact_id) auctioneer_display_name,
2236 pah.bill_to_location_id,
2237 bill_territories_tl.territory_short_name bill_country_name,
2238 loc_bill.location_code bill_address_name,
2239 loc_bill.address_line_1 bill_address1,
2240 loc_bill.address_line_2 bill_address2,
2241 loc_bill.address_line_3 bill_address3,
2242 loc_bill.town_or_city bill_city,
2243 loc_bill.region_2 bill_state,
2244 loc_bill.region_3 bill_province_or_region,
2245 loc_bill.postal_code bill_zip_code,
2246 loc_bill.postal_code bill_postal_code,
2247 loc_bill.country bill_country,
2248 loc_bill.region_1 bill_county,
2249 pah.ship_to_location_id,
2250 ship_territories_tl.territory_short_name ship_country_name,
2251 loc_ship.location_code ship_address_name,
2252 loc_ship.address_line_1 ship_address1,
2253 loc_ship.address_line_2 ship_address2,
2254 loc_ship.address_line_3 ship_address3,
2255 loc_ship.town_or_city ship_city,
2256 loc_ship.region_2 ship_state,
2257 loc_ship.region_3 ship_province_or_region,
2258 loc_ship.postal_code ship_zip_code,
2259 loc_ship.postal_code ship_postal_code,
2260 loc_ship.country ship_country,
2261 loc_ship.region_1 ship_county,
2262 entitytl.name entity,
2263 entity_loc.style entity_address_style,
2264 entity_loc.address_line_1 entity_address_line_1,
2265 entity_loc.address_line_2 entity_address_line_2,
2266 entity_loc.address_line_3 entity_address_line_3,
2267 entity_loc.town_or_city entity_city,
2268 entity_loc.postal_code entity_postal_code,
2269 nvl(entity_terr.territory_short_name, entity_loc.country) entity_country,
2270 entity_loc.country entity_country_code,
2271 entity_loc.region_1 entity_region_1,
2272 entity_loc.region_2 entity_region_2,
2273 entity_loc.region_3 entity_region_3,
2274 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,
2275 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,
2276 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,
2277 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,
2278 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,
2279 pon_oa_util_pkg.display_date_time(pah.publish_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') publish_date,
2280 pon_oa_util_pkg.display_date_time(pah.close_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') close_date,
2281 pon_oa_util_pkg.display_date_time(pah.cancel_date, p_client_time_zone, p_server_time_zone, p_date_format,'N') cancel_date,
2282 pah.time_zone,
2283 pon_auction_pkg.get_timezone_description(p_client_time_zone,l_printing_language) display_time_zone,
2284 pah.open_auction_now_flag,
2285 pah.publish_auction_now_flag,
2286 fl.meaning pon_bid_visibility_display,
2287 pah.bid_visibility_code,
2288 pah.bid_list_type,
2289 pah.bid_frequency_code,
2290 pah.bid_scope_code,
2291 pah.auto_extend_flag,
2292 pah.auto_extend_min_trigger_rank,
2293 pah.auto_extend_number,
2294 pah.auto_extend_enabled_flag,
2295 pah.number_of_extensions,
2296 pah.min_bid_decrement,
2297 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,
2298 pah.price_driven_auction_flag,
2299 pah.payment_terms_id,
2300 ap.name payment_terms,
2301 pah.freight_terms_code,
2302 fl_freight_terms.meaning freight_terms,
2303 pah.fob_code,
2304 fl_fob.meaning fob,
2305 pah.carrier_code,
2306 pah.currency_code,
2307 l_currency_code l_currency_code,
2308 pon_printing_pkg.get_carrier_description(pah.org_id,pah.carrier_code) carrier,
2309 currency_tl.name currency_name,
2310 -- bidpdf: whether this is for a bid pdf
2311 l_is_bidpdf is_bidpdf,
2312 l_price_visibility price_visibility,
2313 pah.rate_type,
2314 pah.rate_date,
2315 pah.rate,
2316 pah.note_to_bidders,
2317 pah.attachment_flag,
2318 pah.language_code,
2319 pah.auto_extend_all_lines_flag,
2320 pah.min_bid_increment,
2321 pah.allow_other_bid_currency_flag,
2322 pah.shipping_terms_code,
2323 pah.shipping_terms,
2324 pah.auto_extend_duration,
2325 pah.proxy_bid_allowed_flag,
2326 pah.publish_rates_to_bidders_flag,
2327 pah.attributes_exist,
2328 pah.order_number,
2329 pah.event_title,
2330 pah.sealed_auction_status,
2331 pah.sealed_actual_unlock_date,
2332 pah.sealed_actual_unseal_date,
2333 pah.mode_of_transport,
2334 pah.mode_of_transport_code,
2335 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,
2336 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,
2337 to_char(pah.po_agreed_amount*l_rate, l_amount_mask) po_agreed_amount,
2338 pah.min_bid_change_type,
2339 pah.full_quantity_bid_code,
2340 pah.number_price_decimals,
2341 pah.auto_extend_type_flag,
2342 pah.auction_origination_code,
2343 pah.multiple_rounds_flag,
2344 pah.allow_withdraw_flag,
2345 pah.allow_staggered_awards,
2346 pah.auction_header_id_orig_round,
2347 pah.auction_header_id_prev_round,
2348 pah.auction_round_number,
2349 pah.manual_close_flag,
2350 pah.manual_extend_flag,
2351 pah.autoextend_changed_flag,
2352 pah.doctype_id,
2353 pah.approval_required_flag,
2354 pah.max_response_iterations,
2355 pah.payment_terms_neg_flag,
2356 pah.mode_of_transport_neg_flag,
2357 pah.contract_id,
2358 pah.contract_version_num,
2359 pah.show_bidder_notes,
2360 pah.derive_type,
2361 pah.bid_ranking,
2362 flbr.meaning bid_ranking_display,
2363 pah.rank_indicator,
2364 pah.show_bidder_scores,
2365 pah.org_id,
2366 pah.buyer_id,
2367 pah.has_pe_for_all_items,
2368 pah.has_price_elements,
2369 to_char(pah.po_min_rel_amount*l_rate, l_amount_mask) po_min_rel_amount,
2370 pah.global_agreement_flag,
2371 pah.document_number,
2372 pah.amendment_number ,
2373 pah.amendment_description ,
2374 pah.auction_header_id_orig_amend ,
2375 pah.auction_header_id_prev_amend ,
2376 pah.document_number ,
2377 pah.hdr_attr_enable_weights ,
2378 pah.hdr_attr_display_score ,
2379 pah.hdr_attr_maximum_score ,
2380 pah.attribute_line_number ,
2381 pah.conterms_exist_flag ,
2382 pah.award_mode ,
2383 pah.has_hdr_attr_flag ,
2384 nvl(pah.has_items_flag,'Y') has_items_flag,
2385 decode(pah.staggered_closing_interval, null, 'N', 'Y') staggered_closing_enabled,
2386 pah.staggered_closing_interval,
2387 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,
2388 doctypes.internal_name,
2389 l_prev_rnd_doctype prev_rnd_internal_name,
2390 po_setup_s1.get_services_enabled_flag() is_services_enabled,
2391 l_contracts_installed as is_contracts_installed,
2392 p_printing_warning_flag print_warning_flag,
2393 l_cont_attach_doc_flag contract_attached_doc,
2394 l_cont_nonmerge_flag contract_non_mergeable,
2395 p_neg_printed_with_contracts neg_printed_with_contracts,
2396 pon_printing_pkg.get_messages('PON_AUCTS_START_CUR_PRICE','CURRENCY_CODE', l_currency_code) start_price_msg,
2397 pon_printing_pkg.get_messages('PON_AUCTS_TARGET_PRICE_CURR','CURRENCY_CODE', l_currency_code) target_price_msg,
2398 pon_printing_pkg.get_messages('PON_AUC_CURRENT_PRICE', 'AUCTION_CURRENCY', l_currency_code) current_price_msg,
2399 pon_printing_pkg.get_messages('PON_AUCTS_MIN_RELEASE_CURR','AUCTION_CURRENCY', l_currency_code) min_release_amt_msg,
2400 pon_printing_pkg.get_messages('PON_AUCTS_AGREEMENT_AMOUNT_CUR','CURRENCY', l_currency_code) agreement_amount_msg,
2401 pon_printing_pkg.get_messages('PON_MAX_RTNGE_AMT_WITH_CURR','AUCTION_CURRENCY', l_currency_code) max_retainage_amt_curr_msg,
2402 pon_printing_pkg.get_messages('PON_ADVANCE_AMT_WITH_CURR','AUCTION_CURRENCY', l_currency_code) advance_amount_curr_msg,
2403 pon_printing_pkg.get_messages('PON_ESTIMATED_TOTAL_AMT_CURR','CURRENCY_CODE', l_currency_code) estimated_amt_msg,
2404 --bug 7592494, call to get_legal_entity_name
2405 pon_printing_pkg.get_messages('PON_AUC_PRN_LEGAL_CONSEQUENCES','LEGAL_ENTITY_NAME',pon_conterms_utl_pvt.GET_LEGAL_ENTITY_NAME(pah.org_id)) legal_consequences_msg,
2406 pon_printing_pkg.get_messages('PON_AUC_INTERVAL_MIN','MINUTES',pah.staggered_closing_interval) stagger_auc_interval_min,
2407 -- two-part project messages
2408 l_two_part_general_msg two_part_general_info_msg,
2409 l_two_part_tech_msg two_part_technical_msg,
2410 l_two_part_comm_msg two_part_commercial_msg,
2411 -- bidpdf: doc title and footer
2412 decode(l_is_bidpdf, 'Y',
2413 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),
2414 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)
2415 ) page_heading_msg,
2416 pbhs.bid_status,
2417 -- bidpdf: document type
2418 doctypes.doctype_group_name,
2419 -- bidpdf: response status
2420 fl_bid.meaning response_status,
2421 -- bidpdf: Response Valid Until
2422 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,
2423 -- bidpdf: supplier address
2424 l_supplier_address_line1 supplier_address_line1,
2425 l_supplier_address_line2 supplier_address_line2,
2426 l_supplier_address_line3 supplier_address_line3,
2427 l_supplier_address_city supplier_address_city,
2428 l_supplier_address_state supplier_address_state,
2429 l_supplier_postal_code supplier_postal_code,
2430 l_supplier_country_code supplier_country_code,
2431 l_supplier_country supplier_address_country,
2432 -- bidpdf: supplier site:
2433 pbhs.vendor_site_code,
2434 -- bidpdf: supplier contact name:
2435 l_contact_details_name contact_details_name,
2436 pbhs.bid_currency_code bid_currency_selected, --Response Currency
2437 pbhs.bidders_bid_number reference_number, --Reference Number
2438 pbhs.note_to_auction_owner note_to_buyer, --Note to Buyer
2439 --bidpdf: Response Received Time value
2440 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,
2441 pbhs.surrog_bid_flag surrog_bid_flag, --Surrogate Bid Flag
2442 pon_printing_pkg.get_user_email(hp1.party_id) email,
2443 pah.abstract_details,
2444 fl_security.meaning security_level,
2445 pah.approval_status,
2446 ps.display_name outcome,
2447 nvl(gdct.description, gdct.user_conversion_type) rate_type_display,
2448 pon_oa_util_pkg.display_date(pah.rate_date,
2449 p_client_time_zone,
2450 p_server_time_zone,
2451 p_date_format, 'N') rate_date_display,
2452 pah.award_approval_flag,
2453 fl_rank_ind.meaning rank_indicator_display,
2454 pah.pf_type_allowed,
2455 fl_pf_type_allowed.meaning pf_type_allowed_display,
2456 pah.supplier_view_type,
2457 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,
2458 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,
2459 fpg.multi_org_flag,
2460 p_user_view_type as user_view_type,
2461 -- for bidpdf, the Company Name comes from pon_bid_headers.trading_partner_name
2462 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,
2463 l_award_approval_enabled as award_approval_enabled,
2464 ns.style_name,
2465 pah.progress_payment_type,
2466 pah.advance_negotiable_flag,
2467 pah.recoupment_negotiable_flag,
2468 pah.progress_pymt_negotiable_flag,
2469 pah.retainage_negotiable_flag,
2470 pah.max_retainage_negotiable_flag,
2471 pah.supplier_enterable_pymt_flag,
2472 pah.project_id sourcing_project_id,
2473 pah.bid_decrement_method,
2474 proj.segment1 sourcing_project_number,
2475 DECODE(pah.contract_type, 'STANDARD', DECODE(progress_payment_type,'NONE','N','Y'),'N') complex_services_enabled,
2476 postyl.advances_flag,
2477 postyl.retainage_flag,
2478 postyl.progress_payment_flag,
2479 postyl.contract_financing_flag,
2480 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,
2481 pon_auction_pkg.GetPAOUInstalled(pah.org_id) projects_installed_flag,
2482 pon_auction_pkg.GetGMSOUInstalled(pah.org_id) grants_installed_flag,
2483 pah.large_neg_enabled_flag,
2484 pah.team_scoring_enabled_flag,
2485 pah.has_scoring_teams_flag,
2486 NVL(pah.enforce_prevrnd_bid_price_flag, 'N') enforce_prevrnd_bid_price_flag,
2487 nvl(pah.DISPLAY_BEST_PRICE_BLIND_FLAG,'N') DISPLAY_BEST_PRICE_BLIND_FLAG,
2488 pah.neg_team_enabled_flag,
2489 pah.price_element_enabled_flag,
2490 buyer_phone.phone_number,
2491 buyer_fax.phone_number fax_number,
2492 -- bidpdf: Proxy response decrement
2493 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,
2494 pon_printing_pkg.get_messages('PON_AUCTS_CUR_PROXY_DEC','CURRENCY_CODE',l_currency_code) supplier_proxy_dec_msg,
2495 -- bidpdf: response total
2496 decode(p_user_view_type, 'BUYER', to_char(pbhs.buyer_bid_total, l_amount_mask),
2497 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)
2498 ) supplier_bid_total,
2499 pon_printing_pkg.get_messages('PON_BID_CUR_TOTAL','CURRENCY_CODE', l_currency_code) supplier_response_total_msg,
2500 pah.price_tiers_indicator,
2501
2502 --------------------Begin: Add by Chaoqun for addiing EMD info into Printable View on 6-NOV-2008-------------------
2503 pah.EMD_ENABLE_FLAG,
2504 pah.CURRENCY_CODE as EMD_CURRENCY_CODE,
2505 pah.EMD_AMOUNT,
2506 To_Char(pah.emd_amount,'FM999G999G999G999G999G999G999G999G999G999D00') emd_amount_formatted,
2507 pah.EMD_DUE_DATE,
2508 pah.EMD_TYPE as EMD_TYPE_CODE,
2509 flv.meaning as EMD_TYPE,
2510 pah.emd_guarantee_expiry_date AS EMD_GUARANTEE_EXPIRY_DATE,
2511 pah.EMD_ADDITIONAL_INFORMATION as EMD_ADDITIONAL_INFO,
2512 --pah.EMD_STATUS,
2513 --------------------End: Add by Chaoqun for adding EMD info into Printable View on 6-NOV-2008----------------------
2514 ------------Added as part of bug 8771921 ---------------
2515 pah.EXT_ATTRIBUTE_CATEGORY,
2516 pah.EXT_ATTRIBUTE1,
2517 pah.EXT_ATTRIBUTE2,
2518 pah.EXT_ATTRIBUTE3,
2519 pah.EXT_ATTRIBUTE4,
2520 pah.EXT_ATTRIBUTE5,
2521 pah.EXT_ATTRIBUTE6,
2522 pah.EXT_ATTRIBUTE7,
2523 pah.EXT_ATTRIBUTE8,
2524 pah.EXT_ATTRIBUTE9,
2525 pah.EXT_ATTRIBUTE10,
2526 pah.EXT_ATTRIBUTE11,
2527 pah.EXT_ATTRIBUTE12,
2528 pah.EXT_ATTRIBUTE13,
2529 pah.EXT_ATTRIBUTE14,
2530 pah.EXT_ATTRIBUTE15,
2531 pah.INT_ATTRIBUTE_CATEGORY,
2532 pah.INT_ATTRIBUTE1,
2533 pah.INT_ATTRIBUTE2,
2534 pah.INT_ATTRIBUTE3,
2535 pah.INT_ATTRIBUTE4,
2536 pah.INT_ATTRIBUTE5,
2537 pah.INT_ATTRIBUTE6,
2538 pah.INT_ATTRIBUTE7,
2539 pah.INT_ATTRIBUTE8,
2540 pah.INT_ATTRIBUTE9,
2541 pah.INT_ATTRIBUTE10,
2542 pah.INT_ATTRIBUTE11,
2543 pah.INT_ATTRIBUTE12,
2544 pah.INT_ATTRIBUTE13,
2545 pah.INT_ATTRIBUTE14,
2546 pah.INT_ATTRIBUTE15,
2547 pah.NEGOTIATION_REQUESTER_ID
2548 ------------End: Added as part of bug 8771921 ---------------
2549 from
2550 pon_auction_headers_all pah ,
2551 fnd_lookups fl,
2552 fnd_lookups fl2,
2553 fnd_lookups flbr ,
2554 fnd_lookups fl_rank_ind,
2555 fnd_lookups fl_pf_type_allowed,
2556 fnd_lookup_values fl_freight_terms ,
2557 fnd_lookup_values flv,
2558 ap_terms ap ,
2559 fnd_lookup_values fl_fob ,
2560 hr_locations_all loc_bill,
2561 fnd_territories_tl bill_territories_tl,
2562 hr_locations_all loc_ship,
2563 fnd_territories_tl ship_territories_tl,
2564 fnd_currencies_tl currency_tl ,
2565 pon_auc_doctypes doctypes,
2566 hz_parties hp1,
2567 hr_operating_units ou,
2568 hr_all_organization_units entity,
2569 hr_all_organization_units_tl entitytl,
2570 hr_locations_all entity_loc,
2571 fnd_territories_tl entity_terr,
2572 fnd_lookups fl_security,
2573 gl_daily_conversion_types gdct,
2574 fnd_product_groups fpg,
2575 pon_negotiation_styles_vl ns,
2576 PO_ALL_DOC_STYLE_LINES ps,
2577 po_doc_style_headers postyl,
2578 pa_projects_all proj,
2579 fnd_user buyer_user,
2580 per_phones buyer_phone,
2581 per_phones buyer_fax,
2582 pon_bid_headers pbhs,
2583 fnd_lookup_values fl_bid
2584 where pah.auction_header_id = p_auction_header_id
2585 and pbhs.auction_header_id (+) = pah.auction_header_id
2586 and pbhs.bid_number (+) = p_bid_number
2587 and fl_bid.lookup_type(+) = 'PON_BID_STATUS'
2588 and fl_bid.lookup_code(+) = pbhs.bid_status
2589 and fl_bid.language(+) = l_printing_language
2590 and currency_tl.currency_code = pah.currency_code
2591 and currency_tl.language = l_printing_language
2592 and fl.lookup_type = 'PON_BID_VISIBILITY_CODE'
2593 and fl.lookup_code = pah.bid_visibility_code
2594 and flbr.lookup_type = 'PON_BID_RANKING_CODE'
2595 and flbr.lookup_code = pah.bid_ranking
2596 and pah.sealed_auction_status = fl2.lookup_code (+)
2597 and fl2.lookup_type(+) = 'PON_SEALED_AUCTION_STATUS'
2598 and fl_freight_terms.lookup_type(+) = 'FREIGHT TERMS'
2599 and fl_freight_terms.lookup_code(+) = pah.freight_terms_code
2600 and fl_security.lookup_type = 'PON_SECURITY_LEVEL_CODE'
2601 and fl_security.lookup_code = pah.security_level_code
2602 and fl_rank_ind.lookup_type = 'PON_RANK_INDICATOR_CODE'
2603 and fl_rank_ind.lookup_code = pah.rank_indicator
2604 and fl_pf_type_allowed.lookup_type = 'PON_PF_TYPE_ALLOWED'
2605 and fl_pf_type_allowed.lookup_code = pah.pf_type_allowed
2606 and fl_freight_terms.language(+) = l_printing_language
2607 and fl_freight_terms.view_application_id(+) = 201
2608 and fl_freight_terms.security_group_id(+) = 0
2609 and ap.term_id(+) = pah.payment_terms_id
2610 and fl_fob.lookup_type(+) = 'FOB'
2611 and fl_fob.lookup_code(+) = pah.fob_code
2612 and fl_fob.language(+) = l_printing_language
2613 and fl_fob.view_application_id(+) = 201
2614 and fl_fob.security_group_id (+) = 0
2615 and loc_bill.location_id(+) = pah.bill_to_location_id
2616 and bill_territories_tl.territory_code(+) = loc_bill.country
2617 and bill_territories_tl.language(+) = l_printing_language
2618 and loc_bill.bill_to_site_flag(+)='Y'
2619 and sysdate < nvl(loc_bill.inactive_date(+), sysdate + 1)
2620 and nvl(loc_bill.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
2621 = nvl(hr_general.get_business_group_id, nvl(loc_bill.business_group_id(+), -99))
2622 and loc_ship.location_id(+) = pah.ship_to_location_id
2623 and ship_territories_tl.territory_code(+) = loc_ship.country
2624 and ship_territories_tl.language(+) = l_printing_language
2625 and loc_ship.ship_to_site_flag(+)='Y'
2626 and sysdate < nvl(loc_ship.inactive_date(+), sysdate + 1)
2627 and nvl(loc_ship.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
2628 = nvl(hr_general.get_business_group_id, nvl(loc_ship.business_group_id(+), -99))
2629 and pah.org_id = ou.organization_id(+)
2630 and nvl(ou.date_from(+),sysdate-1) < sysdate
2631 and nvl(ou.date_to(+),sysdate+1) > sysdate
2632 --bug 7592494
2633 and pah.org_id = entity.organization_id(+)
2634 and entity.organization_id = entitytl.organization_id(+)
2635 and entitytl.language(+) = l_printing_language
2636 and entity.location_id = entity_loc.location_id(+)
2637 and nvl(entity_loc.inactive_date(+), sysdate+1) > sysdate
2638 and entity_terr.territory_code(+) = entity_loc.country
2639 and entity_terr.territory_code(+) NOT IN ('ZR','FX','LX')
2640 and entity_terr.language(+) = l_printing_language
2641 and gdct.conversion_type(+) = pah.rate_type
2642 and hp1.party_id = pah.trading_partner_contact_id
2643 and pah.doctype_id = doctypes.doctype_id
2644 and pah.style_id = ns.style_id
2645 and pah.po_style_id = postyl.style_id(+)
2646 and pah.project_id = proj.project_id(+)
2647 and pah.po_style_id = ps.style_id(+)
2648 and pah.contract_type = ps.document_subtype(+)
2649 and ps.language(+) = l_printing_language
2650 and pah.trading_partner_contact_name = buyer_user.user_name
2651 and buyer_phone.parent_table(+) = 'PER_ALL_PEOPLE_F'
2652 and buyer_phone.parent_id(+) = buyer_user.employee_id
2653 and buyer_phone.phone_type(+) = 'W1'
2654 and nvl(buyer_phone.date_from(+), trunc(sysdate)) <= trunc(sysdate)
2655 and nvl(buyer_phone.date_to(+), trunc(sysdate)) >= trunc(sysdate)
2656 and buyer_fax.parent_table(+) = 'PER_ALL_PEOPLE_F'
2657 and buyer_fax.parent_id(+) = buyer_user.employee_id
2658 and buyer_fax.phone_type(+) = 'WF'
2659 and nvl(buyer_fax.date_from(+), trunc(sysdate)) <= trunc(sysdate)
2660 and nvl(buyer_fax.date_to(+), trunc(sysdate)) >= trunc(sysdate)
2661 --Added by Chaoqun-------------------------------
2662 and flv.lookup_type(+) = 'PON_AUCTION_EMD_TYPE'
2663 and flv.language(+) = USERENV('LANG')
2664 and flv.lookup_code(+) = pah.EMD_TYPE;
2665 -------------------------------------------------
2666
2667 --dbms_lob.createtemporary(result, TRUE);
2668
2669 SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
2670
2671
2672
2673 BEGIN
2674 queryCtx := DBMS_XMLGEN.newContext(xml_query_cursor);
2675 DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
2676 DBMS_XMLGEN.SetRowTag(queryCtx, null);
2677 DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678 xml_clob := xml_res.getCLOBVal();
2679 DBMS_XMLGEN.closeContext (queryCtx);
2680 exception when others then
2681 DBMS_XMLGEN.closeContext (queryCtx);
2682 RAISE;
2683 END;
2684
2685 CLOSE xml_query_cursor;
2686
2687 Dbms_Lob.append(xml_clob, '<LINES>');
2688 OPEN line_num_cur;
2689 LOOP
2690 FETCH line_num_cur INTO line_num;
2691
2692 EXIT WHEN line_num_cur%NOTFOUND;
2693
2694 OPEN lines_cursor FOR
2695 Select paip.item_number ||
2696 nvl2(paip.item_revision, ', ', '') ||
2697 paip.item_revision || jobs.name item,
2698 paip.line_number,
2699 paip.item_description,
2700 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,
2701 paip.category_id,
2702 paip.category_name,
2703 paip.ip_category_id,
2704 icx.category_name ip_category_name,
2705 paip.uom_code,
2706 units.unit_of_measure_tl,
2707 pon_printing_pkg.format_number(paip.quantity) quantity,
2708 -- bidpdf: Note to Buyer
2709 pbip.note_to_auction_owner,
2710 -- bidpdf: add bid price info
2711 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,
2712 pon_printing_pkg.format_number(pbip.quantity) bid_quantity,
2713 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,
2714 --in MAS case, pbip.quantity is null, use paip.quantity instead
2715 to_char(decode(p_user_view_type, 'BUYER',pbip.price, pbip.bid_currency_price)*decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,decode(l_contract_type, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)), l_amount_mask) bid_amount,
2716 --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,
2717 --bidpdf: Bid Minimum Release Amount
2718 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,
2719 --bidpdf: MAS Score
2720 pbip.total_weighted_score,
2721 --bidpdf: Proxy Minimum
2722 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,
2723 paip.ship_to_location_id,
2724
2725 pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(p_auction_header_id,
2726 paip.line_number, paip.target_price*l_rate, paip.quantity, p_trading_partner_id,
2727 p_trading_partner_contact_id, p_vendor_site_id, p_requested_supplier_id),l_price_mask, l_price_precision)
2728 target_price,
2729 -- Start price comes from the earlier bid for a supplier if he had bid
2730 -- on the earlier round for the line and if the control for enforcing
2731 -- previous round start price is set. If he did not bid on the line or
2732 -- if it is buyer or other supplier, then we fall back upon the
2733 -- auction start price
2734 --untransform_one_price
2735
2736 DECODE(l_is_supplier_bidpdf, 'Y',
2737 pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(p_auction_header_id, paip.line_number,
2738 nvl(pbip.bid_start_price, paip.bid_start_price)*l_rate, paip.quantity,
2739 p_trading_partner_id,
2740 p_trading_partner_contact_id,
2741 p_vendor_site_id,
2742 p_requested_supplier_id),l_price_mask, l_price_precision),
2743 DECODE(l_start_price_from_prev_rnd, 'N',
2744 pon_printing_pkg.format_price(
2745 pon_transform_bidding_pkg.calculate_price(p_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),
2746 l_price_mask, l_price_precision),
2747 pon_printing_pkg.format_price(
2748 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),
2749 l_price_mask, l_price_precision)
2750 )
2751 ) bid_start_price,
2752
2753 paip.note_to_bidders,
2754 paip.display_target_price_flag,
2755 paip.type,
2756 to_char(paip.po_min_rel_amount*l_rate, l_amount_mask) po_min_rel_amount,
2757 paip.unit_of_measure,
2758 paip.has_attributes_flag,
2759 paip.org_id,
2760 paip.has_price_elements_flag,
2761 paip.line_type_id,
2762 paip.order_type_lookup_code,
2763 paip.item_revision,
2764 paip.item_id,
2765 paip.item_number,
2766 paip.price_break_type,
2767 paip.price_break_neg_flag,
2768 paip.has_shipments_flag,
2769 paip.price_disabled_flag,
2770 paip.quantity_disabled_flag,
2771 paip.disp_line_number,
2772 paip.is_quantity_scored,
2773 paip.is_need_by_date_scored,
2774 paip.job_id,
2775 paip.additional_job_details,
2776 to_char(paip.po_agreed_amount*l_rate, l_amount_mask) po_agreed_amount,
2777 paip.has_price_differentials_flag,
2778 paip.price_diff_shipment_number,
2779 paip.differential_response_type,
2780 paip.purchase_basis,
2781 pon_auction_pkg.getNeedByDatesToPrint(paip.auction_header_id,paip.line_number,p_date_format) as need_by_dates_to_print,
2782 paip.document_disp_line_number,
2783 paip.group_type,
2784 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,
2785 tl.territory_short_name country_name,
2786 hl.location_code address_name,
2787 hl.address_line_1 address1,
2788 hl.address_line_2 address2,
2789 hl.address_line_3 address3,
2790 hl.town_or_city city,
2791 hl.region_2 state,
2792 hl.region_3 province_or_region,
2793 hl.postal_code zip_code,
2794 hl.postal_code postal_code,
2795 hl.country country,
2796 hl.region_1 county,
2797 paip.requisition_number,
2798 paip.line_origination_code,
2799 nvl2(paip.source_doc_number, paip.source_doc_number || nvl2(paip.source_line_number, ' / ' || paip.source_line_number, null), null) source_doc_line_display,
2800 lt.line_type,
2801 pon_printing_pkg.format_price(paip.current_price, l_price_mask, l_price_precision) current_price,
2802 pon_printing_pkg.format_price(paip.unit_target_price, l_price_mask, l_price_precision) unit_target_price,
2803 paip.unit_display_target_flag
2804 ,paip.has_payments_flag
2805 ,to_char(paip.advance_amount*l_rate, l_amount_mask) advance_amount
2806 ,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
2807 --bidpdf:remove "," after paip.recoupment_rate_percent and paip.progress_pymt_rate_percent
2808 ,paip.recoupment_rate_percent recoupment_rate_percent
2809 ,pbip.recoupment_rate_percent bid_recoupment_rate_percent
2810 ,paip.progress_pymt_rate_percent progress_pymt_rate_percent
2811 ,pbip.progress_pymt_rate_percent bid_progress_pymt_rate_percent
2812 ,paip.retainage_rate_percent retainage_rate_percent
2813 ,pbip.retainage_rate_percent bid_retainage_rate_percent
2814 ,to_char(paip.max_retainage_amount*l_rate, l_amount_mask) max_retainage_amount
2815 ,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
2816 ,paip.project_id project_id
2817 ,proj.segment1 project_number
2818 ,paip.project_task_id project_task_id
2819 ,task.task_number project_task_number
2820 ,paip.project_award_id project_award_id
2821 ,awrd.award_number project_award_number
2822 ,paip.project_expenditure_type project_expenditure_type
2823 ,paip.project_exp_organization_id project_exp_organization_id
2824 ,hrorg.name project_exp_organization_name
2825 ,pon_oa_util_pkg.display_date(paip.project_expenditure_item_date, p_client_time_zone,
2826 p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
2827 ,NVL2(paip.work_approver_user_id, (SELECT per.full_name
2828 FROM per_all_people_f per
2829 WHERE per.person_id = fuser.employee_id
2830 AND per.effective_end_date =
2831 (SELECT MAX(per1.effective_end_date)
2832 FROM per_all_people_f per1
2833 WHERE per.person_id = per1.person_id)
2834 ), NULL) work_approver_name
2835 ,paip.has_quantity_tiers negline_has_quantity_tiers
2836 ,pbip.has_quantity_tiers bidline_has_quantity_tiers
2837 from
2838 pon_auction_item_prices_all paip ,
2839 hr_locations_all hl,
2840 fnd_territories_tl tl,
2841 per_jobs_vl jobs,
2842 icx_cat_categories_v icx,
2843 mtl_units_of_measure_tl units,
2844 po_line_types_tl lt
2845 ,pa_projects_all proj
2846 ,pa_tasks_expend_v task
2847 ,gms_awards_all awrd
2848 ,hr_all_organization_units hrorg
2849 ,fnd_user fuser
2850 ,pon_bid_item_prices pbip
2851 where
2852 paip.auction_header_id = p_auction_header_id
2853 AND paip.line_number = line_num
2854 and pbip.auction_header_id(+) = paip.auction_header_id
2855 and pbip.bid_number(+) = p_bid_number
2856 and pbip.line_number(+)=paip.line_number
2857 and hl.location_id(+) = paip.ship_to_location_id
2858 and tl.territory_code(+) = hl.country
2859 and tl.language(+) = l_printing_language
2860 and hl.ship_to_site_flag(+)='Y'
2861 and sysdate < nvl(hl.inactive_date(+), sysdate + 1)
2862 and paip.uom_code = units.uom_code(+)
2863 and units.language(+) = l_printing_language
2864 and jobs.job_id(+) = paip.job_id
2865 and paip.ip_category_id = icx.rt_category_id(+)
2866 and icx.language(+) = l_printing_language
2867 and lt.line_type_id(+) = paip.line_type_id
2868 and lt.language(+) = l_printing_language
2869 and nvl(hl.business_group_id(+), nvl(hr_general.get_business_group_id, -99))
2870 = nvl(hr_general.get_business_group_id, nvl(hl.business_group_id(+), -99))
2871 and (l_is_buyer_negpdf IN (SELECT 'Y' FROM dual)
2872 or
2873 (not exists (select 'x'
2874 from pon_bidding_parties bp
2875 where bp.auction_header_id = paip.auction_header_id
2876 and ((bp.trading_partner_id = l_trading_partner_id
2877 and bp.vendor_site_id = p_vendor_site_id)
2878 OR bp.requested_supplier_id = p_requested_supplier_id)
2879 and bp.access_type = 'RESTRICTED')
2880 or
2881 nvl(paip.parent_line_number, paip.line_number) not in (
2882 select line_number
2883 from pon_party_line_exclusions pple
2884 where pple.auction_header_id = paip.auction_header_id
2885 and ((pple.trading_partner_id = l_trading_partner_id
2886 and pple.vendor_site_id = p_vendor_site_id)
2887 OR pple.requested_supplier_id = p_requested_supplier_id))))
2888 AND paip.project_id = proj.project_id(+)
2889 AND paip.project_task_id = task.task_id(+)
2890 AND paip.project_award_id = awrd.award_id(+)
2891 AND paip.project_exp_organization_id = hrorg.organization_id(+)
2892 AND paip.work_approver_user_id = fuser.user_id(+)
2893 order by paip.disp_line_number;
2894
2895 linesCtx := DBMS_XMLGEN.newContext(lines_cursor);
2896
2897 Dbms_Lob.append(xml_clob, '<LINES_ROW>');
2898 BEGIN
2899 DBMS_XMLGEN.SetRowSetTag(linesCtx, null);
2900 DBMS_XMLGEN.SetRowTag(linesCtx, null);
2901 lines_res := NULL;
2902 DBMS_XMLGEN.getXMLType(linesCtx, lines_res, DBMS_XMLGEN.NONE);
2903 Dbms_Lob.append(xml_clob, lines_res.getCLOBVal());
2904 exception when others then
2905 DBMS_XMLGEN.closeContext (linesCtx);
2906 RAISE;
2907 END;
2908
2909 OPEN pay_items_cursor FOR SELECT
2910 pay.payment_id
2911 ,pay.payment_display_number payment_display_number
2912 ,pay.ship_to_location_id
2913 ,terr.territory_short_name shipto_country_name
2914 ,hrl.location_code shipto_address_name
2915 ,hrl.address_line_1 shipto_address1
2916 ,hrl.address_line_2 shipto_address2
2917 ,hrl.address_line_3 shipto_address3
2918 ,hrl.town_or_city shipto_city
2919 ,hrl.region_2 shipto_state
2920 ,hrl.region_3 shipto_province_or_region
2921 ,hrl.postal_code shipto_zip_code
2922 ,hrl.postal_code shipto_postal_code
2923 ,hrl.country shipto_country
2924 ,hrl.region_1 shipto_county
2925 ,pay.payment_description
2926 ,pay.payment_type_code
2927 ,lkp1.displayed_field payment_type_disp
2928 ,pay.quantity
2929 ,pay.uom_code
2930 ,uom_tl.unit_of_measure_tl unit_of_measure_tl
2931 ,pon_printing_pkg.format_price(pay.target_price*l_rate, l_price_mask, l_price_precision) target_price
2932 ,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
2933 ,pay.work_approver_user_id
2934 ,NVL2(pay.work_approver_user_id, (SELECT per.full_name
2935 FROM per_all_people_f per
2936 WHERE per.person_id = fuser.employee_id
2937 AND per.effective_end_date =
2938 (SELECT MAX(per1.effective_end_date)
2939 FROM per_all_people_f per1
2940 WHERE per.person_id = per1.person_id)
2941 ), NULL) work_approver_name
2942 ,pay.note_to_bidders
2943 ,pay.project_id project_id
2944 ,proj.segment1 project_number
2945 ,pay.project_task_id project_task_id
2946 ,task.task_number project_task_number
2947 ,pay.project_award_id project_award_id
2948 ,awrd.award_number project_award_number
2949 ,pay.project_expenditure_type project_expenditure_type
2950 ,pay.project_exp_organization_id project_exp_organization_id
2951 ,hrorg.name project_exp_organization_name
2952 ,pon_oa_util_pkg.display_date(pay.project_expenditure_item_date, p_client_time_zone,
2953 p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
2954 ,null pay_item_price
2955 ,null amount_display
2956 ,null bid_promised_date
2957
2958 FROM
2959 pon_auc_payments_shipments pay,
2960 pa_projects_all proj,
2961 pa_tasks_expend_v task,
2962 gms_awards_all awrd,
2963 hr_locations_all hrl,
2964 hr_all_organization_units hrorg,
2965 fnd_user fuser,
2966 po_lookup_codes lkp1,
2967 fnd_territories_tl terr,
2968 mtl_units_of_measure_tl uom_tl
2969 WHERE pay.auction_header_id = p_auction_header_id
2970 AND pay.line_number = line_num
2971 AND pay.project_id = proj.project_id(+)
2972 AND pay.project_task_id = task.task_id(+)
2973 AND pay.project_award_id = awrd.award_id(+)
2974 AND pay.ship_to_location_id = hrl.location_id(+)
2975 AND terr.territory_code(+) = hrl.country
2976 AND terr.language(+) = l_printing_language
2977 AND pay.project_exp_organization_id = hrorg.organization_id(+)
2978 AND pay.payment_type_code = lkp1.lookup_code(+)
2979 AND lkp1.lookup_type(+) = 'PAYMENT TYPE'
2980 AND pay.uom_code = uom_tl.uom_code(+)
2981 AND uom_tl.language(+) = l_printing_language
2982 AND fuser.user_id(+) = pay.work_approver_user_id
2983 AND not exists (select 1 from pon_bid_item_prices where bid_number = p_bid_number and line_number=pay.line_number)
2984 UNION ALL
2985 SELECT
2986 pbp.BID_PAYMENT_ID payment_id
2987 ,pbp.payment_display_number payment_display_number
2988 ,pay.ship_to_location_id
2989 ,terr.territory_short_name shipto_country_name
2990 ,hrl.location_code shipto_address_name
2991 ,hrl.address_line_1 shipto_address1
2992 ,hrl.address_line_2 shipto_address2
2993 ,hrl.address_line_3 shipto_address3
2994 ,hrl.town_or_city shipto_city
2995 ,hrl.region_2 shipto_state
2996 ,hrl.region_3 shipto_province_or_region
2997 ,hrl.postal_code shipto_zip_code
2998 ,hrl.postal_code shipto_postal_code
2999 ,hrl.country shipto_country
3000 ,hrl.region_1 shipto_county
3001 ,pbp.payment_description
3002 ,pbp.payment_type_code
3003 ,lkp1.displayed_field payment_type_disp
3004 ,pay.quantity
3005 ,pay.uom_code
3006 ,uom_tl.unit_of_measure_tl unit_of_measure_tl
3007 ,pon_printing_pkg.format_price(pay.target_price*l_rate, l_price_mask, l_price_precision) target_price
3008 ,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
3009 ,pay.work_approver_user_id
3010 ,NVL2(pay.work_approver_user_id, (SELECT per.full_name
3011 FROM per_all_people_f per
3012 WHERE per.person_id = fuser.employee_id
3013 AND per.effective_end_date =
3014 (SELECT MAX(per1.effective_end_date)
3015 FROM per_all_people_f per1
3016 WHERE per.person_id = per1.person_id)
3017 ), NULL) work_approver_name
3018 ,pay.note_to_bidders
3019 ,pay.project_id project_id
3020 ,proj.segment1 project_number
3021 ,pay.project_task_id project_task_id
3022 ,task.task_number project_task_number
3023 ,pay.project_award_id project_award_id
3024 ,awrd.award_number project_award_number
3025 ,pay.project_expenditure_type project_expenditure_type
3026 ,pay.project_exp_organization_id project_exp_organization_id
3027 ,hrorg.name project_exp_organization_name
3028 ,pon_oa_util_pkg.display_date(pay.project_expenditure_item_date, p_client_time_zone,
3029 p_server_time_zone, p_date_format, 'N') project_expenditure_item_date
3030 ,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
3031 ,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
3032 ,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
3033 FROM
3034 pon_auc_payments_shipments pay,
3035 pa_projects_all proj,
3036 pa_tasks_expend_v task,
3037 gms_awards_all awrd,
3038 hr_locations_all hrl,
3039 hr_all_organization_units hrorg,
3040 fnd_user fuser,
3041 po_lookup_codes lkp1,
3042 fnd_territories_tl terr,
3043 mtl_units_of_measure_tl uom_tl,
3044 pon_bid_payments_shipments pbp,
3045 pon_bid_item_prices pbip
3046 WHERE
3047 pbp.bid_number = p_bid_number
3048 AND pbp.auction_line_number = line_num
3049 AND pbp.auction_header_id = pay.auction_header_id(+)
3050 AND pbip.bid_number = pbp.bid_number
3051 AND pbip.line_number = pbp.bid_line_number
3052 AND pbp.bid_line_number = pay.line_number(+)
3053 AND pbp.auction_payment_id = pay.payment_id(+)
3054 AND pay.project_id = proj.project_id(+)
3055 AND pay.project_task_id = task.task_id(+)
3056 AND pay.project_award_id = awrd.award_id(+)
3057 AND pay.ship_to_location_id = hrl.location_id(+)
3058 AND terr.territory_code(+) = hrl.country
3059 AND terr.language(+) = l_printing_language
3060 AND pay.project_exp_organization_id = hrorg.organization_id(+)
3061 AND pbp.payment_type_code = lkp1.lookup_code(+)
3062 AND lkp1.lookup_type(+) = 'PAYMENT TYPE'
3063 AND pay.uom_code = uom_tl.uom_code(+)
3064 AND uom_tl.language(+) = l_printing_language
3065 AND fuser.user_id(+) = pay.work_approver_user_id
3066 ORDER BY payment_display_number;
3067
3068 BEGIN
3069 payItemsCtx := DBMS_XMLGEN.newContext(pay_items_cursor);
3070 DBMS_XMLGEN.SetRowSetTag(payItemsCtx, 'PAY_ITEMS');
3071 DBMS_XMLGEN.SetRowTag(payItemsCtx, 'PAY_ITEMS_ROW');
3072 pay_items_res := NULL;
3073 DBMS_XMLGEN.getXMLType(payItemsCtx, pay_items_res, DBMS_XMLGEN.NONE);
3074 IF pay_items_res IS NULL THEN
3075 pay_items_res := XMLType('<PAY_ITEMS></PAY_ITEMS>');
3076 END IF;
3077 Dbms_Lob.append(xml_clob, pay_items_res.getCLOBVal());
3078 exception when others then
3079 DBMS_XMLGEN.closeContext (payItemsCtx);
3080 RAISE;
3081 END;
3082
3083 CLOSE pay_items_cursor;
3084
3085 OPEN line_attr_cursor FOR select
3086 attrGrpFlv.meaning,
3087 pal.line_number,
3088 pal.attribute_name,
3089 pal.description,
3090 pal.datatype,
3091 pal.mandatory_flag,
3092 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,
3093 pal.display_prompt,
3094 pal.display_target_flag,
3095 pal.display_only_flag,
3096 pal.sequence_number,
3097 pal.weight,
3098 pal.scoring_type,
3099 NVL(pal.attr_level,'LINE') attr_level,
3100 NVL(pal.attr_group,'GENERAL') attr_group,
3101 pal.attr_max_score,
3102 pal.internal_attr_flag,
3103 NVL(pal.attr_group_seq_number,10) attr_group_seq_number,
3104 pal.attr_disp_seq_number,
3105 -- bidpdf: add attribute response value
3106 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,
3107 CURSOR(select
3108 pas.value,
3109 pas.from_range,
3110 pas.to_range,
3111 pas.score,
3112 pas.sequence_number,
3113 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
3114 FROM
3115 pon_auction_headers_all pah,
3116 pon_attribute_scores pas,
3117 pon_auction_attributes paa
3118 where
3119 pah.auction_header_id = p_auction_header_id
3120 AND pas.auction_header_id = pah.auction_header_id
3121 AND pas.line_number = line_num
3122 AND pas.attribute_sequence_number = pal.sequence_number
3123 and paa.auction_header_id = p_auction_header_id
3124 and paa.line_number = pas.line_number
3125 and paa.sequence_number = pas.attribute_sequence_number
3126 and NVL(paa.attr_level,'LINE')='LINE'
3127 order by pas.line_number,pas.attribute_sequence_number,pas.sequence_number) AS LINE_ATTRIBUTE_SCORES
3128 from
3129 pon_auction_attributes pal,
3130 pon_bid_attribute_values pbav,
3131 fnd_lookups attrGrpFlv
3132 where
3133 pal.auction_header_id = p_auction_header_id
3134 AND pal.line_number = line_num
3135 and pbav.auction_header_id(+) = pal.auction_header_id
3136 and pbav.bid_number(+) = p_bid_number
3137 and pbav.line_number(+) = pal.line_number
3138 and pbav.sequence_number(+) = pal.sequence_number
3139 and NVL(pal.attr_group,'GENERAL') = attrGrpFlv.lookup_code
3140 and NVL(pal.attr_level,'LINE')='LINE'
3141 and NVL(pal.internal_attr_flag,'N') <> 'Y'
3142 and attrGrpFlv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS'
3143 and attrGrpFlv.enabled_flag = 'Y'
3144 and nvl(attrGrpFlv.start_date_active,sysdate) <= sysdate
3145 and nvl(attrGrpFlv.end_date_active,sysdate) > sysdate-1
3146 order by NVL(pal.attr_group_seq_number,10),pal.attr_disp_seq_number;
3147
3148 BEGIN
3149 attrsCtx := DBMS_XMLGEN.newContext(line_attr_cursor);
3150 DBMS_XMLGEN.SetRowSetTag(attrsCtx, 'LINE_ATTRIBUTES');
3151 DBMS_XMLGEN.SetRowTag(attrsCtx, 'LINE_ATTRIBUTES_ROW');
3152 line_attr_res := NULL;
3153 DBMS_XMLGEN.getXMLType(attrsCtx, line_attr_res, DBMS_XMLGEN.NONE);
3154 IF line_attr_res IS NULL THEN
3155 line_attr_res := XMLType('<LINE_ATTRIBUTES></LINE_ATTRIBUTES>');
3156 END IF;
3157 Dbms_Lob.append(xml_clob, line_attr_res.getCLOBVal());
3158 exception when others then
3159 DBMS_XMLGEN.closeContext (attrsCtx);
3160 RAISE;
3161 END;
3162
3163 CLOSE line_attr_cursor;
3164
3165 OPEN pf_cursor FOR SELECT
3166 pet.name,
3167 pe.pricing_basis,
3168 Decode(Nvl(pe.negative_cost_factor_flag,'N'),'Y','Yes','No') negative_cost_factor_flag,
3169 flv.meaning pricing_basis_display,
3170 pe.value value,
3171 --only in supplier bid pdf, the target value is in supplier currency and number format
3172 --in neg pdf and buyer side bid pdf, the target value is in buyer currency
3173 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||')',
3174 'FIXED_AMOUNT', to_char(pe.value*l_rate, l_amount_mask) ||' ('||l_currency_code||')',
3175 pon_printing_pkg.format_number(pe.value)),
3176 null) target_value_display,
3177 -- bidpdf: response value
3178 nvl2(pbpe.bid_currency_value,
3179 decode(pe.pricing_basis,
3180 'PER_UNIT', decode(p_user_view_type,
3181 'BUYER', pon_printing_pkg.format_price(pbpe.auction_currency_value, l_price_mask, l_price_precision)||' ('||l_currency_code||')',
3182 pon_printing_pkg.format_price(pbpe.bid_currency_value, l_price_mask, l_price_precision)||' ('||l_currency_code||')'),
3183 '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||')'),
3184 pon_printing_pkg.format_number(pbpe.bid_currency_value)),
3185 null) bid_value_display,
3186 pe.price_element_type_id,
3187 pe.sequence_number,
3188 pe.display_target_flag,
3189 pet.description,
3190 pe.pf_type,
3191 pe.display_to_suppliers_flag,
3192 flv2.meaning pf_type_display,
3193 --only in supplier bid pdf, the buyer response value is in supplier currency and number format
3194 --in neg pdf and buyer side bid pdf, the buyer response value is in buyer currency
3195 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||')',
3196 'FIXED_AMOUNT', to_char(pf_values.value*l_rate, l_amount_mask)||' ('||l_currency_code||')',
3197 pon_printing_pkg.format_number(pf_values.value)),
3198 null),
3199 null) buyer_pf_value_display,
3200 decode(pah.trading_partner_id,
3201 p_trading_partner_id, 'Y',
3202 decode(pe.pf_type,
3203 'SUPPLIER', 'Y',
3204 decode(pe.display_to_suppliers_flag,
3205 'N', 'N',
3206 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
3207 FROM
3208 pon_auction_headers_all pah,
3209 pon_price_elements pe,
3210 pon_price_element_types_tl pet,
3211 pon_auction_item_prices_all itm,
3212 fnd_lookup_values flv,
3213 fnd_lookup_values flv2,
3214 pon_pf_supplier_values pf_values,
3215 -- bidpdf: add bid value for cost factor
3216 pon_bid_price_elements pbpe
3217 WHERE pah.auction_header_id = p_auction_header_id
3218 AND pe.auction_header_id = pah.auction_header_id
3219 AND pe.line_number = line_num
3220 AND pbpe.auction_header_id(+) = pe.auction_header_id
3221 AND pbpe.bid_number(+) = p_bid_number
3222 AND pbpe.line_number(+) = pe.line_number
3223 AND pbpe.price_element_type_id(+) = pe.price_element_type_id
3224 AND itm.auction_header_id = pe.auction_header_id
3225 AND itm.line_number = pe.line_number
3226 AND pe.price_element_type_id = pet.price_element_type_id
3227 AND pet.language = l_printing_language
3228 AND flv.lookup_type = 'PON_PRICING_BASIS'
3229 AND flv.language = l_printing_language
3230 AND flv.lookup_code = pe.pricing_basis
3231 AND flv.view_application_id = 0
3232 AND flv.security_group_id = 0
3233 AND flv2.lookup_type = 'PON_PRICE_FACTOR_TYPE'
3234 AND flv2.language = l_printing_language
3235 AND flv2.lookup_code = pe.pf_type
3236 AND flv2.view_application_id = 0
3237 AND flv2.security_group_id = 0
3238 AND decode(pe.price_element_type_id, -10, itm.has_price_elements_flag, 'Y') = 'Y'
3239 AND pf_values.auction_header_id(+) = pe.auction_header_id
3240 AND pf_values.line_number(+) = pe.line_number
3241 AND pf_values.pf_seq_number(+) = pe.sequence_number
3242 AND pf_values.supplier_seq_number(+) = l_supplier_sequence_number
3243 order by pe.line_number, pe.sequence_number ASC;
3244
3245 BEGIN
3246 pfCtx := DBMS_XMLGEN.newContext(pf_cursor);
3247 DBMS_XMLGEN.SetRowSetTag(pfCtx, 'PRICE_FACTORS');
3248 DBMS_XMLGEN.SetRowTag(pfCtx, 'PRICE_FACTORS_ROW');
3249 pf_res := NULL;
3250 DBMS_XMLGEN.getXMLType(pfCtx, pf_res, DBMS_XMLGEN.NONE);
3251 IF pf_res IS NULL THEN
3252 pf_res := XMLType('<PRICE_FACTORS></PRICE_FACTORS>');
3253 END IF;
3254 Dbms_Lob.append(xml_clob, pf_res.getCLOBVal());
3255 exception when others then
3256 DBMS_XMLGEN.closeContext (pfCtx);
3257 RAISE;
3258 END;
3259
3260 CLOSE pf_cursor;
3261
3262 OPEN line_price_diff_cursor FOR SELECT
3263 ppd.shipment_number,
3264 ppd.price_differential_number,
3265 ppd.price_type,
3266 pon_printing_pkg.format_number(ppd.multiplier) as target_multiplier,
3267 pon_printing_pkg.format_number(pbpd.multiplier) as multiplier
3268 FROM pon_price_differentials ppd,
3269 -- bidpdf: add response multiplier for price differentials
3270 pon_bid_price_differentials pbpd
3271 WHERE ppd.auction_header_id = p_auction_header_id
3272 AND ppd.line_number = line_num
3273 and pbpd.auction_header_id(+) = ppd.auction_header_id
3274 and pbpd.bid_number (+) = p_bid_number
3275 and pbpd.line_number (+) = ppd.line_number
3276 and pbpd.shipment_number(+) = ppd.shipment_number
3277 and ppd.shipment_number = -1
3278 and pbpd.price_differential_number(+) = ppd.price_differential_number;
3279
3280 BEGIN
3281 linepdiffCtx := DBMS_XMLGEN.newContext(line_price_diff_cursor);
3282 DBMS_XMLGEN.SetRowSetTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS');
3283 DBMS_XMLGEN.SetRowTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS_ROW');
3284 line_price_diff_res := NULL;
3285 DBMS_XMLGEN.getXMLType(linepdiffCtx, line_price_diff_res, DBMS_XMLGEN.NONE);
3286 IF line_price_diff_res IS NULL THEN
3287 line_price_diff_res := XMLType('<LINE_PRICE_DIFFERENTIALS></LINE_PRICE_DIFFERENTIALS>');
3288 END IF;
3289 Dbms_Lob.append(xml_clob, line_price_diff_res.getCLOBVal());
3290 exception when others then
3291 DBMS_XMLGEN.closeContext (linepdiffCtx);
3292 RAISE;
3293 END;
3294
3295 CLOSE line_price_diff_cursor;
3296
3297 OPEN item_pb_cursor FOR SELECT pbsm.auction_shipment_number shipment_number,
3298 pbsm.shipment_number bid_shipment_number,
3299 pbsm.ship_to_organization_id,
3300 mp.organization_code ship_to_organization,
3301 pbsm.ship_to_location_id,
3302 loc.location_code ship_to_location,
3303 pon_printing_pkg.format_number(pbsm.quantity) quantity,
3304 -- in case when supplier add new shipments, there's no target price
3305 decode(pbsm.auction_shipment_number, null, null,
3306 pon_printing_pkg.format_price(
3307 pon_transform_bidding_pkg.calculate_price(p_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),
3308 l_price_mask, l_price_precision)
3309 ) price,
3310
3311 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,
3312 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,
3313 nvl2(pbsm.ship_to_location_id, loc.location_code, mp.organization_code) ship_to,
3314 pbsm.has_price_differentials_flag,
3315 pas.differential_response_type,
3316 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,
3317 pbsm.price_type,
3318 pbsm.price_discount
3319 FROM pon_auction_shipments_all pas,
3320 pon_auction_item_prices_all paip,
3321 hr_locations_all loc,
3322 mtl_parameters mp,
3323 -- bidpdf: add response price for price breaks
3324 pon_bid_shipments pbsm
3325 WHERE pbsm.bid_number = p_bid_number
3326 AND pbsm.line_number = line_num
3327 and pbsm.auction_header_id = pas.auction_header_id(+)
3328 and pbsm.line_number = pas.line_number(+)
3329 and pbsm.auction_shipment_number = pas.shipment_number(+)
3330 AND l_neg_has_price_breaks = 'Y'
3331 AND paip.auction_header_id = pbsm.auction_header_id
3332 AND paip.line_number = pbsm.line_number
3333 AND pbsm.shipment_type = 'PRICE BREAK'
3334 AND mp.organization_id(+) = pbsm.ship_to_organization_id
3335 AND loc.location_id(+) = pbsm.ship_to_location_id
3336 and exists (select 1 from pon_bid_item_prices where bid_number=pbsm.bid_number and line_number=pbsm.line_number)
3337
3338 UNION ALL
3339
3340 SELECT pas.shipment_number,
3341 pas.shipment_number bid_shipment_number,
3342 pas.ship_to_organization_id,
3343 mp.organization_code ship_to_organization,
3344 pas.ship_to_location_id,
3345 loc.location_code ship_to_location,
3346 pon_printing_pkg.format_number(pas.quantity) quantity,
3347 pon_printing_pkg.format_price(
3348 pon_transform_bidding_pkg.calculate_price(p_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),
3349 l_price_mask,
3350 l_price_precision
3351 ) price,
3352 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,
3353 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,
3354 nvl2(pas.ship_to_location_id, loc.location_code, mp.organization_code) ship_to,
3355 pas.has_price_differentials_flag,
3356 pas.differential_response_type,
3357 null bid_currency_price,
3358 null price_type,
3359 null price_discount
3360 FROM pon_auction_shipments_all pas,
3361 pon_auction_item_prices_all paip,
3362 hr_locations_all loc,
3363 mtl_parameters mp
3364 WHERE pas.auction_header_id = p_auction_header_id
3365 AND pas.line_number = line_num
3366 AND l_neg_has_price_breaks = 'Y'
3367 AND paip.auction_header_id = pas.auction_header_id
3368 AND paip.line_number = pas.line_number
3369 AND pas.shipment_type = 'PRICE BREAK'
3370 AND mp.organization_id(+) = pas.ship_to_organization_id
3371 AND loc.location_id(+) = pas.ship_to_location_id
3372 and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number);
3373
3374 BEGIN
3375 itempbreaksCtx := DBMS_XMLGEN.newContext(item_pb_cursor);
3376 DBMS_XMLGEN.SetRowSetTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS');
3377 DBMS_XMLGEN.SetRowTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS_ROW');
3378 item_pb_res := NULL;
3379 DBMS_XMLGEN.getXMLType(itempbreaksCtx, item_pb_res, DBMS_XMLGEN.NONE);
3380 IF item_pb_res IS NULL THEN
3381 item_pb_res := XMLType('<ITEM_PRICE_BREAKS></ITEM_PRICE_BREAKS>');
3382 END IF;
3383 Dbms_Lob.append(xml_clob, item_pb_res.getCLOBVal());
3384 exception when others then
3385 DBMS_XMLGEN.closeContext (itempbreaksCtx);
3386 RAISE;
3387 END;
3388
3389 CLOSE item_pb_cursor;
3390
3391 OPEN item_price_diff_cursor FOR SELECT
3392 ppd.line_number,
3393 ppd.shipment_number,
3394 ppd.price_differential_number,
3395 ppd.price_type,
3396 pon_printing_pkg.format_number(ppd.multiplier) as target_multiplier,
3397 pon_printing_pkg.format_number(pbpd.multiplier) as multiplier
3398 FROM pon_price_differentials ppd,
3399 -- bidpdf: add response multiplier for price differentials
3400 pon_bid_price_differentials pbpd
3401 WHERE ppd.auction_header_id = p_auction_header_id
3402 AND ppd.line_number = line_num
3403 and pbpd.auction_header_id(+) = ppd.auction_header_id
3404 and pbpd.bid_number (+) = p_bid_number
3405 and pbpd.line_number (+) = ppd.line_number
3406 and pbpd.shipment_number(+) = ppd.shipment_number + 1
3407 and ppd.shipment_number <> -1
3408 and pbpd.price_differential_number(+) = ppd.price_differential_number
3409 ORDER BY shipment_number, price_differential_number;
3410
3411 BEGIN
3412 itempdiffsCtx := DBMS_XMLGEN.newContext(item_price_diff_cursor);
3413 DBMS_XMLGEN.SetRowSetTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS');
3414 DBMS_XMLGEN.SetRowTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS_ROW');
3415 item_price_diff_res := NULL;
3416 DBMS_XMLGEN.getXMLType(itempdiffsCtx, item_price_diff_res, DBMS_XMLGEN.NONE);
3417 IF item_price_diff_res IS NULL THEN
3418 item_price_diff_res := XMLType('<ITEM_PRICE_DIFFERENTIALS></ITEM_PRICE_DIFFERENTIALS>');
3419 END IF;
3420 Dbms_Lob.append(xml_clob, item_price_diff_res.getCLOBVal());
3421 exception when others then
3422 DBMS_XMLGEN.closeContext (itempdiffsCtx);
3423 RAISE;
3424 END;
3425
3426 CLOSE item_price_diff_cursor;
3427
3428 OPEN item_quan_cursor FOR SELECT pbsm.auction_shipment_number shipment_number,
3429 pbsm.shipment_number bid_shipment_number,
3430 pon_printing_pkg.format_number(pbsm.quantity) quantity,
3431 pon_printing_pkg.format_number(pbsm.max_quantity) max_quantity,
3432 -- in case when supplier add new shipments, there's no target price
3433 nvl2(pbsm.auction_shipment_number,
3434 pon_printing_pkg.format_price(pon_transform_bidding_pkg.calculate_price(p_auction_header_id,
3435 pas.line_number, pas.price*l_rate, paip.quantity, p_trading_partner_id, p_trading_partner_contact_id, p_vendor_site_id,
3436 p_requested_supplier_id),l_price_mask, l_price_precision)
3437 , null
3438 ) price,
3439 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
3440 FROM pon_auction_shipments_all pas,
3441 pon_auction_item_prices_all paip,
3442 pon_bid_shipments pbsm
3443 WHERE pbsm.bid_number = p_bid_number
3444 AND pbsm.line_number = line_num
3445 and pbsm.auction_header_id = pas.auction_header_id(+)
3446 and pbsm.line_number = pas.line_number(+)
3447 and pbsm.auction_shipment_number = pas.shipment_number(+)
3448 AND paip.auction_header_id = pbsm.auction_header_id
3449 AND paip.line_number = pbsm.line_number
3450 AND pbsm.shipment_type = 'QUANTITY BASED'
3451 and exists (select 1 from pon_bid_item_prices where bid_number=pbsm.bid_number and line_number=pbsm.line_number)
3452
3453 UNION ALL
3454
3455 SELECT pas.shipment_number,
3456 pas.shipment_number bid_shipment_number,
3457 pon_printing_pkg.format_number(pas.quantity) quantity,
3458 pon_printing_pkg.format_number(pas.max_quantity) max_quantity,
3459 pon_printing_pkg.format_price(
3460 pon_transform_bidding_pkg.calculate_price(p_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),
3461 l_price_mask,
3462 l_price_precision
3463 ) price,
3464 null bid_currency_unit_price
3465 FROM pon_auction_shipments_all pas,
3466 pon_auction_item_prices_all paip
3467 WHERE pas.auction_header_id = p_auction_header_id
3468 AND pas.line_number = line_num
3469 AND paip.auction_header_id = pas.auction_header_id
3470 AND paip.line_number = pas.line_number
3471 AND pas.shipment_type = 'QUANTITY BASED'
3472 and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number)
3473 ORDER BY bid_shipment_number ASC;
3474
3475 BEGIN
3476 quanTiersCtx := DBMS_XMLGEN.newContext(item_quan_cursor);
3477 DBMS_XMLGEN.SetRowSetTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS');
3478 DBMS_XMLGEN.SetRowTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS_ROW');
3479 item_quan_res := NULL;
3480 DBMS_XMLGEN.getXMLType(quanTiersCtx, item_quan_res, DBMS_XMLGEN.NONE);
3481 IF item_quan_res IS NULL THEN
3482 item_quan_res := XMLType('<ITEM_QUANTITY_TIERS></ITEM_QUANTITY_TIERS>');
3483 END IF;
3484 Dbms_Lob.append(xml_clob, item_quan_res.getCLOBVal());
3485 exception when others then
3486 DBMS_XMLGEN.closeContext (quanTiersCtx);
3487 RAISE;
3488 END;
3489
3490 CLOSE item_quan_cursor;
3491
3492 Dbms_Lob.append(xml_clob, '</LINES_ROW>');
3493 CLOSE lines_cursor;
3494 END LOOP;
3495 DBMS_XMLGEN.closeContext (linesCtx);
3496 DBMS_XMLGEN.closeContext (payItemsCtx);
3497 DBMS_XMLGEN.closeContext (attrsCtx);
3498 DBMS_XMLGEN.closeContext (pfCtx);
3499 DBMS_XMLGEN.closeContext (linepdiffCtx);
3500 DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501 DBMS_XMLGEN.closeContext (itempdiffsCtx);
3502 DBMS_XMLGEN.closeContext (quanTiersCtx);
3503 Dbms_Lob.append(xml_clob, '</LINES>');
3504
3505 CLOSE line_num_cur;
3506
3507 OPEN collab_team_cursor FOR
3508 SELECT
3509 TM.AUCTION_HEADER_ID,
3510 P.full_name,
3511 S.NAME position_name,
3512 tm.approver_flag,
3513 tm.menu_name,
3514 flkp.meaning member_access_type,
3515 tm.task_name,
3516 pon_oa_util_pkg.display_date(tm.target_date,
3517 p_client_time_zone,
3518 p_server_time_zone,
3519 p_date_format, 'N') target_date
3520 FROM
3521 PON_AUCTION_HEADERS_ALL PAH,
3522 PON_NEG_TEAM_MEMBERS TM,
3523 FND_USER U,
3524 PER_ALL_PEOPLE_F P,
3525 PER_ALL_ASSIGNMENTS_F A,
3526 PER_ALL_POSITIONS S,
3527 FND_LOOKUPS flkp
3528 WHERE PAH.auction_header_id = p_auction_header_id
3529 AND TM.AUCTION_HEADER_ID = pah.auction_header_id
3530 AND l_is_buyer_negpdf = 'Y'
3531 AND pah.neg_team_enabled_flag = 'Y'
3532 AND TM.LAST_AMENDMENT_UPDATE <= pah.amendment_number
3533 AND tm.menu_name = flkp.lookup_code
3534 AND flkp.lookup_type = 'PON_NEG_TEAM_MEMBER_ACCESS'
3535 AND U.USER_ID = TM.USER_ID
3536 AND U.EMPLOYEE_ID = P.PERSON_ID
3537 AND P.EFFECTIVE_END_DATE =
3538 (SELECT MAX(PP.EFFECTIVE_END_DATE)
3539 FROM PER_ALL_PEOPLE_F PP
3540 WHERE PP.PERSON_ID = U.EMPLOYEE_ID)
3541 AND A.PERSON_ID = P.PERSON_ID
3542 AND A.PRIMARY_FLAG = 'Y'
3543 AND ((A.ASSIGNMENT_TYPE = 'E' AND P.CURRENT_EMPLOYEE_FLAG = 'Y')
3544 OR
3545 (A.ASSIGNMENT_TYPE = 'C' AND P.CURRENT_NPW_FLAG = 'Y'))
3546 AND A.EFFECTIVE_END_DATE =
3547 (SELECT MAX(AA.EFFECTIVE_END_DATE)
3548 FROM PER_ALL_ASSIGNMENTS_F AA
3549 WHERE AA.PRIMARY_FLAG = 'Y'
3550 AND AA.ASSIGNMENT_TYPE in ('E', 'C')
3551 AND AA.PERSON_ID = P.PERSON_ID)
3552 AND A.POSITION_ID = S.POSITION_ID(+)
3553 AND TM.AUCTION_HEADER_ID = pah.auction_header_id
3554 AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
3555 ORDER BY P.FULL_NAME, U.USER_NAME;
3556
3557 BEGIN
3558 queryCtx := DBMS_XMLGEN.newContext(collab_team_cursor);
3559 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'COLLABORATION_TEAM');
3560 DBMS_XMLGEN.SetRowTag(queryCtx, 'COLLABORATION_TEAM_ROW');
3561 DBMS_XMLGEN.getXMLType(queryCtx, collab_team_res, DBMS_XMLGEN.NONE);
3562 IF collab_team_res IS NULL THEN
3563 collab_team_res := XMLType('<COLLABORATION_TEAM></COLLABORATION_TEAM>');
3564 END IF;
3565 Dbms_Lob.append(xml_clob, collab_team_res.getCLOBVal());
3566 DBMS_XMLGEN.closeContext (queryCtx);
3567 exception when others then
3568 DBMS_XMLGEN.closeContext (queryCtx);
3569 RAISE;
3570 END;
3571
3572 CLOSE collab_team_cursor;
3573
3574 OPEN scoring_team_cursor FOR
3575 SELECT sctm.team_id
3576 ,sctm.team_name
3577 ,sctm.price_visible_flag
3578 ,sctm.instruction_text
3579 FROM pon_auction_headers_all pah,
3580 pon_scoring_teams sctm
3581 WHERE pah.auction_header_id = p_auction_header_id
3582 AND sctm.auction_header_id = pah.auction_header_id
3583 AND pah.has_scoring_teams_flag = 'Y' -- teams present only if flag present
3584 ORDER BY sctm.team_name;
3585
3586 BEGIN
3587 queryCtx := DBMS_XMLGEN.newContext(scoring_team_cursor);
3588 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAMS');
3589 DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAMS_ROW');
3590 DBMS_XMLGEN.getXMLType(queryCtx, scoring_team_res, DBMS_XMLGEN.NONE);
3591 IF scoring_team_res IS NULL THEN
3592 scoring_team_res := XMLType('<SCORING_TEAMS></SCORING_TEAMS>');
3593 END IF;
3594 Dbms_Lob.append(xml_clob, scoring_team_res.getCLOBVal());
3595 DBMS_XMLGEN.closeContext (queryCtx);
3596 exception when others then
3597 DBMS_XMLGEN.closeContext (queryCtx);
3598 RAISE;
3599 END;
3600
3601 CLOSE scoring_team_cursor;
3602
3603 OPEN scoring_mems_cursor FOR
3604 SELECT DISTINCT -- Distinct added because sometimes an employee may have
3605 -- multipler user ids resulting in more rows being returned
3606 stmem.team_id
3607 ,stmem.user_id
3608 ,per.full_name member_name
3609 FROM pon_auction_headers_all pah
3610 ,pon_scoring_team_members stmem
3611 ,fnd_user fuser
3612 ,per_all_people_f per
3613 WHERE pah.auction_header_id = p_auction_header_id
3614 AND stmem.auction_header_id = pah.auction_header_id
3615 AND stmem.user_id = fuser.user_id
3616 AND fuser.employee_id = per.person_id
3617 AND pah.has_scoring_teams_flag = 'Y' -- members present only if teams present
3618 AND per.effective_end_date = (select max(pp.effective_end_date) from per_all_people_f pp where pp.person_id = per.person_id);
3619
3620 BEGIN
3621 queryCtx := DBMS_XMLGEN.newContext(scoring_mems_cursor);
3622 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_MEMBERS');
3623 DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_MEMBERS_ROW');
3624 DBMS_XMLGEN.getXMLType(queryCtx, scoring_mems_res, DBMS_XMLGEN.NONE);
3625 IF scoring_mems_res IS NULL THEN
3626 scoring_mems_res := XMLType('<SCORING_TEAM_MEMBERS></SCORING_TEAM_MEMBERS>');
3627 END IF;
3628 Dbms_Lob.append(xml_clob, scoring_mems_res.getCLOBVal());
3629 DBMS_XMLGEN.closeContext (queryCtx);
3630 exception when others then
3631 DBMS_XMLGEN.closeContext (queryCtx);
3632 RAISE;
3633 END;
3634
3635 CLOSE scoring_mems_cursor;
3636
3637
3638 OPEN scoring_secs_cursor for
3639 SELECT team_sections.section_id
3640 ,sections.section_name
3641 ,team_sections.auction_header_id
3642 ,team_sections.team_id
3643 FROM pon_auction_headers_all pah,
3644 pon_scoring_team_sections team_sections
3645 ,pon_auction_sections sections
3646 WHERE pah.auction_header_id = p_auction_header_id
3647 AND team_sections.auction_header_id = pah.auction_header_id
3648 AND sections.section_id = team_sections.section_id
3649 AND sections.auction_header_id = team_sections.auction_header_id
3650 AND pah.has_scoring_teams_flag = 'Y'; -- sections present only if teams present
3651
3652
3653 BEGIN
3654 queryCtx := DBMS_XMLGEN.newContext(scoring_secs_cursor);
3655 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_SECTIONS');
3656 DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_SECTIONS_ROW');
3657 DBMS_XMLGEN.getXMLType(queryCtx, scoring_secs_res, DBMS_XMLGEN.NONE);
3658 IF scoring_secs_res IS NULL THEN
3659 scoring_secs_res := XMLType('<SCORING_TEAM_SECTIONS></SCORING_TEAM_SECTIONS>');
3660 END IF;
3661 Dbms_Lob.append(xml_clob, scoring_secs_res.getCLOBVal());
3662 DBMS_XMLGEN.closeContext (queryCtx);
3663 exception when others then
3664 DBMS_XMLGEN.closeContext (queryCtx);
3665 RAISE;
3666 END;
3667
3668 CLOSE scoring_secs_cursor;
3669
3670
3671 OPEN abstracts_cursor FOR
3672 SELECT
3673 forms_tl.form_name,
3674 forms.form_version,
3675 forms.form_id,
3676 forms.form_code
3677 FROM
3678 pon_forms_instances form_instances,
3679 pon_forms_sections forms,
3680 pon_forms_sections_tl forms_tl
3681 WHERE
3682 form_instances.entity_code = 'PON_AUCTION_HEADERS_ALL'
3683 AND form_instances.entity_pk1 = TO_CHAR(p_auction_header_id)
3684 AND l_is_buyer_negpdf = 'Y'
3685 AND forms_tl.language = l_printing_language
3686 AND form_instances.form_id = forms.form_id
3687 AND forms.form_id = forms_tl.form_id
3688 ORDER BY form_name;
3689
3690 BEGIN
3691 queryCtx := DBMS_XMLGEN.newContext(abstracts_cursor);
3692 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ABSTRACT_AND_FORMS');
3693 DBMS_XMLGEN.SetRowTag(queryCtx, 'ABSTRACT_AND_FORMS_ROW');
3694 DBMS_XMLGEN.getXMLType(queryCtx, abstracts_res, DBMS_XMLGEN.NONE);
3695 IF abstracts_res IS NULL THEN
3696 abstracts_res := XMLType('<ABSTRACT_AND_FORMS></ABSTRACT_AND_FORMS>');
3697 END IF;
3698 Dbms_Lob.append(xml_clob, abstracts_res.getCLOBVal());
3699 DBMS_XMLGEN.closeContext (queryCtx);
3700 exception when others then
3701 DBMS_XMLGEN.closeContext (queryCtx);
3702 RAISE;
3703 END;
3704
3705 CLOSE abstracts_cursor;
3706
3707
3708 OPEN currency_cursor FOR
3709 select
3710 pacr.bid_currency_code,
3711 ftl.name bid_currency_name,
3712 pacr.number_price_decimals,
3713 -- bug 8667493 following column added to display EMD amount, if enabled, in different currencies
3714 to_char((Nvl(pah.emd_amount,0) * pacr.rate),'FM999G999G999G999G999G999G999G999G999G999D00') emd_resp_curr_amount,
3715 pon_printing_pkg.get_display_rate(pacr.rate_dsp,pah.rate_type,pah.rate_date,pah.currency_code,bid_currency_code) display_rate
3716 FROM
3717 pon_auction_headers_all pah,
3718 pon_auction_currency_rates pacr ,
3719 fnd_currencies_tl ftl
3720 where
3721 pah.auction_header_id = p_auction_header_id
3722 AND pacr.auction_header_id = pah.auction_header_id
3723 and ftl.currency_code = pacr.bid_currency_code
3724 and ftl.language = l_printing_language;
3725
3726 BEGIN
3727 queryCtx := DBMS_XMLGEN.newContext(currency_cursor);
3728 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'CURRENCY');
3729 DBMS_XMLGEN.SetRowTag(queryCtx, 'CURRENCY_ROW');
3730 DBMS_XMLGEN.getXMLType(queryCtx, currency_res, DBMS_XMLGEN.NONE);
3731 IF currency_res IS NULL THEN
3732 currency_res := XMLType('<CURRENCY></CURRENCY>');
3733 END IF;
3734 Dbms_Lob.append(xml_clob, currency_res.getCLOBVal());
3735 DBMS_XMLGEN.closeContext (queryCtx);
3736 exception when others then
3737 DBMS_XMLGEN.closeContext (queryCtx);
3738 RAISE;
3739 END;
3740
3741 CLOSE currency_cursor;
3742
3743
3744 OPEN invited_supp_cur_cursor FOR
3745 SELECT
3746 pbp.bid_currency_code,
3747 ftl.name bid_currency_name,
3748 pbp.number_price_decimals,
3749 -- bug 8667493 following column added to display EMD amount, if enabled, in different currencies
3750 to_char((Nvl(pah.emd_amount,0) * pbp.rate),'FM999G999G999G999G999G999G999G999G999G999D00') emd_resp_curr_amount,
3751 nvl2(pbp.rate_dsp, pon_printing_pkg.format_number(pbp.rate_dsp), null) as display_rate
3752 FROM
3753 pon_auction_headers_all pah,
3754 pon_bidding_parties pbp,
3755 fnd_currencies_tl ftl
3756 WHERE pah.auction_header_id = p_auction_header_id
3757 AND pbp.auction_header_id = pah.auction_header_id
3758 AND (l_is_buyer_negpdf = 'N')
3759 AND ftl.currency_code = pbp.bid_currency_code
3760 AND ftl.language = l_printing_language
3761 AND ((pbp.trading_partner_id = l_trading_partner_id
3762 AND pbp.vendor_site_id = p_vendor_site_id)
3763 OR pbp.requested_supplier_id = p_requested_supplier_id)
3764 ORDER BY sequence ASC;
3765 --) AS INVITED_SUPPLIER_CURRENCY,
3766
3767 BEGIN
3768 queryCtx := DBMS_XMLGEN.newContext(invited_supp_cur_cursor);
3769 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY');
3770 DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY_ROW');
3771 DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_cur_res, DBMS_XMLGEN.NONE);
3772 IF invited_supp_cur_res IS NULL THEN
3773 invited_supp_cur_res := XMLType('<INVITED_SUPPLIER_CURRENCY></INVITED_SUPPLIER_CURRENCY>');
3774 END IF;
3775 Dbms_Lob.append(xml_clob, invited_supp_cur_res.getCLOBVal());
3776 DBMS_XMLGEN.closeContext (queryCtx);
3777 exception when others then
3778 DBMS_XMLGEN.closeContext (queryCtx);
3779 RAISE;
3780 END;
3781
3782 CLOSE invited_supp_cur_cursor;
3783
3784
3785
3786 OPEN header_attr_cursor FOR
3787 select sum(nvl(paa.weight,0)) weight,
3788 sum(nvl(paa.attr_max_score,0)) score,
3789 pass.section_name,
3790 nvl(pass.two_part_section_type,'') two_part_section_type,
3791 cursor (select
3792 pa.auction_header_id,
3793 pa.line_number,
3794 pa.attribute_name as header_attribute_name,
3795 pa.description,
3796 pa.datatype,
3797 pa.mandatory_flag,
3798 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,
3799 pa.display_prompt,
3800 pa.display_target_flag,
3801 pa.display_only_flag,
3802 pa.sequence_number,
3803 nvl(pa.weight,0) weight,
3804 pa.scoring_type,
3805 pa.attr_level,
3806 pa.attr_group,
3807 pa.attr_max_score,
3808 pa.internal_attr_flag,
3809 pa.attr_group_seq_number,
3810 pa.attr_disp_seq_number,
3811 pa.knockout_score,
3812 pa.scoring_method,
3813 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,
3814 pbav.score attribute_bid_score,
3815 cursor( select
3816 pas.auction_header_id,
3817 pas.line_number,
3818 pas.attribute_sequence_number,
3819 pas.value,
3820 pas.from_range,
3821 pas.to_range,
3822 pas.score,
3823 pas.sequence_number,
3824 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
3825 from
3826 pon_auction_headers_all pah,
3827 pon_attribute_scores pas
3828 where
3829 pah.auction_header_id = p_auction_header_id
3830 AND pas.auction_header_id = pa.auction_header_id
3831 and pas.line_number = -1
3832 and pas.attribute_sequence_number = pa.sequence_number
3833 order by pas.attribute_sequence_number,pas.sequence_number
3834 ) as HEADER_ATTRIBUTE_SCORES
3835 from
3836 pon_auction_attributes pa, pon_bid_attribute_values pbav
3837 where
3838 pa.auction_header_id = paa.auction_header_id
3839 --bidpdf: add bid values for attributes from table pon_bid_attribute_values
3840 -- The table has index on bid_number, line_number, sequence_number
3841 and pbav.auction_header_id(+) = pa.auction_header_id
3842 and pbav.bid_number(+) = p_bid_number
3843 and pbav.line_number(+) = pa.line_number
3844 and pbav.sequence_number(+) = pa.sequence_number
3845 and pa.section_name = paa.section_name
3846 and pa.line_number = -1
3847 and pa.attr_level='HEADER'
3848 and (l_is_buyer_negpdf = 'Y' or pa.internal_attr_flag <> 'Y')
3849 order by pa.attr_disp_seq_number) as HEADER_ATTRIBUTES_DETAILS
3850 from
3851 pon_auction_attributes paa,pon_auction_sections pass
3852 where
3853 pass.auction_header_id = p_auction_header_id
3854 and pass.auction_header_id = paa.auction_header_id(+)
3855 and pass.section_name = paa.section_name(+)
3856 and paa.attr_level(+)='HEADER'
3857 and paa.line_number(+) = -1
3858 and (l_is_buyer_negpdf = 'Y' or paa.internal_attr_flag <> 'Y')
3859 and (l_is_section_restricted = 'N'
3860 or l_is_section_restricted = 'Y'
3861 and paa.attr_group_seq_number in (
3862 select pas.attr_group_seq_number
3863 from pon_scoring_team_members pstm, pon_scoring_team_sections psts, pon_auction_sections pas
3864 where pstm.auction_header_id = p_auction_header_id
3865 and pstm.user_id = p_user_id
3866 and psts.auction_header_id = pstm.auction_header_id
3867 and psts.team_id = pstm.team_id
3868 and psts.section_id = pas.section_id
3869 and psts.auction_header_id = pas.auction_header_id
3870 )
3871 )
3872 and ((l_hide_comm_part = 'Y' and pass.two_part_section_type = 'TECHNICAL') or l_hide_comm_part <> 'Y')
3873 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)
3874 order by pass.attr_group_seq_number;
3875
3876 BEGIN
3877 queryCtx := DBMS_XMLGEN.newContext(header_attr_cursor);
3878 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES');
3879 DBMS_XMLGEN.SetRowTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES_ROW');
3880 DBMS_XMLGEN.getXMLType(queryCtx, header_attr_res, DBMS_XMLGEN.NONE);
3881 IF header_attr_res IS NULL THEN
3882 header_attr_res := XMLType('<GROUP_HEADER_ATTRIBUTES></GROUP_HEADER_ATTRIBUTES>');
3883 END IF;
3884 Dbms_Lob.append(xml_clob, header_attr_res.getCLOBVal());
3885 DBMS_XMLGEN.closeContext (queryCtx);
3886 exception when others then
3887 DBMS_XMLGEN.closeContext (queryCtx);
3888 RAISE;
3889 END;
3890
3891 CLOSE header_attr_cursor;
3892
3893
3894 OPEN invited_supp_cursor FOR
3895 SELECT
3896 decode(pbp.trading_partner_id, null, pbp.requested_supplier_name, pbp.trading_partner_name) trading_partner_name,
3897 pbp.vendor_site_code,
3898 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,
3899 pbp.additional_contact_email,
3900 pbp.bid_currency_code,
3901 pbp.rate_dsp,
3902 nvl2(pbp.rate_dsp, pon_printing_pkg.format_number(pbp.rate_dsp), null) as rate_dsp_display,
3903 pbp.number_price_decimals,
3904 pbp.access_type,
3905 pbp.auction_header_id,
3906 pbp.trading_partner_id,
3907 pbp.trading_partner_contact_id,
3908 pbp.sequence
3909 FROM pon_bidding_parties pbp
3910 WHERE
3911 pbp.auction_header_id = p_auction_header_id
3912 AND l_is_buyer_negpdf = 'Y'
3913 ORDER BY sequence ASC;
3914
3915 BEGIN
3916 queryCtx := DBMS_XMLGEN.newContext(invited_supp_cursor);
3917 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIERS');
3918 DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIERS_ROW');
3919 DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_res, DBMS_XMLGEN.NONE);
3920 IF invited_supp_res IS NULL THEN
3921 invited_supp_res := XMLType('<INVITED_SUPPLIERS></INVITED_SUPPLIERS>');
3922 END IF;
3923 Dbms_Lob.append(xml_clob, invited_supp_res.getCLOBVal());
3924 DBMS_XMLGEN.closeContext (queryCtx);
3925 exception when others then
3926 DBMS_XMLGEN.closeContext (queryCtx);
3927 RAISE;
3928 END;
3929
3930 CLOSE invited_supp_cursor;
3931
3932
3933 OPEN line_pf_cursor FOR
3934 SELECT
3935 pet.name,
3936 flv.meaning pricing_basis_display,
3937 pet.description,
3938 flv2.meaning pf_type_display
3939 FROM
3940 pon_price_element_types_tl pet,
3941 fnd_lookup_values flv,
3942 fnd_lookup_values flv2
3943 WHERE
3944 pet.language = l_printing_language
3945 AND pet.price_element_type_id = -10
3946 AND flv.lookup_type = 'PON_PRICING_BASIS'
3947 AND flv.language = l_printing_language
3948 AND flv.lookup_code = 'PER_UNIT'
3949 AND flv.view_application_id = 0
3950 AND flv.security_group_id = 0
3951 AND flv2.lookup_type = 'PON_PRICE_FACTOR_TYPE'
3952 AND flv2.language = l_printing_language
3953 AND flv2.lookup_code = 'SUPPLIER'
3954 AND flv2.view_application_id = 0
3955 AND flv2.security_group_id = 0;
3956
3957 BEGIN
3958 queryCtx := DBMS_XMLGEN.newContext(line_pf_cursor);
3959 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LINE_PRICE_PF_DETAILS');
3960 DBMS_XMLGEN.SetRowTag(queryCtx, 'LINE_PRICE_PF_DETAILS_ROW');
3961 DBMS_XMLGEN.getXMLType(queryCtx, line_pf_res, DBMS_XMLGEN.NONE);
3962 IF line_pf_res IS NULL THEN
3963 line_pf_res := XMLType('<LINE_PRICE_PF_DETAILS></LINE_PRICE_PF_DETAILS>');
3964 END IF;
3965 Dbms_Lob.append(xml_clob, line_pf_res.getCLOBVal());
3966 DBMS_XMLGEN.closeContext (queryCtx);
3967 exception when others then
3968 DBMS_XMLGEN.closeContext (queryCtx);
3969 RAISE;
3970 END;
3971
3972 CLOSE line_pf_cursor;
3973
3974
3975 OPEN buyer_pf_cursor FOR
3976 SELECT
3977 pf_values.auction_header_id,
3978 pf_values.line_number,
3979 pf_values.pf_seq_number,
3980 pf_values.supplier_seq_number,
3981 pf_values.value,
3982 pfs.price_element_type_id,
3983 pfs.pricing_basis,
3984 Decode(Nvl(pfs.negative_cost_factor_flag,'N'),'Y','Yes','No') negative_cost_factor_flag
3985 FROM
3986 pon_auction_headers_all pah,
3987 pon_price_elements pfs,
3988 pon_pf_supplier_values pf_values
3989 WHERE pah.auction_header_id = p_auction_header_id
3990 and pf_values.auction_header_id = pah.auction_header_id
3991 AND pah.large_neg_enabled_flag = 'N'
3992 AND l_is_buyer_negpdf = 'Y'
3993 AND pf_values.auction_header_id = pfs.auction_header_id
3994 AND pf_values.line_number = pfs.line_number
3995 AND pf_values.pf_seq_number = pfs.sequence_number
3996 ORDER BY pf_values.supplier_seq_number, pf_values.line_number, pf_values.pf_seq_number;
3997
3998 BEGIN
3999 queryCtx := DBMS_XMLGEN.newContext(buyer_pf_cursor);
4000 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'BUYER_PF_VALUES');
4001 DBMS_XMLGEN.SetRowTag(queryCtx, 'BUYER_PF_VALUES_ROW');
4002 DBMS_XMLGEN.getXMLType(queryCtx, buyer_pf_res, DBMS_XMLGEN.NONE);
4003 IF buyer_pf_res IS NULL THEN
4004 buyer_pf_res := XMLType('<BUYER_PF_VALUES></BUYER_PF_VALUES>');
4005 END IF;
4006 Dbms_Lob.append(xml_clob, buyer_pf_res.getCLOBVal());
4007 DBMS_XMLGEN.closeContext (queryCtx);
4008 exception when others then
4009 DBMS_XMLGEN.closeContext (queryCtx);
4010 RAISE;
4011 END;
4012
4013 CLOSE buyer_pf_cursor;
4014
4015
4016 OPEN dist_buyer_pf_cursor FOR
4017 SELECT DISTINCT
4018 ppe.price_element_type_id,
4019 ppe.pricing_basis,
4020 Decode(Nvl(ppe.negative_cost_factor_flag,'N'),'Y','Yes','No') negative_cost_factor_flag,
4021 ppett.name,
4022 fl.meaning as pricing_basis_meaning
4023 FROM
4024 pon_auction_headers_all pah,
4025 pon_price_elements ppe,
4026 pon_price_element_types_tl ppett,
4027 fnd_lookups fl
4028 WHERE pah.auction_header_id = p_auction_header_id
4029 and ppe.auction_header_id = pah.auction_header_id
4030 AND pah.large_neg_enabled_flag = 'N'
4031 AND l_is_buyer_negpdf = 'Y'
4032 AND ppe.pf_type = 'BUYER'
4033 AND ppe.price_element_type_id = ppett.price_element_type_id
4034 AND ppett.language = l_printing_language
4035 AND ppe.pricing_basis = fl.lookup_code
4036 AND fl.lookup_type = 'PON_PRICING_BASIS'
4037 ORDER BY name, pricing_basis_meaning;
4038
4039 BEGIN
4040 queryCtx := DBMS_XMLGEN.newContext(dist_buyer_pf_cursor);
4041 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DISTINCT_BUYER_PFS');
4042 DBMS_XMLGEN.SetRowTag(queryCtx, 'DISTINCT_BUYER_PFS_ROW');
4043 DBMS_XMLGEN.getXMLType(queryCtx, dist_buyer_pf_res, DBMS_XMLGEN.NONE);
4044 IF dist_buyer_pf_res IS NULL THEN
4045 dist_buyer_pf_res := XMLType('<DISTINCT_BUYER_PFS></DISTINCT_BUYER_PFS>');
4046 END IF;
4047 Dbms_Lob.append(xml_clob, dist_buyer_pf_res.getCLOBVal());
4048 DBMS_XMLGEN.closeContext (queryCtx);
4049 exception when others then
4050 DBMS_XMLGEN.closeContext (queryCtx);
4051 RAISE;
4052 END;
4053
4054 CLOSE dist_buyer_pf_cursor;
4055
4056
4057 OPEN large_neg_bur_pf_cursor FOR
4058 select
4059 pon_large_neg_pf_values.supplier_seq_number,
4060 priceelementtypesvl.name||'('||lookuptable.meaning||')' pf_name,
4061 pon_large_neg_pf_values.value
4062 from
4063 pon_auction_headers_all pah,
4064 pon_large_neg_pf_values pon_large_neg_pf_values,
4065 pon_price_element_types_vl priceelementtypesvl,
4066 fnd_lookups lookuptable
4067 WHERE pah.auction_header_id = p_auction_header_id
4068 AND pon_large_neg_pf_values.auction_header_id = pah.auction_header_id
4069 and pah.large_neg_enabled_flag = 'Y'
4070 AND l_is_buyer_negpdf = 'Y'
4071 AND priceelementtypesvl.price_element_type_id = pon_large_neg_pf_values.price_element_type_id
4072 AND lookuptable.lookup_code = pon_large_neg_pf_values.pricing_basis
4073 AND lookuptable.lookup_type = 'PON_PRICING_BASIS'
4074 AND pon_large_neg_pf_values.value is not null
4075 order by pon_large_neg_pf_values.supplier_seq_number,pf_name;
4076
4077 BEGIN
4078 queryCtx := DBMS_XMLGEN.newContext(large_neg_bur_pf_cursor);
4079 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES');
4080 DBMS_XMLGEN.SetRowTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES_ROW');
4081 DBMS_XMLGEN.getXMLType(queryCtx, large_neg_bur_pf_res, DBMS_XMLGEN.NONE);
4082 IF large_neg_bur_pf_res IS NULL THEN
4083 large_neg_bur_pf_res := XMLType('<LARGE_NEG_BUYER_PF_VALUES></LARGE_NEG_BUYER_PF_VALUES>');
4084 END IF;
4085 Dbms_Lob.append(xml_clob, large_neg_bur_pf_res.getCLOBVal());
4086 DBMS_XMLGEN.closeContext (queryCtx);
4087 exception when others then
4088 DBMS_XMLGEN.closeContext (queryCtx);
4089 RAISE;
4090 END;
4091
4092 CLOSE large_neg_bur_pf_cursor;
4093
4094
4095 OPEN pb_loc_cursor FOR
4096 select
4097 distinct loc.location_id id,
4098 loc.location_code name,
4099 ship_territories_tl.territory_short_name country_name,
4100 loc.location_code address_name,
4101 loc.address_line_1 address1,
4102 loc.address_line_2 address2,
4103 loc.address_line_3 address3,
4104 loc.town_or_city city,
4105 loc.region_2 state,
4106 loc.region_3 province_or_region,
4107 loc.postal_code zip_code,
4108 loc.postal_code postal_code,
4109 loc.country country,
4110 loc.region_1 county
4111 from
4112 hr_locations_all loc,
4113 pon_auction_shipments_all pas,
4114 fnd_territories_tl ship_territories_tl
4115 WHERE
4116 pas.auction_header_id = p_auction_header_id
4117 and l_is_buyer_negpdf = 'N'
4118 and l_neg_has_price_breaks = 'Y'
4119 and pas.shipment_type = 'PRICE BREAK'
4120 and loc.ship_to_site_flag='Y'
4121 and sysdate < nvl(loc.inactive_date, sysdate + 1)
4122 and loc.location_id = pas.ship_to_location_id
4123 and ship_territories_tl.territory_code(+) = loc.country
4124 and ship_territories_tl.language(+) = l_printing_language
4125 and nvl(loc.business_group_id, nvl(hr_general.get_business_group_id, -99))
4126 = nvl(hr_general.get_business_group_id, nvl(loc.business_group_id, -99))
4127 union
4128 (select
4129 mp.organization_id id,
4130 mp.organization_code name,
4131 ship_territories_tl.territory_short_name country_name,
4132 loc.location_code address_name,
4133 loc.address_line_1 address1,
4134 loc.address_line_2 address2,
4135 loc.address_line_3 address3,
4136 loc.town_or_city city,
4137 loc.region_2 state,
4138 loc.region_3 province_or_region,
4139 loc.postal_code zip_code,
4140 loc.postal_code postal_code,
4141 loc.country country,
4142 loc.region_1 county
4143 from
4144 hr_locations_all loc,
4145 hr_all_organization_units haou,
4146 fnd_territories_tl ship_territories_tl,
4147 mtl_parameters mp ,
4148 ( SELECT
4149 distinct pas.ship_to_organization_id
4150 FROM pon_auction_shipments_all pas
4151 WHERE
4152 pas.auction_header_id = p_auction_header_id
4153 AND l_is_buyer_negpdf = 'N'
4154 AND l_neg_has_price_breaks = 'Y'
4155 AND pas.shipment_type = 'PRICE BREAK'
4156 and pas.ship_to_location_id is null) pb_organizations
4157 where
4158 l_is_buyer_negpdf = 'N'
4159 and l_neg_has_price_breaks = 'Y'
4160 and haou.organization_id = mp.organization_id
4161 and haou.organization_id = pb_organizations.ship_to_organization_id
4162 and loc.ship_to_site_flag = 'Y'
4163 and (loc.inventory_organization_id is null or nvl(loc.inventory_organization_id, -1) = nvl(pb_organizations.ship_to_organization_id,-1))
4164 and sysdate < nvl(loc.inactive_date, sysdate + 1)
4165 and ship_territories_tl.territory_code(+) = loc.country
4166 and ship_territories_tl.language(+) = l_printing_language
4167 and nvl(loc.business_group_id, nvl(haou.business_group_id, -99))
4168 = nvl(haou.business_group_id, nvl(loc.business_group_id, -99))
4169 )
4170 order
4171 by name;
4172
4173 BEGIN
4174 queryCtx := DBMS_XMLGEN.newContext(pb_loc_cursor);
4175 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_BREAK_LOCATIONS');
4176 DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_BREAK_LOCATIONS_ROW');
4177 DBMS_XMLGEN.getXMLType(queryCtx, pb_loc_res, DBMS_XMLGEN.NONE);
4178 IF pb_loc_res IS NULL THEN
4179 pb_loc_res := XMLType('<PRICE_BREAK_LOCATIONS></PRICE_BREAK_LOCATIONS>');
4180 END IF;
4181 Dbms_Lob.append(xml_clob, pb_loc_res.getCLOBVal());
4182 DBMS_XMLGEN.closeContext (queryCtx);
4183 exception when others then
4184 DBMS_XMLGEN.closeContext (queryCtx);
4185 RAISE;
4186 END;
4187
4188 CLOSE pb_loc_cursor;
4189
4190
4191 OPEN price_diff_types_cursor FOR
4192 SELECT DISTINCT
4193 pov.price_differential_dsp,
4194 pov.price_differential_desc,
4195 pov.price_differential_type
4196 FROM po_price_diff_lookups_v pov;
4197
4198 BEGIN
4199 queryCtx := DBMS_XMLGEN.newContext(price_diff_types_cursor);
4200 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES');
4201 DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES_ROW');
4202 DBMS_XMLGEN.getXMLType(queryCtx, price_diff_types_res, DBMS_XMLGEN.NONE);
4203 IF price_diff_types_res IS NULL THEN
4204 price_diff_types_res := XMLType('<PRICE_DIFFERENTIAL_TYPES></PRICE_DIFFERENTIAL_TYPES>');
4205 END IF;
4206 Dbms_Lob.append(xml_clob, price_diff_types_res.getCLOBVal());
4207 DBMS_XMLGEN.closeContext (queryCtx);
4208 exception when others then
4209 DBMS_XMLGEN.closeContext (queryCtx);
4210 RAISE;
4211 END;
4212
4213 CLOSE price_diff_types_cursor;
4214
4215
4216 OPEN attachments_cursor FOR
4217 select ad.attached_document_id,
4218 d.datatype_name,
4219 d.file_name file_name,
4220 d.description,
4221 d.title as attachment_title,
4222 d.url,
4223 'PON_AUCTION_ITEM_PRICES_ALL' as entity_name,
4224 to_char(paip.auction_header_id) pk1_value,
4225 to_char(paip.line_number) pk2_value,
4226 ad.pk3_value,
4227 categories_tl.user_name category_name
4228 from pon_auction_headers_all pah,
4229 fnd_documents_vl d,
4230 fnd_attached_documents ad,
4231 fnd_document_categories categories,
4232 fnd_document_categories_tl categories_tl,
4233 pon_auction_item_prices_all paip,
4234 financials_system_params_all fsp
4235 where d.document_id = ad.document_id
4236 and
4237 ad.entity_name = 'MTL_SYSTEM_ITEMS'
4238 AND pah.auction_header_id = p_auction_header_id
4239 AND paip.auction_header_id = pah.auction_header_id
4240 and fsp.org_id = pah.org_id
4241 and ad.pk1_value = to_char(fsp.inventory_organization_id)
4242 AND ad.pk2_value = to_char(paip.item_id)
4243 and categories.name='Vendor'
4244 and categories.category_id = d.category_id
4245 and categories.category_id = categories_tl.category_id
4246 and categories_tl.language = l_printing_language
4247 UNION ALL
4248 select ad.attached_document_id,
4249 d.datatype_name,
4250 d.file_name file_name,
4251 d.description,
4252 d.title,
4253 d.url,
4254 ad.entity_name,
4255 ad.pk1_value,
4256 ad.pk2_value,
4257 ad.pk3_value,
4258 categories_tl.user_name category_name
4259 from fnd_documents_vl d,
4260 fnd_attached_documents ad,
4261 fnd_document_categories categories,
4262 fnd_document_categories_tl categories_tl
4263 where d.document_id = ad.document_id
4264 and
4265 ad.entity_name IN ('PON_AUCTION_ITEM_PRICES_ALL',
4266 'PON_AUCTION_HEADERS_ALL')
4267 and ad.pk1_value = to_char(p_auction_header_id)
4268 and (l_is_buyer_negpdf = 'Y' or categories.name='Vendor')
4269 and categories.category_id = d.category_id
4270 and categories.category_id = categories_tl.category_id
4271 and categories_tl.language = l_printing_language
4272 --bidpdf:attachments in bid
4273 UNION ALL
4274 select ad.attached_document_id,
4275 d.datatype_name,
4276 d.file_name file_name,
4277 d.description,
4278 d.title,
4279 d.url,
4280 ad.entity_name,
4281 ad.pk1_value,
4282 ad.pk2_value,
4283 ad.pk3_value,
4284 categories_tl.user_name category_name
4285 from pon_bid_headers pbhs,
4286 fnd_documents_vl d,
4287 fnd_attached_documents ad,
4288 fnd_document_categories categories,
4289 fnd_document_categories_tl categories_tl
4290 where d.document_id = ad.document_id
4291 AND
4292 pbhs.auction_header_id (+) = p_auction_header_id
4293 and pbhs.bid_number (+) = p_bid_number
4294 AND ad.entity_name IN ('PON_BID_HEADERS',
4295 'PON_BID_ITEM_PRICES')
4296 and ad.pk1_value = to_char(p_auction_header_id)
4297 and ad.pk2_value = to_char(pbhs.bid_number)
4298 --and categories.name=pon_auction_pkg.g_supplier_attachment
4299 and ((l_attach_categ_option = 1 AND categories.name = pon_auction_pkg.g_supplier_attachment)
4300 or (l_attach_categ_option = 2 and categories.name = pon_auction_pkg.g_technical_attachment)
4301 or (l_attach_categ_option = 3 and categories.name in (pon_auction_pkg.g_technical_attachment,pon_auction_pkg.g_commercial_attachment)))
4302 and categories.category_id = d.category_id
4303 and categories.category_id = categories_tl.category_id
4304 and categories_tl.language = l_printing_language
4305 --bidpdf:pay item attachments in bid
4306 UNION ALL
4307 select ad.attached_document_id,
4308 d.datatype_name,
4309 d.file_name file_name,
4310 d.description,
4311 d.title,
4312 d.url,
4313 ad.entity_name,
4314 ad.pk1_value,
4315 ad.pk2_value,
4316 ad.pk3_value,
4317 categories_tl.user_name category_name
4318 from pon_bid_headers pbhs,
4319 fnd_documents_vl d,
4320 fnd_attached_documents ad,
4321 fnd_document_categories categories,
4322 fnd_document_categories_tl categories_tl
4323 where d.document_id = ad.document_id
4324 and pbhs.auction_header_id (+) = p_auction_header_id
4325 and pbhs.bid_number (+) = p_bid_number
4326 and
4327 ad.entity_name IN ('PON_BID_PAYMENTS_SHIPMENTS')
4328 and ad.pk1_value = to_char(pbhs.bid_number)
4329 and categories.name = pon_auction_pkg.g_supplier_attachment
4330 and categories.category_id = d.category_id
4331 and categories.category_id = categories_tl.category_id
4332 and categories_tl.language = l_printing_language
4333 UNION ALL
4334 select ad.attached_document_id,
4335 d.datatype_name,
4336 d.file_name file_name,
4337 d.description,
4338 d.title,
4339 d.url,
4340 ad.entity_name,
4341 ad.pk1_value,
4342 ad.pk2_value,
4343 --for bid pdf, it should be bid_payment_id instead of auction_payment_id,
4344 decode(l_is_bidpdf, 'Y',
4345 (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)),
4346 ad.pk3_value) pk3_value,
4347 categories_tl.user_name category_name
4348 from fnd_documents_vl d,
4349 fnd_attached_documents ad,
4350 fnd_document_categories categories,
4351 fnd_document_categories_tl categories_tl
4352 where d.document_id = ad.document_id
4353 and
4354 ad.entity_name IN ('PON_AUC_PAYMENTS_SHIPMENTS')
4355 and ad.pk1_value = to_char(p_auction_header_id)
4356 and (l_is_buyer_negpdf = 'Y' or categories.name = 'Vendor')
4357 and categories.category_id = d.category_id
4358 and categories.category_id = categories_tl.category_id
4359 and categories_tl.language = l_printing_language;
4360
4361 BEGIN
4362 queryCtx := DBMS_XMLGEN.newContext(attachments_cursor);
4363 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ATTACHMENTS');
4364 DBMS_XMLGEN.SetRowTag(queryCtx, 'ATTACHMENTS_ROW');
4365 DBMS_XMLGEN.getXMLType(queryCtx, attachments_res, DBMS_XMLGEN.NONE);
4366 IF attachments_res IS NULL THEN
4367 attachments_res := XMLType('<ATTACHMENTS></ATTACHMENTS>');
4368 END IF;
4369 Dbms_Lob.append(xml_clob, attachments_res.getCLOBVal());
4370 DBMS_XMLGEN.closeContext (queryCtx);
4371 exception when others then
4372 DBMS_XMLGEN.closeContext (queryCtx);
4373 RAISE;
4374 END;
4375
4376 CLOSE attachments_cursor;
4377
4378
4379 OPEN doc_rules_cursor FOR
4380 select
4381 bizrules.name
4382 FROM
4383 pon_auction_headers_all pah
4384 , pon_auc_doctype_rules doctype_rules
4385 , pon_auc_bizrules bizrules
4386 WHERE pah.auction_header_id = p_auction_header_id
4387 AND doctype_rules.bizrule_id = bizrules.bizrule_id
4388 and doctype_rules.doctype_id = pah.doctype_id
4389 and doctype_rules.display_flag = 'Y'
4390 and doctype_rules.validity_flag = 'Y'
4391 and bizrules.name in (
4392 'BID_LIST_TYPE',
4393 'SHOW_BIDDER_NOTES',
4394 'ALLOW_MULTIPLE_ROUNDS',
4395 'ALLOW_WITHDRAW',
4396 'BID_SCOPE',
4397 'BID_QUANTITY_SCOPE',
4398 'BID_FREQUENCY',
4399 'MIN_BID_DECREMENT',
4400 'MANUAL_CLOSE',
4401 'MANUAL_EXTEND',
4402 'AUTO_EXTENSION',
4403 'RANK_INDICATOR',
4404 'BID_RANKING',
4405 'ALLOW_PRICE_ELEMENT',
4406 'AWARD_APPROVAL_REQUIRED',
4407 'DISPLAY_REQ_LINE_INTEGRATION_SOURCE',
4408 'DISPLAY_LINE_INTEGRATION_SOURCE',
4409 'GLOBAL_AGREEMENT',
4410 'ALLOW_COLLABORATION_TEAM',
4411 'START_PRICE',
4412 'TARGET_PRICE',
4413 'CURRENT_PRICE',
4414 'CONTRACT_TYPE',
4415 'ALLOW_PROXYBID',
4416 'MIN_RELEASE_AMOUNT',
4417 'BEST_PRICE',
4418 'ALLOW_STAGGERED_AWARDS'
4419 );
4420
4421 BEGIN
4422 queryCtx := DBMS_XMLGEN.newContext(doc_rules_cursor);
4423 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_TYPE_RULES');
4424 DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_TYPE_RULES_ROW');
4425 DBMS_XMLGEN.getXMLType(queryCtx, doc_rules_res, DBMS_XMLGEN.NONE);
4426 IF doc_rules_res IS NULL THEN
4427 doc_rules_res := XMLType('<DOCUMENT_TYPE_RULES></DOCUMENT_TYPE_RULES>');
4428 END IF;
4429 Dbms_Lob.append(xml_clob, doc_rules_res.getCLOBVal());
4430 DBMS_XMLGEN.closeContext (queryCtx);
4431 exception when others then
4432 DBMS_XMLGEN.closeContext (queryCtx);
4433 RAISE;
4434 END;
4435
4436 CLOSE doc_rules_cursor;
4437
4438 OPEN generic_msgs_cursor FOR
4439 select message_name,
4440 message_text
4441 from
4442 fnd_new_messages
4443 where message_name in ('PON_AUC_TITLE', --title
4444 'PON_AUCTS_OPEN', -- Open Date
4445 'PON_AUCTS_CLOSE', -- Close Date
4446 'PON_AUCTS_PREVIEW', -- Preview Date
4447 'PON_AUCTS_AWARD', -- Award Date
4448 'PON_AUC_IMMEDIATELY', -- Immediately
4449 'PON_AUC_STYLE', -- Style
4450 'PON_EFFECTIVE_START_DATE', -- Effective Start Date
4451 'PON_EFFECTIVE_END_DATE', -- Effective End Date
4452 'PON_ACCTS_BUYER', -- Buyer
4453 'PON_AUCTS_SHIP_TO_ADDRESS', --Ship-To Address
4454 'PON_AUCTS_BILL_TO_ADDRESS', --Bill-To Address
4455 'PON_AUCTS_PAYMENT_TERMS', --Payment Terms
4456 'PON_AUCTS_CARRIER', --Carrier
4457 'PON_AUCTS_FOB', --FOB
4458 'PON_AUCTS_FREIGHT_TERMS', --Freight Terms
4459 'PON_INTEL_AMOUNT', --Amount
4460 'PON_AUCTS_PRICE_PREC', -- Price Precision
4461 'PON_AUCTS_LINE_NO', --Line No.
4462 'PON_AUCTS_UNIT_PRICE', --Unit Price
4463 'PON_AUCTS_NUMBER_OF_UNITS', --Number of Units
4464 'PON_AUCTS_PRN_GENERAL_INFO', -- L.1. General Information
4465 'PON_AUCTS_PRN_TERMS', --I.2 Terms
4466 'PON_AUCTS_PRN_PRICE_SCHEDULE', --2 Price Schedule
4467 'PON_AUCTS_PRN_LINE_INFO', --2.1 Line Information
4468 'PON_AUCTS_EXCHANGE_RATE', --Exchange Rate
4469 'PON_AUCTS_HEADER_INFORMATION', --1 Header Information
4470 'PON_AUC_WEIGHT', --Weight
4471 'PON_AUC_REQUIRES_NO_RESP', --This requires no response.
4472 'PON_AUCTS_OPTIONAL_RESP', --The response is optional.
4473 'PON_AUCTS_MUST_PROVIDE_RESP', --You must provide a response.
4474 'PON_AUCTS_RESP_MUST_BE_NUMERIC', --The response must be a numeric value.
4475 'PON_AUCTS_RESP_MUST_BE_DATE', --The response must be a date value.
4476 'PON_AUC_RESPONSE_VALUE', --Response Value
4477 'PON_AUC_PRN_LINE_ATTR_NOTE', --You must provide a response unless otherwise indicated.
4478 'PON_AUC_ACCEPTABLE_VALUES', --Acceptable Values
4479 'PON_AUC_ATTRIBUTES', --Attributes
4480 'PON_AUC_PRN_REF_ONLY_NO_RESP', --This is for reference only and your response is not required.
4481 'PON_AUC_ANY', --Any
4482 'PON_AUC_NOT_SPECIFIED', --Not Specified
4483 'PON_AUCTION_CURRENCY', --Currency
4484 'PON_AUC_CURRENCY_DESCRIPTION', --Currency escription
4485 'PON_AUC_SHIP_TO', --Ship To
4486 'PON_AUCTS_NEED_BY_DATE', --Need-By Date
4487 'PON_AUC_TARGET_VALUE', --Target value
4488 'PON_AUC_ENSURE_CURR_SELECTED', --Please ensure that you have selected a currency in Section I.2
4489 'PON_AUC_TIME_ZONE', --Time Zone
4490 'PON_AUCTS_EMAIL', --Email
4491 'PON_AUCTS_PHONE', --Phone
4492 'PON_AUCTS_FAX', --Fax
4493 'PON_AUC_CONTACT_DETAILS', --Contact Details
4494 'PON_AUC_YOUR_COMPANY_NAME', --Your Company Name
4495 'PON_AUC_NOTE_TO_SUPPLIER', --Note to Supplier
4496 'PON_AMEND_DESCRIPTION', --Amendment Description
4497 'PON_AMEND_DATE', --Amendment Date
4498 'PON_AUC_RULES_FOR_REFERENCE', --These rules are for your reference. Please do not check any checkboxes.
4499 '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.
4500 '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.
4501 'PON_AUC_OPTIONAL_PB_3', -- You may propose price breaks in the space provided or on a separate sheet of paper.
4502 'PON_AUC_OPTIONAL_PB_4', -- Suppliers may propose price breaks.
4503 'PON_AUC_REQUIRED_PB', --You must enter a price for each line in the table.
4504 'PON_AUC_REQUIRED_PB_2', -- Suppliers must enter a price for each line in the table.
4505 'PON_AUC_CUMULATIVE_PB', --The break quantity is cumulative.
4506 'PON_AUC_NON_CUMULATVE_PB', --The break quantity is non-cumulative.
4507 'PON_AUC_PB_VIEW_SHIP_TO', --To view Ship-To addresses, refer to section
4508 'PON_AUC_LOCATION_PRICING', --Location Pricing
4509 'PON_AUC_REFER_ATTACH_PE', --Please refer to the attachments for price elements included in this line.
4510 'PON_AUC_REFER_ATTACH_PD', --Please refer to the attachments for price differentials included in this line.
4511 'PON_AUC_RFR_ATTACH_PD_LOC_PRC', --Please refer to the attachments for price differentials included in the location pricing for this line.
4512 'PON_AUC_PART_I_HEADER_INFO_C', --PART I: HEADER INFORMATION
4513 'PON_AUC_HEADER_ATTRIBUTES', --Header Attributes
4514 'PON_AUC_PRN_PRICE_SCHEDULE_C', --2 Price Schedule
4515 'PON_ITEM_DETAILS', --Line Details
4516 'PON_AUC_TABLE_OF_CONTENTS_C', --TABLE OF CONTENTS
4517 'PON_AUC_RESPONSE_RULES', --Response Rules
4518 'PON_AUC_NR_CONTROL_MSG_1', --Negotiation is restricted to invited suppliers
4519 'PON_AUC_NR_CONTROL_MSG_2', --Suppliers are allowed to view other suppliers notes and attachments
4520 'PON_AUC_NR_CONTROL_MSG_2A', --Suppliers are allowed to view other suppliers' contract terms, notes and attachments
4521 'PON_AUC_NR_CONTROL_MSG_9', --Buyer may create multiple rounds of negotiations
4522 'PON_AUC_NR_CONTROL_MSG_3', --Suppliers are allowed to respond to selected lines
4523 'PON_AUC_NR_CONTROL_MSG_4', --Suppliers are required to respond with full quantity on each line
4524 'PON_AUC_NR_CONTROL_MSG_5', --Allow multiple responses
4525 'PON_AUC_NR_CONTROL_MSG_14', --Suppliers are required to lower the line price when submitting a revised response
4526 'PON_AUC_NR_CONTROL_MSG_7', --Buyer may close the negotiation before the Close Date
4527 'PON_AUC_PRN_ALLOW_MANUAL_EXT', --Buyer may manually extend the negotiation while it is open
4528 'PON_AUCTION_PROMISE_DATE', --Promise Date
4529 'PON_AUCTS_DESCRIPTION', --Description
4530 'PON_AUCTS_NAME', --Name
4531 'PON_AUC_DATA_TYPE', --Data Type
4532 'PON_AUC_UNDEFINED', --Undefined
4533 'PON_AUC_GROUP_WEIGHT', --Group Weight
4534 'PON_AUCTS_ITEM_DESC', -- Description
4535 'PON_ITEM_REV', -- Item, Rev
4536 'PON_ITEM_REV_JOB', --Item, Rev / Job
4537 'PON_AUCTS_CATEGORY', --Category
4538 'PON_SHOPPING_CAT', -- Shopping Category
4539 'PON_AUCTS_UOM', -- Unit
4540 'PON_AUC_CICRLE_RESP_BELOW', --Circle one from the response values below:
4541 'PON_AUC_CIRCLE_RESPONSE_BELOW', -- (Circle one from the response values below):
4542 'PON_AUC_UP_TO', --Up to
4543 'PON_AUC_OPTIONAL_RESP', --It is optional for you to provide a response.
4544 'PON_AUC_MAX_SCORE', --Maximum Score
4545 'PON_AUCTS_RESP_MUST_BE_URL', --The response must be an URL value.
4546 'PON_AUC_PRICE_TYPE_CIRCLE_VAL', --Price Type (Circle one value)
4547 'PON_AUC_EFFECTIVE_FROM_DATE', --Effective From Date
4548 'PON_AUC_EFFECTIVE_TO_DATE', --Effective To Date
4549 'PON_BIDS_PRICE', --Price
4550 'PON_AUC_DISCOUNT_PERCENTAGE', --Discount %
4551 'PON_AUC_ADDRESS', --Address
4552 'PON_AUC_LEAVE_BLANK', --Leave blank
4553 'PON_AUC_ENTER_IN_ATTR_TABLE', --Enter in the Attributes Table below
4554 'PON_AUC_SCORE_FOR_RESPONSE', --(Score for the response)
4555 'PON_AUCTION_QUANTITY', --Quantity
4556 'PON_AUC_PRN_CONTRACT_WARNING', --Note: This document does not include Contract Terms because the buyer does not have permission to view them.
4557 'PON_AUCTS_COMPANY', --Company
4558 'PON_AUC_LOCATION', --Location
4559 'PON_JOB_DETAILS', --Job Details
4560 'PON_AUCTION_LOT', --Lot
4561 'PON_AUCTION_GROUP', --Group
4562 'PON_FO_PROPRIETARY_INFORMATION', --Proprietary and Confidential
4563 'PON_PAGE', -- Page PAGE_NUM of END_PAGE
4564 'PON_AUC_SUBMIT_UR_RESPOSE_TO', -- Please submit your response to:
4565 'PON_AUC_INCLD_FOLLOWING_INFO', -- When submitting your response, please include the following information.
4566 'PON_AUC_BID_VALID_UNTIL', -- Response Valid Until
4567 'PON_AUC_SECURITY_LEVEL', -- Security Level
4568 'PON_AUC_APPROVAL_STATUS', -- Approval Status
4569 'PON_OPERATING_UNIT', -- Operating Unit
4570 'PON_AUC_OUTCOME', -- Outcome
4571 'PON_AUC_NEGOTIATION_STYLE', -- Negotiation Style
4572 'PON_AUCTS_AUCTION_EVENT', -- Event
4573 'PON_SOURCING_PROJECT', -- Sourcing Project
4574 'PON_AUC_APPROVAL_REQUIRED', -- Requires Approval
4575 'PON_AUC_APPROVAL_NOT_REQUIRED', -- Requires No Approval
4576 'PON_AUC_APPROVAL_APPROVED', -- Approved
4577 'PON_AUC_APPROVAL_REJECTED', -- Rejected
4578 'PON_AUC_APPROVAL_INPROCESS', -- In Process
4579 'PON_AUC_COLLABORATION_TEAM', -- Collaboration Team
4580 'PON_AUC_MEMBER_ROLE', -- Member
4581 'PON_AUC_POSITION', -- Position
4582 'PON_AUC_APPROVER', -- Approver
4583 'PON_AUC_ACCESS', -- Access
4584 'PON_AUC_TASK', -- Task
4585 'PON_AUC_TARGET_DATE', -- Target Date
4586 'PON_CORE_YES', -- Yes
4587 'PON_CORE_NO', -- No
4588 'PON_AUCTS_GLOBAL_AGREEMENT', -- Global Agreement
4589 'PON_AUC_ELIGIBLE_RESP_CURR', -- Eligible Response Currencies
4590 'PON_AUC_CHECK_RESP_CURR', -- Check the one currency in which you will enter your response.
4591 'PON_AUC_EX_RATE_TYPE', -- Exchange Rate Type
4592 'PON_AUC_EX_RATE_DATE', -- Exchange Rate Date
4593 'PON_DISP_TO_SUPPLIERS', -- Display To Suppliers
4594 'PON_DO_NOT_DISP_TO_SUPPLIERS', -- Do Not Display to Suppliers
4595 'PON_AUC_DISPLAY_SCORE_2', -- Display scoring criteria to Suppliers
4596 'PON_ABSTRACT_FORMS', -- Abstract and Forms
4597 'PON_AUC_VERSION', -- Version
4598 'PON_AUC_NR_CONTROL_MSG_6', -- Buyer is required to obtain approval of award decisions
4599 'PON_AUC_NR_CONTROL_MSG_13', -- Negotiation is allowed to AutoExtend
4600 'PON_AUC_NR_CONTROL_MSG_15', -- Negotiation is allowed to AutoExtend based on the following settings
4601 'PON_AUC_NR_CONTROL_MSG_16', -- Show best price to a supplier in a blind negotiation
4602 'PON_AUC_NR_CONTROL_MSG_18', -- Enforce supplier's previous round price as start price for this round
4603 'PON_AUC_NR_CONTROL_MSG_19', -- Negotiation can have staggered awards
4604 'PON_AUTO_EXTEND_SETTINGS', -- AutoExtend Settings
4605 'PON_START_TIME_EXTEND', -- Start Time of Extensions
4606 'PON_NUMBER_OF_EXTENSIONS', -- Number of Extensions
4607 'PON_AUTO_EXTEND_PERIOD', -- AutoExtend Period
4608 'PON_LINES_TO_AUTO_EXTEND', -- Lines to AutoExtend
4609 'PON_AUCTS_CLOSE_DATE', -- Close Date
4610 'PON_AUTOEXT_TIME_2', -- Receipt time of the triggering winning response
4611 'PON_AUTOEXT_ITEM_2', -- Lines that have received winning responses during the AutoExtend period
4612 'PON_TRIGGERING_RESPONSE', -- Triggering Response
4613 'PON_LOW_TRIGG_RESP_RANK', -- Lowest Triggering Response Rank
4614 'PON_AUTOEXT_RESPONSE_1', -- Response with winning lines
4615 'PON_AUTOEXT_RESPONSE_2', -- Any Response
4616 'PON_AUC_UNLIMITED', -- Unlimited
4617 'PON_AUC_MINUTES', -- Minutes
4618 'PON_AUCTS_ALL_ITEMS', -- All Lines
4619 'PON_AUTOEXT_ITEM_3', -- Lines that have received responses during the AutoExtend period
4620 'PON_AUC_DISPLAY_RANK', -- Display Rank As
4621 'PON_AUC_RANKING', -- Ranking
4622 'PON_AUC_PRICE_ELEMENTS', -- Price Factors
4623 'PON_AUC_SUPPLIER_VIEW', -- Suppliers see their response price transformed
4624 'PON_AUC_ENTER_IN_PF_TABLE', -- Enter in the Cost Factors table below
4625 'PON_AUC_REQUISITION', -- Requisition
4626 'PON_AUC_MULTIPLE', -- Multiple
4627 'PON_AUC_LINE_TYPE', -- Line Type
4628 'PON_AUC_PRICE_ELEMENT', -- Price Factor
4629 'PON_AUC_PRICE_ELEMENT_DESC', -- Description
4630 'PON_AUC_NEGATIVE_COST_FACTOR', -- Negative Cost Factor
4631 'PON_AUCTS_TYPE', -- Type
4632 'PON_AUCTS_DISP_TO_BIDDER', -- Display To Suppliers
4633 'PON_AUC_PRICING_BASIS', -- Pricing Basis
4634 'PON_AUCTS_ATTR_D_TARGET', -- Display Target
4635 'PON_AUCTS_BID_VALUE', -- Response Value
4636 'PON_AUCTION_ITEM_PRICE', -- Line Price
4637 'PON_AUC_PRICE_FACTOR_NOTE_1', -- It is required for you to enter a response value for the Supplier Price Factors.
4638 'PON_AUC_PRICE_FACTOR_NOTE_2', -- Negative Cost Factors will allow subtract the cost factor from the line total.
4639 'PON_PRICE_DIFFERENTIAL_DESC', -- Description
4640 'PON_TARGET_MULTIPLIER', -- Target Multiplier
4641 'PON_AUC_RESP_MULTIPLIER', -- Response Multiplier
4642 'PON_PRICE_DIFFERENTIALS', -- Price Differentials
4643 'PON_AUC_PRICE_DIFF_NOTE_1', -- Suppliers must enter a response multiplier for each line in the table.
4644 'PON_AUC_PRICE_DIFF_NOTE_2', -- It is optional for suppliers to enter a response multiplier for each line in the table.
4645 'PON_AUC_PRICE_DIFF_NOTE_3', -- You must enter a response multiplier for each line in the table.
4646 'PON_AUC_PRICE_DIFF_NOTE_4', -- It is optional for you to enter a response multiplier for each line in the table.
4647 'PON_AUCTS_PRICE_BREAKS', -- Price Breaks
4648 'PON_AUCTS_PRICE_BREAK', -- Price Break
4649 'PON_AUCTS_TARGET_PRICE', -- Target Price
4650 'PON_AUC_SHIP_TO_ADDRESSES', -- Ship-To Addresses
4651 'PON_INVITED_SUPPLIERS', -- Invited Suppliers
4652 'PON_ACCTS_SUPPLIER', -- Supplier
4653 'PON_AUCTS_SUPPLIER_SITE', -- Supplier Site
4654 'PON_AUCTS_CONTACT', -- Contact
4655 'PON_AUC_ADDNL_EMAIL', -- Additional Contact Email
4656 'PON_AUC_RESPONSE_CURR', -- Response Currency
4657 'PON_ANY_RESPONSE_CURRENCY', -- Any Response Currencies
4658 'PON_NEG_FULL', -- Full
4659 'PON_NEG_RESTRICTED', -- Restricted
4660 'PON_AUC_BUYER_PF_VALUES', -- Buyer Price Factor Values
4661 'PON_HEADER_INFORMATION', -- Header Information
4662 'PON_PRICE_SCHEDULE', -- Price Schedule
4663 'PON_AUCTS_ATTACHMENTS', -- Attachments
4664 'PON_LINE_BID_OPTIONAL', -- It is optional for you to respond to this line.
4665 'PON_AUC_BIDDER_ADDRESS' --Address
4666 ,'PON_DECREMENT_METHOD_MSG' -- Suppliers are required to lower the line price from the best response
4667 ,'PON_ADVANCE_AMOUNT_PROMPT' -- Advance Amount
4668 ,'PON_FINANCING' -- Financing
4669 ,'PON_RETAINAGE' -- Retainage
4670 ,'PON_DEFAULT_PROJECT_INFO' -- Default Project Information
4671 ,'PON_DEFAULT_OWNER' -- Default Owner
4672 ,'PON_DESCRIPTION' -- Description
4673 ,'PON_EXPENDITURE_ITEM_DATE' -- Expenditure Item Date
4674 ,'PON_EXPENDITURE_ORGANIZATION' -- Expenditure Organization
4675 ,'PON_EXPENDITURE_TYPE' -- Expenditure Type
4676 ,'PON_FLAG_DISPLAY_NO' -- Yes
4677 ,'PON_FLAG_DISPLAY_YES' -- No
4678 ,'PON_GOODS_LINE_PAY_ITEM_MSG' -- Unit Price for each pay item is based on the Number of Units quoted for this line
4679 ,'PON_MAXIMUM_RETAINAGE_AMOUNT' -- Maximum Retainage Amount
4680 ,'PON_PAYMENT_INFORMATION' -- Pay Item Information
4681 ,'PON_PAYMENT_TIP_FINANCE' -- Total pay item amount may not add up to the line amt
4682 ,'PON_PAYMENT_TIP_ACTUAL' -- Total pay item amount must add up to the line amt
4683 ,'PON_PAY_ITEM' -- Pay Item
4684 ,'PON_NEGOTIABLE' -- Negotiable
4685 ,'PON_FINANCING_ATTRIBUTES' -- Financing Attributes
4686 ,'PON_RETAINAGE_ATTRIBUTES' -- Retainage Attributes
4687 ,'PON_PROGRESS_PAYMENT_RATE' -- Progress Payment Rate
4688 ,'PON_PROJECT' -- Project
4689 ,'PON_PROJECT_INFORMATION' -- Project Information
4690 ,'PON_RECOUPMENT_RATE' -- Recoupment Rate
4691 ,'PON_RETAINAGE_RATE' -- Retainage Rate
4692 ,'PON_SUPP_ENTERABLE_PYMT_FLAG' -- Supplier can modify Pay Items
4693 ,'PON_SUPP_UPD_PAY_ITEMS_1' -- Suppliers may propose pay items.
4694 ,'PON_SUPP_UPD_PAY_ITEMS_2' -- You may propose pay items in the space provided or on a separate sheet of paper.
4695 ,'PON_SUPP_UPD_PAY_ITEMS_3' -- Suppliers may propose different pay items.
4696 ,'PON_SUPP_UPD_PAY_ITEMS_4' -- You may propose different pay items in the space provided or on a separate sheet of paper.
4697 ,'PON_TASK' -- Task
4698 ,'PON_UNITS' -- Units
4699 ,'PON_OWNER' -- Owner
4700 ,'PON_TEAM_SCORING' -- Team Scoring
4701 ,'PON_TEAM_SCORING_ENABLED' -- Team Scoring enabled
4702 ,'PON_TEAM' -- Team
4703 ,'PON_MEMBERS' -- Members
4704 ,'PON_TEAM_INSTRUCTIONS' -- Team Instructions
4705 ,'PON_PRICE_VISIBILITY' -- Price Visibility
4706 ,'PON_SECTION_ASSIGNMENT' -- Section Assignment
4707 ,'PON_AUC_REQUIREMENTS' -- Requirements
4708 ,'PON_AUC_SECTION_WEIGHT' -- Section Weight
4709 ,'PON_AUC_KO_SCORE' -- Knockout Score
4710 ,'PON_AUC_INTERNAL' -- Internal
4711 ,'PON_AUC_AUTOMATIC' -- Automatic
4712 ,'PON_AUC_NONE' -- None
4713 ,'PON_AUC_MANUAL' -- Manual
4714 ,'PON_AUCTS_ATTR_DATATYPE' -- Value Type
4715 ,'PON_AUC_SCORING' -- Scoring
4716 ,'PON_AUC_SCORE_DISPLAYED' -- score displayed in brackets
4717 ,'PON_PROVIDE_ANSWER' -- Provide your answer below
4718 ,'PON_STAGGERED_CLOSING_MSG' -- Staggered Closing
4719 ,'PON_AUCTS_STAG_FIRST_CLOSE_DAT' -- First Line Close date
4720 ,'PON_STAGGERED_CLOSE_INTERVAL' -- Staggered Closing Interval
4721 ,'PON_BID_RESPONSE_STATUS' --Response Status
4722 ,'PON_BID_RESPONSE_SUBMITTED' --Your response has been submitted to:
4723 ,'PON_BID_RESPONSE_WILLSUBMITTED' --Your response will be submitted to:
4724 ,'PON_BID_YOUR_INFO' --Your information is:
4725 ,'PON_AUCTS_YOUR_BID_NUMBER' --Reference Number
4726 ,'PON_AUCTS_NOTE_TO_BUYER' --Note to Buyer
4727 ,'PON_BID_YOUR_REQ_RESPONSE' -- Your response value:
4728 ,'PON_AUC_SURROG_RECVD_TIME' -- Response Received Time
4729 ,'PON_BID_BUYER_ATTACHMENTS' -- Buyer Attachments
4730 ,'PON_BID_SUP_ATTACHMENTS' -- Supplier Attachments
4731 ,'PON_AUCTS_BID_MIN_REL_AMT' -- Bid Minimum Release Amount
4732 ,'PON_BIDS_RESPONSE_PRICE' -- Response Price
4733 ,'PON_AUC_PRICE_SCORE' -- Price/Total Score
4734 ,'PON_AUCTS_PROXY_MIN' --Proxy Minimum
4735 ,'PON_AUCTS_PROXY_DEC' --Proxy Response Decrement
4736 ,'PON_BID_TOTAL_WARNING' --Cannot be displayed because quantity is not available on all lines
4737 ,'PON_AUCTS_PRICE_TYPE' --Price Type
4738 ,'PON_BIDS_PRICE_OR_DISCOUNT' --Response Price or Discount%
4739 ,'PON_BID_BUYER_PI_ATTACHMENTS' -- Buyer Pay Item Attachments
4740 ,'PON_BID_SUP_PI_ATTACHMENTS' -- Supplier Pay Item Attachments
4741 ,'PON_BID_YOUR_RESPONSE_BRACKET' -- Your response value (score displayed in brackets):
4742 ,'PON_BID_YOUR_SITE' -- Your Company Site
4743 ,'PON_BIDS_NO_RESPONSE' -- No Response
4744 ,'PON_BUYER_PDF_TXT' -- Message for buyer view pdf
4745 ,'PON_SUPPLIER_PDF_TXT' -- Message for supplier view pdf
4746 ,'PON_AUCTS_PRICE_TIERS' -- Quantity based price tiers
4747 ,'PON_TIERS_MIN_QUANTITY' -- Minimum Quantity
4748 ,'PON_TIERS_MAX_QUANTITY' -- Maximum Quantity
4749 ,'PON_AUC_TARGET_QUANTITY' --Target Quantity
4750 ,'PON_AUCTS_RESP_QUANTITY' --Response Quantity
4751 ,'PON_AUCTION_PRICE' -- Price
4752
4753 ------------------Begin: Add by Chaoqun for adding EMD info into Printable View on 6-NOV-2008----------
4754 , 'PON_PRN_EMD_INFO' -- L.1. EMD Information
4755 , 'PON_EMD_TYPE' -- EMD Type
4756 , 'PON_EMD_DUE_DATE' -- EMD Due Date
4757 , 'PON_EMD_AMOUNT' -- EMD Amount
4758 , 'PON_EMD_GUAR_EXPIRY_DES_PRE' -- Bank Guarantee Expiry Date
4759 , 'PON_EMD_GUAR_EXPIRY_DES_POST'
4760 , 'PON_EMD_ADDITIONAL_INFO' --Additional EMD Information
4761 , 'PON_EMD_ENABLE_FLAG' --EMD Enable Flag DEscription
4762 ------------------End: Add by Chaoqun for adding EMD info into Printable View on 6-NOV-2008------------
4763 , 'PON_EMD_AMT_RESP_CURR' -- bug 8667493 EMD amount in eligible response currencies
4764 )
4765 and application_id = 396
4766 and language_code = l_printing_language;
4767
4768 BEGIN
4769 queryCtx := DBMS_XMLGEN.newContext(generic_msgs_cursor);
4770 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GENERIC_MESSAGES');
4771 DBMS_XMLGEN.SetRowTag(queryCtx, 'GENERIC_MESSAGES_ROW');
4772 DBMS_XMLGEN.getXMLType(queryCtx, generic_msgs_res, DBMS_XMLGEN.NONE);
4773 IF generic_msgs_res IS NULL THEN
4774 generic_msgs_res := XMLType('<GENERIC_MESSAGES></GENERIC_MESSAGES>');
4775 END IF;
4776 Dbms_Lob.append(xml_clob, generic_msgs_res.getCLOBVal());
4777 DBMS_XMLGEN.closeContext (queryCtx);
4778 exception when others then
4779 DBMS_XMLGEN.closeContext (queryCtx);
4780 RAISE;
4781 END;
4782
4783 CLOSE generic_msgs_cursor;
4784
4785
4786 OPEN doc_msgs_cursor FOR
4787 select substr(message_name,1,length(message_name)-2) message_name,
4788 message_text
4789 FROM
4790 pon_auction_headers_all pah,
4791 pon_auc_doctypes doctypes,
4792 fnd_new_messages msgs
4793 where pah.auction_header_id = p_auction_header_id
4794 AND doctypes.doctype_id = pah.doctype_id
4795 AND msgs.message_name in (
4796 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.
4797 pon_printing_pkg.get_document_message_name('PON_RESPONSE_STYLE',doctypes.message_suffix), -- Response Style
4798 pon_printing_pkg.get_document_message_name('PON_ALLOW_WITHDRAW_FLAG',doctypes.message_suffix) -- Allow Withdraw Flag
4799 ) and msgs.application_id =396
4800 and msgs.language_code = l_printing_language;
4801
4802
4803 BEGIN
4804 queryCtx := DBMS_XMLGEN.newContext(doc_msgs_cursor);
4805 DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES');
4806 DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES_ROW');
4807 DBMS_XMLGEN.getXMLType(queryCtx, doc_msgs_res, DBMS_XMLGEN.NONE);
4808 IF doc_msgs_res IS NULL THEN
4809 doc_msgs_res := XMLType('<DOCUMENT_SPECIFIC_MESSAGES></DOCUMENT_SPECIFIC_MESSAGES>');
4810 END IF;
4811 Dbms_Lob.append(xml_clob, doc_msgs_res.getCLOBVal());
4812 DBMS_XMLGEN.closeContext (queryCtx);
4813 exception when others then
4814 DBMS_XMLGEN.closeContext (queryCtx);
4815 RAISE;
4816 END;
4817
4818 CLOSE doc_msgs_cursor;
4819
4820 dbms_lob.createtemporary(res, TRUE);
4821 queryCtx := DBMS_XMLGEN.newContext('select null from dual');
4822 DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
4823 DBMS_XMLGEN.SetRowTag(queryCtx, null);
4824 DBMS_XMLGEN.getXML(queryCtx, res, DBMS_XMLGEN.NONE);
4825 DBMS_XMLGEN.closeContext (queryCtx);
4826
4827 result := res || '<ROWSET>' || '<ROW>' || xml_clob || '</ROW>' || '</ROWSET>';
4828
4829 SELECT CURRENT_DATE INTO l_end_time FROM DUAL;
4830
4831 IF l_statement_log_level >= l_current_log_level THEN
4832 FND_LOG.string(l_statement_log_level, l_module_name, 'PDF: generating XML time: ' || (l_end_time - l_start_time) * 24 * 60 * 60);
4833 END IF;
4834
4835 return result;
4836
4837 END GENERATE_AUCTION_XML;
4838
4839
4840 END PON_PRINTING_PKG;