[Home] [Help]
PACKAGE BODY: APPS.PA_BILL_WORKBENCH_INVOICE
Source
1 PACKAGE BODY pa_bill_workbench_invoice AS
2 /*$Header: PABWINVB.pls 120.5 2005/11/23 22:48:10 rkchoudh noship $ */
3
4
5
6 PROCEDURE get_inv_global_value(p_project_id IN NUMBER,
7 p_draft_inv_num IN NUMBER,
8 x_mcb_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9 x_user_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10 x_login_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
11 x_person_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
12 x_yes_m OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
13 x_no_m OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
14 x_na_m OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
15 x_employee_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 X_fs_approve OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
17 X_prj_closed_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
18 x_dist_warn_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19 x_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
20 x_multi_cust_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
22 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
24 ) IS
25
26
27 l_project_status_code VARCHAR2(30);
28
29 l_emp_not_found EXCEPTION;
30
31 l_warning_count NUMBER;
32 l_related_inv_num VARCHAR2(30) := NULL;
33 l_dummy NUMBER;
34
35 /*
36 Cursor related_Inv_Cur is
37 select to_char(draft_invoice_num) draft_inv_num
38 FROM pa_draft_invoices_all
39 WHERE project_id = P_Project_ID
40 AND invoice_set_id = (select invoice_set_id from
41 pa_draft_invoices_all
42 where draft_invoice_num = p_draft_inv_num
43 and project_id = p_project_id)
44 and nvl(customer_bill_split,0) not in (0,100);
45 */
46
47 BEGIN
48
49
50
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 x_msg_count := 0;
53 x_msg_data := NULL;
54
55
56 /* -----------------------------------------
57 Get the value from pa_projects table
58 ----------------------------------------- */
59
60 SELECT nvl(multi_currency_billing_flag, 'N'),
61 nvl(org_id, -99)
62 INTO x_mcb_flag,
63 x_org_id
64 FROM pa_projects_all
65 WHERE project_id = p_project_id;
66
67
68 /* ------------------------------------------------------------
69 Get the project closed flag based on the project status code
70 ------------------------------------------------------------ */
71
72
73 x_prj_closed_flag := 'N';
74
75
76
77 /* -------------------------------------------------------
78 Get the User Id and the login id from the FND profile
79 ------------------------------------------------------- */
80
81
82
83
84 BEGIN
85
86 x_user_id := fnd_profile.value ('USER_ID');
87 x_login_id := fnd_profile.value('LOGIN_ID');
88
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 /* ATG NOCOPY changes */
93 x_user_id := null;
94 x_login_id := null;
95
96
97 RAISE l_emp_not_found;
98
99 END;
100
101
102 /* -----------------------------------------
103 Get the meaning of Yes/No Lookup value
104 ----------------------------------------- */
105
106
107
108 SELECT meaning
109 INTO x_yes_m
110 FROM fnd_lookups
111 WHERE lookup_type = 'YES_NO'
112 AND lookup_code = 'Y';
113
114 SELECT meaning
115 INTO x_no_m
116 FROM fnd_lookups
117 WHERE lookup_type = 'YES_NO'
118 AND lookup_code = 'N';
119
120
121
122
123 /* ------------------------------------------
124 Get the Emplpyee Name and Person Id
125 ------------------------------------------ */
126
127
128 BEGIN
129
130 SELECT employee_id
131 INTO x_person_id
132 FROM fnd_user
133 WHERE user_id = x_user_id;
134
135 SELECT full_name
136 INTO x_employee_name
137 FROM pa_employees
138 WHERE person_id = x_person_id;
139
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142
143 /* ATG changes */
144 x_person_id := null;
145 x_employee_name := null;
146
147 RAISE l_emp_not_found;
148
149 END;
150
151
152 /* ------------------------------------------------------
153 Get the Function security of the invoice action
154 ------------------------------------------------------*/
155
156
157 IF (fnd_function.test('PA_PAXINRVW_APPROVE') = TRUE) THEN
158 x_fs_approve := 'Y';
159 ELSE
160 x_fs_approve := 'N';
161 END IF;
162
163
164 /* -----------------------------------------------------------
165 Get the distribution warning flag (if any any warning set for
166 this project
167 ------------------------------------------------------------ */
168
169
170 SELECT count(*)
171 INTO l_warning_count
172 FROM pa_distribution_warnings
173 WHERE project_id = p_project_id
174 AND draft_invoice_num = p_draft_inv_num;
175
176
177 IF (l_warning_count = 0) then
178 x_dist_warn_flag := 'N';
179 else
180 x_dist_warn_flag := 'Y';
181 END IF;
182
183
184 /* -----------------------------------------------------------
185 Get the meaning for Not Applicable
186 ------------------------------------------------------------ */
187
188
189 SELECT meaning
190 INTO x_na_m
191 FROM pa_lookups
192 WHERE lookup_type = 'PA_BILL_WRKBNCH_NA'
193 AND lookup_code = 'N_A';
194
195
196
197 /* -----------------------------------------------------------
198 Get related invoices - invoice number
199 ------------------------------------------------------------ */
200 /*
201 for related_inv_rec in related_inv_cur loop
202
203 if l_related_inv_num is not null then
204 l_related_inv_num := l_related_inv_num ||',';
205 end if;
206 l_related_inv_num := l_related_inv_num || trim(related_inv_rec.draft_inv_num);
207 end loop;
208
209 x_related_inv_num := l_related_inv_num;
210 */
211
212 /* check for multi customer invoices */
213
214 SELECT count(*)
215 INTO l_dummy
216 FROM PA_Draft_Invoices_ALL i
217 WHERE i.invoice_set_id = (select invoice_set_id from pa_draft_invoices_all
218 where project_id = p_project_id
219 and draft_invoice_num = p_draft_inv_num)
220 AND i.customer_bill_split not in (0, 100)
221 AND i.approved_date is null
222 and i.project_id = p_project_id;
223
224 IF (l_dummy > 1) THEN
225 X_multi_cust_flag := 'Y';
226 else
227 X_multi_cust_flag := 'N';
228
229 END IF;
230
231 EXCEPTION
232 WHEN l_emp_not_found THEN
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 x_msg_count := 1;
235 x_msg_data := 'PA_ALL_WARN_NO_EMPL_REC';
236
237 WHEN OTHERS THEN
238 x_mcb_flag := null;
239 x_user_id := null;
240 x_login_id := null;
241 x_person_id := null;
242 x_yes_m := null;
243 x_no_m := null;
244 x_na_m := null;
245 x_employee_name := null;
246 X_fs_approve := null;
247 X_prj_closed_flag := null;
248 x_dist_warn_flag := null;
249 x_org_id := null;
250 x_multi_cust_flag := null;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 x_msg_count := 1;
253 x_msg_data := SUBSTR(SQLERRM,1,100);
254
255 END get_inv_global_value;
256
257
258
259
260 PROCEDURE Approve_info_commit
261 ( p_project_id IN NUMBER,
262 p_draft_invoice_num IN NUMBER,
263 P_user_id IN NUMBER,
264 p_person_id IN NUMBER,
265 p_login_id IN NUMBER,
266 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
267 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
268 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
269 ) IS
270
271
272 l_approved_date date;
273
274
275 BEGIN
276
277
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279 x_msg_count := 0;
280 x_msg_data := NULL;
281
282
283 /* -----------------------------------------------------------------------------
284 get the approved date if the approved date is not null then raise the error
285 otherwise approve the invoice.
286 ------------------------------------------------------- */
287
288
289 SELECT approved_date
290 INTO l_approved_date
291 FROM pa_draft_invoices_all
292 WHERE project_id = p_project_id
293 AND draft_invoice_num = p_draft_invoice_num;
294
295
296 /* -------------------------------------------------------
297 Update Approve Information to the invoice header table
298 ------------------------------------------------------- */
299
300
301 IF (l_approved_date is null ) THEN
302
303 UPDATE pa_draft_invoices_all
304 SET last_update_date = sysdate,
305 Last_updated_by = p_user_id,
306 last_update_login = p_login_id,
307 approved_date = TRUNC(sysdate),
308 approved_by_person_id = p_person_id
309 WHERE project_id = p_project_id
310 AND draft_invoice_num = p_draft_invoice_num ;
311
312 ELSE
313
314 x_return_status := FND_API.G_RET_STS_ERROR;
315 x_msg_data := 'PA_XC_RECORD_CHANGED';
316 x_msg_count := 1;
317
318 END IF;
319
320
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
325 x_msg_count := 1;
326 x_msg_data := SUBSTR(SQLERRM,1,100);
327
328 END Approve_info_commit;
329
330
331 PROCEDURE Get_invoice_mode
332 ( p_project_id IN NUMBER,
333 p_draft_invoice_num IN NUMBER,
334 p_inv_line_num IN NUMBER,
335 p_event_task_id IN NUMBER,
336 p_event_num IN NUMBER,
337 p_retn_inv_flag IN VARCHAR2,
338 p_inv_items_line_type IN VARCHAR2,
339 x_invoice_mode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
340 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
341 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
342 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
343 ) IS
344
345
346 l_event_type varchar2(30) ;
347
348 BEGIN
349
350
351 l_event_type := null;
352
353
354 x_return_status := FND_API.G_RET_STS_SUCCESS;
355 x_msg_count := 0;
356 x_msg_data := NULL;
357
358
359
360 IF (NVL(p_retn_inv_flag,'N') = 'Y' ) THEN
361
362 x_invoice_mode := 'RETENTION';
363
364 ELSE
365
366 IF ((p_event_num IS NOT NULL) AND (NVL(p_inv_items_line_type,'A') <> 'RETENTION')) THEN
367
368 /* Get the event Type from event type classification */
369
370 SELECT et.event_type_classification
371 INTO l_event_type
372 FROM pa_event_types et, pa_events ev
373 WHERE et.event_type = ev.event_type
374 AND ev.project_id = p_project_id
375 AND ev.event_num = p_event_num
376 AND nvl(ev.task_id,0) = nvl(p_event_task_id,0);
377
378
379 IF (l_event_type = 'SCHEDULED PAYMENTS') THEN
380
381 x_invoice_mode := 'FROM-EI';
382
383 ELSE
384
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 x_msg_count := 1;
387 x_msg_data := 'PA_IN_NO_FIFO_ITEMS';
388
389 END IF;
390
391 ELSE
392
393
394 IF (nvl(p_inv_items_line_type, 'A') <> 'RETENTION') THEN
395
396 x_invoice_mode := 'FROM-RDL';
397
398 ELSE
399
400 x_invoice_mode := 'FROM-RDL-RETN';
401
402 END IF;
403
404 END IF;
405
406
407 END IF;
408
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 x_msg_count := 1;
414 x_msg_data := SUBSTR(SQLERRM,1,100);
415
416 END Get_invoice_mode;
417
418
419 Procedure Validate_Approval ( P_Project_ID in number,
420 P_Draft_Invoice_Num in number,
421 P_Validation_Level in varchar2,
422 X_Error_Message_Code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
423
424 l_customer_id pa_draft_invoices_v.customer_id%TYPE;
425 l_generation_error_flag pa_draft_invoices_v.generation_error_flag%TYPE;
426 l_approved_date pa_draft_invoices_v.approved_date%TYPE;
430
427 l_project_status_code pa_draft_invoices_v.project_status_code%TYPE;
428 l_dummy number;
429 l_err_msg_code varchar2(30);
431 Cursor Inv_Cur is
432
433 SELECT i.customer_id, i.generation_error_flag,
434 i.approved_date , p.project_status_code
435 FROM pa_draft_invoices_all i, /* changed the refrence to base table instead of pa_draft_invoices_all_v for bug # 4666256 */
436 pa_projects_all p /* added the refrence to pa_projects_all for project_status_code */
437 WHERE i.project_id = P_Project_ID
438 AND p.project_id = i.project_id
439 AND i.draft_invoice_num = P_Draft_Invoice_Num;
440
441 /* Commented and rewritten for tCA changes
442 Cursor Cust_Cur is
443 SELECT 1
444 FROM RA_CUSTOMERS
445 WHERE customer_id = l_customer_id
446 AND NVL(status, 'A') <> 'A'
447 AND customer_prospect_code = 'CUSTOMER'; */
448
449 Cursor Cust_Cur is
450 SELECT 1
451 FROM HZ_CUST_ACCOUNTS
452 WHERE cust_account_id = l_customer_id
453 AND NVL(status, 'A') <> 'A';
454
455 BEGIN
456 -- Reset Output Parameters
457 X_Error_Message_Code := NULL;
458
459 IF P_Validation_Level = 'R' THEN /* Record Level Validation */
460
461 OPEN Inv_Cur;
462 FETCH Inv_Cur into l_customer_id, l_generation_error_flag,
463 l_approved_date,l_project_status_code;
464 CLOSE Inv_Cur;
465
466 /* Check Project Status */
467 /* Remove Project Status Check as discussed
468 IF (PA_Project_Stus_Utils.Is_Project_Status_Closed(l_project_status_code)
469 = 'Y') THEN
470 X_Error_Message_Code := 'PA_EX_CLOSED_PROJECT';
471 GOTO all_done;
472 END IF;*/
473
474 /* Bug#1499480:Check whether action 'GENERATE_INV' is enabled for Project Status */
475 IF (PA_Project_Utils.Check_Prj_Stus_Action_Allowed(l_project_status_code,'GENERATE_INV')
476 = 'N') THEN
477 X_Error_Message_Code := 'PA_INV_ACTION_INVALID';
478 GOTO all_done;
479 END IF;
480
481 /* Check Generation Error */
482 IF (l_generation_error_flag = 'Y') THEN
483 X_Error_Message_Code := 'PA_IN_NO_APP_GEN_ERR';
484 GOTO all_done;
485 END IF;
486
487 /* Check Invoice Status */
488 IF (l_approved_date is not NULL) THEN
489 X_Error_Message_Code := 'PA_IN_ALREADY_APPROVED';
490 GOTO all_done;
491 END IF;
492
493 /* Check Customer Status */
494 l_dummy := 0;
495 OPEN Cust_Cur;
496 Fetch Cust_Cur into l_dummy;
497 CLOSE Cust_Cur;
498
499 IF (l_dummy > 0) THEN
500 X_Error_Message_Code := 'PA_EX_INACTIVE_CUSTOMER';
501 GOTO all_done;
502 END IF;
503
504
505 ELSIF P_Validation_Level = 'C' THEN /* Commit Level Validation */
506
507 NULL;
508
509 END IF; /* Validation Level Checks */
510
511 <<all_done>>
512 NULL;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 X_Error_Message_Code := NULL;
517 RAISE;
518
519 END Validate_Approval;
520
521
522
523 PROCEDURE validate_multi_Customer ( P_Invoice_Set_ID in number,
524 X_Error_Message_Code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
525
526
527 l_dummy number;
528
529 BEGIN
530
531 X_Error_Message_Code := NULL;
532 l_dummy := 0;
533
534 SELECT count(*)
535 INTO l_dummy
536 FROM PA_Draft_Invoices_ALL i
537 WHERE i.invoice_set_id = P_Invoice_Set_ID
538 AND i.customer_bill_split not in (0, 100)
539 AND i.approved_date is null;
540
541 IF (l_dummy > 1) THEN
542 X_Error_Message_Code := 'PA_INV_APPROVE_MULTI_CUST';
543 END IF;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547
548 /* ATG Changes */
549 X_Error_Message_Code := NULL;
550 RAISE;
551
552 END validate_multi_Customer;
553
554 Procedure Validate_multi_invoices (
555 P_Project_ID in number,
556 P_invoice_set_id in number,
557 P_Validation_Level in varchar2,
558 X_Error_Message_Code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
559
560 l_inactive_customer number;
561 l_err_msg_code varchar2(30);
562 l_fst_flag varchar2(1);
563
564 Cursor Inv_Cur is
565 SELECT project_id, draft_invoice_num, customer_id, approved_date
566 FROM pa_draft_invoices_all --For Bug 3961053
567 -- FROM pa_draft_invoices_all_v --For Bug 3961053
568 WHERE project_id = P_Project_ID
569 AND invoice_set_id = P_invoice_set_id
570 and nvl(customer_bill_split,0) not in (0,100);
571
572 BEGIN
573 -- Reset Output Parameters
574
575 X_Error_Message_Code := NULL;
576 l_fst_flag := 'Y';
577
578 IF P_Validation_Level = 'R' THEN /* Record Level Validation */
579
580 FOR inv_rec IN Inv_Cur LOOP
581
582 IF l_fst_flag = 'Y' THEN
583
584 l_fst_flag := 'N';
585 Validate_Approval ( P_Project_ID => inv_rec.project_id,
586 P_Draft_Invoice_Num => inv_rec.draft_invoice_num,
590 if l_err_msg_code is not null then
587 P_Validation_Level => p_validation_level ,
588 X_Error_Message_Code => l_err_msg_code);
589
591 x_error_message_code := l_err_msg_code;
592 exit;
593 end if;
594
595 END IF;
596
597 IF inv_rec.approved_date IS NOT NULL THEN
598 X_Error_Message_Code := 'PA_IN_ALREADY_APPROVED';
599 ELSE
600 /* Commented and rewritten for TCA changes
601 SELECT count(*) INTO l_inactive_customer FROM ra_customers
602 WHERE customer_id = inv_rec.customer_id
603 AND NVL(status,'A') <> 'A'
604 AND customer_prospect_code = 'CUSTOMER'; */
605
606 SELECT count(*) INTO l_inactive_customer FROM hz_cust_accounts
607 WHERE cust_account_id = inv_rec.customer_id
608 AND NVL(status,'A') <> 'A';
609
610 IF l_inactive_customer >0 THEN
611 X_Error_Message_Code := 'PA_EX_INACTIVE_CUSTOMER';
612 END IF;
613
614 END If;
615 IF x_error_message_code IS NOT NULL THEN
616 EXIT;
617 END IF;
618 END LOOP;
619
620 ELSIF P_Validation_Level = 'C' THEN /* Commit Level Validation */
621
622 NULL;
623
624 END IF; /* Validation Level Checks */
625
626
627 EXCEPTION
628 WHEN OTHERS THEN
629
630 /* ATG Changes */
631 X_Error_Message_Code := NULL;
632
633 RAISE;
634
635 END Validate_multi_invoices;
636
637
638 PROCEDURE Approve_multi_commit
639 ( p_project_id IN NUMBER,
640 p_invoice_set_id IN NUMBER,
641 P_user_id IN NUMBER,
642 p_person_id IN NUMBER,
643 p_login_id IN NUMBER,
644 x_app_draft_num OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
645 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
646 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
647 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
648 ) IS
649
650
651 l_approved_count number;
652 l_app_draft_num varchar2(30);
653
654 Cursor unappr_cur is
655 select draft_invoice_num
656 FROM pa_draft_invoices_all
657 WHERE project_id = P_Project_ID
658 AND invoice_set_id = p_invoice_set_id
659 and nvl(customer_bill_split,0) not in (0,100);
660
661 BEGIN
662
663
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665 x_msg_count := 0;
666 x_msg_data := NULL;
667 x_app_draft_num := NULL;
668 l_app_draft_num := NULL;
669
670
671
672 /* -----------------------------------------------------------------------------
673 get the approved date if the approved date is not null then raise the error
674 otherwise approve the invoice.
675 ------------------------------------------------------- */
676
677
678 SELECT count(*)
679 INTO l_approved_count
680 FROM pa_draft_invoices_all
681 WHERE project_id = p_project_id
682 AND invoice_set_id = p_invoice_set_id
683 AND approved_date is not null
684 AND nvl(customer_bill_split,0) not in (0,100);
685
686
687 /* -------------------------------------------------------
688 Update Approve Information to the invoice header table
689 ------------------------------------------------------- */
690
691
692 IF (l_approved_count = 0 ) THEN
693
694 for unappr_rec in unappr_cur loop
695
696 if l_app_draft_num is not null then
697 l_app_draft_num := l_app_draft_num ||',';
698 end if;
699 l_app_draft_num := l_app_draft_num || trim(to_char(unappr_rec.draft_invoice_num));
700
701 UPDATE pa_draft_invoices_all
702 SET last_update_date = sysdate,
703 Last_updated_by = p_user_id,
704 last_update_login = p_login_id,
705 approved_date = TRUNC(sysdate),
706 approved_by_person_id = p_person_id
707 WHERE project_id = p_project_id
708 AND draft_invoice_num = unappr_rec.draft_invoice_num;
709 end loop;
710 x_app_draft_num := l_app_draft_num;
711
712 ELSE
713
714 x_return_status := FND_API.G_RET_STS_ERROR;
715 x_msg_data := 'PA_XC_RECORD_CHANGED';
716 x_msg_count := 1;
717
718 END IF;
719
720
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725 x_msg_count := 1;
726 x_msg_data := SUBSTR(SQLERRM,1,100);
727
728 /* ATG Changes */
729 x_app_draft_num := NULL;
730 END Approve_multi_commit;
731
732 END pa_bill_workbench_invoice;