1 PACKAGE BODY PO_ACKNOWLEDGE_PO_PVT AS
2 /* $Header: POXVACKB.pls 120.8 2006/09/12 12:25:55 jbalakri noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(50) := 'PO_ACKNOWLEDGE_PO_PVT';
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 -- Read the profile option that determines whether the promise date will be defaulted with need-by date or not
10 g_default_promise_date VARCHAR2(1) := NVL(FND_PROFILE.VALUE('POS_DEFAULT_PROMISE_DATE_ACK'),'N');
11
12
13 /**
14 * Private function: All_Shipments_Acknowledged
15 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM
16 * Modifies:
17 * Effects: Returns if all the shipments have been acknowledged.
18 */
19 FUNCTION All_Shipments_Acknowledged (
20 p_api_version IN NUMBER,
21 p_Init_Msg_List IN VARCHAR2,
22 p_po_header_id IN NUMBER,
23 p_po_release_id IN NUMBER,
24 p_revision_num IN NUMBER )
25 RETURN VARCHAR2 IS
26
27 l_ship_not_acked_flag VARCHAR2(1) := NULL;
28
29 CURSOR l_rel_ship_not_acked_csr IS
30 select 'Y'
31 From PO_LINE_LOCATIONS_ALL PLL
32 Where pll.po_release_id = p_po_release_id
33 And not exists (
34 select 1
35 From PO_ACCEPTANCES PA
36 Where PA.po_release_id = p_po_release_id
37 And pa.revision_num = p_revision_num
38 And pa.po_line_location_id = PLL.line_location_id )
39 And nvl(pll.cancel_flag, 'N') = 'N'
40 And nvl(pll.payment_type,'NULL') not in ('ADVANCE','DELIVERY')
41 And ((nvl(pll.closed_code, 'OPEN') = 'OPEN' and
42 nvl(pll.consigned_flag, 'N') = 'N') OR
43 (pll.closed_code = 'CLOSED FOR INVOICE' and
44 pll.consigned_flag = 'Y'));
45
46 CURSOR l_po_ship_not_acked_csr IS
47 select 'Y'
48 From PO_LINE_LOCATIONS_ALL PLL
49 Where pll.po_header_id = p_po_header_id
50 And pll.po_release_id is null
51 And not exists (
52 select 1
53 From PO_ACCEPTANCES PA
54 Where PA.po_header_id = p_po_header_id
55 And pa.revision_num = p_revision_num
56 And pa.po_line_location_id = PLL.line_location_id )
57 And nvl(pll.cancel_flag, 'N') = 'N'
58 And nvl(pll.payment_type,'NULL') not in ('ADVANCE','DELIVERY')
59 And ((nvl(pll.closed_code, 'OPEN') = 'OPEN' and
60 nvl(pll.consigned_flag, 'N') = 'N') OR
61 (pll.closed_code = 'CLOSED FOR INVOICE' and
62 pll.consigned_flag = 'Y'));
63
64 l_api_name CONSTANT VARCHAR2(30) := 'ALL_SHIPMENTS_ACKNOWLEDGED';
65 l_api_version CONSTANT NUMBER := 1.0;
66
67
68 BEGIN
69
70 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73
74 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
75 l_api_name, g_pkg_name)
76 THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79
80 If (p_po_release_id is not null) then
81 OPEN l_rel_ship_not_acked_csr;
82 LOOP
83 FETCH l_rel_ship_not_acked_csr INTO L_ship_not_acked_flag;
84 EXIT WHEN l_rel_ship_not_acked_csr%NOTFOUND;
85 IF (L_ship_not_acked_flag = 'Y') THEN
86 EXIT;
87 END IF;
88 END LOOP;
89 CLOSE l_rel_ship_not_acked_csr;
90
91 ELSIF (p_po_header_id is not null) then
92 OPEN l_po_ship_not_acked_csr;
93 LOOP
94 FETCH l_po_ship_not_acked_csr INTO L_ship_not_acked_flag;
95 EXIT WHEN l_po_ship_not_acked_csr%NOTFOUND;
96 IF (L_ship_not_acked_flag = 'Y') THEN
97 EXIT;
98 END IF;
99 END LOOP;
100 CLOSE l_po_ship_not_acked_csr;
101
102 END IF;
103
104 If (L_ship_not_acked_flag = 'Y') THEN
105 return FND_API.G_FALSE;
106 ELSE
107 return FND_API.G_TRUE;
108 END IF;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
113 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
114 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
115 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
116 l_api_name || '.others_exception', sqlcode);
117 END IF;
118 END IF;
119 raise;
120 END All_Shipments_Acknowledged;
121
122
123
124
125 /**
126 * Private function: Get_Header_Ack_Change_Status
127 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM
128 * Modifies:
129 * Effects: Return the acknowledgement status of the entire order,
130 * possible values are:
131 * 1. ACK_REQUIRED
132 * 2. SUPPLIER_CHANGE_PENDING
133 * 3. PARTIALLY_ACKNOWLEDGED
134 * 4. ACCEPTED
135 * 5. REJECTED
136 * 6. ACKNOWLEDGED
137 * 7. ''
138 * 8. SIG_REQUIRED
139 * 9. PENDING_BUYERS_SIGNATURE
140 */
141
142 FUNCTION Get_Header_Ack_Change_Status (
143 p_po_header_id IN NUMBER,
144 p_po_release_id IN NUMBER,
145 p_revision_num IN NUMBER )
146 RETURN VARCHAR2 IS
147
148 l_accepted_flag VARCHAR2(1) := null;
149 l_ship_ack_exist_flag VARCHAR2(1) := null;
150 l_acceptance_required_flag VARCHAR2(1) := null;
151 l_shipment_exist_flag VARCHAR2(1) := null;
152 l_change_requested_by PO_HEADERS_ALL.change_requested_by%TYPE := null;
153 l_sign_flag PO_HEADERS_ALL.pending_signature_flag%TYPE := null;
154 l_sup_sign_exist_flag VARCHAR2(1) := null;
155 l_reject_sign_exist_flag VARCHAR2(1) := null;
156 l_arch_revision_num NUMBER := p_revision_num;
157
158 CURSOR l_po_ship_ack_exists_csr IS
159 select 'Y'
160 from PO_ACCEPTANCES
161 where po_header_id = p_po_header_id
162 and po_release_id is null
163 and revision_num = p_revision_num
164 and po_line_location_id is not null;
165
166 CURSOR l_rel_ship_ack_exists_csr IS
167 select 'Y'
168 from PO_ACCEPTANCES
169 where po_header_id is null
170 and po_release_id = p_po_release_id
171 and revision_num = p_revision_num
172 and po_line_location_id is not null;
173
174 CURSOR l_sup_sign_exists_csr IS
175 select 'Y'
176 from PO_ACCEPTANCES
177 where po_header_id = p_po_header_id
178 and revision_num = p_revision_num
179 and po_release_id is null
180 and po_line_location_id is null
181 and accepting_party = 'S'
182 and accepted_flag = 'Y'
183 and signature_flag = 'Y';
184
185 CURSOR l_reject_sign_exists_csr IS
186 select 'Y'
187 from PO_ACCEPTANCES
188 where po_header_id = p_po_header_id
189 and revision_num = p_revision_num
190 and po_release_id is null
191 and po_line_location_id is null
192 and accepted_flag = 'N'
193 and signature_flag = 'Y';
194
195
196 BEGIN
197 /* Release */
198 IF (p_po_release_id is not null) THEN
199 select nvl(acceptance_required_flag, 'N'),
200 nvl(change_requested_by, ' ')
201 into l_acceptance_required_flag,
202 l_change_requested_by
203 from PO_RELEASES_ALL
204 where po_release_id = p_po_release_id;
205
206 /* Get if there is any shipment acknowledged. */
207 OPEN l_rel_ship_ack_exists_csr;
208 LOOP
209 FETCH l_rel_ship_ack_exists_csr INTO l_ship_ack_exist_flag;
210 EXIT WHEN l_rel_ship_ack_exists_csr%NOTFOUND;
211 IF (l_ship_ack_exist_flag = 'Y') THEN
212 EXIT;
213 END IF;
214 END LOOP;
215 CLOSE l_rel_ship_ack_exists_csr;
216
217 /* If PO is acceptance required. */
218 IF (l_acceptance_required_flag = 'Y') THEN
219
220 /* Bug 2731191, if all shipments have been changed or acked and the
221 header acceptance_required_flag is 'Y', the status of PO should be
222 SUPPLIER_CHANGE_PENDING, before we check ship_ack_exist_flag first
223 so the status was mistakenly calculated as 'ACK_REQUIRED'. */
224
225 BEGIN
226 select revision_num
227 into l_arch_revision_num
228 from po_releases_archive_all
229 where po_release_id = p_po_release_id
230 and latest_external_flag = 'Y';
231
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 l_arch_revision_num := p_revision_num;
235 END;
236
237
238 /* If all shipments were either changed or acknowledged. */
239 IF (PO_ACKNOWLEDGE_PO_PVT.All_Shipments_Responded (
240 1.0,
241 FND_API.G_FALSE,
242 null,
243 p_po_release_id,
244 l_arch_revision_num ) = FND_API.G_TRUE AND
245 l_change_requested_by = 'SUPPLIER' ) THEN
246 return 'SUPPLIER_CHANGE_PENDING';
247
248 /* return partially acknowledged when changes are made to shipments */
249 ELSIF (l_change_requested_by = 'SUPPLIER' or l_ship_ack_exist_flag is not null) THEN
250 return 'PARTIALLY_ACKNOWLEDGED';
251
252 /* If none of the shipments was acknowledged, return ACK_REQUIRED. */
253 ELSIF (l_ship_ack_exist_flag is NULL) THEN
254 return 'ACK_REQUIRED';
255 END IF;
256
257 END IF;
258
259 /* PO */
260 ELSIF (p_po_header_id is not null) THEN
261 select nvl(acceptance_required_flag, 'N'),
262 nvl(change_requested_by, ' '),
263 nvl(pending_signature_flag,'N')
264 into l_acceptance_required_flag,
265 l_change_requested_by,
266 l_sign_flag
267 from PO_HEADERS_ALL
268 where po_header_id = p_po_header_id;
269
270 /* Check for Signatures */
271
272 IF (l_sign_flag = 'Y') THEN
273
274 OPEN l_reject_sign_exists_csr;
275 LOOP
276 FETCH l_reject_sign_exists_csr INTO l_reject_sign_exist_flag;
277 EXIT WHEN l_reject_sign_exists_csr%NOTFOUND;
278 IF (l_sup_sign_exist_flag = 'Y') THEN
279 EXIT;
280 END IF;
281 END LOOP;
282 CLOSE l_reject_sign_exists_csr;
283
284 IF (l_reject_sign_exist_flag = 'Y') THEN
285 /* Now Check if the Signature was a reject by any party*/
286 return 'REJECTED';
287 ELSE
288
289 OPEN l_sup_sign_exists_csr;
290 LOOP
291 FETCH l_sup_sign_exists_csr INTO l_sup_sign_exist_flag;
292 EXIT WHEN l_sup_sign_exists_csr%NOTFOUND;
293 IF (l_sup_sign_exist_flag = 'Y') THEN
294 EXIT;
295 END IF;
296 END LOOP;
297 CLOSE l_sup_sign_exists_csr;
298
299 IF (l_sup_sign_exist_flag is NULL) THEN
300 return 'SIG_REQUIRED';
301 ELSIF (l_sup_sign_exist_flag = 'Y') then
302 return 'PENDING_BUYER_SIGNATURE';
303 END IF;
304 END IF;
305 END IF;
306
307 IF (l_acceptance_required_flag = 'Y') OR
308 (l_acceptance_required_flag = 'D') THEN
309 OPEN l_po_ship_ack_exists_csr;
310 LOOP
311 FETCH l_po_ship_ack_exists_csr INTO l_ship_ack_exist_flag;
312 EXIT WHEN l_po_ship_ack_exists_csr%NOTFOUND;
313 IF (l_ship_ack_exist_flag = 'Y') THEN
314 EXIT;
315 END IF;
316 END LOOP;
317 CLOSE l_po_ship_ack_exists_csr;
318
319
320 BEGIN
321 select 'Y'
322 into l_shipment_exist_flag
323 from sys.dual
324 where exists (
325 select 1
326 from PO_LINE_LOCATIONS_ALL
327 where po_header_id = p_po_header_id
328 and po_release_id is NULL );
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 l_shipment_exist_flag := 'N';
333 END;
334
335
336 BEGIN
337 select revision_num
338 into l_arch_revision_num
339 from po_headers_archive_all
340 where po_header_id = p_po_header_id
341 and latest_external_flag = 'Y';
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 l_arch_revision_num := p_revision_num;
346 END;
347
348
349 /* Bug 2816175, if there is no shipment existing and PO was
350 changed, we should show 'SUPPLIER_CHANGE_PENDING'. */
351 IF (NVL(l_shipment_exist_flag, 'N') = 'N' AND
352 l_change_requested_by = 'SUPPLIER' ) THEN
353 return 'SUPPLIER_CHANGE_PENDING';
354
355 ELSIF (NVL(l_shipment_exist_flag, 'N') = 'N') THEN
356 return 'ACK_REQUIRED';
357
358 /* If all shipments were either changed or acknowledged. */
359 ELSIF (PO_ACKNOWLEDGE_PO_PVT.All_Shipments_Responded (
360 1.0,
361 FND_API.G_FALSE,
362 p_po_header_id,
363 null,
364 l_arch_revision_num) = FND_API.G_TRUE AND
365 l_change_requested_by = 'SUPPLIER' ) THEN
366 return 'SUPPLIER_CHANGE_PENDING';
367
368 /* return partially acknowledged when changes are made to shipments */
369 ELSIF (l_change_requested_by = 'SUPPLIER' or
370 l_ship_ack_exist_flag is not null) THEN
371 return 'PARTIALLY_ACKNOWLEDGED';
372
373 /* If none of the shipments was acknowledged, return ACK_REQUIRED. */
374 ELSIF (l_ship_ack_exist_flag is NULL) THEN
375 return 'ACK_REQUIRED';
376 END IF;
377
378 END IF;
379
380 END IF;
381
382
383 /*
384 * If PO does not require acknowledgement, then PO is either
385 * 1. Accepted; 2. Rejected; 3. Acknowledged; 4) Do not require ACK
386 */
387 IF (p_po_release_id is not null) THEN
388 BEGIN
389 /* Get header level accepted_flag. */
390 select accepted_flag
391 into l_accepted_flag
392 from PO_ACCEPTANCES
393 where acceptance_id = (
394 select max(acceptance_id)
395 from PO_ACCEPTANCES
396 where po_release_id = p_po_release_id
397 and revision_num = p_revision_num
398 and po_line_location_id is null );
399
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 l_accepted_flag := null;
403 END;
404
405 ELSIF (p_po_header_id is not null) THEN
406 BEGIN
407 select accepted_flag
408 into l_accepted_flag
409 from PO_ACCEPTANCES
410 where acceptance_id = (
411 select max(acceptance_id)
412 from PO_ACCEPTANCES
413 where po_header_id = p_po_header_id
414 and po_release_id is null
415 and revision_num = p_revision_num
416 and po_line_location_id is null );
417
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 l_accepted_flag := null;
421 END;
422
423 END IF;
424
425 IF (l_accepted_flag = 'Y') THEN
426 return 'ACCEPTED';
430 return 'ACKNOWLEDGED';
427 ELSIF (l_accepted_flag = 'N') THEN
428 return 'REJECTED';
429 ELSIF (l_accepted_flag = 'A') THEN
431 ELSE
432 return '';
433 END IF;
434
435 EXCEPTION
436 WHEN OTHERS THEN
437 raise;
438 END Get_Header_Ack_Change_Status;
439
440
441 /**
442 * Private procedure: Acknowledge_Po
443 * Requires: PO_HEADER_ID, PO_RELEASE_ID, REVISION_NUM, ACCEPTED_FLAG,
444 * COMMENT, BUYER_ID, USER_ID
445 * Modifies: PO_ACCEPTANCES
446 * Effects: Insert header level acknowledgement result into PO_ACCEPTANCES
447 * table, also update ACCEPTANCE_REQUIRED_FLAG at PO header level.
448 */
449
450 PROCEDURE Acknowledge_Po (
451 p_po_header_id IN NUMBER,
452 p_po_release_id IN NUMBER,
453 p_revision_num IN NUMBER,
454 p_accepted_flag IN VARCHAR2,
455 p_comment IN VARCHAR2 default null,
456 p_buyer_id IN NUMBER,
457 p_user_id IN NUMBER )
458
459 IS
460
461 -- Bug 2850566
462 l_rowid ROWID;
463 l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
464 l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
465 l_acc_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
466 l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
467 l_user_id PO_ACCEPTANCES.last_updated_by%TYPE;
468 -- End of Bug 2850566
469
470 BEGIN
471
472 -- Bug 2850566 RBAIRRAJ
473 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
474 -- instead of writing an Insert statement.
475
476 IF p_po_release_id IS NULL THEN
477 l_acc_po_header_id := p_po_header_id;
478 ELSE
479 l_acc_po_header_id := NULL;
480 END IF;
481
482 l_user_id := p_user_id;
483
484 PO_ACCEPTANCES_INS_PVT.insert_row(
485 x_rowid => l_rowid,
486 x_acceptance_id => l_acceptance_id,
487 x_Last_Update_Date => l_Last_Update_Date,
488 x_Last_Updated_By => l_user_id,
489 x_Last_Update_Login => l_Last_Update_Login,
490 p_creation_date => sysdate,
491 p_created_by => p_user_id,
492 p_po_header_id => l_acc_po_header_id,
493 p_po_release_id => p_po_release_id,
494 p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
495 p_action_date => sysdate,
496 p_revision_num => p_revision_num,
497 p_accepted_flag => p_accepted_flag,
498 p_note => p_comment,
499 p_accepting_party => 'S');
500
501 -- End of Bug 2850566
502
503 /* reset the header-level acceptance_required_flag. */
504 IF (p_po_release_id is not null) THEN
505 update PO_RELEASES_ALL
506 set acceptance_required_flag = 'N',
507 acceptance_due_date = ''
508 where po_release_id = p_po_release_id;
509
510 ELSIF (p_po_header_id is not null) THEN
511 update PO_HEADERS_ALL
512 set acceptance_required_flag = 'N',
513 acceptance_due_date = ''
514 where po_header_id = p_po_header_id;
515 END IF;
516
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 raise;
521 END Acknowledge_Po;
522
523
524 /**
525 * Public function: Get_Po_Status_Code
526 * Requires: PO_HEADER_ID,PO_RELEASE_ID
527 * Modifies:
528 * Effects: Return the overall status of the entire order.
529 * Possible values are:
530 * 1. CANCELLED
531 * 2. FROZEN
532 * 3. ON HOLD
533 * 4. INTERNAL CHANGE
534 * 5. SUPPLIER_CHANGE_PENDING
535 * 6. ACCEPTED
536 * 7. REJECTED
537 * 8. ACKNOWLEDGED
538 * 9. PARTIALLY_ACKNOWLEDGED
539 * 10. ACK_REQUIRED
540 * 11. DRAFT
541 * 12. PENDING_SUBMIT
542 * 13. PENDING_SUPP_EDIT
543 * 14. CAT_ADMIN_LOCK
544 * 15. ''
545 */
546
547 FUNCTION Get_Po_Status_Code (
548 p_api_version IN NUMBER,
549 p_Init_Msg_List IN VARCHAR2,
550 p_po_header_id IN NUMBER,
551 p_po_release_id IN NUMBER )
552 RETURN VARCHAR2 IS
553
554 l_api_name CONSTANT VARCHAR2(30) := 'GET_PO_STATUS_CODE';
555 l_api_version CONSTANT NUMBER := 1.0;
556
557 l_cancel_flag PO_HEADERS_ALL.cancel_flag%TYPE := null;
558 l_frozen_flag PO_HEADERS_ALL.frozen_flag%TYPE := null;
559 l_on_hold_flag PO_HEADERS_ALL.user_hold_flag%TYPE := null;
560 l_accp_reqd_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE := null;
561 l_closed_code PO_HEADERS_ALL.closed_code%TYPE := null;
562 l_auth_status PO_HEADERS_ALL.authorization_status%TYPE := null;
563 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
564 l_changed_by PO_HEADERS_ALL.change_requested_by%TYPE := null;
565 l_cancel_pending_flag VARCHAR2(1) := 'N';
566
567 l_ga_flag PO_HEADERS_ALL.GLOBAL_AGREEMENT_flag%TYPE := null;
568 l_authoring_flag PO_HEADERS_ALL.SUPPLIER_AUTH_ENABLED_flag%TYPE := null;
569 l_lock_owner_role PO_HEADERS_ALL.LOCK_OWNER_ROLE%TYPE := null;
570 l_catalog_status VARCHAR2(100) := null;
574 select 'Y'
571 l_return_status VARCHAR2(1) := null;
572
573 CURSOR l_rel_supplier_cancel_csr IS
575 from PO_CHANGE_REQUESTS
576 where po_release_id = p_po_release_id
577 and request_status = 'PENDING'
578 and request_level = 'HEADER'
579 and action_type = 'CANCELLATION'
580 and initiator = 'SUPPLIER';
581
582
583 CURSOR l_po_supplier_cancel_csr IS
584 select 'Y'
585 from PO_CHANGE_REQUESTS
586 where document_header_id = p_po_header_id
587 and request_status = 'PENDING'
588 and request_level = 'HEADER'
589 and action_type = 'CANCELLATION'
590 and initiator = 'SUPPLIER';
591
592 BEGIN
593
594 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
595 FND_MSG_PUB.initialize;
596 END IF;
597
598 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
599 l_api_name, g_pkg_name)
600 THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603
604 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
605 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
606 l_api_name || '.invoked', 'po_header_id: ' ||
607 NVL(TO_CHAR(p_po_header_id), ' ') || ' po_release_id: ' ||
608 NVL(TO_CHAR(p_po_release_id), ' ') );
609 END IF;
610
611 IF (p_po_release_id is not NULL) THEN
612 select nvl(cancel_flag, 'N'),
613 nvl(frozen_flag, 'N'),
614 nvl(hold_flag, 'N'),
615 nvl(closed_code, 'OPEN'),
616 nvl(acceptance_required_flag, 'N'),
617 nvl(authorization_status, 'INCOMPLETE'),
618 revision_num,
619 nvl(change_requested_by, ' ')
620 into l_cancel_flag,
621 l_frozen_flag,
622 l_on_hold_flag,
623 l_closed_code,
624 l_accp_reqd_flag,
625 l_auth_status,
626 l_revision_num,
627 l_changed_by
628 from PO_RELEASES_all
629 where po_release_id = p_po_release_id;
630
631 ELSE
632 select nvl(cancel_flag, 'N'),
633 nvl(frozen_flag, 'N'),
634 nvl(user_hold_flag, 'N'),
635 nvl(closed_code, 'OPEN'),
636 nvl(acceptance_required_flag, 'N'),
637 nvl(authorization_status, 'INCOMPLETE'),
638 revision_num,
639 nvl(change_requested_by, ' '),
640 nvl(global_agreement_flag, 'N'),
641 nvl(supplier_auth_enabled_flag, 'N'),
642 nvl(lock_owner_role, ' ')
643 into l_cancel_flag,
644 l_frozen_flag,
645 l_on_hold_flag,
646 l_closed_code,
647 l_accp_reqd_flag,
648 l_auth_status,
649 l_revision_num,
650 l_changed_by,
651 l_ga_flag,
652 l_authoring_flag,
653 l_lock_owner_role
654 from PO_HEADERS_all
655 where po_header_id = p_po_header_id;
656
657 END IF;
658
659 IF (l_closed_code in ('CLOSED','FINALLY CLOSED') ) THEN
660 return l_closed_code;
661 ELSIF (l_frozen_flag = 'Y') THEN
662 return 'FROZEN';
663 ELSIF (l_on_hold_flag = 'Y') THEN
664 return 'ON HOLD';
665 ELSIF (l_cancel_flag = 'Y') THEN
666 return 'CANCELLED';
667 ELSIF (l_auth_status = 'REJECTED') THEN
668 return 'REJECTED';
669 ELSIF (l_closed_code <> 'OPEN' ) THEN
670 return l_closed_code;
671 ELSIF (l_ga_flag = 'Y' and l_lock_owner_role = 'CAT_ADMIN') THEN
672 return 'CAT_ADMIN_LOCK';
673 ELSIF (l_auth_status in ('IN PROCESS', 'REQUIRES REAPPROVAL')
674 AND l_changed_by <> 'SUPPLIER') THEN
675 return 'INTERNAL CHANGE';
676 END IF;
677
678 IF (l_changed_by <> 'SUPPLIER' AND
679 l_ga_flag = 'Y' and l_authoring_flag = 'Y') THEN
680
681 PO_DRAFTS_GRP.GET_ONLINE_AUTH_STATUS_CODE (
682 P_API_VERSION => 1.0,
683 X_RETURN_STATUS => l_return_status,
684 P_PO_HEADER_ID => p_po_header_id,
685 X_ONLINE_AUTH_STATUS_CODE => l_catalog_status );
686
687 IF (l_catalog_status is not null AND l_catalog_status <> 'NO_DRAFT') THEN
688 return l_catalog_status;
689 END IF;
690
691 END IF;
692
693
697 begin
694 IF (l_accp_reqd_flag='N' and l_changed_by='SUPPLIER') THEN
695
696 If (p_po_release_id is not null) THEN
698 OPEN l_rel_supplier_cancel_csr;
699 FETCH l_rel_supplier_cancel_csr INTO l_cancel_pending_flag;
700 CLOSE l_rel_supplier_cancel_csr;
701
702 exception
703 WHEN OTHERS then
704 l_cancel_pending_flag := null;
705 end;
706
707 IF (l_cancel_pending_flag = 'Y') THEN
708 return 'SUPPLIER_CANCEL_PENDING';
709 ELSE
710 return 'SUPPLIER_CHANGE_PENDING';
711 END IF;
712
713 ELSIF (p_po_header_id is not null) THEN
714 begin
715 OPEN l_po_supplier_cancel_csr;
716 FETCH l_po_supplier_cancel_csr INTO l_cancel_pending_flag;
717 CLOSE l_po_supplier_cancel_csr;
718
719 exception
720 WHEN OTHERS then
721 l_cancel_pending_flag := null;
722 end;
723
724 IF (l_cancel_pending_flag = 'Y') THEN
725 return 'SUPPLIER_CANCEL_PENDING';
726 ELSE
727 return 'SUPPLIER_CHANGE_PENDING';
728 END IF;
729
730 END IF;
731
732 ELSE
733 return PO_ACKNOWLEDGE_PO_PVT.Get_Header_Ack_Change_Status (
734 p_po_header_id,
735 p_po_release_id,
736 l_revision_num );
737 END IF;
738
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
742 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
743 l_api_name || '.NO_DATA_FOUND Exception', sqlcode);
744 END IF;
745 WHEN OTHERS THEN
746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
747 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
748 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
749 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
750 l_api_name || '.others_exception', sqlcode);
751 END IF;
752 END IF;
753 raise;
754
755 END Get_Po_Status_Code;
756
757
758
759 /**
760 * Public function: Get_Shipment_Ack_Change_Status
761 * Requires: PO_HEADER_ID,PO_RELEASE_ID
762 * Modifies:
763 * Effects: Return the acknowledgement status of individual shipment.
764 * Possible values are:
765 * 1. ACK_REQUIRED
766 * 2. PENDING_CHANGE
767 * 3. PENDING_CANCEL
768 * 4. ACCEPTED
769 * 5. REJECTED
770 * 6. ''
771 */
772
773 FUNCTION Get_Shipment_Ack_Change_Status (
774 p_api_version IN NUMBER,
775 p_Init_Msg_List IN VARCHAR2,
776 P_line_location_id IN NUMBER,
777 p_po_header_id IN NUMBER,
778 p_po_release_id IN NUMBER,
779 p_revision_num IN NUMBER )
780 RETURN VARCHAR2 IS
781
782 l_ship_accepted_flag VARCHAR2(1) := null;
783 l_header_accepted_flag VARCHAR2(1) := null;
784 l_acceptance_required_flag VARCHAR2(1) := null;
785 l_action_type PO_CHANGE_REQUESTS.action_type%TYPE := null;
786
787 l_revision_num NUMBER := p_revision_num;
788 l_authorization_status PO_HEADERS_ALL.authorization_status%TYPE;
789
790 l_api_name CONSTANT VARCHAR2(30) := 'GET_PO_STATUS_CODE';
791 l_api_version CONSTANT NUMBER := 1.0;
792
793
794 BEGIN
795
796 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
797 FND_MSG_PUB.initialize;
798 END IF;
799
800 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
801 l_api_name, g_pkg_name) THEN
802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
803 END IF;
804
805 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
806 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
807 l_api_name || '.invoked', 'line_location_id: ' ||
808 NVL(TO_CHAR(P_line_location_id), ''));
809 END IF;
810
811
812 /* Bug 3715595 Use the latest revision_num to calculate the status
813 if PO is rejected. */
814 BEGIN
815
816 IF (p_po_release_id is not null) THEN
817 SELECT POR.revision_num, authorization_status
818 INTO l_revision_num, l_authorization_status
819 FROM PO_RELEASES_ALL POR
820 WHERE POR.po_release_id = p_po_release_id;
821
822 ELSIF (p_po_header_id is not null) THEN
823 SELECT POH.revision_num, authorization_status
824 INTO l_revision_num, l_authorization_status
825 FROM PO_HEADERS_ALL POH
826 WHERE POH.po_header_id = p_po_header_id;
827
828 END IF;
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 l_revision_num := p_revision_num;
833 l_authorization_status := '';
834 END;
835
836 IF (not l_authorization_status = 'REJECTED') THEN
837 l_revision_num := p_revision_num;
838 END IF;
839
840
841 /* Shipment is pending change or cancel. */
845
842 BEGIN
843
844 IF (p_po_release_id is not null) THEN
846 select PCR.action_type
847 into l_action_type
848 from PO_CHANGE_REQUESTS PCR,
849 PO_RELEASES_ALL POR
850 where pcr.document_line_location_id = P_line_location_id
851 and pcr.po_release_id = p_po_release_id
852 and por.po_release_id = p_po_release_id
853 and por.change_requested_by = 'SUPPLIER'
854 and request_level = 'SHIPMENT'
855 and ((request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
856 (request_status = 'REJECTED' and change_request_group_id = (
857 select MAX(change_request_group_id)
858 from po_change_requests pcr2
859 where pcr2.po_release_id = p_po_release_id
860 and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP'))) )
861 and rownum = 1;
862
863 ELSIF (p_po_header_id is not null) THEN
864
865 select PCR.action_type
866 into l_action_type
867 from PO_CHANGE_REQUESTS PCR,
868 PO_HEADERS_ALL POH
869 where pcr.document_line_location_id = P_line_location_id
870 and pcr.document_header_id = p_po_header_id
871 and poh.po_header_id = p_po_header_id
872 and poh.change_requested_by = 'SUPPLIER'
873 and request_level = 'SHIPMENT'
874 and ((request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
875 (request_status = 'REJECTED' and change_request_group_id = (
876 select MAX(change_request_group_id)
877 from po_change_requests pcr2
878 where pcr2.document_header_id = p_po_header_id
879 and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP'))) )
880 and rownum = 1;
881
882 END IF;
883
884 EXCEPTION
885 WHEN NO_DATA_FOUND THEN
886 l_action_type := null;
887 END;
888
889 if (l_action_type = 'CANCELLATION') then
890 return 'PENDING_CANCEL';
891 elsif (l_action_type = 'MODIFICATION') then
892 return 'PENDING_CHANGE';
893 end if;
894
895
896 /* Check if shipment has been acknowledged. */
897 BEGIN
898 select accepted_flag
899 into l_ship_accepted_flag
900 from PO_ACCEPTANCES
901 where po_line_location_id = P_line_location_id
902 and revision_num = l_revision_num
903 and acceptance_id = (select MAX(acceptance_id)
904 from PO_ACCEPTANCES PA2
905 where PA2.po_line_location_id = P_line_location_id
906 and PA2.revision_num = l_revision_num );
907
908 EXCEPTION
909 WHEN NO_DATA_FOUND THEN
910 l_ship_accepted_flag := null;
911 END;
912
913 IF (l_ship_accepted_flag = 'Y') THEN
914 return 'ACCEPTED';
915 ELSIF (l_ship_accepted_flag = 'N') THEN
916 return 'REJECTED';
917 END IF;
918
919
920 IF (p_po_release_id is not null) THEN
921 select nvl(acceptance_required_flag, 'N')
922 into l_acceptance_required_flag
923 from PO_RELEASES_ALL
924 where po_release_id = p_po_release_id
925 and revision_num = l_revision_num;
926
927 IF (l_acceptance_required_flag in ('D','Y')) THEN
928 return 'ACK_REQUIRED';
929
930 ELSE
931 BEGIN
932 select accepted_flag
933 into l_header_accepted_flag
934 from PO_ACCEPTANCES
935 where acceptance_id = (
936 select max(acceptance_id)
937 from PO_ACCEPTANCES
938 where po_release_id = p_po_release_id
939 and revision_num = l_revision_num
940 and po_line_location_id is null );
941
942 EXCEPTION
943 WHEN NO_DATA_FOUND THEN
944 l_header_accepted_flag := null;
945 END;
946 END IF;
947
948 ELSIF (p_po_header_id is not null) THEN
949 select nvl(acceptance_required_flag, 'N')
950 into l_acceptance_required_flag
951 from PO_HEADERS_ALL
952 where po_header_id = p_po_header_id;
953
954 IF (l_acceptance_required_flag in('D', 'Y')) THEN
955 return 'ACK_REQUIRED';
956
957 ELSE
958 BEGIN
959 select accepted_flag
960 into l_header_accepted_flag
961 from PO_ACCEPTANCES
962 where acceptance_id = (
963 select max(acceptance_id)
964 from PO_ACCEPTANCES
965 where po_header_id = p_po_header_id
966 and po_release_id is null
967 and revision_num = l_revision_num
968 and po_line_location_id is null );
969
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972 l_header_accepted_flag := null;
973 END;
974 END IF;
975
976 END IF;
977
978 IF (l_header_accepted_flag = 'Y') THEN
979 return 'ACCEPTED';
980 ELSIF (l_header_accepted_flag = 'N') THEN
981 return 'REJECTED';
982 ELSE
983 return '';
984 END IF;
985
986 EXCEPTION
987 WHEN OTHERS THEN
991 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
988 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
989 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
990 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
992 l_api_name || '.others_exception', sqlcode);
993 END IF;
994 END IF;
995 raise;
996 END Get_Shipment_Ack_Change_Status;
997
998
999
1000 /**
1001 * Public procedure: Acknowledge_Shipment
1002 * Requires: LINE_LOCATION_ID, PO_HEADER_ID, PO_RELEASE_ID, REVISION_NUM,
1003 * ACCEPTED_FLAG, COMMENT, BUYER_ID, USER_ID
1004 * Modifies: PO_ACCEPTANCES
1005 * Effects: Insert shipment level acknowledgement result into PO_ACCEPTANCES
1006 * table. Also checks if all shipments have been acknowledged after
1007 * insertion, if yes then post the header level acknowledge result.
1008 */
1009
1010 PROCEDURE Acknowledge_Shipment (
1011 p_api_version IN NUMBER,
1012 p_Init_Msg_List IN VARCHAR2,
1013 x_return_status OUT NOCOPY VARCHAR2,
1014 p_line_location_id IN NUMBER,
1015 p_po_header_id IN NUMBER,
1016 p_po_release_id IN NUMBER,
1017 p_revision_num IN NUMBER,
1018 p_accepted_flag IN VARCHAR2,
1019 p_comment IN VARCHAR2 default null,
1020 p_buyer_id IN NUMBER,
1021 p_user_id IN NUMBER )
1022 IS
1023
1024 -- Bug 2850566
1025 l_rowid ROWID;
1026 l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
1027 l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
1028 l_acc_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
1029 l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
1030 l_user_id PO_ACCEPTANCES.last_updated_by%TYPE;
1031 -- End of Bug 2850566
1032
1033
1034 l_api_name CONSTANT VARCHAR2(30) := 'ACKNOWLEDGE_SHIPMENT';
1035 l_api_version CONSTANT NUMBER := 1.0;
1036
1037
1038 BEGIN
1039
1040 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1041 FND_MSG_PUB.initialize;
1042 END IF;
1043
1044 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1045 l_api_name, g_pkg_name)
1046 THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1053 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1054 l_api_name || '.invoked', 'Line_location_id: ' ||
1055 NVL(TO_CHAR(p_line_location_id),'null'));
1056 END IF;
1057
1058 -- Bug 2850566 RBAIRRAJ
1059 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
1060 -- instead of writing an Insert statement.
1061
1062 IF(p_accepted_flag = 'Y' AND g_default_promise_date = 'Y') THEN
1063 -- RDP ( defaults the promise date with need by date)
1064 POS_ACK_PO.Acknowledge_promise_date (p_line_location_id,p_po_header_id,p_po_release_id,p_revision_num,p_user_id);
1065 END IF;
1066
1067
1068 IF p_po_release_id IS NULL THEN
1069 l_acc_po_header_id := p_po_header_id;
1070 ELSE
1071 l_acc_po_header_id := NULL;
1072 END IF;
1073
1074 l_user_id := p_user_id;
1075
1076 PO_ACCEPTANCES_INS_PVT.insert_row(
1077 x_rowid => l_rowid,
1078 x_acceptance_id => l_acceptance_id,
1079 x_Last_Update_Date => l_Last_Update_Date,
1080 x_Last_Updated_By => l_user_id,
1081 x_Last_Update_Login => l_Last_Update_Login,
1082 p_creation_date => sysdate,
1083 p_created_by => p_user_id,
1084 p_po_header_id => l_acc_po_header_id,
1085 p_po_release_id => p_po_release_id,
1086 p_po_line_location_id => p_line_location_id,
1087 p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
1088 p_action_date => sysdate,
1089 p_employee_id => to_number(null),
1090 p_revision_num => p_revision_num,
1091 p_accepted_flag => p_accepted_flag,
1092 p_note => p_comment);
1093
1094 -- End of Bug 2850566
1095
1096 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1097 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1098 l_api_name || '.after inserting shipment acknowledgement',
1099 'Line_location_id: '|| NVL(TO_CHAR(p_line_location_id),'null'));
1100 END IF;
1101
1102
1103 EXCEPTION
1104 WHEN FND_API.g_exc_error THEN
1105 x_return_status := FND_API.g_ret_sts_error;
1106 WHEN FND_API.g_exc_unexpected_error THEN
1107 x_return_status := FND_API.g_ret_sts_unexp_error;
1108 WHEN OTHERS THEN
1109 x_return_status := FND_API.g_ret_sts_unexp_error;
1110 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1111 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1112 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1113 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1114 l_api_name || '.others_exception', sqlcode);
1115 END IF;
1116 END IF;
1117 raise;
1118 END acknowledge_shipment;
1119
1120
1121
1122 /**
1126 * Effects: Carry over the shipment_level acknowledgement results from the
1123 * Public procedure: Carry_Over_Acknowledgement
1124 * Requires: PO_HEADER_ID, PO_RELEASE_ID, REVISION_NUM,
1125 * Modifies: PO_ACCEPTANCES
1127 * previous revision, it is called before launching PO approval
1128 * workflow after supplier's change has been accepted by buyer.
1129 * Returns:
1130 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1131 * FND_API.G_RET_STS_ERROR if an error occurs
1132 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1133 */
1134 PROCEDURE Carry_Over_Acknowledgement (
1135 p_api_version IN NUMBER,
1136 p_Init_Msg_List IN VARCHAR2,
1137 x_return_status OUT NOCOPY VARCHAR2,
1138 p_po_header_id IN NUMBER,
1139 p_po_release_id IN NUMBER,
1140 p_revision_num IN NUMBER ) -- current revision_num
1141 IS
1142
1143 l_header_accepted_flag VARCHAR2(1) := 'Y';
1144 l_buyer_id NUMBER;
1145
1146 l_api_name CONSTANT VARCHAR2(30) := 'CARRY_OVER_ACKNOWLEDGEMENT';
1147 l_api_version CONSTANT NUMBER := 1.0;
1148
1149 -- Bug 2850566 RBAIRRAJ
1150 l_rowid ROWID;
1151 l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
1152 l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
1153 l_Last_Updated_By PO_ACCEPTANCES.last_updated_by%TYPE;
1154 l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
1155 l_user_id PO_ACCEPTANCES.last_updated_by%TYPE;
1156
1157 CURSOR c1_csr IS
1158 Select
1159 PA.created_by,
1160 PA.po_line_location_id,
1161 PA.action,
1162 PA.action_date,
1163 PA.accepted_flag,
1164 PA.Last_Updated_By
1165 from PO_ACCEPTANCES PA
1166 Where PA.po_release_id = p_po_release_id
1167 And PA.revision_num = p_revision_num - 1
1168 And PA.po_line_location_id is not null
1169 AND NOT EXISTS ( select 1
1170 From PO_ACCEPTANCES PA2
1171 Where PA2.po_release_id = p_po_release_id
1172 And PA2.revision_num = p_revision_num
1173 And PA2.po_line_location_id = PA.po_line_location_id);
1174
1175
1176 CURSOR c2_csr IS
1177 Select
1178 document_line_location_id
1179 from PO_CHANGE_REQUESTS PCR
1180 Where PCR.po_release_id = p_po_release_id
1181 And PCR.document_revision_num = p_revision_num - 1
1182 And PCR.document_line_location_id is not null
1183 And PCR.request_status = 'ACCEPTED'
1184 and PCR.initiator = 'SUPPLIER'
1185 And PCR.action_type = 'MODIFICATION'
1186 and PCR.REQUEST_LEVEL = 'SHIPMENT'
1187 AND NOT EXISTS ( select 1
1188 From PO_ACCEPTANCES PA2
1189 Where PA2.po_release_id = p_po_release_id
1190 And PA2.revision_num = p_revision_num
1191 And PA2.po_line_location_id = PCR.document_line_location_id);
1192
1193
1194 CURSOR c3_csr IS
1195 Select
1196 PA.created_by,
1197 PA.po_line_location_id,
1198 PA.action,
1199 PA.action_date,
1200 PA.accepted_flag,
1201 PA.Last_Updated_By
1202 from PO_ACCEPTANCES PA
1203 Where PA.po_header_id = p_po_header_id
1204 and PA.po_release_id is null
1205 And PA.revision_num = p_revision_num - 1
1206 And PA.po_line_location_id is not null
1207 AND NOT EXISTS ( select 1
1208 From PO_ACCEPTANCES PA2
1209 Where PA2.po_release_id = p_po_release_id
1210 And PA2.revision_num = p_revision_num
1211 And PA2.po_line_location_id = PA.po_line_location_id);
1212
1213
1214 CURSOR c4_csr IS
1215 Select
1216 document_line_location_id
1217 from PO_CHANGE_REQUESTS PCR
1218 Where PCR.document_header_id = p_po_header_id
1219 And PCR.po_release_id is null
1220 And PCR.document_revision_num = p_revision_num - 1
1221 And PCR.document_line_location_id is not null
1222 And PCR.request_status = 'ACCEPTED'
1223 and PCR.initiator = 'SUPPLIER'
1224 And PCR.action_type = 'MODIFICATION'
1225 and PCR.REQUEST_LEVEL = 'SHIPMENT'
1226 AND NOT EXISTS ( select 1
1227 From PO_ACCEPTANCES PA2
1228 Where PA2.po_header_id = p_po_header_id
1229 And PA2.po_release_id is null
1230 And PA2.revision_num = p_revision_num
1231 And PA2.po_line_location_id = PCR.document_line_location_id);
1232
1233
1234 -- End of Bug 2850566
1235
1236 BEGIN
1237
1238 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1239 FND_MSG_PUB.initialize;
1240 END IF;
1241
1242
1243 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1244 l_api_name, g_pkg_name)
1245 THEN
1246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1247 END IF;
1248
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250
1251 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1252 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1253 l_api_name || '.invoked', 'po_header_id: ' ||
1254 NVL(TO_CHAR(p_po_header_id),'null') || ' po_release_id: ' ||
1255 NVL(TO_CHAR(p_po_release_id),'null'));
1256 END IF;
1257
1258
1262 into l_buyer_id
1259 /* Copy the previous revision shipment-level acknowledgement. */
1260 If (p_po_release_id is not null) THEN
1261 select agent_id
1263 from po_releases_all
1264 where po_release_id = p_po_release_id;
1265
1266
1267 -- Bug 2850566 RBAIRRAJ
1268 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
1269 -- instead of writing an Insert statement.
1270 for c1_rec in c1_csr
1271 loop
1272
1273 l_user_id := c1_rec.Last_Updated_By;
1274 l_acceptance_id := NULL;
1275
1276 PO_ACCEPTANCES_INS_PVT.insert_row(
1277 x_rowid => l_rowid,
1278 x_acceptance_id => l_acceptance_id,
1279 x_Last_Update_Date => l_Last_Update_Date,
1280 x_Last_Updated_By => l_user_id,
1281 x_Last_Update_Login => l_Last_Update_Login,
1282 p_creation_date => sysdate,
1283 p_created_by => c1_rec.created_by,
1284 p_po_header_id => NULL,
1285 p_po_release_id => p_po_release_id,
1286 p_po_line_location_id => c1_rec.po_line_location_id,
1287 p_action => c1_rec.action,
1288 p_action_date => c1_rec.action_date,
1289 p_revision_num => p_revision_num,
1290 p_accepted_flag => c1_rec.accepted_flag);
1291 end loop;
1292 -- End of Bug 2850566
1293
1294
1295 /* If a shipment-level change has been approved, we'll treat this
1296 shipment as being accepted by the supplier. */
1297
1298 -- Bug 2850566 RBAIRRAJ
1299 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
1300 -- instead of writing an Insert statement.
1301 for c2_rec in c2_csr
1302 loop
1303 l_acceptance_id := NULL;
1304 l_Last_Updated_By := NULL;
1305
1306 PO_ACCEPTANCES_INS_PVT.insert_row(
1307 x_rowid => l_rowid,
1308 x_acceptance_id => l_acceptance_id,
1309 x_Last_Update_Date => l_Last_Update_Date,
1310 x_Last_Updated_By => l_Last_Updated_By,
1311 x_Last_Update_Login => l_Last_Update_Login,
1312 p_creation_date => sysdate,
1313 p_created_by => fnd_global.user_id,
1314 p_po_header_id => NULL,
1315 p_po_release_id => p_po_release_id,
1316 p_po_line_location_id => c2_rec.document_line_location_id,
1317 p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
1318 p_action_date => sysdate,
1319 p_revision_num => p_revision_num,
1320 p_accepted_flag => 'Y');
1321 end loop;
1322 -- End of Bug 2850566
1323
1324
1325 ELSIf (p_po_header_id is not null) THEN
1326 select agent_id
1327 into l_buyer_id
1328 from po_headers_all
1329 where po_header_id = p_po_header_id;
1330
1331
1332 -- Bug 2850566 RBAIRRAJ
1333 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
1334 -- instead of writing an Insert statement.
1335
1336 for c3_rec in c3_csr
1337 loop
1338 l_user_id := c3_rec.Last_Updated_By;
1339 l_acceptance_id := NULL;
1340
1341 PO_ACCEPTANCES_INS_PVT.insert_row(
1342 x_rowid => l_rowid,
1343 x_acceptance_id => l_acceptance_id,
1344 x_Last_Update_Date => l_Last_Update_Date,
1345 x_Last_Updated_By => l_user_id,
1346 x_Last_Update_Login => l_Last_Update_Login,
1347 p_creation_date => sysdate,
1348 p_created_by => c3_rec.created_by,
1349 p_po_header_id => p_po_header_id,
1350 p_po_release_id => NULL,
1351 p_po_line_location_id => c3_rec.po_line_location_id,
1352 p_action => c3_rec.action,
1353 p_action_date => c3_rec.action_date,
1354 p_revision_num => p_revision_num,
1355 p_accepted_flag => c3_rec.accepted_flag);
1356 end loop;
1357 -- End of Bug 2850566
1358
1359 /* If a shipment-level change has been approved, we'll treat this
1360 shipment as being accepted by the supplier. */
1361
1362
1363 -- Bug 2850566 RBAIRRAJ
1364 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
1365 -- instead of writing an Insert statement.
1366
1367 for c4_rec in c4_csr
1368 loop
1369 l_acceptance_id := NULL;
1370 l_Last_Updated_By := NULL;
1371
1372 PO_ACCEPTANCES_INS_PVT.insert_row(
1373 x_rowid => l_rowid,
1374 x_acceptance_id => l_acceptance_id,
1375 x_Last_Update_Date => l_Last_Update_Date,
1376 x_Last_Updated_By => l_Last_Updated_By,
1377 x_Last_Update_Login => l_Last_Update_Login,
1378 p_creation_date => sysdate,
1379 p_created_by => fnd_global.user_id,
1380 p_po_header_id => p_po_header_id,
1381 p_po_release_id => NULL,
1382 p_po_line_location_id => c4_rec.document_line_location_id,
1383 p_action => fnd_message.get_string('PO','PO_ACK_WEB'),
1384 p_action_date => sysdate,
1385 p_revision_num => p_revision_num,
1386 p_accepted_flag => 'Y');
1387 end loop;
1388 -- End of Bug 2850566
1389
1390 END IF;
1391
1392 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1396
1393 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1394 l_api_name || '. after carrying over shipment level ack. ', 'Log');
1395 END IF;
1397 /* If all shipments are acknowledged after carryover, post header-level
1398 acknowledgement. */
1399 IF (PO_ACKNOWLEDGE_PO_PVT.All_Shipments_Acknowledged(
1400 1.0,
1401 FND_API.G_FALSE,
1402 p_po_header_id,
1403 p_po_release_id,
1404 p_revision_num ) = FND_API.G_TRUE ) THEN
1405
1406 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1407 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1408 l_api_name || '.All_Shipments_Acknowledged. ', 'Log');
1409 END IF;
1410
1411 BEGIN
1412 If (p_po_release_id is not null) THEN
1413 Select 'A'
1414 into l_header_accepted_flag
1415 From sys.dual
1416 Where exists (
1417 select 1
1418 From PO_ACCEPTANCES
1419 Where po_release_id = p_po_release_id
1420 and revision_num = p_revision_num
1421 and po_line_location_id is not null
1422 and accepted_flag <> 'Y' );
1423
1424 ELSIF (p_po_header_id is not null) THEN
1425 Select 'A'
1426 into l_header_accepted_flag
1427 From sys.dual
1428 Where exists (
1429 select 'Y'
1430 From PO_ACCEPTANCES
1431 Where po_header_id = p_po_header_id
1432 and po_release_id is null
1433 and revision_num = p_revision_num
1434 and po_line_location_id is not null
1435 and accepted_flag <> 'Y' );
1436 END IF;
1437
1438 EXCEPTION
1439 When no_data_found then
1440 l_header_accepted_flag := 'Y';
1441 END;
1442
1443
1444 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1445 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1446 l_api_name || '.call Acknowledge_po', ' header_id: ' ||
1447 NVL(TO_CHAR(p_po_header_id), ' ') || 'release_id: ' ||
1448 NVL(TO_CHAR(p_po_release_id), ' ') || 'accepted_flag: ' ||
1449 NVL(l_header_accepted_flag, ''));
1450 END IF;
1451
1452 PO_ACKNOWLEDGE_PO_PVT.Acknowledge_po (
1453 p_po_header_id,
1454 p_po_release_id,
1455 p_revision_num,
1456 l_header_accepted_flag,
1457 null,
1458 l_buyer_id,
1459 fnd_global.user_id );
1460
1461 END IF;
1462
1463
1464 EXCEPTION
1465 WHEN FND_API.g_exc_error THEN
1466 x_return_status := FND_API.g_ret_sts_error;
1467 WHEN FND_API.g_exc_unexpected_error THEN
1468 x_return_status := FND_API.g_ret_sts_unexp_error;
1469 WHEN OTHERS THEN
1470 x_return_status := FND_API.g_ret_sts_unexp_error;
1471 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1472 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1473 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1474 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1475 l_api_name || '.others_exception', sqlcode);
1476 END IF;
1477 END IF;
1478 raise;
1479
1480 END Carry_Over_Acknowledgement;
1481
1482
1483 /**
1484 * Public function: All_Shipments_Responded
1485 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM
1486 * Modifies:
1487 * Effects: Returns if all the shipments have been either changed or
1488 * acknowledged.
1489 */
1490
1491 FUNCTION All_Shipments_Responded (
1492 p_api_version IN NUMBER,
1493 p_Init_Msg_List IN VARCHAR2,
1494 p_po_header_id IN NUMBER,
1495 p_po_release_id IN NUMBER,
1496 p_revision_num IN NUMBER )
1497 RETURN VARCHAR2 IS
1498
1499 L_ship_not_responded_flag VARCHAR2(1) := NULL;
1500
1501 CURSOR l_rel_ship_not_responded_csr IS
1502 select 'Y'
1503 From PO_LINE_LOCATIONS_ALL PLL
1504 Where pll.po_release_id = p_po_release_id
1505 And not exists (
1506 select 1
1507 From PO_ACCEPTANCES PA
1508 Where PA.po_release_id = p_po_release_id
1509 And pa.revision_num = p_revision_num
1510 And pa.po_line_location_id = PLL.line_location_id )
1511 And not exists (
1512 select 1
1513 From PO_CHANGE_REQUESTS pcr, po_releases_all por
1514 WHERE por.po_release_id = p_po_release_id
1515 and por.change_requested_by = 'SUPPLIER'
1516 and pcr.po_release_id = p_po_release_id
1517 AND PCR.document_revision_num = p_revision_num
1518 And ((pcr.document_line_location_id = PLL.line_location_id) OR
1519 (pcr.parent_line_location_id = PLL.line_location_id))
1520 and pcr.initiator = 'SUPPLIER'
1521 And ((pcr.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
1522 (pcr.request_status = 'REJECTED'
1523 and pcr.CHANGE_REQUEST_GROUP_ID = (
1524 select MAX(pcr2.CHANGE_REQUEST_GROUP_ID)
1525 from po_change_requests pcr2
1526 where pcr2.po_release_id = p_po_release_id
1530 And nvl(pll.payment_type,'NULL') NOT IN ('ADVANCE','DELIVERY')
1527 and pcr2.document_revision_num = p_revision_num
1528 and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP')) ) ) )
1529 And nvl(pll.cancel_flag, 'N') = 'N'
1531 And nvl(pll.closed_code, 'OPEN') not in ('CLOSED', 'FINALLY CLOSED');
1532
1533
1534 CURSOR l_po_ship_not_responded_csr IS
1535 select 'Y'
1536 From PO_LINE_LOCATIONS_ALL PLL
1537 Where pll.po_header_id = p_po_header_id
1538 And pll.po_release_id is null
1539 And not exists (
1540 select 1
1541 From PO_ACCEPTANCES PA
1542 Where PA.po_header_id = p_po_header_id
1543 And pa.revision_num = p_revision_num
1544 And pa.po_line_location_id = PLL.line_location_id )
1545 And not exists (
1546 select 1
1547 From PO_CHANGE_REQUESTS pcr, po_headers_all poh
1548 WHERE poh.po_header_id = p_po_header_id
1549 and poh.change_requested_by = 'SUPPLIER'
1550 and pcr.document_header_id = p_po_header_id
1551 AND PCR.document_revision_num = p_revision_num
1552 And ((pcr.document_line_location_id = PLL.line_location_id) OR
1553 (pcr.parent_line_location_id = PLL.line_location_id))
1554 and pcr.initiator = 'SUPPLIER'
1555 And ((pcr.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP','REQ_APP')) OR
1556 (pcr.request_status = 'REJECTED'
1557 and pcr.CHANGE_REQUEST_GROUP_ID = (
1558 select MAX(pcr2.CHANGE_REQUEST_GROUP_ID)
1559 from po_change_requests pcr2
1560 where pcr2.document_header_id = p_po_header_id
1561 and pcr2.document_revision_num = p_revision_num
1562 and pcr2.request_status in ('PENDING', 'BUYER_APP', 'WAIT_MGR_APP'))) ))
1563 And nvl(pll.cancel_flag, 'N') = 'N'
1564 And nvl(pll.payment_type,'NULL') NOT IN ('ADVANCE','DELIVERY')
1565 And nvl(pll.closed_code, 'OPEN') not in ('CLOSED', 'FINALLY CLOSED');
1566
1567
1568 l_api_name CONSTANT VARCHAR2(30) := 'ALL_SHIPMENTS_RESPONDED';
1569 l_api_version CONSTANT NUMBER := 1.0;
1570
1571
1572 BEGIN
1573
1574 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1575 FND_MSG_PUB.initialize;
1576 END IF;
1577
1578 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1579 l_api_name, g_pkg_name)
1580 THEN
1581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1582 END IF;
1583
1584 If (p_po_release_id is not null) then
1585 OPEN l_rel_ship_not_responded_csr;
1586 LOOP
1587 FETCH l_rel_ship_not_responded_csr INTO L_ship_not_responded_flag;
1588 EXIT WHEN l_rel_ship_not_responded_csr%NOTFOUND;
1589 IF (L_ship_not_responded_flag = 'Y') THEN
1590 EXIT;
1591 END IF;
1592 END LOOP;
1593 CLOSE l_rel_ship_not_responded_csr;
1594
1595 ELSIF (p_po_header_id is not null) then
1596 OPEN l_po_ship_not_responded_csr;
1597 LOOP
1598 FETCH l_po_ship_not_responded_csr INTO L_ship_not_responded_flag;
1599 EXIT WHEN l_po_ship_not_responded_csr%NOTFOUND;
1600 IF (L_ship_not_responded_flag = 'Y') THEN
1601 EXIT;
1602 END IF;
1603 END LOOP;
1604 CLOSE l_po_ship_not_responded_csr;
1605
1606 END IF;
1607
1608 If (L_ship_not_responded_flag = 'Y') THEN
1609 return FND_API.G_FALSE;
1610 ELSE
1611 return FND_API.G_TRUE;
1612 END IF;
1613
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1617 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1618 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1619 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1620 l_api_name || '.others_exception', sqlcode);
1621 END IF;
1622 END IF;
1623 raise;
1624
1625 END All_Shipments_Responded;
1626
1627
1628 /**
1629 * Public procedure: Set_Header_Acknowledgement
1630 * Requires: PO_HEADER_ID, PO_RELEASE_ID
1631 * Modifies: PO_ACCEPTANCES
1632 * Effects: For ack required PO, check if all shipments has been acknowledged
1633 * and if there is no supplier change pending, if both conditions
1634 * satisfied, post the header level acknowledgement record.
1635 * This API should be called after supplier submits the change requests and
1636 * after buyer responds to all supplier changes without revision increase.
1637 * Returns:
1638 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1639 * FND_API.G_RET_STS_ERROR if an error occurs
1640 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1641 */
1642 PROCEDURE Set_Header_Acknowledgement (
1643 p_api_version IN NUMBER,
1644 p_Init_Msg_List IN VARCHAR2,
1645 x_return_status OUT NOCOPY VARCHAR2,
1646 p_po_header_id IN NUMBER,
1647 p_po_release_id IN NUMBER )
1648 IS
1649
1653 l_accp_required_flag VARCHAR2(1) := null;
1650 l_api_name CONSTANT VARCHAR2(30) := 'SET_HEADER_ACKNOWLEDGEMENT';
1651 l_api_version CONSTANT NUMBER := 1.0;
1652
1654 l_ship_accepted_flag VARCHAR2(1) := null;
1655 l_header_accepted_flag VARCHAR2(1) := null;
1656 l_change_requested_by PO_HEADERS_ALL.change_requested_by%TYPE := null; l_revision_num NUMBER;
1657 l_buyer_id NUMBER;
1658
1659 CURSOR l_rel_ship_accp_csr(rev_num NUMBER) IS
1660 SELECT accepted_flag
1661 FROM po_acceptances
1662 WHERE po_release_id = p_po_release_id
1663 AND revision_num = rev_num
1664 AND po_line_location_id is not null;
1665
1666 CURSOR l_po_ship_accp_csr(rev_num NUMBER) IS
1667 SELECT accepted_flag
1668 FROM po_acceptances
1669 WHERE po_header_id = p_po_header_id
1670 AND revision_num = rev_num
1671 AND po_line_location_id is not null;
1672
1673 BEGIN
1674
1675 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1676 FND_MSG_PUB.initialize;
1677 END IF;
1678
1679 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1680 l_api_name, g_pkg_name)
1681 THEN
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 END IF;
1684
1685 x_return_status := FND_API.G_RET_STS_SUCCESS;
1686
1687 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1688 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1689 l_api_name || '.invoked', 'po_header_id: ' ||
1690 NVL(TO_CHAR(p_po_header_id),'null') || ' po_release_id: ' ||
1691 NVL(TO_CHAR(p_po_release_id),'null'));
1692 END IF;
1693
1694
1695 IF (p_po_release_id is not null) THEN
1696 SELECT agent_id,
1697 revision_num,
1698 acceptance_required_flag,
1699 change_requested_by
1700 INTO l_buyer_id,
1701 l_revision_num,
1702 l_accp_required_flag,
1703 l_change_requested_by
1704 FROM PO_RELEASES_ALL
1705 WHERE po_release_id = p_po_release_id;
1706
1707 ELSIF (p_po_header_id is not null) THEN
1708 SELECT agent_id,
1709 revision_num,
1710 acceptance_required_flag,
1711 change_requested_by
1712 INTO l_buyer_id,
1713 l_revision_num,
1714 l_accp_required_flag,
1715 l_change_requested_by
1716 FROM PO_HEADERS_ALL
1717 WHERE po_header_id = p_po_header_id;
1718
1719 END IF;
1720
1721
1722 /* If PO does not require acknowledgement, no need to go further. */
1723 IF (l_accp_required_flag is null OR l_accp_required_flag = 'N') THEN
1724 RETURN;
1725 END IF;
1726
1727
1728 /* Check if there is no supplier change pending and all shipments have been
1729 acknowledged. */
1730 IF (NVL(l_change_requested_by, ' ') <> 'SUPPLIER' AND
1731 PO_ACKNOWLEDGE_PO_PVT.All_Shipments_Acknowledged (
1732 1.0,
1733 FND_API.G_FALSE,
1734 P_po_header_id,
1735 p_po_release_id,
1736 l_revision_num ) = FND_API.G_TRUE ) THEN
1737
1738 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1739 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix ||
1740 l_api_name || '.All_Shipments_Acknowledged', 'po_header_id: ' ||
1741 NVL(TO_CHAR(p_po_header_id),'null') || ' po_release_id: ' ||
1742 NVL(TO_CHAR(p_po_release_id),'null'));
1743 END IF;
1744
1745
1746
1747 /* If there exist different type of shipment-level accepted_flag,
1748 header level accepted_flag will be saved as 'A'. */
1749
1750 BEGIN
1751 IF (p_po_release_id is not null) THEN
1752 OPEN l_rel_ship_accp_csr(l_revision_num);
1753 FETCH l_rel_ship_accp_csr INTO l_ship_accepted_flag;
1754 CLOSE l_rel_ship_accp_csr;
1755
1756 ELSIF (p_po_header_id is not null) THEN
1757 OPEN l_po_ship_accp_csr(l_revision_num);
1758 FETCH l_po_ship_accp_csr INTO l_ship_accepted_flag;
1759 CLOSE l_po_ship_accp_csr;
1760
1761 END IF;
1762
1763 EXCEPTION
1764 WHEN OTHERS THEN
1765 l_ship_accepted_flag := NULL;
1766 END;
1767
1768 /* If there is no ship ack records, no need to go further. */
1769 IF (l_ship_accepted_flag = null) THEN
1770 RETURN;
1771 END IF;
1772
1773 l_header_accepted_flag := l_ship_accepted_flag;
1774
1775
1776 BEGIN
1777 IF (p_po_release_id is not null) THEN
1778 SELECT 'A'
1779 INTO l_header_accepted_flag
1780 FROM sys.dual
1781 WHERE exists (
1782 SELECT 1
1783 FROM PO_ACCEPTANCES
1784 WHERE po_release_id = p_po_release_id
1785 AND revision_num = l_revision_num
1786 AND po_line_location_id is not null
1787 AND accepted_flag <> l_ship_accepted_flag );
1788
1789 ELSIF (p_po_header_id is not null) THEN
1790 SELECT 'A'
1791 INTO l_header_accepted_flag
1792 FROM sys.dual
1793 WHERE exists (
1794 SELECT 'Y'
1795 FROM PO_ACCEPTANCES
1796 WHERE po_header_id = p_po_header_id
1797 AND po_release_id is null
1798 AND revision_num = l_revision_num
1799 AND po_line_location_id is not null
1800 AND accepted_flag <> l_ship_accepted_flag );
1801
1802 END IF;
1803
1804 EXCEPTION
1808
1805 WHEN NO_DATA_FOUND THEN
1806 l_header_accepted_flag := l_ship_accepted_flag;
1807 END;
1809 IF (l_header_accepted_flag is not null) THEN
1810 PO_ACKNOWLEDGE_PO_PVT.Acknowledge_po (
1811 p_po_header_id,
1812 p_po_release_id,
1813 l_revision_num,
1814 l_header_accepted_flag,
1815 null, -- note
1816 l_buyer_id,
1817 fnd_global.user_id );
1818 END IF;
1819
1820 END IF; -- End if all_shipments_acknowledged
1821
1822 EXCEPTION
1823 WHEN FND_API.g_exc_error THEN
1824 x_return_status := FND_API.g_ret_sts_error;
1825 WHEN FND_API.g_exc_unexpected_error THEN
1826 x_return_status := FND_API.g_ret_sts_unexp_error;
1827 WHEN OTHERS THEN
1828 x_return_status := FND_API.g_ret_sts_unexp_error;
1829 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1830 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1831 IF (g_fnd_debug = 'Y') AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1832 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1833 l_api_name || '.others_exception', sqlcode);
1834 END IF;
1835 END IF;
1836 raise;
1837
1838 END Set_Header_Acknowledgement;
1839
1840
1841
1842 END PO_ACKNOWLEDGE_PO_PVT;
[Home] [Help]