[Home] [Help]
7: -- APR-12-99 Shanif Rollup logic modified for Performance improvement
8: -- MAY-19-00 Mohnish Included the changes sent by Shanif for fixing bug 1221390, going to GL_PERIOD_STATUSES instead of PA_PERIODS_V for GL impl_opt
9: -- MAY-19-00 Mohnish Ported changes for 1265148 from R11.0
10: -- Added wbs_level to the 2 cursors Res_accum_Cur and
11: -- PA_Txn_Accum_Cur. Added wbs_level to the order by
12: -- of both for fixing bug 1265148
13: --
14: ---
15: --- 15-OCT-2001 jwhite Changed application_id = 101 to
29: -- package did not make a distinction as to whether
30: -- summarization was processing for GL or PA periods.
31: --
32: -- The logic simply matched the GL and PA periods for the
33: -- pa_txn_accum record to the current reporting period. If EITHER
34: -- period equated to the current reportintg period, PTD
35: -- amounts were processed.
36: --
37: -- 2) Using transaction import for December 2001 data, the client accidently
56: -- 08-Oct-2003 gjain Bug 3147957: Replaced the usage of TO_NUMBER(SUBSTR(USERENV('CLIENT_INFO'),1,10))
57: -- with cursor parameter p_org_id. This paramter is passed on the basis of valus returned
58: -- by LTRIM(RTRIM(SUBSTR(USERENV('CLIENT_INFO'),1,10)))
59: -- 03-may-2004 dkala Added migration_code <> 'N'.
60: -- 07-Mar-2004 sacupta Bug 4195598. Cursors Res_accum_Cur and PA_Txn_Accum_Cur are modified
61: -- in procedure PROCESS_TXN_ACCUM. The reference to GL_PERIOD_STATUSES
62: -- table is replaced by GL_PERIODS. Accordingly the condition to join
63: -- this table with PA_IMPLEMENTATIONS has changed. Now period_set_name
64: -- and period_type is used to join GL_PERIODS and PA_IMPLIMENTATIONS
66: -- previously to join GL_PERIOD_STATUSES with PA_IMPLEMENTAIONS.
67: -- Refer bug for further details.
68: --
69: -- 05-Aug-2005 sacgupta Bug 4532088. Added trunc to the condition trunc(gps.END_DATE) <= x_current_end_date
70: -- in procedure Process_Txn_Accum, cursors Res_accum_Cur and PA_Txn_Accum_Cur
71: -- 30-Sep-2005 pkanupar Bug4631058: In the procedure PROCESS_TXN_ACCUM, in the cursors Res_accum_Cur
72: -- and PA_Txn_Accum_Cur, removed the check for 'PERIOD_TYPE' between GL_PERIODS
73: -- and PA_IMPLIMENTATIONS.
74: -- 16-Feb-2006 djoseph Bug 5019025 : In the procedure PROCESS_TXN_ACCUM, in the cursors Res_accum_Cur
68: --
69: -- 05-Aug-2005 sacgupta Bug 4532088. Added trunc to the condition trunc(gps.END_DATE) <= x_current_end_date
70: -- in procedure Process_Txn_Accum, cursors Res_accum_Cur and PA_Txn_Accum_Cur
71: -- 30-Sep-2005 pkanupar Bug4631058: In the procedure PROCESS_TXN_ACCUM, in the cursors Res_accum_Cur
72: -- and PA_Txn_Accum_Cur, removed the check for 'PERIOD_TYPE' between GL_PERIODS
73: -- and PA_IMPLIMENTATIONS.
74: -- 16-Feb-2006 djoseph Bug 5019025 : In the procedure PROCESS_TXN_ACCUM, in the cursors Res_accum_Cur
75: -- and PA_Txn_Accum_Cur, removed the decode that was used to join the period_name
76: -- based on the value of x_impl_opt.
71: -- 30-Sep-2005 pkanupar Bug4631058: In the procedure PROCESS_TXN_ACCUM, in the cursors Res_accum_Cur
72: -- and PA_Txn_Accum_Cur, removed the check for 'PERIOD_TYPE' between GL_PERIODS
73: -- and PA_IMPLIMENTATIONS.
74: -- 16-Feb-2006 djoseph Bug 5019025 : In the procedure PROCESS_TXN_ACCUM, in the cursors Res_accum_Cur
75: -- and PA_Txn_Accum_Cur, removed the decode that was used to join the period_name
76: -- based on the value of x_impl_opt.
77: -- 20-feb-2006 degupta To port the changes done in 11i file v115.23 to v115.25
78: -- 27-AUG-2008 jngeorge Bug 6511571: Removed the incorrect fix done for Bug# 5019025
79: --
100: x_err_stack In Out NOCOPY Varchar2,
101: x_err_stage In Out NOCOPY Varchar2,
102: x_err_code In Out NOCOPY Number ) Is
103:
104: -- This procedure reads the PA_TXN_ACCUM table and processes all transactions
105:
106: -- x_resource_list_id resource_list_id_tabtype;
107:
108: v_noof_tasks Number := 0;
179: Par.ROLLUP_QUANTITY_FLAG ,
180: Par.UNIT_OF_MEASURE,
181: PT.wbs_level
182: FROM
183: PA_TXN_ACCUM PTA,
184: PA_TASKS PT,
185: GL_PERIOD_STATUSES GPS,
186: PA_IMPLEMENTATIONS PI,
187: PA_RESOURCE_ACCUM_DETAILS Para,
239: Par.ROLLUP_QUANTITY_FLAG ,
240: Par.UNIT_OF_MEASURE,
241: 0 wbs_level
242: FROM
243: PA_TXN_ACCUM PTA,
244: GL_PERIOD_STATUSES GPS,
245: PA_IMPLEMENTATIONS PI,
246: PA_RESOURCE_ACCUM_DETAILS Para,
247: PA_RESOURCES Par,
268: Para.Txn_Accum_id = PTA.Txn_Accum_id
269: Order By 2,29,3,24; --Parent_Task_id,WBS_Level,Task_id,Para.Resource_List_Member_id;
270:
271:
272: CURSOR PA_Txn_Accum_Cur(p_org_id NUMBER) IS --Added p_org_id parameter to the cursor for bug 3147957
273: SELECT DISTINCT
274: PTA.TXN_ACCUM_ID,
275: nvl(PT.parent_task_id,nvl(pt.task_id,0)) top_task_id,
276: PTA.TASK_ID,
294: PTA.UNIT_OF_MEASURE,
295: GPS.PERIOD_YEAR,
296: nvl(PT.WBS_Level,0)
297: FROM
298: PA_TXN_ACCUM PTA,
299: PA_TASKS PT,
300: GL_PERIOD_STATUSES GPS,
301: PA_IMPLEMENTATIONS PI
302: Where PTA.Project_Id = x_project_id
348: Par.ROLLUP_QUANTITY_FLAG ,
349: Par.UNIT_OF_MEASURE,
350: PT.wbs_level
351: FROM
352: PA_TXN_ACCUM PTA,
353: PA_TASKS PT,
354: GL_PERIODS GPS,
355: PA_IMPLEMENTATIONS PI,
356: PA_RESOURCE_ACCUM_DETAILS Para,
405: Par.ROLLUP_QUANTITY_FLAG ,
406: Par.UNIT_OF_MEASURE,
407: 0 wbs_level
408: FROM
409: PA_TXN_ACCUM PTA,
410: GL_PERIODS GPS,
411: PA_IMPLEMENTATIONS PI,
412: PA_RESOURCE_ACCUM_DETAILS Para,
413: PA_RESOURCES Par,
431: Para.Txn_Accum_id = PTA.Txn_Accum_id
432: Order By 2,29,3,24; --Parent_Task_id,WBS_Level,Task_id,Para.Resource_List_Member_id;
433:
434:
435: CURSOR PA_Txn_Accum_Cur IS
436: SELECT DISTINCT
437: PTA.TXN_ACCUM_ID,
438: nvl(PT.parent_task_id,nvl(pt.task_id,0)) top_task_id,
439: PTA.TASK_ID,
457: PTA.UNIT_OF_MEASURE,
458: GPS.PERIOD_YEAR,
459: nvl(PT.WBS_Level,0)
460: FROM
461: PA_TXN_ACCUM PTA,
462: PA_TASKS PT,
463: GL_PERIODS GPS,
464: PA_IMPLEMENTATIONS PI
465: Where PTA.Project_Id = x_project_id
475: and gps.period_name = decode(x_impl_opt,'PA',PTA.pa_period,'GL',PTA.gl_period)
476: and trunc(gps.END_DATE) <= x_current_end_date -- added trunc for the bug 4532088
477: Order By 2,23,3; --Parent_Task_id,WBS_Level,Task_id
478:
479: x_txn_accum_rec PA_Txn_Accum_Cur%ROWTYPE;
480: x_res_accum_rec Res_accum_Cur%ROWTYPE;
481: /* Code Addition for bug 3147957 begins */
482: l_client_info varchar2(20);
483: l_org_id NUMBER;
508: initialize_project_level;
509:
510: /* Modified for bug 4195598
511: OPEN Res_Accum_Cur(l_org_id); -- Bug3147957: Added parameter l_org_id
512: OPEN PA_Txn_Accum_Cur(l_org_id); -- Bug3147957: Added parameter l_org_id
513: */
514: OPEN Res_Accum_Cur;
515: OPEN PA_Txn_Accum_Cur;
516: LOOP
511: OPEN Res_Accum_Cur(l_org_id); -- Bug3147957: Added parameter l_org_id
512: OPEN PA_Txn_Accum_Cur(l_org_id); -- Bug3147957: Added parameter l_org_id
513: */
514: OPEN Res_Accum_Cur;
515: OPEN PA_Txn_Accum_Cur;
516: LOOP
517:
518: IF Fetch_Task = True THEN
519: Fetch PA_Txn_Accum_Cur INTO x_txn_accum_rec;
515: OPEN PA_Txn_Accum_Cur;
516: LOOP
517:
518: IF Fetch_Task = True THEN
519: Fetch PA_Txn_Accum_Cur INTO x_txn_accum_rec;
520: END IF;
521:
522: Fetch_Task := True;
523:
521:
522: Fetch_Task := True;
523:
524: IF (x_txn_accum_rec.task_id = curr_task_id OR curr_task_id = -99)
525: AND (PA_Txn_Accum_Cur%FOUND) then
526:
527: If (x_txn_accum_rec.actual_cost_rollup_flag = 'Y' and
528: x_actual_cost_flag = 'Y') or
529: (x_txn_accum_rec.revenue_rollup_flag = 'Y' and
949: end if;
950:
951: if (x_txn_accum_rec.top_task_id <> curr_parent_id and
952: curr_parent_id > 0)or
953: pa_txn_accum_cur%notfound then
954: add_parent_amounts;
955: Get_all_higher_tasks
956: (x_project_id ,
957: curr_task_id ,
1718: fetch_task := False;
1719: end if;
1720:
1721: END IF;
1722: Exit when PA_TXN_ACCUM_CUR%NOTFOUND;
1723: Curr_task_id := x_Txn_Accum_rec.Task_id;
1724: Curr_parent_id := x_txn_accum_rec.top_task_id;
1725:
1726: -- After processing the records, Update the PA_TXN_ACCUM , modifying the
1722: Exit when PA_TXN_ACCUM_CUR%NOTFOUND;
1723: Curr_task_id := x_Txn_Accum_rec.Task_id;
1724: Curr_parent_id := x_txn_accum_rec.top_task_id;
1725:
1726: -- After processing the records, Update the PA_TXN_ACCUM , modifying the
1727: if pa_proj_accum_main.x_summ_process <> 'RL' then
1728: Update PA_TXN_ACCUM Set
1729: TOT_REVENUE = NVL(TOT_REVENUE,0) + NVL(I_TOT_REVENUE,0),
1730: TOT_RAW_COST = NVL(TOT_RAW_COST,0) + NVL(I_TOT_RAW_COST,0),
1724: Curr_parent_id := x_txn_accum_rec.top_task_id;
1725:
1726: -- After processing the records, Update the PA_TXN_ACCUM , modifying the
1727: if pa_proj_accum_main.x_summ_process <> 'RL' then
1728: Update PA_TXN_ACCUM Set
1729: TOT_REVENUE = NVL(TOT_REVENUE,0) + NVL(I_TOT_REVENUE,0),
1730: TOT_RAW_COST = NVL(TOT_RAW_COST,0) + NVL(I_TOT_RAW_COST,0),
1731: TOT_BURDENED_COST = NVL(TOT_BURDENED_COST,0) +
1732: NVL(I_TOT_BURDENED_COST,0),
1761: program_id = pa_proj_accum_main.x_program_id,
1762: program_update_date = SYSDATE
1763: Where TXN_ACCUM_ID = x_txn_accum_rec.txn_accum_id;
1764: end if;
1765: END LOOP; -- End of x_Txn_Accum_rec in PA_Txn_Accum_cur LOOP
1766:
1767: CLOSE PA_Txn_Accum_Cur;
1768: CLOSE res_accum_cur;
1769: -- Update pa_project_accum_actuals project level records
1763: Where TXN_ACCUM_ID = x_txn_accum_rec.txn_accum_id;
1764: end if;
1765: END LOOP; -- End of x_Txn_Accum_rec in PA_Txn_Accum_cur LOOP
1766:
1767: CLOSE PA_Txn_Accum_Cur;
1768: CLOSE res_accum_cur;
1769: -- Update pa_project_accum_actuals project level records
1770: if (x_actual_cost_flag = 'Y' or x_revenue_flag = 'Y')
1771: and pa_proj_accum_main.x_summ_process <> 'RL' then
2788:
2789: -- If the above procedure had returned any tasks , then we need to insert
2790: -- header record and actuals record. We need to process the tasks one by one
2791: -- since we require the Accum_id for each detail record.
2792: -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
2793: -- 1.1.1, then the first time, Get_all_higher_tasks would return,
2794: -- 1.1.1, 1.1, and 1. We create three header records and three detail records
2795: -- in the Project_accum_actuals table. The next time , if the given task
2796: -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
2935:
2936: -- If the above procedure had returned any tasks , then we need to insert
2937: -- header record and actuals record. We need to process the tasks one by one
2938: -- since we require the Accum_id for each detail record.
2939: -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
2940: -- 1.1.1, then the first time, Get_all_higher_tasks would return,
2941: -- 1.1.1, 1.1, and 1. We create three header records and three detail records
2942: -- in the Project_accum_actuals table. The next time , if the given task
2943: -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since