[Home] [Help]
PACKAGE BODY: APPS.PA_MCB_INVOICE_PKG
Source
1 PACKAGE BODY PA_MCB_INVOICE_PKG AS
2 /* $Header: PAXMCIUB.pls 120.13.12020000.3 2013/02/21 12:21:21 paljain ship $ */
3
4 -- Procedure to
5 -- Convert the Bill Transaction to Invoice Processing
6 -- Bill Transaction to Project Functional
7 -- Bill Transaction to Project
8 -- Update pa_events table
9
10 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
11
12 PROCEDURE Event_Convert_amount_bulk (
13 p_agreement_id IN NUMBER DEFAULT 0,
14 p_project_id IN NUMBER,
15 p_request_id IN NUMBER,
16 p_task_id IN PA_PLSQL_DATATYPES.NumTabTyp ,
17 p_event_num IN PA_PLSQL_DATATYPES.NumTabTyp,
18 p_bill_trans_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
19 p_bill_trans_bill_amount IN PA_PLSQL_DATATYPES.Char30TabTyp,
20 p_invproc_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
21 p_invproc_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
22 p_invproc_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
23 p_invproc_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
24 p_invproc_bill_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
25 p_project_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
26 p_project_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
27 p_project_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
28 p_project_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
29 p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
30 p_projfunc_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
31 p_projfunc_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
32 p_projfunc_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
33 p_funding_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
34 p_funding_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
35 p_funding_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
36 p_shared_funds_consumption IN NUMBER, /* Federal */
37 p_completion_date IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Federal */
38 x_status_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
39 x_return_status IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
40
41 tmp_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
42 tmp_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
43 tmp_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
44 tmp_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
45
46 tmp_status_project_tab PA_PLSQL_DATATYPES.Char30TabTyp;
47 tmp_status_projfunc_tab PA_PLSQL_DATATYPES.Char30TabTyp;
48 tmp_status_funding_tab PA_PLSQL_DATATYPES.Char30TabTyp;
49 tmp_status_invproc_tab PA_PLSQL_DATATYPES.Char30TabTyp;
50 tmp_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
51
52 tmp_project_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
53 tmp_projfunc_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
54 tmp_funding_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
55 tmp_invproc_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
56 tmp_bill_trans_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
57
58 tmp_project_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
59 tmp_projfunc_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
60 tmp_funding_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
61 tmp_invproc_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
62
63 tmp_invproc_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
64 tmp_invproc_currency_code PA_PLSQL_DATATYPES.Char30TabTyp;
65 tmp_funding_currency_code PA_PLSQL_DATATYPES.Char30TabTyp;
66
67 tmp_project_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
68 tmp_projfunc_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
69 tmp_funding_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
70 tmp_invproc_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
71
72
73 tmp_invproc_currency_type VARCHAR2(30);
74
75 l_multi_currency_billing_flag VARCHAR2(1);
76 l_baseline_funding_flag VARCHAR2(1);
77 l_revproc_currency_code VARCHAR2(30);
78 l_invproc_currency_code VARCHAR2(30);
79 l_project_currency_code VARCHAR2(30);
80 l_project_rate_date_code VARCHAR2(30);
81 l_project_rate_type VARCHAR2(30);
82 l_project_rate_date DATE;
83 l_project_exchange_rate NUMBER;
84 l_projfunc_currency_code VARCHAR2(30);
85 l_projfunc_rate_date_code VARCHAR2(30);
86 l_projfunc_rate_type VARCHAR2(30);
87 l_projfunc_rate_date DATE;
88 l_projfunc_exchange_rate NUMBER;
89 l_funding_rate_date_code VARCHAR2(30);
90 l_funding_rate_type VARCHAR2(30);
91 l_funding_rate_date DATE;
92 l_funding_exchange_rate NUMBER;
93 l_funding_currency_code VARCHAR2(30);
94 l_return_status VARCHAR2(1);
95 l_msg_count NUMBER;
96 l_msg_data VARCHAR2(240);
97
98
99 l_request_id NUMBER:= fnd_global.conc_request_id;
100 l_program_id NUMBER:= fnd_global.conc_program_id;
101 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
102 l_program_update_date DATE := sysdate;
103 l_last_update_date DATE := sysdate;
104 l_last_updated_by NUMBER:= fnd_global.user_id;
105 l_last_update_login NUMBER:= fnd_global.login_id;
106
107 /* Federal Changes */
108
109 tmp_completion_date PA_PLSQL_DATATYPES.DateTabTyp;
110 l_agreement_start_date DATE;
111 l_agreement_exp_date DATE;
112
113
114 BEGIN
115
116 IF g1_debug_mode = 'Y' THEN
117 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk');
118 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Call PA_MULTI_CURRENCY_BILLING.get_project_defaults');
119 END IF;
120
121
122 IF g1_debug_mode = 'Y' THEN
123 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: Agreement Id :' || p_agreement_id );
124 END IF;
125
126
127
128
129 -- Get the Agreement Currency Code
130 -- For Write-on Events Agreement id will be there
131 -- For Reg Events Agreement Id will not be there.
132
133 /* Federal Changes : Adding agreement start and end date */
134
135 IF ( NVL(p_agreement_id,0) <> 0 ) THEN
136
137 SELECT agreement_currency_code,
138 nvl(start_date, to_date('01/01/1952','DD/MM/YYYY')),
139 nvl(expiration_date, sysdate)
140 INTO l_funding_currency_code,
141 l_agreement_start_date,
142 l_agreement_exp_date
143 FROM pa_agreements_all
144 WHERE agreement_id =p_agreement_id;
145
146
147 END IF;
148
149
150 IF g1_debug_mode = 'Y' THEN
151 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Call PA_MULTI_CURRENCY_BILLING.get_project_defaults');
152 END IF;
153
154 -- Get the Project Level Defaults
155 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
156 p_project_id => p_project_id,
157 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
158 x_baseline_funding_flag => l_baseline_funding_flag,
159 x_revproc_currency_code => l_revproc_currency_code,
160 x_invproc_currency_type => tmp_invproc_currency_type,
161 x_invproc_currency_code => l_invproc_currency_code,
162 x_project_currency_code => l_project_currency_code,
163 x_project_bil_rate_date_code => l_project_rate_date_code,
164 x_project_bil_rate_type => l_project_rate_type,
165 x_project_bil_rate_date => l_project_rate_date,
166 x_project_bil_exchange_rate => l_project_exchange_rate,
167 x_projfunc_currency_code => l_projfunc_currency_code,
168 x_projfunc_bil_rate_date_code => l_projfunc_rate_date_code,
169 x_projfunc_bil_rate_type => l_projfunc_rate_type,
170 x_projfunc_bil_rate_date => l_projfunc_rate_date,
171 x_projfunc_bil_exchange_rate => l_projfunc_exchange_rate,
172 x_funding_rate_date_code => l_funding_rate_date_code,
173 x_funding_rate_type => l_funding_rate_type,
174 x_funding_rate_date => l_funding_rate_date,
175 x_funding_exchange_rate => l_funding_exchange_rate,
176 x_return_status => l_return_status,
177 x_msg_count => l_msg_count,
178 x_msg_data => l_msg_data);
179
180 IF g1_debug_mode = 'Y' THEN
181 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Events To Process : ' || p_event_num.count);
182 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'IPC Type : ' || tmp_invproc_currency_type);
183 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'IPC Code : ' || l_invproc_currency_code);
184 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'PC Code : ' || l_project_currency_code);
185 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'PFC Code : ' || l_projfunc_currency_code);
186 END IF;
187
188 -- ARRAY is empty, no process required
189
190 IF (p_event_num.exists(p_event_num.first)) THEN
191
192 -- Convert the data types
193
194 FOR i IN p_event_num.FIRST..p_event_num.LAST LOOP
195
196 IF g1_debug_mode = 'Y' THEN
197 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Project Rate Date : ' || p_project_rate_date(i));
198 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Projfunc Rate Date: ' || p_projfunc_rate_date(i));
199 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Funding Rate Date : ' || p_funding_rate_date(i));
200 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Invproc Rate Date : ' || p_invproc_rate_date(i));
201 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Project Rate : ' || p_project_exchange_rate(i));
202 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Projfunc Rate : ' || p_projfunc_exchange_rate(i));
203 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Funding Rate : ' || p_funding_exchange_rate(i));
204 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'IPC Rate : ' || p_invproc_exchange_rate(i));
205 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'BTC Amount : ' || p_bill_trans_bill_amount(i));
206 /* Federal Changes */
207 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Evt Cmplete Date: ' || p_completion_date(i));
208 END IF;
209
210 tmp_project_exchange_rate(i) := TO_NUMBER(p_project_exchange_rate(i));
211 tmp_projfunc_exchange_rate(i):= TO_NUMBER(p_projfunc_exchange_rate(i));
212 tmp_funding_exchange_rate(i) := TO_NUMBER(p_funding_exchange_rate(i));
213 tmp_invproc_exchange_rate(i) := TO_NUMBER(p_invproc_exchange_rate(i));
214
215 /* R12 : ATG changes : added date format */
216 tmp_project_rate_date(i) := TO_DATE(p_project_rate_date(i), 'YYYY/MM/DD');
217 tmp_projfunc_rate_date(i) := TO_DATE(p_projfunc_rate_date(i), 'YYYY/MM/DD');
218 tmp_funding_rate_date(i) := TO_DATE(p_funding_rate_date(i),'YYYY/MM/DD');
219 IF g1_debug_mode = 'Y' THEN
220 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Before Assign IPC date to ');
221 END IF;
222
223 tmp_invproc_rate_date(i) := TO_DATE(p_invproc_rate_date(i),'YYYY/MM/DD');
224
225 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'first .....');
226
227 tmp_denominator_tab(i) :=0;
228 tmp_numerator_tab(i) :=0;
229 tmp_rate_tab(i) :=0;
230 tmp_user_validate_flag_tab(i):='N';
231 tmp_status_project_tab(i) :='N';
232 tmp_status_projfunc_tab(i) :='N';
233 tmp_status_funding_tab(i) := 'N';
234 tmp_status_invproc_tab(i) := 'N';
235 tmp_status_tab(i) := 'N';
236
237 tmp_project_bill_amount(i) :=0;
238 tmp_projfunc_bill_amount(i) :=0;
239 tmp_invproc_bill_amount(i) :=0;
240 tmp_funding_bill_amount(i) :=0;
241
242 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'two .....');
243
244 tmp_bill_trans_bill_amount(i):= TO_NUMBER(p_bill_trans_bill_amount(i));
245 tmp_invproc_currency_code (i) := l_invproc_currency_code;
246 tmp_funding_currency_code(i) := l_funding_currency_code;
247
248 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'three .....');
249
250 IF tmp_invproc_currency_type = 'FUNDING_CURRENCY' THEN
251
252 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'three one .....');
253 tmp_invproc_rate_type(i) := p_funding_rate_type(i);
254 tmp_invproc_rate_date(i) := tmp_funding_rate_date(i);
255 tmp_invproc_exchange_rate(i) := tmp_funding_exchange_rate(i);
256 tmp_invproc_currency_code(i) := l_invproc_currency_code;
257 ELSIF tmp_invproc_currency_type = 'PROJECT_CURRENCY' THEN
258 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'three two .....');
259
260 tmp_invproc_rate_type(i) := p_project_rate_type(i);
261 tmp_invproc_rate_date(i) := tmp_project_rate_date(i);
262 tmp_invproc_exchange_rate(i) := tmp_project_exchange_rate(i);
263 tmp_invproc_currency_code(i) := p_project_currency_code(i);
264 ELSIF tmp_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
265 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'three three .....');
266 tmp_invproc_rate_type(i) := p_projfunc_rate_type(i);
267 tmp_invproc_rate_date(i) := tmp_projfunc_rate_date(i);
268 tmp_invproc_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
269 tmp_invproc_currency_code(i) := p_projfunc_currency_code(i);
270 END IF;
271
272 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'four .....');
273 /* Federal Changes */
274
275 /* tmp_completion_date(i) := TO_DATE(p_completion_date(i), 'YYYY/MM/DD') ; */
276
277
278 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'five .....');
279
280 END LOOP;
281
282 -- Convert the bill transaction to Project functional currency
283 IF g1_debug_mode = 'Y' THEN
284 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Convert Bill Transaction To Project functional ' );
285 END IF;
286
287 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
288 p_from_currency_tab => p_bill_trans_currency_code,
289 p_to_currency_tab => p_projfunc_currency_code,
290 p_conversion_date_tab => tmp_projfunc_rate_date,
291 p_conversion_type_tab => p_projfunc_rate_type,
292 p_amount_tab => tmp_bill_trans_bill_amount,
293 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
294 p_converted_amount_tab => tmp_projfunc_bill_amount,
295 p_denominator_tab => tmp_denominator_tab,
296 p_numerator_tab => tmp_numerator_tab,
297 p_rate_tab => tmp_projfunc_exchange_rate,
298 x_status_tab => tmp_status_projfunc_tab,
299 p_conversion_between => 'BTC_PF',
300 p_cache_flag => 'N');
301
302 tmp_denominator_tab.delete;
303 tmp_numerator_tab.delete;
304
305 if (l_project_currency_code = l_projfunc_currency_code ) then
306
307 IF g1_debug_mode = 'Y' THEN
308 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Proj curr = Proj func currency ..Copy ' );
309 END IF;
310
311 FOR i IN tmp_status_projfunc_tab.FIRST..tmp_status_projfunc_tab.LAST LOOP
312
313 tmp_project_rate_date(i) := tmp_projfunc_rate_date(i);
314 p_project_rate_type(i) := p_projfunc_rate_type(i);
315 tmp_project_bill_amount(i) := tmp_projfunc_bill_amount(i);
316 tmp_project_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
317 tmp_status_project_tab(i) := tmp_status_projfunc_tab(i);
318
319 END LOOP;
320
321 else
322 -- Convert the bill transaction to Project currency
323
324 IF g1_debug_mode = 'Y' THEN
325 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Convert Bill Transaction To Project ' );
326 END IF;
327
328 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
329 p_from_currency_tab => p_bill_trans_currency_code,
330 p_to_currency_tab => p_project_currency_code,
331 p_conversion_date_tab => tmp_project_rate_date,
332 p_conversion_type_tab => p_project_rate_type,
333 p_amount_tab => tmp_bill_trans_bill_amount,
334 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
335 p_converted_amount_tab => tmp_project_bill_amount,
336 p_denominator_tab => tmp_denominator_tab,
337 p_numerator_tab => tmp_numerator_tab,
338 p_rate_tab => tmp_project_exchange_rate,
339 x_status_tab => tmp_status_project_tab,
340 p_conversion_between => 'BTC_PC',
341 p_cache_flag =>'N');
342
343 tmp_denominator_tab.delete;
344 tmp_numerator_tab.delete;
345 end if;
346
347
348 -- Convert the bill transaction to Funding currency
349
350 IF NVL(p_agreement_id,0) <> 0 THEN
351
352 -- This will be done only for WRITE ON events
353
354 if l_funding_currency_code = l_projfunc_currency_code then
355
356 IF g1_debug_mode = 'Y' THEN
357 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Write on - funding curr = Proj func currency ..Copy ' );
358 END IF;
359
360 FOR i IN tmp_status_projfunc_tab.FIRST..tmp_status_projfunc_tab.LAST LOOP
361
362 tmp_funding_rate_date(i) := tmp_projfunc_rate_date(i);
363 p_funding_rate_type(i) := p_projfunc_rate_type(i);
364 tmp_funding_bill_amount(i) := tmp_projfunc_bill_amount(i);
365 tmp_funding_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
366 tmp_status_funding_tab(i) := tmp_status_projfunc_tab(i);
367
368 END LOOP;
369
370 elsif l_funding_currency_code = l_project_currency_code then
371
372 IF g1_debug_mode = 'Y' THEN
373 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Write on - funding curr = Proj currency ..Copy ' );
374 END IF;
375
376 FOR i IN tmp_status_project_tab.FIRST..tmp_status_project_tab.LAST LOOP
377
378 tmp_funding_rate_date(i) := tmp_project_rate_date(i);
379 p_funding_rate_type(i) := p_project_rate_type(i);
380 tmp_funding_bill_amount(i) := tmp_project_bill_amount(i);
381 tmp_funding_exchange_rate(i) := tmp_project_exchange_rate(i);
382 tmp_status_funding_tab(i) := tmp_status_project_tab(i);
383
384 END LOOP;
385
386 else
387
388 IF g1_debug_mode = 'Y' THEN
389 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Convert Bill Transaction To Funding Write-ON ' );
390 END IF;
391
392 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
393 p_from_currency_tab => p_bill_trans_currency_code,
394 p_to_currency_tab => tmp_funding_currency_code,
395 p_conversion_date_tab => tmp_funding_rate_date,
396 p_conversion_type_tab => p_funding_rate_type,
397 p_amount_tab => tmp_bill_trans_bill_amount,
398 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
399 p_converted_amount_tab => tmp_funding_bill_amount,
400 p_denominator_tab => tmp_denominator_tab,
401 p_numerator_tab => tmp_numerator_tab,
402 p_rate_tab => tmp_funding_exchange_rate,
403 x_status_tab => tmp_status_funding_tab,
404 p_conversion_between => 'BTC_FC',
405 p_cache_flag => 'N');
406
407 tmp_denominator_tab.delete;
408 tmp_numerator_tab.delete;
409 end if;
410
411 END IF;
412
413
414 IF l_invproc_currency_code = l_projfunc_currency_code THEN
415
416 IF g1_debug_mode = 'Y' THEN
417 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'invproc curr = Proj func currency ..Copy ' );
418 END IF;
419
420 FOR i IN tmp_status_projfunc_tab.FIRST..tmp_status_projfunc_tab.LAST LOOP
421
422 tmp_invproc_bill_amount(i) := tmp_projfunc_bill_amount(i);
423 tmp_invproc_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
424 tmp_invproc_rate_date(i) := tmp_projfunc_rate_date(i);
425 tmp_invproc_rate_type(i) := p_projfunc_rate_type(i);
426
427 END LOOP;
428
429 ELSIF l_invproc_currency_code = l_project_currency_code THEN
430
431 IF g1_debug_mode = 'Y' THEN
432 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'invproc curr = Project currency ..Copy ' );
433 END IF;
434
435 FOR i IN tmp_status_project_tab.FIRST..tmp_status_project_tab.LAST LOOP
436
437 tmp_invproc_bill_amount(i) := tmp_project_bill_amount(i);
438 tmp_invproc_exchange_rate(i) := tmp_project_exchange_rate(i);
439 tmp_invproc_rate_date(i) := tmp_project_rate_date(i);
440 tmp_invproc_rate_type(i) := p_project_rate_type(i);
441
442 END LOOP;
443
444 ELSE
445
446 IF g1_debug_mode = 'Y' THEN
447 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Convert Bill Transaction To Funding -Invproc ' );
448 END IF;
449
450 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
451 p_from_currency_tab => p_bill_trans_currency_code,
452 p_to_currency_tab => tmp_invproc_currency_code,
453 p_conversion_date_tab => tmp_invproc_rate_date,
454 p_conversion_type_tab => tmp_invproc_rate_type,
455 p_amount_tab => tmp_bill_trans_bill_amount,
456 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
457 p_converted_amount_tab => tmp_invproc_bill_amount,
458 p_denominator_tab => tmp_denominator_tab,
459 p_numerator_tab => tmp_numerator_tab,
460 p_rate_tab => tmp_funding_exchange_rate,
461 x_status_tab => tmp_status_invproc_tab,
462 p_conversion_between => 'BTC_FC',
463 p_cache_flag =>'N');
464 tmp_denominator_tab.delete;
465 tmp_numerator_tab.delete;
466
467
468 END IF;
469
470 /*
471
472 IF tmp_invproc_currency_type = 'FUNDING_CURRENCY' THEN
473
474 -- Invoice Processing is Funding Currency
475 -- Convert the Bill Transaction to Invoice Processing Currency
476
477 IF g1_debug_mode = 'Y' THEN
478 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Convert Bill Transaction To Funding ' );
479 END IF;
480
481 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
482 p_from_currency_tab => p_bill_trans_currency_code,
483 p_to_currency_tab => tmp_invproc_currency_code,
484 p_conversion_date_tab => tmp_invproc_rate_date,
485 p_conversion_type_tab => tmp_invproc_rate_type,
486 p_amount_tab => tmp_bill_trans_bill_amount,
487 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
488 p_converted_amount_tab => tmp_invproc_bill_amount,
489 p_denominator_tab => tmp_denominator_tab,
490 p_numerator_tab => tmp_numerator_tab,
491 p_rate_tab => tmp_funding_exchange_rate,
492 x_status_tab => tmp_status_invproc_tab,
493 p_conversion_between => 'BTC_FC',
494 p_cache_flag =>'N');
495 tmp_denominator_tab.delete;
496 tmp_numerator_tab.delete;
497
498 ELSIF tmp_invproc_currency_type = 'PROJECT_CURRENCY' THEN
499
500 -- Invoice Processing is PC
501 -- Move the Project Currency Amount and attributes to Invoice Processing
502
503 FOR i IN tmp_status_project_tab.FIRST..tmp_status_project_tab.LAST LOOP
504
505 tmp_invproc_bill_amount(i) := tmp_project_bill_amount(i);
506 tmp_invproc_exchange_rate(i) := tmp_project_exchange_rate(i);
507 tmp_invproc_rate_date(i) := tmp_project_rate_date(i);
508 tmp_invproc_rate_type(i) := p_project_rate_type(i);
509
510 END LOOP;
511 ELSIF tmp_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
512
513 -- Invoice Processing is PFC
514 -- Move the Project Functional Currency Amount and attributes to Invoice Processing
515
516 FOR i IN tmp_status_project_tab.FIRST..tmp_status_project_tab.LAST LOOP
517
518 tmp_invproc_bill_amount(i) := tmp_projfunc_bill_amount(i);
519 tmp_invproc_exchange_rate(i) := tmp_projfunc_exchange_rate(i);
520 tmp_invproc_rate_date(i) := tmp_projfunc_rate_date(i);
521 tmp_invproc_rate_type(i) := p_projfunc_rate_type(i);
522
523 END LOOP;
524 END IF;
525 */
526
527 -- Set the Status code array
528
529 FOR i IN tmp_status_project_tab.FIRST..tmp_status_project_tab.LAST LOOP
530
531 tmp_status_tab(i) := 'N';
532 /*
533 The error string concatenation is already done. so commented
534 and rewritten by srividya
535
536 IF NVL(tmp_status_project_tab(i),'N') <> 'N' THEN
537 tmp_status_tab(i):= 'BTC_PC'|| tmp_status_project_tab(i);
538 ELSIF NVL(tmp_status_projfunc_tab(i),'N') <> 'N' THEN
539 tmp_status_tab(i):= 'BTC_PF'|| tmp_status_projfunc_tab(i);
540 ELSIF NVL(tmp_status_invproc_tab(i),'N') <> 'N' THEN
541 tmp_status_tab(i):= 'BTC_FC'|| tmp_status_invproc_tab(i);
542 ELSIF NVL(tmp_status_funding_tab(i),'N') <> 'N'
543 AND NVL(p_agreement_id,0) <>0 THEN
544 tmp_status_tab(i):= 'BTC_FC'|| tmp_status_funding_tab(i);
545 END IF;
546 */
547 IF NVL(tmp_status_project_tab(i),'N') <> 'N' THEN
548 tmp_status_tab(i):= tmp_status_project_tab(i);
549 ELSIF NVL(tmp_status_projfunc_tab(i),'N') <> 'N' THEN
550 tmp_status_tab(i):= tmp_status_projfunc_tab(i);
551 ELSIF NVL(tmp_status_invproc_tab(i),'N') <> 'N' THEN
552 tmp_status_tab(i):= tmp_status_invproc_tab(i);
553 ELSIF NVL(tmp_status_funding_tab(i),'N') <> 'N'
554 AND NVL(p_agreement_id,0) <>0 THEN
555 tmp_status_tab(i):= tmp_status_funding_tab(i);
556 END IF;
557 x_status_tab(i) := tmp_status_tab(i);
558
559
560 /* Federal Changes : Reporting error if event completion date is not with in
561 agreement start and end date for funding consumption rule is enabled
562
563 IF ( p_shared_funds_consumption = 1 ) THEN
564
565 IF ((tmp_completion_date(i) < l_agreement_start_date) OR
566 (tmp_completion_date(i) > l_agreement_exp_date )) THEN
567
568 tmp_status_tab(i) := 'PA_EVT_AGR_DATE_MISMATCH';
569
570 END IF;
571
572 END IF;
573 */
574
575
576
577
578 END LOOP;
579
580 -- Update the events table
581
582 -- Log Messages for Converted Amounts
583
584 IF g1_debug_mode = 'Y' THEN
585 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Project Id ' || p_project_id);
586 END IF;
587
588 FOR i IN p_event_num.FIRST..p_event_num.LAST LOOP
589 IF g1_debug_mode = 'Y' THEN
590 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Event Num ' || P_event_num(i));
591 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Task Id ' || P_task_id(i));
592 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Project Amount ' || tmp_project_bill_amount(i));
593 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Project Func Amount ' || tmp_projfunc_bill_amount(i));
594 PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'InvProc Amount ' || tmp_invproc_bill_amount(i));
595 END IF;
596 END LOOP;
597
598
599 FORALL I IN p_event_num.FIRST..p_event_num.LAST
600 /** Bug 2874486, added decode statement for ensuring all amt fields have same sign as bill_trans_bill_amount */
601 UPDATE pa_events
602 SET bill_amount = decode(SIGN(bill_trans_bill_amount),SIGN(tmp_invproc_bill_amount(i)),
603 tmp_invproc_bill_amount(i),(-1) * tmp_invproc_bill_amount(i)),
604 invproc_currency_code =decode(invproc_currency_code,NULL,
605 tmp_invproc_currency_code(i),invproc_currency_code),
606 /*bug-2483358*/
607 project_bill_amount = decode(SIGN(bill_trans_bill_amount),SIGN(tmp_project_bill_amount(i)),
608 tmp_project_bill_amount(i),(-1) * tmp_project_bill_amount(i)),
609 project_inv_exchange_rate =tmp_project_exchange_rate(i),
610 -- project_inv_rate_date =tmp_project_rate_date(i), --Modified for Bug3087929
611 project_inv_rate_date =decode(p_project_rate_type(i), 'User', null, tmp_project_rate_date(i)),
612 projfunc_bill_amount = decode(SIGN(bill_trans_bill_amount),SIGN(tmp_projfunc_bill_amount(i)),
613 tmp_projfunc_bill_amount(i),(-1) * tmp_projfunc_bill_amount(i)),
614 projfunc_inv_exchange_rate =tmp_projfunc_exchange_rate(i),
615 -- projfunc_inv_rate_date =tmp_projfunc_rate_date(i), --Modified for Bug3087929
616 projfunc_inv_rate_date =decode(p_projfunc_rate_type(i), 'User', null, tmp_projfunc_rate_date(i)),
617 inv_gen_rejection_code = tmp_status_tab(i),
618 request_id = p_request_id,
619 program_id = l_program_id,
620 program_application_id = l_program_application_id,
621 program_update_date = l_program_update_date,
622 last_update_date = l_last_update_date,
623 last_updated_by = l_last_updated_by,
624 last_update_login = l_last_update_login
625 WHERE project_id = p_project_id
626 AND NVL(task_id,0) = NVL(p_task_id(i),0)
627 AND event_num = p_event_num(i);
628
629 IF g1_debug_mode = 'Y' THEN
630 PA_MCB_INVOICE_PKG.log_message('No of Rows Updated ' || sql%rowcount);
631 END IF;
632 -- Convert the Data Type for BTC and IPC
633
634 FOR i IN p_event_num.FIRST..p_event_num.LAST LOOP
635
636 p_invproc_bill_amount(i) := TO_CHAR(tmp_invproc_bill_amount(i));
637 p_invproc_exchange_rate(i) := TO_CHAR(tmp_invproc_exchange_rate(i));
638 p_invproc_rate_date(i) := TO_CHAR(tmp_invproc_rate_date(i),'YYYY/MM/DD');
639
640 p_project_exchange_rate(i) := TO_CHAR(tmp_project_exchange_rate(i));
641 p_project_rate_date(i) := TO_CHAR(tmp_project_rate_date(i),'YYYY/MM/DD');
642
643 p_projfunc_exchange_rate(i) := TO_CHAR(tmp_projfunc_exchange_rate(i));
644 p_projfunc_rate_date(i) := TO_CHAR(tmp_projfunc_rate_date(i),'YYYY/MM/DD');
645
646 p_projfunc_exchange_rate(i) := TO_CHAR(tmp_funding_exchange_rate(i));
647 p_projfunc_rate_date(i) := TO_CHAR(tmp_funding_rate_date(i),'YYYY/MM/DD');
648 p_invproc_currency_code(i) := tmp_invproc_currency_code(i);
649
650 END LOOP;
651
652 END IF;
653
654 EXCEPTION
655
656 When Others Then
657
658 IF g1_debug_mode = 'Y' THEN
659 PA_MCB_INVOICE_PKG.log_message('Error in Event_Convert_amount_bulk ' || sqlerrm);
660 END IF;
661 x_return_status := sqlerrm( sqlcode );
662
663 END Event_Convert_amount_bulk;
664
665 -- Procedure to Convert the Invoice Line Bill Transaction Amount to PFC, PC, FC
666
667 PROCEDURE Convert_Line_Event_Amount (
668 p_agreement_id IN NUMBER ,
669 p_project_id IN NUMBER ,
670 p_task_id IN NUMBER ,
671 p_event_num IN NUMBER ,
672 p_invproc_bill_amount IN VARCHAR2,
673 x_project_bill_amount OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
674 x_projfunc_bill_amount OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
675 x_funding_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
676 x_funding_bill_amount OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
677 x_funding_rate_date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
678 x_funding_exchange_rate OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
679 x_funding_rate_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
680 x_bill_trans_inv_amount OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
681 x_status_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
682 x_return_status IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
683
684 tmp_funding_currency_code VARCHAR2(30);
685
686 tmp_project_event_amount NUMBER:=0;
687 tmp_projfunc_event_amount NUMBER:=0;
688 tmp_invproc_event_amount NUMBER:=0;
689 tmp_funding_exchange_rate NUMBER:=0;
690 tmp_invproc_bill_amount NUMBER:=0;
691 tmp_bill_trans_inv_amount NUMBER:=0;
692 tmp_bill_trans_event_amount NUMBER :=0;
693 tmp_funding_rate_type VARCHAR2(30);
694 tmp_bill_trans_currency_code VARCHAR2(30);
695 tmp_funding_rate_date DATE;
696
697 tmp_project_currency_code VARCHAR2(30);
698 tmp_projfunc_currency_code VARCHAR2(30);
699 tmp_invproc_currency_code VARCHAR2(30);
700
701 tmp_project_inv_exch_rate NUMBER;
702 tmp_project_inv_rate_date DATE;
703 tmp_project_rate_type VARCHAR2(30);
704
705
706 tmp_projfunc_inv_exch_rate NUMBER;
707 tmp_projfunc_inv_rate_date DATE;
708 tmp_projfunc_rate_type VARCHAR2(30);
709
710 tmp_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
711 tmp_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
712 tmp_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
713 tmp_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
714 tmp_funding_bill_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
715 tmp_funding_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
716 tmp_funding_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
717 tmp_funding_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
718 tmp_bill_trans_bill_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
719 tmp_bill_trans_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
720 tmp_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
721 tmp_funding_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
722
723 tmp_invproc_currency_type VARCHAR2(30);
724
725 BEGIN
726
727 IF g1_debug_mode = 'Y' THEN
728 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount');
729 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Project Id '||p_project_id);
730 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Agreement Id '||p_agreement_id);
731 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Task Id '||p_task_id);
732 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Event Num '||p_event_num);
733 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Invproc_Bill_Amount '||p_invproc_bill_amount);
734 END IF;
735
736 tmp_invproc_bill_amount := TO_NUMBER(NVL(p_invproc_bill_amount,0));
737
738 x_status_code := 'N';
739
740
741 BEGIN
742 -- Get the Funding Currency Code
743
744 /* this pre-existing code was commented for bug 2916606*/
745 /* SELECT FUNDING_CURRENCY_CODE
746 INTO tmp_funding_currency_code
747 FROM pa_summary_project_fundings
748 WHERE agreement_id = p_agreement_id
749 AND NVL(task_id,0) = NVL(p_task_id,0)
750 AND project_id = p_project_id
751 AND rownum=1
752 GROUP BY funding_currency_code
753 HAVING sum(total_baselined_amount) <>0;*/
754
755 /* begin code added for bug 2916606 */
756
757 select funding_currency_code
758 into tmp_funding_currency_code
759 from (
760 select funding_currency_code
761 from pa_summary_project_fundings
762 where project_id = p_project_id
763 and agreement_id = p_agreement_id
764 and nvl(task_id, 0) = nvl(p_task_id, 0)
765 group by funding_currency_code
766 having sum(total_baselined_amount) <> 0)
767 where rownum=1;
768
769 /* end of code added for bug 2916606 */
770
771
772 EXCEPTION /** Added for bug 2263965 **/
773 WHEN NO_DATA_FOUND THEN /** Funding is at Project Level **/
774
775 /* this pre-existing code was commented for bug 2916606*/
776 /*SELECT FUNDING_CURRENCY_CODE
777 INTO tmp_funding_currency_code
778 FROM pa_summary_project_fundings
779 WHERE agreement_id = p_agreement_id
780 AND project_id = p_project_id
781 AND rownum=1
782 GROUP BY funding_currency_code
783 HAVING sum(total_baselined_amount) <>0;*/
784
785 /* begin code added for bug 2916606 */
786
787 select funding_currency_code
788 into tmp_funding_currency_code
789 from(
790 select funding_currency_code
791 from pa_summary_project_fundings
792 where project_id = p_project_id
793 and agreement_id = p_agreement_id
794 group by funding_currency_code
795 having sum(total_baselined_amount) <> 0
796 )
797 where rownum = 1;
798
799 /* end of code added for bug 2916606 */
800
801 END;
802
803 -- Event Amounts
804
805 BEGIN
806
807 SELECT evt.bill_trans_currency_code,
808 /* decode(etyp.event_type_classification,
809 'INVOICE REDUCTION' ,-evt.bill_trans_bill_amount,
810 evt.bill_trans_bill_amount), Commented for bug 3108623 */
811 evt.bill_trans_bill_amount, /*Added for 3108623 */
812 evt.project_bill_amount,
813 evt.projfunc_bill_amount,
814 evt.bill_amount,
815 evt.funding_rate_type,
816 evt.funding_rate_date,
817 evt.funding_exchange_rate ,
818 evt.project_currency_code,
819 evt.projfunc_currency_code,
820 evt.invproc_currency_code,
821 evt.project_inv_exchange_rate,
822 evt.project_inv_rate_date,
823 evt.project_rate_type,
824 evt.projfunc_inv_exchange_rate,
825 evt.projfunc_inv_rate_date,
826 evt.projfunc_rate_type,
827 pr.invproc_currency_type
828 INTO tmp_bill_trans_currency_code,
829 tmp_bill_trans_event_amount,
830 tmp_project_event_amount,
831 tmp_projfunc_event_amount,
832 tmp_invproc_event_amount,
833 tmp_funding_rate_type,
834 tmp_funding_rate_date,
835 tmp_funding_exchange_rate,
836 tmp_project_currency_code,
837 tmp_projfunc_currency_code,
838 tmp_invproc_currency_code,
839 tmp_project_inv_exch_rate,
840 tmp_project_inv_rate_date,
841 tmp_project_rate_type,
842 tmp_projfunc_inv_exch_rate,
843 tmp_projfunc_inv_rate_date,
844 tmp_projfunc_rate_type,
845 tmp_invproc_currency_type
846 FROM pa_events evt, pa_projects_all pr,
847 pa_event_types etyp
848 WHERE evt.project_id = p_project_id
849 AND NVL(evt.task_id,0) = NVL(p_task_id,0)
850 AND evt.event_num = p_event_num
851 AND evt.project_id = pr.project_id
852 AND evt.event_type = etyp.event_type;
853
854 IF g1_debug_mode = 'Y' THEN
855 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Event IPC Amount : ' || tmp_invproc_event_amount);
856 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Event PC Amount : ' || tmp_project_event_amount);
857 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Event PFC Amount : ' || tmp_projfunc_event_amount);
858 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Event Bill Trans Amount : ' || tmp_bill_trans_event_amount);
859 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Event Inv Proc Amount : ' || tmp_invproc_event_amount);
860 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Invoice Inv Proc Amount : ' || tmp_invproc_bill_amount);
861 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Inv Proc Currency Type : ' || tmp_invproc_currency_type);
862 END IF;
863
864 -- Calculating Amounts
865
866 IF nvl(tmp_invproc_currency_code,'0') = nvl(tmp_bill_trans_currency_code,'0') THEN
867
868 IF g1_debug_mode = 'Y' THEN
869 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_invproc_currency_code = tmp_bill_trans_currency_code');
870 END IF;
871
872 tmp_bill_trans_inv_amount := nvl(p_invproc_bill_amount,0);
873
874 elsif tmp_invproc_currency_type= 'PROJFUNC_CURRENCY' AND
875 nvl(tmp_bill_trans_currency_code,'0') <> nvl(tmp_projfunc_currency_code,'0') THEN
876
877 IF g1_debug_mode = 'Y' THEN
878 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_invproc_currency_type = PROJFUNC_CURRENCY and tmp_bill_trans_currency_code <> tmp_projfunc_currency_code ');
879 END IF;
880
881 /* Bug 3548844: If the event is fully billed, use the trans amount from event
882 do not rederive the transaction amount */
883
884 /* Bug 3712615: Added abs to compare the absolute value of amounts.
885 Also added sign functions since invproc bill amt will be -ve in case inv redn event
886 and manual events can have -ve values */
887 IF NVL(abs(p_invproc_bill_amount),0) = NVL(abs(tmp_invproc_event_amount),0) THEN
888
889 tmp_bill_trans_inv_amount := NVL(tmp_bill_trans_event_amount, 0) *
890 sign(nvl(p_invproc_bill_amount, 1)) * sign(nvl(tmp_bill_trans_event_amount,1));
891
892 ELSE
893 /* end of the bug fix 3548844 */
894 tmp_bill_trans_inv_amount := pa_multi_currency_billing.round_trans_currency_amt(
895 NVL(p_invproc_bill_amount,0) *
896 (1/ NVL(tmp_projfunc_inv_exch_rate,0))
897 , tmp_bill_trans_currency_code);
898
899 END IF; --- Added for bug 3548844
900
901 elsif tmp_invproc_currency_type= 'PROJECT_CURRENCY' AND
902 nvl(tmp_bill_trans_currency_code,'0') <> nvl(tmp_project_currency_code,'0') THEN
903
904 IF g1_debug_mode = 'Y' THEN
905 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_invproc_currency_type = PROJECT_CURRENCY and tmp_bill_trans_currency_code <> tmp_project_currency_code ');
906 END IF;
907 /* Bug 3548844: If the event is fully billed, use the trans amount from event
908 do not rederive the transaction amount */
909
910 /* Bug 3712615: Added abs to compare the absolute value of amounts.
911 Also added sign function since invproc bill amt will be -ve in case inv redn event */
912 IF NVL(abs(p_invproc_bill_amount),0) = NVL(abs(tmp_invproc_event_amount),0) THEN
913
914 tmp_bill_trans_inv_amount := NVL(tmp_bill_trans_event_amount,0) *
915 sign(nvl(p_invproc_bill_amount,1)) * sign(nvl(tmp_bill_trans_event_amount,1));
916
917 ELSE
918 /* end of the bug fix 3548844 */
919 tmp_bill_trans_inv_amount := pa_multi_currency_billing.round_trans_currency_amt(
920 NVL(p_invproc_bill_amount,0) *
921 (1/ NVL(tmp_project_inv_exch_rate,0))
922 , tmp_bill_trans_currency_code);
923
924 END IF; --- Added for bug 3548844
925 else
926
927 IF g1_debug_mode = 'Y' THEN
928 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_invproc_currency_code <> tmp_bill_trans_currency_code');
929 END IF;
930
931 /* Bug 3548844: If the event is fully billed, use the trans amount from event
932 do not rederive the transaction amount */
933
934 /* Bug 3712615: Added abs to compare the absolute value of amounts.
935 Also added sign function since invproc bill amt will be -ve in case inv redn event */
936 IF NVL(abs(p_invproc_bill_amount),0) = NVL(abs(tmp_invproc_event_amount),0) THEN
937
938 tmp_bill_trans_inv_amount := NVL(tmp_bill_trans_event_amount,0) *
939 sign(nvl(p_invproc_bill_amount,1)) * sign(nvl(tmp_bill_trans_event_amount,1));
940
941 ELSE
942 /* end of the bug fix 3548844 */
943 tmp_bill_trans_inv_amount := pa_multi_currency_billing.round_trans_currency_amt(
944 tmp_bill_trans_event_amount
945 * (NVL(p_invproc_bill_amount,0) /
946 NVL(tmp_invproc_event_amount,0) ) , tmp_bill_trans_currency_code);
947 END IF; --- Added for bug 3548844
948
949 end if;
950
951 IF g1_debug_mode = 'Y' THEN
952 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_bill_trans_inv_amount : ' || tmp_bill_trans_inv_amount);
953 END IF;
954
955 x_bill_trans_inv_amount := TO_CHAR(tmp_bill_trans_inv_amount);
956
957 IF g1_debug_mode = 'Y' THEN
958 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_bill_trans_inv_amount : ' || tmp_bill_trans_inv_amount);
959 END IF;
960
961 if nvl(tmp_projfunc_currency_code,'0') = nvl(tmp_bill_trans_currency_code,'0') then
962
963 x_projfunc_bill_amount := x_bill_trans_inv_amount;
964
965 else
966 /* added for bug 2784321 */
967 if nvl(tmp_projfunc_currency_code,'0') = nvl(tmp_invproc_currency_code,'0') then
968 IF g1_debug_mode = 'Y' THEN
969 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'tmp_projfunc_currency_code <> tmp_bill_trans_currency_code AND tmp_projfunc_currency_code = tmp_invproc_currency_code');
970 END IF;
971
972 x_projfunc_bill_amount := p_invproc_bill_amount;
973 else
974 IF g1_debug_mode = 'Y' THEN
975 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'tmp_projfunc_currency_code <> tmp_bill_trans_currency_code');
976 END IF;
977
978 x_projfunc_bill_amount := TO_CHAR ( pa_multi_currency_billing.round_trans_currency_amt(
979 NVL(tmp_projfunc_event_amount,0) *
980 (NVL(tmp_bill_trans_inv_amount,0)/
981 NVL(tmp_bill_trans_event_amount,0)), tmp_projfunc_currency_code));
982 end if;/* Added for bug 2784321 */
983 end if ;
984
985 IF g1_debug_mode = 'Y' THEN
986 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' x_projfunc_bill_amount : ' || x_projfunc_bill_amount);
987 END IF;
988
989
990 if nvl(tmp_project_currency_code,'0') = nvl(tmp_bill_trans_currency_code,'0') then
991
992 x_project_bill_amount := x_bill_trans_inv_amount;
993
994 elsif nvl(tmp_project_currency_code,'0') = nvl(tmp_projfunc_currency_code,'0') then
995
996 IF g1_debug_mode = 'Y' THEN
997 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' tmp_project_currency_code <> tmp_projfunc_currency_code');
998 END IF;
999
1000 x_project_bill_amount := x_projfunc_bill_amount ;
1001
1002 else
1003
1004 x_project_bill_amount := TO_CHAR ( pa_multi_currency_billing.round_trans_currency_amt(
1005 NVL(tmp_project_event_amount,0) *
1006 (NVL(tmp_bill_trans_inv_amount,0)/
1007 NVL(tmp_bill_trans_event_amount,0)), tmp_project_currency_code));
1008
1009 end if;
1010
1011 IF g1_debug_mode = 'Y' THEN
1012 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' x_project_bill_amount : '|| x_project_bill_amount);
1013 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Line Bill Trans Amount : ' || tmp_bill_trans_inv_amount);
1014 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_Project Amount : ' || x_project_bill_amount);
1015 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_projfunc Amount: ' || x_projfunc_bill_amount);
1016 END IF;
1017
1018 if nvl(tmp_funding_currency_code,'0') = nvl(tmp_bill_trans_currency_code,'0') then
1019
1020 x_funding_currency_code := tmp_funding_currency_code;
1021 x_funding_bill_amount := x_bill_trans_inv_amount;
1022 x_funding_exchange_rate := NULL;
1023 x_funding_rate_type := NULL;
1024 x_funding_rate_date := NULL;
1025
1026 IF g1_debug_mode = 'Y' THEN
1027 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'tmp_funding_currency_code = tmp_bill_trans_currency_code ');
1028 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'x_funding_bill_amount = ' || x_funding_bill_amount);
1029 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'x_bill_trans_inv_amount = ' || x_bill_trans_inv_amount);
1030 END IF;
1031
1032 elsif nvl(tmp_funding_currency_code,'0') = nvl(tmp_projfunc_currency_code,'0') then
1033
1034 x_funding_currency_code := tmp_projfunc_currency_code;
1035 x_funding_bill_amount := x_projfunc_bill_amount;
1036 x_funding_exchange_rate := TO_CHAR(tmp_projfunc_inv_exch_rate);
1037 x_funding_rate_type := tmp_projfunc_rate_type;
1038 x_funding_rate_date := TO_CHAR(tmp_projfunc_inv_rate_date,'YYYY/MM/DD');
1039
1040 IF g1_debug_mode = 'Y' THEN
1041 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'tmp_funding_currency_code = tmp_projfunc_currency_code ');
1042 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'x_funding_bill_amount = ' || x_funding_bill_amount);
1043 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'x_projfunc_bill_amount = ' || x_projfunc_bill_amount);
1044 END IF;
1045
1046 elsif nvl(tmp_funding_currency_code,'0') = nvl(tmp_project_currency_code,'0') then
1047
1048 x_funding_currency_code := tmp_project_currency_code;
1049 x_funding_bill_amount := x_project_bill_amount;
1050 x_funding_exchange_rate := TO_CHAR(tmp_project_inv_exch_rate);
1051 x_funding_rate_type := tmp_project_rate_type;
1052 x_funding_rate_date := TO_CHAR(tmp_project_inv_rate_date,'YYYY/MM/DD');
1053
1054 IF g1_debug_mode = 'Y' THEN
1055 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'tmp_funding_currency_code = tmp_project_currency_code ');
1056 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'x_funding_bill_amount = ' || x_funding_bill_amount);
1057 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'x_project_bill_amount = ' || x_project_bill_amount);
1058 END IF;
1059
1060 else
1061
1062 x_funding_currency_code := tmp_funding_currency_code;
1063 tmp_bill_trans_currency_tab(1) := tmp_bill_trans_currency_code;
1064 tmp_funding_currency_tab(1) := tmp_funding_currency_code;
1065 tmp_funding_rate_type_tab(1) := tmp_funding_rate_type;
1066 tmp_funding_rate_date_tab(1) := nvl(tmp_funding_rate_date,TO_DATE(pa_billing.globvars.invoicedate,'YYYY/MM/DD'));
1067 tmp_funding_exchange_rate_tab(1) := tmp_funding_exchange_rate;
1068 tmp_bill_trans_bill_amount_tab(1) := tmp_bill_trans_inv_amount;
1069 tmp_funding_bill_amount_tab(1) := 0;
1070 tmp_status_tab(1) := 'N';
1071 tmp_denominator_tab(1) :=0;
1072 tmp_numerator_tab(1) :=0;
1073 tmp_user_validate_flag_tab(1) :='N';
1074
1075 IF g1_debug_mode = 'Y' THEN
1076 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Call convert_amount_bulk Using : ');
1077 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'From Currency Code : ' || tmp_bill_trans_currency_code);
1078 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' To Currency Code : ' || tmp_funding_currency_code);
1079 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Ex Rate Type : ' || tmp_funding_rate_type);
1080 PA_MCB_INVOICE_PKG.log_message(' Ex Rate Date : ' ||
1081 nvl(to_char(tmp_funding_rate_date, 'YYYY/MM/DD'), pa_billing.globvars.invoicedate));
1082 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Ex Rate : ' || tmp_funding_exchange_rate);
1083 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' From Amount : ' || tmp_bill_trans_inv_amount);
1084 END IF;
1085
1086 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
1087 p_from_currency_tab => tmp_bill_trans_currency_tab,
1088 p_to_currency_tab => tmp_funding_currency_tab,
1089 p_conversion_date_tab => tmp_funding_rate_date_tab,
1090 p_conversion_type_tab => tmp_funding_rate_type_tab,
1091 p_amount_tab => tmp_bill_trans_bill_amount_tab,
1092 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
1093 p_converted_amount_tab => tmp_funding_bill_amount_tab,
1094 p_denominator_tab => tmp_denominator_tab,
1095 p_numerator_tab => tmp_numerator_tab,
1096 p_rate_tab => tmp_funding_exchange_rate_tab,
1097 x_status_tab =>tmp_status_tab,
1098 p_conversion_between => 'BTC_FC',
1099 p_cache_flag => 'N');
1100
1101 IF g1_debug_mode = 'Y' THEN
1102 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' After Convert_Bulk Call ' );
1103 END IF;
1104
1105 IF NVL(tmp_funding_bill_amount_tab(1),0) <> 0 THEN
1106
1107 IF g1_debug_mode = 'Y' THEN
1108 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Assign Funding Amount ' );
1109 END IF;
1110 x_funding_bill_amount := TO_CHAR(tmp_funding_bill_amount_tab(1));
1111
1112 IF g1_debug_mode = 'Y' THEN
1113 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Assign Funding Ex Date ' );
1114 END IF;
1115 --x_funding_rate_date := TO_CHAR(NVL(tmp_funding_rate_date_tab(1),SYSDATE));
1116 x_funding_rate_date := TO_CHAR(tmp_funding_rate_date_tab(1),'YYYY/MM/DD');
1117
1118 IF g1_debug_mode = 'Y' THEN
1119 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Assign Funding Ex Rate ' );
1120 END IF;
1121 -- x_funding_exchange_rate := TO_CHAR(round(tmp_funding_exchange_rate_tab(1),5));
1122 x_funding_exchange_rate := TO_CHAR(tmp_funding_exchange_rate_tab(1));
1123
1124 IF g1_debug_mode = 'Y' THEN
1125 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || ' Assign Funding Ex Rate type' );
1126 END IF;
1127 x_funding_rate_type := tmp_funding_rate_type_tab(1);
1128 x_status_code := 'N';
1129 ELSE
1130 x_funding_bill_amount :=0 ;
1131 x_status_code := 'Y';
1132 END IF;
1133
1134 END IF;
1135
1136 IF g1_debug_mode = 'Y' THEN
1137 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_Funding Amount : ' || x_funding_bill_amount);
1138 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_Funding Rate : ' || x_funding_exchange_rate);
1139 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_Funding Rate type : ' || x_funding_rate_type);
1140 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_Funding Rate Date : ' || x_funding_rate_date);
1141 PA_MCB_INVOICE_PKG.log_message('Convert_Line_Event_Amount: ' || 'Convert_Line_Funding Status : ' || x_status_code);
1142 END IF;
1143
1144 END;
1145 EXCEPTION
1146
1147 WHEN OTHERS THEN
1148 x_project_bill_amount := NULL; --NOCOPY
1149 x_projfunc_bill_amount := NULL; --NOCOPY
1150 x_funding_currency_code := NULL; --NOCOPY
1151 x_funding_bill_amount := NULL; --NOCOPY
1152 x_funding_rate_date := NULL; --NOCOPY
1153 x_funding_exchange_rate := NULL; --NOCOPY
1154 x_funding_rate_type := NULL; --NOCOPY
1155 x_bill_trans_inv_amount := NULL; --NOCOPY
1156 IF g1_debug_mode = 'Y' THEN
1157 PA_MCB_INVOICE_PKG.log_message('Error in Convert_line_event_amount' || sqlerrm);
1158 END IF;
1159 x_return_status := sqlerrm( sqlcode );
1160
1161 -- RAISE;
1162 END Convert_Line_Event_Amount;
1163 -- Check whether the btc can be converted to FC
1164
1165 PROCEDURE Check_Funding_Conv_Attributes (
1166 p_funding_currency_code IN VARCHAR2 ,
1167 p_bill_trans_currency_code IN VARCHAR2 ,
1168 p_bill_trans_bill_amount IN VARCHAR2 ,
1169 p_funding_rate_type IN VARCHAR2 ,
1170 p_funding_rate_date IN VARCHAR2,
1171 p_funding_exchange_rate IN VARCHAR2,
1172 x_funding_bill_amount OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1173 x_status_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1174 x_return_status IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1175
1176 tmp_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1177 tmp_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1178 tmp_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1179 tmp_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1180 tmp_funding_bill_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1181 tmp_funding_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1182 tmp_funding_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1183 tmp_funding_exchange_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1184 tmp_bill_trans_bill_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1185 tmp_bill_trans_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1186 tmp_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1187 tmp_funding_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1188
1189 BEGIN
1190
1191 IF g1_debug_mode = 'Y' THEN
1192 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes');
1193 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || 'Bill Trans Currency : ' || p_bill_trans_currency_code);
1194 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || 'Bill Trans Amount : ' || p_bill_trans_bill_amount);
1195 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || 'Funding Currency : ' || p_funding_currency_code);
1196 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || ' Rate Type : ' || p_funding_rate_type);
1197 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || ' Rate Date : ' || p_funding_rate_date);
1198 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || ' Rate : ' || p_funding_exchange_rate);
1199 END IF;
1200
1201 tmp_bill_trans_currency_tab(1) := p_bill_trans_currency_code;
1202 tmp_bill_trans_bill_amount_tab(1) := p_bill_trans_bill_amount;
1203 tmp_funding_currency_tab(1) := p_funding_currency_code;
1204 tmp_funding_rate_type_tab(1) := p_funding_rate_type;
1205 tmp_funding_rate_date_tab(1) := TO_DATE(p_funding_rate_date,'YYYY/MM/DD');
1206 tmp_funding_exchange_rate_tab(1) := TO_NUMBER(p_funding_exchange_rate);
1207 tmp_funding_bill_amount_tab(1) := 0;
1208 tmp_status_tab(1) := 'N';
1209 tmp_denominator_tab(1) :=0;
1210 tmp_numerator_tab(1) :=0;
1211 tmp_user_validate_flag_tab(1) :='N';
1212
1213 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
1214 p_from_currency_tab => tmp_bill_trans_currency_tab,
1215 p_to_currency_tab => tmp_funding_currency_tab,
1216 p_conversion_date_tab => tmp_funding_rate_date_tab,
1217 p_conversion_type_tab => tmp_funding_rate_type_tab,
1218 p_amount_tab => tmp_bill_trans_bill_amount_tab,
1219 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
1220 p_converted_amount_tab => tmp_funding_bill_amount_tab,
1221 p_denominator_tab => tmp_denominator_tab,
1222 p_numerator_tab => tmp_numerator_tab,
1223 p_rate_tab => tmp_funding_exchange_rate_tab,
1224 x_status_tab => tmp_status_tab,
1225 p_conversion_between => 'BTC_FC',
1226 p_cache_flag => 'N');
1227
1228 -- IF tmp_funding_bill_amount_tab(1) <> 0 THEN /*Commented for bug 6161196 */
1229 IF tmp_status_tab(1) = 'N' THEN /*Added for bug 6161196 */
1230 x_funding_bill_amount := TO_CHAR(tmp_funding_bill_amount_tab(1));
1231 x_status_code :='N';
1232 ELSE
1233 x_funding_bill_amount :=0 ;
1234 x_status_code :='Y';
1235 END IF;
1236 IF g1_debug_mode = 'Y' THEN
1237 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || 'Funding Tab Amount : ' || tmp_funding_bill_amount_tab(1));
1238 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || 'Funding Amount : ' || x_funding_bill_amount);
1239 PA_MCB_INVOICE_PKG.log_message('Check_Funding_Conv_Attributes: ' || ' Status : ' || x_status_code);
1240 END IF;
1241
1242 EXCEPTION
1243
1244 When Others Then
1245 x_funding_bill_amount := NULL; --NOCOPY
1246 IF g1_debug_mode = 'Y' THEN
1247 PA_MCB_INVOICE_PKG.log_message('Error in Check_funding_conv_attributes ' || sqlerrm);
1248 END IF;
1249 x_return_status := sqlerrm( sqlcode );
1250
1251 END Check_Funding_Conv_Attributes;
1252
1253 PROCEDURE log_message (p_log_msg IN VARCHAR2) IS
1254 BEGIN
1255 pa_debug.write_file ('LOG',to_char(sysdate, 'YYYY/MM/DD HH:MI:SS ')||p_log_msg);
1256 NULL;
1257 END log_message;
1258 PROCEDURE Init (P_DEBUG_MODE VARCHAR2) IS
1259 BEGIN
1260 G_LAST_UPDATE_LOGIN := fnd_global.login_id;
1261 G_REQUEST_ID := fnd_global.conc_request_id;
1262 G_PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
1263 G_PROGRAM_ID := fnd_global.conc_program_id;
1264 G_LAST_UPDATED_BY := fnd_global.user_id;
1265 G_CREATED_BY := fnd_global.user_id;
1266 G_DEBUG_MODE := 'Y';
1267 pa_debug.init_err_stack ('Invoice Generation');
1268 pa_debug.set_process(
1269 x_process => 'PLSQL',
1270 x_debug_mode => G_DEBUG_MODE);
1271
1272 pa_debug.G_Err_Stage :=' Start PLSQL Error ';
1273 IF g1_debug_mode = 'Y' THEN
1274 PA_MCB_INVOICE_PKG.log_message('Init: ' || pa_debug.G_Err_Stage);
1275 END IF;
1276
1277
1278 END Init;
1279 --==================================================================================
1280 --Introduced for the Bug 4146846
1281 ---Procedure cal_conversion_attr
1282 --Assigns the AR Conversion Attributes depending on IPC,PFC and USe PFC flag
1283 --Behaves similar to the case when BTC is not checked
1284 --==================================================================================
1285 Procedure Cal_Conversion_Attr (p_project_id IN NUMBER,
1286 p_draft_invoice_num IN NUMBER,
1287 p_use_pfc_flag IN VARCHAR2,
1288 p_pfc_currency_code IN VARCHAR2,
1289 p_pfc_ex_rate IN NUMBER,
1290 p_pfc_ex_rate_date_code IN VARCHAR2,
1291 p_pfc_rate_type IN VARCHAR2,
1292 p_pfc_rate_date IN DATE,
1293 p_invproc_currency_code IN VARCHAR2,
1294 p_inv_ex_rate IN NUMBER,
1295 p_inv_rate_type IN VARCHAR2,
1296 p_inv_rate_date IN DATE,
1297 p_btc_currency_code IN VARCHAR2,
1298 p_bill_thru_date IN DATE,
1299 x_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1300 IS
1301 -- Declared the cursor for the Bug 4298230 to fetch the lookup meaning
1302 CURSOR reject_reason (reject_code VARCHAR2) IS
1303 SELECT lu.meaning
1304 FROM pa_lookups lu
1305 WHERE lu.lookup_type = 'INVOICE DISTRIBUTION WARNING'
1306 AND lu.lookup_code = reject_code;
1307
1308 -- Bug : 4298230 Added the following parameters for the call to PA_MULTI_CURRENCY_BILLING.convert_amount_bulk
1309 l_reject_reason_meaning VARCHAR2(500);
1310 l_invoice_date DATE := pa_billing.GetInvoiceDate;
1311 tmp_pfc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1312 tmp_btc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1313 tmp_bill_trans_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1314 tmp_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1315 tmp_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1316 tmp_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1317 tmp_status_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1318 tmp_bill_trans_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1319 tmp_bill_trans_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1320 tmp_projfunc_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1321 tmp_projfunc_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1322 tmp_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1323
1324 l_sum_projfunc_bill_amount NUMBER; --Added For Bug 6084445
1325 l_sum_inv_amount NUMBER; --Added For Bug 6084445
1326
1327 l_rate NUMBER; -- Bug#5762081
1328
1329
1330 BEGIN
1331
1332 -- Bug 4298230 Initilaized the variables
1333
1334 tmp_btc_currency_code_tab(1) := p_btc_currency_code;
1335 tmp_pfc_currency_code_tab(1) := p_pfc_currency_code;
1336 tmp_bill_trans_amount_tab(1) := null;
1337 tmp_denominator_tab(1) := null;
1338 tmp_numerator_tab(1) := null;
1339 tmp_rate_tab(1) := null;
1340 tmp_status_tab(1) := null;
1341 tmp_bill_trans_rate_type_tab(1) := null;
1342 tmp_bill_trans_rate_date_tab(1) := null;
1343 tmp_projfunc_rate_type_tab(1) := null;
1344 tmp_projfunc_rate_date_tab(1) := null;
1345 tmp_user_validate_flag_tab(1) := 'N';
1346
1347
1348
1349 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: add for bug 5762081 ');
1350 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_project_id '|| to_char(p_project_id) );
1351 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_draft_invoice_num '|| to_char(p_draft_invoice_num) );
1352 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_use_pfc_flag '|| p_use_pfc_flag );
1353 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_pfc_currency_code '|| p_pfc_currency_code);
1354 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_pfc_ex_rate '|| to_char(p_pfc_ex_rate) );
1355 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_pfc_ex_rate_date_code '|| p_pfc_ex_rate_date_code);
1356 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_pfc_rate_type '|| p_pfc_rate_type);
1357 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_pfc_rate_date '|| to_char(p_pfc_rate_date,'DD-MON-YYYY' ) );
1358 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_invproc_currency_code '|| p_invproc_currency_code );
1359 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_inv_ex_rate '|| to_char(p_inv_ex_rate) );
1360
1361 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_inv_rate_type '|| p_inv_rate_type );
1362 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_inv_rate_date '|| to_char(p_inv_rate_date,'DD-MON-YYYY' ));
1363 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_btc_currency_code '|| p_btc_currency_code );
1364 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' p_bill_thru_date '|| to_char(p_bill_thru_date,'DD-MON-YYYY' ));
1365
1366
1367
1368 IF (p_btc_currency_code = p_pfc_currency_code)
1369 THEN
1370 --CASE : BTC=PFC:e.g IPC=GBP, PFC=BTC=USD or IPC=PFC=BTC=USD : Here we donot need AR attributes as the Inv currency is in terms of PFC
1371
1372 IF g1_debug_mode = 'Y' THEN
1373 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' if (p_invproc_currency_code = p_btc_currency_code) AND (p_btc_currency_code = p_pfc_currency_code)');
1374 END IF;
1375 X_Status := NULL;
1376
1377 UPDATE pa_draft_invoices_all
1378 SET projfunc_invtrans_rate_type = NULL
1379 ,projfunc_invtrans_rate_date = NULL
1380 ,projfunc_invtrans_ex_rate = NULL
1381 WHERE project_id = p_project_id
1382 AND draft_invoice_num = p_draft_invoice_num;
1383
1384 ELSIF (p_invproc_currency_code <> p_pfc_currency_code) THEN
1385
1386 --CASE : BTC<>IPC<>PFC or BTC=IPC<>PFC :Check for Use PFC for Receviabled Flag :e.g::e.g IPC=GBP, PFC=USD BTC=DKK
1387 --e.g : IPC=GBP,BTC=GBP,PFC=USD
1388
1389 IF g1_debug_mode = 'Y' THEN
1390 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: ' || ' iF (p_invproc_currency_code <> p_pfc_currency_code) THEN ');
1391 END IF;
1392
1393 IF p_use_pfc_flag <> 'Y' then --USE PFC FLAG CHECK
1394
1395 --The projfunc_inv_trans_rate derived in the procedure Inv_by_Bill_trans_Currency remains as it is
1396 -- We are going to use the same as derived rate with Rate Type ='User and Date= InvoiceDate
1397
1398 UPDATE pa_draft_invoices_all
1399 SET projfunc_invtrans_rate_type = 'User'
1400 ,projfunc_invtrans_rate_date = l_invoice_date
1401 WHERE project_id = p_project_id
1402 AND draft_invoice_num = p_draft_invoice_num;
1403
1404 ELSE -- If p_use_pfc_flag ='Y' Then use PFC attributes
1405
1406 tmp_rate_tab(1) := p_inv_ex_rate; -- Bug 4298230
1407
1408 SELECT NVL(sum(dii.bill_trans_bill_amount),0)
1409 INTO tmp_bill_trans_amount_tab(1)
1410 FROM pa_draft_invoice_items dii
1411 WHERE dii.project_id = p_project_id
1412 AND dii.draft_invoice_num = p_draft_invoice_num
1413 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1414 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1415 AND dii.invoice_line_type <> 'RETENTION';
1416
1417 tmp_projfunc_rate_type_tab(1) := p_pfc_rate_type; -- bug 4298230
1418 tmp_projfunc_rate_date_tab(1) := l_invoice_date; --Bug 4298230
1419
1420
1421 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
1422 p_from_currency_tab => tmp_pfc_currency_code_tab ,
1423 p_to_currency_tab => tmp_btc_currency_code_tab,
1424 p_conversion_date_tab => tmp_projfunc_rate_date_tab,
1425 p_conversion_type_tab => tmp_projfunc_rate_type_tab,
1426 p_amount_tab => tmp_bill_trans_amount_tab,
1427 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
1428 p_converted_amount_tab => tmp_bill_trans_amount_tab,
1429 p_denominator_tab => tmp_denominator_tab,
1430 p_numerator_tab => tmp_numerator_tab,
1431 p_rate_tab => tmp_rate_tab,
1432 x_status_tab => tmp_status_tab,
1433 p_conversion_between => 'PFC_BT',
1434 p_cache_flag => 'N');
1435
1436 -- Added the below code for Bug 4298230
1437
1438 IF tmp_status_tab(1) = 'PA_NO_EXCH_RATE_EXISTS_PFC_BT' THEN
1439
1440 OPEN reject_reason (tmp_status_tab(1));
1441 FETCH reject_reason INTO l_reject_reason_meaning;
1442 CLOSE reject_reason;
1443
1444 UPDATE pa_draft_invoices_all
1445 SET generation_error_flag = 'Y',
1446 TRANSFER_REJECTION_REASON = l_reject_reason_meaning
1447 WHERE project_id = p_project_id
1448 AND draft_invoice_num = p_draft_invoice_num;
1449
1450 ELSE
1451
1452 UPDATE pa_draft_invoices_all
1453 SET projfunc_invtrans_rate_type = p_pfc_rate_type
1454 ,projfunc_invtrans_rate_date = DECODE(p_pfc_ex_rate_date_code,'PA_INVOICE_DATE',
1455 tmp_projfunc_rate_date_tab(1),p_pfc_rate_date)
1456 ,projfunc_invtrans_ex_rate = DECODE(p_pfc_ex_rate_date_code,'PA_INVOICE_DATE',
1457 tmp_rate_tab(1), p_pfc_ex_rate )
1458 WHERE project_id = p_project_id
1459 AND draft_invoice_num = p_draft_invoice_num;
1460
1461 END IF; -- End of IF for Bug 4298230
1462
1463 END IF; -- of p_use_pfc_flag case
1464 ELSE --IPC=PFC
1465 IF (p_invproc_currency_code <> p_btc_currency_code) AND (p_invproc_currency_code = p_pfc_currency_code) THEN
1466
1467 --CASE: PFC=IPC<>BTC e.g :IPC=PFC=USD , BTC=GBP
1468
1469 IF g1_debug_mode = 'Y' THEN
1470 PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr ' || ' (p_invproc_currency_code <> p_btc_currency_code) AND (p_invproc_currency_code = p_pfc_currency_code) ');
1471
1472 PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || 'Invoice Transaction Rate is based on p_use_pfc_flag '); -- 5762081
1473 END IF;
1474
1475
1476 /* Begin Bug#5762081 */
1477
1478 IF p_use_pfc_flag <> 'Y' then
1479
1480
1481 PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || ' p_use_pfg_flag is not equal to Y ');
1482 PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || ' update invtrans_rate details as User ');
1483
1484 --The projfunc_inv_trans_rate derived in the procedure Inv_by_Bill_trans_Currency remains as it is
1485 -- We are going to use the same as derived rate with Rate Type ='User and Date= InvoiceDate
1486
1487 /*Start of code change for Bug 6084445*/
1488
1489 SELECT sum(NVL(dii.projfunc_bill_amount,0))
1490 INTO l_sum_projfunc_bill_amount
1491 FROM pa_draft_invoice_items dii
1492 WHERE dii.project_id = P_Project_Id
1493 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1494 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1495 AND dii.invoice_line_type <> 'RETENTION'
1496 AND dii.draft_invoice_num = p_draft_invoice_num;
1497
1498 SELECT sum(NVL(dii.bill_trans_bill_amount,0))
1499 INTO l_sum_inv_amount
1500 FROM pa_draft_invoice_items dii
1501 WHERE dii.project_id = P_Project_Id
1502 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1503 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1504 AND dii.invoice_line_type <> 'RETENTION'
1505 AND dii.draft_invoice_num = p_draft_invoice_num;
1506
1507
1508 IF l_sum_projfunc_bill_amount <> 0 AND l_sum_inv_amount <> 0
1509 THEN
1510 SELECT sum(NVL(dii.bill_trans_bill_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
1511 INTO l_rate
1512 FROM pa_draft_invoice_items dii
1513 WHERE dii.project_id = P_Project_Id
1514 AND dii.draft_invoice_num = p_draft_invoice_num
1515 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1516 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1517 AND dii.invoice_line_type <> 'RETENTION'
1518 having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
1519 ELSE
1520 /* Added begin and end to handle the exception for bug 8666892 */
1521 BEGIN
1522
1523 SELECT NVL(dii.bill_trans_bill_amount,0)/NVL(dii.projfunc_bill_amount,0)
1524 INTO l_rate
1525 FROM pa_draft_invoice_items dii
1526 WHERE dii.project_id = P_Project_Id
1527 AND dii.draft_invoice_num = p_draft_invoice_num
1528 AND nvl(dii.projfunc_bill_amount,0) <> 0
1529 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1530 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1531 AND dii.invoice_line_type <> 'RETENTION'
1532 AND rownum=1;
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 l_rate := 0;
1536 END;
1537
1538 END IF;
1539 /* End of code change for Bug 6084445 */
1540
1541 /* Commented for Bug 6084445 Start
1542 SELECT NVL(sum(dii.bill_trans_bill_amount),0) / nvl(sum(dii.projfunc_bill_amount),0)
1543 INTO l_rate
1544 FROM pa_draft_invoice_items dii
1545 WHERE dii.project_id = p_project_id
1546 AND dii.draft_invoice_num = p_draft_invoice_num
1547 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1548 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1549 AND dii.invoice_line_type <> 'RETENTION';
1550 Commented for Bug 6084445 End */
1551
1552 UPDATE pa_draft_invoices_all
1553 SET projfunc_invtrans_rate_type = 'User'
1554 ,projfunc_invtrans_rate_date = NVL(l_invoice_date,p_bill_thru_date)
1555 ,projfunc_invtrans_ex_rate = l_rate
1556 WHERE project_id = p_project_id
1557 AND draft_invoice_num = p_draft_invoice_num;
1558
1559
1560 ELSE /* End Bug#5762081 */
1561
1562
1563 PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || 'Before the Select statement ');
1564
1565 SELECT NVL(sum(dii.bill_trans_bill_amount),0)
1566 INTO tmp_bill_trans_amount_tab(1)
1567 FROM pa_draft_invoice_items dii
1568 WHERE dii.project_id = p_project_id
1569 AND dii.draft_invoice_num = p_draft_invoice_num
1570 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1571 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1572 AND dii.invoice_line_type <> 'RETENTION';
1573
1574 tmp_bill_trans_rate_date_tab(1) :=NVL(p_inv_rate_date,NVL(l_invoice_date,p_bill_thru_date)); -- Bug 4298230
1575
1576 --Converting the PFC to BTC as per Rate Types and DAtes defined in Ct. Screen
1577 tmp_rate_tab(1) := p_inv_ex_rate; --Bug 4298230
1578 tmp_bill_trans_rate_type_tab(1) := p_inv_rate_type; --Bug 4298230
1579
1580 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
1581 p_from_currency_tab => tmp_pfc_currency_code_tab ,
1582 p_to_currency_tab => tmp_btc_currency_code_tab,
1583 p_conversion_date_tab => tmp_bill_trans_rate_date_tab,
1584 p_conversion_type_tab => tmp_bill_trans_rate_type_tab,
1585 p_amount_tab => tmp_bill_trans_amount_tab,
1586 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
1587 p_converted_amount_tab => tmp_bill_trans_amount_tab,
1588 p_denominator_tab => tmp_denominator_tab,
1589 p_numerator_tab => tmp_numerator_tab,
1590 p_rate_tab => tmp_rate_tab,
1591 x_status_tab => tmp_status_tab,
1592 p_conversion_between => 'PFC_BT',
1593 p_cache_flag => 'N');
1594
1595 -- Added the below code for Bug 4298230
1596
1597 IF g1_debug_mode = 'Y' THEN
1598 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: status :: '||tmp_status_tab(1));
1599 END IF;
1600
1601 IF tmp_status_tab(1) = 'PA_NO_EXCH_RATE_EXISTS_PFC_BT' THEN
1602
1603 OPEN reject_reason (tmp_status_tab(1));
1604 FETCH reject_reason INTO l_reject_reason_meaning;
1605 CLOSE reject_reason;
1606
1607 UPDATE pa_draft_invoices_all
1608 SET generation_error_flag = 'Y',
1609 TRANSFER_REJECTION_REASON = l_reject_reason_meaning
1610 WHERE project_id = p_project_id
1611 AND draft_invoice_num = p_draft_invoice_num;
1612
1613 ELSE
1614
1615 UPDATE pa_draft_invoices_all
1616 SET projfunc_invtrans_rate_type = p_inv_rate_type
1617 ,projfunc_invtrans_rate_date = NVL(p_inv_rate_date,NVL(l_invoice_date,p_bill_thru_date))
1618 ,projfunc_invtrans_ex_rate = NVL(p_inv_ex_rate,tmp_rate_tab(1))
1619 WHERE project_id = p_project_id
1620 AND draft_invoice_num = p_draft_invoice_num;
1621
1622 END IF; -- End of IF added for the Bug 4298230
1623
1624 END IF; -- Bug#5762081
1625
1626 END IF;
1627 END IF;
1628 x_status := 'Y';
1629
1630 EXCEPTION
1631 WHEN OTHERS
1632 THEN
1633 IF g1_debug_mode = 'Y' THEN
1634 PA_MCB_INVOICE_PKG.log_message('cal_Conversion_Attr: ' || ' Sql Error : ' || sqlerrm);
1635 END IF;
1636 RAISE;
1637 END Cal_Conversion_Attr;
1638
1639 --====================================================
1640 -- Procedure Invoice_by_Bill_Trans_Currency
1641 -- This procedure will be called only if the project is invoice by
1642 -- bill transaction currency setup.
1643 -- Procedure will split the invoice by grouping BTC and
1644 -- renumber the draft invoice num, line num
1645 -- and also update the RDLs, ERDLs
1646
1647 PROCEDURE Inv_by_Bill_Trans_Currency(
1648 p_project_id IN NUMBER,
1649 p_request_id IN NUMBER,
1650 x_return_status IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1651
1652 -- This structure is used to track the header information
1653
1654 TYPE inv_header IS RECORD
1655 (current_draft_invoice_num NUMBER,
1656 inv_currency_code VARCHAR2(15),
1657 new_draft_invoice_num NUMBER,
1658 action_flag VARCHAR2(1),
1659 retention_percentage NUMBER
1660 );
1661
1662
1663 -- This structure is used to track the invoice line information
1664
1665 TYPE inv_line IS RECORD
1666 (current_draft_invoice_num NUMBER,
1667 current_line_Num NUMBER,
1668 bill_trans_currency_code VARCHAR2(15),
1669 event_num NUMBER,
1670 revenue_amount NUMBER,
1671 new_draft_invoice_num NUMBER,
1672 new_line_num NUMBER
1673 );
1674
1675 TYPE InvLinesTab IS TABLE OF inv_line
1676 INDEX BY BINARY_INTEGER;
1677
1678 TYPE InvHeadersTab IS TABLE OF inv_header
1679 INDEX BY BINARY_INTEGER;
1680
1681 -- Cursor to select the invoice which is created by current request id
1682 -- and should not be the Cancel invoice, not be the credit memo
1683
1684 CURSOR cur_inv IS
1685 SELECT di.agreement_id,
1686 di.draft_invoice_num,
1687 di.retention_percentage,
1688 dii.line_num,
1689 dii.bill_trans_currency_code,
1690 dii.invoice_line_type,
1691 NVL(dii.event_num,0) event_num,
1692 NVL(evt.revenue_amount,0) revenue_amount
1693 FROM pa_draft_invoices_all di,
1694 pa_draft_invoice_items dii,
1695 pa_events evt
1696 WHERE di.project_id = p_project_id
1697 AND di.project_id = dii.project_id
1698 AND dii.request_id = p_request_id
1699 AND di.request_id = p_request_id
1700 AND di.draft_invoice_num = dii.draft_invoice_num
1701 AND NVL(di.canceled_flag,'N') <> 'Y'
1702 AND NVL(di.cancel_credit_memo_flag,'N') <>'Y'
1703 AND dii.draft_inv_line_num_credited IS NULL
1704 AND dii.event_num = evt.event_num(+)
1705 AND dii.project_id = evt.project_id(+)
1706 AND NVL(dii.event_task_id,-99) = NVL(evt.task_id(+),-99)
1707 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1708 AND dii.invoice_line_type <> 'RETENTION'
1709 ORDER BY di.draft_invoice_num,dii.bill_trans_currency_code,dii.line_num;
1710
1711 /* Cursor added for Bug 3062595 */
1712 CURSOR cur_cm IS
1713 SELECT di.draft_invoice_num,
1714 di.draft_invoice_num_credited,
1715 dii.line_num
1716 FROM pa_draft_invoices_all di, pa_draft_invoice_items dii
1717 WHERE di.project_id = p_project_id
1718 AND di.request_id = p_request_id
1719 AND dii.project_id= di.project_id
1720 AND dii.draft_invoice_num = di.draft_invoice_num
1721 AND di.draft_invoice_num_credited IS NOT NULL
1722 AND nvl(di.write_off_flag,'N') <> 'Y'
1723 AND nvl(di.cancel_credit_memo_flag,'N') <> 'Y'
1724 ORDER BY di.drafT_invoice_num;
1725
1726 --For Bug 4146846
1727 CURSOR c_project_details IS
1728 SELECT projfunc_attr_for_ar_flag,
1729 projfunc_currency_code,
1730 projfunc_bil_exchange_rate,
1731 projfunc_bil_rate_date_code,
1732 projfunc_bil_rate_type,
1733 projfunc_bil_rate_date,
1734 project_currency_code,
1735 invproc_currency_type
1736 FROM pa_projects_all
1737 WHERE project_id = P_Project_Id;
1738
1739 --End of Bug fix 4146846
1740
1741 TmpInvLines InvLinesTab;
1742 TmpInvHeaders InvHeadersTab;
1743
1744 previous_btc VARCHAR2(15);
1745 previous_invoice_num NUMBER:=0;
1746 split_invoice BOOLEAN:=TRUE;
1747 i BINARY_INTEGER:=0;
1748 J BINARY_INTEGER:=1;
1749
1750 last_invoice_num NUMBER:=10;
1751 last_line_num NUMBER:=0;
1752 current_invoice_num NUMBER:=0;
1753 current_line_num NUMBER:=0;
1754 new_invoice_num NUMBER:=0;
1755 new_line_num NUMBER:=0;
1756 l_projfunc_invtrans_rate NUMBER:=0;
1757 l_inv_amount NUMBER:=0;
1758 l_pfc_amount NUMBER:=0;
1759 l_ret_line_num NUMBER:=0;
1760 l_fc_amount NUMBER:=0;
1761 l_pc_amount NUMBER:=0;
1762 l_btc_amount NUMBER:=0;
1763
1764 l_sum_projfunc_bill_amount NUMBER:=0;
1765
1766 --For Bug 4146846 :Added following variables
1767 l_invoice_date DATE := pa_billing.GetInvoiceDate;
1768 l_use_pfc_flag VARCHAR2(1);
1769 l_projfunc_exchange_rate NUMBER;
1770 l_projfunc_exchg_rate_type VARCHAR2(30);
1771 l_projfunc_exchg_rate_date Date;
1772 l_pfc_exchg_rate_date_code VARCHAR2(50);
1773 l_bill_trans_currency_code VARCHAR2(50);
1774 l_pfc_currency_code VARCHAR2(50);
1775 l_project_currency_code VARCHAr2(50);
1776 l_invproc_currency_type VARCHAR2(50);
1777 l_funding_currency_code VARCHAR2(30);
1778 l_invproc_currency_code VARCHAR2(30);
1779 l_inv_rate_date DATE;
1780 l_inv_rate_type VARCHAR2(30);
1781 l_inv_rate NUMBER;
1782 l_ret_status VARCHAR2(30);
1783 l_bill_thru_date DATE;
1784 l_customer_id NUMBER;
1785 --End of bug fix
1786
1787 l_head_inv_exch_rate NUMBER := 1; /* Added for bug 4735682 */
1788 l_head_inv_curr_code VARCHAR2(50); /* Added for bug 4735682 */
1789 inv_num_cached NUMBER := -1; /* Added for bug 4735682 */
1790 l_calc_inv_amount NUMBER := 0; /* Added for bug 4995695 */
1791 l_sum_inv_amount NUMBER; /*Added For Bug 5346566*/
1792
1793
1794 BEGIN
1795 IF g1_debug_mode = 'Y' THEN
1796 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Entering Invoice By Bill Transaction Currency API Type ');
1797 END IF;
1798 TmpInvLines.delete;
1799 TmpInvHeaders.delete;
1800
1801
1802 IF g1_debug_mode = 'Y' THEN
1803 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Request Id : ' || p_request_id);
1804 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Project id : ' || p_project_id);
1805 END IF;
1806 /*For bug 4146846 -a) Obtaining the PFC attributes for the project one time
1807 b) Determing the IPC currency code */
1808
1809 OPEN c_project_details;
1810 FETCH c_project_details INTO l_use_pfc_flag,
1811 l_pfc_currency_code,
1812 l_projfunc_exchange_rate,
1813 l_pfc_exchg_rate_date_code,
1814 l_projfunc_exchg_rate_type,
1815 l_projfunc_exchg_rate_date,
1816 l_project_currency_code,
1817 l_invproc_currency_type;
1818
1819 CLOSE c_project_details;
1820
1821
1822 IF l_invproc_currency_type ='PROJECT_CURRENCY' THEN
1823
1824 l_invproc_currency_code := l_project_currency_code;
1825
1826 ELSIF l_invproc_currency_type ='PROJFUNC_CURRENCY' THEN
1827
1828 l_invproc_currency_code := l_pfc_currency_code;
1829
1830 ELSIF l_invproc_currency_type ='FUNDING_CURRENCY' THEN
1831
1832 SELECT funding_currency_code
1833 INTO l_funding_currency_code
1834 FROM pa_summary_project_fundings
1835 WHERE project_id = p_project_id
1836 AND rownum=1
1837 AND NVL(total_baselined_amount,0) > 0;
1838
1839 l_invproc_currency_code := l_funding_currency_code;
1840
1841 END IF;
1842
1843 /*End of bug fix*/
1844 /* Bug 3062595 - Fix Starts here */
1845 -- Credit memo processing is done here
1846 FOR cm_rec IN cur_cm LOOP
1847
1848 IF g1_debug_mode = 'Y' THEN
1849 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Credit memo processing ');
1850 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Invoice number : ' || cm_rec.draft_invoice_num);
1851 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Line number : ' || cm_rec.line_num);
1852 END IF;
1853
1854 /* Start of bug 4735682 */
1855 IF cm_rec.draft_invoice_num <> inv_num_cached THEN
1856 inv_num_cached := cm_rec.draft_invoice_num;
1857
1858 select nvl(da.INV_EXCHANGE_RATE, 1), da.INV_CURRENCY_CODE
1859 into l_head_inv_exch_rate, l_head_inv_curr_code
1860 from pa_draft_invoices_all da
1861 where project_id = p_project_id
1862 and draft_invoice_num = inv_num_cached;
1863
1864 IF g1_debug_mode = 'Y' THEN
1865 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Exchange rate retrival');
1866 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Using currency code: ' || l_head_inv_curr_code);
1867 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Using exchange rate: ' || l_head_inv_exch_rate);
1868 END IF;
1869 END IF;
1870 /* Bug 4735682 ends */
1871
1872 /* Bug 4735682: Following update clause altered to include the exchange rate *
1873
1874 /* Commented and rewritten for bug 4735682
1875 UPDATE pa_draft_invoice_items dii
1876 SET dii.inv_amount =
1877 (SELECT sum(nvl(rdl.bill_trans_bill_amount, 0)) FROM pa_cust_rev_dist_lines_all rdl
1878 WHERE rdl.project_id = dii.project_id
1879 AND rdl.draft_invoice_num = dii.draft_invoice_num
1880 AND rdl.draft_invoice_item_line_num = dii.line_num)
1881 WHERE dii.project_id = p_project_id
1882 AND dii.draft_invoice_num = cm_rec.draft_invoice_num
1883 AND dii.line_num = cm_rec.line_num
1884 AND dii.invoice_line_type = 'STANDARD';
1885 */
1886 /* Select query for bug 4735682 brought out to comply with 8i.. bug 4995695 */
1887
1888 SELECT sum(nvl(rdl.bill_trans_bill_amount, 0)) * l_head_inv_exch_rate
1889 INTO l_calc_inv_amount
1890 FROM pa_cust_rev_dist_lines_all rdl
1891 WHERE rdl.project_id = p_project_id
1892 AND rdl.draft_invoice_num = inv_num_cached
1893 AND rdl.draft_invoice_item_line_num = cm_rec.line_num;
1894
1895 /* End of bug 4735682 .. bug 4995695 */
1896
1897 /* Bug 4995695.. modified the query for compatibility with 8i */
1898
1899 UPDATE pa_draft_invoice_items dii
1900 SET dii.inv_amount =
1901 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_calc_inv_amount, l_head_inv_curr_code)
1902 WHERE dii.project_id = p_project_id
1903 AND dii.draft_invoice_num = cm_rec.draft_invoice_num
1904 AND dii.line_num = cm_rec.line_num
1905 AND dii.invoice_line_type = 'STANDARD';
1906
1907 UPDATE pa_draft_invoice_items dii
1908 SET inv_amount = bill_trans_bill_amount
1909 WHERE dii.project_id = p_project_id
1910 AND dii.draft_invoice_num = cm_rec.draft_invoice_num
1911 AND dii.line_num = cm_rec.line_num
1912 AND dii.invoice_line_type = 'RETENTION';
1913
1914 END LOOP;
1915 /* Bug 3062595 - Fix Ends here */
1916
1917 -- Get the last invoice number
1918 BEGIN
1919 SELECT NVL(MAX(draft_invoice_num),0) INTO Last_invoice_num
1920 FROM pa_draft_invoices_all
1921 WHERE project_id = p_project_id;
1922 END;
1923 IF g1_debug_mode = 'Y' THEN
1924 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Max Invoice Number: ' || Last_invoice_num);
1925 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Befor the cur_inv Loop .....');
1926 END IF;
1927
1928
1929 FOR inv_rec IN cur_inv LOOP
1930
1931 IF g1_debug_mode = 'Y' THEN
1932 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the cursor cur_inv Loop .....');
1933 END IF;
1934
1935 IF inv_rec.draft_invoice_num <> NVL(previous_invoice_num,0) THEN
1936 -- Reset the previous values
1937
1938 IF g1_debug_mode = 'Y' THEN
1939 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the din <> Prvs_din If ....');
1940 END IF;
1941
1942 previous_btc :=inv_rec.bill_trans_currency_code;
1943 previous_invoice_num :=inv_rec.draft_invoice_num;
1944 split_invoice :=FALSE;
1945 last_line_num := 0 ;
1946 i := i+1;
1947 TmpInvHeaders(i).new_draft_invoice_num :=inv_rec.draft_invoice_num;
1948 TmpInvHeaders(i).current_draft_invoice_num :=inv_rec.draft_invoice_num;
1949 TmpInvHeaders(i).inv_currency_code:=inv_rec.bill_trans_currency_code;
1950 TmpInvHeaders(i).action_flag:='U';
1951 TmpInvHeaders(i).retention_percentage:= inv_rec.retention_percentage;
1952 END IF;
1953
1954 current_invoice_num := inv_rec.draft_invoice_num;
1955 new_invoice_num := inv_rec.draft_invoice_num;
1956
1957 current_line_num := inv_rec.line_num;
1958 new_line_num := inv_rec.line_num;
1959
1960
1961 IF inv_rec.bill_trans_currency_code <> previous_btc THEN
1962
1963 IF g1_debug_mode = 'Y' THEN
1964 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the BTC <> Prvs_btc If .....');
1965 END IF;
1966
1967 -- If the bill transaction changes
1968 -- Create new invoice
1969 -- Reset the invoice line num
1970 i := i+1;
1971 last_line_num := 0;
1972 previous_btc :=inv_rec.bill_trans_currency_code;
1973 split_invoice :=TRUE;
1974 last_invoice_num := last_invoice_num +1;
1975 TmpInvHeaders(i).new_draft_invoice_num :=last_invoice_num;
1976 TmpInvHeaders(i).current_draft_invoice_num :=inv_rec.draft_invoice_num;
1977 TmpInvHeaders(i).inv_currency_code:=inv_rec.bill_trans_currency_code;
1978 TmpInvHeaders(i).action_flag:='I';
1979 TmpInvHeaders(i).retention_percentage:= inv_rec.retention_percentage;
1980
1981 new_invoice_num :=TmpInvHeaders(i).new_draft_invoice_num;
1982 new_line_num := 0;
1983 new_invoice_num := last_invoice_num;
1984
1985 END IF;
1986
1987 IF previous_btc = inv_rec.bill_trans_currency_code AND
1988 (split_invoice) THEN
1989
1990 IF g1_debug_mode = 'Y' THEN
1991 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the Prvs_btc = btc If .....');
1992 END IF;
1993
1994 -- Add the invoice lines into new invoice lines array
1995 last_line_num := last_line_num +1;
1996 new_line_num := last_line_num;
1997 new_invoice_num := last_invoice_num;
1998 ELSE
1999
2000 IF g1_debug_mode = 'Y' THEN
2001 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the Prvs_btc = btc Else .....');
2002 END IF;
2003
2004 last_line_num := last_line_num +1;
2005 new_line_num := last_line_num;
2006 END IF;
2007
2008 IF g1_debug_mode = 'Y' THEN
2009 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Before assigning to TmpInvlines .....');
2010 END IF;
2011
2012 TmpInvLines(j).current_draft_invoice_num := current_invoice_num;
2013 TmpInvLines(j).new_draft_invoice_num := new_invoice_num;
2014 TmpInvLines(j).current_line_num := current_line_num;
2015 TmpInvLines(j).new_line_num :=new_line_num;
2016 TmpInvLines(j).event_num :=inv_rec.event_num;
2017 TmpInvLines(j).revenue_amount :=inv_rec.revenue_amount;
2018
2019 IF g1_debug_mode = 'Y' THEN
2020 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' j = ' || j);
2021 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Old Inv : ' || TmpInvLines(j).current_draft_invoice_num);
2022 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Old Line Num : ' || TmpInvLines(j).current_line_num);
2023 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' New Inv : ' || TmpInvLines(j).new_draft_invoice_num);
2024 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' New Line Num : ' || TmpInvLines(j).new_line_num);
2025 END IF;
2026
2027 j:= j+1;
2028
2029 END LOOP;
2030
2031
2032 IF g1_debug_mode = 'Y' THEN
2033 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Out side the cur_inv Loop .....');
2034 END IF;
2035
2036 -- Process only if the project multi bill transaction currencies
2037
2038 IF (TmpInvHeaders.EXISTS(TmpInvHeaders.first)) THEN
2039 -- Reset the line number for newly created invoices
2040 /* Bug 2870248 fix starts*/
2041 FOR k IN TmpInvLines.FIRST.. TmpInvLines.LAST LOOP
2042 UPDATE pa_draft_invoice_items
2043 SET line_num = TmpInvLines(k).current_line_num+1000000
2044 WHERE project_id = p_project_id
2045 AND request_id = p_request_id
2046 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2047 AND line_num = TmpInvLines(k).current_line_num;
2048
2049 /* Added for bug 3144517.Fix is similar to that in 2870248 */
2050
2051 IF TmpInvLines(k).event_num =0 THEN
2052 --Update the RDLS
2053 UPDATE pa_cust_rev_dist_lines
2054 SET draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000
2055 WHERE project_id = p_project_id
2056 AND request_id = p_request_id
2057 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2058 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num;
2059
2060 ELSIF TmpInvLines(k).revenue_amount <> 0 THEN
2061 -- Update only if the event is revenue event
2062
2063 UPDATE pa_cust_event_rdl_all
2064 SET draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000
2065 WHERE project_id = p_project_id
2066 AND request_id = p_request_id
2067 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2068 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num;
2069
2070 END IF;
2071
2072 /*End of fix for bug 3144517 */
2073
2074 END LOOP;
2075 /* Bug 2870248 Ends */
2076
2077 FOR k IN REVERSE TmpInvLines.FIRST..TmpInvLines.LAST LOOP
2078
2079 -- Update the draft invoice items
2080 -- IF (TmpInvLines(k).current_draft_invoice_num <>
2081 -- TmpInvLInes(k).new_draft_invoice_num ) OR
2082 -- ( TmpInvLines(k).current_line_num <>
2083 -- TmpInvLines(k).new_line_num) THEN
2084 --PA_MCB_INVOICE_PKG.log_message('Update Invoice Lines ');
2085
2086 -- PA_MCB_INVOICE_PKG.log_message('O Inv Num : ' || TmpInvLines(k).current_draft_invoice_num ||
2087 -- ' N Inv Num : ' || TmpInvLInes(k).new_draft_invoice_num ||
2088 -- ' O LineNum : ' || TmpInvLInes(k).current_line_num ||
2089 -- ' N Inv Num : ' || TmpInvLInes(k).new_line_num);
2090
2091 UPDATE pa_draft_invoice_items
2092 SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
2093 line_num = TmpInvLines(k).new_line_num,
2094 inv_amount= bill_trans_bill_amount
2095 WHERE project_id = p_project_id
2096 AND request_id = p_request_id
2097 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2098 AND line_num = TmpInvLines(k).current_line_num+1000000;
2099 /* Adding with Huge number for bug2870248 */
2100
2101 /*fix for bug 3144517 */
2102 IF TmpInvLines(k).event_num =0 THEN
2103 --Update the RDLS
2104 UPDATE pa_cust_rev_dist_lines
2105 SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
2106 draft_invoice_item_line_num = TmpInvLines(k).new_line_num
2107 WHERE project_id = p_project_id
2108 AND request_id = p_request_id
2109 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2110 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000;
2111 /*For bug 3144517*/
2112 ELSIF TmpInvLines(k).revenue_amount <> 0 THEN
2113 -- Update only if the event is revenue event
2114
2115 UPDATE pa_cust_event_rdl_all
2116 SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
2117 draft_invoice_item_line_num = TmpInvLines(k).new_line_num
2118 WHERE project_id = p_project_id
2119 AND request_id = p_request_id
2120 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2121 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000;
2122 /*For bug 3144517*/
2123 END IF;
2124 -- END IF;
2125
2126 END LOOP;
2127
2128 -- Final Update to calculate the rate between invoice currency and project functional currency
2129
2130 update pa_draft_invoice_items
2131 set inv_amount = 0
2132 where project_id = P_Project_Id
2133 and invoice_line_type = 'NET ZERO ADJUSTMENT'
2134 and request_id = p_request_id;
2135
2136 -- Update the conversion rate for ITC to PFC
2137
2138 FOR k IN TmpInvHeaders.FIRST..TmpInvHeaders.LAST LOOP
2139 -- Insert the New Invoice Headers
2140 -- All the values will be the same exception draft_invoice_num
2141 -- , inv_currency_code
2142
2143
2144 l_projfunc_invtrans_rate :=0;
2145 l_inv_amount :=0;
2146 l_pfc_amount :=0;
2147 l_fc_amount :=0;
2148 l_pc_amount :=0;
2149 l_btc_amount :=0;
2150 l_ret_line_num :=0;
2151
2152 SELECT NVL(sum(dii.inv_amount),0),
2153 NVL(sum(dii.projfunc_bill_amount),0),
2154 NVL(sum(dii.project_bill_amount),0),
2155 NVL(sum(dii.bill_trans_bill_amount),0),
2156 NVL(sum(dii.funding_bill_amount),0),
2157 NVL(MAX(dii.line_num),0) +1
2158 INTO l_inv_amount,
2159 l_pfc_amount,
2160 l_pc_amount,
2161 l_btc_amount,
2162 l_fc_amount,
2163 l_ret_line_num
2164 FROM pa_draft_invoice_items dii
2165 WHERE dii.project_id = P_Project_Id
2166 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2167 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
2168 AND dii.invoice_line_type <> 'RETENTION';
2169
2170 IF g1_debug_mode = 'Y' THEN
2171 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Old Inv Num : ' || TmpInvHeaders(k).current_draft_invoice_num ||
2172 ' New Inv Num : ' || TmpInvHeaders(k).new_draft_invoice_num ||
2173 ' New Inv Curr : ' || TmpInvHeaders(k).inv_currency_code ||
2174 ' Action Flag : ' || TmpInvHeaders(k).action_flag ||
2175 ' INV AMOUNT : ' || l_inv_amount ||
2176 ' PFC AMOUNT : ' || l_pfc_amount );
2177 END IF;
2178
2179 /* To avoid division by zero error , changing the logic to calculate l_projfunc_invtrans_rate - For bug 2961983
2180 commenting the following code to calculate l_projfunc_invtrans_rate and added the new logic following the same */
2181
2182 /* IF NVL(l_inv_amount,0) <> 0 AND NVL(l_pfc_amount,0) <> 0 THEN
2183 l_projfunc_invtrans_rate := NVL(l_inv_amount,0)/NVL(l_pfc_amount,0);
2184 END IF; Commented for bug 2961983 */
2185
2186 /* Added the following for bug 2961983 */
2187
2188 /* Commented for 3436063
2189 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
2190 INTO l_projfunc_invtrans_rate
2191 FROM pa_draft_invoice_items dii
2192 WHERE dii.project_id = P_Project_Id
2193 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2194 AND nvl(dii.projfunc_bill_amount,0) <> 0
2195 AND rownum=1;
2196 */
2197 /* End of Changes for bug 2961983 */
2198
2199 /****Code added for 3436063****/
2200 SELECT sum(NVL(dii.projfunc_bill_amount,0))
2201 INTO l_sum_projfunc_bill_amount
2202 FROM pa_draft_invoice_items dii
2203 WHERE dii.project_id = P_Project_Id
2204 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num;
2205
2206 /*** For Bug 5346566 ***/
2207 SELECT sum(NVL(dii.inv_amount,0))
2208 INTO l_sum_inv_amount
2209 FROM pa_draft_invoice_items dii
2210 WHERE dii.project_id = P_Project_Id
2211 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num;
2212 /*** End of code change for Bug 5346566 ***/
2213
2214 IF l_sum_projfunc_bill_amount <> 0 AND l_sum_inv_amount <> 0 /*** Condition added for bug 5346566 ***/
2215 THEN
2216 SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
2217 INTO l_projfunc_invtrans_rate
2218 FROM pa_draft_invoice_items dii
2219 WHERE dii.project_id = P_Project_Id
2220 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2221 having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
2222 ELSE
2223 /* Added begin and end to handle the exception for bug 8666892 */
2224 BEGIN
2225 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
2226 INTO l_projfunc_invtrans_rate
2227 FROM pa_draft_invoice_items dii
2228 WHERE dii.project_id = P_Project_Id
2229 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2230 AND nvl(dii.projfunc_bill_amount,0) <> 0
2231 AND rownum=1;
2232
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 l_projfunc_invtrans_rate := 0;
2236 END;
2237 END IF;
2238 /****End of code added for 3436063****/
2239
2240 -- Insert if the action flag is I
2241
2242 IF TmpInvHeaders(k).action_flag ='I' THEN
2243
2244 IF g1_debug_mode = 'Y' THEN
2245 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Insert New Invoices ');
2246 END IF;
2247
2248 INSERT INTO pa_draft_invoices_all
2249 (project_id,
2250 draft_invoice_num,
2251 last_update_date,
2252 last_updated_by,
2253 creation_date,
2254 created_by,
2255 transfer_status_code,
2256 generation_error_flag,
2257 agreement_id,
2258 pa_date,
2259 request_id,
2260 program_application_id,
2261 program_id,
2262 program_update_date,
2263 customer_bill_split,
2264 bill_through_date,
2265 invoice_comment,
2266 approved_date,
2267 approved_by_person_id,
2268 released_date,
2269 released_by_person_id,
2270 invoice_date,
2271 ra_invoice_number,
2272 transferred_date,
2273 transfer_rejection_reason,
2274 unearned_revenue_cr,
2275 unbilled_receivable_dr,
2276 gl_date,
2277 system_reference,
2278 draft_invoice_num_credited ,
2279 canceled_flag,
2280 cancel_credit_memo_flag ,
2281 write_off_flag,
2282 converted_flag,
2283 extracted_date,
2284 last_update_login,
2285 attribute_category,
2286 attribute1,
2287 attribute2,
2288 attribute3,
2289 attribute4,
2290 attribute5,
2291 attribute6,
2292 attribute7,
2293 attribute8,
2294 attribute9,
2295 attribute10,
2296 retention_percentage,
2297 invoice_set_id,
2298 org_id,
2299 inv_currency_code,
2300 inv_rate_type,
2301 inv_rate_date,
2302 inv_exchange_rate,
2303 bill_to_address_id,
2304 ship_to_address_id ,
2305 prc_generated_flag,
2306 receivable_code_combination_id,
2307 rounding_code_combination_id,
2308 unbilled_code_combination_id,
2309 unearned_code_combination_id,
2310 woff_code_combination_id,
2311 acctd_curr_code,
2312 acctd_rate_type,
2313 acctd_rate_date,
2314 acctd_exchg_rate,
2315 language,
2316 cc_invoice_group_code ,
2317 cc_project_id,
2318 ib_ap_transfer_status_code,
2319 ib_ap_transfer_error_code ,
2320 invproc_currency_code,
2321 projfunc_invtrans_rate_type,
2322 projfunc_invtrans_rate_date ,
2323 projfunc_invtrans_ex_rate,
2324 customer_id,
2325 bill_to_customer_id,
2326 ship_to_customer_id,
2327 bill_to_contact_id,
2328 ship_to_contact_id)
2329 SELECT project_id,
2330 TmpInvHeaders(k).new_draft_invoice_num,
2331 last_update_date,
2332 last_updated_by,
2333 creation_date,
2334 created_by,
2335 transfer_status_code,
2336 generation_error_flag,
2337 agreement_id,
2338 pa_date,
2339 request_id,
2340 program_application_id,
2341 program_id,
2342 program_update_date,
2343 customer_bill_split,
2344 bill_through_date,
2345 invoice_comment,
2346 approved_date,
2347 approved_by_person_id,
2348 released_date,
2349 released_by_person_id,
2350 invoice_date,
2351 ra_invoice_number,
2352 transferred_date,
2353 transfer_rejection_reason,
2354 unearned_revenue_cr,
2355 unbilled_receivable_dr,
2356 gl_date,
2357 system_reference,
2358 draft_invoice_num_credited ,
2359 canceled_flag,
2360 cancel_credit_memo_flag ,
2361 write_off_flag,
2362 converted_flag,
2363 extracted_date,
2364 last_update_login,
2365 attribute_category,
2366 attribute1,
2367 attribute2,
2368 attribute3,
2369 attribute4,
2370 attribute5,
2371 attribute6,
2372 attribute7,
2373 attribute8,
2374 attribute9,
2375 attribute10,
2376 retention_percentage,
2377 invoice_set_id,
2378 org_id,
2379 TmpInvHeaders(k).inv_currency_code,
2380 NULL, --'User'
2381 NULL, --sysdate
2382 NULL, --1
2383 bill_to_address_id,
2384 ship_to_address_id ,
2385 prc_generated_flag,
2386 receivable_code_combination_id,
2387 rounding_code_combination_id,
2388 unbilled_code_combination_id,
2389 unearned_code_combination_id,
2390 woff_code_combination_id,
2391 acctd_curr_code,
2392 acctd_rate_type,
2393 acctd_rate_date,
2394 acctd_exchg_rate,
2395 language,
2396 cc_invoice_group_code ,
2397 cc_project_id,
2398 ib_ap_transfer_status_code,
2399 ib_ap_transfer_error_code ,
2400 invproc_currency_code,
2401 'User',
2402 Sysdate ,
2403 l_projfunc_invtrans_rate,
2404 customer_id,
2405 bill_to_customer_id,
2406 ship_to_customer_id,
2407 bill_to_contact_id,
2408 ship_to_contact_id
2409 FROM pa_draft_invoices_all
2410 WHERE project_id = p_project_id
2411 AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num;
2412
2413 -- Handling Retention lines
2414
2415 /* Commented out for Retention enhancements
2416 IF NVL(TmpInvHeaders(k).retention_percentage,0) <> 0 THEN
2417
2418 IF g1_debug_mode = 'Y' THEN
2419 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Insert Ret Line for New Invoice ');
2420 END IF;
2421
2422 -- Insert new retention line
2423
2424 INSERT INTO pa_draft_invoice_items(
2425 project_id,
2426 draft_invoice_num,
2427 line_num,
2428 last_update_date,
2429 last_updated_by,
2430 creation_date,
2431 created_by,
2432 amount,
2433 text,
2434 invoice_line_type,
2435 request_id,
2436 program_application_id,
2437 program_id,
2438 program_update_date,
2439 unearned_revenue_cr,
2440 unbilled_receivable_dr,
2441 task_id,
2442 event_task_id,
2443 event_num,
2444 ship_to_address_id,
2445 taxable_flag,
2446 draft_inv_line_num_credited,
2447 last_update_login,
2448 inv_amount,
2449 output_tax_classification_code,
2450 output_tax_exempt_flag,
2451 output_tax_exempt_reason_code,
2452 output_tax_exempt_number,
2453 acct_amount,
2454 rounding_amount,
2455 unbilled_rounding_amount_dr,
2456 unearned_rounding_amount_cr,
2457 translated_text,
2458 cc_rev_code_combination_id,
2459 cc_project_id,
2460 cc_tax_task_id,
2461 project_currency_code,
2462 project_bill_amount,
2463 projfunc_currency_code,
2464 projfunc_bill_amount,
2465 funding_currency_code,
2466 funding_bill_amount,
2467 invproc_currency_code,
2468 bill_trans_currency_code,
2469 bill_trans_bill_amount)
2470 SELECT
2471 project_id,
2472 TmpInvHeaders(k).new_draft_invoice_num,
2473 l_ret_line_num,
2474 last_update_date,
2475 last_updated_by,
2476 creation_date,
2477 created_by,
2478 NVL(l_btc_amount,0) *
2479 ( NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2480 text,
2481 invoice_line_type,
2482 request_id,
2483 program_application_id,
2484 program_id,
2485 program_update_date,
2486 unearned_revenue_cr,
2487 unbilled_receivable_dr,
2488 task_id,
2489 event_task_id,
2490 event_num,
2491 ship_to_address_id,
2492 taxable_flag,
2493 draft_inv_line_num_credited,
2494 last_update_login,
2495 NVL(l_btc_amount,0) *
2496 ( NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2497 output_tax_classification_code,
2498 output_tax_exempt_flag,
2499 output_tax_exempt_reason_code,
2500 output_tax_exempt_number,
2501 acct_amount,
2502 rounding_amount,
2503 unbilled_rounding_amount_dr,
2504 unearned_rounding_amount_cr,
2505 translated_text,
2506 cc_rev_code_combination_id,
2507 cc_project_id,
2508 cc_tax_task_id,
2509 project_currency_code,
2510 NVL(l_pc_amount,0) *
2511 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2512 projfunc_currency_code,
2513 NVL(l_pfc_amount,0) *
2514 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2515 funding_currency_code,
2516 NVL(l_fc_amount,0) *
2517 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2518 invproc_currency_code,
2519 TmpInvHeaders(k).inv_currency_code,
2520 NVL(l_btc_amount,0) *
2521 (NVL(TmpInvHeaders(k).retention_percentage,0)/100)
2522 FROM pa_draft_invoice_items
2523 WHERE project_id = p_project_id
2524 AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num
2525 AND invoice_Line_type ='RETENTION';
2526
2527
2528
2529 END IF; */
2530
2531 ELSIF TmpInvHeaders(k).action_flag ='U' THEN
2532
2533 ---- Existing Invoice, update BTC currency code and rates
2534 IF g1_debug_mode = 'Y' THEN
2535 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Update Existing Invoice ');
2536 END IF;
2537
2538 UPDATE pa_draft_invoices_all
2539 set inv_currency_code = TmpInvHeaders(k).inv_currency_code,
2540 inv_rate_type = NULL, --'User',
2541 inv_rate_date = NULL, --sysdate,
2542 inv_exchange_rate = NULL, --1,
2543 projfunc_invtrans_rate_type = 'User',
2544 /* projfunc_invtrans_rate_date = sysdate, commented for bug 5141073 */
2545 projfunc_invtrans_rate_date = invoice_date, /* Added for bug 5141073 */
2546 projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
2547 WHERE project_id = P_Project_Id
2548 AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num;
2549
2550 -- Reset the retention line
2551
2552 /* Commented out for Retention Enhancements
2553
2554 IF NVL(TmpInvHeaders(k).retention_percentage,0) <> 0 THEN
2555
2556 IF g1_debug_mode = 'Y' THEN
2557 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Update Existing Invoice Retention Line ');
2558 END IF;
2559
2560 UPDATE pa_draft_invoice_items
2561 SET bill_trans_currency_code = TmpInvHeaders(k).inv_currency_code,
2562 projfunc_bill_amount =
2563 NVL(l_pfc_amount,0) *
2564 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2565 project_bill_amount =
2566 NVL(l_pc_amount,0) *
2567 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2568 funding_bill_amount =
2569 NVL(l_fc_amount,0) *
2570 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2571 bill_trans_bill_amount =
2572 NVL(l_btc_amount,0) *
2573 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2574 amount =
2575 NVL(l_btc_amount,0) *
2576 NVL(TmpInvHeaders(k).retention_percentage,0),
2577 inv_amount =
2578 NVL(l_btc_amount,0) *
2579 (NVL(TmpInvHeaders(k).retention_percentage,0)/100)
2580 WHERE draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num
2581 AND project_id = p_project_id
2582 AND invoice_line_type = 'RETENTION';
2583 END IF; */
2584
2585 END IF;
2586
2587 END LOOP;
2588
2589 END IF;
2590 --For bug 4146846 : Call CAl_Conversion_Attr procedure which computes the Conversion Attributes
2591 -- As per USE PFC flag and BTC,PFC and IPC
2592 FOR k IN TmpInvLines.FIRST.. TmpInvLines.LAST LOOP
2593 SELECT agr.customer_id,
2594 i.bill_through_date
2595 INTO l_customer_id,
2596 l_bill_thru_date
2597 FROM pa_draft_invoices_all i,
2598 pa_agreements_all agr
2599 WHERE i.project_id = p_project_id
2600 AND i.request_id = p_request_id
2601 AND i.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2602 AND NVL(i.generation_error_flag,'N')= 'N'
2603 AND i.agreement_id = agr.agreement_id;
2604
2605 SELECT NVL(ppc.inv_rate_date,NVL(l_invoice_date,l_bill_thru_date)),
2606 ppc.inv_rate_type,
2607 ppc.inv_exchange_rate
2608 INTO l_inv_rate_date,
2609 l_inv_rate_type,
2610 l_inv_rate
2611 FROM pa_project_customers ppc
2612 WHERE ppc.project_id = P_Project_Id
2613 AND ppc.customer_id = l_customer_id;
2614 Cal_Conversion_Attr ( p_project_id => p_project_id,
2615 p_draft_invoice_num => TmpInvHeaders(k).new_draft_invoice_num,
2616 p_use_pfc_flag => l_use_pfc_flag,
2617 p_pfc_currency_code => l_pfc_currency_code,
2618 p_pfc_ex_rate => l_projfunc_exchange_rate,
2619 p_pfc_ex_rate_date_code => l_pfc_exchg_rate_date_code,
2620 p_pfc_rate_type => l_projfunc_exchg_rate_type,
2621 p_pfc_rate_date => l_projfunc_exchg_rate_date,
2622 p_invproc_currency_code => l_invproc_currency_code,
2623 p_inv_ex_rate => l_inv_rate,
2624 p_inv_rate_type => l_inv_rate_type,
2625 p_inv_rate_date => l_inv_rate_date,
2626 p_btc_currency_code => TmpInvHeaders(k).inv_currency_code,
2627 p_bill_thru_date => l_bill_thru_date,
2628 x_status => l_ret_status);
2629 IF l_ret_status <> 'Y' THEN
2630 IF g1_debug_mode = 'Y' THEN
2631 PA_MCB_INVOICE_PKG.log_message(' Error in Inv_by_Bill_Trans_Currency '||sqlerrm(sqlcode));
2632 END IF;
2633 END IF;
2634
2635 END LOOP;
2636
2637 /*End of bug 4146846*/
2638
2639 EXCEPTION
2640 WHEN others THEN
2641 IF g1_debug_mode = 'Y' THEN
2642 PA_MCB_INVOICE_PKG.log_message(' Error in Inv_by_Bill_Trans_Currency '||sqlerrm(sqlcode));
2643 END IF;
2644 x_return_status := sqlerrm( sqlcode );
2645
2646 -- RAISE;
2647 END Inv_by_Bill_Trans_Currency;
2648 END PA_MCB_INVOICE_PKG;