[Home] [Help]
PACKAGE BODY: APPS.PA_MCB_REVENUE_PKG
Source
1 PACKAGE BODY PA_MCB_REVENUE_PKG AS
2 --$Header: PAXMCRUB.pls 120.8 2007/12/28 12:00:24 hkansal ship $
3
4 /*----------------------------------------------------------------------------------------+
5 | Procedure : event_amount_conversion |
6 | Purpose : To update the pa_events table (bill transaction currency to |
7 | revenue processing currency |
8 | Parameters : |
9 | ================================================================================== |
10 | Name Mode Description |
11 | ================================================================================== |
12 | p_project_id IN project Id |
13 | p_request_id IN Id for the current Run |
14 | p_event_type IN Type of events - to identify the AUTOMATIC events and |
15 | other events |
16 | p_calling_place IN |
17 | acc_thru_date IN Input parameter given in When we Generate revenue |
18 | p_project_rate_date IN Project Rate date |
19 p_projfunc_rate_dateIN Project Functional Rate date |
20 | x_return_status IN OUT Return status of this procedure |
21 | x_msg_count IN OUT Error message count |
22 | x_msg_data IN OUT Error message |
23 | ================================================================================== |
24 +----------------------------------------------------------------------------------------*/
25
26 /* Funding Revaluation Changes : Added the realized gain and loss event type */
27
28 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
29 /* Fix for bug 5907315 starts here */
30 gl_pa_dt_st PA_PLSQL_DATATYPES.DateTabTyp;
31 gl_pa_dt_end PA_PLSQL_DATATYPES.DateTabTyp;
32
33 FUNCTION pa_date(p_date IN DATE) RETURN DATE IS
34 l_start_date DATE;
35 l_end_date DATE;
36 l_cnt NUMBER;
37 BEGIN
38 l_cnt := gl_pa_dt_st.COUNT;
39 FOR I IN 1..l_cnt
40 LOOP
41 IF (p_date between gl_pa_dt_st(I) and gl_pa_dt_end(I)) THEN
42 RETURN(gl_pa_dt_end(I));
43 END IF;
44 END LOOP;
45
46 SELECT start_date,end_date
47 INTO l_start_date,l_end_date
48 FROM pa_periods
49 WHERE p_date between start_date and end_date;
50
51 l_cnt := l_cnt+1;
52
53 gl_pa_dt_st(l_cnt):= l_start_date;
54 gl_pa_dt_end(l_cnt):= l_end_date;
55 RETURN(l_end_date);
56
57 END;
58 /* Fix for bug 5907315 ends here */
59
60
61 PROCEDURE event_amount_conversion(
62 p_project_id IN NUMBER,
63 p_request_id IN NUMBER,
64 p_event_type IN VARCHAR2,
65 p_calling_place IN VARCHAR2,
66 p_acc_thru_dt IN DATE,
67 p_project_rate_date IN DATE,
68 p_projfunc_rate_date IN DATE,
69 x_return_status IN OUT NOCOPY VARCHAR2,
70 x_msg_count IN OUT NOCOPY NUMBER,
71 x_msg_data IN OUT NOCOPY VARCHAR2) IS
72
73
74 CURSOR csr_events(p_project_id NUMBER,
75 p_request_id NUMBER,
76 p_event_type VARCHAR2,
77 p_calling_place VARCHAR2,
78 p_acc_thru_dt DATE ) IS
79 SELECT event_id,
80 bill_trans_currency_code,
81 bill_trans_rev_amount,
82 project_currency_code,
83 project_rate_type,
84 project_rate_date,
85 project_exchange_rate,
86 projfunc_currency_code,
87 projfunc_rate_type,
88 projfunc_rate_date,
89 projfunc_exchange_rate,
90 revproc_currency_code,
91 revproc_rate_type,
92 revproc_rate_date,
93 revproc_exchange_rate,
94 'N' /* Bug 2563738 */
95 FROM pa_events v
96 WHERE v.project_id = p_project_id
97 AND v.request_id = p_request_id
98 AND v. revenue_distributed_flag = 'D'
99 AND nvl(v.task_id, -1) IN
100 (SELECT decode(v.task_id, null, -1, t.task_id )
101 FROM pa_tasks t
102 WHERE t.project_id = p_project_id
103 AND t.ready_to_distribute_flag ||'' = 'Y'
104 )
105 AND TRUNC(v.completion_date) <= TRUNC(NVL(p_acc_thru_dt,sysdate)) /* Bug#3118592 */
106 AND (DECODE(NVL(v.bill_trans_rev_amount, 0), 0 ,
107 DECODE(NVL(v.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
108 AND v.calling_place = p_calling_place
109 AND EXISTS
110 (SELECT vt.event_type
111 FROM pa_event_types vt
112 WHERE vt.event_type = v.event_type
113 AND vt.event_type_classification||''= 'AUTOMATIC'
114 )
115 AND ( v.calling_process||'' = 'Revenue'
116 OR ( v.calling_process||'' = 'Invoice'
117 AND EXISTS
118 (SELECT 'Invoice is released'
119 FROM pa_draft_invoice_items drii,
120 pa_draft_invoices dri
121 WHERE drii.project_id = p_project_id
122 AND nvl(drii.event_task_id, -1) = nvl( v.task_id, -1)
123 AND drii.event_num = v.event_num
124 AND dri.project_id = drii.project_id
125 AND dri.draft_invoice_num = drii.draft_invoice_num
126 AND dri.released_date is not null
127 )))
128 AND p_event_type = 'AUTOMATIC'
129 UNION
130 SELECT event_id,
131 bill_trans_currency_code,
132 bill_trans_rev_amount,
133 project_currency_code,
134 project_rate_type,
135 project_rate_date,
136 project_exchange_rate,
137 projfunc_currency_code,
138 projfunc_rate_type,
139 projfunc_rate_date,
140 projfunc_exchange_rate,
141 revproc_currency_code,
142 revproc_rate_type,
143 revproc_rate_date,
144 revproc_exchange_rate,
145 DECODE(vt.event_type_classification , 'REALIZED_GAINS', 'Y',
146 'REALIZED_LOSSES', 'Y','N')
147 FROM pa_events evt,
148 pa_event_types vt
149 WHERE evt.project_id = p_project_id
150 AND evt.request_id = p_request_id
151 AND evt.revenue_distributed_flag = 'D'
152 AND TRUNC(evt.completion_date) <= TRUNC(NVL(p_acc_thru_dt,sysdate)) /* Bug#3118592 */
153 AND (DECODE(NVL(evt.bill_trans_rev_amount, 0), 0 ,
154 DECODE(NVL(evt.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
155 AND vt.event_type = evt.event_type ||''
156 AND vt.event_type_classification ||'' IN
157 ('WRITE ON','WRITE OFF','MANUAL','REALIZED_GAINS','REALIZED_LOSSES')
158 AND EXISTS ( SELECT 'ready to distribute top task exists'
159 FROM pa_tasks tsk
160 WHERE tsk.project_id = p_project_id
161 AND tsk.task_id = NVL( evt.task_id, tsk.task_id )
162 AND tsk.ready_to_distribute_flag ||'' = 'Y'
163 )
164 /* AND EXISTS ( SELECT 'Write on or Write off or Manual events exists'
165 FROM pa_event_types vt
166 WHERE vt.event_type = evt.event_type ||''
167 AND vt.event_type_classification ||'' IN
168 ('WRITE ON','WRITE OFF','MANUAL','REALIZED_GAINS','REALIZED_LOSSES')
169 ) */
170 AND p_event_type = 'MANUAL';
171
172
173 l_event_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
174 l_bill_trans_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
175 l_bill_trans_rev_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
176 l_project_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
177 l_project_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
178 l_project_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
179 l_project_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
180 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
181 l_project_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
182 l_project_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
183 l_projfunc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
184 l_projfunc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
185 l_projfunc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
186 l_projfunc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
187 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
188 l_projfunc_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
189 l_projfunc_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
190 l_revproc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
191 l_revproc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
192 l_revproc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
193 l_revproc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
194 l_revproc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
195 l_revproc_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
196 l_revproc_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
197 l_project_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
198 l_projfunc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
199 l_revproc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
200 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
201
202 l_conversion_between VARCHAR2(6);
203 l_cache_flag VARCHAR2(1);
204
205
206 l_currency_flag VARCHAR2(1):= 'N';
207
208 l_Rgain_Rloss_flag PA_PLSQL_DATATYPES.Char30TabTyp;
209
210 BEGIN
211
212
213 /* This flag is N then the convert_amount_bulk API not cache any currency code,
214 If the flag is Y then it cache the currency and other attributes for avoid the
215 repeat processing. */
216
217 l_cache_flag := 'N';
218
219
220 IF g1_debug_mode = 'Y' THEN
221 PA_MCB_INVOICE_PKG.log_message('... Enter the procedure Event_amount_Conversion');
222 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || '-----------------------------------------------');
223 END IF;
224
225
226 OPEN csr_events(p_project_id,
227 p_request_id,
228 p_event_type,
229 p_calling_place,
230 p_acc_thru_dt
231 );
232
233
234 LOOP
235
236 /*
237 * Clear all PL/SQL table.
238 */
239
240 l_event_id_tab.delete;
241 l_bill_trans_curr_code_tab.delete;
242 l_bill_trans_rev_amount_tab.delete;
243 l_project_curr_code_tab.delete;
244 l_project_rate_type_tab.delete;
245 l_project_rate_date_tab.delete;
246 l_project_exchange_rate_tab.delete;
247 l_project_amount_tab.delete;
248 l_project_denominator_tab.delete;
249 l_project_numerator_tab.delete;
250 l_projfunc_curr_code_tab.delete;
251 l_projfunc_rate_type_tab.delete;
252 l_projfunc_rate_date_tab.delete;
253 l_projfunc_exchange_rate_tab.delete;
254 l_projfunc_amount_tab.delete;
255 l_projfunc_denominator_tab.delete;
256 l_projfunc_numerator_tab.delete;
257 l_revproc_curr_code_tab.delete;
258 l_revproc_rate_type_tab.delete;
259 l_revproc_rate_date_tab.delete;
260 l_revproc_exchange_rate_tab.delete;
261 l_revproc_amount_tab.delete;
262 l_revproc_denominator_tab.delete;
263 l_revproc_numerator_tab.delete;
264 l_project_status_tab.delete;
265 l_projfunc_status_tab.delete;
266 l_revproc_status_tab.delete;
267 l_user_validate_flag_tab.delete;
268 l_Rgain_Rloss_flag.delete;
269
270
271 /* Fetch the Convert Attributes and amount for Event */
272
273 FETCH csr_events BULK COLLECT
274 INTO l_event_id_tab,
275 l_bill_trans_curr_code_tab,
276 l_bill_trans_rev_amount_tab,
277 l_project_curr_code_tab,
278 l_project_rate_type_tab,
279 l_project_rate_date_tab,
280 l_project_exchange_rate_tab,
281 l_projfunc_curr_code_tab,
282 l_projfunc_rate_type_tab,
283 l_projfunc_rate_date_tab,
284 l_projfunc_exchange_rate_tab,
285 l_revproc_curr_code_tab,
286 l_revproc_rate_type_tab,
287 l_revproc_rate_date_tab,
288 l_revproc_exchange_rate_tab,
289 l_Rgain_Rloss_flag LIMIT 100;
290
291
292 /* If any events fetched from table then proceding for conversion */
293
294
295
296
297 IF (l_event_id_tab.COUNT = 0) THEN
298
299 Exit;
300
301 ELSE
302
303 /* Checking for Project Fuunctional and Project Currencies are same
304 If both currencies are same then convert only Project Functional currency
305 copy the Project functional amount and attributes to Project */
306
307
308 l_currency_flag := 'N';
309
310 IF (l_projfunc_curr_code_tab(1) = l_project_curr_code_tab(1)) THEN
311
312 l_currency_flag := 'Y';
313
314 END IF;
315
316
317 IF g1_debug_mode = 'Y' THEN
318 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project and Proj Func currencies are same : ' || l_currency_flag);
319 END IF;
320
321
322 /* Initialize the array variables */
323
324
325 FOR I in 1 .. l_event_id_tab.COUNT
326 LOOP
327
328 l_project_amount_tab(I) := NULL;
329 l_projfunc_amount_tab(I) := NULL;
330 l_revproc_amount_tab(I) := NULL;
331 l_project_denominator_tab(I) := NULL;
332 l_project_numerator_tab(I) := NULL;
333 l_projfunc_denominator_tab(I) := NULL;
334 l_projfunc_numerator_tab(I) := NULL;
335 l_revproc_denominator_tab(I) := NULL;
336 l_revproc_numerator_tab(I) := NULL;
337 l_project_status_tab(I) := NULL;
338 l_projfunc_status_tab(I) := NULL;
339 l_revproc_status_tab(I) := NULL;
340 l_user_validate_flag_tab(I) := 'Y';
341
342
343 /* If project rate date is null in events table then take the project rate date from
344 pa_projects_all table(p_project_rate_date from pa_projects_all table) */
345
346
347 IF l_project_rate_date_tab(I) IS NULL THEN
348
349 l_project_rate_date_tab(I) := p_project_rate_date;
350
351 END IF;
352
353
354 /* If projfunc rate date is null in events table then take the functional rate date from
355 pa_projects_all table(p_projfunc_rate_date from pa_projects_all table) */
356
357
358 IF l_projfunc_rate_date_tab(I) IS NULL THEN
359
360 l_projfunc_rate_date_tab(I) := p_projfunc_rate_date;
361
362 END IF;
363
364
368
365
366 END LOOP;
367
369
370
371 /* Converting Bill Transaction amount to Project Functional amount */
372
373 IF g1_debug_mode = 'Y' THEN
374 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Converting the Bill trans amount to project functional amount');
375 END IF;
376
377 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
378 then the API concate the code with error message */
379
380 l_conversion_between := 'BTC_PF';
381
382
383 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
384 p_from_currency_tab => l_bill_trans_curr_code_tab,
385 p_to_currency_tab => l_projfunc_curr_code_tab,
386 p_conversion_date_tab => l_projfunc_rate_date_tab,
387 p_conversion_type_tab => l_projfunc_rate_type_tab,
388 p_amount_tab => l_bill_trans_rev_amount_tab,
389 p_user_validate_flag_tab => l_user_validate_flag_tab,
390 p_converted_amount_tab => l_projfunc_amount_tab,
391 p_denominator_tab => l_projfunc_denominator_tab,
392 p_numerator_tab => l_projfunc_numerator_tab,
393 p_rate_tab => l_projfunc_exchange_rate_tab,
394 p_conversion_between => l_conversion_between,
395 p_cache_flag => l_cache_flag,
396 x_status_tab => l_projfunc_status_tab
397 );
398
399
400
401
402 /* Converting Bill Transaction amount to Project Currency amount */
403
404 IF g1_debug_mode = 'Y' THEN
405 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Convert the Bill trans amount to project amount');
406 END IF;
407
408
409 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
410 then the API concate the code with error message */
411
412 /* If project and Project Functional both are same then copy the PF attributes to Project other
413 Wise convert the project amount */
414
415
416 IF (l_currency_flag <> 'Y') THEN
417
418 l_conversion_between := 'BTC_PC';
419
420 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
421 p_from_currency_tab => l_bill_trans_curr_code_tab,
422 p_to_currency_tab => l_project_curr_code_tab,
423 p_conversion_date_tab => l_project_rate_date_tab,
424 p_conversion_type_tab => l_project_rate_type_tab,
425 p_amount_tab => l_bill_trans_rev_amount_tab,
426 p_user_validate_flag_tab => l_user_validate_flag_tab,
427 p_converted_amount_tab => l_project_amount_tab,
428 p_denominator_tab => l_project_denominator_tab,
429 p_numerator_tab => l_project_numerator_tab,
430 p_rate_tab => l_project_exchange_rate_tab,
431 p_conversion_between => l_conversion_between,
432 p_cache_flag => l_cache_flag,
433 x_status_tab => l_project_status_tab
434 );
435
436
437 END IF;
438
439 -- Log Messages for Events Converted Amounts
440
441 IF g1_debug_mode = 'Y' THEN
442 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project Id : ' || p_project_id);
443 END IF;
444
445 FOR i IN 1..l_event_id_tab.COUNT LOOP
446
447
448 /* If both Project and Proj Func are same then copy the Proj func attributes to project */
449
450 IF l_currency_flag = 'Y' THEN
451
452 l_project_curr_code_tab(i) := l_projfunc_curr_code_tab(i);
453 l_project_rate_type_tab(i) := l_projfunc_rate_type_tab(i);
454 l_project_rate_date_tab(i) := l_projfunc_rate_date_tab(i);
455 l_project_exchange_rate_tab(i) := l_projfunc_exchange_rate_tab(i);
456 l_project_amount_tab(i) := l_projfunc_amount_tab(i);
457 l_project_status_tab(i) := l_projfunc_status_tab(i);
458
459 END IF;
460
461
462
463 /* If revenue processing currency and project functional currency both are same then
464 copy the project functional attributes to revenue processing attributes */
465
466 /* Bug : 2563738 - Change the logic for Revenue processing and project function check
467 in the IF loop and project and reveproc check in the else loop */
468
469
470 IF (l_revproc_curr_code_tab(i) = l_projfunc_curr_code_tab(i)) THEN
471
472 l_revproc_curr_code_tab(I) := l_projfunc_curr_code_tab(i);
473 l_revproc_rate_type_tab(I) := l_projfunc_rate_type_tab(i);
474 l_revproc_rate_date_tab(I) := l_projfunc_rate_date_tab(i);
475 l_revproc_exchange_rate_tab(I) := l_projfunc_exchange_rate_tab(i);
479 /* If revenue processing currency and project currency both are same then
476 l_revproc_amount_tab(I) := l_projfunc_amount_tab(i);
477
478
480 copy the project currency attributes to revenue processing attributes */
481
482
483 ELSIF (l_revproc_curr_code_tab(i) = l_project_curr_code_tab(i)) THEN
484
485
486 l_revproc_curr_code_tab(I) := l_project_curr_code_tab(i);
487 l_revproc_rate_type_tab(I) := l_project_rate_type_tab(i);
488 l_revproc_rate_date_tab(I) := l_project_rate_date_tab(i);
489 l_revproc_exchange_rate_tab(I) := l_project_exchange_rate_tab(i);
490 l_revproc_amount_tab(I) := l_project_amount_tab(i);
491
492
493 END IF;
494
495 /* Added for Bug 5372663 */
496 IF l_projfunc_status_tab(i) <> 'N' THEN
497 x_msg_data := l_projfunc_status_tab(i);
498
499 ELSIF l_project_status_tab(i) <> 'N' THEN
500 x_msg_data := l_project_status_tab(i);
501
502 END IF;
503 /* End of changes for Bug 5372663 */
504
505
506
507
508 /* Bug#2563738 : If event type realized gain and realized loss then
509 we are not conversting, so making the value as zero for the project amount,
510 and project attributes are null */
511
512
513 IF (l_Rgain_Rloss_flag(i) = 'Y') THEN
514
515 l_project_rate_type_tab(i) := null;
516 l_project_rate_date_tab(i) := null;
517 l_project_exchange_rate_tab(i) := null;
518 l_project_amount_tab(i) := 0;
519 l_project_status_tab(i) := 'N';
520
521 END IF;
522
523
524 IF g1_debug_mode = 'Y' THEN
525 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Event Id :' || l_event_id_tab(i));
526 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
527 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Input Bill Trans Amount :' || l_bill_trans_rev_amount_tab(i));
528 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project Currency Code :' || l_project_curr_code_tab(i));
529 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project rate type :' || l_project_rate_type_tab(i));
530 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project Exchange rate :' || l_project_exchange_rate_tab(i));
531 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project Rate date :' || l_project_rate_date_tab(i));
532 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Convert Project Amount :' || l_project_amount_tab(i));
533 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Project Rejection Reason :' || l_project_status_tab(i));
534 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Convert Project Func Amount :' || l_projfunc_amount_tab(i));
535 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'ProjFunc Curr Code :' || l_projfunc_curr_code_tab(i));
536 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'ProjFunc rate type :' || l_projfunc_rate_type_tab(i));
537 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'ProjFunc Exchange rate :' || l_projfunc_exchange_rate_tab(i));
538 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'ProjFunc Rate date :' || l_projfunc_rate_date_tab(i));
539 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'ProjFunc Amt Rejection Reason :' || l_projfunc_status_tab(i));
540 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Convert RevProc Amount :' || l_revproc_amount_tab(i));
541 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
542 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Revproc rate type :' || l_revproc_rate_type_tab(i));
543 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Revproc Exchange rate :' || l_revproc_exchange_rate_tab(i));
544 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Revproc Rate date :' || l_revproc_rate_date_tab(i));
545 PA_MCB_INVOICE_PKG.log_message('event_amount_conversion: ' || 'Realized gain and Loss Flag :' || l_Rgain_Rloss_flag(i));
546 END IF;
547
548 END LOOP;
549
550
551
552
553
554 /* Updating pa_events table with all converted amounts. Set the
555 revenue_distributed flag = 'N' if conversion fails,
556 so that this event will pick up next time revenue distribution */
557
558
559 /* Bug : 2563738 - Ignore the project amount and attributes if event type is realized gain and loss event
560 l_rgain_rloss_flag = 'Y' (realized_gain and realized_loss event ) */
561
562 FORALL I IN 1 .. l_event_id_tab.COUNT
563 UPDATE pa_events
564 SET project_revenue_amount =
565 DECODE(l_project_status_tab(i), 'N',
566 (DECODE(l_projfunc_status_tab(i), 'N',l_project_amount_tab(i), NULL)), NULL),
567 project_rate_type =
568 DECODE(l_project_status_tab(i), 'N',
569 (DECODE(l_projfunc_status_tab(i), 'N', l_project_rate_type_tab(i),
573 --Modified for Bug3087885
570 project_rate_type)), project_rate_type),
571 project_rev_rate_date =
572 DECODE(l_project_status_tab(i), 'N',
574 -- (DECODE(l_projfunc_status_tab(i), 'N',l_project_rate_date_tab(i), NULL)), NULL),
575 (DECODE(l_projfunc_status_tab(i), 'N',
576 DECODE(l_project_rate_type_tab(i), 'User', null, l_project_rate_date_tab(i)),
577 NULL)), NULL),
578 project_rev_exchange_rate =
579 DECODE(l_project_status_tab(i), 'N',
580 (DECODE(l_projfunc_status_tab(i), 'N',l_project_exchange_rate_tab(i), NULL)), NULL),
581 projfunc_revenue_amount =
582 DECODE(l_project_status_tab(i), 'N',
583 (DECODE(l_projfunc_status_tab(i), 'N',l_projfunc_amount_tab(i), NULL)), NULL),
584 projfunc_rate_type =
585 DECODE(l_project_status_tab(i), 'N',
586 (DECODE(l_projfunc_status_tab(i), 'N', l_projfunc_rate_type_tab(i),
587 projfunc_rate_type)), projfunc_rate_type),
588 projfunc_rev_rate_date =
589 DECODE(l_project_status_tab(i), 'N',
590 --Modified for Bug3087885
591 -- (DECODE(l_projfunc_status_tab(i), 'N',l_projfunc_rate_date_tab(i), NULL)), NULL),
592 (DECODE(l_projfunc_status_tab(i), 'N',
593 DECODE(l_projfunc_rate_type_tab(i), 'User', null, l_projfunc_rate_date_tab(i)),
594 NULL)), NULL),
595 projfunc_rev_exchange_rate =
596 DECODE(l_project_status_tab(i), 'N',
597 (DECODE(l_projfunc_status_tab(i), 'N',l_projfunc_exchange_rate_tab(i), NULL)), NULL),
598 revenue_amount =
599 DECODE(l_revproc_amount_tab(i), NULL, 0, l_revproc_amount_tab(i)),
600 revproc_rate_type =
601 DECODE(l_project_status_tab(i), 'N',
602 (DECODE(l_projfunc_status_tab(i), 'N', l_revproc_rate_type_tab(i),
603 revproc_rate_type)), revproc_rate_type),
604 revproc_rate_date =
605 DECODE(l_project_status_tab(i), 'N',
606 --Modified for Bug3087885
607 -- (DECODE(l_projfunc_status_tab(i), 'N',l_revproc_rate_date_tab(i), revproc_rate_date)),
608 -- revproc_rate_date),
609 (DECODE(l_projfunc_status_tab(i), 'N',
610 DECODE(l_revproc_rate_type_tab(i), 'User', null, l_revproc_rate_date_tab(i)),
611 DECODE(l_revproc_rate_type_tab(i), 'User', null, revproc_rate_date))),
612 DECODE(l_revproc_rate_type_tab(i), 'User', null, revproc_rate_date)),
613 revproc_exchange_rate =
614 DECODE(l_project_status_tab(i), 'N',
615 (DECODE(l_projfunc_status_tab(i), 'N',l_revproc_exchange_rate_tab(i), revproc_exchange_rate)),
616 revproc_exchange_rate),
617 revenue_distributed_flag =
618 DECODE(l_project_status_tab(i), 'N',
619 (DECODE(l_projfunc_status_tab(i), 'N',revenue_distributed_flag, 'N')),
620 'N'),
621 rev_dist_rejection_code =
622 DECODE(l_project_status_tab(i), 'N',
623 (DECODE(l_projfunc_status_tab(i), 'N', NULL, l_projfunc_status_tab(i))),
624 l_project_status_tab(i))
625 WHERE event_id = l_event_id_tab(i);
626
627 IF g1_debug_mode = 'Y' THEN
628 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in Events table : ' || SQL%ROWCOUNT);
629 END IF;
630
631
632 END IF; /* if l_event_id_tab <> 0 */
633
634
635 EXIT WHEN csr_events%NOTFOUND;
636
637
638 END LOOP;
639
640 CLOSE csr_events;
641
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 IF g1_debug_mode = 'Y' THEN
646 PA_MCB_INVOICE_PKG.log_message('Error in Event_amount_conversion ' || sqlerrm);
647 END IF;
648 x_return_status := sqlerrm( sqlcode );
649 END event_amount_conversion;
650
651
652
653 /*----------------------------------------------------------------------------------------+
654 | Procedure : ei_amount_conversion |
655 | Purpose : To update the pa_expenditure_items_all table
656 | (bill transaction currency to revenue processing currency
657 |
658 | Parameters : |
659 | ================================================================================== |
660 | Name Mode Description |
661 | ================================================================================== |
662 | p_project_id IN project Id |
663 | ei_id IN Expenditure item id
667 | x_msg_count IN OUT Error message count |
664 | p_request_id IN Id for the current Run |
665 | p_pa_date IN Project Accounting date |
666 | x_return_status IN OUT Return status of this procedure |
668 | x_msg_data IN OUT Error message |
669 | ================================================================================== |
670 +----------------------------------------------------------------------------------------*/
671
672 PROCEDURE ei_amount_conversion(
673 p_project_id IN NUMBER,
674 p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
675 p_request_id IN NUMBER,
676 p_pa_date IN VARCHAR2,
677 x_return_status IN OUT NOCOPY VARCHAR2,
678 x_msg_count IN OUT NOCOPY NUMBER,
679 x_msg_data IN OUT NOCOPY VARCHAR2,
680 x_rej_reason IN OUT NOCOPY VARCHAR2) IS
681
682
683 CURSOR ei_amt_csr (p_request_id NUMBER) IS
684 SELECT expenditure_item_id,
685 expenditure_item_date, /* Added for bug 5907315*/
686 bill_trans_raw_revenue,
687 bill_trans_adjusted_revenue,
688 bill_trans_currency_code
689 FROM pa_expenditure_items_all
690 WHERE request_id = p_request_id
691 AND revenue_distributed_flag = 'D'
692 AND bill_trans_raw_revenue IS NOT NULL
693 AND raw_revenue IS NULL;
694
695
696 l_ei_date_tab PA_PLSQL_DATATYPES.DateTabTyp; /* Added for bug 5907315*/
697 l_ei_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
698 l_bill_trans_rev_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
699 l_bill_trans_adj_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
700 l_bill_trans_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
701 l_bill_trans_adj_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
702 l_bill_trans_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
703
704 l_revproc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
705 l_revproc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
706 l_revproc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
707 l_revproc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
708 l_revproc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
709
710
711 l_bill_trans_proj_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
712 l_project_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
713 l_project_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
714 l_project_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
715 l_project_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
716 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
717 l_project_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
718
719 l_bill_trans_projfunc_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
720 l_projfunc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
721 l_projfunc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
722 l_projfunc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
723 l_projfunc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
724 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
725 l_projfunc_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
726
727 l_revproc_adj_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
728 l_revproc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
729 l_revproc_adj_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
730
731 l_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
732 l_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
733 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
734 l_raw_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
735
736 l_final_error_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
737
738
739
740 l_project_curr_code VARCHAR2(30);
741 l_project_rate_date_code VARCHAR2(30);
742 l_project_rate_date DATE;
743 l_project_rate_type VARCHAR2(30);
744 l_project_exchange_rate NUMBER;
745
746 l_projfunc_curr_code VARCHAR2(30);
747 l_projfunc_rate_date_code VARCHAR2(30);
748 l_projfunc_rate_date DATE;
749 l_projfunc_rate_type VARCHAR2(30);
750 l_projfunc_exchange_rate NUMBER;
751
752 l_multi_currency_billing_flag VARCHAR2(1);
753 l_baseline_funding_flag VARCHAR2(1);
754 l_revproc_currency_code VARCHAR2(30);
755 l_invproc_currency_type VARCHAR2(30);
756 l_invproc_currency_code VARCHAR2(30);
757 l_funding_rate_date_code VARCHAR2(30);
758 l_funding_rate_type VARCHAR2(30);
759 l_funding_rate_date DATE;
760 l_funding_exchange_rate NUMBER;
761 l_return_status VARCHAR2(1);
762 l_msg_count NUMBER;
763 l_msg_data VARCHAR2(240);
764
768 l_cache_flag VARCHAR2(1);
765 l_pa_date DATE;
766
767 l_conversion_between VARCHAR2(6);
769
770 l_currency_flag VARCHAR2(1):= 'N';
771 /* Variable declaration for bug 5907315 */
772
773 l_previous_project_rate_date DATE;
774 l_previous_projfunc_rate_date DATE;
775
776 /* End of variable declaration: Bug 5907315 */
777
778
779 BEGIN
780
781
782 /* This flag is N then the convert_amount_bulk API not cache the currency code and attributes,
783 If the flag is Y then cache the currency and other attributes to avoid the
784 repeat conversion processing. */
785
786 l_cache_flag := 'Y';
787
788
789 x_rej_reason := NULL;
790
791
792 /* Convert the PA date from character to date */
793
794 /*File.Date.5. Added format to the p_pa_date which was missing*/
795 l_pa_date := TO_DATE(p_pa_date,'YYYY/MM/DD');
796
797
798 IF g1_debug_mode = 'Y' THEN
799 PA_MCB_INVOICE_PKG.log_message('Entering the procedure ei_amount_conversion');
800 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || '-------------------------------------------');
801 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Calling Procedure get_project_defaults');
802 END IF;
803
804
805 -- Get the Project Level Defaults
806
807 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
808 p_project_id => p_project_id,
809 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
810 x_baseline_funding_flag => l_baseline_funding_flag,
811 x_revproc_currency_code => l_revproc_currency_code,
812 x_invproc_currency_type => l_invproc_currency_type,
813 x_invproc_currency_code => l_invproc_currency_code,
814 x_project_currency_code => l_project_curr_code,
815 x_project_bil_rate_date_code => l_project_rate_date_code,
816 x_project_bil_rate_type => l_project_rate_type,
817 x_project_bil_rate_date => l_project_rate_date,
818 x_project_bil_exchange_rate => l_project_exchange_rate,
819 x_projfunc_currency_code => l_projfunc_curr_code,
820 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
821 x_projfunc_bil_rate_type => l_projfunc_rate_type,
822 x_projfunc_bil_rate_date => l_projfunc_rate_date,
823 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
824 x_funding_rate_date_code => l_funding_rate_date_code,
825 x_funding_rate_type => l_funding_rate_type,
826 x_funding_rate_date => l_funding_rate_date,
827 x_funding_exchange_rate => l_funding_exchange_rate,
828 x_return_status => l_return_status,
829 x_msg_count => l_msg_count,
830 x_msg_data => l_msg_data);
831
832
833
834 /* Checking for Project Functional and Project currencies are same, If both are same then
835 Convert only Project Functional and copy the Project Functional attributes to
836 project
837 Here we are setting flag for Whether we need to convert Project amount or not
838 */
839
840
841 l_currency_flag := 'N';
842
843 IF (l_projfunc_curr_code = l_project_curr_code) THEN
844
845 l_currency_flag := 'Y';
846
847 END IF;
848
849 /* Code added for bug 5907315 */
850 fnd_profile_revenue_orig_rate := NVL(fnd_profile.value_specific('PA_REVENUE_ORIGINAL_RATE_FORRECALC'),'N');
851 /* End of bug 5907315 */
852
853
854 /* cursor for select the expenditure details based on current request id */
855
856
857 OPEN ei_amt_csr( p_request_id);
858
859
860
861 LOOP
862
863
864 /*
865 * Clear all PL/SQL table.
866 */
867
868 l_ei_id_tab.delete;
869 l_ei_date_tab.delete; /* Added for bug 5907315*/
870 l_bill_trans_rev_amount_tab.delete;
871 l_bill_trans_bill_rate_tab.delete;
872 l_bill_trans_curr_code_tab.delete;
873 l_bill_trans_adj_rev_tab.delete;
874 l_bill_trans_adj_rate_tab.delete;
875
876 l_revproc_rate_type_tab.delete;
877 l_revproc_rate_date_tab.delete;
878 l_revproc_exchange_rate_tab.delete;
879 l_revproc_curr_code_tab.delete;
880 l_revproc_amount_tab.delete;
881
882 l_project_curr_code_tab.delete;
883 l_project_rate_date_tab.delete;
884 l_project_rate_type_tab.delete;
885 l_project_exchange_rate_tab.delete;
886 l_project_amount_tab.delete;
887 l_project_rev_status_tab.delete;
888
889 l_projfunc_curr_code_tab.delete;
890 l_projfunc_rate_date_tab.delete;
891 l_projfunc_rate_type_tab.delete;
892 l_projfunc_exchange_rate_tab.delete;
893 l_projfunc_amount_tab.delete;
894 l_projfunc_rev_status_tab.delete;
895
896 l_revproc_adj_rev_tab.delete;
900 l_user_validate_flag_tab.delete;
897 l_revproc_bill_rate_tab.delete;
898 l_revproc_adj_rate_tab.delete;
899
901 l_denominator_tab.delete;
902 l_numerator_tab.delete;
903 l_raw_rev_status_tab.delete;
904
905 l_final_error_status_tab.delete;
906
907
908 /* Fetching the expenditure bill transaction value */
909
910
911 FETCH ei_amt_csr BULK COLLECT
912 INTO l_ei_id_tab,
913 l_ei_date_tab,
914 l_bill_trans_rev_amount_tab,
915 l_bill_trans_adj_rev_tab,
916 l_bill_trans_curr_code_tab LIMIT 100;
917 /* Added l_ei_date_tab for bug 5907315*/
918
919 /* If any records select in the fetch then go for conversion */
920
921 IF (l_ei_id_tab.COUNT = 0) THEN
922
923 Exit;
924
925
926 ELSE /* l_ei_id_tab.COUNT <> 0) */
927
928
929 /* Initialize the Array variables to use convert_amount_bulk API */
930
931 FOR I in 1 .. l_ei_id_tab.COUNT
932 LOOP
933
934 l_revproc_amount_tab(I) := NULL;
935 l_user_validate_flag_tab(I) := 'Y';
936 l_project_amount_tab(i) := NULL;
937 l_projfunc_amount_tab(i) := NULL;
938 l_revproc_amount_tab(I) := NULL;
939 l_revproc_adj_rev_tab(I) := NULL;
940 l_revproc_bill_rate_tab(I) := NULL;
941 l_revproc_adj_rate_tab(I) := NULL;
942 l_denominator_tab(I) := NULL;
943 l_numerator_tab(I) := NULL;
944 l_raw_rev_status_tab(i) := 'N';
945 l_project_rev_status_tab(i) := 'N';
946 l_projfunc_rev_status_tab(i) := 'N';
947
948
949 /* Copy the project and project attributed into array variables */
950
951
952 l_project_curr_code_tab(I) := l_project_curr_code;
953 l_project_rate_type_tab(I) := l_project_rate_type;
954 l_project_rate_date_tab(I) := l_project_rate_date;
955 l_project_exchange_rate_tab(I) := l_project_exchange_rate;
956
957 l_projfunc_curr_code_tab(I) := l_projfunc_curr_code;
958 l_projfunc_rate_type_tab(I) := l_projfunc_rate_type;
959 l_projfunc_rate_date_tab(I) := l_projfunc_rate_date;
960 l_projfunc_exchange_rate_tab(I) := l_projfunc_exchange_rate;
961
962
963 /* If Bill transaction adjusted revenue is NOT NULL then take the bill trans
964 adjsuted revenue otherwise take the bill transaction raw revenue revenue */
965
966 l_bill_trans_proj_amt_tab(I)
967 := NVL(l_bill_trans_adj_rev_tab(I), l_bill_trans_rev_amount_tab(I));
968
969 /* Copy the project amount to project functional amount */
970
971
972 l_bill_trans_projfunc_amt_tab(I)
973 := l_bill_trans_proj_amt_tab(I);
974
975 /* Code added for bug 5907315 */
976
977 l_previous_project_rate_date := NULL;
978 l_previous_projfunc_rate_date:= NULL;
979
980 IF fnd_profile_revenue_orig_rate = 'Y' THEN
981
982 begin
983 l_previous_project_rate_date := NULL;
984 l_previous_projfunc_rate_date:= NULL;
985 l_previous_project_rate_date := pa_date(l_ei_date_tab(i));
986 l_previous_projfunc_rate_date:= pa_date(l_ei_date_tab(i));
987
988 EXCEPTION
989 when OTHERS then
990 l_previous_project_rate_date := NULL;
991 l_previous_projfunc_rate_date:= NULL;
992 end;
993
994 END IF;
995
996 /* End of code. Bug 5907315 */
997
998
999 /* Copy the PA date to project rate date */
1000
1001 IF (l_project_rate_date_code = 'PA_INVOICE_DATE') THEN
1002 /* Code commented for bug 5907315
1003 l_project_rate_date_tab(I) := l_pa_date;
1004 The statement is modified as below */
1005 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1006 l_project_rate_date_tab(I) := NVL(l_previous_project_rate_date,l_pa_date);
1007 ELSE
1008 l_project_rate_date_tab(I) := l_pa_date;
1009 END IF;
1010 /* End of bug 5907315 */
1011
1012 END IF;
1013
1014
1015 /* Copy the PA date to project functional rate date */
1016
1017 IF (l_projfunc_rate_date_code = 'PA_INVOICE_DATE') THEN
1018 /* Code commented for bug 5907315
1019 l_projfunc_rate_date_tab(I) := l_pa_date;
1020 The statement is modified as below */
1021 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1022 l_projfunc_rate_date_tab(I) := NVL(l_previous_projfunc_rate_date,l_pa_date);
1023 ELSE
1024 l_projfunc_rate_date_tab(I) := l_pa_date;
1025 END IF;
1026 /* End of bug 5907315 */
1027 END IF;
1028
1029
1030 /* Print the currency attribute value */
1031
1035 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans projfun amount :' || l_bill_trans_projfunc_amt_tab(i));
1032 IF g1_debug_mode = 'Y' THEN
1033 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
1034 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Rev amount :' || l_bill_trans_rev_amount_tab(i));
1036 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Currency Code :' || l_projfunc_curr_code_tab(i));
1037 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Type :' || l_projfunc_rate_type_tab(i));
1038 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Date :' || l_projfunc_rate_date_tab(i));
1039 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Xchg Rate :' || l_projfunc_exchange_rate_tab(i));
1040 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Currency Code :' || l_project_curr_code_tab(i));
1041 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Type :' || l_project_rate_type_tab(i));
1042 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Date :' || l_project_rate_date_tab(i));
1043 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Xchg Rate :' || l_project_exchange_rate_tab(i));
1044 END IF;
1045
1046
1047 END LOOP;
1048
1049
1050
1051 /* Converting Bill Trans Raw revenue to Project Functional Amount(Project Func Amount) */
1052
1053
1054 IF g1_debug_mode = 'Y' THEN
1055 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Calling the procedure convert_amount_bulk for project func amount');
1056 END IF;
1057
1058 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
1059 then the API concatenate the code with error message */
1060
1061 l_conversion_between := 'BTC_PF';
1062
1063
1064 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1065 p_from_currency_tab => l_bill_trans_curr_code_tab,
1066 p_to_currency_tab => l_projfunc_curr_code_tab,
1067 p_conversion_date_tab => l_projfunc_rate_date_tab,
1068 p_conversion_type_tab => l_projfunc_rate_type_tab,
1069 p_amount_tab => l_bill_trans_projfunc_amt_tab,
1070 p_user_validate_flag_tab => l_user_validate_flag_tab,
1071 p_converted_amount_tab => l_projfunc_amount_tab,
1072 p_denominator_tab => l_denominator_tab,
1073 p_numerator_tab => l_numerator_tab,
1074 p_rate_tab => l_projfunc_exchange_rate_tab,
1075 p_conversion_between => l_conversion_between,
1076 p_cache_flag => l_cache_flag,
1077 x_status_tab => l_projfunc_rev_status_tab
1078 );
1079
1080
1081 l_denominator_tab.delete;
1082 l_numerator_tab.delete;
1083
1084
1085 /* Converting Bill Trans Raw revenue to Project Amount(Project Currency) */
1086
1087
1088 IF g1_debug_mode = 'Y' THEN
1089 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Calling the procedure convert_amount_bulk for convert project amount');
1090 END IF;
1091
1092
1093 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
1094 then the API concatenate the code with error message */
1095
1096
1097 IF (l_currency_flag <> 'Y') THEN
1098
1099 l_conversion_between := 'BTC_PC';
1100
1101 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1102 p_from_currency_tab => l_bill_trans_curr_code_tab,
1103 p_to_currency_tab => l_project_curr_code_tab,
1104 p_conversion_date_tab => l_project_rate_date_tab,
1105 p_conversion_type_tab => l_project_rate_type_tab,
1106 p_amount_tab => l_bill_trans_proj_amt_tab,
1107 p_user_validate_flag_tab => l_user_validate_flag_tab,
1108 p_converted_amount_tab => l_project_amount_tab,
1109 p_denominator_tab => l_denominator_tab,
1110 p_numerator_tab => l_numerator_tab,
1111 p_rate_tab => l_project_exchange_rate_tab,
1112 p_conversion_between => l_conversion_between,
1113 p_cache_flag => l_cache_flag,
1114 x_status_tab => l_project_rev_status_tab
1115 );
1116
1117 END IF;
1118
1119
1120 /* Converting Bill trans adjusted revenue to Adjusted revenue
1121 Here not necessary call the conver_amount_bulk API
1122 we have to calculate the adjusted revenue based on rate, which
1123 we got it from previos API */
1124
1125
1126
1127 IF g1_debug_mode = 'Y' THEN
1128 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Id : ' || p_project_id);
1129 END IF;
1130
1131
1132 FOR I in 1 .. l_ei_id_tab.COUNT
1133 LOOP
1134
1135 IF g1_debug_mode = 'Y' THEN
1139
1136 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Inside EI tab .........');
1137 END IF;
1138
1140 /* Checking for Project Functional and Project currencies are same, If both are same then
1141 Convert only Project Functional and copy the Project Functional attributes to
1142 project.
1143 */
1144
1145
1146 IF (l_currency_flag = 'Y') THEN
1147
1148 l_project_curr_code_tab(i) := l_projfunc_curr_code_tab(i);
1149 l_project_rate_type_tab(i) := l_projfunc_rate_type_tab(i);
1150 l_project_rate_date_tab(i) := l_projfunc_rate_date_tab(i);
1151 l_project_exchange_rate_tab(i) := l_projfunc_exchange_rate_tab(i);
1152 l_project_amount_tab(i) := l_projfunc_amount_tab(i);
1153 l_project_rev_status_tab(i) := l_projfunc_rev_status_tab(i);
1154
1155 END IF;
1156
1157
1158
1159 /* If revenue processing currency and project currency both are same then
1160 copy the project attributes to revenue processing attributes */
1161
1162 IF (l_revproc_currency_code = l_project_curr_code) THEN
1163
1164
1165 l_revproc_curr_code_tab(I) := l_project_curr_code_tab(i);
1166 l_revproc_rate_type_tab(I) := l_project_rate_type_tab(i);
1167 l_revproc_rate_date_tab(I) := l_project_rate_date_tab(i);
1168 l_revproc_exchange_rate_tab(I) := l_project_exchange_rate_tab(i);
1169 l_revproc_amount_tab(I) := l_project_amount_tab(i);
1170
1171 /* If revenue processing currency and project functional currency both are same then
1172 copy the project functional attributes to revenue processing attributes */
1173
1174
1175 ELSIF (l_revproc_currency_code = l_projfunc_curr_code) THEN
1176
1177 l_revproc_curr_code_tab(I) := l_projfunc_curr_code_tab(i);
1178 l_revproc_rate_type_tab(I) := l_projfunc_rate_type_tab(i);
1179 l_revproc_rate_date_tab(I) := l_projfunc_rate_date_tab(i);
1180 l_revproc_exchange_rate_tab(I) := l_projfunc_exchange_rate_tab(i);
1181 l_revproc_amount_tab(I) := l_projfunc_amount_tab(i);
1182
1183 END IF;
1184
1185
1186
1187 IF (l_bill_trans_curr_code_tab(I) = l_revproc_curr_code_tab(I)) THEN
1188
1189 l_revproc_adj_rev_tab(I) := l_bill_trans_adj_rev_tab(I) ;
1190
1191 ELSE
1192
1193 l_revproc_adj_rev_tab(I) :=
1194 l_bill_trans_adj_rev_tab(I) * l_revproc_exchange_rate_tab(I);
1195 END IF;
1196
1197
1198
1199 /* If error occur any one of the currency conversion fails then copy
1200 the error code into the variable l_final_error_status_tab
1201 for easy to use in following UPDATE */
1202
1203
1204 l_final_error_status_tab(I) := 'N';
1205
1206 IF l_projfunc_rev_status_tab(I) <> 'N' THEN
1207
1208 l_final_error_status_tab(I) := l_projfunc_rev_status_tab(I);
1209
1210 /* Bug :2135943 - Added for get the any onr rejection reasaon, it will
1211 use for print the rejction reason in the report */
1212
1213 x_rej_reason := l_projfunc_rev_status_tab(I);
1214
1215
1216 ELSIF l_project_rev_status_tab(I) <> 'N' THEN
1217
1218 l_final_error_status_tab(I) := l_project_rev_status_tab(I);
1219
1220 /* Bug :2135943 - Added for get the any onr rejection reasaon, it will
1221 use for print the rejction reason in the report */
1222
1223 x_rej_reason := l_project_rev_status_tab(I);
1224
1225 END IF;
1226
1227
1228
1229 -- Log Messages for EI Converted Amounts
1230
1231 IF g1_debug_mode = 'Y' THEN
1232 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
1233 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
1234 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Raw revenue :' || l_bill_trans_rev_amount_tab(i));
1235 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Adj revenue :' || l_bill_trans_adj_rev_tab(i));
1236 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Currency Code :' || l_project_curr_code_tab(i));
1237 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Type :' || l_project_rate_type_tab(i));
1238 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Date :' || l_project_rate_date_tab(i));
1239 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Xchg Rate :' || l_project_exchange_rate_tab(i));
1240 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Convert Project Amount :' || l_project_amount_tab(i));
1241 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rejection Reason :' || l_project_rev_status_tab(i));
1242 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Convert Project Func Amount :' || l_projfunc_amount_tab(i));
1246 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Xchg Rate :' || l_projfunc_exchange_rate_tab(i));
1243 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Func Curr Code :' || l_projfunc_curr_code_tab(i));
1244 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Type :' || l_projfunc_rate_type_tab(i));
1245 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Date :' || l_projfunc_rate_date_tab(i));
1247 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'ProjFunc Amt Rejection Reason :' || l_projfunc_rev_status_tab(i));
1248 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Convert RevProc Amount :' || l_revproc_amount_tab(i));
1249 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
1250 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Rate Type :' || l_revproc_rate_type_tab(i));
1251 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'RevProc Rate Date :' || l_revproc_rate_date_tab(i));
1252 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Xchg Rate :' || l_revproc_exchange_rate_tab(i));
1253 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Amt Rejection Reason :' || l_raw_rev_status_tab(i));
1254 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Adjusted Reveneue :' || l_revproc_adj_rev_tab(i));
1255 END IF;
1256
1257
1258
1259 END LOOP;
1260
1261
1262
1263 /* Updating the converted amount to the Expenditure Item table.
1264 Converted amount column : raw_revenue, adjusted_revenue, bill_rate, adjusted_rate
1265 Other columns : Initialize when conversion fails and marking revenue
1266 distributed flag to 'N */
1267
1268 IF g1_debug_mode = 'Y' THEN
1269 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Before Updating EI table .....');
1270 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Inside If statement .....');
1271 END IF;
1272
1273 FORALL I IN 1 ..l_ei_id_tab.COUNT
1274 UPDATE pa_expenditure_items_all
1275 SET raw_revenue =
1276 DECODE(l_final_error_status_tab(i), 'N', l_revproc_amount_tab(i), NULL),
1277 adjusted_revenue =
1278 DECODE(l_final_error_status_tab(i), 'N',
1279 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_revproc_adj_rev_tab(i),
1280 l_revproc_curr_code_tab(i)), NULL),
1281 project_raw_revenue =
1282 DECODE(l_final_error_status_tab(i), 'N', l_project_amount_tab(i), NULL),
1283 projfunc_raw_revenue =
1284 DECODE(l_final_error_status_tab(i), 'N', l_projfunc_amount_tab(i), NULL),
1285 bill_trans_raw_revenue =
1286 DECODE(l_final_error_status_tab(i), 'N', bill_trans_raw_revenue, NULL),
1287 bill_trans_adjusted_revenue =
1288 DECODE(l_final_error_status_tab(i), 'N', bill_trans_adjusted_revenue, NULL),
1289 accrued_revenue =
1290 DECODE(l_final_error_status_tab(i), 'N', accrued_revenue, NULL),
1291 accrual_rate =
1292 DECODE(l_final_error_status_tab(i), 'N', accrual_rate, NULL),
1293 revenue_distributed_flag =
1294 DECODE(l_final_error_status_tab(i), 'N', revenue_distributed_flag, 'N'),
1295 rev_dist_rejection_code =
1296 DECODE(l_final_error_status_tab(i), 'N',NULL, l_final_error_status_tab(i)),
1297 revproc_currency_code = l_revproc_curr_code_tab(i),
1298 revproc_rate_type = l_revproc_rate_type_tab(i),
1299 -- revproc_rate_date = l_revproc_rate_date_tab(i), --Modified for Bug3137196
1300 revproc_rate_date = decode(l_revproc_rate_type_tab(i), 'User', null, l_revproc_rate_date_tab(i)),
1301 revproc_exchange_rate = l_revproc_exchange_rate_tab(i),
1302 projfunc_currency_code = l_projfunc_curr_code_tab(i),
1303 project_rev_rate_type = l_project_rate_type_tab(i),
1304 -- project_rev_rate_date = l_project_rate_date_tab(i), --Modified for Bug3137196
1305 project_rev_rate_date = decode(l_project_rate_type_tab(i), 'User', null, l_project_rate_date_tab(i)),
1306 project_rev_exchange_rate = l_project_exchange_rate_tab(i),
1307 projfunc_rev_rate_type = l_projfunc_rate_type_tab(i),
1308 -- projfunc_rev_rate_date = l_projfunc_rate_date_tab(i), --Modified for Bug3137196
1309 projfunc_rev_rate_date = decode(l_projfunc_rate_type_tab(i), 'User', null, l_projfunc_rate_date_tab(i)),
1310 projfunc_rev_exchange_rate = l_projfunc_exchange_rate_tab(i)
1311 WHERE expenditure_item_id = l_ei_id_tab(i);
1312
1313 IF g1_debug_mode = 'Y' THEN
1314 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in EI table : ' || SQL%ROWCOUNT);
1315 END IF;
1316
1317 END IF; /* l_ei_id_tab.COUNT <> 0 */
1318
1322 END LOOP;
1319 EXIT WHEN ei_amt_csr%NOTFOUND;
1320
1321
1323
1324 CLOSE ei_amt_csr;
1325
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329
1330 IF g1_debug_mode = 'Y' THEN
1331 PA_MCB_INVOICE_PKG.log_message('Error in Ei_amount_conversion ' || sqlerrm);
1332 END IF;
1333
1334 x_return_status := sqlerrm( sqlcode );
1335
1336 END ei_amount_conversion;
1337
1338
1339 /*----------------------------------------------------------------------------------------+
1340 | Procedure : rdl_amount_conversion |
1341 | Purpose : To update the RDLltable |
1342 | (bill transaction currency to revenue processing currency) |
1343 | |
1344 | Parameters : |
1345 | ================================================================================== |
1346 | Name Mode Description |
1347 | ================================================================================== |
1348 | p_project_id IN project Id |
1349 | ei_id IN Expenditure item id |
1350 | p_request_id IN Id for the current Run |
1351 | p_raw_revenue IN raw revenue from EI table |
1352 | p_bill_trans_raw_revenue IN bill trans raw revenue from EI table. |
1353 | p_project_raw_revenue IN Project Raw Revenue |
1354 | p_projfunc_raw_revenue IN Project Functional raw Revenue |
1355 | p_funding_rate_date IN Funding Rate Date |
1356 | x_return_status IN OUT Return status of this procedure |
1357 | x_msg_count IN OUT Error message count |
1358 | x_msg_data IN OUT Error message |
1359 | ================================================================================== |
1360 +----------------------------------------------------------------------------------------*/
1361
1362 PROCEDURE rdl_amount_conversion(
1363 p_project_id IN NUMBER,
1364 p_request_id IN NUMBER,
1365 p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
1366 p_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1367 p_bill_trans_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1368 p_project_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1369 p_projfunc_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1370 p_funding_rate_date IN VARCHAR2,
1371 x_return_status IN OUT NOCOPY VARCHAR2,
1372 x_msg_count IN OUT NOCOPY NUMBER,
1373 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1374
1375
1376 CURSOR rdl_amt_csr (p_project_id NUMBER,
1377 p_request_id NUMBER) IS
1378 SELECT rdl.expenditure_item_id,
1379 ei.expenditure_item_date, /* Added for bug 5907315*/
1380 rdl.line_num,
1381 rdl.draft_revenue_num,
1382 rdl.bill_trans_currency_code,
1383 rdl.amount,
1384 rdl.project_currency_code,
1385 rdl.project_rev_rate_type,
1386 rdl.project_rev_rate_date,
1387 rdl.project_rev_exchange_rate,
1388 rdl.projfunc_currency_code,
1389 rdl.projfunc_rev_rate_type,
1390 rdl.projfunc_rev_rate_date,
1391 rdl.projfunc_rev_exchange_rate,
1392 rdl.funding_currency_code,
1393 rdl.funding_rev_rate_type,
1394 rdl.funding_rev_rate_date,
1395 rdl.funding_rev_exchange_rate,
1396 nvl(ei.adjusted_revenue, ei.raw_revenue),
1397 nvl(ei.bill_trans_adjusted_revenue, ei.bill_trans_raw_revenue),
1398 ei.project_raw_revenue,
1399 ei.projfunc_raw_revenue,
1400 RDL.REVTRANS_CURRENCY_CODE,
1401 RDL.REVPROC_REVTRANS_RATE_TYPE,
1402 RDL.REVPROC_REVTRANS_RATE_DATE,
1403 RDL.REVPROC_REVTRANS_EX_RATE
1404 FROM pa_cust_rev_dist_lines rdl,
1405 pa_expenditure_items_all ei
1406 WHERE rdl.project_id = p_project_id
1407 AND ei.expenditure_item_id = rdl.expenditure_item_id
1408 AND rdl.request_id = p_request_id
1409 AND rdl.bill_trans_amount is NULL ;
1410
1411
1412
1413 l_ei_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1414 l_ei_date_tab PA_PLSQL_DATATYPES.DateTabTyp; /* Added for bug 5907315*/
1415 l_bill_trans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1416 l_project_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1417 l_project_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1418 l_project_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1422 l_projfunc_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1419 l_project_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1420 l_projfunc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1421 l_projfunc_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1423 l_projfunc_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1424 l_funding_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1425 l_funding_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1426 l_funding_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1427 l_funding_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1428
1429 l_BTC_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1430
1431 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1432 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1433 l_funding_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1434
1435 l_project_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1436 l_projfunc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1437 l_funding_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1438 l_project_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1439 l_projfunc_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1440 l_funding_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1441 l_project_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1442 l_projfunc_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1443 l_funding_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1444 l_project_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1445 l_projfunc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1446 l_funding_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1447
1448 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1449
1450 l_revenue_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1451
1452 l_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1453
1454 l_draft_revenue_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1455
1456 l_error_draft_rev_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1457 l_error_ei_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1458 l_error_funding_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1459
1460 l_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1461 l_bill_trans_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1462 l_project_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1463 l_projfunc_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1464
1465
1466 l_funding_rate_date DATE;
1467
1468 l_counter NUMBER;
1469
1470 l_conversion_between VARCHAR2(6);
1471 l_cache_flag VARCHAR2(1);
1472
1473 l_pf_currency_flag VARCHAR2(1) := 'N';
1474 l_prj_currency_flag VARCHAR2(1) := 'N';
1475
1476 /* Revenue in foreign currency */
1477 l_revtrans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1478 l_revtrans_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1479 l_revtrans_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1480 l_revtrans_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1481 l_mcb_flag VARCHAR2(1);
1482 l_inv_by_btc_flag VARCHAR2(1);
1483 l_rev_in_txn_curr_flag VARCHAR2(1);
1484 l_pf_currency_flag_rtc_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1485 l_bt_currency_flag_rtc_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1486 l_revtrans_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1487 l_error_revtrans_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1488 l_PFC_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1489 l_revtrans_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1490 l_revtrans_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1491 l_revtrans_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1492
1493 l_pf_currency_code_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1494 l_rt_currency_code_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1495 l_rt_rate_date_t1_tab PA_PLSQL_DATATYPES.DateTabTyp;
1496 l_rt_rate_type_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1497 l_pf_amount_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1498 l_user_validate_flag_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1499 l_rt_amount_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1500 l_rt_denominator_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1501 l_rt_numerator_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1502 l_rt_xchg_rate_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1503 l_rt_status_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1504 l_error_rt_dr_rev_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1505
1506 /* Variable declaration for bug 5907315 */
1507
1508 l_previous_funding_rate_date DATE;
1509
1510 /* End of variable declaration: Bug 5907315 */
1511
1512 BEGIN
1513
1514
1515 /* Initiallizing the Currency variable for check PF and Project curency are same
1516 for Funding Currency or not *
1517
1518 l_pf_currency_flag := 'N';
1519 l_prj_currency_flag := 'N';
1520
1521
1525
1522 /* This flag is N then the convert_amount_bulk API not cache any currency code,
1523 If the flag is Y then it cache the currency and other attributes for avoid the
1524 repeat processing. */
1526
1527 l_cache_flag := 'N';
1528
1529
1530 IF g1_debug_mode = 'Y' THEN
1531 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Inside the Procedure RDL AMOUT conversion');
1532 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '-----------------------------------------');
1533 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'project Id ' || p_project_id);
1534 END IF;
1535
1536
1537
1538 /* Convert the funding rate date charater to DATE format */
1539
1540 /*File.Date.5. Added format to the p_funding_rate_date which was missing*/
1541 l_funding_rate_date := TO_DATE(p_funding_rate_date,'YYYY/MM/DD');
1542
1543 /* Revenue in foreign currency */
1544
1545 l_mcb_flag := pa_billing.globvars.mcb_flag;
1546 l_inv_by_btc_flag := pa_billing.globvars.inv_by_btc_flag;
1547 l_rev_in_txn_curr_flag := pa_billing.globvars.rev_in_txn_curr_flag;
1548
1549
1550 OPEN rdl_amt_csr( p_project_id,
1551 p_request_id);
1552
1553
1554 LOOP
1555
1556 /* Added l_ei_date_tab for bug 5907315*/
1557 FETCH rdl_amt_csr BULK COLLECT
1558 INTO l_ei_id_tab,
1559 l_ei_date_tab,
1560 l_line_num_tab,
1561 l_draft_revenue_num_tab,
1562 l_bill_trans_currency_code_tab,
1563 l_revenue_amount_tab,
1564 l_project_currency_code_tab,
1565 l_project_rev_rate_type_tab,
1566 l_project_rev_rate_date_tab,
1567 l_project_rev_xchg_rate_tab,
1568 l_projfunc_currency_code_tab,
1569 l_projfunc_rev_rate_type_tab,
1570 l_projfunc_rev_rate_date_tab,
1571 l_projfunc_rev_xchg_rate_tab,
1572 l_funding_currency_code_tab,
1573 l_funding_rev_rate_type_tab,
1574 l_funding_rev_rate_date_tab,
1575 l_funding_rev_xchg_rate_tab,
1576 l_raw_revenue,
1577 l_bill_trans_raw_revenue,
1578 l_project_raw_revenue,
1579 l_projfunc_raw_revenue,
1580 l_revtrans_currency_code_tab,
1581 l_revtrans_rate_type_tab,
1582 l_revtrans_rate_date_tab,
1583 l_revtrans_xchg_rate_tab LIMIT 100;
1584
1585
1586
1587 /* If fetch return more than one row then proceeding for the conversion */
1588
1589
1590 IF (l_ei_id_tab.COUNT = 0) THEN
1591
1592 Exit;
1593
1594 ELSE /* (l_ei_id_tab.COUNT <> 0) */
1595
1596
1597 FOR I in 1..l_ei_id_tab.COUNT
1598 LOOP
1599
1600
1601
1602 /* Checking for Project Functional and Funding Currencies are same or
1603 Project and Funding currencies are same the copy the Project Functional and
1604 Project currency to Funding currency
1605 If not same then we need to convert the funding amount
1606 */
1607
1608
1609 IF g1_debug_mode = 'Y' THEN
1610 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PF Currency ' || l_projfunc_currency_code_tab(i));
1611 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PC Currency ' || l_project_currency_code_tab(i));
1612 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'FC Currency ' || l_funding_currency_code_tab(i));
1613 END IF;
1614
1615
1616
1617
1618 IF (l_projfunc_currency_code_tab(i) = l_funding_currency_code_tab(i)) THEN
1619
1620 l_pf_currency_flag := 'Y';
1621
1622 END IF;
1623
1624
1625 IF (l_project_currency_code_tab(i) = l_funding_currency_code_tab(i)) THEN
1626
1627 l_prj_currency_flag := 'Y';
1628
1629 END IF;
1630
1631 /* Revenue in foreign currency */
1632 l_pf_currency_flag_rtc_tab(i) := 'N';
1633 l_bt_currency_flag_rtc_tab(i) := 'N';
1634 If l_mcb_flag = 'Y' AND l_inv_by_btc_flag = 'N' AND l_rev_in_txn_curr_flag = 'Y' Then
1635
1636 IF (l_projfunc_currency_code_tab(i) = l_revtrans_currency_code_tab(i)) THEN
1637
1638 l_pf_currency_flag_rtc_tab(i) := 'Y';
1639
1640 ELSIF (l_bill_trans_currency_code_tab(i) = l_revtrans_currency_code_tab(i)) THEN
1641
1642 l_bt_currency_flag_rtc_tab(i) := 'Y';
1643
1644 ELSIF (l_bill_trans_currency_code_tab(i) <> l_revtrans_currency_code_tab(i)) THEN
1645
1646 l_bt_currency_flag_rtc_tab(i) := 'N';
1647 l_pf_currency_flag_rtc_tab(i) := 'N';
1648
1649 END IF;
1650
1651 End If;
1652
1653 /* Initializing the array variables */
1654
1655
1656 l_user_validate_flag_tab(I) := 'Y';
1657
1658 l_project_bill_rate_tab(I) := NULL;
1659 l_projfunc_bill_rate_tab(I) := NULL;
1660 l_funding_bill_rate_tab(I) := NULL;
1661 l_project_denominator_tab(I) := NULL;
1662 l_projfunc_denominator_tab(I) := NULL;
1663 l_funding_denominator_tab(I) := NULL;
1667 l_project_status_tab(I) := 'N';
1664 l_project_numerator_tab(I) := NULL;
1665 l_projfunc_numerator_tab(I) := NULL;
1666 l_funding_numerator_tab(I) := NULL;
1668 l_projfunc_status_tab(I) := 'N';
1669 l_funding_status_tab(I) := 'N';
1670 l_funding_amount_tab(i) := NULL;
1671
1672 l_projfunc_amount_tab(I) := null;
1673 l_revtrans_amount_tab(I) := null;
1674 l_revtrans_denominator_tab(I) := null;
1675 l_revtrans_numerator_tab(I) := null;
1676 l_revtrans_status_tab(I) := 'N';
1677 /* Copy the funding rate date to array variable */
1678 /* Code added for bug 5907315 */
1679 l_previous_funding_rate_date := NULL;
1680
1681 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1682 l_previous_funding_rate_date := pa_date(l_ei_date_tab(I));
1683 END IF;
1684 /* End of code. Bug 5907315 */
1685
1686 /* Code commented for bug 5907315
1687 l_funding_rev_rate_date_tab(I) := l_funding_rate_date;
1688 The statement is modified as below */
1689 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1690 l_funding_rev_rate_date_tab(I) := nvl(l_previous_funding_rate_date,l_funding_rate_date);
1691 ELSE
1692 l_funding_rev_rate_date_tab(I) := l_funding_rate_date;
1693 END IF;
1694 /* End of bug 5907315 */
1695
1696 /* Calculating BTC amount from bill trans raw revenue and raw revenue */
1697
1698 l_BTC_amount_tab(I) :=
1699 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1700 ( l_revenue_amount_tab(I) / l_raw_revenue(I) ) * l_bill_trans_raw_revenue(I),l_bill_trans_currency_code_tab(i));
1701
1702
1703 /* Calculating the project amount from bill trans raw revenue and raw revenue */
1704
1705 l_project_amount_tab(I) := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1706 ( l_revenue_amount_tab(I) / l_raw_revenue(I) ) * l_project_raw_revenue(I),l_project_currency_code_tab(i));
1707
1708
1709 /* Calculating project functional amount from bill trans raw revenue and raw revenue */
1710
1711
1712 l_projfunc_amount_tab(I) := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1713 ( l_revenue_amount_tab(I) / l_raw_revenue(I) ) * l_projfunc_raw_revenue(I), l_projfunc_currency_code_tab(i));
1714
1715
1716 /* Debug message for MCB testing */
1717
1718
1719 IF g1_debug_mode = 'Y' THEN
1720 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans curr Code :' || l_bill_trans_currency_code_tab(i));
1721 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input revenue amount :' || l_revenue_amount_tab(i));
1722 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input Raw revenue :' || l_raw_revenue(i));
1723 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input Bill trans raw rev:' || l_bill_trans_raw_revenue(i));
1724 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input Project raw rev :' || l_project_raw_revenue(i));
1725 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input projfunc raw rev :' || l_projfunc_raw_revenue(i));
1726 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Calculated Project amount:' || l_project_amount_tab(i));
1727 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Calculated projfunc amount:' || l_projfunc_amount_tab(i));
1728 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Curr Code :' || l_funding_currency_code_tab(i));
1729 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate Type :' || l_funding_rev_rate_type_tab(i));
1730 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate date :' || l_funding_rev_rate_date_tab(i));
1731 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding xchg rate :' || l_funding_rev_xchg_rate_tab(i));
1732 END IF;
1733
1734 END LOOP;
1735
1736
1737
1738 -- BTC amounts to Funding currency amount
1739
1740
1741 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
1742 then the API concatenate the code with error message */
1743
1744
1745 IF g1_debug_mode = 'Y' THEN
1746 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Calling convert_amount_bulk API for funding conversion');
1747 END IF;
1748
1749
1750 /* If Funding Currency are not equal to project and Project Functional currency then
1751 Convert the funding amount otherwise copy the Project or project Function attributes
1752 to funding currency */
1753
1754
1755 IF g1_debug_mode = 'Y' THEN
1756 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PF Currency flag ' || l_pf_currency_flag);
1757 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PC Currency flag ' || l_prj_currency_flag);
1758 END IF;
1759
1760
1761
1762
1763 IF (l_pf_currency_flag <> 'Y') AND (l_prj_currency_flag <> 'Y') THEN
1764
1765 l_conversion_between := 'BTC_FC';
1766
1767
1768 IF g1_debug_mode = 'Y' THEN
1769 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding is not equal to PF and project .....');
1770 END IF;
1771
1772 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1773 p_from_currency_tab => l_bill_trans_currency_code_tab,
1777 p_amount_tab => l_BTC_amount_tab,
1774 p_to_currency_tab => l_funding_currency_code_tab,
1775 p_conversion_date_tab => l_funding_rev_rate_date_tab,
1776 p_conversion_type_tab => l_funding_rev_rate_type_tab,
1778 p_user_validate_flag_tab => l_user_validate_flag_tab,
1779 p_converted_amount_tab => l_funding_amount_tab,
1780 p_denominator_tab => l_funding_denominator_tab,
1781 p_numerator_tab => l_funding_numerator_tab,
1782 p_rate_tab => l_funding_rev_xchg_rate_tab,
1783 p_conversion_between => l_conversion_between,
1784 p_cache_flag => l_cache_flag,
1785 x_status_tab => l_funding_status_tab
1786 );
1787
1788
1789 END IF;
1790
1791
1792 IF g1_debug_mode = 'Y' THEN
1793 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'after Calling convert_amount_bulk API for funding conversion');
1794 END IF;
1795
1796 l_counter := 1;
1797
1798
1799 FOR I in 1 ..l_ei_id_tab.COUNT
1800 LOOP
1801
1802
1803 /* If Funding Currency are not equal to project and Project Functional currency then
1804 Convert the funding amount otherwise copy the Project or project Function attributes
1805 to funding currency */
1806
1807
1808 IF (l_pf_currency_flag = 'Y') THEN
1809
1810 IF g1_debug_mode = 'Y' THEN
1811 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....PF Currency = Funding currency.....');
1812 END IF;
1813
1814 l_funding_currency_code_tab(i) := l_projfunc_currency_code_tab(i);
1815 l_funding_rev_rate_type_tab(i) := l_projfunc_rev_rate_type_tab(i);
1816 l_funding_rev_rate_date_tab(i) := l_projfunc_rev_rate_date_tab(i);
1817 l_funding_rev_xchg_rate_tab(i) := l_projfunc_rev_xchg_rate_tab(i);
1818 l_funding_amount_tab(i) := l_projfunc_amount_tab(i);
1819 l_funding_status_tab(i) := 'N';
1820
1821 ELSIF (l_prj_currency_flag = 'Y') THEN
1822
1823 IF g1_debug_mode = 'Y' THEN
1824 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....Project currency = Funding currency.....');
1825 END IF;
1826
1827 l_funding_currency_code_tab(i) := l_project_currency_code_tab(i);
1828 l_funding_rev_rate_type_tab(i) := l_project_rev_rate_type_tab(i);
1829 l_funding_rev_rate_date_tab(i) := l_project_rev_rate_date_tab(i);
1830 l_funding_rev_xchg_rate_tab(i) := l_project_rev_xchg_rate_tab(i);
1831 l_funding_amount_tab(i) := l_project_amount_tab(i);
1832 l_funding_status_tab(i) := 'N';
1833
1834 END IF;
1835
1836
1837 /* Get the draft revenue number for conversion fail cases
1838 to mark in pa_draft_revenues table */
1839
1840
1841 IF g1_debug_mode = 'Y' THEN
1842 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Inside the loop ........');
1843 END IF;
1844
1845 IF l_funding_status_tab(i) <> 'N' THEN
1846
1847
1848 l_error_draft_rev_num_tab(l_counter) := l_draft_revenue_num_tab(I);
1849
1850 l_error_ei_id_tab(l_counter) := l_ei_id_tab(I);
1851
1852 l_error_funding_status_tab(l_counter) := l_funding_status_tab(I);
1853
1854 l_counter := l_counter + 1;
1855
1856
1857 END IF;
1858
1859
1860
1861 -- Log Messages for Events Converted Amounts
1862
1863 IF g1_debug_mode = 'Y' THEN
1864 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
1865 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Line Num :' || l_line_num_tab(i));
1866 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Draft revenue Num :' || l_draft_revenue_num_tab(i));
1867 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_currency_code_tab(i));
1868 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Bill Trans Amount:' || l_BTC_amount_tab(i));
1869 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Amount :' || l_project_amount_tab(i));
1870 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Func Amount :' || l_projfunc_amount_tab(i));
1871 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Curr Code :' || l_funding_currency_code_tab(i));
1872 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate Type :' || l_funding_rev_rate_type_tab(i));
1873 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate date :' || l_funding_rev_rate_date_tab(i));
1874 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Echg rate :' || l_funding_rev_xchg_rate_tab(i));
1875 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Amt Rejection Reason :' || l_funding_status_tab(i));
1879
1876 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Convert Funding Amount :' || l_funding_amount_tab(i));
1877 END IF;
1878
1880
1881 IF l_error_draft_rev_num_tab.EXISTS(i) THEN
1882
1883 IF g1_debug_mode = 'Y' THEN
1884 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Error Draft revenue num :' || l_error_draft_rev_num_tab(i));
1885 END IF;
1886
1887 END IF;
1888
1889
1890 END LOOP;
1891
1892 IF g1_debug_mode = 'Y' THEN
1893 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'after Calling convert_amount_bulk API for funding conversion');
1894 END IF;
1895
1896 l_counter := 1;
1897
1898
1899 FOR I in 1 .. l_ei_id_tab.COUNT
1900 LOOP
1901
1902 IF l_rev_in_txn_curr_flag = 'Y' AND l_mcb_flag = 'Y' AND l_inv_by_btc_flag = 'N' THEN
1903
1904 /* If revenue txn Currency is not equal to transaction and Project Functional currency then
1905 Convert the revenue txn amount otherwise copy the transaction or project Functional attributes
1906 to revenue txn currency */
1907
1908 -- Log Messages
1909
1910 IF g1_debug_mode = 'Y' THEN
1911 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
1912 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Line Num :' || l_line_num_tab(i));
1913 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Draft revenue Num :' || l_draft_revenue_num_tab(i));
1914 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'Bill Trans Currency Code :'||l_bill_trans_currency_code_tab(i));
1915 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans Amount:' || l_BTC_amount_tab(i));
1916 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Func Amount :' || l_projfunc_amount_tab(i));
1917 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Curr Code :' || l_revtrans_currency_code_tab(i));
1918 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate Type :' || l_revtrans_rate_type_tab(i));
1919 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate date :' || l_revtrans_rate_date_tab(i));
1920 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Echg rate :' || l_revtrans_xchg_rate_tab(i));
1921 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rejection Reason :' || l_revtrans_status_tab(i));
1922 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Convert Revtrans Amount :' || l_revtrans_amount_tab(i));
1923 END IF;
1924
1925
1926 IF (l_pf_currency_flag_rtc_tab(i) = 'Y') THEN
1927
1928 IF g1_debug_mode = 'Y' THEN
1929 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....PF Currency = RT Currency.....');
1930 END IF;
1931
1932 l_revtrans_rate_type_tab(i) := NULL;
1933 l_revtrans_rate_date_tab(i) := NULL;
1934 l_revtrans_xchg_rate_tab(i) := NULL;
1935 l_revtrans_amount_tab(i) := l_projfunc_amount_tab(i);
1936 l_revtrans_status_tab(i) := 'N';
1937
1938 ELSIF (l_bt_currency_flag_rtc_tab(i) = 'Y') THEN
1939
1940 IF g1_debug_mode = 'Y' THEN
1941 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....BT Currency = RT currency.....');
1942 END IF;
1943
1944 l_revtrans_rate_type_tab(i) := l_projfunc_rev_rate_type_tab(i);
1945 l_revtrans_rate_date_tab(i) := l_projfunc_rev_rate_date_tab(i);
1946 l_revtrans_xchg_rate_tab(i) := 1/l_projfunc_rev_xchg_rate_tab(i);
1947 l_revtrans_amount_tab(i) := l_BTC_amount_tab(i);
1948 l_revtrans_status_tab(i) := 'N';
1949
1950 ELSIF ((l_pf_currency_flag_rtc_tab(i) <> 'Y') AND (l_bt_currency_flag_rtc_tab(i) <> 'Y'))
1951 THEN
1952
1953 l_conversion_between := 'RC_RTC';
1954
1955
1956 IF g1_debug_mode = 'Y' THEN
1957 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revenue Txn is not equal to PFC and BTC .....');
1958 END IF;
1959
1960 l_pf_currency_code_t1_tab(1) := null;
1961 l_rt_currency_code_t1_tab(1) := null;
1962 l_rt_rate_date_t1_tab(1) := null;
1963 l_rt_rate_type_t1_tab(1) := null;
1964 l_pf_amount_t1_tab(1) := null;
1965 l_user_validate_flag_t1_tab(1) := 'N';
1966 l_rt_amount_t1_tab(1) := null;
1967 l_rt_denominator_t1_tab(1) := null;
1968 l_rt_numerator_t1_tab(1) := null;
1969 l_rt_xchg_rate_t1_tab(1) := null;
1970 l_rt_status_t1_tab(1) := 'N';
1971
1972 l_pf_currency_code_t1_tab(1) := l_projfunc_currency_code_tab(i);
1973 l_rt_currency_code_t1_tab(1) := l_revtrans_currency_code_tab(i);
1974 l_rt_rate_date_t1_tab(1) := l_revtrans_rate_date_tab(i);
1975 l_rt_rate_type_t1_tab(1) := l_revtrans_rate_type_tab(i);
1976 l_pf_amount_t1_tab(1) := l_projfunc_amount_tab(i);
1977 l_user_validate_flag_t1_tab(1) := l_user_validate_flag_tab(i);
1978 l_rt_xchg_rate_t1_tab(1) := l_revtrans_xchg_rate_tab(i); -- Bug 4760091
1979
1980 IF g1_debug_mode = 'Y' THEN
1984 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1981 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'Before convert_amount_bulk to derive rtc amt.....');
1982 END IF;
1983
1985 p_from_currency_tab => l_pf_currency_code_t1_tab,
1986 p_to_currency_tab => l_rt_currency_code_t1_tab,
1987 p_conversion_date_tab => l_rt_rate_date_t1_tab,
1988 p_conversion_type_tab => l_rt_rate_type_t1_tab,
1989 p_amount_tab => l_pf_amount_t1_tab,
1990 p_user_validate_flag_tab => l_user_validate_flag_t1_tab,
1991 p_converted_amount_tab => l_rt_amount_t1_tab,
1992 p_denominator_tab => l_rt_denominator_t1_tab,
1993 p_numerator_tab => l_rt_numerator_t1_tab,
1994 p_rate_tab => l_rt_xchg_rate_t1_tab,
1995 p_conversion_between => l_conversion_between,
1996 p_cache_flag => l_cache_flag,
1997 x_status_tab => l_rt_status_t1_tab
1998 );
1999
2000 IF g1_debug_mode = 'Y' THEN
2001 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'After convert_amount_bulk to derive RTC Amt.....');
2002 END IF;
2003
2004 l_revtrans_rate_date_tab(i) := trunc(l_rt_rate_date_t1_tab(1));
2005 l_revtrans_rate_type_tab(i) := l_rt_rate_type_t1_tab(1);
2006 l_revtrans_amount_tab(i):= l_rt_amount_t1_tab(1);
2007 l_revtrans_denominator_tab(i):= l_rt_denominator_t1_tab(1);
2008 l_revtrans_numerator_tab(i) := l_rt_numerator_t1_tab(1);
2009 l_revtrans_xchg_rate_tab(i) := l_rt_xchg_rate_t1_tab(1);
2010 l_revtrans_status_tab(i) := l_rt_status_t1_tab(1);
2011
2012 IF g1_debug_mode = 'Y' THEN
2013 PA_MCB_INVOICE_PKG.log_message('Rev trans rate date after conv is.....'||l_revtrans_rate_date_tab(i));
2014 PA_MCB_INVOICE_PKG.log_message('l_revtrans_status_tab('||i||') is.....'||l_revtrans_status_tab(i));
2015 END IF;
2016 END IF;
2017 ELSE
2018 IF g1_debug_mode = 'Y' THEN
2019 PA_MCB_INVOICE_PKG.log_message('Revenue not in foreign curr/mcb disabled/inv by btc enabled...');
2020 END IF;
2021 l_revtrans_currency_code_tab(i) := l_projfunc_currency_code_tab(i);
2022 l_revtrans_rate_type_tab(i) := NULL;
2023 l_revtrans_rate_date_tab(i) := NULL;
2024 l_revtrans_xchg_rate_tab(i) := NULL;
2025 l_revtrans_amount_tab(i) := l_projfunc_amount_tab(i);
2026 l_revtrans_status_tab(i) := 'N';
2027
2028 -- Log Messages
2029
2030 IF g1_debug_mode = 'Y' THEN
2031 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
2032 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Line Num :' || l_line_num_tab(i));
2033 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Draft revenue Num :' || l_draft_revenue_num_tab(i));
2034 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'Bill Trans Currency Code :'||l_bill_trans_currency_code_tab(i));
2035 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans Amount:' || l_BTC_amount_tab(i));
2036 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Func Amount :' || l_projfunc_amount_tab(i));
2037 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Curr Code :' || l_revtrans_currency_code_tab(i));
2038 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate Type :' || l_revtrans_rate_type_tab(i));
2039 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate date :' || l_revtrans_rate_date_tab(i));
2040 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Echg rate :' || l_revtrans_xchg_rate_tab(i));
2041 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rejection Reason :' || l_revtrans_status_tab(i));
2042 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Convert Revtrans Amount :' || l_revtrans_amount_tab(i));
2043 END IF;
2044
2045 END IF;
2046
2047
2048 /* Get the draft revenue number for conversion fail cases
2049 to mark in pa_draft_revenues table */
2050
2051
2052 IF g1_debug_mode = 'Y' THEN
2053 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Before setting the error status........');
2054 END IF;
2055
2056 IF l_revtrans_status_tab(i) <> 'N' THEN
2057
2058
2059 l_error_rt_dr_rev_num_tab(l_counter) := l_draft_revenue_num_tab(I);
2060
2061 l_error_ei_id_tab(l_counter) := l_ei_id_tab(I);
2062
2063 l_error_revtrans_status_tab(l_counter) := l_revtrans_status_tab(I);
2064
2065 l_counter := l_counter + 1;
2066
2067
2068 END IF;
2069
2070
2071 IF l_error_draft_rev_num_tab.EXISTS(i) THEN
2072
2073 IF g1_debug_mode = 'Y' THEN
2074 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Error Draft revenue num :' || l_error_draft_rev_num_tab(i));
2075 END IF;
2076
2077 END IF;
2078
2079
2080 END LOOP;
2081
2082 /* i) Updating the converted project amount, project functional amount,
2083 funding amount and update the rev_dist_rejection_code
2087 as per the funding currency code, so not necessary to call */
2084 ii) Call the round currency function for bill_trans_amount, project_revenue_amount,
2085 projfunc_revenue_amount for calculated values
2086 iii) Funding amount converted through the convert_amount_bulk API, its rounded automatically
2088
2089
2090 FORALL I IN 1 .. l_ei_id_tab.COUNT
2091 UPDATE pa_cust_rev_dist_lines
2092 SET bill_trans_amount =
2093 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2094 l_BTC_amount_tab(i),l_bill_trans_currency_code_tab(i)),
2095 project_revenue_amount =
2096 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2097 l_project_amount_tab(i),l_project_currency_code_tab(i)),
2098 projfunc_revenue_amount =
2099 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2100 l_projfunc_amount_tab(i),l_projfunc_currency_code_tab(i)),
2101 funding_revenue_amount =
2102 DECODE(l_funding_status_tab(I), 'N',
2103 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2104 l_funding_amount_tab(I),l_funding_currency_code_tab(i)),
2105 NULL),
2106 funding_rev_rate_type =
2107 DECODE(l_funding_status_tab(I), 'N', l_funding_rev_rate_type_tab(I),NULL),
2108 funding_rev_rate_date =
2109 DECODE(l_funding_status_tab(I), 'N', l_funding_rev_rate_date_tab(i),funding_rev_rate_date),
2110 funding_rev_exchange_rate =
2111 DECODE(l_funding_status_tab(I), 'N', l_funding_rev_xchg_rate_tab(i), funding_rev_exchange_rate),
2112 revtrans_currency_code =
2113 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_currency_code_tab(I),NULL),
2114 revtrans_amount =
2115 DECODE(l_revtrans_status_tab(I), 'N',
2116 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2117 l_revtrans_amount_tab(I),l_revtrans_currency_code_tab(i)), NULL),
2118 revproc_revtrans_rate_type =
2119 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_rate_type_tab(I),NULL),
2120 revproc_revtrans_rate_date =
2121 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_rate_date_tab(i),NULL),
2122 revproc_revtrans_ex_rate =
2123 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_xchg_rate_tab(i), NULL)
2124 WHERE expenditure_item_id = l_ei_id_tab(I)
2125 AND line_num = l_line_num_tab(I);
2126
2127 IF g1_debug_mode = 'Y' THEN
2128 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in RDL :' || SQL%ROWCOUNT);
2129 END IF;
2130
2131
2132 /* Marking Draft Revenues as error, If error exist in RDL amount conversion */
2133
2134 PA_MCB_INVOICE_PKG.log_message('Before updating RTC error code..BPC...:' );
2135
2136 IF l_rev_in_txn_curr_flag = 'Y' THEN
2137 IF l_error_rt_dr_rev_num_tab.COUNT <> 0 THEN
2138
2139 FORALL J IN 1 .. l_error_rt_dr_rev_num_tab.COUNT
2140 UPDATE pa_draft_revenues
2141 SET generation_error_flag = 'Y',
2142 transfer_rejection_reason = l_error_revtrans_status_tab(j)
2143 WHERE project_id = p_project_id
2144 AND draft_revenue_num = l_error_rt_dr_rev_num_tab(J);
2145
2146 IF g1_debug_mode = 'Y' THEN
2147 PA_MCB_INVOICE_PKG.log_message('No of Rows Marked Error in Draft Revenue :' || SQL%ROWCOUNT);
2148 END IF;
2149
2150 END IF;
2151 END IF;
2152
2153 IF l_error_draft_rev_num_tab.COUNT <> 0 THEN
2154
2155 FORALL J IN 1 .. l_error_draft_rev_num_tab.COUNT
2156 UPDATE pa_draft_revenues
2157 SET generation_error_flag = 'Y',
2158 transfer_rejection_reason = l_error_funding_status_tab(J)
2159 WHERE project_id = p_project_id
2160 AND draft_revenue_num = l_error_draft_rev_num_tab(J);
2161
2162 IF g1_debug_mode = 'Y' THEN
2163 PA_MCB_INVOICE_PKG.log_message('No of Rows Marked Error in Draft Revenue :' || SQL%ROWCOUNT);
2164 END IF;
2165
2166 END IF;
2167
2168 PA_MCB_INVOICE_PKG.log_message('After updating error code..BPC...:' );
2169
2170
2171 END IF; /* l_ei_id_tab.COUNT <> 0 */
2172
2173
2174
2175
2176 EXIT WHEN rdl_amt_csr%NOTFOUND;
2177
2178
2179 END LOOP;
2180
2181 CLOSE rdl_amt_csr;
2182
2183
2184 EXCEPTION
2185 WHEN OTHERS THEN
2186 IF g1_debug_mode = 'Y' THEN
2187 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Error in rdl conversion :' || sqlerrm);
2188 END IF;
2189 x_return_status := sqlerrm( sqlcode );
2190
2191 END rdl_amount_conversion;
2192
2193
2197 | (bill transaction currency to Funding Currency) |
2194 /*----------------------------------------------------------------------------------------+
2195 | Procedure : erdl_amount_conversion |
2196 | Purpose : To update the ERDL table |
2198 | |
2199 | Parameters : |
2200 | ================================================================================== |
2201 | Name Mode Description |
2202 | ================================================================================== |
2203 | p_btc_code IN Bill transaction currency code |
2204 | p_btc_amount IN Bill transaction amount |
2205 | p_funding_curr_code IN Funding currency code to convert funding amount |
2206 | x_funding_rate_type IN OUT Funding Rate type to convert funding amount |
2207 | x_funding_rate_rate IN OUT Funding Rate date to convert funding amount |
2208 | x_funding_exchange_rate IN OUT Funding Exchange Rate to convert funding amount |
2209 | x_funding_amount IN OUT Converted funding amount |
2210 | x_funding_convert_status IN OUT If converted the pass NULL else pass error code |
2211 | x_return_status IN OUT Return status of this procedure |
2212 | x_msg_count IN OUT Error message count |
2213 | x_msg_data IN OUT Error message |
2214 | ================================================================================== |
2215 +----------------------------------------------------------------------------------------*/
2216
2217 PROCEDURE erdl_amount_conversion( p_project_id IN NUMBER,
2218 p_draft_revenue_num IN NUMBER,
2219 p_btc_code IN VARCHAR2,
2220 p_btc_amount IN VARCHAR2,
2221 p_funding_rate_date IN VARCHAR2,
2222 p_funding_curr_code IN VARCHAR2,
2223 x_funding_rate_type IN OUT NOCOPY VARCHAR2,
2224 x_funding_rate_date IN OUT NOCOPY VARCHAR2,
2225 x_funding_exchange_rate IN OUT NOCOPY VARCHAR2,
2226 x_funding_amount IN OUT NOCOPY VARCHAR2,
2227 x_funding_convert_status IN OUT NOCOPY VARCHAR2,
2228 p_projfunc_curr_code IN VARCHAR2,
2229 p_projfunc_amount IN VARCHAR2,
2230 p_projfunc_rate_type IN VARCHAR2,
2231 p_projfunc_rate_date IN VARCHAR2,
2232 p_projfunc_exch_rate IN VARCHAR2,
2233 p_revtrans_curr_code IN VARCHAR2,
2234 p_calling_place IN VARCHAR2,
2235 x_revtrans_rate_type IN OUT NOCOPY VARCHAR2,
2236 x_revtrans_rate_date IN OUT NOCOPY VARCHAR2,
2237 x_revtrans_exch_rate IN OUT NOCOPY VARCHAR2,
2238 x_revtrans_amount IN OUT NOCOPY VARCHAR2,
2239 x_return_status IN OUT NOCOPY VARCHAR2,
2240 x_msg_count IN OUT NOCOPY NUMBER,
2241 x_msg_data IN OUT NOCOPY VARCHAR2
2242 ) IS
2243
2244
2245 l_btc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2246 l_bill_trans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2247 l_funding_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2248 l_funding_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2249 l_funding_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2250 l_funding_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2251 l_funding_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2252 l_funding_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2253 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2254 l_funding_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2255 l_funding_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2256 l_funding_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2257
2258 l_funding_rate_date DATE;
2259
2260 l_conversion_between VARCHAR2(6);
2261 l_cache_flag VARCHAR2(1);
2262
2263 /* Revenue in foreign currency */
2264 l_mcb_flag VARCHAR2(1);
2265 l_inv_by_btc_flag VARCHAR2(1);
2266
2267 l_pfc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2268 l_projfunc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2269 l_revtrans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2270 l_revtrans_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2274 l_revtrans_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2271 l_revtrans_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2272 l_revtrans_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2273 l_revtrans_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2275 l_revtrans_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2276
2277 /* Added for NOCOPY change */
2278 l_x_funding_rate_type VARCHAR2(30) := x_funding_rate_type;
2279 l_x_funding_rate_date VARCHAR2(10) := x_funding_rate_date;
2280 l_x_funding_exchange_rate VARCHAR2(30) := x_funding_exchange_rate;
2281 l_x_funding_amount VARCHAR2(30) := x_funding_amount;
2282 l_x_funding_convert_status VARCHAR2(30) := x_funding_convert_status;
2283 l_x_revtrans_rate_type VARCHAR2(30) := x_revtrans_rate_type;
2284 l_x_revtrans_rate_date VARCHAR2(10) := x_revtrans_rate_date;
2285 l_x_revtrans_exch_rate VARCHAR2(30) := x_revtrans_exch_rate;
2286
2287 BEGIN
2288
2289
2290 /* This flag is N then the convert_amount_bulk API not cache any currency code,
2291 If the flag is Y then it cache the currency and other attributes for avoid the
2292 repeat processing. */
2293
2294 l_cache_flag := 'N';
2295
2296 IF p_calling_place = 'FC' THEN
2297
2298 IF g1_debug_mode = 'Y' THEN
2299 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || '....Inside the Procedure ERDL conversion');
2300 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || '---------------------------------------');
2301 END IF;
2302
2303
2304
2305 /* Converting the funding rate date Character to DATE format */
2306
2307
2308 /*File.Date.5. Added format to the p_funding_rate_date which was missing*/
2309 l_funding_rate_date := TO_DATE(p_funding_rate_date,'YYYY/MM/DD');
2310
2311
2312 x_return_status := NULL;
2313
2314
2315 /* Copy the Input funding attributed to array variables */
2316
2317 l_btc_amount_tab(1) := p_btc_amount;
2318 l_bill_trans_currency_code_tab(1) := p_btc_code;
2319 l_funding_currency_code_tab(1) := p_funding_curr_code;
2320 l_funding_rev_rate_type_tab(1) := x_funding_rate_type;
2321 l_funding_rev_rate_date_tab(1) := x_funding_rate_date;
2322 l_funding_rev_xchg_rate_tab(1) := x_funding_exchange_rate;
2323 l_funding_amount_tab(1) := NULL;
2324 l_funding_status_tab(1) := 'N';
2325 l_user_validate_flag_tab(1) := 'Y';
2326 l_funding_denominator_tab(1) := NULL;
2327 l_funding_numerator_tab(1) := NULL;
2328 l_funding_bill_rate_tab(1) := NULL;
2329
2330
2331
2332 /* If funding rate date is null then take the funding rate from projects table */
2333
2334 IF l_funding_rev_rate_date_tab(1) IS NULL THEN
2335
2336 l_funding_rev_rate_date_tab(1) := l_funding_rate_date;
2337
2338 END IF;
2339
2340
2341
2342 -- BTC amounts to Funding currency amount conversion
2343
2344 IF g1_debug_mode = 'Y' THEN
2345 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Calling the Funding Amount conversion procedure');
2346 END IF;
2347
2348
2349 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
2350 then the API concatenate the code with error message */
2351
2352 l_conversion_between := 'BTC_FC';
2353
2354
2355 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2356 p_from_currency_tab => l_bill_trans_currency_code_tab,
2357 p_to_currency_tab => l_funding_currency_code_tab,
2358 p_conversion_date_tab => l_funding_rev_rate_date_tab,
2359 p_conversion_type_tab => l_funding_rev_rate_type_tab,
2360 p_amount_tab => l_btc_amount_tab,
2361 p_user_validate_flag_tab => l_user_validate_flag_tab,
2362 p_converted_amount_tab => l_funding_amount_tab,
2363 p_denominator_tab => l_funding_denominator_tab,
2364 p_numerator_tab => l_funding_numerator_tab,
2365 p_rate_tab => l_funding_rev_xchg_rate_tab,
2366 p_conversion_between => l_conversion_between,
2367 p_cache_flag => l_cache_flag,
2368 x_status_tab => l_funding_status_tab
2369 );
2370
2371
2372 /* Copy the converted amount and attributes to OUT variables */
2373
2374
2375 x_funding_rate_type := l_funding_rev_rate_type_tab(1);
2376 x_funding_rate_date := l_funding_rev_rate_date_tab(1);
2377 x_funding_exchange_rate := l_funding_rev_xchg_rate_tab(1);
2378 x_funding_amount := l_funding_amount_tab(1);
2379 x_funding_convert_status := l_funding_status_tab(1);
2380
2381
2382 -- Log Messages for Funding amount conversion in ERDL
2383
2384
2385 IF g1_debug_mode = 'Y' THEN
2386 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Project Id :' || p_project_id);
2387 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Draft revenue Num :' || p_draft_revenue_num);
2391 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding rate type :' || l_funding_rev_rate_type_tab(1));
2388 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Bill Trans Currency Code :' || p_btc_code);
2389 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Bill Trans Amount :' || p_btc_amount);
2390 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding Currency Code :' || p_funding_curr_code);
2392 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding rate date :' || l_funding_rev_rate_date_tab(1));
2393 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding exchange rate :' || l_funding_rev_xchg_rate_tab(1));
2394 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding Amt Rejection Reason :' || l_funding_status_tab(1));
2395 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Convert Funding Amount :' || l_funding_amount_tab(1));
2396 END IF;
2397
2398
2399
2400 /* Marking the draft revenues as error if funding conversion fails */
2401
2402
2403 IF (l_funding_status_tab(1) <> 'N') THEN
2404
2405 UPDATE pa_draft_revenues
2406 SET generation_error_flag = 'Y',
2407 transfer_rejection_reason = l_funding_status_tab(1)
2408 WHERE project_id = p_project_id
2409 AND draft_revenue_num = p_draft_revenue_num;
2410
2411 IF g1_debug_mode = 'Y' THEN
2412 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated as error in Draft Revenue : ' || SQL%ROWCOUNT);
2413 END IF;
2414
2415 END IF;
2416
2417 ELSIF p_calling_place = 'RTC' THEN
2418
2419 /* Revenue in foreign currency - Start*/
2420
2421 x_return_status := NULL;
2422
2423 -- Log Messages
2424
2425 IF g1_debug_mode = 'Y' THEN
2426 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Project Id :' || p_project_id);
2427 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Draft revenue Num :' || p_draft_revenue_num);
2428 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Currency Code :' || p_projfunc_curr_code);
2429 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Amount :' || p_projfunc_amount);
2430 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Currency Code :' || p_revtrans_curr_code);
2431 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Amount :' || x_revtrans_amount);
2432 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate type :' || x_revtrans_rate_type);
2433 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate date :' || x_revtrans_rate_date);
2434 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans exchange rate :' || x_revtrans_exch_rate);
2435 END IF;
2436 /* Copy the Input revenue transactions attributes to array variables */
2437
2438 l_pfc_amount_tab(1) := p_projfunc_amount;
2439 l_projfunc_currency_code_tab(1) := p_projfunc_curr_code;
2440 l_revtrans_currency_code_tab(1) := p_revtrans_curr_code;
2441 l_revtrans_rate_type_tab(1) := x_revtrans_rate_type;
2442 l_revtrans_rate_date_tab(1) := to_date(x_revtrans_rate_date, 'YYYY/MM/DD'); -- For bug 4751461
2443 l_revtrans_xchg_rate_tab(1) := x_revtrans_exch_rate;
2444 l_revtrans_amount_tab(1) := NULL;
2445 l_funding_status_tab(1) := 'N';
2446 l_user_validate_flag_tab(1) := 'Y';
2447 l_revtrans_denominator_tab(1) := NULL;
2448 l_revtrans_numerator_tab(1) := NULL;
2449 l_funding_bill_rate_tab(1) := NULL;
2450
2451
2452 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2453 p_from_currency_tab => l_projfunc_currency_code_tab,
2454 p_to_currency_tab => l_revtrans_currency_code_tab,
2455 p_conversion_date_tab => l_revtrans_rate_date_tab,
2456 p_conversion_type_tab => l_revtrans_rate_type_tab,
2457 p_amount_tab => l_pfc_amount_tab,
2458 p_user_validate_flag_tab => l_user_validate_flag_tab,
2459 p_converted_amount_tab => l_revtrans_amount_tab,
2460 p_denominator_tab => l_revtrans_denominator_tab,
2461 p_numerator_tab => l_revtrans_numerator_tab,
2462 p_rate_tab => l_revtrans_xchg_rate_tab,
2463 p_conversion_between => 'RC_RTC',
2464 p_cache_flag => l_cache_flag,
2465 x_status_tab => l_funding_status_tab
2466 );
2467
2468
2469 /* Copy the converted amount and attributes to OUT variables */
2470
2471 x_revtrans_rate_type := l_revtrans_rate_type_tab(1);
2472 x_revtrans_rate_date := l_revtrans_rate_date_tab(1);
2473 x_revtrans_exch_rate := l_revtrans_xchg_rate_tab(1);
2474 x_revtrans_amount := l_revtrans_amount_tab(1);
2475 x_funding_convert_status := l_funding_status_tab(1);
2476
2477 -- Log Messages
2478
2479 IF g1_debug_mode = 'Y' THEN
2480 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Project Id :' || p_project_id);
2481 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Draft revenue Num :' || p_draft_revenue_num);
2482 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Currency Code :' || p_projfunc_curr_code);
2483 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Amount :' || p_projfunc_amount);
2487 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate date :' || x_revtrans_rate_date);
2484 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Currency Code :' || p_revtrans_curr_code);
2485 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Amount :' || x_revtrans_amount);
2486 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate type :' || x_revtrans_rate_type);
2488 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans exchange rate :' || x_revtrans_exch_rate);
2489 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Amt Rejection Reason :' || l_funding_status_tab(1));
2490 END IF;
2491 IF (l_funding_status_tab(1) <> 'N') THEN
2492
2493 UPDATE pa_draft_revenues
2494 SET generation_error_flag = 'Y',
2495 transfer_rejection_reason = l_funding_status_tab(1)
2496 WHERE project_id = p_project_id
2497 AND draft_revenue_num = p_draft_revenue_num;
2498
2499 IF g1_debug_mode = 'Y' THEN
2500 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated as error in Draft Revenue : ' || SQL%ROWCOUNT);
2501 END IF;
2502
2503 END IF;
2504
2505 END IF;
2506 /* Revenue in foreign currency - End*/
2507
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 IF g1_debug_mode = 'Y' THEN
2511 PA_MCB_INVOICE_PKG.log_message('Error in Erdl_amount_conversion ' || sqlerrm);
2512 END IF;
2513 x_return_status := sqlerrm( sqlcode );
2514 /* Added for NOCOPY change */
2515 x_funding_rate_type := l_x_funding_rate_type;
2516 x_funding_rate_date := l_x_funding_rate_date;
2517 x_funding_exchange_rate := l_x_funding_exchange_rate;
2518 x_funding_amount := l_x_funding_amount;
2519 x_funding_convert_status := l_x_funding_convert_status;
2520 x_revtrans_rate_type := l_x_revtrans_rate_type;
2521 x_revtrans_rate_date := l_x_revtrans_rate_date;
2522 x_revtrans_exch_rate := l_x_revtrans_exch_rate;
2523 END erdl_amount_conversion;
2524
2525
2526 /*----------------------------------------------------------------------------------------+
2527 | Procedure : ei_fcst_amount_conversion |
2528 | Purpose : To update the pa_expenditure_items_all table
2529 | (bill transaction currency to revenue processing currency for |
2530 | forecast revenue
2531 | Parameters : |
2532 | ================================================================================== |
2533 | Name Mode Description |
2534 | ================================================================================== |
2535 | p_project_id IN project Id |
2536 | ei_id IN Expenditure item id
2537 | p_request_id IN Id for the current Run |
2538 | p_pa_date IN Project Accounting date |
2539 | x_return_status IN OUT Return status of this procedure |
2540 | x_msg_count IN OUT Error message count |
2541 | x_msg_data IN OUT Error message |
2542 | ================================================================================== |
2543 +----------------------------------------------------------------------------------------*/
2544
2545 PROCEDURE ei_fcst_amount_conversion(
2546 p_project_id IN NUMBER,
2547 p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2548 p_request_id IN NUMBER,
2549 p_pa_date IN VARCHAR2,
2550 x_return_status IN OUT NOCOPY VARCHAR2,
2551 x_msg_count IN OUT NOCOPY NUMBER,
2552 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2553
2554
2555 CURSOR ei_fcst_amt_csr (p_request_id NUMBER) IS
2556 SELECT project_id, /* 2456371 */
2557 expenditure_item_id,
2558 bill_trans_forecast_revenue,
2559 bill_trans_forecast_curr_code
2560 FROM pa_expenditure_items_all
2561 WHERE request_id = p_request_id
2562 AND revenue_distributed_flag = 'F'
2563 AND bill_trans_forecast_revenue IS NOT NULL
2564 AND forecast_revenue IS NULL
2565 ORDER BY project_id; /* 2456371 */
2566
2567
2568 l_ei_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2569 l_bill_trans_rev_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2570 l_bill_trans_adj_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
2571 l_bill_trans_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2572
2573 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp; /* 2456371 */
2574
2575 l_revproc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2576 l_revproc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2577 l_revproc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2578 l_revproc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2582 l_bill_trans_proj_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
2579 l_revproc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2580
2581
2583 l_project_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2584 l_project_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2585 l_project_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2586 l_project_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2587 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2588 l_project_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2589
2590 l_bill_trans_projfunc_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
2591 l_projfunc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2592 l_projfunc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2593 l_projfunc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2594 l_projfunc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2595 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2596 l_projfunc_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2597
2598 l_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2599 l_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2600 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2601 l_raw_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2602
2603 l_final_error_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2604
2605
2606
2607 l_project_curr_code VARCHAR2(30);
2608 l_project_rate_date_code VARCHAR2(30);
2609 l_project_rate_date DATE;
2610 l_project_rate_type VARCHAR2(30);
2611 l_project_exchange_rate NUMBER;
2612
2613 l_projfunc_curr_code VARCHAR2(30);
2614 l_projfunc_rate_date_code VARCHAR2(30);
2615 l_projfunc_rate_date DATE;
2616 l_projfunc_rate_type VARCHAR2(30);
2617 l_projfunc_exchange_rate NUMBER;
2618
2619 l_multi_currency_billing_flag VARCHAR2(1);
2620 l_baseline_funding_flag VARCHAR2(1);
2621 l_revproc_currency_code VARCHAR2(30);
2622 l_invproc_currency_type VARCHAR2(30);
2623 l_invproc_currency_code VARCHAR2(30);
2624 l_funding_rate_date_code VARCHAR2(30);
2625 l_funding_rate_type VARCHAR2(30);
2626 l_funding_rate_date DATE;
2627 l_funding_exchange_rate NUMBER;
2628 l_return_status VARCHAR2(1);
2629 l_msg_count NUMBER;
2630 l_msg_data VARCHAR2(240);
2631
2632 l_pa_date DATE;
2633
2634 l_conversion_between VARCHAR2(6);
2635 l_cache_flag VARCHAR2(1);
2636
2637 l_project_id NUMBER ; /* 2456371 */
2638 l_prv_project_id NUMBER ;
2639
2640
2641 BEGIN
2642
2643
2644 /* Assign the dummy value into the previous project id - This for checking
2645 whenever project id changes then call the get_project_defaults API - 2456371 */
2646
2647
2648 l_prv_project_id := -9999;
2649
2650
2651
2652 /* This flag is N then the convert_amount_bulk API not cache any currency code,
2653 If the flag is Y then it cache the currency and other attributes for avoid the
2654 repeat processing. */
2655
2656 l_cache_flag := 'N';
2657
2658
2659 /* Convert the PA date from character to date */
2660
2661
2662 /*File.Date.5. Added format to the p_pa_date which was missing*/
2663 l_pa_date := TO_DATE(p_pa_date,'YYYY/MM/DD');
2664
2665
2666 IF g1_debug_mode = 'Y' THEN
2667 PA_MCB_INVOICE_PKG.log_message('Entering the procedure ei_fcst_amount_conversion');
2668 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || '------------------------------------------------');
2669 END IF;
2670
2671
2672
2673 /* 2456371 - cursor for select the expenditure details based on current request id */
2674
2675
2676 OPEN ei_fcst_amt_csr( p_request_id);
2677
2678 LOOP
2679
2680
2681
2682
2683 /*
2684 * Clear all PL/SQL table.
2685 */
2686
2687 l_ei_id_tab.delete;
2688 l_bill_trans_rev_amount_tab.delete;
2689 l_bill_trans_curr_code_tab.delete;
2690
2691 l_project_id_tab.delete; /* 2456371 */
2692
2693 l_revproc_rate_type_tab.delete;
2694 l_revproc_rate_date_tab.delete;
2695 l_revproc_exchange_rate_tab.delete;
2696 l_revproc_curr_code_tab.delete;
2697 l_revproc_amount_tab.delete;
2698
2699 l_project_curr_code_tab.delete;
2700 l_project_rate_date_tab.delete;
2701 l_project_rate_type_tab.delete;
2702 l_project_exchange_rate_tab.delete;
2703 l_project_amount_tab.delete;
2704 l_project_rev_status_tab.delete;
2705
2706
2707 l_projfunc_curr_code_tab.delete;
2708 l_projfunc_rate_date_tab.delete;
2712 l_projfunc_rev_status_tab.delete;
2709 l_projfunc_rate_type_tab.delete;
2710 l_projfunc_exchange_rate_tab.delete;
2711 l_projfunc_amount_tab.delete;
2713
2714
2715 l_user_validate_flag_tab.delete;
2716 l_denominator_tab.delete;
2717 l_numerator_tab.delete;
2718 l_raw_rev_status_tab.delete;
2719
2720
2721 /* Fetching the expenditure bill transaction value */
2722
2723
2724 FETCH ei_fcst_amt_csr BULK COLLECT
2725 INTO l_project_id_tab, /* 2456371 */
2726 l_ei_id_tab,
2727 l_bill_trans_rev_amount_tab,
2728 l_bill_trans_curr_code_tab LIMIT 100;
2729
2730
2731 IF g1_debug_mode = 'Y' THEN
2732 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Record Count :' || l_ei_id_tab.COUNT);
2733 END IF;
2734
2735 IF (l_ei_id_tab.COUNT = 0 ) THEN
2736
2737 EXIT;
2738
2739 ELSE
2740
2741
2742 /* Initialize the Array variables to use convert_amount_bulk API */
2743
2744
2745 FOR I in 1 .. l_ei_id_tab.COUNT
2746 LOOP
2747
2748 l_user_validate_flag_tab(I) := 'Y';
2749 l_revproc_amount_tab(I) := NULL;
2750 l_denominator_tab(I) := NULL;
2751 l_numerator_tab(I) := NULL;
2752 l_raw_rev_status_tab(I) := NULL;
2753
2754
2755
2756 /* Assign the project Id from PL/SQL table into non array variable for
2757 pass into the API get_project_defaults - 2456371 */
2758
2759 l_project_id := l_project_id_tab(i);
2760
2761
2762 /* 2456371 - Checking for the Previous and current project Id - If bott are different then
2763 call the api to get the default conversion attributes - If same then not necessary to call
2764 the API to get the conversion attributes */
2765
2766
2767 IF (l_project_id <> l_prv_project_id) THEN
2768
2769
2770
2771 IF g1_debug_mode = 'Y' THEN
2772 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Project Id :' || l_project_id);
2773 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Calling Procedure get_project_defaults');
2774 END IF;
2775
2776 -- Get the Project Level Defaults
2777
2778 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
2779 p_project_id => l_project_id,
2780 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
2781 x_baseline_funding_flag => l_baseline_funding_flag,
2782 x_revproc_currency_code => l_revproc_currency_code,
2783 x_invproc_currency_type => l_invproc_currency_type,
2784 x_invproc_currency_code => l_invproc_currency_code,
2785 x_project_currency_code => l_project_curr_code,
2786 x_project_bil_rate_date_code => l_project_rate_date_code,
2787 x_project_bil_rate_type => l_project_rate_type,
2788 x_project_bil_rate_date => l_project_rate_date,
2789 x_project_bil_exchange_rate => l_project_exchange_rate,
2790 x_projfunc_currency_code => l_projfunc_curr_code,
2791 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
2792 x_projfunc_bil_rate_type => l_projfunc_rate_type,
2793 x_projfunc_bil_rate_date => l_projfunc_rate_date,
2794 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
2795 x_funding_rate_date_code => l_funding_rate_date_code,
2796 x_funding_rate_type => l_funding_rate_type,
2797 x_funding_rate_date => l_funding_rate_date,
2798 x_funding_exchange_rate => l_funding_exchange_rate,
2799 x_return_status => l_return_status,
2800 x_msg_count => l_msg_count,
2801 x_msg_data => l_msg_data);
2802
2803
2804 END IF;
2805
2806
2807 IF g1_debug_mode = 'Y' THEN
2808 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'after calling Procedure get_project_defaults');
2809 END IF;
2810
2811
2812 /* Copy the project and project attributed into array variables */
2813
2814
2815 l_project_curr_code_tab(I) := l_project_curr_code;
2816 l_project_rate_type_tab(I) := l_project_rate_type;
2817 l_project_rate_date_tab(I) := l_project_rate_date;
2818 l_project_exchange_rate_tab(I) := l_project_exchange_rate;
2819
2820 l_projfunc_curr_code_tab(I) := l_projfunc_curr_code;
2821 l_projfunc_rate_type_tab(I) := l_projfunc_rate_type;
2822 l_projfunc_rate_date_tab(I) := l_projfunc_rate_date;
2823 l_projfunc_exchange_rate_tab(I) := l_projfunc_exchange_rate;
2824
2825
2826 /* If revenue processing currency and project currency both are same then
2827 copy the project attributes to revenue processing attributes */
2828
2829
2830 IF (l_revproc_currency_code = l_project_curr_code) THEN
2831
2832
2833 l_revproc_curr_code_tab(I) := l_project_curr_code;
2834 l_revproc_rate_type_tab(I) := l_project_rate_type;
2838
2835 l_revproc_rate_date_tab(I) := l_project_rate_date;
2836 l_revproc_exchange_rate_tab(I) := l_project_exchange_rate;
2837
2839 /* If rate date code = 'PA_INVOICE_DATE' then get the PA DATE and
2840 assign to the revenue processing rate date */
2841
2842 IF (l_project_rate_date_code = 'PA_INVOICE_DATE') THEN
2843
2844 l_revproc_rate_date_tab(I) := l_pa_date;
2845
2846 END IF;
2847
2848 /* If revenue processing currency and project functional currency both are same then
2849 copy the project functional attributes to revenue processing attributes */
2850
2851
2852 ELSIF (l_revproc_currency_code = l_projfunc_curr_code) THEN
2853
2854 l_revproc_curr_code_tab(I) := l_projfunc_curr_code;
2855 l_revproc_rate_type_tab(I) := l_projfunc_rate_type;
2856 l_revproc_rate_date_tab(I) := l_projfunc_rate_date;
2857 l_revproc_exchange_rate_tab(I) := l_projfunc_exchange_rate;
2858
2859 /* If rate date code = 'PA_INVOICE_DATE' then get the PA DATE and
2860 assign to the revenue processing rate date */
2861
2862
2863 IF (l_projfunc_rate_date_code = 'PA_INVOICE_DATE') THEN
2864
2865 l_revproc_rate_date_tab(I) := l_pa_date;
2866
2867 END IF;
2868
2869
2870 END IF;
2871
2872
2873
2874 /* 2456371 : Assign the project id to the previous project Id variable */
2875
2876 l_prv_project_id := l_project_id;
2877
2878
2879 /* Print the currency attribute value */
2880
2881 IF g1_debug_mode = 'Y' THEN
2882 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
2883 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Rate Type :' || l_revproc_rate_type_tab(i));
2884 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'RevProc Rate Date :' || l_revproc_rate_date_tab(i));
2885 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Xchg Rate :' || l_revproc_exchange_rate_tab(i));
2886 END IF;
2887
2888
2889 END LOOP;
2890
2891
2892
2893 /* Converting Bill Trans Raw revenue to Raw revenue (Revenue processing currency) */
2894
2895 IF g1_debug_mode = 'Y' THEN
2896 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Calling the procedure convert_amount_bulk for Revenue amount');
2897 END IF;
2898
2899
2900 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
2901 then the API concatenate the code with error message */
2902
2903 l_conversion_between := 'BTC_PF';
2904
2905
2906 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2907 p_from_currency_tab => l_bill_trans_curr_code_tab,
2908 p_to_currency_tab => l_revproc_curr_code_tab,
2909 p_conversion_date_tab => l_revproc_rate_date_tab,
2910 p_conversion_type_tab => l_revproc_rate_type_tab,
2911 p_amount_tab => l_bill_trans_rev_amount_tab,
2912 p_user_validate_flag_tab => l_user_validate_flag_tab,
2913 p_converted_amount_tab => l_revproc_amount_tab,
2914 p_denominator_tab => l_denominator_tab,
2915 p_numerator_tab => l_numerator_tab,
2916 p_rate_tab => l_revproc_exchange_rate_tab,
2917 p_conversion_between => l_conversion_between,
2918 p_cache_flag => l_cache_flag,
2919 x_status_tab => l_raw_rev_status_tab
2920 );
2921
2922
2923
2924 IF g1_debug_mode = 'Y' THEN
2925 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Project Id : ' || p_project_id);
2926 END IF;
2927
2928
2929 /* FOR I in 1 .. l_ei_id_tab.COUNT
2930 LOOP
2931
2932
2933
2934 -- Log Messages for EI Converted Amounts
2935
2936 IF g1_debug_mode = 'Y' THEN
2937 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
2938 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
2939 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Bill Trans Raw revenue :' || l_bill_trans_rev_amount_tab(i));
2940 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Convert RevProc Amount :' || l_revproc_amount_tab(i));
2941 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
2942 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Amt Rejection Reason :' || l_raw_rev_status_tab(i));
2943 END IF;
2944
2945
2946
2947 END LOOP; */
2948
2949
2950
2951 /* Updating the converted amount to the Expenditure Item table.
2952 Converted amount column : raw_revenue, adjusted_revenue, bill_rate, adjusted_rate
2953 Other columns : Initialize when conversion fails and marking revenue
2954 distributed flag to 'N */
2955
2956
2957 FORALL I IN 1 ..l_ei_id_tab.COUNT
2958 UPDATE pa_expenditure_items_all
2959 SET forecast_revenue =
2960 DECODE(l_raw_rev_status_tab(i), 'N', l_revproc_amount_tab(i), NULL),
2961 projfunc_fcst_rate_type = l_revproc_rate_type_tab(i),
2962 projfunc_fcst_rate_date = l_revproc_rate_date_tab(i),
2963 projfunc_fcst_exchange_rate = l_revproc_exchange_rate_tab(i),
2964 rev_dist_rejection_code =
2965 DECODE(l_raw_rev_status_tab(i), 'N',NULL, l_raw_rev_status_tab(i))
2966 WHERE expenditure_item_id = l_ei_id_tab(i);
2967
2968 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in EI table for forecast revenue: ' || SQL%ROWCOUNT);
2969
2970
2971 END IF; /* l_ei_id_tab.COUNT <> 0 */
2972
2973 EXIT WHEN ei_fcst_amt_csr%NOTFOUND;
2974
2975
2976 END LOOP;
2977
2978 CLOSE ei_fcst_amt_csr;
2979
2980
2981 EXCEPTION
2982 WHEN OTHERS THEN
2983
2984 IF g1_debug_mode = 'Y' THEN
2985 PA_MCB_INVOICE_PKG.log_message('Error in Ei_fcst_amount_conversion ' || sqlerrm);
2986 END IF;
2987
2988 x_return_status := sqlerrm( sqlcode );
2989
2990 END ei_fcst_amount_conversion;
2991
2992
2993
2994 PROCEDURE log_message (p_log_msg IN VARCHAR2) IS
2995 BEGIN
2996 --pa_debug.write_file ('LOG',to_char(sysdate, 'DD-MON-YYYY HH:MI:SS ')||p_log_msg);
2997 pa_debug.write_file ('LOG','MCB.....' || p_log_msg);
2998 NULL;
2999 END log_message;
3000
3001
3002 PROCEDURE Init (P_DEBUG_MODE VARCHAR2) IS
3003 BEGIN
3004 G_LAST_UPDATE_LOGIN := fnd_global.login_id;
3005 G_REQUEST_ID := fnd_global.conc_request_id;
3006 G_PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
3007 G_PROGRAM_ID := fnd_global.conc_program_id;
3008 G_LAST_UPDATED_BY := fnd_global.user_id;
3009 G_CREATED_BY := fnd_global.user_id;
3010 G_DEBUG_MODE := P_DEBUG_MODE;
3011 pa_debug.init_err_stack ('Revenue Generation');
3012 pa_debug.set_process(
3013 x_process => 'PLSQL',
3014 x_debug_mode => G_DEBUG_MODE);
3015
3016
3017 pa_debug.G_Err_Stage :=' Start PLSQL Message ';
3018
3019 IF g1_debug_mode = 'Y' THEN
3020 PA_MCB_REVENUE_PKG.log_message(pa_debug.G_Err_Stage);
3021 END IF;
3022
3023 END Init;
3024
3025
3026 /*----------------------------------------------------------------------------------------+
3027 | Procedure : RTC_UBR_UER_CALC |
3028 | Purpose : To compute transaction level ie, draft revenue level UBR/UER values in |
3029 | Revenue transaction currency. |
3030 | Parameters : |
3031 | ================================================================================== |
3032 | Name Mode Description |
3033 | ================================================================================== |
3034 | P_PFC_REV_AMOUNT IN Total revenue amount for a revenue in PFC |
3035 | P_REVTRANS_AMOUNT IN Total revenue amount for a revenue in RTC |
3036 | P_PROJFUNC_UBR IN UBR amount in project functional currency |
3037 | P_PROJFUNC_UER IN UBR amount in project functional currency |
3038 | P_UBR_CORR IN UBR correction amt in proj functional currency|
3039 | P_UER_CORR IN UER correction amt in proj functional currency|
3040 | P_REVTRANS_UBR OUT NOCOPY UBR amount in revenue transaction currency |
3041 | P_REVTRANS_UER OUT NOCOPY UER amount in revenue transaction currency |
3042 | X_RETURN_STATUS OUT NOCOPY Return status |
3043 | X_MSG_COUNT OUT NOCOPY Error messages count |
3044 | X_MSG_DATA OUT NOCOPY Error message |
3045 | ================================================================================== |
3046 +----------------------------------------------------------------------------------------*/
3047 PROCEDURE RTC_UBR_UER_CALC(
3048 P_PFC_REV_AMOUNT IN NUMBER,
3049 P_REVTRANS_AMOUNT IN NUMBER,
3050 P_PROJFUNC_UBR IN NUMBER,
3051 P_PROJFUNC_UER IN NUMBER,
3052 P_UBR_CORR IN NUMBER,
3053 P_UER_CORR IN NUMBER,
3054 P_REVTRANS_UBR OUT NOCOPY VARCHAR,
3055 P_REVTRANS_UER OUT NOCOPY VARCHAR,
3056 X_RETURN_STATUS OUT NOCOPY VARCHAR,
3057 X_MSG_COUNT OUT NOCOPY NUMBER,
3058 X_MSG_DATA OUT NOCOPY VARCHAR)
3059 IS
3060
3061 l_rtc_ubr_corr VARCHAR2(30);
3062 l_rtc_uer_corr VARCHAR2(30);
3063
3064 BEGIN
3065 X_RETURN_STATUS := NULL;
3066 X_MSG_COUNT := 0;
3067 X_MSG_DATA := NULL;
3068
3069 -- Compute ubr,uer correction amount in revenue transaction currency from project functional amounts.
3070
3071 l_rtc_ubr_corr := substr(to_char((P_REVTRANS_AMOUNT / P_PFC_REV_AMOUNT ) * P_UBR_CORR), 1, 30);
3072 l_rtc_uer_corr := substr(to_char((P_REVTRANS_AMOUNT / P_PFC_REV_AMOUNT ) * P_UER_CORR), 1, 30);
3073
3074
3075 -- Calculate UBR in revenue transaction currency as projfunc UBR multiplied by the ratio of revenue in
3076 -- project functional currency and revenue in revenue transaction currency.
3077
3078 P_Revtrans_Ubr := substr(to_char(((P_RevTrans_Amount / P_Pfc_Rev_Amount ) * P_Projfunc_Ubr) + to_number(l_rtc_ubr_corr)), 1, 30);
3079
3080 -- Calculate UER in revenue transaction currency as projfunc UER multiplied by the ratio of revenue in
3081 -- project functional currency and revenue in revenue transaction currency.
3082
3083 P_Revtrans_Uer := substr(to_char(((P_RevTrans_Amount / P_Pfc_Rev_Amount ) * P_Projfunc_Uer) + to_number(l_rtc_uer_corr)), 1, 30);
3084
3085
3086 EXCEPTION
3087 WHEN ZERO_DIVIDE THEN
3088 P_REVTRANS_UBR := 0;
3089 P_REVTRANS_UER := 0;
3090
3091 WHEN OTHERS THEN
3092 P_REVTRANS_UBR := NULL;
3093 P_REVTRANS_UER := NULL;
3094 raise;
3095 END RTC_UBR_UER_CALC;
3096
3097
3098 END PA_MCB_REVENUE_PKG;