[Home] [Help]
1: PACKAGE BODY PA_ALLOC_RUN AS
2: /* $Header: PAXALRNB.pls 120.7 2008/03/18 14:59:01 sgottimu ship $ */
3: P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4: G_creation_date DATE ;
5: G_last_update_date DATE ;
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;
156: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
157: IF P_DEBUG_MODE = 'Y' THEN
158: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
159: END IF;
160: SELECT pa_alloc_runs_s.nextval
161: INTO v_run_id
162: FROM dual;
163: G_alloc_run_id := v_run_id;
164: x_run_id := v_run_id;
502: pa_debug.G_err_stage := 'RELEASING THE RUN';
503: IF P_DEBUG_MODE = 'Y' THEN
504: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
505: END IF;
506: pa_alloc_run.release_alloc_txns( p_rule_id, x_run_id ,x_retcode,
507: x_errbuf );
508: END IF; /* v_mode = release */
509: if x_errbuf is NOT NULL then
510: alloc_errors( p_rule_id, x_run_id, 'R', 'E',
523: alloc_errors( p_rule_id, x_run_id,'R','W','PA_AL_ALL_TASKS_RECD_ZERO_AMT', FALSE) ;
524: END IF ;
525:
526: if x_errbuf is null then /* added if clause for 6243121 */
527: UPDATE pa_alloc_runs
528: SET run_status = DECODE( v_mode, 'RELEASE', 'RS', 'DS'),
529: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
530: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
531: WHERE run_id = x_run_id;
537: END IF;
538:
539: /* added for 6243121 */
540: else -- if any error/exception is encountered while releasing
541: UPDATE pa_alloc_runs
542: SET run_status = DECODE( v_mode, 'RELEASE', 'RF', 'DF'),
543: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
544: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
545: WHERE run_id = x_run_id;
572: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
573: IF P_DEBUG_MODE = 'Y' THEN
574: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
575: END IF;
576: UPDATE pa_alloc_runs
577: SET run_status = DECODE( v_mode, 'RELEASE', 'RF',
578: 'DRAFT', 'DF',
579: run_status ),
580: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
596: , x_run_id IN OUT NOCOPY NUMBER
597: , x_mode OUT NOCOPY VARCHAR2 ) IS
598: CURSOR C_run_id IS
599: SELECT max(run_id)
600: FROM pa_alloc_runs
601: WHERE rule_id = p_rule_id
602: AND run_id <> x_run_id;
603: CURSOR C_prev_run( p_prev_run_id IN NUMBER) IS
604: SELECT run_status
601: WHERE rule_id = p_rule_id
602: AND run_id <> x_run_id;
603: CURSOR C_prev_run( p_prev_run_id IN NUMBER) IS
604: SELECT run_status
605: FROM pa_alloc_runs
606: WHERE run_id = p_prev_run_id ;
607: v_run_status VARCHAR2(5) := NULL;
608: v_prev_run_id NUMBER;
609: BEGIN
1051: , p_last_updated_by IN NUMBER
1052: , p_last_update_login IN NUMBER) IS
1053: CURSOR source_exists IS
1054: SELECT 1
1055: FROM pa_alloc_run_sources
1056: WHERE run_id = p_run_id
1057: AND project_id = p_project_id
1058: AND task_id = p_task_id;
1059: v_dummy NUMBER;
1073: p_project_id, p_task_id );
1074: END IF;
1075: CLOSE source_exists;
1076: IF (allow_insert_flag = 'Y') THEN
1077: INSERT INTO PA_ALLOC_RUN_SOURCES (
1078: RUN_ID
1079: , RULE_ID
1080: , LINE_NUM
1081: , PROJECT_ID
1114: , p_project_id IN NUMBER
1115: , p_task_id IN NUMBER ) RETURN NUMBER IS
1116: CURSOR C_excl_src_proj_task IS
1117: SELECT project_id, task_id
1118: FROM pa_alloc_run_sources
1119: WHERE run_id = p_run_id
1120: AND exclude_flag = 'Y'
1121: AND project_id = p_project_id
1122: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
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);
1293: WHERE project_id = p_proj_id
1294: AND pa_task_utils.check_child_exists(task_id) = 0;
1295: CURSOR c_alloc_run_src_projects IS
1296: SELECT project_id, task_id
1297: FROM pa_alloc_run_sources
1298: WHERE rule_id = p_rule_id
1299: AND run_id = p_run_id;
1300: RUN_SRC_PROJ_REC c_alloc_run_src_projects%ROWTYPE;
1301: CURSOR C_top_task (p_tsk_id IN NUMBER)IS
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 ;
2549: , p_amount IN NUMBER
2550: , p_eligible_amount IN NUMBER ) IS
2551: BEGIN
2552: pa_debug.set_err_stack('insert_alloc_run_GL_det');
2553: INSERT INTO pa_alloc_run_gl_det(
2554: RUN_ID
2555: , RULE_ID
2556: , LINE_NUM
2557: , SOURCE_CCID
2607: , gl_sets_of_books b
2608: WHERE a.set_of_books_id = b.set_of_books_id;
2609: CURSOR gl_eligible_amount IS
2610: SELECT nvl(sum( nvl(eligible_amount,0)*DECODE(subtract_flag,'Y',-1,1) ),0)
2611: FROM pa_alloc_run_GL_det
2612: WHERE run_id = p_run_id;
2613: CURSOR get_gl_amount( p_sob_id IN NUMBER
2614: , p_source_ccid IN NUMBER
2615: , p_currency_code IN VARCHAR2
2628: AND actual_flag = 'A'
2629: AND translated_flag IS NULL;
2630: CURSOR get_pool_percent IS
2631: SELECT nvl(pool_percent,100) pool_percent
2632: FROM pa_alloc_runs
2633: WHERE run_id = p_run_id;
2634: v_sob_id NUMBER;
2635: v_currency_code VARCHAR2(10);
2636: v_period_type VARCHAR2(10);
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');
2796: SELECT a.run_id run_id
2797: , a.project_id project_id
2798: , a.task_id task_id
2799: , nvl(a.eligible_amount,0) eligible_amount
2800: FROM pa_alloc_run_source_det a,
2801: pa_alloc_runs ar
2802: WHERE a.run_id = ar.run_id
2803: AND a.rule_id = p_rule_id
2804: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2797: , a.project_id project_id
2798: , a.task_id task_id
2799: , nvl(a.eligible_amount,0) eligible_amount
2800: FROM pa_alloc_run_source_det a,
2801: pa_alloc_runs ar
2802: WHERE a.run_id = ar.run_id
2803: AND a.rule_id = p_rule_id
2804: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2805: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
2805: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
2806: AND ar.period_num = NVL(p_prd_num, ar.period_num )
2807: AND a.run_id < p_run_id
2808: AND NOT EXISTS ( SELECT 1
2809: FROM pa_alloc_run_source_det b
2810: WHERE a.project_id = b.project_id
2811: AND a.task_id = b.task_id
2812: AND b.rule_id = p_rule_id
2813: AND b.run_id = p_run_id )
2811: AND a.task_id = b.task_id
2812: AND b.rule_id = p_rule_id
2813: AND b.run_id = p_run_id )
2814: AND a.run_id =( SELECT max(c.run_id)
2815: FROM pa_alloc_run_source_det c,
2816: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2817: WHERE c.project_id = a.project_id
2818: AND c.task_id = a.task_id
2819: AND c.run_id = c_ar.run_id
2812: AND b.rule_id = p_rule_id
2813: AND b.run_id = p_run_id )
2814: AND a.run_id =( SELECT max(c.run_id)
2815: FROM pa_alloc_run_source_det c,
2816: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2817: WHERE c.project_id = a.project_id
2818: AND c.task_id = a.task_id
2819: AND c.run_id = c_ar.run_id
2820: AND c_ar.rule_id = p_rule_id
2831: SELECT A.Run_Id Run_Id,
2832: A.Project_Id Project_Id,
2833: A.Task_Id Task_Id,
2834: NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
2835: FROM PA_ALLOC_RUN_SOURCE_DET A ,
2836: ( --
2837: -- The purpose of this in-line view is to return
2838: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2839: -- less than the input p_Run_Id
2834: NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
2835: FROM PA_ALLOC_RUN_SOURCE_DET A ,
2836: ( --
2837: -- The purpose of this in-line view is to return
2838: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2839: -- less than the input p_Run_Id
2840: --
2841: SELECT MAX ( AR.Run_Id ) AS Run_Id
2842: FROM PA_ALLOC_RUNS AR
2838: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2839: -- less than the input p_Run_Id
2840: --
2841: SELECT MAX ( AR.Run_Id ) AS Run_Id
2842: FROM PA_ALLOC_RUNS AR
2843: WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
2844: AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
2845: AND AR.Period_Num = NVL ( p_Prd_Num , AR.Period_Num )
2846: AND AR.Run_Id < p_Run_Id
2850: WHERE A.Run_Id = AR.Run_Id
2851: AND A.Rule_Id = p_Rule_Id
2852: AND NOT EXISTS
2853: ( SELECT 1
2854: FROM PA_ALLOC_RUN_SOURCE_DET B
2855: WHERE A.Project_Id = B.Project_Id
2856: AND A.Task_Id = B.Task_Id
2857: AND B.Rule_Id = p_Rule_Id
2858: AND B.Run_Id = p_Run_Id
2867: , a.project_id project_id
2868: , a.task_id task_id
2869: , nvl(a.Total_allocation,0) Total_allocation
2870: FROM pa_alloc_txn_details a,
2871: pa_alloc_runs ar
2872: WHERE a.run_id = ar.run_id
2873: AND a.rule_id = p_rule_id
2874: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2875: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
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 )
2883: AND b.exclude_flag = 'N'
2884: AND b.run_id = p_run_id )
2885: AND a.run_id =( SELECT max(c.run_id)
2886: FROM pa_alloc_txn_details c,
2887: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2888: WHERE c.project_id = a.project_id
2889: AND c.task_id = a.task_id
2890: AND c.transaction_type = 'T'
2891: AND c.run_id = c_ar.run_id
2995: , p_fscl_year IN NUMBER
2996: , p_prd_num IN NUMBER ) IS
2997: SELECT nvl(sum(nvl(a.current_allocation,0)),0)
2998: FROM pa_alloc_txn_details a,
2999: pa_alloc_runs b
3000: WHERE b.rule_id = p_rule_id
3001: AND b.run_id < p_run_id
3002: AND b.quarter = nvl(p_qrtr_num, b.quarter)
3003: AND b.fiscal_year = nvl(p_fscl_year, b.fiscal_year)
3290: , p_line_num IN NUMBER
3291: , p_project_id IN NUMBER
3292: , p_task_id IN NUMBER ) IS
3293: SELECT nvl(sum( nvl(basis_percent, 0)*nvl(line_percent,100)/10000),0) basis
3294: FROM pa_alloc_run_basis_det
3295: WHERE run_id = p_run_id
3296: AND line_num = p_line_num
3297: AND project_id = p_project_id
3298: AND task_id = p_task_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;
3320: v_fiscal_year NUMBER;
3321: v_period_num NUMBER;
3322: cursor c_sum_of_allocated_amts is
3323: select NVL(sum ( nvl(par1.allocated_amount,0)),0)
3324: from pa_alloc_runs par1
3325: where par1.run_id < p_run_id
3326: and par1.rule_id = p_rule_id
3327: and par1.fiscal_year = nvl(v_fiscal_year, par1.fiscal_year)
3328: and par1.quarter = nvl(v_quarter_num, par1.quarter)
3579: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3580: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3581: END IF;
3582: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3583: UPDATE pa_alloc_runs
3584: SET total_pool_amount = nvl(p_pool_amount,0)
3585: , allocated_amount = nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0)
3586: , Missing_source_proj_amt = v_src_sunk_cost
3587: , Missing_target_proj_amt = v_tgt_sunk_cost
3612: , p_pool_amount IN NUMBER
3613: , p_allocated_amount IN NUMBER ) IS
3614: CURSOR C_offset_det IS
3615: SELECT line_num, project_id, task_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3616: FROM pa_alloc_run_source_det
3617: WHERE run_id=p_run_id
3618: GROUP BY line_num, project_id, task_id;
3619: CURSOR c_offset_proj_sum IS
3620: SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3617: WHERE run_id=p_run_id
3618: GROUP BY line_num, project_id, task_id;
3619: CURSOR c_offset_proj_sum IS
3620: SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3621: FROM pa_alloc_run_source_det
3622: WHERE run_id = p_run_id
3623: GROUP BY project_id ;
3624: CURSOR c_pool_amount IS
3625: SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
3622: WHERE run_id = p_run_id
3623: GROUP BY project_id ;
3624: CURSOR c_pool_amount IS
3625: SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
3626: FROM pa_alloc_run_source_det
3627: WHERE run_id = p_run_id;
3628: v_project_id NUMBER ;
3629: v_task_id NUMBER ;
3630: v_proj_prev_offset_amt NUMBER ;
3633: CURSOR C_prev_offset_task_CE (v_project_id IN NUMBER
3634: , v_task_id IN NUMBER) is
3635: SELECT nvl(sum(nvl(current_allocation,0)),0)
3636: from pa_alloc_txn_details pat
3637: ,pa_alloc_runs par
3638: where pat.run_id = par.run_id
3639: and par.fiscal_year = nvl(p_fiscal_year, par.fiscal_year)
3640: and par.quarter = nvl(p_quarter_num,par.quarter)
3641: and par.period_num = nvl(p_period_num , par.period_num)
3652: , a.project_id project_id
3653: , a.task_id task_id
3654: , nvl(a.Total_allocation,0) Total_allocation
3655: FROM pa_alloc_txn_details a,
3656: pa_alloc_runs ar
3657: WHERE a.run_id = ar.run_id
3658: AND a.rule_id = p_rule_id
3659: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
3660: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
3668: AND b.transaction_type = 'O'
3669: AND b.run_id = p_run_id )
3670: AND a.run_id =( SELECT max(c.run_id)
3671: FROM pa_alloc_txn_details c,
3672: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
3673: WHERE c.project_id = a.project_id
3674: AND c.task_id = a.task_id
3675: AND c.transaction_type = 'O'
3676: AND c.run_id = c_ar.run_id
4149: IF P_DEBUG_MODE = 'Y' THEN
4150: pa_debug.write_file('create_offset_txns: ' || 'LOG','v_off_sunk_cost =' ||to_char(v_off_sunk_cost) );
4151: END IF;
4152: END IF ;
4153: update pa_alloc_runs
4154: set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
4155: TOTAL_OFFSETTED_AMOUNT = v_sum_tot_offsets
4156: where run_id = p_run_id ;
4157: pa_debug.reset_err_stack;
4281: ) IS
4282: BEGIN
4283: pa_debug.set_err_stack('insert_alloc_runs');
4284: If x_run_id is NULL then
4285: Select pa_alloc_runs_s.nextval
4286: Into x_run_id
4287: From dual;
4288: End If;
4289: INSERT INTO pa_alloc_runs_all (
4285: Select pa_alloc_runs_s.nextval
4286: Into x_run_id
4287: From dual;
4288: End If;
4289: INSERT INTO pa_alloc_runs_all (
4290: RUN_ID
4291: , RULE_ID
4292: , RUN_PERIOD
4293: , EXPND_ITEM_DATE
4784: p_res_list_member_id IN NUMBER,
4785: p_resource_percent IN NUMBER)
4786: IS
4787: BEGIN
4788: insert into pa_alloc_run_resource_det (
4789: rule_id,
4790: run_id,
4791: member_type,
4792: resource_list_member_id,
4810: WHEN OTHERS THEN
4811: RAISE;
4812: END;
4813: -- Bug3149853 .Changes done to enhance performance .
4814: -- Created procedure to perform BULK INSERT into pa_alloc_run_resource_det.
4815: Procedure bulk_ins_alloc_run_res(p_run_id IN NUMBER,
4816: p_rule_id IN NUMBER,
4817: p_member_type IN VARCHAR2,
4818: p_res_list_member_id_tab IN pa_plsql_datatypes.IdTabTyp,
4819: p_resource_percent IN NUMBER)
4820: IS
4821: BEGIN
4822: FORALL i IN p_res_list_member_id_tab.first..p_res_list_member_id_tab.last
4823: insert into pa_alloc_run_resource_det (
4824: rule_id,
4825: run_id,
4826: member_type,
4827: resource_list_member_id,
5371: RAISE ;
5372: END get_amttype_start_date ;
5373: -- ==========================================================================
5374: /* PROCEDURE : insert_alloc_basis_resource
5375: Purpose : To insert data into pa_alloc_run_basis_det table for each resource
5376: for each task which has some data available in summarization.
5377: Separate inserts are written for each type of amt_type
5378: (FYTD,qtd,itd and ptd).
5379: Created : 16-JAN-02 Manokuma
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
5437: IF P_DEBUG_MODE = 'Y' THEN
5438: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
5439: END IF;
5440: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------- {
5441: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5442: RUN_ID
5443: , RULE_ID
5444: , LINE_NUM
5445: , PROJECT_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
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
5488: and prad.Resource_list_member_id = parr.resource_list_member_id
5523: ,G_last_update_date
5524: ,G_last_updated_by
5525: ,G_last_update_login);
5526: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5527: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5528: RUN_ID
5529: , RULE_ID
5530: , LINE_NUM
5531: , PROJECT_ID
5564: ,G_created_by
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
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
5623: IF P_DEBUG_MODE = 'Y' THEN
5624: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5625: END IF;
5626: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------{
5627: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5628: RUN_ID
5629: , RULE_ID
5630: , LINE_NUM
5631: , PROJECT_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
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
5673: and prad.Resource_list_member_id = parr.resource_list_member_id
5691: ,G_last_update_date
5692: ,G_last_updated_by
5693: ,G_last_update_login);
5694: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5695: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5696: RUN_ID
5697: , RULE_ID
5698: , LINE_NUM
5699: , PROJECT_ID
5730: ,G_created_by
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
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
5758: ,G_last_update_login);
5759: End If; ---------------- }
5760: ELSE /* p_run_period_type = 'GL' */
5761: If Nvl(p_resource_struct_type,'RL') = 'RL' Then --------------{
5762: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5763: RUN_ID
5764: , RULE_ID
5765: , LINE_NUM
5766: , PROJECT_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
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
5808: and prad.Resource_list_member_id = parr.resource_list_member_id
5830: ,G_last_update_date
5831: ,G_last_updated_by
5832: ,G_last_update_login);
5833: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5834: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5835: RUN_ID
5836: , RULE_ID
5837: , LINE_NUM
5838: , PROJECT_ID
5869: ,G_created_by
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
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
5924: IF P_DEBUG_MODE = 'Y' THEN
5925: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5926: END IF;
5927: If Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------------{
5928: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5929: RUN_ID
5930: , RULE_ID
5931: , LINE_NUM
5932: , PROJECT_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
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
5975: and prad.Resource_list_member_id = parr.resource_list_member_id
6000: ,G_last_update_date
6001: ,G_last_updated_by
6002: ,G_last_update_login);
6003: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6004: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
6005: RUN_ID
6006: , RULE_ID
6007: , LINE_NUM
6008: , PROJECT_ID
6040: ,G_created_by
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
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
6095: RAISE ;
6096: END insert_alloc_basis_resource;
6097: -- ==========================================================================
6098: /* PROCEDURE : insert_alloc_source_resource
6099: Purpose : To insert data into pa_alloc_run_source_det table for each resource
6100: for each task which has some data available in summarization.
6101: Separate inserts are written for each type of amt_type
6102: (FYTD,qtd,itd and ptd).
6103: Created : 16-JAN-02 Manokuma
6120: )
6121: IS
6122: cursor c_projects is
6123: select distinct pars.project_id project_id
6124: from pa_alloc_run_sources pars,
6125: pa_resource_list_assignments prla
6126: where pars.project_id = prla.project_id
6127: and prla.resource_list_id = p_resource_list_id
6128: and prla.resource_list_accumulated_flag = 'Y'
6129: and pars.run_id = p_run_id
6130: and NVL(p_resource_struct_type,'RL') = 'RL'
6131: UNION All
6132: select distinct pars.project_id project_id
6133: from pa_alloc_run_sources pars,
6134: pa_rbs_prj_assignments prpa
6135: where pars.project_id = prpa.project_id
6136: and prpa.rbs_header_id = p_resource_list_id
6137: and prpa.rbs_version_id = p_rbs_version_id
6176: IF P_DEBUG_MODE = 'Y' THEN
6177: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
6178: END IF;
6179: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------------{
6180: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6181: RUN_ID
6182: , RULE_ID
6183: , LINE_NUM
6184: , PROJECT_ID
6233: ,G_last_update_date
6234: ,G_last_updated_by
6235: ,G_last_update_login
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
6234: ,G_last_updated_by
6235: ,G_last_update_login
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
6279: ,G_last_updated_by
6280: ,G_last_update_login);
6281: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6282: /* FP.M : Allocation Impact */
6283: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6284: RUN_ID
6285: , RULE_ID
6286: , LINE_NUM
6287: , PROJECT_ID
6335: ,G_created_by
6336: ,G_last_update_date
6337: ,G_last_updated_by
6338: ,G_last_update_login
6339: from pa_alloc_run_resource_det parr,
6340: pa_alloc_run_sources pars,
6341: PA_ALLOC_TXN_ACCUM_RBS_V pta
6342: where pta.Rbs_Element_Id = parr.resource_list_member_id
6343: and pta.Project_id = pars.project_id
6336: ,G_last_update_date
6337: ,G_last_updated_by
6338: ,G_last_update_login
6339: from pa_alloc_run_resource_det parr,
6340: pa_alloc_run_sources pars,
6341: PA_ALLOC_TXN_ACCUM_RBS_V pta
6342: where pta.Rbs_Element_Id = parr.resource_list_member_id
6343: and pta.Project_id = pars.project_id
6344: and pta.task_id = pars.task_id
6394: IF P_DEBUG_MODE = 'Y' THEN
6395: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6396: END IF;
6397: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ------------{
6398: INSERT INTO PA_ALLOC_RUN_source_DET (
6399: RUN_ID
6400: , RULE_ID
6401: , LINE_NUM
6402: , PROJECT_ID
6449: ,G_last_update_date
6450: ,G_last_updated_by
6451: ,G_last_update_login
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
6450: ,G_last_updated_by
6451: ,G_last_update_login
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
6476: ,G_last_update_date
6477: ,G_last_updated_by
6478: ,G_last_update_login);
6479: ElsiF Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6480: INSERT INTO PA_ALLOC_RUN_source_DET (
6481: RUN_ID
6482: , RULE_ID
6483: , LINE_NUM
6484: , PROJECT_ID
6529: ,G_created_by
6530: ,G_last_update_date
6531: ,G_last_updated_by
6532: ,G_last_update_login
6533: from pa_alloc_run_resource_det parr,
6534: pa_alloc_run_sources pars,
6535: PA_ALLOC_TXN_ACCUM_RBS_V pta
6536: where pta.Rbs_Element_ID = parr.resource_list_member_id
6537: and pta.Project_id = pars.project_id
6530: ,G_last_update_date
6531: ,G_last_updated_by
6532: ,G_last_update_login
6533: from pa_alloc_run_resource_det parr,
6534: pa_alloc_run_sources pars,
6535: PA_ALLOC_TXN_ACCUM_RBS_V pta
6536: where pta.Rbs_Element_ID = parr.resource_list_member_id
6537: and pta.Project_id = pars.project_id
6538: and pta.task_id = pars.task_id
6557: ,G_last_update_login);
6558: End If; -------------}
6559: ELSE /* p_run_period_type = 'GL' */
6560: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ----------- {
6561: INSERT INTO PA_ALLOC_RUN_source_DET (
6562: RUN_ID
6563: , RULE_ID
6564: , LINE_NUM
6565: , PROJECT_ID
6612: ,G_last_update_date
6613: ,G_last_updated_by
6614: ,G_last_update_login
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
6613: ,G_last_updated_by
6614: ,G_last_update_login
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
6643: ,G_last_update_date
6644: ,G_last_updated_by
6645: ,G_last_update_login);
6646: ElsIF Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6647: INSERT INTO PA_ALLOC_RUN_source_DET (
6648: RUN_ID
6649: , RULE_ID
6650: , LINE_NUM
6651: , PROJECT_ID
6696: ,G_created_by
6697: ,G_last_update_date
6698: ,G_last_updated_by
6699: ,G_last_update_login
6700: from pa_alloc_run_resource_det parr,
6701: pa_alloc_run_sources pars,
6702: PA_ALLOC_TXN_ACCUM_RBS_V pta
6703: where pta.Rbs_Element_Id = parr.resource_list_member_id
6704: and pta.Project_id = pars.project_id
6697: ,G_last_update_date
6698: ,G_last_updated_by
6699: ,G_last_update_login
6700: from pa_alloc_run_resource_det parr,
6701: pa_alloc_run_sources pars,
6702: PA_ALLOC_TXN_ACCUM_RBS_V pta
6703: where pta.Rbs_Element_Id = parr.resource_list_member_id
6704: and pta.Project_id = pars.project_id
6705: and pta.task_id = pars.task_id
6752: IF P_DEBUG_MODE = 'Y' THEN
6753: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6754: END IF;
6755: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------{
6756: INSERT INTO PA_ALLOC_RUN_source_DET (
6757: RUN_ID
6758: , RULE_ID
6759: , LINE_NUM
6760: , PROJECT_ID
6809: ,G_last_update_date
6810: ,G_last_updated_by
6811: ,G_last_update_login
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
6810: ,G_last_updated_by
6811: ,G_last_update_login
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
6843: ,G_last_update_date
6844: ,G_last_updated_by
6845: ,G_last_update_login);
6846: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6847: INSERT INTO PA_ALLOC_RUN_source_DET (
6848: RUN_ID
6849: , RULE_ID
6850: , LINE_NUM
6851: , PROJECT_ID
6898: ,G_created_by
6899: ,G_last_update_date
6900: ,G_last_updated_by
6901: ,G_last_update_login
6902: from pa_alloc_run_resource_det parr,
6903: pa_alloc_run_sources pars,
6904: PA_ALLOC_TXN_ACCUM_RBS_V pta
6905: where pta.Rbs_Element_Id = parr.resource_list_member_id
6906: and pta.Project_id = pars.project_id
6899: ,G_last_update_date
6900: ,G_last_updated_by
6901: ,G_last_update_login
6902: from pa_alloc_run_resource_det parr,
6903: pa_alloc_run_sources pars,
6904: PA_ALLOC_TXN_ACCUM_RBS_V pta
6905: where pta.Rbs_Element_Id = parr.resource_list_member_id
6906: and pta.Project_id = pars.project_id
6907: and pta.task_id = pars.task_id
6952: RAISE ;
6953: END insert_alloc_source_resource;
6954: -- ==========================================================================
6955: /* PROCEDURE : insert_budget_basis_resource
6956: Purpose : inserts records into pa_alloc_run_basis_det when budgets are used
6957: for basis and resource lists are used to calculate basis amounts.
6958: Created : 02-feb-01 Manoj.
6959: Modified : 24-JAN-03 Tarun for bug 2757875
6960: 06-Apr-04 vthakkar FP.M : ALlocation Impact
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
6978: v_commit NUMBER := 0;
6979: BEGIN
6980: FOR c_projects_rec IN c_projects LOOP
6981: If p_run_period_type = 'PA' then
6982: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
6983: RUN_ID
6984: , RULE_ID
6985: , LINE_NUM
6986: , PROJECT_ID
7010: ,G_created_by
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 (
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') ,
7043: ,G_last_update_date
7044: ,G_last_updated_by
7045: ,G_last_update_login);
7046: Elsif p_run_period_type = 'GL' then
7047: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7048: RUN_ID
7049: , RULE_ID
7050: , LINE_NUM
7051: , PROJECT_ID
7076: ,G_created_by
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 */
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 (
7281: , p_last_update_login IN NUMBER)
7282: IS
7283: BEGIN
7284: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7285: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7286: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7287: RUN_ID
7288: , RULE_ID
7289: , LINE_NUM
7282: IS
7283: BEGIN
7284: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7285: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7286: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7287: RUN_ID
7288: , RULE_ID
7289: , LINE_NUM
7290: , PROJECT_ID
7348: Steps: 1. Get the Period Year and Quarter Num values for the given run_period.
7349: 2. If source_resource_lists is not null then
7350: Populate Include_RLM_tbl.
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.
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) ;
7373: v_pool_percent NUMBER ;
7374: v_net_fixed_amount NUMBER ;
7375: cursor C_run_source_details is
7376: Select line_num, project_id , task_id
7377: from pa_alloc_run_sources
7378: where rule_id = p_rule_id
7379: and run_id = p_run_id
7380: and exclude_flag <> 'Y' ;
7381: cursor C_get_pool_amount is
7379: and run_id = p_run_id
7380: and exclude_flag <> 'Y' ;
7381: cursor C_get_pool_amount is
7382: select nvl(sum(nvl(eligible_amount,0)),0)
7383: from pa_alloc_run_source_det
7384: where run_id = p_run_id ;
7385: Cursor c_get_proj is
7386: Select RUN_ID ,
7387: RULE_ID ,
7387: RULE_ID ,
7388: LINE_NUM ,
7389: PROJECT_ID ,
7390: TASK_ID
7391: From Pa_Alloc_Run_Sources
7392: Where Rule_Id = P_Rule_Id
7393: And Run_Id = P_Run_Id
7394: And Nvl(Exclude_Flag,'N') = 'N';
7395: BEGIN
7429: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7430: END IF;
7431: /* bug 2211234 - added p_run_id.
7432: - removed v_src_rlm_tab
7433: This procedure will now populate records into pa_alloc_run_resources_det */
7434: populate_RLM_table( p_rule_id, p_run_id,
7435: 'S',
7436: v_resource_list_id ,
7437: p_source_resource_struct_type ,
7440: ) ;
7441: BEGIN
7442: select count(*)
7443: into v_resource_count
7444: from pa_alloc_run_resource_det
7445: where rule_id = p_rule_id
7446: and run_id = p_run_id
7447: and member_type = 'S';
7448: if v_resource_count = 0 then
7449: alloc_errors(p_rule_id, p_run_id, 'S','E', 'PA_AL_NO_INCL_SRC_RESRC',TRUE) ;
7450: end if ;
7451: EXCEPTION
7452: WHEN OTHERS THEN
7453: pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
7454: IF P_DEBUG_MODE = 'Y' THEN
7455: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7456: END IF;
7457: RAISE;
7513: END IF;
7514: If p_run_amount_type in ( 'FYTD','QTD') then
7515: For l_get_proj in c_get_proj
7516: Loop
7517: Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id,
7518: task_id, creation_date, created_by,
7519: last_update_date,
7520: last_updated_by, last_update_login,
7521: amount, eligible_amount)
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
7554: /* 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 */
7555: where /* Bug 3749469
7556: pars.rule_id = p_rule_id
7557: and pars.run_id = p_run_id
7558: and pars.exclude_flag = 'N'
7583: elsif p_run_amount_type = 'PTD' then
7584: If p_run_period_type = 'GL' then
7585: For l_get_proj in c_get_proj
7586: Loop
7587: Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id, task_id,
7588: creation_date, created_by,
7589: last_update_date,
7590: last_updated_by, last_update_login,
7591: amount, eligible_amount)
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
7624: and pars.exclude_flag = 'N'
7625: and pta.project_id = pars.project_id
7642: End Loop;
7643: elsif p_run_period_type = 'PA' then
7644: For l_get_proj in c_get_proj
7645: Loop
7646: Insert into pa_alloc_run_source_det(
7647: rule_id, run_id, line_num, project_id, task_id,
7648: creation_date, created_by, last_update_date,
7649: last_updated_by, last_update_login,
7650: amount, eligible_amount)
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'
7683: and pta.project_id = pars.project_id
7698: end if ;
7699: else
7700: For l_get_proj in c_get_proj
7701: Loop
7702: Insert into pa_alloc_run_source_det(
7703: rule_id, run_id, line_num, project_id, task_id,
7704: creation_date, created_by, last_update_date,
7705: last_updated_by, last_update_login,
7706: amount, eligible_amount)
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
7738: and pars.run_id = p_run_id
7739: and pars.exclude_flag = 'N'
7740: and pars.project_id = p.project_id
7848: IF P_DEBUG_MODE = 'Y' THEN
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7854: RUN_ID
7855: , RULE_ID
7856: , LINE_NUM
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7854: RUN_ID
7855: , RULE_ID
7856: , LINE_NUM
7857: , PROJECT_ID
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
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
7934: pa_base_budget_by_pa_period_v
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.
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.
7937: End if;
7938: End if
7939: Determine the basis_percent ;
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
7977: and run_id = p_run_id
7978: and exclude_flag <> 'Y' ;
7979: cursor C_tot_basis_amt is
7980: select nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
7981: from pa_alloc_run_basis_det
7982: where run_id = p_run_id ;
7983: cursor C_line_basis_amt is
7984: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
7985: from pa_alloc_run_basis_det
7981: from pa_alloc_run_basis_det
7982: where run_id = p_run_id ;
7983: cursor C_line_basis_amt is
7984: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
7985: from pa_alloc_run_basis_det
7986: where run_id = p_run_id
7987: group by line_num, line_percent ;
7988: cursor C_tgt_line_pct is
7989: select sum((nvl(line_percent,0)))
7986: where run_id = p_run_id
7987: group by line_num, line_percent ;
7988: cursor C_tgt_line_pct is
7989: select sum((nvl(line_percent,0)))
7990: from pa_alloc_run_basis_det
7991: where run_id = p_run_id ;
7992: cursor c_get_proj is
7993: Select RUN_ID ,
7994: RULE_ID ,
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
8069: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8070: END IF;
8071: /* Bug 2211234 - added p_run_id.
8072: - removed v_basis_rlm_tab
8073: this procedure will now insert into pa_alloc_run_resources_det */
8074: populate_RLM_table( p_rule_id,
8075: p_run_id,
8076: 'B',
8077: v_resource_list_id,
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
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8170: END IF;
8171: For l_get_proj in c_get_proj
8172: Loop
8173: Insert into pa_alloc_run_basis_det (
8174: rule_id, run_id, line_num, project_id, task_id,
8175: line_percent, creation_date, created_by,
8176: last_update_date, last_updated_by,
8177: last_update_login, amount)
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
8235: elsif p_basis_amt_type = 'PTD' then
8236: If p_run_period_type = 'GL' then
8237: For l_get_proj in c_get_proj
8238: Loop
8239: Insert into pa_alloc_run_basis_det (
8240: rule_id, run_id, line_num, project_id, task_id,
8241: line_percent, creation_date, created_by,
8242: last_update_date, last_updated_by,
8243: last_update_login, amount)
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'
8292: End Loop;
8293: Elsif p_run_period_type = 'PA' then
8294: For l_get_proj in c_get_proj
8295: Loop
8296: Insert into pa_alloc_run_basis_det (
8297: rule_id, run_id, line_num, project_id, task_id,
8298: line_percent, creation_date, created_by,
8299: last_update_date, last_updated_by,
8300: last_update_login, amount)
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
8344: end if ;
8345: Else -- process for ITD amounts
8346: For l_get_proj in c_get_proj
8347: Loop
8348: Insert into pa_alloc_run_basis_det (
8349: rule_id, run_id, line_num, project_id, task_id,
8350: line_percent, creation_date, created_by,
8351: last_update_date, last_updated_by,
8352: last_update_login, amount)
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
8410: IF P_DEBUG_MODE = 'Y' THEN
8411: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
8412: END IF;
8413: if p_basis_amt_type = 'ITD' then
8414: Insert into pa_alloc_run_basis_det(
8415: rule_id, run_id, line_num, project_id, task_id,
8416: line_percent, creation_date, created_by, last_update_date,
8417: last_updated_by, last_update_login, amount)
8418: select part.rule_id, part.run_id, part.line_num,
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
8447: part.line_percent, G_creation_date, G_created_by,
8448: G_last_update_date,
8449: G_last_updated_by, G_last_update_login ;
8450: elsif p_basis_amt_type in ('FYTD','QTD') then /* Added bug 2757875 */
8451: Insert into pa_alloc_run_basis_det(
8452: rule_id, run_id, line_num, project_id, task_id,
8453: line_percent, creation_date, created_by, last_update_date,
8454: last_updated_by, last_update_login, amount)
8455: select part.rule_id, part.run_id, part.line_num,
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
8485: G_last_updated_by, G_last_update_login ;
8486: /* Added the following code for bug 2757875 */
8487: else -- process for PTD amounts
8488: If p_run_period_type = 'GL' then
8489: Insert into pa_alloc_run_basis_det(
8490: rule_id, run_id, line_num, project_id, task_id,
8491: line_percent, creation_date, created_by, last_update_date,
8492: last_updated_by, last_update_login, amount)
8493: select part.rule_id, part.run_id, part.line_num,
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
8516: part.line_percent, G_creation_date, G_created_by,
8517: G_last_update_date,
8518: G_last_updated_by, G_last_update_login ;
8519: elsif p_run_period_type = 'PA' then
8520: Insert into pa_alloc_run_basis_det(
8521: rule_id, run_id, line_num, project_id, task_id,
8522: line_percent, creation_date, created_by, last_update_date,
8523: last_updated_by, last_update_login, amount)
8524: select part.rule_id, part.run_id, part.line_num,
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
8550: end if; -- p_run_period_type
8551: /* Code changes end for bug 2757875 */
8552: End if ;
8553: --- Create Zero amount records for tasks that did not have budgets..
8554: Insert into pa_alloc_run_basis_det(
8555: rule_id, run_id, line_num, project_id, task_id,
8556: line_percent, creation_date, created_by, last_update_date,
8557: last_updated_by, last_update_login, amount)
8558: select part.rule_id, part.run_id, part.line_num,
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'
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'
8569: from pa_alloc_run_basis_det parbd
8570: where parbd.run_id = part.run_id
8571: and parbd.project_id = part.project_id
8572: and parbd.task_id = part.task_id ) ;
8573: End if ; -- basis category
8574: End if ; -- For Resource list
8575: End if ; --- For basis_method
8576: /* added for bug 2182563. For FP insert one row per target with zero amount */
8577: IF p_basis_method = 'FP' then
8578: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8579: with amount = 0. so that the following insert does not insert any duplicate
8580: records into the table if there is a record already existing with amount = 0 */
8581: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8582: WHERE rule_id = p_rule_id
8577: IF p_basis_method = 'FP' then
8578: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8579: with amount = 0. so that the following insert does not insert any duplicate
8580: records into the table if there is a record already existing with amount = 0 */
8581: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8582: WHERE rule_id = p_rule_id
8583: AND run_id = p_run_id
8584: AND nvl(amount,0) = 0;
8585: /* we need to insert rows for a run_id/line_num only if there are no records in
8584: AND nvl(amount,0) = 0;
8585: /* we need to insert rows for a run_id/line_num only if there are no records in
8586: in basis table for that run_id and line number with amount <> 0. Because in this
8587: case the program has function like basis_method = 'FS' for that target line. */
8588: Insert into pa_alloc_run_basis_det (
8589: rule_id, run_id, line_num, project_id, task_id,
8590: line_percent, creation_date, created_by,
8591: last_update_date, last_updated_by,
8592: last_update_login, amount)
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
8599: and part.run_id = p_run_id
8600: and part.exclude_flag = 'N'
8601: and not exists
8602: (select null
8603: from pa_alloc_run_basis_det parb
8604: where parb.run_id = part.run_id
8605: and parb.line_num = part.line_num
8606: and parb.amount <> 0);
8607: END IF;
8634: END IF;
8635: End if;
8636: Close C_tot_basis_amt ;
8637: if nvl(v_tot_basis_amt,0) <> 0 then /* added for 1900331 */
8638: UPDATE pa_alloc_run_basis_det
8639: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_tot_basis_amt)
8640: WHERE run_id = p_run_id ;
8641: else
8642: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8642: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8643: p_basis_method := 'S';
8644: /* bug 1900331 prorate equally */
8645: /* commented for 2182563
8646: UPDATE pa_alloc_run_basis_det
8647: SET basis_percent = 100/v_tot_basis_rec
8648: WHERE run_id = p_run_id ;
8649: */
8650: end if;
8663: END IF;
8664: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_LINE_BASIS_AMT_IS_ZERO',TRUE) ;
8665: End if;
8666: if nvl(v_line_basis_amt,0) <> 0 then /* added for bug 1900331 */
8667: UPDATE pa_alloc_run_basis_det
8668: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_line_basis_amt)
8669: WHERE run_id = p_run_id
8670: AND line_num = v_line_num ;
8671: else
8669: WHERE run_id = p_run_id
8670: AND line_num = v_line_num ;
8671: else
8672: /* 1900331 - prorate equaly within the same target line */
8673: UPDATE pa_alloc_run_basis_det
8674: SET basis_percent = 100/v_line_count
8675: WHERE run_id = p_run_id
8676: AND line_num = v_line_num ;
8677: end if;
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);
9216: --Added this If and the else part for capital project changes
9217: IF p_rule_id <> -1 THEN
9218: If pa_debug.Acquire_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9219: G_fatal_err_found:= TRUE;
9220: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9221: G_created_by, G_last_update_date,
9222: G_last_updated_by, G_last_update_login,
9223: 'R', 'E', NULL, NULL, 'PA_AL_CANT_ACQUIRE_LOCK');
9224: end if;
9224: end if;
9225: ELSE
9226: If pa_debug.Acquire_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9227: G_fatal_err_found:= TRUE;
9228: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9229: G_created_by, G_last_update_date,
9230: G_last_updated_by, G_last_update_login,
9231: 'R', 'E', NULL, NULL, 'PA_CINT_CANT_ACQUIRE_LOCK');
9232: END IF;
9248: END IF;
9249: IF p_rule_id <> -1 THEN
9250: If pa_debug.Release_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9251: G_fatal_err_found:= TRUE;
9252: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9253: G_created_by, G_last_update_date,
9254: G_last_updated_by, G_last_update_login,
9255: 'R', 'E', NULL, NULL, 'PA_AL_LOCK_RELEASE_FAILED');
9256: end if;
9256: end if;
9257: ELSE
9258: If pa_debug.Release_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9259: G_fatal_err_found:= TRUE;
9260: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9261: G_created_by, G_last_update_date,
9262: G_last_updated_by, G_last_update_login,
9263: 'R', 'E', NULL, NULL, 'PA_CINT_LOCK_RELEASE_FAILED');
9264: End If;
9285: , target_exp_type
9286: , offset_exp_type
9287: , trunc(expnd_item_date) expnd_item_date
9288: , offset_method
9289: from pa_alloc_runs
9290: where run_id = p_run_id ;
9291: cursor C_org_id is
9292: select org_id
9293: from pa_implementations ;
9358: OPEN C_run;
9359: FETCH C_run INTO run_rec ;
9360: IF C_run%NOTFOUND THEN
9361: G_fatal_err_found := TRUE;
9362: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9363: G_created_by, G_last_update_date,
9364: G_last_updated_by, G_last_update_login,
9365: 'R', 'E', NULL, NULL, 'PA_AL_RUN_NOT_EXISTS');
9366: x_errbuf := 'PA_AL_RUN_NOT_EXISTS' ;
9370: v_offset_expnd_org := pa_utils.GetOrgName(run_rec.offset_exp_org_id ) ;
9371: If (nvl(p_rule_id,-99) <> -1) AND --Added this condition for capital project changes
9372: v_target_expnd_org is NULL THEN
9373: G_fatal_err_found := TRUE;
9374: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9375: G_created_by, G_last_update_date,
9376: G_last_updated_by, G_last_update_login,
9377: 'R', 'E', NULL, NULL, 'PA_AL_INVALID_TARGET_EXP_ORG');
9378: x_errbuf := 'PA_AL_INVALID_TARGET_EXP_ORG' ;
9380: If (nvl(p_rule_id,-99) <> -1) AND --Added this condition for capital project changes
9381: (run_rec.offset_method <> 'N') AND
9382: (v_offset_expnd_org is NULL) then
9383: G_fatal_err_found := TRUE;
9384: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9385: G_created_by, G_last_update_date,
9386: G_last_updated_by, G_last_update_login,
9387: 'R', 'E', NULL, NULL, 'PA_AL_INVALID_OFFSET_EXP_ORG');
9388: x_errbuf := 'PA_AL_INVALID_OFFSET_EXP_ORG' ;
9596:
9597: begin
9598: for r1 in c_alloc_txn_err(p_run_id) loop
9599:
9600: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9601: G_created_by, G_last_update_date,
9602: G_last_updated_by, G_last_update_login,
9603: 'T', 'E', NULL, NULL, r1.rejection_code);
9604: end loop;
9633: Delete from pa_expenditure_groups
9634: where expenditure_group in ( v_tgt_exp_group, v_off_exp_group) ;
9635: else
9636: --Modifed this for capital project changes.
9637: update pa_alloc_runs
9638: set target_exp_group = v_tgt_exp_group ,
9639: offset_exp_group = decode(p_rule_id,
9640: -1,null,
9641: decode(run_rec.offset_method,'N',NULL,v_off_exp_group) )
9671: IS
9672: Cursor C_run is
9673: Select target_exp_group
9674: , offset_exp_group
9675: from pa_alloc_runs
9676: where run_id = p_run_id ;
9677: run_rec C_run%ROWTYPE ;
9678: v_num_reversed NUMBER ;
9679: v_num_rejected NUMBER ;
9753: End if;
9754: End If;
9755: -- Update the alloc runs table with the reversal info --
9756: If nvl(v_return_code,'0') = '0' then
9757: update pa_alloc_runs
9758: set run_status = 'RV'
9759: ,reversal_date = trunc(sysdate)
9760: ,rev_target_exp_group = p_tgt_exp_group
9761: ,rev_offset_exp_group = p_off_exp_group
9809: /*Code Changes for Bug No.2984871 end */
9810: End Loop;
9811: --- Delete Transactions from source_details
9812: While 1=1 Loop /* Bug 2176096 */
9813: Delete from pa_alloc_run_source_det
9814: where run_id = p_run_id and rownum < 10001;
9815: /* Commented for Bug 2984871 Commit; */
9816: If Sql%Rowcount = 0 THen
9817: Exit;
9821: /*Code Changes for Bug No.2984871 end */
9822: End Loop;
9823: --- Delete Transactions from basis_details
9824: While 1=1 Loop /* Bug 2176096 */
9825: Delete from pa_alloc_run_basis_det
9826: where run_id = p_run_id and rownum < 10001;
9827: /* Commented for Bug 2984871 Commit; */
9828: If Sql%Rowcount = 0 THen
9829: Exit;
9831: /*Code Changes for Bug No.2984871 start */
9832: Commit;
9833: /*Code Changes for Bug No.2984871 end */
9834: End Loop;
9835: -- Delete Transactions from pa_alloc_run_sources
9836: While 1=1 Loop /* Bug 2176096 */
9837: Delete from pa_alloc_run_sources
9838: where run_id = p_run_id and rownum < 10001;
9839: /* Commented for Bug 2984871 Commit; */
9833: /*Code Changes for Bug No.2984871 end */
9834: End Loop;
9835: -- Delete Transactions from pa_alloc_run_sources
9836: While 1=1 Loop /* Bug 2176096 */
9837: Delete from pa_alloc_run_sources
9838: where run_id = p_run_id and rownum < 10001;
9839: /* Commented for Bug 2984871 Commit; */
9840: If Sql%Rowcount = 0 THen
9841: Exit;
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;
9855: /*Code Changes for Bug No.2984871 start */
9856: Commit;
9857: /*Code Changes for Bug No.2984871 end */
9858: End Loop;
9859: -- Delete Transactions from pa_alloc_run_resource_det
9860: While 1=1 Loop /* Bug 2182563 */
9861: Delete from pa_alloc_run_resource_det
9862: where run_id = p_run_id and rownum < 10001;
9863: /* Commented for Bug 2984871 Commit; */
9857: /*Code Changes for Bug No.2984871 end */
9858: End Loop;
9859: -- Delete Transactions from pa_alloc_run_resource_det
9860: While 1=1 Loop /* Bug 2182563 */
9861: Delete from pa_alloc_run_resource_det
9862: where run_id = p_run_id and rownum < 10001;
9863: /* Commented for Bug 2984871 Commit; */
9864: If Sql%Rowcount = 0 Then
9865: Exit;
9873: where run_id = p_run_id ;
9874: -- Delete Transactions from pa_alloc_exceptions
9875: Delete from pa_alloc_exceptions
9876: where run_id = p_run_id ;
9877: -- Delete Transactions from pa_alloc_runs
9878: Delete from pa_alloc_runs
9879: where run_id = p_run_id ;
9880: ---- Release Lock on the rule_name ---------------------
9881: unlock_rule(p_rule_id , p_run_id) ;
9874: -- Delete Transactions from pa_alloc_exceptions
9875: Delete from pa_alloc_exceptions
9876: where run_id = p_run_id ;
9877: -- Delete Transactions from pa_alloc_runs
9878: Delete from pa_alloc_runs
9879: where run_id = p_run_id ;
9880: ---- Release Lock on the rule_name ---------------------
9881: unlock_rule(p_rule_id , p_run_id) ;
9882: pa_debug.reset_err_stack ;
9894: )
9895: IS
9896: CURSOR get_run_id
9897: IS
9898: select run_id from pa_alloc_runs_all
9899: where rule_id = p_rule_id
9900: and run_status = 'DL';
9901: l_run_id NUMBER;
9902: v_debug_mode VARCHAR2(2);
9931: END IF;
9932: --Delete all the txn source details when this api is called in
9933: --the context of Capitalized Interest
9934: IF p_rule_id = -1 THEN
9935: pa_alloc_run.delete_cint_source_dets
9936: ( p_run_id => l_run_id
9937: ,x_return_status => l_return_status
9938: ,x_msg_data => l_msg_data
9939: ,x_msg_count => l_msg_count);
9959: /*Code Changes for Bug No.2984871 end */
9960: End Loop;
9961: --- Delete Transactions from source_details
9962: While 1=1 Loop /* Bug 2176096 */
9963: Delete from pa_alloc_run_source_det
9964: where run_id = l_run_id and rownum < 10001;
9965: /* Commented for Bug 2984871 Commit; */
9966: If Sql%Rowcount = 0 THen
9967: Exit;
9971: /*Code Changes for Bug No.2984871 end */
9972: End Loop;
9973: --- Delete Transactions from basis_details
9974: While 1=1 Loop /* Bug 2176096 */
9975: Delete from pa_alloc_run_basis_det
9976: where run_id = l_run_id and rownum < 10001;
9977: /* Commented for Bug 2984871 Commit; */
9978: If Sql%Rowcount = 0 THen
9979: Exit;
9981: /*Code Changes for Bug No.2984871 start */
9982: Commit;
9983: /*Code Changes for Bug No.2984871 end */
9984: End Loop;
9985: -- Delete Transactions from pa_alloc_run_sources
9986: While 1=1 Loop /* Bug 2176096 */
9987: Delete from pa_alloc_run_sources
9988: where run_id = l_run_id and rownum < 10001;
9989: /* Commented for Bug 2984871 Commit; */
9983: /*Code Changes for Bug No.2984871 end */
9984: End Loop;
9985: -- Delete Transactions from pa_alloc_run_sources
9986: While 1=1 Loop /* Bug 2176096 */
9987: Delete from pa_alloc_run_sources
9988: where run_id = l_run_id and rownum < 10001;
9989: /* Commented for Bug 2984871 Commit; */
9990: If Sql%Rowcount = 0 THen
9991: 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;
10005: /*Code Changes for Bug No.2984871 start */
10006: Commit;
10007: /*Code Changes for Bug No.2984871 end */
10008: End Loop;
10009: -- Delete Transactions from pa_alloc_run_resource_det
10010: While 1=1 Loop /* Bug 2182563 */
10011: Delete from pa_alloc_run_resource_det
10012: where run_id = l_run_id and rownum < 10001;
10013: /* Commented for Bug 2984871 Commit; */
10007: /*Code Changes for Bug No.2984871 end */
10008: End Loop;
10009: -- Delete Transactions from pa_alloc_run_resource_det
10010: While 1=1 Loop /* Bug 2182563 */
10011: Delete from pa_alloc_run_resource_det
10012: where run_id = l_run_id and rownum < 10001;
10013: /* Commented for Bug 2984871 Commit; */
10014: If Sql%Rowcount = 0 Then
10015: Exit;
10023: where run_id = l_run_id ;
10024: -- Delete Transactions from pa_alloc_exceptions
10025: Delete from pa_alloc_exceptions
10026: where run_id = l_run_id ;
10027: -- Delete Transactions from pa_alloc_runs
10028: Delete from pa_alloc_runs
10029: where run_id = l_run_id ;
10030: ---- Release Lock on the rule_name ---------------------
10031: --unlock_rule(p_rule_id , l_run_id) ;
10024: -- Delete Transactions from pa_alloc_exceptions
10025: Delete from pa_alloc_exceptions
10026: where run_id = l_run_id ;
10027: -- Delete Transactions from pa_alloc_runs
10028: Delete from pa_alloc_runs
10029: where run_id = l_run_id ;
10030: ---- Release Lock on the rule_name ---------------------
10031: --unlock_rule(p_rule_id , l_run_id) ;
10032: END LOOP;
10228: --This procedure is called from PA_CAP_INT_PVT. Generate_cap_interest when release button is pressed on the
10229: --Allocation form(when form is accessed in the context of capitalized interest) or when auto release flag is
10230: --passed Y
10231: PROCEDURE release_capint_txns
10232: ( p_run_id IN pa_alloc_runs_all.run_id%TYPE
10233: ,x_return_status OUT NOCOPY VARCHAR2
10234: ,x_msg_count OUT NOCOPY NUMBER
10235: ,x_msg_data OUT NOCOPY VARCHAR2
10236: )
10256: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10257: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10258: END IF;
10259: --Initialise the who columns
10260: pa_alloc_run.init_who_cols;
10261: --Call the api that releases the allocation run
10262: pa_alloc_run.release_alloc_txns
10263: ( p_rule_id => -1
10264: ,p_run_id => p_run_id
10258: END IF;
10259: --Initialise the who columns
10260: pa_alloc_run.init_who_cols;
10261: --Call the api that releases the allocation run
10262: pa_alloc_run.release_alloc_txns
10263: ( p_rule_id => -1
10264: ,p_run_id => p_run_id
10265: ,x_retcode => x_return_status
10266: ,x_errbuf => x_msg_data);
10283: /* bug fix: 3123539 based on the return status update the run staus and avoid
10284: * setting the concurrent process to raise error
10285: */
10286: IF x_return_status = 'S' Then
10287: UPDATE pa_alloc_runs
10288: SET run_status = 'RS'
10289: ,release_request_id = l_rel_request_id
10290: ,release_request_date =sysdate
10291: WHERE run_id = p_run_id;
10290: ,release_request_date =sysdate
10291: WHERE run_id = p_run_id;
10292: Else
10293: -- Update the status to release failure
10294: UPDATE pa_alloc_runs
10295: SET run_status = 'RF'
10296: ,release_request_id = null
10297: ,release_request_date =sysdate
10298: WHERE run_id = p_run_id;
10319: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10320: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10321: END IF;
10322: -- Update the status to release failure
10323: UPDATE pa_alloc_runs
10324: SET run_status = 'RF'
10325: ,release_request_id = l_rel_request_id
10326: ,release_request_date =sysdate
10327: WHERE run_id = p_run_id;
10335: --This procedure deletes the source details for each capital interest transaction. This procedure will
10336: --be called from delete_alloc_run api when the DELETE button is pressed to delete a capital interest
10337: --batch
10338: PROCEDURE delete_cint_source_dets
10339: ( p_run_id IN pa_alloc_runs_all.run_id%TYPE
10340: ,x_return_status OUT NOCOPY VARCHAR2
10341: ,x_msg_count OUT NOCOPY NUMBER
10342: ,x_msg_data OUT NOCOPY VARCHAR2
10343: )
10389: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10390: x_msg_count := 1;
10391: x_msg_data := SQLERRM;
10392: FND_MSG_PUB.add_exc_msg
10393: ( p_pkg_name => 'pa_alloc_run'
10394: ,p_procedure_name => 'delete_cint_source_dets'
10395: ,p_error_text => x_msg_data);
10396: IF l_debug_mode = 'Y' THEN
10397: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10399: END IF;
10400: pa_debug.reset_curr_function;
10401: RAISE;
10402: END delete_cint_source_dets;
10403: END PA_ALLOC_run;