[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_ARCHIVE_PURGE_PKG
Source
1 PACKAGE BODY IGC_CC_ARCHIVE_PURGE_PKG AS
2 /* $Header: IGCCAPRB.pls 120.14.12010000.2 2008/08/29 13:08:10 schakkin ship $ */
3
4 -- Types :
5
6 -- Constants :
7
8 g_pkg_name CONSTANT VARCHAR2(30) := 'IGC_CC_ARCHIVE_PURGE_PKG';
9
10 -- Private Global Variables :
11
12 g_debug_msg VARCHAR2(10000) := NULL;
13 g_update_login igc_cc_headers.last_update_login%TYPE;
14 g_update_by igc_cc_headers.last_updated_by%TYPE;
15 g_mrc_installed VARCHAR2(1);
16 g_mode VARCHAR2(2);
17 g_last_activity_date igc_cc_archive_history.user_req_last_activity_date%TYPE;
18 g_org_id igc_cc_headers.org_id%TYPE;
19 g_sob_id igc_cc_headers.set_of_books_id%TYPE;
20 g_cc_num igc_cc_headers.cc_num%TYPE;
21 g_maxloops CONSTANT NUMBER(2) := 20;
22 g_seconds CONSTANT NUMBER(2) := 10;
23 g_validation_error BOOLEAN := FALSE;
24
25 -- g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
26 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
27 g_ledger_name GL_LEDGERS.Name%TYPE; /*Added this param during MOAC uptake to just call MO_UTILS.GET_LEDGER_INFO.*/
28 --Variables for ATG Central logging
29 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
31 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
32 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
33 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
34 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
35 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
36 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCCAPRB.IGC_CC_ARCHIVE_PURGE_PKG.';
37
38 -- ---------------------------------------------------------------------
39 -- Private Function Definition:
40 -- ---------------------------------------------------------------------
41
42 --
43 -- Main procedure that performs the operation of the chosen mode.
44 --
45 PROCEDURE Arch_Pur_CC
46 (
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
49 p_last_activity_date IN igc_cc_archive_history.user_req_last_activity_date%TYPE,
50 p_mode IN VARCHAR2,
51 p_debug_flag IN VARCHAR2 := FND_API.G_FALSE,
52 p_commit_work IN VARCHAR2 := FND_API.G_FALSE,
53 x_Return_Status OUT NOCOPY VARCHAR2
54 );
55
56 --
57 -- Archive CC Procedure
58 --
59 PROCEDURE Archive_CC (
60 x_Return_Status OUT NOCOPY VARCHAR2
61 );
62
63 --
64 -- Archive NON MRC tables Procedure
65 --
66 PROCEDURE Archive_NON_MRC_Tbls (
67 x_return_status OUT NOCOPY VARCHAR2
68 );
69
70 --
71 -- Archive MRC Tables Procedure
72 --
73 PROCEDURE Archive_MRC_Tbls (
74 x_return_status OUT NOCOPY VARCHAR2
75 );
76
77 --
78 -- Build Candidate List Procedure
79 --
80 PROCEDURE Build_Candidate_List (
81 x_return_status OUT NOCOPY VARCHAR2
82 );
83
84 --
85 -- Check for MRC being enabled procedure
86 --
87 PROCEDURE Check_MRC (
88 x_Return_Status OUT NOCOPY VARCHAR2
89 );
90
91 --
92 -- Cleanup archive MRC tables of existing records before Archive is done
93 --
94 PROCEDURE Cleanup_MRC_Arc_Tbls (
95 x_return_status OUT NOCOPY VARCHAR2
96 );
97
98 --
99 -- Cleanup archive tables of existing records before Archive is done
100 --
101 PROCEDURE Cleanup_NON_MRC_Arc_Tbls (
102 x_return_status OUT NOCOPY VARCHAR2
103 );
104
105 --
106 -- Initializing History Record procedure
107 --
108 PROCEDURE Initialize_History_Record (
109 p_cc_header_id IN igc_cc_archive_history.cc_header_id%TYPE,
110 p_History_Rec IN OUT NOCOPY igc_cc_archive_history%ROWTYPE,
111 x_Return_Status OUT NOCOPY VARCHAR2
112 );
113
114 --
115 -- Procedure designed to insert history records for all records archived.
116 --
117 PROCEDURE Insert_Archive_History (
118 x_Return_Status OUT NOCOPY VARCHAR2
119 );
120
121 --
122 -- Procedure to lock the temp table IGC_CC_ARC_PUR_CANDIDATES. If the table
123 -- can be locked then the process can proceed.
124 --
125 FUNCTION Lock_Candidates RETURN BOOLEAN;
126
127 --
128 -- Generic Procedure for putting out debug information
129 --
130 PROCEDURE Output_Debug (
131 p_path IN VARCHAR2,
132 p_debug_msg IN VARCHAR2
133 );
134
135 --
136 -- Purge CC Procedure
137 --
138 PROCEDURE Purge_CC (
139 x_Return_Status OUT NOCOPY VARCHAR2
140 );
141
142 --
143 -- Purge NON MRC tables Procedure
144 --
145 PROCEDURE Purge_NON_MRC_Tbls (
146 x_return_status OUT NOCOPY VARCHAR2
147 );
148
149 --
150 -- Purge MRC Tables Procedure
151 --
152 PROCEDURE Purge_MRC_Tbls (
153 x_return_status OUT NOCOPY VARCHAR2
154 );
155
156 --
157 -- Procedure for Updating the CC ARCHIVE History table
158 --
159 PROCEDURE Update_History
160 (
161 p_History_Rec IN igc_cc_archive_history%ROWTYPE, -- History Record
162 x_Return_Status OUT NOCOPY VARCHAR2 -- Status of procedure
163 );
164
165 --
166 -- Validation on Inputs procedure
167 --
168 PROCEDURE Validate_Inputs (
169 x_Return_Status OUT NOCOPY VARCHAR2
170 );
171
172 --
173 -- Function to return the Set Of Books ID to the caller.
174 --
175 -- Parameters :
176 -- None
177 --
178 FUNCTION Get_SOB_ID
179 RETURN NUMBER
180 IS
181
182 BEGIN
183 RETURN (g_sob_id);
184 END Get_SOB_ID;
185
186
187 --
188 -- Function to return the ORG ID to the caller
189 --
190 -- Parameters :
191 -- None
192 --
193 FUNCTION Get_ORG_ID
194 RETURN NUMBER
195 IS
196
197 BEGIN
198 RETURN (g_org_id);
199 END Get_ORG_ID;
200
201 --
202 -- Function to return the input of the last activity date to be archived / purged
203 --
204 -- Parameters :
205 -- None
206 --
207 FUNCTION Get_Last_Activity_Date
208 RETURN DATE
209 IS
210
211 BEGIN
212 RETURN (g_last_activity_date);
213 END Get_Last_Activity_Date;
214
215 --
216 -- Request Procedure called from Concurrent Request Operation
217 --
218 -- Parameters :
219 --
220 -- errbuf ==> Error Buffer for Concurrent Request.
221 -- retcode ==> Return Code for the Concurrent Request.
222 -- p_req_mode ==> P = Purge A = Archive B = Both
223 -- p_req_last_activity_date ==> last activity date that is to be archievd/purged.
224 -- p_req_commit_work ==> Boolean indicating if this process should commit work or not
225 --
226
227 PROCEDURE Archive_Purge_CC_Request
228 (
229 errbuf OUT NOCOPY VARCHAR2,
230 retcode OUT NOCOPY NUMBER,
231 p_req_mode IN VARCHAR2,
232 p_req_last_activity_date IN VARCHAR2
233 ) IS
234 -- --------------------------------------------------------------------
235 -- Define Local Variables to be used.
236 -- --------------------------------------------------------------------
237 l_debug VARCHAR2 (1);
238 l_Return_Status VARCHAR2 (1);
239 l_init_msg VARCHAR2 (1) := FND_API.G_TRUE;
240 l_msg_count NUMBER;
241 l_msg_data VARCHAR2(12000);
242 l_error_text VARCHAR2(12000);
243 l_api_version NUMBER := 1.0;
244 l_commit_work VARCHAR2(1);
245 l_date_request DATE;
246
247 l_option_name VARCHAR2(80);
248 lv_message VARCHAR2(1000);
249 l_full_path VARCHAR2(255);
250 BEGIN
251
252 l_full_path := g_path || 'Archive_Purge_CC_Request';
253
254 -- 01/03/02, check to see if CC is installed
255 -- code will remain commented out for now
256
257 IF igi_gen.is_req_installed('CC',mo_global.get_current_org_id) = 'N' THEN
258
259 SELECT meaning
260 INTO l_option_name
261 FROM igi_lookups
262 WHERE lookup_code = 'CC'
263 AND lookup_type = 'GCC_DESCRIPTION';
264
265 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
266 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
267 lv_message := fnd_message.get;
268 IF (g_error_level >= g_debug_level ) THEN
269 FND_LOG.MESSAGE (g_error_level , l_full_path,FALSE);
270 END IF;
271 errbuf := lv_message;
272 retcode := 2;
273 return;
274 END IF;
275
276 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
277 l_commit_work := FND_API.G_TRUE;
278
279 -- IF (l_debug = 'Y') THEN
280 IF (g_debug_mode = 'Y') THEN
281 l_debug := FND_API.G_TRUE;
282 ELSE
283 l_debug := FND_API.G_FALSE;
284 END IF;
285
286 l_date_request := to_date (p_req_last_activity_date, 'YYYY/MM/DD HH24:MI:SS');
287
288 Arch_Pur_CC (p_api_version => l_api_version,
289 p_init_msg_list => l_init_msg,
290 p_mode => p_req_mode,
291 p_last_activity_date => l_date_request,
292 p_debug_flag => l_debug,
293 p_commit_work => l_commit_work,
294 x_return_status => l_Return_Status);
295
296 IF (l_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
297
298 errbuf := 'Normal Completion For Request';
299 retcode := 0;
300
301 ELSE
302
303 errbuf := 'Abnormal Completion For Request. Check file for Errors.';
304 retcode := 2;
305
306 END IF;
307
308 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
309 p_data => l_msg_data );
310
311 IF (l_msg_count > 0) THEN
312
313 l_error_text := '';
314 FOR l_cur IN 1..l_msg_count LOOP
315 -- l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
316 l_error_text := l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
317 IF (g_excep_level >= g_debug_level ) THEN
318 FND_LOG.STRING (g_excep_level,l_full_path,l_error_text);
319 END IF;
320 fnd_file.put_line (FND_FILE.LOG,
321 l_error_text);
322 END LOOP;
323
324 ELSE
325
326 IF (g_validation_error) THEN
327 l_error_text := 'Error Returned but Error stack has no data';
328 IF (g_excep_level >= g_debug_level ) THEN
329 FND_LOG.STRING (g_excep_level,l_full_path,l_error_text);
330 END IF;
331 fnd_file.put_line (FND_FILE.LOG,
332 l_error_text);
333 END IF;
334
335 END IF;
336
337
338 RETURN;
339
340 -- --------------------------------------------------------------------
341 -- Exception handler section for the Arch_Pur_CBC procedure.
342 -- --------------------------------------------------------------------
343 EXCEPTION
344
345 WHEN OTHERS THEN
346 errbuf := 'Abnormal Completion For Request';
347 retcode := 2;
348 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
349 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Archive_Purge_CC_Request');
350 END IF;
351
352 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
353 p_data => l_msg_data );
354
355 IF (l_msg_count > 0) THEN
356
357 l_error_text := '';
358 FOR l_cur IN 1..l_msg_count LOOP
359 -- l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
360 l_error_text := l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
361 IF (g_excep_level >= g_debug_level ) THEN
362 FND_LOG.STRING (g_excep_level,l_full_path,l_error_text);
363 END IF;
364 fnd_file.put_line (FND_FILE.LOG,
365 l_error_text);
366 END LOOP;
367 ELSE
368 l_error_text := 'Error Returned but Error stack has no data';
369 fnd_file.put_line (FND_FILE.LOG,
370 l_error_text);
371 END IF;
372 RETURN;
373
374 END Archive_Purge_CC_Request;
375
376 --
377 -- Main Procedure for Archiving and Purging the CC tables
378 --
379 -- Parameters :
380 --
381 -- p_api_version ==> Version of procedure to be executed if available.
382 -- p_init_msg_list ==> Variable to determine if the message stack is to be initialized.
383 -- p_last_activity_date ==> last date record activity took place
384 -- p_mode ==> Archive ("AR"), Pre-Purge ("PP"), Purge ("PU")
385 -- p_debug_flag ==> Is debug enabled
386 -- p_commit_work ==> Boolean indicating if this process should commit work or not
387 -- x_Return_Status ==> Status of procedure returned to caller
388 --
389 PROCEDURE Arch_Pur_CC
390 (
391 p_api_version IN NUMBER,
392 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
393 p_last_activity_date IN igc_cc_archive_history.user_req_last_activity_date%TYPE,
394 p_mode IN VARCHAR2,
395 p_debug_flag IN VARCHAR2 := FND_API.G_FALSE,
396 p_commit_work IN VARCHAR2 := FND_API.G_FALSE,
397 x_Return_Status OUT NOCOPY VARCHAR2
398 ) IS
399
400 -- --------------------------------------------------------------------
401 -- Define local variables to be used
402 -- --------------------------------------------------------------------
403 l_Return_Status VARCHAR2(1);
404 l_api_name CONSTANT VARCHAR2(30) := 'Arch_Pur_CC';
405 l_stage1_parent_req NUMBER;
406 l_stage1_wait_for_request BOOLEAN;
407 l_stage1_phase VARCHAR2(240);
408 l_stage1_status VARCHAR2(240);
409 l_stage1_dev_phase VARCHAR2(240);
410 l_stage1_dev_status VARCHAR2(240);
411 l_stage1_message VARCHAR2(240);
412 l_api_version NUMBER := 1.0;
413
414 --Variable for holding error message
415 l_error_text VARCHAR2(500);
416 l_full_path VARCHAR2(255);
417 -- Varibles used for xml report
418 l_terr VARCHAR2(10):='US';
419 l_lang VARCHAR2(10):='en';
420 l_layout BOOLEAN;
421
422 -- --------------------------------------------------------------------
423 -- Define cursors to be used in main archive/purge procedure
424 -- --------------------------------------------------------------------
425
426 BEGIN
427
428 l_full_path := g_path || 'Arch_Pur_CC';
429
430 SAVEPOINT Archive_Purge_CC_PVT;
431
432 -- --------------------------------------------------------------------
433 -- Make sure that the appropriate version is being used and initialize
434 -- the message stack if required.
435 -- --------------------------------------------------------------------
436 IF (NOT FND_API.Compatible_API_Call ( l_api_version,
437 p_api_version,
438 l_api_name,
439 G_PKG_NAME )) THEN
440 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
441 END IF;
442
443 IF (FND_API.to_Boolean ( p_init_msg_list )) THEN
444 FND_MSG_PUB.initialize ;
445 END IF;
446
447 -- --------------------------------------------------------------------
448 -- Attempt to lock the table and if able to lock continue with process
449 -- otherwise exit after too long a wait.
450 -- --------------------------------------------------------------------
451 IF (NOT Lock_Candidates) THEN
452 raise FND_API.G_EXC_ERROR;
453 END IF;
454
455 -- --------------------------------------------------------------------
456 -- Initialize Return status and other local variables.
457 -- --------------------------------------------------------------------
458 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
459 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(p_debug_flag);
460 g_update_login := FND_GLOBAL.LOGIN_ID;
461 g_update_by := FND_GLOBAL.USER_ID;
462 g_mode := p_mode;
463 g_last_activity_date := p_last_activity_date;
464 /* Commented below two lines during r12 MOAC uptake
465 g_org_id := TO_NUMBER (FND_PROFILE.value ('ORG_ID'));
466 g_sob_id := TO_NUMBER (FND_PROFILE.value ('GL_SET_OF_BKS_ID')); */
467 g_org_id := mo_global.get_current_org_id;
468 mo_utils.get_ledger_info(p_operating_unit => g_org_id,
469 p_ledger_id => g_sob_id,
470 p_ledger_name => g_ledger_name);
471 /* Select set_of_books_id into g_sob_id
472 FROM hr_operating_units WHERE organization_id = p_org_id;*/
473
474 IF p_debug_flag = FND_API.G_TRUE THEN
475 g_debug_mode := 'Y';
476 ELSE
477 g_debug_mode := 'N';
478 END IF;
479 -- --------------------------------------------------------------------
480 -- Initialize debug information if the user has requested debug to
481 -- be turned on.
482 -- --------------------------------------------------------------------
483 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
484 IF (g_debug_mode = 'Y') THEN
485 g_debug_msg := ' ************ Starting ARCHIVE / PURGE CC '||
486 to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************';
487 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
488 g_debug_msg := ' Parameters Activity Date:' || g_last_activity_date ||
489 ' Org ID : ' || g_org_id ||
490 ' SOB ID : ' || g_sob_id ||
491 ' Mode : ' || g_mode;
492 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
493 END IF;
494
495 -- --------------------------------------------------------------------
496 -- Make sure that all the inputs that are given are valid and make
497 -- sure that the values given and obtained are ok to continue with the
498 -- archive / purge process.
499 -- --------------------------------------------------------------------
500 Validate_Inputs (x_return_status => l_return_status);
501
502 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
503 raise FND_API.G_EXC_ERROR;
504 END IF;
505
506 -- --------------------------------------------------------------------
507 -- Determine if MRC is available and set global variables accordingly.
508 -- --------------------------------------------------------------------
509 Check_MRC (x_return_status => l_return_status);
510
511 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
512 raise FND_API.G_EXC_ERROR;
513 END IF;
514
515 -- --------------------------------------------------------------------
516 -- Build the table for candidate CC Header IDS that can be archived
517 -- and or purged.
518 -- --------------------------------------------------------------------
519 Build_Candidate_List (x_return_status => l_return_status);
520
521 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
522 raise FND_API.G_EXC_ERROR;
523 END IF;
524
525 -- --------------------------------------------------------------------
526 -- Archive Process is to be run if user has given the mode to be 'A' or
527 -- 'B'. This process will archive the LINES and BATCHES for CC. The
528 -- records to be archived will be for NON MRC and MRC records.
529 -- --------------------------------------------------------------------
530 IF (NOT g_validation_error) THEN
531
532 IF (g_mode = 'AR') THEN
533
534 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
535 IF (g_debug_mode = 'Y') THEN
536 g_debug_msg := ' Calling Procedure to Archive for Last Activity Date : ' ||
537 g_last_activity_date;
538 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
539 END IF;
540
541 Archive_CC (x_Return_Status => l_Return_Status);
542
543 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
544 raise FND_API.G_EXC_ERROR;
545 END IF;
546
547 -- --------------------------------------------------------------------
548 -- Purge process is to be run if user has given the mode to be 'P' or
549 -- 'B'. This process will purge the LINES and BATCHES for CC. The
550 -- records to be purged will be for NON MRC and MRC records.
551 -- --------------------------------------------------------------------
552 ELSIF (g_mode = 'PU') THEN
553
554 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
555 IF (g_debug_mode = 'Y') THEN
556 g_debug_msg := ' Calling Procedure to Purge for Last Activity Date : ' ||
557 g_last_activity_date;
558 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
559 END IF;
560
561 Purge_CC (x_Return_Status => l_Return_Status);
562
563 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
564 raise FND_API.G_EXC_ERROR;
565 END IF;
566
567 END IF;
568
569 ELSE
570
571 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
572 IF (g_debug_mode = 'Y') THEN
573 g_debug_msg := ' Validation error happened for Last Activity Date : ' ||
574 g_last_activity_date;
575 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
576 END IF;
577
578 END IF;
579
580 -- --------------------------------------------------------------------
581 -- Run the appropriate report to give the user insight into what has
582 -- or will be done for the archive/purge process.
583 -- --------------------------------------------------------------------
584 l_stage1_parent_req := FND_REQUEST.SUBMIT_REQUEST
585 ('IGC',
586 'IGCCAPRR',
587 NULL,
588 NULL,
589 FALSE,
590 to_char(g_last_activity_date),
591 g_mode,
592 to_char(g_sob_id),
593 to_char(g_org_id)
594 );
595
596 IF (l_stage1_parent_req > 0) THEN
597 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
598 'IGC_CC_ARCHIVE_PURGE_PKG - IGCCAPRR request Submitted ');*/
599 IF(g_event_level >= g_debug_level) THEN
600 FND_LOG.STRING(g_event_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - IGCCAPRR request Submitted ');
601 END IF;
602 COMMIT;
603 ELSE
604
605 FND_FILE.PUT_LINE (FND_FILE.LOG,
606 'IGC_CC_ARCHIVE_PURGE_PKG - IGCCAPRR error submitting request');
607 /*raise_application_error (-20000,
608 'IGC_CC_ARCHIVE_PURGE_PKG - error submitting IGCCAPRR '||
609 SQLERRM || '-' || SQLCODE);*/
610
611 IF ( g_unexp_level >= g_debug_level ) THEN
612
613 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
614 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
615 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
616 FND_LOG.MESSAGE ( g_unexp_level,l_full_path,TRUE);
617 END IF;
618
619 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_USER_ERROR');
620 l_error_text := FND_MESSAGE.GET;
621 raise_application_error (-20000, l_error_text);
622
623 END IF;
624
625 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
626 'IGC_CC_ARCHIVE_PURGE_PKG - waiting for IGCCAPRR request to finish... ');*/
627 IF(g_state_level >= g_debug_level) THEN
628 FND_LOG.STRING(g_state_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - waiting for IGCCAPRR request to finish... ');
629 END IF;
630
631 l_stage1_wait_for_request := FND_CONCURRENT.WAIT_FOR_REQUEST (l_stage1_parent_req,
632 05,
633 0,
634 l_stage1_phase,
635 l_stage1_status,
636 l_stage1_dev_phase,
637 l_stage1_dev_status,
638 l_stage1_message);
639 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
640 'IGC_CC_ARCHIVE_PURGE_PKG - finished... checking status of IGCCAPRR request');*/
641
642 IF(g_event_level >= g_debug_level) THEN
643 FND_LOG.STRING(g_event_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - finished... checking status of IGCCAPRR request');
644 END IF;
645
646 IF ((l_stage1_dev_phase = 'COMPLETE') AND
647 (l_stage1_dev_status = 'NORMAL')) THEN
648 FND_FILE.PUT_LINE (FND_FILE.LOG,
649 'IGC_CC_ARCHIVE_PURGE_PKG - COMPLETE / NORMAL status of IGCCAPRR request');
650
651 IF(g_event_level >= g_debug_level) THEN
652 FND_LOG.STRING(g_event_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - COMPLETE / NORMAL status of IGCCAPRR request');
653 END IF;
654 ELSE
655 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
656 'IGC_CC_ARCHIVE_PURGE_PKG - FAILED status of IGCCAPRR request');*/
657 raise FND_API.G_EXC_ERROR;
658 END IF;
659
660 -- --------------------------------------------------------------------
661 -- Run the xml report to give the user insight into what has
662 -- or will be done for the archive/purge process in the xml format.
663 -- --------------------------------------------------------------------
664
665 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
666 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
667 l_lang,
668 l_terr,
669 'IGCCAPRR_XML',
670 'IGC',
671 'IGCCAPRR_XML' );
672
673 l_layout := FND_REQUEST.ADD_LAYOUT(
674 'IGC',
675 'IGCCAPRR_XML',
676 l_lang,
677 l_terr,
678 'RTF');
679
680 IF l_layout then
681 l_stage1_parent_req := FND_REQUEST.SUBMIT_REQUEST
682 ('IGC',
683 'IGCCAPRR_XML',
684 NULL,
685 NULL,
686 FALSE,
687 to_char(g_last_activity_date),
688 g_mode,
689 to_char(g_sob_id),
690 to_char(g_org_id)
691 );
692 END if;
693 IF (l_stage1_parent_req > 0) THEN
694 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
695 'IGC_CC_ARCHIVE_PURGE_PKG - IGCCAPRR request Submitted ');*/
696 IF(g_event_level >= g_debug_level) THEN
697 FND_LOG.STRING(g_event_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - IGCCAPRR_XML request Submitted ');
698 END IF;
699 COMMIT;
700 ELSE
701
702 FND_FILE.PUT_LINE (FND_FILE.LOG,
703 'IGC_CC_ARCHIVE_PURGE_PKG - IGCCAPRR_XML error submitting request');
704 /*raise_application_error (-20000,
705 'IGC_CC_ARCHIVE_PURGE_PKG - error submitting IGCCAPRR_XML '||
706 SQLERRM || '-' || SQLCODE);*/
707
708 IF ( g_unexp_level >= g_debug_level ) THEN
709
710 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
711 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
712 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
713 FND_LOG.MESSAGE ( g_unexp_level,l_full_path,TRUE);
714 END IF;
715
716 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_USER_ERROR');
717 l_error_text := FND_MESSAGE.GET;
718 raise_application_error (-20000, l_error_text);
719 END IF;
720 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
721 'IGC_CC_ARCHIVE_PURGE_PKG - waiting for IGCCAPRR_XML request to finish... ');*/
722 IF(g_state_level >= g_debug_level) THEN
723 FND_LOG.STRING(g_state_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - waiting for IGCCAPRR_XML request to finish... ');
724 END IF;
725
726 l_stage1_wait_for_request := FND_CONCURRENT.WAIT_FOR_REQUEST (l_stage1_parent_req,
727 05,
728 0,
729 l_stage1_phase,
730 l_stage1_status,
731 l_stage1_dev_phase,
732 l_stage1_dev_status,
733 l_stage1_message);
734 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
735 'IGC_CC_ARCHIVE_PURGE_PKG - finished... checking status of IGCCAPRR_XML request');*/
736
737 IF(g_event_level >= g_debug_level) THEN
738 FND_LOG.STRING(g_event_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - finished... checking status of IGCCAPRR_XML request');
739 END IF;
740
741 IF ((l_stage1_dev_phase = 'COMPLETE') AND
742 (l_stage1_dev_status = 'NORMAL')) THEN
743 FND_FILE.PUT_LINE (FND_FILE.LOG,'IGC_CC_ARCHIVE_PURGE_PKG - COMPLETE / NORMAL status of IGCCAPRR_XML request');
744
745 IF(g_event_level >= g_debug_level) THEN
746 FND_LOG.STRING(g_event_level, l_full_path, 'IGC_CC_ARCHIVE_PURGE_PKG - COMPLETE / NORMAL status of IGCCAPRR_XML request');
747 END IF;
748 ELSE
749 /*FND_FILE.PUT_LINE (FND_FILE.LOG,
750 'IGC_CC_ARCHIVE_PURGE_PKG - FAILED status of IGCCAPRR_XML request');*/
751 raise FND_API.G_EXC_ERROR;
752 END IF;
753 END IF;
754
755 -- -----------------
756 -- End of XML Report
757 -- -----------------
758
759
760 -- --------------------------------------------------------------------
761 -- Cleanup the candidate list table before exiting.
762 -- --------------------------------------------------------------------
763 DELETE
764 FROM igc_arc_pur_candidates;
765
766 -- --------------------------------------------------------------------
767 -- Commit the cleanup that has been performed.
768 -- --------------------------------------------------------------------
769 COMMIT;
770
771 RETURN;
772
773 -- --------------------------------------------------------------------
774 -- Exception handler section for the Arch_Pur_CC procedure.
775 -- --------------------------------------------------------------------
776 EXCEPTION
777
778 WHEN NO_DATA_FOUND THEN
779 x_Return_Status := FND_API.G_RET_STS_ERROR;
780 ROLLBACK TO Archive_Purge_CC_PVT;
781 IF (g_excep_level >= g_debug_level ) THEN
782 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
783 END IF;
784 RETURN;
785
786 WHEN FND_API.G_EXC_ERROR THEN
787 x_Return_Status := FND_API.G_RET_STS_ERROR;
788 ROLLBACK TO Archive_Purge_CC_PVT;
789 IF (g_excep_level >= g_debug_level ) THEN
790 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
791 END IF;
792 RETURN;
793
794 WHEN OTHERS THEN
795 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
796 ROLLBACK TO Archive_Purge_CC_PVT;
797 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
798 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
799 END IF;
800 IF ( g_unexp_level >= g_debug_level ) THEN
801 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
802 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
803 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
804 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
805 END IF;
806 RETURN;
807
808 END Arch_Pur_CC;
809
810 --
811 -- Archive CC Procedure
812 --
813 -- Parameters :
814 --
815 -- x_Return_Status ==> Status of procedure returned to caller
816 --
817 PROCEDURE Archive_CC (
818 x_Return_Status OUT NOCOPY VARCHAR2
819 ) IS
820
821 -- --------------------------------------------------------------------
822 -- Declare local variables to be used in this procedure.
823 -- --------------------------------------------------------------------
824 l_api_name CONSTANT VARCHAR2(30) := 'Archive_CC';
825 l_Return_Status VARCHAR2(1);
826 l_full_path VARCHAR2(255);
827
828 BEGIN
829
830 l_full_path := g_path || 'Archive_CC';
831
832 -- --------------------------------------------------------------------
833 -- Initialize Return status and other local variables.
834 -- --------------------------------------------------------------------
835 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
836
837 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
838 IF (g_debug_mode = 'Y') THEN
839 g_debug_msg := ' Beginning Archive process for Last Activity Date : ' ||
840 g_last_activity_date;
841 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
842 END IF;
843
844 -- --------------------------------------------------------------------
845 -- Cleanup MRC Archive tables of records that could be present.
846 -- --------------------------------------------------------------------
847 IF (g_mrc_installed = 'Y') THEN
848
849 Cleanup_MRC_Arc_Tbls (x_return_status => l_return_status);
850
851 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
852 raise FND_API.G_EXC_ERROR;
853 END IF;
854
855 ELSE
856
857 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
858 IF (g_debug_mode = 'Y') THEN
859 g_debug_msg := ' NOT deleting records from Archive MRC tables for' ||
860 ' Last Activity Date : ' || g_last_activity_date;
861 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
862 g_debug_msg := ' MRC is not enabled or installed for SOB ID : ' ||
863 to_char(g_sob_id);
864 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
865 END IF;
866
867 END IF;
868
869 -- -------------------------------------------------------------------
870 -- Delete any records in the archive tables that may be present for
871 -- the Header, set_of_books, and cc_number.
872 -- -------------------------------------------------------------------
873 Cleanup_NON_MRC_Arc_Tbls (x_return_status => l_return_status);
874
875 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
876 raise FND_API.G_EXC_ERROR;
877 END IF;
878
879 -- ------------------------------------------------------------------
880 -- Now that the tables have been cleaned up of any pre-existing data
881 -- begin the archiving / copying of data into the archive tables.
882 -- ------------------------------------------------------------------
883 IF (g_mrc_installed = 'Y') THEN
884
885 Archive_MRC_Tbls (x_return_status => l_return_status);
886
887 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
888 raise FND_API.G_EXC_ERROR;
889 END IF;
890
891 ELSE
892
893 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
894 IF (g_debug_mode = 'Y') THEN
895 g_debug_msg := ' NOT Archiving records from MRC tables for' ||
896 ' Last Activity Date : ' || g_last_activity_date;
897 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
898 g_debug_msg := ' MRC is not enabled or installed for SOB ID : ' ||
899 to_char(g_sob_id);
900 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
901 END IF;
902
903 END IF;
904
905 -- -------------------------------------------------------------------
906 -- Archive any records from the original NON MRC tables that have not
907 -- been updated since the date entered for the ORG ID given.
908 -- -------------------------------------------------------------------
909 Archive_NON_MRC_Tbls (x_return_status => l_return_status);
910
911 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
912 raise FND_API.G_EXC_ERROR;
913 END IF;
914
915 -- --------------------------------------------------------------------
916 -- For all the Headers that were just archived insert the corresponding
917 -- records into the Archive History Table.
918 -- --------------------------------------------------------------------
919 Insert_Archive_History (x_return_status => l_return_status);
920
921 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
922 raise FND_API.G_EXC_ERROR;
923 END IF;
924
925 RETURN;
926
927 -- --------------------------------------------------------------------
928 -- Exception handler section for the Archive_CC procedure.
929 -- --------------------------------------------------------------------
930 EXCEPTION
931
932 WHEN NO_DATA_FOUND THEN
933 x_Return_Status := FND_API.G_RET_STS_ERROR;
934 IF (g_excep_level >= g_debug_level ) THEN
935 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
936 END IF;
937 RETURN;
938
939 WHEN FND_API.G_EXC_ERROR THEN
940 x_Return_Status := FND_API.G_RET_STS_ERROR;
941 IF (g_excep_level >= g_debug_level ) THEN
942 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
943 END IF;
944 RETURN;
945
946 WHEN OTHERS THEN
947 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
948 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
949 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
950 END IF;
951 IF ( g_unexp_level >= g_debug_level ) THEN
952 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
953 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
954 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
955 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
956 END IF;
957 RETURN;
958
959 END Archive_CC;
960
961 --
962 -- Archive_MRC_Tbls Procedure
963 --
964 -- Parameters :
965 --
966 -- x_Return_Status ==> Status of procedure returned to caller
967 --
968 PROCEDURE Archive_MRC_Tbls (
969 x_return_status OUT NOCOPY VARCHAR2
970 ) IS
971
972 -- --------------------------------------------------------------------
973 -- Declare local variables to be used in this procedure.
974 -- --------------------------------------------------------------------
975 l_api_name CONSTANT VARCHAR2(30) := 'Archive_MRC_Tbls';
976 l_full_path VARCHAR2(255);
977
978 BEGIN
979
980 l_full_path := g_path || 'Archive_MRC_Tbls';
981
982 -- --------------------------------------------------------------------
983 -- Initialize Return status and other local variables.
984 -- --------------------------------------------------------------------
985 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
986
987 -- --------------------------------------------------------------------
988 -- Insert all records that are able to be inserted based upon the
989 -- last activity date and org id from the original MRC tables into the
990 -- archive MRC tables.
991 -- --------------------------------------------------------------------
992 INSERT
993 INTO igc_cc_arc_mc_headers
994 (CC_HEADER_ID,
995 SET_OF_BOOKS_ID,
996 CONVERSION_TYPE,
997 CONVERSION_DATE,
998 CONVERSION_RATE)
999 SELECT
1000 CC_HEADER_ID,
1001 SET_OF_BOOKS_ID,
1002 CONVERSION_TYPE,
1003 CONVERSION_DATE,
1004 CONVERSION_RATE
1005 FROM igc_cc_mc_headers CMH
1006 WHERE CMH.cc_header_id IN
1007 ( SELECT ICV.cc_header_id
1008 FROM igc_arc_pur_candidates ICV
1009 );
1010
1011 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1012 IF (g_debug_mode = 'Y') THEN
1013 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Headers Table for ' ||
1014 ' Last Activity Date : ' || g_last_activity_date;
1015 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1016 END IF;
1017
1018 -- --------------------------------------------------------------------
1019 -- Make sure that only the period that is closed and matches the period
1020 -- name are inserted into the IGC_CC_ARCHIVE_MC_JE_LINES table.
1021 -- --------------------------------------------------------------------
1022 INSERT
1023 INTO igc_cc_arc_mc_header_hist
1024 (CC_HEADER_ID,
1025 SET_OF_BOOKS_ID,
1026 CC_VERSION_NUM,
1027 CC_VERSION_ACTION,
1028 CONVERSION_TYPE,
1029 CONVERSION_DATE,
1030 CONVERSION_RATE)
1031 SELECT
1032 CC_HEADER_ID,
1033 SET_OF_BOOKS_ID,
1034 CC_VERSION_NUM,
1035 CC_VERSION_ACTION,
1036 CONVERSION_TYPE,
1037 CONVERSION_DATE,
1038 CONVERSION_RATE
1039 FROM igc_cc_mc_header_history CMHH
1040 WHERE CMHH.cc_header_id IN
1041 ( SELECT ICV.cc_header_id
1042 FROM igc_arc_pur_candidates ICV
1043 );
1044
1045 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1046 IF (g_debug_mode = 'Y') THEN
1047 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Headers Hist Table for ' ||
1048 ' Last Activity Date : ' || g_last_activity_date;
1049 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1050 END IF;
1051
1052 -- --------------------------------------------------------------------
1053 -- Insert all records that are able to be inserted based upon the
1054 -- last activity date and org id from the original MRC tables into the
1055 -- archive MRC tables.
1056 -- --------------------------------------------------------------------
1057 INSERT
1058 INTO igc_cc_arc_mc_acct_lines
1059 (CC_ACCT_LINE_ID,
1060 SET_OF_BOOKS_ID,
1061 CC_ACCT_FUNC_AMT ,
1062 CC_ACCT_ENCMBRNC_AMT,
1063 CONVERSION_TYPE,
1064 CONVERSION_DATE,
1065 CONVERSION_RATE,
1066 CC_FUNC_WITHHELD_AMT)
1067 SELECT
1068 CC_ACCT_LINE_ID,
1069 SET_OF_BOOKS_ID,
1070 CC_ACCT_FUNC_AMT ,
1071 CC_ACCT_ENCMBRNC_AMT,
1072 CONVERSION_TYPE,
1073 CONVERSION_DATE,
1074 CONVERSION_RATE,
1075 CC_FUNC_WITHHELD_AMT
1076 FROM igc_cc_mc_acct_lines CML
1077 WHERE CML.cc_acct_line_id IN
1078 ( SELECT ACLH.cc_acct_line_id
1079 FROM igc_cc_acct_line_history ACLH
1080 WHERE ACLH.cc_header_id IN
1081 ( SELECT ICV1.cc_header_id
1082 FROM igc_arc_pur_candidates ICV1
1083 )
1084 )
1085 OR CML.cc_acct_line_id IN
1086 ( SELECT ACL.cc_acct_line_id
1087 FROM igc_cc_acct_lines ACL
1088 WHERE ACL.cc_header_id IN
1089 ( SELECT ICV2.cc_header_id
1090 FROM igc_arc_pur_candidates ICV2
1091 )
1092 );
1093
1094 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1095 IF (g_debug_mode = 'Y') THEN
1096 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Acct Line Table for ' ||
1097 ' Last Activity Date : ' || g_last_activity_date;
1098 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1099 END IF;
1100
1101 -- --------------------------------------------------------------------
1102 -- Make sure that only the period that is closed and matches the period
1103 -- name are inserted into the IGC_CC_ARCHIVE_MC_JE_LINES table.
1104 -- --------------------------------------------------------------------
1105 INSERT
1106 INTO igc_cc_arc_mc_acct_line_hist
1107 ( CC_ACCT_LINE_ID,
1108 SET_OF_BOOKS_ID,
1109 CC_ACCT_FUNC_AMT,
1110 CC_ACCT_ENCMBRNC_AMT,
1111 CC_ACCT_VERSION_NUM,
1112 CC_ACCT_VERSION_ACTION,
1113 CONVERSION_TYPE,
1114 CONVERSION_DATE,
1115 CONVERSION_RATE,
1116 CC_FUNC_WITHHELD_AMT)
1117 SELECT
1118 CC_ACCT_LINE_ID,
1119 SET_OF_BOOKS_ID,
1120 CC_ACCT_FUNC_AMT,
1121 CC_ACCT_ENCMBRNC_AMT,
1122 CC_ACCT_VERSION_NUM,
1123 CC_ACCT_VERSION_ACTION,
1124 CONVERSION_TYPE,
1125 CONVERSION_DATE,
1126 CONVERSION_RATE,
1127 CC_FUNC_WITHHELD_AMT
1128 FROM igc_cc_mc_acct_line_history CMLH
1129 WHERE CMLH.cc_acct_line_id IN
1130 ( SELECT ACLH.cc_acct_line_id
1131 FROM igc_cc_acct_line_history ACLH
1132 WHERE ACLH.cc_header_id IN
1133 ( SELECT ICV1.cc_header_id
1134 FROM igc_arc_pur_candidates ICV1
1135 )
1136 )
1137 OR CMLH.cc_acct_line_id IN
1138 ( SELECT ACL.cc_acct_line_id
1139 FROM igc_cc_acct_lines ACL
1140 WHERE ACL.cc_header_id IN
1141 ( SELECT ICV2.cc_header_id
1142 FROM igc_arc_pur_candidates ICV2
1143 )
1144 );
1145
1146 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1147 IF (g_debug_mode = 'Y') THEN
1148 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Acct Line Hist Table for ' ||
1149 ' Last Activity Date : ' || g_last_activity_date;
1150 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1151 END IF;
1152
1153 -- --------------------------------------------------------------------
1154 -- Insert all records that are able to be inserted based upon the
1155 -- last activity date and org id from the original MRC tables into the
1156 -- archive MRC tables.
1157 -- --------------------------------------------------------------------
1158 INSERT
1159 INTO igc_cc_arc_mc_det_pf
1160 (CC_DET_PF_LINE_ID,
1161 SET_OF_BOOKS_ID,
1162 CC_DET_PF_FUNC_AMT ,
1163 CC_DET_PF_ENCMBRNC_AMT,
1164 CONVERSION_TYPE,
1165 CONVERSION_DATE,
1166 CONVERSION_RATE )
1167 SELECT
1168 CC_DET_PF_LINE_ID,
1169 SET_OF_BOOKS_ID,
1170 CC_DET_PF_FUNC_AMT ,
1171 CC_DET_PF_ENCMBRNC_AMT,
1172 CONVERSION_TYPE,
1173 CONVERSION_DATE,
1174 CONVERSION_RATE
1175 FROM igc_cc_mc_det_pf CMDP
1176 WHERE CMDP.cc_det_pf_line_id IN
1177 ( SELECT DPFH.cc_det_pf_line_id
1178 FROM igc_cc_det_pf_history DPFH
1179 WHERE DPFH.cc_acct_line_id IN
1180 ( SELECT ACLH.cc_acct_line_id
1181 FROM igc_cc_acct_line_history ACLH
1182 WHERE ACLH.cc_header_id IN
1183 ( SELECT ICV1.cc_header_id
1184 FROM igc_arc_pur_candidates ICV1
1185 )
1186 )
1187 OR DPFH.cc_acct_line_id IN
1188 ( SELECT ACL.cc_acct_line_id
1189 FROM igc_cc_acct_lines ACL
1190 WHERE ACL.cc_header_id IN
1191 ( SELECT ICV2.cc_header_id
1192 FROM igc_arc_pur_candidates ICV2
1193 )
1194 )
1195 )
1196 OR CMDP.cc_det_pf_line_id IN
1197 ( SELECT DPF.cc_det_pf_line_id
1198 FROM igc_cc_det_pf DPF
1199 WHERE DPF.cc_acct_line_id IN
1200 ( SELECT ACLH1.cc_acct_line_id
1201 FROM igc_cc_acct_line_history ACLH1
1202 WHERE ACLH1.cc_header_id IN
1203 ( SELECT ICV3.cc_header_id
1204 FROM igc_arc_pur_candidates ICV3
1205 )
1206 )
1207 OR DPF.cc_acct_line_id IN
1208 ( SELECT ACL1.cc_acct_line_id
1209 FROM igc_cc_acct_lines ACL1
1210 WHERE ACL1.cc_header_id IN
1211 ( SELECT ICV4.cc_header_id
1212 FROM igc_arc_pur_candidates ICV4
1213 )
1214 )
1215 );
1216
1217 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1218 IF (g_debug_mode = 'Y') THEN
1219 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Det PF Table for ' ||
1220 ' Last Activity Date : ' || g_last_activity_date;
1221 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1222 END IF;
1223
1224 -- --------------------------------------------------------------------
1225 -- Make sure that only the period that is closed and matches the period
1226 -- name are inserted into the IGC_CC_ARCHIVE_MC_JE_LINES table.
1227 -- --------------------------------------------------------------------
1228 INSERT
1229 INTO igc_cc_arc_mc_det_pf_hist
1230 ( CC_DET_PF_LINE_ID,
1231 SET_OF_BOOKS_ID,
1232 CC_DET_PF_FUNC_AMT,
1233 CC_DET_PF_ENCMBRNC_AMT,
1234 CC_DET_PF_VERSION_NUM ,
1235 CC_DET_PF_VERSION_ACTION,
1236 CONVERSION_TYPE,
1237 CONVERSION_DATE ,
1238 CONVERSION_RATE)
1239 SELECT
1240 CC_DET_PF_LINE_ID,
1241 SET_OF_BOOKS_ID,
1242 CC_DET_PF_FUNC_AMT,
1243 CC_DET_PF_ENCMBRNC_AMT,
1244 CC_DET_PF_VERSION_NUM ,
1245 CC_DET_PF_VERSION_ACTION,
1246 CONVERSION_TYPE,
1247 CONVERSION_DATE ,
1248 CONVERSION_RATE
1249 FROM igc_cc_mc_det_pf_history CMDPH
1250 WHERE CMDPH.cc_det_pf_line_id IN
1251 ( SELECT DPFH.cc_det_pf_line_id
1252 FROM igc_cc_det_pf_history DPFH
1253 WHERE DPFH.cc_acct_line_id IN
1254 ( SELECT ACLH.cc_acct_line_id
1255 FROM igc_cc_acct_line_history ACLH
1256 WHERE ACLH.cc_header_id IN
1257 ( SELECT ICV1.cc_header_id
1258 FROM igc_arc_pur_candidates ICV1
1259 )
1260 )
1261 OR DPFH.cc_acct_line_id IN
1262 ( SELECT ACL.cc_acct_line_id
1263 FROM igc_cc_acct_lines ACL
1264 WHERE ACL.cc_header_id IN
1265 ( SELECT ICV2.cc_header_id
1266 FROM igc_arc_pur_candidates ICV2
1267 )
1268 )
1269 )
1270 OR CMDPH.cc_det_pf_line_id IN
1271 ( SELECT DPF.cc_det_pf_line_id
1272 FROM igc_cc_det_pf DPF
1273 WHERE DPF.cc_acct_line_id IN
1274 ( SELECT ACLH1.cc_acct_line_id
1275 FROM igc_cc_acct_line_history ACLH1
1276 WHERE ACLH1.cc_header_id IN
1277 ( SELECT ICV3.cc_header_id
1278 FROM igc_arc_pur_candidates ICV3
1279 )
1280 )
1281 OR DPF.cc_acct_line_id IN
1282 ( SELECT ACL1.cc_acct_line_id
1283 FROM igc_cc_acct_lines ACL1
1284 WHERE ACL1.cc_header_id IN
1285 ( SELECT ICV4.cc_header_id
1286 FROM igc_arc_pur_candidates ICV4
1287 )
1288 )
1289 );
1290
1291 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1292 IF (g_debug_mode = 'Y') THEN
1293 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into MC Det PF Hist Table for ' ||
1294 ' Last Activity Date : ' || g_last_activity_date;
1295 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1296 END IF;
1297
1298 RETURN;
1299
1300 -- --------------------------------------------------------------------
1301 -- Exception handler section for the Archive_MRC_Tbls procedure.
1302 -- --------------------------------------------------------------------
1303 EXCEPTION
1304
1305 WHEN NO_DATA_FOUND THEN
1306 x_Return_Status := FND_API.G_RET_STS_ERROR;
1307 IF (g_excep_level >= g_debug_level ) THEN
1308 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
1309 END IF;
1310 RETURN;
1311
1312 WHEN FND_API.G_EXC_ERROR THEN
1313 x_Return_Status := FND_API.G_RET_STS_ERROR;
1314 IF (g_excep_level >= g_debug_level ) THEN
1315 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
1316 END IF;
1317 RETURN;
1318
1319 WHEN OTHERS THEN
1320 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1321 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1322 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1323 END IF;
1324 IF ( g_unexp_level >= g_debug_level ) THEN
1325 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1326 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1327 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1328 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1329 END IF;
1330 RETURN;
1331
1332 END Archive_MRC_Tbls;
1333
1334 --
1335 -- Archive_NON_MRC_Tbls Procedure
1336 --
1337 -- Parameters :
1338 --
1339 -- x_Return_Status ==> Status of procedure returned to caller
1340 --
1341 PROCEDURE Archive_NON_MRC_Tbls (
1342 x_return_status OUT NOCOPY VARCHAR2
1343 ) IS
1344
1345 -- --------------------------------------------------------------------
1346 -- Declare local variables to be used in this procedure.
1347 -- --------------------------------------------------------------------
1348 l_api_name CONSTANT VARCHAR2(30) := 'Archive_NON_MRC_Tbls';
1349 l_full_path VARCHAR2(255);
1350
1351 BEGIN
1352
1353 l_full_path := g_path || 'Archive_NON_MRC_Tbls';
1354
1355 -- --------------------------------------------------------------------
1356 -- Initialize Return status and other local variables.
1357 -- --------------------------------------------------------------------
1358 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1359
1360 -- --------------------------------------------------------------------
1361 -- Insert all records that are able to be inserted based upon the
1362 -- last activity date and org id from the original MRC tables into the
1363 -- archive MRC tables.
1364 -- --------------------------------------------------------------------
1365 INSERT
1366 INTO igc_cc_arc_headers_all
1367 (CC_HEADER_ID ,
1368 ORG_ID ,
1369 CC_TYPE ,
1370 CC_NUM ,
1371 CC_REF_NUM ,
1372 CC_VERSION_NUM ,
1373 PARENT_HEADER_ID ,
1374 CC_STATE ,
1375 CC_CTRL_STATUS ,
1376 CC_ENCMBRNC_STATUS,
1377 CC_APPRVL_STATUS ,
1378 VENDOR_ID ,
1379 VENDOR_SITE_ID ,
1380 VENDOR_CONTACT_ID ,
1381 TERM_ID ,
1382 LOCATION_ID ,
1383 SET_OF_BOOKS_ID ,
1384 CC_ACCT_DATE ,
1385 CC_DESC ,
1386 CC_START_DATE ,
1387 CC_END_DATE ,
1388 CC_OWNER_USER_ID ,
1389 CC_PREPARER_USER_ID,
1390 CURRENCY_CODE ,
1391 CONVERSION_TYPE ,
1392 CONVERSION_DATE ,
1393 CONVERSION_RATE ,
1394 LAST_UPDATE_DATE ,
1395 LAST_UPDATED_BY ,
1396 LAST_UPDATE_LOGIN ,
1397 CREATED_BY ,
1398 CREATION_DATE ,
1399 CC_CURRENT_USER_ID ,
1400 WF_ITEM_TYPE ,
1401 WF_ITEM_KEY ,
1402 CONTEXT ,
1403 ATTRIBUTE1 ,
1404 ATTRIBUTE2 ,
1405 ATTRIBUTE3 ,
1406 ATTRIBUTE4 ,
1407 ATTRIBUTE5 ,
1408 ATTRIBUTE6 ,
1409 ATTRIBUTE7 ,
1410 ATTRIBUTE8 ,
1411 ATTRIBUTE9 ,
1412 ATTRIBUTE10 ,
1413 ATTRIBUTE11 ,
1414 ATTRIBUTE12 ,
1415 ATTRIBUTE13 ,
1416 ATTRIBUTE14 ,
1417 ATTRIBUTE15 ,
1418 CC_GUARANTEE_FLAG )
1419
1420 SELECT
1421 CC_HEADER_ID ,
1422 ORG_ID ,
1423 CC_TYPE ,
1424 CC_NUM ,
1425 CC_REF_NUM ,
1426 CC_VERSION_NUM ,
1427 PARENT_HEADER_ID ,
1428 CC_STATE ,
1429 CC_CTRL_STATUS ,
1430 CC_ENCMBRNC_STATUS,
1431 CC_APPRVL_STATUS ,
1432 VENDOR_ID ,
1433 VENDOR_SITE_ID ,
1434 VENDOR_CONTACT_ID ,
1435 TERM_ID ,
1436 LOCATION_ID ,
1437 SET_OF_BOOKS_ID ,
1438 CC_ACCT_DATE ,
1439 CC_DESC ,
1440 CC_START_DATE ,
1441 CC_END_DATE ,
1442 CC_OWNER_USER_ID ,
1443 CC_PREPARER_USER_ID,
1444 CURRENCY_CODE ,
1445 CONVERSION_TYPE ,
1446 CONVERSION_DATE ,
1447 CONVERSION_RATE ,
1448 LAST_UPDATE_DATE ,
1449 LAST_UPDATED_BY ,
1450 LAST_UPDATE_LOGIN ,
1451 CREATED_BY ,
1452 CREATION_DATE ,
1453 CC_CURRENT_USER_ID ,
1454 WF_ITEM_TYPE ,
1455 WF_ITEM_KEY ,
1456 CONTEXT ,
1457 ATTRIBUTE1 ,
1458 ATTRIBUTE2 ,
1459 ATTRIBUTE3 ,
1460 ATTRIBUTE4 ,
1461 ATTRIBUTE5 ,
1462 ATTRIBUTE6 ,
1463 ATTRIBUTE7 ,
1464 ATTRIBUTE8 ,
1465 ATTRIBUTE9 ,
1466 ATTRIBUTE10 ,
1467 ATTRIBUTE11 ,
1468 ATTRIBUTE12 ,
1469 ATTRIBUTE13 ,
1470 ATTRIBUTE14 ,
1471 ATTRIBUTE15 ,
1472 CC_GUARANTEE_FLAG
1473 FROM igc_cc_headers CH
1474 WHERE CH.cc_header_id IN
1475 ( SELECT cc_header_id
1476 FROM igc_arc_pur_candidates
1477 );
1478
1479 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1480 IF (g_debug_mode = 'Y') THEN
1481 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in Headers Table for ' ||
1482 ' Last Activity Date : ' || g_last_activity_date;
1483 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1484 END IF;
1485
1486 -- --------------------------------------------------------------------
1487 -- Make sure that only the period that is closed and matches the period
1488 -- name are inserted into the IGC_CC_ARCHIVE_MC_JE_LINES table.
1489 -- --------------------------------------------------------------------
1490 INSERT
1491 INTO igc_cc_arc_header_hist_all
1492 (CC_HEADER_ID ,
1493 ORG_ID ,
1494 CC_TYPE ,
1495 CC_NUM ,
1496 CC_REF_NUM ,
1497 CC_VERSION_NUM ,
1498 CC_VERSION_ACTION ,
1499 CC_STATE ,
1500 PARENT_HEADER_ID ,
1501 CC_CTRL_STATUS ,
1502 CC_ENCMBRNC_STATUS ,
1503 CC_APPRVL_STATUS ,
1504 VENDOR_ID ,
1505 VENDOR_SITE_ID ,
1506 VENDOR_CONTACT_ID ,
1507 TERM_ID ,
1508 LOCATION_ID ,
1509 SET_OF_BOOKS_ID ,
1510 CC_ACCT_DATE ,
1511 CC_DESC ,
1512 CC_START_DATE ,
1513 CC_END_DATE ,
1514 CC_OWNER_USER_ID ,
1515 CC_PREPARER_USER_ID ,
1516 CURRENCY_CODE ,
1517 CONVERSION_TYPE ,
1518 CONVERSION_DATE ,
1519 CONVERSION_RATE ,
1520 LAST_UPDATE_DATE ,
1521 LAST_UPDATED_BY ,
1522 LAST_UPDATE_LOGIN ,
1523 CREATED_BY ,
1524 CREATION_DATE ,
1525 WF_ITEM_TYPE ,
1526 WF_ITEM_KEY ,
1527 CC_CURRENT_USER_ID ,
1528 CONTEXT ,
1529 ATTRIBUTE1 ,
1530 ATTRIBUTE2 ,
1531 ATTRIBUTE3 ,
1532 ATTRIBUTE4 ,
1533 ATTRIBUTE5 ,
1534 ATTRIBUTE6 ,
1535 ATTRIBUTE7 ,
1536 ATTRIBUTE8 ,
1537 ATTRIBUTE9 ,
1538 ATTRIBUTE10 ,
1539 ATTRIBUTE11 ,
1540 ATTRIBUTE12 ,
1541 ATTRIBUTE13 ,
1542 ATTRIBUTE14 ,
1543 ATTRIBUTE15 ,
1544 CC_GUARANTEE_FLAG )
1545 SELECT
1546 CC_HEADER_ID ,
1547 ORG_ID ,
1548 CC_TYPE ,
1549 CC_NUM ,
1550 CC_REF_NUM ,
1551 CC_VERSION_NUM ,
1552 CC_VERSION_ACTION ,
1553 CC_STATE ,
1554 PARENT_HEADER_ID ,
1555 CC_CTRL_STATUS ,
1556 CC_ENCMBRNC_STATUS ,
1557 CC_APPRVL_STATUS ,
1558 VENDOR_ID ,
1559 VENDOR_SITE_ID ,
1560 VENDOR_CONTACT_ID ,
1561 TERM_ID ,
1562 LOCATION_ID ,
1563 SET_OF_BOOKS_ID ,
1564 CC_ACCT_DATE ,
1565 CC_DESC ,
1566 CC_START_DATE ,
1567 CC_END_DATE ,
1568 CC_OWNER_USER_ID ,
1569 CC_PREPARER_USER_ID ,
1570 CURRENCY_CODE ,
1571 CONVERSION_TYPE ,
1572 CONVERSION_DATE ,
1573 CONVERSION_RATE ,
1574 LAST_UPDATE_DATE ,
1575 LAST_UPDATED_BY ,
1576 LAST_UPDATE_LOGIN ,
1577 CREATED_BY ,
1578 CREATION_DATE ,
1579 WF_ITEM_TYPE ,
1580 WF_ITEM_KEY ,
1581 CC_CURRENT_USER_ID ,
1582 CONTEXT ,
1583 ATTRIBUTE1 ,
1584 ATTRIBUTE2 ,
1585 ATTRIBUTE3 ,
1586 ATTRIBUTE4 ,
1587 ATTRIBUTE5 ,
1588 ATTRIBUTE6 ,
1589 ATTRIBUTE7 ,
1590 ATTRIBUTE8 ,
1591 ATTRIBUTE9 ,
1592 ATTRIBUTE10 ,
1593 ATTRIBUTE11 ,
1594 ATTRIBUTE12 ,
1595 ATTRIBUTE13 ,
1596 ATTRIBUTE14 ,
1597 ATTRIBUTE15 ,
1598 CC_GUARANTEE_FLAG
1599 FROM igc_cc_header_history CHH
1600 WHERE CHH.cc_header_id IN
1601 ( SELECT cc_header_id
1602 FROM igc_arc_pur_candidates
1603 );
1604
1605 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1606 IF (g_debug_mode = 'Y') THEN
1607 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Headers Hist Table for ' ||
1608 ' Last Activity Date : ' || g_last_activity_date;
1609 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1610 END IF;
1611
1612 -- --------------------------------------------------------------------
1613 -- Insert all records that are able to be inserted based upon the
1614 -- last activity date and org id from the original MRC tables into the
1615 -- archive MRC tables.
1616 -- --------------------------------------------------------------------
1617 INSERT
1618 INTO igc_cc_arc_acct_lines
1619 ( CC_ACCT_LINE_ID ,
1620 CC_HEADER_ID ,
1621 PARENT_HEADER_ID ,
1622 PARENT_ACCT_LINE_ID ,
1623 CC_CHARGE_CODE_COMBINATION_ID ,
1624 CC_ACCT_LINE_NUM ,
1625 CC_BUDGET_CODE_COMBINATION_ID ,
1626 CC_ACCT_ENTERED_AMT ,
1627 CC_ACCT_FUNC_AMT ,
1628 CC_ACCT_DESC ,
1629 CC_ACCT_BILLED_AMT ,
1630 CC_ACCT_UNBILLED_AMT ,
1631 CC_ACCT_TAXABLE_FLAG ,
1632 TAX_ID ,
1633 CC_ACCT_ENCMBRNC_AMT ,
1634 CC_ACCT_ENCMBRNC_DATE ,
1635 CC_ACCT_ENCMBRNC_STATUS ,
1636 PROJECT_ID ,
1637 TASK_ID ,
1638 EXPENDITURE_TYPE ,
1639 EXPENDITURE_ORG_ID ,
1640 EXPENDITURE_ITEM_DATE ,
1641 LAST_UPDATE_DATE ,
1642 LAST_UPDATED_BY ,
1643 LAST_UPDATE_LOGIN ,
1644 CREATION_DATE ,
1645 CREATED_BY ,
1646 CONTEXT ,
1647 ATTRIBUTE1 ,
1648 ATTRIBUTE2 ,
1649 ATTRIBUTE3 ,
1650 ATTRIBUTE4 ,
1651 ATTRIBUTE5 ,
1652 ATTRIBUTE6 ,
1653 ATTRIBUTE7 ,
1654 ATTRIBUTE8 ,
1655 ATTRIBUTE9 ,
1656 ATTRIBUTE10 ,
1657 ATTRIBUTE11 ,
1658 ATTRIBUTE12 ,
1659 ATTRIBUTE13 ,
1660 ATTRIBUTE14 ,
1661 ATTRIBUTE15 ,
1662 CC_FUNC_WITHHELD_AMT ,
1663 CC_ENT_WITHHELD_AMT ,
1664 TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
1665 )
1666 SELECT
1667 CC_ACCT_LINE_ID ,
1668 CC_HEADER_ID ,
1669 PARENT_HEADER_ID ,
1670 PARENT_ACCT_LINE_ID ,
1671 CC_CHARGE_CODE_COMBINATION_ID ,
1672 CC_ACCT_LINE_NUM ,
1673 CC_BUDGET_CODE_COMBINATION_ID ,
1674 CC_ACCT_ENTERED_AMT ,
1675 CC_ACCT_FUNC_AMT ,
1676 CC_ACCT_DESC ,
1677 CC_ACCT_BILLED_AMT ,
1678 CC_ACCT_UNBILLED_AMT ,
1679 CC_ACCT_TAXABLE_FLAG ,
1680 TAX_ID ,
1681 CC_ACCT_ENCMBRNC_AMT ,
1682 CC_ACCT_ENCMBRNC_DATE ,
1683 CC_ACCT_ENCMBRNC_STATUS ,
1684 PROJECT_ID ,
1685 TASK_ID ,
1686 EXPENDITURE_TYPE ,
1687 EXPENDITURE_ORG_ID ,
1688 EXPENDITURE_ITEM_DATE ,
1689 LAST_UPDATE_DATE ,
1690 LAST_UPDATED_BY ,
1691 LAST_UPDATE_LOGIN ,
1692 CREATION_DATE ,
1693 CREATED_BY ,
1694 CONTEXT ,
1695 ATTRIBUTE1 ,
1696 ATTRIBUTE2 ,
1697 ATTRIBUTE3 ,
1698 ATTRIBUTE4 ,
1699 ATTRIBUTE5 ,
1700 ATTRIBUTE6 ,
1701 ATTRIBUTE7 ,
1702 ATTRIBUTE8 ,
1703 ATTRIBUTE9 ,
1704 ATTRIBUTE10 ,
1705 ATTRIBUTE11 ,
1706 ATTRIBUTE12 ,
1707 ATTRIBUTE13 ,
1708 ATTRIBUTE14 ,
1709 ATTRIBUTE15 ,
1710 CC_FUNC_WITHHELD_AMT ,
1711 CC_ENT_WITHHELD_AMT ,
1712 TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
1713 FROM igc_cc_acct_lines CL
1714 WHERE CL.cc_header_id IN
1715 ( SELECT ICV1.cc_header_id
1716 FROM igc_arc_pur_candidates ICV1
1717 );
1718
1719 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1720 IF (g_debug_mode = 'Y') THEN
1721 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Acct Line Table for ' ||
1722 ' Last Activity Date : ' || g_last_activity_date;
1723 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1724 END IF;
1725
1726 -- --------------------------------------------------------------------
1727 -- Make sure that only the period that is closed and matches the period
1728 -- name are inserted into the IGC_CC_ARCHIVE_MC_JE_LINES table.
1729 -- --------------------------------------------------------------------
1730 INSERT
1731 INTO igc_cc_arc_acct_line_hist
1732 ( CC_ACCT_LINE_ID ,
1733 CC_HEADER_ID ,
1734 PARENT_HEADER_ID ,
1735 PARENT_ACCT_LINE_ID ,
1736 CC_ACCT_LINE_NUM ,
1737 CC_ACCT_VERSION_NUM ,
1738 CC_ACCT_VERSION_ACTION ,
1739 CC_CHARGE_CODE_COMBINATION_ID ,
1740 CC_BUDGET_CODE_COMBINATION_ID ,
1741 CC_ACCT_ENTERED_AMT ,
1742 CC_ACCT_FUNC_AMT ,
1743 CC_ACCT_DESC ,
1744 CC_ACCT_BILLED_AMT ,
1745 CC_ACCT_UNBILLED_AMT ,
1746 CC_ACCT_TAXABLE_FLAG ,
1747 TAX_ID ,
1748 CC_ACCT_ENCMBRNC_AMT ,
1749 CC_ACCT_ENCMBRNC_DATE ,
1750 CC_ACCT_ENCMBRNC_STATUS ,
1751 PROJECT_ID ,
1752 TASK_ID ,
1753 EXPENDITURE_TYPE ,
1754 EXPENDITURE_ORG_ID ,
1755 EXPENDITURE_ITEM_DATE ,
1756 LAST_UPDATE_DATE ,
1757 LAST_UPDATED_BY ,
1758 LAST_UPDATE_LOGIN ,
1759 CREATION_DATE ,
1760 CREATED_BY ,
1761 CONTEXT ,
1762 ATTRIBUTE1 ,
1763 ATTRIBUTE2 ,
1764 ATTRIBUTE3 ,
1765 ATTRIBUTE4 ,
1766 ATTRIBUTE5 ,
1767 ATTRIBUTE6 ,
1768 ATTRIBUTE7 ,
1769 ATTRIBUTE8 ,
1770 ATTRIBUTE9 ,
1771 ATTRIBUTE10 ,
1772 ATTRIBUTE11 ,
1773 ATTRIBUTE12 ,
1774 ATTRIBUTE13 ,
1775 ATTRIBUTE14 ,
1776 ATTRIBUTE15 ,
1777 CC_FUNC_WITHHELD_AMT ,
1778 CC_ENT_WITHHELD_AMT ,
1779 TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
1780 )
1781 SELECT
1782 CC_ACCT_LINE_ID ,
1783 CC_HEADER_ID ,
1784 PARENT_HEADER_ID ,
1785 PARENT_ACCT_LINE_ID ,
1786 CC_ACCT_LINE_NUM ,
1787 CC_ACCT_VERSION_NUM ,
1788 CC_ACCT_VERSION_ACTION ,
1789 CC_CHARGE_CODE_COMBINATION_ID ,
1790 CC_BUDGET_CODE_COMBINATION_ID ,
1791 CC_ACCT_ENTERED_AMT ,
1792 CC_ACCT_FUNC_AMT ,
1793 CC_ACCT_DESC ,
1794 CC_ACCT_BILLED_AMT ,
1795 CC_ACCT_UNBILLED_AMT ,
1796 CC_ACCT_TAXABLE_FLAG ,
1797 TAX_ID ,
1798 CC_ACCT_ENCMBRNC_AMT ,
1799 CC_ACCT_ENCMBRNC_DATE ,
1800 CC_ACCT_ENCMBRNC_STATUS ,
1801 PROJECT_ID ,
1802 TASK_ID ,
1803 EXPENDITURE_TYPE ,
1804 EXPENDITURE_ORG_ID ,
1805 EXPENDITURE_ITEM_DATE ,
1806 LAST_UPDATE_DATE ,
1807 LAST_UPDATED_BY ,
1808 LAST_UPDATE_LOGIN ,
1809 CREATION_DATE ,
1810 CREATED_BY ,
1811 CONTEXT ,
1812 ATTRIBUTE1 ,
1813 ATTRIBUTE2 ,
1814 ATTRIBUTE3 ,
1815 ATTRIBUTE4 ,
1816 ATTRIBUTE5 ,
1817 ATTRIBUTE6 ,
1818 ATTRIBUTE7 ,
1819 ATTRIBUTE8 ,
1820 ATTRIBUTE9 ,
1821 ATTRIBUTE10 ,
1822 ATTRIBUTE11 ,
1823 ATTRIBUTE12 ,
1824 ATTRIBUTE13 ,
1825 ATTRIBUTE14 ,
1826 ATTRIBUTE15 ,
1827 CC_FUNC_WITHHELD_AMT ,
1828 CC_ENT_WITHHELD_AMT ,
1829 TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
1830 FROM igc_cc_acct_line_history CLH
1831 WHERE CLH.cc_header_id IN
1832 ( SELECT ICV1.cc_header_id
1833 FROM igc_arc_pur_candidates ICV1
1834 );
1835
1836 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1837 IF (g_debug_mode = 'Y') THEN
1838 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Acct Line Hist Table for ' ||
1839 ' Last Activity Date : ' || g_last_activity_date;
1840 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1841 END IF;
1842
1843 -- --------------------------------------------------------------------
1844 -- Insert all records that are able to be inserted based upon the
1845 -- last activity date and org id from the original MRC tables into the
1846 -- archive MRC tables.
1847 -- --------------------------------------------------------------------
1848 INSERT
1849 INTO igc_cc_arc_det_pf
1850 (CC_DET_PF_LINE_ID ,
1851 CC_DET_PF_LINE_NUM ,
1852 CC_ACCT_LINE_ID ,
1853 PARENT_ACCT_LINE_ID ,
1854 PARENT_DET_PF_LINE_ID ,
1855 CC_DET_PF_ENTERED_AMT ,
1856 CC_DET_PF_FUNC_AMT ,
1857 CC_DET_PF_DATE ,
1858 CC_DET_PF_BILLED_AMT ,
1859 CC_DET_PF_UNBILLED_AMT ,
1860 CC_DET_PF_ENCMBRNC_AMT ,
1861 CC_DET_PF_ENCMBRNC_DATE ,
1862 CC_DET_PF_ENCMBRNC_STATUS,
1863 LAST_UPDATE_DATE ,
1864 LAST_UPDATED_BY ,
1865 LAST_UPDATE_LOGIN ,
1866 CREATION_DATE ,
1867 CREATED_BY ,
1868 CONTEXT ,
1869 ATTRIBUTE1 ,
1870 ATTRIBUTE2 ,
1871 ATTRIBUTE3 ,
1872 ATTRIBUTE4 ,
1873 ATTRIBUTE5 ,
1874 ATTRIBUTE6 ,
1875 ATTRIBUTE7 ,
1876 ATTRIBUTE8 ,
1877 ATTRIBUTE9 ,
1878 ATTRIBUTE10 ,
1879 ATTRIBUTE11 ,
1880 ATTRIBUTE12 ,
1881 ATTRIBUTE13 ,
1882 ATTRIBUTE14 ,
1883 ATTRIBUTE15 )
1884 SELECT
1885 CC_DET_PF_LINE_ID ,
1886 CC_DET_PF_LINE_NUM ,
1887 CC_ACCT_LINE_ID ,
1888 PARENT_ACCT_LINE_ID ,
1889 PARENT_DET_PF_LINE_ID ,
1890 CC_DET_PF_ENTERED_AMT ,
1891 CC_DET_PF_FUNC_AMT ,
1892 CC_DET_PF_DATE ,
1893 CC_DET_PF_BILLED_AMT ,
1894 CC_DET_PF_UNBILLED_AMT ,
1895 CC_DET_PF_ENCMBRNC_AMT ,
1896 CC_DET_PF_ENCMBRNC_DATE ,
1897 CC_DET_PF_ENCMBRNC_STATUS,
1898 LAST_UPDATE_DATE ,
1899 LAST_UPDATED_BY ,
1900 LAST_UPDATE_LOGIN ,
1901 CREATION_DATE ,
1902 CREATED_BY ,
1903 CONTEXT ,
1904 ATTRIBUTE1 ,
1905 ATTRIBUTE2 ,
1906 ATTRIBUTE3 ,
1907 ATTRIBUTE4 ,
1908 ATTRIBUTE5 ,
1909 ATTRIBUTE6 ,
1910 ATTRIBUTE7 ,
1911 ATTRIBUTE8 ,
1912 ATTRIBUTE9 ,
1913 ATTRIBUTE10 ,
1914 ATTRIBUTE11 ,
1915 ATTRIBUTE12 ,
1916 ATTRIBUTE13 ,
1917 ATTRIBUTE14 ,
1918 ATTRIBUTE15
1919 FROM igc_cc_det_pf CDP
1920 WHERE CDP.cc_acct_line_id IN
1921 ( SELECT ACLH.cc_acct_line_id
1922 FROM igc_cc_acct_line_history ACLH
1923 WHERE ACLH.cc_header_id IN
1924 ( SELECT ICV1.cc_header_id
1925 FROM igc_arc_pur_candidates ICV1
1926 )
1927 )
1928 OR CDP.cc_acct_line_id IN
1929 ( SELECT ACL.cc_acct_line_id
1930 FROM igc_cc_acct_lines ACL
1931 WHERE ACL.cc_header_id IN
1932 ( SELECT ICV2.cc_header_id
1933 FROM igc_arc_pur_candidates ICV2
1934 )
1935 );
1936
1937 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
1938 IF (g_debug_mode = 'Y') THEN
1939 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Det PF Table for ' ||
1940 ' Last Activity Date : ' || g_last_activity_date;
1941 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
1942 END IF;
1943
1944 -- --------------------------------------------------------------------
1945 -- Make sure that only the period that is closed and matches the period
1946 -- name are inserted into the IGC_CC_ARC_DET_PF_HIST table.
1947 -- --------------------------------------------------------------------
1948 INSERT
1949 INTO igc_cc_arc_det_pf_hist
1950 ( CC_DET_PF_LINE_ID ,
1951 CC_DET_PF_LINE_NUM ,
1952 CC_ACCT_LINE_ID ,
1953 PARENT_ACCT_LINE_ID ,
1954 PARENT_DET_PF_LINE_ID ,
1955 CC_DET_PF_VERSION_NUM ,
1956 CC_DET_PF_VERSION_ACTION ,
1957 CC_DET_PF_ENTERED_AMT ,
1958 CC_DET_PF_FUNC_AMT ,
1959 CC_DET_PF_DATE ,
1960 CC_DET_PF_BILLED_AMT ,
1961 CC_DET_PF_UNBILLED_AMT ,
1962 CC_DET_PF_ENCMBRNC_AMT ,
1963 CC_DET_PF_ENCMBRNC_DATE ,
1964 CC_DET_PF_ENCMBRNC_STATUS ,
1965 LAST_UPDATE_DATE ,
1966 LAST_UPDATED_BY ,
1967 LAST_UPDATE_LOGIN ,
1968 CREATION_DATE ,
1969 CREATED_BY ,
1970 CONTEXT ,
1971 ATTRIBUTE1 ,
1972 ATTRIBUTE2 ,
1973 ATTRIBUTE3 ,
1974 ATTRIBUTE4 ,
1975 ATTRIBUTE5 ,
1976 ATTRIBUTE6 ,
1977 ATTRIBUTE7 ,
1978 ATTRIBUTE8 ,
1979 ATTRIBUTE9 ,
1980 ATTRIBUTE10 ,
1981 ATTRIBUTE11 ,
1982 ATTRIBUTE12 ,
1983 ATTRIBUTE13 ,
1984 ATTRIBUTE14 ,
1985 ATTRIBUTE15 )
1986 SELECT
1987 CC_DET_PF_LINE_ID ,
1988 CC_DET_PF_LINE_NUM ,
1989 CC_ACCT_LINE_ID ,
1990 PARENT_ACCT_LINE_ID ,
1991 PARENT_DET_PF_LINE_ID ,
1992 CC_DET_PF_VERSION_NUM ,
1993 CC_DET_PF_VERSION_ACTION ,
1994 CC_DET_PF_ENTERED_AMT ,
1995 CC_DET_PF_FUNC_AMT ,
1996 CC_DET_PF_DATE ,
1997 CC_DET_PF_BILLED_AMT ,
1998 CC_DET_PF_UNBILLED_AMT ,
1999 CC_DET_PF_ENCMBRNC_AMT ,
2000 CC_DET_PF_ENCMBRNC_DATE ,
2001 CC_DET_PF_ENCMBRNC_STATUS ,
2002 LAST_UPDATE_DATE ,
2003 LAST_UPDATED_BY ,
2004 LAST_UPDATE_LOGIN ,
2005 CREATION_DATE ,
2006 CREATED_BY ,
2007 CONTEXT ,
2008 ATTRIBUTE1 ,
2009 ATTRIBUTE2 ,
2010 ATTRIBUTE3 ,
2011 ATTRIBUTE4 ,
2012 ATTRIBUTE5 ,
2013 ATTRIBUTE6 ,
2014 ATTRIBUTE7 ,
2015 ATTRIBUTE8 ,
2016 ATTRIBUTE9 ,
2017 ATTRIBUTE10 ,
2018 ATTRIBUTE11 ,
2019 ATTRIBUTE12 ,
2020 ATTRIBUTE13 ,
2021 ATTRIBUTE14 ,
2022 ATTRIBUTE15
2023 FROM igc_cc_det_pf_history CDPH
2024 WHERE CDPH.cc_acct_line_id IN
2025 ( SELECT ACLH.cc_acct_line_id
2026 FROM igc_cc_acct_line_history ACLH
2027 WHERE ACLH.cc_header_id IN
2028 ( SELECT ICV1.cc_header_id
2029 FROM igc_arc_pur_candidates ICV1
2030 )
2031 )
2032 OR CDPH.cc_acct_line_id IN
2033 ( SELECT ACL.cc_acct_line_id
2034 FROM igc_cc_acct_lines ACL
2035 WHERE ACL.cc_header_id IN
2036 ( SELECT ICV2.cc_header_id
2037 FROM igc_arc_pur_candidates ICV2
2038 )
2039 );
2040
2041 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
2042 IF (g_debug_mode = 'Y') THEN
2043 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Det PF Hist Table for ' ||
2044 ' Last Activity Date : ' || g_last_activity_date;
2045 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
2046 END IF;
2047
2048 -- --------------------------------------------------------------------
2049 -- Make sure that the CC Actions table is archived for the CC Header
2050 -- IDs that are candidates for archiving.
2051 -- --------------------------------------------------------------------
2052 INSERT
2053 INTO igc_cc_arc_actions
2054 ( CC_HEADER_ID ,
2055 CC_ACTION_NUM ,
2056 CC_ACTION_VERSION_NUM ,
2057 CC_ACTION_TYPE ,
2058 CC_ACTION_STATE ,
2059 CC_ACTION_CTRL_STATUS ,
2060 CC_ACTION_APPRVL_STATUS ,
2061 CC_ACTION_NOTES ,
2062 LAST_UPDATE_DATE ,
2063 LAST_UPDATED_BY ,
2064 LAST_UPDATE_LOGIN ,
2065 CREATION_DATE ,
2066 CREATED_BY )
2067 SELECT
2068 CC_HEADER_ID ,
2069 CC_ACTION_NUM ,
2070 CC_ACTION_VERSION_NUM ,
2071 CC_ACTION_TYPE ,
2072 CC_ACTION_STATE ,
2073 CC_ACTION_CTRL_STATUS ,
2074 CC_ACTION_APPRVL_STATUS ,
2075 CC_ACTION_NOTES ,
2076 LAST_UPDATE_DATE ,
2077 LAST_UPDATED_BY ,
2078 LAST_UPDATE_LOGIN ,
2079 CREATION_DATE ,
2080 CREATED_BY
2081 FROM igc_cc_actions CA
2082 WHERE CA.cc_header_id IN
2083 ( SELECT ICV.cc_header_id
2084 FROM igc_arc_pur_candidates ICV
2085 );
2086
2087 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
2088 IF (g_debug_mode = 'Y') THEN
2089 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Actions Table for ' ||
2090 ' Last Activity Date : ' || g_last_activity_date;
2091 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
2092 END IF;
2093
2094 -- --------------------------------------------------------------------
2095 -- Archive the appropriate PO tables that contain the information on
2096 -- the CC Headers that have already been archived.
2097 -- --------------------------------------------------------------------
2098 INSERT
2099 INTO igc_cc_arc_po_headers_all
2100 (po_header_id,
2101 agent_id,
2102 type_lookup_code,
2103 last_update_date,
2104 last_updated_by,
2105 segment1,
2106 summary_flag,
2107 enabled_flag,
2108 segment2,
2109 segment3,
2110 segment4,
2111 segment5,
2112 start_date_active,
2113 end_date_active,
2114 last_update_login,
2115 creation_date,
2116 created_by,
2117 vendor_id,
2118 vendor_site_id,
2119 vendor_contact_id,
2120 ship_to_location_id,
2121 bill_to_location_id,
2122 terms_id,
2123 ship_via_lookup_code,
2124 fob_lookup_code,
2125 freight_terms_lookup_code,
2126 status_lookup_code,
2127 currency_code,
2128 rate_type,
2129 rate_date,
2130 rate,
2131 from_header_id,
2132 from_type_lookup_code,
2133 start_date,
2134 end_date,
2135 blanket_total_amount,
2136 authorization_status,
2137 revision_num,
2138 revised_date,
2139 approved_flag,
2140 approved_date,
2141 amount_limit,
2142 min_release_amount,
2143 note_to_authorizer,
2144 note_to_vendor,
2145 note_to_receiver,
2146 print_count,
2147 printed_date,
2148 vendor_order_num,
2149 confirming_order_flag,
2150 comments,
2151 reply_date,
2152 reply_method_lookup_code,
2153 rfq_close_date,
2154 quote_type_lookup_code,
2155 quotation_class_code,
2156 quote_warning_delay_unit,
2157 quote_warning_delay,
2158 quote_vendor_quote_number,
2159 acceptance_required_flag,
2160 acceptance_due_date,
2161 closed_date,
2162 user_hold_flag,
2163 approval_required_flag,
2164 cancel_flag,
2165 firm_status_lookup_code,
2166 firm_date,
2167 frozen_flag,
2168 supply_agreement_flag,
2169 edi_processed_flag,
2170 edi_processed_status,
2171 attribute_category,
2172 attribute1,
2173 attribute2,
2174 attribute3,
2175 attribute4,
2176 attribute5,
2177 attribute6,
2178 attribute7,
2179 attribute8,
2180 attribute9,
2181 attribute10,
2182 attribute11,
2183 attribute12,
2184 attribute13,
2185 attribute14,
2186 attribute15,
2187 closed_code,
2188 ussgl_transaction_code,
2189 government_context,
2190 request_id,
2191 program_application_id,
2192 program_id,
2193 program_update_date,
2194 org_id,
2195 global_attribute_category,
2196 global_attribute1,
2197 global_attribute2,
2198 global_attribute3,
2199 global_attribute4,
2200 global_attribute5,
2201 global_attribute6,
2202 global_attribute7,
2203 global_attribute8,
2204 global_attribute9,
2205 global_attribute10,
2206 global_attribute11,
2207 global_attribute12,
2208 global_attribute13,
2209 global_attribute14,
2210 global_attribute15,
2211 global_attribute16,
2212 global_attribute17,
2213 global_attribute18,
2214 global_attribute19,
2215 global_attribute20,
2216 interface_source_code,
2217 reference_num,
2218 wf_item_type,
2219 wf_item_key,
2220 mrc_rate_type,
2221 mrc_rate_date,
2222 mrc_rate,
2223 pcard_id,
2224 price_update_tolerance,
2225 pay_on_code
2226 )
2227 SELECT po_header_id,
2228 agent_id,
2229 type_lookup_code,
2230 last_update_date,
2231 last_updated_by,
2232 segment1,
2233 summary_flag,
2234 enabled_flag,
2235 segment2,
2236 segment3,
2237 segment4,
2238 segment5,
2239 start_date_active,
2240 end_date_active,
2241 last_update_login,
2242 creation_date,
2243 created_by,
2244 vendor_id,
2245 vendor_site_id,
2246 vendor_contact_id,
2247 ship_to_location_id,
2248 bill_to_location_id,
2249 terms_id,
2250 ship_via_lookup_code,
2251 fob_lookup_code,
2252 freight_terms_lookup_code,
2253 status_lookup_code,
2254 currency_code,
2255 rate_type,
2256 rate_date,
2257 rate,
2258 from_header_id,
2259 from_type_lookup_code,
2260 start_date,
2261 end_date,
2262 blanket_total_amount,
2263 authorization_status,
2264 revision_num,
2265 revised_date,
2266 approved_flag,
2267 approved_date,
2268 amount_limit,
2269 min_release_amount,
2270 note_to_authorizer,
2271 note_to_vendor,
2272 note_to_receiver,
2273 print_count,
2274 printed_date,
2275 vendor_order_num,
2276 confirming_order_flag,
2277 comments,
2278 reply_date,
2279 reply_method_lookup_code,
2280 rfq_close_date,
2281 quote_type_lookup_code,
2282 quotation_class_code,
2283 quote_warning_delay_unit,
2284 quote_warning_delay,
2285 quote_vendor_quote_number,
2286 acceptance_required_flag,
2287 acceptance_due_date,
2288 closed_date,
2289 user_hold_flag,
2290 approval_required_flag,
2291 cancel_flag,
2292 firm_status_lookup_code,
2293 firm_date,
2294 frozen_flag,
2295 supply_agreement_flag,
2296 edi_processed_flag,
2297 edi_processed_status,
2298 attribute_category,
2299 attribute1,
2300 attribute2,
2301 attribute3,
2302 attribute4,
2303 attribute5,
2304 attribute6,
2305 attribute7,
2306 attribute8,
2307 attribute9,
2308 attribute10,
2309 attribute11,
2310 attribute12,
2311 attribute13,
2312 attribute14,
2313 attribute15,
2314 closed_code,
2315 ussgl_transaction_code,
2316 government_context,
2317 request_id,
2318 program_application_id,
2319 program_id,
2320 program_update_date,
2321 org_id,
2322 global_attribute_category,
2323 global_attribute1,
2324 global_attribute2,
2325 global_attribute3,
2326 global_attribute4,
2327 global_attribute5,
2328 global_attribute6,
2329 global_attribute7,
2330 global_attribute8,
2331 global_attribute9,
2332 global_attribute10,
2333 global_attribute11,
2334 global_attribute12,
2335 global_attribute13,
2336 global_attribute14,
2337 global_attribute15,
2338 global_attribute16,
2339 global_attribute17,
2340 global_attribute18,
2341 global_attribute19,
2342 global_attribute20,
2343 interface_source_code,
2344 reference_num,
2345 wf_item_type,
2346 wf_item_key,
2347 mrc_rate_type,
2348 mrc_rate_date,
2349 mrc_rate,
2350 pcard_id,
2351 price_update_tolerance,
2352 pay_on_code
2353 FROM po_headers PHA
2354 -- ssmales 11/07/03 bug 2885953 - amended where clause below for performance issues
2355 -- WHERE PHA.po_header_id IN
2356 -- ( SELECT PHA1.po_header_id
2357 -- FROM po_headers_all PHA1
2358 -- WHERE PHA1.segment1 IN
2359 WHERE PHA.segment1 IN
2360 ( SELECT ICV.cc_num
2361 FROM igc_arc_pur_candidates ICV
2362 );
2363 -- );
2364
2365 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
2366 IF (g_debug_mode = 'Y') THEN
2367 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Headers ALL Table for ' ||
2368 ' Last Activity Date : ' || g_last_activity_date;
2369 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
2370 END IF;
2371
2372 INSERT
2373 INTO igc_cc_arc_po_lines_all
2374 (po_line_id,
2375 last_update_date,
2376 last_updated_by,
2377 po_header_id,
2378 line_type_id,
2379 line_num,
2380 last_update_login,
2381 creation_date,
2382 created_by,
2383 item_id,
2384 item_revision,
2385 category_id,
2386 item_description,
2387 unit_meas_lookup_code,
2388 quantity_committed,
2389 committed_amount,
2390 allow_price_override_flag,
2391 not_to_exceed_price,
2392 list_price_per_unit,
2393 unit_price,
2394 quantity,
2395 un_number_id,
2396 hazard_class_id,
2397 note_to_vendor,
2398 from_header_id,
2399 from_line_id,
2400 min_order_quantity,
2401 max_order_quantity,
2402 qty_rcv_tolerance,
2403 over_tolerance_error_flag,
2404 market_price,
2405 unordered_flag,
2406 closed_flag,
2407 user_hold_flag,
2408 cancel_flag,
2409 cancelled_by,
2410 cancel_date,
2411 cancel_reason,
2412 firm_status_lookup_code,
2413 firm_date,
2414 vendor_product_num,
2415 contract_num,
2416 taxable_flag,
2417 tax_name,
2418 type_1099,
2419 capital_expense_flag,
2420 negotiated_by_preparer_flag,
2421 attribute_category,
2422 attribute1,
2423 attribute2,
2424 attribute3,
2425 attribute4,
2426 attribute5,
2427 attribute6,
2428 attribute7,
2429 attribute8,
2430 attribute9,
2431 attribute10,
2432 reference_num,
2433 attribute11,
2434 attribute12,
2435 attribute13,
2436 attribute14,
2437 attribute15,
2438 min_release_amount,
2439 price_type_lookup_code,
2440 closed_code,
2441 price_break_lookup_code,
2442 ussgl_transaction_code,
2443 government_context,
2444 request_id,
2445 program_application_id,
2446 program_id,
2447 program_update_date,
2448 closed_date,
2449 closed_reason,
2450 closed_by,
2451 transaction_reason_code,
2452 org_id,
2453 qc_grade,
2454 base_uom,
2455 base_qty,
2456 secondary_uom,
2457 secondary_qty,
2458 global_attribute_category,
2459 global_attribute1,
2460 global_attribute2,
2461 global_attribute3,
2462 global_attribute4,
2463 global_attribute5,
2464 global_attribute6,
2465 global_attribute7,
2466 global_attribute8,
2467 global_attribute9,
2468 global_attribute10,
2469 global_attribute11,
2470 global_attribute12,
2471 global_attribute13,
2472 global_attribute14,
2473 global_attribute15,
2474 global_attribute16,
2475 global_attribute17,
2476 global_attribute18,
2477 global_attribute19,
2478 global_attribute20,
2479 line_reference_num,
2480 project_id,
2481 task_id,
2482 expiration_date,
2483 tax_code_id
2484 )
2485 SELECT po_line_id,
2486 last_update_date,
2487 last_updated_by,
2488 po_header_id,
2489 line_type_id,
2490 line_num,
2491 last_update_login,
2492 creation_date,
2493 created_by,
2494 item_id,
2495 item_revision,
2496 category_id,
2497 item_description,
2498 unit_meas_lookup_code,
2499 quantity_committed,
2500 committed_amount,
2501 allow_price_override_flag,
2502 not_to_exceed_price,
2503 list_price_per_unit,
2504 unit_price,
2505 quantity,
2506 un_number_id,
2507 hazard_class_id,
2508 note_to_vendor,
2509 from_header_id,
2510 from_line_id,
2511 min_order_quantity,
2512 max_order_quantity,
2513 qty_rcv_tolerance,
2514 over_tolerance_error_flag,
2515 market_price,
2516 unordered_flag,
2517 closed_flag,
2518 user_hold_flag,
2519 cancel_flag,
2520 cancelled_by,
2521 cancel_date,
2522 cancel_reason,
2523 firm_status_lookup_code,
2524 firm_date,
2525 vendor_product_num,
2526 contract_num,
2527 taxable_flag,
2528 tax_name,
2529 type_1099,
2530 capital_expense_flag,
2531 negotiated_by_preparer_flag,
2532 attribute_category,
2533 attribute1,
2534 attribute2,
2535 attribute3,
2536 attribute4,
2537 attribute5,
2538 attribute6,
2539 attribute7,
2540 attribute8,
2541 attribute9,
2542 attribute10,
2543 reference_num,
2544 attribute11,
2545 attribute12,
2546 attribute13,
2547 attribute14,
2548 attribute15,
2549 min_release_amount,
2550 price_type_lookup_code,
2551 closed_code,
2552 price_break_lookup_code,
2553 ussgl_transaction_code,
2554 government_context,
2555 request_id,
2556 program_application_id,
2557 program_id,
2558 program_update_date,
2559 closed_date,
2560 closed_reason,
2561 closed_by,
2562 transaction_reason_code,
2563 org_id,
2564 qc_grade,
2565 base_uom,
2566 base_qty,
2567 secondary_uom,
2568 secondary_qty,
2569 global_attribute_category,
2570 global_attribute1,
2571 global_attribute2,
2572 global_attribute3,
2573 global_attribute4,
2574 global_attribute5,
2575 global_attribute6,
2576 global_attribute7,
2577 global_attribute8,
2578 global_attribute9,
2579 global_attribute10,
2580 global_attribute11,
2581 global_attribute12,
2582 global_attribute13,
2583 global_attribute14,
2584 global_attribute15,
2585 global_attribute16,
2586 global_attribute17,
2587 global_attribute18,
2588 global_attribute19,
2589 global_attribute20,
2590 line_reference_num,
2591 project_id,
2592 task_id,
2593 expiration_date,
2594 tax_code_id
2595 FROM po_lines PLA
2596 WHERE PLA.po_header_id IN
2597 ( SELECT PHA.po_header_id
2598 FROM po_headers PHA
2599 WHERE PHA.segment1 IN
2600 ( SELECT ICV.cc_num
2601 FROM igc_arc_pur_candidates ICV
2602 )
2603 );
2604
2605 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
2606 IF (g_debug_mode = 'Y') THEN
2607 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Lines ALL Table for ' ||
2608 ' Last Activity Date : ' || g_last_activity_date;
2609 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
2610 END IF;
2611
2612 INSERT
2613 INTO igc_cc_arc_po_line_loc_all
2614 (line_location_id,
2615 last_update_date,
2616 last_updated_by,
2617 po_header_id,
2618 po_line_id,
2619 last_update_login,
2620 creation_date,
2621 created_by,
2622 quantity,
2623 quantity_received,
2624 quantity_accepted,
2625 quantity_rejected,
2626 quantity_billed,
2627 quantity_cancelled,
2628 unit_meas_lookup_code,
2629 po_release_id,
2630 ship_to_location_id,
2631 ship_via_lookup_code,
2632 need_by_date,
2633 promised_date,
2634 last_accept_date,
2635 price_override,
2636 encumbered_flag,
2637 encumbered_date,
2638 unencumbered_quantity,
2639 fob_lookup_code,
2640 freight_terms_lookup_code,
2641 taxable_flag,
2642 tax_name,
2643 estimated_tax_amount,
2644 from_header_id,
2645 from_line_id,
2646 from_line_location_id,
2647 start_date,
2648 end_date,
2649 lead_time,
2650 lead_time_unit,
2651 price_discount,
2652 terms_id,
2653 approved_flag,
2654 approved_date,
2655 closed_flag,
2656 cancel_flag,
2657 cancelled_by,
2658 cancel_date,
2659 cancel_reason,
2660 firm_status_lookup_code,
2661 firm_date,
2662 attribute_category,
2663 attribute1,
2664 attribute2,
2665 attribute3,
2666 attribute4,
2667 attribute5,
2668 attribute6,
2669 attribute7,
2670 attribute8,
2671 attribute9,
2672 attribute10,
2673 unit_of_measure_class,
2674 encumber_now,
2675 attribute11,
2676 attribute12,
2677 attribute13,
2678 attribute14,
2679 attribute15,
2680 inspection_required_flag,
2681 receipt_required_flag,
2682 qty_rcv_tolerance,
2683 qty_rcv_exception_code,
2684 enforce_ship_to_location_code,
2685 allow_substitute_receipts_flag,
2686 days_early_receipt_allowed,
2687 days_late_receipt_allowed,
2688 receipt_days_exception_code,
2689 invoice_close_tolerance,
2690 receive_close_tolerance,
2691 ship_to_organization_id,
2692 shipment_num,
2693 source_shipment_id,
2694 shipment_type,
2695 closed_code,
2696 request_id,
2697 program_application_id,
2698 program_id,
2699 program_update_date,
2700 ussgl_transaction_code,
2701 government_context,
2702 receiving_routing_id,
2703 accrue_on_receipt_flag,
2704 closed_reason,
2705 closed_date,
2706 closed_by,
2707 org_id,
2708 global_attribute1,
2709 global_attribute2,
2710 global_attribute3,
2711 global_attribute4,
2712 global_attribute5,
2713 global_attribute6,
2714 global_attribute7,
2715 global_attribute8,
2716 global_attribute9,
2717 global_attribute10,
2718 global_attribute11,
2719 global_attribute12,
2720 global_attribute13,
2721 global_attribute14,
2722 global_attribute15,
2723 global_attribute16,
2724 global_attribute17,
2725 global_attribute18,
2726 global_attribute19,
2727 global_attribute20,
2728 global_attribute_category,
2729 quantity_shipped,
2730 country_of_origin_code,
2731 tax_user_override_flag,
2732 match_option,
2733 tax_code_id,
2734 calculate_tax_flag,
2735 change_promised_date_reason
2736 )
2737 SELECT line_location_id,
2738 last_update_date,
2739 last_updated_by,
2740 po_header_id,
2741 po_line_id,
2742 last_update_login,
2743 creation_date,
2744 created_by,
2745 quantity,
2746 quantity_received,
2747 quantity_accepted,
2748 quantity_rejected,
2749 quantity_billed,
2750 quantity_cancelled,
2751 unit_meas_lookup_code,
2752 po_release_id,
2753 ship_to_location_id,
2754 ship_via_lookup_code,
2755 need_by_date,
2756 promised_date,
2757 last_accept_date,
2758 price_override,
2759 encumbered_flag,
2760 encumbered_date,
2761 unencumbered_quantity,
2762 fob_lookup_code,
2763 freight_terms_lookup_code,
2764 taxable_flag,
2765 tax_name,
2766 estimated_tax_amount,
2767 from_header_id,
2768 from_line_id,
2769 from_line_location_id,
2770 start_date,
2771 end_date,
2772 lead_time,
2773 lead_time_unit,
2774 price_discount,
2775 terms_id,
2776 approved_flag,
2777 approved_date,
2778 closed_flag,
2779 cancel_flag,
2780 cancelled_by,
2781 cancel_date,
2782 cancel_reason,
2783 firm_status_lookup_code,
2784 firm_date,
2785 attribute_category,
2786 attribute1,
2787 attribute2,
2788 attribute3,
2789 attribute4,
2790 attribute5,
2791 attribute6,
2792 attribute7,
2793 attribute8,
2794 attribute9,
2795 attribute10,
2796 unit_of_measure_class,
2797 encumber_now,
2798 attribute11,
2799 attribute12,
2800 attribute13,
2801 attribute14,
2802 attribute15,
2803 inspection_required_flag,
2804 receipt_required_flag,
2805 qty_rcv_tolerance,
2806 qty_rcv_exception_code,
2807 enforce_ship_to_location_code,
2808 allow_substitute_receipts_flag,
2809 days_early_receipt_allowed,
2810 days_late_receipt_allowed,
2811 receipt_days_exception_code,
2812 invoice_close_tolerance,
2813 receive_close_tolerance,
2814 ship_to_organization_id,
2815 shipment_num,
2816 source_shipment_id,
2817 shipment_type,
2818 closed_code,
2819 request_id,
2820 program_application_id,
2821 program_id,
2822 program_update_date,
2823 ussgl_transaction_code,
2824 government_context,
2825 receiving_routing_id,
2826 accrue_on_receipt_flag,
2827 closed_reason,
2828 closed_date,
2829 closed_by,
2830 org_id,
2831 global_attribute1,
2832 global_attribute2,
2833 global_attribute3,
2834 global_attribute4,
2835 global_attribute5,
2836 global_attribute6,
2837 global_attribute7,
2838 global_attribute8,
2839 global_attribute9,
2840 global_attribute10,
2841 global_attribute11,
2842 global_attribute12,
2843 global_attribute13,
2844 global_attribute14,
2845 global_attribute15,
2846 global_attribute16,
2847 global_attribute17,
2848 global_attribute18,
2849 global_attribute19,
2850 global_attribute20,
2851 global_attribute_category,
2852 quantity_shipped,
2853 country_of_origin_code,
2854 tax_user_override_flag,
2855 match_option,
2856 tax_code_id,
2857 calculate_tax_flag,
2858 change_promised_date_reason
2859 FROM po_line_locations PLLA
2860 WHERE PLLA.po_header_id IN
2861 ( SELECT po_header_id
2862 FROM po_headers PHA
2863 WHERE PHA.segment1 IN
2864 ( SELECT ICV.cc_num
2865 FROM igc_arc_pur_candidates ICV
2866 )
2867 );
2868
2869 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
2870 IF (g_debug_mode = 'Y') THEN
2871 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Line Loc ALL Table for ' ||
2872 ' Last Activity Date : ' || g_last_activity_date;
2873 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
2874 END IF;
2875
2876 INSERT
2877 INTO igc_cc_arc_po_distribution_all
2878 (po_distribution_id,
2879 last_update_date,
2880 last_updated_by,
2881 po_header_id,
2882 po_line_id,
2883 line_location_id,
2884 set_of_books_id,
2885 code_combination_id,
2886 quantity_ordered,
2887 last_update_login,
2888 creation_date,
2889 created_by,
2890 po_release_id,
2891 quantity_delivered,
2892 quantity_billed,
2893 quantity_cancelled,
2894 req_header_reference_num,
2895 req_line_reference_num,
2896 req_distribution_id,
2897 deliver_to_location_id,
2898 deliver_to_person_id,
2899 rate_date,
2900 rate,
2901 amount_billed,
2902 accrued_flag,
2903 encumbered_flag,
2904 encumbered_amount,
2905 unencumbered_quantity,
2906 unencumbered_amount,
2907 failed_funds_lookup_code,
2908 gl_encumbered_date,
2909 gl_encumbered_period_name,
2910 gl_cancelled_date,
2911 destination_type_code,
2912 destination_organization_id,
2913 destination_subinventory,
2914 attribute_category,
2915 attribute1,
2916 attribute2,
2917 attribute3,
2918 attribute4,
2919 attribute5,
2920 attribute6,
2921 attribute7,
2922 attribute8,
2923 attribute9,
2924 attribute10,
2925 attribute11,
2926 attribute12,
2927 attribute13,
2928 attribute14,
2929 attribute15,
2930 wip_entity_id,
2931 wip_operation_seq_num,
2932 wip_resource_seq_num,
2933 wip_repetitive_schedule_id,
2934 wip_line_id,
2935 bom_resource_id,
2936 budget_account_id,
2937 accrual_account_id,
2938 variance_account_id,
2939 prevent_encumbrance_flag,
2940 ussgl_transaction_code,
2941 government_context,
2942 destination_context,
2943 distribution_num,
2944 source_distribution_id,
2945 request_id,
2946 program_application_id,
2947 program_id,
2948 program_update_date,
2949 project_id,
2950 task_id,
2951 expenditure_type,
2952 project_accounting_context,
2953 expenditure_organization_id,
2954 gl_closed_date,
2955 accrue_on_receipt_flag,
2956 expenditure_item_date,
2957 org_id,
2958 kanban_card_id,
2959 award_id,
2960 mrc_rate_date,
2961 mrc_rate,
2962 mrc_encumbered_amount,
2963 mrc_unencumbered_amount,
2964 end_item_unit_number,
2965 tax_recovery_override_flag,
2966 recoverable_tax,
2967 nonrecoverable_tax,
2968 recovery_rate
2969 )
2970 SELECT po_distribution_id,
2971 last_update_date,
2972 last_updated_by,
2973 po_header_id,
2974 po_line_id,
2975 line_location_id,
2976 set_of_books_id,
2977 code_combination_id,
2978 quantity_ordered,
2979 last_update_login,
2980 creation_date,
2981 created_by,
2982 po_release_id,
2983 quantity_delivered,
2984 quantity_billed,
2985 quantity_cancelled,
2986 req_header_reference_num,
2987 req_line_reference_num,
2988 req_distribution_id,
2989 deliver_to_location_id,
2990 deliver_to_person_id,
2991 rate_date,
2992 rate,
2993 amount_billed,
2994 accrued_flag,
2995 encumbered_flag,
2996 encumbered_amount,
2997 unencumbered_quantity,
2998 unencumbered_amount,
2999 failed_funds_lookup_code,
3000 gl_encumbered_date,
3001 gl_encumbered_period_name,
3002 gl_cancelled_date,
3003 destination_type_code,
3004 destination_organization_id,
3005 destination_subinventory,
3006 attribute_category,
3007 attribute1,
3008 attribute2,
3009 attribute3,
3010 attribute4,
3011 attribute5,
3012 attribute6,
3013 attribute7,
3014 attribute8,
3015 attribute9,
3016 attribute10,
3017 attribute11,
3018 attribute12,
3019 attribute13,
3020 attribute14,
3021 attribute15,
3022 wip_entity_id,
3023 wip_operation_seq_num,
3024 wip_resource_seq_num,
3025 wip_repetitive_schedule_id,
3026 wip_line_id,
3027 bom_resource_id,
3028 budget_account_id,
3029 accrual_account_id,
3030 variance_account_id,
3031 prevent_encumbrance_flag,
3032 ussgl_transaction_code,
3033 government_context,
3034 destination_context,
3035 distribution_num,
3036 source_distribution_id,
3037 request_id,
3038 program_application_id,
3039 program_id,
3040 program_update_date,
3041 project_id,
3042 task_id,
3043 expenditure_type,
3044 project_accounting_context,
3045 expenditure_organization_id,
3046 gl_closed_date,
3047 accrue_on_receipt_flag,
3048 expenditure_item_date,
3049 org_id,
3050 kanban_card_id,
3051 award_id,
3052 mrc_rate_date,
3053 mrc_rate,
3054 mrc_encumbered_amount,
3055 mrc_unencumbered_amount,
3056 end_item_unit_number,
3057 tax_recovery_override_flag,
3058 recoverable_tax,
3059 nonrecoverable_tax,
3060 recovery_rate
3061 FROM po_distributions PDA
3062 WHERE PDA.po_header_id IN
3063 ( SELECT po_header_id
3064 FROM po_headers PHA
3065 WHERE PHA.segment1 IN
3066 ( SELECT ICV.cc_num
3067 FROM igc_arc_pur_candidates ICV
3068 )
3069 );
3070
3071 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3072 IF (g_debug_mode = 'Y') THEN
3073 g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Dist ALL Table for ' ||
3074 ' Last Activity Date : ' || g_last_activity_date;
3075 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3076 END IF;
3077
3078 RETURN;
3079
3080 -- --------------------------------------------------------------------
3081 -- Exception handler section for the Archive_NON_MRC_Tbls procedure.
3082 -- --------------------------------------------------------------------
3083 EXCEPTION
3084
3085 WHEN NO_DATA_FOUND THEN
3086 x_Return_Status := FND_API.G_RET_STS_ERROR;
3087 IF (g_excep_level >= g_debug_level ) THEN
3088 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
3089 END IF;
3090 RETURN;
3091
3092 WHEN FND_API.G_EXC_ERROR THEN
3093 x_Return_Status := FND_API.G_RET_STS_ERROR;
3094 IF (g_excep_level >= g_debug_level ) THEN
3095 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3096 END IF;
3097 RETURN;
3098
3099 WHEN OTHERS THEN
3100 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3101 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3102 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3103 END IF;
3104 IF ( g_unexp_level >= g_debug_level ) THEN
3105 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3106 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3107 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3108 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3109 END IF;
3110 RETURN;
3111
3112 END Archive_NON_MRC_Tbls;
3113
3114 --
3115 -- Build_Candidate_List Procedure to designed for retrieving the CC Header
3116 -- IDs that are candidates for Archiving and Purging. Once these values are
3117 -- retrieved then they will be inserted into the temporary table used for tracking
3118 -- the candidates for Archiving and Purging.
3119 --
3120 -- Parameters :
3121 --
3122 -- x_return_status ==> Status returned from Procedure.
3123 --
3124 PROCEDURE Build_Candidate_List (
3125 x_return_status OUT NOCOPY VARCHAR2
3126 ) IS
3127
3128 -- --------------------------------------------------------------------
3129 -- Define local variables to be used
3130 -- --------------------------------------------------------------------
3131 l_api_name CONSTANT VARCHAR2(30) := 'Build_Candidate_List';
3132 l_count NUMBER := 0;
3133
3134 -- --------------------------------------------------------------------
3135 -- Define cursor to get count inserted into table.
3136 -- --------------------------------------------------------------------
3137 CURSOR c_cand_count IS
3138 SELECT count(*)
3139 FROM igc_arc_pur_candidates;
3140
3141 l_full_path VARCHAR2(255);
3142
3143 BEGIN
3144
3145 l_full_path := g_path || 'Build_Candidate_List';
3146
3147 -- --------------------------------------------------------------------
3148 -- Initialize local variables.
3149 -- --------------------------------------------------------------------
3150 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3151
3152 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3153 IF (g_debug_mode = 'Y') THEN
3154 g_debug_msg := ' Building the Candidate List......';
3155 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3156 END IF;
3157
3158 -- --------------------------------------------------------------------
3159 -- Clean up the temp table of any records being present.
3160 -- --------------------------------------------------------------------
3161 DELETE
3162 FROM igc_arc_pur_candidates;
3163
3164 -- --------------------------------------------------------------------
3165 -- Insert all candidates that are closed / cancelled and all invoices
3166 -- that have been paid for the contract.
3167 -- --------------------------------------------------------------------
3168 INSERT
3169 INTO igc_arc_pur_candidates
3170 (cc_header_id,
3171 cc_num,
3172 cc_acct_line_id,
3173 cc_det_pf_line_id,
3174 last_activity_date
3175 )
3176 SELECT ICCCHV.cc_hd_id,
3177 ICCCHV.cc_num_val,
3178 ICCCHV.cc_act_id,
3179 ICCCHV.cc_pf_id,
3180 ICCCHV.max_dt
3181 FROM igc_cc_closed_canc_hdrs_v ICCCHV,
3182 igc_cc_headers_all ICH
3183 WHERE ICH.cc_header_id = ICCCHV.cc_hd_id
3184 AND ICH.set_of_books_id = g_sob_id
3185 AND ICH.org_id = g_org_id
3186 AND trunc (ICCCHV.max_dt) <= trunc (g_last_activity_date)
3187 AND ICH.cc_header_id NOT IN
3188 ( SELECT ICH1.cc_header_id
3189 FROM igc_cc_headers ICH1,
3190 po_headers_all PHA1,
3191 po_distributions_all PDA,
3192 ap_invoice_distributions_all AIDA,
3193 ap_invoices_all AIA1
3194 WHERE /*ICH1.set_of_books_id = g_sob_id
3195 AND ICH1.org_id = g_org_id
3196 AND --Commented during MOAC uptake */
3197 PHA1.segment1 = ICH1.cc_num
3198 AND PDA.po_header_id = PHA1.po_header_id
3199 AND AIDA.po_distribution_id = PDA.po_distribution_id
3200 AND AIA1.invoice_id = AIDA.invoice_id
3201 AND AIA1.payment_status_flag = 'N'
3202 AND AIA1.cancelled_date IS NULL
3203 );
3204
3205 IF (SQL%ROWCOUNT <= 0) THEN
3206 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3207 IF (g_debug_mode = 'Y') THEN
3208 g_debug_msg := ' No Candidates Found to be inserted into table......';
3209 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3210 END IF;
3211
3212 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
3213 p_tokval => g_sob_id);
3214 IGC_MSGS_PKG.message_token (p_tokname => 'INPUT_DATE',
3215 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
3216 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
3217 p_msgname => 'IGC_NO_ARC_PUR_CANDIDATES');
3218 g_validation_error := TRUE;
3219 ELSE
3220
3221 -- --------------------------------------------------------------------
3222 -- Now that the candidates have been chosen ensure that all the releases
3223 -- for each cover are closed / cancelled. Remove all cover candidates
3224 -- chosen from above that are not closed / cancelled.
3225 -- --------------------------------------------------------------------
3226 DELETE
3227 FROM igc_arc_pur_candidates
3228 WHERE cc_header_id IN
3229 ( SELECT parent_header_id
3230 FROM igc_cc_headers
3231 WHERE parent_header_id IS NOT NULL
3232 AND cc_header_id NOT IN
3233 ( SELECT cc_header_id
3234 FROM igc_arc_pur_candidates
3235 )
3236 );
3237
3238 -- --------------------------------------------------------------------
3239 -- Now that the candidates have been chosen ensure that all the releases
3240 -- for each cover are closed / cancelled. Remove all release candidates
3241 -- chosen from above that are not closed / cancelled.
3242 -- --------------------------------------------------------------------
3243 DELETE
3244 FROM igc_arc_pur_candidates
3245 WHERE cc_header_id IN
3246 ( SELECT cc_header_id
3247 FROM igc_cc_headers
3248 WHERE parent_header_id IS NOT NULL
3249 AND parent_header_id NOT IN
3250 ( SELECT cc_header_id
3251 FROM igc_arc_pur_candidates
3252 )
3253 );
3254
3255 -- --------------------------------------------------------------------
3256 -- Validate that there are candidates to be archived / purged from the
3257 -- system. If there are none then exit procedure with failure.
3258 -- --------------------------------------------------------------------
3259 OPEN c_cand_count;
3260 FETCH c_cand_count
3261 INTO l_count;
3262
3263 CLOSE c_cand_count;
3264
3265 IF (l_count <= 0) THEN
3266 g_validation_error := TRUE;
3267 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3268 IF (g_debug_mode = 'Y') THEN
3269 g_debug_msg := ' No Candidates Found to be inserted into table......';
3270 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3271 END IF;
3272 END IF;
3273
3274 END IF;
3275
3276 RETURN;
3277
3278 -- --------------------------------------------------------------------
3279 -- Exception handler section for the Build_Candidate_List procedure.
3280 -- --------------------------------------------------------------------
3281 EXCEPTION
3282
3283 WHEN NO_DATA_FOUND THEN
3284 x_Return_Status := FND_API.G_RET_STS_ERROR;
3285 IF (c_cand_count%ISOPEN) THEN
3286 CLOSE c_cand_count;
3287 END IF;
3288 IF (g_excep_level >= g_debug_level ) THEN
3289 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
3290 END IF;
3291 RETURN;
3292
3293 WHEN FND_API.G_EXC_ERROR THEN
3294 x_Return_Status := FND_API.G_RET_STS_ERROR;
3295 IF (c_cand_count%ISOPEN) THEN
3296 CLOSE c_cand_count;
3297 END IF;
3298 IF (g_excep_level >= g_debug_level ) THEN
3299 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3300 END IF;
3301 RETURN;
3302
3303 WHEN OTHERS THEN
3304 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3305 IF (c_cand_count%ISOPEN) THEN
3306 CLOSE c_cand_count;
3307 END IF;
3308 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3309 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3310 END IF;
3311 IF ( g_unexp_level >= g_debug_level ) THEN
3312 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3313 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3314 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3315 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3316 END IF;
3317 RETURN;
3318
3319 END Build_Candidate_List;
3320
3321 --
3322 -- Check_MRC Procedure to determine if MRC is installed/enabled.
3323 --
3324 -- Parameters :
3325 --
3326 -- x_return_status ==> Status returned from Procedure.
3327 --
3328 PROCEDURE Check_MRC (
3329 x_return_status OUT NOCOPY VARCHAR2
3330 ) IS
3331
3332 -- --------------------------------------------------------------------
3333 -- Define local variables to be used
3334 -- --------------------------------------------------------------------
3335 l_api_name CONSTANT VARCHAR2(30) := 'Check_MRC';
3336 l_full_path VARCHAR2(255);
3337 l_igc_application_id NUMBER;
3338
3339 BEGIN
3340
3341 l_full_path := g_path || 'Check_MRC';
3342
3343 -- --------------------------------------------------------------------
3344 -- Initialize local variables.
3345 -- --------------------------------------------------------------------
3346 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3347
3348 -- --------------------------------------------------------------------
3349 -- Get the information to determine if MRC is installed and enabled for
3350 -- the set_of_books_id given
3351 -- --------------------------------------------------------------------
3352 -- Replaced the call to mrc_isntalled with call to mrc_enabled
3353 -- for Bug 3448645
3354 -- gl_mc_info.mrc_installed ( mrc_install => g_mrc_installed );
3355
3356 SELECT application_id
3357 INTO l_igc_application_id
3358 FROM fnd_application
3359 WHERE application_short_name = 'IGC';
3360
3361 gl_mc_info.mrc_enabled (n_sob_id => g_sob_id,
3362 n_appl_id => l_igc_application_id,
3363 n_org_id => g_org_id,
3364 n_fa_book_code => NULL,
3365 n_mrc_enabled => g_mrc_installed);
3366
3367 IF (g_mrc_installed <> 'Y') THEN
3368 g_mrc_installed := 'N';
3369 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3370 IF (g_debug_mode = 'Y') THEN
3371 g_debug_msg := ' MRC is NOT installed thus NOT enabled';
3372 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3373 END IF;
3374 ELSE
3375 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3376 IF (g_debug_mode = 'Y') THEN
3377 g_debug_msg := ' MRC is installed so will be checking for ENABLED.';
3378 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3379 END IF;
3380 END IF;
3381
3382 RETURN;
3383
3384 -- --------------------------------------------------------------------
3385 -- Exception handler section for the Check_MRC procedure.
3386 -- --------------------------------------------------------------------
3387 EXCEPTION
3388
3389 WHEN NO_DATA_FOUND THEN
3390 x_Return_Status := FND_API.G_RET_STS_ERROR;
3391 IF (g_excep_level >= g_debug_level ) THEN
3392 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
3393 END IF;
3394 RETURN;
3395
3396 WHEN FND_API.G_EXC_ERROR THEN
3397 x_Return_Status := FND_API.G_RET_STS_ERROR;
3398 IF (g_excep_level >= g_debug_level ) THEN
3399 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3400 END IF;
3401 RETURN;
3402
3403 WHEN OTHERS THEN
3404 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3405 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3406 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3407 END IF;
3408 IF ( g_unexp_level >= g_debug_level ) THEN
3409 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3410 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3411 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3412 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3413 END IF;
3414 RETURN;
3415
3416 END Check_MRC;
3417
3418 --
3419 -- Cleanup_MRC_Arc_Tbls Procedure to cleanup any data that may be present for the
3420 -- last activity date entered by the user.
3421 --
3422 -- Parameters :
3423 --
3424 -- x_return_status ==> Status returned from Procedure.
3425 --
3426 PROCEDURE Cleanup_MRC_Arc_Tbls (
3427 x_return_status OUT NOCOPY VARCHAR2
3428 ) IS
3429
3430 -- --------------------------------------------------------------------
3431 -- Define local variables to be used
3432 -- --------------------------------------------------------------------
3433 l_api_name CONSTANT VARCHAR2(30) := 'Cleanup_MRC_Arc_Tbls';
3434 l_full_path VARCHAR2(255);
3435
3436 BEGIN
3437
3438 l_full_path := g_path || 'Cleanup_MRC_Arc_Tbls';
3439
3440 -- --------------------------------------------------------------------
3441 -- Initialize local variables.
3442 -- --------------------------------------------------------------------
3443 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3444
3445 -- --------------------------------------------------------------------
3446 -- Delete MRC Archive Header records before adding again.
3447 -- --------------------------------------------------------------------
3448 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3449 IF (g_debug_mode = 'Y') THEN
3450 g_debug_msg := ' Deleting records from Archive MRC Headers table for' ||
3451 ' Last Activity Date : ' || g_last_activity_date;
3452 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3453 END IF;
3454
3455 DELETE
3456 FROM igc_cc_arc_mc_headers CMH
3457 WHERE CMH.cc_header_id IN
3458 ( SELECT ICV.cc_header_id
3459 FROM igc_arc_pur_candidates ICV
3460 );
3461
3462 -- --------------------------------------------------------------------
3463 -- Delete MRC Archive Account Line records before adding again.
3464 -- --------------------------------------------------------------------
3465 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3466 IF (g_debug_mode = 'Y') THEN
3467 g_debug_msg := ' Deleting records from Archive MRC Acct Lines table for' ||
3468 ' Last Activity Date : ' || g_last_activity_date;
3469 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3470 END IF;
3471
3472 DELETE
3473 FROM igc_cc_arc_mc_acct_lines CML
3474 WHERE CML.cc_acct_line_id IN
3475 ( SELECT ACLH.cc_acct_line_id
3476 FROM igc_cc_acct_line_history ACLH
3477 WHERE ACLH.cc_header_id IN
3478 ( SELECT ICV1.cc_header_id
3479 FROM igc_arc_pur_candidates ICV1
3480 )
3481 )
3482 OR CML.cc_acct_line_id IN
3483 ( SELECT ACL.cc_acct_line_id
3484 FROM igc_cc_acct_lines ACL
3485 WHERE ACL.cc_header_id IN
3486 ( SELECT ICV2.cc_header_id
3487 FROM igc_arc_pur_candidates ICV2
3488 )
3489 );
3490
3491 -- --------------------------------------------------------------------
3492 -- Delete MRC Archive Detail Payment Forcast records before adding again.
3493 -- --------------------------------------------------------------------
3494 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3495 IF (g_debug_mode = 'Y') THEN
3496 g_debug_msg := ' Deleting records from Archive MRC DET PF table for' ||
3497 ' Last Activity Date : ' || g_last_activity_date;
3498 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3499 END IF;
3500
3501 DELETE
3502 FROM igc_cc_arc_mc_det_pf CMP
3503 WHERE CMP.cc_det_pf_line_id IN
3504 ( SELECT DPFH.cc_det_pf_line_id
3505 FROM igc_cc_det_pf_history DPFH
3506 WHERE DPFH.cc_acct_line_id IN
3507 ( SELECT ACLH.cc_acct_line_id
3508 FROM igc_cc_acct_line_history ACLH
3509 WHERE ACLH.cc_header_id IN
3510 ( SELECT ICV1.cc_header_id
3511 FROM igc_arc_pur_candidates ICV1
3512 )
3513 )
3514 OR DPFH.cc_acct_line_id IN
3515 ( SELECT ACL.cc_acct_line_id
3516 FROM igc_cc_acct_lines ACL
3517 WHERE ACL.cc_header_id IN
3518 ( SELECT ICV2.cc_header_id
3519 FROM igc_arc_pur_candidates ICV2
3520 )
3521 )
3522 )
3523 OR CMP.cc_det_pf_line_id IN
3524 ( SELECT DPF.cc_det_pf_line_id
3525 FROM igc_cc_det_pf DPF
3526 WHERE DPF.cc_acct_line_id IN
3527 ( SELECT ACLH.cc_acct_line_id
3528 FROM igc_cc_acct_line_history ACLH
3529 WHERE ACLH.cc_header_id IN
3530 ( SELECT ICV1.cc_header_id
3531 FROM igc_arc_pur_candidates ICV1
3532 )
3533 )
3534 OR DPF.cc_acct_line_id IN
3535 ( SELECT ACL.cc_acct_line_id
3536 FROM igc_cc_acct_lines ACL
3537 WHERE ACL.cc_header_id IN
3538 ( SELECT ICV2.cc_header_id
3539 FROM igc_arc_pur_candidates ICV2
3540 )
3541 )
3542 );
3543
3544 -- --------------------------------------------------------------------
3545 -- Delete MRC Archive Header History records before adding again.
3546 -- --------------------------------------------------------------------
3547 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3548 IF (g_debug_mode = 'Y') THEN
3549 g_debug_msg := ' Deleting records from Archive MRC Header History table for' ||
3550 ' Last Activity Date : ' || g_last_activity_date;
3551 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3552 END IF;
3553
3554 DELETE
3555 FROM igc_cc_arc_mc_header_hist CMHH
3556 WHERE CMHH.cc_header_id IN
3557 ( SELECT ICV.cc_header_id
3558 FROM igc_arc_pur_candidates ICV
3559 );
3560
3561 -- --------------------------------------------------------------------
3562 -- Delete MRC Archive Account Line History records before adding again.
3563 -- --------------------------------------------------------------------
3564 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3565 IF (g_debug_mode = 'Y') THEN
3566 g_debug_msg := ' Deleting records from Archive MRC Acct Line History table for' ||
3567 ' Last Activity Date : ' || g_last_activity_date;
3568 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3569 END IF;
3570
3571 DELETE
3572 FROM igc_cc_arc_mc_acct_line_hist CMLH
3573 WHERE CMLH.cc_acct_line_id IN
3574 ( SELECT ACLH.cc_acct_line_id
3575 FROM igc_cc_acct_line_history ACLH
3576 WHERE ACLH.cc_header_id IN
3577 ( SELECT ICV1.cc_header_id
3578 FROM igc_arc_pur_candidates ICV1
3579 )
3580 )
3581 OR CMLH.cc_acct_line_id IN
3582 ( SELECT ACL.cc_acct_line_id
3583 FROM igc_cc_acct_lines ACL
3584 WHERE ACL.cc_header_id IN
3585 ( SELECT ICV2.cc_header_id
3586 FROM igc_arc_pur_candidates ICV2
3587 )
3588 );
3589
3590 -- --------------------------------------------------------------------
3591 -- Delete MRC Archive Payment Forcast History records before adding again.
3592 -- --------------------------------------------------------------------
3593 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3594 IF (g_debug_mode = 'Y') THEN
3595 g_debug_msg := ' Deleting records from Archive MRC DET PF History table for' ||
3596 ' Last Activity Date : ' || g_last_activity_date;
3597 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3598 END IF;
3599
3600 DELETE
3601 FROM igc_cc_arc_mc_det_pf_hist CMPH
3602 WHERE CMPH.cc_det_pf_line_id IN
3603 ( SELECT DPFH.cc_det_pf_line_id
3604 FROM igc_cc_det_pf_history DPFH
3605 WHERE DPFH.cc_acct_line_id IN
3606 ( SELECT ACLH.cc_acct_line_id
3607 FROM igc_cc_acct_line_history ACLH
3608 WHERE ACLH.cc_header_id IN
3609 ( SELECT ICV1.cc_header_id
3610 FROM igc_arc_pur_candidates ICV1
3611 )
3612 )
3613 OR DPFH.cc_acct_line_id IN
3614 ( SELECT ACL.cc_acct_line_id
3615 FROM igc_cc_acct_lines ACL
3616 WHERE ACL.cc_header_id IN
3617 ( SELECT ICV2.cc_header_id
3618 FROM igc_arc_pur_candidates ICV2
3619 )
3620 )
3621 )
3622 OR CMPH.cc_det_pf_line_id IN
3623 ( SELECT DPF.cc_det_pf_line_id
3624 FROM igc_cc_det_pf DPF
3625 WHERE DPF.cc_acct_line_id IN
3626 ( SELECT ACLH.cc_acct_line_id
3627 FROM igc_cc_acct_line_history ACLH
3628 WHERE ACLH.cc_header_id IN
3629 ( SELECT ICV1.cc_header_id
3630 FROM igc_arc_pur_candidates ICV1
3631 )
3632 )
3633 OR DPF.cc_acct_line_id IN
3634 ( SELECT ACL.cc_acct_line_id
3635 FROM igc_cc_acct_lines ACL
3636 WHERE ACL.cc_header_id IN
3637 ( SELECT ICV2.cc_header_id
3638 FROM igc_arc_pur_candidates ICV2
3639 )
3640 )
3641 );
3642
3643 RETURN;
3644
3645 -- --------------------------------------------------------------------
3646 -- Exception handler section for the Cleanup_MRC_Arc_Tbls procedure.
3647 -- --------------------------------------------------------------------
3648 EXCEPTION
3649
3650 WHEN NO_DATA_FOUND THEN
3651 x_Return_Status := FND_API.G_RET_STS_ERROR;
3652 IF (g_excep_level >= g_debug_level ) THEN
3653 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
3654 END IF;
3655 RETURN;
3656
3657 WHEN FND_API.G_EXC_ERROR THEN
3658 x_Return_Status := FND_API.G_RET_STS_ERROR;
3659 IF (g_excep_level >= g_debug_level ) THEN
3660 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3661 END IF;
3662 RETURN;
3663
3664 WHEN OTHERS THEN
3665 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3666 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3667 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3668 END IF;
3669 IF ( g_unexp_level >= g_debug_level ) THEN
3670 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3671 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3672 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3673 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3674 END IF;
3675 RETURN;
3676
3677 END Cleanup_MRC_Arc_Tbls;
3678
3679 --
3680 -- Cleanup_NON_MRC_Arc_Tbls Procedure to cleanup any data that may be present in
3681 -- the tables that needs to be removed before adding the same records.
3682 --
3683 -- Parameters :
3684 --
3685 -- x_return_status ==> Status returned from Procedure.
3686 --
3687 PROCEDURE Cleanup_NON_MRC_Arc_Tbls (
3688 x_return_status OUT NOCOPY VARCHAR2
3689 ) IS
3690
3691 -- --------------------------------------------------------------------
3692 -- Define local variables to be used
3693 -- --------------------------------------------------------------------
3694 l_api_name CONSTANT VARCHAR2(30) := 'Cleanup_NON_MRC_Arc_Tbls';
3695 l_full_path VARCHAR2(255);
3696
3697 BEGIN
3698
3699 l_full_path := g_path || 'Cleanup_NON_MRC_Arc_Tbls';
3700
3701 -- --------------------------------------------------------------------
3702 -- Initialize local variables.
3703 -- --------------------------------------------------------------------
3704 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3705
3706 -- --------------------------------------------------------------------
3707 -- Delete Archive Header records before adding again.
3708 -- --------------------------------------------------------------------
3709 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3710 IF (g_debug_mode = 'Y') THEN
3711 g_debug_msg := ' Deleting records from Archive Headers table for' ||
3712 ' Last Activity Date : ' || g_last_activity_date;
3713 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3714 END IF;
3715
3716 DELETE
3717 FROM igc_cc_arc_headers_all CMH
3718 WHERE CMH.cc_header_id IN
3719 ( SELECT ICV.cc_header_id
3720 FROM igc_arc_pur_candidates ICV
3721 );
3722
3723 -- --------------------------------------------------------------------
3724 -- Delete Archive Header History records before adding again.
3725 -- --------------------------------------------------------------------
3726 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3727 IF (g_debug_mode = 'Y') THEN
3728 g_debug_msg := ' Deleting records from Archive Headers History table for' ||
3729 ' Last Activity Date : ' || g_last_activity_date;
3730 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3731 END IF;
3732
3733 DELETE
3734 FROM igc_cc_arc_header_hist_all CAHH
3735 WHERE CAHH.cc_header_id IN
3736 ( SELECT ICV.cc_header_id
3737 FROM igc_arc_pur_candidates ICV
3738 );
3739
3740 -- --------------------------------------------------------------------
3741 -- Delete Archive Account Line records before adding again.
3742 -- --------------------------------------------------------------------
3743 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3744 IF (g_debug_mode = 'Y') THEN
3745 g_debug_msg := ' Deleting records from Archive Acct Lines table for' ||
3746 ' Last Activity Date : ' || g_last_activity_date;
3747 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3748 END IF;
3749
3750 DELETE
3751 FROM igc_cc_arc_acct_lines CML
3752 WHERE CML.cc_header_id IN
3753 ( SELECT ICV.cc_header_id
3754 FROM igc_arc_pur_candidates ICV
3755 );
3756
3757 -- --------------------------------------------------------------------
3758 -- Delete Archive Account Line History records before adding again.
3759 -- --------------------------------------------------------------------
3760 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3761 IF (g_debug_mode = 'Y') THEN
3762 g_debug_msg := ' Deleting records from Archive Acct Lines History table for' ||
3763 ' Last Activity Date : ' || g_last_activity_date;
3764 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3765 END IF;
3766
3767 DELETE
3768 FROM igc_cc_arc_acct_line_hist CML
3769 WHERE CML.cc_header_id IN
3770 ( SELECT ICV.cc_header_id
3771 FROM igc_arc_pur_candidates ICV
3772 );
3773
3774 -- --------------------------------------------------------------------
3775 -- Delete Archive Payment Forcast records before adding again.
3776 -- --------------------------------------------------------------------
3777 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3778 IF (g_debug_mode = 'Y') THEN
3779 g_debug_msg := ' Deleting records from Archive MRC DET PF table for' ||
3780 ' Last Activity Date : ' || g_last_activity_date;
3781 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3782 END IF;
3783
3784 DELETE
3785 FROM igc_cc_arc_det_pf CDP
3786 WHERE CDP.cc_acct_line_id IN
3787 ( SELECT ACLH.cc_acct_line_id
3788 FROM igc_cc_acct_line_history ACLH
3789 WHERE ACLH.cc_header_id IN
3790 ( SELECT ICV1.cc_header_id
3791 FROM igc_arc_pur_candidates ICV1
3792 )
3793 )
3794 OR CDP.cc_acct_line_id IN
3795 ( SELECT ACL.cc_acct_line_id
3796 FROM igc_cc_acct_lines ACL
3797 WHERE ACL.cc_header_id IN
3798 ( SELECT ICV2.cc_header_id
3799 FROM igc_arc_pur_candidates ICV2
3800 )
3801 );
3802
3803 -- --------------------------------------------------------------------
3804 -- Delete Archive Payment Forcast History records before adding again.
3805 -- --------------------------------------------------------------------
3806 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3807 IF (g_debug_mode = 'Y') THEN
3808 g_debug_msg := ' Deleting records from Archive MRC DET PF History table for' ||
3809 ' Last Activity Date : ' || g_last_activity_date;
3810 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3811 END IF;
3812
3813 DELETE
3814 FROM igc_cc_arc_det_pf_hist CDPH
3815 WHERE CDPH.cc_acct_line_id IN
3816 ( SELECT ACLH.cc_acct_line_id
3817 FROM igc_cc_acct_line_history ACLH
3818 WHERE ACLH.cc_header_id IN
3819 ( SELECT ICV1.cc_header_id
3820 FROM igc_arc_pur_candidates ICV1
3821 )
3822 )
3823 OR CDPH.cc_acct_line_id IN
3824 ( SELECT ACL.cc_acct_line_id
3825 FROM igc_cc_acct_lines ACL
3826 WHERE ACL.cc_header_id IN
3827 ( SELECT ICV1.cc_header_id
3828 FROM igc_arc_pur_candidates ICV1
3829 )
3830 );
3831
3832 -- --------------------------------------------------------------------
3833 -- Delete Archive Action records before adding again.
3834 -- --------------------------------------------------------------------
3835 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3836 IF (g_debug_mode = 'Y') THEN
3837 g_debug_msg := ' Deleting records from Archive Actions table for' ||
3838 ' Last Activity Date : ' || g_last_activity_date;
3839 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3840 END IF;
3841
3842 DELETE
3843 FROM igc_cc_arc_actions CAA
3844 WHERE CAA.cc_header_id IN
3845 ( SELECT ICV.cc_header_id
3846 FROM igc_arc_pur_candidates ICV
3847 );
3848
3849 -- --------------------------------------------------------------------
3850 -- Delete Archived PO Header records that may exist
3851 -- --------------------------------------------------------------------
3852 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3853 IF (g_debug_mode = 'Y') THEN
3854 g_debug_msg := ' Deleting records from Archive PO Headers table for' ||
3855 ' Last Activity Date : ' || g_last_activity_date;
3856 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3857 END IF;
3858
3859 DELETE
3860 FROM igc_cc_arc_po_headers IPHA
3861 WHERE IPHA.po_header_id IN
3862 ( SELECT PHA1.po_header_id
3863 FROM po_headers_all PHA1
3864 WHERE PHA1.segment1 IN
3865 ( SELECT ICV.cc_num
3866 FROM igc_arc_pur_candidates ICV
3867 )
3868 );
3869
3870 -- --------------------------------------------------------------------
3871 -- Delete Archived PO Line records that may exist
3872 -- --------------------------------------------------------------------
3873 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3874 IF (g_debug_mode = 'Y') THEN
3875 g_debug_msg := ' Deleting records from Archive PO Lines table for' ||
3876 ' Last Activity Date : ' || g_last_activity_date;
3877 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3878 END IF;
3879
3880 DELETE
3881 FROM igc_cc_arc_po_lines IPLA
3882 WHERE IPLA.po_header_id IN
3883 ( SELECT PHA.po_header_id
3884 FROM po_headers_all PHA
3885 WHERE PHA.segment1 IN
3886 ( SELECT ICV.cc_num
3887 FROM igc_arc_pur_candidates ICV
3888 )
3889 );
3890
3891 -- --------------------------------------------------------------------
3892 -- Delete Archived PO Line Location records that may exist
3893 -- --------------------------------------------------------------------
3894 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3895 IF (g_debug_mode = 'Y') THEN
3896 g_debug_msg := ' Deleting records from Archive PO Line Locations table for' ||
3897 ' Last Activity Date : ' || g_last_activity_date;
3898 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3899 END IF;
3900
3901 DELETE
3902 FROM igc_cc_arc_po_line_loc IPLLA
3903 WHERE IPLLA.po_header_id IN
3904 ( SELECT PHA.po_header_id
3905 FROM po_headers_all PHA
3906 WHERE PHA.segment1 IN
3907 ( SELECT ICV.cc_num
3908 FROM igc_arc_pur_candidates ICV
3909 )
3910 );
3911
3912 -- --------------------------------------------------------------------
3913 -- Delete Archived PO Distribution records that may exist
3914 -- --------------------------------------------------------------------
3915 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
3916 IF (g_debug_mode = 'Y') THEN
3917 g_debug_msg := ' Deleting records from Archive PO Distributions table for' ||
3918 ' Last Activity Date : ' || g_last_activity_date;
3919 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
3920 END IF;
3921
3922 DELETE
3923 FROM igc_cc_arc_po_distribution IPDA
3924 WHERE IPDA.po_header_id IN
3925 ( SELECT PHA.po_header_id
3926 FROM po_headers_all PHA
3927 WHERE PHA.segment1 IN
3928 ( SELECT ICV.cc_num
3929 FROM igc_arc_pur_candidates ICV
3930 )
3931 );
3932
3933 RETURN;
3934
3935 -- --------------------------------------------------------------------
3936 -- Exception handler section for the Cleanup_NON_MRC_Arc_Tbls procedure.
3937 -- --------------------------------------------------------------------
3938 EXCEPTION
3939
3940 WHEN NO_DATA_FOUND THEN
3941 x_Return_Status := FND_API.G_RET_STS_ERROR;
3942 IF (g_excep_level >= g_debug_level ) THEN
3943 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
3944 END IF;
3945 RETURN;
3946
3947 WHEN FND_API.G_EXC_ERROR THEN
3948 x_Return_Status := FND_API.G_RET_STS_ERROR;
3949 IF (g_excep_level >= g_debug_level ) THEN
3950 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
3951 END IF;
3952 RETURN;
3953
3954 WHEN OTHERS THEN
3955 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3956 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3957 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3958 END IF;
3959 IF ( g_unexp_level >= g_debug_level ) THEN
3960 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3961 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3962 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3963 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3964 END IF;
3965 RETURN;
3966
3967 END Cleanup_NON_MRC_Arc_Tbls;
3968
3969 --
3970 -- Initializing History Record procedure that will setup the record that is to eventually
3971 -- be inserted or updated in the Archive History Table.
3972 --
3973 -- Parameters :
3974 --
3975 -- p_cc_header_id ==> CC Header ID to be archived / purged and added to the History Record
3976 -- p_History_Rec ==> Record that is to be returned initialized for IGC_CC_ARCHIVE_HISTORY table
3977 -- x_Return_Status ==> Status of procedure returned to caller
3978 --
3979 PROCEDURE Initialize_History_Record (
3980 p_cc_header_id IN igc_cc_archive_history.cc_header_id%TYPE,
3981 p_History_Rec IN OUT NOCOPY igc_cc_archive_history%ROWTYPE,
3982 x_Return_Status OUT NOCOPY VARCHAR2
3983 ) IS
3984
3985 l_api_name CONSTANT VARCHAR2(30) := 'Initialize_History_Record';
3986 l_head_id igc_cc_archive_history.cc_header_id%TYPE;
3987 l_sob_id igc_cc_archive_history.set_of_books_id%TYPE;
3988 l_org_id igc_cc_archive_history.org_id%TYPE;
3989 l_parent_id igc_cc_archive_history.parent_header_id%TYPE;
3990 l_cc_type igc_cc_archive_history.cc_type%TYPE;
3991 l_lines_arc igc_cc_archive_history.num_acct_lines_arc%TYPE;
3992 l_pf_lines_arc igc_cc_archive_history.num_det_pf_lines_arc%TYPE;
3993 l_mc_lines_arc igc_cc_archive_history.num_mc_acct_lines_arc%TYPE;
3994 l_mc_pf_lines_arc igc_cc_archive_history.num_mc_det_pf_lines_arc%TYPE;
3995 l_archive_date igc_cc_archive_history.archive_date%TYPE;
3996 l_update_by igc_cc_archive_history.last_updated_by%TYPE;
3997 l_archive_done igc_cc_archive_history.archive_done_flag%TYPE;
3998 l_last_activity_date igc_cc_archive_history.user_req_last_activity_date%TYPE;
3999 l_created_by igc_cc_archive_history.created_by%TYPE;
4000 l_created_date igc_cc_archive_history.creation_date%TYPE;
4001
4002 -- --------------------------------------------------------------------
4003 -- Declare cursors to be used by this procedure.
4004 -- --------------------------------------------------------------------
4005 CURSOR c_archive_history IS
4006 SELECT cc_header_id,
4007 set_of_books_id,
4008 org_id,
4009 parent_header_id,
4010 cc_num,
4011 cc_type,
4012 num_acct_lines_arc,
4013 num_det_pf_lines_arc,
4014 num_mc_acct_lines_arc,
4015 num_mc_det_pf_lines_arc,
4016 archive_date,
4017 last_updated_by,
4018 archive_done_flag,
4019 user_req_last_activity_date,
4020 created_by,
4021 creation_date
4022 FROM IGC_CC_ARCHIVE_HISTORY CAH
4023 WHERE CAH.cc_header_id = p_cc_header_id
4024 AND CAH.org_id = g_org_id
4025 AND CAH.set_of_books_id = g_sob_id;
4026
4027 l_full_path VARCHAR2(255);
4028
4029 BEGIN
4030
4031 l_full_path := g_path || 'Initialize_History_Record';
4032
4033 -- --------------------------------------------------------------------
4034 -- Initialize Return status and other local variables.
4035 -- --------------------------------------------------------------------
4036 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4037
4038 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4039 IF (g_debug_mode = 'Y') THEN
4040 g_debug_msg := ' Initializing History Record for Archive / Purge Process';
4041 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4042 END IF;
4043
4044 OPEN c_archive_history;
4045 FETCH c_archive_history
4046 INTO l_head_id,
4047 l_sob_id,
4048 l_org_id,
4049 l_parent_id,
4050 g_cc_num,
4051 l_cc_type,
4052 l_lines_arc,
4053 l_pf_lines_arc,
4054 l_mc_lines_arc,
4055 l_mc_pf_lines_arc,
4056 l_archive_date,
4057 l_update_by,
4058 l_archive_done,
4059 l_last_activity_date,
4060 l_created_by,
4061 l_created_date;
4062
4063 p_History_Rec.set_of_books_id := g_sob_id;
4064 p_History_Rec.org_id := g_org_id;
4065 p_History_Rec.last_update_date := SYSDATE;
4066 p_History_Rec.last_updated_by := g_update_by;
4067 p_History_Rec.last_update_login := g_update_by;
4068
4069 IF (c_archive_history%FOUND) THEN
4070
4071 p_History_Rec.cc_header_id := l_head_id;
4072 p_History_Rec.parent_header_id := l_parent_id;
4073 p_History_Rec.cc_num := g_cc_num;
4074 p_History_Rec.cc_type := l_cc_type;
4075 p_History_Rec.user_req_last_activity_date := l_last_activity_date;
4076 p_History_Rec.last_cc_activity_date := NULL;
4077 p_History_Rec.created_by := l_created_by;
4078 p_History_Rec.creation_date := l_created_date;
4079
4080 IF (g_mode = 'AR') THEN
4081 p_History_Rec.num_acct_lines_arc := 0;
4082 p_History_Rec.num_det_pf_lines_arc := 0;
4083 p_History_Rec.num_mc_acct_lines_arc := 0;
4084 p_History_Rec.num_mc_det_pf_lines_arc := 0;
4085 p_History_Rec.archive_date := SYSDATE;
4086 p_History_Rec.archived_by := g_update_by;
4087 p_History_Rec.archive_done_flag := 'N';
4088 p_History_Rec.purge_date := NULL;
4089 p_History_Rec.purged_by := NULL;
4090 p_History_Rec.purge_done_flag := 'N';
4091 ELSE
4092 p_History_Rec.num_acct_lines_arc := l_lines_arc;
4093 p_History_Rec.num_det_pf_lines_arc := l_pf_lines_arc;
4094 p_History_Rec.num_mc_acct_lines_arc := l_mc_lines_arc;
4095 p_History_Rec.num_mc_det_pf_lines_arc := l_mc_pf_lines_arc;
4096 p_History_Rec.archive_date := l_archive_date;
4097 p_History_Rec.archived_by := l_update_by;
4098 p_History_Rec.archive_done_flag := l_archive_done;
4099 p_History_Rec.purge_date := SYSDATE;
4100 p_History_Rec.purged_by := g_update_by;
4101 p_History_Rec.purge_done_flag := 'N';
4102 END IF;
4103
4104 ELSE
4105
4106 IF (g_mode = 'AR') THEN
4107
4108 p_History_Rec.cc_header_id := p_cc_header_id;
4109 p_History_Rec.parent_header_id := NULL;
4110 p_History_Rec.cc_num := NULL;
4111 p_History_Rec.cc_type := NULL;
4112 p_History_Rec.num_acct_lines_arc := 0;
4113 p_History_Rec.num_det_pf_lines_arc := 0;
4114 p_History_Rec.num_mc_acct_lines_arc := 0;
4115 p_History_Rec.num_mc_det_pf_lines_arc := 0;
4116 p_History_Rec.archive_date := SYSDATE;
4117 p_History_Rec.archived_by := g_update_by;
4118 p_History_Rec.archive_done_flag := 'N';
4119 p_History_Rec.purge_date := NULL;
4120 p_History_Rec.purged_by := NULL;
4121 p_History_Rec.purge_done_flag := 'N';
4122 p_History_Rec.last_cc_activity_date := NULL;
4123 p_History_Rec.user_req_last_activity_date := g_last_activity_date;
4124 p_History_Rec.created_by := g_update_by;
4125 p_History_Rec.creation_date := SYSDATE;
4126
4127 ELSE
4128
4129 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4130 IF (g_debug_mode = 'Y') THEN
4131 g_debug_msg := ' Not allowed to initialize history record as not found in History Table';
4132 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4133 g_debug_msg := ' and activity is not archive. CC Header id : ' || p_cc_header_id;
4134 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4135 END IF;
4136
4137 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
4138 p_tokval => g_sob_id);
4139 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE1',
4140 p_tokval => p_cc_header_id);
4141 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE2',
4142 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
4143 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
4144 p_tokval => 'CC');
4145 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
4146 p_msgname => 'IGC_NUM_PUR_NO_MATCH_ARC');
4147 raise FND_API.G_EXC_ERROR;
4148
4149 END IF;
4150
4151 END IF;
4152
4153 CLOSE c_archive_history;
4154
4155 RETURN;
4156
4157 -- --------------------------------------------------------------------
4158 -- Exception handler section for the Initialize_History_Record procedure.
4159 -- --------------------------------------------------------------------
4160 EXCEPTION
4161
4162 WHEN OTHERS THEN
4163 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4164 IF (c_archive_history%ISOPEN) THEN
4165 CLOSE c_archive_history;
4166 END IF;
4167 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4168 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4169 END IF;
4170 IF ( g_unexp_level >= g_debug_level ) THEN
4171 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4172 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4173 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4174 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4175 END IF;
4176 RETURN;
4177
4178 END Initialize_History_Record;
4179
4180
4181 --
4182 -- Insert_Archive_History Procedure is designed to ensure that there are history records
4183 -- created for all the records that have been archived based upon the last activity date
4184 -- given by the user
4185 --
4186 -- Parameters :
4187 --
4188 -- x_Return_Status ==> Status of the procedure returned to caller.
4189 --
4190 PROCEDURE Insert_Archive_History (
4191 x_Return_Status OUT NOCOPY VARCHAR2
4192 ) IS
4193
4194 -- --------------------------------------------------------------------
4195 -- Declare local variables to be used in this procedure.
4196 -- --------------------------------------------------------------------
4197 l_return_status VARCHAR2(1);
4198 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
4199 l_cc_type igc_cc_headers.cc_type%TYPE;
4200 l_parent_id igc_cc_headers.parent_header_id%TYPE;
4201 l_history_rec igc_cc_archive_history%ROWTYPE;
4202 l_max_date igc_arc_pur_candidates.last_activity_date%TYPE;
4203
4204 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Archive_History';
4205
4206 -- --------------------------------------------------------------------
4207 -- Declare cursors to be used in this procedure.
4208 -- --------------------------------------------------------------------
4209 CURSOR c_get_archived_hdrs IS
4210 SELECT distinct (cc_header_id),
4211 cc_num,
4212 last_activity_date
4213 FROM igc_arc_pur_candidates;
4214
4215 CURSOR c_cc_header_info IS
4216 SELECT cc_header_id,
4217 cc_num,
4218 cc_type,
4219 parent_header_id
4220 FROM igc_cc_headers
4221 WHERE cc_header_id = l_cc_header_id;
4222 /*AND set_of_books_id = g_sob_id
4223 AND org_id = g_org_id; --Commented during MOAC uptake*/
4224
4225 CURSOR c_acct_line_count IS
4226 SELECT count(*)
4227 FROM igc_cc_arc_acct_lines
4228 WHERE cc_header_id = l_cc_header_id;
4229
4230 CURSOR c_pf_line_count IS
4231 SELECT count(*)
4232 FROM igc_cc_arc_det_pf ICADP
4233 WHERE ICADP.cc_acct_line_id IN
4234 ( SELECT ICAAL.cc_acct_line_id
4235 FROM igc_cc_arc_acct_lines ICAAL
4236 WHERE ICAAL.cc_header_id = l_cc_header_id
4237 );
4238
4239 CURSOR c_mc_acct_line_count IS
4240 SELECT count(*)
4241 FROM igc_cc_arc_mc_acct_lines ICAMAL
4242 WHERE ICAMAL.cc_acct_line_id IN
4243 ( SELECT ICAAL.cc_acct_line_id
4244 FROM igc_cc_arc_acct_lines ICAAL
4245 WHERE ICAAL.cc_header_id = l_cc_header_id
4246 );
4247
4248 CURSOR c_mc_pf_line_count IS
4249 SELECT count(*)
4250 FROM igc_cc_arc_mc_det_pf ICAMDP
4251 WHERE ICAMDP.cc_det_pf_line_id IN
4252 ( SELECT ICADP.cc_det_pf_line_id
4253 FROM igc_cc_arc_det_pf ICADP
4254 WHERE ICADP.cc_acct_line_id IN
4255 ( SELECT ICAAL.cc_acct_line_id
4256 FROM igc_cc_arc_acct_lines ICAAL
4257 WHERE ICAAL.cc_header_id = l_cc_header_id
4258 )
4259 );
4260 l_full_path VARCHAR2(255);
4261
4262 BEGIN
4263
4264 l_full_path := g_path || 'Insert_Archive_History';
4265
4266 -- --------------------------------------------------------------------
4267 -- Initialize Return status and other local variables.
4268 -- --------------------------------------------------------------------
4269 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4270
4271 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4272 IF (g_debug_mode = 'Y') THEN
4273 g_debug_msg := ' Beginning Inserting Archive History Records for Last Activity Date : ' ||
4274 g_last_activity_date;
4275 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4276 END IF;
4277
4278 -- --------------------------------------------------------------------
4279 -- Open the cursor to obtain all the records that have been added into
4280 -- the appropriate archive CC Header table and then begin to insert
4281 -- the corresponding history record.
4282 -- --------------------------------------------------------------------
4283 OPEN c_get_archived_hdrs;
4284 FETCH c_get_archived_hdrs
4285 INTO l_cc_header_id,
4286 g_cc_num,
4287 l_max_date;
4288
4289 IF (c_get_archived_hdrs%FOUND) THEN
4290
4291 WHILE (c_get_archived_hdrs%FOUND) LOOP
4292
4293 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4294 IF (g_debug_mode = 'Y') THEN
4295 g_debug_msg := ' Inserting Archive History Record for CC Header ID : ' ||
4296 l_cc_header_id;
4297 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4298 END IF;
4299
4300 -- -------------------------------------------------------------------
4301 -- Initialize the record that will be inserted into the Archive
4302 -- History Table.
4303 -- -------------------------------------------------------------------
4304 Initialize_History_Record (p_cc_header_id => l_cc_header_id,
4305 p_History_Rec => l_history_rec,
4306 x_Return_Status => l_return_status
4307 );
4308
4309 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4310 raise FND_API.G_EXC_ERROR;
4311 END IF;
4312
4313 OPEN c_cc_header_info;
4314 FETCH c_cc_header_info
4315 INTO l_cc_header_id,
4316 g_cc_num,
4317 l_cc_type,
4318 l_parent_id;
4319
4320 IF (c_cc_header_info%FOUND) THEN
4321
4322 l_history_rec.parent_header_id := l_parent_id;
4323 l_history_rec.cc_type := l_cc_type;
4324 l_history_rec.cc_num := g_cc_num;
4325 l_history_rec.last_cc_activity_date := l_max_date;
4326 l_history_rec.archive_done_flag := 'Y';
4327
4328 -- -------------------------------------------------------------------
4329 -- Get the number of account lines that were archived.
4330 -- -------------------------------------------------------------------
4331 OPEN c_acct_line_count;
4332 FETCH c_acct_line_count
4333 INTO l_history_rec.num_acct_lines_arc;
4334
4335 CLOSE c_acct_line_count;
4336
4337 -- -------------------------------------------------------------------
4338 -- Get the number of Det PF lines that were archived.
4339 -- -------------------------------------------------------------------
4340 OPEN c_pf_line_count;
4341 FETCH c_pf_line_count
4342 INTO l_history_rec.num_det_pf_lines_arc;
4343
4344 CLOSE c_pf_line_count;
4345
4346 -- -------------------------------------------------------------------
4347 -- Get the Number of MRC Acct Lines that were archived.
4348 -- -------------------------------------------------------------------
4349 OPEN c_mc_acct_line_count;
4350 FETCH c_mc_acct_line_count
4351 INTO l_history_rec.num_mc_acct_lines_arc;
4352
4353 CLOSE c_mc_acct_line_count;
4354
4355 -- -------------------------------------------------------------------
4356 -- Get the number of MRC DET PF Lines that were archived.
4357 -- -------------------------------------------------------------------
4358 OPEN c_mc_pf_line_count;
4359 FETCH c_mc_pf_line_count
4360 INTO l_history_rec.num_mc_det_pf_lines_arc;
4361
4362 CLOSE c_mc_pf_line_count;
4363
4364 -- -------------------------------------------------------------------
4365 -- Update or insert the appropriate record in the history table.
4366 -- -------------------------------------------------------------------
4367 Update_History (p_History_Rec => l_history_rec,
4368 x_Return_Status => l_return_status
4369 );
4370
4371 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4372 raise FND_API.G_EXC_ERROR;
4373 END IF;
4374
4375 ELSE
4376
4377 IF (g_debug_mode = 'Y') THEN
4378 g_debug_msg := 'View returned CC Header ID : ' ||
4379 l_cc_header_id || ' But could not find it in Cursor.';
4380 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4381 END IF;
4382
4383 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
4384 p_tokval => g_sob_id);
4385 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE1',
4386 p_tokval => g_cc_num);
4387 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE2',
4388 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
4389 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
4390 p_tokval => 'CC');
4391 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
4392 p_msgname => 'IGC_NUM_PUR_NO_MATCH_ARC');
4393 raise FND_API.G_EXC_ERROR;
4394
4395 END IF;
4396
4397 IF (c_cc_header_info%ISOPEN) THEN
4398 CLOSE c_cc_header_info;
4399 END IF;
4400
4401 FETCH c_get_archived_hdrs
4402 INTO l_cc_header_id,
4403 g_cc_num,
4404 l_max_date;
4405
4406 END LOOP;
4407
4408 ELSE
4409
4410 IF (g_debug_mode = 'Y') THEN
4411 g_debug_msg := 'No Archive Candidates to insert into history table.';
4412 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4413 END IF;
4414
4415 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
4416 p_tokval => g_sob_id);
4417 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE1',
4418 p_tokval => g_cc_num);
4419 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE2',
4420 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
4421 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
4422 p_tokval => 'CC');
4423 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
4424 p_msgname => 'IGC_NO_ARC_RECORDS');
4425
4426 END IF;
4427
4428 -- -------------------------------------------------------------------------
4429 -- Close any and all cursors before returning.
4430 -- -------------------------------------------------------------------------
4431 IF (c_get_archived_hdrs%ISOPEN) THEN
4432 CLOSE c_get_archived_hdrs;
4433 END IF;
4434 IF (c_cc_header_info%ISOPEN) THEN
4435 CLOSE c_cc_header_info;
4436 END IF;
4437 IF (c_acct_line_count%ISOPEN) THEN
4438 CLOSE c_acct_line_count;
4439 END IF;
4440 IF (c_mc_acct_line_count%ISOPEN) THEN
4441 CLOSE c_mc_acct_line_count;
4442 END IF;
4443 IF (c_pf_line_count%ISOPEN) THEN
4444 CLOSE c_pf_line_count;
4445 END IF;
4446 IF (c_mc_pf_line_count%ISOPEN) THEN
4447 CLOSE c_mc_pf_line_count;
4448 END IF;
4449
4450 RETURN;
4451
4452 -- --------------------------------------------------------------------
4453 -- Exception handler section for the Insert_Archive_History procedure.
4454 -- --------------------------------------------------------------------
4455 EXCEPTION
4456
4457 WHEN FND_API.G_EXC_ERROR THEN
4458 x_Return_Status := FND_API.G_RET_STS_ERROR;
4459 IF (c_get_archived_hdrs%ISOPEN) THEN
4460 CLOSE c_get_archived_hdrs;
4461 END IF;
4462 IF (c_cc_header_info%ISOPEN) THEN
4463 CLOSE c_cc_header_info;
4464 END IF;
4465 IF (c_acct_line_count%ISOPEN) THEN
4466 CLOSE c_acct_line_count;
4467 END IF;
4468 IF (c_mc_acct_line_count%ISOPEN) THEN
4469 CLOSE c_mc_acct_line_count;
4470 END IF;
4471 IF (c_pf_line_count%ISOPEN) THEN
4472 CLOSE c_pf_line_count;
4473 END IF;
4474 IF (c_mc_pf_line_count%ISOPEN) THEN
4475 CLOSE c_mc_pf_line_count;
4476 END IF;
4477 IF (g_excep_level >= g_debug_level ) THEN
4478 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
4479 END IF;
4480 RETURN;
4481
4482 WHEN OTHERS THEN
4483 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4484 IF (c_get_archived_hdrs%ISOPEN) THEN
4485 CLOSE c_get_archived_hdrs;
4486 END IF;
4487 IF (c_cc_header_info%ISOPEN) THEN
4488 CLOSE c_cc_header_info;
4489 END IF;
4490 IF (c_acct_line_count%ISOPEN) THEN
4491 CLOSE c_acct_line_count;
4492 END IF;
4493 IF (c_mc_acct_line_count%ISOPEN) THEN
4494 CLOSE c_mc_acct_line_count;
4495 END IF;
4496 IF (c_pf_line_count%ISOPEN) THEN
4497 CLOSE c_pf_line_count;
4498 END IF;
4499 IF (c_mc_pf_line_count%ISOPEN) THEN
4500 CLOSE c_mc_pf_line_count;
4501 END IF;
4502 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4503 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4504 END IF;
4505 IF ( g_unexp_level >= g_debug_level ) THEN
4506 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4507 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4508 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4509 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4510 END IF;
4511 RETURN;
4512
4513 END Insert_Archive_History;
4514
4515 --
4516 -- Lock_Candidates Function is designed to ensure that if one process is already
4517 -- performing the Archive Purge process that another one can not be started.
4518 --
4519 -- Parameters :
4520 -- NONE
4521 --
4522 FUNCTION Lock_Candidates
4523 RETURN BOOLEAN IS
4524
4525 -- --------------------------------------------------------------------
4526 -- Declare local variables to be used.
4527 -- --------------------------------------------------------------------
4528 l_counter NUMBER(1) := 0;
4529 l_cc_header igc_cc_headers.cc_header_id%TYPE;
4530 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Candidates';
4531
4532 CURSOR c_lock_candidates IS
4533 SELECT cc_header_id
4534 FROM igc_arc_pur_candidates
4535 FOR UPDATE NOWAIT;
4536
4537 l_full_path VARCHAR2(255);
4538
4539 BEGIN
4540
4541 l_full_path := g_path || 'Lock_Candidates';
4542
4543 WHILE l_counter < g_maxloops LOOP -- Loop in case we need to retry lock
4544 BEGIN -- loop block
4545
4546 OPEN c_lock_candidates;
4547
4548 FETCH c_lock_candidates
4549 INTO l_cc_header;
4550
4551 IF (c_lock_candidates%NOTFOUND) THEN
4552
4553 CLOSE c_lock_candidates;
4554 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4555 IF (g_debug_mode = 'Y') THEN
4556 Output_Debug (l_full_path, 'No records found to lock in IGC_CC_ARC_PUR_CANDIDATES');
4557 END IF;
4558 RETURN TRUE;
4559
4560 ELSE -- lock, with records, has been obtained
4561
4562 CLOSE c_lock_candidates;
4563 RETURN TRUE;
4564
4565 END IF;
4566
4567 EXCEPTION
4568 WHEN FND_API.G_EXC_ERROR THEN
4569 IF (c_lock_candidates%ISOPEN) THEN
4570 CLOSE c_lock_candidates;
4571 END IF;
4572 IF (g_excep_level >= g_debug_level ) THEN
4573 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
4574 END IF;
4575 RETURN FALSE;
4576
4577 WHEN OTHERS THEN
4578 IF (SQLCODE = -54) THEN -- Record(s) are already locked
4579 l_counter := l_counter + 1;
4580
4581 IF (l_counter >= g_maxloops) THEN -- Tried max number of times
4582 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE',
4583 p_tokval => to_char(g_maxloops));
4584 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
4585 p_msgname => 'IGC_CC_UNABLE_TO_LOCK');
4586
4587 IF (c_lock_candidates%ISOPEN) THEN
4588 CLOSE c_lock_candidates;
4589 END IF;
4590 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4591 IF (g_debug_mode = 'Y') THEN
4592 Output_Debug (l_full_path, 'Maximum tries reached: unable to lock IGC_CC_ARC_PUR_CANDIDATES');
4593 END IF;
4594 RETURN FALSE;
4595 END IF;
4596
4597 DBMS_LOCK.SLEEP (g_seconds); -- Wait, then try again
4598
4599 ELSE
4600
4601 IF (c_lock_candidates%ISOPEN) THEN
4602 CLOSE c_lock_candidates;
4603 END IF;
4604 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4605 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
4606 END IF;
4607 END IF;
4608 IF ( g_unexp_level >= g_debug_level ) THEN
4609 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4610 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4611 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4612 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4613 END IF;
4614
4615 END; -- inner block
4616
4617 END LOOP; -- WHILE loop
4618
4619
4620 EXCEPTION
4621 WHEN OTHERS THEN
4622 IF (c_lock_candidates%ISOPEN) THEN
4623 CLOSE c_lock_candidates;
4624 END IF;
4625 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4626 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
4627 END IF;
4628 IF ( g_unexp_level >= g_debug_level ) THEN
4629 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4630 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4631 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4632 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4633 END IF;
4634 RETURN FALSE;
4635
4636 END Lock_Candidates;
4637
4638 --
4639 -- Output_Debug Procedure is the Generic procedure designed for outputting debug
4640 -- information that is required from this procedure.
4641 --
4642 -- Parameters :
4643 --
4644 -- p_debug_msg ==> Record to be output into the debug log file.
4645 --
4646 PROCEDURE Output_Debug (
4647 p_path IN VARCHAR2,
4648 p_debug_msg IN VARCHAR2
4649 ) IS
4650
4651 -- Constants :
4652
4653 /* l_prod VARCHAR2(3) := 'IGC';
4654 l_sub_comp VARCHAR2(6) := 'CC_ARC';
4655 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
4656 l_Return_Status VARCHAR2(1);*/
4657 l_api_name CONSTANT VARCHAR2(30) := 'Output_Debug';
4658
4659 BEGIN
4660
4661 /* IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
4662 p_profile_log_name => l_profile_name,
4663 p_prod => l_prod,
4664 p_sub_comp => l_sub_comp,
4665 p_filename_val => NULL,
4666 x_Return_Status => l_Return_Status
4667 );
4668
4669 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4670 raise FND_API.G_EXC_ERROR;
4671 END IF;*/
4672
4673 IF(g_state_level >= g_debug_level) THEN
4674 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
4675 END IF;
4676
4677 RETURN;
4678
4679 -- --------------------------------------------------------------------
4680 -- Exception handler section for the Output_Debug procedure.
4681 -- --------------------------------------------------------------------
4682 EXCEPTION
4683
4684 /*WHEN FND_API.G_EXC_ERROR THEN
4685 RETURN;*/
4686
4687 WHEN OTHERS THEN
4688 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4689 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4690 END IF;
4691 RETURN;
4692
4693 END Output_Debug;
4694
4695 --
4696 -- Purge_CC Procedure is the main process designed for deleting the CC Header IDs
4697 -- from the system that have met the criteria for purging.
4698 --
4699 -- Parameters :
4700 --
4701 -- x_Return_Status ==> Status of procedure returned to caller
4702 --
4703 PROCEDURE Purge_CC (
4704 x_Return_Status OUT NOCOPY VARCHAR2
4705 ) IS
4706
4707 -- --------------------------------------------------------------------
4708 -- Declare local variables to be used in this procedure.
4709 -- --------------------------------------------------------------------
4710 l_return_status VARCHAR2(1);
4711 l_api_name CONSTANT VARCHAR2(30) := 'Purge_CC';
4712 l_full_path VARCHAR2(255);
4713
4714 BEGIN
4715
4716 l_full_path := g_path || 'Purge_CC';
4717
4718 -- --------------------------------------------------------------------
4719 -- Initialize Return status and other local variables.
4720 -- --------------------------------------------------------------------
4721 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4722
4723 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4724 IF (g_debug_mode = 'Y') THEN
4725 g_debug_msg := ' Beginning Purge process for Last Activity Date : ' ||
4726 g_last_activity_date;
4727 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4728 END IF;
4729
4730 -- -------------------------------------------------------------------
4731 -- Determine if MRC has been installed and enabled and if this is the
4732 -- case then call the appropriate procedure to remove any MRC records
4733 -- that are candidates for removal and the correct number of records
4734 -- were previously archived.
4735 -- -------------------------------------------------------------------
4736 IF (g_mrc_installed = 'Y') THEN
4737
4738 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4739 IF (g_debug_mode = 'Y') THEN
4740 g_debug_msg := ' Attempt delete records in MRC tables. Last Activity Date : ' ||
4741 g_last_activity_date;
4742 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4743 END IF;
4744
4745 Purge_MRC_Tbls (x_return_status => l_return_status);
4746
4747 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4748 raise FND_API.G_EXC_ERROR;
4749 END IF;
4750
4751 ELSE
4752
4753 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4754 IF (g_debug_mode = 'Y') THEN
4755 g_debug_msg := ' NOT deleting records from MRC tables. Last Activity Date : ' ||
4756 g_last_activity_date;
4757 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4758 g_debug_msg := ' MRC is not enabled or installed.';
4759 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4760 END IF;
4761
4762 END IF;
4763
4764 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4765 IF (g_debug_mode = 'Y') THEN
4766 g_debug_msg := ' Attempt delete records in NON MRC tables. Last Activity Date : ' ||
4767 g_last_activity_date;
4768 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4769 END IF;
4770
4771 -- -------------------------------------------------------------------
4772 -- Call the appropriate procedure to remove any NON MRC records that
4773 -- are candidates for removal and the correct number of records were
4774 -- previously archived.
4775 -- -------------------------------------------------------------------
4776 Purge_NON_MRC_Tbls (x_return_status => l_return_status);
4777
4778 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4779 raise FND_API.G_EXC_ERROR;
4780 END IF;
4781
4782 RETURN;
4783
4784 -- --------------------------------------------------------------------
4785 -- Exception handler section for the Purge_CC procedure.
4786 -- --------------------------------------------------------------------
4787 EXCEPTION
4788
4789 WHEN FND_API.G_EXC_ERROR THEN
4790 x_Return_Status := FND_API.G_RET_STS_ERROR;
4791 IF (g_excep_level >= g_debug_level ) THEN
4792 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
4793 END IF;
4794 RETURN;
4795
4796 WHEN OTHERS THEN
4797 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4798 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4799 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4800 END IF;
4801 IF ( g_unexp_level >= g_debug_level ) THEN
4802 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4803 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4804 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4805 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4806 END IF;
4807 RETURN;
4808
4809 END Purge_CC;
4810
4811 --
4812 -- Purge_MRC_Tbls Procedure is designed to purge ONLY the MRC related data for the CC
4813 -- Header IDs that have been found to be valid candidates for purging.
4814 --
4815 -- Parameters :
4816 --
4817 -- x_Return_Status ==> Status of procedure returned to caller
4818 --
4819 PROCEDURE Purge_MRC_Tbls (
4820 x_Return_Status OUT NOCOPY VARCHAR2
4821 ) IS
4822
4823 -- --------------------------------------------------------------------
4824 -- Declare local variables to be used in this procedure.
4825 -- --------------------------------------------------------------------
4826 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
4827 l_num_mc_acct_arc igc_cc_archive_history.num_mc_acct_lines_arc%TYPE;
4828 l_num_mc_pf_arc igc_cc_archive_history.num_mc_det_pf_lines_arc%TYPE;
4829 l_num_mc_acct_pur igc_cc_archive_history.num_mc_acct_lines_arc%TYPE;
4830 l_num_mc_pf_pur igc_cc_archive_history.num_mc_det_pf_lines_arc%TYPE;
4831 l_api_name CONSTANT VARCHAR2(30) := 'Purge_MRC_Tbls';
4832
4833 -- --------------------------------------------------------------------
4834 -- Declare any cursors to be used.
4835 -- --------------------------------------------------------------------
4836 CURSOR c_get_arc_hist_info IS
4837 SELECT num_mc_acct_lines_arc,
4838 num_mc_det_pf_lines_arc
4839 FROM igc_cc_archive_history
4840 WHERE cc_header_id = l_cc_header_id
4841 AND set_of_books_id = g_sob_id
4842 AND org_id = g_org_id
4843 AND archive_done_flag = 'Y';
4844
4845 CURSOR c_get_purge_candidate IS
4846 SELECT distinct (ICAP.cc_header_id)
4847 FROM igc_arc_pur_candidates ICAP;
4848 l_full_path VARCHAR2(255);
4849
4850 BEGIN
4851
4852 l_full_path := g_path || 'Purge_MRC_Tbls';
4853
4854 -- --------------------------------------------------------------------
4855 -- Initialize Return status and other local variables.
4856 -- --------------------------------------------------------------------
4857 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4858
4859 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
4860 IF (g_debug_mode = 'Y') THEN
4861 g_debug_msg := ' Beginning Purge MRC Tables process for Last Activity Date : ' ||
4862 g_last_activity_date;
4863 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4864 END IF;
4865
4866 OPEN c_get_purge_candidate;
4867 FETCH c_get_purge_candidate
4868 INTO l_cc_header_id;
4869
4870 IF (c_get_purge_candidate%FOUND) THEN
4871
4872 WHILE (c_get_purge_candidate%FOUND) LOOP
4873
4874 IF (g_debug_mode = 'Y') THEN
4875 g_debug_msg := '********** Deleting MRC CC Header ID : ' ||
4876 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' *************';
4877 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4878 END IF;
4879
4880 -- --------------------------------------------------------------------
4881 -- Get CC Header information on record previously archived.
4882 -- --------------------------------------------------------------------
4883 OPEN c_get_arc_hist_info;
4884 FETCH c_get_arc_hist_info
4885 INTO l_num_mc_acct_arc,
4886 l_num_mc_pf_arc;
4887
4888 IF (c_get_arc_hist_info%FOUND) THEN
4889
4890 -- --------------------------------------------------------------------
4891 -- Delete all MRC PF History Lines for related CC Header IDs.
4892 -- --------------------------------------------------------------------
4893 DELETE
4894 FROM igc_cc_mc_det_pf_history DPH
4895 WHERE DPH.cc_det_pf_line_id IN
4896 ( SELECT DPFH.cc_det_pf_line_id
4897 FROM igc_cc_det_pf_history DPFH
4898 WHERE DPFH.cc_acct_line_id IN
4899 ( SELECT ACLH.cc_acct_line_id
4900 FROM igc_cc_acct_line_history ACLH
4901 WHERE ACLH.cc_header_id = l_cc_header_id
4902 )
4903 OR DPFH.cc_acct_line_id IN
4904 ( SELECT ACL.cc_acct_line_id
4905 FROM igc_cc_acct_lines ACL
4906 WHERE ACL.cc_header_id = l_cc_header_id
4907 )
4908 )
4909 OR DPH.cc_det_pf_line_id IN
4910 ( SELECT DPF.cc_det_pf_line_id
4911 FROM igc_cc_det_pf DPF
4912 WHERE DPF.cc_acct_line_id IN
4913 ( SELECT ACLH.cc_acct_line_id
4914 FROM igc_cc_acct_line_history ACLH
4915 WHERE ACLH.cc_header_id = l_cc_header_id
4916 )
4917 OR DPF.cc_acct_line_id IN
4918 ( SELECT ACL.cc_acct_line_id
4919 FROM igc_cc_acct_lines ACL
4920 WHERE ACL.cc_header_id = l_cc_header_id
4921 )
4922 );
4923
4924 IF (g_debug_mode = 'Y') THEN
4925 g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_DET_PF_HISTORY for CC Header ID : ' ||
4926 l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
4927 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4928 END IF;
4929
4930 -- --------------------------------------------------------------------
4931 -- Delete all MRC PF History Lines for related CC Header IDs.
4932 -- --------------------------------------------------------------------
4933 DELETE
4934 FROM igc_cc_mc_det_pf DP
4935 WHERE DP.cc_det_pf_line_id IN
4936 ( SELECT DPFH.cc_det_pf_line_id
4937 FROM igc_cc_det_pf_history DPFH
4938 WHERE DPFH.cc_acct_line_id IN
4939 ( SELECT ACLH.cc_acct_line_id
4940 FROM igc_cc_acct_line_history ACLH
4941 WHERE ACLH.cc_header_id = l_cc_header_id
4942 )
4943 OR DPFH.cc_acct_line_id IN
4944 ( SELECT ACL.cc_acct_line_id
4945 FROM igc_cc_acct_lines ACL
4946 WHERE ACL.cc_header_id = l_cc_header_id
4947 )
4948 )
4949 OR DP.cc_det_pf_line_id IN
4950 ( SELECT DPF.cc_det_pf_line_id
4951 FROM igc_cc_det_pf DPF
4952 WHERE DPF.cc_acct_line_id IN
4953 ( SELECT ACLH.cc_acct_line_id
4954 FROM igc_cc_acct_line_history ACLH
4955 WHERE ACLH.cc_header_id = l_cc_header_id
4956 )
4957 OR DPF.cc_acct_line_id IN
4958 ( SELECT ACL.cc_acct_line_id
4959 FROM igc_cc_acct_lines ACL
4960 WHERE ACL.cc_header_id = l_cc_header_id
4961 )
4962 );
4963
4964 l_num_mc_pf_pur := SQL%ROWCOUNT;
4965 IF (g_debug_mode = 'Y') THEN
4966 g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_DET_PF for CC Header ID : ' ||
4967 l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
4968 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4969 END IF;
4970
4971 -- --------------------------------------------------------------------
4972 -- Delete all MRC Account Line History Lines for related CC Header IDs.
4973 -- --------------------------------------------------------------------
4974 DELETE
4975 FROM igc_cc_mc_acct_line_history CALH
4976 WHERE CALH.cc_acct_line_id IN
4977 ( SELECT ACLH.cc_acct_line_id
4978 FROM igc_cc_acct_line_history ACLH
4979 WHERE ACLH.cc_header_id = l_cc_header_id
4980 )
4981 OR CALH.cc_acct_line_id IN
4982 ( SELECT ACL.cc_acct_line_id
4983 FROM igc_cc_acct_lines ACL
4984 WHERE ACL.cc_header_id = l_cc_header_id
4985 );
4986
4987 IF (g_debug_mode = 'Y') THEN
4988 g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_ACCT_LINE_HISTORY CC Header ID : ' ||
4989 l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
4990 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
4991 END IF;
4992
4993 -- --------------------------------------------------------------------
4994 -- Delete all MRC Account Line History Lines for related CC Header IDs.
4995 -- --------------------------------------------------------------------
4996 DELETE
4997 FROM igc_cc_mc_acct_lines CAL
4998 WHERE CAL.cc_acct_line_id IN
4999 ( SELECT ACLH.cc_acct_line_id
5000 FROM igc_cc_acct_line_history ACLH
5001 WHERE ACLH.cc_header_id = l_cc_header_id
5002 )
5003 OR CAL.cc_acct_line_id IN
5004 ( SELECT ACL.cc_acct_line_id
5005 FROM igc_cc_acct_lines ACL
5006 WHERE ACL.cc_header_id = l_cc_header_id
5007 );
5008
5009 l_num_mc_acct_pur := SQL%ROWCOUNT;
5010 IF (g_debug_mode = 'Y') THEN
5011 g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_ACCT_LINES for CC Header ID : ' ||
5012 l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
5013 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5014 END IF;
5015
5016 -- --------------------------------------------------------------------
5017 -- Delete all MRC Header History records for related CC Header IDs.
5018 -- --------------------------------------------------------------------
5019 DELETE
5020 FROM igc_cc_mc_header_history
5021 WHERE cc_header_id = l_cc_header_id;
5022
5023 IF (g_debug_mode = 'Y') THEN
5024 g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_HEADER_HISTORY for CC Header ID : ' ||
5025 l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
5026 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5027 END IF;
5028
5029 -- --------------------------------------------------------------------
5030 -- Delete all MRC Header records for related CC Header IDs.
5031 -- --------------------------------------------------------------------
5032 DELETE
5033 FROM igc_cc_mc_headers
5034 WHERE cc_header_id = l_cc_header_id;
5035
5036 IF (g_debug_mode = 'Y') THEN
5037 g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_HEADERS for CC Header ID : ' ||
5038 l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
5039 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5040 END IF;
5041
5042 -- -------------------------------------------------------------------
5043 -- Make sure that the values being checked are set to 0 if NULL.
5044 -- -------------------------------------------------------------------
5045 l_num_mc_acct_pur := NVL (l_num_mc_acct_pur, 0);
5046 l_num_mc_pf_pur := NVL (l_num_mc_pf_pur, 0);
5047
5048 -- -------------------------------------------------------------------
5049 -- Check to make sure that the # of records purged match the number
5050 -- of records that were previously archived.
5051 -- -------------------------------------------------------------------
5052 IF ((l_num_mc_acct_arc <> l_num_mc_acct_pur) OR
5053 (l_num_mc_pf_arc <> l_num_mc_pf_pur)) THEN
5054
5055 IF (g_debug_mode = 'Y') THEN
5056 g_debug_msg := ' Number MRC Acct Lines Archived : ' || l_num_mc_acct_arc ||
5057 ' Number MRC Acct Lines Purged : ' || l_num_mc_acct_pur ||
5058 ' Number MRC PF Lines Archived : ' || l_num_mc_pf_arc ||
5059 ' Number MRC PF Lines Purged : ' || l_num_mc_pf_pur ||
5060 ' Does not match for CC Header ID : ' ||
5061 l_cc_header_id || ' SOB ID : ' || g_sob_id ||
5062 '. Thus no Purge can be done for Header ID.';
5063 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5064 END IF;
5065
5066 IGC_MSGS_PKG.message_token (p_tokname => 'CC_NUM',
5067 p_tokval => g_cc_num);
5068 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5069 p_tokval => g_sob_id);
5070 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5071 p_msgname => 'IGC_NUM_MC_PUR_NO_MATCH_ARC');
5072 raise FND_API.G_EXC_ERROR;
5073
5074 END IF;
5075
5076 ELSE
5077
5078 IF (g_debug_mode = 'Y') THEN
5079 g_debug_msg := 'Header ID Not found in Archive History Table for CC Header ID : ' ||
5080 l_cc_header_id || ' SOB ID : ' || g_sob_id ||
5081 '. Thus no Purge can be done for Header ID.';
5082 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5083 END IF;
5084
5085 IGC_MSGS_PKG.message_token (p_tokname => 'CC_NUM',
5086 p_tokval => g_cc_num);
5087 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5088 p_tokval => g_sob_id);
5089 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5090 p_msgname => 'IGC_NUM_MC_PUR_NO_MATCH_ARC');
5091 raise FND_API.G_EXC_ERROR;
5092
5093 END IF;
5094
5095 -- -------------------------------------------------------------------
5096 -- Close the cursor opened.
5097 -- -------------------------------------------------------------------
5098 IF (c_get_arc_hist_info%ISOPEN) THEN
5099 CLOSE c_get_arc_hist_info;
5100 END IF;
5101
5102 FETCH c_get_purge_candidate
5103 INTO l_cc_header_id;
5104
5105 END LOOP;
5106
5107 ELSE
5108
5109 IF (g_debug_mode = 'Y') THEN
5110 g_debug_msg := 'View returned CC Header ID : ' ||
5111 l_cc_header_id || ' But could not find it in Cursor.';
5112 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5113 END IF;
5114
5115 IGC_MSGS_PKG.message_token (p_tokname => 'CC_NUM',
5116 p_tokval => g_cc_num);
5117 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5118 p_tokval => g_sob_id);
5119 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5120 p_msgname => 'IGC_NUM_MC_PUR_NO_MATCH_ARC');
5121 raise FND_API.G_EXC_ERROR;
5122
5123 END IF;
5124
5125 -- -------------------------------------------------------------------
5126 -- Close the cursors opened.
5127 -- -------------------------------------------------------------------
5128 IF (c_get_purge_candidate%ISOPEN) THEN
5129 CLOSE c_get_purge_candidate;
5130 END IF;
5131 IF (c_get_arc_hist_info%ISOPEN) THEN
5132 CLOSE c_get_arc_hist_info;
5133 END IF;
5134
5135 RETURN;
5136
5137 -- --------------------------------------------------------------------
5138 -- Exception handler section for the Purge_CC procedure.
5139 -- --------------------------------------------------------------------
5140 EXCEPTION
5141
5142 WHEN FND_API.G_EXC_ERROR THEN
5143 x_Return_Status := FND_API.G_RET_STS_ERROR;
5144 IF (c_get_purge_candidate%ISOPEN) THEN
5145 CLOSE c_get_purge_candidate;
5146 END IF;
5147 IF (c_get_arc_hist_info%ISOPEN) THEN
5148 CLOSE c_get_arc_hist_info;
5149 END IF;
5150 IF (g_excep_level >= g_debug_level ) THEN
5151 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
5152 END IF;
5153 RETURN;
5154
5155 WHEN OTHERS THEN
5156 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
5157 IF (c_get_purge_candidate%ISOPEN) THEN
5158 CLOSE c_get_purge_candidate;
5159 END IF;
5160 IF (c_get_arc_hist_info%ISOPEN) THEN
5161 CLOSE c_get_arc_hist_info;
5162 END IF;
5163 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
5164 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
5165 END IF;
5166 IF ( g_unexp_level >= g_debug_level ) THEN
5167 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
5168 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
5169 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
5170 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
5171 END IF;
5172 RETURN;
5173
5174 END Purge_MRC_Tbls;
5175
5176 --
5177 -- Purge_NON_MRC_Tbls Procedure is designed for purging all the tables that are not
5178 -- related to MRC for the CC Header IDs that were found to be valid for purging.
5179 --
5180 -- Parameters :
5181 --
5182 -- x_Return_Status ==> Status of procedure returned to caller
5183 --
5184 PROCEDURE Purge_NON_MRC_Tbls (
5185 x_Return_Status OUT NOCOPY VARCHAR2
5186 ) IS
5187
5188 -- --------------------------------------------------------------------
5189 -- Declare local variables to be used in this procedure.
5190 -- --------------------------------------------------------------------
5191 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
5192 l_sob_id igc_cc_headers.set_of_books_id%TYPE;
5193 l_num_acct_arc igc_cc_archive_history.num_acct_lines_arc%TYPE;
5194 l_num_pf_arc igc_cc_archive_history.num_det_pf_lines_arc%TYPE;
5195 l_num_acct_pur igc_cc_archive_history.num_acct_lines_arc%TYPE;
5196 l_num_pf_pur igc_cc_archive_history.num_det_pf_lines_arc%TYPE;
5197 l_api_name CONSTANT VARCHAR2(30) := 'Purge_NON_MRC_Tbls';
5198 l_history_rec igc_cc_archive_history%ROWTYPE;
5199 l_return_status VARCHAR2(1);
5200
5201 -- --------------------------------------------------------------------
5202 -- Declare any cursors to be used.
5203 -- --------------------------------------------------------------------
5204 CURSOR c_get_arc_hist_info IS
5205 SELECT num_acct_lines_arc,
5206 num_det_pf_lines_arc
5207 FROM igc_cc_archive_history
5208 WHERE cc_header_id = l_cc_header_id
5209 /* AND set_of_books_id = g_sob_id
5210 AND org_id = g_org_id --Commented for MOAC uptake */
5211 AND archive_done_flag = 'Y';
5212
5213 CURSOR c_get_purge_candidate IS
5214 SELECT distinct (ICAP.cc_header_id)
5215 FROM igc_arc_pur_candidates ICAP;
5216
5217 l_full_path VARCHAR2(255);
5218
5219 BEGIN
5220
5221 l_full_path := g_path || 'Purge_NON_MRC_Tbls';
5222
5223 -- --------------------------------------------------------------------
5224 -- Initialize Return status and other local variables.
5225 -- --------------------------------------------------------------------
5226 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
5227
5228 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5229 IF (g_debug_mode = 'Y') THEN
5230 g_debug_msg := ' Beginning Purge NON MRC Tables process for Last Activity Date : ' ||
5231 g_last_activity_date;
5232 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5233 END IF;
5234
5235 OPEN c_get_purge_candidate;
5236 FETCH c_get_purge_candidate
5237 INTO l_cc_header_id;
5238
5239 IF (c_get_purge_candidate%FOUND) THEN
5240
5241 WHILE (c_get_purge_candidate%FOUND) LOOP
5242
5243 IF (g_debug_mode = 'Y') THEN
5244 g_debug_msg := '********** Deleting NON MRC CC Header ID : ' ||
5245 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' *************';
5246 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5247 END IF;
5248
5249 -- --------------------------------------------------------------------
5250 -- Get CC Header information on record previously archived.
5251 -- --------------------------------------------------------------------
5252 OPEN c_get_arc_hist_info;
5253 FETCH c_get_arc_hist_info
5254 INTO l_num_acct_arc,
5255 l_num_pf_arc;
5256
5257 IF (c_get_arc_hist_info%FOUND) THEN
5258
5259 -- --------------------------------------------------------------------
5260 -- Delete all CC Action records for related CC Header IDs.
5261 -- --------------------------------------------------------------------
5262 DELETE
5263 FROM igc_cc_actions
5264 WHERE cc_header_id = l_cc_header_id;
5265
5266 IF (g_debug_mode = 'Y') THEN
5267 g_debug_msg := 'Number Rows Deleted from IGC_CC_ACTIONS for CC Header ID : ' ||
5268 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5269 to_char(SQL%ROWCOUNT);
5270 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5271 END IF;
5272
5273 -- --------------------------------------------------------------------
5274 -- Delete all PO Distribution records for related CC Header IDs.
5275 -- --------------------------------------------------------------------
5276 DELETE
5277 FROM po_distributions_all PDA
5278 WHERE PDA.po_header_id IN
5279 ( SELECT PHA.po_header_id
5280 FROM po_headers_all PHA
5281 WHERE PHA.segment1 IN
5282 ( SELECT ICV.cc_num
5283 FROM igc_arc_pur_candidates ICV
5284 WHERE ICV.cc_header_id = l_cc_header_id
5285 )
5286 );
5287
5288 IF (g_debug_mode = 'Y') THEN
5289 g_debug_msg := 'Number Rows Deleted from PO_DISTRIBUTIONS_ALL for CC Header ID : ' ||
5290 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5291 to_char(SQL%ROWCOUNT);
5292 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5293 END IF;
5294
5295 -- --------------------------------------------------------------------
5296 -- Delete all PO Line Location records for related CC Header IDs.
5297 -- --------------------------------------------------------------------
5298 DELETE
5299 FROM po_line_locations_all PLLA
5300 WHERE PLLA.po_header_id IN
5301 ( SELECT PHA.po_header_id
5302 FROM po_headers_all PHA
5303 WHERE PHA.segment1 IN
5304 ( SELECT ICV.cc_num
5305 FROM igc_arc_pur_candidates ICV
5306 WHERE ICV.cc_header_id = l_cc_header_id
5307 )
5308 );
5309
5310 IF (g_debug_mode = 'Y') THEN
5311 g_debug_msg := 'Number Rows Deleted from PO_LINE_LOCATIONS_ALL for CC Header ID : ' ||
5312 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5313 to_char(SQL%ROWCOUNT);
5314 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5315 END IF;
5316
5317 -- --------------------------------------------------------------------
5318 -- Delete all PO Lines records for related CC Header IDs.
5319 -- --------------------------------------------------------------------
5320 DELETE
5321 FROM po_lines_all PLA
5322 WHERE PLA.po_header_id IN
5323 ( SELECT PHA.po_header_id
5324 FROM po_headers_all PHA
5325 WHERE PHA.segment1 IN
5326 ( SELECT ICV.cc_num
5327 FROM igc_arc_pur_candidates ICV
5328 WHERE ICV.cc_header_id = l_cc_header_id
5329 )
5330 );
5331
5332 IF (g_debug_mode = 'Y') THEN
5333 g_debug_msg := 'Number Rows Deleted from PO_LINES_ALL for CC Header ID : ' ||
5334 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5335 to_char(SQL%ROWCOUNT);
5336 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5337 END IF;
5338
5339 -- --------------------------------------------------------------------
5340 -- Delete all PO Header records for related CC Header IDs.
5341 -- --------------------------------------------------------------------
5342 DELETE
5343 FROM po_headers_all PHA
5344 WHERE PHA.po_header_id IN
5345 ( SELECT PHA1.po_header_id
5346 FROM po_headers_all PHA1
5347 WHERE PHA1.segment1 IN
5348 ( SELECT ICV.cc_num
5349 FROM igc_arc_pur_candidates ICV
5350 WHERE ICV.cc_header_id = l_cc_header_id
5351 )
5352 );
5353
5354 IF (g_debug_mode = 'Y') THEN
5355 g_debug_msg := 'Number Rows Deleted from PO_HEADERS_ALL for CC Header ID : ' ||
5356 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5357 to_char(SQL%ROWCOUNT);
5358 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5359 END IF;
5360
5361 -- --------------------------------------------------------------------
5362 -- Delete all PF Line History records for related CC Header IDs.
5363 -- --------------------------------------------------------------------
5364 DELETE
5365 FROM igc_cc_det_pf_history CDPH
5366 WHERE CDPH.cc_acct_line_id IN
5367 ( SELECT ACLH.cc_acct_line_id
5368 FROM igc_cc_acct_line_history ACLH
5369 WHERE ACLH.cc_header_id = l_cc_header_id
5370 )
5371 OR CDPH.cc_acct_line_id IN
5372 ( SELECT ACL.cc_acct_line_id
5373 FROM igc_cc_acct_lines ACL
5374 WHERE ACL.cc_header_id = l_cc_header_id
5375 );
5376
5377 IF (g_debug_mode = 'Y') THEN
5378 g_debug_msg := 'Number Rows Deleted from IGC_CC_DET_PF_HISTORY for CC Header ID : ' ||
5379 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5380 to_char(SQL%ROWCOUNT);
5381 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5382 END IF;
5383
5384 -- --------------------------------------------------------------------
5385 -- Delete all PF Line records for related CC Header IDs.
5386 -- --------------------------------------------------------------------
5387 DELETE
5388 FROM igc_cc_det_pf CDP
5389 WHERE CDP.cc_acct_line_id IN
5390 ( SELECT ACLH.cc_acct_line_id
5391 FROM igc_cc_acct_line_history ACLH
5392 WHERE ACLH.cc_header_id = l_cc_header_id
5393 )
5394 OR CDP.cc_acct_line_id IN
5395 ( SELECT ACL.cc_acct_line_id
5396 FROM igc_cc_acct_lines ACL
5397 WHERE ACL.cc_header_id = l_cc_header_id
5398 );
5399
5400 l_num_pf_pur := SQL%ROWCOUNT;
5401 IF (g_debug_mode = 'Y') THEN
5402 g_debug_msg := 'Number Rows Deleted from IGC_CC_DET_PF for CC Header ID : ' ||
5403 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5404 to_char(SQL%ROWCOUNT);
5405 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5406 END IF;
5407
5408 -- --------------------------------------------------------------------
5409 -- Delete all Acct Line History records for related CC Header IDs.
5410 -- --------------------------------------------------------------------
5411 DELETE
5412 FROM igc_cc_acct_line_history ALH
5413 WHERE ALH.cc_header_id = l_cc_header_id;
5414
5415 IF (g_debug_mode = 'Y') THEN
5416 g_debug_msg := 'Number Rows Deleted from IGC_CC_ACCT_LINE_HISTORY for CC Header ID : ' ||
5417 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5418 to_char(SQL%ROWCOUNT);
5419 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5420 END IF;
5421
5422 -- --------------------------------------------------------------------
5423 -- Delete all Acct Line records for related CC Header IDs.
5424 -- --------------------------------------------------------------------
5425 DELETE
5426 FROM igc_cc_acct_lines AL
5427 WHERE AL.cc_header_id = l_cc_header_id;
5428
5429 l_num_acct_pur := SQL%ROWCOUNT;
5430 IF (g_debug_mode = 'Y') THEN
5431 g_debug_msg := 'Number Rows Deleted from IGC_CC_ACCT_LINES for CC Header ID : ' ||
5432 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5433 to_char(SQL%ROWCOUNT);
5434 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5435 END IF;
5436
5437 -- --------------------------------------------------------------------
5438 -- Delete all CC Interface records for related CC Header IDs.
5439 -- --------------------------------------------------------------------
5440 DELETE
5441 FROM igc_cc_interface CI
5442 WHERE CI.cc_header_id = l_cc_header_id
5443 AND CI.set_of_books_id = g_sob_id;
5444
5445 IF (g_debug_mode = 'Y') THEN
5446 g_debug_msg := 'Number Rows Deleted from IGC_CC_INTERFACE for CC Header ID : ' ||
5447 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5448 to_char(SQL%ROWCOUNT);
5449 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5450 END IF;
5451
5452 -- --------------------------------------------------------------------
5453 -- Delete all Header History records for related CC Header IDs.
5454 -- --------------------------------------------------------------------
5455 DELETE
5456 FROM igc_cc_header_history CHH
5457 WHERE CHH.cc_header_id = l_cc_header_id
5458 AND CHH.set_of_books_id = g_sob_id;
5459
5460 IF (g_debug_mode = 'Y') THEN
5461 g_debug_msg := 'Number Rows Deleted from IGC_CC_HEADER_HISTORY for CC Header ID : ' ||
5462 l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
5463 to_char(SQL%ROWCOUNT);
5464 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5465 END IF;
5466
5467 -- --------------------------------------------------------------------
5468 -- Delete all Header records for related CC Header IDs.
5469 -- --------------------------------------------------------------------
5470 DELETE
5471 FROM igc_cc_headers CH
5472 WHERE CH.cc_header_id = l_cc_header_id
5473 AND CH.set_of_books_id = g_sob_id;
5474
5475 IF (g_debug_mode = 'Y') THEN
5476 g_debug_msg := 'Number Rows Deleted from IGC_CC_HEADERS for CC Header ID : ' ||
5477 l_cc_header_id || ' SOB ID : ' || l_sob_id || ' is : ' ||
5478 to_char(SQL%ROWCOUNT);
5479 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5480 END IF;
5481
5482 -- -------------------------------------------------------------------
5483 -- Make sure that the values are set to 0 if NULL for comparison.
5484 -- -------------------------------------------------------------------
5485 l_num_acct_pur := NVL (l_num_acct_pur, 0);
5486 l_num_pf_pur := NVL (l_num_pf_pur, 0);
5487
5488 -- -------------------------------------------------------------------
5489 -- Check to make sure that the # of records purged match the number
5490 -- of records that were previously archived.
5491 -- -------------------------------------------------------------------
5492 IF ((l_num_acct_arc <> l_num_acct_pur) OR
5493 (l_num_pf_arc <> l_num_pf_pur)) THEN
5494
5495 IF (g_debug_mode = 'Y') THEN
5496 g_debug_msg := ' Number Acct Lines Archived : ' || l_num_acct_arc ||
5497 ' Number Acct Lines Purged : ' || l_num_acct_pur ||
5498 ' Number PF Lines Archived : ' || l_num_pf_arc ||
5499 ' Number PF Lines Purged : ' || l_num_pf_pur ||
5500 ' Does not match for CC Header ID : ' ||
5501 l_cc_header_id || ' SOB ID : ' || g_sob_id ||
5502 '. Thus no Purge can be done for Header ID.';
5503 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5504 END IF;
5505
5506 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5507 p_tokval => g_sob_id);
5508 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE1',
5509 p_tokval => l_cc_header_id);
5510 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE2',
5511 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
5512 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
5513 p_tokval => 'CC');
5514 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5515 p_msgname => 'IGC_NUM_PUR_NO_MATCH_ARC');
5516 raise FND_API.G_EXC_ERROR;
5517
5518 END IF;
5519
5520 -- -------------------------------------------------------------------
5521 -- Make sure that the History Record is updated to indicate that the
5522 -- header ID has been purged from the system.
5523 -- -------------------------------------------------------------------
5524 Initialize_History_Record (p_cc_header_id => l_cc_header_id,
5525 p_History_Rec => l_history_rec,
5526 x_Return_Status => l_return_status
5527 );
5528 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5529 raise FND_API.G_EXC_ERROR;
5530 END IF;
5531
5532 -- -------------------------------------------------------------------
5533 -- Update the record initialized so that it now indicates that the CC
5534 -- Header ID has been purged.
5535 -- -------------------------------------------------------------------
5536 l_history_rec.purge_date := SYSDATE;
5537 l_history_rec.purged_by := g_update_by;
5538 l_history_rec.purge_done_flag := 'Y';
5539
5540 -- -------------------------------------------------------------------
5541 -- Update the History table indicating that this record has been
5542 -- purged from the system.
5543 -- -------------------------------------------------------------------
5544 Update_History (p_History_Rec => l_history_rec,
5545 x_Return_Status => l_return_status
5546 );
5547
5548 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5549 raise FND_API.G_EXC_ERROR;
5550 END IF;
5551
5552 ELSE
5553
5554 IF (g_debug_mode = 'Y') THEN
5555 g_debug_msg := 'Header ID Not found in Archive History Table for CC Header ID : ' ||
5556 l_cc_header_id || ' SOB ID : ' || g_sob_id ||
5557 '. Thus no Purge can be done for Header ID.';
5558 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5559 END IF;
5560
5561 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5562 p_tokval => g_sob_id);
5563 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE1',
5564 p_tokval => l_cc_header_id);
5565 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE2',
5566 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
5567 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
5568 p_tokval => 'CC');
5569 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5570 p_msgname => 'IGC_NUM_PUR_NO_MATCH_ARC');
5571 raise FND_API.G_EXC_ERROR;
5572
5573 END IF;
5574
5575 -- -------------------------------------------------------------------
5576 -- Close the cursor opened.
5577 -- -------------------------------------------------------------------
5578 IF (c_get_arc_hist_info%ISOPEN) THEN
5579 CLOSE c_get_arc_hist_info;
5580 END IF;
5581
5582 FETCH c_get_purge_candidate
5583 INTO l_cc_header_id;
5584
5585 END LOOP;
5586
5587 ELSE
5588
5589 IF (g_debug_mode = 'Y') THEN
5590 g_debug_msg := 'View returned CC Header ID : ' ||
5591 l_cc_header_id || ' But could not find it in Cursor.';
5592 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5593 END IF;
5594
5595 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5596 p_tokval => g_sob_id);
5597 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE1',
5598 p_tokval => l_cc_header_id);
5599 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE2',
5600 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
5601 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
5602 p_tokval => 'CC');
5603 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5604 p_msgname => 'IGC_NUM_PUR_NO_MATCH_ARC');
5605 raise FND_API.G_EXC_ERROR;
5606
5607 END IF;
5608
5609 -- -------------------------------------------------------------------
5610 -- Close the cursors opened.
5611 -- -------------------------------------------------------------------
5612 IF (c_get_purge_candidate%ISOPEN) THEN
5613 CLOSE c_get_purge_candidate;
5614 END IF;
5615 IF (c_get_arc_hist_info%ISOPEN) THEN
5616 CLOSE c_get_arc_hist_info;
5617 END IF;
5618
5619 RETURN;
5620
5621 -- --------------------------------------------------------------------
5622 -- Exception handler section for the Purge_CC procedure.
5623 -- --------------------------------------------------------------------
5624 EXCEPTION
5625
5626 WHEN FND_API.G_EXC_ERROR THEN
5627 x_Return_Status := FND_API.G_RET_STS_ERROR;
5628 IF (c_get_purge_candidate%ISOPEN) THEN
5629 CLOSE c_get_purge_candidate;
5630 END IF;
5631 IF (c_get_arc_hist_info%ISOPEN) THEN
5632 CLOSE c_get_arc_hist_info;
5633 END IF;
5634 IF (g_excep_level >= g_debug_level ) THEN
5635 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
5636 END IF;
5637 RETURN;
5638
5639 WHEN OTHERS THEN
5640 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
5641 IF (c_get_purge_candidate%ISOPEN) THEN
5642 CLOSE c_get_purge_candidate;
5643 END IF;
5644 IF (c_get_arc_hist_info%ISOPEN) THEN
5645 CLOSE c_get_arc_hist_info;
5646 END IF;
5647 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
5648 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
5649 END IF;
5650 IF ( g_unexp_level >= g_debug_level ) THEN
5651 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
5652 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
5653 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
5654 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
5655 END IF;
5656 RETURN;
5657
5658 END Purge_NON_MRC_Tbls;
5659
5660 --
5661 -- Update_History Procedure is designed for Updating or Inserting the CC ARCHIVE History
5662 -- record into the appropriate History table.
5663 --
5664 -- Parameters :
5665 --
5666 -- p_History_Rec ==> Record to be inserted into the IGC_CC_ARCHIVE_HISTORY table
5667 -- x_Return_Status ==> Status of procedure returned to caller
5668 --
5669 PROCEDURE Update_History
5670 (
5671 p_History_Rec IN igc_cc_archive_history%ROWTYPE, -- History Record
5672 x_Return_Status OUT NOCOPY VARCHAR2 -- Status of procedure
5673 ) IS
5674
5675 -- --------------------------------------------------------------------
5676 -- Declare cursors to be used in this procedure.
5677 -- --------------------------------------------------------------------
5678 CURSOR c_archive_history IS
5679 SELECT CAH.cc_header_id
5680 FROM igc_cc_archive_history CAH
5681 WHERE CAH.cc_header_id = p_History_Rec.cc_header_id
5682 AND CAH.cc_num = p_History_Rec.cc_num
5683 AND CAH.cc_type = p_History_Rec.cc_type;
5684 /* AND CAH.org_id = p_History_Rec.org_id
5685 AND CAH.set_of_books_id = p_History_Rec.set_of_books_id; --Commented for MOAC uptake */
5686
5687 -- --------------------------------------------------------------------
5688 -- Declare local variables to be used in this procedure.
5689 -- --------------------------------------------------------------------
5690 l_header_id igc_cc_archive_history.cc_header_id%TYPE;
5691 l_api_name CONSTANT VARCHAR2(30) := 'Update_History';
5692
5693 l_full_path VARCHAR2(255);
5694 BEGIN
5695
5696 l_full_path := g_path || 'Update_History';
5697
5698 -- --------------------------------------------------------------------
5699 -- Initialize Return status and other local variables.
5700 -- --------------------------------------------------------------------
5701 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
5702
5703 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5704 IF (g_debug_mode = 'Y') THEN
5705 g_debug_msg := ' Calling Update History for CC Header ID : ' ||
5706 p_History_Rec.cc_header_id;
5707 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5708 END IF;
5709
5710 -- --------------------------------------------------------------------
5711 -- Determine if there is already a record present in the table
5712 -- IGC_CC_ARCHIVE_HISTORY. If there is already a record present then
5713 -- update the record otherwise insert a new record.
5714 -- --------------------------------------------------------------------
5715 OPEN c_archive_history;
5716
5717 FETCH c_archive_history
5718 INTO l_header_id;
5719
5720 -- --------------------------------------------------------------------
5721 -- If the record is not found then insert new record into the table.
5722 -- --------------------------------------------------------------------
5723 IF (c_archive_history%NOTFOUND) THEN
5724
5725 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5726 IF (g_debug_mode = 'Y') THEN
5727 g_debug_msg := ' Inserting Archive History Record for CC Header ID : ' ||
5728 p_History_Rec.cc_header_id ||
5729 ' Set Of Books ID : ' || p_History_Rec.set_of_books_id;
5730 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5731 END IF;
5732
5733 INSERT
5734 INTO IGC_CC_ARCHIVE_HISTORY_ALL
5735 ( cc_header_id,
5736 set_of_books_id,
5737 org_id,
5738 parent_header_id,
5739 cc_num,
5740 cc_type,
5741 archive_date,
5742 archived_by,
5743 archive_done_flag,
5744 purge_date,
5745 purged_by,
5746 purge_done_flag,
5747 num_acct_lines_arc,
5748 num_det_pf_lines_arc,
5749 num_mc_acct_lines_arc,
5750 num_mc_det_pf_lines_arc,
5751 last_cc_activity_date,
5752 user_req_last_activity_date,
5753 last_update_date,
5754 last_updated_by,
5755 last_update_login,
5756 created_by,
5757 creation_date
5758 )
5759 VALUES
5760 ( p_History_Rec.cc_header_id,
5761 p_History_Rec.set_of_books_id,
5762 p_History_Rec.org_id,
5763 NVL (p_History_Rec.parent_header_id, 0),
5764 p_History_Rec.cc_num,
5765 p_History_Rec.cc_type,
5766 p_History_Rec.archive_date,
5767 p_History_Rec.archived_by,
5768 p_History_Rec.archive_done_flag,
5769 p_History_Rec.purge_date,
5770 p_History_Rec.purged_by,
5771 p_History_Rec.purge_done_flag,
5772 NVL (p_History_Rec.num_acct_lines_arc, 0),
5773 NVL (p_History_Rec.num_det_pf_lines_arc, 0),
5774 NVL (p_History_Rec.num_mc_acct_lines_arc, 0),
5775 NVL (p_History_Rec.num_mc_det_pf_lines_arc, 0),
5776 p_History_Rec.last_cc_activity_date,
5777 p_History_Rec.user_req_last_activity_date,
5778 p_History_Rec.last_update_date,
5779 p_History_Rec.last_updated_by,
5780 p_History_Rec.last_update_login,
5781 p_History_Rec.created_by,
5782 p_History_Rec.creation_date
5783 );
5784
5785 IF (SQL%ROWCOUNT <> 1) THEN
5786 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5787 p_tokval => p_History_Rec.set_of_books_id);
5788 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE',
5789 p_tokval => p_History_Rec.cc_num);
5790 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
5791 p_tokval => 'CC');
5792 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5793 p_msgname => 'IGC_INSERT_ARC_PUR_HISTORY');
5794 raise FND_API.G_EXC_ERROR;
5795 END IF;
5796
5797 ELSE -- Update existing record
5798
5799 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5800 IF (g_debug_mode = 'Y') THEN
5801 g_debug_msg := ' Updating Archive History Record for CC Header ID : ' ||
5802 p_History_Rec.cc_header_id ||
5803 ' Set Of Books ID : ' || p_History_Rec.set_of_books_id;
5804 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5805 END IF;
5806
5807 -- --------------------------------------------------------------------
5808 -- Update the existing fields in the history table for non MRC record.
5809 -- --------------------------------------------------------------------
5810 UPDATE IGC_CC_ARCHIVE_HISTORY AH
5811 SET last_update_date = p_History_Rec.last_update_date,
5812 last_updated_by = p_History_Rec.last_updated_by,
5813 purge_done_flag = p_History_Rec.purge_done_flag,
5814 purge_date = p_History_Rec.purge_date,
5815 purged_by = p_History_Rec.purged_by,
5816 archive_done_flag = p_History_Rec.archive_done_flag,
5817 archive_date = p_History_Rec.archive_date,
5818 archived_by = p_History_Rec.archived_by,
5819 num_acct_lines_arc = NVL (p_History_Rec.num_acct_lines_arc, 0),
5820 num_det_pf_lines_arc = NVL (p_History_Rec.num_det_pf_lines_arc, 0),
5821 num_mc_acct_lines_arc = NVL (p_History_Rec.num_mc_acct_lines_arc, 0),
5822 num_mc_det_pf_lines_arc = NVL (p_History_Rec.num_mc_det_pf_lines_arc, 0)
5823 WHERE cc_header_id = p_History_Rec.cc_header_id
5824 /* AND org_id = p_History_Rec.org_id
5825 AND set_of_books_id = p_History_Rec.set_of_books_id
5826 --Commented for MOAC uptake */
5827 AND NVL(parent_header_id, 0) = NVL(p_History_Rec.parent_header_id, 0)
5828 AND cc_num = p_History_Rec.cc_num
5829 AND cc_type = p_History_Rec.cc_type;
5830
5831 IF (SQL%ROWCOUNT <> 1) THEN
5832 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5833 p_tokval => p_History_Rec.set_of_books_id);
5834 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE',
5835 p_tokval => p_History_Rec.cc_num);
5836 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
5837 p_tokval => 'CC');
5838 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5839 p_msgname => 'IGC_UPDATE_ARC_PUR_HISTORY');
5840 raise FND_API.G_EXC_ERROR;
5841 END IF;
5842
5843 END IF;
5844
5845 -- --------------------------------------------------------------------
5846 -- Close all cursors used by this function here.
5847 -- --------------------------------------------------------------------
5848 IF (c_archive_history%ISOPEN) THEN
5849 CLOSE c_archive_history;
5850 END IF;
5851
5852 RETURN;
5853
5854 -- --------------------------------------------------------------------
5855 -- Exception handler section for the Update_History procedure.
5856 -- --------------------------------------------------------------------
5857 EXCEPTION
5858
5859 WHEN NO_DATA_FOUND THEN
5860 x_Return_Status := FND_API.G_RET_STS_ERROR;
5861 IF (c_archive_history%ISOPEN) THEN
5862 CLOSE c_archive_history;
5863 END IF;
5864 IF (g_excep_level >= g_debug_level ) THEN
5865 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
5866 END IF;
5867 RETURN;
5868
5869 WHEN FND_API.G_EXC_ERROR THEN
5870 x_Return_Status := FND_API.G_RET_STS_ERROR;
5871 IF (c_archive_history%ISOPEN) THEN
5872 CLOSE c_archive_history;
5873 END IF;
5874 IF (g_excep_level >= g_debug_level ) THEN
5875 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
5876 END IF;
5877 RETURN;
5878
5879 WHEN OTHERS THEN
5880 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
5881 IF (c_archive_history%ISOPEN) THEN
5882 CLOSE c_archive_history;
5883 END IF;
5884 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
5885 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
5886 END IF;
5887 IF ( g_unexp_level >= g_debug_level ) THEN
5888 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
5889 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
5890 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
5891 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
5892 END IF;
5893 RETURN;
5894
5895 END Update_History;
5896
5897 --
5898 -- Validate_Inputs Procedure is designed to ensure that the inputs that were given by
5899 -- the user are valid and will enable this Archive / Purge procedure to work properly.
5900 --
5901 -- Parameters :
5902 --
5903 -- x_return_status ==> Status returned from Procedure.
5904 --
5905 PROCEDURE Validate_Inputs (
5906 x_return_status OUT NOCOPY VARCHAR2
5907 ) IS
5908
5909 -- --------------------------------------------------------------------
5910 -- Define local variables to be used
5911 -- --------------------------------------------------------------------
5912 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
5913 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Inputs';
5914
5915 -- --------------------------------------------------------------------
5916 -- Define cursors to be used in main archive/purge procedure
5917 -- --------------------------------------------------------------------
5918 CURSOR c_validate_sob_org IS
5919 SELECT ICCH.cc_header_id
5920 FROM igc_cc_headers ICCH
5921 WHERE ICCH.set_of_books_id = g_sob_id
5922 AND ICCH.org_id = g_org_id;
5923
5924 l_full_path VARCHAR2(255);
5925
5926 BEGIN
5927
5928 l_full_path := g_path || 'Validate_Inputs';
5929
5930 -- --------------------------------------------------------------------
5931 -- Initialize local variables.
5932 -- --------------------------------------------------------------------
5933 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
5934
5935 -- --------------------------------------------------------------------
5936 -- Make sure that the input mode of the process is a valid input. The
5937 -- set of values allowed are 'A' Archive or 'P' Purge.
5938 -- --------------------------------------------------------------------
5939 IF (g_mode NOT IN ('AR', 'PP', 'PU')) THEN
5940 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5941 IF (g_debug_mode = 'Y') THEN
5942 g_debug_msg := ' Invalid Archive / Purge mode passed in : ' || g_mode ||
5943 ' Exiting process.';
5944 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5945 END IF;
5946 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5947 p_tokval => g_sob_id);
5948 IGC_MSGS_PKG.message_token (p_tokname => 'VALUE',
5949 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
5950 IGC_MSGS_PKG.message_token (p_tokname => 'MODE',
5951 p_tokval => g_mode);
5952 IGC_MSGS_PKG.message_token (p_tokname => 'COMPONENT',
5953 p_tokval => 'CC');
5954 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5955 p_msgname => 'IGC_INVALID_ARC_PUR_MODE');
5956 raise FND_API.G_EXC_ERROR;
5957 END IF;
5958
5959 -- --------------------------------------------------------------------
5960 -- Make sure that the set of books ID is valid and that there are
5961 -- records present for the SOB and the ORG ID given.
5962 -- --------------------------------------------------------------------
5963 IF g_sob_id is NULL THEN
5964
5965 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5966 IF (g_debug_mode = 'Y') THEN
5967 g_debug_msg := ' Set of books ID is NULL. Exiting.';
5968 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5969 END IF;
5970
5971 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5972 p_tokval => 'NULL');
5973 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
5974 p_msgname => 'IGC_INVALID_SOB_ARCHIVE');
5975 g_validation_error := TRUE;
5976
5977 ELSE
5978
5979 -- -------------------------------------------------------------------
5980 -- Make sure that there are records in the IGC_CC_HEADERS table that
5981 -- can be reviewed for the SOB ID and the ORG ID.
5982 -- -------------------------------------------------------------------
5983 OPEN c_validate_sob_org;
5984 FETCH c_validate_sob_org
5985 INTO l_cc_header_id;
5986
5987 IF (c_validate_sob_org%NOTFOUND) THEN
5988
5989 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
5990 IF (g_debug_mode = 'Y') THEN
5991 g_debug_msg := ' Set of books ID : ' || g_sob_id ||
5992 ' and ORG ID : ' || g_org_id ||
5993 ' Combination not found in IGC_CC_HEADERS. Exiting.';
5994 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
5995 END IF;
5996
5997 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
5998 p_tokval => g_sob_id);
5999 IGC_MSGS_PKG.message_token (p_tokname => 'ORG_ID',
6000 p_tokval => g_org_id);
6001 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
6002 p_msgname => 'IGC_NO_SOB_ORG_COMBO');
6003 g_validation_error := TRUE;
6004 END IF;
6005
6006 -- --------------------------------------------------------------------
6007 -- Make sure that the cursor opened for validation has been closed.
6008 -- --------------------------------------------------------------------
6009 CLOSE c_validate_sob_org;
6010
6011 END IF;
6012
6013 -- --------------------------------------------------------------------
6014 -- Make sure that the last activity date that has been given is not a
6015 -- date in the future. This is not a valid date for archiving or
6016 -- purging.
6017 -- --------------------------------------------------------------------
6018 IF (sign(sysdate - nvl(g_last_activity_date, sysdate)) < 0) THEN
6019
6020 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
6021 IF (g_debug_mode = 'Y') THEN
6022 g_debug_msg := ' Last Activity date is not valid. Future Date : ' ||
6023 g_last_activity_date;
6024 Output_Debug (l_full_path, p_debug_msg => g_debug_msg);
6025 END IF;
6026
6027 IGC_MSGS_PKG.message_token (p_tokname => 'SOB_ID',
6028 p_tokval => g_sob_id);
6029 IGC_MSGS_PKG.message_token (p_tokname => 'INPUT_DATE',
6030 p_tokval => FND_DATE.DATE_TO_CHARDATE (g_last_activity_date));
6031 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
6032 p_msgname => 'IGC_INVALID_DATE_INPUT');
6033 g_validation_error := TRUE;
6034 END IF;
6035
6036 RETURN;
6037
6038 -- --------------------------------------------------------------------
6039 -- Exception handler section for the Validate_Inputs procedure.
6040 -- --------------------------------------------------------------------
6041 EXCEPTION
6042
6043 WHEN NO_DATA_FOUND THEN
6044 x_Return_Status := FND_API.G_RET_STS_ERROR;
6045 IF (c_validate_sob_org%ISOPEN) THEN
6046 CLOSE c_validate_sob_org;
6047 END IF;
6048 IF (g_excep_level >= g_debug_level ) THEN
6049 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
6050 END IF;
6051 RETURN;
6052
6053 WHEN FND_API.G_EXC_ERROR THEN
6054 x_Return_Status := FND_API.G_RET_STS_ERROR;
6055 IF (c_validate_sob_org%ISOPEN) THEN
6056 CLOSE c_validate_sob_org;
6057 END IF;
6058 IF (g_excep_level >= g_debug_level ) THEN
6059 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
6060 END IF;
6061 RETURN;
6062
6063 WHEN OTHERS THEN
6064 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
6065 IF (c_validate_sob_org%ISOPEN) THEN
6066 CLOSE c_validate_sob_org;
6067 END IF;
6068 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
6069 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
6070 END IF;
6071 IF ( g_unexp_level >= g_debug_level ) THEN
6072 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
6073 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
6074 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
6075 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
6076 END IF;
6077 RETURN;
6078
6079 END Validate_Inputs;
6080
6081 END IGC_CC_ARCHIVE_PURGE_PKG;
6082