[Home] [Help]
139: END IF;
140:
141: x_planning_level := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(p_budget_version_id);
142: /* M23-AUG: changed following select to function call
143: SELECT decode(fin_plan_preference_code, PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,cost_fin_plan_level_code,
144: PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,revenue_fin_plan_level_code,
145: PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,all_fin_plan_level_code) planning_level
146: INTO x_planning_level
147: FROM pa_proj_fp_options
140:
141: x_planning_level := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(p_budget_version_id);
142: /* M23-AUG: changed following select to function call
143: SELECT decode(fin_plan_preference_code, PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,cost_fin_plan_level_code,
144: PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,revenue_fin_plan_level_code,
145: PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,all_fin_plan_level_code) planning_level
146: INTO x_planning_level
147: FROM pa_proj_fp_options
148: WHERE fin_plan_version_id = p_budget_version_id;
141: x_planning_level := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(p_budget_version_id);
142: /* M23-AUG: changed following select to function call
143: SELECT decode(fin_plan_preference_code, PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,cost_fin_plan_level_code,
144: PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,revenue_fin_plan_level_code,
145: PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,all_fin_plan_level_code) planning_level
146: INTO x_planning_level
147: FROM pa_proj_fp_options
148: WHERE fin_plan_version_id = p_budget_version_id;
149: */
258: ,PARENT_ASSIGNMENT_ID
259: ,UNIT_OF_MEASURE)
260: SELECT resource_assignment_id
261: ,parent_assignment_id
262: ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
263: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
264: FROM pa_resource_assignments pra, pa_resource_list_members prlm
265: WHERE pra.budget_version_id = p_budget_version_id
266: AND pra.resource_list_member_id = prlm.resource_list_member_id
259: ,UNIT_OF_MEASURE)
260: SELECT resource_assignment_id
261: ,parent_assignment_id
262: ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
263: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
264: FROM pa_resource_assignments pra, pa_resource_list_members prlm
265: WHERE pra.budget_version_id = p_budget_version_id
266: AND pra.resource_list_member_id = prlm.resource_list_member_id
267: AND prlm.parent_member_id IS NOT NULL
281: ,PARENT_ASSIGNMENT_ID
282: ,UNIT_OF_MEASURE)
283: SELECT resource_assignment_id
284: ,parent_assignment_id
285: ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
286: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
287: FROM pa_resource_assignments pra, pa_resource_list_members prlm
288: WHERE pra.budget_version_id = p_budget_version_id
289: AND pra.resource_list_member_id = prlm.resource_list_member_id
282: ,UNIT_OF_MEASURE)
283: SELECT resource_assignment_id
284: ,parent_assignment_id
285: ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
286: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
287: FROM pa_resource_assignments pra, pa_resource_list_members prlm
288: WHERE pra.budget_version_id = p_budget_version_id
289: AND pra.resource_list_member_id = prlm.resource_list_member_id
290: AND prlm.parent_member_id IS NULL
303: ,PARENT_ASSIGNMENT_ID
304: ,UNIT_OF_MEASURE)
305: SELECT pra.resource_assignment_id
306: ,pra.parent_assignment_id
307: ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
308: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
309: FROM pa_resource_assignments pra, pa_tasks pt
310: WHERE pra.budget_version_id = p_budget_version_id
311: AND pra.task_id = pt.task_id
304: ,UNIT_OF_MEASURE)
305: SELECT pra.resource_assignment_id
306: ,pra.parent_assignment_id
307: ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
308: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
309: FROM pa_resource_assignments pra, pa_tasks pt
310: WHERE pra.budget_version_id = p_budget_version_id
311: AND pra.task_id = pt.task_id
312: AND pra.resource_list_member_id in (0,l_uncat_rlm_id) -- Added for bug #2723515
564: and pbl.pc_cur_conv_rejection_code IS NULL
565: and pbl.pfc_cur_conv_rejection_code IS NULL
566: and pbl.budget_version_id = p_budget_version_id ) --Added for bug 4141042
567: WHERE budget_version_id = p_budget_version_id;
568: --AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED; For bug 3668727
569: END IF; --end of changes for bug 4160258.
570:
571: -- End, Bug 3362316, 08-JAN-2004: Obsolete for FP.M; Added Update ---------------------
572:
825: ,sum(nvl(pra.total_project_revenue,0))
826: ,sum(nvl(pra.total_plan_raw_cost,0))
827: ,sum(nvl(pra.total_plan_burdened_cost,0))
828: ,sum(nvl(pra.total_plan_revenue,0))
829: ,sum(nvl(decode(pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
830: pra.total_plan_quantity,0),0)) quantity
831: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for bug #2697999
832: ,'Y' -- Modified for bug #2697999
833: FROM pa_resource_assignments pra
827: ,sum(nvl(pra.total_plan_burdened_cost,0))
828: ,sum(nvl(pra.total_plan_revenue,0))
829: ,sum(nvl(decode(pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
830: pra.total_plan_quantity,0),0)) quantity
831: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for bug #2697999
832: ,'Y' -- Modified for bug #2697999
833: FROM pa_resource_assignments pra
834: WHERE pra.budget_version_id = c_budget_version_id
835: AND pra.resource_list_member_id <> 0
832: ,'Y' -- Modified for bug #2697999
833: FROM pa_resource_assignments pra
834: WHERE pra.budget_version_id = c_budget_version_id
835: AND pra.resource_list_member_id <> 0
836: AND pra.resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
837: AND (pra.total_plan_quantity IS NOT NULL -- Added for bug #2784520
838: OR pra.total_plan_raw_cost IS NOT NULL
839: OR pra.total_plan_revenue IS NOT NULL
840: OR pra.total_plan_burdened_cost IS NOT NULL)
852: ,sum(nvl(pra.total_plan_raw_cost,0))
853: ,sum(nvl(pra.total_plan_burdened_cost,0))
854: ,sum(nvl(pra.total_plan_revenue,0))
855: ,sum(nvl(pra.total_plan_quantity,0)) quantity /* no decode required on quantity */
856: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for bug #2697999
857: ,'Y' -- Modified for bug #2697999
858: FROM pa_resource_assignments pra
859: ,pa_tasks pt
860: WHERE pra.budget_version_id = c_budget_version_id
885: ,sum(pra.total_project_revenue)
886: ,sum(pra.total_plan_raw_cost)
887: ,sum(pra.total_plan_burdened_cost)
888: ,sum(pra.total_plan_revenue)
889: ,sum(decode(unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
890: nvl(total_plan_quantity,0),0)) quantity
891: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS unit_of_measure --Modified for bug #2697999
892: ,'Y' track_as_labor_flag --Modified for bug #2697999
893: FROM pa_resource_assignments pra
887: ,sum(pra.total_plan_burdened_cost)
888: ,sum(pra.total_plan_revenue)
889: ,sum(decode(unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
890: nvl(total_plan_quantity,0),0)) quantity
891: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS unit_of_measure --Modified for bug #2697999
892: ,'Y' track_as_labor_flag --Modified for bug #2697999
893: FROM pa_resource_assignments pra
894: WHERE pra.budget_version_id = p_budget_Version_id
895: AND pra.resource_assignment_type = 'USER_ENTERED'
912: ,sum(pra.total_plan_raw_cost)
913: ,sum(pra.total_plan_burdened_cost)
914: ,sum(pra.total_plan_revenue)
915: ,sum(nvl(total_plan_quantity,0)) quantity
916: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS unit_of_measure --Modified for bug #2697999
917: ,'Y' track_as_labor_flag --Modified for bug #2697999
918: FROM pa_resource_assignments pra,
919: pa_tasks pt
920: WHERE pra.budget_version_id = p_budget_Version_id
1045: END IF;
1046:
1047: DELETE FROM pa_resource_assignments
1048: WHERE budget_version_id = p_budget_version_id
1049: AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_ROLLED_UP;
1050:
1051: /* Update the pa_resource_assignments USER_ENTERED records set
1052: parent_assignment_id as null. Also update the amounts. Amounts need to
1053: be updated as in case of copy from and copy actual amounts may not be updated.
1078: ,sum(nvl(quantity,0))
1079: FROM pa_budget_lines pbl
1080: WHERE pbl.resource_assignment_id = pra.resource_assignment_id)
1081: WHERE budget_version_id = p_budget_version_id
1082: AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED;
1083:
1084: l_count := sql%rowcount; /* 2598502 - Used local variable for sql%rowcount */
1085:
1086: pa_debug.g_err_stage := 'updated ' || l_count || ' records';
1196: level records. This step need to be done whether resource list is grouped or not. We select
1197: USER_ENTEREDrecords only because for certain records amounts could be entered at resource
1198: group or resource level. Hence it makes our task easy to look only at user entered records. */
1199:
1200: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1201:
1202: pa_debug.g_err_stage := 'Inserting the Task Level Records';
1203: IF P_PA_DEBUG_MODE = 'Y' THEN
1204: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1250:
1251: END LOOP;
1252: CLOSE Cur_Task_Level;
1253:
1254: END IF; /* l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT*/
1255:
1256: END IF; /* If Resource List is not uncategorized */
1257:
1258:
1271:
1272: /* If planning_level is not 'project'or 'top_task'then we need to insert Parent Task
1273: Level records. */
1274:
1275: IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1276: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1277: /* Insert Parent Level Records */
1278:
1279: /* Initialise the variable l_cur_res_assignment_id to 0. */
1272: /* If planning_level is not 'project'or 'top_task'then we need to insert Parent Task
1273: Level records. */
1274:
1275: IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1276: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1277: /* Insert Parent Level Records */
1278:
1279: /* Initialise the variable l_cur_res_assignment_id to 0. */
1280: l_curr_res_assignment_id := 0;
1391: l_curr_wbs_level := l_curr_wbs_level - 1;
1392:
1393: END LOOP; /* End Loop of the outer loop */
1394:
1395: END IF; /* l_planning_level not in PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1396: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP*/
1397:
1398: /* Creating a project level record in pa_resource_assignment table. Resource
1399: list member id should be l_uncat_rlm_id for such record. Store the
1392:
1393: END LOOP; /* End Loop of the outer loop */
1394:
1395: END IF; /* l_planning_level not in PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1396: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP*/
1397:
1398: /* Creating a project level record in pa_resource_assignment table. Resource
1399: list member id should be l_uncat_rlm_id for such record. Store the
1400: resource_assignment_id of this record in l_proj_ra_id for later use. */
1414:
1415:
1416: /* #2598502: Modified the following condition. */
1417:
1418: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1419: IF l_uncat_flag = 'Y' THEN
1420: /* For project level and resource list none we don't need to create proejct
1421: level record */
1422: pa_debug.g_err_stage := 'Not creating a Project Level Record';
1482: ,l_track_as_labor_flag_tbl(1);
1483:
1484: CLOSE c_proj_level_amts2;
1485:
1486: END IF; /* l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT */
1487:
1488: -- bug#2659956 : Added the IF condition to avoid the issue of
1489: -- PA_RESOURCE_ASSIGNMENTS_S.CURRVAL not yet defined ,when
1490: -- planning level is project and resource list is uncategorized.
1488: -- bug#2659956 : Added the IF condition to avoid the issue of
1489: -- PA_RESOURCE_ASSIGNMENTS_S.CURRVAL not yet defined ,when
1490: -- planning level is project and resource list is uncategorized.
1491:
1492: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1493: AND l_uncat_flag = 'Y' THEN
1494: pa_debug.g_err_stage := 'project level uncategorized. hence not inserting project level record';
1495: IF P_PA_DEBUG_MODE = 'Y' THEN
1496: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1663:
1664: /* SET PARENT ASSIGNMENT ID FOR ALL TASK LEVEL RECORDS ONLY IF PLANNING_LEVEL
1665: IS NOT 'PROJECT'or 'TOP_TASK'. */
1666:
1667: IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1668: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1669:
1670: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1671: IF P_PA_DEBUG_MODE = 'Y' THEN
1664: /* SET PARENT ASSIGNMENT ID FOR ALL TASK LEVEL RECORDS ONLY IF PLANNING_LEVEL
1665: IS NOT 'PROJECT'or 'TOP_TASK'. */
1666:
1667: IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1668: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1669:
1670: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1671: IF P_PA_DEBUG_MODE = 'Y' THEN
1672: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1738: END IF;
1739:
1740: /* Do the following only when Planning Level is not 'Project' */
1741:
1742: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1743:
1744: UPDATE pa_resource_assignments pra1
1745: SET parent_assignment_id =
1746: (SELECT resource_assignment_id
1769: IF P_PA_DEBUG_MODE = 'Y' THEN
1770: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1771: END IF;
1772:
1773: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1774:
1775: UPDATE pa_resource_assignments pra
1776: SET parent_assignment_id = p_proj_ra_id
1777: WHERE task_id in
1794: /* UPDATE THE RESOURCE/RESOURCE GROUP LEVEL RECORDS WITH PROJECT LEVEL RECORD'S
1795: RA ID AS PARENT_ASSIGNMENT_ID. THIS IS APPLICABLE ONLY WHEN PLANNING LEVEL IS PROJECT AND
1796: RESOURCE LIST IS ATTACHED. UPDATE ONLY THOSE RECORDS FOR WHICH PARENT_ASSIGNMENT_ID IS NULL. */
1797:
1798: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1799: AND l_uncat_flag <> 'Y' THEN
1800:
1801: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Res/Res Grp level recs';
1802: IF P_PA_DEBUG_MODE = 'Y' THEN
1967: ,fnd_global.login_id
1968: ,p_unit_of_measure_tbl(i)
1969: ,p_track_as_labor_flag_tbl(i)
1970: ,-1
1971: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP) ;
1972:
1973: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
1974: IF P_PA_DEBUG_MODE = 'Y' THEN
1975: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
2147: IF P_PA_DEBUG_MODE = 'Y' THEN
2148: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2149: END IF;
2150:
2151: l_data_source := PA_FP_CONSTANTS_PKG.G_DATA_SOURCE_BUDGET_LINE;
2152: PA_FIN_PLAN_PUB.CALL_MAINTAIN_PLAN_MATRIX(p_budget_version_id => p_budget_version_id
2153: ,p_data_source => l_data_source
2154: ,x_return_status => x_return_status
2155: ,x_msg_count => x_msg_count
2163: Total number of level = number of levels in WBS + resource group (in case resource list is grouped)
2164: + resource (in case resource list attached) + 1 (project level)
2165: */
2166:
2167: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
2168: l_curr_rollup_level := 0;
2169: ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP THEN
2170: l_curr_rollup_level := 1;
2171: ELSE /* planning level is lowest task or top and lowest */
2165: */
2166:
2167: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
2168: l_curr_rollup_level := 0;
2169: ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP THEN
2170: l_curr_rollup_level := 1;
2171: ELSE /* planning level is lowest task or top and lowest */
2172: select max(wbs_level)
2173: into l_curr_rollup_level
2309: ,amount_subtype_code
2310: ,amount_type_id
2311: ,amount_subtype_id
2312: ,currency_type
2313: ,decode(amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2314: l_proj_currency_code,currency_code) --#2801522:For Qty, store Proj Curr Code
2315: ,sum(nvl(preceding_periods_amount,0))
2316: ,sum(nvl(succeeding_periods_amount,0))
2317: ,sum(nvl(prior_period_amount,0))
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
2381: currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382: AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383: tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
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
2381: currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382: AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383: tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
2384: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
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
2381: currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382: AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383: tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
2384: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2385: GROUP BY tmp.parent_assignment_id, currency_type,
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
2381: currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382: AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383: tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
2384: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2385: GROUP BY tmp.parent_assignment_id, currency_type,
2386: currency_code, amount_type_code, amount_subtype_code,
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
2381: currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382: AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383: tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
2384: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2385: GROUP BY tmp.parent_assignment_id, currency_type,
2386: currency_code, amount_type_code, amount_subtype_code,
2387: amount_type_id,amount_subtype_id,
2380: (amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
2381: currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382: AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383: tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
2384: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2385: GROUP BY tmp.parent_assignment_id, currency_type,
2386: currency_code, amount_type_code, amount_subtype_code,
2387: amount_type_id,amount_subtype_id,
2388: ppd.project_id ,ppd.budget_version_id,
2568: ,c_project_id IN NUMBER ) IS --bug#2708524
2569: SELECT TASK_ID
2570: ,prlm.PARENT_MEMBER_ID RESOURCE_LIST_MEMBER_ID
2571: ,max(decode(parent_prlm.track_as_labor_flag,'Y',
2572: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2573: ,NULL)) unit_of_measure -- Added for bug #2697999
2574: ,max(parent_prlm.track_as_labor_flag) track_as_labor_flag
2575: -- Added for bug #2697999
2576: FROM pa_resource_assignments pra
2826: ,fnd_global.login_id
2827: ,l_unit_of_measure_tbl(i)
2828: ,l_track_as_labor_flag_tbl(i)
2829: ,-1
2830: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
2831: RETURNING resource_assignment_id
2832: BULK COLLECT INTO l_parent_res_id_tbl;
2833:
2834: END IF;
2870: /* If resource list is not grouped (group_resource_type_id = 0) then the temp table will
2871: contain only resource level records. For these records we need to insert task level
2872: records only. Following step is common for resource group level as well as resource level. */
2873:
2874: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
2875:
2876: /* Creating a Loop to continuously stamp the parent_assignment_id. By the end of this
2877: loop, pa_fp_ra_map_tmp table will contain only task level records. */
2878: l_task_id_tbl.delete; /* Deleting records from TASKID pl/sql table so that it can be used later. */
2962: ,fnd_global.user_id
2963: ,sysdate
2964: ,fnd_global.user_id
2965: ,fnd_global.login_id
2966: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
2967: ,'Y' -- Modified for #2697999
2968: ,-1
2969: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
2970: RETURNING resource_assignment_id
2965: ,fnd_global.login_id
2966: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
2967: ,'Y' -- Modified for #2697999
2968: ,-1
2969: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
2970: RETURNING resource_assignment_id
2971: BULK COLLECT INTO l_ra_id_tbl;
2972: END IF;
2973:
2991: CLOSE cur_task_rec;
2992:
2993: END LOOP;
2994:
2995: END IF; /* l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT*/
2996:
2997: END IF; /* if Resource List is not uncategorized */
2998:
2999: /* The Following steps have to be done irrespective of whether the Resource List is attached or not.*/
3030: IF P_PA_DEBUG_MODE = 'Y' THEN
3031: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3032: END IF;
3033:
3034: IF (l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,
3035: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT)) THEN
3036:
3037: /* If task planning level for the version is not 'TOP_TASK'or 'PROJECT' THEN Insert middle
3038: level tasks and top task records into PA_RESOURCE_ASSIGNMENTS. */
3031: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3032: END IF;
3033:
3034: IF (l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,
3035: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT)) THEN
3036:
3037: /* If task planning level for the version is not 'TOP_TASK'or 'PROJECT' THEN Insert middle
3038: level tasks and top task records into PA_RESOURCE_ASSIGNMENTS. */
3039:
3136: ,fnd_global.user_id
3137: ,sysdate
3138: ,fnd_global.user_id
3139: ,fnd_global.login_id
3140: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
3141: ,'Y' -- Modified for #2697999
3142: ,-1
3143: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
3144: RETURNING resource_assignment_id
3139: ,fnd_global.login_id
3140: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
3141: ,'Y' -- Modified for #2697999
3142: ,-1
3143: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
3144: RETURNING resource_assignment_id
3145: BULK COLLECT INTO l_ra_id_tbl;
3146:
3147: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3184: CLOSE cur_parent_task_rec; /* manokuma: during ut review moved this to inside loop */
3185:
3186: END LOOP; /* End of the Repetitive loop. */
3187:
3188: END IF; /* l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT) */
3189:
3190: END IF; /* l_continue_procesing_flag = 'Y'*/
3191:
3192:
3208: The Project Level record should not be created, if the Planning Level is 'Project'
3209: and the Uncat Flag is 'Y'. In all other cases, the Project Level Record has to be
3210: created. */
3211:
3212: IF ((l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT)
3213: AND l_uncat_flag = 'Y' ) THEN
3214:
3215: pa_debug.g_err_stage := 'Not inserting Project Level Record';
3216: IF P_PA_DEBUG_MODE = 'Y' THEN
3263: ,fnd_global.user_id
3264: ,sysdate
3265: ,fnd_global.user_id
3266: ,fnd_global.login_id
3267: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
3268: ,'Y' -- Modified for #2697999
3269: ,-1
3270: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
3271: RETURNING resource_assignment_id
3266: ,fnd_global.login_id
3267: ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
3268: ,'Y' -- Modified for #2697999
3269: ,-1
3270: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
3271: RETURNING resource_assignment_id
3272: INTO l_proj_ra_id;
3273:
3274: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3518: ,TOTAL_PLAN_RAW_COST = nvl(TOTAL_PLAN_RAW_COST,0) + l_projfunc_raw_cost_tbl(i)
3519: ,TOTAL_PLAN_BURDENED_COST = nvl(TOTAL_PLAN_BURDENED_COST,0) + l_projfunc_burd_cost_tbl(i)
3520: ,TOTAL_PLAN_REVENUE = nvl(TOTAL_PLAN_REVENUE,0) + l_projfunc_revenue_tbl(i)
3521: ,TOTAL_PLAN_QUANTITY = nvl(TOTAL_PLAN_QUANTITY,0) + decode(l_uom_tbl(i),
3522: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,l_quantity_tbl(i),0)
3523: WHERE resource_assignment_id = l_ra_id_tbl(i)
3524: RETURNING parent_assignment_id
3525: BULK COLLECT INTO l_par_id_tbl;
3526:
3701: ppd.amount_subtype_code,
3702: ppd.amount_type_id,
3703: ppd.amount_subtype_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
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
3713: AND ((ppd.currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
3714: (ppd.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
3715: ppd.currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
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
3713: AND ((ppd.currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
3714: (ppd.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
3715: ppd.currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
3716: AND pra.resource_assignment_id in
3717: (SELECT resource_assignment_id
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
3713: AND ((ppd.currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
3714: (ppd.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
3715: ppd.currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
3716: AND pra.resource_assignment_id in
3717: (SELECT resource_assignment_id
3718: FROM pa_fp_ra_map_tmp)
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
3713: AND ((ppd.currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
3714: (ppd.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
3715: ppd.currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
3716: AND pra.resource_assignment_id in
3717: (SELECT resource_assignment_id
3718: FROM pa_fp_ra_map_tmp)
3719: AND pra.parent_assignment_id IS NOT NULL;
3786: Total number of level = number of levels in WBS + resource group (in case resource list is grouped)
3787: + resource (in case resource list attached) + 1 (project level)
3788: */
3789:
3790: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
3791: l_curr_rollup_level := 0;
3792: ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP THEN
3793: l_curr_rollup_level := 1;
3794: ELSE /* planning level is lowest task or top and lowest */
3788: */
3789:
3790: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
3791: l_curr_rollup_level := 0;
3792: ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP THEN
3793: l_curr_rollup_level := 1;
3794: ELSE /* planning level is lowest task or top and lowest */
3795: select max(wbs_level)
3796: into l_curr_rollup_level
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
4163: AND ppd1.amount_type_id = ppd2.amount_type_id
4164: AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4165: AND decode(ppd2.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
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
4163: AND ppd1.amount_type_id = ppd2.amount_type_id
4164: AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4165: AND decode(ppd2.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4166: pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
4167: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
4168: AND ppd2.resource_assignment_id = pra.resource_assignment_id -- Modified for 2801522
4169: )
4162: ppd1.currency_code,ppd2.currency_code) --#2801522:Dont check curr code for Qty
4163: AND ppd1.amount_type_id = ppd2.amount_type_id
4164: AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4165: AND decode(ppd2.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4166: pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
4167: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
4168: AND ppd2.resource_assignment_id = pra.resource_assignment_id -- Modified for 2801522
4169: )
4170: WHERE ppd1.budget_version_id = p_budget_version_id -- #2839138
4163: AND ppd1.amount_type_id = ppd2.amount_type_id
4164: AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4165: AND decode(ppd2.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4166: pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
4167: PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
4168: AND ppd2.resource_assignment_id = pra.resource_assignment_id -- Modified for 2801522
4169: )
4170: WHERE ppd1.budget_version_id = p_budget_version_id -- #2839138
4171: AND ppd1.resource_assignment_id in
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
4307: AND ppd1.amount_type_id = ppd2.amount_type_id
4308: AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4309: )
4933: /* Delete from pa_proj_periods_denorm. */
4934:
4935: DELETE FROM pa_proj_periods_denorm
4936: WHERE resource_assignment_id = p_resource_assignment_id
4937: AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION
4938: AND currency_code = p_txn_currency_code;
4939:
4940: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4941: