1 PACKAGE PA_FP_CALC_UTILS AS
2 --$Header: PAFPCL1S.pls 120.7 2007/02/06 09:49:11 dthakker ship $
3
4
5 PROCEDURE populate_spreadCalc_Tmp (
6 p_budget_version_id IN Number
7 ,p_budget_version_type IN Varchar2
8 ,p_calling_module IN Varchar2
9 ,p_source_context IN Varchar2
10 ,p_time_phased_code IN Varchar2
11 ,p_apply_progress_flag IN Varchar2 DEFAULT 'N'
12 ,p_rollup_required_flag IN Varchar2 DEFAULT 'Y'
13 ,p_refresh_rates_flag IN Varchar2 DEFAULT 'N'
14 ,p_refresh_conv_rates_flag IN Varchar2 DEFAULT 'N'
15 ,p_mass_adjust_flag IN Varchar2 DEFAULT 'N'
16 ,p_time_phase_changed_flag IN Varchar2 DEFAULT 'N' /* Bug fix:4613444 */
17 ,p_wp_cost_changed_flag IN Varchar2 DEFAULT 'N' /* Bug fix:5309529*/
18 ,x_resource_assignment_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
19 ,x_delete_budget_lines_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
20 ,x_spread_amts_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
21 ,x_txn_currency_code_tab IN OUT NOCOPY SYSTEM.pa_varchar2_15_tbl_type
22 ,x_txn_currency_override_tab IN OUT NOCOPY SYSTEM.pa_varchar2_15_tbl_type
23 ,x_total_qty_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
24 ,x_addl_qty_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
25 ,x_total_raw_cost_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
26 ,x_addl_raw_cost_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
27 ,x_total_burdened_cost_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
28 ,x_addl_burdened_cost_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
29 ,x_total_revenue_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
30 ,x_addl_revenue_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
31 ,x_raw_cost_rate_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
32 ,x_rw_cost_rate_override_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
33 ,x_b_cost_rate_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
34 ,x_b_cost_rate_override_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
35 ,x_bill_rate_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
36 ,x_bill_rate_override_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
37 ,x_line_start_date_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
38 ,x_line_end_date_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
39 ,x_apply_progress_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
40 ,x_spread_curve_id_old_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
41 ,x_spread_curve_id_new_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
42 ,x_sp_fixed_date_old_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
43 ,x_sp_fixed_date_new_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
44 ,x_plan_start_date_old_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
45 ,x_plan_start_date_new_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
46 ,x_plan_end_date_old_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
47 ,x_plan_end_date_new_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
48 ,x_re_spread_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
49 ,x_sp_curve_change_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
50 ,x_plan_dates_change_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
51 ,x_spfix_date_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
52 ,x_mfc_cost_change_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
53 ,x_mfc_cost_type_id_old_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
54 ,x_mfc_cost_type_id_new_tab IN OUT NOCOPY SYSTEM.pa_num_tbl_type
55 ,x_rlm_id_change_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
56 ,x_plan_sdate_shrunk_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
57 ,x_plan_edate_shrunk_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
58 ,x_mfc_cost_refresh_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
59 ,x_ra_in_multi_cur_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
60 ,x_quantity_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
61 ,x_raw_cost_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
62 ,x_cost_rate_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
63 ,x_burden_cost_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
64 ,x_burden_rate_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
65 ,x_rev_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
66 ,x_bill_rate_changed_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
67 ,x_multcur_plan_start_date_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
68 ,x_multcur_plan_end_date_tab IN OUT NOCOPY SYSTEM.pa_date_tbl_type
69 ,x_fp_task_billable_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
70 /*5664280:mrup3 merge */
71 ,x_cost_rt_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
72 ,x_burd_rt_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
73 ,x_bill_rt_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
74 ,x_Qty_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
75 ,x_Rw_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
76 ,x_Br_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
77 ,x_Rv_miss_num_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
78 ,x_rev_only_entry_flag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
79 /* bug fix:5726773 */
80 ,x_neg_Qty_Changflag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
81 ,x_neg_Raw_Changflag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
82 ,x_neg_Burd_Changflag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
83 ,x_neg_rev_Changflag_tab IN OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type
84 ,x_return_status OUT NOCOPY VARCHAR2
85 ,x_msg_data OUT NOCOPY varchar2
86 );
87
88 PROCEDURE cache_rates(
89 p_budget_verson_id IN Number
90 ,p_apply_progress_flag IN Varchar2
91 ,p_source_context IN pa_fp_res_assignments_tmp.source_context%TYPE
92 ,x_return_status OUT NOCOPY varchar2
93 ,x_msg_data OUT NOCOPY varchar2 ) ;
94
95 /* This API copies the override rates, currency conversion attributes, DFF attributes from cache to rollup tmp lines
96 * so the after spread, the old values are retained
97 */
98 PROCEDURE copy_BlAttributes(
99 p_budget_verson_id IN Number
100 ,p_source_context IN Varchar2
101 ,p_calling_module IN Varchar2
102 ,p_apply_progress_flag IN Varchar2
103 ,x_return_status OUT NOCOPY varchar2
104 ,x_msg_data OUT NOCOPY varchar2
105 ) ;
106
107 /* Bug fix: 4184159 The following API will update the budget lines in bulk. This API uses oracle 9i feature of SQL%BULKEXCEPTION
108 * during bulk update fails due to dup_val_on_index exception, the process the rejected rows.
109 * Earlier the api was updating the budget line inside a loop for each row. this was causing the performance bottle neck
110 */
111 PROCEDURE BLK_update_budget_lines
112 (p_budget_version_id IN NUMBER
113 ,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'-- Added for Bug#5395732
114 ,x_return_status OUT NOCOPY VARCHAR2
115 ,x_msg_count OUT NOCOPY NUMBER
116 ,x_msg_data OUT NOCOPY VARCHAR2);
117
118 /* Throw an error If budget lines having zero qty and actuals, these lines corrupted budget lines
119 * getting created through the AMG apis and budget generation process. Just abort the process
120 */
121 PROCEDURE Check_ZeroQty_Bls
122 ( p_budget_version_id IN NUMBER
123 ,x_return_status OUT NOCOPY VARCHAR2
124 );
125
126 --Bug No.: 4224464. Added the signature for this procedure update_dffcols
127 PROCEDURE update_dffcols(
128 p_budget_verson_id IN Number
129 ,p_source_context IN Varchar2
130 ,p_calling_module IN Varchar2
131 ,p_apply_progress_flag IN Varchar2
132 ,x_return_status OUT NOCOPY varchar2
133 ,x_msg_count OUT NOCOPY NUMBER
134 ,x_msg_data OUT NOCOPY varchar2
135 ) ;
136
137 /*Bug:4272944: Added new procedure to insert zero qty budget lines from pa_fp_spread_calc_tmp1 to
138 *pa_budget_lines. This fix is done specific to Funding of Autobase line is failing.
139 *donot populate or use pa_fp_spread_calc_tmp1 table for any other purpose.
140 *Note: Calling API may populate this table only for AMG/MSP/Autobaseline purpose.
141 */
142 PROCEDURE InsertFunding_ReqdLines
143 ( p_budget_verson_id IN Number
144 ,p_source_context IN Varchar2
145 ,p_calling_module IN Varchar2
146 ,p_apply_progress_flag IN Varchar2
147 ,p_approved_rev_flag IN Varchar2
148 ,p_autoBaseLine_flag IN Varchar2
149 ,x_return_status OUT NOCOPY varchar2
150 ) ;
151
152 END PA_FP_CALC_UTILS;