[Home] [Help]
1: PACKAGE BODY PA_ALLOC_RUN AS
2: /* $Header: PAXALRNB.pls 120.12.12020000.3 2013/03/06 09:51:42 admarath 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;
157: pa_debug.G_err_stage := 'GETTING NEW RUN ID';
158: IF P_DEBUG_MODE = 'Y' THEN
159: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
160: END IF;
161: SELECT pa_alloc_runs_s.nextval
162: INTO v_run_id
163: FROM dual;
164: G_alloc_run_id := v_run_id;
165: x_run_id := v_run_id;
505: pa_debug.G_err_stage := 'RELEASING THE RUN';
506: IF P_DEBUG_MODE = 'Y' THEN
507: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
508: END IF;
509: pa_alloc_run.release_alloc_txns( p_rule_id, x_run_id ,x_retcode,
510: x_errbuf );
511: END IF; /* v_mode = release */
512: if x_errbuf is NOT NULL then
513: alloc_errors( p_rule_id, x_run_id, 'R', 'E',
526: alloc_errors( p_rule_id, x_run_id,'R','W','PA_AL_ALL_TASKS_RECD_ZERO_AMT', FALSE) ;
527: END IF ;
528:
529: if x_errbuf is null then /* added if clause for 6243121 */
530: UPDATE pa_alloc_runs
531: SET run_status = DECODE( v_mode, 'RELEASE', 'RS', 'DS'),
532: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
533: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
534: WHERE run_id = x_run_id;
540: END IF;
541:
542: /* added for 6243121 */
543: else -- if any error/exception is encountered while releasing
544: UPDATE pa_alloc_runs
545: SET run_status = DECODE( v_mode, 'RELEASE', 'RF', 'DF'),
546: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
547: release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
548: WHERE run_id = x_run_id;
575: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
576: IF P_DEBUG_MODE = 'Y' THEN
577: pa_debug.write_file('allocation_run: ' || 'LOG', pa_debug.G_err_stage);
578: END IF;
579: UPDATE pa_alloc_runs
580: SET run_status = DECODE( v_mode, 'RELEASE', 'RF',
581: 'DRAFT', 'DF',
582: run_status ),
583: release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
603: , x_run_id IN OUT NOCOPY NUMBER
604: , x_mode OUT NOCOPY VARCHAR2 ) IS
605: CURSOR C_run_id IS
606: SELECT max(run_id)
607: FROM pa_alloc_runs
608: WHERE rule_id = p_rule_id
609: AND run_id <> x_run_id;
610: CURSOR C_prev_run( p_prev_run_id IN NUMBER) IS
611: SELECT run_status
608: WHERE rule_id = p_rule_id
609: AND run_id <> x_run_id;
610: CURSOR C_prev_run( p_prev_run_id IN NUMBER) IS
611: SELECT run_status
612: FROM pa_alloc_runs
613: WHERE run_id = p_prev_run_id ;
614: v_run_status VARCHAR2(5) := NULL;
615: v_prev_run_id NUMBER;
616: BEGIN
1059: , p_last_updated_by IN NUMBER
1060: , p_last_update_login IN NUMBER) IS
1061: CURSOR source_exists IS
1062: SELECT 1
1063: FROM pa_alloc_run_sources
1064: WHERE run_id = p_run_id
1065: AND project_id = p_project_id
1066: AND task_id = p_task_id;
1067: v_dummy NUMBER;
1081: p_project_id, p_task_id );
1082: END IF;
1083: CLOSE source_exists;
1084: IF (allow_insert_flag = 'Y') THEN
1085: INSERT INTO PA_ALLOC_RUN_SOURCES (
1086: RUN_ID
1087: , RULE_ID
1088: , LINE_NUM
1089: , PROJECT_ID
1122: , p_project_id IN NUMBER
1123: , p_task_id IN NUMBER ) RETURN NUMBER IS
1124: CURSOR C_excl_src_proj_task IS
1125: SELECT project_id, task_id
1126: FROM pa_alloc_run_sources
1127: WHERE run_id = p_run_id
1128: AND exclude_flag = 'Y'
1129: AND project_id = p_project_id
1130: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1129: AND project_id = p_project_id
1130: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1131: CURSOR C_excl_trg_proj_task IS
1132: SELECT project_id, task_id
1133: FROM pa_alloc_run_targets
1134: WHERE run_id = p_run_id
1135: AND exclude_flag = 'Y'
1136: AND project_id = p_project_id
1137: AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
1303: WHERE project_id = p_proj_id
1304: AND pa_task_utils.check_child_exists(task_id) = 0;
1305: CURSOR c_alloc_run_src_projects IS
1306: SELECT project_id, task_id
1307: FROM pa_alloc_run_sources
1308: WHERE rule_id = p_rule_id
1309: AND run_id = p_run_id;
1310: RUN_SRC_PROJ_REC c_alloc_run_src_projects%ROWTYPE;
1311: CURSOR C_top_task (p_tsk_id IN NUMBER)IS
1820: , p_bas_method IN VARCHAR2
1821: , p_dup_targets_flag IN VARCHAR2 ) IS
1822: CURSOR target_exists IS
1823: SELECT 1
1824: FROM pa_alloc_run_targets
1825: WHERE run_id = p_run_id
1826: AND project_id = p_project_id
1827: AND task_id = p_task_id;
1828: v_dummy NUMBER;
1847: END IF;
1848: IF (allow_insert_flag = 'Y') THEN
1849: /* added if condition for bug 2619977 */
1850: /* Invoking FP API to get budget_version_id. Will populate the ID in
1851: pa_alloc_run_targets based on budget type or FP type selected. After
1852: this processing become same for both basis - budgets and FPs */
1853: PA_FIN_PLAN_UTILS.GET_COST_BASE_VERSION_INFO
1854: ( p_project_id
1855: ,G_basis_fin_plan_Type_id
1867: '] Return status ['||x_return_status||']' );
1868: END IF;
1869: ---- Here needs a validation of
1870: /******** Need to handle the if error is generated in API *****/
1871: INSERT INTO PA_ALLOC_RUN_TARGETS (
1872: RUN_ID
1873: , RULE_ID
1874: , LINE_NUM
1875: , PROJECT_ID
2066: AND pt.chargeable_flag = 'Y'
2067: AND x_ei_date between nvl(pt.start_date,x_ei_date) and nvl(pt.completion_date,x_ei_date) ;
2068: CURSOR c_alloc_run_trg_projects IS
2069: SELECT project_id, task_id
2070: FROM pa_alloc_run_targets
2071: WHERE run_id = p_run_id;
2072: CURSOR c_rule_entry_level_code IS
2073: SELECT entry_level_code
2074: FROM pa_budget_entry_methods
2108: v_cx_err_flag VARCHAR2(1) ;
2109: FUNCTION check_line_percent RETURN NUMBER IS
2110: CURSOR c_check_line_percent IS
2111: SELECT distinct line_num, nvl(line_percent,0) line_percent
2112: FROM pa_alloc_run_targets
2113: WHERE rule_id = p_rule_id
2114: AND run_id = p_run_id;
2115: v_percent NUMBER;
2116: v_tot_percent number ;
2559: , p_amount IN NUMBER
2560: , p_eligible_amount IN NUMBER ) IS
2561: BEGIN
2562: pa_debug.set_err_stack('insert_alloc_run_GL_det');
2563: INSERT INTO pa_alloc_run_gl_det(
2564: RUN_ID
2565: , RULE_ID
2566: , LINE_NUM
2567: , SOURCE_CCID
2617: , gl_sets_of_books b
2618: WHERE a.set_of_books_id = b.set_of_books_id;
2619: CURSOR gl_eligible_amount IS
2620: SELECT nvl(sum( nvl(eligible_amount,0)*DECODE(subtract_flag,'Y',-1,1) ),0)
2621: FROM pa_alloc_run_GL_det
2622: WHERE run_id = p_run_id;
2623: CURSOR get_gl_amount( p_sob_id IN NUMBER
2624: , p_source_ccid IN NUMBER
2625: , p_currency_code IN VARCHAR2
2638: AND actual_flag = 'A'
2639: AND translated_flag IS NULL;
2640: CURSOR get_pool_percent IS
2641: SELECT nvl(pool_percent,100) pool_percent
2642: FROM pa_alloc_runs
2643: WHERE run_id = p_run_id;
2644: v_sob_id NUMBER;
2645: v_currency_code VARCHAR2(10);
2646: v_period_type VARCHAR2(10);
2729: , p_line_num IN NUMBER ) RETURN NUMBER IS
2730: x_count NUMBER:=0;
2731: CURSOR C_get_count IS
2732: SELECT count(task_id)
2733: FROM pa_alloc_run_targets
2734: WHERE run_id = p_run_id
2735: AND line_num = p_line_num;
2736: BEGIN
2737: pa_debug.set_err_stack ('Get Proj_task Count For Each Target Line');
2806: SELECT a.run_id run_id
2807: , a.project_id project_id
2808: , a.task_id task_id
2809: , nvl(a.eligible_amount,0) eligible_amount
2810: FROM pa_alloc_run_source_det a,
2811: pa_alloc_runs ar
2812: WHERE a.run_id = ar.run_id
2813: AND a.rule_id = p_rule_id
2814: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2807: , a.project_id project_id
2808: , a.task_id task_id
2809: , nvl(a.eligible_amount,0) eligible_amount
2810: FROM pa_alloc_run_source_det a,
2811: pa_alloc_runs ar
2812: WHERE a.run_id = ar.run_id
2813: AND a.rule_id = p_rule_id
2814: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2815: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
2815: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
2816: AND ar.period_num = NVL(p_prd_num, ar.period_num )
2817: AND a.run_id < p_run_id
2818: AND NOT EXISTS ( SELECT 1
2819: FROM pa_alloc_run_source_det b
2820: WHERE a.project_id = b.project_id
2821: AND a.task_id = b.task_id
2822: AND b.rule_id = p_rule_id
2823: AND b.run_id = p_run_id )
2821: AND a.task_id = b.task_id
2822: AND b.rule_id = p_rule_id
2823: AND b.run_id = p_run_id )
2824: AND a.run_id =( SELECT max(c.run_id)
2825: FROM pa_alloc_run_source_det c,
2826: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2827: WHERE c.project_id = a.project_id
2828: AND c.task_id = a.task_id
2829: AND c.run_id = c_ar.run_id
2822: AND b.rule_id = p_rule_id
2823: AND b.run_id = p_run_id )
2824: AND a.run_id =( SELECT max(c.run_id)
2825: FROM pa_alloc_run_source_det c,
2826: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2827: WHERE c.project_id = a.project_id
2828: AND c.task_id = a.task_id
2829: AND c.run_id = c_ar.run_id
2830: AND c_ar.rule_id = p_rule_id
2841: SELECT A.Run_Id Run_Id,
2842: A.Project_Id Project_Id,
2843: A.Task_Id Task_Id,
2844: NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
2845: FROM PA_ALLOC_RUN_SOURCE_DET A ,
2846: ( --
2847: -- The purpose of this in-line view is to return
2848: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2849: -- less than the input p_Run_Id
2844: NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
2845: FROM PA_ALLOC_RUN_SOURCE_DET A ,
2846: ( --
2847: -- The purpose of this in-line view is to return
2848: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2849: -- less than the input p_Run_Id
2850: --
2851: SELECT MAX ( AR.Run_Id ) AS Run_Id
2852: FROM PA_ALLOC_RUNS AR
2848: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2849: -- less than the input p_Run_Id
2850: --
2851: SELECT MAX ( AR.Run_Id ) AS Run_Id
2852: FROM PA_ALLOC_RUNS AR
2853: WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
2854: AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
2855: AND AR.Period_Num = NVL ( p_Prd_Num , AR.Period_Num )
2856: AND AR.Run_Id < p_Run_Id
2861: WHERE A.Run_Id = AR.Run_Id
2862: AND A.Rule_Id = p_Rule_Id
2863: AND NOT EXISTS
2864: ( SELECT 1
2865: FROM PA_ALLOC_RUN_SOURCE_DET B
2866: WHERE A.Project_Id = B.Project_Id
2867: AND A.Task_Id = B.Task_Id
2868: AND B.Rule_Id = p_Rule_Id
2869: AND B.Run_Id = p_Run_Id
2878: , a.project_id project_id
2879: , a.task_id task_id
2880: , nvl(a.Total_allocation,0) Total_allocation
2881: FROM pa_alloc_txn_details a,
2882: pa_alloc_runs ar
2883: WHERE a.run_id = ar.run_id
2884: AND a.rule_id = p_rule_id
2885: AND ar.rule_id = p_rule_id /* Bug 10191044 */
2886: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2888: AND ar.period_num = NVL(p_prd_num, ar.period_num )
2889: AND a.run_id < p_run_id
2890: AND a.transaction_type = 'T'
2891: AND NOT EXISTS ( SELECT 1
2892: FROM pa_alloc_run_targets b
2893: WHERE b.project_id = a.project_id
2894: AND b.task_id = a.task_id
2895: AND b.exclude_flag = 'N'
2896: AND b.run_id = p_run_id )
2895: AND b.exclude_flag = 'N'
2896: AND b.run_id = p_run_id )
2897: AND a.run_id =( SELECT max(c.run_id)
2898: FROM pa_alloc_txn_details c,
2899: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2900: WHERE c.project_id = a.project_id
2901: AND c.task_id = a.task_id
2902: AND c.transaction_type = 'T'
2903: AND c.run_id = c_ar.run_id
3007: , p_fscl_year IN NUMBER
3008: , p_prd_num IN NUMBER ) IS
3009: SELECT nvl(sum(nvl(a.current_allocation,0)),0)
3010: FROM pa_alloc_txn_details a,
3011: pa_alloc_runs b
3012: WHERE b.rule_id = p_rule_id
3013: AND b.run_id < p_run_id
3014: AND b.quarter = nvl(p_qrtr_num, b.quarter)
3015: AND b.fiscal_year = nvl(p_fscl_year, b.fiscal_year)
3302: , p_line_num IN NUMBER
3303: , p_project_id IN NUMBER
3304: , p_task_id IN NUMBER ) IS
3305: SELECT nvl(sum( nvl(basis_percent, 0)*nvl(line_percent,100)/10000),0) basis
3306: FROM pa_alloc_run_basis_det
3307: WHERE run_id = p_run_id
3308: AND line_num = p_line_num
3309: AND project_id = p_project_id
3310: AND task_id = p_task_id ;
3313: , project_id
3314: , task_id
3315: , line_percent
3316: , exclude_flag
3317: FROM pa_alloc_run_targets
3318: WHERE run_id = p_run_id
3319: AND exclude_flag = 'N';
3320: /* added p_run_id parameter for bug 1900331 */
3321: CURSOR C_all_proj_task_count (p_run_id IN NUMBER) IS
3319: AND exclude_flag = 'N';
3320: /* added p_run_id parameter for bug 1900331 */
3321: CURSOR C_all_proj_task_count (p_run_id IN NUMBER) IS
3322: SELECT count(task_id)
3323: FROM pa_alloc_run_targets
3324: WHERE run_id = p_run_id
3325: AND exclude_flag = 'N';
3326: CURSOR C_count_of_trg_lines IS
3327: SELECT COUNT(DISTINCT line_num)
3324: WHERE run_id = p_run_id
3325: AND exclude_flag = 'N';
3326: CURSOR C_count_of_trg_lines IS
3327: SELECT COUNT(DISTINCT line_num)
3328: FROM pa_alloc_run_targets
3329: WHERE run_id = p_run_id
3330: AND exclude_flag = 'N';
3331: v_quarter_num NUMBER;
3332: v_fiscal_year NUMBER;
3332: v_fiscal_year NUMBER;
3333: v_period_num NUMBER;
3334: cursor c_sum_of_allocated_amts is
3335: select NVL(sum ( nvl(par1.allocated_amount,0)),0)
3336: from pa_alloc_runs par1
3337: where par1.run_id < p_run_id
3338: and par1.rule_id = p_rule_id
3339: and par1.fiscal_year = nvl(v_fiscal_year, par1.fiscal_year)
3340: and par1.quarter = nvl(v_quarter_num, par1.quarter)
3602: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_src_sunk_cost:'||to_char(v_src_sunk_cost) );
3603: pa_debug.write_file('create_target_txns: ' || 'LOG', 'v_tgt_sunk_cost:'||to_char(v_tgt_sunk_cost) );
3604: END IF;
3605: x_curr_alloc_amount := nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0) ;
3606: UPDATE pa_alloc_runs
3607: SET total_pool_amount = nvl(p_pool_amount,0)
3608: , allocated_amount = nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0)
3609: -- , Missing_source_proj_amt = v_src_sunk_cost -- commented as part of the bug 13949738
3610: , Missing_target_proj_amt = v_tgt_sunk_cost
3635: , p_pool_amount IN NUMBER
3636: , p_allocated_amount IN NUMBER ) IS
3637: CURSOR C_offset_det IS
3638: SELECT line_num, project_id, task_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3639: FROM pa_alloc_run_source_det
3640: WHERE run_id=p_run_id
3641: GROUP BY line_num, project_id, task_id;
3642: CURSOR c_offset_proj_sum IS
3643: SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3640: WHERE run_id=p_run_id
3641: GROUP BY line_num, project_id, task_id;
3642: CURSOR c_offset_proj_sum IS
3643: SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3644: FROM pa_alloc_run_source_det
3645: WHERE run_id = p_run_id
3646: GROUP BY project_id ;
3647: CURSOR c_pool_amount IS
3648: SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
3645: WHERE run_id = p_run_id
3646: GROUP BY project_id ;
3647: CURSOR c_pool_amount IS
3648: SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
3649: FROM pa_alloc_run_source_det
3650: WHERE run_id = p_run_id;
3651: v_project_id NUMBER ;
3652: v_task_id NUMBER ;
3653: v_proj_prev_offset_amt NUMBER ;
3656: CURSOR C_prev_offset_task_CE (v_project_id IN NUMBER
3657: , v_task_id IN NUMBER) is
3658: SELECT nvl(sum(nvl(current_allocation,0)),0)
3659: from pa_alloc_txn_details pat
3660: ,pa_alloc_runs par
3661: where pat.run_id = par.run_id
3662: and par.fiscal_year = nvl(p_fiscal_year, par.fiscal_year)
3663: and par.quarter = nvl(p_quarter_num,par.quarter)
3664: and par.period_num = nvl(p_period_num , par.period_num)
3675: , a.project_id project_id
3676: , a.task_id task_id
3677: , nvl(a.Total_allocation,0) Total_allocation
3678: FROM pa_alloc_txn_details a,
3679: pa_alloc_runs ar
3680: WHERE a.run_id = ar.run_id
3681: AND a.rule_id = p_rule_id
3682: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
3683: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
3691: AND b.transaction_type = 'O'
3692: AND b.run_id = p_run_id )
3693: AND a.run_id =( SELECT max(c.run_id)
3694: FROM pa_alloc_txn_details c,
3695: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
3696: WHERE c.project_id = a.project_id
3697: AND c.task_id = a.task_id
3698: AND c.transaction_type = 'O'
3699: AND c.run_id = c_ar.run_id
4172: IF P_DEBUG_MODE = 'Y' THEN
4173: pa_debug.write_file('create_offset_txns: ' || 'LOG','v_off_sunk_cost =' ||to_char(v_off_sunk_cost) );
4174: END IF;
4175: END IF ;
4176: update pa_alloc_runs
4177: set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
4178: TOTAL_OFFSETTED_AMOUNT = v_sum_tot_offsets
4179: where run_id = p_run_id ;
4180: pa_debug.reset_err_stack;
4304: ) IS
4305: BEGIN
4306: pa_debug.set_err_stack('insert_alloc_runs');
4307: If x_run_id is NULL then
4308: Select pa_alloc_runs_s.nextval
4309: Into x_run_id
4310: From dual;
4311: End If;
4312: INSERT INTO pa_alloc_runs_all (
4308: Select pa_alloc_runs_s.nextval
4309: Into x_run_id
4310: From dual;
4311: End If;
4312: INSERT INTO pa_alloc_runs_all (
4313: RUN_ID
4314: , RULE_ID
4315: , RUN_PERIOD
4316: , EXPND_ITEM_DATE
4807: p_res_list_member_id IN NUMBER,
4808: p_resource_percent IN NUMBER)
4809: IS
4810: BEGIN
4811: insert into pa_alloc_run_resource_det (
4812: rule_id,
4813: run_id,
4814: member_type,
4815: resource_list_member_id,
4833: WHEN OTHERS THEN
4834: RAISE;
4835: END;
4836: -- Bug3149853 .Changes done to enhance performance .
4837: -- Created procedure to perform BULK INSERT into pa_alloc_run_resource_det.
4838: Procedure bulk_ins_alloc_run_res(p_run_id IN NUMBER,
4839: p_rule_id IN NUMBER,
4840: p_member_type IN VARCHAR2,
4841: p_res_list_member_id_tab IN pa_plsql_datatypes.IdTabTyp,
4842: p_resource_percent IN NUMBER)
4843: IS
4844: BEGIN
4845: FORALL i IN p_res_list_member_id_tab.first..p_res_list_member_id_tab.last
4846: insert into pa_alloc_run_resource_det (
4847: rule_id,
4848: run_id,
4849: member_type,
4850: resource_list_member_id,
5394: RAISE ;
5395: END get_amttype_start_date ;
5396: -- ==========================================================================
5397: /* PROCEDURE : insert_alloc_basis_resource
5398: Purpose : To insert data into pa_alloc_run_basis_det table for each resource
5399: for each task which has some data available in summarization.
5400: Separate inserts are written for each type of amt_type
5401: (FYTD,qtd,itd and ptd).
5402: Created : 16-JAN-02 Manokuma
5419: )
5420: IS
5421: cursor c_projects is
5422: select distinct part.project_id project_id
5423: from pa_alloc_run_targets part,
5424: pa_resource_list_assignments prla
5425: where part.project_id = prla.project_id
5426: and prla.resource_list_id = p_resource_list_id
5427: and prla.resource_list_accumulated_flag = 'Y'
5428: and part.run_id = p_run_id
5429: and Nvl(p_resource_struct_type,'RL') = 'RL'
5430: Union All
5431: select distinct part.project_id project_id
5432: from pa_alloc_run_targets part,
5433: pa_rbs_prj_assignments prpa
5434: where part.project_id = prpa.project_id
5435: and prpa.rbs_header_id = p_resource_list_id
5436: and prpa.rbs_version_id = p_rbs_version_id
5460: IF P_DEBUG_MODE = 'Y' THEN
5461: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
5462: END IF;
5463: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------- {
5464: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5465: RUN_ID
5466: , RULE_ID
5467: , LINE_NUM
5468: , PROJECT_ID
5502: ,G_last_update_date
5503: ,G_last_updated_by
5504: ,G_last_update_login
5505: from ----Bug Fix: 3634912 :Changed the order of the tables
5506: pa_alloc_run_targets part,
5507: pa_alloc_run_resource_det parr,
5508: pa_resource_accum_details prad,
5509: pa_txn_accum pta
5510: where pta.txn_accum_id = prad.txn_accum_id
5503: ,G_last_updated_by
5504: ,G_last_update_login
5505: from ----Bug Fix: 3634912 :Changed the order of the tables
5506: pa_alloc_run_targets part,
5507: pa_alloc_run_resource_det parr,
5508: pa_resource_accum_details prad,
5509: pa_txn_accum pta
5510: where pta.txn_accum_id = prad.txn_accum_id
5511: and prad.Resource_list_member_id = parr.resource_list_member_id
5546: ,G_last_update_date
5547: ,G_last_updated_by
5548: ,G_last_update_login);
5549: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5550: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5551: RUN_ID
5552: , RULE_ID
5553: , LINE_NUM
5554: , PROJECT_ID
5587: ,G_created_by
5588: ,G_last_update_date
5589: ,G_last_updated_by
5590: ,G_last_update_login
5591: from pa_alloc_run_resource_det parr,
5592: pa_alloc_run_targets part,
5593: PA_ALLOC_TXN_ACCUM_RBS_V pta
5594: where pta.Rbs_Element_Id = parr.resource_list_member_id
5595: and pta.Project_id = part.project_id
5588: ,G_last_update_date
5589: ,G_last_updated_by
5590: ,G_last_update_login
5591: from pa_alloc_run_resource_det parr,
5592: pa_alloc_run_targets part,
5593: PA_ALLOC_TXN_ACCUM_RBS_V pta
5594: where pta.Rbs_Element_Id = parr.resource_list_member_id
5595: and pta.Project_id = part.project_id
5596: and pta.task_id = part.task_id
5646: IF P_DEBUG_MODE = 'Y' THEN
5647: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5648: END IF;
5649: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------{
5650: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5651: RUN_ID
5652: , RULE_ID
5653: , LINE_NUM
5654: , PROJECT_ID
5687: ,G_last_update_date
5688: ,G_last_updated_by
5689: ,G_last_update_login
5690: from --Bug Fix: 3634912 : Changed the order of the tables.
5691: pa_alloc_run_targets part,
5692: pa_alloc_run_resource_det parr,
5693: pa_resource_accum_details prad,
5694: pa_txn_accum pta
5695: where pta.txn_accum_id = prad.txn_accum_id
5688: ,G_last_updated_by
5689: ,G_last_update_login
5690: from --Bug Fix: 3634912 : Changed the order of the tables.
5691: pa_alloc_run_targets part,
5692: pa_alloc_run_resource_det parr,
5693: pa_resource_accum_details prad,
5694: pa_txn_accum pta
5695: where pta.txn_accum_id = prad.txn_accum_id
5696: and prad.Resource_list_member_id = parr.resource_list_member_id
5714: ,G_last_update_date
5715: ,G_last_updated_by
5716: ,G_last_update_login);
5717: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5718: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5719: RUN_ID
5720: , RULE_ID
5721: , LINE_NUM
5722: , PROJECT_ID
5753: ,G_created_by
5754: ,G_last_update_date
5755: ,G_last_updated_by
5756: ,G_last_update_login
5757: from pa_alloc_run_resource_det parr,
5758: pa_alloc_run_targets part,
5759: PA_ALLOC_TXN_ACCUM_RBS_V pta
5760: where pta.Rbs_Element_ID = parr.resource_list_member_id
5761: and pta.Project_id = part.project_id
5754: ,G_last_update_date
5755: ,G_last_updated_by
5756: ,G_last_update_login
5757: from pa_alloc_run_resource_det parr,
5758: pa_alloc_run_targets part,
5759: PA_ALLOC_TXN_ACCUM_RBS_V pta
5760: where pta.Rbs_Element_ID = parr.resource_list_member_id
5761: and pta.Project_id = part.project_id
5762: and pta.task_id = part.task_id
5781: ,G_last_update_login);
5782: End If; ---------------- }
5783: ELSE /* p_run_period_type = 'GL' */
5784: If Nvl(p_resource_struct_type,'RL') = 'RL' Then --------------{
5785: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5786: RUN_ID
5787: , RULE_ID
5788: , LINE_NUM
5789: , PROJECT_ID
5822: ,G_last_update_date
5823: ,G_last_updated_by
5824: ,G_last_update_login
5825: from --Bug Fix: 3634912 : Changed the order of tables
5826: pa_alloc_run_targets part,
5827: pa_alloc_run_resource_det parr,
5828: pa_resource_accum_details prad,
5829: pa_txn_accum pta
5830: where pta.txn_accum_id = prad.txn_accum_id
5823: ,G_last_updated_by
5824: ,G_last_update_login
5825: from --Bug Fix: 3634912 : Changed the order of tables
5826: pa_alloc_run_targets part,
5827: pa_alloc_run_resource_det parr,
5828: pa_resource_accum_details prad,
5829: pa_txn_accum pta
5830: where pta.txn_accum_id = prad.txn_accum_id
5831: and prad.Resource_list_member_id = parr.resource_list_member_id
5853: ,G_last_update_date
5854: ,G_last_updated_by
5855: ,G_last_update_login);
5856: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5857: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5858: RUN_ID
5859: , RULE_ID
5860: , LINE_NUM
5861: , PROJECT_ID
5892: ,G_created_by
5893: ,G_last_update_date
5894: ,G_last_updated_by
5895: ,G_last_update_login
5896: from pa_alloc_run_resource_det parr,
5897: pa_alloc_run_targets part,
5898: PA_ALLOC_TXN_ACCUM_RBS_V pta
5899: where pta.Rbs_Element_ID = parr.resource_list_member_id
5900: and pta.Project_id = part.project_id
5893: ,G_last_update_date
5894: ,G_last_updated_by
5895: ,G_last_update_login
5896: from pa_alloc_run_resource_det parr,
5897: pa_alloc_run_targets part,
5898: PA_ALLOC_TXN_ACCUM_RBS_V pta
5899: where pta.Rbs_Element_ID = parr.resource_list_member_id
5900: and pta.Project_id = part.project_id
5901: and pta.task_id = part.task_id
5947: IF P_DEBUG_MODE = 'Y' THEN
5948: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5949: END IF;
5950: If Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------------{
5951: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5952: RUN_ID
5953: , RULE_ID
5954: , LINE_NUM
5955: , PROJECT_ID
5989: ,G_last_update_date
5990: ,G_last_updated_by
5991: ,G_last_update_login
5992: from -- Bug Fix: 3634912 : Changed the order of tables.
5993: pa_alloc_run_targets part,
5994: pa_alloc_run_resource_det parr,
5995: pa_resource_accum_details prad,
5996: pa_txn_accum pta
5997: where pta.txn_accum_id = prad.txn_accum_id
5990: ,G_last_updated_by
5991: ,G_last_update_login
5992: from -- Bug Fix: 3634912 : Changed the order of tables.
5993: pa_alloc_run_targets part,
5994: pa_alloc_run_resource_det parr,
5995: pa_resource_accum_details prad,
5996: pa_txn_accum pta
5997: where pta.txn_accum_id = prad.txn_accum_id
5998: and prad.Resource_list_member_id = parr.resource_list_member_id
6023: ,G_last_update_date
6024: ,G_last_updated_by
6025: ,G_last_update_login);
6026: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6027: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
6028: RUN_ID
6029: , RULE_ID
6030: , LINE_NUM
6031: , PROJECT_ID
6063: ,G_created_by
6064: ,G_last_update_date
6065: ,G_last_updated_by
6066: ,G_last_update_login
6067: from pa_alloc_run_resource_det parr,
6068: pa_alloc_run_targets part,
6069: PA_ALLOC_TXN_ACCUM_RBS_V pta
6070: where pta.Rbs_Element_Id = parr.resource_list_member_id
6071: and pta.Project_id = part.project_id
6064: ,G_last_update_date
6065: ,G_last_updated_by
6066: ,G_last_update_login
6067: from pa_alloc_run_resource_det parr,
6068: pa_alloc_run_targets part,
6069: PA_ALLOC_TXN_ACCUM_RBS_V pta
6070: where pta.Rbs_Element_Id = parr.resource_list_member_id
6071: and pta.Project_id = part.project_id
6072: and pta.task_id = part.task_id
6118: RAISE ;
6119: END insert_alloc_basis_resource;
6120: -- ==========================================================================
6121: /* PROCEDURE : insert_alloc_source_resource
6122: Purpose : To insert data into pa_alloc_run_source_det table for each resource
6123: for each task which has some data available in summarization.
6124: Separate inserts are written for each type of amt_type
6125: (FYTD,qtd,itd and ptd).
6126: Created : 16-JAN-02 Manokuma
6143: )
6144: IS
6145: cursor c_projects is
6146: select distinct pars.project_id project_id
6147: from pa_alloc_run_sources pars,
6148: pa_resource_list_assignments prla
6149: where pars.project_id = prla.project_id
6150: and prla.resource_list_id = p_resource_list_id
6151: and prla.resource_list_accumulated_flag = 'Y'
6152: and pars.run_id = p_run_id
6153: and NVL(p_resource_struct_type,'RL') = 'RL'
6154: UNION All
6155: select distinct pars.project_id project_id
6156: from pa_alloc_run_sources pars,
6157: pa_rbs_prj_assignments prpa
6158: where pars.project_id = prpa.project_id
6159: and prpa.rbs_header_id = p_resource_list_id
6160: and prpa.rbs_version_id = p_rbs_version_id
6199: IF P_DEBUG_MODE = 'Y' THEN
6200: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
6201: END IF;
6202: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------------{
6203: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6204: RUN_ID
6205: , RULE_ID
6206: , LINE_NUM
6207: , PROJECT_ID
6256: ,G_last_update_date
6257: ,G_last_updated_by
6258: ,G_last_update_login
6259: from --Bug Fix: 3634912 : Changed the order of the tables.
6260: pa_alloc_run_sources pars,
6261: pa_alloc_run_resource_det parr,
6262: pa_resource_accum_details prad,
6263: pa_txn_accum pta
6264: where pta.txn_accum_id = prad.txn_accum_id
6257: ,G_last_updated_by
6258: ,G_last_update_login
6259: from --Bug Fix: 3634912 : Changed the order of the tables.
6260: pa_alloc_run_sources pars,
6261: pa_alloc_run_resource_det parr,
6262: pa_resource_accum_details prad,
6263: pa_txn_accum pta
6264: where pta.txn_accum_id = prad.txn_accum_id
6265: and prad.Resource_list_member_id = parr.resource_list_member_id
6302: ,G_last_updated_by
6303: ,G_last_update_login);
6304: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6305: /* FP.M : Allocation Impact */
6306: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6307: RUN_ID
6308: , RULE_ID
6309: , LINE_NUM
6310: , PROJECT_ID
6358: ,G_created_by
6359: ,G_last_update_date
6360: ,G_last_updated_by
6361: ,G_last_update_login
6362: from pa_alloc_run_resource_det parr,
6363: pa_alloc_run_sources pars,
6364: PA_ALLOC_TXN_ACCUM_RBS_V pta
6365: where pta.Rbs_Element_Id = parr.resource_list_member_id
6366: and pta.Project_id = pars.project_id
6359: ,G_last_update_date
6360: ,G_last_updated_by
6361: ,G_last_update_login
6362: from pa_alloc_run_resource_det parr,
6363: pa_alloc_run_sources pars,
6364: PA_ALLOC_TXN_ACCUM_RBS_V pta
6365: where pta.Rbs_Element_Id = parr.resource_list_member_id
6366: and pta.Project_id = pars.project_id
6367: and pta.task_id = pars.task_id
6417: IF P_DEBUG_MODE = 'Y' THEN
6418: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6419: END IF;
6420: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ------------{
6421: INSERT INTO PA_ALLOC_RUN_source_DET (
6422: RUN_ID
6423: , RULE_ID
6424: , LINE_NUM
6425: , PROJECT_ID
6472: ,G_last_update_date
6473: ,G_last_updated_by
6474: ,G_last_update_login
6475: from -- Bug Fix: 3634912 :Changed the order of the tables.
6476: pa_alloc_run_sources pars,
6477: pa_alloc_run_resource_det parr,
6478: pa_resource_accum_details prad,
6479: pa_txn_accum pta
6480: where pta.txn_accum_id = prad.txn_accum_id
6473: ,G_last_updated_by
6474: ,G_last_update_login
6475: from -- Bug Fix: 3634912 :Changed the order of the tables.
6476: pa_alloc_run_sources pars,
6477: pa_alloc_run_resource_det parr,
6478: pa_resource_accum_details prad,
6479: pa_txn_accum pta
6480: where pta.txn_accum_id = prad.txn_accum_id
6481: and prad.Resource_list_member_id = parr.resource_list_member_id
6499: ,G_last_update_date
6500: ,G_last_updated_by
6501: ,G_last_update_login);
6502: ElsiF Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6503: INSERT INTO PA_ALLOC_RUN_source_DET (
6504: RUN_ID
6505: , RULE_ID
6506: , LINE_NUM
6507: , PROJECT_ID
6552: ,G_created_by
6553: ,G_last_update_date
6554: ,G_last_updated_by
6555: ,G_last_update_login
6556: from pa_alloc_run_resource_det parr,
6557: pa_alloc_run_sources pars,
6558: PA_ALLOC_TXN_ACCUM_RBS_V pta
6559: where pta.Rbs_Element_ID = parr.resource_list_member_id
6560: and pta.Project_id = pars.project_id
6553: ,G_last_update_date
6554: ,G_last_updated_by
6555: ,G_last_update_login
6556: from pa_alloc_run_resource_det parr,
6557: pa_alloc_run_sources pars,
6558: PA_ALLOC_TXN_ACCUM_RBS_V pta
6559: where pta.Rbs_Element_ID = parr.resource_list_member_id
6560: and pta.Project_id = pars.project_id
6561: and pta.task_id = pars.task_id
6580: ,G_last_update_login);
6581: End If; -------------}
6582: ELSE /* p_run_period_type = 'GL' */
6583: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ----------- {
6584: INSERT INTO PA_ALLOC_RUN_source_DET (
6585: RUN_ID
6586: , RULE_ID
6587: , LINE_NUM
6588: , PROJECT_ID
6635: ,G_last_update_date
6636: ,G_last_updated_by
6637: ,G_last_update_login
6638: from --Bug Fix: 3634912 : Changed the order of the tables.
6639: pa_alloc_run_sources pars,
6640: pa_alloc_run_resource_det parr,
6641: pa_resource_accum_details prad,
6642: pa_txn_accum pta
6643: where pta.txn_accum_id = prad.txn_accum_id
6636: ,G_last_updated_by
6637: ,G_last_update_login
6638: from --Bug Fix: 3634912 : Changed the order of the tables.
6639: pa_alloc_run_sources pars,
6640: pa_alloc_run_resource_det parr,
6641: pa_resource_accum_details prad,
6642: pa_txn_accum pta
6643: where pta.txn_accum_id = prad.txn_accum_id
6644: and prad.Resource_list_member_id = parr.resource_list_member_id
6666: ,G_last_update_date
6667: ,G_last_updated_by
6668: ,G_last_update_login);
6669: ElsIF Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6670: INSERT INTO PA_ALLOC_RUN_source_DET (
6671: RUN_ID
6672: , RULE_ID
6673: , LINE_NUM
6674: , PROJECT_ID
6719: ,G_created_by
6720: ,G_last_update_date
6721: ,G_last_updated_by
6722: ,G_last_update_login
6723: from pa_alloc_run_resource_det parr,
6724: pa_alloc_run_sources pars,
6725: PA_ALLOC_TXN_ACCUM_RBS_V pta
6726: where pta.Rbs_Element_Id = parr.resource_list_member_id
6727: and pta.Project_id = pars.project_id
6720: ,G_last_update_date
6721: ,G_last_updated_by
6722: ,G_last_update_login
6723: from pa_alloc_run_resource_det parr,
6724: pa_alloc_run_sources pars,
6725: PA_ALLOC_TXN_ACCUM_RBS_V pta
6726: where pta.Rbs_Element_Id = parr.resource_list_member_id
6727: and pta.Project_id = pars.project_id
6728: and pta.task_id = pars.task_id
6775: IF P_DEBUG_MODE = 'Y' THEN
6776: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6777: END IF;
6778: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------{
6779: INSERT INTO PA_ALLOC_RUN_source_DET (
6780: RUN_ID
6781: , RULE_ID
6782: , LINE_NUM
6783: , PROJECT_ID
6832: ,G_last_update_date
6833: ,G_last_updated_by
6834: ,G_last_update_login
6835: from --Bug Fix: 3634912 : Changed the order of the tables
6836: pa_alloc_run_sources pars,
6837: pa_alloc_run_resource_det parr,
6838: pa_resource_accum_details prad,
6839: pa_txn_accum pta
6840: where pta.txn_accum_id = prad.txn_accum_id
6833: ,G_last_updated_by
6834: ,G_last_update_login
6835: from --Bug Fix: 3634912 : Changed the order of the tables
6836: pa_alloc_run_sources pars,
6837: pa_alloc_run_resource_det parr,
6838: pa_resource_accum_details prad,
6839: pa_txn_accum pta
6840: where pta.txn_accum_id = prad.txn_accum_id
6841: and prad.Resource_list_member_id = parr.resource_list_member_id
6866: ,G_last_update_date
6867: ,G_last_updated_by
6868: ,G_last_update_login);
6869: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6870: INSERT INTO PA_ALLOC_RUN_source_DET (
6871: RUN_ID
6872: , RULE_ID
6873: , LINE_NUM
6874: , PROJECT_ID
6921: ,G_created_by
6922: ,G_last_update_date
6923: ,G_last_updated_by
6924: ,G_last_update_login
6925: from pa_alloc_run_resource_det parr,
6926: pa_alloc_run_sources pars,
6927: PA_ALLOC_TXN_ACCUM_RBS_V pta
6928: where pta.Rbs_Element_Id = parr.resource_list_member_id
6929: and pta.Project_id = pars.project_id
6922: ,G_last_update_date
6923: ,G_last_updated_by
6924: ,G_last_update_login
6925: from pa_alloc_run_resource_det parr,
6926: pa_alloc_run_sources pars,
6927: PA_ALLOC_TXN_ACCUM_RBS_V pta
6928: where pta.Rbs_Element_Id = parr.resource_list_member_id
6929: and pta.Project_id = pars.project_id
6930: and pta.task_id = pars.task_id
6975: RAISE ;
6976: END insert_alloc_source_resource;
6977: -- ==========================================================================
6978: /* PROCEDURE : insert_budget_basis_resource
6979: Purpose : inserts records into pa_alloc_run_basis_det when budgets are used
6980: for basis and resource lists are used to calculate basis amounts.
6981: Created : 02-feb-01 Manoj.
6982: Modified : 24-JAN-03 Tarun for bug 2757875
6983: 06-Apr-04 vthakkar FP.M : ALlocation Impact
6995: )
6996: IS
6997: cursor c_projects is
6998: select distinct project_id
6999: from pa_alloc_run_targets part
7000: where part.run_id = p_run_id;
7001: v_commit NUMBER := 0;
7002: BEGIN
7003: FOR c_projects_rec IN c_projects LOOP
7001: v_commit NUMBER := 0;
7002: BEGIN
7003: FOR c_projects_rec IN c_projects LOOP
7004: If p_run_period_type = 'PA' then
7005: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7006: RUN_ID
7007: , RULE_ID
7008: , LINE_NUM
7009: , PROJECT_ID
7033: ,G_created_by
7034: ,G_last_update_date
7035: ,G_last_updated_by
7036: ,G_last_update_login
7037: from pa_alloc_run_resource_det parr,
7038: pa_alloc_run_targets part,
7039: /*** pa_base_budget_by_pa_period_v pbpp *** commented bug 2619977 */
7040: pa_base_finplan_by_pa_period_v pfpp /* added bug 2619977 */
7041: where Decode (
7034: ,G_last_update_date
7035: ,G_last_updated_by
7036: ,G_last_update_login
7037: from pa_alloc_run_resource_det parr,
7038: pa_alloc_run_targets part,
7039: /*** pa_base_budget_by_pa_period_v pbpp *** commented bug 2619977 */
7040: pa_base_finplan_by_pa_period_v pfpp /* added bug 2619977 */
7041: where Decode (
7042: Nvl(p_basis_resource_struct_Type,'RL') ,
7066: ,G_last_update_date
7067: ,G_last_updated_by
7068: ,G_last_update_login);
7069: Elsif p_run_period_type = 'GL' then
7070: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7071: RUN_ID
7072: , RULE_ID
7073: , LINE_NUM
7074: , PROJECT_ID
7099: ,G_created_by
7100: ,G_last_update_date
7101: ,G_last_updated_by
7102: ,G_last_update_login
7103: from pa_alloc_run_resource_det parr,
7104: pa_alloc_run_targets part,
7105: /*** pa_base_budget_by_gl_period_v pbpg *** commented bug 2619977 */
7106: /* pa_base_finplan_by_pa_period_v pfpg added bug 2619977 commented bug 2757875 */
7107: pa_base_finplan_by_gl_period_v pfpg /* added bug 2757875 */
7100: ,G_last_update_date
7101: ,G_last_updated_by
7102: ,G_last_update_login
7103: from pa_alloc_run_resource_det parr,
7104: pa_alloc_run_targets part,
7105: /*** pa_base_budget_by_gl_period_v pbpg *** commented bug 2619977 */
7106: /* pa_base_finplan_by_pa_period_v pfpg added bug 2619977 commented bug 2757875 */
7107: pa_base_finplan_by_gl_period_v pfpg /* added bug 2757875 */
7108: where Decode (
7304: , p_last_update_login IN NUMBER)
7305: IS
7306: BEGIN
7307: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7308: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7309: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7310: RUN_ID
7311: , RULE_ID
7312: , LINE_NUM
7305: IS
7306: BEGIN
7307: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7308: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7309: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7310: RUN_ID
7311: , RULE_ID
7312: , LINE_NUM
7313: , PROJECT_ID
7371: Steps: 1. Get the Period Year and Quarter Num values for the given run_period.
7372: 2. If source_resource_lists is not null then
7373: Populate Include_RLM_tbl.
7374: For each resource list member in the Include_RLM_tbl then
7375: Get source amount for each project and task in pa_alloc_run_sources
7376: tables for given rule_id and run_id
7377: Else
7378: For each project and task in pa_alloc_run_targets
7379: find the basis amount at project and task level.
7374: For each resource list member in the Include_RLM_tbl then
7375: Get source amount for each project and task in pa_alloc_run_sources
7376: tables for given rule_id and run_id
7377: Else
7378: For each project and task in pa_alloc_run_targets
7379: find the basis amount at project and task level.
7380: End if.
7381: */
7382: v_period_type VARCHAR2(15) ;
7401: v_pool_percent NUMBER ;
7402: v_net_fixed_amount NUMBER ;
7403: cursor C_run_source_details is
7404: Select line_num, project_id , task_id
7405: from pa_alloc_run_sources
7406: where rule_id = p_rule_id
7407: and run_id = p_run_id
7408: and exclude_flag <> 'Y' ;
7409: cursor C_get_pool_amount is
7407: and run_id = p_run_id
7408: and exclude_flag <> 'Y' ;
7409: cursor C_get_pool_amount is
7410: select nvl(sum(nvl(eligible_amount,0)),0)
7411: from pa_alloc_run_source_det
7412: where run_id = p_run_id
7413: AND (pa_project_stus_utils.is_project_closed(project_id ))= 'N'; -- added as part of the bug 13949738
7414:
7415: -- Start: added as part of the bug 13949738
7413: AND (pa_project_stus_utils.is_project_closed(project_id ))= 'N'; -- added as part of the bug 13949738
7414:
7415: -- Start: added as part of the bug 13949738
7416: cursor C_get_missing_source_amount is
7417: Select * from pa_alloc_run_source_det
7418: where run_id = p_run_id
7419: AND (pa_project_stus_utils.is_project_closed(project_id ))= 'Y';
7420:
7421: CURSOR C_get_previous_run_amount
7425: , p_Project_Id IN NUMBER
7426: , p_Task_Id IN NUMBER
7427: ) IS
7428: SELECT NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
7429: FROM PA_ALLOC_RUN_SOURCE_DET A ,
7430: ( SELECT MAX ( AR.Run_Id ) AS Run_Id
7431: FROM PA_ALLOC_RUNS AR
7432: WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
7433: AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
7427: ) IS
7428: SELECT NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
7429: FROM PA_ALLOC_RUN_SOURCE_DET A ,
7430: ( SELECT MAX ( AR.Run_Id ) AS Run_Id
7431: FROM PA_ALLOC_RUNS AR
7432: WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
7433: AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
7434: AND AR.Period_Num = NVL ( p_Prd_Num , AR.Period_Num )
7435: AND AR.Run_Id < p_Run_Id
7448: RULE_ID ,
7449: LINE_NUM ,
7450: PROJECT_ID ,
7451: TASK_ID
7452: From Pa_Alloc_Run_Sources
7453: Where Rule_Id = P_Rule_Id
7454: And Run_Id = P_Run_Id
7455: And Nvl(Exclude_Flag,'N') = 'N';
7456: BEGIN
7490: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7491: END IF;
7492: /* bug 2211234 - added p_run_id.
7493: - removed v_src_rlm_tab
7494: This procedure will now populate records into pa_alloc_run_resources_det */
7495: populate_RLM_table( p_rule_id, p_run_id,
7496: 'S',
7497: v_resource_list_id ,
7498: p_source_resource_struct_type ,
7501: ) ;
7502: BEGIN
7503: select count(*)
7504: into v_resource_count
7505: from pa_alloc_run_resource_det
7506: where rule_id = p_rule_id
7507: and run_id = p_run_id
7508: and member_type = 'S';
7509: if v_resource_count = 0 then
7510: alloc_errors(p_rule_id, p_run_id, 'S','E', 'PA_AL_NO_INCL_SRC_RESRC',TRUE) ;
7511: end if ;
7512: EXCEPTION
7513: WHEN OTHERS THEN
7514: pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
7515: IF P_DEBUG_MODE = 'Y' THEN
7516: pa_debug.write_file('cal_amounts_from_projects: ' || 'LOG', pa_debug.G_Err_Stage);
7517: END IF;
7518: RAISE;
7574: END IF;
7575: If p_run_amount_type in ( 'FYTD','QTD') then
7576: For l_get_proj in c_get_proj
7577: Loop
7578: Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id,
7579: task_id, creation_date, created_by,
7580: last_update_date,
7581: last_updated_by, last_update_login,
7582: amount, eligible_amount)
7611: /* Commenting out pa_periods for bug 2757875 and using gl_period_statuses instead */
7612: -- pa_periods pp ,
7613: gl_period_statuses gl ,
7614: pa_implementations imp
7615: /* 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 */
7616: where /* Bug 3749469
7617: pars.rule_id = p_rule_id
7618: and pars.run_id = p_run_id
7619: and pars.exclude_flag = 'N'
7644: elsif p_run_amount_type = 'PTD' then
7645: If p_run_period_type = 'GL' then
7646: For l_get_proj in c_get_proj
7647: Loop
7648: Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id, task_id,
7649: creation_date, created_by,
7650: last_update_date,
7651: last_updated_by, last_update_login,
7652: amount, eligible_amount)
7678: 0) * v_pool_percent ) )
7679: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
7680: -- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
7681: -- pa_periods pp
7682: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7683: where /* pars.rule_id = p_rule_id
7684: and pars.run_id = p_run_id
7685: and pars.exclude_flag = 'N'
7686: and pta.project_id = pars.project_id
7703: End Loop;
7704: elsif p_run_period_type = 'PA' then
7705: For l_get_proj in c_get_proj
7706: Loop
7707: Insert into pa_alloc_run_source_det(
7708: rule_id, run_id, line_num, project_id, task_id,
7709: creation_date, created_by, last_update_date,
7710: last_updated_by, last_update_login,
7711: amount, eligible_amount)
7736: 'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
7737: +nvl(pta.i_tot_burdened_cost,0),
7738: 0) * v_pool_percent ) )
7739: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
7740: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7741: where /* pars.rule_id = p_rule_id
7742: and pars.run_id = p_run_id
7743: and pars.exclude_flag = 'N'
7744: and pta.project_id = pars.project_id
7759: end if ;
7760: else
7761: For l_get_proj in c_get_proj
7762: Loop
7763: Insert into pa_alloc_run_source_det(
7764: rule_id, run_id, line_num, project_id, task_id,
7765: creation_date, created_by, last_update_date,
7766: last_updated_by, last_update_login,
7767: amount, eligible_amount)
7793: 0) * v_pool_percent ) )
7794: from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta */
7795: pa_periods pp ,
7796: pa_projects P
7797: /* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
7798: where /* pars.rule_id = p_rule_id
7799: and pars.run_id = p_run_id
7800: and pars.exclude_flag = 'N'
7801: and pars.project_id = p.project_id
7865: END IF;
7866: v_total_missing_cost := v_total_missing_cost + v_missing_amount;
7867: END LOOP;
7868:
7869: UPDATE pa_alloc_runs
7870: SET Missing_source_proj_amt = v_total_missing_cost
7871: WHERE run_id = p_run_id;
7872: -- End: added as part of the bug 13949738
7873:
7953: IF P_DEBUG_MODE = 'Y' THEN
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7959: RUN_ID
7960: , RULE_ID
7961: , LINE_NUM
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7959: RUN_ID
7960: , RULE_ID
7961: , LINE_NUM
7962: , PROJECT_ID
8019: Get the Period Year and Quarter Num values for the given Relative_period.
8020: If basis_resource_lists is not null then
8021: Populate Include_RLM_tbl.
8022: For each resource list member in the Include_RLM_tbl Loop
8023: For Each project and task in pa_alloc_run_targets table Loop
8024: If Basis_category is 'ACTUALS' then
8025: Get the basis amount using get_alloc_amounts( )
8026: Else
8027: Get the basis amount from get_budget_amounts()
8029: End Loop ;
8030: End Loop ;
8031: Else
8032: If basis_category is ACTUALS then
8033: For each project and task in pa_alloc_run_targets
8034: find the basis amount at project and task level from pa_txn_accum
8035: and insert that into pa_alloc_run_basis_det.
8036: Else
8037: For each project and task in pa_alloc_run_targets
8031: Else
8032: If basis_category is ACTUALS then
8033: For each project and task in pa_alloc_run_targets
8034: find the basis amount at project and task level from pa_txn_accum
8035: and insert that into pa_alloc_run_basis_det.
8036: Else
8037: For each project and task in pa_alloc_run_targets
8038: find the basis amount at project and task level from
8039: pa_base_budget_by_pa_period_v
8033: For each project and task in pa_alloc_run_targets
8034: find the basis amount at project and task level from pa_txn_accum
8035: and insert that into pa_alloc_run_basis_det.
8036: Else
8037: For each project and task in pa_alloc_run_targets
8038: find the basis amount at project and task level from
8039: pa_base_budget_by_pa_period_v
8040: and insert that into pa_alloc_run_basis_det.
8041: End if.
8036: Else
8037: For each project and task in pa_alloc_run_targets
8038: find the basis amount at project and task level from
8039: pa_base_budget_by_pa_period_v
8040: and insert that into pa_alloc_run_basis_det.
8041: End if.
8042: End if;
8043: End if
8044: Determine the basis_percent ;
8076: v_status NUMBER := NULL;
8077: v_err_message VARCHAR2(250);
8078: cursor C_run_targets is
8079: Select line_num, project_id , task_id, line_percent
8080: from pa_alloc_run_targets
8081: where rule_id = p_rule_id
8082: and run_id = p_run_id
8083: and exclude_flag <> 'Y' ;
8084: cursor C_tot_basis_amt is
8082: and run_id = p_run_id
8083: and exclude_flag <> 'Y' ;
8084: cursor C_tot_basis_amt is
8085: select nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
8086: from pa_alloc_run_basis_det
8087: where run_id = p_run_id ;
8088: cursor C_line_basis_amt is
8089: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
8090: from pa_alloc_run_basis_det
8086: from pa_alloc_run_basis_det
8087: where run_id = p_run_id ;
8088: cursor C_line_basis_amt is
8089: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
8090: from pa_alloc_run_basis_det
8091: where run_id = p_run_id
8092: group by line_num, line_percent ;
8093: cursor C_tgt_line_pct is
8094: select sum((nvl(line_percent,0)))
8091: where run_id = p_run_id
8092: group by line_num, line_percent ;
8093: cursor C_tgt_line_pct is
8094: select sum((nvl(line_percent,0)))
8095: from pa_alloc_run_basis_det
8096: where run_id = p_run_id ;
8097: cursor c_get_proj is
8098: Select RUN_ID ,
8099: RULE_ID ,
8102: TASK_ID ,
8103: EXCLUDE_FLAG ,
8104: LINE_PERCENT ,
8105: BUDGET_VERSION_ID
8106: From Pa_Alloc_Run_Targets
8107: Where rule_id = p_rule_id
8108: And run_id = p_run_id
8109: And Nvl(exclude_flag,'N') = 'N' ;
8110: BEGIN
8174: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', pa_debug.G_Err_Stage);
8175: END IF;
8176: /* Bug 2211234 - added p_run_id.
8177: - removed v_basis_rlm_tab
8178: this procedure will now insert into pa_alloc_run_resources_det */
8179: populate_RLM_table( p_rule_id,
8180: p_run_id,
8181: 'B',
8182: v_resource_list_id,
8192: is made and bulk inserts are done in new procedure added insert_alloc_basis_resource
8193: These changes are not done for budgets as in budgets the volume of data should not be that high.
8194: */
8195: IF p_basis_category = 'A' THEN
8196: /* Currenly we insert all project/tasks into pa_alloc_run_targets.
8197: These do not get converted into final txns in case there are no basis existing
8198: for these targets in summarization. We want to intelligently delete those data
8199: from pa_alloc_run_targets table which do not contain any basis
8200: amount. */
8195: IF p_basis_category = 'A' THEN
8196: /* Currenly we insert all project/tasks into pa_alloc_run_targets.
8197: These do not get converted into final txns in case there are no basis existing
8198: for these targets in summarization. We want to intelligently delete those data
8199: from pa_alloc_run_targets table which do not contain any basis
8200: amount. */
8201: clean_up_targets_for_actuals(p_run_id => p_run_id
8202: ,p_rule_id => p_rule_id
8203: ,p_amt_type => p_basis_amt_type
8274: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8275: END IF;
8276: For l_get_proj in c_get_proj
8277: Loop
8278: Insert into pa_alloc_run_basis_det (
8279: rule_id, run_id, line_num, project_id, task_id,
8280: line_percent, creation_date, created_by,
8281: last_update_date, last_updated_by,
8282: last_update_login, amount)
8305: /* Commenting out pa_periods and using gl_period_statuses instead for bug 2757875 */
8306: -- pa_periods pp ,
8307: gl_period_statuses gl,
8308: pa_implementations imp
8309: /* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8310: where /* Bug 3749469 part.rule_id = p_rule_id
8311: and part.run_id = p_run_id
8312: and part.exclude_flag = 'N'
8313: and pta.project_id = part.project_id
8340: elsif p_basis_amt_type = 'PTD' then
8341: If p_run_period_type = 'GL' then
8342: For l_get_proj in c_get_proj
8343: Loop
8344: Insert into pa_alloc_run_basis_det (
8345: rule_id, run_id, line_num, project_id, task_id,
8346: line_percent, creation_date, created_by,
8347: last_update_date, last_updated_by,
8348: last_update_login, amount)
8369: 0 )) AMOUNT
8370: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
8371: -- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
8372: -- pa_periods pp
8373: /* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8374: where /* Bug 3749469
8375: part.rule_id = p_rule_id
8376: and part.run_id = p_run_id
8377: and part.exclude_flag = 'N'
8397: End Loop;
8398: Elsif p_run_period_type = 'PA' then
8399: For l_get_proj in c_get_proj
8400: Loop
8401: Insert into pa_alloc_run_basis_det (
8402: rule_id, run_id, line_num, project_id, task_id,
8403: line_percent, creation_date, created_by,
8404: last_update_date, last_updated_by,
8405: last_update_login, amount)
8424: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8425: +nvl(pta.i_tot_quantity,0),
8426: 0 )) AMOUNT
8427: from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
8428: /* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8429: where /* Bug 3749469 part.rule_id = p_rule_id
8430: and part.run_id = p_run_id
8431: and part.exclude_flag = 'N'
8432: and pta.project_id = part.project_id
8449: end if ;
8450: Else -- process for ITD amounts
8451: For l_get_proj in c_get_proj
8452: Loop
8453: Insert into pa_alloc_run_basis_det (
8454: rule_id, run_id, line_num, project_id, task_id,
8455: line_percent, creation_date, created_by,
8456: last_update_date, last_updated_by,
8457: last_update_login, amount)
8477: 'TOT_QUANTITY', nvl(pta.tot_quantity,0)
8478: +nvl(pta.i_tot_quantity,0),
8479: 0 )) AMOUNT
8480: /* Rearranged the tables in the FROM clause and commented out pa_projects_all for bug 2751178 */
8481: from /* pa_alloc_run_targets part, */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
8482: pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
8483: pa_periods pp
8484: -- pa_projects_all p ,
8485: where /* Bug 3749469
8515: IF P_DEBUG_MODE = 'Y' THEN
8516: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
8517: END IF;
8518: if p_basis_amt_type = 'ITD' then
8519: Insert into pa_alloc_run_basis_det(
8520: rule_id, run_id, line_num, project_id, task_id,
8521: line_percent, creation_date, created_by, last_update_date,
8522: last_updated_by, last_update_login, amount)
8523: select part.rule_id, part.run_id, part.line_num,
8534: ))
8535: /*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
8536: from pa_base_finplan_by_pa_period_v pfpp, /* added bug 2619977 */
8537: pa_projects_all p ,
8538: pa_alloc_run_targets part
8539: where part.rule_id = p_rule_id
8540: and part.run_id = p_run_id
8541: and part.exclude_flag = 'N'
8542: and part.project_id = p.project_id
8552: part.line_percent, G_creation_date, G_created_by,
8553: G_last_update_date,
8554: G_last_updated_by, G_last_update_login ;
8555: elsif p_basis_amt_type in ('FYTD','QTD') then /* Added bug 2757875 */
8556: Insert into pa_alloc_run_basis_det(
8557: rule_id, run_id, line_num, project_id, task_id,
8558: line_percent, creation_date, created_by, last_update_date,
8559: last_updated_by, last_update_login, amount)
8560: select part.rule_id, part.run_id, part.line_num,
8571: ))
8572: /*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
8573: /**** from pa_base_finplan_by_pa_period_v pfpp, added bug 2619977. **** Commented bug 2757875*/
8574: from pa_base_finplan_by_gl_period_v pfpp, /* Added bug 2757875 */
8575: pa_alloc_run_targets part
8576: where part.rule_id = p_rule_id
8577: and part.run_id = p_run_id
8578: and part.exclude_flag = 'N'
8579: and pfpp.project_id = part.project_id
8590: G_last_updated_by, G_last_update_login ;
8591: /* Added the following code for bug 2757875 */
8592: else -- process for PTD amounts
8593: If p_run_period_type = 'GL' then
8594: Insert into pa_alloc_run_basis_det(
8595: rule_id, run_id, line_num, project_id, task_id,
8596: line_percent, creation_date, created_by, last_update_date,
8597: last_updated_by, last_update_login, amount)
8598: select part.rule_id, part.run_id, part.line_num,
8607: 'BASE_LABOR_QUANTITY', nvl(pfgp.labor_quantity,0),
8608: 0
8609: ))
8610: from pa_base_finplan_by_gl_period_v pfgp,
8611: pa_alloc_run_targets part
8612: where part.rule_id = p_rule_id
8613: and part.run_id = p_run_id
8614: and part.exclude_flag = 'N'
8615: and pfgp.project_id = part.project_id
8621: part.line_percent, G_creation_date, G_created_by,
8622: G_last_update_date,
8623: G_last_updated_by, G_last_update_login ;
8624: elsif p_run_period_type = 'PA' then
8625: Insert into pa_alloc_run_basis_det(
8626: rule_id, run_id, line_num, project_id, task_id,
8627: line_percent, creation_date, created_by, last_update_date,
8628: last_updated_by, last_update_login, amount)
8629: select part.rule_id, part.run_id, part.line_num,
8638: 'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
8639: 0
8640: ))
8641: from pa_base_finplan_by_pa_period_v pfpp,
8642: pa_alloc_run_targets part
8643: where part.rule_id = p_rule_id
8644: and part.run_id = p_run_id
8645: and part.exclude_flag = 'N'
8646: and pfpp.project_id = part.project_id
8655: end if; -- p_run_period_type
8656: /* Code changes end for bug 2757875 */
8657: End if ;
8658: --- Create Zero amount records for tasks that did not have budgets..
8659: Insert into pa_alloc_run_basis_det(
8660: rule_id, run_id, line_num, project_id, task_id,
8661: line_percent, creation_date, created_by, last_update_date,
8662: last_updated_by, last_update_login, amount)
8663: select part.rule_id, part.run_id, part.line_num,
8665: part.line_percent, G_creation_date,
8666: G_created_by, G_last_update_date,
8667: G_last_updated_by, G_last_update_login ,
8668: 0
8669: from pa_alloc_run_targets part
8670: where part.rule_id = p_rule_id
8671: and part.run_id = p_run_id
8672: and part.exclude_flag = 'N'
8673: and NOT EXISTS ( Select 'Exists'
8670: where part.rule_id = p_rule_id
8671: and part.run_id = p_run_id
8672: and part.exclude_flag = 'N'
8673: and NOT EXISTS ( Select 'Exists'
8674: from pa_alloc_run_basis_det parbd
8675: where parbd.run_id = part.run_id
8676: and parbd.project_id = part.project_id
8677: and parbd.task_id = part.task_id ) ;
8678: End if ; -- basis category
8679: End if ; -- For Resource list
8680: End if ; --- For basis_method
8681: /* added for bug 2182563. For FP insert one row per target with zero amount */
8682: IF p_basis_method = 'FP' then
8683: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8684: with amount = 0. so that the following insert does not insert any duplicate
8685: records into the table if there is a record already existing with amount = 0 */
8686: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8687: WHERE rule_id = p_rule_id
8682: IF p_basis_method = 'FP' then
8683: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8684: with amount = 0. so that the following insert does not insert any duplicate
8685: records into the table if there is a record already existing with amount = 0 */
8686: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8687: WHERE rule_id = p_rule_id
8688: AND run_id = p_run_id
8689: AND nvl(amount,0) = 0;
8690: /* we need to insert rows for a run_id/line_num only if there are no records in
8689: AND nvl(amount,0) = 0;
8690: /* we need to insert rows for a run_id/line_num only if there are no records in
8691: in basis table for that run_id and line number with amount <> 0. Because in this
8692: case the program has function like basis_method = 'FS' for that target line. */
8693: Insert into pa_alloc_run_basis_det (
8694: rule_id, run_id, line_num, project_id, task_id,
8695: line_percent, creation_date, created_by,
8696: last_update_date, last_updated_by,
8697: last_update_login, amount)
8698: select part.rule_id, part.run_id, part.line_num, part.project_id,
8699: part.task_id, part.line_percent, G_creation_date, G_created_by,
8700: G_last_update_date, G_last_updated_by, G_last_update_login ,
8701: 0 AMOUNT
8702: from pa_alloc_run_targets part
8703: where part.rule_id = p_rule_id
8704: and part.run_id = p_run_id
8705: and part.exclude_flag = 'N'
8706: and not exists
8704: and part.run_id = p_run_id
8705: and part.exclude_flag = 'N'
8706: and not exists
8707: (select null
8708: from pa_alloc_run_basis_det parb
8709: where parb.run_id = part.run_id
8710: and parb.line_num = part.line_num
8711: and parb.amount <> 0);
8712: END IF;
8739: END IF;
8740: End if;
8741: Close C_tot_basis_amt ;
8742: if nvl(v_tot_basis_amt,0) <> 0 then /* added for 1900331 */
8743: UPDATE pa_alloc_run_basis_det
8744: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_tot_basis_amt)
8745: WHERE run_id = p_run_id ;
8746: else
8747: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8747: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8748: p_basis_method := 'S';
8749: /* bug 1900331 prorate equally */
8750: /* commented for 2182563
8751: UPDATE pa_alloc_run_basis_det
8752: SET basis_percent = 100/v_tot_basis_rec
8753: WHERE run_id = p_run_id ;
8754: */
8755: end if;
8768: END IF;
8769: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_LINE_BASIS_AMT_IS_ZERO',TRUE) ;
8770: End if;
8771: if nvl(v_line_basis_amt,0) <> 0 then /* added for bug 1900331 */
8772: UPDATE pa_alloc_run_basis_det
8773: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_line_basis_amt)
8774: WHERE run_id = p_run_id
8775: AND line_num = v_line_num ;
8776: else
8774: WHERE run_id = p_run_id
8775: AND line_num = v_line_num ;
8776: else
8777: /* 1900331 - prorate equaly within the same target line */
8778: UPDATE pa_alloc_run_basis_det
8779: SET basis_percent = 100/v_line_count
8780: WHERE run_id = p_run_id
8781: AND line_num = v_line_num ;
8782: end if;
8866: ***END get_budget_amounts ;
8867: ***/
8868: -- ==========================================================================
8869: /* PROCEDURE : clean_up_targets_for_actuals
8870: Purpose : Deletes records from pa_alloc_run_targets which do not contain
8871: any Basis amount. The addition of this procedure does not
8872: modify the existing flow. This procedure has been added to
8873: delete unnecessary records from pa_alloc_run_targets for
8874: performance reasons. So, removing this procedure will not impact
8869: /* PROCEDURE : clean_up_targets_for_actuals
8870: Purpose : Deletes records from pa_alloc_run_targets which do not contain
8871: any Basis amount. The addition of this procedure does not
8872: modify the existing flow. This procedure has been added to
8873: delete unnecessary records from pa_alloc_run_targets for
8874: performance reasons. So, removing this procedure will not impact
8875: the existing flow of Allocations.
8876: Created : 18-feb-02 Praveen for Bug #2222280
8877: */
8898: IF P_DEBUG_MODE = 'Y' THEN
8899: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8900: END IF;
8901: IF p_amt_type in ( 'FYTD', 'QTD') THEN
8902: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for FYTD or QTD';
8903: IF P_DEBUG_MODE = 'Y' THEN
8904: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
8905: END IF;
8906: v_commit_count := 0;
8906: v_commit_count := 0;
8907: IF p_basis_method = 'P' THEN
8908: SAVEPOINT delete_unwanted_targets;
8909: /*For Bug 5403833*/
8910: DELETE FROM pa_alloc_run_targets part
8911: WHERE part.run_id = p_run_id
8912: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8913: AND not exists
8914: (select null
8926: into v_do_commit
8927: from dual
8928: where exists
8929: (select null
8930: from pa_alloc_run_targets part
8931: where part.run_id = p_run_id
8932: and part.exclude_flag = 'N'
8933: and rownum = 1);
8934: /* We will commit if any records are still there in targets table.
8953: elsif p_basis_method = 'FP' then
8954: FOR c_target_lines_rec in c_target_lines LOOP
8955: SAVEPOINT delete_unwanted_targets;
8956: /* For Bug 5403833 */
8957: DELETE FROM pa_alloc_run_targets part
8958: WHERE part.run_id = p_run_id
8959: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
8960: AND part.line_num = c_target_lines_rec.line_num
8961: AND not exists
8975: into v_do_commit
8976: from dual
8977: where exists
8978: (select null
8979: from pa_alloc_run_targets part
8980: where part.run_id = p_run_id
8981: and part.exclude_flag = 'N'
8982: and line_num = c_target_lines_rec.line_num
8983: and rownum = 1);
8999: END;
9000: end loop;
9001: END IF;
9002: ELSIF p_amt_type = 'PTD' THEN
9003: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for PTD';
9004: IF P_DEBUG_MODE = 'Y' THEN
9005: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9006: END IF;
9007: v_commit_count := 0;
9010: IF P_DEBUG_MODE = 'Y' THEN
9011: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9012: END IF;
9013: SAVEPOINT delete_unwanted_targets;
9014: DELETE FROM pa_alloc_run_targets part
9015: WHERE part.run_id = p_run_id
9016: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9017: AND not exists
9018: (select null
9030: into v_do_commit
9031: from dual
9032: where exists
9033: (select null
9034: from pa_alloc_run_targets part
9035: where part.run_id = p_run_id
9036: and part.exclude_flag = 'N'
9037: and rownum = 1);
9038: /* We will commit if any records are still there in targets table.
9061: SAVEPOINT delete_unwanted_targets;
9062:
9063: /*For Bug 5403833*/
9064:
9065: DELETE FROM pa_alloc_run_targets part
9066: WHERE part.run_id = p_run_id
9067: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9068: AND not exists
9069: (select null
9081: into v_do_commit
9082: from dual
9083: where exists
9084: (select null
9085: from pa_alloc_run_targets part
9086: where part.run_id = p_run_id
9087: and part.exclude_flag = 'N'
9088: and rownum = 1);
9089: /* We will commit if any records are still there in targets table.
9112: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleting for Period_Type = PA');
9113: END IF;
9114: FOR c_target_lines_rec in c_target_lines LOOP
9115: SAVEPOINT delete_unwanted_targets;
9116: DELETE FROM pa_alloc_run_targets part
9117: WHERE part.run_id = p_run_id
9118: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9119: AND part.line_num = c_target_lines_rec.line_num
9120: AND not exists
9133: into v_do_commit
9134: from dual
9135: where exists
9136: (select null
9137: from pa_alloc_run_targets part
9138: where part.run_id = p_run_id
9139: and part.exclude_flag = 'N'
9140: and line_num = c_target_lines_rec.line_num
9141: and rownum = 1);
9162: END IF;
9163: FOR c_target_lines_rec in c_target_lines LOOP
9164: SAVEPOINT delete_unwanted_targets;
9165: /*For Bug 5403833 */
9166: DELETE FROM pa_alloc_run_targets part
9167: WHERE part.run_id = p_run_id
9168: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9169: AND part.line_num = c_target_lines_rec.line_num
9170: AND not exists
9183: into v_do_commit
9184: from dual
9185: where exists
9186: (select null
9187: from pa_alloc_run_targets part
9188: where part.run_id = p_run_id
9189: and part.exclude_flag = 'N'
9190: and line_num = c_target_lines_rec.line_num
9191: and rownum = 1);
9211: ELSIF p_amt_type = 'ITD' THEN
9212: /* For ITD let's consider all periods in pa_periods table rather than
9213: doing this delete for each project id in the targets table. Hence do
9214: not use pa_periods table in this case. */
9215: pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
9216: IF P_DEBUG_MODE = 'Y' THEN
9217: pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', pa_debug.G_err_stage);
9218: END IF;
9219: v_commit_count := 0;
9218: END IF;
9219: v_commit_count := 0;
9220: IF p_basis_method = 'P' THEN
9221: SAVEPOINT delete_unwanted_targets;
9222: DELETE FROM pa_alloc_run_targets part
9223: WHERE part.run_id = p_run_id
9224: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9225: AND not exists
9226: (select null
9237: into v_do_commit
9238: from dual
9239: where exists
9240: (select null
9241: from pa_alloc_run_targets part
9242: where part.run_id = p_run_id
9243: and part.exclude_flag = 'N'
9244: and rownum = 1);
9245: /* We will commit if any records are still there in targets table.
9263: END;
9264: elsif p_basis_method = 'FP' then
9265: FOR c_target_lines_rec in c_target_lines LOOP
9266: SAVEPOINT delete_unwanted_targets;
9267: DELETE FROM pa_alloc_run_targets part
9268: WHERE part.run_id = p_run_id
9269: AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
9270: AND part.line_num = c_target_lines_rec.line_num
9271: AND not exists
9280: into v_do_commit
9281: from dual
9282: where exists
9283: (select null
9284: from pa_alloc_run_targets part
9285: where part.run_id = p_run_id
9286: and part.exclude_flag = 'N'
9287: and line_num = c_target_lines_rec.line_num
9288: and rownum = 1);
9321: --Added this If and the else part for capital project changes
9322: IF p_rule_id <> -1 THEN
9323: If pa_debug.Acquire_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9324: G_fatal_err_found:= TRUE;
9325: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9326: G_created_by, G_last_update_date,
9327: G_last_updated_by, G_last_update_login,
9328: 'R', 'E', NULL, NULL, 'PA_AL_CANT_ACQUIRE_LOCK');
9329: end if;
9329: end if;
9330: ELSE
9331: If pa_debug.Acquire_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9332: G_fatal_err_found:= TRUE;
9333: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9334: G_created_by, G_last_update_date,
9335: G_last_updated_by, G_last_update_login,
9336: 'R', 'E', NULL, NULL, 'PA_CINT_CANT_ACQUIRE_LOCK');
9337: END IF;
9353: END IF;
9354: IF p_rule_id <> -1 THEN
9355: If pa_debug.Release_user_lock( 'PA_AL_'||to_char(p_rule_id)) <> 0 then
9356: G_fatal_err_found:= TRUE;
9357: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9358: G_created_by, G_last_update_date,
9359: G_last_updated_by, G_last_update_login,
9360: 'R', 'E', NULL, NULL, 'PA_AL_LOCK_RELEASE_FAILED');
9361: end if;
9361: end if;
9362: ELSE
9363: If pa_debug.Release_user_lock( 'PA_CINT_'||to_char(p_run_id)) <> 0 then
9364: G_fatal_err_found:= TRUE;
9365: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9366: G_created_by, G_last_update_date,
9367: G_last_updated_by, G_last_update_login,
9368: 'R', 'E', NULL, NULL, 'PA_CINT_LOCK_RELEASE_FAILED');
9369: End If;
9390: , target_exp_type
9391: , offset_exp_type
9392: , trunc(expnd_item_date) expnd_item_date
9393: , offset_method
9394: from pa_alloc_runs
9395: where run_id = p_run_id ;
9396: cursor C_org_id is
9397: select org_id
9398: from pa_implementations ;
9463: OPEN C_run;
9464: FETCH C_run INTO run_rec ;
9465: IF C_run%NOTFOUND THEN
9466: G_fatal_err_found := TRUE;
9467: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9468: G_created_by, G_last_update_date,
9469: G_last_updated_by, G_last_update_login,
9470: 'R', 'E', NULL, NULL, 'PA_AL_RUN_NOT_EXISTS');
9471: x_errbuf := 'PA_AL_RUN_NOT_EXISTS' ;
9475: v_offset_expnd_org := pa_utils.GetOrgName(run_rec.offset_exp_org_id ) ;
9476: If (nvl(p_rule_id,-99) <> -1) AND --Added this condition for capital project changes
9477: v_target_expnd_org is NULL THEN
9478: G_fatal_err_found := TRUE;
9479: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9480: G_created_by, G_last_update_date,
9481: G_last_updated_by, G_last_update_login,
9482: 'R', 'E', NULL, NULL, 'PA_AL_INVALID_TARGET_EXP_ORG');
9483: x_errbuf := 'PA_AL_INVALID_TARGET_EXP_ORG' ;
9485: If (nvl(p_rule_id,-99) <> -1) AND --Added this condition for capital project changes
9486: (run_rec.offset_method <> 'N') AND
9487: (v_offset_expnd_org is NULL) then
9488: G_fatal_err_found := TRUE;
9489: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9490: G_created_by, G_last_update_date,
9491: G_last_updated_by, G_last_update_login,
9492: 'R', 'E', NULL, NULL, 'PA_AL_INVALID_OFFSET_EXP_ORG');
9493: x_errbuf := 'PA_AL_INVALID_OFFSET_EXP_ORG' ;
9701:
9702: begin
9703: for r1 in c_alloc_txn_err(p_run_id) loop
9704:
9705: pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
9706: G_created_by, G_last_update_date,
9707: G_last_updated_by, G_last_update_login,
9708: 'T', 'E', NULL, NULL, r1.rejection_code);
9709: end loop;
9738: Delete from pa_expenditure_groups
9739: where expenditure_group in ( v_tgt_exp_group, v_off_exp_group) ;
9740: else
9741: --Modifed this for capital project changes.
9742: update pa_alloc_runs
9743: set target_exp_group = v_tgt_exp_group ,
9744: offset_exp_group = decode(p_rule_id,
9745: -1,null,
9746: decode(run_rec.offset_method,'N',NULL,v_off_exp_group) )
9776: IS
9777: Cursor C_run is
9778: Select target_exp_group
9779: , offset_exp_group
9780: from pa_alloc_runs
9781: where run_id = p_run_id ;
9782: run_rec C_run%ROWTYPE ;
9783: v_num_reversed NUMBER ;
9784: v_num_rejected NUMBER ;
9858: End if;
9859: End If;
9860: -- Update the alloc runs table with the reversal info --
9861: If nvl(v_return_code,'0') = '0' then
9862: update pa_alloc_runs
9863: set run_status = 'RV'
9864: ,reversal_date = trunc(sysdate)
9865: ,rev_target_exp_group = p_tgt_exp_group
9866: ,rev_offset_exp_group = p_off_exp_group
9914: /*Code Changes for Bug No.2984871 end */
9915: End Loop;
9916: --- Delete Transactions from source_details
9917: While 1=1 Loop /* Bug 2176096 */
9918: Delete from pa_alloc_run_source_det
9919: where run_id = p_run_id and rownum < 10001;
9920: /* Commented for Bug 2984871 Commit; */
9921: If Sql%Rowcount = 0 THen
9922: Exit;
9926: /*Code Changes for Bug No.2984871 end */
9927: End Loop;
9928: --- Delete Transactions from basis_details
9929: While 1=1 Loop /* Bug 2176096 */
9930: Delete from pa_alloc_run_basis_det
9931: where run_id = p_run_id and rownum < 10001;
9932: /* Commented for Bug 2984871 Commit; */
9933: If Sql%Rowcount = 0 THen
9934: Exit;
9936: /*Code Changes for Bug No.2984871 start */
9937: Commit;
9938: /*Code Changes for Bug No.2984871 end */
9939: End Loop;
9940: -- Delete Transactions from pa_alloc_run_sources
9941: While 1=1 Loop /* Bug 2176096 */
9942: Delete from pa_alloc_run_sources
9943: where run_id = p_run_id and rownum < 10001;
9944: /* Commented for Bug 2984871 Commit; */
9938: /*Code Changes for Bug No.2984871 end */
9939: End Loop;
9940: -- Delete Transactions from pa_alloc_run_sources
9941: While 1=1 Loop /* Bug 2176096 */
9942: Delete from pa_alloc_run_sources
9943: where run_id = p_run_id and rownum < 10001;
9944: /* Commented for Bug 2984871 Commit; */
9945: If Sql%Rowcount = 0 THen
9946: Exit;
9948: /*Code Changes for Bug No.2984871 start */
9949: Commit;
9950: /*Code Changes for Bug No.2984871 end */
9951: End Loop;
9952: -- Delete Transactions from pa_alloc_run_targets
9953: While 1=1 Loop /* Bug 2176096 */
9954: Delete from pa_alloc_run_targets
9955: where run_id = p_run_id and rownum < 10001;
9956: /* Commented for Bug 2984871 Commit; */
9950: /*Code Changes for Bug No.2984871 end */
9951: End Loop;
9952: -- Delete Transactions from pa_alloc_run_targets
9953: While 1=1 Loop /* Bug 2176096 */
9954: Delete from pa_alloc_run_targets
9955: where run_id = p_run_id and rownum < 10001;
9956: /* Commented for Bug 2984871 Commit; */
9957: If Sql%Rowcount = 0 THen
9958: Exit;
9960: /*Code Changes for Bug No.2984871 start */
9961: Commit;
9962: /*Code Changes for Bug No.2984871 end */
9963: End Loop;
9964: -- Delete Transactions from pa_alloc_run_resource_det
9965: While 1=1 Loop /* Bug 2182563 */
9966: Delete from pa_alloc_run_resource_det
9967: where run_id = p_run_id and rownum < 10001;
9968: /* Commented for Bug 2984871 Commit; */
9962: /*Code Changes for Bug No.2984871 end */
9963: End Loop;
9964: -- Delete Transactions from pa_alloc_run_resource_det
9965: While 1=1 Loop /* Bug 2182563 */
9966: Delete from pa_alloc_run_resource_det
9967: where run_id = p_run_id and rownum < 10001;
9968: /* Commented for Bug 2984871 Commit; */
9969: If Sql%Rowcount = 0 Then
9970: Exit;
9978: where run_id = p_run_id ;
9979: -- Delete Transactions from pa_alloc_exceptions
9980: Delete from pa_alloc_exceptions
9981: where run_id = p_run_id ;
9982: -- Delete Transactions from pa_alloc_runs
9983: Delete from pa_alloc_runs
9984: where run_id = p_run_id ;
9985: ---- Release Lock on the rule_name ---------------------
9986: unlock_rule(p_rule_id , p_run_id) ;
9979: -- Delete Transactions from pa_alloc_exceptions
9980: Delete from pa_alloc_exceptions
9981: where run_id = p_run_id ;
9982: -- Delete Transactions from pa_alloc_runs
9983: Delete from pa_alloc_runs
9984: where run_id = p_run_id ;
9985: ---- Release Lock on the rule_name ---------------------
9986: unlock_rule(p_rule_id , p_run_id) ;
9987: pa_debug.reset_err_stack ;
9999: )
10000: IS
10001: CURSOR get_run_id
10002: IS
10003: select run_id from pa_alloc_runs_all
10004: where rule_id = p_rule_id
10005: and run_status = 'DL';
10006: l_run_id NUMBER;
10007: v_debug_mode VARCHAR2(2);
10036: END IF;
10037: --Delete all the txn source details when this api is called in
10038: --the context of Capitalized Interest
10039: IF p_rule_id = -1 THEN
10040: pa_alloc_run.delete_cint_source_dets
10041: ( p_run_id => l_run_id
10042: ,x_return_status => l_return_status
10043: ,x_msg_data => l_msg_data
10044: ,x_msg_count => l_msg_count);
10064: /*Code Changes for Bug No.2984871 end */
10065: End Loop;
10066: --- Delete Transactions from source_details
10067: While 1=1 Loop /* Bug 2176096 */
10068: Delete from pa_alloc_run_source_det
10069: where run_id = l_run_id and rownum < 10001;
10070: /* Commented for Bug 2984871 Commit; */
10071: If Sql%Rowcount = 0 THen
10072: Exit;
10076: /*Code Changes for Bug No.2984871 end */
10077: End Loop;
10078: --- Delete Transactions from basis_details
10079: While 1=1 Loop /* Bug 2176096 */
10080: Delete from pa_alloc_run_basis_det
10081: where run_id = l_run_id and rownum < 10001;
10082: /* Commented for Bug 2984871 Commit; */
10083: If Sql%Rowcount = 0 THen
10084: Exit;
10086: /*Code Changes for Bug No.2984871 start */
10087: Commit;
10088: /*Code Changes for Bug No.2984871 end */
10089: End Loop;
10090: -- Delete Transactions from pa_alloc_run_sources
10091: While 1=1 Loop /* Bug 2176096 */
10092: Delete from pa_alloc_run_sources
10093: where run_id = l_run_id and rownum < 10001;
10094: /* Commented for Bug 2984871 Commit; */
10088: /*Code Changes for Bug No.2984871 end */
10089: End Loop;
10090: -- Delete Transactions from pa_alloc_run_sources
10091: While 1=1 Loop /* Bug 2176096 */
10092: Delete from pa_alloc_run_sources
10093: where run_id = l_run_id and rownum < 10001;
10094: /* Commented for Bug 2984871 Commit; */
10095: If Sql%Rowcount = 0 THen
10096: Exit;
10098: /*Code Changes for Bug No.2984871 start */
10099: Commit;
10100: /*Code Changes for Bug No.2984871 end */
10101: End Loop;
10102: -- Delete Transactions from pa_alloc_run_targets
10103: While 1=1 Loop /* Bug 2176096 */
10104: Delete from pa_alloc_run_targets
10105: where run_id = l_run_id and rownum < 10001;
10106: /* Commented for Bug 2984871 Commit; */
10100: /*Code Changes for Bug No.2984871 end */
10101: End Loop;
10102: -- Delete Transactions from pa_alloc_run_targets
10103: While 1=1 Loop /* Bug 2176096 */
10104: Delete from pa_alloc_run_targets
10105: where run_id = l_run_id and rownum < 10001;
10106: /* Commented for Bug 2984871 Commit; */
10107: If Sql%Rowcount = 0 THen
10108: Exit;
10110: /*Code Changes for Bug No.2984871 start */
10111: Commit;
10112: /*Code Changes for Bug No.2984871 end */
10113: End Loop;
10114: -- Delete Transactions from pa_alloc_run_resource_det
10115: While 1=1 Loop /* Bug 2182563 */
10116: Delete from pa_alloc_run_resource_det
10117: where run_id = l_run_id and rownum < 10001;
10118: /* Commented for Bug 2984871 Commit; */
10112: /*Code Changes for Bug No.2984871 end */
10113: End Loop;
10114: -- Delete Transactions from pa_alloc_run_resource_det
10115: While 1=1 Loop /* Bug 2182563 */
10116: Delete from pa_alloc_run_resource_det
10117: where run_id = l_run_id and rownum < 10001;
10118: /* Commented for Bug 2984871 Commit; */
10119: If Sql%Rowcount = 0 Then
10120: Exit;
10128: where run_id = l_run_id ;
10129: -- Delete Transactions from pa_alloc_exceptions
10130: Delete from pa_alloc_exceptions
10131: where run_id = l_run_id ;
10132: -- Delete Transactions from pa_alloc_runs
10133: Delete from pa_alloc_runs
10134: where run_id = l_run_id ;
10135: ---- Release Lock on the rule_name ---------------------
10136: --unlock_rule(p_rule_id , l_run_id) ;
10129: -- Delete Transactions from pa_alloc_exceptions
10130: Delete from pa_alloc_exceptions
10131: where run_id = l_run_id ;
10132: -- Delete Transactions from pa_alloc_runs
10133: Delete from pa_alloc_runs
10134: where run_id = l_run_id ;
10135: ---- Release Lock on the rule_name ---------------------
10136: --unlock_rule(p_rule_id , l_run_id) ;
10137: END LOOP;
10333: --This procedure is called from PA_CAP_INT_PVT. Generate_cap_interest when release button is pressed on the
10334: --Allocation form(when form is accessed in the context of capitalized interest) or when auto release flag is
10335: --passed Y
10336: PROCEDURE release_capint_txns
10337: ( p_run_id IN pa_alloc_runs_all.run_id%TYPE
10338: ,x_return_status OUT NOCOPY VARCHAR2
10339: ,x_msg_count OUT NOCOPY NUMBER
10340: ,x_msg_data OUT NOCOPY VARCHAR2
10341: )
10361: pa_debug.G_err_stage := 'About to call release_alloc_txns';
10362: pa_debug.write_file('LOG',pa_debug.g_err_stage);
10363: END IF;
10364: --Initialise the who columns
10365: pa_alloc_run.init_who_cols;
10366: --Call the api that releases the allocation run
10367: pa_alloc_run.release_alloc_txns
10368: ( p_rule_id => -1
10369: ,p_run_id => p_run_id
10363: END IF;
10364: --Initialise the who columns
10365: pa_alloc_run.init_who_cols;
10366: --Call the api that releases the allocation run
10367: pa_alloc_run.release_alloc_txns
10368: ( p_rule_id => -1
10369: ,p_run_id => p_run_id
10370: ,x_retcode => x_return_status
10371: ,x_errbuf => x_msg_data);
10388: /* bug fix: 3123539 based on the return status update the run staus and avoid
10389: * setting the concurrent process to raise error
10390: */
10391: IF x_return_status = 'S' Then
10392: UPDATE pa_alloc_runs
10393: SET run_status = 'RS'
10394: ,release_request_id = l_rel_request_id
10395: ,release_request_date =sysdate
10396: WHERE run_id = p_run_id;
10395: ,release_request_date =sysdate
10396: WHERE run_id = p_run_id;
10397: Else
10398: -- Update the status to release failure
10399: UPDATE pa_alloc_runs
10400: SET run_status = 'RF'
10401: ,release_request_id = null
10402: ,release_request_date =sysdate
10403: WHERE run_id = p_run_id;
10424: pa_debug.G_err_stage := 'UPDATING RUN STATUS AS FAILURE';
10425: pa_debug.write_file('cap_int: ' || 'LOG', pa_debug.G_err_stage);
10426: END IF;
10427: -- Update the status to release failure
10428: UPDATE pa_alloc_runs
10429: SET run_status = 'RF'
10430: ,release_request_id = l_rel_request_id
10431: ,release_request_date =sysdate
10432: WHERE run_id = p_run_id;
10440: --This procedure deletes the source details for each capital interest transaction. This procedure will
10441: --be called from delete_alloc_run api when the DELETE button is pressed to delete a capital interest
10442: --batch
10443: PROCEDURE delete_cint_source_dets
10444: ( p_run_id IN pa_alloc_runs_all.run_id%TYPE
10445: ,x_return_status OUT NOCOPY VARCHAR2
10446: ,x_msg_count OUT NOCOPY NUMBER
10447: ,x_msg_data OUT NOCOPY VARCHAR2
10448: )
10494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10495: x_msg_count := 1;
10496: x_msg_data := SQLERRM;
10497: FND_MSG_PUB.add_exc_msg
10498: ( p_pkg_name => 'pa_alloc_run'
10499: ,p_procedure_name => 'delete_cint_source_dets'
10500: ,p_error_text => x_msg_data);
10501: IF l_debug_mode = 'Y' THEN
10502: pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
10504: END IF;
10505: pa_debug.reset_curr_function;
10506: RAISE;
10507: END delete_cint_source_dets;
10508: END PA_ALLOC_run;