[Home] [Help]
PACKAGE BODY: APPS.PA_MULTI_CURRENCY_TXN
Source
1 PACKAGE BODY pa_multi_currency_txn AS
2 --$Header: PAXMCTXB.pls 120.4.12010000.9 2009/02/18 17:42:09 prabsing ship $
3
4 /** This is a global Record structure used as a cache for FI calls **/
5
6 TYPE FI_REC_ATTRB_TYPE IS RECORD (
7 prev_project_id pa_projects_all.project_id%type := NULL
8 ,prev_exp_org_id number := NULL
9 ,prev_ei_date date := null
10 ,prev_attribute varchar2(200) := null
11 ,project_rate_type varchar2(100) := null
12 ,project_rate_date date := NULL
13 ,projfunc_cost_rate_type varchar2(100) := null
14 ,projfunc_cost_rate_date date := NULL
15 ,acct_rate_type varchar2(100) := null
16 ,acct_rate_date date := NULL
17 ,project_currency_code varchar2(100) := null
18 ,projfunc_currency_code varchar2(100) := null
19 ,acct_currency_code varchar2(100) := null
20 ,denom_currency_code varchar2(100) := null
21 );
22
23 G_REC_FI_ATTRB FI_REC_ATTRB_TYPE;
24
25
26 P_DEBUG_MODE BOOLEAN := pa_cc_utils.g_debug_mode ;
27
28 PROCEDURE print_message(p_msg varchar2) IS
29 BEGIN
30 --r_debug.r_msg('Log: '||p_msg);
31 --hsk.print_msg('Log: '|| p_msg);
32 NULL;
33 END print_message;
34
35 /** This procedure initializes the global record for each get_currency_amounts call **/
36
37 PROCEDURE initialize_global_rec IS
38
39 BEGIN
40 G_REC_FI_ATTRB.prev_project_id := null;
41 G_REC_FI_ATTRB.prev_exp_org_id := null;
42 G_REC_FI_ATTRB.prev_ei_date := null;
43 G_REC_FI_ATTRB.prev_attribute := null;
44 G_REC_FI_ATTRB.project_rate_type := null;
45 G_REC_FI_ATTRB.project_rate_date := NULL;
46 G_REC_FI_ATTRB.projfunc_cost_rate_type := null;
47 G_REC_FI_ATTRB.projfunc_cost_rate_date := NULL;
48 G_REC_FI_ATTRB.acct_rate_type := null;
49 G_REC_FI_ATTRB.acct_rate_date := NULL;
50 G_REC_FI_ATTRB.project_currency_code := null;
51 G_REC_FI_ATTRB.projfunc_currency_code := null;
52 G_REC_FI_ATTRB.acct_currency_code := null;
53 G_REC_FI_ATTRB.denom_currency_code := null;
54
55 END initialize_global_rec;
56
57
58 /** This api is created to derive project/Acct/Projfunc attributes for
59 * forcasting modules
60 **/
61 PROCEDURE derive_fi_curr_attributes
62 ( P_project_id IN number
63 ,P_exp_org_id IN number
64 ,P_ei_date IN date
65 ,P_attribute IN varchar2
66 ,x_project_rate_type IN OUT NOCOPY varchar2
67 ,x_project_rate_date IN OUT NOCOPY date
68 ,x_projfunc_cost_rate_type IN OUT NOCOPY varchar2
69 ,x_projfunc_cost_rate_date IN OUT NOCOPY date
70 ,x_acct_rate_type IN OUT NOCOPY varchar2
71 ,x_acct_rate_date IN OUT NOCOPY date)
72
73 IS
74
75 /** Bug fix :2322364
76 * Derivation of currency attributes are based on the following logic This holds good only
77 * For Labor Transactions ie, system linkage function = ST / OT and calling module = FORECAST
78 * Logic : If the x_project_rate_type / x_project_rate_date / x_projfunc_cost_rate_type is NULL
79 * Then derive the attributes at the projects OU ie. receiver org
80 * If x_acct_rate_type / x_acct_rate_date / x_projfunc_cost_rate_date is NULL then derive the
81 * attributes at the Expenditure OU .
82 **/
83
84 CURSOR cur_attrib IS
85 SELECT proj.project_currency_code PROJECT_CURRENCY_CODE
86 ,NVL(x_project_rate_type,
87 NVL(proj.project_rate_type,imp_recvr.default_rate_type)) PROJECT_RATE_TYPE
88 ,NVL(proj.project_rate_date,DECODE(imp_recvr.default_rate_date_code,
89 'E', P_EI_date, 'P'
90 ,pa_utils2.get_pa_date(P_EI_date,sysdate, imp_recvr.org_id))) PROJECT_RATE_DATE
91 ,NVL(x_acct_rate_type, imp_prvdr.default_rate_type) ACCT_RATE_TYPE
92 ,NVL(x_acct_rate_date,DECODE(imp_prvdr.default_rate_date_code,
93 'E', P_EI_date, 'P'
94 ,pa_utils2.get_pa_date(P_EI_date,sysdate, imp_prvdr.org_id))) ACCT_RATE_DATE
95 ,proj.projfunc_currency_code PROJFUNC_CURRENCY_CODE
96 ,NVL(x_projfunc_cost_rate_type,
97 NVL( proj.projfunc_cost_rate_type,imp_recvr.default_rate_type)) PROJFUNC_COST_RATE_TYPE
98 ,NVL(proj.projfunc_cost_rate_date, DECODE(imp_prvdr.default_rate_date_code,
99 'E', P_EI_date, 'P'
100 ,pa_utils2.get_pa_date(P_EI_date,sysdate, imp_prvdr.org_id))) PROJFUNC_COST_RATE_DATE
101 FROM pa_projects_all proj
102 ,pa_implementations_all imp_prvdr
103 ,pa_implementations_all imp_recvr /* bug fix :2322364 */
104 WHERE proj.project_id = P_project_id
105 AND imp_prvdr.org_id = P_exp_org_id
106 AND proj.org_id = imp_recvr.org_id; /* bug fix :2322364 */
107
108 l_project_currency_code VARCHAR2(100);
109 l_project_rate_type VARCHAR2(100);
110 l_project_rate_date DATE;
111 l_acct_rate_type VARCHAR2(100);
112 l_acct_rate_date DATE;
113 l_projfunc_currency_code VARCHAR2(100);
114 l_projfunc_cost_rate_type VARCHAR2(100);
115 l_projfunc_cost_rate_date DATE;
116
117 -- begin r12 nocopy changes
118 l_temp_project_rate_type VARCHAR2(100);
119 l_temp_project_rate_date date;
120 l_temp_projfunc_cost_rate_type VARCHAR2(100);
121 l_temp_projfunc_cost_rate_date date;
122 l_temp_acct_rate_type VARCHAR2(100);
123 l_temp_acct_rate_date date;
124
125 BEGIN
126
127 l_temp_project_rate_type := x_project_rate_type;
128 l_temp_project_rate_date := x_project_rate_date;
129 l_temp_projfunc_cost_rate_type := x_projfunc_cost_rate_type;
130 l_temp_projfunc_cost_rate_date := x_projfunc_cost_rate_date;
131 l_temp_acct_rate_type := x_acct_rate_type;
132 l_temp_acct_rate_date := x_acct_rate_date;
133 -- end r12 nocopy changes
134
135 --Initialize the error stack
136 --Note : pa_debug calls are commented out as it voilates get_currency_attrib pragma excpetions
137 --PA_DEBUG.init_err_stack('PA_MULTI_CURRENCY_TXN.derive_currency_attributes_fi');
138 print_message('Inside derive_fi_curr_attributes api');
139
140 IF (G_REC_FI_ATTRB.prev_project_id is NULL OR
141 G_REC_FI_ATTRB.prev_project_id <> P_project_id ) OR
142 (G_REC_FI_ATTRB.prev_exp_org_id is NULL OR
143 G_REC_FI_ATTRB.prev_exp_org_id <> p_exp_org_id ) OR
144 (G_REC_FI_ATTRB.prev_ei_date is NULL OR
145 TRUNC(G_REC_FI_ATTRB.prev_ei_date) <> Trunc(p_ei_date) ) OR
146 (G_REC_FI_ATTRB.prev_attribute is NULL OR
147 G_REC_FI_ATTRB.prev_attribute <> P_attribute ) Then
148
149 print_message('Opening cursor to fetch attributes');
150
151
152 OPEN cur_attrib;
153 FETCH cur_attrib
154 INTO l_project_currency_code
155 ,l_project_rate_type
156 ,l_project_rate_date
157 ,l_acct_rate_type
158 ,l_acct_rate_date
159 ,l_projfunc_currency_code
160 ,l_projfunc_cost_rate_type
161 ,l_projfunc_cost_rate_date ;
162
163 If cur_attrib%found then
164 print_message('Cursor fetch 1 records');
165 Else
166 print_message('Cursor fetch NO rcords');
167 End if;
168 ClOSE cur_attrib;
169
170 print_message('End of Fetch');
171 print_message('Assigning to OUT variables');
172
173 -- Assign to OUT variables
174 x_project_rate_type := l_project_rate_type;
175 x_project_rate_date := l_project_rate_date;
176 x_projfunc_cost_rate_type := l_projfunc_cost_rate_type;
177 x_projfunc_cost_rate_date := l_projfunc_cost_rate_date;
178 x_acct_rate_type := l_acct_rate_type;
179 x_acct_rate_date := l_acct_rate_date;
180 G_REC_FI_ATTRB.prev_project_id := P_project_id;
181 G_REC_FI_ATTRB.prev_exp_org_id := P_exp_org_id;
182 G_REC_FI_ATTRB.prev_ei_date := P_ei_date;
183 G_REC_FI_ATTRB.prev_attribute := P_attribute;
184 G_REC_FI_ATTRB.project_rate_type := l_project_rate_type;
185 G_REC_FI_ATTRB.project_rate_date := l_project_rate_date;
186 G_REC_FI_ATTRB.projfunc_cost_rate_type := l_projfunc_cost_rate_type;
187 G_REC_FI_ATTRB.projfunc_cost_rate_date := l_projfunc_cost_rate_date;
188 G_REC_FI_ATTRB.acct_rate_type := l_acct_rate_type;
189 G_REC_FI_ATTRB.acct_rate_date := l_acct_rate_date;
190 G_REC_FI_ATTRB.project_currency_code := l_project_currency_code;
191 G_REC_FI_ATTRB.projfunc_currency_code := l_projfunc_currency_code;
192
193 ELSE -- Retrieve from the cache
194
195 print_message('Retrieve from the cache ');
196
197 x_project_rate_type := G_REC_FI_ATTRB.project_rate_type;
198 x_project_rate_date := G_REC_FI_ATTRB.project_rate_date;
199 x_projfunc_cost_rate_type := G_REC_FI_ATTRB.projfunc_cost_rate_type;
200 x_projfunc_cost_rate_date := G_REC_FI_ATTRB.projfunc_cost_rate_date;
201 x_acct_rate_type := G_REC_FI_ATTRB.acct_rate_type;
202 x_acct_rate_date := G_REC_FI_ATTRB.acct_rate_date;
203
204 END IF;
205
206 -- reset the error stack;
207 --PA_DEBUG.reset_err_stack;
208 Return;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 IF cur_attrib%isopen then
213 close cur_attrib;
214 END IF;
215 print_message('Failed in derive_fi_curr_attributes :'||sqlerrm||sqlcode);
216 x_project_rate_type := l_temp_project_rate_type;
217 x_project_rate_date := l_temp_project_rate_date;
218 x_projfunc_cost_rate_type := l_temp_projfunc_cost_rate_type;
219 x_projfunc_cost_rate_date := l_temp_projfunc_cost_rate_date;
220 x_acct_rate_type := l_temp_acct_rate_type;
221 x_acct_rate_date := l_temp_acct_rate_date;
222 RAISE;
223
224 END derive_fi_curr_attributes;
225
226
227 PROCEDURE get_projfunc_cost_rate_type (
228 P_task_id IN NUMBER ,
229 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL,
230 P_calling_module IN VARCHAR2 ,
231 p_structure_version_id IN NUMBER DEFAULT NULL,
232 P_projfunc_currency_code IN OUT NOCOPY VARCHAR2 ,
233 P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 )
234
235 IS
236
237 BEGIN
238 --
239 -- This procedure derives project functional currency code and
240 -- project functional currency conversion rate type
241 --
242 -- Logic: if the user provides a projfunc_cost_rate_type, use it.
243 -- Otherwise derive it from the task, if taskfunc_cost_rate_type is not
244 -- defined at task level then get it from project level. If projfunc_cost_rate_type
245 -- is not defined at project level also then derive the
246 -- project_rate_type value from default_rate_type column in
247 -- project owning operating units implementation options table.
248 -- projfunc_currency_code is derived from projects table
249
250 IF (p_calling_module <> 'WORKPLAN')
251 THEN
252 SELECT proj.projfunc_currency_code,
253 NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
254 proj.projfunc_cost_rate_type), imp.default_rate_type))
255 INTO P_projfunc_currency_code,
256 P_projfunc_cost_rate_type
257 FROM pa_projects_all proj,
258 pa_tasks task,
259 pa_implementations_all imp
260 WHERE proj.project_id = task.project_id
261 AND task.task_id = P_task_id
262 AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
263 ELSE
264 /***********************
265 SELECT proj.projfunc_currency_code,
266 NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
267 proj.projfunc_cost_rate_type), imp.default_rate_type))
268 INTO P_projfunc_currency_code,
269 P_projfunc_cost_rate_type
270 FROM pa_projects_all proj,
271 pa_tasks task,
272 pa_map_wp_to_fin_tasks_v map_wp_fin,
273 pa_implementations_all imp
274 WHERE proj.project_id = map_wp_fin.project_id
275 AND task.task_id(+) = map_wp_fin.mapped_fin_task_id
276 AND map_wp_fin.proj_element_id = p_task_id
277 AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
278 ******************/
279 BEGIN
280 SELECT proj.projfunc_currency_code,
281 NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
282 proj.projfunc_cost_rate_type), imp.default_rate_type))
283 INTO P_projfunc_currency_code,
284 P_projfunc_cost_rate_type
285 FROM pa_projects_all proj,
286 pa_tasks task,
287 pa_map_wp_to_fin_tasks_v map_wp_fin,
288 pa_implementations_all imp
289 WHERE proj.project_id = p_project_id
290 AND task.task_id = map_wp_fin.mapped_fin_task_id
291 AND map_wp_fin.proj_element_id = p_task_id
292 AND map_wp_fin.parent_structure_version_id = p_structure_version_id
293 AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
294 EXCEPTION
295 WHEN NO_DATA_FOUND
296 THEN
297 SELECT proj.projfunc_currency_code,
298 NVL(P_projfunc_cost_rate_type, NVL(proj.projfunc_cost_rate_type
299 , imp.default_rate_type))
300 INTO P_projfunc_currency_code,
301 P_projfunc_cost_rate_type
302 FROM pa_projects_all proj,
303 pa_implementations_all imp
304 WHERE proj.project_id = p_project_id
305 AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
306 END; -- anonymous
307 END IF;
308
309 EXCEPTION
310 WHEN no_data_found THEN
311 P_projfunc_currency_code := NULL;
312 P_projfunc_cost_rate_type := NULL;
313
314 WHEN others THEN
315 P_projfunc_currency_code := NULL;
316 P_projfunc_cost_rate_type := NULL;
317 RAISE ;
318
319 END get_projfunc_cost_rate_type ;
320
321
322 PROCEDURE get_def_projfunc_cst_rate_type (
323 P_task_id IN NUMBER ,
324 P_projfunc_currency_code IN OUT NOCOPY VARCHAR2 ,
325 P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 )
326
327 IS
328
329 BEGIN
330 --
331 -- This procedure derives project functional currency code and
332 -- project functional currency conversion rate type
333 --
334 -- Logic: if the user provides a projfunc_cost_rate_type, use it.
335 -- Otherwise derive it from the task, if taskfunc_cost_rate_type is not
336 -- defined at task level then get it from project level. If projfunc_cost_rate_type
337 -- is not defined at project level also then derive the
338 -- project_rate_type value from default_rate_type column in
339 -- expenditure owning operating units implementation options table.
340 -- projfunc_currency_code is derived from projects table
341
342 SELECT proj.projfunc_currency_code,
343 NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
344 proj.projfunc_cost_rate_type), imp.default_rate_type))
345 INTO P_projfunc_currency_code,
346 P_projfunc_cost_rate_type
347 FROM pa_projects_all proj,
348 pa_tasks task,
349 pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
350 WHERE proj.project_id = task.project_id
351 AND task.task_id = P_task_id
352 AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
353
354 EXCEPTION
355 WHEN no_data_found THEN
356 P_projfunc_currency_code := NULL;
357 P_projfunc_cost_rate_type := NULL;
358
359 WHEN others THEN
360 P_projfunc_currency_code := NULL;
361 P_projfunc_cost_rate_type := NULL;
362 RAISE ;
363
364 END get_def_projfunc_cst_rate_type ;
365
366 FUNCTION get_proj_curr_code_sql ( P_project_id NUMBER) RETURN VARCHAR2 IS
367
368 l_project_currency_code VARCHAR2(30) ;
369 BEGIN
370 --
371 -- This function returns the Project Currency Code from the
372 -- pa_projects_all table based on the project_id which is a
373 -- parameter for this function
374 -- Since, the Project Currency Code column is a NOT NULL column
375 -- in the pa_projects_all table, we need not handle the no_data_found
376 -- exception nor do we need to go to the Project owning OU to get the
377 -- currency Code from the Set of Books Id.
378
379 SELECT project_currency_code
380 INTO l_project_currency_code
381 FROM pa_projects_all
382 WHERE project_id = P_project_id ;
383
384 RETURN l_project_currency_code ;
385
386 EXCEPTION
387 WHEN others THEN
388 RAISE ;
389
390 END get_proj_curr_code_sql ;
391
392 -- fix for bug # 910659. Changed from clause to select from
393 -- pa_projects_all instead of pa_projects
394
395 PROCEDURE get_projfunc_cost_rate_date (
396 P_task_id IN NUMBER ,
397 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL ,
398 P_EI_date IN DATE ,
399 p_structure_version_id IN NUMBER DEFAULT NULL,
400 P_calling_module IN VARCHAR2 ,
401 P_projfunc_cost_rate_date IN OUT NOCOPY DATE )
402
403 IS
404
405 BEGIN
406
407 --
408 -- This procedure derives project functional currency conversion rate date
409 --
410 -- Logic: If user provides a project functional currency conversion date, Use it.
411 -- Otherwise derive it from task( identified bt P_task_id),
412 -- if taskfunc_cost_rate_date is not defined at task level then derive it from
413 -- projects table. If the projfunc_cost_rate_date is not defined at project
414 -- level also then the projfunc_cost_rate_date will be derived using the
415 -- default_rate_date_code from expenditure operating units implementation
416 -- options. If the default_rate_date_code is E then return the expenditure
417 -- item date(P_EI_date), if default_rate_date_code is P then return the
418 -- PA period ending date.
419
420 IF ( P_projfunc_cost_rate_date IS NULL )
421 THEN
422 IF (p_calling_module <> 'WORKPLAN')
423 THEN
424 SELECT NVL(NVL(task.taskfunc_cost_rate_date,
425 proj.projfunc_cost_rate_date),
426 DECODE(imp.default_rate_date_code,
427 'E', P_EI_date, 'P',
428 pa_utils2.get_pa_date(P_EI_date,
429 sysdate, imp.org_id))) /**CBGA**/
430 INTO P_projfunc_cost_rate_date
431 FROM pa_projects_all proj,
432 pa_tasks task,
433 pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
434 WHERE task.task_id = P_task_id
435 AND proj.project_id = task.project_id
436 AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
437 ELSE
438 BEGIN
439 SELECT task.taskfunc_cost_rate_date
440 INTO P_projfunc_cost_rate_date
441 FROM pa_tasks task
442 ,pa_map_wp_to_fin_tasks_v map_wp_fin
443 WHERE task.task_id = map_wp_fin.mapped_fin_task_id
444 AND map_wp_fin.proj_element_id = P_task_id
445 AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN NULL;
448 END; -- anonymous
449 IF ( P_projfunc_cost_rate_date IS NULL )
450 THEN
451 SELECT NVL(proj.projfunc_cost_rate_date,
452 DECODE(imp.default_rate_date_code,
453 'E', P_EI_date, 'P',
454 pa_utils2.get_pa_date(P_EI_date,
455 sysdate, imp.org_id)))
456 INTO P_projfunc_cost_rate_date
457 FROM pa_projects_all proj
458 ,pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
459 WHERE proj.project_id = p_project_id
460 AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
461 END IF;
462 END IF; -- calling_module
463 END IF ;
464
465
466 EXCEPTION
467 WHEN no_data_found THEN
468 P_projfunc_cost_rate_date := NULL ;
469
470 WHEN others THEN
471 P_projfunc_cost_rate_date := NULL ;
472 RAISE ;
473
474 END get_projfunc_cost_rate_date ;
475
476
477 PROCEDURE get_def_projfunc_cst_rate_date (
478 P_task_id IN NUMBER ,
479 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL,
480 P_EI_date IN DATE ,
481 P_structure_version_id IN NUMBER DEFAULT NULL,
482 P_calling_module IN VARCHAR2 ,
483 P_projfunc_cost_rate_date IN OUT NOCOPY DATE )
484 IS
485
486 BEGIN
487
488 --
489 -- This procedure derives project functional currency conversion rate date
490 --
491 -- Logic: If user provides a project functional currency conversion date, Use it.
492 -- Otherwise derive it from task( identified bt P_task_id),
493 -- if taskfunc_cost_rate_date is not defined at task level then derive it from
494 -- projects table. If the projfunc_cost_rate_date is not defined at project
495 -- level also then the project_rate_date will be derived using the
496 -- default_rate_date_code from expenditure operating units implementation
497 -- options. If the default_rate_date_code is E then return the expenditure
498 -- item date(P_EI_date), if default_rate_date_code is P then return
499 -- null.
500
501 IF ( P_projfunc_cost_rate_date IS NULL )
502 THEN
503 IF (p_calling_module <> 'WORKPLAN')
504 THEN
505 SELECT NVL(NVL(task.taskfunc_cost_rate_date,
506 proj.projfunc_cost_rate_date),
507 DECODE(imp.default_rate_date_code,
508 'E', P_EI_date, 'P',
509 NULL))
510 INTO P_projfunc_cost_rate_date
511 FROM pa_projects_all proj,
512 pa_tasks task,
513 pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
514 WHERE task.task_id = P_task_id
515 AND proj.project_id = task.project_id
516 AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
517 ELSE
518 /************************
519 SELECT NVL(NVL(task.taskfunc_cost_rate_date,
520 proj.projfunc_cost_rate_date),
521 DECODE(imp.default_rate_date_code,
522 'E', P_EI_date, 'P',
523 NULL))
524 INTO P_projfunc_cost_rate_date
525 FROM pa_projects_all proj,
526 pa_tasks task,
527 pa_map_wp_to_fin_tasks_v map_wp_fin,
528 pa_implementations imp
529 WHERE proj.project_id = map_wp_fin.project_id
530 AND task.task_id(+) = map_wp_fin.mapped_fin_task_id
531 AND map_wp_fin.proj_element_id = p_task_id;
532 *********************/
533 BEGIN
534 SELECT task.taskfunc_cost_rate_date
535 INTO P_projfunc_cost_rate_date
536 FROM pa_tasks task
537 ,pa_map_wp_to_fin_tasks_v map_wp_fin
538 WHERE task.task_id = map_wp_fin.mapped_fin_task_id
539 AND map_wp_fin.proj_element_id = p_task_id
540 AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
541 EXCEPTION
542 WHEN NO_DATA_FOUND
543 THEN
544 NULL;
545 END; -- anonymous
546
547 IF ( P_projfunc_cost_rate_date IS NULL )
548 THEN
549 SELECT NVL(proj.projfunc_cost_rate_date,
550 DECODE(imp.default_rate_date_code,
551 'E', P_EI_date, 'P', NULL))
552 INTO P_projfunc_cost_rate_date
553 FROM pa_projects_all proj
554 ,pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
555 WHERE proj.project_id = p_project_id
556 AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
557 END IF;
558 END IF; -- calling_module
559 END IF ;
560
561
562 EXCEPTION
563 WHEN no_data_found THEN
564 P_projfunc_cost_rate_date := NULL ;
565
566 WHEN others THEN
567 P_projfunc_cost_rate_date := NULL ;
568 RAISE ;
569
570 END get_def_projfunc_cst_rate_date ;
571
572 PROCEDURE get_acct_rate_date (
573 P_EI_date IN DATE ,
574 P_acct_rate_date IN OUT NOCOPY DATE )
575 IS
576
577 BEGIN
578
579 -- This procedure derives the Functional currency conversion date
580 -- Logic: If user provides a acct_rate_date, Use it.
581 -- otherwise derive derive using the default_rate_date_code from expenditure
582 -- operating units implementation options. If the default_rate_date_code is E
583 -- then return the expenditure item date(P_EI_date), if default_rate_date_code
584 -- is P then return the PA period ending date.
585
586 SELECT NVL(P_acct_rate_date,DECODE(default_rate_date_code,
587 'E', P_EI_date, 'P',
588 pa_utils2.get_pa_date(P_EI_date,
589 sysdate, org_id))) /**CBGA**/
590 INTO P_acct_rate_date
591 FROM pa_implementations ;
592
593 EXCEPTION
594 WHEN no_data_found THEN
595 P_acct_rate_date := NULL ;
596
597 WHEN others THEN
598 P_acct_rate_date := NULL ;
599 RAISE ;
600
601 END get_acct_rate_date ;
602
603 PROCEDURE get_default_acct_rate_date (
604 P_EI_date IN DATE ,
605 P_acct_rate_date IN OUT NOCOPY DATE )
606 IS
607
608 BEGIN
609
610 -- This procedure derives the Functional currency conversion date
611 -- Logic: If user provides a acct_rate_date, Use it.
612 -- otherwise derive derive using the default_rate_date_code from expenditure
613 -- operating units implementation options. If the default_rate_date_code is E
614 -- then return the expenditure item date(P_EI_date), if default_rate_date_code
615 -- is P then return null.
616
617 SELECT NVL(P_acct_rate_date,DECODE(default_rate_date_code,
618 'E', P_EI_date, 'P',
619 NULL))
620 INTO P_acct_rate_date
621 FROM pa_implementations ;
622
623 EXCEPTION
624
625 WHEN no_data_found THEN
626 P_acct_rate_date := NULL ;
627
628 WHEN others THEN
629 P_acct_rate_date := NULL ;
630 RAISE ;
631
632 END get_default_acct_rate_date ;
633
634 /** The same API is called from Transactions Adjustments and Forecast Items
635 * so new parameters are added to handle the same API when it is called from
636 * Forecast module
637 * The P_calling_module = 'GET_CURR_AMOUNTS' for Transactions
638 * P_calling_module = 'FORECAST' for FIs
639 * P_calling_module = 'WORKPLAN' for Workplan
640 *
641 * Defaulting System_Linkage_Function to 'NER' meaning Not-ER. Special
642 * handling is required only for ER transactions. Hence the above.
643 **/
644 PROCEDURE get_currency_amounts (
645 /** Added the following new params for the FI calls **/
646 P_project_id IN NUMBER DEFAULT NULL,
647 P_exp_org_id IN NUMBER DEFAULT NULL,
648 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS',
649 /** End of FI changes **/
650 P_task_id IN NUMBER,
651 P_EI_date IN DATE,
652 P_denom_raw_cost IN NUMBER,
653 P_denom_curr_code IN VARCHAR2,
654 P_acct_curr_code IN VARCHAR2,
655 P_accounted_flag IN VARCHAR2 DEFAULT 'N',
656 P_acct_rate_date IN OUT NOCOPY DATE,
657 P_acct_rate_type IN OUT NOCOPY VARCHAR2,
658 P_acct_exch_rate IN OUT NOCOPY NUMBER,
659 P_acct_raw_cost IN OUT NOCOPY NUMBER,
660 P_project_curr_code IN VARCHAR2,
661 P_project_rate_type IN OUT NOCOPY VARCHAR2 ,
662 P_project_rate_date IN OUT NOCOPY DATE,
663 P_project_exch_rate IN OUT NOCOPY NUMBER,
664 P_project_raw_cost IN OUT NOCOPY NUMBER,
665 P_projfunc_curr_code IN VARCHAR2,
666 P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 ,
667 P_projfunc_cost_rate_date IN OUT NOCOPY DATE,
668 P_projfunc_cost_exch_rate IN OUT NOCOPY NUMBER,
669 P_projfunc_raw_cost IN OUT NOCOPY NUMBER,
670 P_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE DEFAULT 'NER',
671 P_structure_version_id IN NUMBER DEFAULT NULL,
672 P_status OUT NOCOPY VARCHAR2,
673 P_stage OUT NOCOPY NUMBER,
674 P_Po_Line_ID IN NUMBER DEFAULT NULL) /* Bug : 3535935 */
675 IS
676
677 l_project_currency_code VARCHAR2(15);
678 l_numerator NUMBER;
679 l_denominator NUMBER;
680 V_allow_user_rate_type VARCHAR2(1);
681 l_calling_module VARCHAR2(100);
682
683
684 l_acct_rate_date date;
685 l_acct_rate_type varchar2(100);
686 l_acct_exch_rate number;
687 l_acct_raw_cost number;
688 l_project_rate_type varchar2(100);
689 l_project_rate_date date;
690 l_project_exch_rate number;
691 l_project_raw_cost number;
692 l_projfunc_cost_rate_type varchar2(100);
693 l_projfunc_cost_rate_date date;
694 l_projfunc_cost_exch_rate number;
695 l_projfunc_raw_cost number;
696
697 Begin
698
699 l_acct_rate_date := P_acct_rate_date;
700 l_acct_rate_type := P_acct_rate_type;
701 l_acct_exch_rate := P_acct_exch_rate;
702 l_acct_raw_cost := P_acct_raw_cost;
703 l_project_rate_type := P_project_rate_type;
704 l_project_rate_date := P_project_rate_date;
705 l_project_exch_rate := P_project_exch_rate;
706 l_project_raw_cost := P_project_raw_cost;
707 l_projfunc_cost_rate_type := P_projfunc_cost_rate_type;
708 l_projfunc_cost_rate_date := P_projfunc_cost_rate_date;
709 l_projfunc_cost_exch_rate := P_projfunc_cost_exch_rate;
710 l_projfunc_raw_cost := P_projfunc_cost_exch_rate;
711
712 P_stage := 1 ;
713
714 -- This procedure derives the project and functional amounts
715 -- for unaccounted/accounted transactions.
716 -- For accounted transactions, it derives only the project currency
717 -- amounts. It also derives the currency conversion attributes
718 -- if they are not provided when this procedure is called.
719 -- If project and functional currencies are same, then this
720 -- procedure forces the project and functional currencies conversion
721 -- attributes to be identical by using the logic described in
722 -- procedure get_currency_attributes.
723 --
724
725 IF p_calling_module IS NULL Then
726 l_calling_module := 'GET_CURR_AMOUNTS';
727 ELSE
728 l_calling_module := p_calling_module;
729 End if;
730
731 If l_calling_module = 'FORECAST' then
732 -- Initialize the global record for cache logic
733 initialize_global_rec;
734 else
735 if l_calling_module = 'WORKPLAN' then /* bug 6058074 */
736
737 G_calling_module := 'WORKPLAN'; /* bug 6058074 */
738 End if;
739
740 End if;
741
742 -- print_message('Inisde get_currency amount api IN PARAMS:P_project_id :'||P_project_id );
743 -- print_message('P_exp_org_id: ['||P_exp_org_id);
744 -- print_message('P_calling_module['||l_calling_module);
745 -- print_message('P_task_id['||P_task_id);
746 -- print_message('P_EI_date['||P_EI_date);
747 -- print_message('P_denom_raw_cost['||P_denom_raw_cost);
748 -- print_message('P_system_linkage['||P_system_linkage);
749 -- print_message('P_denom_curr_code['||P_denom_curr_code);
750 -- print_message('P_project_curr_code['||P_project_curr_code);
751 -- print_message('P_acct_curr_code['||P_acct_curr_code);
752 -- print_message('P_accounted_flag['||P_accounted_flag);
753 -- print_message('P_projfunc_curr_code['||P_projfunc_curr_code||']' );
754 -- print_message('p_acct_rate_date ['||p_acct_rate_date||']');
755 -- print_message('p_acct_rate_type ['||p_acct_rate_type ||']');
756 -- print_message('p_acct_exch_rate ['||p_acct_exch_rate ||']');
757 -- print_message('p_project_rate_type ['||p_project_rate_type||']');
758 -- print_message('p_project_exch_rate ['||p_project_exch_rate||']');
759 -- print_message('p_projfunc_cost_rate_date ['||p_projfunc_cost_rate_date||']');
760 -- print_message('p_projfunc_cost_rate_type ['||p_projfunc_cost_rate_type||']');
761 -- print_message('p_projfunc_cost_exch_rate ['||p_projfunc_cost_exch_rate||']');
762
763 print_message(' Calling pa_multi_currency_txn.get_currency_attributes ');
764
765 pa_multi_currency_txn.get_currency_attributes
766 ( P_project_id => P_project_id
767 ,P_exp_org_id => P_exp_org_id
768 ,P_calling_module => l_calling_module
769 ,P_task_id => P_task_id
770 ,P_ei_date => P_ei_date
771 ,P_denom_curr_code => P_denom_curr_code
772 ,P_accounted_flag => P_accounted_flag
773 ,P_acct_curr_code => P_acct_curr_code
774 ,X_acct_rate_date => P_acct_rate_date
775 ,X_acct_rate_type => P_acct_rate_type
776 ,X_acct_exch_rate => P_acct_exch_rate
777 ,P_project_curr_code => P_project_curr_code
778 ,X_project_rate_date => P_project_rate_date
779 ,X_project_rate_type => P_project_rate_type
780 ,X_project_exch_rate => P_project_exch_rate
781 ,P_projfunc_curr_code => P_projfunc_curr_code
782 ,X_projfunc_cost_rate_date => P_projfunc_cost_rate_date
783 ,X_projfunc_cost_rate_type => P_projfunc_cost_rate_type
784 ,X_projfunc_cost_exch_rate => P_projfunc_cost_exch_rate
785 ,P_system_linkage => P_system_linkage
786 ,P_structure_version_id => P_structure_version_id
787 ,X_status => P_status
788 ,X_stage => P_stage
789 );
790
791 print_message('p_acct_rate_date ['||p_acct_rate_date||']');
792 print_message('p_acct_rate_type ['||p_acct_rate_type ||']');
793 print_message('p_acct_exch_rate ['||p_acct_exch_rate ||']');
794 print_message('p_project_rate_date ['||p_project_rate_date||']');
795 print_message('p_project_rate_type ['||p_project_rate_type||']');
796 print_message('p_project_exch_rate ['||p_project_exch_rate||']');
797 print_message('p_projfunc_cost_rate_date ['||p_projfunc_cost_rate_date||']');
798 print_message('p_projfunc_cost_rate_type ['||p_projfunc_cost_rate_type||']');
799 print_message('p_projfunc_cost_exch_rate ['||p_projfunc_cost_exch_rate||']');
800
801 /**********
802 dbms_output.put_line('Values after get currency attributes api'||
803 'p_acct_rate_date ['||p_acct_rate_date||']'||
804 'p_acct_rate_type ['||p_acct_rate_type ||']'||
805 'p_acct_exch_rate ['||p_acct_exch_rate ||']'||
806 'p_project_rate_date ['||p_project_rate_date||']'|| '}');
807 dbms_output.put_line('Values after get currency attributes api'||
808 'p_project_rate_type ['||p_project_rate_type||']'||
809 'p_project_exch_rate ['||p_project_exch_rate||']'||
810 'p_projfunc_cost_rate_date ['||p_projfunc_cost_rate_date||']'||
811 'p_projfunc_cost_rate_type ['||p_projfunc_cost_rate_type||']'||
812 'p_projfunc_cost_exch_rate ['||p_projfunc_cost_exch_rate||']');
813 ***********/
814
815 print_message('End of get_currency_attributes api call ');
816 IF ( P_status IS NOT NULL ) THEN
817 -- Error in get_currency_attributes
818 RETURN;
819 END IF;
820
821 -- Now we have the conversion attributes, derive the
822 -- project raw cost for both accounted and unaccounted txns.
823 -- derive functional raw cost for unaccounted txns.
824
825 IF ( nvl(P_accounted_flag,'N') = 'Y' ) THEN
826 IF ( P_projfunc_curr_code = P_denom_curr_code )
827 THEN
828 P_projfunc_raw_cost := P_denom_raw_cost ;
829 P_projfunc_cost_exch_rate := NULL ;
830 ELSIF ( P_projfunc_curr_code = P_acct_curr_code )
831 THEN
832 /*
833 * If a transaction is accounted - it should have
834 * acct_raw_cost.
835 */
836 P_projfunc_raw_cost := P_acct_raw_cost ;
837 P_projfunc_cost_exch_rate := P_acct_exch_rate ;
838 ELSE
839 <<Calculate_projfunc_raw_cost>>
840 BEGIN
841 --
842 --
843 print_message('Calling convert_amount for projfunc raw cost');
844 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
845 P_to_currency =>P_projfunc_curr_code,
846 P_conversion_date =>P_projfunc_cost_rate_date,
847 P_conversion_type =>P_projfunc_cost_rate_type,
848 P_amount =>P_denom_raw_cost,
849 P_user_validate_flag =>'N',
850 P_handle_exception_flag =>'N',
851 P_converted_amount =>P_projfunc_raw_cost,
852 P_denominator =>l_denominator,
853 P_numerator =>l_numerator,
854 P_rate =>P_projfunc_cost_exch_rate,
855 X_status =>P_status ) ;
856
857 IF ( P_status IS NOT NULL ) THEN
858 -- Error in convert amount
859 RETURN;
860 END IF;
861
862 EXCEPTION
863 WHEN pa_multi_currency.no_rate THEN
864 P_status := 'PA_NO_PROJFUNC_CURR_RATE';
865 RETURN;
866 WHEN pa_Multi_currency.invalid_currency THEN
867 P_status := 'PA_INVALID_PROJFUNC_CURR';
868 RETURN;
869 WHEN others THEN
870 raise;
871 END Calculate_projfunc_raw_cost;
872 END IF ; --P_projfunc_curr_code = P_denom_curr_code
873
874 IF ( P_project_curr_code = P_denom_curr_code )
875 THEN
876 P_project_raw_cost := P_denom_raw_cost ;
877 P_project_exch_rate := NULL ;
878 ELSIF ( P_project_curr_code = P_acct_curr_code )
879 THEN
880 P_project_raw_cost := P_acct_raw_cost ;
881 P_project_exch_rate := P_acct_exch_rate ;
882 ELSIF (P_project_curr_code = P_projfunc_curr_code )
883 THEN
884 P_project_raw_cost := P_projfunc_raw_cost;
885 P_project_exch_rate := P_projfunc_cost_exch_rate;
886 ELSE
887 <<Calculate_project_raw_cost>>
888 BEGIN
889 --
890 --
891 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
892 P_to_currency =>P_project_curr_code,
893 P_conversion_date =>P_project_rate_date,
894 P_conversion_type =>P_project_rate_type,
895 P_amount =>P_denom_raw_cost,
896 P_user_validate_flag =>'N',
897 P_handle_exception_flag =>'N',
898 P_converted_amount =>P_project_raw_cost,
899 P_denominator =>l_denominator,
900 P_numerator =>l_numerator,
901 P_rate =>P_project_exch_rate,
902 X_status =>P_status ) ;
903
904 IF ( P_status IS NOT NULL ) THEN
905 -- Error in convert amount
906 RETURN;
907 END IF;
908
909 EXCEPTION
910 WHEN pa_multi_currency.no_rate THEN
911 P_status := 'PA_NO_PROJECT_CURR_RATE';
912 RETURN;
913 WHEN pa_Multi_currency.invalid_currency THEN
914 P_status := 'PA_INVALID_PROJ_CURR';
915 RETURN;
916 WHEN others THEN
917 raise;
918 END Calculate_project_raw_cost;
919
920 END IF; -- P_project_curr_code = P_denom_curr_code
921
922 ELSE -- P_accounted_flag = 'N'
923 /* EPP */
924 IF ( P_system_linkage = 'ER' )
925 THEN
926 IF ( P_acct_curr_code = P_denom_curr_code )
927 THEN
928 P_acct_raw_cost := P_denom_raw_cost ;
929 P_acct_exch_rate := NULL ;
930 ELSE
931 -- derive P_acct_raw_cost ;
932 <<Calculate_acct_raw_cost>>
933 BEGIN
934
935 print_message('Calculate_acct_raw_cost');
936 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
937 P_to_currency =>P_acct_curr_code,
938 P_conversion_date =>P_acct_rate_date,
939 P_conversion_type =>P_acct_rate_type,
940 P_amount =>P_denom_raw_cost,
941 P_user_validate_flag =>'N',
942 P_handle_exception_flag =>'N',
943 P_converted_amount =>P_acct_raw_cost,
944 P_denominator =>l_denominator,
945 P_numerator =>l_numerator,
946 P_rate =>P_acct_exch_rate,
947 X_status =>P_status ) ;
948
949 IF ( P_status IS NOT NULL ) THEN
950 -- Error in convert amount
951 RETURN;
952 END IF;
953
954 EXCEPTION
955 WHEN pa_multi_currency.no_rate THEN
956 P_status := 'PA_NO_ACCT_CURR_RATE';
957 RETURN;
958 WHEN pa_multi_currency.invalid_currency THEN
959 P_status := 'PA_INVALID_ACCT_CURR';
960 RETURN;
961 WHEN others THEN
962 raise;
963 END Calculate_acct_raw_cost;
964 END IF; --P_acct_curr_code = P_denom_curr_code
965
966 IF ( P_projfunc_curr_code = P_denom_curr_code )
967 THEN
968 P_projfunc_raw_cost := P_denom_raw_cost ;
969 P_projfunc_cost_exch_rate := NULL ;
970 ELSIF ( P_projfunc_curr_code = P_acct_curr_code )
971 THEN
972 P_projfunc_raw_cost := P_acct_raw_cost ;
973 P_projfunc_cost_exch_rate := P_acct_exch_rate ;
974 ELSE
975 -- derive P_projfunc_raw_cost ;
976 <<Calculate_projfunc_raw_cost>>
977 BEGIN
978 --
979 --
980 print_message('Calculate_projfunc_raw_cost');
981 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
982 P_to_currency =>P_projfunc_curr_code,
983 P_conversion_date =>P_projfunc_cost_rate_date,
984 P_conversion_type =>P_projfunc_cost_rate_type,
985 P_amount =>P_denom_raw_cost,
986 P_user_validate_flag =>'N',
987 P_handle_exception_flag =>'N',
988 P_converted_amount =>P_projfunc_raw_cost,
989 P_denominator =>l_denominator,
990 P_numerator =>l_numerator,
991 P_rate =>P_projfunc_cost_exch_rate,
992 X_status =>P_status ) ;
993
994 IF ( P_status IS NOT NULL ) THEN
995 -- Error in convert amount
996 RETURN;
997 END IF;
998
999 EXCEPTION
1000 WHEN pa_multi_currency.no_rate THEN
1001 P_status := 'PA_NO_PROJFUNC_CURR_RATE';
1002 RETURN;
1003 WHEN pa_Multi_currency.invalid_currency THEN
1004 P_status := 'PA_INVALID_PROJFUNC_CURR';
1005 RETURN;
1006 WHEN others THEN
1007 raise;
1008 END Calculate_projfunc_raw_cost;
1009
1010 END IF; --P_projfunc_curr_code = P_denom_curr_code
1011
1012 IF ( P_project_curr_code = P_denom_curr_code )
1013 THEN
1014 P_project_raw_cost := P_denom_raw_cost ;
1015 P_project_exch_rate := NULL ;
1016 ELSIF ( P_project_curr_code = P_acct_curr_code )
1017 THEN
1018 P_project_raw_cost := P_acct_raw_cost ;
1019 P_project_exch_rate := P_acct_exch_rate ;
1020 ELSIF ( P_project_curr_code = P_projfunc_curr_code )
1021 THEN
1022 P_project_raw_cost := P_projfunc_raw_cost ;
1023 P_project_exch_rate := P_projfunc_cost_exch_rate ;
1024 ELSE
1025 -- derive P_project_raw_cost ;
1026 <<Calculate_project_raw_cost>>
1027 BEGIN
1028 --
1029 --
1030 print_message('Calculate_project_raw_cost');
1031 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
1032 P_to_currency =>P_project_curr_code,
1033 P_conversion_date =>P_project_rate_date,
1034 P_conversion_type =>P_project_rate_type,
1035 P_amount =>P_denom_raw_cost,
1036 P_user_validate_flag =>'N',
1037 P_handle_exception_flag =>'N',
1038 P_converted_amount =>P_project_raw_cost,
1039 P_denominator =>l_denominator,
1040 P_numerator =>l_numerator,
1041 P_rate =>P_project_exch_rate,
1042 X_status =>P_status ) ;
1043
1044 IF ( P_status IS NOT NULL ) THEN
1045 -- Error in convert amount
1046 RETURN;
1047 END IF;
1048
1049 EXCEPTION
1050 WHEN pa_multi_currency.no_rate THEN
1051 P_status := 'PA_NO_PROJECT_CURR_RATE';
1052 RETURN;
1053 WHEN pa_Multi_currency.invalid_currency THEN
1054 P_status := 'PA_INVALID_PROJ_CURR';
1055 RETURN;
1056 WHEN others THEN
1057 raise;
1058 END Calculate_project_raw_cost;
1059
1060 END IF; -- P_project_curr_code = P_denom_curr_code
1061 END IF; -- P_system_linkage = 'ER'
1062
1063 IF ( P_system_linkage <> 'ER' )
1064 THEN
1065 IF( P_projfunc_curr_code = P_denom_curr_code )
1066 THEN
1067 P_projfunc_raw_cost := P_denom_raw_cost ;
1068 P_projfunc_cost_exch_rate := NULL ;
1069 ELSE
1070 -- derive P_projfunc_raw_cost ;
1071 <<Calculate_projfunc_raw_cost>>
1072 BEGIN
1073 --
1074 --
1075 --dbms_output.put_line('Calculate_projfunc_raw_cost for <> ER');
1076
1077 print_message('Before Call to Calculate_acct_raw_cost');
1078
1079 -- print_message('P_denom_raw_cost ['||P_denom_raw_cost||']');
1080 -- print_message('P_projfunc_raw_cost ['||P_projfunc_raw_cost||']');
1081 -- print_message('p_projfunc_cost_rate_date ['||p_projfunc_cost_rate_date||']');
1082 -- print_message('p_projfunc_cost_rate_type ['||p_projfunc_cost_rate_type||']');
1083 -- print_message('p_projfunc_cost_exch_rate ['||p_projfunc_cost_exch_rate||']');
1084
1085 -- print_message('Calculate_projfunc_raw_cost for <> ER');
1086
1087 /* S.N. Bug 3535935 : Typical Case of CWK. Where all currency attributes are derived from PO */
1088
1089 IF ( P_PO_Line_ID IS NOT NULL)
1090 And ( P_acct_curr_code = P_projfunc_curr_code )
1091 /* Bug 3889122 : Calculate only if Non Cross Charnge Txn as in CWK NCC, the PFC should be from PO and FC also */
1092 THEN
1093
1094 P_projfunc_raw_cost := PA_CURRENCY.round_trans_currency_amt
1095 (P_denom_raw_cost * P_projfunc_cost_exch_rate, P_projfunc_curr_code) ;
1096 l_denominator := 1 ;
1097 l_numerator := P_projfunc_cost_exch_rate ;
1098 ELSE
1099
1100 /* E.N. Bug 3535935 : Typical Case of CWK. Where all currency attributes are derived from PO */
1101
1102 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
1103 P_to_currency =>P_projfunc_curr_code,
1104 P_conversion_date =>P_projfunc_cost_rate_date,
1105 P_conversion_type =>P_projfunc_cost_rate_type,
1106 P_amount =>P_denom_raw_cost,
1107 P_user_validate_flag =>'N',
1108 P_handle_exception_flag =>'N',
1109 P_converted_amount =>P_projfunc_raw_cost,
1110 P_denominator =>l_denominator,
1111 P_numerator =>l_numerator,
1112 P_rate =>P_projfunc_cost_exch_rate,
1113 X_status =>P_status ) ;
1114 --dbms_output.put_line('pfrc = ['|| to_char(P_projfunc_raw_cost) || ']');
1115
1116 /* S.N. Bug 3535935 : Typical Case of CWK. Where all currency attributes are derived from PO */
1117 END IF;
1118 /* E.N. Bug 3535935 : Typical Case of CWK. Where all currency attributes are derived from PO */
1119
1120 print_message('After Call to Calculate_acct_raw_cost');
1121
1122 -- print_message('P_denom_raw_cost ['||P_denom_raw_cost||']');
1123 -- print_message('P_projfunc_raw_cost ['||P_projfunc_raw_cost||']');
1124 -- print_message('p_projfunc_cost_rate_date ['||p_projfunc_cost_rate_date||']');
1125 -- print_message('p_projfunc_cost_rate_type ['||p_projfunc_cost_rate_type||']');
1126 -- print_message('p_projfunc_cost_exch_rate ['||p_projfunc_cost_exch_rate||']');
1127
1128 IF ( P_status IS NOT NULL ) THEN
1129 -- Error in convert amount
1130 --dbms_output.put_line('Error in convert amount');
1131 RETURN;
1132 END IF;
1133
1134 EXCEPTION
1135 WHEN pa_multi_currency.no_rate THEN
1136 print_message('Exception Raised in GL currency api');
1137 --dbms_output.put_line('Exception Raised in GL currency api');
1138 P_status := 'PA_NO_PROJFUNC_CURR_RATE';
1139 RETURN;
1140 WHEN pa_Multi_currency.invalid_currency THEN
1141 --dbms_output.put_line('PA_INVALID_PROJFUNC_CURR');
1142 P_status := 'PA_INVALID_PROJFUNC_CURR';
1143 RETURN;
1144 WHEN others THEN
1145 raise;
1146 END Calculate_projfunc_raw_cost;
1147 END IF; --P_projfunc_curr_code = P_denom_curr_code
1148
1149 IF ( P_acct_curr_code = P_denom_curr_code )
1150 THEN
1151 P_acct_raw_cost := P_denom_raw_cost ;
1152 P_acct_exch_rate := NULL ;
1153 ELSIF ( P_acct_curr_code = P_projfunc_curr_code )
1154 THEN
1155 P_acct_raw_cost := P_projfunc_raw_cost ;
1156 P_acct_exch_rate := P_projfunc_cost_exch_rate ;
1157 ELSE
1158 -- derive P_acct_raw_cost ;
1159 If P_PO_Line_ID Is Null Then
1160 /* Bug 3889122 Calculate FC from GL rates if it is non-cwk transaction */
1161 <<Calculate_acct_raw_cost>>
1162 BEGIN
1163 print_message('Calculate_acct_raw_cost for <> ER');
1164 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
1165 P_to_currency =>P_acct_curr_code,
1166 P_conversion_date =>P_acct_rate_date,
1167 P_conversion_type =>P_acct_rate_type,
1168 P_amount =>P_denom_raw_cost,
1169 P_user_validate_flag =>'N',
1170 P_handle_exception_flag =>'N',
1171 P_converted_amount =>P_acct_raw_cost,
1172 P_denominator =>l_denominator,
1173 P_numerator =>l_numerator,
1174 P_rate =>P_acct_exch_rate,
1175 X_status =>P_status ) ;
1176
1177 IF ( P_status IS NOT NULL ) THEN
1178 -- Error in convert amount
1179 RETURN;
1180 END IF;
1181
1182 EXCEPTION
1183 WHEN pa_multi_currency.no_rate THEN
1184 P_status := 'PA_NO_ACCT_CURR_RATE';
1185 RETURN;
1186 WHEN pa_multi_currency.invalid_currency THEN
1187 P_status := 'PA_INVALID_ACCT_CURR';
1188 RETURN;
1189 WHEN others THEN
1190 raise;
1191 END Calculate_acct_raw_cost;
1192 Else
1193
1194 /* Bug 3889122 : Calculate FC From PO for CWK transaction */
1195 P_acct_raw_cost := PA_CURRENCY.round_trans_currency_amt
1196 (P_denom_raw_cost * P_acct_exch_rate, P_acct_curr_code) ;
1197 l_denominator := 1 ;
1198 l_numerator := P_acct_exch_rate ;
1199
1200 End If;
1201
1202 END IF; --P_acct_curr_code = P_denom_curr_code
1203
1204 IF ( P_project_curr_code = P_denom_curr_code )
1205 THEN
1206 P_project_raw_cost := P_denom_raw_cost ;
1207 P_project_exch_rate := NULL ;
1208 ELSIF ( P_project_curr_code = P_projfunc_curr_code )
1209 THEN
1210 P_project_raw_cost := P_projfunc_raw_cost ;
1211 P_project_exch_rate := P_projfunc_cost_exch_rate ;
1212 ELSIF ( P_project_curr_code = P_acct_curr_code )
1213 THEN
1214 P_project_raw_cost := P_acct_raw_cost ;
1215 P_project_exch_rate := P_acct_exch_rate ;
1216 ELSE
1217 -- derive P_project_raw_cost ;
1218 <<Calculate_project_raw_cost>>
1219 BEGIN
1220 --
1221 --
1222 --dbms_output.put_line('Calculate_project_raw_cost for <> ER');
1223 print_message('Calculate_project_raw_cost for <> ER');
1224 pa_multi_currency.convert_amount( P_from_currency =>P_denom_curr_code,
1225 P_to_currency =>P_project_curr_code,
1226 P_conversion_date =>P_project_rate_date,
1227 P_conversion_type =>P_project_rate_type,
1228 P_amount =>P_denom_raw_cost,
1229 P_user_validate_flag =>'N',
1230 P_handle_exception_flag =>'N',
1231 P_converted_amount =>P_project_raw_cost,
1232 P_denominator =>l_denominator,
1233 P_numerator =>l_numerator,
1234 P_rate =>P_project_exch_rate,
1235 X_status =>P_status ) ;
1236 --dbms_output.put_line('P_project_raw_cost = [' || to_char(P_project_raw_cost) || ']');
1237
1238 IF ( P_status IS NOT NULL ) THEN
1239 -- Error in convert amount
1240 --dbms_output.put_line('Error in convert amount');
1241 RETURN;
1242 END IF;
1243
1244 EXCEPTION
1245 WHEN pa_multi_currency.no_rate THEN
1246 --dbms_output.put_line('PA_NO_PROJECT_CURR_RATE');
1247 P_status := 'PA_NO_PROJECT_CURR_RATE';
1248 RETURN;
1249 WHEN pa_Multi_currency.invalid_currency THEN
1250 --dbms_output.put_line('PA_INVALID_PROJ_CURR');
1251 P_status := 'PA_INVALID_PROJ_CURR';
1252 RETURN;
1253 WHEN others THEN
1254 raise;
1255 END Calculate_project_raw_cost;
1256
1257 END IF; --P_project_curr_code = P_denom_currency_code
1258 END IF; -- P_system_linkage <> 'ER'
1259 /* EPP */
1260
1261 END IF; -- End p_accounted_flag = 'Y'
1262 print_message('Converted Amounts are: P_denom_raw_cost ['||P_denom_raw_cost||']P_acct_raw_cost['||
1263 P_acct_raw_cost||']P_project_raw_cost['||P_project_raw_cost||']P_projfunc_raw_cost['||
1264 P_projfunc_raw_cost ||']' );
1265 print_message('end of get currency amounts');
1266
1267 --dbms_output.put_line('from gca prd is [' || to_char(P_project_rate_date) || ']');
1268 --dbms_output.put_line('from gca pfrd is [' || to_char(P_projfunc_cost_rate_date) || ']');
1269
1270
1271 if l_calling_module = 'WORKPLAN' then /* bug 6058074 */
1272 G_calling_module := NULL; /* bug 6058074 */
1273 end if;
1274
1275 EXCEPTION
1276 WHEN others THEN
1277 P_acct_rate_date := l_acct_rate_date;
1278 P_acct_rate_type := l_acct_rate_type;
1279 P_acct_exch_rate := l_acct_exch_rate;
1280 P_acct_raw_cost := l_acct_raw_cost;
1281 P_project_rate_type := l_project_rate_type;
1282 P_project_rate_date := l_project_rate_date;
1283 P_project_exch_rate := l_project_exch_rate;
1284 P_project_raw_cost := l_project_raw_cost;
1285 P_projfunc_cost_rate_type := l_projfunc_cost_rate_type;
1286 P_projfunc_cost_rate_date := l_projfunc_cost_rate_date;
1287 P_projfunc_cost_exch_rate := l_projfunc_cost_exch_rate;
1288 P_projfunc_raw_cost := l_projfunc_cost_exch_rate;
1289 RAISE ;
1290
1291 END get_currency_amounts ;
1292
1293 PROCEDURE Perform_MC_and_IC_processing(
1294 P_Sys_Link IN VARCHAR2,
1295 P_Request_Id IN NUMBER,
1296 P_Source OUT NOCOPY VARCHAR2,
1297 P_MC_IC_status OUT NOCOPY NUMBER,
1298 P_Update_Count OUT NOCOPY NUMBER)
1299 IS
1300
1301 /*
1302 * Variable Declarations
1303 */
1304
1305 V_loop_index NUMBER := 1;
1306 V_acct_raw_cost PA_EXPENDITURE_ITEMS.ACCT_RAW_COST%TYPE;
1307 V_acct_rate_date PA_EXPENDITURE_ITEMS.ACCT_RATE_DATE%TYPE;
1308 V_acct_rate_type PA_EXPENDITURE_ITEMS.ACCT_RATE_TYPE%TYPE;
1309 V_acct_exchange_rate PA_EXPENDITURE_ITEMS.ACCT_EXCHANGE_RATE%TYPE;
1310 V_projfunc_raw_cost PA_EXPENDITURE_ITEMS.RAW_COST%TYPE;
1311 V_projfunc_cost_rate_date PA_EXPENDITURE_ITEMS.projfunc_cost_rate_DATE%TYPE;
1312 V_projfunc_cost_rate_type PA_EXPENDITURE_ITEMS.projfunc_cost_rate_TYPE%TYPE;
1313 V_projfunc_cost_exchange_rate PA_EXPENDITURE_ITEMS.projfunc_cost_exchANGE_RATE%TYPE;
1314 V_project_raw_cost PA_EXPENDITURE_ITEMS.RAW_COST%TYPE;
1315 V_project_rate_date PA_EXPENDITURE_ITEMS.PROJECT_RATE_DATE%TYPE;
1316 V_project_rate_type PA_EXPENDITURE_ITEMS.PROJECT_RATE_TYPE%TYPE;
1317 V_project_exchange_rate PA_EXPENDITURE_ITEMS.PROJECT_EXCHANGE_RATE%TYPE;
1318 V_system_linkage PA_EXPENDITURE_ITEMS.SYSTEM_LINKAGE_FUNCTION%TYPE;
1319 V_status VARCHAR2(150);
1320 V_stage NUMBER;
1321 V_denominator NUMBER;
1322 V_numerator NUMBER;
1323 V_cur_status NUMBER;
1324 V_errorstage VARCHAR2(150);
1325 V_errorcode NUMBER;
1326 V_related_Item VARCHAR2(1);
1327 E_local_exception Exception;
1328 l_debug_mode VARCHAR2(1);
1329
1330 /*
1331 * 2048868
1332 */
1333 l_er_expenditure_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1334 l_exp_acct_exch_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1335 l_exp_already_exists VARCHAR2(1) := 'N' ;
1336 l_er_exp_count NUMBER := 1 ;
1337
1338 l_cc_dist_count_tab PA_PLSQL_DATATYPES.NumTabTyp; /* added for bug#2919885 */
1339
1340 /** CBGA Table Declaration.... to be removed later...
1341 ** will be declared in PA_Cross_Business_Grp
1342 ** Will be uncommented when rates-model is decided.
1343 ** JobIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1344 ** JobGroupIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1345 ** CostJobIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1346 ** V_status_code VARCHAR2(150);
1347 ** ErrorStageTab VARCHAR2(150);
1348 ** ErrorCodeTab NUMBER;
1349 **/
1350
1351 /*
1352 * This cursor is used to get all the Expenditure Item records
1353 * Which are being processed by the Cost distribute program.
1354 * For getting such records, we rely on cost_distributed_flag to be 'S'
1355 * and use the request_id as passed by the distribute program.
1356 * This cursor picks up all columns required by the MC/IC APIs
1357 * and Client Extn. for creating related items. ( in case of labor )
1358 * The parameter to this cursor is used to process regular and related items
1359 * separately. This is required to take care of the adjustment of a regular
1360 * item which has got some related items. In this case, we should not process
1361 * regular and its related items in the same pass since the cost of the related
1362 * item is calculated only in the client extension of the regular item
1363 *
1364 * Note: denom_currency_code should not be null under normal circumstances.
1365 * The check is included for safety purpose only.
1366 * If denom_currency_code is null then acct_currency_code is used.
1367 *
1368 * Expenditure organization is passed as incurred_by_organization_id of
1369 * expenditure if override_to_organization_id in expenditure item
1370 * doesn't exist. The incurred_by_organization_id value is passed from the
1371 * cost distribute program.
1372 *
1373 * Note: Special processing is done for expenditure items of type
1374 * 'BTC'. For These type of Txns, the conversion of burden cost
1375 * in Functional and Project currencies are carried out exactly in the
1376 * similar manner in which the corresponding conversions are done for raw
1377 * cost in case of Txns of other type.
1378 */
1379
1380 CURSOR expenditure_item_cursor(l_related_item VARCHAR2) is
1381 SELECT
1382 ITEM.expenditure_item_id,
1383 ITEM.expenditure_item_date,
1384 ITEM.Task_Id,
1385 EXP.expenditure_id,
1386 NVL(ITEM.Denom_Currency_Code,ITEM.Acct_Currency_Code) Denom_Currency_Code,
1387 DECODE(ITEM.System_Linkage_Function,'BTC',
1388 ITEM.Denom_Burdened_Cost,ITEM.Denom_Raw_Cost) Denom_Raw_Cost,
1389 ITEM.Acct_Raw_Cost,
1390 ITEM.Acct_Currency_Code,
1391 DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Rate_Date, ITEM.Acct_Rate_Date) Acct_Rate_Date,
1392 DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Rate_Type, ITEM.Acct_Rate_Type) Acct_Rate_Type,
1393 DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Exchange_Rate, ITEM.Acct_Exchange_Rate) Acct_Exchange_Rate,
1394 ITEM.Raw_Cost,
1395 ITEM.Projfunc_Currency_Code,
1396 ITEM.Projfunc_Cost_Rate_Date,
1397 ITEM.Projfunc_Cost_Rate_Type,
1398 ITEM.Projfunc_Cost_Exchange_Rate,
1399 ITEM.Project_Raw_Cost,
1400 ITEM.Project_Currency_Code,
1401 ITEM.Project_Rate_Date,
1402 ITEM.Project_Rate_Type,
1403 ITEM.Project_Exchange_Rate,
1404 ITEM.Source_Expenditure_Item_ID Source_Id,
1405 ITEM.Net_Zero_Adjustment_Flag Net_zero,
1406 ITEM.org_id,
1407 ITEM.expenditure_type,
1408 ITEM.system_linkage_function,
1409 ITEM.transaction_source,
1410 TXN.GL_Accounted_Flag, /* Bug #1824407 */
1411 NVL(ITEM.override_to_organization_id,EXP.incurred_by_organization_id) exp_organization_id,
1412 ITEM.Organization_Id nlr_organization_id,
1413 EXP.incurred_by_person_id,
1414 ITEM.Cc_Cross_Charge_Type,
1415 ITEM.Cc_Cross_Charge_Code,
1416 ITEM.Cc_Prvdr_Organization_Id,
1417 ITEM.Cc_Recvr_Organization_Id,
1418 ITEM.Recvr_Org_Id
1419 ,ITEM.PO_Line_Id --3535935 hkulkarn
1420 /**CBGA select job_id and project_group_id using API GetProjectGroupId().
1421 ** To be uncommented after decing upon rates-model.
1422 ** ITEM.Job_Id,
1423 ** ITEM.Cost_Job_Id,
1424 ** PA_Cross_Business_Grp.GetProjectGroupId(TASK.Project_Id, 'C') job_group_id
1425 **/
1426 FROM PA_Expenditure_Items ITEM,
1427 PA_Expenditures EXP,
1428 PA_Transaction_Sources TXN /* Bug 1824407 */
1429 /** To be uncommented after decing upon rates-model.
1430 ** CBGA Join pa_tasks with pa_expenditure_items_all.
1431 ** PA_Tasks TASK
1432 **/
1433 WHERE ITEM.Cost_Distributed_Flag = 'S'
1434 AND ITEM.Cost_Dist_Rejection_Code IS NULL
1435 AND ITEM.Request_id = P_request_id
1436 AND ITEM.expenditure_id = EXP.expenditure_id
1437 AND ITEM.Transaction_Source = TXN.Transaction_Source (+)/*Bug1824407*/
1438 AND (( ITEM.Source_Expenditure_Item_Id IS NULL
1439 AND l_related_item = 'N')
1440 OR
1441 ( ITEM.Source_Expenditure_Item_Id IS NOT NULL
1442 AND l_related_item = 'Y'))
1443 /** To be uncommented after decing upon rates-model.
1444 ** CBGA Joining pa_tasks with EIs.
1445 ** AND ITEM.task_id = TASK.task_id
1446 **/
1447 ;
1448 /******
1449 * Right now, I cant find out any reason to put this order by,
1450 * if necessary, i will come back and change this.
1451 ORDER BY ITEM.Expenditure_Item_Id;
1452 ******/
1453 BEGIN
1454 if pa_cc_utils.g_debug_mode then
1455 l_debug_mode := 'Y';
1456 else
1457 l_debug_mode := 'N';
1458 end if;
1459 pa_debug.set_process(
1460 x_process => 'PLSQL',
1461 x_debug_mode => l_debug_mode);
1462 pa_cc_utils.set_curr_function('Perform_MC_and_IC_processing');
1463 IF P_DEBUG_MODE THEN
1464 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Start ');
1465 END IF;
1466 P_MC_IC_status := 0;
1467 P_update_count := 0;
1468
1469 /*
1470 * The init procedure is called to set the global variables
1471 * to be used by the MC API
1472 */
1473 IF P_DEBUG_MODE THEN
1474 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Call to PA_MULTI_CURRENCY.INIT');
1475 END IF;
1476 PA_MULTI_CURRENCY.INIT;
1477 IF P_DEBUG_MODE THEN
1478 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Call to PA_MULTI_CURRENCY.INIT');
1479 END IF;
1480 /*
1481 * Loop through all expenditure items and set the required
1482 * Parameter tables with the appropriate values.
1483 * There is an outer loop of two passes, in the first pass all
1484 * regular items are processed and in the next all related items are
1485 * processed. This is done to take care of the case of adjustment
1486 * of a regular item containing related items. ( in this case, the
1487 * cost of the related items is calculated only during the client extension
1488 * call of the regular item and hence we shouldnot fetch the regular items
1489 * and related items together; otherwise the calculated cost wont be visible to the
1490 * cursor )
1491 *
1492 * We call the MC API within this loop because it doesnt accept
1493 * array parameters currently. The output values are stored into arrays
1494 * for later use in the update. The IC API is called outside this loop
1495 * since array parameters are accepted by it. The final update
1496 * takes care of both MC and IC.
1497 */
1498
1499 FOR loop_control in 1 .. 2
1500 LOOP
1501 IF P_DEBUG_MODE THEN
1502 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'In outer Loop');
1503 END IF;
1504 IF ( loop_control = 1 ) THEN
1505 V_Related_Item := 'N';
1506 ELSE
1507 V_Related_Item := 'Y';
1508 END IF;
1509 FOR expenditure_item_rec in expenditure_item_cursor(V_Related_Item)
1510 LOOP
1511 IF P_DEBUG_MODE THEN
1512 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'In related Items Loop');
1513 END IF;
1514
1515 P_Source := 'Assignment';
1516 /*
1517 * Set the array variables with the appropriate values.
1518 * Project Org ID is populated with the recvr_org_id value available in EI
1519 * so that the Identification process doesnt update this value.
1520 */
1521 IF P_DEBUG_MODE THEN
1522 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before setting Array variables');
1523 END IF;
1524
1525 PA_CC_IDENT.ProjectIdTab(v_loop_index) := NULL;
1526 PA_CC_IDENT.PrjOrganizationIdTab(v_loop_index) := NULL;
1527 PA_CC_IDENT.PrjOrgIdTab(v_loop_index) := expenditure_item_rec.recvr_org_id;
1528 PA_CC_IDENT.PrvdrLEIdTab(v_loop_index) := NULL;
1529 PA_CC_IDENT.RecvrLEIdTab(v_loop_index) := NULL;
1530 PA_CC_IDENT.ExpItemDateTab(v_loop_index) := expenditure_item_rec.expenditure_item_date;
1531 PA_CC_IDENT.TaskIdTab(v_loop_index) := expenditure_item_rec.task_id;
1532 PA_CC_IDENT.ExpItemIdTab(v_loop_index) := expenditure_item_rec.expenditure_item_id;
1533 PA_CC_IDENT.ExpOrgIdTab(v_loop_index) := expenditure_item_rec.org_id;
1534 PA_CC_IDENT.ExpTypeTab(v_loop_index) := expenditure_item_rec.expenditure_type;
1535 PA_CC_IDENT.SysLinkTab(v_loop_index) := expenditure_item_rec.system_linkage_function;
1536 PA_CC_IDENT.TransSourceTab(v_loop_index) := expenditure_item_rec.transaction_source;
1537 PA_CC_IDENT.ExpOrganizationIdTab(v_loop_index) := expenditure_item_rec.exp_organization_id;
1538 PA_CC_IDENT.NLROrganizationIdTab(v_loop_index) := expenditure_item_rec.nlr_organization_id;
1539 PA_CC_IDENT.PersonIdTab(v_loop_index) := expenditure_item_rec.incurred_by_person_id;
1540 PA_CC_IDENT.CrossChargeTypeTab(v_loop_index) := expenditure_item_rec.cc_cross_charge_type;
1541 PA_CC_IDENT.CrossChargeCodeTab(v_loop_index) := expenditure_item_rec.cc_cross_charge_code;
1542 PA_CC_IDENT.PrvdrOrganizationIdTab(v_loop_index):= expenditure_item_rec.cc_prvdr_organization_id;
1543 PA_CC_IDENT.RecvrOrganizationIdTab(v_loop_index):= expenditure_item_rec.cc_recvr_organization_id;
1544 PA_CC_IDENT.RecvrOrgIdTab(v_loop_index) := expenditure_item_rec.recvr_org_id;
1545 V_acct_rate_date := expenditure_item_rec.acct_rate_date;
1546 V_acct_rate_type := expenditure_item_rec.acct_rate_type;
1547 V_acct_exchange_rate := expenditure_item_rec.acct_exchange_rate;
1548 V_projfunc_cost_rate_type := expenditure_item_rec.projfunc_cost_rate_type;
1549 V_projfunc_cost_rate_date := expenditure_item_rec.projfunc_cost_rate_date;
1550 V_projfunc_cost_exchange_rate := expenditure_item_rec.projfunc_cost_exchange_rate;
1551 V_project_rate_type := expenditure_item_rec.project_rate_type;
1552 V_project_rate_date := expenditure_item_rec.project_rate_date;
1553 V_project_exchange_rate := expenditure_item_rec.project_exchange_rate;
1554 V_status := NULL; -- Bug 4142911
1555
1556 /** To be uncommented after decing upon rates-model.
1557 ** CBGA The array variables for GetMappedToJobs () are populated here.
1558 **
1559 ** JobIdTab (v_loop_index) := expenditure_item_rec.job_id;
1560 ** JobGroupIdTab(v_loop_index) := expenditure_item_rec.job_group_id;
1561 ** CostJobIdTab (v_loop_index) := expenditure_item_rec.cost_job_id;
1562 ** V_status_code := NULL; -- x_status_code
1563 ** ErrorStageTab(v_loop_index) := NULL; -- x_error_stage_tab
1564 ** ErrorCodeTab (v_loop_index) := NULL; -- x_error_code_tab
1565 **
1566 **/
1567 IF P_DEBUG_MODE THEN
1568 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After setting Array variables');
1569 END IF;
1570
1571 /*
1572 * MC API is to be called ( for getting Acct/Project raw cost )
1573 * only when any of the three buckets are empty.
1574 */
1575 IF ( expenditure_item_rec.acct_raw_cost IS NULL
1576 OR
1577 expenditure_item_rec.raw_cost IS NULL
1578 OR
1579 expenditure_item_rec.project_raw_cost IS NULL) THEN
1580 /*
1581 * Set the source. Its value is used by the calling program
1582 * only when the status <> 0
1583 */
1584 P_Source := 'MC Error';
1585 IF P_DEBUG_MODE THEN
1586 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Call to PA_MULTI_CURRENCY_TXN.GET_CURRENCY_AMOUNTS');
1587 END IF;
1588 If nvl(expenditure_item_rec.GL_Accounted_Flag,'N') = 'Y' THEN
1589
1590 V_acct_raw_cost := expenditure_item_rec.acct_raw_cost ;
1591 V_acct_rate_date := expenditure_item_rec.acct_rate_date ;
1592 V_acct_exchange_rate := expenditure_item_rec.acct_exchange_rate ;
1593 V_acct_rate_type := expenditure_item_rec.acct_rate_type ;
1594 End If;
1595
1596 IF expenditure_item_rec.Po_Line_Id IS NOT NULL THEN --3535935 hkulkarn
1597
1598 V_project_rate_date := V_acct_rate_date;
1599 V_project_rate_type := V_acct_rate_type;
1600 V_project_exchange_rate := V_acct_exchange_rate;
1601
1602 V_projfunc_cost_rate_date := V_acct_rate_date;
1603 V_projfunc_cost_rate_type := V_acct_rate_type;
1604 V_projfunc_cost_exchange_rate := V_acct_exchange_rate;
1605
1606 -- print_message('P_denom_curr_code['||expenditure_item_rec.denom_currency_code);
1607 -- print_message('P_project_curr_code['||expenditure_item_rec.project_currency_code);
1608 -- print_message('P_acct_curr_code['||expenditure_item_rec.acct_currency_code);
1609 -- print_message('P_accounted_flag['||nvl(expenditure_item_rec.GL_Accounted_Flag,'N'));
1610 -- print_message('P_projfunc_curr_code['||expenditure_item_rec.projfunc_currency_code||']' );
1611 -- print_message('p_acct_rate_date ['||V_acct_rate_date||']');
1612 -- print_message('p_acct_rate_type ['||V_acct_rate_type ||']');
1613 -- print_message('p_acct_exch_rate ['||V_acct_exchange_rate ||']');
1614 -- print_message('p_project_rate_type ['||V_project_rate_type||']');
1615 -- print_message('p_project_exch_rate ['||V_project_rate_date||']');
1616 -- print_message('p_projfunc_cost_rate_date ['||V_projfunc_cost_rate_date||']');
1617 -- print_message('p_projfunc_cost_rate_type ['||V_projfunc_cost_rate_type||']');
1618 -- print_message('p_projfunc_cost_exch_rate ['||V_projfunc_cost_exchange_rate||']');
1619
1620
1621 END IF ; --3535935 hkulkarn
1622
1623 PA_MULTI_CURRENCY_TXN.GET_CURRENCY_AMOUNTS(
1624 p_project_id => null,
1625 p_exp_org_id => null,
1626 p_calling_module => null,
1627 P_task_id => expenditure_item_rec.task_id,
1628 P_Ei_date => expenditure_item_rec.expenditure_item_date,
1629 P_denom_raw_cost => expenditure_item_rec.denom_raw_cost,
1630 P_denom_curr_code => expenditure_item_rec.denom_currency_code,
1631 P_acct_curr_code => expenditure_item_rec.acct_currency_code,
1632 P_accounted_flag => nvl(expenditure_item_rec.GL_Accounted_Flag,'N'), /*Bug 1824407 */
1633 P_acct_rate_date => V_acct_rate_date,
1634 P_acct_rate_type => V_acct_rate_type,
1635 P_acct_exch_rate => V_acct_exchange_rate,
1636 P_acct_raw_cost => V_acct_raw_cost,
1637 P_project_curr_code => expenditure_item_rec.project_currency_code,
1638 P_project_rate_type => V_project_rate_type,
1639 P_project_rate_date => V_project_rate_date,
1640 P_project_exch_rate => V_project_exchange_rate,
1641 P_project_raw_cost => V_project_raw_cost,
1642 P_projfunc_curr_code => expenditure_item_rec.projfunc_currency_code,
1643 P_projfunc_cost_rate_type => V_projfunc_cost_rate_type,
1644 P_projfunc_cost_rate_date => V_projfunc_cost_rate_date,
1645 P_projfunc_cost_exch_rate => V_projfunc_cost_exchange_rate,
1646 P_projfunc_raw_cost => V_projfunc_raw_cost,
1647 P_system_linkage => expenditure_item_rec.system_linkage_function,
1648 P_status => V_status,
1649 P_stage => V_stage,
1650 P_Po_Line_ID => expenditure_item_rec.Po_Line_Id);
1651 IF P_DEBUG_MODE THEN
1652 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Call to PA_MULTI_CURRENCY_TXN.GET_CURRENCY_AMOUNTS');
1653
1654 END IF;
1655 /*
1656 * 2048868
1657 */
1658 IF ( expenditure_item_rec.system_linkage_function = 'ER' )
1659 THEN
1660 IF ( l_er_expenditure_id_tab.count > 0 )
1661 THEN
1662 IF P_DEBUG_MODE THEN
1663 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Size of the ER table [' || to_char(l_er_expenditure_id_tab.count) || ']');
1664 END IF;
1665 /*
1666 * The table is not expty.
1667 * Check whether this expenditure_id is already available in
1668 * the table. If it already exists, set l_exp_already_exists to 'Y'.
1669 */
1670 l_exp_already_exists := 'N' ;
1671 FOR LOOP_INDEX IN l_er_expenditure_id_tab.first..l_er_expenditure_id_tab.last
1672 LOOP
1673 IF P_DEBUG_MODE THEN
1674 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Comparing [' || to_char(expenditure_item_rec.expenditure_id) ||
1675 '] with [' || to_char(l_er_expenditure_id_tab(LOOP_INDEX)) || ']' );
1676 END IF;
1677 IF ( expenditure_item_rec.expenditure_id = l_er_expenditure_id_tab(LOOP_INDEX) )
1678 THEN
1679 l_exp_already_exists := 'Y' ;
1680 exit ;
1681 END IF ;
1682 END LOOP ;
1683 END IF;
1684
1685 IF P_DEBUG_MODE THEN
1686 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Already exists [' || l_exp_already_exists || ']');
1687 END IF;
1688 IF ( nvl(l_exp_already_exists, 'N') = 'N' )
1689 THEN
1690 /*
1691 * The current expenditure is not in the table.
1692 * So, add it.
1693 */
1694 l_er_expenditure_id_tab(l_er_exp_count) := expenditure_item_rec.expenditure_id ;
1695 l_exp_acct_exch_rate_tab(l_er_exp_count) := V_acct_exchange_rate ;
1696 IF P_DEBUG_MODE THEN
1697 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'New id [' || to_char(l_er_expenditure_id_tab(l_er_exp_count)) ||
1698 '] rate [' || to_char(l_exp_acct_exch_rate_tab(l_er_exp_count)) || ']' );
1699 END IF;
1700 l_er_exp_count := l_er_exp_count + 1 ;
1701 END IF;
1702 END IF ; -- 'ER'
1703 /*
1704 * 2048868
1705 */
1706
1707 END IF;
1708 /*
1709 * The output values are stored into array for later use in the update.
1710 *
1711 * Note: These assigments need not to be kept within the previous if statement
1712 * because the variables are initialized with the existing values
1713 * of the EIs before the MC Call. So even if MC call is not made
1714 * ( because of the existence of both the buckets ) the old
1715 * values will be assigned to the array elelemnts. So, final update
1716 * will update the columns with the existing values only and that is fine.
1717 */
1718 IF P_DEBUG_MODE THEN
1719 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before storing in Array ');
1720 END IF;
1721
1722 PA_CC_IDENT.DenomCurrCodeTab(v_loop_index) := expenditure_item_rec.denom_currency_code;
1723 PA_CC_IDENT.AcctRawCostTab(v_loop_index) := V_acct_raw_cost;
1724 /* This needs to be changed later bug951161 */
1725 PA_CC_IDENT.AcctRateDateTab(v_loop_index) := to_char(V_acct_rate_date,
1726 'dd-mon-yyyy hh:mi:ss');
1727 PA_CC_IDENT.AcctRateTypeTab(v_loop_index) := V_acct_rate_type;
1728 PA_CC_IDENT.AcctRateTab(v_loop_index) := V_acct_exchange_rate;
1729
1730 PA_CC_IDENT.ProjFuncRawCostTab(v_loop_index) := V_projfunc_raw_cost;
1731 PA_CC_IDENT.ProjFuncRateDateTab(v_loop_index) := to_char(V_projfunc_cost_rate_date,
1732 'dd-mon-yyyy hh:mi:ss');
1733 PA_CC_IDENT.ProjFuncRateTypeTab(v_loop_index) := V_projfunc_cost_rate_type;
1734 PA_CC_IDENT.ProjFuncRateTab(v_loop_index) := V_projfunc_cost_exchange_rate;
1735
1736 PA_CC_IDENT.ProjRawCostTab(v_loop_index) := V_project_raw_cost;
1737 /* This needs to be changed later bug951161 */
1738 PA_CC_IDENT.ProjRateDateTab(v_loop_index) := to_char(V_project_rate_date,
1739 'dd-mon-yyyy hh:mi:ss');
1740 PA_CC_IDENT.ProjRateTypeTab(v_loop_index) := V_project_rate_type;
1741 PA_CC_IDENT.ProjRateTab(v_loop_index) := V_project_exchange_rate;
1742 PA_CC_IDENT.StatusTab(v_loop_index) := V_status;
1743 IF P_DEBUG_MODE THEN
1744 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After storing in Array ');
1745 END IF;
1746 /*
1747 * Call the client extn. for creating related items
1748 * ( for labor )
1749 *
1750 * If Oracle error is retured ( v_cur_status < 0) then
1751 * an exception is raised and processing is halted. The
1752 * control goes back to the calling program and appropriate error
1753 * handling is done based on the output status variable as set.
1754 *
1755 * If application error is retured ( v_cur_status > 0) then
1756 * appropriate cost_dist_rejection_code is populated in the status array and
1757 * processing continues with the next record.
1758 * Here the same status variable as MC is reused.
1759 */
1760
1761 IF ( expenditure_item_rec.Source_Id IS NULL
1762 /*AND (NVL(expenditure_item_rec.net_zero, 'N') = 'N') */ /*Bug 4460518*/
1763 AND P_Sys_Link = 'LABOR') THEN
1764 IF P_DEBUG_MODE THEN
1765 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before call to PA_Costing_Client_Extns.Add_Transactions_Hook');
1766 END IF;
1767
1768 PA_Costing_Client_Extns.Add_Transactions_Hook(
1769 expenditure_item_rec.expenditure_item_id,
1770 expenditure_item_rec.system_linkage_function,
1771 v_cur_status);
1772 IF P_DEBUG_MODE THEN
1773 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After call to PA_Costing_Client_Extns.Add_Transactions_Hook');
1774 END IF;
1775 IF ( v_cur_status < 0 ) THEN
1776 P_Source := 'Client Extn';
1777 P_MC_IC_status := v_cur_status;
1778 RAISE E_local_exception;
1779 END IF;
1780 IF ( v_cur_status > 0 ) THEN
1781 PA_CC_IDENT.StatusTab(v_loop_index) := 'ADD_TRANSACTIONS_EXT_FAIL';
1782 END IF;
1783 END IF;
1784
1785 V_loop_index := V_loop_index + 1;
1786 END LOOP;
1787 END LOOP;
1788
1789
1790 /*
1791 * Subtract 1 from loop index to get the
1792 * actual no of times the previous loop is executed
1793 */
1794 V_loop_index := V_loop_index -1;
1795
1796 /*
1797 * Set the source. Its value is used by the calling program
1798 * only when the status <> 0
1799 */
1800 P_Source := 'IC Error';
1801
1802 /*
1803 * Call IC API to get the provider-receiver org
1804 * and cross-charge code
1805 */
1806 IF V_loop_index > 0 THEN
1807 IF P_DEBUG_MODE THEN
1808 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before call to PA_CC_IDENT.PA_CC_IDENTIFY_TXN');
1809 END IF;
1810 PA_CC_IDENT.PA_CC_IDENTIFY_TXN(
1811 P_ExpOrganizationIdTab => PA_CC_IDENT.ExpOrganizationIdTab,
1812 P_ExpOrgIdTab => PA_CC_IDENT.ExpOrgIdTab,
1813 P_ProjectIdTab => PA_CC_IDENT.ProjectIdTab,
1814 P_TaskIdTab => PA_CC_IDENT.TaskIdTab,
1815 P_ExpItemDateTab => PA_CC_IDENT.ExpItemDateTab,
1816 P_ExpItemIdTab => PA_CC_IDENT.ExpItemIdTab,
1817 P_PersonIdTab => PA_CC_IDENT.PersonIdTab,
1818 P_ExpTypeTab => PA_CC_IDENT.ExpTypeTab,
1819 P_SysLinkTab => PA_CC_IDENT.SysLinkTab,
1820 P_PrjOrganizationIdTab => PA_CC_IDENT.PrjOrganizationIdTab,
1821 P_PrjOrgIdTab => PA_CC_IDENT.PrjOrgIdTab,
1822 P_TransSourceTab => PA_CC_IDENT.TransSourceTab,
1823 P_NLROrganizationIdTab => PA_CC_IDENT.NLROrganizationIdTab,
1824 P_PrvdrLEIdTab => PA_CC_IDENT.PrvdrLEIdTab,
1825 P_RecvrLEIdTab => PA_CC_IDENT.RecvrLEIdTab,
1826 X_StatusTab => PA_CC_IDENT.StatusTab,
1827 X_CrossChargeTypeTab => PA_CC_IDENT.CrossChargeTypeTab,
1828 X_CrossChargeCodeTab => PA_CC_IDENT.CrossChargeCodeTab,
1829 X_PrvdrOrganizationIdTab => PA_CC_IDENT.PrvdrOrganizationIdTab,
1830 X_RecvrOrganizationIdTab => PA_CC_IDENT.RecvrOrganizationIdTab,
1831 X_RecvrOrgIdTab => PA_CC_IDENT.RecvrOrgIdTab,
1832 X_Error_Stage => V_Errorstage,
1833 X_Error_Code => V_Errorcode);
1834 IF P_DEBUG_MODE THEN
1835 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Call to PA_CC_IDENT.PA_CC_IDENTIFY_TXN');
1836 END IF;
1837 END IF;
1838
1839 /** To be uncommented after decing upon rates-model.
1840 ** CBGA Call to GetMappedToJobs ().
1841 **
1842 ** IF V_loop_index > 0 THEN
1843 ** pa_cc_utils.log_message('Before call to GetMappedToJobs');
1844 ** PA_Cross_Business_Grp.GetMappedToJobs (
1845 ** p_from_job_id_tab => JobIdTab,
1846 ** p_to_job_group_id_tab => JobGroupIdTab,
1847 ** x_to_job_id_tab => CostJobIdTab,
1848 ** x_status_code => V_Statuscode,
1849 ** x_error_stage_tab => ErrorStageTab,
1850 ** x_error_code_tab => ErrorCodeTab
1851 ** );
1852 ** pa_cc_utils.log_message('After Call to GetMappedToJobs');
1853 ** END IF;
1854 **
1855 **/
1856
1857 /*
1858 * Set the source. Its value is used by the calling program
1859 * only when the status <> 0
1860 */
1861 P_Source := 'Update Error';
1862 /*
1863 * Bug2048868
1864 */
1865 l_er_exp_count := l_er_exp_count - 1 ;
1866 IF P_DEBUG_MODE THEN
1867 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before updating Expenditures. Count to update [' || to_char(l_er_exp_count) || ']');
1868 END IF;
1869 IF ( l_er_exp_count > 0 )
1870 THEN
1871 FORALL i IN 1..l_er_exp_count
1872 UPDATE pa_expenditures exp
1873 SET exp.Acct_Exchange_rate = l_exp_acct_exch_rate_tab(i)
1874 WHERE exp.expenditure_id = l_er_expenditure_id_tab(i)
1875 ;
1876 END IF; -- l_er_exp_count
1877 IF P_DEBUG_MODE THEN
1878 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After updating Expenditures with exchange rate.');
1879 END IF;
1880 /*
1881 * End Bug2048868
1882 */
1883 /*
1884 * Final update statement to update all the relevant columns for
1885 * MC as well as IC. ( arrays are used for that )
1886 *
1887 * Note: The MC related column are updated only when the cost in the
1888 * appropriate currency is not available and the MC/IC API hasnot returned
1889 * any error.
1890 * The IC related columns are updated only when the MC/IC API hasnot returned
1891 * any error.
1892 *
1893 * Denom_Currency_Code shouldn't be null under normal circumstances,
1894 * for safety purpose it is set to accounting currency code if it's value
1895 * is null.
1896 */
1897 IF V_loop_index > 0 THEN
1898
1899
1900 IF P_DEBUG_MODE THEN
1901 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Final update statement');
1902 END IF;
1903
1904 /* added for bug#2919885 */
1905 FOR LOOP_INDEX IN 1..V_loop_index LOOP
1906 select count(*) into l_cc_dist_count_tab(loop_index)
1907 from pa_cc_dist_lines_all
1908 where expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index)
1909 and line_type = 'BL' ; /*Bug# 3184731 :Excluding line_type ='PC' here */
1910 END LOOP;
1911
1912
1913 FORALL LOOP_INDEX IN 1..V_loop_index
1914
1915
1916 UPDATE Pa_Expenditure_Items ITEM
1917 SET ITEM.Denom_Currency_Code =
1918 DECODE(ITEM.Denom_Currency_Code, NULL,
1919 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1920 PA_CC_IDENT.DenomCurrCodeTab(loop_index),
1921 ITEM.Denom_Currency_Code),
1922 ITEM.Denom_Currency_Code),
1923
1924 ITEM.Cost_Dist_Rejection_Code =
1925 PA_CC_IDENT.StatusTab(loop_index),
1926
1927 /** To be uncommented after decing upon rates-model.
1928 ** CBGA StatusTab contains the status information for the call pa_cc_identify_txn.
1929 ** ErrorStageTab contains the status information for the call GetMappedToJobs.
1930 ** The rejection code is set - when either of them is NOT NULL.
1931 **
1932 ** ITEM.Cost_Dist_Rejection_Code =
1933 ** decode ( PA_CC_IDENT.StatusTab(loop_index), NULL,
1934 ** ErrorStageTab(loop_index) ,
1935 ** PA_CC_IDENT.StatusTab(loop_index)
1936 ** ),
1937 **/
1938 ITEM.Burden_Cost =
1939 DECODE(ITEM.Burden_Cost,NULL,
1940 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1941 DECODE(ITEM.System_Linkage_Function,'BTC',
1942 PA_CC_IDENT.ProjFuncRawCostTab(loop_index), /* Replaced ProjRawCostTab by ProjFuncRawCostTab for bug 3285759 */
1943 ITEM.Burden_Cost),
1944 ITEM.Burden_Cost),
1945 ITEM.Burden_Cost),
1946 /***** Added for 3285759 */
1947 ITEM.Project_Burdened_Cost =
1948 DECODE(ITEM.Project_Burdened_Cost,NULL,
1949 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1950 DECODE(ITEM.System_Linkage_Function,'BTC',
1951 PA_CC_IDENT.ProjRawCostTab(loop_index),
1952 ITEM.Project_Burdened_Cost),
1953 ITEM.Project_Burdened_Cost),
1954 ITEM.Project_Burdened_Cost),
1955 /***** Added for 3285759 End */
1956 ITEM.Acct_Burdened_Cost =
1957 DECODE(ITEM.Acct_Burdened_Cost,NULL,
1958 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1959 DECODE(ITEM.System_Linkage_Function,'BTC',
1960 PA_CC_IDENT.AcctRawCostTab(loop_index),
1961 ITEM.Acct_Burdened_Cost),
1962 ITEM.Acct_Burdened_Cost),
1963 ITEM.Acct_Burdened_Cost),
1964 ITEM.Raw_Cost =
1965 DECODE(ITEM.Raw_Cost, NULL,
1966 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1967 DECODE(ITEM.System_Linkage_Function,'BTC',
1968 0,
1969 PA_CC_IDENT.ProjFuncRawCostTab(loop_index)),
1970 ITEM.Raw_Cost),
1971 ITEM.Raw_Cost),
1972 ITEM.ProjFunc_Cost_Exchange_Rate =
1973 DECODE(ITEM.Raw_Cost,NULL,
1974 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1975 PA_CC_IDENT.ProjFuncRateTab(loop_index),
1976 ITEM.ProjFunc_Cost_Exchange_Rate),
1977 ITEM.ProjFunc_Cost_exchange_Rate),
1978 ITEM.projfunc_cost_rate_Date =
1979 DECODE(ITEM.Raw_Cost,NULL,
1980 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1981 to_date(PA_CC_IDENT.ProjFuncRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
1982 ITEM.projfunc_cost_rate_Date),
1983 ITEM.projfunc_cost_rate_Date),
1984 ITEM.projfunc_cost_rate_Type =
1985 DECODE(ITEM.Raw_Cost,NULL,
1986 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1987 PA_CC_IDENT.ProjFuncRateTypeTab(loop_index),
1988 ITEM.projfunc_cost_rate_Type),
1989 ITEM.projfunc_cost_rate_Type),
1990 ITEM.Project_Raw_Cost =
1991 DECODE(ITEM.Project_Raw_Cost, NULL,
1992 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1993 DECODE(ITEM.System_Linkage_Function,'BTC',
1994 0,
1995 PA_CC_IDENT.ProjRawCostTab(loop_index)),
1996 ITEM.Project_Raw_Cost),
1997 ITEM.Project_Raw_Cost),
1998 ITEM.Project_Exchange_Rate =
1999 DECODE(ITEM.Project_Raw_Cost,NULL,
2000 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2001 PA_CC_IDENT.ProjRateTab(loop_index),
2002 ITEM.Project_Exchange_Rate),
2003 ITEM.Project_exchange_Rate),
2004 ITEM.Project_Rate_Date =
2005 DECODE(ITEM.Project_Raw_Cost,NULL,
2006 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2007 to_date(PA_CC_IDENT.ProjRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
2008 ITEM.Project_Rate_Date),
2009 ITEM.Project_Rate_Date),
2010 ITEM.Project_Rate_Type =
2011 DECODE(ITEM.Project_Raw_Cost,NULL,
2012 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2013 PA_CC_IDENT.ProjRateTypeTab(loop_index),
2014 ITEM.Project_Rate_Type),
2015 ITEM.Project_Rate_Type),
2016 ITEM.Acct_Raw_Cost =
2017 DECODE(ITEM.Acct_Raw_Cost,NULL,
2018 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2019 DECODE(ITEM.System_Linkage_Function,'BTC',
2020 0,
2021 PA_CC_IDENT.AcctRawCostTab(loop_index)),
2022 ITEM.Acct_Raw_Cost),
2023 ITEM.Acct_Raw_Cost),
2024 ITEM.Acct_Exchange_Rate =
2025 DECODE(ITEM.Acct_Raw_Cost,NULL,
2026 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2027 PA_CC_IDENT.AcctRateTab(loop_index),
2028 ITEM.Acct_Exchange_Rate),
2029 ITEM.Acct_Exchange_Rate),
2030 ITEM.Acct_Rate_Date =
2031 DECODE(ITEM.Acct_Raw_Cost,NULL,
2032 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2033 to_date(PA_CC_IDENT.AcctRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
2034 ITEM.Acct_Rate_Date),
2035 ITEM.Acct_Rate_Date),
2036 ITEM.Acct_Rate_Type =
2037 DECODE(ITEM.Acct_Raw_Cost,NULL,
2038 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2039 PA_CC_IDENT.AcctRateTypeTab(loop_index),
2040 ITEM.Acct_Rate_Type),
2041 ITEM.Acct_Rate_Type),
2042 ITEM.Cc_Cross_Charge_Code =
2043 Decode(ITEM.Cc_Cross_Charge_Code,'P', -- Added Decode wrapper to update only for P case 3173932
2044 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2045 PA_CC_IDENT.CrossChargeCodeTab(loop_index),
2046 ITEM.Cc_Cross_Charge_Code),ITEM.Cc_Cross_Charge_Code),
2047 ITEM.Cc_Cross_Charge_Type =
2048 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2049 PA_CC_IDENT.CrossChargeTypeTab(loop_index),
2050 ITEM.Cc_Cross_Charge_Type),
2051 ITEM.Cc_Bl_Distributed_Code =
2052 DECODE(PA_CC_IDENT.StatusTab(loop_index), NULL,
2053 DECODE(PA_CC_IDENT.CrossChargeCodeTab(loop_index),'B',
2054 'N',
2055 DECODE(l_cc_dist_count_tab(loop_index),0,'X','N')), /* bug#2919885 */
2056 ITEM.Cc_Bl_Distributed_Code),
2057 ITEM.Cc_IC_Processed_Code =
2058 DECODE(PA_CC_IDENT.StatusTab(loop_index), NULL,
2059 DECODE(PA_CC_IDENT.CrossChargeCodeTab(loop_index),'I',
2060 'N',
2061 'X'),
2062 ITEM.Cc_IC_processed_Code),
2063 ITEM.Cc_Prvdr_Organization_Id =
2064 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2065 PA_CC_IDENT.PrvdrOrganizationIdTab(loop_index),
2066 ITEM.Cc_Prvdr_Organization_Id),
2067 ITEM.Cc_Recvr_Organization_Id =
2068 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2069 PA_CC_IDENT.RecvrOrganizationIdTab(loop_index),
2070 ITEM.Cc_Recvr_Organization_Id),
2071 ITEM.Recvr_Org_Id =
2072 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2073 PA_CC_IDENT.RecvrOrgIdTab(loop_index),
2074 ITEM.Recvr_Org_Id)
2075
2076 /** To be uncommented after decing upon rates-model.
2077 ** CBGA Updating Cost_Job_Id in EI.
2078 ** ITEM.cost_job_id =
2079 ** DECODE(ErrorStageTab(loop_index),NULL,
2080 ** CostJobIdTab(loop_index),
2081 ** ITEM.cost_job_id)
2082 **/
2083 WHERE ITEM.Expenditure_Item_Id = PA_CC_IDENT.ExpItemIdTab(loop_index);
2084
2085 /*Code Changes for Bug No.2984871 start */
2086 P_Update_Count := SQL%ROWCOUNT;
2087 /*Code Changes for Bug No.2984871 end */
2088
2089 END IF;
2090 IF P_DEBUG_MODE THEN
2091 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Final Update Statement');
2092 END IF;
2093 /*
2094 * No of records updated is set to the output parameter.
2095 */
2096 /* Commented for Bug 2984871
2097 P_Update_Count := SQL%ROWCOUNT; */
2098
2099 IF P_DEBUG_MODE THEN
2100 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'End ');
2101 END IF;
2102 pa_cc_utils.reset_curr_function ;
2103
2104 EXCEPTION
2105 /*
2106 * This exception is raised in case of Oracle error in
2107 * the client extn. The control is retured back to the calling
2108 * program. Error handling to be done over there.
2109 */
2110 WHEN E_local_exception THEN
2111 RAISE;
2112 /*
2113 * Commented during bug 1943559
2114 * NULL;
2115 */
2116
2117 /*
2118 * Any other error encountered either in MC or IC processing.
2119 * Set the status variable and control is returned back to the
2120 * calling program.
2121 * Note: Source is already populated in the processing part.
2122 */
2123 WHEN OTHERS THEN
2124 IF V_errorcode IS NOT NULL THEN
2125 P_MC_IC_STATUS := V_errorcode;
2126 ELSE
2127 P_MC_IC_Status := SQLCODE;
2128 END IF;
2129
2130 END Perform_MC_and_IC_processing;
2131
2132 /*---------------------------------------------------------------------*/
2133 PROCEDURE get_proj_rate_type ( P_task_id IN NUMBER
2134 ,p_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL
2135 ,p_structure_version_id IN NUMBER DEFAULT NULL
2136 ,p_calling_module IN VARCHAR2
2137 ,P_project_currency_code IN OUT NOCOPY VARCHAR2
2138 ,P_project_rate_type IN OUT NOCOPY VARCHAR2
2139 )
2140 IS
2141
2142 BEGIN
2143
2144 --
2145 -- This procedure derives project currency code and
2146 -- project currency conversion rate type
2147 --
2148 -- Logic: if the user provides a proj_rate_type, use it.
2149 -- Otherwise derive it from the task, if project_rate_type is not
2150 -- defined at task level then get it from project level. If project_rate_type
2151 -- is not defined at project level also then derive the
2152 -- project_rate_type value from default_rate_type column in
2153 -- project owning operating units implementation options table.
2154 -- proj_currency_code is derived from projects table
2155
2156 IF (p_calling_module <> 'WORKPLAN') THEN
2157
2158 SELECT proj.project_currency_code,
2159 NVL(P_project_rate_type, NVL(NVL(task.project_rate_type,
2160 proj.project_rate_type), imp.default_rate_type))
2161 INTO P_project_currency_code,
2162 P_project_rate_type
2163 FROM pa_projects_all proj,
2164 pa_tasks task,
2165 pa_implementations_all imp
2166 WHERE proj.project_id = task.project_id
2167 AND task.task_id = P_task_id
2168 AND proj.org_id = imp.org_id;
2169
2170 ELSE
2171
2172 BEGIN
2173
2174 SELECT proj.project_currency_code,
2175 NVL(P_project_rate_type, NVL(NVL(task.project_rate_type,
2176 proj.project_rate_type), imp.default_rate_type))
2177 INTO P_project_currency_code,
2178 P_project_rate_type
2179 FROM pa_projects_all proj,
2180 pa_tasks task,
2181 pa_map_wp_to_fin_tasks_v map_wp_fin,
2182 pa_implementations_all imp
2183 WHERE proj.project_id = p_project_id
2184 AND task.task_id = map_wp_fin.mapped_fin_task_id
2185 AND map_wp_fin.proj_element_id = p_task_id
2186 AND map_wp_fin.parent_structure_version_id = p_structure_version_id
2187 AND proj.org_id = imp.org_id;
2188
2189 EXCEPTION
2190 WHEN NO_DATA_FOUND THEN
2191 SELECT proj.project_currency_code,
2192 NVL(NVL(P_project_rate_type, proj.project_rate_type)
2193 , imp.default_rate_type)
2194 INTO P_project_currency_code,
2195 P_project_rate_type
2196 FROM pa_projects_all proj,
2197 pa_implementations_all imp
2198 WHERE proj.project_id = p_project_id
2199 AND proj.org_id = imp.org_id;
2200 END ; -- anonymous
2201
2202 END IF;
2203
2204
2205 EXCEPTION
2206 WHEN no_data_found THEN
2207 P_project_currency_code := NULL;
2208 P_project_rate_type := NULL;
2209
2210 WHEN others THEN
2211 RAISE ;
2212
2213 END get_proj_rate_type ;
2214
2215 /*---------------------------------------------------------------------*/
2216 PROCEDURE get_proj_rate_date ( P_task_id IN NUMBER ,
2217 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL ,
2218 P_EI_date IN DATE ,
2219 p_structure_version_id IN NUMBER DEFAULT NULL,
2220 p_calling_module IN VARCHAR2,
2221 P_project_rate_date IN OUT NOCOPY DATE )
2222 IS
2223
2224 BEGIN
2225
2226 --
2227 -- This procedure derives project currency conversion rate date
2228 --
2229 -- Logic: If user provides a project currency conversion date, Use it.
2230 -- Otherwise derive it from task( identified bt P_task_id),
2231 -- if project_rate_date is not defined at task level then derive it from
2232 -- projects table. If the project_rate_date is not defined at project
2233 -- level also then the proj_rate_date will be derived using the
2234 -- default_rate_date_code from expenditure operating units implementation
2235 -- options. If the default_rate_date_code is E then return the expenditure
2236 -- item date(P_EI_date), if default_rate_date_code is P then return the
2237 -- PA period ending date.
2238
2239 IF ( P_project_rate_date IS NULL )
2240 THEN
2241 IF (p_calling_module <> 'WORKPLAN')
2242 THEN
2243 SELECT NVL(NVL(task.project_rate_date,
2244 proj.project_rate_date),
2245 DECODE(imp.default_rate_date_code,
2246 'E', P_EI_date, 'P',
2247 pa_utils2.get_pa_date(P_EI_date,
2248 sysdate, imp.org_id))) /**CBGA**/
2249 INTO P_project_rate_date
2250 FROM pa_projects_all proj,
2251 pa_tasks task,
2252 pa_implementations_all imp
2253 WHERE task.task_id = P_task_id
2254 AND proj.project_id = task.project_id
2255 AND nvl(proj.org_id, -99) = nvl(imp.org_id, -99);
2256 ELSE
2257 BEGIN
2258 SELECT task.project_rate_date
2259 INTO P_project_rate_date
2260 FROM pa_tasks task
2261 ,pa_map_wp_to_fin_tasks_v map_wp_fin
2262 WHERE task.task_id = map_wp_fin.mapped_fin_task_id
2263 AND map_wp_fin.proj_element_id = p_task_id
2264 AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
2265 EXCEPTION
2266 WHEN NO_DATA_FOUND THEN
2267 NULL;
2268 END ; -- anonymous
2269 IF ( P_project_rate_date IS NULL )
2270 THEN
2271 SELECT NVL(proj.project_rate_date,
2272 DECODE(imp.default_rate_date_code,
2273 'E', P_EI_date, 'P',
2274 pa_utils2.get_pa_date(P_EI_date,
2275 sysdate, imp.org_id)))
2276 INTO P_project_rate_date
2277 FROM pa_projects_all proj
2278 ,pa_implementations_all imp
2279 WHERE proj.project_id = p_project_id
2280 AND nvl(proj.org_id, -99) = nvl(imp.org_id, -99);
2281 END IF;
2282 END IF; -- calling_module
2283 END IF ;
2284
2285
2286 EXCEPTION
2287 WHEN no_data_found THEN
2288 P_project_rate_date := NULL ;
2289
2290 WHEN others THEN
2291 P_project_rate_date := NULL ;
2292 RAISE ;
2293
2294 END get_proj_rate_date ;
2295
2296
2297 /** The following new parameters are added for the FI changes
2298 * p_project_id IN pa_projects_all.project_id%type
2299 * p_exp_org_id IN pa_projects_all.org_id%type
2300 **/
2301 PROCEDURE get_currency_attributes
2302 (P_project_id IN pa_projects_all.project_id%type default NULL,
2303 P_exp_org_id IN pa_projects_all.org_id%type default NULL,
2304 P_task_id IN pa_expenditure_items_all.task_id%TYPE,
2305 P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
2306 P_calling_module IN VARCHAR2,
2307 P_denom_curr_code IN pa_expenditure_items_all.denom_currency_code%TYPE,
2308 P_accounted_flag IN VARCHAR2 DEFAULT 'N',
2309 P_acct_curr_code IN pa_expenditure_items_all.acct_currency_code%TYPE,
2310 X_acct_rate_date IN OUT NOCOPY pa_expenditure_items_all.acct_rate_date%TYPE,
2311 X_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE,
2312 X_acct_exch_rate IN OUT NOCOPY pa_expenditure_items_all.acct_exchange_rate%TYPE,
2313 P_project_curr_code IN pa_expenditure_items_all.project_currency_code%TYPE,
2314 X_project_rate_date IN OUT NOCOPY pa_expenditure_items_all.project_rate_date%TYPE,
2315 X_project_rate_type IN OUT NOCOPY pa_expenditure_items_all.project_rate_type%TYPE ,
2316 X_project_exch_rate IN OUT NOCOPY pa_expenditure_items_all.project_exchange_rate%TYPE,
2317 P_projfunc_curr_code IN pa_expenditure_items_all.projfunc_currency_code%TYPE,
2318 X_projfunc_cost_rate_date IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_date%TYPE,
2319 X_projfunc_cost_rate_type IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_type%TYPE ,
2320 X_projfunc_cost_exch_rate IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_exchange_rate%TYPE,
2321 P_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE,
2322 P_structure_version_id IN NUMBER DEFAULT NULL,
2323 X_status OUT NOCOPY VARCHAR2,
2324 X_stage OUT NOCOPY NUMBER)
2325 IS
2326
2327 l_dummy_char varchar2(100);
2328 l_dummy_date Date;
2329
2330
2331 TYPE UserSuppliedType IS RECORD (
2332 acct_rate_type VARCHAR2(1) := 'N'
2333 ,acct_rate_date VARCHAR2(1) := 'N'
2334 ,projfunc_cost_rate_type VARCHAR2(1) := 'N'
2335 ,projfunc_cost_rate_date VARCHAR2(1) := 'N');
2336
2337 usersupplied UserSuppliedType;
2338
2339 l_temp_acct_rate_date date;
2340 l_temp_acct_rate_type varchar2(100);
2341 l_temp_acct_exch_rate number;
2342 l_temp_project_rate_date date;
2343 l_temp_project_rate_type varchar2(100);
2344 l_temp_project_exch_rate number;
2345 l_temp_projfunc_cost_rate_date date;
2346 l_temp_projfunc_cost_rate_type varchar2(100);
2347 l_temp_projfunc_cost_exch_rate number;
2348
2349
2350 --------------------------------------
2351 --Forward bodies
2352
2353 ----------------------------------------------------------------------
2354 Procedure derive_project_attributes( P_task_id IN pa_expenditure_items_all.task_id%TYPE
2355 ,P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL
2356 ,P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2357 ,P_structure_version_id IN NUMBER DEFAULT NULL
2358 ,P_calling_module IN VARCHAR2
2359 ,x_project_rate_type IN OUT NOCOPY pa_expenditure_items_all.project_rate_type%TYPE
2360 ,x_project_rate_date IN OUT NOCOPY pa_expenditure_items_all.project_rate_date%TYPE
2361 )
2362 is
2363
2364 l_char_dummy pa_expenditure_items_all.project_currency_code%TYPE;
2365
2366 begin
2367
2368 --dbms_output.put_line('deriving project attributes');
2369 /*
2370 * Project_rate_type.
2371 */
2372 IF ( x_project_rate_type IS NULL )
2373 THEN --{
2374 pa_multi_currency_txn.get_proj_rate_type( P_task_id => P_task_id
2375 ,p_project_id => p_project_id
2376 ,p_structure_version_id => p_structure_version_id
2377 ,p_calling_module => p_calling_module
2378 ,P_project_currency_code => l_char_dummy
2379 ,P_project_rate_type => x_project_rate_type
2380 );
2381 END IF ; --} x_project_rate_type IS NULL
2382
2383 /*
2384 * Project_rate_date.
2385 */
2386 IF ( x_project_rate_date IS NULL )
2387 THEN --{
2388 pa_multi_currency_txn.get_proj_rate_date( P_task_id => P_task_id
2389 ,P_project_id => P_project_id
2390 ,P_ei_date => P_ei_date
2391 ,P_structure_version_id => p_structure_version_id
2392 ,P_calling_module => p_calling_module
2393 ,P_project_rate_date => x_project_rate_date
2394 );
2395 END IF ; --} x_project_rate_date IS NULL
2396 /***
2397 dbms_output.put_line('t [' || to_char(P_task_id) ||
2398 '] dt [' || to_char(P_ei_date) ||
2399 '] cm [' || P_calling_module ||
2400 '] prt [' || x_project_rate_type ||
2401 '] prd [' || to_char(x_project_rate_date) || ']');
2402 *********/
2403 end derive_project_attributes ;
2404
2405 -----------------------------------------------------------------------
2406 procedure derive_acct_attributes( P_calling_module IN VARCHAR2
2407 ,P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2408 ,P_attribute IN VARCHAR2
2409 ,x_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
2410 ,x_acct_rate_date IN OUT NOCOPY pa_expenditure_items_all.acct_rate_date%TYPE
2411 )
2412 is
2413 begin
2414
2415 IF ( P_attribute = 'TYPE' OR P_attribute = 'BOTH' )
2416 THEN
2417 x_acct_rate_type := NVL(x_acct_rate_type, pa_multi_currency.G_rate_type);
2418 END IF; -- P_attribute = 'TYPE'
2419
2420 IF ( P_attribute = 'DATE' OR P_attribute = 'BOTH' )
2421 THEN
2422 IF ( P_calling_module = 'TRANSFER' )
2423 THEN --{
2424
2425 pa_multi_currency_txn.get_default_acct_rate_date( P_ei_date => P_ei_date
2426 ,P_acct_rate_date => X_acct_rate_date
2427 );
2428
2429 ELSE --}{
2430
2431 pa_multi_currency_txn.get_acct_rate_date( P_ei_date => P_ei_date
2432 ,P_acct_rate_date => X_acct_rate_date
2433 ) ;
2434
2435 END IF; --} End P_calling_module = 'TRANSFER'
2436 END IF; -- P_attribute = 'DATE'
2437 end derive_acct_attributes ;
2438
2439 -----------------------------------------------------------------------
2440 procedure derive_projfunc_attributes
2441 ( P_calling_module IN VARCHAR2
2442 ,P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2443 ,P_task_id IN pa_expenditure_items_all.task_id%TYPE
2444 ,P_project_id IN pa_projects_all.project_id%TYPE
2445 ,P_attribute IN VARCHAR2
2446 ,P_structure_version_id IN NUMBER DEFAULT NULL
2447 ,x_projfunc_cost_rate_type IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_type%TYPE
2448 ,x_projfunc_cost_rate_date IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_date%TYPE
2449 )
2450 is
2451
2452 l_char_dummy pa_expenditure_items_all.projfunc_currency_code%TYPE;
2453
2454 begin
2455
2456 --dbms_output.put_line('deriving projfunc attributes');
2457 IF ( P_attribute = 'TYPE' OR P_attribute = 'BOTH')
2458 THEN --{
2459 pa_multi_currency_txn.get_projfunc_cost_rate_type
2460 ( P_task_id =>P_task_id ,
2461 P_project_id => p_project_id ,
2462 P_structure_version_id => p_structure_version_id ,
2463 P_calling_module => p_calling_module ,
2464 P_projfunc_currency_code =>l_char_dummy ,
2465 P_projfunc_cost_rate_type =>x_projfunc_cost_rate_type
2466 ) ;
2467 END IF; --} P_attribute = 'TYPE' OR P_attribute = 'BOTH'
2468
2469 IF ( P_attribute = 'DATE' OR P_attribute = 'BOTH' )
2470 THEN --{
2471 IF ( P_calling_module = 'TRANSFER' ) THEN
2472
2473 pa_multi_currency_txn.get_def_projfunc_cst_rate_date
2474 ( P_task_id =>P_task_id ,
2475 P_project_id => p_project_id ,
2476 P_structure_version_id => p_structure_version_id ,
2477 P_calling_module => p_calling_module ,
2478 P_ei_date =>P_ei_date ,
2479 P_projfunc_cost_rate_date =>x_projfunc_cost_rate_date
2480 ) ;
2481 ELSE --}{
2482
2483 --dbms_output.put_line('calling get_projfunc_cost_rate_date');
2484
2485 pa_multi_currency_txn.get_projfunc_cost_rate_date
2486 ( P_task_id =>P_task_id ,
2487 P_project_id =>P_project_id ,
2488 P_ei_date =>P_ei_date ,
2489 P_structure_version_id =>p_structure_version_id ,
2490 P_calling_module =>p_calling_module ,
2491 P_projfunc_cost_rate_date =>x_projfunc_cost_rate_date
2492 ) ;
2493
2494 END IF; --} end P_calling_module = 'TRANSFER'
2495 END IF; --} P_attribute = 'DATE' OR P_attribute = 'BOTH'
2496
2497 /*********
2498 dbms_output.put_line('t [' || to_char(P_task_id) ||
2499 '] dt [' || to_char(P_ei_date) ||
2500 '] cm [' || P_calling_module ||
2501 '] att [' || P_attribute ||
2502 '] pfrt [' || x_projfunc_cost_rate_type ||
2503 '] pfrd [' || to_char(x_projfunc_cost_rate_date) || ']');
2504 *****/
2505
2506 end derive_projfunc_attributes ;
2507 --Forward bodies end
2508 --------------------------------------
2509
2510 BEGIN
2511
2512 l_temp_acct_rate_date := x_acct_rate_date;
2513 l_temp_acct_rate_type := x_acct_rate_type;
2514 l_temp_acct_exch_rate := x_acct_exch_rate;
2515 l_temp_project_rate_date := x_project_rate_date;
2516 l_temp_project_rate_type := x_project_rate_type;
2517 l_temp_project_exch_rate := x_project_exch_rate;
2518 l_temp_projfunc_cost_rate_date := x_projfunc_cost_rate_date;
2519 l_temp_projfunc_cost_rate_type := x_projfunc_cost_rate_type;
2520 l_temp_projfunc_cost_exch_rate := x_projfunc_cost_exch_rate;
2521
2522 IF ( P_projfunc_curr_code = P_acct_curr_code )
2523 THEN -- {
2524 IF ( P_projfunc_curr_code = P_denom_curr_code )
2525 THEN --{
2526 X_acct_rate_date := NULL;
2527 X_acct_rate_type := NULL;
2528 X_acct_exch_rate := NULL;
2529
2530 X_projfunc_cost_rate_date := NULL ;
2531 X_projfunc_cost_rate_type := NULL ;
2532 X_projfunc_cost_exch_rate := NULL ;
2533 ELSE --}{
2534
2535 IF ( P_accounted_flag = 'Y' )
2536 THEN -- {
2537 /*
2538 * At this point it is assumed that - if the txn is accounted
2539 * the account attributes will be NOT NULL.
2540 */
2541 X_projfunc_cost_rate_date := X_acct_rate_date ;
2542 X_projfunc_cost_rate_type := X_acct_rate_type ;
2543 X_projfunc_cost_exch_rate := X_acct_exch_rate ;
2544
2545 END IF; -- } P_accounted_flag = 'Y'
2546
2547 IF ( P_system_linkage = 'ER' AND P_accounted_flag <> 'Y' )
2548 THEN -- {
2549 /*
2550 * IF its an Expense Report, Functional gets the highest precedence.
2551 */
2552 IF ( X_acct_rate_type IS NULL )
2553 THEN --{
2554
2555 IF p_calling_module = 'FORECAST' Then
2556 derive_fi_curr_attributes
2557 ( P_project_id => p_project_id
2558 ,P_exp_org_id => p_exp_org_id
2559 ,P_ei_date => p_ei_date
2560 ,P_attribute => 'FORECAST'
2561 ,x_project_rate_type => l_dummy_char
2562 ,x_project_rate_date => l_dummy_date
2563 ,x_projfunc_cost_rate_type => l_dummy_char
2564 ,x_projfunc_cost_rate_date => l_dummy_date
2565 ,x_acct_rate_type => X_acct_rate_type
2566 ,x_acct_rate_date => l_dummy_date --X_acct_rate_date
2567 );
2568 Else
2569 derive_acct_attributes
2570 ( P_calling_module => P_calling_module
2571 ,P_ei_date => P_ei_date
2572 ,P_attribute => 'TYPE'
2573 ,x_acct_rate_type => X_acct_rate_type
2574 ,x_acct_rate_date=> X_acct_rate_date
2575 );
2576 End If;
2577
2578 ELSE -- }{ X_acct_rate_type IS NOT NULL
2579 usersupplied.acct_rate_type := 'Y' ;
2580 END IF; --} X_acct_rate_type IS NULL
2581 /*
2582 * For Expense Report, Project Functional is overridden by the Functional
2583 * even if its user supplied.
2584 */
2585 X_projfunc_cost_rate_type := X_acct_rate_type ;
2586
2587 IF ( X_acct_rate_date IS NULL)
2588 THEN --{
2589 IF p_calling_module = 'FORECAST' Then
2590 derive_fi_curr_attributes
2591 ( P_project_id => p_project_id
2592 ,P_exp_org_id => p_exp_org_id
2593 ,P_ei_date => p_ei_date
2594 ,P_attribute => 'FORECAST'
2595 ,x_project_rate_type => l_dummy_char
2596 ,x_project_rate_date => l_dummy_date
2597 ,x_projfunc_cost_rate_type => l_dummy_char
2598 ,x_projfunc_cost_rate_date => l_dummy_date
2599 ,x_acct_rate_type => l_dummy_char --X_acct_rate_type
2600 ,x_acct_rate_date => X_acct_rate_date
2601 );
2602 Else
2603
2604 derive_acct_attributes
2605 ( P_calling_module => P_calling_module
2606 ,P_ei_date => P_ei_date
2607 ,P_attribute => 'DATE'
2608 ,x_acct_rate_type => X_acct_rate_type
2609 ,x_acct_rate_date=> X_acct_rate_date
2610 );
2611 End If;
2612 ELSE -- }{ X_acct_rate_date IS NOT NULL
2613 usersupplied.acct_rate_date := 'Y' ;
2614 END IF; --} X_acct_rate_date IS NULL
2615 X_projfunc_cost_rate_date := X_acct_rate_date ;
2616
2617 IF ( X_acct_rate_type = 'User' )
2618 THEN --{
2619 IF ( pa_multi_currency.is_user_rate_type_allowed(
2620 P_from_currency => P_denom_curr_code,
2621 P_to_currency => P_acct_curr_code,
2622 P_conversion_date => X_acct_rate_date)='Y'
2623 )
2624 THEN --{
2625 IF (X_acct_exch_rate IS NOT NULL)
2626 THEN --{
2627 X_projfunc_cost_exch_rate := X_acct_exch_rate ;
2628 ELSE --}{
2629 X_status := 'PA_ACCT_USER_RATE_NOT_DEFINED' ;
2630 RETURN ;
2631 END IF; --}
2632 ELSE -- }{
2633 X_status := 'PA_NO_ACCT_USER_RATE_TYPE';
2634 RETURN ;
2635 END IF; --}
2636 END IF; --} X_acct_rate_type = 'User'
2637 END IF; -- } P_system_linkage = 'ER' AND P_accounted_flag <> 'Y'
2638 print_message('account flag <> N and syslinkage <> ER');
2639 IF ( NVL(P_accounted_flag, 'N') <> 'Y' AND P_system_linkage <> 'ER' )
2640 THEN --{
2641 IF ( X_projfunc_cost_rate_type IS NOT NULL )
2642 THEN --{
2643 /*
2644 * Functional Attribute is overridden by Project Functional -
2645 * if Project Functional is supplied.
2646 */
2647 usersupplied.projfunc_cost_rate_type := 'Y' ;
2648 X_acct_rate_type := X_projfunc_cost_rate_type ;
2649 ELSE -- }{ X_projfunc_cost_rate_type IS NULL
2650 /*
2651 * <2822867> If Functional Rate Type is available, override Project Functional Rate Type
2652 * with it - but only for non-Timecards. For Time-cards derive Project
2653 * Functional attributes afresh.
2654 */
2655 IF ( X_acct_rate_type IS NOT NULL AND P_system_linkage <> 'ST' AND P_system_linkage <> 'OT' )
2656 THEN --{
2657 usersupplied.acct_rate_type := 'Y' ;
2658 X_projfunc_cost_rate_type := X_acct_rate_type ;
2659 ELSE --}{ X_acct_rate_type IS NULL
2660 /*
2661 * Both Project Functional rate type and Functional rate type
2662 * are NULL. Derive Project Functional and copy it to Functional.
2663 */
2664 print_message('Both Project Functional rate type and Functional rate type are NULL');
2665 IF p_calling_module = 'FORECAST' Then
2666 print_message('Calling derive_fi_curr_attributes for projfunc rate type');
2667 derive_fi_curr_attributes
2668 ( P_project_id => p_project_id
2669 ,P_exp_org_id => p_exp_org_id
2670 ,P_ei_date => p_ei_date
2671 ,P_attribute => 'FORECAST'
2672 ,x_project_rate_type => l_dummy_char
2673 ,x_project_rate_date => l_dummy_date
2674 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2675 ,x_projfunc_cost_rate_date => l_dummy_date --X_projfunc_cost_rate_date
2676 ,x_acct_rate_type => l_dummy_char
2677 ,x_acct_rate_date => l_dummy_date
2678 );
2679 Else
2680 derive_projfunc_attributes
2681 ( P_calling_module => P_calling_module
2682 ,P_ei_date => P_ei_date
2683 ,P_task_id => P_task_id
2684 ,P_project_id => P_project_id
2685 ,P_attribute => 'TYPE'
2686 ,P_structure_version_id => p_structure_version_id
2687 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2688 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2689 );
2690 End if;
2691
2692 X_acct_rate_type := X_projfunc_cost_rate_type ;
2693 X_acct_exch_rate := X_projfunc_cost_exch_rate; --2822867
2694 END IF; --} X_acct_rate_type IS NOT NULL
2695 END IF; --} X_projfunc_cost_rate_type IS NOT NULL
2696
2697 IF ( X_projfunc_cost_rate_date IS NOT NULL )
2698 THEN --{
2699 /*
2700 * Functional Attribute is overridden by Project Functional -
2701 * if Project Functional is supplied.
2702 */
2703 usersupplied.projfunc_cost_rate_date := 'Y' ;
2704 X_acct_rate_date := X_projfunc_cost_rate_date ;
2705 ELSE -- }{ X_projfunc_cost_rate_date IS NULL
2706 /*
2707 * <2822867> If Functional Rate Date is available, override Project Functional Rate Date
2708 * with it - but only for non-Timecards. For Time-cards derive Project
2709 * Functional attributes afresh.
2710 */
2711 IF ( X_acct_rate_date IS NOT NULL AND P_system_linkage <> 'ST' AND P_system_linkage <> 'OT' )
2712 THEN --{
2713 usersupplied.acct_rate_date := 'Y' ;
2714 X_projfunc_cost_rate_date := X_acct_rate_date ;
2715 ELSE --}{ X_acct_rate_date IS NULL
2716 /*
2717 * Both Project Functional rate date and Functional rate date
2718 * are NULL. Derive Project Functional and copy it to Functional.
2719 */
2720 IF p_calling_module = 'FORECAST' Then
2721 print_message('Calling derive_fi_curr_attributes for projfunc rate date');
2722 derive_fi_curr_attributes
2723 ( P_project_id => p_project_id
2724 ,P_exp_org_id => p_exp_org_id
2725 ,P_ei_date => p_ei_date
2726 ,P_attribute => 'FORECAST'
2727 ,x_project_rate_type => l_dummy_char
2728 ,x_project_rate_date => l_dummy_date
2729 ,x_projfunc_cost_rate_type => l_dummy_char --X_projfunc_cost_rate_type
2730 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2731 ,x_acct_rate_type => l_dummy_char
2732 ,x_acct_rate_date => l_dummy_date
2733 );
2734 Else
2735 derive_projfunc_attributes
2736 ( P_calling_module => P_calling_module
2737 ,P_ei_date => P_ei_date
2738 ,P_task_id => P_task_id
2739 ,P_project_id => P_project_id
2740 ,P_attribute => 'DATE'
2741 ,P_structure_version_id => P_structure_version_id
2742 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2743 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2744 );
2745 End If;
2746 X_acct_rate_date := X_projfunc_cost_rate_date ;
2747 END IF; --} X_acct_rate_date IS NOT NULL
2748 END IF; --} X_projfunc_cost_rate_date IS NOT NULL
2749
2750 /*
2751 * Exchange Rate.
2752 * If either project functional was provided or neither of them was provided
2753 * - user project functional.
2754 */
2755 IF ( ( usersupplied.projfunc_cost_rate_type = 'Y' ) OR
2756 ( usersupplied.acct_rate_type <> 'Y' AND usersupplied.projfunc_cost_rate_type <> 'Y' )
2757 )
2758 THEN --{
2759 /*
2760 * If projfunc is supplied (1) or neither of them is given (2)
2761 * In both the cases (1)(2) projfunc takes precedence.
2762 */
2763 IF (X_projfunc_cost_rate_type = 'User')
2764 THEN --{
2765 IF ( pa_multi_currency.is_user_rate_type_allowed(
2766 P_from_currency => P_denom_curr_code,
2767 P_to_currency => P_projfunc_curr_code,
2768 P_conversion_date => X_projfunc_cost_rate_date) = 'Y'
2769 )
2770 THEN --{
2771 IF (X_projfunc_cost_exch_rate IS NOT NULL)
2772 THEN --{
2773 X_acct_exch_rate := X_projfunc_cost_exch_rate ;
2774 ELSE -- }{
2775 X_status := 'PA_NO_PROJFUNC_USER_RATE' ; /* bug#2855640 */
2776 RETURN ;
2777 END IF; --} X_projfunc_cost_exch_rate IS NOT NULL
2778 ELSE -- }{
2779 X_status := 'PA_NO_PROJFUNC_USER_RATE_TYPE';
2780 RETURN ;
2781 END IF; --} -- user_allowed
2782 END IF; --}
2783 ELSIF (usersupplied.acct_rate_type ='Y')
2784 THEN -- }{
2785 IF ( X_acct_rate_type = 'User')
2786 THEN --{
2787 IF (pa_multi_currency.is_user_rate_type_allowed(
2788 P_from_currency => P_denom_curr_code,
2789 P_to_currency => P_acct_curr_code,
2790 P_conversion_date => X_acct_rate_date) ='Y')
2791 THEN --{
2792 IF ( X_acct_exch_rate IS NOT NULL)
2793 THEN --{
2794 X_projfunc_cost_exch_rate := X_acct_exch_rate ;
2795 ELSE --}{
2796 X_status := 'PA_ACCT_USER_RATE_NOT_DEFINED' ;
2797 RETURN ;
2798 END IF; -- } X_acct_exch_rate IS NOT NULL
2799 ELSE --}{
2800 X_status := 'PA_NO_ACCT_USER_RATE_TYPE';
2801 RETURN ;
2802 END IF; --} user_allowed
2803 END IF; --} X_acct_rate_type = 'User'
2804 END IF ; --} usersupplied.projfunc_cost_rate_type = 'Y'
2805 END IF; --} NVL(P_accounted_flag, 'N') = 'N' AND P_system_linkage <> 'ER'
2806 END IF; --} P_projfunc_curr_code = P_denom_curr_code
2807 /*
2808 * All projfunc and acct attributes are derived.
2809 * The following code derives the project attributes.
2810 * projfunc = acct.
2811 */
2812 IF ( P_project_curr_code = P_denom_curr_code )
2813 THEN --{
2814 X_project_rate_type := NULL ;
2815 X_project_rate_date := NULL ;
2816 X_project_exch_rate := NULL ;
2817 ELSE --}{
2818 IF (P_project_curr_code = P_projfunc_curr_code)
2819 THEN --{
2820 /*
2821 * Project_rate_type.
2822 */
2823 IF (X_project_rate_type IS NOT NULL)
2824 THEN --{
2825 IF ( usersupplied.acct_rate_type <> 'Y' AND usersupplied.projfunc_cost_rate_type <> 'Y' )
2826 THEN --{
2827 X_acct_rate_type := X_project_rate_type ;
2828 X_projfunc_cost_rate_type := X_project_rate_type ;
2829 END IF; --} usersupplied.acct_rate_type <> 'Y' AND usersupplied.projfunc_cost_rate_type <> 'Y'
2830 ELSE --}{
2831 X_project_rate_type := X_projfunc_cost_rate_type ;
2832 END IF; --} X_project_rate_type IS NOT NULL
2833
2834 /*
2835 * Project_rate_date.
2836 */
2837 IF (X_project_rate_date IS NOT NULL)
2838 THEN --{
2839 IF ( usersupplied.acct_rate_date <> 'Y' AND usersupplied.projfunc_cost_rate_date <> 'Y' )
2840 THEN --{
2841 X_acct_rate_date := X_project_rate_date ;
2842 X_projfunc_cost_rate_date := X_project_rate_date ;
2843 END IF; --} usersupplied.acct_rate_date <> 'Y' AND usersupplied.projfunc_cost_rate_date <> 'Y'
2844 ELSE --}{
2845 X_project_rate_date := X_projfunc_cost_rate_date ;
2846 END IF; --} X_project_rate_date IS NOT NULL
2847
2848 /*
2849 * Project_exch_rate.
2850 */
2851 IF ( X_projfunc_cost_rate_type = 'User')
2852 THEN --{
2853 X_project_exch_rate := X_projfunc_cost_exch_rate ;
2854 ELSE --}{
2855 IF ( X_project_rate_type = 'User')
2856 THEN --{
2857 IF ( pa_multi_currency.is_user_rate_type_allowed(
2858 P_from_currency => P_denom_curr_code,
2859 P_to_currency => P_project_curr_code,
2860 P_conversion_date => X_project_rate_date) = 'Y' )
2861 THEN --}{
2862 IF ( X_project_exch_rate IS NOT NULL )
2863 THEN --{
2864 X_projfunc_cost_exch_rate := X_project_exch_rate ;
2865 X_acct_exch_rate := X_project_exch_rate ;
2866 ELSE --}{
2867 X_status := 'PA_PROJ_USER_RATE_NOT_DEFINED' ;
2868 RETURN ;
2869 END IF;
2870 ELSE --}{
2871 X_status := 'PA_NO_PROJ_USER_RATE_TYPE';
2872 RETURN ;
2873 END IF; --}
2874 ELSE --}{
2875 X_project_exch_rate := X_projfunc_cost_exch_rate ;
2876 END IF; --}
2877 END IF; --} X_projfunc_cost_rate_type = 'User'
2878
2879 ELSE --}{
2880 IF p_calling_module = 'FORECAST' Then
2881 print_message('calling derive_fi_curr_attributes for project rate type');
2882 derive_fi_curr_attributes
2883 ( P_project_id => p_project_id
2884 ,P_exp_org_id => p_exp_org_id
2885 ,P_ei_date => p_ei_date
2886 ,P_attribute => 'FORECAST'
2887 ,x_project_rate_type => X_project_rate_type
2888 ,x_project_rate_date => X_project_rate_date
2889 ,x_projfunc_cost_rate_type => l_dummy_char
2890 ,x_projfunc_cost_rate_date => l_dummy_date
2891 ,x_acct_rate_type => l_dummy_char
2892 ,x_acct_rate_date => l_dummy_date
2893 );
2894 Else
2895 derive_project_attributes( P_task_id => P_task_id
2896 ,P_project_id => P_project_id
2897 ,P_ei_date => P_ei_date
2898 ,P_structure_version_id => P_structure_version_id
2899 ,P_calling_module => P_calling_module
2900 ,x_project_rate_type => X_project_rate_type
2901 ,x_project_rate_date => X_project_rate_date
2902 );
2903 End If;
2904
2905 END IF; --} P_project_curr_code = P_projfunc_curr_code
2906 END IF; --} P_project_curr_code = P_denom_curr_code
2907 END IF; -- } P_projfunc_curr_code = P_acct_curr_code
2908
2909 IF ( P_projfunc_curr_code <> P_acct_curr_code )
2910 THEN --{
2911
2912 IF ( P_projfunc_curr_code = P_denom_curr_code )
2913 THEN --{
2914 X_projfunc_cost_rate_type := NULL ;
2915 X_projfunc_cost_rate_date := NULL ;
2916 X_projfunc_cost_exch_rate := NULL ;
2917 ELSE --}{
2918
2919 IF ( X_projfunc_cost_rate_type IS NULL )
2920 THEN --{
2921 IF p_calling_module = 'FORECAST' Then
2922 print_message('calling derive_fi_curr_attributes for project rate date ');
2923 derive_fi_curr_attributes
2924 ( P_project_id => p_project_id
2925 ,P_exp_org_id => p_exp_org_id
2926 ,P_ei_date => p_ei_date
2927 ,P_attribute => 'FORECAST'
2928 ,x_project_rate_type => l_dummy_char
2929 ,x_project_rate_date => l_dummy_date
2930 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2931 ,x_projfunc_cost_rate_date => l_dummy_date --X_projfunc_cost_rate_date
2932 ,x_acct_rate_type => l_dummy_char
2933 ,x_acct_rate_date => l_dummy_date
2934 );
2935 Else
2936
2937 derive_projfunc_attributes
2938 ( P_calling_module => P_calling_module
2939 ,P_ei_date => P_ei_date
2940 ,P_task_id => P_task_id
2941 ,P_project_id => P_project_id
2942 ,P_attribute => 'TYPE'
2943 ,P_structure_version_id => P_structure_version_id
2944 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2945 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2946 );
2947 End if;
2948 ELSE --}{
2949 usersupplied.projfunc_cost_rate_type := 'Y' ;
2950 END IF; --} X_projfunc_cost_rate_type IS NULL
2951
2952 IF ( X_projfunc_cost_rate_date IS NULL )
2953 THEN --{
2954 IF p_calling_module = 'FORECAST' Then
2955 print_message('calling derive_fi_curr_attributes for projfunc rate type');
2956 derive_fi_curr_attributes
2957 ( P_project_id => p_project_id
2958 ,P_exp_org_id => p_exp_org_id
2959 ,P_ei_date => p_ei_date
2960 ,P_attribute => 'FORECAST'
2961 ,x_project_rate_type => l_dummy_char
2962 ,x_project_rate_date => l_dummy_date
2963 ,x_projfunc_cost_rate_type => l_dummy_char --X_projfunc_cost_rate_type
2964 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2965 ,x_acct_rate_type => l_dummy_char
2966 ,x_acct_rate_date => l_dummy_date
2967 );
2968 Else
2969 derive_projfunc_attributes
2970 ( P_calling_module => P_calling_module
2971 ,P_ei_date => P_ei_date
2972 ,P_task_id => P_task_id
2973 ,P_project_id => P_project_id
2974 ,P_attribute => 'DATE'
2975 ,P_structure_version_id => P_structure_version_id
2976 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2977 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2978 );
2979 End if;
2980 ELSE --}{
2981 usersupplied.projfunc_cost_rate_date := 'Y' ;
2982 END IF; --} X_projfunc_cost_rate_date IS NULL
2983
2984 IF (X_projfunc_cost_rate_type = 'User')
2985 THEN --{
2986 IF (pa_multi_currency.is_user_rate_type_allowed(
2987 P_from_currency => P_denom_curr_code,
2988 P_to_currency => P_projfunc_curr_code,
2989 P_conversion_date => X_projfunc_cost_rate_date) = 'Y')
2990 THEN --{
2991 IF ( X_projfunc_cost_exch_rate IS NULL )
2992 THEN --{
2993 X_status := 'PA_NO_PROJFUNC_USER_RATE' ; /* bug#2855640 */
2994 RETURN ;
2995 END IF; --} X_projfunc_cost_exch_rate IS NOT NULL
2996 ELSE --}{
2997 X_status := 'PA_NO_PROJFUNC_USER_RATE_TYPE';
2998 RETURN ;
2999 END IF; --} user_allowed <> 'Y'
3000 END IF; --} X_projfunc_cost_rate_type = 'User'
3001 END IF; --} P_projfunc_curr_code = P_denom_curr_code
3002
3003
3004 IF ( P_acct_curr_code = P_denom_curr_code )
3005 THEN --{
3006 X_acct_rate_type := NULL ;
3007 X_acct_rate_date := NULL ;
3008 X_acct_exch_rate := NULL ;
3009 ELSE --}{
3010 IF ( X_acct_rate_type IS NULL )
3011 THEN --{
3012 IF p_calling_module = 'FORECAST' Then
3013 print_message('calling derive_fi_curr_attributes for acct rate type');
3014 derive_fi_curr_attributes
3015 ( P_project_id => p_project_id
3016 ,P_exp_org_id => p_exp_org_id
3017 ,P_ei_date => p_ei_date
3018 ,P_attribute => 'FORECAST'
3019 ,x_project_rate_type => l_dummy_char
3020 ,x_project_rate_date => l_dummy_date
3021 ,x_projfunc_cost_rate_type => l_dummy_char
3022 ,x_projfunc_cost_rate_date => l_dummy_date
3023 ,x_acct_rate_type => X_acct_rate_type
3024 ,x_acct_rate_date => l_dummy_date --X_acct_rate_date
3025 );
3026 Else
3027 derive_acct_attributes( P_calling_module => P_calling_module
3028 ,P_ei_date => P_ei_date
3029 ,P_attribute => 'TYPE'
3030 ,x_acct_rate_type => X_acct_rate_type
3031 ,x_acct_rate_date=> X_acct_rate_date
3032 );
3033 End if;
3034
3035 ELSE --}{
3036 usersupplied.acct_rate_type := 'Y' ;
3037 END IF; --} X_acct_rate_type IS NULL
3038
3039 IF ( X_acct_rate_date IS NULL )
3040 THEN --{
3041 IF p_calling_module = 'FORECAST' Then
3042 print_message('calling derive_fi_curr_attributes for acct rate date');
3043 derive_fi_curr_attributes
3044 ( P_project_id => p_project_id
3045 ,P_exp_org_id => p_exp_org_id
3046 ,P_ei_date => p_ei_date
3047 ,P_attribute => 'FORECAST'
3048 ,x_project_rate_type => l_dummy_char
3049 ,x_project_rate_date => l_dummy_date
3050 ,x_projfunc_cost_rate_type => l_dummy_char
3051 ,x_projfunc_cost_rate_date => l_dummy_date
3052 ,x_acct_rate_type => l_dummy_char --X_acct_rate_type
3053 ,x_acct_rate_date => X_acct_rate_date
3054 );
3055 Else
3056 derive_acct_attributes( P_calling_module => P_calling_module
3057 ,P_ei_date => P_ei_date
3058 ,P_attribute => 'DATE'
3059 ,x_acct_rate_type => X_acct_rate_type
3060 ,x_acct_rate_date=> X_acct_rate_date
3061 );
3062 End if;
3063 ELSE --}{
3064 usersupplied.acct_rate_date := 'Y' ;
3065 END IF; --} X_acct_rate_date IS NULL
3066
3067 IF (X_acct_rate_type = 'User')
3068 THEN --{
3069 IF (pa_multi_currency.is_user_rate_type_allowed(
3070 P_from_currency => P_denom_curr_code,
3071 P_to_currency => P_acct_curr_code,
3072 P_conversion_date => X_acct_rate_date) = 'Y')
3073 THEN --{
3074 IF ( X_acct_exch_rate IS NULL )
3075 THEN --{
3076 X_status := 'PA_ACCT_USER_RATE_NOT_DEFINED' ;
3077 RETURN ;
3078 END IF; --} X_acct_exch_rate IS NOT NULL
3079 ELSE --}{
3080 X_status := 'PA_NO_ACCT_USER_RATE_TYPE';
3081 RETURN ;
3082 END IF; --} user_allowed <> 'Y'
3083 END IF; --} X_acct_rate_type = 'User'
3084 END IF ; --} P_acct_curr_code = P_denom_curr_code
3085
3086 /*
3087 * Projfunc and acct rates are ready.
3088 */
3089 IF ( P_project_curr_code = P_denom_curr_code )
3090 THEN --{
3091 X_project_rate_type := NULL ;
3092 X_project_rate_date := NULL ;
3093 X_project_exch_rate := NULL ;
3094 ELSE --}{
3095 IF ( P_project_curr_code = P_projfunc_curr_code )
3096 THEN --{
3097
3098 /*
3099 * Project_rate_type
3100 */
3101 IF ( usersupplied.projfunc_cost_rate_type = 'Y' )
3102 THEN --{
3103 --dbms_output.put_line('moving pfrt to prt');
3104 X_project_rate_type := X_projfunc_cost_rate_type ;
3105 ELSE --}{
3106 IF ( X_project_rate_type IS NOT NULL)
3107 THEN -- {
3108 X_projfunc_cost_rate_type := X_project_rate_type ;
3109 ELSE --}{
3110 X_project_rate_type := X_projfunc_cost_rate_type ;
3111 END IF; --} X_project_rate_type IS NOT NULL
3112 END IF; --} usersupplied.projfunc_cost_rate_type = 'Y'
3113
3114 /*
3115 * Project_rate_date
3116 */
3117 IF ( usersupplied.projfunc_cost_rate_date = 'Y' )
3118 THEN --{
3119 X_project_rate_date := X_projfunc_cost_rate_date ;
3120 ELSE --}{
3121 IF ( X_project_rate_date IS NOT NULL)
3122 THEN -- {
3123 X_projfunc_cost_rate_date := X_project_rate_date ;
3124 ELSE --}{
3125 --dbms_output.put_line('b4 moving pfrd to prd');
3126 --dbms_output.put_line('pfrd ['|| to_char(X_projfunc_cost_rate_date) || ']');
3127 --dbms_output.put_line('prd ['|| to_char(X_project_rate_date) || ']');
3128 X_project_rate_date := X_projfunc_cost_rate_date ;
3129 --dbms_output.put_line('after moving pfrd to prd');
3130 --dbms_output.put_line('pfrd ['|| to_char(X_projfunc_cost_rate_date) || ']');
3131 --dbms_output.put_line('prd ['|| to_char(X_project_rate_date) || ']');
3132 END IF; --} X_project_rate_date IS NOT NULL
3133 END IF; --} usersupplied.projfunc_cost_rate_date = 'Y'
3134
3135 /*
3136 * Project exch rate.
3137 */
3138 IF ( X_projfunc_cost_rate_type = 'User')
3139 THEN --{
3140 X_project_exch_rate := X_projfunc_cost_exch_rate ;
3141 ELSE --}{
3142 IF ( X_project_rate_type = 'User')
3143 THEN --{
3144 IF ( pa_multi_currency.is_user_rate_type_allowed(
3145 P_from_currency => P_denom_curr_code,
3146 P_to_currency => P_project_curr_code,
3147 P_conversion_date => X_project_rate_date) ='Y' )
3148 THEN --}{
3149 IF ( X_project_exch_rate IS NOT NULL )
3150 THEN --{
3151 X_projfunc_cost_exch_rate := X_project_exch_rate ;
3152 ELSE --}{
3153 X_status := 'PA_PROJ_USER_RATE_NOT_DEFINED' ;
3154 RETURN ;
3155 END IF;
3156 ELSE --}{
3157 X_status := 'PA_NO_PROJ_USER_RATE_TYPE';
3158 RETURN ;
3159 END IF; --}
3160 ELSE --}{
3161 --dbms_output.put_line('moving pfer to per');
3162 X_project_exch_rate := X_projfunc_cost_exch_rate ;
3163 END IF; --}
3164 END IF; --} X_projfunc_cost_rate_type = 'User'
3165 END IF; --} P_project_curr_code = P_projfunc_curr_code
3166
3167 IF ( P_project_curr_code = P_acct_curr_code )
3168 THEN --{
3169
3170 /*
3171 * Project_rate_type
3172 */
3173 IF ( usersupplied.acct_rate_type = 'Y' )
3174 THEN --{
3175 X_project_rate_type := X_acct_rate_type ;
3176 ELSE --}{
3177 IF ( X_project_rate_type IS NOT NULL)
3178 THEN -- {
3179 X_acct_rate_type := X_project_rate_type ;
3180 ELSE --}{
3181 X_project_rate_type := X_acct_rate_type ;
3182 END IF; --} X_project_rate_type IS NOT NULL
3183 END IF; --} usersupplied.acct_rate_type = 'Y'
3184
3185 /*
3186 * Project_rate_date
3187 */
3188 IF ( usersupplied.acct_rate_date = 'Y' )
3189 THEN --{
3190 X_project_rate_date := X_acct_rate_date ;
3191 ELSE --}{
3192 IF ( X_project_rate_date IS NOT NULL)
3193 THEN -- {
3194 X_acct_rate_date := X_project_rate_date ;
3195 ELSE --}{
3196 X_project_rate_date := X_acct_rate_date ;
3197 END IF; --} X_project_rate_date IS NOT NULL
3198 END IF; --} usersupplied.acct_rate_date = 'Y'
3199
3200 /*
3201 * Project exch rate.
3202 */
3203 IF ( X_acct_rate_type = 'User')
3204 THEN --{
3205 X_project_exch_rate := X_acct_exch_rate ;
3206 ELSE --}{
3207 IF ( X_project_rate_type = 'User')
3208 THEN --{
3209 IF ( pa_multi_currency.is_user_rate_type_allowed(
3210 P_from_currency => P_denom_curr_code,
3211 P_to_currency => P_project_curr_code,
3212 P_conversion_date => X_project_rate_date) ='Y' )
3213 THEN --}{
3214 IF ( X_project_exch_rate IS NOT NULL )
3215 THEN --{
3216 X_acct_exch_rate := X_project_exch_rate ;
3217 ELSE --}{
3218 X_status := 'PA_PROJ_USER_RATE_NOT_DEFINED' ;
3219 RETURN ;
3220 END IF;
3221 ELSE --}{
3222 X_status := 'PA_NO_PROJ_USER_RATE_TYPE';
3223 RETURN ;
3224 END IF; --}
3225 ELSE --}{
3226 X_project_exch_rate := X_acct_exch_rate ;
3227 END IF; --}
3228 END IF; --} X_acct_rate_type = 'User'
3229 END IF; --} P_project_curr_code = P_acct_curr_code
3230
3231 IF ( P_project_curr_Code <> P_acct_curr_code AND P_project_curr_code <> P_projfunc_curr_code)
3232 THEN --{
3233 IF p_calling_module = 'FORECAST' Then
3234 derive_fi_curr_attributes
3235 ( P_project_id => p_project_id
3236 ,P_exp_org_id => p_exp_org_id
3237 ,P_ei_date => p_ei_date
3238 ,P_attribute => 'FORECAST'
3239 ,x_project_rate_type => X_project_rate_type
3240 ,x_project_rate_date => X_project_rate_date
3241 ,x_projfunc_cost_rate_type => l_dummy_char
3242 ,x_projfunc_cost_rate_date => l_dummy_date
3243 ,x_acct_rate_type => l_dummy_char
3244 ,x_acct_rate_date => l_dummy_date
3245 );
3246 Else
3247 derive_project_attributes( P_task_id => P_task_id
3248 ,P_project_id => P_project_id
3249 ,P_ei_date => P_ei_date
3250 ,P_structure_version_id => P_structure_version_id
3251 ,P_calling_module => P_calling_module
3252 ,x_project_rate_type => X_project_rate_type
3253 ,x_project_rate_date => X_project_rate_date
3254 );
3255 End If;
3256 END IF;--}
3257 END IF ; --} P_project_curr_code = P_denom_curr_code
3258
3259 END IF; --} P_projfunc_curr_code <> P_acct_curr_code
3260
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 x_acct_rate_date := l_temp_acct_rate_date;
3264 x_acct_rate_type := l_temp_acct_rate_type;
3265 x_acct_exch_rate := l_temp_acct_exch_rate;
3266 x_project_rate_date := l_temp_project_rate_date;
3267 x_project_rate_type := l_temp_project_rate_type;
3268 x_project_exch_rate := l_temp_project_exch_rate;
3269 x_projfunc_cost_rate_date := l_temp_projfunc_cost_rate_date;
3270 x_projfunc_cost_rate_type := l_temp_projfunc_cost_rate_type;
3271 x_projfunc_cost_exch_rate := l_temp_projfunc_cost_exch_rate;
3272 RAISE;
3273 END get_currency_attributes;
3274 /*---------------------------------------------------------------------*/
3275
3276 END pa_multi_currency_txn ;