[Home] [Help]
5505: from ----Bug Fix: 3634912 :Changed the order of the tables
5506: pa_alloc_run_targets part,
5507: pa_alloc_run_resource_det parr,
5508: pa_resource_accum_details prad,
5509: pa_txn_accum pta
5510: where pta.txn_accum_id = prad.txn_accum_id
5511: and prad.Resource_list_member_id = parr.resource_list_member_id
5512: and prad.Project_id = part.project_id
5513: and prad.task_id = part.task_id
5690: from --Bug Fix: 3634912 : Changed the order of the tables.
5691: pa_alloc_run_targets part,
5692: pa_alloc_run_resource_det parr,
5693: pa_resource_accum_details prad,
5694: pa_txn_accum pta
5695: where pta.txn_accum_id = prad.txn_accum_id
5696: and prad.Resource_list_member_id = parr.resource_list_member_id
5697: and pta.Project_id = part.project_id
5698: and pta.task_id = part.task_id
5825: from --Bug Fix: 3634912 : Changed the order of tables
5826: pa_alloc_run_targets part,
5827: pa_alloc_run_resource_det parr,
5828: pa_resource_accum_details prad,
5829: pa_txn_accum pta
5830: where pta.txn_accum_id = prad.txn_accum_id
5831: and prad.Resource_list_member_id = parr.resource_list_member_id
5832: and pta.Project_id = part.project_id
5833: and pta.task_id = part.task_id
5992: from -- Bug Fix: 3634912 : Changed the order of tables.
5993: pa_alloc_run_targets part,
5994: pa_alloc_run_resource_det parr,
5995: pa_resource_accum_details prad,
5996: pa_txn_accum pta
5997: where pta.txn_accum_id = prad.txn_accum_id
5998: and prad.Resource_list_member_id = parr.resource_list_member_id
5999: and prad.Project_id = part.project_id
6000: and prad.task_id = part.task_id
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
6265: and prad.Resource_list_member_id = parr.resource_list_member_id
6266: and prad.Project_id = pars.project_id
6267: and prad.task_id = pars.task_id
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
6481: and prad.Resource_list_member_id = parr.resource_list_member_id
6482: and pta.Project_id = pars.project_id
6483: and pta.task_id = pars.task_id
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
6644: and prad.Resource_list_member_id = parr.resource_list_member_id
6645: and pta.Project_id = pars.project_id
6646: and pta.task_id = pars.task_id
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
6841: and prad.Resource_list_member_id = parr.resource_list_member_id
6842: and prad.Project_id = pars.project_id
6843: and prad.task_id = pars.task_id
7186: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
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
7211: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
7212: +nvl(pta.i_tot_quantity,0),
7213: 0
7214: )),0)
7215: from pa_txn_accum pta,
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
7233: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
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
7606: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
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
7675: +nvl( pta.i_tot_billable_burdened_cost,0),
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
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
7735: +nvl( pta.i_tot_billable_burdened_cost,0),
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'
7790: +nvl( pta.i_tot_billable_burdened_cost,0),
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
8030: End Loop ;
8031: Else
8032: If basis_category is ACTUALS then
8033: For each project and task in pa_alloc_run_targets
8034: find the basis amount at project and task level from pa_txn_accum
8035: and insert that into pa_alloc_run_basis_det.
8036: Else
8037: For each project and task in pa_alloc_run_targets
8038: find the basis amount at project and task level from
8300: +nvl(pta.i_tot_labor_hours,0),
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
8366: +nvl(pta.i_tot_labor_hours,0),
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
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
8423: +nvl(pta.i_tot_labor_hours,0),
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'
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
8486: part.rule_id = p_rule_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
9015: WHERE part.run_id = p_run_id
9016: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9017: AND not exists
9018: (select null
9019: from pa_txn_accum pta
9020: where pta.project_id = part.project_id
9021: and pta.task_id = part.task_id
9022: and pta.pa_period = p_period
9023: and rownum = 1
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);
9118: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9119: AND part.line_num = c_target_lines_rec.line_num
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 pta.pa_period = p_period
9126: and rownum = 1
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
9223: WHERE part.run_id = p_run_id
9224: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9225: AND not exists
9226: (select null
9227: from pa_txn_accum pta
9228: where pta.project_id = part.project_id
9229: and pta.task_id = part.task_id
9230: and rownum = 1
9231: );
9269: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9270: AND part.line_num = c_target_lines_rec.line_num
9271: AND not exists
9272: (select null
9273: from pa_txn_accum pta
9274: where pta.project_id = part.project_id
9275: and pta.task_id = part.task_id
9276: and rownum = 1
9277: );