[Home] [Help]
PACKAGE BODY: APPS.INV_SERIAL_NUMBER_ATTR
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_SERIAL_NUMBER_ATTR';
1 PACKAGE BODY INV_SERIAL_NUMBER_ATTR AS
2 /* $Header: INVSATRB.pls 120.2 2005/07/15 03:45:00 abshukla ship $ */
3
5
6 procedure print_debug(p_err_msg VARCHAR2)
7 IS
8 BEGIN
9 IF (g_debug = 1) THEN
10 inv_log_util.trace(p_err_msg, G_PKG_NAME, 9);
11 --DBMS_OUTPUT.PUT_LINE(p_err_msg);
12 END IF;
13 end print_debug;
14
15 procedure Update_Serial_number_attr(
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19
20 p_serial_number IN VARCHAR2,
21 p_inventory_item_id IN NUMBER,
22 p_attribute_category IN VARCHAR2 DEFAULT NULL,
23 p_attributes_tbl IN inv_serial_number_attr.char_table) IS
24
25 TYPE seg_name IS TABLE OF VARCHAR2(500)
26 INDEX BY BINARY_INTEGER;
27
28 l_tempvar NUMBER;
29 l_default_attr_category VARCHAR2(240);
30 l_context VARCHAR2(1000);
31 l_context_r fnd_dflex.context_r;
32 l_contexts_dr fnd_dflex.contexts_dr;
33 l_dflex_r fnd_dflex.dflex_r;
34 l_segments_dr fnd_dflex.segments_dr;
35 l_enabled_seg_name seg_name;
36 l_inv_attributes_tbl seg_name;
37 l_nsegments BINARY_INTEGER;
38 l_global_context BINARY_INTEGER;
39 v_index NUMBER := 1;
40 v_index1 NUMBER := 1;
41 l_chk_flag NUMBER := 0;
42 l_return_status VARCHAR2(1);
43 l_msg_count NUMBER;
44 l_msg_data VARCHAR2(1000);
45 l_attr_index NUMBER;
46 g_miss_char CONSTANT VARCHAR2(1) := CHR(0);
47
48 /* Variables used for Validate_desccols procedure */
49 error_segment VARCHAR2(30);
50 errors_received EXCEPTION;
51 serial_not_found EXCEPTION;
52 error_msg VARCHAR2(5000);
53 s NUMBER;
54 e NUMBER;
55 l_null_char_val VARCHAR2(1000);
56 BEGIN
57
58 -- Initialize API return status to success
59 x_return_status := FND_API.G_RET_STS_SUCCESS;
60
61 IF (g_debug = 1) THEN
62 print_debug('item_id: '||p_inventory_item_id||', serial_number: '||p_serial_number||
63 ', attribute_category: '||p_attribute_category);
64
65 l_attr_index := p_attributes_tbl.FIRST;
66 WHILE l_attr_index <= p_attributes_tbl.LAST LOOP
67 print_debug('p_attributes_tbl'||l_attr_index||' = ' ||p_attributes_tbl(l_attr_index));
68 l_attr_index := p_attributes_tbl.NEXT(l_attr_index);
69 END LOOP;
70 END IF;
71
72 BEGIN
73 SELECT 1
74 INTO l_tempvar
75 FROM mtl_serial_numbers
76 WHERE inventory_item_id = p_inventory_item_id
77 AND serial_number = p_serial_number
78 AND current_status in (1,3,4); --Bug4493227
79 EXCEPTION
80 WHEN no_data_found THEN
81 IF (g_debug = 1) THEN
82 print_debug('no data found for the serial');
83 END IF;
84
85 fnd_message.set_name('INV', 'INV_SER_NOTEXIST');
86 fnd_message.set_token('TOKEN', p_serial_number);
87 fnd_msg_pub.ADD;
88 RAISE serial_not_found;
89 END;
90
91 -- Initialize savepoint
92 SAVEPOINT get_serial_attr_information;
93
94 IF p_attribute_category IS NULL THEN
95 --Get default attribute_category context
96 l_dflex_r.application_id := 401;
97 l_dflex_r.flexfield_name := 'MTL_SERIAL_NUMBERS';
98 /* Get all contexts */
99 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
100
101 IF g_debug = 1 THEN
102 print_debug('Found contexts for the Flexfield MTL_SERIAL_NUMBERS');
103 END IF;
104
105 /* From the l_contexts_dr, get the position of the global context */
106 l_global_context := l_contexts_dr.global_context;
107
111
108 IF g_debug = 1 THEN
109 print_debug('Found the position of the global context');
110 END IF;
112 /* Using the position get the segments in the global context which are enabled */
113 l_default_attr_category := l_contexts_dr.context_code(l_global_context);
114 ELSE
115 l_default_attr_category := p_attribute_category;
116 END IF;
117
118 IF (g_debug = 1) THEN
119 print_debug('l_default_attr_category: ' ||l_default_attr_category);
120 END IF;
121
122 /* Populate the flex field record */
123 IF l_default_attr_category IS NOT NULL THEN
124 --AND p_attributes_tbl.COUNT > 0 THEN
125 l_dflex_r.application_id := 401;
126 l_dflex_r.flexfield_name := 'MTL_SERIAL_NUMBERS';
127 /* Get all contexts */
128 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
129
130 IF g_debug = 1 THEN
131 print_debug('Found contexts for the Flexfield MTL_SERIAL_NUMBERS');
132 END IF;
133
134 /* From the l_contexts_dr, get the position of the global context */
135 l_global_context := l_contexts_dr.global_context;
136
137 IF g_debug = 1 THEN
138 print_debug('Found the position of the global context');
139 END IF;
140
141 /* Using the position get the segments in the global context which are enabled */
142 l_context := l_contexts_dr.context_code(l_global_context);
143
144 IF g_debug = 1 THEN
145 print_debug('l_context: ' ||l_context);
146 END IF;
147
148 /* Prepare the context_r type for getting the segments associated with the global context */
149 l_context_r.flexfield := l_dflex_r;
150 l_context_r.context_code := l_context;
151
152 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
153
154 IF g_debug = 1 THEN
155 print_debug('After successfully getting all the enabled segments for the Global Context ');
156 END IF;
157
158 /* read through the segments */
159 l_nsegments := l_segments_dr.nsegments;
160
161 IF g_debug = 1 THEN
162 print_debug('The number of enabled segments for the Global Context are ' || l_nsegments);
163 END IF;
164
165 FOR i IN 1 .. l_nsegments LOOP
166 IF g_debug = 1 THEN
167 print_debug('v_index is ' || v_index);
168 print_debug('application_column_name is ' || l_segments_dr.application_column_name(i));
169 END IF;
170
171 l_enabled_seg_name(v_index) := l_segments_dr.application_column_name(i);
172
173 IF g_debug = 1 THEN
174 print_debug('The segment is ' || l_segments_dr.segment_name(i));
175 print_debug('p_attributes_tbl count ' ||p_attributes_tbl.count);
176 END IF;
177
178 IF l_segments_dr.is_required(i) THEN
179 IF NOT p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
180 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
181 fnd_message.set_name('INV', 'INV_REQ_SEG_MISS');
182 fnd_message.set_token('SEGMENT', l_segments_dr.segment_name(i));
183 fnd_msg_pub.ADD;
184
185 IF g_debug = 1 THEN
186 print_debug('Req segment is not populated');
187 END IF;
188
189 RAISE FND_API.G_EXC_ERROR;
190 END IF;
191 ELSE
192 IF g_debug = 1 THEN
193 print_debug('This segment is not required');
194 END IF;
195 END IF;
196
197 IF p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
198 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
199 fnd_flex_descval.set_column_value(
200 l_segments_dr.application_column_name(i)
201 , p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i)
202 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9))
203 );
204 ELSE
205 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i), l_null_char_val);
206 END IF;
207
208 --fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i),p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i),INSTR(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)));
209 v_index := v_index + 1;
210 END LOOP;
211
212 IF l_enabled_seg_name.COUNT > 0 THEN
213 FOR i IN l_enabled_seg_name.FIRST .. l_enabled_seg_name.LAST LOOP
214 IF g_debug = 1 THEN
215 print_debug('The enabled segment : ' || l_enabled_seg_name(i));
216 END IF;
217 END LOOP;
218 END IF;
219
220 /* Initialise the l_context_value to null */
221 l_context := NULL;
222 l_nsegments := 0;
223
224 /*Get the context for the item passed */
225 IF l_default_attr_category IS NOT NULL THEN
226 l_context := l_default_attr_category;
227 /* Set flex context for validation of the value set */
228 fnd_flex_descval.set_context_value(l_context);
229
230 IF g_debug = 1 THEN
231 print_debug('The value of INV context is ' || l_context);
232 END IF;
233
234 /* Prepare the context_r type */
235 l_context_r.flexfield := l_dflex_r;
236 l_context_r.context_code := l_context;
237 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
241 print_debug('No of segments enabled for context ' || l_context || ' are ' || l_nsegments);
238 /* read through the segments */
239 l_nsegments := l_segments_dr.nsegments;
240 IF g_debug = 1 THEN
242 END IF;
243
244 print_debug('v_index is ' || v_index);
245 v_index := 1;
246 FOR i IN 1 .. l_nsegments LOOP
247 l_enabled_seg_name(v_index) := l_segments_dr.application_column_name(i);
248
249 print_debug('v_index is ' || v_index);
250 print_debug('The segment is ' || l_segments_dr.segment_name(i));
251
252 IF l_segments_dr.is_required(i) THEN
253 IF NOT p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
254 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
255 fnd_message.set_name('INV', 'INV_REQ_SEG_MISS');
256 fnd_message.set_token('SEGMENT', l_segments_dr.segment_name(i));
257 fnd_msg_pub.ADD;
258 RAISE FND_API.G_EXC_ERROR;
259
260 IF g_debug = 1 THEN
261 print_debug('Req segment is not populated');
262 END IF;
263 END IF;
264 END IF;
265
266 IF p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
267 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
268 fnd_flex_descval.set_column_value(
269 l_segments_dr.application_column_name(i)
270 , p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i)
271 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9))
272 );
273 ELSE
274 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i), l_null_char_val);
275 END IF;
276
277 v_index := v_index + 1;
278 END LOOP;
279
280 --IF l_enabled_seg_name.count = P_ATTRIBUTES_TBL.count THEN
281 /*v_index1 := p_attributes_tbl.FIRST;
282
283 print_debug('l_enabled_seg_name.count is ' || l_enabled_seg_name.COUNT);
284 WHILE v_index1 <= p_attributes_tbl.LAST LOOP
285 IF g_debug = 1 THEN
286 print_debug('The value of segment is ' || v_index1);
287 END IF;
288
289 FOR i IN 1 .. l_enabled_seg_name.COUNT LOOP
290 IF l_enabled_seg_name(i) = 'ATTRIBUTE' || v_index1 THEN
291 print_debug('The value of segments have matched '||l_enabled_seg_name(i));
292 l_chk_flag := 1;
293 EXIT;
294 END IF;
295 END LOOP;
296
297 IF l_chk_flag = 0 AND p_attributes_tbl(v_index1) IS NOT NULL THEN
298 fnd_message.set_name('INV', 'INV_WRONG_SEG_POPULATE');
299 fnd_message.set_token('SEGMENT', 'ATTRIBUTE' || v_index1);
300 fnd_message.set_token('CONTEXT', l_context);
301 fnd_msg_pub.ADD;
302 --print_debug('Error out. Correct segmenst are not populated ');
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305
306 v_index1 := p_attributes_tbl.NEXT(v_index1);
307 l_chk_flag := 0;
308 END LOOP;*/
309
310 /*Make a call to FND_FLEX_DESCVAL.validate_desccols */
311 IF fnd_flex_descval.validate_desccols(appl_short_name => 'INV', desc_flex_name => 'MTL_SERIAL_NUMBERS', values_or_ids => 'I', validation_date => SYSDATE) THEN
312 IF g_debug = 1 THEN
313 print_debug('Value set validation successful');
314 END IF;
315 ELSE
316 IF g_debug = 1 THEN
317 error_segment := fnd_flex_descval.error_segment;
318 print_debug('Value set validation failed for segment ' || error_segment);
319 RAISE errors_received;
320 END IF;
321 END IF;
322 END IF; /*If P attribute category is not null */
323
324 END IF; /* l_default_attr_category IS NOT NULL */
325
326 IF p_attributes_tbl.COUNT > 0 THEN
327 l_attr_index := p_attributes_tbl.FIRST;
328
329 WHILE l_attr_index <= p_attributes_tbl.LAST LOOP
330 IF p_attributes_tbl(l_attr_index) = g_miss_char THEN
331 l_inv_attributes_tbl(l_attr_index) := g_miss_char;
332 ELSE
333 l_inv_attributes_tbl(l_attr_index) := p_attributes_tbl(l_attr_index);
334 END IF;
335
336 l_attr_index := p_attributes_tbl.NEXT(l_attr_index);
337 END LOOP;
338
339 -- Setting other attributes which are not passed to null
340 l_attr_index := 1;
341 WHILE l_attr_index <= 15 LOOP
342 IF NOT l_inv_attributes_tbl.EXISTS(l_attr_index) THEN
343 l_inv_attributes_tbl(l_attr_index) := null;
344 END IF;
345 l_attr_index := l_attr_index + 1;
346 END LOOP;
347 END IF;
348
349 IF g_debug = 1 THEN
350 l_attr_index := l_inv_attributes_tbl.FIRST;
351 WHILE l_attr_index <= l_inv_attributes_tbl.LAST LOOP
352 print_debug('l_inv_attributes_tbl'||l_attr_index||' = ' ||l_inv_attributes_tbl(l_attr_index));
353 l_attr_index := l_inv_attributes_tbl.NEXT(l_attr_index);
354 END LOOP;
355 print_debug('updating MSN with attributes');
356 print_debug('item_id = ' ||p_inventory_item_id||', serial_number = ' ||p_serial_number);
357 END IF;
358
359 UPDATE mtl_serial_numbers
360 SET
361 attribute_category = l_default_attr_category
362 , attribute1 = DECODE(l_inv_attributes_tbl(1), g_miss_char, NULL, NULL, attribute1, l_inv_attributes_tbl(1))
363 , attribute2 = DECODE(l_inv_attributes_tbl(2), g_miss_char, NULL, NULL, attribute2, l_inv_attributes_tbl(2))
364 , attribute3 = DECODE(l_inv_attributes_tbl(3), g_miss_char, NULL, NULL, attribute3, l_inv_attributes_tbl(3))
365 , attribute4 = DECODE(l_inv_attributes_tbl(4), g_miss_char, NULL, NULL, attribute4, l_inv_attributes_tbl(4))
366 , attribute5 = DECODE(l_inv_attributes_tbl(5), g_miss_char, NULL, NULL, attribute5, l_inv_attributes_tbl(5))
367 , attribute6 = DECODE(l_inv_attributes_tbl(6), g_miss_char, NULL, NULL, attribute6, l_inv_attributes_tbl(6))
368 , attribute7 = DECODE(l_inv_attributes_tbl(7), g_miss_char, NULL, NULL, attribute7, l_inv_attributes_tbl(7))
369 , attribute8 = DECODE(l_inv_attributes_tbl(8), g_miss_char, NULL, NULL, attribute8, l_inv_attributes_tbl(8))
370 , attribute9 = DECODE(l_inv_attributes_tbl(9), g_miss_char, NULL, NULL, attribute9, l_inv_attributes_tbl(9))
371 , attribute10 = DECODE(l_inv_attributes_tbl(10), g_miss_char, NULL, NULL, attribute10, l_inv_attributes_tbl(10))
372 , attribute11 = DECODE(l_inv_attributes_tbl(11), g_miss_char, NULL, NULL, attribute11, l_inv_attributes_tbl(11))
373 , attribute12 = DECODE(l_inv_attributes_tbl(12), g_miss_char, NULL, NULL, attribute12, l_inv_attributes_tbl(12))
374 , attribute13 = DECODE(l_inv_attributes_tbl(13), g_miss_char, NULL, NULL, attribute13, l_inv_attributes_tbl(13))
375 , attribute14 = DECODE(l_inv_attributes_tbl(14), g_miss_char, NULL, NULL, attribute14, l_inv_attributes_tbl(14))
376 , attribute15 = DECODE(l_inv_attributes_tbl(15), g_miss_char, NULL, NULL, attribute15, l_inv_attributes_tbl(15))
377 WHERE inventory_item_id = p_inventory_item_id
378 AND serial_number = p_serial_number
379 AND current_status in (1,3,4); --Bug4493227
380
381 IF SQL%FOUND THEN
382 IF g_debug = 1 THEN
383 print_debug('Upd Serial Attr: Update successfully completed');
384 END IF;
385 ELSE
386 IF g_debug = 1 THEN
387 print_debug('Serial not found for update');
388 END IF;
389 END IF;
390
391 EXCEPTION
392 WHEN serial_not_found THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
395
396 WHEN errors_received THEN
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 error_msg := fnd_flex_descval.error_message;
399 s := 1;
400 e := 200;
401
402 print_debug('Here are the error messages: ');
403 WHILE e < 5001
404 AND SUBSTR(error_msg, s, e) IS NOT NULL LOOP
405 fnd_message.set_name('INV', 'INV_FND_GENERIC_MSG');
406 fnd_message.set_token('MSG', SUBSTR(error_msg, s, e));
407 fnd_msg_pub.ADD;
408 print_debug(SUBSTR(error_msg, s, e));
409 s := s + 200;
410 e := e + 200;
411 END LOOP;
412
413 ROLLBACK TO get_serial_attr_information;
414 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
415 WHEN FND_API.G_EXC_ERROR THEN
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 ROLLBACK TO get_serial_attr_information;
418 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 ROLLBACK TO get_serial_attr_information;
422 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
423 WHEN OTHERS THEN
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
425 ROLLBACK TO get_serial_attr_information;
426 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
427 print_debug('Error ' || SQLERRM);
428 END Update_Serial_number_attr;
429
430 END INV_SERIAL_NUMBER_ATTR;