1 PACKAGE BODY PON_CONSOLE_PVT AS
2 /* $Header: PONVCONB.pls 120.4 2011/11/03 05:42:06 puppulur ship $ */
3
4 ---
5 --- +=======================================================================+
6 --- | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
7 --- | All rights reserved. |
8 --- +=======================================================================+
9 --- |
10 --- | FILENAME
11 --- | PONVCONB.pls
12 --- |
13 --- |
14 --- | DESCRIPTION
15 --- | This package contains procedures called from the live console
16 --- | the Supplier activities function
17 --- |
18 --- | HISTORY
19 --- |
20 --- | 14-Jun-2004 sparames Created.
21 --- | 30-Jun-2004 sahegde Added procedrues for supplier activities,
22 --- | debugging
23 --- |
24 --- +=======================================================================+
25 ---
26
27 -- The external procedure returns the dates individually in separate
28 -- columns since Java does not understand arrays. Internally, the
29 -- procedures pass around an array of dates
30
31 TYPE datetbltype IS TABLE OF DATE INDEX BY BINARY_INTEGER;
32
33 -- Since the console plots all dates as numbers, it is required to
34 -- convert a given date to a number. It is assumed that the total
35 -- range of the graph in numbers is 2882880 (12*24*60*11*12*13*14).
36 -- Hence for plotting a value, one has to take the difference between
37 -- the date (bid published date) and the starting date of the graph
38 -- and factor it according to the total range which equals 2882880
39
40 g_graph_range CONSTANT PLS_INTEGER := 2882880;
41
42 -- The user will see a maximum of 13 divisions. 14 is used to include
43 -- the start and end ticks
44
45 g_max_no_ticks CONSTANT PLS_INTEGER := 14;
46
47 -- private package variable for debugging purpose
48 g_pkg_name CONSTANT varchar2(30) := 'PON_CONSOLE_PVT';
49
50 --------------------------------------------------------------------------------
51 -- Private procedure definitions --
52 --------------------------------------------------------------------------------
53 -- private function to determine if debug level is enabled at statement level
54 FUNCTION is_debug_statement_on
55 RETURN BOOLEAN IS
56 l_debug_statement BOOLEAN;
57 BEGIN
58
59 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
60 l_debug_statement := TRUE;
61 ELSE
62 l_debug_statement := FALSE;
63 END IF;
64 RETURN l_debug_statement;
65 END;
66
67
68 -- private function to determine if debug level is enabled at unexpected level
69 FUNCTION is_debug_unexpected_on
70 RETURN BOOLEAN IS
71 l_debug_unexpected BOOLEAN;
72 BEGIN
73
74 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
75 l_debug_unexpected := TRUE;
76 ELSE
77 l_debug_unexpected := FALSE;
78 END IF;
79 RETURN l_debug_unexpected;
80 END;
81 --
82 -- Comments for get_date_ params - see detailed definition lower in
83 -- the package body
84 --
85
86 PROCEDURE get_date_params( p_start_date IN DATE,
87 p_end_date IN DATE,
88 x_graph_start_date OUT NOCOPY DATE,
89 x_graph_duration OUT NOCOPY NUMBER,
90 x_number_of_ticks OUT NOCOPY PLS_INTEGER,
91 x_tick_duration_days OUT NOCOPY NUMBER,
92 x_start_offset_value OUT NOCOPY VARCHAR2,
93 x_start_offset_unit OUT NOCOPY VARCHAR2,
94 x_output_date_format OUT NOCOPY VARCHAR2);
95
96 --
97 -- Comments for get_tick_values - see detailed definition lower in
98 -- the package body
99 --
100
101 PROCEDURE get_tick_values (
102 p_start_date IN DATE,
103 p_end_date IN DATE,
104 p_graph_duration IN NUMBER,
105 p_number_of_ticks IN PLS_INTEGER,
106 p_tick_duration_days IN NUMBER,
107 p_start_offset_value IN PLS_INTEGER,
108 p_start_offset_unit IN VARCHAR2,
109 p_output_date_format IN VARCHAR2,
110 x_graph_start_date OUT NOCOPY DATE,
111 x_graph_end_date OUT NOCOPY DATE,
112 x_graph_duration OUT NOCOPY NUMBER,
113 x_number_of_ticks OUT NOCOPY NUMBER,
114 x_tick_date_values OUT NOCOPY dateTblType
115 );
116
117 -----------------------------------------------------------------
118 -- get_date_params ----
119 -----------------------------------------------------------------
120 --
121 -- Start of Comments
122 --
123 -- API Name: get_date_params
124 --
125 -- Type : Private
126 --
127 -- Pre-reqs: None
128 --
129 -- Function: This API is called internally within this package and
130 -- is used to get the parameters for a given set of dates
131 --
132 -- Parameters:
133 --
134 -- p_start_date IN DATE
135 -- Required - start date for determining the range
136 --
137 -- p_end_date IN DATE
138 -- Required - end date of the range
139 --
140 -- x_graph_start_date OUT DATE
141 -- If the start date was less than an hour earlier than
142 -- the end date, then the new start date for the graph
143 -- will be an hour earlier than the end date
144 --
145 -- x_graph_duration OUT NUMBER
146 -- This is the duration of the graph in days
147 --
148 -- x_number_of_ticks OUT PLS_INTEGER
149 -- This is the number of ticks. The actual number of ticks
150 -- will be one more than what is given here
151 --
152 -- x_tick_duration_days OUT NUMBER
153 -- This is the duration of each tick in days
154 --
155 -- x_start_offset_value OUT VARCHAR2
156 -- Since in most cases, it would not be wise to start the
157 -- graph exactly on the start date, we round down to the
158 -- nearest time unit based on the scale of the graph. This
159 -- contains the value e.g 5 if the rounding is to the nearest
160 -- 5 mins
161 --
162 -- x_start_offset_unit OUT VARCHAR2
163 -- This contains the unit of rounding as explained above
164 --
165 -- x_output_date_format OUT VARCHAR2
166 -- This contains the format in which the ticks are
167 -- labelled. Depending on the scale of the graph, only
168 -- the date, or the date+time or only the time is shown
169 --
170 -----------------------------------------------------------------
171 --
172 -- The following table shows the logic that is used to determine
173 -- the parameters for each time range. The first two entries can
174 -- be ignored since we will not have a range of less than 1 hour.
175 -- e.g. If the duration is between 60 and 90 mins, the fourth row
176 -- will be used, resulting in 12 ticks, 5 minutes apart, starting
177 -- at the nearest 5 minutes before the start time. i.e. if the
178 -- start time is 11:43, the graph will start at 11:40. This
179 -- downward rounding may cause an extra tick to be added but this
180 -- will be done in the procedure that calls this one
181
182
183 -- +----------+-------+-----------+----------+
184 -- | Duration | Ticks | Tick Dur | Start at |
185 -- | + + + nearest |
186 -- |----------+-------+-----------+----------|
187 -- | 15 mins + 8 + 2 mins + 1 min |
188 -- |----------+-------+-----------+----------|
189 -- | 30 mins + 10 + 3 mins + 1 min |
190 -- |----------+-------+-----------+----------|
191 -- | 60 mins + 12 + 5 mins + 5 min |
192 -- |----------+-------+-----------+----------|
193 -- | 90 mins + 10 + 10 mins + 10 min |
194 -- |----------+-------+-----------+----------|
195 -- | 2 hour + 12 + 10 mins + 10 min |
196 -- |----------+-------+-----------+----------|
197 -- | 3 hour + 12 + 15 mins + 15 min |
198 -- |----------+-------+-----------+----------|
199 -- | 6 hour + 12 + 30 mins + 30 min |
200 -- |----------+-------+-----------+----------|
201 -- | 10 hour + 10 + 60 mins + 1 hour |
202 -- |----------+-------+-----------+----------|
203 -- | 12 hour + 12 + 60 mins + 1 hour |
204 -- |----------+-------+-----------+----------|
205 -- | 18 hour + 12 + 90 mins + 1 hour |
206 -- |----------+-------+-----------+----------|
207 -- | 1 day + 12 + 2 hours + 1 hour |
208 -- |----------+-------+-----------+----------|
209 -- | 1.5 days + 10 + 3 hours + 1 hour |
210 -- |----------+-------+-----------+----------|
211 -- | 2 days + 12 + 4 hours + 1 hour |
212 -- |----------+-------+-----------+----------|
213 -- | 3 days + 12 + 6 hours + 1 hour |
214 -- |----------+-------+-----------+----------|
215 -- | 4 days + 12 + 8 hours + 1 hour |
216 -- |----------+-------+-----------+----------|
217 -- | 5 days + 12 + 10 hours + 1 hour |
218 -- |----------+-------+-----------+----------|
219 -- | 6 days + 12 + 12 hours + 1 hour |
220 -- |----------+-------+-----------+----------|
221 -- | 8 days + 8 + 1 day + 1 day |
222 -- |----------+-------+-----------+----------|
223 -- | 9 days + 9 + 1 day + 1 day |
224 -- |----------+-------+-----------+----------|
225 -- | 12 days + 12 + 1 days + 1 day |
226 -- |----------+-------+-----------+----------|
227 -- | 24 days + 12 + 2 days + 1 day |
228 -- |----------+-------+-----------+----------|
229 -- | 36 days + 12 + 3 days + 1 day |
230 -- |----------+-------+-----------+----------|
231 -- | 48 days + 12 + 4 days + 1 day |
232 -- |----------+-------+-----------+----------|
233 -- | 60 days + 12 + 5 days + 1 day |
234 -- |----------+-------+-----------+----------|
235 -- | 90 days + 10 + 9 days + 1 day |
236 -- |----------+-------+-----------+----------|
237 -- | 120 days + 10 + 10 days + 1 day |
238 -- |----------+-------+-----------+----------|
239 -- | 150 days + 10 + 15 days + 1 day |
240 -- |----------+-------+-----------+----------|
241 -- | 180 days + 10 + 18 days + 1 day |
242 -- +----------+-------+-----------+----------+
243
244 PROCEDURE get_date_params( p_start_date IN DATE,
245 p_end_date IN DATE,
246 x_graph_start_date OUT NOCOPY DATE,
247 x_graph_duration OUT NOCOPY NUMBER,
248 x_number_of_ticks OUT NOCOPY PLS_INTEGER,
249 x_tick_duration_days OUT NOCOPY NUMBER,
250 x_start_offset_value OUT NOCOPY VARCHAR2,
251 x_start_offset_unit OUT NOCOPY VARCHAR2,
252 x_output_date_format OUT NOCOPY VARCHAR2) IS
253
254
255 l_1_min CONSTANT NUMBER := 1/24/60; -- expressing 1 min in days
256 l_1_hour CONSTANT NUMBER := 1/24; -- expressing 1 hour in days
257
258 l_start_date DATE;
259 l_date_range NUMBER;
260
261 i PLS_INTEGER := 0;
262
263 invalid_date EXCEPTION;
264
265 BEGIN
266
267 -- The minimum to be displayed is 1 hour from the closing time. Set
268 -- the graph start date accordingly
269
270 IF p_end_date - p_start_date < l_1_hour
271 THEN
272 l_start_date := p_end_date - l_1_hour;
273 --- dbms_output.put_line('Got less than 1 hour...resetting new start date = ' || to_char(l_start_date, 'hh24:mi:ss'));
274 ELSE
275 l_start_date := p_start_date;
276 END IF;
277
278 x_graph_start_date := l_start_date;
279
280 l_date_range := p_end_date - l_start_date;
281
282 --- dbms_output.put_line('11.01: l_date_range_days = ' || l_date_range);
283 --- dbms_output.put_line('11.01: l_date_range_hours = ' || l_date_range*24);
284 --- dbms_output.put_line('11.01: l_date_range_mins = ' || l_date_range*24*60);
285
286 IF (l_date_range <= l_1_min*60) -- 60 mins
287 THEN
288 x_graph_duration := l_1_min*60;
289 x_number_of_ticks := 12; x_tick_duration_days := l_1_min*5;
290 x_start_offset_value := 5; x_start_offset_unit := 'MIN';
291 x_output_date_format := 'hh24:mi';
292
293 ELSIF (l_date_range <= l_1_min*90) -- 90 mins
294 THEN
295 x_graph_duration := l_1_min*90;
296 x_number_of_ticks := 10; x_tick_duration_days := l_1_min*10;
297 x_start_offset_value := 10; x_start_offset_unit := 'MIN';
298 x_output_date_format := 'hh24:mi';
299
300 ELSIF (l_date_range <= l_1_hour*2) -- 2 hours
301 THEN
302 x_graph_duration := l_1_hour*2;
303 x_number_of_ticks := 12; x_tick_duration_days := l_1_min*10;
304 x_start_offset_value := 10; x_start_offset_unit := 'MIN';
305 x_output_date_format := 'hh24:mi';
306
307 ELSIF (l_date_range <= l_1_hour*3) -- 3 hours
308 THEN
309 x_graph_duration := l_1_hour*3;
310 x_number_of_ticks := 12; x_tick_duration_days := l_1_min*15;
311 x_start_offset_value := 15; x_start_offset_unit := 'MIN';
312 x_output_date_format := 'hh24:mi';
313
314 ELSIF (l_date_range <= l_1_hour*6) -- 6 hours
315 THEN
316 x_graph_duration := l_1_hour*6;
317 x_number_of_ticks := 12; x_tick_duration_days := l_1_min*30;
318 x_start_offset_value := 30; x_start_offset_unit := 'MIN';
319 x_output_date_format := 'hh24:mi';
320
321 ELSIF (l_date_range <= l_1_hour*10) -- 10 hours
322 THEN
323 x_graph_duration := l_1_hour*10;
324 x_number_of_ticks := 10; x_tick_duration_days := l_1_min*60;
325 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
326 x_output_date_format := 'hh24:mi';
327
328 ELSIF (l_date_range <= l_1_hour*12) -- 12 hours
329 THEN
330 x_graph_duration := l_1_hour*12;
331 x_number_of_ticks := 12; x_tick_duration_days := l_1_min*60;
332 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
333 x_output_date_format := 'ddmon hh24:mi';
334
335 ELSIF (l_date_range <= l_1_hour*18) -- 18 hours
336 THEN
337 x_graph_duration := l_1_hour*18;
338 x_number_of_ticks := 12; x_tick_duration_days := l_1_min*90;
339 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
340 x_output_date_format := 'ddmon hh24:mi';
341
342 ELSIF (l_date_range <= 1) -- 1 days
343 THEN
344 x_graph_duration := 1;
345 x_number_of_ticks := 12; x_tick_duration_days := l_1_hour*2;
346 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
347 x_output_date_format := 'ddmon hh24:mi';
348
349 ELSIF (l_date_range <= 1.5) -- 1.5 days
350 THEN
351 x_graph_duration := 1.5;
352 x_number_of_ticks := 10; x_tick_duration_days := l_1_hour*3;
353 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
354 x_output_date_format := 'ddmon hh24:mi';
355
356 ELSIF (l_date_range <= 2) -- 2 days
357 THEN
358 x_graph_duration := 2;
359 x_number_of_ticks := 12; x_tick_duration_days := l_1_hour*4;
360 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
361 x_output_date_format := 'ddmon hh24:mi';
362
363 ELSIF (l_date_range <= 3) -- 3 days
364 THEN
365 x_graph_duration := 3;
366 x_number_of_ticks := 12; x_tick_duration_days := l_1_hour*6;
367 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
368 x_output_date_format := 'ddmon hh24:mi';
369
370 ELSIF (l_date_range <= 4) -- 4 days
371 THEN
372 x_graph_duration := 4;
373 x_number_of_ticks := 12; x_tick_duration_days := l_1_hour*8;
374 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
375 x_output_date_format := 'ddmon hh24:mi';
376
377 ELSIF (l_date_range <= 5) -- 5 days
378 THEN
379 x_graph_duration := 5;
380 x_number_of_ticks := 12; x_tick_duration_days := l_1_hour*10;
381 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
382 x_output_date_format := 'ddmon hh24:mi';
383
384 ELSIF (l_date_range <= 6) -- 6 days
385 THEN
386 x_graph_duration := 6;
387 x_number_of_ticks := 12; x_tick_duration_days := l_1_hour*12;
388 x_start_offset_value := 1; x_start_offset_unit := 'HOUR';
389 x_output_date_format := 'ddmon hh24:mi';
390
391 ELSIF (l_date_range <= 8) -- 8 days
392 THEN
393 x_graph_duration := 8;
394 x_number_of_ticks := 08; x_tick_duration_days := 1;
395 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
396 x_output_date_format := 'ddmon ';
397
398 ELSIF (l_date_range <= 9) -- 9 days
399 THEN
400 x_graph_duration := 9;
401 x_number_of_ticks := 09; x_tick_duration_days := 1;
402 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
403 x_output_date_format := 'ddmon ';
404
405 ELSIF (l_date_range <= 12) -- 12 days
406 THEN
407 x_graph_duration := 12;
408 x_number_of_ticks := 12; x_tick_duration_days := 1;
409 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
410 x_output_date_format := 'ddmon ';
411
412 ELSIF (l_date_range <= 24) -- 24 days
413 THEN
414 x_graph_duration := 24;
415 x_number_of_ticks := 12; x_tick_duration_days := 2;
416 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
417 x_output_date_format := 'ddmon ';
418
419 ELSIF (l_date_range <= 36) -- 36 days
420 THEN
421 x_graph_duration := 36;
422 x_number_of_ticks := 12; x_tick_duration_days := 3;
423 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
424 x_output_date_format := 'ddmon ';
425
426 ELSIF (l_date_range <= 48) -- 48 days
427 THEN
428 x_graph_duration := 48;
429 x_number_of_ticks := 12; x_tick_duration_days := 4;
430 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
431 x_output_date_format := 'ddmon ';
432
433 ELSIF (l_date_range <= 60) -- 60 days
434 THEN
435 x_graph_duration := 60;
436 x_number_of_ticks := 12; x_tick_duration_days := 5;
437 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
438 x_output_date_format := 'ddmon ';
439
440 ELSIF (l_date_range <= 90) -- 90 days
441 THEN
442 x_graph_duration := 90;
443 x_number_of_ticks := 10; x_tick_duration_days := 9;
444 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
445 x_output_date_format := 'ddmon ';
446
447 ELSIF (l_date_range <= 120) -- 120 days
448 THEN
449 x_graph_duration := 120;
450 x_number_of_ticks := 10; x_tick_duration_days := 10;
451 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
452 x_output_date_format := 'ddmon ';
453
454 ELSIF (l_date_range <= 150) -- 150 days
455 THEN
456 x_graph_duration := 150;
457 x_number_of_ticks := 10; x_tick_duration_days := 15;
458 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
459 x_output_date_format := 'ddmon ';
460
461 ELSIF (l_date_range <= 180) -- 180 days
462 THEN
463 x_graph_duration := 180;
464 x_number_of_ticks := 10; x_tick_duration_days := 18;
465 x_start_offset_value := 1; x_start_offset_unit := 'DAY';
466 x_output_date_format := 'ddmon ';
467 ELSE
468 RAISE invalid_date;
469 END IF; --}
470
471
472 EXCEPTION
473
474 WHEN invalid_date
475 THEN
476 raise;
477 --- dbms_output.put_line('Start date: ' || to_char(p_start_date, 'dd-Mon-yyyy hh24:mi'));
478 --- dbms_output.put_line('End date : ' || to_char(p_end_date, 'dd-Mon-yyyy hh24:mi'));
479 --- dbms_output.put_line('Actual duration: ' || to_char(p_end_date - p_start_date));
480 --- dbms_output.put_line('Exception - invalid date');
481 END get_date_params;
482
483
484 -----------------------------------------------------------------
485 ---- get_tick_values ----
486 -----------------------------------------------------------------
487 --
488 -- Start of Comments
489 --
490 -- API Name: get_date_params
491 --
492 -- Type : Private
493 --
494 -- Pre-reqs: None
495 --
496 -- Function: This API is called internally within this package and
497 -- is used to get calculate the dates with the parameters
498 -- from get_date_params
499 -- Most of the parameters are the out parameters of
500 -- get_date_params
501 --
502 -- Parameters:
503 --
504 -- p_start_date IN DATE
505 -- Required - start date for determining the range
506 --
507 -- p_end_date IN DATE
508 -- Required - end date of the range
509 --
510 -- p_graph_duration OUT NUMBER
511 -- This is the duration of the graph in days
512 --
513 -- p_number_of_ticks IN PLS_INTEGER
514 -- This is the number of ticks. The actual number of ticks
515 -- will be one more than what is given here
516 --
517 -- p_tick_duration_days IN NUMBER
518 -- This is the duration of each tick in days
519 --
520 -- p_start_offset_value IN VARCHAR2
521 -- Since in most cases, it would not be wise to start the
522 -- graph exactly on the start date, we round down to the
523 -- nearest time unit based on the scale of the graph. This
524 -- contains the value e.g 5 if the rounding is to the nearest
525 -- 5 mins
526 --
527 -- p_start_offset_unit IN VARCHAR2
528 -- This contains the unit of rounding as explained above
529 --
530 -- p_output_date_format OUT VARCHAR2
531 -- This contains the format in which the ticks are
532 -- labelled. Depending on the scale of the graph, only
533 -- the date, or the date+time or only the time is shown
534 --
535 -- x_graph_start_date OUT DATE
536 -- If the start date was less than an hour earlier than
537 -- the end date, then the new start date for the graph
538 -- will be an hour earlier than the end date
539 --
540 -----------------------------------------------------------------
541 --
542 PROCEDURE get_tick_values (
543 p_start_date IN DATE,
544 p_end_date IN DATE,
545 p_graph_duration IN NUMBER,
546 p_number_of_ticks IN PLS_INTEGER,
547 p_tick_duration_days IN NUMBER,
548 p_start_offset_value IN PLS_INTEGER,
549 p_start_offset_unit IN VARCHAR2,
550 p_output_date_format IN VARCHAR2,
551 x_graph_start_date OUT NOCOPY DATE,
552 x_graph_end_date OUT NOCOPY DATE,
553 x_graph_duration OUT NOCOPY NUMBER,
554 x_number_of_ticks OUT NOCOPY NUMBER,
555 x_tick_date_values OUT NOCOPY dateTblType
556 ) IS
557
558 l_graph_duration NUMBER;
559
560 l_start_format_num NUMBER;
561 l_graph_start_date DATE;
562 l_start_minutes PLS_INTEGER;
563
564 l_round_date_format VARCHAR2(20);
565 l_start_init_date_format VARCHAR2(20);
566 l_tick_server_date DATE;
567 l_tick_client_date DATE;
568
569 excp_invalid_timezone EXCEPTION;
570 excp_invalid_data EXCEPTION;
571
572 l_timezone_conversion_reqd VARCHAR2(1);
573
574 l_client_timezone_id NUMBER;
575 l_server_timezone_id NUMBER;
576
577 l_return_status VARCHAR2(1);
578 l_msg_count NUMBER;
579 l_msg_data VARCHAR2(250);
580
581 l_number_of_ticks PLS_INTEGER;
582
583
584 BEGIN
585
586 --- dbms_output.put_line('------------ Entering get_tick_values. Input parameters:');
587 --- dbms_output.put_line('p_start_date = ' || to_char(p_start_date, 'dd-mon-yyyy hh24:mi:ss'));
588 --- dbms_output.put_line('p_end_date = ' || to_char(p_end_date, 'dd-mon-yyyy hh24:mi:ss'));
589 --- dbms_output.put_line('p_number_of_ticks = ' || to_char(p_number_of_ticks));
590 --- dbms_output.put_line('p_tick_duration_days = ' || p_tick_duration_days);
591 --- dbms_output.put_line(' p_tick_duration_hours = ' || p_tick_duration_days*24);
592 --- dbms_output.put_line(' p_tick_duration_minutes = ' || p_tick_duration_days*24*60);
593 --- dbms_output.put_line('p_start_offset_value = '|| to_char(p_start_offset_value));
594 --- dbms_output.put_line('p_start_offset_unit = '||p_start_offset_unit );
595 --- dbms_output.put_line('p_output_date_format = '||p_output_date_format );
596 --- dbms_output.put_line('------------ End Input parameters');
597
598 -- Initialize output tick values. This is required to prevent a
599 -- no_data_found in the calling procedure
600
601 FOR i IN 1..g_max_no_ticks
602 LOOP
603 x_tick_date_values(i) := NULL;
604 END LOOP;
605
606 -- This code does the rounding of the start date to the nearest
607 -- specified unit for the range being displayed. For example, if the
608 -- duration is 2 days, then the start time is rounded off to the
609 -- nearest hour i.e. the graph starts at the nearest hour before the
610 -- start date. The offset_unit indicates the degree to which it should
611 -- be rounded - nearest hour, min, day, etc. The round_date_format is
612 -- used to extract the part of the date that will be rounded. For
613 -- example, if we decide to round to the nearest 5 mins, then the
614 -- round_date_format will be 'mi' and the the start_offset_value will
615 -- be 5.
616
617 -- Note that though the term "rounding" is used in the code, this is
618 -- not true rounding. We always round down. i.e. rounding 10:55 to the
619 -- nearest hour will be 10:00 and not 11:00
620
621 -- The start_init_date_format gives the format of the rest of the
622 -- string. In the above example, the minutes will be rounded off say
623 -- :43 becomes :40 and then the :40 is appended to the rest of the
624 -- date. Hence 02-Jun-04 22:43 becomes 02-Jun-04 22:40
625
626 IF p_start_offset_unit = 'MIN'
627 THEN
628 l_round_date_format := 'mi';
629 l_start_init_date_format := 'yyyy/mm/dd hh24:';
630 ELSIF p_start_offset_unit = 'HOUR'
631 THEN
632 l_round_date_format := 'hh24';
633 l_start_init_date_format := 'yyyy/mm/dd ';
634 ELSIF p_start_offset_unit = 'DAY'
635 THEN
636 l_round_date_format := 'dd';
637 l_start_init_date_format := 'yyyy/mm/dd';
638 ELSE
639 -- junk value, raise exception
640 RAISE excp_invalid_data;
641 END IF;
642
643 --- dbms_output.put_line('20.2: l_round_date_format = ' || l_round_date_format);
644 --- dbms_output.put_line('20.3: l_start_init_date_format = ' || l_start_init_date_format);
645
646 -- An offset value of 1 indicates that rounding has to be done to the
647 -- nearest multiple of the start_offset_value. In our example, this
648 -- would be the nearest 5 mins
649 -- Extract the rounding factor (:43 in our example), round it and then
650 -- append it to the rest of the string as described above
651
652 IF p_start_offset_value > 1
653 THEN
654 IF p_start_offset_unit = 'HOUR' OR
655 p_start_offset_unit = 'MIN'
656 THEN
657 l_start_format_num := to_number(to_char(p_start_date, l_round_date_format));
658 l_start_format_num := l_start_format_num - MOD(l_start_format_num, p_start_offset_value);
659 l_graph_start_date := to_date(to_char(p_start_date, l_start_init_date_format) ||
660 to_char(l_start_format_num), l_start_init_date_format || l_round_date_format);
661 ELSE
662 RAISE excp_invalid_data;
663 END IF;
664 ELSIF p_start_offset_unit = 'HOUR'
665 THEN
666 l_graph_start_date := to_date(to_char(p_start_date,'yyyy/mm/dd hh24'),'yyyy/mm/dd hh24');
667 ELSIF p_start_offset_unit = 'MIN'
668 THEN
669 l_graph_start_date := to_date(to_char(p_start_date,'yyyy/mm/dd hh24:mi'),'yyyy/mm/dd hh24:mi');
670 ELSIF p_start_offset_unit = 'DAY'
671 THEN
672 l_graph_start_date := TRUNC(p_start_date);
673 ELSE
674 RAISE excp_invalid_data;
675 END IF;
676
677 --- dbms_output.put_line('20.4: l_graph_start_date = ' || to_char(l_graph_start_date,'dd-mon-yyyy hh24:mi:ss'));
678
679 -- Once the start date is obtained, we have to see whether we fell
680 -- short of the duration. This can happen as in the following example:
681
682 -- The start date is 7-Jun 11:23. The end date is 9-Jun 11:15. The
683 -- duration of the graph in the get_date_params procedure will be
684 -- determined to be 2 days.
685
686 -- When we apply rounding, we will round the start date to the nearest
687 -- hour: 07-Jun 11:00. We will then have ticks for every 4 hours,
688 -- resulting in a total of 48 hours. This will bring us to 09-Jun
689 -- 11:00. If we stop the graph here, this will mean that all points
690 -- after this will be left out (which is where most of the action for
691 -- an auction will be - towards the end).
692
693 -- The solution for this will be to add an extra tick to the graph to
694 -- take it to 10-Jun 03:00. The graph now spans a little more than 2
695 -- days. This will be better than bumping the entire graph to the next
696 -- higher range of 3 days which will cause the points to get clustered
697 -- together
698
699 x_graph_start_date := l_graph_start_date;
700
701 -- Determine whether the end date of the graph with the duration is
702 -- still short. If yes, add an extra tick to the graph
703 -- The graph end date and duration can be determined after the last
704 -- tick is added
705
706 --- dbms_output.put_line('Graph start_date = ' || to_char(l_graph_start_Date,'dd-mon-yyyy hh24:mi:ss'));
707 --- dbms_output.put_line('Graph end_date = ' || to_char(p_end_date,'dd-mon-yyyy hh24:mi:ss'));
708 --- dbms_output.put_line('Graph duration = ' || p_graph_duration);
709 --- dbms_output.put_line('New end_date = ' || to_char(l_graph_start_date + p_graph_duration,'dd-mon-yyyy hh24:mi:ss'));
710 IF p_end_date > l_graph_start_date + p_graph_duration
711 THEN
712 l_number_of_ticks := p_number_of_ticks + 1;
713 ELSE
714 l_number_of_ticks := p_number_of_ticks;
715 END IF;
716
717 -- The dates have to be displayed in the timezone of the user. For
718 -- this, we first determine if timezones have been implemented
719 -- Per Kris Doherty, this can be determined by verifying if the
720 -- profiles for the client and server timezone are set. It should not
721 -- be determined by a call to fnd_timezones.get_timezone_conversion_reqd.
722 -- If the server and client timezones are the same, no conversion is
723 -- required
724
725 l_client_timezone_id := fnd_profile.value('CLIENT_TIMEZONE_ID');
726 l_server_timezone_id := fnd_profile.value('SERVER_TIMEZONE_ID');
727
728 IF l_client_timezone_id IS NULL OR
729 l_server_timezone_id IS NULL OR
730 l_client_timezone_id = l_server_timezone_id
731 THEN
732 l_timezone_conversion_reqd := 'N';
733 ELSE
734 l_timezone_conversion_reqd := 'Y';
735 END IF;
736
737 --- dbms_output.put_line('Client tiemzone : ' || l_client_timezone_id);
738 --- dbms_output.put_line('Server tiemzone : ' || l_server_timezone_id);
739
740 -- The l_number_of_ticks corresponds to the number of divisions of the
741 -- graph. However, we have to plot 1 more tick to include the start
742 -- and end ticks. Hence the addition of 1 in the loop below
743 -- For example, if there are two divisions Jan-Feb, Feb-Mar on the
744 -- graph, there are three ticks corresponding to Jan, Feb and Mar
745 -- respectively
746
747 --- dbms_output.put_line('number of ticks = '||l_number_of_ticks);
748
749 FOR i IN 1..l_number_of_ticks + 1
750 LOOP -- {
751
752 --- dbms_output.put_line('Entered for loop i = ' || i );
753 l_tick_server_date := l_graph_start_date + (i-1) * p_tick_duration_days;
754
755 --- dbms_output.put_line('Tick server date ' || i || ' : ' || to_char(l_tick_server_date,'dd-mon-yyyy hh24:mi:ss'));
756
757 -- Convert the date to the client timezone if timezones
758 -- are enabled. Otherwise, return as is
759
760 IF l_timezone_conversion_reqd = 'Y'
761 THEN
762 --- dbms_output.put_line('Timezone enabled - calling api');
763 --- dbms_output.put_line('Timezone l_server_timezone_id = ' ||l_server_timezone_id);
764 --- dbms_output.put_line('Timezone l_client_timezone_id = ' ||l_client_timezone_id);
765 --- dbms_output.put_line('Timezone l_server_date = ' ||to_char(l_tick_server_date,'dd-mon-yyyy hh24:mi:ss'));
766 hz_timezone_pub.get_time(
767 p_api_version => 1,
768 p_init_msg_list => 'F',
769 p_source_tz_id => l_server_timezone_id,
770 p_dest_tz_id => l_client_timezone_id,
771 p_source_day_time => l_tick_server_date,
772 x_dest_day_time => l_tick_client_date,
773 x_return_status => l_return_status,
774 x_msg_count => l_msg_count,
775 x_msg_data => l_msg_data);
776
777 --- dbms_output.put_line('After timezone api call');
778 --- dbms_output.put_line('Tick client date ' || i || ' : ' || to_char(l_tick_client_date,'dd-mon-yyyy hh24:mi:ss'));
779
780 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
781 THEN
782 -- Temporary code...have better handling later on
783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
784 END IF;
785
786 x_tick_date_values(i) := l_tick_client_date;
787
788 ELSE
789 x_tick_date_values(i) := l_tick_server_date;
790 END IF;
791
792 END LOOP; -- }
793
794 -- Now determine the end date in the server timezone. It should be the
795 -- last element added in the loop
796
797 x_graph_end_date := l_tick_server_date;
798 x_graph_duration := x_graph_end_date - x_graph_start_date;
799 x_number_of_ticks:= l_number_of_ticks;
800
801 --- dbms_output.put_line('Number of ticks: ' || x_number_of_ticks);
802 --- dbms_output.put_line('Graph end date: ' || to_char(x_graph_end_date, 'dd-mon-yyyy hh24:mi:ss'));
803 --- dbms_output.put_line('Graph duration: ' || to_char(x_Graph_duration));
804
805 EXCEPTION
806 WHEN excp_invalid_data
807 THEN
808 -- set appropriate out parameters; temporary code below
809 Raise;
810 WHEN excp_invalid_timezone
811 THEN
812 -- set appropriate out parameters; temporary code below
813 raise;
814 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
815 THEN
816 RAISE;
817 END get_tick_values;
818
819 -----------------------------------------------------------------
820 ---- get_time_axis_tick_labels ----
821 -----------------------------------------------------------------
822 ---- See specs for detailed procedure level comments
823
824 PROCEDURE get_time_axis_tick_labels(
825 p_auction_header_id IN NUMBER,
826 p_graph_start_date IN DATE,
827 p_auction_close_date IN DATE,
828 x_graph_start_date OUT NOCOPY DATE,
829 x_graph_end_date OUT NOCOPY DATE,
830 x_number_of_ticks OUT NOCOPY NUMBER,
831 x_multiplier OUT NOCOPY NUMBER,
832 x_tick_length OUT NOCOPY NUMBER,
833 x_tick_label_1 OUT NOCOPY VARCHAR2,
834 x_tick_label_2 OUT NOCOPY VARCHAR2,
835 x_tick_label_3 OUT NOCOPY VARCHAR2,
836 x_tick_label_4 OUT NOCOPY VARCHAR2,
837 x_tick_label_5 OUT NOCOPY VARCHAR2,
838 x_tick_label_6 OUT NOCOPY VARCHAR2,
839 x_tick_label_7 OUT NOCOPY VARCHAR2,
840 x_tick_label_8 OUT NOCOPY VARCHAR2,
841 x_tick_label_9 OUT NOCOPY VARCHAR2,
842 x_tick_label_10 OUT NOCOPY VARCHAR2,
843 x_tick_label_11 OUT NOCOPY VARCHAR2,
844 x_tick_label_12 OUT NOCOPY VARCHAR2,
845 x_tick_label_13 OUT NOCOPY VARCHAR2,
846 x_tick_label_14 OUT NOCOPY VARCHAR2) IS
847
848 l_graph_start_date DATE;
849 l_graph_new_start_date DATE;
850 l_number_of_ticks PLS_INTEGER;
851 l_start_offset_value PLS_INTEGER;
852 l_start_offset_unit VARCHAR2(20);
853 l_output_date_format VARCHAR2(20);
854 l_graph_duration NUMBER;
855 l_tick_duration_days NUMBER;
856 l_final_number_of_ticks PLS_INTEGER;
857 l_final_graph_duration NUMBER;
858
859 l_tick_date_values dateTblType;
860
861
862 BEGIN -- {
863
864 -- Initialize
865
866 x_number_of_ticks := 0;
867 x_tick_label_1 := NULL;
868 x_tick_label_2 := NULL;
869 x_tick_label_3 := NULL;
870 x_tick_label_4 := NULL;
871 x_tick_label_5 := NULL;
872 x_tick_label_6 := NULL;
873 x_tick_label_7 := NULL;
874 x_tick_label_8 := NULL;
875 x_tick_label_9 := NULL;
876 x_tick_label_10 := NULL;
877 x_tick_label_11 := NULL;
878 x_tick_label_12 := NULL;
879 x_tick_label_13 := NULL;
880 x_tick_label_14 := NULL;
881
882 -- If the front end already knows the start date of the graph and is
883 -- just trying to get the tick labels, then we do not need to hit the
884 -- negotiation tables. This will happen when the user switches context
885 -- from one line to another. The front end does not store the ticks
886 -- for each line but will store the graph start date. This procedure
887 -- will be called to get the ticks
888
889 l_graph_start_date := p_graph_start_date;
890
891 --Commenting If condition as it is no longer required.
892 -- We may need this when we plan to implement zoom in live console
893 /*IF l_graph_start_date IS NULL -- {
894 THEN */
895
896 -- The procedure could be called to determine ticks for the entire
897 -- auction or for a particular line
898
899 -- If the context is header, check to see if any bids for the auctions
900 -- As part of the check, get the earliest publish date. This will be
901 -- the leftmost point of the graph. Only active/archived bids are
902 -- shown; disqualified bids are omitted from the graph
903
904
905 SELECT MIN(bh.publish_date)
906 INTO l_graph_start_date
907 FROM pon_bid_headers bh
908 WHERE bh.auction_header_id = p_auction_header_id
909 AND bh.bid_status IN ('ACTIVE', 'ARCHIVED');
910
911 -- If no bids were available for the context, then let the caller know
912 -- to not display the graph
913
914 IF l_graph_start_date IS NULL -- {
915 THEN
916 x_number_of_ticks := 0; -- this indicates no data to the caller
917 RETURN;
918 ELSIF ((p_auction_close_date-l_graph_start_date) > 180)
919 THEN
920 l_graph_start_date := p_auction_close_date-180;
921 END IF; -- }
922
923 --END IF; -- }
924
925
926 -- Now that the start and end dates are known, determine each tick and
927 -- other parameters
928
929 get_date_params( p_start_date => l_graph_start_date,
930 p_end_date => p_auction_close_date,
931 x_graph_start_date => l_graph_new_start_date,
932 x_graph_duration => l_graph_duration,
933 x_number_of_ticks => l_number_of_ticks,
934 x_tick_duration_days => l_tick_duration_days,
935 x_start_offset_value => l_start_offset_value,
936 x_start_offset_unit => l_start_offset_unit,
937 x_output_date_format => l_output_date_format
938 );
939
940 get_tick_values (
941 p_start_date => l_graph_new_start_date,
942 p_end_date => p_auction_close_date,
943 p_graph_duration => l_graph_duration,
944 p_number_of_ticks => l_number_of_ticks,
945 p_tick_duration_days => l_tick_duration_days,
946 p_start_offset_value => l_start_offset_value,
947 p_start_offset_unit => l_start_offset_unit,
948 p_output_date_format => l_output_date_format,
949 x_graph_start_date => x_graph_start_date,
950 x_graph_end_date => x_graph_end_date,
951 x_graph_duration => l_final_graph_duration,
952 x_number_of_ticks => l_final_number_of_ticks,
953 x_tick_date_values => l_tick_date_values
954 );
955
956 -- Return the scalar equivalents of l_tick_values. No need to check
957 -- for no_data_found as get_tick_values() will ensure that all values
958 -- are initialized
959
960 x_tick_label_1 := to_char(l_tick_date_values(1), l_output_date_format);
961 x_tick_label_2 := to_char(l_tick_date_values(2), l_output_date_format);
962 x_tick_label_3 := to_char(l_tick_date_values(3), l_output_date_format);
963 x_tick_label_4 := to_char(l_tick_date_values(4), l_output_date_format);
964 x_tick_label_5 := to_char(l_tick_date_values(5), l_output_date_format);
965 x_tick_label_6 := to_char(l_tick_date_values(6), l_output_date_format);
966 x_tick_label_7 := to_char(l_tick_date_values(7), l_output_date_format);
967 x_tick_label_8 := to_char(l_tick_date_values(8), l_output_date_format);
968 x_tick_label_9 := to_char(l_tick_date_values(9), l_output_date_format);
969 x_tick_label_10 := to_char(l_tick_date_values(10), l_output_date_format);
970 x_tick_label_11 := to_char(l_tick_date_values(11), l_output_date_format);
971 x_tick_label_12 := to_char(l_tick_date_values(12), l_output_date_format);
972 x_tick_label_13 := to_char(l_tick_date_values(13), l_output_date_format);
973 x_tick_label_14 := to_char(l_tick_date_values(14), l_output_date_format);
974
975 -- The last tick is held in l_tick_server_date. Hence the duration for
976 -- the graph is from the start date until the last tick. This
977 -- corresponds to 1440 in numeric terms since the console plots all
978 -- date/time points as numbers. The total length of the x-axis between
979 -- all ticks is 1440. Hence, given a date to be plotted, the console
980 -- will determine its numeric value by:
981
982 -- Total graph range = 1440
983 -- Hence current date is: (Current date - graph start date)/total_graph_range * 1440
984
985 -- Current date - graph start date is determined by the console.
986 -- Current date will be the publish date of each bid being plotted
987
988 --- dbms_output.put_line('20.25: l_graph_duration = ' || l_graph_duration);
989 --- dbms_output.put_line('20.26: g_graph_range = ' || g_graph_range);
990
991 x_multiplier := g_graph_range/l_final_graph_duration;
992 x_tick_length := g_graph_range/l_final_number_of_ticks;
993 x_number_of_ticks:= l_final_number_of_ticks;
994
995 --- dbms_output.put_line('20.30: x_multiplier = ' || x_multiplier);
996 --- dbms_output.put_line('20.40: x_tick_length = ' || x_tick_length);
997 --- dbms_output.put_line('20.50: x_graph_end_date = ' || to_char(x_graph_end_date,'dd-mon-yyyy hh24:mi:ss'));
998
999
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND
1002 THEN
1003 x_number_of_ticks := 0;
1004 RETURN;
1005
1006 WHEN OTHERS
1007 THEN
1008 RAISE;
1009
1010 END get_time_axis_tick_labels;
1011
1012
1013 -----------------------------------------------------------------
1014 ---- check_estimated_qty_available ----
1015 -----------------------------------------------------------------
1016 ---- See specs for detailed procedure level comments
1017
1018 PROCEDURE check_estimated_qty_available(
1019 p_auction_header_id IN NUMBER,
1020 p_auction_line_number IN NUMBER,
1021 x_est_qty_available_flag OUT NOCOPY VARCHAR2) IS
1022
1023 BEGIN
1024
1025 x_est_qty_available_flag := 'Y';
1026
1027 -- If p_auction_line_number is -1, this indicates that the procedure is being
1028 -- called at the header level. Check if any lines do not have
1029 -- quantity. If a line number is provided, then query for the specific
1030 -- line number
1031
1032 IF NVL(p_auction_line_number, -1) = -1
1033 THEN
1034 SELECT 'N'
1035 INTO x_est_qty_available_flag
1036 FROM dual
1037 WHERE EXISTS (SELECT 1
1038 FROM pon_auction_item_prices_all al
1039 WHERE al.auction_header_id = p_auction_header_id
1040 AND al.group_type NOT IN ('GROUP','LOT_LINE')
1041 AND al.order_type_lookup_code <> 'FIXED PRICE'
1042 AND NVL(quantity, 0) = 0);
1043 ELSE
1044 SELECT 'N'
1045 INTO x_est_qty_available_flag
1046 FROM dual
1047 WHERE EXISTS (SELECT 1
1048 FROM pon_auction_item_prices_all al
1049 WHERE al.auction_header_id = p_auction_header_id
1050 AND al.line_number = p_auction_line_number
1051 AND al.group_type NOT IN ('GROUP','LOT_LINE')
1052 AND al.order_type_lookup_code <> 'FIXED PRICE'
1053 AND NVL(quantity, 0) = 0);
1054 END IF;
1055
1056 EXCEPTION
1057
1058 WHEN NO_DATA_FOUND
1059 THEN
1060 x_est_qty_available_flag := 'Y';
1061
1062 WHEN OTHERS THEN
1063 RAISE;
1064
1065 END check_estimated_qty_available;
1066
1067
1068 -----------------------------------------------------------------
1069 ---- upgrade_bid_colors ----
1070 -----------------------------------------------------------------
1071 ---- See specs for detailed procedure level comments
1072
1073 PROCEDURE upgrade_bid_colors (p_auction_header_id IN NUMBER) IS
1074 PRAGMA AUTONOMOUS_TRANSACTION;
1075
1076 l_color_sequence_id PLS_INTEGER;
1077
1078 TYPE rowidTblType IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1079
1080 l_rowid_tbl rowidTblType;
1081
1082 -- Determine the latest bid for a combination of a supplier, supplier
1083 -- site and contact. After identifying the latest bid, mark all the
1084 -- bids from the same supplier/site/contact combination with the same
1085 -- color so that they are shown as being related on the graph
1086 -- Bids that don't have a site will have a site id of -1 hence no need
1087 -- for an NVL
1088
1089 CURSOR c1 IS
1090 SELECT bh.trading_partner_id,
1091 bh.trading_partner_contact_id,
1092 bh.vendor_site_id,
1093 MAX(bid_number)
1094 FROM pon_bid_headers bh
1095 WHERE bh.auction_header_id = p_auction_header_id
1096 GROUP BY
1097 bh.trading_partner_id,
1098 bh.trading_partner_contact_id,
1099 bh.vendor_site_id;
1100
1101 BEGIN -- {
1102
1103 -- Colors may have been partially assigned to some bids. This happens
1104 -- if bidding for the negotiation had started before the upgrade for FPK.
1105 -- However, we will ignore the assigned colors and reassign them for
1106 -- simplicity - and because the possibility of this happening is rare,
1107 -- since it is extremely unlikely that a company will perform its
1108 -- upgrade in the middle of a negotiation
1109
1110 -- Only suppliers with some active bids are visible in the live
1111 -- console. In the normal course of events, colors are assigned to the
1112 -- first bid and are then carried forward to every subsequent bid (by
1113 -- the same user, same supplier, same site combination; the old bid
1114 -- becomes archived) i.e. all the bids in a "chain" will have the same
1115 -- color. For our strategy, we will start with the latest bid,
1116 -- assigning them a color in sequence. Since there is no column to
1117 -- indicate the source of a rebid (unless it is from a previous
1118 -- round), we will update all bids for the auction that have the same
1119 -- supplier, site and contact since this combination has to be unique
1120 -- for an active bid
1121
1122 l_color_sequence_id := -1;
1123
1124 FOR c IN c1
1125 LOOP -- {
1126
1127 l_color_sequence_id := l_color_sequence_id + 1;
1128
1129 -- Update all related bids with the same color
1130 -- site_id is -1 if it is not there - hence no nvl
1131 -- last_update_login field is missing in the table. Add the update to
1132 -- that column after the column is introduced
1133
1134 UPDATE pon_bid_headers bh
1135 SET bh.color_sequence_id = l_color_sequence_id,
1136 bh.last_update_date = sysdate,
1137 bh.last_updated_by = fnd_global.user_id
1138 WHERE bh.trading_partner_id = c.trading_partner_id
1139 AND bh.trading_partner_contact_id = c.trading_partner_contact_id
1140 AND bh.vendor_site_id = c.vendor_site_id
1141 AND bh.auction_header_id = p_auction_header_id;
1142
1143 END LOOP; -- }
1144
1145 -- Update the count on the negotiation to the last value
1146 -- last_update_login field is missing in the table. Add the update to
1147 -- that column after the column is introduced
1148
1149 UPDATE pon_auction_headers_all ah
1150 SET ah.max_bid_color_sequence_id = l_color_sequence_id,
1151 ah.last_update_date = sysdate,
1152 ah.last_updated_by = fnd_global.user_id
1153 WHERE ah.auction_header_id = p_auction_header_id;
1154
1155 -- Commit because this is an autonomous transaction
1156
1157 COMMIT;
1158
1159 EXCEPTION
1160 WHEN OTHERS
1161 THEN
1162 RAISE;
1163 END upgrade_bid_colors; -- }
1164
1165
1166 -----------------------------------------------------------------
1167 ---- record_supplier_activity ----
1168 -----------------------------------------------------------------
1169
1170 PROCEDURE record_supplier_activity(
1171 p_auction_header_id IN NUMBER,
1172 p_auction_header_id_orig_amend IN NUMBER,
1173 p_trading_partner_id IN NUMBER,
1174 p_trading_partner_contact_id IN NUMBER,
1175 p_session_id IN NUMBER,
1176 p_last_activity_code IN VARCHAR2,
1177 p_bid_number IN NUMBER,
1178 x_record_status OUT NOCOPY VARCHAR2) IS
1179
1180 PRAGMA AUTONOMOUS_TRANSACTION; -- {
1181
1182 l_api_name CONSTANT VARCHAR2(25) := 'record_supplier_activity';
1183 l_progress VARCHAR2 (3);
1184
1185 l_sysdate DATE;
1186 l_user_id NUMBER;
1187 l_login_id NUMBER;
1188
1189 l_last_activity_code pon_supplier_activities.last_activity_code%TYPE;
1190 l_rowid ROWID;
1191 l_insert_flag VARCHAR2(1);
1192
1193 CURSOR c_supplier_activity IS
1194 SELECT last_activity_code
1195 , rowid
1196 FROM pon_supplier_activities
1197 WHERE auction_header_id_orig_amend = p_auction_header_id_orig_amend
1198 AND trading_partner_id = p_trading_partner_id
1199 AND trading_partner_contact_id = p_trading_partner_contact_id
1200 AND last_action_flag = 'Y';
1201
1202 l_debug_statement BOOLEAN;
1203 l_debug_unexpected BOOLEAN;
1204
1205
1206 BEGIN
1207
1208 -- initialize the out parameter
1209 x_record_status := FND_API.G_TRUE;
1210
1211 -- Initialize other variables
1212 l_sysdate := SYSDATE;
1213 l_user_id := fnd_global.user_id;
1214 l_login_id := fnd_global.login_id;
1215 l_insert_flag := 'N';
1216
1217 l_debug_statement := is_debug_statement_on;
1218 l_debug_unexpected := is_debug_unexpected_on;
1219
1220 l_progress := '000';
1221
1222 OPEN c_supplier_activity;
1223 FETCH c_supplier_activity INTO l_last_activity_code, l_rowid;
1224 IF c_supplier_activity%FOUND THEN
1225 l_progress := '001';
1226
1227 -- check activity codes are same and update the record.
1228 IF l_last_activity_code = p_last_activity_code THEN
1229 l_progress := '002';
1230 -- activities are same just update the activity time
1231 UPDATE pon_supplier_activities
1232 SET last_activity_time = l_sysdate
1233 , last_update_date = l_sysdate
1234 , last_updated_by = l_user_id
1235 , last_update_login = l_login_id
1236 WHERE rowid = l_rowid;
1237 ELSE
1238 l_progress := '003';
1239 -- activities are different.
1240 -- disable previous record and set insert flag
1241 UPDATE pon_supplier_activities
1242 SET last_action_flag = 'N'
1243 , last_update_date = l_sysdate
1244 , last_updated_by = l_user_id
1245 , last_update_login = l_login_id
1246 WHERE rowid = l_rowid;
1247
1248 l_insert_flag := 'Y';
1249 END IF;
1250 ELSE
1251
1252 l_progress := '004';
1253 -- no records found, set insert flag
1254 l_insert_flag := 'Y';
1255 END IF;
1256 CLOSE c_supplier_activity;
1257
1258 l_progress := '005';
1259
1260 IF (l_insert_flag = 'Y') THEN
1261
1262 l_progress := '006';
1263 INSERT INTO pon_supplier_activities
1264 ( auction_header_id
1265 , auction_header_id_orig_amend
1266 , trading_partner_id
1267 , trading_partner_contact_id
1268 , session_id
1269 , last_activity_code
1270 , last_activity_time
1271 , last_action_flag
1272 , creation_date
1273 , created_by
1274 , last_update_date
1275 , last_updated_by
1276 , last_update_login
1277 , bid_number)
1278 VALUES ( p_auction_header_id
1279 , p_auction_header_id_orig_amend
1280 , p_trading_partner_id
1281 , p_trading_partner_contact_id
1282 , p_session_id
1283 , p_last_activity_code
1284 , l_sysdate
1285 , 'Y'
1286 , l_sysdate
1287 , l_user_id
1288 , l_sysdate
1289 , l_user_id
1290 , l_login_id
1291 , p_bid_number);
1292 END IF;
1293
1294 -- commit the autonomous transaction
1295 COMMIT;
1296
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299 x_record_status := FND_API.G_FALSE;
1300 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.string(log_level => FND_LOG.level_unexpected
1302 ,module => g_pkg_name||'.'||l_api_name
1303 ,message => 'unexpected error '||l_progress
1304 );
1305 END IF;
1306 ROLLBACK;
1307 RETURN;
1308 END record_supplier_activity; --}
1309
1310 -----------------------------------------------------------------
1311 ---- update_supplier_access ----
1312 -----------------------------------------------------------------
1313 PROCEDURE update_supplier_access(p_auction_header_id IN NUMBER
1314 , p_auction_header_id_orig_amend IN NUMBER
1315 , p_supplier_trading_partner_id IN NUMBER
1316 , p_buyer_tp_contact_id IN NUMBER
1317 , p_lock_status IN VARCHAR2
1318 , p_lock_reason IN VARCHAR2
1319 , x_record_status OUT NOCOPY VARCHAR2)
1320 IS
1321 PRAGMA AUTONOMOUS_TRANSACTION; -- {
1322
1323 l_api_name CONSTANT VARCHAR2(25) := 'update_supplier_access';
1324 l_progress VARCHAR2 (3);
1325
1326 l_sysdate DATE;
1327 l_user_id NUMBER;
1328 l_login_id NUMBER;
1329
1330 l_debug_statement BOOLEAN;
1331 l_debug_unexpected BOOLEAN;
1332
1333 BEGIN
1334
1335 -- initialize the out parameter
1336 x_record_status := FND_API.G_TRUE;
1337
1338 -- Initialize other variables
1339 l_sysdate := SYSDATE;
1340 l_user_id := fnd_global.user_id;
1341 l_login_id := fnd_global.login_id;
1342
1343 l_debug_statement := is_debug_statement_on;
1344 l_debug_unexpected := is_debug_unexpected_on;
1345
1346 l_progress := '000';
1347
1348 -- update the record with the orig header id /trading partner id
1349 UPDATE pon_supplier_access
1350 SET active_flag = 'N'
1351 , last_update_date = l_sysdate
1352 , last_updated_by = l_user_id
1353 , last_update_login = l_login_id
1354 WHERE auction_header_id_orig_amend = p_auction_header_id_orig_amend
1355 AND supplier_trading_partner_id = p_supplier_trading_partner_id;
1356
1357 -- if not found error do nothing
1358 IF SQL%NOTFOUND THEN
1359 NULL;
1360 END IF;
1361
1362 l_progress := '001';
1363
1364 -- now insert the new record
1365 INSERT INTO pon_supplier_access(auction_header_id_orig_amend
1366 ,supplier_trading_partner_id
1367 ,lock_date
1368 ,buyer_tp_contact_id
1369 ,lock_status
1370 ,active_flag
1371 ,auction_header_id
1372 ,lock_reason
1373 ,creation_date
1374 ,created_by
1375 ,last_update_date
1376 ,last_updated_by
1377 ,last_update_login
1378 )
1379 VALUES (p_auction_header_id_orig_amend
1380 ,p_supplier_trading_partner_id
1381 ,l_sysdate
1382 ,p_buyer_tp_contact_id
1383 ,p_lock_status
1384 ,'Y'
1385 ,p_auction_header_id
1386 ,p_lock_reason
1387 ,l_sysdate
1388 ,l_user_id
1389 ,l_sysdate
1390 ,l_user_id
1391 ,l_login_id);
1392
1393 l_progress := '002';
1394 -- commit the autonomous transaction
1395 COMMIT;
1396
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 --DBMS_OUTPUT.PUT_LINE('error' ||SQLERRM ||' '||SQLCODE);
1400 x_record_status := FND_API.G_FALSE;
1401 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1402 FND_LOG.string(log_level => FND_LOG.level_unexpected
1403 ,module => g_pkg_name||'.'||l_api_name
1404 ,message => 'unexpected error '||l_progress
1405 );
1406 END IF;
1407 RETURN;
1408 END update_supplier_access; --}
1409
1410 -----------------------------------------------------------------
1411 ---- calculate_console_summary ----
1412 -----------------------------------------------------------------
1413 --
1414 -- Start of Comments
1415 --
1416 -- API Name: calculate_console_summary
1417 --
1418 -- Type : public
1419 --
1420 -- Pre-reqs: None
1421 --
1422 -- Function: This API is called from ConsoleAMImpl.java to calculate
1423 -- Auction Value, Current Value, Optimal Value(based on Auto
1424 -- Award Recommendation), no bid value and num of lines without bids
1425 --
1426 -- Parameters:
1427 --
1428 -- P_AUCTION_ID IN NUMBER
1429 -- Required - Auction_header_id of the negotiation
1430 --
1431 -- x_auction_value OUT NUMBER
1432 -- Total value of the negotiation, calculated based on
1433 -- line qty and current price
1434 -- x_current_value OUT NUMBER
1435 -- Total current value of the negotiation, calculated based on
1436 -- awarded qty and current price
1437 -- x_optimal_value OUT NUMBER
1438 -- Total Value of the negotiation, calculated based on
1439 -- awarded qty and bid price
1440 -- x_no_bid_value OUT NUMBER
1441 -- Total value of the lines that didn't receive bids, calculated
1442 -- based on line qty and current price
1443 -- x_no_bid_lines OUT NUMBER
1444 -- Number of lines without bids
1445 -----------------------------------------------------------------
1446
1447 PROCEDURE calculate_console_summary( p_auction_id IN NUMBER,
1448 x_auction_value OUT NOCOPY NUMBER,
1449 x_current_value OUT NOCOPY NUMBER,
1450 x_optimal_value OUT NOCOPY NUMBER,
1451 x_no_bid_value OUT NOCOPY NUMBER,
1452 x_no_bid_lines OUT NOCOPY NUMBER
1453 )
1454 IS
1455
1456 TYPE line_number_tbl_type IS TABLE OF pon_bid_item_prices.line_number%TYPE;
1457 TYPE bid_price_tbl_type IS TABLE OF pon_bid_item_prices.price%TYPE;
1458 TYPE bid_qty_tbl_type IS TABLE OF pon_bid_item_prices.quantity%TYPE;
1459 TYPE current_price_tbl_type IS TABLE OF pon_auction_item_prices_all.current_price%TYPE;
1460 TYPE auction_qty_tbl_type IS TABLE OF pon_auction_item_prices_all.quantity%TYPE;
1461
1462 l_line_number_tbl line_number_tbl_type;
1463 l_bid_price_tbl bid_price_tbl_type;
1464 l_bid_qty_tbl bid_qty_tbl_type;
1465 l_current_price_tbl current_price_tbl_type;
1466 l_auction_qty_tbl auction_qty_tbl_type;
1467 l_line_number PON_BID_ITEM_PRICES.line_number%TYPE;
1468 l_qty_remaining NUMBER;
1469 l_current_value NUMBER := 0;
1470 l_optimal_value NUMBER := 0;
1471 l_no_bid_lines NUMBER := 0;
1472 l_no_bid_value NUMBER := 0;
1473 l_auction_value NUMBER := 0;
1474 l_prev_line_number NUMBER;
1475
1476 BEGIN
1477 -- To calculate auction value
1478 BEGIN
1479 SELECT SUM(nvl(current_price, 0)
1480 * decode (order_type_lookup_code, 'FIXED PRICE', 1, quantity)) AUCTION_VALUE,
1481 SUM(decode(nvl(number_of_bids,0),0,1,0)) NO_BID_LINES,
1482 SUM(decode(nvl(number_of_bids,0),0,
1483 (nvl(current_price, 0) * decode(order_type_lookup_code, 'FIXED PRICE', 1, quantity)),0)) NO_BID_VALUE
1484 INTO l_auction_value,
1485 l_no_bid_lines,
1486 l_no_bid_value
1487 FROM PON_AUCTION_ITEM_PRICES_ALL
1488 WHERE group_type in ('LOT', 'LINE', 'GROUP_LINE')
1489 AND auction_header_id = P_AUCTION_ID;
1490 EXCEPTION
1491 WHEN NO_DATA_FOUND THEN
1492 l_auction_value := 0;
1493 l_no_bid_value := 0;
1494 l_no_bid_lines := 0;
1495 WHEN OTHERS THEN
1496 RAISE;
1497 END;
1498 x_auction_value := l_auction_value;
1499 x_no_bid_value := l_no_bid_value;
1500 x_no_bid_lines := l_no_bid_lines;
1501
1502 -- To calculate current value and optimal value.
1503 -- Used in potential savings calculation in ConsoleAMImpl.java
1504 -- bid_quantity: For Blankets bid quantity will be null, so defaulting it to auction qty
1505 -- Auction qty : For Fixed price lines auction qty will be null, so defaulting it to 1
1506 BEGIN
1507 SELECT
1508 bl.line_number,
1509 bl.price bid_price,
1510 nvl(bl.quantity,nvl(ai.quantity,1)) bid_quantity,
1511 ai.current_price,
1512 nvl(ai.quantity,1) auction_qty
1513 BULK COLLECT INTO l_line_number_tbl, l_bid_price_tbl, l_bid_qty_tbl, l_current_price_tbl, l_auction_qty_tbl
1514 FROM pon_bid_headers bh,
1515 pon_bid_item_prices bl,
1516 pon_auction_headers_all ah,
1517 pon_auction_item_prices_all ai
1518 WHERE ah.auction_header_id = bh.auction_header_id
1519 and bh.auction_header_id = bl.auction_header_id
1520 and bh.bid_number = bl.bid_number
1521 and bh.bid_status = 'ACTIVE'
1522 and bh.auction_header_id = P_AUCTION_ID
1523 and nvl(bh.SHORTLIST_FLAG, 'Y') = 'Y'
1524 and ai.auction_header_id = ah.auction_header_id
1525 and ai.line_number = bl.line_number
1526 AND ai.group_type IN ('LOT', 'LINE', 'GROUP_LINE')
1527 ORDER BY bl.line_number, decode(ah.bid_ranking, 'PRICE_ONLY', 1/bl.price, nvl(bl.total_weighted_score,0)/bl.price) desc ,bl.publish_date asc;
1528
1529 l_prev_line_number := -9999;
1530 IF l_line_number_tbl.COUNT > 0 THEN
1531 FOR j IN l_line_number_tbl.FIRST .. l_line_number_tbl.LAST
1532 LOOP
1533 IF l_prev_line_number <> l_line_number_tbl(j) THEN
1534 l_prev_line_number := l_line_number_tbl(j);
1535 l_qty_remaining := l_auction_qty_tbl(j);
1536 END IF;
1537 IF (l_qty_remaining > 0) THEN
1538 IF (l_bid_qty_tbl(j) <= l_qty_remaining) THEN
1539 l_current_value := l_current_value + (l_bid_qty_tbl(j) * nvl(l_current_price_tbl(j), l_bid_price_tbl(j)));
1540 l_optimal_value := l_optimal_value + (l_bid_qty_tbl(j) * l_bid_price_tbl(j));
1541 l_qty_remaining := l_qty_remaining - l_bid_qty_tbl(j);
1542 ELSE
1543 l_current_value := l_current_value + (l_qty_remaining * nvl(l_current_price_tbl(j), l_bid_price_tbl(j)));
1544 l_optimal_value := l_optimal_value + (l_qty_remaining * l_bid_price_tbl(j));
1545 l_qty_remaining := 0;
1546 END IF;
1547 END IF; --End of if l_qty_remaining > 0
1548 END LOOP; -- End of for loop
1549 END IF;
1550 EXCEPTION
1551 WHEN NO_DATA_FOUND THEN
1552 l_current_value := 0;
1553 l_optimal_value := 0;
1554 WHEN OTHERS THEN
1555 RAISE;
1556 END;
1557
1558 x_current_value := nvl(l_current_value,0);
1559 x_optimal_value := nvl(l_optimal_value,0);
1560
1561 END calculate_console_summary;
1562
1563 END PON_CONSOLE_PVT;
[Home] [Help]