1 PACKAGE BODY EAM_ATTR_ASSIGN_PVT as
2 /* $Header: EAMVATAB.pls 120.1 2005/07/28 08:12:53 yjhabak noship $ */
3 -- Start of comments
4 -- API name : EAM_ATTR_ASSIGN_PVT
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN P_API_VERSION IN NUMBER REQUIRED
10 -- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
11 -- DEFAULT = FND_API.G_FALSE
12 -- P_COMMIT IN VARCHAR2 OPTIONAL
13 -- DEFAULT = FND_API.G_FALSE
14 -- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
15 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
16 -- P_ROWID IN OUT VARCHAR2 REQUIRED
17 -- P_ATTRIBUTE_ASSOCIATION_ID IN NUMBER REQUIRED
18 -- P_APPLICATION_ID IN NUMBER REQUIRED
19 -- P_FLEXFIELD_NAME IN VARCHAR2(40) REQUIRED
20 -- P_FLEX_CONTEXT_CODE IN VARCHAR2(30) REQUIRED
21 -- P_ORGANIZATION_ID IN NUMBER REQUIRED
22 -- P_INVENTORY_ITEM_ID IN NUMBER REQUIRED
23 -- P_ENABLED_FLAG IN VARCHAR2(1) REQUIRED
24 -- P_REQUEST_ID IN NUMBER DEFAULT NULL OPTIONAL
25 -- P_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL OPTIONAL
26 -- P_PROGRAM_ID IN NUMBER DEFAULT NULL OPTIONAL
27 -- P_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL
28 -- OUT X_RETURN_STATUS OUT VARCHAR2(1)
29 -- X_MSG_COUNT OUT NUMBER
30 -- X_MSG_DATA OUT VARCHAR2(2000)
31 --
32 -- Version Current version 115.0
33 --
34 -- Notes : Note text
35 --
36 -- End of comments
37
38 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_attr_assign_pvt';
39
40
41 PROCEDURE INSERT_ROW(
42 P_API_VERSION IN NUMBER,
43 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
44 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
45 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
46 P_ROWID IN OUT NOCOPY VARCHAR2,
47 P_ATTRIBUTE_ASSOCIATION_ID IN OUT NOCOPY NUMBER,
48 P_APPLICATION_ID NUMBER,
49 P_FLEXFIELD_NAME VARCHAR2,
50 P_FLEX_CONTEXT_CODE VARCHAR2,
51 P_ORGANIZATION_ID NUMBER,
52 P_INVENTORY_ITEM_ID NUMBER,
53 P_ENABLED_FLAG VARCHAR2,
54 P_LAST_UPDATE_DATE DATE,
55 P_LAST_UPDATED_BY NUMBER,
56 P_CREATION_DATE DATE,
57 P_CREATED_BY NUMBER,
58 P_LAST_UPDATE_LOGIN NUMBER,
59 P_CREATION_ORGANIZATION_ID NUMBER,
60 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
61 X_MSG_COUNT OUT NOCOPY NUMBER,
62 X_MSG_DATA OUT NOCOPY VARCHAR2
63 ) IS
64 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
67
68 CURSOR C IS SELECT rowid FROM MTL_EAM_ASSET_ATTR_GROUPS
69 WHERE ASSOCIATION_ID = P_ATTRIBUTE_ASSOCIATION_ID;
70 BEGIN
71
72 -- Standard Start of API savepoint
73 SAVEPOINT apiname_apitype;
74
75 -- Standard call to check for call compatibility.
76 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
77 RAISE fnd_api.g_exc_unexpected_error;
78 END IF;
79
80 -- Initialize message list if p_init_msg_list is set to TRUE.
81 IF fnd_api.to_boolean(p_init_msg_list) THEN
82 fnd_msg_pub.initialize;
83 END IF;
84
85 -- Initialize API return status to success
86 x_return_status := fnd_api.g_ret_sts_success;
87
88 -- API body
89
90 INSERT INTO MTL_EAM_ASSET_ATTR_GROUPS(
91 ASSOCIATION_ID,
92 APPLICATION_ID,
93 DESCRIPTIVE_FLEXFIELD_NAME,
94 DESCRIPTIVE_FLEX_CONTEXT_CODE,
95 ORGANIZATION_ID,
96 INVENTORY_ITEM_ID,
97 ENABLED_FLAG,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 CREATION_DATE,
101 CREATED_BY,
102 LAST_UPDATE_LOGIN,
103 CREATION_ORGANIZATION_ID
104 ) values (
105 -- P_ATTRIBUTE_ASSOCIATION_ID,
106 mtl_eam_asset_attr_groups_s.nextval,
107 P_APPLICATION_ID,
108 P_FLEXFIELD_NAME,
109 P_FLEX_CONTEXT_CODE,
110 P_ORGANIZATION_ID,
111 P_INVENTORY_ITEM_ID,
112 P_ENABLED_FLAG,
113 P_LAST_UPDATE_DATE,
114 P_LAST_UPDATED_BY,
115 P_CREATION_DATE,
116 P_CREATED_BY,
117 P_LAST_UPDATE_LOGIN,
118 P_CREATION_ORGANIZATION_ID) returning association_id, rowid into P_ATTRIBUTE_ASSOCIATION_ID, P_ROWID;
119
120 OPEN C;
121 FETCH C INTO P_Rowid;
122 if (C%NOTFOUND) then
123 CLOSE C;
124 Raise NO_DATA_FOUND;
125 end if;
126 CLOSE C;
127
128 -- End of API body.
129 -- Standard check of p_commit.
130 IF fnd_api.to_boolean(p_commit) THEN
131 COMMIT WORK;
132 END IF;
133
134 -- Standard call to get message count and if count is 1, get message info.
135 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
136 EXCEPTION
137 WHEN fnd_api.g_exc_error THEN
138 ROLLBACK TO apiname_apitype;
139 x_return_status := fnd_api.g_ret_sts_error;
140 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
141 WHEN fnd_api.g_exc_unexpected_error THEN
142 ROLLBACK TO apiname_apitype;
143 x_return_status := fnd_api.g_ret_sts_unexp_error;
144 fnd_msg_pub.count_and_get(
145 p_count => x_msg_count
146 ,p_data => x_msg_data);
147 WHEN OTHERS THEN
148 ROLLBACK TO apiname_apitype;
149 x_return_status := fnd_api.g_ret_sts_unexp_error;
150
151 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
152 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
153 END IF;
154
155 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
156
157 END Insert_Row;
158
159 PROCEDURE LOCK_ROW(
160 P_API_VERSION IN NUMBER,
161 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
162 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
163 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
164 P_ROWID VARCHAR2,
165 P_ATTRIBUTE_ASSOCIATION_ID IN OUT NOCOPY NUMBER,
166 P_APPLICATION_ID NUMBER,
167 P_FLEXFIELD_NAME VARCHAR2,
168 P_FLEX_CONTEXT_CODE VARCHAR2,
169 P_ORGANIZATION_ID NUMBER,
170 P_INVENTORY_ITEM_ID NUMBER,
171 P_ENABLED_FLAG VARCHAR2,
172 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
173 X_MSG_COUNT OUT NOCOPY NUMBER,
174 X_MSG_DATA OUT NOCOPY VARCHAR2
175 ) IS
176 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
177 l_api_version CONSTANT NUMBER := 1.0;
178 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
179
180 CURSOR C IS
181 SELECT *
182 FROM MTL_EAM_ASSET_ATTR_GROUPS
183 WHERE rowid = P_Rowid
184 FOR UPDATE of ASSOCIATION_ID NOWAIT;
185 Recinfo C%ROWTYPE;
186
187 BEGIN
188
189 -- Standard Start of API savepoint
190 SAVEPOINT apiname_apitype;
191
192 -- Standard call to check for call compatibility.
193 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
194 RAISE fnd_api.g_exc_unexpected_error;
195 END IF;
196
197 -- Initialize message list if p_init_msg_list is set to TRUE.
198 IF fnd_api.to_boolean(p_init_msg_list) THEN
199 fnd_msg_pub.initialize;
200 END IF;
201
202 -- Initialize API return status to success
203 x_return_status := fnd_api.g_ret_sts_success;
204
205 -- API body
206
207 OPEN C;
208 FETCH C INTO Recinfo;
209 if (C%NOTFOUND) then
210 CLOSE C;
211 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
212 APP_EXCEPTION.Raise_Exception;
213 end if;
214 CLOSE C;
215 if (
216 (Recinfo.ASSOCIATION_ID = P_ATTRIBUTE_ASSOCIATION_ID)
217 AND (Recinfo.APPLICATION_ID = P_APPLICATION_ID)
218 AND (Recinfo.DESCRIPTIVE_FLEXFIELD_NAME = P_FLEXFIELD_NAME)
219 AND (Recinfo.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_FLEX_CONTEXT_CODE)
220 AND (Recinfo.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID)
221 AND (Recinfo.ENABLED_FLAG = P_ENABLED_FLAG)
222 ) then
223 return;
224 else
225 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
226 APP_EXCEPTION.Raise_Exception;
227 end if;
228
229 -- End of API body.
230 -- Standard check of p_commit.
231 IF fnd_api.to_boolean(p_commit) THEN
232 COMMIT WORK;
233 END IF;
234
235 -- Standard call to get message count and if count is 1, get message info.
236 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
237 EXCEPTION
238 WHEN fnd_api.g_exc_error THEN
239 ROLLBACK TO apiname_apitype;
240 x_return_status := fnd_api.g_ret_sts_error;
241 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
242 WHEN fnd_api.g_exc_unexpected_error THEN
243 ROLLBACK TO apiname_apitype;
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245 fnd_msg_pub.count_and_get(
246 p_count => x_msg_count
247 ,p_data => x_msg_data);
248 WHEN OTHERS THEN
249 ROLLBACK TO apiname_apitype;
250 x_return_status := fnd_api.g_ret_sts_unexp_error;
251
252 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
253 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
254 END IF;
255
256 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
257
258 END Lock_Row;
259
260 PROCEDURE UPDATE_ROW(
261 P_API_VERSION IN NUMBER,
262 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
263 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
264 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
265 P_ROWID VARCHAR2,
266 P_ENABLED_FLAG VARCHAR2,
267 P_LAST_UPDATE_DATE DATE,
268 P_LAST_UPDATED_BY NUMBER,
269 P_LAST_UPDATE_LOGIN NUMBER,
270 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
271 X_MSG_COUNT OUT NOCOPY NUMBER,
272 X_MSG_DATA OUT NOCOPY VARCHAR2
273 ) IS
274 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
275 l_api_version CONSTANT NUMBER := 1.0;
276 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
277 BEGIN
278 -- Standard Start of API savepoint
279 SAVEPOINT apiname_apitype;
280
281 -- Standard call to check for call compatibility.
282 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
283 RAISE fnd_api.g_exc_unexpected_error;
284 END IF;
285
286 -- Initialize message list if p_init_msg_list is set to TRUE.
287 IF fnd_api.to_boolean(p_init_msg_list) THEN
288 fnd_msg_pub.initialize;
289 END IF;
290
291 -- Initialize API return status to success
292 x_return_status := fnd_api.g_ret_sts_success;
293
294 -- API body
295
296 UPDATE MTL_EAM_ASSET_ATTR_GROUPS
297 SET
298 ENABLED_FLAG = P_ENABLED_FLAG,
299 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
300 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
302 WHERE ROWID = P_ROWID;
303
304 if (SQL%NOTFOUND) then
305 Raise NO_DATA_FOUND;
306 end if;
307
308 -- End of API body.
309 -- Standard check of p_commit.
310 IF fnd_api.to_boolean(p_commit) THEN
311 COMMIT WORK;
312 END IF;
313
314 -- Standard call to get message count and if count is 1, get message info.
315 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
316 EXCEPTION
317 WHEN fnd_api.g_exc_error THEN
318 ROLLBACK TO apiname_apitype;
319 x_return_status := fnd_api.g_ret_sts_error;
320 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
321 WHEN fnd_api.g_exc_unexpected_error THEN
322 ROLLBACK TO apiname_apitype;
323 x_return_status := fnd_api.g_ret_sts_unexp_error;
324 fnd_msg_pub.count_and_get(
325 p_count => x_msg_count
326 ,p_data => x_msg_data);
327 WHEN OTHERS THEN
328 ROLLBACK TO apiname_apitype;
329 x_return_status := fnd_api.g_ret_sts_unexp_error;
330
331 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
332 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
333 END IF;
334
335 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
336
337 END Update_Row;
338
339 END EAM_ATTR_ASSIGN_PVT;