1 PACKAGE PA_FP_CALC_PLAN_PKG AUTHID CURRENT_USER AS
2 --$Header: PAFPCALS.pls 120.12.12020000.3 2013/04/19 10:44:14 krkondur ship $
3
4 -- Package level global variables
5 g_spread_from_date DATE;
6 G_populate_mrc_tab_flag VARCHAR2(10) := 'N';
7
8 --For Bug 6722414
9 g_from_etc_client_extn_flag VARCHAR2(1);
10 -- Package variables for PJI reporting APIs
11 /* global system tables declared for calling reporting api at the end */
12 g_rep_budget_line_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
13 g_rep_res_assignment_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
17 g_rep_txn_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
14 g_rep_start_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
15 g_rep_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
16 g_rep_period_name_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
18 g_rep_quantity_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
19 g_rep_txn_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
20 g_rep_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
21 g_rep_txn_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
22 g_rep_project_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
23 g_rep_project_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
24 g_rep_project_burden_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
25 g_rep_project_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
26 g_rep_projfunc_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
27 g_rep_projfunc_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
28 g_rep_projfunc_burden_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
29 g_rep_projfunc_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
30 /*bug fix:5116157 */
31 g_rep_line_mode_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
32 g_rep_rate_base_flag_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
33
34 /* Initialize reporting tbls */
35 PROCEDURE Init_reporting_Tbls ;
36
37 /* This API calls the PJI reporting procedures to rollup of the budget lines
38 * to the task and project level
39 */
40 PROCEDURE Add_Toreporting_Tabls
41 (p_calling_module IN Varchar2 Default 'CALCULATE_API'
42 ,p_activity_code IN Varchar2 Default 'UPDATE'
43 ,p_budget_version_id IN Number
44 ,p_budget_line_id IN Number
45 ,p_resource_assignment_id IN Number
46 ,p_start_date IN Date
47 ,p_end_date IN Date
48 ,p_period_name IN Varchar2
49 ,p_txn_currency_code IN Varchar2
50 ,p_quantity IN Number
51 ,p_txn_raw_cost IN Number
52 ,p_txn_burdened_cost IN Number
53 ,p_txn_revenue IN Number
54 ,p_project_currency_code IN Varchar2
55 ,p_project_raw_cost IN Number
56 ,p_project_burdened_cost IN Number
57 ,p_project_revenue IN Number
58 ,p_projfunc_currency_code IN Varchar2
59 ,p_projfunc_raw_cost IN Number
60 ,p_projfunc_burdened_cost IN Number
61 ,p_projfunc_revenue IN Number
62 ,p_rep_line_mode IN Varchar2
63 ,x_msg_data OUT NOCOPY Varchar2
64 ,x_return_status OUT NOCOPY Varchar2
65 );
66
67 PROCEDURE delete_budget_lines (p_budget_version_id IN pa_budget_lines.budget_version_id%type
68 ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
69 ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
70 ,p_line_start_date IN pa_budget_lines.start_date%TYPE
71 ,p_line_end_date IN pa_budget_lines.end_date%TYPE
72 ,p_source_context IN varchar2
73 ,x_return_status OUT NOCOPY VARCHAR2
74 ,x_msg_count OUT NOCOPY NUMBER
75 ,x_msg_data OUT NOCOPY VARCHAR2
76 ,x_num_rowsdeleted OUT NOCOPY NUMBER
77 );
78
79 PROCEDURE chk_req_rate_api_inputs ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
80 ,p_budget_version_type IN pa_budget_versions.version_type%TYPE
81 ,p_person_id IN pa_resource_assignments.person_id%TYPE
82 ,p_job_id IN pa_resource_assignments.job_id%TYPE
83 ,p_resource_class IN pa_resource_assignments.resource_class_code%TYPE
84 ,p_rate_based_flag IN pa_resource_assignments.rate_based_flag%TYPE
85 ,p_uom IN pa_resource_assignments.unit_of_measure%TYPE
86 ,p_quantity IN pa_budget_lines.quantity%TYPE
87 ,p_item_date IN pa_budget_lines.start_date%TYPE
88 ,p_non_labor_resource IN pa_resource_assignments.non_labor_resource%TYPE
89 ,p_expenditure_org_id IN pa_resource_assignments.rate_expenditure_org_id%TYPE
90 ,p_nlr_organization_id IN pa_resource_assignments.organization_id%TYPE
91 ,p_cost_override_rate IN pa_fp_res_assignments_tmp.rw_cost_rate_override%TYPE
95 ,p_raw_revenue IN pa_fp_res_assignments_tmp.txn_revenue%TYPE
92 ,p_revenue_override_rate IN pa_fp_res_assignments_tmp.bill_rate_override%TYPE
93 ,p_raw_cost IN pa_fp_res_assignments_tmp.txn_raw_cost%TYPE
94 ,p_burden_cost IN pa_fp_res_assignments_tmp.txn_burdened_cost%TYPE
96 ,p_override_currency_code IN pa_fp_res_assignments_tmp.txn_currency_code%TYPE
97 ,x_return_status OUT NOCOPY VARCHAR2
98 ,x_msg_count OUT NOCOPY NUMBER
99 ,x_msg_data OUT NOCOPY VARCHAR2
100 );
101
102
103 PROCEDURE populate_rollup_tmp ( p_budget_version_id IN NUMBER
104 ,x_return_status OUT NOCOPY VARCHAR2
105 ,x_msg_count OUT NOCOPY NUMBER
106 ,x_msg_data OUT NOCOPY VARCHAR2);
107
108 -- gboomina for AAI Requirement - Start
109 -- Modifying this procedure to take calling mode as a parameter
110 -- so that this api can be called from Collect actuals flow
111 PROCEDURE rollup_pf_pfc_to_ra ( p_budget_version_id IN NUMBER
112 ,p_calling_module IN VARCHAR2 DEFAULT 'CALCULATE_API'
113 ,x_return_status OUT NOCOPY VARCHAR2
114 ,x_msg_count OUT NOCOPY NUMBER
115 ,x_msg_data OUT NOCOPY VARCHAR2);
116 -- gboomina for AAI Requirement - End
117
118 PROCEDURE rollup_pf_pfc_to_bv ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
119 ,x_return_status OUT NOCOPY VARCHAR2
120 ,x_msg_count OUT NOCOPY NUMBER
121 ,x_msg_data OUT NOCOPY VARCHAR2);
122
123 PROCEDURE convert_ra_txn_currency
124 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
125 ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
126 ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
127 ,p_budget_line_id IN pa_budget_lines.budget_line_id%TYPE
128 ,p_txn_raw_cost IN pa_fp_rollup_tmp.txn_raw_cost%TYPE
129 ,p_txn_burdened_cost IN pa_fp_rollup_tmp.txn_burdened_cost%TYPE
130 ,p_txn_revenue IN pa_fp_rollup_tmp.txn_revenue%TYPE
131 ,x_projfunc_currency_code OUT NOCOPY pa_fp_rollup_tmp.projfunc_currency_code%TYPE
132 ,x_projfunc_raw_cost OUT NOCOPY pa_fp_rollup_tmp.projfunc_raw_cost%TYPE
133 ,x_projfunc_burdened_cost OUT NOCOPY pa_fp_rollup_tmp.projfunc_burdened_cost%TYPE
134 ,x_projfunc_revenue OUT NOCOPY pa_fp_rollup_tmp.projfunc_revenue%TYPE
135 ,x_projfunc_rejection_code OUT NOCOPY pa_fp_rollup_tmp.pfc_cur_conv_rejection_code%TYPE
136 ,x_project_currency_code OUT NOCOPY pa_fp_rollup_tmp.project_currency_code%TYPE
137 ,x_project_raw_cost OUT NOCOPY pa_fp_rollup_tmp.project_raw_cost%TYPE
138 ,x_project_burdened_cost OUT NOCOPY pa_fp_rollup_tmp.project_burdened_cost%TYPE
139 ,x_project_revenue OUT NOCOPY pa_fp_rollup_tmp.project_revenue%TYPE
140 ,x_project_rejection_code OUT NOCOPY pa_fp_rollup_tmp.pc_cur_conv_rejection_code%TYPE
141 ,x_return_status OUT NOCOPY VARCHAR2
142 ,x_msg_count OUT NOCOPY NUMBER
143 ,x_msg_data OUT NOCOPY VARCHAR2);
144
145
146 PROCEDURE calculate ( p_project_id IN pa_projects_all.project_id%TYPE
147 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
148 ,p_refresh_rates_flag IN VARCHAR2 := 'N'
149 ,p_refresh_conv_rates_flag IN VARCHAR2 := 'N'
150 ,p_spread_required_flag IN VARCHAR2 := 'Y'
151 ,p_conv_rates_required_flag IN VARCHAR2 := 'Y'
152 ,p_rollup_required_flag IN VARCHAR2 := 'Y'
153 ,p_mass_adjust_flag IN VARCHAR2 := 'N'
154 ,p_apply_progress_flag IN VARCHAR2 := 'N'
155 ,p_wp_cost_changed_flag IN VARCHAR2 := 'N'
156 ,p_time_phase_changed_flag IN VARCHAR2 := 'N'
157 ,p_quantity_adj_pct IN NUMBER := NULL
158 ,p_cost_rate_adj_pct IN NUMBER := NULL
159 ,p_burdened_rate_adj_pct IN NUMBER := NULL
160 ,p_bill_rate_adj_pct IN NUMBER := NULL
161 ,p_raw_cost_adj_pct IN NUMBER := NULL
162 ,p_burden_cost_adj_pct IN NUMBER := NULL
163 ,p_revenue_adj_pct IN NUMBER := NULL
164 ,p_source_context IN pa_fp_res_assignments_tmp.source_context%TYPE
165 ,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'
166 ,p_activity_code IN VARCHAR2 DEFAULT 'CALCULATE'
167 ,p_resource_assignment_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
168 ,p_delete_budget_lines_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
169 ,p_spread_amts_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
173 ,p_addl_qty_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
170 ,p_txn_currency_code_tab IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
171 ,p_txn_currency_override_tab IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
172 ,p_total_qty_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
174 ,p_total_raw_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
175 ,p_addl_raw_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
176 ,p_total_burdened_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
177 ,p_addl_burdened_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
178 ,p_total_revenue_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
179 ,p_addl_revenue_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
180 ,p_raw_cost_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
181 ,p_rw_cost_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
182 ,p_b_cost_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
183 ,p_b_cost_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
184 ,p_bill_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
185 ,p_bill_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
186 ,p_line_start_date_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
187 ,p_line_end_date_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
188 ,p_mfc_cost_type_id_old_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
189 ,p_mfc_cost_type_id_new_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
190 ,p_spread_curve_id_old_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
191 ,p_spread_curve_id_new_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
192 ,p_sp_fixed_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
193 ,p_sp_fixed_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
194 ,p_plan_start_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
195 ,p_plan_start_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
196 ,p_plan_end_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
197 ,p_plan_end_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
198 ,p_re_spread_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
199 ,p_rlm_id_change_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
200 ,p_del_spread_calc_tmp1_flg IN VARCHAR2 := 'Y' /* Bug: 4309290.Added the parameter to identify if
201 PA_FP_SPREAD_CALC_TMP1 table */
202 ,p_fp_task_billable_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type() /* default 'D' */
203 ,p_clientExtn_api_call_flag IN VARCHAR2 DEFAULT 'Y'
204 ,p_raTxn_rollup_api_call_flag IN VARCHAR2 DEFAULT 'Y' /* Bug fix:4900436 */
205 ,x_return_status OUT NOCOPY VARCHAR2
206 ,x_msg_count OUT NOCOPY NUMBER
207 ,x_msg_data OUT VARCHAR2);
208
209 /* Compare the In params value with the existsing budget line values
210 * and populate the changed flags. Based on these flag apply the
211 * precedence and set the addl variables to pass it to spread api
212 */
213 PROCEDURE Compare_With_BdgtLine_Values
214 (p_resource_ass_id IN Number
215 ,p_txn_currency_code IN Varchar2
216 ,p_line_start_date IN Date
217 ,p_line_end_date IN Date
218 ,p_bdgt_version_type IN Varchar2
219 ,p_rate_based_flag IN Varchar2
220 ,p_apply_progress_flag IN Varchar2
221 ,p_resAttribute_changed_flag IN Varchar2
222 /* Bug fix:4263265 Added these param to avoid deriving rate overrides */
223 ,p_qty_changed_flag IN Varchar2
224 ,p_raw_cost_changed_flag IN Varchar2
225 ,p_rw_cost_rate_changed_flag IN Varchar2
226 ,p_burden_cost_changed_flag IN Varchar2
227 ,p_b_cost_rate_changed_flag IN Varchar2
228 ,p_rev_changed_flag IN Varchar2
229 ,p_bill_rate_changed_flag IN Varchar2
230 ,p_revenue_only_entry_flag IN Varchar2
231 /* end of bug fix 4263265 */
232 ,p_txn_currency_code_ovr IN OUT NOCOPY Varchar2
233 ,p_txn_plan_quantity IN OUT NOCOPY Number
234 ,p_txn_raw_cost IN OUT NOCOPY Number
235 ,p_txn_raw_cost_rate IN OUT NOCOPY Number
236 ,p_txn_rw_cost_rate_override IN OUT NOCOPY Number
237 ,p_txn_burdened_cost IN OUT NOCOPY Number
238 ,p_txn_b_cost_rate IN OUT NOCOPY Number
242 ,p_txn_bill_rate_override IN OUT NOCOPY Number
239 ,p_txn_b_cost_rate_override IN OUT NOCOPY Number
240 ,p_txn_revenue IN OUT NOCOPY Number
241 ,p_txn_bill_rate IN OUT NOCOPY Number
243 ,x_qty_changed_flag OUT NOCOPY Varchar2
244 ,x_raw_cost_changed_flag OUT NOCOPY Varchar2
245 ,x_rw_cost_rate_changed_flag OUT NOCOPY Varchar2
246 ,x_burden_cost_changed_flag OUT NOCOPY Varchar2
247 ,x_b_cost_rate_changed_flag OUT NOCOPY Varchar2
248 ,x_rev_changed_flag OUT NOCOPY Varchar2
249 ,x_bill_rate_changed_flag OUT NOCOPY Varchar2
250 ,x_bill_rt_ovr_changed_flag OUT NOCOPY Varchar2
251 ,x_txn_revenue_addl OUT NOCOPY Number
252 ,x_txn_raw_cost_addl OUT NOCOPY Number
253 ,x_txn_plan_quantity_addl OUT NOCOPY Number
254 ,x_txn_burdened_cost_addl OUT NOCOPY Number
255 ,x_init_raw_cost OUT NOCOPY Number
256 ,x_init_burdened_cost OUT NOCOPY Number
257 ,x_init_revenue OUT NOCOPY Number
258 ,x_init_quantity OUT NOCOPY Number
259 ,x_bl_raw_cost OUT NOCOPY Number
260 ,x_bl_burdened_cost OUT NOCOPY Number
261 ,x_bl_revenue OUT NOCOPY Number
262 ,x_bl_quantity OUT NOCOPY Number
263 );
264
265
266 /* This API calls the main wrapper RATE api and converts the amounts from txn to txn currency if
267 * cost rate and revenue rate currencies are different
268 * Note: Before calling this API, pa_fp_rollup_tmp should be populated
269 */
270 PROCEDURE Get_Res_RATEs
271 (p_calling_module IN varchar2
272 ,p_activity_code IN varchar2
273 ,p_budget_version_id IN Number
274 ,p_mass_adjust_flag IN varchar2
275 ,p_apply_progress_flag IN varchar2 DEFAULT 'N'
276 ,p_precedence_progress_flag IN varchar2 DEFAULT 'N'
277 ,x_return_status OUT NOCOPY varchar2
278 ,x_msg_data OUT NOCOPY varchar2
279 ,x_msg_count OUT NOCOPY Number
280 );
281
282 /* This API will apply the precedence rules on Rate Based planning transactions */
283 PROCEDURE Apply_NON_RATE_BASE_precedence(
284 p_txn_currency_code IN Varchar2
285 ,p_rate_based_flag IN Varchar2
286 ,p_budget_version_type IN Varchar2
287 ,p_qty_changed_flag IN Varchar2
288 ,p_raw_cost_changed_flag IN Varchar2
289 ,p_rw_cost_rate_changed_flag IN Varchar2
290 ,p_burden_cost_changed_flag IN Varchar2
291 ,p_b_cost_rate_changed_flag IN Varchar2
292 ,p_rev_changed_flag IN Varchar2
293 ,p_bill_rate_changed_flag IN Varchar2
294 ,p_bill_rt_ovr_changed_flag IN Varchar2
295 ,p_init_raw_cost IN Number
296 ,p_init_burdened_cost IN Number
297 ,p_init_revenue IN Number
298 ,p_init_quantity IN Number
299 ,p_bl_raw_cost IN Number
300 ,p_bl_burdened_cost IN Number
301 ,p_bl_revenue IN Number
302 ,p_bl_quantity IN Number
303 ,p_curr_cost_rate IN Number
304 ,p_curr_burden_rate IN Number
305 ,p_curr_bill_rate IN Number
306 ,p_revenue_only_entry_flag IN Varchar2
307 ,x_txn_plan_quantity IN OUT NOCOPY Number
308 ,x_txn_raw_cost IN OUT NOCOPY Number
309 ,x_txn_raw_cost_rate IN OUT NOCOPY Number
310 ,x_txn_rw_cost_rate_override IN OUT NOCOPY Number
311 ,x_txn_burdened_cost IN OUT NOCOPY Number
312 ,x_txn_b_cost_rate IN OUT NOCOPY Number
313 ,x_txn_b_cost_rate_override IN OUT NOCOPY Number
314 ,x_txn_revenue IN OUT NOCOPY Number
315 ,x_txn_bill_rate IN OUT NOCOPY Number
316 ,x_txn_bill_rate_override IN OUT NOCOPY Number
317 ,x_txn_revenue_addl IN OUT NOCOPY Number
318 ,x_txn_raw_cost_addl IN OUT NOCOPY Number
319 ,x_txn_plan_quantity_addl IN OUT NOCOPY Number
320 ,x_txn_burdened_cost_addl IN OUT NOCOPY Number
321 );
322
323 /* This API will apply the precedence rules on Rate Based planning transactions */
324 PROCEDURE Apply_RATE_BASE_precedence(
325 p_txn_currency_code IN Varchar2
326 ,p_rate_based_flag IN Varchar2
327 ,p_budget_version_type IN Varchar2
328 ,p_qty_changed_flag IN Varchar2
329 ,p_raw_cost_changed_flag IN Varchar2
330 ,p_rw_cost_rate_changed_flag IN Varchar2
331 ,p_burden_cost_changed_flag IN Varchar2
332 ,p_b_cost_rate_changed_flag IN Varchar2
333 ,p_rev_changed_flag IN Varchar2
334 ,p_bill_rate_changed_flag IN Varchar2
335 ,p_bill_rt_ovr_changed_flag IN Varchar2
336 ,p_init_raw_cost IN Number
337 ,p_init_burdened_cost IN Number
338 ,p_init_revenue IN Number
339 ,p_init_quantity IN Number
340 ,p_bl_raw_cost IN Number
341 ,p_bl_burdened_cost IN Number
342 ,p_bl_revenue IN Number
343 ,p_bl_quantity IN Number
347 ,x_txn_plan_quantity IN OUT NOCOPY Number
344 ,p_curr_cost_rate IN Number
345 ,p_curr_burden_rate IN Number
346 ,p_curr_bill_rate IN Number
348 ,x_txn_raw_cost IN OUT NOCOPY Number
349 ,x_txn_raw_cost_rate IN OUT NOCOPY Number
350 ,x_txn_rw_cost_rate_override IN OUT NOCOPY Number
351 ,x_txn_burdened_cost IN OUT NOCOPY Number
352 ,x_txn_b_cost_rate IN OUT NOCOPY Number
353 ,x_txn_b_cost_rate_override IN OUT NOCOPY Number
354 ,x_txn_revenue IN OUT NOCOPY Number
355 ,x_txn_bill_rate IN OUT NOCOPY Number
356 ,x_txn_bill_rate_override IN OUT NOCOPY Number
357 ,x_txn_revenue_addl IN OUT NOCOPY Number
358 ,x_txn_raw_cost_addl IN OUT NOCOPY Number
359 ,x_txn_plan_quantity_addl IN OUT NOCOPY Number
360 ,x_txn_burdened_cost_addl IN OUT NOCOPY Number
361 );
362
363
364 /* This API rounds off the amounts to currency precision level and the last budget line of resoruce per currency will be
365 * updated with the rounding discrepancy amounts
366 */
367 PROCEDURE Update_rounding_diff(
368 p_project_id IN pa_budget_versions.project_id%type
369 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
370 ,p_calling_module IN VARCHAR2 DEFAULT NULL
371 ,p_source_context IN pa_fp_res_assignments_tmp.source_context%TYPE
372 ,p_wp_cost_enabled_flag IN varchar2
373 ,p_budget_version_type IN varchar2
374 ,x_return_status OUT NOCOPY VARCHAR2
375 ,x_msg_count OUT NOCOPY NUMBER
376 ,x_msg_data OUT NOCOPY VARCHAR2) ;
377
378 /* This API initializes the global variables for the given budget version Id */
379 PROCEDURE Init_Globals(
380 p_budget_version_id IN NUMBER
381 ,p_source_context IN VARCHAR2
382 ,x_return_status OUT NOCOPY VARCHAR2
383 );
384
385 /* Bug 5726773 : CheckZeroQTyNegETC API is obsoleted : remove the API from package spec to avoid confusion of making calls
386 * this API
387 * Bug fix:4395494: added the following API CheckZeroQTyNegETC in spec so that it can be used in Change order flows
388 * Bug fix: 4387004: This API is added for checking -ve ETC quantity / zero planned quantity
389 * with non-zero actual quantity for planning resource and txn currency combination
390 *
391 * Bug 4290043.Included parameters so as to pass the amounts/actuals as pl-sql tbls input.
392 * The pl-sql tbls are assumed to be ordered by resource assignment id and each record in the pl-sql
393 * tbl corresponds to a record in pa_budget_lines.p_calling_context can be GLB_TBL (Global Temp Table)
394 * or PLS_TBL(Pl-Sql tbl)
395 */
396
397 /* ERs: MRC Enhancements:This API builds plsql table of records required for MRC conversions
398 */
399 PROCEDURE Populate_MRC_plsqlTabs
400 (p_calling_module IN Varchar2 Default 'CALCULATE_API'
401 ,p_budget_version_id IN Number
402 ,p_budget_line_id IN Number
403 ,p_resource_assignment_id IN Number
404 ,p_start_date IN Date
405 ,p_end_date IN Date
406 ,p_period_name IN Varchar2 Default NULL
407 ,p_txn_currency_code IN Varchar2
408 ,p_quantity IN Number Default NULL
409 ,p_txn_raw_cost IN Number Default NULL
410 ,p_txn_burdened_cost IN Number Default NULL
411 ,p_txn_revenue IN Number Default NULL
412 ,p_project_currency_code IN Varchar2 Default NULL
413 ,p_project_raw_cost IN Number Default NULL
414 ,p_project_burdened_cost IN Number Default NULL
415 ,p_project_revenue IN Number Default NULL
416 ,p_projfunc_currency_code IN Varchar2 Default NULL
417 ,p_projfunc_raw_cost IN Number Default NULL
418 ,p_projfunc_burdened_cost IN Number Default NULL
419 ,p_projfunc_revenue IN Number Default NULL
420 ,p_delete_flag IN Varchar2 := 'N'
421 ,p_billable_flag IN Varchar2 := 'Y'
422 ,p_project_cost_rate_type IN Varchar2 default NULL
423 ,p_project_cost_exchange_rate IN Number default NULL
424 ,p_project_cost_rate_date_type IN Varchar2 default NULL
425 ,p_project_cost_rate_date IN Date default NULL
426 ,p_project_rev_rate_type IN Varchar2 default NULL
427 ,p_project_rev_exchange_rate IN Number default NULL
428 ,p_project_rev_rate_date_type IN Varchar2 default NULL
429 ,p_project_rev_rate_date IN Date default NULL
430 ,p_projfunc_cost_rate_type IN Varchar2 default NULL
431 ,p_projfunc_cost_exchange_rate IN Number default NULL
432 ,p_projfunc_cost_rate_date_type IN Varchar2 default NULL
436 ,p_projfunc_rev_rate_date_type IN Varchar2 default NULL
433 ,p_projfunc_cost_rate_date IN Date default NULL
434 ,p_projfunc_rev_rate_type IN Varchar2 default NULL
435 ,p_projfunc_rev_exchange_rate IN Number default NULL
437 ,p_projfunc_rev_rate_date IN Date default NULL
438 ,x_msg_data OUT NOCOPY Varchar2
439 ,x_return_status OUT NOCOPY Varchar2
440 );
441
442 PROCEDURE Init_MRC_plsqlTabs;
443
444 PROCEDURE SetGatherTmpTblIndxStats
445 (p_table_name IN VARCHAR2
446 ,p_numRow IN NUMBER
447 ,x_return_status OUT NOCOPY VARCHAR2 );
448
449 /* Added for bug 5028631 */
450 PROCEDURE Process_skipped_records
451 ( p_budget_version_id IN NUMBER
452 ,p_calling_mode IN VARCHAR2
453 ,p_source_context IN VARCHAR2
454 ,x_return_status OUT NOCOPY VARCHAR2
455 ,x_msg_count OUT NOCOPY NUMBER
456 ,x_msg_data OUT NOCOPY VARCHAR2);
457
458 END PA_FP_CALC_PLAN_PKG;
[Home] [Help]