[Home] [Help]
PACKAGE BODY: APPS.PA_MCB_REVENUE_PKG
Source
1 PACKAGE BODY PA_MCB_REVENUE_PKG AS
2 --$Header: PAXMCRUB.pls 120.9.12020000.3 2013/03/22 10:05:53 rvadali 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
365
366 END LOOP;
367
368
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);
476 l_revproc_amount_tab(I) := l_projfunc_amount_tab(i);
477
478
479 /* If revenue processing currency and project currency both are same then
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),
570 project_rate_type)), project_rate_type),
571 project_rev_rate_date =
572 DECODE(l_project_status_tab(i), 'N',
573 --Modified for Bug3087885
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
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 |
667 | x_msg_count IN OUT Error message count |
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 /*Commented for bug 9767275 */
683 /*CURSOR ei_amt_csr (p_request_id NUMBER) IS
684 SELECT expenditure_item_id,
685 expenditure_item_date, /* Added for bug 5919299*
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 l_raw_revenue_amount_tab PA_PLSQL_DATATYPES.NumTabTyp; /*Added for bug 9767275*/
696 l_revenue_distributed_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp; /*Added for bug 9767275*/
697 l_ei_date_tab PA_PLSQL_DATATYPES.DateTabTyp; /* Added for bug 5919299*/
698 l_ei_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
699 l_bill_trans_rev_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
700 l_bill_trans_adj_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
701 l_bill_trans_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
702 l_bill_trans_adj_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
703 l_bill_trans_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
704
705 l_revproc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
706 l_revproc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
707 l_revproc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
708 l_revproc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
709 l_revproc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
710
711
712 l_bill_trans_proj_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
713 l_project_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
714 l_project_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
715 l_project_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
716 l_project_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
717 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
718 l_project_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
719
720 l_bill_trans_projfunc_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
721 l_projfunc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
722 l_projfunc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
723 l_projfunc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
724 l_projfunc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
725 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
726 l_projfunc_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
727
728 l_revproc_adj_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
729 l_revproc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
730 l_revproc_adj_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
731
732 l_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
733 l_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
734 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
735 l_raw_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
736
737 l_final_error_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
738
739
740
741 l_project_curr_code VARCHAR2(30);
742 l_project_rate_date_code VARCHAR2(30);
743 l_project_rate_date DATE;
744 l_project_rate_type VARCHAR2(30);
745 l_project_exchange_rate NUMBER;
746
747 l_projfunc_curr_code VARCHAR2(30);
748 l_projfunc_rate_date_code VARCHAR2(30);
749 l_projfunc_rate_date DATE;
750 l_projfunc_rate_type VARCHAR2(30);
751 l_projfunc_exchange_rate NUMBER;
752
753 l_multi_currency_billing_flag VARCHAR2(1);
754 l_baseline_funding_flag VARCHAR2(1);
755 l_revproc_currency_code VARCHAR2(30);
756 l_invproc_currency_type VARCHAR2(30);
757 l_invproc_currency_code VARCHAR2(30);
758 l_funding_rate_date_code VARCHAR2(30);
759 l_funding_rate_type VARCHAR2(30);
760 l_funding_rate_date DATE;
761 l_funding_exchange_rate NUMBER;
762 l_return_status VARCHAR2(1);
763 l_msg_count NUMBER;
764 l_msg_data VARCHAR2(240);
765
766 l_pa_date DATE;
767
768 l_conversion_between VARCHAR2(6);
769 l_cache_flag VARCHAR2(1);
770
771 l_currency_flag VARCHAR2(1):= 'N';
772 /* Variable declaration for bug 5907315 */
773
774 l_previous_project_rate_date DATE;
775 l_previous_projfunc_rate_date DATE;
776 j NUMBER; /*loop varilable for bug 9767275*/
777
778 /* End of variable declaration: Bug 5907315 */
779
780
781 BEGIN
782
783
784 /* This flag is N then the convert_amount_bulk API not cache the currency code and attributes,
785 If the flag is Y then cache the currency and other attributes to avoid the
786 repeat conversion processing. */
787
788 l_cache_flag := 'Y';
789
790
791 x_rej_reason := NULL;
792
793
794 /* Convert the PA date from character to date */
795
796 /*File.Date.5. Added format to the p_pa_date which was missing*/
797 l_pa_date := TO_DATE(p_pa_date,'YYYY/MM/DD');
798
799
800 IF g1_debug_mode = 'Y' THEN
801 PA_MCB_INVOICE_PKG.log_message('Entering the procedure ei_amount_conversion');
802 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || '-------------------------------------------');
803 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Calling Procedure get_project_defaults');
804 END IF;
805
806
807 -- Get the Project Level Defaults
808
809 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
810 p_project_id => p_project_id,
811 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
812 x_baseline_funding_flag => l_baseline_funding_flag,
813 x_revproc_currency_code => l_revproc_currency_code,
814 x_invproc_currency_type => l_invproc_currency_type,
815 x_invproc_currency_code => l_invproc_currency_code,
816 x_project_currency_code => l_project_curr_code,
817 x_project_bil_rate_date_code => l_project_rate_date_code,
818 x_project_bil_rate_type => l_project_rate_type,
819 x_project_bil_rate_date => l_project_rate_date,
820 x_project_bil_exchange_rate => l_project_exchange_rate,
821 x_projfunc_currency_code => l_projfunc_curr_code,
822 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
823 x_projfunc_bil_rate_type => l_projfunc_rate_type,
824 x_projfunc_bil_rate_date => l_projfunc_rate_date,
825 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
826 x_funding_rate_date_code => l_funding_rate_date_code,
827 x_funding_rate_type => l_funding_rate_type,
828 x_funding_rate_date => l_funding_rate_date,
829 x_funding_exchange_rate => l_funding_exchange_rate,
830 x_return_status => l_return_status,
831 x_msg_count => l_msg_count,
832 x_msg_data => l_msg_data);
833
834
835
836 /* Checking for Project Functional and Project currencies are same, If both are same then
837 Convert only Project Functional and copy the Project Functional attributes to
838 project
839 Here we are setting flag for Whether we need to convert Project amount or not
840 */
841
842
843 l_currency_flag := 'N';
844
845 IF (l_projfunc_curr_code = l_project_curr_code) THEN
846
847 l_currency_flag := 'Y';
848
849 END IF;
850
851 /* Code added for bug 5907315 */
852 fnd_profile_revenue_orig_rate := NVL(fnd_profile.value_specific('PA_REVENUE_ORIGINAL_RATE_FORRECALC'),'N');
853 /* End of bug 5907315 */
854
855
856 /* cursor for select the expenditure details based on current request id */
857
858
859 /*OPEN ei_amt_csr( p_request_id); */ /*Commented for bug 9767275*/
860
861
862
863 /*LOOP*/ /*Commented for bug 9767275*/
864
865
866 /*
867 * Clear all PL/SQL table.
868 */
869
870 l_ei_id_tab.delete;
871 l_ei_date_tab.delete; /* Added for bug 5907315*/
872 l_raw_revenue_amount_tab.delete; /*Added for bug 9767275*/
873 l_revenue_distributed_flag_tab.delete; /*Added for bug 9767275*/
874 l_bill_trans_rev_amount_tab.delete;
875 l_bill_trans_bill_rate_tab.delete;
876 l_bill_trans_curr_code_tab.delete;
877 l_bill_trans_adj_rev_tab.delete;
878 l_bill_trans_adj_rate_tab.delete;
879
880 l_revproc_rate_type_tab.delete;
881 l_revproc_rate_date_tab.delete;
882 l_revproc_exchange_rate_tab.delete;
883 l_revproc_curr_code_tab.delete;
884 l_revproc_amount_tab.delete;
885
886 l_project_curr_code_tab.delete;
887 l_project_rate_date_tab.delete;
888 l_project_rate_type_tab.delete;
889 l_project_exchange_rate_tab.delete;
890 l_project_amount_tab.delete;
891 l_project_rev_status_tab.delete;
892
893 l_projfunc_curr_code_tab.delete;
894 l_projfunc_rate_date_tab.delete;
895 l_projfunc_rate_type_tab.delete;
896 l_projfunc_exchange_rate_tab.delete;
897 l_projfunc_amount_tab.delete;
898 l_projfunc_rev_status_tab.delete;
899
900 l_revproc_adj_rev_tab.delete;
901 l_revproc_bill_rate_tab.delete;
902 l_revproc_adj_rate_tab.delete;
903
904 l_user_validate_flag_tab.delete;
905 l_denominator_tab.delete;
906 l_numerator_tab.delete;
907 l_raw_rev_status_tab.delete;
908
909 l_final_error_status_tab.delete;
910
911
912 /* Fetching the expenditure bill transaction value */
913
914 /*Added for bug 9767275*/
915 /*FETCH ei_amt_csr BULK COLLECT
916 INTO l_ei_id_tab,
917 l_ei_date_tab,
918 l_bill_trans_rev_amount_tab,
919 l_bill_trans_adj_rev_tab,
920 l_bill_trans_curr_code_tab LIMIT 100;*/
921 /* Added l_ei_date_tab for bug 5907315*/
922
923 /* If any records select in the fetch then go for conversion */
924
925 /*Added the following for bug 9767275*/
926
927 BEGIN
928 j := 1;
929 FOR I in 1 .. p_ei_id.COUNT
930 LOOP
931 BEGIN
932 SELECT expenditure_item_date,
933 bill_trans_raw_revenue,
934 bill_trans_adjusted_revenue,
935 bill_trans_currency_code,
936 revenue_distributed_flag,
937 raw_revenue
938 INTO l_ei_date_tab(j),
939 l_bill_trans_rev_amount_tab(j),
940 l_bill_trans_adj_rev_tab(j),
941 l_bill_trans_curr_code_tab(j),
942 l_revenue_distributed_flag_tab(j),
943 l_raw_revenue_amount_tab(j)
944 FROM pa_expenditure_items_all
945 WHERE expenditure_item_id = p_ei_id(I)
946 AND revenue_distributed_flag = 'D'
947 AND bill_trans_raw_revenue IS NOT NULL
948 AND raw_revenue IS NULL;
949
950 l_ei_id_tab(j) := p_ei_id(I);
951
952 j := j+1;
953
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 NULL;
957 END;
958
959 END LOOP;
960
961 j:=j-1;
962
963 END;
964
965 /*IF (l_ei_id_tab.COUNT = 0) THEN
966
967 Exit;
968
969
970 ELSE Commented for bug 9767275*/ /* l_ei_id_tab.COUNT <> 0) */
971
972
973 /* Initialize the Array variables to use convert_amount_bulk API */
974
975 FOR I in 1 .. j /*l_ei_id_tab.COUNT Commented for bug 9767275*/
976 LOOP /*Added following if condition for bug 9767275*/
977 l_revproc_amount_tab(I) := NULL;
978 l_user_validate_flag_tab(I) := 'Y';
979 l_project_amount_tab(i) := NULL;
980 l_projfunc_amount_tab(i) := NULL;
981 l_revproc_amount_tab(I) := NULL;
982 l_revproc_adj_rev_tab(I) := NULL;
983 l_revproc_bill_rate_tab(I) := NULL;
984 l_revproc_adj_rate_tab(I) := NULL;
985 l_denominator_tab(I) := NULL;
986 l_numerator_tab(I) := NULL;
987 l_raw_rev_status_tab(i) := 'N';
988 l_project_rev_status_tab(i) := 'N';
989 l_projfunc_rev_status_tab(i) := 'N';
990
991
992 /* Copy the project and project attributed into array variables */
993
994
995 l_project_curr_code_tab(I) := l_project_curr_code;
996 l_project_rate_type_tab(I) := l_project_rate_type;
997 l_project_rate_date_tab(I) := l_project_rate_date;
998 l_project_exchange_rate_tab(I) := l_project_exchange_rate;
999
1000 l_projfunc_curr_code_tab(I) := l_projfunc_curr_code;
1001 l_projfunc_rate_type_tab(I) := l_projfunc_rate_type;
1002 l_projfunc_rate_date_tab(I) := l_projfunc_rate_date;
1003 l_projfunc_exchange_rate_tab(I) := l_projfunc_exchange_rate;
1004
1005
1006 /* If Bill transaction adjusted revenue is NOT NULL then take the bill trans
1007 adjsuted revenue otherwise take the bill transaction raw revenue revenue */
1008
1009 l_bill_trans_proj_amt_tab(I)
1010 := NVL(l_bill_trans_adj_rev_tab(I), l_bill_trans_rev_amount_tab(I));
1011
1012 /* Copy the project amount to project functional amount */
1013
1014
1015 l_bill_trans_projfunc_amt_tab(I)
1016 := l_bill_trans_proj_amt_tab(I);
1017
1018 /* Code added for bug 5907315 */
1019
1020 l_previous_project_rate_date := NULL;
1021 l_previous_projfunc_rate_date:= NULL;
1022
1023 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1024
1025 begin
1026 l_previous_project_rate_date := NULL;
1027 l_previous_projfunc_rate_date:= NULL;
1028 l_previous_project_rate_date := pa_date(l_ei_date_tab(i));
1029 l_previous_projfunc_rate_date:= pa_date(l_ei_date_tab(i));
1030
1031 EXCEPTION
1032 when OTHERS then
1033 l_previous_project_rate_date := NULL;
1034 l_previous_projfunc_rate_date:= NULL;
1035 end;
1036
1037 END IF;
1038
1039 /* End of code. Bug 5907315 */
1040
1041
1042 /* Copy the PA date to project rate date */
1043
1044 IF (l_project_rate_date_code = 'PA_INVOICE_DATE') THEN
1045 /* Code commented for bug 5907315
1046 l_project_rate_date_tab(I) := l_pa_date;
1047 The statement is modified as below */
1048 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1049 l_project_rate_date_tab(I) := NVL(l_previous_project_rate_date,l_pa_date);
1050 ELSE
1051 l_project_rate_date_tab(I) := l_pa_date;
1052 END IF;
1053 /* End of bug 5907315 */
1054
1055 END IF;
1056
1057
1058 /* Copy the PA date to project functional rate date */
1059
1060 IF (l_projfunc_rate_date_code = 'PA_INVOICE_DATE') THEN
1061 /* Code commented for bug 5907315
1062 l_projfunc_rate_date_tab(I) := l_pa_date;
1063 The statement is modified as below */
1064 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1065 l_projfunc_rate_date_tab(I) := NVL(l_previous_projfunc_rate_date,l_pa_date);
1066 ELSE
1067 l_projfunc_rate_date_tab(I) := l_pa_date;
1068 END IF;
1069 /* End of bug 5907315 */
1070 END IF;
1071
1072
1073 /* Print the currency attribute value */
1074
1075 IF g1_debug_mode = 'Y' THEN
1076 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
1077 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Rev amount :' || l_bill_trans_rev_amount_tab(i));
1078 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans projfun amount :' || l_bill_trans_projfunc_amt_tab(i));
1079 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Currency Code :' || l_projfunc_curr_code_tab(i));
1080 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Type :' || l_projfunc_rate_type_tab(i));
1081 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Date :' || l_projfunc_rate_date_tab(i));
1082 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Xchg Rate :' || l_projfunc_exchange_rate_tab(i));
1083 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Currency Code :' || l_project_curr_code_tab(i));
1084 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Type :' || l_project_rate_type_tab(i));
1085 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Date :' || l_project_rate_date_tab(i));
1086 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Xchg Rate :' || l_project_exchange_rate_tab(i));
1087 END IF;
1088
1089
1090 END LOOP;
1091
1092
1093
1094 /* Converting Bill Trans Raw revenue to Project Functional Amount(Project Func Amount) */
1095
1096
1097 IF g1_debug_mode = 'Y' THEN
1098 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Calling the procedure convert_amount_bulk for project func amount');
1099 END IF;
1100
1101 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
1102 then the API concatenate the code with error message */
1103
1104 l_conversion_between := 'BTC_PF';
1105
1106
1107 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1108 p_from_currency_tab => l_bill_trans_curr_code_tab,
1109 p_to_currency_tab => l_projfunc_curr_code_tab,
1110 p_conversion_date_tab => l_projfunc_rate_date_tab,
1111 p_conversion_type_tab => l_projfunc_rate_type_tab,
1112 p_amount_tab => l_bill_trans_projfunc_amt_tab,
1113 p_user_validate_flag_tab => l_user_validate_flag_tab,
1114 p_converted_amount_tab => l_projfunc_amount_tab,
1115 p_denominator_tab => l_denominator_tab,
1116 p_numerator_tab => l_numerator_tab,
1117 p_rate_tab => l_projfunc_exchange_rate_tab,
1118 p_conversion_between => l_conversion_between,
1119 p_cache_flag => l_cache_flag,
1120 x_status_tab => l_projfunc_rev_status_tab
1121 );
1122
1123
1124 l_denominator_tab.delete;
1125 l_numerator_tab.delete;
1126
1127
1128 /* Converting Bill Trans Raw revenue to Project Amount(Project Currency) */
1129
1130
1131 IF g1_debug_mode = 'Y' THEN
1132 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Calling the procedure convert_amount_bulk for convert project amount');
1133 END IF;
1134
1135
1136 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
1137 then the API concatenate the code with error message */
1138
1139
1140 IF (l_currency_flag <> 'Y') THEN
1141
1142 l_conversion_between := 'BTC_PC';
1143
1144 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1145 p_from_currency_tab => l_bill_trans_curr_code_tab,
1146 p_to_currency_tab => l_project_curr_code_tab,
1147 p_conversion_date_tab => l_project_rate_date_tab,
1148 p_conversion_type_tab => l_project_rate_type_tab,
1149 p_amount_tab => l_bill_trans_proj_amt_tab,
1150 p_user_validate_flag_tab => l_user_validate_flag_tab,
1151 p_converted_amount_tab => l_project_amount_tab,
1152 p_denominator_tab => l_denominator_tab,
1153 p_numerator_tab => l_numerator_tab,
1154 p_rate_tab => l_project_exchange_rate_tab,
1155 p_conversion_between => l_conversion_between,
1156 p_cache_flag => l_cache_flag,
1157 x_status_tab => l_project_rev_status_tab
1158 );
1159
1160 END IF;
1161
1162
1163 /* Converting Bill trans adjusted revenue to Adjusted revenue
1164 Here not necessary call the conver_amount_bulk API
1165 we have to calculate the adjusted revenue based on rate, which
1166 we got it from previos API */
1167
1168
1169
1170 IF g1_debug_mode = 'Y' THEN
1171 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Id : ' || p_project_id);
1172 END IF;
1173
1174
1175 FOR I in 1 .. j /*l_ei_id_tab.COUNT Commented for bug 9767275*/
1176 LOOP
1177
1178 IF g1_debug_mode = 'Y' THEN
1179 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Inside EI tab .........');
1180 END IF;
1181
1182
1183 /* Checking for Project Functional and Project currencies are same, If both are same then
1184 Convert only Project Functional and copy the Project Functional attributes to
1185 project.
1186 */
1187
1188
1189 IF (l_currency_flag = 'Y') THEN
1190
1191 l_project_curr_code_tab(i) := l_projfunc_curr_code_tab(i);
1192 l_project_rate_type_tab(i) := l_projfunc_rate_type_tab(i);
1193 l_project_rate_date_tab(i) := l_projfunc_rate_date_tab(i);
1194 l_project_exchange_rate_tab(i) := l_projfunc_exchange_rate_tab(i);
1195 l_project_amount_tab(i) := l_projfunc_amount_tab(i);
1196 l_project_rev_status_tab(i) := l_projfunc_rev_status_tab(i);
1197
1198 END IF;
1199
1200
1201
1202 /* If revenue processing currency and project currency both are same then
1203 copy the project attributes to revenue processing attributes */
1204
1205 IF (l_revproc_currency_code = l_project_curr_code) THEN
1206
1207
1208 l_revproc_curr_code_tab(I) := l_project_curr_code_tab(i);
1209 l_revproc_rate_type_tab(I) := l_project_rate_type_tab(i);
1210 l_revproc_rate_date_tab(I) := l_project_rate_date_tab(i);
1211 l_revproc_exchange_rate_tab(I) := l_project_exchange_rate_tab(i);
1212 l_revproc_amount_tab(I) := l_project_amount_tab(i);
1213
1214 /* If revenue processing currency and project functional currency both are same then
1215 copy the project functional attributes to revenue processing attributes */
1216
1217
1218 ELSIF (l_revproc_currency_code = l_projfunc_curr_code) THEN
1219
1220 l_revproc_curr_code_tab(I) := l_projfunc_curr_code_tab(i);
1221 l_revproc_rate_type_tab(I) := l_projfunc_rate_type_tab(i);
1222 l_revproc_rate_date_tab(I) := l_projfunc_rate_date_tab(i);
1223 l_revproc_exchange_rate_tab(I) := l_projfunc_exchange_rate_tab(i);
1224 l_revproc_amount_tab(I) := l_projfunc_amount_tab(i);
1225
1226 END IF;
1227
1228
1229
1230 IF (l_bill_trans_curr_code_tab(I) = l_revproc_curr_code_tab(I)) THEN
1231
1232 l_revproc_adj_rev_tab(I) := l_bill_trans_adj_rev_tab(I) ;
1233
1234 ELSE
1235
1236 l_revproc_adj_rev_tab(I) :=
1237 l_bill_trans_adj_rev_tab(I) * l_revproc_exchange_rate_tab(I);
1238 END IF;
1239
1240
1241
1242 /* If error occur any one of the currency conversion fails then copy
1243 the error code into the variable l_final_error_status_tab
1244 for easy to use in following UPDATE */
1245
1246
1247 l_final_error_status_tab(I) := 'N';
1248
1249 IF l_projfunc_rev_status_tab(I) <> 'N' THEN
1250
1251 l_final_error_status_tab(I) := l_projfunc_rev_status_tab(I);
1252
1253 /* Bug :2135943 - Added for get the any onr rejection reasaon, it will
1254 use for print the rejction reason in the report */
1255
1256 x_rej_reason := l_projfunc_rev_status_tab(I);
1257
1258
1259 ELSIF l_project_rev_status_tab(I) <> 'N' THEN
1260
1261 l_final_error_status_tab(I) := l_project_rev_status_tab(I);
1262
1263 /* Bug :2135943 - Added for get the any onr rejection reasaon, it will
1264 use for print the rejction reason in the report */
1265
1266 x_rej_reason := l_project_rev_status_tab(I);
1267
1268 END IF;
1269
1270
1271
1272 -- Log Messages for EI Converted Amounts
1273
1274 IF g1_debug_mode = 'Y' THEN
1275 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
1276 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
1277 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Raw revenue :' || l_bill_trans_rev_amount_tab(i));
1278 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Bill Trans Adj revenue :' || l_bill_trans_adj_rev_tab(i));
1279 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Currency Code :' || l_project_curr_code_tab(i));
1280 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Type :' || l_project_rate_type_tab(i));
1281 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rate Date :' || l_project_rate_date_tab(i));
1282 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Xchg Rate :' || l_project_exchange_rate_tab(i));
1283 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Convert Project Amount :' || l_project_amount_tab(i));
1284 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Rejection Reason :' || l_project_rev_status_tab(i));
1285 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Convert Project Func Amount :' || l_projfunc_amount_tab(i));
1286 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Project Func Curr Code :' || l_projfunc_curr_code_tab(i));
1287 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Type :' || l_projfunc_rate_type_tab(i));
1288 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Rate Date :' || l_projfunc_rate_date_tab(i));
1289 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Projfunc Xchg Rate :' || l_projfunc_exchange_rate_tab(i));
1290 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'ProjFunc Amt Rejection Reason :' || l_projfunc_rev_status_tab(i));
1291 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Convert RevProc Amount :' || l_revproc_amount_tab(i));
1292 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
1293 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Rate Type :' || l_revproc_rate_type_tab(i));
1294 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'RevProc Rate Date :' || l_revproc_rate_date_tab(i));
1295 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Xchg Rate :' || l_revproc_exchange_rate_tab(i));
1296 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Revproc Amt Rejection Reason :' || l_raw_rev_status_tab(i));
1297 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Adjusted Reveneue :' || l_revproc_adj_rev_tab(i));
1298 END IF;
1299
1300
1301
1302 END LOOP;
1303
1304
1305
1306 /* Updating the converted amount to the Expenditure Item table.
1307 Converted amount column : raw_revenue, adjusted_revenue, bill_rate, adjusted_rate
1308 Other columns : Initialize when conversion fails and marking revenue
1309 distributed flag to 'N */
1310
1311 IF g1_debug_mode = 'Y' THEN
1312 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Before Updating EI table .....');
1313 PA_MCB_INVOICE_PKG.log_message('ei_amount_conversion: ' || 'Inside If statement .....');
1314 END IF;
1315
1316 FORALL I IN 1 ..j /*l_ei_id_tab.COUNT Commented for bug 9767275*/
1317 UPDATE pa_expenditure_items_all
1318 SET raw_revenue =
1319 DECODE(l_final_error_status_tab(i), 'N', l_revproc_amount_tab(i), NULL),
1320 adjusted_revenue =
1321 DECODE(l_final_error_status_tab(i), 'N',
1322 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_revproc_adj_rev_tab(i),
1323 l_revproc_curr_code_tab(i)), NULL),
1324 project_raw_revenue =
1325 DECODE(l_final_error_status_tab(i), 'N', l_project_amount_tab(i), NULL),
1326 projfunc_raw_revenue =
1327 DECODE(l_final_error_status_tab(i), 'N', l_projfunc_amount_tab(i), NULL),
1328 bill_trans_raw_revenue =
1329 DECODE(l_final_error_status_tab(i), 'N', bill_trans_raw_revenue, NULL),
1330 bill_trans_adjusted_revenue =
1331 DECODE(l_final_error_status_tab(i), 'N', bill_trans_adjusted_revenue, NULL),
1332 accrued_revenue =
1333 DECODE(l_final_error_status_tab(i), 'N', accrued_revenue, NULL),
1334 accrual_rate =
1335 DECODE(l_final_error_status_tab(i), 'N', accrual_rate, NULL),
1336 revenue_distributed_flag =
1337 DECODE(l_final_error_status_tab(i), 'N', revenue_distributed_flag, 'N'),
1338 rev_dist_rejection_code =
1339 DECODE(l_final_error_status_tab(i), 'N',NULL, l_final_error_status_tab(i)),
1340 revproc_currency_code = l_revproc_curr_code_tab(i),
1341 revproc_rate_type = l_revproc_rate_type_tab(i),
1342 -- revproc_rate_date = l_revproc_rate_date_tab(i), --Modified for Bug3137196
1343 revproc_rate_date = decode(l_revproc_rate_type_tab(i), 'User', null, l_revproc_rate_date_tab(i)),
1344 revproc_exchange_rate = l_revproc_exchange_rate_tab(i),
1345 projfunc_currency_code = l_projfunc_curr_code_tab(i),
1346 project_rev_rate_type = l_project_rate_type_tab(i),
1347 -- project_rev_rate_date = l_project_rate_date_tab(i), --Modified for Bug3137196
1348 project_rev_rate_date = decode(l_project_rate_type_tab(i), 'User', null, l_project_rate_date_tab(i)),
1349 project_rev_exchange_rate = l_project_exchange_rate_tab(i),
1350 projfunc_rev_rate_type = l_projfunc_rate_type_tab(i),
1351 -- projfunc_rev_rate_date = l_projfunc_rate_date_tab(i), --Modified for Bug3137196
1352 projfunc_rev_rate_date = decode(l_projfunc_rate_type_tab(i), 'User', null, l_projfunc_rate_date_tab(i)),
1353 projfunc_rev_exchange_rate = l_projfunc_exchange_rate_tab(i)
1354 WHERE expenditure_item_id = l_ei_id_tab(i);
1355
1356 IF g1_debug_mode = 'Y' THEN
1357 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in EI table : ' || SQL%ROWCOUNT);
1358 END IF;
1359
1360 /*END IF; /* l_ei_id_tab.COUNT <> 0 *
1361
1362 EXIT WHEN ei_amt_csr%NOTFOUND;
1363
1364
1365 END LOOP;
1366
1367 CLOSE ei_amt_csr; Commented for bug 9767275*/
1368
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372
1373 IF g1_debug_mode = 'Y' THEN
1374 PA_MCB_INVOICE_PKG.log_message('Error in Ei_amount_conversion ' || sqlerrm);
1375 END IF;
1376
1377 x_return_status := sqlerrm( sqlcode );
1378
1379 END ei_amount_conversion;
1380
1381
1382 /*----------------------------------------------------------------------------------------+
1383 | Procedure : rdl_amount_conversion |
1384 | Purpose : To update the RDLltable |
1385 | (bill transaction currency to revenue processing currency) |
1386 | |
1387 | Parameters : |
1388 | ================================================================================== |
1389 | Name Mode Description |
1390 | ================================================================================== |
1391 | p_project_id IN project Id |
1392 | ei_id IN Expenditure item id |
1393 | p_request_id IN Id for the current Run |
1394 | p_raw_revenue IN raw revenue from EI table |
1395 | p_bill_trans_raw_revenue IN bill trans raw revenue from EI table. |
1396 | p_project_raw_revenue IN Project Raw Revenue |
1397 | p_projfunc_raw_revenue IN Project Functional raw Revenue |
1398 | p_funding_rate_date IN Funding Rate Date |
1399 | x_return_status IN OUT Return status of this procedure |
1400 | x_msg_count IN OUT Error message count |
1401 | x_msg_data IN OUT Error message |
1402 | ================================================================================== |
1403 +----------------------------------------------------------------------------------------*/
1404
1405 PROCEDURE rdl_amount_conversion(
1406 p_project_id IN NUMBER,
1407 p_request_id IN NUMBER,
1408 p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
1409 p_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1410 p_bill_trans_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1411 p_project_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1412 p_projfunc_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
1413 p_funding_rate_date IN VARCHAR2,
1414 x_return_status IN OUT NOCOPY VARCHAR2,
1415 x_msg_count IN OUT NOCOPY NUMBER,
1416 x_msg_data IN OUT NOCOPY VARCHAR2) IS
1417
1418
1419 CURSOR rdl_amt_csr (p_project_id NUMBER,
1420 p_request_id NUMBER) IS
1421 SELECT rdl.expenditure_item_id,
1422 ei.expenditure_item_date, /* Added for bug 5907315*/
1423 rdl.line_num,
1424 rdl.draft_revenue_num,
1425 rdl.bill_trans_currency_code,
1426 rdl.amount,
1427 rdl.project_currency_code,
1428 rdl.project_rev_rate_type,
1429 rdl.project_rev_rate_date,
1430 rdl.project_rev_exchange_rate,
1431 rdl.projfunc_currency_code,
1432 rdl.projfunc_rev_rate_type,
1433 rdl.projfunc_rev_rate_date,
1434 rdl.projfunc_rev_exchange_rate,
1435 rdl.funding_currency_code,
1436 rdl.funding_rev_rate_type,
1437 rdl.funding_rev_rate_date,
1438 rdl.funding_rev_exchange_rate,
1439 nvl(ei.adjusted_revenue, ei.raw_revenue),
1440 nvl(ei.bill_trans_adjusted_revenue, ei.bill_trans_raw_revenue),
1441 ei.project_raw_revenue,
1442 ei.projfunc_raw_revenue,
1443 RDL.REVTRANS_CURRENCY_CODE,
1444 RDL.REVPROC_REVTRANS_RATE_TYPE,
1445 RDL.REVPROC_REVTRANS_RATE_DATE,
1446 RDL.REVPROC_REVTRANS_EX_RATE
1447 FROM pa_cust_rev_dist_lines rdl,
1448 pa_expenditure_items_all ei
1449 WHERE rdl.project_id = p_project_id
1450 AND ei.expenditure_item_id = rdl.expenditure_item_id
1451 AND rdl.request_id = p_request_id
1452 AND rdl.bill_trans_amount is NULL ;
1453
1454
1455
1456 l_ei_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1457 l_ei_date_tab PA_PLSQL_DATATYPES.DateTabTyp; /* Added for bug 5907315*/
1458 l_bill_trans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1459 l_project_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1460 l_project_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1461 l_project_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1462 l_project_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1463 l_projfunc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1464 l_projfunc_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1465 l_projfunc_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1466 l_projfunc_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1467 l_funding_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1468 l_funding_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1469 l_funding_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1470 l_funding_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1471
1472 l_BTC_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1473
1474 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1475 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1476 l_funding_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1477
1478 l_project_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1479 l_projfunc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1480 l_funding_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1481 l_project_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1482 l_projfunc_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1483 l_funding_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1484 l_project_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1485 l_projfunc_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1486 l_funding_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1487 l_project_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1488 l_projfunc_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1489 l_funding_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1490
1491 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1492
1493 l_revenue_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1494
1495 l_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1496
1497 l_draft_revenue_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1498
1499 l_error_draft_rev_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1500 l_error_ei_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1501 l_error_funding_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1502
1503 l_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1504 l_bill_trans_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1505 l_project_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1506 l_projfunc_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1507
1508
1509 l_funding_rate_date DATE;
1510
1511 l_counter NUMBER;
1512
1513 l_conversion_between VARCHAR2(6);
1514 l_cache_flag VARCHAR2(1);
1515
1516 l_pf_currency_flag VARCHAR2(1) := 'N';
1517 l_prj_currency_flag VARCHAR2(1) := 'N';
1518
1519 /* Revenue in foreign currency */
1520 l_revtrans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1521 l_revtrans_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1522 l_revtrans_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1523 l_revtrans_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1524 l_mcb_flag VARCHAR2(1);
1525 l_inv_by_btc_flag VARCHAR2(1);
1526 l_rev_in_txn_curr_flag VARCHAR2(1);
1527 l_pf_currency_flag_rtc_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1528 l_bt_currency_flag_rtc_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1529 l_revtrans_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1530 l_error_revtrans_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1531 l_PFC_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1532 l_revtrans_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1533 l_revtrans_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1534 l_revtrans_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1535
1536 l_pf_currency_code_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1537 l_rt_currency_code_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1538 l_rt_rate_date_t1_tab PA_PLSQL_DATATYPES.DateTabTyp;
1539 l_rt_rate_type_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1540 l_pf_amount_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1541 l_user_validate_flag_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1542 l_rt_amount_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1543 l_rt_denominator_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1544 l_rt_numerator_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1545 l_rt_xchg_rate_t1_tab PA_PLSQL_DATATYPES.NumTabTyp;
1546 l_rt_status_t1_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1547 l_error_rt_dr_rev_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1548
1549 /* Variable declaration for bug 5907315 */
1550
1551 l_previous_funding_rate_date DATE;
1552
1553 /* End of variable declaration: Bug 5907315 */
1554
1555 BEGIN
1556
1557
1558 /* Initiallizing the Currency variable for check PF and Project curency are same
1559 for Funding Currency or not *
1560
1561 l_pf_currency_flag := 'N';
1562 l_prj_currency_flag := 'N';
1563
1564
1565 /* This flag is N then the convert_amount_bulk API not cache any currency code,
1566 If the flag is Y then it cache the currency and other attributes for avoid the
1567 repeat processing. */
1568
1569
1570 l_cache_flag := 'N';
1571
1572
1573 IF g1_debug_mode = 'Y' THEN
1574 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Inside the Procedure RDL AMOUT conversion');
1575 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '-----------------------------------------');
1576 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'project Id ' || p_project_id);
1577 END IF;
1578
1579
1580
1581 /* Convert the funding rate date charater to DATE format */
1582
1583 /*File.Date.5. Added format to the p_funding_rate_date which was missing*/
1584 l_funding_rate_date := TO_DATE(p_funding_rate_date,'YYYY/MM/DD');
1585
1586 /* Revenue in foreign currency */
1587
1588 l_mcb_flag := pa_billing.globvars.mcb_flag;
1589 l_inv_by_btc_flag := pa_billing.globvars.inv_by_btc_flag;
1590 l_rev_in_txn_curr_flag := pa_billing.globvars.rev_in_txn_curr_flag;
1591
1592
1593 OPEN rdl_amt_csr( p_project_id,
1594 p_request_id);
1595
1596
1597 LOOP
1598
1599 /* Added l_ei_date_tab for bug 5907315*/
1600 FETCH rdl_amt_csr BULK COLLECT
1601 INTO l_ei_id_tab,
1602 l_ei_date_tab,
1603 l_line_num_tab,
1604 l_draft_revenue_num_tab,
1605 l_bill_trans_currency_code_tab,
1606 l_revenue_amount_tab,
1607 l_project_currency_code_tab,
1608 l_project_rev_rate_type_tab,
1609 l_project_rev_rate_date_tab,
1610 l_project_rev_xchg_rate_tab,
1611 l_projfunc_currency_code_tab,
1612 l_projfunc_rev_rate_type_tab,
1613 l_projfunc_rev_rate_date_tab,
1614 l_projfunc_rev_xchg_rate_tab,
1615 l_funding_currency_code_tab,
1616 l_funding_rev_rate_type_tab,
1617 l_funding_rev_rate_date_tab,
1618 l_funding_rev_xchg_rate_tab,
1619 l_raw_revenue,
1620 l_bill_trans_raw_revenue,
1621 l_project_raw_revenue,
1622 l_projfunc_raw_revenue,
1623 l_revtrans_currency_code_tab,
1624 l_revtrans_rate_type_tab,
1625 l_revtrans_rate_date_tab,
1626 l_revtrans_xchg_rate_tab LIMIT 100;
1627
1628
1629
1630 /* If fetch return more than one row then proceeding for the conversion */
1631
1632
1633 IF (l_ei_id_tab.COUNT = 0) THEN
1634
1635 Exit;
1636
1637 ELSE /* (l_ei_id_tab.COUNT <> 0) */
1638
1639
1640 FOR I in 1..l_ei_id_tab.COUNT
1641 LOOP
1642
1643
1644
1645 /* Checking for Project Functional and Funding Currencies are same or
1646 Project and Funding currencies are same the copy the Project Functional and
1647 Project currency to Funding currency
1648 If not same then we need to convert the funding amount
1649 */
1650
1651
1652 IF g1_debug_mode = 'Y' THEN
1653 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PF Currency ' || l_projfunc_currency_code_tab(i));
1654 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PC Currency ' || l_project_currency_code_tab(i));
1655 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'FC Currency ' || l_funding_currency_code_tab(i));
1656 END IF;
1657
1658
1659
1660
1661 IF (l_projfunc_currency_code_tab(i) = l_funding_currency_code_tab(i)) THEN
1662
1663 l_pf_currency_flag := 'Y';
1664
1665 END IF;
1666
1667
1668 IF (l_project_currency_code_tab(i) = l_funding_currency_code_tab(i)) THEN
1669
1670 l_prj_currency_flag := 'Y';
1671
1672 END IF;
1673
1674 /* Revenue in foreign currency */
1675 l_pf_currency_flag_rtc_tab(i) := 'N';
1676 l_bt_currency_flag_rtc_tab(i) := 'N';
1677 If l_mcb_flag = 'Y' AND l_inv_by_btc_flag = 'N' AND l_rev_in_txn_curr_flag = 'Y' Then
1678
1679 IF (l_projfunc_currency_code_tab(i) = l_revtrans_currency_code_tab(i)) THEN
1680
1681 l_pf_currency_flag_rtc_tab(i) := 'Y';
1682
1683 ELSIF (l_bill_trans_currency_code_tab(i) = l_revtrans_currency_code_tab(i)) THEN
1684
1685 l_bt_currency_flag_rtc_tab(i) := 'Y';
1686
1687 ELSIF (l_bill_trans_currency_code_tab(i) <> l_revtrans_currency_code_tab(i)) THEN
1688
1689 l_bt_currency_flag_rtc_tab(i) := 'N';
1690 l_pf_currency_flag_rtc_tab(i) := 'N';
1691
1692 END IF;
1693
1694 End If;
1695
1696 /* Initializing the array variables */
1697
1698
1699 l_user_validate_flag_tab(I) := 'Y';
1700
1701 l_project_bill_rate_tab(I) := NULL;
1702 l_projfunc_bill_rate_tab(I) := NULL;
1703 l_funding_bill_rate_tab(I) := NULL;
1704 l_project_denominator_tab(I) := NULL;
1705 l_projfunc_denominator_tab(I) := NULL;
1706 l_funding_denominator_tab(I) := NULL;
1707 l_project_numerator_tab(I) := NULL;
1708 l_projfunc_numerator_tab(I) := NULL;
1709 l_funding_numerator_tab(I) := NULL;
1710 l_project_status_tab(I) := 'N';
1711 l_projfunc_status_tab(I) := 'N';
1712 l_funding_status_tab(I) := 'N';
1713 l_funding_amount_tab(i) := NULL;
1714
1715 l_projfunc_amount_tab(I) := null;
1716 l_revtrans_amount_tab(I) := null;
1717 l_revtrans_denominator_tab(I) := null;
1718 l_revtrans_numerator_tab(I) := null;
1719 l_revtrans_status_tab(I) := 'N';
1720 /* Copy the funding rate date to array variable */
1721 /* Code added for bug 5907315 */
1722 l_previous_funding_rate_date := NULL;
1723
1724 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1725 l_previous_funding_rate_date := pa_date(l_ei_date_tab(I));
1726 END IF;
1727 /* End of code. Bug 5907315 */
1728
1729 /* Code commented for bug 5907315
1730 l_funding_rev_rate_date_tab(I) := l_funding_rate_date;
1731 The statement is modified as below */
1732 IF fnd_profile_revenue_orig_rate = 'Y' THEN
1733 l_funding_rev_rate_date_tab(I) := nvl(l_previous_funding_rate_date,l_funding_rate_date);
1734 ELSE
1735 l_funding_rev_rate_date_tab(I) := l_funding_rate_date;
1736 END IF;
1737 /* End of bug 5907315 */
1738
1739 /* Calculating BTC amount from bill trans raw revenue and raw revenue */
1740
1741 l_BTC_amount_tab(I) :=
1742 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1743 ( l_revenue_amount_tab(I) / l_raw_revenue(I) ) * l_bill_trans_raw_revenue(I),l_bill_trans_currency_code_tab(i));
1744
1745
1746 /* Calculating the project amount from bill trans raw revenue and raw revenue */
1747
1748 l_project_amount_tab(I) := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1749 ( l_revenue_amount_tab(I) / l_raw_revenue(I) ) * l_project_raw_revenue(I),l_project_currency_code_tab(i));
1750
1751
1752 /* Calculating project functional amount from bill trans raw revenue and raw revenue */
1753
1754
1755 l_projfunc_amount_tab(I) := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1756 ( l_revenue_amount_tab(I) / l_raw_revenue(I) ) * l_projfunc_raw_revenue(I), l_projfunc_currency_code_tab(i));
1757
1758
1759 /* Debug message for MCB testing */
1760
1761
1762 IF g1_debug_mode = 'Y' THEN
1763 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans curr Code :' || l_bill_trans_currency_code_tab(i));
1764 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input revenue amount :' || l_revenue_amount_tab(i));
1765 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input Raw revenue :' || l_raw_revenue(i));
1766 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input Bill trans raw rev:' || l_bill_trans_raw_revenue(i));
1767 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input Project raw rev :' || l_project_raw_revenue(i));
1768 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Input projfunc raw rev :' || l_projfunc_raw_revenue(i));
1769 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Calculated Project amount:' || l_project_amount_tab(i));
1770 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Calculated projfunc amount:' || l_projfunc_amount_tab(i));
1771 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Curr Code :' || l_funding_currency_code_tab(i));
1772 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate Type :' || l_funding_rev_rate_type_tab(i));
1773 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate date :' || l_funding_rev_rate_date_tab(i));
1774 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding xchg rate :' || l_funding_rev_xchg_rate_tab(i));
1775 END IF;
1776
1777 END LOOP;
1778
1779
1780
1781 -- BTC amounts to Funding currency amount
1782
1783
1784 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
1785 then the API concatenate the code with error message */
1786
1787
1788 IF g1_debug_mode = 'Y' THEN
1789 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Calling convert_amount_bulk API for funding conversion');
1790 END IF;
1791
1792
1793 /* If Funding Currency are not equal to project and Project Functional currency then
1794 Convert the funding amount otherwise copy the Project or project Function attributes
1795 to funding currency */
1796
1797
1798 IF g1_debug_mode = 'Y' THEN
1799 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PF Currency flag ' || l_pf_currency_flag);
1800 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'PC Currency flag ' || l_prj_currency_flag);
1801 END IF;
1802
1803
1804
1805
1806 IF (l_pf_currency_flag <> 'Y') AND (l_prj_currency_flag <> 'Y') THEN
1807
1808 l_conversion_between := 'BTC_FC';
1809
1810
1811 IF g1_debug_mode = 'Y' THEN
1812 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding is not equal to PF and project .....');
1813 END IF;
1814
1815 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1816 p_from_currency_tab => l_bill_trans_currency_code_tab,
1817 p_to_currency_tab => l_funding_currency_code_tab,
1818 p_conversion_date_tab => l_funding_rev_rate_date_tab,
1819 p_conversion_type_tab => l_funding_rev_rate_type_tab,
1820 p_amount_tab => l_BTC_amount_tab,
1821 p_user_validate_flag_tab => l_user_validate_flag_tab,
1822 p_converted_amount_tab => l_funding_amount_tab,
1823 p_denominator_tab => l_funding_denominator_tab,
1824 p_numerator_tab => l_funding_numerator_tab,
1825 p_rate_tab => l_funding_rev_xchg_rate_tab,
1826 p_conversion_between => l_conversion_between,
1827 p_cache_flag => l_cache_flag,
1828 x_status_tab => l_funding_status_tab
1829 );
1830
1831
1832 END IF;
1833
1834
1835 IF g1_debug_mode = 'Y' THEN
1836 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'after Calling convert_amount_bulk API for funding conversion');
1837 END IF;
1838
1839 l_counter := 1;
1840
1841
1842 FOR I in 1 ..l_ei_id_tab.COUNT
1843 LOOP
1844
1845
1846 /* If Funding Currency are not equal to project and Project Functional currency then
1847 Convert the funding amount otherwise copy the Project or project Function attributes
1848 to funding currency */
1849
1850
1851 IF (l_pf_currency_flag = 'Y') THEN
1852
1853 IF g1_debug_mode = 'Y' THEN
1854 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....PF Currency = Funding currency.....');
1855 END IF;
1856
1857 l_funding_currency_code_tab(i) := l_projfunc_currency_code_tab(i);
1858 l_funding_rev_rate_type_tab(i) := l_projfunc_rev_rate_type_tab(i);
1859 l_funding_rev_rate_date_tab(i) := l_projfunc_rev_rate_date_tab(i);
1860 l_funding_rev_xchg_rate_tab(i) := l_projfunc_rev_xchg_rate_tab(i);
1861 l_funding_amount_tab(i) := l_projfunc_amount_tab(i);
1862 l_funding_status_tab(i) := 'N';
1863
1864 ELSIF (l_prj_currency_flag = 'Y') THEN
1865
1866 IF g1_debug_mode = 'Y' THEN
1867 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....Project currency = Funding currency.....');
1868 END IF;
1869
1870 l_funding_currency_code_tab(i) := l_project_currency_code_tab(i);
1871 l_funding_rev_rate_type_tab(i) := l_project_rev_rate_type_tab(i);
1872 l_funding_rev_rate_date_tab(i) := l_project_rev_rate_date_tab(i);
1873 l_funding_rev_xchg_rate_tab(i) := l_project_rev_xchg_rate_tab(i);
1874 l_funding_amount_tab(i) := l_project_amount_tab(i);
1875 l_funding_status_tab(i) := 'N';
1876
1877 END IF;
1878
1879
1880 /* Get the draft revenue number for conversion fail cases
1881 to mark in pa_draft_revenues table */
1882
1883
1884 IF g1_debug_mode = 'Y' THEN
1885 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Inside the loop ........');
1886 END IF;
1887
1888 IF l_funding_status_tab(i) <> 'N' THEN
1889
1890
1891 l_error_draft_rev_num_tab(l_counter) := l_draft_revenue_num_tab(I);
1892
1893 l_error_ei_id_tab(l_counter) := l_ei_id_tab(I);
1894
1895 l_error_funding_status_tab(l_counter) := l_funding_status_tab(I);
1896
1897 l_counter := l_counter + 1;
1898
1899
1900 END IF;
1901
1902
1903
1904 -- Log Messages for Events Converted Amounts
1905
1906 IF g1_debug_mode = 'Y' THEN
1907 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
1908 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Line Num :' || l_line_num_tab(i));
1909 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Draft revenue Num :' || l_draft_revenue_num_tab(i));
1910 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_currency_code_tab(i));
1911 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Bill Trans Amount:' || l_BTC_amount_tab(i));
1912 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Amount :' || l_project_amount_tab(i));
1913 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Func Amount :' || l_projfunc_amount_tab(i));
1914 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Curr Code :' || l_funding_currency_code_tab(i));
1915 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate Type :' || l_funding_rev_rate_type_tab(i));
1916 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Rate date :' || l_funding_rev_rate_date_tab(i));
1917 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Echg rate :' || l_funding_rev_xchg_rate_tab(i));
1918 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Funding Amt Rejection Reason :' || l_funding_status_tab(i));
1919 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Convert Funding Amount :' || l_funding_amount_tab(i));
1920 END IF;
1921
1922
1923
1924 IF l_error_draft_rev_num_tab.EXISTS(i) THEN
1925
1926 IF g1_debug_mode = 'Y' THEN
1927 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Error Draft revenue num :' || l_error_draft_rev_num_tab(i));
1928 END IF;
1929
1930 END IF;
1931
1932
1933 END LOOP;
1934
1935 IF g1_debug_mode = 'Y' THEN
1936 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'after Calling convert_amount_bulk API for funding conversion');
1937 END IF;
1938
1939 l_counter := 1;
1940
1941
1942 FOR I in 1 .. l_ei_id_tab.COUNT
1943 LOOP
1944
1945 IF l_rev_in_txn_curr_flag = 'Y' AND l_mcb_flag = 'Y' AND l_inv_by_btc_flag = 'N' THEN
1946
1947 /* If revenue txn Currency is not equal to transaction and Project Functional currency then
1948 Convert the revenue txn amount otherwise copy the transaction or project Functional attributes
1949 to revenue txn currency */
1950
1951 -- Log Messages
1952
1953 IF g1_debug_mode = 'Y' THEN
1954 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
1955 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Line Num :' || l_line_num_tab(i));
1956 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Draft revenue Num :' || l_draft_revenue_num_tab(i));
1957 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'Bill Trans Currency Code :'||l_bill_trans_currency_code_tab(i));
1958 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans Amount:' || l_BTC_amount_tab(i));
1959 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Func Amount :' || l_projfunc_amount_tab(i));
1960 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Curr Code :' || l_revtrans_currency_code_tab(i));
1961 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate Type :' || l_revtrans_rate_type_tab(i));
1962 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate date :' || l_revtrans_rate_date_tab(i));
1963 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Echg rate :' || l_revtrans_xchg_rate_tab(i));
1964 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rejection Reason :' || l_revtrans_status_tab(i));
1965 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Convert Revtrans Amount :' || l_revtrans_amount_tab(i));
1966 END IF;
1967
1968
1969 IF (l_pf_currency_flag_rtc_tab(i) = 'Y') THEN
1970
1971 IF g1_debug_mode = 'Y' THEN
1972 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....PF Currency = RT Currency.....');
1973 END IF;
1974
1975 l_revtrans_rate_type_tab(i) := NULL;
1976 l_revtrans_rate_date_tab(i) := NULL;
1977 l_revtrans_xchg_rate_tab(i) := NULL;
1978 l_revtrans_amount_tab(i) := l_projfunc_amount_tab(i);
1979 l_revtrans_status_tab(i) := 'N';
1980
1981 ELSIF (l_bt_currency_flag_rtc_tab(i) = 'Y') THEN
1982
1983 IF g1_debug_mode = 'Y' THEN
1984 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || '....BT Currency = RT currency.....');
1985 END IF;
1986
1987 l_revtrans_rate_type_tab(i) := l_projfunc_rev_rate_type_tab(i);
1988 l_revtrans_rate_date_tab(i) := l_projfunc_rev_rate_date_tab(i);
1989 l_revtrans_xchg_rate_tab(i) := 1/l_projfunc_rev_xchg_rate_tab(i);
1990 l_revtrans_amount_tab(i) := l_BTC_amount_tab(i);
1991 l_revtrans_status_tab(i) := 'N';
1992
1993 ELSIF ((l_pf_currency_flag_rtc_tab(i) <> 'Y') AND (l_bt_currency_flag_rtc_tab(i) <> 'Y'))
1994 THEN
1995
1996 l_conversion_between := 'RC_RTC';
1997
1998
1999 IF g1_debug_mode = 'Y' THEN
2000 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revenue Txn is not equal to PFC and BTC .....');
2001 END IF;
2002
2003 l_pf_currency_code_t1_tab(1) := null;
2004 l_rt_currency_code_t1_tab(1) := null;
2005 l_rt_rate_date_t1_tab(1) := null;
2006 l_rt_rate_type_t1_tab(1) := null;
2007 l_pf_amount_t1_tab(1) := null;
2008 l_user_validate_flag_t1_tab(1) := 'N';
2009 l_rt_amount_t1_tab(1) := null;
2010 l_rt_denominator_t1_tab(1) := null;
2011 l_rt_numerator_t1_tab(1) := null;
2012 l_rt_xchg_rate_t1_tab(1) := null;
2013 l_rt_status_t1_tab(1) := 'N';
2014
2015 l_pf_currency_code_t1_tab(1) := l_projfunc_currency_code_tab(i);
2016 l_rt_currency_code_t1_tab(1) := l_revtrans_currency_code_tab(i);
2017 l_rt_rate_date_t1_tab(1) := l_revtrans_rate_date_tab(i);
2018 l_rt_rate_type_t1_tab(1) := l_revtrans_rate_type_tab(i);
2019 l_pf_amount_t1_tab(1) := l_projfunc_amount_tab(i);
2020 l_user_validate_flag_t1_tab(1) := l_user_validate_flag_tab(i);
2021 l_rt_xchg_rate_t1_tab(1) := l_revtrans_xchg_rate_tab(i); -- Bug 4760091
2022
2023 IF g1_debug_mode = 'Y' THEN
2024 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'Before convert_amount_bulk to derive rtc amt.....');
2025 END IF;
2026
2027 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2028 p_from_currency_tab => l_pf_currency_code_t1_tab,
2029 p_to_currency_tab => l_rt_currency_code_t1_tab,
2030 p_conversion_date_tab => l_rt_rate_date_t1_tab,
2031 p_conversion_type_tab => l_rt_rate_type_t1_tab,
2032 p_amount_tab => l_pf_amount_t1_tab,
2033 p_user_validate_flag_tab => l_user_validate_flag_t1_tab,
2034 p_converted_amount_tab => l_rt_amount_t1_tab,
2035 p_denominator_tab => l_rt_denominator_t1_tab,
2036 p_numerator_tab => l_rt_numerator_t1_tab,
2037 p_rate_tab => l_rt_xchg_rate_t1_tab,
2038 p_conversion_between => l_conversion_between,
2039 p_cache_flag => l_cache_flag,
2040 x_status_tab => l_rt_status_t1_tab
2041 );
2042
2043 IF g1_debug_mode = 'Y' THEN
2044 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'After convert_amount_bulk to derive RTC Amt.....');
2045 END IF;
2046
2047 l_revtrans_rate_date_tab(i) := trunc(l_rt_rate_date_t1_tab(1));
2048 l_revtrans_rate_type_tab(i) := l_rt_rate_type_t1_tab(1);
2049 l_revtrans_amount_tab(i):= l_rt_amount_t1_tab(1);
2050 l_revtrans_denominator_tab(i):= l_rt_denominator_t1_tab(1);
2051 l_revtrans_numerator_tab(i) := l_rt_numerator_t1_tab(1);
2052 l_revtrans_xchg_rate_tab(i) := l_rt_xchg_rate_t1_tab(1);
2053 l_revtrans_status_tab(i) := l_rt_status_t1_tab(1);
2054
2055 IF g1_debug_mode = 'Y' THEN
2056 PA_MCB_INVOICE_PKG.log_message('Rev trans rate date after conv is.....'||l_revtrans_rate_date_tab(i));
2057 PA_MCB_INVOICE_PKG.log_message('l_revtrans_status_tab('||i||') is.....'||l_revtrans_status_tab(i));
2058 END IF;
2059 END IF;
2060 ELSE
2061 IF g1_debug_mode = 'Y' THEN
2062 PA_MCB_INVOICE_PKG.log_message('Revenue not in foreign curr/mcb disabled/inv by btc enabled...');
2063 END IF;
2064 l_revtrans_currency_code_tab(i) := l_projfunc_currency_code_tab(i);
2065 l_revtrans_rate_type_tab(i) := NULL;
2066 l_revtrans_rate_date_tab(i) := NULL;
2067 l_revtrans_xchg_rate_tab(i) := NULL;
2068 l_revtrans_amount_tab(i) := l_projfunc_amount_tab(i);
2069 l_revtrans_status_tab(i) := 'N';
2070
2071 -- Log Messages
2072
2073 IF g1_debug_mode = 'Y' THEN
2074 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
2075 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Line Num :' || l_line_num_tab(i));
2076 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Draft revenue Num :' || l_draft_revenue_num_tab(i));
2077 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: '||'Bill Trans Currency Code :'||l_bill_trans_currency_code_tab(i));
2078 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Bill Trans Amount:' || l_BTC_amount_tab(i));
2079 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Output Project Func Amount :' || l_projfunc_amount_tab(i));
2080 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Curr Code :' || l_revtrans_currency_code_tab(i));
2081 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate Type :' || l_revtrans_rate_type_tab(i));
2082 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rate date :' || l_revtrans_rate_date_tab(i));
2083 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Echg rate :' || l_revtrans_xchg_rate_tab(i));
2084 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Revtrans Rejection Reason :' || l_revtrans_status_tab(i));
2085 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Convert Revtrans Amount :' || l_revtrans_amount_tab(i));
2086 END IF;
2087
2088 END IF;
2089
2090
2091 /* Get the draft revenue number for conversion fail cases
2092 to mark in pa_draft_revenues table */
2093
2094
2095 IF g1_debug_mode = 'Y' THEN
2096 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Before setting the error status........');
2097 END IF;
2098
2099 IF l_revtrans_status_tab(i) <> 'N' THEN
2100
2101
2102 l_error_rt_dr_rev_num_tab(l_counter) := l_draft_revenue_num_tab(I);
2103
2104 l_error_ei_id_tab(l_counter) := l_ei_id_tab(I);
2105
2106 l_error_revtrans_status_tab(l_counter) := l_revtrans_status_tab(I);
2107
2108 l_counter := l_counter + 1;
2109
2110
2111 END IF;
2112
2113
2114 IF l_error_draft_rev_num_tab.EXISTS(i) THEN
2115
2116 IF g1_debug_mode = 'Y' THEN
2117 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Error Draft revenue num :' || l_error_draft_rev_num_tab(i));
2118 END IF;
2119
2120 END IF;
2121
2122
2123 END LOOP;
2124
2125 /* i) Updating the converted project amount, project functional amount,
2126 funding amount and update the rev_dist_rejection_code
2127 ii) Call the round currency function for bill_trans_amount, project_revenue_amount,
2128 projfunc_revenue_amount for calculated values
2129 iii) Funding amount converted through the convert_amount_bulk API, its rounded automatically
2130 as per the funding currency code, so not necessary to call */
2131
2132 /*Bug:16102971 Added NVL function to the revtrans_amount calculation line */
2133
2134 FORALL I IN 1 .. l_ei_id_tab.COUNT
2135 UPDATE pa_cust_rev_dist_lines
2136 SET bill_trans_amount =
2137 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2138 l_BTC_amount_tab(i),l_bill_trans_currency_code_tab(i)),
2139 project_revenue_amount =
2140 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2141 l_project_amount_tab(i),l_project_currency_code_tab(i)),
2142 projfunc_revenue_amount =
2143 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2144 l_projfunc_amount_tab(i),l_projfunc_currency_code_tab(i)),
2145 funding_revenue_amount =
2146 DECODE(l_funding_status_tab(I), 'N',
2147 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2148 l_funding_amount_tab(I),l_funding_currency_code_tab(i)),
2149 NULL),
2150 funding_rev_rate_type =
2151 DECODE(l_funding_status_tab(I), 'N', l_funding_rev_rate_type_tab(I),NULL),
2152 funding_rev_rate_date =
2153 DECODE(l_funding_status_tab(I), 'N', l_funding_rev_rate_date_tab(i),funding_rev_rate_date),
2154 funding_rev_exchange_rate =
2155 DECODE(l_funding_status_tab(I), 'N', l_funding_rev_xchg_rate_tab(i), funding_rev_exchange_rate),
2156 revtrans_currency_code =
2157 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_currency_code_tab(I),NULL),
2158 revtrans_amount =
2159 DECODE(l_revtrans_status_tab(I), 'N',
2160 NVL(PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
2161 l_revtrans_amount_tab(I),l_revtrans_currency_code_tab(i)),0), NULL),
2162 revproc_revtrans_rate_type =
2163 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_rate_type_tab(I),NULL),
2164 revproc_revtrans_rate_date =
2165 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_rate_date_tab(i),NULL),
2166 revproc_revtrans_ex_rate =
2167 DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_xchg_rate_tab(i), NULL)
2168 WHERE expenditure_item_id = l_ei_id_tab(I)
2169 AND line_num = l_line_num_tab(I);
2170
2171 IF g1_debug_mode = 'Y' THEN
2172 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in RDL :' || SQL%ROWCOUNT);
2173 END IF;
2174
2175
2176 /* Marking Draft Revenues as error, If error exist in RDL amount conversion */
2177
2178 PA_MCB_INVOICE_PKG.log_message('Before updating RTC error code..BPC...:' );
2179
2180 IF l_rev_in_txn_curr_flag = 'Y' THEN
2181 IF l_error_rt_dr_rev_num_tab.COUNT <> 0 THEN
2182
2183 FORALL J IN 1 .. l_error_rt_dr_rev_num_tab.COUNT
2184 UPDATE pa_draft_revenues
2185 SET generation_error_flag = 'Y',
2186 transfer_rejection_reason = l_error_revtrans_status_tab(j)
2187 WHERE project_id = p_project_id
2188 AND draft_revenue_num = l_error_rt_dr_rev_num_tab(J);
2189
2190 IF g1_debug_mode = 'Y' THEN
2191 PA_MCB_INVOICE_PKG.log_message('No of Rows Marked Error in Draft Revenue :' || SQL%ROWCOUNT);
2192 END IF;
2193
2194 END IF;
2195 END IF;
2196
2197 IF l_error_draft_rev_num_tab.COUNT <> 0 THEN
2198
2199 FORALL J IN 1 .. l_error_draft_rev_num_tab.COUNT
2200 UPDATE pa_draft_revenues
2201 SET generation_error_flag = 'Y',
2202 transfer_rejection_reason = l_error_funding_status_tab(J)
2203 WHERE project_id = p_project_id
2204 AND draft_revenue_num = l_error_draft_rev_num_tab(J);
2205
2206 IF g1_debug_mode = 'Y' THEN
2207 PA_MCB_INVOICE_PKG.log_message('No of Rows Marked Error in Draft Revenue :' || SQL%ROWCOUNT);
2208 END IF;
2209
2210 END IF;
2211
2212 PA_MCB_INVOICE_PKG.log_message('After updating error code..BPC...:' );
2213
2214
2215 END IF; /* l_ei_id_tab.COUNT <> 0 */
2216
2217
2218
2219
2220 EXIT WHEN rdl_amt_csr%NOTFOUND;
2221
2222
2223 END LOOP;
2224
2225 CLOSE rdl_amt_csr;
2226
2227
2228 EXCEPTION
2229 WHEN OTHERS THEN
2230 IF g1_debug_mode = 'Y' THEN
2231 PA_MCB_INVOICE_PKG.log_message('rdl_amount_conversion: ' || 'Error in rdl conversion :' || sqlerrm);
2232 END IF;
2233 x_return_status := sqlerrm( sqlcode );
2234
2235 END rdl_amount_conversion;
2236
2237
2238 /*----------------------------------------------------------------------------------------+
2239 | Procedure : erdl_amount_conversion |
2240 | Purpose : To update the ERDL table |
2241 | (bill transaction currency to Funding Currency) |
2242 | |
2243 | Parameters : |
2244 | ================================================================================== |
2245 | Name Mode Description |
2246 | ================================================================================== |
2247 | p_btc_code IN Bill transaction currency code |
2248 | p_btc_amount IN Bill transaction amount |
2249 | p_funding_curr_code IN Funding currency code to convert funding amount |
2250 | x_funding_rate_type IN OUT Funding Rate type to convert funding amount |
2251 | x_funding_rate_rate IN OUT Funding Rate date to convert funding amount |
2252 | x_funding_exchange_rate IN OUT Funding Exchange Rate to convert funding amount |
2253 | x_funding_amount IN OUT Converted funding amount |
2254 | x_funding_convert_status IN OUT If converted the pass NULL else pass error code |
2255 | x_return_status IN OUT Return status of this procedure |
2256 | x_msg_count IN OUT Error message count |
2257 | x_msg_data IN OUT Error message |
2258 | ================================================================================== |
2259 +----------------------------------------------------------------------------------------*/
2260
2261 PROCEDURE erdl_amount_conversion( p_project_id IN NUMBER,
2262 p_draft_revenue_num IN NUMBER,
2263 p_btc_code IN VARCHAR2,
2264 p_btc_amount IN VARCHAR2,
2265 p_funding_rate_date IN VARCHAR2,
2266 p_funding_curr_code IN VARCHAR2,
2267 x_funding_rate_type IN OUT NOCOPY VARCHAR2,
2268 x_funding_rate_date IN OUT NOCOPY VARCHAR2,
2269 x_funding_exchange_rate IN OUT NOCOPY VARCHAR2,
2270 x_funding_amount IN OUT NOCOPY VARCHAR2,
2271 x_funding_convert_status IN OUT NOCOPY VARCHAR2,
2272 p_projfunc_curr_code IN VARCHAR2,
2273 p_projfunc_amount IN VARCHAR2,
2274 p_projfunc_rate_type IN VARCHAR2,
2275 p_projfunc_rate_date IN VARCHAR2,
2276 p_projfunc_exch_rate IN VARCHAR2,
2277 p_revtrans_curr_code IN VARCHAR2,
2278 p_calling_place IN VARCHAR2,
2279 x_revtrans_rate_type IN OUT NOCOPY VARCHAR2,
2280 x_revtrans_rate_date IN OUT NOCOPY VARCHAR2,
2281 x_revtrans_exch_rate IN OUT NOCOPY VARCHAR2,
2282 x_revtrans_amount IN OUT NOCOPY VARCHAR2,
2283 x_return_status IN OUT NOCOPY VARCHAR2,
2284 x_msg_count IN OUT NOCOPY NUMBER,
2285 x_msg_data IN OUT NOCOPY VARCHAR2
2286 ) IS
2287
2288
2289 l_btc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2290 l_bill_trans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2291 l_funding_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2292 l_funding_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2293 l_funding_rev_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2294 l_funding_rev_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2295 l_funding_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2296 l_funding_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2297 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2298 l_funding_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2299 l_funding_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2300 l_funding_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2301
2302 l_funding_rate_date DATE;
2303
2304 l_conversion_between VARCHAR2(6);
2305 l_cache_flag VARCHAR2(1);
2306
2307 /* Revenue in foreign currency */
2308 l_mcb_flag VARCHAR2(1);
2309 l_inv_by_btc_flag VARCHAR2(1);
2310
2311 l_pfc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2312 l_projfunc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2313 l_revtrans_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2314 l_revtrans_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2315 l_revtrans_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2316 l_revtrans_xchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2317 l_revtrans_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2318 l_revtrans_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2319 l_revtrans_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2320
2321 /* Added for NOCOPY change */
2322 l_x_funding_rate_type VARCHAR2(30) := x_funding_rate_type;
2323 l_x_funding_rate_date VARCHAR2(10) := x_funding_rate_date;
2324 l_x_funding_exchange_rate VARCHAR2(30) := x_funding_exchange_rate;
2325 l_x_funding_amount VARCHAR2(30) := x_funding_amount;
2326 l_x_funding_convert_status VARCHAR2(30) := x_funding_convert_status;
2327 l_x_revtrans_rate_type VARCHAR2(30) := x_revtrans_rate_type;
2328 l_x_revtrans_rate_date VARCHAR2(10) := x_revtrans_rate_date;
2329 l_x_revtrans_exch_rate VARCHAR2(30) := x_revtrans_exch_rate;
2330
2331 BEGIN
2332
2333
2334 /* This flag is N then the convert_amount_bulk API not cache any currency code,
2335 If the flag is Y then it cache the currency and other attributes for avoid the
2336 repeat processing. */
2337
2338 l_cache_flag := 'N';
2339
2340 IF p_calling_place = 'FC' THEN
2341
2342 IF g1_debug_mode = 'Y' THEN
2343 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || '....Inside the Procedure ERDL conversion');
2344 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || '---------------------------------------');
2345 END IF;
2346
2347
2348
2349 /* Converting the funding rate date Character to DATE format */
2350
2351
2352 /*File.Date.5. Added format to the p_funding_rate_date which was missing*/
2353 l_funding_rate_date := TO_DATE(p_funding_rate_date,'YYYY/MM/DD');
2354
2355
2356 x_return_status := NULL;
2357
2358
2359 /* Copy the Input funding attributed to array variables */
2360
2361 l_btc_amount_tab(1) := p_btc_amount;
2362 l_bill_trans_currency_code_tab(1) := p_btc_code;
2363 l_funding_currency_code_tab(1) := p_funding_curr_code;
2364 l_funding_rev_rate_type_tab(1) := x_funding_rate_type;
2365 l_funding_rev_rate_date_tab(1) := x_funding_rate_date;
2366 l_funding_rev_xchg_rate_tab(1) := x_funding_exchange_rate;
2367 l_funding_amount_tab(1) := NULL;
2368 l_funding_status_tab(1) := 'N';
2369 l_user_validate_flag_tab(1) := 'Y';
2370 l_funding_denominator_tab(1) := NULL;
2371 l_funding_numerator_tab(1) := NULL;
2372 l_funding_bill_rate_tab(1) := NULL;
2373
2374
2375
2376 /* If funding rate date is null then take the funding rate from projects table */
2377
2378 IF l_funding_rev_rate_date_tab(1) IS NULL THEN
2379
2380 l_funding_rev_rate_date_tab(1) := l_funding_rate_date;
2381
2382 END IF;
2383
2384
2385
2386 -- BTC amounts to Funding currency amount conversion
2387
2388 IF g1_debug_mode = 'Y' THEN
2389 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Calling the Funding Amount conversion procedure');
2390 END IF;
2391
2392
2393 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
2394 then the API concatenate the code with error message */
2395
2396 l_conversion_between := 'BTC_FC';
2397
2398
2399 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2400 p_from_currency_tab => l_bill_trans_currency_code_tab,
2401 p_to_currency_tab => l_funding_currency_code_tab,
2402 p_conversion_date_tab => l_funding_rev_rate_date_tab,
2403 p_conversion_type_tab => l_funding_rev_rate_type_tab,
2404 p_amount_tab => l_btc_amount_tab,
2405 p_user_validate_flag_tab => l_user_validate_flag_tab,
2406 p_converted_amount_tab => l_funding_amount_tab,
2407 p_denominator_tab => l_funding_denominator_tab,
2408 p_numerator_tab => l_funding_numerator_tab,
2409 p_rate_tab => l_funding_rev_xchg_rate_tab,
2410 p_conversion_between => l_conversion_between,
2411 p_cache_flag => l_cache_flag,
2412 x_status_tab => l_funding_status_tab
2413 );
2414
2415
2416 /* Copy the converted amount and attributes to OUT variables */
2417
2418
2419 x_funding_rate_type := l_funding_rev_rate_type_tab(1);
2420 x_funding_rate_date := l_funding_rev_rate_date_tab(1);
2421 x_funding_exchange_rate := l_funding_rev_xchg_rate_tab(1);
2422 x_funding_amount := l_funding_amount_tab(1);
2423 x_funding_convert_status := l_funding_status_tab(1);
2424
2425
2426 -- Log Messages for Funding amount conversion in ERDL
2427
2428
2429 IF g1_debug_mode = 'Y' THEN
2430 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Project Id :' || p_project_id);
2431 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Draft revenue Num :' || p_draft_revenue_num);
2432 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Bill Trans Currency Code :' || p_btc_code);
2433 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Bill Trans Amount :' || p_btc_amount);
2434 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding Currency Code :' || p_funding_curr_code);
2435 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding rate type :' || l_funding_rev_rate_type_tab(1));
2436 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding rate date :' || l_funding_rev_rate_date_tab(1));
2437 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding exchange rate :' || l_funding_rev_xchg_rate_tab(1));
2438 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Funding Amt Rejection Reason :' || l_funding_status_tab(1));
2439 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Convert Funding Amount :' || l_funding_amount_tab(1));
2440 END IF;
2441
2442
2443
2444 /* Marking the draft revenues as error if funding conversion fails */
2445
2446
2447 IF (l_funding_status_tab(1) <> 'N') THEN
2448
2449 UPDATE pa_draft_revenues
2450 SET generation_error_flag = 'Y',
2451 transfer_rejection_reason = l_funding_status_tab(1)
2452 WHERE project_id = p_project_id
2453 AND draft_revenue_num = p_draft_revenue_num;
2454
2455 IF g1_debug_mode = 'Y' THEN
2456 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated as error in Draft Revenue : ' || SQL%ROWCOUNT);
2457 END IF;
2458
2459 END IF;
2460
2461 ELSIF p_calling_place = 'RTC' THEN
2462
2463 /* Revenue in foreign currency - Start*/
2464
2465 x_return_status := NULL;
2466
2467 -- Log Messages
2468
2469 IF g1_debug_mode = 'Y' THEN
2470 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Project Id :' || p_project_id);
2471 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Draft revenue Num :' || p_draft_revenue_num);
2472 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Currency Code :' || p_projfunc_curr_code);
2473 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Amount :' || p_projfunc_amount);
2474 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Currency Code :' || p_revtrans_curr_code);
2475 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Amount :' || x_revtrans_amount);
2476 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate type :' || x_revtrans_rate_type);
2477 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate date :' || x_revtrans_rate_date);
2478 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans exchange rate :' || x_revtrans_exch_rate);
2479 END IF;
2480 /* Copy the Input revenue transactions attributes to array variables */
2481
2482 l_pfc_amount_tab(1) := p_projfunc_amount;
2483 l_projfunc_currency_code_tab(1) := p_projfunc_curr_code;
2484 l_revtrans_currency_code_tab(1) := p_revtrans_curr_code;
2485 l_revtrans_rate_type_tab(1) := x_revtrans_rate_type;
2486 l_revtrans_rate_date_tab(1) := to_date(x_revtrans_rate_date, 'YYYY/MM/DD'); -- For bug 4751461
2487 l_revtrans_xchg_rate_tab(1) := x_revtrans_exch_rate;
2488 l_revtrans_amount_tab(1) := NULL;
2489 l_funding_status_tab(1) := 'N';
2490 l_user_validate_flag_tab(1) := 'Y';
2491 l_revtrans_denominator_tab(1) := NULL;
2492 l_revtrans_numerator_tab(1) := NULL;
2493 l_funding_bill_rate_tab(1) := NULL;
2494
2495
2496 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2497 p_from_currency_tab => l_projfunc_currency_code_tab,
2498 p_to_currency_tab => l_revtrans_currency_code_tab,
2499 p_conversion_date_tab => l_revtrans_rate_date_tab,
2500 p_conversion_type_tab => l_revtrans_rate_type_tab,
2501 p_amount_tab => l_pfc_amount_tab,
2502 p_user_validate_flag_tab => l_user_validate_flag_tab,
2503 p_converted_amount_tab => l_revtrans_amount_tab,
2504 p_denominator_tab => l_revtrans_denominator_tab,
2505 p_numerator_tab => l_revtrans_numerator_tab,
2506 p_rate_tab => l_revtrans_xchg_rate_tab,
2507 p_conversion_between => 'RC_RTC',
2508 p_cache_flag => l_cache_flag,
2509 x_status_tab => l_funding_status_tab
2510 );
2511
2512
2513 /* Copy the converted amount and attributes to OUT variables */
2514
2515 x_revtrans_rate_type := l_revtrans_rate_type_tab(1);
2516 x_revtrans_rate_date := l_revtrans_rate_date_tab(1);
2517 x_revtrans_exch_rate := l_revtrans_xchg_rate_tab(1);
2518 x_revtrans_amount := l_revtrans_amount_tab(1);
2519 x_funding_convert_status := l_funding_status_tab(1);
2520
2521 -- Log Messages
2522
2523 IF g1_debug_mode = 'Y' THEN
2524 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Project Id :' || p_project_id);
2525 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Draft revenue Num :' || p_draft_revenue_num);
2526 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Currency Code :' || p_projfunc_curr_code);
2527 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Projfunc Amount :' || p_projfunc_amount);
2528 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Currency Code :' || p_revtrans_curr_code);
2529 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Amount :' || x_revtrans_amount);
2530 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate type :' || x_revtrans_rate_type);
2531 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans rate date :' || x_revtrans_rate_date);
2532 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans exchange rate :' || x_revtrans_exch_rate);
2533 PA_MCB_INVOICE_PKG.log_message('erdl_amount_conversion: ' || 'Revtrans Amt Rejection Reason :' || l_funding_status_tab(1));
2534 END IF;
2535 IF (l_funding_status_tab(1) <> 'N') THEN
2536
2537 UPDATE pa_draft_revenues
2538 SET generation_error_flag = 'Y',
2539 transfer_rejection_reason = l_funding_status_tab(1)
2540 WHERE project_id = p_project_id
2541 AND draft_revenue_num = p_draft_revenue_num;
2542
2543 IF g1_debug_mode = 'Y' THEN
2544 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated as error in Draft Revenue : ' || SQL%ROWCOUNT);
2545 END IF;
2546
2547 END IF;
2548
2549 END IF;
2550 /* Revenue in foreign currency - End*/
2551
2552 EXCEPTION
2553 WHEN OTHERS THEN
2554 IF g1_debug_mode = 'Y' THEN
2555 PA_MCB_INVOICE_PKG.log_message('Error in Erdl_amount_conversion ' || sqlerrm);
2556 END IF;
2557 x_return_status := sqlerrm( sqlcode );
2558 /* Added for NOCOPY change */
2559 x_funding_rate_type := l_x_funding_rate_type;
2560 x_funding_rate_date := l_x_funding_rate_date;
2561 x_funding_exchange_rate := l_x_funding_exchange_rate;
2562 x_funding_amount := l_x_funding_amount;
2563 x_funding_convert_status := l_x_funding_convert_status;
2564 x_revtrans_rate_type := l_x_revtrans_rate_type;
2565 x_revtrans_rate_date := l_x_revtrans_rate_date;
2566 x_revtrans_exch_rate := l_x_revtrans_exch_rate;
2567 END erdl_amount_conversion;
2568
2569
2570 /*----------------------------------------------------------------------------------------+
2571 | Procedure : ei_fcst_amount_conversion |
2572 | Purpose : To update the pa_expenditure_items_all table
2573 | (bill transaction currency to revenue processing currency for |
2574 | forecast revenue
2575 | Parameters : |
2576 | ================================================================================== |
2577 | Name Mode Description |
2578 | ================================================================================== |
2579 | p_project_id IN project Id |
2580 | ei_id IN Expenditure item id
2581 | p_request_id IN Id for the current Run |
2582 | p_pa_date IN Project Accounting date |
2583 | x_return_status IN OUT Return status of this procedure |
2584 | x_msg_count IN OUT Error message count |
2585 | x_msg_data IN OUT Error message |
2586 | ================================================================================== |
2587 +----------------------------------------------------------------------------------------*/
2588
2589 PROCEDURE ei_fcst_amount_conversion(
2590 p_project_id IN NUMBER,
2591 p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
2592 p_request_id IN NUMBER,
2593 p_pa_date IN VARCHAR2,
2594 x_return_status IN OUT NOCOPY VARCHAR2,
2595 x_msg_count IN OUT NOCOPY NUMBER,
2596 x_msg_data IN OUT NOCOPY VARCHAR2) IS
2597
2598
2599 CURSOR ei_fcst_amt_csr (p_request_id NUMBER) IS
2600 SELECT project_id, /* 2456371 */
2601 expenditure_item_id,
2602 bill_trans_forecast_revenue,
2603 bill_trans_forecast_curr_code
2604 FROM pa_expenditure_items_all
2605 WHERE request_id = p_request_id
2606 AND revenue_distributed_flag = 'F'
2607 AND bill_trans_forecast_revenue IS NOT NULL
2608 AND forecast_revenue IS NULL
2609 ORDER BY project_id; /* 2456371 */
2610
2611
2612 l_ei_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2613 l_bill_trans_rev_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2614 l_bill_trans_adj_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
2615 l_bill_trans_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2616
2617 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp; /* 2456371 */
2618
2619 l_revproc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2620 l_revproc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2621 l_revproc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2622 l_revproc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2623 l_revproc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2624
2625
2626 l_bill_trans_proj_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
2627 l_project_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2628 l_project_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2629 l_project_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2630 l_project_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2631 l_project_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2632 l_project_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2633
2634 l_bill_trans_projfunc_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
2635 l_projfunc_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2636 l_projfunc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2637 l_projfunc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2638 l_projfunc_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2639 l_projfunc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
2640 l_projfunc_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2641
2642 l_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2643 l_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
2644 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2645 l_raw_rev_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2646
2647 l_final_error_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2648
2649
2650
2651 l_project_curr_code VARCHAR2(30);
2652 l_project_rate_date_code VARCHAR2(30);
2653 l_project_rate_date DATE;
2654 l_project_rate_type VARCHAR2(30);
2655 l_project_exchange_rate NUMBER;
2656
2657 l_projfunc_curr_code VARCHAR2(30);
2658 l_projfunc_rate_date_code VARCHAR2(30);
2659 l_projfunc_rate_date DATE;
2660 l_projfunc_rate_type VARCHAR2(30);
2661 l_projfunc_exchange_rate NUMBER;
2662
2663 l_multi_currency_billing_flag VARCHAR2(1);
2664 l_baseline_funding_flag VARCHAR2(1);
2665 l_revproc_currency_code VARCHAR2(30);
2666 l_invproc_currency_type VARCHAR2(30);
2667 l_invproc_currency_code VARCHAR2(30);
2668 l_funding_rate_date_code VARCHAR2(30);
2669 l_funding_rate_type VARCHAR2(30);
2670 l_funding_rate_date DATE;
2671 l_funding_exchange_rate NUMBER;
2672 l_return_status VARCHAR2(1);
2673 l_msg_count NUMBER;
2674 l_msg_data VARCHAR2(240);
2675
2676 l_pa_date DATE;
2677
2678 l_conversion_between VARCHAR2(6);
2679 l_cache_flag VARCHAR2(1);
2680
2681 l_project_id NUMBER ; /* 2456371 */
2682 l_prv_project_id NUMBER ;
2683
2684
2685 BEGIN
2686
2687
2688 /* Assign the dummy value into the previous project id - This for checking
2689 whenever project id changes then call the get_project_defaults API - 2456371 */
2690
2691
2692 l_prv_project_id := -9999;
2693
2694
2695
2696 /* This flag is N then the convert_amount_bulk API not cache any currency code,
2697 If the flag is Y then it cache the currency and other attributes for avoid the
2698 repeat processing. */
2699
2700 l_cache_flag := 'N';
2701
2702
2703 /* Convert the PA date from character to date */
2704
2705
2706 /*File.Date.5. Added format to the p_pa_date which was missing*/
2707 l_pa_date := TO_DATE(p_pa_date,'YYYY/MM/DD');
2708
2709
2710 IF g1_debug_mode = 'Y' THEN
2711 PA_MCB_INVOICE_PKG.log_message('Entering the procedure ei_fcst_amount_conversion');
2712 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || '------------------------------------------------');
2713 END IF;
2714
2715
2716
2717 /* 2456371 - cursor for select the expenditure details based on current request id */
2718
2719
2720 OPEN ei_fcst_amt_csr( p_request_id);
2721
2722 LOOP
2723
2724
2725
2726
2727 /*
2728 * Clear all PL/SQL table.
2729 */
2730
2731 l_ei_id_tab.delete;
2732 l_bill_trans_rev_amount_tab.delete;
2733 l_bill_trans_curr_code_tab.delete;
2734
2735 l_project_id_tab.delete; /* 2456371 */
2736
2737 l_revproc_rate_type_tab.delete;
2738 l_revproc_rate_date_tab.delete;
2739 l_revproc_exchange_rate_tab.delete;
2740 l_revproc_curr_code_tab.delete;
2741 l_revproc_amount_tab.delete;
2742
2743 l_project_curr_code_tab.delete;
2744 l_project_rate_date_tab.delete;
2745 l_project_rate_type_tab.delete;
2746 l_project_exchange_rate_tab.delete;
2747 l_project_amount_tab.delete;
2748 l_project_rev_status_tab.delete;
2749
2750
2751 l_projfunc_curr_code_tab.delete;
2752 l_projfunc_rate_date_tab.delete;
2753 l_projfunc_rate_type_tab.delete;
2754 l_projfunc_exchange_rate_tab.delete;
2755 l_projfunc_amount_tab.delete;
2756 l_projfunc_rev_status_tab.delete;
2757
2758
2759 l_user_validate_flag_tab.delete;
2760 l_denominator_tab.delete;
2761 l_numerator_tab.delete;
2762 l_raw_rev_status_tab.delete;
2763
2764
2765 /* Fetching the expenditure bill transaction value */
2766
2767
2768 FETCH ei_fcst_amt_csr BULK COLLECT
2769 INTO l_project_id_tab, /* 2456371 */
2770 l_ei_id_tab,
2771 l_bill_trans_rev_amount_tab,
2772 l_bill_trans_curr_code_tab LIMIT 100;
2773
2774
2775 IF g1_debug_mode = 'Y' THEN
2776 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Record Count :' || l_ei_id_tab.COUNT);
2777 END IF;
2778
2779 IF (l_ei_id_tab.COUNT = 0 ) THEN
2780
2781 EXIT;
2782
2783 ELSE
2784
2785
2786 /* Initialize the Array variables to use convert_amount_bulk API */
2787
2788
2789 FOR I in 1 .. l_ei_id_tab.COUNT
2790 LOOP
2791
2792 l_user_validate_flag_tab(I) := 'Y';
2793 l_revproc_amount_tab(I) := NULL;
2794 l_denominator_tab(I) := NULL;
2795 l_numerator_tab(I) := NULL;
2796 l_raw_rev_status_tab(I) := NULL;
2797
2798
2799
2800 /* Assign the project Id from PL/SQL table into non array variable for
2801 pass into the API get_project_defaults - 2456371 */
2802
2803 l_project_id := l_project_id_tab(i);
2804
2805
2806 /* 2456371 - Checking for the Previous and current project Id - If bott are different then
2807 call the api to get the default conversion attributes - If same then not necessary to call
2808 the API to get the conversion attributes */
2809
2810
2811 IF (l_project_id <> l_prv_project_id) THEN
2812
2813
2814
2815 IF g1_debug_mode = 'Y' THEN
2816 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Project Id :' || l_project_id);
2817 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Calling Procedure get_project_defaults');
2818 END IF;
2819
2820 -- Get the Project Level Defaults
2821
2822 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
2823 p_project_id => l_project_id,
2824 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
2825 x_baseline_funding_flag => l_baseline_funding_flag,
2826 x_revproc_currency_code => l_revproc_currency_code,
2827 x_invproc_currency_type => l_invproc_currency_type,
2828 x_invproc_currency_code => l_invproc_currency_code,
2829 x_project_currency_code => l_project_curr_code,
2830 x_project_bil_rate_date_code => l_project_rate_date_code,
2831 x_project_bil_rate_type => l_project_rate_type,
2832 x_project_bil_rate_date => l_project_rate_date,
2833 x_project_bil_exchange_rate => l_project_exchange_rate,
2834 x_projfunc_currency_code => l_projfunc_curr_code,
2835 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
2836 x_projfunc_bil_rate_type => l_projfunc_rate_type,
2837 x_projfunc_bil_rate_date => l_projfunc_rate_date,
2838 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
2839 x_funding_rate_date_code => l_funding_rate_date_code,
2840 x_funding_rate_type => l_funding_rate_type,
2841 x_funding_rate_date => l_funding_rate_date,
2842 x_funding_exchange_rate => l_funding_exchange_rate,
2843 x_return_status => l_return_status,
2844 x_msg_count => l_msg_count,
2845 x_msg_data => l_msg_data);
2846
2847
2848 END IF;
2849
2850
2851 IF g1_debug_mode = 'Y' THEN
2852 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'after calling Procedure get_project_defaults');
2853 END IF;
2854
2855
2856 /* Copy the project and project attributed into array variables */
2857
2858
2859 l_project_curr_code_tab(I) := l_project_curr_code;
2860 l_project_rate_type_tab(I) := l_project_rate_type;
2861 l_project_rate_date_tab(I) := l_project_rate_date;
2862 l_project_exchange_rate_tab(I) := l_project_exchange_rate;
2863
2864 l_projfunc_curr_code_tab(I) := l_projfunc_curr_code;
2865 l_projfunc_rate_type_tab(I) := l_projfunc_rate_type;
2866 l_projfunc_rate_date_tab(I) := l_projfunc_rate_date;
2867 l_projfunc_exchange_rate_tab(I) := l_projfunc_exchange_rate;
2868
2869
2870 /* If revenue processing currency and project currency both are same then
2871 copy the project attributes to revenue processing attributes */
2872
2873
2874 IF (l_revproc_currency_code = l_project_curr_code) THEN
2875
2876
2877 l_revproc_curr_code_tab(I) := l_project_curr_code;
2878 l_revproc_rate_type_tab(I) := l_project_rate_type;
2879 l_revproc_rate_date_tab(I) := l_project_rate_date;
2880 l_revproc_exchange_rate_tab(I) := l_project_exchange_rate;
2881
2882
2883 /* If rate date code = 'PA_INVOICE_DATE' then get the PA DATE and
2884 assign to the revenue processing rate date */
2885
2886 IF (l_project_rate_date_code = 'PA_INVOICE_DATE') THEN
2887
2888 l_revproc_rate_date_tab(I) := l_pa_date;
2889
2890 END IF;
2891
2892 /* If revenue processing currency and project functional currency both are same then
2893 copy the project functional attributes to revenue processing attributes */
2894
2895
2896 ELSIF (l_revproc_currency_code = l_projfunc_curr_code) THEN
2897
2898 l_revproc_curr_code_tab(I) := l_projfunc_curr_code;
2899 l_revproc_rate_type_tab(I) := l_projfunc_rate_type;
2900 l_revproc_rate_date_tab(I) := l_projfunc_rate_date;
2901 l_revproc_exchange_rate_tab(I) := l_projfunc_exchange_rate;
2902
2903 /* If rate date code = 'PA_INVOICE_DATE' then get the PA DATE and
2904 assign to the revenue processing rate date */
2905
2906
2907 IF (l_projfunc_rate_date_code = 'PA_INVOICE_DATE') THEN
2908
2909 l_revproc_rate_date_tab(I) := l_pa_date;
2910
2911 END IF;
2912
2913
2914 END IF;
2915
2916
2917
2918 /* 2456371 : Assign the project id to the previous project Id variable */
2919
2920 l_prv_project_id := l_project_id;
2921
2922
2923 /* Print the currency attribute value */
2924
2925 IF g1_debug_mode = 'Y' THEN
2926 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
2927 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Rate Type :' || l_revproc_rate_type_tab(i));
2928 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'RevProc Rate Date :' || l_revproc_rate_date_tab(i));
2929 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Xchg Rate :' || l_revproc_exchange_rate_tab(i));
2930 END IF;
2931
2932
2933 END LOOP;
2934
2935
2936
2937 /* Converting Bill Trans Raw revenue to Raw revenue (Revenue processing currency) */
2938
2939 IF g1_debug_mode = 'Y' THEN
2940 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Calling the procedure convert_amount_bulk for Revenue amount');
2941 END IF;
2942
2943
2944 /* Passing the param (two currency code) to the convert_amount_bulk API, If any conversion fails
2945 then the API concatenate the code with error message */
2946
2947 l_conversion_between := 'BTC_PF';
2948
2949
2950 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
2951 p_from_currency_tab => l_bill_trans_curr_code_tab,
2952 p_to_currency_tab => l_revproc_curr_code_tab,
2953 p_conversion_date_tab => l_revproc_rate_date_tab,
2954 p_conversion_type_tab => l_revproc_rate_type_tab,
2955 p_amount_tab => l_bill_trans_rev_amount_tab,
2956 p_user_validate_flag_tab => l_user_validate_flag_tab,
2957 p_converted_amount_tab => l_revproc_amount_tab,
2958 p_denominator_tab => l_denominator_tab,
2959 p_numerator_tab => l_numerator_tab,
2960 p_rate_tab => l_revproc_exchange_rate_tab,
2961 p_conversion_between => l_conversion_between,
2962 p_cache_flag => l_cache_flag,
2963 x_status_tab => l_raw_rev_status_tab
2964 );
2965
2966
2967
2968 IF g1_debug_mode = 'Y' THEN
2969 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Project Id : ' || p_project_id);
2970 END IF;
2971
2972
2973 /* FOR I in 1 .. l_ei_id_tab.COUNT
2974 LOOP
2975
2976
2977
2978 -- Log Messages for EI Converted Amounts
2979
2980 IF g1_debug_mode = 'Y' THEN
2981 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Expenditure Item Id :' || l_ei_id_tab(i));
2982 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Bill Trans Currency Code :' || l_bill_trans_curr_code_tab(i));
2983 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Bill Trans Raw revenue :' || l_bill_trans_rev_amount_tab(i));
2984 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Convert RevProc Amount :' || l_revproc_amount_tab(i));
2985 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Currency Code :' || l_revproc_curr_code_tab(i));
2986 PA_MCB_INVOICE_PKG.log_message('ei_fcst_amount_conversion: ' || 'Revproc Amt Rejection Reason :' || l_raw_rev_status_tab(i));
2987 END IF;
2988
2989
2990
2991 END LOOP; */
2992
2993
2994
2995 /* Updating the converted amount to the Expenditure Item table.
2996 Converted amount column : raw_revenue, adjusted_revenue, bill_rate, adjusted_rate
2997 Other columns : Initialize when conversion fails and marking revenue
2998 distributed flag to 'N */
2999
3000
3001 FORALL I IN 1 ..l_ei_id_tab.COUNT
3002 UPDATE pa_expenditure_items_all
3003 SET forecast_revenue =
3004 DECODE(l_raw_rev_status_tab(i), 'N', l_revproc_amount_tab(i), NULL),
3005 projfunc_fcst_rate_type = l_revproc_rate_type_tab(i),
3006 projfunc_fcst_rate_date = l_revproc_rate_date_tab(i),
3007 projfunc_fcst_exchange_rate = l_revproc_exchange_rate_tab(i),
3008 rev_dist_rejection_code =
3009 DECODE(l_raw_rev_status_tab(i), 'N',NULL, l_raw_rev_status_tab(i))
3010 WHERE expenditure_item_id = l_ei_id_tab(i);
3011
3012 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in EI table for forecast revenue: ' || SQL%ROWCOUNT);
3013
3014
3015 END IF; /* l_ei_id_tab.COUNT <> 0 */
3016
3017 EXIT WHEN ei_fcst_amt_csr%NOTFOUND;
3018
3019
3020 END LOOP;
3021
3022 CLOSE ei_fcst_amt_csr;
3023
3024
3025 EXCEPTION
3026 WHEN OTHERS THEN
3027
3028 IF g1_debug_mode = 'Y' THEN
3029 PA_MCB_INVOICE_PKG.log_message('Error in Ei_fcst_amount_conversion ' || sqlerrm);
3030 END IF;
3031
3032 x_return_status := sqlerrm( sqlcode );
3033
3034 END ei_fcst_amount_conversion;
3035
3036
3037
3038 PROCEDURE log_message (p_log_msg IN VARCHAR2) IS
3039 BEGIN
3040 --pa_debug.write_file ('LOG',to_char(sysdate, 'DD-MON-YYYY HH:MI:SS ')||p_log_msg);
3041 pa_debug.write_file ('LOG','MCB.....' || p_log_msg);
3042 NULL;
3043 END log_message;
3044
3045
3046 PROCEDURE Init (P_DEBUG_MODE VARCHAR2) IS
3047 BEGIN
3048 G_LAST_UPDATE_LOGIN := fnd_global.login_id;
3049 G_REQUEST_ID := fnd_global.conc_request_id;
3050 G_PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
3051 G_PROGRAM_ID := fnd_global.conc_program_id;
3052 G_LAST_UPDATED_BY := fnd_global.user_id;
3053 G_CREATED_BY := fnd_global.user_id;
3054 G_DEBUG_MODE := P_DEBUG_MODE;
3055 pa_debug.init_err_stack ('Revenue Generation');
3056 pa_debug.set_process(
3057 x_process => 'PLSQL',
3058 x_debug_mode => G_DEBUG_MODE);
3059
3060
3061 pa_debug.G_Err_Stage :=' Start PLSQL Message ';
3062
3063 IF g1_debug_mode = 'Y' THEN
3064 PA_MCB_REVENUE_PKG.log_message(pa_debug.G_Err_Stage);
3065 END IF;
3066
3067 END Init;
3068
3069
3070 /*----------------------------------------------------------------------------------------+
3071 | Procedure : RTC_UBR_UER_CALC |
3072 | Purpose : To compute transaction level ie, draft revenue level UBR/UER values in |
3073 | Revenue transaction currency. |
3074 | Parameters : |
3075 | ================================================================================== |
3076 | Name Mode Description |
3077 | ================================================================================== |
3078 | P_PFC_REV_AMOUNT IN Total revenue amount for a revenue in PFC |
3079 | P_REVTRANS_AMOUNT IN Total revenue amount for a revenue in RTC |
3080 | P_PROJFUNC_UBR IN UBR amount in project functional currency |
3081 | P_PROJFUNC_UER IN UBR amount in project functional currency |
3082 | P_UBR_CORR IN UBR correction amt in proj functional currency|
3083 | P_UER_CORR IN UER correction amt in proj functional currency|
3084 | P_REVTRANS_UBR OUT NOCOPY UBR amount in revenue transaction currency |
3085 | P_REVTRANS_UER OUT NOCOPY UER amount in revenue transaction currency |
3086 | X_RETURN_STATUS OUT NOCOPY Return status |
3087 | X_MSG_COUNT OUT NOCOPY Error messages count |
3088 | X_MSG_DATA OUT NOCOPY Error message |
3089 | ================================================================================== |
3090 +----------------------------------------------------------------------------------------*/
3091 PROCEDURE RTC_UBR_UER_CALC(
3092 P_PFC_REV_AMOUNT IN NUMBER,
3093 P_REVTRANS_AMOUNT IN NUMBER,
3094 P_PROJFUNC_UBR IN NUMBER,
3095 P_PROJFUNC_UER IN NUMBER,
3096 P_UBR_CORR IN NUMBER,
3097 P_UER_CORR IN NUMBER,
3098 P_REVTRANS_UBR OUT NOCOPY VARCHAR,
3099 P_REVTRANS_UER OUT NOCOPY VARCHAR,
3100 X_RETURN_STATUS OUT NOCOPY VARCHAR,
3101 X_MSG_COUNT OUT NOCOPY NUMBER,
3102 X_MSG_DATA OUT NOCOPY VARCHAR)
3103 IS
3104
3105 l_rtc_ubr_corr VARCHAR2(30);
3106 l_rtc_uer_corr VARCHAR2(30);
3107
3108 BEGIN
3109 X_RETURN_STATUS := NULL;
3110 X_MSG_COUNT := 0;
3111 X_MSG_DATA := NULL;
3112
3113 -- Compute ubr,uer correction amount in revenue transaction currency from project functional amounts.
3114
3115 l_rtc_ubr_corr := substr(to_char((P_REVTRANS_AMOUNT / P_PFC_REV_AMOUNT ) * P_UBR_CORR), 1, 30);
3116 l_rtc_uer_corr := substr(to_char((P_REVTRANS_AMOUNT / P_PFC_REV_AMOUNT ) * P_UER_CORR), 1, 30);
3117
3118
3119 -- Calculate UBR in revenue transaction currency as projfunc UBR multiplied by the ratio of revenue in
3120 -- project functional currency and revenue in revenue transaction currency.
3121 /*Commented the following for bug 14642473*/
3122 -- P_Revtrans_Ubr := substr(to_char(((P_RevTrans_Amount / P_Pfc_Rev_Amount ) * P_Projfunc_Ubr) + to_number(l_rtc_ubr_corr)), 1, 30);
3123 /*Comment end for bug 14642473*/
3124 -- Calculate UER in revenue transaction currency as projfunc UER multiplied by the ratio of revenue in
3125 -- project functional currency and revenue in revenue transaction currency.
3126
3127 P_Revtrans_Uer := substr(to_char(((P_RevTrans_Amount / P_Pfc_Rev_Amount ) * P_Projfunc_Uer) + to_number(l_rtc_uer_corr)), 1, 30);
3128 /*Added the following for bug 14642473*/
3129 P_Revtrans_Ubr := P_RevTrans_Amount + P_Revtrans_Uer;
3130 /*Added end for bug 14642473*/
3131 EXCEPTION
3132 WHEN ZERO_DIVIDE THEN
3133 P_REVTRANS_UBR := 0;
3134 P_REVTRANS_UER := 0;
3135
3136 WHEN OTHERS THEN
3137 P_REVTRANS_UBR := NULL;
3138 P_REVTRANS_UER := NULL;
3139 raise;
3140 END RTC_UBR_UER_CALC;
3141
3142
3143 /*Added the below procedure for 14642473*/
3144 /*----------------------------------------------------------------------------------------+
3145 | Procedure : RDL_AMOUNT_CORRECTION |
3146 | Purpose : To correct the differential amount which are caused due to the rounding|
3147 | |
3148 | Parameters : |
3149 | ================================================================================== |
3150 | Name Mode Description |
3151 | ================================================================================== |
3152 | P_PROJECT_ID IN Project ID |
3153 | P_REQUEST_ID IN Request ID |
3154 | ================================================================================== |
3155 +----------------------------------------------------------------------------------------*/
3156 PROCEDURE RDL_AMOUNT_CORRECTION( P_PROJECT_ID IN NUMBER,
3157 P_REQUEST_ID IN NUMBER)
3158 IS
3159
3160 l_draft_revenue_num NUMBER;
3161 l_exp_id NUMBER;
3162 l_exp_line_num NUMBER;
3163 l_amount_value NUMBER;
3164 l_diff_amount NUMBER;
3165
3166 BEGIN
3167
3168 SELECT draft_revenue_num,
3169 EI,
3170 line_num,
3171 amt_value,
3172 diff
3173 INTO
3174 l_draft_revenue_num,
3175 l_exp_id,
3176 l_exp_line_num,
3177 l_amount_value,
3178 l_diff_amount
3179 FROM
3180 (SELECT draft_revenue_num,
3181 amt_value,
3182 EI,
3183 line_num,
3184 sgn,
3185 rank()over( order by amt_value) AS rnk,
3186 lead(amt_value)over(order by amt_value)- amt_value diff
3187 FROM
3188 (SELECT draft_revenue_num,
3189 SUM(NVL(DECODE(SIGN(RDL.REVTRANS_AMOUNT),1,RDL.REVTRANS_AMOUNT),0))amt_value,
3190 MAX(expenditure_item_id) EI,max(line_num)line_num,
3191 'POSITIVE' sgn
3192 FROM pa_cust_rev_dist_lines_all rdl
3193 WHERE rdl.project_id = P_PROJECT_ID
3194 AND rdl.request_id = P_REQUEST_ID
3195 AND REVTRANS_AMOUNT >0
3196 GROUP BY draft_revenue_num
3197 UNION
3198 SELECT draft_revenue_num,
3199 SUM(NVL(DECODE(SIGN(RDL.REVTRANS_AMOUNT),-1,RDL.REVTRANS_AMOUNT),0))*-1 amt_value,
3200 MAX(expenditure_item_id) EI, max(line_num)line_num,
3201 'NEGATIVE' sgn
3202 FROM pa_cust_rev_dist_lines_all rdl
3203 WHERE rdl.project_id = P_PROJECT_ID
3204 AND rdl.request_id = P_REQUEST_ID
3205 AND REVTRANS_AMOUNT <0
3206 GROUP BY draft_revenue_num
3207 ) order by amt_value
3208 )
3209 WHERE rnk=1 ;
3210 IF g1_debug_mode = 'Y' THEN
3211 PA_MCB_INVOICE_PKG.log_message('Entered into RDL_AMOUNT_CORRECTION ');
3212 PA_MCB_INVOICE_PKG.log_message('Entered into RDL_AMOUNT_CORRECTION-l_diff_amount -'||l_diff_amount);
3213 PA_MCB_INVOICE_PKG.log_message('Entered into RDL_AMOUNT_CORRECTION-P_REQUEST_ID -'||P_REQUEST_ID);
3214 PA_MCB_INVOICE_PKG.log_message('Entered into RDL_AMOUNT_CORRECTION-l_draft_revenue_num -'||l_draft_revenue_num);
3215 PA_MCB_INVOICE_PKG.log_message('Entered into RDL_AMOUNT_CORRECTION-l_exp_id -'||l_exp_id);
3216 PA_MCB_INVOICE_PKG.log_message('Entered into RDL_AMOUNT_CORRECTION-l_exp_line_num -'||l_exp_line_num);
3217 END IF;
3218 UPDATE PA_CUST_REV_DIST_LINES_ALL
3219 SET REVTRANS_AMOUNT= REVTRANS_AMOUNT + l_diff_amount
3220 WHERE PROJECT_ID = P_PROJECT_ID
3221 AND REQUEST_ID = P_REQUEST_ID
3222 AND DRAFT_REVENUE_NUM=l_draft_revenue_num
3223 and expenditure_item_id=l_exp_id
3224 and line_num=l_exp_line_num ;
3225
3226 EXCEPTION
3227 WHEN OTHERS THEN
3228 IF g1_debug_mode = 'Y' THEN
3229 PA_MCB_INVOICE_PKG.log_message('Error in RDL_AMOUNT_CORRECTION ' || sqlerrm);
3230 END IF;
3231 END RDL_AMOUNT_CORRECTION;
3232 /*Added end procedure for 14642473*/
3233
3234
3235 END PA_MCB_REVENUE_PKG;