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