[Home] [Help]
PACKAGE BODY: APPS.PA_BILL_PCT
Source
1 PACKAGE BODY pa_bill_pct AS
2 /* $Header: PAXPCTB.pls 120.3 2007/12/06 07:23:47 arbandyo 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 CURSOR pctfunc_rev_inv_amt(X2_project_id Number,X2_task_id Number,X2_accrue_through_date Date) IS
481 SELECT (DECODE(et.event_type_classification,
482 'WRITE OFF',-1 * nvl(bill_trans_rev_amount,0),
483 'RLZED_LOSSES',-1 * nvl(bill_trans_rev_amount,0),
484 NVL(bill_trans_rev_amount,0))) trans_rev_amount,
485 (DECODE(et.event_type_classification,'INVOICE REDUCTION', -1 * nvl(bill_trans_bill_amount,0),
486 NVL(bill_trans_bill_amount,0))) trans_bill_amount,e.bill_trans_currency_code,e.projfunc_currency_code,
487 e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
488 FROM pa_events e,
489 pa_event_types et
490 WHERE e.event_type = et.event_type
491 AND e.project_id = X2_project_id
492 AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
493 AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
494 AND NOT EXISTS ( select '1'
495 from pa_billing_assignments bea,
496 pa_billing_extensions be
497 where be.billing_extension_id = bea.billing_extension_id
498 and bea.billing_assignment_id = e.billing_assignment_id
499 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');
500
501 BEGIN
502
503 IF g1_debug_mode = 'Y' THEN
504 PA_MCB_INVOICE_PKG.log_message('Enetering pa_bill_pct.PotEventAmount ');
505 END IF;
506 /** Sum of all event amounts other than events created by percent complete **/
507 /*The following sql has been commented for MCB2 */
508 /* SELECT sum(decode(et.event_type_classification,
509 'WRITE OFF', -1 * nvl(revenue_amount,0),
510 nvl(revenue_amount,0))),
511 sum(decode(et.event_type_classification,
512 'INVOICE REDUCTION', -1 * nvl(bill_amount,0),
513 nvl(bill_amount,0)))
514 INTO X2_revenue_amount,
515 X2_invoice_amount
516 FROM pa_events e,
517 pa_event_types et
518 WHERE e.event_type = et.event_type
519 AND e.project_id = X2_project_id
520 AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
521 AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
522 AND NOT EXISTS ( select '1'
523 from pa_billing_assignments bea,
524 pa_billing_extensions be
525 where be.billing_extension_id = bea.billing_extension_id
526 and bea.billing_assignment_id = e.billing_assignment_id
527 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'); */
528
529 /* Following code has been added for MCB2 */
530 l_project_id := X2_project_id;
531 l_calling_process := pa_billing.GetCallProcess;
532 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
533 p_project_id => l_project_id,
534 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
535 x_baseline_funding_flag => l_baseline_funding_flag,
536 x_revproc_currency_code => l_revproc_currency_code,
537 x_invproc_currency_type => l_invproc_currency_type,
538 x_invproc_currency_code => l_invproc_currency_code,
539 x_project_currency_code => l_project_currency_code,
540 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
541 x_project_bil_rate_type => l_project_bil_rate_type,
542 x_project_bil_rate_date => l_project_bil_rate_date,
543 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
544 x_projfunc_currency_code => l_projfunc_currency_code,
545 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
546 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
547 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
548 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
549 x_funding_rate_date_code => l_funding_rate_date_code,
550 x_funding_rate_type => l_funding_rate_type,
551 x_funding_rate_date => l_funding_rate_date,
552 x_funding_exchange_rate => l_funding_exchange_rate,
553 x_return_status => l_return_status,
554 x_msg_count => l_msg_count,
555 x_msg_data => l_msg_data);
556 OPEN pctfunc_rev_inv_amt( X2_project_id,X2_task_id,X2_accrue_through_date);
557 Loop
558 FETCH pctfunc_rev_inv_amt INTO l_trans_rev_amt,l_trans_bill_amt,l_txn_currency_code,
559 l_projfunc_currency_code,l_projfunc_rate_type,
560 l_projfunc_rate_date,l_projfunc_exchange_rate;
561 EXIT WHEN pctfunc_rev_inv_amt%NOTFOUND;
562 IF ( l_calling_process = 'Revenue' ) THEN
563 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
564 l_passd_amt := l_trans_rev_amt;
565 ELSIF ( l_calling_process = 'Invoice' ) THEN
566 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
567 l_passd_amt := l_trans_bill_amt;
568 END IF;
569 /* Calling convert amount proc to convert this amount in PFC */
570 PA_MULTI_CURRENCY.convert_amount(
571 P_FROM_CURRENCY => l_txn_currency_code,
572 P_TO_CURRENCY => l_projfunc_currency_code,
573 P_CONVERSION_DATE => l_projfunc_rate_date,
574 P_CONVERSION_TYPE => l_projfunc_rate_type,
575 P_AMOUNT => l_passd_amt,
576 P_USER_VALIDATE_FLAG => 'Y',
577 P_HANDLE_EXCEPTION_FLAG => 'Y',
578 P_CONVERTED_AMOUNT => l_converted_amount,
579 P_DENOMINATOR => l_denominator,
580 P_NUMERATOR => l_numerator,
581 P_RATE => l_projfunc_exchange_rate,
582 X_STATUS => l_staus);
583 IF ( l_staus IS NOT NULL ) THEN
584 l_converted_amount := 0;
585 END IF;
586
587 l_projfunc_amount_sum := NVL(l_projfunc_amount_sum,0) + NVL(l_converted_amount,0);
588 IF g1_debug_mode = 'Y' THEN
589 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);
590 END IF;
591 End Loop;
592 Close pctfunc_rev_inv_amt;
593
594 IF ( l_calling_process = 'Revenue' ) THEN
595 X2_revenue_amount := l_projfunc_amount_sum;
596 X2_invoice_amount := 0;
597 ELSIF ( l_calling_process = 'Invoice' ) THEN
598 X2_revenue_amount := 0;
599 X2_invoice_amount := l_projfunc_amount_sum;
600 END IF;
601
602 IF g1_debug_mode = 'Y' THEN
603 PA_MCB_INVOICE_PKG.log_message('Exiting from pa_bill_pct.PotEventAmount ');
604 END IF;
605 /* Added the below for NOCOPY mandate */
606 EXCEPTION WHEN OTHERS THEN
607 X2_revenue_amount := 0;
608 X2_invoice_amount := 0;
609 END PotEventAmount;
610
611
612 Procedure RevenueAmount( X2_project_id NUMBER,
613 X2_task_Id NUMBER DEFAULT NULL,
614 X2_revenue_amount OUT NOCOPY REAL) IS
615
616 pending_pctrev REAL;
617 accrued_pctrev REAL;
618
619 /* Varible for MCB2 */
620 l_trans_rev_amt pa_events.bill_trans_rev_amount%TYPE;
621 l_projfunc_rev_amount_sum pa_events.projfunc_revenue_amount%TYPE;
622 l_converted_rev_amount pa_events.projfunc_revenue_amount%TYPE;
623 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
624 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
625 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
626 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
627 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
628 l_event_date pa_events.completion_date%TYPE;
629 l_conv_date pa_events.completion_date%TYPE;
630 l_denominator Number;
631 l_numerator Number;
632 l_staus Varchar2(30);
633 l_project_id pa_projects_all.project_id%TYPE;
634 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
635 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
636 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
637 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
638 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
639 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
640 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
641 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
642 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
643 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
644 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
645 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
646 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
647 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
648 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
649 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
650 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
651 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
652 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
653 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
654 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
655 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
656 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
657 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
658 l_return_status VARCHAR2(30);
659 l_msg_count NUMBER;
660 l_msg_data VARCHAR2(30);
661
662 CURSOR pctfunc_revenue(X2_project_id Number,X2_task_id Number) IS
663 SELECT NVL(e.bill_trans_rev_amount,0) trans_rev_amount,e.bill_trans_currency_code,
664 e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
665 FROM pa_events e,
666 pa_billing_assignments bea,
667 pa_billing_extensions be
668 where be.billing_extension_id = bea.billing_extension_id
669 and e.project_id = X2_project_id
670 and nvl(e.task_id,0) =
671 decode(X2_task_id,
672 NULL, nvl(e.task_id,0), X2_task_id )
673 and bea.billing_assignment_id = e.billing_assignment_id
674 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
675 and e.revenue_distributed_flag||'' = 'N';
676
677 BEGIN
678
679 IF g1_debug_mode = 'Y' THEN
680 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Entering pa_bill_pct.RevenuAmount ');
681 END IF;
682 -- Percent Complete Revenue that has been accrued.
683 /* change this column from amount to projfunc_revenue_amount for MCB2 */
684 SELECT sum(nvl(dri.projfunc_revenue_amount,0))
685 INTO accrued_pctrev
686 FROM pa_draft_revenue_items dri
687 WHERE dri.project_id = X2_project_id
688 AND nvl(dri.task_id,0) = decode(X2_task_id, NULL, nvl(dri.task_id,0), X2_task_id )
689 AND (EXISTS ( select '1'
690 from pa_cust_event_rev_dist_lines erdl,
691 pa_events e,
692 pa_billing_assignments bea,
693 pa_billing_extensions be
694 where be.billing_extension_id = bea.billing_extension_id
695 and bea.billing_assignment_id = e.billing_assignment_id
696 and e.project_id = erdl.project_id
697 and e.event_num = erdl.event_num
698 and nvl(e.task_id,0) = nvl(erdl.task_id, 0)
699 and erdl.project_id = dri.project_id
700 and erdl.draft_revenue_num = dri.draft_revenue_num
701 and erdl.draft_revenue_item_line_num = dri.line_num
702 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
703 OR dri.revenue_source like 'Expenditure%');
704
705 IF g1_debug_mode = 'Y' THEN
706 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The accrued_pctrev inside pa_bill_pct.RevenuAmount: '||to_char(accrued_pctrev));
707 END IF;
708 -- Percent Complete revenue that has not been created as events
709 -- but not accrued yet.
710 -- This could be due to unauthorized task or an erroring request.
711 /* The following code is commented because this amount is in RPC i.e. Revenue programm is going to populate this amount
712 */
713 /*
714 SELECT sum(nvl(e.revenue_amount,0))
715 INTO pending_pctrev
716 FROM pa_events e,
717 pa_billing_assignments bea,
718 pa_billing_extensions be
719 where be.billing_extension_id = bea.billing_extension_id
720 and e.project_id = X2_project_id
721 and nvl(e.task_id,0) =
722 decode(X2_task_id,
723 NULL, nvl(e.task_id,0), X2_task_id )
724 and bea.billing_assignment_id = e.billing_assignment_id
725 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
726 and e.revenue_distributed_flag||'' = 'N'; */
727
728 /* Following code has been added for MCB2 */
729 l_project_id := X2_project_id;
730 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
731 p_project_id => l_project_id,
732 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
733 x_baseline_funding_flag => l_baseline_funding_flag,
734 x_revproc_currency_code => l_revproc_currency_code,
735 x_invproc_currency_type => l_invproc_currency_type,
736 x_invproc_currency_code => l_invproc_currency_code,
737 x_project_currency_code => l_project_currency_code,
738 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
739 x_project_bil_rate_type => l_project_bil_rate_type,
740 x_project_bil_rate_date => l_project_bil_rate_date,
741 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
742 x_projfunc_currency_code => l_projfunc_currency_code,
743 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
744 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
745 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
746 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
747 x_funding_rate_date_code => l_funding_rate_date_code,
748 x_funding_rate_type => l_funding_rate_type,
749 x_funding_rate_date => l_funding_rate_date,
750 x_funding_exchange_rate => l_funding_exchange_rate,
751 x_return_status => l_return_status,
752 x_msg_count => l_msg_count,
753 x_msg_data => l_msg_data);
754
755 OPEN pctfunc_revenue( X2_project_id,X2_task_id);
756 Loop
757 FETCH pctfunc_revenue INTO l_trans_rev_amt,l_txn_currency_code,l_projfunc_currency_code,
758 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
759 EXIT WHEN pctfunc_revenue%NOTFOUND;
760 IF ( l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
761 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
762 END IF;
763
764 /* Calling convert amount proc to convert this amount in PFC */
765 PA_MULTI_CURRENCY.convert_amount(
766 P_FROM_CURRENCY => l_txn_currency_code,
767 P_TO_CURRENCY => l_projfunc_currency_code,
768 P_CONVERSION_DATE => l_projfunc_rate_date,
769 P_CONVERSION_TYPE => l_projfunc_rate_type,
770 P_AMOUNT => l_trans_rev_amt,
771 P_USER_VALIDATE_FLAG => 'Y',
772 P_HANDLE_EXCEPTION_FLAG => 'Y',
773 P_CONVERTED_AMOUNT => l_converted_rev_amount,
774 P_DENOMINATOR => l_denominator,
775 P_NUMERATOR => l_numerator,
776 P_RATE => l_projfunc_exchange_rate,
777 X_STATUS => l_staus);
778 IF ( l_staus IS NOT NULL ) THEN
779 l_converted_rev_amount := 0;
780 END IF;
781
782 l_projfunc_rev_amount_sum := NVL(l_projfunc_rev_amount_sum,0) + NVL(l_converted_rev_amount,0);
783 End Loop;
784 Close pctfunc_revenue;
785 pending_pctrev := l_projfunc_rev_amount_sum;
786
787 IF g1_debug_mode = 'Y' THEN
788 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The pending_pctrev : inside pa_bill_pct.RevenuAmount '||to_char(pending_pctrev ));
789 END IF;
790 X2_revenue_amount := nvl(accrued_pctrev,0) + nvl(pending_pctrev,0);
791
792 IF g1_debug_mode = 'Y' THEN
793 PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Exiting pa_bill_pct.RevenuAmount ');
794 END IF;
795
796 /* Added the below for NOCOPY mandate */
797 EXCEPTION WHEN OTHERS THEN
798 X2_revenue_amount := NULL;
799 END RevenueAmount;
800
801 Procedure InvoiceAmount( X2_project_id NUMBER,
802 X2_task_id NUMBER default NULL,
803 X2_invoice_amount OUT NOCOPY REAL) IS
804
805 pending_ccinv REAL;
806 task_billed_ccinv REAL;
807 task_billed_ev_ccinv REAL;
808 billed_ccinv REAL;
809
810 /* Varibles added for MCB2 */
811 l_trans_bill_amt pa_events.bill_trans_bill_amount%TYPE;
812 l_projfunc_bill_amount_sum pa_events.projfunc_bill_amount%TYPE;
813 l_converted_bill_amount pa_events.projfunc_bill_amount%TYPE;
814 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
815 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
816 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
817 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
818 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
819 l_event_date pa_events.completion_date%TYPE;
820 l_conv_date pa_events.completion_date%TYPE;
821 l_denominator Number;
822 l_numerator Number;
823 l_staus Varchar2(30);
824 l_project_id pa_projects_all.project_id%TYPE;
825 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
826 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
827 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
828 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
829 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
830 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
831 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
832 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
833 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
834 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
835 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
836 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
837 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
838 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
839 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
840 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
841 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
842 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
843 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
844 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
845 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
846 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
847 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
848 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
849 l_return_status VARCHAR2(30);
850 l_msg_count NUMBER;
851 l_msg_data VARCHAR2(30);
852
853 CURSOR pctfunc_invoice(X2_project_id Number,X2_task_id Number) IS
854 SELECT NVL(e.bill_trans_bill_amount,0) trans_bill_amount,e.bill_trans_currency_code,e.projfunc_currency_code,
855 e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
856 FROM pa_events e,
857 pa_billing_assignments bea,
858 pa_billing_extensions be
859 WHERE be.billing_extension_id = bea.billing_extension_id
860 AND bea.billing_assignment_id = e.billing_assignment_id
861 AND e.project_id = X2_project_id
862 AND be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
863 AND nvl(e.task_id,0) = decode(X2_task_id,
864 NULL, nvl(e.task_id,0), X2_task_id)
865 AND NOT EXISTS
866 (select 'billed'
867 from pa_draft_invoice_items pdii
868 where pdii.project_id = e.project_id
869 and pdii.event_num = e.event_num
870 and nvl(pdii.task_id,0) = nvl(e.task_id,0));
871
872 BEGIN
873 IF g1_debug_mode = 'Y' THEN
874 PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.InvoiceAmount ');
875 END IF;
876
877 -- Percent Complete Invoice Amount that has been created as an event,
878 -- but not billed yet.
879 /* The following code is commented because this amount is in IPC i.e. Invoice programm is going to populate this amount
880 */
881 /*
882 SELECT sum(nvl(e.bill_amount,0))
883 INTO pending_ccinv
884 from pa_events e,
885 pa_billing_assignments bea,
886 pa_billing_extensions be
887 where be.billing_extension_id = bea.billing_extension_id
888 and bea.billing_assignment_id = e.billing_assignment_id
889 and e.project_id = X2_project_id
890 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
891 and nvl(e.task_id,0) = decode(X2_task_id,
892 NULL, nvl(e.task_id,0), X2_task_id)
893 and NOT EXISTS
894 (select 'billed'
895 from pa_draft_invoice_items pdii
896 where pdii.project_id = e.project_id
897 and pdii.event_num = e.event_num
898 and nvl(pdii.task_id,0) = nvl(e.task_id,0)); */
899
900 /* Following code has been added for MCB2 */
901 l_project_id := X2_project_id;
902 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
903 p_project_id => l_project_id,
904 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
905 x_baseline_funding_flag => l_baseline_funding_flag,
906 x_revproc_currency_code => l_revproc_currency_code,
907 x_invproc_currency_type => l_invproc_currency_type,
908 x_invproc_currency_code => l_invproc_currency_code,
909 x_project_currency_code => l_project_currency_code,
910 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
911 x_project_bil_rate_type => l_project_bil_rate_type,
912 x_project_bil_rate_date => l_project_bil_rate_date,
913 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
914 x_projfunc_currency_code => l_projfunc_currency_code,
915 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
916 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
917 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
918 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
919 x_funding_rate_date_code => l_funding_rate_date_code,
920 x_funding_rate_type => l_funding_rate_type,
921 x_funding_rate_date => l_funding_rate_date,
922 x_funding_exchange_rate => l_funding_exchange_rate,
923 x_return_status => l_return_status,
924 x_msg_count => l_msg_count,
925 x_msg_data => l_msg_data);
926
927 OPEN pctfunc_invoice( X2_project_id,X2_task_id);
928 Loop
929 FETCH pctfunc_invoice INTO l_trans_bill_amt,l_txn_currency_code,l_projfunc_currency_code,
930 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
931 EXIT WHEN pctfunc_invoice%NOTFOUND;
932 IF ( l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
933 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
934 END IF;
935 /* Calling convert amount proc to convert this amount in PFC */
936 PA_MULTI_CURRENCY.convert_amount(
937 P_FROM_CURRENCY => l_txn_currency_code,
938 P_TO_CURRENCY => l_projfunc_currency_code,
939 P_CONVERSION_DATE => l_projfunc_rate_date,
940 P_CONVERSION_TYPE => l_projfunc_rate_type,
941 P_AMOUNT => l_trans_bill_amt,
942 P_USER_VALIDATE_FLAG => 'Y',
943 P_HANDLE_EXCEPTION_FLAG => 'Y',
944 P_CONVERTED_AMOUNT => l_converted_bill_amount,
945 P_DENOMINATOR => l_denominator,
946 P_NUMERATOR => l_numerator,
947 P_RATE => l_projfunc_exchange_rate,
948 X_STATUS => l_staus);
949 IF ( l_staus IS NOT NULL ) THEN
950 l_converted_bill_amount := 0;
951 END IF;
952
953 l_projfunc_bill_amount_sum := NVL(l_projfunc_bill_amount_sum,0) + NVL(l_converted_bill_amount,0);
954 End Loop;
955 Close pctfunc_invoice;
956 pending_ccinv := l_projfunc_bill_amount_sum;
957
958
959 IF g1_debug_mode = 'Y' THEN
960 PA_MCB_INVOICE_PKG.log_message('The pending_ccinv inside pa_bill_pct.InvoiceAmount: '||to_char(pending_ccinv));
961 END IF;
962 IF (X2_task_id IS NULL) THEN
963
964 -- Percent Complete Invoice Amount that has been billed, or originates from
965 -- expenditure items (historical cost-cost invoice amount)
966
967 /* Change this column from amount to projfunc_bill_amount for MCB2 */
968 SELECT sum(nvl(dii.projfunc_bill_amount,0))
969 INTO billed_ccinv
970 FROM pa_draft_invoice_items dii
971 WHERE dii.project_id = X2_project_id
972 AND (EXISTS (select '1'
973 from pa_events e,
974 pa_billing_assignments bea,
975 pa_billing_extensions be
976 where be.billing_extension_id = bea.billing_extension_id
977 and bea.billing_assignment_id = e.billing_assignment_id
978 and dii.project_id = e.project_id
979 and dii.event_num = e.event_num
980 and nvl(dii.event_task_id,0) = nvl(e.task_id,0)
981 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
982 OR EXISTS ( select '1'
983 from pa_cust_rev_dist_lines erdl
984 where erdl.project_id = dii.project_id
985 and erdl.draft_invoice_num = dii.draft_invoice_num
986 and erdl.draft_invoice_item_line_num = dii.line_num));
987
988 IF g1_debug_mode = 'Y' THEN
989 PA_MCB_INVOICE_PKG.log_message('The billed_ccinv for task id is null inside pa_bill_pct.InvoiceAmount: '||to_char(billed_ccinv));
990 END IF;
991 X2_invoice_amount := nvl(pending_ccinv,0) + nvl(billed_ccinv,0);
992
993 ELSE
994
995 /* Change this column from amount to projfunc_bill_amount for MCB2 */
996 SELECT sum(nvl(rdl.projfunc_bill_amount,0))
997 INTO task_billed_ccinv
998 FROM pa_cust_rev_dist_lines rdl,
999 pa_expenditure_items_all ei,
1000 pa_tasks t
1001 WHERE ei.task_id = t.task_id
1002 AND ei.Project_ID = t.Project_ID -- Perf Bug 2695332
1003 AND ei.expenditure_item_id = rdl.expenditure_item_id
1004 AND rdl.project_id = X2_project_id
1005 AND t.top_task_id = X2_task_id
1006 AND rdl.draft_invoice_num IS NOT NULL;
1007
1008 IF g1_debug_mode = 'Y' THEN
1009 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));
1010 END IF;
1011 /* Change this column from amount to projfunc_bill_amount for MCB2 */
1012 SELECT sum(nvl(pdii.projfunc_bill_amount,0))
1013 INTO task_billed_ev_ccinv
1014 FROM pa_draft_invoice_items pdii
1015 WHERE pdii.event_task_id = X2_task_id
1016 AND pdii.Project_ID = X2_Project_ID -- Perf Bug 2695332
1017 AND EXISTS (select '1'
1018 from pa_events e,
1019 pa_billing_assignments bea,
1020 pa_billing_extensions be
1021 where be.billing_extension_id = bea.billing_extension_id
1022 and bea.billing_assignment_id = e.billing_assignment_id
1023 and pdii.project_id = e.project_id
1024 and pdii.event_num = e.event_num
1025 and pdii.event_task_id = e.task_id
1026 and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');
1027
1028 IF g1_debug_mode = 'Y' THEN
1029 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));
1030 END IF;
1031 X2_invoice_amount := nvl(task_billed_ccinv,0) + nvl(task_billed_ev_ccinv,0) + nvl(pending_ccinv,0);
1032
1033 END IF;
1034
1035 IF g1_debug_mode = 'Y' THEN
1036 PA_MCB_INVOICE_PKG.log_message('Exiting pa_bill_pct.InvoiceAmount: ');
1037 END IF;
1038 /* Added the below for NOCOPY mandate */
1039 EXCEPTION WHEN OTHERS THEN
1040 X2_invoice_amount := NULL;
1041 END InvoiceAmount;
1042
1043
1044 Function GetPercentComplete( X2_project_id NUMBER,
1045 X2_task_id NUMBER DEFAULT NULL,
1046 X2_accrue_through_date DATE DEFAULT NULL)
1047 RETURN REAL IS
1048
1049 percent_comp REAL :=0;
1050
1051 -- Cursor to select the most recently entered completion percentage
1052 --
1053 /* Patchset K : Percent complete changes :
1054 Using the view PA_PERCENT_COMPLETES_FIN_V instead of pa_percent_completes */
1055
1056
1057 /* Changed cursor to pickup record based on sequence number (percent_complete_id)
1058 for bug no 1283352.
1059
1060 CURSOR pct IS
1061 SELECT NVL(completed_percentage,0)
1062 FROM PA_PERCENT_COMPLETES_FIN_V ppc
1063 WHERE
1064 ppc.project_id = X2_project_id AND
1065 nvl(ppc.task_id,0) = nvl(X2_task_id,0 )
1066 AND ppc.date_computed
1067 =
1068 (SELECT max(date_computed)
1069 from PA_PERCENT_COMPLETES_FIN_V
1070 where date_computed <= nvl(X2_accrue_through_date, sysdate)
1071 and project_id = X2_project_id
1072 and nvl(task_id,0) = nvl(X2_task_id,0)
1073 )
1074 ORDER BY creation_date desc
1075 ;
1076 bug fix 1283352 "commentation" ends. */
1077
1078 CURSOR pct IS
1079 SELECT NVL(completed_percentage,0)
1080 FROM PA_PERCENT_COMPLETES_FIN_V ppc
1081 WHERE
1082 ppc.project_id = X2_project_id
1083 And nvl(ppc.task_id,0) = nvl(X2_task_id,0 )
1084 And ppc.date_computed <= nvl(X2_accrue_through_date, sysdate)
1085 And ppc.percent_complete_id = ( Select max(ppcx.percent_complete_id)
1086 from PA_PERCENT_COMPLETES_FIN_V ppcx
1087 where project_id = X2_project_id
1088 and nvl(task_id,0) = nvl(X2_task_id,0)
1089 and ppcx.date_computed = (
1090 Select max(ppcy.date_computed)
1091 from PA_PERCENT_COMPLETES_FIN_V ppcy
1092 Where ppcy.date_computed <= nvl(X2_accrue_through_date, sysdate)
1093 and ppcy.project_id = X2_project_id
1094 and nvl(ppcy.task_id,0) = nvl(X2_task_id,0)));
1095 BEGIN
1096
1097 /** get the most recent percent complete before the accru thru date **/
1098
1099 -- PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.GetPercentComplete: ');
1100 OPEN pct;
1101
1102 FETCH pct INTO percent_comp;
1103
1104 -- PA_MCB_INVOICE_PKG.log_message('Inside the loop of pa_bill_pct.GetPercentComplete: ');
1105 CLOSE pct;
1106
1107 --PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.GetPercentComplete: ');
1108 RETURN percent_comp;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 CLOSE pct;
1113 RETURN 0;
1114
1115 END GetPercentComplete;
1116
1117 ---------------------
1118 -- GLOBALS
1119 --
1120
1121 -- get_rev_budget_amount modified to use User defined budget types
1122 -- and use api pa_budget_utils.get_project_task_totals
1123 --
1124 PROCEDURE get_rev_budget_amount( X2_project_id NUMBER,
1125 X2_task_id NUMBER DEFAULT NULL,
1126 X2_revenue_amount OUT NOCOPY REAL,
1127 P_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
1128 P_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
1129 X_rev_budget_type_code OUT NOCOPY VARCHAR2,
1130 X_rev_plan_type_id OUT NOCOPY NUMBER, /* Added for Fin plan impact */
1131 X_error_message OUT NOCOPY VARCHAR2,
1132 X_status OUT NOCOPY NUMBER
1133 ) IS
1134
1135 -- local variables for budget codes
1136 status VARCHAR2(240); -- For error messages from subprogs
1137 l_rev_budget_type_code VARCHAR2(30) ;
1138 l_rev_budget_status_code VARCHAR2(1) ;
1139 dummy CHAR(1);
1140 err_msg VARCHAR2(240);
1141 err_status NUMBER;
1142 l_status NUMBER;
1143 l_rev_budget_version_id NUMBER;
1144 l_raw_cost_total REAL := 0;
1145 l_revenue_total REAL := 0;
1146 l_quantity_total NUMBER;
1147 l_burdened_cost_total NUMBER;
1148 l_err_code NUMBER;
1149 l_err_stage VARCHAR2(30);
1150 l_err_stack VARCHAR2(630);
1151 invalid_rev_budget_code EXCEPTION;
1152 rev_budget_not_baselined EXCEPTION;
1153
1154 /* Added for Fin plan impact */
1155 l_rev_plan_version_id NUMBER;
1156 l_rev_plan_type_id NUMBER ;
1157 /* till here */
1158
1159 BEGIN
1160 IF g1_debug_mode = 'Y' THEN
1161 PA_MCB_INVOICE_PKG.log_message(' Entering pa_bill_pct.get_rev_budget_amount: ');
1162 END IF;
1163 X_status := 0;
1164 X_error_message := NULL;
1165 BEGIN
1166 -- If user doesnt provide the budget get the default value from biling extensions
1167 --
1168
1169 IF ( P_rev_budget_type_code IS NULL) OR (P_rev_plan_type_id IS NULL) THEN
1170
1171 SELECT DECODE(P_rev_budget_type_code,NULL,default_rev_budget_type_code,P_rev_budget_type_code),
1172 DECODE(P_rev_plan_type_id,NULL,default_rev_plan_type_id,
1173 P_rev_plan_type_id) /* Added for fin plan type id */
1174 INTO l_rev_budget_type_code,
1175 l_rev_plan_type_id
1176 FROM pa_billing_extensions
1177 WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
1178
1179 IF g1_debug_mode = 'Y' THEN
1180 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);
1181 END IF;
1182 END IF;
1183
1184
1185 -- The plan code should be a valid code and of the right version type
1186 -- If invalid then process will check for budget code
1187 /* Added this select for Fin plan Impact */
1188 BEGIN
1189 SELECT 'x'
1190 INTO dummy
1191 FROM dual
1192 WHERE EXISTS( SELECT *
1193 FROM pa_fin_plan_types_b f
1194 WHERE f.fin_plan_type_id=l_rev_plan_type_id );
1195
1196 EXCEPTION
1197 WHEN NO_DATA_FOUND THEN
1198
1199 -- The budget code should be a valid code and of the right amount code
1200 -- If invalid then raise appropriate exception
1201
1202 BEGIN
1203
1204 SELECT 'x'
1205 INTO dummy
1206 FROM pa_budget_types
1207 WHERE budget_type_code = l_rev_budget_type_code
1208 AND budget_amount_code = 'R';
1209
1210 EXCEPTION
1211 WHEN NO_DATA_FOUND THEN
1212 raise invalid_rev_budget_code;
1213 END ;
1214
1215 END; /* End of newly added code for Fin plan Impact */
1216
1217
1218 /* Added this select for Fin plan Impact */
1219 BEGIN
1220
1221 SELECT v.budget_version_id
1222 INTO l_rev_plan_version_id
1223 FROM pa_budget_versions v
1224 WHERE v.project_id = X2_project_id
1225 AND v.current_flag = 'Y'
1226 AND v.budget_status_code = 'B'
1227 AND v.fin_plan_type_id = l_rev_plan_type_id
1228 AND v.version_type IN ('REVENUE','ALL');
1229
1230 EXCEPTION
1231 WHEN NO_DATA_FOUND THEN
1232 -- get the budget version id for cost and revenue budget
1233 -- Changed to use api pa_budget_utils.get_project_task_totals
1234
1235 BEGIN
1236
1237 SELECT budget_version_id
1238 INTO l_rev_budget_version_id
1239 FROM pa_budget_versions pbv
1240 WHERE project_id = X2_project_id
1241 AND budget_type_code = l_rev_budget_type_code
1242 AND budget_status_code = 'B'
1243 AND current_flag = 'Y';
1244
1245 IF g1_debug_mode = 'Y' THEN
1246 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);
1247 END IF;
1248 EXCEPTION
1249 WHEN NO_DATA_FOUND THEN
1250 raise rev_budget_not_baselined;
1251 END;
1252
1253 END; /* End of newly added code for Fin plan Impact */
1254
1255 l_rev_budget_version_id := NVL(l_rev_plan_version_id,l_rev_budget_version_id);
1256
1257
1258 -- Call api to get revenue budget amount
1259 --
1260 pa_budget_utils.get_project_task_totals
1261 (l_rev_budget_version_id ,
1262 x2_task_id ,
1263 l_quantity_total,
1264 l_raw_cost_total ,
1265 l_burdened_cost_total ,
1266 l_revenue_total ,
1267 l_err_code ,
1268 l_err_stage,
1269 l_err_stack );
1270
1271 X2_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
1272
1273 IF g1_debug_mode = 'Y' THEN
1274 PA_MCB_INVOICE_PKG.log_message(' getting l_revenue_total inside pa_bill_pct.get_rev_budget_amount: '||to_char(l_revenue_total));
1275 END IF;
1276 X_rev_budget_type_code := l_rev_budget_type_code;
1277 X_rev_plan_type_id := l_rev_plan_type_id; /* Added for Fin plan impact */
1278
1279 -- If any exception then raise it to the calling pl/sql block
1280 --
1281 IF g1_debug_mode = 'Y' THEN
1282 PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.get_rev_budget_amount: ');
1283 END IF;
1284 EXCEPTION
1285 WHEN invalid_rev_budget_code THEN
1286 status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
1287 l_status := 2;
1288 RAISE_APPLICATION_ERROR(-20101,status);
1289 WHEN rev_budget_not_baselined THEN
1290 status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
1291 l_status := 3;
1292 RAISE_APPLICATION_ERROR(-20101,status);
1293 WHEN OTHERS THEN
1294 status := substr(SQLERRM,1,240);
1295 l_status := sqlcode;
1296 RAISE;
1297 END;
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 -- DBMS_OUTPUT.PUT_LINE(status);
1302 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1303 X2_revenue_amount := NULL;
1304 X_rev_budget_type_code := NULL;
1305 ----
1306 X_rev_plan_type_id := NULL;
1307
1308 X_error_message := status;
1309 X_status := l_status;
1310
1311 pa_billing_pub.insert_message
1312 (X_inserting_procedure_name =>'pa_billing_pct.get_rev_budget_amount',
1313 X_attribute2 => l_rev_budget_type_code,
1314 X_message => status,
1315 X_error_message=>err_msg,
1316 X_status=>err_status);
1317
1318 IF (l_status < 0 OR NVL(err_status,0) <0) THEN
1319 RAISE;
1320 END IF;
1321
1322 END get_rev_budget_amount;
1323 END pa_bill_pct;