[Home] [Help]
PACKAGE BODY: APPS.PA_MCB_INVOICE_PKG
Source
1 PACKAGE BODY PA_MCB_INVOICE_PKG AS
2 /* $Header: PAXMCIUB.pls 120.12 2007/10/31 12:48:24 rmandali 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 SELECT NVL(dii.bill_trans_bill_amount,0)/NVL(dii.projfunc_bill_amount,0)
1521 INTO l_rate
1522 FROM pa_draft_invoice_items dii
1523 WHERE dii.project_id = P_Project_Id
1524 AND dii.draft_invoice_num = p_draft_invoice_num
1525 AND nvl(dii.projfunc_bill_amount,0) <> 0
1526 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1527 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1528 AND dii.invoice_line_type <> 'RETENTION'
1529 AND rownum=1;
1530 END IF;
1531 /* End of code change for Bug 6084445 */
1532
1533 /* Commented for Bug 6084445 Start
1534 SELECT NVL(sum(dii.bill_trans_bill_amount),0) / nvl(sum(dii.projfunc_bill_amount),0)
1535 INTO l_rate
1536 FROM pa_draft_invoice_items dii
1537 WHERE dii.project_id = p_project_id
1538 AND dii.draft_invoice_num = p_draft_invoice_num
1539 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1540 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1541 AND dii.invoice_line_type <> 'RETENTION';
1542 Commented for Bug 6084445 End */
1543
1544 UPDATE pa_draft_invoices_all
1545 SET projfunc_invtrans_rate_type = 'User'
1546 ,projfunc_invtrans_rate_date = NVL(l_invoice_date,p_bill_thru_date)
1547 ,projfunc_invtrans_ex_rate = l_rate
1548 WHERE project_id = p_project_id
1549 AND draft_invoice_num = p_draft_invoice_num;
1550
1551
1552 ELSE /* End Bug#5762081 */
1553
1554
1555 PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || 'Before the Select statement ');
1556
1557 SELECT NVL(sum(dii.bill_trans_bill_amount),0)
1558 INTO tmp_bill_trans_amount_tab(1)
1559 FROM pa_draft_invoice_items dii
1560 WHERE dii.project_id = p_project_id
1561 AND dii.draft_invoice_num = p_draft_invoice_num
1562 AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
1563 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1564 AND dii.invoice_line_type <> 'RETENTION';
1565
1566 tmp_bill_trans_rate_date_tab(1) :=NVL(p_inv_rate_date,NVL(l_invoice_date,p_bill_thru_date)); -- Bug 4298230
1567
1568 --Converting the PFC to BTC as per Rate Types and DAtes defined in Ct. Screen
1569 tmp_rate_tab(1) := p_inv_ex_rate; --Bug 4298230
1570 tmp_bill_trans_rate_type_tab(1) := p_inv_rate_type; --Bug 4298230
1571
1572 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk (
1573 p_from_currency_tab => tmp_pfc_currency_code_tab ,
1574 p_to_currency_tab => tmp_btc_currency_code_tab,
1575 p_conversion_date_tab => tmp_bill_trans_rate_date_tab,
1576 p_conversion_type_tab => tmp_bill_trans_rate_type_tab,
1577 p_amount_tab => tmp_bill_trans_amount_tab,
1578 p_user_validate_flag_tab => tmp_user_validate_flag_tab,
1579 p_converted_amount_tab => tmp_bill_trans_amount_tab,
1580 p_denominator_tab => tmp_denominator_tab,
1581 p_numerator_tab => tmp_numerator_tab,
1582 p_rate_tab => tmp_rate_tab,
1583 x_status_tab => tmp_status_tab,
1584 p_conversion_between => 'PFC_BT',
1585 p_cache_flag => 'N');
1586
1587 -- Added the below code for Bug 4298230
1588
1589 IF g1_debug_mode = 'Y' THEN
1590 PA_MCB_INVOICE_PKG.log_message('cal_conversion_attr: status :: '||tmp_status_tab(1));
1591 END IF;
1592
1593 IF tmp_status_tab(1) = 'PA_NO_EXCH_RATE_EXISTS_PFC_BT' THEN
1594
1595 OPEN reject_reason (tmp_status_tab(1));
1596 FETCH reject_reason INTO l_reject_reason_meaning;
1597 CLOSE reject_reason;
1598
1599 UPDATE pa_draft_invoices_all
1600 SET generation_error_flag = 'Y',
1601 TRANSFER_REJECTION_REASON = l_reject_reason_meaning
1602 WHERE project_id = p_project_id
1603 AND draft_invoice_num = p_draft_invoice_num;
1604
1605 ELSE
1606
1607 UPDATE pa_draft_invoices_all
1608 SET projfunc_invtrans_rate_type = p_inv_rate_type
1609 ,projfunc_invtrans_rate_date = NVL(p_inv_rate_date,NVL(l_invoice_date,p_bill_thru_date))
1610 ,projfunc_invtrans_ex_rate = NVL(p_inv_ex_rate,tmp_rate_tab(1))
1611 WHERE project_id = p_project_id
1612 AND draft_invoice_num = p_draft_invoice_num;
1613
1614 END IF; -- End of IF added for the Bug 4298230
1615
1616 END IF; -- Bug#5762081
1617
1618 END IF;
1619 END IF;
1620 x_status := 'Y';
1621
1622 EXCEPTION
1623 WHEN OTHERS
1624 THEN
1625 IF g1_debug_mode = 'Y' THEN
1626 PA_MCB_INVOICE_PKG.log_message('cal_Conversion_Attr: ' || ' Sql Error : ' || sqlerrm);
1627 END IF;
1628 RAISE;
1629 END Cal_Conversion_Attr;
1630
1631 --====================================================
1632 -- Procedure Invoice_by_Bill_Trans_Currency
1633 -- This procedure will be called only if the project is invoice by
1634 -- bill transaction currency setup.
1635 -- Procedure will split the invoice by grouping BTC and
1636 -- renumber the draft invoice num, line num
1637 -- and also update the RDLs, ERDLs
1638
1639 PROCEDURE Inv_by_Bill_Trans_Currency(
1640 p_project_id IN NUMBER,
1641 p_request_id IN NUMBER,
1642 x_return_status IN OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1643
1644 -- This structure is used to track the header information
1645
1646 TYPE inv_header IS RECORD
1647 (current_draft_invoice_num NUMBER,
1648 inv_currency_code VARCHAR2(15),
1649 new_draft_invoice_num NUMBER,
1650 action_flag VARCHAR2(1),
1651 retention_percentage NUMBER
1652 );
1653
1654
1655 -- This structure is used to track the invoice line information
1656
1657 TYPE inv_line IS RECORD
1658 (current_draft_invoice_num NUMBER,
1659 current_line_Num NUMBER,
1660 bill_trans_currency_code VARCHAR2(15),
1661 event_num NUMBER,
1662 revenue_amount NUMBER,
1663 new_draft_invoice_num NUMBER,
1664 new_line_num NUMBER
1665 );
1666
1667 TYPE InvLinesTab IS TABLE OF inv_line
1668 INDEX BY BINARY_INTEGER;
1669
1670 TYPE InvHeadersTab IS TABLE OF inv_header
1671 INDEX BY BINARY_INTEGER;
1672
1673 -- Cursor to select the invoice which is created by current request id
1674 -- and should not be the Cancel invoice, not be the credit memo
1675
1676 CURSOR cur_inv IS
1677 SELECT di.agreement_id,
1678 di.draft_invoice_num,
1679 di.retention_percentage,
1680 dii.line_num,
1681 dii.bill_trans_currency_code,
1682 dii.invoice_line_type,
1683 NVL(dii.event_num,0) event_num,
1684 NVL(evt.revenue_amount,0) revenue_amount
1685 FROM pa_draft_invoices_all di,
1686 pa_draft_invoice_items dii,
1687 pa_events evt
1688 WHERE di.project_id = p_project_id
1689 AND di.project_id = dii.project_id
1690 AND dii.request_id = p_request_id
1691 AND di.request_id = p_request_id
1692 AND di.draft_invoice_num = dii.draft_invoice_num
1693 AND NVL(di.canceled_flag,'N') <> 'Y'
1694 AND NVL(di.cancel_credit_memo_flag,'N') <>'Y'
1695 AND dii.draft_inv_line_num_credited IS NULL
1696 AND dii.event_num = evt.event_num(+)
1697 AND dii.project_id = evt.project_id(+)
1698 AND NVL(dii.event_task_id,-99) = NVL(evt.task_id(+),-99)
1699 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
1700 AND dii.invoice_line_type <> 'RETENTION'
1701 ORDER BY di.draft_invoice_num,dii.bill_trans_currency_code,dii.line_num;
1702
1703 /* Cursor added for Bug 3062595 */
1704 CURSOR cur_cm IS
1705 SELECT di.draft_invoice_num,
1706 di.draft_invoice_num_credited,
1707 dii.line_num
1708 FROM pa_draft_invoices_all di, pa_draft_invoice_items dii
1709 WHERE di.project_id = p_project_id
1710 AND di.request_id = p_request_id
1711 AND dii.project_id= di.project_id
1712 AND dii.draft_invoice_num = di.draft_invoice_num
1713 AND di.draft_invoice_num_credited IS NOT NULL
1714 AND nvl(di.write_off_flag,'N') <> 'Y'
1715 AND nvl(di.cancel_credit_memo_flag,'N') <> 'Y'
1716 ORDER BY di.drafT_invoice_num;
1717
1718 --For Bug 4146846
1719 CURSOR c_project_details IS
1720 SELECT projfunc_attr_for_ar_flag,
1721 projfunc_currency_code,
1722 projfunc_bil_exchange_rate,
1723 projfunc_bil_rate_date_code,
1724 projfunc_bil_rate_type,
1725 projfunc_bil_rate_date,
1726 project_currency_code,
1727 invproc_currency_type
1728 FROM pa_projects_all
1729 WHERE project_id = P_Project_Id;
1730
1731 --End of Bug fix 4146846
1732
1733 TmpInvLines InvLinesTab;
1734 TmpInvHeaders InvHeadersTab;
1735
1736 previous_btc VARCHAR2(15);
1737 previous_invoice_num NUMBER:=0;
1738 split_invoice BOOLEAN:=TRUE;
1739 i BINARY_INTEGER:=0;
1740 J BINARY_INTEGER:=1;
1741
1742 last_invoice_num NUMBER:=10;
1743 last_line_num NUMBER:=0;
1744 current_invoice_num NUMBER:=0;
1745 current_line_num NUMBER:=0;
1746 new_invoice_num NUMBER:=0;
1747 new_line_num NUMBER:=0;
1748 l_projfunc_invtrans_rate NUMBER:=0;
1749 l_inv_amount NUMBER:=0;
1750 l_pfc_amount NUMBER:=0;
1751 l_ret_line_num NUMBER:=0;
1752 l_fc_amount NUMBER:=0;
1753 l_pc_amount NUMBER:=0;
1754 l_btc_amount NUMBER:=0;
1755
1756 l_sum_projfunc_bill_amount NUMBER:=0;
1757
1758 --For Bug 4146846 :Added following variables
1759 l_invoice_date DATE := pa_billing.GetInvoiceDate;
1760 l_use_pfc_flag VARCHAR2(1);
1761 l_projfunc_exchange_rate NUMBER;
1762 l_projfunc_exchg_rate_type VARCHAR2(30);
1763 l_projfunc_exchg_rate_date Date;
1764 l_pfc_exchg_rate_date_code VARCHAR2(50);
1765 l_bill_trans_currency_code VARCHAR2(50);
1766 l_pfc_currency_code VARCHAR2(50);
1767 l_project_currency_code VARCHAr2(50);
1768 l_invproc_currency_type VARCHAR2(50);
1769 l_funding_currency_code VARCHAR2(30);
1770 l_invproc_currency_code VARCHAR2(30);
1771 l_inv_rate_date DATE;
1772 l_inv_rate_type VARCHAR2(30);
1773 l_inv_rate NUMBER;
1774 l_ret_status VARCHAR2(30);
1775 l_bill_thru_date DATE;
1776 l_customer_id NUMBER;
1777 --End of bug fix
1778
1779 l_head_inv_exch_rate NUMBER := 1; /* Added for bug 4735682 */
1780 l_head_inv_curr_code VARCHAR2(50); /* Added for bug 4735682 */
1781 inv_num_cached NUMBER := -1; /* Added for bug 4735682 */
1782 l_calc_inv_amount NUMBER := 0; /* Added for bug 4995695 */
1783 l_sum_inv_amount NUMBER; /*Added For Bug 5346566*/
1784
1785
1786 BEGIN
1787 IF g1_debug_mode = 'Y' THEN
1788 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Entering Invoice By Bill Transaction Currency API Type ');
1789 END IF;
1790 TmpInvLines.delete;
1791 TmpInvHeaders.delete;
1792
1793
1794 IF g1_debug_mode = 'Y' THEN
1795 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Request Id : ' || p_request_id);
1796 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Project id : ' || p_project_id);
1797 END IF;
1798 /*For bug 4146846 -a) Obtaining the PFC attributes for the project one time
1799 b) Determing the IPC currency code */
1800
1801 OPEN c_project_details;
1802 FETCH c_project_details INTO l_use_pfc_flag,
1803 l_pfc_currency_code,
1804 l_projfunc_exchange_rate,
1805 l_pfc_exchg_rate_date_code,
1806 l_projfunc_exchg_rate_type,
1807 l_projfunc_exchg_rate_date,
1808 l_project_currency_code,
1809 l_invproc_currency_type;
1810
1811 CLOSE c_project_details;
1812
1813
1814 IF l_invproc_currency_type ='PROJECT_CURRENCY' THEN
1815
1816 l_invproc_currency_code := l_project_currency_code;
1817
1818 ELSIF l_invproc_currency_type ='PROJFUNC_CURRENCY' THEN
1819
1820 l_invproc_currency_code := l_pfc_currency_code;
1821
1822 ELSIF l_invproc_currency_type ='FUNDING_CURRENCY' THEN
1823
1824 SELECT funding_currency_code
1825 INTO l_funding_currency_code
1826 FROM pa_summary_project_fundings
1827 WHERE project_id = p_project_id
1828 AND rownum=1
1829 AND NVL(total_baselined_amount,0) > 0;
1830
1831 l_invproc_currency_code := l_funding_currency_code;
1832
1833 END IF;
1834
1835 /*End of bug fix*/
1836 /* Bug 3062595 - Fix Starts here */
1837 -- Credit memo processing is done here
1838 FOR cm_rec IN cur_cm LOOP
1839
1840 IF g1_debug_mode = 'Y' THEN
1841 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Credit memo processing ');
1842 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Invoice number : ' || cm_rec.draft_invoice_num);
1843 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Line number : ' || cm_rec.line_num);
1844 END IF;
1845
1846 /* Start of bug 4735682 */
1847 IF cm_rec.draft_invoice_num <> inv_num_cached THEN
1848 inv_num_cached := cm_rec.draft_invoice_num;
1849
1850 select nvl(da.INV_EXCHANGE_RATE, 1), da.INV_CURRENCY_CODE
1851 into l_head_inv_exch_rate, l_head_inv_curr_code
1852 from pa_draft_invoices_all da
1853 where project_id = p_project_id
1854 and draft_invoice_num = inv_num_cached;
1855
1856 IF g1_debug_mode = 'Y' THEN
1857 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Exchange rate retrival');
1858 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Using currency code: ' || l_head_inv_curr_code);
1859 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: Using exchange rate: ' || l_head_inv_exch_rate);
1860 END IF;
1861 END IF;
1862 /* Bug 4735682 ends */
1863
1864 /* Bug 4735682: Following update clause altered to include the exchange rate *
1865
1866 /* Commented and rewritten for bug 4735682
1867 UPDATE pa_draft_invoice_items dii
1868 SET dii.inv_amount =
1869 (SELECT sum(nvl(rdl.bill_trans_bill_amount, 0)) FROM pa_cust_rev_dist_lines_all rdl
1870 WHERE rdl.project_id = dii.project_id
1871 AND rdl.draft_invoice_num = dii.draft_invoice_num
1872 AND rdl.draft_invoice_item_line_num = dii.line_num)
1873 WHERE dii.project_id = p_project_id
1874 AND dii.draft_invoice_num = cm_rec.draft_invoice_num
1875 AND dii.line_num = cm_rec.line_num
1876 AND dii.invoice_line_type = 'STANDARD';
1877 */
1878 /* Select query for bug 4735682 brought out to comply with 8i.. bug 4995695 */
1879
1880 SELECT sum(nvl(rdl.bill_trans_bill_amount, 0)) * l_head_inv_exch_rate
1881 INTO l_calc_inv_amount
1882 FROM pa_cust_rev_dist_lines_all rdl
1883 WHERE rdl.project_id = p_project_id
1884 AND rdl.draft_invoice_num = inv_num_cached
1885 AND rdl.draft_invoice_item_line_num = cm_rec.line_num;
1886
1887 /* End of bug 4735682 .. bug 4995695 */
1888
1889 /* Bug 4995695.. modified the query for compatibility with 8i */
1890
1891 UPDATE pa_draft_invoice_items dii
1892 SET dii.inv_amount =
1893 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_calc_inv_amount, l_head_inv_curr_code)
1894 WHERE dii.project_id = p_project_id
1895 AND dii.draft_invoice_num = cm_rec.draft_invoice_num
1896 AND dii.line_num = cm_rec.line_num
1897 AND dii.invoice_line_type = 'STANDARD';
1898
1899 UPDATE pa_draft_invoice_items dii
1900 SET inv_amount = bill_trans_bill_amount
1901 WHERE dii.project_id = p_project_id
1902 AND dii.draft_invoice_num = cm_rec.draft_invoice_num
1903 AND dii.line_num = cm_rec.line_num
1904 AND dii.invoice_line_type = 'RETENTION';
1905
1906 END LOOP;
1907 /* Bug 3062595 - Fix Ends here */
1908
1909 -- Get the last invoice number
1910 BEGIN
1911 SELECT NVL(MAX(draft_invoice_num),0) INTO Last_invoice_num
1912 FROM pa_draft_invoices_all
1913 WHERE project_id = p_project_id;
1914 END;
1915 IF g1_debug_mode = 'Y' THEN
1916 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Max Invoice Number: ' || Last_invoice_num);
1917 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Befor the cur_inv Loop .....');
1918 END IF;
1919
1920
1921 FOR inv_rec IN cur_inv LOOP
1922
1923 IF g1_debug_mode = 'Y' THEN
1924 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the cursor cur_inv Loop .....');
1925 END IF;
1926
1927 IF inv_rec.draft_invoice_num <> NVL(previous_invoice_num,0) THEN
1928 -- Reset the previous values
1929
1930 IF g1_debug_mode = 'Y' THEN
1931 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the din <> Prvs_din If ....');
1932 END IF;
1933
1934 previous_btc :=inv_rec.bill_trans_currency_code;
1935 previous_invoice_num :=inv_rec.draft_invoice_num;
1936 split_invoice :=FALSE;
1937 last_line_num := 0 ;
1938 i := i+1;
1939 TmpInvHeaders(i).new_draft_invoice_num :=inv_rec.draft_invoice_num;
1940 TmpInvHeaders(i).current_draft_invoice_num :=inv_rec.draft_invoice_num;
1941 TmpInvHeaders(i).inv_currency_code:=inv_rec.bill_trans_currency_code;
1942 TmpInvHeaders(i).action_flag:='U';
1943 TmpInvHeaders(i).retention_percentage:= inv_rec.retention_percentage;
1944 END IF;
1945
1946 current_invoice_num := inv_rec.draft_invoice_num;
1947 new_invoice_num := inv_rec.draft_invoice_num;
1948
1949 current_line_num := inv_rec.line_num;
1950 new_line_num := inv_rec.line_num;
1951
1952
1953 IF inv_rec.bill_trans_currency_code <> previous_btc THEN
1954
1955 IF g1_debug_mode = 'Y' THEN
1956 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the BTC <> Prvs_btc If .....');
1957 END IF;
1958
1959 -- If the bill transaction changes
1960 -- Create new invoice
1961 -- Reset the invoice line num
1962 i := i+1;
1963 last_line_num := 0;
1964 previous_btc :=inv_rec.bill_trans_currency_code;
1965 split_invoice :=TRUE;
1966 last_invoice_num := last_invoice_num +1;
1967 TmpInvHeaders(i).new_draft_invoice_num :=last_invoice_num;
1968 TmpInvHeaders(i).current_draft_invoice_num :=inv_rec.draft_invoice_num;
1969 TmpInvHeaders(i).inv_currency_code:=inv_rec.bill_trans_currency_code;
1970 TmpInvHeaders(i).action_flag:='I';
1971 TmpInvHeaders(i).retention_percentage:= inv_rec.retention_percentage;
1972
1973 new_invoice_num :=TmpInvHeaders(i).new_draft_invoice_num;
1974 new_line_num := 0;
1975 new_invoice_num := last_invoice_num;
1976
1977 END IF;
1978
1979 IF previous_btc = inv_rec.bill_trans_currency_code AND
1980 (split_invoice) THEN
1981
1982 IF g1_debug_mode = 'Y' THEN
1983 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the Prvs_btc = btc If .....');
1984 END IF;
1985
1986 -- Add the invoice lines into new invoice lines array
1987 last_line_num := last_line_num +1;
1988 new_line_num := last_line_num;
1989 new_invoice_num := last_invoice_num;
1990 ELSE
1991
1992 IF g1_debug_mode = 'Y' THEN
1993 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Inside the Prvs_btc = btc Else .....');
1994 END IF;
1995
1996 last_line_num := last_line_num +1;
1997 new_line_num := last_line_num;
1998 END IF;
1999
2000 IF g1_debug_mode = 'Y' THEN
2001 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Before assigning to TmpInvlines .....');
2002 END IF;
2003
2004 TmpInvLines(j).current_draft_invoice_num := current_invoice_num;
2005 TmpInvLines(j).new_draft_invoice_num := new_invoice_num;
2006 TmpInvLines(j).current_line_num := current_line_num;
2007 TmpInvLines(j).new_line_num :=new_line_num;
2008 TmpInvLines(j).event_num :=inv_rec.event_num;
2009 TmpInvLines(j).revenue_amount :=inv_rec.revenue_amount;
2010
2011 IF g1_debug_mode = 'Y' THEN
2012 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' j = ' || j);
2013 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Old Inv : ' || TmpInvLines(j).current_draft_invoice_num);
2014 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Old Line Num : ' || TmpInvLines(j).current_line_num);
2015 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' New Inv : ' || TmpInvLines(j).new_draft_invoice_num);
2016 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' New Line Num : ' || TmpInvLines(j).new_line_num);
2017 END IF;
2018
2019 j:= j+1;
2020
2021 END LOOP;
2022
2023
2024 IF g1_debug_mode = 'Y' THEN
2025 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Out side the cur_inv Loop .....');
2026 END IF;
2027
2028 -- Process only if the project multi bill transaction currencies
2029
2030 IF (TmpInvHeaders.EXISTS(TmpInvHeaders.first)) THEN
2031 -- Reset the line number for newly created invoices
2032 /* Bug 2870248 fix starts*/
2033 FOR k IN TmpInvLines.FIRST.. TmpInvLines.LAST LOOP
2034 UPDATE pa_draft_invoice_items
2035 SET line_num = TmpInvLines(k).current_line_num+1000000
2036 WHERE project_id = p_project_id
2037 AND request_id = p_request_id
2038 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2039 AND line_num = TmpInvLines(k).current_line_num;
2040
2041 /* Added for bug 3144517.Fix is similar to that in 2870248 */
2042
2043 IF TmpInvLines(k).event_num =0 THEN
2044 --Update the RDLS
2045 UPDATE pa_cust_rev_dist_lines
2046 SET draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000
2047 WHERE project_id = p_project_id
2048 AND request_id = p_request_id
2049 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2050 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num;
2051
2052 ELSIF TmpInvLines(k).revenue_amount <> 0 THEN
2053 -- Update only if the event is revenue event
2054
2055 UPDATE pa_cust_event_rdl_all
2056 SET draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000
2057 WHERE project_id = p_project_id
2058 AND request_id = p_request_id
2059 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2060 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num;
2061
2062 END IF;
2063
2064 /*End of fix for bug 3144517 */
2065
2066 END LOOP;
2067 /* Bug 2870248 Ends */
2068
2069 FOR k IN REVERSE TmpInvLines.FIRST..TmpInvLines.LAST LOOP
2070
2071 -- Update the draft invoice items
2072 -- IF (TmpInvLines(k).current_draft_invoice_num <>
2073 -- TmpInvLInes(k).new_draft_invoice_num ) OR
2074 -- ( TmpInvLines(k).current_line_num <>
2075 -- TmpInvLines(k).new_line_num) THEN
2076 --PA_MCB_INVOICE_PKG.log_message('Update Invoice Lines ');
2077
2078 -- PA_MCB_INVOICE_PKG.log_message('O Inv Num : ' || TmpInvLines(k).current_draft_invoice_num ||
2079 -- ' N Inv Num : ' || TmpInvLInes(k).new_draft_invoice_num ||
2080 -- ' O LineNum : ' || TmpInvLInes(k).current_line_num ||
2081 -- ' N Inv Num : ' || TmpInvLInes(k).new_line_num);
2082
2083 UPDATE pa_draft_invoice_items
2084 SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
2085 line_num = TmpInvLines(k).new_line_num,
2086 inv_amount= bill_trans_bill_amount
2087 WHERE project_id = p_project_id
2088 AND request_id = p_request_id
2089 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2090 AND line_num = TmpInvLines(k).current_line_num+1000000;
2091 /* Adding with Huge number for bug2870248 */
2092
2093 /*fix for bug 3144517 */
2094 IF TmpInvLines(k).event_num =0 THEN
2095 --Update the RDLS
2096 UPDATE pa_cust_rev_dist_lines
2097 SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
2098 draft_invoice_item_line_num = TmpInvLines(k).new_line_num
2099 WHERE project_id = p_project_id
2100 AND request_id = p_request_id
2101 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2102 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000;
2103 /*For bug 3144517*/
2104 ELSIF TmpInvLines(k).revenue_amount <> 0 THEN
2105 -- Update only if the event is revenue event
2106
2107 UPDATE pa_cust_event_rdl_all
2108 SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
2109 draft_invoice_item_line_num = TmpInvLines(k).new_line_num
2110 WHERE project_id = p_project_id
2111 AND request_id = p_request_id
2112 AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
2113 AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000;
2114 /*For bug 3144517*/
2115 END IF;
2116 -- END IF;
2117
2118 END LOOP;
2119
2120 -- Final Update to calculate the rate between invoice currency and project functional currency
2121
2122 update pa_draft_invoice_items
2123 set inv_amount = 0
2124 where project_id = P_Project_Id
2125 and invoice_line_type = 'NET ZERO ADJUSTMENT'
2126 and request_id = p_request_id;
2127
2128 -- Update the conversion rate for ITC to PFC
2129
2130 FOR k IN TmpInvHeaders.FIRST..TmpInvHeaders.LAST LOOP
2131 -- Insert the New Invoice Headers
2132 -- All the values will be the same exception draft_invoice_num
2133 -- , inv_currency_code
2134
2135
2136 l_projfunc_invtrans_rate :=0;
2137 l_inv_amount :=0;
2138 l_pfc_amount :=0;
2139 l_fc_amount :=0;
2140 l_pc_amount :=0;
2141 l_btc_amount :=0;
2142 l_ret_line_num :=0;
2143
2144 SELECT NVL(sum(dii.inv_amount),0),
2145 NVL(sum(dii.projfunc_bill_amount),0),
2146 NVL(sum(dii.project_bill_amount),0),
2147 NVL(sum(dii.bill_trans_bill_amount),0),
2148 NVL(sum(dii.funding_bill_amount),0),
2149 NVL(MAX(dii.line_num),0) +1
2150 INTO l_inv_amount,
2151 l_pfc_amount,
2152 l_pc_amount,
2153 l_btc_amount,
2154 l_fc_amount,
2155 l_ret_line_num
2156 FROM pa_draft_invoice_items dii
2157 WHERE dii.project_id = P_Project_Id
2158 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2159 AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
2160 AND dii.invoice_line_type <> 'RETENTION';
2161
2162 IF g1_debug_mode = 'Y' THEN
2163 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || 'Old Inv Num : ' || TmpInvHeaders(k).current_draft_invoice_num ||
2164 ' New Inv Num : ' || TmpInvHeaders(k).new_draft_invoice_num ||
2165 ' New Inv Curr : ' || TmpInvHeaders(k).inv_currency_code ||
2166 ' Action Flag : ' || TmpInvHeaders(k).action_flag ||
2167 ' INV AMOUNT : ' || l_inv_amount ||
2168 ' PFC AMOUNT : ' || l_pfc_amount );
2169 END IF;
2170
2171 /* To avoid division by zero error , changing the logic to calculate l_projfunc_invtrans_rate - For bug 2961983
2172 commenting the following code to calculate l_projfunc_invtrans_rate and added the new logic following the same */
2173
2174 /* IF NVL(l_inv_amount,0) <> 0 AND NVL(l_pfc_amount,0) <> 0 THEN
2175 l_projfunc_invtrans_rate := NVL(l_inv_amount,0)/NVL(l_pfc_amount,0);
2176 END IF; Commented for bug 2961983 */
2177
2178 /* Added the following for bug 2961983 */
2179
2180 /* Commented for 3436063
2181 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
2182 INTO l_projfunc_invtrans_rate
2183 FROM pa_draft_invoice_items dii
2184 WHERE dii.project_id = P_Project_Id
2185 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2186 AND nvl(dii.projfunc_bill_amount,0) <> 0
2187 AND rownum=1;
2188 */
2189 /* End of Changes for bug 2961983 */
2190
2191 /****Code added for 3436063****/
2192 SELECT sum(NVL(dii.projfunc_bill_amount,0))
2193 INTO l_sum_projfunc_bill_amount
2194 FROM pa_draft_invoice_items dii
2195 WHERE dii.project_id = P_Project_Id
2196 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num;
2197
2198 /*** For Bug 5346566 ***/
2199 SELECT sum(NVL(dii.inv_amount,0))
2200 INTO l_sum_inv_amount
2201 FROM pa_draft_invoice_items dii
2202 WHERE dii.project_id = P_Project_Id
2203 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num;
2204 /*** End of code change for Bug 5346566 ***/
2205
2206 IF l_sum_projfunc_bill_amount <> 0 AND l_sum_inv_amount <> 0 /*** Condition added for bug 5346566 ***/
2207 THEN
2208 SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
2209 INTO l_projfunc_invtrans_rate
2210 FROM pa_draft_invoice_items dii
2211 WHERE dii.project_id = P_Project_Id
2212 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2213 having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
2214 ELSE
2215 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
2216 INTO l_projfunc_invtrans_rate
2217 FROM pa_draft_invoice_items dii
2218 WHERE dii.project_id = P_Project_Id
2219 AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2220 AND nvl(dii.projfunc_bill_amount,0) <> 0
2221 AND rownum=1;
2222 END IF;
2223 /****End of code added for 3436063****/
2224
2225 -- Insert if the action flag is I
2226
2227 IF TmpInvHeaders(k).action_flag ='I' THEN
2228
2229 IF g1_debug_mode = 'Y' THEN
2230 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Insert New Invoices ');
2231 END IF;
2232
2233 INSERT INTO pa_draft_invoices_all
2234 (project_id,
2235 draft_invoice_num,
2236 last_update_date,
2237 last_updated_by,
2238 creation_date,
2239 created_by,
2240 transfer_status_code,
2241 generation_error_flag,
2242 agreement_id,
2243 pa_date,
2244 request_id,
2245 program_application_id,
2246 program_id,
2247 program_update_date,
2248 customer_bill_split,
2249 bill_through_date,
2250 invoice_comment,
2251 approved_date,
2252 approved_by_person_id,
2253 released_date,
2254 released_by_person_id,
2255 invoice_date,
2256 ra_invoice_number,
2257 transferred_date,
2258 transfer_rejection_reason,
2259 unearned_revenue_cr,
2260 unbilled_receivable_dr,
2261 gl_date,
2262 system_reference,
2263 draft_invoice_num_credited ,
2264 canceled_flag,
2265 cancel_credit_memo_flag ,
2266 write_off_flag,
2267 converted_flag,
2268 extracted_date,
2269 last_update_login,
2270 attribute_category,
2271 attribute1,
2272 attribute2,
2273 attribute3,
2274 attribute4,
2275 attribute5,
2276 attribute6,
2277 attribute7,
2278 attribute8,
2279 attribute9,
2280 attribute10,
2281 retention_percentage,
2282 invoice_set_id,
2283 org_id,
2284 inv_currency_code,
2285 inv_rate_type,
2286 inv_rate_date,
2287 inv_exchange_rate,
2288 bill_to_address_id,
2289 ship_to_address_id ,
2290 prc_generated_flag,
2291 receivable_code_combination_id,
2292 rounding_code_combination_id,
2293 unbilled_code_combination_id,
2294 unearned_code_combination_id,
2295 woff_code_combination_id,
2296 acctd_curr_code,
2297 acctd_rate_type,
2298 acctd_rate_date,
2299 acctd_exchg_rate,
2300 language,
2301 cc_invoice_group_code ,
2302 cc_project_id,
2303 ib_ap_transfer_status_code,
2304 ib_ap_transfer_error_code ,
2305 invproc_currency_code,
2306 projfunc_invtrans_rate_type,
2307 projfunc_invtrans_rate_date ,
2308 projfunc_invtrans_ex_rate,
2309 customer_id,
2310 bill_to_customer_id,
2311 ship_to_customer_id,
2312 bill_to_contact_id,
2313 ship_to_contact_id)
2314 SELECT project_id,
2315 TmpInvHeaders(k).new_draft_invoice_num,
2316 last_update_date,
2317 last_updated_by,
2318 creation_date,
2319 created_by,
2320 transfer_status_code,
2321 generation_error_flag,
2322 agreement_id,
2323 pa_date,
2324 request_id,
2325 program_application_id,
2326 program_id,
2327 program_update_date,
2328 customer_bill_split,
2329 bill_through_date,
2330 invoice_comment,
2331 approved_date,
2332 approved_by_person_id,
2333 released_date,
2334 released_by_person_id,
2335 invoice_date,
2336 ra_invoice_number,
2337 transferred_date,
2338 transfer_rejection_reason,
2339 unearned_revenue_cr,
2340 unbilled_receivable_dr,
2341 gl_date,
2342 system_reference,
2343 draft_invoice_num_credited ,
2344 canceled_flag,
2345 cancel_credit_memo_flag ,
2346 write_off_flag,
2347 converted_flag,
2348 extracted_date,
2349 last_update_login,
2350 attribute_category,
2351 attribute1,
2352 attribute2,
2353 attribute3,
2354 attribute4,
2355 attribute5,
2356 attribute6,
2357 attribute7,
2358 attribute8,
2359 attribute9,
2360 attribute10,
2361 retention_percentage,
2362 invoice_set_id,
2363 org_id,
2364 TmpInvHeaders(k).inv_currency_code,
2365 NULL, --'User'
2366 NULL, --sysdate
2367 NULL, --1
2368 bill_to_address_id,
2369 ship_to_address_id ,
2370 prc_generated_flag,
2371 receivable_code_combination_id,
2372 rounding_code_combination_id,
2373 unbilled_code_combination_id,
2374 unearned_code_combination_id,
2375 woff_code_combination_id,
2376 acctd_curr_code,
2377 acctd_rate_type,
2378 acctd_rate_date,
2379 acctd_exchg_rate,
2380 language,
2381 cc_invoice_group_code ,
2382 cc_project_id,
2383 ib_ap_transfer_status_code,
2384 ib_ap_transfer_error_code ,
2385 invproc_currency_code,
2386 'User',
2387 Sysdate ,
2388 l_projfunc_invtrans_rate,
2389 customer_id,
2390 bill_to_customer_id,
2391 ship_to_customer_id,
2392 bill_to_contact_id,
2393 ship_to_contact_id
2394 FROM pa_draft_invoices_all
2395 WHERE project_id = p_project_id
2396 AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num;
2397
2398 -- Handling Retention lines
2399
2400 /* Commented out for Retention enhancements
2401 IF NVL(TmpInvHeaders(k).retention_percentage,0) <> 0 THEN
2402
2403 IF g1_debug_mode = 'Y' THEN
2404 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Insert Ret Line for New Invoice ');
2405 END IF;
2406
2407 -- Insert new retention line
2408
2409 INSERT INTO pa_draft_invoice_items(
2410 project_id,
2411 draft_invoice_num,
2412 line_num,
2413 last_update_date,
2414 last_updated_by,
2415 creation_date,
2416 created_by,
2417 amount,
2418 text,
2419 invoice_line_type,
2420 request_id,
2421 program_application_id,
2422 program_id,
2423 program_update_date,
2424 unearned_revenue_cr,
2425 unbilled_receivable_dr,
2426 task_id,
2427 event_task_id,
2428 event_num,
2429 ship_to_address_id,
2430 taxable_flag,
2431 draft_inv_line_num_credited,
2432 last_update_login,
2433 inv_amount,
2434 output_tax_classification_code,
2435 output_tax_exempt_flag,
2436 output_tax_exempt_reason_code,
2437 output_tax_exempt_number,
2438 acct_amount,
2439 rounding_amount,
2440 unbilled_rounding_amount_dr,
2441 unearned_rounding_amount_cr,
2442 translated_text,
2443 cc_rev_code_combination_id,
2444 cc_project_id,
2445 cc_tax_task_id,
2446 project_currency_code,
2447 project_bill_amount,
2448 projfunc_currency_code,
2449 projfunc_bill_amount,
2450 funding_currency_code,
2451 funding_bill_amount,
2452 invproc_currency_code,
2453 bill_trans_currency_code,
2454 bill_trans_bill_amount)
2455 SELECT
2456 project_id,
2457 TmpInvHeaders(k).new_draft_invoice_num,
2458 l_ret_line_num,
2459 last_update_date,
2460 last_updated_by,
2461 creation_date,
2462 created_by,
2463 NVL(l_btc_amount,0) *
2464 ( NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2465 text,
2466 invoice_line_type,
2467 request_id,
2468 program_application_id,
2469 program_id,
2470 program_update_date,
2471 unearned_revenue_cr,
2472 unbilled_receivable_dr,
2473 task_id,
2474 event_task_id,
2475 event_num,
2476 ship_to_address_id,
2477 taxable_flag,
2478 draft_inv_line_num_credited,
2479 last_update_login,
2480 NVL(l_btc_amount,0) *
2481 ( NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2482 output_tax_classification_code,
2483 output_tax_exempt_flag,
2484 output_tax_exempt_reason_code,
2485 output_tax_exempt_number,
2486 acct_amount,
2487 rounding_amount,
2488 unbilled_rounding_amount_dr,
2489 unearned_rounding_amount_cr,
2490 translated_text,
2491 cc_rev_code_combination_id,
2492 cc_project_id,
2493 cc_tax_task_id,
2494 project_currency_code,
2495 NVL(l_pc_amount,0) *
2496 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2497 projfunc_currency_code,
2498 NVL(l_pfc_amount,0) *
2499 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2500 funding_currency_code,
2501 NVL(l_fc_amount,0) *
2502 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2503 invproc_currency_code,
2504 TmpInvHeaders(k).inv_currency_code,
2505 NVL(l_btc_amount,0) *
2506 (NVL(TmpInvHeaders(k).retention_percentage,0)/100)
2507 FROM pa_draft_invoice_items
2508 WHERE project_id = p_project_id
2509 AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num
2510 AND invoice_Line_type ='RETENTION';
2511
2512
2513
2514 END IF; */
2515
2516 ELSIF TmpInvHeaders(k).action_flag ='U' THEN
2517
2518 ---- Existing Invoice, update BTC currency code and rates
2519 IF g1_debug_mode = 'Y' THEN
2520 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Update Existing Invoice ');
2521 END IF;
2522
2523 UPDATE pa_draft_invoices_all
2524 set inv_currency_code = TmpInvHeaders(k).inv_currency_code,
2525 inv_rate_type = NULL, --'User',
2526 inv_rate_date = NULL, --sysdate,
2527 inv_exchange_rate = NULL, --1,
2528 projfunc_invtrans_rate_type = 'User',
2529 /* projfunc_invtrans_rate_date = sysdate, commented for bug 5141073 */
2530 projfunc_invtrans_rate_date = invoice_date, /* Added for bug 5141073 */
2531 projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
2532 WHERE project_id = P_Project_Id
2533 AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num;
2534
2535 -- Reset the retention line
2536
2537 /* Commented out for Retention Enhancements
2538
2539 IF NVL(TmpInvHeaders(k).retention_percentage,0) <> 0 THEN
2540
2541 IF g1_debug_mode = 'Y' THEN
2542 PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Update Existing Invoice Retention Line ');
2543 END IF;
2544
2545 UPDATE pa_draft_invoice_items
2546 SET bill_trans_currency_code = TmpInvHeaders(k).inv_currency_code,
2547 projfunc_bill_amount =
2548 NVL(l_pfc_amount,0) *
2549 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2550 project_bill_amount =
2551 NVL(l_pc_amount,0) *
2552 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2553 funding_bill_amount =
2554 NVL(l_fc_amount,0) *
2555 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2556 bill_trans_bill_amount =
2557 NVL(l_btc_amount,0) *
2558 (NVL(TmpInvHeaders(k).retention_percentage,0)/100),
2559 amount =
2560 NVL(l_btc_amount,0) *
2561 NVL(TmpInvHeaders(k).retention_percentage,0),
2562 inv_amount =
2563 NVL(l_btc_amount,0) *
2564 (NVL(TmpInvHeaders(k).retention_percentage,0)/100)
2565 WHERE draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num
2566 AND project_id = p_project_id
2567 AND invoice_line_type = 'RETENTION';
2568 END IF; */
2569
2570 END IF;
2571
2572 END LOOP;
2573
2574 END IF;
2575 --For bug 4146846 : Call CAl_Conversion_Attr procedure which computes the Conversion Attributes
2576 -- As per USE PFC flag and BTC,PFC and IPC
2577 FOR k IN TmpInvLines.FIRST.. TmpInvLines.LAST LOOP
2578 SELECT agr.customer_id,
2579 i.bill_through_date
2580 INTO l_customer_id,
2581 l_bill_thru_date
2582 FROM pa_draft_invoices_all i,
2583 pa_agreements_all agr
2584 WHERE i.project_id = p_project_id
2585 AND i.request_id = p_request_id
2586 AND i.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
2587 AND NVL(i.generation_error_flag,'N')= 'N'
2588 AND i.agreement_id = agr.agreement_id;
2589
2590 SELECT NVL(ppc.inv_rate_date,NVL(l_invoice_date,l_bill_thru_date)),
2591 ppc.inv_rate_type,
2592 ppc.inv_exchange_rate
2593 INTO l_inv_rate_date,
2594 l_inv_rate_type,
2595 l_inv_rate
2596 FROM pa_project_customers ppc
2597 WHERE ppc.project_id = P_Project_Id
2598 AND ppc.customer_id = l_customer_id;
2599 Cal_Conversion_Attr ( p_project_id => p_project_id,
2600 p_draft_invoice_num => TmpInvHeaders(k).new_draft_invoice_num,
2601 p_use_pfc_flag => l_use_pfc_flag,
2602 p_pfc_currency_code => l_pfc_currency_code,
2603 p_pfc_ex_rate => l_projfunc_exchange_rate,
2604 p_pfc_ex_rate_date_code => l_pfc_exchg_rate_date_code,
2605 p_pfc_rate_type => l_projfunc_exchg_rate_type,
2606 p_pfc_rate_date => l_projfunc_exchg_rate_date,
2607 p_invproc_currency_code => l_invproc_currency_code,
2608 p_inv_ex_rate => l_inv_rate,
2609 p_inv_rate_type => l_inv_rate_type,
2610 p_inv_rate_date => l_inv_rate_date,
2611 p_btc_currency_code => TmpInvHeaders(k).inv_currency_code,
2612 p_bill_thru_date => l_bill_thru_date,
2613 x_status => l_ret_status);
2614 IF l_ret_status <> 'Y' THEN
2615 IF g1_debug_mode = 'Y' THEN
2616 PA_MCB_INVOICE_PKG.log_message(' Error in Inv_by_Bill_Trans_Currency '||sqlerrm(sqlcode));
2617 END IF;
2618 END IF;
2619
2620 END LOOP;
2621
2622 /*End of bug 4146846*/
2623
2624 EXCEPTION
2625 WHEN others THEN
2626 IF g1_debug_mode = 'Y' THEN
2627 PA_MCB_INVOICE_PKG.log_message(' Error in Inv_by_Bill_Trans_Currency '||sqlerrm(sqlcode));
2628 END IF;
2629 x_return_status := sqlerrm( sqlcode );
2630
2631 -- RAISE;
2632 END Inv_by_Bill_Trans_Currency;
2633 END PA_MCB_INVOICE_PKG;