1 PACKAGE BODY po_invoice_hold_check AS
2 /* $Header: PO_INVOICE_HOLD_CHECK.plb 120.0.12010000.4 2009/01/29 11:45:47 vdurbhak noship $ */
3 --Debug info
4
5 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_INVOICE_HOLD_CHECK';
6 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'||g_pkg_name||'.';
7 g_debug_stmt BOOLEAN := po_debug.is_debug_stmt_on;
8 g_debug_unexp BOOLEAN := po_debug.is_debug_unexp_on;
9
10 --------------------------------------------------------------------------------
11 --Start of Comments
12 --Name: PAY_WHEN_PAID
13 --Pre-reqs:
14 -- None.
15 --Modifies:
16 -- None.
17 --Locks:
18 -- None.
19 --Procedure:
20 -- This procedure returns information about 'pay when paid' status for a standard PO.
21 -- In case the type of the PO is other than 'Standard', there is no need to
22 -- apply any hold. This proc simply returns a pay_when_paid -> 'N' to the caller.
23 --Parameters:
24 --IN:
25 --p_api_version
26 -- Version number of API that caller expects. It
27 -- should match the l_api_version defined in the
28 -- procedure (expected value : 1.0)
29 --p_po_header_id
30 -- The header id of the Purchase Order.
31 --p_invoice_id
32 -- Invoice id of the Invoice that is being validated.
33
34 -- OUT PARAMETERS
35 -- x_return_status
36 -- FND_API.G_RET_STS_SUCCESS if API succeeds
37 -- FND_API.G_RET_STS_ERROR if API fails
38 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
39 -- x_msg_count
40 -- x_msg_data
41 --
42 -- IN OUT PARAMETERS
43 -- x_pay_when_paid
44 -- Return 'Y' when the pay when paid on the PO is 'Yes', else return 'N'.
45 --End of Comments
46
47 PROCEDURE PAY_WHEN_PAID
48 (P_API_VERSION IN NUMBER,
49 P_PO_HEADER_ID IN NUMBER,
50 P_INVOICE_ID IN NUMBER,
51 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
52 X_MSG_COUNT OUT NOCOPY NUMBER,
53 X_MSG_DATA OUT NOCOPY VARCHAR2,
54 X_PAY_WHEN_PAID IN OUT NOCOPY VARCHAR2)
55 IS
56 l_api_version NUMBER := 1.0;
57 l_api_name VARCHAR2(60) := 'PAY_WHEN_PAID';
58 l_log_head CONSTANT VARCHAR2(100) := g_log_head
59 ||l_api_name;
60 d_progress VARCHAR2(3) := '000';
61 l_type_lookup_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
62 d_module VARCHAR2(70) := 'po.plsql.PO_INVOICE_HOLD_CHECK.pay_when_paid';
63
64 BEGIN
65
66 -- Check for the API version
67 IF (NOT fnd_api.compatible_api_call(l_api_version,p_api_version,l_api_name,g_pkg_name)) THEN
68 x_return_status := fnd_api.g_ret_sts_unexp_error;
69 END IF;
70
71 d_progress := '001';
72
73 IF (PO_LOG.d_stmt) THEN
74 PO_LOG.stmt(d_module, d_progress, 'API Compatible call success');
75 END IF;
76
77 -- Initialize API return status to success
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 d_progress := '002';
81
82 SELECT NVL(pay_when_paid, 'N'),
83 type_lookup_code
84 INTO x_pay_when_paid,
85 l_type_lookup_code
86 FROM po_headers_all
87 WHERE po_header_id = p_po_header_id;
88
89 IF (PO_LOG.d_stmt) THEN
90 PO_LOG.stmt(d_module, d_progress, 'pay when paid query executed');
91 END IF;
92
93 IF l_type_lookup_code <> 'STANDARD'
94 THEN
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96 x_pay_when_paid := 'N';
97 END IF;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 x_return_status := fnd_api.g_ret_sts_unexp_error;
102 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
103 END pay_when_paid;
104
105
106 --Start of Comments
107 --Name: DELIVERABLE_OVERDUE_CHECK
108 --Pre-reqs:
109 -- None.
110 --Modifies:
111 -- None.
112 --Locks:
113 -- None.
114 --Procedure:
115 -- This procedure returns a 'Y' or 'N' based on the contract deliverables' status.
116 -- In case the type of the PO in picture is other than 'Standard', there is no need to
117 -- apply any hold. This proc simply returns a hold_required -> 'N' to the caller.
118 -- In this case, we do not call OKC api which provides paymenthold information.
119
120 --Parameters:
121 --IN:
122 --p_api_version
123 -- Version number of API that caller expects. It
124 -- should match the l_api_version defined in the
125 -- procedure (expected value : 1.0)
126 --p_po_header_id
127 -- Header id of the Purchase Order.
128 --p_invoice_id
129 -- Invoice id of the Invoice that is being validated.
130 --
131 -- OUT PARAMETERS
132 -- x_return_status
133 -- FND_API.G_RET_STS_SUCCESS if API succeeds
134 -- FND_API.G_RET_STS_ERROR if API fails
135 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
136 -- x_msg_count
137 -- x_msg_data
138 --
139 -- IN OUT PARAMETERS
140 -- X_HOLD_REQUIRED
141 -- Return 'Y' when any of the deliverables are overdue, else return 'N'.
142
143 PROCEDURE DELIVERABLE_OVERDUE_CHECK
144 (P_API_VERSION IN NUMBER,
145 P_PO_HEADER_ID IN NUMBER,
146 P_INVOICE_ID IN NUMBER,
147 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
148 X_MSG_COUNT OUT NOCOPY NUMBER,
149 X_MSG_DATA OUT NOCOPY VARCHAR2,
150 X_HOLD_REQUIRED IN OUT NOCOPY VARCHAR2)
151 IS
152 l_api_version NUMBER := 1.0;
153 l_api_name VARCHAR2(60) := 'DELIVERABLE_OVERDUE_CHECK';
154 l_log_head CONSTANT VARCHAR2(100) := g_log_head
155 ||l_api_name;
156 l_msg_count NUMBER;
157 l_msg_data VARCHAR2(2000);
158 l_return_status_okc VARCHAR2(1);
159 d_progress VARCHAR2(3) := '000';
160 l_pay_when_paid VARCHAR2(1);
161 l_rev_num NUMBER;
162 l_type_lookup_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
163 d_module VARCHAR2(70) := 'po.plsql.PO_INVOICE_HOLD_CHECK.deliverable_overdue_check';
164 BEGIN
165
166 -- Check for the API version
167 IF (NOT fnd_api.compatible_api_call(l_api_version,p_api_version,l_api_name,g_pkg_name)) THEN
168 x_return_status := fnd_api.g_ret_sts_unexp_error;
169 RETURN;
170 END IF;
171
172 d_progress := '001';
173 IF (PO_LOG.d_stmt) THEN
174 PO_LOG.stmt(d_module, d_progress, 'API Compatible call success');
175 END IF;
176
177 -- Initialize API return status to success
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 d_progress := '002';
181
182 /* Bug 8204164 - The below query was initially checking for the document type = 'STANDARD'.
183 Changed that to query all type_lookup_codes as this proc is called \
184 from AP for all types of POs.
185 Added fnd debug messages as well */
186
187 SELECT NVL(pay_when_paid, 'N'),
188 revision_num,
189 type_lookup_code
190 INTO l_pay_when_paid,
191 l_rev_num,
192 l_type_lookup_code
193 FROM po_headers_all
194 WHERE po_header_id = p_po_header_id;
195
196
197
198 IF l_type_lookup_code <> 'STANDARD'
199 THEN
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201 X_HOLD_REQUIRED := 'N';
202 END IF;
203
204 IF (PO_LOG.d_stmt) THEN
205 PO_LOG.stmt(d_module, d_progress, 'pay when paid query executed');
206 END IF;
207
208
209 IF l_pay_when_paid = 'Y' THEN
210 --Call OKC API to get the hold related information.
211
212 d_progress := '003';
213 IF (PO_LOG.d_stmt) THEN
214 PO_LOG.stmt(d_module, d_progress, 'Before Calling OKC code to find contract deliverable hold info');
215 END IF;
216
217 OKC_MANAGE_DELIVERABLES_GRP.applypaymentholds
218 (p_api_version => l_api_version,
219 p_bus_doc_id => p_po_header_id,
220 p_bus_doc_version => l_rev_num,
221 x_msg_data => l_msg_data,
222 x_msg_count => l_msg_count,
223 x_return_status => l_return_status_okc);
224
225 d_progress := '004';
226 IF (PO_LOG.d_stmt) THEN
227 PO_LOG.stmt(d_module, d_progress, 'After Calling OKC code to find contract deliverable hold info');
228 END IF;
229 IF l_return_status_okc = fnd_api.g_true THEN
230 x_hold_required := 'Y';
231 x_return_status := fnd_api.g_ret_sts_success;
232 ELSIF l_return_status_okc = fnd_api.g_false THEN
233 x_hold_required := 'N';
234 x_return_status := fnd_api.g_ret_sts_success;
235 ELSIF l_return_status_okc = fnd_api.g_ret_sts_error THEN
236 x_return_status := fnd_api.g_ret_sts_error;
237 ELSE
238 x_return_status := fnd_api.g_ret_sts_unexp_error;
239 END IF;
240 ELSE
241 d_progress := '005';
242 -- No need to call OKC. This PO does not have 'Pay when Paid' term marked.
243 IF (PO_LOG.d_stmt) THEN
244 PO_LOG.stmt(d_module, d_progress, 'No need to call OKC as the Pay When Paid term is N or NULL');
245 END IF;
246 x_hold_required := 'N';
247 END IF;
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status := fnd_api.g_ret_sts_unexp_error;
252 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
253 END deliverable_overdue_check;
254 END po_invoice_hold_check;
[Home] [Help]