[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_AMOUNT
Source
1 PACKAGE BODY pa_billing_amount AS
2 /* $Header: PAXIAMTB.pls 120.6.12010000.3 2008/08/11 04:18:10 arbandyo ship $ */
3
4 --------------------------------------
5 -- FUNCTION/PROCEDURE IMPLEMENTATIONS
6 --
7
8 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
9
10 Procedure CostAmount( X2_project_id NUMBER,
11 X2_task_id NUMBER DEFAULT NULL,
12 X2_accrue_through_date DATE DEFAULT NULL,
13 X2_cost_amount OUT NOCOPY REAL) IS --File.Sql.39 bug 4440895
14
15 new_cost REAL := 0;
16 l_calling_process varchar2(15);
17
18 BEGIN
19 IF g1_debug_mode = 'Y' THEN
20 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_amount.CostAmount :');
21 END IF;
22
23 l_calling_process := pa_billing.GetCallProcess; /*Added for bug 7299493*/
24
25 /* commented for bug 4251205 */
26 /* SELECT nvl(sum(nvl(a.burdened_cost,0)),0)
27 INTO new_cost
28 FROM pa_proj_ccrev_cost_v a
29 WHERE a.project_id = X2_project_id
30 AND a.task_id= nvl(X2_task_id,a.task_id)
31 AND TRUNC(nvl(X2_accrue_through_date,sysdate)) >= trunc(a.pa_start_date);/* BUG#3118592 */
32
33 /* Split select into two parts for bug 4251205 */
34
35 /* Commented for bug 4860032 Forward port of bug 4646775 */
36
37 /*IF X2_task_id is NULL THEN
38 SELECT nvl(sum(nvl(a.burdened_cost,0)),0)
39 INTO new_cost
40 FROM pa_proj_ccrev_cost_v a
41 WHERE a.project_id = X2_project_id
42 AND TRUNC(nvl(X2_accrue_through_date,sysdate)) >= trunc(a.pa_start_date);
43
44 ELSE
45
46 SELECT nvl(sum(nvl(a.burdened_cost,0)),0)
47 INTO new_cost
48 FROM pa_proj_ccrev_cost_v a
49 WHERE a.project_id = X2_project_id
50 AND a.task_id= X2_task_id
51 AND TRUNC(nvl(X2_accrue_through_date,sysdate)) >= trunc(a.pa_start_date);
52
53 END IF;
54 End of Commenting for Bug 4860032 */
55
56 --Inserted for Bug 4860032 ( Forward port 4646775 )
57
58 IF X2_task_id is NULL THEN
59 select sum(BURDENED_COST)
60 INTO new_cost
61 from (
62 SELECT sum(nvl(ta.tot_burdened_cost, nvl(ta.tot_raw_cost,0)) +
63 nvl(ta.i_tot_burdened_cost, nvl(i_tot_raw_cost,0))) BURDENED_COST
64 FROM
65 pa_txn_accum ta
66 WHERE ta.project_id = X2_project_id
67 AND EXISTS
68 (
69 select 1
70 from pa_periods pp
71 where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
72 and pp.period_name = ta.pa_period
73 )
74 AND EXISTS (select 1 from pa_tasks t
75 where t.task_id = ta.task_id
76 and exists (select 1 from pa_tasks t1
77 where t1.task_id = t.top_task_id
78 and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
79 'Invoice',t1.ready_to_bill_flag,0) = 'Y')
80 ) /* Exists clause added for bug 7299493 */
81
82 UNION ALL
83 SELECT sum(nvl((cdl.burdened_cost + nvl(project_burdened_change,0)), nvl(cdl.amount,0))) BURDENED_COST
84 FROM pa_cost_distribution_lines_all cdl
85 WHERE cdl.resource_accumulated_flag = 'N'
86 AND cdl.line_type = 'R'
87 AND cdl.project_id = X2_project_id
88 AND EXISTS
89 (
90 select 1
91 from pa_periods pp
92 where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
93 and cdl.pa_date between pp.start_date and pp.end_date
94 )
95 AND EXISTS (select 1 from pa_tasks t
96 where t.task_id = cdl.task_id
97 and exists (select 1 from pa_tasks t1
98 where t1.task_id = t.top_task_id
99 and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
100 'Invoice',t1.ready_to_bill_flag,0) = 'Y')
101 ) /* Exists clause added for bug 7299493 */
102 );
103
104 ELSE
105
106 select sum(BURDENED_COST)
107 INTO new_cost
108 from (
109
110 SELECT sum(nvl(ta.tot_burdened_cost, nvl(ta.tot_raw_cost,0)) +
111 nvl(ta.i_tot_burdened_cost, nvl(i_tot_raw_cost,0))) BURDENED_COST
112 FROM
113 pa_txn_accum ta,
114 pa_tasks t
115 WHERE ta.task_id = t.task_id
116 AND t.top_task_id = X2_task_id
117 AND t.project_id = X2_project_id
118 AND ta.project_id = X2_project_id
119 AND ta.project_id = t.project_id
120 and exists (select 1 from pa_tasks t1
121 where t1.task_id = t.top_task_id
122 and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
123 'Invoice',t1.ready_to_bill_flag,0) = 'Y') /* Exists clause added for bug 7299493 */
124
125 AND EXISTS
126 (
127 select 1
128 from pa_periods pp
129 where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
130 and pp.period_name = ta.pa_period
131 )
132 UNION ALL
133 SELECT sum(nvl((cdl.burdened_cost + nvl(project_burdened_change,0)), nvl(cdl.amount,0))) BURDENED_COST
134 FROM pa_cost_distribution_lines_all cdl /* Added _all for bug 5953670*/
135 /*pa_tasks commented for bug 6521198*/
136 WHERE EXISTS ( select 1 from pa_tasks t
137 WHERE cdl.project_id = t.project_id
138 AND cdl.task_id = t.task_id
139 AND t.project_id = X2_project_id
140 AND t.top_task_id = X2_task_id
141 and exists (select 1 from pa_tasks t1
142 where t1.task_id = t.top_task_id
143 and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
144 'Invoice',t1.ready_to_bill_flag,0) = 'Y') /* Exists clause added for bug 7294641 */
145 )
146 AND cdl.resource_accumulated_flag = 'N'
147 AND cdl.line_type = 'R'
148 AND cdl.project_id = X2_project_id
149 AND EXISTS
150 (
151 select 1
152 from pa_periods pp
153 where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
154 AND cdl.pa_date between pp.start_date and pp.end_date
155 )
156 );
157
158 END IF;
159
160 --End of Inserting for bug 4860032
161
162
163 X2_cost_amount := nvl(new_cost,0);
164
165 IF g1_debug_mode = 'Y' THEN
166 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_amount.CostAmount :');
167 END IF;
168 END CostAmount;
169
170
171
172
173 Procedure RevenueAmount( X2_project_id NUMBER,
174 X2_task_Id NUMBER DEFAULT NULL,
175 X2_revenue_amount OUT NOCOPY REAL) IS --File.Sql.39 bug 4440895
176
177 pending_ccrev REAL;
178 accrued_ccrev REAL;
179 /* Varible for MCB2 */
180 l_trans_rev_amt pa_events.bill_trans_rev_amount%TYPE;
181 l_projfunc_rev_amount_sum pa_events.projfunc_revenue_amount%TYPE;
182 l_converted_rev_amount pa_events.projfunc_revenue_amount%TYPE;
183 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
184 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
185 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
186 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
187 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
188 l_event_date pa_events.completion_date%TYPE;
189 l_conv_date pa_events.completion_date%TYPE;
190 l_denominator Number;
191 l_numerator Number;
192 l_staus Varchar2(30);
193 l_project_id pa_projects_all.project_id%TYPE;
194 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
195 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
196 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
197 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
198 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
199 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
200 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
201 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
202 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
203 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
204 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
205 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
206 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
207 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
208 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
209 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
210 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
211 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
212 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
213 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
214 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
215 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
216 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
217 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
218 l_return_status VARCHAR2(30);
219 l_msg_count NUMBER;
220 l_msg_data VARCHAR2(30);
221
222 CURSOR func_revenue(X2_project_id Number,X2_task_id Number) IS
223 SELECT NVL(e.bill_trans_rev_amount,0) trans_rev_amount,e.bill_trans_currency_code,
224 e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
225 FROM pa_events e,
226 pa_billing_assignments bea,
227 pa_billing_extensions be
228 where be.billing_extension_id = bea.billing_extension_id
229 and e.project_id = X2_project_id
230 and nvl(e.task_id,0) = decode(X2_task_id,NULL,nvl(e.task_id,0), X2_task_id )
231 and bea.billing_assignment_id = e.billing_assignment_id
232 and be.procedure_name = 'pa_billing.ccrev'
233 and e.revenue_distributed_flag||'' = 'N';
234
235 l_projfunc_convers_fail EXCEPTION;
236 BEGIN
237 IF g1_debug_mode = 'Y' THEN
238 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_amount.RevenueAmount :');
239 END IF;
240
241 -- Cost-Cost Revenue that has been accrued.
242 SELECT sum(nvl(dri.projfunc_revenue_amount,0)) /* change this column from amount to projfunc_revenue_amount for MCB2 */
243 INTO accrued_ccrev
244 FROM pa_draft_revenue_items dri
245 WHERE dri.project_id = X2_project_id
246 AND nvl(dri.task_id,0) = decode(X2_task_id, NULL, nvl(dri.task_id,0), X2_task_id )
247 AND (EXISTS ( select '1'
248 from pa_cust_event_rev_dist_lines erdl,
249 pa_events e,
250 pa_billing_assignments bea,
251 pa_billing_extensions be
252 where be.billing_extension_id = bea.billing_extension_id
253 and bea.billing_assignment_id = e.billing_assignment_id
254 and e.project_id = erdl.project_id
255 and e.event_num = erdl.event_num
256 and nvl(e.task_id,0) = nvl(erdl.task_id, 0)
257 and erdl.project_id = dri.project_id
258 and erdl.draft_revenue_num = dri.draft_revenue_num
259 and erdl.draft_revenue_item_line_num = dri.line_num
260 and be.procedure_name = 'pa_billing.ccrev')
261 OR dri.revenue_source like 'Expenditure%');
262
263 IF g1_debug_mode = 'Y' THEN
264 PA_MCB_INVOICE_PKG.log_message('Number 01 RevenueAmount.accrued_ccrev :'||TO_CHAR(accrued_ccrev));
265 END IF;
266 -- Cost-Cost revenue that has not been created as events but not accrued yet.
267 -- This could be due to unauthorized task or an erroring request.
268 /* The following code is commented because this amount is in RPC i.e. Revenue programm is going to populate this amount
269 */
270 /* SELECT sum(nvl(e.revenue_amount,0))
271 INTO pending_ccrev
272 FROM pa_events e,
273 pa_billing_assignments bea,
274 pa_billing_extensions be
275 where be.billing_extension_id = bea.billing_extension_id
276 and e.project_id = X2_project_id
277 and nvl(e.task_id,0) =
278 decode(X2_task_id,
279 NULL, nvl(e.task_id,0), X2_task_id )
280 and bea.billing_assignment_id = e.billing_assignment_id
281 and be.procedure_name = 'pa_billing.ccrev'
282 and e.revenue_distributed_flag||'' = 'N'; */
283
284 /* Following code has been added for MCB2 */
285 l_project_id := X2_project_id;
286 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
287 p_project_id => l_project_id,
288 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
289 x_baseline_funding_flag => l_baseline_funding_flag,
290 x_revproc_currency_code => l_revproc_currency_code,
291 x_invproc_currency_type => l_invproc_currency_type,
292 x_invproc_currency_code => l_invproc_currency_code,
293 x_project_currency_code => l_project_currency_code,
294 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
295 x_project_bil_rate_type => l_project_bil_rate_type,
296 x_project_bil_rate_date => l_project_bil_rate_date,
297 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
298 x_projfunc_currency_code => l_projfunc_currency_code,
299 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
300 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
301 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
302 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
303 x_funding_rate_date_code => l_funding_rate_date_code,
304 x_funding_rate_type => l_funding_rate_type,
305 x_funding_rate_date => l_funding_rate_date,
306 x_funding_exchange_rate => l_funding_exchange_rate,
307 x_return_status => l_return_status,
308 x_msg_count => l_msg_count,
309 x_msg_data => l_msg_data);
310
311 OPEN func_revenue( X2_project_id,X2_task_id);
312 Loop
313 FETCH func_revenue INTO l_trans_rev_amt,l_txn_currency_code,l_projfunc_currency_code,
314 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
315 EXIT WHEN func_revenue%NOTFOUND;
316 IF ( l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
317 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
318 END IF;
319 /* Calling convert amount proc to convert this amount in PFC */
320 PA_MULTI_CURRENCY.convert_amount(
321 P_FROM_CURRENCY => l_txn_currency_code,
322 P_TO_CURRENCY => l_projfunc_currency_code,
323 P_CONVERSION_DATE => l_projfunc_rate_date,
324 P_CONVERSION_TYPE => l_projfunc_rate_type,
325 P_AMOUNT => l_trans_rev_amt,
326 P_USER_VALIDATE_FLAG => 'Y',
327 P_HANDLE_EXCEPTION_FLAG => 'Y',
328 P_CONVERTED_AMOUNT => l_converted_rev_amount,
329 P_DENOMINATOR => l_denominator,
330 P_NUMERATOR => l_numerator,
331 P_RATE => l_projfunc_exchange_rate,
332 X_STATUS => l_staus);
333 IF ( l_staus IS NOT NULL ) THEN
334 l_converted_rev_amount := 0;
335 END IF;
336 l_projfunc_rev_amount_sum := NVL(l_projfunc_rev_amount_sum,0) + NVL(l_converted_rev_amount,0);
337 IF g1_debug_mode = 'Y' THEN
338 PA_MCB_INVOICE_PKG.log_message('Number 02 RevenueAmount.pending_ccrev :'||to_char(l_projfunc_rev_amount_sum));
339 END IF;
340 End Loop;
341 Close func_revenue;
342 pending_ccrev := l_projfunc_rev_amount_sum;
343 X2_revenue_amount := nvl(accrued_ccrev,0) + nvl(pending_ccrev,0);
344 IF g1_debug_mode = 'Y' THEN
345 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_amount.RevenueAmount :');
346 END IF;
347 END RevenueAmount;
348
349
350 Procedure PotEventAmount( X2_project_id NUMBER,
351 X2_task_id NUMBER DEFAULT NULL,
352 X2_accrue_through_date DATE DEFAULT NULL,
353 X2_revenue_amount OUT NOCOPY REAL, --File.Sql.39 bug 4440895
354 X2_invoice_amount OUT NOCOPY REAL ) --File.Sql.39 bug 4440895
355 IS
356 /* Varibles added for MCB2 */
357 l_trans_rev_amt pa_events.bill_trans_rev_amount%TYPE;
358 l_trans_bill_amt pa_events.bill_trans_bill_amount%TYPE;
359 l_passd_amt pa_events.bill_trans_bill_amount%TYPE := 0;
360 l_projfunc_amount_sum pa_events.projfunc_revenue_amount%TYPE;
361 l_converted_amount pa_events.projfunc_revenue_amount%TYPE;
362 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
363 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
364 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
365 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
366 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
367 l_event_date pa_events.completion_date%TYPE;
368 l_conv_date pa_events.completion_date%TYPE;
369 l_denominator Number;
370 l_numerator Number;
371 l_staus Varchar2(30);
372 l_calling_process Varchar2(50);
373 l_project_id pa_projects_all.project_id%TYPE;
374 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
375 l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
376 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
377 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
378 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
379 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
380 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
381 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
382 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
383 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
384 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
385 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
386 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
387 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
388 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
389 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
390 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
391 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
392 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
393 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
394 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
395 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
396 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
397 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
398 l_return_status VARCHAR2(30);
399 l_msg_count NUMBER;
400 l_msg_data VARCHAR2(30);
401
402 l_projfunc_convers_fail EXCEPTION;
403
404 -- added the RLZD_LOSSES event type classification
405
406 CURSOR func_rev_inv_amt(X2_project_id Number,X2_task_id Number,X2_accrue_through_date Date) IS
407 SELECT (DECODE(et.event_type_classification,
408 'WRITE OFF',-1 * NVL(e.bill_trans_rev_amount,0),
409 'RLZED_LOSSES',-1 * NVL(e.bill_trans_rev_amount,0),
410 NVL(e.bill_trans_rev_amount,0))) trans_rev_amount,
411 (DECODE(et.event_type_classification,'INVOICE REDUCTION', -1 * NVL(e.bill_trans_bill_amount,0),
412 NVL(e.bill_trans_bill_amount,0))) trans_bill_amount,
413 e.bill_trans_currency_code,e.projfunc_currency_code,e.projfunc_rate_type,
414 e.projfunc_rate_date,e.projfunc_exchange_rate
415 FROM pa_events e,
416 pa_event_types et
417 WHERE e.event_type = et.event_type
418 AND e.project_id = X2_project_id
419 AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
420 AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
421 AND NOT EXISTS ( select 'cost-cost event'
422 from pa_billing_assignments bea,
423 pa_billing_extensions be
424 where be.billing_extension_id = bea.billing_extension_id
425 and bea.billing_assignment_id = e.billing_assignment_id
426 and be.procedure_name = 'pa_billing.ccrev');
427
428 BEGIN
429 IF g1_debug_mode = 'Y' THEN
430 PA_MCB_INVOICE_PKG.log_message('PotEventAmount: ' || 'Entering pa_billing_amount.PotEvent :');
431 END IF;
432 /* The following sql has been commented for MCB2 */
433 /* SELECT sum(decode(et.event_type_classification,
434 'WRITE OFF', -1 * nvl(revenue_amount,0),
435 nvl(revenue_amount,0))),
436 sum(decode(et.event_type_classification,
437 'INVOICE REDUCTION', -1 * nvl(bill_amount,0),
438 nvl(bill_amount,0)))
439 INTO X2_revenue_amount,
440 X2_invoice_amount
441 FROM pa_events e,
442 pa_event_types et
443 WHERE e.event_type = et.event_type
444 AND e.project_id = X2_project_id
445 AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
446 AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
447 AND NOT EXISTS ( select 'cost-cost event'
448 from pa_billing_assignments bea,
449 pa_billing_extensions be
450 where be.billing_extension_id = bea.billing_extension_id
451 and bea.billing_assignment_id = e.billing_assignment_id
452 and be.procedure_name = 'pa_billing.ccrev'); */
453
454 /* Following code has been added for MCB2 */
455 l_project_id := X2_project_id;
456 l_calling_process := pa_billing.GetCallProcess;
457 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
458 p_project_id => l_project_id,
459 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
460 x_baseline_funding_flag => l_baseline_funding_flag,
461 x_revproc_currency_code => l_revproc_currency_code,
462 x_invproc_currency_type => l_invproc_currency_type,
463 x_invproc_currency_code => l_invproc_currency_code,
464 x_project_currency_code => l_project_currency_code,
465 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
466 x_project_bil_rate_type => l_project_bil_rate_type,
467 x_project_bil_rate_date => l_project_bil_rate_date,
468 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
469 x_projfunc_currency_code => l_projfunc_currency_code,
470 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
471 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
472 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
473 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
474 x_funding_rate_date_code => l_funding_rate_date_code,
475 x_funding_rate_type => l_funding_rate_type,
476 x_funding_rate_date => l_funding_rate_date,
477 x_funding_exchange_rate => l_funding_exchange_rate,
478 x_return_status => l_return_status,
479 x_msg_count => l_msg_count,
480 x_msg_data => l_msg_data);
481 OPEN func_rev_inv_amt( X2_project_id,X2_task_id,X2_accrue_through_date);
482 Loop
483 FETCH func_rev_inv_amt
484 INTO l_trans_rev_amt,l_trans_bill_amt,l_txn_currency_code,l_projfunc_currency_code,
485 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
486 EXIT WHEN func_rev_inv_amt%NOTFOUND;
487 IF ( l_calling_process = 'Revenue' ) THEN
488 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
489 l_passd_amt := l_trans_rev_amt;
490 ELSIF ( l_calling_process = 'Invoice' ) THEN
491 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
492 l_passd_amt := l_trans_bill_amt;
493 END IF;
494 /* Calling convert amount proc to convert this amount in PFC */
495 PA_MULTI_CURRENCY.convert_amount(
496 P_FROM_CURRENCY => l_txn_currency_code,
497 P_TO_CURRENCY => l_projfunc_currency_code,
498 P_CONVERSION_DATE => l_projfunc_rate_date,
499 P_CONVERSION_TYPE => l_projfunc_rate_type,
500 P_AMOUNT => l_passd_amt,
501 P_USER_VALIDATE_FLAG => 'Y',
502 P_HANDLE_EXCEPTION_FLAG => 'Y',
503 P_CONVERTED_AMOUNT => l_converted_amount,
504 P_DENOMINATOR => l_denominator,
505 P_NUMERATOR => l_numerator,
506 P_RATE => l_projfunc_exchange_rate,
507 X_STATUS => l_staus);
508 IF ( l_staus IS NOT NULL ) THEN
509 l_converted_amount := 0;
510 END IF;
511
512 l_projfunc_amount_sum := NVL(l_projfunc_amount_sum,0) + NVL(l_converted_amount,0);
513 IF g1_debug_mode = 'Y' THEN
514 PA_MCB_INVOICE_PKG.log_message('PotEventAmount: ' || 'Number 01 pa_billing_amount.PotEvent.l_projfunc_amount_sum :'||to_char(l_projfunc_amount_sum));
515 END IF;
516 End Loop;
517 Close func_rev_inv_amt;
518
519 IF ( l_calling_process = 'Revenue' ) THEN
520 X2_revenue_amount := l_projfunc_amount_sum;
521 X2_invoice_amount := 0;
522 ELSIF ( l_calling_process = 'Invoice' ) THEN
523 X2_revenue_amount := 0;
524 X2_invoice_amount := l_projfunc_amount_sum;
525 END IF;
526 IF g1_debug_mode = 'Y' THEN
527 PA_MCB_INVOICE_PKG.log_message('PotEventAmount: ' || 'Exiting pa_billing_amount.PotEvent :');
528 END IF;
529 END PotEventAmount;
530
531
532
533 Procedure InvoiceAmount( X2_project_id NUMBER,
534 X2_task_id NUMBER default NULL,
535 X2_invoice_amount OUT NOCOPY REAL) IS --File.Sql.39 bug 4440895
536
537 pending_ccinv REAL;
538 task_billed_ccinv REAL;
539 task_billed_ev_ccinv REAL;
540 billed_ccinv REAL;
541
542 /* Varibles added for MCB2 */
543 l_trans_bill_amt pa_events.bill_trans_bill_amount%TYPE;
544 l_projfunc_bill_amount_sum pa_events.projfunc_bill_amount%TYPE;
545 l_converted_bill_amount pa_events.projfunc_bill_amount%TYPE;
546 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
547 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
548 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
549 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
550 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
551 l_event_date pa_events.completion_date%TYPE;
552 l_conv_date pa_events.completion_date%TYPE;
553 l_denominator Number;
554 l_numerator Number;
555 l_staus Varchar2(30);
556 l_project_id pa_projects_all.project_id%TYPE;
557 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
558 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
559 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
560 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
561 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
562 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
563 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
564 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
565 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
566 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
567 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
568 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
569 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
570 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
571 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
572 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
573 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
574 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
575 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
576 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
577 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
578 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
579 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
580 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
581 l_return_status VARCHAR2(30);
582 l_msg_count NUMBER;
583 l_msg_data VARCHAR2(30);
584
585 l_projfunc_convers_fail EXCEPTION;
586
587 CURSOR func_invoice(X2_project_id Number,X2_task_id Number) IS
588 SELECT NVL(e.bill_trans_bill_amount,0) trans_bill_amount,e.bill_trans_currency_code,
589 e.projfunc_currency_code,
590 e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
591 FROM pa_events e,
592 pa_billing_assignments bea,
593 pa_billing_extensions be
594 WHERE be.billing_extension_id = bea.billing_extension_id
595 AND bea.billing_assignment_id = e.billing_assignment_id
596 AND e.project_id = X2_project_id
597 AND be.procedure_name = 'pa_billing.ccrev'
598 AND nvl(e.task_id,0) = decode(X2_task_id,
599 NULL, nvl(e.task_id,0), X2_task_id)
600 AND NOT EXISTS
601 (select 'billed'
602 from pa_draft_invoice_items pdii
603 where pdii.project_id = e.project_id
604 and pdii.event_num = e.event_num
605 and nvl(pdii.task_id,0) = nvl(e.task_id,0));
606
607 BEGIN
608 IF g1_debug_mode = 'Y' THEN
609 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_amount.InvoiceAmount :');
610 END IF;
611
612 -- Cost/Cost Invoice Amount that has been created as an event, but not billed
613 -- yet.
614 /* The following code is commented because this amount is in IPC i.e.
615 Invoice programm is going to populate this amount
616 */
617
618 /*
619 SELECT sum(nvl(e.bill_amount,0))
620 INTO pending_ccinv
621 from pa_events e,
622 pa_billing_assignments bea,
623 pa_billing_extensions be
624 where be.billing_extension_id = bea.billing_extension_id
625 and bea.billing_assignment_id = e.billing_assignment_id
626 and e.project_id = X2_project_id
627 and be.procedure_name = 'pa_billing.ccrev'
628 and nvl(e.task_id,0) = decode(X2_task_id,
629 NULL, nvl(e.task_id,0), X2_task_id)
630 and NOT EXISTS
631 (select 'billed'
632 from pa_draft_invoice_items pdii
633 where pdii.project_id = e.project_id
634 and pdii.event_num = e.event_num
635 and nvl(pdii.task_id,0) = nvl(e.task_id,0)); */
636
637 /* Following code has been added for MCB2 */
638 l_project_id := X2_project_id;
639 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
640 p_project_id => l_project_id,
641 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
642 x_baseline_funding_flag => l_baseline_funding_flag,
643 x_revproc_currency_code => l_revproc_currency_code,
644 x_invproc_currency_type => l_invproc_currency_type,
645 x_invproc_currency_code => l_invproc_currency_code,
646 x_project_currency_code => l_project_currency_code,
647 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
648 x_project_bil_rate_type => l_project_bil_rate_type,
649 x_project_bil_rate_date => l_project_bil_rate_date,
650 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
651 x_projfunc_currency_code => l_projfunc_currency_code,
652 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
653 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
654 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
655 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
656 x_funding_rate_date_code => l_funding_rate_date_code,
657 x_funding_rate_type => l_funding_rate_type,
658 x_funding_rate_date => l_funding_rate_date,
659 x_funding_exchange_rate => l_funding_exchange_rate,
660 x_return_status => l_return_status,
661 x_msg_count => l_msg_count,
662 x_msg_data => l_msg_data);
663
664 OPEN func_invoice( X2_project_id,X2_task_id);
665 Loop
666 FETCH func_invoice INTO l_trans_bill_amt,l_txn_currency_code,l_projfunc_currency_code,
667 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
668 EXIT WHEN func_invoice%NOTFOUND;
669 IF ( l_project_bil_rate_date_code = 'PA_INVOICE_DATE' ) THEN
670 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
671 END IF;
672 /* Calling convert amount proc to convert this amount in PFC */
673 PA_MULTI_CURRENCY.convert_amount(
674 P_FROM_CURRENCY => l_txn_currency_code,
675 P_TO_CURRENCY => l_projfunc_currency_code,
676 P_CONVERSION_DATE => l_projfunc_rate_date,
677 P_CONVERSION_TYPE => l_projfunc_rate_type,
678 P_AMOUNT => l_trans_bill_amt,
679 P_USER_VALIDATE_FLAG => 'Y',
680 P_HANDLE_EXCEPTION_FLAG => 'Y',
681 P_CONVERTED_AMOUNT => l_converted_bill_amount,
682 P_DENOMINATOR => l_denominator,
683 P_NUMERATOR => l_numerator,
684 P_RATE => l_projfunc_exchange_rate,
685 X_STATUS => l_staus);
686 IF ( l_staus IS NOT NULL ) THEN
687 l_converted_bill_amount := 0;
688 END IF;
689
690 l_projfunc_bill_amount_sum := NVL(l_projfunc_bill_amount_sum,0) + NVL(l_converted_bill_amount,0);
691 IF g1_debug_mode = 'Y' THEN
692 PA_MCB_INVOICE_PKG.log_message('inside pa_billing_amount.InvoiceAmount :'||l_projfunc_bill_amount_sum);
693 END IF;
694 End Loop;
695 Close func_invoice;
696 pending_ccinv := l_projfunc_bill_amount_sum;
697
698
699 IF (X2_task_id IS NULL) THEN
700
701 -- Cost-Cost Invoice Amount that has been billed, or originates from
702 -- expenditure items (historical cost-cost invoice amount)
703
704 /* change this column from amount to projfunc_bill_amount for MCB2 */
705 SELECT sum(nvl(dii.projfunc_bill_amount,0))
706 INTO billed_ccinv
707 FROM pa_draft_invoice_items dii
708 WHERE dii.project_id = X2_project_id
709 AND (EXISTS (select '1'
710 from pa_events e,
711 pa_billing_assignments bea,
712 pa_billing_extensions be
713 where be.billing_extension_id = bea.billing_extension_id
714 and bea.billing_assignment_id = e.billing_assignment_id
715 and dii.project_id = e.project_id
716 and dii.event_num = e.event_num
717 and nvl(dii.event_task_id,0) = nvl(e.task_id,0)
718 and be.procedure_name = 'pa_billing.ccrev')
719 OR EXISTS ( select '1'
720 from pa_cust_rev_dist_lines erdl
721 where erdl.project_id = dii.project_id
722 and erdl.draft_invoice_num = dii.draft_invoice_num
723 and erdl.draft_invoice_item_line_num = dii.line_num));
724
725
726
727
728 X2_invoice_amount := nvl(pending_ccinv,0) + nvl(billed_ccinv,0);
729
730 ELSE
731
732 /* Change this column from amount to projfunc_bill_amount for MCB2 */
733 SELECT sum(nvl(rdl.projfunc_bill_amount,0))
734 INTO task_billed_ccinv
735 FROM pa_cust_rev_dist_lines rdl,
736 pa_expenditure_items_all ei,
737 pa_tasks t
738 WHERE ei.task_id = t.task_id
739 AND ei.expenditure_item_id = rdl.expenditure_item_id
740 AND rdl.project_id = X2_project_id
741 AND t.top_task_id = X2_task_id
742 AND rdl.draft_invoice_num IS NOT NULL;
743
744 /* Change this column from amount to projfunc_bill_amount for MCB2 */
745 SELECT sum(nvl(pdii.projfunc_bill_amount,0))
746 INTO task_billed_ev_ccinv
747 FROM pa_draft_invoice_items pdii
748 WHERE pdii.event_task_id = X2_task_id
749 AND pdii.Project_ID = X2_Project_ID -- Perf Bug 2695243
750 AND EXISTS (select '1'
751 from pa_events e,
752 pa_billing_assignments bea,
753 pa_billing_extensions be
754 where be.billing_extension_id = bea.billing_extension_id
755 and bea.billing_assignment_id = e.billing_assignment_id
756 and pdii.project_id = e.project_id
757 and pdii.event_num = e.event_num
758 and pdii.event_task_id = e.task_id
759 and be.procedure_name = 'pa_billing.ccrev');
760
761 X2_invoice_amount := nvl(task_billed_ccinv,0) + nvl(task_billed_ev_ccinv,0) + nvl(pending_ccinv,0);
762
763 IF g1_debug_mode = 'Y' THEN
764 PA_MCB_INVOICE_PKG.log_message('Overall invoice amount pa_billing_amount.InvoiceAmount :'||to_char(nvl(task_billed_ccinv,0) + nvl(task_billed_ev_ccinv,0) + nvl(pending_ccinv,0)));
765 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_amount.InvoiceAmount :');
766 END IF;
767 END IF;
768
769 END InvoiceAmount;
770
771
772
773 FUNCTION LowestAmountLeft ( X2_project_id NUMBER,
774 X2_task_id NUMBER,
775 X2_calling_process VARCHAR2)
776 RETURN REAL IS
777
778 lowest_revenue_amount REAL := 0;
779 lowest_invoice_amount REAL := 0;
780 current_event_revenue REAL := 0;
781 current_event_invoice REAL := 0;
782
783 l_trans_rev_amt pa_events.bill_trans_rev_amount%TYPE;
784 l_trans_bill_amt pa_events.bill_trans_bill_amount%TYPE;
785 l_passd_amt pa_events.bill_trans_bill_amount%TYPE := 0;
786 l_projfunc_amount_sum pa_events.projfunc_revenue_amount%TYPE;
787 l_converted_amount pa_events.projfunc_revenue_amount%TYPE;
788 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
789 l_projfunc_currency_code pa_events.projfunc_currency_code%TYPE;
790 l_projfunc_rate_type pa_events.projfunc_rate_type%TYPE;
791 l_projfunc_rate_date pa_events.projfunc_rate_date%TYPE;
792 l_projfunc_exchange_rate pa_events.projfunc_exchange_rate%TYPE;
793 l_event_date pa_events.completion_date%TYPE;
794 l_conv_date pa_events.completion_date%TYPE;
795 l_denominator Number;
796 l_numerator Number;
797 l_staus Varchar2(30);
798 l_project_id pa_projects_all.project_id%TYPE;
799 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
800 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
801 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
802 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
803 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
804 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
805 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
806 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
807 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
808 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
809 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
810 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
811 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
812 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
813 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
814 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
815 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
816 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
817 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
818 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
819 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
820 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
821 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
822 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
823 l_return_status VARCHAR2(30);
824 l_msg_count NUMBER;
825 l_msg_data VARCHAR2(30);
826
827 l_projfunc_convers_fail EXCEPTION;
828
829 l_Enable_Top_Task_Cust_Flag VARCHAR2(1);
830
831 CURSOR func_lwst_rev_inv_amt(X2_project_id Number,X2_task_id Number) IS
832 SELECT DECODE(e.revenue_distributed_flag,'N', NVL(e.bill_trans_rev_amount,0),0) trans_rev_amount,
833 DECODE(pdii.event_num,NULL, NVL(e.bill_trans_bill_amount,0),0) trans_bill_amount,
834 e.bill_trans_currency_code,
835 e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
836 FROM pa_events e, pa_event_types et, pa_draft_invoice_items pdii
837 WHERE e.project_id = X2_project_id
838 AND pdii.project_id (+)= e.project_id
839 AND pdii.event_num (+)= e.event_num
840 AND nvl(pdii.event_task_id,0) = nvl(e.task_id,0)
841 AND nvl(e.task_id,0) = nvl(X2_task_id,0)
842 AND e.event_type = et.event_type
843 AND et.event_type_classification||'' = 'AUTOMATIC';
844
845 BEGIN
846
847 IF g1_debug_mode = 'Y' THEN
848 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_amount.LowestAmountLeft :');
849 END IF;
850 -- This select gets automatic events that were created in this run or some
851 -- previous run, but have not been accrued/billed yet.
852 -- This must be subtracted from amount available to get the real amount
853 -- available.
854 /* The following sql has been commented for MCB2 */
855 /*
856 SELECT sum(decode(e.revenue_distributed_flag,
857 'N', nvl(e.revenue_amount,0),
858 0)),
859 sum(decode(pdii.event_num,
860 NULL, nvl(e.bill_amount,0),
861 0))
862 INTO current_event_revenue, current_event_invoice
863 FROM pa_events e, pa_event_types et, pa_draft_invoice_items pdii
864 WHERE e.project_id = X2_project_id
865 AND pdii.project_id (+)= e.project_id
866 and pdii.event_num (+)= e.event_num
867 and nvl(pdii.event_task_id,0) = nvl(e.task_id,0)
868 AND nvl(e.task_id,0) = nvl(X2_task_id,0)
869 and e.event_type = et.event_type
870 and et.event_type_classification||'' = 'AUTOMATIC';
871 */
872
873 /* Following code has been added for MCB2 */
874 l_project_id := X2_project_id;
875 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
876 p_project_id => l_project_id,
877 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
878 x_baseline_funding_flag => l_baseline_funding_flag,
879 x_revproc_currency_code => l_revproc_currency_code,
880 x_invproc_currency_type => l_invproc_currency_type,
881 x_invproc_currency_code => l_invproc_currency_code,
882 x_project_currency_code => l_project_currency_code,
883 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
884 x_project_bil_rate_type => l_project_bil_rate_type,
885 x_project_bil_rate_date => l_project_bil_rate_date,
886 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
887 x_projfunc_currency_code => l_projfunc_currency_code,
888 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
889 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
890 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
891 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
892 x_funding_rate_date_code => l_funding_rate_date_code,
893 x_funding_rate_type => l_funding_rate_type,
894 x_funding_rate_date => l_funding_rate_date,
895 x_funding_exchange_rate => l_funding_exchange_rate,
896 x_return_status => l_return_status,
897 x_msg_count => l_msg_count,
898 x_msg_data => l_msg_data);
899 OPEN func_lwst_rev_inv_amt( X2_project_id,X2_task_id);
900 Loop
901 FETCH func_lwst_rev_inv_amt
902 INTO l_trans_rev_amt,l_trans_bill_amt,l_txn_currency_code,l_projfunc_currency_code,
903 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate;
904 EXIT WHEN func_lwst_rev_inv_amt%NOTFOUND;
905 IF ( X2_calling_process = 'Revenue' ) THEN
906 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetPaDate);
907 l_passd_amt := l_trans_rev_amt;
908 ELSIF ( X2_calling_process = 'Invoice' ) THEN
909 l_projfunc_rate_date := NVL(l_projfunc_rate_date,pa_billing.GetInvoiceDate);
910 l_passd_amt := l_trans_bill_amt;
911 END IF;
912 /* Calling convert amount proc to convert this amount in PFC */
913 PA_MULTI_CURRENCY.convert_amount(
914 P_FROM_CURRENCY => l_txn_currency_code,
915 P_TO_CURRENCY => l_projfunc_currency_code,
916 P_CONVERSION_DATE => l_projfunc_rate_date,
917 P_CONVERSION_TYPE => l_projfunc_rate_type,
918 P_AMOUNT => l_passd_amt,
919 P_USER_VALIDATE_FLAG => 'Y',
920 P_HANDLE_EXCEPTION_FLAG => 'Y',
921 P_CONVERTED_AMOUNT => l_converted_amount,
922 P_DENOMINATOR => l_denominator,
923 P_NUMERATOR => l_numerator,
924 P_RATE => l_projfunc_exchange_rate,
925 X_STATUS => l_staus);
926 IF ( l_staus IS NOT NULL ) THEN
927 l_converted_amount := 0;
928 END IF;
929
930 l_projfunc_amount_sum := NVL(l_projfunc_amount_sum,0) + NVL(l_converted_amount,0);
931 IF g1_debug_mode = 'Y' THEN
932 PA_MCB_INVOICE_PKG.log_message('LowestAmountLeft: ' || 'Inside pa_billing_amount.LowestAmount :'||l_projfunc_amount_sum);
933 END IF;
934 End Loop;
935 Close func_lwst_rev_inv_amt;
936
937 IF ( X2_calling_process = 'Revenue' ) THEN
938 current_event_revenue := l_projfunc_amount_sum;
939 current_event_invoice := 0;
940 ELSIF ( X2_calling_process = 'Invoice' ) THEN
941 current_event_revenue := 0;
942 current_event_invoice := l_projfunc_amount_sum;
943 END IF;
944 /* DBMS_OUTPUT.PUT('current_event_revenue='); */
945 /* DBMS_OUTPUT.PUT(current_event_revenue); */
946 /* DBMS_OUTPUT.PUT('current_event_invoice='); */
947 /* DBMS_OUTPUT.PUT(current_event_invoice); */
948 /** Bug # 505759 , changed the select to use (100/pc.customer_bill_split)
949 rather than (pc.customer_bill_split * .01)
950 **/
951
952 /* MCB2: Change the name from total_accrued_amount to projfunc_accrued_amount,
953 total_billed_amount to projfunc_billed_amount, and total_baselined_amount to
954 projfunc_baselined_amount */
955
956 -- Following changes are made for FP_M : Top Task customer changes
957 l_Enable_Top_Task_Cust_Flag := PA_Billing_Pub.Get_Top_Task_Customer_Flag (
958 P_Project_ID => l_Project_ID );
959
960 -- If the project is implemented with Top Task customer enabled then the
961 -- lowest revenue amount is calculated as the total baselined fundings
962 -- less the total accrued amounts.
963 -- Similarly the lowest invoice amount is calculated as the total baselined
964 -- fundings less the total billed amount.
965 --
966 If l_Enable_Top_Task_Cust_Flag = 'Y' then
967 SELECT sum(nvl(psf.projfunc_baselined_amount,0)
968 - nvl(psf.projfunc_accrued_amount,0)),
969 sum(nvl(psf.projfunc_baselined_amount,0)
970 - nvl(psf.projfunc_billed_amount,0))
971 INTO lowest_revenue_amount,
972 lowest_invoice_amount
973 FROM pa_summary_project_fundings psf,
974 pa_agreements_all a
975 WHERE a.agreement_id = psf.agreement_id
976 AND psf.project_id = X2_project_id
977 AND psf.task_id = X2_task_id
978 AND DECODE (X2_calling_process,'Revenue',
979 a.revenue_limit_flag||'','Invoice',
980 a.invoice_limit_flag||'') = 'Y' ;
981 Else
982 SELECT min(sum(nvl(psf.projfunc_baselined_amount,0)
983 - nvl(psf.projfunc_accrued_amount,0))
984 * (100/nvl(pc.customer_bill_split,100)) ), /*Bug 5718115*/
985 min(sum(nvl(psf.projfunc_baselined_amount,0)
986 - nvl(psf.projfunc_billed_amount,0))
987 * (100/nvl(pc.customer_bill_split,100)) ) /* Bug 5718115*/
988 INTO lowest_revenue_amount,
989 lowest_invoice_amount
990 FROM pa_summary_project_fundings psf,
991 pa_agreements_all a, /* Changed table from pa_agreements to pa_agreements_all for MCB2 */
992 pa_projects p,
993 pa_project_customers pc
994 WHERE
995 a.agreement_id = psf.agreement_id
996 AND p.project_id = psf.project_id
997 AND a.customer_id = pc.customer_id
998 AND pc.project_id = p.project_id
999 AND nvl(psf.task_id,0) = nvl(X2_task_id,0)
1000 AND psf.project_id = X2_project_id
1001 AND DECODE (X2_calling_process,'Revenue',a.revenue_limit_flag||'','Invoice',a.invoice_limit_flag||'') = 'Y'
1002 GROUP BY pc.customer_id, pc.customer_bill_split;
1003 /* Change the above condition (X2_calling_process one ) and added decode to get only revenue or only invoice amounts for MCB2 */
1004 END IF ;
1005 -- End of FP_M changes
1006
1007 IF g1_debug_mode = 'Y' THEN
1008 PA_MCB_INVOICE_PKG.log_message('LowestAmountLeft: ' || 'Entering .LowestAmount 1 :'||lowest_revenue_amount);
1009 PA_MCB_INVOICE_PKG.log_message('LowestAmountLeft: ' || 'Entering .LowestAmount 2 :'||lowest_invoice_amount);
1010 END IF;
1011
1012
1013 IF (X2_calling_process = 'Revenue') THEN
1014 return greatest((nvl(lowest_revenue_amount,999999999999)
1015 - nvl(current_event_revenue,0)),0);
1016 ELSE
1017 return greatest((nvl(lowest_invoice_amount,999999999999)
1018 - nvl(current_event_invoice,0)),0);
1019 END IF;
1020
1021 IF g1_debug_mode = 'Y' THEN
1022 PA_MCB_INVOICE_PKG.log_message('LowestAmountLeft: ' || 'Exiting pa_billing_amount.LowestAmount :');
1023 END IF;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 /* DBMS_OUTPUT.PUT_LINE(SQLERRM); */
1027 RAISE;
1028 END LowestAmountLeft;
1029
1030
1031 FUNCTION rdl_amount(X_which VARCHAR2, X_eiid NUMBER, X_adj VARCHAR2, X_ei_adj VARCHAR2)
1032 return REAL IS
1033
1034 Ramount REAL;
1035 BEGIN
1036 IF g1_debug_mode = 'Y' THEN
1037 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_amount.rdl_amount :');
1038 END IF;
1039 /* Changed column from bill_amount to projfunc_bill_amount and amount to projfunc_revenue_amount for MCB2 */
1040 SELECT sum(decode(X_which, 'I', nvl(rdl.projfunc_bill_amount,0),
1041 'R', NVL(rdl.projfunc_revenue_amount,0),NVL(rdl.projfunc_revenue_amount,0)))
1042 INTO Ramount
1043 FROM pa_cust_rev_dist_lines rdl
1044 WHERE rdl.expenditure_item_id = X_eiid
1045 AND (X_adj = 'ADJ'
1046 AND (rdl.line_num_reversed IS NOT NULL
1047 OR X_ei_adj = 'Y')
1048 OR (X_adj = 'REG'
1049 AND (rdl.line_num_reversed IS NULL
1050 and rdl.reversed_flag IS NULL
1051 and X_ei_adj = 'N')));
1052
1053 -- Explanation for last two statements above:
1054 -- 1. If we wants adjustment items only, only rdl's with
1055 -- NOT NULL line_num_reversed will be returned, or rdl's
1056 -- belonging to reversin ei's.
1057 -- If we want regular items only, only rdl's with a NULL
1058 -- line_num_reversed will be returned. These rdl's MUST NOT
1059 -- belong to a reversing ei.
1060 -- If want both, all rdl's will be returned.
1061 -- 2. Exclude items which have been reversed out, if
1062 -- only positive items requested.
1063
1064 IF g1_debug_mode = 'Y' THEN
1065 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_amount.rdl_amount :');
1066 END IF;
1067
1068 RETURN Ramount;
1069 END rdl_amount;
1070
1071
1072 PROCEDURE get_baseline_budget
1073 ( X_project_id in NUMBER,
1074 X_rev_budget out NOCOPY REAL, --File.Sql.39 bug 4440895
1075 X_cost_budget out NOCOPY REAL , --File.Sql.39 bug 4440895
1076 X_err_msg out NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1077 as
1078 l_cost_budget_version_id pa_budget_versions.budget_version_id%type;
1079 l_rev_budget_version_id pa_budget_versions.budget_version_id%type;
1080 l_quantity_total Real;
1081 l_raw_cost_total Real;
1082 l_burdened_cost_total Real;
1083 l_err_code Number;
1084 l_revenue_total Real;
1085 l_err_stage VARCHAR2(30);
1086 l_err_stack VARCHAR2(630);
1087 l_dummy VARCHAR2(1);
1088 c Number;
1089 cost_budget_exception exception;
1090 rev_budget_exception exception;
1091
1092
1093 BEGIN
1094 IF g1_debug_mode = 'Y' THEN
1095 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_amount.get_baseline_budget :');
1096 END IF;
1097 c := 5;
1098
1099 SELECT Decode(SUBSTR(distribution_rule,INSTR(distribution_rule,'/')+1),'COST','x','y') /* Added Decode for Bug 2389765 */
1100 INTO l_dummy
1101 FROM pa_projects_all
1102 WHERE project_id = X_project_id
1103 AND substr(distribution_rule,1,instr(distribution_rule,'/')-1)
1104 IN ('COST','EVENT')
1105 AND exists ( select 'x'
1106 from pa_events e,
1107 pa_event_types et
1108 where e.project_id = X_project_id
1109 and e.event_type = et.event_type
1110 and et.event_type_classification = 'SCHEDULED PAYMENTS');
1111 /* Bug 2389765 . Cost Budget is needed only when the Distribution Rule is
1112 'COST/COST'. Hence the following if condition is added
1113 to make sure cost budget details are required only for
1114 Distribution rule 'COST/COST' */
1115
1116 If (l_dummy='x') then
1117
1118 /* Added for Fin plan impact */
1119 BEGIN
1120 SELECT v.budget_version_id
1121 INTO l_cost_budget_version_id
1122 FROM pa_budget_versions v
1123 WHERE v.project_id = X_project_id
1124 AND v.current_flag = 'Y'
1125 AND v.budget_status_code = 'B'
1126 AND v.version_type IN ('COST','ALL');
1127
1128 EXCEPTION
1129 WHEN NO_DATA_FOUND THEN
1130
1131 c := 10;
1132 SELECT budget_version_id
1133 INTO l_cost_budget_version_id
1134 FROM pa_budget_versions pbv
1135 WHERE project_id = X_project_id
1136 AND budget_type_code = 'AC'
1137 AND budget_status_code = 'B'
1138 AND current_flag = 'Y';
1139
1140 END;
1141
1142 end if;
1143
1144
1145 /* Added for Fin plan impact */
1146 BEGIN
1147 SELECT v.budget_version_id
1148 INTO l_rev_budget_version_id
1149 FROM pa_budget_versions v
1150 WHERE v.project_id = X_project_id
1151 AND v.current_flag = 'Y'
1152 AND v.budget_status_code = 'B'
1153 AND v.version_type IN ('REVENUE','ALL')
1154 AND v.approved_rev_plan_type_flag = 'Y' ; /* Added for bug 4059918 */
1155
1156 EXCEPTION
1157 WHEN NO_DATA_FOUND THEN
1158 c := 20;
1159
1160 SELECT budget_version_id
1161 INTO l_rev_budget_version_id
1162 FROM pa_budget_versions pbv
1163 WHERE project_id = X_project_id
1164 AND budget_type_code = 'AR'
1165 AND budget_status_code = 'B'
1166 AND current_flag = 'Y';
1167
1168 END;
1169
1170 If (l_dummy='x') then /* if added for Bug 2389765 */
1171 pa_budget_utils.get_project_task_totals
1172 (l_cost_budget_version_id ,
1173 NULL ,
1174 l_quantity_total,
1175 l_raw_cost_total ,
1176 l_burdened_cost_total ,
1177 l_revenue_total ,
1178 l_err_code ,
1179 l_err_stage,
1180 l_err_stack );
1181
1182 If (l_burdened_cost_total is null) or ( l_burdened_cost_total = 0)
1183 then
1184 raise cost_budget_exception;
1185 end if;
1186
1187 X_cost_budget :=
1188 pa_currency.round_currency_amt(l_burdened_cost_total);
1189 end if;
1190 pa_budget_utils.get_project_task_totals
1191 (l_rev_budget_version_id ,
1192 NULL ,
1193 l_quantity_total,
1194 l_raw_cost_total ,
1195 l_burdened_cost_total ,
1196 l_revenue_total ,
1197 l_err_code ,
1198 l_err_stage,
1199 l_err_stack );
1200
1201 If (l_revenue_total is null) or ( L_revenue_total = 0)
1202 then
1203 raise rev_budget_exception;
1204 end if;
1205
1206 X_rev_budget :=
1207 pa_currency.round_currency_amt(l_revenue_total);
1208
1209
1210 IF g1_debug_mode = 'Y' THEN
1211 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_amount.get_baseline_budget :');
1212 END IF;
1213 EXCEPTION
1214 When NO_DATA_FOUND
1215 then
1216 if (c = 5)
1217 then
1218 X_rev_budget := NULL;
1219 X_cost_budget:= NULL;
1220 Return;
1221 elsif ( c = 10)
1222 then
1223 X_err_msg := 'No Cost Budget Version Id';
1224 elsif ( c = 20 )
1225 then
1226 X_err_msg := 'No Revnue Budget Version Id';
1227 end if;
1228
1229 When rev_budget_exception
1230 then
1231 X_rev_budget := 0;
1232 X_err_msg := 'Exception Raised in Revenue Budget calculation';
1233
1234 When cost_budget_exception
1235 then
1236 X_cost_budget := 0;
1237 X_err_msg := 'Exception Raised in Cost Budget Calculation';
1238
1239 END get_baseline_budget;
1240
1241 END pa_billing_amount;