[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;
1121: AND project_id = p_project_id
1122: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1123: CURSOR C_excl_trg_proj_task IS
1124: SELECT project_id, task_id
1125: FROM pa_alloc_run_targets
1126: WHERE run_id = p_run_id
1127: AND exclude_flag = 'Y'
1128: AND project_id = p_project_id
1129: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1810: , p_bas_method IN VARCHAR2
1811: , p_dup_targets_flag IN VARCHAR2 ) IS
1812: CURSOR target_exists IS
1813: SELECT 1
1814: FROM pa_alloc_run_targets
1815: WHERE run_id = p_run_id
1816: AND project_id = p_project_id
1817: AND task_id = p_task_id;
1818: v_dummy NUMBER;
1837: END IF;
1838: IF (allow_insert_flag = 'Y') THEN
1839: /* added if condition for bug 2619977 */
1840: /* Invoking FP API to get budget_version_id. Will populate the ID in
1841: pa_alloc_run_targets based on budget type or FP type selected. After
1842: this processing become same for both basis - budgets and FPs */
1843: PA_FIN_PLAN_UTILS.GET_COST_BASE_VERSION_INFO
1844: ( p_project_id
1845: ,G_basis_fin_plan_Type_id
1857: '] Return status ['||x_return_status||']' );
1858: END IF;
1859: ---- Here needs a validation of
1860: /******** Need to handle the if error is generated in API *****/
1861: INSERT INTO PA_ALLOC_RUN_TARGETS (
1862: RUN_ID
1863: , RULE_ID
1864: , LINE_NUM
1865: , PROJECT_ID
2056: AND pt.chargeable_flag = 'Y'
2057: AND x_ei_date between nvl(pt.start_date,x_ei_date) and nvl(pt.completion_date,x_ei_date) ;
2058: CURSOR c_alloc_run_trg_projects IS
2059: SELECT project_id, task_id
2060: FROM pa_alloc_run_targets
2061: WHERE run_id = p_run_id;
2062: CURSOR c_rule_entry_level_code IS
2063: SELECT entry_level_code
2064: FROM pa_budget_entry_methods
2098: v_cx_err_flag VARCHAR2(1) ;
2099: FUNCTION check_line_percent RETURN NUMBER IS
2100: CURSOR c_check_line_percent IS
2101: SELECT distinct line_num, nvl(line_percent,0) line_percent
2102: FROM pa_alloc_run_targets
2103: WHERE rule_id = p_rule_id
2104: AND run_id = p_run_id;
2105: v_percent NUMBER;
2106: v_tot_percent number ;
2719: , p_line_num IN NUMBER ) RETURN NUMBER IS
2720: x_count NUMBER:=0;
2721: CURSOR C_get_count IS
2722: SELECT count(task_id)
2723: FROM pa_alloc_run_targets
2724: WHERE run_id = p_run_id
2725: AND line_num = p_line_num;
2726: BEGIN
2727: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2876: AND ar.period_num = NVL(p_prd_num, ar.period_num )
2877: AND a.run_id < p_run_id
2878: AND a.transaction_type = 'T'
2879: AND NOT EXISTS ( SELECT 1
2880: FROM pa_alloc_run_targets b
2881: WHERE b.project_id = a.project_id
2882: AND b.task_id = a.task_id
2883: AND b.exclude_flag = 'N'
2884: AND b.run_id = p_run_id )
3301: , project_id
3302: , task_id
3303: , line_percent
3304: , exclude_flag
3305: FROM pa_alloc_run_targets
3306: WHERE run_id = p_run_id
3307: AND exclude_flag = 'N';
3308: /* added p_run_id parameter for bug 1900331 */
3309: CURSOR C_all_proj_task_count (p_run_id IN NUMBER) IS
3307: AND exclude_flag = 'N';
3308: /* added p_run_id parameter for bug 1900331 */
3309: CURSOR C_all_proj_task_count (p_run_id IN NUMBER) IS
3310: SELECT count(task_id)
3311: FROM pa_alloc_run_targets
3312: WHERE run_id = p_run_id
3313: AND exclude_flag = 'N';
3314: CURSOR C_count_of_trg_lines IS
3315: SELECT COUNT(DISTINCT line_num)
3312: WHERE run_id = p_run_id
3313: AND exclude_flag = 'N';
3314: CURSOR C_count_of_trg_lines IS
3315: SELECT COUNT(DISTINCT line_num)
3316: FROM pa_alloc_run_targets
3317: WHERE run_id = p_run_id
3318: AND exclude_flag = 'N';
3319: v_quarter_num NUMBER;
3320: v_fiscal_year NUMBER;
5396: )
5397: IS
5398: cursor c_projects is
5399: select distinct part.project_id project_id
5400: from pa_alloc_run_targets part,
5401: pa_resource_list_assignments prla
5402: where part.project_id = prla.project_id
5403: and prla.resource_list_id = p_resource_list_id
5404: and prla.resource_list_accumulated_flag = 'Y'
5405: and part.run_id = p_run_id
5406: and Nvl(p_resource_struct_type,'RL') = 'RL'
5407: Union All
5408: select distinct part.project_id project_id
5409: from pa_alloc_run_targets part,
5410: pa_rbs_prj_assignments prpa
5411: where part.project_id = prpa.project_id
5412: and prpa.rbs_header_id = p_resource_list_id
5413: and prpa.rbs_version_id = p_rbs_version_id
5479: ,G_last_update_date
5480: ,G_last_updated_by
5481: ,G_last_update_login
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
5565: ,G_last_update_date
5566: ,G_last_updated_by
5567: ,G_last_update_login
5568: from pa_alloc_run_resource_det parr,
5569: pa_alloc_run_targets part,
5570: PA_ALLOC_TXN_ACCUM_RBS_V pta
5571: where pta.Rbs_Element_Id = parr.resource_list_member_id
5572: and pta.Project_id = part.project_id
5573: and pta.task_id = part.task_id
5664: ,G_last_update_date
5665: ,G_last_updated_by
5666: ,G_last_update_login
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
5731: ,G_last_update_date
5732: ,G_last_updated_by
5733: ,G_last_update_login
5734: from pa_alloc_run_resource_det parr,
5735: pa_alloc_run_targets part,
5736: PA_ALLOC_TXN_ACCUM_RBS_V pta
5737: where pta.Rbs_Element_ID = parr.resource_list_member_id
5738: and pta.Project_id = part.project_id
5739: and pta.task_id = part.task_id
5799: ,G_last_update_date
5800: ,G_last_updated_by
5801: ,G_last_update_login
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
5870: ,G_last_update_date
5871: ,G_last_updated_by
5872: ,G_last_update_login
5873: from pa_alloc_run_resource_det parr,
5874: pa_alloc_run_targets part,
5875: PA_ALLOC_TXN_ACCUM_RBS_V pta
5876: where pta.Rbs_Element_ID = parr.resource_list_member_id
5877: and pta.Project_id = part.project_id
5878: and pta.task_id = part.task_id
5966: ,G_last_update_date
5967: ,G_last_updated_by
5968: ,G_last_update_login
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
6041: ,G_last_update_date
6042: ,G_last_updated_by
6043: ,G_last_update_login
6044: from pa_alloc_run_resource_det parr,
6045: pa_alloc_run_targets part,
6046: PA_ALLOC_TXN_ACCUM_RBS_V pta
6047: where pta.Rbs_Element_Id = parr.resource_list_member_id
6048: and pta.Project_id = part.project_id
6049: and pta.task_id = part.task_id
6972: )
6973: IS
6974: cursor c_projects is
6975: select distinct project_id
6976: from pa_alloc_run_targets part
6977: where part.run_id = p_run_id;
6978: v_commit NUMBER := 0;
6979: BEGIN
6980: FOR c_projects_rec IN c_projects LOOP
7011: ,G_last_update_date
7012: ,G_last_updated_by
7013: ,G_last_update_login
7014: from pa_alloc_run_resource_det parr,
7015: pa_alloc_run_targets part,
7016: /*** pa_base_budget_by_pa_period_v pbpp *** commented bug 2619977 */
7017: pa_base_finplan_by_pa_period_v pfpp /* added bug 2619977 */
7018: where Decode (
7019: Nvl(p_basis_resource_struct_Type,'RL') ,
7077: ,G_last_update_date
7078: ,G_last_updated_by
7079: ,G_last_update_login
7080: from pa_alloc_run_resource_det parr,
7081: pa_alloc_run_targets part,
7082: /*** pa_base_budget_by_gl_period_v pbpg *** commented bug 2619977 */
7083: /* pa_base_finplan_by_pa_period_v pfpg added bug 2619977 commented bug 2757875 */
7084: pa_base_finplan_by_gl_period_v pfpg /* added bug 2757875 */
7085: where Decode (
7351: For each resource list member in the Include_RLM_tbl then
7352: Get source amount for each project and task in pa_alloc_run_sources
7353: tables for given rule_id and run_id
7354: Else
7355: For each project and task in pa_alloc_run_targets
7356: find the basis amount at project and task level.
7357: End if.
7358: */
7359: v_period_type VARCHAR2(15) ;
7914: Get the Period Year and Quarter Num values for the given Relative_period.
7915: If basis_resource_lists is not null then
7916: Populate Include_RLM_tbl.
7917: For each resource list member in the Include_RLM_tbl Loop
7918: For Each project and task in pa_alloc_run_targets table Loop
7919: If Basis_category is 'ACTUALS' then
7920: Get the basis amount using get_alloc_amounts( )
7921: Else
7922: Get the basis amount from get_budget_amounts()
7924: End Loop ;
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
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
7934: pa_base_budget_by_pa_period_v
7935: and insert that into pa_alloc_run_basis_det.
7936: End if.
7971: v_status NUMBER := NULL;
7972: v_err_message VARCHAR2(250);
7973: cursor C_run_targets is
7974: Select line_num, project_id , task_id, line_percent
7975: from pa_alloc_run_targets
7976: where rule_id = p_rule_id
7977: and run_id = p_run_id
7978: and exclude_flag <> 'Y' ;
7979: cursor C_tot_basis_amt is
7997: TASK_ID ,
7998: EXCLUDE_FLAG ,
7999: LINE_PERCENT ,
8000: BUDGET_VERSION_ID
8001: From Pa_Alloc_Run_Targets
8002: Where rule_id = p_rule_id
8003: And run_id = p_run_id
8004: And Nvl(exclude_flag,'N') = 'N' ;
8005: BEGIN
8087: is made and bulk inserts are done in new procedure added insert_alloc_basis_resource
8088: These changes are not done for budgets as in budgets the volume of data should not be that high.
8089: */
8090: IF p_basis_category = 'A' THEN
8091: /* Currenly we insert all project/tasks into pa_alloc_run_targets.
8092: These do not get converted into final txns in case there are no basis existing
8093: for these targets in summarization. We want to intelligently delete those data
8094: from pa_alloc_run_targets table which do not contain any basis
8095: amount. */
8090: IF p_basis_category = 'A' THEN
8091: /* Currenly we insert all project/tasks into pa_alloc_run_targets.
8092: These do not get converted into final txns in case there are no basis existing
8093: for these targets in summarization. We want to intelligently delete those data
8094: from pa_alloc_run_targets table which do not contain any basis
8095: amount. */
8096: clean_up_targets_for_actuals(p_run_id => p_run_id
8097: ,p_rule_id => p_rule_id
8098: ,p_amt_type => p_basis_amt_type
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
8206: and part.run_id = p_run_id
8207: and part.exclude_flag = 'N'
8208: and pta.project_id = part.project_id
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
8272: and part.exclude_flag = 'N'
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'
8327: and pta.project_id = part.project_id
8372: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
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
8429: ))
8430: /*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
8431: from pa_base_finplan_by_pa_period_v pfpp, /* added bug 2619977 */
8432: pa_projects_all p ,
8433: pa_alloc_run_targets part
8434: where part.rule_id = p_rule_id
8435: and part.run_id = p_run_id
8436: and part.exclude_flag = 'N'
8437: and part.project_id = p.project_id
8466: ))
8467: /*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
8468: /**** from pa_base_finplan_by_pa_period_v pfpp, added bug 2619977. **** Commented bug 2757875*/
8469: from pa_base_finplan_by_gl_period_v pfpp, /* Added bug 2757875 */
8470: pa_alloc_run_targets part
8471: where part.rule_id = p_rule_id
8472: and part.run_id = p_run_id
8473: and part.exclude_flag = 'N'
8474: and pfpp.project_id = part.project_id
8502: 'BASE_LABOR_QUANTITY', nvl(pfgp.labor_quantity,0),
8503: 0
8504: ))
8505: from pa_base_finplan_by_gl_period_v pfgp,
8506: pa_alloc_run_targets part
8507: where part.rule_id = p_rule_id
8508: and part.run_id = p_run_id
8509: and part.exclude_flag = 'N'
8510: and pfgp.project_id = part.project_id
8533: 'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
8534: 0
8535: ))
8536: from pa_base_finplan_by_pa_period_v pfpp,
8537: pa_alloc_run_targets part
8538: where part.rule_id = p_rule_id
8539: and part.run_id = p_run_id
8540: and part.exclude_flag = 'N'
8541: and pfpp.project_id = part.project_id
8560: part.line_percent, G_creation_date,
8561: G_created_by, G_last_update_date,
8562: G_last_updated_by, G_last_update_login ,
8563: 0
8564: from pa_alloc_run_targets part
8565: where part.rule_id = p_rule_id
8566: and part.run_id = p_run_id
8567: and part.exclude_flag = 'N'
8568: and NOT EXISTS ( Select 'Exists'
8593: select part.rule_id, part.run_id, part.line_num, part.project_id,
8594: part.task_id, part.line_percent, G_creation_date, G_created_by,
8595: G_last_update_date, G_last_updated_by, G_last_update_login ,
8596: 0 AMOUNT
8597: from pa_alloc_run_targets part
8598: where part.rule_id = p_rule_id
8599: and part.run_id = p_run_id
8600: and part.exclude_flag = 'N'
8601: and not exists
8761: ***END get_budget_amounts ;
8762: ***/
8763: -- ==========================================================================
8764: /* PROCEDURE : clean_up_targets_for_actuals
8765: Purpose : Deletes records from pa_alloc_run_targets which do not contain
8766: any Basis amount. The addition of this procedure does not
8767: modify the existing flow. This procedure has been added to
8768: delete unnecessary records from pa_alloc_run_targets for
8769: performance reasons. So, removing this procedure will not impact
8764: /* PROCEDURE : clean_up_targets_for_actuals
8765: Purpose : Deletes records from pa_alloc_run_targets which do not contain
8766: any Basis amount. The addition of this procedure does not
8767: modify the existing flow. This procedure has been added to
8768: delete unnecessary records from pa_alloc_run_targets for
8769: performance reasons. So, removing this procedure will not impact
8770: the existing flow of Allocations.
8771: Created : 18-feb-02 Praveen for Bug #2222280
8772: */
8793: IF P_DEBUG_MODE = 'Y' THEN
8794: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8795: END IF;
8796: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8797: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8798: IF P_DEBUG_MODE = 'Y' THEN
8799: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8800: END IF;
8801: v_commit_count := 0;
8801: v_commit_count := 0;
8802: IF p_basis_method = 'P' THEN
8803: SAVEPOINT delete_unwanted_targets;
8804: /*For Bug 5403833*/
8805: DELETE FROM pa_alloc_run_targets part
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
8821: into v_do_commit
8822: from dual
8823: where exists
8824: (select null
8825: from pa_alloc_run_targets part
8826: where part.run_id = p_run_id
8827: and part.exclude_flag = 'N'
8828: and rownum = 1);
8829: /* We will commit if any records are still there in targets table.
8848: elsif p_basis_method = 'FP' then
8849: FOR c_target_lines_rec in c_target_lines LOOP
8850: SAVEPOINT delete_unwanted_targets;
8851: /* For Bug 5403833 */
8852: DELETE FROM pa_alloc_run_targets part
8853: WHERE part.run_id = p_run_id
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
8870: into v_do_commit
8871: from dual
8872: where exists
8873: (select null
8874: from pa_alloc_run_targets part
8875: where part.run_id = p_run_id
8876: and part.exclude_flag = 'N'
8877: and line_num = c_target_lines_rec.line_num
8878: and rownum = 1);
8894: END;
8895: end loop;
8896: END IF;
8897: ELSIF p_amt_type = 'PTD' THEN
8898: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
8899: IF P_DEBUG_MODE = 'Y' THEN
8900: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8901: END IF;
8902: v_commit_count := 0;
8905: IF P_DEBUG_MODE = 'Y' THEN
8906: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
8907: END IF;
8908: SAVEPOINT delete_unwanted_targets;
8909: DELETE FROM pa_alloc_run_targets part
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
8925: into v_do_commit
8926: from dual
8927: where exists
8928: (select null
8929: from pa_alloc_run_targets part
8930: where part.run_id = p_run_id
8931: and part.exclude_flag = 'N'
8932: and rownum = 1);
8933: /* We will commit if any records are still there in targets table.
8956: SAVEPOINT delete_unwanted_targets;
8957:
8958: /*For Bug 5403833*/
8959:
8960: DELETE FROM pa_alloc_run_targets part
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
8976: into v_do_commit
8977: from dual
8978: where exists
8979: (select null
8980: from pa_alloc_run_targets part
8981: where part.run_id = p_run_id
8982: and part.exclude_flag = 'N'
8983: and rownum = 1);
8984: /* We will commit if any records are still there in targets table.
9007: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9008: END IF;
9009: FOR c_target_lines_rec in c_target_lines LOOP
9010: SAVEPOINT delete_unwanted_targets;
9011: DELETE FROM pa_alloc_run_targets part
9012: WHERE part.run_id = p_run_id
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
9028: into v_do_commit
9029: from dual
9030: where exists
9031: (select null
9032: from pa_alloc_run_targets part
9033: where part.run_id = p_run_id
9034: and part.exclude_flag = 'N'
9035: and line_num = c_target_lines_rec.line_num
9036: and rownum = 1);
9057: END IF;
9058: FOR c_target_lines_rec in c_target_lines LOOP
9059: SAVEPOINT delete_unwanted_targets;
9060: /*For Bug 5403833 */
9061: DELETE FROM pa_alloc_run_targets part
9062: WHERE part.run_id = p_run_id
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
9078: into v_do_commit
9079: from dual
9080: where exists
9081: (select null
9082: from pa_alloc_run_targets part
9083: where part.run_id = p_run_id
9084: and part.exclude_flag = 'N'
9085: and line_num = c_target_lines_rec.line_num
9086: and rownum = 1);
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;
9114: v_commit_count := 0;
9113: END IF;
9114: v_commit_count := 0;
9115: IF p_basis_method = 'P' THEN
9116: SAVEPOINT delete_unwanted_targets;
9117: DELETE FROM pa_alloc_run_targets part
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
9132: into v_do_commit
9133: from dual
9134: where exists
9135: (select null
9136: from pa_alloc_run_targets part
9137: where part.run_id = p_run_id
9138: and part.exclude_flag = 'N'
9139: and rownum = 1);
9140: /* We will commit if any records are still there in targets table.
9158: END;
9159: elsif p_basis_method = 'FP' then
9160: FOR c_target_lines_rec in c_target_lines LOOP
9161: SAVEPOINT delete_unwanted_targets;
9162: DELETE FROM pa_alloc_run_targets part
9163: WHERE part.run_id = p_run_id
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
9175: into v_do_commit
9176: from dual
9177: where exists
9178: (select null
9179: from pa_alloc_run_targets part
9180: where part.run_id = p_run_id
9181: and part.exclude_flag = 'N'
9182: and line_num = c_target_lines_rec.line_num
9183: and rownum = 1);
9843: /*Code Changes for Bug No.2984871 start */
9844: Commit;
9845: /*Code Changes for Bug No.2984871 end */
9846: End Loop;
9847: -- Delete Transactions from pa_alloc_run_targets
9848: While 1=1 Loop /* Bug 2176096 */
9849: Delete from pa_alloc_run_targets
9850: where run_id = p_run_id and rownum < 10001;
9851: /* Commented for Bug 2984871 Commit; */
9845: /*Code Changes for Bug No.2984871 end */
9846: End Loop;
9847: -- Delete Transactions from pa_alloc_run_targets
9848: While 1=1 Loop /* Bug 2176096 */
9849: Delete from pa_alloc_run_targets
9850: where run_id = p_run_id and rownum < 10001;
9851: /* Commented for Bug 2984871 Commit; */
9852: If Sql%Rowcount = 0 THen
9853: Exit;
9993: /*Code Changes for Bug No.2984871 start */
9994: Commit;
9995: /*Code Changes for Bug No.2984871 end */
9996: End Loop;
9997: -- Delete Transactions from pa_alloc_run_targets
9998: While 1=1 Loop /* Bug 2176096 */
9999: Delete from pa_alloc_run_targets
10000: where run_id = l_run_id and rownum < 10001;
10001: /* Commented for Bug 2984871 Commit; */
9995: /*Code Changes for Bug No.2984871 end */
9996: End Loop;
9997: -- Delete Transactions from pa_alloc_run_targets
9998: While 1=1 Loop /* Bug 2176096 */
9999: Delete from pa_alloc_run_targets
10000: where run_id = l_run_id and rownum < 10001;
10001: /* Commented for Bug 2984871 Commit; */
10002: If Sql%Rowcount = 0 THen
10003: Exit;