[Home] [Help]
336: END Insert_Parent_Rec_Tmp;
337:
338: /*====================================================================================================
339: ROLLUP_BUDGET_VERSION: This is the main API which will do whatever necessary for doing rollup into
340: pa_resource_assignments, PA_PROJ_PERIODS_DENORM and pa_budget_versions_tables.
341:
342:
343: r11.5 FP.M Developement ----------------------------------
344:
2129: WHERE project_id = l_project_id;
2130:
2131: IF (l_period_profile_id IS NOT NULL) THEN
2132:
2133: /* Delete all the records from PA_PROJ_PERIODS_DENORM as new resource assignment IDs
2134: would have been generated. */
2135: pa_debug.g_err_stage := 'period profile id not null deleting denorm records ';
2136: IF P_PA_DEBUG_MODE = 'Y' THEN
2137: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2136: IF P_PA_DEBUG_MODE = 'Y' THEN
2137: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2138: END IF;
2139:
2140: DELETE FROM pa_proj_periods_denorm
2141: WHERE budget_version_id = p_budget_version_id;
2142:
2143: /* Call call_maintain_plan_matrix API with data source as 'BUDGET_LINES' to dump all budget lines
2144: into PA_PROJ_PERIODS_DENORM table. */
2140: DELETE FROM pa_proj_periods_denorm
2141: WHERE budget_version_id = p_budget_version_id;
2142:
2143: /* Call call_maintain_plan_matrix API with data source as 'BUDGET_LINES' to dump all budget lines
2144: into PA_PROJ_PERIODS_DENORM table. */
2145:
2146: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records. Calling CALL_MAINTAIN_PLAN_MATRIX' ;
2147: IF P_PA_DEBUG_MODE = 'Y' THEN
2148: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2186: ELSE
2187: L_INSERTING_PARENT_TASK_LEVEL := true;
2188: END IF;
2189:
2190: /* If the amount_type_code is QUANTITY in pa_proj_periods_denorm, then the records
2191: with unit_of_measure as that in the temp table are rolled up. Hence the temp
2192: table should contain the uom as HOURS for the first user_enetered_level so that
2193: the Quantity in the next upper level records are automatically inserted correctly. */
2194:
2217: ,PX_INSERTING_TASK_LEVEL => L_INSERTING_TASK_LEVEL
2218: ,PX_INSERTING_PARENT_TASK_LEVEL => L_INSERTING_PARENT_TASK_LEVEL
2219: ,p_curr_rollup_level => l_curr_rollup_level);
2220:
2221: pa_debug.g_err_stage := 'Inserting into PA_Proj_Periods_Denorm';
2222: IF P_PA_DEBUG_MODE = 'Y' THEN
2223: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2224: END IF;
2225:
2222: IF P_PA_DEBUG_MODE = 'Y' THEN
2223: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2224: END IF;
2225:
2226: INSERT INTO PA_PROJ_PERIODS_DENORM(
2227: PROJECT_ID
2228: ,BUDGET_VERSION_ID
2229: ,RESOURCE_ASSIGNMENT_ID
2230: ,PARENT_ASSIGNMENT_ID
2372: ,sysdate
2373: ,fnd_global.user_id
2374: ,fnd_global.login_id
2375: FROM pa_fp_ra_map_tmp tmp,
2376: pa_proj_periods_denorm ppd
2377: WHERE tmp.resource_assignment_id = ppd.resource_assignment_id
2378: AND ppd.budget_version_id = p_budget_version_id -- performance bug 2802862
2379: AND ((currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
2380: (amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
2460:
2461:
2462: /***********************************************************************************************
2463: UPDATE_DENORM_PARENT_ASSIGN_ID: This procedure updates the Parent Assignment ID on the
2464: pa_proj_periods_denorm table.
2465: ***********************************************************************************************/
2466: PROCEDURE UPDATE_DENORM_PARENT_ASSIGN_ID(
2467: p_budget_version_id IN NUMBER
2468: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2491: IF P_PA_DEBUG_MODE = 'Y' THEN
2492: pa_debug.set_process('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
2493: END IF;
2494:
2495: /* Updating the records present in the table pa_proj_periods_denorm with the Parent
2496: Assignment ID. */
2497: pa_debug.g_err_stage := 'updating parents on denorm table';
2498: IF P_PA_DEBUG_MODE = 'Y' THEN
2499: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2498: IF P_PA_DEBUG_MODE = 'Y' THEN
2499: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2500: END IF;
2501:
2502: UPDATE pa_proj_periods_denorm ppd
2503: SET ppd.parent_assignment_id =
2504: (SELECT parent_assignment_id
2505: FROM pa_resource_assignments pra
2506: WHERE pra.resource_assignment_id = ppd.resource_assignment_id)
3704: ppd.currency_type,
3705: decode(ppd.amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
3706: l_proj_currency_code,ppd.currency_code) --#2801522:For Qty, store Proj Curr Code
3707: FROM pa_resource_assignments pra,
3708: pa_proj_periods_denorm ppd
3709: WHERE ppd.budget_version_id = p_budget_version_id -- #2839138
3710: AND pra.resource_assignment_id = ppd.resource_assignment_id
3711: AND ppd.object_type_code = PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT -- #2839138
3712: AND ppd.object_id = ppd.resource_assignment_id -- #2839138
3809: ELSE
3810: L_INSERTING_PARENT_TASK_LEVEL := true;
3811: END IF;
3812:
3813: pa_debug.g_err_stage := 'Inserting Parent Records into pa_proj_periods_denorm in a loop';
3814: IF P_PA_DEBUG_MODE = 'Y' THEN
3815: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3816: END IF;
3817:
3844:
3845: IF nvl(l_ra_id_tbl.last,0) > 0 THEN
3846:
3847: FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3848: INSERT INTO pa_proj_periods_denorm
3849: (RESOURCE_ASSIGNMENT_ID
3850: ,PROJECT_ID
3851: ,BUDGET_VERSION_ID
3852: ,PARENT_ASSIGNMENT_ID
4031: l_first_level is being used for this purpose. */
4032:
4033: l_first_level := 'Y';
4034:
4035: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 1st level';
4036: IF P_PA_DEBUG_MODE = 'Y' THEN
4037: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4038: END IF;
4039: LOOP
4043: IF P_PA_DEBUG_MODE = 'Y' THEN
4044: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4045: END IF;
4046:
4047: UPDATE PA_PROJ_PERIODS_DENORM ppd1
4048: SET (preceding_periods_amount
4049: ,succeeding_periods_amount
4050: ,prior_period_amount
4051: ,period_amount1
4154: ,sum(nvl(period_amount49,0))
4155: ,sum(nvl(period_amount50,0))
4156: ,sum(nvl(period_amount51,0))
4157: ,sum(nvl(period_amount52,0))
4158: FROM PA_PROJ_PERIODS_DENORM ppd2, pa_resource_assignments pra
4159: WHERE ppd1.resource_assignment_id = ppd2.parent_assignment_id
4160: AND ppd1.currency_type = ppd2.currency_type
4161: AND ppd1.currency_code = decode(ppd2.amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4162: ppd1.currency_code,ppd2.currency_code) --#2801522:Dont check curr code for Qty
4182: l_upd_rec := nvl(l_parent_ra_id_tbl.last,0);
4183:
4184: ELSE
4185:
4186: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 2nd level';
4187: IF P_PA_DEBUG_MODE = 'Y' THEN
4188: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4189: END IF;
4190:
4187: IF P_PA_DEBUG_MODE = 'Y' THEN
4188: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4189: END IF;
4190:
4191: UPDATE PA_PROJ_PERIODS_DENORM ppd1
4192: SET (preceding_periods_amount
4193: ,succeeding_periods_amount
4194: ,prior_period_amount
4195: ,period_amount1
4298: ,sum(nvl(period_amount49,0))
4299: ,sum(nvl(period_amount50,0))
4300: ,sum(nvl(period_amount51,0))
4301: ,sum(nvl(period_amount52,0))
4302: FROM PA_PROJ_PERIODS_DENORM ppd2
4303: WHERE ppd1.resource_assignment_id = ppd2.parent_assignment_id
4304: AND ppd1.currency_type = ppd2.currency_type
4305: AND ppd1.currency_code = decode(ppd2.amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4306: ppd1.currency_code,ppd2.currency_code) --#2801522:Dont check curr code for Qty
4864: ,x_return_status => x_return_status
4865: ,x_msg_count => x_msg_count
4866: ,x_msg_data => x_msg_data);
4867:
4868: DELETE FROM pa_proj_periods_denorm
4869: WHERE resource_assignment_id = p_resource_assignment_id;
4870:
4871: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4872: IF P_PA_DEBUG_MODE = 'Y' THEN
4875:
4876: ELSE
4877:
4878: /* Deleting records from pa_budget_lines (for resource_assignment_id
4879: and txn_currency_code) and pa_proj_periods_denorm (for 'TRANSACTION'
4880: currency type. */
4881:
4882: DELETE FROM pa_budget_lines
4883: WHERE resource_assignment_id = p_resource_assignment_id
4933: ,x_return_status => x_return_status
4934: ,x_msg_count => x_msg_count
4935: ,x_msg_data => x_msg_data);
4936:
4937: /* Delete from pa_proj_periods_denorm. */
4938:
4939: DELETE FROM pa_proj_periods_denorm
4940: WHERE resource_assignment_id = p_resource_assignment_id
4941: AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION
4935: ,x_msg_data => x_msg_data);
4936:
4937: /* Delete from pa_proj_periods_denorm. */
4938:
4939: DELETE FROM pa_proj_periods_denorm
4940: WHERE resource_assignment_id = p_resource_assignment_id
4941: AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION
4942: AND currency_code = p_txn_currency_code;
4943:
5000: IF P_PA_DEBUG_MODE = 'Y' THEN
5001: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5002: END IF;
5003:
5004: DELETE FROM pa_proj_periods_denorm
5005: WHERE resource_assignment_id = p_resource_assignment_id;
5006:
5007: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
5008: IF P_PA_DEBUG_MODE = 'Y' THEN
5034: IF l_child_res_count = 0 THEN
5035:
5036: /* If no child is found for this Parent Assignment ID,then delete this
5037: resource_assignment_id record from pa_resource_assignments and pa
5038: pa_proj_periods_denorm. Get the parent of this resource assignment id.
5039: Continue this loop until some parent is found with child records. */
5040:
5041: pa_debug.g_err_stage := 'no child found. deleting the parent';
5042: IF P_PA_DEBUG_MODE = 'Y' THEN
5046: /* the delete below is moved before the next delete as after the next delte
5047: l_parent_assignment_id value will change
5048: */
5049:
5050: DELETE FROM pa_proj_periods_denorm
5051: WHERE resource_assignment_id = l_parent_assignment_id;
5052: /* WHERE resource_assignment_id = p_resource_assignment_id during bug fix 2649117 found that it
5053: was p_resource_assignment_id */
5054: