[Home] [Help]
PACKAGE BODY: APPS.PA_BILL_PCT
Source
1 PACKAGE BODY pa_bill_pct AS
2 /* $Header: PAXPCTB.pls 120.3.12010000.2 2009/06/01 05:45:53 dlella ship $ */
3
4 /** Main procedure that calculates the revenue and invoice amounts **/
5
6 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
7
8 PROCEDURE calc_pct_comp_amt
9 ( X_project_id IN NUMBER,
10 X_top_task_id IN NUMBER DEFAULT NULL,
11 X_calling_process IN VARCHAR2 DEFAULT NULL,
12 X_calling_place IN VARCHAR2 DEFAULT NULL,
13 X_amount IN NUMBER DEFAULT NULL,
14 X_percentage IN NUMBER DEFAULT NULL,
15 X_rev_or_bill_date IN DATE DEFAULT NULL,
16 X_billing_assignment_id IN NUMBER DEFAULT NULL,
17 X_billing_extension_id IN NUMBER DEFAULT NULL,
18 X_request_id IN NUMBER DEFAULT NULL
19 )
20 IS
21
22 budget_revenue REAL := 0;
23 budget_cost REAL := 0;
24 invoice_amount REAL := 0;
25 revenue_amount REAL := 0;
26 event_revenue REAL := 0;
27 event_invoice REAL := 0;
28 cost_amount REAL := 0;
29 revenue REAL := 0;
30 invoice REAL := 0;
31 Amount_Left REAL := 0;
32 Percent_Complete REAL := 0;
33 calc_inv_amount REAL :=0; -- added for bug 4719700
34 calc_rev_amount REAL :=0; -- added for bug 4719700
35
36 event_description VARCHAR2(240);
37 l_currency_code VARCHAR2(15);
38
39 -- The cost and revenue budget type codes used by the get_rev_budget_amount procedure
40 --
41 l_cost_budget_type_code VARCHAR2(30);
42 l_rev_budget_type_code VARCHAR2(30);
43
44 l_status NUMBER;
45 l_error_message VARCHAR2(240);
46
47 pct_error EXCEPTION;
48
49 /* Declaring varible for MCB2 */
50 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
51 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
52 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
53 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
54 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
55 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
56 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
57 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
58 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
59 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
60 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
61 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
62 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
63 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
64 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
65 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
66 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
67 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
68 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
69 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
70 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
71 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
72 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
73 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
74 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
75 l_return_status VARCHAR2(30);
76 l_msg_count NUMBER;
77 l_msg_data VARCHAR2(30);
78
79 /* Added for Fin Plan impact */
80 l_cost_plan_type_id NUMBER;
81 l_rev_plan_type_id NUMBER;
82 /* till here */
83
84 BEGIN
85 IF g1_debug_mode = 'Y' THEN
86 PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.calc_pct_comp_amt :');
87 END IF;
88
89
90 /** gets project currency code This is commented because now PFC and Project currency can be diffrent for MCB2 **/
91 /* l_currency_code := pa_multi_currency_txn.get_proj_curr_code_sql(X_project_id); */
92
93 /* To get the Project functional currency for Project, calling get default procedure for MCB2 */
94
95 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
96 p_project_id => X_project_id,
97 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
98 x_baseline_funding_flag => l_baseline_funding_flag,
99 x_revproc_currency_code => l_revproc_currency_code,
100 x_invproc_currency_type => l_invproc_currency_type,
101 x_invproc_currency_code => l_invproc_currency_code,
102 x_project_currency_code => l_project_currency_code,
103 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
104 x_project_bil_rate_type => l_project_bil_rate_type,
105 x_project_bil_rate_date => l_project_bil_rate_date,
106 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
107 x_projfunc_currency_code => l_projfunc_currency_code,
108 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
109 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
110 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
111 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
112 x_funding_rate_date_code => l_funding_rate_date_code,
113 x_funding_rate_type => l_funding_rate_type,
114 x_funding_rate_date => l_funding_rate_date,
115 x_funding_exchange_rate => l_funding_exchange_rate,
116 x_return_status => l_return_status,
117 x_msg_count => l_msg_count,
118 x_msg_data => l_msg_data);
119
120 l_currency_code := l_projfunc_currency_code;
121
122 IF g1_debug_mode = 'Y' THEN
123 PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt currency :'||l_currency_code);
124 END IF;
125 /** gets the cost and revenue budget amounts **/
126
127 IF g1_debug_mode = 'Y' THEN
128 PA_MCB_INVOICE_PKG.log_message('Before pa_billing_extn_params_v select pa_bill_pct.calc_pct_comp_amt :');
129 END IF;
130 /* Added for bug 2649456.Not handling exception intentionaly because if it is coming,
131 it will be data issue */
132 BEGIN
133 SELECT default_cost_plan_type_id,default_rev_plan_type_id
134 INTO l_cost_plan_type_id,l_rev_plan_type_id
135 FROM pa_billing_extn_params_v;
136 EXCEPTION
137 WHEN OTHERS THEN
138 IF g1_debug_mode = 'Y' THEN
139 PA_MCB_INVOICE_PKG.log_message('Error from pa_billing_extn_params_v pa_bill_pct.calc_pct_comp_amt :'||SQLERRM);
140 END IF;
141 RAISE;
142 END;
143 /* till here */
144
145 IF g1_debug_mode = 'Y' THEN
146 PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt cost_plan_type_id :'||l_cost_plan_type_id);
147 PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt rev_plan_type_id :' ||l_rev_plan_type_id);
148 END IF;
149
150 l_status := 0;
151 l_error_message := NULL;
152 get_rev_budget_amount(
153 X2_project_id => X_project_id,
154 X2_task_id => X_top_task_id,
155 X2_revenue_amount => budget_revenue,
156 X_rev_budget_type_code => l_rev_budget_type_code,
157 X_rev_plan_type_id => l_rev_plan_type_id,
158 X_error_message => l_error_message,
159 X_status => l_status);
160 IF g1_debug_mode = 'Y' THEN
161 PA_MCB_INVOICE_PKG.log_message('After call of get_rev_budget_amount inside pa_bill_pct.calc_pct_comp_amt :'||to_char(budget_revenue));
162 END IF;
163
164 -- If get budget amount return an error its fatal.
165
166 IF l_status <> 0 THEN
167 raise pct_error;
168 END IF;
169
170 /** Get the event amounts generated by events other than Percent complete **/
171
172 IF g1_debug_mode = 'Y' THEN
173 PA_MCB_INVOICE_PKG.log_message('Before calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt :');
174 END IF;
175 PA_BILL_PCT.PotEventAmount(
176 X2_project_id => X_project_id,
177 X2_task_id => X_top_task_id,
178 X2_accrue_through_date => X_rev_or_bill_date,
179 X2_revenue_amount => event_revenue,
180 X2_invoice_amount => event_invoice);
181
182 IF g1_debug_mode = 'Y' THEN
183 PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt event revenue :'||to_char(event_revenue));
184 PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt event invoice :'||to_char(event_invoice));
185 END IF;
186 /** Get the amount left based on the hard limit set for the projects
187 customers **/
188
189 IF g1_debug_mode = 'Y' THEN
190 PA_MCB_INVOICE_PKG.log_message('before calling pa_billing_amount.LowestAmountLeft inside pa_bill_pct.calc_pct_comp_amt :');
191 END IF;
192 Amount_Left := pa_billing_amount.LowestAmountLeft(
193 X_project_id,
194 X_top_task_id,
195 X_calling_process);
196
197 IF g1_debug_mode = 'Y' THEN
198 PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt Amount_Left :'||to_char(Amount_Left));
199 END IF;
200 /** Get the Percent complete for the project / top task **/
201
202 IF g1_debug_mode = 'Y' THEN
203 PA_MCB_INVOICE_PKG.log_message('before calling GetPercentComplete inside pa_bill_pct.calc_pct_comp_amt Percent_Complete :');
204 END IF;
205 Percent_Complete := PA_BILL_PCT.GetPercentComplete(
206 X_project_id ,
207 X_top_task_id,
208 X_rev_or_bill_date
209 );
210
211
212 IF g1_debug_mode = 'Y' THEN
213 PA_MCB_INVOICE_PKG.log_message('After calling GetPercentComplete inside pa_bill_pct.calc_pct_comp_amt Percent_Complete :'||to_char(Percent_Complete));
214 END IF;
215 IF Percent_Complete > 100 THEN
216 Percent_Complete := 100;
217 END IF;
218
219 -- DBMS_OUTPUT.PUT('Revenue =');
220 -- DBMS_OUTPUT.PUT_LINE(Revenue);
221 -- DBMS_OUTPUT.PUT('Amount_Left=');
222 -- DBMS_OUTPUT.PUT_LINE(Amount_Left);
223 -- DBMS_OUTPUT.PUT('budget_cost=');
224 -- DBMS_OUTPUT.PUT_LINE(budget_cost);
225 -- DBMS_OUTPUT.PUT('budget_revenue=');
226 -- DBMS_OUTPUT.PUT_LINE(budget_revenue);
227 -- DBMS_OUTPUT.PUT('cost_amount=');
228 -- DBMS_OUTPUT.PUT_LINE(cost_amount);
229 -- DBMS_OUTPUT.PUT('revenue_amount=');
230 -- DBMS_OUTPUT.PUT_LINE(revenue_amount);
231 -- DBMS_OUTPUT.PUT('event_revenue=');
232 -- DBMS_OUTPUT.PUT_LINE(event_revenue);
233
234 IF (X_calling_process = 'Revenue') THEN
235
236 IF g1_debug_mode = 'Y' THEN
237 PA_MCB_INVOICE_PKG.log_message('before calling RevenueAmount inside pa_bill_pct.calc_pct_comp_amt RevenueAmount :');
238 END IF;
239 PA_BILL_PCT.RevenueAmount(
240 X2_project_id => x_project_id,
241 X2_task_id => X_top_task_id,
242 X2_revenue_amount => revenue_amount);
243
244 IF g1_debug_mode = 'Y' THEN
245 PA_MCB_INVOICE_PKG.log_message('After calling RevenueAmount inside pa_bill_pct.calc_pct_comp_amt RevenueAmount :'||to_char(revenue_amount));
246 END IF;
247 -- IF (budget_cost <> 0) THEN
248 -- Revenue is the Least of
249 -- revenue = percent_complete * (budget_revenue - event_revenue)
250 -- - existing revenue.
251 -- or the amount left in the funding.
252
253 /* Revenue := Least( ((nvl(Percent_Complete,0) * 0.01)
254 * greatest( nvl(budget_revenue,0)
255 - nvl(event_revenue,0), 0
256 )
257 - (nvl(revenue_amount,0))
258 ) ,
259 Amount_Left
260 );Commenting for bug 4719700*/
261 /* Added for bug 4719700 BEGIN */
262 calc_rev_amount := pa_multi_currency_billing.round_trans_currency_amt((nvl(Percent_Complete,0) * 0.01)
263 * greatest( nvl(budget_revenue,0)
264 - nvl(event_revenue,0), 0
265 ),l_projfunc_currency_code);
266
267 Revenue := Least( (calc_rev_amount - (nvl(revenue_amount,0))) , Amount_Left);
268 /* Addded for bug 4719700 END */
269
270
271 IF g1_debug_mode = 'Y' THEN
272 PA_MCB_INVOICE_PKG.log_message('Calculating Revenue inside pa_bill_pct.calc_pct_comp_amt (Least) Revenue :'||to_char(Revenue));
273 END IF;
274 /* Changed the length of the format mask for amount_left column from 15 to 22
275 to fix the bug 2124494 for MCB2 */
276 /* Changed the length of the format mask for all column from 15 to 22
277 to fix the bug 2162900 for MCB2 */
278 /* Removed the format mask for Percent_Complete as this was rounding off
279 the value for bug 6660286 */
280 Event_Description := 'Percent Complete Least ' || '(' ||
281 to_char(amount_left,fnd_currency.get_format_mask(l_currency_code,22))
282 || ' ,((' ||
283 rtrim(to_char(Percent_Complete * 0.01,'0.000000'),'0')
284 || ' * (' ||
285 to_char(budget_revenue,fnd_currency.get_format_mask(l_currency_code,22))
286 || ' - ' ||
287 to_char(nvl(event_revenue,0),fnd_currency.get_format_mask(l_currency_code,22))
288 || ')) - '||
289 to_char(nvl(revenue_amount,0),fnd_currency.get_format_mask(l_currency_code,22))
290 || ' ))';
291
292 /** public api to insert event **/
293
294 IF g1_debug_mode = 'Y' THEN
295 PA_MCB_INVOICE_PKG.log_message('rev part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);
296 PA_MCB_INVOICE_PKG.log_message('rev part Before calling insert_event inside pa_bill_pct.calc_pct_comp_amt Revenue :');
297 END IF;
298 pa_billing_pub.insert_event (
299 X_rev_amt => Revenue,
300 X_bill_amt => 0,
301 X_event_description => event_description,
302 X_audit_amount1 => amount_left,
303 X_audit_amount2 => revenue_amount,
304 X_audit_amount3 => budget_revenue,
305 X_audit_amount4 => event_revenue,
306 X_audit_amount5 => Percent_Complete,
307 X_audit_cost_budget_type_code => l_cost_budget_type_code,
308 X_audit_rev_budget_type_code => l_rev_budget_type_code,
309 X_audit_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
310 X_audit_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
311 X_error_message => l_error_message,
312 X_status => l_status
313 );
314
315 IF g1_debug_mode = 'Y' THEN
316 PA_MCB_INVOICE_PKG.log_message('Rev part After calling insert_event inside pa_bill_pct.calc_pct_comp_amt Revenue -> status :'||l_status);
317 END IF;
318 IF l_status <> 0 THEN
319 raise pct_error;
320 END IF;
321
322 -- END IF;
323
324 ELSE
325 IF g1_debug_mode = 'Y' THEN
326 PA_MCB_INVOICE_PKG.log_message('before calling InvoiceAmount inside pa_bill_pct.calc_pct_comp_amt InvoiceAmount :');
327 END IF;
328 PA_BILL_PCT.InvoiceAmount(
329 X2_project_id => X_project_id,
330 X2_task_id => X_top_task_id,
331 X2_invoice_amount => invoice_amount);
332
333 IF g1_debug_mode = 'Y' THEN
334 PA_MCB_INVOICE_PKG.log_message('After calling InvoiceAmount inside pa_bill_pct.calc_pct_comp_amt InvoiceAmount :'||to_char(invoice_amount));
335 END IF;
336 -- IF (budget_cost <> 0) THEN
337 /*Invoice := Least( (( nvl(Percent_Complete,0) * 0.01)
338 * greatest( (nvl(budget_revenue,0)
339 - nvl(event_invoice,0)), 0)
340 ) - nvl(invoice_amount,0),
341 nvl(Amount_Left,0)
342 );Commenting for bug 4719700*/
343 /* Changes for 4719700 -Start */
344
345 calc_inv_amount := pa_multi_currency_billing.round_trans_currency_amt((( nvl(Percent_Complete,0) * 0.01)
346 * greatest( (nvl(budget_revenue,0)
347 - nvl(event_invoice,0)), 0)
348 ),l_projfunc_currency_code);
349
350 Invoice :=Least( calc_inv_amount - nvl(invoice_amount,0), nvl(Amount_Left,0));
351 /* Changes for 4719700 -End */
352
353
354 IF g1_debug_mode = 'Y' THEN
355 PA_MCB_INVOICE_PKG.log_message('calculating Invoice Amount ( with least) inside pa_bill_pct.calc_pct_comp_amt Invoice :'||to_char(Invoice));
356 END IF;
357 /* Changed the length of the format mask for amount_left column from 15 to 22
358 to fix the bug 2124494 for MCB2 */
359 /* Changed the length of the format mask for all column from 15 to 22
360 to fix the bug 2162900 for MCB2 */
361 /* Removed the format mask for Percent_Complete as this was rounding off
362 the value for bug 6660286 */
363 Event_Description := 'Percent Complete Least '|| '(' ||
364 to_char(amount_left,fnd_currency.get_format_mask(l_currency_code,22))
365 || ' ,((' ||
366 rtrim(to_char(Percent_Complete * 0.01,'0.000000'),'0')
367 || ' * (' ||
368 to_char(budget_revenue,fnd_currency.get_format_mask(l_currency_code,22))
369 || ' - ' ||
370 to_char(nvl(event_invoice,0),fnd_currency.get_format_mask(l_currency_code,22))
371 || ')) - '||
372 to_char(nvl(invoice_amount,0),fnd_currency.get_format_mask(l_currency_code,22))
373 || ' ))';
374
375 IF g1_debug_mode = 'Y' THEN
376 PA_MCB_INVOICE_PKG.log_message('Inv part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);
377 PA_MCB_INVOICE_PKG.log_message('Inv part Before insert event inside pa_bill_pct.calc_pct_comp_amt Invoice :');
378 END IF;
379 pa_billing_pub.insert_event (
380 X_rev_amt => 0,
381 X_bill_amt => Invoice,
382 X_event_description => Event_Description,
383 X_audit_amount1 => amount_left,
384 X_audit_amount2 => invoice_amount,
385 X_audit_amount3 => budget_revenue,
386 X_audit_amount4 => event_invoice,
387 X_audit_amount5 => Percent_Complete,
388 X_audit_cost_budget_type_code => l_cost_budget_type_code,
389 X_audit_rev_budget_type_code => l_rev_budget_type_code,
390 X_audit_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
391 X_audit_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
392 X_error_message => l_error_message,
393 X_status => l_status
394 );
395
396 IF g1_debug_mode = 'Y' THEN
397 PA_MCB_INVOICE_PKG.log_message('Inv partinsert event inside pa_bill_pct.calc_pct_comp_amt Invoice -> status :'||l_status);
398 END IF;
399 IF l_status <> 0 THEN
400 raise pct_error;
401 END IF;
402 -- END IF;
403 END IF;
404
405 IF g1_debug_mode = 'Y' THEN
406 PA_MCB_INVOICE_PKG.log_message('Exiting pa_bill_pct.calc_pct_comp_amt :');
407 END IF;
408 EXCEPTION
409 WHEN pct_error THEN
410 IF g1_debug_mode = 'Y' THEN
411 PA_MCB_INVOICE_PKG.log_message('Inside pct_error inside pa_bill_pct.calc_pct_comp_amt');
412 END IF;
413 NULL;
414 -- Modified so that this exception is reported but doesnot stop revenue
415 -- processing
416 -- RAISE_APPLICATION_ERROR(-20101,l_error_message);
417 WHEN OTHERS THEN
418 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
419 IF g1_debug_mode = 'Y' THEN
420 PA_MCB_INVOICE_PKG.log_message('Inside when other of pa_bill_pct.calc_pct_comp_amt');
421 END IF;
422 RAISE;
423
424 END calc_pct_comp_amt;
425
426
427 Procedure PotEventAmount( X2_project_id NUMBER,
428 X2_task_id NUMBER DEFAULT NULL,
429 X2_accrue_through_date DATE DEFAULT NULL,
430 X2_revenue_amount OUT NOCOPY REAL,
431 X2_invoice_amount OUT NOCOPY REAL)
432 IS
433 /* Declaring varible for MCB2 */
434 l_trans_rev_amt pa_events.bill_trans_rev_amount%TYPE;
435 l_trans_bill_amt pa_events.bill_trans_bill_amount%TYPE;
436 l_passd_amt pa_events.bill_trans_bill_amount%TYPE := 0;
437 l_projfunc_amount_sum pa_events.projfunc_revenue_amount%TYPE;
438 l_converted_amount pa_events.projfunc_revenue_amount%TYPE;
439 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
440 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
441 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
442 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
443 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
444 l_event_date pa_events.completion_date%TYPE;
445 l_conv_date pa_events.completion_date%TYPE;
446 l_denominator Number;
447 l_numerator Number;
448 l_staus Varchar2(30);
449 l_calling_process Varchar2(50);
450 l_project_id pa_projects_all.project_id%TYPE;
451 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
452 l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
453 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
454 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
455 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
456 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
457 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
458 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
459 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
460 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
461 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
462 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
463 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
464 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
465 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
466 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
467 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
468 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
469 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
470 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
471 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
472 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
473 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
474 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
475 l_return_status VARCHAR2(30);
476 l_msg_count NUMBER;
477 l_msg_data VARCHAR2(30);
478
479
480 --Modified the following cursor for bug 8429063
481
482 CURSOR pctfunc_rev_inv_amt(X2_project_id Number,X2_task_id Number,X2_accrue_through_date Date) IS
483 SELECT (DECODE(revenue_hold_flag, 'Y' , 0 ,DECODE(et.event_type_classification,
484 'WRITE OFF',-1 * nvl(bill_trans_rev_amount,0),
485 'RLZED_LOSSES',-1 * nvl(bill_trans_rev_amount,0),
486 NVL(bill_trans_rev_amount,0)))) trans_rev_amount,
487 (DECODE(bill_hold_flag, 'Y' , 0 , DECODE(et.event_type_classification,'INVOICE REDUCTION', -1 * nvl(bill_trans_bill_amount,0),
488 NVL(bill_trans_bill_amount,0)))) trans_bill_amount,e.bill_trans_currency_code,e.projfunc_currency_code,
489 e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
490 FROM pa_events e,
491 pa_event_types et
492 WHERE e.event_type = et.event_type
493 AND e.project_id = X2_project_id
494 AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
495 AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
496 AND NOT EXISTS ( select '1'
497 from pa_billing_assignments bea,
498 pa_billing_extensions be
499 where be.billing_extension_id = bea.billing_extension_id
500 and bea.billing_assignment_id = e.billing_assignment_id
501 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');
502
503 BEGIN
504
505 IF g1_debug_mode = 'Y' THEN
506 PA_MCB_INVOICE_PKG.log_message('Enetering pa_bill_pct.PotEventAmount ');
507 END IF;
508 /** Sum of all event amounts other than events created by percent complete **/
509 /*The following sql has been commented for MCB2 */
510 /* SELECT sum(decode(et.event_type_classification,
511 'WRITE OFF', -1 * nvl(revenue_amount,0),
512 nvl(revenue_amount,0))),
513 sum(decode(et.event_type_classification,
514 'INVOICE REDUCTION', -1 * nvl(bill_amount,0),
515 nvl(bill_amount,0)))
516 INTO X2_revenue_amount,
517 X2_invoice_amount
518 FROM pa_events e,
519 pa_event_types et
520 WHERE e.event_type = et.event_type
521 AND e.project_id = X2_project_id
522 AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
523 AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
524 AND NOT EXISTS ( select '1'
525 from pa_billing_assignments bea,
526 pa_billing_extensions be
527 where be.billing_extension_id = bea.billing_extension_id
528 and bea.billing_assignment_id = e.billing_assignment_id
529 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'); */
530
531 /* Following code has been added for MCB2 */
532 l_project_id := X2_project_id;
533 l_calling_process := pa_billing.GetCallProcess;
534 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
535 p_project_id => l_project_id,
536 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
537 x_baseline_funding_flag => l_baseline_funding_flag,
538 x_revproc_currency_code => l_revproc_currency_code,
539 x_invproc_currency_type => l_invproc_currency_type,
540 x_invproc_currency_code => l_invproc_currency_code,
541 x_project_currency_code => l_project_currency_code,
542 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
543 x_project_bil_rate_type => l_project_bil_rate_type,
544 x_project_bil_rate_date => l_project_bil_rate_date,
545 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
546 x_projfunc_currency_code => l_projfunc_currency_code,
547 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
548 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
549 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
550 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
551 x_funding_rate_date_code => l_funding_rate_date_code,
552 x_funding_rate_type => l_funding_rate_type,
553 x_funding_rate_date => l_funding_rate_date,
554 x_funding_exchange_rate => l_funding_exchange_rate,
555 x_return_status => l_return_status,
556 x_msg_count => l_msg_count,
557 x_msg_data => l_msg_data);
558 OPEN pctfunc_rev_inv_amt( X2_project_id,X2_task_id,X2_accrue_through_date);
559 Loop
560 FETCH pctfunc_rev_inv_amt INTO l_trans_rev_amt,l_trans_bill_amt,l_txn_currency_code,
561 l_projfunc_currency_code,l_projfunc_rate_type,
562 l_projfunc_rate_date,l_projfunc_exchange_rate;
563 EXIT WHEN pctfunc_rev_inv_amt%NOTFOUND;
564 IF ( l_calling_process = 'Revenue' ) THEN
565 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
566 l_passd_amt := l_trans_rev_amt;
567 ELSIF ( l_calling_process = 'Invoice' ) THEN
568 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
569 l_passd_amt := l_trans_bill_amt;
570 END IF;
571 /* Calling convert amount proc to convert this amount in PFC */
572 PA_MULTI_CURRENCY.convert_amount(
573 P_FROM_CURRENCY => l_txn_currency_code,
574 P_TO_CURRENCY => l_projfunc_currency_code,
575 P_CONVERSION_DATE => l_projfunc_rate_date,
576 P_CONVERSION_TYPE => l_projfunc_rate_type,
577 P_AMOUNT => l_passd_amt,
578 P_USER_VALIDATE_FLAG => 'Y',
579 P_HANDLE_EXCEPTION_FLAG => 'Y',
580 P_CONVERTED_AMOUNT => l_converted_amount,
581 P_DENOMINATOR => l_denominator,
582 P_NUMERATOR => l_numerator,
583 P_RATE => l_projfunc_exchange_rate,
584 X_STATUS => l_staus);
585 IF ( l_staus IS NOT NULL ) THEN
586 l_converted_amount := 0;
587 END IF;
588
589 l_projfunc_amount_sum := NVL(l_projfunc_amount_sum,0) + NVL(l_converted_amount,0);
590 IF g1_debug_mode = 'Y' THEN
591 PA_MCB_INVOICE_PKG.log_message('The calculated amount after convert amount inside pa_bill_pct.PotEventAmount : '||to_char(l_projfunc_amount_sum)||'calling process '||l_calling_process);
592 END IF;
593 End Loop;
594 Close pctfunc_rev_inv_amt;
595
596 IF ( l_calling_process = 'Revenue' ) THEN
597 X2_revenue_amount := l_projfunc_amount_sum;
598 X2_invoice_amount := 0;
599 ELSIF ( l_calling_process = 'Invoice' ) THEN
600 X2_revenue_amount := 0;
601 X2_invoice_amount := l_projfunc_amount_sum;
602 END IF;
603
604 IF g1_debug_mode = 'Y' THEN
605 PA_MCB_INVOICE_PKG.log_message('Exiting from pa_bill_pct.PotEventAmount ');
606 END IF;
607 /* Added the below for NOCOPY mandate */
608 EXCEPTION WHEN OTHERS THEN
609 X2_revenue_amount := 0;
610 X2_invoice_amount := 0;
611 END PotEventAmount;
612
613
614 Procedure RevenueAmount( X2_project_id NUMBER,
615 X2_task_Id NUMBER DEFAULT NULL,
616 X2_revenue_amount OUT NOCOPY REAL) IS
617
618 pending_pctrev REAL;
619 accrued_pctrev REAL;
620
621 /* Varible for MCB2 */
622 l_trans_rev_amt pa_events.bill_trans_rev_amount%TYPE;
623 l_projfunc_rev_amount_sum pa_events.projfunc_revenue_amount%TYPE;
624 l_converted_rev_amount pa_events.projfunc_revenue_amount%TYPE;
625 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
626 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
627 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
628 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
629 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
630 l_event_date pa_events.completion_date%TYPE;
631 l_conv_date pa_events.completion_date%TYPE;
632 l_denominator Number;
633 l_numerator Number;
634 l_staus Varchar2(30);
635 l_project_id pa_projects_all.project_id%TYPE;
636 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
637 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
638 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
639 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
640 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
641 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
642 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
643 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
644 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
645 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
646 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
647 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
648 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
649 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
650 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
651 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
652 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
653 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
654 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
655 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
656 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
657 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
658 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
659 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
660 l_return_status VARCHAR2(30);
661 l_msg_count NUMBER;
662 l_msg_data VARCHAR2(30);
663
664 CURSOR pctfunc_revenue(X2_project_id Number,X2_task_id Number) IS
665 SELECT NVL(e.bill_trans_rev_amount,0) trans_rev_amount,e.bill_trans_currency_code,
666 e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
667 FROM pa_events e,
668 pa_billing_assignments bea,
669 pa_billing_extensions be
670 where be.billing_extension_id = bea.billing_extension_id
671 and e.project_id = X2_project_id
672 and nvl(e.task_id,0) =
673 decode(X2_task_id,
674 NULL, nvl(e.task_id,0), X2_task_id )
675 and bea.billing_assignment_id = e.billing_assignment_id
676 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
677 and e.revenue_distributed_flag||'' = 'N';
678
679 BEGIN
680
681 IF g1_debug_mode = 'Y' THEN
682 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Entering pa_bill_pct.RevenuAmount ');
683 END IF;
684 -- Percent Complete Revenue that has been accrued.
685 /* change this column from amount to projfunc_revenue_amount for MCB2 */
686 SELECT sum(nvl(dri.projfunc_revenue_amount,0))
687 INTO accrued_pctrev
688 FROM pa_draft_revenue_items dri
689 WHERE dri.project_id = X2_project_id
690 AND nvl(dri.task_id,0) = decode(X2_task_id, NULL, nvl(dri.task_id,0), X2_task_id )
691 AND (EXISTS ( select '1'
692 from pa_cust_event_rev_dist_lines erdl,
693 pa_events e,
694 pa_billing_assignments bea,
695 pa_billing_extensions be
696 where be.billing_extension_id = bea.billing_extension_id
697 and bea.billing_assignment_id = e.billing_assignment_id
698 and e.project_id = erdl.project_id
699 and e.event_num = erdl.event_num
700 and nvl(e.task_id,0) = nvl(erdl.task_id, 0)
701 and erdl.project_id = dri.project_id
702 and erdl.draft_revenue_num = dri.draft_revenue_num
703 and erdl.draft_revenue_item_line_num = dri.line_num
704 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
705 OR dri.revenue_source like 'Expenditure%');
706
707 IF g1_debug_mode = 'Y' THEN
708 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The accrued_pctrev inside pa_bill_pct.RevenuAmount: '||to_char(accrued_pctrev));
709 END IF;
710 -- Percent Complete revenue that has not been created as events
711 -- but not accrued yet.
712 -- This could be due to unauthorized task or an erroring request.
713 /* The following code is commented because this amount is in RPC i.e. Revenue programm is going to populate this amount
714 */
715 /*
716 SELECT sum(nvl(e.revenue_amount,0))
717 INTO pending_pctrev
718 FROM pa_events e,
719 pa_billing_assignments bea,
720 pa_billing_extensions be
721 where be.billing_extension_id = bea.billing_extension_id
722 and e.project_id = X2_project_id
723 and nvl(e.task_id,0) =
724 decode(X2_task_id,
725 NULL, nvl(e.task_id,0), X2_task_id )
726 and bea.billing_assignment_id = e.billing_assignment_id
727 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
728 and e.revenue_distributed_flag||'' = 'N'; */
729
730 /* Following code has been added for MCB2 */
731 l_project_id := X2_project_id;
732 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
733 p_project_id => l_project_id,
734 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
735 x_baseline_funding_flag => l_baseline_funding_flag,
736 x_revproc_currency_code => l_revproc_currency_code,
737 x_invproc_currency_type => l_invproc_currency_type,
738 x_invproc_currency_code => l_invproc_currency_code,
739 x_project_currency_code => l_project_currency_code,
740 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
741 x_project_bil_rate_type => l_project_bil_rate_type,
742 x_project_bil_rate_date => l_project_bil_rate_date,
743 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
744 x_projfunc_currency_code => l_projfunc_currency_code,
745 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
746 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
747 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
748 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
749 x_funding_rate_date_code => l_funding_rate_date_code,
750 x_funding_rate_type => l_funding_rate_type,
751 x_funding_rate_date => l_funding_rate_date,
752 x_funding_exchange_rate => l_funding_exchange_rate,
753 x_return_status => l_return_status,
754 x_msg_count => l_msg_count,
755 x_msg_data => l_msg_data);
756
757 OPEN pctfunc_revenue( X2_project_id,X2_task_id);
758 Loop
759 FETCH pctfunc_revenue INTO l_trans_rev_amt,l_txn_currency_code,l_projfunc_currency_code,
760 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
761 EXIT WHEN pctfunc_revenue%NOTFOUND;
762 IF ( l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
763 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
764 END IF;
765
766 /* Calling convert amount proc to convert this amount in PFC */
767 PA_MULTI_CURRENCY.convert_amount(
768 P_FROM_CURRENCY => l_txn_currency_code,
769 P_TO_CURRENCY => l_projfunc_currency_code,
770 P_CONVERSION_DATE => l_projfunc_rate_date,
771 P_CONVERSION_TYPE => l_projfunc_rate_type,
772 P_AMOUNT => l_trans_rev_amt,
773 P_USER_VALIDATE_FLAG => 'Y',
774 P_HANDLE_EXCEPTION_FLAG => 'Y',
775 P_CONVERTED_AMOUNT => l_converted_rev_amount,
776 P_DENOMINATOR => l_denominator,
777 P_NUMERATOR => l_numerator,
778 P_RATE => l_projfunc_exchange_rate,
779 X_STATUS => l_staus);
780 IF ( l_staus IS NOT NULL ) THEN
781 l_converted_rev_amount := 0;
782 END IF;
783
784 l_projfunc_rev_amount_sum := NVL(l_projfunc_rev_amount_sum,0) + NVL(l_converted_rev_amount,0);
785 End Loop;
786 Close pctfunc_revenue;
787 pending_pctrev := l_projfunc_rev_amount_sum;
788
789 IF g1_debug_mode = 'Y' THEN
790 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The pending_pctrev : inside pa_bill_pct.RevenuAmount '||to_char(pending_pctrev ));
791 END IF;
792 X2_revenue_amount := nvl(accrued_pctrev,0) + nvl(pending_pctrev,0);
793
794 IF g1_debug_mode = 'Y' THEN
795 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Exiting pa_bill_pct.RevenuAmount ');
796 END IF;
797
798 /* Added the below for NOCOPY mandate */
799 EXCEPTION WHEN OTHERS THEN
800 X2_revenue_amount := NULL;
801 END RevenueAmount;
802
803 Procedure InvoiceAmount( X2_project_id NUMBER,
804 X2_task_id NUMBER default NULL,
805 X2_invoice_amount OUT NOCOPY REAL) IS
806
807 pending_ccinv REAL;
808 task_billed_ccinv REAL;
809 task_billed_ev_ccinv REAL;
810 billed_ccinv REAL;
811
812 /* Varibles added for MCB2 */
813 l_trans_bill_amt pa_events.bill_trans_bill_amount%TYPE;
814 l_projfunc_bill_amount_sum pa_events.projfunc_bill_amount%TYPE;
815 l_converted_bill_amount pa_events.projfunc_bill_amount%TYPE;
816 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
817 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
818 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
819 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
820 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
821 l_event_date pa_events.completion_date%TYPE;
822 l_conv_date pa_events.completion_date%TYPE;
823 l_denominator Number;
824 l_numerator Number;
825 l_staus Varchar2(30);
826 l_project_id pa_projects_all.project_id%TYPE;
827 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
828 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
829 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
830 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
831 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
832 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
833 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
834 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
835 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
836 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
837 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
838 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
839 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
840 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
841 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
842 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
843 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
844 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
845 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
846 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
847 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
848 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
849 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
850 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
851 l_return_status VARCHAR2(30);
852 l_msg_count NUMBER;
853 l_msg_data VARCHAR2(30);
854
855 CURSOR pctfunc_invoice(X2_project_id Number,X2_task_id Number) IS
856 SELECT NVL(e.bill_trans_bill_amount,0) trans_bill_amount,e.bill_trans_currency_code,e.projfunc_currency_code,
857 e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
858 FROM pa_events e,
859 pa_billing_assignments bea,
860 pa_billing_extensions be
861 WHERE be.billing_extension_id = bea.billing_extension_id
862 AND bea.billing_assignment_id = e.billing_assignment_id
863 AND e.project_id = X2_project_id
864 AND be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
865 AND nvl(e.task_id,0) = decode(X2_task_id,
866 NULL, nvl(e.task_id,0), X2_task_id)
867 AND NOT EXISTS
868 (select 'billed'
869 from pa_draft_invoice_items pdii
870 where pdii.project_id = e.project_id
871 and pdii.event_num = e.event_num
872 and nvl(pdii.task_id,0) = nvl(e.task_id,0));
873
874 BEGIN
875 IF g1_debug_mode = 'Y' THEN
876 PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.InvoiceAmount ');
877 END IF;
878
879 -- Percent Complete Invoice Amount that has been created as an event,
880 -- but not billed yet.
881 /* The following code is commented because this amount is in IPC i.e. Invoice programm is going to populate this amount
882 */
883 /*
884 SELECT sum(nvl(e.bill_amount,0))
885 INTO pending_ccinv
886 from pa_events e,
887 pa_billing_assignments bea,
888 pa_billing_extensions be
889 where be.billing_extension_id = bea.billing_extension_id
890 and bea.billing_assignment_id = e.billing_assignment_id
891 and e.project_id = X2_project_id
892 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
893 and nvl(e.task_id,0) = decode(X2_task_id,
894 NULL, nvl(e.task_id,0), X2_task_id)
895 and NOT EXISTS
896 (select 'billed'
897 from pa_draft_invoice_items pdii
898 where pdii.project_id = e.project_id
899 and pdii.event_num = e.event_num
900 and nvl(pdii.task_id,0) = nvl(e.task_id,0)); */
901
902 /* Following code has been added for MCB2 */
903 l_project_id := X2_project_id;
904 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
905 p_project_id => l_project_id,
906 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
907 x_baseline_funding_flag => l_baseline_funding_flag,
908 x_revproc_currency_code => l_revproc_currency_code,
909 x_invproc_currency_type => l_invproc_currency_type,
910 x_invproc_currency_code => l_invproc_currency_code,
911 x_project_currency_code => l_project_currency_code,
912 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
913 x_project_bil_rate_type => l_project_bil_rate_type,
914 x_project_bil_rate_date => l_project_bil_rate_date,
915 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
916 x_projfunc_currency_code => l_projfunc_currency_code,
917 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
918 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
919 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
920 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
921 x_funding_rate_date_code => l_funding_rate_date_code,
922 x_funding_rate_type => l_funding_rate_type,
923 x_funding_rate_date => l_funding_rate_date,
924 x_funding_exchange_rate => l_funding_exchange_rate,
925 x_return_status => l_return_status,
926 x_msg_count => l_msg_count,
927 x_msg_data => l_msg_data);
928
929 OPEN pctfunc_invoice( X2_project_id,X2_task_id);
930 Loop
931 FETCH pctfunc_invoice INTO l_trans_bill_amt,l_txn_currency_code,l_projfunc_currency_code,
932 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
933 EXIT WHEN pctfunc_invoice%NOTFOUND;
934 IF ( l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
935 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
936 END IF;
937 /* Calling convert amount proc to convert this amount in PFC */
938 PA_MULTI_CURRENCY.convert_amount(
939 P_FROM_CURRENCY => l_txn_currency_code,
940 P_TO_CURRENCY => l_projfunc_currency_code,
941 P_CONVERSION_DATE => l_projfunc_rate_date,
942 P_CONVERSION_TYPE => l_projfunc_rate_type,
943 P_AMOUNT => l_trans_bill_amt,
944 P_USER_VALIDATE_FLAG => 'Y',
945 P_HANDLE_EXCEPTION_FLAG => 'Y',
946 P_CONVERTED_AMOUNT => l_converted_bill_amount,
947 P_DENOMINATOR => l_denominator,
948 P_NUMERATOR => l_numerator,
949 P_RATE => l_projfunc_exchange_rate,
950 X_STATUS => l_staus);
951 IF ( l_staus IS NOT NULL ) THEN
952 l_converted_bill_amount := 0;
953 END IF;
954
955 l_projfunc_bill_amount_sum := NVL(l_projfunc_bill_amount_sum,0) + NVL(l_converted_bill_amount,0);
956 End Loop;
957 Close pctfunc_invoice;
958 pending_ccinv := l_projfunc_bill_amount_sum;
959
960
961 IF g1_debug_mode = 'Y' THEN
962 PA_MCB_INVOICE_PKG.log_message('The pending_ccinv inside pa_bill_pct.InvoiceAmount: '||to_char(pending_ccinv));
963 END IF;
964 IF (X2_task_id IS NULL) THEN
965
966 -- Percent Complete Invoice Amount that has been billed, or originates from
967 -- expenditure items (historical cost-cost invoice amount)
968
969 /* Change this column from amount to projfunc_bill_amount for MCB2 */
970 SELECT sum(nvl(dii.projfunc_bill_amount,0))
971 INTO billed_ccinv
972 FROM pa_draft_invoice_items dii
973 WHERE dii.project_id = X2_project_id
974 AND (EXISTS (select '1'
975 from pa_events e,
976 pa_billing_assignments bea,
977 pa_billing_extensions be
978 where be.billing_extension_id = bea.billing_extension_id
979 and bea.billing_assignment_id = e.billing_assignment_id
980 and dii.project_id = e.project_id
981 and dii.event_num = e.event_num
982 and nvl(dii.event_task_id,0) = nvl(e.task_id,0)
983 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
984 OR EXISTS ( select '1'
985 from pa_cust_rev_dist_lines erdl
986 where erdl.project_id = dii.project_id
987 and erdl.draft_invoice_num = dii.draft_invoice_num
988 and erdl.draft_invoice_item_line_num = dii.line_num));
989
990 IF g1_debug_mode = 'Y' THEN
991 PA_MCB_INVOICE_PKG.log_message('The billed_ccinv for task id is null inside pa_bill_pct.InvoiceAmount: '||to_char(billed_ccinv));
992 END IF;
993 X2_invoice_amount := nvl(pending_ccinv,0) + nvl(billed_ccinv,0);
994
995 ELSE
996
997 /* Change this column from amount to projfunc_bill_amount for MCB2 */
998 SELECT sum(nvl(rdl.projfunc_bill_amount,0))
999 INTO task_billed_ccinv
1000 FROM pa_cust_rev_dist_lines rdl,
1001 pa_expenditure_items_all ei,
1002 pa_tasks t
1003 WHERE ei.task_id = t.task_id
1004 AND ei.Project_ID = t.Project_ID -- Perf Bug 2695332
1005 AND ei.expenditure_item_id = rdl.expenditure_item_id
1006 AND rdl.project_id = X2_project_id
1007 AND t.top_task_id = X2_task_id
1008 AND rdl.draft_invoice_num IS NOT NULL;
1009
1010 IF g1_debug_mode = 'Y' THEN
1011 PA_MCB_INVOICE_PKG.log_message('The task_billed_ccinv for task id is not null inside pa_bill_pct.InvoiceAmount: '||to_char(task_billed_ccinv));
1012 END IF;
1013 /* Change this column from amount to projfunc_bill_amount for MCB2 */
1014 SELECT sum(nvl(pdii.projfunc_bill_amount,0))
1015 INTO task_billed_ev_ccinv
1016 FROM pa_draft_invoice_items pdii
1017 WHERE pdii.event_task_id = X2_task_id
1018 AND pdii.Project_ID = X2_Project_ID -- Perf Bug 2695332
1019 AND EXISTS (select '1'
1020 from pa_events e,
1021 pa_billing_assignments bea,
1022 pa_billing_extensions be
1023 where be.billing_extension_id = bea.billing_extension_id
1024 and bea.billing_assignment_id = e.billing_assignment_id
1025 and pdii.project_id = e.project_id
1026 and pdii.event_num = e.event_num
1027 and pdii.event_task_id = e.task_id
1028 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');
1029
1030 IF g1_debug_mode = 'Y' THEN
1031 PA_MCB_INVOICE_PKG.log_message('The task_billed_ev_ccinv for task id is not null inside pa_bill_pct.InvoiceAmount: '||to_char(task_billed_ev_ccinv));
1032 END IF;
1033 X2_invoice_amount := nvl(task_billed_ccinv,0) + nvl(task_billed_ev_ccinv,0) + nvl(pending_ccinv,0);
1034
1035 END IF;
1036
1037 IF g1_debug_mode = 'Y' THEN
1038 PA_MCB_INVOICE_PKG.log_message('Exiting pa_bill_pct.InvoiceAmount: ');
1039 END IF;
1040 /* Added the below for NOCOPY mandate */
1041 EXCEPTION WHEN OTHERS THEN
1042 X2_invoice_amount := NULL;
1043 END InvoiceAmount;
1044
1045
1046 Function GetPercentComplete( X2_project_id NUMBER,
1047 X2_task_id NUMBER DEFAULT NULL,
1048 X2_accrue_through_date DATE DEFAULT NULL)
1049 RETURN REAL IS
1050
1051 percent_comp REAL :=0;
1052
1053 -- Cursor to select the most recently entered completion percentage
1054 --
1055 /* Patchset K : Percent complete changes :
1056 Using the view PA_PERCENT_COMPLETES_FIN_V instead of pa_percent_completes */
1057
1058
1059 /* Changed cursor to pickup record based on sequence number (percent_complete_id)
1060 for bug no 1283352.
1061
1062 CURSOR pct IS
1063 SELECT NVL(completed_percentage,0)
1064 FROM PA_PERCENT_COMPLETES_FIN_V ppc
1065 WHERE
1066 ppc.project_id = X2_project_id AND
1067 nvl(ppc.task_id,0) = nvl(X2_task_id,0 )
1068 AND ppc.date_computed
1069 =
1070 (SELECT max(date_computed)
1071 from PA_PERCENT_COMPLETES_FIN_V
1072 where date_computed <= nvl(X2_accrue_through_date, sysdate)
1073 and project_id = X2_project_id
1074 and nvl(task_id,0) = nvl(X2_task_id,0)
1075 )
1076 ORDER BY creation_date desc
1077 ;
1078 bug fix 1283352 "commentation" ends. */
1079
1080 CURSOR pct IS
1081 SELECT NVL(completed_percentage,0)
1082 FROM PA_PERCENT_COMPLETES_FIN_V ppc
1083 WHERE
1084 ppc.project_id = X2_project_id
1085 And nvl(ppc.task_id,0) = nvl(X2_task_id,0 )
1086 And ppc.date_computed <= nvl(X2_accrue_through_date, sysdate)
1087 And ppc.percent_complete_id = ( Select max(ppcx.percent_complete_id)
1088 from PA_PERCENT_COMPLETES_FIN_V ppcx
1089 where project_id = X2_project_id
1090 and nvl(task_id,0) = nvl(X2_task_id,0)
1091 and ppcx.date_computed = (
1092 Select max(ppcy.date_computed)
1093 from PA_PERCENT_COMPLETES_FIN_V ppcy
1094 Where ppcy.date_computed <= nvl(X2_accrue_through_date, sysdate)
1095 and ppcy.project_id = X2_project_id
1096 and nvl(ppcy.task_id,0) = nvl(X2_task_id,0)));
1097 BEGIN
1098
1099 /** get the most recent percent complete before the accru thru date **/
1100
1101 -- PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.GetPercentComplete: ');
1102 OPEN pct;
1103
1104 FETCH pct INTO percent_comp;
1105
1106 -- PA_MCB_INVOICE_PKG.log_message('Inside the loop of pa_bill_pct.GetPercentComplete: ');
1107 CLOSE pct;
1108
1109 --PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.GetPercentComplete: ');
1110 RETURN percent_comp;
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 CLOSE pct;
1115 RETURN 0;
1116
1117 END GetPercentComplete;
1118
1119 ---------------------
1120 -- GLOBALS
1121 --
1122
1123 -- get_rev_budget_amount modified to use User defined budget types
1124 -- and use api pa_budget_utils.get_project_task_totals
1125 --
1126 PROCEDURE get_rev_budget_amount( X2_project_id NUMBER,
1127 X2_task_id NUMBER DEFAULT NULL,
1128 X2_revenue_amount OUT NOCOPY REAL,
1129 P_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
1130 P_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
1131 X_rev_budget_type_code OUT NOCOPY VARCHAR2,
1132 X_rev_plan_type_id OUT NOCOPY NUMBER, /* Added for Fin plan impact */
1133 X_error_message OUT NOCOPY VARCHAR2,
1134 X_status OUT NOCOPY NUMBER
1135 ) IS
1136
1137 -- local variables for budget codes
1138 status VARCHAR2(240); -- For error messages from subprogs
1139 l_rev_budget_type_code VARCHAR2(30) ;
1140 l_rev_budget_status_code VARCHAR2(1) ;
1141 dummy CHAR(1);
1142 err_msg VARCHAR2(240);
1143 err_status NUMBER;
1144 l_status NUMBER;
1145 l_rev_budget_version_id NUMBER;
1146 l_raw_cost_total REAL := 0;
1147 l_revenue_total REAL := 0;
1148 l_quantity_total NUMBER;
1149 l_burdened_cost_total NUMBER;
1150 l_err_code NUMBER;
1151 l_err_stage VARCHAR2(30);
1152 l_err_stack VARCHAR2(630);
1153 invalid_rev_budget_code EXCEPTION;
1154 rev_budget_not_baselined EXCEPTION;
1155
1156 /* Added for Fin plan impact */
1157 l_rev_plan_version_id NUMBER;
1158 l_rev_plan_type_id NUMBER ;
1159 /* till here */
1160
1161 BEGIN
1162 IF g1_debug_mode = 'Y' THEN
1163 PA_MCB_INVOICE_PKG.log_message(' Entering pa_bill_pct.get_rev_budget_amount: ');
1164 END IF;
1165 X_status := 0;
1166 X_error_message := NULL;
1167 BEGIN
1168 -- If user doesnt provide the budget get the default value from biling extensions
1169 --
1170
1171 IF ( P_rev_budget_type_code IS NULL) OR (P_rev_plan_type_id IS NULL) THEN
1172
1173 SELECT DECODE(P_rev_budget_type_code,NULL,default_rev_budget_type_code,P_rev_budget_type_code),
1174 DECODE(P_rev_plan_type_id,NULL,default_rev_plan_type_id,
1175 P_rev_plan_type_id) /* Added for fin plan type id */
1176 INTO l_rev_budget_type_code,
1177 l_rev_plan_type_id
1178 FROM pa_billing_extensions
1179 WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
1180
1181 IF g1_debug_mode = 'Y' THEN
1182 PA_MCB_INVOICE_PKG.log_message(' getting l_rev_budget_type_code inside pa_bill_pct.get_rev_budget_amount: '||l_rev_budget_type_code);
1183 END IF;
1184 END IF;
1185
1186
1187 -- The plan code should be a valid code and of the right version type
1188 -- If invalid then process will check for budget code
1189 /* Added this select for Fin plan Impact */
1190 BEGIN
1191 SELECT 'x'
1192 INTO dummy
1193 FROM dual
1194 WHERE EXISTS( SELECT *
1195 FROM pa_fin_plan_types_b f
1196 WHERE f.fin_plan_type_id=l_rev_plan_type_id );
1197
1198 EXCEPTION
1199 WHEN NO_DATA_FOUND THEN
1200
1201 -- The budget code should be a valid code and of the right amount code
1202 -- If invalid then raise appropriate exception
1203
1204 BEGIN
1205
1206 SELECT 'x'
1207 INTO dummy
1208 FROM pa_budget_types
1209 WHERE budget_type_code = l_rev_budget_type_code
1210 AND budget_amount_code = 'R';
1211
1212 EXCEPTION
1213 WHEN NO_DATA_FOUND THEN
1214 raise invalid_rev_budget_code;
1215 END ;
1216
1217 END; /* End of newly added code for Fin plan Impact */
1218
1219
1220 /* Added this select for Fin plan Impact */
1221 BEGIN
1222
1223 SELECT v.budget_version_id
1224 INTO l_rev_plan_version_id
1225 FROM pa_budget_versions v
1226 WHERE v.project_id = X2_project_id
1227 AND v.current_flag = 'Y'
1228 AND v.budget_status_code = 'B'
1229 AND v.fin_plan_type_id = l_rev_plan_type_id
1230 AND v.version_type IN ('REVENUE','ALL');
1231
1232 EXCEPTION
1233 WHEN NO_DATA_FOUND THEN
1234 -- get the budget version id for cost and revenue budget
1235 -- Changed to use api pa_budget_utils.get_project_task_totals
1236
1237 BEGIN
1238
1239 SELECT budget_version_id
1240 INTO l_rev_budget_version_id
1241 FROM pa_budget_versions pbv
1242 WHERE project_id = X2_project_id
1243 AND budget_type_code = l_rev_budget_type_code
1244 AND budget_status_code = 'B'
1245 AND current_flag = 'Y';
1246
1247 IF g1_debug_mode = 'Y' THEN
1248 PA_MCB_INVOICE_PKG.log_message(' getting l_rev_budget_version_id inside pa_bill_pct.get_rev_budget_amount: '||l_rev_budget_version_id);
1249 END IF;
1250 EXCEPTION
1251 WHEN NO_DATA_FOUND THEN
1252 raise rev_budget_not_baselined;
1253 END;
1254
1255 END; /* End of newly added code for Fin plan Impact */
1256
1257 l_rev_budget_version_id := NVL(l_rev_plan_version_id,l_rev_budget_version_id);
1258
1259
1260 -- Call api to get revenue budget amount
1261 --
1262 pa_budget_utils.get_project_task_totals
1263 (l_rev_budget_version_id ,
1264 x2_task_id ,
1265 l_quantity_total,
1266 l_raw_cost_total ,
1267 l_burdened_cost_total ,
1268 l_revenue_total ,
1269 l_err_code ,
1270 l_err_stage,
1271 l_err_stack );
1272
1273 X2_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
1274
1275 IF g1_debug_mode = 'Y' THEN
1276 PA_MCB_INVOICE_PKG.log_message(' getting l_revenue_total inside pa_bill_pct.get_rev_budget_amount: '||to_char(l_revenue_total));
1277 END IF;
1278 X_rev_budget_type_code := l_rev_budget_type_code;
1279 X_rev_plan_type_id := l_rev_plan_type_id; /* Added for Fin plan impact */
1280
1281 -- If any exception then raise it to the calling pl/sql block
1282 --
1283 IF g1_debug_mode = 'Y' THEN
1284 PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.get_rev_budget_amount: ');
1285 END IF;
1286 EXCEPTION
1287 WHEN invalid_rev_budget_code THEN
1288 status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
1289 l_status := 2;
1290 RAISE_APPLICATION_ERROR(-20101,status);
1291 WHEN rev_budget_not_baselined THEN
1292 status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
1293 l_status := 3;
1294 RAISE_APPLICATION_ERROR(-20101,status);
1295 WHEN OTHERS THEN
1296 status := substr(SQLERRM,1,240);
1297 l_status := sqlcode;
1298 RAISE;
1299 END;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 -- DBMS_OUTPUT.PUT_LINE(status);
1304 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1305 X2_revenue_amount := NULL;
1306 X_rev_budget_type_code := NULL;
1307 ----
1308 X_rev_plan_type_id := NULL;
1309
1310 X_error_message := status;
1311 X_status := l_status;
1312
1313 pa_billing_pub.insert_message
1314 (X_inserting_procedure_name =>'pa_billing_pct.get_rev_budget_amount',
1315 X_attribute2 => l_rev_budget_type_code,
1316 X_message => status,
1317 X_error_message=>err_msg,
1318 X_status=>err_status);
1319
1320 IF (l_status < 0 OR NVL(err_status,0) <0) THEN
1321 RAISE;
1322 END IF;
1323
1324 END get_rev_budget_amount;
1325 END pa_bill_pct;