1 PACKAGE BODY PO_MOAC_UTILS_PVT AS
2 /*$Header: POXVMOUB.pls 120.8.12010000.2 2008/08/04 08:34:07 rramasam ship $*/
3
4 -----------------------------------------------------------------------------
5 -- Declare private package variables.
6 -----------------------------------------------------------------------------
7 g_pkg_name CONSTANT VARCHAR2(30)
8 := 'PO_MOAC_UTILS_PVT';
9
10 D_PACKAGE_BASE CONSTANT VARCHAR2(100) := PO_LOG.get_package_base(g_pkg_name);
11
12
13 -------------------------------------------------------------------------------
14 --Start of Comments
15 --Name: SET_ORG_CONTEXT
16 --Pre-reqs:
17 -- None.
18 --Modifies:
19 -- None.
20 --Locks:
21 -- None.
22 --Function:
23 -- This procedure would be used to set the Org Context. This procedure replaces
24 -- setting of ORG Context using FND CLIENT INFO or DBMS APPLICATION INFO calls.
25 -- This procedure first checks if the global temp table is already populated or
26 -- not, if it is not populated it would invoke initialize routine and then set
27 -- the org context to single and set current ou to the p_org_id.
28 --Parameters:
29 --IN:
30 --p_org_id
31 -- This contains the org_id for which the context needs to be set.
32 --IN OUT:
33 -- None.
34 --OUT:
35 -- None.
36 --Notes:
37 -- The algorithm of the procedure is as follows :
38 -- Check for the current orgid. If p_org_id does not match with the current org_id
39 -- call the initialize routine. Then set the policy context to the p_org_id.
40 --Testing:
41 --
42 --End of Comments
43 -------------------------------------------------------------------------------
44 PROCEDURE SET_ORG_CONTEXT
45 (
46 p_org_id IN NUMBER
47 )
48 IS
49 l_progress VARCHAR2(3) := NULL;
50
51 l_current_org_id hr_all_organization_units_tl.organization_id%TYPE;
52
53 l_access_mode VARCHAR2(1);
54 l_ou_count NUMBER;
55 l_is_mo_init_done VARCHAR2(1);
56 BEGIN
57 l_progress := '010';
58
59 --<R12 MOAC IMPACTS START>
60
61 -- bug5562486
62 -- Rewrote the whole procedure to call expensive MO init procedure only when
63 -- absolutely necessary
64
65 l_is_mo_init_done := MO_GLOBAL.is_mo_init_done;
66
67 IF (l_is_mo_init_done <> 'Y') THEN
68 PO_MOAC_UTILS_PVT.initialize;
69 END IF;
70
71 l_progress := '020';
72
73 IF (p_org_id IS NOT NULL) THEN
74
75 l_access_mode := MO_GLOBAL.get_access_mode;
76
77 IF ( l_access_mode = 'S') THEN
78
79 l_current_org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
80
81 -- If access mode is already single, then we need to call
82 -- set_policy_context only if the org is different from current org
83 IF ( l_current_org_id IS NULL OR
84 l_current_org_id <> p_org_id) THEN
85
86 l_progress := '030';
87 PO_MOAC_UTILS_PVT.set_policy_context('S', p_org_id);
88 END IF;
89 ELSE
90 l_progress := '040';
91 PO_MOAC_UTILS_PVT.set_policy_context('S', p_org_id);
92
93 END IF;
94
95 ELSE
96
97 l_ou_count := PO_MOAC_UTILS_PVT.get_ou_count;
98
99 IF (l_ou_count > 1) THEN
100 PO_MOAC_UTILS_PVT.set_policy_context ('M', NULL);
101 END IF;
102
103 END IF;
104
105
106 --<R12 MOAC IMPACTS END>
107 EXCEPTION
108 WHEN OTHERS THEN
109 po_message_s.sql_error('po_moac_utils_pvt.set_org_context', l_progress, sqlcode);
110 RAISE;
111 END set_org_context;
112
113 -------------------------------------------------------------------------------
114 --Start of Comments
115 --Name: MO_INIT
116 --Pre-reqs:
117 -- None.
118 --Modifies:
119 -- None.
120 --Locks:
121 -- None.
122 --Function:
123 -- This procedure would be invoked by Public APIs to initialize proper multi-org
124 -- context. This procedure checks if the P_ORG_ID passed is valid or not using
125 -- get_valid_org function. If it is not valid then error is thrown, else OU
126 -- context is set to Single and current OU is set to P_ORG_ID. If p_org_id is
127 -- NULL, then the get_valid_org routine would derive either current OU or
128 -- default OU and return this value. Again context is set to single and current
129 -- OU is set to the value returned from get_valid_org if there is no existing
130 -- context(in case of default OU).
131 --Parameters:
132 --IN:
133 --p_org_id
134 -- This contains the org_id for which the context needs to be set.
135 --IN OUT:
136 -- None.
137 --OUT:
138 -- None.
139 --Notes:
140 --
141 --Testing:
142 --
143 --End of Comments
144 -------------------------------------------------------------------------------
145
146 PROCEDURE MO_INIT(p_org_id IN NUMBER )
147 IS
148 l_org_id hr_all_organization_units_tl.organization_id%TYPE;
149 l_progress VARCHAR2(3) := NULL;
150 BEGIN
151 l_progress := '010';
152 l_org_id := GET_VALID_ORG(p_org_id);
153 l_progress := '020';
154 IF l_org_id IS NULL THEN
155 RAISE FND_API.G_EXC_ERROR;
156 END IF;
157 SET_ORG_CONTEXT(l_org_id);
158 EXCEPTION
159 WHEN OTHERS THEN
160 po_message_s.sql_error('po_moac_utils_pvt.mo_init', l_progress, sqlcode);
161 RAISE;
162 END MO_INIT;
163
164 -------------------------------------------------------------------------------
165 --Start of Comments
166 --Name: GET_CURRENT_ORG_ID
167 --Pre-reqs:
168 -- None.
169 --Modifies:
170 -- None.
171 --Locks:
172 -- None.
173 --Function:
174 -- This function would be used to return current org_id. This function would
175 -- return the ORG ID set for the current session if the context is set to
176 -- Single, for Multi-context this function would return NULL. This function
177 -- is a wrapper that makes a call to MO_GLOBAL.GET_CURRENT_ORG_ID.
178 --Parameters:
179 --IN:
180 -- None.
181 --IN OUT:
182 -- None.
183 --OUT:
184 -- None.
185 --Returns:
186 -- Returns the ORG ID set for the current session if the context is set to
187 -- Single. Else for Multi-context this function returns NULL.
188 --Notes:
189 --
190 --Testing:
191 --
192 --End of Comments
193 -------------------------------------------------------------------------------
194 FUNCTION GET_CURRENT_ORG_ID
195 RETURN NUMBER
196 IS
197 l_progress VARCHAR2(3) := NULL;
198 BEGIN
199 l_progress := '010';
200 RETURN MO_GLOBAL.GET_CURRENT_ORG_ID();
201 EXCEPTION
202 WHEN OTHERS THEN
203 po_message_s.sql_error('po_moac_utils_pvt.get_current_org_id', l_progress, sqlcode);
204 RAISE;
205 END GET_CURRENT_ORG_ID;
206
207 -------------------------------------------------------------------------------
208 --Start of Comments
209 --Name: GET_OU_NAME
210 --Pre-reqs:
211 -- None.
212 --Modifies:
213 -- None.
214 --Locks:
215 -- None.
216 --Function:
217 -- This function would return Operating Unit Name for the ORG_ID passed.
218 --Parameters:
219 --IN:
220 --p_org_id
221 -- This contains the org_id for which the context needs to be set.
222 --IN OUT:
223 -- None.
224 --OUT:
225 -- None.
226 --Returns:
227 -- Returns the Operating Unit name corresponding to the org_id parameter.
228 -- Returns NULL if the ORG_ID is NULL or invalid.
229 --Notes:
230 -- This function is a wrapper that makes call to MO_GLOBAL.GET_OU_NAME.
231 --Testing:
232 --
233 --End of Comments
234 -------------------------------------------------------------------------------
235 FUNCTION GET_OU_NAME
236 (
237 p_org_id IN NUMBER
238 )
239 RETURN VARCHAR2
240 IS
241 --
242 -- This function would return OU Name for the ORG_ID passed.
243 -- If the ORG_ID is NULL or invalid, it would return NULL
244 -- This function is a wrapper that makes call to MO_GLOBAL.GET_OU_NAME
245 --
246 -- OU Name could be NULL in case global temp table is not
247 -- populated, in such cases get the OU name from HR view
248 -- This is needed for CLP
249 --
250 l_ou_name hr_operating_units.name%TYPE;
251 l_progress VARCHAR2(3) := NULL;
252
253 BEGIN
254 l_progress := '010';
255
256 l_ou_name := MO_GLOBAL.GET_OU_NAME(p_org_id);
257
258 l_progress := '020';
259 IF l_ou_name IS NULL THEN
260 SELECT
261 hrou.name
262 INTO
263 l_ou_name
264 FROM
265 hr_operating_units hrou
266 WHERE
267 hrou.organization_id = p_org_id;
268 END IF;
269 l_progress := '030';
270
271 RETURN l_ou_name;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 po_message_s.sql_error('po_moac_utils_pvt.get_ou_name', l_progress, sqlcode);
276 RAISE;
277 END GET_OU_NAME;
278
279 -------------------------------------------------------------------------------
280 --Start of Comments
281 --Name: GET_OU_COUNT
282 --Pre-reqs:
283 -- None.
284 --Modifies:
285 -- None.
286 --Locks:
287 -- None.
288 --Function:
289 -- This function would return the number of Operating Units the user currently
290 -- has access to currently.
291 --Parameters:
292 --IN:
293 -- None.
294 --IN OUT:
295 -- None.
296 --OUT:
297 -- None.
298 --Returns:
299 -- Returns the number of Operating Units the user has access to currently.
300 --Notes:
301 -- This function is a wrapper that makes call to MO_GLOBAL.GET_OU_COUNT.
302 --Testing:
303 --
304 --End of Comments
305 -------------------------------------------------------------------------------
306 FUNCTION GET_OU_COUNT
307 RETURN NUMBER
308 IS
309 l_progress VARCHAR2(3) := NULL;
310 BEGIN
311 l_progress := '010';
312 RETURN MO_GLOBAL.GET_OU_COUNT ();
313 EXCEPTION
314 WHEN OTHERS THEN
315 po_message_s.sql_error('po_moac_utils_pvt.get_ou_count', l_progress, sqlcode);
316 RAISE;
317 END GET_OU_COUNT;
318
319 -------------------------------------------------------------------------------
320 --Start of Comments
321 --Name: GET_DEFAULT_OU
322 --Pre-reqs:
323 -- None.
324 --Modifies:
325 -- None.
326 --Locks:
327 -- None.
328 --Function:
329 -- This procedure should be used to get the default operating unit for a user.
330 --Parameters:
331 --IN:
332 -- None
333 --IN OUT:
334 -- None
335 --OUT:
336 -- x_default_org_id
337 -- This will contain the default org_id.
338 -- x_default_ou_name
339 -- This will contain the default operating unit name.
340 -- x_ou_count
341 -- This will contain the number of operating units the user has access to.
342 --Notes:
343 -- This procedure is a wrapper that makes call to MO_GLOBAL.GET_DEFAULT_OU.
344 --Testing:
345 --
346 --End of Comments
347 -------------------------------------------------------------------------------
348 PROCEDURE GET_DEFAULT_OU
349 (
350 x_default_org_id OUT NOCOPY NUMBER,
351 x_default_ou_name OUT NOCOPY VARCHAR2,
352 x_ou_count OUT NOCOPY NUMBER
353 )
354 IS
355 l_progress VARCHAR2(3) := NULL;
356 BEGIN
357 l_progress := '010';
358 MO_UTILS.GET_DEFAULT_OU( x_default_org_id,
359 x_default_ou_name,
360 x_ou_count
361 );
362 EXCEPTION
363 WHEN OTHERS THEN
364 po_message_s.sql_error('po_moac_utils_pvt.get_default_ou', l_progress, sqlcode);
365 RAISE;
366 END GET_DEFAULT_OU;
367
368 -------------------------------------------------------------------------------
369 --Start of Comments
370 --Name: GET_VALID_ORG
371 --Pre-reqs:
372 -- None.
373 --Modifies:
374 -- None.
375 --Locks:
376 -- None.
377 --Function:
378 -- This function should be used to determine and get a valid operating unit.
379 -- This function checks if the p_org_id that is passed exists in the global
380 -- temporary table or not. If it does not exist, then it would throw up an
381 -- error. Before calling this function, global temp table should be populated
382 -- using MO initialization routine. If the passed org_id exists in the global
383 -- temporary table, then same is returned. If the p_org_Id is NULL, this
384 -- function tries to retrieve current org id or gets the default operating unit.
385 --Parameters:
386 --IN:
387 --p_org_id
388 -- This contains the org_id whose validity needs to be confirmed.
389 --IN OUT:
390 -- None.
391 --OUT:
392 -- None.
393 --Returns:
394 -- Returns the org_id as per validity. Refer details under function section.
395 --Notes:
396 -- This function is a wrapper that makes call to MO_GLOBAL.GET_VALID_ORG.
397 --Testing:
398 --
399 --End of Comments
400 -------------------------------------------------------------------------------
401 FUNCTION GET_VALID_ORG
402 (
403 p_org_id IN NUMBER
404 )
405 RETURN NUMBER
406 IS
407 l_progress VARCHAR2(3) := NULL;
408 BEGIN
409 l_progress := '010';
410 RETURN MO_GLOBAL.GET_VALID_ORG(p_org_id);
411 EXCEPTION
412 WHEN OTHERS THEN
413 po_message_s.sql_error('po_moac_utils_pvt.get_valid_org', l_progress, sqlcode);
414 RAISE;
415 END GET_VALID_ORG;
416
417 -------------------------------------------------------------------------------
418 --Start of Comments
419 --Name: INITIALIZE
420 --Pre-reqs:
421 -- None.
422 --Modifies:
423 -- None.
424 --Locks:
425 -- None.
426 --Function:
427 -- This procedure invokes MO Global initialization routine by passing PO as
428 -- product short code. This procedure would populate the global temporary
429 -- table with the operating units that a user has access to.
430 --Parameters:
431 --IN:
432 -- None.
433 --IN OUT:
434 -- None.
435 --OUT:
436 -- None.
437 --Notes:
438 -- This procedure is a wrapper that makes call to MO_GLOBAL.INIT.
439 --Testing:
440 --
441 --End of Comments
442 -------------------------------------------------------------------------------
443 PROCEDURE INITIALIZE
444 IS
445 l_progress VARCHAR2(3) := NULL;
446 BEGIN
447 l_progress := '010';
448 MO_GLOBAL.INIT('PO');
449 EXCEPTION
450 WHEN OTHERS THEN
451 po_message_s.sql_error('po_moac_utils_pvt.initialize', l_progress, sqlcode);
452 RAISE;
453 END INITIALIZE;
454
455 -------------------------------------------------------------------------------
456 --Start of Comments
457 --Name: SET_POLICY_CONTEXT
458 --Pre-reqs:
459 -- None.
460 --Modifies:
461 -- None.
462 --Locks:
463 -- None.
464 --Function:
465 -- This procedure is used to initialize org context. If the access mode is S,
466 -- the context is set to Single and p_Org_id is set as current org_id. If the
467 -- access mode is M, the context is set to Multiple and then current org_id
468 -- would be set to NULL.
469 --Parameters:
470 --IN:
471 --p_access_mode
472 -- This specifies the access mode ( 'S' for Single, 'M' for multiple )
473 --p_org_id
474 -- This contains the org_id whose context needs to be set when access mode
475 -- is Single ('S').
476 --IN OUT:
477 -- None.
478 --OUT:
479 -- None.
480 --Notes:
484 --End of Comments
481 -- This procedure is a wrapper that makes call to MO_GLOBAL.SET_POLICY_CONTEXT.
482 --Testing:
483 --
485 -------------------------------------------------------------------------------
486 PROCEDURE SET_POLICY_CONTEXT
487 (
488 p_access_mode IN VARCHAR2,
489 p_org_id IN NUMBER
490 )
491 IS
492 l_progress VARCHAR2(3) := NULL;
493 BEGIN
494 l_progress := '010';
495 MO_GLOBAL.SET_POLICY_CONTEXT(p_access_mode,p_org_id);
496 EXCEPTION
497 WHEN OTHERS THEN
498 po_message_s.sql_error('po_moac_utils_pvt.set_policy_context', l_progress, sqlcode);
499 RAISE;
500 END SET_POLICY_CONTEXT;
501
502 -------------------------------------------------------------------------------
503 --Start of Comments
504 --Name: CHECK_ACCESS
505 --Pre-reqs:
506 -- None.
507 --Modifies:
508 -- None.
509 --Locks:
510 -- None.
511 --Function:
512 -- This function is used to know if the ORG_ID that is passed has been set in
513 -- the context and if it is valid or not in the current context. This function
514 -- checks if the org_id exists in the global temorary table or not, if it is
515 -- present function returns 'Y', else returns 'N'. Global temporary table gets
516 -- populated when proper org context is initialized.
517 --Parameters:
518 --IN:
519 --p_org_id
520 -- This contains the org_id whose accessibility needs to be confirmed.
521 --IN OUT:
522 -- None.
523 --OUT:
524 -- None.
525 --Returns:
526 -- Returns 'Y' or 'N' as per accessibility.
527 --Notes:
528 -- This function is a wrapper that makes call to MO_GLOBAL.CHECK_ACCESS.
529 --Testing:
530 --
531 --End of Comments
532 -------------------------------------------------------------------------------
533 FUNCTION CHECK_ACCESS
534 (
535 p_org_id IN NUMBER
536 )
537 RETURN VARCHAR2
538 IS
539 l_progress VARCHAR2(3) := NULL;
540 l_retval VARCHAR2(1) := 'N';
541 BEGIN
542 l_progress := '010';
543 RETURN MO_GLOBAL.CHECK_ACCESS(p_org_id);
544 /*IF MO_GLOBAL.get_ou_name(p_org_id) IS NOT NULL THEN
545 l_retval := 'Y';
546 END IF;
547 RETURN l_retval;*/
548 EXCEPTION
549 WHEN OTHERS THEN
550 po_message_s.sql_error('po_moac_utils_pvt.check_access', l_progress, sqlcode);
551 RAISE;
552 END CHECK_ACCESS;
553
554 -------------------------------------------------------------------------------
555 --Start of Comments
556 --Name: GET_OU_SHORTCODE
557 --Pre-reqs:
558 -- None.
559 --Modifies:
560 -- None.
561 --Locks:
562 -- None.
563 --Function:
564 -- This function would return OU Short Code for the ORG_ID passed. If the
565 -- ORG_ID is NULL or invalid, it would return NULL.
566 --Parameters:
567 --IN:
568 --p_org_id
569 -- This contains the org_id whose OU short code is required.
570 --IN OUT:
571 -- None.
572 --OUT:
573 -- None.
574 --Returns:
575 -- Returns the OU Short Code corresponding to p_org_id. Returns NULL if the
576 -- ORG_ID is NULL or invalid.
577 --Notes:
578 --
579 --Testing:
580 --
581 --End of Comments
582 -------------------------------------------------------------------------------
583 FUNCTION GET_OU_SHORTCODE
584 (
585 p_org_id IN NUMBER
586 )
587 RETURN VARCHAR2
588 IS
589 l_ou_short_code HR_ORGANIZATION_INFORMATION.ORG_INFORMATION5%TYPE;
590 l_progress VARCHAR2(3) := NULL;
591 BEGIN
592
593 l_progress := '010';
594 SELECT HROI.ORG_INFORMATION5
595 INTO l_ou_short_code
596 FROM HR_ORGANIZATION_INFORMATION HROI
597 WHERE HROI.ORGANIZATION_ID = p_org_id
598 AND HROI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information';
599
600 l_progress := '020';
601 RETURN l_ou_short_code;
602
603 EXCEPTION
604 WHEN OTHERS THEN
605 RETURN NULL;
606 END GET_OU_SHORTCODE;
607
608 -------------------------------------------------------------------------------
609 --Start of Comments
610 --Name: SET_REQUEST_CONTEXT
611 --Pre-reqs:
612 -- None.
613 --Modifies:
614 -- None.
615 --Locks:
616 -- None.
617 --Function:
618 -- This procedure should be used to set the Operating Unit context for Reports/
619 -- Concurrent programs that require single OU context.
620 --Parameters:
621 --IN:
622 --p_org_id
623 -- This contains the org_id whose context needs to be set.
624 --IN OUT:
625 -- None.
626 --OUT:
627 -- None.
628 --Notes:
629 --
630 --Testing:
631 --
632 --End of Comments
633 -------------------------------------------------------------------------------
634 PROCEDURE SET_REQUEST_CONTEXT
635 (
636 p_org_id IN NUMBER
637 )
638 --
639 -- This procedure is a wrapper for fnd_request.set_org_id
640 --
641 IS
642 l_progress VARCHAR2(3) := NULL;
643 BEGIN
644 l_progress := '010';
645 fnd_request.set_org_id(p_org_id);
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 po_message_s.sql_error('po_moac_utils_pvt.set_request_context', l_progress, sqlcode);
650 RAISE;
651 END;
652
653
654 -- Bug 5124686: moved get_entity_org_id to this package from PO_CORE_S
655 -- Also added support for additional document types
656 -------------------------------------------------------------------------------
657 --Start of Comments
658 --Name: GET_ENTITY_ORG_ID
659 --Pre-reqs:
660 -- None.
661 --Modifies:
662 -- None.
663 --Locks:
664 -- None.
665 --Function:
666 -- This procedure gets the Operating Unit ID for Req/PO/PA/Release entity
667 -- at any level -> Header/Line/Shipment/Distribution
668 --Parameters:
669 --IN:
670 --p_document_type
671 -- Document type. Use the g_doc_type_<> variables, where <> is:
672 -- REQUISITION
673 -- PA
674 -- PO
675 -- RELEASE
676 --p_doc_level
677 -- The type of ids that are being passed. Use g_doc_level_<>
678 -- HEADER
679 -- LINE
680 -- SHIPMENT
681 -- DISTRIBUTION
682 --p_doc_level_id
683 -- Id of the doc level type for which to retrieve org information
684 --RETURNS:
685 -- The org_id of the passed in entity
686 --Notes:
687 --
688 --Testing:
689 --
690 --End of Comments
691 -------------------------------------------------------------------------------
692 FUNCTION GET_ENTITY_ORG_ID
693 (
694 p_doc_type IN VARCHAR2
695 , p_doc_level IN VARCHAR2
696 , p_doc_level_id IN NUMBER
697 ) RETURN NUMBER
698 IS
699 l_org_id HR_ALL_ORGANIZATION_UNITS.organization_id%type := NULL;
700 l_module_name CONSTANT VARCHAR2(100) := 'get_entity_org_id';
701 d_module_base CONSTANT VARCHAR2(100) :=
702 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
703 d_progress NUMBER := 0;
704 BEGIN
705
706 IF (PO_LOG.d_proc) THEN
707 PO_LOG.proc_begin(d_module_base);
708 PO_LOG.proc_begin(d_module_base, 'p_doc_type', p_doc_type);
709 PO_LOG.proc_begin(d_module_base, 'p_doc_level', p_doc_level);
710 PO_LOG.proc_begin(d_module_base, 'p_doc_level_id', p_doc_level_id);
711 END IF;
712
713 IF p_doc_type = g_doc_type_REQUISITION THEN
714 d_progress:=100;
715
716 IF p_doc_level = g_doc_level_HEADER THEN
717 d_progress := 110;
718
719 select prh.org_id
720 into l_org_id
721 from po_requisition_headers_all prh
722 where prh.requisition_header_id = p_doc_level_id;
723
724 ELSIF p_doc_level = g_doc_level_LINE THEN
725 d_progress := 120;
726
727 select prl.org_id
728 into l_org_id
729 from po_requisition_lines_all prl
730 where prl.requisition_line_id = p_doc_level_id;
731
732 ELSIF p_doc_level = g_doc_level_DISTRIBUTION THEN
733 d_progress := 130;
734
735 select prd.org_id
736 into l_org_id
737 from po_req_distributions_all prd
738 where prd.distribution_id = p_doc_level_id;
739
740 END IF;
741
742 ELSE -- doc type is PO/PA/RELEASE
743 d_progress := 200;
744
745 IF p_doc_level = g_doc_level_HEADER THEN
746 d_progress := 210;
747
748 IF p_doc_type IN (g_doc_type_PO, g_doc_type_PA) THEN
749 d_progress := 220;
750
751 SELECT poh.org_id
752 INTO l_org_id
753 FROM po_headers_all poh
754 WHERE poh.po_header_id = p_doc_level_id;
755
756 ELSIF p_doc_type = g_doc_type_RELEASE THEN
757 d_progress := 230;
758
759 SELECT por.org_id
760 INTO l_org_id
761 FROM po_releases_all por
762 WHERE por.po_release_id = p_doc_level_id;
763
764 END IF;
765
766 ELSIF p_doc_level = g_doc_level_LINE THEN
767 d_progress := 240;
768
769 SELECT pol.org_id
770 INTO l_org_id
771 FROM po_lines_all pol
772 WHERE pol.po_line_id = p_doc_level_id;
773
774 ELSIF p_doc_level = g_doc_level_SHIPMENT THEN
775 d_progress := 250;
776
777 SELECT poll.org_id
778 INTO l_org_id
779 FROM po_line_locations_all poll
780 WHERE poll.line_location_id = p_doc_level_id;
781
782 ELSIF p_doc_level = g_doc_level_DISTRIBUTION THEN
783 d_progress := 260;
784
785 SELECT pod.org_id
786 INTO l_org_id
787 FROM po_distributions_all pod
788 WHERE pod.po_distribution_id = p_doc_level_id;
789
790 END IF;
791
792 END IF; -- p_doc_type is Requisition or not
793
794 d_progress := 300;
795 IF (PO_LOG.d_proc) THEN
796 PO_LOG.proc_return(d_module_base, l_org_id);
797 END IF;
798
799 RETURN l_org_id;
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 IF (PO_LOG.d_exc) THEN
804 PO_LOG.exc(d_module_base,d_progress,SQLERRM);
805 END IF;
806 RAISE;
807 END;
808 -- <Bug#4581621 Start>
809 -------------------------------------------------------------------------------
810 --Start of Comments
811 --Name: validate_orgid_pub_api
812 --Pre-reqs:
813 -- None.
814 --Modifies:
815 -- None.
816 --Locks:
817 -- None.
818 --Function:
819 -- This procedure is a wrapper on the MO_GLOBAL.validate_orgid_pub_api
820 --Parameters:
821 --IN:
822 --x_org_id
823 -- Org id which needs to be validated
824 --Notes:
825 -- To be used in public API's only for backward compatibilty.
826 --Testing:
827 --
828 --End of Comments
829 -------------------------------------------------------------------------------
830 PROCEDURE validate_orgid_pub_api(x_org_id IN OUT NOCOPY NUMBER)
831 IS
832 l_status varchar2(1);
833 l_module_name CONSTANT VARCHAR2(100) := 'validate_orgid_pub_api';
834 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
835 d_progress NUMBER := 0;
836 BEGIN
837
838 IF (PO_LOG.d_proc) THEN
839 PO_LOG.proc_begin(d_module_base);
840 PO_LOG.proc_begin(d_module_base, 'x_org_id', x_org_id);
841 END IF;
842
843 MO_GLOBAL.validate_orgid_pub_api(org_id => x_org_id,
844 error_mesg_suppr => 'N',
845 status => l_status);
846
847 IF (PO_LOG.d_proc) THEN
848 PO_LOG.proc_end(d_module_base, 'x_org_id', x_org_id);
849 PO_LOG.proc_end(d_module_base);
850 END IF;
851
852 EXCEPTION
853 WHEN OTHERS THEN
854 IF (PO_LOG.d_exc) THEN
855 PO_LOG.exc(d_module_base,d_progress,SQLERRM);
856 END IF;
857 RAISE;
858 END validate_orgid_pub_api;
859 -- <Bug#4581621 End>
860 END PO_MOAC_UTILS_PVT;