[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_WORKBENCH_BILL_PKG
Source
1 PACKAGE BODY PA_BILLING_WORKBENCH_BILL_PKG as
2 /* $Header: PAXBLWBB.pls 120.5 2006/06/26 05:49:14 rkchoudh noship $ */
3 -- This procedure will get all the parameters for Billing Region for the given project.
4 -- burdened cost and raw revenue on the basis of passed parameters
5 -- Input parameters
6 -- Parameters Type Required Description
7 -- p_project_id NUMBER YES The identifier of the project
8 -- p_project_currency VARCHAR2 YES Currency of the project
9 -- p_projfunc_currency VARCHAR2 YES Project functional currency
10 -- p_ubr NUMBER YES Total Unbilled receivables for the given project
11 -- p_uer NUMBER YES Total Unearned revenue for the given project
12 --
13 -- Out parameters
14 --
15 --
16 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
17
18 /* Declaring global variable for invoice region VO */
19 G_system_reference NUMBER;
20 G_ar_amount NUMBER;
21
22 PROCEDURE Get_Billing_Sum_Region_Amts (
23 p_project_id IN NUMBER ,
24 p_project_currency IN VARCHAR2 ,
25 p_projfunc_currency IN VARCHAR2 ,
26 p_ubr IN NUMBER ,
27 p_uer IN NUMBER ,
28 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
29 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
30 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
31 )
32 IS
33
34 -- Local standard variables used to pass calculated values
35 -- in project currency to the calling program
36
37 l_proj_funding_amt NUMBER ;
38 l_proj_rev_accured NUMBER ;
39 l_proj_rev_backlog NUMBER ;
40 l_proj_rev_writeoff NUMBER ;
41 l_proj_ubr NUMBER ;
42 l_proj_uer NUMBER ;
43 l_proj_inv_invoiced NUMBER ;
44 l_proj_inv_backlog NUMBER ;
45 l_proj_billable_cost NUMBER ;
46 l_proj_unbilled_cost NUMBER ;
47 l_proj_unbilled_events NUMBER ;
48 l_proj_unbilled_retn NUMBER ;
49 l_proj_unapprov_inv_amt NUMBER ;
50 l_pc_count NUMBER;
51 l_pc_ubr_applicab_flag VARCHAR2(1) := 'Y';
52 l_pc_uer_applicab_flag VARCHAR2(1) := 'Y';
53 l_pc_unbil_eve_applicab_flag VARCHAR2(1);
54
55
56 -- Local standard variables used to pass calculated values
57 -- in project functional currency to the calling program
58
59 l_projfunc_funding_amt NUMBER ;
60 l_projfunc_rev_accured NUMBER ;
61 l_projfunc_rev_backlog NUMBER ;
62 l_projfunc_rev_writeoff NUMBER ;
63 l_projfunc_ubr NUMBER ;
64 l_projfunc_uer NUMBER ;
65 l_projfunc_inv_invoiced NUMBER ;
66 l_projfunc_inv_backlog NUMBER ;
67 l_projfunc_billable_cost NUMBER ;
68 l_projfunc_unbilled_cost NUMBER ;
69 l_projfunc_unbilled_events NUMBER ;
70 l_projfunc_unbilled_retn NUMBER ;
71 l_projfunc_unapprov_inv_amt NUMBER ;
72 l_pfc_count NUMBER;
73 l_pfc_unbil_eve_applicab_flag VARCHAR2(1);
74
75
76 -- Local variables used for internal calculation
77
78 l_writeoff NUMBER ;
79 l_revproc_writeoff NUMBER ;
80 l_pc_unbill_eve_amt_with_part NUMBER ;
81 l_pc_total_part_bill_amt NUMBER ;
82 l_pfc_unbill_eve_amt_with_part NUMBER ;
83 l_pfc_total_part_bill_amt NUMBER ;
84 l_project_id NUMBER ;
85 l_pfc_invoiced NUMBER ;
86 l_pc_invoiced NUMBER ;
87 l_multi_customer_flag VARCHAR2(1):= 'N';
88 l_count NUMBER ;
89
90 l_projfunc_inv_due_unaccepted NUMBER ;
91 l_proj_inv_due_unaccepted NUMBER ;
92
93 l_projfunc_inv_orig NUMBER ;
94 l_projfunc_inv_due NUMBER ;
95 l_projfunc_tax_orig NUMBER ;
96 l_projfunc_tax_due NUMBER ;
97
98 l_projfunc_inv_tot_due NUMBER ;
99 l_projfunc_inv_tot_paid NUMBER ;
100
101 l_proj_inv_orig NUMBER ;
102 l_proj_inv_due NUMBER ;
103 l_proj_tax_orig NUMBER ;
104 l_proj_tax_due NUMBER ;
105
106 l_proj_inv_tot_due NUMBER ;
107 l_proj_inv_tot_paid NUMBER ;
108
109 l_accepted_exist varchar2(1);
110
111 l_next_invoice_date DATE;
112
113 -- Local standard variables used to pass status and error,
114 -- if occur, to the calling program
115
116 l_return_status VARCHAR2(1);
117 l_msg_count NUMBER ;
118 l_msg_data VARCHAR2(30);
119 l_ubr_uer_msg_data VARCHAR2(30);
120 BEGIN
121
122 -- Initilizing status variable with success status
123 l_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 -- Initilizing FND MSG stack, so that it can start storing messages from 0th index
126 FND_MSG_PUB.initialize;
127
128 -- Initializing the Error Stack
129 PA_DEBUG.init_err_stack('PA_BILLING_WORKBENCH_BILL_PKG.Get_Billing_Sum_Region_Amts');
130
131 BEGIN
132 /* Modified this select for bug 3677900. This select will check how many customers are funding this project
133 */
134 SELECT count(*)
135 INTO l_count
136 FROM pa_project_customers pc
137 WHERE pc.project_id = p_project_id
138 AND EXISTS (
139 SELECT
140 spf.project_id project_id
141 ,spf.agreement_id
142 ,agr.customer_id customer_id
143 FROM
144 pa_agreements_all agr
145 , pa_summary_project_fundings spf
146 WHERE agr.customer_id = pc.customer_id
147 AND agr.agreement_id = spf.agreement_id
148 AND spf.project_id = pc.project_id
149 AND spf.project_id = p_project_id
150 );
151
152 IF ( l_count > 1 ) THEN
153 l_multi_customer_flag := 'Y';
154 ELSIF ( l_count = 1 ) THEN
155 l_multi_customer_flag := 'N';
156 ELSE
157 l_multi_customer_flag := 'N';
158 END IF;
159 END;
160
161 /* get next invoice_date */
162
163 BEGIN
164
165 l_next_invoice_date := pa_billing_cycles_pkg.get_next_billing_date (
166 x_project_id => p_project_id );
167
168 EXCEPTION
169 when others then
170 l_next_invoice_date := NULL;
171 null;
172 END;
173
174 BEGIN
175 /* Funding, Revenue Accrued,Revenue Backlog,Invoiced, and Invoice Backlog
176 in Project and Project Functional Currency for Revenue and Invoice Collections sections */
177 SELECT
178 ( SUM(NVL(spf.project_baselined_amount,0)) ) Total_PC_Funding
179 ,( SUM(NVL(spf.project_accrued_amount,0))) PC_Rev_Accrued
180 ,( (SUM(NVL(spf.project_baselined_amount,0)) ) - (SUM(NVL(spf.project_accrued_amount,0))) ) PC_Rev_backlog
181 ,( SUM(NVL(spf.project_billed_amount,0))) PC_Inv_Invoiced
182 ,( (SUM(NVL(spf.project_baselined_amount,0)) ) - (SUM(NVL(spf.project_billed_amount,0))) ) PC_Inv_backlog
183 ,( SUM(NVL(spf.projfunc_baselined_amount,0)) ) Total_PFC_Funding
184 ,( SUM(NVL(spf.projfunc_accrued_amount,0))) PFC_Rev_Accrued
185 ,( (SUM(NVL(spf.projfunc_baselined_amount,0)) ) - (SUM(NVL(spf.projfunc_accrued_amount,0))) ) PFC_Rev_backlog
186 ,( SUM(NVL(spf.projfunc_billed_amount,0))) PFC_Inv_Invoiced
187 ,( (SUM(NVL(spf.projfunc_baselined_amount,0)) ) - (SUM(NVL(spf.projfunc_billed_amount,0))) ) PFC_Inv_backlog
188 INTO
189 l_proj_funding_amt ,
190 l_proj_rev_accured ,
191 l_proj_rev_backlog ,
192 l_proj_inv_invoiced ,
193 l_proj_inv_backlog ,
194 l_projfunc_funding_amt ,
195 l_projfunc_rev_accured ,
196 l_projfunc_rev_backlog ,
197 l_projfunc_inv_invoiced ,
198 l_projfunc_inv_backlog
199 FROM pa_summary_project_fundings spf
200 WHERE spf.project_id = p_project_id;
201
202 /* Unbilled Receivable, and Unearned Revenue in Project and
203 Project Functional Currency for Revenue Section */
204 IF (p_project_currency = p_projfunc_currency ) THEN
205 l_proj_ubr := p_ubr;
206 l_proj_uer := p_uer;
207 l_projfunc_ubr := p_ubr;
208 l_projfunc_uer := p_uer;
209 l_pc_ubr_applicab_flag := 'Y';
210 l_pc_uer_applicab_flag := 'Y';
211 ELSE
212 /* Populating Unbilled Receivable, and Unearned Revenue only for PFC
213 and 'N/A' for PC */
214 l_projfunc_ubr := p_ubr;
215 l_projfunc_uer := p_uer;
216
217 /* When Project curr is diff from project func, call the below
218 API to convert UBR and UER amounts in PFC to PC - Bug 4932118 */
219 PROJECT_UBR_UER_CONVERT (
220 P_PROJECT_ID => p_project_id ,
221 X_PROJECT_CURR_UBR => l_proj_ubr ,
222 X_PROJECT_CURR_UER => l_proj_uer ,
223 X_RETURN_STATUS => x_return_status,
224 X_MSG_COUNT => x_msg_count,
225 X_MSG_DATA => l_ubr_uer_msg_data );
226 l_pc_ubr_applicab_flag := 'Y';
227 l_pc_uer_applicab_flag := 'Y';
228
229 END IF;
230
231 -- Calculating Revenue write off in Project and Project Functional Currency
232 PA_BILLING.Get_WriteOff_Revenue_Amount(
233 p_project_id => p_project_id,
234 p_task_id => NULL,
235 p_agreement_id => NULL,
236 p_funding_flag => 'N',
237 p_writeoff_amount => l_writeoff,
238 x_project_writeoff_amount => l_proj_rev_writeoff,
239 x_projfunc_writeoff_amount => l_projfunc_rev_writeoff,
240 x_revproc_writeoff_amount => l_revproc_writeoff
241 );
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 NULL;
246 END;
247
248 BEGIN
249 -- Calculating total paid and due amount in project functional currency
250 -- and project currency
251 -- calculate tax amount in pfc/pc
252
253 BEGIN
254 /**
255 * Unaccepted invoices amount
256 **/
257 SELECT
258 SUM(dii.projfunc_bill_amount) pfc_inv_amt,
259 SUM(dii.project_bill_amount) pc_inv_amt
260 INTO
261 l_projfunc_inv_due_unaccepted,
262 l_proj_inv_due_unaccepted
263 FROM pa_draft_invoices_all di,
264 pa_draft_invoice_items dii
265 WHERE dii.project_id = di.project_id
266 AND dii.draft_invoice_num = di.draft_invoice_num
267 AND di.transfer_status_code <> 'A'
268 AND di.project_id = p_project_id
269 AND di.system_reference IS NULL
270 GROUP BY di.project_id;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 l_projfunc_inv_due_unaccepted := 0;
275 l_proj_inv_due_unaccepted := 0;
276 NULL;
277
278 END;
279
280 BEGIN
281
282 /**
283 * Accepted invoices amount
284 **/
285
286 /* check if any accepted invoice exist as the sql below is throwing no data found */
287
288 SELECT 'T' into l_accepted_exist
289 FROM dual
290 WHERE EXISTS
291 (SELECT null
292 FROM pa_draft_invoices_all di
293 WHERE di.project_id = p_project_id
294 AND di.transfer_status_code = 'A'
295 AND di.system_reference IS NOT NULL) ;
296
297 IF l_accepted_exist = 'T' THEN
298
299 SELECT
300 pa_inv.project_id,
301 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) * ar.amount_line_items_remaining)
302 inv_due_pfc,
303 SUM(((pa_inv.pfc_inv_amt)/ar.amount_line_items_original)*(ar.amount_line_items_original ))
304 inv_orig_pfc,
305 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) * ar.tax_original) tax_orig_pfc,
306 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) * ar.tax_remaining) tax_due_pfc,
307 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) * ar.amount_line_items_remaining)
308 inv_due_pc,
309 SUM(((pa_inv.pc_inv_amt)/ar.amount_line_items_original)*(ar.amount_line_items_original )) inv_orig_pc,
310 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) * ar.tax_original) tax_orig_pc,
311 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) * ar.tax_remaining) tax_due_pc
312 INTO
313 l_project_id,
314 l_projfunc_inv_due,
315 l_projfunc_inv_orig,
316 l_projfunc_tax_orig,
317 l_projfunc_tax_due,
318 l_proj_inv_due,
319 l_proj_inv_orig,
320 l_proj_tax_orig,
321 l_proj_tax_due
322 FROM ar_payment_schedules_all ar,
323 (SELECT di.project_id,
324 di.system_reference system_reference,
325 SUM(dii.projfunc_bill_amount) pfc_inv_amt,
326 SUM(dii.project_bill_amount) pc_inv_amt
327 FROM pa_draft_invoices_all di,
328 pa_draft_invoice_items dii
329 WHERE di.project_id = dii.project_id
330 AND di.draft_invoice_num = dii.draft_invoice_num
331 AND di.transfer_status_code = 'A'
332 AND di.system_reference IS NOT NULL
333 GROUP BY di.project_id,di.system_reference) pa_inv
334 WHERE pa_inv.project_id = p_project_id
335 AND pa_inv.system_reference = ar.customer_trx_id
336 GROUP BY pa_inv.project_id;
337
338 end if;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 NULL;
343
344 END;
345
346 /**
347 * Total PFC due, PC due, PFC paid, and PC paid amounts
348 **/
349
350 l_projfunc_inv_tot_due := NVL(l_projfunc_inv_due_unaccepted,0) + NVL(l_projfunc_inv_due,0) +
351 NVL(l_projfunc_tax_due,0);
352 l_proj_inv_tot_due := NVL(l_proj_inv_due_unaccepted,0) + NVL(l_proj_inv_due,0) +
353 NVL(l_proj_tax_due,0);
354 l_projfunc_inv_tot_paid := NVL(l_projfunc_inv_orig,0) - NVL(l_projfunc_inv_due,0) +
355 NVL(l_projfunc_tax_orig,0) - NVL(l_projfunc_tax_due,0) ;
356 l_proj_inv_tot_paid := NVL(l_proj_inv_orig,0) - NVL(l_proj_inv_due,0) +
357 NVL(l_proj_tax_orig,0) - NVL(l_proj_tax_due,0) ;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 NULL;
362 END;
363
364 -- Selecting column for invoicing status section
365 BEGIN
366 -- Calculating total billable cost and unbilled cost in project functional currency
367 -- and project currency
368 /*
369 SELECT
370 SUM(DECODE(system_linkage_function,'BTC',NVL(project_burdened_cost,0),NVL(project_raw_cost,0))) Proj_billable_cost
371 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0,DECODE(system_linkage_function,'BTC',
372 NVL(project_burdened_cost,0),NVL(project_raw_cost,0)),0),0) ) Proj_unbill_cost
373 , SUM(DECODE(system_linkage_function,'BTC',NVL(burden_cost,0),NVL(raw_cost,0))) Projfunc_billable_cost
374 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0,DECODE(system_linkage_function,'BTC',
375 NVL(burden_cost,0),NVL(raw_cost,0)),0),0) ) Projfunc_unbill_cost
376 */
377 SELECT
378 SUM(NVL(project_burdened_cost,0)) Proj_billable_cost
379 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0, NVL(project_burdened_cost,0) ,0),0) )Proj_unbill_cost
380 , SUM(NVL(burden_cost,0)) Projfunc_billable_cost
381 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0, NVL(burden_cost,0),0),0) ) Projfunc_unbill_cost
382 INTO
383 l_proj_billable_cost
384 ,l_proj_unbilled_cost
385 ,l_projfunc_billable_cost
386 ,l_projfunc_unbilled_cost
387 FROM pa_expenditure_items_all
388 WHERE project_id = p_project_id
389 AND nvl(billable_flag,'N') = 'Y';
390
391
392 -- Checking event currency is same as project currency
393 SELECT COUNT(*)
394 INTO l_pc_count
395 FROM pa_events eve
396 WHERE eve.project_id = p_project_id
397 AND eve.bill_trans_currency_code <> eve.project_currency_code;
398
399 IF (l_pc_count < 1 ) THEN
400
401 l_pc_unbil_eve_applicab_flag := 'Y';
402
403 /* Calculating unbilled event amount, which is first selct - second selct */
404 -- Calculating total event amount with partially billed event also if project currency is same
405 -- as bill transaction currency
406 SELECT
407 SUM(NVL(eve.bill_trans_bill_amount,0)) total_pc_unbilled_with_partial
408 INTO
409 l_pc_unbill_eve_amt_with_part
410 FROM pa_events eve
411 WHERE eve.project_id = p_project_id
412 AND nvl(eve.billed_flag,'N') = 'N'
413 AND 1 > ( SELECT COUNT(*)
414 FROM pa_events eve2
415 WHERE eve2.project_id = p_project_id
416 AND eve2.bill_trans_currency_code <> eve2.project_currency_code);
417
418 ELSE
419 l_pc_unbil_eve_applicab_flag := 'N';
420 END IF;
421
422
423 -- Checking event currency is same as project currency
424 SELECT COUNT(*)
425 INTO l_pfc_count
426 FROM pa_events eve
427 WHERE eve.project_id = p_project_id
428 AND eve.bill_trans_currency_code <> eve.projfunc_currency_code;
429
430 IF (l_pfc_count < 1 ) THEN
431
432 l_pfc_unbil_eve_applicab_flag := 'Y';
433 -- Calculating total event amount with partially billed event also if project functional currency is same
434 -- as bill transaction currency
435 SELECT
436 SUM(NVL(eve.bill_trans_bill_amount,0)) tot_pfc_unbilled_with_partial
437 INTO
438 l_pfc_unbill_eve_amt_with_part
439 FROM pa_events eve
440 WHERE eve.project_id = p_project_id
441 AND nvl(eve.billed_flag,'N') = 'N'
442 AND 1 > ( SELECT COUNT(*)
443 FROM pa_events eve2
444 WHERE eve2.project_id = p_project_id
445 AND eve2.bill_trans_currency_code <> eve2.projfunc_currency_code);
446
447 ELSE
448 l_pfc_unbil_eve_applicab_flag := 'N';
449 END IF;
450
451
452 IF( l_pc_unbil_eve_applicab_flag = 'Y' OR l_pfc_unbil_eve_applicab_flag = 'Y' ) THEN
453
454 -- Calculating total partially billed amount for events in project functional currency
455 -- and project currency
456 /*
457 SELECT
458 SUM(NVL(dii.project_bill_amount,0)) pc_total_partial_billed,
459 SUM(NVL(dii.projfunc_bill_amount,0)) pfc_total_partial_billed
460 INTO
461 l_pc_total_part_bill_amt,
462 l_pfc_total_part_bill_amt
463 FROM pa_draft_invoice_items dii
464 WHERE dii.project_id = p_project_id
465 AND EXISTS ( SELECT null
466 FROM pa_events eve
467 WHERE eve.event_num = dii.event_num
468 AND eve.billed_flag = 'N');
469 */
470 SELECT
471 SUM(NVL(dii.project_bill_amount,0)) pc_total_partial_billed,
472 SUM(NVL(dii.projfunc_bill_amount,0)) pfc_total_partial_billed
473 INTO
474 l_pc_total_part_bill_amt,
475 l_pfc_total_part_bill_amt
476 FROM pa_draft_invoice_items dii, pa_events eve
477 WHERE eve.project_id = p_project_id
478 and eve.project_id = dii.project_id
479 and nvl(eve.task_id, 0) = nvl(dii.task_id,0)
480 and eve.event_num = nvl(dii.event_num,0)
481 and nvl(eve.billed_flag,'N') = 'N';
482
483 -- Calculating total unbilled event amount in project functional currency
484 -- and project currency
485 l_proj_unbilled_events := nvl(l_pc_unbill_eve_amt_with_part,0) - nvl(l_pc_total_part_bill_amt,0);
486 l_projfunc_unbilled_events := nvl(l_pfc_unbill_eve_amt_with_part,0) - nvl(l_pfc_total_part_bill_amt,0);
487 END IF;
488
489 -- Calculating total unbilled retention amount in project functional currency
490 -- and project currency
491 SELECT
492 /*
493 SUM(NVL(project_total_billed,0)) - SUM(NVL(project_total_retained,0)) Proj_Unbilled_Retn
494 ,SUM(NVL(projfunc_total_billed,0)) - SUM(NVL(projfunc_total_retained,0)) Projfunc_Unbilled_Retn
495 */
496 SUM(NVL(project_total_retained,0)) - SUM(NVL(project_total_billed,0)) Proj_Unbilled_Retn
497 ,SUM(NVL(projfunc_total_retained,0)) - SUM(NVL(projfunc_total_billed,0)) Projfunc_Unbilled_Retn
498 INTO
499 l_proj_unbilled_retn
500 ,l_projfunc_unbilled_retn
501 FROM pa_summary_project_retn
502 WHERE project_id = p_project_id;
503
504 -- Calculating total unapproved invoice amount in project functional currency
505 -- and project currency
506 SELECT
507 SUM(NVL(dii.project_bill_amount,0)) unapproved_project_invoice,
508 SUM(NVL(dii.projfunc_bill_amount,0)) unapproved_project_invoice
509 INTO
510 l_proj_unapprov_inv_amt,
511 l_projfunc_unapprov_inv_amt
512 FROM pa_draft_invoice_items dii,pa_draft_invoices_all di
513 WHERE dii.draft_invoice_num = di.draft_invoice_num
514 AND dii.project_id = di.project_id
515 AND di.project_id = p_project_id
516 AND di.approved_by_person_id IS NULL;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 NULL;
521 END;
522
523 /* For Bug 3500408 : Introducing NVL clause for all amount columns to ensure that in case any amount column has
524 Null then on the Billing summary Page it shall be viewed as 0.00 . UBR and UER amount wont be touched as it is displayed
525 as N/A in case of PC<> PFC and otherwise when NULL they take value 0.00*/
526 -- Calling procedure to populate temp table
527 Populat_Bill_Workbench_Data (
528 p_project_id => p_project_id,
529 p_proj_funding_amt => NVL(l_proj_funding_amt ,0),
530 p_proj_rev_accured => NVL(l_proj_rev_accured,0) ,
531 p_proj_rev_backlog => NVL(l_proj_rev_backlog,0) ,
532 p_proj_rev_writeoff => NVL(l_proj_rev_writeoff,0) ,
533 p_proj_ubr => l_proj_ubr ,
534 p_proj_uer => l_proj_uer ,
535 p_proj_inv_invoiced => NVL(l_proj_inv_invoiced ,0),
536 p_proj_inv_backlog => NVL(l_proj_inv_backlog ,0),
537 p_proj_inv_paid => NVL(l_proj_inv_tot_paid ,0),
538 p_proj_inv_due => NVL(l_proj_inv_tot_due ,0),
539 p_proj_billable_cost => NVL(l_proj_billable_cost ,0),
540 p_proj_unbilled_cost => NVL(l_proj_unbilled_cost ,0),
541 p_proj_unbilled_events => NVL(l_proj_unbilled_events ,0),
542 p_proj_unbilled_retn => NVL(l_proj_unbilled_retn ,0),
543 p_proj_unapproved_inv_amt => NVL(l_proj_unapprov_inv_amt ,0),
544 p_proj_tax => NVL(l_proj_tax_orig ,0),
545 p_pc_ubr_applicab_flag => l_pc_ubr_applicab_flag,
546 p_pc_uer_applicab_flag => l_pc_uer_applicab_flag,
547 p_pc_unbil_eve_applicab_flag => l_pc_unbil_eve_applicab_flag,
548 p_projfunc_funding_amt => NVL(l_projfunc_funding_amt,0) ,
549 p_projfunc_rev_accured => NVL(l_projfunc_rev_accured ,0),
550 p_projfunc_rev_backlog => NVL(l_projfunc_rev_backlog,0) ,
551 p_projfunc_rev_writeoff => NVL(l_projfunc_rev_writeoff,0) ,
552 p_projfunc_ubr => l_projfunc_ubr ,
553 p_projfunc_uer => l_projfunc_uer ,
554 p_projfunc_inv_invoiced => NVL(l_projfunc_inv_invoiced,0) ,
555 p_projfunc_inv_backlog => NVL(l_projfunc_inv_backlog ,0),
556 p_projfunc_inv_paid => NVL(l_projfunc_inv_tot_paid ,0),
557 p_projfunc_inv_due => NVL(l_projfunc_inv_tot_due ,0),
558 p_projfunc_billable_cost => NVL(l_projfunc_billable_cost ,0),
559 p_projfunc_unbilled_cost => NVL(l_projfunc_unbilled_cost ,0),
560 p_projfunc_unbilled_events => NVL(l_projfunc_unbilled_events ,0),
561 p_projfunc_unbilled_retn => NVL(l_projfunc_unbilled_retn ,0),
562 p_projfunc_unapprov_inv_amt => NVL(l_projfunc_unapprov_inv_amt ,0),
563 p_projfunc_tax => NVL(l_projfunc_tax_orig ,0),
564 p_pfc_unbil_eve_applicab_flag => l_pfc_unbil_eve_applicab_flag,
565 p_next_invoice_date => l_next_invoice_date,
566 p_multi_customer_flag => l_multi_customer_flag,
567 x_return_status => l_return_status,
568 x_msg_count => l_msg_count ,
569 x_msg_data => l_msg_data
570 );
571 /*End of Bug fix for Bug 3500408*/
572 IF l_ubr_uer_msg_data ='PA_NO_EXCH_RATE_EXISTS_PFC_PC' THEN
573 x_msg_data := l_ubr_uer_msg_data;
574 END IF;
575 EXCEPTION
576 WHEN OTHERS THEN
577 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
578 ,p_procedure_name => 'Get_Billing_Sum_Region_Amts' );
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
580 x_msg_count := 1;
581 x_msg_data := SUBSTR(SQLERRM,1,30);
582 RAISE;
583 END Get_Billing_Sum_Region_Amts;
584
585 -- This procedure will populate the temp table with all the input paramters for billing
586 -- work bench.
587 -- Input parameters
588 -- Parameters Type Required Description
589 -- p_project_id NUMBER YES The identifier of the project
590 -- p_funding_amt NUMBER YES Total Baselined amount for the given project
591 -- p_rev_accured NUMBER YES Total Revenue accrued for the given project
592 -- p_rev_backlog NUMBER YES Revenue funding backlog. The diff of above two
593 -- p_rev_writeoff NUMBER YES Total accrued revenue writeoff
594 -- p_ubr NUMBER YES Total Unbilled receivables for the given project
595 -- p_uer NUMBER YES Total Unearned revenue for the given project
596 -- p_inv_billed NUMBER YES Total Invoiced amount(including project invoices, credit
597 -- memos,write-off,cancelling,concession project, and
598 -- retention invoices
599 -- p_inv_backlog NUMBER YES Invoice Funding backlog. The diff of Funding amt and inv_billed
600 -- p_inv_paid NUMBER YES Total invoice amount paid by the customers for this project
601 -- p_inv_due NUMBER YES Total invoice amount due from customers
602 -- p_billable_cost NUMBER YES Sum of the burdened cost of all the expenditure items
603 -- with billable flag as yes and cost distribution as yes
604 -- p_unbilled_cost NUMBER YES Total burdened cost that is not yet billed, but marked
605 -- as billable as yes
606 -- p_unbilled_events NUMBER YES Sum of all invoice events that are not billed to the customers (
607 -- including partialy billed event amount also
608 -- p_unbilled_retn NUMBER YES Total withheld amount that is not billed to the customer
609 -- p_unapproved_inv_amt NUMBER YES Sum of all the unapproved project and retention invoices
610 -- including credit memosof project invoices, cancelling,
611 -- x_funding_amt NUMBER YES Total Baselined amount for the given project
612 -- x_rev_accured NUMBER YES Total Revenue accrued for the given project
613 -- x_rev_backlog NUMBER YES Revenue funding backlog. The diff of above two
614 -- x_rev_writeoff NUMBER YES Total accrued revenue writeoff
615 -- x_ubr NUMBER YES Total Unbilled receivables for the given project
616 -- x_uer NUMBER YES Total Unearned revenue for the given project
617 -- x_inv_billed NUMBER YES Total Invoiced amount(including project invoices, credit
618 -- memos,write-off,cancelling,concession project, and
619 -- retention invoices
620 -- x_inv_backlog NUMBER YES Invoice Funding backlog. The diff of Funding amt and inv_billed
621 -- x_inv_paid NUMBER YES Total invoice amount paid by the customers for this project
622 -- x_inv_due NUMBER YES Total invoice amount due from customers
623 -- x_billable_cost NUMBER YES Sum of the burdened cost of all the expenditure items
624 -- with billable flag as yes and cost distribution as yes
625 -- x_unbilled_cost NUMBER YES Total burdened cost that is not yet billed, but marked
626 -- as billable as yes
627 -- x_unbilled_events NUMBER YES Sum of all invoice events that are not billed to the customers (
628 -- including partialy billed event amount also
629 -- x_unbilled_retn NUMBER YES Total withheld amount that is not billed to the customer
630 -- x_unapproved_inv_amt NUMBER YES Sum of all the unapproved project and retention invoices
631 -- including credit memosof project invoices, cancelling,
632 -- writeoff,concession project
633 -- writeoff,concession project
634 --
635 -- Out parameters
636 --
637
638 PROCEDURE Populat_Bill_Workbench_Data (
639 p_project_id IN NUMBER,
640 p_proj_funding_amt IN NUMBER ,
641 p_proj_rev_accured IN NUMBER ,
642 p_proj_rev_backlog IN NUMBER ,
643 p_proj_rev_writeoff IN NUMBER ,
644 p_proj_ubr IN NUMBER ,
645 p_proj_uer IN NUMBER ,
646 p_proj_inv_invoiced IN NUMBER ,
647 p_proj_inv_backlog IN NUMBER ,
648 p_proj_inv_paid IN NUMBER ,
649 p_proj_inv_due IN NUMBER ,
650 p_proj_billable_cost IN NUMBER ,
651 p_proj_unbilled_cost IN NUMBER ,
652 p_proj_unbilled_events IN NUMBER ,
653 p_proj_unbilled_retn IN NUMBER ,
654 p_proj_unapproved_inv_amt IN NUMBER ,
655 p_proj_tax IN NUMBER ,
656 p_pc_ubr_applicab_flag IN VARCHAR2,
657 p_pc_uer_applicab_flag IN VARCHAR2,
658 p_pc_unbil_eve_applicab_flag IN VARCHAR2,
659 p_projfunc_funding_amt IN NUMBER ,
660 p_projfunc_rev_accured IN NUMBER ,
661 p_projfunc_rev_backlog IN NUMBER ,
662 p_projfunc_rev_writeoff IN NUMBER ,
663 p_projfunc_ubr IN NUMBER ,
664 p_projfunc_uer IN NUMBER ,
665 p_projfunc_inv_invoiced IN NUMBER ,
666 p_projfunc_inv_backlog IN NUMBER ,
667 p_projfunc_inv_paid IN NUMBER ,
668 p_projfunc_inv_due IN NUMBER ,
669 p_projfunc_billable_cost IN NUMBER ,
670 p_projfunc_unbilled_cost IN NUMBER ,
671 p_projfunc_unbilled_events IN NUMBER ,
672 p_projfunc_unbilled_retn IN NUMBER ,
673 p_projfunc_unapprov_inv_amt IN NUMBER ,
674 p_projfunc_tax IN NUMBER ,
675 p_pfc_unbil_eve_applicab_flag IN VARCHAR2,
676 p_next_invoice_date IN DATE,
677 p_multi_customer_flag IN VARCHAR2,
678 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
679 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
680 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
681 )
682 IS
683 BEGIN
684 x_return_status := FND_API.G_RET_STS_SUCCESS;
685
686 DELETE pa_bill_workbench_temp;
687 INSERT
688 INTO pa_bill_workbench_temp(
689 PROJECT_ID ,
690 PC_FUNDING ,
691 PC_REV_ACCRUED ,
692 PC_REV_BACKLOG ,
693 PC_WRITEOFF ,
694 PC_UBR ,
695 PC_UER ,
696 PC_INVOICED ,
697 PC_INV_BACKLOG ,
698 PC_PAID ,
699 PC_DUE ,
700 PC_BILLABLE_COST ,
701 PC_UNBILLED_COST ,
702 PC_UNBILLED_EVENTS ,
703 PC_UNBILLED_RETENTION ,
704 PC_UNAPPRO_INVOICES ,
705 PC_TAX ,
706 PC_UBR_APPLICAB_FLAG ,
707 PC_UER_APPLICAB_FLAG ,
708 PC_UNBIL_EVE_APPLICAB_FLAG ,
709 PFC_FUNDING ,
710 PFC_REV_ACCRUED ,
711 PFC_REV_BACKLOG ,
712 PFC_WRITEOFF ,
713 PFC_UBR ,
714 PFC_UER ,
715 PFC_INVOICED ,
716 PFC_INV_BACKLOG ,
717 PFC_PAID ,
718 PFC_DUE ,
719 PFC_BILLABLE_COST ,
720 PFC_UNBILLED_COST ,
721 PFC_UNBILLED_EVENTS ,
722 PFC_UNBILLED_RETENTION ,
723 PFC_UNAPPRO_INVOICES ,
724 PFC_TAX ,
725 PFC_UNBIL_EVE_APPLICAB_FLAG ,
726 NEXT_INVOICE_DATE ,
727 Multi_Customer_Flag
728 )
729 VALUES(
730 p_project_id ,
731 p_proj_funding_amt ,
732 p_proj_rev_accured ,
733 p_proj_rev_backlog ,
734 p_proj_rev_writeoff ,
735 p_proj_ubr ,
736 p_proj_uer ,
737 p_proj_inv_invoiced ,
738 p_proj_inv_backlog ,
739 p_proj_inv_paid ,
740 p_proj_inv_due ,
741 p_proj_billable_cost ,
742 p_proj_unbilled_cost ,
743 p_proj_unbilled_events ,
744 p_proj_unbilled_retn ,
745 p_proj_unapproved_inv_amt ,
746 p_proj_tax ,
747 p_pc_ubr_applicab_flag ,
748 p_pc_uer_applicab_flag ,
749 p_pc_unbil_eve_applicab_flag ,
750 p_projfunc_funding_amt ,
751 p_projfunc_rev_accured ,
752 p_projfunc_rev_backlog ,
753 p_projfunc_rev_writeoff ,
754 p_projfunc_ubr ,
755 p_projfunc_uer ,
756 p_projfunc_inv_invoiced ,
757 p_projfunc_inv_backlog ,
758 p_projfunc_inv_paid ,
759 p_projfunc_inv_due ,
760 p_projfunc_billable_cost ,
761 p_projfunc_unbilled_cost ,
762 p_projfunc_unbilled_events ,
763 p_projfunc_unbilled_retn ,
764 p_projfunc_unapprov_inv_amt ,
765 p_projfunc_tax ,
766 p_pfc_unbil_eve_applicab_flag ,
767 p_next_invoice_date ,
768 p_multi_customer_flag
769 );
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
774 ,p_procedure_name => 'Populat_Bill_Workbench_Data' );
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
776 x_msg_count := 1;
777 x_msg_data := SUBSTR(SQLERRM,1,30);
778 RAISE;
779 END Populat_Bill_Workbench_Data;
780
781
782 -- This procedure will populate the temp table with all the input paramters for Summary by customer region of invoicing
783 -- Input parameters
784 -- Parameters Type Required Description
785 -- p_project_id NUMBER YES The identifier of the project
786 -- p_inv_filter VARCHAR2 YES Filter to filter invoices based on the user inputs
787 --
788 -- Out parameters
789 --
790
791 /* Added 10 parameter after p_inv_filter for search region i.e. bug 3618704 */
792
793 PROCEDURE Populat_Inv_Summ_by_Cust_RN (
794 p_project_id IN NUMBER,
795 p_inv_filter IN VARCHAR2,
796 p_search_flag IN VARCHAR2,
797 p_agreement_id IN NUMBER ,
798 p_draft_num IN NUMBER,
799 p_ar_number IN VARCHAR2 ,
800 p_creation_frm_date IN DATE ,
801 p_creation_to_date IN DATE ,
802 p_invoice_frm_date IN DATE ,
803 p_invoice_to_date IN DATE ,
804 p_gl_frm_date IN DATE ,
805 p_gl_to_date IN DATE ,
806 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
807 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
808 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
809 )
810 IS
811 BEGIN
812 x_return_status := FND_API.G_RET_STS_SUCCESS;
813
814
815 DELETE pa_bill_wrkbench_inv_temp;
816
817
818 /* Added search flag filter if for search region */
819
820 IF (NVL(UPPER(p_search_flag),'N') = 'N') THEN
821
822 INSERT
823 INTO pa_bill_wrkbench_inv_temp(
824 PROJECT_ID
825 ,AGREEMENT_ID
826 ,CUSTOMER_ID
827 ,CUSTOMER_NAME
828 ,PC_FUNDING
829 ,PC_INVOICED
830 ,PC_DUE_ACCEPTED
831 ,PC_DUE_PENDING
832 ,PC_TAX
833 ,PC_TAX_DUE
834 ,PFC_FUNDING
835 ,PFC_INVOICED
836 ,PFC_DUE_ACCEPTED
837 ,PFC_DUE_PENDING
838 ,PFC_TAX
839 ,PFC_TAX_DUE
840 )
841 SELECT
842 spf.project_id project_id
843 ,spf.agreement_id
844 -- ,ra.customer_id customer_id
845 -- ,ra.customer_name||' ('||ra.customer_number||')' Customer
846 ,cust_acct.cust_account_id customer_id
847 ,substrb(party.party_name,1,50)||' ('||cust_acct.account_number||')' Customer
848 ,SUM(NVL(spf.project_baselined_amount,0)) pc_Baselined
849 ,NULL pc_invoiced
850 ,NULL pc_due_accepted
851 ,NULL pc_due_pending
852 ,NULL pc_tax
853 ,NULL pc_tax_due
854 ,SUM(NVL(spf.projfunc_baselined_amount,0)) pfc_Baselined
855 ,NULL pfc_Invoiced
856 ,NULL pfc_due_accepted
857 ,NULL pfc_due_pending
858 ,NULL pfc_tax
859 ,NULL pfc_tax_due
860 FROM
861 -- ra_customers ra
862 hz_parties party
863 , hz_cust_accounts cust_acct
864 , pa_agreements_all agr
865 , pa_project_customers pc
866 , pa_summary_project_fundings spf
867 -- WHERE ra.customer_id = agr.customer_id
868 WHERE cust_acct.cust_account_id= agr.customer_id
869 AND cust_acct.party_id = party.party_id
870 AND agr.customer_id = pc.customer_id
871 AND agr.agreement_id = spf.agreement_id
872 AND spf.project_id = pc.project_id
873 AND spf.project_id = p_project_id
874 GROUP BY /*ra.customer_name,ra.customer_id,ra.customer_number*/
875 substrb(party.party_name,1,50),cust_acct.account_number,cust_acct.cust_account_id,spf.project_id,spf.agreement_id;
876
877
878 /**
879 * Updating Project invoiced amount and project functional invoice amount
880 **/
881
882 UPDATE pa_bill_wrkbench_inv_temp pbw
883 SET (pbw.pc_invoiced,pbw.pfc_invoiced ) =
884 (SELECT
885 SUM(NVL(dii.project_bill_amount,0)) projinv_amt
886 ,SUM(NVL(dii.projfunc_bill_amount,0)) projfuncinv_amt
887 FROM pa_draft_invoices_all di , pa_draft_invoice_items dii , pa_agreements_all agr
888 WHERE dii.draft_invoice_num = di.draft_invoice_num
889 AND dii.project_id = di.project_id
890 AND agr.customer_id = di.customer_id
891 AND agr.agreement_id = di.agreement_id
892 AND di.customer_id = pbw.customer_id
893 AND di.agreement_id = pbw.agreement_id
894 AND di.project_id = pbw.project_id
895 AND (
896 ( 'INV_ALL' = p_inv_filter)
897 OR ( 'INV_APPRO' = p_inv_filter AND di.approved_by_person_id IS NOT NULL
898 AND di.released_date IS NULL)
899 OR ( 'INV_CREDITS' = p_inv_filter AND di.draft_invoice_num_credited IS NOT NULL )
900 OR ( 'INV_RETN_BILL_INV' = p_inv_filter AND NVL(di.retention_invoice_flag,'N') = 'Y' )
901 OR ( 'INV_UNAPPRO' = p_inv_filter AND di.approved_by_person_id IS NULL )
902 OR ( 'INV_RELEASE' = p_inv_filter AND di.transfer_status_code = 'P'
903 AND di.released_date IS NOT NULL )
904 OR ( 'INV_ACCEPT' = p_inv_filter AND di.transfer_status_code = 'A'
905 AND NVL(di.generation_error_flag,'N') <> 'Y' )
906 OR ( 'INV_REJECT' = p_inv_filter AND (di.transfer_status_code = 'X'
907 OR di.transfer_status_code = 'R')
908 AND NVL(di.generation_error_flag,'N') <> 'Y' )
909 OR ( 'INV_ERROR' = p_inv_filter AND NVL(di.generation_error_flag,'N') = 'Y' )
910 )
911 GROUP BY di.project_id,di.customer_id,di.agreement_id);
912
913
914
915 /**
916 * Updating Project due amount (accepted) and project functional due amount (accepted)
917 * Project tax amount, tax due amount and project functional tax amount tax due amount
918 **/
919
920 UPDATE pa_bill_wrkbench_inv_temp pbw
921 SET (pbw.pc_due_accepted,pbw.pfc_due_accepted, pbw.pc_tax, pbw.pfc_tax, pbw.pc_tax_due, pbw.pfc_tax_due ) =
922 ( SELECT
923 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
924 ar.amount_line_items_remaining) due_accepted_pc,
925 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
926 ar.amount_line_items_remaining) due_accepted_pfc,
927 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
928 ar.tax_original) tax_pc,
929 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
930 ar.tax_original) tax_pfc,
931 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
932 ar.tax_remaining) tax_due_pc,
933 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
934 ar.tax_remaining) tax_due_pfc
935 FROM ar_payment_schedules_all ar,
936 (SELECT di.project_id,
937 di.customer_id,
938 di.agreement_id,
939 di.system_reference system_reference,
940 SUM(dii.project_bill_amount) pc_inv_amt,
941 SUM(dii.projfunc_bill_amount) pfc_inv_amt
942 FROM pa_draft_invoices_all di,
943 pa_draft_invoice_items dii,
944 PA_BILL_WRKBENCH_INV_TEMP pbwi
945 WHERE di.project_id = dii.project_id
946 AND di.draft_invoice_num = dii.draft_invoice_num
947 AND di.transfer_status_code = 'A'
948 AND di.customer_id = pbwi.customer_id
949 AND di.agreement_id = pbwi.agreement_id
950 AND di.project_id = pbwi.project_id
951 AND di.system_reference IS NOT NULL
952 AND (
953 ( 'INV_ALL' = p_inv_filter)
954 OR ( 'INV_APPRO' = p_inv_filter
955 AND di.approved_by_person_id IS NOT NULL
956 AND di.released_date IS NULL)
957 OR ( 'INV_CREDITS' = p_inv_filter
958 AND di.draft_invoice_num_credited IS NOT NULL )
959 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
960 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
961 OR ( 'INV_UNAPPRO' = p_inv_filter
962 AND di.approved_by_person_id IS NULL )
963 OR ( 'INV_RELEASE' = p_inv_filter
964 AND di.transfer_status_code = 'P'
965 AND di.released_date IS NOT NULL )
966 OR ( 'INV_ACCEPT' = p_inv_filter
967 AND di.transfer_status_code = 'A'
968 AND NVL(di.generation_error_flag,'N') <> 'Y' )
969 OR ( 'INV_REJECT' = p_inv_filter
970 AND (di.transfer_status_code = 'X'
971 OR di.transfer_status_code = 'R')
972 AND NVL(di.generation_error_flag,'N') <> 'Y' )
973 OR ( 'INV_ERROR' = p_inv_filter
974 AND NVL(di.generation_error_flag,'N') = 'Y' )
975 )
976 GROUP BY di.project_id,di.customer_id,di.agreement_id,
977 di.system_reference) pa_inv
978 WHERE pa_inv.project_id = pbw.project_id
979 AND pa_inv.customer_id = pbw.customer_id
980 AND pa_inv.agreement_id = pbw.agreement_id
981 AND pa_inv.system_reference = ar.customer_trx_id
982 AND ar.amount_line_items_original <> 0 /* Condition added for bug 5230465 */
983 GROUP BY pa_inv.project_id, pa_inv.customer_id,pa_inv.agreement_id);
984
985
986 UPDATE pa_bill_wrkbench_inv_temp pbw
987 SET (pbw.pc_due_pending,pbw.pfc_due_pending ) =
988 ( SELECT
989 SUM(dii.project_bill_amount) ,
990 SUM(dii.projfunc_bill_amount)
991 FROM pa_draft_invoices_all di,
992 pa_draft_invoice_items dii
993 WHERE di.project_id = dii.project_id
994 AND di.draft_invoice_num = dii.draft_invoice_num
995 AND di.transfer_status_code <> 'A'
996 AND di.customer_id = pbw.customer_id
997 AND di.agreement_id = pbw.agreement_id
998 AND di.project_id = pbw.project_id
999 AND di.system_reference IS NULL
1000 AND (
1001 ( 'INV_ALL' = p_inv_filter)
1002 OR ( 'INV_APPRO' = p_inv_filter
1003 AND di.approved_by_person_id IS NOT NULL
1004 AND di.released_date IS NULL)
1005 OR ( 'INV_CREDITS' = p_inv_filter
1006 AND di.draft_invoice_num_credited IS NOT NULL )
1007 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1008 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1009 OR ( 'INV_UNAPPRO' = p_inv_filter
1010 AND di.approved_by_person_id IS NULL )
1011 OR ( 'INV_RELEASE' = p_inv_filter
1012 AND di.transfer_status_code = 'P'
1013 AND di.released_date IS NOT NULL )
1014 OR ( 'INV_ACCEPT' = p_inv_filter
1015 AND di.transfer_status_code = 'A'
1016 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1017 OR ( 'INV_REJECT' = p_inv_filter
1018 AND (di.transfer_status_code = 'X'
1019 OR di.transfer_status_code = 'R')
1020 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1021 OR ( 'INV_ERROR' = p_inv_filter
1022 AND NVL(di.generation_error_flag,'N') = 'Y' )
1023 )
1024 GROUP BY di.project_id,di.customer_id,di.agreement_id);
1025
1026 ELSIF (NVL(UPPER(p_search_flag),'N') = 'Y') THEN
1027
1028 INSERT
1029 INTO pa_bill_wrkbench_inv_temp(
1030 PROJECT_ID
1031 ,AGREEMENT_ID
1032 ,CUSTOMER_ID
1033 ,CUSTOMER_NAME
1034 ,PC_FUNDING
1035 ,PC_INVOICED
1036 ,PC_DUE_ACCEPTED
1037 ,PC_DUE_PENDING
1038 ,PC_TAX
1039 ,PC_TAX_DUE
1040 ,PFC_FUNDING
1041 ,PFC_INVOICED
1042 ,PFC_DUE_ACCEPTED
1043 ,PFC_DUE_PENDING
1044 ,PFC_TAX
1045 ,PFC_TAX_DUE
1046 )
1047 SELECT
1048 spf.project_id project_id
1049 ,spf.agreement_id
1050 -- ,ra.customer_id customer_id
1051 -- ,ra.customer_name||' ('||ra.customer_number||')' Customer
1052 ,cust_acct.cust_account_id customer_id
1053 ,substrb(party.party_name,1,50)||' ('||cust_acct.account_number||')' Customer
1054 ,SUM(NVL(spf.project_baselined_amount,0)) pc_Baselined
1055 ,NULL pc_invoiced
1056 ,NULL pc_due_accepted
1057 ,NULL pc_due_pending
1058 ,NULL pc_tax
1059 ,NULL pc_tax_due
1060 ,SUM(NVL(spf.projfunc_baselined_amount,0)) pfc_Baselined
1061 ,NULL pfc_Invoiced
1062 ,NULL pfc_due_accepted
1063 ,NULL pfc_due_pending
1064 ,NULL pfc_tax
1065 ,NULL pfc_tax_due
1066 FROM
1067 -- ra_customers ra
1068 hz_parties party
1069 , hz_cust_accounts cust_acct
1070 , pa_agreements_all agr
1071 , pa_project_customers pc
1072 , pa_summary_project_fundings spf
1073 , pa_draft_invoices_all di
1074 -- WHERE ra.customer_id = agr.customer_id
1075 WHERE cust_acct.cust_account_id= agr.customer_id
1076 AND cust_acct.party_id = party.party_id
1077 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1078 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1079 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1080 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1081 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1082 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1083 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1084 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1085 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1086 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1087 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1088 AND di.agreement_id = agr.agreement_id
1089 AND di.customer_id = agr.customer_id
1090 AND di.project_id = spf.project_id
1091 AND agr.customer_id = pc.customer_id
1092 AND agr.agreement_id = spf.agreement_id
1093 AND spf.project_id = pc.project_id
1094 AND spf.agreement_id = NVL(p_agreement_id,spf.agreement_id)
1095 AND spf.project_id = p_project_id
1096 GROUP BY /*ra.customer_name,ra.customer_id,ra.customer_number*/
1097 substrb(party.party_name,1,50),cust_acct.account_number,cust_acct.cust_account_id,spf.project_id,spf.agreement_id;
1098 /**
1099 * Updating Project invoiced amount and project functional invoice amount
1100 **/
1101
1102 UPDATE pa_bill_wrkbench_inv_temp pbw
1103 SET (pbw.pc_invoiced,pbw.pfc_invoiced ) =
1104 (SELECT
1105 SUM(NVL(dii.project_bill_amount,0)) projinv_amt
1106 ,SUM(NVL(dii.projfunc_bill_amount,0)) projfuncinv_amt
1107 FROM pa_draft_invoices_all di , pa_draft_invoice_items dii , pa_agreements_all agr
1108 WHERE dii.draft_invoice_num = di.draft_invoice_num
1109 AND dii.project_id = di.project_id
1110 AND agr.customer_id = di.customer_id
1111 AND agr.agreement_id = di.agreement_id
1112 AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
1113 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1114 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1115 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1116 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1117 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1118 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1119 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1120 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1121 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1122 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1123 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1124 AND di.customer_id = pbw.customer_id
1125 AND di.agreement_id = pbw.agreement_id
1126 AND di.project_id = pbw.project_id
1127 AND (
1128 ( 'INV_ALL' = p_inv_filter)
1129 OR ( 'INV_APPRO' = p_inv_filter AND di.approved_by_person_id IS NOT NULL
1130 AND di.released_date IS NULL)
1131 OR ( 'INV_CREDITS' = p_inv_filter AND di.draft_invoice_num_credited IS NOT NULL )
1132 OR ( 'INV_RETN_BILL_INV' = p_inv_filter AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1133 OR ( 'INV_UNAPPRO' = p_inv_filter AND di.approved_by_person_id IS NULL )
1134 OR ( 'INV_RELEASE' = p_inv_filter AND di.transfer_status_code = 'P'
1135 AND di.released_date IS NOT NULL )
1136 OR ( 'INV_ACCEPT' = p_inv_filter AND di.transfer_status_code = 'A'
1137 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1138 OR ( 'INV_REJECT' = p_inv_filter AND (di.transfer_status_code = 'X'
1139 OR di.transfer_status_code = 'R')
1140 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1141 OR ( 'INV_ERROR' = p_inv_filter AND NVL(di.generation_error_flag,'N') = 'Y' )
1142 )
1143 GROUP BY di.project_id,di.customer_id,di.agreement_id);
1144
1145
1146 /**
1147 * Updating Project due amount (accepted) and project functional due amount (accepted)
1148 * Project tax amount, tax due amount and project functional tax amount tax due amount
1149 **/
1150
1151 UPDATE pa_bill_wrkbench_inv_temp pbw
1152 SET (pbw.pc_due_accepted,pbw.pfc_due_accepted, pbw.pc_tax, pbw.pfc_tax, pbw.pc_tax_due, pbw.pfc_tax_due ) =
1153 ( SELECT
1154 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
1155 ar.amount_line_items_remaining) due_accepted_pc,
1156 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
1157 ar.amount_line_items_remaining) due_accepted_pfc,
1158 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
1159 ar.tax_original) tax_pc,
1160 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
1161 ar.tax_original) tax_pfc,
1162 SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
1163 ar.tax_remaining) tax_due_pc,
1164 SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
1165 ar.tax_remaining) tax_due_pfc
1166 FROM ar_payment_schedules_all ar,
1167 (SELECT di.project_id,
1168 di.customer_id,
1169 di.agreement_id,
1170 di.system_reference system_reference,
1171 SUM(dii.project_bill_amount) pc_inv_amt,
1172 SUM(dii.projfunc_bill_amount) pfc_inv_amt
1173 FROM pa_draft_invoices_all di,
1174 pa_draft_invoice_items dii,
1175 PA_BILL_WRKBENCH_INV_TEMP pbwi
1176 WHERE di.project_id = dii.project_id
1177 AND di.draft_invoice_num = dii.draft_invoice_num
1178 AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
1179 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1180 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1181 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1182 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1183 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1184 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1185 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1186 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1187 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1188 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1189 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1190 AND di.transfer_status_code = 'A'
1191 AND di.customer_id = pbwi.customer_id
1192 AND di.agreement_id = pbwi.agreement_id
1193 AND di.project_id = pbwi.project_id
1194 AND di.system_reference IS NOT NULL
1195 AND (
1196 ( 'INV_ALL' = p_inv_filter)
1197 OR ( 'INV_APPRO' = p_inv_filter
1198 AND di.approved_by_person_id IS NOT NULL
1199 AND di.released_date IS NULL)
1200 OR ( 'INV_CREDITS' = p_inv_filter
1201 AND di.draft_invoice_num_credited IS NOT NULL )
1202 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1203 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1204 OR ( 'INV_UNAPPRO' = p_inv_filter
1205 AND di.approved_by_person_id IS NULL )
1206 OR ( 'INV_RELEASE' = p_inv_filter
1207 AND di.transfer_status_code = 'P'
1208 AND di.released_date IS NOT NULL )
1209 OR ( 'INV_ACCEPT' = p_inv_filter
1210 AND di.transfer_status_code = 'A'
1211 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1212 OR ( 'INV_REJECT' = p_inv_filter
1213 AND (di.transfer_status_code = 'X'
1214 OR di.transfer_status_code = 'R')
1215 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1216 OR ( 'INV_ERROR' = p_inv_filter
1217 AND NVL(di.generation_error_flag,'N') = 'Y' )
1218 )
1219 GROUP BY di.project_id,di.customer_id,di.agreement_id,
1220 di.system_reference) pa_inv
1221 WHERE pa_inv.project_id = pbw.project_id
1222 AND pa_inv.customer_id = pbw.customer_id
1223 AND pa_inv.agreement_id = pbw.agreement_id
1224 AND pa_inv.system_reference = ar.customer_trx_id
1225 AND ar.amount_line_items_original <> 0 /* Condition added for bug 5230465 */
1226 GROUP BY pa_inv.project_id, pa_inv.customer_id,pa_inv.agreement_id);
1227
1228
1229 UPDATE pa_bill_wrkbench_inv_temp pbw
1230 SET (pbw.pc_due_pending,pbw.pfc_due_pending ) =
1231 ( SELECT
1232 SUM(dii.project_bill_amount) ,
1233 SUM(dii.projfunc_bill_amount)
1234 FROM pa_draft_invoices_all di,
1235 pa_draft_invoice_items dii
1236 WHERE di.project_id = dii.project_id
1237 AND di.draft_invoice_num = dii.draft_invoice_num
1238 AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
1239 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1240 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1241 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1242 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1243 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1244 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1245 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1246 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1247 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1248 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1249 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1250 AND di.transfer_status_code <> 'A'
1251 AND di.customer_id = pbw.customer_id
1252 AND di.agreement_id = pbw.agreement_id
1253 AND di.project_id = pbw.project_id
1254 AND di.system_reference IS NULL
1255 AND (
1256 ( 'INV_ALL' = p_inv_filter)
1257 OR ( 'INV_APPRO' = p_inv_filter
1258 AND di.approved_by_person_id IS NOT NULL
1259 AND di.released_date IS NULL)
1260 OR ( 'INV_CREDITS' = p_inv_filter
1261 AND di.draft_invoice_num_credited IS NOT NULL )
1262 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1263 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1264 OR ( 'INV_UNAPPRO' = p_inv_filter
1265 AND di.approved_by_person_id IS NULL )
1266 OR ( 'INV_RELEASE' = p_inv_filter
1267 AND di.transfer_status_code = 'P'
1268 AND di.released_date IS NOT NULL )
1269 OR ( 'INV_ACCEPT' = p_inv_filter
1270 AND di.transfer_status_code = 'A'
1271 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1272 OR ( 'INV_REJECT' = p_inv_filter
1273 AND (di.transfer_status_code = 'X'
1274 OR di.transfer_status_code = 'R')
1275 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1276 OR ( 'INV_ERROR' = p_inv_filter
1277 AND NVL(di.generation_error_flag,'N') = 'Y' )
1278 )
1279 GROUP BY di.project_id,di.customer_id,di.agreement_id);
1280
1281 END IF;
1282
1283 /**
1284 * If there is no rows in this table then inserting null row so that
1285 * temp table should not return null pointer exception
1286 **/
1287
1288 INSERT
1289 INTO pa_bill_wrkbench_inv_temp(
1290 PROJECT_ID
1291 ,AGREEMENT_ID
1292 ,CUSTOMER_ID
1293 ,CUSTOMER_NAME
1294 ,PC_FUNDING
1295 ,PC_INVOICED
1296 ,PC_DUE_ACCEPTED
1297 ,PC_DUE_PENDING
1298 ,PC_TAX
1299 ,PC_TAX_DUE
1300 ,PFC_FUNDING
1301 ,PFC_INVOICED
1302 ,PFC_DUE_ACCEPTED
1303 ,PFC_DUE_PENDING
1304 ,PFC_TAX
1305 ,PFC_TAX_DUE)
1306 SELECT
1307 p_project_id
1308 ,-1
1309 ,-1
1310 ,NULL
1311 ,NULL
1312 ,NULL
1313 ,NULL
1314 ,NULL
1315 ,NULL
1316 ,NULL
1317 ,NULL
1318 ,NULL
1319 ,NULL
1320 ,NULL
1321 ,NULL
1322 ,NULL
1323 FROM
1324 dual
1325 WHERE NOT EXISTS( SELECT 'x'
1326 FROM pa_bill_wrkbench_inv_temp a
1327 WHERE a.project_id = p_project_id);
1328
1329
1330
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
1334 ,p_procedure_name => 'Populat_Inv_Summ_by_Cust_RN' );
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1336 x_msg_count := 1;
1337 x_msg_data := SUBSTR(SQLERRM,1,30);
1338 RAISE;
1339 END Populat_Inv_Summ_by_Cust_RN;
1340
1341
1342
1343
1344
1345 --
1346 -- Procedure : Get_Due_Amount
1347 -- Purpose : This procedure will get all the parameters for Billing Region for the given project.
1348 -- Parameters :
1349 --
1350
1351 FUNCTION Get_Due_Amount (
1352 p_project_id IN NUMBER DEFAULT NULL,
1353 p_draft_inv_num IN NUMBER DEFAULT NULL,
1354 p_system_reference IN NUMBER ,
1355 p_transfer_status_code IN VARCHAR2 ,
1356 p_calling_mode IN VARCHAR2 ,
1357 p_inv_amount IN NUMBER DEFAULT NULL,
1358 p_proj_bill_amount IN NUMBER DEFAULT NULL,
1359 p_projfunc_bill_amount IN NUMBER DEFAULT NULL
1360 ) RETURN NUMBER
1361 IS
1362
1363 BEGIN
1364
1365 IF (p_transfer_status_code = 'A') THEN
1366
1367 IF ( NVL(G_system_reference,-99) <> p_system_reference ) THEN
1368 BEGIN
1369 SELECT
1370 SUM(ar.amount_line_items_remaining + ar.tax_remaining)
1371 INTO G_ar_amount
1372 FROM ar_payment_schedules_all ar
1373 WHERE p_system_reference IS NOT NULL
1374 AND p_transfer_status_code = 'A'
1375 AND ar.customer_trx_id = p_system_reference;
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 RAISE;
1379 END;
1380
1381 G_system_reference := p_system_reference;
1382 END IF;
1383
1384 IF ( p_calling_mode = 'TRANS' ) THEN
1385 RETURN (G_ar_amount);
1386 ELSIF (p_inv_amount = 0 ) THEN /* condition added */
1387 RETURN (0); /* to fix bug 5230465 */
1388 ELSIF ( p_calling_mode = 'PFC' ) THEN
1389 RETURN ( (p_projfunc_bill_amount/p_inv_amount) * G_ar_amount);
1390 ELSIF ( p_calling_mode = 'PC' ) THEN
1391 RETURN ( (p_proj_bill_amount/p_inv_amount) * G_ar_amount);
1392 END IF;
1393
1394 END IF;
1395
1396
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
1400 ,p_procedure_name => 'Get_Due_Amount' );
1401 RAISE;
1402
1403 END Get_Due_Amount;
1404
1405
1406 FUNCTION Get_tax_Amount (
1407 p_project_id IN NUMBER DEFAULT NULL,
1408 p_draft_inv_num IN NUMBER DEFAULT NULL,
1409 p_system_reference IN NUMBER ,
1410 p_transfer_status_code IN VARCHAR2 ,
1411 p_calling_mode IN VARCHAR2 ,
1412 p_inv_amount IN NUMBER DEFAULT NULL,
1413 p_proj_bill_amount IN NUMBER DEFAULT NULL,
1414 p_projfunc_bill_amount IN NUMBER DEFAULT NULL
1415 ) RETURN NUMBER
1416 IS
1417
1418 l_tax_amount number;
1419
1420 BEGIN
1421
1422 IF (p_transfer_status_code = 'A') THEN
1423
1424 BEGIN
1425 SELECT
1426 SUM(ar.tax_original)
1427 INTO l_tax_amount
1428 FROM ar_payment_schedules_all ar
1429 WHERE p_system_reference IS NOT NULL
1430 AND p_transfer_status_code = 'A'
1431 AND ar.customer_trx_id = p_system_reference;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 RAISE;
1435 END;
1436
1437
1438 IF ( p_calling_mode = 'TRANS' ) THEN
1439 RETURN (l_tax_amount);
1440 ELSIF (p_inv_amount = 0 ) THEN /* condition added */
1441 RETURN (0); /* to fix bug 5230465 */
1442 ELSIF ( p_calling_mode = 'PFC' ) THEN
1443 RETURN ( (p_projfunc_bill_amount/p_inv_amount) * l_tax_amount);
1444 ELSIF ( p_calling_mode = 'PC' ) THEN
1445 RETURN ( (p_proj_bill_amount/p_inv_amount) * l_tax_amount);
1446 END IF;
1447
1448 END IF;
1449
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
1454 ,p_procedure_name => 'Get_Tax_Amount' );
1455 RAISE;
1456
1457 END Get_tax_Amount;
1458
1459 -- Added for bug 4932118
1460 -- Procedure : PROJECT_UBR_UER_CONVERT
1461 -- Purpose : This procedure will convert UBR/UER amounts
1462 -- in Projfunc curr to project curr for MCB
1463 -- projects while revtrans curr is different from
1464 -- Projfunc currency.
1465 -- Parameters : P_PROJECT_ID - Input Project Id
1466 -- X_PROJECT_CURR_UBR - UBR amount in Project currency
1467 -- X_PROJECT_CURR_UER - UER amount in Project currency
1468 -- X_RETURN_STATUS_ - Return status of the API
1469 -- X_MSG_COUNT - Count of error messages
1470 -- X_MSG_DATA - Actual message data
1471 --
1472
1473 Procedure PROJECT_UBR_UER_CONVERT (
1474 P_PROJECT_ID IN NUMBER,
1475 X_PROJECT_CURR_UBR OUT NOCOPY NUMBER,
1476 X_PROJECT_CURR_UER OUT NOCOPY NUMBER,
1477 X_RETURN_STATUS OUT NOCOPY VARCHAR,
1478 X_MSG_COUNT OUT NOCOPY NUMBER,
1479 X_MSG_DATA OUT NOCOPY VARCHAR )
1480 Is
1481 l_mcb_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp ;
1482 l_prj_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1483 l_projfunc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1484 l_prj_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1485 l_prj_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1486 l_prj_exch_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1487 l_ubr_dr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1488 l_uer_cr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1489 l_project_ubr_dr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1490 l_project_uer_cr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1491 l_conversion_between varchar(30);
1492 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp ;
1493 l_cache_flag varchar(1);
1494 l_project_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1495 l_project_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1496 l_prj_status_tab PA_PLSQL_DATATYPES.Char30TabTyp ;
1497 Begin
1498 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1499
1500 --FND_MSG_PUB.initialize;
1501
1502 SELECT
1503 MULTI_CURRENCY_BILLING_FLAG,
1504 PROJFUNC_CURRENCY_CODE,
1505 PROJECT_CURRENCY_CODE,
1506 PROJECT_BIL_RATE_TYPE,
1507 PROJECT_BIL_RATE_DATE,
1508 PROJECT_BIL_EXCHANGE_RATE,
1509 UNBILLED_RECEIVABLE_DR,
1510 UNEARNED_REVENUE_CR
1511 INTO
1512 l_mcb_flag_tab(1),
1513 l_projfunc_currency_code_tab(1),
1514 l_prj_currency_code_tab(1),
1515 l_prj_rate_type_tab(1),
1516 l_prj_rate_date_tab(1),
1517 l_prj_exch_rate_tab(1),
1518 l_ubr_dr_tab(1),
1519 l_uer_cr_tab(1)
1520 FROM PA_PROJECTS_ALL
1521 WHERE PROJECT_ID = P_PROJECT_ID;
1522 l_user_validate_flag_tab(1) := 'N';
1523 l_prj_status_tab(1) := 'N';
1524
1525 IF l_mcb_flag_tab(1) = 'Y' AND l_projfunc_currency_code_tab(1) <> l_prj_currency_code_tab(1) Then
1526 /* Call PA_MULTI_CURRENCY_BILLING.convert_amount_bulk to convert from ubr/uer
1527 amount in project functional currency to project currency. */
1528 l_conversion_between := 'PFC_PC';
1529 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1530 p_from_currency_tab => l_projfunc_currency_code_tab,
1531 p_to_currency_tab => l_prj_currency_code_tab,
1532 p_conversion_date_tab => L_prj_rate_date_tab,
1533 p_conversion_type_tab => L_prj_rate_type_tab,
1534 p_amount_tab => l_ubr_dr_tab,
1535 p_user_validate_flag_tab => l_user_validate_flag_tab,
1536 p_converted_amount_tab => l_project_ubr_dr_tab,
1537 p_denominator_tab => l_project_denominator_tab,
1538 p_numerator_tab => l_project_numerator_tab,
1539 p_rate_tab => l_prj_exch_rate_tab,
1540 p_conversion_between => l_conversion_between,
1541 p_cache_flag => l_cache_flag,
1542 x_status_tab => l_prj_status_tab
1543 );
1544 /* Copy if the API call is successful */
1545 IF l_prj_status_tab(1) = 'N' Then
1546 X_PROJECT_CURR_UBR := l_project_ubr_dr_tab(1);
1547 ELSE
1548 x_msg_count := 1;
1549 X_MSG_DATA := l_prj_status_tab(1);
1550 End If;
1551
1552 l_conversion_between := 'PFC_PC';
1553 l_prj_status_tab(1) := 'N';
1554 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1555 p_from_currency_tab => l_projfunc_currency_code_tab,
1556 p_to_currency_tab => l_prj_currency_code_tab,
1557 p_conversion_date_tab => L_prj_rate_date_tab,
1558 p_conversion_type_tab => L_prj_rate_type_tab,
1559 p_amount_tab => l_uer_cr_tab,
1560 p_user_validate_flag_tab => l_user_validate_flag_tab,
1561 p_converted_amount_tab => l_project_uer_cr_tab,
1562 p_denominator_tab => l_project_denominator_tab,
1563 p_numerator_tab => l_project_numerator_tab,
1564 p_rate_tab => l_prj_exch_rate_tab,
1565 p_conversion_between => l_conversion_between,
1566 p_cache_flag => l_cache_flag,
1567 x_status_tab => l_prj_status_tab
1568 );
1569 /* Copy if the API call is successful */
1570 IF l_prj_status_tab(1) = 'N' Then
1571 X_PROJECT_CURR_UER := l_project_uer_cr_tab(1);
1572 ELSE
1573 x_msg_count := 1;
1574 X_MSG_DATA := l_prj_status_tab(1);
1575 End If;
1576
1577 Else
1578 X_Project_CURR_UBR := l_ubr_dr_tab(1);
1579 X_Project_CURR_UER := l_uer_cr_tab(1);
1580 End If;
1581 /* Handle Exceptions */
1582 EXCEPTION
1583 WHEN OTHERS THEN
1584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1585 x_msg_count := 1;
1586 x_msg_data := SQLERRM;
1587
1588 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_BILLING_WORKBENCH_BILL_PKG'
1589 ,p_procedure_name => 'PROJECT_UBR_UER_CONVERT');
1590 Raise;
1591
1592 End PROJECT_UBR_UER_CONVERT;
1593 END PA_BILLING_WORKBENCH_BILL_PKG;
1594