[Home] [Help]
1051: , p_last_updated_by IN NUMBER
1052: , p_last_update_login IN NUMBER) IS
1053: CURSOR source_exists IS
1054: SELECT 1
1055: FROM pa_alloc_run_sources
1056: WHERE run_id = p_run_id
1057: AND project_id = p_project_id
1058: AND task_id = p_task_id;
1059: v_dummy NUMBER;
1073: p_project_id, p_task_id );
1074: END IF;
1075: CLOSE source_exists;
1076: IF (allow_insert_flag = 'Y') THEN
1077: INSERT INTO PA_ALLOC_RUN_SOURCES (
1078: RUN_ID
1079: , RULE_ID
1080: , LINE_NUM
1081: , PROJECT_ID
1114: , p_project_id IN NUMBER
1115: , p_task_id IN NUMBER ) RETURN NUMBER IS
1116: CURSOR C_excl_src_proj_task IS
1117: SELECT project_id, task_id
1118: FROM pa_alloc_run_sources
1119: WHERE run_id = p_run_id
1120: AND exclude_flag = 'Y'
1121: AND project_id = p_project_id
1122: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1293: WHERE project_id = p_proj_id
1294: AND pa_task_utils.check_child_exists(task_id) = 0;
1295: CURSOR c_alloc_run_src_projects IS
1296: SELECT project_id, task_id
1297: FROM pa_alloc_run_sources
1298: WHERE rule_id = p_rule_id
1299: AND run_id = p_run_id;
1300: RUN_SRC_PROJ_REC c_alloc_run_src_projects%ROWTYPE;
1301: CURSOR C_top_task (p_tsk_id IN NUMBER)IS
6120: )
6121: IS
6122: cursor c_projects is
6123: select distinct pars.project_id project_id
6124: from pa_alloc_run_sources pars,
6125: pa_resource_list_assignments prla
6126: where pars.project_id = prla.project_id
6127: and prla.resource_list_id = p_resource_list_id
6128: and prla.resource_list_accumulated_flag = 'Y'
6129: and pars.run_id = p_run_id
6130: and NVL(p_resource_struct_type,'RL') = 'RL'
6131: UNION All
6132: select distinct pars.project_id project_id
6133: from pa_alloc_run_sources pars,
6134: pa_rbs_prj_assignments prpa
6135: where pars.project_id = prpa.project_id
6136: and prpa.rbs_header_id = p_resource_list_id
6137: and prpa.rbs_version_id = p_rbs_version_id
6233: ,G_last_update_date
6234: ,G_last_updated_by
6235: ,G_last_update_login
6236: from --Bug Fix: 3634912 : Changed the order of the tables.
6237: pa_alloc_run_sources pars,
6238: pa_alloc_run_resource_det parr,
6239: pa_resource_accum_details prad,
6240: pa_txn_accum pta
6241: where pta.txn_accum_id = prad.txn_accum_id
6336: ,G_last_update_date
6337: ,G_last_updated_by
6338: ,G_last_update_login
6339: from pa_alloc_run_resource_det parr,
6340: pa_alloc_run_sources pars,
6341: PA_ALLOC_TXN_ACCUM_RBS_V pta
6342: where pta.Rbs_Element_Id = parr.resource_list_member_id
6343: and pta.Project_id = pars.project_id
6344: and pta.task_id = pars.task_id
6449: ,G_last_update_date
6450: ,G_last_updated_by
6451: ,G_last_update_login
6452: from -- Bug Fix: 3634912 :Changed the order of the tables.
6453: pa_alloc_run_sources pars,
6454: pa_alloc_run_resource_det parr,
6455: pa_resource_accum_details prad,
6456: pa_txn_accum pta
6457: where pta.txn_accum_id = prad.txn_accum_id
6530: ,G_last_update_date
6531: ,G_last_updated_by
6532: ,G_last_update_login
6533: from pa_alloc_run_resource_det parr,
6534: pa_alloc_run_sources pars,
6535: PA_ALLOC_TXN_ACCUM_RBS_V pta
6536: where pta.Rbs_Element_ID = parr.resource_list_member_id
6537: and pta.Project_id = pars.project_id
6538: and pta.task_id = pars.task_id
6612: ,G_last_update_date
6613: ,G_last_updated_by
6614: ,G_last_update_login
6615: from --Bug Fix: 3634912 : Changed the order of the tables.
6616: pa_alloc_run_sources pars,
6617: pa_alloc_run_resource_det parr,
6618: pa_resource_accum_details prad,
6619: pa_txn_accum pta
6620: where pta.txn_accum_id = prad.txn_accum_id
6697: ,G_last_update_date
6698: ,G_last_updated_by
6699: ,G_last_update_login
6700: from pa_alloc_run_resource_det parr,
6701: pa_alloc_run_sources pars,
6702: PA_ALLOC_TXN_ACCUM_RBS_V pta
6703: where pta.Rbs_Element_Id = parr.resource_list_member_id
6704: and pta.Project_id = pars.project_id
6705: and pta.task_id = pars.task_id
6809: ,G_last_update_date
6810: ,G_last_updated_by
6811: ,G_last_update_login
6812: from --Bug Fix: 3634912 : Changed the order of the tables
6813: pa_alloc_run_sources pars,
6814: pa_alloc_run_resource_det parr,
6815: pa_resource_accum_details prad,
6816: pa_txn_accum pta
6817: where pta.txn_accum_id = prad.txn_accum_id
6899: ,G_last_update_date
6900: ,G_last_updated_by
6901: ,G_last_update_login
6902: from pa_alloc_run_resource_det parr,
6903: pa_alloc_run_sources pars,
6904: PA_ALLOC_TXN_ACCUM_RBS_V pta
6905: where pta.Rbs_Element_Id = parr.resource_list_member_id
6906: and pta.Project_id = pars.project_id
6907: and pta.task_id = pars.task_id
7348: Steps: 1. Get the Period Year and Quarter Num values for the given run_period.
7349: 2. If source_resource_lists is not null then
7350: Populate Include_RLM_tbl.
7351: For each resource list member in the Include_RLM_tbl then
7352: Get source amount for each project and task in pa_alloc_run_sources
7353: tables for given rule_id and run_id
7354: Else
7355: For each project and task in pa_alloc_run_targets
7356: find the basis amount at project and task level.
7373: v_pool_percent NUMBER ;
7374: v_net_fixed_amount NUMBER ;
7375: cursor C_run_source_details is
7376: Select line_num, project_id , task_id
7377: from pa_alloc_run_sources
7378: where rule_id = p_rule_id
7379: and run_id = p_run_id
7380: and exclude_flag <> 'Y' ;
7381: cursor C_get_pool_amount is
7387: RULE_ID ,
7388: LINE_NUM ,
7389: PROJECT_ID ,
7390: TASK_ID
7391: From Pa_Alloc_Run_Sources
7392: Where Rule_Id = P_Rule_Id
7393: And Run_Id = P_Run_Id
7394: And Nvl(Exclude_Flag,'N') = 'N';
7395: BEGIN
7550: /* Commenting out pa_periods for bug 2757875 and using gl_period_statuses instead */
7551: -- pa_periods pp ,
7552: gl_period_statuses gl ,
7553: pa_implementations imp
7554: /* 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 */
7555: where /* Bug 3749469
7556: pars.rule_id = p_rule_id
7557: and pars.run_id = p_run_id
7558: and pars.exclude_flag = 'N'
7617: 0) * v_pool_percent ) )
7618: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
7619: -- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
7620: -- pa_periods pp
7621: /* 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 */
7622: where /* pars.rule_id = p_rule_id
7623: and pars.run_id = p_run_id
7624: and pars.exclude_flag = 'N'
7625: and pta.project_id = pars.project_id
7675: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7676: +nvl(pta.i_tot_burdened_cost,0),
7677: 0) * v_pool_percent ) )
7678: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
7679: /* 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 */
7680: where /* pars.rule_id = p_rule_id
7681: and pars.run_id = p_run_id
7682: and pars.exclude_flag = 'N'
7683: and pta.project_id = pars.project_id
7732: 0) * v_pool_percent ) )
7733: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta */
7734: pa_periods pp ,
7735: pa_projects P
7736: /* 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 */
7737: where /* pars.rule_id = p_rule_id
7738: and pars.run_id = p_run_id
7739: and pars.exclude_flag = 'N'
7740: and pars.project_id = p.project_id
8200: /* Commenting out pa_periods and using gl_period_statuses instead for bug 2757875 */
8201: -- pa_periods pp ,
8202: gl_period_statuses gl,
8203: pa_implementations imp
8204: /* 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 */
8205: where /* Bug 3749469 part.rule_id = p_rule_id
8206: and part.run_id = p_run_id
8207: and part.exclude_flag = 'N'
8208: and pta.project_id = part.project_id
8264: 0 )) AMOUNT
8265: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
8266: -- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
8267: -- pa_periods pp
8268: /* 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 */
8269: where /* Bug 3749469
8270: part.rule_id = p_rule_id
8271: and part.run_id = p_run_id
8272: and part.exclude_flag = 'N'
8319: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8320: +nvl(pta.i_tot_quantity,0),
8321: 0 )) AMOUNT
8322: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
8323: /* 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 */
8324: where /* Bug 3749469 part.rule_id = p_rule_id
8325: and part.run_id = p_run_id
8326: and part.exclude_flag = 'N'
8327: and pta.project_id = part.project_id
8372: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8373: +nvl(pta.i_tot_quantity,0),
8374: 0 )) AMOUNT
8375: /* Rearranged the tables in the FROM clause and commented out pa_projects_all for bug 2751178 */
8376: 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 */
8377: pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
8378: pa_periods pp
8379: -- pa_projects_all p ,
8380: where /* Bug 3749469
9831: /*Code Changes for Bug No.2984871 start */
9832: Commit;
9833: /*Code Changes for Bug No.2984871 end */
9834: End Loop;
9835: -- Delete Transactions from pa_alloc_run_sources
9836: While 1=1 Loop /* Bug 2176096 */
9837: Delete from pa_alloc_run_sources
9838: where run_id = p_run_id and rownum < 10001;
9839: /* Commented for Bug 2984871 Commit; */
9833: /*Code Changes for Bug No.2984871 end */
9834: End Loop;
9835: -- Delete Transactions from pa_alloc_run_sources
9836: While 1=1 Loop /* Bug 2176096 */
9837: Delete from pa_alloc_run_sources
9838: where run_id = p_run_id and rownum < 10001;
9839: /* Commented for Bug 2984871 Commit; */
9840: If Sql%Rowcount = 0 THen
9841: Exit;
9981: /*Code Changes for Bug No.2984871 start */
9982: Commit;
9983: /*Code Changes for Bug No.2984871 end */
9984: End Loop;
9985: -- Delete Transactions from pa_alloc_run_sources
9986: While 1=1 Loop /* Bug 2176096 */
9987: Delete from pa_alloc_run_sources
9988: where run_id = l_run_id and rownum < 10001;
9989: /* Commented for Bug 2984871 Commit; */
9983: /*Code Changes for Bug No.2984871 end */
9984: End Loop;
9985: -- Delete Transactions from pa_alloc_run_sources
9986: While 1=1 Loop /* Bug 2176096 */
9987: Delete from pa_alloc_run_sources
9988: where run_id = l_run_id and rownum < 10001;
9989: /* Commented for Bug 2984871 Commit; */
9990: If Sql%Rowcount = 0 THen
9991: Exit;