[Home] [Help]
5482: from ----Bug Fix: 3634912 :Changed the order of the tables
5483: pa_alloc_run_targets part,
5484: pa_alloc_run_resource_det parr,
5485: pa_resource_accum_details prad,
5486: pa_txn_accum pta
5487: where pta.txn_accum_id = prad.txn_accum_id
5488: and prad.Resource_list_member_id = parr.resource_list_member_id
5489: and prad.Project_id = part.project_id
5490: and prad.task_id = part.task_id
5667: from --Bug Fix: 3634912 : Changed the order of the tables.
5668: pa_alloc_run_targets part,
5669: pa_alloc_run_resource_det parr,
5670: pa_resource_accum_details prad,
5671: pa_txn_accum pta
5672: where pta.txn_accum_id = prad.txn_accum_id
5673: and prad.Resource_list_member_id = parr.resource_list_member_id
5674: and pta.Project_id = part.project_id
5675: and pta.task_id = part.task_id
5802: from --Bug Fix: 3634912 : Changed the order of tables
5803: pa_alloc_run_targets part,
5804: pa_alloc_run_resource_det parr,
5805: pa_resource_accum_details prad,
5806: pa_txn_accum pta
5807: where pta.txn_accum_id = prad.txn_accum_id
5808: and prad.Resource_list_member_id = parr.resource_list_member_id
5809: and pta.Project_id = part.project_id
5810: and pta.task_id = part.task_id
5969: from -- Bug Fix: 3634912 : Changed the order of tables.
5970: pa_alloc_run_targets part,
5971: pa_alloc_run_resource_det parr,
5972: pa_resource_accum_details prad,
5973: pa_txn_accum pta
5974: where pta.txn_accum_id = prad.txn_accum_id
5975: and prad.Resource_list_member_id = parr.resource_list_member_id
5976: and prad.Project_id = part.project_id
5977: and prad.task_id = part.task_id
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
6242: and prad.Resource_list_member_id = parr.resource_list_member_id
6243: and prad.Project_id = pars.project_id
6244: and prad.task_id = pars.task_id
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
6458: and prad.Resource_list_member_id = parr.resource_list_member_id
6459: and pta.Project_id = pars.project_id
6460: and pta.task_id = pars.task_id
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
6621: and prad.Resource_list_member_id = parr.resource_list_member_id
6622: and pta.Project_id = pars.project_id
6623: and pta.task_id = pars.task_id
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
6818: and prad.Resource_list_member_id = parr.resource_list_member_id
6819: and prad.Project_id = pars.project_id
6820: and prad.task_id = pars.task_id
7163: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
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
7188: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
7189: +nvl(pta.i_tot_quantity,0),
7190: 0
7191: )),0)
7192: from pa_txn_accum pta,
7193: pa_resource_accum_details prad
7194: where pta.txn_accum_id = prad.txn_accum_id
7195: and prad. Resource_list_member_id = p_rlm_id
7196: and prad. Project_id = p_project_id
7210: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
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
7545: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
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
7614: +nvl( pta.i_tot_billable_burdened_cost,0),
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
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
7674: +nvl( pta.i_tot_billable_burdened_cost,0),
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'
7729: +nvl( pta.i_tot_billable_burdened_cost,0),
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
7925: End Loop ;
7926: Else
7927: If basis_category is ACTUALS then
7928: For each project and task in pa_alloc_run_targets
7929: find the basis amount at project and task level from pa_txn_accum
7930: and insert that into pa_alloc_run_basis_det.
7931: Else
7932: For each project and task in pa_alloc_run_targets
7933: find the basis amount at project and task level from
8195: +nvl(pta.i_tot_labor_hours,0),
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
8261: +nvl(pta.i_tot_labor_hours,0),
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
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
8318: +nvl(pta.i_tot_labor_hours,0),
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'
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
8381: part.rule_id = p_rule_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
8910: WHERE part.run_id = p_run_id
8911: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8912: AND not exists
8913: (select null
8914: from pa_txn_accum pta
8915: where pta.project_id = part.project_id
8916: and pta.task_id = part.task_id
8917: and pta.pa_period = p_period
8918: and rownum = 1
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);
9013: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9014: AND part.line_num = c_target_lines_rec.line_num
9015: AND not exists
9016: (select null
9017: from pa_txn_accum pta
9018: where pta.project_id = part.project_id
9019: and pta.task_id = part.task_id
9020: and pta.pa_period = p_period
9021: and rownum = 1
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
9118: WHERE part.run_id = p_run_id
9119: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9120: AND not exists
9121: (select null
9122: from pa_txn_accum pta
9123: where pta.project_id = part.project_id
9124: and pta.task_id = part.task_id
9125: and rownum = 1
9126: );
9164: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9165: AND part.line_num = c_target_lines_rec.line_num
9166: AND not exists
9167: (select null
9168: from pa_txn_accum pta
9169: where pta.project_id = part.project_id
9170: and pta.task_id = part.task_id
9171: and rownum = 1
9172: );