1 PACKAGE BODY AP_MATCH_UTILITIES_PUB AS
2 /* $Header: aprmtutb.pls 120.4 2011/03/17 13:59:54 sbonala ship $ */
3
4 /*=============================================================================
5 | PUBLIC FUNCTION Check_Unvalidated_Invoices
6 |
7 | DESCRIPTION
8 | The function will Return 'TRUE' if there are unvalidated payables
9 | documents matched to a Purchase Order based on the input parameters.
10 |
11 | USAGE
12 | p_invoice_type and p_po_header_id are required parameters.
13 | Call this function, when the user unreserves funds for the PO.
14 | Unreserve from PO Header, pass p_po_line_id, p_line_location_id, p_po_distribution_id AS NULL
15 | Unreserve from PO Line, pass p_line_location_id and p_po_distribution_id AS NULL
16 | Unreserve from PO Shipment, pass p_po_distribution_id AS NULL
17 | Unreserve from PO Distribution, pass all parameters.
18 |
19 | This function can also be used to prevent 'Final Close' of a PO if there are unvalidated
20 | invoices matched to it.
21 |
22 | Parameter p_invoice_id
23 | ----------------------
24 | A special case during matching, is when a user indicates that it is a 'Final Match'. During
25 | invoice validation, Payables invokes po_actions.close_po() to 'Final Close' the PO.
26 |
27 | When po_actions.close_po() is invoked as a result of 'Final Match', pass p_invoice_id
28 | to skip the check for the invoice doing the final match. Otherwise, we will not be
29 | able to final match or close the PO.
30 |
31 | RETURNS
32 | TRUE if there are unvalidated invoices, credit or debit memos.
33 |
34 | PARAMETERS
35 | p_invoice_type IN Required parameter. Values: 'INVOICE' or 'CREDIT'
36 | p_po_header_id IN Required parameter. PO Header Identifier.
37 | p_po_line_id IN PO Line Identifier
38 | p_line_location_id IN PO Shipment Identifier
39 | p_po_distribution_id IN PO Distribution Identifier
40 | p_invoice_id IN Invoice Identifier
41 | p_calling_sequence IN Calling module (package_name.procedure or block_name.field_name)
42 |
43 | MODIFICATION HISTORY
44 | Date Author Description of Changes
45 |
46 *=============================================================================*/
47
48 FUNCTION Check_Unvalidated_Invoices(p_invoice_type IN VARCHAR2 DEFAULT 'BOTH',
49 p_po_header_id IN NUMBER,
50 p_po_release_id IN NUMBER DEFAULT NULL,
51 p_po_line_id IN NUMBER DEFAULT NULL,
52 p_line_location_id IN NUMBER DEFAULT NULL,
53 p_po_distribution_id IN NUMBER DEFAULT NULL,
54 p_invoice_id IN NUMBER DEFAULT NULL,
55 p_calling_sequence IN VARCHAR2)
56 RETURN BOOLEAN IS
57
58 l_status Number;
59 l_debug_info Varchar2(240);
60 l_curr_calling_sequence Varchar2(2000);
61 l_sql_stmt Varchar2(2000);
62
63 BEGIN
64
65 l_curr_calling_sequence := 'Ap_Match_Utilities_Pub.Check_Unvalidated_Invoices<-' || p_calling_sequence;
66
67 --Modified Match_status_flag conditions for bug#10396600
68 --added reverse_flag condition for bug#10283059
69 /* Added the Hold Exists for bug#7203269 in the Select Query*/
70 l_sql_stmt := 'SELECT count(*)
71 FROM po_headers ph,
72 po_distributions pd,
73 po_releases pr,
74 ap_invoice_distributions aid,
75 ap_invoices ai
76 WHERE ph.po_header_id = :b_po_header_id
77 AND ph.po_header_id = pd.po_header_id
78 AND pd.po_release_id = pr.po_release_id(+)
79 AND pd.po_distribution_id = aid.po_distribution_id
80 AND aid.invoice_id = ai.invoice_id
81 AND nvl(aid.reversal_flag,''N'') <> ''Y''
82 AND ( exists (select ''hold''
83 from ap_holds_all ah
84 where ai.invoice_id = ah.invoice_id
85 and ah.release_lookup_code is null)
86 OR exists (select ''unvalidated dist''
87 from ap_invoice_distributions_all aid2,
88 financials_system_params_all fsp
89 where ai.invoice_id = aid2.invoice_id
90 and fsp.org_id = ai.org_id
91 and fsp.set_of_books_id = ai.set_of_books_id
92 and ( (nvl(fsp.purch_encumbrance_flag,''N'') = ''Y'' and
93 nvl(aid2.match_status_flag, ''N'') <> ''A'')
94 or (nvl(fsp.purch_encumbrance_flag,''N'') = ''N'' and
95 nvl(aid2.match_status_flag, ''N'') not in (''A'',''T''))
96 )
97 ))';
98
99 If p_invoice_type = 'INVOICE' Then
100
101 l_sql_stmt := l_sql_stmt || ' AND ai.invoice_amount > 0';
102
103 Elsif p_invoice_type = 'CREDIT' Then
104
105 l_sql_stmt := l_sql_stmt || ' AND ai.invoice_amount < 0';
106
107 End If;
108
109 If p_invoice_id Is Not Null Then
110 l_sql_stmt := l_sql_stmt || ' AND ai.invoice_id <> :b_invoice_id';
111 End If;
112
113
114
115 If p_po_release_id Is Not Null Then
116 l_sql_stmt := l_sql_stmt || ' AND pr.po_release_id = nvl('||p_po_release_id||', pr.po_release_id) ';
117 End If;
118
119 If p_po_line_id Is Not Null Then
120
121 l_sql_stmt := l_sql_stmt || ' AND pd.po_line_id = :b_line_id AND rownum = 1';
122
123 If p_invoice_id Is Not Null Then
124 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_invoice_id, p_po_line_id;
125 Else
126 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_po_line_id;
127 End If;
128
129 Elsif p_line_location_id Is Not Null Then
130
131 l_sql_stmt := l_sql_stmt || ' AND pd.line_location_id = :b_line_location_id AND rownum = 1';
132 If p_invoice_id Is Not Null Then
133 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_invoice_id, p_line_location_id;
134 Else
135 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_line_location_id;
136 End If;
137
138 Elsif p_po_distribution_id Is Not Null Then
139
140 l_sql_stmt := l_sql_stmt || ' AND pd.po_distribution_id = :b_po_distribution_id AND rownum = 1';
141
142 If p_invoice_id Is Not Null Then
143 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_invoice_id, p_po_distribution_id;
144 Else
145 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_po_distribution_id;
146 End If;
147
148 Elsif p_po_line_id Is Null And p_line_location_id Is Null And p_po_distribution_id Is Null Then
149 l_sql_stmt := l_sql_stmt || ' AND rownum = 1';
150
151 If p_invoice_id Is Not Null Then
152 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_invoice_id;
153 Else
154 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id;
155 End If;
156
157 End If;
158 /* Added the if condition for bug#7203269 */
159 If l_status > 0 Then
160 RETURN (TRUE);
161 Else
162 RETURN (FALSE);
163 End If;
164
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167
168 RETURN FALSE;
169
170 WHEN OTHERS THEN
171
172 IF (SQLCODE <> -20001) THEN
173 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
174 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
175 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
176 FND_MESSAGE.SET_TOKEN('PARAMETERS',
177 ' P_invoice_type = ' || P_invoice_type
178 ||' P_po_header_id = ' || P_po_header_id
179 ||' P_po_line_id = ' || P_po_line_id
180 ||' P_line_location_id = ' || P_line_location_id
181 ||' P_po_distribution_id = ' || P_po_distribution_id);
182
183 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
184 END IF;
185 APP_EXCEPTION.RAISE_EXCEPTION;
186
187 END Check_Unvalidated_Invoices;
188
189 END AP_MATCH_UTILITIES_PUB;
[Home] [Help]