[Home] [Help]
5493: and parr.member_type = 'B'
5494: and part.project_id = c_projects_rec.project_id
5495: and part.exclude_flag = 'N'
5496: and exists
5497: /* Using gl_period_statuses instead of pa_periods for Bug 2757875 */
5498: (select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
5499: gl.period_name
5500: from gl_period_statuses gl,
5501: pa_implementations imp
5506: and gl.closing_status in ('C','F','O','P')
5507: and gl.end_date between p_amttype_start_date
5508: and p_run_period_end_date)
5509: /**** (select 1
5510: from pa_periods pp
5511: where pta.pa_period = pp.period_name
5512: and pp.end_date between p_amttype_start_date
5513: and p_run_period_end_date ) **** Commented for Bug 2757875 ****/
5514: group by part.run_id
5815: and part.exclude_flag = 'N'
5816: and pta.gl_period = p_period /*Using gl_period on pta directly for bug 2757875 */
5817: /**** and pta.pa_period IN
5818: (SELECT period_name
5819: FROM pa_periods pp
5820: WHERE pp.gl_period_name = p_period) **** Commented for bug 2757875 ****/
5821: group by part.run_id
5822: ,part.rule_id
5823: ,part.line_num
5982: and part.exclude_flag = 'N'
5983: and exists
5984: (select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
5985: 1
5986: from pa_periods pp
5987: where pta.pa_period = pp.period_name
5988: and pp.end_date <= p_run_period_end_date) /* Added for bug 2757875 */
5989: /**** and pp.end_date between v_project_start_date
5990: and p_run_period_end_date ) **** Commented for bug 2757875 ****/
6056: and exists
6057: (
6058: select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
6059: 1
6060: from pa_periods pp
6061: where pta.pa_period = pp.period_name
6062: and pp.end_date <= p_run_period_end_date
6063: )
6064: group by part.run_id
6247: and parr.member_type = 'S'
6248: and pars.project_id = c_projects_rec.project_id
6249: and pars.exclude_flag = 'N'
6250: and exists
6251: /* Using gl_period_statuses instead of pa_periods for bug 2757875 */
6252: (select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
6253: gl.period_name
6254: From gl_period_statuses gl,
6255: pa_implementations imp
6261: and gl.end_date between p_amttype_start_date
6262: and p_run_period_end_date
6263: )
6264: /* (select 1
6265: from pa_periods pp
6266: where pta.pa_period = pp.period_name
6267: and pp.end_date between p_amttype_start_date
6268: and p_run_period_end_date) **** 2757875 */
6269: group by pars.run_id
6628: and pars.project_id = c_projects_rec.project_id
6629: and pta.gl_period = p_period /* Using gl_period on pta directly :bug 2757875 */
6630: /**** and pta.pa_period IN
6631: (SELECT period_name
6632: FROM pa_periods pp
6633: WHERE pp.gl_period_name = p_period) **** Commented for bug 2757875 ****/
6634: group by pars.run_id
6635: ,pars.rule_id
6636: ,pars.line_num
6825: and pars.project_id = c_projects_rec.project_id
6826: and exists
6827: (select /*+ NO_UNNEST */ --Bug 3634912 : Added Hint.
6828: 1
6829: from pa_periods pp
6830: where pta.pa_period = pp.period_name
6831: and pp.end_date <= p_run_period_end_date) /* Added for bug 2757875 */
6832: /**** and pp.end_date between v_project_start_date
6833: and p_run_period_end_date) **** Commented for bug 2757875****/
6913: and pars.project_id = C_projects_rec.project_id
6914: and exists
6915: (select /*+ NO_UNNEST */ -- Bug3634912 : Added hint .
6916: 1
6917: from pa_periods pp
6918: where pta.pa_period = pp.period_name
6919: and pp.end_date <= p_run_period_end_date
6920: )
6921: group by pars.run_id
7164: +nvl(pta.i_tot_quantity,0),
7165: 0
7166: )),0)
7167: from pa_txn_accum pta,
7168: pa_periods pp,
7169: pa_resource_accum_details prad
7170: where pta.txn_accum_id = prad.txn_accum_id
7171: and prad. Resource_list_member_id = p_rlm_id
7172: and prad. Project_id = p_project_id
7211: +nvl(pta.i_tot_quantity,0),
7212: 0
7213: )),0)
7214: from pa_txn_accum pta,
7215: pa_periods pp ,
7216: pa_resource_accum_details prad
7217: where pta.txn_accum_id = prad.txn_accum_id
7218: and prad. Resource_list_member_id = p_rlm_id
7219: and prad. Project_id = p_project_id
7546: +nvl(pta.i_tot_burdened_cost,0),
7547: 0) * v_pool_percent ) )
7548: from pa_alloc_txn_accum_v pta,
7549: /* FP.M : Allocation Impact : pa_txn_accum pta */
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 */
7547: 0) * v_pool_percent ) )
7548: from pa_alloc_txn_accum_v pta,
7549: /* FP.M : Allocation Impact : pa_txn_accum pta */
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
7615: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7616: +nvl(pta.i_tot_burdened_cost,0),
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
7616: +nvl(pta.i_tot_burdened_cost,0),
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'
7730: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7731: +nvl(pta.i_tot_burdened_cost,0),
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
8196: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8197: +nvl(pta.i_tot_quantity,0),
8198: 0 )) AMOUNT
8199: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
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 */
8197: +nvl(pta.i_tot_quantity,0),
8198: 0 )) AMOUNT
8199: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
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
8262: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8263: +nvl(pta.i_tot_quantity,0),
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
8263: +nvl(pta.i_tot_quantity,0),
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
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
8381: part.rule_id = p_rule_id
8382: and part.run_id = p_run_id
8806: WHERE part.run_id = p_run_id
8807: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8808: AND not exists
8809: (select null
8810: from pa_txn_accum pta,pa_periods pp
8811: where pta.project_id = part.project_id
8812: and pta.task_id = part.task_id
8813: and pp.period_name = pta.pa_period
8814: and pp.end_date between p_amttype_start_date
8854: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8855: AND part.line_num = c_target_lines_rec.line_num
8856: AND not exists
8857: (select null
8858: from pa_txn_accum pta,pa_periods pp
8859: where pta.project_id = part.project_id
8860: and pta.task_id = part.task_id
8861: and pp.period_name = pta.pa_period
8862: and pp.end_date between p_amttype_start_date
8961: WHERE part.run_id = p_run_id
8962: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8963: AND not exists
8964: (select null
8965: from pa_txn_accum pta,pa_periods pp
8966: where pta.project_id = part.project_id
8967: and pta.task_id = part.task_id
8968: and pp.period_name = pta.pa_period
8969: and pp.gl_period_name = p_period);
9063: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9064: AND part.line_num = c_target_lines_rec.line_num
9065: AND not exists
9066: (select null
9067: from pa_txn_accum pta,pa_periods pp
9068: where pta.project_id = part.project_id
9069: and pta.task_id = part.task_id
9070: and pp.period_name = pta.pa_period
9071: and pp.gl_period_name = p_period
9103: end loop;
9104: END IF; /* p_run_period_type = 'GL' */
9105: END IF;
9106: ELSIF p_amt_type = 'ITD' THEN
9107: /* For ITD let's consider all periods in pa_periods table rather than
9108: doing this delete for each project id in the targets table. Hence do
9109: not use pa_periods table in this case. */
9110: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9111: IF P_DEBUG_MODE = 'Y' THEN
9105: END IF;
9106: ELSIF p_amt_type = 'ITD' THEN
9107: /* For ITD let's consider all periods in pa_periods table rather than
9108: doing this delete for each project id in the targets table. Hence do
9109: not use pa_periods table in this case. */
9110: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9111: IF P_DEBUG_MODE = 'Y' THEN
9112: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9113: END IF;