[Home] [Help]
PACKAGE BODY: APPS.PO_DIFF_SUMMARY_PKG
Source
1 PACKAGE BODY PO_DIFF_SUMMARY_PKG AS
2 -- $Header: PO_DIFF_SUMMARY_PKG.plb 120.12.12020000.3 2013/02/10 13:19:42 vegajula ship $
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_DIFF_SUMMARY_PKG');
6
7 g_ITEMTYPE CONSTANT VARCHAR2(20) := 'PODSNOTF';
8 g_ITEMKEY_PREFIX CONSTANT VARCHAR2(50) := 'PO_DS_ACCEPT_NOTIF';
9
10 --==========================================================================
11 -- Private procedures prototype
12 --==========================================================================
13 PROCEDURE accept_translations
14 ( p_draft_id IN NUMBER
15 );
16
17 PROCEDURE autoaccept_deleted_records
18 ( p_draft_id IN NUMBER
19 );
20
21 FUNCTION autoaccept_unchanged_records
22 ( p_draft_id IN NUMBER
23 ) RETURN VARCHAR2;
24
25 FUNCTION has_record_to_accept
26 ( p_draft_id IN NUMBER
27 ) RETURN VARCHAR2;
28
29
30 PROCEDURE accept_if_no_line_changes
31 ( p_draft_id IN NUMBER
32 );
33
34 PROCEDURE cascade_change_acceptance
35 ( p_draft_id IN NUMBER,
36 p_line_id_list IN PO_TBL_NUMBER,
37 p_change_accepted_value IN VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE validate_disposition
42 ( p_draft_id IN NUMBER,
43 p_reject_line_id_list IN PO_TBL_NUMBER,
44 x_invalid_line_id_list OUT NOCOPY PO_TBL_NUMBER,
45 x_invalid_line_num_list OUT NOCOPY PO_TBL_NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2
47 );
48
49 --==========================================================================
50 -- Workflow related procedures
51 --==========================================================================
52
53 ------------------------------------------------------------------------------
54 --Start of Comments
55 --Name: start_workflow
56 --Function:
57 -- Initiates Buyer Acceptance Workflow for draft changes submitted by
58 -- Supplier or Catalog Admin
59 -- If Draft does not exists, this procedure returns without initiating the
60 -- workflow
61 --Parameters:
62 --IN:
63 --p_po_header_id
64 -- Header id of the document
65 --End of Comments
66 -------------------------------------------------------------------------------
67 PROCEDURE start_workflow
68 ( p_po_header_id IN NUMBER
69 ) IS
70
71 d_api_name CONSTANT VARCHAR2(30) := 'send_acceptance_notification';
72 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
73 d_position NUMBER;
74
75 l_ItemKey VARCHAR2(240);
76 l_agreement_num PO_HEADERS_ALL.segment1%TYPE;
77 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
78 l_draft_id PO_DRAFTS.draft_id%TYPE;
79 l_draft_status PO_DRAFTS.status%TYPE;
80 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
81 l_draft_owner_user_id PO_DRAFTS.owner_user_id%TYPE;
82 l_org_id PO_HEADERS_ALL.org_id%TYPE;
83 l_agent_id PO_HEADERS_ALL.agent_id%TYPE;
84 l_buyer_user_name FND_USER.user_name%TYPE;
85 l_buyer_name_dsp VARCHAR2(1000);
86
87 BEGIN
88 d_position := 0;
89
90 IF (PO_LOG.d_proc) THEN
91 PO_LOG.proc_begin (d_module);
92 END IF;
93
94 -- Get the agreement number and revision number
95 SELECT NVL(revision_num, 0), segment1, org_id, agent_id
96 INTO l_revision_num, l_agreement_num, l_org_id, l_agent_id
97 FROM po_headers_all
98 WHERE po_header_id = p_po_header_id;
99
100 d_position := 10;
101
102 PO_DRAFTS_PVT.find_draft
103 ( p_po_header_id => p_po_header_id,
104 x_draft_id => l_draft_id,
105 x_draft_status => l_draft_status,
106 x_draft_owner_role => l_draft_owner_role
107 );
108
109 IF (l_draft_id IS NULL) THEN
110 -- no draft exists. Do nothing.
111 RETURN;
112 END IF;
113
114 SELECT owner_user_id
115 INTO l_draft_owner_user_id
116 FROM po_drafts DFT
117 WHERE draft_id = l_draft_id;
118
119 d_position := 20;
120
121 -- Get buyer user name
122 PO_REQAPPROVAL_INIT1.get_user_name
123 ( l_agent_id,
124 l_buyer_user_name,
125 l_buyer_name_dsp
126 );
127
128 l_ItemKey := get_new_itemkey
129 ( p_draft_id => l_draft_id
130 );
131
132 d_position := 30;
133
134 WF_ENGINE.createProcess
135 ( ItemType => g_ITEMTYPE,
136 ItemKey => l_ItemKey,
137 process => 'PO_DS_BUYER_ACCEPTANCE_PROCESS'
138 );
139
140 -- Set all the Item Attributes
141
142 PO_WF_UTIL_PKG.SetItemAttrNumber
143 ( itemtype => g_ITEMTYPE,
144 itemkey => l_ItemKey,
145 aname => 'PO_HEADER_ID',
146 avalue => p_po_header_id
147 );
148
149 PO_WF_UTIL_PKG.SetItemAttrText
150 ( itemtype => g_ITEMTYPE,
151 itemkey => l_ItemKey,
152 aname => 'AGREEMENT_NUM',
153 avalue => l_agreement_num
154 );
155
156 d_position := 40;
157
158 PO_WF_UTIL_PKG.SetItemAttrNumber
159 ( itemtype => g_ITEMTYPE,
160 itemkey => l_ItemKey,
161 aname => 'ORG_ID',
162 avalue => l_org_id
163 );
164
165 PO_WF_UTIL_PKG.SetItemAttrNumber
166 ( itemtype => g_ITEMTYPE,
167 itemkey => l_ItemKey,
168 aname => 'REVISION_NUM',
169 avalue => l_revision_num
170 );
171
172 d_position := 50;
173
174 PO_WF_UTIL_PKG.SetItemAttrNumber
175 ( itemtype => g_ITEMTYPE,
176 itemkey => l_ItemKey,
177 aname => 'DRAFT_ID',
178 avalue => l_draft_id
179 );
180
181 PO_WF_UTIL_PKG.SetItemAttrText
182 ( itemtype => g_ITEMTYPE,
183 itemkey => l_ItemKey,
184 aname => 'DRAFT_OWNER_ROLE',
185 avalue => l_draft_owner_role
186 );
187
188 d_position := 60;
189
190 PO_WF_UTIL_PKG.SetItemAttrNumber
191 ( itemtype => g_ITEMTYPE,
192 itemkey => l_ItemKey,
193 aname => 'DRAFT_OWNER_USER_ID',
194 avalue => l_draft_owner_user_id
195 );
196
197 PO_WF_UTIL_PKG.SetItemAttrNumber
198 ( itemtype => g_ITEMTYPE,
199 itemkey => l_ItemKey,
200 aname => 'AGENT_ID',
201 avalue => l_agent_id
202 );
203
204 d_position := 70;
205
206 PO_WF_UTIL_PKG.SetItemAttrText
207 ( itemtype => g_ITEMTYPE,
208 itemkey => l_ItemKey,
209 aname => 'BUYER_USER_NAME',
213 PO_WF_UTIL_PKG.SetItemAttrText
210 avalue => l_buyer_user_name
211 );
212
214 ( itemtype => g_ITEMTYPE,
215 itemkey => l_ItemKey,
216 aname => 'BUYER_NAME_DSP',
217 avalue => l_buyer_name_dsp
218 );
219
220 d_position := 80;
221
222 -- Start the workflow process
223 WF_ENGINE.startProcess
224 ( ItemType => g_ITEMTYPE,
225 ItemKey => l_ItemKey
226 );
227
228 IF (PO_LOG.d_proc) THEN
229 PO_LOG.proc_end (d_module);
230 END IF;
231
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 WF_CORE.context (d_pkg_name,d_api_name, d_position || ': No Data Found');
235 RAISE;
236 WHEN OTHERS THEN
237 WF_CORE.context (d_pkg_name,d_api_name, d_position || ': SQL Error ' || sqlcode);
238 RAISE;
239 END start_workflow;
240
241 PROCEDURE selector
242 ( item_type IN VARCHAR2,
243 item_key IN VARCHAR2,
244 activity_id IN NUMBER,
245 command IN VARCHAR2,
246 resultout IN OUT NOCOPY VARCHAR2
247 ) IS
248
249 d_api_name CONSTANT VARCHAR2(30) := 'selector';
250 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
251 d_position NUMBER;
252
253 l_cur_org_id PO_HEADERS_ALL.org_id%TYPE;
254 l_org_id PO_HEADERS_ALL.org_id%TYPE;
255 BEGIN
256 d_position := 0;
257 IF (PO_LOG.d_proc) THEN
258 PO_LOG.proc_begin (d_module, 'item_type', item_type);
259 PO_LOG.proc_begin (d_module, 'item_key', item_key);
260 PO_LOG.proc_begin (d_module, 'activity_id', activity_id);
261 PO_LOG.proc_begin (d_module, 'command', command);
262 END IF;
263
264 IF (command = 'RUN') THEN
265 d_position := 10;
266 resultout := 'PO_DS_BUYER_ACCEPTANCE_PROCESS';
267
268 ELSIF (command = 'SET_CTX') THEN
269 d_position := 20;
270 l_org_id :=
271 PO_WF_UTIL_PKG.GetItemAttrNumber
272 ( itemtype => item_type,
273 itemkey => item_key,
274 aname => 'ORG_ID'
275 );
276
277 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
278
279 ELSIF (command = 'TEST_CTX') THEN
280 d_position := 30;
281 l_cur_org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
282
283 l_org_id :=
284 PO_WF_UTIL_PKG.GetItemAttrNumber
285 ( itemtype => item_type,
286 itemkey => item_key,
287 aname => 'ORG_ID'
288 );
289
290 IF (l_cur_org_id IS NULL) THEN
291 resultout := 'NOTSET';
292 ELSIF (l_cur_org_id = l_org_id) THEN
293 resultout := 'TRUE';
294 ELSE
295 resultout := 'FALSE';
296 END IF;
297
298 END IF;
299
300 IF (PO_LOG.d_proc) THEN
301 PO_LOG.proc_end (d_module);
302 END IF;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
307 RAISE;
308 END selector;
309
310
311
312
313 PROCEDURE mark_autoaccept_lines
314 ( itemtype IN VARCHAR2,
315 itemkey IN VARCHAR2,
316 actid IN NUMBER,
317 funcmode IN VARCHAR2,
318 resultout OUT NOCOPY VARCHAR2
319 ) IS
320
321 d_api_name CONSTANT VARCHAR2(30) := 'mark_autoaccept_lines';
322 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
323 d_position NUMBER;
324
325 l_contains_changes VARCHAR2(1);
326 l_acceptance_required VARCHAR2(1);
327 l_org_id PO_HEADERS_ALL.org_id%TYPE;
328 l_draft_id PO_DRAFTS.draft_id%TYPE;
329 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
330 l_acceptance_level PO_SYSTEM_PARAMETERS_ALL.cat_admin_authoring_acceptance%TYPE;
331 BEGIN
332 d_position := 0;
333
334 IF (PO_LOG.d_proc) THEN
335 PO_LOG.proc_begin (d_module);
336 END IF;
337
338 l_acceptance_required := FND_API.G_TRUE;
339
340 l_org_id :=
341 PO_WF_UTIL_PKG.GetItemAttrNumber
342 ( itemtype => itemType,
343 itemkey => itemKey,
344 aname => 'ORG_ID'
345 );
346
347 l_draft_id :=
348 PO_WF_UTIL_PKG.GetItemAttrNumber
349 ( itemtype => itemType,
350 itemkey => itemKey,
351 aname => 'DRAFT_ID'
352 );
353
354 l_draft_owner_role :=
355 PO_WF_UTIL_PKG.GetItemAttrText
356 ( itemtype => itemType,
357 itemkey => itemKey,
358 aname => 'DRAFT_OWNER_ROLE'
359 );
360
361 -- Get acceptance level setting from po_system_paramaters
362 -- For Cat Admin, default acceptance level is 'No Acceptance Required'
363 -- For Supplier, default acceptance level is 'Acceptance Required'
364 SELECT DECODE (l_draft_owner_role,
365 PO_GLOBAL.g_ROLE_CAT_ADMIN,
366 NVL(PSP.cat_admin_authoring_acceptance, 'NOT_REQUIRED'),
367 PO_GLOBAL.g_ROLE_SUPPLIER,
368 NVL(PSP.supplier_authoring_acceptance, 'REQUIRED'))
369 INTO l_acceptance_level
370 FROM po_system_parameters_all PSP
371 WHERE PSP.org_id = l_org_id;
372
373 -- bug5249414
374 -- Accept attribute values tlp records that are translations
375 accept_translations
376 ( p_draft_id => l_draft_id
377 );
378
379
380 IF (l_acceptance_level = 'NOT_REQUIRED') THEN
381 l_acceptance_required := FND_API.G_FALSE;
382
383 ELSE
384
385 -- bug5570989
386 -- accept records with delete_flag = 'Y' but without corresponding lines
387 -- in the txn table
388 autoaccept_deleted_records
389 ( p_draft_id => l_draft_id
390 );
391
392
393 -- bug5249414 START
394
395 IF (l_acceptance_level = 'REQ_PRICE_CHANGES') THEN
396
397 l_acceptance_required :=
398 accept_lines_within_tolerance
399 ( p_draft_id => l_draft_id
400 );
401 END IF;
402
403 -- Clean up process...
404 -- IF there are still lines that require acceptance, make sure that
405 -- the changes they are making are supported by Diff Summary
406 IF (l_acceptance_required = FND_API.G_TRUE) THEN
407
408 l_acceptance_required :=
409 autoaccept_unchanged_records
410 ( p_draft_id => l_draft_id
411 );
412
413 END IF;
414
415 -- bug5249414 END
416 END IF;
417
418 PO_WF_UTIL_PKG.SetItemAttrText
419 ( itemtype => itemType,
420 itemkey => itemKey,
424
421 aname => 'BUYER_ACCEPTANCE_REQUIRED',
422 avalue => l_acceptance_required
423 );
425 IF (PO_LOG.d_proc) THEN
426 PO_LOG.proc_end (d_module);
427 END IF;
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
432 RAISE;
433 END mark_autoaccept_lines;
434
435
436 PROCEDURE buyer_acceptance_required
437 ( itemtype IN VARCHAR2,
438 itemkey IN VARCHAR2,
439 actid IN NUMBER,
440 funcmode IN VARCHAR2,
441 resultout OUT NOCOPY VARCHAR2
442 ) IS
443
444 d_api_name CONSTANT VARCHAR2(30) := 'buyer_acceptance_required';
445 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
446 d_position NUMBER;
447
448 l_accept_required VARCHAR2(1);
449 BEGIN
450 d_position := 0;
451
452 IF (PO_LOG.d_proc) THEN
453 PO_LOG.proc_begin (d_module);
454 END IF;
455
456 l_accept_required := PO_WF_UTIL_PKG.GetItemAttrText
457 ( itemtype => itemtype,
458 itemkey => itemkey,
459 aname => 'BUYER_ACCEPTANCE_REQUIRED'
460 );
461
462 IF (l_accept_required = FND_API.G_TRUE) THEN
463 resultout := WF_ENGINE.eng_completed || ':Y';
464 ELSE
465 resultout := WF_ENGINE.eng_completed || ':N';
466 END IF;
467
468 IF (PO_LOG.d_proc) THEN
469 PO_LOG.proc_end (d_module);
470 END IF;
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
475 RAISE;
476 END buyer_acceptance_required;
477
478 PROCEDURE transfer_if_all_autoaccepted
479 ( itemtype IN VARCHAR2,
480 itemkey IN VARCHAR2,
481 actid IN NUMBER,
482 funcmode IN VARCHAR2,
483 resultout OUT NOCOPY VARCHAR2
484 ) IS
485
486 d_api_name CONSTANT VARCHAR2(30) := 'transfer_if_all_autoaccepted';
487 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
488 d_position NUMBER;
489
490 l_transfer_flag VARCHAR2(1);
491
492 BEGIN
493 d_position := 0;
494
495 IF (PO_LOG.d_proc) THEN
496 PO_LOG.proc_begin (d_module);
497 END IF;
498
499 l_transfer_flag := PO_WF_UTIL_PKG.GetItemAttrText
500 ( itemtype => itemtype,
501 itemkey => itemkey,
502 aname => 'TRANSFER_IF_AUTOACCEPT_ALL'
503 );
504
505 IF (l_transfer_flag = FND_API.G_TRUE) THEN
506 resultout := WF_ENGINE.eng_completed || ':Y';
507 ELSE
508 resultout := WF_ENGINE.eng_completed || ':N';
509 END IF;
510
511 IF (PO_LOG.d_proc) THEN
512 PO_LOG.proc_end (d_module);
513 END IF;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
518 RAISE;
519 END transfer_if_all_autoaccepted;
520
521
522 PROCEDURE start_buyer_acceptance_process
523 ( itemtype IN VARCHAR2,
524 itemkey IN VARCHAR2,
525 actid IN NUMBER,
526 funcmode IN VARCHAR2,
527 resultout OUT NOCOPY VARCHAR2
528 ) IS
529
530 d_api_name CONSTANT VARCHAR2(30) := 'start_buyer_acceptance_process';
531 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
532 d_position NUMBER;
533
534 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
535 l_draft_id PO_DRAFTS.draft_id%TYPE;
536 l_emp_id FND_USER.employee_id%TYPE;
537 l_buyer_user_name FND_USER.user_name%TYPE;
538
539 l_lock_owner_role PO_DRAFTS.owner_role%TYPE;
540 l_lock_owner_user_id PO_DRAFTS.owner_user_id%TYPE;
541
542 l_lock_owner_wf_role WF_USERS.name%TYPE;
543 l_lock_owner_wf_role_dsp WF_USERS.display_name%TYPE;
544
545 BEGIN
546 d_position := 0;
547
548 IF (PO_LOG.d_proc) THEN
549 PO_LOG.proc_begin (d_module);
550 END IF;
551
552 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber
553 ( itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'PO_HEADER_ID'
556 );
557
558 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
559 ( itemtype => itemtype,
560 itemkey => itemkey,
561 aname => 'DRAFT_ID'
562 );
563
564 d_position := 10;
565
566 PO_DRAFTS_PVT.update_draft_status
567 ( p_draft_id => l_draft_id,
568 p_new_status => PO_DRAFTS_PVT.g_status_IN_PROCESS
569 );
570
571 l_buyer_user_name := PO_WF_UTIL_PKG.GetItemAttrText
572 ( itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'BUYER_USER_NAME'
575 );
576
577 -- Initially, the user role of the acceptance party is the buyer of
578 -- the document
579 PO_WF_UTIL_PKG.SetItemAttrText
580 ( itemtype => itemtype,
581 itemkey => itemkey,
582 aname => 'ACCEPTANCE_USER_WF_ROLE',
583 avalue => l_buyer_user_name
584 );
585
586 d_position := 20;
587
588 -- bug5249393
589 -- Get the document lock owner as it is the last owner that touched the
590 -- document
591 PO_DRAFTS_PVT.get_lock_owner_info
592 ( p_po_header_id => l_po_header_id,
593 x_lock_owner_role => l_lock_owner_role,
594 x_lock_owner_user_id => l_lock_owner_user_id
595 );
596
597 PO_ONLINE_AUTHORING_WF_ACTIONS.get_wf_role_for_lock_owner
598 ( p_po_header_id => l_po_header_id,
599 p_lock_owner_role => l_lock_owner_role,
600 p_lock_owner_user_id => l_lock_owner_user_id,
601 x_wf_role_name => l_lock_owner_wf_role,
602 x_wf_role_name_dsp => l_lock_owner_wf_role_dsp
603 );
604
605 WF_ENGINE.SetItemAttrText
606 ( itemtype => itemType,
607 itemkey => itemKey,
608 aname => 'DRAFT_OWNER_WF_ROLE',
609 avalue => l_lock_owner_wf_role
610 );
611
612 WF_ENGINE.SetItemAttrText
613 ( itemtype => itemType,
614 itemkey => itemKey,
615 aname => 'DRAFT_OWNER_NAME_DSP',
616 avalue => l_lock_owner_wf_role_dsp
617 );
618
619 IF (PO_LOG.d_proc) THEN
620 PO_LOG.proc_end (d_module);
621 END IF;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
626 RAISE;
627 END start_buyer_acceptance_process;
628
629
630 PROCEDURE get_buyers_manager
631 ( itemtype IN VARCHAR2,
632 itemkey IN VARCHAR2,
633 actid IN NUMBER,
634 funcmode IN VARCHAR2,
635 resultout OUT NOCOPY VARCHAR2
636 ) IS
637
638 d_api_name CONSTANT VARCHAR2(30) := 'get_buyers_manager';
639 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
640 d_position NUMBER;
641
642 l_agent_id PO_HEADERS_ALL.agent_id%TYPE;
643 l_manager_id NUMBER;
644 l_manager_user_name FND_USER.user_name%TYPE;
645 l_manager_name_dsp VARCHAR2(1000);
646 BEGIN
647 d_position := 0;
648
649 l_agent_id := PO_WF_UTIL_PKG.GetItemAttrNumber
650 ( itemtype => itemtype,
651 itemkey => itemkey,
652 aname => 'AGENT_ID'
653 );
654
655 d_position := 10;
656
657 BEGIN
658 SELECT supervisor_id
659 INTO l_manager_id
660 FROM hr_employees_current_v HREMP
661 WHERE employee_id = l_agent_id;
662
663 -- Get manager's user name
664 PO_REQAPPROVAL_INIT1.get_user_name
665 ( l_manager_id,
666 l_manager_user_name,
667 l_manager_name_dsp
668 );
669
670 d_position := 20;
671
672 -- Change user that performs acceptance
673 PO_WF_UTIL_PKG.SetItemAttrText
674 ( itemtype => itemtype,
675 itemkey => itemkey,
676 aname => 'ACCEPTANCE_USER_WF_ROLE',
677 avalue => l_manager_user_name
678 );
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 -- if supervisor cannot be found, do no change the acceptance performer
683 d_position := 30;
684 NULL;
685 END;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
690 RAISE;
691 END get_buyers_manager;
692
693
694
695
696 PROCEDURE buyer_accept_changes
697 ( itemtype IN VARCHAR2,
698 itemkey IN VARCHAR2,
699 actid IN NUMBER,
700 funcmode IN VARCHAR2,
701 resultout OUT NOCOPY VARCHAR2
702 ) IS
703
704 d_api_name CONSTANT VARCHAR2(30) := 'buyer_accept_changes';
705 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
706 d_position NUMBER;
707
708 BEGIN
709 d_position := 0;
710
711 IF (PO_LOG.d_proc) THEN
712 PO_LOG.proc_begin (d_module);
713 END IF;
714
715 PO_WF_UTIL_PKG.SetItemAttrText
716 ( itemtype => itemType,
717 itemkey => itemKey,
718 aname => 'ACCEPTANCE_RESULT',
719 avalue => PO_DRAFTS_PVT.g_ACCEPT_ALL
720 );
721
722 IF (PO_LOG.d_proc) THEN
723 PO_LOG.proc_end (d_module);
724 END IF;
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
729 RAISE;
730 END buyer_accept_changes;
731
732
733 PROCEDURE buyer_reject_changes
734 ( itemtype IN VARCHAR2,
735 itemkey IN VARCHAR2,
736 actid IN NUMBER,
737 funcmode IN VARCHAR2,
738 resultout OUT NOCOPY VARCHAR2
739 ) IS
740
741 d_api_name CONSTANT VARCHAR2(30) := 'buyer_reject_changes';
742 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
743 d_position NUMBER;
744
745 l_draft_id PO_DRAFTS.draft_id%TYPE;
746 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
747 l_created_language PO_HEADERS_ALL.created_language%TYPE;
748 l_reject_list PO_TBL_NUMBER;
749
750 l_return_status VARCHAR2(1);
751
752 BEGIN
753 d_position := 0;
754
755 IF (PO_LOG.d_proc) THEN
756 PO_LOG.proc_begin (d_module);
757 END IF;
758
759 l_draft_id :=
760 PO_WF_UTIL_PKG.GetItemAttrNumber
761 ( itemtype => itemType,
762 itemkey => itemKey,
763 aname => 'DRAFT_ID'
764 );
765
766 l_po_header_id :=
767 PO_WF_UTIL_PKG.GetItemAttrNumber
768 ( itemtype => itemType,
769 itemkey => itemKey,
770 aname => 'PO_HEADER_ID'
771 );
772
773 d_position := 10;
774
775 SELECT created_language
776 INTO l_created_language
777 FROM po_headers_all
778 WHERE po_header_id = l_po_header_id;
779
780 d_position := 20;
781
782 -- bug5249414
783 -- When user clicks "Reject All", we only want to reject the lines that
784 -- are visible to the user.
785 -- Example: If the price that cat admin updates does not exceed price
786 -- tolerance and the acceptance level is "Required only if
787 -- price tolerance exceeded", then the line will be automatically
788 -- accepted and won't show up in the report. If buyer clicks reject
789 -- all, the line should still be accepted since the buyer can't see
790 -- it in the report to begin with
791 -- On the other hand, if buyer does reject a line, the whole pending
792 -- changes for the line will be rejected, even though it contains
793 -- things that are supposed to be autoaccepted, like tranlsations
794 -- and flexfield changes.
795
796 SELECT *
797 BULK COLLECT
798 INTO l_reject_list
799 FROM
800 (
801 SELECT po_line_id
802 FROM po_lines_draft_all
803 WHERE draft_id = l_draft_id
804 AND change_accepted_flag IS NULL
805 UNION
806 SELECT po_line_id
807 FROM po_line_locations_draft_all
808 WHERE draft_id = l_draft_id
809 AND change_accepted_flag IS NULL
810 UNION
811 SELECT entity_id
812 FROM po_price_diff_draft
813 WHERE draft_id = l_draft_id
814 AND entity_type = 'BLANKET LINE'
815 AND change_accepted_flag IS NULL
816 UNION
817 SELECT NVL(LLD.po_line_id, LL.po_line_id)
818 FROM po_line_locations_draft_all LLD,
819 po_line_locations_all LL,
820 po_price_diff_draft PDD
821 WHERE PDD.draft_id = l_draft_id
822 AND entity_type = 'PRICE BREAK'
823 AND PDD.change_accepted_flag IS NULL
824 AND PDD.entity_id = LLD.line_location_id(+)
825 AND PDD.draft_id = LLD.draft_id(+)
826 AND PDD.entity_id = LL.line_location_id(+)
827 UNION
828 SELECT po_line_id
829 FROM po_attribute_values_draft
830 WHERE draft_id = l_draft_id
831 AND change_accepted_flag IS NULL
832 UNION
833 SELECT po_line_id
834 FROM po_attribute_values_tlp_draft
835 WHERE draft_id = l_draft_id
836 AND language = l_created_language
837 AND change_accepted_flag IS NULL
838 );
839
840 d_position := 30;
841
842 cascade_change_acceptance
843 ( p_draft_id => l_draft_id,
844 p_line_id_list => l_reject_list,
845 p_change_accepted_value => 'N',
846 x_return_status => l_return_status
847 );
848
849 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
851 END IF;
852
853
854 IF (PO_LOG.d_proc) THEN
855 PO_LOG.proc_end (d_module);
856 END IF;
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
861 RAISE;
862 END buyer_reject_changes;
863
864
865 PROCEDURE any_lines_get_rejected
866 ( itemtype IN VARCHAR2,
867 itemkey IN VARCHAR2,
871 ) IS
868 actid IN NUMBER,
869 funcmode IN VARCHAR2,
870 resultout OUT NOCOPY VARCHAR2
872
873 d_api_name CONSTANT VARCHAR2(30) := 'any_lines_get_rejected';
874 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
875 d_position NUMBER;
876
877 l_draft_id PO_DRAFTS.draft_id%TYPE;
878 l_any_lines_get_rejected VARCHAR2(10);
879 BEGIN
880 d_position := 0;
881
882 IF (PO_LOG.d_proc) THEN
883 PO_LOG.proc_begin (d_module);
884 END IF;
885
886 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
887 ( itemtype => itemtype,
888 itemkey => itemkey,
889 aname => 'DRAFT_ID'
890 );
891
892 d_position := 10;
893
894 BEGIN
895 SELECT 'Y'
896 INTO l_any_lines_get_rejected
897 FROM DUAL
898 WHERE EXISTS
899 (SELECT 1
900 FROM po_lines_draft_all
901 WHERE draft_id = l_draft_id
902 AND change_accepted_flag = 'N')
903 OR EXISTS
904 (SELECT 1
905 FROM po_line_locations_draft_all
906 WHERE draft_id = l_draft_id
907 AND change_accepted_flag = 'N')
908 OR EXISTS
909 (SELECT 1
910 FROM po_price_diff_draft
911 WHERE draft_id = l_draft_id
912 AND change_accepted_flag = 'N')
913 OR EXISTS
914 (SELECT 1
915 FROM po_attribute_values_draft
916 WHERE draft_id = l_draft_id
917 AND change_accepted_flag = 'N');
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920 l_any_lines_get_rejected := 'N';
921 END;
922
923 d_position := 20;
924
925 IF (PO_LOG.d_stmt) THEN
926 PO_LOG.stmt (d_module, d_position, 'l_any_lines_get_rejected', l_any_lines_get_rejected);
927 END IF;
928
929 IF (l_any_lines_get_rejected = 'Y') THEN
930 resultout := WF_ENGINE.eng_completed || ':Y';
931 ELSE
932 resultout := WF_ENGINE.eng_completed || ':N';
933 END IF;
934
935 IF (PO_LOG.d_proc) THEN
936 PO_LOG.proc_end (d_module);
937 END IF;
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
942 RAISE;
943 END any_lines_get_rejected;
944
945
946
947 PROCEDURE transfer_draft_to_txn
948 ( itemtype IN VARCHAR2,
949 itemkey IN VARCHAR2,
950 actid IN NUMBER,
951 funcmode IN VARCHAR2,
952 resultout OUT NOCOPY VARCHAR2
953 ) IS
954
955 d_api_name CONSTANT VARCHAR2(30) := 'transfer_draft_to_txn';
956 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
957 d_position NUMBER;
958
959 l_draft_id PO_DRAFTS.draft_id%TYPE;
960 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
961 l_acceptance_action VARCHAR2(20);
962 l_need_notif VARCHAR2(1);
963 l_return_status VARCHAR2(1);
964
965 BEGIN
966 d_position := 0;
967
968 IF (PO_LOG.d_proc) THEN
969 PO_LOG.proc_begin (d_module);
970 END IF;
971
972 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
973 ( itemtype => itemtype,
974 itemkey => itemkey,
975 aname => 'DRAFT_ID'
976 );
977
978 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber
979 ( itemtype => itemtype,
980 itemkey => itemkey,
981 aname => 'PO_HEADER_ID'
982 );
983
984 l_acceptance_action := PO_WF_UTIL_PKG.GetItemAttrText
985 ( itemtype => itemtype,
986 itemkey => itemkey,
987 aname => 'ACCEPTANCE_RESULT'
988 );
989
990 d_position := 10;
991
992 PO_DRAFTS_PVT.transfer_draft_to_txn
993 ( p_api_version => 1.0,
994 p_init_msg_list => FND_API.G_TRUE,
995 p_draft_id => l_draft_id,
996 p_po_header_id => l_po_header_id,
997 p_delete_processed_draft => FND_API.G_FALSE,
998 p_acceptance_action => l_acceptance_action,
999 x_return_status => l_return_status
1000 );
1001
1002 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1003
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006
1007 IF (PO_LOG.d_proc) THEN
1008 PO_LOG.proc_end (d_module);
1009 END IF;
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
1014 RAISE;
1015 END transfer_draft_to_txn;
1016
1017
1018 PROCEDURE launch_po_approval_wf
1019 ( itemtype IN VARCHAR2,
1020 itemkey IN VARCHAR2,
1021 actid IN NUMBER,
1022 funcmode IN VARCHAR2,
1023 resultout OUT NOCOPY VARCHAR2
1024 ) IS
1025
1026 d_api_name CONSTANT VARCHAR2(30) := 'launch_po_approval_wf';
1027 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1028 d_position NUMBER;
1029
1030 l_document_type VARCHAR2(10) := 'PA';
1031 l_document_subtype VARCHAR2(10) := 'BLANKET';
1032 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
1036 l_printflag VARCHAR2(1) := 'N';
1033 l_org_id PO_HEADERS_ALL.org_id%TYPE;
1034 l_preparer_id PO_HEADERS_ALL.agent_id%TYPE;
1035
1037 l_faxflag VARCHAR2(1) := 'N';
1038 l_faxnum VARCHAR2(30); --Bug 5765243
1039 l_emailflag VARCHAR2(1) := 'N';
1040 l_emailaddress PO_VENDOR_SITES.email_address%TYPE;
1041 l_default_method PO_VENDOR_SITES.supplier_notif_method%TYPE;
1042 l_document_num PO_HEADERS.segment1%TYPE;
1043 l_note_to_approver VARCHAR2(480);
1044
1045 /* Bug 10026155 Start */
1046 l_create_sourcing_rule VARCHAR2(10);
1047 l_update_sourcing_rule VARCHAR2(10);
1048 /* Bug 10026155 End */
1049
1050 BEGIN
1051 d_position := 0;
1052
1053 IF (PO_LOG.d_proc) THEN
1054 PO_LOG.proc_begin (d_module);
1055 END IF;
1056
1057
1058 -- Launch Approval WF
1059 -- Retrieve some settings for launching the PO Approval workflow.
1060 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1061 ( itemtype => itemtype,
1062 itemkey => itemkey,
1063 aname => 'PO_HEADER_ID'
1064 );
1065
1066 l_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1067 ( itemtype => itemtype,
1068 itemkey => itemkey,
1069 aname => 'ORG_ID'
1070 );
1071
1072 l_note_to_approver := PO_WF_UTIL_PKG.GetItemAttrText
1073 ( itemtype => itemtype,
1074 itemkey => itemkey,
1075 aname => 'NOTE_TO_APPROVER'
1076 );
1077
1078 d_position := 10;
1079
1080 PO_VENDOR_SITES_SV.get_transmission_defaults
1081 ( p_document_id => l_po_header_id,
1082 p_document_type => l_document_type,
1083 p_document_subtype => l_document_subtype,
1084 p_preparer_id => l_preparer_id, -- IN OUT parameter
1085 x_default_method => l_default_method,
1086 x_email_address => l_emailaddress,
1087 x_fax_number => l_faxnum,
1088 x_document_num => l_document_num
1089 );
1090
1091 d_position := 20;
1092
1093 IF (l_default_method = 'EMAIL') AND (l_emailaddress IS NOT NULL) THEN
1094 l_emailflag := 'Y';
1095 ELSIF (l_default_method = 'FAX') AND (l_faxnum IS NOT NULL) then
1096 l_emailaddress := NULL;
1097 l_faxflag := 'Y';
1098 ELSIF (l_default_method = 'PRINT') then
1099 l_emailaddress := null;
1100 l_faxnum := null;
1101 l_printflag := 'Y';
1102 ELSE
1103 l_emailaddress := null;
1104 l_faxnum := null;
1105 END IF; -- l_default_method
1106
1107 d_position := 30;
1108
1109 /* Bug 10026155 Start */
1110
1111 SELECT AUTO_SOURCING_FLAG,UPDATE_SOURCING_RULES_FLAG
1112 INTO
1113 l_create_sourcing_rule,l_update_sourcing_rule
1114 FROM
1115 PO_HEADERS_ALL
1116 WHERE
1117 PO_HEADER_ID=l_po_header_id;
1118
1119 /* Bug 10026155 End */
1120
1121 -- Launch the PO Approval workflow.
1122 PO_REQAPPROVAL_INIT1.start_wf_process (
1123 ItemType => NULL, -- defaulted in start_wf_process
1124 ItemKey => NULL, -- defaulted in start_wf_process
1125 WorkflowProcess => NULL, -- defaulted in start_wf_process
1126 ActionOriginatedFrom => NULL,
1127 DocumentID => l_po_header_id,
1128 DocumentNumber => NULL,
1129 PreparerID => l_preparer_id,
1130 DocumentTypeCode => l_document_type,
1131 DocumentSubtype => l_document_subtype,
1132 SubmitterAction => NULL,
1133 ForwardToID => NULL,
1134 ForwardFromID => NULL,
1135 DefaultApprovalPathID => NULL,
1136 Note => l_note_to_approver,
1137 PrintFlag => l_printflag,
1138 FaxFlag => l_faxflag,
1139 FaxNumber => l_faxnum,
1140 EmailFlag => l_emailflag,
1141 EmailAddress => l_emailaddress,
1142 CreateSourcingRule=>l_create_sourcing_rule,
1143 UpdateSourcingRule=>l_update_sourcing_rule
1144 );
1145
1146 d_position := 40;
1147
1148 IF (PO_LOG.d_proc) THEN
1149 PO_LOG.proc_end (d_module);
1150 END IF;
1151
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
1155 RAISE;
1156 END launch_po_approval_wf;
1157
1158
1159 --==========================================================================
1160 -- Regular procedures
1161 --==========================================================================
1162
1163 -----------------------------------------------------------------------
1164 --Start of Comments
1165 --Name: get_new_itemkey
1166 --Function:
1167 -- Get new itemkey
1168 --Parameters:
1169 --IN:
1170 --p_draft_id
1171 -- draft unique identifier
1172 --IN OUT:
1173 --OUT:
1174 --Returns:
1175 -- New item key
1176 --Notes:
1177 --Testing:
1178 --End of Comments
1179 ------------------------------------------------------------------------
1180 FUNCTION get_new_itemkey
1181 ( p_draft_id IN NUMBER
1182 ) RETURN VARCHAR2
1183 IS
1184
1185 l_new_itemkey NUMBER;
1186
1187 BEGIN
1188 SELECT PO_WF_ITEMKEY_S.NEXTVAL
1189 INTO l_new_itemkey
1190 FROM DUAL;
1191
1192 RETURN g_ITEMKEY_PREFIX || '#' || TO_CHAR(p_draft_id) || '#' ||
1193 TO_CHAR(l_new_itemkey);
1194
1195 END get_new_itemkey;
1196
1200 --Function:
1197 -----------------------------------------------------------------------
1198 --Start of Comments
1199 --Name: find_itemkey
1201 -- Find item key of the buyer acceptance workflow for the current draft
1202 --Parameters:
1203 --IN:
1204 --p_draft_id
1205 -- draft unique identifier
1206 --IN OUT:
1207 --OUT:
1208 --x_return_status
1209 -- status of the procedure
1210 --Returns:
1211 --Notes:
1212 --Testing:
1213 --End of Comments
1214 ------------------------------------------------------------------------
1215 FUNCTION find_itemkey
1216 ( p_draft_id IN NUMBER
1217 ) RETURN VARCHAR2
1218 IS
1219
1220 d_api_name CONSTANT VARCHAR2(30) := 'find_itemkey';
1221 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1222 d_position NUMBER;
1223
1224 l_itemkey_like WF_ITEMS.item_key%TYPE;
1225 l_itemkey WF_ITEMS.item_key%TYPE;
1226
1227 BEGIN
1228 d_position := 0;
1229
1230 IF (PO_LOG.d_proc) THEN
1231 PO_LOG.proc_begin (d_module);
1232 END IF;
1233
1234 l_itemkey_like := g_ITEMKEY_PREFIX || '#' || TO_CHAR(p_draft_id) || '#%';
1235
1236 SELECT MAX(item_key)
1237 INTO l_itemkey
1238 FROM WF_ITEMS
1239 WHERE item_type = g_ITEMTYPE
1240 AND item_key LIKE l_itemkey_like
1241 AND end_date IS NULL;
1242
1243 IF (PO_LOG.d_proc) THEN
1244 PO_LOG.proc_end (d_module, 'l_itemkey', l_itemkey);
1245 END IF;
1246
1247 RETURN l_itemkey;
1248
1249 EXCEPTION
1250 WHEN OTHERS THEN
1251 PO_MESSAGE_S.add_exc_msg
1252 ( p_pkg_name => d_pkg_name,
1253 p_procedure_name => d_api_name || '.' || d_position
1254 );
1255 RAISE;
1256 END find_itemkey;
1257
1258 -----------------------------------------------------------------------
1259 --Start of Comments
1260 --Name: record_disposition
1261 --Function:
1262 -- Mark all lines and their children with status Rejected based on the
1263 -- line rejection list passed in, if the rejected lines passed validation
1264 --Parameters:
1265 --IN:
1266 --p_draft_id
1267 -- draft unique identifier
1268 --p_reject_line_id_list
1269 -- lines to reject
1270 --IN OUT:
1271 --OUT:
1272 --x_invalid_line_id_list
1273 -- lines that fail validation
1274 --x_invalid_line_num_list
1275 -- line num for the lines that fail validation
1276 --x_return_status
1277 -- status of the procedure
1278 --Returns:
1279 --Notes:
1280 --Testing:
1281 --End of Comments
1282 ------------------------------------------------------------------------
1283 PROCEDURE record_disposition
1284 ( p_draft_id IN NUMBER,
1285 p_reject_line_id_list IN PO_TBL_NUMBER,
1286 x_invalid_line_id_list OUT NOCOPY PO_TBL_NUMBER, -- bug5187544
1287 x_invalid_line_num_list OUT NOCOPY PO_TBL_NUMBER, -- bug5187544
1288 x_return_status OUT NOCOPY VARCHAR2
1289 ) IS
1290
1291 d_api_name CONSTANT VARCHAR2(30) := 'record_disposition';
1292 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1293 d_position NUMBER;
1294
1295 l_return_status VARCHAR2(1);
1296 BEGIN
1297 d_position := 0;
1298
1299 IF (PO_LOG.d_proc) THEN
1300 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
1301 PO_LOG.proc_begin (d_module, 'p_reject_line_id_list.COUNT', p_reject_line_id_list.COUNT);
1302 END IF;
1303
1304 x_return_status := FND_API.g_RET_STS_SUCCESS;
1305
1306 d_position := 10;
1307
1308 -- bug5187544 START
1309 -- perform validation to the rejected lines
1310 validate_disposition
1311 ( p_draft_id => p_draft_id,
1312 p_reject_line_id_list => p_reject_line_id_list,
1313 x_invalid_line_id_list => x_invalid_line_id_list,
1314 x_invalid_line_num_list => x_invalid_line_num_list,
1315 x_return_status => l_return_status
1316 );
1317
1318 IF (PO_LOG.d_stmt) THEN
1319 PO_LOG.stmt (d_module, d_position, 'l_return_status', l_return_status);
1320 END IF;
1321
1322 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1323 x_return_status := l_return_status;
1324 RETURN;
1325
1326 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END IF;
1329
1330 -- bug5187544 END
1331
1332 d_position := 20;
1333
1334 -- bug5035979
1335 -- refactored the code to call cascade_change_aceptance
1336 cascade_change_acceptance
1337 ( p_draft_id => p_draft_id,
1338 p_line_id_list => p_reject_line_id_list,
1339 p_change_accepted_value => 'N',
1340 x_return_status => l_return_status
1341 );
1342
1343 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346
1347 IF (PO_LOG.d_proc) THEN
1348 PO_LOG.proc_end (d_module);
1349 END IF;
1350
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 PO_MESSAGE_S.add_exc_msg
1354 ( p_pkg_name => d_pkg_name,
1355 p_procedure_name => d_api_name || '.' || d_position
1356 );
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358 END record_disposition;
1359
1360 -----------------------------------------------------------------------
1361 --Start of Comments
1362 --Name: complete_resp_to_changes
1363 --Function:
1364 -- Post processing after buyer has responded to the changes. It relieves
1365 -- the block set up in buyer acceptance workflow so that draft changes can
1366 -- be applied to the transaction table
1370 -- draft unique identifier
1367 --Parameters:
1368 --IN:
1369 --p_draft_id
1371 --p_action
1372 -- Buyer's response to the draft changes
1373 --p_note_to_approver
1374 -- Note to the approver in approval hierarchy
1375 --IN OUT:
1376 --OUT:
1377 --x_return_status
1378 -- status of the procedure
1379 --Returns:
1380 --Notes:
1381 --Testing:
1382 --End of Comments
1383 ------------------------------------------------------------------------
1384 PROCEDURE complete_resp_to_changes
1385 ( p_draft_id IN NUMBER,
1386 p_action IN VARCHAR2,
1387 p_initiate_approval IN VARCHAR2,
1388 p_note_to_approver IN VARCHAR2,
1389 x_return_status OUT NOCOPY VARCHAR2
1390 ) IS
1391
1392 d_api_name CONSTANT VARCHAR2(30) := 'complete_resp_to_changes';
1393 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1394 d_position NUMBER;
1395
1396 l_itemKey WF_ITEMS.item_key%TYPE;
1397 l_activity VARCHAR2(200);
1398 l_activity_result VARCHAR2(30);
1399
1400 BEGIN
1401 d_position := 0;
1402
1403 x_return_status := FND_API.G_RET_STS_SUCCESS;
1404
1405 IF (PO_LOG.d_proc) THEN
1406 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
1407 END IF;
1408
1409 l_itemKey := find_ItemKey
1410 ( p_draft_id => p_draft_id
1411 );
1412
1413 d_position := 10;
1414
1415 IF (l_itemKey IS NOT NULL) THEN
1416
1417 -- release the activity that is blocked since the user has responded to
1418 -- the draft changes
1419
1420 l_activity := 'BUYER_NOTIFICATIONS:WAIT_FOR_RESP_TO_CHG';
1421
1422 IF (p_action = PO_DRAFTS_PVT.g_ACCEPT_ALL) THEN
1423 l_activity_result := 'ACCEPT';
1424 ELSIF (p_action = PO_DRAFTS_PVT.g_REJECT_ALL) THEN
1425 l_activity_result := 'REJECT';
1426 ELSIF (p_action = PO_DRAFTS_PVT.g_LINE_DISP) THEN
1427 l_activity_result := 'SUBMIT_LINE_DISP';
1428 END IF;
1429
1430 d_position := 20;
1431 --Bug 13356264
1432 PO_WF_UTIL_PKG.SetItemAttrText
1433 ( itemtype => g_ITEMTYPE,
1434 itemkey => l_ItemKey,
1435 aname => 'LAUNCH_APPROVAL',
1436 avalue => p_initiate_approval
1437 );
1438 PO_WF_UTIL_PKG.SetItemAttrText
1439 ( itemtype => g_ITEMTYPE,
1440 itemkey => l_ItemKey,
1441 aname => 'NOTE_TO_APPROVER',
1442 avalue => SUBSTRB(p_note_to_approver, 1, 480)
1443 );
1444
1445 WF_ENGINE.CompleteActivity
1446 ( itemType => g_ITEMTYPE,
1447 itemKey => l_itemKey,
1448 activity => l_activity,
1449 result => l_activity_result
1450 );
1451
1452 END IF;
1453
1454 IF (PO_LOG.d_proc) THEN
1455 PO_LOG.proc_end (d_module);
1456 END IF;
1457
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 PO_MESSAGE_S.add_exc_msg
1461 ( p_pkg_name => d_pkg_name,
1462 p_procedure_name => d_api_name || '.' || d_position
1463 );
1464
1465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466 END complete_resp_to_changes;
1467
1468
1469 -----------------------------------------------------------------------
1470 --Start of Comments
1471 --Name: accept_lines_within_tolerance
1472 --Function:
1473 -- Check the lines in draft table and see if the price change has exceed
1474 -- tolerance. If so, return an indicator saying that manual acceptance is
1475 -- required. For those that do not exceed price tolerance, makr them as
1476 -- autoaccepted
1477 --Parameters:
1478 --IN:
1479 --p_draft_id
1480 -- draft unique identifier
1481 --IN OUT:
1482 --OUT:
1483 --Returns:
1484 -- FND_API.G_TRUE if manual acceptance is required
1485 -- FND_API.G_FALSE otherwise
1486 --Notes:
1487 --Testing:
1488 --End of Comments
1489 ------------------------------------------------------------------------
1490 FUNCTION accept_lines_within_tolerance
1491 ( p_draft_id IN NUMBER
1492 ) RETURN VARCHAR2
1493 IS
1494
1495 d_api_name CONSTANT VARCHAR2(30) := 'accept_lines_within_tolerance';
1496 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1497 d_position NUMBER;
1498
1499 l_index NUMBER;
1500
1501 l_manual_accept_required VARCHAR2(1) := FND_API.G_FALSE;
1502 l_need_to_check_tolerance VARCHAR2(1) := FND_API.G_FALSE;
1503 l_has_autoaccepted_line VARCHAR2(1) := FND_API.G_FALSE;
1504 l_over_tolerance VARCHAR2(1);
1505
1506 l_check_tolerance_index_tbl DBMS_SQL.NUMBER_TABLE;
1507
1508 l_autoaccept_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1509 l_vendor_id_tbl PO_TBL_NUMBER;
1510 l_po_line_id_tbl PO_TBL_NUMBER;
1511 l_po_header_id_tbl PO_TBL_NUMBER;
1512 l_item_id_tbl PO_TBL_NUMBER;
1513 l_category_id_tbl PO_TBL_NUMBER;
1514 l_new_price_tbl PO_TBL_NUMBER;
1515 l_old_price_tbl PO_TBL_NUMBER;
1516 l_new_line_flag_tbl PO_TBL_VARCHAR1;
1517 l_price_update_tolerance_tbl PO_TBL_NUMBER;
1518
1519 l_return_status VARCHAR2(1);
1520
1521 BEGIN
1522 d_position := 0;
1523
1524 IF (PO_LOG.d_proc) THEN
1525 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
1526 END IF;
1527
1528 -- bug5035979
1529 -- mark the records that do not have line level changes as
1530 -- 'accepted'
1531 accept_if_no_line_changes
1532 ( p_draft_id => p_draft_id
1533 );
1534
1535 SELECT PH.vendor_id,
1536 PLD.po_line_id,
1537 PLD.po_header_id,
1538 PLD.item_id,
1542 NVL2(PL.po_line_id, 'N', 'Y')
1539 PLD.category_id,
1540 PLD.unit_price,
1541 PL.unit_price,
1543 BULK COLLECT
1544 INTO l_vendor_id_tbl,
1545 l_po_line_id_tbl,
1546 l_po_header_id_tbl,
1547 l_item_id_tbl,
1548 l_category_id_tbl,
1549 l_new_price_tbl,
1550 l_old_price_tbl,
1551 l_new_line_flag_tbl
1552 FROM po_headers_all PH,
1553 po_lines_draft_all PLD,
1554 po_lines_all PL
1555 WHERE PLD.draft_id = p_draft_id
1556 AND NVL(PLD.delete_flag, 'N') = 'N'
1557 AND PLD.change_accepted_flag IS NULL
1558 AND PLD.po_line_id = PL.po_line_id(+)
1559 AND PLD.po_header_id = PH.po_header_id;
1560
1561 d_position := 10;
1562
1563 -- If there are any new lines, manual acceptance will be required by default
1564 -- Otherwise, we need to check price tolerance to see if manual acceptance
1565 -- is necessary
1566 FOR i IN 1..l_po_line_id_tbl.COUNT LOOP
1567 IF (l_new_line_flag_tbl(i) = 'Y') THEN
1568 l_manual_accept_required := FND_API.G_TRUE;
1569 ELSE
1570 -- add to the table that indicates what rows we need to check
1571 -- for tolerance
1572 l_check_tolerance_index_tbl(i) := i;
1573 l_need_to_check_tolerance := FND_API.G_TRUE;
1574 END IF;
1575 END LOOP;
1576
1577 d_position := 20;
1578
1579 IF (l_need_to_check_tolerance = FND_API.G_TRUE) THEN
1580
1581 PO_PDOI_PRICE_TOLERANCE_PVT.get_price_tolerance
1582 ( p_index_tbl => l_check_tolerance_index_tbl,
1583 p_po_header_id_tbl => l_po_header_id_tbl,
1584 p_item_id_tbl => l_item_id_tbl,
1585 p_category_id_tbl => l_category_id_tbl,
1586 p_vendor_id_tbl => l_vendor_id_tbl,
1587 x_price_update_tolerance_tbl => l_price_update_tolerance_tbl
1588 );
1589
1590 d_position := 30;
1591
1592 -- now, check whether the lines have exceeded price tolerance
1593 l_index := l_check_tolerance_index_tbl.FIRST;
1594
1595 WHILE (l_index IS NOT NULL) LOOP
1596 d_position := 40;
1597
1598 l_over_tolerance :=
1599 PO_PDOI_PRICE_TOLERANCE_PVT.exceed_tolerance_check
1600 ( p_price_tolerance => l_price_update_tolerance_tbl(l_index),
1601 p_old_price => l_old_price_tbl(l_index),
1602 p_new_price => l_new_price_tbl(l_index)
1603 );
1604
1605 IF (l_over_tolerance = FND_API.G_FALSE) THEN
1606 -- need to autoaccept this line
1607 l_autoaccept_tbl.EXTEND;
1608 l_autoaccept_tbl(l_autoaccept_tbl.COUNT) := l_po_line_id_tbl(l_index);
1609
1610 l_has_autoaccepted_line := FND_API.G_TRUE;
1611 ELSE
1612 -- require manual acceptance, if there is a line that goes over
1613 -- price tolerance limit
1614 l_manual_accept_required := FND_API.G_TRUE;
1615 END IF;
1616
1617 l_index := l_check_tolerance_index_tbl.NEXT(l_index);
1618 END LOOP;
1619
1620 d_position := 50;
1621
1622 -- For the lines that do not exceed price tolerance, autoaccept them.
1623 IF (l_has_autoaccepted_line = FND_API.G_TRUE) THEN
1624 -- bug5035979
1625 -- cascade acceptance value to lower level as well.
1626 cascade_change_acceptance
1627 ( p_draft_id => p_draft_id,
1628 p_line_id_list => l_autoaccept_tbl,
1629 p_change_accepted_value => 'Y',
1630 x_return_status => l_return_status
1631 );
1632
1633 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1635 END IF;
1636 END IF;
1637
1638 END IF;
1639
1640 IF (PO_LOG.d_proc) THEN
1641 PO_LOG.proc_end (d_module);
1642 END IF;
1643
1644 RETURN l_manual_accept_required;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 PO_MESSAGE_S.add_exc_msg
1649 ( p_pkg_name => d_pkg_name,
1650 p_procedure_name => d_api_name || '.' || d_position
1651 );
1652
1653 RAISE;
1654 END accept_lines_within_tolerance;
1655
1656 --==========================================================================
1657 -- Private procedures
1658 --==========================================================================
1659
1660 -- bug5249414 START
1661 PROCEDURE accept_translations
1662 ( p_draft_id IN NUMBER
1663 ) IS
1664
1665 d_api_name CONSTANT VARCHAR2(30) := 'accept_translations';
1666 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1667 d_position NUMBER;
1668
1669 l_created_language PO_HEADERS_ALL.created_language%TYPE;
1670
1671 BEGIN
1672
1673 d_position := 0;
1674
1675 IF (PO_LOG.d_proc) THEN
1676 PO_LOG.proc_begin (d_module);
1677 END IF;
1678
1679 SELECT PH.created_language
1680 INTO l_created_language
1681 FROM po_drafts DFT,
1682 po_headers_all PH
1683 WHERE DFT.draft_id = p_draft_id
1684 AND PH.po_header_id = DFT.document_id;
1685
1686 UPDATE po_attribute_values_tlp_draft PAVTD
1687 SET change_accepted_flag = 'Y'
1688 WHERE change_accepted_flag IS NULL
1689 AND PAVTD.draft_id = p_draft_id
1690 AND PAVTD.language <> l_created_language;
1691
1692
1693 IF (PO_LOG.d_proc) THEN
1694 PO_LOG.proc_end (d_module, '# of records updated: ', SQL%ROWCOUNT);
1695 END IF;
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 PO_MESSAGE_S.add_exc_msg
1700 ( p_pkg_name => d_pkg_name,
1701 p_procedure_name => d_api_name || '.' || d_position
1705 END accept_translations;
1702 );
1703
1704 RAISE;
1706
1707 -- bug5570989 START
1708 -----------------------------------------------------------------------
1709 --Start of Comments
1710 --Name: autoaccept_deleted_records
1711 --Function:
1712 -- If the draft record is marked for deletion, but the corresponding
1713 -- line doesn't exist in the txn table, autoaccept it because they
1714 -- won't show up in the diff summary report anyway
1715 --Parameters:
1716 --IN:
1717 --p_draft_id
1718 -- draft unique identifier
1719 --IN OUT:
1720 --OUT:
1721 --Returns:
1722 --Notes:
1723 --Testing:
1724 --End of Comments
1725 ------------------------------------------------------------------------
1726 PROCEDURE autoaccept_deleted_records
1727 ( p_draft_id IN NUMBER
1728 ) IS
1729
1730 d_api_name CONSTANT VARCHAR2(30) := 'autoaccept_deleted_records';
1731 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1732 d_position NUMBER;
1733
1734 l_has_record_to_accept VARCHAR2(1) := FND_API.G_TRUE;
1735
1736 BEGIN
1737
1738 d_position := 0;
1739
1740 IF (PO_LOG.d_proc) THEN
1741 PO_LOG.proc_begin (d_module);
1742 END IF;
1743
1744 -- Line Level
1745 UPDATE po_lines_draft_all PLD
1746 SET PLD.change_accepted_flag = 'Y'
1747 WHERE PLD.draft_id = p_draft_id
1748 AND PLD.change_accepted_flag IS NULL
1749 AND PLD.delete_flag = 'Y'
1750 AND NOT EXISTS
1751 ( SELECT 1
1752 FROM po_lines_all PL
1753 WHERE PLD.po_line_id = PL.po_line_id);
1754
1755 d_position := 10;
1756
1757 -- Price Break Level
1758 UPDATE po_line_locations_draft_all PLLD
1759 SET PLLD.change_accepted_flag = 'Y'
1760 WHERE PLLD.draft_id = p_draft_id
1761 AND PLLD.change_accepted_flag IS NULL
1762 AND PLLD.delete_flag = 'Y'
1763 AND NOT EXISTS
1764 ( SELECT 1
1765 FROM po_line_locations_all PLL
1766 WHERE PLLD.line_location_id = PLL.line_location_id);
1767
1768 d_position := 20;
1769
1770 -- Price Diff Level
1771 UPDATE po_price_diff_draft PPDD
1772 SET PPDD.change_accepted_flag = 'Y'
1773 WHERE PPDD.draft_id = p_draft_id
1774 AND PPDD.change_accepted_flag IS NULL
1775 AND PPDD.delete_flag = 'Y'
1776 AND NOT EXISTS
1777 ( SELECT 1
1778 FROM po_price_differentials PPD
1779 WHERE PPDD.price_differential_id = PPD.price_differential_id);
1780
1781 d_position := 30;
1782
1783 -- Attr Values Level
1784 UPDATE po_attribute_values_draft PAVD
1785 SET PAVD.change_accepted_flag = 'Y'
1786 WHERE PAVD.draft_id = p_draft_id
1787 AND PAVD.change_accepted_flag IS NULL
1788 AND PAVD.delete_flag = 'Y'
1789 AND NOT EXISTS
1790 ( SELECT 1
1791 FROM po_attribute_values PAV
1792 WHERE PAVD.attribute_values_id = PAV.attribute_values_id);
1793
1794 d_position := 40;
1795
1796 -- Attr Values TLP Level
1797 UPDATE po_attribute_values_tlp_draft PAVTD
1798 SET PAVTD.change_accepted_flag = 'Y'
1799 WHERE PAVTD.draft_id = p_draft_id
1800 AND PAVTD.change_accepted_flag IS NULL
1801 AND PAVTD.delete_flag = 'Y'
1802 AND NOT EXISTS
1803 ( SELECT 1
1804 FROM po_attribute_values_tlp PAVT
1805 WHERE PAVTD.attribute_values_tlp_id = PAVT.attribute_values_tlp_id);
1806
1807 IF (PO_LOG.d_proc) THEN
1808 PO_LOG.proc_end (d_module);
1809 END IF;
1810
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 PO_MESSAGE_S.add_exc_msg
1814 ( p_pkg_name => d_pkg_name,
1815 p_procedure_name => d_api_name || '.' || d_position
1816 );
1817
1818 RAISE;
1819 END autoaccept_deleted_records;
1820
1821
1822
1823 -- bug5570989 END
1824
1825 -----------------------------------------------------------------------
1826 --Start of Comments
1827 --Name: autoaccept_unchanged_records
1828 --Function:
1829 -- For records that are not changed or have only the non-diffsummary-supported
1830 -- attributes changed, autoaccept them
1831 --Parameters:
1832 --IN:
1833 --p_draft_id
1834 -- draft unique identifier
1835 --IN OUT:
1836 --OUT:
1837 --Returns:
1838 -- FND_API.G_TRUE if there's still record in the draft to accept
1839 -- FND_API.G_FALSE otherwise
1840 --Notes:
1841 --Testing:
1842 --End of Comments
1843 ------------------------------------------------------------------------
1844 FUNCTION autoaccept_unchanged_records
1845 ( p_draft_id IN NUMBER
1846 ) RETURN VARCHAR2
1847 IS
1848
1849 d_api_name CONSTANT VARCHAR2(30) := 'autoaccept_unchanged_records';
1850 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1851 d_position NUMBER;
1852
1853 l_has_record_to_accept VARCHAR2(1) := FND_API.G_TRUE;
1854
1855 BEGIN
1856
1857 d_position := 0;
1858
1859 IF (PO_LOG.d_proc) THEN
1860 PO_LOG.proc_begin (d_module);
1861 END IF;
1862
1863 -- Line Level Attributes
1864 UPDATE po_lines_draft_all PLD
1865 SET PLD.change_accepted_flag = 'Y'
1866 WHERE PLD.draft_id = p_draft_id
1867 AND PLD.change_accepted_flag IS NULL
1868 AND NVL(PLD.delete_flag, 'N') <> 'Y'
1869 AND EXISTS
1870 ( SELECT 1
1874 PL.line_num, 'Y', 'N') = 'Y'
1871 FROM po_lines_all PL
1872 WHERE PLD.po_line_id = PL.po_line_id
1873 AND DECODE (PLD.line_num,
1875 AND DECODE (PLD.line_type_id,
1876 PL.line_type_id, 'Y', 'N') = 'Y'
1877 AND DECODE (PLD.item_id,
1878 PL.item_id, 'Y', 'N') = 'Y'
1879 AND DECODE (PLD.job_id,
1880 PL.job_id, 'Y', 'N') = 'Y'
1881 AND DECODE (PLD.item_description,
1882 PL.item_description, 'Y', 'N') = 'Y'
1883 AND DECODE (PLD.category_id,
1884 PL.category_id, 'Y', 'N') = 'Y'
1885 AND DECODE (PLD.unit_meas_lookup_code,
1886 PL.unit_meas_lookup_code, 'Y', 'N') = 'Y'
1887 AND DECODE (PLD.unit_price,
1888 PL.unit_price, 'Y', 'N') = 'Y'
1889 AND DECODE (PLD.amount,
1890 PL.amount, 'Y', 'N') = 'Y'
1891 AND DECODE (PLD.expiration_date,
1892 PL.expiration_date, 'Y', 'N') = 'Y'
1893 AND DECODE (PLD.item_revision,
1894 PL.item_revision, 'Y', 'N') = 'Y'
1895 AND DECODE (PLD.vendor_product_num,
1896 PL.vendor_product_num, 'Y', 'N') = 'Y'
1897 AND DECODE (PLD.supplier_ref_number,
1898 PL.supplier_ref_number, 'Y', 'N') = 'Y'
1899 AND DECODE (PLD.ip_category_id,
1900 PL.ip_category_id, 'Y', 'N') = 'Y'
1901 AND DECODE (NVL(PLD.capital_expense_flag, 'N'),
1902 NVL(PL.capital_expense_flag, 'N'), 'Y', 'N') = 'Y'
1903 AND DECODE (NVL(PLD.allow_price_override_flag, 'N'),
1904 NVL(PL.allow_price_override_flag, 'N'), 'Y', 'N') = 'Y'
1905 AND DECODE (PLD.not_to_exceed_price,
1906 PL.not_to_exceed_price, 'Y', 'N') = 'Y'
1907 AND DECODE (PLD.list_price_per_unit,
1908 PL.list_price_per_unit, 'Y', 'N') = 'Y'
1909 AND DECODE (PLD.market_price,
1910 PL.market_price, 'Y', 'N') = 'Y'
1911 AND DECODE (PLD.price_type_lookup_code,
1912 PL.price_type_lookup_code, 'Y', 'N') = 'Y'
1913 AND DECODE (NVL(PLD.negotiated_by_preparer_flag, 'N'),
1914 NVL(PL.negotiated_by_preparer_flag, 'N'), 'Y', 'N') = 'Y'
1915 AND DECODE (PLD.min_release_amount,
1916 PL.min_release_amount, 'Y', 'N') = 'Y'
1917 AND DECODE (PLD.committed_amount,
1918 PL.committed_amount, 'Y', 'N') = 'Y'
1919 AND DECODE (PLD.quantity_committed,
1920 PL.quantity_committed, 'Y', 'N') = 'Y'
1921 AND DECODE (PLD.supplier_part_auxid,
1922 PL.supplier_part_auxid, 'Y', 'N') = 'Y'
1923 AND DECODE (PLD.un_number_id,
1924 PL.un_number_id, 'Y', 'N') = 'Y'
1925 AND DECODE (PLD.hazard_class_id,
1926 PL.hazard_class_id, 'Y', 'N') = 'Y'
1927 AND DECODE (PLD.note_to_vendor,
1928 PL.note_to_vendor, 'Y', 'N') = 'Y');
1929
1930 IF (PO_LOG.d_stmt) THEN
1931 PO_LOG.stmt (d_module, d_position, '# of lines autoaccepted', SQL%ROWCOUNT);
1932 END IF;
1933
1934 d_position := 10;
1935
1936 -- Price Break attributes
1937 UPDATE po_line_locations_draft_all PLLD
1938 SET PLLD.change_accepted_flag = 'Y'
1939 WHERE PLLD.draft_id = p_draft_id
1940 AND PLLD.change_accepted_flag IS NULL
1941 AND NVL(PLLD.delete_flag, 'N') <> 'Y'
1942 AND EXISTS
1943 ( SELECT 1
1944 FROM po_line_locations_all PLL
1945 WHERE PLLD.line_location_id = PLL.line_location_id
1946 AND DECODE (PLLD.ship_to_organization_id,
1947 PLL.ship_to_organization_id, 'Y', 'N') = 'Y'
1948 AND DECODE (PLLD.ship_to_location_id,
1949 PLL.ship_to_location_id, 'Y', 'N') = 'Y'
1950 AND DECODE (PLLD.quantity,
1951 PLL.quantity, 'Y', 'N') = 'Y'
1952 AND DECODE (PLLD.price_override,
1953 PLL.price_override, 'Y', 'N') = 'Y'
1954 AND DECODE (PLLD.price_discount,
1955 PLL.price_discount, 'Y', 'N') = 'Y'
1956 AND DECODE (PLLD.start_date,
1957 PLL.start_date, 'Y', 'N') = 'Y'
1958 AND DECODE (PLLD.end_date,
1959 PLL.end_date, 'Y', 'N') = 'Y');
1960
1961 IF (PO_LOG.d_stmt) THEN
1962 PO_LOG.stmt (d_module, d_position, '# of price breaks autoaccepted', SQL%ROWCOUNT);
1963 END IF;
1964
1965 d_position := 20;
1966
1967 -- Pirce Differentials Attributes
1968 UPDATE po_price_diff_draft PPDD
1969 SET PPDD.change_accepted_flag = 'Y'
1970 WHERE PPDD.draft_id = p_draft_id
1971 AND PPDD.change_accepted_flag IS NULL
1972 AND NVL(PPDD.delete_flag, 'N') <> 'Y'
1973 AND EXISTS
1974 ( SELECT 1
1975 FROM po_price_differentials PPD
1976 WHERE PPDD.price_differential_id = PPD.price_differential_id
1977 AND DECODE (PPDD.price_differential_num,
1978 PPD.price_differential_num, 'Y', 'N') = 'Y'
1982 PPD.min_multiplier, 'Y', 'N') = 'Y'
1979 AND DECODE (PPDD.price_type,
1980 PPD.price_type, 'Y', 'N') = 'Y'
1981 AND DECODE (PPDD.min_multiplier,
1983 AND DECODE (PPDD.max_multiplier,
1984 PPD.max_multiplier, 'Y', 'N') = 'Y');
1985
1986 IF (PO_LOG.d_stmt) THEN
1987 PO_LOG.stmt (d_module, d_position, '# of price diff autoaccepted', SQL%ROWCOUNT);
1988 END IF;
1989
1990 d_position := 30;
1991
1992 -- Attribute Values
1993 UPDATE po_attribute_values_draft PAVD
1994 SET PAVD.change_accepted_flag = 'Y'
1995 WHERE PAVD.draft_id = p_draft_id
1996 AND PAVD.change_accepted_flag IS NULL
1997 AND NVL(PAVD.delete_flag, 'N') <> 'Y'
1998 AND EXISTS
1999 ( SELECT 1
2000 FROM po_attribute_values PAV
2001 WHERE PAVD.attribute_values_id = PAV.attribute_values_id
2002 AND DECODE (PAVD.manufacturer_part_num ,
2003 PAV.manufacturer_part_num , 'Y', 'N') = 'Y'
2004 AND DECODE (PAVD.picture,
2005 PAV.picture, 'Y', 'N') = 'Y'
2006 AND DECODE (PAVD.thumbnail_image,
2007 PAV.thumbnail_image, 'Y', 'N') = 'Y'
2008 AND DECODE (PAVD.supplier_url,
2009 PAV.supplier_url, 'Y', 'N') = 'Y'
2010 AND DECODE (PAVD.manufacturer_url,
2011 PAV.manufacturer_url, 'Y', 'N') = 'Y'
2012 AND DECODE (PAVD.attachment_url,
2013 PAV.attachment_url, 'Y', 'N') = 'Y'
2014 AND DECODE (PAVD.unspsc,
2015 PAV.unspsc, 'Y', 'N') = 'Y'
2016 AND DECODE (PAVD.availability,
2017 PAV.availability, 'Y', 'N') = 'Y'
2018 AND DECODE (PAVD.lead_time,
2019 PAV.lead_time, 'Y', 'N') = 'Y'
2020 AND DECODE (PAVD.text_base_attribute1,
2021 PAV.text_base_attribute1, 'Y', 'N') = 'Y'
2022 AND DECODE (PAVD.text_base_attribute2,
2023 PAV.text_base_attribute2, 'Y', 'N') = 'Y'
2024 AND DECODE (PAVD.text_base_attribute3,
2025 PAV.text_base_attribute3, 'Y', 'N') = 'Y'
2026 AND DECODE (PAVD.text_base_attribute4,
2027 PAV.text_base_attribute4, 'Y', 'N') = 'Y'
2028 AND DECODE (PAVD.text_base_attribute5,
2029 PAV.text_base_attribute5, 'Y', 'N') = 'Y'
2030 AND DECODE (PAVD.text_base_attribute6,
2031 PAV.text_base_attribute6, 'Y', 'N') = 'Y'
2032 AND DECODE (PAVD.text_base_attribute7,
2033 PAV.text_base_attribute7, 'Y', 'N') = 'Y'
2034 AND DECODE (PAVD.text_base_attribute8,
2035 PAV.text_base_attribute8, 'Y', 'N') = 'Y'
2036 AND DECODE (PAVD.text_base_attribute9,
2037 PAV.text_base_attribute9, 'Y', 'N') = 'Y'
2038 AND DECODE (PAVD.text_base_attribute10,
2039 PAV.text_base_attribute10, 'Y', 'N') = 'Y'
2040 AND DECODE (PAVD.text_base_attribute11,
2041 PAV.text_base_attribute11, 'Y', 'N') = 'Y'
2042 AND DECODE (PAVD.text_base_attribute12,
2043 PAV.text_base_attribute12, 'Y', 'N') = 'Y'
2044 AND DECODE (PAVD.text_base_attribute13,
2045 PAV.text_base_attribute13, 'Y', 'N') = 'Y'
2046 AND DECODE (PAVD.text_base_attribute14,
2047 PAV.text_base_attribute14, 'Y', 'N') = 'Y'
2048 AND DECODE (PAVD.text_base_attribute15,
2049 PAV.text_base_attribute15, 'Y', 'N') = 'Y'
2050 AND DECODE (PAVD.text_base_attribute16,
2051 PAV.text_base_attribute16, 'Y', 'N') = 'Y'
2052 AND DECODE (PAVD.text_base_attribute17,
2053 PAV.text_base_attribute17, 'Y', 'N') = 'Y'
2054 AND DECODE (PAVD.text_base_attribute18,
2055 PAV.text_base_attribute18, 'Y', 'N') = 'Y'
2056 AND DECODE (PAVD.text_base_attribute19,
2057 PAV.text_base_attribute19, 'Y', 'N') = 'Y'
2058 AND DECODE (PAVD.text_base_attribute20,
2059 PAV.text_base_attribute20, 'Y', 'N') = 'Y'
2060 AND DECODE (PAVD.text_base_attribute21,
2061 PAV.text_base_attribute21, 'Y', 'N') = 'Y'
2062 AND DECODE (PAVD.text_base_attribute22,
2063 PAV.text_base_attribute22, 'Y', 'N') = 'Y'
2064 AND DECODE (PAVD.text_base_attribute23,
2065 PAV.text_base_attribute23, 'Y', 'N') = 'Y'
2066 AND DECODE (PAVD.text_base_attribute24,
2067 PAV.text_base_attribute24, 'Y', 'N') = 'Y'
2068 AND DECODE (PAVD.text_base_attribute25,
2069 PAV.text_base_attribute25, 'Y', 'N') = 'Y'
2070 AND DECODE (PAVD.text_base_attribute26,
2071 PAV.text_base_attribute26, 'Y', 'N') = 'Y'
2072 AND DECODE (PAVD.text_base_attribute27,
2073 PAV.text_base_attribute27, 'Y', 'N') = 'Y'
2074 AND DECODE (PAVD.text_base_attribute28,
2075 PAV.text_base_attribute28, 'Y', 'N') = 'Y'
2076 AND DECODE (PAVD.text_base_attribute29,
2077 PAV.text_base_attribute29, 'Y', 'N') = 'Y'
2078 AND DECODE (PAVD.text_base_attribute30,
2079 PAV.text_base_attribute30, 'Y', 'N') = 'Y'
2083 PAV.text_base_attribute32, 'Y', 'N') = 'Y'
2080 AND DECODE (PAVD.text_base_attribute31,
2081 PAV.text_base_attribute31, 'Y', 'N') = 'Y'
2082 AND DECODE (PAVD.text_base_attribute32,
2084 AND DECODE (PAVD.text_base_attribute33,
2085 PAV.text_base_attribute33, 'Y', 'N') = 'Y'
2086 AND DECODE (PAVD.text_base_attribute34,
2087 PAV.text_base_attribute34, 'Y', 'N') = 'Y'
2088 AND DECODE (PAVD.text_base_attribute35,
2089 PAV.text_base_attribute35, 'Y', 'N') = 'Y'
2090 AND DECODE (PAVD.text_base_attribute36,
2091 PAV.text_base_attribute36, 'Y', 'N') = 'Y'
2092 AND DECODE (PAVD.text_base_attribute37,
2093 PAV.text_base_attribute37, 'Y', 'N') = 'Y'
2094 AND DECODE (PAVD.text_base_attribute38,
2095 PAV.text_base_attribute38, 'Y', 'N') = 'Y'
2096 AND DECODE (PAVD.text_base_attribute39,
2097 PAV.text_base_attribute39, 'Y', 'N') = 'Y'
2098 AND DECODE (PAVD.text_base_attribute40,
2099 PAV.text_base_attribute40, 'Y', 'N') = 'Y'
2100 AND DECODE (PAVD.text_base_attribute41,
2101 PAV.text_base_attribute41, 'Y', 'N') = 'Y'
2102 AND DECODE (PAVD.text_base_attribute42,
2103 PAV.text_base_attribute42, 'Y', 'N') = 'Y'
2104 AND DECODE (PAVD.text_base_attribute43,
2105 PAV.text_base_attribute43, 'Y', 'N') = 'Y'
2106 AND DECODE (PAVD.text_base_attribute44,
2107 PAV.text_base_attribute44, 'Y', 'N') = 'Y'
2108 AND DECODE (PAVD.text_base_attribute45,
2109 PAV.text_base_attribute45, 'Y', 'N') = 'Y'
2110 AND DECODE (PAVD.text_base_attribute46,
2111 PAV.text_base_attribute46, 'Y', 'N') = 'Y'
2112 AND DECODE (PAVD.text_base_attribute47,
2113 PAV.text_base_attribute47, 'Y', 'N') = 'Y'
2114 AND DECODE (PAVD.text_base_attribute48,
2115 PAV.text_base_attribute48, 'Y', 'N') = 'Y'
2116 AND DECODE (PAVD.text_base_attribute49,
2117 PAV.text_base_attribute49, 'Y', 'N') = 'Y'
2118 AND DECODE (PAVD.text_base_attribute50,
2119 PAV.text_base_attribute50, 'Y', 'N') = 'Y'
2120 AND DECODE (PAVD.text_base_attribute51,
2121 PAV.text_base_attribute51, 'Y', 'N') = 'Y'
2122 AND DECODE (PAVD.text_base_attribute52,
2123 PAV.text_base_attribute52, 'Y', 'N') = 'Y'
2124 AND DECODE (PAVD.text_base_attribute53,
2125 PAV.text_base_attribute53, 'Y', 'N') = 'Y'
2126 AND DECODE (PAVD.text_base_attribute54,
2127 PAV.text_base_attribute54, 'Y', 'N') = 'Y'
2128 AND DECODE (PAVD.text_base_attribute55,
2129 PAV.text_base_attribute55, 'Y', 'N') = 'Y'
2130 AND DECODE (PAVD.text_base_attribute56,
2131 PAV.text_base_attribute56, 'Y', 'N') = 'Y'
2132 AND DECODE (PAVD.text_base_attribute57,
2133 PAV.text_base_attribute57, 'Y', 'N') = 'Y'
2134 AND DECODE (PAVD.text_base_attribute58,
2135 PAV.text_base_attribute58, 'Y', 'N') = 'Y'
2136 AND DECODE (PAVD.text_base_attribute59,
2137 PAV.text_base_attribute59, 'Y', 'N') = 'Y'
2138 AND DECODE (PAVD.text_base_attribute60,
2139 PAV.text_base_attribute60, 'Y', 'N') = 'Y'
2140 AND DECODE (PAVD.text_base_attribute61,
2141 PAV.text_base_attribute61, 'Y', 'N') = 'Y'
2142 AND DECODE (PAVD.text_base_attribute62,
2143 PAV.text_base_attribute62, 'Y', 'N') = 'Y'
2144 AND DECODE (PAVD.text_base_attribute63,
2145 PAV.text_base_attribute63, 'Y', 'N') = 'Y'
2146 AND DECODE (PAVD.text_base_attribute64,
2147 PAV.text_base_attribute64, 'Y', 'N') = 'Y'
2148 AND DECODE (PAVD.text_base_attribute65,
2149 PAV.text_base_attribute65, 'Y', 'N') = 'Y'
2150 AND DECODE (PAVD.text_base_attribute66,
2151 PAV.text_base_attribute66, 'Y', 'N') = 'Y'
2152 AND DECODE (PAVD.text_base_attribute67,
2153 PAV.text_base_attribute67, 'Y', 'N') = 'Y'
2154 AND DECODE (PAVD.text_base_attribute68,
2155 PAV.text_base_attribute68, 'Y', 'N') = 'Y'
2156 AND DECODE (PAVD.text_base_attribute69,
2157 PAV.text_base_attribute69, 'Y', 'N') = 'Y'
2158 AND DECODE (PAVD.text_base_attribute70,
2159 PAV.text_base_attribute70, 'Y', 'N') = 'Y'
2160 AND DECODE (PAVD.text_base_attribute71,
2161 PAV.text_base_attribute71, 'Y', 'N') = 'Y'
2162 AND DECODE (PAVD.text_base_attribute72,
2163 PAV.text_base_attribute72, 'Y', 'N') = 'Y'
2164 AND DECODE (PAVD.text_base_attribute73,
2165 PAV.text_base_attribute73, 'Y', 'N') = 'Y'
2166 AND DECODE (PAVD.text_base_attribute74,
2167 PAV.text_base_attribute74, 'Y', 'N') = 'Y'
2168 AND DECODE (PAVD.text_base_attribute75,
2169 PAV.text_base_attribute75, 'Y', 'N') = 'Y'
2170 AND DECODE (PAVD.text_base_attribute76,
2174 AND DECODE (PAVD.text_base_attribute78,
2171 PAV.text_base_attribute76, 'Y', 'N') = 'Y'
2172 AND DECODE (PAVD.text_base_attribute77,
2173 PAV.text_base_attribute77, 'Y', 'N') = 'Y'
2175 PAV.text_base_attribute78, 'Y', 'N') = 'Y'
2176 AND DECODE (PAVD.text_base_attribute79,
2177 PAV.text_base_attribute79, 'Y', 'N') = 'Y'
2178 AND DECODE (PAVD.text_base_attribute80,
2179 PAV.text_base_attribute80, 'Y', 'N') = 'Y'
2180 AND DECODE (PAVD.text_base_attribute81,
2181 PAV.text_base_attribute81, 'Y', 'N') = 'Y'
2182 AND DECODE (PAVD.text_base_attribute82,
2183 PAV.text_base_attribute82, 'Y', 'N') = 'Y'
2184 AND DECODE (PAVD.text_base_attribute83,
2185 PAV.text_base_attribute83, 'Y', 'N') = 'Y'
2186 AND DECODE (PAVD.text_base_attribute84,
2187 PAV.text_base_attribute84, 'Y', 'N') = 'Y'
2188 AND DECODE (PAVD.text_base_attribute85,
2189 PAV.text_base_attribute85, 'Y', 'N') = 'Y'
2190 AND DECODE (PAVD.text_base_attribute86,
2191 PAV.text_base_attribute86, 'Y', 'N') = 'Y'
2192 AND DECODE (PAVD.text_base_attribute87,
2193 PAV.text_base_attribute87, 'Y', 'N') = 'Y'
2194 AND DECODE (PAVD.text_base_attribute88,
2195 PAV.text_base_attribute88, 'Y', 'N') = 'Y'
2196 AND DECODE (PAVD.text_base_attribute89,
2197 PAV.text_base_attribute89, 'Y', 'N') = 'Y'
2198 AND DECODE (PAVD.text_base_attribute90,
2199 PAV.text_base_attribute90, 'Y', 'N') = 'Y'
2200 AND DECODE (PAVD.text_base_attribute91,
2201 PAV.text_base_attribute91, 'Y', 'N') = 'Y'
2202 AND DECODE (PAVD.text_base_attribute92,
2203 PAV.text_base_attribute92, 'Y', 'N') = 'Y'
2204 AND DECODE (PAVD.text_base_attribute93,
2205 PAV.text_base_attribute93, 'Y', 'N') = 'Y'
2206 AND DECODE (PAVD.text_base_attribute94,
2207 PAV.text_base_attribute94, 'Y', 'N') = 'Y'
2208 AND DECODE (PAVD.text_base_attribute95,
2209 PAV.text_base_attribute95, 'Y', 'N') = 'Y'
2210 AND DECODE (PAVD.text_base_attribute96,
2211 PAV.text_base_attribute96, 'Y', 'N') = 'Y'
2212 AND DECODE (PAVD.text_base_attribute97,
2213 PAV.text_base_attribute97, 'Y', 'N') = 'Y'
2214 AND DECODE (PAVD.text_base_attribute98,
2215 PAV.text_base_attribute98, 'Y', 'N') = 'Y'
2216 AND DECODE (PAVD.text_base_attribute99,
2217 PAV.text_base_attribute99, 'Y', 'N') = 'Y'
2218 AND DECODE (PAVD.text_base_attribute100,
2219 PAV.text_base_attribute100, 'Y', 'N') = 'Y'
2220 AND DECODE (PAVD.num_base_attribute1,
2221 PAV.num_base_attribute1, 'Y', 'N') = 'Y'
2222 AND DECODE (PAVD.num_base_attribute2,
2223 PAV.num_base_attribute2, 'Y', 'N') = 'Y'
2224 AND DECODE (PAVD.num_base_attribute3,
2225 PAV.num_base_attribute3, 'Y', 'N') = 'Y'
2226 AND DECODE (PAVD.num_base_attribute4,
2227 PAV.num_base_attribute4, 'Y', 'N') = 'Y'
2228 AND DECODE (PAVD.num_base_attribute5,
2229 PAV.num_base_attribute5, 'Y', 'N') = 'Y'
2230 AND DECODE (PAVD.num_base_attribute6,
2231 PAV.num_base_attribute6, 'Y', 'N') = 'Y'
2232 AND DECODE (PAVD.num_base_attribute7,
2233 PAV.num_base_attribute7, 'Y', 'N') = 'Y'
2234 AND DECODE (PAVD.num_base_attribute8,
2235 PAV.num_base_attribute8, 'Y', 'N') = 'Y'
2236 AND DECODE (PAVD.num_base_attribute9,
2237 PAV.num_base_attribute9, 'Y', 'N') = 'Y'
2238 AND DECODE (PAVD.num_base_attribute10,
2239 PAV.num_base_attribute10, 'Y', 'N') = 'Y'
2240 AND DECODE (PAVD.num_base_attribute11,
2241 PAV.num_base_attribute11, 'Y', 'N') = 'Y'
2242 AND DECODE (PAVD.num_base_attribute12,
2243 PAV.num_base_attribute12, 'Y', 'N') = 'Y'
2244 AND DECODE (PAVD.num_base_attribute13,
2245 PAV.num_base_attribute13, 'Y', 'N') = 'Y'
2246 AND DECODE (PAVD.num_base_attribute14,
2247 PAV.num_base_attribute14, 'Y', 'N') = 'Y'
2248 AND DECODE (PAVD.num_base_attribute15,
2249 PAV.num_base_attribute15, 'Y', 'N') = 'Y'
2250 AND DECODE (PAVD.num_base_attribute16,
2251 PAV.num_base_attribute16, 'Y', 'N') = 'Y'
2252 AND DECODE (PAVD.num_base_attribute17,
2253 PAV.num_base_attribute17, 'Y', 'N') = 'Y'
2254 AND DECODE (PAVD.num_base_attribute18,
2255 PAV.num_base_attribute18, 'Y', 'N') = 'Y'
2256 AND DECODE (PAVD.num_base_attribute19,
2257 PAV.num_base_attribute19, 'Y', 'N') = 'Y'
2258 AND DECODE (PAVD.num_base_attribute20,
2259 PAV.num_base_attribute20, 'Y', 'N') = 'Y'
2260 AND DECODE (PAVD.num_base_attribute21,
2261 PAV.num_base_attribute21, 'Y', 'N') = 'Y'
2265 PAV.num_base_attribute23, 'Y', 'N') = 'Y'
2262 AND DECODE (PAVD.num_base_attribute22,
2263 PAV.num_base_attribute22, 'Y', 'N') = 'Y'
2264 AND DECODE (PAVD.num_base_attribute23,
2266 AND DECODE (PAVD.num_base_attribute24,
2267 PAV.num_base_attribute24, 'Y', 'N') = 'Y'
2268 AND DECODE (PAVD.num_base_attribute25,
2269 PAV.num_base_attribute25, 'Y', 'N') = 'Y'
2270 AND DECODE (PAVD.num_base_attribute26,
2271 PAV.num_base_attribute26, 'Y', 'N') = 'Y'
2272 AND DECODE (PAVD.num_base_attribute27,
2273 PAV.num_base_attribute27, 'Y', 'N') = 'Y'
2274 AND DECODE (PAVD.num_base_attribute28,
2275 PAV.num_base_attribute28, 'Y', 'N') = 'Y'
2276 AND DECODE (PAVD.num_base_attribute29,
2277 PAV.num_base_attribute29, 'Y', 'N') = 'Y'
2278 AND DECODE (PAVD.num_base_attribute30,
2279 PAV.num_base_attribute30, 'Y', 'N') = 'Y'
2280 AND DECODE (PAVD.num_base_attribute31,
2281 PAV.num_base_attribute31, 'Y', 'N') = 'Y'
2282 AND DECODE (PAVD.num_base_attribute32,
2283 PAV.num_base_attribute32, 'Y', 'N') = 'Y'
2284 AND DECODE (PAVD.num_base_attribute33,
2285 PAV.num_base_attribute33, 'Y', 'N') = 'Y'
2286 AND DECODE (PAVD.num_base_attribute34,
2287 PAV.num_base_attribute34, 'Y', 'N') = 'Y'
2288 AND DECODE (PAVD.num_base_attribute35,
2289 PAV.num_base_attribute35, 'Y', 'N') = 'Y'
2290 AND DECODE (PAVD.num_base_attribute36,
2291 PAV.num_base_attribute36, 'Y', 'N') = 'Y'
2292 AND DECODE (PAVD.num_base_attribute37,
2293 PAV.num_base_attribute37, 'Y', 'N') = 'Y'
2294 AND DECODE (PAVD.num_base_attribute38,
2295 PAV.num_base_attribute38, 'Y', 'N') = 'Y'
2296 AND DECODE (PAVD.num_base_attribute39,
2297 PAV.num_base_attribute39, 'Y', 'N') = 'Y'
2298 AND DECODE (PAVD.num_base_attribute40,
2299 PAV.num_base_attribute40, 'Y', 'N') = 'Y'
2300 AND DECODE (PAVD.num_base_attribute41,
2301 PAV.num_base_attribute41, 'Y', 'N') = 'Y'
2302 AND DECODE (PAVD.num_base_attribute42,
2303 PAV.num_base_attribute42, 'Y', 'N') = 'Y'
2304 AND DECODE (PAVD.num_base_attribute43,
2305 PAV.num_base_attribute43, 'Y', 'N') = 'Y'
2306 AND DECODE (PAVD.num_base_attribute44,
2307 PAV.num_base_attribute44, 'Y', 'N') = 'Y'
2308 AND DECODE (PAVD.num_base_attribute45,
2309 PAV.num_base_attribute45, 'Y', 'N') = 'Y'
2310 AND DECODE (PAVD.num_base_attribute46,
2311 PAV.num_base_attribute46, 'Y', 'N') = 'Y'
2312 AND DECODE (PAVD.num_base_attribute47,
2313 PAV.num_base_attribute47, 'Y', 'N') = 'Y'
2314 AND DECODE (PAVD.num_base_attribute48,
2315 PAV.num_base_attribute48, 'Y', 'N') = 'Y'
2316 AND DECODE (PAVD.num_base_attribute49,
2317 PAV.num_base_attribute49, 'Y', 'N') = 'Y'
2318 AND DECODE (PAVD.num_base_attribute50,
2319 PAV.num_base_attribute50, 'Y', 'N') = 'Y'
2320 AND DECODE (PAVD.num_base_attribute51,
2321 PAV.num_base_attribute51, 'Y', 'N') = 'Y'
2322 AND DECODE (PAVD.num_base_attribute52,
2323 PAV.num_base_attribute52, 'Y', 'N') = 'Y'
2324 AND DECODE (PAVD.num_base_attribute53,
2325 PAV.num_base_attribute53, 'Y', 'N') = 'Y'
2326 AND DECODE (PAVD.num_base_attribute54,
2327 PAV.num_base_attribute54, 'Y', 'N') = 'Y'
2328 AND DECODE (PAVD.num_base_attribute55,
2329 PAV.num_base_attribute55, 'Y', 'N') = 'Y'
2330 AND DECODE (PAVD.num_base_attribute56,
2331 PAV.num_base_attribute56, 'Y', 'N') = 'Y'
2332 AND DECODE (PAVD.num_base_attribute57,
2333 PAV.num_base_attribute57, 'Y', 'N') = 'Y'
2334 AND DECODE (PAVD.num_base_attribute58,
2335 PAV.num_base_attribute58, 'Y', 'N') = 'Y'
2336 AND DECODE (PAVD.num_base_attribute59,
2337 PAV.num_base_attribute59, 'Y', 'N') = 'Y'
2338 AND DECODE (PAVD.num_base_attribute60,
2339 PAV.num_base_attribute60, 'Y', 'N') = 'Y'
2340 AND DECODE (PAVD.num_base_attribute61,
2341 PAV.num_base_attribute61, 'Y', 'N') = 'Y'
2342 AND DECODE (PAVD.num_base_attribute62,
2343 PAV.num_base_attribute62, 'Y', 'N') = 'Y'
2344 AND DECODE (PAVD.num_base_attribute63,
2345 PAV.num_base_attribute63, 'Y', 'N') = 'Y'
2346 AND DECODE (PAVD.num_base_attribute64,
2347 PAV.num_base_attribute64, 'Y', 'N') = 'Y'
2348 AND DECODE (PAVD.num_base_attribute65,
2349 PAV.num_base_attribute65, 'Y', 'N') = 'Y'
2350 AND DECODE (PAVD.num_base_attribute66,
2351 PAV.num_base_attribute66, 'Y', 'N') = 'Y'
2352 AND DECODE (PAVD.num_base_attribute67,
2353 PAV.num_base_attribute67, 'Y', 'N') = 'Y'
2357 PAV.num_base_attribute69, 'Y', 'N') = 'Y'
2354 AND DECODE (PAVD.num_base_attribute68,
2355 PAV.num_base_attribute68, 'Y', 'N') = 'Y'
2356 AND DECODE (PAVD.num_base_attribute69,
2358 AND DECODE (PAVD.num_base_attribute70,
2359 PAV.num_base_attribute70, 'Y', 'N') = 'Y'
2360 AND DECODE (PAVD.num_base_attribute71,
2361 PAV.num_base_attribute71, 'Y', 'N') = 'Y'
2362 AND DECODE (PAVD.num_base_attribute72,
2363 PAV.num_base_attribute72, 'Y', 'N') = 'Y'
2364 AND DECODE (PAVD.num_base_attribute73,
2365 PAV.num_base_attribute73, 'Y', 'N') = 'Y'
2366 AND DECODE (PAVD.num_base_attribute74,
2367 PAV.num_base_attribute74, 'Y', 'N') = 'Y'
2368 AND DECODE (PAVD.num_base_attribute75,
2369 PAV.num_base_attribute75, 'Y', 'N') = 'Y'
2370 AND DECODE (PAVD.num_base_attribute76,
2371 PAV.num_base_attribute76, 'Y', 'N') = 'Y'
2372 AND DECODE (PAVD.num_base_attribute77,
2373 PAV.num_base_attribute77, 'Y', 'N') = 'Y'
2374 AND DECODE (PAVD.num_base_attribute78,
2375 PAV.num_base_attribute78, 'Y', 'N') = 'Y'
2376 AND DECODE (PAVD.num_base_attribute79,
2377 PAV.num_base_attribute79, 'Y', 'N') = 'Y'
2378 AND DECODE (PAVD.num_base_attribute80,
2379 PAV.num_base_attribute80, 'Y', 'N') = 'Y'
2380 AND DECODE (PAVD.num_base_attribute81,
2381 PAV.num_base_attribute81, 'Y', 'N') = 'Y'
2382 AND DECODE (PAVD.num_base_attribute82,
2383 PAV.num_base_attribute82, 'Y', 'N') = 'Y'
2384 AND DECODE (PAVD.num_base_attribute83,
2385 PAV.num_base_attribute83, 'Y', 'N') = 'Y'
2386 AND DECODE (PAVD.num_base_attribute84,
2387 PAV.num_base_attribute84, 'Y', 'N') = 'Y'
2388 AND DECODE (PAVD.num_base_attribute85,
2389 PAV.num_base_attribute85, 'Y', 'N') = 'Y'
2390 AND DECODE (PAVD.num_base_attribute86,
2391 PAV.num_base_attribute86, 'Y', 'N') = 'Y'
2392 AND DECODE (PAVD.num_base_attribute87,
2393 PAV.num_base_attribute87, 'Y', 'N') = 'Y'
2394 AND DECODE (PAVD.num_base_attribute88,
2395 PAV.num_base_attribute88, 'Y', 'N') = 'Y'
2396 AND DECODE (PAVD.num_base_attribute89,
2397 PAV.num_base_attribute89, 'Y', 'N') = 'Y'
2398 AND DECODE (PAVD.num_base_attribute90,
2399 PAV.num_base_attribute90, 'Y', 'N') = 'Y'
2400 AND DECODE (PAVD.num_base_attribute91,
2401 PAV.num_base_attribute91, 'Y', 'N') = 'Y'
2402 AND DECODE (PAVD.num_base_attribute92,
2403 PAV.num_base_attribute92, 'Y', 'N') = 'Y'
2404 AND DECODE (PAVD.num_base_attribute93,
2405 PAV.num_base_attribute93, 'Y', 'N') = 'Y'
2406 AND DECODE (PAVD.num_base_attribute94,
2407 PAV.num_base_attribute94, 'Y', 'N') = 'Y'
2408 AND DECODE (PAVD.num_base_attribute95,
2409 PAV.num_base_attribute95, 'Y', 'N') = 'Y'
2410 AND DECODE (PAVD.num_base_attribute96,
2411 PAV.num_base_attribute96, 'Y', 'N') = 'Y'
2412 AND DECODE (PAVD.num_base_attribute97,
2413 PAV.num_base_attribute97, 'Y', 'N') = 'Y'
2414 AND DECODE (PAVD.num_base_attribute98,
2415 PAV.num_base_attribute98, 'Y', 'N') = 'Y'
2416 AND DECODE (PAVD.num_base_attribute99,
2417 PAV.num_base_attribute99, 'Y', 'N') = 'Y'
2418 AND DECODE (PAVD.num_base_attribute100,
2419 PAV.num_base_attribute100, 'Y', 'N') = 'Y'
2420 AND DECODE (PAVD.text_cat_attribute1,
2421 PAV.text_cat_attribute1, 'Y', 'N') = 'Y'
2422 AND DECODE (PAVD.text_cat_attribute2,
2423 PAV.text_cat_attribute2, 'Y', 'N') = 'Y'
2424 AND DECODE (PAVD.text_cat_attribute3,
2425 PAV.text_cat_attribute3, 'Y', 'N') = 'Y'
2426 AND DECODE (PAVD.text_cat_attribute4,
2427 PAV.text_cat_attribute4, 'Y', 'N') = 'Y'
2428 AND DECODE (PAVD.text_cat_attribute5,
2429 PAV.text_cat_attribute5, 'Y', 'N') = 'Y'
2430 AND DECODE (PAVD.text_cat_attribute6,
2431 PAV.text_cat_attribute6, 'Y', 'N') = 'Y'
2432 AND DECODE (PAVD.text_cat_attribute7,
2433 PAV.text_cat_attribute7, 'Y', 'N') = 'Y'
2434 AND DECODE (PAVD.text_cat_attribute8,
2435 PAV.text_cat_attribute8, 'Y', 'N') = 'Y'
2436 AND DECODE (PAVD.text_cat_attribute9,
2437 PAV.text_cat_attribute9, 'Y', 'N') = 'Y'
2438 AND DECODE (PAVD.text_cat_attribute10,
2439 PAV.text_cat_attribute10, 'Y', 'N') = 'Y'
2440 AND DECODE (PAVD.text_cat_attribute11,
2441 PAV.text_cat_attribute11, 'Y', 'N') = 'Y'
2442 AND DECODE (PAVD.text_cat_attribute12,
2443 PAV.text_cat_attribute12, 'Y', 'N') = 'Y'
2444 AND DECODE (PAVD.text_cat_attribute13,
2445 PAV.text_cat_attribute13, 'Y', 'N') = 'Y'
2449 PAV.text_cat_attribute15, 'Y', 'N') = 'Y'
2446 AND DECODE (PAVD.text_cat_attribute14,
2447 PAV.text_cat_attribute14, 'Y', 'N') = 'Y'
2448 AND DECODE (PAVD.text_cat_attribute15,
2450 AND DECODE (PAVD.text_cat_attribute16,
2451 PAV.text_cat_attribute16, 'Y', 'N') = 'Y'
2452 AND DECODE (PAVD.text_cat_attribute17,
2453 PAV.text_cat_attribute17, 'Y', 'N') = 'Y'
2454 AND DECODE (PAVD.text_cat_attribute18,
2455 PAV.text_cat_attribute18, 'Y', 'N') = 'Y'
2456 AND DECODE (PAVD.text_cat_attribute19,
2457 PAV.text_cat_attribute19, 'Y', 'N') = 'Y'
2458 AND DECODE (PAVD.text_cat_attribute20,
2459 PAV.text_cat_attribute20, 'Y', 'N') = 'Y'
2460 AND DECODE (PAVD.text_cat_attribute21,
2461 PAV.text_cat_attribute21, 'Y', 'N') = 'Y'
2462 AND DECODE (PAVD.text_cat_attribute22,
2463 PAV.text_cat_attribute22, 'Y', 'N') = 'Y'
2464 AND DECODE (PAVD.text_cat_attribute23,
2465 PAV.text_cat_attribute23, 'Y', 'N') = 'Y'
2466 AND DECODE (PAVD.text_cat_attribute24,
2467 PAV.text_cat_attribute24, 'Y', 'N') = 'Y'
2468 AND DECODE (PAVD.text_cat_attribute25,
2469 PAV.text_cat_attribute25, 'Y', 'N') = 'Y'
2470 AND DECODE (PAVD.text_cat_attribute26,
2471 PAV.text_cat_attribute26, 'Y', 'N') = 'Y'
2472 AND DECODE (PAVD.text_cat_attribute27,
2473 PAV.text_cat_attribute27, 'Y', 'N') = 'Y'
2474 AND DECODE (PAVD.text_cat_attribute28,
2475 PAV.text_cat_attribute28, 'Y', 'N') = 'Y'
2476 AND DECODE (PAVD.text_cat_attribute29,
2477 PAV.text_cat_attribute29, 'Y', 'N') = 'Y'
2478 AND DECODE (PAVD.text_cat_attribute30,
2479 PAV.text_cat_attribute30, 'Y', 'N') = 'Y'
2480 AND DECODE (PAVD.text_cat_attribute31,
2481 PAV.text_cat_attribute31, 'Y', 'N') = 'Y'
2482 AND DECODE (PAVD.text_cat_attribute32,
2483 PAV.text_cat_attribute32, 'Y', 'N') = 'Y'
2484 AND DECODE (PAVD.text_cat_attribute33,
2485 PAV.text_cat_attribute33, 'Y', 'N') = 'Y'
2486 AND DECODE (PAVD.text_cat_attribute34,
2487 PAV.text_cat_attribute34, 'Y', 'N') = 'Y'
2488 AND DECODE (PAVD.text_cat_attribute35,
2489 PAV.text_cat_attribute35, 'Y', 'N') = 'Y'
2490 AND DECODE (PAVD.text_cat_attribute36,
2491 PAV.text_cat_attribute36, 'Y', 'N') = 'Y'
2492 AND DECODE (PAVD.text_cat_attribute37,
2493 PAV.text_cat_attribute37, 'Y', 'N') = 'Y'
2494 AND DECODE (PAVD.text_cat_attribute38,
2495 PAV.text_cat_attribute38, 'Y', 'N') = 'Y'
2496 AND DECODE (PAVD.text_cat_attribute39,
2497 PAV.text_cat_attribute39, 'Y', 'N') = 'Y'
2498 AND DECODE (PAVD.text_cat_attribute40,
2499 PAV.text_cat_attribute40, 'Y', 'N') = 'Y'
2500 AND DECODE (PAVD.text_cat_attribute41,
2501 PAV.text_cat_attribute41, 'Y', 'N') = 'Y'
2502 AND DECODE (PAVD.text_cat_attribute42,
2503 PAV.text_cat_attribute42, 'Y', 'N') = 'Y'
2504 AND DECODE (PAVD.text_cat_attribute43,
2505 PAV.text_cat_attribute43, 'Y', 'N') = 'Y'
2506 AND DECODE (PAVD.text_cat_attribute44,
2507 PAV.text_cat_attribute44, 'Y', 'N') = 'Y'
2508 AND DECODE (PAVD.text_cat_attribute45,
2509 PAV.text_cat_attribute45, 'Y', 'N') = 'Y'
2510 AND DECODE (PAVD.text_cat_attribute46,
2511 PAV.text_cat_attribute46, 'Y', 'N') = 'Y'
2512 AND DECODE (PAVD.text_cat_attribute47,
2513 PAV.text_cat_attribute47, 'Y', 'N') = 'Y'
2514 AND DECODE (PAVD.text_cat_attribute48,
2515 PAV.text_cat_attribute48, 'Y', 'N') = 'Y'
2516 AND DECODE (PAVD.text_cat_attribute49,
2517 PAV.text_cat_attribute49, 'Y', 'N') = 'Y'
2518 AND DECODE (PAVD.text_cat_attribute50,
2519 PAV.text_cat_attribute50, 'Y', 'N') = 'Y'
2520 AND DECODE (PAVD.num_cat_attribute1,
2521 PAV.num_cat_attribute1, 'Y', 'N') = 'Y'
2522 AND DECODE (PAVD.num_cat_attribute2,
2523 PAV.num_cat_attribute2, 'Y', 'N') = 'Y'
2524 AND DECODE (PAVD.num_cat_attribute3,
2525 PAV.num_cat_attribute3, 'Y', 'N') = 'Y'
2526 AND DECODE (PAVD.num_cat_attribute4,
2527 PAV.num_cat_attribute4, 'Y', 'N') = 'Y'
2528 AND DECODE (PAVD.num_cat_attribute5,
2529 PAV.num_cat_attribute5, 'Y', 'N') = 'Y'
2530 AND DECODE (PAVD.num_cat_attribute6,
2531 PAV.num_cat_attribute6, 'Y', 'N') = 'Y'
2532 AND DECODE (PAVD.num_cat_attribute7,
2533 PAV.num_cat_attribute7, 'Y', 'N') = 'Y'
2534 AND DECODE (PAVD.num_cat_attribute8,
2535 PAV.num_cat_attribute8, 'Y', 'N') = 'Y'
2536 AND DECODE (PAVD.num_cat_attribute9,
2537 PAV.num_cat_attribute9, 'Y', 'N') = 'Y'
2538 AND DECODE (PAVD.num_cat_attribute10,
2542 AND DECODE (PAVD.num_cat_attribute12,
2539 PAV.num_cat_attribute10, 'Y', 'N') = 'Y'
2540 AND DECODE (PAVD.num_cat_attribute11,
2541 PAV.num_cat_attribute11, 'Y', 'N') = 'Y'
2543 PAV.num_cat_attribute12, 'Y', 'N') = 'Y'
2544 AND DECODE (PAVD.num_cat_attribute13,
2545 PAV.num_cat_attribute13, 'Y', 'N') = 'Y'
2546 AND DECODE (PAVD.num_cat_attribute14,
2547 PAV.num_cat_attribute14, 'Y', 'N') = 'Y'
2548 AND DECODE (PAVD.num_cat_attribute15,
2549 PAV.num_cat_attribute15, 'Y', 'N') = 'Y'
2550 AND DECODE (PAVD.num_cat_attribute16,
2551 PAV.num_cat_attribute16, 'Y', 'N') = 'Y'
2552 AND DECODE (PAVD.num_cat_attribute17,
2553 PAV.num_cat_attribute17, 'Y', 'N') = 'Y'
2554 AND DECODE (PAVD.num_cat_attribute18,
2555 PAV.num_cat_attribute18, 'Y', 'N') = 'Y'
2556 AND DECODE (PAVD.num_cat_attribute19,
2557 PAV.num_cat_attribute19, 'Y', 'N') = 'Y'
2558 AND DECODE (PAVD.num_cat_attribute20,
2559 PAV.num_cat_attribute20, 'Y', 'N') = 'Y'
2560 AND DECODE (PAVD.num_cat_attribute21,
2561 PAV.num_cat_attribute21, 'Y', 'N') = 'Y'
2562 AND DECODE (PAVD.num_cat_attribute22,
2563 PAV.num_cat_attribute22, 'Y', 'N') = 'Y'
2564 AND DECODE (PAVD.num_cat_attribute23,
2565 PAV.num_cat_attribute23, 'Y', 'N') = 'Y'
2566 AND DECODE (PAVD.num_cat_attribute24,
2567 PAV.num_cat_attribute24, 'Y', 'N') = 'Y'
2568 AND DECODE (PAVD.num_cat_attribute25,
2569 PAV.num_cat_attribute25, 'Y', 'N') = 'Y'
2570 AND DECODE (PAVD.num_cat_attribute26,
2571 PAV.num_cat_attribute26, 'Y', 'N') = 'Y'
2572 AND DECODE (PAVD.num_cat_attribute27,
2573 PAV.num_cat_attribute27, 'Y', 'N') = 'Y'
2574 AND DECODE (PAVD.num_cat_attribute28,
2575 PAV.num_cat_attribute28, 'Y', 'N') = 'Y'
2576 AND DECODE (PAVD.num_cat_attribute29,
2577 PAV.num_cat_attribute29, 'Y', 'N') = 'Y'
2578 AND DECODE (PAVD.num_cat_attribute30,
2579 PAV.num_cat_attribute30, 'Y', 'N') = 'Y'
2580 AND DECODE (PAVD.num_cat_attribute31,
2581 PAV.num_cat_attribute31, 'Y', 'N') = 'Y'
2582 AND DECODE (PAVD.num_cat_attribute32,
2583 PAV.num_cat_attribute32, 'Y', 'N') = 'Y'
2584 AND DECODE (PAVD.num_cat_attribute33,
2585 PAV.num_cat_attribute33, 'Y', 'N') = 'Y'
2586 AND DECODE (PAVD.num_cat_attribute34,
2587 PAV.num_cat_attribute34, 'Y', 'N') = 'Y'
2588 AND DECODE (PAVD.num_cat_attribute35,
2589 PAV.num_cat_attribute35, 'Y', 'N') = 'Y'
2590 AND DECODE (PAVD.num_cat_attribute36,
2591 PAV.num_cat_attribute36, 'Y', 'N') = 'Y'
2592 AND DECODE (PAVD.num_cat_attribute37,
2593 PAV.num_cat_attribute37, 'Y', 'N') = 'Y'
2594 AND DECODE (PAVD.num_cat_attribute38,
2595 PAV.num_cat_attribute38, 'Y', 'N') = 'Y'
2596 AND DECODE (PAVD.num_cat_attribute39,
2597 PAV.num_cat_attribute39, 'Y', 'N') = 'Y'
2598 AND DECODE (PAVD.num_cat_attribute40,
2599 PAV.num_cat_attribute40, 'Y', 'N') = 'Y'
2600 AND DECODE (PAVD.num_cat_attribute41,
2601 PAV.num_cat_attribute41, 'Y', 'N') = 'Y'
2602 AND DECODE (PAVD.num_cat_attribute42,
2603 PAV.num_cat_attribute42, 'Y', 'N') = 'Y'
2604 AND DECODE (PAVD.num_cat_attribute43,
2605 PAV.num_cat_attribute43, 'Y', 'N') = 'Y'
2606 AND DECODE (PAVD.num_cat_attribute44,
2607 PAV.num_cat_attribute44, 'Y', 'N') = 'Y'
2608 AND DECODE (PAVD.num_cat_attribute45,
2609 PAV.num_cat_attribute45, 'Y', 'N') = 'Y'
2610 AND DECODE (PAVD.num_cat_attribute46,
2611 PAV.num_cat_attribute46, 'Y', 'N') = 'Y'
2612 AND DECODE (PAVD.num_cat_attribute47,
2613 PAV.num_cat_attribute47, 'Y', 'N') = 'Y'
2614 AND DECODE (PAVD.num_cat_attribute48,
2615 PAV.num_cat_attribute48, 'Y', 'N') = 'Y'
2616 AND DECODE (PAVD.num_cat_attribute49,
2617 PAV.num_cat_attribute49, 'Y', 'N') = 'Y'
2618 AND DECODE (PAVD.num_cat_attribute50,
2619 PAV.num_cat_attribute50, 'Y', 'N') = 'Y');
2620
2621
2622 IF (PO_LOG.d_stmt) THEN
2623 PO_LOG.stmt (d_module, d_position, '# of attr values autoaccepted', SQL%ROWCOUNT);
2624 END IF;
2625
2626 d_position := 40;
2627
2628 -- Attribute values tlp
2629 UPDATE po_attribute_values_tlp_draft PAVTD
2630 SET PAVTD.change_accepted_flag = 'Y'
2631 WHERE PAVTD.draft_id = p_draft_id
2632 AND PAVTD.change_accepted_flag IS NULL
2633 AND NVL(PAVTD.delete_flag, 'N') <> 'Y'
2634 AND EXISTS
2635 ( SELECT 1
2636 FROM po_attribute_values_tlp PAVT
2637 WHERE PAVTD.attribute_values_tlp_id = PAVT.attribute_values_tlp_id
2638 AND DECODE (PAVTD.description,
2639 PAVT.description, 'Y', 'N') = 'Y'
2643 PAVT.comments, 'Y', 'N') = 'Y'
2640 AND DECODE (PAVTD.manufacturer,
2641 PAVT.manufacturer, 'Y', 'N') = 'Y'
2642 AND DECODE (PAVTD.comments,
2644 AND DECODE (PAVTD.alias,
2645 PAVT.alias, 'Y', 'N') = 'Y'
2646 AND DECODE (PAVTD.long_description,
2647 PAVT.long_description, 'Y', 'N') = 'Y'
2648 AND DECODE (PAVTD.tl_text_base_attribute1,
2649 PAVT.tl_text_base_attribute1, 'Y', 'N') = 'Y'
2650 AND DECODE (PAVTD.tl_text_base_attribute2,
2651 PAVT.tl_text_base_attribute2, 'Y', 'N') = 'Y'
2652 AND DECODE (PAVTD.tl_text_base_attribute3,
2653 PAVT.tl_text_base_attribute3, 'Y', 'N') = 'Y'
2654 AND DECODE (PAVTD.tl_text_base_attribute4,
2655 PAVT.tl_text_base_attribute4, 'Y', 'N') = 'Y'
2656 AND DECODE (PAVTD.tl_text_base_attribute5,
2657 PAVT.tl_text_base_attribute5, 'Y', 'N') = 'Y'
2658 AND DECODE (PAVTD.tl_text_base_attribute6,
2659 PAVT.tl_text_base_attribute6, 'Y', 'N') = 'Y'
2660 AND DECODE (PAVTD.tl_text_base_attribute7,
2661 PAVT.tl_text_base_attribute7, 'Y', 'N') = 'Y'
2662 AND DECODE (PAVTD.tl_text_base_attribute8,
2663 PAVT.tl_text_base_attribute8, 'Y', 'N') = 'Y'
2664 AND DECODE (PAVTD.tl_text_base_attribute9,
2665 PAVT.tl_text_base_attribute9, 'Y', 'N') = 'Y'
2666 AND DECODE (PAVTD.tl_text_base_attribute10,
2667 PAVT.tl_text_base_attribute10, 'Y', 'N') = 'Y'
2668 AND DECODE (PAVTD.tl_text_base_attribute11,
2669 PAVT.tl_text_base_attribute11, 'Y', 'N') = 'Y'
2670 AND DECODE (PAVTD.tl_text_base_attribute12,
2671 PAVT.tl_text_base_attribute12, 'Y', 'N') = 'Y'
2672 AND DECODE (PAVTD.tl_text_base_attribute13,
2673 PAVT.tl_text_base_attribute13, 'Y', 'N') = 'Y'
2674 AND DECODE (PAVTD.tl_text_base_attribute14,
2675 PAVT.tl_text_base_attribute14, 'Y', 'N') = 'Y'
2676 AND DECODE (PAVTD.tl_text_base_attribute15,
2677 PAVT.tl_text_base_attribute15, 'Y', 'N') = 'Y'
2678 AND DECODE (PAVTD.tl_text_base_attribute16,
2679 PAVT.tl_text_base_attribute16, 'Y', 'N') = 'Y'
2680 AND DECODE (PAVTD.tl_text_base_attribute17,
2681 PAVT.tl_text_base_attribute17, 'Y', 'N') = 'Y'
2682 AND DECODE (PAVTD.tl_text_base_attribute18,
2683 PAVT.tl_text_base_attribute18, 'Y', 'N') = 'Y'
2684 AND DECODE (PAVTD.tl_text_base_attribute19,
2685 PAVT.tl_text_base_attribute19, 'Y', 'N') = 'Y'
2686 AND DECODE (PAVTD.tl_text_base_attribute20,
2687 PAVT.tl_text_base_attribute20, 'Y', 'N') = 'Y'
2688 AND DECODE (PAVTD.tl_text_base_attribute21,
2689 PAVT.tl_text_base_attribute21, 'Y', 'N') = 'Y'
2690 AND DECODE (PAVTD.tl_text_base_attribute22,
2691 PAVT.tl_text_base_attribute22, 'Y', 'N') = 'Y'
2692 AND DECODE (PAVTD.tl_text_base_attribute23,
2693 PAVT.tl_text_base_attribute23, 'Y', 'N') = 'Y'
2694 AND DECODE (PAVTD.tl_text_base_attribute24,
2695 PAVT.tl_text_base_attribute24, 'Y', 'N') = 'Y'
2696 AND DECODE (PAVTD.tl_text_base_attribute25,
2697 PAVT.tl_text_base_attribute25, 'Y', 'N') = 'Y'
2698 AND DECODE (PAVTD.tl_text_base_attribute26,
2699 PAVT.tl_text_base_attribute26, 'Y', 'N') = 'Y'
2700 AND DECODE (PAVTD.tl_text_base_attribute27,
2701 PAVT.tl_text_base_attribute27, 'Y', 'N') = 'Y'
2702 AND DECODE (PAVTD.tl_text_base_attribute28,
2703 PAVT.tl_text_base_attribute28, 'Y', 'N') = 'Y'
2704 AND DECODE (PAVTD.tl_text_base_attribute29,
2705 PAVT.tl_text_base_attribute29, 'Y', 'N') = 'Y'
2706 AND DECODE (PAVTD.tl_text_base_attribute30,
2707 PAVT.tl_text_base_attribute30, 'Y', 'N') = 'Y'
2708 AND DECODE (PAVTD.tl_text_base_attribute31,
2709 PAVT.tl_text_base_attribute31, 'Y', 'N') = 'Y'
2710 AND DECODE (PAVTD.tl_text_base_attribute32,
2711 PAVT.tl_text_base_attribute32, 'Y', 'N') = 'Y'
2712 AND DECODE (PAVTD.tl_text_base_attribute33,
2713 PAVT.tl_text_base_attribute33, 'Y', 'N') = 'Y'
2714 AND DECODE (PAVTD.tl_text_base_attribute34,
2715 PAVT.tl_text_base_attribute34, 'Y', 'N') = 'Y'
2716 AND DECODE (PAVTD.tl_text_base_attribute35,
2717 PAVT.tl_text_base_attribute35, 'Y', 'N') = 'Y'
2718 AND DECODE (PAVTD.tl_text_base_attribute36,
2719 PAVT.tl_text_base_attribute36, 'Y', 'N') = 'Y'
2720 AND DECODE (PAVTD.tl_text_base_attribute37,
2721 PAVT.tl_text_base_attribute37, 'Y', 'N') = 'Y'
2722 AND DECODE (PAVTD.tl_text_base_attribute38,
2723 PAVT.tl_text_base_attribute38, 'Y', 'N') = 'Y'
2724 AND DECODE (PAVTD.tl_text_base_attribute39,
2725 PAVT.tl_text_base_attribute39, 'Y', 'N') = 'Y'
2726 AND DECODE (PAVTD.tl_text_base_attribute40,
2727 PAVT.tl_text_base_attribute40, 'Y', 'N') = 'Y'
2731 PAVT.tl_text_base_attribute42, 'Y', 'N') = 'Y'
2728 AND DECODE (PAVTD.tl_text_base_attribute41,
2729 PAVT.tl_text_base_attribute41, 'Y', 'N') = 'Y'
2730 AND DECODE (PAVTD.tl_text_base_attribute42,
2732 AND DECODE (PAVTD.tl_text_base_attribute43,
2733 PAVT.tl_text_base_attribute43, 'Y', 'N') = 'Y'
2734 AND DECODE (PAVTD.tl_text_base_attribute44,
2735 PAVT.tl_text_base_attribute44, 'Y', 'N') = 'Y'
2736 AND DECODE (PAVTD.tl_text_base_attribute45,
2737 PAVT.tl_text_base_attribute45, 'Y', 'N') = 'Y'
2738 AND DECODE (PAVTD.tl_text_base_attribute46,
2739 PAVT.tl_text_base_attribute46, 'Y', 'N') = 'Y'
2740 AND DECODE (PAVTD.tl_text_base_attribute47,
2741 PAVT.tl_text_base_attribute47, 'Y', 'N') = 'Y'
2742 AND DECODE (PAVTD.tl_text_base_attribute48,
2743 PAVT.tl_text_base_attribute48, 'Y', 'N') = 'Y'
2744 AND DECODE (PAVTD.tl_text_base_attribute49,
2745 PAVT.tl_text_base_attribute49, 'Y', 'N') = 'Y'
2746 AND DECODE (PAVTD.tl_text_base_attribute50,
2747 PAVT.tl_text_base_attribute50, 'Y', 'N') = 'Y'
2748 AND DECODE (PAVTD.tl_text_base_attribute51,
2749 PAVT.tl_text_base_attribute51, 'Y', 'N') = 'Y'
2750 AND DECODE (PAVTD.tl_text_base_attribute52,
2751 PAVT.tl_text_base_attribute52, 'Y', 'N') = 'Y'
2752 AND DECODE (PAVTD.tl_text_base_attribute53,
2753 PAVT.tl_text_base_attribute53, 'Y', 'N') = 'Y'
2754 AND DECODE (PAVTD.tl_text_base_attribute54,
2755 PAVT.tl_text_base_attribute54, 'Y', 'N') = 'Y'
2756 AND DECODE (PAVTD.tl_text_base_attribute55,
2757 PAVT.tl_text_base_attribute55, 'Y', 'N') = 'Y'
2758 AND DECODE (PAVTD.tl_text_base_attribute56,
2759 PAVT.tl_text_base_attribute56, 'Y', 'N') = 'Y'
2760 AND DECODE (PAVTD.tl_text_base_attribute57,
2761 PAVT.tl_text_base_attribute57, 'Y', 'N') = 'Y'
2762 AND DECODE (PAVTD.tl_text_base_attribute58,
2763 PAVT.tl_text_base_attribute58, 'Y', 'N') = 'Y'
2764 AND DECODE (PAVTD.tl_text_base_attribute59,
2765 PAVT.tl_text_base_attribute59, 'Y', 'N') = 'Y'
2766 AND DECODE (PAVTD.tl_text_base_attribute60,
2767 PAVT.tl_text_base_attribute60, 'Y', 'N') = 'Y'
2768 AND DECODE (PAVTD.tl_text_base_attribute61,
2769 PAVT.tl_text_base_attribute61, 'Y', 'N') = 'Y'
2770 AND DECODE (PAVTD.tl_text_base_attribute62,
2771 PAVT.tl_text_base_attribute62, 'Y', 'N') = 'Y'
2772 AND DECODE (PAVTD.tl_text_base_attribute63,
2773 PAVT.tl_text_base_attribute63, 'Y', 'N') = 'Y'
2774 AND DECODE (PAVTD.tl_text_base_attribute64,
2775 PAVT.tl_text_base_attribute64, 'Y', 'N') = 'Y'
2776 AND DECODE (PAVTD.tl_text_base_attribute65,
2777 PAVT.tl_text_base_attribute65, 'Y', 'N') = 'Y'
2778 AND DECODE (PAVTD.tl_text_base_attribute66,
2779 PAVT.tl_text_base_attribute66, 'Y', 'N') = 'Y'
2780 AND DECODE (PAVTD.tl_text_base_attribute67,
2781 PAVT.tl_text_base_attribute67, 'Y', 'N') = 'Y'
2782 AND DECODE (PAVTD.tl_text_base_attribute68,
2783 PAVT.tl_text_base_attribute68, 'Y', 'N') = 'Y'
2784 AND DECODE (PAVTD.tl_text_base_attribute69,
2785 PAVT.tl_text_base_attribute69, 'Y', 'N') = 'Y'
2786 AND DECODE (PAVTD.tl_text_base_attribute70,
2787 PAVT.tl_text_base_attribute70, 'Y', 'N') = 'Y'
2788 AND DECODE (PAVTD.tl_text_base_attribute71,
2789 PAVT.tl_text_base_attribute71, 'Y', 'N') = 'Y'
2790 AND DECODE (PAVTD.tl_text_base_attribute72,
2791 PAVT.tl_text_base_attribute72, 'Y', 'N') = 'Y'
2792 AND DECODE (PAVTD.tl_text_base_attribute73,
2793 PAVT.tl_text_base_attribute73, 'Y', 'N') = 'Y'
2794 AND DECODE (PAVTD.tl_text_base_attribute74,
2795 PAVT.tl_text_base_attribute74, 'Y', 'N') = 'Y'
2796 AND DECODE (PAVTD.tl_text_base_attribute75,
2797 PAVT.tl_text_base_attribute75, 'Y', 'N') = 'Y'
2798 AND DECODE (PAVTD.tl_text_base_attribute76,
2799 PAVT.tl_text_base_attribute76, 'Y', 'N') = 'Y'
2800 AND DECODE (PAVTD.tl_text_base_attribute77,
2801 PAVT.tl_text_base_attribute77, 'Y', 'N') = 'Y'
2802 AND DECODE (PAVTD.tl_text_base_attribute78,
2803 PAVT.tl_text_base_attribute78, 'Y', 'N') = 'Y'
2804 AND DECODE (PAVTD.tl_text_base_attribute79,
2805 PAVT.tl_text_base_attribute79, 'Y', 'N') = 'Y'
2806 AND DECODE (PAVTD.tl_text_base_attribute80,
2807 PAVT.tl_text_base_attribute80, 'Y', 'N') = 'Y'
2808 AND DECODE (PAVTD.tl_text_base_attribute81,
2809 PAVT.tl_text_base_attribute81, 'Y', 'N') = 'Y'
2810 AND DECODE (PAVTD.tl_text_base_attribute82,
2811 PAVT.tl_text_base_attribute82, 'Y', 'N') = 'Y'
2812 AND DECODE (PAVTD.tl_text_base_attribute83,
2816 AND DECODE (PAVTD.tl_text_base_attribute85,
2813 PAVT.tl_text_base_attribute83, 'Y', 'N') = 'Y'
2814 AND DECODE (PAVTD.tl_text_base_attribute84,
2815 PAVT.tl_text_base_attribute84, 'Y', 'N') = 'Y'
2817 PAVT.tl_text_base_attribute85, 'Y', 'N') = 'Y'
2818 AND DECODE (PAVTD.tl_text_base_attribute86,
2819 PAVT.tl_text_base_attribute86, 'Y', 'N') = 'Y'
2820 AND DECODE (PAVTD.tl_text_base_attribute87,
2821 PAVT.tl_text_base_attribute87, 'Y', 'N') = 'Y'
2822 AND DECODE (PAVTD.tl_text_base_attribute88,
2823 PAVT.tl_text_base_attribute88, 'Y', 'N') = 'Y'
2824 AND DECODE (PAVTD.tl_text_base_attribute89,
2825 PAVT.tl_text_base_attribute89, 'Y', 'N') = 'Y'
2826 AND DECODE (PAVTD.tl_text_base_attribute90,
2827 PAVT.tl_text_base_attribute90, 'Y', 'N') = 'Y'
2828 AND DECODE (PAVTD.tl_text_base_attribute91,
2829 PAVT.tl_text_base_attribute91, 'Y', 'N') = 'Y'
2830 AND DECODE (PAVTD.tl_text_base_attribute92,
2831 PAVT.tl_text_base_attribute92, 'Y', 'N') = 'Y'
2832 AND DECODE (PAVTD.tl_text_base_attribute93,
2833 PAVT.tl_text_base_attribute93, 'Y', 'N') = 'Y'
2834 AND DECODE (PAVTD.tl_text_base_attribute94,
2835 PAVT.tl_text_base_attribute94, 'Y', 'N') = 'Y'
2836 AND DECODE (PAVTD.tl_text_base_attribute95,
2837 PAVT.tl_text_base_attribute95, 'Y', 'N') = 'Y'
2838 AND DECODE (PAVTD.tl_text_base_attribute96,
2839 PAVT.tl_text_base_attribute96, 'Y', 'N') = 'Y'
2840 AND DECODE (PAVTD.tl_text_base_attribute97,
2841 PAVT.tl_text_base_attribute97, 'Y', 'N') = 'Y'
2842 AND DECODE (PAVTD.tl_text_base_attribute98,
2843 PAVT.tl_text_base_attribute98, 'Y', 'N') = 'Y'
2844 AND DECODE (PAVTD.tl_text_base_attribute99,
2845 PAVT.tl_text_base_attribute99, 'Y', 'N') = 'Y'
2846 AND DECODE (PAVTD.tl_text_base_attribute100,
2847 PAVT.tl_text_base_attribute100, 'Y', 'N') = 'Y'
2848 AND DECODE (PAVTD.tl_text_cat_attribute1,
2849 PAVT.tl_text_cat_attribute1, 'Y', 'N') = 'Y'
2850 AND DECODE (PAVTD.tl_text_cat_attribute2,
2851 PAVT.tl_text_cat_attribute2, 'Y', 'N') = 'Y'
2852 AND DECODE (PAVTD.tl_text_cat_attribute3,
2853 PAVT.tl_text_cat_attribute3, 'Y', 'N') = 'Y'
2854 AND DECODE (PAVTD.tl_text_cat_attribute4,
2855 PAVT.tl_text_cat_attribute4, 'Y', 'N') = 'Y'
2856 AND DECODE (PAVTD.tl_text_cat_attribute5,
2857 PAVT.tl_text_cat_attribute5, 'Y', 'N') = 'Y'
2858 AND DECODE (PAVTD.tl_text_cat_attribute6,
2859 PAVT.tl_text_cat_attribute6, 'Y', 'N') = 'Y'
2860 AND DECODE (PAVTD.tl_text_cat_attribute7,
2861 PAVT.tl_text_cat_attribute7, 'Y', 'N') = 'Y'
2862 AND DECODE (PAVTD.tl_text_cat_attribute8,
2863 PAVT.tl_text_cat_attribute8, 'Y', 'N') = 'Y'
2864 AND DECODE (PAVTD.tl_text_cat_attribute9,
2865 PAVT.tl_text_cat_attribute9, 'Y', 'N') = 'Y'
2866 AND DECODE (PAVTD.tl_text_cat_attribute10,
2867 PAVT.tl_text_cat_attribute10, 'Y', 'N') = 'Y'
2868 AND DECODE (PAVTD.tl_text_cat_attribute11,
2869 PAVT.tl_text_cat_attribute11, 'Y', 'N') = 'Y'
2870 AND DECODE (PAVTD.tl_text_cat_attribute12,
2871 PAVT.tl_text_cat_attribute12, 'Y', 'N') = 'Y'
2872 AND DECODE (PAVTD.tl_text_cat_attribute13,
2873 PAVT.tl_text_cat_attribute13, 'Y', 'N') = 'Y'
2874 AND DECODE (PAVTD.tl_text_cat_attribute14,
2875 PAVT.tl_text_cat_attribute14, 'Y', 'N') = 'Y'
2876 AND DECODE (PAVTD.tl_text_cat_attribute15,
2877 PAVT.tl_text_cat_attribute15, 'Y', 'N') = 'Y'
2878 AND DECODE (PAVTD.tl_text_cat_attribute16,
2879 PAVT.tl_text_cat_attribute16, 'Y', 'N') = 'Y'
2880 AND DECODE (PAVTD.tl_text_cat_attribute17,
2881 PAVT.tl_text_cat_attribute17, 'Y', 'N') = 'Y'
2882 AND DECODE (PAVTD.tl_text_cat_attribute18,
2883 PAVT.tl_text_cat_attribute18, 'Y', 'N') = 'Y'
2884 AND DECODE (PAVTD.tl_text_cat_attribute19,
2885 PAVT.tl_text_cat_attribute19, 'Y', 'N') = 'Y'
2886 AND DECODE (PAVTD.tl_text_cat_attribute20,
2887 PAVT.tl_text_cat_attribute20, 'Y', 'N') = 'Y'
2888 AND DECODE (PAVTD.tl_text_cat_attribute21,
2889 PAVT.tl_text_cat_attribute21, 'Y', 'N') = 'Y'
2890 AND DECODE (PAVTD.tl_text_cat_attribute22,
2891 PAVT.tl_text_cat_attribute22, 'Y', 'N') = 'Y'
2892 AND DECODE (PAVTD.tl_text_cat_attribute23,
2893 PAVT.tl_text_cat_attribute23, 'Y', 'N') = 'Y'
2894 AND DECODE (PAVTD.tl_text_cat_attribute24,
2895 PAVT.tl_text_cat_attribute24, 'Y', 'N') = 'Y'
2896 AND DECODE (PAVTD.tl_text_cat_attribute25,
2897 PAVT.tl_text_cat_attribute25, 'Y', 'N') = 'Y'
2898 AND DECODE (PAVTD.tl_text_cat_attribute26,
2899 PAVT.tl_text_cat_attribute26, 'Y', 'N') = 'Y'
2903 PAVT.tl_text_cat_attribute28, 'Y', 'N') = 'Y'
2900 AND DECODE (PAVTD.tl_text_cat_attribute27,
2901 PAVT.tl_text_cat_attribute27, 'Y', 'N') = 'Y'
2902 AND DECODE (PAVTD.tl_text_cat_attribute28,
2904 AND DECODE (PAVTD.tl_text_cat_attribute29,
2905 PAVT.tl_text_cat_attribute29, 'Y', 'N') = 'Y'
2906 AND DECODE (PAVTD.tl_text_cat_attribute30,
2907 PAVT.tl_text_cat_attribute30, 'Y', 'N') = 'Y'
2908 AND DECODE (PAVTD.tl_text_cat_attribute31,
2909 PAVT.tl_text_cat_attribute31, 'Y', 'N') = 'Y'
2910 AND DECODE (PAVTD.tl_text_cat_attribute32,
2911 PAVT.tl_text_cat_attribute32, 'Y', 'N') = 'Y'
2912 AND DECODE (PAVTD.tl_text_cat_attribute33,
2913 PAVT.tl_text_cat_attribute33, 'Y', 'N') = 'Y'
2914 AND DECODE (PAVTD.tl_text_cat_attribute34,
2915 PAVT.tl_text_cat_attribute34, 'Y', 'N') = 'Y'
2916 AND DECODE (PAVTD.tl_text_cat_attribute35,
2917 PAVT.tl_text_cat_attribute35, 'Y', 'N') = 'Y'
2918 AND DECODE (PAVTD.tl_text_cat_attribute36,
2919 PAVT.tl_text_cat_attribute36, 'Y', 'N') = 'Y'
2920 AND DECODE (PAVTD.tl_text_cat_attribute37,
2921 PAVT.tl_text_cat_attribute37, 'Y', 'N') = 'Y'
2922 AND DECODE (PAVTD.tl_text_cat_attribute38,
2923 PAVT.tl_text_cat_attribute38, 'Y', 'N') = 'Y'
2924 AND DECODE (PAVTD.tl_text_cat_attribute39,
2925 PAVT.tl_text_cat_attribute39, 'Y', 'N') = 'Y'
2926 AND DECODE (PAVTD.tl_text_cat_attribute40,
2927 PAVT.tl_text_cat_attribute40, 'Y', 'N') = 'Y'
2928 AND DECODE (PAVTD.tl_text_cat_attribute41,
2929 PAVT.tl_text_cat_attribute41, 'Y', 'N') = 'Y'
2930 AND DECODE (PAVTD.tl_text_cat_attribute42,
2931 PAVT.tl_text_cat_attribute42, 'Y', 'N') = 'Y'
2932 AND DECODE (PAVTD.tl_text_cat_attribute43,
2933 PAVT.tl_text_cat_attribute43, 'Y', 'N') = 'Y'
2934 AND DECODE (PAVTD.tl_text_cat_attribute44,
2935 PAVT.tl_text_cat_attribute44, 'Y', 'N') = 'Y'
2936 AND DECODE (PAVTD.tl_text_cat_attribute45,
2937 PAVT.tl_text_cat_attribute45, 'Y', 'N') = 'Y'
2938 AND DECODE (PAVTD.tl_text_cat_attribute46,
2939 PAVT.tl_text_cat_attribute46, 'Y', 'N') = 'Y'
2940 AND DECODE (PAVTD.tl_text_cat_attribute47,
2941 PAVT.tl_text_cat_attribute47, 'Y', 'N') = 'Y'
2942 AND DECODE (PAVTD.tl_text_cat_attribute48,
2943 PAVT.tl_text_cat_attribute48, 'Y', 'N') = 'Y'
2944 AND DECODE (PAVTD.tl_text_cat_attribute49,
2945 PAVT.tl_text_cat_attribute49, 'Y', 'N') = 'Y'
2946 AND DECODE (PAVTD.tl_text_cat_attribute50,
2947 PAVT.tl_text_cat_attribute50, 'Y', 'N') = 'Y');
2948
2949 IF (PO_LOG.d_stmt) THEN
2950 PO_LOG.stmt (d_module, d_position, '# of attr values tlp autoaccepted', SQL%ROWCOUNT);
2951 END IF;
2952
2953 d_position := 50;
2954
2955 l_has_record_to_accept := has_record_to_accept
2956 ( p_draft_id => p_draft_id
2957 );
2958
2959
2960 IF (PO_LOG.d_proc) THEN
2961 PO_LOG.proc_end (d_module, 'l_has_record_to_accept', l_has_record_to_accept);
2962 END IF;
2963
2964 RETURN l_has_record_to_accept;
2965
2966 EXCEPTION
2967 WHEN OTHERS THEN
2968 PO_MESSAGE_S.add_exc_msg
2969 ( p_pkg_name => d_pkg_name,
2970 p_procedure_name => d_api_name || '.' || d_position
2971 );
2972
2973 RAISE;
2974 END autoaccept_unchanged_records;
2975
2976
2977 -----------------------------------------------------------------------
2978 --Start of Comments
2979 --Name: has_record_to_accept
2980 --Function:
2981 -- Returns whether there's still record pending for acceptance in the
2982 -- draft
2983 --Parameters:
2984 --IN:
2985 --p_draft_id
2986 -- draft unique identifier
2987 --IN OUT:
2988 --OUT:
2989 --Return:
2990 -- FND_API.G_TRUE if there's still record in the draft to accept
2991 -- FND_API.G_FALSE otherwise
2992 --Notes:
2993 --Testing:
2994 --End of Comments
2995 ------------------------------------------------------------------------
2996 FUNCTION has_record_to_accept
2997 ( p_draft_id IN NUMBER
2998 ) RETURN VARCHAR2
2999 IS
3000
3001 d_api_name CONSTANT VARCHAR2(30) := 'has_record_to_accept';
3002 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3003 d_position NUMBER;
3004
3005 l_has_record_to_accept VARCHAR2(1) := FND_API.G_TRUE;
3006
3007 BEGIN
3008
3009 d_position := 0;
3010
3011 IF (PO_LOG.d_proc) THEN
3012 PO_LOG.proc_begin (d_module);
3013 END IF;
3014
3015 SELECT NVL(MAX(FND_API.G_TRUE), FND_API.G_FALSE)
3016 INTO l_has_record_to_accept
3017 FROM DUAL
3018 WHERE EXISTS
3019 ( SELECT 1
3020 FROM po_lines_draft_all
3021 WHERE draft_id = p_draft_id
3022 AND change_accepted_flag IS NULL)
3023 OR EXISTS
3024 ( SELECT 1
3025 FROM po_line_locations_draft_all
3026 WHERE draft_id = p_draft_id
3027 AND change_accepted_flag IS NULL)
3028 OR EXISTS
3029 ( SELECT 1
3033 OR EXISTS
3030 FROM po_price_diff_draft
3031 WHERE draft_id = p_draft_id
3032 AND change_accepted_flag IS NULL)
3034 ( SELECT 1
3035 FROM po_attribute_values_draft
3036 WHERE draft_id = p_draft_id
3037 AND change_accepted_flag IS NULL)
3038 OR EXISTS
3039 ( SELECT 1
3040 FROM po_attribute_values_tlp_draft
3041 WHERE draft_id = p_draft_id
3042 AND change_accepted_flag IS NULL);
3043
3044
3045 IF (PO_LOG.d_proc) THEN
3046 PO_LOG.proc_end (d_module, 'l_has_record_to_accept', l_has_record_to_accept);
3047 END IF;
3048
3049 RETURN l_has_record_to_accept;
3050
3051
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 PO_MESSAGE_S.add_exc_msg
3055 ( p_pkg_name => d_pkg_name,
3056 p_procedure_name => d_api_name || '.' || d_position
3057 );
3058
3059 RAISE;
3060 END has_record_to_accept;
3061
3062 -- bug5249414 END
3063
3064
3065 -- bug5035979 START
3066 -- when the price acceptance level is 'Required when price update exceeds
3067 -- tolerance', we need to autoaccept lines that do not contain line changes
3068
3069 -----------------------------------------------------------------------
3070 --Start of Comments
3071 --Name: accept_if_no_line_changes
3072 --Function:
3073 -- Autoaccept records that do not have line level changes. This is called
3074 -- when acceptance level is 'required when price exceeds tolereance'. We
3075 -- do not need to accept the records since they won't contain any price
3076 -- changes.
3077 --Parameters:
3078 --IN:
3079 --p_draft_id
3080 -- draft unique identifier
3081 --IN OUT:
3082 --OUT:
3083 --Notes:
3084 --Testing:
3085 --End of Comments
3086 ------------------------------------------------------------------------
3087 PROCEDURE accept_if_no_line_changes
3088 ( p_draft_id IN NUMBER
3089 ) IS
3090
3091 d_api_name CONSTANT VARCHAR2(30) := 'accept_if_no_line_changes';
3092 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3093 d_position NUMBER;
3094
3095 BEGIN
3096
3097 d_position := 0;
3098
3099 IF (PO_LOG.d_proc) THEN
3100 PO_LOG.proc_begin (d_module);
3101 END IF;
3102
3103 UPDATE po_line_locations_draft_all PLLDA
3104 SET PLLDA.change_accepted_flag = 'Y'
3105 WHERE PLLDA.draft_id = p_draft_id
3106 AND NVL(PLLDA.change_accepted_flag, 'N') <> 'Y'
3107 AND NOT EXISTS
3108 ( SELECT NULL
3109 FROM po_lines_draft_all PLDA
3110 WHERE PLDA.po_line_id = PLLDA.po_line_id
3111 AND PLDA.draft_id = PLLDA.draft_id );
3112
3113 d_position := 10;
3114
3115 UPDATE po_attribute_values_draft PLVD
3116 SET PLVD.change_accepted_flag = 'Y'
3117 WHERE PLVD.draft_id = p_draft_id
3118 AND NVL(PLVD.change_accepted_flag, 'N') <> 'Y'
3119 AND NOT EXISTS
3120 ( SELECT NULL
3121 FROM po_lines_draft_all PLDA
3122 WHERE PLDA.po_line_id = PLVD.po_line_id
3123 AND PLDA.draft_id = PLVD.draft_id );
3124
3125 d_position := 20;
3126
3127 UPDATE po_attribute_values_tlp_draft PLVTD
3128 SET PLVTD.change_accepted_flag = 'Y'
3129 WHERE PLVTD.draft_id = p_draft_id
3130 AND NVL(PLVTD.change_accepted_flag, 'N') <> 'Y'
3131 AND NOT EXISTS
3132 ( SELECT NULL
3133 FROM po_lines_draft_all PLDA
3134 WHERE PLDA.po_line_id = PLVTD.po_line_id
3135 AND PLDA.draft_id = PLVTD.draft_id );
3136
3137 d_position := 30;
3138
3139 UPDATE po_price_diff_draft PPDD
3140 SET PPDD.change_accepted_flag = 'Y'
3141 WHERE PPDD.draft_id = p_draft_id
3142 AND NVL(PPDD.change_accepted_flag, 'N') <> 'Y'
3143 AND (( PPDD.entity_type = 'BLANKET LINE'
3144 AND NOT EXISTS
3145 ( SELECT NULL
3146 FROM po_lines_draft_all PLDA
3147 WHERE PLDA.po_line_id = PPDD.entity_id
3148 AND PLDA.draft_id = PPDD.draft_id
3149 )
3150 )
3151 OR
3152 ( PPDD.entity_type = 'PRICE BREAK'
3153 AND NOT EXISTS
3154 ( SELECT NULL
3155 FROM po_line_locations_draft_all PLLDA,
3156 po_lines_draft_all PLDA
3157 WHERE PLLDA.line_location_id = PPDD.entity_id
3158 AND PLLDA.draft_id = PPDD.draft_id
3159 AND PLDA.po_line_id = PLLDA.po_line_id
3160 AND PLDA.draft_id = PLLDA.draft_id )
3161 AND NOT EXISTS
3162 ( SELECT NULL
3163 FROM po_line_locations_all PLLA,
3164 po_lines_draft_all PLDA
3165 WHERE PLLA.line_location_id = PPDD.entity_id
3166 AND PLDA.po_line_id = PLLA.po_line_id
3167 AND PLDA.draft_id = PPDD.draft_id)));
3168
3169 IF (PO_LOG.d_proc) THEN
3170 PO_LOG.proc_end (d_module);
3171 END IF;
3172
3173 EXCEPTION
3174 WHEN OTHERS THEN
3175 PO_MESSAGE_S.add_exc_msg
3176 ( p_pkg_name => d_pkg_name,
3177 p_procedure_name => d_api_name || '.' || d_position
3178 );
3179
3180 RAISE;
3181 END accept_if_no_line_changes;
3182
3183 -----------------------------------------------------------------------
3184 --Start of Comments
3185 --Name: cascade_change_acceptance
3186 --Function:
3187 -- Update the change acceptance status for the lines being passed in,
3191 --p_draft_id
3188 -- and cascade the changes to their children.
3189 --Parameters:
3190 --IN:
3192 -- draft unique identifier
3193 --p_line_id_list
3194 -- lines to modify
3195 --p_change_accepted_value
3196 -- New change acceptance status
3197 --IN OUT:
3198 --OUT:
3199 --x_return_status
3200 -- status of the procedure
3201 --Returns:
3202 --Notes:
3203 --Testing:
3204 --End of Comments
3205 ------------------------------------------------------------------------
3206 PROCEDURE cascade_change_acceptance
3207 ( p_draft_id IN NUMBER,
3208 p_line_id_list IN PO_TBL_NUMBER,
3209 p_change_accepted_value IN VARCHAR2,
3210 x_return_status OUT NOCOPY VARCHAR2
3211 ) IS
3212
3213 d_api_name CONSTANT VARCHAR2(30) := 'cascade_change_acceptance';
3214 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3215 d_position NUMBER;
3216
3217 BEGIN
3218 d_position := 0;
3219
3220 IF (PO_LOG.d_proc) THEN
3221 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
3222 PO_LOG.proc_begin (d_module, 'p_line_id_list.COUNT', p_line_id_list.COUNT);
3223 END IF;
3224
3225 x_return_status := FND_API.g_RET_STS_SUCCESS;
3226
3227 -- Update records in PO_LINES_DRAFT_ALL based on the list of lines to change
3228 -- in p_line_id_list;
3229 FORALL i IN 1..p_line_id_list.COUNT
3230 UPDATE po_lines_draft_all
3231 SET change_accepted_flag = p_change_accepted_value
3232 WHERE draft_id = p_draft_id
3233 AND po_line_id = p_line_id_list(i);
3234
3235 d_position := 10;
3236
3237 -- price breaks
3238 FORALL i IN 1..p_line_id_list.COUNT
3239 UPDATE po_line_locations_draft_all
3240 SET change_accepted_flag = p_change_accepted_value
3241 WHERE draft_id = p_draft_id
3242 AND po_line_id = p_line_id_list(i);
3243
3244 d_position := 20;
3245
3246 -- attribute values
3247 FORALL i IN 1..p_line_id_list.COUNT
3248 UPDATE po_attribute_values_draft
3249 SET change_accepted_flag = p_change_accepted_value
3250 WHERE draft_id = p_draft_id
3251 AND po_line_id = p_line_id_list(i);
3252
3253 d_position := 30;
3254
3255 -- attribute values tlp
3256 FORALL i IN 1..p_line_id_list.COUNT
3257 UPDATE po_attribute_values_tlp_draft
3258 SET change_accepted_flag = p_change_accepted_value
3259 WHERE draft_id = p_draft_id
3260 AND po_line_id = p_line_id_list(i);
3261
3262 d_position := 40;
3263
3264 -- blanket line price differentials
3265 FORALL i IN 1..p_line_id_list.COUNT
3266 UPDATE po_price_diff_draft
3267 SET change_accepted_flag = p_change_accepted_value
3268 WHERE draft_id = p_draft_id
3269 AND entity_id = p_line_id_list(i)
3270 AND entity_type = 'BLANKET LINE';
3271
3272 d_position := 50;
3273
3274 -- Price break price diffs - the po_line_id reference can either
3275 -- be found in draft or transaction table
3276 FORALL i IN 1..p_line_id_list.COUNT
3277 UPDATE po_price_diff_draft PPDD
3278 SET change_accepted_flag = p_change_accepted_value
3279 WHERE draft_id = p_draft_id
3280 AND entity_type = 'PRICE BREAK'
3281 AND EXISTS (SELECT 1
3282 FROM po_line_locations_draft_all PLLD
3283 WHERE PLLD.draft_id = p_draft_id
3284 AND PLLD.po_line_id = p_line_id_list(i)
3285 AND PLLD.line_location_id = PPDD.entity_id
3286 UNION ALL
3287 SELECT 1
3288 FROM po_line_locations_all PLLD
3289 WHERE PLLD.po_line_id = p_line_id_list(i)
3290 AND PLLD.line_location_id = PPDD.entity_id);
3291
3292 IF (PO_LOG.d_proc) THEN
3293 PO_LOG.proc_end (d_module);
3294 END IF;
3295
3296 EXCEPTION
3297 WHEN OTHERS THEN
3298 PO_MESSAGE_S.add_exc_msg
3299 ( p_pkg_name => d_pkg_name,
3300 p_procedure_name => d_api_name || '.' || d_position
3301 );
3302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3303 END cascade_change_acceptance;
3304
3305 -- bug5035979 END
3306
3307 -- bug5187544 START
3308 -----------------------------------------------------------------------
3309 --Start of Comments
3310 --Name: validate_disposition
3311 --Function:
3312 -- Validate the rejected list by the user can make sure that the action
3313 -- can be performed
3314 --Parameters:
3315 --IN:
3316 --p_draft_id
3317 -- draft unique identifier
3318 --p_reject_line_id_list
3319 -- lines being rejected
3320 --IN OUT:
3321 --OUT:
3322 --x_invalid_line_id_list
3323 -- lines that fail the validation
3324 --x_invalid_line_num_list
3325 -- line num for the lines that fail the validation
3326 --Returns:
3327 --Notes:
3328 --Testing:
3329 --End of Comments
3330 ------------------------------------------------------------------------
3331 PROCEDURE validate_disposition
3332 ( p_draft_id IN NUMBER,
3333 p_reject_line_id_list IN PO_TBL_NUMBER,
3334 x_invalid_line_id_list OUT NOCOPY PO_TBL_NUMBER,
3335 x_invalid_line_num_list OUT NOCOPY PO_TBL_NUMBER,
3336 x_return_status OUT NOCOPY VARCHAR2
3337 ) IS
3338
3339 d_api_name CONSTANT VARCHAR2(30) := 'validate_disposition';
3340 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3341 d_position NUMBER;
3342
3343 l_key PO_SESSION_GT.key%TYPE;
3344
3345 BEGIN
3346
3347 d_position := 0;
3348
3352 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
3349 x_return_status := FND_API.G_RET_STS_SUCCESS;
3350
3351 IF (PO_LOG.d_proc) THEN
3353 END IF;
3354
3355 x_invalid_line_id_list := PO_TBL_NUMBER();
3356
3357 IF (p_reject_line_id_list IS NULL OR
3358 p_reject_line_id_list.COUNT = 0) THEN
3359 RETURN;
3360 END IF;
3361
3362 -- If user rejects the deletion of a line, he cannot accept another line
3363 -- with the same line number, otherwise we'll have the old line not being
3364 -- deleted and the new line (same line #) being inserted.
3365
3366 l_key := PO_CORE_S.get_session_gt_nextval;
3367
3368 d_position := 10;
3369
3370 FORALL i IN 1..p_reject_line_id_list.COUNT
3371 INSERT INTO po_session_gt
3372 ( key,
3373 index_num1, -- po_line_id
3374 num2, -- line_num
3375 char2 -- delete_flag
3376 )
3377 SELECT l_key,
3378 p_reject_line_id_list(i),
3379 pld.line_num,
3380 pld.delete_flag
3381 FROM po_lines_draft_all pld
3382 WHERE pld.draft_id = p_draft_id
3383 AND pld.po_line_id = p_reject_line_id_list(i);
3384
3385 d_position := 20;
3386
3387 -- SQL What: Return the deleted lines that are being rejected, if there are
3388 -- other lines with the same line number that are being accepted
3389 SELECT PSG.index_num1,
3390 num2
3391 BULK COLLECT
3392 INTO x_invalid_line_id_list,
3393 x_invalid_line_num_list
3394 FROM po_session_gt PSG
3395 WHERE PSG.key = l_key
3396 AND PSG.char2 = 'Y' -- delete_flag
3397 AND EXISTS
3398 (SELECT NULL
3399 FROM po_lines_draft_all PLD
3400 WHERE PLD.draft_id = p_draft_id
3401 AND NVL(PLD.delete_flag, 'N') <> 'Y'
3402 AND PLD.line_num = PSG.num2 -- line_num
3403 AND NOT EXISTS
3404 (SELECT NULL
3405 FROM po_session_gt PSG1
3406 WHERE PSG1.key = l_key
3407 AND PSG1.index_num1 = PLD.po_line_id));
3408
3409 d_position := 30;
3410
3411 DELETE FROM po_session_gt
3412 WHERE key = l_key;
3413
3414 IF (x_invalid_line_id_list.COUNT > 0) THEN
3415 x_return_status := FND_API.G_RET_STS_ERROR;
3416 END IF;
3417
3418 IF (PO_LOG.d_proc) THEN
3419 PO_LOG.proc_end (d_module);
3420 END IF;
3421
3422 EXCEPTION
3423 WHEN OTHERS THEN
3424 PO_MESSAGE_S.add_exc_msg
3425 ( p_pkg_name => d_pkg_name,
3426 p_procedure_name => d_api_name || '.' || d_position
3427 );
3428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3429 END validate_disposition;
3430
3431 -- bug5187544 END
3432
3433 --Bug 12964696
3434 PROCEDURE post_buyer_acceptance
3435 ( itemtype IN VARCHAR2,
3436 itemkey IN VARCHAR2,
3437 actid IN NUMBER,
3438 funcmode IN VARCHAR2,
3439 resultout OUT NOCOPY VARCHAR2
3440 )
3441 IS
3442
3443 d_api_name CONSTANT VARCHAR2(30) := 'post_buyer_acceptance';
3444 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3445 d_position NUMBER;
3446
3447
3448 l_nid wf_notifications.notification_id%TYPE;
3449 l_session_user_id fnd_user.USER_ID%TYPE;
3450 l_responder_id fnd_user.USER_ID%TYPE;
3451 l_responder_name fnd_user.user_name%TYPE;
3452
3453 BEGIN
3454
3455 d_position := 0;
3456
3457
3458
3459 IF (PO_LOG.d_proc) THEN
3460 PO_LOG.proc_begin (d_module, 'funcmode : ', funcmode);
3461 END IF;
3462
3463
3464
3465 IF (funcmode = 'CANCEL') THEN
3466 d_position := 1;
3467
3468 l_nid := WF_ENGINE.context_nid;
3469 l_session_user_id := fnd_global.user_id;
3470
3471
3472
3473 SELECT fu.USER_ID, fu.user_name
3474 INTO l_responder_id, l_responder_name
3475 FROM fnd_user fu,
3476 wf_notifications wfn
3477 WHERE wfn.notification_id = l_nid
3478 AND wfn.original_recipient = fu.user_name;
3479
3480 d_position := 2;
3481
3482 IF (l_responder_id = l_session_user_id) THEN
3483 d_position := 3;
3484 wf_notification.CLOSE(l_nid,l_responder_name);
3485 END IF;
3486 d_position := 4;
3487
3488 END IF;
3489
3490 d_position := 5;
3491
3492 IF (PO_LOG.d_proc) THEN
3493 PO_LOG.proc_end (d_module);
3494 END IF;
3495
3496
3497 EXCEPTION
3498
3499 WHEN OTHERS THEN
3500
3501 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
3502 RAISE;
3503
3504 END post_buyer_acceptance;
3505
3506 -- Bug 13469215
3507 PROCEDURE should_approval_be_launched (itemtype IN VARCHAR2,
3508 itemkey IN VARCHAR2,
3509 actid IN NUMBER,
3510 funcmode IN VARCHAR2,
3511 resultout OUT NOCOPY VARCHAR2)
3512 IS
3513 d_api_name CONSTANT VARCHAR2(30) := 'should_approval_be_launched';
3514 d_module CONSTANT VARCHAR2(255) := po_log.Get_subprogram_base(d_pkg_name,
3515 d_api_name);
3516 d_position NUMBER;
3517 l_launch_approval VARCHAR2(10);
3518 l_any_line_accepted VARCHAR2(1);
3519 l_draft_id po_drafts.draft_id%TYPE;
3520 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
3521
3522 BEGIN
3523
3524 d_position := 0;
3525 IF ( po_log.d_proc ) THEN
3526 po_log.Proc_begin (d_module);
3527 END IF;
3528
3529 l_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
3530 itemkey => itemkey,
3531 aname => 'DRAFT_ID');
3532
3533 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber
3534 ( itemtype => itemtype,
3535 itemkey => itemkey,
3536 aname => 'PO_HEADER_ID'
3537 );
3538
3539 BEGIN
3540
3541 SELECT 'Y'
3542 INTO l_any_line_accepted
3543 FROM dual
3544 WHERE EXISTS (SELECT 1
3545 FROM po_lines_draft_all
3546 WHERE draft_id = l_draft_id
3547 AND nvl(change_accepted_flag,'Y') = 'Y')
3548 OR EXISTS (SELECT 1
3549 FROM po_line_locations_draft_all
3550 WHERE draft_id = l_draft_id
3551 AND nvl(change_accepted_flag,'Y') = 'Y')
3552 OR EXISTS (SELECT 1
3553 FROM po_price_diff_draft
3554 WHERE draft_id = l_draft_id
3555 AND nvl(change_accepted_flag,'Y') = 'Y')
3556 OR EXISTS (SELECT 1
3557 FROM po_attribute_values_draft
3558 WHERE draft_id = l_draft_id
3559 AND nvl(change_accepted_flag,'Y') = 'Y');
3560
3561 EXCEPTION
3562
3563 WHEN no_data_found THEN
3564 l_any_line_accepted := 'N';
3565 END;
3566
3567 d_position := 20;
3568
3569 IF ( po_log.d_stmt ) THEN
3570 po_log.Stmt (d_module, d_position, 'l_any_line_accepted', l_any_line_accepted);
3571 END IF;
3572
3573 l_launch_approval := po_wf_util_pkg.Getitemattrtext (itemtype => itemtype,
3574 itemkey => itemkey,
3575 aname=> 'LAUNCH_APPROVAL');
3576
3577 d_position := 30;
3578
3579 IF ( po_log.d_stmt ) THEN
3580 po_log.Stmt (d_module, d_position, 'l_launch_approval', l_launch_approval);
3581 END IF;
3582
3583 IF( l_any_line_accepted = 'Y' AND l_launch_approval = 'Y' ) THEN
3584 resultout := wf_engine.eng_completed || ':Y';
3585 ELSE
3586 PO_DRAFTS_PVT.unlock_document(l_po_header_id);
3587 resultout := wf_engine.eng_completed || ':N';
3588 END IF;
3589
3590 IF ( po_log.d_proc ) THEN
3591 po_log.Proc_end (d_module);
3592 END IF;
3593
3594 EXCEPTION
3595
3596 WHEN OTHERS THEN
3597 wf_core.Context (d_pkg_name, d_api_name, d_position
3598 || ': SQL Error '
3599 || SQLCODE);
3600 RAISE;
3601 END should_approval_be_launched;
3602
3603 END PO_DIFF_SUMMARY_PKG;