[Home] [Help]
5516: and parr.member_type = 'B'
5517: and part.project_id = c_projects_rec.project_id
5518: and part.exclude_flag = 'N'
5519: and exists
5520: /* Using gl_period_statuses instead of pa_periods for Bug 2757875 */
5521: (select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
5522: gl.period_name
5523: from gl_period_statuses gl,
5524: pa_implementations imp
5529: and gl.closing_status in ('C','F','O','P')
5530: and gl.end_date between p_amttype_start_date
5531: and p_run_period_end_date)
5532: /**** (select 1
5533: from pa_periods pp
5534: where pta.pa_period = pp.period_name
5535: and pp.end_date between p_amttype_start_date
5536: and p_run_period_end_date ) **** Commented for Bug 2757875 ****/
5537: group by part.run_id
5838: and part.exclude_flag = 'N'
5839: and pta.gl_period = p_period /*Using gl_period on pta directly for bug 2757875 */
5840: /**** and pta.pa_period IN
5841: (SELECT period_name
5842: FROM pa_periods pp
5843: WHERE pp.gl_period_name = p_period) **** Commented for bug 2757875 ****/
5844: group by part.run_id
5845: ,part.rule_id
5846: ,part.line_num
6005: and part.exclude_flag = 'N'
6006: and exists
6007: (select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
6008: 1
6009: from pa_periods pp
6010: where pta.pa_period = pp.period_name
6011: and pp.end_date <= p_run_period_end_date) /* Added for bug 2757875 */
6012: /**** and pp.end_date between v_project_start_date
6013: and p_run_period_end_date ) **** Commented for bug 2757875 ****/
6079: and exists
6080: (
6081: select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
6082: 1
6083: from pa_periods pp
6084: where pta.pa_period = pp.period_name
6085: and pp.end_date <= p_run_period_end_date
6086: )
6087: group by part.run_id
6270: and parr.member_type = 'S'
6271: and pars.project_id = c_projects_rec.project_id
6272: and pars.exclude_flag = 'N'
6273: and exists
6274: /* Using gl_period_statuses instead of pa_periods for bug 2757875 */
6275: (select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
6276: gl.period_name
6277: From gl_period_statuses gl,
6278: pa_implementations imp
6284: and gl.end_date between p_amttype_start_date
6285: and p_run_period_end_date
6286: )
6287: /* (select 1
6288: from pa_periods pp
6289: where pta.pa_period = pp.period_name
6290: and pp.end_date between p_amttype_start_date
6291: and p_run_period_end_date) **** 2757875 */
6292: group by pars.run_id
6651: and pars.project_id = c_projects_rec.project_id
6652: and pta.gl_period = p_period /* Using gl_period on pta directly :bug 2757875 */
6653: /**** and pta.pa_period IN
6654: (SELECT period_name
6655: FROM pa_periods pp
6656: WHERE pp.gl_period_name = p_period) **** Commented for bug 2757875 ****/
6657: group by pars.run_id
6658: ,pars.rule_id
6659: ,pars.line_num
6848: and pars.project_id = c_projects_rec.project_id
6849: and exists
6850: (select /*+ NO_UNNEST */ --Bug 3634912 : Added Hint.
6851: 1
6852: from pa_periods pp
6853: where pta.pa_period = pp.period_name
6854: and pp.end_date <= p_run_period_end_date) /* Added for bug 2757875 */
6855: /**** and pp.end_date between v_project_start_date
6856: and p_run_period_end_date) **** Commented for bug 2757875****/
6936: and pars.project_id = C_projects_rec.project_id
6937: and exists
6938: (select /*+ NO_UNNEST */ -- Bug3634912 : Added hint .
6939: 1
6940: from pa_periods pp
6941: where pta.pa_period = pp.period_name
6942: and pp.end_date <= p_run_period_end_date
6943: )
6944: group by pars.run_id
7187: +nvl(pta.i_tot_quantity,0),
7188: 0
7189: )),0)
7190: from pa_txn_accum pta,
7191: pa_periods pp,
7192: pa_resource_accum_details prad
7193: where pta.txn_accum_id = prad.txn_accum_id
7194: and prad. Resource_list_member_id = p_rlm_id
7195: and prad. Project_id = p_project_id
7234: +nvl(pta.i_tot_quantity,0),
7235: 0
7236: )),0)
7237: from pa_txn_accum pta,
7238: pa_periods pp ,
7239: pa_resource_accum_details prad
7240: where pta.txn_accum_id = prad.txn_accum_id
7241: and prad. Resource_list_member_id = p_rlm_id
7242: and prad. Project_id = p_project_id
7607: +nvl(pta.i_tot_burdened_cost,0),
7608: 0) * v_pool_percent ) )
7609: from pa_alloc_txn_accum_v pta,
7610: /* FP.M : Allocation Impact : pa_txn_accum pta */
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 */
7608: 0) * v_pool_percent ) )
7609: from pa_alloc_txn_accum_v pta,
7610: /* FP.M : Allocation Impact : pa_txn_accum pta */
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
7676: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7677: +nvl(pta.i_tot_burdened_cost,0),
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
7677: +nvl(pta.i_tot_burdened_cost,0),
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'
7791: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7792: +nvl(pta.i_tot_burdened_cost,0),
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
8301: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8302: +nvl(pta.i_tot_quantity,0),
8303: 0 )) AMOUNT
8304: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
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 */
8302: +nvl(pta.i_tot_quantity,0),
8303: 0 )) AMOUNT
8304: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
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
8367: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8368: +nvl(pta.i_tot_quantity,0),
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
8368: +nvl(pta.i_tot_quantity,0),
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
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
8486: part.rule_id = p_rule_id
8487: and part.run_id = p_run_id
8911: WHERE part.run_id = p_run_id
8912: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8913: AND not exists
8914: (select null
8915: from pa_txn_accum pta,pa_periods pp
8916: where pta.project_id = part.project_id
8917: and pta.task_id = part.task_id
8918: and pp.period_name = pta.pa_period
8919: and pp.end_date between p_amttype_start_date
8959: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8960: AND part.line_num = c_target_lines_rec.line_num
8961: AND not exists
8962: (select null
8963: from pa_txn_accum pta,pa_periods pp
8964: where pta.project_id = part.project_id
8965: and pta.task_id = part.task_id
8966: and pp.period_name = pta.pa_period
8967: and pp.end_date between p_amttype_start_date
9066: WHERE part.run_id = p_run_id
9067: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9068: AND not exists
9069: (select null
9070: from pa_txn_accum pta,pa_periods pp
9071: where pta.project_id = part.project_id
9072: and pta.task_id = part.task_id
9073: and pp.period_name = pta.pa_period
9074: and pp.gl_period_name = p_period);
9168: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9169: AND part.line_num = c_target_lines_rec.line_num
9170: AND not exists
9171: (select null
9172: from pa_txn_accum pta,pa_periods pp
9173: where pta.project_id = part.project_id
9174: and pta.task_id = part.task_id
9175: and pp.period_name = pta.pa_period
9176: and pp.gl_period_name = p_period
9208: end loop;
9209: END IF; /* p_run_period_type = 'GL' */
9210: END IF;
9211: ELSIF p_amt_type = 'ITD' THEN
9212: /* For ITD let's consider all periods in pa_periods table rather than
9213: doing this delete for each project id in the targets table. Hence do
9214: not use pa_periods table in this case. */
9215: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9216: IF P_DEBUG_MODE = 'Y' THEN
9210: END IF;
9211: ELSIF p_amt_type = 'ITD' THEN
9212: /* For ITD let's consider all periods in pa_periods table rather than
9213: doing this delete for each project id in the targets table. Hence do
9214: not use pa_periods table in this case. */
9215: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9216: IF P_DEBUG_MODE = 'Y' THEN
9217: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9218: END IF;