[Home] [Help]
PACKAGE BODY: APPS.JL_CO_FA_PURGE_PKG
Source
1 PACKAGE BODY jl_co_fa_purge_pkg AS
2 /* $Header: jlcoftpb.pls 120.7.12020000.2 2012/11/29 13:40:09 mbarrett ship $ */
3
4 /* ======================================================================*
5 | FND Logging infrastructure |
6 * ======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_PURGE_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_CO_FA_PURGE_PKG.';
16
17 TYPE varchar2s is table of VARCHAR2(256) INDEX BY BINARY_INTEGER;
18 x_last_updated_by NUMBER(15);
19 x_last_update_login NUMBER(15);
20 x_request_id NUMBER(15);
21 x_program_application_id NUMBER(15);
22 x_program_id NUMBER(15);
23 x_sysdate DATE;
24
25 PROCEDURE find_who_columns;
26 FUNCTION do_sql( p_string VARCHAR2) RETURN BOOLEAN;
27 FUNCTION STORAGE_FACTOR( p_table_name IN VARCHAR2,
28 p_rows_to_archive IN NUMBER,
29 p_storage_factor OUT NOCOPY NUMBER) RETURN BOOLEAN;
30 PROCEDURE create_output_headings( p_fiscal_year IN NUMBER);
31
32 ----------------------------------------------------------------------------
33 -- PROCEDURE --
34 -- purge_adjustment --
35 -- --
36 -- DESCRIPTION --
37 -- Use this procedure to purge jl_co_fa_adjustments table --
38 -- --
39 -- PURPOSE: --
40 -- Oracle Applications Rel 11.0 --
41 -- --
42 -- PARAMETERS: --
43 -- p_book --
44 -- p_fiscal_year --
45 -- p_option --
46 -- --
47 -- HISTORY: --
48 -- 08/21/98 Sujit Dalai Created --
49 -- 10/23/98 Sujit dalai Changed Messages --
50 -- 10/26/98 Sujit Dalai Changed. Excluded purging of --
51 -- jl_co_fa_retirements table --
52 -- 05/26/00 Sujit dalai Changed to include storage clause for --
53 -- backup table. Changed not process if --
54 -- are not posted to GL. --
55 -- 06/30/99 Santosh Vaze Fixed the bug with the function storage --
56 -- factor. --
57 ----------------------------------------------------------------------------
58
59 PROCEDURE purge_adjustment( ERRBUF OUT NOCOPY VARCHAR2,
60 RETCODE OUT NOCOPY VARCHAR2,
61 p_book VARCHAR2,
62 p_fiscal_year number,
63 p_option varchar2) IS
64
65 x_deprn_calendar fa_book_controls.deprn_calendar%TYPE;
66 x_initial_period_counter fa_book_controls.initial_period_counter%TYPE;
67 x_current_fiscal_year fa_book_controls.current_fiscal_year%TYPE;
68 x_deprn_status fa_book_controls.deprn_status%TYPE;
69 x_book_class fa_book_controls.book_class%TYPE;
70 x_initial_fiscal_year fa_deprn_periods.fiscal_year%TYPE;
71 x_count NUMBER;
72 x_index NUMBER(7);
73 x_period_number fa_calendar_types.number_per_fiscal_year%TYPE;
74 x_start_period_counter fa_deprn_periods.period_counter%TYPE;
75 x_end_period_counter fa_deprn_periods.period_counter%TYPE;
76 x_adjustments_rows NUMBER(15);
77 x_adj_table VARCHAR2(30);
78 x_oracle_username VARCHAR2(30);
79 x_adjustments_amount NUMBER;
80 x_storage_factor NUMBER;
81 x_string VARCHAR2(250);
82 x_cursor INTEGER;
83 x_row_processed INTEGER;
84 err_num NUMBER;
85 err_msg VARCHAR2(2000);
86 call_status BOOLEAN;
87 NOT_POSTED_TO_GL EXCEPTION;
88 INVALID_FISCAL_YEAR EXCEPTION;
89 NOT_A_TAX_BOOK EXCEPTION;
90 DEPRN_STATUS_NOT_C EXCEPTION;
91 STATUS_NOT_NEW_OR_ARCHIVE EXCEPTION;
92 NOT_PROCESSED_FOR_LAST_YEAR EXCEPTION;
93 STATUS_NOT_ARCHVD_OR_RSTORE EXCEPTION;
94 STATUS_NOT_PURGED_LAST_YEAR EXCEPTION;
95 STATUS_NOT_PURGED EXCEPTION;
96 STATUS_PURGED EXCEPTION;
97 INCORRECT_SET_OF_ROWS EXCEPTION;
98 UNABLE_TO_DO_SQL EXCEPTION;
99 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_ADJUSTMENT';
100
101
102 BEGIN
103
104 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
105 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
106 END IF;
107
108 fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
109 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
110 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
111 fnd_message.set_name('JL', 'JL_CO_FA_BOOK');
112 fnd_message.set_token('BOOK', p_book);
113 fnd_file.put_line( 1, fnd_message.get);
114 fnd_message.set_name('JL', 'JL_CO_FA_FISCAL_YEAR');
115 fnd_message.set_token('FISCAL_YEAR', p_fiscal_year);
116 fnd_file.put_line( 1, fnd_message.get);
117 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_OPTION');
118 fnd_message.set_token('OPTION', p_option);
119 fnd_file.put_line( 1, fnd_message.get);
120 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
121 ---------------------------------------------------------
122 -- Find who_columns values --
123 ---------------------------------------------------------
124
125 find_who_columns;
126
127 ---------------------------------------------------------
128 -- get informaton from fa_book_controls --
129 ---------------------------------------------------------
130
131 SELECT deprn_calendar,
132 initial_period_counter,
133 current_fiscal_year,
134 deprn_status,
135 book_class
136 INTO x_deprn_calendar,
137 x_initial_period_counter,
138 x_current_fiscal_year,
139 x_deprn_status,
140 x_book_class
141 FROM fa_book_controls
142 WHERE book_type_code = p_book;
143
144 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
145 fnd_file.put_line( 1, 'Deprn calendar :'||x_deprn_calendar);
146 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn calendar :'||x_deprn_calendar);
147 fnd_file.put_line( 1, 'Initial period Counter :'||to_char(x_initial_period_counter));
148 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial period Counter :'||to_char(x_initial_period_counter));
149 fnd_file.put_line( 1, 'Current fiscal year :'||x_current_fiscal_year);
150 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Current fiscal year :'||x_current_fiscal_year);
151 fnd_file.put_line( 1, 'Deprn Status :'||x_deprn_status);
152 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn Status :'||x_deprn_status);
153 fnd_file.put_line( 1, 'book class :'||x_book_class);
154 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'book class :'||x_book_class);
155
156 END IF;
157
158
159 ---------------------------------------------------------
160 -- Stop the program if input parameter p_fiscal_year --
161 -- is greater than equal to current_fiscal_year in --
162 -- fa_book_controls --
163 ---------------------------------------------------------
164
165 IF p_fiscal_year >= x_current_fiscal_year THEN
166 RAISE INVALID_FISCAL_YEAR;
167 END IF;
168
169 ---------------------------------------------------------
170 -- Get intial fiscal year --
171 ---------------------------------------------------------
172
173 SELECT fiscal_year
174 INTO x_initial_fiscal_year
175 FROM fa_deprn_periods
176 WHERE book_type_code = p_book
177 AND period_counter = x_initial_period_counter;
178
179 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
180 fnd_file.put_line( 1, 'Initial Fiscal year :'||x_initial_fiscal_year);
181 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial Fiscal year :'||x_initial_fiscal_year);
182 END IF;
183
184 ---------------------------------------------------------
185 -- Show the error conditions and finish the procedure --
186 -- if any of the following conditions not satisfied --
187 ---------------------------------------------------------
188
189 ---------------------------------------------------------
190 /* -- Stop the program if book_class is not TAX --
191 ---------------------------------------------------------
192
193
194
195 IF (x_book_class <> 'TAX') THEN
196 RAISE NOT_A_TAX_BOOK;
197 END IF; */
198
199 ---------------------------------------------------------
200 -- Stop the program if deprn_status for the book --
201 -- is not C. --
202 ---------------------------------------------------------
203
204 IF (x_deprn_status <> 'C') THEN
205 RAISE DEPRN_STATUS_NOT_C;
206 END IF;
207
208 ---------------------------------------------------------
209 -- Check the conditions when option is ARCHAIVE --
210 ---------------------------------------------------------
211
212 IF p_option = 'ARCHIVE' THEN
213
214 ---------------------------------------------------------
215 -- Stop the program if row exists for the couple --
216 -- book-year in jl_co_fa_purge and their status is --
217 -- 'RESTORED' or 'PURGED' --
218 ---------------------------------------------------------
219
220 SELECT count(*)
221 INTO x_count
222 FROM jl_co_fa_purge
223 WHERE book_type_code = p_book
224 AND fiscal_year = p_fiscal_year
225 AND status IN ( 'RESTORED', 'PURGED');
226 IF x_count <> 0 then
227 RAISE STATUS_NOT_NEW_OR_ARCHIVE;
228 END IF;
229
230 ---------------------------------------------------------
231 -- Stop the program if any of the processes archive, --
232 -- purge or restore are not done for previous fiscal --
233 -- year. --
234 ---------------------------------------------------------
235
236 IF (p_fiscal_year > x_initial_fiscal_year) THEN
237 SELECT count(*)
238 INTO x_count
239 FROM jl_co_fa_purge
240 WHERE book_type_code = p_book
241 AND fiscal_year = (p_fiscal_year -1)
242 AND status IN ('PURGED', 'ARCHIVED', 'RESTORE');
243 IF (x_count = 0) THEN
244 RAISE NOT_PROCESSED_FOR_LAST_YEAR;
245
246 END IF;
247
248 END IF;
249
250 END IF;
251
252 ---------------------------------------------------------
253 -- Check the conditions when option is DELETE --
254 ---------------------------------------------------------
255
256 IF (p_option = 'DELETE') THEN
257
258 ---------------------------------------------------------
259 -- Stop the program if row exists for the couple --
260 -- book-year in jl_co_fa_purge and their status is --
261 -- different from 'RESTORED' or 'ARCHAIVED' --
262 ---------------------------------------------------------
263
264 SELECT count(*)
265 INTO x_count
266 FROM jl_co_fa_purge
267 WHERE book_type_code = p_book
268 AND fiscal_year = p_fiscal_year
269 AND status IN ('ARCHIVED', 'RESTORED');
270
271
272 IF (x_count = 0) THEN
273 RAISE STATUS_NOT_ARCHVD_OR_RSTORE;
274 null;
275 END IF;
276
277 ---------------------------------------------------------
278 -- Stop the program if purge is not processed --
279 -- for previous fiscal year --
280 ---------------------------------------------------------
281
282 IF (p_fiscal_year > x_initial_fiscal_year) THEN
283 SELECT count(*)
284 INTO x_count
285 FROM jl_co_fa_purge
286 WHERE book_type_code = p_book
287 AND fiscal_year = (p_fiscal_year -1)
288 AND status IN ('PURGED');
289 IF (x_count = 0) THEN
290 RAISE STATUS_NOT_PURGED_LAST_YEAR;
291
292 END IF;
293
294 END IF;
295
296 END IF;
297
298 ---------------------------------------------------------
299 -- Check the conditions when option is RESTORE --
300 ---------------------------------------------------------
301
302 IF (p_option = 'RESTORE') THEN
303
304 ---------------------------------------------------------
305 -- Stop the program if row exists for the couple --
306 -- book-year in jl_co_fa_purge and their status is --
307 -- different from 'PURGED' --
308 ---------------------------------------------------------
309
310 SELECT count(*)
311 INTO x_count
312 FROM jl_co_fa_purge
313 WHERE book_type_code = p_book
314 AND fiscal_year = p_fiscal_year
315 AND status = 'PURGED';
316 IF (x_count = 0) THEN
317 RAISE STATUS_NOT_PURGED;
318
319 END IF;
320
321 ---------------------------------------------------------
322 -- Stop the program if status for next fiscal year --
323 -- is 'PURGED' --
324 ---------------------------------------------------------
325
326 IF (p_fiscal_year < x_current_fiscal_year) THEN
327
328 SELECT count(*)
329 INTO x_count
330 FROM jl_co_fa_purge
331 WHERE book_type_code = p_book
332 AND fiscal_year = (p_fiscal_year +1)
333 ANd status = 'PURGED';
334 IF (x_count <> 0) THEN
335 RAISE STATUS_PURGED;
336
337 END IF;
338
339 END IF;
340 END IF;
341
342 ---------------------------------------------------------
343 -- find the row from jl_co_fa_purge --
344 ---------------------------------------------------------
345
346 SELECT count(*)
347 INTO x_count
348 FROM jl_co_fa_purge
349 WHERE book_type_code = p_book
350 AND fiscal_year = p_fiscal_year;
351
352 ---------------------------------------------------------
353 -- Insert a row if row is not available in --
354 -- jl_co_fa_purge --
355 ---------------------------------------------------------
356
357 IF x_count = 0 THEN
358 INSERT INTO jl_co_fa_purge(
359 purge_id,
360 book_type_code,
361 fiscal_year,
362 status,
363 appraisals_rows_archived,
364 appraisals_check_sum,
365 asset_apprs_rows_archived,
366 asset_apprs_check_sum,
367 adjustments_rows_archived,
368 adjustments_check_sum,
369 appraisal_books_rows_archived,
370 appraisal_books_check_sum,
371 LAST_UPDATE_DATE,
372 LAST_UPDATED_BY,
373 CREATION_DATE,
374 CREATED_BY,
375 LAST_UPDATE_LOGIN,
376 request_id,
377 program_application_id,
378 program_id,
379 program_update_date)
380 VALUES (jl_co_fa_purge_s.NEXTVAL,
381 p_book,
382 p_fiscal_year,
383 'NEW',
384 0,
385 0,
386 0,
387 0,
388 0,
389 0,
390 0,
391 0,
392 x_sysdate,
393 x_last_updated_by,
394 x_sysdate,
395 x_last_updated_by,
396 x_last_update_login,
397 x_request_id,
398 x_program_application_id,
399 x_program_id,
400 x_sysdate);
401
402 END IF;
403
404 ---------------------------------------------------------
405 -- Store the purge_id for future use --
406 ---------------------------------------------------------
407
408 SELECT purge_id
409 INTO x_index
410 FROM jl_co_fa_purge
411 WHERE book_type_code = p_book
412 AND fiscal_year = p_fiscal_year;
413
414 x_adj_table := 'JL_CO_FA_ADJUSTMENTS'||TO_CHAR(x_index);
415
416
417 ---------------------------------------------------------
418 -- Get Oracle username --
419 ---------------------------------------------------------
420
421 SELECT u.oracle_username
422 INTO x_oracle_username
423 FROM fnd_oracle_userid u,
424 fnd_product_installations p,
425 fnd_application a
426 WHERE a.application_short_name = 'JL'
427 AND p.application_id = a.application_id
428 AND p.oracle_id = u.oracle_id;
429
430
431
432 ---------------------------------------------------------
433 -- Process for the options 'ARCHIVE' or 'PURGE' --
434 ---------------------------------------------------------
435
436
437 IF (p_option IN ('ARCHIVE', 'DELETE')) THEN
438
439 ---------------------------------------------------------
440 -- Get the value of start period counter and end period--
441 -- counter for the fiscal year --
442 ---------------------------------------------------------
443
444 /* SELECT number_per_fiscal_year
445 INTO x_period_number
446 FROM fa_calendar_types
447 WHERE calendar_type = x_deprn_calendar;
448
449 SELECT period_counter
450 INTO x_start_period_counter
451 FROM fa_deprn_periods
452 WHERE book_type_code = p_book
453 AND fiscal_year = p_fiscal_year
454 AND period_num = 1;
455
456 x_end_period_counter := x_start_period_counter + x_period_number; */
457
458 SELECT MIN(period_counter),
459 MAX(period_counter)
460 INTO x_start_period_counter,
461 x_end_period_counter
462 FROM fa_deprn_periods
463 WHERE book_type_code = p_book
464 AND fiscal_year = p_fiscal_year;
465
466 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
467 fnd_file.put_line( 1, 'Start period Counter :' || to_char(x_start_period_counter));
468 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start period Counter :' || to_char(x_start_period_counter));
469 fnd_file.put_line( 1, 'Start end Counter :' || to_char(x_end_period_counter));
470 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start end Counter :' || to_char(x_end_period_counter));
471 END IF;
472
473
474 ---------------------------------------------------------
475 --Complete the process with error if any of the record --
476 --in to be processed is not posted to GL --
477 ---------------------------------------------------------
478
479 SELECT count(*)
480 INTO x_count
481 FROM jl_co_fa_adjustments
482 WHERE book_type_code = p_book
483 AND posting_flag <> 'C'
484 AND period_counter_created >= x_start_period_counter
485 AND period_counter_created <= x_end_period_counter;
486
487 IF x_count <> 0 THEN
488 RAISE NOT_POSTED_TO_GL;
489 END IF;
490
491
492 ---------------------------------------------------------
493 -- Get no of row to be processed and sum of amount for --
494 -- future check --
495 ---------------------------------------------------------
496
497 SELECT count(*),
498 NVL(SUM(adjustment_amount), 0)
499 INTO x_adjustments_rows,
500 x_adjustments_amount
501 FROM jl_co_fa_adjustments
502 WHERE book_type_code = p_book
503 AND period_counter_adjusted >= x_start_period_counter
504 AND period_counter_adjusted <= x_end_period_counter;
505
506 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
507 fnd_file.put_line( 1, 'adjustment rows :' || to_char(x_adjustments_rows));
508 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'adjustment rows :' || to_char(x_adjustments_rows));
509 fnd_file.put_line( 1, 'adjustment amount :' || to_char(x_adjustments_amount));
510 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'adjustment amount :' || to_char(x_adjustments_amount));
511 END IF;
512
513
514
515
516 ---------------------------------------------------------
517 -- If the option is 'ARCHIVED' then make following --
518 -- process --
519 ---------------------------------------------------------
520
521 IF (p_option = 'ARCHIVE') THEN
522
523 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
524 fnd_message.set_token('OPTION', p_option);
525 fnd_file.put_line( 1, fnd_message.get);
526
527 ---------------------------------------------------------
528 -- Check weather backup table for adjustments is --
529 -- exists. Its name is jl_co_fa_adjustment with --
530 -- extension purge_id --
531 ---------------------------------------------------------
532
533 SELECT count(*)
534 INTO x_count
535 FROM all_tables
536 WHERE table_name = x_adj_table
537 AND owner = x_oracle_username;
538
539 ---------------------------------------------------------
540 -- If backup table does exists then drop it --
541 ---------------------------------------------------------
542
543 IF x_count <> 0 THEN
544
545 x_string := 'drop table '||x_oracle_username||'.'||ltrim(rtrim(x_adj_table));
546
547 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
548 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
549 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQl statement to be processed :'||x_string);
550 END IF;
551
552 IF NOT (do_sql(x_string)) THEN
553 RAISE UNABLE_TO_DO_SQL;
554 END IF;
555 END IF;
556
557
558
559 ---------------------------------------------------------
560 -- create tables and insert the rows to be archived --
561 ---------------------------------------------------------
562
563 IF NOT (storage_factor('JL_CO_FA_ADJUSTMENTS',
564 x_adjustments_rows,
565 x_storage_factor)) THEN
566
567 RAISE UNABLE_TO_DO_SQL;
568
569 END IF;
570
571 x_string := 'create table '||x_oracle_username||'.'||ltrim(rtrim(x_adj_table))||
572 ' STORAGE( INITIAL '||TO_CHAR(ceil(x_storage_factor))||'K '||
573 'NEXT '||TO_CHAR(ceil(x_storage_factor/2))||'K '||
574 'MINEXTENTS 1 MAXEXTENTS 20 PCTINCREASE 100) '||
575 ' as select * from jl_co_fa_adjustments'||
576 ' where book_type_code ='||''''||p_book||''''||
577 ' and period_counter_adjusted >= '||to_char(x_start_period_counter)||
578 ' and period_counter_adjusted <= '||to_char(x_end_period_counter);
579
580 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
581 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
582 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQl statement to be processed :'||x_string);
583 END IF;
584
585 IF NOT (do_sql(x_string)) THEN
586 RAISE UNABLE_TO_DO_SQL;
587 END IF;
588
589
590
591 ---------------------------------------------------------
592 -- change the status in jl_co_fa_purge to 'ARCHIVE' --
593 ---------------------------------------------------------
594 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
595 fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
596 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
597 END IF;
598
599
600 UPDATE jl_co_fa_purge SET
601 adjustments_rows_archived = x_adjustments_rows,
602 adjustments_check_sum = x_adjustments_amount,
603 status = 'ARCHIVED',
604 last_update_date = x_sysdate,
605 last_updated_by = x_last_updated_by,
606 last_update_login = x_last_update_login,
607 program_update_date = x_sysdate
608 WHERE purge_id = x_index;
609
610 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
611 fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
612 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
613 END IF;
614
615
616 ELSE
617
618 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
619 fnd_message.set_token('OPTION', p_option);
620 fnd_file.put_line( 1, fnd_message.get);
621
622 ---------------------------------------------------------
623 -- If the option is purge then check for correct set --
624 -- of rows to be purged. Stop the program if anything --
625 -- is wrong --
626 ---------------------------------------------------------
627
628 SELECT COUNT(*)
629 INTO x_count
630 FROM jl_co_fa_purge
631 WHERE purge_id = x_index
632 AND adjustments_rows_archived = x_adjustments_rows
633 AND adjustments_check_sum = x_adjustments_amount;
634 IF x_count = 0 THEN
635 RAISE INCORRECT_SET_OF_ROWS;
636
637 ELSE
638
639 ---------------------------------------------------------
640 -- Delete the rows from jl_co_fa_adjustments and --
641
642 -- change the status in jl_co_fa_purge to 'PURGED' --
643 ---------------------------------------------------------
644 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
645 fnd_file.put_line( 1, 'Deleting JL_CO_FA_ADJUSTMENTS');
646 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleting JL_CO_FA_ADJUSTMENTS');
647 END IF;
648
649 DELETE FROM jl_co_fa_adjustments
650 WHERE book_type_code = p_book
651 AND period_counter_adjusted >= x_start_period_counter
652 AND period_counter_adjusted <= x_end_period_counter;
653
654 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
655 fnd_file.put_line( 1, 'Deleted JL_CO_FA_ADJUSTMENTS');
656 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_ADJUSTMENTS');
657 END IF;
658
659
660 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
661 fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
662 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
663 END IF;
664
665
666 UPDATE jl_co_fa_purge
667 SET status = 'PURGED',
668 last_update_date = x_sysdate,
669 last_updated_by = x_last_updated_by,
670 last_update_login = x_last_update_login,
671 program_update_date = x_sysdate
672 WHERE purge_id = x_index;
673
674 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
675 fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
676 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
677 END IF;
678
679 END IF;
680 END IF;
681 ELSE
682
683 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
684 fnd_message.set_token('OPTION', p_option);
685 fnd_file.put_line( 1, fnd_message.get);
686
687 ---------------------------------------------------------
688 -- Process for the option 'RESTORE' --
689 ---------------------------------------------------------
690
691 x_string := 'insert into jl_co_fa_adjustments select * from '||x_oracle_username||'.'||ltrim(rtrim(x_adj_table));
692 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
693 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
694 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQl statement to be processed :'||x_string);
695 END IF;
696
697 IF NOT (do_sql(x_string)) THEN
698 RAISE UNABLE_TO_DO_SQL;
699 END IF;
700
701 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
702 fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
703 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
704 END IF;
705
706
707 UPDATE jl_co_fa_purge
708 SET status = 'RESTORED',
709 last_update_date = x_sysdate,
710 last_updated_by = x_last_updated_by,
711 last_update_login = x_last_update_login,
712 program_update_date = x_sysdate
713 WHERE purge_id = x_index;
714
715 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
716 fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
717 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
718 END IF;
719
720 END IF;
721 commit;
722
723 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
724 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
725 END IF;
726
727 EXCEPTION
728
729 WHEN NOT_POSTED_TO_GL THEN
730 fnd_message.set_name('JL', 'JL_CO_FA_NOT_POSTED_TO_GL');
731 err_msg := fnd_message.get;
732 fnd_file.put_line(fnd_file.log, err_msg);
733 call_status := fnd_concurrent.set_completion_status('ERROR','');
734 /*
735 fnd_message.raise_error;
736 */
737
738 WHEN INVALID_FISCAL_YEAR THEN
739 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_FISCAL_YEAR');
740 err_msg := fnd_message.get;
741 fnd_file.put_line(fnd_file.log, err_msg);
742 call_status := fnd_concurrent.set_completion_status('ERROR','');
743 /*
744 fnd_message.raise_error;
745 */
746
747 WHEN NOT_A_TAX_BOOK THEN
748 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_TAX_BOOK');
749 fnd_message.set_token('BOOK', p_book);
750 err_msg := fnd_message.get;
751 fnd_file.put_line(fnd_file.log, err_msg);
752 call_status := fnd_concurrent.set_completion_status('ERROR','');
753 /*
754 fnd_message.raise_error;
755 */
756
757 WHEN DEPRN_STATUS_NOT_C THEN
758 fnd_message.set_name('JL', 'JL_CO_FA_DEPRECIATION_STATUS');
759 fnd_message.set_token('BOOK', p_book);
760 err_msg := fnd_message.get;
761 fnd_file.put_line(fnd_file.log, err_msg);
762 call_status := fnd_concurrent.set_completion_status('ERROR','');
763 /*
764 fnd_message.raise_error;
765 */
766
767 WHEN STATUS_NOT_NEW_OR_ARCHIVE THEN
768 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
769 err_msg := fnd_message.get;
770 fnd_file.put_line(fnd_file.log, err_msg);
771 call_status := fnd_concurrent.set_completion_status('ERROR','');
772 /*
773 fnd_message.raise_error;
774 */
775
776 WHEN NOT_PROCESSED_FOR_LAST_YEAR THEN
777 fnd_message.set_name('JL', 'JL_CO_FA_FISCAL_YEAR_NOT_PROC');
778 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year - 1));
779 err_msg := fnd_message.get;
780 fnd_file.put_line(fnd_file.log, err_msg);
781 call_status := fnd_concurrent.set_completion_status('ERROR','');
782 /*
783 fnd_message.raise_error;
784 */
785
786 WHEN STATUS_NOT_ARCHVD_OR_RSTORE THEN
787 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
788 err_msg := fnd_message.get;
789 fnd_file.put_line(fnd_file.log, err_msg);
790 call_status := fnd_concurrent.set_completion_status('ERROR','');
791 /*
792 fnd_message.raise_error;
793 */
794
795 WHEN STATUS_NOT_PURGED_LAST_YEAR THEN
796 fnd_message.set_name('JL', 'JL_CO_FA_FISCAL_YEAR_NOT_PROC');
797 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year - 1));
798 err_msg := fnd_message.get;
799 fnd_file.put_line(fnd_file.log, err_msg);
800 call_status := fnd_concurrent.set_completion_status('ERROR','');
801 /*
802 fnd_message.raise_error;
803 */
804
805 WHEN STATUS_NOT_PURGED THEN
806 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
807 err_msg := fnd_message.get;
808 fnd_file.put_line(fnd_file.log, err_msg);
809 call_status := fnd_concurrent.set_completion_status('ERROR','');
810 /*
811 fnd_message.raise_error;
812 */
813
814
815 WHEN STATUS_PURGED THEN
816 fnd_message.set_name('JL', 'JL_CO_FA_PURGED');
817 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year +1));
818 err_msg := fnd_message.get;
819 fnd_file.put_line(fnd_file.log, err_msg);
820 call_status := fnd_concurrent.set_completion_status('ERROR','');
821 /*
822 fnd_message.raise_error;
823 */
824
825 WHEN INCORRECT_SET_OF_ROWS THEN
826 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_SET_OF_ROWS');
827 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year));
828 err_msg := fnd_message.get;
829 fnd_file.put_line(fnd_file.log, err_msg);
830 call_status := fnd_concurrent.set_completion_status('ERROR','');
831 /*
832 fnd_message.raise_error;
833 */
834
835 WHEN UNABLE_TO_DO_SQL THEN
836 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
837 fnd_file.put_line( fnd_file.log, fnd_message.get);
838 err_num := SQLCODE;
839 err_msg := substr(SQLERRM, 1, 200);
840 ROLLBACK;
841 RAISE_APPLICATION_ERROR( err_num, err_msg);
842
843
844 WHEN OTHERS THEN
845 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
846 fnd_file.put_line( fnd_file.log, fnd_message.get);
847 err_num := SQLCODE;
848 err_msg := substr(SQLERRM, 1, 200);
849 ROLLBACK;
850 RAISE_APPLICATION_ERROR( err_num, err_msg);
851
852 END purge_adjustment;
853
854 ----------------------------------------------------------------------------
855 -- PROCEDURE --
856 -- purge_appraisal --
857 -- --
858 -- DESCRIPTION --
859 -- Use this procedure to purge the tables jl_co-fa_appraisals and --
860 -- jl_co_fa_asset_apprs --
861 -- PURPOSE: --
862 -- Oracle Applications Rel 11.0 --
863 -- --
864 -- PARAMETERS: --
865 -- p_fiscal_year --
866 -- p_option --
867 -- --
868 -- HISTORY: --
869 -- 08/21/98 Sujit Dalai Created --
870 -- 05/28/99 Sujit Dalai Changed to provide storage criteria --
871 -- for backup tables and purge table --
872 -- JL_CO_FA_APPRAISAL_BOOKS. Also changed --
873 -- give message if any of appraisal infor --
874 -- mation to be purged has not been --
875 -- revalued. --
876 ----------------------------------------------------------------------------
877 PROCEDURE purge_appraisal( ERRBUF OUT NOCOPY VARCHAR2,
878 RETCODE OUT NOCOPY VARCHAR2,
879 p_fiscal_year NUMBER,
880 p_option VARCHAR2,
881 p_del_unproc_app VARCHAR2) IS
882
883 x_count NUMBER;
884 x_index NUMBER(7);
885 x_appr_table VARCHAR2(30);
886 x_asset_table VARCHAR2(30);
887 x_book_table VARCHAR2(30);
888 x_appraisal_rows NUMBER;
889 x_appraisal_amount NUMBER;
890 x_asset_rows NUMBER;
891 x_asset_amount NUMBER;
892 x_book_rows NUMBER;
893 x_book_amount NUMBER;
894 x_oracle_username VARCHAR2(30);
895 x_string VARCHAR2(250);
896 x_cursor INTEGER;
897 x_row_processed INTEGER;
898 x_storage_factor NUMBER;
899 err_num NUMBER;
900 err_msg VARCHAR2(2000);
901 call_status BOOLEAN;
902 x_del_unproc_app VARCHAR2(1);
903 STATUS_NOT_NEW_OR_ARCHIVE EXCEPTION;
904 NOTHING_TO_ARCHIVE EXCEPTION;
905 NOT_PROCESSED_FOR_LAST_YEAR EXCEPTION;
906 STATUS_NOT_ARCHVD_OR_RSTORE EXCEPTION;
907 STATUS_NOT_PURGED_LAST_YEAR EXCEPTION;
908 STATUS_NOT_PURGED EXCEPTION;
909 STATUS_PURGED EXCEPTION;
910 INCORRECT_SET_OF_ROWS EXCEPTION;
911 UNABLE_TO_DO_SQL EXCEPTION;
912 APPRAISAL_NOT_PROCESSED EXCEPTION;
913 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_APPRAISAL';
914
915
916 BEGIN
917
918 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
919 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
920 END IF;
921
922 fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
923 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
924 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
925 fnd_message.set_name('JL', 'JL_CO_FA_FISCAL_YEAR');
926 fnd_message.set_token('FISCAL_YEAR', p_fiscal_year);
927 fnd_file.put_line( 1, fnd_message.get);
928 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_OPTION');
929 fnd_message.set_token('OPTION', p_option);
930 fnd_file.put_line( 1, fnd_message.get);
931 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
932 ---------------------------------------------------------
933 -- Find who_columns values --
934 ---------------------------------------------------------
935
936 find_who_columns;
937 ---------------------------------------------------------
938 -- Complete the process with error if any appraisal of --
939 -- fiscal year is not processed --
940 ---------------------------------------------------------
941 x_del_unproc_app := nvl(p_del_unproc_app, 'N');
942 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943 fnd_file.put_line( 1, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
944 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
945 fnd_file.put_line( 1, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
946 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
947 END IF;
948
949 SELECT count(*)
950 INTO x_count
951 FROM jl_co_fa_appraisals
952 WHERE fiscal_year = p_fiscal_year
953 AND appraisal_status <> 'P';
954
955 IF x_count <> 0 THEN
956 IF x_del_unproc_app = 'Y' THEN
957 DELETE FROM jl_co_fa_asset_apprs
958 WHERE appraisal_id IN (select appraisal_id
959 from jl_co_fa_appraisals
960 where fiscal_year = p_fiscal_year
961 and appraisal_status <> 'P');
962 DELETE FROM jl_co_fa_appraisal_books
963 WHERE appraisal_id IN (select appraisal_id
964 from jl_co_fa_appraisals
965 where fiscal_year = p_fiscal_year
966 and appraisal_status <> 'P');
967
968 fnd_message.set_name('JL', 'JL_CO_FA_DELETED_APPRS');
969 fnd_message.set_token('FISCAL_YEAR', p_fiscal_year);
970
971 create_output_headings(p_fiscal_year);
972
973 DELETE FROM jl_co_fa_appraisals
974 WHERE fiscal_year = p_fiscal_year
975 AND appraisal_status <> 'P';
976 ELSE
977 fnd_message.set_name('JL', 'JL_CO_FA_DEL_UNPROC_APPRS');
978 fnd_message.set_token('FISCAL_YEAR', p_fiscal_year);
979
980 create_output_headings(p_fiscal_year);
981
982 RAISE APPRAISAL_NOT_PROCESSED;
983 END IF;
984 END IF;
985
986
987 ---------------------------------------------------------
988 -- Show the error conditions and finish the procedure --
989 -- if any of the following conditions not satisfied --
990 ---------------------------------------------------------
991 ---------------------------------------------------------
992 -- Check the conditions when option is ARCHAIVE --
993 ---------------------------------------------------------
994
995 IF p_option = 'ARCHIVE' THEN
996
997 ---------------------------------------------------------
998 -- Stop the program if there is no row in appraisals --
999 -- table to archive --
1000 ---------------------------------------------------------
1001 SELECT count(*)
1002 INTO x_count
1003 FROM jl_co_fa_appraisals
1004 WHERE fiscal_year = p_fiscal_year;
1005 If x_count = 0 THEN
1006 RAISE NOTHING_TO_ARCHIVE;
1007 END IF;
1008
1009 ---------------------------------------------------------
1010 -- Stop the program if row exists for the year --
1011 -- in jl_co_fa_purge and their status is --
1012 -- 'RESTORED' or 'PURGED' --
1013 ---------------------------------------------------------
1014
1015 SELECT count(*)
1016 INTO x_counT
1017 FROM jl_co_fa_purge
1018 WHERE book_type_code IS NULL
1019 AND fiscal_year = p_fiscal_year
1020 AND status IN ( 'RESTORED', 'PURGED');
1021 IF x_count <> 0 then
1022 RAISE STATUS_NOT_NEW_OR_ARCHIVE;
1023 END IF;
1024
1025 ---------------------------------------------------------
1026 -- Stop the program if any of the processes archive, --
1027 -- purge or restore are not done for previous fiscal --
1028 -- year --
1029 ---------------------------------------------------------
1030
1031 SELECT count(*)
1032 INTO x_count
1033 FROM jl_co_fa_appraisals
1034 WHERE fiscal_year = (p_fiscal_year - 1);
1035
1036 IF x_count <> 0 THEN
1037
1038 SELECT count(*)
1039 INTO x_count
1040 FROM jl_co_fa_purge
1041 WHERE book_type_code IS NULL
1042 AND fiscal_year = (p_fiscal_year -1)
1043 AND status IN ('PURGED', 'ARCHIVED', 'RESTORE');
1044 IF (x_count <> 0) THEN
1045 RAISE NOT_PROCESSED_FOR_LAST_YEAR;
1046
1047 END IF;
1048
1049 END IF;
1050
1051 END IF;
1052
1053 ---------------------------------------------------------
1054 -- Check the conditions when option is DELETE --
1055 ---------------------------------------------------------
1056
1057 IF (p_option = 'DELETE') THEN
1058
1059 ---------------------------------------------------------
1060 -- Stop the program if row exists for the couple --
1061 -- book-year in jl_co_fa_purge and their status is --
1062 -- different from 'RESTORED' or 'ARCHAIVED' --
1063 ---------------------------------------------------------
1064
1065 SELECT count(*)
1066 INTO x_count
1067 FROM jl_co_fa_purge
1068 WHERE book_type_code IS NULL
1069 AND fiscal_year = p_fiscal_year
1070 AND status IN ('ARCHIVED', 'RESTORED');
1071 IF (x_count = 0) THEN
1072 RAISE STATUS_NOT_ARCHVD_OR_RSTORE;
1073 END IF;
1074
1075 ---------------------------------------------------------
1076 -- Stop the program if purge is not processed --
1077 -- for previous fiscal year --
1078 ---------------------------------------------------------
1079 SELECT count(*)
1080 INTO x_count
1081 FROM jl_co_fa_appraisals
1082 WHERE fiscal_year = (p_fiscal_year - 1);
1083 IF x_count <> 0 THEN
1084 SELECT count(*)
1085 INTO x_count
1086 FROM jl_co_fa_purge
1087 WHERE book_type_code IS NULL
1088 AND fiscal_year = (p_fiscal_year -1)
1089 AND status IN ('PURGED');
1090 IF (x_count = 0) THEN
1091 RAISE STATUS_NOT_PURGED_LAST_YEAR;
1092
1093 END IF;
1094
1095 END IF;
1096
1097 END IF;
1098
1099 ---------------------------------------------------------
1100 -- Check the conditions when option is RESTORE --
1101 ---------------------------------------------------------
1102
1103 IF (p_option = 'RESTORE') THEN
1104
1105 ---------------------------------------------------------
1106 -- Stop the program if row exists for the couple --
1107 -- book-year in jl_co_fa_purge and their status is --
1108 -- different from 'PURGED' --
1109 ---------------------------------------------------------
1110
1111 SELECT count(*)
1112 INTO x_count
1113 FROM jl_co_fa_purge
1114 WHERE book_type_code IS NULL
1115 AND fiscal_year = p_fiscal_year
1116 AND status = 'PURGED';
1117 IF (x_count = 0) THEN
1118 RAISE STATUS_NOT_PURGED;
1119
1120 END IF;
1121
1122 ---------------------------------------------------------
1123 -- Stop the program if status for next fiscal year --
1124 -- is 'PURGED' --
1125 ---------------------------------------------------------
1126
1127
1128
1129 SELECT count(*)
1130 INTO x_count
1131 FROM jl_co_fa_purge
1132 WHERE book_type_code IS NULL
1133 AND fiscal_year = (p_fiscal_year +1)
1134 ANd status = 'PURGED';
1135 IF (x_count <> 0) THEN
1136 RAISE STATUS_PURGED;
1137
1138 END IF;
1139
1140 END IF;
1141
1142 ---------------------------------------------------------
1143 -- find the row from jl_co_fa_purge --
1144 ---------------------------------------------------------
1145
1146 SELECT count(*)
1147 INTO x_count
1148 FROM jl_co_fa_purge
1149 WHERE book_type_code IS NULL
1150 AND fiscal_year = p_fiscal_year;
1151
1152 ---------------------------------------------------------
1153 -- Insert a row if row is not available in --
1154 -- jl_co_fa_purge --
1155 ---------------------------------------------------------
1156
1157 IF x_count = 0 THEN
1158
1159 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1160 fnd_file.put_line( 1, 'Inserting row into JL_CO_FA_PURGE');
1161 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserting row into JL_CO_FA_PURGE');
1162 END IF;
1163
1164 INSERT INTO jl_co_fa_purge(
1165 purge_id,
1166 fiscal_year,
1167 status,
1168 appraisals_rows_archived,
1169 appraisals_check_sum,
1170 asset_apprs_rows_archived,
1171 asset_apprs_check_sum,
1172 adjustments_rows_archived,
1173 adjustments_check_sum,
1174 appraisal_books_rows_archived,
1175 appraisal_books_check_sum,
1176 LAST_UPDATE_DATE,
1177 LAST_UPDATED_BY,
1178 CREATION_DATE,
1179 CREATED_BY,
1180 LAST_UPDATE_LOGIN,
1181 request_id,
1182 program_application_id,
1183 program_id,
1184 program_update_date)
1185 VALUES (jl_co_fa_purge_s.NEXTVAL,
1186 p_fiscal_year,
1187 'NEW',
1188 0,
1189 0,
1190 0,
1191 0,
1192 0,
1193 0,
1194 0,
1195 0,
1196 x_sysdate,
1197 x_last_updated_by,
1198 x_sysdate,
1199 x_last_updated_by,
1200 x_last_update_login,
1201 x_request_id,
1202 x_program_application_id,
1203 x_program_id,
1204 x_sysdate);
1205
1206 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1207 fnd_file.put_line( 1, 'Inserted row into JL_CO_FA_PURGE');
1208 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserted row into JL_CO_FA_PURGE');
1209 END IF;
1210
1211
1212
1213 END IF;
1214
1215 ---------------------------------------------------------
1216 -- Store the purge_id for future use --
1217 ---------------------------------------------------------
1218
1219 SELECT purge_id
1220 INTO x_index
1221 FROM jl_co_fa_purge
1222 WHERE book_type_code IS NULL
1223 AND fiscal_year = p_fiscal_year;
1224
1225 x_appr_table := 'JL_CO_FA_APPRAISALS'||TO_CHAR(x_index);
1226 x_asset_table := 'JL_CO_FA_ASSET_APPRS'||TO_CHAR(x_index);
1227 x_book_table := 'JL_CO_FA_APPRAISAL_BOOKS'||TO_CHAR(x_index);
1228
1229
1230 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1231 fnd_file.put_line( 1, 'Backup table for JL_CO_FA_APPRAISALS :'||x_appr_table);
1232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Backup table for JL_CO_FA_APPRAISALS :'||x_appr_table);
1233 fnd_file.put_line( 1, 'Backup table for JL_CO_FA_ASSET_APPRS :'||x_asset_table);
1234 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Backup table for JL_CO_FA_ASSET_APPRS :'||x_asset_table);
1235 END IF;
1236
1237 ---------------------------------------------------------
1238 -- Get Oracle username --
1239 ---------------------------------------------------------
1240
1241 SELECT u.oracle_username
1242 INTO x_oracle_username
1243 FROM fnd_oracle_userid u,
1244 fnd_product_installations p,
1245 fnd_application a
1246 WHERE a.application_short_name = 'JL'
1247 AND p.application_id = a.application_id
1248 AND p.oracle_id = u.oracle_id;
1249
1250
1251
1252
1253 ---------------------------------------------------------
1254 -- Process for the options 'ARCHIVE' or 'PURGE' --
1255 ---------------------------------------------------------
1256
1257
1258 IF (p_option IN ('ARCHIVE', 'DELETE')) THEN
1259
1260 ---------------------------------------------------------
1261 -- Get no of row to be processed and sum of amount for --
1262 -- future check --
1263 ---------------------------------------------------------
1264
1265 SELECT count(*),
1266 SUM(NVL(appraisal_id, 0))
1267 INTO x_appraisal_rows,
1268 x_appraisal_amount
1269 FROM jl_co_fa_appraisals
1270 WHERE fiscal_year = p_fiscal_year;
1271
1272 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1273 fnd_file.put_line( 1, 'No of appraisal rows :'||to_char(x_appraisal_rows));
1274 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of appraisal rows :'||to_char(x_appraisal_rows));
1275 fnd_file.put_line( 1, 'Appraisal Amount :'||to_char(x_appraisal_amount));
1276 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal Amount :'||to_char(x_appraisal_amount));
1277 END IF;
1278
1279
1280 SELECT count(*),
1281 SUM(NVL(appraisal_value, 0))
1282 INTO x_asset_rows,
1283 x_asset_amount
1284 FROM jl_co_fa_asset_apprs
1285 WHERE appraisal_id IN (select appraisal_id
1286 from jl_co_fa_appraisals
1287 where fiscal_year = p_fiscal_year);
1288
1289 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1290 fnd_file.put_line( 1, 'No of asset rows :'||to_char(x_asset_rows));
1291 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of asset rows :'||to_char(x_asset_rows));
1292 fnd_file.put_line( 1, 'Asset Amount :'||to_char(x_asset_amount));
1293 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset Amount :'||to_char(x_asset_amount));
1294 END IF;
1295
1296 SELECT count(*),
1297 SUM(NVL(appraisal_id, 0))
1298 INTO x_book_rows,
1299 x_book_amount
1300 FROM jl_co_fa_appraisal_books
1301 WHERE appraisal_id IN (select appraisal_id
1302 from jl_co_fa_appraisals
1303 where fiscal_year = p_fiscal_year);
1304
1305 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1306 fnd_file.put_line( 1, 'No of appraisal_book rows :'||to_char(x_book_rows));
1307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of appraisal_book rows :'||to_char(x_book_rows));
1308 fnd_file.put_line( 1, 'Appraisal_book Amount :'||to_char(x_book_amount));
1309 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal_book Amount :'||to_char(x_book_amount));
1310 END IF;
1311
1312
1313
1314 ---------------------------------------------------------
1315 -- Process for the option 'ARCHIVE' --
1316 ---------------------------------------------------------
1317
1318 IF (p_option = 'ARCHIVE') THEN
1319
1320 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
1321 fnd_message.set_token('OPTION', p_option);
1322 fnd_file.put_line( 1, fnd_message.get);
1323
1324 ---------------------------------------------------------
1325 -- Check weather backup tables for appraisal is exists.--
1326 -- Its name is jl_co_fa_appraisals with extension --
1327 -- purge_id. --
1328 ---------------------------------------------------------
1329
1330 SELECT count(*)
1331 INTO x_count
1332 FROM all_tables
1333 WHERE table_name = x_appr_table
1334 AND owner = x_oracle_username;
1335
1336 ---------------------------------------------------------
1337 -- If backup table does exists then drop the table. --
1338 ---------------------------------------------------------
1339
1340 IF x_count <> 0 THEN
1341 x_string := 'drop table '||x_oracle_username||'.'||ltrim(rtrim(x_appr_table));
1342 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1343 fnd_file.put_line( 1, 'SQL statement to be processed :'||x_string);
1344 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1345 END IF;
1346
1347 IF NOT (do_sql(x_string)) THEN
1348 RAISE UNABLE_TO_DO_SQL;
1349 END IF;
1350 END IF;
1351
1352 ---------------------------------------------------------
1353 -- Check weather backup tables for assets is exists. --
1354 -- Its name is jl_co_fa_asset_apprs with extension --
1355 -- purge_id --
1356 ---------------------------------------------------------
1357
1358 SELECT count(*)
1359 INTO x_count
1360 FROM all_tables
1361 WHERE table_name = x_asset_table
1362 AND owner = x_oracle_username;
1363
1364 ---------------------------------------------------------
1365 -- If backup table does exists then drop the table --
1366 ---------------------------------------------------------
1367
1368 IF x_count <> 0 THEN
1369 x_string := 'Drop table '||x_oracle_username||'.'||ltrim(rtrim(x_asset_table));
1370 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1371 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1372 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1373 END IF;
1374
1375 IF NOT (do_sql(x_string)) THEN
1376 RAISE UNABLE_TO_DO_SQL;
1377 END IF;
1378
1379 END IF;
1380
1381 ---------------------------------------------------------
1382 -- Check weather backup tables for books is exists. --
1383 -- Its name is jl_co_fa_appraisal_books with extension --
1384 -- purge_id --
1385 ---------------------------------------------------------
1386 SELECT count(*)
1387 INTO x_count
1388 FROM all_tables
1389 WHERE table_name = x_book_table
1390 AND owner = x_oracle_username;
1391
1392
1393 ---------------------------------------------------------
1394 -- If backup table does exists then drop the table --
1395 ---------------------------------------------------------
1396
1397 IF x_count <> 0 THEN
1398 x_string := 'Drop table '||x_oracle_username||'.'||ltrim(rtrim(x_book_table));
1399 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1400 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1401 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1402 END IF;
1403
1404 IF NOT (do_sql(x_string)) THEN
1405 RAISE UNABLE_TO_DO_SQL;
1406 END IF;
1407
1408 END IF;
1409
1410 ---------------------------------------------------------
1411 -- Create and insert the rows to be archived into the --
1412 -- backup tables --
1413 ---------------------------------------------------------
1414
1415 IF NOT (storage_factor('JL_CO_FA_APPRAISALS',
1416 x_appraisal_rows,
1417 x_storage_factor)) THEN
1418
1419 RAISE UNABLE_TO_DO_SQL;
1420
1421 END IF;
1422
1423
1424
1425 x_string := 'create table '||x_oracle_username||'.'||ltrim(rtrim(x_appr_table))||
1426 ' STORAGE( INITIAL '||TO_CHAR(ceil(x_storage_factor))||'K '||
1427 'NEXT '||TO_CHAR(ceil(x_storage_factor/2))||'K '||
1428 'MINEXTENTS 1 MAXEXTENTS 20 PCTINCREASE 100) '||
1429 ' as select * from jl_co_fa_appraisals'||
1430 ' where fiscal_year = '||p_fiscal_year;
1431
1432 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1433 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1434 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1435 END IF;
1436
1437 IF NOT (do_sql(x_string)) THEN
1438 RAISE UNABLE_TO_DO_SQL;
1439 END IF;
1440
1441 IF NOT (storage_factor('JL_CO_FA_ASSET_APPRS',
1442 x_asset_rows,
1443 x_storage_factor)) THEN
1444
1445 RAISE UNABLE_TO_DO_SQL;
1446
1447 END IF;
1448
1449 x_string := 'create table '||x_oracle_username||'.'||ltrim(rtrim(x_asset_table))||
1450 ' STORAGE( INITIAL '||TO_CHAR(ceil(x_storage_factor))||'K '||
1451 'NEXT '||TO_CHAR(ceil(x_storage_factor/2))||'K '||
1452 'MINEXTENTS 1 MAXEXTENTS 20 PCTINCREASE 100) '||
1453 ' as select * from jl_co_fa_asset_apprs'||
1454 ' where appraisal_id in (select appraisal_id from jl_co_fa_appraisals'||
1455 ' where fiscal_year = '||p_fiscal_year||')';
1456 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1457 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1458 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1459 END IF;
1460
1461 IF NOT (do_sql(x_string)) THEN
1462 RAISE UNABLE_TO_DO_SQL;
1463 END IF;
1464
1465 IF NOT (storage_factor('JL_CO_FA_APPRAISAL_BOOKS',
1466 x_book_rows,
1467 x_storage_factor)) THEN
1468
1469 RAISE UNABLE_TO_DO_SQL;
1470
1471 END IF;
1472
1473 x_string := 'create table '||x_oracle_username||'.'||ltrim(rtrim(x_book_table))||
1474 ' STORAGE( INITIAL '||TO_CHAR(ceil(x_storage_factor))||'K '||
1475 'NEXT '||TO_CHAR(ceil(x_storage_factor/2))||'K '||
1476 'MINEXTENTS 1 MAXEXTENTS 20 PCTINCREASE 100) '||
1477 'as select * from jl_co_fa_appraisal_books'||
1478 ' where appraisal_id in (select appraisal_id from jl_co_fa_appraisals'||
1479 ' where fiscal_year='||p_fiscal_year||')';
1480 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1481 fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1482 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1483 END IF;
1484
1485 IF NOT (do_sql(x_string)) THEN
1486 RAISE UNABLE_TO_DO_SQL;
1487 END IF;
1488
1489
1490 ---------------------------------------------------------
1491 -- change the status in jl_co_fa_purge to 'ARCHIVE' --
1492 ---------------------------------------------------------
1493 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1494 fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1495 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1496 END IF;
1497
1498
1499 UPDATE jl_co_fa_purge SET
1500 appraisals_rows_archived = x_appraisal_rows,
1501 appraisals_check_sum = x_appraisal_amount,
1502 asset_apprs_rows_archived = x_asset_rows,
1503 asset_apprs_check_sum = x_asset_amount,
1504 appraisal_books_rows_archived = x_book_rows,
1505 appraisal_books_check_sum = x_book_amount,
1506 status = 'ARCHIVED',
1507 last_update_date = x_sysdate,
1508 last_updated_by = x_last_updated_by,
1509 last_update_login = x_last_update_login,
1510 program_update_date = x_sysdate
1511 WHERE purge_id = x_index;
1512
1513 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1514 fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
1515 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
1516 END IF;
1517
1518 ELSE
1519
1520 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
1521 fnd_message.set_token('OPTION', p_option);
1522 fnd_file.put_line( 1, fnd_message.get);
1523
1524 ---------------------------------------------------------
1525 -- If the option is purge then check for correct set --
1526 -- of rows to be purged. Stop the program if anything --
1527 -- is wrong --
1528 ---------------------------------------------------------
1529
1530 SELECT COUNT(*)
1531 INTO x_count
1532 FROM jl_co_fa_purge
1533 WHERE purge_id = x_index
1534 AND appraisals_rows_archived = x_appraisal_rows
1535 AND appraisals_check_sum = x_appraisal_amount
1536 AND asset_apprs_rows_archived = x_asset_rows
1537 AND asset_apprs_check_sum = x_asset_amount
1538 AND appraisal_books_rows_archived = x_book_rows
1539 AND appraisal_books_check_sum = x_book_amount;
1540 IF x_count = 0 THEN
1541 RAISE INCORRECT_SET_OF_ROWS;
1542
1543 ELSE
1544
1545 ---------------------------------------------------------
1546 -- Delete the rows from jl_co_fa_adjustments, --
1547 -- jl_co_fa_appraisals and jl_co_fa_appraisal_books --
1548 -- and change the status in jl_co_fa_purge to 'PURGED' --
1549 ---------------------------------------------------------
1550
1551 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1552 fnd_file.put_line( 1, 'Deleting JL_CO_FA_APPRAISALS');
1553 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleting JL_CO_FA_ASSET_APPRS');
1554 END IF;
1555
1556 DELETE FROM jl_co_fa_asset_apprs
1557 WHERE appraisal_id IN (select appraisal_id
1558 from jl_co_fa_appraisals
1559 where fiscal_year = p_fiscal_year);
1560
1561 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1562 fnd_file.put_line( 1, 'Deleted JL_CO_FA_APPRAISALS');
1563 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_ASSET_APPRS');
1564 END IF;
1565
1566 DELETE FROM jl_co_fa_appraisal_books
1567 WHERE appraisal_id IN (select appraisal_id
1568 from jl_co_fa_appraisals
1569 where fiscal_year = p_fiscal_year);
1570
1571
1572 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1573 fnd_file.put_line( 1, 'Deleting JL_CO_FA_Appraisals');
1574 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleting JL_CO_FA_Appraisals');
1575 END IF;
1576
1577
1578
1579 DELETE FROM jl_co_fa_appraisals
1580 WHERE fiscal_year = p_fiscal_year;
1581
1582 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1583 fnd_file.put_line( 1, 'Deleted JL_CO_FA_Appraisals');
1584 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_Appraisals');
1585 END IF;
1586 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1587 fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1588 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1589 END IF;
1590
1591
1592 UPDATE jl_co_fa_purge
1593 SET status = 'PURGED',
1594 last_update_date = x_sysdate,
1595 last_updated_by = x_last_updated_by,
1596 last_update_login = x_last_update_login,
1597 program_update_date = x_sysdate
1598 WHERE purge_id = x_index;
1599
1600 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1601 fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
1602 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
1603 END IF;
1604
1605 END IF;
1606 END IF;
1607
1608 ELSE
1609
1610 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
1611 fnd_message.set_token('OPTION', p_option);
1612 fnd_file.put_line( 1, fnd_message.get);
1613
1614 ---------------------------------------------------------
1615 -- Process for the option 'RESTORE' --
1616 ---------------------------------------------------------
1617
1618 x_string := 'insert into jl_co_fa_appraisals select * from '||x_oracle_username||'.'||ltrim(rtrim(x_appr_table));
1619 IF NOT (do_sql(x_string)) THEN
1620 RAISE UNABLE_TO_DO_SQL;
1621 END IF;
1622 x_string := 'insert into jl_co_fa_asset_apprs select * from '||x_oracle_username||'.'||ltrim(rtrim(x_asset_table));
1623 IF NOT (do_sql(x_string)) THEN
1624 RAISE UNABLE_TO_DO_SQL;
1625 END IF;
1626
1627 x_string := 'insert into jl_co_fa_appraisal_books select * from '||x_oracle_username||'.'||ltrim(rtrim(x_book_table));
1628 IF NOT (do_sql(x_string)) THEN
1629 RAISE UNABLE_TO_DO_SQL;
1630 END IF;
1631
1632 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1633 fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1634 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1635 END IF;
1636
1637 UPDATE jl_co_fa_purge
1638 SET status = 'RESTORED',
1639 last_update_date = x_sysdate,
1640 last_updated_by = x_last_updated_by,
1641 last_update_login = x_last_update_login,
1642 program_update_date = x_sysdate
1643 WHERE purge_id = x_index;
1644
1645 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1646 fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
1647 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
1648 END IF;
1649
1650 END IF;
1651
1652 commit;
1653
1654 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1655 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1656 END IF;
1657
1658 EXCEPTION
1659
1660 WHEN APPRAISAL_NOT_PROCESSED THEN
1661 fnd_message.set_name('JL', 'JL_CO_FA_APPR_NOT_PROCESSED');
1662 err_msg := fnd_message.get;
1663 fnd_file.put_line(fnd_file.log, err_msg);
1664 call_status := fnd_concurrent.set_completion_status('ERROR','');
1665 /*
1666 fnd_message.raise_error;
1667 */
1668
1669 WHEN NOTHING_TO_ARCHIVE THEN
1670 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
1671 err_msg := fnd_message.get;
1672 fnd_file.put_line(fnd_file.log, err_msg);
1673 call_status := fnd_concurrent.set_completion_status('ERROR','');
1674 /*
1675 fnd_message.raise_error;
1676 /*
1677 app_exception.raise_exception (exception_type => 'APP',
1678 exception_code =>
1679 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_FA_NOTHING_TO_PROCESS'),
1680 exception_text => err_msg);
1681 */
1682
1683 WHEN STATUS_NOT_NEW_OR_ARCHIVE THEN
1684 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
1685 err_msg := fnd_message.get;
1686 fnd_file.put_line(fnd_file.log, err_msg);
1687 call_status := fnd_concurrent.set_completion_status('ERROR','');
1688 /*
1689 fnd_message.raise_error;
1690 */
1691
1692 WHEN NOT_PROCESSED_FOR_LAST_YEAR THEN
1693 fnd_message.set_name('JL', 'JL_CO_FA_FISCAL_YEAR_NOT_PROC');
1694 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year - 1));
1695 err_msg := fnd_message.get;
1696 fnd_file.put_line(fnd_file.log, err_msg);
1697 call_status := fnd_concurrent.set_completion_status('ERROR','');
1698 /*
1699 fnd_message.raise_error;
1700 */
1701
1702 WHEN STATUS_NOT_ARCHVD_OR_RSTORE THEN
1703 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
1704 err_msg := fnd_message.get;
1705 fnd_file.put_line(fnd_file.log, err_msg);
1706 call_status := fnd_concurrent.set_completion_status('ERROR','');
1707 /*
1708 fnd_message.raise_error;
1709 */
1710
1711 WHEN STATUS_NOT_PURGED_LAST_YEAR THEN
1712 fnd_message.set_name('JL', 'JL_CO_FA_FISCAL_YEAR_NOT_PROC');
1713 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year - 1));
1714 err_msg := fnd_message.get;
1715 fnd_file.put_line(fnd_file.log, err_msg);
1716 call_status := fnd_concurrent.set_completion_status('ERROR','');
1717 /*
1718 fnd_message.raise_error;
1719 */
1720
1721 WHEN STATUS_NOT_PURGED THEN
1722 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
1723 err_msg := fnd_message.get;
1724 fnd_file.put_line(fnd_file.log, err_msg);
1725 call_status := fnd_concurrent.set_completion_status('ERROR','');
1726 /*
1727 fnd_message.raise_error;
1728 */
1729
1730 WHEN STATUS_PURGED THEN
1731 fnd_message.set_name('JL', 'JL_CO_FA_PURGED');
1732 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year + 1));
1733 err_msg := fnd_message.get;
1734 fnd_file.put_line(fnd_file.log, err_msg);
1735 call_status := fnd_concurrent.set_completion_status('ERROR','');
1736 /*
1737 fnd_message.raise_error;
1738 */
1739
1740 WHEN INCORRECT_SET_OF_ROWS THEN
1741 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_SET_OF_ROWS');
1742 fnd_message.set_token('FISCAL_YEAR', TO_CHAR(p_fiscal_year));
1743 err_msg := fnd_message.get;
1744 fnd_file.put_line(fnd_file.log, err_msg);
1745 call_status := fnd_concurrent.set_completion_status('ERROR','');
1746 /*
1747 fnd_message.raise_error;
1748 */
1749
1750 WHEN UNABLE_TO_DO_SQL THEN
1751 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
1752 fnd_file.put_line( fnd_file.log, fnd_message.get);
1753 err_num := SQLCODE;
1754 err_msg := substr(SQLERRM, 1, 200);
1755 ROLLBACK;
1756 RAISE_APPLICATION_ERROR( err_num, err_msg);
1757
1758
1759
1760 WHEN OTHERS THEN
1761 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
1762 fnd_file.put_line( fnd_file.log, fnd_message.get);
1763 err_num := SQLCODE;
1764 err_msg := substr(SQLERRM, 1, 200);
1765 ROLLBACK;
1766 RAISE_APPLICATION_ERROR( err_num, err_msg);
1767
1768 END purge_appraisal;
1769
1770 ----------------------------------------------------------------------------
1771 -- PROCEDURE --
1772 -- find_who_columns --
1773 -- --
1774 -- DESCRIPTION --
1775 -- Use this procedure to get the values of who columns. --
1776 -- --
1777 -- PURPOSE: --
1778 -- Oracle Applications Rel 11.0 --
1779 -- --
1780 -- --
1781 -- HISTORY: --
1782 -- 08/21/98 Sujit Dalai Created --
1783 ----------------------------------------------------------------------------
1784 PROCEDURE find_who_columns IS
1785
1786 l_api_name CONSTANT VARCHAR2(30) := 'FIND_WHO_COLUMNS';
1787
1788 BEGIN
1789
1790 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1791 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1792 END IF;
1793
1794 x_last_updated_by := fnd_global.user_id;
1795 x_last_update_login := fnd_global.login_id;
1796 x_request_id := fnd_global.conc_request_id;
1797 x_program_application_id := fnd_global.prog_appl_id;
1798 x_program_id := fnd_global.conc_program_id;
1799 x_sysdate := SYSDATE;
1800
1801
1802 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1803 fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
1804 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
1805 fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
1806 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
1807 fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
1808 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1809 fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
1810 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1811 fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
1812 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_id :'||to_char(x_program_id ));
1813 fnd_file.put_line( 1, 'x_sysdate :'||to_char(x_sysdate ));
1814 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_sysdate :'||to_char(x_sysdate ));
1815
1816 END IF;
1817
1818 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1819 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1820 END IF;
1821
1822 END find_who_columns;
1823
1824 ----------------------------------------------------------------------------
1825 -- PROCEDURE --
1826 -- do_sql --
1827 -- --
1828 -- DESCRIPTION --
1829 -- Use this procedure to create table dynamically in JL schema and --
1830 -- register to AOL. --
1831 -- --
1832 -- PURPOSE: --
1833 -- Oracle Applications Rel 11.0 --
1834 -- --
1835 -- PARAMETERS: --
1836 -- p_string --
1837 -- --
1838 -- HISTORY: --
1839 -- 08/21/98 Sujit Dalai Created --
1840 ----------------------------------------------------------------------------
1841
1842 FUNCTION do_sql( p_string VARCHAR2) RETURN BOOLEAN IS
1843
1844
1845
1846 x_cursor INTEGER;
1847 x_row_processed INTEGER;
1848
1849 BEGIN
1850
1851 x_cursor := DBMS_SQL.OPEN_CURSOR;
1852 DBMS_SQL.PARSE( x_cursor, p_string, DBMS_SQL.V7);
1853 x_row_processed := DBMS_SQL.EXECUTE( x_cursor);
1854 DBMS_SQL.CLOSE_CURSOR( x_cursor);
1855 RETURN (TRUE);
1856
1857
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860
1861 IF DBMS_SQL.IS_OPEN(x_cursor) THEN
1862 DBMS_SQL.CLOSE_CURSOR(x_cursor);
1863 END IF;
1864 RETURN (FALSE);
1865
1866
1867 END do_sql;
1868
1869
1870 ----------------------------------------------------------------------------
1871 -- FUNCTION --
1872 -- storage_factor --
1873 -- --
1874 -- DESCRIPTION --
1875 -- Use this function to get storage clause for backup table --
1876 -- --
1877 -- PURPOSE: --
1878 -- Oracle Applications Rel 11.5 --
1879 -- --
1880 -- PARAMETERS: --
1881 -- p_table_name IN VARCHAR2 --
1882 -- p_rows_to_archive IN NUMBER --
1883 -- p_storage_factor OUT NOCOPY NUMBER --
1884 -- HISTORY: --
1885 -- 24/05/99 Sujit Dalai Created --
1886 ----------------------------------------------------------------------------
1887 FUNCTION STORAGE_FACTOR( p_table_name IN VARCHAR2,
1888 p_rows_to_archive IN NUMBER,
1889 p_storage_factor OUT NOCOPY NUMBER) RETURN BOOLEAN IS
1890
1891
1892 x_cursor INTEGER;
1893 x_row_processed INTEGER;
1894 x_statement DBMS_SQL.VARCHAR2S;
1895 x_count NUMBER := 1;
1896 x_avg_size NUMBER;
1897
1898
1899 -- Bug 15934288 Start
1900 /*
1901 CURSOR c_column IS
1902 SELECT column_name
1903 FROM sys.all_tab_columns
1904 WHERE table_name = UPPER(p_table_name);
1905 * ** This query returns logical columns for the table pointed to by the APPS
1906 * ** table synonym X_SYNONYM_NAME.
1907 * ** - This is the easiest way to get logical columns for an APPS table synonym
1908 * ** - This query works on old releases of EBS as well (dual checkin compatible)
1909 */
1910 CURSOR c_column IS
1911 select col.column_name
1912 from user_synonyms syn, all_tab_columns col
1913 where syn.synonym_name = UPPER(p_table_name)
1914 and col.owner = syn.table_owner
1915 and col.table_name = syn.table_name
1916 order by col.column_id;
1917 -- Bug 15934288 End
1918
1919 BEGIN
1920
1921 ---------------------------------------------------------
1922 -- Construct select statement to get average row size --
1923 ---------------------------------------------------------
1924
1925 x_statement(1) := 'SELECT ';
1926 FOR rec_column IN c_column LOOP
1927
1928 x_count := x_count +1;
1929 x_statement(x_count) := 'avg(nvl(vsize('||rec_column.column_name||'), 0)) +';
1930
1931 END LOOP;
1932
1933 x_statement(x_count) := RTRIM( x_statement(x_count), '+');
1934 x_statement(x_count +1) := ' FROM '||UPPER(p_table_name);
1935
1936 ---------------------------------------------------------
1937 -- Execute select statement using Dynamic SQL and get --
1938 -- storgae factor for the table --
1939 ---------------------------------------------------------
1940
1941
1942 x_cursor := DBMS_SQL.OPEN_CURSOR;
1943 DBMS_SQL.PARSE( x_cursor,
1944 x_statement,
1945 1,
1946 x_count+1,
1947 TRUE,
1948 DBMS_SQL.NATIVE);
1949 DBMS_SQL.DEFINE_COLUMN( x_cursor, 1, x_avg_size);
1950 x_row_processed := DBMS_SQL.EXECUTE_AND_FETCH(x_cursor, TRUE);
1951 DBMS_SQL.COLUMN_VALUE(x_cursor, 1, x_avg_size);
1952 DBMS_SQL.CLOSE_CURSOR( x_cursor);
1953 p_storage_factor := (p_rows_to_archive * x_avg_size)/1000;
1954
1955 RETURN TRUE;
1956
1957 EXCEPTION
1958 WHEN OTHERS THEN
1959
1960 IF DBMS_SQL.IS_OPEN(x_cursor) THEN
1961 DBMS_SQL.CLOSE_CURSOR(x_cursor);
1962 END IF;
1963 RETURN (FALSE);
1964
1965 END storage_factor;
1966
1967 ----------------------------------------------------------------------------
1968 -- PROCEDURE --
1969 -- create_output_headings --
1970 -- --
1971 -- DESCRIPTION --
1972 -- Use this procedure to create column headings in the output file --
1973 -- --
1974 -- PURPOSE: --
1975 -- Oracle Applications Rel 11.5.2 --
1976 -- --
1977 -- PARAMETERS: --
1978 -- p_fiscal_year --
1979 -- --
1980 -- HISTORY: --
1981 -- 04/21/00 Santosh Vaze Created --
1982 ----------------------------------------------------------------------------
1983
1984 PROCEDURE create_output_headings(p_fiscal_year NUMBER) IS
1985
1986 x_output_line VARCHAR2(180);
1987 CURSOR c_unproc_appraisals IS
1988 SELECT apprs.appraisal_id appraisal_id,
1989 fl.meaning appraisal_status,
1990 apprs.appraiser_name appraiser_name,
1991 apprs.appraisal_date appraisal_date
1992 FROM jl_co_fa_appraisals apprs, fnd_lookups fl
1993 WHERE apprs.fiscal_year = p_fiscal_year
1994 AND apprs.appraisal_status <> 'P'
1995 AND apprs.appraisal_status = fl.lookup_code
1996 AND fl.lookup_type = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
1997 ORDER BY apprs.appraisal_id;
1998
1999 BEGIN
2000
2001 fnd_file.put_line(fnd_file.output, fnd_message.get);
2002 fnd_message.set_name('JL', 'JL_CO_FA_APPR_NUM_OUT');
2003 fnd_file.put(fnd_file.output, SUBSTR(RPAD(fnd_message.get,20,' '),1,20) || ' ');
2004 fnd_message.set_name('JL', 'JL_CO_FA_APPRAISAL_STATUS_OUT');
2005 fnd_file.put(fnd_file.output, SUBSTR(RPAD(fnd_message.get,80,' '),1,80) || ' ');
2006 fnd_message.set_name('JL', 'JL_CO_FA_APPRAISER_NAME_OUT');
2007 fnd_file.put(fnd_file.output, SUBSTR(fnd_message.get,1,60));
2008 fnd_file.new_line(fnd_file.output,1);
2009 FOR line_ctr IN 1..4 LOOP
2010 fnd_file.put(fnd_file.output, '----------------------------------------');
2011 END LOOP;
2012 fnd_file.new_line(fnd_file.output,1);
2013 FOR rec_unproc_appraisals IN c_unproc_appraisals LOOP
2014 x_output_line := SUBSTR(RPAD(to_char(rec_unproc_appraisals.appraisal_id),20,' '),1,20)||' ';
2015 x_output_line := x_output_line || SUBSTR(RPAD(rec_unproc_appraisals.appraisal_status,80,' '),1,80)||' ';
2016 x_output_line := x_output_line || SUBSTR(rec_unproc_appraisals.appraiser_name,1,60);
2017 fnd_file.put_line(fnd_file.output, x_output_line);
2018 END LOOP;
2019 FOR line_ctr IN 1..4 LOOP
2020 fnd_file.put(fnd_file.output, '----------------------------------------');
2021 END LOOP;
2022
2023 END create_output_headings;
2024
2025 END jl_co_fa_purge_pkg;