[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.14.12020000.2 2012/08/08 09:43:11 rvadali ship $ */
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 /* Added NVL,additional condition pa_ar.line_amt_orig(+) <> 0)and outer joins for bug 7394622 */
300 /* added group function SUM to Ar amounts,modifed query to calculate billing amounts for bug 7628408 */
301 /* Modified the below query completely for the Bug 8249757 - Start *//* Commented the below query for Bug 10125590
302 SELECT
303 pa_inv.project_id,
304 SUM(pa_inv.pfc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.line_amt_remn,1)) inv_due_pfc,
305 SUM(pa_inv.pfc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.line_amt_orig,1)) inv_orig_pfc,
306 SUM(pa_inv.pfc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.tax_orig,0)) tax_orig_pfc,
307 SUM(pa_inv.pfc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.tax_remn,0)) tax_due_pfc,
308 SUM(pa_inv.pc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.line_amt_remn,1)) inv_due_pc,
309 SUM(pa_inv.pc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.line_amt_orig,1)) inv_orig_pc,
310 SUM(pa_inv.pc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.tax_orig,0)) tax_orig_pc,
311 SUM(pa_inv.pc_inv_amt / Nvl(pa_ar.line_amt_orig,1) * Nvl(pa_ar.tax_remn,0)) 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
323 (SELECT arps.customer_trx_id, arps.trx_number,
324 Nvl(SUM(arps.amount_line_items_original),1) line_amt_orig,
325 Nvl(SUM(arps.amount_line_items_remaining),1)line_amt_remn,
326 Nvl(SUM(arps.tax_original),0) tax_orig,
327 Nvl(SUM(arps.tax_remaining),0) tax_remn
328 FROM ar_payment_schedules_all arps
329 GROUP BY arps.customer_trx_id, arps.trx_number
330 ) pa_ar,
331 (SELECT di.project_id,
332 di.ra_invoice_number,
333 di.system_reference system_reference,
334 SUM(dii.projfunc_bill_amount) pfc_inv_amt,
335 SUM(dii.project_bill_amount) pc_inv_amt
336 FROM pa_draft_invoices_all di,
337 pa_draft_invoice_items dii
338 WHERE di.project_id = dii.project_id
339 AND di.draft_invoice_num = dii.draft_invoice_num
340 AND di.transfer_status_code = 'A'
341 AND di.system_reference IS NOT NULL
342 GROUP BY di.project_id,di.ra_invoice_number,di.system_reference) pa_inv
343 WHERE pa_inv.project_id = p_project_id
344 AND pa_inv.system_reference = pa_ar.customer_trx_id(+)
345 AND pa_inv.ra_invoice_number = pa_ar.trx_number(+)
346 AND pa_ar.line_amt_orig(+) <> 0
347 GROUP BY pa_inv.project_id;
348 /* Modified the query completely for the Bug 8249757 - End *//* Commented the above query and added below for Bug 10125590 */
349 SELECT
350 pa_inv.project_id,
351 SUM(pa_inv.pfc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.line_amt_remn,1)) inv_due_pfc,
352 SUM(pa_inv.pfc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.line_amt_orig,1)) inv_orig_pfc,
353 SUM(pa_inv.pfc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.tax_orig,0)) tax_orig_pfc,
354 SUM(pa_inv.pfc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.tax_remn,0)) tax_due_pfc,
355 SUM(pa_inv.pc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.line_amt_remn,1)) inv_due_pc,
356 SUM(pa_inv.pc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.line_amt_orig,1)) inv_orig_pc,
357 SUM(pa_inv.pc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.tax_orig,0)) tax_orig_pc,
358 SUM(pa_inv.pc_inv_amt / Nvl(pa_inv.line_amt_orig,1) * Nvl(pa_inv.tax_remn,0)) tax_due_pc
359 INTO
360 l_project_id,
361 l_projfunc_inv_due,
362 l_projfunc_inv_orig,
363 l_projfunc_tax_orig,
364 l_projfunc_tax_due,
365 l_proj_inv_due,
366 l_proj_inv_orig,
367 l_proj_tax_orig,
368 l_proj_tax_due
369 FROM
370 (SELECT di.project_id,
371 di.ra_invoice_number,
372 di.system_reference system_reference,
373 SUM(dii.projfunc_bill_amount) pfc_inv_amt,
374 SUM(dii.project_bill_amount) pc_inv_amt ,
375 Nvl(SUM(arps.amount_line_items_original),1) line_amt_orig,
376 Nvl(SUM(arps.amount_line_items_remaining),1)line_amt_remn,
377 Nvl(SUM(arps.tax_original),0) tax_orig,
378 Nvl(SUM(arps.tax_remaining),0) tax_remn
379 FROM pa_draft_invoices_all di,
380 pa_draft_invoice_items dii ,
381 ar_payment_schedules_all arps
382 WHERE di.project_id = dii.project_id
383 AND di.draft_invoice_num = dii.draft_invoice_num
384 AND di.transfer_status_code = 'A'
385 AND di.system_reference IS NOT NULL
386 AND di.system_reference = arps.customer_trx_id(+)
387 AND di.ra_invoice_number = arps.trx_number(+)
388 GROUP BY di.project_id,di.ra_invoice_number,di.system_reference ) pa_inv
389 WHERE pa_inv.project_id = p_project_id
390 AND pa_inv.line_amt_orig(+) <> 0
391 GROUP BY pa_inv.project_id;
392 /* Added the above query for Bug 10125590 */
393
394 end if;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 NULL;
399
400 END;
401
402 /**
403 * Total PFC due, PC due, PFC paid, and PC paid amounts
404 **/
405
406 l_projfunc_inv_tot_due := NVL(l_projfunc_inv_due_unaccepted,0) + NVL(l_projfunc_inv_due,0) +
407 NVL(l_projfunc_tax_due,0);
408 l_proj_inv_tot_due := NVL(l_proj_inv_due_unaccepted,0) + NVL(l_proj_inv_due,0) +
409 NVL(l_proj_tax_due,0);
410 l_projfunc_inv_tot_paid := NVL(l_projfunc_inv_orig,0) - NVL(l_projfunc_inv_due,0) +
411 NVL(l_projfunc_tax_orig,0) - NVL(l_projfunc_tax_due,0) ;
412 l_proj_inv_tot_paid := NVL(l_proj_inv_orig,0) - NVL(l_proj_inv_due,0) +
413 NVL(l_proj_tax_orig,0) - NVL(l_proj_tax_due,0) ;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 NULL;
418 END;
419
420 -- Selecting column for invoicing status section
421 BEGIN
422 -- Calculating total billable cost and unbilled cost in project functional currency
423 -- and project currency
424 /*
425 SELECT
426 SUM(DECODE(system_linkage_function,'BTC',NVL(project_burdened_cost,0),NVL(project_raw_cost,0))) Proj_billable_cost
427 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0,DECODE(system_linkage_function,'BTC',
428 NVL(project_burdened_cost,0),NVL(project_raw_cost,0)),0),0) ) Proj_unbill_cost
429 , SUM(DECODE(system_linkage_function,'BTC',NVL(burden_cost,0),NVL(raw_cost,0))) Projfunc_billable_cost
430 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0,DECODE(system_linkage_function,'BTC',
431 NVL(burden_cost,0),NVL(raw_cost,0)),0),0) ) Projfunc_unbill_cost
432 */
433 SELECT
434 SUM(NVL(project_burdened_cost,0)) Proj_billable_cost
435 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0, NVL(project_burdened_cost,0) ,0),0) )Proj_unbill_cost
436 , SUM(NVL(burden_cost,0)) Projfunc_billable_cost
437 ,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0, NVL(burden_cost,0),0),0) ) Projfunc_unbill_cost
438 INTO
439 l_proj_billable_cost
440 ,l_proj_unbilled_cost
441 ,l_projfunc_billable_cost
442 ,l_projfunc_unbilled_cost
443 FROM pa_expenditure_items_all
444 WHERE project_id = p_project_id
445 /*AND nvl(billable_flag,'N') = 'Y';*/
446 AND billable_flag = 'Y'; /* Commented above and added for Bug 10125590 */
447
448
449 -- Checking event currency is same as project currency
450 SELECT COUNT(*)
451 INTO l_pc_count
452 FROM pa_events eve
453 WHERE eve.project_id = p_project_id
454 AND eve.bill_trans_currency_code <> eve.project_currency_code;
455
456 IF (l_pc_count < 1 ) THEN
457
458 l_pc_unbil_eve_applicab_flag := 'Y';
459
460 /* Calculating unbilled event amount, which is first selct - second selct */
461 -- Calculating total event amount with partially billed event also if project currency is same
462 -- as bill transaction currency
463 SELECT
464 SUM(NVL(eve.bill_trans_bill_amount,0)) total_pc_unbilled_with_partial
465 INTO
466 l_pc_unbill_eve_amt_with_part
467 FROM pa_events eve
468 WHERE eve.project_id = p_project_id
469 AND nvl(eve.billed_flag,'N') = 'N'
470 AND 1 > ( SELECT COUNT(*)
471 FROM pa_events eve2
472 WHERE eve2.project_id = p_project_id
473 AND eve2.bill_trans_currency_code <> eve2.project_currency_code);
474
475 ELSE
476 l_pc_unbil_eve_applicab_flag := 'N';
477 END IF;
478
479
480 -- Checking event currency is same as project currency
481 SELECT COUNT(*)
482 INTO l_pfc_count
483 FROM pa_events eve
484 WHERE eve.project_id = p_project_id
485 AND eve.bill_trans_currency_code <> eve.projfunc_currency_code;
486
487 IF (l_pfc_count < 1 ) THEN
488
489 l_pfc_unbil_eve_applicab_flag := 'Y';
490 -- Calculating total event amount with partially billed event also if project functional currency is same
491 -- as bill transaction currency
492 SELECT
493 SUM(NVL(eve.bill_trans_bill_amount,0)) tot_pfc_unbilled_with_partial
494 INTO
495 l_pfc_unbill_eve_amt_with_part
496 FROM pa_events eve
497 WHERE eve.project_id = p_project_id
498 AND nvl(eve.billed_flag,'N') = 'N'
499 AND 1 > ( SELECT COUNT(*)
500 FROM pa_events eve2
501 WHERE eve2.project_id = p_project_id
502 AND eve2.bill_trans_currency_code <> eve2.projfunc_currency_code);
503
504 ELSE
505 l_pfc_unbil_eve_applicab_flag := 'N';
506 END IF;
507
508
509 IF( l_pc_unbil_eve_applicab_flag = 'Y' OR l_pfc_unbil_eve_applicab_flag = 'Y' ) THEN
510
511 -- Calculating total partially billed amount for events in project functional currency
512 -- and project currency
513 /*
514 SELECT
515 SUM(NVL(dii.project_bill_amount,0)) pc_total_partial_billed,
516 SUM(NVL(dii.projfunc_bill_amount,0)) pfc_total_partial_billed
517 INTO
518 l_pc_total_part_bill_amt,
519 l_pfc_total_part_bill_amt
520 FROM pa_draft_invoice_items dii
521 WHERE dii.project_id = p_project_id
522 AND EXISTS ( SELECT null
523 FROM pa_events eve
524 WHERE eve.event_num = dii.event_num
525 AND eve.billed_flag = 'N');
526 */
527 /* Bug:14470226 dii.task_id replaced with dii.event_task_id*/
528 SELECT
529 SUM(NVL(dii.project_bill_amount,0)) pc_total_partial_billed,
530 SUM(NVL(dii.projfunc_bill_amount,0)) pfc_total_partial_billed
531 INTO
532 l_pc_total_part_bill_amt,
533 l_pfc_total_part_bill_amt
534 FROM pa_draft_invoice_items dii, pa_events eve
535 WHERE eve.project_id = p_project_id
536 and eve.project_id = dii.project_id
537 and nvl(eve.task_id, 0) = nvl(dii.event_task_id,0)
538 and eve.event_num = nvl(dii.event_num,0)
539 and nvl(eve.billed_flag,'N') = 'N';
540
541 -- Calculating total unbilled event amount in project functional currency
542 -- and project currency
543 l_proj_unbilled_events := nvl(l_pc_unbill_eve_amt_with_part,0) - nvl(l_pc_total_part_bill_amt,0);
544 l_projfunc_unbilled_events := nvl(l_pfc_unbill_eve_amt_with_part,0) - nvl(l_pfc_total_part_bill_amt,0);
545 END IF;
546
547 -- Calculating total unbilled retention amount in project functional currency
548 -- and project currency
549 SELECT
550 /*
551 SUM(NVL(project_total_billed,0)) - SUM(NVL(project_total_retained,0)) Proj_Unbilled_Retn
552 ,SUM(NVL(projfunc_total_billed,0)) - SUM(NVL(projfunc_total_retained,0)) Projfunc_Unbilled_Retn
553 */
554 SUM(NVL(project_total_retained,0)) - SUM(NVL(project_total_billed,0)) Proj_Unbilled_Retn
555 ,SUM(NVL(projfunc_total_retained,0)) - SUM(NVL(projfunc_total_billed,0)) Projfunc_Unbilled_Retn
556 INTO
557 l_proj_unbilled_retn
558 ,l_projfunc_unbilled_retn
559 FROM pa_summary_project_retn
560 WHERE project_id = p_project_id;
561
562 -- Calculating total unapproved invoice amount in project functional currency
563 -- and project currency
564 SELECT
565 SUM(NVL(dii.project_bill_amount,0)) unapproved_project_invoice,
566 SUM(NVL(dii.projfunc_bill_amount,0)) unapproved_project_invoice
567 INTO
568 l_proj_unapprov_inv_amt,
569 l_projfunc_unapprov_inv_amt
570 FROM pa_draft_invoice_items dii,pa_draft_invoices_all di
571 WHERE dii.draft_invoice_num = di.draft_invoice_num
572 AND dii.project_id = di.project_id
573 AND di.project_id = p_project_id
574 AND di.approved_by_person_id IS NULL;
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 NULL;
579 END;
580
581 /* For Bug 3500408 : Introducing NVL clause for all amount columns to ensure that in case any amount column has
582 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
583 as N/A in case of PC<> PFC and otherwise when NULL they take value 0.00*/
584 -- Calling procedure to populate temp table
585 Populat_Bill_Workbench_Data (
586 p_project_id => p_project_id,
587 p_proj_funding_amt => NVL(l_proj_funding_amt ,0),
588 p_proj_rev_accured => NVL(l_proj_rev_accured,0) ,
589 p_proj_rev_backlog => NVL(l_proj_rev_backlog,0) ,
590 p_proj_rev_writeoff => NVL(l_proj_rev_writeoff,0) ,
591 p_proj_ubr => l_proj_ubr ,
592 p_proj_uer => l_proj_uer ,
593 p_proj_inv_invoiced => NVL(l_proj_inv_invoiced ,0),
594 p_proj_inv_backlog => NVL(l_proj_inv_backlog ,0),
595 p_proj_inv_paid => NVL(l_proj_inv_tot_paid ,0),
596 p_proj_inv_due => NVL(l_proj_inv_tot_due ,0),
597 p_proj_billable_cost => NVL(l_proj_billable_cost ,0),
598 p_proj_unbilled_cost => NVL(l_proj_unbilled_cost ,0),
599 p_proj_unbilled_events => NVL(l_proj_unbilled_events ,0),
600 p_proj_unbilled_retn => NVL(l_proj_unbilled_retn ,0),
601 p_proj_unapproved_inv_amt => NVL(l_proj_unapprov_inv_amt ,0),
602 p_proj_tax => NVL(l_proj_tax_orig ,0),
603 p_pc_ubr_applicab_flag => l_pc_ubr_applicab_flag,
604 p_pc_uer_applicab_flag => l_pc_uer_applicab_flag,
605 p_pc_unbil_eve_applicab_flag => l_pc_unbil_eve_applicab_flag,
606 p_projfunc_funding_amt => NVL(l_projfunc_funding_amt,0) ,
607 p_projfunc_rev_accured => NVL(l_projfunc_rev_accured ,0),
608 p_projfunc_rev_backlog => NVL(l_projfunc_rev_backlog,0) ,
609 p_projfunc_rev_writeoff => NVL(l_projfunc_rev_writeoff,0) ,
610 p_projfunc_ubr => l_projfunc_ubr ,
611 p_projfunc_uer => l_projfunc_uer ,
612 p_projfunc_inv_invoiced => NVL(l_projfunc_inv_invoiced,0) ,
613 p_projfunc_inv_backlog => NVL(l_projfunc_inv_backlog ,0),
614 p_projfunc_inv_paid => NVL(l_projfunc_inv_tot_paid ,0),
615 p_projfunc_inv_due => NVL(l_projfunc_inv_tot_due ,0),
616 p_projfunc_billable_cost => NVL(l_projfunc_billable_cost ,0),
617 p_projfunc_unbilled_cost => NVL(l_projfunc_unbilled_cost ,0),
618 p_projfunc_unbilled_events => NVL(l_projfunc_unbilled_events ,0),
619 p_projfunc_unbilled_retn => NVL(l_projfunc_unbilled_retn ,0),
620 p_projfunc_unapprov_inv_amt => NVL(l_projfunc_unapprov_inv_amt ,0),
621 p_projfunc_tax => NVL(l_projfunc_tax_orig ,0),
622 p_pfc_unbil_eve_applicab_flag => l_pfc_unbil_eve_applicab_flag,
623 p_next_invoice_date => l_next_invoice_date,
624 p_multi_customer_flag => l_multi_customer_flag,
625 x_return_status => l_return_status,
626 x_msg_count => l_msg_count ,
627 x_msg_data => l_msg_data
628 );
629 /*End of Bug fix for Bug 3500408*/
630 IF l_ubr_uer_msg_data ='PA_NO_EXCH_RATE_EXISTS_PFC_PC' THEN
631 x_msg_data := l_ubr_uer_msg_data;
632 END IF;
633 EXCEPTION
634 WHEN OTHERS THEN
635 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
636 ,p_procedure_name => 'Get_Billing_Sum_Region_Amts' );
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
638 x_msg_count := 1;
639 x_msg_data := SUBSTR(SQLERRM,1,30);
640 RAISE;
641 END Get_Billing_Sum_Region_Amts;
642
643 -- This procedure will populate the temp table with all the input paramters for billing
644 -- work bench.
645 -- Input parameters
646 -- Parameters Type Required Description
647 -- p_project_id NUMBER YES The identifier of the project
648 -- p_funding_amt NUMBER YES Total Baselined amount for the given project
649 -- p_rev_accured NUMBER YES Total Revenue accrued for the given project
650 -- p_rev_backlog NUMBER YES Revenue funding backlog. The diff of above two
651 -- p_rev_writeoff NUMBER YES Total accrued revenue writeoff
652 -- p_ubr NUMBER YES Total Unbilled receivables for the given project
653 -- p_uer NUMBER YES Total Unearned revenue for the given project
654 -- p_inv_billed NUMBER YES Total Invoiced amount(including project invoices, credit
655 -- memos,write-off,cancelling,concession project, and
656 -- retention invoices
657 -- p_inv_backlog NUMBER YES Invoice Funding backlog. The diff of Funding amt and inv_billed
658 -- p_inv_paid NUMBER YES Total invoice amount paid by the customers for this project
659 -- p_inv_due NUMBER YES Total invoice amount due from customers
660 -- p_billable_cost NUMBER YES Sum of the burdened cost of all the expenditure items
661 -- with billable flag as yes and cost distribution as yes
662 -- p_unbilled_cost NUMBER YES Total burdened cost that is not yet billed, but marked
663 -- as billable as yes
664 -- p_unbilled_events NUMBER YES Sum of all invoice events that are not billed to the customers (
665 -- including partialy billed event amount also
666 -- p_unbilled_retn NUMBER YES Total withheld amount that is not billed to the customer
667 -- p_unapproved_inv_amt NUMBER YES Sum of all the unapproved project and retention invoices
668 -- including credit memosof project invoices, cancelling,
669 -- x_funding_amt NUMBER YES Total Baselined amount for the given project
670 -- x_rev_accured NUMBER YES Total Revenue accrued for the given project
671 -- x_rev_backlog NUMBER YES Revenue funding backlog. The diff of above two
672 -- x_rev_writeoff NUMBER YES Total accrued revenue writeoff
673 -- x_ubr NUMBER YES Total Unbilled receivables for the given project
674 -- x_uer NUMBER YES Total Unearned revenue for the given project
675 -- x_inv_billed NUMBER YES Total Invoiced amount(including project invoices, credit
676 -- memos,write-off,cancelling,concession project, and
677 -- retention invoices
678 -- x_inv_backlog NUMBER YES Invoice Funding backlog. The diff of Funding amt and inv_billed
679 -- x_inv_paid NUMBER YES Total invoice amount paid by the customers for this project
680 -- x_inv_due NUMBER YES Total invoice amount due from customers
681 -- x_billable_cost NUMBER YES Sum of the burdened cost of all the expenditure items
682 -- with billable flag as yes and cost distribution as yes
683 -- x_unbilled_cost NUMBER YES Total burdened cost that is not yet billed, but marked
684 -- as billable as yes
685 -- x_unbilled_events NUMBER YES Sum of all invoice events that are not billed to the customers (
686 -- including partialy billed event amount also
687 -- x_unbilled_retn NUMBER YES Total withheld amount that is not billed to the customer
688 -- x_unapproved_inv_amt NUMBER YES Sum of all the unapproved project and retention invoices
689 -- including credit memosof project invoices, cancelling,
690 -- writeoff,concession project
691 -- writeoff,concession project
692 --
693 -- Out parameters
694 --
695
696 PROCEDURE Populat_Bill_Workbench_Data (
697 p_project_id IN NUMBER,
698 p_proj_funding_amt IN NUMBER ,
699 p_proj_rev_accured IN NUMBER ,
700 p_proj_rev_backlog IN NUMBER ,
701 p_proj_rev_writeoff IN NUMBER ,
702 p_proj_ubr IN NUMBER ,
703 p_proj_uer IN NUMBER ,
704 p_proj_inv_invoiced IN NUMBER ,
705 p_proj_inv_backlog IN NUMBER ,
706 p_proj_inv_paid IN NUMBER ,
707 p_proj_inv_due IN NUMBER ,
708 p_proj_billable_cost IN NUMBER ,
709 p_proj_unbilled_cost IN NUMBER ,
710 p_proj_unbilled_events IN NUMBER ,
711 p_proj_unbilled_retn IN NUMBER ,
712 p_proj_unapproved_inv_amt IN NUMBER ,
713 p_proj_tax IN NUMBER ,
714 p_pc_ubr_applicab_flag IN VARCHAR2,
715 p_pc_uer_applicab_flag IN VARCHAR2,
716 p_pc_unbil_eve_applicab_flag IN VARCHAR2,
717 p_projfunc_funding_amt IN NUMBER ,
718 p_projfunc_rev_accured IN NUMBER ,
719 p_projfunc_rev_backlog IN NUMBER ,
720 p_projfunc_rev_writeoff IN NUMBER ,
721 p_projfunc_ubr IN NUMBER ,
722 p_projfunc_uer IN NUMBER ,
723 p_projfunc_inv_invoiced IN NUMBER ,
724 p_projfunc_inv_backlog IN NUMBER ,
725 p_projfunc_inv_paid IN NUMBER ,
726 p_projfunc_inv_due IN NUMBER ,
727 p_projfunc_billable_cost IN NUMBER ,
728 p_projfunc_unbilled_cost IN NUMBER ,
729 p_projfunc_unbilled_events IN NUMBER ,
730 p_projfunc_unbilled_retn IN NUMBER ,
731 p_projfunc_unapprov_inv_amt IN NUMBER ,
732 p_projfunc_tax IN NUMBER ,
733 p_pfc_unbil_eve_applicab_flag IN VARCHAR2,
734 p_next_invoice_date IN DATE,
735 p_multi_customer_flag IN VARCHAR2,
736 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
737 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
738 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
739 )
740 IS
741 BEGIN
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743
744 DELETE pa_bill_workbench_temp;
745 INSERT
746 INTO pa_bill_workbench_temp(
747 PROJECT_ID ,
748 PC_FUNDING ,
749 PC_REV_ACCRUED ,
750 PC_REV_BACKLOG ,
751 PC_WRITEOFF ,
752 PC_UBR ,
753 PC_UER ,
754 PC_INVOICED ,
755 PC_INV_BACKLOG ,
756 PC_PAID ,
757 PC_DUE ,
758 PC_BILLABLE_COST ,
759 PC_UNBILLED_COST ,
760 PC_UNBILLED_EVENTS ,
761 PC_UNBILLED_RETENTION ,
762 PC_UNAPPRO_INVOICES ,
763 PC_TAX ,
764 PC_UBR_APPLICAB_FLAG ,
765 PC_UER_APPLICAB_FLAG ,
766 PC_UNBIL_EVE_APPLICAB_FLAG ,
767 PFC_FUNDING ,
768 PFC_REV_ACCRUED ,
769 PFC_REV_BACKLOG ,
770 PFC_WRITEOFF ,
771 PFC_UBR ,
772 PFC_UER ,
773 PFC_INVOICED ,
774 PFC_INV_BACKLOG ,
775 PFC_PAID ,
776 PFC_DUE ,
777 PFC_BILLABLE_COST ,
778 PFC_UNBILLED_COST ,
779 PFC_UNBILLED_EVENTS ,
780 PFC_UNBILLED_RETENTION ,
781 PFC_UNAPPRO_INVOICES ,
782 PFC_TAX ,
783 PFC_UNBIL_EVE_APPLICAB_FLAG ,
784 NEXT_INVOICE_DATE ,
785 Multi_Customer_Flag
786 )
787 VALUES(
788 p_project_id ,
789 p_proj_funding_amt ,
790 p_proj_rev_accured ,
791 p_proj_rev_backlog ,
792 p_proj_rev_writeoff ,
793 p_proj_ubr ,
794 p_proj_uer ,
795 p_proj_inv_invoiced ,
796 p_proj_inv_backlog ,
797 p_proj_inv_paid ,
798 p_proj_inv_due ,
799 p_proj_billable_cost ,
800 p_proj_unbilled_cost ,
801 p_proj_unbilled_events ,
802 p_proj_unbilled_retn ,
803 p_proj_unapproved_inv_amt ,
804 p_proj_tax ,
805 p_pc_ubr_applicab_flag ,
806 p_pc_uer_applicab_flag ,
807 p_pc_unbil_eve_applicab_flag ,
808 p_projfunc_funding_amt ,
809 p_projfunc_rev_accured ,
810 p_projfunc_rev_backlog ,
811 p_projfunc_rev_writeoff ,
812 p_projfunc_ubr ,
813 p_projfunc_uer ,
814 p_projfunc_inv_invoiced ,
815 p_projfunc_inv_backlog ,
816 p_projfunc_inv_paid ,
817 p_projfunc_inv_due ,
818 p_projfunc_billable_cost ,
819 p_projfunc_unbilled_cost ,
820 p_projfunc_unbilled_events ,
821 p_projfunc_unbilled_retn ,
822 p_projfunc_unapprov_inv_amt ,
823 p_projfunc_tax ,
824 p_pfc_unbil_eve_applicab_flag ,
825 p_next_invoice_date ,
826 p_multi_customer_flag
827 );
828
829 EXCEPTION
830 WHEN OTHERS THEN
831 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
832 ,p_procedure_name => 'Populat_Bill_Workbench_Data' );
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
834 x_msg_count := 1;
835 x_msg_data := SUBSTR(SQLERRM,1,30);
836 RAISE;
837 END Populat_Bill_Workbench_Data;
838
839
840 -- This procedure will populate the temp table with all the input paramters for Summary by customer region of invoicing
841 -- Input parameters
842 -- Parameters Type Required Description
843 -- p_project_id NUMBER YES The identifier of the project
844 -- p_inv_filter VARCHAR2 YES Filter to filter invoices based on the user inputs
845 --
846 -- Out parameters
847 --
848
849 /* Added 10 parameter after p_inv_filter for search region i.e. bug 3618704 */
850
851 PROCEDURE Populat_Inv_Summ_by_Cust_RN (
852 p_project_id IN NUMBER,
853 p_inv_filter IN VARCHAR2,
854 p_search_flag IN VARCHAR2,
855 p_agreement_id IN NUMBER ,
856 p_draft_num IN NUMBER,
857 p_ar_number IN VARCHAR2 ,
858 p_creation_frm_date IN DATE ,
859 p_creation_to_date IN DATE ,
860 p_invoice_frm_date IN DATE ,
861 p_invoice_to_date IN DATE ,
862 p_gl_frm_date IN DATE ,
863 p_gl_to_date IN DATE ,
864 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
865 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
866 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
867 )
868 IS
869 BEGIN
870 x_return_status := FND_API.G_RET_STS_SUCCESS;
871
872
873 DELETE pa_bill_wrkbench_inv_temp;
874
875
876 /* Added search flag filter if for search region */
877
878 IF (NVL(UPPER(p_search_flag),'N') = 'N') THEN
879
880 INSERT
881 INTO pa_bill_wrkbench_inv_temp(
882 PROJECT_ID
883 ,AGREEMENT_ID
884 ,CUSTOMER_ID
885 ,CUSTOMER_NAME
886 ,PC_FUNDING
887 ,PC_INVOICED
888 ,PC_DUE_ACCEPTED
889 ,PC_DUE_PENDING
890 ,PC_TAX
891 ,PC_TAX_DUE
892 ,PFC_FUNDING
893 ,PFC_INVOICED
894 ,PFC_DUE_ACCEPTED
895 ,PFC_DUE_PENDING
896 ,PFC_TAX
897 ,PFC_TAX_DUE
898 )
899 SELECT
900 spf.project_id project_id
901 ,spf.agreement_id
902 -- ,ra.customer_id customer_id
903 -- ,ra.customer_name||' ('||ra.customer_number||')' Customer
904 ,cust_acct.cust_account_id customer_id
905 ,substrb(party.party_name,1,50)||' ('||cust_acct.account_number||')' Customer
906 ,SUM(NVL(spf.project_baselined_amount,0)) pc_Baselined
907 ,NULL pc_invoiced
908 ,NULL pc_due_accepted
909 ,NULL pc_due_pending
910 ,NULL pc_tax
911 ,NULL pc_tax_due
912 ,SUM(NVL(spf.projfunc_baselined_amount,0)) pfc_Baselined
913 ,NULL pfc_Invoiced
914 ,NULL pfc_due_accepted
915 ,NULL pfc_due_pending
916 ,NULL pfc_tax
917 ,NULL pfc_tax_due
918 FROM
919 -- ra_customers ra
920 hz_parties party
921 , hz_cust_accounts cust_acct
922 , pa_agreements_all agr
923 , pa_project_customers pc
924 , pa_summary_project_fundings spf
925 -- WHERE ra.customer_id = agr.customer_id
926 WHERE cust_acct.cust_account_id= agr.customer_id
927 AND cust_acct.party_id = party.party_id
928 AND agr.customer_id = pc.customer_id
929 AND agr.agreement_id = spf.agreement_id
930 AND spf.project_id = pc.project_id
931 AND spf.project_id = p_project_id
932 GROUP BY /*ra.customer_name,ra.customer_id,ra.customer_number*/
933 substrb(party.party_name,1,50),cust_acct.account_number,cust_acct.cust_account_id,spf.project_id,spf.agreement_id;
934
935
936 /**
937 * Updating Project invoiced amount and project functional invoice amount
938 **/
939
940 UPDATE pa_bill_wrkbench_inv_temp pbw
941 SET (pbw.pc_invoiced,pbw.pfc_invoiced ) =
942 (SELECT
943 SUM(NVL(dii.project_bill_amount,0)) projinv_amt
944 ,SUM(NVL(dii.projfunc_bill_amount,0)) projfuncinv_amt
945 FROM pa_draft_invoices_all di , pa_draft_invoice_items dii , pa_agreements_all agr
946 WHERE dii.draft_invoice_num = di.draft_invoice_num
947 AND dii.project_id = di.project_id
948 AND agr.customer_id = di.customer_id
949 AND agr.agreement_id = di.agreement_id
950 AND di.customer_id = pbw.customer_id
951 AND di.agreement_id = pbw.agreement_id
952 AND di.project_id = pbw.project_id
953 AND (
954 ( 'INV_ALL' = p_inv_filter)
955 OR ( 'INV_APPRO' = p_inv_filter AND di.approved_by_person_id IS NOT NULL
956 AND di.released_date IS NULL)
957 OR ( 'INV_CREDITS' = p_inv_filter AND di.draft_invoice_num_credited IS NOT NULL )
958 OR ( 'INV_RETN_BILL_INV' = p_inv_filter AND NVL(di.retention_invoice_flag,'N') = 'Y' )
959 OR ( 'INV_UNAPPRO' = p_inv_filter AND di.approved_by_person_id IS NULL )
960 OR ( 'INV_RELEASE' = p_inv_filter AND di.transfer_status_code = 'P'
961 AND di.released_date IS NOT NULL )
962 OR ( 'INV_ACCEPT' = p_inv_filter AND di.transfer_status_code = 'A'
963 AND NVL(di.generation_error_flag,'N') <> 'Y' )
964 OR ( 'INV_REJECT' = p_inv_filter AND (di.transfer_status_code = 'X'
965 OR di.transfer_status_code = 'R')
966 AND NVL(di.generation_error_flag,'N') <> 'Y' )
967 OR ( 'INV_ERROR' = p_inv_filter AND NVL(di.generation_error_flag,'N') = 'Y' )
968 )
969 GROUP BY di.project_id,di.customer_id,di.agreement_id);
970
971
972
973 /**
974 * Updating Project due amount (accepted) and project functional due amount (accepted)
975 * Project tax amount, tax due amount and project functional tax amount tax due amount
976 **/
977 /* Modified the below query completely for the Bug 8249757 includes the fix of bug 7394622 - Start */
978 UPDATE pa_bill_wrkbench_inv_temp pbw
979 /* added group function SUM to Ar amounts,modifed query to calculate billing amounts for bug 7628408 */
980 SET (pbw.pc_due_accepted,pbw.pfc_due_accepted, pbw.pc_tax, pbw.pfc_tax, pbw.pc_tax_due, pbw.pfc_tax_due ) =
981 ( SELECT
982 SUM(( pa_inv.pc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.line_amt_remn,1)) due_accepted_pc,
983 SUM(( pa_inv.pfc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.line_amt_remn,1)) due_accepted_pfc,
984 SUM(( pa_inv.pc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_orig,0)) tax_pc,
985 SUM(( pa_inv.pfc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_orig,0)) tax_pfc,
986 SUM(( pa_inv.pc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_remn,0)) tax_due_pc,
987 SUM(( pa_inv.pfc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_remn,0)) tax_due_pfc
988 FROM
989 (SELECT arps.customer_trx_id, arps.trx_number,
990 /* Nvl(SUM(arps.amount_line_items_original),1) line_amt_orig, Commented for Bug 10125590 Start
991 Nvl(SUM(arps.amount_line_items_remaining),1)line_amt_remn,
992 Nvl(SUM(arps.tax_original),0) tax_orig,
993 Nvl(SUM(arps.tax_remaining),0) tax_remn Commented for Bug 10125590 End*/
994 ARPS.AMOUNT_LINE_ITEMS_ORIGINAL LINE_AMT_ORIG,
995 ARPS.AMOUNT_LINE_ITEMS_REMAINING LINE_AMT_REMN,
996 ARPS.TAX_ORIGINAL TAX_ORIG,
997 ARPS.TAX_REMAINING TAX_REMN
998 FROM ar_payment_schedules_all arps
999 /* GROUP BY arps.customer_trx_id, arps.trx_number Commented for Bug 10125590 */
1000 ) pa_ar,
1001 (SELECT di.project_id,
1002 di.customer_id,
1003 di.agreement_id,
1004 di.ra_invoice_number,
1005 di.system_reference system_reference,
1006 SUM(dii.project_bill_amount) pc_inv_amt,
1007 SUM(dii.projfunc_bill_amount) pfc_inv_amt
1008 FROM pa_draft_invoices_all di,
1009 pa_draft_invoice_items dii,
1010 PA_BILL_WRKBENCH_INV_TEMP pbwi
1011 WHERE di.project_id = dii.project_id
1012 AND di.draft_invoice_num = dii.draft_invoice_num
1013 AND di.transfer_status_code = 'A'
1014 AND di.customer_id = pbwi.customer_id
1015 AND di.agreement_id = pbwi.agreement_id
1016 AND di.project_id = pbwi.project_id
1017 AND di.system_reference IS NOT NULL
1018 AND (
1019 ( 'INV_ALL' = p_inv_filter)
1020 OR ( 'INV_APPRO' = p_inv_filter
1021 AND di.approved_by_person_id IS NOT NULL
1022 AND di.released_date IS NULL)
1023 OR ( 'INV_CREDITS' = p_inv_filter
1024 AND di.draft_invoice_num_credited IS NOT NULL )
1025 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1026 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1027 OR ( 'INV_UNAPPRO' = p_inv_filter
1028 AND di.approved_by_person_id IS NULL )
1029 OR ( 'INV_RELEASE' = p_inv_filter
1030 AND di.transfer_status_code = 'P'
1031 AND di.released_date IS NOT NULL )
1032 OR ( 'INV_ACCEPT' = p_inv_filter
1033 AND di.transfer_status_code = 'A'
1034 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1035 OR ( 'INV_REJECT' = p_inv_filter
1036 AND (di.transfer_status_code = 'X'
1037 OR di.transfer_status_code = 'R')
1038 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1039 OR ( 'INV_ERROR' = p_inv_filter
1040 AND NVL(di.generation_error_flag,'N') = 'Y' )
1041 )
1042 GROUP BY di.project_id,di.customer_id,di.agreement_id, di.ra_invoice_number,
1043 di.system_reference) pa_inv
1044 WHERE pa_inv.project_id = pbw.project_id
1045 AND pa_inv.customer_id = pbw.customer_id
1046 AND pa_inv.agreement_id = pbw.agreement_id
1047 AND pa_inv.system_reference = pa_ar.customer_trx_id(+)
1048 AND pa_inv.ra_invoice_number = pa_ar.trx_number(+)
1049 AND pa_ar.line_amt_orig(+) <> 0 /* Condition added for bug 5230465 */
1050 GROUP BY pa_inv.project_id, pa_inv.customer_id,pa_inv.agreement_id);
1051 /* Modified the query completely for the Bug 8249757 includes the fix of bug 7394622 - End */
1052
1053 UPDATE pa_bill_wrkbench_inv_temp pbw
1054 SET (pbw.pc_due_pending,pbw.pfc_due_pending ) =
1055 ( SELECT
1056 SUM(dii.project_bill_amount) ,
1057 SUM(dii.projfunc_bill_amount)
1058 FROM pa_draft_invoices_all di,
1059 pa_draft_invoice_items dii
1060 WHERE di.project_id = dii.project_id
1061 AND di.draft_invoice_num = dii.draft_invoice_num
1062 AND di.transfer_status_code <> 'A'
1063 AND di.customer_id = pbw.customer_id
1064 AND di.agreement_id = pbw.agreement_id
1065 AND di.project_id = pbw.project_id
1066 AND di.system_reference IS NULL
1067 AND (
1068 ( 'INV_ALL' = p_inv_filter)
1069 OR ( 'INV_APPRO' = p_inv_filter
1070 AND di.approved_by_person_id IS NOT NULL
1071 AND di.released_date IS NULL)
1072 OR ( 'INV_CREDITS' = p_inv_filter
1073 AND di.draft_invoice_num_credited IS NOT NULL )
1074 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1075 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1076 OR ( 'INV_UNAPPRO' = p_inv_filter
1077 AND di.approved_by_person_id IS NULL )
1078 OR ( 'INV_RELEASE' = p_inv_filter
1079 AND di.transfer_status_code = 'P'
1080 AND di.released_date IS NOT NULL )
1081 OR ( 'INV_ACCEPT' = p_inv_filter
1082 AND di.transfer_status_code = 'A'
1083 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1084 OR ( 'INV_REJECT' = p_inv_filter
1085 AND (di.transfer_status_code = 'X'
1086 OR di.transfer_status_code = 'R')
1087 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1088 OR ( 'INV_ERROR' = p_inv_filter
1089 AND NVL(di.generation_error_flag,'N') = 'Y' )
1090 )
1091 GROUP BY di.project_id,di.customer_id,di.agreement_id);
1092
1093 ELSIF (NVL(UPPER(p_search_flag),'N') = 'Y') THEN
1094
1095 INSERT
1096 INTO pa_bill_wrkbench_inv_temp(
1097 PROJECT_ID
1098 ,AGREEMENT_ID
1099 ,CUSTOMER_ID
1100 ,CUSTOMER_NAME
1101 ,PC_FUNDING
1102 ,PC_INVOICED
1103 ,PC_DUE_ACCEPTED
1104 ,PC_DUE_PENDING
1105 ,PC_TAX
1106 ,PC_TAX_DUE
1107 ,PFC_FUNDING
1108 ,PFC_INVOICED
1109 ,PFC_DUE_ACCEPTED
1110 ,PFC_DUE_PENDING
1111 ,PFC_TAX
1112 ,PFC_TAX_DUE
1113 )
1114 SELECT
1115 spf.project_id project_id
1116 ,spf.agreement_id
1117 -- ,ra.customer_id customer_id
1118 -- ,ra.customer_name||' ('||ra.customer_number||')' Customer
1119 ,cust_acct.cust_account_id customer_id
1120 ,substrb(party.party_name,1,50)||' ('||cust_acct.account_number||')' Customer
1121 ,SUM(NVL(spf.project_baselined_amount,0)) pc_Baselined
1122 ,NULL pc_invoiced
1123 ,NULL pc_due_accepted
1124 ,NULL pc_due_pending
1125 ,NULL pc_tax
1126 ,NULL pc_tax_due
1127 ,SUM(NVL(spf.projfunc_baselined_amount,0)) pfc_Baselined
1128 ,NULL pfc_Invoiced
1129 ,NULL pfc_due_accepted
1130 ,NULL pfc_due_pending
1131 ,NULL pfc_tax
1132 ,NULL pfc_tax_due
1133 FROM
1134 -- ra_customers ra
1135 hz_parties party
1136 , hz_cust_accounts cust_acct
1137 , pa_agreements_all agr
1138 , pa_project_customers pc
1139 , pa_summary_project_fundings spf
1140 , pa_draft_invoices_all di
1141 -- WHERE ra.customer_id = agr.customer_id
1142 WHERE cust_acct.cust_account_id= agr.customer_id
1143 AND cust_acct.party_id = party.party_id
1144 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1145 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1146 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1147 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1148 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1149 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1150 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1151 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1152 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1153 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1154 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1155 AND di.agreement_id = agr.agreement_id
1156 AND di.customer_id = agr.customer_id
1157 AND di.project_id = spf.project_id
1158 AND agr.customer_id = pc.customer_id
1159 AND agr.agreement_id = spf.agreement_id
1160 AND spf.project_id = pc.project_id
1161 AND spf.agreement_id = NVL(p_agreement_id,spf.agreement_id)
1162 AND spf.project_id = p_project_id
1163 GROUP BY /*ra.customer_name,ra.customer_id,ra.customer_number*/
1164 substrb(party.party_name,1,50),cust_acct.account_number,cust_acct.cust_account_id,spf.project_id,spf.agreement_id;
1165 /**
1166 * Updating Project invoiced amount and project functional invoice amount
1167 **/
1168
1169 UPDATE pa_bill_wrkbench_inv_temp pbw
1170 SET (pbw.pc_invoiced,pbw.pfc_invoiced ) =
1171 (SELECT
1172 SUM(NVL(dii.project_bill_amount,0)) projinv_amt
1173 ,SUM(NVL(dii.projfunc_bill_amount,0)) projfuncinv_amt
1174 FROM pa_draft_invoices_all di , pa_draft_invoice_items dii , pa_agreements_all agr
1175 WHERE dii.draft_invoice_num = di.draft_invoice_num
1176 AND dii.project_id = di.project_id
1177 AND agr.customer_id = di.customer_id
1178 AND agr.agreement_id = di.agreement_id
1179 AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
1180 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1181 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1182 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1183 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1184 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1185 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1186 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1187 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1188 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1189 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1190 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1191 AND di.customer_id = pbw.customer_id
1192 AND di.agreement_id = pbw.agreement_id
1193 AND di.project_id = pbw.project_id
1194 AND (
1195 ( 'INV_ALL' = p_inv_filter)
1196 OR ( 'INV_APPRO' = p_inv_filter AND di.approved_by_person_id IS NOT NULL
1197 AND di.released_date IS NULL)
1198 OR ( 'INV_CREDITS' = p_inv_filter AND di.draft_invoice_num_credited IS NOT NULL )
1199 OR ( 'INV_RETN_BILL_INV' = p_inv_filter AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1200 OR ( 'INV_UNAPPRO' = p_inv_filter AND di.approved_by_person_id IS NULL )
1201 OR ( 'INV_RELEASE' = p_inv_filter AND di.transfer_status_code = 'P'
1202 AND di.released_date IS NOT NULL )
1203 OR ( 'INV_ACCEPT' = p_inv_filter AND di.transfer_status_code = 'A'
1204 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1205 OR ( 'INV_REJECT' = p_inv_filter AND (di.transfer_status_code = 'X'
1206 OR di.transfer_status_code = 'R')
1207 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1208 OR ( 'INV_ERROR' = p_inv_filter AND NVL(di.generation_error_flag,'N') = 'Y' )
1209 )
1210 GROUP BY di.project_id,di.customer_id,di.agreement_id);
1211
1212
1213 /**
1214 * Updating Project due amount (accepted) and project functional due amount (accepted)
1215 * Project tax amount, tax due amount and project functional tax amount tax due amount
1216 **/
1217 /* Modified the below query completely for the Bug 8249757 includes the fix of bug 7394622 - Start */
1218 UPDATE pa_bill_wrkbench_inv_temp pbw
1219 /* added group function SUM to Ar amounts,modifed query to calculate billing amounts for bug 7628408 */
1220 SET (pbw.pc_due_accepted,pbw.pfc_due_accepted, pbw.pc_tax, pbw.pfc_tax, pbw.pc_tax_due, pbw.pfc_tax_due ) =
1221 ( SELECT
1222 SUM(( pa_inv.pc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.line_amt_remn,1)) due_accepted_pc,
1223 SUM(( pa_inv.pfc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.line_amt_remn,1)) due_accepted_pfc,
1224 SUM(( pa_inv.pc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_orig,0)) tax_pc,
1225 SUM(( pa_inv.pfc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_orig,0)) tax_pfc,
1226 SUM(( pa_inv.pc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_remn,0)) tax_due_pc,
1227 SUM(( pa_inv.pfc_inv_amt /Nvl(pa_ar.line_amt_orig,1)) * Nvl(pa_ar.tax_remn,0)) tax_due_pfc
1228 FROM
1229 (SELECT arps.customer_trx_id, arps.trx_number,
1230 Nvl(SUM(arps.amount_line_items_original),1) line_amt_orig,
1231 Nvl(SUM(arps.amount_line_items_remaining),1)line_amt_remn,
1232 Nvl(SUM(arps.tax_original),0) tax_orig,
1233 Nvl(SUM(arps.tax_remaining),0) tax_remn
1234 FROM ar_payment_schedules_all arps
1235 GROUP BY arps.customer_trx_id, arps.trx_number
1236 ) pa_ar,
1237 (SELECT di.project_id,
1238 di.customer_id,
1239 di.agreement_id,
1240 di.ra_invoice_number,
1241 di.system_reference system_reference,
1242 SUM(dii.project_bill_amount) pc_inv_amt,
1243 SUM(dii.projfunc_bill_amount) pfc_inv_amt
1244 FROM pa_draft_invoices_all di,
1245 pa_draft_invoice_items dii,
1246 PA_BILL_WRKBENCH_INV_TEMP pbwi
1247 WHERE di.project_id = dii.project_id
1248 AND di.draft_invoice_num = dii.draft_invoice_num
1249 AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
1250 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1251 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1252 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1253 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1254 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1255 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1256 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1257 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1258 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1259 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1260 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1261 AND di.transfer_status_code = 'A'
1262 AND di.customer_id = pbwi.customer_id
1263 AND di.agreement_id = pbwi.agreement_id
1264 AND di.project_id = pbwi.project_id
1265 AND di.system_reference IS NOT NULL
1266 AND (
1267 ( 'INV_ALL' = p_inv_filter)
1268 OR ( 'INV_APPRO' = p_inv_filter
1269 AND di.approved_by_person_id IS NOT NULL
1270 AND di.released_date IS NULL)
1271 OR ( 'INV_CREDITS' = p_inv_filter
1272 AND di.draft_invoice_num_credited IS NOT NULL )
1273 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1274 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1275 OR ( 'INV_UNAPPRO' = p_inv_filter
1276 AND di.approved_by_person_id IS NULL )
1277 OR ( 'INV_RELEASE' = p_inv_filter
1278 AND di.transfer_status_code = 'P'
1279 AND di.released_date IS NOT NULL )
1280 OR ( 'INV_ACCEPT' = p_inv_filter
1281 AND di.transfer_status_code = 'A'
1282 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1283 OR ( 'INV_REJECT' = p_inv_filter
1284 AND (di.transfer_status_code = 'X'
1285 OR di.transfer_status_code = 'R')
1286 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1287 OR ( 'INV_ERROR' = p_inv_filter
1288 AND NVL(di.generation_error_flag,'N') = 'Y' )
1289 )
1290 GROUP BY di.project_id,di.customer_id,di.agreement_id, di.ra_invoice_number,
1291 di.system_reference) pa_inv
1292 WHERE pa_inv.project_id = pbw.project_id
1293 AND pa_inv.customer_id = pbw.customer_id
1294 AND pa_inv.agreement_id = pbw.agreement_id
1295 AND pa_inv.system_reference = pa_ar.customer_trx_id(+)
1296 AND pa_inv.ra_invoice_number = pa_ar.trx_number(+)
1297 AND pa_ar.line_amt_orig(+) <> 0 /* Condition added for bug 5230465 */
1298 GROUP BY pa_inv.project_id, pa_inv.customer_id,pa_inv.agreement_id);
1299 /* Modified the query completely for the Bug 8249757 includes the fix of bug 7394622 - End */
1300
1301 UPDATE pa_bill_wrkbench_inv_temp pbw
1302 SET (pbw.pc_due_pending,pbw.pfc_due_pending ) =
1303 ( SELECT
1304 SUM(dii.project_bill_amount) ,
1305 SUM(dii.projfunc_bill_amount)
1306 FROM pa_draft_invoices_all di,
1307 pa_draft_invoice_items dii
1308 WHERE di.project_id = dii.project_id
1309 AND di.draft_invoice_num = dii.draft_invoice_num
1310 AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
1311 AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
1312 AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
1313 AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
1314 NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1315 AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
1316 AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
1317 NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1318 AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
1319 AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
1320 NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1321 AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
1322 AND di.transfer_status_code <> 'A'
1323 AND di.customer_id = pbw.customer_id
1324 AND di.agreement_id = pbw.agreement_id
1325 AND di.project_id = pbw.project_id
1326 AND di.system_reference IS NULL
1327 AND (
1328 ( 'INV_ALL' = p_inv_filter)
1329 OR ( 'INV_APPRO' = p_inv_filter
1330 AND di.approved_by_person_id IS NOT NULL
1331 AND di.released_date IS NULL)
1332 OR ( 'INV_CREDITS' = p_inv_filter
1333 AND di.draft_invoice_num_credited IS NOT NULL )
1334 OR ( 'INV_RETN_BILL_INV' = p_inv_filter
1335 AND NVL(di.retention_invoice_flag,'N') = 'Y' )
1336 OR ( 'INV_UNAPPRO' = p_inv_filter
1337 AND di.approved_by_person_id IS NULL )
1338 OR ( 'INV_RELEASE' = p_inv_filter
1339 AND di.transfer_status_code = 'P'
1340 AND di.released_date IS NOT NULL )
1341 OR ( 'INV_ACCEPT' = p_inv_filter
1342 AND di.transfer_status_code = 'A'
1343 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1344 OR ( 'INV_REJECT' = p_inv_filter
1345 AND (di.transfer_status_code = 'X'
1346 OR di.transfer_status_code = 'R')
1347 AND NVL(di.generation_error_flag,'N') <> 'Y' )
1348 OR ( 'INV_ERROR' = p_inv_filter
1349 AND NVL(di.generation_error_flag,'N') = 'Y' )
1350 )
1351 GROUP BY di.project_id,di.customer_id,di.agreement_id);
1352
1353 END IF;
1354
1355 /**
1356 * If there is no rows in this table then inserting null row so that
1357 * temp table should not return null pointer exception
1358 **/
1359
1360 INSERT
1361 INTO pa_bill_wrkbench_inv_temp(
1362 PROJECT_ID
1363 ,AGREEMENT_ID
1364 ,CUSTOMER_ID
1365 ,CUSTOMER_NAME
1366 ,PC_FUNDING
1367 ,PC_INVOICED
1368 ,PC_DUE_ACCEPTED
1369 ,PC_DUE_PENDING
1370 ,PC_TAX
1371 ,PC_TAX_DUE
1372 ,PFC_FUNDING
1373 ,PFC_INVOICED
1374 ,PFC_DUE_ACCEPTED
1375 ,PFC_DUE_PENDING
1376 ,PFC_TAX
1377 ,PFC_TAX_DUE)
1378 SELECT
1379 p_project_id
1380 ,-1
1381 ,-1
1382 ,NULL
1383 ,NULL
1384 ,NULL
1385 ,NULL
1386 ,NULL
1387 ,NULL
1388 ,NULL
1389 ,NULL
1390 ,NULL
1391 ,NULL
1392 ,NULL
1393 ,NULL
1394 ,NULL
1395 FROM
1396 dual
1397 WHERE NOT EXISTS( SELECT 'x'
1398 FROM pa_bill_wrkbench_inv_temp a
1399 WHERE a.project_id = p_project_id);
1400
1401
1402
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
1406 ,p_procedure_name => 'Populat_Inv_Summ_by_Cust_RN' );
1407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1408 x_msg_count := 1;
1409 x_msg_data := SUBSTR(SQLERRM,1,30);
1410 RAISE;
1411 END Populat_Inv_Summ_by_Cust_RN;
1412
1413
1414
1415
1416
1417 --
1418 -- Procedure : Get_Due_Amount
1419 -- Purpose : This procedure will get all the parameters for Billing Region for the given project.
1420 -- Parameters :
1421 --
1422
1423 FUNCTION Get_Due_Amount (
1424 p_project_id IN NUMBER DEFAULT NULL,
1425 p_draft_inv_num IN NUMBER DEFAULT NULL,
1426 p_system_reference IN NUMBER ,
1427 p_transfer_status_code IN VARCHAR2 ,
1428 p_calling_mode IN VARCHAR2 ,
1429 p_inv_amount IN NUMBER DEFAULT NULL,
1430 p_proj_bill_amount IN NUMBER DEFAULT NULL,
1431 p_projfunc_bill_amount IN NUMBER DEFAULT NULL
1432 ) RETURN NUMBER
1433 IS
1434
1435 BEGIN
1436
1437 IF (p_transfer_status_code = 'A') THEN
1438
1439 IF ( NVL(G_system_reference,-99) <> p_system_reference ) THEN
1440 BEGIN
1441 SELECT
1442 SUM(ar.amount_line_items_remaining + ar.tax_remaining)
1443 INTO G_ar_amount
1444 FROM ar_payment_schedules_all ar
1445 WHERE p_system_reference IS NOT NULL
1446 AND p_transfer_status_code = 'A'
1447 AND ar.customer_trx_id = p_system_reference;
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 RAISE;
1451 END;
1452
1453 G_system_reference := p_system_reference;
1454 END IF;
1455
1456 IF ( p_calling_mode = 'TRANS' ) THEN
1457 RETURN (G_ar_amount);
1458 ELSIF (p_inv_amount = 0 ) THEN /* condition added */
1459 RETURN (0); /* to fix bug 5230465 */
1460 ELSIF ( p_calling_mode = 'PFC' ) THEN
1461 RETURN ( (p_projfunc_bill_amount/p_inv_amount) * G_ar_amount);
1462 ELSIF ( p_calling_mode = 'PC' ) THEN
1463 RETURN ( (p_proj_bill_amount/p_inv_amount) * G_ar_amount);
1464 END IF;
1465
1466 END IF;
1467
1468
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
1472 ,p_procedure_name => 'Get_Due_Amount' );
1473 RAISE;
1474
1475 END Get_Due_Amount;
1476
1477
1478 FUNCTION Get_tax_Amount (
1479 p_project_id IN NUMBER DEFAULT NULL,
1480 p_draft_inv_num IN NUMBER DEFAULT NULL,
1481 p_system_reference IN NUMBER ,
1482 p_transfer_status_code IN VARCHAR2 ,
1483 p_calling_mode IN VARCHAR2 ,
1484 p_inv_amount IN NUMBER DEFAULT NULL,
1485 p_proj_bill_amount IN NUMBER DEFAULT NULL,
1486 p_projfunc_bill_amount IN NUMBER DEFAULT NULL
1487 ) RETURN NUMBER
1488 IS
1489
1490 l_tax_amount number;
1491
1492 BEGIN
1493
1494 IF (p_transfer_status_code = 'A') THEN
1495
1496 BEGIN
1497 SELECT
1498 SUM(ar.tax_original)
1499 INTO l_tax_amount
1500 FROM ar_payment_schedules_all ar
1501 WHERE p_system_reference IS NOT NULL
1502 AND p_transfer_status_code = 'A'
1503 AND ar.customer_trx_id = p_system_reference;
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 RAISE;
1507 END;
1508
1509
1510 IF ( p_calling_mode = 'TRANS' ) THEN
1511 RETURN (l_tax_amount);
1512 ELSIF (p_inv_amount = 0 ) THEN /* condition added */
1513 RETURN (0); /* to fix bug 5230465 */
1514 ELSIF ( p_calling_mode = 'PFC' ) THEN
1515 RETURN ( (p_projfunc_bill_amount/p_inv_amount) * l_tax_amount);
1516 ELSIF ( p_calling_mode = 'PC' ) THEN
1517 RETURN ( (p_proj_bill_amount/p_inv_amount) * l_tax_amount);
1518 END IF;
1519
1520 END IF;
1521
1522
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BILLING_WORKBENCH_BILL_PKG'
1526 ,p_procedure_name => 'Get_Tax_Amount' );
1527 RAISE;
1528
1529 END Get_tax_Amount;
1530
1531 -- Added for bug 4932118
1532 -- Procedure : PROJECT_UBR_UER_CONVERT
1533 -- Purpose : This procedure will convert UBR/UER amounts
1534 -- in Projfunc curr to project curr for MCB
1535 -- projects while revtrans curr is different from
1536 -- Projfunc currency.
1537 -- Parameters : P_PROJECT_ID - Input Project Id
1538 -- X_PROJECT_CURR_UBR - UBR amount in Project currency
1539 -- X_PROJECT_CURR_UER - UER amount in Project currency
1540 -- X_RETURN_STATUS_ - Return status of the API
1541 -- X_MSG_COUNT - Count of error messages
1542 -- X_MSG_DATA - Actual message data
1543 --
1544
1545 Procedure PROJECT_UBR_UER_CONVERT (
1546 P_PROJECT_ID IN NUMBER,
1547 X_PROJECT_CURR_UBR OUT NOCOPY NUMBER,
1548 X_PROJECT_CURR_UER OUT NOCOPY NUMBER,
1549 X_RETURN_STATUS OUT NOCOPY VARCHAR,
1550 X_MSG_COUNT OUT NOCOPY NUMBER,
1551 X_MSG_DATA OUT NOCOPY VARCHAR )
1552 Is
1553 l_mcb_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp ;
1554 l_prj_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1555 l_projfunc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1556 l_prj_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1557 l_prj_rate_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1558 l_prj_exch_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1559 l_ubr_dr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1560 l_uer_cr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1561 l_project_ubr_dr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1562 l_project_uer_cr_tab PA_PLSQL_DATATYPES.NumTabTyp;
1563 l_conversion_between varchar(30);
1564 l_user_validate_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp ;
1565 l_cache_flag varchar(1);
1566 l_project_denominator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1567 l_project_numerator_tab PA_PLSQL_DATATYPES.NumTabTyp;
1568 l_prj_status_tab PA_PLSQL_DATATYPES.Char30TabTyp ;
1569 Begin
1570 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1571
1572 --FND_MSG_PUB.initialize;
1573
1574 SELECT
1575 MULTI_CURRENCY_BILLING_FLAG,
1576 PROJFUNC_CURRENCY_CODE,
1577 PROJECT_CURRENCY_CODE,
1578 PROJECT_BIL_RATE_TYPE,
1579 PROJECT_BIL_RATE_DATE,
1580 PROJECT_BIL_EXCHANGE_RATE,
1581 UNBILLED_RECEIVABLE_DR,
1582 UNEARNED_REVENUE_CR
1583 INTO
1584 l_mcb_flag_tab(1),
1585 l_projfunc_currency_code_tab(1),
1586 l_prj_currency_code_tab(1),
1587 l_prj_rate_type_tab(1),
1588 l_prj_rate_date_tab(1),
1589 l_prj_exch_rate_tab(1),
1590 l_ubr_dr_tab(1),
1591 l_uer_cr_tab(1)
1592 FROM PA_PROJECTS_ALL
1593 WHERE PROJECT_ID = P_PROJECT_ID;
1594 l_user_validate_flag_tab(1) := 'N';
1595 l_prj_status_tab(1) := 'N';
1596
1597 IF l_mcb_flag_tab(1) = 'Y' AND l_projfunc_currency_code_tab(1) <> l_prj_currency_code_tab(1) Then
1598 /* Call PA_MULTI_CURRENCY_BILLING.convert_amount_bulk to convert from ubr/uer
1599 amount in project functional currency to project currency. */
1600 l_conversion_between := 'PFC_PC';
1601 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1602 p_from_currency_tab => l_projfunc_currency_code_tab,
1603 p_to_currency_tab => l_prj_currency_code_tab,
1604 p_conversion_date_tab => L_prj_rate_date_tab,
1605 p_conversion_type_tab => L_prj_rate_type_tab,
1606 p_amount_tab => l_ubr_dr_tab,
1607 p_user_validate_flag_tab => l_user_validate_flag_tab,
1608 p_converted_amount_tab => l_project_ubr_dr_tab,
1609 p_denominator_tab => l_project_denominator_tab,
1610 p_numerator_tab => l_project_numerator_tab,
1611 p_rate_tab => l_prj_exch_rate_tab,
1612 p_conversion_between => l_conversion_between,
1613 p_cache_flag => l_cache_flag,
1614 x_status_tab => l_prj_status_tab
1615 );
1616 /* Copy if the API call is successful */
1617 IF l_prj_status_tab(1) = 'N' Then
1618 X_PROJECT_CURR_UBR := l_project_ubr_dr_tab(1);
1619 ELSE
1620 x_msg_count := 1;
1621 X_MSG_DATA := l_prj_status_tab(1);
1622 End If;
1623
1624 l_conversion_between := 'PFC_PC';
1625 l_prj_status_tab(1) := 'N';
1626 PA_MULTI_CURRENCY_BILLING.convert_amount_bulk(
1627 p_from_currency_tab => l_projfunc_currency_code_tab,
1628 p_to_currency_tab => l_prj_currency_code_tab,
1629 p_conversion_date_tab => L_prj_rate_date_tab,
1630 p_conversion_type_tab => L_prj_rate_type_tab,
1631 p_amount_tab => l_uer_cr_tab,
1632 p_user_validate_flag_tab => l_user_validate_flag_tab,
1633 p_converted_amount_tab => l_project_uer_cr_tab,
1634 p_denominator_tab => l_project_denominator_tab,
1635 p_numerator_tab => l_project_numerator_tab,
1636 p_rate_tab => l_prj_exch_rate_tab,
1637 p_conversion_between => l_conversion_between,
1638 p_cache_flag => l_cache_flag,
1639 x_status_tab => l_prj_status_tab
1640 );
1641 /* Copy if the API call is successful */
1642 IF l_prj_status_tab(1) = 'N' Then
1643 X_PROJECT_CURR_UER := l_project_uer_cr_tab(1);
1644 ELSE
1645 x_msg_count := 1;
1646 X_MSG_DATA := l_prj_status_tab(1);
1647 End If;
1648
1649 Else
1650 X_Project_CURR_UBR := l_ubr_dr_tab(1);
1651 X_Project_CURR_UER := l_uer_cr_tab(1);
1652 End If;
1653 /* Handle Exceptions */
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1657 x_msg_count := 1;
1658 x_msg_data := SQLERRM;
1659
1660 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_BILLING_WORKBENCH_BILL_PKG'
1661 ,p_procedure_name => 'PROJECT_UBR_UER_CONVERT');
1662 Raise;
1663
1664 End PROJECT_UBR_UER_CONVERT;
1665 END PA_BILLING_WORKBENCH_BILL_PKG;
1666