1 PACKAGE BODY PO_GA_PVT AS
2 /* $Header: POXVGAB.pls 120.2.12020000.2 2013/02/11 00:59:30 vegajula ship $ */
3
4 --<Bug 2721740 mbhargav>
5 G_PKG_NAME CONSTANT varchar2(30) := 'PO_GA_PVT';
6
7 --< Shared Proc FPJ Start >
8 -- Debugging booleans used to bypass logging when turned off
9 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
11
12 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
13 --< Shared Proc FPJ End >
14
15
16 /*=============================================================================
17
18 FUNCTION: get_org_id
19
20 DESCRIPTION: Gets the owning org_id for the particular po_header_id.
21
22 =============================================================================*/
23 FUNCTION get_org_id
24 (
25 p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE
26 )
27 RETURN PO_HEADERS_ALL.org_id%TYPE
28 IS
29 x_org_id PO_HEADERS_ALL.org_id%TYPE;
30
31 BEGIN
32
33 SELECT org_id
34 INTO x_org_id
35 FROM po_headers_all
36 WHERE po_header_id = p_po_header_id;
37
38 return (x_org_id);
39
40 EXCEPTION
41
42 WHEN OTHERS THEN
43 return (NULL);
44
45 END get_org_id;
46
47
48 /*=============================================================================
49
50 FUNCTION: get_current_org
51
52 DESCRIPTION: Gets the org_id for the current session.
53
54 =============================================================================*/
55 FUNCTION get_current_org
56 RETURN PO_SYSTEM_PARAMETERS.org_id%TYPE
57 IS
58 x_org_id PO_SYSTEM_PARAMETERS.org_id%TYPE;
59 BEGIN
60
61 SELECT org_id
62 INTO x_org_id
63 FROM po_system_parameters;
64
65 return (x_org_id);
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 return (NULL);
70
71 END get_current_org;
72
73
74 /*=============================================================================
75
76 FUNCTION: is_owning_org
77
78 DESCRIPTION: Returns TRUE if the current org is the owning org of the
79 po_header_id and the document is a Global Agreement.
80 FALSE otherwise.
81
82 =============================================================================*/
83 FUNCTION is_owning_org
84 (
85 p_po_header_id IN PO_HEADERS.po_header_id%TYPE
86 )
87 RETURN BOOLEAN
88 IS
89 l_global_agreement_flag PO_HEADERS.global_agreement_flag%TYPE;
90 BEGIN
91
92 SELECT global_agreement_flag
93 INTO l_global_agreement_flag -- all documents held in PO_HEADERS
94 FROM po_headers -- are "owned" by the current org
95 WHERE po_header_id = p_po_header_id;
96
97 IF ( l_global_agreement_flag = 'Y' ) THEN-- document must also be a GA
98 return (TRUE); -- for it to have an "owning org"
99 ELSE
100 return (FALSE);
101 END IF;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 return (FALSE);
106
107 END is_owning_org;
108
109 --< Shared Proc FPJ Start >
110 -- Modified signature, and fixed implementation to use _TL table
111 --------------------------------------------------------------------------------
112 --Start of Comments
113 --Name: get_org_name
114 --Pre-reqs:
115 -- None.
116 --Modifies:
117 -- None.
118 --Locks:
119 -- None.
120 --Function:
121 -- Gets the translated name of the operating unit p_org_id.
122 --Parameters:
123 --IN:
124 --p_org_id
125 -- The operating unit ID
126 --Returns:
127 -- The translated operating unit name of p_org_id. If an exception occurs,
128 -- then returns NULL.
129 --Testing:
130 --End of Comments
131 --------------------------------------------------------------------------------
132 FUNCTION get_org_name(p_org_id IN NUMBER) RETURN VARCHAR2
133 IS
134 l_name HR_ALL_ORGANIZATION_UNITS_TL.name%TYPE;
135 BEGIN
136
137 SELECT name
138 INTO l_name
139 FROM hr_all_organization_units_tl
140 WHERE organization_id = p_org_id
141 AND language = USERENV('LANG');
142
143 return (l_name);
144
145 EXCEPTION
146
147 WHEN OTHERS THEN
148 return (NULL);
149
150 END get_org_name;
151 --< Shared Proc FPJ End >
152
153 /*=============================================================================
154
155 FUNCTION: is_global_agreement
156
157 DESCRIPTION: Returns TRUE if the po_header_id is a Global Agreement.
158 FALSE otherwise.
159
160 =============================================================================*/
161 FUNCTION is_global_agreement
162 (
163 p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE
164 )
165 RETURN BOOLEAN
166 IS
167 l_global_agreement_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
168
169 BEGIN
170 SELECT global_agreement_flag
171 INTO l_global_agreement_flag
172 FROM po_headers_all
173 WHERE po_header_id = p_po_header_id;
174
175 IF (l_global_agreement_flag = 'Y') THEN
176 return (TRUE);
177 ELSE
178 return (FALSE);
179 END IF;
180
181 EXCEPTION
182
183 WHEN OTHERS THEN
184 return (FALSE);
185
186 END is_global_agreement;
187
188
189 --=============================================================================
190 -- Function : is_enabled
191 -- Type : Private
192 --
193 -- Pre-reqs : p_po_header_id must refer to a Global Agreement.
194 -- Modifies : -
195 -- Description : Determines if the current OU is an enabled Requesting Org for
196 -- the input GA doc.
197 --
198 -- Parameters : p_po_header_id - document ID for the Global Agreement
199 --
200 -- Returns : TRUE if current OU is an enabled Requesting Org for the GA.
201 -- FALSE otherwise.
202 -- Exceptions : -
203 -- Notes : Functionality changed due to new column purchasing_org_id added
204 -- in Shared Proc FPJ.
205 --=============================================================================
206 FUNCTION is_enabled ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE )
207 RETURN BOOLEAN
208 IS
209 l_enabled_flag PO_GA_ORG_ASSIGNMENTS.enabled_flag%TYPE;
210
211 BEGIN
212
213 --SQL Gets the 'enabled_flag' to determine if
214 --SQL the current OU is enabled for the given
215 --SQL Global Agreement.
216 --
217 SELECT pgoa.enabled_flag
218 INTO l_enabled_flag
219 FROM po_ga_org_assignments pgoa,
220 po_system_parameters psp
221 WHERE pgoa.po_header_id = p_po_header_id -- input GA ID
222 AND pgoa.organization_id = psp.org_id; -- current OU
223
224 IF ( l_enabled_flag = 'Y' ) THEN
225 return (TRUE);
226 ELSE
227 return (FALSE);
228 END IF;
229
230 EXCEPTION
231
232 WHEN OTHERS THEN
233 return (FALSE);
234
235 END is_enabled;
236
237
238 /*=============================================================================
239
240 FUNCTION: enabled_orgs_exist
241
242 DESCRIPTION: Returns TRUE if enabled orgs (other than the Owning OU) for
243 the particular 'po_header_id' exist, and FALSE otherwise.
244
245 =============================================================================*/
246 FUNCTION enabled_orgs_exist
247 (
248 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
249 p_owning_org_id IN PO_HEADERS_ALL.org_id%TYPE
250 )
251 RETURN BOOLEAN
252 IS
253 l_enabled_orgs_exist VARCHAR2(1) := 'N';
254 BEGIN
255
256 SELECT 'Y'
257 INTO l_enabled_orgs_exist
258 FROM po_ga_org_assignments
259 WHERE po_header_id = p_po_header_id -- for the current GA
260 AND organization_id <> p_owning_org_id -- for OUs besides Owning OU
261 AND enabled_flag = 'Y' -- that are enabled
262 HAVING count(*) > 0;
263
264 IF ( l_enabled_orgs_exist = 'Y' ) THEN
265 return (TRUE);
266 ELSE
267 return (FALSE);
268 END IF;
269
270 EXCEPTION
271
272 WHEN NO_DATA_FOUND THEN
273 return (FALSE); -- no rows exist
274
275 WHEN OTHERS THEN
276 PO_MESSAGE_S.sql_error('enabled_orgs_exist','000',sqlcode);
277 RAISE;
278
279 END enabled_orgs_exist;
280
281
282 --=============================================================================
283 -- Function : is_referenced
284 -- Type : Private
285 --
286 -- Pre-reqs : p_po_line_id must refer to a Global Agreement line ID.
287 -- Modifies : -
288 --
289 -- Description : Determines if any Standard POs (in any status) reference
290 -- the GA line.
291 --
292 -- Parameters : p_po_line_id - line ID for the Global Agreement
293 --
294 -- Returns : TRUE - if Standard POs exist which reference this line
295 -- FALSE - otherwise
296 --
297 -- Exceptions : -
298 --=============================================================================
299 FUNCTION is_referenced
300 (
301 p_po_line_id IN PO_LINES_ALL.from_line_id%TYPE
302 )
303 RETURN BOOLEAN
304 IS
305 l_count NUMBER;
306
307 BEGIN
308
309 SELECT count('Standard POs referencing GA line')
310 INTO l_count
311 FROM po_lines_all
312 WHERE from_line_id = p_po_line_id;
313
314 IF ( l_count > 0 ) THEN
315 return (TRUE);
316 ELSE
317 return (FALSE);
318 END IF;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 return (FALSE);
323
324 END is_referenced;
325
326
327 --< Shared Proc FPJ Start >
328 -- Modified signature, and fixed implementation to use _TL table
329 /*=============================================================================
330
331 PROCEDURE: get_ga_values
332
333 DESCRIPTION: Based on po_header_id, fetches the global_agreement_flag,
334 owning_org_id, owning_org_name. If po_header_id is not a
335 Global Agreement, returns 'N' for global_agreement_flag and
336 NULL's for owning_org_id and owning_org_name.
337
338 =============================================================================*/
339 PROCEDURE get_ga_values
340 (
341 p_po_header_id IN NUMBER,
342 x_global_agreement_flag OUT NOCOPY VARCHAR2,
343 x_owning_org_id OUT NOCOPY NUMBER,
344 x_owning_org_name OUT NOCOPY VARCHAR2
345 )
346 IS
347 l_global_agreement_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
348
349 BEGIN
350 SELECT nvl(poh.global_agreement_flag, 'N'),
351 decode(poh.global_agreement_flag, -- only return values
352 'Y', poh.org_id, -- if Global Agreement.
353 NULL ), -- else, return NULL
354 decode(poh.global_agreement_flag,
355 'Y', hrou.name,
356 NULL )
357 INTO x_global_agreement_flag,
358 x_owning_org_id,
359 x_owning_org_name
360 FROM po_headers_all poh,
361 hr_all_organization_units_tl hrou
362 WHERE poh.po_header_id = p_po_header_id
363 AND hrou.organization_id = poh.org_id
364 AND hrou.language = USERENV('LANG');
365
366 EXCEPTION
367
368 WHEN OTHERS THEN
369 po_message_s.sql_error('PO_GA_PVT.get_ga_values', '000', sqlcode);
370
371 END get_ga_values;
372 --< Shared Proc FPJ End >
373
374 --=============================================================================
375 -- Function : is_expired
376 -- Type : Private
377 --
378 -- Pre-reqs : p_po_header_id/p_po_line_id must refer to Global Agreement
379 -- Modifies : -
380 --
381 -- Description : Determines if the Global Agreement/line are expired.
382 --
383 -- Parameters : p_po_header_id - document ID for the Global Agreement
384 -- p_po_line_id - line ID for Global Agreement
385 --
386 -- Returns : TRUE - if Global Agreement is expired
387 -- FALSE - otherwise
388 --
389 -- Exceptions : -
390 --=============================================================================
391 FUNCTION is_expired
392 (
393 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
394 )
395 RETURN BOOLEAN
396 IS
397 l_header_end_date PO_HEADERS_ALL.end_date%TYPE;
398 l_line_expiration_date PO_LINES_ALL.expiration_date%TYPE;
399
400 BEGIN
401
402 --SQL Get the end_date for the header
403 --SQL and the expiration_date for the line
404 --
405 SELECT poh.end_date,
406 pol.expiration_date
407 INTO l_header_end_date,
408 l_line_expiration_date
409 FROM po_headers_all poh,
410 po_lines_all pol
411 WHERE poh.po_header_id = pol.po_header_id -- JOIN
412 AND pol.po_line_id = p_po_line_id;
413
414 -- Both the header end_date and line expiration_date must be in the future
415 -- for the GA/line to not be expired
416 --
417 IF ( ( trunc(sysdate) <= trunc(nvl(l_header_end_date, sysdate)) )
418 AND ( trunc(sysdate) <= trunc(nvl(l_line_expiration_date, sysdate)) ) )
419 THEN
420 return (FALSE);
421 ELSE
422 return (TRUE);
423 END IF;
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 return (TRUE);
428
429 END is_expired;
430
431
432 --=============================================================================
433 -- Function : is_approved
434 -- Type : Private
435 --
436 -- Pre-reqs : p_po_line_id must refer to a Global Agreement line ID.
437 -- Modifies : -
438 --
439 -- Description : Determines if the Global Agreement is...
440 -- (a) Approved
441 -- (b) not Cancelled
442 -- (c) not On Hold
443 -- (d) not Finally Closed
444 -- and if the Global Agreement line is...
445 -- (a) not Cancelled
446 -- (b) not On Hold
447 -- (c) not Finally Closed
448 --
449 -- Parameters : p_po_line_id - line ID for the Global Agreement
450 --
451 -- Returns : TRUE - if Global Agreement meets above requirements
452 -- FALSE - otherwise
453 --
454 -- Exceptions : -
455 --=============================================================================
456 FUNCTION is_approved
457 (
458 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
459 )
460 RETURN BOOLEAN
461 IS
462 l_count NUMBER;
463
464 BEGIN
465
466 --SQL Count the number of records that meet the requirements
467 --SQL listed in the specifications. If the requirements are met
468 --SQL for the particular line_id, the count should return 1.
469 --SQL Else, count should return 0.
470 --
471 SELECT count('Line exists with following conditions.')
472 INTO l_count
473 FROM po_headers_all poh,
474 po_lines_all pol
475 WHERE
476 nvl(poh.cancel_flag, 'N') = 'N'
477 AND nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
478 AND nvl(poh.approved_flag, 'N') = 'Y'
479 AND nvl(poh.user_hold_flag,'N') = 'N'
480
481 AND nvl(pol.cancel_flag, 'N') = 'N'
482 AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
483 AND nvl(pol.user_hold_flag,'N') = 'N'
484
485 AND poh.po_header_id = pol.po_header_id -- JOIN
486 AND pol.po_line_id = p_po_line_id;
487
488 -- Exactly 1 row should be retrieved if requirements are met.
489 --
490 IF ( l_count = 1 ) THEN
491 return (TRUE);
492 ELSE
493 return (FALSE);
494 END IF;
495
496 EXCEPTION
497
498 WHEN OTHERS THEN
499 return (FALSE);
500
501 END is_approved;
502
503
504 --=============================================================================
505 -- Function : is_ga_valid
506 -- Type : Private
507 --
508 -- Pre-reqs : p_po_header_id/p_po_line_id must be a GA document/line ID.
509 -- Modifies : -
510 --
511 -- Description : Determines if the Global Agreement line is valid...
512 -- (1) GA/line is not expired
513 -- (2) GA/line Approved (NOT Cancelled, On Hold, Finally Closed)
514 --
515 -- Parameters : p_po_header_id - document ID for the Global Agreement
516 -- p_po_line_id - ID of the Global Agreement line
517 --
518 -- Returns : TRUE - if Global Agreement line is valid
519 -- FALSE - otherwise
520 --
521 -- Exceptions : -
522 -- Notes : Functionality changed by Shared Proc FPJ. No longer performs
523 -- enabled org assignment validation.
524 --=============================================================================
525 FUNCTION is_ga_valid
526 (
527 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
528 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
529 )
530 RETURN BOOLEAN
531 IS
532 BEGIN
533
534 --< Shared Proc FPJ > Bug 3301427: Removed org assignment check. This
535 -- function should not validate against OU's for enabled org assignments.
536 IF ( ( NOT is_expired(p_po_line_id) )
537 AND ( is_approved(p_po_line_id) ) )
538 THEN
539 return (TRUE);
540 ELSE
541 return (FALSE);
542 END IF;
543
544 EXCEPTION
545
546 WHEN OTHERS THEN
547 return (FALSE);
548
549 END is_ga_valid;
550
551
552 --=============================================================================
553 -- Function : is_date_valid
554 -- Type : Private
555 --
556 -- Pre-reqs : p_po_header_id must be a valid document ID.
557 -- Modifies : -
558 --
559 -- Description : Determines if the input date falls between the document's
560 -- Start and End dates. If the input date is NULL, it is valid.
561 --
562 -- Parameters : p_po_header_id - document ID for the GA or Quotation
563 -- p_date - date to compare
564 --
565 -- Returns : TRUE - if p_date falls between document's Start/End dates.
566 -- FALSE - otherwise.
567 --
568 -- Exceptions : -
569 --=============================================================================
570 FUNCTION is_date_valid
571 (
572 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
573 p_date IN DATE
574 )
575 RETURN BOOLEAN
576 IS
577 l_start_date PO_HEADERS_ALL.start_date%TYPE;
578 l_end_date PO_HEADERS_ALL.end_date%TYPE;
579
580 BEGIN
581
582 -- If the input date is NULL, then treat as valid.
583 --
584 IF ( p_date IS NULL ) THEN
585 return (TRUE);
586 END IF;
587
588 --SQL Get the Start/End dates for the given document.
589 --
590 SELECT start_date,
591 end_date
592 INTO l_start_date,
593 l_end_date
594 FROM po_headers_all
595 WHERE po_header_id = p_po_header_id;
596
597 --< NBD TZ/Timestamp FPJ Start >
598 -- The IN parameter p_date would be the need-by-date which is now
599 -- timestamped. Whereas the efeectivity dates are not timestamped.
600 -- The boundary case of the need-by-date comparision with the End
601 -- Effectivity Date would fail if the dates are not truncated. Therefore,
602 -- truncating the dates during the comparison.
603 --IF ( p_date BETWEEN nvl(l_start_date, p_date-1)
604 -- AND nvl(l_end_date, p_date+1) )
605 IF ( trunc(p_date) BETWEEN trunc(nvl(l_start_date, p_date-1))
606 AND trunc(nvl(l_end_date, p_date+1)) )
607 --< NBD TZ/Timestamp FPJ End >
608 THEN
609 return (TRUE);
610 ELSE
611 return (FALSE);
612 END IF;
613
614 EXCEPTION
615
616 WHEN OTHERS THEN
617 PO_MESSAGE_S.sql_error('is_date_valid','000',sqlcode);
618 RAISE;
619
620 END is_date_valid;
621
622
623 --< Shared Proc FPJ Start>
624 -- Rewrote to be a procedure
625 --------------------------------------------------------------------------------
626 --Start of Comments
627 --Name: validate_item
628 --Pre-reqs:
629 -- None.
630 --Modifies:
631 -- FND_MSG_PUB
632 --Locks:
633 -- None.
634 --Function:
635 -- Performs item validity checks between p_org_id and p_valid_org_id. Appends
636 -- to the API message list upon error.
637 --Parameters:
638 --IN:
639 --p_item_id
640 -- The item ID to validate
641 --p_org_id
642 -- The org ID of the OU to validate against p_valid_org_id
643 --p_valid_org_id
644 -- The org ID of the OU where this item is already valid (e.g. GA owning org)
645 --OUT:
646 --x_return_status
647 -- FND_API.g_ret_sts_success - if the procedure completed successfully
648 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
649 --x_is_purchasable
650 -- TRUE if the item master item in the Financial Options Inventory Organization
651 -- of p_org_id is defined as 'purchasable', or if this is a one-time item, or
652 -- p_org_id equals p_valid_org_id.
653 -- FALSE otherwise.
654 --x_is_same_uom_class
655 -- TRUE if the item in p_org_id shares the same UOM class as p_valid_org_id, or
656 -- if this is a one-time item, or p_org_id equals p_valid_org_id.
657 -- FALSE otherwise.
658 --x_is_not_osp_item
659 -- TRUE if the item is NOT defined as 'Outside Processing' in p_org_id, or if
660 -- this is a one-time item, or p_org_id equals p-valid_org_id.
661 -- FALSE otherwise.
662 --End of Comments
663 --------------------------------------------------------------------------------
664 PROCEDURE validate_item
665 ( x_return_status OUT NOCOPY VARCHAR2,
666 p_item_id IN NUMBER,
667 p_org_id IN NUMBER,
668 p_valid_org_id IN NUMBER,
669 x_is_purchasable OUT NOCOPY BOOLEAN,
670 x_is_same_uom_class OUT NOCOPY BOOLEAN,
671 x_is_not_osp_item OUT NOCOPY BOOLEAN
672 )
673 IS
674 l_purchasable_flag MTL_SYSTEM_ITEMS_B.purchasing_enabled_flag%TYPE;
675 l_osp_flag MTL_SYSTEM_ITEMS_B.outside_operation_flag%TYPE;
676 l_uom_class MTL_UNITS_OF_MEASURE_TL.uom_class%TYPE;
677 l_valid_uom_class MTL_UNITS_OF_MEASURE_TL.uom_class%TYPE;
678 l_progress VARCHAR2(3);
679
680 BEGIN
681 l_progress := '000';
682 x_return_status := FND_API.g_ret_sts_success;
683
684 IF g_debug_stmt THEN
685 PO_DEBUG.debug_stmt
686 (p_log_head => g_module_prefix||'validate_item',
687 p_token => 'invoked',
688 p_message => 'item ID: '||p_item_id||' org ID: '||p_org_id||
689 ' valid org ID: '||p_valid_org_id);
690 END IF;
691
692 IF ( p_item_id IS NULL) OR -- item is a one-time item
693 ( p_org_id = p_valid_org_id ) -- or same OU's
694 THEN
695 x_is_purchasable := TRUE;
696 x_is_same_uom_class := TRUE;
697 x_is_not_osp_item := TRUE;
698 ELSE
699
700 l_progress := '010';
701
702 SELECT
703 ITEMS1.purchasing_enabled_flag,
704 ITEMS1.outside_operation_flag,
705 UOM1.uom_class,
706 UOM2.uom_class
707 INTO
708 l_purchasable_flag,
709 l_osp_flag,
710 l_uom_class,
711 l_valid_uom_class
712 FROM
713 financials_system_params_all FSP1,
714 financials_system_params_all FSP2, -- valid org
715 mtl_system_items_b ITEMS1,
716 mtl_system_items_b ITEMS2, -- valid org
717 mtl_units_of_measure_tl UOM1,
718 mtl_units_of_measure_tl UOM2 -- valid org
719 WHERE
720 FSP1.org_id = p_org_id
721 AND ITEMS1.inventory_item_id = p_item_id
722 AND ITEMS1.organization_id = FSP1.inventory_organization_id
723 AND UOM1.uom_code = ITEMS1.primary_uom_code
724 AND UOM1.language = USERENV('LANG')
725 AND FSP2.org_id = p_valid_org_id
726 AND ITEMS2.inventory_item_id = p_item_id
727 AND ITEMS2.organization_id = FSP2.inventory_organization_id
728 AND UOM2.uom_code = ITEMS2.primary_uom_code
729 AND UOM2.language = USERENV('LANG');
730
731 x_is_purchasable := (l_purchasable_flag = 'Y');
732 x_is_same_uom_class := (l_uom_class = l_valid_uom_class);
733 x_is_not_osp_item := (l_osp_flag = 'N');
734
735 END IF;
736
737 IF g_debug_stmt THEN
738 PO_DEBUG.debug_var
739 (p_log_head => g_module_prefix||'validate_item',
740 p_progress => l_progress,
741 p_name => 'x_is_purchasable',
742 p_value => x_is_purchasable);
743 PO_DEBUG.debug_var
744 (p_log_head => g_module_prefix||'validate_item',
745 p_progress => l_progress,
746 p_name => 'x_is_same_uom_class',
747 p_value => x_is_same_uom_class);
748 PO_DEBUG.debug_var
749 (p_log_head => g_module_prefix||'validate_item',
750 p_progress => l_progress,
751 p_name => 'x_is_not_osp_item',
752 p_value => x_is_not_osp_item);
753 END IF;
754
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 x_is_purchasable := FALSE;
758 x_is_same_uom_class := FALSE;
759 x_is_not_osp_item := FALSE;
760 WHEN OTHERS THEN
761 x_return_status := FND_API.g_ret_sts_unexp_error;
762 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
763 p_procedure_name => 'validate_item',
764 p_error_text => 'Progress: '||l_progress||
765 ' Error: '||SUBSTRB(SQLERRM,1,215));
766 IF g_debug_unexp THEN
767 PO_DEBUG.debug_exc
768 (p_log_head => g_module_prefix||'validate_item',
769 p_progress => l_progress);
770 END IF;
771 END validate_item;
772
773 /*=============================================================================
774
775 FUNCTION: is_ship_to_org_valid
776
777 DESCRIPTION: Returns TRUE if the Ship-To Org specified in the
778 Price Break is in the same Set of Books as the current org.
779 FALSE otherwise.
780
781 =============================================================================*/
782 FUNCTION is_ship_to_org_valid
783 (
784 p_ship_to_org_id PO_LINE_LOCATIONS_ALL.ship_to_organization_id%TYPE
785 )
786 RETURN BOOLEAN
787 IS
788 l_current_sob ORG_ORGANIZATION_DEFINITIONS.set_of_books_id%TYPE;
789 l_ga_sob ORG_ORGANIZATION_DEFINITIONS.set_of_books_id%TYPE;
790
791 BEGIN
792
793 -- Bug 3014005
794 -- If the ship to org is not specified on the price break as we could only
795 -- have quantity price breaks we need to return true for that case.
796
797 IF p_ship_to_org_id is null THEN
798 return (TRUE);
799 END IF;
800
801 SELECT FSP.set_of_books_id,
802 OOD.set_of_books_id
803 INTO l_current_sob,
804 l_ga_sob
805 FROM financials_system_parameters FSP,
806 org_organization_definitions OOD
807 WHERE OOD.organization_id = p_ship_to_org_id;
808
809 IF ( l_current_sob = l_ga_sob ) THEN
810 return (TRUE);
811 ELSE
812 return (FALSE);
813 END IF;
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 return (FALSE);
818
819 END is_ship_to_org_valid;
820
821
822 /*=============================================================================
823
824 FUNCTION: get_vendor_site_id
825
826 DESCRIPTION: Returns the vendor_site_id for the current org specified in
827 the input Global Agreement. Returns NULL if the current org
828 is not enabled for the Global Agreement.
829
830 =============================================================================*/
831 FUNCTION get_vendor_site_id
832 (
833 p_po_header_id PO_GA_ORG_ASSIGNMENTS.po_header_id%TYPE
834 )
835 RETURN PO_GA_ORG_ASSIGNMENTS.vendor_site_id%TYPE
836 IS
837 x_vendor_site_id PO_GA_ORG_ASSIGNMENTS.vendor_site_id%TYPE;
838
839 BEGIN
840
841 SELECT pgoa.vendor_site_id
842 INTO x_vendor_site_id
843 FROM po_ga_org_assignments pgoa,
844 po_system_parameters psp
845 WHERE pgoa.po_header_id = p_po_header_id
846 AND pgoa.organization_id = psp.org_id
847 AND pgoa.enabled_flag = 'Y'; --<Shared Proc FPJ>
848
849 return (x_vendor_site_id);
850
851 EXCEPTION
852 WHEN OTHERS THEN
853 return (NULL);
854
855 END get_vendor_site_id;
856
857
858 --=============================================================================
859 -- PROCEDURE : get_currency_info <2694908>
860 -- TYPE : Private
861 --
862 -- PRE-REQS : p_po_header_id must refer to a Global Agreement.
863 -- MODIFIES : -
864 --
865 -- DESCRIPTION : Retrieves all currency-related info for the Global Agreement
866 -- (i.e. currency_code, rate_type, rate_date, rate).
867 --
868 -- PARAMETERS : p_po_header_id - document ID for the GA
869 --
870 -- RETURNS : x_currency_code - Currency of the GA
871 -- x_rate_type - Default rate type for current org
872 -- x_rate_date - sysdate
873 -- x_rate - Rate between GA and functional currency
874 --
875 -- EXCEPTIONS : GL_CURRENCY_API.no_rate - No rate exists
876 -- GL_CURRENCY_API.invalid_currency - Invalid currency
877 --=============================================================================
878 PROCEDURE get_currency_info
879 (
880 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
881 x_currency_code OUT NOCOPY PO_HEADERS_ALL.currency_code%TYPE ,
882 x_rate_type OUT NOCOPY PO_HEADERS_ALL.rate_type%TYPE,
883 x_rate_date OUT NOCOPY PO_HEADERS_ALL.rate_date%TYPE,
884 x_rate OUT NOCOPY PO_HEADERS_ALL.rate%TYPE
885 )
886 IS
887 l_set_of_books_id FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
888 BEGIN
889
890 -- Get Currency Code specifically for the Global Agreement
891 SELECT currency_code
892 INTO x_currency_code
893 FROM po_headers_all
894 WHERE po_header_id = p_po_header_id;
895
896 -- Get the current Set of Books ID
897 SELECT set_of_books_id
898 INTO l_set_of_books_id
899 FROM financials_system_parameters;
900
901 -- Get the default Rate Type for the current org
902 SELECT default_rate_type
903 INTO x_rate_type
904 FROM po_system_parameters;
905
906 -- Set Rate Date equal to current date
907 x_rate_date := sysdate;
908
909 -- Retrieve rate based on above values
910 x_rate := PO_CORE_S.get_conversion_rate( l_set_of_books_id ,
911 x_currency_code ,
912 x_rate_date ,
913 x_rate_type );
914 EXCEPTION
915
916 WHEN NO_DATA_FOUND THEN
917 x_currency_code := NULL;
918 x_rate_type := NULL;
919 x_rate_date := NULL;
920 x_rate := NULL;
921
922 END get_currency_info;
923
924
925 --=============================================================================
926 -- FUNCTION : rate_exists -- <2709419>
927 -- TYPE : Private
928 --
929 -- PRE-REQS : -
930 -- MODIFIES : -
931 --
932 -- DESCRIPTION : Determines if a Rate is defined in the current org for
933 -- the input document's currency and the functional currency.
934 --
935 -- PARAMETERS : p_po_header_id - document ID for the GA
936 --
937 -- RETURNS : TRUE if a Rate is defined in the current org between the GA
938 -- and functional currency. FALSE otherwise.
939 --=============================================================================
940 FUNCTION rate_exists
941 (
942 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
943 )
944 RETURN BOOLEAN
945 IS
946 l_currency_code PO_HEADERS_ALL.currency_code%TYPE;
947 l_sob_id FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
948 l_rate_type PO_SYSTEM_PARAMETERS.default_rate_type%TYPE;
949 x_rate PO_HEADERS_ALL.rate%TYPE;
950
951 BEGIN
952
953 SELECT currency_code
954 INTO l_currency_code
955 FROM po_headers_all
956 WHERE po_header_id = p_po_header_id;
957
958 SELECT set_of_books_id
959 INTO l_sob_id
960 FROM financials_system_parameters;
961
962 SELECT default_rate_type
963 INTO l_rate_type
964 FROM po_system_parameters;
965
966 x_rate := PO_CORE_S.get_conversion_rate( l_sob_id ,
967 l_currency_code ,
968 sysdate ,
969 l_rate_type );
970 IF ( x_rate IS NULL )
971 THEN
972 return (FALSE);
973 ELSE
974 return (TRUE);
975 END IF;
976
977 EXCEPTION
978
979 WHEN OTHERS THEN
980 return (FALSE);
981
982 END rate_exists;
983
984 --<Bug 2721740 mbhargav START>
985 --=============================================================================
986 -- PROCEDURE : sync_ga__line_attachments <2721740>
987 -- TYPE : Private
988 --
989 -- PRE-REQS : -
990 -- MODIFIES : -
991 --
992 -- DESCRIPTION : This procedure called when 'SAVE' is issued on enter PO form
993 -- copies attachment from referenced GA header and GA line to
994 -- Standard PO line. This is alos called from post-query on lines block
995 --
996 -- PARAMETERS : p_po_header_id - GA header Id
997 -- p_po_line_id - GA Line Id
998 -- RETURNS : -
999 ---
1000 -- EXCEPTIONS : -
1001 --==========================================================================
1002 PROCEDURE sync_ga_line_attachments(
1003 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE,
1004 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
1005 x_return_status OUT NOCOPY VARCHAR2,
1006 x_msg_data OUT NOCOPY VARCHAR2) IS
1007
1008 l_count_po_line_att NUMBER := 0;
1009 l_count_ga_line_att NUMBER := 0;
1010
1011 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_GA_LINE_ATTACHMENTS';
1012 l_api_return_status VARCHAR2(1);
1013 l_api_msg_data VARCHAR2(2000);
1014
1015 BEGIN
1016 IF is_global_agreement(p_po_header_id) THEN
1017
1018 --<Bug 2887275 mbhargav>
1019 --Changed the call to delete_attachments. Explicitely passing
1020 --'Y' for x_automatically_added_flag as we want to delete only
1021 --those attachment references that have been added automatically
1022 --in procedure reference_attachments
1023 --first delete all references on PO_IN_GA_LINES
1024 fnd_attached_documents2_pkg.delete_attachments('PO_IN_GA_LINES',
1025 p_po_line_id,
1026 '', '', '', '', 'N', 'Y');
1027
1028 --Copy reference from entity 'PO_LINES' to enity 'PO_IN_GA_LINES'
1029 reference_attachments(
1030 p_api_version => 1.0,
1031 p_from_entity_name => 'PO_LINES',
1032 p_from_pk1_value => p_po_line_id,
1033 p_from_pk2_value => '',
1034 p_from_pk3_value => '',
1035 p_from_pk4_value => '',
1036 p_from_pk5_value => '',
1037 p_to_entity_name => 'PO_IN_GA_LINES',
1038 p_to_pk1_value => p_po_line_id,
1039 p_to_pk2_value => '',
1040 p_to_pk3_value => '',
1041 p_to_pk4_value => '',
1042 p_to_pk5_value => '',
1043 p_automatically_added_flag => 'Y',
1044 x_return_status => l_api_return_status,
1045 x_msg_data => l_api_msg_data);
1046
1047 IF l_api_return_status = FND_API.G_RET_STS_ERROR THEN
1048 x_msg_data := l_api_msg_data;
1049 RAISE FND_API.G_EXC_ERROR;
1050 ELSIF l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1051 x_msg_data := l_api_msg_data;
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053 END IF;
1054
1055 END IF; --references a GA
1056
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058
1059 EXCEPTION
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062 WHEN FND_API.G_EXC_ERROR THEN
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 WHEN OTHERS THEN
1065 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1066 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
1067 END IF;
1068
1069 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1070 p_encoded => 'F');
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1072
1073 END sync_ga_line_attachments;
1074
1075 --=============================================================================
1076 -- PROCEDURE : reference_attachments <2721740>
1077 -- TYPE : Public API
1078 --
1079 -- PRE-REQS : -
1080 -- MODIFIES : -
1081 --
1082 -- DESCRIPTION : This API does a shallow copy of attachments from entity
1083 -- p_from_entity_name to entity p_to_entity_name. By shallow copy
1084 -- we mean it copies only the reference and not the actual physical
1085 -- copy of attachments.
1086 --
1087 -- PARAMETERS : -
1088 --
1089 -- RETURNS : x_return_status - values FND_API.G_RET_STS_SUCCESS
1090 -- FND_API.G_RET_STS_ERROR
1091 -- FND_API.G_RET_STS_UNEXP_ERROR
1092 -- x_msg_data in case of failure
1093 ---
1094 -- EXCEPTIONS : -
1095 --==========================================================================
1096 PROCEDURE reference_attachments(
1097 p_api_version IN NUMBER,
1098 p_from_entity_name IN VARCHAR2,
1099 p_from_pk1_value IN VARCHAR2,
1100 p_from_pk2_value IN VARCHAR2 DEFAULT NULL,
1101 p_from_pk3_value IN VARCHAR2 DEFAULT NULL,
1102 p_from_pk4_value IN VARCHAR2 DEFAULT NULL,
1103 p_from_pk5_value IN VARCHAR2 DEFAULT NULL,
1104 p_to_entity_name IN VARCHAR2,
1105 p_to_pk1_value IN VARCHAR2,
1106 p_to_pk2_value IN VARCHAR2 DEFAULT NULL,
1107 p_to_pk3_value IN VARCHAR2 DEFAULT NULL,
1108 p_to_pk4_value IN VARCHAR2 DEFAULT NULL,
1109 p_to_pk5_value IN VARCHAR2 DEFAULT NULL,
1110 p_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
1111 x_return_status OUT NOCOPY VARCHAR2,
1112 x_msg_data OUT NOCOPY VARCHAR2) IS
1113
1114
1115 l_api_name CONSTANT VARCHAR2(30) := 'REFERENCE_ATTACHMENTS';
1116 l_api_version CONSTANT NUMBER := 1.0;
1117
1118 BEGIN
1119 -- Standard call to check for call compatibility
1120 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1121 THEN
1122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1123 END IF;
1124
1125 --Insert new records in fnd_attached_documents table to create
1126 --reference from p_from_entity_name to p_to_entity_name
1127 INSERT INTO fnd_attached_documents(
1128 ATTACHED_DOCUMENT_ID,
1129 DOCUMENT_ID,
1130 CREATION_DATE,
1131 CREATED_BY,
1132 LAST_UPDATE_DATE,
1133 LAST_UPDATED_BY,
1134 LAST_UPDATE_LOGIN,
1135 SEQ_NUM,
1136 ENTITY_NAME,
1137 PK1_VALUE,
1138 PK2_VALUE,
1139 PK3_VALUE,
1140 PK4_VALUE,
1141 PK5_VALUE,
1142 AUTOMATICALLY_ADDED_FLAG,
1143 PROGRAM_APPLICATION_ID,
1144 PROGRAM_ID,
1145 PROGRAM_UPDATE_DATE,
1146 REQUEST_ID,
1147 ATTRIBUTE_CATEGORY,
1148 ATTRIBUTE1,
1149 ATTRIBUTE2,
1150 ATTRIBUTE3,
1151 ATTRIBUTE4,
1152 ATTRIBUTE5,
1153 ATTRIBUTE6,
1154 ATTRIBUTE7,
1155 ATTRIBUTE8,
1156 ATTRIBUTE9,
1157 ATTRIBUTE10,
1158 ATTRIBUTE11,
1159 ATTRIBUTE12,
1160 ATTRIBUTE13,
1161 ATTRIBUTE14,
1162 ATTRIBUTE15,
1163 COLUMN1,
1164 APP_SOURCE_VERSION,
1165 CATEGORY_ID,
1166 STATUS)
1167 SELECT fnd_attached_documents_s.nextval,
1168 fad.DOCUMENT_ID,
1169 fad.CREATION_DATE,
1170 fad.CREATED_BY,
1171 fad.LAST_UPDATE_DATE,
1172 fad.LAST_UPDATED_BY,
1173 fad.LAST_UPDATE_LOGIN,
1174 SEQ_NUM,
1175 p_to_entity_name, --entity_name
1176 p_to_pk1_value, --pk1 value
1177 p_to_pk2_value, --PK2_VALUE,
1178 p_to_pk3_value, --PK3_VALUE,
1179 p_to_pk4_value, --PK4_VALUE,
1180 p_to_pk5_value, --PK5_VALUE,
1181 nvl(p_AUTOMATICALLY_ADDED_FLAG,AUTOMATICALLY_ADDED_FLAG),
1182 fad.PROGRAM_APPLICATION_ID,
1183 fad.PROGRAM_ID,
1184 fad.PROGRAM_UPDATE_DATE,
1185 fad.REQUEST_ID,
1186 ATTRIBUTE_CATEGORY,
1187 ATTRIBUTE1,
1188 ATTRIBUTE2,
1189 ATTRIBUTE3,
1190 ATTRIBUTE4,
1191 ATTRIBUTE5,
1192 ATTRIBUTE6,
1193 ATTRIBUTE7,
1194 ATTRIBUTE8,
1195 ATTRIBUTE9,
1196 ATTRIBUTE10,
1197 ATTRIBUTE11,
1198 ATTRIBUTE12,
1199 ATTRIBUTE13,
1200 ATTRIBUTE14,
1201 ATTRIBUTE15,
1202 COLUMN1,
1203 fad.APP_SOURCE_VERSION,
1204 fad.CATEGORY_ID,
1205 fad.STATUS
1206 FROM fnd_attached_documents fad, fnd_documents fd, financials_system_parameters fsp
1207 WHERE entity_name = p_from_entity_name
1208 AND pk1_value = p_from_pk1_value
1209 AND (p_from_pk2_value IS NULL
1210 OR p_from_pk2_value = pk2_value)
1211 AND (p_from_pk3_value IS NULL
1212 OR p_from_pk3_value = pk3_value)
1213 AND (p_from_pk4_value IS NULL
1214 OR p_from_pk4_value = pk4_value)
1215 AND (p_from_pk5_value IS NULL
1216 OR p_from_pk5_value = pk5_value)
1217 --<Bug 2872552 mbhargav START>
1218 AND fad.document_id = fd.document_id
1219 AND (fd.publish_flag = 'Y'
1220 --Security level is Organization
1221 OR (fd.security_type = 1 AND fd.security_id = fsp.org_id)
1222 --Security level is Set Of Books
1223 OR (fd.security_type = 2 AND fd.security_id = fsp.set_of_books_id)
1224 --Security level is NONE
1225 OR (fd.security_type = 4)
1226 );
1227 --<Bug 2872552 mbhargav END>
1228
1229 x_return_status := FND_API.G_RET_STS_SUCCESS;
1230
1231 EXCEPTION
1232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1233 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1234 p_encoded => 'F');
1235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236 WHEN FND_API.G_EXC_ERROR THEN
1237 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1238 p_encoded => 'F');
1239 x_return_status := FND_API.G_RET_STS_ERROR;
1240 WHEN OTHERS THEN
1241 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1242 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
1243 END IF;
1244
1245 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1246 p_encoded => 'F');
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248
1249 END reference_attachments;
1250 --<Bug 2721740 mbhargav END>
1251
1252 -- <GC FPJ START>
1253
1254 --=============================================================================
1255 -- PROCEDURE : is_purchasing_site_on_ga <GC FPJ>
1256 -- TYPE : Public
1257 --
1258 -- PRE-REQS : -
1259 -- MODIFIES : -
1260 --
1261 -- DESCRIPTION : This procedure checks whether global ga is purchasing
1262 -- enabled given a vendor site
1263 --
1264 -- PARAMETERS : p_po_header_id - GC header_id
1265 -- p_vendor_site_id - target site
1266 -- RETURNS : x_result : FND_API.G_TRUE if enabled,
1267 -- FND_API.G_FALSE otherwise
1268 ---
1269 -- EXCEPTIONS : -
1270 --==========================================================================
1271
1272 PROCEDURE is_purchasing_site_on_ga
1273 ( p_po_header_id IN NUMBER,
1274 p_vendor_site_id IN NUMBER,
1275 x_result OUT NOCOPY VARCHAR2
1276 ) IS
1277
1278 l_is_enabled VARCHAR2(1) := 'N';
1279
1280 BEGIN
1281
1282 SELECT 'Y'
1283 INTO l_is_enabled
1284 FROM po_headers_all POH
1285 WHERE POH.po_header_id = p_po_header_id
1286 AND EXISTS (SELECT 1
1287 FROM po_ga_org_assignments PGOA
1288 WHERE PGOA.po_header_id = p_po_header_id
1289 AND PGOA.vendor_site_id = p_vendor_site_id
1290 AND PGOA.enabled_flag = 'Y');
1291
1292 IF (l_is_enabled = 'Y') THEN
1293 x_result := FND_API.G_TRUE;
1294 ELSE
1295 x_result := FND_API.G_FALSE;
1296 END IF;
1297
1298 EXCEPTION
1299 WHEN NO_DATA_FOUND THEN
1300 x_result := FND_API.G_FALSE;
1301 END is_purchasing_site_on_ga;
1302
1303
1304 --=============================================================================
1305 -- PROCEDURE : is_local_document <GC FPJ>
1306 -- TYPE : Public
1307 --
1308 -- PRE-REQS : -
1309 -- MODIFIES : -
1310 --
1311 -- DESCRIPTION : Check whether the document is local document with document
1312 -- type specified by p_type_lookup_code
1313 --
1314 -- PARAMETERS : p_po_header_id - po_header_id
1315 -- p_type_lookup_code - doc subtype of the document
1316 -- RETURNS : TRUE if document is local
1317 -- FALSE if document is a global document, or if document type
1318 -- does not match the one specified in p_type_lookup_code
1319 --
1320 -- EXCEPTIONS : -
1321 --==========================================================================
1322
1323 FUNCTION is_local_document (p_po_header_id IN NUMBER,
1324 p_type_lookup_code IN VARCHAR2) RETURN BOOLEAN
1325 IS
1326
1327 l_global VARCHAR2(1);
1328
1329 BEGIN
1330
1331 SELECT NVL(POH.global_agreement_flag, 'N')
1332 INTO l_global
1333 FROM po_headers_all POH
1334 WHERE POH.po_header_id = p_po_header_id
1335 AND POH.type_lookup_code = p_type_lookup_code;
1336
1337 IF (l_global = 'Y') THEN -- global doc
1338 RETURN FALSE;
1339 ELSE
1340 RETURN TRUE;
1341 END IF;
1342
1343 EXCEPTION
1344 WHEN NO_DATA_FOUND THEN
1345 RETURN FALSE;
1346 WHEN OTHERS THEN
1347 PO_MESSAGE_S.sql_error('is_local_document', '000', sqlcode);
1348 END is_local_document;
1349 -- <GC FPJ END>
1350
1351 --<Shared Proc FPJ START>
1352 -------------------------------------------------------------------------------
1353 --Start of Comments
1354 --Name: GET_PURCHASING_ORG_ID
1355 --Pre-reqs:
1356 -- Assumes that p_po_header_id is valid
1357 --Modifies:
1358 -- None.
1359 --Locks:
1360 -- None.
1361 --Function:
1362 -- Hit the enabled org form of given GA and get purchasing org for
1363 -- the current org as enabled org.
1364 --Parameters:
1365 --IN:
1366 --p_po_header_id
1367 -- unique identifier of the document
1368 --Returns:
1369 -- Returns the purchasing_org_id for the current org specified in
1370 -- the input Global Agreement. Returns NULL if the current org
1371 -- is not enabled for the Global Agreement.
1372 --Notes:
1373 -- None
1374 --Testing:
1375 -- None
1376 --End of Comments
1377 -------------------------------------------------------------------------------
1378 FUNCTION get_purchasing_org_id (
1379 p_po_header_id IN NUMBER
1380 )
1381 RETURN NUMBER
1382 IS
1383 x_purchasing_org_id po_ga_org_assignments.purchasing_org_id%TYPE;
1384 BEGIN
1385 --SQL WHAT: Join to psp to get current org
1386 --SQL WHY: Need to get purchasing org_id
1387 SELECT pgoa.purchasing_org_id
1388 INTO x_purchasing_org_id
1389 FROM po_ga_org_assignments pgoa, po_system_parameters psp
1390 WHERE pgoa.po_header_id = p_po_header_id
1391 AND pgoa.organization_id = psp.org_id
1392 AND pgoa.enabled_flag = 'Y';
1393
1394 RETURN (x_purchasing_org_id);
1395 EXCEPTION
1396 WHEN OTHERS
1397 THEN
1398 RETURN (NULL);
1399 END get_purchasing_org_id;
1400
1401
1402 --------------------------------------------------------------------------------
1403 --Start of Comments
1404 --Name: validate_item_revision
1405 --Pre-reqs:
1406 -- None.
1407 --Modifies:
1408 -- FND_LOG
1409 -- FND_MSG_PUB
1410 --Locks:
1411 -- None.
1412 --Function:
1413 -- Validates that p_ga_item_revision for p_item_id is available in both
1414 -- p_org_id and p_owning_org_id. Appends to the API message list upon error.
1415 --Parameters:
1416 --IN:
1417 --p_item_id
1418 -- The item ID on the GA line
1419 --p_org_id
1420 -- The org ID of the OU to test against p_owning_org_id
1421 --p_ga_item_revision
1422 -- The item revision on the GA line. (MTL_ITEM_REVISIONS_B.revision%TYPE)
1423 --p_owning_org_id
1424 -- The GA Owning Org ID
1425 --p_check_rev_control
1426 -- If TRUE, then the revision control property of the item in p_org_id will be
1427 -- used during validation. If FALSE, then it will not be used.
1428 --OUT:
1429 --x_return_status
1430 -- FND_API.g_ret_sts_success - if the procedure completed successfully
1431 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
1432 --x_is_valid
1433 -- TRUE if:
1434 -- - p_item_id is a one-time item
1435 -- OR
1436 -- - p_org_id is equal to p_owning_org_id
1437 -- OR
1438 -- - p_ga_item_revision is available in both p_org_id and p_owning_org_id
1439 -- OR
1440 -- - p_check_rev_control is TRUE
1441 -- - p_item_id in p_org_id does not use revision control
1442 --
1443 -- FALSE otherwise.
1444 --x_item_revision
1445 -- The valid item revision for the item in p_org_id. This will be NULL if
1446 -- p_ga_item_revision is not available in p_org_id, but revision control for
1447 -- p_item_id in p_org_id is off.
1448 -- (MTL_ITEM_REVISIONS_B.revision%TYPE)
1449 --End of Comments
1450 --------------------------------------------------------------------------------
1451 PROCEDURE validate_item_revision
1452 (
1453 x_return_status OUT NOCOPY VARCHAR2,
1454 p_item_id IN NUMBER,
1455 p_org_id IN NUMBER,
1456 p_ga_item_revision IN VARCHAR2,
1457 p_owning_org_id IN NUMBER,
1458 p_check_rev_control IN BOOLEAN,
1459 x_is_valid OUT NOCOPY BOOLEAN,
1460 x_item_revision OUT NOCOPY VARCHAR2
1461 )
1462 IS
1463
1464 l_rev_control MTL_SYSTEM_ITEMS.revision_qty_control_code%TYPE;
1465 l_progress VARCHAR2(3);
1466
1467 BEGIN
1468 l_progress := '000';
1469 x_return_status := FND_API.g_ret_sts_success;
1470
1471 IF g_debug_stmt THEN
1472 PO_DEBUG.debug_stmt
1473 (p_log_head => g_module_prefix||'validate_item_revision',
1474 p_token => 'invoked',
1475 p_message => 'item ID: '||p_item_id||' org ID: '||p_org_id||
1476 ' ga item rev: '||p_ga_item_revision||' ownorg ID: '||
1477 p_owning_org_id);
1478 END IF;
1479
1480 IF (p_item_id IS NULL) OR -- One-time item
1481 (p_org_id = p_owning_org_id) OR -- Same org
1482 (p_ga_item_revision IS NULL) -- GA item revision is NULL
1483 THEN
1484 x_item_revision := p_ga_item_revision;
1485 x_is_valid := TRUE;
1486 RETURN;
1487 END IF;
1488
1489 l_progress := '010';
1490 BEGIN
1491 --SQL What: Check that p_ga_item_revision is available in p_org_id and
1492 -- p_owning_org_id.
1493 --SQL Why: Validate that p_org_id can source p_ga_item_revision from
1494 -- p_owning_org_id
1495 SELECT mir_ga.revision
1496 INTO x_item_revision
1497 FROM financials_system_params_all fspa_ga, -- GA owning org
1498 financials_system_params_all fspa,
1499 mtl_item_revisions_b mir_ga, -- GA revision
1500 mtl_item_revisions_b mir
1501 WHERE fspa_ga.org_id = p_owning_org_id
1502 AND mir_ga.organization_id = fspa_ga.inventory_organization_id
1503 AND mir_ga.inventory_item_id = p_item_id
1504 AND mir_ga.revision = p_ga_item_revision
1505 AND fspa.org_id = p_org_id
1506 AND mir.organization_id = fspa.inventory_organization_id
1507 AND mir.inventory_item_id = mir_ga.inventory_item_id
1508 AND mir.revision = mir_ga.revision;
1509
1510 -- If SQL was successful, return here
1511 x_is_valid := TRUE;
1512 RETURN;
1513
1514 EXCEPTION
1515 WHEN NO_DATA_FOUND THEN
1516 -- Could not match revisions the first time, so only continue if the
1517 -- API caller wants to check revision control
1518 IF (NOT p_check_rev_control) THEN
1519 x_is_valid := FALSE;
1520 RETURN;
1521 END IF;
1522 END;
1523
1524 l_progress := '020';
1525
1526 -- The first check failed, but the API caller wants to check the revision
1527 -- control in p_org_id
1528
1529 --SQL What: Get the revision control property of p_item_id in p_org_id where
1530 -- p_ga_item_revision exists in p_owning_org_id
1531 --SQL Why: Check if p_org_id can still source the item revision
1532 SELECT NVL(msi.revision_qty_control_code, 1)
1533 INTO l_rev_control
1534 FROM financials_system_params_all fspa_ga, -- GA owning org
1535 financials_system_params_all fspa,
1536 mtl_item_revisions_b mir_ga, -- GA revision
1537 mtl_system_items_b msi
1538 WHERE fspa_ga.org_id = p_owning_org_id
1539 AND mir_ga.organization_id = fspa_ga.inventory_organization_id
1540 AND mir_ga.inventory_item_id = p_item_id
1541 AND mir_ga.revision = p_ga_item_revision
1542 AND fspa.org_id = p_org_id
1543 AND msi.inventory_item_id = mir_ga.inventory_item_id
1544 AND msi.organization_id = fspa.inventory_organization_id;
1545
1546 IF (l_rev_control = 1) THEN
1547 -- Revision control is off, so validation passes.
1548 -- Need to set the x_item_revision to NULL.
1549 x_item_revision := NULL;
1550 x_is_valid := TRUE;
1551 ELSE
1552 -- Revision control is on, so validation fails
1553 x_item_revision := NULL;
1554 x_is_valid := FALSE;
1555 END IF; --< if rev control = 1>
1556
1557 EXCEPTION
1558 WHEN NO_DATA_FOUND THEN
1559 x_is_valid := FALSE;
1560 WHEN OTHERS THEN
1561 x_return_status := FND_API.g_ret_sts_unexp_error;
1562 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1563 p_procedure_name => 'validate_item_revision',
1564 p_error_text => 'Progress: '||l_progress||
1565 ' Error: '||SUBSTRB(SQLERRM,1,215));
1566 IF g_debug_unexp THEN
1567 PO_DEBUG.debug_exc
1568 (p_log_head => g_module_prefix || 'validate_item_revision',
1569 p_progress => l_progress);
1570 END IF;
1571 END validate_item_revision;
1572
1573 --------------------------------------------------------------------------------
1574 --Start of Comments
1575 --Name: validate_item_in_org
1576 --Pre-reqs:
1577 -- None.
1578 --Modifies:
1579 -- FND_LOG
1580 -- FND_MSG_PUB
1581 --Locks:
1582 -- None.
1583 --Function:
1584 -- Performs item and item revision validity checks between p_org_id and
1585 -- p_owning_org_id. Appends to the API message list upon error.
1586 --Parameters:
1587 --IN:
1588 --p_item_id
1589 -- The item ID on the GA line
1590 --p_org_id
1591 -- The org ID of the OU to compare against p_owning_org_id
1592 --p_ga_org_type
1593 -- The type of OU that p_org_id is for the GA. Acceptable values are the
1594 -- global variables g_requesting_org_type or g_purchasing_org_type. Calls
1595 -- APP_EXCEPTION.invalid_argument if the parameter is not one of these values.
1596 --p_ga_item_revision
1597 -- The item revision on the GA line. (MTL_ITEM_REVISIONS_B.revision%TYPE)
1598 --p_owning_org_id
1599 -- The GA Owning Org ID
1600 --OUT:
1601 --x_return_status
1602 -- FND_API.g_ret_sts_success - if the procedure completed successfully
1603 -- FND_API.g_ret_sts_error - if an error occurred
1604 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
1605 --x_is_valid
1606 -- TRUE if:
1607 -- - item is a one-time item
1608 -- OR
1609 -- - item master item defined in Financial Options Inventory Organization of
1610 -- p_org_id as 'purchasable'
1611 -- - item in p_org_id must share same 'UOM' as p_owning_org_id
1612 -- - item not defined as 'Outside Processing' in p_org_id
1613 -- - p_ga_item_revision in p_owning_org_id is available in p_org_id. If
1614 -- - it is not available AND
1615 -- - p_ga_org_type is g_requesting_org_type AND
1616 -- - revision control is off in p_org_id
1617 -- then the revision check passes and x_item_revision will be NULL.
1618 --
1619 -- FALSE otherwise.
1620 --x_item_revision
1621 -- The valid item revision for the item in p_org_id.
1622 -- (MTL_ITEM_REVISIONS_B.revision%TYPE)
1623 --End of Comments
1624 --------------------------------------------------------------------------------
1625 PROCEDURE validate_item_in_org
1626 (
1627 x_return_status OUT NOCOPY VARCHAR2,
1628 p_item_id IN NUMBER,
1629 p_org_id IN NUMBER,
1630 p_ga_org_type IN VARCHAR2,
1631 p_ga_item_revision IN VARCHAR2,
1632 p_owning_org_id IN NUMBER,
1633 x_is_valid OUT NOCOPY BOOLEAN,
1634 x_item_revision OUT NOCOPY VARCHAR2
1635 )
1636 IS
1637
1638 l_check_rev_control BOOLEAN;
1639 l_is_purchasable BOOLEAN;
1640 l_is_same_uom_class BOOLEAN;
1641 l_is_not_osp_item BOOLEAN;
1642 l_progress VARCHAR2(3);
1643
1644 BEGIN
1645 l_progress := '000';
1646 x_return_status := FND_API.g_ret_sts_success;
1647
1648 IF g_debug_stmt THEN
1649 PO_DEBUG.debug_stmt
1650 (p_log_head => g_module_prefix||'validate_item_in_org',
1651 p_token => 'invoked',
1652 p_message => 'item ID: '||p_item_id||' org ID: '||p_org_id||
1653 ' orgtype: '||p_ga_org_type||' ga item rev: '||
1654 p_ga_item_revision||' ownorg ID: '||p_owning_org_id);
1655 END IF;
1656
1657 -- Only do revision check if GA revision is not NULL
1658 IF (p_ga_item_revision IS NOT NULL) THEN
1659
1660 IF (p_ga_org_type = g_requesting_org_type) THEN
1661 -- Pass in TRUE because Requesting Org may use revision control
1662 l_check_rev_control := TRUE;
1663 ELSIF (p_ga_org_type = g_purchasing_org_type) THEN
1664 -- Pass in FALSE because Purchasing Org must always match exactly
1665 l_check_rev_control := FALSE;
1666 ELSE
1667 l_progress := '010';
1668
1669 -- Invalid parameter, so raise error here
1670 APP_EXCEPTION.invalid_argument
1671 (procname => 'PO_GA_PVT.validate_item_in_org',
1672 argument => 'p_ga_org_type',
1673 value => p_ga_org_type);
1674 END IF;
1675
1676 l_progress := '020';
1677
1678 validate_item_revision(x_return_status => x_return_status,
1679 p_item_id => p_item_id,
1680 p_org_id => p_org_id,
1681 p_ga_item_revision => p_ga_item_revision,
1682 p_owning_org_id => p_owning_org_id,
1683 p_check_rev_control => l_check_rev_control,
1684 x_is_valid => x_is_valid,
1685 x_item_revision => x_item_revision);
1686
1687 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1688 RAISE FND_API.g_exc_error;
1689 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1690 RAISE FND_API.g_exc_unexpected_error;
1691 END IF;
1692
1693 IF (NOT x_is_valid) THEN
1694 -- revision check failed, so validation fails. No need to continue
1695 RETURN;
1696 END IF;
1697
1698 END IF; --<if ga item revision not null>
1699
1700 l_progress := '030';
1701
1702 -- Check item validity
1703 validate_item(x_return_status => x_return_status,
1704 p_item_id => p_item_id,
1705 p_org_id => p_org_id,
1706 p_valid_org_id => p_owning_org_id,
1707 x_is_purchasable => l_is_purchasable,
1708 x_is_same_uom_class => l_is_same_uom_class,
1709 x_is_not_osp_item => l_is_not_osp_item);
1710
1711 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1712 RAISE FND_API.g_exc_error;
1713 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1714 RAISE FND_API.g_exc_unexpected_error;
1715 END IF;
1716
1717 x_is_valid := (l_is_purchasable AND l_is_same_uom_class AND
1718 l_is_not_osp_item);
1719
1720 EXCEPTION
1721 WHEN APP_EXCEPTION.application_exception THEN
1722 x_return_status := FND_API.g_ret_sts_error;
1723 FND_MSG_PUB.add;
1724 IF g_debug_stmt THEN
1725 PO_DEBUG.debug_stmt
1726 (p_log_head => g_module_prefix||'validate_item_in_org',
1727 p_token => l_progress,
1728 p_message => 'APPLICATION_EXCEPTION caught.');
1729 END IF;
1730 WHEN FND_API.g_exc_error THEN
1731 x_return_status := FND_API.g_ret_sts_error;
1732 WHEN FND_API.g_exc_unexpected_error THEN
1733 x_return_status := FND_API.g_ret_sts_unexp_error;
1734 WHEN OTHERS THEN
1735 x_return_status := FND_API.g_ret_sts_unexp_error;
1736 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1737 p_procedure_name => 'validate_item_in_org',
1738 p_error_text => 'Progress: '||l_progress||
1739 ' Error: '||SUBSTRB(SQLERRM,1,215));
1740 IF g_debug_unexp THEN
1741 PO_DEBUG.debug_exc
1742 (p_log_head => g_module_prefix || 'validate_item_in_org',
1743 p_progress => l_progress);
1744 END IF;
1745 END validate_item_in_org;
1746
1747 --------------------------------------------------------------------------------
1748 --Start of Comments
1749 --Name: val_enabled_purchasing_org
1750 --Pre-reqs:
1751 -- None.
1752 --Modifies:
1753 -- FND_LOG
1754 -- FND_MSG_PUB
1755 --Locks:
1756 -- None.
1757 --Function:
1758 -- Validate that x_purchasing_org_id is a Purchasing Org for the GA, with an
1759 -- enabled status. If x_purchasing_org_id is NULL, the current OU will be
1760 -- used as the Purchasing Org. Appends to the API message list upon error.
1761 --Parameters:
1762 --IN:
1763 --p_po_header_id
1764 -- The po_header_id of the GA.
1765 --IN OUT:
1766 --x_purchasing_org_id
1767 -- The Purchasing Org ID, or NULL to use the current OU. If NULL, this will be
1768 -- set to be the current OU if x_is_valid is TRUE.
1769 --OUT:
1770 --x_return_status
1771 -- FND_API.g_ret_sts_success - if the procedure completed successfully
1772 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
1773 --x_is_valid
1774 -- TRUE if x_purchasing_org_id is valid and enabled for use for the GA.
1775 -- FALSE otherwise.
1776 --End of Comments
1777 --------------------------------------------------------------------------------
1778 PROCEDURE val_enabled_purchasing_org
1779 (
1780 x_return_status OUT NOCOPY VARCHAR2,
1781 p_po_header_id IN NUMBER,
1782 x_purchasing_org_id IN OUT NOCOPY NUMBER,
1783 x_is_valid OUT NOCOPY BOOLEAN
1784 )
1785 IS
1786
1787 l_valid_flag VARCHAR2(1);
1788 l_progress VARCHAR2(3);
1789
1790 BEGIN
1791 l_progress := '000';
1792 x_return_status := FND_API.g_ret_sts_success;
1793
1794 IF g_debug_stmt THEN
1795 PO_DEBUG.debug_stmt
1796 (p_log_head => g_module_prefix||'val_enabled_purchasing_org',
1797 p_token => 'invoked',
1798 p_message => 'po_header_id: '||p_po_header_id||
1799 ' purchorg ID: '||x_purchasing_org_id);
1800 END IF;
1801
1802 IF (x_purchasing_org_id IS NULL) THEN
1803 l_progress := '010';
1804
1805 SELECT pgoa.purchasing_org_id
1806 INTO x_purchasing_org_id
1807 FROM po_ga_org_assignments pgoa,
1808 po_system_parameters psp
1809 WHERE pgoa.po_header_id = p_po_header_id
1810 AND pgoa.purchasing_org_id = psp.org_id
1811 AND pgoa.enabled_flag = 'Y'
1812 AND rownum = 1;
1813
1814 ELSE
1815 l_progress := '020';
1816
1817 SELECT 'Y'
1818 INTO l_valid_flag
1819 FROM po_ga_org_assignments
1820 WHERE po_header_id = p_po_header_id
1821 AND purchasing_org_id = x_purchasing_org_id
1822 AND enabled_flag = 'Y'
1823 AND rownum = 1;
1824
1825 END IF;
1826
1827 l_progress := '030';
1828
1829 -- Successful select means that it is a valid enabled purchasing org
1830 x_is_valid := TRUE;
1831
1832 IF g_debug_stmt THEN
1833 PO_DEBUG.debug_var
1834 (p_log_head => g_module_prefix||'val_enabled_purchasing_org',
1835 p_progress => l_progress,
1836 p_name => 'x_is_valid',
1837 p_value => x_is_valid);
1838 END IF;
1839
1840 EXCEPTION
1841 WHEN NO_DATA_FOUND THEN
1842 x_is_valid := FALSE;
1843 IF g_debug_stmt THEN
1844 PO_DEBUG.debug_var
1845 (p_log_head => g_module_prefix||'val_enabled_purchasing_org',
1846 p_progress => l_progress,
1847 p_name => 'x_is_valid',
1848 p_value => x_is_valid);
1849 END IF;
1850 WHEN OTHERS THEN
1851 x_return_status := FND_API.g_ret_sts_unexp_error;
1852 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1853 p_procedure_name => 'val_enabled_purchasing_org',
1854 p_error_text => 'Progress: '||l_progress||
1855 ' Error: '||SUBSTRB(SQLERRM,1,215));
1856 IF g_debug_unexp THEN
1857 PO_DEBUG.debug_exc
1858 (p_log_head => g_module_prefix||'val_enabled_purchasing_org',
1859 p_progress => l_progress);
1860 END IF;
1861 END val_enabled_purchasing_org;
1862
1863 --------------------------------------------------------------------------------
1864 --Start of Comments
1865 --Name: val_enabled_requesting_org
1866 --Pre-reqs:
1867 -- None.
1868 --Modifies:
1869 -- FND_LOG
1870 -- FND_MSG_PUB
1871 --Locks:
1872 -- None.
1873 --Function:
1874 -- Validate that x_requesting_org_id is a Requesting Org for the GA, with an
1875 -- enabled status. If x_requesting_org_id is NULL, the current OU is used as
1876 -- the Requesting Org. Appends to the API message list upon error.
1877 --Parameters:
1878 --IN:
1879 --p_po_header_id
1880 -- The po_header_id of the GA.
1881 --IN OUT:
1882 --x_requesting_org_id
1883 -- The Requesting Org ID, or NULL to use the current OU. If NULL, this will be
1884 -- set to be the current OU if x_is_valid is TRUE.
1885 --OUT:
1886 --x_return_status
1887 -- FND_API.g_ret_sts_success - if the procedure completed successfully
1888 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
1889 --x_is_valid
1890 -- TRUE if p_requesting_org_id is valid and enabled for use for the GA.
1891 -- FALSE otherwise.
1892 --x_purchasing_org_id
1893 -- The Purchasing Org ID for the org assignment of the Requesting Org. Only
1894 -- has a valid value if x_is_valid is TRUE.
1895 -- (PO_GA_ORG_ASSIGNMENTS.purchasing_org_id%TYPE)
1896 --End of Comments
1897 --------------------------------------------------------------------------------
1898 PROCEDURE val_enabled_requesting_org
1899 (
1900 x_return_status OUT NOCOPY VARCHAR2,
1901 p_po_header_id IN NUMBER,
1902 x_requesting_org_id IN OUT NOCOPY NUMBER,
1903 x_is_valid OUT NOCOPY BOOLEAN,
1904 x_purchasing_org_id OUT NOCOPY NUMBER
1905 )
1906 IS
1907
1908 l_progress VARCHAR2(3);
1909
1910 BEGIN
1911 l_progress := '000';
1912 x_return_status := FND_API.g_ret_sts_success;
1913
1914 IF g_debug_stmt THEN
1915 PO_DEBUG.debug_stmt
1916 (p_log_head => g_module_prefix||'val_enabled_requesting_org',
1917 p_token => 'invoked',
1918 p_message => 'po_header_id: '||p_po_header_id||
1919 ' reqorg ID: '||x_requesting_org_id);
1920 END IF;
1921
1922 IF (x_requesting_org_id IS NULL) THEN
1923 l_progress := '010';
1924
1925 SELECT pgoa.purchasing_org_id,
1926 pgoa.organization_id
1927 INTO x_purchasing_org_id,
1928 x_requesting_org_id
1929 FROM po_ga_org_assignments pgoa,
1930 po_system_parameters psp
1931 WHERE pgoa.po_header_id = p_po_header_id
1932 AND pgoa.organization_id = psp.org_id
1933 AND pgoa.enabled_flag = 'Y';
1934
1935 ELSE
1936 l_progress := '020';
1937
1938 SELECT purchasing_org_id
1939 INTO x_purchasing_org_id
1940 FROM po_ga_org_assignments
1941 WHERE po_header_id = p_po_header_id
1942 AND organization_id = x_requesting_org_id
1943 AND enabled_flag = 'Y';
1944
1945 END IF;
1946
1947 l_progress := '030';
1948
1949 -- Successful select means that it is a valid enabled requesting org
1950 x_is_valid := TRUE;
1951
1952 IF g_debug_stmt THEN
1953 PO_DEBUG.debug_var
1954 (p_log_head => g_module_prefix||'val_enabled_requesting_org',
1955 p_progress => l_progress,
1956 p_name => 'x_is_valid',
1957 p_value => x_is_valid);
1958 END IF;
1959
1960 EXCEPTION
1961 WHEN NO_DATA_FOUND THEN
1962 x_purchasing_org_id := NULL;
1963 x_is_valid := FALSE;
1964 IF g_debug_stmt THEN
1965 PO_DEBUG.debug_var
1966 (p_log_head => g_module_prefix||'val_enabled_requesting_org',
1967 p_progress => l_progress,
1968 p_name => 'x_is_valid',
1969 p_value => x_is_valid);
1970 END IF;
1971 WHEN OTHERS THEN
1972 x_return_status := FND_API.g_ret_sts_unexp_error;
1973 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1974 p_procedure_name => 'val_enabled_requesting_org',
1975 p_error_text => 'Progress: '||l_progress||
1976 ' Error: '||SUBSTRB(SQLERRM,1,215));
1977 IF g_debug_unexp THEN
1978 PO_DEBUG.debug_exc
1979 (p_log_head => g_module_prefix||'val_enabled_requesting_org',
1980 p_progress => l_progress);
1981 END IF;
1982 END val_enabled_requesting_org;
1983
1984 ---------------------------------------------------------------------------
1985 --Start of Comments
1986 --Name: validate_in_purchasing_org
1987 --Pre-reqs:
1988 -- None.
1989 --Modifies:
1990 -- FND_LOG
1991 -- FND_MSG_PUB
1992 --Locks:
1993 -- None.
1994 --Function:
1995 -- Validates the item in the Purchasing Org p_purchasing_org_id against the
1996 -- Owning Org. If p_purchasing_org_id is NULL, then the current OU is used
1997 -- as the Purchasing Org. Appends to the API message list upon error.
1998 --Parameters:
1999 --IN:
2000 --p_po_header_id
2001 -- The header ID of the GA
2002 --p_item_id
2003 -- The item ID on the GA line
2004 --p_purchasing_org_id
2005 -- The org ID of the Purchasing Org, or NULL to use the current OU.
2006 --p_ga_item_revision
2007 -- The item revision on the GA line. (MTL_ITEM_REVISIONS_B.revision%TYPE)
2008 --p_owning_org_id
2009 -- The GA Owning Org ID
2010 --OUT:
2011 --x_return_status
2012 -- FND_API.g_ret_sts_success - if the procedure completed successfully
2013 -- FND_API.g_ret_sts_error - if an error occurred
2014 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
2015 --x_is_pou_valid
2016 -- TRUE if p_purchasing_org_id is a valid Purchasing Org for the GA.
2017 -- FALSE otherwise.
2018 --x_is_item_valid:
2019 -- TRUE if x_is_pou_valid is TRUE, and all item validations succeed for the
2020 -- Purchasing Org.
2021 -- FALSE otherwise.
2022 --x_item_revision
2023 -- The valid item revision for the item in the Purchasing Org
2024 -- p_purchasing_org_id. (MTL_ITEM_REVISIONS_B.revision%TYPE)
2025 --End of Comments
2026 ---------------------------------------------------------------------------
2027 PROCEDURE validate_in_purchasing_org
2028 (
2029 x_return_status OUT NOCOPY VARCHAR2,
2030 p_po_header_id IN NUMBER,
2031 p_item_id IN NUMBER,
2032 p_purchasing_org_id IN NUMBER,
2033 p_ga_item_revision IN VARCHAR2,
2034 p_owning_org_id IN NUMBER,
2035 x_is_pou_valid OUT NOCOPY BOOLEAN,
2036 x_is_item_valid OUT NOCOPY BOOLEAN,
2037 x_item_revision OUT NOCOPY VARCHAR2
2038 )
2039 IS
2040
2041 l_purchasing_org_id
2042 PO_GA_ORG_ASSIGNMENTS.purchasing_org_id%TYPE := p_purchasing_org_id;
2043 l_item_revision MTL_ITEM_REVISIONS_B.revision%TYPE;
2044 l_progress VARCHAR2(3);
2045
2046 BEGIN
2047 l_progress := '000';
2048 x_return_status := FND_API.g_ret_sts_success;
2049
2050 IF g_debug_stmt THEN
2051 PO_DEBUG.debug_stmt
2052 (p_log_head => g_module_prefix||'validate_in_purchasing_org',
2053 p_token => 'invoked',
2054 p_message => 'po_header_id: '||p_po_header_id||' item ID: '||
2055 p_item_id||' purchorg ID: '||p_purchasing_org_id||
2056 ' ownorg ID: '||p_owning_org_id);
2057 END IF;
2058
2059 l_progress := '010';
2060
2061 -- First ensure that p_purchasing_org_id is a valid Purchasing Org
2062 val_enabled_purchasing_org(x_return_status => x_return_status,
2063 p_po_header_id => p_po_header_id,
2064 x_purchasing_org_id => l_purchasing_org_id,
2065 x_is_valid => x_is_pou_valid);
2066
2067 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2068 RAISE FND_API.g_exc_error;
2069 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2070 RAISE FND_API.g_exc_unexpected_error;
2071 END IF;
2072
2073 IF (NOT x_is_pou_valid) THEN
2074 -- p_purchasing_org_id is not a valid Purchasing Org, so return
2075 x_is_item_valid := FALSE;
2076 RETURN;
2077 END IF;
2078
2079 l_progress := '020';
2080
2081 -- p_purchasing_org_id is a valid Purchasing Org. Validate item against
2082 -- the Purchasing Org
2083 validate_item_in_org(x_return_status => x_return_status,
2084 p_item_id => p_item_id,
2085 p_org_id => l_purchasing_org_id,
2086 p_ga_org_type => g_purchasing_org_type,
2087 p_ga_item_revision => p_ga_item_revision,
2088 p_owning_org_id => p_owning_org_id,
2089 x_is_valid => x_is_item_valid,
2090 x_item_revision => x_item_revision);
2091
2092 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2093 RAISE FND_API.g_exc_error;
2094 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2095 RAISE FND_API.g_exc_unexpected_error;
2096 END IF;
2097
2098 IF g_debug_stmt THEN
2099 PO_DEBUG.debug_var
2100 (p_log_head => g_module_prefix||'validate_in_purchasing_org',
2101 p_progress => l_progress,
2102 p_name => 'x_is_item_valid',
2103 p_value => x_is_item_valid);
2104 END IF;
2105
2106 EXCEPTION
2107 WHEN FND_API.g_exc_error THEN
2108 x_return_status := FND_API.g_ret_sts_error;
2109 WHEN FND_API.g_exc_unexpected_error THEN
2110 x_return_status := FND_API.g_ret_sts_unexp_error;
2111 WHEN OTHERS THEN
2112 x_return_status := FND_API.g_ret_sts_unexp_error;
2113 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
2114 p_procedure_name => 'validate_in_purchasing_org',
2115 p_error_text => 'Progress: '||l_progress||
2116 ' Error: '||SUBSTRB(SQLERRM,1,215));
2117 IF g_debug_unexp THEN
2118 PO_DEBUG.debug_exc
2119 (p_log_head => g_module_prefix||'validate_in_purchasing_org',
2120 p_progress => l_progress);
2121 END IF;
2122 END validate_in_purchasing_org;
2123
2124 ---------------------------------------------------------------------------
2125 --Start of Comments
2126 --Name: validate_in_requesting_org
2127 --Pre-reqs:
2128 -- None.
2129 --Modifies:
2130 -- FND_LOG
2131 -- FND_MSG_PUB
2132 --Locks:
2133 -- None.
2134 --Function:
2135 -- Validates the item in the Requesting Org p_requesting_org_id against the
2136 -- Owning Org. Also performs item validations with the Purchasing Org of this
2137 -- org assignment against the Owning Org. If p_requesting_org_id is NULL,
2138 -- then the current OU is used as the Requesting Org. Appends to the API
2139 -- message list upon error.
2140 --Parameters:
2141 --IN:
2142 --p_po_header_id
2143 -- The header ID of the GA
2144 --p_item_id
2145 -- The item ID on the GA line
2146 --p_requesting_org_id
2147 -- The org ID of the Requesting Org, or NULL to use the current OU.
2148 --p_ga_item_revision
2149 -- The item revision on the GA line. (MTL_ITEM_REVISIONS_B.revision%TYPE)
2150 --p_owning_org_id
2151 -- The GA Owning Org ID
2152 --OUT:
2153 --x_return_status
2154 -- FND_API.g_ret_sts_success - if the procedure completed successfully
2155 -- FND_API.g_ret_sts_error - if an error occurred
2156 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
2157 --x_is_rou_valid
2158 -- TRUE if p_requesting_org_id is a valid Requesting Org for the GA.
2159 -- FALSE otherwise.
2160 --x_is_item_valid:
2161 -- TRUE if x_is_rou_valid is TRUE, and all item validations succeed for the
2162 -- Requesting Org.
2163 -- FALSE otherwise.
2164 --x_item_revision
2165 -- The valid item revision for the item in the Requesting Org
2166 -- p_requesting_org_id. (MTL_ITEM_REVISIONS_B.revision%TYPE)
2167 --End of Comments
2168 ---------------------------------------------------------------------------
2169 PROCEDURE validate_in_requesting_org
2170 (
2171 x_return_status OUT NOCOPY VARCHAR2,
2172 p_po_header_id IN NUMBER,
2173 p_item_id IN NUMBER,
2174 p_requesting_org_id IN NUMBER,
2175 p_ga_item_revision IN VARCHAR2,
2176 p_owning_org_id IN NUMBER,
2177 x_is_rou_valid OUT NOCOPY BOOLEAN,
2178 x_is_item_valid OUT NOCOPY BOOLEAN,
2179 x_item_revision OUT NOCOPY VARCHAR2
2180 )
2181 IS
2182
2183 l_requesting_org_id
2184 PO_GA_ORG_ASSIGNMENTS.organization_id%TYPE := p_requesting_org_id;
2185 l_purchasing_org_id PO_GA_ORG_ASSIGNMENTS.purchasing_org_id%TYPE;
2186 l_dummy_revision MTL_ITEM_REVISIONS_B.revision%TYPE;
2187 l_progress VARCHAR2(3);
2188
2189 BEGIN
2190 l_progress := '000';
2191 x_return_status := FND_API.g_ret_sts_success;
2192
2193 IF g_debug_stmt THEN
2194 PO_DEBUG.debug_stmt
2195 (p_log_head => g_module_prefix||'validate_in_requesting_org',
2196 p_token => 'invoked',
2197 p_message => 'po_header_id: '||p_po_header_id||' item ID: '||
2198 p_item_id||' reqorg ID: '||p_requesting_org_id||
2199 ' ownorg ID: '||p_owning_org_id);
2200 END IF;
2201
2202 l_progress := '010';
2203
2204 -- First ensure that p_requesting_org_id is a valid Requesting Org
2205 val_enabled_requesting_org(x_return_status => x_return_status,
2206 p_po_header_id => p_po_header_id,
2207 x_requesting_org_id => l_requesting_org_id,
2208 x_is_valid => x_is_rou_valid,
2209 x_purchasing_org_id => l_purchasing_org_id);
2210
2211 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2212 RAISE FND_API.g_exc_error;
2213 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2214 RAISE FND_API.g_exc_unexpected_error;
2215 END IF;
2216
2217 IF (NOT x_is_rou_valid) THEN
2218 -- p_requesting_org_id is not a valid Requesting Org, so return
2219 x_is_item_valid := FALSE;
2220 RETURN;
2221 END IF;
2222
2223 l_progress := '020';
2224
2225 -- p_requesting_org_id is a valid Requesting Org. Validate item against the
2226 -- Requesting Org
2227 validate_item_in_org(x_return_status => x_return_status,
2228 p_item_id => p_item_id,
2229 p_org_id => l_requesting_org_id,
2230 p_ga_org_type => g_requesting_org_type,
2231 p_ga_item_revision => p_ga_item_revision,
2232 p_owning_org_id => p_owning_org_id,
2233 x_is_valid => x_is_item_valid,
2234 x_item_revision => x_item_revision);
2235
2236 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2237 RAISE FND_API.g_exc_error;
2238 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2239 RAISE FND_API.g_exc_unexpected_error;
2240 END IF;
2241
2242 IF (NOT x_is_item_valid) THEN
2243 -- This Requesting Org failed item validity, so return here
2244 RETURN;
2245 END IF;
2246
2247 l_progress := '030';
2248
2249 -- Now validate item against the Purchasing Org for this Requesting Org
2250 validate_item_in_org(x_return_status => x_return_status,
2251 p_item_id => p_item_id,
2252 p_org_id => l_purchasing_org_id,
2253 p_ga_org_type => g_purchasing_org_type,
2254 p_ga_item_revision => p_ga_item_revision,
2255 p_owning_org_id => p_owning_org_id,
2256 x_is_valid => x_is_item_valid,
2257 x_item_revision => l_dummy_revision);
2258
2259 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2260 RAISE FND_API.g_exc_error;
2261 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2262 RAISE FND_API.g_exc_unexpected_error;
2263 END IF;
2264
2265 IF g_debug_stmt THEN
2266 PO_DEBUG.debug_var
2267 (p_log_head => g_module_prefix||'validate_in_requesting_org',
2268 p_progress => l_progress,
2269 p_name => 'x_is_item_valid',
2270 p_value => x_is_item_valid);
2271 END IF;
2272
2273 EXCEPTION
2274 WHEN FND_API.g_exc_error THEN
2275 x_return_status := FND_API.g_ret_sts_error;
2276 WHEN FND_API.g_exc_unexpected_error THEN
2277 x_return_status := FND_API.g_ret_sts_unexp_error;
2278 WHEN OTHERS THEN
2279 x_return_status := FND_API.g_ret_sts_unexp_error;
2280 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
2281 p_procedure_name => 'validate_in_requesting_org',
2282 p_error_text => 'Progress: '||l_progress||
2283 ' Error: '||SUBSTRB(SQLERRM,1,215));
2284 IF g_debug_unexp THEN
2285 PO_DEBUG.debug_exc
2286 (p_log_head => g_module_prefix||'validate_in_requesting_org',
2287 p_progress => l_progress);
2288 END IF;
2289 END validate_in_requesting_org;
2290
2291 ---------------------------------------------------------------------------
2292 --Start of Comments
2293 --Name: requesting_org_type
2294 --Pre-reqs:
2295 -- None.
2296 --Modifies:
2297 -- None.
2298 --Locks:
2299 -- None.
2300 --Function:
2301 -- Returns the global variable g_requesting_org_type
2302 --Returns:
2303 -- g_requesting_org_type
2304 --End of Comments
2305 ---------------------------------------------------------------------------
2306 FUNCTION requesting_org_type RETURN VARCHAR2
2307 IS
2308 BEGIN
2309 RETURN g_requesting_org_type;
2310 END requesting_org_type;
2311
2312 ---------------------------------------------------------------------------
2313 --Start of Comments
2314 --Name: purchasing_org_type
2315 --Pre-reqs:
2316 -- None.
2317 --Modifies:
2318 -- None.
2319 --Locks:
2320 -- None.
2321 --Function:
2322 -- Returns the global variable g_purchasing_org_type
2323 --Returns:
2324 -- g_purchasing_org_type
2325 --End of Comments
2326 ---------------------------------------------------------------------------
2327 FUNCTION purchasing_org_type RETURN VARCHAR2
2328 IS
2329 BEGIN
2330 RETURN g_purchasing_org_type;
2331 END purchasing_org_type;
2332
2333 ---------------------------------------------------------------------------
2334 --Start of Comments
2335 --Name: owning_org_type
2336 --Pre-reqs:
2337 -- None.
2338 --Modifies:
2339 -- None.
2340 --Locks:
2341 -- None.
2342 --Function:
2343 -- Returns the global variable g_owning_org_type
2344 --Returns:
2345 -- g_owning_org_type
2346 --End of Comments
2347 ---------------------------------------------------------------------------
2348 FUNCTION owning_org_type RETURN VARCHAR2
2349 IS
2350 BEGIN
2351 RETURN g_owning_org_type;
2352 END owning_org_type;
2353
2354
2355 --<Shared Proc FPJ END>
2356
2357 --Bug 12838070
2358 PROCEDURE sync_all_ga_line_attachments(
2359 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE,
2360 p_draft_id IN PO_LINES_DRAFT_ALL.draft_id%TYPE, --Bug 13540474
2361 x_return_status OUT NOCOPY VARCHAR2,
2362 x_msg_data OUT NOCOPY VARCHAR2) IS
2363
2364 l_count_po_line_att NUMBER := 0;
2365 l_count_ga_line_att NUMBER := 0;
2366
2367 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_ALL_GA_LINE_ATTACHMENTS';
2368 l_api_return_status VARCHAR2(1);
2369 l_api_msg_data VARCHAR2(2000);
2370
2371 cursor lines_csr is
2372 select from_line_id, decode(from_header_id, NULL,contract_id, from_header_id ) from po_lines_merge_v where po_header_id = p_po_header_id and draft_id = p_draft_id; --Bug 13540474
2373
2374 l_po_line_id PO_LINES_ALL.po_line_id%type;
2375 l_src_doc_id PO_HEADERS_ALL.po_header_id%TYPE;
2376
2377
2378 BEGIN
2379
2380 open lines_csr;
2381
2382 loop
2383 fetch lines_csr into l_po_line_id,l_src_doc_id;
2384 exit when lines_csr%NOTFOUND;
2385
2386 IF is_global_agreement(l_src_doc_id) THEN
2387
2388
2389
2390
2391 --<Bug 2887275 mbhargav>
2392 --Changed the call to delete_attachments. Explicitely passing
2393 --'Y' for x_automatically_added_flag as we want to delete only
2394 --those attachment references that have been added automatically
2395 --in procedure reference_attachments
2396 --first delete all references on PO_IN_GA_LINES
2397 fnd_attached_documents2_pkg.delete_attachments('PO_IN_GA_LINES',
2398 l_po_line_id,
2399 '', '', '', '', 'N', 'Y');
2400
2401 --Copy reference from entity 'PO_LINES' to enity 'PO_IN_GA_LINES'
2402 reference_attachments(
2403 p_api_version => 1.0,
2404 p_from_entity_name => 'PO_LINES',
2405 p_from_pk1_value => l_po_line_id,
2406 p_from_pk2_value => '',
2407 p_from_pk3_value => '',
2408 p_from_pk4_value => '',
2409 p_from_pk5_value => '',
2410 p_to_entity_name => 'PO_IN_GA_LINES',
2411 p_to_pk1_value => l_po_line_id,
2412 p_to_pk2_value => '',
2413 p_to_pk3_value => '',
2414 p_to_pk4_value => '',
2415 p_to_pk5_value => '',
2416 p_automatically_added_flag => 'Y',
2417 x_return_status => l_api_return_status,
2418 x_msg_data => l_api_msg_data);
2419
2420 IF l_api_return_status = FND_API.G_RET_STS_ERROR THEN
2421 x_msg_data := l_api_msg_data;
2422 RAISE FND_API.G_EXC_ERROR;
2423 ELSIF l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2424 x_msg_data := l_api_msg_data;
2425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2426 END IF;
2427
2428 END IF; --references a GA
2429
2430 end loop;
2431 close lines_csr;
2432
2433
2434
2435 x_return_status := FND_API.G_RET_STS_SUCCESS;
2436
2437 EXCEPTION
2438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2440 WHEN FND_API.G_EXC_ERROR THEN
2441 x_return_status := FND_API.G_RET_STS_ERROR;
2442 WHEN OTHERS THEN
2443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2444 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
2445 END IF;
2446
2447 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
2448 p_encoded => 'F');
2449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2450
2451 END sync_all_ga_line_attachments;
2452
2453 END PO_GA_PVT;
[Home] [Help]