[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_SEARCH_PVT
Source
1 PACKAGE BODY EAM_ASSET_SEARCH_PVT as
2 /* $Header: EAMVASEB.pls 115.7 2003/05/05 02:13:31 lllin ship $ */
3
4 -- Start of comments
5 -- API name : BUILD_SEARCH_SQL
6 -- Type : Private
7 -- Function :
8 -- Pre-reqs : None.
9 -- Parameters :
10 -- IN p_api_version IN NUMBER Required
11 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
13 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
14 --
15 -- p_application_id IN NUMBER Optional Default = 401 (INV)
16 -- p_descr_flexfield_name IN VARCHAR2 Opt Default = 'MTL_EAM_ASSET_ATTR_VALUES'
17 -- p_search_set_id IN NUMBER
18 -- p_where_clause IN VARCHAR2
19 -- p_purge_option IN VARCHAR2 Optional Default = FND_API.G_FALSE
20 -- OUT x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 --
24 -- x_sql_stmt OUT VARCHAR2
25 -- Version Initial version 1.0
26 --
27 -- Notes : This API Build the dynamic SQL to retrieve the Asset Numbers based on
28 -- extensible attributes criteria as identified by the search_set_id in
29 -- mtl_eam_asset_search_temp table.
30 -- ****** Sample Output statement of the API ***********
31 --
32 -- SELECT MAEAV.INVENTORY_ITEM_ID, MAEAV.SERIAL_NUMBER, MAEAV.ORGANIZATION_ID
33 -- FROM MTL_EAM_ASSET_ATTR_VALUES MAEAV
34 -- WHERE MAEAV.ATTRIBUTE_CATEGORY LIKE 'Crane Physical Attributes'
35 -- AND MAEAV.C_ATTRIBUTE1 LIKE '%d%'
36 -- AND MAEAV.D_ATTRIBUTE1 <= to_date('17-JUL-02','DD-MON-RR')
37 -- INTERSECT
38 -- SELECT MAEAV.INVENTORY_ITEM_ID, MAEAV.SERIAL_NUMBER, MAEAV.ORGANIZATION_ID
39 -- FROM MTL_EAM_ASSET_ATTR_VALUES MAEAV
40 -- WHERE MAEAV.ATTRIBUTE_CATEGORY LIKE 'Office Space'
41 -- AND MAEAV.N_ATTRIBUTE1 > 2
42 --
43 -- ******** End Sample output **************************
44 --
45 -- End of comments
46
47 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSET_SEARCH_PVT';
48
49
50 PROCEDURE BUILD_SEARCH_SQL
51 (
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
54 p_commit IN VARCHAR2 := fnd_api.g_false,
55 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
56 p_application_id IN NUMBER := 401,
57 p_descr_flexfield_name IN VARCHAR2 := 'MTL_EAM_ASSET_ATTR_VALUES',
58 p_search_set_id IN NUMBER,
59 p_where_clause IN VARCHAR2 := NULL,
60 p_purge_option IN VARCHAR2 := fnd_api.g_false,
61 x_sql_stmt OUT NOCOPY VARCHAR2,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2
65 ) AS
66 l_api_name CONSTANT VARCHAR2(30) := 'build_search_sql';
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
69 l_stmt_num NUMBER;
70 l_sql_stmt VARCHAR2(30000);
71 l_counter NUMBER;
72 l_context VARCHAR2(30);
73 l_context_counter NUMBER;
74 l_line_type_counter NUMBER;
75
76 CURSOR context_cur IS
77 SELECT DISTINCT meast.descriptive_flex_context_code AS DESCR_CONTEXT_CODE
78 FROM MTL_EAM_ASSET_SEARCH_TEMP meast
79 WHERE meast.SEARCH_SET_ID = p_search_set_id;
80
81 CURSOR attribute_cur (l_context_code VARCHAR2, l_line_type NUMBER) IS
82 SELECT meast.end_user_column_name END_USER_COLUMN_NAME,
83 meast.operator OPERATOR,
84 meast.line_type LINE_TYPE,
85 meast.attribute_varchar2_value ATTRIBUTE_VARCHAR2_VALUE,
86 meast.attribute_number_value ATTRIBUTE_NUMBER_VALUE,
87 meast.attribute_date_value ATTRIBUTE_DATE_VALUE
88 FROM MTL_EAM_ASSET_SEARCH_TEMP meast
89 WHERE meast.SEARCH_SET_ID = p_search_set_id
90 AND meast.DESCRIPTIVE_FLEX_CONTEXT_CODE = l_context_code
91 AND meast.line_type = l_line_type;
92
93 BEGIN
94 null;
95 /*
96 commented out this function body to obsolete this function.
97 The code of this function has been moved to EAMFANDF.fmb.
98
99 -- Standard Start of API savepoint
100 l_stmt_num := 10;
101 SAVEPOINT build_search_sql_pvt;
102
103 l_stmt_num := 20;
104 -- Standard call to check for call compatibility.
105 IF NOT fnd_api.compatible_api_call(
106 l_api_version
107 ,p_api_version
108 ,l_api_name
109 ,g_pkg_name) THEN
110 RAISE fnd_api.g_exc_unexpected_error;
111 END IF;
112
113 l_stmt_num := 30;
114 -- Initialize message list if p_init_msg_list is set to TRUE.
115 IF fnd_api.to_boolean(p_init_msg_list) THEN
116 fnd_msg_pub.initialize;
117 END IF;
118
119 l_stmt_num := 40;
120 -- Initialize API return status to success
121 x_return_status := fnd_api.g_ret_sts_success;
122 x_sql_stmt := NULL;
123 l_sql_stmt := NULL;
124
125 l_stmt_num := 50;
126 -- API body
127
128 l_stmt_num := 60;
129 IF (p_application_id IS NULL OR p_descr_flexfield_name IS NULL
130 OR p_search_set_id <= 0 OR p_search_set_id IS NULL) THEN
131
132 fnd_message.set_name('EAM', 'EAM_INPUT_PARAMS_NULL');
133 fnd_message.set_token('EAM_DEBUG',l_full_name||'('||l_stmt_num||')');
134 fnd_msg_pub.add;
135 l_sql_stmt := NULL;
136 RAISE fnd_api.g_exc_error;
137 END IF;
138
139 l_context_counter := 0;
140 l_counter := 0;
141
142 FOR l_context_cur IN context_cur LOOP
143
144 l_stmt_num := 70;
145
146 l_context := l_context_cur.descr_context_code;
147
148 l_context_counter := l_context_counter + 1;
149
150 -- IF (l_context_counter > 1) THEN
151 --
152 -- l_stmt_num := 80;
153 -- l_sql_stmt := l_sql_stmt || ' INTERSECT ';
154 -- END IF;
155
156 l_stmt_num := 90;
157
158 -- l_sql_stmt := l_sql_stmt || 'SELECT MAEAV.INVENTORY_ITEM_ID, MAEAV.SERIAL_NUMBER, MAEAV.ORGANIZATION_ID'
159 l_sql_stmt := l_sql_stmt || ' AND EXISTS (SELECT * '
160 || ' '
161 || 'FROM MTL_EAM_ASSET_ATTR_VALUES MAEAV WHERE ';
162
163 l_stmt_num := 100;
164 l_sql_stmt := l_sql_stmt || ' MAEAV.ATTRIBUTE_CATEGORY LIKE '
165 || ' '''
166 || l_context
167 || ''' ';
168
169 l_stmt_num := 110;
170 FOR l_line_type_counter IN 1..3 LOOP
171
172
173 l_stmt_num := 120;
174 FOR l_attribute_cur IN attribute_cur(l_context, l_line_type_counter) LOOP
175
176
177 l_stmt_num := 130;
178 l_sql_stmt := l_sql_stmt ||' AND '
179 || ' MAEAV.'
180 || UPPER(EAM_ASSET_SEARCH_PVT.get_attribute_column_name
181 (
182 p_application_id,
183 p_descr_flexfield_name,
184 l_context,
185 l_attribute_cur.end_user_column_name
186 ))
187 || ' '
188 || NVL(l_attribute_cur.operator, 'LIKE')
189 || ' ';
190
191
192 l_stmt_num := 140;
193 IF(l_line_type_counter = 1) THEN
194 l_stmt_num := 142;
195 l_sql_stmt := l_sql_stmt || ' '''
196 || l_attribute_cur.attribute_varchar2_value
197 || ''' ';
198 ELSIF(l_line_type_counter = 2) THEN
199 l_stmt_num := 144;
200 l_sql_stmt := l_sql_stmt || l_attribute_cur.attribute_number_value;
201 ELSIF(l_line_type_counter = 3) THEN
202 l_stmt_num := 146;
203 l_sql_stmt := l_sql_stmt || 'to_date('''
204 || l_attribute_cur.attribute_date_value
205 ||''',''DD-MON-RR'') ';
206 END IF;
207
208 END LOOP; -- end attribute Loop
209
210 END LOOP; -- end line type loop
211
212 l_sql_stmt := l_sql_stmt ||' AND MAEAV.INVENTORY_ITEM_ID = mtl_eam_asset_numbers_all_v.INVENTORY_ITEM_ID ';
213 l_sql_stmt := l_sql_stmt ||' AND MAEAV.SERIAL_NUMBER = mtl_eam_asset_numbers_all_v.SERIAL_NUMBER ';
214 l_sql_stmt := l_sql_stmt ||' AND MAEAV.ORGANIZATION_ID = mtl_eam_asset_numbers_all_v.CURRENT_ORGANIZATION_ID ';
215 l_sql_stmt := l_sql_stmt ||' ) ';
216
217 END LOOP; -- end context loop
218
219
220 l_counter := 0;
221
222 l_stmt_num := 150;
223 -- Purge Search Criteria from Temp table
224 IF fnd_api.to_boolean(p_purge_option) THEN
225 DELETE MTL_EAM_ASSET_SEARCH_TEMP
226 WHERE SEARCH_SET_ID = p_search_set_id;
227 END IF;
228
229
230 -- Debug purposes only
231 --FOR l_counter IN 1..125 LOOP
232 -- dbms_output.put_line (substr(l_sql_stmt, (240*(l_counter-1))+1, (240*(l_counter))));
233 --END LOOP;
234
235
236 l_stmt_num := 160;
237 x_sql_stmt := l_sql_stmt;
238
239
240 l_stmt_num := 998;
241
242 -- End of API body.
243 -- Standard check of p_commit.
244 IF fnd_api.to_boolean(p_commit) THEN
245 COMMIT WORK;
246 END IF;
247
248 l_stmt_num := 999;
249 -- Standard call to get message count and if count is 1, get message info.
250 fnd_msg_pub.count_and_get(
251 p_encoded => fnd_api.g_false
252 ,p_count => x_msg_count
253 ,p_data => x_msg_data);
254
255
256 EXCEPTION
257 WHEN fnd_api.g_exc_error THEN
258 ROLLBACK TO build_search_sql_pvt;
259 x_return_status := fnd_api.g_ret_sts_error;
260 x_sql_stmt := l_sql_stmt;
261 fnd_msg_pub.count_and_get(
262 p_encoded => fnd_api.g_false
263 ,p_count => x_msg_count
264 ,p_data => x_msg_data);
265 WHEN fnd_api.g_exc_unexpected_error THEN
266 ROLLBACK TO build_search_sql_pvt;
267 x_return_status := fnd_api.g_ret_sts_unexp_error;
268 x_sql_stmt := l_sql_stmt;
269 fnd_msg_pub.count_and_get(
270 p_encoded => fnd_api.g_false
271 ,p_count => x_msg_count
272 ,p_data => x_msg_data);
273 WHEN OTHERS THEN
274 ROLLBACK TO build_search_sql_pvt;
275 x_return_status := fnd_api.g_ret_sts_unexp_error;
276 x_sql_stmt := l_sql_stmt;
277
278 IF fnd_msg_pub.check_msg_level(
279 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
280 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
281 END IF;
282
283 fnd_msg_pub.count_and_get(
284 p_encoded => fnd_api.g_false
285 ,p_count => x_msg_count
286 ,p_data => x_msg_data);
287
288 */
289 END build_search_sql;
290
291
292
293
294
295 -- Start of comments
296 -- API name :
297 -- Type : Private
298 -- Function : GET_ATTRIBUTE_COLUMN_NAME
299 -- Pre-reqs : None.
300 -- Parameters :
301 -- IN
302 -- p_application_id IN NUMBER Optional Default = 401 (INV)
303 -- p_descr_flexfield_name IN VARCHAR2 Opt Default = 'MTL_EAM_ASSET_ATTR_VALUES'
304 -- p_descr_flex_context_code IN VARCHAR2 Required
305 -- p_end_user_column_name IN VARCHAR2 Required
306 --
307 -- RETURNS VARCHAR2
308
309 --
310 -- Notes : This function returns the column name where a specific attribute value
311 -- is stored in table MTL_EAM_ASSET_ATTR_VALUES based on flexfield metadata
312 --
313 -- End of comments
314
315
316 FUNCTION get_attribute_column_name
317 (
318 p_application_id IN NUMBER := 401,
319 p_descr_flexfield_name IN VARCHAR2 := 'MTL_EAM_ASSET_ATTR_VALUES',
320 p_descr_flex_context_code IN VARCHAR2,
321 p_end_user_column_name IN VARCHAR2
322 )
323 RETURN VARCHAR2 IS
324 l_application_column_name VARCHAR2(30);
325 l_stmt_num NUMBER;
326 l_api_name VARCHAR2(30) := 'get_attribute_column_name';
327 BEGIN
328
329 l_stmt_num := 10;
330 l_application_column_name := NULL;
331
332 BEGIN
333
334 l_stmt_num := 20;
335
336 SELECT application_column_name
337 INTO l_application_column_name
338 FROM FND_DESCR_FLEX_COLUMN_USAGES fdfcu
339 WHERE fdfcu.APPLICATION_ID = p_application_id
340 AND fdfcu.DESCRIPTIVE_FLEXFIELD_NAME = p_descr_flexfield_name
341 AND fdfcu.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_descr_flex_context_code
342 AND fdfcu.end_user_column_name = p_end_user_column_name;
343 EXCEPTION
344 WHEN OTHERS THEN
345 l_application_column_name := NULL;
346 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
347 RETURN l_application_column_name;
348 END;
349
350 RETURN l_application_column_name;
351
352 END get_attribute_column_name;
353
354
355 END EAM_ASSET_SEARCH_PVT;
356