1 PACKAGE BODY po_invoice_hold_check AS
2 /* $Header: PO_INVOICE_HOLD_CHECK.plb 120.5 2010/10/04 14:32:56 vlalwani ship $ */
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 -- This procedure is called by AP to apply Deliverable holds on the invoices.
120
121 --Parameters:
122 --IN:
123 --p_api_version
124 -- Version number of API that caller expects. It
125 -- should match the l_api_version defined in the
126 -- procedure (expected value : 1.0)
127 --p_po_header_id
128 -- Header id of the Purchase Order.
129 --p_invoice_id
130 -- Invoice id of the Invoice that is being validated.
131 --
132 -- OUT PARAMETERS
133 -- x_return_status
134 -- FND_API.G_RET_STS_SUCCESS if API succeeds
135 -- FND_API.G_RET_STS_ERROR if API fails
136 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
137 -- x_msg_count
138 -- x_msg_data
139 --
140 -- IN OUT PARAMETERS
141 -- X_HOLD_REQUIRED
142 -- Return 'Y' when any of the deliverables are overdue, else return 'N'.
143
144 PROCEDURE DELIVERABLE_OVERDUE_CHECK
145 (P_API_VERSION IN NUMBER,
146 P_PO_HEADER_ID IN NUMBER,
147 P_INVOICE_ID IN NUMBER,
148 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
149 X_MSG_COUNT OUT NOCOPY NUMBER,
150 X_MSG_DATA OUT NOCOPY VARCHAR2,
151 X_HOLD_REQUIRED IN OUT NOCOPY VARCHAR2)
152 IS
153 l_api_version NUMBER := 1.0;
154 l_api_name VARCHAR2(60) := 'DELIVERABLE_OVERDUE_CHECK';
155 l_log_head CONSTANT VARCHAR2(100) := g_log_head
156 ||l_api_name;
157 l_msg_count NUMBER;
158 l_msg_data VARCHAR2(2000);
159 l_return_status_okc VARCHAR2(1);
160 d_progress VARCHAR2(3) := '000';
161 l_pay_when_paid VARCHAR2(1);
162 l_rev_num NUMBER;
163 l_type_lookup_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
164 l_deliverable_hold_flag VARCHAR2(10);
165 d_module VARCHAR2(70) := 'po.plsql.PO_INVOICE_HOLD_CHECK.deliverable_overdue_check';
166 BEGIN
167
168 -- Check for the API version
169 IF (NOT fnd_api.compatible_api_call(l_api_version,p_api_version,l_api_name,g_pkg_name)) THEN
170 x_return_status := fnd_api.g_ret_sts_unexp_error;
171 RETURN;
172 END IF;
173
174 d_progress := '001';
175 IF (PO_LOG.d_stmt) THEN
176 PO_LOG.stmt(d_module, d_progress, 'API Compatible call success');
177 END IF;
178
179 -- Initialize API return status to success
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181
182 d_progress := '002';
183
184 /* Bug 8204164 - The below query was initially checking for the document type = 'STANDARD'.
185 Changed that to query all type_lookup_codes as this proc is called \
186 from AP for all types of POs.
187 Added fnd debug messages as well */
188
189 /* SELECT NVL(pay_when_paid, 'N'),
190 revision_num,
191 type_lookup_code
192 INTO l_pay_when_paid,
193 l_rev_num,
194 l_type_lookup_code
195 FROM po_headers_all
196 WHERE po_header_id = p_po_header_id; */
197
198 /* E and C Phase 2 changes -
199 Deliverable Hold control does not depend on the pay when paid attribute.
200 This depends on the document style attribute deliverable_hold_flag.
201 One of the attributes mentioned above should have a value 'Y', if this is satisfied,
202 we call the OKC API to find out the overdue status of the deliverables.
203 Commented the above sql and added the complete sql below. */
204
205 SELECT NVL(poh.pay_when_paid, 'N'),
206 poh.revision_num,
207 poh.type_lookup_code,
208 Nvl(ps.deliverable_hold_flag, 'N')
209 INTO l_pay_when_paid,
210 l_rev_num,
211 l_type_lookup_code,
212 l_deliverable_hold_flag
213 FROM po_doc_style_headers ps, po_headers_all poh
214 WHERE poh.po_header_id = p_po_header_id
215 AND poh.style_id = ps.style_id;
216
217
218 IF l_type_lookup_code <> 'STANDARD'
219 THEN
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 X_HOLD_REQUIRED := 'N';
222 END IF;
223
224 IF (PO_LOG.d_stmt) THEN
225 PO_LOG.stmt(d_module, d_progress, 'Pay when paid and Deliverable hold flag query executed');
226 END IF;
227
228 -- E and C Phase 2 changes -
229 -- Commented the earlier IF and added the deliverable hold flag condition to the same below */
230
231 IF (l_pay_when_paid = 'Y' OR l_deliverable_hold_flag = 'Y') THEN
232
233 /* IF l_pay_when_paid = 'Y' THEN */
234 --Call OKC API to get the hold related information.
235
236 d_progress := '003';
237 IF (PO_LOG.d_stmt) THEN
238 PO_LOG.stmt(d_module, d_progress, 'Before Calling OKC code to find contract deliverable hold info');
239 END IF;
240
241 OKC_MANAGE_DELIVERABLES_GRP.applypaymentholds
242 (p_api_version => l_api_version,
243 p_bus_doc_id => p_po_header_id,
244 p_bus_doc_version => l_rev_num,
245 x_msg_data => l_msg_data,
246 x_msg_count => l_msg_count,
247 x_return_status => l_return_status_okc);
248
249 d_progress := '004';
250 IF (PO_LOG.d_stmt) THEN
251 PO_LOG.stmt(d_module, d_progress, 'After Calling OKC code to find contract deliverable hold info');
252 END IF;
253 IF l_return_status_okc = fnd_api.g_true THEN
254 x_hold_required := 'Y';
255 x_return_status := fnd_api.g_ret_sts_success;
256 ELSIF l_return_status_okc = fnd_api.g_false THEN
257 x_hold_required := 'N';
258 x_return_status := fnd_api.g_ret_sts_success;
259 ELSIF l_return_status_okc = fnd_api.g_ret_sts_error THEN
260 x_return_status := fnd_api.g_ret_sts_error;
261 ELSE
262 x_return_status := fnd_api.g_ret_sts_unexp_error;
263 END IF;
264 ELSE
265 d_progress := '005';
266 -- No need to call OKC. This PO does not have 'Pay when Paid' or 'Deliverable Hold flag' attribute marked.
267 IF (PO_LOG.d_stmt) THEN
268 PO_LOG.stmt(d_module, d_progress, 'No need to call OKC as Pay When Paid/Deliverable Hold flag is N or NULL');
269 END IF;
270 x_hold_required := 'N';
271 END IF;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 x_return_status := fnd_api.g_ret_sts_unexp_error;
276 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
277 END deliverable_overdue_check;
278 --------------------------------------------------------------------------------
279 --Start of Comments
280 --Name: DELIVERABLE_HOLD_CONTROL
281 --Pre-reqs:
282 -- None.
283 --Modifies:
284 -- None.
285 --Locks:
286 -- None.
287 --Procedure:
288 -- This procedure returns information about whether the 'Initiate Payment Holds'
289 -- region should be rendered for the deliverables on a given Standard PO.
290 -- It is called from Contracts at the time of rendering the Create/Update Deliverables Pg.
291 --Parameters:
292 --IN:
293 --p_api_version
294 -- Version number of API that caller expects. It
295 -- should match the l_api_version defined in the
296 -- procedure (expected value : 1.0)
297 --p_po_header_id
298 -- The header id of the Purchase Order.
299
300 -- OUT PARAMETERS
301 -- x_return_status
302 -- FND_API.G_RET_STS_SUCCESS if API succeeds
303 -- FND_API.G_RET_STS_ERROR if API fails
304 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
305 -- x_msg_count
306 -- x_msg_data
307 --
308 -- IN OUT PARAMETERS
309 -- X_RENDER_DELIVERABLE_HOLD
310 -- Return 'Y' when the Deliverable Hold Flag on the PO Style is 'Y' or pay when paid on the PO is 'Yes', else return 'N'.
311 --End of Comments
312
313 PROCEDURE DELIVERABLE_HOLD_CONTROL
314 (P_API_VERSION IN NUMBER,
315 P_PO_HEADER_ID IN NUMBER,
316 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
317 X_MSG_COUNT OUT NOCOPY NUMBER,
318 X_MSG_DATA OUT NOCOPY VARCHAR2,
319 X_RENDER_DELIVERABLE_HOLD IN OUT NOCOPY VARCHAR2)
320 IS
321 l_api_version NUMBER := 1.0;
322 l_api_name VARCHAR2(60) := 'DELIVERABLE_HOLD_CONTROL';
323 l_log_head CONSTANT VARCHAR2(100) := g_log_head
324 ||l_api_name;
325 d_progress VARCHAR2(3) := '000';
326 d_module VARCHAR2(70) := 'po.plsql.PO_INVOICE_HOLD_CHECK.DELIVERABLE_HOLD_CONTROL';
327
328 BEGIN
329
330 -- Check for the API version
331 IF (NOT fnd_api.compatible_api_call(l_api_version,p_api_version,l_api_name,g_pkg_name)) THEN
332 x_return_status := fnd_api.g_ret_sts_unexp_error;
333 END IF;
334
335 d_progress := '001';
336
337 IF (PO_LOG.d_stmt) THEN
338 PO_LOG.stmt(d_module, d_progress, 'API Compatible call success');
339 END IF;
340
341 -- Initialize API return status to success
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343
344 d_progress := '002';
345
346 SELECT deliverable_hold_flag
347 INTO x_render_deliverable_hold
348 FROM (SELECT DECODE(Nvl(ps.deliverable_hold_flag, 'N'),'N',Nvl(ph.pay_when_paid, 'N'),ps.deliverable_hold_flag) deliverable_hold_flag
349 FROM po_doc_style_headers ps,
350 po_headers_all ph
351 WHERE ph.po_header_id = p_po_header_id
352 AND ph.style_id = ps.style_id
353 UNION
354 SELECT DECODE(Nvl(ps.deliverable_hold_flag, 'N'),'N',Nvl(ph.pay_when_paid, 'N'),ps.deliverable_hold_flag) deliverable_hold_flag
355 FROM po_doc_style_headers ps,
356 po_headers_draft_all ph
357 WHERE ph.draft_id = p_po_header_id
358 AND ph.style_id = ps.style_id);
359
360
361 IF (PO_LOG.d_stmt) THEN
362 PO_LOG.stmt(d_module, d_progress, 'Deliverable Hold and pay when paid query executed');
363 END IF;
364
365
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 x_return_status := fnd_api.g_ret_sts_unexp_error;
370 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
371 END DELIVERABLE_HOLD_CONTROL;
372
373 END po_invoice_hold_check;
374
375
[Home] [Help]