[Home] [Help]
117: cursor c_target_det is
118: select '1'
119: from dual
120: where exists ( select 'Y'
121: from pa_alloc_run_targets
122: where run_id = x_run_id
123: and exclude_flag <> 'Y' ) ;
124: v_alloc_rule_rec C_get_rule%ROWTYPE;
125: v_status NUMBER:= NULL;
1129: AND project_id = p_project_id
1130: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1131: CURSOR C_excl_trg_proj_task IS
1132: SELECT project_id, task_id
1133: FROM pa_alloc_run_targets
1134: WHERE run_id = p_run_id
1135: AND exclude_flag = 'Y'
1136: AND project_id = p_project_id
1137: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1820: , p_bas_method IN VARCHAR2
1821: , p_dup_targets_flag IN VARCHAR2 ) IS
1822: CURSOR target_exists IS
1823: SELECT 1
1824: FROM pa_alloc_run_targets
1825: WHERE run_id = p_run_id
1826: AND project_id = p_project_id
1827: AND task_id = p_task_id;
1828: v_dummy NUMBER;
1847: END IF;
1848: IF (allow_insert_flag = 'Y') THEN
1849: /* added if condition for bug 2619977 */
1850: /* Invoking FP API to get budget_version_id. Will populate the ID in
1851: pa_alloc_run_targets based on budget type or FP type selected. After
1852: this processing become same for both basis - budgets and FPs */
1853: PA_FIN_PLAN_UTILS.GET_COST_BASE_VERSION_INFO
1854: ( p_project_id
1855: ,G_basis_fin_plan_Type_id
1867: '] Return status ['||x_return_status||']' );
1868: END IF;
1869: ---- Here needs a validation of
1870: /******** Need to handle the if error is generated in API *****/
1871: INSERT INTO PA_ALLOC_RUN_TARGETS (
1872: RUN_ID
1873: , RULE_ID
1874: , LINE_NUM
1875: , PROJECT_ID
2066: AND pt.chargeable_flag = 'Y'
2067: AND x_ei_date between nvl(pt.start_date,x_ei_date) and nvl(pt.completion_date,x_ei_date) ;
2068: CURSOR c_alloc_run_trg_projects IS
2069: SELECT project_id, task_id
2070: FROM pa_alloc_run_targets
2071: WHERE run_id = p_run_id;
2072: CURSOR c_rule_entry_level_code IS
2073: SELECT entry_level_code
2074: FROM pa_budget_entry_methods
2108: v_cx_err_flag VARCHAR2(1) ;
2109: FUNCTION check_line_percent RETURN NUMBER IS
2110: CURSOR c_check_line_percent IS
2111: SELECT distinct line_num, nvl(line_percent,0) line_percent
2112: FROM pa_alloc_run_targets
2113: WHERE rule_id = p_rule_id
2114: AND run_id = p_run_id;
2115: v_percent NUMBER;
2116: v_tot_percent number ;
2729: , p_line_num IN NUMBER ) RETURN NUMBER IS
2730: x_count NUMBER:=0;
2731: CURSOR C_get_count IS
2732: SELECT count(task_id)
2733: FROM pa_alloc_run_targets
2734: WHERE run_id = p_run_id
2735: AND line_num = p_line_num;
2736: BEGIN
2737: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2888: AND ar.period_num = NVL(p_prd_num, ar.period_num )
2889: AND a.run_id < p_run_id
2890: AND a.transaction_type = 'T'
2891: AND NOT EXISTS ( SELECT 1
2892: FROM pa_alloc_run_targets b
2893: WHERE b.project_id = a.project_id
2894: AND b.task_id = a.task_id
2895: AND b.exclude_flag = 'N'
2896: AND b.run_id = p_run_id )
3313: , project_id
3314: , task_id
3315: , line_percent
3316: , exclude_flag
3317: FROM pa_alloc_run_targets
3318: WHERE run_id = p_run_id
3319: AND exclude_flag = 'N';
3320: /* added p_run_id parameter for bug 1900331 */
3321: CURSOR C_all_proj_task_count (p_run_id IN NUMBER) IS
3319: AND exclude_flag = 'N';
3320: /* added p_run_id parameter for bug 1900331 */
3321: CURSOR C_all_proj_task_count (p_run_id IN NUMBER) IS
3322: SELECT count(task_id)
3323: FROM pa_alloc_run_targets
3324: WHERE run_id = p_run_id
3325: AND exclude_flag = 'N';
3326: CURSOR C_count_of_trg_lines IS
3327: SELECT COUNT(DISTINCT line_num)
3324: WHERE run_id = p_run_id
3325: AND exclude_flag = 'N';
3326: CURSOR C_count_of_trg_lines IS
3327: SELECT COUNT(DISTINCT line_num)
3328: FROM pa_alloc_run_targets
3329: WHERE run_id = p_run_id
3330: AND exclude_flag = 'N';
3331: v_quarter_num NUMBER;
3332: v_fiscal_year NUMBER;
5419: )
5420: IS
5421: cursor c_projects is
5422: select distinct part.project_id project_id
5423: from pa_alloc_run_targets part,
5424: pa_resource_list_assignments prla
5425: where part.project_id = prla.project_id
5426: and prla.resource_list_id = p_resource_list_id
5427: and prla.resource_list_accumulated_flag = 'Y'
5428: and part.run_id = p_run_id
5429: and Nvl(p_resource_struct_type,'RL') = 'RL'
5430: Union All
5431: select distinct part.project_id project_id
5432: from pa_alloc_run_targets part,
5433: pa_rbs_prj_assignments prpa
5434: where part.project_id = prpa.project_id
5435: and prpa.rbs_header_id = p_resource_list_id
5436: and prpa.rbs_version_id = p_rbs_version_id
5502: ,G_last_update_date
5503: ,G_last_updated_by
5504: ,G_last_update_login
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
5588: ,G_last_update_date
5589: ,G_last_updated_by
5590: ,G_last_update_login
5591: from pa_alloc_run_resource_det parr,
5592: pa_alloc_run_targets part,
5593: PA_ALLOC_TXN_ACCUM_RBS_V pta
5594: where pta.Rbs_Element_Id = parr.resource_list_member_id
5595: and pta.Project_id = part.project_id
5596: and pta.task_id = part.task_id
5687: ,G_last_update_date
5688: ,G_last_updated_by
5689: ,G_last_update_login
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
5754: ,G_last_update_date
5755: ,G_last_updated_by
5756: ,G_last_update_login
5757: from pa_alloc_run_resource_det parr,
5758: pa_alloc_run_targets part,
5759: PA_ALLOC_TXN_ACCUM_RBS_V pta
5760: where pta.Rbs_Element_ID = parr.resource_list_member_id
5761: and pta.Project_id = part.project_id
5762: and pta.task_id = part.task_id
5822: ,G_last_update_date
5823: ,G_last_updated_by
5824: ,G_last_update_login
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
5893: ,G_last_update_date
5894: ,G_last_updated_by
5895: ,G_last_update_login
5896: from pa_alloc_run_resource_det parr,
5897: pa_alloc_run_targets part,
5898: PA_ALLOC_TXN_ACCUM_RBS_V pta
5899: where pta.Rbs_Element_ID = parr.resource_list_member_id
5900: and pta.Project_id = part.project_id
5901: and pta.task_id = part.task_id
5989: ,G_last_update_date
5990: ,G_last_updated_by
5991: ,G_last_update_login
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
6064: ,G_last_update_date
6065: ,G_last_updated_by
6066: ,G_last_update_login
6067: from pa_alloc_run_resource_det parr,
6068: pa_alloc_run_targets part,
6069: PA_ALLOC_TXN_ACCUM_RBS_V pta
6070: where pta.Rbs_Element_Id = parr.resource_list_member_id
6071: and pta.Project_id = part.project_id
6072: and pta.task_id = part.task_id
6995: )
6996: IS
6997: cursor c_projects is
6998: select distinct project_id
6999: from pa_alloc_run_targets part
7000: where part.run_id = p_run_id;
7001: v_commit NUMBER := 0;
7002: BEGIN
7003: FOR c_projects_rec IN c_projects LOOP
7034: ,G_last_update_date
7035: ,G_last_updated_by
7036: ,G_last_update_login
7037: from pa_alloc_run_resource_det parr,
7038: pa_alloc_run_targets part,
7039: /*** pa_base_budget_by_pa_period_v pbpp *** commented bug 2619977 */
7040: pa_base_finplan_by_pa_period_v pfpp /* added bug 2619977 */
7041: where Decode (
7042: Nvl(p_basis_resource_struct_Type,'RL') ,
7100: ,G_last_update_date
7101: ,G_last_updated_by
7102: ,G_last_update_login
7103: from pa_alloc_run_resource_det parr,
7104: pa_alloc_run_targets part,
7105: /*** pa_base_budget_by_gl_period_v pbpg *** commented bug 2619977 */
7106: /* pa_base_finplan_by_pa_period_v pfpg added bug 2619977 commented bug 2757875 */
7107: pa_base_finplan_by_gl_period_v pfpg /* added bug 2757875 */
7108: where Decode (
7374: For each resource list member in the Include_RLM_tbl then
7375: Get source amount for each project and task in pa_alloc_run_sources
7376: tables for given rule_id and run_id
7377: Else
7378: For each project and task in pa_alloc_run_targets
7379: find the basis amount at project and task level.
7380: End if.
7381: */
7382: v_period_type VARCHAR2(15) ;
8019: Get the Period Year and Quarter Num values for the given Relative_period.
8020: If basis_resource_lists is not null then
8021: Populate Include_RLM_tbl.
8022: For each resource list member in the Include_RLM_tbl Loop
8023: For Each project and task in pa_alloc_run_targets table Loop
8024: If Basis_category is 'ACTUALS' then
8025: Get the basis amount using get_alloc_amounts( )
8026: Else
8027: Get the basis amount from get_budget_amounts()
8029: End Loop ;
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
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
8039: pa_base_budget_by_pa_period_v
8040: and insert that into pa_alloc_run_basis_det.
8041: End if.
8076: v_status NUMBER := NULL;
8077: v_err_message VARCHAR2(250);
8078: cursor C_run_targets is
8079: Select line_num, project_id , task_id, line_percent
8080: from pa_alloc_run_targets
8081: where rule_id = p_rule_id
8082: and run_id = p_run_id
8083: and exclude_flag <> 'Y' ;
8084: cursor C_tot_basis_amt is
8102: TASK_ID ,
8103: EXCLUDE_FLAG ,
8104: LINE_PERCENT ,
8105: BUDGET_VERSION_ID
8106: From Pa_Alloc_Run_Targets
8107: Where rule_id = p_rule_id
8108: And run_id = p_run_id
8109: And Nvl(exclude_flag,'N') = 'N' ;
8110: BEGIN
8192: is made and bulk inserts are done in new procedure added insert_alloc_basis_resource
8193: These changes are not done for budgets as in budgets the volume of data should not be that high.
8194: */
8195: IF p_basis_category = 'A' THEN
8196: /* Currenly we insert all project/tasks into pa_alloc_run_targets.
8197: These do not get converted into final txns in case there are no basis existing
8198: for these targets in summarization. We want to intelligently delete those data
8199: from pa_alloc_run_targets table which do not contain any basis
8200: amount. */
8195: IF p_basis_category = 'A' THEN
8196: /* Currenly we insert all project/tasks into pa_alloc_run_targets.
8197: These do not get converted into final txns in case there are no basis existing
8198: for these targets in summarization. We want to intelligently delete those data
8199: from pa_alloc_run_targets table which do not contain any basis
8200: amount. */
8201: clean_up_targets_for_actuals(p_run_id => p_run_id
8202: ,p_rule_id => p_rule_id
8203: ,p_amt_type => p_basis_amt_type
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
8311: and part.run_id = p_run_id
8312: and part.exclude_flag = 'N'
8313: and pta.project_id = part.project_id
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
8377: and part.exclude_flag = 'N'
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'
8432: and pta.project_id = part.project_id
8477: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
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
8534: ))
8535: /*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
8536: from pa_base_finplan_by_pa_period_v pfpp, /* added bug 2619977 */
8537: pa_projects_all p ,
8538: pa_alloc_run_targets part
8539: where part.rule_id = p_rule_id
8540: and part.run_id = p_run_id
8541: and part.exclude_flag = 'N'
8542: and part.project_id = p.project_id
8571: ))
8572: /*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
8573: /**** from pa_base_finplan_by_pa_period_v pfpp, added bug 2619977. **** Commented bug 2757875*/
8574: from pa_base_finplan_by_gl_period_v pfpp, /* Added bug 2757875 */
8575: pa_alloc_run_targets part
8576: where part.rule_id = p_rule_id
8577: and part.run_id = p_run_id
8578: and part.exclude_flag = 'N'
8579: and pfpp.project_id = part.project_id
8607: 'BASE_LABOR_QUANTITY', nvl(pfgp.labor_quantity,0),
8608: 0
8609: ))
8610: from pa_base_finplan_by_gl_period_v pfgp,
8611: pa_alloc_run_targets part
8612: where part.rule_id = p_rule_id
8613: and part.run_id = p_run_id
8614: and part.exclude_flag = 'N'
8615: and pfgp.project_id = part.project_id
8638: 'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
8639: 0
8640: ))
8641: from pa_base_finplan_by_pa_period_v pfpp,
8642: pa_alloc_run_targets part
8643: where part.rule_id = p_rule_id
8644: and part.run_id = p_run_id
8645: and part.exclude_flag = 'N'
8646: and pfpp.project_id = part.project_id
8665: part.line_percent, G_creation_date,
8666: G_created_by, G_last_update_date,
8667: G_last_updated_by, G_last_update_login ,
8668: 0
8669: from pa_alloc_run_targets part
8670: where part.rule_id = p_rule_id
8671: and part.run_id = p_run_id
8672: and part.exclude_flag = 'N'
8673: and NOT EXISTS ( Select 'Exists'
8698: select part.rule_id, part.run_id, part.line_num, part.project_id,
8699: part.task_id, part.line_percent, G_creation_date, G_created_by,
8700: G_last_update_date, G_last_updated_by, G_last_update_login ,
8701: 0 AMOUNT
8702: from pa_alloc_run_targets part
8703: where part.rule_id = p_rule_id
8704: and part.run_id = p_run_id
8705: and part.exclude_flag = 'N'
8706: and not exists
8866: ***END get_budget_amounts ;
8867: ***/
8868: -- ==========================================================================
8869: /* PROCEDURE : clean_up_targets_for_actuals
8870: Purpose : Deletes records from pa_alloc_run_targets which do not contain
8871: any Basis amount. The addition of this procedure does not
8872: modify the existing flow. This procedure has been added to
8873: delete unnecessary records from pa_alloc_run_targets for
8874: performance reasons. So, removing this procedure will not impact
8869: /* PROCEDURE : clean_up_targets_for_actuals
8870: Purpose : Deletes records from pa_alloc_run_targets which do not contain
8871: any Basis amount. The addition of this procedure does not
8872: modify the existing flow. This procedure has been added to
8873: delete unnecessary records from pa_alloc_run_targets for
8874: performance reasons. So, removing this procedure will not impact
8875: the existing flow of Allocations.
8876: Created : 18-feb-02 Praveen for Bug #2222280
8877: */
8898: IF P_DEBUG_MODE = 'Y' THEN
8899: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8900: END IF;
8901: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8902: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8903: IF P_DEBUG_MODE = 'Y' THEN
8904: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8905: END IF;
8906: v_commit_count := 0;
8906: v_commit_count := 0;
8907: IF p_basis_method = 'P' THEN
8908: SAVEPOINT delete_unwanted_targets;
8909: /*For Bug 5403833*/
8910: DELETE FROM pa_alloc_run_targets part
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
8926: into v_do_commit
8927: from dual
8928: where exists
8929: (select null
8930: from pa_alloc_run_targets part
8931: where part.run_id = p_run_id
8932: and part.exclude_flag = 'N'
8933: and rownum = 1);
8934: /* We will commit if any records are still there in targets table.
8953: elsif p_basis_method = 'FP' then
8954: FOR c_target_lines_rec in c_target_lines LOOP
8955: SAVEPOINT delete_unwanted_targets;
8956: /* For Bug 5403833 */
8957: DELETE FROM pa_alloc_run_targets part
8958: WHERE part.run_id = p_run_id
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
8975: into v_do_commit
8976: from dual
8977: where exists
8978: (select null
8979: from pa_alloc_run_targets part
8980: where part.run_id = p_run_id
8981: and part.exclude_flag = 'N'
8982: and line_num = c_target_lines_rec.line_num
8983: and rownum = 1);
8999: END;
9000: end loop;
9001: END IF;
9002: ELSIF p_amt_type = 'PTD' THEN
9003: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
9004: IF P_DEBUG_MODE = 'Y' THEN
9005: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9006: END IF;
9007: v_commit_count := 0;
9010: IF P_DEBUG_MODE = 'Y' THEN
9011: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9012: END IF;
9013: SAVEPOINT delete_unwanted_targets;
9014: DELETE FROM pa_alloc_run_targets part
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
9030: into v_do_commit
9031: from dual
9032: where exists
9033: (select null
9034: from pa_alloc_run_targets part
9035: where part.run_id = p_run_id
9036: and part.exclude_flag = 'N'
9037: and rownum = 1);
9038: /* We will commit if any records are still there in targets table.
9061: SAVEPOINT delete_unwanted_targets;
9062:
9063: /*For Bug 5403833*/
9064:
9065: DELETE FROM pa_alloc_run_targets part
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
9081: into v_do_commit
9082: from dual
9083: where exists
9084: (select null
9085: from pa_alloc_run_targets part
9086: where part.run_id = p_run_id
9087: and part.exclude_flag = 'N'
9088: and rownum = 1);
9089: /* We will commit if any records are still there in targets table.
9112: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9113: END IF;
9114: FOR c_target_lines_rec in c_target_lines LOOP
9115: SAVEPOINT delete_unwanted_targets;
9116: DELETE FROM pa_alloc_run_targets part
9117: WHERE part.run_id = p_run_id
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
9133: into v_do_commit
9134: from dual
9135: where exists
9136: (select null
9137: from pa_alloc_run_targets part
9138: where part.run_id = p_run_id
9139: and part.exclude_flag = 'N'
9140: and line_num = c_target_lines_rec.line_num
9141: and rownum = 1);
9162: END IF;
9163: FOR c_target_lines_rec in c_target_lines LOOP
9164: SAVEPOINT delete_unwanted_targets;
9165: /*For Bug 5403833 */
9166: DELETE FROM pa_alloc_run_targets part
9167: WHERE part.run_id = p_run_id
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
9183: into v_do_commit
9184: from dual
9185: where exists
9186: (select null
9187: from pa_alloc_run_targets part
9188: where part.run_id = p_run_id
9189: and part.exclude_flag = 'N'
9190: and line_num = c_target_lines_rec.line_num
9191: and rownum = 1);
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;
9219: v_commit_count := 0;
9218: END IF;
9219: v_commit_count := 0;
9220: IF p_basis_method = 'P' THEN
9221: SAVEPOINT delete_unwanted_targets;
9222: DELETE FROM pa_alloc_run_targets part
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
9237: into v_do_commit
9238: from dual
9239: where exists
9240: (select null
9241: from pa_alloc_run_targets part
9242: where part.run_id = p_run_id
9243: and part.exclude_flag = 'N'
9244: and rownum = 1);
9245: /* We will commit if any records are still there in targets table.
9263: END;
9264: elsif p_basis_method = 'FP' then
9265: FOR c_target_lines_rec in c_target_lines LOOP
9266: SAVEPOINT delete_unwanted_targets;
9267: DELETE FROM pa_alloc_run_targets part
9268: WHERE part.run_id = p_run_id
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
9280: into v_do_commit
9281: from dual
9282: where exists
9283: (select null
9284: from pa_alloc_run_targets part
9285: where part.run_id = p_run_id
9286: and part.exclude_flag = 'N'
9287: and line_num = c_target_lines_rec.line_num
9288: and rownum = 1);
9948: /*Code Changes for Bug No.2984871 start */
9949: Commit;
9950: /*Code Changes for Bug No.2984871 end */
9951: End Loop;
9952: -- Delete Transactions from pa_alloc_run_targets
9953: While 1=1 Loop /* Bug 2176096 */
9954: Delete from pa_alloc_run_targets
9955: where run_id = p_run_id and rownum < 10001;
9956: /* Commented for Bug 2984871 Commit; */
9950: /*Code Changes for Bug No.2984871 end */
9951: End Loop;
9952: -- Delete Transactions from pa_alloc_run_targets
9953: While 1=1 Loop /* Bug 2176096 */
9954: Delete from pa_alloc_run_targets
9955: where run_id = p_run_id and rownum < 10001;
9956: /* Commented for Bug 2984871 Commit; */
9957: If Sql%Rowcount = 0 THen
9958: Exit;
10098: /*Code Changes for Bug No.2984871 start */
10099: Commit;
10100: /*Code Changes for Bug No.2984871 end */
10101: End Loop;
10102: -- Delete Transactions from pa_alloc_run_targets
10103: While 1=1 Loop /* Bug 2176096 */
10104: Delete from pa_alloc_run_targets
10105: where run_id = l_run_id and rownum < 10001;
10106: /* Commented for Bug 2984871 Commit; */
10100: /*Code Changes for Bug No.2984871 end */
10101: End Loop;
10102: -- Delete Transactions from pa_alloc_run_targets
10103: While 1=1 Loop /* Bug 2176096 */
10104: Delete from pa_alloc_run_targets
10105: where run_id = l_run_id and rownum < 10001;
10106: /* Commented for Bug 2984871 Commit; */
10107: If Sql%Rowcount = 0 THen
10108: Exit;