[Home] [Help]
PACKAGE BODY: APPS.BOM_EAMUTIL
Source
1 PACKAGE BODY BOM_EAMUTIL AS
2 /* $Header: BOMPEAMB.pls 120.0.12010000.3 2010/01/13 12:32:23 agoginen ship $ */
3 /*==========================================================================+
4 | Copyright (c) 2001 Oracle Corporation, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMPEAMS.pls |
9 | Description : EAM utility programs package |
10 | Created By : Refaitheen Farook |
11 | |
12 | item_id Assembly_Item_Id |
13 | org_id Organization_id |
14 | |
15 +==========================================================================*/
16
17 /***************************************************************************
18 * Function : Enabled
19 * Returns : VARCHAR2
20 * Parameters IN : None
21 * Parameters OUT: None
22 * Purpose : Function will return the value of 'Y' or 'N'
23 * to check if eam is enabled.
24 *****************************************************************************/
25 FUNCTION Enabled RETURN VARCHAR2 IS
26
27 x_install_eam BOOLEAN ;
28 x_status VARCHAR2(50);
29 x_industry VARCHAR2(50);
30 x_schema VARCHAR2(50);
31
32
33 BEGIN
34
35 x_install_eam :=
36 Fnd_Installation.Get_App_Info
37 (application_short_name => 'EAM',
38 status => x_status,
39 industry => x_industry,
40 oracle_schema => x_schema);
41
42 IF (x_status <> 'I' or x_status is NULL)
43 THEN
44 RETURN 'N' ;
45
46 END IF;
47
48 RETURN 'Y';
49
50 END;
51
52 /***************************************************************************
53 * Function : Serial_Effective_Item
54 * Returns : VARCHAR2
55 * Parameters IN : item_id,org_id
56 * Parameters OUT: None
57 * Purpose : Function will return the value of 'Y' or 'N'
58 * to check if item is serial effective.
59 *****************************************************************************/
60 FUNCTION Serial_Effective_Item(item_id NUMBER,
61 org_id NUMBER) RETURN VARCHAR2 IS
62 l_serial_eff_item VARCHAR2(1);
63 BEGIN
64 IF item_id IS NULL THEN
65 RETURN ('N');
66 END IF;
67
68 SELECT decode(effectivity_control , 2 , 'Y' , 'N')
69 INTO l_serial_eff_item
70 FROM mtl_system_items
71 WHERE inventory_item_id = item_id
72 AND organization_id = org_id
73 AND nvl(eam_item_type,0) IN (1,3); /*Bug 7286777: Serial Effectivity
74 should be for both Asset Group as well as Rebuildable*/
75
76 RETURN (l_serial_eff_item);
77
78 EXCEPTION WHEN NO_DATA_FOUND THEN
79 l_serial_eff_item := 'N';
80 RETURN ( l_serial_eff_item );
81 WHEN OTHERS THEN
82 RETURN ( NULL );
83
84 END;
85
86
87 /***************************************************************************
88 * Function : OrgIsEamEnabled
89 * Returns : VARCHAR2
90 * Parameters IN : p_org_id
91 * Parameters OUT: None
92 * Purpose : Function will return the value of 'Y' or 'N'
93 * to check if organization is eAM enabled.
94 *****************************************************************************/
95 FUNCTION OrgIsEamEnabled(p_org_id NUMBER) RETURN VARCHAR2
96 IS
97
98 CURSOR GetEamEnabledFlag IS
99 SELECT NVL(eam_enabled_flag, 'N') eam_enabled_flag
100 FROM mtl_parameters
101 WHERE organization_id = p_org_id ;
102
103
104 x_eam_enabled_flag VARCHAR2(1) ;
105
106 BEGIN
107
108 FOR X_Eam in GetEamEnabledFlag LOOP
109 x_eam_enabled_flag := X_Eam.eam_enabled_flag ;
110 END LOOP;
111
112 RETURN x_eam_enabled_flag ;
113
114 END OrgIsEamEnabled ;
115
116
117 /***************************************************************************
118 * Function : Asset_Activity_Item
119 * Returns : VARCHAR2
120 * Parameters IN : item_id,org_id
121 * Parameters OUT: None
122 * Purpose : Function will return 'Y' for asset activity item
123 *****************************************************************************/
124 FUNCTION Asset_Activity_Item(item_id NUMBER,
125 org_id NUMBER) RETURN VARCHAR2 IS
126 l_asset_activity_item VARCHAR2(1);
127 BEGIN
128 IF item_id IS NULL THEN
129 RETURN ('N');
130 END IF;
131
132 SELECT 'Y'
133 INTO l_asset_activity_item
134 FROM mtl_system_items
135 WHERE inventory_item_id = item_id AND
136 organization_id = org_id AND
137 nvl(eam_item_type,0) = 2;
138
139 RETURN (l_asset_activity_item);
140
141 EXCEPTION WHEN NO_DATA_FOUND THEN
142 l_asset_activity_item := 'N';
143 RETURN ( l_asset_activity_item );
144
145 WHEN OTHERS THEN
146 RETURN ( NULL );
147 END;
148
149
150 /***************************************************************************
151 * Function : Asset_Group_Item
152 * Returns : VARCHAR2
153 * Parameters IN : item_id,org_id
154 * Parameters OUT: None
155 * Purpose : Function will return 'Y' for asset group item
156 *****************************************************************************/
157 FUNCTION Asset_Group_Item(item_id NUMBER,
158 org_id NUMBER) RETURN VARCHAR2 IS
159 l_asset_group_item VARCHAR2(1);
160 BEGIN
161 IF item_id IS NULL THEN
162 RETURN ('N');
163 END IF;
164
165 SELECT 'Y'
166 INTO l_asset_group_item
167 FROM mtl_system_items
168 WHERE inventory_item_id = item_id AND
169 organization_id = org_id AND
170 nvl(eam_item_type,0) = 1;
171
172 RETURN (l_asset_group_item);
173
174 EXCEPTION WHEN NO_DATA_FOUND THEN
175 l_asset_group_item := 'N';
176 RETURN ( l_asset_group_item );
177
178 WHEN OTHERS THEN
179 RETURN ( NULL );
180 END;
181
182 /***************************************************************************
183 * Function : Direct_Item
184 * Returns : VARCHAR2
185 * Parameters IN : item_id,org_id
186 * Parameters OUT: None
187 * Purpose : Function will return 'Y' for direct item
188 *****************************************************************************/
189 FUNCTION Direct_Item(item_id NUMBER,
190 org_id NUMBER) RETURN VARCHAR2 IS
191 l_direct_item VARCHAR2(1);
192 BEGIN
193 IF item_id IS NULL THEN
194 RETURN ('N');
195 END IF;
196
197 SELECT 'Y'
198 INTO l_direct_item
199 FROM mtl_system_items
200 WHERE inventory_item_id = item_id AND
201 organization_id = org_id AND
202 /*inventory_item_flag = 'Y' AND Bug 7566475 Removing the and condition on the column inventory_item_flag*/
203 stock_enabled_flag = 'N';
204
205 RETURN (l_direct_item);
206
207 EXCEPTION WHEN NO_DATA_FOUND THEN
208 l_direct_item := 'N';
209 RETURN ( l_direct_item );
210
211 WHEN OTHERS THEN
212 RETURN ( NULL );
213 END;
214
215 END BOM_EAMUTIL;