1 PACKAGE BODY PO_VMI_ENABLED AS
2 /* $Header: POXPVIEB.pls 115.4 2004/05/27 23:54:07 jmcfadde ship $ */
3
4 -- Global constant holding package name
5 g_pkg_name constant varchar2(50) := 'PO_VMI_ENABLED';
6
7 /*
8 ** -------------------------------------------------------------------------
9 ** Function: check_vmi_enabled
10 ** Description: This function is called from Inventory OrganizationParameters
11 ** form(INVSDOIO.fmb). When a value of true is returned by the API, the form
12 ** disallows enabling of wms for that organization.
13 ** Output:
14 ** x_return_status
15 ** return status indicating success, error, unexpected error
16 ** x_msg_count
17 ** number of messages in message list
18 ** x_msg_data
19 ** if the number of messages in message list is 1, contains
20 ** message text
21 ** Input:
22 ** p_organization_id
23 ** -specific inventory organization to be checked if VMI enabled.
24 **
25 ** Returns:
26 ** TRUE if VMI installed, else FALSE
27 **
28 ** Please use return value to determine if VMI is installed or not.
29 ** Do not use x_return_status for this purpose as
30 ** . x_return_status could be success and yet VMI not be installed.
31 ** . x_return_status is set to error when an error(such as SQL error)
32 ** occurs.
33 ** --------------------------------------------------------------------------
34 */
35
36 function check_vmi_enabled(
37 x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ,p_organization_id IN NUMBER )
41 RETURN BOOLEAN IS
42
43 --constant
44 c_api_name constant varchar(30) := 'CHECK_VMI_ENABLED';
45 v_temp varchar2(1) :=NULL;
46 l_sob_id org_organization_definitions.set_of_books_id%TYPE; --bug 3648672
47
48
49 BEGIN
50 x_return_status := fnd_api.g_ret_sts_success ;
51
52
53 BEGIN
54 SELECT 'Y' into v_temp from dual
55 WHERE exists
56 (SELECT 1 from
57 po_asl_attributes paa,
58 po_approved_supplier_list pasl,
59 po_asl_status_rules pasr --bug 3648705 join to table not view
60 WHERE
61 paa.using_organization_id = p_organization_id
62 AND pasl.asl_id = paa.asl_id
63 AND (pasl.disable_flag = 'N' OR pasl.disable_flag IS NULL)
64 and pasr.status_id = pasl.asl_status_id
65 AND pasr.business_rule like '2_SOURCING'
66 AND pasr.allow_action_flag like 'Y'
67 AND paa.enable_vmi_flag = 'Y');
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 v_temp := NULL;
71 END;
72
73 if(v_temp ='Y') then
74 return TRUE;
75 ELSE
76
77 BEGIN
78
79 -- move this query out of below query for performance
80 -- bug 3648672
81 SELECT set_of_books_id
82 INTO l_sob_id
83 FROM org_organization_definitions OOD
84 WHERE OOD.organization_id = p_organization_id;
85
86 select 'Y' INTO v_temp from dual where exists
87 (
88 select paa.asl_id
89 from
90 po_vendors pv,
91 po_vendor_sites_all pvsa,
92 po_asl_attributes paa,
93 po_approved_supplier_list pasl,
94 po_asl_status_rules pasr --bug 3648672, join to base table instead of view
95 where
96 --Getting all organizations associated with the particular set_of_books_id
97 pv.set_of_books_id = l_sob_id -- bug 3648672
98
99 --Getting the vendor_id,vendor_site_id
100 AND pvsa.vendor_id = pv.vendor_id
101
102
103 --Getting to the po_approved_supplier_list using vendor_id and vendor_site_id
104 AND pvsa.vendor_id = pasl.vendor_id
105 AND pvsa.vendor_site_id = pasl.vendor_site_id
106 AND (pasl.disable_flag = 'N' OR pasl.disable_flag IS NULL)
107 and paa.asl_id = pasl.asl_id
108
109 --getting the global_asl_id from paa and verifying its validity
110 and paa.using_organization_id = -1
111 and pasr.status_id = pasl.asl_status_id
112 AND pasr.business_rule like '2_SOURCING'
113 AND pasr.allow_action_flag like 'Y'
114 AND paa.enable_vmi_flag = 'Y'
115
116 );
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 v_temp := NULL;
120 END;
121 end if;
122
123 if(v_temp ='Y') then
124 return TRUE;
125 ELSE
126 RETURN FALSE;
127 END IF;
128
129 EXCEPTION
130 when others then
131 x_return_status := fnd_api.g_ret_sts_unexp_error;
132
133 if (fnd_msg_pub.check_msg_level
134 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
135 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
136 end if;
137 RETURN TRUE;
138 end check_vmi_enabled;
139 end PO_VMI_ENABLED;
[Home] [Help]