[Home] [Help]
PACKAGE BODY: APPS.CS_SR_COST_CP
Source
1 PACKAGE BODY CS_SR_COST_CP AS
2 /* $Header: csvcstpgb.pls 120.4.12010000.3 2008/09/25 23:03:38 bkanimoz ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'CS_SR_COST_CP';
4
5 PROCEDURE Write_cost_Output
6 (
7 p_cost_batch_size IN NUMBER
8 , p_request_id IN NUMBER
9 , p_worker_id IN NUMBER := NULL
10 );
11
12 PROCEDURE Create_Cost
13 (
14 errbuf OUT NOCOPY VARCHAR2
15 , errcode OUT NOCOPY NUMBER
16 , p_api_version_number IN NUMBER
17 , p_init_msg_list IN VARCHAR2
18 , p_commit IN VARCHAR2
19 , p_validation_level IN NUMBER
20 , p_creation_from_date IN VARCHAR2
21 , p_creation_to_date IN VARCHAR2
22 , p_sr_status IN VARCHAR2
23 , p_number_of_workers IN NUMBER --Number of Worker Threads to be started
24 , p_cost_batch_size IN NUMBER --Number of Service Requests to be processed in a batch
25 )
26 IS
27 --------------------------------------------------------------------------------
28
29 L_API_VERSION CONSTANT NUMBER := 1.0;
30 L_API_NAME CONSTANT VARCHAR2(60) := 'CREATE_COST_FOR_SERVICEREQUESTS';
31 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
32 L_LOG_MODULE CONSTANT VARCHAR2(255):= 'csvcstpgb.pls' || L_API_NAME_FULL || '.';
33
34 TYPE t_worker_conc_req_arr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
35
36 L_EXC_COST_WARNING EXCEPTION;
37
38 x_msg_index_out NUMBER;
39 x_msg_count NUMBER;
40 x_msg_data VARCHAR2(1000);
41 x_return_status VARCHAR2(1);
42
43 -- Request id of the current
44 -- concurrent request.
45
46 l_request_id NUMBER;
47
48 -- Request data used to identify if the concurrent
49 -- request is started for the first time or if it
50 -- is resumed from a PAUSED state.
51
52 l_request_data VARCHAR2(1);
53
54 -- variables defined for holding the validated
55 -- value of the dates that are received as
56 -- VARCHARs from the concurrent program UI
57
58 l_creation_from_date DATE;
59 l_creation_to_date DATE;
60
61
62 l_row_count NUMBER;
63 l_ret BOOLEAN;
64
65 -- Actual number of worker concurrent requests
66 -- to be started based on the number of SRs in
67 -- the costset.
68
69 l_number_of_workers NUMBER := p_number_of_workers;
70
71 -- Table of request ids of the worker concurrent request
72
73 l_worker_conc_req_arr t_worker_conc_req_arr;
74
75 -- Variables holding the status information of each
76 -- worker concurrent request
77
78 l_worker_conc_req_phase VARCHAR2(100);
79 l_worker_conc_req_status VARCHAR2(100);
80 l_worker_conc_req_dev_phase VARCHAR2(100);
81 l_worker_conc_req_dev_status VARCHAR2(100);
82 l_worker_conc_req_message VARCHAR2(512);
83
84 -- Variables holding the status information of
85 -- the parent concurrent request
86
87 l_main_conc_req_phase VARCHAR2(100);
88 l_main_conc_req_status VARCHAR2(100);
89 l_main_conc_req_dev_phase VARCHAR2(100);
90 l_main_conc_req_dev_status VARCHAR2(100);
91 l_main_conc_req_message VARCHAR2(512);
92 l_child_message VARCHAR2(4000);
93
94 CURSOR c_child_request
95 (
96 c_request_id NUMBER
97 )
98 IS
99 SELECT
100 request_id
101 FROM
102 fnd_concurrent_requests
103 WHERE
104 parent_request_id = c_request_id;
105
106
107
108 BEGIN
109
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 --logging the input parameters
113
114 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
115 THEN
116 FND_LOG.String
117 (
118 FND_LOG.level_procedure
119 , L_LOG_MODULE || 'start_time'
120 , 'The start time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
121 );
122 FND_LOG.String
123 (
124 FND_LOG.level_procedure
125 , L_LOG_MODULE || 'start'
126 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
127 );
128 FND_LOG.String
129 (
130 FND_LOG.level_procedure
131 , L_LOG_MODULE || 'param 1'
132 , 'errbuf:' || errbuf
133 );
134 FND_LOG.String
135 (
136 FND_LOG.level_procedure
137 , L_LOG_MODULE || 'param 2'
138 , 'errcode:' || errcode
139 );
140 FND_LOG.String
141 (
142 FND_LOG.level_procedure
143 , L_LOG_MODULE || 'param 3'
144 , 'p_api_version_number:' || p_api_version_number
145 );
146 FND_LOG.String
147 (
148 FND_LOG.level_procedure
149 , L_LOG_MODULE || 'param 4'
150 , 'p_init_msg_list:' || p_init_msg_list
151 );
152 FND_LOG.String
153 (
154 FND_LOG.level_procedure
155 , L_LOG_MODULE || 'param 5'
156 , 'p_commit:' || p_commit
157 );
158 FND_LOG.String
159 (
160 FND_LOG.level_procedure
161 , L_LOG_MODULE || 'param 6'
162 , 'p_validation_level:' || p_validation_level
163 );
164 FND_LOG.String
165 (
166 FND_LOG.level_procedure
167 , L_LOG_MODULE || 'param 7'
168 , 'p_creation_from_date' ||p_creation_from_date
169 );
170 FND_LOG.String
171 (
172 FND_LOG.level_procedure
173 , L_LOG_MODULE || 'param 8'
174 , 'p_creation_to_date' ||p_creation_to_date
175 );
176 FND_LOG.String
177 (
178 FND_LOG.level_procedure
179 , L_LOG_MODULE || 'param 9'
180 , 'p_sr_status:' || p_sr_status
181 );
182 FND_LOG.String
183 (
184 FND_LOG.level_procedure
185 , L_LOG_MODULE || 'param 10'
186 , 'p_number_of_workers:' || p_number_of_workers
187 );
188 FND_LOG.String
189 (
190 FND_LOG.level_procedure
191 , L_LOG_MODULE || 'param 11'
192 , 'p_cost_batch_size:' ||p_cost_batch_size
193 );
194 END IF;
195
196
197 IF NOT FND_API.Compatible_API_Call
198 (
199 L_API_VERSION
200 , p_api_version_number
201 , L_API_NAME
202 , g_pkg_name
203 )
204 THEN
205 FND_MSG_PUB.Count_And_Get
206 (
207 p_count => x_msg_count
208 , p_data => x_msg_data
209 );
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF ;
212
213 IF FND_API.to_Boolean(p_init_msg_list)
214 THEN
215 FND_MSG_PUB.initialize;
216 END IF ;
217
218 ---
219
220 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
221 THEN
222 FND_LOG.String
223 (
224 FND_LOG.level_statement
225 , L_LOG_MODULE || 'get_request_info_start'
226 , 'Getting Current Concurrent Request ID '
227 );
228 END IF;
229
230
231
232 -- preserving this concurrent request's
233 -- request_id in a local variable
234
235 -- Read the value from REQUEST_DATA. If this is the
236 -- first run of the program, then this value will be
237 -- null.
238 -- Otherwise, this will be the value that we passed to
239 -- SET_REQ_GLOBALS on the previous run.
240
241
242 l_request_id := fnd_global.conc_request_id;
243 l_request_data := fnd_conc_global.request_data; --This package is used for submitting sub-requests from PL/SQL concurrent programs.
244
245 IF l_request_data IS NULL then
246
247 -- This portion of the code is executed when the concurrent request is
248 -- invokedby the user. This time, the request data is NULL indicating
249 -- that the request is started newly.
250
251
252
253 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
254 THEN
255 FND_LOG.String
256 (
257 FND_LOG.level_statement
258 , L_LOG_MODULE || 'cleanup_start'
259 , 'deleting rows in staging table that were not cleared earlier'
260 );
261 END IF;
262
263 ----------------------------------------------------------------------------
264 -- Cleanup process: Delete all the rows in the staging table corresponding
265 -- to completed concurrent programs that have been left behind by an earlier
266 -- execution of this concurrent program.
267 ----------------------------------------------------------------------------
268
269
270 DELETE cs_cost_staging
271 WHERE
272 concurrent_request_id IN
273 (
274 SELECT
275 request_id
276 FROM
277 fnd_concurrent_requests r
278 , fnd_concurrent_programs p
279 WHERE
280 r.phase_code = 'C'
281 AND p.concurrent_program_id = r.concurrent_program_id
282 AND p.concurrent_program_name = 'CSCSTPG'
283 AND p.application_id = 170
284 );
285
286 l_row_count := SQL%ROWCOUNT;
287
288
289 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
290 THEN
291 FND_LOG.String
292 (
293 FND_LOG.level_statement
294 , L_LOG_MODULE || 'cleanup_end'
295 , 'after deleting rows in staging table that were not cleared earlier '
296 || l_row_count || ' rows'
297 );
298 END IF;
299 -- Committing the changes in order to make
300 -- the rows unavailable to all sessions.
301 COMMIT;
302
303
304
305
306
307 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
308 THEN
309 FND_LOG.String
310 (
311 FND_LOG.level_statement
312 , L_LOG_MODULE || 'call_validate_param_start'
313 , 'Calling procedure to validate cost parameters'
314 );
315 END IF;
316
317
318 -- calling a private procedure to perform validations on all the
319 -- cost parameters and throw corresponding exceptions in case
320 -- there are any errors
321
322 -- this procedure will validate the params p_creation_from_date, p_creation_to_date and p_sr_status
323
324
325 Validate_params( p_creation_from_date => p_creation_from_date
326 , p_creation_to_date => p_creation_to_date
327 , p_sr_status => p_sr_status
328 , x_creation_from_date => l_creation_from_date
329 , x_creation_to_date => l_creation_to_date
330 , x_msg_count => x_msg_count
331 , x_msg_data => x_msg_data
332 );
333
334
335
336 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
337 THEN
338 FND_LOG.String
339 (
340 FND_LOG.level_statement
341 , L_LOG_MODULE || 'call_validate_param_end'
342 , 'After calling procedure to validate cost parameters'
343 );
344 END IF;
345
346
347
348
349 ----------------------------------------------------------------------------
350 -- Preparation of Staging Table Data and Submission of Child Requests
351 ----------------------------------------------------------------------------
352
353 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
354 THEN
355 FND_LOG.String
356 (
357 FND_LOG.level_statement
358 , L_LOG_MODULE || 'call_form_and_exec_statement_start'
359 , 'Calling procedure to form and execute statement to fill staging table'
360 );
361 END IF;
362
363
364
365 Form_And_Exec_Statement -- this will insert data into the staging table
366 (
367 p_sr_status => p_sr_status
368 , p_creation_from_date => l_creation_from_date
369 , p_creation_to_date => l_creation_to_date
370 , p_number_of_workers => l_number_of_workers
371 , p_cost_batch_size => p_cost_batch_size
372 , p_request_id => l_request_id
373 , p_row_count => l_row_count
374 );
375
376 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
377 THEN
378 FND_LOG.String
379 (
380 FND_LOG.level_statement
381 , L_LOG_MODULE || 'call_form_and_exec_statement_end'
382 , 'After calling procedure to form and execute statement to '
383 || 'fill staging table ' || l_row_count
384 );
385 END IF;
386
387
388
389 IF l_row_count = 0
390 THEN
391 -- If there were no SRs selected, return
392 -- from the concurrent program with a warning
393
394 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
395 THEN
396 FND_LOG.String
397 (
398 FND_LOG.level_unexpected
399 , L_LOG_MODULE || 'no_rows'
400 , 'There were no rows picked up. Row count was ' || l_row_count
401 );
402 END IF ;
403
404 FND_MESSAGE.Set_Name('CS', 'CS_SR_NO_SRS_TO_CREATE_COST');
405 FND_MSG_PUB.ADD;
406
407 RAISE L_EXC_COST_WARNING;
408 END IF;
409
410 -- Start worker concurrent programs:
411
412 FOR
413 j IN 1..l_number_of_workers
414 LOOP
415 l_worker_conc_req_arr(j) := FND_REQUEST.Submit_Request
416 (
417 application => 'CS'
418 , program => 'CSCSTPGW'
419 , description => TO_CHAR(j)
420 , start_time => NULL
421 , sub_request => TRUE
422 , argument1 => 1-- p_api_version_number
423 , argument2 => 'T'--p_init_msg_list
424 , argument3 => 'T'--p_commit
425 , argument4 => 100--p_validation_level
426 , argument5 => j -- p_worker_id
427 , argument6 => 1000--p_cost_batch_size
428 , argument7 => l_request_id -- p_cost_set_id
429 );
430
431
432
433 IF
434 l_worker_conc_req_arr(j) = 0
435 THEN
436 -- If the worker request was not created successfully
437 -- raise an unexpected exception and terminate the
438 -- process.
439
440 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
441 THEN
442 FND_LOG.String
443 (
444 FND_LOG.level_unexpected
445 , L_LOG_MODULE || 'create_workers_error'
446 , 'Failed while starting worker concurrent request'
447 );
448 END IF;
449
450 FND_MESSAGE.Set_Name('CS', 'CS_SR_SUBMIT_CHILD_FAILED');
451 FND_MSG_PUB.ADD;
452
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 END IF;
455
456 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
457 THEN
458 FND_LOG.String
459 (
460 FND_LOG.level_statement
461 , L_LOG_MODULE || 'create_workers_doing'
462 , 'After starting worker ' || l_worker_conc_req_arr(j)
463 );
464 END IF;
465 END LOOP;
466
467 -- Committing so that the worker concurrent program that
468 -- was submitted above is started by the concurrent manager.
469
470 COMMIT;
471
472
473
474 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
475 THEN
476 FND_LOG.String
477 (
478 FND_LOG.level_statement
479 , L_LOG_MODULE || 'create_workers_end'
480 , 'After starting all worker concurrent requests'
481 );
482 END IF;
483
484 ---
485
486 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
487 THEN
488 FND_LOG.String
489 (
490 FND_LOG.level_statement
491 , L_LOG_MODULE || 'move_parent_to_paused_start'
492 , 'Moving parent concurrent request to paused status'
493 );
494 END IF;
495
496 COMMIT;
497
498 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
499 THEN
500 FND_LOG.String
501 (
502 FND_LOG.level_statement
503 , L_LOG_MODULE || 'move_parent_to_paused_end'
504 , 'After moving parent concurrent request to paused status'
505 );
506 END IF;
507
508
509 fnd_conc_global.set_req_globals
510 (
511 conc_status => 'PAUSED'
512 , request_data => '1'
513 );
514
515
516
517 elsif l_request_data IS NOT NULL then --ELSIF l_request_data IS NOT NULL then
518
519
520
521 -- If the concurrent request is restarted from the PAUSED state,
522 -- this portion of the code is executed. When all the child
523 -- requests have completed their work, (their PHASE_CODE
524 -- is 'COMPLETED') the concurrent manager restarts the parent. This
525 -- time, the request_data returns a Non NULL value and so this
526 -- portion of the code is executed.
527
528
529
530 l_main_conc_req_dev_status := 'NORMAL';
531
532 -- check status of worker concurrent request
533 -- to arrive at the parent request's
534 -- completion status
535
536 FOR r_child_request IN c_child_request(l_request_id)
537 LOOP
538 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
539 THEN
540 FND_LOG.String
541 (
542 FND_LOG.level_statement
543 , L_LOG_MODULE || 'collect_a_child_status'
544 , 'Worker Concurrent Request No : ' || r_child_request.request_id
545 );
546 END IF;
547
548 IF FND_CONCURRENT.Get_Request_Status
549 (
550 request_id => r_child_request.request_id
551 , phase => l_worker_conc_req_phase
552 , status => l_worker_conc_req_status
553 , dev_phase => l_worker_conc_req_dev_phase
554 , dev_status => l_worker_conc_req_dev_status
555 , message => l_worker_conc_req_message
556 )
557 THEN
558 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
559 THEN
560 FND_LOG.String
561 (
562 FND_LOG.level_statement
563 , L_LOG_MODULE || 'child_return_status'
564 , 'l_worker_conc_req_phase:' || l_worker_conc_req_phase
565 );
566 FND_LOG.String
567 (
568 FND_LOG.level_statement
569 , L_LOG_MODULE || 'child_return_status'
570 , 'l_worker_conc_req_status:' || l_worker_conc_req_status
571 );
572 FND_LOG.String
573 (
574 FND_LOG.level_statement
575 , L_LOG_MODULE || 'child_return_status'
576 , 'l_worker_conc_req_dev_phase:' || l_worker_conc_req_dev_phase
577 );
578 FND_LOG.String
579 (
580 FND_LOG.level_statement
581 , L_LOG_MODULE || 'child_return_status'
582 , 'l_worker_conc_req_dev_status:' || l_worker_conc_req_dev_status
583 );
584 FND_LOG.String
585 (
586 FND_LOG.level_statement
587 , L_LOG_MODULE || 'child_return_status'
588 , 'l_worker_conc_req_message:' || l_worker_conc_req_message
589 );
590 END IF;
591
592 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
593 THEN
594 FND_LOG.String
595 (
596 FND_LOG.level_statement
597 , L_LOG_MODULE || 'resolve_main_dev_status_start'
598 , 'Resolving l_main_conc_req_dev_status'
599 );
600 END IF;
601
602 -- If the current worker has completed its work, based
603 -- on the return status of the worker, mark the completion
604 -- status of the main concurrent request.
605
606 IF l_worker_conc_req_dev_status <> 'NORMAL'
607 THEN
608 IF l_main_conc_req_dev_status IN ('WARNING', 'NORMAL')
609 AND l_worker_conc_req_dev_status IN ('ERROR', 'DELETED', 'TERMINATED')
610 THEN
611 l_main_conc_req_dev_status := 'ERROR';
612 l_child_message := l_worker_conc_req_message;
613 ELSIF l_main_conc_req_dev_status = 'NORMAL'
614 AND l_worker_conc_req_dev_status = 'WARNING'
615 THEN
616 l_main_conc_req_dev_status := 'WARNING';
617 l_child_message := l_worker_conc_req_message;
618 END IF;
619 END IF;
620
621 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
622 THEN
623 FND_LOG.String
624 (
625 FND_LOG.level_statement
626 , L_LOG_MODULE || 'resolve_main_dev_status_end'
627 , 'After resolving l_main_conc_req_dev_status:'
628 || l_main_conc_req_dev_status
629 );
630 FND_LOG.String
631 (
632 FND_LOG.level_statement
633 , L_LOG_MODULE || 'resolve_main_dev_status_end'
634 , 'After resolving l_main_conc_req_dev_status - child_message :'
635 || l_child_message
636 );
637 END IF;
638
639 ELSE
640
641 -- There was a failure while collecting a child request
642 -- status, raising an unexpected exception
643
644 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
645 THEN
646 FND_LOG.String
647 (
648 FND_LOG.level_unexpected
649 , L_LOG_MODULE || 'collect_child_status_failed'
650 , 'Call to function fnd_concurrent.get_request_status failed. '
651 || l_main_conc_req_message
652 );
653 END IF;
654
655 FND_MESSAGE.Set_Name('CS', 'CS_SR_GET_CHILD_STAT_FAILED');
656 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
657 FND_MSG_PUB.ADD;
658
659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660 END IF;
661
662 END LOOP;
663
664
665 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
666 THEN
667 FND_LOG.String
668 (
669 FND_LOG.level_statement
670 , L_LOG_MODULE || 'collect_child_status_end'
671 , 'After collecting child completion status'
672 );
673 END IF;
674
675
676
677
678
679 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
680 THEN
681 FND_LOG.String
682 (
683 FND_LOG.level_statement
684 , L_LOG_MODULE || 'Write_cost_Output_start'
685 , 'Calling procedure to Write_cost_Output'
686 );
687 END IF;
688
689
690
691 --added newly
692 Write_cost_Output
693 (
694 p_cost_batch_size => p_cost_batch_size
695 , p_request_id => l_request_id
696 );
697
698
699 -- Cleaning up the staging table
700
701 /*DELETE cs_cost_staging
702 WHERE
703 concurrent_request_id = l_request_id;
704
705 l_row_count := SQL%ROWCOUNT;*/
706
707 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
708 THEN
709 FND_LOG.String
710 (
711 FND_LOG.level_statement
712 , L_LOG_MODULE || 'staging_table_cleanup_end'
713 , 'After cleaning up staging table ' || l_row_count
714 );
715 END IF;
716
717 ---
718
719 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
720 THEN
721 FND_LOG.String
722 (
723 FND_LOG.level_statement
724 , L_LOG_MODULE || 'act_on_ret_status_start'
725 , 'Acting on the main concurrent request return status:'
726 || l_main_conc_req_dev_status
727 );
728 END IF;
729
730
731 ---
732
733 -- commented now
734
735 -- Cleaning up the staging table
736
737 /* DELETE cs_cost_staging
738 WHERE
739 concurrent_request_id = l_request_id;
740
741 l_row_count := SQL%ROWCOUNT;*/
742
743
744 -- Set the completion status of the main concurrent request
745 -- by raising corresponding exceptions.
746
747 IF l_main_conc_req_dev_status = 'WARNING'
748 THEN
749 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_RET_STAT');
750 FND_MSG_PUB.ADD;
751
752 RAISE L_EXC_COST_WARNING;
753 ELSIF l_main_conc_req_dev_status = 'ERROR'
754 THEN
755 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_RET_STAT');
756 FND_MSG_PUB.ADD;
757
758 RAISE FND_API.G_EXC_ERROR;
759 END IF;
760
761
762 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
763 THEN
764 FND_LOG.String
765 (
766 FND_LOG.level_statement
767 , L_LOG_MODULE || 'act_on_ret_status_end'
768 , 'after Acting on the main concurrent request return status:'
769 || l_main_conc_req_dev_status
770 );
771 END IF;
772
773 ---
774
775 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
776 THEN
777 FND_LOG.String
778 (
779 FND_LOG.level_statement
780 , L_LOG_MODULE || 'set_comp_stat_normal_start'
781 , 'Setting completion status for parent concurrent request as NORMAL'
782 );
783 END IF;
784
785 ---
786 -- Setting the completion status of this concurrent
787 -- request as COMPLETED NORMALLY
788
789 l_ret := fnd_concurrent.set_completion_status
790 (
791 'NORMAL'
792 , ' '
793 );
794
795 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
796 THEN
797 FND_LOG.String
798 (
799 FND_LOG.level_statement
800 , L_LOG_MODULE || 'set_comp_stat_normal_end'
801 , 'After setting completion status for parent concurrent '
802 || 'request as NORMAL'
803 );
804 END IF;
805
806
807
808 end if;
809
810
811 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
812 THEN
813 IF FND_CONCURRENT.Get_Request_Status
814 (
815 request_id => l_request_id
816 , phase => l_main_conc_req_phase
817 , status => l_main_conc_req_status
818 , dev_phase => l_main_conc_req_dev_phase
819 , dev_status => l_main_conc_req_dev_status
820 , message => l_main_conc_req_message
821 )
822 THEN
823 FND_LOG.String
824 (
825 FND_LOG.level_procedure
826 , L_LOG_MODULE || 'request_status_1'
827 , 'l_main_conc_req_phase:' || l_main_conc_req_phase
828 );
829 FND_LOG.String
830 (
831 FND_LOG.level_procedure
832 , L_LOG_MODULE || 'request_status_2'
833 , 'l_main_conc_req_status:' || l_main_conc_req_status
834 );
835 FND_LOG.String
836 (
837 FND_LOG.level_procedure
838 , L_LOG_MODULE || 'request_status_3'
839 , 'l_main_conc_req_dev_phase:' || l_main_conc_req_dev_phase
840 );
841 FND_LOG.String
842 (
843 FND_LOG.level_procedure
844 , L_LOG_MODULE || 'request_status_4'
845 , 'l_main_conc_req_dev_status:' || l_main_conc_req_dev_status
846 );
847 FND_LOG.String
848 (
849 FND_LOG.level_procedure
850 , L_LOG_MODULE || 'request_status_5'
851 , 'l_main_conc_req_message:' || l_main_conc_req_message
852 );
853 END IF;
854 END IF ;
855
856 ---
857
858 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
859 THEN
860 FND_LOG.String
861 (
862 FND_LOG.level_procedure
863 , L_LOG_MODULE || 'end'
864 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
865 || x_return_status
866 );
867 FND_LOG.String
868 (
869 FND_LOG.level_procedure
870 , L_LOG_MODULE || 'end_time'
871 , 'The end time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
872 );
873 END IF ;
874
875
876
877 EXCEPTION
878
879 WHEN L_EXC_COST_WARNING THEN
880 x_return_status := FND_API.G_RET_STS_ERROR;
881
882 -- setting the completion status as WARNING since
883 -- there was a warning in the execution of this
884 -- request.
885
886 x_msg_count := FND_MSG_PUB.Count_Msg;
887 IF x_msg_count > 0
888 THEN
889 FND_MSG_PUB.Get
890 (
891 p_msg_index => 1
892 , p_encoded => 'F'
893 , p_data => x_msg_data
894 , p_msg_index_out => x_msg_index_out
895 );
896 END IF;
897
898 l_ret := fnd_concurrent.set_completion_status
899 (
900 'WARNING'
901 , SUBSTR(x_msg_data, 1, 240)
902 );
903
904 --Added for bug 7168775
905 fnd_file.put_line(fnd_file.log, 'Error encountered is : ' || x_msg_data || ' [Index:' || x_msg_index_out || ']');
906
907
908 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
909 THEN
910 FND_LOG.String
911 (
912 FND_LOG.level_unexpected
913 , L_LOG_MODULE || 'error'
914 , 'Inside WHEN L_EXC_COST_WARNING of ' || L_API_NAME_FULL
915 );
916
917 x_msg_count := FND_MSG_PUB.Count_Msg;
918
919 IF x_msg_count > 0
920 THEN
921 FOR
922 i IN 1..x_msg_count
923 LOOP
924 FND_MSG_PUB.Get
925 (
926 p_msg_index => i
927 , p_encoded => 'F'
928 , p_data => x_msg_data
929 , p_msg_index_out => x_msg_index_out
930 );
931 FND_LOG.String
932 (
933 FND_LOG.level_unexpected
934 , L_LOG_MODULE || 'error'
935 , 'Error encountered is : ' || x_msg_data || ' [Index:'
936 || x_msg_index_out || ']'
937 );
938 END LOOP;
939 END IF ;
940 END IF ;
941
942 WHEN FND_API.G_EXC_ERROR THEN
943 x_return_status := FND_API.G_RET_STS_ERROR;
944
945 -- setting the completion status as ERROR since
946 -- there was an error in the execution of this
947 -- request.
948
949 x_msg_count := FND_MSG_PUB.Count_Msg;
950 IF x_msg_count > 0
951 THEN
952 FND_MSG_PUB.Get
953 (
954 p_msg_index => 1
955 , p_encoded => 'F'
956 , p_data => x_msg_data
957 , p_msg_index_out => x_msg_index_out
958 );
959 END IF;
960
961 l_ret := fnd_concurrent.set_completion_status
962 (
963 'ERROR'
964 , SUBSTR(x_msg_data, 1, 240)
965 );
966
967 --Added for bug 7168775
968 fnd_file.put_line(fnd_file.log, 'Error encountered is : ' || x_msg_data || ' [Index:' || x_msg_index_out || ']');
969
970 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
971 THEN
972 FND_LOG.String
973 (
974 FND_LOG.level_unexpected
975 , L_LOG_MODULE || 'error'
976 , 'Inside WHEN FND_API.G_EXC_ERROR of ' || L_API_NAME_FULL
977 );
978
979 x_msg_count := FND_MSG_PUB.Count_Msg;
980
981 IF x_msg_count > 0
982 THEN
983 FOR
984 i IN 1..x_msg_count
985 LOOP
986 FND_MSG_PUB.Get
987 (
988 p_msg_index => i
989 , p_encoded => 'F'
990 , p_data => x_msg_data
991 , p_msg_index_out => x_msg_index_out
992 );
993 FND_LOG.String
994 (
995 FND_LOG.level_unexpected
996 , L_LOG_MODULE || 'error'
997 , 'Error encountered is : ' || x_msg_data || ' [Index:'
998 || x_msg_index_out || ']'
999 );
1000 END LOOP;
1001 END IF ;
1002 END IF ;
1003
1004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1005 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1006
1007 -- setting the completion status as ERROR since
1008 -- there was an unexpected error in the execution
1009 -- of this request.
1010
1011 x_msg_count := FND_MSG_PUB.Count_Msg;
1012 IF x_msg_count > 0
1013 THEN
1014 FND_MSG_PUB.Get
1015 (
1016 p_msg_index => 1
1017 , p_encoded => 'F'
1018 , p_data => x_msg_data
1019 , p_msg_index_out => x_msg_index_out
1020 );
1021 END IF;
1022
1023 l_ret := fnd_concurrent.set_completion_status
1024 (
1025 'ERROR'
1026 , SUBSTR(x_msg_data, 1, 240)
1027 );
1028
1029 --Added for bug 7168775
1030 fnd_file.put_line(fnd_file.log, 'Error encountered is : ' || x_msg_data || ' [Index:' || x_msg_index_out || ']');
1031
1032
1033 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1034 THEN
1035 FND_LOG.String
1036 (
1037 FND_LOG.level_unexpected
1038 , L_LOG_MODULE || 'unexpected_error'
1039 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
1040 );
1041
1042 x_msg_count := FND_MSG_PUB.Count_Msg;
1043
1044 IF x_msg_count > 0
1045 THEN
1046 FOR
1047 i IN 1..x_msg_count
1048 LOOP
1049 FND_MSG_PUB.Get
1050 (
1051 p_msg_index => i
1052 , p_encoded => 'F'
1053 , p_data => x_msg_data
1054 , p_msg_index_out => x_msg_index_out
1055 );
1056 FND_LOG.String
1057 (
1058 FND_LOG.level_unexpected
1059 , L_LOG_MODULE || 'unexpected_error'
1060 , 'Error encountered is : ' || x_msg_data || ' [Index:'
1061 || x_msg_index_out || ']'
1062 );
1063 END LOOP;
1064 END IF ;
1065 END IF ;
1066
1067 WHEN OTHERS THEN
1068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1069
1070 FND_MESSAGE.Set_Name('CS', 'CS_SR_COST_MAIN_FAIL');
1071 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
1072 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
1073 FND_MSG_PUB.ADD;
1074
1075 -- setting the completion status as ERROR since
1076 -- there was an unexpected error in the execution
1077 -- of this request.
1078
1079 x_msg_count := FND_MSG_PUB.Count_Msg;
1080 IF x_msg_count > 0
1081 THEN
1082 FND_MSG_PUB.Get
1083 (
1084 p_msg_index => 1
1085 , p_encoded => 'F'
1086 , p_data => x_msg_data
1087 , p_msg_index_out => x_msg_index_out
1088 );
1089 END IF;
1090
1091 l_ret := fnd_concurrent.set_completion_status
1092 (
1093 'ERROR'
1094 , SUBSTR(x_msg_data, 1, 240)
1095 );
1096
1097 --Added for bug 7168775
1098 fnd_file.put_line(fnd_file.log, 'Error encountered is : ' || x_msg_data || ' [Index:' || x_msg_index_out || ']');
1099
1100
1101 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1102 THEN
1103 FND_LOG.String
1104 (
1105 FND_LOG.level_unexpected
1106 , L_LOG_MODULE || 'when_others'
1107 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
1108 );
1109 FND_LOG.String
1110 (
1111 FND_LOG.level_unexpected
1112 , L_LOG_MODULE || 'when_others'
1113 , SQLERRM
1114 );
1115 END IF ;
1116
1117
1118 END create_cost;
1119
1120 ---------------------------------
1121 -- VALIDATE_PARAMS
1122 ---------------------------------
1123
1124
1125 PROCEDURE Validate_params
1126 (
1127 p_creation_from_date IN varchar2
1128 , p_creation_to_date IN varchar2
1129 , p_sr_status IN VARCHAR2
1130 , x_creation_from_date OUT NOCOPY DATE
1131 , x_creation_to_date OUT NOCOPY DATE
1132 , x_msg_count OUT NOCOPY NUMBER
1133 , x_msg_data OUT NOCOPY VARCHAR2
1134 ) IS
1135
1136 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_COST_PARAMS';
1137 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
1138 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
1139
1140 l_creation_from_date DATE;
1141 l_creation_to_date DATE;
1142 l_prompt VARCHAR2(250);
1143
1144 TIME_23_59_59 CONSTANT NUMBER := 1 - 1 / (24*60*59);
1145
1146
1147 BEGIN
1148
1149
1150 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1151 THEN
1152 FND_LOG.String
1153 (
1154 FND_LOG.level_statement
1155 , L_LOG_MODULE || 'check_blind_cost_start'
1156 , 'checking for blind search'
1157 );
1158 END IF;
1159
1160
1161 IF p_creation_from_date IS NULL
1162 OR p_creation_to_date IS NULL
1163 OR p_sr_status IS NULL
1164 THEN
1165 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1166 THEN
1167 FND_LOG.String
1168 (
1169 FND_LOG.level_unexpected
1170 , L_LOG_MODULE || 'no_params'
1171 , 'no parameters were supplied to the Concurrent program'
1172 );
1173 END IF ;
1174
1175 FND_MESSAGE.Set_Name('CS', 'CS_SR_NO_COST_PARAMS');
1176 FND_MSG_PUB.ADD;
1177
1178 RAISE FND_API.G_EXC_ERROR;
1179
1180
1181
1182 END IF;
1183
1184
1185 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1186 THEN
1187 FND_LOG.String
1188 (
1189 FND_LOG.level_statement
1190 , L_LOG_MODULE || 'check_blind_cost_end'
1191 , 'after checking for blind search'
1192 );
1193 END IF;
1194
1195 IF p_creation_from_date IS NOT NULL
1196 THEN
1197 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1198 THEN
1199 FND_LOG.String
1200 (
1201 FND_LOG.level_statement
1202 , L_LOG_MODULE || 'check_date_format_start_1'
1203 , 'checking if p_creation_from_date is in the format '
1204 || fnd_date.user_mask
1205 );
1206
1207 END IF;
1208
1209 -- Check if p_creation_from_date is of the format
1210 -- as maintained in the profile option ICX_DATE_FORMAT
1211 -- and if not, throw an error.
1212
1213 x_creation_from_date := fnd_date.string_to_date
1214 (
1215 p_creation_from_date
1216 , fnd_date.user_mask
1217 );
1218
1219 IF x_creation_from_date IS NULL
1220 THEN
1221 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1222 THEN
1223 FND_LOG.String
1224 (
1225 FND_LOG.level_unexpected
1226 , L_LOG_MODULE || 'crtfrmdt_format_invalid'
1227 , 'format of field p_creation_from_date is invalid. should be '
1228 || fnd_date.user_mask
1229 );
1230
1231 END IF ;
1232
1233 SELECT
1234 form_left_prompt
1235 INTO
1236 l_prompt
1237 FROM
1238 fnd_descr_flex_col_usage_vl
1239 WHERE
1240 end_user_column_name = 'P_CREATION_FROM_DATE'
1241 AND application_id = 170
1242 AND descriptive_flexfield_name = '$SRS$.CSCSTPG';
1243
1244 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_FORMAT_ERR');
1245 FND_MESSAGE.Set_Token('DATEFIELDNAME', l_prompt);
1246 FND_MESSAGE.Set_Token('FORMAT', fnd_date.user_mask);
1247 FND_MSG_PUB.ADD;
1248
1249 RAISE FND_API.G_EXC_ERROR;
1250
1251
1252
1253
1254 END IF;
1255
1256 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1257 THEN
1258 FND_LOG.String
1259 (
1260 FND_LOG.level_statement
1261 , L_LOG_MODULE || 'check_date_format_end_1'
1262 , 'after checking if p_creation_from_date is in the format '
1263 || fnd_date.user_mask
1264 );
1265 END IF;
1266 END IF;--p_creation_from_date IS NOT NULL
1267
1268 ------------------------------------------------------
1269
1270 IF p_creation_to_date IS NOT NULL
1271 THEN
1272 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1273 THEN
1274 FND_LOG.String
1275 (
1276 FND_LOG.level_statement
1277 , L_LOG_MODULE || 'check_date_format_start_2'
1278 , 'checking if p_creation_to_date is in the format '
1279 || fnd_date.user_mask
1280 );
1281 END IF;
1282
1283 -- Check if p_creation_to_date is of the format
1284 -- as maintained in the profile option ICX_DATE_FORMAT
1285 -- and if not, throw an error.
1286
1287 x_creation_to_date := fnd_date.string_to_date
1288 (
1289 p_creation_to_date
1290 , fnd_date.user_mask
1291 );
1292
1293 IF x_creation_to_date IS NULL
1294 THEN
1295 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1296 THEN
1297 FND_LOG.String
1298 (
1299 FND_LOG.level_unexpected
1300 , L_LOG_MODULE || 'crttodt_format_invalid'
1301 , 'format of field p_creation_to_date is invalid. should be ' ||
1302 fnd_date.user_mask
1303 );
1304 END IF ;
1305
1306 SELECT
1307 form_left_prompt
1308 INTO
1309 l_prompt
1310 FROM
1311 fnd_descr_flex_col_usage_vl
1312 WHERE
1313 end_user_column_name = 'P_CREATION_TO_DATE'
1314 AND application_id = 170
1315 AND descriptive_flexfield_name = '$SRS$.CSCSTPG';
1316
1317 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_FORMAT_ERR');
1318 FND_MESSAGE.Set_Token('DATEFIELDNAME', l_prompt);
1319 FND_MESSAGE.Set_Token('FORMAT', fnd_date.user_mask);
1320 FND_MSG_PUB.ADD;
1321
1322 RAISE FND_API.G_EXC_ERROR;
1323
1324
1325
1326 END IF;
1327
1328 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1329 THEN
1330 FND_LOG.String
1331 (
1332 FND_LOG.level_statement
1333 , L_LOG_MODULE || 'check_date_format_end_2'
1334 , 'after checking if p_creation_to_date is in the format ' ||
1335 fnd_date.user_mask
1336 );
1337 END IF;
1338
1339 ---
1340
1341 -- If the user_mask does not contain the time, then appending the time
1342 -- 23:59:59 to the date so that the whole day is covered. This is to
1343 -- take care of conditions where the from and to dates are the same day.
1344 --
1345 -- For ex., if the from date is 1-jan-1999 and to date is also 1-jan-1999,
1346 -- since there is no time given for these dates, the condition "where
1347 -- creation_date >= from_date and creation_date <= to_date" will not
1348 -- return any rows because if the creation date is 1-jan-1999 12:00:01,
1349 -- the condition creation_date >= from_date will be satisfied but the
1350 -- condition creation_date <= to_date will not be satisfied. In this
1351 -- situation, no rows will be picked up. To correct this issue, if the
1352 -- to_date contains the time 23:59:59, both the conditions will be
1353 -- satisfied.
1354
1355 IF TRUNC(x_creation_to_date) = x_creation_to_date
1356 THEN
1357 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1358 THEN
1359 FND_LOG.String
1360 (
1361 FND_LOG.level_statement
1362 , L_LOG_MODULE || 'add_time_to_todate_start'
1363 , 'adding time to x_creation_to_date if it does not have time'
1364 );
1365 END IF;
1366
1367 x_creation_to_date := x_creation_to_date + TIME_23_59_59;
1368
1369 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1370 THEN
1371 FND_LOG.String
1372 (
1373 FND_LOG.level_statement
1374 , L_LOG_MODULE || 'add_time_to_todate_end'
1375 , 'adding time to x_creation_to_date if it does not have time ' ||
1376 TO_CHAR(x_creation_to_date, 'DD-MON-YYYY HH24:MI:SS')
1377 );
1378 END IF;
1379 END IF;
1380 END IF;--p_creation_to_date IS NOT NULL
1381
1382
1383
1384 ---
1385
1386 IF x_creation_from_date IS NOT NULL
1387 AND x_creation_to_date IS NOT NULL
1388 THEN
1389 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1390 THEN
1391 FND_LOG.String
1392 (
1393 FND_LOG.level_statement
1394 , L_LOG_MODULE || 'date_crossvalid_start_1'
1395 , 'doing cross field validations x_creation_from_date '
1396 || '> x_creation_to_date '
1397 );
1398 END IF;
1399
1400 -- if both x_creation_from_date and x_creation_to_date are
1401 -- entered then x_creation_from_date should be before
1402 -- x_creation_to_date
1403
1404 IF x_creation_from_date > x_creation_to_date
1405 THEN
1406 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1407 THEN
1408 FND_LOG.String
1409 (
1410 FND_LOG.level_unexpected
1411 , L_LOG_MODULE || 'crtfrmdt_after_crttodt'
1412 , 'it is invalid to have x_creation_from_date > x_creation_to_date'
1413 );
1414 END IF ;
1415
1416 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_VALUE_ERR');
1417
1418 SELECT
1419 form_left_prompt
1420 INTO
1421 l_prompt
1422 FROM
1423 fnd_descr_flex_col_usage_vl
1424 WHERE
1425 end_user_column_name = 'P_CREATION_FROM_DATE'
1426 AND application_id = 170
1427 AND descriptive_flexfield_name = '$SRS$.CSCSTPG';
1428
1429 FND_MESSAGE.Set_Token('DATEFIELDNAME1', l_prompt);
1430
1431 SELECT
1432 form_left_prompt
1433 INTO
1434 l_prompt
1435 FROM
1436 fnd_descr_flex_col_usage_vl
1437 WHERE
1438 end_user_column_name = 'P_CREATION_TO_DATE'
1439 AND application_id = 170
1440 AND descriptive_flexfield_name = '$SRS$.CSCSTPG';
1441
1442 FND_MESSAGE.Set_Token('DATEFIELDNAME2', l_prompt);
1443 FND_MSG_PUB.ADD;
1444
1445 RAISE FND_API.G_EXC_ERROR;
1446 END IF;
1447
1448 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1449 THEN
1450 FND_LOG.String
1451 (
1452 FND_LOG.level_statement
1453 , L_LOG_MODULE || 'date_crossvalid_end_1'
1454 , 'after doing cross field validations x_creation_from_date > '
1455 || 'x_creation_to_date '
1456 );
1457 END IF;
1458 END IF;
1459
1460 ---
1461
1462
1463
1464 END Validate_params;
1465
1466
1467 ---------------------------------------
1468 -- Form_And_Exec_Statement
1469 ---------------------------------------
1470
1471 PROCEDURE Form_And_Exec_Statement
1472 (
1473 p_creation_from_date IN DATE
1474 , p_creation_to_date IN DATE
1475 , p_sr_status IN VARCHAR2
1476 , p_number_of_workers IN OUT NOCOPY NUMBER
1477 , p_cost_batch_size IN NUMBER
1478 , p_request_id IN NUMBER
1479 , p_row_count OUT NOCOPY NUMBER
1480 )
1481 IS
1482
1483 L_API_NAME CONSTANT VARCHAR2(30) := 'FORM_AND_EXEC_STATEMENT';
1484 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
1485 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
1486
1487
1488 CURSOR get_estimate_id IS
1489 SELECT ced.estimate_detail_id
1490 FROM cs_incidents_all_b cia
1491 ,cs_incident_statuses cis
1492 ,cs_estimate_details ced
1493 WHERE Nvl(cis.close_flag,'N') IN (decode( p_sr_status,'OPEN','N','CLOSED','Y' ,'ALL',Nvl(cis.close_flag,'N')))
1494 and cis.incident_subtype='INC'
1495 and cia.incident_status_id = cis.incident_status_id
1496 and cia.incident_id = ced.incident_id
1497 --and ced.estimate_Detail_id =115538
1498 and trunc(cia.creation_date) between trunc(p_creation_from_date) and trunc(p_creation_to_date);
1499
1500 l_number_of_workers NUMBER;
1501 l_row_count NUMBER;
1502 l_estimate_detail_id NUMBER;
1503
1504 BEGIN
1505
1506 OPEN get_estimate_id;
1507 LOOP
1508 FETCH get_estimate_id INTO l_estimate_detail_id;
1509 EXIT WHEN get_estimate_id%NOTFOUND;
1510
1511 INSERT INTO cs_cost_staging
1512 (
1513 estimate_detail_id,
1514 worker_id,
1515 concurrent_request_id
1516 )
1517 VALUES
1518 (
1519 l_estimate_Detail_id,
1520 NULL,
1521 p_request_id
1522 );
1523
1524 COMMIT;
1525 END LOOP;
1526
1527 p_row_count :=get_estimate_id%rowcount;
1528
1529 CLOSE get_estimate_id;
1530
1531
1532 COMMIT;
1533
1534
1535 -- Computing the number of worker concurrent
1536 -- requests required for purging the SRs.
1537 -- The approach followed to decide on howmany
1538 -- workers is required is as follows:
1539 -- 1. If the no. of rows < batch size, only 1 worker
1540 -- is required for processing the cost set.
1541 -- 2. If the no. of rows > batch size, ceil(batch_size / no_of_rows)
1542 -- is the no. of workers required for processing the cost set.
1543 -- But if this is more than the no. of workers asked for,
1544 -- no. of workers is = p_number_of_workers. Otherwise, it will
1545 -- be the result of the above formula.
1546
1547
1548
1549 IF p_row_count <= p_cost_batch_size
1550 THEN
1551 l_number_of_workers := 1;
1552 ELSIF p_row_count > p_cost_batch_size
1553 THEN
1554 l_number_of_workers := LEAST
1555 (
1556 p_number_of_workers
1557 , CEIL(p_row_count / p_cost_batch_size)
1558 );
1559 END IF;
1560
1561 p_number_of_workers := l_number_of_workers;
1562
1563
1564 UPDATE cs_cost_staging
1565 SET
1566 worker_id = MOD
1567 (
1568 ROWNUM - 1
1569 , l_number_of_workers
1570 ) + 1;
1571
1572 l_row_count := SQL%ROWCOUNT;
1573
1574 COMMIT;
1575
1576
1577 END Form_And_Exec_Statement;
1578
1579
1580 ---------------------------------------
1581 -- COST_WORKER
1582 ---------------------------------------
1583 -- -----------------------------------------------------------------------------
1584 -- Modification History
1585 -- Date Name Desc
1586 -- -------- --------- ----------------------------------------------------------
1587 -- 04-Jun-2008 bkanimoz Bug 7146881.Additional condition added to
1588 -- Update the Staging table status to 'S' only if cost record exist
1589 -- exists in costing table for the passed estimate_detail_id
1590
1591 -- -----------------------------------------------------------------------------
1592
1593 PROCEDURE Cost_Worker
1594 (
1595 errbuf OUT NOCOPY VARCHAR2
1596 , errcode OUT NOCOPY NUMBER
1597 , p_api_version_number IN NUMBER
1598 , p_init_msg_list IN VARCHAR2
1599 , p_commit IN VARCHAR2
1600 , p_validation_level IN NUMBER
1601 , p_worker_id IN NUMBER
1602 , p_cost_batch_size IN NUMBER
1603 , p_cost_set_id IN NUMBER
1604 )
1605 IS
1606
1607
1608 L_API_VERSION CONSTANT NUMBER := 1.0;
1609 L_API_NAME CONSTANT VARCHAR2(30) := 'COST_WORKER';
1610 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
1611 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
1612
1613 x_msg_count NUMBER;
1614 x_msg_index_out NUMBER;
1615 x_msg_data VARCHAR2(1000);
1616 x_return_status VARCHAR2(1);
1617 x_object_version_number NUMBER;
1618 l_Cost_rec cs_cost_details_pub.COST_REC_TYPE;
1619 x_cost_id NUMBER;
1620
1621 l_conc_req_phase VARCHAR2(100);
1622 l_conc_req_status VARCHAR2(100);
1623 l_conc_req_dev_phase VARCHAR2(100);
1624 l_conc_req_dev_status VARCHAR2(100);
1625 l_conc_req_message VARCHAR2(512);
1626
1627 l_request_id NUMBER;
1628
1629 l_processing_set_id NUMBER;
1630 l_row_count NUMBER;
1631 l_ret BOOLEAN;
1632 l_has_any_batch_failed BOOLEAN := FALSE;
1633
1634 l_message VARCHAR2(1000);
1635 p_estimate_detail_id NUMBER;
1636
1637 -- PL/SQL table to hold the incident_ids retrieved
1638 -- from the staging table, a batch at a time.
1639
1640 TYPE t_incident_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1641 l_incident_id_tbl t_incident_id_tbl;
1642
1643 -- PL/SQL table to hold the incident ids that had
1644 -- errors while performing validations with other
1645 -- products before purging the SRs. This table is
1646 -- used only when one of these procedures encountered
1647 -- an ORACLE EXCEPTION.
1648
1649 l_err_incident_id_tbl t_incident_id_tbl;
1650
1651 -- PL/SQL table to hold the error messages retrieved
1652 -- from the staging table when one of the procedures
1653 -- in the worker encounters an ORACLE EXCEPTION.
1654 -- This table is only used under these circumstances.
1655
1656 TYPE t_cost_error_message_tbl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1657 l_cost_error_message_tbl t_cost_error_message_tbl;
1658
1659
1660 CURSOR c_staging IS
1661 SELECT
1662 estimate_detail_id
1663 FROM
1664 cs_cost_staging
1665 WHERE
1666 worker_id = p_worker_id
1667 AND concurrent_request_id = p_cost_set_id
1668 AND status IS NULL;
1669 --Bug fix for 7146881
1670 CURSOR c_check_cost IS
1671 SELECT cost_id
1672 FROM cs_cost_details
1673 WHERE estimate_detail_id = p_estimate_detail_id;
1674
1675 j NUMBER :=1;
1676 l_estimate_id NUMBER;
1677 l_temp_count NUMBER;
1678 l_cost_id NUMBER;
1679
1680
1681 BEGIN
1682
1683 x_return_status := FND_API.G_RET_STS_SUCCESS;
1684
1685 -- capturing the request id of the
1686 -- worker thread into a local variable.
1687
1688 l_request_id := fnd_global.conc_request_id;
1689
1690 IF p_worker_id IS NULL
1691 OR p_cost_set_id IS NULL
1692 OR p_cost_batch_size IS NULL
1693
1694
1695 THEN
1696 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1697 THEN
1698 FND_LOG.String
1699 (
1700 FND_LOG.level_unexpected
1701 , L_LOG_MODULE || 'worker_params_not_enuf'
1702 , 'no parameters were supplied to the cost worker program'
1703 );
1704 END IF ;
1705
1706 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PARAM_NULL');
1707 FND_MSG_PUB.ADD;
1708
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711
1712 BEGIN
1713 SELECT
1714 1
1715 INTO
1716 l_row_count
1717 FROM
1718 fnd_concurrent_requests r
1719 , fnd_concurrent_programs p
1720 WHERE
1721 r.request_id = p_cost_set_id
1722 AND p.concurrent_program_id = r.concurrent_program_id
1723 AND p.concurrent_program_name = 'CSCSTPG'
1724 AND p.application_id = 170
1725 AND r.status_code <> 'C';
1726
1727 EXCEPTION
1728 WHEN NO_DATA_FOUND THEN
1729 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1730 THEN
1731 FND_LOG.String
1732 (
1733 FND_LOG.level_unexpected
1734 , L_LOG_MODULE || 'worker_purgset_invalid'
1735 , 'invalid cost set id supplied to the worker concurrent program'
1736 );
1737 END IF ;
1738
1739 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_COSTSET_INV');
1740 FND_MSG_PUB.ADD;
1741
1742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1743 END;
1744
1745 -- Opening the cursor inside the loop to avoid
1746 -- the ORA-1555 snapshot too old problem
1747
1748 begin
1749 OPEN c_staging;
1750 LOOP
1751
1752 -- main loop of the worker thread that collects
1753 -- incident_ids that need to be inserted into a
1754 -- pl/sql table, a batch at a time and inserts
1755 -- into the global temp table and calls the
1756 -- SR delete API.
1757
1758
1759 FETCH c_staging INTO l_temp_count;
1760 exit when c_staging%notfound;
1761
1762 --BULK COLLECT
1763 -- LIMIT p_cost_batch_size;
1764 -- EXIT WHEN c_staging%notfound;
1765 l_cost_rec.estimate_detail_id := l_temp_count;
1766
1767 --for j in 1..l_incident_id_tbl.COUNT loop
1768
1769 CS_COST_DETAILS_PVT.Create_cost_details
1770 (
1771 p_api_version => 1.0 ,
1772 p_init_msg_list => 'T' ,
1773 p_commit => 'T' ,
1774 p_validation_level => 100,
1775 x_return_status => x_return_status ,
1776 x_msg_count => x_msg_count ,
1777 x_object_version_number => x_object_version_number,
1778 x_msg_data => x_msg_data ,
1779 x_cost_id => x_cost_id ,
1780 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
1781 p_resp_id => FND_GLOBAL.RESP_ID,
1782 p_user_id => FND_GLOBAL.USER_ID,
1783 p_login_id => NULL ,
1784 p_transaction_control => 'T' ,
1785 p_Cost_Rec => l_cost_rec ,
1786 p_cost_creation_override => 'N'
1787 );
1788
1789
1790
1791 IF x_return_status = FND_API.G_RET_STS_SUCCESS then
1792 p_estimate_detail_id := l_temp_count;
1793
1794 OPEN c_check_cost;
1795 FETCH c_check_cost into l_cost_id;
1796 if c_check_cost%notfound then
1797 UPDATE cs_cost_staging
1798 SET status = 'E'
1799 ,error_message = x_msg_data
1800 WHERE estimate_detail_id = l_temp_count ;
1801 else
1802 UPDATE cs_cost_staging
1803 SET status = 'S'
1804 WHERE estimate_detail_id = l_temp_count ;
1805 end if;
1806 CLOSE c_check_cost;
1807
1808 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1809 OR x_return_status = FND_API.G_RET_STS_ERROR
1810
1811 -- If there was an error or unexpected error
1812 -- while executing the SR delete API
1813
1814 THEN
1815
1816 UPDATE cs_cost_staging
1817 SET
1818 status = 'E'
1819 , error_message = x_msg_data
1820 WHERE
1821 estimate_detail_id = l_temp_count
1822 AND NVL(status, 'S') = 'S';
1823
1824 END IF;
1825
1826 END LOOP;
1827 exception
1828 WHEN OTHERS THEN
1829 FND_FILE.PUT_LINE(FND_FILE.LOG,'exception : '||sqlerrm);
1830 end;
1831 --END LOOP;
1832
1833 CLOSE c_staging;
1834
1835
1836 Write_cost_Output
1837 (
1838 p_cost_batch_size => p_cost_batch_size
1839 , p_request_id => p_cost_set_id
1840 , p_worker_id => p_worker_id
1841 );
1842
1843 END COST_WORKER;
1844
1845
1846 PROCEDURE Write_cost_Output
1847 (
1848 p_cost_batch_size IN NUMBER
1849 , p_request_id IN NUMBER
1850 , p_worker_id IN NUMBER := NULL
1851 )
1852 IS
1853 --------------------------------------------------------------------------------
1854
1855 L_API_NAME CONSTANT VARCHAR2(300) := 'Write_cost_Output';
1856 L_API_NAME_FULL CONSTANT VARCHAR2(300) := g_pkg_name || '.' || L_API_NAME;
1857 L_LOG_MODULE CONSTANT VARCHAR2(1000) := 'cs.plsql.' || L_API_NAME_FULL || '.';
1858
1859 TYPE t_varchar_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
1860 TYPE t_number_arr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1861
1862 -- Cursor used to fetch all the SRs for which cost could not created due to business reasons as indicated
1863 -- by the various validation routines, along with
1864 -- the basic information of those SRs, to be used
1865 -- to generate a report of such SRs.
1866
1867 CURSOR c_cost_staging_err
1868 IS
1869 SELECT
1870 cia.incident_number incident_number
1871 ,ced.estimate_detail_id estimate_detail_id
1872 ,ced.line_number line_number
1873 -- , css.error_message cost_error_message
1874 --, substr(css.error_message,instr(css.error_message,':',1)+1) cost_error_message
1875 ,case when length(css.error_message)>120 then
1876 substr(css.error_message,1,75)
1877 else
1878 substr(css.error_message,instr(css.error_message,':',1)+1)
1879 end
1880 -- , p.party_number customer_number
1881 --, i.segment1 item_number
1882 -- , t.summary summary
1883
1884 FROM
1885 cs_cost_staging css
1886 , cs_estimate_Details ced
1887 , cs_incidents_all_b cia
1888 -- , mtl_system_items_b i
1889 -- , hz_parties p
1890 WHERE
1891 css.status = 'E'
1892 and css.ESTIMATE_DETAIL_ID = ced.ESTIMATE_DETAIL_ID
1893 and ced.incident_id = cia.incident_id
1894 and css.CONCURRENT_REQUEST_ID =p_request_id
1895 and css.worker_id=NVL(1, css.worker_id) ;
1896
1897
1898
1899
1900 l_incident_number_arr t_number_arr;
1901 l_estimate_detail_id_arr t_number_arr;
1902 l_line_number_arr t_varchar_arr;
1903 --l_summary_arr t_varchar_arr;
1904 l_cost_error_message_arr t_varchar_arr;
1905
1906 l_row_count NUMBER;
1907 l_report_caption VARCHAR2(4000);
1908 l_text VARCHAR2(4000);
1909 l_error_code_loc NUMBER;
1910 l_error_message_loc NUMBER;
1911 l_error_message_text VARCHAR2(4000);
1912
1913 l_exec_count NUMBER := 0;
1914
1915 BEGIN
1916
1917
1918
1919 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1920 THEN
1921 FND_LOG.String
1922 (
1923 FND_LOG.level_statement
1924 , L_LOG_MODULE || 'sr_submit_count_start'
1925 , 'Getting number of SRs submitted for Cost Creation'
1926 );
1927 END IF;
1928
1929 fnd_file.put_line
1930 (
1931 FND_FILE.OUTPUT
1932 , '<html><body>'
1933 );
1934
1935
1936
1937 l_report_caption := FND_MESSAGE.Get_String
1938 (
1939 'CS'
1940 , 'CS_SR_COST_RESULT'
1941 );
1942
1943 fnd_file.put_line
1944 (
1945 FND_FILE.OUTPUT
1946 , '<h3>' || l_report_caption
1947 || '</h3><table border cellspacing=0 cellpadding=5 width=40%>'
1948 );
1949
1950
1951 l_report_caption := FND_MESSAGE.Get_String
1952 (
1953 'CS'
1954 , 'CS_SR_COST_SUBMIT_COUNT'
1955 );
1956
1957 -- Query to find out the total number of SRs
1958 -- submitted for COST
1959
1960 SELECT
1961 count(1)
1962 INTO
1963 l_row_count
1964 FROM
1965 cs_cost_staging s
1966 WHERE
1967 s.worker_id = NVL(p_worker_id, s.worker_id);
1968
1969 fnd_file.put_line
1970 (
1971 FND_FILE.OUTPUT
1972 , '<tr><td><b>' || l_report_caption || '</b></td><td><b>'
1973 || l_row_count || '</b></td></tr>'
1974 );
1975
1976
1977
1978
1979 l_report_caption := FND_MESSAGE.Get_String
1980 (
1981 'CS'
1982 , 'CS_SR_COST_SUCCESS_COUNT'
1983 );
1984
1985 -- Query to find out the total number of SRs
1986 -- successfully Created
1987
1988 SELECT
1989 count(1)
1990 INTO
1991 l_row_count
1992 FROM
1993 cs_cost_staging s
1994 WHERE
1995 status = 'S'
1996 AND s.worker_id = NVL(p_worker_id, s.worker_id);
1997
1998 fnd_file.put_line
1999 (
2000 FND_FILE.OUTPUT
2001 , '<tr><td><b>' || l_report_caption || '</b></td><td><font color=green><b>'
2002 || l_row_count || '</b></font></td></tr>'
2003 );
2004
2005
2006
2007 l_report_caption := FND_MESSAGE.Get_String
2008 (
2009 'CS'
2010 , 'CS_SR_COST_NOTDONE_COUNT'
2011 );
2012
2013 -- Query to find out the total number of SRs
2014 -- successfully cost Created
2015
2016 SELECT
2017 count(1)
2018 INTO
2019 l_row_count
2020 FROM
2021 cs_cost_staging s
2022 WHERE
2023 status IS NULL
2024 AND s.worker_id = NVL(p_worker_id, s.worker_id);
2025
2026 IF l_row_count > 0
2027
2028 -- if there were some rows that were not
2029 -- processed, display that too in the report.
2030
2031 THEN
2032 fnd_file.put_line
2033 (
2034 FND_FILE.OUTPUT
2035 , '<tr><td><b>' || l_report_caption
2036 || '</b></td><td><font color=blue><b>'
2037 || l_row_count || '</b></font></td></tr>'
2038 );
2039 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2040 THEN
2041 FND_LOG.String
2042 (
2043 FND_LOG.level_statement
2044 , L_LOG_MODULE || 'html_output_5'
2045 , '<tr><td><b>' || l_report_caption
2046 || '</b></td><td><font color=blue><b>' || l_row_count
2047 || '</b></font></td></tr>'
2048 );
2049 END IF;
2050 END IF;
2051
2052
2053
2054
2055 l_report_caption := FND_MESSAGE.Get_String
2056 (
2057 'CS'
2058 , 'CS_SR_COST_FAILURE_COUNT'
2059 );
2060
2061 -- Query to find out the total number of SRs
2062 -- failed while attempting to create cost due to
2063 -- failure in validations
2064
2065 SELECT
2066 count(1)
2067 INTO
2068 l_row_count
2069 FROM
2070 cs_cost_staging s
2071 WHERE
2072 status = 'E'
2073 AND s.worker_id = NVL(p_worker_id, s.worker_id);
2074
2075 IF l_row_count > 0
2076
2077 -- if there were some rows that failed during
2078 -- processing, display that in the report.
2079
2080 THEN
2081 fnd_file.put_line
2082 (
2083 FND_FILE.OUTPUT
2084 , '<tr><td><b>' || l_report_caption
2085 || '</b></td><td><font color=red><b>' || l_row_count
2086 || '</b></font></td></tr></table>'
2087 );
2088 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2089 THEN
2090 FND_LOG.String
2091 (
2092 FND_LOG.level_statement
2093 , L_LOG_MODULE || 'html_output_6'
2094 , '<tr><td><b>' || l_report_caption
2095 || '</b></td><td><font color=red><b>' || l_row_count
2096 || '</b></font></td></tr></table>'
2097 );
2098 END IF;
2099 END IF;
2100
2101
2102
2103 IF l_row_count > 0
2104
2105 -- if there are any rows in the staging
2106 -- table with cost_status = E
2107
2108 THEN
2109 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2110 THEN
2111 FND_LOG.String
2112 (
2113 FND_LOG.level_statement
2114 , L_LOG_MODULE || 'sr_failure_report_start'
2115 , 'Listing all SRs that failed with details and error message'
2116 );
2117 END IF;
2118
2119 -- Starting to print the report on all the SRs that failed
2120 -- cost creation due to business reasons along with the vital details
2121
2122 fnd_file.put_line
2123 (
2124 FND_FILE.OUTPUT
2125 , '<h3>'
2126 );
2127 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2128 THEN
2129 FND_LOG.String
2130 (
2131 FND_LOG.level_statement
2132 , L_LOG_MODULE || 'html_output_7'
2133 , '<h3>'
2134 );
2135 END IF;
2136
2137 l_report_caption := FND_MESSAGE.Get_String
2138 (
2139 'CS'
2140 , 'CS_SR_FAILED_ESTIMATE_REPORT'
2141 );
2142 fnd_file.put_line
2143 (
2144 FND_FILE.OUTPUT
2145 , l_report_caption
2146 );
2147 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2148 THEN
2149 FND_LOG.String
2150 (
2151 FND_LOG.level_statement
2152 , L_LOG_MODULE || 'html_output_8'
2153 , l_report_caption
2154 );
2155 END IF;
2156
2157 fnd_file.put_line
2158 (
2159 FND_FILE.OUTPUT
2160 , '</h3>'
2161 );
2162 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2163 THEN
2164 FND_LOG.String
2165 (
2166 FND_LOG.level_statement
2167 , L_LOG_MODULE || 'html_output_9'
2168 , '</h3>'
2169 );
2170 END IF;
2171
2172 ---
2173
2174
2175 fnd_file.put_line
2176 (
2177 FND_FILE.OUTPUT
2178 , '<table border cellspacing=0 width=100%><tr>'
2179 );
2180 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2181 THEN
2182 FND_LOG.String
2183 (
2184 FND_LOG.level_statement
2185 , L_LOG_MODULE || 'html_output_10'
2186 , '<table border cellspacing=0 width=100%><tr>'
2187 );
2188 END IF;
2189
2190 l_report_caption := FND_MESSAGE.Get_String
2191 (
2192 'CS'
2193 , 'CS_SR_COST_FAILED_RPT_HEAD'
2194 );
2195 fnd_file.put_line
2196 (
2197 FND_FILE.OUTPUT
2198 , l_report_caption
2199 );
2200 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2201 THEN
2202 FND_LOG.String
2203 (
2204 FND_LOG.level_statement
2205 , L_LOG_MODULE || 'html_output_11'
2206 , l_report_caption
2207 );
2208 END IF;
2209
2210 fnd_file.put_line
2211 (
2212 FND_FILE.OUTPUT
2213 , '</tr>'
2214 );
2215 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2216 THEN
2217 FND_LOG.String
2218 (
2219 FND_LOG.level_statement
2220 , L_LOG_MODULE || 'html_output_12'
2221 , '</tr>'
2222 );
2223 END IF;
2224
2225 -- Opening cursor on staging table that lists
2226 -- all the SRs that failed due to business reasons
2227 -- along with the vital details of the SR
2228
2229 OPEN c_cost_staging_err;
2230
2231 -- Loop that retrieves the rows from the staging table
2232 -- in batches and prints the output file.
2233
2234 LOOP
2235 FETCH c_cost_staging_err
2236 BULK COLLECT INTO
2237 l_incident_number_arr
2238 , l_estimate_detail_id_arr
2239 , l_line_number_arr
2240 --, l_summary_arr
2241 , l_cost_error_message_arr
2242 LIMIT p_cost_batch_size;
2243
2244 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2245 THEN
2246 FND_LOG.String
2247 (
2248 FND_LOG.level_statement
2249 , L_LOG_MODULE || 'report_fetch_count'
2250 , 'Fetched ' || l_incident_number_arr.COUNT
2251 || ' rows during this execution'
2252 );
2253 END IF;
2254
2255 IF l_incident_number_arr.COUNT > 0
2256 THEN
2257
2258 -- Inner loop that inserts the current batch of
2259 -- SRs into the output file. Here, it is assumed
2260 -- that the cost_error_message field contains
2261 -- messages in the format
2262 -- <product>:<message code>~<concurrent request text-message>
2263 -- using which the message text is retrieved from
2264 -- the message dictionary.
2265
2266 FOR j IN l_incident_number_arr.FIRST..l_incident_number_arr.LAST
2267 LOOP
2268 fnd_file.put_line
2269 (
2270 FND_FILE.OUTPUT
2271 , '<tr>'
2272 );
2273
2274 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2275 THEN
2276 FND_LOG.String
2277 (
2278 FND_LOG.level_statement
2279 , L_LOG_MODULE || 'html_output_13'
2280 , '<tr>'
2281 );
2282 END IF;
2283
2284 l_text := '<td>' || l_incident_number_arr(j)
2285 || '</td><td>' || l_estimate_detail_id_arr(j)
2286 || '</td><td>' || NVL(l_line_number_arr(j), '-')
2287 -- || '</td><td>' || l_summary_arr(j)
2288 || '</td><td>';
2289
2290 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2291 THEN
2292 FND_LOG.String
2293 (
2294 FND_LOG.level_statement
2295 , L_LOG_MODULE || 'compute_text'
2296 , 'framing l_text = ' || l_text
2297 );
2298 END IF;
2299
2300 l_error_code_loc := INSTR
2301 (
2302 l_cost_error_message_arr(j)
2303 , ':'
2304 , 1
2305 );
2306
2307 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2308 THEN
2309 FND_LOG.String
2310 (
2311 FND_LOG.level_statement
2312 , L_LOG_MODULE || 'compute_text_1'
2313 , 'getting l_error_code_loc = ' || l_error_code_loc
2314 );
2315 END IF;
2316
2317 l_error_message_loc := INSTR
2318 (
2319 l_cost_error_message_arr(j)
2320 , '~'
2321 , 1
2322 );
2323
2324
2325 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2326 THEN
2327 FND_LOG.String
2328 (
2329 FND_LOG.level_statement
2330 , L_LOG_MODULE || 'compute_text_2'
2331 , 'getting l_error_message_loc = ' || l_error_message_loc
2332 );
2333 END IF;
2334
2335 IF l_error_message_loc > 0 THEN
2336
2337
2338 l_error_message_text := FND_MESSAGE.Get_String
2339
2340 (
2341 SUBSTR
2342 (
2343 l_cost_error_message_arr(j)
2344 , 1
2345 , l_error_code_loc - 1
2346 )
2347 , SUBSTR
2348 (
2349 l_cost_error_message_arr(j)
2350 , l_error_code_loc + 1
2351 , l_error_message_loc - l_error_code_loc - 1
2352 )
2353 )
2354 || ' - '
2355 || SUBSTR
2356 (
2357 l_cost_error_message_arr(j)
2358 , l_error_message_loc + 1
2359 );
2360
2361 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2362 THEN
2363 FND_LOG.String
2364 (
2365 FND_LOG.level_statement
2366 , L_LOG_MODULE || 'compute_text_3.1'
2367 , 'getting l_error_message_text = ' || l_error_message_text
2368 );
2369 END IF;
2370 ELSIF l_error_message_loc <= 0
2371 THEN
2372 l_error_message_text := FND_MESSAGE.Get_String
2373 (
2374 SUBSTR
2375 (
2376 l_cost_error_message_arr(j)
2377 , 1
2378 , l_error_code_loc - 1
2379 )
2380 , SUBSTR
2381 (
2382 l_cost_error_message_arr(j)
2383 , l_error_code_loc + 1
2384 )
2385 );
2386
2387 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2388 THEN
2389 FND_LOG.String
2390 (
2391 FND_LOG.level_statement
2392 , L_LOG_MODULE || 'compute_text_3.2'
2393 , 'getting l_error_message_text = ' || l_error_message_text
2394 );
2395 END IF;
2396 END IF;
2397
2398 l_text := l_text || NVL(l_error_message_text, '-') || '</td>';
2399
2400 fnd_file.put_line
2401 (
2402 FND_FILE.OUTPUT
2403 , l_text
2404 );
2405
2406 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2407 THEN
2408 FND_LOG.String
2409 (
2410 FND_LOG.level_statement
2411 , L_LOG_MODULE || 'html_output_14'
2412 , l_text
2413 );
2414 END IF;
2415
2416 fnd_file.put_line
2417 (
2418 FND_FILE.OUTPUT
2419 , '</tr>'
2420 );
2421
2422 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2423 THEN
2424 FND_LOG.String
2425 (
2426 FND_LOG.level_statement
2427 , L_LOG_MODULE || 'html_output_15'
2428 , '</tr>'
2429 );
2430 END IF;
2431 END LOOP;
2432 END IF;
2433
2434 EXIT WHEN c_cost_staging_err%NOTFOUND;
2435 END LOOP;
2436
2437 CLOSE c_cost_staging_err;
2438
2439
2440
2441 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2442 THEN
2443 FND_LOG.String
2444 (
2445 FND_LOG.level_statement
2446 , L_LOG_MODULE || 'sr_failure_report_end'
2447 , 'After listing all SRs that failed with details and error message'
2448 );
2449 END IF;
2450 END IF;
2451
2452 fnd_file.put_line
2453 (
2454 FND_FILE.OUTPUT
2455 , '</table></body></html>'
2456 );
2457 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2458 THEN
2459 FND_LOG.String
2460 (
2461 FND_LOG.level_statement
2462 , L_LOG_MODULE || 'html_output_16'
2463 , '</table></body></html>'
2464 );
2465 END IF;
2466
2467 ---
2468
2469 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2470 THEN
2471 FND_LOG.String
2472 (
2473 FND_LOG.level_procedure
2474 , L_LOG_MODULE || 'end'
2475 , 'Completed work in ' || L_API_NAME_FULL || ' with Success'
2476 );
2477 END IF ;
2478 END Write_cost_Output;
2479 --------------------------------------------------------------------------------
2480
2481 END CS_SR_COST_CP ;
2482