[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.3 2009/06/25 11:02:56 rthumma 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 ,bv.approved_rev_plan_type_flag -- Bug 5845142
2036 FROM pa_budget_versions bv
2037 WHERE bv.budget_version_id = p_budget_version_id;
2038
2039 CURSOR cur_agr(p_agr_id Number) IS
2040 SELECT agr.agreement_currency_code
2041 FROM pa_agreements_all agr
2042 WHERE agr.agreement_id = p_agr_id;
2043
2044
2045 l_agreement_id Number;
2046 l_ci_id Number;
2047 l_agr_curr_code Varchar2(100);
2048 l_agr_con_reqd_flag varchar2(1) := 'N';
2049 l_version_name pa_budget_versions.version_name%type;
2050 l_version_type pa_budget_versions.version_type%type;
2051 l_error_msg_code Varchar2(100);
2052 INVALID_EXCEPTION EXCEPTION;
2053 l_debug_flag varchar2(100);
2054 l_proc_name varchar2(100) := 'Get_Agreement_Details';
2055 G_AGR_CONV_REQD_FLAG varchar2(1) := 'N';
2056 G_AGR_CURRENCY_CODE varchar2(100);
2057
2058 --Bug 5845142
2059 l_approved_rev_plan_type_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
2060
2061 BEGIN
2062 IF p_pa_debug_mode = 'Y' THEN
2063 pa_debug.init_err_stack('PA_FIN_PLAN_UTILS2.Get_Agreement_Details');
2064 END IF;
2065 fnd_profile.get('PA_DEBUG_MODE',l_debug_flag);
2066 l_debug_flag := NVL(l_debug_flag, 'N');
2067
2068 IF p_pa_debug_mode = 'Y' THEN
2069 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2070 ,x_write_file => 'LOG'
2071 ,x_debug_mode => l_debug_flag
2072 );
2073 END IF;
2074 print_msg(l_debug_flag,l_proc_name,'Entered Get_Agreement_Details Api');
2075
2076 x_return_status := 'S';
2077 OPEN cur_bv;
2078 FETCH cur_bv INTO
2079 l_agreement_id
2080 ,l_ci_id
2081 ,l_version_type
2082 ,l_version_name
2083 ,l_approved_rev_plan_type_flag; -- Bug 5845142
2084 CLOSE cur_bv ;
2085 l_agr_con_reqd_flag := 'N';
2086 l_agr_curr_code := Null;
2087 print_msg(l_debug_flag,l_proc_name,'VersionType['||l_version_type||']CiId['||l_ci_id||']AgrId['||l_agreement_id||']');
2088 -- Bug 5845142. Amounts should be in agreement currency only for approved revenue impacts.
2089 IF ( l_version_type in ('ALL','REVENUE') AND
2090 l_approved_rev_plan_type_flag ='Y' ) Then
2091 If l_ci_id is NOT NULL Then
2092 print_msg(l_debug_flag,l_proc_name,'This is a change order/change request budget');
2093 IF l_agreement_id is NULL Then
2094 -- add error msg to stack
2095 l_error_msg_code := 'PA_FP_MISSING_AGR_REV_IMPACT';
2096 x_return_status := 'E';
2097 raise INVALID_EXCEPTION;
2098 ELSE
2099 OPEN cur_agr(l_agreement_id);
2100 FETCH cur_agr INTO l_agr_curr_code;
2101 CLOSE cur_agr;
2102 print_msg(l_debug_flag,l_proc_name,'Agreement Currency code['||l_agr_curr_code||']');
2103 IF l_agr_curr_code is NULL Then
2104 l_error_msg_code := 'PA_FP_MISSING_AGR_CURCODE';
2105 l_agr_con_reqd_flag := 'N';
2106 l_agr_curr_code := null;
2107 x_return_status := 'E';
2108 raise INVALID_EXCEPTION;
2109 Else
2110 l_agr_con_reqd_flag := 'Y';
2111 x_agr_curr_code := l_agr_curr_code;
2112 x_AGR_CONV_REQD_FLAG := l_agr_con_reqd_flag;
2113 End IF;
2114 End IF;
2115 Else
2116 l_agr_con_reqd_flag := 'N';
2117 l_agr_curr_code := null;
2118 End If;
2119 END IF;
2120
2121 /* Set the global varaibles to call conv rates api*/
2122 G_AGR_CONV_REQD_FLAG := l_agr_con_reqd_flag;
2123 G_AGR_CURRENCY_CODE := x_agr_curr_code;
2124 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||']');
2125 /* set the output variables */
2126 x_agr_curr_code := G_AGR_CURRENCY_CODE;
2127 x_AGR_CONV_REQD_FLAG := G_AGR_CONV_REQD_FLAG;
2128 -- reset error stack
2129 IF p_pa_debug_mode = 'Y' THEN
2130 pa_debug.reset_err_stack;
2131 END IF;
2132 EXCEPTION
2133 WHEN INVALID_EXCEPTION THEN
2134 If p_calling_mode = 'CALCULATE_API' Then
2135 pa_utils.Add_Message
2136 (p_app_short_name => 'PA'
2137 ,p_msg_name => l_error_msg_code
2138 ,p_token1 => 'BUDGET_VERSION_ID'
2139 ,p_value1 => p_budget_version_id
2140 ,p_token2 => 'VERSIONNAME'
2141 ,p_value2 => l_version_name
2142 );
2143 x_return_status := 'E';
2144 x_agr_curr_code := NULL;
2145 x_AGR_CONV_REQD_FLAG := 'N';
2146 RAISE ;
2147 ELSE
2148 x_return_status := 'S';
2149 x_agr_curr_code := NULL;
2150 x_AGR_CONV_REQD_FLAG := 'N';
2151 END IF;
2152
2153 WHEN OTHERS THEN
2154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2155 x_agr_curr_code := NULL;
2156 fnd_msg_pub.add_exc_msg
2157 ( p_pkg_name => 'PA_FIN_PLAN_UTILS2'
2158 ,p_procedure_name => 'Get_Agreement_Details' );
2159 print_msg(l_debug_flag,l_proc_name,'Failed in Get_Agreement_Details substr(SQLERRM,1,240) => '|| substr(SQLERRM,1,240));
2160 IF p_pa_debug_mode = 'Y' THEN
2161 pa_debug.reset_err_stack;
2162 END IF;
2163 RAISE;
2164 END Get_Agreement_Details;
2165
2166 /* This API rounds off the given quantity to 5 decimal places. This API should be called for rounding the quantity
2167 * for rate based planning transaction only.
2168 * This API accepts the following parameters
2169 */
2170 FUNCTION round_quantity
2171 (P_quantity IN Number
2172 ) RETURN NUMBER IS
2173
2174 l_rounded_quantity Number := P_quantity;
2175 BEGIN
2176 If P_quantity is NOT NULL Then
2177
2178 l_rounded_quantity := round(P_quantity,5);
2179 End If;
2180 RETURN l_rounded_quantity;
2181
2182 EXCEPTION
2183 WHEN OTHERS THEN
2184 RAISE;
2185
2186 END round_quantity;
2187
2188 /* This API checks the given financial Task is billable or not
2189 * If task is billable, it returns 'Y' else 'N'
2190 */
2191 FUNCTION IsFpTaskBillable(p_project_id NUMBER
2192 ,p_task_id NUMBER) RETURN varchar2 IS
2193
2194 CURSOR cur_TaskBillable IS
2195 SELECT NVL(t.billable_flag,'N')
2196 FROM pa_tasks t
2197 WHERE t.task_id = p_task_id
2198 AND t.project_id = p_project_id;
2199
2200 X_billable_flag Varchar2(10);
2201 l_RecFound BOOLEAN := FALSE;
2202 l_projTaskId NUMBER;
2203 BEGIN
2204 /* Note: As suggested by venkatesh for project level always set the billable flag as Y */
2205 IF p_project_id is NOT NULL AND NVL(p_task_id,0) = 0 Then
2206 X_billable_flag := 'Y';
2207 ELSIF p_project_id is NOT NULL AND NVL(p_task_id,0) <> 0 THEN --{
2208 /* l_projTaskId := p_project_id||p_task_id; This is not required as this may lead to corruption
2209 * P1||T1 = 33||3 similarly P1||T1 = 3||33 will give the same results*/
2210 l_projTaskId := p_task_id;
2211 --print_msg('Y','IsFpTaskBillable','l_projTaskId['||l_projTaskId||']Count['||G_FpTaskBillable_Tab.COUNT||']');
2212 If G_FpTaskBillable_Tab.COUNT > 0 Then
2213 Begin
2214 /*Get the Project Number from the pl/sql table.
2215 *If there is no index with the value of the project_id passed
2216 *in then an ora-1403: no_data_found is generated.
2217 */
2218 X_billable_flag := G_FpTaskBillable_Tab(l_projTaskId).Billable_Flag;
2219 l_RecFound := TRUE;
2220
2221 Exception
2222 When No_Data_Found Then
2223 l_RecFound := FALSE;
2224 When Others Then
2225 Raise;
2226
2227 End;
2228 End If;
2229
2230 If Not l_RecFound Then
2231 --print_msg('Y','IsFpTaskBillable','l_projTaskId['||l_projTaskId||']Executing cursor to get BillableFlag');
2232 -- Since the project has not been cached yet, will need to add it.
2233 -- So check to see if there are already 200 records in the pl/sql table.
2234 If G_FpTaskBillable_Tab.COUNT > 199 Then
2235 G_FpTaskBillable_Tab.Delete;
2236 End If;
2237 X_billable_flag := 'N';
2238 OPEN cur_TaskBillable;
2239 FETCH cur_TaskBillable INTO X_billable_flag;
2240 IF cur_TaskBillable%NOTFOUND Then
2241 X_billable_flag := 'N';
2242 End If;
2243 CLOSE cur_TaskBillable;
2244
2245 -- Add the billable Flag to the pl/sql table using the project_id||TaskId combination
2246 G_FpTaskBillable_Tab(l_projTaskId).Billable_Flag := NVL(X_billable_flag,'N');
2247
2248 End If;
2249
2250 END IF; --}
2251 --print_msg('Y','IsFpTaskBillable','X_billable_flag['||X_billable_flag||']');
2252 RETURN NVL(X_billable_flag,'N');
2253
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 fnd_msg_pub.add_exc_msg
2257 ( p_pkg_name => 'PA_FIN_PLAN_UTILS2'
2258 ,p_procedure_name => 'IsFpTaskBillable');
2259 print_msg('Y','IsFpTaskBillable','Failed in IsFpTaskBillable => '|| substr(SQLERRM,1,240));
2260 RAISE;
2261
2262 END IsFpTaskBillable;
2263
2264 PROCEDURE populate_res_details
2265 ( p_calling_module IN VARCHAR2
2266 ,p_source_context IN VARCHAR2
2267 ,p_project_id IN NUMBER
2268 ,p_project_type IN VARCHAR2
2269 ,p_budget_version_id IN NUMBER
2270 ,p_resource_list_member_Id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2271 ,p_plsql_index_tab IN SYSTEM.PA_NUM_TBL_TYPE
2272 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE
2273 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2274 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE
2275 ,p_txn_currency_code_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2276 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2277 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2278 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2279 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2280 ,x_return_status OUT NOCOPY VARCHAR2
2281 ) IS
2282
2283 --Start of variables for Variable for Resource Attributes
2284 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2285 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2286 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2287 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2288 l_incur_by_res_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2289 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2290 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2291 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2292 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
2293 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2294 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := SYSTEM.PA_VARCHAR2_20_TBL_TYPE();
2295 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2296 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2297 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2298 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2299 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2300 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2301 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2302 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2303 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2304 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2305 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2306 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2307 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2308 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2309 l_procure_resource_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2310 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2311 l_Incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2312 l_Incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2313 l_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2314 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2315 l_rate_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2316 l_rate_func_curr_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2317 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2318 l_assignment_description_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
2319 l_planning_resource_alias_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
2320 l_resource_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2321 l_project_role_name_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
2322 l_organization_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
2323 l_financial_category_code_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
2324 l_project_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2325 l_use_task_schedule_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2326 l_planning_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2327 l_planning_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2328 l_total_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2329 l_override_currency_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2330 l_billable_percent_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2331 l_cost_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2332 l_burdened_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2333 l_sp_fixed_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2334 l_financial_category_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2335 l_supplier_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
2336 --End of variables for Variable for Resource Attributes
2337 l_msg_data Varchar2(1000);
2338 l_msg_count Number;
2339 l_return_status Varchar2(1) := 'S';
2340
2341 BEGIN
2342 x_return_status := 'S';
2343 --This api derives the resource defaults
2344 IF p_resource_list_member_Id_tab.COUNT > 0 THEN --{
2345 print_msg(p_pa_debug_mode,'populate_res_details','Calling get_resource_defaults');
2346 PA_PLANNING_RESOURCE_UTILS.get_resource_defaults(
2347 p_resource_list_members => p_resource_list_member_Id_tab
2348 ,p_project_id => p_project_id
2349 ,x_resource_class_flag => l_resource_class_flag_tbl
2350 ,x_resource_class_code => l_resource_class_code_tbl
2351 ,x_resource_class_id => l_resource_class_id_tbl
2352 ,x_res_type_code => l_res_type_code_tbl
2353 ,x_incur_by_res_type => l_incur_by_res_type_tbl
2354 ,x_person_id => l_person_id_tbl
2355 ,x_job_id => l_job_id_tbl
2356 ,x_person_type_code => l_person_type_code_tbl
2357 ,x_named_role => l_named_role_tbl
2358 ,x_bom_resource_id => l_bom_resource_id_tbl
2359 ,x_non_labor_resource => l_non_labor_resource_tbl
2360 ,x_inventory_item_id => l_inventory_item_id_tbl
2361 ,x_item_category_id => l_item_category_id_tbl
2362 ,x_project_role_id => l_project_role_id_tbl
2363 ,x_organization_id => l_organization_id_tbl
2364 ,x_fc_res_type_code => l_fc_res_type_code_tbl
2365 ,x_expenditure_type => l_expenditure_type_tbl
2366 ,x_expenditure_category => l_expenditure_category_tbl
2367 ,x_event_type => l_event_type_tbl
2368 ,x_revenue_category_code => l_revenue_category_code_tbl
2369 ,x_supplier_id => l_supplier_id_tbl
2370 ,x_unit_of_measure => l_unit_of_measure_tbl
2371 ,x_spread_curve_id => l_spread_curve_id_tbl
2372 ,x_etc_method_code => l_etc_method_code_tbl
2373 ,x_mfc_cost_type_id => l_mfc_cost_type_id_tbl
2374 ,x_incurred_by_res_flag => l_incurred_by_res_flag_tbl
2375 ,x_incur_by_res_class_code => l_incur_by_res_class_code_tbl
2376 ,x_Incur_by_role_id => l_Incur_by_role_id_tbl
2377 ,x_org_id => l_org_id_tbl
2378 ,X_rate_based_flag => l_rate_based_flag_tbl
2379 ,x_rate_expenditure_type => l_rate_expenditure_type_tbl
2380 ,x_rate_func_curr_code => l_rate_func_curr_code_tbl
2381 ,x_msg_data => l_msg_data
2382 ,x_msg_count => l_msg_count
2383 ,x_return_status => l_return_status
2384 );
2385 print_msg(p_pa_debug_mode,'populate_res_details','RetunSts['||l_return_status||']MsgData['||l_msg_data||']');
2386
2387 IF NVL(l_return_status,'S') = 'S' Then
2388 print_msg(p_pa_debug_mode,'populate_res_details','populating res assignments tmp');
2389 FORALL i IN p_resource_list_member_Id_tab.FIRST .. p_resource_list_member_Id_tab.LAST
2390 INSERT INTO pa_fp_res_assignments_tmp
2391 (project_id
2392 ,budget_version_id
2393 ,resource_assignment_id
2394 ,resource_list_member_id
2395 ,unit_of_measure
2396 ,resource_class_code
2397 ,organization_id
2398 ,job_id
2399 ,person_id
2400 ,expenditure_type
2401 ,expenditure_category
2402 ,non_labor_resource
2403 ,bom_resource_id
2404 ,inventory_item_id
2405 ,item_category_id
2406 ,mfc_cost_type_id
2407 --,rate_job_id
2408 ,rate_expenditure_type
2409 ,rate_based_flag
2410 ,rate_expenditure_org_id
2411 --,res_format_id
2412 ,project_type
2413 ,org_id
2414 ,rbs_element_id
2415 ) VALUES
2416 (p_project_id
2417 ,NVL(p_budget_version_id,-9999)
2418 ,-9999 --raid
2419 ,p_resource_list_member_Id_tab(i)
2420 ,l_unit_of_measure_tbl(i)
2421 ,l_resource_class_code_tbl(i)
2422 ,l_organization_id_tbl(i)
2423 ,l_job_id_tbl(i)
2424 ,l_person_id_tbl(i)
2425 ,l_expenditure_type_tbl(i)
2426 ,l_expenditure_category_tbl(i)
2427 ,l_non_labor_resource_tbl(i)
2428 ,l_bom_resource_id_tbl(i)
2429 ,l_inventory_item_id_tbl(i)
2430 ,l_item_category_id_tbl(i)
2431 ,l_mfc_cost_type_id_tbl(i)
2432 --,l_rate_job_id_tbl(i)
2433 ,l_rate_expenditure_type_tbl(i)
2434 ,l_rate_based_flag_tbl(i)
2435 ,l_org_id_tbl(i) --l_rate_expenditure_org_id_tbl(i)
2436 --,to_number(null) --l_res_format_id_tbl(i)
2437 ,p_project_type
2438 ,l_org_id_tbl(i)
2439 ,p_plsql_index_tab(i)
2440 )
2441 ;
2442
2443 /* Now Update the tmp table with rates and currencys passed */
2444 FORALL i IN p_resource_list_member_Id_tab.FIRST .. p_resource_list_member_Id_tab.LAST
2445 UPDATE pa_fp_res_assignments_tmp TMP
2446 SET tmp.txn_currency_code = p_txn_currency_code_tab(i)
2447 ,txn_currency_code_override = p_txn_currency_code_ovr_tab(i)
2448 ,rw_cost_rate_override = p_cost_rate_override_tab(i)
2449 ,burden_cost_rate_override = p_burden_rate_override_tab(i)
2450 ,bill_rate_override = p_bill_rate_override_tab(i)
2451 ,task_id = p_task_id_tab(i)
2452 ,txn_plan_quantity = p_quantity_tab(i)
2453 ,line_start_date = NVL(p_ra_date_tab(i),trunc(sysdate))
2454 WHERE tmp.budget_version_id = NVL(p_budget_version_id,-9999)
2455 AND tmp.resource_list_member_id = p_resource_list_member_Id_tab(i);
2456
2457 END IF;
2458
2459 END IF; --}
2460 x_return_status := l_return_status;
2461 print_msg(p_pa_debug_mode,'populate_res_details','End of populate_res_details retSts['||x_return_status||']');
2462
2463 EXCEPTION
2464 WHEN OTHERS THEN
2465 print_msg('Y','populate_res_details','Error occured at populate_res_details: '||sqlcode||sqlerrm);
2466 x_return_status := 'U';
2467 RAISE;
2468 END populate_res_details;
2469
2470 PROCEDURE populate_ra_details
2471 ( p_calling_module IN VARCHAR2
2472 ,p_source_context IN VARCHAR2
2473 ,p_project_id IN NUMBER
2474 ,p_project_type IN VARCHAR2
2475 ,p_budget_version_id IN NUMBER
2476 ,p_resource_assignment_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2477 ,p_plsql_index_tab IN SYSTEM.PA_NUM_TBL_TYPE
2478 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE
2479 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE
2480 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE
2481 ,p_txn_currency_code_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2482 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2483 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2484 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2485 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE
2486 ,x_return_status OUT NOCOPY VARCHAR2
2487 ) IS
2488
2489 BEGIN
2490 x_return_status := 'S';
2491 IF p_resource_assignment_id_tab.COUNT > 0 Then
2492 print_msg(p_pa_debug_mode,'populate_ra_details','bulk insert to res_assignments_tmp table');
2493 FORALL i IN p_resource_assignment_id_tab.FIRST .. p_resource_assignment_id_tab.LAST
2494 INSERT INTO pa_fp_res_assignments_tmp
2495 (project_id
2496 ,budget_version_id
2497 ,resource_assignment_id
2498 ,resource_list_member_id
2499 ,line_start_date
2500 ,txn_currency_code
2501 ,txn_currency_code_override
2502 ,rw_cost_rate_override
2503 ,burden_cost_rate_override
2504 ,bill_rate_override
2505 ,task_id
2506 ,unit_of_measure
2507 ,resource_class_code
2508 ,organization_id
2509 ,job_id
2510 ,person_id
2511 ,expenditure_type
2512 ,expenditure_category
2513 ,non_labor_resource
2514 ,bom_resource_id
2515 ,inventory_item_id
2516 ,item_category_id
2517 ,mfc_cost_type_id
2518 ,rate_job_id
2519 ,rate_expenditure_type
2520 ,rate_based_flag
2521 ,rate_expenditure_org_id
2522 ,project_type
2523 ,rbs_element_id
2524 ) SELECT
2525 p_project_id
2526 ,NVL(p_budget_version_id,-9999)
2527 ,p_resource_assignment_id_tab(i)
2528 ,ra.resource_list_member_id
2529 ,NVL(p_ra_date_tab(i),trunc(sysdate))
2530 ,p_txn_currency_code_tab(i)
2531 ,p_txn_currency_code_ovr_tab(i)
2532 ,p_cost_rate_override_tab(i)
2533 ,p_burden_rate_override_tab(i)
2534 ,p_bill_rate_override_tab(i)
2535 ,ra.task_id
2536 ,ra.unit_of_measure
2537 ,ra.resource_class_code
2538 ,ra.organization_id
2539 ,ra.job_id
2540 ,ra.person_id
2541 ,ra.expenditure_type
2542 ,ra.expenditure_category
2543 ,ra.non_labor_resource
2544 ,ra.bom_resource_id
2545 ,ra.inventory_item_id
2546 ,ra.item_category_id
2547 ,ra.mfc_cost_type_id
2548 ,ra.rate_job_id
2549 ,ra.rate_expenditure_type
2550 ,NVL(ra.rate_based_flag,'N') rate_based_flag
2551 ,ra.rate_expenditure_org_id
2552 ,p_project_type
2553 ,p_plsql_index_tab(i)
2554 FROM pa_resource_assignments ra
2555 WHERE ra.resource_assignment_id = p_resource_assignment_id_tab(i);
2556
2557 END IF;
2558 print_msg(p_pa_debug_mode,'populate_ra_details','End of populate_ra_details retSts['||x_return_status||']');
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 x_return_status := 'U';
2562 print_msg('Y','populate_ra_details','Error occured at populate_ra_details: '||sqlcode||sqlerrm);
2563 RAISE;
2564 END populate_ra_details;
2565
2566 /* Bug Fix:4621597: Added new generic api to derive rates for RA / RLMI
2567 * This is a generic wrapper API to call the get planning rates to derive raw cost rate, burden rate and bill rates
2568 * The default and possible values for the IN params
2569 * p_calling_module IN VARCHAR2 := 'MSP'
2570 * p_source_context IN VARCHAR2 := 'RLMI' -- Resource List member context
2571 * 'RA' -- Resource assignment context
2572 * if p_budget_version_id is NULL Then it will be treated as the 'COST' only version and attributes will be defaulted from the
2573 * project level.
2574 * p_porject_id is NOT NULL param
2575 * If p_source_context = 'RLMI' then p_resource_list_member_Id_tab must be passed
2576 * If p_source_context = 'RA' then p_resource_assignment_id_tab must be passed
2577 * OUT PARAMS:
2578 * API provides two set of txn currency, x_cost_txn_curr_code_tab - for Cost currency
2579 * x_rev_txn_curr_code_tab - for Revenue currency
2580 * Its calling APIs responsibiltiy to convert these currency into Txn currency
2581 */
2582 PROCEDURE Get_Resource_Rates
2583 ( p_calling_module IN VARCHAR2 := 'MSP'
2584 ,p_source_context IN VARCHAR2 := 'RLMI'
2585 ,p_project_id IN NUMBER
2586 ,p_budget_version_id IN NUMBER
2587 ,p_resource_assignment_id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2588 ,p_resource_list_member_Id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2589 ,p_ra_date_tab IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
2590 ,p_task_id_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2591 ,p_quantity_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2592 ,p_txn_currency_code_ovr_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
2593 ,p_cost_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2594 ,p_burden_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2595 ,p_bill_rate_override_tab IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
2596 ,x_resource_assignment_id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2597 ,x_resource_list_member_Id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2598 ,x_expenditure_ou_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2599 ,x_raw_cost_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2600 ,x_burden_cost_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2601 ,x_burden_multiplier_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2602 ,x_ind_compiled_set_id_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2603 ,x_bill_rate_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2604 ,x_markup_percent_tab OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
2605 ,x_txn_currency_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2606 ,x_cost_txn_curr_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2607 ,x_rev_txn_curr_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
2608 ,x_cost_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
2609 ,x_burden_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
2610 ,x_revenue_rejection_code_tab OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE
2611 ,x_return_status OUT NOCOPY VARCHAR2
2612 ,x_msg_data OUT NOCOPY VARCHAR2
2613 ,x_msg_count OUT NOCOPY NUMBER
2614 ) IS
2615
2616 l_plsql_index_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2617 l_resource_assignment_id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2618 l_resource_list_member_Id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2619 l_task_id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2620 l_quantity_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2621 l_txn_currency_code_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2622 l_txn_currency_code_ovr_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2623 l_curr_conv_reqd_flag_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2624 l_cost_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2625 l_burden_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2626 l_bill_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2627 l_ra_date_tab SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
2628 l_rowid_tab SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
2629 x_rw_cost_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2630 x_burden_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2631 x_bill_rate_override_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2632
2633 p_activity_code Varchar2(100) := 'CALCULATE';
2634 l_return_status VArchar2(1) := 'S';
2635 l_billable_flag Varchar2(1) := 'Y';
2636 l_calculate_mode Varchar2(100);
2637 l_txn_currency_code_override Varchar2(100);
2638 l_stage Varchar2(1000);
2639 l_task_id Number;
2640 RATEAPI_UNEXPECTED_ERRORS EXCEPTION;
2641 L_TXNCONVRATE_ERROR EXCEPTION;
2642 l_invalid_params EXCEPTION;
2643 l_plsql_Tab_Ct Number;
2644
2645 x_dummy_curr_code Varchar2(100);
2646 x_dummy_rate_date Date;
2647 x_dummy_rate_type Varchar2(100);
2648 x_dummy_exch_rate Number;
2649 x_dummy_cost Number;
2650 x_Final_Txn_raw_cost Number;
2651 x_Final_Txn_quantity Number;
2652 x_Final_txn_exch_rate Number;
2653 x_final_txn_rate_type Varchar2(100);
2654 x_final_txn_rate_date Date;
2655 l_Cntr Number := 0;
2656
2657 CURSOR CUR_projDetails IS
2658 SELECT to_number(null) res_class_bill_rate_sch_id
2659 ,to_number(null) res_class_raw_cost_sch_id
2660 ,'N' use_planning_rates_flag
2661 ,to_number(null) rev_job_rate_sch_id
2662 ,to_number(null) cost_job_rate_sch_id
2663 ,to_number(null) rev_emp_rate_sch_id
2664 ,to_number(null) cost_emp_rate_sch_id
2665 ,to_number(null) rev_non_labor_res_rate_sch_id
2666 ,to_number(null) cost_non_labor_res_rate_sch_id
2667 ,to_number(null) cost_burden_rate_sch_id
2668 ,'Y' track_workplan_costs_flag
2669 ,'COST' fp_budget_version_type
2670 ,to_number(null) resource_list_id
2671 ,'N' approved_rev_plan_type_flag
2672 ,'N' plan_in_multi_curr_flag
2673 ,to_date(null) etc_start_date
2674 ,'N' wp_version_flag
2675 ,pp.assign_precedes_task
2676 ,pp.bill_job_group_id
2677 ,pp.carrying_out_organization_id
2678 ,nvl(pp.multi_currency_billing_flag,'N') multi_currency_billing_flag
2679 ,pp.org_id
2680 ,pp.non_labor_bill_rate_org_id
2681 ,pp.project_currency_code
2682 ,pp.non_labor_schedule_discount
2683 ,pp.non_labor_schedule_fixed_date
2684 ,pp.non_lab_std_bill_rt_sch_id
2685 ,pp.project_type
2686 ,pp.projfunc_currency_code
2687 ,pp.emp_bill_rate_schedule_id
2688 ,pp.job_bill_rate_schedule_id
2689 ,pp.labor_bill_rate_org_id
2690 ,pp.labor_sch_type
2691 ,pp.non_labor_sch_type
2692 ,to_number(null) project_structure_version_id
2693 ,pp.project_id
2694 ,pp.segment1 project_name
2695 FROM pa_projects_all pp
2696 WHERE pp.project_id = p_project_id;
2697
2698 CURSOR CUR_VersionDts IS
2699 SELECT decode(nvl(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_bill_rate_sch_id,
2700 decode(bv.version_type,'REVENUE',pfo.rev_res_class_rate_sch_id,
2701 'ALL' ,pfo.rev_res_class_rate_sch_id,
2702 NULL)) res_class_bill_rate_sch_id
2703 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_raw_cost_sch_id,
2704 decode(bv.version_type,'COST',pfo.cost_res_class_rate_sch_id,
2705 'ALL' ,pfo.cost_res_class_rate_sch_id,
2706 NULL)) res_class_raw_cost_sch_id
2707 ,nvl(pfo.use_planning_rates_flag,'N') use_planning_rates_flag
2708 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2709 decode(bv.version_type,'REVENUE',pfo.rev_job_rate_sch_id,
2710 'ALL' ,pfo.rev_job_rate_sch_id,
2711 NULL)) rev_job_rate_sch_id
2712 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2713 decode(bv.version_type,'COST' ,pfo.cost_job_rate_sch_id,
2714 'ALL' ,pfo.cost_job_rate_sch_id,
2715 NULL)) cost_job_rate_sch_id
2716 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2717 decode(bv.version_type,'REVENUE',pfo.rev_emp_rate_sch_id,
2718 'ALL' ,pfo.rev_emp_rate_sch_id,
2719 NULL)) rev_emp_rate_sch_id
2720 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2721 decode(bv.version_type,'COST' ,pfo.cost_emp_rate_sch_id,
2722 'ALL' ,pfo.cost_emp_rate_sch_id,
2723 NULL)) cost_emp_rate_sch_id
2724 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2725 decode(bv.version_type,'REVENUE',pfo.rev_non_labor_res_rate_sch_id,
2726 'ALL' ,pfo.rev_non_labor_res_rate_sch_id,
2727 NULL)) rev_non_labor_res_rate_sch_id
2728 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2729 decode(bv.version_type,'COST' ,pfo.cost_non_labor_res_rate_sch_id,
2730 'ALL' ,pfo.cost_non_labor_res_rate_sch_id,
2731 NULL)) cost_non_labor_res_rate_sch_id
2732 ,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
2733 decode(bv.version_type,'COST' ,pfo.cost_burden_rate_sch_id,
2734 'ALL' ,pfo.cost_burden_rate_sch_id,
2735 NULL)) cost_burden_rate_sch_id
2736 ,decode(nvl(bv.wp_version_flag,'N'),'Y',NVL(pfo.track_workplan_costs_flag,'N'),'Y') track_workplan_costs_flag
2737 ,bv.version_type fp_budget_version_type
2738 ,bv.resource_list_id
2739 ,nvl(bv.approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
2740 ,nvl(pfo.plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
2741 ,bv.etc_start_date
2742 ,nvl(bv.wp_version_flag,'N') wp_version_flag
2743 ,pp.assign_precedes_task
2744 ,pp.bill_job_group_id
2745 ,pp.carrying_out_organization_id
2746 ,nvl(pp.multi_currency_billing_flag,'N') multi_currency_billing_flag
2747 ,pp.org_id
2748 ,pp.non_labor_bill_rate_org_id
2749 ,pp.project_currency_code
2750 ,pp.non_labor_schedule_discount
2751 ,pp.non_labor_schedule_fixed_date
2752 ,pp.non_lab_std_bill_rt_sch_id
2753 ,pp.project_type
2754 ,pp.projfunc_currency_code
2755 ,pp.emp_bill_rate_schedule_id
2756 ,pp.job_bill_rate_schedule_id
2757 ,pp.labor_bill_rate_org_id
2758 ,pp.labor_sch_type
2759 ,pp.non_labor_sch_type
2760 ,bv.project_structure_version_id
2761 ,bv.project_id
2762 ,pp.segment1 project_name
2763 FROM pa_proj_fp_options pfo
2764 ,pa_budget_versions bv
2765 ,pa_projects_all pp
2766 WHERE pfo.fin_plan_version_id = bv.budget_version_id
2767 AND bv.budget_version_id = p_budget_version_id
2768 AND pp.project_id = bv.project_id
2769 AND pfo.project_id = pp.project_id;
2770 rate_rec CUR_VersionDts%ROWtype;
2771
2772
2773 CURSOR CUR_RateApi_Attrbs IS
2774 SELECT tmp.rowid
2775 ,tmp.rbs_element_id
2776 ,tmp.resource_assignment_id
2777 ,tmp.txn_currency_code
2778 ,NVL(tmp.txn_plan_quantity,1) quantity
2779 ,tmp.line_start_date start_date
2780 ,tmp.burden_cost_rate_override
2781 ,tmp.rw_cost_rate_override
2782 ,tmp.bill_rate_override
2783 ,tmp.txn_currency_code_override
2784 ,tmp.task_id
2785 ,tmp.resource_list_member_id
2786 ,tmp.unit_of_measure
2787 ,tmp.resource_class_code
2788 ,tmp.organization_id
2789 ,tmp.job_id
2790 ,tmp.person_id
2791 ,tmp.expenditure_type
2792 ,tmp.expenditure_category
2793 ,tmp.revenue_category_code
2794 ,tmp.event_type
2795 ,tmp.supplier_id
2796 ,tmp.non_labor_resource
2797 ,tmp.bom_resource_id
2798 ,tmp.inventory_item_id
2799 ,tmp.item_category_id
2800 ,tmp.billable_percent
2801 ,tmp.mfc_cost_type_id
2802 ,tmp.incurred_by_res_flag
2803 ,tmp.rate_job_id
2804 ,tmp.rate_expenditure_type
2805 ,tmp.sp_fixed_date
2806 ,tmp.person_type_code
2807 ,NVL(tmp.rate_based_flag,'N') rate_based_flag
2808 ,tmp.rate_exp_func_curr_code
2809 ,tmp.rate_expenditure_org_id
2810 ,tmp.incur_by_res_class_code
2811 ,tmp.project_role_id
2812 ,tmp.resource_class_flag
2813 ,to_number(null) res_format_id --tmp.res_format_id
2814 ,tmp.task_bill_rate_org_id non_labor_bill_rate_org_id
2815 ,tmp.task_sch_discount non_labor_schedule_discount
2816 ,tmp.task_sch_date non_labor_schedule_fixed_date
2817 ,tmp.task_std_bill_rate_sch non_lab_std_bill_rt_sch_id
2818 ,tmp.emp_bill_rate_schedule_id
2819 ,tmp.job_bill_rate_schedule_id
2820 ,tmp.labor_bill_rate_org_id
2821 ,tmp.labor_sch_type
2822 ,tmp.non_labor_sch_type
2823 ,tmp.top_task_id
2824 ,NVL(tmp.billable_flag,'N') billable_flag
2825 ,to_number(null) budget_line_id
2826 ,tmp.task_name
2827 ,tmp.resource_name
2828 FROM pa_fp_res_assignments_tmp tmp
2829 WHERE tmp.budget_version_id = NVL(p_budget_version_id,-9999)
2830 ORDER BY tmp.rbs_element_id ; /* added this to ensure that In and Out plsql table indexes are mapped */
2831
2832 l_txn_currency_code Varchar2(100);
2833 l_txn_plan_quantity Number;
2834 l_budget_lines_start_date Date;
2835 l_budget_line_id Number;
2836 l_burden_cost_rate_override Number;
2837 l_rw_cost_rate_override Number;
2838 l_bill_rate_override Number;
2839 l_txn_raw_cost Number;
2840 l_txn_burdened_cost Number;
2841 l_txn_revenue Number;
2842 x_bill_rate Number;
2843 x_cost_rate Number;
2844 x_burden_cost_rate Number;
2845 x_raw_cost Number;
2846 x_burden_cost Number;
2847 x_raw_revenue Number;
2848 x_bill_markup_percentage Number;
2849 l_bill_markup_percentage Number;
2850 x_cost_txn_curr_code Varchar2(100);
2851 x_rev_txn_curr_code Varchar2(100);
2852 x_raw_cost_rejection_code Varchar2(100);
2853 x_burden_cost_rejection_code Varchar2(100);
2854 x_revenue_rejection_code Varchar2(100);
2855 x_cost_ind_compiled_set_id Number;
2856 x_projfunc_rejection_code Varchar2(100);
2857 x_project_rejection_code Varchar2(100);
2858 X_BURDEN_MULTIPLIER Number;
2859 l_cost_rate_multiplier CONSTANT pa_labor_cost_multipliers.multiplier%TYPE := 1;
2860 l_bill_rate_multiplier CONSTANT pa_labor_cost_multipliers.multiplier%TYPE := 1;
2861 l_cost_sch_type VARCHAR2(30) := 'COST';
2862 l_mfc_cost_source CONSTANT NUMBER := 2;
2863 x_stage varchar2(1000);
2864 l_status varchar2(100);
2865
2866 l_labor_sch_type pa_projects_all.labor_sch_type%TYPE;
2867 l_non_labor_sch_type pa_projects_all.labor_sch_type%TYPE;
2868
2869 /* Added these variables for bug fix: 3681314,3828998 */
2870 l_override_organization_id Number;
2871 l_debug_flag Varchar2(1) := 'N';
2872 l_proc_name VARCHAR2(100) := 'GET_RESOURCE_RATES';
2873
2874 BEGIN
2875
2876 l_return_status := 'S';
2877 x_return_status := 'S';
2878 fnd_profile.get('PA_DEBUG_MODE',l_debug_flag);
2879 l_debug_flag := NVL(l_debug_flag, 'N');
2880
2881 l_stage := '10:Entered Get_Res_Rates API:ProjId['||p_project_id||']BdgtVer['||p_budget_version_id||']CallingModule['||p_calling_module||']';
2882 l_stage := l_stage||' SourceContext['||p_source_context||']';
2883 print_msg(l_debug_flag,l_proc_name,l_stage);
2884
2885 /* Initialize tmp tables */
2886 DELETE FROM PA_FP_RES_ASSIGNMENTS_TMP;
2887 DELETE FROM PA_FP_ROLLUP_TMP;
2888
2889 /* Validate Input Params */
2890 l_resource_assignment_id_tab := p_resource_assignment_id_tab;
2891 l_resource_list_member_Id_tab := p_resource_list_member_Id_tab;
2892 l_task_id_tab := p_task_id_tab;
2893 l_quantity_tab := p_quantity_tab;
2894 --l_txn_currency_code_tab := p_txn_currency_code_tab;
2895 l_txn_currency_code_ovr_tab := p_txn_currency_code_ovr_tab;
2896 --l_curr_conv_reqd_flag_tab := p_curr_conv_reqd_flag_tab;
2897 l_cost_rate_override_tab := p_cost_rate_override_tab;
2898 l_burden_rate_override_tab := p_burden_rate_override_tab;
2899 l_bill_rate_override_tab := p_bill_rate_override_tab;
2900 l_ra_date_tab := p_ra_date_tab;
2901
2902 IF NVL(p_project_id,0) = 0 AND NVL(p_budget_version_id,0) = 0 Then
2903 l_stage := '11: Project and Budget Version is NULL';
2904 print_msg(l_debug_flag,l_proc_name,l_stage);
2905 l_return_status := 'E';
2906 END If;
2907
2908 IF ((p_source_context = 'RA' AND l_resource_assignment_id_tab.COUNT = 0 ) OR
2909 (p_source_context = 'RLMI' AND l_resource_list_member_Id_tab.COUNT = 0)) Then
2910 l_stage := '12: RA and RLMI is NULL';
2911 print_msg(l_debug_flag,l_proc_name,l_stage);
2912 l_return_status := 'E';
2913 END IF;
2914
2915 /* Now Extend the passed in the plsql tables if param is not passed. This has to be done to avoid no data found error*/
2916 IF (p_source_context = 'RA' AND l_resource_assignment_id_tab.COUNT > 0 ) Then
2917 l_plsql_Tab_Ct := l_resource_assignment_id_tab.COUNT;
2918 ElsIf (p_source_context = 'RLMI' AND l_resource_list_member_Id_tab.COUNT > 0) Then
2919 l_plsql_Tab_Ct := l_resource_list_member_Id_tab.COUNT;
2920 End If;
2921
2922 FOR i IN 1 .. l_plsql_Tab_Ct LOOP
2923 If NOT l_plsql_index_tab.exists(i) Then
2924 l_plsql_index_tab.extend;
2925 l_plsql_index_tab(i) := i;
2926 End If;
2927 If NOT l_quantity_tab.exists(i) Then
2928 l_quantity_tab.extend;
2929 l_quantity_tab(i) := NULL;
2930 End If;
2931
2932 If NOT l_txn_currency_code_tab.exists(i) Then
2933 l_txn_currency_code_tab.extend;
2934 l_txn_currency_code_tab(i) := NULL;
2935 End If;
2936
2937 If NOT l_txn_currency_code_ovr_tab.exists(i) Then
2938 l_txn_currency_code_ovr_tab.extend;
2939 l_txn_currency_code_ovr_tab(i) := NULL;
2940 End If;
2941 If NOT l_curr_conv_reqd_flag_tab.exists(i) Then
2942 l_curr_conv_reqd_flag_tab.extend;
2943 l_curr_conv_reqd_flag_tab(i) := 'N';
2944 End If;
2945 If NOT l_cost_rate_override_tab.exists(i) Then
2946 l_cost_rate_override_tab.extend;
2947 l_cost_rate_override_tab(i) := NULL;
2948 End If;
2949 If NOT l_burden_rate_override_tab.exists(i) Then
2950 l_burden_rate_override_tab.extend;
2951 l_burden_rate_override_tab(i) := NULL;
2952 End If;
2953 If NOT l_bill_rate_override_tab.exists(i) Then
2954 l_bill_rate_override_tab.extend;
2955 l_bill_rate_override_tab(i) := NULL;
2956 End If;
2957
2958 If NOT l_task_id_tab.exists(i) Then
2959 l_task_id_tab.extend;
2960 l_task_id_tab(i) := NULL;
2961 End If;
2962
2963 IF NOT l_ra_date_tab.exists(i) Then
2964 l_ra_date_tab.extend;
2965 l_ra_date_tab(i) := NULL;
2966 End If;
2967 IF l_txn_currency_code_ovr_tab(i) IS NULL AND
2968 (l_cost_rate_override_tab(i) is NOT NULL OR
2969 l_burden_rate_override_tab(i) is NOT NULL OR
2970 l_bill_rate_override_tab(i) is NOT NULL ) THEN
2971 l_return_status := 'E';
2972 END If;
2973 END LOOP;
2974
2975 IF l_return_status <> 'S' Then
2976 Raise l_invalid_params;
2977 End If;
2978
2979
2980 rate_rec := NULL;
2981 IF p_budget_version_id is NOT NULL Then
2982 OPEN CUR_VersionDts;
2983 FETCH CUR_VersionDts INTO rate_rec;
2984 CLOSE CUR_VersionDts;
2985 Else
2986 OPEN CUR_projDetails;
2987 FETCH CUR_projDetails INTO rate_rec;
2988 CLOSE CUR_projDetails;
2989 End If;
2990
2991
2992 IF l_return_status = 'S' Then
2993 If (p_source_context = 'RA' AND l_resource_assignment_id_tab.COUNT > 0 ) Then
2994 l_stage := '13: Calling populate_ra_details API';
2995 print_msg(l_debug_flag,l_proc_name,l_stage);
2996 populate_ra_details
2997 ( p_calling_module => p_calling_module
2998 ,p_source_context => p_source_context
2999 ,p_project_id => p_project_id
3000 ,p_project_type => rate_rec.project_type
3001 ,p_budget_version_id => p_budget_version_id
3002 ,p_resource_assignment_id_tab => l_resource_assignment_id_tab
3003 ,p_plsql_index_tab => l_plsql_index_tab
3004 ,p_task_id_tab => l_task_id_tab
3005 ,p_ra_date_tab => l_ra_date_tab
3006 ,p_quantity_tab => l_quantity_tab
3007 ,p_txn_currency_code_tab => l_txn_currency_code_tab
3008 ,p_txn_currency_code_ovr_tab => l_txn_currency_code_ovr_tab
3009 ,p_cost_rate_override_tab => l_cost_rate_override_tab
3010 ,p_burden_rate_override_tab => l_burden_rate_override_tab
3011 ,p_bill_rate_override_tab => l_bill_rate_override_tab
3012 ,x_return_status => l_return_status
3013 );
3014 ELSIF (p_source_context = 'RLMI' AND l_resource_list_member_Id_tab.COUNT > 0) THEN
3015 -- Call resource defaults to get the resource attributes.
3016 l_stage := '14: Calling populate_res_details';
3017 print_msg(l_debug_flag,l_proc_name,l_stage);
3018 populate_res_details
3019 ( p_calling_module => p_calling_module
3020 ,p_source_context => p_source_context
3021 ,p_project_id => p_project_id
3022 ,p_project_type => rate_rec.project_type
3023 ,p_budget_version_id => p_budget_version_id
3024 ,p_resource_list_member_Id_tab => l_resource_list_member_Id_tab
3025 ,p_plsql_index_tab => l_plsql_index_tab
3026 ,p_task_id_tab => l_task_id_tab
3027 ,p_ra_date_tab => l_ra_date_tab
3028 ,p_quantity_tab => l_quantity_tab
3029 ,p_txn_currency_code_tab => l_txn_currency_code_tab
3030 ,p_txn_currency_code_ovr_tab => l_txn_currency_code_ovr_tab
3031 ,p_cost_rate_override_tab => l_cost_rate_override_tab
3032 ,p_burden_rate_override_tab => l_burden_rate_override_tab
3033 ,p_bill_rate_override_tab => l_bill_rate_override_tab
3034 ,x_return_status => l_return_status
3035 );
3036 End If;
3037
3038
3039 END If;
3040
3041 IF l_return_status = 'S' Then
3042 l_stage := '15: Update tmp table with task level details';
3043 print_msg(l_debug_flag,l_proc_name,l_stage);
3044 /* update the task details */
3045 UPDATE pa_fp_res_assignments_tmp tmp
3046 SET (tmp.task_bill_rate_org_id ---non_labor_bill_rate_org_id
3047 ,tmp.task_sch_discount ---non_labor_schedule_discount
3048 ,tmp.task_sch_date ---non_labor_schedule_fixed_date
3049 ,tmp.task_std_bill_rate_sch ---non_lab_std_bill_rt_sch_id
3050 ,tmp.emp_bill_rate_schedule_id
3051 ,tmp.job_bill_rate_schedule_id
3052 ,tmp.labor_bill_rate_org_id
3053 ,tmp.labor_sch_type
3054 ,tmp.non_labor_sch_type
3055 ,tmp.top_task_id
3056 ,tmp.billable_flag
3057 ,tmp.task_name ) =
3058 (SELECT t.non_labor_bill_rate_org_id
3059 ,t.non_labor_schedule_discount
3060 ,t.non_labor_schedule_fixed_date
3061 ,t.non_lab_std_bill_rt_sch_id
3062 ,t.emp_bill_rate_schedule_id
3063 ,t.job_bill_rate_schedule_id
3064 ,t.labor_bill_rate_org_id
3065 ,t.labor_sch_type
3066 ,t.non_labor_sch_type
3067 ,t.top_task_id
3068 ,NVL(t.billable_flag,'Y')
3069 ,t.task_name
3070 FROM pa_tasks t
3071 WHERE t.task_id = tmp.task_id
3072 AND t.project_id = p_project_id
3073 )
3074 WHERE tmp.budget_version_id = p_budget_version_id
3075 AND (tmp.task_id is NOT NULL OR tmp.task_id <> 0 )
3076 AND EXISTS (select null
3077 from pa_tasks t1
3078 Where t1.task_id = tmp.task_id
3079 and t1.project_id = p_project_id
3080 );
3081 END If;
3082
3083 /* for each resource assignment in calctmp open the task cursor */
3084 IF rate_rec.fp_budget_version_type = 'REVENUE' THEN
3085 l_calculate_mode := 'REVENUE';
3086 ELSIF rate_rec.fp_budget_version_type = 'COST' THEN
3087 l_calculate_mode := 'COST';
3088 ELSIF rate_rec.fp_budget_version_type = 'ALL' THEN
3089 l_calculate_mode := 'COST_REVENUE';
3090 END IF;
3091
3092 x_resource_assignment_id_tab := SYSTEM.PA_NUM_TBL_TYPE();
3093 x_resource_list_member_Id_tab := SYSTEM.PA_NUM_TBL_TYPE();
3094 x_raw_cost_rate_tab := SYSTEM.PA_NUM_TBL_TYPE();
3095 x_burden_cost_rate_tab := SYSTEM.PA_NUM_TBL_TYPE();
3096 x_burden_multiplier_tab := SYSTEM.PA_NUM_TBL_TYPE();
3097 x_ind_compiled_set_id_tab := SYSTEM.PA_NUM_TBL_TYPE();
3098 x_bill_rate_tab := SYSTEM.PA_NUM_TBL_TYPE();
3099 x_markup_percent_tab := SYSTEM.PA_NUM_TBL_TYPE();
3100 x_txn_currency_code_tab := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3101 x_cost_txn_curr_code_tab := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3102 x_rev_txn_curr_code_tab := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3103 x_cost_rejection_code_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3104 x_burden_rejection_code_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3105 x_revenue_rejection_code_tab := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3106 x_expenditure_ou_tab := SYSTEM.PA_NUM_TBL_TYPE();
3107 /* loop through the tmp table and call rate api for each line */
3108 l_Cntr := 0;
3109 FOR z IN CUR_RateApi_Attrbs LOOP --{
3110 /* Initialize the out params */
3111 l_Cntr := l_Cntr +1;
3112
3113 /* initialize the out params */
3114 l_rowid_tab.extend;
3115 x_resource_assignment_id_tab.extend;
3116 x_resource_list_member_Id_tab.extend;
3117 x_raw_cost_rate_tab.extend;
3118 x_burden_cost_rate_tab.extend;
3119 x_burden_multiplier_tab.extend;
3120 x_ind_compiled_set_id_tab.extend;
3121 x_bill_rate_tab.extend;
3122 x_markup_percent_tab.extend;
3123 x_txn_currency_code_tab.extend;
3124 x_cost_txn_curr_code_tab.extend;
3125 x_rev_txn_curr_code_tab.extend;
3126 x_cost_rejection_code_tab.extend;
3127 x_burden_rejection_code_tab.extend;
3128 x_revenue_rejection_code_tab.extend;
3129 x_rw_cost_rate_override_tab.extend;
3130 x_burden_rate_override_tab.extend;
3131 x_bill_rate_override_tab.extend;
3132 x_expenditure_ou_tab.extend;
3133
3134 l_rowid_tab(l_Cntr) := z.rowid;
3135 x_resource_assignment_id_tab(l_Cntr) := z.resource_assignment_id;
3136 x_resource_list_member_Id_tab(l_Cntr) := z.resource_list_member_Id;
3137 x_expenditure_ou_tab(l_Cntr) := nvl(z.rate_expenditure_org_id,rate_rec.org_id);
3138 x_raw_cost_rate_tab(l_Cntr) := null;
3139 x_burden_cost_rate_tab(l_Cntr) := null;
3140 x_burden_multiplier_tab(l_Cntr) := null;
3141 x_ind_compiled_set_id_tab(l_Cntr) := null;
3142 x_bill_rate_tab(l_Cntr) := null;
3143 x_markup_percent_tab(l_Cntr) := null;
3144 x_txn_currency_code_tab(l_Cntr) := null;
3145 x_cost_txn_curr_code_tab(l_Cntr) := null;
3146 x_rev_txn_curr_code_tab(l_Cntr) := null;
3147 x_cost_rejection_code_tab(l_Cntr) := null;
3148 x_burden_rejection_code_tab(l_Cntr) := null;
3149 x_revenue_rejection_code_tab(l_Cntr) := null;
3150 x_rw_cost_rate_override_tab(l_Cntr) := null;
3151 x_burden_rate_override_tab(l_Cntr) := null;
3152 x_bill_rate_override_tab(l_Cntr) := null;
3153
3154 /* setting the quantity to null to avoid call to rate api with 0 qty */
3155 l_txn_plan_quantity := z.quantity;
3156 l_rw_cost_rate_override := z.rw_cost_rate_override;
3157 l_burden_cost_rate_override := z.burden_cost_rate_override;
3158 l_bill_rate_override := z.bill_rate_override;
3159 l_txn_currency_code_override := z.txn_currency_code_override;
3160 l_txn_raw_cost := NULL;
3161 l_txn_burdened_cost := NULL;
3162 l_txn_revenue := NULL;
3163 x_raw_cost := NULL;
3164 x_burden_cost := NULL;
3165 x_raw_revenue := NULL;
3166 x_bill_rate := NULL;
3167 x_cost_rate := NULL;
3168 x_burden_cost_rate := NULL;
3169 x_burden_multiplier := NULL;
3170 x_bill_markup_percentage := NULL;
3171 x_cost_txn_curr_code := NULL;
3172 x_rev_txn_curr_code := NULL;
3173 x_raw_cost_rejection_code := NULL;
3174 x_burden_cost_rejection_code := NULL;
3175 x_revenue_rejection_code := NULL;
3176 x_cost_ind_compiled_set_id := NULL;
3177
3178 IF NVL(z.rate_based_flag,'N') = 'N' Then
3179 IF rate_rec.fp_budget_version_type in ('COST','ALL') Then
3180 l_txn_currency_code_override := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3181 x_cost_txn_curr_code := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3182 x_cost_rate := 1;
3183 l_rw_cost_rate_override := 1;
3184 Else
3185 l_txn_currency_code_override := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3186 x_rev_txn_curr_code := NVL(l_txn_currency_code_override,rate_rec.project_currency_code);
3187 x_bill_rate := 1;
3188 l_bill_rate_override := 1;
3189 End IF;
3190 End If;
3191 l_override_organization_id := NULL;
3192 IF l_override_organization_id is NULL Then
3193 l_stage := 'Calling Override_exp_organization ';
3194 pa_cost.Override_exp_organization
3195 (P_item_date => z.start_date
3196 ,P_person_id => z.person_id
3197 ,P_project_id => p_project_id
3198 ,P_incurred_by_organz_id => z.organization_id
3199 ,P_Expenditure_type => nvl(z.expenditure_type,z.rate_expenditure_type)
3200 ,X_overr_to_organization_id => l_override_organization_id
3201 ,X_return_status => l_return_status
3202 ,X_msg_count => x_msg_count
3203 ,X_msg_data => x_msg_data
3204 );
3205 l_stage := 'End of Override_exp_organization retSts['||l_return_status||']';
3206 End If;
3207 BEGIN
3208 l_task_id := z.task_id;
3209 If l_task_id = 0 Then
3210 l_task_id := NULL;
3211 End If;
3212
3213 /* Bug fix:4133047 pass the Task level or project level labor and non-labor sch types to bill rate api in order to
3214 * derive the markup based on burden schedule or bill rate schedule
3215 */
3216 If l_task_id IS NOT NULL THEN
3217 l_labor_sch_type:= z.labor_sch_type;
3218 l_non_labor_sch_type := z.non_labor_sch_type;
3219 Else
3220 l_labor_sch_type:= rate_rec.labor_sch_type;
3221 l_non_labor_sch_type := rate_rec.non_labor_sch_type;
3222 End If;
3223
3224 l_stage := 'Calling get_planning_rates API for lineId ['||z.rbs_element_id||']';
3225 print_msg(l_debug_flag,l_proc_name,' **REQUIRED** = MUST BE PASSED TO RATE API');
3226 print_msg(l_debug_flag,l_proc_name,' p_project_id => '||p_project_id);
3227 print_msg(l_debug_flag,l_proc_name,' p_task_id => '||l_task_id);
3228 print_msg(l_debug_flag,l_proc_name,' p_top_task_id => '||z.top_task_id);
3229 print_msg(l_debug_flag,l_proc_name,' p_person_id => '||z.person_id);
3230 print_msg(l_debug_flag,l_proc_name,' p_job_id => '||z.job_id);
3231 print_msg(l_debug_flag,l_proc_name,' p_bill_job_grp_id => '||rate_rec.bill_job_group_id);
3232 print_msg(l_debug_flag,l_proc_name,' p_project_organz_id => '||rate_rec.carrying_out_organization_id);
3233 print_msg(l_debug_flag,l_proc_name,' p_rev_res_class_rate_sch_id => '||rate_rec.res_class_bill_rate_sch_id);
3234 print_msg(l_debug_flag,l_proc_name,' p_cost_res_class_rate_sch_id=> '||rate_rec.res_class_raw_cost_sch_id);
3235 print_msg(l_debug_flag,l_proc_name,' p_rev_task_nl_rate_sch_id => '||z.non_lab_std_bill_rt_sch_id);
3236 print_msg(l_debug_flag,l_proc_name,' p_rev_proj_nl_rate_sch_id => '||rate_rec.non_lab_std_bill_rt_sch_id);
3237 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));
3238 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));
3239 print_msg(l_debug_flag,l_proc_name,' p_plan_rev_job_rate_sch_id => '||rate_rec.rev_job_rate_sch_id);
3240 print_msg(l_debug_flag,l_proc_name,' p_plan_cost_job_rate_sch_id => '||rate_rec.cost_job_rate_sch_id);
3241 print_msg(l_debug_flag,l_proc_name,' p_plan_rev_emp_rate_sch_id => '||rate_rec.rev_emp_rate_sch_id);
3242 print_msg(l_debug_flag,l_proc_name,' p_plan_cost_emp_rate_sch_id => '||rate_rec.cost_emp_rate_sch_id);
3243 print_msg(l_debug_flag,l_proc_name,' p_plan_rev_nlr_rate_sch_id => '||rate_rec.rev_non_labor_res_rate_sch_id);
3244 print_msg(l_debug_flag,l_proc_name,' p_plan_cost_nlr_rate_sch_id => '||rate_rec.cost_non_labor_res_rate_sch_id);
3245 print_msg(l_debug_flag,l_proc_name,' p_plan_burden_cost_sch_id => '||rate_rec.cost_burden_rate_sch_id);
3246 print_msg(l_debug_flag,l_proc_name,' p_calculate_mode => '||l_calculate_mode);
3247 print_msg(l_debug_flag,l_proc_name,' p_mcb_flag => '||rate_rec.multi_currency_billing_flag);
3248 print_msg(l_debug_flag,l_proc_name,' p_cost_rate_multiplier => '||l_cost_rate_multiplier);
3249 print_msg(l_debug_flag,l_proc_name,' p_bill_rate_multiplier => '||l_bill_rate_multiplier);
3250 print_msg(l_debug_flag,l_proc_name,' p_cost_sch_type => '||l_cost_sch_type);
3251 print_msg(l_debug_flag,l_proc_name,' p_labor_sch_type => '||rate_rec.labor_sch_type);
3252 print_msg(l_debug_flag,l_proc_name,' p_non_labor_sch_type => '||rate_rec.non_labor_sch_type);
3253 print_msg(l_debug_flag,l_proc_name,' p_labor_schdl_discnt => '||NULL);
3254 print_msg(l_debug_flag,l_proc_name,' p_labor_bill_rate_org_id => '||rate_rec.labor_bill_rate_org_id);
3255 print_msg(l_debug_flag,l_proc_name,' p_labor_std_bill_rate_schdl => '||NULL);
3256 print_msg(l_debug_flag,l_proc_name,' p_labor_schdl_fixed_date => '||NULL);
3257 print_msg(l_debug_flag,l_proc_name,' p_project_org_id => '||rate_rec.org_id);
3258 print_msg(l_debug_flag,l_proc_name,' p_project_type => '||rate_rec.project_type);
3259 print_msg(l_debug_flag,l_proc_name,' p_expenditure_type => '||nvl(z.expenditure_type,z.rate_expenditure_type));
3260 print_msg(l_debug_flag,l_proc_name,' p_non_labor_resource => '||z.non_labor_resource);
3261 print_msg(l_debug_flag,l_proc_name,' p_incurred_by_organz_id => '||z.organization_id);
3262 print_msg(l_debug_flag,l_proc_name,' p_override_to_organz_id => '||l_override_organization_id);
3263 print_msg(l_debug_flag,l_proc_name,' p_expenditure_org_id => '||nvl(z.rate_expenditure_org_id,rate_rec.org_id));
3264 print_msg(l_debug_flag,l_proc_name,' p_assignment_precedes_task => '||rate_rec.assign_precedes_task);
3265 print_msg(l_debug_flag,l_proc_name,' p_planning_transaction_id => '||z.budget_line_id);
3266 print_msg(l_debug_flag,l_proc_name,' p_task_bill_rate_org_id => '||z.non_labor_bill_rate_org_id);
3267 print_msg(l_debug_flag,l_proc_name,' p_project_bill_rate_org_id => '||rate_rec.non_labor_bill_rate_org_id);
3268 print_msg(l_debug_flag,l_proc_name,' p_nlr_organization_id => '||z.organization_id);
3269 print_msg(l_debug_flag,l_proc_name,' p_project_sch_date => '||rate_rec.non_labor_schedule_fixed_date);
3270 print_msg(l_debug_flag,l_proc_name,' p_task_sch_date => '||z.non_labor_schedule_fixed_date);
3271 print_msg(l_debug_flag,l_proc_name,' p_project_sch_discount => '||rate_rec.non_labor_schedule_discount);
3272 print_msg(l_debug_flag,l_proc_name,' p_task_sch_discount => '||z.non_labor_schedule_discount);
3273 print_msg(l_debug_flag,l_proc_name,' p_inventory_item_id => '||z.inventory_item_id);
3274 print_msg(l_debug_flag,l_proc_name,' p_BOM_resource_Id => '||z.bom_resource_id);
3275 print_msg(l_debug_flag,l_proc_name,' P_mfc_cost_type_id => '||z.mfc_cost_type_id);
3276 print_msg(l_debug_flag,l_proc_name,' P_item_category_id => '||z.item_category_id);
3277 print_msg(l_debug_flag,l_proc_name,' p_mfc_cost_source => '||l_mfc_cost_source);
3278 print_msg(l_debug_flag,l_proc_name,' ** p_assignment_id => '||z.resource_assignment_id);
3279 print_msg(l_debug_flag,l_proc_name,' ** p_rlmi_id => '||z.resource_list_member_id);
3280 print_msg(l_debug_flag,l_proc_name,' ** p_resource_class => '||z.resource_class_code);
3281 print_msg(l_debug_flag,l_proc_name,' ** p_planning_resource_format => '||z.res_format_id);
3282 print_msg(l_debug_flag,l_proc_name,' ** p_use_planning_rates_flag => '||rate_rec.use_planning_rates_flag);
3283 print_msg(l_debug_flag,l_proc_name,' ** p_rate_based_flag => '||z.rate_based_flag);
3284 print_msg(l_debug_flag,l_proc_name,' ** p_uom => '||z.unit_of_measure);
3285 print_msg(l_debug_flag,l_proc_name,' ** p_quantity => '||l_txn_plan_quantity);
3286 print_msg(l_debug_flag,l_proc_name,' ** p_item_date => '||z.start_date);
3287 print_msg(l_debug_flag,l_proc_name,' ** p_cost_override_rate => '||l_rw_cost_rate_override);
3288 print_msg(l_debug_flag,l_proc_name,' ** p_revenue_override_rate => '||l_bill_rate_override);
3289 print_msg(l_debug_flag,l_proc_name,' ** p_override_burden_cost_rate => '||l_burden_cost_rate_override);
3290 print_msg(l_debug_flag,l_proc_name,' ** p_override_currency_code => '||l_txn_currency_code_override);
3291 print_msg(l_debug_flag,l_proc_name,' ** p_txn_currency_code => '||l_txn_currency_code);
3292 print_msg(l_debug_flag,l_proc_name,' ** p_raw_cost => '||l_txn_raw_cost);
3293 print_msg(l_debug_flag,l_proc_name,' ** p_burden_cost => '||l_txn_burdened_cost);
3294 print_msg(l_debug_flag,l_proc_name,' ** p_raw_revenue => '||l_txn_revenue);
3295 print_msg(l_debug_flag,l_proc_name,' ** p_billable_flag => '||l_billable_flag);
3296 pa_plan_revenue.Get_planning_Rates
3297 (
3298 p_project_id => p_project_id
3299 ,p_task_id => l_task_id
3300 ,p_top_task_id => z.top_task_id
3301 ,p_person_id => z.person_id
3302 ,p_job_id => z.job_id
3303 ,p_bill_job_grp_id => rate_rec.bill_job_group_id
3304 ,p_resource_class => z.resource_class_code
3305 ,p_planning_resource_format => z.res_format_id
3306 ,p_use_planning_rates_flag => NVL(rate_rec.use_planning_rates_flag,'N')
3307 ,p_rate_based_flag => NVL(z.rate_based_flag,'N')
3308 ,p_uom => z.unit_of_measure
3309 ,p_system_linkage => NULL
3310 ,p_project_organz_id => rate_rec.carrying_out_organization_id
3311 ,p_rev_res_class_rate_sch_id => rate_rec.res_class_bill_rate_sch_id
3312 ,p_cost_res_class_rate_sch_id => rate_rec.res_class_raw_cost_sch_id
3313 ,p_rev_task_nl_rate_sch_id => z.non_lab_std_bill_rt_sch_id
3314 ,p_rev_proj_nl_rate_sch_id => rate_rec.non_lab_std_bill_rt_sch_id
3315 ,p_rev_job_rate_sch_id => rate_rec.job_bill_rate_schedule_id
3316 ,p_rev_emp_rate_sch_id => rate_rec.emp_bill_rate_schedule_id
3317 ,p_plan_rev_job_rate_sch_id => rate_rec.rev_job_rate_sch_id
3318 ,p_plan_cost_job_rate_sch_id => rate_rec.cost_job_rate_sch_id
3319 ,p_plan_rev_emp_rate_sch_id => rate_rec.rev_emp_rate_sch_id
3320 ,p_plan_cost_emp_rate_sch_id => rate_rec.cost_emp_rate_sch_id
3321 ,p_plan_rev_nlr_rate_sch_id => rate_rec.rev_non_labor_res_rate_sch_id
3322 ,p_plan_cost_nlr_rate_sch_id => rate_rec.cost_non_labor_res_rate_sch_id
3323 ,p_plan_burden_cost_sch_id => rate_rec.cost_burden_rate_sch_id
3324 ,p_calculate_mode => l_calculate_mode
3325 ,p_mcb_flag => rate_rec.multi_currency_billing_flag
3326 ,p_cost_rate_multiplier => l_cost_rate_multiplier
3327 ,p_bill_rate_multiplier => l_bill_rate_multiplier
3328 ,p_quantity => l_txn_plan_quantity
3329 ,p_item_date => z.start_date
3330 ,p_cost_sch_type => l_cost_sch_type
3331 ,p_labor_sch_type => l_labor_sch_type
3332 ,p_non_labor_sch_type => l_non_labor_sch_type
3333 ,p_labor_schdl_discnt => NULL
3334 ,p_labor_bill_rate_org_id => rate_rec.labor_bill_rate_org_id
3335 ,p_labor_std_bill_rate_schdl => NULL
3336 ,p_labor_schdl_fixed_date => NULL
3337 ,p_assignment_id => z.resource_assignment_id
3338 ,p_project_org_id => rate_rec.org_id
3339 ,p_project_type => rate_rec.project_type
3340 ,p_expenditure_type => nvl(z.expenditure_type,z.rate_expenditure_type)
3341 ,p_non_labor_resource => z.non_labor_resource
3342 ,p_incurred_by_organz_id => z.organization_id
3343 ,p_override_to_organz_id => l_override_organization_id
3344 ,p_expenditure_org_id => nvl(z.rate_expenditure_org_id,rate_rec.org_id)
3345 ,p_assignment_precedes_task => rate_rec.assign_precedes_task
3346 ,p_planning_transaction_id => z.budget_line_id
3347 ,p_task_bill_rate_org_id => z.non_labor_bill_rate_org_id
3348 ,p_project_bill_rate_org_id => rate_rec.non_labor_bill_rate_org_id
3349 ,p_nlr_organization_id => z.organization_id
3350 ,p_project_sch_date => rate_rec.non_labor_schedule_fixed_date
3351 ,p_task_sch_date => z.non_labor_schedule_fixed_date
3352 ,p_project_sch_discount => rate_rec.non_labor_schedule_discount
3353 ,p_task_sch_discount => z.non_labor_schedule_discount
3354 ,p_inventory_item_id => z.inventory_item_id
3355 ,p_BOM_resource_Id => z.bom_resource_id
3356 ,P_mfc_cost_type_id => z.mfc_cost_type_id
3357 ,P_item_category_id => z.item_category_id
3358 ,p_mfc_cost_source => l_mfc_cost_source
3359 ,p_cost_override_rate => l_rw_cost_rate_override
3360 ,p_revenue_override_rate => l_bill_rate_override
3361 ,p_override_burden_cost_rate => l_burden_cost_rate_override
3362 ,p_override_currency_code => l_txn_currency_code_override
3363 ,p_txn_currency_code => l_txn_currency_code
3364 ,p_raw_cost => l_txn_raw_cost
3365 ,p_burden_cost => l_txn_burdened_cost
3366 ,p_raw_revenue => l_txn_revenue
3367 ,p_billability_flag => l_billable_flag
3368 ,x_bill_rate => x_bill_rate
3369 ,x_cost_rate => x_cost_rate
3370 ,x_burden_cost_rate => x_burden_cost_rate
3371 ,x_burden_multiplier => x_burden_multiplier
3372 ,x_raw_cost => x_raw_cost
3373 ,x_burden_cost => x_burden_cost
3374 ,x_raw_revenue => x_raw_revenue
3375 ,x_bill_markup_percentage => x_bill_markup_percentage
3376 ,x_cost_txn_curr_code => x_cost_txn_curr_code
3377 ,x_rev_txn_curr_code => x_rev_txn_curr_code
3378 ,x_raw_cost_rejection_code => x_raw_cost_rejection_code
3379 ,x_burden_cost_rejection_code => x_burden_cost_rejection_code
3380 ,x_revenue_rejection_code => x_revenue_rejection_code
3381 ,x_cost_ind_compiled_set_id => x_cost_ind_compiled_set_id
3382 ,x_return_status => l_return_status
3383 ,x_msg_data => x_msg_data
3384 ,x_msg_count => x_msg_count
3385 );
3386
3387 /* in the msp flow, if the rates are not found then show it as zero, may not be possible to show the
3388 * cost /burden rejections
3389 */
3390 If p_calling_module = 'MSP' Then
3391 If l_return_status = 'E' then
3392 l_return_status := 'S';
3393 x_msg_count := 0;
3394 End If;
3395 End If;
3396 l_stage := 'Return Sts of Rate API['||l_return_status||']msgData['||x_msg_data||']';
3397 If l_return_status = 'U' Then
3398 x_return_status := l_return_status;
3399 pa_utils.add_message
3400 (p_app_short_name => 'PA'
3401 ,p_msg_name => 'PA_FP_ERROR_FROM_RATE_API_CALL'
3402 ,p_token1 => 'G_PROJECT_NAME'
3403 ,p_value1 => rate_rec.project_name
3404 ,p_token2 => 'G_TASK_NAME'
3405 ,p_value2 => z.task_name
3406 ,p_token3 => 'G_RESOURCE_NAME'
3407 ,p_value3 => z.resource_name
3408 ,p_token4 => 'TO_CHAR(L_TXN_CURRENCY_CODE)'
3409 ,p_value4 => l_txn_currency_code
3410 ,p_token5 => 'TO_CHAR(L_BUDGET_LINES_START_DATE)'
3411 ,p_value5 => to_char(z.start_date));
3412 RAISE RATEAPI_UNEXPECTED_ERRORS;
3413 End If;
3414
3415 EXCEPTION
3416 WHEN OTHERS THEN
3417 l_stage := 'Unexpected error from Rate API['||l_return_status||']msgData['||x_msg_data||']';
3418 x_raw_cost_rejection_code := substr('PA_FP_ERROR_FROM_RATE_API_CALL',1,30);
3419 x_burden_cost_rejection_code := substr(SQLERRM,1,30);
3420 x_revenue_rejection_code := substr('PA_FP_ERROR_FROM_RATE_API_CALL',1,30);
3421 x_return_status := l_return_status;
3422 pa_utils.add_message
3423 (p_app_short_name => 'PA'
3424 ,p_msg_name => 'PA_FP_ERROR_FROM_RATE_API_CALL'
3425 ,p_token1 => 'G_PROJECT_NAME'
3426 ,p_value1 => rate_rec.project_name
3427 ,p_token2 => 'G_TASK_NAME'
3428 ,p_value2 => z.task_name
3429 ,p_token3 => 'G_RESOURCE_NAME'
3430 ,p_value3 => z.resource_name
3431 ,p_token4 => 'TO_CHAR(L_TXN_CURRENCY_CODE)'
3432 ,p_value4 => l_txn_currency_code
3433 ,p_token5 => 'TO_CHAR(L_BUDGET_LINES_START_DATE)'
3434 ,p_value5 => to_char(z.start_date));
3435 RAISE RATEAPI_UNEXPECTED_ERRORS;
3436 END;
3437
3438 IF l_rw_cost_rate_override is NOT NULL Then
3439 x_cost_rate := l_rw_cost_rate_override;
3440 x_cost_txn_curr_code := l_txn_currency_code_override;
3441 End If;
3442 If l_burden_cost_rate_override is NOT NULL Then
3443 x_burden_cost_rate := l_burden_cost_rate_override;
3444 End If;
3445 If l_bill_rate_override is NOT NULL Then
3446 x_bill_rate := l_bill_rate_override;
3447 End If;
3448
3449 l_stage := 'RawValues returned from Rate API:x_cost_txn_curr_code['||x_cost_txn_curr_code||']x_cost_rate['||x_cost_rate||']';
3450 l_stage := l_stage||']x_burden_cost_rate['||x_burden_cost_rate||']x_burden_multiplier['||x_burden_multiplier||']';
3451 l_stage := l_stage||'x_rev_txn_curr_code['||x_rev_txn_curr_code||']x_bill_rate['||x_bill_rate||']';
3452 print_msg(l_debug_flag,l_proc_name,l_stage);
3453 l_stage := 'CostRejection['||x_raw_cost_rejection_code||']BurdRejection['||x_burden_cost_rejection_code||']';
3454 l_stage := l_stage||']x_revenue_rejection_code['||x_revenue_rejection_code||']';
3455 print_msg(l_debug_flag,l_proc_name,l_stage);
3456
3457 IF x_cost_rate is NOT NULL AND x_burden_cost_rejection_code is NULL AND l_return_status = 'S' Then --{
3458
3459 --convert the cost amounts to burden currency if burden rate is passed
3460 IF l_burden_cost_rate_override is NOT NULL and l_txn_currency_code_override is NOT NULL Then
3461 IF x_cost_rate is NOT NULL and
3462 x_cost_txn_curr_code <> l_txn_currency_code_override Then
3463 x_dummy_curr_code := l_txn_currency_code_override;
3464 l_stage := 'Calling multi currency api to convert raw to burden currency';
3465 x_final_txn_exch_rate := NULL;
3466 x_final_txn_rate_type := NULL;
3467 x_final_txn_rate_date := NULL;
3468 x_final_txn_raw_cost := NULL;
3469 print_msg(l_debug_flag,l_proc_name,l_stage);
3470 pa_multi_currency_txn.get_currency_amounts (
3471 p_project_id => p_project_id
3472 ,p_exp_org_id => nvl(z.rate_expenditure_org_id,rate_rec.org_id)
3473 ,p_calling_module => 'WORKPLAN'
3474 ,p_task_id => z.task_id
3475 ,p_ei_date => z.start_date
3476 ,p_denom_raw_cost => 1
3477 ,p_denom_curr_code => x_cost_txn_curr_code
3478 ,p_acct_curr_code => x_dummy_curr_code
3479 ,p_accounted_flag => 'N'
3480 ,p_acct_rate_date => x_dummy_rate_date
3481 ,p_acct_rate_type => x_dummy_rate_type
3482 ,p_acct_exch_rate => x_dummy_exch_rate
3483 ,p_acct_raw_cost => x_dummy_cost
3484 ,p_project_curr_code => l_txn_currency_code_override
3485 ,p_project_rate_type => x_final_txn_rate_type
3486 ,p_project_rate_date => x_final_txn_rate_date
3487 ,p_project_exch_rate => x_final_txn_exch_rate
3488 ,p_project_raw_cost => x_final_txn_raw_cost
3489 ,p_projfunc_curr_code => x_dummy_curr_code
3490 ,p_projfunc_cost_rate_type => x_dummy_rate_type
3491 ,p_projfunc_cost_rate_date => x_dummy_rate_date
3492 ,p_projfunc_cost_exch_rate => x_dummy_exch_rate
3493 ,p_projfunc_raw_cost => x_dummy_cost
3494 ,p_system_linkage => 'NER'
3495 ,p_structure_version_id => rate_rec.project_structure_version_id
3496 ,p_status => l_status
3497 ,p_stage => x_stage) ;
3498
3499 l_stage := 'x_final_txn_exch_rate['||x_final_txn_exch_rate||']status['||l_status||']';
3500 print_msg(l_debug_flag,l_proc_name,l_stage);
3501 IF x_final_txn_exch_rate is NULL OR l_status is NOT NULL Then
3502 x_return_status := 'U';
3503 l_return_status := 'U';
3504 pa_utils.add_message
3505 ( p_app_short_name => 'PA'
3506 ,p_msg_name => 'PA_FP_PROJ_NO_TXNCONVRATE'
3507 ,p_token1 => 'G_PROJECT_NAME'
3508 ,p_value1 => rate_rec.project_name
3509 ,p_token2 => 'FROMCURRENCY'
3510 ,p_value2 => x_cost_txn_curr_code
3511 ,p_token3 => 'TOCURRENCY'
3512 ,p_value3 => l_txn_currency_code_override
3513 ,p_token4 => 'CONVERSION_TYPE'
3514 ,p_value4 => x_final_txn_rate_type
3515 ,p_token5 => 'CONVERSION_DATE'
3516 ,p_value5 => x_final_txn_rate_date
3517 );
3518 x_msg_data := 'PA_FP_PROJ_NO_TXNCONVRATE';
3519 RAISE L_TXNCONVRATE_ERROR;
3520 END IF;
3521 IF NVL(l_return_status,'S') = 'S' Then
3522 x_cost_rate := x_final_txn_exch_rate * x_cost_rate;
3523 x_cost_txn_curr_code := l_txn_currency_code_override;
3524 End If;
3525
3526 End If;
3527 END IF;
3528 END IF; --}
3529 /* Assign derived values to the out params */
3530 l_stage := 'Assigning derived values to out params:'||x_cost_rate||':'||x_burden_cost_rate||':'||x_bill_rate;
3531 x_raw_cost_rate_tab(l_Cntr) := x_cost_rate;
3532 x_burden_cost_rate_tab(l_Cntr) := x_burden_cost_rate;
3533 x_burden_multiplier_tab(l_Cntr) := x_burden_multiplier;
3534 x_ind_compiled_set_id_tab(l_Cntr) := x_cost_ind_compiled_set_id;
3535 x_bill_rate_tab(l_Cntr) := x_bill_rate;
3536 x_markup_percent_tab(l_Cntr) := x_bill_markup_percentage;
3537 x_txn_currency_code_tab(l_Cntr) := null;
3538 x_cost_txn_curr_code_tab(l_Cntr):= x_cost_txn_curr_code;
3539 x_rev_txn_curr_code_tab(l_Cntr):= x_rev_txn_curr_code;
3540 x_cost_rejection_code_tab(l_Cntr) := x_raw_cost_rejection_code;
3541 x_burden_rejection_code_tab(l_Cntr) := x_burden_cost_rejection_code;
3542 x_revenue_rejection_code_tab(l_Cntr) := x_revenue_rejection_code;
3543
3544 END LOOP; --}
3545 print_msg(l_debug_flag,l_proc_name,'End of rate api loop');
3546 /*** Not required as re arrangig the plsql indexes
3547 IF l_rowid_tab.COUNT > 0 Then
3548 l_stage := 'Inserting rate api values into rollup tmp table';
3549 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
3550 INSERT INTO pa_fp_rollup_tmp tmp
3551 (resource_assignment_id
3552 ,system_reference1
3553 ,txn_currency_code
3554 ,cost_rate
3555 ,rw_cost_rate_override
3556 ,burden_cost_rate
3557 ,burden_cost_rate_override
3558 ,bill_rate
3559 ,bill_rate_override
3560 ,burden_multiplier
3561 ,bill_markup_percentage
3562 ,cost_txn_curr_code
3563 ,rev_txn_curr_code
3564 ,cost_ind_compiled_set_id
3565 ,cost_rejection_code
3566 ,burden_rejection_code
3567 ,revenue_rejection_code
3568 ) VALUES
3569 (x_resource_assignment_id_tab(i)
3570 ,x_resource_list_member_id_tab(i)
3571 ,x_txn_currency_code_tab(i)
3572 ,x_raw_cost_rate_tab(i)
3573 ,x_rw_cost_rate_override_tab(i)
3574 ,x_burden_cost_rate_tab(i)
3575 ,x_burden_rate_override_tab(i)
3576 ,x_bill_rate_tab(i)
3577 ,x_bill_rate_override_tab(i)
3578 ,x_burden_multiplier_tab(i)
3579 ,x_markup_percent_tab(i)
3580 ,x_cost_txn_curr_code_tab(i)
3581 ,x_rev_txn_curr_code_tab(i)
3582 ,x_ind_compiled_set_id_tab(i)
3583 ,x_cost_rejection_code_tab(i)
3584 ,x_burden_rejection_code_tab(i)
3585 ,x_revenue_rejection_code_tab(i)
3586 );
3587 END IF;
3588 ***/
3589
3590 x_return_status := l_return_status;
3591 l_stage := 'Return status of Get_resource_Rates['||x_return_status||']';
3592 print_msg(l_debug_flag,l_proc_name,l_stage);
3593 /* added this to avoid msg added in the stack during bill rate api */
3594 IF x_return_status = 'S' then
3595 FND_MSG_PUB.initialize;
3596 End If;
3597 EXCEPTION
3598
3599 WHEN L_INVALID_PARAMS THEN
3600 PRINT_msg('Y',l_proc_name,'INVALID PARAMS FOR PROCESSING');
3601 x_return_status := 'E';
3602 RAISE;
3603
3604 WHEN RATEAPI_UNEXPECTED_ERRORS then
3605 PRINT_msg('Y',l_proc_name,'Rate API returned with unexpected error');
3606 x_return_status := 'U';
3607 RAISE;
3608 WHEN L_TXNCONVRATE_ERROR THEN
3609 PRINT_msg('Y',l_proc_name,'Error from Multi-Currency API');
3610 x_return_status := 'U';
3611 RAISE;
3612
3613 WHEN OTHERS THEN
3614 PRINT_msg('Y',l_proc_name,'EXCEPTIONS: '||SQLCODE||SQLERRM);
3615 x_return_status := 'U';
3616 RAISE;
3617
3618 END Get_Resource_Rates;
3619
3620 /** MRC Elimination: Moved this procedure from pa_mrc_finplan pkg to utils as
3621 * package itself is dropped
3622 */
3623 PROCEDURE POPULATE_BL_MAP_TMP
3624 (p_source_fin_plan_version_id IN PA_BUDGET_LINES.budget_version_id%TYPE
3625 ,x_return_status OUT NOCOPY VARCHAR2
3626 ,x_msg_count OUT NOCOPY NUMBER
3627 ,x_msg_data OUT NOCOPY VARCHAR2
3628 ) IS
3629
3630 CURSOR C_TMP_BUDGET_LINE IS
3631 SELECT
3632 budget_line_id
3633 ,pa_budget_lines_s.nextval
3634
3635 FROM
3636 pa_budget_lines
3637 WHERE
3638 budget_version_id = p_source_fin_plan_version_id ;
3639
3640
3641
3642 l_msg_count NUMBER := 0;
3643 l_data VARCHAR2(2000);
3644 l_msg_data VARCHAR2(2000);
3645 l_error_msg_code VARCHAR2(30);
3646 l_msg_index_out NUMBER;
3647 l_debug_mode VARCHAR2(30);
3648 g_module_name VARCHAR2(30) := 'pa.plsql.PA_FIN_PLAN_UTILS2';
3649 g_plsql_max_array_size NUMBER := 200 ;
3650
3651 TYPE l_budget_line_id_tbl_typ IS TABLE OF
3652 pa_budget_lines.BUDGET_LINE_ID%TYPE INDEX BY BINARY_INTEGER ;
3653
3654 l_source_budget_line_id_tbl l_budget_line_id_tbl_typ ;
3655 l_target_budget_line_id_tbl l_budget_line_id_tbl_typ ;
3656
3657
3658 BEGIN
3659
3660 -- Set the error stack.
3661 pa_debug.set_err_stack('PA_MRC_FINPLAN.POPULATE_BL_MAP_TMP');
3662
3663 -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3664 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3665 l_debug_mode := NVL(l_debug_mode, 'Y');
3666
3667 -- Initialize the return status to success
3668 x_return_status := FND_API.G_RET_STS_SUCCESS;
3669 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3670
3671 IF P_PA_DEBUG_MODE = 'Y' THEN
3672 pa_debug.g_err_stage := 'In PA_MRC_FINPLAN.POPULATE_BL_MAP_TMP ';
3673 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,pa_debug.g_err_stage,3);
3674 END IF;
3675
3676 -- Validate the input parameters.
3677
3678 IF p_source_fin_plan_version_id IS NULL THEN
3679
3680 IF P_PA_DEBUG_MODE = 'Y' THEN
3681 pa_debug.g_err_stage := 'Mandatory input parameter is null.';
3682 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,pa_debug.g_err_stage,5);
3683
3684 pa_debug.g_err_stage := 'Source Budget Version Id = ' || p_source_fin_plan_version_id;
3685 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,pa_debug.g_err_stage,5);
3686 END IF;
3687
3688 x_return_status := FND_API.G_RET_STS_ERROR;
3689
3690 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
3691 ,p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3692
3693 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3694
3695 END IF;
3696
3697 -- Clear the PA_FP_BL_MAP_TMP before inserting fresh records
3698 DELETE FROM PA_FP_BL_MAP_TMP;
3699
3700 OPEN C_TMP_BUDGET_LINE ;
3701
3702 LOOP
3703 -- Doing bulk fetch
3704 l_source_budget_line_id_tbl.delete;
3705 l_target_budget_line_id_tbl.delete;
3706 FETCH C_TMP_BUDGET_LINE BULK COLLECT INTO
3707 l_source_budget_line_id_tbl
3708 ,l_target_budget_line_id_tbl
3709 LIMIT g_plsql_max_array_size;
3710
3711 /* Commented for bug# 2629138:
3712 EXIT WHEN C_TMP_BUDGET_LINE%NOTFOUND; */
3713
3714 IF NVL(l_target_budget_line_id_tbl.last,0) >= 1 THEN
3715
3716 -- Only if something is fetched
3717
3718 FORALL i in l_target_budget_line_id_tbl.first..l_target_budget_line_id_tbl.last
3719
3720 INSERT INTO PA_FP_BL_MAP_TMP
3721 ( source_budget_line_id
3722 ,target_budget_line_id
3723 )
3724 VALUES ( l_source_budget_line_id_tbl(i)
3725 ,l_target_budget_line_id_tbl(i)
3726 );
3727
3728 END IF;
3729
3730 --exit loop if the recent fetch size is less than 200
3731
3732 EXIT WHEN NVL(l_target_budget_line_id_tbl.last,0)<g_plsql_max_array_size;
3733
3734 END LOOP;
3735 CLOSE C_TMP_BUDGET_LINE; -- Added for bug#6320022
3736 --Bug 2628051:- stack should be reset at the end of the api
3737 pa_debug.reset_err_stack;
3738
3739 EXCEPTION
3740
3741 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3742 l_msg_count := FND_MSG_PUB.count_msg;
3743 IF l_msg_count = 1 THEN
3744 PA_INTERFACE_UTILS_PUB.get_messages
3745 (p_encoded => FND_API.G_TRUE
3746 ,p_msg_index => 1
3747 ,p_msg_count => l_msg_count
3748 ,p_msg_data => l_msg_data
3749 ,p_data => l_data
3750 ,p_msg_index_out => l_msg_index_out);
3751 x_msg_data := l_data;
3752 x_msg_count := l_msg_count;
3753 ELSE
3754 x_msg_count := l_msg_count;
3755 END IF;
3756 x_return_status := FND_API.G_RET_STS_ERROR;
3757 IF P_PA_DEBUG_MODE = 'Y' THEN
3758 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,'Invalid Arguments Passed. ' || x_msg_data,5);
3759 pa_debug.write_file('POPULATE_BL_MAP_TMP: Invalid Arguments Passed. ' || x_msg_data);
3760 END IF;
3761 pa_debug.reset_err_stack;
3762 RAISE;
3763
3764 WHEN Others THEN
3765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3766 x_msg_count := 1;
3767 x_msg_data := SQLERRM;
3768 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MRC_FINPLAN'
3769 ,p_procedure_name => 'POPULATE_BL_MAP_TMP'
3770 ,p_error_text => SQLERRM);
3771 IF P_PA_DEBUG_MODE = 'Y' THEN
3772 pa_debug.write('POPULATE_BL_MAP_TMP: ' || g_module_name,x_msg_data,4);
3773 pa_debug.write_file('POPULATE_BL_MAP_TMP: ' || x_msg_data);
3774 END IF;
3775 pa_debug.reset_err_stack;
3776 RAISE ;--FND_API.G_EXC_UNEXPECTED_ERROR;
3777
3778 END POPULATE_BL_MAP_TMP ;
3779
3780 END PA_FIN_PLAN_UTILS2 ;