[Home] [Help]
1: Package body pa_purge as
2: /* $Header: PAXPRMNB.pls 120.4 2007/12/09 10:24:43 vvjoshi ship $ */
3:
4: -- Start of comments
5: -- API name : Purge_Project
77: l_project_status_code VARCHAR2(30);
78: l_project_type_class pa_project_types_all.project_type_class_code%TYPE; -- Added for bug 3583748
79: begin
80: l_old_err_stack := X_err_stack;
81: X_err_stack := X_err_stack ||'->pa_purge.purge_project';
82: X_err_stack := X_err_stack ||'Batch Id: '||p_batch_id || 'Project Id: '||p_project_id ;
83: X_err_code := 0;
84:
85: pa_debug.debug(X_err_stack);
85: pa_debug.debug(X_err_stack);
86:
87: -- Call user defined extension to purge customer specific tables
88: pa_debug.debug('--Calling client extension procedure');
89: pa_purge_extn.pa_purge_client_extn(
90: p_purge_batch_id => p_batch_id,
91: p_project_id => p_project_id,
92: p_purge_release => p_purge_release,
93: p_txn_through_date => p_txn_to_date,
106: --
107:
108: IF P_purge_summary_flag = 'Y' THEN
109: if P_active_closed_flag = 'C' then
110: pa_debug.debug('--Calling procedure pa_purge_summary.pa_summary_main_purge');
111: pa_purge_summary.pa_summary_main_purge (
112: p_purge_batch_id => p_batch_id,
113: p_project_id => p_project_id,
114: p_purge_release => p_purge_release,
107:
108: IF P_purge_summary_flag = 'Y' THEN
109: if P_active_closed_flag = 'C' then
110: pa_debug.debug('--Calling procedure pa_purge_summary.pa_summary_main_purge');
111: pa_purge_summary.pa_summary_main_purge (
112: p_purge_batch_id => p_batch_id,
113: p_project_id => p_project_id,
114: p_purge_release => p_purge_release,
115: p_txn_to_date => p_txn_to_date,
126: --
127: /***
128: IF P_purge_budgets_flag = 'Y' THEN
129: if P_active_closed_flag = 'C' then
130: pa_debug.debug('--Calling procedure pa_purge_budget.pa_budget_main_purge');
131: pa_purge_budget.pa_budget_main_purge (
132: p_batch_id,
133: p_project_id,
134: p_purge_release,
127: /***
128: IF P_purge_budgets_flag = 'Y' THEN
129: if P_active_closed_flag = 'C' then
130: pa_debug.debug('--Calling procedure pa_purge_budget.pa_budget_main_purge');
131: pa_purge_budget.pa_budget_main_purge (
132: p_batch_id,
133: p_project_id,
134: p_purge_release,
135: p_txn_to_date,
147: --
148:
149: IF P_purge_capital_flag = 'Y' THEN
150: if P_active_closed_flag = 'C' then
151: pa_debug.debug('--Calling procedure pa_purge_capital.pa_capital_main_purge');
152: pa_purge_capital.pa_capital_main_purge (
153: p_purge_batch_id => p_batch_id,
154: p_project_id => p_project_id,
155: p_purge_release => p_purge_release,
148:
149: IF P_purge_capital_flag = 'Y' THEN
150: if P_active_closed_flag = 'C' then
151: pa_debug.debug('--Calling procedure pa_purge_capital.pa_capital_main_purge');
152: pa_purge_capital.pa_capital_main_purge (
153: p_purge_batch_id => p_batch_id,
154: p_project_id => p_project_id,
155: p_purge_release => p_purge_release,
156: p_txn_to_date => p_txn_to_date,
178: and project_id = p_project_id;
179:
180: If p_active_closed_flag = 'C'
181: and l_project_type_class = 'CONTRACT' Then -- added for bug 3583748
182: pa_debug.debug('--Calling procedure pa_purge_billing.pa_billing_main_purge ');
183: pa_purge_billing.pa_billing_main_purge (
184: p_purge_batch_id => p_batch_id,
185: p_project_id => p_project_id,
186: p_purge_release => p_purge_release,
179:
180: If p_active_closed_flag = 'C'
181: and l_project_type_class = 'CONTRACT' Then -- added for bug 3583748
182: pa_debug.debug('--Calling procedure pa_purge_billing.pa_billing_main_purge ');
183: pa_purge_billing.pa_billing_main_purge (
184: p_purge_batch_id => p_batch_id,
185: p_project_id => p_project_id,
186: p_purge_release => p_purge_release,
187: p_txn_to_date => p_txn_to_date,
195: /* Bug#2416385 Code added for the Phase III of Archive and Purge, starts here */
196:
197: -- Call the Intercomapany and Interproject Billing purge procedure
198: --
199: pa_debug.debug('--Calling procedure pa_purge_icip.PA_DraftInvDetails');
200: pa_purge_icip.PA_DraftInvDetails (
201: p_purge_batch_id => p_batch_id,
202: p_project_id => p_project_id,
203: p_purge_release => p_purge_release,
196:
197: -- Call the Intercomapany and Interproject Billing purge procedure
198: --
199: pa_debug.debug('--Calling procedure pa_purge_icip.PA_DraftInvDetails');
200: pa_purge_icip.PA_DraftInvDetails (
201: p_purge_batch_id => p_batch_id,
202: p_project_id => p_project_id,
203: p_purge_release => p_purge_release,
204: p_txn_to_date => p_txn_to_date,
209: X_Err_Code => X_Err_Code);
210:
211: /* Bug#2441479 Code added for the PJR Archive and Purge, starts here */
212:
213: pa_debug.debug('--Calling procedure PA_PURGE_PJR_TXNS.PA_REQUIREMENTS_PURGE');
214: pa_purge_pjr_txns.pa_requirements_purge (
215: p_purge_batch_id => p_batch_id,
216: p_project_id => p_project_id,
217: p_purge_release => p_purge_release,
210:
211: /* Bug#2441479 Code added for the PJR Archive and Purge, starts here */
212:
213: pa_debug.debug('--Calling procedure PA_PURGE_PJR_TXNS.PA_REQUIREMENTS_PURGE');
214: pa_purge_pjr_txns.pa_requirements_purge (
215: p_purge_batch_id => p_batch_id,
216: p_project_id => p_project_id,
217: p_purge_release => p_purge_release,
218: p_txn_to_date => p_txn_to_date,
221: X_Err_Stack => X_Err_Stack,
222: X_Err_Stage => X_Err_Stage,
223: X_Err_Code => X_Err_Code);
224:
225: pa_debug.debug('--Calling procedure PA_PURGE_PJR_TXNS.PA_ASSIGNMENTS_PURGE');
226: pa_purge_pjr_txns.pa_assignments_purge (
227: p_purge_batch_id => p_batch_id,
228: p_project_id => p_project_id,
229: p_purge_release => p_purge_release,
222: X_Err_Stage => X_Err_Stage,
223: X_Err_Code => X_Err_Code);
224:
225: pa_debug.debug('--Calling procedure PA_PURGE_PJR_TXNS.PA_ASSIGNMENTS_PURGE');
226: pa_purge_pjr_txns.pa_assignments_purge (
227: p_purge_batch_id => p_batch_id,
228: p_project_id => p_project_id,
229: p_purge_release => p_purge_release,
230: p_txn_to_date => p_txn_to_date,
238: /* Bug#2416385 Code added for the Phase III of Archive and Purge, ends here */
239:
240: -- Call the main costing purge procedure
241: --
242: pa_debug.debug('--Calling procedure pa_purge_costing.pa_costing_main_purge');
243: pa_purge_costing.pa_costing_main_purge (
244: p_purge_batch_id => p_batch_id,
245: p_project_id => p_project_id,
246: p_purge_release => p_purge_release,
239:
240: -- Call the main costing purge procedure
241: --
242: pa_debug.debug('--Calling procedure pa_purge_costing.pa_costing_main_purge');
243: pa_purge_costing.pa_costing_main_purge (
244: p_purge_batch_id => p_batch_id,
245: p_project_id => p_project_id,
246: p_purge_release => p_purge_release,
247: p_txn_to_date => p_txn_to_date,
255:
256: -- Call user defined extension to purge customer specific tables after the
257: -- main tables are purged
258: pa_debug.debug('--Calling client extension procedure');
259: pa_purge_extn.pa_purge_client_extn(
260: p_purge_batch_id => p_batch_id,
261: p_project_id => p_project_id,
262: p_purge_release => p_purge_release,
263: p_txn_through_date => p_txn_to_date,
269: X_Err_Code => X_Err_Code);
270:
271:
272: -- Set completed status for the project
273: pa_debug.debug('--Before update of pa_purge_projects batch id :
274: '|| p_batch_id || ' project id : '|| p_project_id);
275:
276: update pa_purge_projects
277: set
272: -- Set completed status for the project
273: pa_debug.debug('--Before update of pa_purge_projects batch id :
274: '|| p_batch_id || ' project id : '|| p_project_id);
275:
276: update pa_purge_projects
277: set
278: purged_date=sysdate
279: ,last_update_date=sysdate
280: ,last_updated_by=fnd_global.user_id
287: and project_id = p_project_id;
288:
289: commit;
290:
291: pa_debug.debug('--After update of pa_purge_projects batch id :
292: '|| p_batch_id || ' project id : '|| p_project_id);
293:
294: -- For closed projects set the project status to fully purged or partially purged
295: -- For active projects set the project status code to old project status code
297: pa_debug.debug('--Before update of pa_projects_all batch id :
298: '|| p_batch_id || ' project id : '|| p_project_id);
299:
300:
301: l_project_status_code := pa_purge.get_post_purge_status(p_project_id,p_batch_id);
302:
303: update pa_projects_all
304: set project_status_code = l_project_status_code
305: where project_id = p_project_id;
313: WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
314: RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
315:
316: WHEN OTHERS THEN
317: pa_debug.debug('Error Procedure Name := PA_PURGE.PURGE_PROJECT' );
318: pa_debug.debug('Error stage is '||x_err_stage );
319: pa_debug.debug('Error stack is '||x_err_stack );
320: pa_debug.debug(SQLERRM);
321: PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
359: COUNT_MRC_SET_OF_BOOKS NUMBER;
360:
361: Begin
362:
363: l_err_stack := '->pa_purge.purge';
364: l_err_code := 0;
365: l_err_stage := 'Purge for batch_id: ' || to_char(p_batch_id);
366: l_project_id := -9999;
367:
383: pa_debug.debug('Calculate ARPUR_Commit_Size and ARPUR_MRC_Commit_Size');
384:
385:
386: -- Set working status for the batch
387: pa_debug.debug('Before update of pa_purge_batches to pending');
388: Update pa_purge_batches
389: Set batch_status_code='P' ,
390: request_id = fnd_global.conc_request_id
391: Where purge_batch_id = p_batch_id;
384:
385:
386: -- Set working status for the batch
387: pa_debug.debug('Before update of pa_purge_batches to pending');
388: Update pa_purge_batches
389: Set batch_status_code='P' ,
390: request_id = fnd_global.conc_request_id
391: Where purge_batch_id = p_batch_id;
392:
390: request_id = fnd_global.conc_request_id
391: Where purge_batch_id = p_batch_id;
392:
393: Commit;
394: pa_debug.debug('After update of pa_purge_batches to pending');
395:
396: -- Lock the batch row
397: pa_debug.debug('Before locking table pa_purge_batches');
398: Select active_closed_flag , purge_release
393: Commit;
394: pa_debug.debug('After update of pa_purge_batches to pending');
395:
396: -- Lock the batch row
397: pa_debug.debug('Before locking table pa_purge_batches');
398: Select active_closed_flag , purge_release
399: Into l_active_closed_flag , l_purge_release
400: From pa_purge_batches
401: Where purge_batch_id = p_batch_id
396: -- Lock the batch row
397: pa_debug.debug('Before locking table pa_purge_batches');
398: Select active_closed_flag , purge_release
399: Into l_active_closed_flag , l_purge_release
400: From pa_purge_batches
401: Where purge_batch_id = p_batch_id
402: and request_id = fnd_global.conc_request_id
403: For update;
404:
401: Where purge_batch_id = p_batch_id
402: and request_id = fnd_global.conc_request_id
403: For update;
404:
405: pa_debug.debug('After locking table pa_purge_batches');
406:
407: -- Select the projects in the batch that have not been purged
408: -- (Purged date is Null)
409:
423: txn_to_date,
424: last_project_status_code,
425: next_pp_project_status_code,
426: next_p_project_status_code
427: From pa_purge_projects
428: Where purge_batch_id = p_Batch_Id
429: And purged_date is NULL
430: Order By project_id
431: )
433:
434: l_project_id := pp_rec.project_id ;
435:
436: -- For each project call procedure to purge
437: pa_debug.debug('Calling pa_purge.purge_project with project_id : '||pp_rec.project_id);
438: pa_purge.purge_project(
439: p_batch_id => p_batch_id,
440: p_project_id => pp_rec.project_id,
441: p_active_closed_flag => l_active_closed_flag,
434: l_project_id := pp_rec.project_id ;
435:
436: -- For each project call procedure to purge
437: pa_debug.debug('Calling pa_purge.purge_project with project_id : '||pp_rec.project_id);
438: pa_purge.purge_project(
439: p_batch_id => p_batch_id,
440: p_project_id => pp_rec.project_id,
441: p_active_closed_flag => l_active_closed_flag,
442: p_purge_release => l_purge_release,
466: -- will be done at a later point of time
467: /*
468: pa_debug.debug('*-> About to purge expenditures ');
469: l_err_stage := 'About to purge expenditures without any expenditure items ';
470: pa_purge_costing.PA_Expenditures1(p_purge_batch_id => p_batch_id,
471: p_project_id => l_project_id,
472: p_purge_release => l_purge_release,
473: p_archive_flag => 'Y',
474: p_commit_size => p_commit_size,
478: ) ;
479:
480: pa_debug.debug('*-> About to purge routings ');
481: l_err_stage := 'About to purge routing records ';
482: pa_purge_costing.PA_Routings1(p_purge_batch_id => p_batch_id,
483: p_project_id => l_project_id,
484: p_purge_release => l_purge_release,
485: p_archive_flag => 'Y',
486: p_commit_size => p_commit_size,
490: ) ;
491:
492: */
493: -- Set completed status for the batch
494: pa_debug.debug('Before update of pa_purge_batches to complete ');
495: Update pa_purge_batches
496: Set batch_status_code='C'
497: ,purged_date=sysdate
498: ,last_update_date=sysdate
491:
492: */
493: -- Set completed status for the batch
494: pa_debug.debug('Before update of pa_purge_batches to complete ');
495: Update pa_purge_batches
496: Set batch_status_code='C'
497: ,purged_date=sysdate
498: ,last_update_date=sysdate
499: ,last_updated_by=fnd_global.user_id
500: ,last_update_login=fnd_global.login_id
501: Where purge_batch_id = p_batch_id;
502:
503: Commit;
504: pa_debug.debug('After update of pa_purge_batches to complete');
505:
506:
507: Exception
508:
568: Begin
569:
570: l_old_err_stack := x_err_stack;
571:
572: X_err_stack := X_err_stack ||'->pa_purge.CommitProcess';
573: X_err_stack := X_err_stack ||'Batch Id: '||p_purge_batch_id || 'Project Id: '||p_project_id ;
574:
575: pa_debug.debug(X_err_stack);
576:
576:
577: -- update the pa_project_details with statistics
578: pa_debug.debug('Before update of pa_project_details' );
579:
580: Update PA_PURGE_PRJ_DETAILS
581: Set num_recs_purged = nvl(num_recs_purged,0) + nvl(p_NoOfRecordsDel,0)
582: ,num_recs_archived = nvl(num_recs_archived,0) + nvl(p_NoOfRecordsIns,0)
583: ,last_update_date=sysdate
584: ,last_updated_by=fnd_global.user_id
596: --
597: IF SQL%Rowcount = 0 Then
598:
599: pa_debug.debug('Before insert into pa_project_details' );
600: Insert into PA_PURGE_PRJ_DETAILS
601: (
602: purge_batch_id,
603: project_id,
604: table_name,
635: pa_debug.debug('After insert into pa_project_details' );
636: END IF;
637:
638: IF p_MRC_table_name is not null then
639: X_err_stack := X_err_stack ||'->pa_purge.CommitProcess';
640: X_err_stack := X_err_stack ||'Batch Id: '||p_purge_batch_id || 'Project Id: '||
641: p_project_id || 'MRC details' ;
642:
643: pa_debug.debug(X_err_stack);
644:
645: -- update the pa_project_details with MRC statistics
646: pa_debug.debug('Before update of pa_project_details for MRC' );
647:
648: Update PA_PURGE_PRJ_DETAILS
649: Set num_recs_purged = nvl(num_recs_purged,0) + nvl(p_MRC_NoOfRecordsDel,0)
650: ,num_recs_archived = nvl(num_recs_archived,0) + nvl(p_MRC_NoOfRecordsIns,0)
651: ,last_update_date=sysdate
652: ,last_updated_by=fnd_global.user_id
664: --
665: IF SQL%Rowcount = 0 Then
666:
667: pa_debug.debug('Before insert MRC into pa_project_details' );
668: Insert into PA_PURGE_PRJ_DETAILS
669: (
670: purge_batch_id,
671: project_id,
672: table_name,
710: --
711: /* Added the condition for the bug#2510609 */
712: if p_table_name not in ('PA_FORECAST_ITEMS', 'PA_FORECAST_ITEM_DETAILS', 'PA_FI_AMOUNT_DETAILS') then
713:
714: pa_debug.debug('Before locking pa_purge_batches again');
715:
716: Select 'x'
717: Into l_dummy
718: From pa_purge_batches
714: pa_debug.debug('Before locking pa_purge_batches again');
715:
716: Select 'x'
717: Into l_dummy
718: From pa_purge_batches
719: Where purge_batch_id = p_purge_batch_id
720: and request_id = fnd_global.conc_request_id
721: For update;
722:
719: Where purge_batch_id = p_purge_batch_id
720: and request_id = fnd_global.conc_request_id
721: For update;
722:
723: pa_debug.debug('After locking pa_purge_batches again');
724:
725: end if;
726:
727: x_err_stack := l_old_err_stack;
730: WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
731: RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
732:
733: WHEN OTHERS THEN
734: pa_debug.debug('Error Procedure Name := PA_PURGE.CommitProcess' );
735: pa_debug.debug('Error stage is '||x_err_stage );
736: pa_debug.debug('Error stack is '||x_err_stack );
737: pa_debug.debug(SQLERRM);
738: PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
765:
766: /* Changed the data length of l_next_p_project_status_code and l_next_pp_project_status_code to 90 for UTF8.
767: l_last_project_status_code VARCHAR2(30);
768: */
769: l_last_project_status_code pa_purge_projects.Last_project_status_code%TYPE;
770: l_project_type_class_code VARCHAR2(30);
771:
772:
773: CURSOR next_p_pp_status IS
771:
772:
773: CURSOR next_p_pp_status IS
774: SELECT next_p_project_status_code, next_pp_project_status_code
775: FROM pa_purge_projects
776: WHERE purge_batch_id = p_batch_id
777: AND project_id = p_project_id;
778:
779: /* Changed the data length of l_next_p_project_status_code and l_next_pp_project_status_code to 90 for UTF8.
780: l_next_p_project_status_code VARCHAR2(30);
781: l_next_pp_project_status_code VARCHAR2(30);
782: */
783:
784: l_next_p_project_status_code pa_purge_projects.next_p_project_status_code%TYPE;
785: l_next_pp_project_status_code pa_purge_projects.next_pp_project_status_code%TYPE;
786:
787: BEGIN
788:
781: l_next_pp_project_status_code VARCHAR2(30);
782: */
783:
784: l_next_p_project_status_code pa_purge_projects.next_p_project_status_code%TYPE;
785: l_next_pp_project_status_code pa_purge_projects.next_pp_project_status_code%TYPE;
786:
787: BEGIN
788:
789: pa_debug.debug(' In function get_post_purge_status ');
789: pa_debug.debug(' In function get_post_purge_status ');
790:
791: Select active_closed_flag
792: into l_active_closed_flag
793: from pa_purge_batches
794: where purge_batch_id = p_batch_id;
795:
796: IF l_active_closed_flag = 'C' THEN
797: -- Select MAX of the flags for all rows of the project
806: Into l_purge_actuals_flag,
807: l_purge_capital_flag,
808: l_purge_budgets_flag,
809: l_purge_summary_flag
810: From pa_purge_projects
811: Where project_id = p_project_id
812: And purged_date is not null;
813:
814: OPEN next_p_pp_status;
815: FETCH next_p_pp_status into l_next_p_project_status_code,
816: l_next_pp_project_status_code;
817: CLOSE next_p_pp_status;
818:
819: -- Here if the Project is not a Capital Project then "purge_capital_flag" On pa_purge_projects
820: -- Will not be Y and will return the status as 'PARTIALLY PURGED' as there is no question
821: -- of purging any capital data for non capital project. So is project is NON CAPITAL then
822: -- traeting this as it has been purged for capital data completely.
823:
846: End if;
847: ELSE
848: Select LAST_PROJECT_STATUS_CODE
849: Into l_last_project_status_code
850: From pa_purge_projects
851: Where project_id = p_project_id
852: And purge_batch_id = p_batch_id;
853:
854: Return (l_last_project_status_code);
859: WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
860: RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
861:
862: WHEN OTHERS THEN
863: pa_debug.debug('Error functio Name := PA_PURGE.get_post_purge_status ' );
864: pa_debug.debug(SQLERRM);
865: PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
866:
867: RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
867: RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
868:
869: end get_post_purge_status ;
870:
871: end pa_purge ; /*Package Body*/