1 PACKAGE BODY PA_AP_VAL_PKG AS
2 -- /* $Header: PAAPVALB.pls 120.0.12010000.5 2010/03/31 13:24:59 sesingh noship $ */
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 g_api_name VARCHAR2(30) :='Validate_unprocessed_ded';
6
7 -- This procedure is for logging debug messages
8 Procedure log_message (p_log_msg IN VARCHAR2,p_proc_name VARCHAR2) ;
9
10 PROCEDURE log_message (p_log_msg IN VARCHAR2,p_proc_name VARCHAR2) IS
11 BEGIN
12 pa_debug.write('log_message: ' || p_proc_name, 'log: ' || p_log_msg, 3);
13 END log_message;
14
15 /*---------------------------------------------------------------------------------------------------------
16 -- This procedure is to validate a Retainage Release invoice in payables. This is called from Payables
17 -- Input parameters
18 -- Parameters Type Required Description
19 -- invoice_id NUMBER YES invoice_id being validated
20 -- cmt_exist_flag VARCHAR returns whether unprocessed dedns exist
21 ----------------------------------------------------------------------------------------------------------*/
22 Procedure validate_unprocessed_ded ( invoice_id IN ap_invoices_all.invoice_id%type,
23 cmt_exist_flag OUT NOCOPY VARCHAR2)
24 IS
25
26 --Cursor check_cmt_exist will check the number of deductions that have not yet
27 --resulted in a debit memo in payables. It will include the deductions
28 --in working,submitted statuses as well.
29
30
31 cursor check_cmt_exist(p_invoice_id ap_invoices_all.invoice_id%type) is
32 select 1 from pa_deductions_all
33 where not exists
34 (select 1 from ap_invoices_all
35 where source = 'Oracle Project Accounting'
36 AND invoice_type_lookup_code = 'DEBIT'
37 AND product_table='PA_DEDUCTIONS_ALL'
38 and deduction_req_id = to_number(reference_key1))
39 and (vendor_id, project_id) in (select inv.vendor_id, apd.project_id
40 from ap_invoices_all inv, ap_invoice_distributions_all apd
41 where inv.invoice_id =p_invoice_id
42 and inv.invoice_id=apd.invoice_id);
43
44
45 l_invoice_id ap_invoices_all.invoice_id%type;
46 l_count NUMBER;
47
48 BEGIN
49
50 l_invoice_id := invoice_id;
51 IF P_DEBUG_MODE = 'Y' THEN
52 log_message ('Entered validate_unprocessed_ded with invoice_id = '||l_invoice_id,g_api_name);
53 END IF;
54
55 open check_cmt_exist(l_invoice_id);
56 fetch check_cmt_exist into l_count;
57 close check_cmt_exist;
58
59 IF P_DEBUG_MODE = 'Y' THEN
60 log_message ('Value of l_count in validate_unprocessed_ded = '||l_count,g_api_name);
61 END IF;
62
63 if l_count > 0 then
64 cmt_exist_flag := 'Y'; -- return Y, because unprocessed deductions exist
65 else
66 cmt_exist_flag := 'N'; -- return N, no unprocessed deductions exist
67 end if;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 RAISE;
72
73 end validate_unprocessed_ded;
74
75 END PA_AP_VAL_PKG ;
[Home] [Help]