[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.10.12000000.2 2007/07/11 08:28:58 puppulur 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',
210 avalue => l_buyer_user_name
211 );
212
213 PO_WF_UTIL_PKG.SetItemAttrText
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,
421 aname => 'BUYER_ACCEPTANCE_REQUIRED',
422 avalue => l_acceptance_required
423 );
424
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
468 IF (PO_LOG.d_proc) THEN
465 resultout := WF_ENGINE.eng_completed || ':N';
466 END IF;
467
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;
631 ( itemtype IN VARCHAR2,
628
629
630 PROCEDURE get_buyers_manager
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 *
800 (
797 BULK COLLECT
798 INTO l_reject_list
799 FROM
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,
868 actid IN NUMBER,
869 funcmode IN VARCHAR2,
870 resultout OUT NOCOPY VARCHAR2
871 ) IS
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);
966 d_position := 0;
963 l_return_status VARCHAR2(1);
964
965 BEGIN
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;
1033 l_org_id PO_HEADERS_ALL.org_id%TYPE;
1034 l_preparer_id PO_HEADERS_ALL.agent_id%TYPE;
1035
1036 l_printflag VARCHAR2(1) := 'N';
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 BEGIN
1046 d_position := 0;
1047
1048 IF (PO_LOG.d_proc) THEN
1049 PO_LOG.proc_begin (d_module);
1050 END IF;
1051
1052
1053 -- Launch Approval WF
1054 -- Retrieve some settings for launching the PO Approval workflow.
1055 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1056 ( itemtype => itemtype,
1057 itemkey => itemkey,
1058 aname => 'PO_HEADER_ID'
1059 );
1060
1061 l_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1062 ( itemtype => itemtype,
1063 itemkey => itemkey,
1064 aname => 'ORG_ID'
1065 );
1066
1067 l_note_to_approver := PO_WF_UTIL_PKG.GetItemAttrText
1068 ( itemtype => itemtype,
1069 itemkey => itemkey,
1070 aname => 'NOTE_TO_APPROVER'
1071 );
1072
1073 d_position := 10;
1074
1075 PO_VENDOR_SITES_SV.get_transmission_defaults
1076 ( p_document_id => l_po_header_id,
1077 p_document_type => l_document_type,
1078 p_document_subtype => l_document_subtype,
1079 p_preparer_id => l_preparer_id, -- IN OUT parameter
1080 x_default_method => l_default_method,
1081 x_email_address => l_emailaddress,
1082 x_fax_number => l_faxnum,
1083 x_document_num => l_document_num
1084 );
1085
1086 d_position := 20;
1087
1088 IF (l_default_method = 'EMAIL') AND (l_emailaddress IS NOT NULL) THEN
1089 l_emailflag := 'Y';
1090 ELSIF (l_default_method = 'FAX') AND (l_faxnum IS NOT NULL) then
1091 l_emailaddress := NULL;
1092 l_faxflag := 'Y';
1093 ELSIF (l_default_method = 'PRINT') then
1094 l_emailaddress := null;
1095 l_faxnum := null;
1096 l_printflag := 'Y';
1097 ELSE
1098 l_emailaddress := null;
1099 l_faxnum := null;
1100 END IF; -- l_default_method
1101
1102 d_position := 30;
1103
1104 -- Launch the PO Approval workflow.
1105 PO_REQAPPROVAL_INIT1.start_wf_process (
1106 ItemType => NULL, -- defaulted in start_wf_process
1107 ItemKey => NULL, -- defaulted in start_wf_process
1108 WorkflowProcess => NULL, -- defaulted in start_wf_process
1109 ActionOriginatedFrom => NULL,
1110 DocumentID => l_po_header_id,
1111 DocumentNumber => NULL,
1112 PreparerID => l_preparer_id,
1116 ForwardToID => NULL,
1113 DocumentTypeCode => l_document_type,
1114 DocumentSubtype => l_document_subtype,
1115 SubmitterAction => NULL,
1117 ForwardFromID => NULL,
1118 DefaultApprovalPathID => NULL,
1119 Note => l_note_to_approver,
1120 PrintFlag => l_printflag,
1121 FaxFlag => l_faxflag,
1122 FaxNumber => l_faxnum,
1123 EmailFlag => l_emailflag,
1124 EmailAddress => l_emailaddress
1125 );
1126
1127 d_position := 40;
1128
1129 IF (PO_LOG.d_proc) THEN
1130 PO_LOG.proc_end (d_module);
1131 END IF;
1132
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 WF_CORE.context (d_pkg_name, d_api_name, d_position || ': SQL Error ' || sqlcode);
1136 RAISE;
1137 END launch_po_approval_wf;
1138
1139
1140 --==========================================================================
1141 -- Regular procedures
1142 --==========================================================================
1143
1144 -----------------------------------------------------------------------
1145 --Start of Comments
1146 --Name: get_new_itemkey
1147 --Function:
1148 -- Get new itemkey
1149 --Parameters:
1150 --IN:
1151 --p_draft_id
1152 -- draft unique identifier
1153 --IN OUT:
1154 --OUT:
1155 --Returns:
1156 -- New item key
1157 --Notes:
1158 --Testing:
1159 --End of Comments
1160 ------------------------------------------------------------------------
1161 FUNCTION get_new_itemkey
1162 ( p_draft_id IN NUMBER
1163 ) RETURN VARCHAR2
1164 IS
1165
1166 l_new_itemkey NUMBER;
1167
1168 BEGIN
1169 SELECT PO_WF_ITEMKEY_S.NEXTVAL
1170 INTO l_new_itemkey
1171 FROM DUAL;
1172
1173 RETURN g_ITEMKEY_PREFIX || '#' || TO_CHAR(p_draft_id) || '#' ||
1174 TO_CHAR(l_new_itemkey);
1175
1176 END get_new_itemkey;
1177
1178 -----------------------------------------------------------------------
1179 --Start of Comments
1180 --Name: find_itemkey
1181 --Function:
1182 -- Find item key of the buyer acceptance workflow for the current draft
1183 --Parameters:
1184 --IN:
1185 --p_draft_id
1186 -- draft unique identifier
1187 --IN OUT:
1188 --OUT:
1189 --x_return_status
1190 -- status of the procedure
1191 --Returns:
1192 --Notes:
1193 --Testing:
1194 --End of Comments
1195 ------------------------------------------------------------------------
1196 FUNCTION find_itemkey
1197 ( p_draft_id IN NUMBER
1198 ) RETURN VARCHAR2
1199 IS
1200
1201 d_api_name CONSTANT VARCHAR2(30) := 'find_itemkey';
1202 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1203 d_position NUMBER;
1204
1205 l_itemkey_like WF_ITEMS.item_key%TYPE;
1206 l_itemkey WF_ITEMS.item_key%TYPE;
1207
1208 BEGIN
1209 d_position := 0;
1210
1211 IF (PO_LOG.d_proc) THEN
1212 PO_LOG.proc_begin (d_module);
1213 END IF;
1214
1215 l_itemkey_like := g_ITEMKEY_PREFIX || '#' || TO_CHAR(p_draft_id) || '#%';
1216
1217 SELECT MAX(item_key)
1218 INTO l_itemkey
1219 FROM WF_ITEMS
1220 WHERE item_type = g_ITEMTYPE
1221 AND item_key LIKE l_itemkey_like
1222 AND end_date IS NULL;
1223
1224 IF (PO_LOG.d_proc) THEN
1225 PO_LOG.proc_end (d_module, 'l_itemkey', l_itemkey);
1226 END IF;
1227
1228 RETURN l_itemkey;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 PO_MESSAGE_S.add_exc_msg
1233 ( p_pkg_name => d_pkg_name,
1234 p_procedure_name => d_api_name || '.' || d_position
1235 );
1236 RAISE;
1237 END find_itemkey;
1238
1239 -----------------------------------------------------------------------
1240 --Start of Comments
1241 --Name: record_disposition
1242 --Function:
1243 -- Mark all lines and their children with status Rejected based on the
1244 -- line rejection list passed in, if the rejected lines passed validation
1245 --Parameters:
1246 --IN:
1247 --p_draft_id
1248 -- draft unique identifier
1249 --p_reject_line_id_list
1250 -- lines to reject
1251 --IN OUT:
1252 --OUT:
1253 --x_invalid_line_id_list
1254 -- lines that fail validation
1255 --x_invalid_line_num_list
1256 -- line num for the lines that fail validation
1257 --x_return_status
1258 -- status of the procedure
1259 --Returns:
1260 --Notes:
1261 --Testing:
1262 --End of Comments
1263 ------------------------------------------------------------------------
1264 PROCEDURE record_disposition
1265 ( p_draft_id IN NUMBER,
1266 p_reject_line_id_list IN PO_TBL_NUMBER,
1267 x_invalid_line_id_list OUT NOCOPY PO_TBL_NUMBER, -- bug5187544
1268 x_invalid_line_num_list OUT NOCOPY PO_TBL_NUMBER, -- bug5187544
1269 x_return_status OUT NOCOPY VARCHAR2
1270 ) IS
1271
1272 d_api_name CONSTANT VARCHAR2(30) := 'record_disposition';
1273 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1274 d_position NUMBER;
1275
1276 l_return_status VARCHAR2(1);
1277 BEGIN
1278 d_position := 0;
1279
1280 IF (PO_LOG.d_proc) THEN
1281 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
1282 PO_LOG.proc_begin (d_module, 'p_reject_line_id_list.COUNT', p_reject_line_id_list.COUNT);
1283 END IF;
1284
1285 x_return_status := FND_API.g_RET_STS_SUCCESS;
1286
1287 d_position := 10;
1288
1292 ( p_draft_id => p_draft_id,
1289 -- bug5187544 START
1290 -- perform validation to the rejected lines
1291 validate_disposition
1293 p_reject_line_id_list => p_reject_line_id_list,
1294 x_invalid_line_id_list => x_invalid_line_id_list,
1295 x_invalid_line_num_list => x_invalid_line_num_list,
1296 x_return_status => l_return_status
1297 );
1298
1299 IF (PO_LOG.d_stmt) THEN
1300 PO_LOG.stmt (d_module, d_position, 'l_return_status', l_return_status);
1301 END IF;
1302
1303 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1304 x_return_status := l_return_status;
1305 RETURN;
1306
1307 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1309 END IF;
1310
1311 -- bug5187544 END
1312
1313 d_position := 20;
1314
1315 -- bug5035979
1316 -- refactored the code to call cascade_change_aceptance
1317 cascade_change_acceptance
1318 ( p_draft_id => p_draft_id,
1319 p_line_id_list => p_reject_line_id_list,
1320 p_change_accepted_value => 'N',
1321 x_return_status => l_return_status
1322 );
1323
1324 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327
1328 IF (PO_LOG.d_proc) THEN
1329 PO_LOG.proc_end (d_module);
1330 END IF;
1331
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 PO_MESSAGE_S.add_exc_msg
1335 ( p_pkg_name => d_pkg_name,
1336 p_procedure_name => d_api_name || '.' || d_position
1337 );
1338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1339 END record_disposition;
1340
1341 -----------------------------------------------------------------------
1342 --Start of Comments
1343 --Name: complete_resp_to_changes
1344 --Function:
1345 -- Post processing after buyer has responded to the changes. It relieves
1346 -- the block set up in buyer acceptance workflow so that draft changes can
1347 -- be applied to the transaction table
1348 --Parameters:
1349 --IN:
1350 --p_draft_id
1351 -- draft unique identifier
1352 --p_action
1353 -- Buyer's response to the draft changes
1354 --p_note_to_approver
1355 -- Note to the approver in approval hierarchy
1356 --IN OUT:
1357 --OUT:
1358 --x_return_status
1359 -- status of the procedure
1360 --Returns:
1361 --Notes:
1362 --Testing:
1363 --End of Comments
1364 ------------------------------------------------------------------------
1365 PROCEDURE complete_resp_to_changes
1366 ( p_draft_id IN NUMBER,
1367 p_action IN VARCHAR2,
1368 p_note_to_approver IN VARCHAR2,
1369 x_return_status OUT NOCOPY VARCHAR2
1370 ) IS
1371
1372 d_api_name CONSTANT VARCHAR2(30) := 'complete_resp_to_changes';
1373 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1374 d_position NUMBER;
1375
1376 l_itemKey WF_ITEMS.item_key%TYPE;
1377 l_activity VARCHAR2(200);
1378 l_activity_result VARCHAR2(30);
1379
1380 BEGIN
1381 d_position := 0;
1382
1383 x_return_status := FND_API.G_RET_STS_SUCCESS;
1384
1385 IF (PO_LOG.d_proc) THEN
1386 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
1387 END IF;
1388
1389 l_itemKey := find_ItemKey
1390 ( p_draft_id => p_draft_id
1391 );
1392
1393 d_position := 10;
1394
1395 IF (l_itemKey IS NOT NULL) THEN
1396
1397 -- release the activity that is blocked since the user has responded to
1398 -- the draft changes
1399
1400 l_activity := 'BUYER_NOTIFICATIONS:WAIT_FOR_RESP_TO_CHG';
1401
1402 IF (p_action = PO_DRAFTS_PVT.g_ACCEPT_ALL) THEN
1403 l_activity_result := 'ACCEPT';
1404 ELSIF (p_action = PO_DRAFTS_PVT.g_REJECT_ALL) THEN
1405 l_activity_result := 'REJECT';
1406 ELSIF (p_action = PO_DRAFTS_PVT.g_LINE_DISP) THEN
1407 l_activity_result := 'SUBMIT_LINE_DISP';
1408 END IF;
1409
1410 d_position := 20;
1411
1412 PO_WF_UTIL_PKG.SetItemAttrText
1413 ( itemtype => g_ITEMTYPE,
1414 itemkey => l_ItemKey,
1415 aname => 'NOTE_TO_APPROVER',
1416 avalue => SUBSTRB(p_note_to_approver, 1, 480)
1417 );
1418
1419 WF_ENGINE.CompleteActivity
1420 ( itemType => g_ITEMTYPE,
1421 itemKey => l_itemKey,
1422 activity => l_activity,
1423 result => l_activity_result
1424 );
1425
1426 END IF;
1427
1428 IF (PO_LOG.d_proc) THEN
1429 PO_LOG.proc_end (d_module);
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 PO_MESSAGE_S.add_exc_msg
1435 ( p_pkg_name => d_pkg_name,
1436 p_procedure_name => d_api_name || '.' || d_position
1437 );
1438
1439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1440 END complete_resp_to_changes;
1441
1442
1443 -----------------------------------------------------------------------
1444 --Start of Comments
1445 --Name: accept_lines_within_tolerance
1446 --Function:
1447 -- Check the lines in draft table and see if the price change has exceed
1448 -- tolerance. If so, return an indicator saying that manual acceptance is
1449 -- required. For those that do not exceed price tolerance, makr them as
1450 -- autoaccepted
1451 --Parameters:
1452 --IN:
1453 --p_draft_id
1454 -- draft unique identifier
1458 -- FND_API.G_TRUE if manual acceptance is required
1455 --IN OUT:
1456 --OUT:
1457 --Returns:
1459 -- FND_API.G_FALSE otherwise
1460 --Notes:
1461 --Testing:
1462 --End of Comments
1463 ------------------------------------------------------------------------
1464 FUNCTION accept_lines_within_tolerance
1465 ( p_draft_id IN NUMBER
1466 ) RETURN VARCHAR2
1467 IS
1468
1469 d_api_name CONSTANT VARCHAR2(30) := 'accept_lines_within_tolerance';
1470 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1471 d_position NUMBER;
1472
1473 l_index NUMBER;
1474
1475 l_manual_accept_required VARCHAR2(1) := FND_API.G_FALSE;
1476 l_need_to_check_tolerance VARCHAR2(1) := FND_API.G_FALSE;
1477 l_has_autoaccepted_line VARCHAR2(1) := FND_API.G_FALSE;
1478 l_over_tolerance VARCHAR2(1);
1479
1480 l_check_tolerance_index_tbl DBMS_SQL.NUMBER_TABLE;
1481
1482 l_autoaccept_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1483 l_vendor_id_tbl PO_TBL_NUMBER;
1484 l_po_line_id_tbl PO_TBL_NUMBER;
1485 l_po_header_id_tbl PO_TBL_NUMBER;
1486 l_item_id_tbl PO_TBL_NUMBER;
1487 l_category_id_tbl PO_TBL_NUMBER;
1488 l_new_price_tbl PO_TBL_NUMBER;
1489 l_old_price_tbl PO_TBL_NUMBER;
1490 l_new_line_flag_tbl PO_TBL_VARCHAR1;
1491 l_price_update_tolerance_tbl PO_TBL_NUMBER;
1492
1493 l_return_status VARCHAR2(1);
1494
1495 BEGIN
1496 d_position := 0;
1497
1498 IF (PO_LOG.d_proc) THEN
1499 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
1500 END IF;
1501
1502 -- bug5035979
1503 -- mark the records that do not have line level changes as
1504 -- 'accepted'
1505 accept_if_no_line_changes
1506 ( p_draft_id => p_draft_id
1507 );
1508
1509 SELECT PH.vendor_id,
1510 PLD.po_line_id,
1511 PLD.po_header_id,
1512 PLD.item_id,
1513 PLD.category_id,
1514 PLD.unit_price,
1515 PL.unit_price,
1516 NVL2(PL.po_line_id, 'N', 'Y')
1517 BULK COLLECT
1518 INTO l_vendor_id_tbl,
1519 l_po_line_id_tbl,
1520 l_po_header_id_tbl,
1521 l_item_id_tbl,
1522 l_category_id_tbl,
1523 l_new_price_tbl,
1524 l_old_price_tbl,
1525 l_new_line_flag_tbl
1526 FROM po_headers_all PH,
1527 po_lines_draft_all PLD,
1528 po_lines_all PL
1529 WHERE PLD.draft_id = p_draft_id
1530 AND NVL(PLD.delete_flag, 'N') = 'N'
1531 AND PLD.change_accepted_flag IS NULL
1532 AND PLD.po_line_id = PL.po_line_id(+)
1533 AND PLD.po_header_id = PH.po_header_id;
1534
1535 d_position := 10;
1536
1537 -- If there are any new lines, manual acceptance will be required by default
1538 -- Otherwise, we need to check price tolerance to see if manual acceptance
1539 -- is necessary
1540 FOR i IN 1..l_po_line_id_tbl.COUNT LOOP
1541 IF (l_new_line_flag_tbl(i) = 'Y') THEN
1542 l_manual_accept_required := FND_API.G_TRUE;
1543 ELSE
1544 -- add to the table that indicates what rows we need to check
1545 -- for tolerance
1546 l_check_tolerance_index_tbl(i) := i;
1547 l_need_to_check_tolerance := FND_API.G_TRUE;
1548 END IF;
1549 END LOOP;
1550
1551 d_position := 20;
1552
1553 IF (l_need_to_check_tolerance = FND_API.G_TRUE) THEN
1554
1555 PO_PDOI_PRICE_TOLERANCE_PVT.get_price_tolerance
1556 ( p_index_tbl => l_check_tolerance_index_tbl,
1557 p_po_header_id_tbl => l_po_header_id_tbl,
1558 p_item_id_tbl => l_item_id_tbl,
1559 p_category_id_tbl => l_category_id_tbl,
1560 p_vendor_id_tbl => l_vendor_id_tbl,
1561 x_price_update_tolerance_tbl => l_price_update_tolerance_tbl
1562 );
1563
1567 l_index := l_check_tolerance_index_tbl.FIRST;
1564 d_position := 30;
1565
1566 -- now, check whether the lines have exceeded price tolerance
1568
1569 WHILE (l_index IS NOT NULL) LOOP
1570 d_position := 40;
1571
1572 l_over_tolerance :=
1573 PO_PDOI_PRICE_TOLERANCE_PVT.exceed_tolerance_check
1574 ( p_price_tolerance => l_price_update_tolerance_tbl(l_index),
1575 p_old_price => l_old_price_tbl(l_index),
1576 p_new_price => l_new_price_tbl(l_index)
1577 );
1578
1579 IF (l_over_tolerance = FND_API.G_FALSE) THEN
1580 -- need to autoaccept this line
1581 l_autoaccept_tbl.EXTEND;
1582 l_autoaccept_tbl(l_autoaccept_tbl.COUNT) := l_po_line_id_tbl(l_index);
1583
1584 l_has_autoaccepted_line := FND_API.G_TRUE;
1585 ELSE
1586 -- require manual acceptance, if there is a line that goes over
1587 -- price tolerance limit
1588 l_manual_accept_required := FND_API.G_TRUE;
1589 END IF;
1590
1591 l_index := l_check_tolerance_index_tbl.NEXT(l_index);
1592 END LOOP;
1593
1594 d_position := 50;
1595
1596 -- For the lines that do not exceed price tolerance, autoaccept them.
1597 IF (l_has_autoaccepted_line = FND_API.G_TRUE) THEN
1598 -- bug5035979
1599 -- cascade acceptance value to lower level as well.
1600 cascade_change_acceptance
1601 ( p_draft_id => p_draft_id,
1602 p_line_id_list => l_autoaccept_tbl,
1603 p_change_accepted_value => 'Y',
1604 x_return_status => l_return_status
1605 );
1606
1607 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1609 END IF;
1610 END IF;
1611
1612 END IF;
1613
1614 IF (PO_LOG.d_proc) THEN
1615 PO_LOG.proc_end (d_module);
1616 END IF;
1617
1618 RETURN l_manual_accept_required;
1619
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 PO_MESSAGE_S.add_exc_msg
1623 ( p_pkg_name => d_pkg_name,
1624 p_procedure_name => d_api_name || '.' || d_position
1625 );
1626
1627 RAISE;
1628 END accept_lines_within_tolerance;
1629
1630 --==========================================================================
1631 -- Private procedures
1632 --==========================================================================
1633
1634 -- bug5249414 START
1635 PROCEDURE accept_translations
1636 ( p_draft_id IN NUMBER
1637 ) IS
1638
1639 d_api_name CONSTANT VARCHAR2(30) := 'accept_translations';
1640 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1641 d_position NUMBER;
1642
1643 l_created_language PO_HEADERS_ALL.created_language%TYPE;
1644
1645 BEGIN
1646
1647 d_position := 0;
1648
1649 IF (PO_LOG.d_proc) THEN
1650 PO_LOG.proc_begin (d_module);
1651 END IF;
1652
1653 SELECT PH.created_language
1654 INTO l_created_language
1655 FROM po_drafts DFT,
1656 po_headers_all PH
1657 WHERE DFT.draft_id = p_draft_id
1658 AND PH.po_header_id = DFT.document_id;
1659
1660 UPDATE po_attribute_values_tlp_draft PAVTD
1661 SET change_accepted_flag = 'Y'
1662 WHERE change_accepted_flag IS NULL
1663 AND PAVTD.draft_id = p_draft_id
1664 AND PAVTD.language <> l_created_language;
1665
1666
1667 IF (PO_LOG.d_proc) THEN
1668 PO_LOG.proc_end (d_module, '# of records updated: ', SQL%ROWCOUNT);
1669 END IF;
1670
1671 EXCEPTION
1672 WHEN OTHERS THEN
1673 PO_MESSAGE_S.add_exc_msg
1674 ( p_pkg_name => d_pkg_name,
1675 p_procedure_name => d_api_name || '.' || d_position
1676 );
1677
1678 RAISE;
1679 END accept_translations;
1680
1681 -- bug5570989 START
1682 -----------------------------------------------------------------------
1683 --Start of Comments
1684 --Name: autoaccept_deleted_records
1685 --Function:
1686 -- If the draft record is marked for deletion, but the corresponding
1687 -- line doesn't exist in the txn table, autoaccept it because they
1688 -- won't show up in the diff summary report anyway
1689 --Parameters:
1690 --IN:
1691 --p_draft_id
1692 -- draft unique identifier
1693 --IN OUT:
1694 --OUT:
1695 --Returns:
1696 --Notes:
1697 --Testing:
1698 --End of Comments
1699 ------------------------------------------------------------------------
1700 PROCEDURE autoaccept_deleted_records
1701 ( p_draft_id IN NUMBER
1702 ) IS
1703
1704 d_api_name CONSTANT VARCHAR2(30) := 'autoaccept_deleted_records';
1705 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1706 d_position NUMBER;
1707
1708 l_has_record_to_accept VARCHAR2(1) := FND_API.G_TRUE;
1709
1710 BEGIN
1711
1712 d_position := 0;
1713
1714 IF (PO_LOG.d_proc) THEN
1715 PO_LOG.proc_begin (d_module);
1716 END IF;
1717
1718 -- Line Level
1719 UPDATE po_lines_draft_all PLD
1720 SET PLD.change_accepted_flag = 'Y'
1721 WHERE PLD.draft_id = p_draft_id
1722 AND PLD.change_accepted_flag IS NULL
1723 AND PLD.delete_flag = 'Y'
1724 AND NOT EXISTS
1725 ( SELECT 1
1726 FROM po_lines_all PL
1727 WHERE PLD.po_line_id = PL.po_line_id);
1728
1729 d_position := 10;
1733 SET PLLD.change_accepted_flag = 'Y'
1730
1731 -- Price Break Level
1732 UPDATE po_line_locations_draft_all PLLD
1734 WHERE PLLD.draft_id = p_draft_id
1735 AND PLLD.change_accepted_flag IS NULL
1736 AND PLLD.delete_flag = 'Y'
1737 AND NOT EXISTS
1738 ( SELECT 1
1739 FROM po_line_locations_all PLL
1740 WHERE PLLD.line_location_id = PLL.line_location_id);
1741
1742 d_position := 20;
1743
1744 -- Price Diff Level
1745 UPDATE po_price_diff_draft PPDD
1746 SET PPDD.change_accepted_flag = 'Y'
1747 WHERE PPDD.draft_id = p_draft_id
1748 AND PPDD.change_accepted_flag IS NULL
1749 AND PPDD.delete_flag = 'Y'
1750 AND NOT EXISTS
1751 ( SELECT 1
1752 FROM po_price_differentials PPD
1753 WHERE PPDD.price_differential_id = PPD.price_differential_id);
1754
1755 d_position := 30;
1756
1757 -- Attr Values Level
1758 UPDATE po_attribute_values_draft PAVD
1759 SET PAVD.change_accepted_flag = 'Y'
1760 WHERE PAVD.draft_id = p_draft_id
1761 AND PAVD.change_accepted_flag IS NULL
1762 AND PAVD.delete_flag = 'Y'
1763 AND NOT EXISTS
1764 ( SELECT 1
1765 FROM po_attribute_values PAV
1766 WHERE PAVD.attribute_values_id = PAV.attribute_values_id);
1767
1768 d_position := 40;
1769
1770 -- Attr Values TLP Level
1771 UPDATE po_attribute_values_tlp_draft PAVTD
1772 SET PAVTD.change_accepted_flag = 'Y'
1773 WHERE PAVTD.draft_id = p_draft_id
1774 AND PAVTD.change_accepted_flag IS NULL
1775 AND PAVTD.delete_flag = 'Y'
1776 AND NOT EXISTS
1777 ( SELECT 1
1778 FROM po_attribute_values_tlp PAVT
1779 WHERE PAVTD.attribute_values_tlp_id = PAVT.attribute_values_tlp_id);
1780
1781 IF (PO_LOG.d_proc) THEN
1782 PO_LOG.proc_end (d_module);
1783 END IF;
1784
1785 EXCEPTION
1786 WHEN OTHERS THEN
1787 PO_MESSAGE_S.add_exc_msg
1788 ( p_pkg_name => d_pkg_name,
1789 p_procedure_name => d_api_name || '.' || d_position
1790 );
1791
1792 RAISE;
1793 END autoaccept_deleted_records;
1794
1795
1796
1797 -- bug5570989 END
1798
1799 -----------------------------------------------------------------------
1800 --Start of Comments
1801 --Name: autoaccept_unchanged_records
1802 --Function:
1803 -- For records that are not changed or have only the non-diffsummary-supported
1804 -- attributes changed, autoaccept them
1805 --Parameters:
1806 --IN:
1807 --p_draft_id
1808 -- draft unique identifier
1809 --IN OUT:
1810 --OUT:
1811 --Returns:
1812 -- FND_API.G_TRUE if there's still record in the draft to accept
1813 -- FND_API.G_FALSE otherwise
1814 --Notes:
1815 --Testing:
1816 --End of Comments
1817 ------------------------------------------------------------------------
1818 FUNCTION autoaccept_unchanged_records
1819 ( p_draft_id IN NUMBER
1820 ) RETURN VARCHAR2
1821 IS
1822
1823 d_api_name CONSTANT VARCHAR2(30) := 'autoaccept_unchanged_records';
1824 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
1825 d_position NUMBER;
1826
1827 l_has_record_to_accept VARCHAR2(1) := FND_API.G_TRUE;
1828
1829 BEGIN
1830
1831 d_position := 0;
1832
1833 IF (PO_LOG.d_proc) THEN
1834 PO_LOG.proc_begin (d_module);
1835 END IF;
1836
1837 -- Line Level Attributes
1838 UPDATE po_lines_draft_all PLD
1839 SET PLD.change_accepted_flag = 'Y'
1840 WHERE PLD.draft_id = p_draft_id
1841 AND PLD.change_accepted_flag IS NULL
1842 AND NVL(PLD.delete_flag, 'N') <> 'Y'
1843 AND EXISTS
1844 ( SELECT 1
1845 FROM po_lines_all PL
1846 WHERE PLD.po_line_id = PL.po_line_id
1847 AND DECODE (PLD.line_num,
1848 PL.line_num, 'Y', 'N') = 'Y'
1849 AND DECODE (PLD.line_type_id,
1850 PL.line_type_id, 'Y', 'N') = 'Y'
1851 AND DECODE (PLD.item_id,
1852 PL.item_id, 'Y', 'N') = 'Y'
1853 AND DECODE (PLD.job_id,
1854 PL.job_id, 'Y', 'N') = 'Y'
1855 AND DECODE (PLD.item_description,
1856 PL.item_description, 'Y', 'N') = 'Y'
1857 AND DECODE (PLD.category_id,
1858 PL.category_id, 'Y', 'N') = 'Y'
1859 AND DECODE (PLD.unit_meas_lookup_code,
1860 PL.unit_meas_lookup_code, 'Y', 'N') = 'Y'
1861 AND DECODE (PLD.unit_price,
1862 PL.unit_price, 'Y', 'N') = 'Y'
1863 AND DECODE (PLD.amount,
1864 PL.amount, 'Y', 'N') = 'Y'
1865 AND DECODE (PLD.expiration_date,
1866 PL.expiration_date, 'Y', 'N') = 'Y'
1867 AND DECODE (PLD.item_revision,
1868 PL.item_revision, 'Y', 'N') = 'Y'
1869 AND DECODE (PLD.vendor_product_num,
1870 PL.vendor_product_num, 'Y', 'N') = 'Y'
1871 AND DECODE (PLD.supplier_ref_number,
1872 PL.supplier_ref_number, 'Y', 'N') = 'Y'
1873 AND DECODE (PLD.ip_category_id,
1874 PL.ip_category_id, 'Y', 'N') = 'Y'
1878 NVL(PL.allow_price_override_flag, 'N'), 'Y', 'N') = 'Y'
1875 AND DECODE (NVL(PLD.capital_expense_flag, 'N'),
1876 NVL(PL.capital_expense_flag, 'N'), 'Y', 'N') = 'Y'
1877 AND DECODE (NVL(PLD.allow_price_override_flag, 'N'),
1879 AND DECODE (PLD.not_to_exceed_price,
1880 PL.not_to_exceed_price, 'Y', 'N') = 'Y'
1881 AND DECODE (PLD.list_price_per_unit,
1882 PL.list_price_per_unit, 'Y', 'N') = 'Y'
1883 AND DECODE (PLD.market_price,
1884 PL.market_price, 'Y', 'N') = 'Y'
1885 AND DECODE (PLD.price_type_lookup_code,
1886 PL.price_type_lookup_code, 'Y', 'N') = 'Y'
1887 AND DECODE (NVL(PLD.negotiated_by_preparer_flag, 'N'),
1888 NVL(PL.negotiated_by_preparer_flag, 'N'), 'Y', 'N') = 'Y'
1889 AND DECODE (PLD.min_release_amount,
1890 PL.min_release_amount, 'Y', 'N') = 'Y'
1891 AND DECODE (PLD.committed_amount,
1892 PL.committed_amount, 'Y', 'N') = 'Y'
1893 AND DECODE (PLD.quantity_committed,
1894 PL.quantity_committed, 'Y', 'N') = 'Y'
1895 AND DECODE (PLD.supplier_part_auxid,
1896 PL.supplier_part_auxid, 'Y', 'N') = 'Y'
1897 AND DECODE (PLD.un_number_id,
1898 PL.un_number_id, 'Y', 'N') = 'Y'
1899 AND DECODE (PLD.hazard_class_id,
1900 PL.hazard_class_id, 'Y', 'N') = 'Y'
1901 AND DECODE (PLD.note_to_vendor,
1902 PL.note_to_vendor, 'Y', 'N') = 'Y');
1903
1904 IF (PO_LOG.d_stmt) THEN
1905 PO_LOG.stmt (d_module, d_position, '# of lines autoaccepted', SQL%ROWCOUNT);
1906 END IF;
1907
1908 d_position := 10;
1909
1910 -- Price Break attributes
1911 UPDATE po_line_locations_draft_all PLLD
1912 SET PLLD.change_accepted_flag = 'Y'
1913 WHERE PLLD.draft_id = p_draft_id
1914 AND PLLD.change_accepted_flag IS NULL
1915 AND NVL(PLLD.delete_flag, 'N') <> 'Y'
1916 AND EXISTS
1917 ( SELECT 1
1918 FROM po_line_locations_all PLL
1919 WHERE PLLD.line_location_id = PLL.line_location_id
1920 AND DECODE (PLLD.ship_to_organization_id,
1921 PLL.ship_to_organization_id, 'Y', 'N') = 'Y'
1922 AND DECODE (PLLD.ship_to_location_id,
1923 PLL.ship_to_location_id, 'Y', 'N') = 'Y'
1924 AND DECODE (PLLD.quantity,
1925 PLL.quantity, 'Y', 'N') = 'Y'
1926 AND DECODE (PLLD.price_override,
1927 PLL.price_override, 'Y', 'N') = 'Y'
1928 AND DECODE (PLLD.price_discount,
1929 PLL.price_discount, 'Y', 'N') = 'Y'
1930 AND DECODE (PLLD.start_date,
1931 PLL.start_date, 'Y', 'N') = 'Y'
1932 AND DECODE (PLLD.end_date,
1933 PLL.end_date, 'Y', 'N') = 'Y');
1934
1935 IF (PO_LOG.d_stmt) THEN
1936 PO_LOG.stmt (d_module, d_position, '# of price breaks autoaccepted', SQL%ROWCOUNT);
1937 END IF;
1938
1939 d_position := 20;
1940
1941 -- Pirce Differentials Attributes
1942 UPDATE po_price_diff_draft PPDD
1943 SET PPDD.change_accepted_flag = 'Y'
1944 WHERE PPDD.draft_id = p_draft_id
1945 AND PPDD.change_accepted_flag IS NULL
1946 AND NVL(PPDD.delete_flag, 'N') <> 'Y'
1947 AND EXISTS
1948 ( SELECT 1
1949 FROM po_price_differentials PPD
1950 WHERE PPDD.price_differential_id = PPD.price_differential_id
1951 AND DECODE (PPDD.price_differential_num,
1952 PPD.price_differential_num, 'Y', 'N') = 'Y'
1953 AND DECODE (PPDD.price_type,
1954 PPD.price_type, 'Y', 'N') = 'Y'
1955 AND DECODE (PPDD.min_multiplier,
1956 PPD.min_multiplier, 'Y', 'N') = 'Y'
1957 AND DECODE (PPDD.max_multiplier,
1958 PPD.max_multiplier, 'Y', 'N') = 'Y');
1959
1960 IF (PO_LOG.d_stmt) THEN
1961 PO_LOG.stmt (d_module, d_position, '# of price diff autoaccepted', SQL%ROWCOUNT);
1962 END IF;
1963
1964 d_position := 30;
1965
1966 -- Attribute Values
1967 UPDATE po_attribute_values_draft PAVD
1968 SET PAVD.change_accepted_flag = 'Y'
1969 WHERE PAVD.draft_id = p_draft_id
1970 AND PAVD.change_accepted_flag IS NULL
1971 AND NVL(PAVD.delete_flag, 'N') <> 'Y'
1972 AND EXISTS
1973 ( SELECT 1
1974 FROM po_attribute_values PAV
1975 WHERE PAVD.attribute_values_id = PAV.attribute_values_id
1976 AND DECODE (PAVD.manufacturer_part_num ,
1977 PAV.manufacturer_part_num , 'Y', 'N') = 'Y'
1978 AND DECODE (PAVD.picture,
1979 PAV.picture, 'Y', 'N') = 'Y'
1980 AND DECODE (PAVD.thumbnail_image,
1981 PAV.thumbnail_image, 'Y', 'N') = 'Y'
1982 AND DECODE (PAVD.supplier_url,
1983 PAV.supplier_url, 'Y', 'N') = 'Y'
1984 AND DECODE (PAVD.manufacturer_url,
1985 PAV.manufacturer_url, 'Y', 'N') = 'Y'
1986 AND DECODE (PAVD.attachment_url,
1987 PAV.attachment_url, 'Y', 'N') = 'Y'
1991 PAV.availability, 'Y', 'N') = 'Y'
1988 AND DECODE (PAVD.unspsc,
1989 PAV.unspsc, 'Y', 'N') = 'Y'
1990 AND DECODE (PAVD.availability,
1992 AND DECODE (PAVD.lead_time,
1993 PAV.lead_time, 'Y', 'N') = 'Y'
1994 AND DECODE (PAVD.text_base_attribute1,
1995 PAV.text_base_attribute1, 'Y', 'N') = 'Y'
1996 AND DECODE (PAVD.text_base_attribute2,
1997 PAV.text_base_attribute2, 'Y', 'N') = 'Y'
1998 AND DECODE (PAVD.text_base_attribute3,
1999 PAV.text_base_attribute3, 'Y', 'N') = 'Y'
2000 AND DECODE (PAVD.text_base_attribute4,
2001 PAV.text_base_attribute4, 'Y', 'N') = 'Y'
2002 AND DECODE (PAVD.text_base_attribute5,
2003 PAV.text_base_attribute5, 'Y', 'N') = 'Y'
2004 AND DECODE (PAVD.text_base_attribute6,
2005 PAV.text_base_attribute6, 'Y', 'N') = 'Y'
2006 AND DECODE (PAVD.text_base_attribute7,
2007 PAV.text_base_attribute7, 'Y', 'N') = 'Y'
2008 AND DECODE (PAVD.text_base_attribute8,
2009 PAV.text_base_attribute8, 'Y', 'N') = 'Y'
2010 AND DECODE (PAVD.text_base_attribute9,
2011 PAV.text_base_attribute9, 'Y', 'N') = 'Y'
2012 AND DECODE (PAVD.text_base_attribute10,
2013 PAV.text_base_attribute10, 'Y', 'N') = 'Y'
2014 AND DECODE (PAVD.text_base_attribute11,
2015 PAV.text_base_attribute11, 'Y', 'N') = 'Y'
2016 AND DECODE (PAVD.text_base_attribute12,
2017 PAV.text_base_attribute12, 'Y', 'N') = 'Y'
2018 AND DECODE (PAVD.text_base_attribute13,
2019 PAV.text_base_attribute13, 'Y', 'N') = 'Y'
2020 AND DECODE (PAVD.text_base_attribute14,
2021 PAV.text_base_attribute14, 'Y', 'N') = 'Y'
2022 AND DECODE (PAVD.text_base_attribute15,
2023 PAV.text_base_attribute15, 'Y', 'N') = 'Y'
2024 AND DECODE (PAVD.text_base_attribute16,
2025 PAV.text_base_attribute16, 'Y', 'N') = 'Y'
2026 AND DECODE (PAVD.text_base_attribute17,
2027 PAV.text_base_attribute17, 'Y', 'N') = 'Y'
2028 AND DECODE (PAVD.text_base_attribute18,
2029 PAV.text_base_attribute18, 'Y', 'N') = 'Y'
2030 AND DECODE (PAVD.text_base_attribute19,
2031 PAV.text_base_attribute19, 'Y', 'N') = 'Y'
2032 AND DECODE (PAVD.text_base_attribute20,
2033 PAV.text_base_attribute20, 'Y', 'N') = 'Y'
2034 AND DECODE (PAVD.text_base_attribute21,
2035 PAV.text_base_attribute21, 'Y', 'N') = 'Y'
2036 AND DECODE (PAVD.text_base_attribute22,
2037 PAV.text_base_attribute22, 'Y', 'N') = 'Y'
2038 AND DECODE (PAVD.text_base_attribute23,
2039 PAV.text_base_attribute23, 'Y', 'N') = 'Y'
2040 AND DECODE (PAVD.text_base_attribute24,
2041 PAV.text_base_attribute24, 'Y', 'N') = 'Y'
2042 AND DECODE (PAVD.text_base_attribute25,
2043 PAV.text_base_attribute25, 'Y', 'N') = 'Y'
2044 AND DECODE (PAVD.text_base_attribute26,
2045 PAV.text_base_attribute26, 'Y', 'N') = 'Y'
2046 AND DECODE (PAVD.text_base_attribute27,
2047 PAV.text_base_attribute27, 'Y', 'N') = 'Y'
2048 AND DECODE (PAVD.text_base_attribute28,
2049 PAV.text_base_attribute28, 'Y', 'N') = 'Y'
2050 AND DECODE (PAVD.text_base_attribute29,
2051 PAV.text_base_attribute29, 'Y', 'N') = 'Y'
2052 AND DECODE (PAVD.text_base_attribute30,
2053 PAV.text_base_attribute30, 'Y', 'N') = 'Y'
2054 AND DECODE (PAVD.text_base_attribute31,
2055 PAV.text_base_attribute31, 'Y', 'N') = 'Y'
2056 AND DECODE (PAVD.text_base_attribute32,
2057 PAV.text_base_attribute32, 'Y', 'N') = 'Y'
2058 AND DECODE (PAVD.text_base_attribute33,
2059 PAV.text_base_attribute33, 'Y', 'N') = 'Y'
2060 AND DECODE (PAVD.text_base_attribute34,
2061 PAV.text_base_attribute34, 'Y', 'N') = 'Y'
2062 AND DECODE (PAVD.text_base_attribute35,
2063 PAV.text_base_attribute35, 'Y', 'N') = 'Y'
2064 AND DECODE (PAVD.text_base_attribute36,
2065 PAV.text_base_attribute36, 'Y', 'N') = 'Y'
2066 AND DECODE (PAVD.text_base_attribute37,
2067 PAV.text_base_attribute37, 'Y', 'N') = 'Y'
2068 AND DECODE (PAVD.text_base_attribute38,
2069 PAV.text_base_attribute38, 'Y', 'N') = 'Y'
2070 AND DECODE (PAVD.text_base_attribute39,
2071 PAV.text_base_attribute39, 'Y', 'N') = 'Y'
2072 AND DECODE (PAVD.text_base_attribute40,
2073 PAV.text_base_attribute40, 'Y', 'N') = 'Y'
2074 AND DECODE (PAVD.text_base_attribute41,
2078 AND DECODE (PAVD.text_base_attribute43,
2075 PAV.text_base_attribute41, 'Y', 'N') = 'Y'
2076 AND DECODE (PAVD.text_base_attribute42,
2077 PAV.text_base_attribute42, 'Y', 'N') = 'Y'
2079 PAV.text_base_attribute43, 'Y', 'N') = 'Y'
2080 AND DECODE (PAVD.text_base_attribute44,
2081 PAV.text_base_attribute44, 'Y', 'N') = 'Y'
2082 AND DECODE (PAVD.text_base_attribute45,
2083 PAV.text_base_attribute45, 'Y', 'N') = 'Y'
2084 AND DECODE (PAVD.text_base_attribute46,
2085 PAV.text_base_attribute46, 'Y', 'N') = 'Y'
2086 AND DECODE (PAVD.text_base_attribute47,
2087 PAV.text_base_attribute47, 'Y', 'N') = 'Y'
2088 AND DECODE (PAVD.text_base_attribute48,
2089 PAV.text_base_attribute48, 'Y', 'N') = 'Y'
2090 AND DECODE (PAVD.text_base_attribute49,
2091 PAV.text_base_attribute49, 'Y', 'N') = 'Y'
2092 AND DECODE (PAVD.text_base_attribute50,
2093 PAV.text_base_attribute50, 'Y', 'N') = 'Y'
2094 AND DECODE (PAVD.text_base_attribute51,
2095 PAV.text_base_attribute51, 'Y', 'N') = 'Y'
2096 AND DECODE (PAVD.text_base_attribute52,
2097 PAV.text_base_attribute52, 'Y', 'N') = 'Y'
2098 AND DECODE (PAVD.text_base_attribute53,
2099 PAV.text_base_attribute53, 'Y', 'N') = 'Y'
2100 AND DECODE (PAVD.text_base_attribute54,
2101 PAV.text_base_attribute54, 'Y', 'N') = 'Y'
2102 AND DECODE (PAVD.text_base_attribute55,
2103 PAV.text_base_attribute55, 'Y', 'N') = 'Y'
2104 AND DECODE (PAVD.text_base_attribute56,
2105 PAV.text_base_attribute56, 'Y', 'N') = 'Y'
2106 AND DECODE (PAVD.text_base_attribute57,
2107 PAV.text_base_attribute57, 'Y', 'N') = 'Y'
2108 AND DECODE (PAVD.text_base_attribute58,
2109 PAV.text_base_attribute58, 'Y', 'N') = 'Y'
2110 AND DECODE (PAVD.text_base_attribute59,
2111 PAV.text_base_attribute59, 'Y', 'N') = 'Y'
2112 AND DECODE (PAVD.text_base_attribute60,
2113 PAV.text_base_attribute60, 'Y', 'N') = 'Y'
2114 AND DECODE (PAVD.text_base_attribute61,
2115 PAV.text_base_attribute61, 'Y', 'N') = 'Y'
2116 AND DECODE (PAVD.text_base_attribute62,
2117 PAV.text_base_attribute62, 'Y', 'N') = 'Y'
2118 AND DECODE (PAVD.text_base_attribute63,
2119 PAV.text_base_attribute63, 'Y', 'N') = 'Y'
2120 AND DECODE (PAVD.text_base_attribute64,
2121 PAV.text_base_attribute64, 'Y', 'N') = 'Y'
2122 AND DECODE (PAVD.text_base_attribute65,
2123 PAV.text_base_attribute65, 'Y', 'N') = 'Y'
2124 AND DECODE (PAVD.text_base_attribute66,
2125 PAV.text_base_attribute66, 'Y', 'N') = 'Y'
2126 AND DECODE (PAVD.text_base_attribute67,
2127 PAV.text_base_attribute67, 'Y', 'N') = 'Y'
2128 AND DECODE (PAVD.text_base_attribute68,
2129 PAV.text_base_attribute68, 'Y', 'N') = 'Y'
2130 AND DECODE (PAVD.text_base_attribute69,
2131 PAV.text_base_attribute69, 'Y', 'N') = 'Y'
2132 AND DECODE (PAVD.text_base_attribute70,
2133 PAV.text_base_attribute70, 'Y', 'N') = 'Y'
2134 AND DECODE (PAVD.text_base_attribute71,
2135 PAV.text_base_attribute71, 'Y', 'N') = 'Y'
2136 AND DECODE (PAVD.text_base_attribute72,
2137 PAV.text_base_attribute72, 'Y', 'N') = 'Y'
2138 AND DECODE (PAVD.text_base_attribute73,
2139 PAV.text_base_attribute73, 'Y', 'N') = 'Y'
2140 AND DECODE (PAVD.text_base_attribute74,
2141 PAV.text_base_attribute74, 'Y', 'N') = 'Y'
2142 AND DECODE (PAVD.text_base_attribute75,
2143 PAV.text_base_attribute75, 'Y', 'N') = 'Y'
2144 AND DECODE (PAVD.text_base_attribute76,
2145 PAV.text_base_attribute76, 'Y', 'N') = 'Y'
2146 AND DECODE (PAVD.text_base_attribute77,
2147 PAV.text_base_attribute77, 'Y', 'N') = 'Y'
2148 AND DECODE (PAVD.text_base_attribute78,
2149 PAV.text_base_attribute78, 'Y', 'N') = 'Y'
2150 AND DECODE (PAVD.text_base_attribute79,
2151 PAV.text_base_attribute79, 'Y', 'N') = 'Y'
2152 AND DECODE (PAVD.text_base_attribute80,
2153 PAV.text_base_attribute80, 'Y', 'N') = 'Y'
2154 AND DECODE (PAVD.text_base_attribute81,
2155 PAV.text_base_attribute81, 'Y', 'N') = 'Y'
2156 AND DECODE (PAVD.text_base_attribute82,
2157 PAV.text_base_attribute82, 'Y', 'N') = 'Y'
2158 AND DECODE (PAVD.text_base_attribute83,
2159 PAV.text_base_attribute83, 'Y', 'N') = 'Y'
2163 PAV.text_base_attribute85, 'Y', 'N') = 'Y'
2160 AND DECODE (PAVD.text_base_attribute84,
2161 PAV.text_base_attribute84, 'Y', 'N') = 'Y'
2162 AND DECODE (PAVD.text_base_attribute85,
2164 AND DECODE (PAVD.text_base_attribute86,
2165 PAV.text_base_attribute86, 'Y', 'N') = 'Y'
2166 AND DECODE (PAVD.text_base_attribute87,
2167 PAV.text_base_attribute87, 'Y', 'N') = 'Y'
2168 AND DECODE (PAVD.text_base_attribute88,
2169 PAV.text_base_attribute88, 'Y', 'N') = 'Y'
2170 AND DECODE (PAVD.text_base_attribute89,
2171 PAV.text_base_attribute89, 'Y', 'N') = 'Y'
2172 AND DECODE (PAVD.text_base_attribute90,
2173 PAV.text_base_attribute90, 'Y', 'N') = 'Y'
2174 AND DECODE (PAVD.text_base_attribute91,
2175 PAV.text_base_attribute91, 'Y', 'N') = 'Y'
2176 AND DECODE (PAVD.text_base_attribute92,
2177 PAV.text_base_attribute92, 'Y', 'N') = 'Y'
2178 AND DECODE (PAVD.text_base_attribute93,
2179 PAV.text_base_attribute93, 'Y', 'N') = 'Y'
2180 AND DECODE (PAVD.text_base_attribute94,
2181 PAV.text_base_attribute94, 'Y', 'N') = 'Y'
2182 AND DECODE (PAVD.text_base_attribute95,
2183 PAV.text_base_attribute95, 'Y', 'N') = 'Y'
2184 AND DECODE (PAVD.text_base_attribute96,
2185 PAV.text_base_attribute96, 'Y', 'N') = 'Y'
2186 AND DECODE (PAVD.text_base_attribute97,
2187 PAV.text_base_attribute97, 'Y', 'N') = 'Y'
2188 AND DECODE (PAVD.text_base_attribute98,
2189 PAV.text_base_attribute98, 'Y', 'N') = 'Y'
2190 AND DECODE (PAVD.text_base_attribute99,
2191 PAV.text_base_attribute99, 'Y', 'N') = 'Y'
2192 AND DECODE (PAVD.text_base_attribute100,
2193 PAV.text_base_attribute100, 'Y', 'N') = 'Y'
2194 AND DECODE (PAVD.num_base_attribute1,
2195 PAV.num_base_attribute1, 'Y', 'N') = 'Y'
2196 AND DECODE (PAVD.num_base_attribute2,
2197 PAV.num_base_attribute2, 'Y', 'N') = 'Y'
2198 AND DECODE (PAVD.num_base_attribute3,
2199 PAV.num_base_attribute3, 'Y', 'N') = 'Y'
2200 AND DECODE (PAVD.num_base_attribute4,
2201 PAV.num_base_attribute4, 'Y', 'N') = 'Y'
2202 AND DECODE (PAVD.num_base_attribute5,
2203 PAV.num_base_attribute5, 'Y', 'N') = 'Y'
2204 AND DECODE (PAVD.num_base_attribute6,
2205 PAV.num_base_attribute6, 'Y', 'N') = 'Y'
2206 AND DECODE (PAVD.num_base_attribute7,
2207 PAV.num_base_attribute7, 'Y', 'N') = 'Y'
2208 AND DECODE (PAVD.num_base_attribute8,
2209 PAV.num_base_attribute8, 'Y', 'N') = 'Y'
2210 AND DECODE (PAVD.num_base_attribute9,
2211 PAV.num_base_attribute9, 'Y', 'N') = 'Y'
2212 AND DECODE (PAVD.num_base_attribute10,
2213 PAV.num_base_attribute10, 'Y', 'N') = 'Y'
2214 AND DECODE (PAVD.num_base_attribute11,
2215 PAV.num_base_attribute11, 'Y', 'N') = 'Y'
2216 AND DECODE (PAVD.num_base_attribute12,
2217 PAV.num_base_attribute12, 'Y', 'N') = 'Y'
2218 AND DECODE (PAVD.num_base_attribute13,
2219 PAV.num_base_attribute13, 'Y', 'N') = 'Y'
2220 AND DECODE (PAVD.num_base_attribute14,
2221 PAV.num_base_attribute14, 'Y', 'N') = 'Y'
2222 AND DECODE (PAVD.num_base_attribute15,
2223 PAV.num_base_attribute15, 'Y', 'N') = 'Y'
2224 AND DECODE (PAVD.num_base_attribute16,
2225 PAV.num_base_attribute16, 'Y', 'N') = 'Y'
2226 AND DECODE (PAVD.num_base_attribute17,
2227 PAV.num_base_attribute17, 'Y', 'N') = 'Y'
2228 AND DECODE (PAVD.num_base_attribute18,
2229 PAV.num_base_attribute18, 'Y', 'N') = 'Y'
2230 AND DECODE (PAVD.num_base_attribute19,
2231 PAV.num_base_attribute19, 'Y', 'N') = 'Y'
2232 AND DECODE (PAVD.num_base_attribute20,
2233 PAV.num_base_attribute20, 'Y', 'N') = 'Y'
2234 AND DECODE (PAVD.num_base_attribute21,
2235 PAV.num_base_attribute21, 'Y', 'N') = 'Y'
2236 AND DECODE (PAVD.num_base_attribute22,
2237 PAV.num_base_attribute22, 'Y', 'N') = 'Y'
2238 AND DECODE (PAVD.num_base_attribute23,
2239 PAV.num_base_attribute23, 'Y', 'N') = 'Y'
2240 AND DECODE (PAVD.num_base_attribute24,
2241 PAV.num_base_attribute24, 'Y', 'N') = 'Y'
2242 AND DECODE (PAVD.num_base_attribute25,
2243 PAV.num_base_attribute25, 'Y', 'N') = 'Y'
2247 PAV.num_base_attribute27, 'Y', 'N') = 'Y'
2244 AND DECODE (PAVD.num_base_attribute26,
2245 PAV.num_base_attribute26, 'Y', 'N') = 'Y'
2246 AND DECODE (PAVD.num_base_attribute27,
2248 AND DECODE (PAVD.num_base_attribute28,
2249 PAV.num_base_attribute28, 'Y', 'N') = 'Y'
2250 AND DECODE (PAVD.num_base_attribute29,
2251 PAV.num_base_attribute29, 'Y', 'N') = 'Y'
2252 AND DECODE (PAVD.num_base_attribute30,
2253 PAV.num_base_attribute30, 'Y', 'N') = 'Y'
2254 AND DECODE (PAVD.num_base_attribute31,
2255 PAV.num_base_attribute31, 'Y', 'N') = 'Y'
2256 AND DECODE (PAVD.num_base_attribute32,
2257 PAV.num_base_attribute32, 'Y', 'N') = 'Y'
2258 AND DECODE (PAVD.num_base_attribute33,
2259 PAV.num_base_attribute33, 'Y', 'N') = 'Y'
2260 AND DECODE (PAVD.num_base_attribute34,
2261 PAV.num_base_attribute34, 'Y', 'N') = 'Y'
2262 AND DECODE (PAVD.num_base_attribute35,
2263 PAV.num_base_attribute35, 'Y', 'N') = 'Y'
2264 AND DECODE (PAVD.num_base_attribute36,
2265 PAV.num_base_attribute36, 'Y', 'N') = 'Y'
2266 AND DECODE (PAVD.num_base_attribute37,
2267 PAV.num_base_attribute37, 'Y', 'N') = 'Y'
2268 AND DECODE (PAVD.num_base_attribute38,
2269 PAV.num_base_attribute38, 'Y', 'N') = 'Y'
2270 AND DECODE (PAVD.num_base_attribute39,
2271 PAV.num_base_attribute39, 'Y', 'N') = 'Y'
2272 AND DECODE (PAVD.num_base_attribute40,
2273 PAV.num_base_attribute40, 'Y', 'N') = 'Y'
2274 AND DECODE (PAVD.num_base_attribute41,
2275 PAV.num_base_attribute41, 'Y', 'N') = 'Y'
2276 AND DECODE (PAVD.num_base_attribute42,
2277 PAV.num_base_attribute42, 'Y', 'N') = 'Y'
2278 AND DECODE (PAVD.num_base_attribute43,
2279 PAV.num_base_attribute43, 'Y', 'N') = 'Y'
2280 AND DECODE (PAVD.num_base_attribute44,
2281 PAV.num_base_attribute44, 'Y', 'N') = 'Y'
2282 AND DECODE (PAVD.num_base_attribute45,
2283 PAV.num_base_attribute45, 'Y', 'N') = 'Y'
2284 AND DECODE (PAVD.num_base_attribute46,
2285 PAV.num_base_attribute46, 'Y', 'N') = 'Y'
2286 AND DECODE (PAVD.num_base_attribute47,
2287 PAV.num_base_attribute47, 'Y', 'N') = 'Y'
2288 AND DECODE (PAVD.num_base_attribute48,
2289 PAV.num_base_attribute48, 'Y', 'N') = 'Y'
2290 AND DECODE (PAVD.num_base_attribute49,
2291 PAV.num_base_attribute49, 'Y', 'N') = 'Y'
2292 AND DECODE (PAVD.num_base_attribute50,
2293 PAV.num_base_attribute50, 'Y', 'N') = 'Y'
2294 AND DECODE (PAVD.num_base_attribute51,
2295 PAV.num_base_attribute51, 'Y', 'N') = 'Y'
2296 AND DECODE (PAVD.num_base_attribute52,
2297 PAV.num_base_attribute52, 'Y', 'N') = 'Y'
2298 AND DECODE (PAVD.num_base_attribute53,
2299 PAV.num_base_attribute53, 'Y', 'N') = 'Y'
2300 AND DECODE (PAVD.num_base_attribute54,
2301 PAV.num_base_attribute54, 'Y', 'N') = 'Y'
2302 AND DECODE (PAVD.num_base_attribute55,
2303 PAV.num_base_attribute55, 'Y', 'N') = 'Y'
2304 AND DECODE (PAVD.num_base_attribute56,
2305 PAV.num_base_attribute56, 'Y', 'N') = 'Y'
2306 AND DECODE (PAVD.num_base_attribute57,
2307 PAV.num_base_attribute57, 'Y', 'N') = 'Y'
2308 AND DECODE (PAVD.num_base_attribute58,
2309 PAV.num_base_attribute58, 'Y', 'N') = 'Y'
2310 AND DECODE (PAVD.num_base_attribute59,
2311 PAV.num_base_attribute59, 'Y', 'N') = 'Y'
2312 AND DECODE (PAVD.num_base_attribute60,
2313 PAV.num_base_attribute60, 'Y', 'N') = 'Y'
2314 AND DECODE (PAVD.num_base_attribute61,
2315 PAV.num_base_attribute61, 'Y', 'N') = 'Y'
2316 AND DECODE (PAVD.num_base_attribute62,
2317 PAV.num_base_attribute62, 'Y', 'N') = 'Y'
2318 AND DECODE (PAVD.num_base_attribute63,
2319 PAV.num_base_attribute63, 'Y', 'N') = 'Y'
2320 AND DECODE (PAVD.num_base_attribute64,
2321 PAV.num_base_attribute64, 'Y', 'N') = 'Y'
2322 AND DECODE (PAVD.num_base_attribute65,
2323 PAV.num_base_attribute65, 'Y', 'N') = 'Y'
2324 AND DECODE (PAVD.num_base_attribute66,
2325 PAV.num_base_attribute66, 'Y', 'N') = 'Y'
2326 AND DECODE (PAVD.num_base_attribute67,
2327 PAV.num_base_attribute67, 'Y', 'N') = 'Y'
2331 PAV.num_base_attribute69, 'Y', 'N') = 'Y'
2328 AND DECODE (PAVD.num_base_attribute68,
2329 PAV.num_base_attribute68, 'Y', 'N') = 'Y'
2330 AND DECODE (PAVD.num_base_attribute69,
2332 AND DECODE (PAVD.num_base_attribute70,
2333 PAV.num_base_attribute70, 'Y', 'N') = 'Y'
2334 AND DECODE (PAVD.num_base_attribute71,
2335 PAV.num_base_attribute71, 'Y', 'N') = 'Y'
2336 AND DECODE (PAVD.num_base_attribute72,
2337 PAV.num_base_attribute72, 'Y', 'N') = 'Y'
2338 AND DECODE (PAVD.num_base_attribute73,
2339 PAV.num_base_attribute73, 'Y', 'N') = 'Y'
2340 AND DECODE (PAVD.num_base_attribute74,
2341 PAV.num_base_attribute74, 'Y', 'N') = 'Y'
2342 AND DECODE (PAVD.num_base_attribute75,
2343 PAV.num_base_attribute75, 'Y', 'N') = 'Y'
2344 AND DECODE (PAVD.num_base_attribute76,
2345 PAV.num_base_attribute76, 'Y', 'N') = 'Y'
2346 AND DECODE (PAVD.num_base_attribute77,
2347 PAV.num_base_attribute77, 'Y', 'N') = 'Y'
2348 AND DECODE (PAVD.num_base_attribute78,
2349 PAV.num_base_attribute78, 'Y', 'N') = 'Y'
2350 AND DECODE (PAVD.num_base_attribute79,
2351 PAV.num_base_attribute79, 'Y', 'N') = 'Y'
2352 AND DECODE (PAVD.num_base_attribute80,
2353 PAV.num_base_attribute80, 'Y', 'N') = 'Y'
2354 AND DECODE (PAVD.num_base_attribute81,
2355 PAV.num_base_attribute81, 'Y', 'N') = 'Y'
2356 AND DECODE (PAVD.num_base_attribute82,
2357 PAV.num_base_attribute82, 'Y', 'N') = 'Y'
2358 AND DECODE (PAVD.num_base_attribute83,
2359 PAV.num_base_attribute83, 'Y', 'N') = 'Y'
2360 AND DECODE (PAVD.num_base_attribute84,
2361 PAV.num_base_attribute84, 'Y', 'N') = 'Y'
2362 AND DECODE (PAVD.num_base_attribute85,
2363 PAV.num_base_attribute85, 'Y', 'N') = 'Y'
2364 AND DECODE (PAVD.num_base_attribute86,
2365 PAV.num_base_attribute86, 'Y', 'N') = 'Y'
2366 AND DECODE (PAVD.num_base_attribute87,
2367 PAV.num_base_attribute87, 'Y', 'N') = 'Y'
2368 AND DECODE (PAVD.num_base_attribute88,
2369 PAV.num_base_attribute88, 'Y', 'N') = 'Y'
2370 AND DECODE (PAVD.num_base_attribute89,
2371 PAV.num_base_attribute89, 'Y', 'N') = 'Y'
2372 AND DECODE (PAVD.num_base_attribute90,
2373 PAV.num_base_attribute90, 'Y', 'N') = 'Y'
2374 AND DECODE (PAVD.num_base_attribute91,
2375 PAV.num_base_attribute91, 'Y', 'N') = 'Y'
2376 AND DECODE (PAVD.num_base_attribute92,
2377 PAV.num_base_attribute92, 'Y', 'N') = 'Y'
2378 AND DECODE (PAVD.num_base_attribute93,
2379 PAV.num_base_attribute93, 'Y', 'N') = 'Y'
2380 AND DECODE (PAVD.num_base_attribute94,
2381 PAV.num_base_attribute94, 'Y', 'N') = 'Y'
2382 AND DECODE (PAVD.num_base_attribute95,
2383 PAV.num_base_attribute95, 'Y', 'N') = 'Y'
2384 AND DECODE (PAVD.num_base_attribute96,
2385 PAV.num_base_attribute96, 'Y', 'N') = 'Y'
2386 AND DECODE (PAVD.num_base_attribute97,
2387 PAV.num_base_attribute97, 'Y', 'N') = 'Y'
2388 AND DECODE (PAVD.num_base_attribute98,
2389 PAV.num_base_attribute98, 'Y', 'N') = 'Y'
2390 AND DECODE (PAVD.num_base_attribute99,
2391 PAV.num_base_attribute99, 'Y', 'N') = 'Y'
2392 AND DECODE (PAVD.num_base_attribute100,
2393 PAV.num_base_attribute100, 'Y', 'N') = 'Y'
2394 AND DECODE (PAVD.text_cat_attribute1,
2395 PAV.text_cat_attribute1, 'Y', 'N') = 'Y'
2396 AND DECODE (PAVD.text_cat_attribute2,
2397 PAV.text_cat_attribute2, 'Y', 'N') = 'Y'
2398 AND DECODE (PAVD.text_cat_attribute3,
2399 PAV.text_cat_attribute3, 'Y', 'N') = 'Y'
2400 AND DECODE (PAVD.text_cat_attribute4,
2401 PAV.text_cat_attribute4, 'Y', 'N') = 'Y'
2402 AND DECODE (PAVD.text_cat_attribute5,
2403 PAV.text_cat_attribute5, 'Y', 'N') = 'Y'
2404 AND DECODE (PAVD.text_cat_attribute6,
2405 PAV.text_cat_attribute6, 'Y', 'N') = 'Y'
2406 AND DECODE (PAVD.text_cat_attribute7,
2407 PAV.text_cat_attribute7, 'Y', 'N') = 'Y'
2408 AND DECODE (PAVD.text_cat_attribute8,
2409 PAV.text_cat_attribute8, 'Y', 'N') = 'Y'
2410 AND DECODE (PAVD.text_cat_attribute9,
2411 PAV.text_cat_attribute9, 'Y', 'N') = 'Y'
2412 AND DECODE (PAVD.text_cat_attribute10,
2416 AND DECODE (PAVD.text_cat_attribute12,
2413 PAV.text_cat_attribute10, 'Y', 'N') = 'Y'
2414 AND DECODE (PAVD.text_cat_attribute11,
2415 PAV.text_cat_attribute11, 'Y', 'N') = 'Y'
2417 PAV.text_cat_attribute12, 'Y', 'N') = 'Y'
2418 AND DECODE (PAVD.text_cat_attribute13,
2419 PAV.text_cat_attribute13, 'Y', 'N') = 'Y'
2420 AND DECODE (PAVD.text_cat_attribute14,
2421 PAV.text_cat_attribute14, 'Y', 'N') = 'Y'
2422 AND DECODE (PAVD.text_cat_attribute15,
2423 PAV.text_cat_attribute15, 'Y', 'N') = 'Y'
2424 AND DECODE (PAVD.text_cat_attribute16,
2425 PAV.text_cat_attribute16, 'Y', 'N') = 'Y'
2426 AND DECODE (PAVD.text_cat_attribute17,
2427 PAV.text_cat_attribute17, 'Y', 'N') = 'Y'
2428 AND DECODE (PAVD.text_cat_attribute18,
2429 PAV.text_cat_attribute18, 'Y', 'N') = 'Y'
2430 AND DECODE (PAVD.text_cat_attribute19,
2431 PAV.text_cat_attribute19, 'Y', 'N') = 'Y'
2432 AND DECODE (PAVD.text_cat_attribute20,
2433 PAV.text_cat_attribute20, 'Y', 'N') = 'Y'
2434 AND DECODE (PAVD.text_cat_attribute21,
2435 PAV.text_cat_attribute21, 'Y', 'N') = 'Y'
2436 AND DECODE (PAVD.text_cat_attribute22,
2437 PAV.text_cat_attribute22, 'Y', 'N') = 'Y'
2438 AND DECODE (PAVD.text_cat_attribute23,
2439 PAV.text_cat_attribute23, 'Y', 'N') = 'Y'
2440 AND DECODE (PAVD.text_cat_attribute24,
2441 PAV.text_cat_attribute24, 'Y', 'N') = 'Y'
2442 AND DECODE (PAVD.text_cat_attribute25,
2443 PAV.text_cat_attribute25, 'Y', 'N') = 'Y'
2444 AND DECODE (PAVD.text_cat_attribute26,
2445 PAV.text_cat_attribute26, 'Y', 'N') = 'Y'
2446 AND DECODE (PAVD.text_cat_attribute27,
2447 PAV.text_cat_attribute27, 'Y', 'N') = 'Y'
2448 AND DECODE (PAVD.text_cat_attribute28,
2449 PAV.text_cat_attribute28, 'Y', 'N') = 'Y'
2450 AND DECODE (PAVD.text_cat_attribute29,
2451 PAV.text_cat_attribute29, 'Y', 'N') = 'Y'
2452 AND DECODE (PAVD.text_cat_attribute30,
2453 PAV.text_cat_attribute30, 'Y', 'N') = 'Y'
2454 AND DECODE (PAVD.text_cat_attribute31,
2455 PAV.text_cat_attribute31, 'Y', 'N') = 'Y'
2456 AND DECODE (PAVD.text_cat_attribute32,
2457 PAV.text_cat_attribute32, 'Y', 'N') = 'Y'
2458 AND DECODE (PAVD.text_cat_attribute33,
2459 PAV.text_cat_attribute33, 'Y', 'N') = 'Y'
2460 AND DECODE (PAVD.text_cat_attribute34,
2461 PAV.text_cat_attribute34, 'Y', 'N') = 'Y'
2462 AND DECODE (PAVD.text_cat_attribute35,
2463 PAV.text_cat_attribute35, 'Y', 'N') = 'Y'
2464 AND DECODE (PAVD.text_cat_attribute36,
2465 PAV.text_cat_attribute36, 'Y', 'N') = 'Y'
2466 AND DECODE (PAVD.text_cat_attribute37,
2467 PAV.text_cat_attribute37, 'Y', 'N') = 'Y'
2468 AND DECODE (PAVD.text_cat_attribute38,
2469 PAV.text_cat_attribute38, 'Y', 'N') = 'Y'
2470 AND DECODE (PAVD.text_cat_attribute39,
2471 PAV.text_cat_attribute39, 'Y', 'N') = 'Y'
2472 AND DECODE (PAVD.text_cat_attribute40,
2473 PAV.text_cat_attribute40, 'Y', 'N') = 'Y'
2474 AND DECODE (PAVD.text_cat_attribute41,
2475 PAV.text_cat_attribute41, 'Y', 'N') = 'Y'
2476 AND DECODE (PAVD.text_cat_attribute42,
2477 PAV.text_cat_attribute42, 'Y', 'N') = 'Y'
2478 AND DECODE (PAVD.text_cat_attribute43,
2479 PAV.text_cat_attribute43, 'Y', 'N') = 'Y'
2480 AND DECODE (PAVD.text_cat_attribute44,
2481 PAV.text_cat_attribute44, 'Y', 'N') = 'Y'
2482 AND DECODE (PAVD.text_cat_attribute45,
2483 PAV.text_cat_attribute45, 'Y', 'N') = 'Y'
2484 AND DECODE (PAVD.text_cat_attribute46,
2485 PAV.text_cat_attribute46, 'Y', 'N') = 'Y'
2486 AND DECODE (PAVD.text_cat_attribute47,
2487 PAV.text_cat_attribute47, 'Y', 'N') = 'Y'
2488 AND DECODE (PAVD.text_cat_attribute48,
2489 PAV.text_cat_attribute48, 'Y', 'N') = 'Y'
2490 AND DECODE (PAVD.text_cat_attribute49,
2491 PAV.text_cat_attribute49, 'Y', 'N') = 'Y'
2492 AND DECODE (PAVD.text_cat_attribute50,
2493 PAV.text_cat_attribute50, 'Y', 'N') = 'Y'
2494 AND DECODE (PAVD.num_cat_attribute1,
2495 PAV.num_cat_attribute1, 'Y', 'N') = 'Y'
2496 AND DECODE (PAVD.num_cat_attribute2,
2497 PAV.num_cat_attribute2, 'Y', 'N') = 'Y'
2501 PAV.num_cat_attribute4, 'Y', 'N') = 'Y'
2498 AND DECODE (PAVD.num_cat_attribute3,
2499 PAV.num_cat_attribute3, 'Y', 'N') = 'Y'
2500 AND DECODE (PAVD.num_cat_attribute4,
2502 AND DECODE (PAVD.num_cat_attribute5,
2503 PAV.num_cat_attribute5, 'Y', 'N') = 'Y'
2504 AND DECODE (PAVD.num_cat_attribute6,
2505 PAV.num_cat_attribute6, 'Y', 'N') = 'Y'
2506 AND DECODE (PAVD.num_cat_attribute7,
2507 PAV.num_cat_attribute7, 'Y', 'N') = 'Y'
2508 AND DECODE (PAVD.num_cat_attribute8,
2509 PAV.num_cat_attribute8, 'Y', 'N') = 'Y'
2510 AND DECODE (PAVD.num_cat_attribute9,
2511 PAV.num_cat_attribute9, 'Y', 'N') = 'Y'
2512 AND DECODE (PAVD.num_cat_attribute10,
2513 PAV.num_cat_attribute10, 'Y', 'N') = 'Y'
2514 AND DECODE (PAVD.num_cat_attribute11,
2515 PAV.num_cat_attribute11, 'Y', 'N') = 'Y'
2516 AND DECODE (PAVD.num_cat_attribute12,
2517 PAV.num_cat_attribute12, 'Y', 'N') = 'Y'
2518 AND DECODE (PAVD.num_cat_attribute13,
2519 PAV.num_cat_attribute13, 'Y', 'N') = 'Y'
2520 AND DECODE (PAVD.num_cat_attribute14,
2521 PAV.num_cat_attribute14, 'Y', 'N') = 'Y'
2522 AND DECODE (PAVD.num_cat_attribute15,
2523 PAV.num_cat_attribute15, 'Y', 'N') = 'Y'
2524 AND DECODE (PAVD.num_cat_attribute16,
2525 PAV.num_cat_attribute16, 'Y', 'N') = 'Y'
2526 AND DECODE (PAVD.num_cat_attribute17,
2527 PAV.num_cat_attribute17, 'Y', 'N') = 'Y'
2528 AND DECODE (PAVD.num_cat_attribute18,
2529 PAV.num_cat_attribute18, 'Y', 'N') = 'Y'
2530 AND DECODE (PAVD.num_cat_attribute19,
2531 PAV.num_cat_attribute19, 'Y', 'N') = 'Y'
2532 AND DECODE (PAVD.num_cat_attribute20,
2533 PAV.num_cat_attribute20, 'Y', 'N') = 'Y'
2534 AND DECODE (PAVD.num_cat_attribute21,
2535 PAV.num_cat_attribute21, 'Y', 'N') = 'Y'
2536 AND DECODE (PAVD.num_cat_attribute22,
2537 PAV.num_cat_attribute22, 'Y', 'N') = 'Y'
2538 AND DECODE (PAVD.num_cat_attribute23,
2539 PAV.num_cat_attribute23, 'Y', 'N') = 'Y'
2540 AND DECODE (PAVD.num_cat_attribute24,
2541 PAV.num_cat_attribute24, 'Y', 'N') = 'Y'
2542 AND DECODE (PAVD.num_cat_attribute25,
2543 PAV.num_cat_attribute25, 'Y', 'N') = 'Y'
2544 AND DECODE (PAVD.num_cat_attribute26,
2545 PAV.num_cat_attribute26, 'Y', 'N') = 'Y'
2546 AND DECODE (PAVD.num_cat_attribute27,
2547 PAV.num_cat_attribute27, 'Y', 'N') = 'Y'
2548 AND DECODE (PAVD.num_cat_attribute28,
2549 PAV.num_cat_attribute28, 'Y', 'N') = 'Y'
2550 AND DECODE (PAVD.num_cat_attribute29,
2551 PAV.num_cat_attribute29, 'Y', 'N') = 'Y'
2552 AND DECODE (PAVD.num_cat_attribute30,
2553 PAV.num_cat_attribute30, 'Y', 'N') = 'Y'
2554 AND DECODE (PAVD.num_cat_attribute31,
2555 PAV.num_cat_attribute31, 'Y', 'N') = 'Y'
2556 AND DECODE (PAVD.num_cat_attribute32,
2557 PAV.num_cat_attribute32, 'Y', 'N') = 'Y'
2558 AND DECODE (PAVD.num_cat_attribute33,
2559 PAV.num_cat_attribute33, 'Y', 'N') = 'Y'
2560 AND DECODE (PAVD.num_cat_attribute34,
2561 PAV.num_cat_attribute34, 'Y', 'N') = 'Y'
2562 AND DECODE (PAVD.num_cat_attribute35,
2563 PAV.num_cat_attribute35, 'Y', 'N') = 'Y'
2564 AND DECODE (PAVD.num_cat_attribute36,
2565 PAV.num_cat_attribute36, 'Y', 'N') = 'Y'
2566 AND DECODE (PAVD.num_cat_attribute37,
2567 PAV.num_cat_attribute37, 'Y', 'N') = 'Y'
2568 AND DECODE (PAVD.num_cat_attribute38,
2569 PAV.num_cat_attribute38, 'Y', 'N') = 'Y'
2570 AND DECODE (PAVD.num_cat_attribute39,
2571 PAV.num_cat_attribute39, 'Y', 'N') = 'Y'
2572 AND DECODE (PAVD.num_cat_attribute40,
2573 PAV.num_cat_attribute40, 'Y', 'N') = 'Y'
2574 AND DECODE (PAVD.num_cat_attribute41,
2575 PAV.num_cat_attribute41, 'Y', 'N') = 'Y'
2576 AND DECODE (PAVD.num_cat_attribute42,
2577 PAV.num_cat_attribute42, 'Y', 'N') = 'Y'
2578 AND DECODE (PAVD.num_cat_attribute43,
2579 PAV.num_cat_attribute43, 'Y', 'N') = 'Y'
2580 AND DECODE (PAVD.num_cat_attribute44,
2581 PAV.num_cat_attribute44, 'Y', 'N') = 'Y'
2582 AND DECODE (PAVD.num_cat_attribute45,
2583 PAV.num_cat_attribute45, 'Y', 'N') = 'Y'
2587 PAV.num_cat_attribute47, 'Y', 'N') = 'Y'
2584 AND DECODE (PAVD.num_cat_attribute46,
2585 PAV.num_cat_attribute46, 'Y', 'N') = 'Y'
2586 AND DECODE (PAVD.num_cat_attribute47,
2588 AND DECODE (PAVD.num_cat_attribute48,
2589 PAV.num_cat_attribute48, 'Y', 'N') = 'Y'
2590 AND DECODE (PAVD.num_cat_attribute49,
2591 PAV.num_cat_attribute49, 'Y', 'N') = 'Y'
2592 AND DECODE (PAVD.num_cat_attribute50,
2593 PAV.num_cat_attribute50, 'Y', 'N') = 'Y');
2594
2595
2596 IF (PO_LOG.d_stmt) THEN
2597 PO_LOG.stmt (d_module, d_position, '# of attr values autoaccepted', SQL%ROWCOUNT);
2598 END IF;
2599
2600 d_position := 40;
2601
2602 -- Attribute values tlp
2603 UPDATE po_attribute_values_tlp_draft PAVTD
2604 SET PAVTD.change_accepted_flag = 'Y'
2605 WHERE PAVTD.draft_id = p_draft_id
2606 AND PAVTD.change_accepted_flag IS NULL
2607 AND NVL(PAVTD.delete_flag, 'N') <> 'Y'
2608 AND EXISTS
2609 ( SELECT 1
2610 FROM po_attribute_values_tlp PAVT
2611 WHERE PAVTD.attribute_values_tlp_id = PAVT.attribute_values_tlp_id
2612 AND DECODE (PAVTD.description,
2613 PAVT.description, 'Y', 'N') = 'Y'
2614 AND DECODE (PAVTD.manufacturer,
2615 PAVT.manufacturer, 'Y', 'N') = 'Y'
2616 AND DECODE (PAVTD.comments,
2617 PAVT.comments, 'Y', 'N') = 'Y'
2618 AND DECODE (PAVTD.alias,
2619 PAVT.alias, 'Y', 'N') = 'Y'
2620 AND DECODE (PAVTD.long_description,
2621 PAVT.long_description, 'Y', 'N') = 'Y'
2622 AND DECODE (PAVTD.tl_text_base_attribute1,
2623 PAVT.tl_text_base_attribute1, 'Y', 'N') = 'Y'
2624 AND DECODE (PAVTD.tl_text_base_attribute2,
2625 PAVT.tl_text_base_attribute2, 'Y', 'N') = 'Y'
2626 AND DECODE (PAVTD.tl_text_base_attribute3,
2627 PAVT.tl_text_base_attribute3, 'Y', 'N') = 'Y'
2628 AND DECODE (PAVTD.tl_text_base_attribute4,
2629 PAVT.tl_text_base_attribute4, 'Y', 'N') = 'Y'
2630 AND DECODE (PAVTD.tl_text_base_attribute5,
2631 PAVT.tl_text_base_attribute5, 'Y', 'N') = 'Y'
2632 AND DECODE (PAVTD.tl_text_base_attribute6,
2633 PAVT.tl_text_base_attribute6, 'Y', 'N') = 'Y'
2634 AND DECODE (PAVTD.tl_text_base_attribute7,
2635 PAVT.tl_text_base_attribute7, 'Y', 'N') = 'Y'
2636 AND DECODE (PAVTD.tl_text_base_attribute8,
2637 PAVT.tl_text_base_attribute8, 'Y', 'N') = 'Y'
2638 AND DECODE (PAVTD.tl_text_base_attribute9,
2639 PAVT.tl_text_base_attribute9, 'Y', 'N') = 'Y'
2640 AND DECODE (PAVTD.tl_text_base_attribute10,
2641 PAVT.tl_text_base_attribute10, 'Y', 'N') = 'Y'
2642 AND DECODE (PAVTD.tl_text_base_attribute11,
2643 PAVT.tl_text_base_attribute11, 'Y', 'N') = 'Y'
2644 AND DECODE (PAVTD.tl_text_base_attribute12,
2645 PAVT.tl_text_base_attribute12, 'Y', 'N') = 'Y'
2646 AND DECODE (PAVTD.tl_text_base_attribute13,
2647 PAVT.tl_text_base_attribute13, 'Y', 'N') = 'Y'
2648 AND DECODE (PAVTD.tl_text_base_attribute14,
2649 PAVT.tl_text_base_attribute14, 'Y', 'N') = 'Y'
2650 AND DECODE (PAVTD.tl_text_base_attribute15,
2651 PAVT.tl_text_base_attribute15, 'Y', 'N') = 'Y'
2652 AND DECODE (PAVTD.tl_text_base_attribute16,
2653 PAVT.tl_text_base_attribute16, 'Y', 'N') = 'Y'
2654 AND DECODE (PAVTD.tl_text_base_attribute17,
2655 PAVT.tl_text_base_attribute17, 'Y', 'N') = 'Y'
2656 AND DECODE (PAVTD.tl_text_base_attribute18,
2657 PAVT.tl_text_base_attribute18, 'Y', 'N') = 'Y'
2658 AND DECODE (PAVTD.tl_text_base_attribute19,
2659 PAVT.tl_text_base_attribute19, 'Y', 'N') = 'Y'
2660 AND DECODE (PAVTD.tl_text_base_attribute20,
2661 PAVT.tl_text_base_attribute20, 'Y', 'N') = 'Y'
2662 AND DECODE (PAVTD.tl_text_base_attribute21,
2663 PAVT.tl_text_base_attribute21, 'Y', 'N') = 'Y'
2664 AND DECODE (PAVTD.tl_text_base_attribute22,
2665 PAVT.tl_text_base_attribute22, 'Y', 'N') = 'Y'
2666 AND DECODE (PAVTD.tl_text_base_attribute23,
2667 PAVT.tl_text_base_attribute23, 'Y', 'N') = 'Y'
2668 AND DECODE (PAVTD.tl_text_base_attribute24,
2669 PAVT.tl_text_base_attribute24, 'Y', 'N') = 'Y'
2670 AND DECODE (PAVTD.tl_text_base_attribute25,
2671 PAVT.tl_text_base_attribute25, 'Y', 'N') = 'Y'
2672 AND DECODE (PAVTD.tl_text_base_attribute26,
2673 PAVT.tl_text_base_attribute26, 'Y', 'N') = 'Y'
2677 PAVT.tl_text_base_attribute28, 'Y', 'N') = 'Y'
2674 AND DECODE (PAVTD.tl_text_base_attribute27,
2675 PAVT.tl_text_base_attribute27, 'Y', 'N') = 'Y'
2676 AND DECODE (PAVTD.tl_text_base_attribute28,
2678 AND DECODE (PAVTD.tl_text_base_attribute29,
2679 PAVT.tl_text_base_attribute29, 'Y', 'N') = 'Y'
2680 AND DECODE (PAVTD.tl_text_base_attribute30,
2681 PAVT.tl_text_base_attribute30, 'Y', 'N') = 'Y'
2682 AND DECODE (PAVTD.tl_text_base_attribute31,
2683 PAVT.tl_text_base_attribute31, 'Y', 'N') = 'Y'
2684 AND DECODE (PAVTD.tl_text_base_attribute32,
2685 PAVT.tl_text_base_attribute32, 'Y', 'N') = 'Y'
2686 AND DECODE (PAVTD.tl_text_base_attribute33,
2687 PAVT.tl_text_base_attribute33, 'Y', 'N') = 'Y'
2688 AND DECODE (PAVTD.tl_text_base_attribute34,
2689 PAVT.tl_text_base_attribute34, 'Y', 'N') = 'Y'
2690 AND DECODE (PAVTD.tl_text_base_attribute35,
2691 PAVT.tl_text_base_attribute35, 'Y', 'N') = 'Y'
2692 AND DECODE (PAVTD.tl_text_base_attribute36,
2693 PAVT.tl_text_base_attribute36, 'Y', 'N') = 'Y'
2694 AND DECODE (PAVTD.tl_text_base_attribute37,
2695 PAVT.tl_text_base_attribute37, 'Y', 'N') = 'Y'
2696 AND DECODE (PAVTD.tl_text_base_attribute38,
2697 PAVT.tl_text_base_attribute38, 'Y', 'N') = 'Y'
2698 AND DECODE (PAVTD.tl_text_base_attribute39,
2699 PAVT.tl_text_base_attribute39, 'Y', 'N') = 'Y'
2700 AND DECODE (PAVTD.tl_text_base_attribute40,
2701 PAVT.tl_text_base_attribute40, 'Y', 'N') = 'Y'
2702 AND DECODE (PAVTD.tl_text_base_attribute41,
2703 PAVT.tl_text_base_attribute41, 'Y', 'N') = 'Y'
2704 AND DECODE (PAVTD.tl_text_base_attribute42,
2705 PAVT.tl_text_base_attribute42, 'Y', 'N') = 'Y'
2706 AND DECODE (PAVTD.tl_text_base_attribute43,
2707 PAVT.tl_text_base_attribute43, 'Y', 'N') = 'Y'
2708 AND DECODE (PAVTD.tl_text_base_attribute44,
2709 PAVT.tl_text_base_attribute44, 'Y', 'N') = 'Y'
2710 AND DECODE (PAVTD.tl_text_base_attribute45,
2711 PAVT.tl_text_base_attribute45, 'Y', 'N') = 'Y'
2712 AND DECODE (PAVTD.tl_text_base_attribute46,
2713 PAVT.tl_text_base_attribute46, 'Y', 'N') = 'Y'
2714 AND DECODE (PAVTD.tl_text_base_attribute47,
2715 PAVT.tl_text_base_attribute47, 'Y', 'N') = 'Y'
2716 AND DECODE (PAVTD.tl_text_base_attribute48,
2717 PAVT.tl_text_base_attribute48, 'Y', 'N') = 'Y'
2718 AND DECODE (PAVTD.tl_text_base_attribute49,
2719 PAVT.tl_text_base_attribute49, 'Y', 'N') = 'Y'
2720 AND DECODE (PAVTD.tl_text_base_attribute50,
2721 PAVT.tl_text_base_attribute50, 'Y', 'N') = 'Y'
2722 AND DECODE (PAVTD.tl_text_base_attribute51,
2723 PAVT.tl_text_base_attribute51, 'Y', 'N') = 'Y'
2724 AND DECODE (PAVTD.tl_text_base_attribute52,
2725 PAVT.tl_text_base_attribute52, 'Y', 'N') = 'Y'
2726 AND DECODE (PAVTD.tl_text_base_attribute53,
2727 PAVT.tl_text_base_attribute53, 'Y', 'N') = 'Y'
2728 AND DECODE (PAVTD.tl_text_base_attribute54,
2729 PAVT.tl_text_base_attribute54, 'Y', 'N') = 'Y'
2730 AND DECODE (PAVTD.tl_text_base_attribute55,
2731 PAVT.tl_text_base_attribute55, 'Y', 'N') = 'Y'
2732 AND DECODE (PAVTD.tl_text_base_attribute56,
2733 PAVT.tl_text_base_attribute56, 'Y', 'N') = 'Y'
2734 AND DECODE (PAVTD.tl_text_base_attribute57,
2735 PAVT.tl_text_base_attribute57, 'Y', 'N') = 'Y'
2736 AND DECODE (PAVTD.tl_text_base_attribute58,
2737 PAVT.tl_text_base_attribute58, 'Y', 'N') = 'Y'
2738 AND DECODE (PAVTD.tl_text_base_attribute59,
2739 PAVT.tl_text_base_attribute59, 'Y', 'N') = 'Y'
2740 AND DECODE (PAVTD.tl_text_base_attribute60,
2741 PAVT.tl_text_base_attribute60, 'Y', 'N') = 'Y'
2742 AND DECODE (PAVTD.tl_text_base_attribute61,
2743 PAVT.tl_text_base_attribute61, 'Y', 'N') = 'Y'
2744 AND DECODE (PAVTD.tl_text_base_attribute62,
2745 PAVT.tl_text_base_attribute62, 'Y', 'N') = 'Y'
2746 AND DECODE (PAVTD.tl_text_base_attribute63,
2747 PAVT.tl_text_base_attribute63, 'Y', 'N') = 'Y'
2748 AND DECODE (PAVTD.tl_text_base_attribute64,
2749 PAVT.tl_text_base_attribute64, 'Y', 'N') = 'Y'
2750 AND DECODE (PAVTD.tl_text_base_attribute65,
2751 PAVT.tl_text_base_attribute65, 'Y', 'N') = 'Y'
2752 AND DECODE (PAVTD.tl_text_base_attribute66,
2753 PAVT.tl_text_base_attribute66, 'Y', 'N') = 'Y'
2757 PAVT.tl_text_base_attribute68, 'Y', 'N') = 'Y'
2754 AND DECODE (PAVTD.tl_text_base_attribute67,
2755 PAVT.tl_text_base_attribute67, 'Y', 'N') = 'Y'
2756 AND DECODE (PAVTD.tl_text_base_attribute68,
2758 AND DECODE (PAVTD.tl_text_base_attribute69,
2759 PAVT.tl_text_base_attribute69, 'Y', 'N') = 'Y'
2760 AND DECODE (PAVTD.tl_text_base_attribute70,
2761 PAVT.tl_text_base_attribute70, 'Y', 'N') = 'Y'
2762 AND DECODE (PAVTD.tl_text_base_attribute71,
2763 PAVT.tl_text_base_attribute71, 'Y', 'N') = 'Y'
2764 AND DECODE (PAVTD.tl_text_base_attribute72,
2765 PAVT.tl_text_base_attribute72, 'Y', 'N') = 'Y'
2766 AND DECODE (PAVTD.tl_text_base_attribute73,
2767 PAVT.tl_text_base_attribute73, 'Y', 'N') = 'Y'
2768 AND DECODE (PAVTD.tl_text_base_attribute74,
2769 PAVT.tl_text_base_attribute74, 'Y', 'N') = 'Y'
2770 AND DECODE (PAVTD.tl_text_base_attribute75,
2771 PAVT.tl_text_base_attribute75, 'Y', 'N') = 'Y'
2772 AND DECODE (PAVTD.tl_text_base_attribute76,
2773 PAVT.tl_text_base_attribute76, 'Y', 'N') = 'Y'
2774 AND DECODE (PAVTD.tl_text_base_attribute77,
2775 PAVT.tl_text_base_attribute77, 'Y', 'N') = 'Y'
2776 AND DECODE (PAVTD.tl_text_base_attribute78,
2777 PAVT.tl_text_base_attribute78, 'Y', 'N') = 'Y'
2778 AND DECODE (PAVTD.tl_text_base_attribute79,
2779 PAVT.tl_text_base_attribute79, 'Y', 'N') = 'Y'
2780 AND DECODE (PAVTD.tl_text_base_attribute80,
2781 PAVT.tl_text_base_attribute80, 'Y', 'N') = 'Y'
2782 AND DECODE (PAVTD.tl_text_base_attribute81,
2783 PAVT.tl_text_base_attribute81, 'Y', 'N') = 'Y'
2784 AND DECODE (PAVTD.tl_text_base_attribute82,
2785 PAVT.tl_text_base_attribute82, 'Y', 'N') = 'Y'
2786 AND DECODE (PAVTD.tl_text_base_attribute83,
2787 PAVT.tl_text_base_attribute83, 'Y', 'N') = 'Y'
2788 AND DECODE (PAVTD.tl_text_base_attribute84,
2789 PAVT.tl_text_base_attribute84, 'Y', 'N') = 'Y'
2790 AND DECODE (PAVTD.tl_text_base_attribute85,
2791 PAVT.tl_text_base_attribute85, 'Y', 'N') = 'Y'
2792 AND DECODE (PAVTD.tl_text_base_attribute86,
2793 PAVT.tl_text_base_attribute86, 'Y', 'N') = 'Y'
2794 AND DECODE (PAVTD.tl_text_base_attribute87,
2795 PAVT.tl_text_base_attribute87, 'Y', 'N') = 'Y'
2796 AND DECODE (PAVTD.tl_text_base_attribute88,
2797 PAVT.tl_text_base_attribute88, 'Y', 'N') = 'Y'
2798 AND DECODE (PAVTD.tl_text_base_attribute89,
2799 PAVT.tl_text_base_attribute89, 'Y', 'N') = 'Y'
2800 AND DECODE (PAVTD.tl_text_base_attribute90,
2801 PAVT.tl_text_base_attribute90, 'Y', 'N') = 'Y'
2802 AND DECODE (PAVTD.tl_text_base_attribute91,
2803 PAVT.tl_text_base_attribute91, 'Y', 'N') = 'Y'
2804 AND DECODE (PAVTD.tl_text_base_attribute92,
2805 PAVT.tl_text_base_attribute92, 'Y', 'N') = 'Y'
2806 AND DECODE (PAVTD.tl_text_base_attribute93,
2807 PAVT.tl_text_base_attribute93, 'Y', 'N') = 'Y'
2808 AND DECODE (PAVTD.tl_text_base_attribute94,
2809 PAVT.tl_text_base_attribute94, 'Y', 'N') = 'Y'
2810 AND DECODE (PAVTD.tl_text_base_attribute95,
2811 PAVT.tl_text_base_attribute95, 'Y', 'N') = 'Y'
2812 AND DECODE (PAVTD.tl_text_base_attribute96,
2813 PAVT.tl_text_base_attribute96, 'Y', 'N') = 'Y'
2814 AND DECODE (PAVTD.tl_text_base_attribute97,
2815 PAVT.tl_text_base_attribute97, 'Y', 'N') = 'Y'
2816 AND DECODE (PAVTD.tl_text_base_attribute98,
2817 PAVT.tl_text_base_attribute98, 'Y', 'N') = 'Y'
2818 AND DECODE (PAVTD.tl_text_base_attribute99,
2819 PAVT.tl_text_base_attribute99, 'Y', 'N') = 'Y'
2820 AND DECODE (PAVTD.tl_text_base_attribute100,
2821 PAVT.tl_text_base_attribute100, 'Y', 'N') = 'Y'
2822 AND DECODE (PAVTD.tl_text_cat_attribute1,
2823 PAVT.tl_text_cat_attribute1, 'Y', 'N') = 'Y'
2824 AND DECODE (PAVTD.tl_text_cat_attribute2,
2825 PAVT.tl_text_cat_attribute2, 'Y', 'N') = 'Y'
2826 AND DECODE (PAVTD.tl_text_cat_attribute3,
2827 PAVT.tl_text_cat_attribute3, 'Y', 'N') = 'Y'
2828 AND DECODE (PAVTD.tl_text_cat_attribute4,
2829 PAVT.tl_text_cat_attribute4, 'Y', 'N') = 'Y'
2830 AND DECODE (PAVTD.tl_text_cat_attribute5,
2831 PAVT.tl_text_cat_attribute5, 'Y', 'N') = 'Y'
2832 AND DECODE (PAVTD.tl_text_cat_attribute6,
2833 PAVT.tl_text_cat_attribute6, 'Y', 'N') = 'Y'
2837 PAVT.tl_text_cat_attribute8, 'Y', 'N') = 'Y'
2834 AND DECODE (PAVTD.tl_text_cat_attribute7,
2835 PAVT.tl_text_cat_attribute7, 'Y', 'N') = 'Y'
2836 AND DECODE (PAVTD.tl_text_cat_attribute8,
2838 AND DECODE (PAVTD.tl_text_cat_attribute9,
2839 PAVT.tl_text_cat_attribute9, 'Y', 'N') = 'Y'
2840 AND DECODE (PAVTD.tl_text_cat_attribute10,
2841 PAVT.tl_text_cat_attribute10, 'Y', 'N') = 'Y'
2842 AND DECODE (PAVTD.tl_text_cat_attribute11,
2843 PAVT.tl_text_cat_attribute11, 'Y', 'N') = 'Y'
2844 AND DECODE (PAVTD.tl_text_cat_attribute12,
2845 PAVT.tl_text_cat_attribute12, 'Y', 'N') = 'Y'
2846 AND DECODE (PAVTD.tl_text_cat_attribute13,
2847 PAVT.tl_text_cat_attribute13, 'Y', 'N') = 'Y'
2848 AND DECODE (PAVTD.tl_text_cat_attribute14,
2849 PAVT.tl_text_cat_attribute14, 'Y', 'N') = 'Y'
2850 AND DECODE (PAVTD.tl_text_cat_attribute15,
2851 PAVT.tl_text_cat_attribute15, 'Y', 'N') = 'Y'
2852 AND DECODE (PAVTD.tl_text_cat_attribute16,
2853 PAVT.tl_text_cat_attribute16, 'Y', 'N') = 'Y'
2854 AND DECODE (PAVTD.tl_text_cat_attribute17,
2855 PAVT.tl_text_cat_attribute17, 'Y', 'N') = 'Y'
2856 AND DECODE (PAVTD.tl_text_cat_attribute18,
2857 PAVT.tl_text_cat_attribute18, 'Y', 'N') = 'Y'
2858 AND DECODE (PAVTD.tl_text_cat_attribute19,
2859 PAVT.tl_text_cat_attribute19, 'Y', 'N') = 'Y'
2860 AND DECODE (PAVTD.tl_text_cat_attribute20,
2861 PAVT.tl_text_cat_attribute20, 'Y', 'N') = 'Y'
2862 AND DECODE (PAVTD.tl_text_cat_attribute21,
2863 PAVT.tl_text_cat_attribute21, 'Y', 'N') = 'Y'
2864 AND DECODE (PAVTD.tl_text_cat_attribute22,
2865 PAVT.tl_text_cat_attribute22, 'Y', 'N') = 'Y'
2866 AND DECODE (PAVTD.tl_text_cat_attribute23,
2867 PAVT.tl_text_cat_attribute23, 'Y', 'N') = 'Y'
2868 AND DECODE (PAVTD.tl_text_cat_attribute24,
2869 PAVT.tl_text_cat_attribute24, 'Y', 'N') = 'Y'
2870 AND DECODE (PAVTD.tl_text_cat_attribute25,
2871 PAVT.tl_text_cat_attribute25, 'Y', 'N') = 'Y'
2872 AND DECODE (PAVTD.tl_text_cat_attribute26,
2873 PAVT.tl_text_cat_attribute26, 'Y', 'N') = 'Y'
2874 AND DECODE (PAVTD.tl_text_cat_attribute27,
2875 PAVT.tl_text_cat_attribute27, 'Y', 'N') = 'Y'
2876 AND DECODE (PAVTD.tl_text_cat_attribute28,
2877 PAVT.tl_text_cat_attribute28, 'Y', 'N') = 'Y'
2878 AND DECODE (PAVTD.tl_text_cat_attribute29,
2879 PAVT.tl_text_cat_attribute29, 'Y', 'N') = 'Y'
2880 AND DECODE (PAVTD.tl_text_cat_attribute30,
2881 PAVT.tl_text_cat_attribute30, 'Y', 'N') = 'Y'
2882 AND DECODE (PAVTD.tl_text_cat_attribute31,
2883 PAVT.tl_text_cat_attribute31, 'Y', 'N') = 'Y'
2884 AND DECODE (PAVTD.tl_text_cat_attribute32,
2885 PAVT.tl_text_cat_attribute32, 'Y', 'N') = 'Y'
2886 AND DECODE (PAVTD.tl_text_cat_attribute33,
2887 PAVT.tl_text_cat_attribute33, 'Y', 'N') = 'Y'
2888 AND DECODE (PAVTD.tl_text_cat_attribute34,
2889 PAVT.tl_text_cat_attribute34, 'Y', 'N') = 'Y'
2890 AND DECODE (PAVTD.tl_text_cat_attribute35,
2891 PAVT.tl_text_cat_attribute35, 'Y', 'N') = 'Y'
2892 AND DECODE (PAVTD.tl_text_cat_attribute36,
2893 PAVT.tl_text_cat_attribute36, 'Y', 'N') = 'Y'
2894 AND DECODE (PAVTD.tl_text_cat_attribute37,
2895 PAVT.tl_text_cat_attribute37, 'Y', 'N') = 'Y'
2896 AND DECODE (PAVTD.tl_text_cat_attribute38,
2897 PAVT.tl_text_cat_attribute38, 'Y', 'N') = 'Y'
2898 AND DECODE (PAVTD.tl_text_cat_attribute39,
2899 PAVT.tl_text_cat_attribute39, 'Y', 'N') = 'Y'
2900 AND DECODE (PAVTD.tl_text_cat_attribute40,
2901 PAVT.tl_text_cat_attribute40, 'Y', 'N') = 'Y'
2902 AND DECODE (PAVTD.tl_text_cat_attribute41,
2903 PAVT.tl_text_cat_attribute41, 'Y', 'N') = 'Y'
2904 AND DECODE (PAVTD.tl_text_cat_attribute42,
2905 PAVT.tl_text_cat_attribute42, 'Y', 'N') = 'Y'
2906 AND DECODE (PAVTD.tl_text_cat_attribute43,
2907 PAVT.tl_text_cat_attribute43, 'Y', 'N') = 'Y'
2908 AND DECODE (PAVTD.tl_text_cat_attribute44,
2909 PAVT.tl_text_cat_attribute44, 'Y', 'N') = 'Y'
2910 AND DECODE (PAVTD.tl_text_cat_attribute45,
2911 PAVT.tl_text_cat_attribute45, 'Y', 'N') = 'Y'
2912 AND DECODE (PAVTD.tl_text_cat_attribute46,
2913 PAVT.tl_text_cat_attribute46, 'Y', 'N') = 'Y'
2917 PAVT.tl_text_cat_attribute48, 'Y', 'N') = 'Y'
2914 AND DECODE (PAVTD.tl_text_cat_attribute47,
2915 PAVT.tl_text_cat_attribute47, 'Y', 'N') = 'Y'
2916 AND DECODE (PAVTD.tl_text_cat_attribute48,
2918 AND DECODE (PAVTD.tl_text_cat_attribute49,
2919 PAVT.tl_text_cat_attribute49, 'Y', 'N') = 'Y'
2920 AND DECODE (PAVTD.tl_text_cat_attribute50,
2921 PAVT.tl_text_cat_attribute50, 'Y', 'N') = 'Y');
2922
2923 IF (PO_LOG.d_stmt) THEN
2924 PO_LOG.stmt (d_module, d_position, '# of attr values tlp autoaccepted', SQL%ROWCOUNT);
2925 END IF;
2926
2927 d_position := 50;
2928
2929 l_has_record_to_accept := has_record_to_accept
2930 ( p_draft_id => p_draft_id
2931 );
2932
2933
2934 IF (PO_LOG.d_proc) THEN
2935 PO_LOG.proc_end (d_module, 'l_has_record_to_accept', l_has_record_to_accept);
2936 END IF;
2937
2938 RETURN l_has_record_to_accept;
2939
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 PO_MESSAGE_S.add_exc_msg
2943 ( p_pkg_name => d_pkg_name,
2944 p_procedure_name => d_api_name || '.' || d_position
2945 );
2946
2947 RAISE;
2948 END autoaccept_unchanged_records;
2949
2950
2951 -----------------------------------------------------------------------
2952 --Start of Comments
2953 --Name: has_record_to_accept
2954 --Function:
2955 -- Returns whether there's still record pending for acceptance in the
2956 -- draft
2957 --Parameters:
2958 --IN:
2959 --p_draft_id
2960 -- draft unique identifier
2961 --IN OUT:
2962 --OUT:
2963 --Return:
2964 -- FND_API.G_TRUE if there's still record in the draft to accept
2965 -- FND_API.G_FALSE otherwise
2966 --Notes:
2967 --Testing:
2968 --End of Comments
2969 ------------------------------------------------------------------------
2970 FUNCTION has_record_to_accept
2971 ( p_draft_id IN NUMBER
2972 ) RETURN VARCHAR2
2973 IS
2974
2975 d_api_name CONSTANT VARCHAR2(30) := 'has_record_to_accept';
2976 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
2977 d_position NUMBER;
2978
2979 l_has_record_to_accept VARCHAR2(1) := FND_API.G_TRUE;
2980
2981 BEGIN
2982
2983 d_position := 0;
2984
2985 IF (PO_LOG.d_proc) THEN
2986 PO_LOG.proc_begin (d_module);
2987 END IF;
2988
2989 SELECT NVL(MAX(FND_API.G_TRUE), FND_API.G_FALSE)
2990 INTO l_has_record_to_accept
2991 FROM DUAL
2992 WHERE EXISTS
2993 ( SELECT 1
2994 FROM po_lines_draft_all
2995 WHERE draft_id = p_draft_id
2996 AND change_accepted_flag IS NULL)
2997 OR EXISTS
2998 ( SELECT 1
2999 FROM po_line_locations_draft_all
3000 WHERE draft_id = p_draft_id
3001 AND change_accepted_flag IS NULL)
3002 OR EXISTS
3003 ( SELECT 1
3004 FROM po_price_diff_draft
3005 WHERE draft_id = p_draft_id
3006 AND change_accepted_flag IS NULL)
3007 OR EXISTS
3008 ( SELECT 1
3009 FROM po_attribute_values_draft
3010 WHERE draft_id = p_draft_id
3011 AND change_accepted_flag IS NULL)
3012 OR EXISTS
3013 ( SELECT 1
3017
3014 FROM po_attribute_values_tlp_draft
3015 WHERE draft_id = p_draft_id
3016 AND change_accepted_flag IS NULL);
3018
3019 IF (PO_LOG.d_proc) THEN
3020 PO_LOG.proc_end (d_module, 'l_has_record_to_accept', l_has_record_to_accept);
3021 END IF;
3022
3023 RETURN l_has_record_to_accept;
3024
3025
3026 EXCEPTION
3027 WHEN OTHERS THEN
3028 PO_MESSAGE_S.add_exc_msg
3029 ( p_pkg_name => d_pkg_name,
3030 p_procedure_name => d_api_name || '.' || d_position
3031 );
3032
3033 RAISE;
3034 END has_record_to_accept;
3035
3036 -- bug5249414 END
3037
3038
3039 -- bug5035979 START
3040 -- when the price acceptance level is 'Required when price update exceeds
3041 -- tolerance', we need to autoaccept lines that do not contain line changes
3042
3043 -----------------------------------------------------------------------
3044 --Start of Comments
3045 --Name: accept_if_no_line_changes
3046 --Function:
3047 -- Autoaccept records that do not have line level changes. This is called
3048 -- when acceptance level is 'required when price exceeds tolereance'. We
3049 -- do not need to accept the records since they won't contain any price
3050 -- changes.
3051 --Parameters:
3052 --IN:
3053 --p_draft_id
3054 -- draft unique identifier
3055 --IN OUT:
3056 --OUT:
3057 --Notes:
3058 --Testing:
3059 --End of Comments
3060 ------------------------------------------------------------------------
3061 PROCEDURE accept_if_no_line_changes
3062 ( p_draft_id IN NUMBER
3063 ) IS
3064
3065 d_api_name CONSTANT VARCHAR2(30) := 'accept_if_no_line_changes';
3066 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3067 d_position NUMBER;
3068
3069 BEGIN
3070
3071 d_position := 0;
3072
3073 IF (PO_LOG.d_proc) THEN
3074 PO_LOG.proc_begin (d_module);
3075 END IF;
3076
3077 UPDATE po_line_locations_draft_all PLLDA
3078 SET PLLDA.change_accepted_flag = 'Y'
3079 WHERE PLLDA.draft_id = p_draft_id
3080 AND NVL(PLLDA.change_accepted_flag, 'N') <> 'Y'
3081 AND NOT EXISTS
3082 ( SELECT NULL
3083 FROM po_lines_draft_all PLDA
3084 WHERE PLDA.po_line_id = PLLDA.po_line_id
3085 AND PLDA.draft_id = PLLDA.draft_id );
3086
3087 d_position := 10;
3088
3089 UPDATE po_attribute_values_draft PLVD
3090 SET PLVD.change_accepted_flag = 'Y'
3091 WHERE PLVD.draft_id = p_draft_id
3092 AND NVL(PLVD.change_accepted_flag, 'N') <> 'Y'
3093 AND NOT EXISTS
3094 ( SELECT NULL
3095 FROM po_lines_draft_all PLDA
3096 WHERE PLDA.po_line_id = PLVD.po_line_id
3097 AND PLDA.draft_id = PLVD.draft_id );
3098
3099 d_position := 20;
3100
3101 UPDATE po_attribute_values_tlp_draft PLVTD
3102 SET PLVTD.change_accepted_flag = 'Y'
3103 WHERE PLVTD.draft_id = p_draft_id
3104 AND NVL(PLVTD.change_accepted_flag, 'N') <> 'Y'
3105 AND NOT EXISTS
3106 ( SELECT NULL
3107 FROM po_lines_draft_all PLDA
3108 WHERE PLDA.po_line_id = PLVTD.po_line_id
3109 AND PLDA.draft_id = PLVTD.draft_id );
3110
3111 d_position := 30;
3112
3113 UPDATE po_price_diff_draft PPDD
3114 SET PPDD.change_accepted_flag = 'Y'
3115 WHERE PPDD.draft_id = p_draft_id
3116 AND NVL(PPDD.change_accepted_flag, 'N') <> 'Y'
3117 AND (( PPDD.entity_type = 'BLANKET LINE'
3118 AND NOT EXISTS
3119 ( SELECT NULL
3120 FROM po_lines_draft_all PLDA
3121 WHERE PLDA.po_line_id = PPDD.entity_id
3122 AND PLDA.draft_id = PPDD.draft_id
3123 )
3124 )
3125 OR
3126 ( PPDD.entity_type = 'PRICE BREAK'
3127 AND NOT EXISTS
3128 ( SELECT NULL
3129 FROM po_line_locations_draft_all PLLDA,
3130 po_lines_draft_all PLDA
3131 WHERE PLLDA.line_location_id = PPDD.entity_id
3132 AND PLLDA.draft_id = PPDD.draft_id
3133 AND PLDA.po_line_id = PLLDA.po_line_id
3134 AND PLDA.draft_id = PLLDA.draft_id )
3135 AND NOT EXISTS
3136 ( SELECT NULL
3137 FROM po_line_locations_all PLLA,
3138 po_lines_draft_all PLDA
3139 WHERE PLLA.line_location_id = PPDD.entity_id
3140 AND PLDA.po_line_id = PLLA.po_line_id
3141 AND PLDA.draft_id = PPDD.draft_id)));
3142
3143 IF (PO_LOG.d_proc) THEN
3144 PO_LOG.proc_end (d_module);
3145 END IF;
3146
3147 EXCEPTION
3148 WHEN OTHERS THEN
3149 PO_MESSAGE_S.add_exc_msg
3150 ( p_pkg_name => d_pkg_name,
3151 p_procedure_name => d_api_name || '.' || d_position
3152 );
3153
3154 RAISE;
3158 --Start of Comments
3155 END accept_if_no_line_changes;
3156
3157 -----------------------------------------------------------------------
3159 --Name: cascade_change_acceptance
3160 --Function:
3161 -- Update the change acceptance status for the lines being passed in,
3162 -- and cascade the changes to their children.
3163 --Parameters:
3164 --IN:
3165 --p_draft_id
3166 -- draft unique identifier
3167 --p_line_id_list
3168 -- lines to modify
3169 --p_change_accepted_value
3170 -- New change acceptance status
3171 --IN OUT:
3172 --OUT:
3173 --x_return_status
3174 -- status of the procedure
3175 --Returns:
3176 --Notes:
3177 --Testing:
3178 --End of Comments
3179 ------------------------------------------------------------------------
3180 PROCEDURE cascade_change_acceptance
3181 ( p_draft_id IN NUMBER,
3182 p_line_id_list IN PO_TBL_NUMBER,
3183 p_change_accepted_value IN VARCHAR2,
3184 x_return_status OUT NOCOPY VARCHAR2
3185 ) IS
3186
3187 d_api_name CONSTANT VARCHAR2(30) := 'cascade_change_acceptance';
3188 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3189 d_position NUMBER;
3190
3191 BEGIN
3192 d_position := 0;
3193
3194 IF (PO_LOG.d_proc) THEN
3195 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
3196 PO_LOG.proc_begin (d_module, 'p_line_id_list.COUNT', p_line_id_list.COUNT);
3197 END IF;
3198
3199 x_return_status := FND_API.g_RET_STS_SUCCESS;
3200
3201 -- Update records in PO_LINES_DRAFT_ALL based on the list of lines to change
3202 -- in p_line_id_list;
3203 FORALL i IN 1..p_line_id_list.COUNT
3204 UPDATE po_lines_draft_all
3205 SET change_accepted_flag = p_change_accepted_value
3206 WHERE draft_id = p_draft_id
3207 AND po_line_id = p_line_id_list(i);
3208
3209 d_position := 10;
3210
3211 -- price breaks
3212 FORALL i IN 1..p_line_id_list.COUNT
3213 UPDATE po_line_locations_draft_all
3214 SET change_accepted_flag = p_change_accepted_value
3215 WHERE draft_id = p_draft_id
3216 AND po_line_id = p_line_id_list(i);
3217
3218 d_position := 20;
3219
3220 -- attribute values
3221 FORALL i IN 1..p_line_id_list.COUNT
3222 UPDATE po_attribute_values_draft
3223 SET change_accepted_flag = p_change_accepted_value
3224 WHERE draft_id = p_draft_id
3225 AND po_line_id = p_line_id_list(i);
3226
3227 d_position := 30;
3228
3229 -- attribute values tlp
3230 FORALL i IN 1..p_line_id_list.COUNT
3231 UPDATE po_attribute_values_tlp_draft
3232 SET change_accepted_flag = p_change_accepted_value
3233 WHERE draft_id = p_draft_id
3234 AND po_line_id = p_line_id_list(i);
3235
3236 d_position := 40;
3237
3238 -- blanket line price differentials
3239 FORALL i IN 1..p_line_id_list.COUNT
3240 UPDATE po_price_diff_draft
3241 SET change_accepted_flag = p_change_accepted_value
3242 WHERE draft_id = p_draft_id
3243 AND entity_id = p_line_id_list(i)
3244 AND entity_type = 'BLANKET LINE';
3245
3246 d_position := 50;
3247
3248 -- Price break price diffs - the po_line_id reference can either
3249 -- be found in draft or transaction table
3250 FORALL i IN 1..p_line_id_list.COUNT
3251 UPDATE po_price_diff_draft PPDD
3252 SET change_accepted_flag = p_change_accepted_value
3253 WHERE draft_id = p_draft_id
3254 AND entity_type = 'PRICE BREAK'
3255 AND EXISTS (SELECT 1
3256 FROM po_line_locations_draft_all PLLD
3257 WHERE PLLD.draft_id = p_draft_id
3258 AND PLLD.po_line_id = p_line_id_list(i)
3259 AND PLLD.line_location_id = PPDD.entity_id
3260 UNION ALL
3261 SELECT 1
3262 FROM po_line_locations_all PLLD
3263 WHERE PLLD.po_line_id = p_line_id_list(i)
3264 AND PLLD.line_location_id = PPDD.entity_id);
3265
3266 IF (PO_LOG.d_proc) THEN
3267 PO_LOG.proc_end (d_module);
3268 END IF;
3269
3270 EXCEPTION
3271 WHEN OTHERS THEN
3272 PO_MESSAGE_S.add_exc_msg
3273 ( p_pkg_name => d_pkg_name,
3274 p_procedure_name => d_api_name || '.' || d_position
3275 );
3276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3277 END cascade_change_acceptance;
3278
3279 -- bug5035979 END
3280
3281 -- bug5187544 START
3282 -----------------------------------------------------------------------
3283 --Start of Comments
3284 --Name: validate_disposition
3285 --Function:
3286 -- Validate the rejected list by the user can make sure that the action
3287 -- can be performed
3288 --Parameters:
3289 --IN:
3290 --p_draft_id
3291 -- draft unique identifier
3292 --p_reject_line_id_list
3293 -- lines being rejected
3294 --IN OUT:
3295 --OUT:
3296 --x_invalid_line_id_list
3297 -- lines that fail the validation
3298 --x_invalid_line_num_list
3299 -- line num for the lines that fail the validation
3300 --Returns:
3301 --Notes:
3302 --Testing:
3303 --End of Comments
3304 ------------------------------------------------------------------------
3305 PROCEDURE validate_disposition
3306 ( p_draft_id IN NUMBER,
3307 p_reject_line_id_list IN PO_TBL_NUMBER,
3308 x_invalid_line_id_list OUT NOCOPY PO_TBL_NUMBER,
3309 x_invalid_line_num_list OUT NOCOPY PO_TBL_NUMBER,
3310 x_return_status OUT NOCOPY VARCHAR2
3311 ) IS
3312
3313 d_api_name CONSTANT VARCHAR2(30) := 'validate_disposition';
3314 d_module CONSTANT VARCHAR2(255) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
3315 d_position NUMBER;
3316
3317 l_key PO_SESSION_GT.key%TYPE;
3318
3319 BEGIN
3320
3321 d_position := 0;
3325 IF (PO_LOG.d_proc) THEN
3322
3323 x_return_status := FND_API.G_RET_STS_SUCCESS;
3324
3326 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
3327 END IF;
3328
3329 x_invalid_line_id_list := PO_TBL_NUMBER();
3330
3331 IF (p_reject_line_id_list IS NULL OR
3332 p_reject_line_id_list.COUNT = 0) THEN
3333 RETURN;
3334 END IF;
3335
3336 -- If user rejects the deletion of a line, he cannot accept another line
3337 -- with the same line number, otherwise we'll have the old line not being
3338 -- deleted and the new line (same line #) being inserted.
3339
3340 l_key := PO_CORE_S.get_session_gt_nextval;
3341
3342 d_position := 10;
3343
3344 FORALL i IN 1..p_reject_line_id_list.COUNT
3345 INSERT INTO po_session_gt
3346 ( key,
3347 index_num1, -- po_line_id
3348 num2, -- line_num
3349 char2 -- delete_flag
3350 )
3351 SELECT l_key,
3352 p_reject_line_id_list(i),
3353 pld.line_num,
3354 pld.delete_flag
3355 FROM po_lines_draft_all pld
3356 WHERE pld.draft_id = p_draft_id
3357 AND pld.po_line_id = p_reject_line_id_list(i);
3358
3359 d_position := 20;
3360
3361 -- SQL What: Return the deleted lines that are being rejected, if there are
3362 -- other lines with the same line number that are being accepted
3363 SELECT PSG.index_num1,
3364 num2
3365 BULK COLLECT
3366 INTO x_invalid_line_id_list,
3367 x_invalid_line_num_list
3368 FROM po_session_gt PSG
3369 WHERE PSG.key = l_key
3370 AND PSG.char2 = 'Y' -- delete_flag
3371 AND EXISTS
3372 (SELECT NULL
3373 FROM po_lines_draft_all PLD
3374 WHERE PLD.draft_id = p_draft_id
3375 AND NVL(PLD.delete_flag, 'N') <> 'Y'
3376 AND PLD.line_num = PSG.num2 -- line_num
3377 AND NOT EXISTS
3378 (SELECT NULL
3379 FROM po_session_gt PSG1
3380 WHERE PSG1.key = l_key
3381 AND PSG1.index_num1 = PLD.po_line_id));
3382
3383 d_position := 30;
3384
3385 DELETE FROM po_session_gt
3386 WHERE key = l_key;
3387
3388 IF (x_invalid_line_id_list.COUNT > 0) THEN
3389 x_return_status := FND_API.G_RET_STS_ERROR;
3390 END IF;
3391
3392 IF (PO_LOG.d_proc) THEN
3393 PO_LOG.proc_end (d_module);
3394 END IF;
3395
3396 EXCEPTION
3397 WHEN OTHERS THEN
3398 PO_MESSAGE_S.add_exc_msg
3399 ( p_pkg_name => d_pkg_name,
3400 p_procedure_name => d_api_name || '.' || d_position
3401 );
3402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3403 END validate_disposition;
3404
3405 -- bug5187544 END
3406
3407 END PO_DIFF_SUMMARY_PKG;