[Home] [Help]
PACKAGE BODY: APPS.PA_MULTI_CURRENCY_TXN
Source
1 PACKAGE BODY pa_multi_currency_txn AS
2 --$Header: PAXMCTXB.pls 120.18.12020000.3 2013/03/06 09:38:41 admarath 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 ) AND (P_projfunc_cost_exch_rate is NOT NULL) /*Added the extra and condition for bug 13966212*/
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 /*bug 12983087*/
1330 l_parent_cdf_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1331 l_parent_CCharge_Type PA_PLSQL_DATATYPES.Char15TabTyp;
1332 l_parent_Cc_Prvdr_Org_Id PA_PLSQL_DATATYPES.IdTabTyp;
1333 l_parent_Cc_Recvr_Org_Id PA_PLSQL_DATATYPES.IdTabTyp;
1334 l_parent_Recvr_Org_Id PA_PLSQL_DATATYPES.IdTabTyp;
1335 /*bug 12983087*/
1336
1337 /*
1338 * 2048868
1339 */
1340 l_er_expenditure_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1341 l_exp_acct_exch_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1342 l_exp_already_exists VARCHAR2(1) := 'N' ;
1343 l_er_exp_count NUMBER := 1 ;
1344
1345 l_cc_dist_count_tab PA_PLSQL_DATATYPES.NumTabTyp; /* added for bug#2919885 */
1346
1347 /** CBGA Table Declaration.... to be removed later...
1348 ** will be declared in PA_Cross_Business_Grp
1349 ** Will be uncommented when rates-model is decided.
1350 ** JobIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1351 ** JobGroupIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1352 ** CostJobIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1353 ** V_status_code VARCHAR2(150);
1354 ** ErrorStageTab VARCHAR2(150);
1355 ** ErrorCodeTab NUMBER;
1356 **/
1357
1358 /*
1359 * This cursor is used to get all the Expenditure Item records
1360 * Which are being processed by the Cost distribute program.
1361 * For getting such records, we rely on cost_distributed_flag to be 'S'
1362 * and use the request_id as passed by the distribute program.
1363 * This cursor picks up all columns required by the MC/IC APIs
1364 * and Client Extn. for creating related items. ( in case of labor )
1365 * The parameter to this cursor is used to process regular and related items
1366 * separately. This is required to take care of the adjustment of a regular
1367 * item which has got some related items. In this case, we should not process
1368 * regular and its related items in the same pass since the cost of the related
1369 * item is calculated only in the client extension of the regular item
1370 *
1371 * Note: denom_currency_code should not be null under normal circumstances.
1372 * The check is included for safety purpose only.
1373 * If denom_currency_code is null then acct_currency_code is used.
1374 *
1375 * Expenditure organization is passed as incurred_by_organization_id of
1376 * expenditure if override_to_organization_id in expenditure item
1377 * doesn't exist. The incurred_by_organization_id value is passed from the
1378 * cost distribute program.
1379 *
1380 * Note: Special processing is done for expenditure items of type
1381 * 'BTC'. For These type of Txns, the conversion of burden cost
1382 * in Functional and Project currencies are carried out exactly in the
1383 * similar manner in which the corresponding conversions are done for raw
1384 * cost in case of Txns of other type.
1385 */
1386
1387 CURSOR expenditure_item_cursor(l_related_item VARCHAR2) is
1388 SELECT
1389 ITEM.expenditure_item_id,
1390 ITEM.expenditure_item_date,
1391 ITEM.Task_Id,
1392 EXP.expenditure_id,
1393 NVL(ITEM.Denom_Currency_Code,ITEM.Acct_Currency_Code) Denom_Currency_Code,
1394 DECODE(ITEM.System_Linkage_Function,'BTC',
1395 ITEM.Denom_Burdened_Cost,ITEM.Denom_Raw_Cost) Denom_Raw_Cost,
1396 ITEM.Acct_Raw_Cost,
1397 ITEM.Acct_Currency_Code,
1398 DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Rate_Date, ITEM.Acct_Rate_Date) Acct_Rate_Date,
1399 DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Rate_Type, ITEM.Acct_Rate_Type) Acct_Rate_Type,
1400 DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Exchange_Rate, ITEM.Acct_Exchange_Rate) Acct_Exchange_Rate,
1401 ITEM.Raw_Cost,
1402 ITEM.Projfunc_Currency_Code,
1403 ITEM.Projfunc_Cost_Rate_Date,
1404 ITEM.Projfunc_Cost_Rate_Type,
1405 ITEM.Projfunc_Cost_Exchange_Rate,
1406 ITEM.Project_Raw_Cost,
1407 ITEM.Project_Currency_Code,
1408 ITEM.Project_Rate_Date,
1409 ITEM.Project_Rate_Type,
1410 ITEM.Project_Exchange_Rate,
1411 ITEM.Source_Expenditure_Item_ID Source_Id,
1412 ITEM.Net_Zero_Adjustment_Flag Net_zero,
1413 ITEM.org_id,
1414 ITEM.expenditure_type,
1415 ITEM.system_linkage_function,
1416 ITEM.transaction_source,
1417 TXN.GL_Accounted_Flag, /* Bug #1824407 */
1418 NVL(ITEM.override_to_organization_id,EXP.incurred_by_organization_id) exp_organization_id,
1419 ITEM.Organization_Id nlr_organization_id,
1420 EXP.incurred_by_person_id,
1421 ITEM.Cc_Cross_Charge_Type,
1422 ITEM.Cc_Cross_Charge_Code,
1423 ITEM.Cc_Prvdr_Organization_Id,
1424 ITEM.Cc_Recvr_Organization_Id,
1425 ITEM.Recvr_Org_Id
1426 ,ITEM.PO_Line_Id --3535935 hkulkarn
1427 /**CBGA select job_id and project_group_id using API GetProjectGroupId().
1428 ** To be uncommented after decing upon rates-model.
1429 ** ITEM.Job_Id,
1430 ** ITEM.Cost_Job_Id,
1431 ** PA_Cross_Business_Grp.GetProjectGroupId(TASK.Project_Id, 'C') job_group_id
1432 **/
1433 FROM PA_Expenditure_Items ITEM,
1434 PA_Expenditures EXP,
1435 PA_Transaction_Sources TXN /* Bug 1824407 */
1436 /** To be uncommented after decing upon rates-model.
1437 ** CBGA Join pa_tasks with pa_expenditure_items_all.
1438 ** PA_Tasks TASK
1439 **/
1440 WHERE ITEM.Cost_Distributed_Flag = 'S'
1441 AND ITEM.Cost_Dist_Rejection_Code IS NULL
1442 AND ITEM.Request_id = P_request_id
1443 AND ITEM.expenditure_id = EXP.expenditure_id
1444 AND ITEM.Transaction_Source = TXN.Transaction_Source (+)/*Bug1824407*/
1445 AND (( ITEM.Source_Expenditure_Item_Id IS NULL
1446 AND l_related_item = 'N')
1447 OR
1448 ( ITEM.Source_Expenditure_Item_Id IS NOT NULL
1449 AND l_related_item = 'Y'))
1450 /** To be uncommented after decing upon rates-model.
1451 ** CBGA Joining pa_tasks with EIs.
1452 ** AND ITEM.task_id = TASK.task_id
1453 **/
1454 ;
1455 /******
1456 * Right now, I cant find out any reason to put this order by,
1457 * if necessary, i will come back and change this.
1458 ORDER BY ITEM.Expenditure_Item_Id;
1459 ******/
1460 BEGIN
1461 if pa_cc_utils.g_debug_mode then
1462 l_debug_mode := 'Y';
1463 else
1464 l_debug_mode := 'N';
1465 end if;
1466 pa_debug.set_process(
1467 x_process => 'PLSQL',
1468 x_debug_mode => l_debug_mode);
1469 pa_cc_utils.set_curr_function('Perform_MC_and_IC_processing');
1470 IF P_DEBUG_MODE THEN
1471 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Start ');
1472 END IF;
1473 P_MC_IC_status := 0;
1474 P_update_count := 0;
1475
1476 /*
1477 * The init procedure is called to set the global variables
1478 * to be used by the MC API
1479 */
1480 IF P_DEBUG_MODE THEN
1481 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Call to PA_MULTI_CURRENCY.INIT');
1482 END IF;
1483 PA_MULTI_CURRENCY.INIT;
1484 IF P_DEBUG_MODE THEN
1485 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Call to PA_MULTI_CURRENCY.INIT');
1486 END IF;
1487 /*
1488 * Loop through all expenditure items and set the required
1489 * Parameter tables with the appropriate values.
1490 * There is an outer loop of two passes, in the first pass all
1491 * regular items are processed and in the next all related items are
1492 * processed. This is done to take care of the case of adjustment
1493 * of a regular item containing related items. ( in this case, the
1494 * cost of the related items is calculated only during the client extension
1495 * call of the regular item and hence we shouldnot fetch the regular items
1496 * and related items together; otherwise the calculated cost wont be visible to the
1497 * cursor )
1498 *
1499 * We call the MC API within this loop because it doesnt accept
1500 * array parameters currently. The output values are stored into arrays
1501 * for later use in the update. The IC API is called outside this loop
1502 * since array parameters are accepted by it. The final update
1503 * takes care of both MC and IC.
1504 */
1505
1506 FOR loop_control in 1 .. 2
1507 LOOP
1508 IF P_DEBUG_MODE THEN
1509 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'In outer Loop');
1510 END IF;
1511 IF ( loop_control = 1 ) THEN
1512 V_Related_Item := 'N';
1513 ELSE
1514 V_Related_Item := 'Y';
1515 END IF;
1516 FOR expenditure_item_rec in expenditure_item_cursor(V_Related_Item)
1517 LOOP
1518 IF P_DEBUG_MODE THEN
1519 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'In related Items Loop');
1520 END IF;
1521
1522 P_Source := 'Assignment';
1523 /*
1524 * Set the array variables with the appropriate values.
1525 * Project Org ID is populated with the recvr_org_id value available in EI
1526 * so that the Identification process doesnt update this value.
1527 */
1528 IF P_DEBUG_MODE THEN
1529 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before setting Array variables');
1530 END IF;
1531
1532 PA_CC_IDENT.ProjectIdTab(v_loop_index) := NULL;
1533 PA_CC_IDENT.PrjOrganizationIdTab(v_loop_index) := NULL;
1534 PA_CC_IDENT.PrjOrgIdTab(v_loop_index) := expenditure_item_rec.recvr_org_id;
1535 PA_CC_IDENT.PrvdrLEIdTab(v_loop_index) := NULL;
1536 PA_CC_IDENT.RecvrLEIdTab(v_loop_index) := NULL;
1537 PA_CC_IDENT.ExpItemDateTab(v_loop_index) := expenditure_item_rec.expenditure_item_date;
1538 PA_CC_IDENT.TaskIdTab(v_loop_index) := expenditure_item_rec.task_id;
1539 PA_CC_IDENT.ExpItemIdTab(v_loop_index) := expenditure_item_rec.expenditure_item_id;
1540 PA_CC_IDENT.ExpOrgIdTab(v_loop_index) := expenditure_item_rec.org_id;
1541 PA_CC_IDENT.ExpTypeTab(v_loop_index) := expenditure_item_rec.expenditure_type;
1542 PA_CC_IDENT.SysLinkTab(v_loop_index) := expenditure_item_rec.system_linkage_function;
1543 PA_CC_IDENT.TransSourceTab(v_loop_index) := expenditure_item_rec.transaction_source;
1544 PA_CC_IDENT.ExpOrganizationIdTab(v_loop_index) := expenditure_item_rec.exp_organization_id;
1545 PA_CC_IDENT.NLROrganizationIdTab(v_loop_index) := expenditure_item_rec.nlr_organization_id;
1546 PA_CC_IDENT.PersonIdTab(v_loop_index) := expenditure_item_rec.incurred_by_person_id;
1547 PA_CC_IDENT.CrossChargeTypeTab(v_loop_index) := expenditure_item_rec.cc_cross_charge_type;
1548 PA_CC_IDENT.CrossChargeCodeTab(v_loop_index) := expenditure_item_rec.cc_cross_charge_code;
1549 PA_CC_IDENT.PrvdrOrganizationIdTab(v_loop_index):= expenditure_item_rec.cc_prvdr_organization_id;
1550 PA_CC_IDENT.RecvrOrganizationIdTab(v_loop_index):= expenditure_item_rec.cc_recvr_organization_id;
1551 PA_CC_IDENT.RecvrOrgIdTab(v_loop_index) := expenditure_item_rec.recvr_org_id;
1552 V_acct_rate_date := expenditure_item_rec.acct_rate_date;
1553 V_acct_rate_type := expenditure_item_rec.acct_rate_type;
1554 V_acct_exchange_rate := expenditure_item_rec.acct_exchange_rate;
1555 V_projfunc_cost_rate_type := expenditure_item_rec.projfunc_cost_rate_type;
1556 V_projfunc_cost_rate_date := expenditure_item_rec.projfunc_cost_rate_date;
1557 V_projfunc_cost_exchange_rate := expenditure_item_rec.projfunc_cost_exchange_rate;
1558 V_project_rate_type := expenditure_item_rec.project_rate_type;
1559 V_project_rate_date := expenditure_item_rec.project_rate_date;
1560 V_project_exchange_rate := expenditure_item_rec.project_exchange_rate;
1561 V_status := NULL; -- Bug 4142911
1562
1563 /** To be uncommented after decing upon rates-model.
1564 ** CBGA The array variables for GetMappedToJobs () are populated here.
1565 **
1566 ** JobIdTab (v_loop_index) := expenditure_item_rec.job_id;
1567 ** JobGroupIdTab(v_loop_index) := expenditure_item_rec.job_group_id;
1568 ** CostJobIdTab (v_loop_index) := expenditure_item_rec.cost_job_id;
1569 ** V_status_code := NULL; -- x_status_code
1570 ** ErrorStageTab(v_loop_index) := NULL; -- x_error_stage_tab
1571 ** ErrorCodeTab (v_loop_index) := NULL; -- x_error_code_tab
1572 **
1573 **/
1574 IF P_DEBUG_MODE THEN
1575 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After setting Array variables');
1576 END IF;
1577
1578 /*
1579 * MC API is to be called ( for getting Acct/Project raw cost )
1580 * only when any of the three buckets are empty.
1581 */
1582 IF ( expenditure_item_rec.acct_raw_cost IS NULL
1583 OR
1584 expenditure_item_rec.raw_cost IS NULL
1585 OR
1586 expenditure_item_rec.project_raw_cost IS NULL) THEN
1587 /*
1588 * Set the source. Its value is used by the calling program
1589 * only when the status <> 0
1590 */
1591 P_Source := 'MC Error';
1592 IF P_DEBUG_MODE THEN
1593 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Call to PA_MULTI_CURRENCY_TXN.GET_CURRENCY_AMOUNTS');
1594 END IF;
1595 If nvl(expenditure_item_rec.GL_Accounted_Flag,'N') = 'Y' THEN
1596
1597 V_acct_raw_cost := expenditure_item_rec.acct_raw_cost ;
1598 V_acct_rate_date := expenditure_item_rec.acct_rate_date ;
1599 V_acct_exchange_rate := expenditure_item_rec.acct_exchange_rate ;
1600 V_acct_rate_type := expenditure_item_rec.acct_rate_type ;
1601 End If;
1602
1603 IF expenditure_item_rec.Po_Line_Id IS NOT NULL THEN --3535935 hkulkarn
1604
1605 V_project_rate_date := V_acct_rate_date;
1606 V_project_rate_type := V_acct_rate_type;
1607 V_project_exchange_rate := V_acct_exchange_rate;
1608
1609 V_projfunc_cost_rate_date := V_acct_rate_date;
1610 V_projfunc_cost_rate_type := V_acct_rate_type;
1611 V_projfunc_cost_exchange_rate := V_acct_exchange_rate;
1612
1613 -- print_message('P_denom_curr_code['||expenditure_item_rec.denom_currency_code);
1614 -- print_message('P_project_curr_code['||expenditure_item_rec.project_currency_code);
1615 -- print_message('P_acct_curr_code['||expenditure_item_rec.acct_currency_code);
1616 -- print_message('P_accounted_flag['||nvl(expenditure_item_rec.GL_Accounted_Flag,'N'));
1617 -- print_message('P_projfunc_curr_code['||expenditure_item_rec.projfunc_currency_code||']' );
1618 -- print_message('p_acct_rate_date ['||V_acct_rate_date||']');
1619 -- print_message('p_acct_rate_type ['||V_acct_rate_type ||']');
1620 -- print_message('p_acct_exch_rate ['||V_acct_exchange_rate ||']');
1621 -- print_message('p_project_rate_type ['||V_project_rate_type||']');
1622 -- print_message('p_project_exch_rate ['||V_project_rate_date||']');
1623 -- print_message('p_projfunc_cost_rate_date ['||V_projfunc_cost_rate_date||']');
1624 -- print_message('p_projfunc_cost_rate_type ['||V_projfunc_cost_rate_type||']');
1625 -- print_message('p_projfunc_cost_exch_rate ['||V_projfunc_cost_exchange_rate||']');
1626
1627
1628 END IF ; --3535935 hkulkarn
1629
1630 PA_MULTI_CURRENCY_TXN.GET_CURRENCY_AMOUNTS(
1631 p_project_id => null,
1632 p_exp_org_id => null,
1633 p_calling_module => null,
1634 P_task_id => expenditure_item_rec.task_id,
1635 P_Ei_date => expenditure_item_rec.expenditure_item_date,
1636 P_denom_raw_cost => expenditure_item_rec.denom_raw_cost,
1637 P_denom_curr_code => expenditure_item_rec.denom_currency_code,
1638 P_acct_curr_code => expenditure_item_rec.acct_currency_code,
1639 P_accounted_flag => nvl(expenditure_item_rec.GL_Accounted_Flag,'N'), /*Bug 1824407 */
1640 P_acct_rate_date => V_acct_rate_date,
1641 P_acct_rate_type => V_acct_rate_type,
1642 P_acct_exch_rate => V_acct_exchange_rate,
1643 P_acct_raw_cost => V_acct_raw_cost,
1644 P_project_curr_code => expenditure_item_rec.project_currency_code,
1645 P_project_rate_type => V_project_rate_type,
1646 P_project_rate_date => V_project_rate_date,
1647 P_project_exch_rate => V_project_exchange_rate,
1648 P_project_raw_cost => V_project_raw_cost,
1649 P_projfunc_curr_code => expenditure_item_rec.projfunc_currency_code,
1650 P_projfunc_cost_rate_type => V_projfunc_cost_rate_type,
1651 P_projfunc_cost_rate_date => V_projfunc_cost_rate_date,
1652 P_projfunc_cost_exch_rate => V_projfunc_cost_exchange_rate,
1653 P_projfunc_raw_cost => V_projfunc_raw_cost,
1654 P_system_linkage => expenditure_item_rec.system_linkage_function,
1655 P_status => V_status,
1656 P_stage => V_stage,
1657 P_Po_Line_ID => expenditure_item_rec.Po_Line_Id);
1658 IF P_DEBUG_MODE THEN
1659 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Call to PA_MULTI_CURRENCY_TXN.GET_CURRENCY_AMOUNTS');
1660
1661 END IF;
1662 /*
1663 * 2048868
1664 */
1665 IF ( expenditure_item_rec.system_linkage_function = 'ER' )
1666 THEN
1667 IF ( l_er_expenditure_id_tab.count > 0 )
1668 THEN
1669 IF P_DEBUG_MODE THEN
1670 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Size of the ER table [' || to_char(l_er_expenditure_id_tab.count) || ']');
1671 END IF;
1672 /*
1673 * The table is not expty.
1674 * Check whether this expenditure_id is already available in
1675 * the table. If it already exists, set l_exp_already_exists to 'Y'.
1676 */
1677 l_exp_already_exists := 'N' ;
1678 FOR LOOP_INDEX IN l_er_expenditure_id_tab.first..l_er_expenditure_id_tab.last
1679 LOOP
1680 IF P_DEBUG_MODE THEN
1681 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Comparing [' || to_char(expenditure_item_rec.expenditure_id) ||
1682 '] with [' || to_char(l_er_expenditure_id_tab(LOOP_INDEX)) || ']' );
1683 END IF;
1684 IF ( expenditure_item_rec.expenditure_id = l_er_expenditure_id_tab(LOOP_INDEX) )
1685 THEN
1686 l_exp_already_exists := 'Y' ;
1687 exit ;
1688 END IF ;
1689 END LOOP ;
1690 END IF;
1691
1692 IF P_DEBUG_MODE THEN
1693 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Already exists [' || l_exp_already_exists || ']');
1694 END IF;
1695 IF ( nvl(l_exp_already_exists, 'N') = 'N' )
1696 THEN
1697 /*
1698 * The current expenditure is not in the table.
1699 * So, add it.
1700 */
1701 l_er_expenditure_id_tab(l_er_exp_count) := expenditure_item_rec.expenditure_id ;
1702 l_exp_acct_exch_rate_tab(l_er_exp_count) := V_acct_exchange_rate ;
1703 IF P_DEBUG_MODE THEN
1704 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'New id [' || to_char(l_er_expenditure_id_tab(l_er_exp_count)) ||
1705 '] rate [' || to_char(l_exp_acct_exch_rate_tab(l_er_exp_count)) || ']' );
1706 END IF;
1707 l_er_exp_count := l_er_exp_count + 1 ;
1708 END IF;
1709 END IF ; -- 'ER'
1710 /*
1711 * 2048868
1712 */
1713
1714 END IF;
1715 /*
1716 * The output values are stored into array for later use in the update.
1717 *
1718 * Note: These assigments need not to be kept within the previous if statement
1719 * because the variables are initialized with the existing values
1720 * of the EIs before the MC Call. So even if MC call is not made
1721 * ( because of the existence of both the buckets ) the old
1722 * values will be assigned to the array elelemnts. So, final update
1723 * will update the columns with the existing values only and that is fine.
1724 */
1725 IF P_DEBUG_MODE THEN
1726 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before storing in Array ');
1727 END IF;
1728
1729 PA_CC_IDENT.DenomCurrCodeTab(v_loop_index) := expenditure_item_rec.denom_currency_code;
1730 PA_CC_IDENT.AcctRawCostTab(v_loop_index) := V_acct_raw_cost;
1731 /* This needs to be changed later bug951161 */
1732 PA_CC_IDENT.AcctRateDateTab(v_loop_index) := to_char(V_acct_rate_date,
1733 'dd-mon-yyyy hh:mi:ss');
1734 PA_CC_IDENT.AcctRateTypeTab(v_loop_index) := V_acct_rate_type;
1735 PA_CC_IDENT.AcctRateTab(v_loop_index) := V_acct_exchange_rate;
1736
1737 PA_CC_IDENT.ProjFuncRawCostTab(v_loop_index) := V_projfunc_raw_cost;
1738 PA_CC_IDENT.ProjFuncRateDateTab(v_loop_index) := to_char(V_projfunc_cost_rate_date,
1739 'dd-mon-yyyy hh:mi:ss');
1740 PA_CC_IDENT.ProjFuncRateTypeTab(v_loop_index) := V_projfunc_cost_rate_type;
1741 PA_CC_IDENT.ProjFuncRateTab(v_loop_index) := V_projfunc_cost_exchange_rate;
1742
1743 PA_CC_IDENT.ProjRawCostTab(v_loop_index) := V_project_raw_cost;
1744 /* This needs to be changed later bug951161 */
1745 PA_CC_IDENT.ProjRateDateTab(v_loop_index) := to_char(V_project_rate_date,
1746 'dd-mon-yyyy hh:mi:ss');
1747 PA_CC_IDENT.ProjRateTypeTab(v_loop_index) := V_project_rate_type;
1748 PA_CC_IDENT.ProjRateTab(v_loop_index) := V_project_exchange_rate;
1749 PA_CC_IDENT.StatusTab(v_loop_index) := V_status;
1750 IF P_DEBUG_MODE THEN
1751 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After storing in Array ');
1752 END IF;
1753 /*
1754 * Call the client extn. for creating related items
1755 * ( for labor )
1756 *
1757 * If Oracle error is retured ( v_cur_status < 0) then
1758 * an exception is raised and processing is halted. The
1759 * control goes back to the calling program and appropriate error
1760 * handling is done based on the output status variable as set.
1761 *
1762 * If application error is retured ( v_cur_status > 0) then
1763 * appropriate cost_dist_rejection_code is populated in the status array and
1764 * processing continues with the next record.
1765 * Here the same status variable as MC is reused.
1766 */
1767
1768 IF ( expenditure_item_rec.Source_Id IS NULL
1769 /*AND (NVL(expenditure_item_rec.net_zero, 'N') = 'N') */ /*Bug 4460518*/
1770 AND P_Sys_Link = 'LABOR') THEN
1771 IF P_DEBUG_MODE THEN
1772 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before call to PA_Costing_Client_Extns.Add_Transactions_Hook');
1773 END IF;
1774
1775 PA_Costing_Client_Extns.Add_Transactions_Hook(
1776 expenditure_item_rec.expenditure_item_id,
1777 expenditure_item_rec.system_linkage_function,
1778 v_cur_status);
1779 IF P_DEBUG_MODE THEN
1780 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After call to PA_Costing_Client_Extns.Add_Transactions_Hook');
1781 END IF;
1782 IF ( v_cur_status < 0 ) THEN
1783 P_Source := 'Client Extn';
1784 P_MC_IC_status := v_cur_status;
1785 RAISE E_local_exception;
1786 END IF;
1787 IF ( v_cur_status > 0 ) THEN
1788 PA_CC_IDENT.StatusTab(v_loop_index) := 'ADD_TRANSACTIONS_EXT_FAIL';
1789 END IF;
1790 END IF;
1791
1792 V_loop_index := V_loop_index + 1;
1793 END LOOP;
1794 END LOOP;
1795
1796
1797 /*
1798 * Subtract 1 from loop index to get the
1799 * actual no of times the previous loop is executed
1800 */
1801 V_loop_index := V_loop_index -1;
1802
1803 /*
1804 * Set the source. Its value is used by the calling program
1805 * only when the status <> 0
1806 */
1807 P_Source := 'IC Error';
1808
1809 /*
1810 * Call IC API to get the provider-receiver org
1811 * and cross-charge code
1812 */
1813 IF V_loop_index > 0 THEN
1814 IF P_DEBUG_MODE THEN
1815 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before call to PA_CC_IDENT.PA_CC_IDENTIFY_TXN');
1816 END IF;
1817 PA_CC_IDENT.PA_CC_IDENTIFY_TXN(
1818 P_ExpOrganizationIdTab => PA_CC_IDENT.ExpOrganizationIdTab,
1819 P_ExpOrgIdTab => PA_CC_IDENT.ExpOrgIdTab,
1820 P_ProjectIdTab => PA_CC_IDENT.ProjectIdTab,
1821 P_TaskIdTab => PA_CC_IDENT.TaskIdTab,
1822 P_ExpItemDateTab => PA_CC_IDENT.ExpItemDateTab,
1823 P_ExpItemIdTab => PA_CC_IDENT.ExpItemIdTab,
1824 P_PersonIdTab => PA_CC_IDENT.PersonIdTab,
1825 P_ExpTypeTab => PA_CC_IDENT.ExpTypeTab,
1826 P_SysLinkTab => PA_CC_IDENT.SysLinkTab,
1827 P_PrjOrganizationIdTab => PA_CC_IDENT.PrjOrganizationIdTab,
1828 P_PrjOrgIdTab => PA_CC_IDENT.PrjOrgIdTab,
1829 P_TransSourceTab => PA_CC_IDENT.TransSourceTab,
1830 P_NLROrganizationIdTab => PA_CC_IDENT.NLROrganizationIdTab,
1831 P_PrvdrLEIdTab => PA_CC_IDENT.PrvdrLEIdTab,
1832 P_RecvrLEIdTab => PA_CC_IDENT.RecvrLEIdTab,
1833 X_StatusTab => PA_CC_IDENT.StatusTab,
1834 X_CrossChargeTypeTab => PA_CC_IDENT.CrossChargeTypeTab,
1835 X_CrossChargeCodeTab => PA_CC_IDENT.CrossChargeCodeTab,
1836 X_PrvdrOrganizationIdTab => PA_CC_IDENT.PrvdrOrganizationIdTab,
1837 X_RecvrOrganizationIdTab => PA_CC_IDENT.RecvrOrganizationIdTab,
1838 X_RecvrOrgIdTab => PA_CC_IDENT.RecvrOrgIdTab,
1839 X_Error_Stage => V_Errorstage,
1840 X_Error_Code => V_Errorcode);
1841 IF P_DEBUG_MODE THEN
1842 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Call to PA_CC_IDENT.PA_CC_IDENTIFY_TXN');
1843 END IF;
1844 END IF;
1845
1846 /** To be uncommented after decing upon rates-model.
1847 ** CBGA Call to GetMappedToJobs ().
1848 **
1849 ** IF V_loop_index > 0 THEN
1850 ** pa_cc_utils.log_message('Before call to GetMappedToJobs');
1851 ** PA_Cross_Business_Grp.GetMappedToJobs (
1852 ** p_from_job_id_tab => JobIdTab,
1853 ** p_to_job_group_id_tab => JobGroupIdTab,
1854 ** x_to_job_id_tab => CostJobIdTab,
1855 ** x_status_code => V_Statuscode,
1856 ** x_error_stage_tab => ErrorStageTab,
1857 ** x_error_code_tab => ErrorCodeTab
1858 ** );
1859 ** pa_cc_utils.log_message('After Call to GetMappedToJobs');
1860 ** END IF;
1861 **
1862 **/
1863
1864 /*
1865 * Set the source. Its value is used by the calling program
1866 * only when the status <> 0
1867 */
1868 P_Source := 'Update Error';
1869 /*
1870 * Bug2048868
1871 */
1872 l_er_exp_count := l_er_exp_count - 1 ;
1873 IF P_DEBUG_MODE THEN
1874 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before updating Expenditures. Count to update [' || to_char(l_er_exp_count) || ']');
1875 END IF;
1876 IF ( l_er_exp_count > 0 )
1877 THEN
1878 FORALL i IN 1..l_er_exp_count
1879 UPDATE pa_expenditures exp
1880 SET exp.Acct_Exchange_rate = l_exp_acct_exch_rate_tab(i)
1881 WHERE exp.expenditure_id = l_er_expenditure_id_tab(i)
1882 ;
1883 END IF; -- l_er_exp_count
1884 IF P_DEBUG_MODE THEN
1885 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After updating Expenditures with exchange rate.');
1886 END IF;
1887 /*
1888 * End Bug2048868
1889 */
1890 /*
1891 * Final update statement to update all the relevant columns for
1892 * MC as well as IC. ( arrays are used for that )
1893 *
1894 * Note: The MC related column are updated only when the cost in the
1895 * appropriate currency is not available and the MC/IC API hasnot returned
1896 * any error.
1897 * The IC related columns are updated only when the MC/IC API hasnot returned
1898 * any error.
1899 *
1900 * Denom_Currency_Code shouldn't be null under normal circumstances,
1901 * for safety purpose it is set to accounting currency code if it's value
1902 * is null.
1903 */
1904 IF V_loop_index > 0 THEN
1905
1906
1907 IF P_DEBUG_MODE THEN
1908 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Final update statement');
1909 END IF;
1910
1911 /* added for bug#2919885 */
1912 FOR LOOP_INDEX IN 1..V_loop_index LOOP
1913 select count(*) into l_cc_dist_count_tab(loop_index)
1914 from pa_cc_dist_lines_all
1915 where expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index)
1916 and line_type = 'BL' ; /*Bug# 3184731 :Excluding line_type ='PC' here */
1917 END LOOP;
1918
1919 /* bug 12983087 Fetching Parent EIs Cost_distributed_flag */
1920 FOR LOOP_INDEX IN 1..V_loop_index LOOP
1921 BEGIN
1922 select ei1.cost_distributed_flag,ei1.Cc_Cross_Charge_Type,
1923 ei1.Cc_Prvdr_Organization_Id,ei1.Cc_Recvr_Organization_Id,
1924 ei1.Recvr_Org_Id
1925 into l_parent_cdf_tab(loop_index),l_parent_CCharge_Type(loop_index),
1926 l_parent_Cc_Prvdr_Org_Id(loop_index),l_parent_Cc_Recvr_Org_Id(loop_index),
1927 l_parent_Recvr_Org_Id(loop_index) from
1928 pa_expenditure_items_all ei1,pa_expenditure_items_all ei2
1929 where ei1.expenditure_item_id = ei2.adjusted_expenditure_item_id and
1930 ei2.expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index);
1931 EXCEPTION
1932 WHEN NO_DATA_FOUND THEN
1933 l_parent_cdf_tab(loop_index) := 'N';
1934 l_parent_CCharge_Type(loop_index) := NULL;
1935 l_parent_Cc_Prvdr_Org_Id(loop_index) := NULL;l_parent_Cc_Recvr_Org_Id(loop_index) := NULL;
1936 l_parent_Recvr_Org_Id(loop_index) := NULL;
1937 WHEN OTHERS THEN
1938 l_parent_cdf_tab(loop_index) := 'N';
1939 l_parent_CCharge_Type(loop_index) := NULL;
1940 l_parent_Cc_Prvdr_Org_Id(loop_index) := NULL;l_parent_Cc_Recvr_Org_Id(loop_index) := NULL;
1941 l_parent_Recvr_Org_Id(loop_index) := NULL;
1942 end;
1943 END LOOP;
1944
1945
1946 FORALL LOOP_INDEX IN 1..V_loop_index
1947
1948
1949 UPDATE Pa_Expenditure_Items ITEM
1950 SET ITEM.Denom_Currency_Code =
1951 DECODE(ITEM.Denom_Currency_Code, NULL,
1952 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1953 PA_CC_IDENT.DenomCurrCodeTab(loop_index),
1954 ITEM.Denom_Currency_Code),
1955 ITEM.Denom_Currency_Code),
1956
1957 ITEM.Cost_Dist_Rejection_Code =
1958 PA_CC_IDENT.StatusTab(loop_index),
1959
1960 /** To be uncommented after decing upon rates-model.
1961 ** CBGA StatusTab contains the status information for the call pa_cc_identify_txn.
1962 ** ErrorStageTab contains the status information for the call GetMappedToJobs.
1963 ** The rejection code is set - when either of them is NOT NULL.
1964 **
1965 ** ITEM.Cost_Dist_Rejection_Code =
1966 ** decode ( PA_CC_IDENT.StatusTab(loop_index), NULL,
1967 ** ErrorStageTab(loop_index) ,
1968 ** PA_CC_IDENT.StatusTab(loop_index)
1969 ** ),
1970 **/
1971 ITEM.Burden_Cost =
1972 DECODE(ITEM.Burden_Cost,NULL,
1973 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1974 DECODE(ITEM.System_Linkage_Function,'BTC',
1975 PA_CC_IDENT.ProjFuncRawCostTab(loop_index), /* Replaced ProjRawCostTab by ProjFuncRawCostTab for bug 3285759 */
1976 ITEM.Burden_Cost),
1977 ITEM.Burden_Cost),
1978 ITEM.Burden_Cost),
1979 /***** Added for 3285759 */
1980 ITEM.Project_Burdened_Cost =
1981 DECODE(ITEM.Project_Burdened_Cost,NULL,
1982 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1983 DECODE(ITEM.System_Linkage_Function,'BTC',
1984 PA_CC_IDENT.ProjRawCostTab(loop_index),
1985 ITEM.Project_Burdened_Cost),
1986 ITEM.Project_Burdened_Cost),
1987 ITEM.Project_Burdened_Cost),
1988 /***** Added for 3285759 End */
1989 ITEM.Acct_Burdened_Cost =
1990 DECODE(ITEM.Acct_Burdened_Cost,NULL,
1991 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
1992 DECODE(ITEM.System_Linkage_Function,'BTC',
1993 PA_CC_IDENT.AcctRawCostTab(loop_index),
1994 ITEM.Acct_Burdened_Cost),
1995 ITEM.Acct_Burdened_Cost),
1996 ITEM.Acct_Burdened_Cost),
1997 ITEM.Raw_Cost =
1998 DECODE(ITEM.Raw_Cost, NULL,
1999 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2000 DECODE(ITEM.System_Linkage_Function,'BTC',
2001 0,
2002 PA_CC_IDENT.ProjFuncRawCostTab(loop_index)),
2003 ITEM.Raw_Cost),
2004 ITEM.Raw_Cost),
2005 ITEM.ProjFunc_Cost_Exchange_Rate =
2006 DECODE(ITEM.Raw_Cost,NULL,
2007 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2008 PA_CC_IDENT.ProjFuncRateTab(loop_index),
2009 ITEM.ProjFunc_Cost_Exchange_Rate),
2010 ITEM.ProjFunc_Cost_exchange_Rate),
2011 ITEM.projfunc_cost_rate_Date =
2012 DECODE(ITEM.Raw_Cost,NULL,
2013 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2014 to_date(PA_CC_IDENT.ProjFuncRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
2015 ITEM.projfunc_cost_rate_Date),
2016 ITEM.projfunc_cost_rate_Date),
2017 ITEM.projfunc_cost_rate_Type =
2018 DECODE(ITEM.Raw_Cost,NULL,
2019 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2020 PA_CC_IDENT.ProjFuncRateTypeTab(loop_index),
2021 ITEM.projfunc_cost_rate_Type),
2022 ITEM.projfunc_cost_rate_Type),
2023 ITEM.Project_Raw_Cost =
2024 DECODE(ITEM.Project_Raw_Cost, NULL,
2025 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2026 DECODE(ITEM.System_Linkage_Function,'BTC',
2027 0,
2028 PA_CC_IDENT.ProjRawCostTab(loop_index)),
2029 ITEM.Project_Raw_Cost),
2030 ITEM.Project_Raw_Cost),
2031 ITEM.Project_Exchange_Rate =
2032 DECODE(ITEM.Project_Raw_Cost,NULL,
2033 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2034 PA_CC_IDENT.ProjRateTab(loop_index),
2035 ITEM.Project_Exchange_Rate),
2036 ITEM.Project_exchange_Rate),
2037 ITEM.Project_Rate_Date =
2038 DECODE(ITEM.Project_Raw_Cost,NULL,
2039 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2040 to_date(PA_CC_IDENT.ProjRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
2041 ITEM.Project_Rate_Date),
2042 ITEM.Project_Rate_Date),
2043 ITEM.Project_Rate_Type =
2044 DECODE(ITEM.Project_Raw_Cost,NULL,
2045 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2046 PA_CC_IDENT.ProjRateTypeTab(loop_index),
2047 ITEM.Project_Rate_Type),
2048 ITEM.Project_Rate_Type),
2049 ITEM.Acct_Raw_Cost =
2050 DECODE(ITEM.Acct_Raw_Cost,NULL,
2051 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2052 DECODE(ITEM.System_Linkage_Function,'BTC',
2053 0,
2054 PA_CC_IDENT.AcctRawCostTab(loop_index)),
2055 ITEM.Acct_Raw_Cost),
2056 ITEM.Acct_Raw_Cost),
2057 ITEM.Acct_Exchange_Rate =
2058 DECODE(ITEM.Acct_Raw_Cost,NULL,
2059 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2060 PA_CC_IDENT.AcctRateTab(loop_index),
2061 ITEM.Acct_Exchange_Rate),
2062 ITEM.Acct_Exchange_Rate),
2063 ITEM.Acct_Rate_Date =
2064 DECODE(ITEM.Acct_Raw_Cost,NULL,
2065 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2066 to_date(PA_CC_IDENT.AcctRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
2067 ITEM.Acct_Rate_Date),
2068 ITEM.Acct_Rate_Date),
2069 ITEM.Acct_Rate_Type =
2070 DECODE(ITEM.Acct_Raw_Cost,NULL,
2071 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2072 PA_CC_IDENT.AcctRateTypeTab(loop_index),
2073 ITEM.Acct_Rate_Type),
2074 ITEM.Acct_Rate_Type),
2075 ITEM.Cc_Cross_Charge_Code =
2076 Decode(ITEM.Cc_Cross_Charge_Code,'P', -- Added Decode wrapper to update only for P case 3173932
2077 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2078 PA_CC_IDENT.CrossChargeCodeTab(loop_index),
2079 ITEM.Cc_Cross_Charge_Code),ITEM.Cc_Cross_Charge_Code),
2080 ITEM.Cc_Cross_Charge_Type =
2081 DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_CCharge_Type(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
2082 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2083 PA_CC_IDENT.CrossChargeTypeTab(loop_index),
2084 ITEM.Cc_Cross_Charge_Type)),
2085 ITEM.Cc_Bl_Distributed_Code =
2086 DECODE(l_parent_cdf_tab(loop_index),'Y','N', /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
2087 DECODE(PA_CC_IDENT.StatusTab(loop_index), NULL,
2088 DECODE(PA_CC_IDENT.CrossChargeCodeTab(loop_index),'B',
2089 'N',
2090 DECODE(l_cc_dist_count_tab(loop_index),0,'X','N')), /* bug#2919885 */
2091 ITEM.Cc_Bl_Distributed_Code)),
2092 ITEM.Cc_IC_Processed_Code =
2093 DECODE(l_parent_cdf_tab(loop_index),'Y','N', /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
2094 DECODE(PA_CC_IDENT.StatusTab(loop_index), NULL,
2095 DECODE(PA_CC_IDENT.CrossChargeCodeTab(loop_index),'I',
2096 'N',
2097 'X'),
2098 ITEM.Cc_IC_processed_Code)),
2099 ITEM.Cc_Prvdr_Organization_Id =
2100 DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_Cc_Prvdr_Org_Id(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
2101 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2102 PA_CC_IDENT.PrvdrOrganizationIdTab(loop_index),
2103 ITEM.Cc_Prvdr_Organization_Id)),
2104 ITEM.Cc_Recvr_Organization_Id =
2105 DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_Cc_Recvr_Org_Id(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
2106 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2107 PA_CC_IDENT.RecvrOrganizationIdTab(loop_index),
2108 ITEM.Cc_Recvr_Organization_Id)),
2109 ITEM.Recvr_Org_Id =
2110 DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_Recvr_Org_Id(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
2111 DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
2112 PA_CC_IDENT.RecvrOrgIdTab(loop_index),
2113 ITEM.Recvr_Org_Id))
2114
2115 /** To be uncommented after decing upon rates-model.
2116 ** CBGA Updating Cost_Job_Id in EI.
2117 ** ITEM.cost_job_id =
2118 ** DECODE(ErrorStageTab(loop_index),NULL,
2119 ** CostJobIdTab(loop_index),
2120 ** ITEM.cost_job_id)
2121 **/
2122 WHERE ITEM.Expenditure_Item_Id = PA_CC_IDENT.ExpItemIdTab(loop_index);
2123
2124 /*Code Changes for Bug No.2984871 start */
2125 P_Update_Count := SQL%ROWCOUNT;
2126 /*Code Changes for Bug No.2984871 end */
2127
2128 /* bug 12983087 Updating EI table again for the reversal EIs from parent if distributed in the same run.*/
2129 FOR LOOP_INDEX IN 1..V_loop_index LOOP
2130 l_parent_cdf_tab(loop_index) := 'N';
2131 l_parent_CCharge_Type(loop_index) := NULL;
2132 l_parent_Cc_Prvdr_Org_Id(loop_index) := NULL;l_parent_Cc_Recvr_Org_Id(loop_index) := NULL;
2133 l_parent_Recvr_Org_Id(loop_index) := NULL;
2134 BEGIN
2135 select ei1.cost_distributed_flag,ei1.Cc_Cross_Charge_Type,
2136 ei1.Cc_Prvdr_Organization_Id,ei1.Cc_Recvr_Organization_Id,
2137 ei1.Recvr_Org_Id
2138 into l_parent_cdf_tab(loop_index),l_parent_CCharge_Type(loop_index),
2139 l_parent_Cc_Prvdr_Org_Id(loop_index),l_parent_Cc_Recvr_Org_Id(loop_index),
2140 l_parent_Recvr_Org_Id(loop_index)
2141 from pa_expenditure_items_all ei1,pa_expenditure_items_all ei2
2142 where ei1.expenditure_item_id = ei2.adjusted_expenditure_item_id and
2143 ei2.expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index)
2144 AND ei1.Request_id = P_request_id;
2145 EXCEPTION
2146 WHEN NO_DATA_FOUND THEN
2147 l_parent_cdf_tab(loop_index) := 'N';
2148 l_parent_CCharge_Type(loop_index) := NULL;
2149 l_parent_Cc_Prvdr_Org_Id(loop_index) := NULL;l_parent_Cc_Recvr_Org_Id(loop_index) := NULL;
2150 l_parent_Recvr_Org_Id(loop_index) := NULL;
2151 WHEN OTHERS THEN
2152 l_parent_cdf_tab(loop_index) := 'N';
2153 l_parent_CCharge_Type(loop_index) := NULL;
2154 l_parent_Cc_Prvdr_Org_Id(loop_index) := NULL;l_parent_Cc_Recvr_Org_Id(loop_index) := NULL;
2155 l_parent_Recvr_Org_Id(loop_index) := NULL;
2156 end;
2157 IF P_DEBUG_MODE THEN
2158 pa_cc_utils.log_message('l_parent_cdf_tab(loop_index):'||l_parent_cdf_tab(loop_index)||',l_parent_CCharge_Type(loop_index):'||l_parent_CCharge_Type(loop_index)||
2159 ',l_parent_Cc_Prvdr_Org_Id(loop_index):'||l_parent_Cc_Prvdr_Org_Id(loop_index)||',l_parent_Cc_Recvr_Org_Id(loop_index):'||l_parent_Cc_Recvr_Org_Id(loop_index)||
2160 ',l_parent_Recvr_Org_Id(loop_index)'||l_parent_Recvr_Org_Id(loop_index));
2161 END IF;
2162
2163 END LOOP;
2164
2165 FORALL LOOP_INDEX IN 1..V_loop_index
2166 UPDATE Pa_Expenditure_Items ITEM
2167 SET ITEM.Cc_Cross_Charge_Type =
2168 DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_CCharge_Type(loop_index), ITEM.Cc_Cross_Charge_Type),
2169 ITEM.Cc_Prvdr_Organization_Id =
2170 DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_Cc_Prvdr_Org_Id(loop_index), ITEM.Cc_Prvdr_Organization_Id),
2171 ITEM.Cc_Recvr_Organization_Id =
2172 DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_Cc_Recvr_Org_Id(loop_index), ITEM.Cc_Recvr_Organization_Id),
2173 ITEM.Recvr_Org_Id =
2174 DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_Recvr_Org_Id(loop_index), ITEM.Recvr_Org_Id)
2175 WHERE ITEM.Expenditure_Item_Id = PA_CC_IDENT.ExpItemIdTab(loop_index)
2176 AND ITEM.adjusted_expenditure_item_id is NOT NULL;
2177
2178 END IF;
2179 IF P_DEBUG_MODE THEN
2180 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Final Update Statement');
2181 END IF;
2182 /*
2183 * No of records updated is set to the output parameter.
2184 */
2185 /* Commented for Bug 2984871
2186 P_Update_Count := SQL%ROWCOUNT; */
2187
2188 IF P_DEBUG_MODE THEN
2189 pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'End ');
2190 END IF;
2191 pa_cc_utils.reset_curr_function ;
2192
2193 EXCEPTION
2194 /*
2195 * This exception is raised in case of Oracle error in
2196 * the client extn. The control is retured back to the calling
2197 * program. Error handling to be done over there.
2198 */
2199 WHEN E_local_exception THEN
2200 RAISE;
2201 /*
2202 * Commented during bug 1943559
2203 * NULL;
2204 */
2205
2206 /*
2207 * Any other error encountered either in MC or IC processing.
2208 * Set the status variable and control is returned back to the
2209 * calling program.
2210 * Note: Source is already populated in the processing part.
2211 */
2212 WHEN OTHERS THEN
2213 IF V_errorcode IS NOT NULL THEN
2214 P_MC_IC_STATUS := V_errorcode;
2215 ELSE
2216 P_MC_IC_Status := SQLCODE;
2217 END IF;
2218
2219 END Perform_MC_and_IC_processing;
2220
2221 /*---------------------------------------------------------------------*/
2222 PROCEDURE get_proj_rate_type ( P_task_id IN NUMBER
2223 ,p_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL
2224 ,p_structure_version_id IN NUMBER DEFAULT NULL
2225 ,p_calling_module IN VARCHAR2
2226 ,P_project_currency_code IN OUT NOCOPY VARCHAR2
2227 ,P_project_rate_type IN OUT NOCOPY VARCHAR2
2228 )
2229 IS
2230
2231 BEGIN
2232
2233 --
2234 -- This procedure derives project currency code and
2235 -- project currency conversion rate type
2236 --
2237 -- Logic: if the user provides a proj_rate_type, use it.
2238 -- Otherwise derive it from the task, if project_rate_type is not
2239 -- defined at task level then get it from project level. If project_rate_type
2240 -- is not defined at project level also then derive the
2241 -- project_rate_type value from default_rate_type column in
2242 -- project owning operating units implementation options table.
2243 -- proj_currency_code is derived from projects table
2244
2245 IF (p_calling_module <> 'WORKPLAN') THEN
2246
2247 SELECT proj.project_currency_code,
2248 NVL(P_project_rate_type, NVL(NVL(task.project_rate_type,
2249 proj.project_rate_type), imp.default_rate_type))
2250 INTO P_project_currency_code,
2251 P_project_rate_type
2252 FROM pa_projects_all proj,
2253 pa_tasks task,
2254 pa_implementations_all imp
2255 WHERE proj.project_id = task.project_id
2256 AND task.task_id = P_task_id
2257 AND proj.org_id = imp.org_id;
2258
2259 ELSE
2260
2261 BEGIN
2262
2263 SELECT proj.project_currency_code,
2264 NVL(P_project_rate_type, NVL(NVL(task.project_rate_type,
2265 proj.project_rate_type), imp.default_rate_type))
2266 INTO P_project_currency_code,
2267 P_project_rate_type
2268 FROM pa_projects_all proj,
2269 pa_tasks task,
2270 pa_map_wp_to_fin_tasks_v map_wp_fin,
2271 pa_implementations_all imp
2272 WHERE proj.project_id = p_project_id
2273 AND task.task_id = map_wp_fin.mapped_fin_task_id
2274 AND map_wp_fin.proj_element_id = p_task_id
2275 AND map_wp_fin.parent_structure_version_id = p_structure_version_id
2276 AND proj.org_id = imp.org_id;
2277
2278 EXCEPTION
2279 WHEN NO_DATA_FOUND THEN
2280 SELECT proj.project_currency_code,
2281 NVL(NVL(P_project_rate_type, proj.project_rate_type)
2282 , imp.default_rate_type)
2283 INTO P_project_currency_code,
2284 P_project_rate_type
2285 FROM pa_projects_all proj,
2286 pa_implementations_all imp
2287 WHERE proj.project_id = p_project_id
2288 AND proj.org_id = imp.org_id;
2289 END ; -- anonymous
2290
2291 END IF;
2292
2293
2294 EXCEPTION
2295 WHEN no_data_found THEN
2296 P_project_currency_code := NULL;
2297 P_project_rate_type := NULL;
2298
2299 WHEN others THEN
2300 RAISE ;
2301
2302 END get_proj_rate_type ;
2303
2304 /*---------------------------------------------------------------------*/
2305 PROCEDURE get_proj_rate_date ( P_task_id IN NUMBER ,
2306 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL ,
2307 P_EI_date IN DATE ,
2308 p_structure_version_id IN NUMBER DEFAULT NULL,
2309 p_calling_module IN VARCHAR2,
2310 P_project_rate_date IN OUT NOCOPY DATE )
2311 IS
2312
2313 BEGIN
2314
2315 --
2316 -- This procedure derives project currency conversion rate date
2317 --
2318 -- Logic: If user provides a project currency conversion date, Use it.
2319 -- Otherwise derive it from task( identified bt P_task_id),
2320 -- if project_rate_date is not defined at task level then derive it from
2321 -- projects table. If the project_rate_date is not defined at project
2322 -- level also then the proj_rate_date will be derived using the
2323 -- default_rate_date_code from expenditure operating units implementation
2324 -- options. If the default_rate_date_code is E then return the expenditure
2325 -- item date(P_EI_date), if default_rate_date_code is P then return the
2326 -- PA period ending date.
2327
2328 IF ( P_project_rate_date IS NULL )
2329 THEN
2330 IF (p_calling_module <> 'WORKPLAN')
2331 THEN
2332 SELECT NVL(NVL(task.project_rate_date,
2333 proj.project_rate_date),
2334 DECODE(imp.default_rate_date_code,
2335 'E', P_EI_date, 'P',
2336 pa_utils2.get_pa_date(P_EI_date,
2337 sysdate, imp.org_id))) /**CBGA**/
2338 INTO P_project_rate_date
2339 FROM pa_projects_all proj,
2340 pa_tasks task,
2341 pa_implementations_all imp
2342 WHERE task.task_id = P_task_id
2343 AND proj.project_id = task.project_id
2344 AND nvl(proj.org_id, -99) = nvl(imp.org_id, -99);
2345 ELSE
2346 BEGIN
2347 SELECT task.project_rate_date
2348 INTO P_project_rate_date
2349 FROM pa_tasks task
2350 ,pa_map_wp_to_fin_tasks_v map_wp_fin
2351 WHERE task.task_id = map_wp_fin.mapped_fin_task_id
2352 AND map_wp_fin.proj_element_id = p_task_id
2353 AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
2354 EXCEPTION
2355 WHEN NO_DATA_FOUND THEN
2356 NULL;
2357 END ; -- anonymous
2358 IF ( P_project_rate_date IS NULL )
2359 THEN
2360 SELECT NVL(proj.project_rate_date,
2361 DECODE(imp.default_rate_date_code,
2362 'E', P_EI_date, 'P',
2363 pa_utils2.get_pa_date(P_EI_date,
2364 sysdate, imp.org_id)))
2365 INTO P_project_rate_date
2366 FROM pa_projects_all proj
2367 ,pa_implementations_all imp
2368 WHERE proj.project_id = p_project_id
2369 AND nvl(proj.org_id, -99) = nvl(imp.org_id, -99);
2370 END IF;
2371 END IF; -- calling_module
2372 END IF ;
2373
2374
2375 EXCEPTION
2376 WHEN no_data_found THEN
2377 P_project_rate_date := NULL ;
2378
2379 WHEN others THEN
2380 P_project_rate_date := NULL ;
2381 RAISE ;
2382
2383 END get_proj_rate_date ;
2384
2385
2386 /** The following new parameters are added for the FI changes
2387 * p_project_id IN pa_projects_all.project_id%type
2388 * p_exp_org_id IN pa_projects_all.org_id%type
2389 **/
2390 PROCEDURE get_currency_attributes
2391 (P_project_id IN pa_projects_all.project_id%type default NULL,
2392 P_exp_org_id IN pa_projects_all.org_id%type default NULL,
2393 P_task_id IN pa_expenditure_items_all.task_id%TYPE,
2394 P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
2395 P_calling_module IN VARCHAR2,
2396 P_denom_curr_code IN pa_expenditure_items_all.denom_currency_code%TYPE,
2397 P_accounted_flag IN VARCHAR2 DEFAULT 'N',
2398 P_acct_curr_code IN pa_expenditure_items_all.acct_currency_code%TYPE,
2399 X_acct_rate_date IN OUT NOCOPY pa_expenditure_items_all.acct_rate_date%TYPE,
2400 X_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE,
2401 X_acct_exch_rate IN OUT NOCOPY pa_expenditure_items_all.acct_exchange_rate%TYPE,
2402 P_project_curr_code IN pa_expenditure_items_all.project_currency_code%TYPE,
2403 X_project_rate_date IN OUT NOCOPY pa_expenditure_items_all.project_rate_date%TYPE,
2404 X_project_rate_type IN OUT NOCOPY pa_expenditure_items_all.project_rate_type%TYPE ,
2405 X_project_exch_rate IN OUT NOCOPY pa_expenditure_items_all.project_exchange_rate%TYPE,
2406 P_projfunc_curr_code IN pa_expenditure_items_all.projfunc_currency_code%TYPE,
2407 X_projfunc_cost_rate_date IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_date%TYPE,
2408 X_projfunc_cost_rate_type IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_type%TYPE ,
2409 X_projfunc_cost_exch_rate IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_exchange_rate%TYPE,
2410 P_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE,
2411 P_structure_version_id IN NUMBER DEFAULT NULL,
2412 X_status OUT NOCOPY VARCHAR2,
2413 X_stage OUT NOCOPY NUMBER)
2414 IS
2415
2416 l_dummy_char varchar2(100);
2417 l_dummy_date Date;
2418
2419
2420 TYPE UserSuppliedType IS RECORD (
2421 acct_rate_type VARCHAR2(1) := 'N'
2422 ,acct_rate_date VARCHAR2(1) := 'N'
2423 ,projfunc_cost_rate_type VARCHAR2(1) := 'N'
2424 ,projfunc_cost_rate_date VARCHAR2(1) := 'N');
2425
2426 usersupplied UserSuppliedType;
2427
2428 l_temp_acct_rate_date date;
2429 l_temp_acct_rate_type varchar2(100);
2430 l_temp_acct_exch_rate number;
2431 l_temp_project_rate_date date;
2432 l_temp_project_rate_type varchar2(100);
2433 l_temp_project_exch_rate number;
2434 l_temp_projfunc_cost_rate_date date;
2435 l_temp_projfunc_cost_rate_type varchar2(100);
2436 l_temp_projfunc_cost_exch_rate number;
2437
2438
2439 --------------------------------------
2440 --Forward bodies
2441
2442 ----------------------------------------------------------------------
2443 Procedure derive_project_attributes( P_task_id IN pa_expenditure_items_all.task_id%TYPE
2444 ,P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL
2445 ,P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2446 ,P_structure_version_id IN NUMBER DEFAULT NULL
2447 ,P_calling_module IN VARCHAR2
2448 ,x_project_rate_type IN OUT NOCOPY pa_expenditure_items_all.project_rate_type%TYPE
2449 ,x_project_rate_date IN OUT NOCOPY pa_expenditure_items_all.project_rate_date%TYPE
2450 )
2451 is
2452
2453 l_char_dummy pa_expenditure_items_all.project_currency_code%TYPE;
2454
2455 begin
2456
2457 --dbms_output.put_line('deriving project attributes');
2458 /*
2459 * Project_rate_type.
2460 */
2461 IF ( x_project_rate_type IS NULL )
2462 THEN --{
2463 pa_multi_currency_txn.get_proj_rate_type( P_task_id => P_task_id
2464 ,p_project_id => p_project_id
2465 ,p_structure_version_id => p_structure_version_id
2466 ,p_calling_module => p_calling_module
2467 ,P_project_currency_code => l_char_dummy
2468 ,P_project_rate_type => x_project_rate_type
2469 );
2470 END IF ; --} x_project_rate_type IS NULL
2471
2472 /*
2473 * Project_rate_date.
2474 */
2475 IF ( x_project_rate_date IS NULL )
2476 THEN --{
2477 pa_multi_currency_txn.get_proj_rate_date( P_task_id => P_task_id
2478 ,P_project_id => P_project_id
2479 ,P_ei_date => P_ei_date
2480 ,P_structure_version_id => p_structure_version_id
2481 ,P_calling_module => p_calling_module
2482 ,P_project_rate_date => x_project_rate_date
2483 );
2484 END IF ; --} x_project_rate_date IS NULL
2485 /***
2486 dbms_output.put_line('t [' || to_char(P_task_id) ||
2487 '] dt [' || to_char(P_ei_date) ||
2488 '] cm [' || P_calling_module ||
2489 '] prt [' || x_project_rate_type ||
2490 '] prd [' || to_char(x_project_rate_date) || ']');
2491 *********/
2492 end derive_project_attributes ;
2493
2494 -----------------------------------------------------------------------
2495 procedure derive_acct_attributes( P_calling_module IN VARCHAR2
2496 ,P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2497 ,P_attribute IN VARCHAR2
2498 ,x_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
2499 ,x_acct_rate_date IN OUT NOCOPY pa_expenditure_items_all.acct_rate_date%TYPE
2500 )
2501 is
2502 begin
2503
2504 IF ( P_attribute = 'TYPE' OR P_attribute = 'BOTH' )
2505 THEN
2506 x_acct_rate_type := NVL(x_acct_rate_type, pa_multi_currency.G_rate_type);
2507 END IF; -- P_attribute = 'TYPE'
2508
2509 IF ( P_attribute = 'DATE' OR P_attribute = 'BOTH' )
2510 THEN
2511 IF ( P_calling_module = 'TRANSFER' )
2512 THEN --{
2513
2514 pa_multi_currency_txn.get_default_acct_rate_date( P_ei_date => P_ei_date
2515 ,P_acct_rate_date => X_acct_rate_date
2516 );
2517
2518 ELSE --}{
2519
2520 pa_multi_currency_txn.get_acct_rate_date( P_ei_date => P_ei_date
2521 ,P_acct_rate_date => X_acct_rate_date
2522 ) ;
2523
2524 END IF; --} End P_calling_module = 'TRANSFER'
2525 END IF; -- P_attribute = 'DATE'
2526 end derive_acct_attributes ;
2527
2528 -----------------------------------------------------------------------
2529 procedure derive_projfunc_attributes
2530 ( P_calling_module IN VARCHAR2
2531 ,P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2532 ,P_task_id IN pa_expenditure_items_all.task_id%TYPE
2533 ,P_project_id IN pa_projects_all.project_id%TYPE
2534 ,P_attribute IN VARCHAR2
2535 ,P_structure_version_id IN NUMBER DEFAULT NULL
2536 ,x_projfunc_cost_rate_type IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_type%TYPE
2537 ,x_projfunc_cost_rate_date IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_date%TYPE
2538 )
2539 is
2540
2541 l_char_dummy pa_expenditure_items_all.projfunc_currency_code%TYPE;
2542
2543 begin
2544
2545 --dbms_output.put_line('deriving projfunc attributes');
2546 IF ( P_attribute = 'TYPE' OR P_attribute = 'BOTH')
2547 THEN --{
2548 pa_multi_currency_txn.get_projfunc_cost_rate_type
2549 ( P_task_id =>P_task_id ,
2550 P_project_id => p_project_id ,
2551 P_structure_version_id => p_structure_version_id ,
2552 P_calling_module => p_calling_module ,
2553 P_projfunc_currency_code =>l_char_dummy ,
2554 P_projfunc_cost_rate_type =>x_projfunc_cost_rate_type
2555 ) ;
2556 END IF; --} P_attribute = 'TYPE' OR P_attribute = 'BOTH'
2557
2558 IF ( P_attribute = 'DATE' OR P_attribute = 'BOTH' )
2559 THEN --{
2560 IF ( P_calling_module = 'TRANSFER' ) THEN
2561
2562 pa_multi_currency_txn.get_def_projfunc_cst_rate_date
2563 ( P_task_id =>P_task_id ,
2564 P_project_id => p_project_id ,
2565 P_structure_version_id => p_structure_version_id ,
2566 P_calling_module => p_calling_module ,
2567 P_ei_date =>P_ei_date ,
2568 P_projfunc_cost_rate_date =>x_projfunc_cost_rate_date
2569 ) ;
2570 ELSE --}{
2571
2572 --dbms_output.put_line('calling get_projfunc_cost_rate_date');
2573
2574 pa_multi_currency_txn.get_projfunc_cost_rate_date
2575 ( P_task_id =>P_task_id ,
2576 P_project_id =>P_project_id ,
2577 P_ei_date =>P_ei_date ,
2578 P_structure_version_id =>p_structure_version_id ,
2579 P_calling_module =>p_calling_module ,
2580 P_projfunc_cost_rate_date =>x_projfunc_cost_rate_date
2581 ) ;
2582
2583 END IF; --} end P_calling_module = 'TRANSFER'
2584 END IF; --} P_attribute = 'DATE' OR P_attribute = 'BOTH'
2585
2586 /*********
2587 dbms_output.put_line('t [' || to_char(P_task_id) ||
2588 '] dt [' || to_char(P_ei_date) ||
2589 '] cm [' || P_calling_module ||
2590 '] att [' || P_attribute ||
2591 '] pfrt [' || x_projfunc_cost_rate_type ||
2592 '] pfrd [' || to_char(x_projfunc_cost_rate_date) || ']');
2593 *****/
2594
2595 end derive_projfunc_attributes ;
2596 --Forward bodies end
2597 --------------------------------------
2598
2599 BEGIN
2600
2601 l_temp_acct_rate_date := x_acct_rate_date;
2602 l_temp_acct_rate_type := x_acct_rate_type;
2603 l_temp_acct_exch_rate := x_acct_exch_rate;
2604 l_temp_project_rate_date := x_project_rate_date;
2605 l_temp_project_rate_type := x_project_rate_type;
2606 l_temp_project_exch_rate := x_project_exch_rate;
2607 l_temp_projfunc_cost_rate_date := x_projfunc_cost_rate_date;
2608 l_temp_projfunc_cost_rate_type := x_projfunc_cost_rate_type;
2609 l_temp_projfunc_cost_exch_rate := x_projfunc_cost_exch_rate;
2610
2611 IF ( P_projfunc_curr_code = P_acct_curr_code )
2612 THEN -- {
2613 IF ( P_projfunc_curr_code = P_denom_curr_code )
2614 THEN --{
2615 X_acct_rate_date := NULL;
2616 X_acct_rate_type := NULL;
2617 X_acct_exch_rate := NULL;
2618
2619 X_projfunc_cost_rate_date := NULL ;
2620 X_projfunc_cost_rate_type := NULL ;
2621 X_projfunc_cost_exch_rate := NULL ;
2622 ELSE --}{
2623
2624 IF ( P_accounted_flag = 'Y' )
2625 THEN -- {
2626 /*
2627 * At this point it is assumed that - if the txn is accounted
2628 * the account attributes will be NOT NULL.
2629 */
2630 X_projfunc_cost_rate_date := X_acct_rate_date ;
2631 X_projfunc_cost_rate_type := X_acct_rate_type ;
2632 X_projfunc_cost_exch_rate := X_acct_exch_rate ;
2633
2634 END IF; -- } P_accounted_flag = 'Y'
2635
2636 IF ( P_system_linkage = 'ER' AND P_accounted_flag <> 'Y' )
2637 THEN -- {
2638 /*
2639 * IF its an Expense Report, Functional gets the highest precedence.
2640 */
2641 IF ( X_acct_rate_type IS NULL )
2642 THEN --{
2643
2644 IF p_calling_module = 'FORECAST' Then
2645 derive_fi_curr_attributes
2646 ( P_project_id => p_project_id
2647 ,P_exp_org_id => p_exp_org_id
2648 ,P_ei_date => p_ei_date
2649 ,P_attribute => 'FORECAST'
2650 ,x_project_rate_type => l_dummy_char
2651 ,x_project_rate_date => l_dummy_date
2652 ,x_projfunc_cost_rate_type => l_dummy_char
2653 ,x_projfunc_cost_rate_date => l_dummy_date
2654 ,x_acct_rate_type => X_acct_rate_type
2655 ,x_acct_rate_date => l_dummy_date --X_acct_rate_date
2656 );
2657 Else
2658 derive_acct_attributes
2659 ( P_calling_module => P_calling_module
2660 ,P_ei_date => P_ei_date
2661 ,P_attribute => 'TYPE'
2662 ,x_acct_rate_type => X_acct_rate_type
2663 ,x_acct_rate_date=> X_acct_rate_date
2664 );
2665 End If;
2666
2667 ELSE -- }{ X_acct_rate_type IS NOT NULL
2668 usersupplied.acct_rate_type := 'Y' ;
2669 END IF; --} X_acct_rate_type IS NULL
2670 /*
2671 * For Expense Report, Project Functional is overridden by the Functional
2672 * even if its user supplied.
2673 */
2674 X_projfunc_cost_rate_type := X_acct_rate_type ;
2675
2676 IF ( X_acct_rate_date IS NULL)
2677 THEN --{
2678 IF p_calling_module = 'FORECAST' Then
2679 derive_fi_curr_attributes
2680 ( P_project_id => p_project_id
2681 ,P_exp_org_id => p_exp_org_id
2682 ,P_ei_date => p_ei_date
2683 ,P_attribute => 'FORECAST'
2684 ,x_project_rate_type => l_dummy_char
2685 ,x_project_rate_date => l_dummy_date
2686 ,x_projfunc_cost_rate_type => l_dummy_char
2687 ,x_projfunc_cost_rate_date => l_dummy_date
2688 ,x_acct_rate_type => l_dummy_char --X_acct_rate_type
2689 ,x_acct_rate_date => X_acct_rate_date
2690 );
2691 Else
2692
2693 derive_acct_attributes
2694 ( P_calling_module => P_calling_module
2695 ,P_ei_date => P_ei_date
2696 ,P_attribute => 'DATE'
2697 ,x_acct_rate_type => X_acct_rate_type
2698 ,x_acct_rate_date=> X_acct_rate_date
2699 );
2700 End If;
2701 ELSE -- }{ X_acct_rate_date IS NOT NULL
2702 usersupplied.acct_rate_date := 'Y' ;
2703 END IF; --} X_acct_rate_date IS NULL
2704 X_projfunc_cost_rate_date := X_acct_rate_date ;
2705
2706 IF ( X_acct_rate_type = 'User' )
2707 THEN --{
2708 IF ( pa_multi_currency.is_user_rate_type_allowed(
2709 P_from_currency => P_denom_curr_code,
2710 P_to_currency => P_acct_curr_code,
2711 P_conversion_date => X_acct_rate_date)='Y'
2712 )
2713 THEN --{
2714 IF (X_acct_exch_rate IS NOT NULL)
2715 THEN --{
2716 X_projfunc_cost_exch_rate := X_acct_exch_rate ;
2717 ELSE --}{
2718 X_status := 'PA_ACCT_USER_RATE_NOT_DEFINED' ;
2719 RETURN ;
2720 END IF; --}
2721 ELSE -- }{
2722 X_status := 'PA_NO_ACCT_USER_RATE_TYPE';
2723 RETURN ;
2724 END IF; --}
2725 END IF; --} X_acct_rate_type = 'User'
2726 END IF; -- } P_system_linkage = 'ER' AND P_accounted_flag <> 'Y'
2727 print_message('account flag <> N and syslinkage <> ER');
2728 IF ( NVL(P_accounted_flag, 'N') <> 'Y' AND P_system_linkage <> 'ER' )
2729 THEN --{
2730 IF ( X_projfunc_cost_rate_type IS NOT NULL )
2731 THEN --{
2732 /*
2733 * Functional Attribute is overridden by Project Functional -
2734 * if Project Functional is supplied.
2735 */
2736 IF ( X_acct_rate_type IS NOT NULL ) /* Added for bug:10070224 */
2737 THEN --{
2738 usersupplied.acct_rate_type := 'Y' ;
2739 X_projfunc_cost_rate_type := X_acct_rate_type ;
2740 ELSE
2741 usersupplied.projfunc_cost_rate_type := 'Y' ;
2742 X_acct_rate_type := X_projfunc_cost_rate_type ;
2743 END IF;
2744 ELSE -- }{ X_projfunc_cost_rate_type IS NULL
2745 /*
2746 * <2822867> If Functional Rate Type is available, override Project Functional Rate Type
2747 * with it - but only for non-Timecards. For Time-cards derive Project
2748 * Functional attributes afresh.
2749 * <7680781> No need of putting the check for ST,OT transactions here, if the acct_rate is
2750 * passed we need to use the same for calculating projfunc rate
2751 */
2752 /* IF ( X_acct_rate_type IS NOT NULL AND P_system_linkage <> 'ST' AND P_system_linkage <> 'OT' )*/
2753 IF ( X_acct_rate_type IS NOT NULL )
2754 THEN --{
2755 usersupplied.acct_rate_type := 'Y' ;
2756 X_projfunc_cost_rate_type := X_acct_rate_type ;
2757 ELSE --}{ X_acct_rate_type IS NULL
2758 /*
2759 * Both Project Functional rate type and Functional rate type
2760 * are NULL. Derive Project Functional and copy it to Functional.
2761 */
2762 print_message('Both Project Functional rate type and Functional rate type are NULL');
2763 IF p_calling_module = 'FORECAST' Then
2764 print_message('Calling derive_fi_curr_attributes for projfunc rate type');
2765 derive_fi_curr_attributes
2766 ( P_project_id => p_project_id
2767 ,P_exp_org_id => p_exp_org_id
2768 ,P_ei_date => p_ei_date
2769 ,P_attribute => 'FORECAST'
2770 ,x_project_rate_type => l_dummy_char
2771 ,x_project_rate_date => l_dummy_date
2772 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2773 ,x_projfunc_cost_rate_date => l_dummy_date --X_projfunc_cost_rate_date
2774 ,x_acct_rate_type => l_dummy_char
2775 ,x_acct_rate_date => l_dummy_date
2776 );
2777 Else
2778 derive_projfunc_attributes
2779 ( P_calling_module => P_calling_module
2780 ,P_ei_date => P_ei_date
2781 ,P_task_id => P_task_id
2782 ,P_project_id => P_project_id
2783 ,P_attribute => 'TYPE'
2784 ,P_structure_version_id => p_structure_version_id
2785 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2786 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2787 );
2788 End if;
2789
2790 X_acct_rate_type := X_projfunc_cost_rate_type ;
2791 X_acct_exch_rate := X_projfunc_cost_exch_rate; --2822867
2792 END IF; --} X_acct_rate_type IS NOT NULL
2793 END IF; --} X_projfunc_cost_rate_type IS NOT NULL
2794
2795 IF ( X_projfunc_cost_rate_date IS NOT NULL )
2796 THEN --{
2797 /*
2798 * Functional Attribute is overridden by Project Functional -
2799 * if Project Functional is supplied.
2800 */
2801 --IF ( X_acct_rate_type IS NOT NULL ) /*Added bug:10070224 if condition since override should take the precendence if any override exists */
2802 IF ( X_acct_rate_date IS NOT NULL ) -- Commented the above line and changed the IF condition as part of the Bug 13001376
2803 THEN --{
2804 usersupplied.acct_rate_date := 'Y' ;
2805 X_projfunc_cost_rate_date := X_acct_rate_date ;
2806 ELSE
2807 usersupplied.projfunc_cost_rate_date := 'Y' ;
2808 X_acct_rate_date := X_projfunc_cost_rate_date ;
2809 END IF;
2810 ELSE -- }{ X_projfunc_cost_rate_date IS NULL
2811 /*
2812 * <2822867> If Functional Rate Date is available, override Project Functional Rate Date
2813 * with it - but only for non-Timecards. For Time-cards derive Project
2814 * Functional attributes afresh.
2815 * <7680781> No need of putting the check for ST,OT transactions here, if the acct_rate is
2816 * passed we need to use the same for calculating projfunc rate
2817 */
2818 /*IF ( X_acct_rate_date IS NOT NULL AND P_system_linkage <> 'ST' AND P_system_linkage <> 'OT' )*/
2819 IF ( X_acct_rate_date IS NOT NULL ) /* Bug:12566412:Replaced X_acct_rate_type IS NOT NULL to X_acct_rate_date IS NOT NULL */
2820 THEN --{
2821 usersupplied.acct_rate_date := 'Y' ;
2822 X_projfunc_cost_rate_date := X_acct_rate_date ;
2823 ELSE --}{ X_acct_rate_date IS NULL
2824 /*
2825 * Both Project Functional rate date and Functional rate date
2826 * are NULL. Derive Project Functional and copy it to Functional.
2827 */
2828 IF p_calling_module = 'FORECAST' Then
2829 print_message('Calling derive_fi_curr_attributes for projfunc rate date');
2830 derive_fi_curr_attributes
2831 ( P_project_id => p_project_id
2832 ,P_exp_org_id => p_exp_org_id
2833 ,P_ei_date => p_ei_date
2834 ,P_attribute => 'FORECAST'
2835 ,x_project_rate_type => l_dummy_char
2836 ,x_project_rate_date => l_dummy_date
2837 ,x_projfunc_cost_rate_type => l_dummy_char --X_projfunc_cost_rate_type
2838 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2839 ,x_acct_rate_type => l_dummy_char
2840 ,x_acct_rate_date => l_dummy_date
2841 );
2842 Else
2843 derive_projfunc_attributes
2844 ( P_calling_module => P_calling_module
2845 ,P_ei_date => P_ei_date
2846 ,P_task_id => P_task_id
2847 ,P_project_id => P_project_id
2848 ,P_attribute => 'DATE'
2849 ,P_structure_version_id => P_structure_version_id
2850 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
2851 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
2852 );
2853 End If;
2854 X_acct_rate_date := X_projfunc_cost_rate_date ;
2855 END IF; --} X_acct_rate_date IS NOT NULL
2856 END IF; --} X_projfunc_cost_rate_date IS NOT NULL
2857
2858 /*
2859 * Exchange Rate.
2860 * If either project functional was provided or neither of them was provided
2861 * - user project functional.
2862 */
2863 IF ( ( usersupplied.projfunc_cost_rate_type = 'Y' ) OR
2864 ( usersupplied.acct_rate_type <> 'Y' AND usersupplied.projfunc_cost_rate_type <> 'Y' )
2865 )
2866 THEN --{
2867 /*
2868 * If projfunc is supplied (1) or neither of them is given (2)
2869 * In both the cases (1)(2) projfunc takes precedence.
2870 */
2871 IF (X_projfunc_cost_rate_type = 'User')
2872 THEN --{
2873 IF ( pa_multi_currency.is_user_rate_type_allowed(
2874 P_from_currency => P_denom_curr_code,
2875 P_to_currency => P_projfunc_curr_code,
2876 P_conversion_date => X_projfunc_cost_rate_date) = 'Y'
2877 )
2878 THEN --{
2879 IF (X_projfunc_cost_exch_rate IS NOT NULL)
2880 THEN --{
2881 X_acct_exch_rate := X_projfunc_cost_exch_rate ;
2882 ELSE -- }{
2883 X_status := 'PA_NO_PROJFUNC_USER_RATE' ; /* bug#2855640 */
2884 RETURN ;
2885 END IF; --} X_projfunc_cost_exch_rate IS NOT NULL
2886 ELSE -- }{
2887 X_status := 'PA_NO_PROJFUNC_USER_RATE_TYPE';
2888 RETURN ;
2889 END IF; --} -- user_allowed
2890 END IF; --}
2891 ELSIF (usersupplied.acct_rate_type ='Y')
2892 THEN -- }{
2893 IF ( X_acct_rate_type = 'User')
2894 THEN --{
2895 IF (pa_multi_currency.is_user_rate_type_allowed(
2896 P_from_currency => P_denom_curr_code,
2897 P_to_currency => P_acct_curr_code,
2898 P_conversion_date => X_acct_rate_date) ='Y')
2899 THEN --{
2900 IF ( X_acct_exch_rate IS NOT NULL)
2901 THEN --{
2902 X_projfunc_cost_exch_rate := X_acct_exch_rate ;
2903 ELSE --}{
2904 X_status := 'PA_ACCT_USER_RATE_NOT_DEFINED' ;
2905 RETURN ;
2906 END IF; -- } X_acct_exch_rate IS NOT NULL
2907 ELSE --}{
2908 X_status := 'PA_NO_ACCT_USER_RATE_TYPE';
2909 RETURN ;
2910 END IF; --} user_allowed
2911 END IF; --} X_acct_rate_type = 'User'
2912 END IF ; --} usersupplied.projfunc_cost_rate_type = 'Y'
2913 END IF; --} NVL(P_accounted_flag, 'N') = 'N' AND P_system_linkage <> 'ER'
2914 END IF; --} P_projfunc_curr_code = P_denom_curr_code
2915 /*
2916 * All projfunc and acct attributes are derived.
2917 * The following code derives the project attributes.
2918 * projfunc = acct.
2919 */
2920 IF ( P_project_curr_code = P_denom_curr_code )
2921 THEN --{
2922 X_project_rate_type := NULL ;
2923 X_project_rate_date := NULL ;
2924 X_project_exch_rate := NULL ;
2925 ELSE --}{
2926 IF (P_project_curr_code = P_projfunc_curr_code)
2927 THEN --{
2928 /*
2929 * Project_rate_type.
2930 */
2931 IF (X_project_rate_type IS NOT NULL)
2932 THEN --{
2933 IF ( usersupplied.acct_rate_type <> 'Y' AND usersupplied.projfunc_cost_rate_type <> 'Y' )
2934 THEN --{
2935 X_acct_rate_type := X_project_rate_type ;
2936 X_projfunc_cost_rate_type := X_project_rate_type ;
2937 ELSE
2938 X_project_rate_type := X_projfunc_cost_rate_type; /* ADDED for bug:10070224 */
2939 END IF; --} usersupplied.acct_rate_type <> 'Y' AND usersupplied.projfunc_cost_rate_type <> 'Y'
2940 ELSE --}{
2941 X_project_rate_type := X_projfunc_cost_rate_type ;
2942 END IF; --} X_project_rate_type IS NOT NULL
2943
2944 /*
2945 * Project_rate_date.
2946 */
2947 IF (X_project_rate_date IS NOT NULL)
2948 THEN --{
2949 IF ( usersupplied.acct_rate_date <> 'Y' AND usersupplied.projfunc_cost_rate_date <> 'Y' )
2950 THEN --{
2951 X_acct_rate_date := X_project_rate_date ;
2952 X_projfunc_cost_rate_date := X_project_rate_date ;
2953 END IF; --} usersupplied.acct_rate_date <> 'Y' AND usersupplied.projfunc_cost_rate_date <> 'Y'
2954 ELSE --}{
2955 X_project_rate_date := X_projfunc_cost_rate_date ;
2956 END IF; --} X_project_rate_date IS NOT NULL
2957
2958 /*
2959 * Project_exch_rate.
2960 */
2961 IF ( X_projfunc_cost_rate_type = 'User')
2962 THEN --{
2963 X_project_exch_rate := X_projfunc_cost_exch_rate ;
2964 ELSE --}{
2965 IF ( X_project_rate_type = 'User')
2966 THEN --{
2967 IF ( pa_multi_currency.is_user_rate_type_allowed(
2968 P_from_currency => P_denom_curr_code,
2969 P_to_currency => P_project_curr_code,
2970 P_conversion_date => X_project_rate_date) = 'Y' )
2971 THEN --}{
2972 IF ( X_project_exch_rate IS NOT NULL )
2973 THEN --{
2974 X_projfunc_cost_exch_rate := X_project_exch_rate ;
2975 X_acct_exch_rate := X_project_exch_rate ;
2976 ELSE --}{
2977 X_status := 'PA_PROJ_USER_RATE_NOT_DEFINED' ;
2978 RETURN ;
2979 END IF;
2980 ELSE --}{
2981 X_status := 'PA_NO_PROJ_USER_RATE_TYPE';
2982 RETURN ;
2983 END IF; --}
2984 ELSE --}{
2985 X_project_exch_rate := X_projfunc_cost_exch_rate ;
2986 END IF; --}
2987 END IF; --} X_projfunc_cost_rate_type = 'User'
2988
2989 ELSE --}{
2990 IF p_calling_module = 'FORECAST' Then
2991 print_message('calling derive_fi_curr_attributes for project rate type');
2992 derive_fi_curr_attributes
2993 ( P_project_id => p_project_id
2994 ,P_exp_org_id => p_exp_org_id
2995 ,P_ei_date => p_ei_date
2996 ,P_attribute => 'FORECAST'
2997 ,x_project_rate_type => X_project_rate_type
2998 ,x_project_rate_date => X_project_rate_date
2999 ,x_projfunc_cost_rate_type => l_dummy_char
3000 ,x_projfunc_cost_rate_date => l_dummy_date
3001 ,x_acct_rate_type => l_dummy_char
3002 ,x_acct_rate_date => l_dummy_date
3003 );
3004 Else
3005 derive_project_attributes( P_task_id => P_task_id
3006 ,P_project_id => P_project_id
3007 ,P_ei_date => P_ei_date
3008 ,P_structure_version_id => P_structure_version_id
3009 ,P_calling_module => P_calling_module
3010 ,x_project_rate_type => X_project_rate_type
3011 ,x_project_rate_date => X_project_rate_date
3012 );
3013 End If;
3014
3015 END IF; --} P_project_curr_code = P_projfunc_curr_code
3016 END IF; --} P_project_curr_code = P_denom_curr_code
3017 END IF; -- } P_projfunc_curr_code = P_acct_curr_code
3018
3019 IF ( P_projfunc_curr_code <> P_acct_curr_code )
3020 THEN --{
3021
3022 IF ( P_projfunc_curr_code = P_denom_curr_code )
3023 THEN --{
3024 X_projfunc_cost_rate_type := NULL ;
3025 X_projfunc_cost_rate_date := NULL ;
3026 X_projfunc_cost_exch_rate := NULL ;
3027 ELSE --}{
3028
3029 IF ( X_projfunc_cost_rate_type IS NULL )
3030 THEN --{
3031 IF p_calling_module = 'FORECAST' Then
3032 print_message('calling derive_fi_curr_attributes for project rate date ');
3033 derive_fi_curr_attributes
3034 ( P_project_id => p_project_id
3035 ,P_exp_org_id => p_exp_org_id
3036 ,P_ei_date => p_ei_date
3037 ,P_attribute => 'FORECAST'
3038 ,x_project_rate_type => l_dummy_char
3039 ,x_project_rate_date => l_dummy_date
3040 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
3041 ,x_projfunc_cost_rate_date => l_dummy_date --X_projfunc_cost_rate_date
3042 ,x_acct_rate_type => l_dummy_char
3043 ,x_acct_rate_date => l_dummy_date
3044 );
3045 Else
3046
3047 derive_projfunc_attributes
3048 ( P_calling_module => P_calling_module
3049 ,P_ei_date => P_ei_date
3050 ,P_task_id => P_task_id
3051 ,P_project_id => P_project_id
3052 ,P_attribute => 'TYPE'
3053 ,P_structure_version_id => P_structure_version_id
3054 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
3055 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
3056 );
3057 End if;
3058 ELSE --}{
3059 usersupplied.projfunc_cost_rate_type := 'Y' ;
3060 END IF; --} X_projfunc_cost_rate_type IS NULL
3061
3062 IF ( X_projfunc_cost_rate_date IS NULL )
3063 THEN --{
3064 IF p_calling_module = 'FORECAST' Then
3065 print_message('calling derive_fi_curr_attributes for projfunc rate type');
3066 derive_fi_curr_attributes
3067 ( P_project_id => p_project_id
3068 ,P_exp_org_id => p_exp_org_id
3069 ,P_ei_date => p_ei_date
3070 ,P_attribute => 'FORECAST'
3071 ,x_project_rate_type => l_dummy_char
3072 ,x_project_rate_date => l_dummy_date
3073 ,x_projfunc_cost_rate_type => l_dummy_char --X_projfunc_cost_rate_type
3074 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
3075 ,x_acct_rate_type => l_dummy_char
3076 ,x_acct_rate_date => l_dummy_date
3077 );
3078 Else
3079 derive_projfunc_attributes
3080 ( P_calling_module => P_calling_module
3081 ,P_ei_date => P_ei_date
3082 ,P_task_id => P_task_id
3083 ,P_project_id => P_project_id
3084 ,P_attribute => 'DATE'
3085 ,P_structure_version_id => P_structure_version_id
3086 ,x_projfunc_cost_rate_type => X_projfunc_cost_rate_type
3087 ,x_projfunc_cost_rate_date => X_projfunc_cost_rate_date
3088 );
3089 End if;
3090 ELSE --}{
3091 usersupplied.projfunc_cost_rate_date := 'Y' ;
3092 END IF; --} X_projfunc_cost_rate_date IS NULL
3093
3094 IF (X_projfunc_cost_rate_type = 'User')
3095 THEN --{
3096 IF (pa_multi_currency.is_user_rate_type_allowed(
3097 P_from_currency => P_denom_curr_code,
3098 P_to_currency => P_projfunc_curr_code,
3099 P_conversion_date => X_projfunc_cost_rate_date) = 'Y')
3100 THEN --{
3101 IF ( X_projfunc_cost_exch_rate IS NULL )
3102 THEN --{
3103 X_status := 'PA_NO_PROJFUNC_USER_RATE' ; /* bug#2855640 */
3104 RETURN ;
3105 END IF; --} X_projfunc_cost_exch_rate IS NOT NULL
3106 ELSE --}{
3107 X_status := 'PA_NO_PROJFUNC_USER_RATE_TYPE';
3108 RETURN ;
3109 END IF; --} user_allowed <> 'Y'
3110 END IF; --} X_projfunc_cost_rate_type = 'User'
3111 END IF; --} P_projfunc_curr_code = P_denom_curr_code
3112
3113
3114 IF ( P_acct_curr_code = P_denom_curr_code )
3115 THEN --{
3116 X_acct_rate_type := NULL ;
3117 X_acct_rate_date := NULL ;
3118 X_acct_exch_rate := NULL ;
3119 ELSE --}{
3120 IF ( X_acct_rate_type IS NULL )
3121 THEN --{
3122 IF p_calling_module = 'FORECAST' Then
3123 print_message('calling derive_fi_curr_attributes for acct rate type');
3124 derive_fi_curr_attributes
3125 ( P_project_id => p_project_id
3126 ,P_exp_org_id => p_exp_org_id
3127 ,P_ei_date => p_ei_date
3128 ,P_attribute => 'FORECAST'
3129 ,x_project_rate_type => l_dummy_char
3130 ,x_project_rate_date => l_dummy_date
3131 ,x_projfunc_cost_rate_type => l_dummy_char
3132 ,x_projfunc_cost_rate_date => l_dummy_date
3133 ,x_acct_rate_type => X_acct_rate_type
3134 ,x_acct_rate_date => l_dummy_date --X_acct_rate_date
3135 );
3136 Else
3137 derive_acct_attributes( P_calling_module => P_calling_module
3138 ,P_ei_date => P_ei_date
3139 ,P_attribute => 'TYPE'
3140 ,x_acct_rate_type => X_acct_rate_type
3141 ,x_acct_rate_date=> X_acct_rate_date
3142 );
3143 End if;
3144
3145 ELSE --}{
3146 usersupplied.acct_rate_type := 'Y' ;
3147 END IF; --} X_acct_rate_type IS NULL
3148
3149 IF ( X_acct_rate_date IS NULL )
3150 THEN --{
3151 IF p_calling_module = 'FORECAST' Then
3152 print_message('calling derive_fi_curr_attributes for acct rate date');
3153 derive_fi_curr_attributes
3154 ( P_project_id => p_project_id
3155 ,P_exp_org_id => p_exp_org_id
3156 ,P_ei_date => p_ei_date
3157 ,P_attribute => 'FORECAST'
3158 ,x_project_rate_type => l_dummy_char
3159 ,x_project_rate_date => l_dummy_date
3160 ,x_projfunc_cost_rate_type => l_dummy_char
3161 ,x_projfunc_cost_rate_date => l_dummy_date
3162 ,x_acct_rate_type => l_dummy_char --X_acct_rate_type
3163 ,x_acct_rate_date => X_acct_rate_date
3164 );
3165 Else
3166 derive_acct_attributes( P_calling_module => P_calling_module
3167 ,P_ei_date => P_ei_date
3168 ,P_attribute => 'DATE'
3169 ,x_acct_rate_type => X_acct_rate_type
3170 ,x_acct_rate_date=> X_acct_rate_date
3171 );
3172 End if;
3173 ELSE --}{
3174 usersupplied.acct_rate_date := 'Y' ;
3175 END IF; --} X_acct_rate_date IS NULL
3176
3177 IF (X_acct_rate_type = 'User')
3178 THEN --{
3179 IF (pa_multi_currency.is_user_rate_type_allowed(
3180 P_from_currency => P_denom_curr_code,
3181 P_to_currency => P_acct_curr_code,
3182 P_conversion_date => X_acct_rate_date) = 'Y')
3183 THEN --{
3184 IF ( X_acct_exch_rate IS NULL )
3185 THEN --{
3186 X_status := 'PA_ACCT_USER_RATE_NOT_DEFINED' ;
3187 RETURN ;
3188 END IF; --} X_acct_exch_rate IS NOT NULL
3189 ELSE --}{
3190 X_status := 'PA_NO_ACCT_USER_RATE_TYPE';
3191 RETURN ;
3192 END IF; --} user_allowed <> 'Y'
3193 END IF; --} X_acct_rate_type = 'User'
3194 END IF ; --} P_acct_curr_code = P_denom_curr_code
3195
3196 /*
3197 * Projfunc and acct rates are ready.
3198 */
3199 IF ( P_project_curr_code = P_denom_curr_code )
3200 THEN --{
3201 X_project_rate_type := NULL ;
3202 X_project_rate_date := NULL ;
3203 X_project_exch_rate := NULL ;
3204 ELSE --}{
3205 IF ( P_project_curr_code = P_projfunc_curr_code )
3206 THEN --{
3207
3208 /*
3209 * Project_rate_type
3210 */
3211 IF ( usersupplied.projfunc_cost_rate_type = 'Y' )
3212 THEN --{
3213 --dbms_output.put_line('moving pfrt to prt');
3214 X_project_rate_type := X_projfunc_cost_rate_type ;
3215 ELSE --}{
3216 IF ( X_project_rate_type IS NOT NULL)
3217 THEN -- {
3218 X_projfunc_cost_rate_type := X_project_rate_type ;
3219 ELSE --}{
3220 X_project_rate_type := X_projfunc_cost_rate_type ;
3221 END IF; --} X_project_rate_type IS NOT NULL
3222 END IF; --} usersupplied.projfunc_cost_rate_type = 'Y'
3223
3224 /*
3225 * Project_rate_date
3226 */
3227 IF ( usersupplied.projfunc_cost_rate_date = 'Y' )
3228 THEN --{
3229 X_project_rate_date := X_projfunc_cost_rate_date ;
3230 ELSE --}{
3231 IF ( X_project_rate_date IS NOT NULL)
3232 THEN -- {
3233 X_projfunc_cost_rate_date := X_project_rate_date ;
3234 ELSE --}{
3235 --dbms_output.put_line('b4 moving pfrd to prd');
3236 --dbms_output.put_line('pfrd ['|| to_char(X_projfunc_cost_rate_date) || ']');
3237 --dbms_output.put_line('prd ['|| to_char(X_project_rate_date) || ']');
3238 X_project_rate_date := X_projfunc_cost_rate_date ;
3239 --dbms_output.put_line('after moving pfrd to prd');
3240 --dbms_output.put_line('pfrd ['|| to_char(X_projfunc_cost_rate_date) || ']');
3241 --dbms_output.put_line('prd ['|| to_char(X_project_rate_date) || ']');
3242 END IF; --} X_project_rate_date IS NOT NULL
3243 END IF; --} usersupplied.projfunc_cost_rate_date = 'Y'
3244
3245 /*
3246 * Project exch rate.
3247 */
3248 IF ( X_projfunc_cost_rate_type = 'User')
3249 THEN --{
3250 X_project_exch_rate := X_projfunc_cost_exch_rate ;
3251 ELSE --}{
3252 IF ( X_project_rate_type = 'User')
3253 THEN --{
3254 IF ( pa_multi_currency.is_user_rate_type_allowed(
3255 P_from_currency => P_denom_curr_code,
3256 P_to_currency => P_project_curr_code,
3257 P_conversion_date => X_project_rate_date) ='Y' )
3258 THEN --}{
3259 IF ( X_project_exch_rate IS NOT NULL )
3260 THEN --{
3261 X_projfunc_cost_exch_rate := X_project_exch_rate ;
3262 ELSE --}{
3263 X_status := 'PA_PROJ_USER_RATE_NOT_DEFINED' ;
3264 RETURN ;
3265 END IF;
3266 ELSE --}{
3267 X_status := 'PA_NO_PROJ_USER_RATE_TYPE';
3268 RETURN ;
3269 END IF; --}
3270 ELSE --}{
3271 --dbms_output.put_line('moving pfer to per');
3272 X_project_exch_rate := X_projfunc_cost_exch_rate ;
3273 END IF; --}
3274 END IF; --} X_projfunc_cost_rate_type = 'User'
3275 END IF; --} P_project_curr_code = P_projfunc_curr_code
3276
3277 IF ( P_project_curr_code = P_acct_curr_code )
3278 THEN --{
3279
3280 /*
3281 * Project_rate_type
3282 */
3283 IF ( usersupplied.acct_rate_type = 'Y' )
3284 THEN --{
3285 X_project_rate_type := X_acct_rate_type ;
3286 ELSE --}{
3287 IF ( X_project_rate_type IS NOT NULL)
3288 THEN -- {
3289 X_acct_rate_type := X_project_rate_type ;
3290 ELSE --}{
3291 X_project_rate_type := X_acct_rate_type ;
3292 END IF; --} X_project_rate_type IS NOT NULL
3293 END IF; --} usersupplied.acct_rate_type = 'Y'
3294
3295 /*
3296 * Project_rate_date
3297 */
3298 IF ( usersupplied.acct_rate_date = 'Y' )
3299 THEN --{
3300 X_project_rate_date := X_acct_rate_date ;
3301 ELSE --}{
3302 IF ( X_project_rate_date IS NOT NULL)
3303 THEN -- {
3304 X_acct_rate_date := X_project_rate_date ;
3305 ELSE --}{
3306 X_project_rate_date := X_acct_rate_date ;
3307 END IF; --} X_project_rate_date IS NOT NULL
3308 END IF; --} usersupplied.acct_rate_date = 'Y'
3309
3310 /*
3311 * Project exch rate.
3312 */
3313 IF ( X_acct_rate_type = 'User')
3314 THEN --{
3315 X_project_exch_rate := X_acct_exch_rate ;
3316 ELSE --}{
3317 IF ( X_project_rate_type = 'User')
3318 THEN --{
3319 IF ( pa_multi_currency.is_user_rate_type_allowed(
3320 P_from_currency => P_denom_curr_code,
3321 P_to_currency => P_project_curr_code,
3322 P_conversion_date => X_project_rate_date) ='Y' )
3323 THEN --}{
3324 IF ( X_project_exch_rate IS NOT NULL )
3325 THEN --{
3326 X_acct_exch_rate := X_project_exch_rate ;
3327 ELSE --}{
3328 X_status := 'PA_PROJ_USER_RATE_NOT_DEFINED' ;
3329 RETURN ;
3330 END IF;
3331 ELSE --}{
3332 X_status := 'PA_NO_PROJ_USER_RATE_TYPE';
3333 RETURN ;
3334 END IF; --}
3335 ELSE --}{
3336 X_project_exch_rate := X_acct_exch_rate ;
3337 END IF; --}
3338 END IF; --} X_acct_rate_type = 'User'
3339 END IF; --} P_project_curr_code = P_acct_curr_code
3340
3341 IF ( P_project_curr_Code <> P_acct_curr_code AND P_project_curr_code <> P_projfunc_curr_code)
3342 THEN --{
3343 IF p_calling_module = 'FORECAST' Then
3344 derive_fi_curr_attributes
3345 ( P_project_id => p_project_id
3346 ,P_exp_org_id => p_exp_org_id
3347 ,P_ei_date => p_ei_date
3348 ,P_attribute => 'FORECAST'
3349 ,x_project_rate_type => X_project_rate_type
3350 ,x_project_rate_date => X_project_rate_date
3351 ,x_projfunc_cost_rate_type => l_dummy_char
3352 ,x_projfunc_cost_rate_date => l_dummy_date
3353 ,x_acct_rate_type => l_dummy_char
3354 ,x_acct_rate_date => l_dummy_date
3355 );
3356 Else
3357 derive_project_attributes( P_task_id => P_task_id
3358 ,P_project_id => P_project_id
3359 ,P_ei_date => P_ei_date
3360 ,P_structure_version_id => P_structure_version_id
3361 ,P_calling_module => P_calling_module
3362 ,x_project_rate_type => X_project_rate_type
3363 ,x_project_rate_date => X_project_rate_date
3364 );
3365 End If;
3366 END IF;--}
3367 END IF ; --} P_project_curr_code = P_denom_curr_code
3368
3369 END IF; --} P_projfunc_curr_code <> P_acct_curr_code
3370
3371 EXCEPTION
3372 WHEN OTHERS THEN
3373 x_acct_rate_date := l_temp_acct_rate_date;
3374 x_acct_rate_type := l_temp_acct_rate_type;
3375 x_acct_exch_rate := l_temp_acct_exch_rate;
3376 x_project_rate_date := l_temp_project_rate_date;
3377 x_project_rate_type := l_temp_project_rate_type;
3378 x_project_exch_rate := l_temp_project_exch_rate;
3379 x_projfunc_cost_rate_date := l_temp_projfunc_cost_rate_date;
3380 x_projfunc_cost_rate_type := l_temp_projfunc_cost_rate_type;
3381 x_projfunc_cost_exch_rate := l_temp_projfunc_cost_exch_rate;
3382 RAISE;
3383 END get_currency_attributes;
3384 /*---------------------------------------------------------------------*/
3385
3386 END pa_multi_currency_txn ;