[Home] [Help]
1059: , p_last_updated_by IN NUMBER
1060: , p_last_update_login IN NUMBER) IS
1061: CURSOR source_exists IS
1062: SELECT 1
1063: FROM pa_alloc_run_sources
1064: WHERE run_id = p_run_id
1065: AND project_id = p_project_id
1066: AND task_id = p_task_id;
1067: v_dummy NUMBER;
1081: p_project_id, p_task_id );
1082: END IF;
1083: CLOSE source_exists;
1084: IF (allow_insert_flag = 'Y') THEN
1085: INSERT INTO PA_ALLOC_RUN_SOURCES (
1086: RUN_ID
1087: , RULE_ID
1088: , LINE_NUM
1089: , PROJECT_ID
1122: , p_project_id IN NUMBER
1123: , p_task_id IN NUMBER ) RETURN NUMBER IS
1124: CURSOR C_excl_src_proj_task IS
1125: SELECT project_id, task_id
1126: FROM pa_alloc_run_sources
1127: WHERE run_id = p_run_id
1128: AND exclude_flag = 'Y'
1129: AND project_id = p_project_id
1130: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1303: WHERE project_id = p_proj_id
1304: AND pa_task_utils.check_child_exists(task_id) = 0;
1305: CURSOR c_alloc_run_src_projects IS
1306: SELECT project_id, task_id
1307: FROM pa_alloc_run_sources
1308: WHERE rule_id = p_rule_id
1309: AND run_id = p_run_id;
1310: RUN_SRC_PROJ_REC c_alloc_run_src_projects%ROWTYPE;
1311: CURSOR C_top_task (p_tsk_id IN NUMBER)IS
6143: )
6144: IS
6145: cursor c_projects is
6146: select distinct pars.project_id project_id
6147: from pa_alloc_run_sources pars,
6148: pa_resource_list_assignments prla
6149: where pars.project_id = prla.project_id
6150: and prla.resource_list_id = p_resource_list_id
6151: and prla.resource_list_accumulated_flag = 'Y'
6152: and pars.run_id = p_run_id
6153: and NVL(p_resource_struct_type,'RL') = 'RL'
6154: UNION All
6155: select distinct pars.project_id project_id
6156: from pa_alloc_run_sources pars,
6157: pa_rbs_prj_assignments prpa
6158: where pars.project_id = prpa.project_id
6159: and prpa.rbs_header_id = p_resource_list_id
6160: and prpa.rbs_version_id = p_rbs_version_id
6256: ,G_last_update_date
6257: ,G_last_updated_by
6258: ,G_last_update_login
6259: from --Bug Fix: 3634912 : Changed the order of the tables.
6260: pa_alloc_run_sources pars,
6261: pa_alloc_run_resource_det parr,
6262: pa_resource_accum_details prad,
6263: pa_txn_accum pta
6264: where pta.txn_accum_id = prad.txn_accum_id
6359: ,G_last_update_date
6360: ,G_last_updated_by
6361: ,G_last_update_login
6362: from pa_alloc_run_resource_det parr,
6363: pa_alloc_run_sources pars,
6364: PA_ALLOC_TXN_ACCUM_RBS_V pta
6365: where pta.Rbs_Element_Id = parr.resource_list_member_id
6366: and pta.Project_id = pars.project_id
6367: and pta.task_id = pars.task_id
6472: ,G_last_update_date
6473: ,G_last_updated_by
6474: ,G_last_update_login
6475: from -- Bug Fix: 3634912 :Changed the order of the tables.
6476: pa_alloc_run_sources pars,
6477: pa_alloc_run_resource_det parr,
6478: pa_resource_accum_details prad,
6479: pa_txn_accum pta
6480: where pta.txn_accum_id = prad.txn_accum_id
6553: ,G_last_update_date
6554: ,G_last_updated_by
6555: ,G_last_update_login
6556: from pa_alloc_run_resource_det parr,
6557: pa_alloc_run_sources pars,
6558: PA_ALLOC_TXN_ACCUM_RBS_V pta
6559: where pta.Rbs_Element_ID = parr.resource_list_member_id
6560: and pta.Project_id = pars.project_id
6561: and pta.task_id = pars.task_id
6635: ,G_last_update_date
6636: ,G_last_updated_by
6637: ,G_last_update_login
6638: from --Bug Fix: 3634912 : Changed the order of the tables.
6639: pa_alloc_run_sources pars,
6640: pa_alloc_run_resource_det parr,
6641: pa_resource_accum_details prad,
6642: pa_txn_accum pta
6643: where pta.txn_accum_id = prad.txn_accum_id
6720: ,G_last_update_date
6721: ,G_last_updated_by
6722: ,G_last_update_login
6723: from pa_alloc_run_resource_det parr,
6724: pa_alloc_run_sources pars,
6725: PA_ALLOC_TXN_ACCUM_RBS_V pta
6726: where pta.Rbs_Element_Id = parr.resource_list_member_id
6727: and pta.Project_id = pars.project_id
6728: and pta.task_id = pars.task_id
6832: ,G_last_update_date
6833: ,G_last_updated_by
6834: ,G_last_update_login
6835: from --Bug Fix: 3634912 : Changed the order of the tables
6836: pa_alloc_run_sources pars,
6837: pa_alloc_run_resource_det parr,
6838: pa_resource_accum_details prad,
6839: pa_txn_accum pta
6840: where pta.txn_accum_id = prad.txn_accum_id
6922: ,G_last_update_date
6923: ,G_last_updated_by
6924: ,G_last_update_login
6925: from pa_alloc_run_resource_det parr,
6926: pa_alloc_run_sources pars,
6927: PA_ALLOC_TXN_ACCUM_RBS_V pta
6928: where pta.Rbs_Element_Id = parr.resource_list_member_id
6929: and pta.Project_id = pars.project_id
6930: and pta.task_id = pars.task_id
7371: Steps: 1. Get the Period Year and Quarter Num values for the given run_period.
7372: 2. If source_resource_lists is not null then
7373: Populate Include_RLM_tbl.
7374: For each resource list member in the Include_RLM_tbl then
7375: Get source amount for each project and task in pa_alloc_run_sources
7376: tables for given rule_id and run_id
7377: Else
7378: For each project and task in pa_alloc_run_targets
7379: find the basis amount at project and task level.
7401: v_pool_percent NUMBER ;
7402: v_net_fixed_amount NUMBER ;
7403: cursor C_run_source_details is
7404: Select line_num, project_id , task_id
7405: from pa_alloc_run_sources
7406: where rule_id = p_rule_id
7407: and run_id = p_run_id
7408: and exclude_flag <> 'Y' ;
7409: cursor C_get_pool_amount is
7448: RULE_ID ,
7449: LINE_NUM ,
7450: PROJECT_ID ,
7451: TASK_ID
7452: From Pa_Alloc_Run_Sources
7453: Where Rule_Id = P_Rule_Id
7454: And Run_Id = P_Run_Id
7455: And Nvl(Exclude_Flag,'N') = 'N';
7456: BEGIN
7611: /* Commenting out pa_periods for bug 2757875 and using gl_period_statuses instead */
7612: -- pa_periods pp ,
7613: gl_period_statuses gl ,
7614: pa_implementations imp
7615: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7616: where /* Bug 3749469
7617: pars.rule_id = p_rule_id
7618: and pars.run_id = p_run_id
7619: and pars.exclude_flag = 'N'
7678: 0) * v_pool_percent ) )
7679: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
7680: -- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
7681: -- pa_periods pp
7682: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7683: where /* pars.rule_id = p_rule_id
7684: and pars.run_id = p_run_id
7685: and pars.exclude_flag = 'N'
7686: and pta.project_id = pars.project_id
7736: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7737: +nvl(pta.i_tot_burdened_cost,0),
7738: 0) * v_pool_percent ) )
7739: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
7740: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7741: where /* pars.rule_id = p_rule_id
7742: and pars.run_id = p_run_id
7743: and pars.exclude_flag = 'N'
7744: and pta.project_id = pars.project_id
7793: 0) * v_pool_percent ) )
7794: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta */
7795: pa_periods pp ,
7796: pa_projects P
7797: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7798: where /* pars.rule_id = p_rule_id
7799: and pars.run_id = p_run_id
7800: and pars.exclude_flag = 'N'
7801: and pars.project_id = p.project_id
8305: /* Commenting out pa_periods and using gl_period_statuses instead for bug 2757875 */
8306: -- pa_periods pp ,
8307: gl_period_statuses gl,
8308: pa_implementations imp
8309: /* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8310: where /* Bug 3749469 part.rule_id = p_rule_id
8311: and part.run_id = p_run_id
8312: and part.exclude_flag = 'N'
8313: and pta.project_id = part.project_id
8369: 0 )) AMOUNT
8370: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
8371: -- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
8372: -- pa_periods pp
8373: /* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8374: where /* Bug 3749469
8375: part.rule_id = p_rule_id
8376: and part.run_id = p_run_id
8377: and part.exclude_flag = 'N'
8424: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8425: +nvl(pta.i_tot_quantity,0),
8426: 0 )) AMOUNT
8427: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
8428: /* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8429: where /* Bug 3749469 part.rule_id = p_rule_id
8430: and part.run_id = p_run_id
8431: and part.exclude_flag = 'N'
8432: and pta.project_id = part.project_id
8477: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8478: +nvl(pta.i_tot_quantity,0),
8479: 0 )) AMOUNT
8480: /* Rearranged the tables in the FROM clause and commented out pa_projects_all for bug 2751178 */
8481: from /* pa_alloc_run_targets part, */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8482: pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
8483: pa_periods pp
8484: -- pa_projects_all p ,
8485: where /* Bug 3749469
9936: /*Code Changes for Bug No.2984871 start */
9937: Commit;
9938: /*Code Changes for Bug No.2984871 end */
9939: End Loop;
9940: -- Delete Transactions from pa_alloc_run_sources
9941: While 1=1 Loop /* Bug 2176096 */
9942: Delete from pa_alloc_run_sources
9943: where run_id = p_run_id and rownum < 10001;
9944: /* Commented for Bug 2984871 Commit; */
9938: /*Code Changes for Bug No.2984871 end */
9939: End Loop;
9940: -- Delete Transactions from pa_alloc_run_sources
9941: While 1=1 Loop /* Bug 2176096 */
9942: Delete from pa_alloc_run_sources
9943: where run_id = p_run_id and rownum < 10001;
9944: /* Commented for Bug 2984871 Commit; */
9945: If Sql%Rowcount = 0 THen
9946: Exit;
10086: /*Code Changes for Bug No.2984871 start */
10087: Commit;
10088: /*Code Changes for Bug No.2984871 end */
10089: End Loop;
10090: -- Delete Transactions from pa_alloc_run_sources
10091: While 1=1 Loop /* Bug 2176096 */
10092: Delete from pa_alloc_run_sources
10093: where run_id = l_run_id and rownum < 10001;
10094: /* Commented for Bug 2984871 Commit; */
10088: /*Code Changes for Bug No.2984871 end */
10089: End Loop;
10090: -- Delete Transactions from pa_alloc_run_sources
10091: While 1=1 Loop /* Bug 2176096 */
10092: Delete from pa_alloc_run_sources
10093: where run_id = l_run_id and rownum < 10001;
10094: /* Commented for Bug 2984871 Commit; */
10095: If Sql%Rowcount = 0 THen
10096: Exit;