[Home] [Help]
PACKAGE BODY: APPS.PA_FIN_PLAN_UTILS2
Source
1 PACKAGE BODY PA_FIN_PLAN_UTILS2 AS
2 /* $Header: PAFPUT2B.pls 120.8.12010000.2 2008/11/28 12:16:02 vgovvala ship $ */
3
4 G_FpTaskBillable_Tab PA_FIN_PLAN_UTILS2.BillableTab;
5
6 g_debug_flag Varchar2(1);
7 /** Forward declaration */
8 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
9 PROCEDURE checkUserRateAllowed
10 (p_From_curr_code IN Varchar2
11 ,p_To_curr_code IN Varchar2
12 ,p_Conversion_Date IN Date
13 ,p_To_Curr_Rate_Type IN Varchar2
14 ,p_To_Curr_Exchange_Rate IN Number
15 ,p_calling_mode IN Varchar2
16 ,p_calling_context IN Varchar2
17 ,x_return_status OUT NOCOPY Varchar2
18 ,x_error_msg_code OUT NOCOPY Varchar2
19 );
20 /******/
21 /**
22 procedure calc_log(p_msg varchar2) IS
23
24 pragma autonomous_transaction ;
25 BEGIN
26 --dbms_output.put_line(p_msg);
27 --IF P_PA_DEBUG_MODE = 'Y' Then
28 NULL;
29 INSERT INTO PA_FP_CALCULATE_LOG
30 (SESSIONID
31 ,SEQ_NUMBER
32 ,LOG_MESSAGE)
33 VALUES
34 (userenv('sessionid')
35 ,HR.PAY_US_GARN_FEE_RULES_S.nextval
36 ,substr(P_MSG,1,240)
37 );
38 --END IF;
39 COMMIT;
40
41 end calc_log;
42 **/
43 PROCEDURE print_msg(p_debug_flag varchar2
44 ,p_proc_name varchar2
45 ,p_msg varchar2 ) IS
46
47 l_module varchar2(1000) := 'PA_FIN_PLAN_UTILS2.'||p_proc_name;
48
49 BEGIN
50 --calc_log(p_msg);
51 If p_debug_flag = 'Y' Then
52 PA_DEBUG.write
53 (x_Module => l_module
54 ,x_Msg => substr('LOG:'||p_msg,1,240)
55 ,x_Log_Level => 3);
56 End If;
57 END print_msg;
58
59 /* This API will be called from view to derive rejection flags based on the
60 * given start and end dates
61 */
62 FUNCTION get_bdgt_start_date Return DATE IS
63 BEGIN
64 return g_bdgt_start_date;
65 END get_bdgt_start_date;
66 /* This API will be called from view to derive rejection flags based on the
67 * given start and end dates
68 */
69 FUNCTION get_bdgt_end_date Return DATE IS
70 BEGIN
71 return g_bdgt_end_date;
72 END get_bdgt_end_date;
73
74 /* This API will derive the rate based flag and UOM for the planning transaction
75 * This should be called while updating the planning resource transaction
76 * Based on the IN params the new rate based flag and UOM will be derived
77 * If old and new rate base flag values are different then x_rate_based_flag_changed_tab will be set to 'Y'
78 * If old and new UOM values are different then x_uom_changed_flag_tab will be set to 'Y'
79 * NOTE: Since this is PLSQL table LOOPING , this api should be called in batch of 100 records only
80 */
81 PROCEDURE Get_UOM_RateBasedFlag (
82 p_resource_class_code_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
83 ,p_inventory_item_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
84 ,p_rate_organization_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
85 ,p_expenditure_type_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
86 ,p_rate_expenditure_type_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
87 ,p_old_UOM_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
88 ,p_old_rate_based_flag_tab IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE
89 ,x_New_UOM_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
90 ,x_uom_changed_flag_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
91 ,x_new_rate_based_flag_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
92 ,x_rate_based_flag_changed_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
93 ,x_return_status OUT NOCOPY VARCHAR2
94 ) IS
95
96 l_debug_flag Varchar2(100);
97 l_stage Varchar2(1000);
98 l_new_rate_based_flag Varchar2(100);
99 l_new_uom Varchar2(100);
100 l_return_status Varchar2(100) := 'S';
101 l_dummy_rate_based_flag Varchar2(100);
102
103 l_proc_name varchar2(100) := 'Get_UOM_RateBasedFlag';
104
105
106 CURSOR cur_expRateFlag(p_exp_type varchar2) IS
107 SELECT NVL(c.cost_rate_flag,'N') Cost_rate_flag
108 ,Unit_Of_measure
109 FROM pa_expenditure_types c
110 WHERE c.expenditure_type = p_exp_type;
111
112 CURSOR cur_ItemUom(p_item_id Number,p_organization_id Number) IS
113 SELECT primary_uom_code
114 FROM mtl_system_items_b items
115 WHERE items.inventory_item_id = p_item_id
116 AND items.organization_id = p_organization_id
117 AND rownum = 1;
118
119
120
121 BEGIN
122 --- Initialize the error statck
123 IF p_pa_debug_mode = 'Y' THEN
124 PA_DEBUG.init_err_stack ('PA_FIN_PLAN_UTILS2.Get_UOM_RateBasedFlag');
125 END IF;
126 fnd_profile.get('PA_DEBUG_MODE',l_debug_flag);
127 l_debug_flag := NVL(l_debug_flag, 'N');
128
129 IF p_pa_debug_mode = 'Y' THEN
130 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
131 ,x_write_file => 'LOG'
132 ,x_debug_mode => l_debug_flag
133 );
134 END IF;
135 x_return_status := 'S';
136
137 l_stage := 'Begin of Get_UOM_RateBasedFlag Table Count['||p_resource_class_code_tab.count||']';
138 print_msg(l_debug_flag,l_proc_name,l_stage);
139
140 x_New_UOM_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
141 x_uom_changed_flag_tab := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
142 x_new_rate_based_flag_tab := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
143 x_rate_based_flag_changed_tab := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
144
145 x_New_UOM_tab.extend(p_resource_class_code_tab.count);
146 x_uom_changed_flag_tab.extend(p_resource_class_code_tab.count);
147 x_new_rate_based_flag_tab.extend(p_resource_class_code_tab.count);
148 x_rate_based_flag_changed_tab.extend(p_resource_class_code_tab.count);
149
150 /* Bug fix:3610888 removed the nvl(p_rate_expenditure_type_tab,p_rate_expenditure_type_tab
151 * the param p_rate_expenditure_type_tab WILL BE IGNORED */
152 IF p_resource_class_code_tab.count > 0 THEN
153 FOR i IN p_resource_class_code_tab.FIRST .. p_resource_class_code_tab.LAST LOOP
154 l_stage := 'Inside the Loop Index['||i||']: ResClass['||p_resource_class_code_tab(i)||
155 ']InvItem['||p_inventory_item_id_tab(i)||
156 ']OldRateFlag['||p_old_rate_based_flag_tab(i)||
157 ']OldUom['||p_old_UOM_tab(i)||
158 ']ExpType['||p_expenditure_type_tab(i)||
159 ']RateExpType['||p_rate_expenditure_type_tab(i)||
160 ']RateOrganzId['||p_rate_organization_id_tab(i)||
161 ']';
162 print_msg(l_debug_flag,l_proc_name,l_stage);
163 l_new_rate_based_flag := NVL(p_old_rate_based_flag_tab(i),'N');
164 l_new_uom := NVL(p_old_UOM_tab(i),'DOLLARS');
165
166 /* Derive the new Rate Based flag */
167 IF p_resource_class_code_tab(i) in ('PEOPLE','EQUIPMENT') Then
168 l_new_rate_based_flag := 'Y';
169 l_new_uom := 'HOURS';
170 Elsif p_resource_class_code_tab(i) in ('MATERIAL_ITEMS') Then
171 If p_inventory_item_id_tab(i) is NOT NULL Then
172 /* for inventory items the rate based flag is always Y */
173 l_new_rate_based_flag := 'Y';
174 OPEN cur_ItemUom(p_inventory_item_id_tab(i),p_rate_organization_id_tab(i));
175 FETCH cur_ItemUom INTO
176 l_new_uom ;
177 CLOSE cur_ItemUom;
178 If l_new_uom is NULL Then
179 /* derive uom based on the expenditure type */
180 If p_expenditure_type_tab(i) is NOT NULL Then
181 OPEN cur_expRateFlag(p_expenditure_type_tab(i));
182 FETCH cur_expRateFlag INTO
183 l_dummy_rate_based_flag
184 ,l_new_uom ;
185 CLOSE cur_expRateFlag;
186 End If;
187 End If;
188 Elsif p_expenditure_type_tab(i) is NOT NULL Then
189 OPEN cur_expRateFlag(p_expenditure_type_tab(i));
190 FETCH cur_expRateFlag INTO
191 l_new_rate_based_flag
192 ,l_new_uom ;
193 CLOSE cur_expRateFlag;
194 Else
195 l_new_rate_based_flag := 'N' ;
196 End If;
197 ElsIf p_resource_class_code_tab(i) in ('FINANCIAL_ELEMENTS') Then
198 IF p_expenditure_type_tab(i) is NOT NULL Then
199 OPEN cur_expRateFlag(p_expenditure_type_tab(i));
200 FETCH cur_expRateFlag INTO
201 l_new_rate_based_flag
202 ,l_new_uom ;
203 CLOSE cur_expRateFlag;
204 Else --!!!!expenditure type is null
205 l_new_rate_based_flag := 'N' ;
206 l_new_uom := p_old_UOM_tab(i);
207 End If;
208
209 Else -- no resource class
210 l_new_rate_based_flag := 'N' ;
211 End If;
212 /* Assign the local variable to Out param */
213 x_new_rate_based_flag_tab(i) := l_new_rate_based_flag;
214 x_rate_based_flag_changed_tab(i) := 'N';
215 If NVL(p_old_rate_based_flag_tab(i),'N') <> NVL(x_new_rate_based_flag_tab(i),'N') Then
216 x_rate_based_flag_changed_tab(i) := 'Y';
217 End If;
218
219 If l_new_uom is NULL Then
220 l_new_uom := NVL(p_old_UOM_tab(i),'DOLLARS');
221 End IF;
222 x_new_uom_tab(i) := l_new_uom;
223 x_uom_changed_flag_tab(i) := 'N';
224 If NVL(p_old_uom_tab(i),'DOLLARS') <> NVL(x_new_uom_tab(i),'DOLLARS') Then
225 x_uom_changed_flag_tab(i) := 'Y';
226 End IF;
227 l_stage := 'OutParams Are NewUOM['||x_new_uom_tab(i)||
228 ']NewRateFlag['||x_new_rate_based_flag_tab(i)||
229 ']UomChangedFlag['||x_uom_changed_flag_tab(i)||
230 ']RateChangedFlag['||x_rate_based_flag_changed_tab(i)||
231 ']';
232 print_msg(l_debug_flag,l_proc_name,l_stage);
233 END LOOP;
234 END IF;
235
236 x_return_status := l_return_status;
237 l_stage := 'End of Get_UOM_RateBasedFlag RetnSts['||x_return_status||']';
238 print_msg(l_debug_flag,l_proc_name,l_stage);
239
240 IF p_pa_debug_mode = 'Y' THEN
241 PA_DEBUG.reset_err_stack;
242 END IF;
243 EXCEPTION
244 WHEN OTHERS THEN
245 IF p_pa_debug_mode = 'Y' THEN
246 PA_DEBUG.write_file('LOG','Failed in Get_UOM_RateBasedFlag['||SQLCODE||SQLERRM);
247 END IF;
248 print_msg(l_debug_flag,l_proc_name,'Failed in Get_UOM_RateBasedFlag['||SQLCODE||SQLERRM);
249 IF p_pa_debug_mode = 'Y' THEN
250 PA_DEBUG.reset_err_stack;
251 END IF;
252 Raise;
253
254 END Get_UOM_RateBasedFlag;
255
256 /* This API derives the rejection code flags from budget lines for the given
257 * resource_assignment_id and txn_currency_code
258 * The out variables will be set to 'Y' if there is any rejection else it is 'N'
259 */
260 PROCEDURE Get_BdgtLineRejFlags
261 (p_resource_assignment_id IN Number
262 ,p_txn_currency_code IN Varchar2
263 ,p_budget_version_id IN Number
264 ,p_start_date IN Date default Null
265 ,p_end_date IN Date default Null
266 ,x_cost_rejection_flag OUT NOCOPY Varchar2
267 ,x_burden_rejection_flag OUT NOCOPY Varchar2
268 ,x_revenue_rejection_flag OUT NOCOPY Varchar2
269 ,x_pc_conv_rejection_flag OUT NOCOPY Varchar2
270 ,x_pfc_conv_rejection_flag OUT NOCOPY Varchar2
271 ,x_other_rejection_flag OUT NOCOPY Varchar2
272 ,x_return_status OUT NOCOPY Varchar2
273 ) IS
274
275 l_stage varchar2(1000);
276 l_cost_rejection_flag Varchar2(10);
277 l_burden_rejection_flag Varchar2(10);
278 l_revenue_rejection_flag Varchar2(10);
279 l_pc_conv_rejection_flag Varchar2(10);
280 l_pfc_conv_rejection_flag Varchar2(10);
281 l_other_rejection_flag Varchar2(10);
282 l_proc_name Varchar2(100) := 'Get_BdgtLineRejFlags';
283
284 CURSOR cur_bdgtRejFlags IS
285 SELECT DISTINCT NVL(bl.cost_rejection_flag,'N')
286 ,NVL(bl.burden_rejection_flag,'N')
287 ,NVL(bl.revenue_rejection_flag,'N')
288 ,NVL(bl.pc_conv_rejection_flag,'N')
289 ,NVL(bl.pfc_conv_rejection_flag,'N')
290 ,NVL(bl.other_rejection_flag,'N')
291 FROM pa_fp_budget_line_rejections_v bl
292 WHERE bl.budget_version_id = p_budget_version_id
293 AND bl.resource_assignment_id = p_resource_assignment_id
294 AND bl.txn_currency_code = p_txn_currency_code
295 AND (bl.cost_rejection_code is NOT NULL
296 OR bl.burden_rejection_code is NOT NULL
297 OR bl.revenue_rejection_code is NOT NULL
298 OR bl.pc_cur_conv_rejection_code is NOT NULL
299 OR bl.pfc_cur_conv_rejection_code is NOT NULL
300 OR bl.other_rejection_code is NOT NULL -- Bug 5203622
301 );
302
303 CURSOR cur_bdgtPeriodRejFlags IS
304 SELECT DISTINCT NVL(bl.period_cost_rejection_flag,'N')
305 ,NVL(bl.period_burden_rejection_flag,'N')
306 ,NVL(bl.period_revenue_rejection_flag,'N')
307 ,NVL(bl.period_pc_conv_rejection_flag,'N')
308 ,NVL(bl.period_pfc_conv_rejection_flag,'N')
309 ,NVL(bl.period_other_rejection_flag,'N')
310 FROM pa_fp_budget_line_rejections_v bl
311 WHERE bl.budget_version_id = p_budget_version_id
312 AND bl.resource_assignment_id = p_resource_assignment_id
313 AND bl.txn_currency_code = p_txn_currency_code
314 AND bl.start_date between trunc(p_start_date) AND trunc(p_end_date)
315 AND bl.end_date between trunc(p_start_date) AND trunc(p_end_date)
316 AND (bl.cost_rejection_code is NOT NULL
317 OR bl.burden_rejection_code is NOT NULL
318 OR bl.revenue_rejection_code is NOT NULL
319 OR bl.pc_cur_conv_rejection_code is NOT NULL
320 OR bl.pfc_cur_conv_rejection_code is NOT NULL
321 OR bl.other_rejection_code is NOT NULL -- Bug 5203622
322 );
323
324
325 BEGIN
326 l_stage := 'Begin Get_BdgtLineRejFlags IN Params:ResAssn['||p_resource_assignment_id||
327 ']TxnCurr['||p_txn_currency_code||']BdgtVer['||p_budget_version_id||']';
328 print_msg(g_debug_flag,l_proc_name,l_stage);
329
330 x_return_status := 'S';
331
332 /* open the cursor and fetch values into out variables */
333 IF p_start_date is NULL OR p_end_date is NULL Then
334 OPEN cur_bdgtRejFlags;
335 FETCH cur_bdgtRejFlags INTO
336 l_cost_rejection_flag
337 ,l_burden_rejection_flag
338 ,l_revenue_rejection_flag
339 ,l_pc_conv_rejection_flag
340 ,l_pfc_conv_rejection_flag
341 ,l_other_rejection_flag ;
342 CLOSE cur_bdgtRejFlags;
343 Else
344 /* set the start and end dates for the global variables to return to the function */
345 pa_fin_plan_utils2.g_bdgt_start_date := p_start_date;
346 pa_fin_plan_utils2.g_bdgt_end_date := p_end_date;
347
348 OPEN cur_bdgtPeriodRejFlags;
349 FETCH cur_bdgtPeriodRejFlags INTO
350 l_cost_rejection_flag
351 ,l_burden_rejection_flag
352 ,l_revenue_rejection_flag
353 ,l_pc_conv_rejection_flag
354 ,l_pfc_conv_rejection_flag
355 ,l_other_rejection_flag ;
356 CLOSE cur_bdgtPeriodRejFlags;
357 End If;
358
359 /* set the values to N in case of cursor not found */
360 x_cost_rejection_flag := NVL(l_cost_rejection_flag,'N');
361 x_burden_rejection_flag := NVL(l_burden_rejection_flag,'N');
362 x_revenue_rejection_flag := NVL(l_revenue_rejection_flag,'N');
363 x_pc_conv_rejection_flag := NVL(l_pc_conv_rejection_flag,'N');
364 x_pfc_conv_rejection_flag := NVL(l_pfc_conv_rejection_flag,'N');
365 x_other_rejection_flag := NVL(l_other_rejection_flag,'N');
366
367 l_stage := 'End of Get_BdgtLineRejFlags: Cost['||x_cost_rejection_flag||']Burden['||x_burden_rejection_flag||
368 ']revenue['||x_revenue_rejection_flag ||']PC['||x_pc_conv_rejection_flag ||']PFC['||x_pfc_conv_rejection_flag||
369 ']Others['||x_other_rejection_flag||']' ;
370 print_msg(g_debug_flag,l_proc_name,l_stage);
371 Return;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 x_return_status := 'U';
376 RAISE;
377
378 END Get_BdgtLineRejFlags;
379
380
381 /* This API derives the rejection reason from budget lines for the given
382 * resource_assignment_id and txn_currency_code,Start_date, end_date
383 * The out variable will be an array of messages corresponding to the
384 * budget line rejection codes
385 */
386 PROCEDURE Get_BdgtLineRejctions
387 (p_resource_assignment_id IN Number
388 ,p_txn_currency_code IN Varchar2
389 ,p_budget_version_id IN Number
390 ,p_start_date IN Date
391 ,p_end_date IN Date
392 ,x_period_name_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_80_TBL_TYPE
393 ,x_cost_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
394 ,x_burden_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
395 ,x_revenue_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
396 ,x_pc_conv_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
397 ,x_pfc_conv_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
398 ,x_other_rejection_data_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
399 ,x_return_status OUT NOCOPY Varchar2
400 ) IS
401 l_stage VARCHAR2(2000);
402 l_tab_project_id SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
403 l_tab_budget_version_id SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
404 l_tab_res_assignment_id SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
405 l_tab_period_name SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
406 l_tab_start_date SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
407 l_tab_end_date SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
408 l_tab_cost_rejection_code SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
409 l_tab_burd_rejection_code SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
410 l_tab_revn_rejection_code SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
411 l_tab_pc_rejection_code SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
412 l_tab_pfc_rejection_code SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
413 l_tab_othr_rejection_code SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
414 l_tab_cost_rejection_data SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
415 l_tab_burd_rejection_data SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
416 l_tab_revn_rejection_data SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
417 l_tab_pc_rejection_data SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
418 l_tab_pfc_rejection_data SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
419 l_tab_othr_rejection_data SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
420
421 l_proc_name varchar2(100) := 'Get_BdgtLineRejctions';
422
423 CURSOR cur_BdgtRejctions IS
424 SELECT bl.project_id
425 ,bl.budget_version_id
426 ,bl.resource_assignment_id
427 ,bl.period_name
428 ,bl.start_date
429 ,bl.end_date
430 ,bl.cost_rejection_code
431 ,bl.burden_rejection_code
432 ,bl.revenue_rejection_code
433 ,bl.pc_cur_conv_rejection_code
434 ,bl.pfc_cur_conv_rejection_code
435 ,bl.other_rejection_code
436 ,decode(bl.cost_rejection_code,NULL,NULL,
437 bl.period_name||':'||(substr(bl.cost_rejection_msg_data,instr(bl.cost_rejection_msg_data,';',-1)+1)))
438 ,decode(bl.burden_rejection_code,NULL,NULL,
439 bl.period_name||':'||(substr(bl.burden_rejection_msg_data,instr(bl.burden_rejection_msg_data,';',-1)+1)))
440 ,decode(bl.revenue_rejection_code,NULL,NULL,
441 bl.period_name||':'||(substr(bl.revenue_rejection_msg_data,instr(bl.revenue_rejection_msg_data,';',-1)+1)))
442 ,decode(bl.pc_cur_conv_rejection_code,NULL,NULL,
443 bl.period_name||':'||(substr(bl.pc_conv_rejection_msg_data,instr(bl.pc_conv_rejection_msg_data,';',-1)+1)))
444 ,decode(bl.pfc_cur_conv_rejection_code,NULL,NULL,
445 bl.period_name||':'||(substr(bl.pfc_conv_rejection_msg_data,instr(bl.pfc_conv_rejection_msg_data,';',-1)+1)))
446 ,decode(bl.other_rejection_code,NULL,NULL,
447 bl.period_name||':'||(substr(bl.other_rejection_msg_data,instr(bl.other_rejection_msg_data,';',-1)+1)))
448 FROM pa_fp_budget_line_rejections_v bl
449 WHERE bl.budget_version_id = p_budget_version_id
450 AND bl.resource_assignment_id = p_resource_assignment_id
451 AND bl.txn_currency_code = p_txn_currency_code
452 AND bl.start_date BETWEEN trunc(p_start_date) AND trunc(p_end_date)
453 AND bl.end_date BETWEEN trunc(p_start_date) AND trunc(p_end_date) ;
454
455 BEGIN
456 l_stage := 'Begin Get_BdgtLineRejctions IN Params:ResAssn['||p_resource_assignment_id||
457 ']TxnCurr['||p_txn_currency_code||']BdgtVer['||p_budget_version_id||']SD['||p_start_date||
458 ']ED['||p_end_date||']';
459 print_msg(g_debug_flag,l_proc_name,l_stage);
460
461 x_return_status := 'S';
462 /* Initialize the out varrys */
463 x_period_name_tab := system.PA_VARCHAR2_80_TBL_TYPE();
464 x_cost_rejection_data_tab := system.PA_VARCHAR2_2000_TBL_TYPE();
465 x_burden_rejection_data_tab := system.PA_VARCHAR2_2000_TBL_TYPE();
466 x_revenue_rejection_data_tab := system.PA_VARCHAR2_2000_TBL_TYPE();
467 x_pc_conv_rejection_data_tab := system.PA_VARCHAR2_2000_TBL_TYPE();
468 x_pfc_conv_rejection_data_tab := system.PA_VARCHAR2_2000_TBL_TYPE();
469 x_other_rejection_data_tab := system.PA_VARCHAR2_2000_TBL_TYPE();
470
471
472 OPEN cur_BdgtRejctions;
473 FETCH cur_BdgtRejctions BULK COLLECT INTO
474 l_tab_project_id
475 ,l_tab_budget_version_id
476 ,l_tab_res_assignment_id
477 ,l_tab_period_name
478 ,l_tab_start_date
479 ,l_tab_end_date
480 ,l_tab_cost_rejection_code
481 ,l_tab_burd_rejection_code
482 ,l_tab_revn_rejection_code
483 ,l_tab_pc_rejection_code
484 ,l_tab_pfc_rejection_code
485 ,l_tab_othr_rejection_code
486 ,l_tab_cost_rejection_data
487 ,l_tab_burd_rejection_data
488 ,l_tab_revn_rejection_data
489 ,l_tab_pc_rejection_data
490 ,l_tab_pfc_rejection_data
491 ,l_tab_othr_rejection_data
492 ;
493 CLOSE cur_BdgtRejctions;
494
495 l_stage := 'Num Of Rows Fetched ['||l_tab_res_assignment_id.count||']';
496 print_msg(g_debug_flag,l_proc_name,l_stage);
497
498
499 /* assign the values to OUT varry */
500 x_period_name_tab := l_tab_period_name;
501 x_cost_rejection_data_tab := l_tab_cost_rejection_data;
502 x_burden_rejection_data_tab := l_tab_burd_rejection_data;
503 x_revenue_rejection_data_tab := l_tab_revn_rejection_data;
504 x_pc_conv_rejection_data_tab := l_tab_pc_rejection_data;
505 x_pfc_conv_rejection_data_tab := l_tab_pfc_rejection_data;
506 x_other_rejection_data_tab := l_tab_othr_rejection_data;
507
508
509 l_stage := 'End Get_BdgtLineRejections';
510 print_msg(g_debug_flag,l_proc_name,l_stage);
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 x_return_status := 'U';
515 print_msg('Y',l_proc_name,l_stage||SQLCODE||SQLERRM);
516 Raise;
517
518 END Get_BdgtLineRejctions;
519
520 /* This API returns the default resource list for the given project and Plan type
521 * based on the finplan option level code = 'PLAN_TYPE'
522 * By Default it gives the Cost resource list attached at the plan type
523 * if not found then it returns the Revenue resource list
524 */
525 PROCEDURE Get_Default_FP_Reslist
526 (p_project_id IN Number
527 ,p_fin_plan_type_id IN Number DEFAULT NULL
528 ,x_res_list_id OUT NOCOPY NUMBER
529 ,x_res_list_name OUT NOCOPY Varchar2)
530 IS
531 CURSOR cur_fpReslist IS
532 SELECT fp_resource_list_id
533 ,fp_resource_list_type
534 FROM pa_fp_options_Reslists_v
535 WHERE project_id = p_project_id
536 AND fin_plan_type_id = p_fin_plan_type_id ;
537
538 /* This cursor derives the default resource list associated with the project
539 * Logic: get the ResList from project fp options based on the approved cost Plan type
540 for the given project. If no rows found or resList is null then Get the reslist
541 from the current budget versions for the approved cost budget version, if no rows found
542 then get the Resource List from the ResourceList Assignments where default flag is Y
543 */
544 CURSOR cur_projReslist IS
545 select fp.project_id
546 ,1 default_level --just for ordering purpose
547 ,NVL(fp.cost_resource_list_id,fp.all_resource_list_id) Resource_list_id
548 from pa_proj_fp_options fp
549 ,pa_fin_plan_types_b typ
550 where fp.project_id = p_project_id
551 and fp.fin_plan_type_id = typ.fin_plan_type_id
552 and typ.plan_class_code = 'BUDGET'
553 and fp.fin_plan_option_level_code = 'PLAN_TYPE'
554 and NVL(fp.cost_resource_list_id,fp.all_resource_list_id) is NOT NULL
555 and fp.approved_cost_plan_type_flag = 'Y'
556 and rownum = 1
557 UNION
558 select bv.project_id
559 ,2 default_level
560 ,bv.resource_list_id
561 from pa_budget_versions bv
562 ,pa_budget_types bt
563 where bv.fin_plan_type_id is Null
564 and bv.budget_type_code = bt.budget_type_code
565 and bt.budget_amount_code = 'C'
566 and bv.current_flag = 'Y'
567 and bv.project_id = p_project_id
568 and rownum =1
569 UNION
570 Select pp.project_id
571 ,3 Default_level
572 ,rla.resource_list_id
573 from pa_resource_list_assignments rla
574 ,pa_resource_list_uses rlu
575 ,pa_projects_all pp
576 where pp.project_id = p_project_id
577 and rla.project_id = pp.project_id
578 and rlu.resource_list_assignment_id = rla.resource_list_assignment_id
579 and rlu.default_flag = 'Y'
580 and rownum = 1
581 ORDER BY 1,2;
582
583
584 l_resource_list_id Number;
585 l_resource_list_type Varchar2(1000);
586 l_resource_list_name pa_resource_lists_tl.name%type;
587 l_project_id Number;
588 l_default_level Number;
589
590 BEGIN
591
592 If p_fin_plan_type_id is NOT NULL Then
593 OPEN cur_fpReslist;
594 FETCH cur_fpReslist INTO
595 l_resource_list_id
596 ,l_resource_list_type ;
597 CLOSE cur_fpReslist;
598 Else
599 OPEN cur_ProjReslist;
600 FETCH cur_ProjReslist INTO
601 l_project_id
602 ,l_default_level
603 ,l_resource_list_id;
604 CLOSE cur_ProjReslist;
605 End If;
606
607 x_res_list_id:= l_resource_list_id;
608
609 If l_resource_list_id is NOT NULL Then
610 select name
611 into l_resource_list_name
612 from pa_resource_lists_tl
613 where resource_list_id = l_resource_list_id
614 and language = userenv('LANG');
615
616
617 x_res_list_name:= l_resource_list_name;
618 End If;
619
620 END Get_Default_FP_Reslist;
621
622 /* This API derives the default Resource list used in the WorkPlan structure
623 * for the given project Id
624 */
625 -- Changed this api structure for bugfix # 3680252.
626 PROCEDURE Get_Default_WP_ResList
627 (p_project_id IN Number
628 ,p_wps_version_id IN Number default NULL
629 ,x_res_list_id OUT NOCOPY NUMBER
630 ,x_res_list_name OUT NOCOPY Varchar2)
631 IS
632
633 l_resource_list_id Number;
634 l_resource_list_name pa_resource_lists_tl.name%type;
635
636 BEGIN
637 l_resource_list_id := PA_TASK_ASSIGNMENT_UTILS.Get_WP_Resource_List_Id(p_project_id);
638
639 If l_resource_list_id is not null then
640 select name
641 into l_resource_list_name
642 from pa_resource_lists_v
643 where resource_list_id = l_resource_list_id;
644 End if;
645
646 x_res_list_id := l_resource_list_id;
647 x_res_list_name:= l_resource_list_name;
648
649 END Get_Default_WP_ResList;
650
651 /* This cursor derives the default resource list associated with the project
652 * Logic: get the ResList from project fp options based on the approved cost Plan type
653 for the given project. If no rows found or resList is null then Get the reslist
654 from the current budget versions for the approved cost budget version, if no rows found
655 then get the Resource List from the project_types
656 */
657 PROCEDURE Get_Default_Project_ResList
658 (p_project_id IN Number
659 ,x_res_list_id OUT NOCOPY NUMBER
660 ,x_res_list_name OUT NOCOPY Varchar2) IS
661
662 BEGIN
663 PA_FIN_PLAN_UTILS2.Get_Default_FP_Reslist
664 (p_project_id => p_project_id
665 ,p_fin_plan_type_id => NULL
666 ,x_res_list_id => x_res_list_id
667 ,x_res_list_name => x_res_list_name
668 );
669
670 END Get_Default_Project_ResList;
671
672 /* This API adds the messages to the error stack */
673 PROCEDURE AddMsgtoStack
674 (p_msg_code IN VARCHAR2
675 ,p_project_token IN VARCHAR2
676 ,p_task_token IN VARCHAR2
677 ,p_budgt_token IN VARCHAR2
678 ,p_resource_token IN VARCHAR2
679 ,p_currency_token IN VARCHAR2
680 ,p_date_token IN DATE
681 ) IS
682
683 BEGIN
684
685 pa_utils.Add_Message( p_app_short_name => 'PA'
686 ,p_msg_name => p_msg_code
687 ,p_token1 => 'PROJECT'
688 ,p_value1 => p_project_token
689 ,p_token2 => 'TASK'
690 ,p_value2 => p_task_token
691 ,p_token3 => 'RESOURCE'
692 ,p_value3 => p_resource_token
693 ,p_token4 => 'CURRENCY'
694 ,p_value4 => p_currency_token
695 ,p_token5 => 'START DATE'
696 ,p_value5 => to_char(p_date_token)
697 );
698 EXCEPTION
699 WHEN OTHERS THEN
700 RAISE;
701
702 END AddMsgtoStack;
703
704 /* This API derives the rejection reason from budget lines for the given
705 * budget version id.The out variable will be an array of messages corresponding to the
706 * budget line rejection codes.
707 * This procedure is called from AMG apis.
708 * x_return_status will be 'S' in case of successful execution 'U' incase of 'Unexpected errors'
709 */
710 PROCEDURE Get_AMG_BdgtLineRejctions
711 (p_budget_version_id IN Number
712 ,x_budget_line_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
713 ,x_cost_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
714 ,x_burden_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
715 ,x_revenue_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
716 ,x_pc_conv_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
717 ,x_pfc_conv_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
718 ,x_other_rejection_data_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char2000TabTyp
719 ,x_return_status OUT NOCOPY Varchar2
720 ) IS
721 l_stage VARCHAR2(2000);
722 l_tab_project_id PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
723 l_tab_task_id PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
724 l_tab_budget_version_id PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
725 l_tab_budget_line_id PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
726 l_tab_res_assignment_id PA_PLSQL_DATATYPES.IdTabTyp := PA_PLSQL_DATATYPES.EmptyIdTab;
727 l_tab_period_name PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80Tab;
728 l_tab_curr_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80Tab;
729 l_tab_start_date PA_PLSQL_DATATYPES.DateTabTyp := PA_PLSQL_DATATYPES.EmptyDateTab;
730 l_tab_end_date PA_PLSQL_DATATYPES.DateTabTyp := PA_PLSQL_DATATYPES.EmptyDateTab;
731 l_tab_resource_name PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
732 l_tab_cost_rejection_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
733 l_tab_burd_rejection_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
734 l_tab_revn_rejection_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
735 l_tab_pc_rejection_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
736 l_tab_pfc_rejection_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
737 l_tab_othr_rejection_code PA_PLSQL_DATATYPES.Char80TabTyp := PA_PLSQL_DATATYPES.EmptyChar80tab;
738 l_tab_cost_rejection_data PA_PLSQL_DATATYPES.Char2000TabTyp := PA_PLSQL_DATATYPES.EmptyChar2000tab;
739 l_tab_burd_rejection_data PA_PLSQL_DATATYPES.Char2000TabTyp := PA_PLSQL_DATATYPES.EmptyChar2000tab;
740 l_tab_revn_rejection_data PA_PLSQL_DATATYPES.Char2000TabTyp := PA_PLSQL_DATATYPES.EmptyChar2000tab;
741 l_tab_pc_rejection_data PA_PLSQL_DATATYPES.Char2000TabTyp := PA_PLSQL_DATATYPES.EmptyChar2000tab;
742 l_tab_pfc_rejection_data PA_PLSQL_DATATYPES.Char2000TabTyp := PA_PLSQL_DATATYPES.EmptyChar2000tab;
743 l_tab_othr_rejection_data PA_PLSQL_DATATYPES.Char2000TabTyp := PA_PLSQL_DATATYPES.EmptyChar2000tab;
744
745 CURSOR cur_AmgBdgtRejctions IS
746 SELECT bl.project_id
747 ,bl.task_id
748 ,bl.budget_version_id
749 ,bl.budget_line_id
750 ,bl.resource_assignment_id
751 ,bl.resource_list_member_name
752 ,bl.txn_currency_code
753 ,bl.period_name
754 ,bl.start_date
755 ,bl.end_date
756 ,bl.cost_rejection_code
757 ,bl.burden_rejection_code
758 ,bl.revenue_rejection_code
759 ,bl.pc_cur_conv_rejection_code
760 ,bl.pfc_cur_conv_rejection_code
761 ,bl.other_rejection_code
762 ,decode(bl.cost_rejection_code,NULL,NULL,
763 msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
764 ||msg3.message_text||':'||bl.resource_list_member_name||'; '||msg4.message_text||':'
765 ||bl.txn_currency_code||'; '||msg5.message_text||':'||bl.start_date||'; '
766 ||(substr(bl.cost_rejection_msg_data,instr(bl.cost_rejection_msg_data,';',-1)+1)))
767 ,decode(bl.burden_rejection_code,NULL,NULL,
768 msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
769 ||msg3.message_text||':'||bl.resource_list_member_name||'; '||msg4.message_text||':'
770 ||bl.txn_currency_code||'; '||msg5.message_text||':'||bl.start_date||'; '
771 ||(substr(bl.burden_rejection_msg_data,instr(bl.burden_rejection_msg_data,';',-1)+1)))
772 ,decode(bl.revenue_rejection_code,NULL,NULL,
773 msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
774 ||msg3.message_text||':'||bl.resource_list_member_name||'; '||msg4.message_text||':'
775 ||bl.txn_currency_code||'; '||msg5.message_text||':'||bl.start_date||'; '
776 ||(substr(bl.revenue_rejection_msg_data,instr(bl.revenue_rejection_msg_data,';',-1)+1)))
777 ,decode(bl.pc_cur_conv_rejection_code,NULL,NULL,
778 msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
779 ||msg3.message_text||':'||bl.resource_list_member_name||'; '||msg4.message_text||':'
780 ||bl.txn_currency_code||'; '||msg5.message_text||':'||bl.start_date||'; '
781 ||(substr(bl.pc_conv_rejection_msg_data,instr(bl.pc_conv_rejection_msg_data,';',-1)+1)))
782 ,decode(bl.pfc_cur_conv_rejection_code,NULL,NULL,
783 msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
784 ||msg3.message_text||':'||bl.resource_list_member_name||'; '||msg4.message_text||':'
785 ||bl.txn_currency_code||'; '||msg5.message_text||':'||bl.start_date||'; '
786 ||(substr(bl.pfc_conv_rejection_msg_data,instr(bl.pfc_conv_rejection_msg_data,';',-1)+1)))
787 ,decode(bl.other_rejection_code,NULL,NULL,
788 msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
789 ||msg3.message_text||':'||bl.resource_list_member_name||'; '||msg4.message_text||':'
790 ||bl.txn_currency_code||'; '||msg5.message_text||':'||bl.start_date||'; '
791 ||(substr(bl.other_rejection_msg_data,instr(bl.other_rejection_msg_data,';',-1)+1)))
792 FROM pa_fp_budget_line_rejections_v bl
793 ,fnd_new_messages msg1
794 ,fnd_new_messages msg2
795 ,fnd_new_messages msg3
796 ,fnd_new_messages msg4
797 ,fnd_new_messages msg5
798 WHERE bl.budget_version_id = p_budget_version_id
799 and msg1.message_name = 'PA_FP_PROJ_LABEL'
800 and msg1.application_id = 275
801 and msg1.language_code = userenv('LANG')
802 and msg2.message_name = 'PA_FP_TASK_LABEL'
803 and msg2.application_id = 275
804 and msg2.language_code = userenv('LANG')
805 and msg3.message_name = 'PA_FP_RES_LABEL'
806 and msg3.application_id = 275
807 and msg3.language_code = userenv('LANG')
808 and msg4.message_name = 'PA_FP_CURR_LABEL'
809 and msg4.application_id = 275
810 and msg4.language_code = userenv('LANG')
811 and msg5.message_name = 'PA_FP_DATE_LABEL'
812 and msg5.application_id = 275
813 and msg5.language_code = userenv('LANG')
814 and ( bl.cost_rejection_code is NOT NULL
815 OR bl.burden_rejection_code is NOT NULL
816 OR bl.revenue_rejection_code is NOT NULL
817 OR bl.pc_cur_conv_rejection_code is NOT NULL
818 OR bl.pfc_cur_conv_rejection_code is NOT NULL
819 OR bl.other_rejection_code is NOT NULL
820 );
821
822 l_proc_name varchar2(200) := 'Get_AMG_BdgtLineRejctions';
823
824 BEGIN
825 l_stage := 'Begin Get_AMGBdgtLineRejctions IN Params:BdgtVer['||p_budget_version_id||']';
826 print_msg(g_debug_flag,l_proc_name,l_stage);
827 x_return_status := 'S';
828 /* Initialize the out varrys */
829 x_budget_line_id_tab.delete;
830 x_cost_rejection_data_tab.delete;
831 x_burden_rejection_data_tab.delete;
832 x_revenue_rejection_data_tab.delete;
833 x_pc_conv_rejection_data_tab.delete;
834 x_pfc_conv_rejection_data_tab.delete;
835 x_other_rejection_data_tab.delete;
836
837 OPEN cur_AmgBdgtRejctions;
838 FETCH cur_AmgBdgtRejctions BULK COLLECT INTO
839 l_tab_project_id
840 ,l_tab_task_id
841 ,l_tab_budget_version_id
842 ,l_tab_budget_line_id
843 ,l_tab_res_assignment_id
844 ,l_tab_resource_name
845 ,l_tab_curr_code
846 ,l_tab_period_name
847 ,l_tab_start_date
848 ,l_tab_end_date
849 ,l_tab_cost_rejection_code
850 ,l_tab_burd_rejection_code
851 ,l_tab_revn_rejection_code
852 ,l_tab_pc_rejection_code
853 ,l_tab_pfc_rejection_code
854 ,l_tab_othr_rejection_code
855 ,l_tab_cost_rejection_data
856 ,l_tab_burd_rejection_data
857 ,l_tab_revn_rejection_data
858 ,l_tab_pc_rejection_data
859 ,l_tab_pfc_rejection_data
860 ,l_tab_othr_rejection_data
861 ;
862 CLOSE cur_AmgBdgtRejctions;
863
864 l_stage := 'Num Of Rows Fetched ['||l_tab_res_assignment_id.count||']';
865 print_msg(g_debug_flag,l_proc_name,l_stage);
866
867 /* loop through each plsql tables and add it to error msg stack if there is any error */
868 /****** this is commented out as the calling api will add it to the msg stack
869 FOR i IN l_tab_budget_version_id.FIRST .. l_tab_budget_version_id.LAST LOOP
870 If l_tab_cost_rejection_code(i) is NOT NULL Then
871 AddMsgtoStack(p_msg_code => l_tab_cost_rejection_code(i)
872 ,p_project_token => l_tab_project_id(i)
873 ,p_task_token => l_tab_task_id(i)
874 ,p_budgt_token => l_tab_budget_version_id(i)
875 ,p_resource_token => l_tab_resource_name(i)
876 ,p_currency_token => l_tab_curr_code(i)
877 ,p_date_token => l_tab_start_date(i)
878 );
879 End If;
880 If l_tab_burd_rejection_code(i) is NOT NULL Then
881 AddMsgtoStack(p_msg_code => l_tab_burd_rejection_code(i)
882 ,p_project_token => l_tab_project_id(i)
883 ,p_task_token => l_tab_task_id(i)
884 ,p_budgt_token => l_tab_budget_version_id(i)
885 ,p_resource_token => l_tab_resource_name(i)
886 ,p_currency_token => l_tab_curr_code(i)
887 ,p_date_token => l_tab_start_date(i)
888 );
889 End If;
890 If l_tab_revn_rejection_code(i) is NOT NULL then
891 AddMsgtoStack(p_msg_code => l_tab_revn_rejection_code(i)
892 ,p_project_token => l_tab_project_id(i)
893 ,p_task_token => l_tab_task_id(i)
894 ,p_budgt_token => l_tab_budget_version_id(i)
895 ,p_resource_token => l_tab_resource_name(i)
896 ,p_currency_token => l_tab_curr_code(i)
897 ,p_date_token => l_tab_start_date(i)
898 );
899 End If;
900 If l_tab_pc_rejection_code(i) is NOT NULL Then
901 AddMsgtoStack(p_msg_code => l_tab_pc_rejection_code(i)
902 ,p_project_token => l_tab_project_id(i)
903 ,p_task_token => l_tab_task_id(i)
904 ,p_budgt_token => l_tab_budget_version_id(i)
905 ,p_resource_token => l_tab_resource_name(i)
906 ,p_currency_token => l_tab_curr_code(i)
907 ,p_date_token => l_tab_start_date(i)
908 );
909 End If;
910 If l_tab_pfc_rejection_code(i) is NOT NULL Then
911 AddMsgtoStack(p_msg_code => l_tab_pfc_rejection_code(i)
912 ,p_project_token => l_tab_project_id(i)
913 ,p_task_token => l_tab_task_id(i)
914 ,p_budgt_token => l_tab_budget_version_id(i)
915 ,p_resource_token => l_tab_resource_name(i)
916 ,p_currency_token => l_tab_curr_code(i)
917 ,p_date_token => l_tab_start_date(i)
918 );
919 End If;
920 If l_tab_othr_rejection_code(i) is NOT NULL Then
921 AddMsgtoStack(p_msg_code => l_tab_othr_rejection_code(i)
922 ,p_project_token => l_tab_project_id(i)
923 ,p_task_token => l_tab_task_id(i)
924 ,p_budgt_token => l_tab_budget_version_id(i)
925 ,p_resource_token => l_tab_resource_name(i)
926 ,p_currency_token => l_tab_curr_code(i)
927 ,p_date_token => l_tab_start_date(i)
928 );
929 End If;
930 END LOOP;
931 */
932
933 /* assign the values to OUT varry */
934 x_budget_line_id_tab := l_tab_budget_line_id;
935 x_cost_rejection_data_tab := l_tab_cost_rejection_data;
936 x_burden_rejection_data_tab := l_tab_burd_rejection_data;
937 x_revenue_rejection_data_tab := l_tab_revn_rejection_data;
938 x_pc_conv_rejection_data_tab := l_tab_pc_rejection_data;
939 x_pfc_conv_rejection_data_tab := l_tab_pfc_rejection_data;
940 x_other_rejection_data_tab := l_tab_othr_rejection_data;
941
942 l_stage := 'End Get_BdgtLineRejections';
943 print_msg(g_debug_flag,l_proc_name,l_stage);
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 x_return_status := 'U';
948 print_msg('Y',l_proc_name,l_stage||SQLCODE||SQLERRM);
949 Raise;
950
951 END Get_AMG_BdgtLineRejctions;
952
953 FUNCTION GetPeriodMask(p_period_name IN VARCHAR2)
954 RETURN varchar2 IS
955
956 BEGIN
957 Return p_period_name;
958
959 END GetPeriodMask;
960
961 /* THIS API is called from EditBudgetLineDetails.java page
962 * This api validates the currency conversion parameters and updates the pa_budget_lines table
963 * if there is any changes in the currency conversion attributes, it calls calculate api ()
964 * THIS API SHOULD NOT BE CALLED FROM ANY OTHER SOURCE, IF SO ALL THE PARAMETER VALUES MUST BE PASSED
965 * This api will not default any parameter values. This is similar to a table handler
966 */
967 PROCEDURE validateAndUpdateBdgtLine(
968 p_budget_line_id IN Number
969 ,p_BDGT_VERSION_ID IN Number
970 ,p_RES_ASSIGNMENT_ID IN Number
971 ,p_TXN_CURRENCY_CODE IN Varchar2
972 ,p_START_DATE IN Date
973 ,p_END_DATE IN Date
974 ,P_CALLING_CONTEXT IN Varchar2
975 ,P_ORG_ID IN Number
976 ,p_PLAN_VERSION_TYPE IN Varchar2
977 ,p_PROJFUNC_CURRENCY_CODE IN Varchar2
978 ,p_PROJFUNC_COST_RATE_TYPE IN Varchar2
979 ,p_PROJFUNC_COST_EXCHANGE_RATE IN Number
980 ,p_PROJFUNC_COST_RATE_DATE_TYPE IN Varchar2
981 ,p_PROJFUNC_COST_RATE_DATE IN Date
982 ,p_PROJFUNC_REV_RATE_TYPE IN Varchar2
983 ,p_PROJFUNC_REV_EXCHANGE_RATE IN Number
984 ,p_PROJFUNC_REV_RATE_DATE_TYPE IN Varchar2
985 ,p_PROJFUNC_REV_RATE_DATE IN Date
986 ,p_PROJECT_CURRENCY_CODE IN Varchar2
987 ,p_PROJECT_COST_RATE_TYPE IN Varchar2
988 ,p_PROJECT_COST_EXCHANGE_RATE IN Number
989 ,p_PROJECT_COST_RATE_DATE_TYPE IN Varchar2
990 ,p_PROJECT_COST_RATE_DATE IN Date
991 ,p_PROJECT_REV_RATE_TYPE IN Varchar2
992 ,p_PROJECT_REV_EXCHANGE_RATE IN Number
993 ,p_PROJECT_REV_RATE_DATE_TYPE IN Varchar2
994 ,p_PROJECT_REV_RATE_DATE IN Date
995 ,p_CHANGE_REASON_CODE IN Varchar2
996 ,p_DESCRIPTION IN Varchar2
997 ,p_ATTRIBUTE_CATEGORY IN Varchar2
998 ,p_ATTRIBUTE1 IN Varchar2
999 ,p_ATTRIBUTE2 IN Varchar2
1000 ,p_ATTRIBUTE3 IN Varchar2
1001 ,p_ATTRIBUTE4 IN Varchar2
1002 ,p_ATTRIBUTE5 IN Varchar2
1003 ,p_ATTRIBUTE6 IN Varchar2
1004 ,p_ATTRIBUTE7 IN Varchar2
1005 ,p_ATTRIBUTE8 IN Varchar2
1006 ,p_ATTRIBUTE9 IN Varchar2
1007 ,p_ATTRIBUTE10 IN Varchar2
1008 ,p_ATTRIBUTE11 IN Varchar2
1009 ,p_ATTRIBUTE12 IN Varchar2
1010 ,p_ATTRIBUTE13 IN Varchar2
1011 ,p_ATTRIBUTE14 IN Varchar2
1012 ,p_ATTRIBUTE15 IN Varchar2
1013 ,p_CI_ID IN Number
1014 ,x_return_status OUT NOCOPY Varchar2
1015 ,x_msg_data OUT NOCOPY Varchar2
1016 ,x_msg_count OUT NOCOPY Number
1017 ) IS
1018
1019 cursor check_bdgtLine_changed IS
1020 SELECT project_currency_code
1021 ,project_cost_rate_type
1022 ,project_cost_rate_date
1023 ,project_cost_rate_date_type
1024 ,project_cost_exchange_rate
1025 ,project_rev_rate_type
1026 ,project_rev_rate_date
1027 ,project_rev_rate_date_type
1028 ,project_rev_exchange_rate
1029 ,projfunc_currency_code
1030 ,projfunc_cost_rate_type
1031 ,projfunc_cost_rate_date
1032 ,projfunc_cost_rate_date_type
1033 ,projfunc_cost_exchange_rate
1034 ,projfunc_rev_rate_type
1035 ,projfunc_rev_rate_date
1036 ,projfunc_rev_rate_date_type
1037 ,projfunc_rev_exchange_rate
1038 FROM pa_budget_lines
1039 Where budget_line_id = p_budget_line_id;
1040
1041 Cursor projDetails IS
1042 Select bv.project_id
1043 ,bv.etc_start_date
1044 ,bv.version_type
1045 From pa_budget_versions bv
1046 Where bv.budget_version_id = p_bdgt_version_id;
1047
1048 l_stage varchar2(1000);
1049 l_call_calculate_api Varchar2(1) := 'N';
1050 l_error_msg_code Varchar2(1000) := Null;
1051 l_msg_count Number := 0;
1052 l_return_status Varchar2(100) := 'S';
1053 l_msg_index_out Number := 0;
1054 l_rowcount Number := 0;
1055 l_project_id Number := 0;
1056 l_etc_start_date Date;
1057 l_plan_version_type Varchar2(100);
1058
1059 l_resource_assignment_tab system.pa_num_tbl_type := system.pa_num_tbl_type();
1060 l_txn_currency_code_tab system.pa_varchar2_15_tbl_type := system.pa_varchar2_15_tbl_type();
1061 l_line_start_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1062 l_line_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1063
1064 /* Bug fix: 3964805 Declare the local variables as IN params cannot be assigned */
1065 l_PROJFUNC_COST_RATE_TYPE Varchar2(100);
1066 l_PROJFUNC_COST_EXCHANGE_RATE Number;
1067 l_PROJFUNC_COST_RATE_DATE_TYPE Varchar2(100);
1068 l_PROJFUNC_COST_RATE_DATE Date;
1069 l_PROJFUNC_REV_RATE_TYPE Varchar2(100);
1070 l_PROJFUNC_REV_EXCHANGE_RATE Number;
1071 l_PROJFUNC_REV_RATE_DATE_TYPE Varchar2(100);
1072 l_PROJFUNC_REV_RATE_DATE Date;
1073
1074 l_PROJECT_COST_RATE_TYPE Varchar2(100);
1075 l_PROJECT_COST_EXCHANGE_RATE Number;
1076 l_PROJECT_COST_RATE_DATE_TYPE Varchar2(100);
1077 l_PROJECT_COST_RATE_DATE Date;
1078 l_PROJECT_REV_RATE_TYPE Varchar2(100);
1079 l_PROJECT_REV_EXCHANGE_RATE Number;
1080 l_PROJECT_REV_RATE_DATE_TYPE Varchar2(100);
1081 l_PROJECT_REV_RATE_DATE Date;
1082
1083
1084 l_proc_name varchar2(100) := 'validateAndUpdateBdgtLine';
1085
1086 FUNCTION getMsgtext(p_msg_name IN Varchar2) Return Varchar2 IS
1087
1088 Cursor getMsgDetails IS
1089 Select substr(msg.message_text,instr(msg.message_text,';',-1)+1) message_text
1090 From fnd_new_messages msg
1091 Where msg.application_id = 275
1092 And msg.message_name = p_msg_name
1093 And msg.language_code = userenv('LANG');
1094
1095 l_msg_text Varchar2(1000);
1096 BEGIN
1097 OPEN getMsgDetails;
1098 FETCH getMsgDetails INTO l_msg_text;
1099 CLOSE getMsgDetails;
1100 RETURN l_msg_text;
1101
1102 END getMsgtext ;
1103 BEGIN
1104 --- Initialize the error statck
1105 IF p_pa_debug_mode = 'Y' THEN
1106 PA_DEBUG.init_err_stack ('PA_FIN_PLAN_UTILS2.validateAndUpdateBdgtLine');
1107 END IF;
1108 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
1109 g_debug_flag := NVL(g_debug_flag, 'N');
1110
1111 IF p_pa_debug_mode = 'Y' THEN
1112 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
1113 ,x_write_file => 'LOG'
1114 ,x_debug_mode => g_debug_flag
1115 );
1116 END IF;
1117 /* Initialize the out params */
1118 x_return_status := 'S';
1119 x_msg_data := Null;
1120 x_msg_count := 0;
1121
1122 /** clear the message stack **/
1123 fnd_msg_pub.INITIALIZE;
1124
1125 l_stage := 'Inside validateAndUpdateBdgtLine API BdgtLineId['||p_budget_line_id||']ResAssingID['||p_res_assignment_id||
1126 ']txnCurrCode['||p_txn_currency_code||']StartDate['||p_start_date||']EndDate['||p_end_date||']BdgtVerId['||p_BDGT_VERSION_ID||
1127 ']PlanVerType['||p_plan_version_type||']Callingcontext['||p_calling_context||']';
1128 print_msg(g_debug_flag,l_proc_name,l_stage);
1129
1130 OPEN projDetails;
1131 FETCH projDetails INTO
1132 l_project_id
1133 ,l_etc_start_date
1134 ,l_plan_version_type;
1135 CLOSE projDetails;
1136
1137 /* Bug fix: 3964805 Initialize the local variables */
1138 l_PROJFUNC_COST_RATE_TYPE := p_PROJFUNC_COST_RATE_TYPE;
1139 l_PROJFUNC_COST_EXCHANGE_RATE := p_PROJFUNC_COST_EXCHANGE_RATE;
1140 l_PROJFUNC_COST_RATE_DATE_TYPE := p_PROJFUNC_COST_RATE_DATE_TYPE;
1141 l_PROJFUNC_COST_RATE_DATE := p_PROJFUNC_COST_RATE_DATE;
1142 l_PROJFUNC_REV_RATE_TYPE := p_PROJFUNC_REV_RATE_TYPE;
1143 l_PROJFUNC_REV_EXCHANGE_RATE := p_PROJFUNC_REV_EXCHANGE_RATE;
1144 l_PROJFUNC_REV_RATE_DATE_TYPE := p_PROJFUNC_REV_RATE_DATE_TYPE;
1145 l_PROJFUNC_REV_RATE_DATE :=p_PROJFUNC_REV_RATE_DATE;
1146
1147 l_PROJECT_COST_RATE_TYPE := p_PROJECT_COST_RATE_TYPE;
1148 l_PROJECT_COST_EXCHANGE_RATE := p_PROJECT_COST_EXCHANGE_RATE;
1149 l_PROJECT_COST_RATE_DATE_TYPE := p_PROJECT_COST_RATE_DATE_TYPE;
1150 l_PROJECT_COST_RATE_DATE := p_PROJECT_COST_RATE_DATE;
1151 l_PROJECT_REV_RATE_TYPE := p_PROJECT_REV_RATE_TYPE;
1152 l_PROJECT_REV_EXCHANGE_RATE :=p_PROJECT_REV_EXCHANGE_RATE;
1153 l_PROJECT_REV_RATE_DATE_TYPE := p_PROJECT_REV_RATE_DATE_TYPE;
1154 l_PROJECT_REV_RATE_DATE := p_PROJECT_REV_RATE_DATE;
1155
1156 l_stage := 'BudgetVersionType['||l_PLAN_VERSION_TYPE||']';
1157 l_stage := 'Values PassedIn FuncstRatTyp['||l_PROJFUNC_COST_RATE_TYPE||']FuncstRate['||l_PROJFUNC_COST_EXCHANGE_RATE||']';
1158 l_stage := l_stage||'Funcstdttype['||l_PROJFUNC_COST_RATE_DATE_TYPE||']Funcdt['||l_PROJFUNC_COST_RATE_DATE||']';
1159 l_stage := l_stage||'FuncRevTyp['||l_PROJFUNC_REV_RATE_TYPE||']FuncRt['||l_PROJFUNC_REV_EXCHANGE_RATE||']';
1160 l_stage := l_stage||'FuncDttyp['||l_PROJFUNC_REV_RATE_DATE_TYPE||']FuncDt['||l_PROJFUNC_REV_RATE_DATE||']';
1161 print_msg(g_debug_flag,l_proc_name,l_stage);
1162
1163 l_stage := 'ProjCstRtTyp['||l_PROJECT_COST_RATE_TYPE||']Rate['||l_PROJECT_COST_EXCHANGE_RATE||']Dttyp['||l_PROJECT_COST_RATE_DATE_TYPE||']';
1164 l_stage := l_stage||'RtDt['||l_PROJECT_COST_RATE_DATE||']ProjRevRtTyp['||l_PROJECT_REV_RATE_TYPE||']Rt['||l_PROJECT_REV_EXCHANGE_RATE||']';
1165 l_stage := l_stage||'RrDtTyp['||l_PROJECT_REV_RATE_DATE_TYPE||']RvDt['||l_PROJECT_REV_RATE_DATE||']';
1166 print_msg(g_debug_flag,l_proc_name,l_stage);
1167
1168 If (p_budget_line_id is not null AND
1169 (p_txn_currency_code <> p_project_currency_code OR
1170 p_txn_currency_code <> p_projfunc_currency_code)) then
1171
1172 /* Bug fix: 3964805 If PC = PFC then UI should display PFC attributes only and backend copy the
1173 * PFC attributes to PC attributes
1174 */
1175 IF p_project_currency_code = p_projfunc_currency_code Then
1176 -- ignore any changes made to project currency attributes. copy pfc attributes to pc attributes
1177 l_stage := 'Proj and ProjFunc currencies are Same';
1178 print_msg(g_debug_flag,l_proc_name,l_stage);
1179 IF l_PLAN_VERSION_TYPE in ('COST','ALL') Then
1180 l_PROJECT_COST_RATE_TYPE := l_PROJFUNC_COST_RATE_TYPE;
1181 l_PROJECT_COST_EXCHANGE_RATE := l_PROJFUNC_COST_EXCHANGE_RATE;
1182 l_PROJECT_COST_RATE_DATE_TYPE := l_PROJFUNC_COST_RATE_DATE_TYPE;
1183 l_PROJECT_COST_RATE_DATE := l_PROJFUNC_COST_RATE_DATE;
1184 End If;
1185
1186 IF l_PLAN_VERSION_TYPE in ('REVENUE','ALL') Then
1187 l_PROJECT_REV_RATE_TYPE := l_PROJFUNC_REV_RATE_TYPE;
1188 l_PROJECT_REV_EXCHANGE_RATE := l_PROJFUNC_REV_EXCHANGE_RATE;
1189 l_PROJECT_REV_RATE_DATE_TYPE := l_PROJFUNC_REV_RATE_DATE_TYPE;
1190 l_PROJECT_REV_RATE_DATE := l_PROJFUNC_REV_RATE_DATE;
1191 End If;
1192 End If;
1193 /* End of bug fix:3964805 */
1194
1195 l_stage := 'Trxn Currency PC / PFC are different';
1196 print_msg(g_debug_flag,l_proc_name,l_stage);
1197 FOR i IN check_bdgtLine_changed LOOP
1198 l_msg_count := 0;
1199 l_stage := 'Validate currency attributes';
1200 print_msg(g_debug_flag,l_proc_name,l_stage);
1201 IF (NVL(i.project_cost_rate_type,'XX') <> NVL(l_project_cost_rate_type,'XX') OR
1202 NVL(i.project_cost_rate_date,trunc(sysdate)) <> NVL(l_project_cost_rate_date,trunc(sysdate)) OR
1203 NVL(i.project_cost_rate_date_type,'XX') <> NVL(l_project_cost_rate_date_type,'XX') OR
1204 NVL(i.project_cost_exchange_rate,0) <> NVL(l_project_cost_exchange_rate,0)) Then
1205 l_call_calculate_api := 'Y';
1206 If NVL(l_project_cost_rate_type,'XX') = 'User' Then
1207 l_stage := 'Calling checkUserRateAllowed For l_project_cost_rate_type';
1208 checkUserRateAllowed
1209 (p_From_curr_code => p_txn_currency_code
1210 ,p_To_curr_code => p_project_currency_code
1211 ,p_Conversion_Date => p_start_date
1212 ,p_To_Curr_Rate_Type => l_project_cost_rate_type
1213 ,p_To_Curr_Exchange_Rate => l_project_cost_exchange_rate
1214 ,p_calling_mode => 'PC'
1215 ,p_calling_context => 'COST'
1216 ,x_return_status => l_return_status
1217 ,x_error_msg_code => l_error_msg_code
1218 ) ;
1219 l_stage :='ReturnSTst['||l_return_status||']l_error_msg_code['||l_error_msg_code||']';
1220 print_msg(g_debug_flag,l_proc_name,l_stage);
1221
1222 If l_return_status <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
1223 --l_error_msg_code := getMsgtext(p_msg_name =>l_error_msg_code);
1224 PA_UTILS.ADD_MESSAGE
1225 ( p_app_short_name => 'PA'
1226 ,p_msg_name =>l_error_msg_code
1227 );
1228 l_msg_count := l_msg_count +1;
1229 End if;
1230 Elsif NVL(l_project_cost_rate_type,'XX') <> 'User' Then
1231 l_project_cost_exchange_rate := NULL;
1232 End If; -- end of projcostratetype
1233 END IF;
1234
1235 IF (NVL(i.project_rev_rate_type,'XX') <> NVL(l_project_rev_rate_type,'XX') OR
1236 NVL(i.project_rev_rate_date,trunc(sysdate)) <> NVL(l_project_rev_rate_date,trunc(sysdate)) OR
1237 NVL(i.project_rev_rate_date_type,'XX') <> NVL(l_project_rev_rate_date_type,'XX') OR
1238 NVL(i.project_rev_exchange_rate,0) <> NVL(l_project_rev_exchange_rate,0)) Then
1239 l_call_calculate_api := 'Y';
1240 If NVL(l_project_rev_rate_type,'XX') = 'User' Then
1241 l_stage := 'Calling checkUserRateAllowed For l_project_rev_rate_type';
1242 print_msg(g_debug_flag,l_proc_name,l_stage);
1243 checkUserRateAllowed
1244 (p_From_curr_code => p_txn_currency_code
1245 ,p_To_curr_code => p_project_currency_code
1246 ,p_Conversion_Date => p_start_date
1247 ,p_To_Curr_Rate_Type => l_project_rev_rate_type
1248 ,p_To_Curr_Exchange_Rate => l_project_rev_exchange_rate
1249 ,p_calling_mode => 'PC'
1250 ,p_calling_context => 'REV'
1251 ,x_return_status => l_return_status
1252 ,x_error_msg_code => l_error_msg_code
1253 ) ;
1254 l_stage :='ReturnSTst['||l_return_status||']l_error_msg_code['||l_error_msg_code||']';
1255 print_msg(g_debug_flag,l_proc_name,l_stage);
1256
1257 If l_return_status <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
1258 --l_error_msg_code := getMsgtext(p_msg_name =>l_error_msg_code);
1259 PA_UTILS.ADD_MESSAGE
1260 ( p_app_short_name => 'PA'
1261 ,p_msg_name =>l_error_msg_code
1262 );
1263 l_msg_count := l_msg_count +1;
1264 End if;
1265 Elsif NVL(l_project_rev_rate_type,'XX') <> 'User' Then
1266 l_project_rev_exchange_rate := NULL;
1267 End If;
1268 END IF;
1269
1270 IF (NVL(i.projfunc_cost_rate_type,'XX') <> NVL(l_projfunc_cost_rate_type,'XX') OR
1271 NVL(i.projfunc_cost_rate_date,trunc(sysdate)) <> NVL(l_projfunc_cost_rate_date,trunc(sysdate)) OR
1272 NVL(i.projfunc_cost_rate_date_type,'XX') <> NVL(l_projfunc_cost_rate_date_type,'XX') OR
1273 NVL(i.projfunc_cost_exchange_rate,0) <> NVL(l_projfunc_cost_exchange_rate,0)) THEN
1274 l_call_calculate_api := 'Y';
1275 If NVL(l_projfunc_cost_rate_type,'XX') = 'User' Then
1276 l_stage := 'Calling checkUserRateAllowed For l_projfunc_cost_rate_type';
1277 print_msg(g_debug_flag,l_proc_name,l_stage);
1278 checkUserRateAllowed
1279 (p_From_curr_code => p_txn_currency_code
1280 ,p_To_curr_code => p_projfunc_currency_code
1281 ,p_Conversion_Date => p_start_date
1282 ,p_To_Curr_Rate_Type => l_projfunc_cost_rate_type
1283 ,p_To_Curr_Exchange_Rate => l_projfunc_cost_exchange_rate
1284 ,p_calling_mode => 'PFC'
1285 ,p_calling_context => 'COST'
1286 ,x_return_status => l_return_status
1287 ,x_error_msg_code => l_error_msg_code
1288 ) ;
1289 l_stage :='ReturnSTst['||l_return_status||']l_error_msg_code['||l_error_msg_code||']';
1290 print_msg(g_debug_flag,l_proc_name,l_stage);
1291
1292 If l_return_status <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
1293 --l_error_msg_code := getMsgtext(p_msg_name =>l_error_msg_code);
1294 PA_UTILS.ADD_MESSAGE
1295 ( p_app_short_name => 'PA'
1296 ,p_msg_name =>l_error_msg_code
1297 );
1298 l_msg_count := l_msg_count +1;
1299 End if;
1300 Elsif NVL(l_projfunc_cost_rate_type,'XX') <> 'User' Then
1301 l_projfunc_cost_exchange_rate := NULL;
1302 End If;
1303 END IF;
1304
1305
1306 IF (NVL(i.projfunc_rev_rate_type,'XX') <> NVL(l_projfunc_rev_rate_type,'XX') OR
1307 NVL(i.projfunc_rev_rate_date,trunc(sysdate)) <> NVL(l_projfunc_rev_rate_date,trunc(sysdate)) OR
1308 NVL(i.projfunc_rev_rate_date_type,'XX') <> NVL(l_projfunc_rev_rate_date_type,'XX') OR
1309 NVL(i.projfunc_rev_exchange_rate ,0) <> NVL(l_projfunc_rev_exchange_rate ,0)) THEN
1310 l_call_calculate_api := 'Y';
1311 If NVL(l_projfunc_rev_rate_type,'XX') = 'User' Then
1312 l_stage := 'Calling checkUserRateAllowed For l_projfunc_rev_rate_type';
1313 print_msg(g_debug_flag,l_proc_name,l_stage);
1314 checkUserRateAllowed
1315 (p_From_curr_code => p_txn_currency_code
1316 ,p_To_curr_code => p_projfunc_currency_code
1317 ,p_Conversion_Date => p_start_date
1318 ,p_To_Curr_Rate_Type => l_projfunc_rev_rate_type
1319 ,p_To_Curr_Exchange_Rate => l_projfunc_rev_exchange_rate
1320 ,p_calling_mode => 'PFC'
1321 ,p_calling_context => 'REV'
1322 ,x_return_status => l_return_status
1323 ,x_error_msg_code => l_error_msg_code
1324 ) ;
1325 l_stage :='ReturnSTst['||l_return_status||']l_error_msg_code['||l_error_msg_code||']';
1326 print_msg(g_debug_flag,l_proc_name,l_stage);
1327
1328 If l_return_status <> 'S' and nvl(l_error_msg_code,'X') <> 'X' then
1329 --l_error_msg_code := getMsgtext(p_msg_name =>l_error_msg_code);
1330 PA_UTILS.ADD_MESSAGE
1331 ( p_app_short_name => 'PA'
1332 ,p_msg_name =>l_error_msg_code
1333 );
1334 l_msg_count := l_msg_count +1;
1335 End if;
1336 Elsif NVL(l_projfunc_rev_rate_type,'XX') <> 'User' Then
1337 l_projfunc_rev_exchange_rate := NULL;
1338 End If;
1339 END IF;
1340 /* Retrive the msg from stack */
1341 l_stage := 'Message Count['||l_msg_count||']';
1342 print_msg(g_debug_flag,l_proc_name,l_stage);
1343 If l_msg_count = 1 then
1344 pa_interface_utils_pub.get_messages
1345 ( p_encoded => FND_API.G_TRUE
1346 ,p_msg_index => 1
1347 ,p_data => x_msg_data
1348 ,p_msg_index_out => l_msg_index_out
1349 );
1350 x_return_status := 'E';
1351 Elsif l_msg_count > 1 then
1352 x_return_status := 'E';
1353 x_msg_count := l_msg_count;
1354 x_msg_data := null;
1355 End if;
1356
1357 END LOOP;
1358 End if;
1359
1360 IF ( x_return_status = 'S' and
1361 p_res_assignment_id is NOT NULL and
1362 p_txn_currency_code is NOT NULL and
1363 p_start_date is NOT NULL and
1364 p_end_date is NOT NULL ) Then
1365 l_stage := 'Updateing the budget lines with currency attributes';
1366 print_msg(g_debug_flag,l_proc_name,l_stage);
1367 UPDATE pa_budget_lines bl
1368 SET bl.description = nvl(p_description,bl.description)
1369 ,bl.change_reason_code = nvl(p_change_reason_code,bl.change_reason_code)
1370 ,bl.attribute_category = nvl(p_attribute_category,bl.attribute_category)
1371 ,bl.attribute1 = nvl(p_attribute1,bl.attribute1)
1372 ,bl.attribute2 = nvl(p_attribute2,bl.attribute2)
1373 ,bl.attribute3 = nvl(p_attribute3,bl.attribute3)
1374 ,bl.attribute4 = nvl(p_attribute4,bl.attribute4)
1375 ,bl.attribute5 = nvl(p_attribute5,bl.attribute5)
1376 ,bl.attribute6 = nvl(p_attribute6,bl.attribute6)
1377 ,bl.attribute7 = nvl(p_attribute7,bl.attribute7)
1378 ,bl.attribute8 = nvl(p_attribute8,bl.attribute8)
1379 ,bl.attribute9 = nvl(p_attribute9,bl.attribute9)
1380 ,bl.attribute10 = nvl(p_attribute10,bl.attribute10)
1381 ,bl.attribute11 = nvl(p_attribute11,bl.attribute11)
1382 ,bl.attribute12 = nvl(p_attribute12,bl.attribute12)
1383 ,bl.attribute13 = nvl(p_attribute13,bl.attribute13)
1384 ,bl.attribute14 = nvl(p_attribute14,bl.attribute14)
1385 ,bl.attribute15 = nvl(p_attribute15,bl.attribute15)
1386 ---- cost attributes
1387 ,bl.project_cost_rate_type = decode(p_txn_currency_code,p_project_currency_code,bl.project_cost_rate_type
1388 ,decode(l_plan_version_type,'COST'
1389 ,l_project_cost_rate_type
1390 ,'ALL',l_project_cost_rate_type
1391 ,bl.project_cost_rate_type))
1392 ,bl.project_cost_rate_date_type = decode(p_txn_currency_code,p_project_currency_code,
1393 bl.project_cost_rate_date_type,decode(l_plan_version_type,'COST'
1394 ,l_project_cost_rate_date_type
1395 ,'ALL',l_project_cost_rate_date_type
1396 ,bl.project_cost_rate_date_type))
1397 ,bl.project_cost_rate_date = decode(p_txn_currency_code,p_project_currency_code,
1398 bl.project_cost_rate_date,decode(l_plan_version_type,'COST'
1399 ,l_project_cost_rate_date
1400 ,'ALL',l_project_cost_rate_date
1401 ,bl.project_cost_rate_date ))
1402 ,bl.project_cost_exchange_rate = decode(p_txn_currency_code,p_project_currency_code,
1403 bl.project_cost_exchange_rate,decode(l_plan_version_type,'COST'
1404 ,l_project_cost_exchange_rate
1405 ,'ALL',l_project_cost_exchange_rate
1406 ,bl.project_cost_exchange_rate))
1407 ,bl.projfunc_cost_rate_type = decode(p_txn_currency_code,p_projfunc_currency_code,
1408 bl.projfunc_cost_rate_type,decode(l_plan_version_type,'COST'
1409 ,l_projfunc_cost_rate_type
1410 ,'ALL',l_projfunc_cost_rate_type
1411 ,bl.projfunc_cost_rate_type))
1412 ,bl.projfunc_cost_rate_date_type = decode(p_txn_currency_code,p_projfunc_currency_code,
1413 bl.projfunc_cost_rate_date_type,decode(l_plan_version_type,'COST'
1414 ,l_projfunc_cost_rate_date_type
1415 ,'ALL',l_projfunc_cost_rate_date_type
1416 ,bl.projfunc_cost_rate_date_type))
1417 ,bl.projfunc_cost_rate_date = decode(p_txn_currency_code,p_projfunc_currency_code,
1418 bl.projfunc_cost_rate_date,decode(l_plan_version_type,'COST'
1419 ,l_projfunc_cost_rate_date
1420 ,'ALL',l_projfunc_cost_rate_date
1421 ,bl.projfunc_cost_rate_date ))
1422 ,bl.projfunc_cost_exchange_rate = decode(p_txn_currency_code,p_projfunc_currency_code,
1423 bl.projfunc_cost_exchange_rate,decode(l_plan_version_type,'COST'
1424 ,l_projfunc_cost_exchange_rate
1425 ,'ALL',l_projfunc_cost_exchange_rate
1426 ,bl.projfunc_cost_exchange_rate))
1427 --revenue attributes
1428 ,bl.project_rev_rate_type = decode(p_txn_currency_code,p_project_currency_code,
1429 bl.project_rev_rate_type,decode(l_plan_version_type,'REVENUE'
1430 ,l_project_rev_rate_type
1431 ,'ALL',l_project_rev_rate_type
1432 ,bl.project_rev_rate_type))
1433 ,bl.project_rev_rate_date_type = decode(p_txn_currency_code,p_project_currency_code,
1434 bl.project_rev_rate_date_type,decode(l_plan_version_type,'REVENUE'
1435 ,l_project_rev_rate_date_type
1436 ,'ALL',l_project_rev_rate_date_type
1437 ,bl.project_rev_rate_date_type))
1438 ,bl.project_rev_rate_date = decode(p_txn_currency_code,p_project_currency_code,
1439 bl.project_rev_rate_date,decode(l_plan_version_type,'REVENUE'
1440 ,l_project_rev_rate_date
1441 ,'ALL',l_project_rev_rate_date
1442 ,bl.project_rev_rate_date ))
1443 ,bl.project_rev_exchange_rate = decode(p_txn_currency_code,p_project_currency_code,
1444 bl.project_rev_exchange_rate,decode(l_plan_version_type,'REVENUE'
1445 ,l_project_rev_exchange_rate
1446 ,'ALL',l_project_rev_exchange_rate
1447 ,bl.project_rev_exchange_rate))
1448 ,bl.projfunc_rev_rate_type = decode(p_txn_currency_code,p_projfunc_currency_code,
1449 bl.projfunc_rev_rate_type,decode(l_plan_version_type,'REVENUE'
1450 ,l_projfunc_rev_rate_type
1451 ,'ALL',l_projfunc_rev_rate_type
1452 ,bl.projfunc_rev_rate_type))
1453 ,bl.projfunc_rev_rate_date_type = decode(p_txn_currency_code,p_projfunc_currency_code,
1454 bl.projfunc_rev_rate_date_type,decode(l_plan_version_type,'REVENUE'
1455 ,l_projfunc_rev_rate_date_type
1456 ,'ALL',l_projfunc_rev_rate_date_type
1457 ,bl.projfunc_rev_rate_date_type))
1458 ,bl.projfunc_rev_rate_date = decode(p_txn_currency_code,p_projfunc_currency_code,
1459 bl.projfunc_rev_rate_date,decode(l_plan_version_type,'REVENUE'
1460 ,l_projfunc_rev_rate_date
1461 ,'ALL',l_projfunc_rev_rate_date
1462 ,bl.projfunc_rev_rate_date ))
1463 ,bl.projfunc_rev_exchange_rate = decode(p_txn_currency_code,p_projfunc_currency_code,
1464 bl.projfunc_rev_exchange_rate,decode(l_plan_version_type,'REVENUE'
1465 ,l_projfunc_rev_exchange_rate
1466 ,'ALL',l_projfunc_rev_exchange_rate
1467 ,bl.projfunc_rev_exchange_rate))
1468 WHERE bl.resource_assignment_id = p_res_assignment_id
1469 AND bl.txn_currency_code = p_txn_currency_code
1470 AND bl.start_date BETWEEN trunc(p_start_date) and trunc(p_end_date)
1471 AND bl.end_date BETWEEN trunc(p_start_date) and trunc(p_end_date)
1472 AND (l_etc_start_date is NULL
1473 OR (l_etc_start_date is NOT NULL
1474 and ((l_etc_start_date between bl.start_date and bl.end_date)
1475 or (bl.start_date > l_etc_start_date))
1476 )
1477 );
1478
1479 l_rowcount := sql%rowcount;
1480 l_stage := 'Number of BudgetLines Updated['||l_rowcount||']';
1481 print_msg(g_debug_flag,l_proc_name,l_stage);
1482
1483 If l_rowcount > 0 Then
1484
1485 IF x_return_status = 'S' AND l_call_calculate_api = 'Y' Then
1486 l_stage := 'Calling Calculate API';
1487 print_msg(g_debug_flag,l_proc_name,l_stage);
1488 /* Initialize the arrays to pass to the calculate api */
1489 l_resource_assignment_tab.extend;
1490 l_resource_assignment_tab(1) := p_res_assignment_id;
1491 l_txn_currency_code_tab.extend;
1492 l_txn_currency_code_tab(1) := p_txn_currency_code;
1493 l_line_start_date_tab.extend;
1494 l_line_start_date_tab(1) := p_start_date;
1495 l_line_end_date_tab.extend;
1496 l_line_end_date_tab(1) := p_end_date;
1497 PA_FP_CALC_PLAN_PKG.CALCULATE
1498 (p_project_id => l_project_id
1499 ,p_budget_version_id => p_BDGT_VERSION_ID
1500 ,p_refresh_conv_rates_flag => 'Y'
1501 ,p_source_context => 'BUDGET_LINE'
1502 ,p_resource_assignment_tab => l_resource_assignment_tab
1503 ,p_txn_currency_code_tab => l_txn_currency_code_tab
1504 ,p_line_start_date_tab => l_line_start_date_tab
1505 ,p_line_end_date_tab => l_line_end_date_tab
1506 ,x_return_status => x_return_status
1507 ,x_msg_count => x_msg_count
1508 ,x_msg_data => x_msg_data
1509 );
1510 l_stage := 'End of Calculate API ReturnSTatus['||x_return_status||']MsgCt['||x_msg_count||']MsgData['
1511 ||x_msg_data||']';
1512 print_msg(g_debug_flag,l_proc_name,l_stage);
1513 End IF;
1514
1515 End IF;
1516
1517 End if;
1518
1519 l_stage := 'End of validateAndUpdateBdgtLine API msgCt['||x_msg_count||']RetSts['||x_return_status||']';
1520 print_msg(g_debug_flag,l_proc_name,l_stage);
1521 If x_return_status = 'S' Then
1522 COMMIT;
1523 Else
1524 ROLLBACK;
1525 End If;
1526 IF p_pa_debug_mode = 'Y' THEN
1527 pa_debug.reset_err_stack;
1528 END IF;
1529 Return;
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 x_return_status := 'U';
1534 x_msg_data := SQLCODE||SQLERRM;
1535 x_msg_count := 1;
1536 print_msg('Y',l_proc_name,l_stage||SQLCODE||SQLERRM);
1537 FND_MSG_PUB.add_exc_msg
1538 ( p_pkg_name => 'PA_FIN_PLAN_UTILS2'
1539 ,p_procedure_name => 'validateAndUpdateBdgtLine');
1540 IF p_pa_debug_mode = 'Y' THEN
1541 PA_DEBUG.reset_err_stack;
1542 END IF;
1543 Raise;
1544
1545
1546 END validateAndUpdateBdgtLine;
1547
1548 /* This API returns the period mask to be displayed on the edit budgetline details page
1549 * NOTE: donot use this API for any other purpose
1550 */
1551 PROCEDURE setMaskName(p_period_Mask IN Varchar2) IS
1552
1553 BEGIN
1554 PA_FIN_PLAN_UTILS2.period_mask_display := p_period_Mask;
1555
1556 END setMaskName;
1557
1558 /* This API returns the period mask to be displayed on the edit budgetline details page
1559 * NOTE: donot use this API for any other purpose
1560 */
1561 FUNCTION getMaskName Return Varchar2 IS
1562
1563 BEGIN
1564 RETURN PA_FIN_PLAN_UTILS2.period_mask_display;
1565
1566 END getMaskName;
1567
1568 PROCEDURE checkUserRateAllowed
1569 (p_From_curr_code IN Varchar2
1570 ,p_To_curr_code IN Varchar2
1571 ,p_Conversion_Date IN Date
1572 ,p_To_Curr_Rate_Type IN Varchar2
1573 ,p_To_Curr_Exchange_Rate IN Number
1574 ,p_calling_mode IN Varchar2
1575 ,p_calling_context IN Varchar2
1576 ,x_return_status OUT NOCOPY Varchar2
1577 ,x_error_msg_code OUT NOCOPY Varchar2
1578 ) IS
1579 l_usrRateAllowed Varchar2(10);
1580 l_stage Varchar2(1000);
1581 l_calling_mode Varchar2(100) := p_calling_mode;
1582 l_calling_context Varchar2(100) := p_calling_context;
1583 l_proc_name Varchar2(100) := 'checkUserRateAllowed';
1584 BEGIN
1585 x_return_status := 'S';
1586 x_error_msg_code := NULL;
1587
1588 If p_To_Curr_Rate_Type = 'User' Then
1589 -- check if the user rate type is allowed for this currency
1590 l_stage := 'Calling pa_multi_currency.is_user_rate_type_allowed API()';
1591 print_msg(g_debug_flag,l_proc_name,l_stage);
1592 l_usrRateAllowed := pa_multi_currency.is_user_rate_type_allowed
1593 (P_from_currency => p_From_curr_code
1594 ,P_to_currency => p_To_curr_code
1595 ,P_conversion_date => p_Conversion_Date );
1596 l_stage := 'End of pa_multi_currency API() UserRateAllowedFlag['||l_usrRateAllowed||']';
1597 print_msg(g_debug_flag,l_proc_name,l_stage);
1598 If NVL(l_usrRateAllowed,'N') = 'Y' Then
1599 If p_To_Curr_Exchange_Rate is NULL Then
1600 x_return_status := 'E';
1601 If l_calling_mode = 'PC' Then
1602 If l_calling_context = 'COST' Then
1603 x_error_msg_code := 'PA_FP_PROJ_MISS_COST_RATE';
1604 Else
1605 x_error_msg_code := 'PA_FP_PROJ_MISS_REV_RATE';
1606 End If;
1607 Else
1608 If l_calling_context = 'COST' Then
1609 x_error_msg_code := 'PA_FP_PROJFUNC_MISS_COST_RATE';
1610 Else
1611 x_error_msg_code := 'PA_FP_PROJFUNC_MISS_REV_RATE';
1612 End If;
1613 End If;
1614 End If;
1615
1616 Else -- user rate type is not allowed so error out
1617 x_return_status := 'E';
1618 If l_calling_mode = 'PC' Then
1619 x_error_msg_code := 'PA_FP_PROJ_USR_NOT_ALLOWED';
1620 Else
1621 x_error_msg_code := 'PA_FP_PROJFUNC_USR_NOT_ALLOWED';
1622 End If;
1623
1624 End If;
1625 End If;
1626 l_stage := 'End of checkUserRateAllowed RetSts['||x_return_status||']MsgCode['||x_error_msg_code||']';
1627 print_msg(g_debug_flag,l_proc_name,l_stage);
1628 Return;
1629
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 x_return_status := 'U';
1633 x_error_msg_code :=l_stage||sqlcode||sqlerrm;
1634 print_msg('Y',l_proc_name,l_stage);
1635
1636 END checkUserRateAllowed;
1637
1638 /*
1639 * This API provides the budget line rejections for the given Project STructure
1640 * Version Id and Task Str Version Id
1641 * IN Params:
1642 * p_project_id IN Number Required
1643 * p_calling_mode IN Varchar2 Default 'PROJ_STR_VER'
1644 * the possible values are 'PROJ_STR_VER' or 'TASK_STR_VER'
1645 * p_proj_str_version_id IN Number Required
1646 * p_Task_str_version_id IN Number If calling mode is TASK_STR_VER then it is reqd
1647 * p_start_date IN Date
1648 * If calling mode is TASK_STR_VER then it is reqd.
1649 * value should be periodmask or task start date
1650 * p_end_date IN Date
1651 * If calling mode is TASK_STR_VER then it is reqd.
1652 * value should be periodmask or task end date
1653 * OUT Params:
1654 * x_return_status will be 'U' - in case of unexpected error
1655 * 'E' - in case of expected error - invalid params
1656 * 'S' - in case of success
1657 * If calling mode 'PROJ_STR_VER' then
1658 * x_projstrlvl_rejn_flag will populated
1659 * ElsIf calling mode 'TASK_STR_VER'
1660 * the following out variables will be populated
1661 * x_cost_rejn_flag
1662 * x_burden_rejn_flag
1663 * x_revenue_rejn_flag
1664 * x_pc_conv_rejn_flag
1665 * x_pfc_conv_rejn_flag
1666 * End If;
1667 *
1668 */
1669 PROCEDURE Get_WbsBdgtLineRejns
1670 (p_project_id IN Number
1671 ,p_calling_mode IN Varchar2 Default 'PROJ_STR_VER'
1672 ,p_proj_str_version_id IN Number
1673 ,p_Task_str_version_id IN Number Default Null
1674 ,p_start_date IN Date Default Null
1675 ,p_end_date IN Date Default Null
1676 ,x_cost_rejn_flag OUT NOCOPY Varchar2
1677 ,x_burden_rejn_flag OUT NOCOPY Varchar2
1678 ,x_revenue_rejn_flag OUT NOCOPY Varchar2
1679 ,x_pc_conv_rejn_flag OUT NOCOPY Varchar2
1680 ,x_pfc_conv_rejn_flag OUT NOCOPY Varchar2
1681 ,x_projstrlvl_rejn_flag OUT NOCOPY Varchar2
1682 ,x_return_status OUT NOCOPY Varchar2
1683 ,p_budget_version_id IN Number Default Null --Bug 5611909
1684 ) IS
1685
1686 l_proc_name VARCHAR2(100) := 'Get_WbsBdgtLineRejns';
1687 l_stage VARCHAR2(2000);
1688 l_return_status VARCHAR2(10);
1689 l_budget_version_id Number;
1690 l_bdgtProjStrRejFlag Varchar2(10);
1691 l_bdgtCstRejFlag Varchar2(10);
1692 l_bdgtBdnRejFlag Varchar2(10);
1693 l_bdgtRevnRejFlag Varchar2(10);
1694 l_bdgtPcConvRejFlag Varchar2(10);
1695 l_bdgtPfcConvRejFlag Varchar2(10);
1696 l_debug_flag Varchar2(10);
1697
1698
1699 CURSOR cur_bdgtProjStrRejFlag IS
1700 SELECT 'Y'
1701 FROM DUAL
1702 WHERE EXISTS
1703 (SELECT null
1704 FROM pa_budget_lines bl
1705 WHERE bl.budget_version_id = l_budget_version_id
1706 AND (bl.cost_rejection_code is NOT NULL
1707 OR bl.burden_rejection_code is NOT NULL
1708 OR bl.revenue_rejection_code is NOT NULL
1709 OR bl.pc_cur_conv_rejection_code is NOT NULL
1710 OR bl.pfc_cur_conv_rejection_code is NOT NULL)
1711 );
1712
1713 CURSOR cur_bdgtCstRejFlag IS
1714 SELECT 'Y'
1715 FROM DUAL
1716 WHERE EXISTS
1717 (SELECT null
1718 FROM pa_budget_lines bl
1719 ,pa_resource_assignments ra
1720 WHERE ra.budget_version_id = l_budget_version_id
1721 and ra.wbs_element_version_id = p_task_str_version_id
1722 and bl.resource_assignment_id = ra.resource_assignment_id
1723 and bl.cost_rejection_code is NOT NULL
1724 and bl.start_date between NVL(p_start_date,bl.start_date)
1725 AND NVL(p_end_date,bl.end_date)
1726 and bl.end_date between NVL(p_start_date,bl.start_date)
1727 AND NVL(p_end_date,bl.end_date)
1728 );
1729
1730 CURSOR cur_bdgtBdnRejFlag IS
1731 SELECT 'Y'
1732 FROM DUAL
1733 WHERE EXISTS
1734 (SELECT null
1735 FROM pa_budget_lines bl
1736 ,pa_resource_assignments ra
1737 WHERE ra.budget_version_id = l_budget_version_id
1738 and ra.wbs_element_version_id = p_task_str_version_id
1739 and bl.resource_assignment_id = ra.resource_assignment_id
1740 and bl.burden_rejection_code is NOT NULL
1741 and bl.start_date between NVL(p_start_date,bl.start_date)
1742 AND NVL(p_end_date,bl.end_date)
1743 and bl.end_date between NVL(p_start_date,bl.start_date)
1744 AND NVL(p_end_date,bl.end_date)
1745 );
1746
1747 CURSOR cur_bdgtRevnRejFlag IS
1748 SELECT 'Y'
1749 FROM DUAL
1750 WHERE EXISTS
1751 (SELECT null
1752 FROM pa_budget_lines bl
1753 ,pa_resource_assignments ra
1754 WHERE ra.budget_version_id = l_budget_version_id
1755 and ra.wbs_element_version_id = p_task_str_version_id
1756 and bl.resource_assignment_id = ra.resource_assignment_id
1757 and bl.revenue_rejection_code is NOT NULL
1758 and bl.start_date between NVL(p_start_date,bl.start_date)
1759 AND NVL(p_end_date,bl.end_date)
1760 and bl.end_date between NVL(p_start_date,bl.start_date)
1761 AND NVL(p_end_date,bl.end_date)
1762 );
1763
1764 CURSOR cur_bdgtPcConvRejFlag IS
1765 SELECT 'Y'
1766 FROM DUAL
1767 WHERE EXISTS
1768 (SELECT null
1769 FROM pa_budget_lines bl
1770 ,pa_resource_assignments ra
1771 WHERE ra.budget_version_id = l_budget_version_id
1772 and ra.wbs_element_version_id = p_task_str_version_id
1773 and bl.resource_assignment_id = ra.resource_assignment_id
1774 and bl.pc_cur_conv_rejection_code is NOT NULL
1775 and bl.start_date between NVL(p_start_date,bl.start_date)
1776 AND NVL(p_end_date,bl.end_date)
1777 and bl.end_date between NVL(p_start_date,bl.start_date)
1778 AND NVL(p_end_date,bl.end_date)
1779 );
1780
1781 CURSOR cur_bdgtPfcConvRejFlag IS
1782 SELECT 'Y'
1783 FROM DUAL
1784 WHERE EXISTS
1785 (SELECT null
1786 FROM pa_budget_lines bl
1787 ,pa_resource_assignments ra
1788 WHERE ra.budget_version_id = l_budget_version_id
1789 and ra.wbs_element_version_id = p_task_str_version_id
1790 and bl.resource_assignment_id = ra.resource_assignment_id
1791 and bl.pfc_cur_conv_rejection_code is NOT NULL
1792 and bl.start_date between NVL(p_start_date,bl.start_date)
1793 AND NVL(p_end_date,bl.end_date)
1794 and bl.end_date between NVL(p_start_date,bl.start_date)
1795 AND NVL(p_end_date,bl.end_date)
1796 );
1797
1798 BEGIN
1799 --- Initialize the error statck
1800 IF p_pa_debug_mode = 'Y' THEN
1801 PA_DEBUG.init_err_stack ('PA_FIN_PLAN_UTILS2.Get_WbsBdgtLineRejns');
1802 END IF;
1803 fnd_profile.get('PA_DEBUG_MODE',l_debug_flag);
1804 l_debug_flag := NVL(l_debug_flag, 'N');
1805
1806 IF p_pa_debug_mode = 'Y' THEN
1807 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
1808 ,x_write_file => 'LOG'
1809 ,x_debug_mode => l_debug_flag
1810 );
1811 END IF;
1812 x_return_status := 'S';
1813 l_stage := 'Inside Get_WbsBdgtLineRejns API: In Paramsare Proj Id['||p_project_id||
1814 ']projStrver['||p_proj_str_version_id||']TaskstrVer['||p_task_str_version_id||
1815 ']SD['||p_start_date||']ED['||p_end_date||']callinMode['||p_calling_mode||']';
1816 print_msg(l_debug_flag,l_proc_name,l_stage);
1817
1818 -- Validate In params
1819 IF p_project_id is NULL OR p_proj_str_version_id is NULL Then
1820 x_return_status := 'E';
1821 l_stage := 'Insufficent params to get the Bdgt Rejections';
1822 print_msg(l_debug_flag,l_proc_name,l_stage);
1823 IF p_pa_debug_mode = 'Y' THEN
1824 pa_debug.reset_err_stack;
1825 END IF;
1826 Return;
1827 END IF;
1828 If p_budget_version_id is NULL then --Bug 5611909
1829 l_budget_version_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id
1830 (p_project_id => p_project_id
1831 ,p_plan_type_id => null
1832 ,p_proj_str_ver_id => p_proj_str_version_id
1833 );
1834 else
1835 l_budget_version_id := p_budget_version_id; --Bug 5611909
1836 end if;
1837 l_stage := 'Budget version for the projStrVersionid['||l_budget_version_id||']';
1838 print_msg(l_debug_flag,l_proc_name,l_stage);
1839
1840 If p_calling_mode = 'PROJ_STR_VER' Then
1841 OPEN cur_bdgtProjStrRejFlag;
1842 FETCH cur_bdgtProjStrRejFlag INTO l_bdgtProjStrRejFlag;
1843 CLOSE cur_bdgtProjStrRejFlag;
1844 ELSE
1845 OPEN cur_bdgtCstRejFlag ;
1846 FETCH cur_bdgtCstRejFlag INTO l_bdgtCstRejFlag;
1847 CLOSE cur_bdgtCstRejFlag;
1848
1849 OPEN cur_bdgtBdnRejFlag ;
1850 FETCH cur_bdgtBdnRejFlag INTO l_bdgtBdnRejFlag;
1851 CLOSE cur_bdgtBdnRejFlag;
1852
1853 OPEN cur_bdgtRevnRejFlag ;
1854 FETCH cur_bdgtRevnRejFlag INTO l_bdgtRevnRejFlag;
1855 CLOSE cur_bdgtRevnRejFlag;
1856
1857 OPEN cur_bdgtpcConvRejFlag ;
1858 FETCH cur_bdgtPcConvRejFlag INTO l_bdgtPcConvRejFlag;
1859 CLOSE cur_bdgtPcConvRejFlag;
1860
1861 OPEN cur_bdgtpfcConvRejFlag ;
1862 FETCH cur_bdgtPfcConvRejFlag INTO l_bdgtPfcConvRejFlag;
1863 CLOSE cur_bdgtPfcConvRejFlag;
1864
1865 END IF;
1866
1867 x_cost_rejn_flag := NVL(l_bdgtCstRejFlag,'N');
1868 x_burden_rejn_flag := NVL(l_bdgtBdnRejFlag,'N');
1869 x_revenue_rejn_flag := NVL(l_bdgtRevnRejFlag,'N');
1870 x_pc_conv_rejn_flag := NVL(l_bdgtPcConvRejFlag,'N');
1871 x_pfc_conv_rejn_flag := NVL(l_bdgtPfcConvRejFlag,'N');
1872 x_projstrlvl_rejn_flag := NVL(l_bdgtProjStrRejFlag,'N');
1873
1874 l_stage := 'OUT Params CstRej['||x_cost_rejn_flag||']BdRej['||x_burden_rejn_flag||
1875 ']RevRej['||x_revenue_rejn_flag||']PcRej['||x_pc_conv_rejn_flag||
1876 ']PfcRej['||x_pfc_conv_rejn_flag||']ProjStrRej['||x_projstrlvl_rejn_flag||']';
1877 print_msg(l_debug_flag,l_proc_name,l_stage);
1878 IF p_pa_debug_mode = 'Y' THEN
1879 pa_debug.reset_err_stack;
1880 END IF;
1881 Return;
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 x_return_status := 'U';
1886 l_stage := 'Unexpected error Occured ['||SQLCODE||SQLERRM||']';
1887 print_msg('Y',l_proc_name,l_stage);
1888 FND_MSG_PUB.add_exc_msg
1889 ( p_pkg_name => 'PA_FIN_PLAN_UTILS2'
1890 ,p_procedure_name => 'Get_WbsBdgtLineRejns');
1891 IF p_pa_debug_mode = 'Y' THEN
1892 PA_DEBUG.reset_err_stack;
1893 END IF;
1894 Raise;
1895
1896 END Get_WbsBdgtLineRejns;
1897
1898 /* This API provides the budget line Actual Start Date and End Date
1899 * for the given budget version and resource assignment id
1900 * Logic: Derive Actual SDate as derive the MIN(budget_line.Start_date) where
1901 * actuals on the budget lines are populated.
1902 * similarly for EDate derive the ETC start date from budget versions for the given resource assignment
1903 * if etc start date is null then derive the MAX(budget_line.end_date) where
1904 * actuals on the budget lines are populated.
1905 * The PARAMS :
1906 * p_budget_version_id IN Number Required
1907 * p_resource_assignment_id IN Number Required
1908 * x_bl_actual_start_date OUT DATE
1909 * x_bl_actual_end_date OUT DATE
1910 * x_return_status OUT Varchar2
1911 * Note : if ETC start date and actual values donot exists then the out params
1912 * x_bl_actual_start_date and x_bl_actual_end_date will be passed as NULL
1913 * Rule:
1914 * 1. If x_bl_actual_start_date is NULL and x_bl_actual_end_date is NULL
1915 * then planning trx start date and end date can be shifted.
1916 * 2. if x_bl_actual_end_date is NOT NULL then planning trx end date can't be shifted earlier than x_bl_actual_end_date
1917 * 3. if x_bl_actual_start_date is NOT NULL then planning trx start date can't be shifted later than x_bl_actual_start_date
1918 */
1919 PROCEDURE get_blactual_Dates
1920 (p_budget_version_id IN Number
1921 ,p_resource_assignment_id IN Number
1922 ,x_bl_actual_start_date OUT NOCOPY date
1923 ,x_bl_actual_end_date OUT NOCOPY date
1924 ,x_return_status OUT NOCOPY varchar2
1925 ,x_error_msg_code OUT NOCOPY varchar2
1926 ) IS
1927
1928 cursor cur_etc IS
1929 select bv.etc_start_date
1930 from pa_budget_versions bv
1931 where bv.budget_version_id = p_budget_version_id
1932 and Exists ( select null
1933 from pa_budget_lines bl
1934 where bl.resource_assignment_id = p_resource_assignment_id
1935 and bl.budget_version_id = bv.budget_version_id
1936 ) ;
1937
1938 cursor cur_se_date IS
1939 select MIN(bl.start_date)
1940 ,MAX(bl.end_date)
1941 from pa_budget_lines bl
1942 where bl.resource_assignment_id = p_resource_assignment_id
1943 and bl.budget_version_id = p_budget_version_id
1944 and (bl.init_quantity is NOT NULL
1945 or bl.txn_init_raw_cost is NOT NULL
1946 or bl.txn_init_burdened_cost is NOT NULL
1947 or bl.txn_init_revenue is NOT NULL
1948 );
1949
1950 l_etc_start_date Date;
1951 l_bl_start_date Date;
1952 l_bl_end_date Date;
1953
1954 BEGIN
1955 x_return_status := 'S';
1956 x_error_msg_code := NULL;
1957 OPEN cur_etc;
1958 FETCH cur_etc INTO l_etc_start_date;
1959 CLOSE cur_etc;
1960
1961 OPEN cur_se_date;
1962 FETCH cur_se_date INTO
1963 l_bl_start_date
1964 ,l_bl_end_date;
1965 CLOSE cur_se_date;
1966
1967 If l_bl_end_date is NOT NULL Then
1968 If l_etc_start_date is NOT NULL Then
1969 IF l_etc_start_date > l_bl_end_date Then
1970 x_bl_actual_end_date := l_etc_start_date;
1971 Else
1972 x_bl_actual_end_date := l_bl_end_date;
1973 End If;
1974 Else
1975 x_bl_actual_end_date := l_bl_end_date;
1976 End If;
1977 Else
1978 x_bl_actual_end_date := l_etc_start_date;
1979 End if;
1980
1981 x_bl_actual_start_date := l_bl_start_date;
1982 RETURN;
1983
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 x_return_status := 'U';
1987 x_error_msg_code := SQLCODE||SQLERRM;
1988 RAISE;
1989 END get_blactual_Dates;
1990
1991 /* This API returns the Agreement currency for the given change order budget version
1992 * If the currency is null Or budget version is not part of the change order then
1993 * the api returns the NULL
1994 */
1995 FUNCTION get_Agreement_Currency(p_budget_version_id IN Number)
1996 RETURN varchar2 IS
1997
1998 l_calling_mode varchar2(100);
1999 l_agr_curr_code varchar2(100);
2000 l_return_status varchar2(100);
2001 l_agr_conv_reqd_flag varchar2(100);
2002 BEGIN
2003 PA_FIN_PLAN_UTILS2.Get_Agreement_Details
2004 (p_budget_version_id => p_budget_version_id
2005 ,p_calling_mode => 'FUNCTION_CALL'
2006 ,x_agr_curr_code => l_agr_curr_code
2007 ,x_AGR_CONV_REQD_FLAG => l_agr_conv_reqd_flag
2008 ,x_return_status => l_return_status
2009 );
2010
2011 If l_return_status <> 'S' Then
2012 l_agr_curr_code := null;
2013 End If;
2014 RETURN l_agr_curr_code;
2015
2016 END get_Agreement_Currency;
2017
2018 /* This API provides the agreement related details
2019 * Bug fix: 3679142 Change order versions which have revenue impact should also be in agreement
2020 * currency. This means all change order versions with version type as ALL or REVENUE
2021 * should ultimately have the planning txns and budget lines in AGR CURRENCY.
2022 */
2023 PROCEDURE Get_Agreement_Details
2024 (p_budget_version_id IN Number
2025 ,p_calling_mode IN Varchar2 DEFAULT 'CALCULATE_API'
2026 ,x_agr_curr_code OUT NOCOPY Varchar2
2027 ,x_AGR_CONV_REQD_FLAG OUT NOCOPY Varchar2
2028 ,x_return_status OUT NOCOPY Varchar2 ) IS
2029
2030 Cursor cur_bv IS
2031 SELECT bv.agreement_id
2032 ,bv.ci_id
2033 ,bv.version_type
2034 ,bv.version_name
2035 FROM pa_budget_versions bv
2036 WHERE bv.budget_version_id = p_budget_version_id;
2037
2038 CURSOR cur_agr(p_agr_id Number) IS
2039 SELECT agr.agreement_currency_code
2040 FROM pa_agreements_all agr
2041 WHERE agr.agreement_id = p_agr_id;
2042
2043
2044 l_agreement_id Number;
2045 l_ci_id Number;
2046 l_agr_curr_code Varchar2(100);
2047 l_agr_con_reqd_flag varchar2(1) := 'N';
2048 l_version_name pa_budget_versions.version_name%type;
2049 l_version_type pa_budget_versions.version_type%type;
2050 l_error_msg_code Varchar2(100);
2051 INVALID_EXCEPTION EXCEPTION;
2052 l_debug_flag varchar2(100);
2053 l_proc_name varchar2(100) := 'Get_Agreement_Details';
2054 G_AGR_CONV_REQD_FLAG varchar2(1) := 'N';
2055 G_AGR_CURRENCY_CODE varchar2(100);
2056
2057 BEGIN
2058 IF p_pa_debug_mode = 'Y' THEN
2059 pa_debug.init_err_stack('PA_FIN_PLAN_UTILS2.Get_Agreement_Details');
2060 END IF;
2061 fnd_profile.get('PA_DEBUG_MODE',l_debug_flag);
2062 l_debug_flag := NVL(l_debug_flag, 'N');
2063
2064 IF p_pa_debug_mode = 'Y' THEN
2065 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2066 ,x_write_file => 'LOG'
2067 ,x_debug_mode => l_debug_flag
2068 );
2069 END IF;
2070 print_msg(l_debug_flag,l_proc_name,'Entered Get_Agreement_Details Api');
2071
2072 x_return_status := 'S';
2073 OPEN cur_bv;
2074 FETCH cur_bv INTO
2075 l_agreement_id
2076 ,l_ci_id
2077 ,l_version_type
2078 ,l_version_name ;
2079 CLOSE cur_bv ;
2080 l_agr_con_reqd_flag := 'N';
2081 l_agr_curr_code := Null;
2082 print_msg(l_debug_flag,l_proc_name,'VersionType['||l_version_type||']CiId['||l_ci_id||']AgrId['||l_agreement_id||']');
2083 IF ( l_version_type in ('ALL','REVENUE')) Then
2084 If l_ci_id is NOT NULL Then
2085 print_msg(l_debug_flag,l_proc_name,'This is a change order/change request budget');
2086 IF l_agreement_id is NULL Then
2087 -- add error msg to stack
2088 l_error_msg_code := 'PA_FP_MISSING_AGR_REV_IMPACT';
2089 x_return_status := 'E';
2090 raise INVALID_EXCEPTION;
2091 ELSE
2092 OPEN cur_agr(l_agreement_id);
2093 FETCH cur_agr INTO l_agr_curr_code;
2094 CLOSE cur_agr;
2095 print_msg(l_debug_flag,l_proc_name,'Agreement Currency code['||l_agr_curr_code||']');
2096 IF l_agr_curr_code is NULL Then
2097 l_error_msg_code := 'PA_FP_MISSING_AGR_CURCODE';
2098 l_agr_con_reqd_flag := 'N';
2099 l_agr_curr_code := null;
2100 x_return_status := 'E';
2101 raise INVALID_EXCEPTION;
2102 Else
2103 l_agr_con_reqd_flag := 'Y';
2104 x_agr_curr_code := l_agr_curr_code;
2105 x_AGR_CONV_REQD_FLAG := l_agr_con_reqd_flag;
2106 End IF;
2107 End IF;
2108 Else
2109 l_agr_con_reqd_flag := 'N';
2110 l_agr_curr_code := null;
2111 End If;
2112 END IF;
2113
2114 /* Set the global varaibles to call conv rates api*/
2115 G_AGR_CONV_REQD_FLAG := l_agr_con_reqd_flag;
2116 G_AGR_CURRENCY_CODE := x_agr_curr_code;
2117 print_msg(l_debug_flag,l_proc_name,'Leaving Get_Agreement_Details G_AGR_CONV_REQD_FLAG['||G_AGR_CONV_REQD_FLAG||']G_AGR_CURRENCY_CODE['||G_AGR_CURRENCY_CODE||']');
2118 /* set the output variables */
2119 x_agr_curr_code := G_AGR_CURRENCY_CODE;
2120 x_AGR_CONV_REQD_FLAG := G_AGR_CONV_REQD_FLAG;
2121 -- reset error stack
2122 IF p_pa_debug_mode = 'Y' THEN
2123 pa_debug.reset_err_stack;
2124 END IF;
2125 EXCEPTION
2126 WHEN INVALID_EXCEPTION THEN
2127 If p_calling_mode = 'CALCULATE_API' Then
2128 pa_utils.Add_Message
2129 (p_app_short_name => 'PA'
2130 ,p_msg_name => l_error_msg_code
2131 ,p_token1 => 'BUDGET_VERSION_ID'
2132 ,p_value1 => p_budget_version_id
2133 ,p_token2 => 'VERSIONNAME'
2134 ,p_value2 => l_version_name
2135 );
2136 x_return_status := 'E';
2137 x_agr_curr_code := NULL;
2138 x_AGR_CONV_REQD_FLAG := 'N';
2139 RAISE ;
2140 ELSE
2141 x_return_status := 'S';
2142 x_agr_curr_code := NULL;
2143 x_AGR_CONV_REQD_FLAG := 'N';
2144 END IF;
2145
2146 WHEN OTHERS THEN
2147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2148 x_agr_curr_code := NULL;
2149 fnd_msg_pub.add_exc_msg
2150 ( p_pkg_name => 'PA_FIN_PLAN_UTILS2'
2151 ,p_procedure_name => 'Get_Agreement_Details' );
2152 print_msg(l_debug_flag,l_proc_name,'Failed in Get_Agreement_Details substr(SQLERRM,1,240) => '|| substr(SQLERRM,1,240));
2153 IF p_pa_debug_mode = 'Y' THEN
2154 pa_debug.reset_err_stack;
2155 END IF;
2156 RAISE;
2157 END Get_Agreement_Details;
2158
2159 /* This API rounds off the given quantity to 5 decimal places. This API should be called for rounding the quantity
2160 * for rate based planning transaction only.
2161 * This API accepts the following parameters
2162 */
2163 FUNCTION round_quantity
2164 (P_quantity IN Number
2165 ) RETURN NUMBER IS
2166
2167 l_rounded_quantity Number := P_quantity;
2168 BEGIN
2169 If P_quantity is NOT NULL Then
2170
2171 l_rounded_quantity := round(P_quantity,5);
2172 End If;
2173 RETURN l_rounded_quantity;
2174
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177 RAISE;
2178
2179 END round_quantity;
2180
2181 /* This API checks the given financial Task is billable or not
2182 * If task is billable, it returns 'Y' else 'N'
2183 */
2184 FUNCTION IsFpTaskBillable(p_project_id NUMBER
2185 ,p_task_id NUMBER) RETURN varchar2 IS
2186
2187 CURSOR cur_TaskBillable IS
2188 SELECT NVL(t.billable_flag,'N')
2189 FROM pa_tasks t
2190 WHERE t.task_id = p_task_id
2191 AND t.project_id = p_project_id;
2192
2193 X_billable_flag Varchar2(10);
2194 l_RecFound BOOLEAN := FALSE;
2195 l_projTaskId NUMBER;
2196 BEGIN
2197 /* Note: As suggested by venkatesh for project level always set the billable flag as Y */
2198 IF p_project_id is NOT NULL AND NVL(p_task_id,0) = 0 Then
2199 X_billable_flag := 'Y';
2200 ELSIF p_project_id is NOT NULL AND NVL(p_task_id,0) <> 0 THEN --{
2201 /* l_projTaskId := p_project_id||p_task_id; This is not required as this may lead to corruption
2202 * P1||T1 = 33||3 similarly P1||T1 = 3||33 will give the same results*/
2203 l_projTaskId := p_task_id;
2204 --print_msg('Y','IsFpTaskBillable','l_projTaskId['||l_projTaskId||']Count['||G_FpTaskBillable_Tab.COUNT||']');
2205 If G_FpTaskBillable_Tab.COUNT > 0 Then
2206 Begin
2207 /*Get the Project Number from the pl/sql table.
2208 *If there is no index with the value of the project_id passed
2209 *in then an ora-1403: no_data_found is generated.
2210 */
2211 X_billable_flag := G_FpTaskBillable_Tab(l_projTaskId).Billable_Flag;
2212 l_RecFound := TRUE;
2213
2214 Exception
2215 When No_Data_Found Then
2216 l_RecFound := FALSE;
2217 When Others Then
2218 Raise;
2219
2220 End;
2221 End If;
2222
2223 If Not l_RecFound Then
2224 --print_msg('Y','IsFpTaskBillable','l_projTaskId['||l_projTaskId||']Executing cursor to get BillableFlag');
2225 -- Since the project has not been cached yet, will need to add it.
2226 -- So check to see if there are already 200 records in the pl/sql table.
2227 If G_FpTaskBillable_Tab.COUNT > 199 Then
2228 G_FpTaskBillable_Tab.Delete;
2229 End If;
2230 X_billable_flag := 'N';
2231 OPEN cur_TaskBillable;
2232 FETCH cur_TaskBillable INTO X_billable_flag;
2233 IF cur_TaskBillable%NOTFOUND Then
2234 X_billable_flag := 'N';
2235 End If;
2236 CLOSE cur_TaskBillable;
2237
2238 -- Add the billable Flag to the pl/sql table using the project_id||TaskId combination
2239 G_FpTaskBillable_Tab(l_projTaskId).Billable_Flag := NVL(X_billable_flag,'N');
2240
2241 End If;
2242
2243 END IF; --}
2244 --print_msg('Y','IsFpTaskBillable','X_billable_flag['||X_billable_flag||']');
2245 RETURN NVL(X_billable_flag,'N');
2246
2247 EXCEPTION
2248 WHEN OTHERS THEN
2249 fnd_msg_pub.add_exc_msg
2250 ( p_pkg_name => 'PA_FIN_PLAN_UTILS2'
2251 ,p_procedure_name => 'IsFpTaskBillable');
2252 print_msg('Y','IsFpTaskBillable','Failed in IsFpTaskBillable => '|| substr(SQLERRM,1,240));
2253 RAISE;
2254
2255 END IsFpTaskBillable;
2256
2257 PROCEDURE populate_res_details
2258 ( p_calling_module IN VARCHAR2
2259 ,p_source_context IN VARCHAR2
2260 ,p_project_id IN NUMBER
2261 ,p_project_type IN VARCHAR2
2262 ,p_budget_version_id IN NUMBER
2263 ,p_resource_list_member_Id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2264 ,p_plsql_index_tab IN SYSTEM.PA_NUM_TBL_TYPE
2265 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE
2266 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2267 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE
2268 ,p_txn_currency_code_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2269 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2270 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2271 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2272 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2273 ,x_return_status OUT NOCOPY VARCHAR2
2274 ) IS
2275
2276 --Start of variables for Variable for Resource Attributes
2277 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2278 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2279 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2280 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2281 l_incur_by_res_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2282 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2283 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2284 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2285 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
2286 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2287 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := SYSTEM.PA_VARCHAR2_20_TBL_TYPE();
2288 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2289 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2290 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2291 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2292 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2293 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2294 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2295 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2296 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2297 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2298 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2299 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2300 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2301 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2302 l_procure_resource_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2303 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2304 l_Incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2305 l_Incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2306 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2307 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2308 l_rate_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2309 l_rate_func_curr_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2310 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2311 l_assignment_description_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
2312 l_planning_resource_alias_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
2313 l_resource_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2314 l_project_role_name_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
2315 l_organization_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
2316 l_financial_category_code_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
2317 l_project_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2318 l_use_task_schedule_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2319 l_planning_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2320 l_planning_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2321 l_total_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2322 l_override_currency_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2323 l_billable_percent_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2324 l_cost_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2325 l_burdened_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2326 l_sp_fixed_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2327 l_financial_category_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2328 l_supplier_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
2329 --End of variables for Variable for Resource Attributes
2330 l_msg_data Varchar2(1000);
2331 l_msg_count Number;
2332 l_return_status Varchar2(1) := 'S';
2333
2334 BEGIN
2335 x_return_status := 'S';
2336 --This api derives the resource defaults
2337 IF p_resource_list_member_Id_tab.COUNT > 0 THEN --{
2338 print_msg(p_pa_debug_mode,'populate_res_details','Calling get_resource_defaults');
2339 PA_PLANNING_RESOURCE_UTILS.get_resource_defaults(
2340 p_resource_list_members => p_resource_list_member_Id_tab
2341 ,p_project_id => p_project_id
2342 ,x_resource_class_flag => l_resource_class_flag_tbl
2343 ,x_resource_class_code => l_resource_class_code_tbl
2344 ,x_resource_class_id => l_resource_class_id_tbl
2345 ,x_res_type_code => l_res_type_code_tbl
2346 ,x_incur_by_res_type => l_incur_by_res_type_tbl
2347 ,x_person_id => l_person_id_tbl
2348 ,x_job_id => l_job_id_tbl
2349 ,x_person_type_code => l_person_type_code_tbl
2350 ,x_named_role => l_named_role_tbl
2351 ,x_bom_resource_id => l_bom_resource_id_tbl
2352 ,x_non_labor_resource => l_non_labor_resource_tbl
2353 ,x_inventory_item_id => l_inventory_item_id_tbl
2354 ,x_item_category_id => l_item_category_id_tbl
2355 ,x_project_role_id => l_project_role_id_tbl
2356 ,x_organization_id => l_organization_id_tbl
2357 ,x_fc_res_type_code => l_fc_res_type_code_tbl
2358 ,x_expenditure_type => l_expenditure_type_tbl
2359 ,x_expenditure_category => l_expenditure_category_tbl
2360 ,x_event_type => l_event_type_tbl
2361 ,x_revenue_category_code => l_revenue_category_code_tbl
2362 ,x_supplier_id => l_supplier_id_tbl
2363 ,x_unit_of_measure => l_unit_of_measure_tbl
2364 ,x_spread_curve_id => l_spread_curve_id_tbl
2365 ,x_etc_method_code => l_etc_method_code_tbl
2366 ,x_mfc_cost_type_id => l_mfc_cost_type_id_tbl
2367 ,x_incurred_by_res_flag => l_incurred_by_res_flag_tbl
2368 ,x_incur_by_res_class_code => l_incur_by_res_class_code_tbl
2369 ,x_Incur_by_role_id => l_Incur_by_role_id_tbl
2370 ,x_org_id => l_org_id_tbl
2371 ,X_rate_based_flag => l_rate_based_flag_tbl
2372 ,x_rate_expenditure_type => l_rate_expenditure_type_tbl
2373 ,x_rate_func_curr_code => l_rate_func_curr_code_tbl
2374 ,x_msg_data => l_msg_data
2375 ,x_msg_count => l_msg_count
2376 ,x_return_status => l_return_status
2377 );
2378 print_msg(p_pa_debug_mode,'populate_res_details','RetunSts['||l_return_status||']MsgData['||l_msg_data||']');
2379
2380 IF NVL(l_return_status,'S') = 'S' Then
2381 print_msg(p_pa_debug_mode,'populate_res_details','populating res assignments tmp');
2382 FORALL i IN p_resource_list_member_Id_tab.FIRST .. p_resource_list_member_Id_tab.LAST
2383 INSERT INTO pa_fp_res_assignments_tmp
2384 (project_id
2385 ,budget_version_id
2386 ,resource_assignment_id
2387 ,resource_list_member_id
2388 ,unit_of_measure
2389 ,resource_class_code
2390 ,organization_id
2391 ,job_id
2392 ,person_id
2393 ,expenditure_type
2394 ,expenditure_category
2395 ,non_labor_resource
2396 ,bom_resource_id
2397 ,inventory_item_id
2398 ,item_category_id
2399 ,mfc_cost_type_id
2400 --,rate_job_id
2401 ,rate_expenditure_type
2402 ,rate_based_flag
2403 ,rate_expenditure_org_id
2404 --,res_format_id
2405 ,project_type
2406 ,org_id
2407 ,rbs_element_id
2408 ) VALUES
2409 (p_project_id
2410 ,NVL(p_budget_version_id,-9999)
2411 ,-9999 --raid
2412 ,p_resource_list_member_Id_tab(i)
2413 ,l_unit_of_measure_tbl(i)
2414 ,l_resource_class_code_tbl(i)
2415 ,l_organization_id_tbl(i)
2416 ,l_job_id_tbl(i)
2417 ,l_person_id_tbl(i)
2418 ,l_expenditure_type_tbl(i)
2419 ,l_expenditure_category_tbl(i)
2420 ,l_non_labor_resource_tbl(i)
2421 ,l_bom_resource_id_tbl(i)
2422 ,l_inventory_item_id_tbl(i)
2423 ,l_item_category_id_tbl(i)
2424 ,l_mfc_cost_type_id_tbl(i)
2425 --,l_rate_job_id_tbl(i)
2426 ,l_rate_expenditure_type_tbl(i)
2427 ,l_rate_based_flag_tbl(i)
2428 ,l_org_id_tbl(i) --l_rate_expenditure_org_id_tbl(i)
2429 --,to_number(null) --l_res_format_id_tbl(i)
2430 ,p_project_type
2431 ,l_org_id_tbl(i)
2432 ,p_plsql_index_tab(i)
2433 )
2434 ;
2435
2436 /* Now Update the tmp table with rates and currencys passed */
2437 FORALL i IN p_resource_list_member_Id_tab.FIRST .. p_resource_list_member_Id_tab.LAST
2438 UPDATE pa_fp_res_assignments_tmp TMP
2439 SET tmp.txn_currency_code = p_txn_currency_code_tab(i)
2440 ,txn_currency_code_override = p_txn_currency_code_ovr_tab(i)
2441 ,rw_cost_rate_override = p_cost_rate_override_tab(i)
2442 ,burden_cost_rate_override = p_burden_rate_override_tab(i)
2443 ,bill_rate_override = p_bill_rate_override_tab(i)
2444 ,task_id = p_task_id_tab(i)
2445 ,txn_plan_quantity = p_quantity_tab(i)
2446 ,line_start_date = NVL(p_ra_date_tab(i),trunc(sysdate))
2447 WHERE tmp.budget_version_id = NVL(p_budget_version_id,-9999)
2448 AND tmp.resource_list_member_id = p_resource_list_member_Id_tab(i);
2449
2450 END IF;
2451
2452 END IF; --}
2453 x_return_status := l_return_status;
2454 print_msg(p_pa_debug_mode,'populate_res_details','End of populate_res_details retSts['||x_return_status||']');
2455
2456 EXCEPTION
2457 WHEN OTHERS THEN
2458 print_msg('Y','populate_res_details','Error occured at populate_res_details: '||sqlcode||sqlerrm);
2459 x_return_status := 'U';
2460 RAISE;
2461 END populate_res_details;
2462
2463 PROCEDURE populate_ra_details
2464 ( p_calling_module IN VARCHAR2
2465 ,p_source_context IN VARCHAR2
2466 ,p_project_id IN NUMBER
2467 ,p_project_type IN VARCHAR2
2468 ,p_budget_version_id IN NUMBER
2469 ,p_resource_assignment_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2470 ,p_plsql_index_tab IN SYSTEM.PA_NUM_TBL_TYPE
2471 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE
2472 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2473 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE
2474 ,p_txn_currency_code_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2475 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2476 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2477 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2478 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2479 ,x_return_status OUT NOCOPY VARCHAR2
2480 ) IS
2481
2482 BEGIN
2483 x_return_status := 'S';
2484 IF p_resource_assignment_id_tab.COUNT > 0 Then
2485 print_msg(p_pa_debug_mode,'populate_ra_details','bulk insert to res_assignments_tmp table');
2486 FORALL i IN p_resource_assignment_id_tab.FIRST .. p_resource_assignment_id_tab.LAST
2487 INSERT INTO pa_fp_res_assignments_tmp
2488 (project_id
2489 ,budget_version_id
2490 ,resource_assignment_id
2491 ,resource_list_member_id
2492 ,line_start_date
2493 ,txn_currency_code
2494 ,txn_currency_code_override
2495 ,rw_cost_rate_override
2496 ,burden_cost_rate_override
2497 ,bill_rate_override
2498 ,task_id
2499 ,unit_of_measure
2500 ,resource_class_code
2501 ,organization_id
2502 ,job_id
2503 ,person_id
2504 ,expenditure_type
2505 ,expenditure_category
2506 ,non_labor_resource
2507 ,bom_resource_id
2508 ,inventory_item_id
2509 ,item_category_id
2510 ,mfc_cost_type_id
2511 ,rate_job_id
2512 ,rate_expenditure_type
2513 ,rate_based_flag
2514 ,rate_expenditure_org_id
2515 ,project_type
2516 ,rbs_element_id
2517 ) SELECT
2518 p_project_id
2519 ,NVL(p_budget_version_id,-9999)
2520 ,p_resource_assignment_id_tab(i)
2521 ,ra.resource_list_member_id
2522 ,NVL(p_ra_date_tab(i),trunc(sysdate))
2523 ,p_txn_currency_code_tab(i)
2524 ,p_txn_currency_code_ovr_tab(i)
2525 ,p_cost_rate_override_tab(i)
2526 ,p_burden_rate_override_tab(i)
2527 ,p_bill_rate_override_tab(i)
2528 ,ra.task_id
2529 ,ra.unit_of_measure
2530 ,ra.resource_class_code
2531 ,ra.organization_id
2532 ,ra.job_id
2533 ,ra.person_id
2534 ,ra.expenditure_type
2535 ,ra.expenditure_category
2536 ,ra.non_labor_resource
2537 ,ra.bom_resource_id
2538 ,ra.inventory_item_id
2539 ,ra.item_category_id
2540 ,ra.mfc_cost_type_id
2541 ,ra.rate_job_id
2542 ,ra.rate_expenditure_type
2543 ,NVL(ra.rate_based_flag,'N') rate_based_flag
2544 ,ra.rate_expenditure_org_id
2545 ,p_project_type
2546 ,p_plsql_index_tab(i)
2547 FROM pa_resource_assignments ra
2548 WHERE ra.resource_assignment_id = p_resource_assignment_id_tab(i);
2549
2550 END IF;
2551 print_msg(p_pa_debug_mode,'populate_ra_details','End of populate_ra_details retSts['||x_return_status||']');
2552 EXCEPTION
2553 WHEN OTHERS THEN
2554 x_return_status := 'U';
2555 print_msg('Y','populate_ra_details','Error occured at populate_ra_details: '||sqlcode||sqlerrm);
2556 RAISE;
2557 END populate_ra_details;
2558
2559 /* Bug Fix:4621597: Added new generic api to derive rates for RA / RLMI
2560 * This is a generic wrapper API to call the get planning rates to derive raw cost rate, burden rate and bill rates
2561 * The default and possible values for the IN params
2562 * p_calling_module IN VARCHAR2 := 'MSP'
2563 * p_source_context IN VARCHAR2 := 'RLMI' -- Resource List member context
2564 * 'RA' -- Resource assignment context
2565 * if p_budget_version_id is NULL Then it will be treated as the 'COST' only version and attributes will be defaulted from the
2566 * project level.
2567 * p_porject_id is NOT NULL param
2568 * If p_source_context = 'RLMI' then p_resource_list_member_Id_tab must be passed
2569 * If p_source_context = 'RA' then p_resource_assignment_id_tab must be passed
2570 * OUT PARAMS:
2571 * API provides two set of txn currency, x_cost_txn_curr_code_tab - for Cost currency
2572 * x_rev_txn_curr_code_tab - for Revenue currency
2573 * Its calling APIs responsibiltiy to convert these currency into Txn currency
2574 */
2575 PROCEDURE Get_Resource_Rates
2576 ( p_calling_module IN VARCHAR2 := 'MSP'
2577 ,p_source_context IN VARCHAR2 := 'RLMI'
2578 ,p_project_id IN NUMBER
2579 ,p_budget_version_id IN NUMBER
2580 ,p_resource_assignment_id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2581 ,p_resource_list_member_Id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2582 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
2583 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2584 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2585 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
2586 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2587 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2588 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2589 ,x_resource_assignment_id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2590 ,x_resource_list_member_Id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2591 ,x_expenditure_ou_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2592 ,x_raw_cost_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2593 ,x_burden_cost_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2594 ,x_burden_multiplier_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2595 ,x_ind_compiled_set_id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2596 ,x_bill_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2597 ,x_markup_percent_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2598 ,x_txn_currency_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2599 ,x_cost_txn_curr_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2600 ,x_rev_txn_curr_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2601 ,x_cost_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
2602 ,x_burden_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
2603 ,x_revenue_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
2604 ,x_return_status OUT NOCOPY VARCHAR2
2605 ,x_msg_data OUT NOCOPY VARCHAR2
2606 ,x_msg_count OUT NOCOPY NUMBER
2607 ) IS
2608
2609 l_plsql_index_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2610 l_resource_assignment_id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2611 l_resource_list_member_Id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2612 l_task_id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2613 l_quantity_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2614 l_txn_currency_code_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2615 l_txn_currency_code_ovr_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2616 l_curr_conv_reqd_flag_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2617 l_cost_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2618 l_burden_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2619 l_bill_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2620 l_ra_date_tab SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2621 l_rowid_tab SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
2622 x_rw_cost_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2623 x_burden_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2624 x_bill_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2625
2626 p_activity_code Varchar2(100) := 'CALCULATE';
2627 l_return_status VArchar2(1) := 'S';
2628 l_billable_flag Varchar2(1) := 'Y';
2629 l_calculate_mode Varchar2(100);
2630 l_txn_currency_code_override Varchar2(100);
2631 l_stage Varchar2(1000);
2632 l_task_id Number;
2633 RATEAPI_UNEXPECTED_ERRORS EXCEPTION;
2634 L_TXNCONVRATE_ERROR EXCEPTION;
2635 l_invalid_params EXCEPTION;
2636 l_plsql_Tab_Ct Number;
2637
2638 x_dummy_curr_code Varchar2(100);
2639 x_dummy_rate_date Date;
2640 x_dummy_rate_type Varchar2(100);
2641 x_dummy_exch_rate Number;
2642 x_dummy_cost Number;
2643 x_Final_Txn_raw_cost Number;
2644 x_Final_Txn_quantity Number;
2645 x_Final_txn_exch_rate Number;
2646 x_final_txn_rate_type Varchar2(100);
2647 x_final_txn_rate_date Date;
2648 l_Cntr Number := 0;
2649
2650 CURSOR CUR_projDetails IS
2651 SELECT to_number(null) res_class_bill_rate_sch_id
2652 ,to_number(null) res_class_raw_cost_sch_id
2653 ,'N' use_planning_rates_flag
2654 ,to_number(null) rev_job_rate_sch_id
2655 ,to_number(null) cost_job_rate_sch_id
2656 ,to_number(null) rev_emp_rate_sch_id
2657 ,to_number(null) cost_emp_rate_sch_id
2658 ,to_number(null) rev_non_labor_res_rate_sch_id
2659 ,to_number(null) cost_non_labor_res_rate_sch_id
2660 ,to_number(null) cost_burden_rate_sch_id
2661 ,'Y' track_workplan_costs_flag
2662 ,'COST' fp_budget_version_type
2663 ,to_number(null) resource_list_id
2664 ,'N' approved_rev_plan_type_flag
2665 ,'N' plan_in_multi_curr_flag
2666 ,to_date(null) etc_start_date
2667 ,'N' wp_version_flag
2668 ,pp.assign_precedes_task
2669 ,pp.bill_job_group_id
2670 ,pp.carrying_out_organization_id
2671 ,nvl(pp.multi_currency_billing_flag,'N') multi_currency_billing_flag
2672 ,pp.org_id
2673 ,pp.non_labor_bill_rate_org_id
2674 ,pp.project_currency_code
2675 ,pp.non_labor_schedule_discount
2676 ,pp.non_labor_schedule_fixed_date
2677 ,pp.non_lab_std_bill_rt_sch_id
2678 ,pp.project_type
2679 ,pp.projfunc_currency_code
2680 ,pp.emp_bill_rate_schedule_id
2681 ,pp.job_bill_rate_schedule_id
2682 ,pp.labor_bill_rate_org_id
2683 ,pp.labor_sch_type
2684 ,pp.non_labor_sch_type
2685 ,to_number(null) project_structure_version_id
2686 ,pp.project_id
2687 ,pp.segment1 project_name
2688 FROM pa_projects_all pp
2689 WHERE pp.project_id = p_project_id;
2690
2691 CURSOR CUR_VersionDts IS
2692 SELECT decode(nvl(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_bill_rate_sch_id,
2693 decode(bv.version_type,'REVENUE',pfo.rev_res_class_rate_sch_id,
2694 'ALL' ,pfo.rev_res_class_rate_sch_id,
2695 NULL)) res_class_bill_rate_sch_id
2696 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_raw_cost_sch_id,
2697 decode(bv.version_type,'COST',pfo.cost_res_class_rate_sch_id,
2698 'ALL' ,pfo.cost_res_class_rate_sch_id,
2699 NULL)) res_class_raw_cost_sch_id
2700 ,nvl(pfo.use_planning_rates_flag,'N') use_planning_rates_flag
2701 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2702 decode(bv.version_type,'REVENUE',pfo.rev_job_rate_sch_id,
2703 'ALL' ,pfo.rev_job_rate_sch_id,
2704 NULL)) rev_job_rate_sch_id
2705 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2706 decode(bv.version_type,'COST' ,pfo.cost_job_rate_sch_id,
2707 'ALL' ,pfo.cost_job_rate_sch_id,
2708 NULL)) cost_job_rate_sch_id
2709 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2710 decode(bv.version_type,'REVENUE',pfo.rev_emp_rate_sch_id,
2711 'ALL' ,pfo.rev_emp_rate_sch_id,
2712 NULL)) rev_emp_rate_sch_id
2713 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2714 decode(bv.version_type,'COST' ,pfo.cost_emp_rate_sch_id,
2715 'ALL' ,pfo.cost_emp_rate_sch_id,
2716 NULL)) cost_emp_rate_sch_id
2717 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2718 decode(bv.version_type,'REVENUE',pfo.rev_non_labor_res_rate_sch_id,
2719 'ALL' ,pfo.rev_non_labor_res_rate_sch_id,
2720 NULL)) rev_non_labor_res_rate_sch_id
2721 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2722 decode(bv.version_type,'COST' ,pfo.cost_non_labor_res_rate_sch_id,
2723 'ALL' ,pfo.cost_non_labor_res_rate_sch_id,
2724 NULL)) cost_non_labor_res_rate_sch_id
2725 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2726 decode(bv.version_type,'COST' ,pfo.cost_burden_rate_sch_id,
2727 'ALL' ,pfo.cost_burden_rate_sch_id,
2728 NULL)) cost_burden_rate_sch_id
2729 ,decode(nvl(bv.wp_version_flag,'N'),'Y',NVL(pfo.track_workplan_costs_flag,'N'),'Y') track_workplan_costs_flag
2730 ,bv.version_type fp_budget_version_type
2731 ,bv.resource_list_id
2732 ,nvl(bv.approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
2733 ,nvl(pfo.plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
2734 ,bv.etc_start_date
2735 ,nvl(bv.wp_version_flag,'N') wp_version_flag
2736 ,pp.assign_precedes_task
2737 ,pp.bill_job_group_id
2738 ,pp.carrying_out_organization_id
2739 ,nvl(pp.multi_currency_billing_flag,'N') multi_currency_billing_flag
2740 ,pp.org_id
2741 ,pp.non_labor_bill_rate_org_id
2742 ,pp.project_currency_code
2743 ,pp.non_labor_schedule_discount
2744 ,pp.non_labor_schedule_fixed_date
2745 ,pp.non_lab_std_bill_rt_sch_id
2746 ,pp.project_type
2747 ,pp.projfunc_currency_code
2748 ,pp.emp_bill_rate_schedule_id
2749 ,pp.job_bill_rate_schedule_id
2750 ,pp.labor_bill_rate_org_id
2751 ,pp.labor_sch_type
2752 ,pp.non_labor_sch_type
2753 ,bv.project_structure_version_id
2754 ,bv.project_id
2755 ,pp.segment1 project_name
2756 FROM pa_proj_fp_options pfo
2757 ,pa_budget_versions bv
2758 ,pa_projects_all pp
2759 WHERE pfo.fin_plan_version_id = bv.budget_version_id
2760 AND bv.budget_version_id = p_budget_version_id
2761 AND pp.project_id = bv.project_id
2762 AND pfo.project_id = pp.project_id;
2763 rate_rec CUR_VersionDts%ROWtype;
2764
2765
2766 CURSOR CUR_RateApi_Attrbs IS
2767 SELECT tmp.rowid
2768 ,tmp.rbs_element_id
2769 ,tmp.resource_assignment_id
2770 ,tmp.txn_currency_code
2771 ,NVL(tmp.txn_plan_quantity,1) quantity
2772 ,tmp.line_start_date start_date
2773 ,tmp.burden_cost_rate_override
2774 ,tmp.rw_cost_rate_override
2775 ,tmp.bill_rate_override
2776 ,tmp.txn_currency_code_override
2777 ,tmp.task_id
2778 ,tmp.resource_list_member_id
2779 ,tmp.unit_of_measure
2780 ,tmp.resource_class_code
2781 ,tmp.organization_id
2782 ,tmp.job_id
2783 ,tmp.person_id
2784 ,tmp.expenditure_type
2785 ,tmp.expenditure_category
2786 ,tmp.revenue_category_code
2787 ,tmp.event_type
2788 ,tmp.supplier_id
2789 ,tmp.non_labor_resource
2790 ,tmp.bom_resource_id
2791 ,tmp.inventory_item_id
2792 ,tmp.item_category_id
2793 ,tmp.billable_percent
2794 ,tmp.mfc_cost_type_id
2795 ,tmp.incurred_by_res_flag
2796 ,tmp.rate_job_id
2797 ,tmp.rate_expenditure_type
2798 ,tmp.sp_fixed_date
2799 ,tmp.person_type_code
2800 ,NVL(tmp.rate_based_flag,'N') rate_based_flag
2801 ,tmp.rate_exp_func_curr_code
2802 ,tmp.rate_expenditure_org_id
2803 ,tmp.incur_by_res_class_code
2804 ,tmp.project_role_id
2805 ,tmp.resource_class_flag
2806 ,to_number(null) res_format_id --tmp.res_format_id
2807 ,tmp.task_bill_rate_org_id non_labor_bill_rate_org_id
2808 ,tmp.task_sch_discount non_labor_schedule_discount
2809 ,tmp.task_sch_date non_labor_schedule_fixed_date
2810 ,tmp.task_std_bill_rate_sch non_lab_std_bill_rt_sch_id
2811 ,tmp.emp_bill_rate_schedule_id
2812 ,tmp.job_bill_rate_schedule_id
2813 ,tmp.labor_bill_rate_org_id
2814 ,tmp.labor_sch_type
2815 ,tmp.non_labor_sch_type
2816 ,tmp.top_task_id
2817 ,NVL(tmp.billable_flag,'N') billable_flag
2818 ,to_number(null) budget_line_id
2819 ,tmp.task_name
2820 ,tmp.resource_name
2821 FROM pa_fp_res_assignments_tmp tmp
2822 WHERE tmp.budget_version_id = NVL(p_budget_version_id,-9999)
2823 ORDER BY tmp.rbs_element_id ; /* added this to ensure that In and Out plsql table indexes are mapped */
2824
2825 l_txn_currency_code Varchar2(100);
2826 l_txn_plan_quantity Number;
2827 l_budget_lines_start_date Date;
2828 l_budget_line_id Number;
2829 l_burden_cost_rate_override Number;
2830 l_rw_cost_rate_override Number;
2831 l_bill_rate_override Number;
2832 l_txn_raw_cost Number;
2833 l_txn_burdened_cost Number;
2834 l_txn_revenue Number;
2835 x_bill_rate Number;
2836 x_cost_rate Number;
2837 x_burden_cost_rate Number;
2838 x_raw_cost Number;
2839 x_burden_cost Number;
2840 x_raw_revenue Number;
2841 x_bill_markup_percentage Number;
2842 l_bill_markup_percentage Number;
2843 x_cost_txn_curr_code Varchar2(100);
2844 x_rev_txn_curr_code Varchar2(100);
2845 x_raw_cost_rejection_code Varchar2(100);
2846 x_burden_cost_rejection_code Varchar2(100);
2847 x_revenue_rejection_code Varchar2(100);
2848 x_cost_ind_compiled_set_id Number;
2849 x_projfunc_rejection_code Varchar2(100);
2850 x_project_rejection_code Varchar2(100);
2851 X_BURDEN_MULTIPLIER Number;
2852 l_cost_rate_multiplier CONSTANT pa_labor_cost_multipliers.multiplier%TYPE := 1;
2853 l_bill_rate_multiplier CONSTANT pa_labor_cost_multipliers.multiplier%TYPE := 1;
2854 l_cost_sch_type VARCHAR2(30) := 'COST';
2855 l_mfc_cost_source CONSTANT NUMBER := 2;
2856 x_stage varchar2(1000);
2857 l_status varchar2(100);
2858
2859 l_labor_sch_type pa_projects_all.labor_sch_type%TYPE;
2860 l_non_labor_sch_type pa_projects_all.labor_sch_type%TYPE;
2861
2862 /* Added these variables for bug fix: 3681314,3828998 */
2863 l_override_organization_id Number;
2864 l_debug_flag Varchar2(1) := 'N';
2865 l_proc_name VARCHAR2(100) := 'GET_RESOURCE_RATES';
2866
2867 BEGIN
2868
2869 l_return_status := 'S';
2870 x_return_status := 'S';
2871 fnd_profile.get('PA_DEBUG_MODE',l_debug_flag);
2872 l_debug_flag := NVL(l_debug_flag, 'N');
2873
2874 l_stage := '10:Entered Get_Res_Rates API:ProjId['||p_project_id||']BdgtVer['||p_budget_version_id||']CallingModule['||p_calling_module||']';
2875 l_stage := l_stage||' SourceContext['||p_source_context||']';
2876 print_msg(l_debug_flag,l_proc_name,l_stage);
2877
2878 /* Initialize tmp tables */
2879 DELETE FROM PA_FP_RES_ASSIGNMENTS_TMP;
2880 DELETE FROM PA_FP_ROLLUP_TMP;
2881
2882 /* Validate Input Params */
2883 l_resource_assignment_id_tab := p_resource_assignment_id_tab;
2884 l_resource_list_member_Id_tab := p_resource_list_member_Id_tab;
2885 l_task_id_tab := p_task_id_tab;
2886 l_quantity_tab := p_quantity_tab;
2887 --l_txn_currency_code_tab := p_txn_currency_code_tab;
2888 l_txn_currency_code_ovr_tab := p_txn_currency_code_ovr_tab;
2889 --l_curr_conv_reqd_flag_tab := p_curr_conv_reqd_flag_tab;
2890 l_cost_rate_override_tab := p_cost_rate_override_tab;
2891 l_burden_rate_override_tab := p_burden_rate_override_tab;
2892 l_bill_rate_override_tab := p_bill_rate_override_tab;
2893 l_ra_date_tab := p_ra_date_tab;
2894
2895 IF NVL(p_project_id,0) = 0 AND NVL(p_budget_version_id,0) = 0 Then
2896 l_stage := '11: Project and Budget Version is NULL';
2897 print_msg(l_debug_flag,l_proc_name,l_stage);
2898 l_return_status := 'E';
2899 END If;
2900
2901 IF ((p_source_context = 'RA' AND l_resource_assignment_id_tab.COUNT = 0 ) OR
2902 (p_source_context = 'RLMI' AND l_resource_list_member_Id_tab.COUNT = 0)) Then
2903 l_stage := '12: RA and RLMI is NULL';
2904 print_msg(l_debug_flag,l_proc_name,l_stage);
2905 l_return_status := 'E';
2906 END IF;
2907
2908 /* Now Extend the passed in the plsql tables if param is not passed. This has to be done to avoid no data found error*/
2909 IF (p_source_context = 'RA' AND l_resource_assignment_id_tab.COUNT > 0 ) Then
2910 l_plsql_Tab_Ct := l_resource_assignment_id_tab.COUNT;
2911 ElsIf (p_source_context = 'RLMI' AND l_resource_list_member_Id_tab.COUNT > 0) Then
2912 l_plsql_Tab_Ct := l_resource_list_member_Id_tab.COUNT;
2913 End If;
2914
2915 FOR i IN 1 .. l_plsql_Tab_Ct LOOP
2916 If NOT l_plsql_index_tab.exists(i) Then
2917 l_plsql_index_tab.extend;
2918 l_plsql_index_tab(i) := i;
2919 End If;
2920 If NOT l_quantity_tab.exists(i) Then
2921 l_quantity_tab.extend;
2922 l_quantity_tab(i) := NULL;
2923 End If;
2924
2925 If NOT l_txn_currency_code_tab.exists(i) Then
2926 l_txn_currency_code_tab.extend;
2927 l_txn_currency_code_tab(i) := NULL;
2928 End If;
2929
2930 If NOT l_txn_currency_code_ovr_tab.exists(i) Then
2931 l_txn_currency_code_ovr_tab.extend;
2932 l_txn_currency_code_ovr_tab(i) := NULL;
2933 End If;
2934 If NOT l_curr_conv_reqd_flag_tab.exists(i) Then
2935 l_curr_conv_reqd_flag_tab.extend;
2936 l_curr_conv_reqd_flag_tab(i) := 'N';
2937 End If;
2938 If NOT l_cost_rate_override_tab.exists(i) Then
2939 l_cost_rate_override_tab.extend;
2940 l_cost_rate_override_tab(i) := NULL;
2941 End If;
2942 If NOT l_burden_rate_override_tab.exists(i) Then
2943 l_burden_rate_override_tab.extend;
2944 l_burden_rate_override_tab(i) := NULL;
2945 End If;
2946 If NOT l_bill_rate_override_tab.exists(i) Then
2947 l_bill_rate_override_tab.extend;
2948 l_bill_rate_override_tab(i) := NULL;
2949 End If;
2950
2951 If NOT l_task_id_tab.exists(i) Then
2952 l_task_id_tab.extend;
2953 l_task_id_tab(i) := NULL;
2954 End If;
2955
2956 IF NOT l_ra_date_tab.exists(i) Then
2957 l_ra_date_tab.extend;
2958 l_ra_date_tab(i) := NULL;
2959 End If;
2960 IF l_txn_currency_code_ovr_tab(i) IS NULL AND
2961 (l_cost_rate_override_tab(i) is NOT NULL OR
2962 l_burden_rate_override_tab(i) is NOT NULL OR
2963 l_bill_rate_override_tab(i) is NOT NULL ) THEN
2964 l_return_status := 'E';
2965 END If;
2966 END LOOP;
2967
2968 IF l_return_status <> 'S' Then
2969 Raise l_invalid_params;
2970 End If;
2971
2972
2973 rate_rec := NULL;
2974 IF p_budget_version_id is NOT NULL Then
2975 OPEN CUR_VersionDts;
2976 FETCH CUR_VersionDts INTO rate_rec;
2977 CLOSE CUR_VersionDts;
2978 Else
2979 OPEN CUR_projDetails;
2980 FETCH CUR_projDetails INTO rate_rec;
2981 CLOSE CUR_projDetails;
2982 End If;
2983
2984
2985 IF l_return_status = 'S' Then
2986 If (p_source_context = 'RA' AND l_resource_assignment_id_tab.COUNT > 0 ) Then
2987 l_stage := '13: Calling populate_ra_details API';
2988 print_msg(l_debug_flag,l_proc_name,l_stage);
2989 populate_ra_details
2990 ( p_calling_module => p_calling_module
2991 ,p_source_context => p_source_context
2992 ,p_project_id => p_project_id
2993 ,p_project_type => rate_rec.project_type
2994 ,p_budget_version_id => p_budget_version_id
2995 ,p_resource_assignment_id_tab => l_resource_assignment_id_tab
2996 ,p_plsql_index_tab => l_plsql_index_tab
2997 ,p_task_id_tab => l_task_id_tab
2998 ,p_ra_date_tab => l_ra_date_tab
2999 ,p_quantity_tab => l_quantity_tab
3000 ,p_txn_currency_code_tab => l_txn_currency_code_tab
3001 ,p_txn_currency_code_ovr_tab => l_txn_currency_code_ovr_tab
3002 ,p_cost_rate_override_tab => l_cost_rate_override_tab
3003 ,p_burden_rate_override_tab => l_burden_rate_override_tab
3004 ,p_bill_rate_override_tab => l_bill_rate_override_tab
3005 ,x_return_status => l_return_status
3006 );
3007 ELSIF (p_source_context = 'RLMI' AND l_resource_list_member_Id_tab.COUNT > 0) THEN
3008 -- Call resource defaults to get the resource attributes.
3009 l_stage := '14: Calling populate_res_details';
3010 print_msg(l_debug_flag,l_proc_name,l_stage);
3011 populate_res_details
3012 ( p_calling_module => p_calling_module
3013 ,p_source_context => p_source_context
3014 ,p_project_id => p_project_id
3015 ,p_project_type => rate_rec.project_type
3016 ,p_budget_version_id => p_budget_version_id
3017 ,p_resource_list_member_Id_tab => l_resource_list_member_Id_tab
3018 ,p_plsql_index_tab => l_plsql_index_tab
3019 ,p_task_id_tab => l_task_id_tab
3020 ,p_ra_date_tab => l_ra_date_tab
3021 ,p_quantity_tab => l_quantity_tab
3022 ,p_txn_currency_code_tab => l_txn_currency_code_tab
3023 ,p_txn_currency_code_ovr_tab => l_txn_currency_code_ovr_tab
3024 ,p_cost_rate_override_tab => l_cost_rate_override_tab
3025 ,p_burden_rate_override_tab => l_burden_rate_override_tab
3026 ,p_bill_rate_override_tab => l_bill_rate_override_tab
3027 ,x_return_status => l_return_status
3028 );
3029 End If;
3030
3031
3032 END If;
3033
3034 IF l_return_status = 'S' Then
3035 l_stage := '15: Update tmp table with task level details';
3036 print_msg(l_debug_flag,l_proc_name,l_stage);
3037 /* update the task details */
3038 UPDATE pa_fp_res_assignments_tmp tmp
3039 SET (tmp.task_bill_rate_org_id ---non_labor_bill_rate_org_id
3040 ,tmp.task_sch_discount ---non_labor_schedule_discount
3041 ,tmp.task_sch_date ---non_labor_schedule_fixed_date
3042 ,tmp.task_std_bill_rate_sch ---non_lab_std_bill_rt_sch_id
3043 ,tmp.emp_bill_rate_schedule_id
3044 ,tmp.job_bill_rate_schedule_id
3045 ,tmp.labor_bill_rate_org_id
3046 ,tmp.labor_sch_type
3047 ,tmp.non_labor_sch_type
3048 ,tmp.top_task_id
3049 ,tmp.billable_flag
3050 ,tmp.task_name ) =
3051 (SELECT t.non_labor_bill_rate_org_id
3052 ,t.non_labor_schedule_discount
3053 ,t.non_labor_schedule_fixed_date
3054 ,t.non_lab_std_bill_rt_sch_id
3055 ,t.emp_bill_rate_schedule_id
3056 ,t.job_bill_rate_schedule_id
3057 ,t.labor_bill_rate_org_id
3058 ,t.labor_sch_type
3059 ,t.non_labor_sch_type
3060 ,t.top_task_id
3061 ,NVL(t.billable_flag,'Y')
3062 ,t.task_name
3063 FROM pa_tasks t
3064 WHERE t.task_id = tmp.task_id
3065 AND t.project_id = p_project_id
3066 )
3067 WHERE tmp.budget_version_id = p_budget_version_id
3068 AND (tmp.task_id is NOT NULL OR tmp.task_id <> 0 )
3069 AND EXISTS (select null
3070 from pa_tasks t1
3071 Where t1.task_id = tmp.task_id
3072 and t1.project_id = p_project_id
3073 );
3074 END If;
3075
3076 /* for each resource assignment in calctmp open the task cursor */
3077 IF rate_rec.fp_budget_version_type = 'REVENUE' THEN
3078 l_calculate_mode := 'REVENUE';
3079 ELSIF rate_rec.fp_budget_version_type = 'COST' THEN
3080 l_calculate_mode := 'COST';
3081 ELSIF rate_rec.fp_budget_version_type = 'ALL' THEN
3082 l_calculate_mode := 'COST_REVENUE';
3083 END IF;
3084
3085 x_resource_assignment_id_tab := SYSTEM.PA_NUM_TBL_TYPE();
3086 x_resource_list_member_Id_tab := SYSTEM.PA_NUM_TBL_TYPE();
3087 x_raw_cost_rate_tab := SYSTEM.PA_NUM_TBL_TYPE();
3088 x_burden_cost_rate_tab := SYSTEM.PA_NUM_TBL_TYPE();
3089 x_burden_multiplier_tab := SYSTEM.PA_NUM_TBL_TYPE();
3090 x_ind_compiled_set_id_tab := SYSTEM.PA_NUM_TBL_TYPE();
3091 x_bill_rate_tab := SYSTEM.PA_NUM_TBL_TYPE();
3092 x_markup_percent_tab := SYSTEM.PA_NUM_TBL_TYPE();
3093 x_txn_currency_code_tab := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3094 x_cost_txn_curr_code_tab := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3095 x_rev_txn_curr_code_tab := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3096 x_cost_rejection_code_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3097 x_burden_rejection_code_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3098 x_revenue_rejection_code_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3099 x_expenditure_ou_tab := SYSTEM.PA_NUM_TBL_TYPE();
3100 /* loop through the tmp table and call rate api for each line */
3101 l_Cntr := 0;
3102 FOR z IN CUR_RateApi_Attrbs LOOP --{
3103 /* Initialize the out params */
3104 l_Cntr := l_Cntr +1;
3105
3106 /* initialize the out params */
3107 l_rowid_tab.extend;
3108 x_resource_assignment_id_tab.extend;
3109 x_resource_list_member_Id_tab.extend;
3110 x_raw_cost_rate_tab.extend;
3111 x_burden_cost_rate_tab.extend;
3112 x_burden_multiplier_tab.extend;
3113 x_ind_compiled_set_id_tab.extend;
3114 x_bill_rate_tab.extend;
3115 x_markup_percent_tab.extend;
3116 x_txn_currency_code_tab.extend;
3117 x_cost_txn_curr_code_tab.extend;
3118 x_rev_txn_curr_code_tab.extend;
3119 x_cost_rejection_code_tab.extend;
3120 x_burden_rejection_code_tab.extend;
3121 x_revenue_rejection_code_tab.extend;
3122 x_rw_cost_rate_override_tab.extend;
3123 x_burden_rate_override_tab.extend;
3124 x_bill_rate_override_tab.extend;
3125 x_expenditure_ou_tab.extend;
3126
3127 l_rowid_tab(l_Cntr) := z.rowid;
3128 x_resource_assignment_id_tab(l_Cntr) := z.resource_assignment_id;
3129 x_resource_list_member_Id_tab(l_Cntr) := z.resource_list_member_Id;
3130 x_expenditure_ou_tab(l_Cntr) := nvl(z.rate_expenditure_org_id,rate_rec.org_id);
3131 x_raw_cost_rate_tab(l_Cntr) := null;
3132 x_burden_cost_rate_tab(l_Cntr) := null;
3133 x_burden_multiplier_tab(l_Cntr) := null;
3134 x_ind_compiled_set_id_tab(l_Cntr) := null;
3135 x_bill_rate_tab(l_Cntr) := null;
3136 x_markup_percent_tab(l_Cntr) := null;
3137 x_txn_currency_code_tab(l_Cntr) := null;
3138 x_cost_txn_curr_code_tab(l_Cntr) := null;
3139 x_rev_txn_curr_code_tab(l_Cntr) := null;
3140 x_cost_rejection_code_tab(l_Cntr) := null;
3141 x_burden_rejection_code_tab(l_Cntr) := null;
3142 x_revenue_rejection_code_tab(l_Cntr) := null;
3143 x_rw_cost_rate_override_tab(l_Cntr) := null;
3144 x_burden_rate_override_tab(l_Cntr) := null;
3145 x_bill_rate_override_tab(l_Cntr) := null;
3146
3147 /* setting the quantity to null to avoid call to rate api with 0 qty */
3148 l_txn_plan_quantity := z.quantity;
3149 l_rw_cost_rate_override := z.rw_cost_rate_override;
3150 l_burden_cost_rate_override := z.burden_cost_rate_override;
3151 l_bill_rate_override := z.bill_rate_override;
3152 l_txn_currency_code_override := z.txn_currency_code_override;
3153 l_txn_raw_cost := NULL;
3154 l_txn_burdened_cost := NULL;
3155 l_txn_revenue := NULL;
3156 x_raw_cost := NULL;
3157 x_burden_cost := NULL;
3158 x_raw_revenue := NULL;
3159 x_bill_rate := NULL;
3160 x_cost_rate := NULL;
3161 x_burden_cost_rate := NULL;
3162 x_burden_multiplier := NULL;
3163 x_bill_markup_percentage := NULL;
3164 x_cost_txn_curr_code := NULL;
3165 x_rev_txn_curr_code := NULL;
3166 x_raw_cost_rejection_code := NULL;
3167 x_burden_cost_rejection_code := NULL;
3168 x_revenue_rejection_code := NULL;
3169 x_cost_ind_compiled_set_id := NULL;
3170
3171 IF NVL(z.rate_based_flag,'N') = 'N' Then
3172 IF rate_rec.fp_budget_version_type in ('COST','ALL') Then
3173 l_txn_currency_code_override := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3174 x_cost_txn_curr_code := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3175 x_cost_rate := 1;
3176 l_rw_cost_rate_override := 1;
3177 Else
3178 l_txn_currency_code_override := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3179 x_rev_txn_curr_code := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3180 x_bill_rate := 1;
3181 l_bill_rate_override := 1;
3182 End IF;
3183 End If;
3184 l_override_organization_id := NULL;
3185 IF l_override_organization_id is NULL Then
3186 l_stage := 'Calling Override_exp_organization ';
3187 pa_cost.Override_exp_organization
3188 (P_item_date => z.start_date
3189 ,P_person_id => z.person_id
3190 ,P_project_id => p_project_id
3191 ,P_incurred_by_organz_id => z.organization_id
3192 ,P_Expenditure_type => nvl(z.expenditure_type,z.rate_expenditure_type)
3193 ,X_overr_to_organization_id => l_override_organization_id
3194 ,X_return_status => l_return_status
3195 ,X_msg_count => x_msg_count
3196 ,X_msg_data => x_msg_data
3197 );
3198 l_stage := 'End of Override_exp_organization retSts['||l_return_status||']';
3199 End If;
3200 BEGIN
3201 l_task_id := z.task_id;
3202 If l_task_id = 0 Then
3203 l_task_id := NULL;
3204 End If;
3205
3206 /* Bug fix:4133047 pass the Task level or project level labor and non-labor sch types to bill rate api in order to
3207 * derive the markup based on burden schedule or bill rate schedule
3208 */
3209 If l_task_id IS NOT NULL THEN
3210 l_labor_sch_type:= z.labor_sch_type;
3211 l_non_labor_sch_type := z.non_labor_sch_type;
3212 Else
3213 l_labor_sch_type:= rate_rec.labor_sch_type;
3214 l_non_labor_sch_type := rate_rec.non_labor_sch_type;
3215 End If;
3216
3217 l_stage := 'Calling get_planning_rates API for lineId ['||z.rbs_element_id||']';
3218 print_msg(l_debug_flag,l_proc_name,' **REQUIRED** = MUST BE PASSED TO RATE API');
3219 print_msg(l_debug_flag,l_proc_name,' p_project_id => '||p_project_id);
3220 print_msg(l_debug_flag,l_proc_name,' p_task_id => '||l_task_id);
3221 print_msg(l_debug_flag,l_proc_name,' p_top_task_id => '||z.top_task_id);
3222 print_msg(l_debug_flag,l_proc_name,' p_person_id => '||z.person_id);
3223 print_msg(l_debug_flag,l_proc_name,' p_job_id => '||z.job_id);
3224 print_msg(l_debug_flag,l_proc_name,' p_bill_job_grp_id => '||rate_rec.bill_job_group_id);
3225 print_msg(l_debug_flag,l_proc_name,' p_project_organz_id => '||rate_rec.carrying_out_organization_id);
3226 print_msg(l_debug_flag,l_proc_name,' p_rev_res_class_rate_sch_id => '||rate_rec.res_class_bill_rate_sch_id);
3227 print_msg(l_debug_flag,l_proc_name,' p_cost_res_class_rate_sch_id=> '||rate_rec.res_class_raw_cost_sch_id);
3228 print_msg(l_debug_flag,l_proc_name,' p_rev_task_nl_rate_sch_id => '||z.non_lab_std_bill_rt_sch_id);
3229 print_msg(l_debug_flag,l_proc_name,' p_rev_proj_nl_rate_sch_id => '||rate_rec.non_lab_std_bill_rt_sch_id);
3230 print_msg(l_debug_flag,l_proc_name,' p_rev_job_rate_sch_id => '||nvl(z.job_bill_rate_schedule_id,rate_rec.job_bill_rate_schedule_id));
3231 print_msg(l_debug_flag,l_proc_name,' p_rev_emp_rate_sch_id => '||nvl(z.emp_bill_rate_schedule_id,rate_rec.emp_bill_rate_schedule_id));
3232 print_msg(l_debug_flag,l_proc_name,' p_plan_rev_job_rate_sch_id => '||rate_rec.rev_job_rate_sch_id);
3233 print_msg(l_debug_flag,l_proc_name,' p_plan_cost_job_rate_sch_id => '||rate_rec.cost_job_rate_sch_id);
3234 print_msg(l_debug_flag,l_proc_name,' p_plan_rev_emp_rate_sch_id => '||rate_rec.rev_emp_rate_sch_id);
3235 print_msg(l_debug_flag,l_proc_name,' p_plan_cost_emp_rate_sch_id => '||rate_rec.cost_emp_rate_sch_id);
3236 print_msg(l_debug_flag,l_proc_name,' p_plan_rev_nlr_rate_sch_id => '||rate_rec.rev_non_labor_res_rate_sch_id);
3237 print_msg(l_debug_flag,l_proc_name,' p_plan_cost_nlr_rate_sch_id => '||rate_rec.cost_non_labor_res_rate_sch_id);
3238 print_msg(l_debug_flag,l_proc_name,' p_plan_burden_cost_sch_id => '||rate_rec.cost_burden_rate_sch_id);
3239 print_msg(l_debug_flag,l_proc_name,' p_calculate_mode => '||l_calculate_mode);
3240 print_msg(l_debug_flag,l_proc_name,' p_mcb_flag => '||rate_rec.multi_currency_billing_flag);
3241 print_msg(l_debug_flag,l_proc_name,' p_cost_rate_multiplier => '||l_cost_rate_multiplier);
3242 print_msg(l_debug_flag,l_proc_name,' p_bill_rate_multiplier => '||l_bill_rate_multiplier);
3243 print_msg(l_debug_flag,l_proc_name,' p_cost_sch_type => '||l_cost_sch_type);
3244 print_msg(l_debug_flag,l_proc_name,' p_labor_sch_type => '||rate_rec.labor_sch_type);
3245 print_msg(l_debug_flag,l_proc_name,' p_non_labor_sch_type => '||rate_rec.non_labor_sch_type);
3246 print_msg(l_debug_flag,l_proc_name,' p_labor_schdl_discnt => '||NULL);
3247 print_msg(l_debug_flag,l_proc_name,' p_labor_bill_rate_org_id => '||rate_rec.labor_bill_rate_org_id);
3248 print_msg(l_debug_flag,l_proc_name,' p_labor_std_bill_rate_schdl => '||NULL);
3249 print_msg(l_debug_flag,l_proc_name,' p_labor_schdl_fixed_date => '||NULL);
3250 print_msg(l_debug_flag,l_proc_name,' p_project_org_id => '||rate_rec.org_id);
3251 print_msg(l_debug_flag,l_proc_name,' p_project_type => '||rate_rec.project_type);
3252 print_msg(l_debug_flag,l_proc_name,' p_expenditure_type => '||nvl(z.expenditure_type,z.rate_expenditure_type));
3253 print_msg(l_debug_flag,l_proc_name,' p_non_labor_resource => '||z.non_labor_resource);
3254 print_msg(l_debug_flag,l_proc_name,' p_incurred_by_organz_id => '||z.organization_id);
3255 print_msg(l_debug_flag,l_proc_name,' p_override_to_organz_id => '||l_override_organization_id);
3256 print_msg(l_debug_flag,l_proc_name,' p_expenditure_org_id => '||nvl(z.rate_expenditure_org_id,rate_rec.org_id));
3257 print_msg(l_debug_flag,l_proc_name,' p_assignment_precedes_task => '||rate_rec.assign_precedes_task);
3258 print_msg(l_debug_flag,l_proc_name,' p_planning_transaction_id => '||z.budget_line_id);
3259 print_msg(l_debug_flag,l_proc_name,' p_task_bill_rate_org_id => '||z.non_labor_bill_rate_org_id);
3260 print_msg(l_debug_flag,l_proc_name,' p_project_bill_rate_org_id => '||rate_rec.non_labor_bill_rate_org_id);
3261 print_msg(l_debug_flag,l_proc_name,' p_nlr_organization_id => '||z.organization_id);
3262 print_msg(l_debug_flag,l_proc_name,' p_project_sch_date => '||rate_rec.non_labor_schedule_fixed_date);
3263 print_msg(l_debug_flag,l_proc_name,' p_task_sch_date => '||z.non_labor_schedule_fixed_date);
3264 print_msg(l_debug_flag,l_proc_name,' p_project_sch_discount => '||rate_rec.non_labor_schedule_discount);
3265 print_msg(l_debug_flag,l_proc_name,' p_task_sch_discount => '||z.non_labor_schedule_discount);
3266 print_msg(l_debug_flag,l_proc_name,' p_inventory_item_id => '||z.inventory_item_id);
3267 print_msg(l_debug_flag,l_proc_name,' p_BOM_resource_Id => '||z.bom_resource_id);
3268 print_msg(l_debug_flag,l_proc_name,' P_mfc_cost_type_id => '||z.mfc_cost_type_id);
3269 print_msg(l_debug_flag,l_proc_name,' P_item_category_id => '||z.item_category_id);
3270 print_msg(l_debug_flag,l_proc_name,' p_mfc_cost_source => '||l_mfc_cost_source);
3271 print_msg(l_debug_flag,l_proc_name,' ** p_assignment_id => '||z.resource_assignment_id);
3272 print_msg(l_debug_flag,l_proc_name,' ** p_rlmi_id => '||z.resource_list_member_id);
3273 print_msg(l_debug_flag,l_proc_name,' ** p_resource_class => '||z.resource_class_code);
3274 print_msg(l_debug_flag,l_proc_name,' ** p_planning_resource_format => '||z.res_format_id);
3275 print_msg(l_debug_flag,l_proc_name,' ** p_use_planning_rates_flag => '||rate_rec.use_planning_rates_flag);
3276 print_msg(l_debug_flag,l_proc_name,' ** p_rate_based_flag => '||z.rate_based_flag);
3277 print_msg(l_debug_flag,l_proc_name,' ** p_uom => '||z.unit_of_measure);
3278 print_msg(l_debug_flag,l_proc_name,' ** p_quantity => '||l_txn_plan_quantity);
3279 print_msg(l_debug_flag,l_proc_name,' ** p_item_date => '||z.start_date);
3280 print_msg(l_debug_flag,l_proc_name,' ** p_cost_override_rate => '||l_rw_cost_rate_override);
3281 print_msg(l_debug_flag,l_proc_name,' ** p_revenue_override_rate => '||l_bill_rate_override);
3282 print_msg(l_debug_flag,l_proc_name,' ** p_override_burden_cost_rate => '||l_burden_cost_rate_override);
3283 print_msg(l_debug_flag,l_proc_name,' ** p_override_currency_code => '||l_txn_currency_code_override);
3284 print_msg(l_debug_flag,l_proc_name,' ** p_txn_currency_code => '||l_txn_currency_code);
3285 print_msg(l_debug_flag,l_proc_name,' ** p_raw_cost => '||l_txn_raw_cost);
3286 print_msg(l_debug_flag,l_proc_name,' ** p_burden_cost => '||l_txn_burdened_cost);
3287 print_msg(l_debug_flag,l_proc_name,' ** p_raw_revenue => '||l_txn_revenue);
3288 print_msg(l_debug_flag,l_proc_name,' ** p_billable_flag => '||l_billable_flag);
3289 pa_plan_revenue.Get_planning_Rates
3290 (
3291 p_project_id => p_project_id
3292 ,p_task_id => l_task_id
3293 ,p_top_task_id => z.top_task_id
3294 ,p_person_id => z.person_id
3295 ,p_job_id => z.job_id
3296 ,p_bill_job_grp_id => rate_rec.bill_job_group_id
3297 ,p_resource_class => z.resource_class_code
3298 ,p_planning_resource_format => z.res_format_id
3299 ,p_use_planning_rates_flag => NVL(rate_rec.use_planning_rates_flag,'N')
3300 ,p_rate_based_flag => NVL(z.rate_based_flag,'N')
3301 ,p_uom => z.unit_of_measure
3302 ,p_system_linkage => NULL
3303 ,p_project_organz_id => rate_rec.carrying_out_organization_id
3304 ,p_rev_res_class_rate_sch_id => rate_rec.res_class_bill_rate_sch_id
3305 ,p_cost_res_class_rate_sch_id => rate_rec.res_class_raw_cost_sch_id
3306 ,p_rev_task_nl_rate_sch_id => z.non_lab_std_bill_rt_sch_id
3307 ,p_rev_proj_nl_rate_sch_id => rate_rec.non_lab_std_bill_rt_sch_id
3308 ,p_rev_job_rate_sch_id => rate_rec.job_bill_rate_schedule_id
3309 ,p_rev_emp_rate_sch_id => rate_rec.emp_bill_rate_schedule_id
3310 ,p_plan_rev_job_rate_sch_id => rate_rec.rev_job_rate_sch_id
3311 ,p_plan_cost_job_rate_sch_id => rate_rec.cost_job_rate_sch_id
3312 ,p_plan_rev_emp_rate_sch_id => rate_rec.rev_emp_rate_sch_id
3313 ,p_plan_cost_emp_rate_sch_id => rate_rec.cost_emp_rate_sch_id
3314 ,p_plan_rev_nlr_rate_sch_id => rate_rec.rev_non_labor_res_rate_sch_id
3315 ,p_plan_cost_nlr_rate_sch_id => rate_rec.cost_non_labor_res_rate_sch_id
3316 ,p_plan_burden_cost_sch_id => rate_rec.cost_burden_rate_sch_id
3317 ,p_calculate_mode => l_calculate_mode
3318 ,p_mcb_flag => rate_rec.multi_currency_billing_flag
3319 ,p_cost_rate_multiplier => l_cost_rate_multiplier
3320 ,p_bill_rate_multiplier => l_bill_rate_multiplier
3321 ,p_quantity => l_txn_plan_quantity
3322 ,p_item_date => z.start_date
3323 ,p_cost_sch_type => l_cost_sch_type
3324 ,p_labor_sch_type => l_labor_sch_type
3325 ,p_non_labor_sch_type => l_non_labor_sch_type
3326 ,p_labor_schdl_discnt => NULL
3327 ,p_labor_bill_rate_org_id => rate_rec.labor_bill_rate_org_id
3328 ,p_labor_std_bill_rate_schdl => NULL
3329 ,p_labor_schdl_fixed_date => NULL
3330 ,p_assignment_id => z.resource_assignment_id
3331 ,p_project_org_id => rate_rec.org_id
3332 ,p_project_type => rate_rec.project_type
3333 ,p_expenditure_type => nvl(z.expenditure_type,z.rate_expenditure_type)
3334 ,p_non_labor_resource => z.non_labor_resource
3335 ,p_incurred_by_organz_id => z.organization_id
3336 ,p_override_to_organz_id => l_override_organization_id
3337 ,p_expenditure_org_id => nvl(z.rate_expenditure_org_id,rate_rec.org_id)
3338 ,p_assignment_precedes_task => rate_rec.assign_precedes_task
3339 ,p_planning_transaction_id => z.budget_line_id
3340 ,p_task_bill_rate_org_id => z.non_labor_bill_rate_org_id
3341 ,p_project_bill_rate_org_id => rate_rec.non_labor_bill_rate_org_id
3342 ,p_nlr_organization_id => z.organization_id
3343 ,p_project_sch_date => rate_rec.non_labor_schedule_fixed_date
3344 ,p_task_sch_date => z.non_labor_schedule_fixed_date
3345 ,p_project_sch_discount => rate_rec.non_labor_schedule_discount
3346 ,p_task_sch_discount => z.non_labor_schedule_discount
3347 ,p_inventory_item_id => z.inventory_item_id
3348 ,p_BOM_resource_Id => z.bom_resource_id
3349 ,P_mfc_cost_type_id => z.mfc_cost_type_id
3350 ,P_item_category_id => z.item_category_id
3351 ,p_mfc_cost_source => l_mfc_cost_source
3352 ,p_cost_override_rate => l_rw_cost_rate_override
3353 ,p_revenue_override_rate => l_bill_rate_override
3354 ,p_override_burden_cost_rate => l_burden_cost_rate_override
3355 ,p_override_currency_code => l_txn_currency_code_override
3356 ,p_txn_currency_code => l_txn_currency_code
3357 ,p_raw_cost => l_txn_raw_cost
3358 ,p_burden_cost => l_txn_burdened_cost
3359 ,p_raw_revenue => l_txn_revenue
3360 ,p_billability_flag => l_billable_flag
3361 ,x_bill_rate => x_bill_rate
3362 ,x_cost_rate => x_cost_rate
3363 ,x_burden_cost_rate => x_burden_cost_rate
3364 ,x_burden_multiplier => x_burden_multiplier
3365 ,x_raw_cost => x_raw_cost
3366 ,x_burden_cost => x_burden_cost
3367 ,x_raw_revenue => x_raw_revenue
3368 ,x_bill_markup_percentage => x_bill_markup_percentage
3369 ,x_cost_txn_curr_code => x_cost_txn_curr_code
3370 ,x_rev_txn_curr_code => x_rev_txn_curr_code
3371 ,x_raw_cost_rejection_code => x_raw_cost_rejection_code
3372 ,x_burden_cost_rejection_code => x_burden_cost_rejection_code
3373 ,x_revenue_rejection_code => x_revenue_rejection_code
3374 ,x_cost_ind_compiled_set_id => x_cost_ind_compiled_set_id
3375 ,x_return_status => l_return_status
3376 ,x_msg_data => x_msg_data
3377 ,x_msg_count => x_msg_count
3378 );
3379
3380 /* in the msp flow, if the rates are not found then show it as zero, may not be possible to show the
3381 * cost /burden rejections
3382 */
3383 If p_calling_module = 'MSP' Then
3384 If l_return_status = 'E' then
3385 l_return_status := 'S';
3386 x_msg_count := 0;
3387 End If;
3388 End If;
3389 l_stage := 'Return Sts of Rate API['||l_return_status||']msgData['||x_msg_data||']';
3390 If l_return_status = 'U' Then
3391 x_return_status := l_return_status;
3392 pa_utils.add_message
3393 (p_app_short_name => 'PA'
3394 ,p_msg_name => 'PA_FP_ERROR_FROM_RATE_API_CALL'
3395 ,p_token1 => 'G_PROJECT_NAME'
3396 ,p_value1 => rate_rec.project_name
3397 ,p_token2 => 'G_TASK_NAME'
3398 ,p_value2 => z.task_name
3399 ,p_token3 => 'G_RESOURCE_NAME'
3400 ,p_value3 => z.resource_name
3401 ,p_token4 => 'TO_CHAR(L_TXN_CURRENCY_CODE)'
3402 ,p_value4 => l_txn_currency_code
3403 ,p_token5 => 'TO_CHAR(L_BUDGET_LINES_START_DATE)'
3404 ,p_value5 => to_char(z.start_date));
3405 RAISE RATEAPI_UNEXPECTED_ERRORS;
3406 End If;
3407
3408 EXCEPTION
3409 WHEN OTHERS THEN
3410 l_stage := 'Unexpected error from Rate API['||l_return_status||']msgData['||x_msg_data||']';
3411 x_raw_cost_rejection_code := substr('PA_FP_ERROR_FROM_RATE_API_CALL',1,30);
3412 x_burden_cost_rejection_code := substr(SQLERRM,1,30);
3413 x_revenue_rejection_code := substr('PA_FP_ERROR_FROM_RATE_API_CALL',1,30);
3414 x_return_status := l_return_status;
3415 pa_utils.add_message
3416 (p_app_short_name => 'PA'
3417 ,p_msg_name => 'PA_FP_ERROR_FROM_RATE_API_CALL'
3418 ,p_token1 => 'G_PROJECT_NAME'
3419 ,p_value1 => rate_rec.project_name
3420 ,p_token2 => 'G_TASK_NAME'
3421 ,p_value2 => z.task_name
3422 ,p_token3 => 'G_RESOURCE_NAME'
3423 ,p_value3 => z.resource_name
3424 ,p_token4 => 'TO_CHAR(L_TXN_CURRENCY_CODE)'
3425 ,p_value4 => l_txn_currency_code
3426 ,p_token5 => 'TO_CHAR(L_BUDGET_LINES_START_DATE)'
3427 ,p_value5 => to_char(z.start_date));
3428 RAISE RATEAPI_UNEXPECTED_ERRORS;
3429 END;
3430
3431 IF l_rw_cost_rate_override is NOT NULL Then
3432 x_cost_rate := l_rw_cost_rate_override;
3433 x_cost_txn_curr_code := l_txn_currency_code_override;
3434 End If;
3435 If l_burden_cost_rate_override is NOT NULL Then
3436 x_burden_cost_rate := l_burden_cost_rate_override;
3437 End If;
3438 If l_bill_rate_override is NOT NULL Then
3439 x_bill_rate := l_bill_rate_override;
3440 End If;
3441
3442 l_stage := 'RawValues returned from Rate API:x_cost_txn_curr_code['||x_cost_txn_curr_code||']x_cost_rate['||x_cost_rate||']';
3443 l_stage := l_stage||']x_burden_cost_rate['||x_burden_cost_rate||']x_burden_multiplier['||x_burden_multiplier||']';
3444 l_stage := l_stage||'x_rev_txn_curr_code['||x_rev_txn_curr_code||']x_bill_rate['||x_bill_rate||']';
3445 print_msg(l_debug_flag,l_proc_name,l_stage);
3446 l_stage := 'CostRejection['||x_raw_cost_rejection_code||']BurdRejection['||x_burden_cost_rejection_code||']';
3447 l_stage := l_stage||']x_revenue_rejection_code['||x_revenue_rejection_code||']';
3448 print_msg(l_debug_flag,l_proc_name,l_stage);
3449
3450 IF x_cost_rate is NOT NULL AND x_burden_cost_rejection_code is NULL AND l_return_status = 'S' Then --{
3451
3452 --convert the cost amounts to burden currency if burden rate is passed
3453 IF l_burden_cost_rate_override is NOT NULL and l_txn_currency_code_override is NOT NULL Then
3454 IF x_cost_rate is NOT NULL and
3455 x_cost_txn_curr_code <> l_txn_currency_code_override Then
3456 x_dummy_curr_code := l_txn_currency_code_override;
3457 l_stage := 'Calling multi currency api to convert raw to burden currency';
3458 x_final_txn_exch_rate := NULL;
3459 x_final_txn_rate_type := NULL;
3460 x_final_txn_rate_date := NULL;
3461 x_final_txn_raw_cost := NULL;
3462 print_msg(l_debug_flag,l_proc_name,l_stage);
3463 pa_multi_currency_txn.get_currency_amounts (
3464 p_project_id => p_project_id
3465 ,p_exp_org_id => nvl(z.rate_expenditure_org_id,rate_rec.org_id)
3466 ,p_calling_module => 'WORKPLAN'
3467 ,p_task_id => z.task_id
3468 ,p_ei_date => z.start_date
3469 ,p_denom_raw_cost => 1
3470 ,p_denom_curr_code => x_cost_txn_curr_code
3471 ,p_acct_curr_code => x_dummy_curr_code
3472 ,p_accounted_flag => 'N'
3473 ,p_acct_rate_date => x_dummy_rate_date
3474 ,p_acct_rate_type => x_dummy_rate_type
3475 ,p_acct_exch_rate => x_dummy_exch_rate
3476 ,p_acct_raw_cost => x_dummy_cost
3477 ,p_project_curr_code => l_txn_currency_code_override
3478 ,p_project_rate_type => x_final_txn_rate_type
3479 ,p_project_rate_date => x_final_txn_rate_date
3480 ,p_project_exch_rate => x_final_txn_exch_rate
3481 ,p_project_raw_cost => x_final_txn_raw_cost
3482 ,p_projfunc_curr_code => x_dummy_curr_code
3483 ,p_projfunc_cost_rate_type => x_dummy_rate_type
3484 ,p_projfunc_cost_rate_date => x_dummy_rate_date
3485 ,p_projfunc_cost_exch_rate => x_dummy_exch_rate
3486 ,p_projfunc_raw_cost => x_dummy_cost
3487 ,p_system_linkage => 'NER'
3488 ,p_structure_version_id => rate_rec.project_structure_version_id
3489 ,p_status => l_status
3490 ,p_stage => x_stage) ;
3491
3492 l_stage := 'x_final_txn_exch_rate['||x_final_txn_exch_rate||']status['||l_status||']';
3493 print_msg(l_debug_flag,l_proc_name,l_stage);
3494 IF x_final_txn_exch_rate is NULL OR l_status is NOT NULL Then
3495 x_return_status := 'U';
3496 l_return_status := 'U';
3497 pa_utils.add_message
3498 ( p_app_short_name => 'PA'
3499 ,p_msg_name => 'PA_FP_PROJ_NO_TXNCONVRATE'
3500 ,p_token1 => 'G_PROJECT_NAME'
3501 ,p_value1 => rate_rec.project_name
3502 ,p_token2 => 'FROMCURRENCY'
3503 ,p_value2 => x_cost_txn_curr_code
3504 ,p_token3 => 'TOCURRENCY'
3505 ,p_value3 => l_txn_currency_code_override
3506 ,p_token4 => 'CONVERSION_TYPE'
3507 ,p_value4 => x_final_txn_rate_type
3508 ,p_token5 => 'CONVERSION_DATE'
3509 ,p_value5 => x_final_txn_rate_date
3510 );
3511 x_msg_data := 'PA_FP_PROJ_NO_TXNCONVRATE';
3512 RAISE L_TXNCONVRATE_ERROR;
3513 END IF;
3514 IF NVL(l_return_status,'S') = 'S' Then
3515 x_cost_rate := x_final_txn_exch_rate * x_cost_rate;
3516 x_cost_txn_curr_code := l_txn_currency_code_override;
3517 End If;
3518
3519 End If;
3520 END IF;
3521 END IF; --}
3522 /* Assign derived values to the out params */
3523 l_stage := 'Assigning derived values to out params:'||x_cost_rate||':'||x_burden_cost_rate||':'||x_bill_rate;
3524 x_raw_cost_rate_tab(l_Cntr) := x_cost_rate;
3525 x_burden_cost_rate_tab(l_Cntr) := x_burden_cost_rate;
3526 x_burden_multiplier_tab(l_Cntr) := x_burden_multiplier;
3527 x_ind_compiled_set_id_tab(l_Cntr) := x_cost_ind_compiled_set_id;
3528 x_bill_rate_tab(l_Cntr) := x_bill_rate;
3529 x_markup_percent_tab(l_Cntr) := x_bill_markup_percentage;
3530 x_txn_currency_code_tab(l_Cntr) := null;
3531 x_cost_txn_curr_code_tab(l_Cntr):= x_cost_txn_curr_code;
3532 x_rev_txn_curr_code_tab(l_Cntr):= x_rev_txn_curr_code;
3533 x_cost_rejection_code_tab(l_Cntr) := x_raw_cost_rejection_code;
3534 x_burden_rejection_code_tab(l_Cntr) := x_burden_cost_rejection_code;
3535 x_revenue_rejection_code_tab(l_Cntr) := x_revenue_rejection_code;
3536
3537 END LOOP; --}
3538 print_msg(l_debug_flag,l_proc_name,'End of rate api loop');
3539 /*** Not required as re arrangig the plsql indexes
3540 IF l_rowid_tab.COUNT > 0 Then
3541 l_stage := 'Inserting rate api values into rollup tmp table';
3542 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
3543 INSERT INTO pa_fp_rollup_tmp tmp
3544 (resource_assignment_id
3545 ,system_reference1
3546 ,txn_currency_code
3547 ,cost_rate
3548 ,rw_cost_rate_override
3549 ,burden_cost_rate
3550 ,burden_cost_rate_override
3551 ,bill_rate
3552 ,bill_rate_override
3553 ,burden_multiplier
3554 ,bill_markup_percentage
3555 ,cost_txn_curr_code
3556 ,rev_txn_curr_code
3557 ,cost_ind_compiled_set_id
3558 ,cost_rejection_code
3559 ,burden_rejection_code
3560 ,revenue_rejection_code
3561 ) VALUES
3562 (x_resource_assignment_id_tab(i)
3563 ,x_resource_list_member_id_tab(i)
3564 ,x_txn_currency_code_tab(i)
3565 ,x_raw_cost_rate_tab(i)
3566 ,x_rw_cost_rate_override_tab(i)
3567 ,x_burden_cost_rate_tab(i)
3568 ,x_burden_rate_override_tab(i)
3569 ,x_bill_rate_tab(i)
3570 ,x_bill_rate_override_tab(i)
3571 ,x_burden_multiplier_tab(i)
3572 ,x_markup_percent_tab(i)
3573 ,x_cost_txn_curr_code_tab(i)
3574 ,x_rev_txn_curr_code_tab(i)
3575 ,x_ind_compiled_set_id_tab(i)
3576 ,x_cost_rejection_code_tab(i)
3577 ,x_burden_rejection_code_tab(i)
3578 ,x_revenue_rejection_code_tab(i)
3579 );
3580 END IF;
3581 ***/
3582
3583 x_return_status := l_return_status;
3584 l_stage := 'Return status of Get_resource_Rates['||x_return_status||']';
3585 print_msg(l_debug_flag,l_proc_name,l_stage);
3586 /* added this to avoid msg added in the stack during bill rate api */
3587 IF x_return_status = 'S' then
3588 FND_MSG_PUB.initialize;
3589 End If;
3590 EXCEPTION
3591
3592 WHEN L_INVALID_PARAMS THEN
3593 PRINT_msg('Y',l_proc_name,'INVALID PARAMS FOR PROCESSING');
3594 x_return_status := 'E';
3595 RAISE;
3596
3597 WHEN RATEAPI_UNEXPECTED_ERRORS then
3598 PRINT_msg('Y',l_proc_name,'Rate API returned with unexpected error');
3599 x_return_status := 'U';
3600 RAISE;
3601 WHEN L_TXNCONVRATE_ERROR THEN
3602 PRINT_msg('Y',l_proc_name,'Error from Multi-Currency API');
3603 x_return_status := 'U';
3604 RAISE;
3605
3606 WHEN OTHERS THEN
3607 PRINT_msg('Y',l_proc_name,'EXCEPTIONS: '||SQLCODE||SQLERRM);
3608 x_return_status := 'U';
3609 RAISE;
3610
3611 END Get_Resource_Rates;
3612
3613 /** MRC Elimination: Moved this procedure from pa_mrc_finplan pkg to utils as
3614 * package itself is dropped
3615 */
3616 PROCEDURE POPULATE_BL_MAP_TMP
3617 (p_source_fin_plan_version_id IN PA_BUDGET_LINES.budget_version_id%TYPE
3618 ,x_return_status OUT NOCOPY VARCHAR2
3619 ,x_msg_count OUT NOCOPY NUMBER
3620 ,x_msg_data OUT NOCOPY VARCHAR2
3621 ) IS
3622
3623 CURSOR C_TMP_BUDGET_LINE IS
3624 SELECT
3625 budget_line_id
3626 ,pa_budget_lines_s.nextval
3627
3628 FROM
3629 pa_budget_lines
3630 WHERE
3631 budget_version_id = p_source_fin_plan_version_id ;
3632
3633
3634
3635 l_msg_count NUMBER := 0;
3636 l_data VARCHAR2(2000);
3637 l_msg_data VARCHAR2(2000);
3638 l_error_msg_code VARCHAR2(30);
3639 l_msg_index_out NUMBER;
3640 l_debug_mode VARCHAR2(30);
3641 g_module_name VARCHAR2(30) := 'pa.plsql.PA_FIN_PLAN_UTILS2';
3642 g_plsql_max_array_size NUMBER := 200 ;
3643
3644 TYPE l_budget_line_id_tbl_typ IS TABLE OF
3645 pa_budget_lines.BUDGET_LINE_ID%TYPE INDEX BY BINARY_INTEGER ;
3646
3647 l_source_budget_line_id_tbl l_budget_line_id_tbl_typ ;
3648 l_target_budget_line_id_tbl l_budget_line_id_tbl_typ ;
3649
3650
3651 BEGIN
3652
3653 -- Set the error stack.
3654 pa_debug.set_err_stack('PA_MRC_FINPLAN.POPULATE_BL_MAP_TMP');
3655
3656 -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3657 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3658 l_debug_mode := NVL(l_debug_mode, 'Y');
3659
3660 -- Initialize the return status to success
3661 x_return_status := FND_API.G_RET_STS_SUCCESS;
3662 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3663
3664 IF P_PA_DEBUG_MODE = 'Y' THEN
3665 pa_debug.g_err_stage := 'In PA_MRC_FINPLAN.POPULATE_BL_MAP_TMP ';
3666 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,pa_debug.g_err_stage,3);
3667 END IF;
3668
3669 -- Validate the input parameters.
3670
3671 IF p_source_fin_plan_version_id IS NULL THEN
3672
3673 IF P_PA_DEBUG_MODE = 'Y' THEN
3674 pa_debug.g_err_stage := 'Mandatory input parameter is null.';
3675 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,pa_debug.g_err_stage,5);
3676
3677 pa_debug.g_err_stage := 'Source Budget Version Id = ' || p_source_fin_plan_version_id;
3678 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,pa_debug.g_err_stage,5);
3679 END IF;
3680
3681 x_return_status := FND_API.G_RET_STS_ERROR;
3682
3683 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
3684 ,p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3685
3686 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3687
3688 END IF;
3689
3690 -- Clear the PA_FP_BL_MAP_TMP before inserting fresh records
3691 DELETE FROM PA_FP_BL_MAP_TMP;
3692
3693 OPEN C_TMP_BUDGET_LINE ;
3694
3695 LOOP
3696 -- Doing bulk fetch
3697 l_source_budget_line_id_tbl.delete;
3698 l_target_budget_line_id_tbl.delete;
3699 FETCH C_TMP_BUDGET_LINE BULK COLLECT INTO
3700 l_source_budget_line_id_tbl
3701 ,l_target_budget_line_id_tbl
3702 LIMIT g_plsql_max_array_size;
3703
3704 /* Commented for bug# 2629138:
3705 EXIT WHEN C_TMP_BUDGET_LINE%NOTFOUND; */
3706
3707 IF NVL(l_target_budget_line_id_tbl.last,0) >= 1 THEN
3708
3709 -- Only if something is fetched
3710
3711 FORALL i in l_target_budget_line_id_tbl.first..l_target_budget_line_id_tbl.last
3712
3713 INSERT INTO PA_FP_BL_MAP_TMP
3714 ( source_budget_line_id
3715 ,target_budget_line_id
3716 )
3717 VALUES ( l_source_budget_line_id_tbl(i)
3718 ,l_target_budget_line_id_tbl(i)
3719 );
3720
3721 END IF;
3722
3723 --exit loop if the recent fetch size is less than 200
3724
3725 EXIT WHEN NVL(l_target_budget_line_id_tbl.last,0)<g_plsql_max_array_size;
3726
3727 END LOOP;
3728 CLOSE C_TMP_BUDGET_LINE; -- Added for bug#6320022
3729 --Bug 2628051:- stack should be reset at the end of the api
3730 pa_debug.reset_err_stack;
3731
3732 EXCEPTION
3733
3734 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3735 l_msg_count := FND_MSG_PUB.count_msg;
3736 IF l_msg_count = 1 THEN
3737 PA_INTERFACE_UTILS_PUB.get_messages
3738 (p_encoded => FND_API.G_TRUE
3739 ,p_msg_index => 1
3740 ,p_msg_count => l_msg_count
3741 ,p_msg_data => l_msg_data
3742 ,p_data => l_data
3743 ,p_msg_index_out => l_msg_index_out);
3744 x_msg_data := l_data;
3745 x_msg_count := l_msg_count;
3746 ELSE
3747 x_msg_count := l_msg_count;
3748 END IF;
3749 x_return_status := FND_API.G_RET_STS_ERROR;
3750 IF P_PA_DEBUG_MODE = 'Y' THEN
3751 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,'Invalid Arguments Passed. ' || x_msg_data,5);
3752 pa_debug.write_file('POPULATE_BL_MAP_TMP: Invalid Arguments Passed. ' || x_msg_data);
3753 END IF;
3754 pa_debug.reset_err_stack;
3755 RAISE;
3756
3757 WHEN Others THEN
3758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3759 x_msg_count := 1;
3760 x_msg_data := SQLERRM;
3761 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MRC_FINPLAN'
3762 ,p_procedure_name => 'POPULATE_BL_MAP_TMP'
3763 ,p_error_text => SQLERRM);
3764 IF P_PA_DEBUG_MODE = 'Y' THEN
3765 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,x_msg_data,4);
3766 pa_debug.write_file('POPULATE_BL_MAP_TMP: ' || x_msg_data);
3767 END IF;
3768 pa_debug.reset_err_stack;
3769 RAISE ;--FND_API.G_EXC_UNEXPECTED_ERROR;
3770
3771 END POPULATE_BL_MAP_TMP ;
3772
3773 END PA_FIN_PLAN_UTILS2 ;