4 -- Package level definitions
1 PACKAGE BODY CS_SR_PURGE_CP AS
2 /* $Header: csvsrpgb.pls 120.9.12020000.2 2013/01/17 01:53:16 siahmed ship $ */
3 --------------------------------------------------------------------------------
5 --------------------------------------------------------------------------------
6 g_pkg_name CONSTANT VARCHAR2(30) := 'CS_SR_PURGE_CP';
7
8 PROCEDURE Validate_Purge_Params
9 (
10 p_incident_id IN NUMBER
11 , p_incident_status_id IN NUMBER
12 , p_incident_type_id IN NUMBER
13 , p_creation_from_date IN VARCHAR2
14 , p_creation_to_date IN VARCHAR2
15 , p_last_update_from_date IN VARCHAR2
16 , p_last_update_to_date IN VARCHAR2
17 , x_creation_from_date OUT NOCOPY DATE
18 , x_creation_to_date OUT NOCOPY DATE
19 , x_last_update_from_date OUT NOCOPY DATE
20 , x_last_update_to_date OUT NOCOPY DATE
21 , p_not_updated_since IN VARCHAR2
22 , p_customer_id IN NUMBER
23 , p_customer_acc_id IN NUMBER
24 , p_item_category_id IN NUMBER
25 , p_inventory_item_id IN NUMBER
26 , p_history_size IN NUMBER
27 , p_number_of_workers IN NUMBER
28 , p_purge_batch_size IN NUMBER
29 , p_purge_source_with_open_task IN VARCHAR2
30 , p_audit_required IN VARCHAR2
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 );
34
35 PROCEDURE Form_And_Exec_Statement
36 (
37 p_incident_id IN NUMBER
38 , p_incident_status_id IN NUMBER
39 , p_incident_type_id IN NUMBER
40 , p_creation_from_date IN DATE
41 , p_creation_to_date IN DATE
42 , p_last_update_from_date IN DATE
43 , p_last_update_to_date IN DATE
44 , p_customer_id IN NUMBER
45 , p_customer_acc_id IN NUMBER
46 , p_item_category_id IN NUMBER
47 , p_inventory_item_id IN NUMBER
48 , p_history_size IN NUMBER
49 , p_number_of_workers IN OUT NOCOPY NUMBER
50 , p_purge_batch_size IN NUMBER
51 , p_request_id IN NUMBER
52 , p_row_count OUT NOCOPY NUMBER
53 );
54
55 PROCEDURE Write_Purge_Output
56 (
57 p_purge_batch_size IN NUMBER
58 , p_request_id IN NUMBER
59 , p_worker_id IN NUMBER := NULL
60 );
61
62 --------------------------------------------------------------------------------
63
64
65 --------------------------------------------------------------------------------
66 -- Procedure Name : PURGE_SERVICEREQUESTS
67 --
68 -- Parameters (other than standard ones)
69 --
70 -- IN OUT
71 -- errbuf : This parameter is not used but is a standard
72 -- parameter for concurrent program procedures.
73 -- The function fnd_concurrent.set_completion_status
74 -- is called instead.
75 -- errcode : This parameter is not used but is a standard
76 -- parameter for concurrent program procedures.
77 -- The function fnd_concurrent.set_completion_status
78 -- is called instead.
79 --
80 -- IN
81 -- p_incident_id : Indicates that SR with this id needs
82 -- to be purged
83 -- p_incident_status_id : Indicates that SR with this status id
87 -- p_creation_from_date : Indicates the lower end of the range of dates
84 -- needs to be purged
85 -- p_incident_type_id : Indicates that SRs with this type id
86 -- needs to be purged
88 -- that need to be compared with CREATION_DATE of
89 -- the SR to pick it up for purge
90 -- p_creation_to_date : Indicates the higher end of the range of dates
91 -- that need to be compared with CREATION_DATE of
92 -- the SR to pick it up for purge
93 -- p_last_update_from_date : Indicates the lower end of the range of dates
94 -- that need to be compared with LAST_UPDATED_DATE of
95 -- the SR to pick it up for purge
96 -- p_last_update_to_date : Indicates the higher end of the range of dates
97 -- that need to be compared with LAST_UPDATED_DATE of
98 -- the SR to pick it up for purge
99 -- p_not_updated_since : This is a set of values like 1Y,2Y etc. which
100 -- shall be compared with the LAST_UPDATED_DATE of the
101 -- the SR to pick it up for purge
102 -- p_customer_id : Indicates that SRs with this customer_id need
103 -- to be purged.
104 -- p_customer_acc_id : Indicates that SRs with this customer acc id
105 -- need to be purged
106 -- p_item_category_id : Indicates that SRs created for items falling
107 -- under this category need to be purged
108 -- p_inventory_item_id : Indicates that SRs created for this item
109 -- need to be purged
110 -- p_history_size : Number of customer SR's to retain while purging
111 -- SRs identified using other parameters. This param
112 -- alone CANNOT be used to identify a valid purgeset.
113 -- p_number_of_workers : Number of workers that needs to be launched for
114 -- purging Service Requests
115 -- p_purge_batch_size : Number of Service Requests that needs to be purged
116 -- in a batch
117 -- p_purge_source_with_open_task :
118 -- This signifies if the Tasks Validation API can
119 -- delete tasks that are open. If this is N, only SRs
120 -- linked to closed Tasks are allowed to be purged.
121 -- If this is Y, all SRs, irrespective of whether the
122 -- Tasks linked tothem are open or closed, can be
123 -- deleted.
124 -- p_audit_required : This indicates if the SR Delete API should write
125 -- the purge audit information. If this is N, no rows
126 -- are inserted into the table
127 -- CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y,
128 -- audit rows are inserted into these tables.
129 --
130 -- Note: The above parameters are not mandatory and may contain NULL values.
131 -- If these have been sent NULL, it means that the parameter should not be
132 -- considered for identifying SRs to be purged. A where clause shall be
133 -- constructed using values sent in these parameters and this will be used
134 -- to identify SRs that need to be purged.
135 --
136 -- Description
137 -- This procedure accepts the above list of parameters to identify the SRs
138 -- that need to be purged. It constructs a WHERE clause out of these
139 -- parameters after validating them. This WHERE clause is appended to a
140 -- query on the table CS_INCIDENTS_ALL_B and result of this query is
141 -- inserted into a staging table CS_INCIDENTS_PURGE_STAGING after which
142 -- the rows are divided among the number of worker concurrent programs
143 -- using a formula 'mod(rownum - 1, <no. of workers>) + 1'. After that
144 -- the child concurrent requests are launched and the SRs are purged. This
145 -- procedure waits for all the child concurrent requests to complete
146 -- purging the SRs allocated to them and then ends.
147 --
148 -- HISTORY
149 --
150 ----------------+------------+--------------------------------------------------
151 -- DATE | UPDATED BY | Change Description
152 ----------------+------------+--------------------------------------------------
153 -- 2-Aug_2005 | varnaray | Created
154 -- | |
155 ----------------+------------+--------------------------------------------------
156 /*#
157 * This procedure accepts the above list of parameters to identify the SRs that
158 * need to be purged. It constructs a WHERE clause out of these parameters after
159 * validating them. This WHERE clause is appended to a query on the table
160 * CS_INCIDENTS_ALL_B and result of this query is inserted into a staging table
161 * CS_INCIDENTS_PURGE_STAGING after which the rows are divided among the number
162 * of worker concurrent programs using a formula 'mod(rownum - 1,
163 * <no. of workers>) + 1'. After that the child concurrent requests are
164 * launched and the SRs are purged. This procedure waits for all the child
165 * concurrent requests to complete purging the SRs allocated to them and then
166 * ends.
167 * @param errbuf This parameter is not used but is a standard parameter for
168 * concurrent program procedures. The function fnd_concurrent.
169 * set_completion_status is called instead.
170 * @param errcode This parameter is not used but is a standard parameter
171 * for concurrent program procedures. The function fnd_concurrent.
172 * set_completion_status is called instead.
173 * @param p_incident_id Indicates that SR with this id needs to be purged
174 * @param p_incident_status_id Indicates that SR with this status id needs
175 * to be purged
176 * @param p_incident_type_id Indicates that SRs with this type id needs to
177 * be purged
181 * need to be compared with CREATION_DATE of the SR to pick it up for purge
178 * @param p_creation_from_date Indicates the lower end of the range of dates
179 * that need to be compared with CREATION_DATE of the SR to pick it up for purge
180 * @param p_creation_to_date Indicates the higher end of the range of dates that
182 * @param p_last_update_from_date Indicates the lower end of the range of dates
183 * that need to be compared with LAST_UPDATED_DATE of the SR to pick it
184 * up for purge
185 * @param p_last_update_to_date Indicates the higher end of the range of dates
186 * that need to be compared with LAST_UPDATED_DATE of the SR to pick it up for
187 * purge
188 * @param p_not_updated_since This is a set of values like 1Y,2Y etc. which
189 * shall be compared with the LAST_UPDATED_DATE of the the SR to pick it up
190 * for purge
191 * @param p_customer_id Indicates that SRs with this customer_id need to
192 * be purged.
193 * @param p_customer_acc_id Indicates that SRs with this customer acc id need
194 * to be purged
195 * @param p_item_category_id Indicates that SRs created for items falling
196 * under this category need to be purged
197 * @param p_inventory_item_id Indicates that SRs created for this item need
198 * to be purged
199 * @param p_history_size Number of customer SR's to retain while purging SRs
200 * identified using other parameters. This parameter alone CANNOT be used to
201 * identify a valid purgeset.
202 * @param p_number_of_workers Number of workers that needs to be launched
203 * for purging Service Requests
204 * @param p_purge_batch_size Number of Service Requests that needs to be purged
205 * in a batch
206 * @param p_purge_source_with_open_task This signifies if the Tasks Validation
207 * API can delete tasks that are open. If this is N, only SRs linked to closed
208 * Tasks are allowed to be purged. If this is Y, all SRs, irrespective of
209 * whether the Tasks linked to them are open or closed, can be deleted.
210 * @param p_audit_required This indicates if the SR Delete API should write
211 * the purge audit information. If this is N, no rows are inserted into the
212 * table CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y, audit rows are
213 * inserted into these tables.
214 * @rep:scope internal
215 * @rep:product CS
216 * @rep:displayname Purge Service Requests Concurrent Program
217 */
218 PROCEDURE Purge_ServiceRequests
219 (
220 errbuf IN OUT NOCOPY VARCHAR2
221 , errcode IN OUT NOCOPY INTEGER
222 , p_api_version_number IN NUMBER
223 , p_init_msg_list IN VARCHAR2
224 , p_commit IN VARCHAR2
225 , p_validation_level IN NUMBER
226 , p_incident_id IN NUMBER
227 , p_incident_status_id IN NUMBER
228 , p_incident_type_id IN NUMBER
229 , p_creation_from_date IN VARCHAR2
230 , p_creation_to_date IN VARCHAR2
231 , p_last_update_from_date IN VARCHAR2
232 , p_last_update_to_date IN VARCHAR2
233 , p_not_updated_since IN VARCHAR2
234 , p_customer_id IN NUMBER
235 , p_customer_acc_id IN NUMBER
236 , p_item_category_id IN NUMBER
237 , p_inventory_item_id IN NUMBER
238 , p_history_size IN NUMBER
239 , p_number_of_workers IN NUMBER
240 , p_purge_batch_size IN NUMBER
241 , p_purge_source_with_open_task IN VARCHAR2
242 , p_audit_required IN VARCHAR2
243 )
244 IS
245 --------------------------------------------------------------------------------
246
247 L_API_VERSION CONSTANT NUMBER := 1.0;
248 L_API_NAME CONSTANT VARCHAR2(30) := 'PURGE_SERVICEREQUESTS';
249 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
250 L_LOG_MODULE CONSTANT VARCHAR2(255):= 'cs.plsql.' || L_API_NAME_FULL || '.';
251
252 -- PL/SQL table defined to hold the ids of the child
253 -- concurrent requests. This will be used to check
254 -- their statuses in a loop.
255
256 TYPE t_worker_conc_req_arr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
257
258 L_EXC_PURGE_WARNING EXCEPTION;
259
260 x_msg_index_out NUMBER;
261 x_msg_count NUMBER;
262 x_msg_data VARCHAR2(1000);
263 x_return_status VARCHAR2(1);
264
265 -- Request id of the current
266 -- concurrent request.
267
268 l_request_id NUMBER;
269
270 -- Request data used to identify if the concurrent
271 -- request is started for the first time or if it
272 -- is resumed from a PAUSED state.
273
274 l_request_data VARCHAR2(1);
275
276 -- variables defined for holding the validated
277 -- value of the dates that are received as
278 -- VARCHARs from the concurrent program UI
279
280 l_creation_from_date DATE;
281 l_creation_to_date DATE;
282 l_last_update_from_date DATE;
283 l_last_update_to_date DATE;
284
285 l_row_count NUMBER;
286 l_ret BOOLEAN;
287
288 -- Actual number of worker concurrent requests
289 -- to be started based on the number of SRs in
290 -- the purgeset.
291
292 l_number_of_workers NUMBER := p_number_of_workers;
293
294 -- Table of request ids of the worker concurrent request
295
296 l_worker_conc_req_arr t_worker_conc_req_arr;
297
298 -- Variables holding the status information of each
299 -- worker concurrent request
300
301 l_worker_conc_req_phase VARCHAR2(100);
305 l_worker_conc_req_message VARCHAR2(512);
302 l_worker_conc_req_status VARCHAR2(100);
303 l_worker_conc_req_dev_phase VARCHAR2(100);
304 l_worker_conc_req_dev_status VARCHAR2(100);
306
307 -- Variables holding the status information of
308 -- the parent concurrent request
309
310 l_main_conc_req_phase VARCHAR2(100);
311 l_main_conc_req_status VARCHAR2(100);
312 l_main_conc_req_dev_phase VARCHAR2(100);
313 l_main_conc_req_dev_status VARCHAR2(100);
314 l_main_conc_req_message VARCHAR2(512);
315 l_child_message VARCHAR2(4000);
316
317 CURSOR c_child_request
318 (
319 c_request_id NUMBER
320 )
321 IS
322 SELECT
323 request_id
324 FROM
325 fnd_concurrent_requests
326 WHERE
327 parent_request_id = c_request_id;
328
329 BEGIN
330
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
334 THEN
335 FND_LOG.String
336 (
337 FND_LOG.level_procedure
338 , L_LOG_MODULE || 'start_time'
339 , 'The start time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
340 );
341 FND_LOG.String
342 (
343 FND_LOG.level_procedure
344 , L_LOG_MODULE || 'start'
345 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
346 );
347 FND_LOG.String
348 (
349 FND_LOG.level_procedure
350 , L_LOG_MODULE || 'param 1'
351 , 'errbuf:' || errbuf
352 );
353 FND_LOG.String
354 (
355 FND_LOG.level_procedure
356 , L_LOG_MODULE || 'param 2'
357 , 'errcode:' || errcode
358 );
359 FND_LOG.String
360 (
361 FND_LOG.level_procedure
362 , L_LOG_MODULE || 'param 3'
363 , 'p_api_version_number:' || p_api_version_number
364 );
365 FND_LOG.String
366 (
367 FND_LOG.level_procedure
368 , L_LOG_MODULE || 'param 4'
369 , 'p_init_msg_list:' || p_init_msg_list
370 );
371 FND_LOG.String
372 (
373 FND_LOG.level_procedure
374 , L_LOG_MODULE || 'param 5'
375 , 'p_commit:' || p_commit
376 );
377 FND_LOG.String
378 (
379 FND_LOG.level_procedure
380 , L_LOG_MODULE || 'param 6'
381 , 'p_validation_level:' || p_validation_level
382 );
383 FND_LOG.String
384 (
385 FND_LOG.level_procedure
386 , L_LOG_MODULE || 'param 7'
387 , 'p_incident_id:' || p_incident_id
388 );
389 FND_LOG.String
390 (
391 FND_LOG.level_procedure
392 , L_LOG_MODULE || 'param 8'
393 , 'p_incident_status_id:' || p_incident_status_id
394 );
395 FND_LOG.String
396 (
397 FND_LOG.level_procedure
398 , L_LOG_MODULE || 'param 9'
399 , 'p_incident_type_id:' || p_incident_type_id
400 );
401 FND_LOG.String
402 (
403 FND_LOG.level_procedure
404 , L_LOG_MODULE || 'param 10'
405 , 'p_creation_from_date:' || p_creation_from_date
406 );
407 FND_LOG.String
408 (
409 FND_LOG.level_procedure
410 , L_LOG_MODULE || 'param 11'
411 , 'p_creation_to_date:' || p_creation_to_date
412 );
413 FND_LOG.String
414 (
415 FND_LOG.level_procedure
416 , L_LOG_MODULE || 'param 12'
417 , 'p_last_update_from_date:' || p_last_update_from_date
418 );
419 FND_LOG.String
420 (
421 FND_LOG.level_procedure
422 , L_LOG_MODULE || 'param 13'
423 , 'p_last_update_to_date:' || p_last_update_to_date
424 );
425 FND_LOG.String
426 (
427 FND_LOG.level_procedure
428 , L_LOG_MODULE || 'param 14'
429 , 'p_not_updated_since:' || p_not_updated_since
430 );
431 FND_LOG.String
432 (
433 FND_LOG.level_procedure
434 , L_LOG_MODULE || 'param 15'
435 , 'p_customer_id:' || p_customer_id
436 );
437 FND_LOG.String
438 (
439 FND_LOG.level_procedure
440 , L_LOG_MODULE || 'param 16'
441 , 'p_customer_acc_id:' || p_customer_acc_id
442 );
443 FND_LOG.String
444 (
445 FND_LOG.level_procedure
446 , L_LOG_MODULE || 'param 17'
447 , 'p_item_category_id:' || p_item_category_id
448 );
449 FND_LOG.String
450 (
451 FND_LOG.level_procedure
452 , L_LOG_MODULE || 'param 18'
453 , 'p_inventory_item_id:' || p_inventory_item_id
454 );
455 FND_LOG.String
456 (
457 FND_LOG.level_procedure
458 , L_LOG_MODULE || 'param 19'
459 , 'p_history_size:' || p_history_size
460 );
461 FND_LOG.String
462 (
463 FND_LOG.level_procedure
464 , L_LOG_MODULE || 'param 20'
465 , 'p_number_of_workers:' || p_number_of_workers
466 );
467 FND_LOG.String
468 (
469 FND_LOG.level_procedure
470 , L_LOG_MODULE || 'param 21'
471 , 'p_purge_batch_size:' || p_purge_batch_size
472 );
473 FND_LOG.String
474 (
475 FND_LOG.level_procedure
476 , L_LOG_MODULE || 'param 22'
477 , 'p_purge_source_with_open_task:' || p_purge_source_with_open_task
478 );
479 FND_LOG.String
480 (
481 FND_LOG.level_procedure
485 END IF ;
482 , L_LOG_MODULE || 'param 23'
483 , 'p_audit_required:' || p_audit_required
484 );
486
487 IF NOT FND_API.Compatible_API_Call
488 (
489 L_API_VERSION
490 , p_api_version_number
491 , L_API_NAME
492 , g_pkg_name
493 )
494 THEN
495 FND_MSG_PUB.Count_And_Get
496 (
497 p_count => x_msg_count
498 , p_data => x_msg_data
499 );
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 END IF ;
502
503 IF FND_API.to_Boolean(p_init_msg_list)
504 THEN
505 FND_MSG_PUB.initialize;
506 END IF ;
507
508 ---
509
510 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
511 THEN
512 FND_LOG.String
513 (
514 FND_LOG.level_statement
515 , L_LOG_MODULE || 'get_request_info_start'
516 , 'Getting Current Concurrent Request ID '
517 );
518 END IF;
519
520 -- preserving this concurrent request's
521 -- request_id in a local variable
522
523 l_request_id := fnd_global.conc_request_id;
524 l_request_data := fnd_conc_global.request_data;
525
526 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
527 THEN
528 FND_LOG.String
529 (
530 FND_LOG.level_statement
531 , L_LOG_MODULE || 'get_request_info_end'
532 , 'After Getting Current Concurrent Request ID ' || l_request_id ||
533 '(' || NVL(l_request_data, 'NULL') || ')'
534 );
535 END IF;
536
537 ---
538
539 IF l_request_data IS NULL
540
541 -- This portion of the code is executed when the concurrent request is
542 -- invokedby the user. This time, the request data is NULL indicating
543 -- that the request is started newly.
544
545 THEN
546
547 ----------------------------------------------------------------------------
548 -- Cleanup process: Delete all the rows in the staging table corresponding
549 -- to completed concurrent programs that have been left behind by an earlier
550 -- execution of this concurrent program.
551 ----------------------------------------------------------------------------
552
553 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
554 THEN
555 FND_LOG.String
556 (
557 FND_LOG.level_statement
558 , L_LOG_MODULE || 'cleanup_start'
559 , 'deleting rows in staging table that were not cleared earlier'
560 );
561 END IF;
562
563 DELETE cs_incidents_purge_staging
564 WHERE
565 concurrent_request_id IN
566 (
567 SELECT
568 request_id
569 FROM
570 fnd_concurrent_requests r
571 , fnd_concurrent_programs p
572 WHERE
573 r.phase_code = 'C'
574 AND p.concurrent_program_id = r.concurrent_program_id
575 AND p.concurrent_program_name = 'CSSRPGP'
576 AND p.application_id = 170
577 );
578
579 l_row_count := SQL%ROWCOUNT;
580
581 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
582 THEN
583 FND_LOG.String
584 (
585 FND_LOG.level_statement
586 , L_LOG_MODULE || 'cleanup_end'
587 , 'after deleting rows in staging table that were not cleared earlier '
588 || l_row_count || ' rows'
589 );
590 END IF;
591
592 -- Committing the changes in order to make
593 -- the rows unavailable to all sessions.
594
595 COMMIT;
596
597 ----------------------------------------------------------------------------
598 -- Purge Parameter Validations:
599 ----------------------------------------------------------------------------
600
601 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
602 THEN
603 FND_LOG.String
604 (
605 FND_LOG.level_statement
606 , L_LOG_MODULE || 'call_validate_param_start'
607 , 'Calling procedure to validate purge parameters'
608 );
609 END IF;
610
611 -- calling a private procedure to perform validations on all the
612 -- purge parameters and throw corresponding exceptions in case
613 -- there are any errors
614
615 Validate_Purge_Params
616 (
617 p_incident_id => p_incident_id
618 , p_incident_status_id => p_incident_status_id
619 , p_incident_type_id => p_incident_type_id
620 , p_creation_from_date => p_creation_from_date
621 , p_creation_to_date => p_creation_to_date
622 , p_last_update_from_date => p_last_update_from_date
623 , p_last_update_to_date => p_last_update_to_date
624 , p_not_updated_since => p_not_updated_since
625 , p_customer_id => p_customer_id
626 , p_customer_acc_id => p_customer_acc_id
627 , p_item_category_id => p_item_category_id
628 , p_inventory_item_id => p_inventory_item_id
629 , p_history_size => p_history_size
630 , p_number_of_workers => p_number_of_workers
631 , p_purge_batch_size => p_purge_batch_size
632 , p_purge_source_with_open_task => p_purge_source_with_open_task
633 , p_audit_required => p_audit_required
634 , x_creation_from_date => l_creation_from_date
638 , x_msg_count => x_msg_count
635 , x_creation_to_date => l_creation_to_date
636 , x_last_update_from_date => l_last_update_from_date
637 , x_last_update_to_date => l_last_update_to_date
639 , x_msg_data => x_msg_data
640 );
641
642 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
643 THEN
644 FND_LOG.String
645 (
646 FND_LOG.level_statement
647 , L_LOG_MODULE || 'call_validate_param_end'
648 , 'After calling procedure to validate purge parameters'
649 );
650 END IF;
651
652 ---
653
654 ----------------------------------------------------------------------------
655 -- Preparation of Staging Table Data and Submission of Child Requests
656 ----------------------------------------------------------------------------
657
658 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
659 THEN
660 FND_LOG.String
661 (
662 FND_LOG.level_statement
663 , L_LOG_MODULE || 'call_form_and_exec_statement_start'
664 , 'Calling procedure to form and execute statement to fill staging table'
665 );
666 END IF;
667
668 -- Calling the procedure to form an SQL statement
669 -- that will insert the rows that need to be purged
670 -- as per the parameters passed by the user, into
671 -- the Staging table.
672
673 Form_And_Exec_Statement
674 (
675 p_incident_id => p_incident_id
676 , p_incident_status_id => p_incident_status_id
680 , p_last_update_from_date => l_last_update_from_date
677 , p_incident_type_id => p_incident_type_id
678 , p_creation_from_date => l_creation_from_date
679 , p_creation_to_date => l_creation_to_date
681 , p_last_update_to_date => l_last_update_to_date
682 , p_customer_id => p_customer_id
683 , p_customer_acc_id => p_customer_acc_id
684 , p_item_category_id => p_item_category_id
685 , p_inventory_item_id => p_inventory_item_id
686 , p_history_size => p_history_size
687 , p_number_of_workers => l_number_of_workers
688 , p_purge_batch_size => p_purge_batch_size
689 , p_request_id => l_request_id
690 , p_row_count => l_row_count
691 );
692
693 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
694 THEN
695 FND_LOG.String
696 (
697 FND_LOG.level_statement
698 , L_LOG_MODULE || 'call_form_and_exec_statement_end'
699 , 'After calling procedure to form and execute statement to '
700 || 'fill staging table ' || l_row_count
701 );
702 END IF;
703
704 ---
705
706 IF l_row_count = 0
707 THEN
708
709 -- If there were no SRs selected to be purged, return
710 -- from the concurrent program with a warning
711
712 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
713 THEN
714 FND_LOG.String
715 (
716 FND_LOG.level_unexpected
717 , L_LOG_MODULE || 'no_rows_to_purge'
718 , 'There were no rows to purge. Row count was ' || l_row_count
719 );
720 END IF ;
721
722 FND_MESSAGE.Set_Name('CS', 'CS_SR_NO_SRS_TO_PURGE');
723 FND_MSG_PUB.ADD;
724
725 RAISE L_EXC_PURGE_WARNING;
726 END IF;
727
728 ---
729
730 -- Start worker concurrent programs: Here if the number of
731 -- SRs to be purged is lesser than the no.of workers, only
732 -- one worker is started per SR. This is an optimization for
733 -- Case management.
734
735 FOR
736 j IN 1..l_number_of_workers
737 LOOP
738 l_worker_conc_req_arr(j) := FND_REQUEST.Submit_Request
739 (
740 application => 'CS'
741 , program => 'CSSRPGW'
742 , description => TO_CHAR(j)
743 , start_time => NULL
744 , sub_request => TRUE
745 , argument1 => p_api_version_number
746 , argument2 => p_init_msg_list
747 , argument3 => p_commit
748 , argument4 => p_validation_level
749 , argument5 => j -- p_worker_id
750 , argument6 => p_purge_batch_size
751 , argument7 => l_request_id -- p_purge_set_id
752 , argument8 => p_purge_source_with_open_task
753 , argument9 => p_audit_required
754 );
755
756 IF
757 l_worker_conc_req_arr(j) = 0
758 THEN
759 -- If the worker request was not created successfully
760 -- raise an unexpected exception and terminate the
761 -- process.
762
763 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
764 THEN
765 FND_LOG.String
766 (
767 FND_LOG.level_unexpected
768 , L_LOG_MODULE || 'create_workers_error'
769 , 'Failed while starting worker concurrent request'
770 );
771 END IF;
772
773 FND_MESSAGE.Set_Name('CS', 'CS_SR_SUBMIT_CHILD_FAILED');
774 FND_MSG_PUB.ADD;
775
776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777 END IF;
778
779 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
780 THEN
781 FND_LOG.String
782 (
783 FND_LOG.level_statement
784 , L_LOG_MODULE || 'create_workers_doing'
785 , 'After starting worker ' || l_worker_conc_req_arr(j)
786 );
787 END IF;
788 END LOOP;
789
790 -- Committing so that the worker concurrent program that
791 -- was submitted above is started by the concurrent manager.
792
793 COMMIT;
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 || 'create_workers_end'
801 , 'After starting all worker concurrent requests'
802 );
803 END IF;
804
805 ---
806
807 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
808 THEN
809 FND_LOG.String
810 (
811 FND_LOG.level_statement
812 , L_LOG_MODULE || 'move_parent_to_paused_start'
813 , 'Moving parent concurrent request to paused status'
814 );
815 END IF;
816
817 -- Moving the parent concurrent request to Paused
818 -- status in order to start the child
819
820 fnd_conc_global.set_req_globals
821 (
822 conc_status => 'PAUSED'
823 , request_data => '1'
824 );
825
826 COMMIT;
827
828 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
829 THEN
830 FND_LOG.String
831 (
832 FND_LOG.level_statement
833 , L_LOG_MODULE || 'move_parent_to_paused_end'
834 , 'After moving parent concurrent request to paused status'
835 );
839 -- first time, gets over. Here the parent request is moved to a
836 END IF;
837
838 -- At this point, execution of the parent request, invoked for the
840 -- paused status after which the procedure execution ends.
841
842 ELSIF l_request_data IS NOT NULL
843
844 -- If the concurrent request is restarted from the PAUSED state,
845 -- this portion of the code is executed. When all the child
846 -- requests have completed their work, (their PHASE_CODE
847 -- is 'COMPLETED') the concurrent manager restarts the parent. This
848 -- time, the request_data returns a Non NULL value and so this
849 -- portion of the code is executed.
850
851 THEN
852
853 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
854 THEN
855 FND_LOG.String
856 (
857 FND_LOG.level_statement
858 , L_LOG_MODULE || 'collect_child_status_start'
859 , 'Collecting child completion status'
860 );
861 END IF;
862
863 l_main_conc_req_dev_status := 'NORMAL';
864
865 -- check status of worker concurrent request
866 -- to arrive at the parent request's
867 -- completion status
868
869 FOR r_child_request IN c_child_request(l_request_id)
870 LOOP
871 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
872 THEN
873 FND_LOG.String
874 (
875 FND_LOG.level_statement
876 , L_LOG_MODULE || 'collect_a_child_status'
877 , 'Worker Concurrent Request No : ' || r_child_request.request_id
878 );
879 END IF;
880
881 IF FND_CONCURRENT.Get_Request_Status
882 (
883 request_id => r_child_request.request_id
884 , phase => l_worker_conc_req_phase
885 , status => l_worker_conc_req_status
886 , dev_phase => l_worker_conc_req_dev_phase
887 , dev_status => l_worker_conc_req_dev_status
888 , message => l_worker_conc_req_message
889 )
890 THEN
891 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
892 THEN
893 FND_LOG.String
894 (
895 FND_LOG.level_statement
896 , L_LOG_MODULE || 'child_return_status'
897 , 'l_worker_conc_req_phase:' || l_worker_conc_req_phase
898 );
899 FND_LOG.String
900 (
901 FND_LOG.level_statement
902 , L_LOG_MODULE || 'child_return_status'
903 , 'l_worker_conc_req_status:' || l_worker_conc_req_status
904 );
905 FND_LOG.String
906 (
907 FND_LOG.level_statement
908 , L_LOG_MODULE || 'child_return_status'
909 , 'l_worker_conc_req_dev_phase:' || l_worker_conc_req_dev_phase
910 );
911 FND_LOG.String
912 (
913 FND_LOG.level_statement
914 , L_LOG_MODULE || 'child_return_status'
915 , 'l_worker_conc_req_dev_status:' || l_worker_conc_req_dev_status
916 );
917 FND_LOG.String
918 (
919 FND_LOG.level_statement
920 , L_LOG_MODULE || 'child_return_status'
921 , 'l_worker_conc_req_message:' || l_worker_conc_req_message
922 );
923 END IF;
924
925 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
926 THEN
927 FND_LOG.String
928 (
929 FND_LOG.level_statement
930 , L_LOG_MODULE || 'resolve_main_dev_status_start'
931 , 'Resolving l_main_conc_req_dev_status'
932 );
933 END IF;
934
935 -- If the current worker has completed its work, based
936 -- on the return status of the worker, mark the completion
937 -- status of the main concurrent request.
938
939 IF l_worker_conc_req_dev_status <> 'NORMAL'
940 THEN
941 IF l_main_conc_req_dev_status IN ('WARNING', 'NORMAL')
942 AND l_worker_conc_req_dev_status IN ('ERROR', 'DELETED', 'TERMINATED')
943 THEN
944 l_main_conc_req_dev_status := 'ERROR';
945 l_child_message := l_worker_conc_req_message;
946 ELSIF l_main_conc_req_dev_status = 'NORMAL'
947 AND l_worker_conc_req_dev_status = 'WARNING'
948 THEN
949 l_main_conc_req_dev_status := 'WARNING';
950 l_child_message := l_worker_conc_req_message;
951 END IF;
952 END IF;
953
954 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
955 THEN
956 FND_LOG.String
957 (
958 FND_LOG.level_statement
959 , L_LOG_MODULE || 'resolve_main_dev_status_end'
960 , 'After resolving l_main_conc_req_dev_status:'
961 || l_main_conc_req_dev_status
962 );
963 FND_LOG.String
964 (
965 FND_LOG.level_statement
966 , L_LOG_MODULE || 'resolve_main_dev_status_end'
967 , 'After resolving l_main_conc_req_dev_status - child_message :'
968 || l_child_message
969 );
970 END IF;
971
972 ELSE
973
974 -- There was a failure while collecting a child request
975 -- status, raising an unexpected exception
976
977 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
978 THEN
979 FND_LOG.String
980 (
984 || l_main_conc_req_message
981 FND_LOG.level_unexpected
982 , L_LOG_MODULE || 'collect_child_status_failed'
983 , 'Call to function fnd_concurrent.get_request_status failed. '
985 );
986 END IF;
987
988 FND_MESSAGE.Set_Name('CS', 'CS_SR_GET_CHILD_STAT_FAILED');
989 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
990 FND_MSG_PUB.ADD;
991
992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
993 END IF;
994 END LOOP;
995
996 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
997 THEN
998 FND_LOG.String
999 (
1000 FND_LOG.level_statement
1001 , L_LOG_MODULE || 'collect_child_status_end'
1002 , 'After collecting child completion status'
1003 );
1004 END IF;
1005
1006 ---
1007
1008 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1009 THEN
1010 FND_LOG.String
1011 (
1012 FND_LOG.level_statement
1013 , L_LOG_MODULE || 'write_purge_output_start'
1014 , 'Calling procedure to write_purge_output'
1015 );
1016 END IF;
1017
1018 -- Write the details of no. of SRs picked up for purge,
1019 -- number of SRs purged successfully, no. of SRs failed
1020 -- during validation and a list of these SRs along with
1021 -- the cause of failure.
1022
1023 Write_Purge_Output
1024 (
1025 p_purge_batch_size => p_purge_batch_size
1026 , p_request_id => l_request_id
1027 );
1028
1029 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1030 THEN
1031 FND_LOG.String
1032 (
1033 FND_LOG.level_statement
1034 , L_LOG_MODULE || 'write_purge_output_end'
1035 , 'After calling procedure to write_purge_output'
1036 );
1037 END IF;
1038
1039 ---
1040
1041 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1042 THEN
1043 FND_LOG.String
1044 (
1045 FND_LOG.level_statement
1046 , L_LOG_MODULE || 'staging_table_cleanup_start'
1047 , 'Cleaning up staging table'
1048 );
1049 END IF;
1050
1051 -- Cleaning up the staging table
1052
1053 DELETE cs_incidents_purge_staging
1054 WHERE
1055 concurrent_request_id = l_request_id;
1056
1057 l_row_count := SQL%ROWCOUNT;
1058
1059 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1060 THEN
1061 FND_LOG.String
1062 (
1063 FND_LOG.level_statement
1064 , L_LOG_MODULE || 'staging_table_cleanup_end'
1065 , 'After cleaning up staging table ' || l_row_count
1066 );
1067 END IF;
1068
1069 ---
1070
1071 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1072 THEN
1073 FND_LOG.String
1074 (
1075 FND_LOG.level_statement
1076 , L_LOG_MODULE || 'act_on_ret_status_start'
1077 , 'Acting on the main concurrent request return status:'
1078 || l_main_conc_req_dev_status
1079 );
1080 END IF;
1081
1082 -- Set the completion status of the main concurrent request
1083 -- by raising corresponding exceptions.
1084
1085 IF l_main_conc_req_dev_status = 'WARNING'
1086 THEN
1087 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_RET_STAT');
1088 FND_MSG_PUB.ADD;
1089
1090 RAISE L_EXC_PURGE_WARNING;
1091 ELSIF l_main_conc_req_dev_status = 'ERROR'
1092 THEN
1093 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_RET_STAT');
1094 FND_MSG_PUB.ADD;
1095
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098
1099 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1100 THEN
1101 FND_LOG.String
1102 (
1103 FND_LOG.level_statement
1104 , L_LOG_MODULE || 'act_on_ret_status_end'
1105 , 'after Acting on the main concurrent request return status:'
1106 || l_main_conc_req_dev_status
1107 );
1108 END IF;
1109
1110 ---
1111
1112 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1113 THEN
1114 FND_LOG.String
1115 (
1116 FND_LOG.level_statement
1117 , L_LOG_MODULE || 'set_comp_stat_normal_start'
1118 , 'Setting completion status for parent concurrent request as NORMAL'
1119 );
1120 END IF;
1121
1122 -- Setting the completion status of this concurrent
1123 -- request as COMPLETED NORMALLY
1124
1125 l_ret := fnd_concurrent.set_completion_status
1126 (
1127 'NORMAL'
1128 , ' '
1129 );
1130
1131 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1132 THEN
1133 FND_LOG.String
1134 (
1135 FND_LOG.level_statement
1136 , L_LOG_MODULE || 'set_comp_stat_normal_end'
1137 , 'After setting completion status for parent concurrent '
1138 || 'request as NORMAL'
1139 );
1140 END IF;
1141
1142 -- At this point, execution of the concurrent program
1143 -- that is restarted from the paused state is completed.
1144
1145 END IF;
1146
1147 ---
1148
1149 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1150 THEN
1151 IF FND_CONCURRENT.Get_Request_Status
1152 (
1153 request_id => l_request_id
1154 , phase => l_main_conc_req_phase
1155 , status => l_main_conc_req_status
1156 , dev_phase => l_main_conc_req_dev_phase
1160 THEN
1157 , dev_status => l_main_conc_req_dev_status
1158 , message => l_main_conc_req_message
1159 )
1161 FND_LOG.String
1162 (
1163 FND_LOG.level_procedure
1164 , L_LOG_MODULE || 'request_status_1'
1165 , 'l_main_conc_req_phase:' || l_main_conc_req_phase
1166 );
1167 FND_LOG.String
1168 (
1169 FND_LOG.level_procedure
1170 , L_LOG_MODULE || 'request_status_2'
1171 , 'l_main_conc_req_status:' || l_main_conc_req_status
1172 );
1173 FND_LOG.String
1174 (
1175 FND_LOG.level_procedure
1176 , L_LOG_MODULE || 'request_status_3'
1177 , 'l_main_conc_req_dev_phase:' || l_main_conc_req_dev_phase
1178 );
1179 FND_LOG.String
1180 (
1181 FND_LOG.level_procedure
1182 , L_LOG_MODULE || 'request_status_4'
1183 , 'l_main_conc_req_dev_status:' || l_main_conc_req_dev_status
1184 );
1185 FND_LOG.String
1186 (
1187 FND_LOG.level_procedure
1188 , L_LOG_MODULE || 'request_status_5'
1189 , 'l_main_conc_req_message:' || l_main_conc_req_message
1190 );
1191 END IF;
1192 END IF ;
1193
1194 ---
1195
1196 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1197 THEN
1198 FND_LOG.String
1199 (
1200 FND_LOG.level_procedure
1201 , L_LOG_MODULE || 'end'
1202 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
1203 || x_return_status
1204 );
1205 FND_LOG.String
1206 (
1207 FND_LOG.level_procedure
1208 , L_LOG_MODULE || 'end_time'
1209 , 'The end time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1210 );
1211 END IF ;
1212
1213 EXCEPTION
1214
1215 WHEN L_EXC_PURGE_WARNING THEN
1216 x_return_status := FND_API.G_RET_STS_ERROR;
1217
1218 -- setting the completion status as WARNING since
1219 -- there was a warning in the execution of this
1220 -- request.
1221
1222 x_msg_count := FND_MSG_PUB.Count_Msg;
1223 IF x_msg_count > 0
1224 THEN
1225 FND_MSG_PUB.Get
1226 (
1227 p_msg_index => 1
1228 , p_encoded => 'F'
1229 , p_data => x_msg_data
1230 , p_msg_index_out => x_msg_index_out
1231 );
1232 END IF;
1233
1234 l_ret := fnd_concurrent.set_completion_status
1235 (
1236 'WARNING'
1237 , SUBSTR(x_msg_data, 1, 240)
1238 );
1239
1240 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1241 THEN
1242 FND_LOG.String
1243 (
1244 FND_LOG.level_unexpected
1245 , L_LOG_MODULE || 'error'
1246 , 'Inside WHEN L_EXC_PURGE_WARNING of ' || L_API_NAME_FULL
1247 );
1248
1249 x_msg_count := FND_MSG_PUB.Count_Msg;
1250
1251 IF x_msg_count > 0
1252 THEN
1253 FOR
1254 i IN 1..x_msg_count
1255 LOOP
1256 FND_MSG_PUB.Get
1257 (
1258 p_msg_index => i
1259 , p_encoded => 'F'
1260 , p_data => x_msg_data
1261 , p_msg_index_out => x_msg_index_out
1262 );
1263 FND_LOG.String
1264 (
1265 FND_LOG.level_unexpected
1266 , L_LOG_MODULE || 'error'
1267 , 'Error encountered is : ' || x_msg_data || ' [Index:'
1268 || x_msg_index_out || ']'
1269 );
1270 END LOOP;
1271 END IF ;
1272 END IF ;
1273
1274 WHEN FND_API.G_EXC_ERROR THEN
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276
1277 -- setting the completion status as ERROR since
1278 -- there was an error in the execution of this
1279 -- request.
1280
1281 x_msg_count := FND_MSG_PUB.Count_Msg;
1282 IF x_msg_count > 0
1283 THEN
1284 FND_MSG_PUB.Get
1285 (
1286 p_msg_index => 1
1287 , p_encoded => 'F'
1288 , p_data => x_msg_data
1289 , p_msg_index_out => x_msg_index_out
1290 );
1291 END IF;
1292
1293 l_ret := fnd_concurrent.set_completion_status
1294 (
1295 'ERROR'
1296 , SUBSTR(x_msg_data, 1, 240)
1297 );
1298
1299 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1300 THEN
1301 FND_LOG.String
1302 (
1303 FND_LOG.level_unexpected
1304 , L_LOG_MODULE || 'error'
1305 , 'Inside WHEN FND_API.G_EXC_ERROR of ' || L_API_NAME_FULL
1306 );
1307
1308 x_msg_count := FND_MSG_PUB.Count_Msg;
1309
1310 IF x_msg_count > 0
1311 THEN
1312 FOR
1313 i IN 1..x_msg_count
1314 LOOP
1315 FND_MSG_PUB.Get
1316 (
1317 p_msg_index => i
1318 , p_encoded => 'F'
1319 , p_data => x_msg_data
1320 , p_msg_index_out => x_msg_index_out
1321 );
1322 FND_LOG.String
1323 (
1324 FND_LOG.level_unexpected
1325 , L_LOG_MODULE || 'error'
1326 , 'Error encountered is : ' || x_msg_data || ' [Index:'
1327 || x_msg_index_out || ']'
1328 );
1329 END LOOP;
1330 END IF ;
1331 END IF ;
1332
1333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335
1336 -- setting the completion status as ERROR since
1337 -- there was an unexpected error in the execution
1341 IF x_msg_count > 0
1338 -- of this request.
1339
1340 x_msg_count := FND_MSG_PUB.Count_Msg;
1342 THEN
1343 FND_MSG_PUB.Get
1344 (
1345 p_msg_index => 1
1346 , p_encoded => 'F'
1347 , p_data => x_msg_data
1348 , p_msg_index_out => x_msg_index_out
1349 );
1350 END IF;
1351
1352 l_ret := fnd_concurrent.set_completion_status
1353 (
1354 'ERROR'
1355 , SUBSTR(x_msg_data, 1, 240)
1356 );
1357
1358 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1359 THEN
1360 FND_LOG.String
1361 (
1362 FND_LOG.level_unexpected
1363 , L_LOG_MODULE || 'unexpected_error'
1364 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
1365 );
1366
1367 x_msg_count := FND_MSG_PUB.Count_Msg;
1368
1369 IF x_msg_count > 0
1370 THEN
1371 FOR
1372 i IN 1..x_msg_count
1373 LOOP
1374 FND_MSG_PUB.Get
1375 (
1376 p_msg_index => i
1377 , p_encoded => 'F'
1378 , p_data => x_msg_data
1379 , p_msg_index_out => x_msg_index_out
1380 );
1381 FND_LOG.String
1382 (
1383 FND_LOG.level_unexpected
1384 , L_LOG_MODULE || 'unexpected_error'
1385 , 'Error encountered is : ' || x_msg_data || ' [Index:'
1386 || x_msg_index_out || ']'
1387 );
1388 END LOOP;
1389 END IF ;
1390 END IF ;
1391
1392 WHEN OTHERS THEN
1393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1394
1395 FND_MESSAGE.Set_Name('CS', 'CS_SR_PURG_MAIN_FAIL');
1396 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
1397 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
1398 FND_MSG_PUB.ADD;
1399
1400 -- setting the completion status as ERROR since
1401 -- there was an unexpected error in the execution
1402 -- of this request.
1403
1404 x_msg_count := FND_MSG_PUB.Count_Msg;
1405 IF x_msg_count > 0
1406 THEN
1407 FND_MSG_PUB.Get
1408 (
1409 p_msg_index => 1
1410 , p_encoded => 'F'
1411 , p_data => x_msg_data
1412 , p_msg_index_out => x_msg_index_out
1413 );
1414 END IF;
1415
1416 l_ret := fnd_concurrent.set_completion_status
1417 (
1418 'ERROR'
1419 , SUBSTR(x_msg_data, 1, 240)
1420 );
1421
1422 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1423 THEN
1424 FND_LOG.String
1425 (
1426 FND_LOG.level_unexpected
1427 , L_LOG_MODULE || 'when_others'
1428 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
1429 );
1430 FND_LOG.String
1431 (
1432 FND_LOG.level_unexpected
1433 , L_LOG_MODULE || 'when_others'
1434 , SQLERRM
1435 );
1436 END IF ;
1437
1438 END Purge_ServiceRequests;
1439
1440 --------------------------------------------------------------------------------
1441 -- Procedure Name : PURGE_SR_WORKER
1442 --
1443 -- Parameters (other than standard ones)
1444 --
1445 -- IN OUT
1446 -- errbuf : This parameter is not used but is a standard
1447 -- parameter for concurrent program procedures.
1448 -- The function fnd_concurrent.set_completion_status
1449 -- is called instead.
1450 -- errcode : This parameter is not used but is a standard
1451 -- parameter for concurrent program procedures.
1452 -- The function fnd_concurrent.set_completion_status
1453 -- is called instead.
1454 --
1455 -- IN
1456 -- p_worker_id : The number assigned to this worker which enables
1457 -- the worker concurrent program to identify the SRs
1458 -- in the staging table that it needs to purge
1459 -- p_purge_set_id : The concurrent request id of the parent concurrent
1460 -- request. This is used in addition to the worker id
1461 -- to identify the SRs in the staging table that need
1462 -- to be purged.
1463 -- p_purge_batch_size : Number of SRs that need to be processed in one
1464 -- call to the SR Delete API. At any point in time, a
1465 -- maximum of batch_size number of rows will be inserted
1466 -- into the table JTF_OBJECT_PURGE_PARAM_TMP, which will
1467 -- be picked up by the SR Delete API to purge SRs.
1468 -- p_purge_source_with_open_task :
1469 -- This signifies if the Tasks Validation API can delete
1470 -- tasks that are open. If this is N, only SRs linked to
1471 -- closed Tasks are allowed to be purged. If this is Y,
1472 -- all SRs, irrespective of whether the Tasks linked to
1473 -- them are open or closed, can be deleted.
1474 -- p_audit_required : This indicates if the SR Delete API should write the
1475 -- purge audit information. If this is N, no rows are
1476 -- inserted into the table CS_INCIDENTS_PURGE_AUDIT_B and
1477 -- TL. If this is Y, audit rows are inserted into these
1478 -- tables.
1479 -- Description
1480 -- This procedure is invoked by the procedure cs_sr_purge_cp.
1481 -- purge_servicerequests as child concurrent requests using an API
1482 -- fnd_request.submit_request. It reads the staging table filled by
1483 -- purge_servicerequests with the purge_set_id and worker_id
1487 -- this procedure may be running in parallel since the Purge Concurrent
1484 -- in batches of size purge_batch_size through a cursor and bulk
1485 -- inserts these rows into the global temp table JTF_OBJECT_PURGE_PARAM_TMP
1486 -- and calls the SR Delete API. At any point in time, several copies of
1488 -- Program will generate multiple Worker Concurrent Programs
1489 -- based on its parameter no_of_workers.
1490 --
1491 -- HISTORY
1492 --
1493 ----------------+------------+--------------------------------------------------
1494 -- DATE | UPDATED BY | Change Description
1495 ----------------+------------+--------------------------------------------------
1496 -- 2-Aug_2005 | varnaray | Created
1497 -- | |
1498 ----------------+------------+--------------------------------------------------
1499 /*#
1500 * This procedure is invoked by the procedure cs_sr_purge_cp.
1501 * purge_servicerequests as child concurrent requests using an API
1502 * fnd_request.submit_request. It reads the staging table filled by
1503 * purge_servicerequests with the purge_set_id and worker_id
1504 * in batches of size purge_batch_size through a cursor and bulk inserts
1505 * these rows into the global temp table JTF_OBJECT_PURGE_PARAM_TMP and calls
1506 * the SR Delete API. At any point in time, several copies of this procedure
1507 * may be running in parallel since the Purge Concurrent Program will generate
1508 * multiple Worker Concurrent Programs based on its parameter no_of_workers.
1509 * @param errbuf This parameter is not used but is a standard parameter for
1510 * concurrent program procedures. The function fnd_concurrent.
1511 * set_completion_status is called instead.
1512 * @param errcode This parameter is not used but is a standard parameter
1513 * for concurrent program procedures. The function
1514 * fnd_concurrent.set_completion_status is called instead.
1515 * @param p_worker_id The number assigned to this worker which enables the
1516 * worker concurrent program to identify the SRs in the staging table that
1517 * it needs to purge
1518 * @param p_purge_set_id The concurrent request id of the parent concurrent
1519 * request. This is used in addition to the worker id to identify the SRs
1520 * in the staging table that need to be purged.
1521 * @param p_purge_batch_size Number of SRs that need to be processed in
1522 * one call to the SR Delete API. At any point in time, a maximum of
1523 * batch_size number of rows will be inserted into the table
1524 * JTF_OBJECT_PURGE_PARAM_TMP, which will be picked up by the SR Delete
1525 * API to purge SRs.
1526 * @param p_purge_source_with_open_task This signifies if the Tasks
1527 * Validation API can delete tasks that are open. If this is N, only SRs
1528 * linked to closed Tasks are allowed to be purged. If this is
1529 * Y, all SRs, irrespective of whether the Tasks linked to them are
1530 * open or closed, can be deleted.
1531 * @param p_audit_required This indicates if the SR Delete API should write
1532 * the purge audit information. If this is N, no rows are inserted into the
1533 * table CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y, audit rows
1534 * are inserted into these tables.
1535 * @rep:scope internal
1536 * @rep:product CS
1537 * @rep:displayname Purge Service Requests Worker Concurrent Program
1538 */
1539 PROCEDURE Purge_Sr_Worker
1540 (
1541 errbuf IN OUT NOCOPY VARCHAR2
1542 , errcode IN OUT NOCOPY INTEGER
1543 , p_api_version_number IN NUMBER
1544 , p_init_msg_list IN VARCHAR2
1545 , p_commit IN VARCHAR2
1546 , p_validation_level IN NUMBER
1547 , p_worker_id IN NUMBER
1548 , p_purge_batch_size IN NUMBER
1549 , p_purge_set_id IN NUMBER
1550 , p_purge_source_with_open_task IN VARCHAR2
1551 , p_audit_required IN VARCHAR2
1552 )
1553 IS
1554 --------------------------------------------------------------------------------
1555
1556 L_API_VERSION CONSTANT NUMBER := 1.0;
1557 L_API_NAME CONSTANT VARCHAR2(30) := 'PURGE_SR_WORKER';
1558 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
1559 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
1560
1561 x_msg_count NUMBER;
1562 x_msg_index_out NUMBER;
1563 x_msg_data VARCHAR2(1000);
1564 x_return_status VARCHAR2(1);
1565
1566 l_conc_req_phase VARCHAR2(100);
1567 l_conc_req_status VARCHAR2(100);
1568 l_conc_req_dev_phase VARCHAR2(100);
1569 l_conc_req_dev_status VARCHAR2(100);
1570 l_conc_req_message VARCHAR2(512);
1571
1572 l_request_id NUMBER;
1573
1574 l_processing_set_id NUMBER;
1575 l_row_count NUMBER;
1576 l_ret BOOLEAN;
1577 l_has_any_batch_failed BOOLEAN := FALSE;
1578
1579 l_message VARCHAR2(1000);
1580
1581 -- PL/SQL table to hold the incident_ids retrieved
1582 -- from the staging table, a batch at a time.
1583
1584 TYPE t_incident_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1585 l_incident_id_tbl t_incident_id_tbl;
1586
1587 -- PL/SQL table to hold the incident ids that had
1588 -- errors while performing validations with other
1589 -- products before purging the SRs. This table is
1590 -- used only when one of these procedures encountered
1591 -- an ORACLE EXCEPTION.
1592
1593 l_err_incident_id_tbl t_incident_id_tbl;
1594
1595 -- PL/SQL table to hold the error messages retrieved
1596 -- from the staging table when one of the procedures
1597 -- in the worker encounters an ORACLE EXCEPTION.
1598 -- This table is only used under these circumstances.
1599
1600 TYPE t_purge_error_message_tbl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1601 l_purge_error_message_tbl t_purge_error_message_tbl;
1602
1606 CURSOR c_staging IS
1603 -- Cursor to fetch SRs that need to
1604 -- be purged from the staging table
1605
1607 SELECT
1608 incident_id
1609 FROM
1610 cs_incidents_purge_staging
1611 WHERE
1612 worker_id = p_worker_id
1613 AND concurrent_request_id = p_purge_set_id
1614 AND purge_status IS NULL;
1615
1616 BEGIN
1617
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619
1620 -- capturing the request id of the
1621 -- worker thread into a local variable.
1622
1623 l_request_id := fnd_global.conc_request_id;
1624
1625 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1626 THEN
1627 FND_LOG.String
1628 (
1629 FND_LOG.level_procedure
1630 , L_LOG_MODULE || 'start_time'
1631 , 'The start time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1632 );
1633 FND_LOG.String
1634 (
1635 FND_LOG.level_procedure
1636 , L_LOG_MODULE || 'start'
1637 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
1638 );
1639 FND_LOG.String
1640 (
1641 FND_LOG.level_procedure
1642 , L_LOG_MODULE || 'param 1'
1643 , 'p_api_version_number:' || p_api_version_number
1644 );
1645 FND_LOG.String
1646 (
1647 FND_LOG.level_procedure
1648 , L_LOG_MODULE || 'param 2'
1649 , 'p_init_msg_list:' || p_init_msg_list
1650 );
1651 FND_LOG.String
1652 (
1653 FND_LOG.level_procedure
1654 , L_LOG_MODULE || 'param 3'
1655 , 'p_commit:' || p_commit
1656 );
1657 FND_LOG.String
1658 (
1659 FND_LOG.level_procedure
1660 , L_LOG_MODULE || 'param 4'
1661 , 'p_validation_level:' || p_validation_level
1662 );
1663 FND_LOG.String
1664 (
1665 FND_LOG.level_procedure
1666 , L_LOG_MODULE || 'param 5'
1667 , 'p_worker_id:' || p_worker_id
1668 );
1669 FND_LOG.String
1670 (
1671 FND_LOG.level_procedure
1672 , L_LOG_MODULE || 'param 6'
1673 , 'p_purge_set_id:' || p_purge_set_id
1674 );
1675 FND_LOG.String
1676 (
1677 FND_LOG.level_procedure
1678 , L_LOG_MODULE || 'param 7'
1679 , 'p_purge_batch_size:' || p_purge_batch_size
1680 );
1681 FND_LOG.String
1682 (
1683 FND_LOG.level_procedure
1684 , L_LOG_MODULE || 'param 8'
1685 , 'p_purge_source_with_open_task:' || p_purge_source_with_open_task
1686 );
1687 FND_LOG.String
1688 (
1689 FND_LOG.level_procedure
1690 , L_LOG_MODULE || 'param 9'
1691 , 'p_audit_required:' || p_audit_required
1692 );
1693 END IF ;
1694
1695 IF NOT FND_API.Compatible_API_Call
1696 (
1697 L_API_VERSION
1698 , p_api_version_number
1699 , L_API_NAME
1700 , g_pkg_name
1701 )
1702 THEN
1703 FND_MSG_PUB.Count_And_Get
1704 (
1705 p_count => x_msg_count
1706 , p_data => x_msg_data
1707 );
1708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1709 END IF ;
1710
1711 IF FND_API.to_Boolean(p_init_msg_list)
1712 THEN
1713 FND_MSG_PUB.initialize;
1714 END IF ;
1715
1716 ------------------------------------------------------------------------------
1717 -- Parameter Validations:
1718 ------------------------------------------------------------------------------
1719
1720 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1721 THEN
1722 FND_LOG.String
1723 (
1724 FND_LOG.level_statement
1725 , L_LOG_MODULE || 'param_null_check_start'
1726 , 'checking if any of the parameters are NULL'
1727 );
1728 END IF ;
1729
1730 -- none of the parameters passed to the worker
1731 -- concurrent request should be null
1732
1733 IF p_worker_id IS NULL
1734 OR p_purge_set_id IS NULL
1735 OR p_purge_batch_size IS NULL
1736 OR p_purge_source_with_open_task IS NULL
1737 OR p_audit_required IS NULL
1738 THEN
1739 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1740 THEN
1741 FND_LOG.String
1742 (
1743 FND_LOG.level_unexpected
1744 , L_LOG_MODULE || 'worker_params_not_enuf'
1745 , 'no parameters were supplied to the purge worker program'
1746 );
1747 END IF ;
1748
1749 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PARAM_NULL');
1750 FND_MSG_PUB.ADD;
1751
1752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1753 END IF;
1754
1755 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1756 THEN
1757 FND_LOG.String
1758 (
1759 FND_LOG.level_statement
1760 , L_LOG_MODULE || 'param_null_check_end'
1761 , 'after checking if any of the parameters are NULL'
1762 );
1763 END IF ;
1764
1765 ---
1766
1767 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1768 THEN
1769 FND_LOG.String
1770 (
1771 FND_LOG.level_statement
1772 , L_LOG_MODULE || 'parent_request_id_check_start'
1773 , 'checking if the parent request id is valid'
1774 );
1775 END IF ;
1776
1777 -- the worker concurrent request should
1778 -- be supplied with a purge_set_id which
1779 -- is a valid concurrent request id and
1780 -- should be one that is not complete.
1781
1782 BEGIN
1783 SELECT
1787 FROM
1784 1
1785 INTO
1786 l_row_count
1788 fnd_concurrent_requests r
1789 , fnd_concurrent_programs p
1790 WHERE
1791 r.request_id = p_purge_set_id
1792 AND p.concurrent_program_id = r.concurrent_program_id
1793 AND p.concurrent_program_name = 'CSSRPGP'
1794 AND p.application_id = 170
1795 AND r.status_code <> 'C';
1796 EXCEPTION
1797 WHEN NO_DATA_FOUND THEN
1798 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
1799 THEN
1800 FND_LOG.String
1801 (
1802 FND_LOG.level_unexpected
1803 , L_LOG_MODULE || 'worker_purgset_invalid'
1804 , 'invalid purge set id supplied to the worker concurrent program'
1805 );
1806 END IF ;
1807
1808 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKER_PURGSET_INV');
1809 FND_MSG_PUB.ADD;
1810
1811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1812 END;
1813
1814 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1815 THEN
1816 FND_LOG.String
1817 (
1818 FND_LOG.level_statement
1819 , L_LOG_MODULE || 'parent_request_id_check_end'
1820 , 'after checking if the parent request id is valid'
1821 );
1822 END IF ;
1823
1824 ------------------------------------------------------------------------------
1825 -- Actual Logic starts below:
1826 ------------------------------------------------------------------------------
1827
1828 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1829 THEN
1830 FND_LOG.String
1831 (
1832 FND_LOG.level_statement
1833 , L_LOG_MODULE || 'loop_start'
1834 , 'At the beginning of the main loop'
1835 );
1836 END IF ;
1837
1838 LOOP
1839
1840 -- Opening the cursor inside the loop to avoid
1841 -- the ORA-1555 snapshot too old problem
1842
1843 OPEN c_staging;
1844
1845 -- main loop of the worker thread that collects
1846 -- incident_ids that need to be purged into a
1847 -- pl/sql table, a batch at a time and inserts
1848 -- into the global temp table and calls the
1849 -- SR delete API.
1850
1851 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1852 THEN
1853 FND_LOG.String
1854 (
1855 FND_LOG.level_statement
1856 , L_LOG_MODULE || 'fetch_start'
1857 , 'fetching rows from the cursor on the table CS_INCIDENTS_PURGE_STAGING'
1858 );
1859 END IF ;
1860
1861 -- fetch a batch of records at a time
1862 -- to be purged into a pl/sql table
1863
1864 FETCH c_staging
1865 BULK COLLECT INTO l_incident_id_tbl
1866 LIMIT p_purge_batch_size;
1867
1868 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1869 THEN
1870 FND_LOG.String
1871 (
1872 FND_LOG.level_statement
1873 , L_LOG_MODULE || 'fetch_end'
1874 , 'after fetching rows from the cursor on the table '
1875 || 'CS_INCIDENTS_PURGE_STAGING '
1876 || l_incident_id_tbl.COUNT
1877 );
1878 END IF ;
1879
1880 ---
1881
1882 IF l_incident_id_tbl.COUNT > 0
1883
1884 -- [IF-1]
1885 -- check if there is some data fetched into
1886 -- the pl/sql table before inserting into the
1887 -- global temp table.
1888
1889 THEN
1890 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1891 THEN
1892 FND_LOG.String
1893 (
1894 FND_LOG.level_statement
1895 , L_LOG_MODULE || 'gen_proc_setid_start'
1896 , 'generating processing set id'
1897 );
1898 END IF ;
1899
1900 -- Generating a new processing set id
1901
1902 SELECT
1903 jtf_object_purge_proc_set_s.NEXTVAL
1904 INTO
1905 l_processing_set_id
1906 FROM
1907 dual;
1908
1909 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1910 THEN
1911 FND_LOG.String
1912 (
1913 FND_LOG.level_statement
1914 , L_LOG_MODULE || 'gen_proc_setid_end'
1915 , 'after generating processing set id ' || l_processing_set_id
1916 );
1917 END IF ;
1918
1919 ---
1920
1921 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1922 THEN
1923 FND_LOG.String
1924 (
1925 FND_LOG.level_statement
1926 , L_LOG_MODULE || 'insert_temp_start'
1927 , 'inserting incident ids into global temp table '
1928 || 'JTF_OBJECT_PURGE_PARAM_TMP'
1929 );
1930 END IF ;
1931
1932 -- Inserting the current batch of incident_ids
1933 -- into the global temp table for purging
1934
1935 FORALL j IN 1..l_incident_id_tbl.COUNT
1936 INSERT INTO jtf_object_purge_param_tmp
1937 (
1938 object_id
1939 , object_type
1940 , processing_set_id
1941 )
1942 VALUES
1943 (
1944 l_incident_id_tbl(j)
1945 , 'SR'
1946 , l_processing_set_id
1947 );
1948
1949 l_row_count := SQL%ROWCOUNT;
1950
1951 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1952 THEN
1953 FND_LOG.String
1954 (
1958 || 'JTF_OBJECT_PURGE_PARAM_TMP ' || l_row_count
1955 FND_LOG.level_statement
1956 , L_LOG_MODULE || 'insert_temp_end'
1957 , 'after inserting incident ids into global temp table '
1959 );
1960 END IF ;
1961
1962 ---
1963
1964 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1965 THEN
1966 FND_LOG.String
1967 (
1968 FND_LOG.level_statement
1969 , L_LOG_MODULE || 'sr_del_api_start'
1970 , 'calling the service request delete private api'
1971 );
1972 END IF ;
1973
1974 -- Calling the Service Request Private API to
1975 -- delete service requests that have been uploaded
1976 -- to the global temp table.
1977
1978 CS_SERVICEREQUEST_PVT.Delete_ServiceRequest
1979 (
1980 p_api_version_number => 1.0
1981 , p_init_msg_list => FND_API.G_FALSE
1982 , p_commit => FND_API.G_FALSE
1983 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1984 , p_processing_set_id => l_processing_set_id
1985 , p_purge_set_id => p_purge_set_id
1986 , p_purge_source_with_open_task => p_purge_source_with_open_task
1987 , p_audit_required => p_audit_required
1988 , x_return_status => x_return_status
1989 , x_msg_count => x_msg_count
1990 , x_msg_data => x_msg_data
1991 );
1992
1993 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1994 THEN
1995 FND_LOG.String
1996 (
1997 FND_LOG.level_statement
1998 , L_LOG_MODULE || 'sr_del_api_end'
1999 , 'after calling the service request delete private api'
2000 );
2001 FND_LOG.String
2002 (
2003 FND_LOG.level_statement
2004 , L_LOG_MODULE || 'sr_del_api_end'
2005 , 'return status of api call was ' || x_return_status
2006 );
2007 END IF;
2008
2009 ---
2010
2011 IF x_return_status = FND_API.G_RET_STS_SUCCESS
2012
2013 -- [IF-2]
2014 -- If all went well while
2015 -- executing the SR delete API
2016
2017 THEN
2018
2019 -- Since the current batch execution succeeded
2020 -- committing the work done in this transaction.
2021
2022 COMMIT;
2023
2024 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2025 THEN
2026 FND_LOG.String
2027 (
2028 FND_LOG.level_statement
2029 , L_LOG_MODULE || 'curr_batch_commit'
2030 , 'committed work done in batch with processing set id '
2031 || l_processing_set_id
2032 );
2033 END IF ;
2034
2035 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2036 OR x_return_status = FND_API.G_RET_STS_ERROR
2037
2038 -- If there was an error or unexpected error
2039 -- while executing the SR delete API
2040
2041 THEN
2042
2043 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2044 THEN
2045 FND_LOG.String
2046 (
2047 FND_LOG.level_statement
2048 , L_LOG_MODULE || 'error_incident_id_collect_start'
2049 , 'collecting errored incident ids into pl/sql table since batch '
2050 || ' with processing set id ' || l_processing_set_id || ' failed'
2051 );
2052 END IF ;
2053
2054 -- Collecting the set of SRs in the temp table
2055 -- that encountered errors during validations.
2056 -- This is done to preserve errors so that it is
2057 -- clear that these SRs would anyways not have been
2058 -- deleted even if an error condition had not occurred.
2059
2060 SELECT
2061 object_id
2062 , purge_error_message
2063 BULK COLLECT INTO
2064 l_err_incident_id_tbl
2065 , l_purge_error_message_tbl
2066 FROM
2067 jtf_object_purge_param_tmp
2068 WHERE
2069 processing_set_id = l_processing_set_id
2070 AND object_type = 'SR'
2071 AND NVL(purge_status, 'S') = 'E';
2072
2073 l_row_count := SQL%ROWCOUNT;
2074
2075 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2076 THEN
2077 FND_LOG.String
2078 (
2079 FND_LOG.level_statement
2080 , L_LOG_MODULE || 'error_incident_id_collect_end'
2081 , 'after collecting errored incident ids into pl/sql table '
2082 || l_row_count || ' rows'
2083 );
2084 END IF ;
2085
2086 ---
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 || 'curr_batch_rollback'
2094 , 'issuing ROLLBACK due to failure of batch with processing set id '
2095 || l_processing_set_id
2096 );
2097 END IF ;
2098
2099 -- Since the current batch execution failed
2100 -- rolling back the work done in this transaction.
2101
2102 ROLLBACK;
2103
2104 ---
2105
2106 x_msg_count := FND_MSG_PUB.Count_Msg;
2107 IF x_msg_count > 0
2108 THEN
2109 FND_MSG_PUB.Get
2110 (
2111 p_msg_index => 1
2112 , p_encoded => 'F'
2113 , p_data => x_msg_data
2117
2114 , p_msg_index_out => x_msg_index_out
2115 );
2116 END IF;
2118 -- setting the interim status of this
2119 -- worker thread as running with WARNINGS
2120 -- since purging this batch failed.
2121 -- however this will NOT stop the process
2122 -- from running further.
2123
2124 l_ret := fnd_concurrent.set_interim_status
2125 (
2126 'WARNING'
2127 , SUBSTR(x_msg_data, 1, 240)
2128 );
2129
2130 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2131 THEN
2132 FND_LOG.String
2133 (
2134 FND_LOG.level_statement
2135 , L_LOG_MODULE || 'worker_completion_sts_warn'
2136 , 'setting completion status to WARNING since batch '
2137 || l_processing_set_id || ' failed'
2138 );
2139 END IF ;
2140
2141 -- setting flag to identify that
2142 -- the batch has failed if the
2143 -- concurrent request was not terminated
2144
2145 IF NVL(fnd_conc_global.request_data, 'X') <> 'T'
2146 THEN
2147 -- Only if the program is not terminated, we
2148 -- set the batch failed status to TRUE if a
2149 -- batch had some problems and completed with
2150 -- errors.
2151 l_has_any_batch_failed := TRUE;
2152 END IF;
2153
2154 ---
2155
2156 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2157 THEN
2158 FND_LOG.String
2159 (
2160 FND_LOG.level_statement
2161 , L_LOG_MODULE || 'update_validation_errors'
2162 , 'updating validation errors to staging table again'
2163 );
2164 END IF ;
2165
2166 -- Updating the staging table with the errors
2167 -- that occurred due to validations.
2168
2169 FORALL j IN l_err_incident_id_tbl.FIRST..l_err_incident_id_tbl.LAST
2170 UPDATE cs_incidents_purge_staging
2171 SET
2172 purge_status = 'E'
2173 , purge_error_message = l_purge_error_message_tbl(j)
2174 WHERE
2175 incident_id = l_err_incident_id_tbl(j);
2176
2177 l_row_count := SQL%ROWCOUNT;
2178
2179 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2180 THEN
2181 FND_LOG.String
2182 (
2183 FND_LOG.level_statement
2184 , L_LOG_MODULE || 'update_validation_errors'
2185 , 'updating validation errors to staging table again ' || l_row_count
2186 );
2187 END IF ;
2188
2189 ---
2190
2191 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2192 THEN
2193 FND_LOG.String
2194 (
2195 FND_LOG.level_statement
2196 , L_LOG_MODULE || 'update_oracle_errors'
2197 , 'updating oracle errors to staging table - '
2198 || 'CS:CS_SR_PURG_BATCH_FAIL~' || x_msg_data
2199 );
2200 END IF ;
2201
2202 -- Updating the staging table with the errors
2203 -- that occurred due to failure during execution
2204 -- of the SR delete API.
2205
2206 FORALL j IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST
2207 UPDATE cs_incidents_purge_staging
2208 SET
2209 purge_status = 'E'
2210 , purge_error_message = 'CS:CS_SR_PURG_BATCH_FAIL~' || x_msg_data
2211 WHERE
2212 incident_id = l_incident_id_tbl(j)
2213 AND NVL(purge_status, 'S') = 'S';
2214
2215 l_row_count := SQL%ROWCOUNT;
2216
2217 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2218 THEN
2219 FND_LOG.String
2220 (
2221 FND_LOG.level_statement
2222 , L_LOG_MODULE || 'update_oracle_errors'
2223 , 'after updating oracle errors to staging table ' || l_row_count
2224 );
2225 END IF ;
2226
2227 -- committing the above error update
2228
2229 COMMIT;
2230
2231 ---
2232
2233 -- collecting the error messages
2234 -- and writing them to the log
2235
2236 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
2237 THEN
2238 x_msg_count := FND_MSG_PUB.Count_Msg;
2239
2240 IF x_msg_count > 0
2241 THEN
2242 FOR
2243 i IN 1..x_msg_count
2244 LOOP
2245 FND_MSG_PUB.Get
2246 (
2247 p_msg_index => i
2248 , p_encoded => 'F'
2249 , p_data => x_msg_data
2250 , p_msg_index_out => x_msg_index_out
2251 );
2252 FND_LOG.String
2253 (
2254 FND_LOG.level_unexpected
2255 , L_LOG_MODULE || 'curr_batch_error'
2256 , 'Error encountered is : ' || x_msg_data || ' [Index:'
2257 || x_msg_index_out || ']'
2258 );
2259 END LOOP;
2260 END IF ;
2261 END IF;
2262 END IF; -- [IF-2]
2263 END IF; -- [IF-1]
2264
2265 -- exit the loop when no more records
2266 -- are left in the cursor to process
2267 -- of if the user has requested to
2268 -- terminate the process.
2269
2270 EXIT WHEN
2271 c_staging%NOTFOUND
2272 OR fnd_conc_global.request_data = 'T';
2273
2274 -- Closing the cursor inside the loop to prevent
2278 CLOSE c_staging;
2275 -- ORA-1555 error. This cursor is reopened in the
2276 -- beginning of the loop during the next iteration.
2277
2279
2280 END LOOP;
2281
2282 IF c_staging%ISOPEN
2283 THEN
2284 -- In case the cursor did not have sufficient
2285 -- while executing, closing it outside the loop.
2286
2287 CLOSE c_staging;
2288 END IF;
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 || 'loop_end'
2296 , 'At the end of the main loop'
2297 );
2298 END IF ;
2299
2300 -- Writing the output for this worker thread
2301 -- indicating the operations carried out in it.
2302
2303 IF FND_CONC_GLOBAL.request_data = 'T'
2304 THEN
2305 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2306 THEN
2307 FND_LOG.String
2308 (
2309 FND_LOG.level_statement
2310 , L_LOG_MODULE || 'write_purge_output_start'
2311 , 'Writing purge output since parent was terminated'
2312 );
2313 END IF ;
2314
2315 Write_Purge_Output
2316 (
2317 p_purge_batch_size => p_purge_batch_size
2318 , p_request_id => p_purge_set_id
2319 , p_worker_id => p_worker_id
2320 );
2321
2322 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2323 THEN
2324 FND_LOG.String
2325 (
2326 FND_LOG.level_statement
2327 , L_LOG_MODULE || 'write_purge_output_end'
2328 , 'After writing purge output since parent was terminated'
2329 );
2330 END IF ;
2331 END IF;
2332
2333 ---
2334
2335 IF NVL(fnd_conc_global.request_data, 'X') <> 'T'
2336
2337 -- If the concurrent request is not terminated
2338 -- then the completion status is determined
2339 -- based on the outcome of the concurrent request.
2340 -- Otherwise, it is just not changed since the
2341 -- concurrent manager sets the request's status
2342 -- to 'TERMINATED' when the user requests to
2343 -- terminate the process.
2344
2345 THEN
2346 IF NOT l_has_any_batch_failed
2347
2348 -- If none of the batches have
2349 -- failed, then set the completion
2350 -- status to NORMAL.
2351
2352 THEN
2353 -- Setting the completion status of this concurrent
2354 -- request as COMPLETED NORMALLY
2355
2356 l_ret := fnd_concurrent.set_completion_status
2357 (
2358 'NORMAL'
2359 , ' '
2360 );
2361 ELSE
2362 -- Setting the completion status of this concurrent
2363 -- request as COMPLETED with WARNINGS since there
2364 -- were some batches that had failed
2365
2366 l_message := FND_MESSAGE.Get_String
2367 (
2368 'CS'
2369 , 'CS_SR_WORKER_BATCH_FAIL'
2370 );
2371
2372 l_ret := fnd_concurrent.set_completion_status
2373 (
2374 'WARNING'
2375 , l_message
2376 );
2377 END IF;
2378 ELSE
2379
2380 -- If a request has been terminated
2381 -- completion status has to be made
2382 -- as TERMINATED explicitly; otherwise
2383 -- the concurrent manager flags the
2384 -- request has completed with status
2385 -- NORMAL which is not correct in the
2386 -- current scenario.
2387
2388 l_ret := fnd_concurrent.set_completion_status
2389 (
2390 'TERMINATED'
2391 , ' '
2392 );
2393 END IF;
2394
2395 ---
2396
2397 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2398 THEN
2399 IF fnd_concurrent.get_request_status
2400 (
2401 request_id => l_request_id
2402 , phase => l_conc_req_phase
2403 , status => l_conc_req_status
2404 , dev_phase => l_conc_req_dev_phase
2405 , dev_status => l_conc_req_dev_status
2406 , message => l_conc_req_message
2407 )
2408 THEN
2409 FND_LOG.String
2410 (
2411 FND_LOG.level_procedure
2412 , L_LOG_MODULE || 'request_status_1'
2413 , 'l_main_conc_req_phase:' || l_conc_req_phase
2414 );
2415 FND_LOG.String
2416 (
2417 FND_LOG.level_procedure
2418 , L_LOG_MODULE || 'request_status_2'
2419 , 'l_conc_req_status:' || l_conc_req_status
2420 );
2421 FND_LOG.String
2422 (
2423 FND_LOG.level_procedure
2424 , L_LOG_MODULE || 'request_status_3'
2425 , 'l_conc_req_dev_phase:' || l_conc_req_dev_phase
2426 );
2427 FND_LOG.String
2428 (
2429 FND_LOG.level_procedure
2430 , L_LOG_MODULE || 'request_status_4'
2431 , 'l_conc_req_dev_status:' || l_conc_req_dev_status
2432 );
2433 FND_LOG.String
2434 (
2435 FND_LOG.level_procedure
2436 , L_LOG_MODULE || 'request_status_5'
2437 , 'l_conc_req_message:' || l_conc_req_message
2438 );
2439 END IF;
2440 END IF ;
2441
2442 ---
2443
2444 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2445 THEN
2446 FND_LOG.String
2447 (
2448 FND_LOG.level_procedure
2449 , L_LOG_MODULE || 'end'
2450 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2451 || x_return_status
2452 );
2453 FND_LOG.String
2454 (
2455 FND_LOG.level_procedure
2456 , L_LOG_MODULE || 'end_time'
2460
2457 , 'The end time is ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
2458 );
2459 END IF ;
2461 EXCEPTION
2462
2463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2464
2465 -- since there was an unexpected error,
2466 -- rolling back the work done
2467
2468 ROLLBACK;
2469
2470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471
2472 -- setting the completion status of this
2473 -- worker thread as COMPLETED with ERRORS
2474 -- since purging failed.
2475
2476 x_msg_count := FND_MSG_PUB.Count_Msg;
2477 IF x_msg_count > 0
2478 THEN
2479 FND_MSG_PUB.Get
2480 (
2481 p_msg_index => 1
2482 , p_encoded => 'F'
2483 , p_data => x_msg_data
2484 , p_msg_index_out => x_msg_index_out
2485 );
2486 END IF;
2487
2488 l_ret := fnd_concurrent.set_completion_status
2489 (
2490 'ERROR'
2491 , SUBSTR(x_msg_data, 1, 240)
2492 );
2493
2494 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
2495 THEN
2496 FND_LOG.String
2497 (
2498 FND_LOG.level_unexpected
2499 , L_LOG_MODULE || 'unexpected_error'
2500 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2501 );
2502
2503 x_msg_count := FND_MSG_PUB.Count_Msg;
2504
2505 IF x_msg_count > 0
2506 THEN
2507 FOR
2508 i IN 1..x_msg_count
2509 LOOP
2510 FND_MSG_PUB.Get
2511 (
2512 p_msg_index => i
2513 , p_encoded => 'F'
2514 , p_data => x_msg_data
2515 , p_msg_index_out => x_msg_index_out
2516 );
2517 FND_LOG.String
2518 (
2519 FND_LOG.level_unexpected
2520 , L_LOG_MODULE || 'unexpected_error'
2521 , 'Error encountered is : ' || x_msg_data || ' [Index:'
2522 || x_msg_index_out || ']'
2523 );
2524 END LOOP;
2525 END IF ;
2526 END IF ;
2527
2528 WHEN OTHERS THEN
2529
2530 -- since there was an unexpected error,
2531 -- rolling back the work done
2532
2533 ROLLBACK;
2534
2535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2536
2537 FND_MESSAGE.Set_Name('CS', 'CS_SR_PURG_WORKER_FAIL');
2538 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2539 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2540 FND_MSG_PUB.ADD;
2541
2542 -- setting the completion status of this
2543 -- worker thread as COMPLETED with ERRORS
2544 -- since purging failed.
2545
2546 x_msg_count := FND_MSG_PUB.Count_Msg;
2547 IF x_msg_count > 0
2548 THEN
2549 FND_MSG_PUB.Get
2550 (
2551 p_msg_index => 1
2552 , p_encoded => 'F'
2553 , p_data => x_msg_data
2554 , p_msg_index_out => x_msg_index_out
2555 );
2556 END IF;
2557
2558 l_ret := fnd_concurrent.set_completion_status
2559 (
2560 'ERROR'
2561 , SUBSTR(x_msg_data, 1, 240)
2562 );
2563
2564 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
2565 THEN
2566 FND_LOG.String
2567 (
2568 FND_LOG.level_unexpected
2569 , L_LOG_MODULE || 'when_others'
2570 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2571 );
2572 FND_LOG.String
2573 (
2574 FND_LOG.level_unexpected
2575 , L_LOG_MODULE || 'when_others'
2576 , SQLERRM
2577 );
2578 END IF ;
2579
2580 END Purge_Sr_Worker;
2581
2582 --------------------------------------------------------------------------------
2583 -- Procedure Name : ACTIVITY_SUMMARIZER
2584 --
2585 -- Parameters (other than standard ones)
2586 -- None.
2587 --
2588 -- Description
2589 -- This procedure helps the administrator to get a summary of the
2590 -- number of rows that each object that is associated with an SR
2591 -- has in the database. This will be called by the Oracle Application
2592 -- Manager when the administrator invokes the activitiy summarizer
2593 -- option for this concurrent program.
2594 --
2595 -- HISTORY
2596 --
2597 ----------------+------------+--------------------------------------------------
2598 -- DATE | UPDATED BY | Change Description
2599 ----------------+------------+--------------------------------------------------
2600 -- 2-Aug_2005 | varnaray | Created
2601 -- | |
2602 ----------------+------------+--------------------------------------------------
2603 /*#
2604 * This procedure helps the administrator to get a summary of the
2605 * number of rows that each object that is associated with an SR
2606 * has in the database. This will be called by the Oracle Application
2607 * Manager when the administrator invokes the activitiy summarizer
2608 * option for this concurrent program.
2609 * @rep:scope internal
2610 * @rep:product CS
2611 * @rep:displayname Activity Summarizer
2612 */
2613 PROCEDURE Activity_Summarizer
2614 IS
2615 --------------------------------------------------------------------------------
2616
2617 L_API_NAME CONSTANT VARCHAR2(30) := 'ACTIVITY_SUMMARIZER';
2618 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
2619 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2620
2621 l_sr_audit_1 NUMBER;
2622 l_sr_audit_2 NUMBER;
2623 l_sr_audit_3 NUMBER;
2624 l_sr_audit_4 NUMBER;
2625 l_sr_audits NUMBER;
2629 l_sr_rows NUMBER;
2626 l_sr_attr_1 NUMBER;
2627 l_sr_attr_2 NUMBER;
2628 l_sr_attrs NUMBER;
2630 l_sr_contacts NUMBER;
2631 l_sr_cont_attrs NUMBER;
2632 l_sr_links NUMBER;
2633 l_sr_msgs NUMBER;
2634 l_sr_kb_links NUMBER;
2635 l_sr_estimates NUMBER;
2636 l_sr_tasks NUMBER;
2637 l_sr_notes NUMBER;
2638 l_sr_activities NUMBER;
2639 l_sr_attachs NUMBER;
2640 l_sr_work_items NUMBER;
2641 l_sr_total_rows NUMBER;
2642
2643 l_string VARCHAR2(500);
2644
2645 BEGIN
2646 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2647 THEN
2648 FND_LOG.String
2649 (
2650 FND_LOG.level_procedure
2651 , L_LOG_MODULE || 'start'
2652 , 'Inside ' || L_API_NAME_FULL || ', does not have any parameters.'
2653 );
2654 END IF ;
2655
2656 ------------------------------------------------------------------------------
2657 -- Actual Logic starts below:
2658 ------------------------------------------------------------------------------
2659
2660 -- The statements below collect the number of Service Requests
2661 -- and the corresponding child objects, to be displayed
2662 -- in the activity summarizer.
2663
2664 --- 1
2665
2666 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2667 THEN
2668 FND_LOG.String
2669 (
2670 FND_LOG.level_statement
2671 , L_LOG_MODULE || 'sr_start'
2672 , 'fetching count of closed SRs'
2673 );
2674 END IF ;
2675
2676 SELECT
2677 count(*)
2678 INTO
2679 l_sr_rows
2680 FROM
2681 cs_incidents_all_b b
2682 WHERE
2683 NOT EXISTS
2684 (
2685 SELECT
2686 1
2687 FROM
2688 csd_repairs
2689 WHERE
2690 incident_id = b.incident_id
2691 )
2692 AND NOT EXISTS
2693 (
2694 SELECT
2695 1
2696 FROM
2697 cs_incident_types_b
2698 WHERE
2699 incident_type_id = b.incident_type_id
2700 AND
2701 (
2702 NVL
2703 (
2704 maintenance_flag
2705 , 'N'
2706 ) = 'Y'
2707 OR NVL
2708 (
2709 cmro_flag
2710 , 'N'
2711 ) = 'Y'
2712 )
2713 )
2714 AND status_flag = 'C';
2715
2716 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2717 THEN
2718 FND_LOG.String
2719 (
2720 FND_LOG.level_statement
2721 , L_LOG_MODULE || 'sr_end'
2722 , 'after fetching count of closed SRs ' || l_sr_rows
2723 );
2724 END IF ;
2725
2726 --- 2
2727
2728 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2729 THEN
2730 FND_LOG.String
2731 (
2732 FND_LOG.level_statement
2733 , L_LOG_MODULE || 'contact_start'
2734 , 'fetching count of contacts linked to closed SRs'
2735 );
2736 END IF ;
2737
2738 SELECT
2739 count(*)
2740 INTO
2741 l_sr_contacts
2742 FROM
2743 cs_hz_sr_contact_points cp
2744 , cs_incidents_all_b b
2745 WHERE
2746 NOT EXISTS
2747 (
2748 SELECT
2749 1
2750 FROM
2751 csd_repairs
2752 WHERE
2753 incident_id = b.incident_id
2754 )
2755 AND NOT EXISTS
2756 (
2757 SELECT
2758 1
2759 FROM
2760 cs_incident_types_b
2761 WHERE
2762 incident_type_id = b.incident_type_id
2763 AND
2764 (
2765 NVL
2766 (
2767 maintenance_flag
2768 , 'N'
2769 ) = 'Y'
2770 OR NVL
2771 (
2772 cmro_flag
2773 , 'N'
2774 ) = 'Y'
2775 )
2776 )
2777 AND b.incident_id = cp.incident_id
2778 AND b.status_flag = 'C';
2779
2780 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2781 THEN
2782 FND_LOG.String
2783 (
2784 FND_LOG.level_statement
2785 , L_LOG_MODULE || 'contact_end'
2786 , 'after fetching count of contacts linked to closed SRs ' || l_sr_contacts
2787 );
2788 END IF ;
2789
2790 --- 3
2791
2792 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2793 THEN
2794 FND_LOG.String
2795 (
2796 FND_LOG.level_statement
2797 , L_LOG_MODULE || 'contact_attr_start'
2798 , 'fetching count of extended attributes of contacts linked to closed SRs'
2799 );
2800 END IF ;
2801
2802 SELECT
2803 count(*)
2804 INTO
2805 l_sr_cont_attrs
2806 FROM
2807 cs_sr_contacts_ext ex
2808 , cs_incidents_all_b b
2809 WHERE
2810 NOT EXISTS
2811 (
2812 SELECT
2813 1
2814 FROM
2815 csd_repairs
2816 WHERE
2817 incident_id = b.incident_id
2818 )
2819 AND NOT EXISTS
2820 (
2821 SELECT
2822 1
2823 FROM
2824 cs_incident_types_b
2825 WHERE
2826 incident_type_id = b.incident_type_id
2827 AND
2828 (
2829 NVL
2830 (
2831 maintenance_flag
2832 , 'N'
2833 ) = 'Y'
2834 OR NVL
2835 (
2836 cmro_flag
2837 , 'N'
2838 ) = 'Y'
2842 AND b.status_flag = 'C';
2839 )
2840 )
2841 AND b.incident_id = ex.incident_id
2843
2844 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2845 THEN
2846 FND_LOG.String
2847 (
2848 FND_LOG.level_statement
2849 , L_LOG_MODULE || 'contact_attr_end'
2850 , 'after fetching count of extended attributes of contacts '
2851 || 'linked to closed SRs '
2852 || l_sr_cont_attrs
2853 );
2854 END IF ;
2855
2856 --- 4
2857
2858 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2859 THEN
2860 FND_LOG.String
2861 (
2862 FND_LOG.level_statement
2863 , L_LOG_MODULE || 'link_start'
2864 , 'fetching count of links to closed SRs'
2865 );
2866 END IF ;
2867
2868 SELECT
2869 count(*)
2870 INTO
2871 l_sr_links
2872 FROM
2873 cs_incident_links l
2874 , cs_incidents_all_b b
2875 WHERE
2876 (
2877 l.subject_id = b.incident_id
2878 AND l.subject_type = 'SR'
2879 OR l.object_id = b.incident_id
2880 AND l.object_type = 'SR'
2881 )
2882 AND NOT EXISTS
2883 (
2884 SELECT
2885 1
2886 FROM
2887 csd_repairs
2888 WHERE
2889 incident_id = b.incident_id
2890 )
2891 AND NOT EXISTS
2892 (
2893 SELECT
2894 1
2895 FROM
2896 cs_incident_types_b
2897 WHERE
2898 incident_type_id = b.incident_type_id
2899 AND
2900 (
2901 NVL
2902 (
2903 maintenance_flag
2904 , 'N'
2905 ) = 'Y'
2906 OR NVL
2907 (
2908 cmro_flag
2909 , 'N'
2910 ) = 'Y'
2911 )
2912 )
2913 AND b.status_flag = 'C';
2914
2915 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2916 THEN
2917 FND_LOG.String
2918 (
2919 FND_LOG.level_statement
2920 , L_LOG_MODULE || 'link_end'
2921 , 'after fetching count of links to closed SRs ' || l_sr_links
2922 );
2923 END IF ;
2924
2925 --- 5
2926
2927 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2928 THEN
2929 FND_LOG.String
2930 (
2931 FND_LOG.level_statement
2932 , L_LOG_MODULE || 'message_start'
2933 , 'fetching count of messages linked to closed SRs'
2934 );
2935 END IF ;
2936
2937 SELECT
2938 count(*)
2939 INTO
2940 l_sr_msgs
2941 FROM
2942 cs_messages msg
2943 , cs_incidents_all_b b
2944 WHERE
2945 msg.source_object_int_id = b.incident_id
2946 AND msg.source_object_type_code = 'INC'
2947 AND NOT EXISTS
2948 (
2949 SELECT
2950 1
2951 FROM
2952 csd_repairs
2953 WHERE
2954 incident_id = b.incident_id
2955 )
2956 AND NOT EXISTS
2957 (
2958 SELECT
2959 1
2960 FROM
2961 cs_incident_types_b
2962 WHERE
2963 incident_type_id = b.incident_type_id
2964 AND
2965 (
2966 NVL
2967 (
2968 maintenance_flag
2969 , 'N'
2970 ) = 'Y'
2971 OR NVL
2972 (
2973 cmro_flag
2974 , 'N'
2975 ) = 'Y'
2976 )
2977 )
2978 AND b.status_flag = 'C';
2979
2980 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2981 THEN
2982 FND_LOG.String
2983 (
2984 FND_LOG.level_statement
2985 , L_LOG_MODULE || 'message_end'
2986 , 'after fetching count of messages linked to closed SRs ' || l_sr_msgs
2987 );
2988 END IF ;
2989
2990 --- 6
2991
2992 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2993 THEN
2994 FND_LOG.String
2995 (
2996 FND_LOG.level_statement
2997 , L_LOG_MODULE || 'km_start'
2998 , 'fetching count of solutions linked to closed SRs'
2999 );
3000 END IF ;
3001
3002 SELECT
3003 count(*)
3004 INTO
3005 l_sr_kb_links
3006 FROM
3007 cs_kb_set_links k
3008 , cs_incidents_all_b b
3009 WHERE
3010 k.object_code = 'SR'
3011 AND k.other_id = b.incident_id
3012 AND b.status_flag = 'C'
3013 AND NOT EXISTS
3014 (
3015 SELECT
3016 1
3017 FROM
3018 csd_repairs
3019 WHERE
3020 incident_id = b.incident_id
3021 )
3022 AND NOT EXISTS
3023 (
3024 SELECT
3025 1
3026 FROM
3027 cs_incident_types_b
3028 WHERE
3029 incident_type_id = b.incident_type_id
3030 AND
3031 (
3032 NVL
3033 (
3034 maintenance_flag
3035 , 'N'
3036 ) = 'Y'
3037 OR NVL
3038 (
3039 cmro_flag
3040 , 'N'
3041 ) = 'Y'
3042 )
3043 );
3044
3045 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3046 THEN
3047 FND_LOG.String
3048 (
3049 FND_LOG.level_statement
3050 , L_LOG_MODULE || 'km_end'
3054
3051 , 'after fetching count of solutions linked to closed SRs ' || l_sr_kb_links
3052 );
3053 END IF ;
3055 --- 7
3056
3057 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3058 THEN
3059 FND_LOG.String
3060 (
3061 FND_LOG.level_statement
3062 , L_LOG_MODULE || 'charge_start'
3063 , 'fetching count of charge lines linked to closed SRs'
3064 );
3065 END IF ;
3066
3067 SELECT
3068 count(*)
3069 INTO
3070 l_sr_estimates
3071 FROM
3072 cs_estimate_details es
3073 , cs_incidents_all_b b
3074 WHERE
3075 b.incident_id = es.incident_id
3076 AND b.status_flag = 'C'
3077 AND NOT EXISTS
3078 (
3079 SELECT
3080 1
3081 FROM
3082 csd_repairs
3083 WHERE
3084 incident_id = b.incident_id
3085 )
3086 AND NOT EXISTS
3087 (
3088 SELECT
3089 1
3090 FROM
3091 cs_incident_types_b
3092 WHERE
3093 incident_type_id = b.incident_type_id
3094 AND
3095 (
3096 NVL
3097 (
3098 maintenance_flag
3099 , 'N'
3100 ) = 'Y'
3101 OR NVL
3102 (
3103 cmro_flag
3104 , 'N'
3105 ) = 'Y'
3106 )
3107 );
3108
3109 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3110 THEN
3111 FND_LOG.String
3112 (
3113 FND_LOG.level_statement
3114 , L_LOG_MODULE || 'charge_end'
3115 , 'after fetching count of charge lines linked to closed SRs '
3116 || l_sr_estimates
3117 );
3118 END IF ;
3119
3120 --- 8
3121
3122 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3123 THEN
3124 FND_LOG.String
3125 (
3126 FND_LOG.level_statement
3127 , L_LOG_MODULE || 'task_start'
3128 , 'fetching count of tasks linked to closed SRs'
3129 );
3130 END IF ;
3131
3132 SELECT
3133 count(*)
3134 INTO
3135 l_sr_tasks
3136 FROM
3137 jtf_tasks_b j
3138 , cs_incidents_all_b b
3139 WHERE
3140 b.incident_id = j.source_object_id
3141 AND j.source_object_type_code = 'SR'
3142 AND b.status_flag = 'C'
3143 AND NOT EXISTS
3144 (
3145 SELECT
3146 1
3147 FROM
3148 csd_repairs
3149 WHERE
3150 incident_id = b.incident_id
3151 )
3152 AND NOT EXISTS
3153 (
3154 SELECT
3155 1
3156 FROM
3157 cs_incident_types_b
3158 WHERE
3159 incident_type_id = b.incident_type_id
3160 AND
3161 (
3162 NVL
3163 (
3164 maintenance_flag
3165 , 'N'
3166 ) = 'Y'
3167 OR NVL
3168 (
3169 cmro_flag
3170 , 'N'
3171 ) = 'Y'
3172 )
3173 );
3174
3175 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3176 THEN
3177 FND_LOG.String
3178 (
3179 FND_LOG.level_statement
3180 , L_LOG_MODULE || 'task_end'
3181 , 'after fetching count of tasks linked to closed SRs ' || l_sr_tasks
3182 );
3183 END IF ;
3184
3185 --- 9
3186
3187 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3188 THEN
3189 FND_LOG.String
3190 (
3191 FND_LOG.level_statement
3192 , L_LOG_MODULE || 'note_start'
3193 , 'fetching count of notes linked to closed SRs'
3194 );
3195 END IF ;
3196
3197 SELECT
3198 count(*)
3199 INTO
3200 l_sr_notes
3201 FROM
3202 jtf_notes_b j
3203 , cs_incidents_all_b b
3204 WHERE
3205 b.incident_id = j.source_object_id
3206 AND j.source_object_code = 'SR'
3207 AND b.status_flag = 'C'
3208 AND NOT EXISTS
3209 (
3210 SELECT
3211 1
3212 FROM
3213 csd_repairs
3214 WHERE
3215 incident_id = b.incident_id
3216 )
3217 AND NOT EXISTS
3218 (
3219 SELECT
3220 1
3221 FROM
3222 cs_incident_types_b
3223 WHERE
3224 incident_type_id = b.incident_type_id
3225 AND
3226 (
3227 NVL
3228 (
3229 maintenance_flag
3230 , 'N'
3231 ) = 'Y'
3232 OR NVL
3233 (
3234 cmro_flag
3235 , 'N'
3236 ) = 'Y'
3237 )
3238 );
3239
3240 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3241 THEN
3242 FND_LOG.String
3243 (
3244 FND_LOG.level_statement
3245 , L_LOG_MODULE || 'note_end'
3246 , 'after fetching count of notes linked to closed SRs ' || l_sr_notes
3247 );
3248 END IF ;
3249
3250 --- 10
3251
3252 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3253 THEN
3254 FND_LOG.String
3255 (
3256 FND_LOG.level_statement
3257 , L_LOG_MODULE || 'activity_start'
3258 , 'fetching count of activities linked to closed SRs'
3259 );
3260 END IF ;
3261
3262 SELECT
3263 count(*)
3264 INTO
3268 , cs_incidents_all_b b
3265 l_sr_activities
3266 FROM
3267 jtf_ih_activities j
3269 WHERE
3270 b.incident_id = j.doc_id
3271 AND j.doc_ref = 'SR'
3272 AND b.status_flag = 'C'
3273 AND NOT EXISTS
3274 (
3275 SELECT
3276 1
3277 FROM
3278 csd_repairs
3279 WHERE
3280 incident_id = b.incident_id
3281 )
3282 AND NOT EXISTS
3283 (
3284 SELECT
3285 1
3286 FROM
3287 cs_incident_types_b
3288 WHERE
3289 incident_type_id = b.incident_type_id
3290 AND
3291 (
3292 NVL
3293 (
3294 maintenance_flag
3295 , 'N'
3296 ) = 'Y'
3297 OR NVL
3298 (
3299 cmro_flag
3300 , 'N'
3301 ) = 'Y'
3302 )
3303 );
3304
3305 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3306 THEN
3307 FND_LOG.String
3308 (
3309 FND_LOG.level_statement
3310 , L_LOG_MODULE || 'activity_end'
3311 , 'after fetching count of activities linked to closed SRs '
3312 || l_sr_activities
3313 );
3314 END IF ;
3315
3316 --- 11
3317
3318 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3319 THEN
3320 FND_LOG.String
3321 (
3322 FND_LOG.level_statement
3323 , L_LOG_MODULE || 'attach_start'
3324 , 'fetching count of attachments linked to closed SRs'
3325 );
3326 END IF ;
3327
3328 SELECT
3329 count(*)
3330 INTO
3331 l_sr_attachs
3332 FROM
3333 fnd_attached_documents d
3334 , cs_incidents_all_b b
3335 WHERE
3336 b.incident_id = d.pk1_value
3337 AND d.entity_name = 'CS_INCIDENTS'
3338 AND b.status_flag = 'C'
3339 AND NOT EXISTS
3340 (
3341 SELECT
3342 1
3343 FROM
3344 csd_repairs
3345 WHERE
3346 incident_id = b.incident_id
3347 )
3348 AND NOT EXISTS
3349 (
3350 SELECT
3351 1
3352 FROM
3353 cs_incident_types_b
3354 WHERE
3355 incident_type_id = b.incident_type_id
3356 AND
3357 (
3358 NVL
3359 (
3360 maintenance_flag
3361 , 'N'
3362 ) = 'Y'
3363 OR NVL
3364 (
3365 cmro_flag
3366 , 'N'
3367 ) = 'Y'
3368 )
3369 );
3370
3371 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3372 THEN
3373 FND_LOG.String
3374 (
3375 FND_LOG.level_statement
3376 , L_LOG_MODULE || 'attach_end'
3377 , 'after fetching count of attachments linked to closed SRs '
3378 || l_sr_attachs
3379 );
3380 END IF ;
3381
3382 --- 12
3383
3384 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3385 THEN
3386 FND_LOG.String
3387 (
3388 FND_LOG.level_statement
3389 , L_LOG_MODULE || 'work_item_start'
3390 , 'fetching count of uwq work items linked to closed SRs'
3391 );
3392 END IF ;
3393
3394 SELECT
3395 count(*)
3396 INTO
3397 l_sr_work_items
3398 FROM
3399 ieu_uwqm_items u
3400 , cs_incidents_all_b b
3401 WHERE
3402 b.incident_id = u.workitem_pk_id
3403 AND u.workitem_obj_code = 'SR'
3404 AND b.status_flag = 'C'
3405 AND NOT EXISTS
3406 (
3407 SELECT
3408 1
3409 FROM
3410 csd_repairs
3411 WHERE
3412 incident_id = b.incident_id
3413 )
3414 AND NOT EXISTS
3415 (
3416 SELECT
3417 1
3418 FROM
3419 cs_incident_types_b
3420 WHERE
3421 incident_type_id = b.incident_type_id
3422 AND
3423 (
3424 NVL
3425 (
3426 maintenance_flag
3427 , 'N'
3428 ) = 'Y'
3429 OR NVL
3430 (
3431 cmro_flag
3432 , 'N'
3433 ) = 'Y'
3434 )
3435 );
3436
3437 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3438 THEN
3439 FND_LOG.String
3440 (
3441 FND_LOG.level_statement
3442 , L_LOG_MODULE || 'work_item_end'
3443 , 'after fetching count of uwq work items linked to closed SRs '
3444 || l_sr_work_items
3445 );
3446 END IF ;
3447
3448 --- 13
3449
3450 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3451 THEN
3452 FND_LOG.String
3453 (
3454 FND_LOG.level_statement
3455 , L_LOG_MODULE || 'audit_sr_ext_start'
3456 , 'fetching count of sr extended attributes audit linked to closed SRs'
3457 );
3458 END IF ;
3459
3460 SELECT
3461 count(*)
3462 INTO
3463 l_sr_audit_1
3464 FROM
3465 cs_incidents_ext_audit a
3466 , cs_incidents_all_b b
3467 WHERE
3468 b.incident_id = a.incident_id
3469 AND b.status_flag = 'C'
3470 AND NOT EXISTS
3471 (
3472 SELECT
3473 1
3474 FROM
3475 csd_repairs
3476 WHERE
3477 incident_id = b.incident_id
3478 )
3479 AND NOT EXISTS
3480 (
3481 SELECT
3482 1
3483 FROM
3484 cs_incident_types_b
3485 WHERE
3486 incident_type_id = b.incident_type_id
3490 (
3487 AND
3488 (
3489 NVL
3491 maintenance_flag
3492 , 'N'
3493 ) = 'Y'
3494 OR NVL
3495 (
3496 cmro_flag
3497 , 'N'
3498 ) = 'Y'
3499 )
3500 );
3501
3502 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3503 THEN
3504 FND_LOG.String
3505 (
3506 FND_LOG.level_statement
3507 , L_LOG_MODULE || 'audit_sr_ext_end'
3508 , 'after fetching count of sr extended attributes audit'
3509 || ' linked to closed SRs '
3510 || l_sr_audit_1
3511 );
3512 END IF ;
3513
3514 --- 14
3515
3516 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3517 THEN
3518 FND_LOG.String
3519 (
3520 FND_LOG.level_statement
3521 , L_LOG_MODULE || 'audit_sr_cont_start'
3522 , 'fetching count of contact extended attributes audit linked to closed SRs'
3523 );
3524 END IF ;
3525
3526 SELECT
3527 count(*)
3528 INTO
3529 l_sr_audit_2
3530 FROM
3531 cs_sr_contacts_ext_audit a
3532 , cs_incidents_all_b b
3533 WHERE
3534 b.incident_id = a.incident_id
3535 AND b.status_flag = 'C'
3536 AND NOT EXISTS
3537 (
3538 SELECT
3539 1
3540 FROM
3541 csd_repairs
3542 WHERE
3543 incident_id = b.incident_id
3544 )
3545 AND NOT EXISTS
3546 (
3547 SELECT
3548 1
3549 FROM
3550 cs_incident_types_b
3551 WHERE
3552 incident_type_id = b.incident_type_id
3553 AND
3554 (
3555 NVL
3556 (
3557 maintenance_flag
3558 , 'N'
3559 ) = 'Y'
3560 OR NVL
3561 (
3562 cmro_flag
3563 , 'N'
3564 ) = 'Y'
3565 )
3566 );
3567
3568 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3569 THEN
3570 FND_LOG.String
3571 (
3572 FND_LOG.level_statement
3573 , L_LOG_MODULE || 'audit_sr_cont_end'
3574 , 'after fetching count of contact extended attributes audit '
3575 || 'linked to closed SRs '
3576 || l_sr_audit_2
3577 );
3578 END IF ;
3579
3580 --- 15
3581
3582 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3583 THEN
3584 FND_LOG.String
3585 (
3586 FND_LOG.level_statement
3587 , L_LOG_MODULE || 'audit_srtl_start'
3588 , 'fetching count of sr audit TL rows linked to closed SRs'
3589 );
3590 END IF ;
3591
3592 SELECT
3593 count(*)
3594 INTO
3595 l_sr_audit_3
3596 FROM
3597 cs_incidents_audit_tl a
3598 , cs_incidents_all_b b
3599 WHERE
3600 b.incident_id = a.incident_id
3601 AND b.status_flag = 'C'
3602 AND NOT EXISTS
3603 (
3604 SELECT
3605 1
3606 FROM
3607 csd_repairs
3608 WHERE
3609 incident_id = b.incident_id
3610 )
3611 AND NOT EXISTS
3612 (
3613 SELECT
3614 1
3615 FROM
3616 cs_incident_types_b
3617 WHERE
3618 incident_type_id = b.incident_type_id
3619 AND
3620 (
3621 NVL
3622 (
3623 maintenance_flag
3624 , 'N'
3625 ) = 'Y'
3626 OR NVL
3627 (
3628 cmro_flag
3629 , 'N'
3630 ) = 'Y'
3631 )
3632 );
3633
3634 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3635 THEN
3636 FND_LOG.String
3637 (
3638 FND_LOG.level_statement
3639 , L_LOG_MODULE || 'audit_srtl_end'
3640 , 'after fetching count of sr audit TL rows linked to closed SRs '
3641 || l_sr_audit_3
3642 );
3643 END IF ;
3644
3645 --- 16
3646
3647 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3648 THEN
3649 FND_LOG.String
3650 (
3651 FND_LOG.level_statement
3652 , L_LOG_MODULE || 'audit_srb_start'
3653 , 'fetching count of sr audit B rows linked to closed SRs'
3654 );
3655 END IF ;
3656
3657 SELECT
3658 count(*)
3659 INTO
3660 l_sr_audit_4
3661 FROM
3662 cs_incidents_audit_b a
3663 , cs_incidents_all_b b
3664 WHERE
3665 b.incident_id = a.incident_id
3666 AND b.status_flag = 'C'
3667 AND NOT EXISTS
3668 (
3669 SELECT
3670 1
3671 FROM
3672 csd_repairs
3673 WHERE
3674 incident_id = b.incident_id
3675 )
3676 AND NOT EXISTS
3677 (
3678 SELECT
3679 1
3680 FROM
3681 cs_incident_types_b
3682 WHERE
3683 incident_type_id = b.incident_type_id
3684 AND
3685 (
3686 NVL
3687 (
3688 maintenance_flag
3689 , 'N'
3690 ) = 'Y'
3691 OR NVL
3692 (
3693 cmro_flag
3694 , 'N'
3695 ) = 'Y'
3696 )
3697 );
3698
3699 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3700 THEN
3701 FND_LOG.String
3702 (
3703 FND_LOG.level_statement
3704 , L_LOG_MODULE || 'audit_srb_end'
3705 , 'after fetching count of sr audit B rows linked to closed SRs '
3706 || l_sr_audit_4
3710 --- 17
3707 );
3708 END IF ;
3709
3711
3712 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3713 THEN
3714 FND_LOG.String
3715 (
3716 FND_LOG.level_statement
3717 , L_LOG_MODULE || 'cug_attrib_start'
3718 , 'fetching count of cug attributes linked to closed SRs'
3719 );
3720 END IF ;
3721
3722 SELECT
3723 count(*)
3724 INTO
3725 l_sr_attr_1
3726 FROM
3727 cug_incidnt_attr_vals_b a
3728 , cs_incidents_all_b b
3729 WHERE
3730 b.incident_id = a.incident_id
3731 AND b.status_flag = 'C'
3732 AND NOT EXISTS
3733 (
3734 SELECT
3735 1
3736 FROM
3737 csd_repairs
3738 WHERE
3739 incident_id = b.incident_id
3740 )
3741 AND NOT EXISTS
3742 (
3743 SELECT
3744 1
3745 FROM
3746 cs_incident_types_b
3747 WHERE
3748 incident_type_id = b.incident_type_id
3749 AND
3750 (
3751 NVL
3752 (
3753 maintenance_flag
3754 , 'N'
3755 ) = 'Y'
3756 OR NVL
3757 (
3758 cmro_flag
3759 , 'N'
3760 ) = 'Y'
3761 )
3762 );
3763
3764 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3765 THEN
3766 FND_LOG.String
3767 (
3768 FND_LOG.level_statement
3769 , L_LOG_MODULE || 'cug_attrib_end'
3770 , 'after fetching count of cug attributes linked to closed SRs '
3771 || l_sr_attr_1
3772 );
3773 END IF ;
3774
3775 --- 18
3776
3777 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3778 THEN
3779 FND_LOG.String
3780 (
3781 FND_LOG.level_statement
3782 , L_LOG_MODULE || 'sr_attrib_start'
3783 , 'fetching count of sr extended attributes linked to closed SRs'
3784 );
3785 END IF ;
3786
3787 SELECT
3788 count(*)
3789 INTO
3790 l_sr_attr_2
3791 FROM
3792 cs_incidents_ext a
3793 , cs_incidents_all_b b
3794 WHERE
3795 b.incident_id = a.incident_id
3796 AND b.status_flag = 'C'
3797 AND NOT EXISTS
3798 (
3799 SELECT
3800 1
3801 FROM
3802 csd_repairs
3803 WHERE
3804 incident_id = b.incident_id
3805 )
3806 AND NOT EXISTS
3807 (
3808 SELECT
3809 1
3810 FROM
3811 cs_incident_types_b
3812 WHERE
3813 incident_type_id = b.incident_type_id
3814 AND
3815 (
3816 NVL
3817 (
3818 maintenance_flag
3819 , 'N'
3820 ) = 'Y'
3821 OR NVL
3822 (
3823 cmro_flag
3824 , 'N'
3825 ) = 'Y'
3826 )
3827 );
3828
3829 l_sr_audits := l_sr_audit_1 + l_sr_audit_2 + l_sr_audit_3 + l_sr_audit_4;
3830 l_sr_attrs := l_sr_attr_1 + l_sr_attr_2;
3831 l_sr_total_rows := l_sr_rows + l_sr_contacts + l_sr_cont_attrs
3832 + l_sr_links + l_sr_msgs + l_sr_kb_links + l_sr_estimates
3833 + l_sr_tasks + l_sr_notes + l_sr_activities + l_sr_attachs
3834 + l_sr_work_items + l_sr_audits + l_sr_attrs;
3835
3836 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3837 THEN
3838 FND_LOG.String
3839 (
3840 FND_LOG.level_statement
3841 , L_LOG_MODULE || 'sr_attrib_end'
3842 , 'after fetching count of sr extended attributes linked to closed SRs '
3843 || l_sr_attr_2
3844 );
3845 END IF ;
3846
3847 ---
3848
3849 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
3850 THEN
3851 FND_LOG.String
3852 (
3853 FND_LOG.level_statement
3854 , L_LOG_MODULE || 'activity_summary_start'
3855 , 'preparing the activity summary report'
3856 );
3857 END IF ;
3858
3859 -- The following lines fetch the text that needs to be displayed
3860 -- in the activity summarizer and then inserts a new row into
3861 -- the table indicating the number of instances that are available
3862 -- under each object.
3863 --
3864 -- Eg.,
3865 -- +--------------------------------------------------+-------+
3866 -- | Name | Value |
3867 -- +--------------------------------------------------+-------+
3868 -- | Service Requests | 20039 |
3869 -- +--------------------------------------------------+-------+
3870
3871 l_string := FND_MESSAGE.Get_String
3872 (
3873 'CS'
3874 , 'CS_SERVICE_REQUESTS'
3875 );
3876 FND_CONC_SUMMARIZER.Insert_Row
3877 (
3878 l_string
3879 , to_char(l_sr_rows)
3880 );
3881
3882 l_string := FND_MESSAGE.Get_String
3883 (
3884 'CS'
3885 , 'CS_SR_ATTRIBUTES'
3886 );
3887 FND_CONC_SUMMARIZER.Insert_Row
3888 (
3889 l_string
3890 , to_char(l_sr_attrs)
3891 );
3892
3893 l_string := FND_MESSAGE.Get_String
3894 (
3895 'CS'
3896 , 'CS_CONTACTS'
3897 );
3898 FND_CONC_SUMMARIZER.Insert_Row
3899 (
3900 l_string
3901 , to_char(l_sr_contacts)
3902 );
3903
3904 l_string := FND_MESSAGE.Get_String
3905 (
3906 'CS'
3907 , 'CS_CONTACT_ATTRIBS'
3908 );
3909 FND_CONC_SUMMARIZER.Insert_Row
3910 (
3911 l_string
3912 , to_char(l_sr_cont_attrs)
3916 (
3913 );
3914
3915 l_string := FND_MESSAGE.Get_String
3917 'CS'
3918 , 'CS_SR_LINKS'
3919 );
3920 FND_CONC_SUMMARIZER.Insert_Row
3921 (
3922 l_string
3923 , to_char(l_sr_links)
3924 );
3925
3926 l_string := FND_MESSAGE.Get_String
3927 (
3928 'CS'
3929 , 'CS_MESSAGES'
3930 );
3931 FND_CONC_SUMMARIZER.Insert_Row
3932 (
3933 l_string
3934 , to_char(l_sr_msgs)
3935 );
3936
3937 l_string := FND_MESSAGE.Get_String
3938 (
3939 'CS'
3940 , 'CS_KM_LINKS'
3941 );
3942 FND_CONC_SUMMARIZER.Insert_Row
3943 (
3944 l_string
3945 , to_char(l_sr_kb_links)
3946 );
3947
3948 l_string := FND_MESSAGE.Get_String
3949 (
3950 'CS'
3951 , 'CS_CHARGE_LINES'
3952 );
3953 FND_CONC_SUMMARIZER.Insert_Row
3954 (
3955 l_string
3956 , to_char(l_sr_estimates)
3957 );
3958
3959 l_string := FND_MESSAGE.Get_String
3960 (
3961 'CS'
3962 , 'CS_TASKS'
3963 );
3964 FND_CONC_SUMMARIZER.Insert_Row
3965 (
3966 l_string
3967 , to_char(l_sr_tasks)
3968 );
3969
3970 l_string := FND_MESSAGE.Get_String
3971 (
3972 'CS'
3973 , 'CS_NOTES'
3974 );
3975 FND_CONC_SUMMARIZER.Insert_Row
3976 (
3977 l_string
3978 , to_char(l_sr_notes)
3979 );
3980
3981 l_string := FND_MESSAGE.Get_String
3982 (
3983 'CS'
3984 , 'CS_INT_ACTIVITIES'
3985 );
3986 FND_CONC_SUMMARIZER.Insert_Row
3987 (
3988 l_string
3989 , to_char(l_sr_activities)
3990 );
3991
3992 l_string := FND_MESSAGE.Get_String
3993 (
3994 'CS'
3995 , 'CS_ATTACHMENTS'
3996 );
3997 FND_CONC_SUMMARIZER.Insert_Row
3998 (
3999 l_string
4000 , to_char(l_sr_attachs)
4001 );
4002
4003 l_string := FND_MESSAGE.Get_String
4004 (
4005 'CS'
4006 , 'CS_UWQ_WORK_ITEMS'
4007 );
4008 FND_CONC_SUMMARIZER.Insert_Row
4009 (
4010 l_string
4011 , to_char(l_sr_work_items)
4012 );
4013
4014 l_string := FND_MESSAGE.Get_String
4015 (
4016 'CS'
4017 , 'CS_AUDIT_RECORDS'
4018 );
4019 FND_CONC_SUMMARIZER.Insert_Row
4020 (
4021 l_string
4022 , to_char(l_sr_audits)
4023 );
4024
4025 l_string := FND_MESSAGE.Get_String
4026 (
4027 'CS'
4028 , 'CS_TOTAL_ROWS'
4029 );
4030 FND_CONC_SUMMARIZER.Insert_Row
4031 (
4032 l_string
4033 , to_char(l_sr_total_rows)
4034 );
4035
4036 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4037 THEN
4038 FND_LOG.String
4039 (
4040 FND_LOG.level_statement
4041 , L_LOG_MODULE || 'activity_summary_end'
4042 , 'after preparing the activity summary report'
4043 );
4044 END IF ;
4045
4046 ---
4047
4048 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4049 THEN
4050 FND_LOG.String
4051 (
4052 FND_LOG.level_procedure
4053 , L_LOG_MODULE || 'end'
4054 , 'Completed work in ' || L_API_NAME_FULL || ' with Success'
4055 );
4056 END IF ;
4057
4058 EXCEPTION
4059 WHEN OTHERS THEN
4060 FND_MESSAGE.Set_Name('CS', 'CS_SR_PURG_ACT_SUM_FAIL');
4061 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4062 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
4063 FND_MSG_PUB.ADD;
4064
4065 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4066 THEN
4067 FND_LOG.String
4068 (
4069 FND_LOG.level_unexpected
4070 , L_LOG_MODULE || 'when_others'
4071 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
4072 );
4073 FND_LOG.String
4074 (
4075 FND_LOG.level_unexpected
4076 , L_LOG_MODULE || 'when_others'
4077 , SQLERRM
4078 );
4079 END IF ;
4080 END Activity_Summarizer;
4081
4082 --------------------------------------------------------------------------------
4083 -- Procedure Name : VALIDATE_PURGE_PARAMS
4084 --
4085 -- Parameters (other than standard ones)
4086 --
4087 -- OUT
4088 --
4089 -- x_creation_from_date : If the p_creation_from_date is supplied, the
4090 -- validated and converted value is returned into
4091 -- this parameter
4092 -- x_creation_to_date : If the p_creation_to_date is supplied, the
4093 -- validated and converted value is returned into
4094 -- this parameter
4095 -- x_last_update_from_date : If the p_last_update_from_date is supplied, the
4096 -- validated and converted value is returned into
4097 -- this parameter
4098 -- x_last_update_to_date : If the p_last_update_to_date is supplied,
4099 -- the validated and converted value is returned
4100 -- into this parameter
4101 --
4102 -- IN
4103 --
4104 -- p_incident_id : Indicates that SR with this id needs
4105 -- to be purged
4106 -- p_incident_status_id : Indicates that SR with this status id
4107 -- needs to be purged
4108 -- p_incident_type_id : Indicates that SRs with this type id
4112 -- the SR to pick it up for purge
4109 -- needs to be purged
4110 -- p_creation_from_date : Indicates the lower end of the range of dates
4111 -- that need to be compared with CREATION_DATE of
4113 -- p_creation_to_date : Indicates the higher end of the range of dates
4114 -- that need to be compared with CREATION_DATE of
4115 -- the SR to pick it up for purge
4116 -- p_last_update_from_date : Indicates the lower end of the range of dates
4117 -- that need to be compared with LAST_UPDATED_DATE of
4118 -- the SR to pick it up for purge
4119 -- p_last_update_to_date : Indicates the higher end of the range of dates
4120 -- that need to be compared with LAST_UPDATED_DATE of
4121 -- the SR to pick it up for purge
4122 -- p_not_updated_since : This is a set of values like 1Y,2Y etc. which
4123 -- shall be compared with the LAST_UPDATED_DATE
4124 -- of the the SR to pick it up for purge
4125 -- p_customer_id : Indicates that SRs with this customer_id need
4126 -- to be purged.
4127 -- p_customer_acc_id : Indicates that SRs with this customer acc id
4128 -- need to be purged
4129 -- p_item_category_id : Indicates that SRs created for items falling
4130 -- under this category need to be purged
4131 -- p_inventory_item_id : Indicates that SRs created for this item
4132 -- need to be purged
4133 -- p_history_size : Number of customer SR's to retain while purging
4134 -- SRs identified using other parameters. This
4135 -- parameter alone CANNOT be used to identify a valid
4136 -- purgeset.
4137 -- p_number_of_workers : Number of workers that needs to be launched for
4138 -- purging Service Requests
4139 -- p_purge_batch_size : Number of Service Requests that needs to be purged
4140 -- in a batch
4141 -- p_purge_source_with_open_task :
4142 -- This signifies if the Tasks Validation API can
4143 -- delete tasks that are open. If this is N, only SRs
4144 -- linked to closed Tasks are allowed to be purged.
4145 -- If this is Y, all SRs, irrespective of whether the
4146 -- Tasks linked to them are open or closed, can
4147 -- be deleted.
4148 -- p_audit_required : This indicates if the SR Delete API should write
4149 -- the purge audit information. If this is N, no rows
4150 -- are inserted into the table
4151 -- CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y,
4152 -- audit rows are inserted into these tables.
4153 --
4154 -- Description
4155 -- This procedure performs validations on all the purge parameters.
4156 -- It uses global variables to set the values for the creation from/to
4157 -- dates and last updated from/to dates to avoid too many parameters
4158 -- being passed back and forth.
4159 --
4160 --
4161 -- HISTORY
4162 --
4163 ----------------+------------+--------------------------------------------------
4164 -- DATE | UPDATED BY | Change Description
4165 ----------------+------------+--------------------------------------------------
4166 -- 2-Aug_2005 | varnaray | Created
4167 -- | |
4168 ----------------+------------+--------------------------------------------------
4169 /*#
4170 * This procedure performs validations on all the purge parameters.
4171 * It uses global variables to set the values for the creation from/to
4172 * dates and last updated from/to dates to avoid too many parameters
4173 * being passed back and forth.
4174 * @param x_creation_from_date If the p_creation_from_date is supplied,
4175 * the validated and converted value is returned into this parameter
4176 * @param x_creation_to_date If the p_creation_to_date is supplied, the
4177 * validated and converted value is returned into this parameter
4178 * @param x_last_update_from_date If the p_last_update_from_date is supplied,
4179 * the validated and converted value is returned into this parameter
4180 * @param x_last_update_to_date If the p_last_update_to_date is supplied,
4181 * the validated and converted value is returned into this parameter
4182 * @param p_incident_id Indicates that SR with this id needs to be purged
4183 * @param p_incident_status_id Indicates that SR with this status id needs
4184 * to be purged
4185 * @param p_incident_type_id Indicates that SRs with this type id needs to
4186 * be purged
4187 * @param p_creation_from_date Indicates the lower end of the range of
4188 * dates that need to be compared with CREATION_DATE of the SR to pick
4189 * it up for purge
4190 * @param p_creation_to_date Indicates the higher end of the range of
4191 * dates that need to be compared with CREATION_DATE of the SR to pick
4192 * it up for purge
4193 * @param p_last_update_from_date Indicates the lower end of the range of
4194 * dates that need to be compared with LAST_UPDATED_DATE of the SR to
4195 * pick it up for purge
4196 * @param p_last_update_to_date Indicates the higher end of the range of
4197 * dates that need to be compared with LAST_UPDATED_DATE of the SR to pick
4198 * it up for purge
4199 * @param p_not_updated_since This is a set of values like 1Y,2Y etc.
4200 * which shall be compared with the LAST_UPDATED_DATE of the the SR to pick
4201 * it up for purge
4202 * @param p_customer_id Indicates that SRs with this customer_id need to
4203 * be purged.
4204 * @param p_customer_acc_id Indicates that SRs with this customer acc id
4205 * need to be purged
4206 * @param p_item_category_id Indicates that SRs created for items falling
4207 * under this category need to be purged
4211 * SRs identified using other parameters. This parameter alone CANNOT be
4208 * @param p_inventory_item_id Indicates that SRs created for this item
4209 * need to be purged
4210 * @param p_history_size Number of customer SR's to retain while purging
4212 * used to identify a valid purgeset.
4213 * @param p_number_of_workers Number of workers that needs to be launched
4214 * for purging Service Requests
4215 * @param p_purge_batch_size Number of Service Requests that needs to
4216 * be purged in a batch
4217 * @param p_purge_source_with_open_task This signifies if the Tasks
4218 * Validation API can delete tasks that are open. If this is N, only SRs
4219 * linked to closed Tasks are allowed to be purged. If this is Y, all SRs,
4220 * irrespective of whether the Tasks linked to them are open or closed,
4221 * can be deleted.
4222 * @param p_audit_required This indicates if the SR Delete API should write
4223 * the purge audit information. If this is N, no rows are inserted into the
4224 * table CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y, audit rows are
4225 * inserted into these tables.
4226 * @rep:scope internal
4227 * @rep:product CS
4228 * @rep:displayname Validate Purge Parameters
4229 */
4230 PROCEDURE Validate_Purge_Params
4231 (
4232 p_incident_id IN NUMBER
4233 , p_incident_status_id IN NUMBER
4234 , p_incident_type_id IN NUMBER
4235 , p_creation_from_date IN VARCHAR2
4236 , p_creation_to_date IN VARCHAR2
4237 , p_last_update_from_date IN VARCHAR2
4238 , p_last_update_to_date IN VARCHAR2
4239 , x_creation_from_date OUT NOCOPY DATE
4240 , x_creation_to_date OUT NOCOPY DATE
4241 , x_last_update_from_date OUT NOCOPY DATE
4242 , x_last_update_to_date OUT NOCOPY DATE
4243 , p_not_updated_since IN VARCHAR2
4244 , p_customer_id IN NUMBER
4245 , p_customer_acc_id IN NUMBER
4246 , p_item_category_id IN NUMBER
4247 , p_inventory_item_id IN NUMBER
4248 , p_history_size IN NUMBER
4249 , p_number_of_workers IN NUMBER
4250 , p_purge_batch_size IN NUMBER
4251 , p_purge_source_with_open_task IN VARCHAR2
4252 , p_audit_required IN VARCHAR2
4253 , x_msg_count OUT NOCOPY NUMBER
4254 , x_msg_data OUT NOCOPY VARCHAR2
4255 )
4256 IS
4257 --------------------------------------------------------------------------------
4258
4259 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_PURGE_PARAMS';
4260 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
4261 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
4262
4263 l_prompt VARCHAR2(250);
4264 l_not_updated_since VARCHAR2(10);
4265
4266 -- Variables used for calculating the
4267 -- interval value used for processing
4268 -- value in p_not_updated_since.
4269
4270 l_month_loc NUMBER;
4271 l_year_loc NUMBER;
4272 l_month_part NUMBER;
4273 l_year_part NUMBER;
4274 l_interval VARCHAR2(50);
4275
4276 -- Constant value containing the number that when added
4277 -- to a truncated date will point to the last second of
4278 -- the day.
4279 -- Eg., if a_date is '10-JAN-1999 14:01:02' then the
4280 -- TRUNC(a_date) will be '10-JAN-1999 00:00:00'. Adding
4281 -- the constant to this date will give '10-JAN-1999 23:59:59'.
4282
4283 TIME_23_59_59 CONSTANT NUMBER := 1 - 1 / (24*60*59);
4284
4285 l_str_month_part VARCHAR2(30);
4286 l_str_year_part VARCHAR2(30);
4287
4288 -- Function to check if a given value is
4289 -- numeric or not. This is used to find
4290 -- out if the format of value given in the
4291 -- lookup for NOT_UPDATED_SINCE is valid.
4292
4293 FUNCTION Is_Number
4294 (
4295 p_value IN VARCHAR2
4296 , p_result OUT NOCOPY NUMBER
4297 )
4298 RETURN BOOLEAN
4299 IS
4300 BEGIN
4301 p_result := TO_NUMBER(p_value);
4302 RETURN TRUE;
4303 EXCEPTION
4304 WHEN VALUE_ERROR THEN
4305 RETURN FALSE;
4306 END Is_Number;
4307 --------------------------------------------------------------------------------
4308
4309 BEGIN
4310 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4311 THEN
4312 FND_LOG.String
4313 (
4314 FND_LOG.level_procedure
4315 , L_LOG_MODULE || 'start'
4316 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
4317 );
4318 FND_LOG.String
4319 (
4320 FND_LOG.level_procedure
4321 , L_LOG_MODULE || 'param 1'
4322 , 'p_incident_id:' || p_incident_id
4323 );
4324 FND_LOG.String
4325 (
4326 FND_LOG.level_procedure
4327 , L_LOG_MODULE || 'param 2'
4328 , 'p_incident_status_id:' || p_incident_status_id
4329 );
4330 FND_LOG.String
4331 (
4332 FND_LOG.level_procedure
4333 , L_LOG_MODULE || 'param 3'
4334 , 'p_incident_type_id:' || p_incident_type_id
4335 );
4336 FND_LOG.String
4337 (
4338 FND_LOG.level_procedure
4339 , L_LOG_MODULE || 'param 4'
4340 , 'p_creation_from_date:' || p_creation_from_date
4341 );
4342 FND_LOG.String
4343 (
4344 FND_LOG.level_procedure
4345 , L_LOG_MODULE || 'param 5'
4346 , 'p_creation_to_date:' || p_creation_to_date
4347 );
4348 FND_LOG.String
4349 (
4353 );
4350 FND_LOG.level_procedure
4351 , L_LOG_MODULE || 'param 6'
4352 , 'p_last_update_from_date:' || p_last_update_from_date
4354 FND_LOG.String
4355 (
4356 FND_LOG.level_procedure
4357 , L_LOG_MODULE || 'param 7'
4358 , 'p_last_update_to_date:' || p_last_update_to_date
4359 );
4360 FND_LOG.String
4361 (
4362 FND_LOG.level_procedure
4363 , L_LOG_MODULE || 'param 8'
4364 , 'p_not_updated_since:' || p_not_updated_since
4365 );
4366 FND_LOG.String
4367 (
4368 FND_LOG.level_procedure
4369 , L_LOG_MODULE || 'param 9'
4370 , 'p_customer_id:' || p_customer_id
4371 );
4372 FND_LOG.String
4373 (
4374 FND_LOG.level_procedure
4375 , L_LOG_MODULE || 'param 10'
4376 , 'p_customer_acc_id:' || p_customer_acc_id
4377 );
4378 FND_LOG.String
4379 (
4380 FND_LOG.level_procedure
4381 , L_LOG_MODULE || 'param 11'
4382 , 'p_item_category_id:' || p_item_category_id
4383 );
4384 FND_LOG.String
4385 (
4386 FND_LOG.level_procedure
4387 , L_LOG_MODULE || 'param 12'
4388 , 'p_inventory_item_id:' || p_inventory_item_id
4389 );
4390 FND_LOG.String
4391 (
4392 FND_LOG.level_procedure
4393 , L_LOG_MODULE || 'param 13'
4394 , 'p_history_size:' || p_history_size
4395 );
4396 FND_LOG.String
4397 (
4398 FND_LOG.level_procedure
4399 , L_LOG_MODULE || 'param 14'
4400 , 'p_number_of_workers:' || p_number_of_workers
4401 );
4402 FND_LOG.String
4403 (
4404 FND_LOG.level_procedure
4405 , L_LOG_MODULE || 'param 15'
4406 , 'p_purge_batch_size:' || p_purge_batch_size
4407 );
4408 FND_LOG.String
4409 (
4410 FND_LOG.level_procedure
4411 , L_LOG_MODULE || 'param 16'
4412 , 'p_purge_source_with_open_task:' || p_purge_source_with_open_task
4413 );
4414 FND_LOG.String
4415 (
4416 FND_LOG.level_procedure
4417 , L_LOG_MODULE || 'param 17'
4418 , 'p_audit_required:' || p_audit_required
4419 );
4420 END IF ;
4421
4422 ---
4423
4424 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4425 THEN
4426 FND_LOG.String
4427 (
4428 FND_LOG.level_statement
4429 , L_LOG_MODULE || 'check_blind_purge_start'
4430 , 'checking for blind search'
4431 );
4432 END IF;
4433
4434 -- raising error if none of the parameters are entered
4435 -- while submitting the concurrent program
4436
4437 IF p_incident_id IS NULL
4438 AND p_incident_status_id IS NULL
4439 AND p_incident_type_id IS NULL
4440 AND p_creation_from_date IS NULL
4441 AND p_creation_to_date IS NULL
4442 AND p_last_update_from_date IS NULL
4443 AND p_last_update_to_date IS NULL
4444 AND p_not_updated_since IS NULL
4445 AND p_customer_id IS NULL
4446 AND p_customer_acc_id IS NULL
4447 AND p_item_category_id IS NULL
4448 AND p_inventory_item_id IS NULL
4449 AND p_history_size IS NULL
4450 THEN
4451 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4452 THEN
4453 FND_LOG.String
4454 (
4455 FND_LOG.level_unexpected
4456 , L_LOG_MODULE || 'no_params'
4457 , 'no parameters were supplied to the purge program'
4458 );
4459 END IF ;
4460
4461 FND_MESSAGE.Set_Name('CS', 'CS_SR_NO_PURGE_PARAMS');
4462 FND_MSG_PUB.ADD;
4463
4464 RAISE FND_API.G_EXC_ERROR;
4465 END IF;
4466
4467 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4468 THEN
4469 FND_LOG.String
4470 (
4471 FND_LOG.level_statement
4472 , L_LOG_MODULE || 'check_blind_purge_end'
4473 , 'after checking for blind search'
4474 );
4475 END IF;
4476
4477 ---
4478
4479 IF p_creation_from_date IS NOT NULL
4480 THEN
4481 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4482 THEN
4483 FND_LOG.String
4484 (
4485 FND_LOG.level_statement
4486 , L_LOG_MODULE || 'check_date_format_start_1'
4487 , 'checking if p_creation_from_date is in the format '
4488 || fnd_date.user_mask
4489 );
4490 END IF;
4491
4492 -- Check if p_creation_from_date is of the format
4493 -- as maintained in the profile option ICX_DATE_FORMAT
4494 -- and if not, throw an error.
4495
4496 x_creation_from_date := fnd_date.string_to_date
4497 (
4498 p_creation_from_date
4499 , fnd_date.user_mask
4500 );
4501
4502 IF x_creation_from_date IS NULL
4503 THEN
4504 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4505 THEN
4506 FND_LOG.String
4507 (
4508 FND_LOG.level_unexpected
4509 , L_LOG_MODULE || 'crtfrmdt_format_invalid'
4510 , 'format of field p_creation_from_date is invalid. should be '
4511 || fnd_date.user_mask
4512 );
4513 END IF ;
4514
4515 SELECT
4516 form_left_prompt
4517 INTO
4518 l_prompt
4519 FROM
4520 fnd_descr_flex_col_usage_vl
4521 WHERE
4522 end_user_column_name = 'P_CREATION_FROM_DATE'
4523 AND application_id = 170
4524 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
4525
4526 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_FORMAT_ERR');
4527 FND_MESSAGE.Set_Token('DATEFIELDNAME', l_prompt);
4531 RAISE FND_API.G_EXC_ERROR;
4528 FND_MESSAGE.Set_Token('FORMAT', fnd_date.user_mask);
4529 FND_MSG_PUB.ADD;
4530
4532 END IF;
4533
4534 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4535 THEN
4536 FND_LOG.String
4537 (
4538 FND_LOG.level_statement
4539 , L_LOG_MODULE || 'check_date_format_end_1'
4540 , 'after checking if p_creation_from_date is in the format '
4541 || fnd_date.user_mask
4542 );
4543 END IF;
4544 END IF;
4545
4546 ---
4547
4548 IF p_creation_to_date IS NOT NULL
4549 THEN
4550 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4551 THEN
4552 FND_LOG.String
4553 (
4554 FND_LOG.level_statement
4555 , L_LOG_MODULE || 'check_date_format_start_2'
4556 , 'checking if p_creation_to_date is in the format '
4557 || fnd_date.user_mask
4558 );
4559 END IF;
4560
4561 -- Check if p_creation_to_date is of the format
4562 -- as maintained in the profile option ICX_DATE_FORMAT
4563 -- and if not, throw an error.
4564
4565 x_creation_to_date := fnd_date.string_to_date
4566 (
4567 p_creation_to_date
4568 , fnd_date.user_mask
4569 );
4570
4571 IF x_creation_to_date IS NULL
4572 THEN
4573 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4574 THEN
4575 FND_LOG.String
4576 (
4577 FND_LOG.level_unexpected
4578 , L_LOG_MODULE || 'crttodt_format_invalid'
4579 , 'format of field p_creation_to_date is invalid. should be ' ||
4580 fnd_date.user_mask
4581 );
4582 END IF ;
4583
4584 SELECT
4585 form_left_prompt
4586 INTO
4587 l_prompt
4588 FROM
4589 fnd_descr_flex_col_usage_vl
4590 WHERE
4591 end_user_column_name = 'P_CREATION_TO_DATE'
4592 AND application_id = 170
4593 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
4594
4595 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_FORMAT_ERR');
4596 FND_MESSAGE.Set_Token('DATEFIELDNAME', l_prompt);
4597 FND_MESSAGE.Set_Token('FORMAT', fnd_date.user_mask);
4598 FND_MSG_PUB.ADD;
4599
4600 RAISE FND_API.G_EXC_ERROR;
4601 END IF;
4602
4603 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4604 THEN
4605 FND_LOG.String
4606 (
4607 FND_LOG.level_statement
4608 , L_LOG_MODULE || 'check_date_format_end_2'
4609 , 'after checking if p_creation_to_date is in the format ' ||
4610 fnd_date.user_mask
4611 );
4612 END IF;
4613
4614 ---
4615
4616 -- If the user_mask does not contain the time, then appending the time
4617 -- 23:59:59 to the date so that the whole day is covered. This is to
4618 -- take care of conditions where the from and to dates are the same day.
4619 --
4620 -- For ex., if the from date is 1-jan-1999 and to date is also 1-jan-1999,
4621 -- since there is no time given for these dates, the condition "where
4622 -- creation_date <= from_date and creation_date >= to_date" will not
4623 -- return any rows because if the creation date is 1-jan-1999 12:00:01,
4624 -- the condition creation_date >= from_date will be satisfied but the
4625 -- condition creation_date <= to_date will not be satisfied. In this
4626 -- situation, no rows will be picked up. To correct this issue, if the
4627 -- to_date contains the time 23:59:59, both the conditions will be
4628 -- satisfied.
4629
4630 IF TRUNC(x_creation_to_date) = x_creation_to_date
4631 THEN
4632 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4633 THEN
4634 FND_LOG.String
4635 (
4636 FND_LOG.level_statement
4637 , L_LOG_MODULE || 'add_time_to_todate_start'
4638 , 'adding time to x_creation_to_date if it does not have time'
4639 );
4640 END IF;
4641
4642 x_creation_to_date := x_creation_to_date + TIME_23_59_59;
4643
4644 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4645 THEN
4646 FND_LOG.String
4647 (
4648 FND_LOG.level_statement
4649 , L_LOG_MODULE || 'add_time_to_todate_end'
4650 , 'adding time to x_creation_to_date if it does not have time ' ||
4651 TO_CHAR(x_creation_to_date, 'DD-MON-YYYY HH24:MI:SS')
4652 );
4653 END IF;
4654 END IF;
4655 END IF;
4656
4657 ---
4658
4659 IF p_last_update_from_date IS NOT NULL
4660 THEN
4661 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4662 THEN
4663 FND_LOG.String
4664 (
4665 FND_LOG.level_statement
4666 , L_LOG_MODULE || 'check_date_format_start_3'
4667 , 'checking if p_last_update_from_date is in the format '
4668 || fnd_date.user_mask
4669 );
4670 END IF;
4671
4672 -- Check if p_last_update_from_date is of the format
4673 -- as maintained in the profile option ICX_DATE_FORMAT
4674 -- and if not, throw an error.
4675
4676 x_last_update_from_date := fnd_date.string_to_date
4677 (
4678 p_last_update_from_date
4679 , fnd_date.user_mask
4680 );
4681
4682 IF x_last_update_from_date IS NULL
4683 THEN
4684 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4685 THEN
4686 FND_LOG.String
4687 (
4688 FND_LOG.level_unexpected
4689 , L_LOG_MODULE || 'lstupdfrmdt_format_invalid'
4693 END IF ;
4690 , 'format of field p_last_update_from_date is invalid. should be '
4691 || fnd_date.user_mask
4692 );
4694
4695 SELECT
4696 form_left_prompt
4697 INTO
4698 l_prompt
4699 FROM
4700 fnd_descr_flex_col_usage_vl
4701 WHERE
4702 end_user_column_name = 'P_LAST_UPDATE_FROM_DATE'
4703 AND application_id = 170
4704 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
4705
4706 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_FORMAT_ERR');
4707 FND_MESSAGE.Set_Token('DATEFIELDNAME', l_prompt);
4708 FND_MESSAGE.Set_Token('FORMAT', fnd_date.user_mask);
4709 FND_MSG_PUB.ADD;
4710
4711 RAISE FND_API.G_EXC_ERROR;
4712 END IF;
4713
4714 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4715 THEN
4716 FND_LOG.String
4717 (
4718 FND_LOG.level_statement
4719 , L_LOG_MODULE || 'check_date_format_end_3'
4720 , 'after checking if p_last_update_from_date is in the format '
4721 || fnd_date.user_mask
4722 );
4723 END IF;
4724 END IF;
4725
4726 ---
4727
4728 IF p_last_update_to_date IS NOT NULL
4729 THEN
4730 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4731 THEN
4732 FND_LOG.String
4733 (
4734 FND_LOG.level_statement
4735 , L_LOG_MODULE || 'check_date_format_start_4'
4736 , 'checking if p_last_update_to_date is in the format '
4737 || fnd_date.user_mask
4738 );
4739 END IF;
4740
4741 -- Check if p_last_update_to_date is of the format
4742 -- as maintained in the profile option ICX_DATE_FORMAT
4743 -- and if not, throw an error.
4744
4745 x_last_update_to_date := fnd_date.string_to_date
4746 (
4747 p_last_update_to_date
4748 , fnd_date.user_mask
4749 );
4750
4751 IF x_last_update_to_date IS NULL
4752 THEN
4753 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4754 THEN
4755 FND_LOG.String
4756 (
4757 FND_LOG.level_unexpected
4758 , L_LOG_MODULE || 'lstupdtodt_format_invalid'
4759 , 'format of field p_last_update_to_date is invalid. should be '
4760 || fnd_date.user_mask
4761 );
4762 END IF ;
4763
4764 SELECT
4765 form_left_prompt
4766 INTO
4767 l_prompt
4768 FROM
4769 fnd_descr_flex_col_usage_vl
4770 WHERE
4771 end_user_column_name = 'P_LAST_UPDATE_TO_DATE'
4772 AND application_id = 170
4773 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
4774
4775 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_FORMAT_ERR');
4776 FND_MESSAGE.Set_Token('DATEFIELDNAME', l_prompt);
4777 FND_MESSAGE.Set_Token('FORMAT', fnd_date.user_mask);
4778 FND_MSG_PUB.ADD;
4779
4780 RAISE FND_API.G_EXC_ERROR;
4781 END IF;
4782
4783 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4784 THEN
4785 FND_LOG.String
4786 (
4787 FND_LOG.level_statement
4788 , L_LOG_MODULE || 'check_date_format_end_4'
4789 , 'after checking if p_last_update_to_date is in the format '
4790 || fnd_date.user_mask
4791 );
4792 END IF;
4793
4794 ---
4795
4796 -- If the user_mask does not contain the time, then appending
4797 -- time 23:59:59 to the date so that the whole day is covered.
4798
4799 IF TRUNC(x_last_update_to_date) = x_last_update_to_date
4800 THEN
4801 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4802 THEN
4803 FND_LOG.String
4804 (
4805 FND_LOG.level_statement
4806 , L_LOG_MODULE || 'add_time_to_todate_start'
4807 , 'adding time to x_last_update_to_date as it does not have time'
4808 );
4809 END IF;
4810
4811 x_last_update_to_date := x_last_update_to_date + TIME_23_59_59;
4812
4813 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4814 THEN
4815 FND_LOG.String
4816 (
4817 FND_LOG.level_statement
4818 , L_LOG_MODULE || 'add_time_to_todate_end'
4819 , 'after adding time to x_last_update_to_date as it '
4820 || 'does not have time '
4821 || TO_CHAR(x_last_update_to_date, 'DD-MON-YYYY HH24:MI:SS')
4822 );
4823 END IF;
4824 END IF;
4825 END IF;
4826
4827 ---
4828
4829 IF p_not_updated_since IS NOT NULL
4830 AND p_last_update_from_date IS NULL
4831 AND p_last_update_to_date IS NULL
4832
4833 -- Consider the p_not_updated_since parameter only
4834 -- if the parameters last_updated_from_date and
4835 -- last_updated_to_date are omitted. Otherwise, use
4836 -- the explisit values provided in the parameters.
4837
4838 THEN
4839
4840 -- Assign the value of p_not_updated_since to l_not_updated_since
4841 -- just to indicate that the value of the parameter p_not_updated_since
4842 -- is considered for framing the purge set.
4843
4844 l_not_updated_since := p_not_updated_since;
4845
4846 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4847 THEN
4848 FND_LOG.String
4849 (
4850 FND_LOG.level_statement
4851 , L_LOG_MODULE || 'not_updated_since_start'
4852 , 'computing last_updated_from_date and last_updated_to_date'
4853 );
4854 END IF;
4855
4859
4856 -- Resolving the value for the field p_not_updated_since. The following
4857 -- Table explains the buckets in which the SRs shall be arranged in order
4858 -- to pick them up as per the value chosen by the user.
4860 --+-----------------------------------+------------------------+
4861 --| Condition | Expression (YY-MM) |
4862 --+-----------------------------------+------------------------+
4863 --| Not updated since last 3 months | < SYSDATE - (00-03) |
4864 --| Not updated since last 6 months | < SYSDATE - (00-06) |
4865 --| Not updated between 0-1 year | < SYSDATE - (01-00) |
4866 --| Not updated between 0-2 years | < SYSDATE - (02-00) |
4867 --| Not updated between 0-3 years | < SYSDATE - (03-00) |
4868 --| Not updated between 0-4 years | < SYSDATE - (04-00) |
4869 --| Not updated between 0-5 years | < SYSDATE - (05-00) |
4870 --+-----------------------------------+------------------------+
4871
4872 -- The oracle function to_yminterval('yy-mm') takes a string containing
4873 -- the number of years and number of months and returns an interval value
4874 -- that could be added to or subtracted from a date to move that many
4875 -- years and months ahead or behind that date.
4876
4877 -- The following code assumes that the lookup setup for
4878 -- this parameter will have the values in the format <n>Y<n>M.
4879 -- Eg., 10 Years 3 Months will be created as 10Y3M.
4880 -- Just 3 months will be created as 3M.
4881
4882 l_month_loc := INSTR(p_not_updated_since, 'M');
4883 l_year_loc := INSTR(p_not_updated_since, 'Y');
4884
4885 IF l_month_loc = 0
4886 AND l_year_loc = 0
4887
4888 -- If the value specified for Not Updated Since, chosen
4889 -- from the LOV does not confirm to the format <n>Y<n>M
4890 -- then the following error is raised
4891
4892 THEN
4893 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4894 THEN
4895 FND_LOG.String
4896 (
4897 FND_LOG.level_unexpected
4898 , L_LOG_MODULE || 'month_year_loc_err_1'
4899 , 'error while getting the month/year combination from the lookup'
4900 );
4901 END IF;
4902
4903 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
4904 FND_MSG_PUB.ADD;
4905
4906 RAISE FND_API.G_EXC_ERROR;
4907 END IF;
4908
4909 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4910 THEN
4911 FND_LOG.String
4912 (
4913 FND_LOG.level_statement
4914 , L_LOG_MODULE || 'month_year_loc'
4915 , 'after getting month and year occurrence locations '
4916 || l_month_loc || ' ' || l_year_loc
4917 );
4918 END IF;
4919
4920 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4921 THEN
4922 FND_LOG.String
4923 (
4924 FND_LOG.level_statement
4925 , L_LOG_MODULE || 'month_year_part_start'
4926 , 'getting month and year values from p_not_updated_since'
4927 );
4928 END IF;
4929
4930 IF l_year_loc = 0
4931 AND l_month_loc > 0
4932 THEN
4933 l_str_month_part := SUBSTR(p_not_updated_since, 1, l_month_loc - 1);
4934 IF NOT Is_Number(l_str_month_part, l_month_part)
4935 OR l_month_loc < LENGTH(p_not_updated_since)
4936 THEN
4937 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4938 THEN
4939 FND_LOG.String
4940 (
4941 FND_LOG.level_unexpected
4942 , L_LOG_MODULE || 'month_year_loc_err_2'
4943 , 'error while getting the month/year combination from the lookup'
4944 );
4945 END IF;
4946
4947 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
4948 FND_MSG_PUB.ADD;
4949
4950 RAISE FND_API.G_EXC_ERROR;
4951 END IF;
4952 l_year_part := 0;
4953 ELSIF l_year_loc > 0
4954 AND l_month_loc = 0
4955 THEN
4956 l_month_part := 0;
4957 l_str_year_part := SUBSTR(p_not_updated_since, 1, l_year_loc - 1);
4958 IF NOT Is_Number(l_str_year_part, l_year_part)
4959 OR l_year_loc < LENGTH(p_not_updated_since)
4960 THEN
4961 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4962 THEN
4963 FND_LOG.String
4964 (
4965 FND_LOG.level_unexpected
4966 , L_LOG_MODULE || 'month_year_loc_err_3'
4967 , 'error while getting the month/year combination from the lookup'
4968 );
4969 END IF;
4970
4971 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
4972 FND_MSG_PUB.ADD;
4973
4974 RAISE FND_API.G_EXC_ERROR;
4975 END IF;
4976 ELSIF l_year_loc > 0
4977 AND l_month_loc > 0
4978 THEN
4979 IF l_month_loc > l_year_loc
4980 THEN
4981 l_str_year_part := SUBSTR(p_not_updated_since, 1, l_year_loc - 1);
4982 l_str_month_part := SUBSTR
4983 (
4984 p_not_updated_since
4985 , l_year_loc + 1
4986 , l_month_loc - l_year_loc - 1
4987 );
4988 IF NOT Is_Number(l_str_year_part, l_year_part)
4989 THEN
4990 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4991 THEN
4992 FND_LOG.String
4993 (
4994 FND_LOG.level_unexpected
4995 , L_LOG_MODULE || 'month_year_loc_err_5'
4996 , 'error while getting the month/year combination from the lookup'
4997 );
4998 END IF;
4999
5000 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
5004 END IF;
5001 FND_MSG_PUB.ADD;
5002
5003 RAISE FND_API.G_EXC_ERROR;
5005 IF NOT Is_Number(l_str_month_part, l_month_part)
5006 OR l_month_loc < LENGTH(p_not_updated_since)
5007 THEN
5008 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5009 THEN
5010 FND_LOG.String
5011 (
5012 FND_LOG.level_unexpected
5013 , L_LOG_MODULE || 'month_year_loc_err_4'
5014 , 'error while getting the month/year combination from the lookup'
5015 );
5016 END IF;
5017
5018 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
5019 FND_MSG_PUB.ADD;
5020
5021 RAISE FND_API.G_EXC_ERROR;
5022 END IF;
5023 ELSIF l_year_loc > l_month_loc
5024 THEN
5025 l_str_month_part := SUBSTR(p_not_updated_since, 1, l_month_loc - 1);
5026 l_str_year_part := SUBSTR
5027 (
5028 p_not_updated_since
5029 , l_month_loc + 1
5030 , l_year_loc - l_month_loc - 1
5031 );
5032 IF NOT Is_Number(l_str_month_part, l_month_part)
5033 THEN
5034 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5035 THEN
5036 FND_LOG.String
5037 (
5038 FND_LOG.level_unexpected
5039 , L_LOG_MODULE || 'month_year_loc_err_6'
5040 , 'error while getting the month/year combination from the lookup'
5041 );
5042 END IF;
5043
5044 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
5045 FND_MSG_PUB.ADD;
5046
5047 RAISE FND_API.G_EXC_ERROR;
5048 END IF;
5049 IF NOT Is_Number(l_str_year_part, l_year_part)
5050 OR l_year_loc < LENGTH(p_not_updated_since)
5051 THEN
5052 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5053 THEN
5054 FND_LOG.String
5055 (
5056 FND_LOG.level_unexpected
5057 , L_LOG_MODULE || 'month_year_loc_err_7'
5058 , 'error while getting the month/year combination from the lookup'
5059 );
5060 END IF;
5061
5062 FND_MESSAGE.Set_Name('CS', 'CS_SR_NOT_UPD_SINCE_INV');
5063 FND_MSG_PUB.ADD;
5064
5065 RAISE FND_API.G_EXC_ERROR;
5066 END IF;
5067 END IF ;
5068 END IF;
5069
5070 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5071 THEN
5072 FND_LOG.String
5073 (
5074 FND_LOG.level_statement
5075 , L_LOG_MODULE || 'month_year_part_literal'
5076 , 'after getting literal values for month and year parts ' ||
5077 l_month_part || ' ' || l_year_part
5078 );
5079 END IF;
5080
5081 -- If the month part is given more than 11, it means that
5082 -- it is going beyond a year. so this portion of the code
5083 -- makes the month < 12 and adds up Years to the extent
5084 -- required.
5085
5086 IF l_month_part > 11
5087 THEN
5088 l_year_part := l_year_part + FLOOR
5089 (
5090 l_month_part / 12
5091 );
5092 l_month_part := MOD
5093 (
5094 l_month_part
5095 , 12
5096 );
5097 END IF ;
5098
5099 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5100 THEN
5101 FND_LOG.String
5102 (
5103 FND_LOG.level_statement
5104 , L_LOG_MODULE || 'month_year_part_end'
5105 , 'after getting computed month and year values '
5106 || 'from p_not_updated_since ' ||
5107 l_month_part || ' ' || l_year_part
5108 );
5109 END IF;
5110
5111 l_interval := l_year_part || '-' || l_month_part;
5112
5113 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5114 THEN
5115 FND_LOG.String
5116 (
5117 FND_LOG.level_statement
5118 , L_LOG_MODULE || 'interval_formed'
5119 , 'after forming the interval to be used for the value ' ||
5120 p_not_updated_since || ' ' || l_interval
5121 );
5122 END IF;
5123
5124 -- Since the date computed out of p_not_updated_since does not contain time,
5125 -- appending time 23:59:59 to the date so that the whole day is covered.
5126
5127 x_last_update_to_date := trunc(SYSDATE)
5128 - to_yminterval(l_interval)
5129 + TIME_23_59_59;
5130 x_last_update_from_date := NULL;
5131
5132 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5133 THEN
5134 FND_LOG.String
5135 (
5136 FND_LOG.level_statement
5137 , L_LOG_MODULE || 'not_updated_since_1'
5138 , 'x_last_update_from_date:' || x_last_update_from_date
5139 );
5140 FND_LOG.String
5141 (
5142 FND_LOG.level_statement
5143 , L_LOG_MODULE || 'not_updated_since_2'
5144 , 'x_last_update_to_date:'
5145 || TO_CHAR(x_last_update_to_date, 'DD-MON-YYYY HH24:MI:SS')
5146 );
5147 FND_LOG.String
5148 (
5149 FND_LOG.level_statement
5150 , L_LOG_MODULE || 'not_updated_since_end'
5151 , 'after computing last_updated_from_date and last_updated_to_date'
5152 );
5153 END IF;
5154 END IF;
5155
5156 ---
5157
5158 IF x_creation_from_date IS NOT NULL
5159 THEN
5160 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5164 FND_LOG.level_statement
5161 THEN
5162 FND_LOG.String
5163 (
5165 , L_LOG_MODULE || 'check_crtdtfrom_start'
5166 , 'checking value for field x_creation_from_date'
5167 );
5168 END IF;
5169
5170 -- if the field x_creation_from_date is after sysdate
5171 -- throw an error since there will not be any SRs in
5172 -- that date range.
5173
5174 IF x_creation_from_date > SYSDATE
5175 THEN
5176 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5177 THEN
5178 FND_LOG.String
5179 (
5180 FND_LOG.level_unexpected
5181 , L_LOG_MODULE || 'crtdtfrom_invalid'
5182 , 'x_creation_from_date is invalid'
5183 );
5184 END IF ;
5185
5186 FND_MESSAGE.Set_Name('CS', 'CS_SR_CRTDT_FROM_ERR');
5187 FND_MSG_PUB.ADD;
5188
5189 RAISE FND_API.G_EXC_ERROR;
5190 END IF;
5191
5192 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5193 THEN
5194 FND_LOG.String
5195 (
5196 FND_LOG.level_statement
5197 , L_LOG_MODULE || 'check_crtdtfrom_end'
5198 , 'after checking value for field x_creation_from_date'
5199 );
5200 END IF;
5201 END IF;
5202
5203 ---
5204
5205 IF x_creation_to_date IS NOT NULL
5206 THEN
5207 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5208 THEN
5209 FND_LOG.String
5210 (
5211 FND_LOG.level_statement
5212 , L_LOG_MODULE || 'check_crtdtto_start'
5213 , 'checking value for field x_creation_to_date'
5214 );
5215 END IF;
5216
5217 -- if the field x_creation_to_date is after sysdate
5218 -- throw an error since there will not be any SRs in
5219 -- that date range.
5220
5221 IF x_creation_to_date > SYSDATE
5222 THEN
5223 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5224 THEN
5225 FND_LOG.String
5226 (
5227 FND_LOG.level_unexpected
5228 , L_LOG_MODULE || 'crtdtto_invalid'
5229 , 'x_creation_to_date is invalid'
5230 );
5231 END IF ;
5232
5233 FND_MESSAGE.Set_Name('CS', 'CS_SR_CRTDT_TO_ERR');
5234 FND_MSG_PUB.ADD;
5235
5236 RAISE FND_API.G_EXC_ERROR;
5237 END IF;
5238
5239 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5240 THEN
5241 FND_LOG.String
5242 (
5243 FND_LOG.level_statement
5244 , L_LOG_MODULE || 'check_crtdtto_end'
5245 , 'after checking value for field x_creation_to_date'
5246 );
5247 END IF;
5248 END IF;
5249
5250 ---
5251
5252 IF x_last_update_from_date IS NOT NULL
5253 THEN
5254 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5255 THEN
5256 FND_LOG.String
5257 (
5258 FND_LOG.level_statement
5259 , L_LOG_MODULE || 'check_lupdfromdt_start'
5260 , 'checking value for field x_last_update_from_date'
5261 );
5262 END IF;
5263
5264 -- if the field x_last_update_from_date is after sysdate
5265 -- throw an error since there will not be any SRs in
5266 -- that date range.
5267
5268 IF x_last_update_from_date > SYSDATE
5269 THEN
5270 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5271 THEN
5272 FND_LOG.String
5273 (
5274 FND_LOG.level_unexpected
5275 , L_LOG_MODULE || 'lupddtfrom_invalid'
5276 , 'x_last_update_from_date is invalid'
5277 );
5278 END IF ;
5279
5280 FND_MESSAGE.Set_Name('CS', 'CS_SR_MODDT_FROM_ERR');
5281 FND_MSG_PUB.ADD;
5282
5283 RAISE FND_API.G_EXC_ERROR;
5284 END IF;
5285
5286 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5287 THEN
5288 FND_LOG.String
5289 (
5290 FND_LOG.level_statement
5291 , L_LOG_MODULE || 'check_lupdfromdt_end'
5292 , 'after checking value for field x_last_update_from_date'
5293 );
5294 END IF;
5295 END IF;
5296
5297 ---
5298
5299 IF x_last_update_to_date IS NOT NULL
5300 THEN
5301 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5302 THEN
5303 FND_LOG.String
5304 (
5305 FND_LOG.level_statement
5306 , L_LOG_MODULE || 'check_lupdtodt_start'
5307 , 'checking value for field x_last_update_to_date'
5308 );
5309 END IF;
5310
5311 -- if the field x_last_update_to_date is after sysdate
5312 -- throw an error since there will not be any SRs in
5313 -- that date range.
5314
5315 IF x_last_update_to_date > SYSDATE
5316 THEN
5317 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5318 THEN
5319 FND_LOG.String
5320 (
5321 FND_LOG.level_unexpected
5322 , L_LOG_MODULE || 'lupddtto_invalid'
5323 , 'x_last_update_to_date is invalid'
5324 );
5325 END IF ;
5326
5327 FND_MESSAGE.Set_Name('CS', 'CS_SR_MODDT_TO_ERR');
5328 FND_MSG_PUB.ADD;
5329
5330 RAISE FND_API.G_EXC_ERROR;
5331 END IF;
5332
5333 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5334 THEN
5335 FND_LOG.String
5336 (
5337 FND_LOG.level_statement
5341 END IF;
5338 , L_LOG_MODULE || 'check_lupdtodt_end'
5339 , 'after checking value for field x_last_update_to_date'
5340 );
5342 END IF;
5343
5344 ---
5345
5346 IF x_creation_from_date IS NOT NULL
5347 AND x_creation_to_date IS NOT NULL
5348 THEN
5349 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5350 THEN
5351 FND_LOG.String
5352 (
5353 FND_LOG.level_statement
5354 , L_LOG_MODULE || 'date_crossvalid_start_1'
5355 , 'doing cross field validations x_creation_from_date '
5356 || '> x_creation_to_date '
5357 );
5358 END IF;
5359
5360 -- if both x_creation_from_date and x_creation_to_date are
5361 -- entered then x_creation_from_date should be before
5362 -- x_creation_to_date
5363
5364 IF x_creation_from_date > x_creation_to_date
5365 THEN
5366 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5367 THEN
5368 FND_LOG.String
5369 (
5370 FND_LOG.level_unexpected
5371 , L_LOG_MODULE || 'crtfrmdt_after_crttodt'
5372 , 'it is invalid to have x_creation_from_date > x_creation_to_date'
5373 );
5374 END IF ;
5375
5376 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_VALUE_ERR');
5377
5378 SELECT
5379 form_left_prompt
5380 INTO
5381 l_prompt
5382 FROM
5383 fnd_descr_flex_col_usage_vl
5384 WHERE
5385 end_user_column_name = 'P_CREATION_FROM_DATE'
5386 AND application_id = 170
5387 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5388
5389 FND_MESSAGE.Set_Token('DATEFIELDNAME1', l_prompt);
5390
5391 SELECT
5392 form_left_prompt
5393 INTO
5394 l_prompt
5395 FROM
5396 fnd_descr_flex_col_usage_vl
5397 WHERE
5398 end_user_column_name = 'P_CREATION_TO_DATE'
5399 AND application_id = 170
5400 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5401
5402 FND_MESSAGE.Set_Token('DATEFIELDNAME2', l_prompt);
5403 FND_MSG_PUB.ADD;
5404
5405 RAISE FND_API.G_EXC_ERROR;
5406 END IF;
5407
5408 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5409 THEN
5410 FND_LOG.String
5411 (
5412 FND_LOG.level_statement
5413 , L_LOG_MODULE || 'date_crossvalid_end_1'
5414 , 'after doing cross field validations x_creation_from_date > '
5415 || 'x_creation_to_date '
5416 );
5417 END IF;
5418 END IF;
5419
5420 ---
5421
5422 IF x_creation_from_date IS NOT NULL
5423 AND x_last_update_from_date IS NOT NULL
5424 THEN
5425 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5426 THEN
5427 FND_LOG.String
5428 (
5429 FND_LOG.level_statement
5430 , L_LOG_MODULE || 'date_crossvalid_start_2'
5431 , 'doing cross field validations x_creation_from_date > '
5432 || 'x_last_update_from_date '
5433 );
5434 END IF;
5435
5436 -- if both x_creation_from_date and x_last_update_from_date are
5437 -- entered then x_creation_from_date should be before
5438 -- x_last_update_from_date
5439
5440 IF x_creation_from_date > x_last_update_from_date
5441 THEN
5442 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5443 THEN
5444 FND_LOG.String
5445 (
5446 FND_LOG.level_unexpected
5447 , L_LOG_MODULE || 'crtfrmdt_after_lupdfrmdt'
5448 , 'it is invalid to have x_creation_from_date > '
5449 || 'x_last_update_from_date'
5450 );
5451 END IF ;
5452
5453 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_VALUE_ERR');
5454
5455 SELECT
5456 form_left_prompt
5457 INTO
5458 l_prompt
5459 FROM
5460 fnd_descr_flex_col_usage_vl
5461 WHERE
5462 end_user_column_name = 'P_CREATION_FROM_DATE'
5463 AND application_id = 170
5464 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5465
5466 FND_MESSAGE.Set_Token('DATEFIELDNAME1', l_prompt);
5467
5468 SELECT
5469 form_left_prompt
5470 INTO
5471 l_prompt
5472 FROM
5473 fnd_descr_flex_col_usage_vl
5474 WHERE
5475 end_user_column_name = DECODE
5476 (
5477 l_not_updated_since
5478 , NULL
5479 , 'P_LAST_UPDATE_FROM_DATE'
5480 , 'P_NOT_UPDATED_SINCE'
5481 )
5482 AND application_id = 170
5483 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5484
5485 FND_MESSAGE.Set_Token('DATEFIELDNAME2', l_prompt);
5486 FND_MSG_PUB.ADD;
5487
5488 RAISE FND_API.G_EXC_ERROR;
5489 END IF;
5490
5491 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5492 THEN
5493 FND_LOG.String
5494 (
5495 FND_LOG.level_statement
5496 , L_LOG_MODULE || 'date_crossvalid_end_2'
5497 , 'after doing cross field validations x_creation_from_date > '
5498 || 'x_last_update_from_date '
5499 );
5500 END IF;
5501 END IF;
5502
5503 ---
5504
5505 IF x_creation_from_date IS NOT NULL
5506 AND x_last_update_to_date IS NOT NULL
5507 THEN
5511 (
5508 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5509 THEN
5510 FND_LOG.String
5512 FND_LOG.level_statement
5513 , L_LOG_MODULE || 'date_crossvalid_start_3'
5514 , 'doing cross field validations x_creation_from_date > '
5515 || 'x_last_update_to_date'
5516 );
5517 END IF;
5518
5519 -- if both x_creation_from_date and x_last_update_to_date are
5520 -- entered then x_creation_from_date should be before
5521 -- x_last_update_to_date
5522
5523 IF x_creation_from_date > x_last_update_to_date
5524 THEN
5525 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5526 THEN
5527 FND_LOG.String
5528 (
5529 FND_LOG.level_unexpected
5530 , L_LOG_MODULE || 'crttodt_after_lupdtodt'
5531 , 'it is invalid to have x_creation_from_date > '
5532 || 'x_last_update_to_date'
5533 );
5534 END IF ;
5535
5536 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_VALUE_ERR');
5537
5538 SELECT
5539 form_left_prompt
5540 INTO
5541 l_prompt
5542 FROM
5543 fnd_descr_flex_col_usage_vl
5544 WHERE
5545 end_user_column_name = 'P_CREATION_FROM_DATE'
5546 AND application_id = 170
5547 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5548
5549 FND_MESSAGE.Set_Token('DATEFIELDNAME1', l_prompt);
5550
5551 SELECT
5552 form_left_prompt
5553 INTO
5554 l_prompt
5555 FROM
5556 fnd_descr_flex_col_usage_vl
5557 WHERE
5558 end_user_column_name = DECODE
5559 (
5560 l_not_updated_since
5561 , NULL
5562 , 'P_LAST_UPDATE_TO_DATE'
5563 , 'P_NOT_UPDATED_SINCE'
5564 )
5565 AND application_id = 170
5566 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5567
5568 FND_MESSAGE.Set_Token('DATEFIELDNAME2', l_prompt);
5569 FND_MSG_PUB.ADD;
5570
5571 RAISE FND_API.G_EXC_ERROR;
5572 END IF;
5573
5574 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5575 THEN
5576 FND_LOG.String
5577 (
5578 FND_LOG.level_statement
5579 , L_LOG_MODULE || 'date_crossvalid_end_3'
5580 , 'after doing cross field validations x_creation_from_date > '
5581 || 'x_last_update_to_date'
5582 );
5583 END IF;
5584 END IF;
5585
5586 ---
5587
5588 IF x_creation_to_date IS NOT NULL
5589 AND x_last_update_to_date IS NOT NULL
5590 THEN
5591 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5592 THEN
5593 FND_LOG.String
5594 (
5595 FND_LOG.level_statement
5596 , L_LOG_MODULE || 'date_crossvalid_start_4'
5597 , 'doing cross field validations x_creation_to_date > '
5598 || 'x_last_update_to_date'
5599 );
5600 END IF;
5601
5602 -- if both x_creation_to_date and x_last_update_to_date are
5603 -- entered then x_creation_to_date should be before
5604 -- x_last_update_to_date
5605
5606 IF x_creation_to_date > x_last_update_to_date
5607 THEN
5608 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5609 THEN
5610 FND_LOG.String
5611 (
5612 FND_LOG.level_unexpected
5613 , L_LOG_MODULE || 'crttodt_after_lupdtodt'
5614 , 'it is invalid to have x_creation_to_date > x_last_update_to_date'
5615 );
5616 END IF ;
5617
5618 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_VALUE_ERR');
5619
5620 SELECT
5621 form_left_prompt
5622 INTO
5623 l_prompt
5624 FROM
5625 fnd_descr_flex_col_usage_vl
5626 WHERE
5627 end_user_column_name = 'P_CREATION_TO_DATE'
5628 AND application_id = 170
5629 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5630
5631 FND_MESSAGE.Set_Token('DATEFIELDNAME1', l_prompt);
5632
5633 SELECT
5634 form_left_prompt
5635 INTO
5636 l_prompt
5637 FROM
5638 fnd_descr_flex_col_usage_vl
5639 WHERE
5640 end_user_column_name = DECODE
5641 (
5642 l_not_updated_since
5643 , NULL
5644 , 'P_LAST_UPDATE_TO_DATE'
5645 , 'P_NOT_UPDATED_SINCE'
5646 )
5647 AND application_id = 170
5648 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5649
5650 FND_MESSAGE.Set_Token('DATEFIELDNAME2', l_prompt);
5651 FND_MSG_PUB.ADD;
5652
5653 RAISE FND_API.G_EXC_ERROR;
5654 END IF;
5655
5656 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5657 THEN
5658 FND_LOG.String
5659 (
5660 FND_LOG.level_statement
5661 , L_LOG_MODULE || 'date_crossvalid_end_4'
5662 , 'after doing cross field validations x_creation_to_date > '
5663 || 'x_last_update_to_date'
5664 );
5665 END IF;
5666 END IF;
5667
5668 ---
5669
5670 IF x_last_update_from_date IS NOT NULL
5671 AND x_last_update_to_date IS NOT NULL
5672 THEN
5673 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5674 THEN
5675 FND_LOG.String
5676 (
5677 FND_LOG.level_statement
5678 , L_LOG_MODULE || 'date_crossvalid_start_5'
5679 , 'doing cross field validations x_last_update_from_date > '
5680 || 'x_last_update_to_date'
5681 );
5682 END IF;
5683
5687
5684 -- if both x_last_update_from_date and x_last_update_to_date are
5685 -- entered then x_last_update_from_date should be before
5686 -- x_last_update_to_date
5688 IF x_last_update_from_date > x_last_update_to_date
5689 THEN
5690 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
5691 THEN
5692 FND_LOG.String
5693 (
5694 FND_LOG.level_unexpected
5695 , L_LOG_MODULE || 'lupdfrmdt_after_lupdtodt'
5696 , 'it is invalid to have x_last_update_from_date > '
5697 || 'x_last_update_to_date'
5698 );
5699 END IF ;
5700
5701 FND_MESSAGE.Set_Name('CS', 'CS_SR_DATE_VALUE_ERR');
5702
5703 SELECT
5704 form_left_prompt
5705 INTO
5706 l_prompt
5707 FROM
5708 fnd_descr_flex_col_usage_vl
5709 WHERE
5710 end_user_column_name = 'P_LAST_UPDATE_FROM_DATE'
5711 AND application_id = 170
5712 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5713
5714 FND_MESSAGE.Set_Token('DATEFIELDNAME1', l_prompt);
5715
5716 SELECT
5717 form_left_prompt
5718 INTO
5719 l_prompt
5720 FROM
5721 fnd_descr_flex_col_usage_vl
5722 WHERE
5723 end_user_column_name = 'P_LAST_UPDATE_TO_DATE'
5724 AND application_id = 170
5725 AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
5726
5727 FND_MESSAGE.Set_Token('DATEFIELDNAME2', l_prompt);
5728 FND_MSG_PUB.ADD;
5729
5730 RAISE FND_API.G_EXC_ERROR;
5731 END IF;
5732
5733 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
5734 THEN
5735 FND_LOG.String
5736 (
5737 FND_LOG.level_statement
5738 , L_LOG_MODULE || 'date_crossvalid_end_5'
5739 , 'after doing cross field validations x_last_update_from_date > '
5740 || 'x_last_update_to_date'
5741 );
5742 END IF;
5743 END IF;
5744
5745 ---
5746
5747 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
5748 THEN
5749 FND_LOG.String
5750 (
5751 FND_LOG.level_procedure
5752 , L_LOG_MODULE || 'end'
5753 , 'Completed work in ' || L_API_NAME_FULL || ' with Success'
5754 );
5755 END IF ;
5756
5757 END Validate_Purge_Params;
5758
5759 --------------------------------------------------------------------------------
5760 -- Procedure Name : FORM_AND_EXEC_STATEMENT
5761 --
5762 -- Parameters (other than standard ones)
5763 --
5764 -- IN
5765 --
5766 -- p_request_id : Concurrent Request Id of the
5767 -- current request
5768 -- p_incident_id : Indicates that SR with this id needs
5769 -- to be purged
5770 -- p_incident_status_id : Indicates that SR with this status id
5771 -- needs to be purged
5772 -- p_incident_type_id : Indicates that SRs with this type id
5773 -- needs to be purged
5774 -- p_creation_from_date : Indicates the lower end of the range of dates
5775 -- that need to be compared with CREATION_DATE of
5776 -- the SR to pick it up for purge
5777 -- p_creation_to_date : Indicates the higher end of the range of dates
5778 -- that need to be compared with CREATION_DATE of
5779 -- the SR to pick it up for purge
5780 -- p_last_update_from_date : Indicates the lower end of the range of dates
5781 -- that need to be compared with LAST_UPDATED_DATE of
5782 -- the SR to pick it up for purge
5783 -- p_last_update_to_date : Indicates the higher end of the range of dates
5784 -- that need to be compared with LAST_UPDATED_DATE of
5785 -- the SR to pick it up for purge
5786 -- p_customer_id : Indicates that SRs with this customer_id need
5787 -- to be purged.
5788 -- p_customer_acc_id : Indicates that SRs with this customer acc id
5789 -- need to be purged
5790 -- p_item_category_id : Indicates that SRs created for items falling
5791 -- under this category need to be purged
5792 -- p_inventory_item_id : Indicates that SRs created for this item
5793 -- need to be purged
5794 -- p_history_size : Number of customer SR's to retain while purging
5795 -- SRs identified using other parameters. This
5796 -- parameter alone CANNOT be used to identify a
5797 -- valid purgeset.
5798 -- p_number_of_workers : Number of workers that needs to be launched for
5799 -- purging Service Requests
5800 --
5801 -- OUT
5802 --
5803 -- p_row_count : Number of rows inserted into the staging table
5804 --
5805 -- Description
5806 -- This procedure takes all the validated concurrent request parameters
5807 -- and based on their availability constructs and executes an SQL statement
5808 -- that inserts SR ids that can be purged into the staging table. Bind
5809 -- variables are created and used in the dynamic SQL.
5810 --
5811 -- HISTORY
5812 --
5813 ----------------+------------+--------------------------------------------------
5814 -- DATE | UPDATED BY | Change Description
5815 ----------------+------------+--------------------------------------------------
5816 -- 2-Aug_2005 | varnaray | Created
5817 -- | |
5818 ----------------+------------+--------------------------------------------------
5819 /*#
5820 * This procedure takes all the validated concurrent request parameters and
5824 * @param p_request_id Concurrent Request Id of the current request
5821 * based on their availability constructs and executes an SQL statement that
5822 * inserts SR ids that can be purged into the staging table. Bind variables are
5823 * created and used in the dynamic SQL.
5825 * @param p_incident_id Indicates that SR with this id needs to be purged
5826 * @param p_incident_status_id Indicates that SR with this status id needs to
5827 * be purged
5828 * @param p_incident_type_id Indicates that SRs with this type id needs to be
5829 * purged
5830 * @param p_creation_from_date Indicates the lower end of the range of dates
5831 * that need to be compared with CREATION_DATE of the SR to pick it up for
5832 * purge
5833 * @param p_creation_to_date Indicates the higher end of the range of dates
5834 * that need to be compared with CREATION_DATE of the SR to pick it up for purge
5835 * @param p_last_update_from_date Indicates the lower end of the range of dates
5836 * that need to be compared with LAST_UPDATED_DATE of the SR to pick it up for
5837 * purge
5838 * @param p_last_update_to_date Indicates the higher end of the range of dates
5839 * that need to be compared with LAST_UPDATED_DATE of the SR to pick it up for
5840 * purge
5841 * @param p_customer_id Indicates that SRs with this customer_id need to be
5842 * purged.
5843 * @param p_customer_acc_id Indicates that SRs with this customer acc id need
5844 * to be purged
5845 * @param p_item_category_id Indicates that SRs created for items falling under
5846 * this category need to be purged
5847 * @param p_inventory_item_id Indicates that SRs created for this item need to
5848 * be purged
5849 * @param p_history_size Number of customer SR's to retain while purging SRs
5850 * identified using other parameters. This parameter alone CANNOT be used to
5851 * identify a valid purgeset.
5852 * @param p_number_of_workers Number of workers that needs to be launched for
5853 * purging Service Requests
5854 * @param p_row_count Number of rows inserted into the staging table
5855 * @rep:scope internal
5856 * @rep:product CS
5857 * @rep:displayname Form and Execute SQL Statement
5858 */
5859 PROCEDURE Form_And_Exec_Statement
5860 (
5861 p_incident_id IN NUMBER
5862 , p_incident_status_id IN NUMBER
5863 , p_incident_type_id IN NUMBER
5864 , p_creation_from_date IN DATE
5865 , p_creation_to_date IN DATE
5866 , p_last_update_from_date IN DATE
5867 , p_last_update_to_date IN DATE
5868 , p_customer_id IN NUMBER
5869 , p_customer_acc_id IN NUMBER
5870 , p_item_category_id IN NUMBER
5871 , p_inventory_item_id IN NUMBER
5872 , p_history_size IN NUMBER
5873 , p_number_of_workers IN OUT NOCOPY NUMBER
5874 , p_purge_batch_size IN NUMBER
5875 , p_request_id IN NUMBER
5876 , p_row_count OUT NOCOPY NUMBER
5877 )
5878 IS
5879 --------------------------------------------------------------------------------
5880
5881 L_API_NAME CONSTANT VARCHAR2(30) := 'FORM_AND_EXEC_STATEMENT';
5882 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
5883 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
5884
5885 -- PL/SQL tables defined for holding bind variables for the
5886 -- where clause which is constructed and added to the main
5887 -- SQL that will identify SRs that need to be purged
5888
5889 TYPE t_bind_var_val_arr IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
5890 TYPE t_bind_var_type_arr IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
5891 TYPE t_where_clause_arr IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
5892
5893 -- Actual number of worker concurrent requests
5894 -- to be started based on the number of SRs in
5895 -- the purgeset.
5896
5897 l_number_of_workers NUMBER;
5898
5899 -- variables used to hold intermediate information that
5900 -- is used to construct a full-blown SQL statement by
5901 -- collecting all the purge parameters and making a
5902 -- predicate out of them
5903
5904 l_bind_var_val_arr t_bind_var_val_arr;
5905 l_bind_var_type_arr t_bind_var_type_arr;
5906 l_where_clause_arr t_where_clause_arr;
5907 l_bind_var_ctr NUMBER;
5908
5909 l_sql_statement VARCHAR2(10000);
5910 l_where_clause VARCHAR2(4000);
5911 l_dbms_sql_cursor NUMBER;
5912
5913 l_row_count NUMBER;
5914
5915 BEGIN
5916 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
5917 THEN
5918 FND_LOG.String
5919 (
5920 FND_LOG.level_procedure
5921 , L_LOG_MODULE || 'start'
5922 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
5923 );
5924 FND_LOG.String
5925 (
5926 FND_LOG.level_procedure
5927 , L_LOG_MODULE || 'param 1'
5928 , 'p_incident_id:' || p_incident_id
5929 );
5930 FND_LOG.String
5931 (
5932 FND_LOG.level_procedure
5933 , L_LOG_MODULE || 'param 2'
5934 , 'p_incident_status_id:' || p_incident_status_id
5935 );
5936 FND_LOG.String
5937 (
5938 FND_LOG.level_procedure
5939 , L_LOG_MODULE || 'param 3'
5940 , 'p_incident_type_id:' || p_incident_type_id
5941 );
5942 FND_LOG.String
5943 (
5944 FND_LOG.level_procedure
5945 , L_LOG_MODULE || 'param 4'
5946 , 'p_creation_from_date:' || p_creation_from_date
5947 );
5948 FND_LOG.String
5949 (
5950 FND_LOG.level_procedure
5951 , L_LOG_MODULE || 'param 5'
5955 (
5952 , 'p_creation_to_date:' || p_creation_to_date
5953 );
5954 FND_LOG.String
5956 FND_LOG.level_procedure
5957 , L_LOG_MODULE || 'param 6'
5958 , 'p_last_update_from_date:' || p_last_update_from_date
5959 );
5960 FND_LOG.String
5961 (
5962 FND_LOG.level_procedure
5963 , L_LOG_MODULE || 'param 7'
5964 , 'p_last_update_to_date:' || p_last_update_to_date
5965 );
5966 FND_LOG.String
5967 (
5968 FND_LOG.level_procedure
5969 , L_LOG_MODULE || 'param 8'
5970 , 'p_customer_id:' || p_customer_id
5971 );
5972 FND_LOG.String
5973 (
5974 FND_LOG.level_procedure
5975 , L_LOG_MODULE || 'param 8'
5976 , 'p_customer_acc_id:' || p_customer_acc_id
5977 );
5978 FND_LOG.String
5979 (
5980 FND_LOG.level_procedure
5981 , L_LOG_MODULE || 'param 10'
5982 , 'p_item_category_id:' || p_item_category_id
5983 );
5984 FND_LOG.String
5985 (
5986 FND_LOG.level_procedure
5987 , L_LOG_MODULE || 'param 11'
5988 , 'p_inventory_item_id:' || p_inventory_item_id
5989 );
5990 FND_LOG.String
5991 (
5992 FND_LOG.level_procedure
5993 , L_LOG_MODULE || 'param 12'
5994 , 'p_history_size:' || p_history_size
5995 );
5996 FND_LOG.String
5997 (
5998 FND_LOG.level_procedure
5999 , L_LOG_MODULE || 'param 13'
6000 , 'p_number_of_workers:' || p_number_of_workers
6001 );
6002 END IF;
6003
6004 -- Initializing the bind variable counter to 0.
6005 -- This variable will be incremented for each
6006 -- bind variable that is added to the where clause.
6007
6008 l_bind_var_ctr := 0;
6009
6010 ---
6011
6012 IF p_incident_id IS NOT NULL
6013 THEN
6014 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6015 THEN
6016 FND_LOG.String
6017 (
6018 FND_LOG.level_statement
6019 , L_LOG_MODULE || 'where_incident_id_start'
6020 , 'Framing where clause for incident_id'
6021 );
6022 END IF;
6023
6024 -- frame the where clause for the parameter p_incident_id
6025 -- along with the bind variable value into the plsql tables
6026
6027 l_bind_var_ctr := l_bind_var_ctr + 1;
6028 l_where_clause_arr(l_bind_var_ctr) := ' incident_id = :bind'
6029 || l_bind_var_ctr || ' ';
6030 l_bind_var_val_arr(l_bind_var_ctr) := p_incident_id;
6031 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6032
6033 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6034 THEN
6035 FND_LOG.String
6036 (
6037 FND_LOG.level_statement
6038 , L_LOG_MODULE || 'where_incident_id_1'
6039 , 'l_where_clause_arr(l_bind_var_ctr):'
6040 || l_where_clause_arr(l_bind_var_ctr)
6041 );
6042 FND_LOG.String
6043 (
6044 FND_LOG.level_statement
6045 , L_LOG_MODULE || 'where_incident_id_2'
6046 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6047 || l_bind_var_val_arr(l_bind_var_ctr)
6048 );
6049 FND_LOG.String
6050 (
6051 FND_LOG.level_statement
6052 , L_LOG_MODULE || 'where_incident_id_end'
6053 , 'After framing where clause for incident_id'
6054 || l_where_clause_arr(l_bind_var_ctr)
6055 );
6056 END IF;
6057 END IF;
6058
6059 ---
6060
6061 IF p_incident_status_id IS NOT NULL
6062 THEN
6063 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6064 THEN
6065 FND_LOG.String
6066 (
6067 FND_LOG.level_statement
6068 , L_LOG_MODULE || 'where_status_id_start'
6069 , 'framing where clause for status_id'
6070 );
6071 END IF;
6072
6073 -- frame the where clause for the parameter p_incident_status_id
6074 -- along with the bind variable value into the plsql tables
6075
6076 l_bind_var_ctr := l_bind_var_ctr + 1;
6077 l_where_clause_arr(l_bind_var_ctr) := ' incident_status_id = :bind'
6078 || l_bind_var_ctr || ' ';
6079 l_bind_var_val_arr(l_bind_var_ctr) := p_incident_status_id;
6080 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6081
6082 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6083 THEN
6084 FND_LOG.String
6085 (
6086 FND_LOG.level_statement
6087 , L_LOG_MODULE || 'where_status_id_1'
6088 , 'l_where_clause_arr(l_bind_var_ctr):'
6089 || l_where_clause_arr(l_bind_var_ctr)
6090 );
6091 FND_LOG.String
6092 (
6093 FND_LOG.level_statement
6094 , L_LOG_MODULE || 'where_status_id_2'
6095 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6096 || l_bind_var_val_arr(l_bind_var_ctr)
6097 );
6098 FND_LOG.String
6099 (
6100 FND_LOG.level_statement
6101 , L_LOG_MODULE || 'where_status_id_end'
6102 , 'after framing where clause for status_id '
6103 || l_where_clause_arr(l_bind_var_ctr)
6104 );
6105 END IF;
6106 END IF;
6107
6108 ---
6109
6110 IF p_incident_type_id IS NOT NULL
6111 THEN
6112 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6113 THEN
6114 FND_LOG.String
6115 (
6116 FND_LOG.level_statement
6117 , L_LOG_MODULE || 'where_type_id_start'
6118 , 'framing where clause for p_incident_type_id'
6119 );
6120 END IF;
6121
6122 -- frame the where clause for the parameter p_incident_type_id
6126 l_where_clause_arr(l_bind_var_ctr) := ' incident_type_id = :bind'
6123 -- along with the bind variable value into the plsql tables
6124
6125 l_bind_var_ctr := l_bind_var_ctr + 1;
6127 || l_bind_var_ctr || ' ';
6128 l_bind_var_val_arr(l_bind_var_ctr) := p_incident_type_id;
6129 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6130
6131 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6132 THEN
6133 FND_LOG.String
6134 (
6135 FND_LOG.level_statement
6136 , L_LOG_MODULE || 'where_type_id_1'
6137 , 'l_where_clause_arr(l_bind_var_ctr):'
6138 || l_where_clause_arr(l_bind_var_ctr)
6139 );
6140 FND_LOG.String
6141 (
6142 FND_LOG.level_statement
6143 , L_LOG_MODULE || 'where_type_id_2'
6144 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6145 || l_bind_var_val_arr(l_bind_var_ctr)
6146 );
6147 FND_LOG.String
6148 (
6149 FND_LOG.level_statement
6150 , L_LOG_MODULE || 'where_type_id_end'
6151 , 'after framing where clause for p_incident_type_id'
6152 );
6153 END IF;
6154 END IF;
6155
6156
6157 ---
6158
6159 IF p_creation_from_date IS NOT NULL
6160 THEN
6161 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6162 THEN
6163 FND_LOG.String
6164 (
6165 FND_LOG.level_statement
6166 , L_LOG_MODULE || 'where_creation_from_date_start'
6167 , 'framing where clause for p_creation_from_date'
6168 );
6169 END IF;
6170
6171 -- frame the where clause for the parameter p_creation_from_date
6172 -- along with the bind variable value into the plsql tables
6173
6174 l_bind_var_ctr := l_bind_var_ctr + 1;
6175 l_where_clause_arr(l_bind_var_ctr) := ' creation_date >= :bind'
6176 || l_bind_var_ctr || ' ';
6177 l_bind_var_val_arr(l_bind_var_ctr) := TO_CHAR
6178 (
6179 p_creation_from_date
6180 , 'DD-MM-RRRR HH24:MI:SS'
6181 );
6182 l_bind_var_type_arr(l_bind_var_ctr) := 'D';
6183
6184 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6185 THEN
6186 FND_LOG.String
6187 (
6188 FND_LOG.level_statement
6189 , L_LOG_MODULE || 'where_creation_from_date_1'
6190 , 'l_where_clause_arr(l_bind_var_ctr):'
6191 || l_where_clause_arr(l_bind_var_ctr)
6192 );
6193 FND_LOG.String
6194 (
6195 FND_LOG.level_statement
6196 , L_LOG_MODULE || 'where_creation_from_date_2'
6197 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6198 || l_bind_var_val_arr(l_bind_var_ctr)
6199 );
6200 FND_LOG.String
6201 (
6202 FND_LOG.level_statement
6203 , L_LOG_MODULE || 'where_creation_from_date_end'
6204 , 'after framing where clause for p_creation_from_date'
6205 );
6206 END IF;
6207 END IF;
6208
6209 ---
6210
6211 IF p_creation_to_date IS NOT NULL
6212 THEN
6213 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6214 THEN
6215 FND_LOG.String
6216 (
6217 FND_LOG.level_statement
6218 , L_LOG_MODULE || 'where_creation_to_date_start'
6219 , 'framing where clause for p_creation_to_date'
6220 );
6221 END IF;
6222
6223 -- frame the where clause for the parameter p_creation_to_date
6224 -- along with the bind variable value into the plsql tables
6225
6226 l_bind_var_ctr := l_bind_var_ctr + 1;
6227 l_where_clause_arr(l_bind_var_ctr) := ' creation_date <= :bind'
6228 || l_bind_var_ctr || ' ';
6229 l_bind_var_val_arr(l_bind_var_ctr) := TO_CHAR
6230 (
6231 p_creation_to_date
6232 , 'DD-MM-RRRR HH24:MI:SS'
6233 );
6234 l_bind_var_type_arr(l_bind_var_ctr) := 'D';
6235
6236 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6237 THEN
6238 FND_LOG.String
6239 (
6240 FND_LOG.level_statement
6241 , L_LOG_MODULE || 'where_creation_to_date_1'
6242 , 'l_where_clause_arr(l_bind_var_ctr):'
6243 || l_where_clause_arr(l_bind_var_ctr)
6244 );
6245 FND_LOG.String
6246 (
6247 FND_LOG.level_statement
6248 , L_LOG_MODULE || 'where_creation_to_date_2'
6249 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6250 || l_bind_var_val_arr(l_bind_var_ctr)
6251 );
6252 FND_LOG.String
6253 (
6254 FND_LOG.level_statement
6255 , L_LOG_MODULE || 'where_creation_to_date_end'
6256 , 'after framing where clause for p_creation_to_date'
6257 );
6258 END IF;
6259 END IF;
6260
6261 ---
6262
6263 IF p_last_update_from_date IS NOT NULL
6264 THEN
6265 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6266 THEN
6267 FND_LOG.String
6268 (
6269 FND_LOG.level_statement
6270 , L_LOG_MODULE || 'where_last_update_from_date_start'
6271 , 'framing where clause for p_last_update_from_date'
6272 );
6273 END IF;
6274
6275 -- frame the where clause for the parameter p_last_update_from_date
6276 -- along with the bind variable value into the plsql tables
6277
6278 l_bind_var_ctr := l_bind_var_ctr + 1;
6279 l_where_clause_arr(l_bind_var_ctr) := ' last_update_date >= :bind'
6280 || l_bind_var_ctr || ' ';
6281 l_bind_var_val_arr(l_bind_var_ctr) := TO_CHAR
6282 (
6283 p_last_update_from_date
6287
6284 , 'DD-MM-RRRR HH24:MI:SS'
6285 );
6286 l_bind_var_type_arr(l_bind_var_ctr) := 'D';
6288 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6289 THEN
6290 FND_LOG.String
6291 (
6292 FND_LOG.level_statement
6293 , L_LOG_MODULE || 'where_last_update_from_date_1'
6294 , 'l_where_clause_arr(l_bind_var_ctr):'
6295 || l_where_clause_arr(l_bind_var_ctr)
6296 );
6297 FND_LOG.String
6298 (
6299 FND_LOG.level_statement
6300 , L_LOG_MODULE || 'where_last_update_from_date_2'
6301 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6302 || l_bind_var_val_arr(l_bind_var_ctr)
6303 );
6304 FND_LOG.String
6305 (
6306 FND_LOG.level_statement
6307 , L_LOG_MODULE || 'where_last_update_from_date_end'
6308 , 'after framing where clause for p_last_update_from_date'
6309 );
6310 END IF;
6311 END IF;
6312
6313 ---
6314
6315 IF p_last_update_to_date IS NOT NULL
6316 THEN
6317 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6318 THEN
6319 FND_LOG.String
6320 (
6321 FND_LOG.level_statement
6322 , L_LOG_MODULE || 'where_last_update_to_date_start'
6323 , 'framing where clause for p_last_update_to_date'
6324 );
6325 END IF;
6326
6327 -- frame the where clause for the parameter p_last_update_to_date
6328 -- along with the bind variable value into the plsql tables
6329
6330 l_bind_var_ctr := l_bind_var_ctr + 1;
6331 l_where_clause_arr(l_bind_var_ctr) := ' last_update_date <= :bind'
6332 || l_bind_var_ctr || ' ';
6333 l_bind_var_val_arr(l_bind_var_ctr) := TO_CHAR
6334 (
6335 p_last_update_to_date
6336 , 'DD-MM-RRRR HH24:MI:SS'
6337 );
6338 l_bind_var_type_arr(l_bind_var_ctr) := 'D';
6339
6340 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6341 THEN
6342 FND_LOG.String
6343 (
6344 FND_LOG.level_statement
6345 , L_LOG_MODULE || 'where_last_update_to_date_1'
6346 , 'l_where_clause_arr(l_bind_var_ctr):'
6347 || l_where_clause_arr(l_bind_var_ctr)
6348 );
6349 FND_LOG.String
6350 (
6351 FND_LOG.level_statement
6352 , L_LOG_MODULE || 'where_last_update_to_date_2'
6353 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6354 || l_bind_var_val_arr(l_bind_var_ctr)
6355 );
6356 FND_LOG.String
6357 (
6358 FND_LOG.level_statement
6359 , L_LOG_MODULE || 'where_last_update_to_date_end'
6360 , 'after framing where clause for p_last_update_to_date'
6361 );
6362 END IF;
6363 END IF;
6364
6365 ---
6366
6367 IF p_customer_id IS NOT NULL
6368 THEN
6369 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6370 THEN
6371 FND_LOG.String
6372 (
6373 FND_LOG.level_statement
6374 , L_LOG_MODULE || 'where_customer_id_start'
6375 , 'framing where clause for p_customer_id'
6376 );
6377 END IF;
6378
6379 -- frame the where clause for the parameter p_customer_id
6380 -- along with the bind variable value into the plsql tables
6381
6382 l_bind_var_ctr := l_bind_var_ctr + 1;
6383 l_where_clause_arr(l_bind_var_ctr) := ' customer_id = :bind'
6384 || l_bind_var_ctr || ' ';
6385 l_bind_var_val_arr(l_bind_var_ctr) := p_customer_id;
6386 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6387
6388 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6389 THEN
6390 FND_LOG.String
6391 (
6392 FND_LOG.level_statement
6393 , L_LOG_MODULE || 'where_customer_id_1'
6394 , 'l_where_clause_arr(l_bind_var_ctr):'
6395 || l_where_clause_arr(l_bind_var_ctr)
6396 );
6397 FND_LOG.String
6398 (
6399 FND_LOG.level_statement
6400 , L_LOG_MODULE || 'where_customer_id_2'
6401 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6402 || l_bind_var_val_arr(l_bind_var_ctr)
6403 );
6404 FND_LOG.String
6405 (
6406 FND_LOG.level_statement
6407 , L_LOG_MODULE || 'where_customer_id_end'
6408 , 'after framing where clause for p_customer_id'
6409 );
6410 END IF;
6411 END IF;
6412
6413 ---
6414
6415 IF p_customer_acc_id IS NOT NULL
6416 THEN
6417 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6418 THEN
6419 FND_LOG.String
6420 (
6421 FND_LOG.level_statement
6422 , L_LOG_MODULE || 'where_customer_acc_id_start'
6423 , 'framing where clause for p_customer_acc_id'
6424 );
6425 END IF;
6426
6427 -- frame the where clause for the parameter p_customer_acc_id
6428 -- along with the bind variable value into the plsql tables
6429
6430 l_bind_var_ctr := l_bind_var_ctr + 1;
6431 l_where_clause_arr(l_bind_var_ctr) := ' account_id = :bind'
6432 || l_bind_var_ctr || ' ';
6433 l_bind_var_val_arr(l_bind_var_ctr) := p_customer_acc_id;
6434 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6435
6436 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6437 THEN
6438 FND_LOG.String
6439 (
6440 FND_LOG.level_statement
6444 );
6441 , L_LOG_MODULE || 'where_customer_acc_id_1'
6442 , 'l_where_clause_arr(l_bind_var_ctr):'
6443 || l_where_clause_arr(l_bind_var_ctr)
6445 FND_LOG.String
6446 (
6447 FND_LOG.level_statement
6448 , L_LOG_MODULE || 'where_customer_acc_id_2'
6449 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6450 || l_bind_var_val_arr(l_bind_var_ctr)
6451 );
6452 FND_LOG.String
6453 (
6454 FND_LOG.level_statement
6455 , L_LOG_MODULE || 'where_customer_acc_id_end'
6456 , 'after framing where clause for p_customer_acc_id'
6457 );
6458 END IF;
6459 END IF;
6460
6461 ---
6462
6463 IF p_item_category_id IS NOT NULL
6464 THEN
6465 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6466 THEN
6467 FND_LOG.String
6468 (
6469 FND_LOG.level_statement
6470 , L_LOG_MODULE || 'where_item_category_id_start'
6471 , 'framing where clause for p_item_category_id'
6472 );
6473 END IF;
6474
6475 -- frame the where clause for the parameter p_item_category_id
6476 -- along with the bind variable value into the plsql tables
6477
6478 l_bind_var_ctr := l_bind_var_ctr + 1;
6479 l_where_clause_arr(l_bind_var_ctr) := ' category_id = :bind'
6480 || l_bind_var_ctr || ' ';
6481 l_bind_var_val_arr(l_bind_var_ctr) := p_item_category_id;
6482 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6483
6484 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6485 THEN
6486 FND_LOG.String
6487 (
6488 FND_LOG.level_statement
6489 , L_LOG_MODULE || 'where_item_category_id_1'
6490 , 'l_where_clause_arr(l_bind_var_ctr):'
6491 || l_where_clause_arr(l_bind_var_ctr)
6492 );
6493 FND_LOG.String
6494 (
6495 FND_LOG.level_statement
6496 , L_LOG_MODULE || 'where_item_category_id_2'
6497 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6498 || l_bind_var_val_arr(l_bind_var_ctr)
6499 );
6500 FND_LOG.String
6501 (
6502 FND_LOG.level_statement
6503 , L_LOG_MODULE || 'where_item_category_id_end'
6504 , 'after framing where clause for p_item_category_id'
6505 );
6506 END IF;
6507 END IF;
6508
6509 ---
6510
6511 IF p_inventory_item_id IS NOT NULL
6512 THEN
6513 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6514 THEN
6515 FND_LOG.String
6516 (
6517 FND_LOG.level_statement
6518 , L_LOG_MODULE || 'where_inventory_item_id_start'
6519 , 'framing where clause for p_inventory_item_id'
6520 );
6521 END IF;
6522
6523 -- frame the where clause for the parameter p_item_category_id
6524 -- along with the bind variable value into the plsql tables
6525
6526 l_bind_var_ctr := l_bind_var_ctr + 1;
6527 l_where_clause_arr(l_bind_var_ctr) := ' inventory_item_id = :bind'
6528 || l_bind_var_ctr || ' ';
6529 l_bind_var_val_arr(l_bind_var_ctr) := p_inventory_item_id;
6530 l_bind_var_type_arr(l_bind_var_ctr) := 'N';
6531
6532 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6533 THEN
6534 FND_LOG.String
6535 (
6536 FND_LOG.level_statement
6537 , L_LOG_MODULE || 'where_inventory_item_id_1'
6538 , 'l_where_clause_arr(l_bind_var_ctr):'
6539 || l_where_clause_arr(l_bind_var_ctr)
6540 );
6541 FND_LOG.String
6542 (
6543 FND_LOG.level_statement
6544 , L_LOG_MODULE || 'where_inventory_item_id_2'
6545 , 'l_bind_var_val_arr(l_bind_var_ctr):'
6546 || l_bind_var_val_arr(l_bind_var_ctr)
6547 );
6548 FND_LOG.String
6549 (
6550 FND_LOG.level_statement
6551 , L_LOG_MODULE || 'where_inventory_item_id_end'
6552 , 'after framing where clause for p_inventory_item_id'
6553 );
6554 END IF;
6555 END IF;
6556
6557 ---
6558
6559 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6560 THEN
6561 FND_LOG.String
6562 (
6563 FND_LOG.level_statement
6564 , L_LOG_MODULE || 'final_where_clause_start'
6565 , 'framing final where clause'
6566 );
6567 END IF;
6568
6569 -- Initializing the where clause to eleminate SRs that
6570 -- have depot repair orders linked to them. We do not
6571 -- purge these SRs now.
6572
6573 l_where_clause := ' NOT EXISTS '
6574 || ' ( '
6575 || ' SELECT '
6576 || ' 1 '
6577 || ' FROM '
6578 || ' csd_repairs '
6579 || ' WHERE '
6580 || ' incident_id = basetbl.incident_id '
6581 || ' ) ';
6582
6583 IF p_incident_id IS NULL
6584 THEN
6585 -- If the incident_id is null, then include the
6586 -- clause to filter out only SRs that are closed.
6587
6588 l_where_clause := l_where_clause || ' AND basetbl.status_flag = ''C'' ';
6589 END IF;
6590
6591 IF p_incident_type_id IS NULL
6592 THEN
6593
6594 -- If the incident type is not chosen in the purge parameters
6595 -- make sure no SRs of type CMRO or EAM are included in the
6596 -- purge set. These SRs shall not be purged now.
6597
6598 l_where_clause := l_where_clause || ' AND '
6599 || ' NOT EXISTS '
6600 || ' ( '
6601 || ' SELECT '
6605 || ' WHERE '
6602 || ' 1 '
6603 || ' FROM '
6604 || ' cs_incident_types_b '
6606 || ' incident_type_id = basetbl.incident_type_id '
6607 || ' AND '
6608 || ' ( '
6609 || ' NVL(maintenance_flag, ''N'') = ''Y'' '
6610 || ' OR NVL(cmro_flag, ''N'') = ''Y'' '
6611 || ' ) '
6612 || ' ) ';
6613 END IF;
6614
6615 -- constructing the where clause from the pl/sql table
6616 -- formed by looking up the values of the purge parameters
6617
6618 FOR j in 1..l_bind_var_ctr
6619 LOOP
6620 l_where_clause := l_where_clause || ' AND ' || l_where_clause_arr(j);
6621 END LOOP;
6622
6623 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6624 THEN
6625 FND_LOG.String
6626 (
6627 FND_LOG.level_statement
6628 , L_LOG_MODULE || 'final_where_clause_end'
6629 , 'l_where_clause:' || l_where_clause
6630 );
6631 FND_LOG.String
6632 (
6633 FND_LOG.level_statement
6634 , L_LOG_MODULE || 'final_where_clause_end'
6635 , 'after framing final where clause'
6636 );
6637 END IF;
6638
6639 ---
6640
6641 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6642 THEN
6643 FND_LOG.String
6644 (
6645 FND_LOG.level_statement
6646 , L_LOG_MODULE || 'final_select_start'
6647 , 'framing final select statement'
6648 );
6649 END IF;
6650
6651 -- Initializing the statement to insert the SRs to be
6652 -- purged into the STAGING table. The APPEND hint is used
6653 -- to make sure no redo log is generated. After executing
6654 -- this statement, a COMMIT is mandated.
6655
6656 l_sql_statement := ' INSERT /*+ APPEND */ INTO cs_incidents_purge_staging '
6657 || ' ( '
6658 || ' incident_id '
6659 || ' , worker_id '
6660 || ' , concurrent_request_id '
6661 || ' ) '
6662 || ' SELECT '
6663 || ' incident_id '
6664 || ' , NULL '
6665 || ' , :request_id ';
6666
6667 IF p_history_size IS NOT NULL
6668 THEN
6669
6670 -- If the Retain Service History parameter is given a value,
6671 -- a sub query is constructed which ranks the rows in the table
6672 -- for each customer_id and based on the incident_id and creation
6673 -- date in order to choose all the SRs that fall after the
6674 -- number specified in the History Size.
6675
6676 l_sql_statement := l_sql_statement
6677 || ' FROM '
6678 || ' ( '
6679 || ' SELECT '
6680 || ' incident_id '
6681 || ' , RANK() OVER '
6682 || ' ( '
6683 || ' PARTITION BY '
6684 || ' customer_id '
6685 || ' ORDER BY '
6686 || ' creation_date DESC '
6687 || ' , incident_id DESC '
6688 || ' ) AS group_row_num '
6689 || ' FROM '
6690 || ' cs_incidents_all_b basetbl '
6691 || ' WHERE '
6692 || l_where_clause
6693 || ' ) inner '
6694 || ' WHERE '
6695 || ' inner.group_row_num > :histoy_size ';
6696 ELSIF p_history_size IS NULL
6697
6698 -- If the Service History parameter is NOT selected, only
6699 -- the where clause is appended to the main query.
6700
6701 THEN
6702 l_sql_statement := l_sql_statement
6703 || ' FROM '
6704 || ' cs_incidents_all_b basetbl '
6705 || ' WHERE '
6706 || l_where_clause;
6707 END IF;
6708
6709 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6710 THEN
6711 FND_LOG.String
6712 (
6713 FND_LOG.level_statement
6714 , L_LOG_MODULE || 'final_select_end'
6715 , 'l_sql_statement:' || l_sql_statement
6716 );
6717 FND_LOG.String
6718 (
6719 FND_LOG.level_statement
6720 , L_LOG_MODULE || 'final_select_end'
6721 , 'after framing final select statement'
6722 );
6723 END IF;
6724
6725 ---
6726
6727 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6728 THEN
6729 FND_LOG.String
6730 (
6731 FND_LOG.level_statement
6732 , L_LOG_MODULE || 'sql_execution_start'
6733 , 'Executing the SQL framed to insert SRs to staging table'
6734 );
6735 END IF;
6736
6737 -- Open a cursor to execute the Dynamic SQL
6738
6739 l_dbms_sql_cursor := DBMS_SQL.OPEN_CURSOR;
6740
6741 -- Parse the sql dynamic SQL statement
6742
6743 DBMS_SQL.PARSE
6744 (
6745 l_dbms_sql_cursor
6746 , l_sql_statement
6747 , DBMS_SQL.NATIVE
6748 );
6749
6750 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6751 THEN
6752 FND_LOG.String
6753 (
6754 FND_LOG.level_statement
6755 , L_LOG_MODULE || 'bind_variable_values'
6756 , ':request_id - ' || p_request_id
6757 );
6758 END IF;
6759
6760 -- Bind value of the variable p_request_id to the dynamic SQL
6761
6762 DBMS_SQL.BIND_VARIABLE
6763 (
6767 );
6764 l_dbms_sql_cursor
6765 , ':request_id'
6766 , p_request_id
6768
6769 FOR j IN 1..l_bind_var_ctr
6770 LOOP
6771 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6772 THEN
6773 FND_LOG.String
6774 (
6775 FND_LOG.level_statement
6776 , L_LOG_MODULE || 'bind_variable_values'
6777 , ':bind' || j || '(' || l_bind_var_type_arr(j) || ') - '
6778 || l_bind_var_val_arr(j)
6779 );
6780 END IF;
6781
6782 -- Bind value of the variables the dynamic SQL
6783
6784 IF l_bind_var_type_arr(j) = 'V'
6785 THEN
6786 DBMS_SQL.BIND_VARIABLE
6787 (
6788 l_dbms_sql_cursor
6789 , ':bind' || j
6790 , l_bind_var_val_arr(j)
6791 );
6792 ELSIF l_bind_var_type_arr(j) = 'N'
6793 THEN
6794 DBMS_SQL.BIND_VARIABLE
6795 (
6796 l_dbms_sql_cursor
6797 , ':bind' || j
6798 , TO_NUMBER(l_bind_var_val_arr(j))
6799 );
6800 ELSIF l_bind_var_type_arr(j) = 'D'
6801 THEN
6802 DBMS_SQL.BIND_VARIABLE
6803 (
6804 l_dbms_sql_cursor
6805 , ':bind' || j
6806 , TO_DATE(l_bind_var_val_arr(j), 'DD-MM-RRRR HH24:MI:SS')
6807 );
6808 END IF;
6809
6810 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6811 THEN
6812 FND_LOG.String
6813 (
6814 FND_LOG.level_statement
6815 , L_LOG_MODULE || 'bind_variable_values'
6816 , 'after binding the field ' || j
6817 );
6818 END IF;
6819 END LOOP;
6820
6821 IF p_history_size IS NOT NULL
6822 THEN
6823 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6824 THEN
6825 FND_LOG.String
6826 (
6827 FND_LOG.level_statement
6828 , L_LOG_MODULE || 'bind_variable_values'
6829 , ':histoy_size - ' || p_history_size
6830 );
6831 END IF;
6832
6833 -- Bind value of the variable p_history_size to the dynamic SQL
6834
6835 DBMS_SQL.BIND_VARIABLE
6836 (
6837 l_dbms_sql_cursor
6838 , ':histoy_size'
6839 , p_history_size
6840 );
6841 END IF;
6842
6843 -- Execute the Query
6844
6845 p_row_count := DBMS_SQL.EXECUTE
6846 (
6847 l_dbms_sql_cursor
6848 );
6849
6850 -- Since the APPEND hint forces to commit
6851 -- after the transaction, committing here
6852
6853 COMMIT;
6854
6855 DBMS_SQL.CLOSE_CURSOR
6856 (
6857 l_dbms_sql_cursor
6858 );
6859
6860 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6861 THEN
6862 FND_LOG.String
6863 (
6864 FND_LOG.level_statement
6865 , L_LOG_MODULE || 'sql_execution_end'
6866 , 'After executing the SQL framed to insert SRs to '
6867 || 'staging table - inserted ' || p_row_count || ' rows'
6868 );
6869 END IF;
6870
6871 ---
6872
6873 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6874 THEN
6875 FND_LOG.String
6876 (
6877 FND_LOG.level_statement
6878 , L_LOG_MODULE || 'compute_required_workers_start'
6879 , 'Computing required number of worker concurrent requests'
6880 );
6881 END IF;
6882
6883 -- Computing the number of worker concurrent
6884 -- requests required for purging the SRs.
6885 -- The approach followed to decide on howmany
6886 -- workers is required is as follows:
6887 -- 1. If the no. of rows < batch size, only 1 worker
6888 -- is required for processing the purge set.
6889 -- 2. If the no. of rows > batch size, ceil(batch_size / no_of_rows)
6890 -- is the no. of workers required for processing the purge set.
6891 -- But if this is more than the no. of workers asked for,
6892 -- no. of workers is = p_number_of_workers. Otherwise, it will
6893 -- be the result of the above formula.
6894
6895 IF p_row_count <= p_purge_batch_size
6896 THEN
6897 l_number_of_workers := 1;
6898 ELSIF p_row_count > p_purge_batch_size
6899 THEN
6900 l_number_of_workers := LEAST
6901 (
6902 p_number_of_workers
6903 , CEIL(p_row_count / p_purge_batch_size)
6904 );
6905 END IF;
6906
6907 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6908 THEN
6909 FND_LOG.String
6910 (
6911 FND_LOG.level_statement
6912 , L_LOG_MODULE || 'compute_required_workers_end'
6913 , 'Starting worker concurrent requests : asked for - '
6914 || p_number_of_workers || ', actual - '
6915 || l_number_of_workers
6916 );
6917 END IF;
6918
6919 ---
6920
6921 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6922 THEN
6923 FND_LOG.String
6924 (
6925 FND_LOG.level_statement
6926 , L_LOG_MODULE || 'allocating_worker_id_start'
6927 , 'Allocating worker ids to rows in the staging table'
6928 );
6929 END IF;
6930
6931 -- Updating the staging table with the worker id
6932 -- which helps in dividing the work among the
6933 -- number of workers actually required for working
6934 -- on the current purge set.
6935
6936 UPDATE cs_incidents_purge_staging
6937 SET
6938 worker_id = MOD
6939 (
6940 ROWNUM - 1
6941 , l_number_of_workers
6942 ) + 1;
6943
6944 l_row_count := SQL%ROWCOUNT;
6945
6946 COMMIT;
6947
6948 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
6949 THEN
6950 FND_LOG.String
6951 (
6955 || l_row_count
6952 FND_LOG.level_statement
6953 , L_LOG_MODULE || 'allocating_worker_id_end'
6954 , 'After allocating worker ids to rows in the staging table '
6956 );
6957 END IF;
6958
6959 p_number_of_workers := l_number_of_workers;
6960
6961 ---
6962
6963 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
6964 THEN
6965 FND_LOG.String
6966 (
6967 FND_LOG.level_procedure
6968 , L_LOG_MODULE || 'end'
6969 , 'Completed work in ' || L_API_NAME_FULL || ' with Success'
6970 );
6971 END IF ;
6972 END Form_And_Exec_Statement;
6973
6974 --------------------------------------------------------------------------------
6975 -- Procedure Name : WRITE_PURGE_OUTPUT
6976 --
6977 -- Parameters (other than standard ones)
6978 -- p_purge_batch_size : Used to indicate the number of rows that need to
6979 -- be inserted into the output file at any point in time.
6980 -- This parameter is the same batch size that is used
6981 -- while picking up SRs for purging.
6982 -- p_request_id : Concurrent Request id for which output needs to be
6983 -- generated.
6984 -- p_worker_id : Worker Number for which the output needs to be
6985 -- generated. This field can be left NULL if the
6986 -- output is to be generated for the parent request.
6987 --
6988 -- Description
6989 -- This procedure lists out the number of SRs submitted for purge, the
6990 -- number of SRs that were successfully purged and the number of SRs that
6991 -- failed purge due to business reasons. This also prints a list of the
6992 -- SRs that failed for business reasons along with the error messages.
6993 --
6994 -- HISTORY
6995 --
6996 ----------------+------------+--------------------------------------------------
6997 -- DATE | UPDATED BY | Change Description
6998 ----------------+------------+--------------------------------------------------
6999 -- 2-Aug_2005 | varnaray | Created
7000 -- | |
7001 ----------------+------------+--------------------------------------------------
7002 /*#
7003 * This procedure lists out the number of SRs submitted for purge, the number
7004 * of SRs that were successfully purged and the number of SRs that failed purge
7005 * due to business reasons. This also prints a list of the SRs that failed for
7006 * business reasons along with the error messages.
7007 * @param p_purge_batch_size Used to indicate the number of rows that need to
7008 * be inserted into the output file at any point in time. This parameter is
7009 * the same batch size that is used while picking up SRs for purging.
7010 * @param p_request_id Concurrent Request id for which output needs to be
7011 * generated.
7012 * @param p_worker_id Worker Number for which the output needs to be generated.
7013 * This field can be left NULL if the output is to be generated for the parent
7014 * request.
7015 * @rep:scope internal
7016 * @rep:product CS
7017 * @rep:displayname Write Purge Program Output
7018 */
7019 PROCEDURE Write_Purge_Output
7020 (
7021 p_purge_batch_size IN NUMBER
7022 , p_request_id IN NUMBER
7023 , p_worker_id IN NUMBER := NULL
7024 )
7025 IS
7026 --------------------------------------------------------------------------------
7027
7028 L_API_NAME CONSTANT VARCHAR2(30) := 'WRITE_PURGE_OUTPUT';
7029 L_API_NAME_FULL CONSTANT VARCHAR2(61) := g_pkg_name || '.' || L_API_NAME;
7030 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
7031
7032 TYPE t_varchar_arr IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
7033 TYPE t_number_arr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7034
7035 -- Cursor used to fetch all the SRs that could not
7036 -- be purged due to business reasons as indicated
7037 -- by the various validation routines, along with
7038 -- the basic information of those SRs, to be used
7039 -- to generate a report of such SRs.
7040
7041 CURSOR c_purge_staging_err
7042 IS
7043 SELECT
7044 b.incident_number incident_number
7045 , p.party_number customer_number
7046 , i.segment1 item_number
7047 , t.summary summary
7048 , s.purge_error_message purge_error_message
7049 FROM
7050 cs_incidents_purge_staging s
7051 , cs_incidents_all_b b
7052 , cs_incidents_all_tl t
7053 , mtl_system_items_b i
7054 , hz_parties p
7055 WHERE
7056 s.purge_status = 'E'
7057 AND s.incident_id = b.incident_id
7058 AND s.incident_id = t.incident_id
7059 AND b.inventory_item_id = i.inventory_item_id(+)
7060 AND b.inv_organization_id = i.organization_id(+)
7061 AND b.customer_id = p.party_id
7062 AND t.language = USERENV('LANG')
7063 AND s.concurrent_request_id = p_request_id
7064 AND s.worker_id = NVL(p_worker_id, s.worker_id)
7065 ORDER BY
7066 b.incident_number;
7067
7068 --siahmed fix for bug 16023070 where customer is getting a number error and in the db the
7069 --incident_number and customer_number are varchar columns
7070 --l_incident_number_arr t_number_arr;
7071 --l_customer_number_arr t_number_arr;
7072 l_incident_number_arr t_varchar_arr;
7073 l_customer_number_arr t_varchar_arr;
7074 --end of fix siahmed
7075 l_item_number_arr t_varchar_arr;
7076 l_summary_arr t_varchar_arr;
7077 l_purge_error_message_arr t_varchar_arr;
7078
7079 l_row_count NUMBER;
7080 l_report_caption VARCHAR2(2000);
7081 l_text VARCHAR2(2000);
7082 l_error_code_loc NUMBER;
7086 l_exec_count NUMBER := 0;
7083 l_error_message_loc NUMBER;
7084 l_error_message_text VARCHAR2(2000);
7085
7087
7088 BEGIN
7089 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
7090 THEN
7091 FND_LOG.String
7092 (
7093 FND_LOG.level_procedure
7094 , L_LOG_MODULE || 'start'
7095 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
7096 );
7097 FND_LOG.String
7098 (
7099 FND_LOG.level_procedure
7100 , L_LOG_MODULE || 'param 1'
7101 , 'p_purge_batch_size:' || p_purge_batch_size
7102 );
7103 FND_LOG.String
7104 (
7105 FND_LOG.level_procedure
7106 , L_LOG_MODULE || 'param 2'
7107 , 'p_request_id:' || p_request_id
7108 );
7109 FND_LOG.String
7110 (
7111 FND_LOG.level_procedure
7112 , L_LOG_MODULE || 'param 3'
7113 , 'p_worker_id:' || p_worker_id
7114 );
7115 END IF;
7116
7117 ---
7118
7119 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7120 THEN
7121 FND_LOG.String
7122 (
7123 FND_LOG.level_statement
7124 , L_LOG_MODULE || 'sr_submit_count_start'
7125 , 'Getting number of SRs submitted for purge'
7126 );
7127 END IF;
7128
7129 fnd_file.put_line
7130 (
7131 FND_FILE.OUTPUT
7132 , '<html><body>'
7133 );
7134 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7135 THEN
7136 FND_LOG.String
7137 (
7138 FND_LOG.level_statement
7139 , L_LOG_MODULE || 'html_output_1'
7140 , '<html><body>'
7141 );
7142 END IF;
7143
7144 l_report_caption := FND_MESSAGE.Get_String
7145 (
7146 'CS'
7147 , 'CS_SR_PURGE_RESULT'
7148 );
7149 fnd_file.put_line
7150 (
7151 FND_FILE.OUTPUT
7152 , '<h3>' || l_report_caption
7153 || '</h3><table border cellspacing=0 cellpadding=5 width=40%>'
7154 );
7155 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7156 THEN
7157 FND_LOG.String
7158 (
7159 FND_LOG.level_statement
7160 , L_LOG_MODULE || 'html_output_2'
7161 , '<h3>' || l_report_caption
7162 || '</h3><table border cellspacing=0 cellpadding=5 width=40%>'
7163 );
7164 END IF;
7165
7166 l_report_caption := FND_MESSAGE.Get_String
7167 (
7168 'CS'
7169 , 'CS_SR_PURGE_SUBMIT_COUNT'
7170 );
7171
7172 -- Query to find out the total number of SRs
7173 -- submitted for purge
7174
7175 SELECT
7176 count(1)
7177 INTO
7178 l_row_count
7179 FROM
7180 cs_incidents_purge_staging s
7181 WHERE
7182 s.worker_id = NVL(p_worker_id, s.worker_id);
7183
7184 fnd_file.put_line
7185 (
7186 FND_FILE.OUTPUT
7187 , '<tr><td><b>' || l_report_caption || '</b></td><td><b>'
7188 || l_row_count || '</b></td></tr>'
7189 );
7190 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7191 THEN
7192 FND_LOG.String
7193 (
7194 FND_LOG.level_statement
7195 , L_LOG_MODULE || 'html_output_3'
7196 , '<tr><td><b>' || l_report_caption || '</b></td><td><b>'
7197 || l_row_count || '</b></td></tr>'
7198 );
7199 END IF;
7200
7201 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7202 THEN
7203 FND_LOG.String
7204 (
7205 FND_LOG.level_statement
7206 , L_LOG_MODULE || 'sr_submit_count_end'
7207 , 'After getting number of SRs submitted for purge ' || l_row_count
7208 );
7209 END IF;
7210
7211 ---
7212
7213 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7214 THEN
7215 FND_LOG.String
7216 (
7217 FND_LOG.level_statement
7218 , L_LOG_MODULE || 'sr_success_count_start'
7219 , 'Getting number of SRs Successfully purged'
7220 );
7221 END IF;
7222
7223 l_report_caption := FND_MESSAGE.Get_String
7224 (
7225 'CS'
7226 , 'CS_SR_PURGE_SUCCESS_COUNT'
7227 );
7228
7229 -- Query to find out the total number of SRs
7230 -- successfully purged
7231
7232 SELECT
7233 count(1)
7234 INTO
7235 l_row_count
7236 FROM
7237 cs_incidents_purge_staging s
7238 WHERE
7239 purge_status = 'S'
7240 AND s.worker_id = NVL(p_worker_id, s.worker_id);
7241
7242 fnd_file.put_line
7243 (
7244 FND_FILE.OUTPUT
7245 , '<tr><td><b>' || l_report_caption || '</b></td><td><font color=green><b>'
7246 || l_row_count || '</b></font></td></tr>'
7247 );
7248 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7249 THEN
7250 FND_LOG.String
7251 (
7252 FND_LOG.level_statement
7253 , L_LOG_MODULE || 'html_output_4'
7254 , '<tr><td><b>' || l_report_caption || '</b></td><td><font color=green><b>'
7255 || l_row_count || '</b></font></td></tr>'
7256 );
7257 END IF;
7258
7259 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7260 THEN
7261 FND_LOG.String
7262 (
7263 FND_LOG.level_statement
7264 , L_LOG_MODULE || 'sr_success_count_end'
7265 , 'After getting number of SRs Successfully purged ' || l_row_count
7266 );
7267 END IF;
7268
7269 ---
7270
7271 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7272 THEN
7273 FND_LOG.String
7274 (
7275 FND_LOG.level_statement
7276 , L_LOG_MODULE || 'sr_notdone_count_start'
7277 , 'Getting number of SRs not attempted to be purged'
7278 );
7279 END IF;
7280
7281 l_report_caption := FND_MESSAGE.Get_String
7282 (
7283 'CS'
7284 , 'CS_SR_PURGE_NOTDONE_COUNT'
7285 );
7286
7287 -- Query to find out the total number of SRs
7288 -- successfully purged
7289
7290 SELECT
7291 count(1)
7292 INTO
7293 l_row_count
7294 FROM
7295 cs_incidents_purge_staging s
7296 WHERE
7297 purge_status IS NULL
7298 AND s.worker_id = NVL(p_worker_id, s.worker_id);
7299
7300 IF l_row_count > 0
7301
7302 -- if there were some rows that were not
7303 -- processed, display that too in the report.
7304
7305 THEN
7306 fnd_file.put_line
7307 (
7308 FND_FILE.OUTPUT
7309 , '<tr><td><b>' || l_report_caption
7310 || '</b></td><td><font color=blue><b>'
7311 || l_row_count || '</b></font></td></tr>'
7312 );
7313 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7314 THEN
7315 FND_LOG.String
7316 (
7317 FND_LOG.level_statement
7318 , L_LOG_MODULE || 'html_output_5'
7319 , '<tr><td><b>' || l_report_caption
7320 || '</b></td><td><font color=blue><b>' || l_row_count
7321 || '</b></font></td></tr>'
7322 );
7323 END IF;
7324 END IF;
7325
7326 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7327 THEN
7328 FND_LOG.String
7329 (
7330 FND_LOG.level_statement
7331 , L_LOG_MODULE || 'sr_success_count_end'
7332 , 'After getting number of SRs not attempted to be purged '
7333 || l_row_count
7334 );
7335 END IF;
7336
7337 ---
7338
7339 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7340 THEN
7341 FND_LOG.String
7342 (
7343 FND_LOG.level_statement
7344 , L_LOG_MODULE || 'sr_failed_count_start'
7345 , 'Getting number of SRs not purged due to validation failures'
7346 );
7347 END IF;
7348
7349 l_report_caption := FND_MESSAGE.Get_String
7350 (
7351 'CS'
7352 , 'CS_SR_PURGE_FAILURE_COUNT'
7353 );
7354
7355 -- Query to find out the total number of SRs
7356 -- failed while attempting to purge due to
7357 -- failure in validations
7358
7359 SELECT
7360 count(1)
7361 INTO
7362 l_row_count
7363 FROM
7364 cs_incidents_purge_staging s
7365 WHERE
7366 purge_status = 'E'
7367 AND s.worker_id = NVL(p_worker_id, s.worker_id);
7368
7369 IF l_row_count > 0
7370
7371 -- if there were some rows that failed during
7372 -- processing, display that in the report.
7373
7374 THEN
7375 fnd_file.put_line
7376 (
7377 FND_FILE.OUTPUT
7378 , '<tr><td><b>' || l_report_caption
7379 || '</b></td><td><font color=red><b>' || l_row_count
7380 || '</b></font></td></tr></table>'
7381 );
7382 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7383 THEN
7384 FND_LOG.String
7385 (
7386 FND_LOG.level_statement
7387 , L_LOG_MODULE || 'html_output_6'
7388 , '<tr><td><b>' || l_report_caption
7389 || '</b></td><td><font color=red><b>' || l_row_count
7390 || '</b></font></td></tr></table>'
7391 );
7392 END IF;
7393 END IF;
7394
7395 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7396 THEN
7397 FND_LOG.String
7398 (
7399 FND_LOG.level_statement
7400 , L_LOG_MODULE || 'sr_failed_count_end'
7401 , 'After getting number of SRs not purged due to validation failures '
7402 || l_row_count
7403 );
7404 END IF;
7405
7406 ---
7407
7408 IF l_row_count > 0
7409
7410 -- if there are any rows in the staging
7411 -- table with purge_status = E
7412
7413 THEN
7414 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7415 THEN
7416 FND_LOG.String
7417 (
7418 FND_LOG.level_statement
7419 , L_LOG_MODULE || 'sr_failure_report_start'
7420 , 'Listing all SRs that failed with details and error message'
7421 );
7422 END IF;
7423
7424 -- Starting to print the report on all the SRs that failed
7425 -- purge due to business reasons along with the vital details
7426
7427 fnd_file.put_line
7428 (
7429 FND_FILE.OUTPUT
7430 , '<h3>'
7431 );
7432 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7433 THEN
7434 FND_LOG.String
7435 (
7436 FND_LOG.level_statement
7437 , L_LOG_MODULE || 'html_output_7'
7438 , '<h3>'
7439 );
7440 END IF;
7441
7442 l_report_caption := FND_MESSAGE.Get_String
7443 (
7444 'CS'
7445 , 'CS_SR_FAILED_SRS_REPORT'
7446 );
7447 fnd_file.put_line
7448 (
7449 FND_FILE.OUTPUT
7450 , l_report_caption
7451 );
7452 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7453 THEN
7454 FND_LOG.String
7455 (
7456 FND_LOG.level_statement
7457 , L_LOG_MODULE || 'html_output_8'
7458 , l_report_caption
7459 );
7460 END IF;
7461
7462 fnd_file.put_line
7463 (
7464 FND_FILE.OUTPUT
7465 , '</h3>'
7466 );
7467 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7468 THEN
7469 FND_LOG.String
7470 (
7471 FND_LOG.level_statement
7472 , L_LOG_MODULE || 'html_output_9'
7473 , '</h3>'
7474 );
7475 END IF;
7476
7477 ---
7478
7479 fnd_file.put_line
7480 (
7481 FND_FILE.OUTPUT
7482 , '<table border cellspacing=0 width=100%><tr>'
7483 );
7484 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7485 THEN
7486 FND_LOG.String
7487 (
7488 FND_LOG.level_statement
7489 , L_LOG_MODULE || 'html_output_10'
7490 , '<table border cellspacing=0 width=100%><tr>'
7491 );
7492 END IF;
7493
7494 l_report_caption := FND_MESSAGE.Get_String
7495 (
7496 'CS'
7497 , 'CS_SR_FAILED_SRS_RPT_HEAD'
7498 );
7499 fnd_file.put_line
7500 (
7501 FND_FILE.OUTPUT
7502 , l_report_caption
7503 );
7504 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7505 THEN
7506 FND_LOG.String
7507 (
7508 FND_LOG.level_statement
7509 , L_LOG_MODULE || 'html_output_11'
7510 , l_report_caption
7511 );
7512 END IF;
7513
7514 fnd_file.put_line
7515 (
7516 FND_FILE.OUTPUT
7517 , '</tr>'
7518 );
7519 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7520 THEN
7521 FND_LOG.String
7522 (
7523 FND_LOG.level_statement
7524 , L_LOG_MODULE || 'html_output_12'
7525 , '</tr>'
7526 );
7527 END IF;
7528
7529 -- Opening cursor on staging table that lists
7530 -- all the SRs that failed due to business reasons
7531 -- along with the vital details of the SR
7532
7533 OPEN c_purge_staging_err;
7534
7535 -- Loop that retrieves the rows from the staging table
7536 -- in batches and prints the output file.
7537
7538 LOOP
7539 FETCH c_purge_staging_err
7540 BULK COLLECT INTO
7541 l_incident_number_arr
7542 , l_customer_number_arr
7543 , l_item_number_arr
7544 , l_summary_arr
7545 , l_purge_error_message_arr
7546 LIMIT p_purge_batch_size;
7547
7548 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7549 THEN
7550 FND_LOG.String
7551 (
7552 FND_LOG.level_statement
7553 , L_LOG_MODULE || 'report_fetch_count'
7554 , 'Fetched ' || l_incident_number_arr.COUNT
7555 || ' rows during this execution'
7556 );
7557 END IF;
7558
7559 IF l_incident_number_arr.COUNT > 0
7560 THEN
7561
7562 -- Inner loop that inserts the current batch of
7563 -- SRs into the output file. Here, it is assumed
7564 -- that the purge_error_message field contains
7565 -- messages in the format
7566 -- <product>:<message code>~<concurrent request text-message>
7567 -- using which the message text is retrieved from
7568 -- the message dictionary.
7569
7570 FOR j IN l_incident_number_arr.FIRST..l_incident_number_arr.LAST
7571 LOOP
7572 fnd_file.put_line
7573 (
7574 FND_FILE.OUTPUT
7575 , '<tr>'
7576 );
7577
7578 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7579 THEN
7580 FND_LOG.String
7581 (
7582 FND_LOG.level_statement
7583 , L_LOG_MODULE || 'html_output_13'
7584 , '<tr>'
7585 );
7586 END IF;
7587
7588 l_text := '<td>' || l_incident_number_arr(j)
7589 || '</td><td>' || l_customer_number_arr(j)
7590 || '</td><td>' || NVL(l_item_number_arr(j), '-')
7591 || '</td><td>' || l_summary_arr(j)
7592 || '</td><td>';
7593
7594 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7595 THEN
7596 FND_LOG.String
7597 (
7598 FND_LOG.level_statement
7599 , L_LOG_MODULE || 'compute_text'
7600 , 'framing l_text = ' || l_text
7601 );
7602 END IF;
7603
7604 l_error_code_loc := INSTR
7605 (
7606 l_purge_error_message_arr(j)
7607 , ':'
7608 , 1
7609 );
7610
7611 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7612 THEN
7613 FND_LOG.String
7614 (
7615 FND_LOG.level_statement
7616 , L_LOG_MODULE || 'compute_text_1'
7617 , 'getting l_error_code_loc = ' || l_error_code_loc
7618 );
7619 END IF;
7620
7621 l_error_message_loc := INSTR
7622 (
7623 l_purge_error_message_arr(j)
7624 , '~'
7625 , 1
7626 );
7627
7628 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7629 THEN
7630 FND_LOG.String
7631 (
7632 FND_LOG.level_statement
7633 , L_LOG_MODULE || 'compute_text_2'
7634 , 'getting l_error_message_loc = ' || l_error_message_loc
7635 );
7636 END IF;
7637
7638 IF l_error_message_loc > 0
7639 THEN
7640 l_error_message_text := FND_MESSAGE.Get_String
7641 (
7642 SUBSTR
7643 (
7644 l_purge_error_message_arr(j)
7645 , 1
7646 , l_error_code_loc - 1
7647 )
7648 , SUBSTR
7649 (
7650 l_purge_error_message_arr(j)
7651 , l_error_code_loc + 1
7652 , l_error_message_loc - l_error_code_loc - 1
7653 )
7654 )
7655 || ' - '
7656 || SUBSTR
7657 (
7658 l_purge_error_message_arr(j)
7659 , l_error_message_loc + 1
7660 );
7661
7662 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7663 THEN
7664 FND_LOG.String
7665 (
7666 FND_LOG.level_statement
7667 , L_LOG_MODULE || 'compute_text_3.1'
7668 , 'getting l_error_message_text = ' || l_error_message_text
7669 );
7670 END IF;
7671 ELSIF l_error_message_loc <= 0
7672 THEN
7673 l_error_message_text := FND_MESSAGE.Get_String
7674 (
7675 SUBSTR
7676 (
7677 l_purge_error_message_arr(j)
7678 , 1
7679 , l_error_code_loc - 1
7680 )
7681 , SUBSTR
7682 (
7683 l_purge_error_message_arr(j)
7684 , l_error_code_loc + 1
7685 )
7686 );
7687
7688 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7689 THEN
7690 FND_LOG.String
7691 (
7692 FND_LOG.level_statement
7693 , L_LOG_MODULE || 'compute_text_3.2'
7694 , 'getting l_error_message_text = ' || l_error_message_text
7695 );
7696 END IF;
7697 END IF;
7698
7699 l_text := l_text || NVL(l_error_message_text, '-') || '</td>';
7700
7701 fnd_file.put_line
7702 (
7703 FND_FILE.OUTPUT
7704 , l_text
7705 );
7706
7707 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7708 THEN
7709 FND_LOG.String
7710 (
7711 FND_LOG.level_statement
7712 , L_LOG_MODULE || 'html_output_14'
7713 , l_text
7714 );
7715 END IF;
7716
7717 fnd_file.put_line
7718 (
7719 FND_FILE.OUTPUT
7720 , '</tr>'
7721 );
7722
7723 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7724 THEN
7725 FND_LOG.String
7726 (
7727 FND_LOG.level_statement
7728 , L_LOG_MODULE || 'html_output_15'
7729 , '</tr>'
7730 );
7731 END IF;
7732 END LOOP;
7733 END IF;
7734
7735 EXIT WHEN c_purge_staging_err%NOTFOUND;
7736 END LOOP;
7737
7738 CLOSE c_purge_staging_err;
7739
7740 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7741 THEN
7742 FND_LOG.String
7743 (
7744 FND_LOG.level_statement
7745 , L_LOG_MODULE || 'sr_failure_report_end'
7746 , 'After listing all SRs that failed with details and error message'
7747 );
7748 END IF;
7749 END IF;
7750
7751 fnd_file.put_line
7752 (
7753 FND_FILE.OUTPUT
7754 , '</table></body></html>'
7755 );
7756 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
7757 THEN
7758 FND_LOG.String
7759 (
7760 FND_LOG.level_statement
7761 , L_LOG_MODULE || 'html_output_16'
7762 , '</table></body></html>'
7763 );
7764 END IF;
7765
7766 ---
7767
7768 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
7769 THEN
7770 FND_LOG.String
7771 (
7772 FND_LOG.level_procedure
7773 , L_LOG_MODULE || 'end'
7774 , 'Completed work in ' || L_API_NAME_FULL || ' with Success'
7775 );
7776 END IF ;
7777 END Write_Purge_Output;
7778 --------------------------------------------------------------------------------
7779
7780 END CS_SR_PURGE_CP;
[Home] [Help]