1 PACKAGE BODY AP_MATCH_UTILITIES_PUB AS
2 /* $Header: aprmtutb.pls 120.0.12010000.3 2008/08/14 18:52:50 bgoyal 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 /* Added the Hold Exists for bug#7203269 in the Select Query*/
68 l_sql_stmt := 'SELECT count(*)
69 FROM po_headers ph,
70 po_distributions pd,
71 po_releases pr,
72 ap_invoice_distributions aid,
73 ap_invoices ai
74 WHERE ph.po_header_id = :b_po_header_id
75 AND ph.po_header_id = pd.po_header_id
76 AND pd.po_release_id = pr.po_release_id(+)
77 AND pd.po_distribution_id = aid.po_distribution_id
78 AND aid.invoice_id = ai.invoice_id
79 AND ( exists (select ''hold''
80 from ap_holds_all ah
81 where ai.invoice_id = ah.invoice_id
82 AND ah.release_lookup_code is null)
83 OR exists (select ''unvalidated dist''
84 from ap_invoice_distributions_all aid2
85 where ai.invoice_id = aid2.invoice_id
86 and nvl(aid2.match_status_flag, ''N'') <> ''A'')) ';
87
88
89
90 If p_invoice_type = 'INVOICE' Then
91
92 l_sql_stmt := l_sql_stmt || ' AND ai.invoice_amount > 0';
93
94 Elsif p_invoice_type = 'CREDIT' Then
95
96 l_sql_stmt := l_sql_stmt || ' AND ai.invoice_amount < 0';
97
98 End If;
99
100 If p_invoice_id Is Not Null Then
101 l_sql_stmt := l_sql_stmt || ' AND ai.invoice_id <> :b_invoice_id';
102 End If;
103
104
105
106 If p_po_release_id Is Not Null Then
107 l_sql_stmt := l_sql_stmt || ' AND pr.po_release_id = nvl('||p_po_release_id||', pr.po_release_id) ';
108 End If;
109
110 If p_po_line_id Is Not Null Then
111
112 l_sql_stmt := l_sql_stmt || ' AND pd.po_line_id = :b_line_id AND rownum = 1';
113
114 If p_invoice_id Is Not Null Then
115 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_invoice_id, p_po_line_id;
116 Else
117 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_po_line_id;
118 End If;
119
120 Elsif p_line_location_id Is Not Null Then
121
122 l_sql_stmt := l_sql_stmt || ' AND pd.line_location_id = :b_line_location_id AND rownum = 1';
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_line_location_id;
125 Else
126 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_line_location_id;
127 End If;
128
129 Elsif p_po_distribution_id Is Not Null Then
130
131 l_sql_stmt := l_sql_stmt || ' AND pd.po_distribution_id = :b_po_distribution_id AND rownum = 1';
132
133 If p_invoice_id Is Not Null Then
134 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_invoice_id, p_po_distribution_id;
135 Else
136 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id, p_po_distribution_id;
137 End If;
138
139 Elsif p_po_line_id Is Null And p_line_location_id Is Null And p_po_distribution_id Is Null Then
140 l_sql_stmt := l_sql_stmt || ' 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;
144 Else
145 Execute Immediate l_sql_stmt INTO l_status USING p_po_header_id;
146 End If;
147
148 End If;
149 /* Added the if condition for bug#7203269 */
150 If l_status > 0 Then
151 RETURN (TRUE);
152 Else
153 RETURN (FALSE);
154 End If;
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158
159 RETURN FALSE;
160
161 WHEN OTHERS THEN
162
163 IF (SQLCODE <> -20001) THEN
164 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
165 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
166 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
167 FND_MESSAGE.SET_TOKEN('PARAMETERS',
168 ' P_invoice_type = ' || P_invoice_type
169 ||' P_po_header_id = ' || P_po_header_id
170 ||' P_po_line_id = ' || P_po_line_id
171 ||' P_line_location_id = ' || P_line_location_id
172 ||' P_po_distribution_id = ' || P_po_distribution_id);
173
174 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
175 END IF;
176 APP_EXCEPTION.RAISE_EXCEPTION;
177
178 END Check_Unvalidated_Invoices;
179
180 END AP_MATCH_UTILITIES_PUB;
[Home] [Help]