[Home] [Help]
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;
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
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);
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)
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
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
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)
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)
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
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
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
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:
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 ;
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
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);
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: )
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: )