[Home] [Help]
PACKAGE BODY: APPS.PO_ACKNOWLEDGE_PO_GRP
Source
1 PACKAGE BODY PO_ACKNOWLEDGE_PO_GRP AS
2 /* $Header: POXGACKB.pls 120.1 2005/06/29 18:31:38 shsiung noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(50) := 'PO_ACKNOWLEDGE_PO_GRP';
5 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
6
7 -- Read the profile option that enables/disables the debug log
8 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10
11 /**
12 * Public function: Get_Po_Status_Code
13 * Requires: PO_HEADER_ID,PO_RELEASE_ID
14 * Modifies:
15 * Effects: Return the overall status of the entire order.
16 * Possible values are:
17 * 1. CANCELLED
18 * 2. FROZEN
19 * 3. ON HOLD
20 * 4. INTERNAL CHANGE
21 * 5. SUPPLIER_CHANGE_PENDING
22 * 6. ACCEPTED
23 * 7. REJECTED
24 * 8. ACKNOWLEDGED
25 * 9. PARTIALLY_ACKNOWLEDGED
26 * 10. ACK_REQUIRED
27 * 11. ''
28 */
29
30 FUNCTION Get_Po_Status_Code (
31 p_api_version IN NUMBER,
32 p_Init_Msg_List IN VARCHAR2,
33 p_po_header_id IN NUMBER,
34 p_po_release_id IN NUMBER )
35 RETURN VARCHAR2 IS
36
37 l_status_code VARCHAR2(30) := NULL;
38 l_api_name CONSTANT VARCHAR2(30) := 'GET_PO_STATUS_CODE';
39 l_api_version CONSTANT NUMBER := 1.0;
40
41
42 BEGIN
43
44 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
45 FND_MSG_PUB.initialize;
46 END IF;
47
48 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
49 l_api_name, g_pkg_name)
50 THEN
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52 END IF;
53
54 IF (g_fnd_debug = 'Y') THEN
55 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
56 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
57 l_api_name || '.invoked', 'po_header_id: ' ||
58 NVL(TO_CHAR(p_po_header_id), ' ') || ' po_release_id: ' ||
59 NVL(TO_CHAR(p_po_release_id), ' ') );
60 END IF;
61 END IF;
62
63 l_status_code := PO_ACKNOWLEDGE_PO_PVT.Get_Po_Status_Code (
64 p_api_version => 1.0,
65 p_init_msg_list => FND_API.G_FALSE,
66 p_po_header_id => p_po_header_id,
67 p_po_release_id => p_po_release_id );
68
69 IF (g_fnd_debug = 'Y') THEN
70 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
71 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
72 l_api_name || '.before return', 'status_code: ' ||
73 NVL(l_status_code, ''));
74 END IF;
75 END IF;
76
77 return l_status_code;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
82 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
83 IF (g_fnd_debug = 'Y') THEN
84 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
85 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
86 l_api_name || '.others_exception', sqlcode);
87 END IF;
88 END IF;
89 END IF;
90 raise;
91
92 END Get_Po_Status_Code;
93
94
95
96 /**
97 * Public function: Get_Shipment_Ack_Change_Status
98 * Requires: PO_HEADER_ID,PO_RELEASE_ID
99 * Modifies:
100 * Effects: Return the acknowledgement status of individual shipment.
101 * Possible values are:
102 * 1. ACK_REQUIRED
103 * 2. PENDING_CHANGE
104 * 3. PENDING_CANCEL
105 * 4. ACCEPTED
106 * 5. REJECTED
107 * 6. ''
108 */
109
110 FUNCTION Get_Shipment_Ack_Change_Status (
111 p_api_version IN NUMBER,
112 p_Init_Msg_List IN VARCHAR2,
113 P_line_location_id IN NUMBER,
114 p_po_header_id IN NUMBER,
115 p_po_release_id IN NUMBER,
116 p_revision_num IN NUMBER )
117 RETURN VARCHAR2 IS
118
119 l_shipment_status VARCHAR2(30) := NULL;
120 l_api_name CONSTANT VARCHAR2(30) := 'GET_SHIPMENT_ACK_CHANGE_STATUS';
121 l_api_version CONSTANT NUMBER := 1.0;
122
123
124 BEGIN
125
126 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
127 FND_MSG_PUB.initialize;
128 END IF;
129
130 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
131 l_api_name, g_pkg_name) THEN
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 END IF;
134
135 IF (g_fnd_debug = 'Y') THEN
136 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
137 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
138 l_api_name || '.invoked', 'line_location_id: ' ||
139 NVL(TO_CHAR(P_line_location_id), ''));
140 END IF;
141 END IF;
142
143 l_shipment_status := PO_ACKNOWLEDGE_PO_PVT.Get_Shipment_Ack_Change_Status(
144 p_api_version => 1.0,
145 p_init_msg_list => FND_API.G_FALSE,
146 P_line_location_id => P_line_location_id,
147 p_po_header_id => P_po_header_id,
148 p_po_release_id => p_po_release_id,
149 p_revision_num => p_revision_num );
150
151
152 IF (g_fnd_debug = 'Y') THEN
153 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
154 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
155 l_api_name || '.before return', 'shipment_status: ' ||
156 NVL(l_shipment_status, ''));
157 END IF;
158 END IF;
159
160 return l_shipment_status;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
165 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
166 IF (g_fnd_debug = 'Y') THEN
167 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
168 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
169 l_api_name || '.others_exception', sqlcode);
170 END IF;
171 END IF;
172 END IF;
173 raise;
174
175 END Get_Shipment_Ack_Change_Status;
176
177
178
179 /**
180 * Public procedure: Acknowledge_Shipment
181 * Requires: LINE_LOCATION_ID, PO_HEADER_ID, PO_RELEASE_ID, REVISION_NUM,
182 * ACCEPTED_FLAG, COMMENT, BUYER_ID, USER_ID
183 * Modifies: PO_ACCEPTANCES
184 * Effects: Insert shipment level acknowledgement result into PO_ACCEPTANCES
185 * table. It also checks if all shipments are acknowledged after
186 * inserting the record, if yes then post the header level acknowledge
187 * result.
188 * Returns:
189 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
190 * FND_API.G_RET_STS_ERROR if an error occurs
191 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
192 */
193
194 PROCEDURE Acknowledge_Shipment (
195 p_api_version IN NUMBER,
196 p_Init_Msg_List IN VARCHAR2,
197 x_return_status OUT NOCOPY VARCHAR2,
198 p_line_location_id IN NUMBER,
199 p_po_header_id IN NUMBER,
200 p_po_release_id IN NUMBER,
201 p_revision_num IN NUMBER,
202 p_accepted_flag IN VARCHAR2,
203 p_comment IN VARCHAR2 default null,
204 p_buyer_id IN NUMBER,
205 p_user_id IN NUMBER )
206 IS
207
208 l_api_name CONSTANT VARCHAR2(30) := 'ACKNOWLEDGE_SHIPMENT';
209 l_api_version CONSTANT NUMBER := 1.0;
210
211 BEGIN
212
213 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
214 FND_MSG_PUB.initialize;
215 END IF;
216
217 IF NOT FND_API.Compatible_API_Call ( l_api_version,
218 p_api_version,
219 l_api_name,
220 g_pkg_name)
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END IF;
224
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226
227 IF (g_fnd_debug = 'Y') THEN
228 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
229 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
230 l_api_name || '.invoked', 'Line_location_id: ' ||
231 NVL(TO_CHAR(p_line_location_id),'null'));
232 END IF;
233 END IF;
234
235 PO_ACKNOWLEDGE_PO_PVT.Acknowledge_shipment(
236 p_api_version => 1.0,
237 p_Init_Msg_List => FND_API.G_FALSE,
238 x_return_status => x_return_status,
239 p_line_location_id => p_line_location_id,
240 p_po_header_id => p_po_header_id,
241 p_po_release_id => p_po_release_id,
242 p_revision_num => p_revision_num,
243 p_accepted_flag => p_accepted_flag,
244 p_comment => p_comment,
245 p_buyer_id => p_buyer_id,
246 p_user_id => p_user_id );
247
248
249
250 EXCEPTION
251 WHEN FND_API.g_exc_error THEN
252 x_return_status := FND_API.g_ret_sts_error;
253 WHEN FND_API.g_exc_unexpected_error THEN
254 x_return_status := FND_API.g_ret_sts_unexp_error;
255 WHEN OTHERS THEN
256 x_return_status := FND_API.g_ret_sts_unexp_error;
257 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
258 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
259 IF (g_fnd_debug = 'Y') THEN
260 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
261 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
262 l_api_name || '.others_exception', sqlcode);
263 END IF;
264 END IF;
265 END IF;
266 raise;
267 END Acknowledge_Shipment;
268
269
270
271 /**
272 * Public procedure: Carry_Over_Acknowledgement
273 * Requires: PO_HEADER_ID, PO_RELEASE_ID, REVISION_NUM,
274 * Modifies: PO_ACCEPTANCES
275 * Effects: Carry over the shipment_level acknowledgement results from the
276 * previous revision, it is called before launching PO approval
277 * workflow after supplier's change has been accepted by buyer.
278 * Returns:
279 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
280 * FND_API.G_RET_STS_ERROR if an error occurs
281 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
282 */
283 PROCEDURE Carry_Over_Acknowledgement (
284 p_api_version IN NUMBER,
285 p_Init_Msg_List IN VARCHAR2,
286 x_return_status OUT NOCOPY VARCHAR2,
287 p_po_header_id IN NUMBER,
288 p_po_release_id IN NUMBER,
289 p_revision_num IN NUMBER ) -- current revision_num
290 IS
291
292 l_api_name CONSTANT VARCHAR2(30) := 'CARRY_OVER_ACKNOWLEDGEMENT';
293 l_api_version CONSTANT NUMBER := 1.0;
294
295
296 BEGIN
297
298 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301
302 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
303 l_api_name, g_pkg_name)
304 THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 IF (g_fnd_debug = 'Y') THEN
311 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
312 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
313 l_api_name || '.invoked', 'po_header_id: ' ||
314 NVL(TO_CHAR(p_po_header_id),'null') || ' po_release_id: ' ||
315 NVL(TO_CHAR(p_po_release_id),'null'));
316 END IF;
317 END IF;
318
319 PO_ACKNOWLEDGE_PO_PVT.Carry_Over_Acknowledgement(
320 p_api_version => 1.0,
321 p_Init_Msg_List => FND_API.G_FALSE,
322 x_return_status => x_return_status,
323 p_po_header_id => p_po_header_id,
324 p_po_release_id => p_po_release_id,
325 p_revision_num => p_revision_num );
326
327
328 EXCEPTION
329 WHEN FND_API.g_exc_error THEN
330 x_return_status := FND_API.g_ret_sts_error;
331 WHEN FND_API.g_exc_unexpected_error THEN
332 x_return_status := FND_API.g_ret_sts_unexp_error;
333 WHEN OTHERS THEN
334 x_return_status := FND_API.g_ret_sts_unexp_error;
335 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
336 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
337 IF (g_fnd_debug = 'Y') THEN
338 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
339 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
340 l_api_name || '.others_exception', sqlcode);
341 END IF;
342 END IF;
343 END IF;
344 raise;
345
346 END Carry_Over_Acknowledgement;
347
348
349 /**
350 * Public function: All_Shipments_Responded
351 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM
352 * Modifies:
353 * Effects: Returns if all the shipments have been either changed
354 * or acknowledged.
355 * Returns: FND_API.G_FALSE or FND_API.G_TRUE
356 */
357
358 FUNCTION All_Shipments_Responded (
359 p_api_version IN NUMBER,
360 p_Init_Msg_List IN VARCHAR2,
361 p_po_header_id IN NUMBER,
362 p_po_release_id IN NUMBER,
363 p_revision_num IN NUMBER )
364 RETURN VARCHAR2 IS
365
366 l_api_name CONSTANT VARCHAR2(30) := 'ALL_SHIPMENTS_RESPONDED';
367 l_api_version CONSTANT NUMBER := 1.0;
368 l_result VARCHAR2(1) := NULL;
369
370 BEGIN
371
372 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
373 FND_MSG_PUB.initialize;
374 END IF;
375
376 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
377 l_api_name, g_pkg_name)
378 THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 IF (g_fnd_debug = 'Y') THEN
383 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
384 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
385 l_api_name || '.invoked', 'po_header_id: ' ||
386 NVL(TO_CHAR(p_po_header_id), ' ') || ' po_release_id: ' ||
387 NVL(TO_CHAR(p_po_release_id), ' ') || ' revision_num: ' ||
388 NVL(TO_CHAR(p_revision_num), ' '));
389 END IF;
390 END IF;
391
392 l_result := PO_ACKNOWLEDGE_PO_PVT.All_Shipments_Responded (
393 p_api_version => 1.0,
394 p_Init_Msg_List => FND_API.G_FALSE,
395 p_po_header_id => p_po_header_id,
396 p_po_release_id => p_po_release_id,
397 p_revision_num => p_revision_num );
398
399 IF (g_fnd_debug = 'Y') THEN
400 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
401 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
402 l_api_name || '.before return', 'All shipments acknowledged: ' ||
403 NVL(l_result, ''));
404 END IF;
405 END IF;
406
407 return l_result;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
412 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
413 IF (g_fnd_debug = 'Y') THEN
414 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
415 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
416 l_api_name || '.others_exception', sqlcode);
417 END IF;
418 END IF;
419 END IF;
420 raise;
421
422 END All_Shipments_Responded;
423
424
425
426 /**
427 * Public procedure: Set_Header_Acknowledgement
428 * Requires: PO_HEADER_ID, PO_RELEASE_ID
429 * Modifies: PO_ACCEPTANCES
430 * Effects: For ack required PO, check if all shipments has been acknowledged
431 * and if there is no supplier change pending, if both conditions
432 * satisfied, post the header level acknowledgement record.
433 * This API should be called after supplier submits the change requests and
434 * after buyer responds to all supplier changes without revision increase.
435 * Returns:
436 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
437 * FND_API.G_RET_STS_ERROR if an error occurs
438 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
439 */
440 PROCEDURE Set_Header_Acknowledgement (
441 p_api_version IN NUMBER,
442 p_Init_Msg_List IN VARCHAR2,
443 x_return_status OUT NOCOPY VARCHAR2,
444 p_po_header_id IN NUMBER,
445 p_po_release_id IN NUMBER )
446 IS
447
448 l_api_name CONSTANT VARCHAR2(30) := 'SET_HEADER_ACKNOWLEDGEMENT';
449 l_api_version CONSTANT NUMBER := 1.0;
450
451
452 BEGIN
453
454 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
455 FND_MSG_PUB.initialize;
456 END IF;
457
458 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
459 l_api_name, g_pkg_name)
460 THEN
461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
462 END IF;
463
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 IF (g_fnd_debug = 'Y') THEN
467 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
468 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
469 l_api_name || '.invoked', 'po_header_id: ' ||
470 NVL(TO_CHAR(p_po_header_id),'null') || ' po_release_id: ' ||
471 NVL(TO_CHAR(p_po_release_id),'null'));
472 END IF;
473 END IF;
474
475 PO_ACKNOWLEDGE_PO_PVT.Set_Header_Acknowledgement (
476 p_api_version => 1.0,
477 p_Init_Msg_List => FND_API.G_FALSE,
478 x_return_status => x_return_status,
479 p_po_header_id => p_po_header_id,
480 p_po_release_id => p_po_release_id );
481
482
483 EXCEPTION
484 WHEN FND_API.g_exc_error THEN
485 x_return_status := FND_API.g_ret_sts_error;
486 WHEN FND_API.g_exc_unexpected_error THEN
487 x_return_status := FND_API.g_ret_sts_unexp_error;
488 WHEN OTHERS THEN
489 x_return_status := FND_API.g_ret_sts_unexp_error;
490 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
491 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
492 IF (g_fnd_debug = 'Y') THEN
493 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
494 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
495 l_api_name || '.others_exception', sqlcode);
496 END IF;
497 END IF;
498 END IF;
499 raise;
500
501 END Set_Header_Acknowledgement;
502
503
504 END PO_ACKNOWLEDGE_PO_GRP;