[Home] [Help]
PACKAGE BODY: APPS.PO_DRAFTS_PVT
Source
1 PACKAGE BODY PO_DRAFTS_PVT AS
2 /* $Header: PO_DRAFTS_PVT.plb 120.32 2006/11/21 03:08:36 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_DRAFTS_PVT');
6
7
8 -------------------------------------------------------
9 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
10 -------------------------------------------------------
11
12 PROCEDURE pre_apply
13 ( p_draft_info IN DRAFT_INFO_REC_TYPE
14 );
15
16 PROCEDURE set_new_revision
17 ( p_draft_info IN DRAFT_INFO_REC_TYPE
18 );
19
20 PROCEDURE complete_transfer
21 ( p_draft_info IN DRAFT_INFO_REC_TYPE,
22 p_delete_draft IN VARCHAR2
23 );
24
25 PROCEDURE update_acceptance_status
26 ( p_draft_id IN NUMBER,
27 p_acceptance_action IN VARCHAR2
28 );
29
30 FUNCTION is_doc_in_updatable_state
31 ( p_po_header_id IN NUMBER,
32 p_role IN VARCHAR2
33 ) RETURN VARCHAR2;
34
35 -------------------------------------------------------
36 -------------- PUBLIC PROCEDURES ----------------------
37 -------------------------------------------------------
38
39 -----------------------------------------------------------------------
40 --Start of Comments
41 --Name: draft_id_nextval
42 --Pre-reqs: None
43 --Modifies:
44 --Locks:
45 --Function:
46 -- Return next draft id from sequence PO_DRAFTS_S
47 --Parameters:
48 --IN:
49 --IN OUT:
50 --OUT:
51 --Returns:
52 -- Next draft id from sequence
53 --Notes:
54 --Testing:
55 --End of Comments
56 ------------------------------------------------------------------------
57 FUNCTION draft_id_nextval
58 RETURN NUMBER IS
59
60 l_draft_id NUMBER;
61 BEGIN
62 SELECT PO_DRAFTS_S.nextval
63 INTO l_draft_id
64 FROM DUAL;
65
66 RETURN l_draft_id;
67 END draft_id_nextval;
68
69 -----------------------------------------------------------------------
70 --Start of Comments
71 --Name: transfer_draft_to_txn
72 --Pre-reqs: None
73 --Modifies:
74 --Locks:
75 -- transaction tables if update is allowed
76 --Function:
77 -- API to move the draft changes to transaction table
78 --Parameters:
79 --IN:
80 --p_draft_id
81 -- draft unique identifier
82 --p_po_header_id
83 -- unique identifier for document with the draft changes
84 --p_delete_processed_draft
85 -- indicates whether draft changes should be removed after the process
86 -- If FND_API.G_TRUE, draft records will be removed after the process
87 -- If FND_API.G_FALSE, draft records will retain after the process
88 -- If 'X', drafts records except for the one in PO_DRAFTS will be removed
89 -- after the process
90 --IN OUT:
91 --OUT:
92 --x_return_status
93 -- status of the procedure
94 --Returns:
95 --Notes:
96 --Testing:
97 --End of Comments
98 ------------------------------------------------------------------------
99 PROCEDURE transfer_draft_to_txn
100 ( p_api_version IN NUMBER,
101 p_init_msg_list IN VARCHAR2,
102 p_draft_id IN NUMBER,
103 p_po_header_id IN NUMBER,
104 p_delete_processed_draft IN VARCHAR2,
105 p_acceptance_action IN VARCHAR2,
106 x_return_status OUT NOCOPY VARCHAR2
107 ) IS
108
109 d_api_name CONSTANT VARCHAR2(30) := 'transfer_draft_to_txn';
110 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
111 d_position NUMBER;
112
113 d_api_version CONSTANT NUMBER := 1.0;
114
115 l_draft_info DRAFT_INFO_REC_TYPE;
116 l_status_rec PO_STATUS_REC_TYPE;
117 l_return_status VARCHAR2(1);
118 l_rebuild_attribs BOOLEAN := TRUE; -- 4902870
119 l_type VARCHAR2(20); -- 4902870
120
121 BEGIN
122 d_position := 0;
123
124 SAVEPOINT transfer_draft_to_txn_sp;
125
126 IF (PO_LOG.d_proc) THEN
127 PO_LOG.proc_begin(d_module);
128 PO_LOG.proc_begin(d_module, 'p_init_msg_list', p_init_msg_list);
129 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
130 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
131 PO_LOG.proc_begin(d_module, 'p_delete_processed_draft',
132 p_delete_processed_draft);
133 PO_LOG.proc_begin(d_module, 'p_acceptance_action', p_acceptance_action);
134 END IF;
135
136 IF (FND_API.to_boolean(p_init_msg_list)) THEN
137 FND_MSG_PUB.initialize;
138 END IF;
139
140 IF (NOT FND_API.Compatible_API_Call
141 ( p_current_version_number => d_api_version
142 , p_caller_version_number => p_api_version
143 , p_api_name => d_api_name
144 , p_pkg_name => d_pkg_name
145 )
146 ) THEN
147
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149 END IF; -- not compatible_api
150
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 -- if acceptance action is specified, we need to propogate the action to
154 -- all levels
155 IF (p_acceptance_action IS NOT NULL) THEN
156 IF (PO_LOG.d_stmt) THEN
157 PO_LOG.stmt(d_module, d_position, 'update acceptance action');
158 END IF;
159
160 update_acceptance_status
161 ( p_draft_id => p_draft_id,
162 p_acceptance_action => p_acceptance_action
163 );
164 END IF;
165
166 d_position := 10;
167 -- gather draft related information and put it into l_draft_info
168 populate_draft_info
169 ( p_draft_id => p_draft_id,
170 p_po_header_id => p_po_header_id,
171 x_draft_info => l_draft_info
172 );
173
174 IF (l_draft_info.doc_type <> 'QUOTATION') THEN
175
176 d_position := 20;
177
178 -- determine the new approval status for the document
179 PO_DRAFT_APPR_STATUS_PVT.update_approval_status
180 ( p_draft_info => l_draft_info
181 , x_rebuild_attribs => l_rebuild_attribs -- Bug#4902870
182 );
183
184
185 -- bug4176111
186 -- actions to be done before applying draft changes to txn tables
187 d_position := 30;
188 pre_apply
189 ( p_draft_info => l_draft_info
190 );
191
192 d_position := 40;
193 -- move changes from draft to transaction tables
194 apply_changes
195 ( p_draft_info => l_draft_info
196 );
197
198 d_position := 50;
199 -- determine new revision
200 set_new_revision
201 ( p_draft_info => l_draft_info
202 );
203 l_type := PO_CATALOG_INDEX_PVT.TYPE_BLANKET; --Bug#4902870
204
205 ELSE -- QUOTATION
206 l_type := PO_CATALOG_INDEX_PVT.TYPE_QUOTATION; --Bug#4902870
207 -- For quotations, there is no need to performa approval status or revision
208 -- checking
209
210 IF (PO_LOG.d_stmt) THEN
211 PO_LOG.stmt(d_module, d_position, 'document is quotation');
212 END IF;
213
214 d_position := 60;
215 apply_changes
216 ( p_draft_info => l_draft_info
217 );
218
219 END IF;
220
221 d_position := 70;
222
223 -- <Unified Catalog R12 Start>
224 -- Generate the default translations for the Attribute TLP records.
225 -- (Call this BEFORE complete_transfer because it deletes drafts)
226 PO_ATTRIBUTE_VALUES_PVT.gen_draft_line_translations
227 (
228 p_draft_id => p_draft_id
229 , p_doc_type => l_draft_info.doc_subtype
230 );
231 -- <Unified Catalog R12 End>
232
233 -- mark transction as completed
234 complete_transfer
235 ( p_draft_info => l_draft_info,
236 p_delete_draft => p_delete_processed_draft
237 );
238
239 d_position := 80;
240 --Bug#4902870
241 if(l_rebuild_attribs) then
242 PO_CATALOG_INDEX_PVT.rebuild_index
243 ( p_type => l_type,
244 p_po_header_id => p_po_header_id
245 );
246 end if;
247
248 IF (PO_LOG.d_proc) THEN
249 PO_LOG.proc_end(d_module);
250 END IF;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254
255 ROLLBACK TO transfer_draft_to_txn_sp;
256 PO_MESSAGE_S.add_exc_msg
257 ( p_pkg_name => d_pkg_name,
258 p_procedure_name => d_api_name || '.' || d_position
259 );
260
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 END transfer_draft_to_txn;
263
264
265 -----------------------------------------------------------------------
266 --Start of Comments
267 --Name: remove_draft_changes
268 --Pre-reqs: None
269 --Modifies:
270 --Locks:
271 -- None
272 --Function:
273 -- Removes all draft changes at all levels
274 --Parameters:
275 --IN:
276 --p_draft_id
277 -- draft unique identifier
278 --p_exclude_ctrl_tbl
279 -- determines whether control table should be excluded from deletion
280 -- FND_API.G_TRUE if PO_DRAFTS should be excluded
281 -- FND_API.G_FALSE if PO_DRAFTS should not be excluded
282 --IN OUT:
283 --OUT:
284 --x_return_status
285 -- return status of the procedure
286 --Returns:
287 --Notes:
288 --Testing:
289 --End of Comments
290 ------------------------------------------------------------------------
291 PROCEDURE remove_draft_changes
292 ( p_draft_id IN NUMBER,
293 p_exclude_ctrl_tbl IN VARCHAR2,
294 x_return_status OUT NOCOPY VARCHAR2
295 ) IS
296
297 d_api_name CONSTANT VARCHAR2(30) := 'remove_draft_changes';
298 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
299 d_position NUMBER;
300
301 BEGIN
302
303 d_position := 0;
304 IF (PO_LOG.d_proc) THEN
305 PO_LOG.proc_begin(d_module);
306 END IF;
307
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 PO_HEADERS_DRAFT_PKG.delete_rows
311 ( p_draft_id => p_draft_id,
312 p_po_header_id => NULL
313 );
314
315 d_position := 10;
316 PO_LINES_DRAFT_PKG.delete_rows
317 ( p_draft_id => p_draft_id,
318 p_po_line_id => NULL
319 );
320
321 d_position := 20;
322 PO_LINE_LOCATIONS_DRAFT_PKG.delete_rows
323 ( p_draft_id => p_draft_id,
324 p_line_location_id => NULL
325 );
326
327 d_position := 30;
328 PO_DISTRIBUTIONS_DRAFT_PKG.delete_rows
329 ( p_draft_id => p_draft_id,
330 p_po_distribution_id => NULL
331 );
332
333 d_position := 40;
334 PO_GA_ORG_ASSIGN_DRAFT_PKG.delete_rows
335 ( p_draft_id => p_draft_id,
336 p_org_assignment_id => NULL
337 );
338
339 d_position := 50;
340 PO_PRICE_DIFF_DRAFT_PKG.delete_rows
341 ( p_draft_id => p_draft_id,
342 p_price_differential_id => NULL
343 );
344
345 d_position := 60;
346 PO_NOTIFICATION_CTRL_DRAFT_PKG.delete_rows
347 ( p_draft_id => p_draft_id,
348 p_notification_id => NULL
349 );
350
351 d_position := 70;
352 PO_ATTR_VALUES_DRAFT_PKG.delete_rows
353 ( p_draft_id => p_draft_id,
354 p_attribute_values_id => NULL
355 );
356
357 d_position := 80;
358 PO_ATTR_VALUES_TLP_DRAFT_PKG.delete_rows
359 ( p_draft_id => p_draft_id,
360 p_attribute_values_tlp_id => NULL
361 );
362
363 d_position := 90;
364
365 IF (NVL(p_exclude_ctrl_tbl, FND_API.G_FALSE) = FND_API.G_FALSE) THEN
366 -- delete draft control table as well
367 DELETE FROM po_drafts
368 WHERE draft_id = p_draft_id;
369 END IF;
370
371 d_position := 100;
372 IF (PO_LOG.d_proc) THEN
373 PO_LOG.proc_end(d_module);
374 END IF;
375 EXCEPTION
376 WHEN OTHERS THEN
377 PO_MESSAGE_S.add_exc_msg
378 ( p_pkg_name => d_pkg_name,
379 p_procedure_name => d_api_name || '.' || d_position
380 );
381
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 END remove_draft_changes;
384
385
386
387 -----------------------------------------------------------------------
388 --Start of Comments
389 --Name: populate_draft_info
390 --Pre-reqs: None
391 --Modifies:
392 --Locks:
393 -- None
394 --Function:
395 -- Populates record structure that holds draft information
396 --Parameters:
397 --IN:
398 --p_draft_id
399 -- draft unique identifier
400 --p_po_header_id
401 -- document unique identifier
402 --IN OUT:
403 --OUT:
404 --x_draft_info
405 -- record that holds draft information
406 --Returns:
407 --Notes:
408 --Testing:
409 --End of Comments
410 ------------------------------------------------------------------------
411 PROCEDURE populate_draft_info
412 ( p_draft_id IN NUMBER,
413 p_po_header_id IN NUMBER,
414 x_draft_info OUT NOCOPY DRAFT_INFO_REC_TYPE
415 ) IS
416
417 d_api_name CONSTANT VARCHAR2(30) := 'populate_draft_info';
418 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
419 d_position NUMBER;
420
421 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
422 l_quote_type_lookup_code PO_HEADERS_ALL.quote_type_lookup_code%TYPE;
423
424
425 BEGIN
426
427 d_position := 0;
428 IF (PO_LOG.d_proc) THEN
429 PO_LOG.proc_begin(d_module);
430 END IF;
431
432 x_draft_info.draft_id := p_draft_id;
433 x_draft_info.po_header_id := p_po_header_id;
434
435 -- SQL What: Check whether po_header_id specified already
436 -- exists in PO_HEADERS_ALL
437 -- SQL Why: Need to see whether it is a new document being created
438 SELECT NVL(MAX(FND_API.G_FALSE), FND_API.G_TRUE)
439 INTO x_draft_info.new_document
440 FROM po_headers_all POH
441 WHERE POH.po_header_id = p_po_header_id;
442
443 IF (x_draft_info.new_document = FND_API.G_TRUE) THEN
444 d_position := 10;
445 SELECT PHD.type_lookup_code,
446 PHD.quote_type_lookup_code,
447 NVL(PHD.global_agreement_flag, 'N')
448 INTO l_type_lookup_code,
449 l_quote_type_lookup_code,
450 x_draft_info.ga_flag
451 FROM PO_HEADERS_DRAFT_ALL PHD
452 WHERE PHD.po_header_id = p_po_header_id
453 AND PHD.draft_id = p_draft_id;
454 ELSE
455 d_position := 20;
456 -- If it's not new document, then the changes may not contain header
457 -- change. Get the information from the underlined transaction table
458 SELECT PHA.type_lookup_code,
459 PHA.quote_type_lookup_code,
460 NVL(PHA.global_agreement_flag, 'N')
461 INTO l_type_lookup_code,
462 l_quote_type_lookup_code,
463 x_draft_info.ga_flag
464 FROM PO_HEADERS_ALL PHA
465 WHERE PHA.po_header_id = p_po_header_id;
466 END IF;
467
468 d_position := 30;
469 IF (l_type_lookup_code = 'QUOTATION') THEN
470 x_draft_info.doc_type := 'QUOTATION';
471 x_draft_info.doc_subtype := l_quote_type_lookup_code;
472 ELSE
473 IF (l_type_lookup_code = 'STANDARD') THEN
474 x_draft_info.doc_type := 'PO';
475 ELSIF (l_type_lookup_code IN ('BLANKET', 'CONTRACT')) THEN
476 x_draft_info.doc_type := 'PA';
477 END IF;
478
479 x_draft_info.doc_subtype := l_type_lookup_code;
480 END IF;
481
482 d_position := 40;
483 -- check if header gets changed
484 x_draft_info.headers_changed :=
485 PO_HEADERS_DRAFT_PVT.draft_changes_exist
486 ( p_draft_id => p_draft_id,
487 p_po_header_id => NULL
488 );
489
490 d_position := 50;
491 -- check if any line gets changed
492 x_draft_info.lines_changed :=
493 PO_LINES_DRAFT_PVT.draft_changes_exist
494 ( p_draft_id => p_draft_id,
495 p_po_line_id => NULL
496 );
497
498 d_position := 60;
499 -- check if any line location gets changed
500 x_draft_info.line_locations_changed :=
501 PO_LINE_LOCATIONS_DRAFT_PVT.draft_changes_exist
502 ( p_draft_id => p_draft_id,
503 p_line_location_id => NULL
504 );
505
506 d_position := 70;
507 -- check if any distribution gets changed
508 x_draft_info.distributions_changed :=
509 PO_DISTRIBUTIONS_DRAFT_PVT.draft_changes_exist
510 ( p_draft_id => p_draft_id,
511 p_po_distribution_id => NULL
512 );
513
514 d_position := 80;
515 -- check if any org assignment gets changed
516 x_draft_info.ga_org_assign_changed :=
517 PO_GA_ORG_ASSIGN_DRAFT_PVT.draft_changes_exist
518 ( p_draft_id => p_draft_id,
519 p_org_assignment_id => NULL
520 );
521
522 d_position := 90;
523 -- check if price differentials gets changed
524 x_draft_info.price_diff_changed :=
525 PO_PRICE_DIFF_DRAFT_PVT.draft_changes_exist
526 ( p_draft_id => p_draft_id,
527 p_price_differential_id => NULL
528 );
529
530 d_position := 100;
531 -- check if any notification control gets changed
532 x_draft_info.notification_ctrl_changed :=
533 PO_NOTIFICATION_CTRL_DRAFT_PVT.draft_changes_exist
534 ( p_draft_id => p_draft_id,
535 p_notification_id => NULL
536 );
537
538 d_position := 110;
539 -- check if any attribute values record gets changed
540 x_draft_info.attr_values_changed :=
541 PO_ATTR_VALUES_DRAFT_PVT.draft_changes_exist
542 ( p_draft_id => p_draft_id,
543 p_attribute_values_id => NULL
544 );
545
546 d_position := 120;
547 -- check if any attribute values record gets changed
548 x_draft_info.attr_values_tlp_changed :=
549 PO_ATTR_VALUES_TLP_DRAFT_PVT.draft_changes_exist
550 ( p_draft_id => p_draft_id,
551 p_attribute_values_tlp_id => NULL
552 );
553
554 d_position := 130;
555
556 IF (PO_LOG.d_stmt) THEN
557 PO_LOG.stmt(d_module, d_position, 'draft_id', x_draft_info.draft_id);
558 PO_LOG.stmt(d_module, d_position, 'po_header_id',
559 x_draft_info.po_header_id);
560 PO_LOG.stmt(d_module, d_position, 'doc_type', x_draft_info.doc_type);
561 PO_LOG.stmt(d_module, d_position, 'doc_subtype', x_draft_info.doc_subtype);
562 PO_LOG.stmt(d_module, d_position, 'ga_flag', x_draft_info.ga_flag);
563 PO_LOG.stmt(d_module, d_position, 'new_document',
564 x_draft_info.new_document);
565 PO_LOG.stmt(d_module, d_position, 'headers_changed',
566 x_draft_info.headers_changed);
567 PO_LOG.stmt(d_module, d_position, 'lines_changed',
568 x_draft_info.lines_changed);
569 PO_LOG.stmt(d_module, d_position, 'line_locations_changed',
570 x_draft_info.line_locations_changed);
571 PO_LOG.stmt(d_module, d_position, 'distributions_changed',
572 x_draft_info.distributions_changed);
573 PO_LOG.stmt(d_module, d_position, 'ga_org_assign_changed',
574 x_draft_info.ga_org_assign_changed);
575 PO_LOG.stmt(d_module, d_position, 'price_diff_changed',
576 x_draft_info.price_diff_changed);
577 PO_LOG.stmt(d_module, d_position, 'notification_ctrl_changed',
578 x_draft_info.notification_ctrl_changed);
579 PO_LOG.stmt(d_module, d_position, 'attribute_values_changed',
580 x_draft_info.attr_values_changed);
581 PO_LOG.stmt(d_module, d_position, 'attribute_values_tlp_changed',
582 x_draft_info.attr_values_tlp_changed);
583 END IF;
584
585 IF (PO_LOG.d_proc) THEN
586 PO_LOG.proc_end(d_module);
587 END IF;
588 EXCEPTION
589 WHEN OTHERS THEN
590 PO_MESSAGE_S.add_exc_msg
591 ( p_pkg_name => d_pkg_name,
592 p_procedure_name => d_api_name || '.' || d_position
593 );
594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595 END populate_draft_info;
596
597 -- bug4176111
598 -----------------------------------------------------------------------
599 --Start of Comments
600 --Name: pre_apply
601 --Function: This is the procedure to call before any draft data is moved
602 -- to transaction table
603 --
604 --Parameters:
605 --IN:
606 --p_draft_info
607 -- record structure that holds draft information
608 --IN OUT:
609 --OUT:
610 --End of Comments
611 ------------------------------------------------------------------------
612
613 PROCEDURE pre_apply
614 ( p_draft_info IN DRAFT_INFO_REC_TYPE
615 ) IS
616 d_api_name CONSTANT VARCHAR2(30) := 'pre_apply';
617 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
618 d_position NUMBER;
619
620 BEGIN
621
622 IF (PO_LOG.d_proc) THEN
623 PO_LOG.proc_begin(d_module);
624 END IF;
625
626 d_position := 0;
627
628 PO_LINES_DRAFT_PVT.maintain_retroactive_change
629 ( p_draft_info => p_draft_info
630 );
631
632 d_position := 10;
633
634 PO_LINE_LOCATIONS_DRAFT_PVT.maintain_retroactive_change
635 ( p_draft_info => p_draft_info
636 );
637
638
639 IF (PO_LOG.d_proc) THEN
640 PO_LOG.proc_end(d_module);
641 END IF;
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 PO_MESSAGE_S.add_exc_msg
646 ( p_pkg_name => d_pkg_name,
647 p_procedure_name => d_api_name || '.' || d_position
648 );
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END pre_apply;
651
652
653
654
655
656 -----------------------------------------------------------------------
657 --Start of Comments
658 --Name: find_draft
659 --Function: Find a non-completed draft that meets the search criteria. It also
660 -- returns extra information about the draft
661 --
662 --Parameters:
663 --IN:
664 --p_po_header_id
665 -- document id
666 --IN OUT:
667 --x_draft_id
668 -- The draft id identifying the draft changes.
669 --x_draft_status
670 -- Status of the draft
671 --x_draft_owner_role
672 -- Owner role of the draft
673 --OUT:
674 --End of Comments
675 ------------------------------------------------------------------------
676
677 PROCEDURE find_draft
678 ( p_po_header_id IN NUMBER,
679 x_draft_id OUT NOCOPY NUMBER,
680 x_draft_status OUT NOCOPY VARCHAR2,
681 x_draft_owner_role OUT NOCOPY VARCHAR2
682 ) IS
683 d_api_name CONSTANT VARCHAR2(30) := 'find_draft';
684 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
685 d_position NUMBER;
686
687 BEGIN
688 d_position := 0;
689 IF (PO_LOG.d_proc) THEN
690 PO_LOG.proc_begin(d_module);
691 END IF;
692
693 --SQL What: Search for a non-completed draft given the document id
694 --SQL Why:
695 SELECT DFT.draft_id,
696 DFT.status,
697 DFT.owner_role
698 INTO x_draft_id,
699 x_draft_status,
700 x_draft_owner_role
701 FROM po_drafts DFT
702 WHERE DFT.document_id = p_po_header_id
703 AND DFT.status <> g_status_COMPLETED;
704
705 d_position := 10;
706
707 IF (PO_LOG.d_proc) THEN
708 PO_LOG.proc_end(d_module, 'draft_id', x_draft_id);
709 PO_LOG.proc_end(d_module, 'draft_status', x_draft_status);
710 PO_LOG.proc_end(d_module, 'draft_owner_role', x_draft_owner_role);
711 PO_LOG.proc_end(d_module);
712 END IF;
713
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 x_draft_id := NULL;
717 x_draft_status := NULL;
718 x_draft_owner_role := NULL;
719 WHEN OTHERS THEN
720 x_draft_id := NULL;
721 x_draft_status := NULL;
722 x_draft_owner_role := NULL;
723 END find_draft;
724
725 -----------------------------------------------------------------------
726 --Start of Comments
727 --Name: find_draft
728 --Function: Find a non-completed draft that meets the search criteria.
729 -- This is an overloadded procedure
730 --Parameters:
731 --IN:
732 --p_po_header_id
733 -- document id
734 --IN OUT:
735 --x_draft_id
736 -- The draft id identifying the draft changes.
737 --OUT:
738 --End of Comments
739 ------------------------------------------------------------------------
740
741 PROCEDURE find_draft
742 ( p_po_header_id IN NUMBER,
743 x_draft_id OUT NOCOPY NUMBER
744 ) IS
745 d_api_name CONSTANT VARCHAR2(30) := 'find_draft';
746 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
747 d_position NUMBER;
748
749 l_draft_status PO_DRAFTS.status%TYPE;
750 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
751
752 BEGIN
753 d_position := 0;
754 IF (PO_LOG.d_proc) THEN
755 PO_LOG.proc_begin(d_module);
756 END IF;
757
758 find_draft
759 ( p_po_header_id => p_po_header_id,
760 x_draft_id => x_draft_id,
761 x_draft_status => l_draft_status,
762 x_draft_owner_role => l_draft_owner_role
763 );
764
765 IF (PO_LOG.d_proc) THEN
766 PO_LOG.proc_end(d_module, 'draft_id', x_draft_id);
767 PO_LOG.proc_end(d_module);
768 END IF;
769 END find_draft;
770
771 -----------------------------------------------------------------------
772 --Start of Comments
773 --Name: get_request_id
774 --Function:
775 -- Get request id that is processing the draft
776 --Parameters:
777 --IN:
778 --p_draft_id
779 -- draft unique identifier
780 --IN OUT:
781 --OUT:
782 --x_request_id
783 -- request_id processing the draft
784 --Returns:
785 --End of Comments
786 ------------------------------------------------------------------------
787 PROCEDURE get_request_id
788 ( p_draft_id IN NUMBER,
789 x_request_id OUT NOCOPY NUMBER
790 ) IS
791
792 d_api_name CONSTANT VARCHAR2(30) := 'get_request_id';
793 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
794 d_position NUMBER;
795
796 BEGIN
797 d_position := 0;
798
799 IF (PO_LOG.d_proc) THEN
800 PO_LOG.proc_begin(d_module);
801 END IF;
802
803 SELECT request_id
804 INTO x_request_id
805 FROM po_drafts
806 WHERE draft_id = p_draft_id;
807
808 IF (PO_LOG.d_proc) THEN
809 PO_LOG.proc_end(d_module);
810 END IF;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 PO_MESSAGE_S.add_exc_msg
815 ( p_pkg_name => d_pkg_name,
816 p_procedure_name => d_api_name || '.' || d_position
817 );
818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
819 END get_request_id;
820
821 -----------------------------------------------------------------------
822 --Start of Comments
823 --Name: get_lock_owner_info
824 --Function:
825 -- Gets lock owner and role info for the document in transaction table.
826 -- This
827 --Parameters:
828 --IN:p_po_header_id
829 -- document header id
830 --IN OUT:
831 --OUT:
832 --x_lock_owner_role
833 -- role of the user locking the document
834 --x_lock_owner_user_id
835 -- id of the user having control of the document
836 --End of Comments
837 ------------------------------------------------------------------------
838 PROCEDURE get_lock_owner_info
839 ( p_po_header_id IN NUMBER,
840 x_lock_owner_role OUT NOCOPY VARCHAR2,
841 x_lock_owner_user_id OUT NOCOPY NUMBER
842 ) IS
843 d_api_name CONSTANT VARCHAR2(30) := 'get_lock_owner_info';
844 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
845 d_position NUMBER;
846
847 BEGIN
848
849 d_position := 0;
850 IF (PO_LOG.d_proc) THEN
851 PO_LOG.proc_begin(d_module);
852 END IF;
853
854 --SQL What: get information about the user locking the document
855 --SQL Why: these are the values getting returned
856 SELECT lock_owner_role,
857 lock_owner_user_id
858 INTO x_lock_owner_role,
859 x_lock_owner_user_id
860 FROM po_headers_all
861 WHERE po_header_id = p_po_header_id;
862
863 IF (PO_LOG.d_proc) THEN
864 PO_LOG.proc_end(d_module);
865 END IF;
866
867 EXCEPTION
868 WHEN NO_DATA_FOUND THEN
869 x_lock_owner_role := NULL;
870 x_lock_owner_user_id := NULL;
871
872 END get_lock_owner_info;
873
874 -----------------------------------------------------------------------
875 --Start of Comments
876 --Name: set_lock_owner_info
877 --Function:
878 -- sets lock owner and role info for the document in transaction table.
879 -- This
880 --Parameters:
881 --IN:
882 --p_po_header_id
883 -- document header id
884 --p_role
885 -- role of the user
886 --p_role_user_id
887 -- user id of the user
888 --IN OUT:
889 --OUT:
890 --End of Comments
891 ------------------------------------------------------------------------
892 PROCEDURE set_lock_owner_info
893 ( p_po_header_id IN NUMBER,
894 p_role IN VARCHAR2,
895 p_role_user_id IN NUMBER
896 ) IS
897 d_api_name CONSTANT VARCHAR2(30) := 'set_lock_owner_info';
898 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
899 d_position NUMBER;
900
901 BEGIN
902
903 d_position := 0;
904 IF (PO_LOG.d_proc) THEN
905 PO_LOG.proc_begin(d_module);
906 PO_LOG.proc_begin(d_module, 'p_role', p_role);
907 PO_LOG.proc_begin(d_module, 'p_role_user_id', p_role_user_id);
908 END IF;
909
910 --SQL What: update lock owner role and lock owner id
911 --SQL Why: This is what this procedure is doing
912 UPDATE po_headers_all
913 SET lock_owner_role = p_role,
914 lock_owner_user_id = p_role_user_id
915 WHERE po_header_id = p_po_header_id;
916
917 IF (PO_LOG.d_proc) THEN
918 PO_LOG.proc_end(d_module);
919 END IF;
920 END set_lock_owner_info;
921
922
923 -----------------------------------------------------------------------
924 --Start of Comments
925 --Name: update_permission_check
926 --Function:
927 -- Checks whether user can update the document based on draft and
928 -- functional locking status - This is just a subset of checks to determine
929 -- whether a document can be updated
930 --Parameters:
931 --IN:
932 --p_calling_module
933 -- indicate where this API is invoked from
934 --p_po_header_id
935 -- document header id
936 --p_role
937 -- role of the user
938 --IN OUT:
939 --OUT:
940 --x_update_allowed
941 -- indicates whether user has
942 -- authority to update the document
943 --x_lock_applicable
944 -- returns whether locking is applicable for the document
945 --x_unlock_required
946 -- if update is allowed, checks whether unlock needs to be done before
947 -- updating
948 --x_message
949 -- placeholder for whatever error message that prevents the document
950 -- from being updatable
951 --End of Comments
952 ------------------------------------------------------------------------
953
954 PROCEDURE update_permission_check
955 ( p_calling_module IN VARCHAR2,
956 p_po_header_id IN NUMBER,
957 p_role IN VARCHAR2,
958 p_skip_cat_upload_chk IN VARCHAR2,
959 x_update_allowed OUT NOCOPY VARCHAR2,
960 x_locking_applicable OUT NOCOPY VARCHAR2,
961 x_unlock_required OUT NOCOPY VARCHAR2,
962 x_message OUT NOCOPY VARCHAR2
963 ) IS
964 d_api_name CONSTANT VARCHAR2(30) := 'update_permission_check';
965 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
966 d_position NUMBER;
967
968 l_draft_id PO_DRAFTS.draft_id%TYPE;
969 l_draft_status PO_DRAFTS.status%TYPE;
970 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
971
972 l_calling_module VARCHAR2(100);
973 l_upload_in_progress VARCHAR2(1);
974 l_upload_status_code VARCHAR2(30);
975 l_upload_requestor_role PO_DRAFTS.owner_role%TYPE;
976 l_upload_requestor_role_id PO_DRAFTS.owner_user_id%TYPE;
977 l_upload_job_number NUMBER;
978 l_upload_status_display VARCHAR2(80);
979
980 l_authorization_status PO_HEADERS_ALL.authorization_status%TYPE;
981 l_supplier_auth_enabled PO_HEADERS_ALL.supplier_auth_enabled_flag%TYPE;
982 l_cat_admin_auth_enabled PO_HEADERS_ALL.cat_admin_auth_enabled_flag%TYPE;
983 l_current_lock_owner_role PO_HEADERS_ALL.lock_owner_role%TYPE;
984 l_current_lock_owner_id PO_HEADERS_ALL.lock_owner_user_id%TYPE;
985
986 l_updatable_state VARCHAR2(1); -- bug5532550
987 BEGIN
988
989 d_position := 0;
990 IF (PO_LOG.d_proc) THEN
991 PO_LOG.proc_begin(d_module);
992 END IF;
993
994 x_update_allowed := FND_API.G_TRUE;
995 x_locking_applicable := FND_API.G_TRUE;
996 x_unlock_required := FND_API.G_FALSE;
997
998 -- Default calling module if one is not specified
999 l_calling_module := NVL(p_calling_module, g_call_mod_UNKNOWN);
1000
1001 IF (p_po_header_id IS NULL) THEN
1002 d_position := 10;
1003
1004 IF (PO_LOG.d_stmt) THEN
1005 PO_LOG.stmt (d_module, d_position, 'no po header id. Quitting');
1006 END IF;
1007
1008 -- no document to check. Simply return
1009 RETURN;
1010 END IF;
1011
1012 d_position := 20;
1013 -- search for any non-completed draft
1014 find_draft
1015 ( p_po_header_id => p_po_header_id,
1016 x_draft_id => l_draft_id,
1017 x_draft_status => l_draft_status,
1018 x_draft_owner_role => l_draft_owner_role
1019 );
1020
1021 IF (l_draft_id IS NOT NULL) THEN
1022
1023 IF (PO_LOG.d_stmt) THEN
1024 PO_LOG.stmt (d_module, d_position, 'has active draft');
1025 END IF;
1026
1027 d_position := 30;
1028
1029 -- modules other than HTML UI or PDOI cannot handle draft changes
1030 --<Bug#4382472>
1031 --Added FORMS PO SUMMARY module to the list as we would allow the breaking of the lock from PO Summary
1032 --Form.
1033 -- bug 5358300
1034 -- Added g_call_mod_HTML_UI_SAVE to the list as we would allow online save operations on the document
1035 IF ( l_calling_module NOT IN (g_call_mod_HTML_UI, g_call_mod_HTML_UI_SAVE, g_call_mod_PDOI, g_call_mod_FORMS_PO_SUMMARY)) THEN
1036 x_message := 'PO_DOC_LOCKED';
1037 x_update_allowed := FND_API.G_FALSE;
1038 RETURN;
1039 END IF;
1040
1041 -- Disallow update if one of the following is true
1042 -- 1) status of the draft is 'IN PROCESS', meaning it's pending for buyer
1043 -- acceptance
1044 -- 2) status is PDOI PROCESSING, and the calling module is not PDOI
1045 -- (status = PDOI PROCESSING may mean that there was an unhandled
1046 -- exception during PDOI. Within PDOI there is code to handle
1047 -- such case to recover the document so we do not want to prevent
1048 -- PDOI from processing this document here)
1049 IF (l_draft_status = g_status_IN_PROCESS ) THEN
1050 x_message := 'PO_BUYER_ACCEPTANCE_PENDING';
1051 x_update_allowed := FND_API.G_FALSE;
1052 RETURN;
1053
1054 ELSIF (l_draft_status = g_status_PDOI_PROCESSING AND
1055 l_calling_module <> g_call_mod_PDOI ) THEN
1056
1057 x_message := 'PO_UPLOAD_PENDING_RUNNING';
1058 x_update_allowed := FND_API.G_FALSE;
1059 RETURN;
1060
1061 ELSIF (l_draft_status = g_status_PDOI_ERROR
1062 AND l_calling_module <> g_call_mod_PDOI) THEN
1063
1064 x_message := 'PO_LOCKED_BY_PDOI_ERR';
1065 x_update_allowed := FND_API.G_FALSE;
1066 RETURN;
1067
1068 END IF;
1069 END IF;
1070
1071 d_position := 40;
1072
1073 IF ( NVL(p_skip_cat_upload_chk, FND_API.G_FALSE) = FND_API.G_FALSE) THEN
1074 -- Call iP API to see if there is catalog uploading activity. If so,
1075 -- Prevent locking
1076
1077 -- bug5014131
1078 -- Changed the API call to the one that checks any in progress upload
1079 get_in_process_upload_info
1080 ( p_po_header_id => p_po_header_id,
1081 x_upload_in_progress => l_upload_in_progress,
1082 x_upload_status_code => l_upload_status_code,
1083 x_upload_requestor_role => l_upload_requestor_role,
1084 x_upload_requestor_role_id => l_upload_requestor_role_id,
1085 x_upload_job_number => l_upload_job_number,
1086 x_upload_status_display => l_upload_status_display
1087 );
1088
1089 IF (l_upload_in_progress = FND_API.G_TRUE) THEN
1090 IF (l_upload_status_code IN (g_upload_status_PENDING, g_upload_status_RUNNING)) THEN
1091 IF (PO_LOG.d_stmt) THEN
1092 PO_LOG.stmt (d_module, d_position, 'in progress catalog upload');
1093 END IF;
1094
1095 x_message := 'PO_UPLOAD_PENDING_RUNNING';
1096 x_update_allowed := FND_API.G_FALSE;
1097 RETURN;
1098 ELSIF (l_upload_status_code = g_upload_status_ERROR) THEN
1099 IF (PO_LOG.d_stmt) THEN
1100 PO_LOG.stmt (d_module, d_position, 'errored catalog upload');
1101 END IF;
1102
1103 x_message := 'PO_UPLOAD_ERROR';
1104 --If buyer is making changes and upload had errors and
1105 --upload was done by Supplier/Cat Admin
1106 --then buyer can break the lock
1107 IF (p_role = PO_GLOBAL.g_role_BUYER AND
1108 p_role <> l_upload_requestor_role) THEN
1109 x_unlock_required := FND_API.G_TRUE;
1110 ELSE
1111 x_update_allowed := FND_API.G_FALSE;
1112 END IF;
1113 RETURN;
1114 END IF;
1115 END IF;
1116 END IF;
1117
1118 x_locking_applicable := is_locking_applicable
1119 ( p_po_header_id => p_po_header_id,
1120 p_role => p_role
1121 );
1122
1123 IF (x_locking_applicable = FND_API.G_FALSE) THEN
1124 IF (PO_LOG.d_stmt) THEN
1125 PO_LOG.stmt (d_module, d_position, 'locking is not applicable');
1126 END IF;
1127
1128 -- if locking is not applicable, then we are done with the checks.
1129 -- simply return
1130 RETURN;
1131 END IF;
1132
1133 d_position := 50;
1134
1135 IF (p_role = PO_GLOBAL.g_role_SUPPLIER) THEN
1136 l_supplier_auth_enabled :=
1137 get_supplier_auth_enabled_flag
1138 ( p_po_header_id => p_po_header_id
1139 );
1140
1141 -- supplier is allowed to get the lock only if the document is enabled
1142 -- for supplier authoring
1143 IF (NVL(l_supplier_auth_enabled, 'N') = 'N') THEN
1144 x_message := 'PO_UPDATE_NOT_ALLOWED';
1145 x_update_allowed := FND_API.G_FALSE;
1146 RETURN;
1147 END IF;
1148 END IF;
1149
1150 IF (p_role = PO_GLOBAL.g_role_CAT_ADMIN) THEN
1151 l_cat_admin_auth_enabled :=
1152 get_cat_admin_auth_enable_flag
1153 ( p_po_header_id => p_po_header_id
1154 );
1155
1156 -- cat admin is allowed to get the lock only if the document is enabled
1157 -- for Cat Admin authoring
1158 IF (NVL(l_cat_admin_auth_enabled, 'N') = 'N') THEN
1159 x_message := 'PO_UPDATE_NOT_ALLOWED';
1160 x_update_allowed := FND_API.G_FALSE;
1161 RETURN;
1162 END IF;
1163 END IF;
1164
1165 d_position := 55;
1166
1167 SELECT NVL(authorization_status, 'INCOMPLETE')
1168 INTO l_authorization_status
1169 FROM po_headers_all
1170 WHERE po_header_id = p_po_header_id;
1171
1172 -- do not allow document update by non-buyer role if
1173 -- document is not in INCOMPLETE or APPROVED status
1174 IF ( p_role <> PO_GLOBAL.g_role_BUYER AND
1175 l_authorization_status NOT IN ('INCOMPLETE', 'APPROVED')) THEN
1176
1177 IF (PO_LOG.d_stmt) THEN
1178 PO_LOG.stmt (d_module, d_position, 'auth status = ' ||
1179 l_authorization_status || '. This role cannot update the document ' ||
1180 'in this status');
1181 END IF;
1182
1183 x_message := 'PO_AUTH_STATUS_ERROR';
1184 x_update_allowed := FND_API.G_FALSE;
1185 RETURN;
1186 END IF;
1187
1188 -- bug5532550 START
1189 -- Check and make sure that the document is in a state allowing
1190 -- updates
1191
1192 l_updatable_state := is_doc_in_updatable_state
1193 ( p_po_header_id => p_po_header_id,
1194 p_role => p_role
1195 );
1196
1197 IF ( l_updatable_state = FND_API.G_FALSE ) THEN
1198 x_message := 'PO_ALL_CADM_DOC_CANT_BE_OPENED';
1199 x_update_allowed := FND_API.G_FALSE;
1200 RETURN;
1201 END IF;
1202
1203 -- bug5532550 END
1204
1205 d_position := 60;
1206
1207 get_lock_owner_info
1208 ( p_po_header_id => p_po_header_id,
1209 x_lock_owner_role => l_current_lock_owner_role,
1210 x_lock_owner_user_id => l_current_lock_owner_id
1211 );
1212
1213 IF (PO_LOG.d_stmt) THEN
1214 PO_LOG.stmt (d_module, d_position, 'lock owner role', l_current_lock_owner_role);
1215 PO_LOG.stmt (d_module, d_position, 'lock owner user id', l_current_lock_owner_id);
1216 END IF;
1217
1218 IF ( l_calling_module in (g_call_mod_HTML_UI,g_call_mod_FORMS_PO_SUMMARY)) THEN --<Bug4382472>
1219
1220 d_position := 70;
1221
1222 IF (p_role = l_current_lock_owner_role OR
1223 l_current_lock_owner_role IS NULL) THEN
1224
1225 -- In HTML, a role can update the document if
1226 -- the same role is currently locking the document OR
1227 -- nobody is locking the document
1228
1229 -- In such cases, we can simply take the default value of the
1230 -- update_allowed_flag (FND_API.G_TRUE)
1231 NULL;
1232 ELSIF (p_role = PO_GLOBAL.g_role_BUYER) THEN
1233 x_message := 'PO_DOC_LOCKED_BY_OTHER_ROLE';
1234 -- if role is buyer and the role currently locking the document
1235 -- is different, we need to unlock the document first
1236 x_unlock_required := FND_API.G_TRUE;
1237 RETURN;
1238 ELSE
1239 -- cannot update - role is not BUYER and it is currently locked
1240 -- by somebody else
1241 x_message := 'PO_DOC_LOCKED_BY_OTHER_ROLE';
1242 x_update_allowed := FND_API.G_FALSE;
1243 RETURN;
1244 END IF;
1245
1246 ELSIF (l_calling_module in (g_call_mod_HTML_UI_SAVE)) THEN
1247 -- bug 5358300
1248 -- call during html save operation
1249 -- current role must have lock on document
1250 d_position := 80;
1251
1252 -- If coming from html save operation, nobody has update authority
1253 -- if document is currently locked by some other role
1254
1255 IF (p_role <> l_current_lock_owner_role) THEN
1256 x_message := 'PO_DOC_LOCKED_BY_OTHER_ROLE';
1257 x_update_allowed := FND_API.G_FALSE;
1258 RETURN;
1259 END IF;
1260
1261 ELSE
1262 d_position := 90;
1263
1264 -- If coming from anywhere else, nobody has update authority
1265 -- if document is currently locked by some other role
1266
1267 IF (p_role <> l_current_lock_owner_role) THEN
1268 x_message := 'PO_DOC_LOCKED';
1269 x_update_allowed := FND_API.G_FALSE;
1270 RETURN;
1271 END IF;
1272
1273 END IF;
1274
1275 IF (PO_LOG.d_proc) THEN
1276 PO_LOG.proc_end(d_module);
1277 END IF;
1278
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 PO_MESSAGE_S.add_exc_msg
1282 ( p_pkg_name => d_pkg_name,
1283 p_procedure_name => d_api_name || '.' || d_position
1284 );
1285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1286 END update_permission_check;
1287
1288 -- update_permission_check - 2
1289 PROCEDURE update_permission_check
1290 ( p_calling_module IN VARCHAR2,
1291 p_po_header_id IN NUMBER,
1292 p_role IN VARCHAR2,
1293 p_skip_cat_upload_chk IN VARCHAR2,
1294 x_update_allowed OUT NOCOPY VARCHAR2,
1295 x_locking_applicable OUT NOCOPY VARCHAR2,
1296 x_unlock_required OUT NOCOPY VARCHAR2,
1297 x_message OUT NOCOPY VARCHAR2,
1298 x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
1299 x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000
1300 ) IS
1301
1302 l_num_records NUMBER;
1303 l_style_name PO_DOC_STYLE_HEADERS.style_name%TYPE;
1304
1305 BEGIN
1306 update_permission_check
1307 ( p_calling_module => p_calling_module,
1308 p_po_header_id => p_po_header_id,
1309 p_role => p_role,
1310 p_skip_cat_upload_chk => p_skip_cat_upload_chk,
1311 x_update_allowed => x_update_allowed,
1312 x_locking_applicable => x_locking_applicable,
1313 x_unlock_required => x_unlock_required,
1314 x_message => x_message
1315 );
1316
1317 x_token_name_tbl := PO_TBL_VARCHAR30();
1318 x_token_value_tbl := PO_TBL_VARCHAR2000();
1319
1320 IF (x_update_allowed = FND_API.G_TRUE) THEN
1321 RETURN;
1322 END IF;
1323
1324 l_style_name := PO_DOC_STYLE_PVT.get_style_display_name
1325 (p_doc_id => p_po_header_id);
1326
1327 IF (x_message IN ('PO_DOC_LOCKED', 'PO_UPDATE_NOT_ALLOWED',
1328 'PO_AUTH_STATUS_ERROR', 'PO_DOC_LOCKED_BY_OTHER_ROLE',
1329 'PO_UPLOAD_PENDING_RUNNING', 'PO_UPLOAD_ERROR',
1330 'PO_BUYER_ACCEPTANCE_PENDING',
1331 'PO_LOCKED_BY_PDOI_ERR')) THEN
1332 x_token_name_tbl := PO_TBL_VARCHAR30 ('STYLE_NAME');
1333 x_token_value_tbl := PO_TBL_VARCHAR2000 (l_style_name);
1334 END IF;
1335
1336 END update_permission_check;
1337
1338 -- update_permission_check - 3
1339 PROCEDURE update_permission_check
1340 ( p_calling_module IN VARCHAR2,
1341 p_po_header_id IN NUMBER,
1342 p_role IN VARCHAR2,
1343 p_skip_cat_upload_chk IN VARCHAR2,
1344 x_update_allowed OUT NOCOPY VARCHAR2,
1345 x_locking_applicable OUT NOCOPY VARCHAR2,
1346 x_unlock_required OUT NOCOPY VARCHAR2,
1347 x_message OUT NOCOPY VARCHAR2,
1348 x_message_text OUT NOCOPY VARCHAR2
1349 ) IS
1350
1351 l_token_name_tbl PO_TBL_VARCHAR30;
1352 l_token_value_tbl PO_TBL_VARCHAR2000;
1353
1354 BEGIN
1355 update_permission_check
1356 ( p_calling_module => p_calling_module,
1357 p_po_header_id => p_po_header_id,
1358 p_role => p_role,
1359 p_skip_cat_upload_chk => p_skip_cat_upload_chk,
1360 x_update_allowed => x_update_allowed,
1361 x_locking_applicable => x_locking_applicable,
1362 x_unlock_required => x_unlock_required,
1363 x_message => x_message,
1364 x_token_name_tbl => l_token_name_tbl,
1365 x_token_value_tbl => l_token_value_tbl
1366 );
1367
1368 IF (x_update_allowed = FND_API.G_TRUE) THEN
1369 RETURN;
1370 END IF;
1371
1372 FND_MESSAGE.set_name ('PO', x_message);
1373 FOR i IN 1..l_token_name_tbl.COUNT LOOP
1374 FND_MESSAGE.set_token (l_token_name_tbl(i), l_token_value_tbl(i));
1375 END LOOP;
1376
1377 x_message_text := FND_MESSAGE.get;
1378
1379 END update_permission_check;
1380
1381
1382 -----------------------------------------------------------------------
1383 --Start of Comments
1384 --Name: unlock_document
1385 --Function: unlock the document by setting role and role id to null
1386 --Parameters:
1387 --IN:
1388 --p_po_header_id
1389 --document header id
1390 --IN OUT:
1391 --OUT:
1392 --End of Comments
1393 ------------------------------------------------------------------------
1394
1395 PROCEDURE unlock_document
1396 ( p_po_header_id IN NUMBER
1397 ) IS
1398 d_api_name CONSTANT VARCHAR2(30) := 'unlock_document';
1399 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1400 d_position NUMBER;
1401
1402 l_draft_id PO_DRAFTS.draft_id%TYPE;
1403 l_draft_status PO_DRAFTS.status%TYPE;
1404 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
1405
1406 l_return_status VARCHAR2(1);
1407
1408 BEGIN
1409
1410 d_position := 0;
1411 IF (PO_LOG.d_proc) THEN
1412 PO_LOG.proc_begin(d_module);
1413 END IF;
1414
1415 find_draft
1416 ( p_po_header_id => p_po_header_id,
1417 x_draft_id => l_draft_id,
1418 x_draft_status => l_draft_status,
1419 x_draft_owner_role => l_draft_owner_role
1420 );
1421
1422 IF ( l_draft_id IS NOT NULL ) THEN
1423 d_position := 10;
1424
1425 IF (PO_LOG.d_stmt) THEN
1426 PO_LOG.stmt(d_module, d_position, 'need to remove draft changes');
1427 END IF;
1428
1429 PO_DRAFTS_PVT.remove_draft_changes
1430 ( p_draft_id => l_draft_id,
1431 p_exclude_ctrl_tbl => FND_API.G_FALSE,
1432 x_return_status => l_return_status
1433 );
1434
1435 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1437 END IF;
1438 END IF;
1439
1440 -- unlock document by setting lock owner role to NULL
1441 set_lock_owner_info
1442 ( p_po_header_id => p_po_header_id,
1443 p_role => NULL,
1444 p_role_user_id => NULL
1445 );
1446
1447 IF (PO_LOG.d_proc) THEN
1448 PO_LOG.proc_end(d_module);
1449 END IF;
1450
1451 END unlock_document;
1452
1453
1454
1455 -----------------------------------------------------------------------
1456 --Start of Comments
1457 --Name: lock_document
1458 --Function:
1459 -- Set document lock of the document. it first unlocks the document, removes
1460 -- draft changes before locking
1461 --Parameters:
1462 --IN:
1463 --p_po_header_id
1464 -- document header id
1465 --p_role
1466 -- role of the user
1467 --p_role_user_id
1468 -- role id of the user
1469 --p_unlock_current
1470 -- indicates whether the document needs to go through draft cleanup
1471 --IN OUT:
1472 --OUT:
1473 --RETURNS
1474 --
1475 --End of Comments
1476 ------------------------------------------------------------------------
1477 PROCEDURE lock_document
1478 ( p_po_header_id IN NUMBER,
1479 p_role IN VARCHAR2,
1480 p_role_user_id IN NUMBER,
1481 p_unlock_current IN VARCHAR2
1482 ) IS
1483 d_api_name CONSTANT VARCHAR2(30) := 'lock_document';
1484 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1485 d_position NUMBER;
1486
1487 l_locking_applicable VARCHAR2(1);
1488 BEGIN
1489
1490 d_position := 0;
1491 IF (PO_LOG.d_proc) THEN
1492 PO_LOG.proc_begin(d_module);
1493 END IF;
1494
1495 l_locking_applicable := is_locking_applicable
1496 ( p_po_header_id => p_po_header_id,
1497 p_role => p_role
1498 );
1499
1500 IF (l_locking_applicable = FND_API.G_FALSE) THEN
1501 RETURN;
1502 END IF;
1503
1504 IF (p_unlock_current = FND_API.G_TRUE) THEN
1505 unlock_document
1506 ( p_po_header_id => p_po_header_id
1507 );
1508 END IF;
1509
1510 d_position := 10;
1511 set_lock_owner_info
1512 ( p_po_header_id => p_po_header_id,
1513 p_role => p_role,
1514 p_role_user_id => p_role_user_id
1515 );
1516
1517 IF (PO_LOG.d_proc) THEN
1518 PO_LOG.proc_end(d_module);
1519 END IF;
1520 END lock_document;
1521
1522
1523 -----------------------------------------------------------------------
1524 --Start of Comments
1525 --Name: is_locking_applicable
1526 --Function:
1527 -- check whether locking of the document is required
1528 --Parameters:
1529 --IN:
1530 --p_po_header_id
1531 -- document header id
1532 --p_role
1533 -- role of the user
1534 --End of Comments
1535 ------------------------------------------------------------------------
1536 FUNCTION is_locking_applicable
1537 ( p_po_header_id IN NUMBER,
1538 p_role IN VARCHAR2
1539 ) RETURN VARCHAR2 IS
1540
1541 d_api_name CONSTANT VARCHAR2(30) := 'is_locking_applicable';
1542 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1543 d_position NUMBER;
1544
1545 l_applicable VARCHAR2(1) := FND_API.G_TRUE;
1546
1547 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
1548 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
1549 l_approved_date PO_HEADERS_ALL.approved_date%TYPE;
1550 l_current_lock_owner_role PO_HEADERS_ALL.lock_owner_role%TYPE;
1551
1552 BEGIN
1553
1554 d_position := 0;
1555 IF (PO_LOG.d_proc) THEN
1556 PO_LOG.proc_begin(d_module);
1557 END IF;
1558
1559 -- SQL What: Get several columns from PO tables
1560 -- SQL Why: Need all these columns to determine whether locking
1561 -- is applicable
1562 SELECT type_lookup_code,
1563 NVL(global_agreement_flag, 'N'),
1564 approved_date,
1565 lock_owner_role
1566 INTO l_type_lookup_code,
1567 l_ga_flag,
1568 l_approved_date,
1569 l_current_lock_owner_role
1570 FROM po_headers_all
1571 WHERE po_header_id = p_po_header_id;
1572
1573 -- locking is applicable only for global blanket agreement
1574 IF (NOT (l_type_lookup_code = 'BLANKET' AND l_ga_flag = 'Y')) THEN
1575
1576 l_applicable := FND_API.G_FALSE;
1577
1578 ELSIF ( l_current_lock_owner_role IS NULL AND
1579 p_role = PO_GLOBAL.g_role_BUYER AND
1580 l_approved_date IS NULL ) THEN
1581
1582 l_applicable := FND_API.G_FALSE;
1583 END IF;
1584
1585 IF (PO_LOG.d_proc) THEN
1586 PO_LOG.proc_end(d_module, 'l_applicable', l_applicable);
1587 END IF;
1588
1589 RETURN l_applicable;
1590
1591 EXCEPTION
1592 WHEN NO_DATA_FOUND THEN
1593 l_applicable := FND_API.G_FALSE;
1594 RETURN l_applicable;
1595 END is_locking_applicable;
1596
1597 -----------------------------------------------------------------------
1598 --Start of Comments
1599 --Name: is_draft_applicable
1600 --Function:
1601 -- check whether the document can have pending drafts
1602 --Parameters:
1603 --IN:
1604 --p_po_header_id
1605 -- document header id
1606 --p_role
1607 -- role of the user
1608 --End of Comments
1609 ------------------------------------------------------------------------
1610 FUNCTION is_draft_applicable
1611 ( p_po_header_id IN NUMBER,
1612 p_role IN VARCHAR2
1613 ) RETURN VARCHAR2 IS
1614
1615 d_api_name CONSTANT VARCHAR2(30) := 'is_draft_applicable';
1616 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1617 d_position NUMBER;
1618
1619 l_applicable VARCHAR2(1) := FND_API.G_TRUE;
1620
1621 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
1622 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
1623 BEGIN
1624 d_position := 0;
1625
1626 IF (PO_LOG.d_proc) THEN
1627 PO_LOG.proc_begin(d_module);
1628 END IF;
1629
1630 -- SQL What: Get several columns from PO tables
1631 -- SQL Why: Need all these columns to determine whether locking
1632 -- is applicable
1633 SELECT type_lookup_code,
1634 NVL(global_agreement_flag, 'N')
1635 INTO l_type_lookup_code,
1636 l_ga_flag
1637 FROM po_headers_all
1638 WHERE po_header_id = p_po_header_id;
1639
1640 d_position := 10;
1641
1642 -- locking is applicable only for global blanket agreement
1643 IF (NOT (l_type_lookup_code = 'BLANKET' AND l_ga_flag = 'Y')) THEN
1644
1645 l_applicable := FND_API.G_FALSE;
1646
1647 ELSIF ( p_role = PO_GLOBAL.g_role_BUYER) THEN
1648
1649 l_applicable := FND_API.G_FALSE;
1650 END IF;
1651
1652 IF (PO_LOG.d_proc) THEN
1653 PO_LOG.proc_end(d_module, 'l_applicable', l_applicable);
1654 END IF;
1655
1656 RETURN l_applicable;
1657
1658 EXCEPTION
1659 WHEN NO_DATA_FOUND THEN
1660 l_applicable := FND_API.G_FALSE;
1661 RETURN l_applicable;
1662 END is_draft_applicable;
1663
1664
1665
1666 -----------------------------------------------------------------------
1667 --Start of Comments
1668 --Name: lock_document_with_validate
1669 --Function:
1670 -- Same as lock_document, except that it performs update_permission_check
1671 -- procedure before going to lock_document procedure.
1672 --Parameters:
1673 --IN:
1674 --p_calling_module
1675 -- indicates where the procedure is called from
1676 --p_po_header_id
1677 -- document header id
1678 --p_role
1679 -- role of the user
1680 --p_role_user_id
1681 -- role id of the user
1682 --OUT:
1683 --x_locking_allowed
1684 -- indicate whether locking was permitted
1685 --x_message
1686 -- error message when locking has not been permitted
1687 --End of Comments
1688 ------------------------------------------------------------------------
1689
1690 PROCEDURE lock_document_with_validate
1691 ( p_calling_module IN VARCHAR2,
1692 p_po_header_id IN NUMBER,
1693 p_role IN VARCHAR2,
1694 p_role_user_id IN NUMBER,
1695 x_locking_allowed OUT NOCOPY VARCHAR2,
1696 x_message OUT NOCOPY VARCHAR2,
1697 x_message_text OUT NOCOPY VARCHAR2
1698 ) IS
1699 d_api_name CONSTANT VARCHAR2(30) := 'lock_document_with_validate';
1700 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1701 d_position NUMBER;
1702
1703 l_message VARCHAR2(2000);
1704 l_locking_applicable VARCHAR2(1);
1705 l_unlock_required VARCHAR2(1);
1706
1707 BEGIN
1708
1709 d_position := 0;
1710 IF (PO_LOG.d_proc) THEN
1711 PO_LOG.proc_begin(d_module);
1712 END IF;
1713
1714 update_permission_check
1715 ( p_calling_module => p_calling_module,
1716 p_po_header_id => p_po_header_id,
1717 p_role => p_role,
1718 x_update_allowed => x_locking_allowed,
1719 x_locking_applicable => l_locking_applicable,
1720 x_unlock_required => l_unlock_required,
1721 x_message => x_message,
1722 x_message_text => x_message_text
1723 );
1724
1725
1726 -- if locking is not allowed, do not need to continue to lock the
1727 -- document
1728 IF (l_locking_applicable = FND_API.G_FALSE OR
1729 x_locking_allowed = FND_API.G_FALSE) THEN
1730 RETURN;
1731 END IF;
1732
1733 d_position := 10;
1734
1735 lock_document
1736 ( p_po_header_id => p_po_header_id,
1737 p_role => p_role,
1738 p_role_user_id => p_role_user_id,
1739 p_unlock_current => l_unlock_required
1740 );
1741
1742 IF (PO_LOG.d_proc) THEN
1743 PO_LOG.proc_end(d_module);
1744 END IF;
1745 END lock_document_with_validate;
1746
1747 -----------------------------------------------------------------------
1748 --Start of Comments
1749 --Name: update_draft_status
1750 --Function:
1751 -- updates status of the draft
1752 --Parameters:
1753 --IN:
1754 --p_draft_id
1755 -- draft id of the pending changes
1756 --End of Comments
1757 ------------------------------------------------------------------------
1758
1759 PROCEDURE update_draft_status
1760 ( p_draft_id IN NUMBER,
1761 p_new_status IN VARCHAR2
1762 ) IS
1763 d_api_name CONSTANT VARCHAR2(30) := 'update_draft_status';
1764 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1765 d_position NUMBER;
1766
1767 l_cur_conc_request_id NUMBER := FND_GLOBAL.conc_request_id;
1768 BEGIN
1769
1770 d_position := 0;
1771 IF (PO_LOG.d_proc) THEN
1772 PO_LOG.proc_begin(d_module);
1773 END IF;
1774
1775 -- for request id, populate only if the draft is being processed by PDOI
1776 UPDATE po_drafts
1777 SET status = p_new_status,
1778 request_id = DECODE (p_new_status,
1779 g_status_PDOI_PROCESSING, l_cur_conc_request_id,
1780 NULL)
1781 WHERE draft_id = p_draft_id;
1782
1783 IF (PO_LOG.d_proc) THEN
1784 PO_LOG.proc_end(d_module);
1785 END IF;
1786
1787 END update_draft_status;
1788
1789 -----------------------------------------------------------------------
1790 --Start of Comments
1791 --Name: pending_changes_exist
1792 --Function:
1793 -- check whether a non-completed draft exists in the system
1794 --Parameters:
1795 --IN:
1796 --p_po_header_id
1797 -- document id of the record
1798 --End of Comments
1799 ------------------------------------------------------------------------
1800
1801 FUNCTION pending_changes_exist
1802 ( p_po_header_id IN NUMBER
1803 ) RETURN VARCHAR2 IS
1804
1805 d_api_name CONSTANT VARCHAR2(30) := 'pending_changes_exist';
1806 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1807 d_position NUMBER;
1808
1809 l_draft_id PO_DRAFTS.draft_id%TYPE;
1810 l_draft_status PO_DRAFTS.status%TYPE;
1811 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
1812
1813 l_pending_changes_exist VARCHAR2(1) := FND_API.G_FALSE;
1814 BEGIN
1815
1816 d_position := 0;
1817 IF (PO_LOG.d_proc) THEN
1818 PO_LOG.proc_begin(d_module);
1819 END IF;
1820
1821 find_draft
1822 ( p_po_header_id => p_po_header_id,
1823 x_draft_id => l_draft_id,
1824 x_draft_status => l_draft_status,
1825 x_draft_owner_role => l_draft_owner_role
1826 );
1827
1828 IF (l_draft_id IS NOT NULL) THEN
1829 l_pending_changes_exist := FND_API.G_TRUE;
1830 END IF;
1831
1832 IF (PO_LOG.d_proc) THEN
1833 PO_LOG.proc_end(d_module, 'l_pending_changes_exist', l_pending_changes_exist);
1834 END IF;
1835
1836 RETURN l_pending_changes_exist;
1837
1838 END pending_changes_exist;
1839
1840 -----------------------------------------------------------------------
1841 --Start of Comments
1842 --Name: changes_exist_for_draft
1843 --Function:
1844 -- Given the draft id table, check whether there exist any draft changes
1845 -- at any entity for each draft id. FND_API.G_TRUE will be populated to
1846 -- the corresponding entry in the returned table
1847 --Parameters:
1848 --IN:
1849 --p_draft_id_tbl
1850 -- draft id table
1851 --RETURN
1852 -- list of VARCHAR2(1) indicating whether the draft id has draft changes
1853 -- at any entity level for the corrsponding entry in p_draft_id_tbl
1854 --End of Comments
1855 ------------------------------------------------------------------------
1856 FUNCTION changes_exist_for_draft
1857 ( p_draft_id_tbl PO_TBL_NUMBER
1858 ) RETURN PO_TBL_VARCHAR1 IS
1859
1860 d_api_name CONSTANT VARCHAR2(30) := 'changes_exist_for_draft';
1861 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1862 d_position NUMBER;
1863
1864 l_null_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1865
1866 l_master_chg_exist_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1867 l_chg_exist_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1868 BEGIN
1869
1870 d_position := 0;
1871
1872 IF (PO_LOG.d_proc) THEN
1873 PO_LOG.proc_begin (d_module);
1874 END IF;
1875
1876 l_null_id_tbl.EXTEND(p_draft_id_tbl.COUNT);
1877 l_master_chg_exist_tbl.EXTEND(p_draft_id_tbl.COUNT);
1878 l_chg_exist_tbl.EXTEND(p_draft_id_tbl.COUNT);
1879
1880 d_position := 10;
1881
1882 -- check if header gets changed
1883 l_master_chg_exist_tbl :=
1884 PO_HEADERS_DRAFT_PVT.draft_changes_exist
1885 ( p_draft_id_tbl => p_draft_id_tbl,
1886 p_po_header_id_tbl => l_null_id_tbl
1887 );
1888
1889 d_position := 20;
1890 -- check if any line gets changed
1891 l_chg_exist_tbl :=
1892 PO_LINES_DRAFT_PVT.draft_changes_exist
1893 ( p_draft_id_tbl => p_draft_id_tbl,
1894 p_po_line_id_tbl => l_null_id_tbl
1895 );
1896
1897 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1898 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1899 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1900 END IF;
1901 END LOOP;
1902
1903 d_position := 30;
1904 -- check if any line location gets changed
1905 l_chg_exist_tbl :=
1906 PO_LINE_LOCATIONS_DRAFT_PVT.draft_changes_exist
1907 ( p_draft_id_tbl => p_draft_id_tbl,
1908 p_line_location_id_tbl => l_null_id_tbl
1909 );
1910
1911 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1912 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1913 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1914 END IF;
1915 END LOOP;
1916
1917 d_position := 40;
1918 -- check if any distribution gets changed
1919 l_chg_exist_tbl :=
1920 PO_DISTRIBUTIONS_DRAFT_PVT.draft_changes_exist
1921 ( p_draft_id_tbl => p_draft_id_tbl,
1922 p_po_distribution_id_tbl => l_null_id_tbl
1923 );
1924
1925 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1926 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1927 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1928 END IF;
1929 END LOOP;
1930
1931 d_position := 50;
1932 -- check if any org assignment gets changed
1933 l_chg_exist_tbl :=
1934 PO_GA_ORG_ASSIGN_DRAFT_PVT.draft_changes_exist
1935 ( p_draft_id_tbl => p_draft_id_tbl,
1936 p_org_assignment_id_tbl => l_null_id_tbl
1937 );
1938
1939 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1940 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1941 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1942 END IF;
1943 END LOOP;
1944
1945 d_position := 60;
1946 -- check if price differentials gets changed
1947 l_chg_exist_tbl :=
1948 PO_PRICE_DIFF_DRAFT_PVT.draft_changes_exist
1949 ( p_draft_id_tbl => p_draft_id_tbl,
1950 p_price_differential_id_tbl => l_null_id_tbl
1951 );
1952
1953 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1954 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1955 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1956 END IF;
1957 END LOOP;
1958
1959 d_position := 70;
1960 -- check if any notification control gets changed
1961 l_chg_exist_tbl :=
1962 PO_NOTIFICATION_CTRL_DRAFT_PVT.draft_changes_exist
1963 ( p_draft_id_tbl => p_draft_id_tbl,
1964 p_notification_id_tbl => l_null_id_tbl
1965 );
1966
1967 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1968 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1969 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1970 END IF;
1971 END LOOP;
1972
1973 d_position := 80;
1974 -- check if any attribute values record gets changed
1975 l_chg_exist_tbl :=
1976 PO_ATTR_VALUES_DRAFT_PVT.draft_changes_exist
1977 ( p_draft_id_tbl => p_draft_id_tbl,
1978 p_attribute_values_id_tbl => l_null_id_tbl
1979 );
1980
1981 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1982 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1983 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1984 END IF;
1985 END LOOP;
1986
1987 d_position := 90;
1988 -- check if any attribute values record gets changed
1989 l_chg_exist_tbl :=
1990 PO_ATTR_VALUES_TLP_DRAFT_PVT.draft_changes_exist
1991 ( p_draft_id_tbl => p_draft_id_tbl,
1992 p_attribute_values_tlp_id_tbl => l_null_id_tbl
1993 );
1994
1995 FOR i IN 1..l_master_chg_exist_tbl.COUNT LOOP
1996 IF (l_chg_exist_tbl(i) = FND_API.G_TRUE) THEN
1997 l_master_chg_exist_tbl(i) := FND_API.G_TRUE;
1998 END IF;
1999 END LOOP;
2000
2001 IF (PO_LOG.d_proc) THEN
2002 PO_LOG.proc_end (d_module);
2003 END IF;
2004
2005 RETURN l_master_chg_exist_tbl;
2006
2007 END changes_exist_for_draft;
2008
2009
2010
2011
2012 -----------------------------------------------------------------------
2013 --Start of Comments
2014 --Name: is_pending_buyer_acceptance
2015 --Function:
2016 -- checks whether the draft changes have been submitted for buyer acceptance
2017 --Parameters:
2018 --IN:
2019 --p_po_header_id
2020 -- document header id.
2021 --End of Comments
2022 ------------------------------------------------------------------------
2023
2024 FUNCTION is_pending_buyer_acceptance
2025 ( p_po_header_id IN NUMBER
2026 ) RETURN VARCHAR2 IS
2027
2028 d_api_name CONSTANT VARCHAR2(30) := 'is_pending_buyer_acceptance';
2029 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2030 d_position NUMBER;
2031
2032 l_draft_id PO_DRAFTS.draft_id%TYPE;
2033 l_draft_status PO_DRAFTS.status%TYPE;
2034 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
2035
2036 l_pending_acceptance VARCHAR2(1) := FND_API.G_FALSE;
2037 BEGIN
2038
2039 d_position := 0;
2040 IF (PO_LOG.d_proc) THEN
2041 PO_LOG.proc_begin(d_module);
2042 END IF;
2043
2044 find_draft
2045 ( p_po_header_id => p_po_header_id,
2046 x_draft_id => l_draft_id,
2047 x_draft_status => l_draft_status,
2048 x_draft_owner_role => l_draft_owner_role
2049 );
2050
2051 IF (l_draft_status = g_status_IN_PROCESS) THEN
2052 l_pending_acceptance := FND_API.G_TRUE;
2053 END IF;
2054
2055 IF (PO_LOG.d_proc) THEN
2056 PO_LOG.proc_end(d_module, 'l_pending_acceptance', l_pending_acceptance);
2057 END IF;
2058
2059 RETURN l_pending_acceptance;
2060
2061 END is_pending_buyer_acceptance;
2062
2063
2064 -----------------------------------------------------------------------
2065 --Start of Comments
2066 --Name: lock_merge_view_records
2067 --Function:
2068 -- given the table id and draft id, obtain DB lock for the draft and
2069 -- transaciton table
2070 --Parameters:
2071 --IN:
2072 --p_view_name
2073 -- View name of the merge view
2074 --p_entity_id
2075 -- primary key of the transaction table.
2076 --p_draft_id
2077 -- draft unique identifier
2078 --RETURN:
2079 -- a flag indicating whether there are problems during locking
2080 --End of Comments
2081 ------------------------------------------------------------------------
2082
2083 FUNCTION lock_merge_view_records
2084 ( p_view_name IN VARCHAR2,
2085 p_entity_id IN NUMBER,
2086 p_draft_id IN NUMBER
2087 ) RETURN VARCHAR2 IS
2088
2089 d_api_name CONSTANT VARCHAR2(30) := 'lock_merge_view_records';
2090 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2091 d_position NUMBER;
2092
2093 -- Assign exception with a name
2094 INVALID_VIEW_NAME_EXC EXCEPTION;
2095 RESOURCE_BUSY_EXC EXCEPTION;
2096 PRAGMA EXCEPTION_INIT (RESOURCE_BUSY_EXC, -54);
2097
2098 l_success VARCHAR2(1) := FND_API.G_TRUE;
2099
2100 BEGIN
2101
2102 d_position := 0;
2103 IF (PO_LOG.d_proc) THEN
2104 PO_LOG.proc_begin(d_module);
2105 END IF;
2106
2107 IF (p_view_name = 'PO_HEADERS_MERGE_V') THEN
2108 d_position := 10;
2109
2110 PO_HEADERS_DRAFT_PKG.lock_draft_record
2111 ( p_po_header_id => p_entity_id,
2112 p_draft_id => p_draft_id
2113 );
2114
2115 PO_HEADERS_DRAFT_PKG.lock_transaction_record
2116 ( p_po_header_id => p_entity_id
2117 );
2118
2119 ELSIF (p_view_name = 'PO_LINES_MERGE_V') THEN
2120 d_position := 20;
2121
2122 PO_LINES_DRAFT_PKG.lock_draft_record
2123 ( p_po_line_id => p_entity_id,
2124 p_draft_id => p_draft_id
2125 );
2126
2127 PO_LINES_DRAFT_PKG.lock_transaction_record
2128 ( p_po_line_id => p_entity_id
2129 );
2130
2131 ELSIF (p_view_name = 'PO_LINE_LOCATIONS_MERGE_V') THEN
2132 d_position := 30;
2133
2134 PO_LINE_LOCATIONS_DRAFT_PKG.lock_draft_record
2135 ( p_line_location_id => p_entity_id,
2136 p_draft_id => p_draft_id
2137 );
2138
2139 PO_LINE_LOCATIONS_DRAFT_PKG.lock_transaction_record
2140 ( p_line_location_id => p_entity_id
2141 );
2142
2143 ELSIF (p_view_name = 'PO_DISTRIBUTIONS_MERGE_V') THEN
2144 d_position := 40;
2145
2146 PO_DISTRIBUTIONS_DRAFT_PKG.lock_draft_record
2147 ( p_po_distribution_id => p_entity_id,
2148 p_draft_id => p_draft_id
2149 );
2150
2151 PO_DISTRIBUTIONS_DRAFT_PKG.lock_transaction_record
2152 ( p_po_distribution_id => p_entity_id
2153 );
2154
2155 ELSIF (p_view_name = 'PO_GA_ORG_ASSIGN_MERGE_V') THEN
2156 d_position := 50;
2157
2158 PO_GA_ORG_ASSIGN_DRAFT_PKG.lock_draft_record
2159 ( p_org_assignment_id => p_entity_id,
2160 p_draft_id => p_draft_id
2161 );
2162
2163 PO_GA_ORG_ASSIGN_DRAFT_PKG.lock_transaction_record
2164 ( p_org_assignment_id => p_entity_id
2165 );
2166
2167 ELSIF (p_view_name = 'PO_PRICE_DIFF_MERGE_V') THEN
2168 d_position := 60;
2169
2170 PO_PRICE_DIFF_DRAFT_PKG.lock_draft_record
2171 ( p_price_differential_id => p_entity_id,
2172 p_draft_id => p_draft_id
2173 );
2174
2175 PO_PRICE_DIFF_DRAFT_PKG.lock_transaction_record
2176 ( p_price_differential_id => p_entity_id
2177 );
2178
2179 ELSIF (p_view_name = 'PO_NOTIFICATION_CTRL_MERGE_V') THEN
2180 d_position := 70;
2181
2182 PO_NOTIFICATION_CTRL_DRAFT_PKG.lock_draft_record
2183 ( p_notification_id => p_entity_id,
2184 p_draft_id => p_draft_id
2185 );
2186
2187 PO_NOTIFICATION_CTRL_DRAFT_PKG.lock_transaction_record
2188 ( p_notification_id => p_entity_id
2189 );
2190
2191 ELSIF (p_view_name = 'PO_ATTR_VALUES_MERGE_V') THEN
2192 d_position := 80;
2193
2194 PO_ATTR_VALUES_DRAFT_PKG.lock_draft_record
2195 ( p_attribute_values_id => p_entity_id,
2196 p_draft_id => p_draft_id
2197 );
2198
2199 PO_ATTR_VALUES_DRAFT_PKG.lock_transaction_record
2200 ( p_attribute_values_id => p_entity_id
2201 );
2202
2203 ELSIF (p_view_name = 'PO_ATTR_VALUES_TLP_MERGE_V') THEN
2204 d_position := 90;
2205
2206 PO_ATTR_VALUES_TLP_DRAFT_PKG.lock_draft_record
2207 ( p_attribute_values_tlp_id => p_entity_id,
2208 p_draft_id => p_draft_id
2209 );
2210
2211 PO_ATTR_VALUES_TLP_DRAFT_PKG.lock_transaction_record
2212 ( p_attribute_values_tlp_id => p_entity_id
2213 );
2214
2215 ELSE
2216 d_position := 100;
2217
2218 RAISE INVALID_VIEW_NAME_EXC;
2219 END IF;
2220
2221 IF (PO_LOG.d_proc) THEN
2222 PO_LOG.proc_end(d_module);
2223 END IF;
2224
2225 RETURN FND_API.G_TRUE;
2226
2227 EXCEPTION
2228 WHEN RESOURCE_BUSY_EXC THEN
2229 -- come here if database locking cannot be acquired
2230 l_success := FND_API.G_FALSE;
2231 RETURN FND_API.G_FALSE;
2232
2233 WHEN INVALID_VIEW_NAME_EXC THEN
2234 IF (PO_LOG.d_exc) THEN
2235 PO_LOG.exc(d_module, d_position, 'Invalid view name');
2236 END IF;
2237
2238 l_success := FND_API.G_FALSE;
2239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2240 WHEN OTHERS THEN
2241 l_success := FND_API.G_FALSE;
2242
2243 PO_MESSAGE_S.add_exc_msg
2244 ( p_pkg_name => d_pkg_name,
2245 p_procedure_name => d_api_name || '.' || d_position
2246 );
2247
2248 RAISE;
2249 END lock_merge_view_records;
2250
2251
2252
2253
2254 -----------------------------------------------------------------------
2255 --Start of Comments
2256 --Name: get_supplier_auth_enabled_flag
2257 --Pre-reqs: None
2258 --Modifies:
2259 --Locks:
2260 -- None
2261 --Function:
2262 -- Returns back the supp_auth_enabled_flag value
2263 --Parameters:
2264 --IN:
2265 --p_po_header_id
2266 -- header_id of the document
2267 --IN OUT:
2268 --OUT:
2269 --Returns:
2270 --Notes:
2271 --Testing:
2272 --End of Comments
2273 ------------------------------------------------------------------------
2274 FUNCTION get_supplier_auth_enabled_flag
2275 (p_po_header_id IN NUMBER
2276 ) RETURN VARCHAR2 IS
2277
2278 d_api_name CONSTANT VARCHAR2(30) := 'get_supplier_authoring_status';
2279 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2280 d_position NUMBER;
2281 l_supplier_auth_enabled_flag VARCHAR2(1);
2282
2283 BEGIN
2284
2285 d_position := 0;
2286 IF (PO_LOG.d_proc) THEN
2287 PO_LOG.proc_begin(d_module);
2288 END IF;
2289
2290 SELECT supplier_auth_enabled_flag
2291 INTO l_supplier_auth_enabled_flag
2292 FROM po_headers_all
2293 WHERE po_header_id = p_po_header_id;
2294
2295 return l_supplier_auth_enabled_flag;
2296
2297 EXCEPTION
2298 WHEN no_data_found THEN
2299 RETURN NULL;
2300 WHEN others THEN
2301 po_message_s.sql_error('get_supp_auth_enabled_flag',d_position, sqlcode);
2302 raise;
2303
2304 END get_supplier_auth_enabled_flag;
2305
2306 -----------------------------------------------------------------------
2307 --Start of Comments
2308 --Name: set_supplier_auth_enabled_flag
2309 --Pre-reqs: None
2310 --Modifies:
2311 --Locks:
2312 -- None
2313 --Function:
2314 -- Sets the supp_auth_enabled_flag value
2315 --Parameters:
2316 --IN:
2317 --p_po_header_id
2318 -- header_id of the document
2319 --p_supplier_auth_enabled_flag
2320 -- the value to set
2321 --IN OUT:
2322 --OUT:
2323 --Returns:
2324 --Notes:
2325 --Testing:
2326 --End of Comments
2327 ------------------------------------------------------------------------
2328 FUNCTION set_supplier_auth_enabled_flag
2329 (p_po_header_id IN NUMBER,
2330 p_supplier_auth_enabled_flag IN VARCHAR2
2331 ) RETURN VARCHAR2 IS
2332
2333 d_api_name CONSTANT VARCHAR2(30) := 'set_supplier_authoring_status';
2334 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2335 d_position NUMBER;
2336
2337 BEGIN
2338
2339 d_position := 0;
2340 IF (PO_LOG.d_proc) THEN
2341 PO_LOG.proc_begin(d_module);
2342 END IF;
2343
2344 UPDATE po_headers_all
2345 SET supplier_auth_enabled_flag
2346 = p_supplier_auth_enabled_flag
2347 WHERE po_header_id = p_po_header_id;
2348
2349 return FND_API.G_RET_STS_SUCCESS;
2350
2351 EXCEPTION
2352 WHEN no_data_found THEN
2353 RETURN FND_API.G_RET_STS_ERROR;
2354 WHEN others THEN
2355 po_message_s.sql_error('get_supp_auth_enabled_flag',d_position, sqlcode);
2356 raise;
2357
2358 END set_supplier_auth_enabled_flag;
2359
2360 -----------------------------------------------------------------------
2361 --Start of Comments
2362 --Name: get_cat_admin_auth_enable_flag
2363 --Pre-reqs: None
2364 --Modifies:
2365 --Locks:
2366 -- None
2367 --Function:
2368 -- Returns back the cat_admin_auth_enabled_flag value
2369 --Parameters:
2370 --IN:
2371 --p_po_header_id
2372 -- header_id of the document
2373 --IN OUT:
2374 --OUT:
2375 --Returns:
2376 --Notes:
2377 --Testing:
2378 --End of Comments
2379 ------------------------------------------------------------------------
2380 FUNCTION get_cat_admin_auth_enable_flag
2381 (p_po_header_id IN NUMBER
2382 ) RETURN VARCHAR2 IS
2383
2384 d_api_name CONSTANT VARCHAR2(30) := 'get_cat_admin_authoring_status';
2385 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2386 d_position NUMBER;
2387 l_cat_admin_auth_enable_flag VARCHAR2(1);
2388
2389 BEGIN
2390
2391 d_position := 0;
2392 IF (PO_LOG.d_proc) THEN
2393 PO_LOG.proc_begin(d_module);
2394 END IF;
2395
2396 SELECT cat_admin_auth_enabled_flag
2397 INTO l_cat_admin_auth_enable_flag
2398 FROM po_headers_all
2399 WHERE po_header_id = p_po_header_id;
2400
2401 return l_cat_admin_auth_enable_flag;
2402
2403 EXCEPTION
2404 WHEN no_data_found THEN
2405 RETURN NULL;
2406 WHEN others THEN
2407 po_message_s.sql_error('get_cat_admin_auth_enable_flag',d_position, sqlcode);
2408 raise;
2409
2410 END get_cat_admin_auth_enable_flag;
2411
2412 -----------------------------------------------------------------------
2413 --Start of Comments
2414 --Name: set_cat_admin_auth_enable_flag
2415 --Pre-reqs: None
2416 --Modifies:
2417 --Locks:
2418 -- None
2419 --Function:
2420 -- Sets the cat_admin_auth_enable_flag value
2421 --Parameters:
2422 --IN:
2423 --p_po_header_id
2424 -- header_id of the document
2425 --p_cat_admin_auth_enable_flag
2426 -- the value to set
2427 --IN OUT:
2428 --OUT:
2429 --Returns:
2430 --Notes:
2431 --Testing:
2432 --End of Comments
2433 ------------------------------------------------------------------------
2434 FUNCTION set_cat_admin_auth_enable_flag
2435 (p_po_header_id IN NUMBER,
2436 p_cat_admin_auth_enable_flag IN VARCHAR2
2437 ) RETURN VARCHAR2 IS
2438
2439 d_api_name CONSTANT VARCHAR2(30) := 'set_cat_admin_authoring_status';
2440 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2441 d_position NUMBER;
2442
2443 BEGIN
2444
2445 d_position := 0;
2446 IF (PO_LOG.d_proc) THEN
2447 PO_LOG.proc_begin(d_module);
2448 END IF;
2449
2450 UPDATE po_headers_all
2451 SET cat_admin_auth_enabled_flag
2452 = p_cat_admin_auth_enable_flag
2453 WHERE po_header_id = p_po_header_id;
2454
2455 return FND_API.G_RET_STS_SUCCESS;
2456
2457 EXCEPTION
2458 WHEN no_data_found THEN
2459 RETURN FND_API.G_RET_STS_ERROR;
2460 WHEN others THEN
2461 po_message_s.sql_error('get_cat_admin_auth_enable_flag',d_position, sqlcode);
2462 raise;
2463
2464 END set_cat_admin_auth_enable_flag;
2465
2466 -- bug 5014131 START
2467 -----------------------------------------------------------------------
2468 --Start of Comments
2469 --Name: get_upload_status_info
2470 --Function:
2471 -- Get the catalog upload status based on user role
2472 --Parameters:
2473 --IN:
2474 --p_po_header_id
2475 -- header_id of the document
2476 --p_role
2477 --
2478 --IN OUT:
2479 --OUT:
2480 -- upload_status_code
2481 -- upload_requestor_role_id id of the role
2482 -- upload_job_number latest upload job id
2483 -- upload_is_error Whether Upload errored out
2484 --Returns:
2485 --Notes:
2486 --Testing:
2487 --End of Comments
2488 ------------------------------------------------------------------------
2489 PROCEDURE get_upload_status_info
2490 ( p_po_header_id IN NUMBER,
2491 p_role IN VARCHAR2,
2492 x_upload_status_code OUT NOCOPY VARCHAR2,
2493 x_upload_requestor_role_id OUT NOCOPY NUMBER,
2494 x_upload_job_number OUT NOCOPY NUMBER,
2495 x_upload_status_display OUT NOCOPY VARCHAR2,
2496 x_upload_is_error OUT NOCOPY NUMBER -- Bug#5518826
2497 ) IS
2498
2499 BEGIN
2500
2501 --Bug#5518826
2502 --Added is_error column in the select clause
2503 SELECT job_status,
2504 -- role_user_id,
2505 job_number,
2506 job_status_display,
2507 is_error
2508 INTO x_upload_status_code,
2509 --x_upload_requestor_role_id,
2510 x_upload_job_number,
2511 x_upload_status_display,
2512 x_upload_is_error
2513 FROM icx_cat_latest_batch_jobs_v
2514 WHERE po_header_Id = p_po_header_id
2515 AND role = p_role;
2516
2517 EXCEPTION
2518 WHEN NO_DATA_FOUND THEN
2519 x_upload_status_code := 'NOT_REQUESTED';
2520 x_upload_requestor_role_id := NULL;
2521 x_upload_job_number := NULL;
2522 x_upload_status_display := NULL;
2523 x_upload_is_error := NULL;
2524 END get_upload_status_info;
2525
2526 -----------------------------------------------------------------------
2527 --Start of Comments
2528 --Name: get_in_process_upload_info
2529 --Function:
2530 -- Return the information for the upload that in progress regardless
2531 -- of the role
2532 --Parameters:
2533 --IN:
2534 --p_po_header_id
2535 -- header_id of the document
2536 --p_role
2537 -- role of the user
2538 --IN OUT:
2539 --OUT:
2540 -- upload_in_progress: FND_API.G_TRUE if there's one upload that's not yet
2541 -- complete
2542 -- upload_status_code
2543 -- upload_requestor_role
2544 -- upload_requestor_role_id id of the role
2545 -- upload_job_id latest upload job id
2546 -- upload_status_display
2547 --Returns:
2548 --Notes:
2549 --Testing:
2550 --End of Comments
2551 ------------------------------------------------------------------------
2552 PROCEDURE get_in_process_upload_info
2553 ( p_po_header_id IN NUMBER,
2554 x_upload_in_progress OUT NOCOPY VARCHAR2,
2555 x_upload_status_code OUT NOCOPY VARCHAR2,
2556 x_upload_requestor_role OUT NOCOPY VARCHAR2,
2557 x_upload_requestor_role_id OUT NOCOPY NUMBER,
2558 x_upload_job_number OUT NOCOPY NUMBER,
2559 x_upload_status_display OUT NOCOPY VARCHAR2
2560 ) IS
2561
2562 BEGIN
2563 x_upload_in_progress := FND_API.G_FALSE;
2564
2565 -- return the upload status for any upload that's considered 'IN PROGRESS'
2566 SELECT job_status,
2567 -- role_user_id,
2568 job_number,
2569 job_status_display
2570 INTO x_upload_status_code,
2571 --x_upload_requestor_role_id,
2572 x_upload_job_number,
2573 x_upload_status_display
2574 FROM icx_cat_latest_batch_jobs_v
2575 WHERE po_header_id = p_po_header_id
2576 AND job_status IN (g_upload_status_PENDING,
2577 g_upload_status_RUNNING,
2578 g_upload_status_ERROR)
2579 AND ROWNUM = 1;
2580
2581 x_upload_in_progress := FND_API.G_TRUE;
2582
2583 EXCEPTION
2584 WHEN NO_DATA_FOUND THEN
2585 NULL;
2586 END get_in_process_upload_info;
2587
2588 -- bug 5014131 END
2589
2590 -- bug5090429 START
2591 -- Overloaded procedure for another one. All the parameters required by
2592 -- the other one can be derived from p_po_header_id
2593 PROCEDURE unlock_document_and_send_notif
2594 ( p_commit IN VARCHAR2 := FND_API.G_FALSE,
2595 p_po_header_id IN NUMBER
2596 ) IS
2597
2598 d_api_name CONSTANT VARCHAR2(30) := 'unlock_document_and_send_notif';
2599 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2600 d_position NUMBER;
2601
2602 l_org_id PO_HEADERS_ALL.org_id%TYPE;
2603 l_segment1 PO_HEADERS_ALL.segment1%TYPE;
2604 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
2605
2606 BEGIN
2607
2608 d_position := 0;
2609
2610 IF (PO_LOG.d_proc) THEN
2611 PO_LOG.proc_begin(d_module,'p_commit',p_commit);
2612 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
2613 END IF;
2614
2615 SELECT org_id,
2616 segment1,
2617 revision_num
2618 INTO l_org_id,
2619 l_segment1,
2620 l_revision_num
2621 FROM po_headers_all
2622 WHERE po_header_id = p_po_header_id;
2623
2624 unlock_document_and_send_notif
2625 ( p_commit => p_commit,
2626 p_po_header_id => p_po_header_id,
2627 p_org_id => l_org_id,
2628 p_segment1 => l_segment1,
2629 p_revision_num => l_revision_num
2630 );
2631
2632 IF (PO_LOG.d_proc) THEN
2633 PO_LOG.proc_end(d_module,'p_commit',p_commit);
2634 END IF;
2635
2636 EXCEPTION
2637 WHEN OTHERS THEN
2638 PO_MESSAGE_S.add_exc_msg
2639 ( p_pkg_name => d_pkg_name,
2640 p_procedure_name => d_api_name || '.' || d_position
2641 );
2642
2643 RAISE;
2644 END unlock_document_and_send_notif;
2645
2646
2647
2648 -- bug5090429 END
2649
2650
2651 --<Bug#4382472 Start>
2652 -----------------------------------------------------------------------
2653 --Start of Comments
2654 --Name: unlock_document_and_send_notif
2655 --Pre-reqs:
2656 -- None
2657 --Modifies:None
2658 --Locks:
2659 -- None
2660 --Parameters:
2661 --IN:
2662 --p_commit
2663 -- Flag to indicate whether the procedure will commit or not.
2664 --p_po_header_id
2665 -- header_id of the document
2666 --p_org_id
2667 -- Unique Identifier for Org to which document belongs
2668 --p_segment1
2669 -- Document Number
2670 --p_revision_num
2671 -- Revision Number of the document
2672 --Returns:
2673 --Notes:
2674 --Testing:
2675 --End of Comments
2676 ------------------------------------------------------------------------
2677 procedure unlock_document_and_send_notif(p_commit IN VARCHAR2,
2678 p_po_header_id IN NUMBER,
2679 p_org_id IN NUMBER,
2680 p_segment1 IN VARCHAR2,
2681 p_revision_num IN NUMBER)
2682 IS
2683 d_api_name CONSTANT VARCHAR2(30) := 'unlock_document_and_send_notif';
2684 d_module CONSTANT VARCHAR2(2000) := PO_LOG.get_subprogram_base(d_pkg_name, d_api_name);
2685 d_pos NUMBER := 0;
2686
2687 l_agreement_info FND_NEW_MESSAGES.message_text%type := NULL;
2688 l_doc_style_name PO_DOC_STYLE_LINES_TL.display_name%type := NULL;
2689 l_ou_name HR_OPERATING_UNITS.name%type := NULL;
2690
2691 l_lock_owner_role PO_HEADERS_ALL.lock_owner_role%TYPE;
2692 l_lock_owner_user_id PO_HEADERS_ALL.lock_owner_user_id%TYPE;
2693
2694 l_agent_id PO_HEADERS_ALL.agent_id%TYPE;
2695 BEGIN
2696
2697 IF (PO_LOG.d_proc) THEN
2698 PO_LOG.proc_begin(d_module,'p_commit',p_commit);
2699 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
2700 PO_LOG.proc_begin(d_module,'p_org_id',p_org_id);
2701 PO_LOG.proc_begin(d_module,'p_segment1',p_segment1);
2702 PO_LOG.proc_begin(d_module,'p_revision_num',p_revision_num);
2703 END IF;
2704
2705 -- bug5090429
2706 -- Get who is locking the document
2707
2708 get_lock_owner_info
2709 ( p_po_header_id => p_po_header_id,
2710 x_lock_owner_role => l_lock_owner_role,
2711 x_lock_owner_user_id => l_lock_owner_user_id
2712 );
2713
2714
2715 --Unlock the document
2716 unlock_document(p_po_header_id => p_po_header_id);
2717
2718 d_pos := 10;
2719 --Get Operating Unit Name
2720 l_ou_name := PO_MOAC_UTILS_PVT.get_ou_name(p_org_id);
2721 IF (PO_LOG.d_stmt) THEN
2722 PO_LOG.stmt(d_module,d_pos,'l_ou_name',l_ou_name);
2723 END IF;
2724
2725 d_pos := 20;
2726 --Get the Agreement's Title as we get by calling getTitle of HTML page's controller
2727 IF nvl(p_revision_num, 0) > 0 THEN
2728 fnd_message.set_name('PO', 'PO_DOCUMENT_PO_TTL_INFO_REV');
2729 fnd_message.set_token('POREVNUM', p_revision_num);
2730 ELSE
2731 fnd_message.set_name('PO', 'PO_DOCUMENT_PO_TTL_INFO_NO_REV');
2732 END IF;
2733
2734 d_pos := 30;
2735 l_doc_style_name := PO_DOC_STYLE_PVT.get_style_display_name(p_po_header_id);
2736 IF (PO_LOG.d_stmt) THEN
2737 PO_LOG.stmt(d_module,d_pos,'l_doc_style_name',l_doc_style_name);
2738 END IF;
2739
2740 d_pos := 40;
2741 fnd_message.set_token('PONUM', p_segment1);
2742 fnd_message.set_token('DOCSTYLE', l_doc_style_name);
2743 l_agreement_info := fnd_message.get;
2744 IF (PO_LOG.d_stmt) THEN
2745 PO_LOG.stmt(d_module,d_pos,'l_agreement_info',l_agreement_info);
2746 END IF;
2747
2748 d_pos := 50;
2749 --Send the notification
2750
2751 -- bug5249393
2752 -- The notification from is always the buyer
2753 SELECT agent_id
2754 INTO l_agent_id
2755 FROM po_headers_all
2756 WHERE po_header_id = p_po_header_id;
2757
2758 -- bug5090429
2759 -- Changed the signature of the API
2760 PO_ONLINE_AUTHORING_WF_ACTIONS.start_changes_discarded_wf
2761 ( p_agreement_id => p_po_header_id,
2762 p_agreement_info => l_agreement_info,
2763 p_lock_owner_role => l_lock_owner_role,
2764 p_lock_owner_user_id => l_lock_owner_user_id,
2765 p_buyer_user_id => l_agent_id -- bug5249393
2766 );
2767
2768 d_pos := 60;
2769 --Commit the changes
2770 IF p_commit = FND_API.G_TRUE THEN
2771 COMMIT WORK;
2772 END IF;
2773
2774 IF (PO_LOG.d_proc) THEN
2775 PO_LOG.proc_end(d_module);
2776 END IF;
2777 EXCEPTION
2778 WHEN OTHERS THEN
2779 PO_MESSAGE_S.add_exc_msg
2780 ( p_pkg_name => d_pkg_name,
2781 p_procedure_name => d_api_name || '.' || d_pos
2782 );
2783
2784 PO_MESSAGE_S.sql_error('unlock_document_and_send_notif',d_pos, sqlcode);
2785 RAISE;
2786 END unlock_document_and_send_notif;
2787 --<Bug#4382472 End>
2788
2789 -------------------------------------------------------
2790 -------------- PRIVATE PROCEDURES ---------------------
2791 -------------------------------------------------------
2792
2793 -----------------------------------------------------------------------
2794 --Start of Comments < bug5532550 >
2795 --Name: is_doc_in_updatable_state
2796 --Function:
2797 -- check whether the document is in a status updatable by the role
2798 --Parameters:
2799 --IN:
2800 --p_po_header_id
2801 -- document header id
2802 --p_role
2803 -- role of the user
2804 --End of Comments
2805 ------------------------------------------------------------------------
2806 FUNCTION is_doc_in_updatable_state
2807 ( p_po_header_id IN NUMBER,
2808 p_role IN VARCHAR2
2809 ) RETURN VARCHAR2 IS
2810
2811 d_api_name CONSTANT VARCHAR2(30) := 'is_doc_in_updatable_state';
2812 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2813 d_position NUMBER;
2814
2815 l_dummy NUMBER;
2816
2817 BEGIN
2818
2819 d_position := 0;
2820 IF (PO_LOG.d_proc) THEN
2821 PO_LOG.proc_begin(d_module);
2822 END IF;
2823
2824 IF (p_role IN (PO_GLOBAL.g_role_SUPPLIER,
2825 PO_GLOBAL.g_role_CAT_ADMIN)) THEN
2826
2827 SELECT 1
2828 INTO l_dummy
2829 FROM po_headers_all POH
2830 WHERE POH.po_header_id = p_po_header_id
2831 AND NVL(cancel_flag, 'N') = 'N'
2832 AND NVL(closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
2833 AND NVL(frozen_flag, 'N') <> 'Y'
2834 AND NVL(user_hold_flag, 'N') <> 'Y';
2835
2836 RETURN FND_API.G_TRUE;
2837 ELSE
2838 RETURN FND_API.G_TRUE;
2839 END IF;
2840
2841 IF (PO_LOG.d_proc) THEN
2842 PO_LOG.proc_end(d_module);
2843 END IF;
2844 EXCEPTION
2845 WHEN NO_DATA_FOUND THEN
2846 RETURN FND_API.G_FALSE;
2847 END is_doc_in_updatable_state;
2848
2849 -----------------------------------------------------------------------
2850 --Start of Comments
2851 --Name: apply_changes
2852 --Pre-reqs: None
2853 --Modifies:
2854 --Locks:
2855 -- None
2856 --Function:
2857 -- Merge data to transaction tables at each level
2858 --Parameters:
2859 --IN:
2860 --p_draft_info
2861 -- record structure that holds draft information
2862 --IN OUT:
2863 --OUT:
2864 --Returns:
2865 --Notes:
2866 --Testing:
2867 --End of Comments
2868 ------------------------------------------------------------------------
2869 PROCEDURE apply_changes
2870 ( p_draft_info IN DRAFT_INFO_REC_TYPE
2871 ) IS
2872
2873 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
2874 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2875 d_position NUMBER;
2876
2877 BEGIN
2878 d_position := 0;
2879 IF (PO_LOG.d_proc) THEN
2880 PO_LOG.proc_begin(d_module);
2881 END IF;
2882
2883 PO_HEADERS_DRAFT_PVT.apply_changes
2884 (p_draft_info => p_draft_info
2885 );
2886
2887 d_position := 10;
2888 PO_LINES_DRAFT_PVT.apply_changes
2889 (p_draft_info => p_draft_info
2890 );
2891
2892 d_position := 20;
2893 PO_LINE_LOCATIONS_DRAFT_PVT.apply_changes
2894 (p_draft_info => p_draft_info
2895 );
2896
2897 d_position := 30;
2898 PO_DISTRIBUTIONS_DRAFT_PVT.apply_changes
2899 (p_draft_info => p_draft_info
2900 );
2901
2902 d_position := 40;
2903 PO_GA_ORG_ASSIGN_DRAFT_PVT.apply_changes
2904 (p_draft_info => p_draft_info
2905 );
2906
2907 d_position := 50;
2908 PO_PRICE_DIFF_DRAFT_PVT.apply_changes
2909 (p_draft_info => p_draft_info
2910 );
2911
2912 d_position := 60;
2913 PO_NOTIFICATION_CTRL_DRAFT_PVT.apply_changes
2914 (p_draft_info => p_draft_info
2915 );
2916
2917 d_position := 70;
2918 PO_ATTR_VALUES_DRAFT_PVT.apply_changes
2919 (p_draft_info => p_draft_info
2920 );
2921
2922 d_position := 80;
2923 PO_ATTR_VALUES_TLP_DRAFT_PVT.apply_changes
2924 (p_draft_info => p_draft_info
2925 );
2926
2927 d_position := 90;
2928 IF (PO_LOG.d_proc) THEN
2929 PO_LOG.proc_end(d_module);
2930 END IF;
2931 EXCEPTION
2932 WHEN OTHERS THEN
2933 PO_MESSAGE_S.add_exc_msg
2934 ( p_pkg_name => d_pkg_name,
2935 p_procedure_name => d_api_name || '.' || d_position
2936 );
2937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2938 END apply_changes;
2939
2940
2941 -----------------------------------------------------------------------
2942 --Start of Comments
2943 --Name: set_new_revision
2944 --Pre-reqs: None
2945 --Modifies:
2946 --Locks:
2947 -- None
2948 --Function:
2949 -- Update document revision, if necessary
2950 --Parameters:
2951 --IN:
2952 --p_draft_info
2953 -- record structure that holds draft information
2954 --IN OUT:
2955 --OUT:
2956 --Returns:
2957 --Notes:
2958 --Testing:
2959 --End of Comments
2960 ------------------------------------------------------------------------
2961 PROCEDURE set_new_revision
2962 ( p_draft_info DRAFT_INFO_REC_TYPE
2963 ) IS
2964
2965 TYPE rev_check_level_tbl_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
2966
2967 d_api_name CONSTANT VARCHAR2(30) := 'set_new_revision';
2968 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2969 d_position NUMBER;
2970
2971 l_orig_revision_num PO_HEADERS_ALL.revision_num%TYPE;
2972 l_rev_check_level_tbl rev_check_level_tbl_type;
2973 l_index NUMBER := 0;
2974 l_new_revision_num PO_HEADERS_ALL.revision_num%TYPE;
2975 l_return_status VARCHAR2(1);
2976 l_message VARCHAR2(2000);
2977
2978 BEGIN
2979 d_position := 0;
2980 IF (PO_LOG.d_proc) THEN
2981 PO_LOG.proc_begin(d_module);
2982 END IF;
2983
2984 IF (p_draft_info.new_document = FND_API.G_TRUE) THEN
2985 RETURN;
2986 END IF;
2987
2988 SELECT PH.revision_num
2989 INTO l_orig_revision_num
2990 FROM po_headers_all PH
2991 WHERE PH.po_header_id = p_draft_info.po_header_id;
2992
2993 d_position := 10;
2994 -- determine which level(s) do we need to check for revision change
2995 IF (p_draft_info.headers_changed = FND_API.G_TRUE
2996 OR p_draft_info.ga_org_assign_changed = FND_API.G_TRUE) THEN
2997
2998 l_index := l_index +1;
2999 l_rev_check_level_tbl(l_index) := 'HEADER';
3000 END IF;
3001
3002 d_position := 20;
3003 IF (p_draft_info.lines_changed = FND_API.G_TRUE) THEN
3004 l_index := l_index +1;
3005 l_rev_check_level_tbl(l_index) := 'LINES';
3006 END IF;
3007
3008 d_position := 30;
3009 IF (p_draft_info.line_locations_changed = FND_API.G_TRUE) THEN
3010 l_index := l_index +1;
3011 l_rev_check_level_tbl(l_index) := 'SHIPMENTS';
3012 END IF;
3013
3014 d_position := 40;
3015 IF (p_draft_info.distributions_changed = FND_API.G_TRUE) THEN
3016 l_index := l_index +1;
3017 l_rev_check_level_tbl(l_index) := 'DISTRIBUTIONS';
3018 END IF;
3019
3020 d_position := 50;
3021 IF (p_draft_info.price_diff_changed = FND_API.G_TRUE) THEN
3022 l_index := l_index +1;
3023 l_rev_check_level_tbl(l_index) := 'PO_LINE_PRICE_DIFF';
3024
3025 IF (p_draft_info.doc_subtype = 'BLANKET') THEN
3026 l_index := l_index +1;
3027 l_rev_check_level_tbl(l_index) := 'PO_PB_PRICE_DIFF';
3028 END IF;
3029 END IF;
3030
3031 d_position := 60;
3032 l_new_revision_num := l_orig_revision_num;
3033
3034 FOR i IN 1..l_index LOOP
3035 d_position := 70;
3036
3037 PO_DOCUMENT_REVISION_GRP.check_new_revision
3038 ( p_api_version => 1.0,
3039 p_doc_type => p_draft_info.doc_type,
3040 p_doc_subtype => p_draft_info.doc_subtype,
3041 p_doc_id => p_draft_info.po_header_id,
3042 p_table_name => l_rev_check_level_tbl(i),
3043 x_return_status => l_return_status,
3044 x_doc_revision_num => l_new_revision_num,
3045 x_message => l_message
3046 );
3047
3048 d_position := 80;
3049 IF (l_return_status <> 'S') THEN
3050 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3051 END IF;
3052
3053 IF (l_orig_revision_num <> l_new_revision_num) THEN
3054 d_position := 90;
3055
3056 UPDATE po_headers_all
3057 SET revision_num = l_new_revision_num,
3058 revised_date = SYSDATE
3059 WHERE po_header_id = p_draft_info.po_header_id;
3060
3061 -- revision has been incremented. No need to check another level
3062 EXIT;
3063 END IF;
3064 END LOOP;
3065
3066 d_position := 100;
3067 IF (PO_LOG.d_proc) THEN
3068 PO_LOG.proc_end(d_module);
3069 END IF;
3070 EXCEPTION
3071 WHEN OTHERS THEN
3072 PO_MESSAGE_S.add_exc_msg
3073 ( p_pkg_name => d_pkg_name,
3074 p_procedure_name => d_api_name || '.' || d_position
3075 );
3076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3077 END set_new_revision;
3078
3079
3080 -----------------------------------------------------------------------
3081 --Start of Comments
3082 --Name: complete_transfer
3083 --Pre-reqs: None
3084 --Modifies:
3085 --Locks:
3086 -- None
3087 --Function:
3088 -- Mark transfer process as completed. If draft changes should be
3089 -- removed, then all draft changes will get deleted
3090 --Parameters:
3091 --IN:
3092 --p_draft_info
3093 -- record structure that holds draft information
3094 --p_delete_draft
3095 -- flag to indicate whether draft changes should get removed from draft
3096 -- tables. Possible values are FND_API.G_TRUE, FND_API.G_FALSE, 'X'
3097 --IN OUT:
3098 --OUT:
3099 --Returns:
3100 --Notes:
3101 --Testing:
3102 --End of Comments
3103 ------------------------------------------------------------------------
3104 PROCEDURE complete_transfer
3105 ( p_draft_info IN DRAFT_INFO_REC_TYPE,
3106 p_delete_draft IN VARCHAR2
3107 ) IS
3108
3109 d_api_name CONSTANT VARCHAR2(30) := 'complete_transfer';
3110 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3111 d_position NUMBER;
3112
3113 l_return_status VARCHAR2(1);
3114 l_exclude_ctrl_tbl VARCHAR2(1);
3115
3116 BEGIN
3117 d_position := 0;
3118 IF (PO_LOG.d_proc) THEN
3119 PO_LOG.proc_begin(d_module);
3120 END IF;
3121
3122 -- delete_draft = 'X' means that the PO_DRAFTS table should be
3123 -- excluded from deletion. We should just mark it as 'COMPLETED'
3124
3125 IF (p_delete_draft = FND_API.G_FALSE OR
3126 p_delete_draft = 'X') THEN
3127
3128 d_position := 10;
3129
3130 update_draft_status
3131 ( p_draft_id => p_draft_info.draft_id,
3132 p_new_status => g_status_COMPLETED
3133 );
3134
3135 END IF;
3136
3137 IF (p_delete_draft = FND_API.G_TRUE OR
3138 p_delete_draft = 'X') THEN
3139
3140 IF (p_delete_draft = FND_API.G_TRUE) THEN
3141 l_exclude_ctrl_tbl := FND_API.G_FALSE;
3142 ELSIF (p_delete_draft = 'X') THEN
3143 l_exclude_ctrl_tbl := FND_API.G_TRUE;
3144 END IF;
3145
3146 d_position := 20;
3147 remove_draft_changes
3148 ( p_draft_id => p_draft_info.draft_id,
3149 p_exclude_ctrl_tbl => l_exclude_ctrl_tbl,
3150 x_return_status => l_return_status
3151 );
3152
3153 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3155 END IF;
3156 END IF;
3157
3158 d_position := 30;
3159 IF (PO_LOG.d_proc) THEN
3160 PO_LOG.proc_end(d_module);
3161 END IF;
3162 EXCEPTION
3163 WHEN OTHERS THEN
3164 PO_MESSAGE_S.add_exc_msg
3165 ( p_pkg_name => d_pkg_name,
3166 p_procedure_name => d_api_name || '.' || d_position
3167 );
3168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3169 END complete_transfer;
3170
3171
3172 -----------------------------------------------------------------------
3173 --Start of Comments
3174 --Name: update_acceptance_status
3175 --Pre-reqs: None
3176 --Modifies:
3177 --Locks:
3178 -- None
3179 --Function:
3180 -- This procedure performs mass update of the draft records if action is
3181 -- either ACCEPT_ALL or REJECT_ALL
3182 --Parameters:
3183 --IN:
3184 --p_draft_id
3185 -- draft unique identifier
3186 --p_acceptance_action
3187 -- either g_ACCEPT_ALL (Accept all changes) or g_REJECT_ALL
3188 -- (Reject all changes)
3189 --IN OUT:
3190 --OUT:
3191 --Returns:
3192 --Notes:
3193 --Testing:
3194 --End of Comments
3195 ------------------------------------------------------------------------
3196 PROCEDURE update_acceptance_status
3197 ( p_draft_id IN NUMBER,
3198 p_acceptance_action IN VARCHAR2
3199 ) IS
3200
3201 d_api_name CONSTANT VARCHAR2(30) := 'update_acceptance_status';
3202 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3203 d_position NUMBER;
3204
3205 l_change_accepted_flag PO_HEADERS_DRAFT_ALL.change_accepted_flag%TYPE := NULL;
3206
3207 BEGIN
3208
3209 d_position := 0;
3210 IF (PO_LOG.d_proc) THEN
3211 PO_LOG.proc_begin(d_module);
3212 END IF;
3213
3214 IF (p_acceptance_action = g_ACCEPT_ALL) THEN
3215 l_change_accepted_flag := 'Y';
3216 ELSIF (p_acceptance_action = g_REJECT_ALL) THEN
3217 l_change_accepted_flag := 'N';
3218 END IF;
3219
3220 d_position := 20;
3221
3222 IF (l_change_accepted_flag IS NOT NULL) THEN
3223 d_position := 30;
3224 UPDATE po_headers_draft_all
3225 SET change_accepted_flag = l_change_accepted_flag
3226 WHERE draft_id = p_draft_id
3227 AND change_accepted_flag IS NULL;
3228
3229 d_position := 40;
3230 UPDATE po_lines_draft_all
3231 SET change_accepted_flag = l_change_accepted_flag
3232 WHERE draft_id = p_draft_id
3233 AND change_accepted_flag IS NULL;
3234
3235 d_position := 50;
3236 UPDATE po_line_locations_draft_all
3237 SET change_accepted_flag = l_change_accepted_flag
3238 WHERE draft_id = p_draft_id
3239 AND change_accepted_flag IS NULL;
3240
3241 d_position := 60;
3242 UPDATE po_distributions_draft_all
3243 SET change_accepted_flag = l_change_accepted_flag
3244 WHERE draft_id = p_draft_id
3245 AND change_accepted_flag IS NULL;
3246
3247 d_position := 70;
3248 UPDATE po_ga_org_assign_draft
3249 SET change_accepted_flag = l_change_accepted_flag
3250 WHERE draft_id = p_draft_id
3251 AND change_accepted_flag IS NULL;
3252
3253 d_position := 80;
3254 UPDATE po_price_diff_draft
3255 SET change_accepted_flag = l_change_accepted_flag
3256 WHERE draft_id = p_draft_id
3257 AND change_accepted_flag IS NULL;
3258
3259 d_position := 90;
3260 UPDATE po_notification_ctrl_draft
3261 SET change_accepted_flag = l_change_accepted_flag
3262 WHERE draft_id = p_draft_id
3263 AND change_accepted_flag IS NULL;
3264
3265 d_position := 100;
3266 UPDATE po_attribute_values_draft
3267 SET change_accepted_flag = l_change_accepted_flag
3268 WHERE draft_id = p_draft_id
3269 AND change_accepted_flag IS NULL;
3270
3271 d_position := 110;
3272 UPDATE po_attribute_values_tlp_draft
3273 SET change_accepted_flag = l_change_accepted_flag
3274 WHERE draft_id = p_draft_id
3275 AND change_accepted_flag IS NULL;
3276
3277
3278 END IF;
3279
3280 IF (PO_LOG.d_proc) THEN
3281 PO_LOG.proc_end(d_module);
3282 END IF;
3283
3284 EXCEPTION
3285 WHEN OTHERS THEN
3286 PO_MESSAGE_S.add_exc_msg
3287 ( p_pkg_name => d_pkg_name,
3288 p_procedure_name => d_api_name || '.' || d_position
3289 );
3290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3291 END update_acceptance_status;
3292
3293 END PO_DRAFTS_PVT;