1818: creation_date = Trunc(Sysdate),
1819: created_by = pa_proj_accum_main.x_created_by,
1820: last_update_login = pa_proj_accum_main.x_last_update_login
1821: Where PAA.Project_Accum_id In
1822: (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
1823: Where Pah.Project_id = x_project_id and
1824: pah.Resource_list_member_id = 0 and
1825: Pah.Task_id = 0);
1826: if sql%notfound then
1825: Pah.Task_id = 0);
1826: if sql%notfound then
1827:
1828: Select project_accum_id into v_accum_id
1829: from pa_project_accum_headers
1830: where project_id = x_project_id
1831: and task_id = 0
1832: and resource_list_member_id = 0;
1833:
1913: creation_date = Trunc(Sysdate),
1914: created_by = pa_proj_accum_main.x_created_by,
1915: last_update_login = pa_proj_accum_main.x_last_update_login
1916: Where PAA.Project_Accum_id In
1917: (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
1918: Where Pah.Project_id = x_project_id and
1919: pah.Resource_list_member_id = 0 and
1920: Pah.Task_id = 0);
1921:
1921:
1922: If sql%notfound then
1923:
1924: Select project_accum_id into v_accum_id
1925: from pa_project_accum_headers
1926: where project_id = x_project_id
1927: and task_id = 0
1928: and resource_list_member_id = 0;
1929:
2000: END IF;
2001: If x_create_actuals = 'Y' or x_create_commit = 'Y' then
2002: BEGIN
2003: SELECT project_accum_id INTO V_accum_id
2004: FROM Pa_Project_Accum_Headers PAH
2005: WHERE PAH.Project_id = x_project_id
2006: AND PAH.Task_id = x_task_id
2007: AND PAH.Resource_List_Member_id = x_resource_list_member_id;
2008:
2007: AND PAH.Resource_List_Member_id = x_resource_list_member_id;
2008:
2009: EXCEPTION
2010: WHEN NO_DATA_FOUND THEN
2011: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
2012: From Dual;
2013:
2014: Insert into PA_PROJECT_ACCUM_HEADERS
2015: (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
2010: WHEN NO_DATA_FOUND THEN
2011: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
2012: From Dual;
2013:
2014: Insert into PA_PROJECT_ACCUM_HEADERS
2015: (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
2016: RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
2017: RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2018: REQUEST_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN )
2334: END IF;
2335: If x_create_wbs_actuals = 'Y' or x_create_wbs_commit = 'Y' then
2336: BEGIN
2337: SELECT project_accum_id INTO V_accum_id
2338: FROM Pa_Project_Accum_Headers PAH
2339: WHERE PAH.Project_id = x_project_id
2340: AND PAH.Task_id = x_task_id
2341: AND PAH.Resource_List_Member_id = x_resource_list_member_id;
2342:
2341: AND PAH.Resource_List_Member_id = x_resource_list_member_id;
2342:
2343: EXCEPTION
2344: WHEN NO_DATA_FOUND THEN
2345: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
2346: From Dual;
2347:
2348: Insert into PA_PROJECT_ACCUM_HEADERS
2349: (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
2344: WHEN NO_DATA_FOUND THEN
2345: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
2346: From Dual;
2347:
2348: Insert into PA_PROJECT_ACCUM_HEADERS
2349: (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
2350: RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
2351: RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2352: REQUEST_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN )
2638: END Check_Accum_wbs;
2639:
2640: -- This procedure - For the given Task Id returns all the
2641: -- higher level tasks in the WBS (including the given
2642: -- task) which are not in PA_PROJECT_ACCUM_HEADERS
2643: -- (Tasks with the given Resource )
2644:
2645: Procedure Get_all_higher_tasks (x_project_id in Number,
2646: x_task_id in Number,
2694: x_err_stack In Out NOCOPY Varchar2,
2695: x_err_stage In Out NOCOPY Varchar2,
2696: x_err_code In Out NOCOPY Number ) IS
2697: -- Insert_Headers_tasks - Inserts Header records in the
2698: -- PA_PROJECT_ACCUM_HEADERS table
2699: V_Old_Stack Varchar2(630);
2700:
2701: Begin
2702:
2707: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2708: pa_debug.debug(x_err_stack);
2709: END IF;
2710:
2711: Insert into PA_PROJECT_ACCUM_HEADERS
2712: (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
2713: RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
2714: RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2715: CREATION_DATE,REQUEST_ID,CREATED_BY,LAST_UPDATE_LOGIN )
2755: x_err_stack :=
2756: x_err_stack||'->PA_PROCESS_ACCUM_ACTUALS.create_accum_actuals';
2757: -- This checks for Actuals record in PA_PROJECT_ACCUM_ACTUALS for this
2758: -- project and task combination. It is possible that there might be a
2759: -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
2760: -- no corresponding detail record. The procedure called below,will
2761: -- check for the existence of the detail records and if not available
2762: -- would create it.
2763:
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
2797: -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
2798: -- two records would have been processed by the Update statements.
2799:
2800: If v_noof_tasks > 0 Then
2801: For i in 1..v_noof_tasks LOOP
2798: -- two records would have been processed by the Update statements.
2799:
2800: If v_noof_tasks > 0 Then
2801: For i in 1..v_noof_tasks LOOP
2802: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
2803: From Dual;
2804: Insert_Headers_tasks
2805: (X_project_id,
2806: v_task_array(i),
2867:
2868: CURSOR Proj_Res_level_Cur IS
2869: SELECT Project_Accum_Id
2870: FROM
2871: PA_PROJECT_ACCUM_HEADERS
2872: WHERE Project_id = X_project_id
2873: AND Task_Id = 0
2874: AND Resource_list_Member_id = X_resource_list_member_id;
2875:
2885: x_err_stack :=
2886: X_err_stack ||'-PA_MAINT_PROJECT_ACCUM.create_accum_actual_res';
2887: -- This checks for Actuals record in PA_PROJECT_ACCUM_ACTUALS for this
2888: -- project,task and resource combination.It is possible that there might be a
2889: -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
2890: -- no corresponding detail record. The procedure called below,will
2891: -- check for the existence of the detail records and if not available
2892: -- would create it.
2893:
2902: Recs_processed := Recs_processed + Res_recs_processed;
2903:
2904: -- This checks for Actuals record in PA_PROJECT_ACCUM_ACTUALS for this
2905: -- project and Resource combination. It is possible that there might be a
2906: -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
2907: -- no corresponding detail record. The procedure called below,will
2908: -- check for the existence of the detail records and if not available
2909: -- would create it.
2910:
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
2944: -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
2945: -- two records would have been processed by the Update statements.
2946:
2947: If v_noof_tasks > 0 Then
2948: For i in 1..v_noof_tasks LOOP
2945: -- two records would have been processed by the Update statements.
2946:
2947: If v_noof_tasks > 0 Then
2948: For i in 1..v_noof_tasks LOOP
2949: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
2950: From Dual;
2951: PA_process_accum_actuals_res.insert_headers_res
2952: (x_project_id,
2953: v_task_array(i),
2995: -- and if not present create the Header and Detail records for Actuals
2996: Open Proj_Res_level_Cur;
2997: Fetch Proj_Res_level_Cur Into V_Accum_Id;
2998: IF Proj_Res_level_Cur%NOTFOUND Then
2999: Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
3000: From Dual;
3001: PA_process_accum_actuals_res.insert_headers_res
3002: (x_project_id,
3003: 0,
3141: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3142: LAST_UPDATE_DATE = trunc(sysdate),
3143: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3144: Where Project_Accum_id IN
3145: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3146: PAH.Project_Id = x_project_id);
3147: Else
3148: Update PA_PROJECT_ACCUM_ACTUALS SET
3149: RAW_COST_PP = RAW_COST_PTD,
3167: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3168: LAST_UPDATE_DATE = trunc(sysdate),
3169: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3170: Where Project_Accum_id IN
3171: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3172: PAH.Project_Id = x_project_id);
3173: End If;
3174: ElsIf X_prev_start_date > x_prev_accum_start_date then
3175: If x_current_year > x_prev_accum_year then
3197: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3198: LAST_UPDATE_DATE = trunc(sysdate),
3199: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3200: Where Project_Accum_id IN
3201: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3202: PAH.Project_Id = x_project_id);
3203: Else
3204: Update PA_PROJECT_ACCUM_ACTUALS SET
3205: RAW_COST_PP = 0,
3223: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3224: LAST_UPDATE_DATE = trunc(sysdate),
3225: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3226: Where Project_Accum_id IN
3227: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3228: PAH.Project_Id = x_project_id);
3229: End If;
3230: End If;
3231: End If;
3228: PAH.Project_Id = x_project_id);
3229: End If;
3230: End If;
3231: End If;
3232: -- Update Pa_project_accum_Headers Set
3233: -- Accum_Period = x_current_period
3234: -- where Project_Id = X_Project_id;
3235: -- Restore the old x_err_stack;
3236:
3334: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3335: LAST_UPDATE_DATE = trunc(sysdate),
3336: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3337: Where Project_Accum_id IN
3338: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3339: PAH.Project_Id = x_project_id) And
3340: Budget_Type_Code <> Nvl(x_budget_type,'00');
3341: Else
3342: Update PA_PROJECT_ACCUM_BUDGETS SET
3361: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3362: LAST_UPDATE_DATE = trunc(sysdate),
3363: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3364: Where Project_Accum_id IN
3365: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3366: PAH.Project_Id = x_project_id) And
3367: Budget_Type_Code <> Nvl(x_budget_type,'00');
3368: End If;
3369: ElsIf X_prev_start_date > x_prev_accum_start_date then
3399: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3400: LAST_UPDATE_DATE = trunc(sysdate),
3401: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3402: Where Project_Accum_id IN
3403: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3404: PAH.Project_Id = x_project_id) And
3405: Budget_Type_Code <> Nvl(x_budget_type,'00');
3406: Else
3407: Update PA_PROJECT_ACCUM_BUDGETS SET
3426: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3427: LAST_UPDATE_DATE = trunc(sysdate),
3428: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3429: Where Project_Accum_id IN
3430: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3431: PAH.Project_Id = x_project_id) And
3432: Budget_Type_Code <> Nvl(x_budget_type,'00');
3433: End If;
3434: End If;
3526: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3527: LAST_UPDATE_DATE = trunc(sysdate),
3528: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3529: Where Project_Accum_id IN
3530: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3531: PAH.Project_Id = x_project_id);
3532: Else
3533: Update PA_PROJECT_ACCUM_COMMITMENTS SET
3534: CMT_RAW_COST_PP = CMT_RAW_COST_PTD,
3540: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3541: LAST_UPDATE_DATE = trunc(sysdate),
3542: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3543: Where Project_Accum_id IN
3544: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3545: PAH.Project_Id = x_project_id);
3546: End If;
3547: ElsIf X_prev_start_date > x_prev_accum_start_date then
3548: If x_current_year > x_prev_accum_year then
3559: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3560: LAST_UPDATE_DATE = trunc(sysdate),
3561: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3562: Where Project_Accum_id IN
3563: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3564: PAH.Project_Id = x_project_id);
3565: Else
3566: Update PA_PROJECT_ACCUM_COMMITMENTS SET
3567: CMT_RAW_COST_PP = 0,
3573: LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
3574: LAST_UPDATE_DATE = trunc(sysdate),
3575: LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
3576: Where Project_Accum_id IN
3577: (Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
3578: PAH.Project_Id = x_project_id);
3579: End If;
3580: End If;
3581: End If;