[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_ATTRIBS_PVT
Source
1 PACKAGE BODY csi_t_txn_attribs_pvt as
2 /* $Header: csivteab.pls 120.1 2005/09/26 15:14:46 shegde noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_attribs_pvt';
5 g_file_name CONSTANT VARCHAR2(12) := 'csivteab.pls';
6
7 g_user_id NUMBER := fnd_global.user_id;
8 g_login_id NUMBER := fnd_global.login_id;
9
10
11 PROCEDURE debug(
12 p_message IN varchar2)
13 IS
14 BEGIN
15 csi_t_gen_utility_pvt.add(p_message);
16 END debug;
17
18 PROCEDURE api_log(
19 p_api_name IN varchar2)
20 IS
21 BEGIN
22 csi_t_gen_utility_pvt.dump_api_info(
23 p_pkg_name => 'csi_t_txn_attribs_pvt',
24 p_api_name => p_api_name);
25 END api_log;
26
27 PROCEDURE create_txn_ext_attrib_dtls(
28 p_api_version IN number,
29 p_commit IN varchar2 := fnd_api.g_false,
30 p_init_msg_list IN varchar2 := fnd_api.g_false,
31 p_validation_level IN number := fnd_api.g_valid_level_full,
32 p_txn_ext_attrib_vals_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_rec,
33 x_return_status OUT NOCOPY varchar2,
34 x_msg_count OUT NOCOPY number,
35 x_msg_data OUT NOCOPY varchar2)
36
37 IS
38 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_ext_attrib_dtls';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_debug_level NUMBER;
41 l_return_status VARCHAR2(1);
42 l_preserve_detail_flag VARCHAR2(1);
43 l_process_flag VARCHAR2(1);
44 l_txn_attrib_detail_id NUMBER;
45 x_attribute_id NUMBER ;
46 x_error_msg VARCHAR2(2000);
47
48 BEGIN
49
50 -- Standard Start of API savepoint
51 SAVEPOINT create_txn_ext_attrib_dtls;
52
53 -- Initialize message list if p_init_msg_list is set to TRUE.
54 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
55 fnd_msg_pub.initialize;
56 END IF;
57
58 -- Initialize API return status to success
59 x_return_status := fnd_api.G_RET_STS_SUCCESS;
60
61 -- Standard call to check for call compatibility.
62 IF NOT
63
64 fnd_api.Compatible_API_Call (
65 p_current_version_number => l_api_version,
66 p_caller_version_number => p_api_version,
67 p_api_name => l_api_name,
68 p_pkg_name => G_PKG_NAME) THEN
69
70 RAISE fnd_api.g_exc_unexpected_error;
71
72 END IF;
73
74 -- Check the profile option debug_level for debug message reporting
75
76 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
77
78 csi_t_gen_utility_pvt.dump_api_info(
79 p_pkg_name => g_pkg_name,
80 p_api_name => l_api_name);
81
82 IF l_debug_level > 1 THEN
83
84 csi_t_gen_utility_pvt.add(
85 p_api_version||'-'||p_commit||'-'||p_init_msg_list||'-'||p_validation_level);
86
87 csi_t_gen_utility_pvt.dump_txn_eav_rec(
88 p_txn_eav_rec => p_txn_ext_attrib_vals_rec);
89
90 END IF;
91
92 -- Added for CZ Integration (Begin)
93 IF NVL(p_txn_ext_attrib_vals_rec.attribute_source_id,fnd_api.g_miss_num)
94 = fnd_api.g_miss_num
95 THEN
96 IF p_txn_ext_attrib_vals_rec.api_caller_identity <> 'CONFIG'
97 THEN
98 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
99 FND_MESSAGE.set_token('API_CALLER',p_txn_ext_attrib_vals_rec.api_caller_identity) ;
100 FND_MSG_PUB.add;
101 RAISE FND_API.g_exc_error;
102 END IF ;
103 -- get the attrib_source_id
104 IF NVL(p_txn_ext_attrib_vals_rec.attribute_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
105 AND NVL(p_txn_ext_attrib_vals_rec.attribute_level, fnd_api.g_miss_char) <> fnd_api.g_miss_char
106 THEN
107 ---Both the attribs are given
108 get_ext_attrib_id ( p_txn_ext_attrib_vals_rec.attribute_code
109 ,p_txn_ext_attrib_vals_rec.attribute_level
110 ,p_txn_ext_attrib_vals_rec.txn_line_detail_id
111 ,x_attribute_id
112 ,p_txn_ext_attrib_vals_rec.attrib_source_table
113 ,x_return_status
114 ,x_error_msg );
115
116 debug ('After calling get_ext_attrib_id : attrib id - attrib_source_table :'|| x_attribute_id || ' - '||p_txn_ext_attrib_vals_rec.attrib_source_table);
117 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
118 THEN
119 debug ('Call to get_ext_attrib_id failed ..');
120 FND_MESSAGE.set_token('ATTRIB_LEVEL',p_txn_ext_attrib_vals_rec.attribute_level);
121 fnd_msg_pub.add;
122 RAISE fnd_api.g_exc_error;
123 END IF ;
124 ELSIF NVL(p_txn_ext_attrib_vals_rec.attribute_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
125 AND NVL(p_txn_ext_attrib_vals_rec.attribute_level, fnd_api.g_miss_char) = fnd_api.g_miss_char
126 THEN
127 ---Attrib level is NOT passed
128 ---First trywith different attrib levels
129 debug ('Attribute level is not passed ');
130 FOR i IN 1..4
131 LOOP
132 IF i=1
133 THEN
134 p_txn_ext_attrib_vals_rec.attribute_level := 'GLOBAL' ;
135 ELSIF i=2
136 THEN
137 p_txn_ext_attrib_vals_rec.attribute_level := 'CATEGORY' ;
138 ELSIF i=3
139 THEN
140 p_txn_ext_attrib_vals_rec.attribute_level := 'ITEM' ;
141 ELSIF i=4
142 THEN
143 p_txn_ext_attrib_vals_rec.attribute_level := 'INSTANCE' ;
144 END IF ;
145 get_ext_attrib_id ( p_txn_ext_attrib_vals_rec.attribute_code
146 ,p_txn_ext_attrib_vals_rec.attribute_level
147 ,p_txn_ext_attrib_vals_rec.txn_line_detail_id
148 ,x_attribute_id
149 ,p_txn_ext_attrib_vals_rec.attrib_source_table
150 ,x_return_status
151 ,x_error_msg );
152
153 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
154 THEN
155 IF i=4 THEN
156 debug ('Call to get_ext_attrib_id failed ..');
157 FND_MESSAGE.set_token('ATTRIB_LEVEL','');
158 fnd_msg_pub.add;
159 RAISE fnd_api.g_exc_error;
160 END IF;
161 END IF ;
162
163 IF NVL(x_attribute_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
164 THEN
165 ---Attribute ID is derived
166 ---Exit from this loop we don't need to go further
167 EXIT ;
168 END IF ;
169 END LOOP ;
170
171 debug ('After calling get_ext_attrib_id : attrib id - attrib_source_table :'|| x_attribute_id || ' - '||p_txn_ext_attrib_vals_rec.attrib_source_table);
172 ELSE
173 ---Attrib code is NOT passed.
174 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
175 fnd_message.set_token('MESSAGE',
176 'No Attribute code is passed ');
177 fnd_msg_pub.add;
178 debug ( 'Attribute Code is null so raise the error :');
179 RAISE fnd_api.g_exc_error;
180 END IF ;
181 debug ( 'checking x_attribute_id :'|| x_attribute_id) ;
182 IF NVL(x_attribute_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
183 THEN
184 p_txn_ext_attrib_vals_rec.attribute_source_id := x_attribute_id ;
185 ELSE
186 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
187 fnd_message.set_token('MESSAGE',
188 'Either no Attribute code is passed OR csi_t_extend_attribs_pkg.get_attrib_id failed');
189 fnd_msg_pub.add;
190 debug ( 'Attribute id is null so raise the error :');
191 RAISE fnd_api.g_exc_error;
192 END IF ;
193 END IF ; --(p_txn_ext_attrib_vals_rec.attrib_source_id is not passed
194 -- Added for CZ Integration (End)
195
196
197 -- Main API code
198 csi_t_vldn_routines_pvt.check_reqd_param(
199 p_value => p_txn_ext_attrib_vals_rec.txn_line_detail_id,
200 p_param_name => 'p_txn_ext_attrib_vals_rec.txn_line_detail_id',
201 p_api_name => l_api_name);
202
203 csi_t_vldn_routines_pvt.check_reqd_param(
204 p_value => p_txn_ext_attrib_vals_rec.attrib_source_table,
205 p_param_name => 'p_txn_ext_attrib_vals_rec.attrib_source_table',
206 p_api_name => l_api_name);
207
208 csi_t_vldn_routines_pvt.check_reqd_param(
209 p_value => p_txn_ext_attrib_vals_rec.attribute_source_id,
210 p_param_name => 'p_txn_ext_attrib_vals_rec.attribute_source_id',
211 p_api_name => l_api_name);
212
213 -- validate attrib_source_id
214 csi_t_vldn_routines_pvt.validate_attrib_source_id(
215 p_attrib_source_table => p_txn_ext_attrib_vals_rec.attrib_source_table,
216 p_attrib_source_id => p_txn_ext_attrib_vals_rec.attribute_source_id,
217 x_return_status => l_return_status);
218
219 IF l_return_status <> fnd_api.g_ret_sts_success THEN
220 RAISE fnd_api.g_exc_error;
221 END IF;
222
223 -- defaulting the preserve detail flag
224
225 SELECT decode( nvl(p_txn_ext_attrib_vals_rec.preserve_detail_flag,fnd_api.g_miss_char),
226 fnd_api.g_miss_char, 'Y', p_txn_ext_attrib_vals_rec.preserve_detail_flag)
227 INTO l_preserve_detail_flag
228 FROM sys.dual;
229
230 -- defaulting the process_flag
231 IF nvl(p_txn_ext_attrib_vals_rec.attribute_value, fnd_api.g_miss_char) = fnd_api.g_miss_char
232 THEN
233 l_process_flag := 'N';
234 ELSE
235 l_process_flag := p_txn_ext_attrib_vals_rec.process_flag;
236 END IF;
237
238 IF nvl(p_txn_ext_attrib_vals_rec.txn_attrib_detail_id,fnd_api.g_miss_num) <>
239 fnd_api.g_miss_num THEN
240 l_txn_attrib_detail_id := p_txn_ext_attrib_vals_rec.txn_attrib_detail_id;
241 END IF;
242
243 --debug info
244 BEGIN
245
246 csi_t_gen_utility_pvt.dump_api_info(
247 p_api_name => 'insert_row',
248 p_pkg_name => 'csi_t_extend_attribs_pkg');
249
250 csi_t_extend_attribs_pkg.insert_row(
251 px_txn_attrib_detail_id => l_txn_attrib_detail_id,
252 p_txn_line_detail_id => p_txn_ext_attrib_vals_rec.txn_line_detail_id,
253 p_attrib_source_id => p_txn_ext_attrib_vals_rec.attribute_source_id,
254 p_attrib_source_table => p_txn_ext_attrib_vals_rec.attrib_source_table,
255 p_attribute_value => p_txn_ext_attrib_vals_rec.attribute_value,
256 p_process_flag => l_process_flag,
257 p_active_start_date => p_txn_ext_attrib_vals_rec.active_start_date,
258 p_active_end_date => p_txn_ext_attrib_vals_rec.active_end_date,
259 p_preserve_detail_flag => l_preserve_detail_flag,
260 p_attribute1 => p_txn_ext_attrib_vals_rec.attribute1,
261 p_attribute2 => p_txn_ext_attrib_vals_rec.attribute2,
262 p_attribute3 => p_txn_ext_attrib_vals_rec.attribute3,
263 p_attribute4 => p_txn_ext_attrib_vals_rec.attribute4,
264 p_attribute5 => p_txn_ext_attrib_vals_rec.attribute5,
265 p_attribute6 => p_txn_ext_attrib_vals_rec.attribute6,
266 p_attribute7 => p_txn_ext_attrib_vals_rec.attribute7,
267 p_attribute8 => p_txn_ext_attrib_vals_rec.attribute8,
268 p_attribute9 => p_txn_ext_attrib_vals_rec.attribute9,
269 p_attribute10 => p_txn_ext_attrib_vals_rec.attribute10,
270 p_attribute11 => p_txn_ext_attrib_vals_rec.attribute11,
271 p_attribute12 => p_txn_ext_attrib_vals_rec.attribute12,
272 p_attribute13 => p_txn_ext_attrib_vals_rec.attribute13,
273 p_attribute14 => p_txn_ext_attrib_vals_rec.attribute14,
274 p_attribute15 => p_txn_ext_attrib_vals_rec.attribute15,
275 p_created_by => g_user_id,
276 p_creation_date => sysdate,
277 p_last_updated_by => g_user_id,
278 p_last_update_date => sysdate,
279 p_last_update_login => g_login_id,
280 p_object_version_number => 1.0,
281 p_context => p_txn_ext_attrib_vals_rec.context);
282
283 EXCEPTION
284 WHEN others THEN
285 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
286 fnd_message.set_token('MESSAGE',
287 'csi_t_extend_attribs_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
288 fnd_msg_pub.add;
289 RAISE fnd_api.g_exc_error;
290 END;
291
292 p_txn_ext_attrib_vals_rec.txn_attrib_detail_id := l_txn_attrib_detail_id;
293
294 -- Standard check of p_commit.
295 IF fnd_api.To_Boolean( p_commit ) THEN
296 COMMIT WORK;
297 END IF;
298
299 IF csi_t_gen_utility_pvt.g_debug = fnd_api.g_true THEN
300 csi_t_gen_utility_pvt.set_debug_off;
301 END IF;
302
303 -- Standard call to get message count and if count is get message info.
304 fnd_msg_pub.count_and_get(
305 p_count => x_msg_count,
306 p_data => x_msg_data);
307
308 EXCEPTION
309 WHEN fnd_api.g_exc_error THEN
310
311 ROLLBACK TO create_txn_ext_attrib_dtls;
312 x_return_status := fnd_api.g_ret_sts_error ;
313 fnd_msg_pub.count_and_get (
314 p_count => x_msg_count,
315 p_data => x_msg_data);
316
317 WHEN fnd_api.g_exc_unexpected_error THEN
318
319 ROLLBACK TO create_txn_ext_attrib_dtls;
320 x_return_status := fnd_api.g_ret_sts_unexp_error ;
321
322 fnd_msg_pub.count_and_get(
323 p_count => x_msg_count,
324 p_data => x_msg_data);
325
326 WHEN OTHERS THEN
327
328 ROLLBACK TO create_txn_ext_attrib_dtls;
329 x_return_status := fnd_api.g_ret_sts_unexp_error ;
330
331 IF fnd_msg_pub.Check_Msg_Level(
332 p_message_level => fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
333
334 fnd_msg_pub.Add_Exc_Msg(
335 p_pkg_name => G_PKG_NAME,
336 p_procedure_name => l_api_name);
337
338 END IF;
339
340 fnd_msg_pub.count_and_get(
341 p_count => x_msg_count,
342 p_data => x_msg_data);
343
344 END create_txn_ext_attrib_dtls;
345
346 PROCEDURE update_txn_ext_attrib_dtls(
347 p_api_version IN number,
348 p_commit IN varchar2 := fnd_api.g_false,
349 p_init_msg_list IN varchar2 := fnd_api.g_false,
350 p_validation_level IN number := fnd_api.g_valid_level_full,
351 p_txn_ext_attrib_vals_tbl IN csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
352 x_return_status OUT NOCOPY varchar2,
353 x_msg_count OUT NOCOPY number,
354 x_msg_data OUT NOCOPY varchar2)
355 IS
356
357 l_ea_rec csi_t_datastructures_grp.txn_ext_attrib_vals_rec;
358 l_ext_att_id number;
359 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
360
361 l_api_name CONSTANT varchar2(30) := 'update_txn_ext_attrib_dtls';
362 l_api_version CONSTANT number := 1.0;
363 l_debug_level number;
364 l_return_status varchar2(1);
365 l_txn_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl ;
366 l_update_ext_attribs BOOLEAN ;
367 l_attribute_id NUMBER ;
368 l_error_msg VARCHAR2(2000);
369
370 CURSOR l_ea_cur(p_attrib_dtl_id in number) IS
371 SELECT *
372 FROM csi_t_extend_attribs
373 where txn_attrib_detail_id = p_attrib_dtl_id;
374
375 CURSOR ext_attribs_cur (c_txn_line_detail_id IN NUMBER,
376 c_attrib_source_id IN NUMBER,
377 c_attrib_source_table IN VARCHAR2)
378 IS
379 SELECT txn_attrib_detail_id
380 FROM csi_t_extend_attribs
381 WHERE txn_line_detail_id = c_txn_line_detail_id
382 AND attrib_source_id = c_attrib_source_id
383 AND attrib_source_table = c_attrib_source_table ;
384
385 BEGIN
386
387 -- Standard Start of API savepoint
388 SAVEPOINT update_txn_ext_attrib_dtls;
389
390 -- Initialize message list if p_init_msg_list is set to TRUE.
391 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
392 fnd_msg_pub.initialize;
393 END IF;
394
395 -- Initialize API return status to success
396 x_return_status := fnd_api.G_RET_STS_SUCCESS;
397
398 -- Standard call to check for call compatibility.
399 IF NOT
400
401 fnd_api.Compatible_API_Call (
402 p_current_version_number => l_api_version,
403 p_caller_version_number => p_api_version,
404 p_api_name => l_api_name,
405 p_pkg_name => g_pkg_name) THEN
406
407 RAISE fnd_api.g_exc_unexpected_error;
408
409 END IF;
410
411 -- Check the profile option debug_level for debug message reporting
412 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
413
414 csi_t_gen_utility_pvt.dump_api_info(
415 p_pkg_name => g_pkg_name,
416 p_api_name => l_api_name);
417
418 csi_t_gen_utility_pvt.add(
419 p_api_version||'-'||p_commit||'-'||p_init_msg_list||'-'||p_validation_level);
420 ---FOR CZ
421 l_txn_ext_attrib_vals_tbl := p_txn_ext_attrib_vals_tbl ;
422 ---FOR CZ end
423
424 -- Main API code
425
426 IF l_txn_ext_attrib_vals_tbl.COUNT > 0 THEN
427 FOR l_ind in l_txn_ext_attrib_vals_tbl.FIRST..l_txn_ext_attrib_vals_tbl.LAST
428 LOOP
429 IF l_debug_level > 1 THEN
430 csi_t_gen_utility_pvt.dump_txn_eav_rec(
431 p_txn_eav_rec => l_txn_ext_attrib_vals_tbl(l_ind));
432 END IF;
433
434 -- Added for CZ Integration (Begin)
435 IF NVL(l_txn_ext_attrib_vals_tbl(l_ind).attribute_source_id,
436 fnd_api.g_miss_num) = fnd_api.g_miss_num
437 THEN
438 IF l_txn_ext_attrib_vals_tbl(l_ind).api_caller_identity <> 'CONFIG'
439 THEN
440 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
441 FND_MESSAGE.set_token('API_CALLER',l_txn_ext_attrib_vals_tbl(l_ind).api_caller_identity) ;
442 FND_MSG_PUB.add;
443 RAISE FND_API.g_exc_error;
444 END IF ;
445 -- get the attrib_source_id
446 IF NVL(l_txn_ext_attrib_vals_tbl(l_ind).attribute_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
447 AND NVL(l_txn_ext_attrib_vals_tbl(l_ind).attribute_level, fnd_api.g_miss_char) <> fnd_api.g_miss_char
448 THEN
449 ---Both the attribs are given
450 get_ext_attrib_id ( l_txn_ext_attrib_vals_tbl(l_ind).attribute_code
451 ,l_txn_ext_attrib_vals_tbl(l_ind).attribute_level
452 ,l_txn_ext_attrib_vals_tbl(l_ind).txn_line_detail_id
453 ,l_attribute_id
454 ,l_txn_ext_attrib_vals_tbl(l_ind).attrib_source_table
455 ,x_return_status
456 ,l_error_msg );
457
458 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
459 THEN
460 debug ('Call to get_ext_attrib_id failed ..');
461 FND_MESSAGE.set_token('ATTRIB_LEVEL',l_txn_ext_attrib_vals_tbl(l_ind).attribute_level);
462 fnd_msg_pub.add;
463 RAISE fnd_api.g_exc_error;
464 END IF ;
465 ELSIF NVL(l_txn_ext_attrib_vals_tbl(l_ind).attribute_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
466 AND NVL(l_txn_ext_attrib_vals_tbl(l_ind).attribute_level, fnd_api.g_miss_char) = fnd_api.g_miss_char
467 THEN
468 ---Attrib level is NOT passed
469 ---First trywith different attrib levels
470 debug ('Attribute level is not passed ');
471 FOR i IN 1..4
472 LOOP
473 IF i=1
474 THEN
475 l_txn_ext_attrib_vals_tbl(l_ind).attribute_level := 'GLOBAL' ;
476 ELSIF i=2
477 THEN
478 l_txn_ext_attrib_vals_tbl(l_ind).attribute_level := 'CATEGORY' ;
479 ELSIF i=3
480 THEN
481 l_txn_ext_attrib_vals_tbl(l_ind).attribute_level := 'ITEM' ;
482 ELSIF i=4
483 THEN
484 l_txn_ext_attrib_vals_tbl(l_ind).attribute_level := 'INSTANCE' ;
485 END IF ;
486
487 get_ext_attrib_id ( l_txn_ext_attrib_vals_tbl(l_ind).attribute_code
488 ,l_txn_ext_attrib_vals_tbl(l_ind).attribute_level
489 ,l_txn_ext_attrib_vals_tbl(l_ind).txn_line_detail_id
490 ,l_attribute_id
491 ,l_txn_ext_attrib_vals_tbl(l_ind).attrib_source_table
492 ,x_return_status
493 ,l_error_msg );
494
495 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
496 THEN
497 IF i=4 THEN
498 debug ('Call to get_ext_attrib_id failed ..');
499 FND_MESSAGE.set_token('ATTRIB_LEVEL','');
500 fnd_msg_pub.add;
501 RAISE fnd_api.g_exc_error;
502 END IF;
503 END IF ;
504
505 debug ( 'l_attribute_id :'|| l_attribute_id) ;
506 IF NVL(l_attribute_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
507 THEN
508 ---Attribute ID is derived
509 ---Exit from this loop we dont need to go further
510 EXIT ;
511 END IF ;
512 END LOOP ; --i IN 1..4
513 ELSE
514 ---Attrib code is NOT passed.
515 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
516 fnd_message.set_token('MESSAGE',
517 'No attribute code is passed ');
518 fnd_msg_pub.add;
519 debug ( 'Attribute id is null so raise the error :');
520 RAISE fnd_api.g_exc_error;
521 END IF ;
522 IF NVL(l_attribute_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
523 THEN
524 l_txn_ext_attrib_vals_tbl(l_ind).attribute_source_id := l_attribute_id ;
525 ELSE
526 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
527 fnd_message.set_token('MESSAGE',
528 'csi_t_extend_attribs_pkg.get_attrib_id failed');
529 fnd_msg_pub.add;
530 debug ( 'Attribute id is null so raise the error :');
531 RAISE fnd_api.g_exc_error;
532 END IF ;
533 END IF ; --l_txn_ext_attrib_vals_tbl(l_ind).source_id
534 -- Added for CZ Integration (End)
535
536 l_ext_att_id := l_txn_ext_attrib_vals_tbl(l_ind).txn_attrib_detail_id;
537
538 csi_t_vldn_routines_pvt.check_reqd_param(
539 p_value => l_ext_att_id,
540 p_param_name => 'l_txn_ext_attrib_vals_tbl.txn_attrib_detail_id',
541 p_api_name => l_api_name);
542
543 -- validate txn_attrib_detail_id
544 csi_t_vldn_routines_pvt.validate_txn_attrib_detail_id(
545 p_txn_attrib_detail_id => l_ext_att_id,
546 x_return_status => l_return_status);
547
548 IF l_return_status <> fnd_api.g_ret_sts_success THEN
549
550 FND_MESSAGE.set_name('CSI','CSI_TXN_EXT_ATTRIB_ID_INVALID');
551 FND_MESSAGE.set_token('EXT_ATT_ID',l_ext_att_id);
552 fnd_msg_pub.add;
553 RAISE fnd_api.g_exc_error;
554
555 END IF;
556
557 l_ea_rec.txn_attrib_detail_id := l_ext_att_id;
558
559 FOR l_ea_cur_rec IN l_ea_cur(l_ext_att_id)
560 LOOP
561
562 l_ea_rec.txn_line_detail_id := l_ea_cur_rec.txn_line_detail_id;
563
564 csi_t_vldn_routines_pvt.get_processing_status(
565 p_level => 'EXT_ATTRIB',
566 p_level_dtl_id => l_ea_rec.txn_line_detail_id,
567 x_processing_status => l_processing_status,
568 x_return_status => l_return_status);
569
570 IF l_processing_status = 'PROCESSED' THEN
571
572 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
573 FND_MESSAGE.set_token('LVL_ID', l_ext_att_id);
574 FND_MESSAGE.set_token('STATUS',l_processing_status);
575 fnd_msg_pub.add;
576 RAISE fnd_api.g_exc_error;
577
578 END IF;
579
580 IF nvl(l_txn_ext_attrib_vals_tbl(l_ind).attribute_source_id, fnd_api.g_miss_num) <>
581 fnd_api.g_miss_num
582 THEN
583
584 csi_t_vldn_routines_pvt.validate_attrib_source_id(
585 p_attrib_source_table => l_txn_ext_attrib_vals_tbl(l_ind).attrib_source_table,
586 p_attrib_source_id => l_txn_ext_attrib_vals_tbl(l_ind).attribute_source_id,
587 x_return_status => l_return_status);
588
589 IF l_return_status <> fnd_api.g_ret_sts_success THEN
590 raise fnd_api.g_exc_error;
591 END IF;
592
593 END IF;
594
595 l_ea_rec.attribute_source_id :=
596 nvl(l_txn_ext_attrib_vals_tbl(l_ind).attribute_source_id, fnd_api.g_miss_num);
597
598 l_ea_rec.attrib_source_table :=
599 nvl(l_txn_ext_attrib_vals_tbl(l_ind).attrib_source_table, fnd_api.g_miss_char);
600
601 l_ea_rec.attribute_value :=
602 l_txn_ext_attrib_vals_tbl(l_ind).attribute_value;
603
604 IF nvl(l_ea_rec.attribute_value, fnd_api.g_miss_char) = fnd_api.g_miss_char
605 AND
606 l_ea_cur_rec.attribute_value = NULL
607 THEN
608 l_ea_rec.process_flag := 'N';
609 ELSE
610 l_ea_rec.process_flag := l_txn_ext_attrib_vals_tbl(l_ind).process_flag;
611 END IF;
612
613 l_ea_rec.active_start_date :=
614 l_txn_ext_attrib_vals_tbl(l_ind).active_start_date;
615
616 l_ea_rec.active_end_date :=
617 l_txn_ext_attrib_vals_tbl(l_ind).active_end_date ;
618
619 l_ea_rec.preserve_detail_flag :=
620 l_txn_ext_attrib_vals_tbl(l_ind).preserve_detail_flag;
621
622 l_ea_rec.attribute1 :=
623 l_txn_ext_attrib_vals_tbl(l_ind).attribute1 ;
624
625 l_ea_rec.attribute2 :=
626 l_txn_ext_attrib_vals_tbl(l_ind).attribute2 ;
627
628 l_ea_rec.attribute3 :=
629 l_txn_ext_attrib_vals_tbl(l_ind).attribute3 ;
630
631 l_ea_rec.attribute4 :=
632 l_txn_ext_attrib_vals_tbl(l_ind).attribute4 ;
633
634 l_ea_rec.attribute5 :=
635 l_txn_ext_attrib_vals_tbl(l_ind).attribute5 ;
636
637 l_ea_rec.attribute6 :=
638 l_txn_ext_attrib_vals_tbl(l_ind).attribute6 ;
639
640 l_ea_rec.attribute7 :=
641 l_txn_ext_attrib_vals_tbl(l_ind).attribute7 ;
642
643 l_ea_rec.attribute8 :=
644 l_txn_ext_attrib_vals_tbl(l_ind).attribute8 ;
645
646 l_ea_rec.attribute9 :=
647 l_txn_ext_attrib_vals_tbl(l_ind).attribute9 ;
648
649 l_ea_rec.attribute10 :=
650 l_txn_ext_attrib_vals_tbl(l_ind).attribute10 ;
651
652 l_ea_rec.attribute11 :=
653 l_txn_ext_attrib_vals_tbl(l_ind).attribute11 ;
654
655 l_ea_rec.attribute12 :=
656 l_txn_ext_attrib_vals_tbl(l_ind).attribute12 ;
657
658 l_ea_rec.attribute13 :=
659 l_txn_ext_attrib_vals_tbl(l_ind).attribute13 ;
660
661 l_ea_rec.attribute14 :=
662 l_txn_ext_attrib_vals_tbl(l_ind).attribute14 ;
663
664 l_ea_rec.attribute15 :=
665 l_txn_ext_attrib_vals_tbl(l_ind).attribute15 ;
666
667 l_ea_rec.object_version_number:=
668 l_txn_ext_attrib_vals_tbl(l_ind).object_version_number ;
669
670 l_ea_rec.context :=
671 l_txn_ext_attrib_vals_tbl(l_ind).context ;
672
673 begin
674
675 csi_t_gen_utility_pvt.dump_api_info(
676 p_api_name => 'update_row',
677 p_pkg_name => 'csi_t_extend_attribs_pkg');
678
679 csi_t_extend_attribs_pkg.update_row(
680 p_txn_attrib_detail_id => l_ea_rec.txn_attrib_detail_id,
681 p_txn_line_detail_id => l_ea_rec.txn_line_detail_id,
682 p_attrib_source_id => l_ea_rec.attribute_source_id,
683 p_attrib_source_table => l_ea_rec.attrib_source_table,
684 p_attribute_value => l_ea_rec.attribute_value,
685 p_process_flag => l_ea_rec.process_flag,
686 p_active_start_date => l_ea_rec.active_start_date,
687 p_active_end_date => l_ea_rec.active_end_date,
688 p_preserve_detail_flag => l_ea_rec.preserve_detail_flag,
689 p_attribute1 => l_ea_rec.attribute1 ,
690 p_attribute2 => l_ea_rec.attribute2 ,
691 p_attribute3 => l_ea_rec.attribute3 ,
692 p_attribute4 => l_ea_rec.attribute4 ,
693 p_attribute5 => l_ea_rec.attribute5 ,
694 p_attribute6 => l_ea_rec.attribute6 ,
695 p_attribute7 => l_ea_rec.attribute7 ,
696 p_attribute8 => l_ea_rec.attribute8 ,
697 p_attribute9 => l_ea_rec.attribute9 ,
698 p_attribute10 => l_ea_rec.attribute10 ,
699 p_attribute11 => l_ea_rec.attribute11 ,
700 p_attribute12 => l_ea_rec.attribute12 ,
701 p_attribute13 => l_ea_rec.attribute13 ,
702 p_attribute14 => l_ea_rec.attribute14 ,
703 p_attribute15 => l_ea_rec.attribute15 ,
704 p_created_by => fnd_api.g_miss_num,
705 p_creation_date => fnd_api.g_miss_date,
706 p_last_updated_by => g_user_id,
707 p_last_update_date => sysdate,
708 p_last_update_login => g_login_id,
709 p_object_version_number => l_ea_rec.object_version_number,
710 p_context => l_ea_rec.context);
711
712 exception
713 when others then
714 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
715 fnd_message.set_token('MESSAGE',
716 'csi_t_extend_attribs_pkg.update_row Failed. '||substr(sqlerrm,1,200));
717 fnd_msg_pub.add;
718 raise fnd_api.g_exc_error;
719 end;
720
721 END LOOP;
722 END LOOP;
723
724 END IF;
725 -- Standard check of p_commit.
726 IF fnd_api.To_Boolean( p_commit ) THEN
727 COMMIT WORK;
728 END IF;
729 -- Standard call to get message count and if count is get message info.
730 fnd_msg_pub.count_and_get(
731 p_count => x_msg_count,
732 p_data => x_msg_data);
733
734 EXCEPTION
735 WHEN fnd_api.g_exc_error THEN
736
737 ROLLBACK TO update_txn_ext_attrib_dtls;
738 x_return_status := fnd_api.g_ret_sts_error ;
739 fnd_msg_pub.count_and_get (
740 p_count => x_msg_count,
741 p_data => x_msg_data);
742
743 WHEN fnd_api.g_exc_unexpected_error THEN
744
745 ROLLBACK TO update_txn_ext_attrib_dtls;
746 x_return_status := fnd_api.g_ret_sts_unexp_error ;
747
748 fnd_msg_pub.count_and_get(
749 p_count => x_msg_count,
750 p_data => x_msg_data);
751
752 WHEN OTHERS THEN
753
754 ROLLBACK TO update_txn_ext_attrib_dtls;
755 x_return_status := fnd_api.g_ret_sts_unexp_error ;
756
757 IF fnd_msg_pub.Check_Msg_Level(
758 p_message_level => fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
759
760 fnd_msg_pub.Add_Exc_Msg(
761 p_pkg_name => G_PKG_NAME,
762 p_procedure_name => l_api_name);
763
764 END IF;
765
766 fnd_msg_pub.count_and_get(
767 p_count => x_msg_count,
768 p_data => x_msg_data);
769
770 END update_txn_ext_attrib_dtls;
771
772 PROCEDURE delete_txn_ext_attrib_dtls
773 (
774 p_api_version IN NUMBER
775 ,p_commit IN VARCHAR2 := fnd_api.g_false
776 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
777 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
778 ,p_txn_ext_attrib_ids_tbl IN csi_t_datastructures_grp.txn_ext_attrib_ids_tbl
779 ,x_return_status OUT NOCOPY VARCHAR2
780 ,x_msg_count OUT NOCOPY NUMBER
781 ,x_msg_data OUT NOCOPY VARCHAR2
782 )
783 IS
784
785 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_ext_attrib_dtls';
786 l_api_version CONSTANT NUMBER := 1.0;
787 l_debug_level NUMBER;
788
789 l_return_status VARCHAR2(1);
790 l_ext_att_id NUMBER;
791
792 l_line_dtl_id NUMBER;
793
794 CURSOR ea_cur (p_line_dtl_id IN NUMBER) IS
795 SELECT txn_attrib_detail_id
796 FROM csi_t_extend_attribs
797 WHERE txn_line_detail_id = p_line_dtl_id;
798
799 BEGIN
800
801 -- Standard Start of API savepoint
802 SAVEPOINT delete_txn_ext_attrib_dtls;
803
804 -- Initialize message list if p_init_msg_list is set to TRUE.
805 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
806 fnd_msg_pub.initialize;
807 END IF;
808
809 -- Initialize API return status to success
810 x_return_status := fnd_api.G_RET_STS_SUCCESS;
811
812 -- Standard call to check for call compatibility.
813 IF NOT
814
815 fnd_api.Compatible_API_Call (
816 p_current_version_number => l_api_version,
817 p_caller_version_number => p_api_version,
818 p_api_name => l_api_name,
819 p_pkg_name => G_PKG_NAME) THEN
820
821 RAISE fnd_api.g_exc_unexpected_error;
822
823 END IF;
824
825 -- Check the profile option debug_level for debug message reporting
826 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
827
828 csi_t_gen_utility_pvt.dump_api_info(
829 p_pkg_name => g_pkg_name,
830 p_api_name => l_api_name);
831
832 csi_t_gen_utility_pvt.add(
833 p_api_version||'-'||p_commit||'-'||p_init_msg_list||'-'||p_validation_level);
834
835 -- Main API code
836 IF p_txn_ext_attrib_ids_tbl.count > 0 THEN
837 FOR l_ind in p_txn_ext_attrib_ids_tbl.FIRST..p_txn_ext_attrib_ids_tbl.LAST
838 LOOP
839
840 IF l_debug_level > 1 THEN
841 null; --##
842 END IF;
843
844 l_ext_att_id := p_txn_ext_attrib_ids_tbl(l_ind).txn_attrib_detail_id;
845
846 IF nvl(l_ext_att_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
847
848 -- validate txn_attrib_detail_id
849 csi_t_vldn_routines_pvt.validate_txn_attrib_detail_id(
850 p_txn_attrib_detail_id => l_ext_att_id,
851 x_return_status => l_return_status);
852
853 IF l_return_status <> fnd_api.g_ret_sts_success THEN
854
855 FND_MESSAGE.set_name('CSI','CSI_TXN_EXT_ATTRIB_ID_INVALID');
856 FND_MESSAGE.set_token('EXT_ATT_ID',l_ext_att_id);
857 fnd_msg_pub.add;
858 RAISE fnd_api.g_exc_error;
859
860 END IF;
861
862 csi_t_gen_utility_pvt.dump_api_info(
863 p_api_name => 'delete_row',
864 p_pkg_name => 'csi_t_extend_attribs_pkg');
865
866 csi_t_extend_attribs_pkg.delete_row(
867 p_txn_attrib_detail_id => l_ext_att_id);
868
869 ELSE
870
871 l_line_dtl_id := p_txn_ext_attrib_ids_tbl(l_ind).txn_line_detail_id;
872
873 csi_t_vldn_routines_pvt.check_reqd_param(
874 p_value => l_line_dtl_id,
875 p_param_name => 'p_txn_ext_attrib_ids_tbl.txn_line_detail_id',
876 p_api_name => l_api_name);
877
878 -- validate txn_line_detail_id
879 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
880 p_txn_line_detail_id => l_line_dtl_id,
881 x_return_status => l_return_status);
882
883 IF l_return_status <> fnd_api.g_ret_sts_success THEN
884
885 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_DTL_ID_INVALID');
886 FND_MESSAGE.set_token('LINE_DTL_ID',l_line_dtl_id);
887 fnd_msg_pub.add;
888 RAISE fnd_api.g_exc_error;
889
890 END IF;
891
892 FOR ea_rec in ea_cur (l_line_dtl_id)
893 LOOP
894
895 csi_t_gen_utility_pvt.dump_api_info(
896 p_api_name => 'delete_row',
897 p_pkg_name => 'csi_t_extend_attribs_pkg');
898
899 csi_t_extend_attribs_pkg.delete_row(
900 p_txn_attrib_detail_id => ea_rec.txn_attrib_detail_id);
901
902 END LOOP;
903
904 END IF;
905
906 END LOOP;
907
908 END IF;
909
910 -- Standard check of p_commit.
911 IF fnd_api.To_Boolean( p_commit ) THEN
912 COMMIT WORK;
913 END IF;
914
915 -- Standard call to get message count and if count is get message info.
916 fnd_msg_pub.count_and_get(
917 p_count => x_msg_count,
918 p_data => x_msg_data);
919
920 EXCEPTION
921 WHEN fnd_api.g_exc_error THEN
922
923 ROLLBACK TO delete_txn_ext_attrib_dtls;
924 x_return_status := fnd_api.g_ret_sts_error ;
925 fnd_msg_pub.count_and_get (
926 p_count => x_msg_count,
927 p_data => x_msg_data);
928
929 WHEN fnd_api.g_exc_unexpected_error THEN
930
931 ROLLBACK TO delete_txn_ext_attrib_dtls;
932 x_return_status := fnd_api.g_ret_sts_unexp_error ;
933
934 fnd_msg_pub.count_and_get(
935 p_count => x_msg_count,
936 p_data => x_msg_data);
937
938 WHEN OTHERS THEN
939
940 ROLLBACK TO delete_txn_ext_attrib_dtls;
941 x_return_status := fnd_api.g_ret_sts_unexp_error ;
942
943 IF fnd_msg_pub.check_msg_level(
944 p_message_level => fnd_msg_pub.g_msg_lvl_unexp_error) THEN
945
946 fnd_msg_pub.add_exc_msg(
947 p_pkg_name => g_pkg_name,
948 p_procedure_name => l_api_name);
949
950 END IF;
951
952 fnd_msg_pub.count_and_get(
953 p_count => x_msg_count,
954 p_data => x_msg_data);
955
956 END delete_txn_ext_attrib_dtls;
957
958 PROCEDURE get_csi_ext_attrib_vals(
959 p_instance_id in number,
960 x_csi_ea_vals_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attrib_vals_tbl,
961 x_return_status OUT NOCOPY varchar2)
962 IS
963
964 l_select_stmt varchar2(2000);
965 l_iea_cur_id integer;
966 l_iea_rec csi_t_datastructures_grp.csi_ext_attrib_vals_rec;
967 l_processed_rows number := 0;
968 l_ind binary_integer;
969
970 BEGIN
971
972 l_select_stmt :=
973 'select attribute_value_id, instance_id,attribute_id, attribute_value,'||
974 ' active_start_date, active_end_date, context, attribute1, attribute2,'||
975 ' attribute3, attribute4, attribute5, attribute6, attribute7,'||
976 ' attribute8, attribute9, attribute10, attribute11, attribute12,'||
977 ' attribute13, attribute14, attribute15, object_version_number '||
978 'from csi_iea_values '||
979 'where instance_id = :instance_id';
980
981 l_iea_cur_id := dbms_sql.open_cursor;
982
983 dbms_sql.parse(l_iea_cur_id, l_select_stmt , dbms_sql.native);
984
985 dbms_sql.bind_variable(l_iea_cur_id, 'instance_id', p_instance_id);
986
987 dbms_sql.define_column(l_iea_cur_id,1,l_iea_rec.attribute_value_id);
988 dbms_sql.define_column(l_iea_cur_id,2,l_iea_rec.instance_id);
989 dbms_sql.define_column(l_iea_cur_id,3,l_iea_rec.attribute_id);
990 dbms_sql.define_column(l_iea_cur_id,4,l_iea_rec.attribute_value,240);
991 dbms_sql.define_column(l_iea_cur_id,5,l_iea_rec.active_start_date);
992 dbms_sql.define_column(l_iea_cur_id,6,l_iea_rec.active_end_date);
993 dbms_sql.define_column(l_iea_cur_id,7,l_iea_rec.context,30);
994 dbms_sql.define_column(l_iea_cur_id,8,l_iea_rec.attribute1,150);
995 dbms_sql.define_column(l_iea_cur_id,9,l_iea_rec.attribute2,150);
996 dbms_sql.define_column(l_iea_cur_id,10,l_iea_rec.attribute3,150);
997 dbms_sql.define_column(l_iea_cur_id,11,l_iea_rec.attribute4,150);
998 dbms_sql.define_column(l_iea_cur_id,12,l_iea_rec.attribute5,150);
999 dbms_sql.define_column(l_iea_cur_id,13,l_iea_rec.attribute6,150);
1000 dbms_sql.define_column(l_iea_cur_id,14,l_iea_rec.attribute7,150);
1001 dbms_sql.define_column(l_iea_cur_id,15,l_iea_rec.attribute8,150);
1002 dbms_sql.define_column(l_iea_cur_id,16,l_iea_rec.attribute9,150);
1003 dbms_sql.define_column(l_iea_cur_id,17,l_iea_rec.attribute10,150);
1004 dbms_sql.define_column(l_iea_cur_id,18,l_iea_rec.attribute11,150);
1005 dbms_sql.define_column(l_iea_cur_id,19,l_iea_rec.attribute12,150);
1006 dbms_sql.define_column(l_iea_cur_id,20,l_iea_rec.attribute13,150);
1007 dbms_sql.define_column(l_iea_cur_id,21,l_iea_rec.attribute14,150);
1008 dbms_sql.define_column(l_iea_cur_id,22,l_iea_rec.attribute15,150);
1009 dbms_sql.define_column(l_iea_cur_id,23,l_iea_rec.object_version_number);
1010
1011 l_ind := 0;
1012
1013 l_processed_rows := dbms_sql.execute(l_iea_cur_id);
1014 LOOP
1015
1016 exit when dbms_sql.fetch_rows(l_iea_cur_id) = 0;
1017
1018 l_ind := l_ind + 1;
1019 dbms_sql.column_value(l_iea_cur_id,1,x_csi_ea_vals_tbl(l_ind).attribute_value_id);
1020 dbms_sql.column_value(l_iea_cur_id,2,x_csi_ea_vals_tbl(l_ind).instance_id);
1021 dbms_sql.column_value(l_iea_cur_id,3,x_csi_ea_vals_tbl(l_ind).attribute_id);
1022 dbms_sql.column_value(l_iea_cur_id,4,x_csi_ea_vals_tbl(l_ind).attribute_value);
1023 dbms_sql.column_value(l_iea_cur_id,5,x_csi_ea_vals_tbl(l_ind).active_start_date);
1024 dbms_sql.column_value(l_iea_cur_id,6,x_csi_ea_vals_tbl(l_ind).active_end_date);
1025 dbms_sql.column_value(l_iea_cur_id,7,x_csi_ea_vals_tbl(l_ind).context);
1026 dbms_sql.column_value(l_iea_cur_id,8,x_csi_ea_vals_tbl(l_ind).attribute1);
1027 dbms_sql.column_value(l_iea_cur_id,9,x_csi_ea_vals_tbl(l_ind).attribute2);
1028 dbms_sql.column_value(l_iea_cur_id,10,x_csi_ea_vals_tbl(l_ind).attribute3);
1029 dbms_sql.column_value(l_iea_cur_id,11,x_csi_ea_vals_tbl(l_ind).attribute4);
1030 dbms_sql.column_value(l_iea_cur_id,12,x_csi_ea_vals_tbl(l_ind).attribute5);
1031 dbms_sql.column_value(l_iea_cur_id,13,x_csi_ea_vals_tbl(l_ind).attribute6);
1032 dbms_sql.column_value(l_iea_cur_id,14,x_csi_ea_vals_tbl(l_ind).attribute7);
1033 dbms_sql.column_value(l_iea_cur_id,15,x_csi_ea_vals_tbl(l_ind).attribute8);
1034 dbms_sql.column_value(l_iea_cur_id,16,x_csi_ea_vals_tbl(l_ind).attribute9);
1035 dbms_sql.column_value(l_iea_cur_id,17,x_csi_ea_vals_tbl(l_ind).attribute10);
1036 dbms_sql.column_value(l_iea_cur_id,18,x_csi_ea_vals_tbl(l_ind).attribute11);
1037 dbms_sql.column_value(l_iea_cur_id,19,x_csi_ea_vals_tbl(l_ind).attribute12);
1038 dbms_sql.column_value(l_iea_cur_id,20,x_csi_ea_vals_tbl(l_ind).attribute13);
1039 dbms_sql.column_value(l_iea_cur_id,21,x_csi_ea_vals_tbl(l_ind).attribute14);
1040 dbms_sql.column_value(l_iea_cur_id,22,x_csi_ea_vals_tbl(l_ind).attribute15);
1041 dbms_sql.column_value(l_iea_cur_id,23,x_csi_ea_vals_tbl(l_ind).object_version_number);
1042
1043 END LOOP;
1044
1045 dbms_sql.close_cursor(l_iea_cur_id);
1046
1047 EXCEPTION
1048 WHEN others THEN
1049
1050 IF dbms_sql.is_open(l_iea_cur_id) THEN
1051 dbms_sql.close_cursor(l_iea_cur_id);
1052 END IF;
1053 END get_csi_ext_attrib_vals;
1054
1055 PROCEDURE get_csi_ext_attribs(
1056 p_line_dtl_id in number,
1057 p_instance_id in number,
1058 x_csi_ext_attribs_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attribs_tbl,
1059 x_return_status OUT NOCOPY varchar2)
1060 IS
1061
1062 l_select_stmt varchar2(2000);
1063 l_inst_based_stmt varchar2(1000);
1064 l_ea_cur_id integer;
1065 l_ea_rec csi_t_datastructures_grp.csi_ext_attribs_rec;
1066 l_processed_rows number := 0;
1067 l_ind binary_integer;
1068
1069 BEGIN
1070
1071 x_return_status := fnd_api.g_ret_sts_success;
1072
1073 l_select_stmt :=
1074 'select attribute_id, attribute_level, master_organization_id,'||
1075 ' inventory_item_id, item_category_id, instance_id, attribute_code,'||
1076 ' attribute_name, attribute_category, description, active_start_date,'||
1077 ' active_end_date, context, attribute1, attribute2, attribute3,'||
1078 ' attribute4, attribute5, attribute6, attribute7, attribute8,'||
1079 ' attribute9, attribute10, attribute11, attribute12, attribute13,'||
1080 ' attribute14, attribute15, object_version_number '||
1081 'from csi_i_extended_attribs '||
1082 'where attribute_level = ''GLOBAL'' '||
1083 'union '||
1084 'select attribute_id, attribute_level, master_organization_id,'||
1085 ' inventory_item_id, item_category_id, instance_id, attribute_code,'||
1086 ' attribute_name, attribute_category, description, active_start_date,'||
1087 ' active_end_date, context, attribute1, attribute2, attribute3,'||
1088 ' attribute4, attribute5, attribute6, attribute7, attribute8,'||
1089 ' attribute9, attribute10, attribute11, attribute12, attribute13,'||
1090 ' attribute14, attribute15, object_version_number '||
1091 'from csi_i_extended_attribs '||
1092 'where (inventory_item_id, master_organization_id) in '||
1093 ' (select inventory_item_id,inv_organization_id '||
1094 ' from csi_t_txn_line_details'||
1095 ' where txn_line_detail_id = :line_dtl_id '||
1096 ' and instance_exists_flag = ''N'') ';
1097
1098 l_inst_based_stmt :=
1099 'union '||
1100 'select attribute_id, attribute_level, master_organization_id,'||
1101 ' inventory_item_id, item_category_id, instance_id, attribute_code,'||
1102 ' attribute_name, attribute_category, description, active_start_date,'||
1103 ' active_end_date, context, attribute1, attribute2, attribute3,'||
1104 ' attribute4, attribute5, attribute6, attribute7, attribute8,'||
1105 ' attribute9, attribute10, attribute11, attribute12, attribute13,'||
1106 ' attribute14, attribute15, object_version_number '||
1107 'from csi_i_extended_attribs '||
1108 'where instance_id = :instance_id';
1109
1110 IF p_instance_id is not null THEN
1111 l_select_stmt := l_select_stmt||l_inst_based_stmt;
1112 END IF;
1113 l_ea_cur_id := dbms_sql.open_cursor;
1114
1115 dbms_sql.parse(l_ea_cur_id, l_select_stmt , dbms_sql.native);
1116
1117 dbms_sql.bind_variable(l_ea_cur_id,'line_dtl_id',p_line_dtl_id);
1118
1119 IF p_instance_id is not null THEN
1120 dbms_sql.bind_variable(l_ea_cur_id,'instance_id',p_instance_id);
1121 END IF;
1122
1123
1124 dbms_sql.define_column(l_ea_cur_id,1,l_ea_rec.attribute_id);
1125 dbms_sql.define_column(l_ea_cur_id,2,l_ea_rec.attribute_level,15);
1126 dbms_sql.define_column(l_ea_cur_id,3,l_ea_rec.master_organization_id);
1127 dbms_sql.define_column(l_ea_cur_id,4,l_ea_rec.inventory_item_id);
1128 dbms_sql.define_column(l_ea_cur_id,5,l_ea_rec.item_category_id);
1129 dbms_sql.define_column(l_ea_cur_id,6,l_ea_rec.instance_id);
1130 dbms_sql.define_column(l_ea_cur_id,7,l_ea_rec.attribute_code,30);
1131 dbms_sql.define_column(l_ea_cur_id,8,l_ea_rec.attribute_name,50);
1132 dbms_sql.define_column(l_ea_cur_id,9,l_ea_rec.attribute_category,30);
1133 dbms_sql.define_column(l_ea_cur_id,10,l_ea_rec.description,240);
1134 dbms_sql.define_column(l_ea_cur_id,11,l_ea_rec.active_start_date);
1135 dbms_sql.define_column(l_ea_cur_id,12,l_ea_rec.active_end_date);
1136 dbms_sql.define_column(l_ea_cur_id,13,l_ea_rec.context,30);
1137 dbms_sql.define_column(l_ea_cur_id,14,l_ea_rec.attribute1,150);
1138 dbms_sql.define_column(l_ea_cur_id,15,l_ea_rec.attribute2,150);
1139 dbms_sql.define_column(l_ea_cur_id,16,l_ea_rec.attribute3,150);
1140 dbms_sql.define_column(l_ea_cur_id,17,l_ea_rec.attribute4,150);
1141 dbms_sql.define_column(l_ea_cur_id,18,l_ea_rec.attribute5,150);
1142 dbms_sql.define_column(l_ea_cur_id,19,l_ea_rec.attribute6,150);
1143 dbms_sql.define_column(l_ea_cur_id,20,l_ea_rec.attribute7,150);
1144 dbms_sql.define_column(l_ea_cur_id,21,l_ea_rec.attribute8,150);
1145 dbms_sql.define_column(l_ea_cur_id,22,l_ea_rec.attribute9,150);
1146 dbms_sql.define_column(l_ea_cur_id,23,l_ea_rec.attribute10,150);
1147 dbms_sql.define_column(l_ea_cur_id,24,l_ea_rec.attribute11,150);
1148 dbms_sql.define_column(l_ea_cur_id,25,l_ea_rec.attribute12,150);
1149 dbms_sql.define_column(l_ea_cur_id,26,l_ea_rec.attribute13,150);
1150 dbms_sql.define_column(l_ea_cur_id,27,l_ea_rec.attribute14,150);
1151 dbms_sql.define_column(l_ea_cur_id,28,l_ea_rec.attribute15,150);
1152 dbms_sql.define_column(l_ea_cur_id,29,l_ea_rec.object_version_number);
1153
1154 l_ind := 0;
1155
1156 l_processed_rows := dbms_sql.execute(l_ea_cur_id);
1157 LOOP
1158
1159 exit when dbms_sql.fetch_rows(l_ea_cur_id) = 0;
1160
1161 l_ind := l_ind + 1;
1162
1163 dbms_sql.column_value(l_ea_cur_id,1,x_csi_ext_attribs_tbl(l_ind).attribute_id);
1164 dbms_sql.column_value(l_ea_cur_id,2,x_csi_ext_attribs_tbl(l_ind).attribute_level);
1165 dbms_sql.column_value(l_ea_cur_id,3,x_csi_ext_attribs_tbl(l_ind).master_organization_id);
1166 dbms_sql.column_value(l_ea_cur_id,4,x_csi_ext_attribs_tbl(l_ind).inventory_item_id);
1167 dbms_sql.column_value(l_ea_cur_id,5,x_csi_ext_attribs_tbl(l_ind).item_category_id);
1168 dbms_sql.column_value(l_ea_cur_id,6,x_csi_ext_attribs_tbl(l_ind).instance_id);
1169 dbms_sql.column_value(l_ea_cur_id,7,x_csi_ext_attribs_tbl(l_ind).attribute_code);
1170 dbms_sql.column_value(l_ea_cur_id,8,x_csi_ext_attribs_tbl(l_ind).attribute_name);
1171 dbms_sql.column_value(l_ea_cur_id,9,x_csi_ext_attribs_tbl(l_ind).attribute_category);
1172 dbms_sql.column_value(l_ea_cur_id,10,x_csi_ext_attribs_tbl(l_ind).description);
1173 dbms_sql.column_value(l_ea_cur_id,11,x_csi_ext_attribs_tbl(l_ind).active_start_date);
1174 dbms_sql.column_value(l_ea_cur_id,12,x_csi_ext_attribs_tbl(l_ind).active_end_date);
1175 dbms_sql.column_value(l_ea_cur_id,13,x_csi_ext_attribs_tbl(l_ind).context);
1176 dbms_sql.column_value(l_ea_cur_id,14,x_csi_ext_attribs_tbl(l_ind).attribute1);
1177 dbms_sql.column_value(l_ea_cur_id,15,x_csi_ext_attribs_tbl(l_ind).attribute2);
1178 dbms_sql.column_value(l_ea_cur_id,16,x_csi_ext_attribs_tbl(l_ind).attribute3);
1179 dbms_sql.column_value(l_ea_cur_id,17,x_csi_ext_attribs_tbl(l_ind).attribute4);
1180 dbms_sql.column_value(l_ea_cur_id,18,x_csi_ext_attribs_tbl(l_ind).attribute5);
1181 dbms_sql.column_value(l_ea_cur_id,19,x_csi_ext_attribs_tbl(l_ind).attribute6);
1182 dbms_sql.column_value(l_ea_cur_id,20,x_csi_ext_attribs_tbl(l_ind).attribute7);
1183 dbms_sql.column_value(l_ea_cur_id,21,x_csi_ext_attribs_tbl(l_ind).attribute8);
1184 dbms_sql.column_value(l_ea_cur_id,22,x_csi_ext_attribs_tbl(l_ind).attribute9);
1185 dbms_sql.column_value(l_ea_cur_id,23,x_csi_ext_attribs_tbl(l_ind).attribute10);
1186 dbms_sql.column_value(l_ea_cur_id,24,x_csi_ext_attribs_tbl(l_ind).attribute11);
1187 dbms_sql.column_value(l_ea_cur_id,25,x_csi_ext_attribs_tbl(l_ind).attribute12);
1188 dbms_sql.column_value(l_ea_cur_id,26,x_csi_ext_attribs_tbl(l_ind).attribute13);
1189 dbms_sql.column_value(l_ea_cur_id,27,x_csi_ext_attribs_tbl(l_ind).attribute14);
1190 dbms_sql.column_value(l_ea_cur_id,28,x_csi_ext_attribs_tbl(l_ind).attribute15);
1191 dbms_sql.column_value(l_ea_cur_id,29,x_csi_ext_attribs_tbl(l_ind).object_version_number);
1192
1193 END LOOP;
1194
1195 dbms_sql.close_cursor(l_ea_cur_id);
1196
1197 EXCEPTION
1198 WHEN others THEN
1199
1200 IF dbms_sql.is_open(l_ea_cur_id) THEN
1201 dbms_sql.close_cursor(l_ea_cur_id);
1202 END IF;
1203 END get_csi_ext_attribs;
1204
1205 PROCEDURE get_ext_attrib_dtls(
1206 p_line_dtl_id in number,
1207 x_ext_attrib_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
1208 x_return_status OUT NOCOPY varchar2)
1209 IS
1210
1211 l_select_stmt varchar2(2000);
1212 l_ea_cur_id integer;
1213 l_ea_rec csi_t_datastructures_grp.txn_ext_attrib_vals_rec;
1214 l_processed_rows number := 0;
1215 l_ind binary_integer;
1216
1217 BEGIN
1218
1219 l_select_stmt :=
1220 'select txn_attrib_detail_id, txn_line_detail_id, attrib_source_table,
1221 attrib_source_id, attribute_value, process_flag,
1222 active_start_date, active_end_date, preserve_detail_flag,
1223 context, attribute1, attribute2, attribute3, attribute4,
1224 attribute5, attribute6, attribute7, attribute8, attribute9,
1225 attribute10, attribute11, attribute12, attribute13, attribute14,
1226 attribute15, object_version_number
1227 from csi_t_extend_attribs
1228 where txn_line_detail_id = :line_dtl_id';
1229
1230 l_ea_cur_id := dbms_sql.open_cursor;
1231
1232 dbms_sql.parse(l_ea_cur_id, l_select_stmt , dbms_sql.native);
1233
1234 dbms_sql.bind_variable(l_ea_cur_id,'line_dtl_id', p_line_dtl_id);
1235
1236 dbms_sql.define_column(l_ea_cur_id,1,l_ea_rec.txn_attrib_detail_id);
1237 dbms_sql.define_column(l_ea_cur_id,2,l_ea_rec.txn_line_detail_id);
1238 dbms_sql.define_column(l_ea_cur_id,3,l_ea_rec.attrib_source_table,30);
1239 dbms_sql.define_column(l_ea_cur_id,4,l_ea_rec.attribute_source_id);
1240 dbms_sql.define_column(l_ea_cur_id,5,l_ea_rec.attribute_value,240);
1241 dbms_sql.define_column(l_ea_cur_id,6,l_ea_rec.process_flag,1);
1242 dbms_sql.define_column(l_ea_cur_id,7,l_ea_rec.active_start_date);
1243 dbms_sql.define_column(l_ea_cur_id,8,l_ea_rec.active_end_date);
1244 dbms_sql.define_column(l_ea_cur_id,9,l_ea_rec.preserve_detail_flag,1);
1245 dbms_sql.define_column(l_ea_cur_id,10,l_ea_rec.context,30);
1246 dbms_sql.define_column(l_ea_cur_id,11,l_ea_rec.attribute1,150);
1247 dbms_sql.define_column(l_ea_cur_id,12,l_ea_rec.attribute2,150);
1248 dbms_sql.define_column(l_ea_cur_id,13,l_ea_rec.attribute3,150);
1249 dbms_sql.define_column(l_ea_cur_id,14,l_ea_rec.attribute4,150);
1250 dbms_sql.define_column(l_ea_cur_id,15,l_ea_rec.attribute5,150);
1251 dbms_sql.define_column(l_ea_cur_id,16,l_ea_rec.attribute6,150);
1252 dbms_sql.define_column(l_ea_cur_id,17,l_ea_rec.attribute7,150);
1253 dbms_sql.define_column(l_ea_cur_id,18,l_ea_rec.attribute8,150);
1254 dbms_sql.define_column(l_ea_cur_id,19,l_ea_rec.attribute9,150);
1255 dbms_sql.define_column(l_ea_cur_id,20,l_ea_rec.attribute10,150);
1256 dbms_sql.define_column(l_ea_cur_id,21,l_ea_rec.attribute11,150);
1257 dbms_sql.define_column(l_ea_cur_id,22,l_ea_rec.attribute12,150);
1258 dbms_sql.define_column(l_ea_cur_id,23,l_ea_rec.attribute13,150);
1259 dbms_sql.define_column(l_ea_cur_id,24,l_ea_rec.attribute14,150);
1260 dbms_sql.define_column(l_ea_cur_id,25,l_ea_rec.attribute15,150);
1261 dbms_sql.define_column(l_ea_cur_id,26,l_ea_rec.object_version_number);
1262
1263 l_ind := 0;
1264
1265 l_processed_rows := dbms_sql.execute(l_ea_cur_id);
1266 LOOP
1267
1268 exit when dbms_sql.fetch_rows(l_ea_cur_id) = 0;
1269
1270 l_ind := l_ind + 1;
1271
1272 dbms_sql.column_value(l_ea_cur_id,1,x_ext_attrib_tbl(l_ind).txn_attrib_detail_id);
1273 dbms_sql.column_value(l_ea_cur_id,2,x_ext_attrib_tbl(l_ind).txn_line_detail_id);
1274 dbms_sql.column_value(l_ea_cur_id,3,x_ext_attrib_tbl(l_ind).attrib_source_table);
1275 dbms_sql.column_value(l_ea_cur_id,4,x_ext_attrib_tbl(l_ind).attribute_source_id);
1276 dbms_sql.column_value(l_ea_cur_id,5,x_ext_attrib_tbl(l_ind).attribute_value);
1277 dbms_sql.column_value(l_ea_cur_id,6,x_ext_attrib_tbl(l_ind).process_flag);
1278 dbms_sql.column_value(l_ea_cur_id,7,x_ext_attrib_tbl(l_ind).active_start_date);
1279 dbms_sql.column_value(l_ea_cur_id,8,x_ext_attrib_tbl(l_ind).active_end_date);
1280 dbms_sql.column_value(l_ea_cur_id,9,x_ext_attrib_tbl(l_ind).preserve_detail_flag);
1281 dbms_sql.column_value(l_ea_cur_id,10,x_ext_attrib_tbl(l_ind).context);
1282 dbms_sql.column_value(l_ea_cur_id,11,x_ext_attrib_tbl(l_ind).attribute1);
1283 dbms_sql.column_value(l_ea_cur_id,12,x_ext_attrib_tbl(l_ind).attribute2);
1284 dbms_sql.column_value(l_ea_cur_id,13,x_ext_attrib_tbl(l_ind).attribute3);
1285 dbms_sql.column_value(l_ea_cur_id,14,x_ext_attrib_tbl(l_ind).attribute4);
1286 dbms_sql.column_value(l_ea_cur_id,15,x_ext_attrib_tbl(l_ind).attribute5);
1287 dbms_sql.column_value(l_ea_cur_id,16,x_ext_attrib_tbl(l_ind).attribute6);
1288 dbms_sql.column_value(l_ea_cur_id,17,x_ext_attrib_tbl(l_ind).attribute7);
1289 dbms_sql.column_value(l_ea_cur_id,18,x_ext_attrib_tbl(l_ind).attribute8);
1290 dbms_sql.column_value(l_ea_cur_id,19,x_ext_attrib_tbl(l_ind).attribute9);
1291 dbms_sql.column_value(l_ea_cur_id,20,x_ext_attrib_tbl(l_ind).attribute10);
1292 dbms_sql.column_value(l_ea_cur_id,21,x_ext_attrib_tbl(l_ind).attribute11);
1293 dbms_sql.column_value(l_ea_cur_id,22,x_ext_attrib_tbl(l_ind).attribute12);
1294 dbms_sql.column_value(l_ea_cur_id,23,x_ext_attrib_tbl(l_ind).attribute13);
1295 dbms_sql.column_value(l_ea_cur_id,24,x_ext_attrib_tbl(l_ind).attribute14);
1296 dbms_sql.column_value(l_ea_cur_id,25,x_ext_attrib_tbl(l_ind).attribute15);
1297 dbms_sql.column_value(l_ea_cur_id,26,x_ext_attrib_tbl(l_ind).object_version_number);
1298
1299 END LOOP;
1300
1301 dbms_sql.close_cursor(l_ea_cur_id);
1302
1303 EXCEPTION
1304 WHEN others THEN
1305
1306 IF dbms_sql.is_open(l_ea_cur_id) THEN
1307 dbms_sql.close_cursor(l_ea_cur_id);
1308 END IF;
1309 END get_ext_attrib_dtls;
1310
1311 PROCEDURE get_all_csi_ext_attrib_vals(
1312 p_txn_line_detail_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
1313 x_csi_ea_vals_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attrib_vals_tbl,
1314 x_return_status OUT NOCOPY varchar2)
1315 IS
1316 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1317 l_ceav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
1318 l_tmp_ceav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
1319 l_c_ind binary_integer := 0;
1320
1321 BEGIN
1322 x_return_status := fnd_api.g_ret_sts_success;
1323 api_log('get_all_csi_ext_attrib_vals');
1324
1325 IF p_txn_line_detail_tbl.COUNT > 0 THEN
1326 FOR l_ind IN p_txn_line_detail_tbl.FIRST .. p_txn_line_detail_tbl.LAST
1327 LOOP
1328
1329 IF nvl(p_txn_line_detail_tbl(l_ind).instance_id, fnd_api.g_miss_num) <>
1330 fnd_api.g_miss_num
1331 THEN
1332
1333 l_tmp_ceav_tbl.delete;
1334
1335 get_csi_ext_attrib_vals(
1336 p_instance_id => p_txn_line_detail_tbl(l_ind).instance_id,
1337 x_csi_ea_vals_tbl => l_tmp_ceav_tbl,
1338 x_return_status => l_return_status);
1339
1340 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1341 raise fnd_api.g_exc_error;
1342 END IF;
1343
1344 IF l_tmp_ceav_tbl.count > 0 THEN
1345 FOR l_t_ind IN l_tmp_ceav_tbl.FIRST .. l_tmp_ceav_tbl.LAST
1346 LOOP
1347 l_c_ind := l_ceav_tbl.COUNT;
1348 l_ceav_tbl(l_c_ind) := l_tmp_ceav_tbl(l_t_ind);
1349 END LOOP;
1350 END IF;
1351
1352 END IF;
1353
1354 END LOOP;
1355 END IF;
1356 x_csi_ea_vals_tbl := l_ceav_tbl;
1357
1358 EXCEPTION
1359 WHEN fnd_api.g_exc_error THEN
1360 x_return_status := fnd_api.g_ret_sts_error;
1361 END get_all_csi_ext_attrib_vals;
1362
1363 PROCEDURE get_all_csi_ext_attribs(
1364 p_txn_line_detail_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
1365 x_csi_ext_attribs_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attribs_tbl,
1366 x_return_status OUT NOCOPY varchar2)
1367 IS
1368
1369 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1370 l_cea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
1371 l_tmp_cea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
1372 l_c_ind binary_integer := 0;
1373 l_instance_id number := null;
1374
1375 BEGIN
1376 x_return_status := fnd_api.g_ret_sts_success;
1377 api_log('get_all_csi_ext_attribs');
1378
1379 IF p_txn_line_detail_tbl.count > 0 THEN
1380 FOR l_ind IN p_txn_line_detail_tbl.FIRST .. p_txn_line_detail_tbl.LAST
1381 LOOP
1382
1383 IF nvl(p_txn_line_detail_tbl(l_ind).instance_id, fnd_api.g_miss_num) =
1384 fnd_api.g_miss_num
1385 THEN
1386 l_instance_id := null;
1387 ELSE
1388 l_instance_id := p_txn_line_detail_tbl(l_ind).instance_id;
1389 END IF;
1390
1391 l_tmp_cea_tbl.DELETE;
1392
1393 get_csi_ext_attribs(
1394 p_line_dtl_id => p_txn_line_detail_tbl(l_ind).txn_line_detail_id,
1395 p_instance_id => l_instance_id,
1396 x_csi_ext_attribs_tbl => l_tmp_cea_tbl,
1397 x_return_status => l_return_status);
1398
1399 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1400 RAISE fnd_api.g_exc_error;
1401 END IF;
1402
1403 IF l_tmp_cea_tbl.COUNT > 0 THEN
1404 FOR l_t_ind IN l_tmp_cea_tbl.FIRST .. l_tmp_cea_tbl.LAST
1405 LOOP
1406 l_c_ind := l_cea_tbl.count + 1;
1407 l_cea_tbl(l_c_ind) := l_tmp_cea_tbl(l_t_ind);
1408 END LOOP;
1409 END IF;
1410
1411 END LOOP;
1412 END IF;
1413 x_csi_ext_attribs_tbl := l_cea_tbl;
1414
1415 EXCEPTION
1416 WHEN fnd_api.g_exc_error THEN
1417 x_return_status := fnd_api.g_ret_sts_error;
1418 END get_all_csi_ext_attribs;
1419
1420 PROCEDURE get_all_ext_attrib_dtls(
1421 p_txn_line_detail_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
1422 x_ext_attrib_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
1423 x_return_status OUT NOCOPY varchar2)
1424 IS
1425 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1426 l_c_ind binary_integer := 0;
1427 l_teav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
1428 l_tmp_teav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
1429
1430 BEGIN
1431 x_return_status := fnd_api.g_ret_sts_success;
1432 api_log('get_all_ext_attrib_dtls');
1433
1434 IF p_txn_line_detail_tbl.count > 0 THEN
1435 FOR l_ind IN p_txn_line_detail_tbl.FIRST .. p_txn_line_detail_tbl.LAST
1436 LOOP
1437
1438 l_tmp_teav_tbl.delete;
1439
1440 get_ext_attrib_dtls(
1441 p_line_dtl_id => p_txn_line_detail_tbl(l_ind).txn_line_detail_id,
1442 x_ext_attrib_tbl => l_tmp_teav_tbl,
1443 x_return_status => l_return_status);
1444
1445 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1446 raise fnd_api.g_exc_error;
1447 END IF;
1448 IF l_tmp_teav_tbl.count > 0 THEN
1449 FOR l_t_ind IN l_tmp_teav_tbl.FIRST .. l_tmp_teav_tbl.LAST
1450 LOOP
1451 l_c_ind := l_teav_tbl.count + 1;
1452 l_teav_tbl(l_c_ind) := l_tmp_teav_tbl(l_t_ind);
1453 END LOOP;
1454 END IF;
1455
1456 END LOOP;
1457 END IF;
1458
1459 x_ext_attrib_tbl := l_teav_tbl;
1460
1461 EXCEPTION
1462 WHEN fnd_api.g_exc_error THEN
1463 x_return_status := fnd_api.g_ret_sts_error;
1464 END get_all_ext_attrib_dtls;
1465
1466 -- Added for CZ Integration (Begin)
1467 PROCEDURE get_ext_attrib_id(
1468 p_attrib_code IN VARCHAR2 ,
1469 p_attrib_level IN VARCHAR2 ,
1470 p_txn_line_detail_id IN NUMBER ,
1471 x_attribute_id OUT NOCOPY NUMBER ,
1472 x_source_table OUT NOCOPY VARCHAR2,
1473 x_return_status OUT NOCOPY VARCHAR2,
1474 x_error_msg OUT NOCOPY VARCHAR2)
1475 IS
1476 l_sysdate DATE ;
1477 l_inv_item_id Number ;
1478 l_inv_orgn_id Number ;
1479 l_item_category Number ;
1480 l_attrib_param Varchar2(150);
1481
1482 CURSOR get_global_attrib_cur (c_attribute_code IN VARCHAR2)
1483 IS
1484 SELECT attribute_id
1485 FROM csi_i_extended_attribs a
1486 WHERE attribute_level = 'GLOBAL'
1487 AND attribute_code = c_attribute_code
1488 AND (a.active_end_date is NULL OR
1489 trunc(a.active_end_date) > l_sysdate) ;
1490
1491 CURSOR get_item_attrib_cur (c_attribute_code IN VARCHAR2)
1492 IS
1493 SELECT a.attribute_id, b.inventory_item_id, b.inv_organization_id
1494 FROM csi_i_extended_attribs a ,
1495 csi_t_txn_line_details b,
1496 mtl_parameters mtp -- Added for bug 4118180
1497 WHERE a.attribute_level = 'ITEM'
1498 AND a.attribute_code = c_attribute_code
1499 AND a.inventory_item_id = b.inventory_item_id
1500 AND b.inv_organization_id = mtp.organization_id -- Modified for bug 4118180
1501 AND a.master_organization_id = mtp.master_organization_id -- Modified for bug 4118180
1502 AND b.txn_line_detail_id = p_txn_line_detail_id
1503 AND (a.active_end_date is NULL OR
1504 trunc(a.active_end_date) > l_sysdate) ;
1505
1506 CURSOR get_instance_attrib_cur (c_attribute_code IN VARCHAR2 )
1507 IS
1508 --FOR non-CZ
1509 SELECT a.attribute_id, b.inventory_item_id, b.inv_organization_id
1510 , a.instance_id
1511 FROM csi_i_extended_attribs a,
1512 csi_t_txn_line_details b
1513 WHERE a.attribute_level = 'INSTANCE'
1514 AND a.attribute_code = c_attribute_code
1515 AND b.config_inst_hdr_id IS NULL
1516 AND b.txn_line_detail_id = p_txn_line_detail_id
1517 AND a.instance_id = b.instance_id
1518 AND (a.active_end_date is NULL OR
1519 trunc(a.active_end_date) > l_sysdate)
1520 UNION
1521 ---FOR CZ
1522 SELECT a.attribute_id, b.inventory_item_id, b.inv_organization_id
1523 , c.instance_id
1524 FROM csi_i_extended_attribs a,
1525 csi_t_txn_line_details b,
1526 csi_item_instances c
1527 WHERE a.attribute_level = 'INSTANCE'
1528 AND a.attribute_code = c_attribute_code
1529 AND b.config_inst_hdr_id IS NOT NULL
1530 AND b.config_inst_hdr_id = c.config_inst_hdr_id
1531 AND b.config_inst_rev_num = c.config_inst_rev_num
1532 AND b.config_inst_item_id = c.config_inst_item_id
1533 AND b.txn_line_detail_id = p_txn_line_detail_id
1534 AND (a.active_end_date is NULL OR
1535 trunc(a.active_end_date) > l_sysdate) ;
1536
1537 CURSOR get_category_attrib_cur (c_attribute_code IN VARCHAR2, c_category_set_id IN VARCHAR2 ) -- changes for 4028827
1538 IS
1539 SELECT csie.attribute_id, csie.item_category_id,
1540 ctld.inventory_item_id, ctld.inv_organization_id
1541 FROM mtl_item_categories mti,
1542 mtl_categories_b mtc ,
1543 --csi_install_parameters csii,
1544 csi_i_extended_attribs csie ,
1545 csi_t_txn_line_details ctld
1546 WHERE mti.organization_id = ctld.inv_organization_id -- Bug 4306650. Typo:inv_orgn and item_id swap
1547 AND mti.inventory_item_id = ctld.inventory_item_id -- Bug 4306650. Typo:inv_orgn and item_id swap
1548 AND mti.category_set_id = c_category_set_id -- csii.category_set_id
1549 AND mti.category_id = mtc.category_id
1550 AND (mtc.disable_date is NULL OR TRUNC(mtc.disable_date) > TRUNC(SYSDATE))
1551 AND csie.item_category_id = mti.category_id
1552 AND csie.attribute_level = 'CATEGORY'
1553 AND attribute_code = c_attribute_code
1554 AND ctld.txn_line_detail_id = p_txn_line_detail_id
1555 AND (csie.active_end_date is NULL OR
1556 trunc(csie.active_end_date) > l_sysdate) ;
1557
1558 CURSOR instance_id_cur
1559 IS
1560 SELECT instance_id
1561 FROM csi_t_txn_line_details
1562 WHERE txn_line_detail_id = p_txn_line_detail_id
1563 AND config_inst_hdr_id IS NULL
1564 UNION
1565 SELECT b.instance_id
1566 FROM csi_t_txn_line_details a,
1567 csi_item_instances b
1568 WHERE a.txn_line_detail_id = p_txn_line_detail_id
1569 AND a.config_inst_hdr_id IS NOT NULL
1570 AND a.config_inst_hdr_id = b.config_inst_hdr_id
1571 AND a.config_inst_rev_num = b.config_inst_rev_num
1572 AND a.config_inst_item_id = b.config_inst_item_id
1573 AND a.txn_line_detail_id = p_txn_line_detail_id
1574 AND (b.active_end_date is NULL OR
1575 trunc(b.active_end_date) > l_sysdate) ;
1576
1577 CURSOR iea_values_cur (c_instance_id IN NUMBER,
1578 c_attrib_source_id IN NUMBER)
1579 IS
1580 SELECT attribute_value_id
1581 FROM csi_iea_values
1582 WHERE instance_id = c_instance_id
1583 AND attribute_id = c_attrib_source_id
1584 AND (active_end_date is NULL OR
1585 trunc(active_end_date) > l_sysdate) ;
1586
1587 l_instance_id NUMBER ;
1588 l_attribute_value_id NUMBER ;
1589 l_loop_count NUMBER ;
1590 l_category_set_id csi_install_parameters.category_set_id%type;
1591
1592 BEGIN
1593 debug ('Begin : get_source_attribute_id -' ||p_attrib_code||' - '||
1594 p_attrib_level || ' - '|| p_txn_line_detail_id);
1595
1596 x_return_status := fnd_api.g_ret_sts_success ;
1597 x_attribute_id := NULL ;
1598 l_instance_id := NULL ;
1599 x_source_table := NULL ;
1600 l_attribute_value_id := NULL ;
1601 l_loop_count := 0;
1602
1603 SELECT TRUNC(SYSDATE) INTO l_sysdate FROM dual ;
1604
1605 IF p_attrib_level = 'GLOBAL'
1606 THEN
1607 FOR get_global_attrib_rec IN get_global_attrib_cur(p_attrib_code)
1608 LOOP
1609 x_attribute_id := get_global_attrib_rec.attribute_id ;
1610 l_loop_count := l_loop_count+1 ;
1611 END LOOP ;
1612 IF l_loop_count > 1
1613 THEN
1614 x_error_msg := 'Unable to derive attribute id .....';
1615 FND_MESSAGE.set_name('CSI','CSI_TXN_TOO_MANY_EXT_ATT');
1616 FND_MESSAGE.set_token('ATTRIB_CODE',p_attrib_code);
1617 FND_MESSAGE.set_token('ATTRIB_LEVEL',p_attrib_level);
1618 fnd_msg_pub.add;
1619 RAISE fnd_api.g_exc_error ;
1620 END IF ;
1621 ELSIF p_attrib_level = 'CATEGORY'
1622 THEN
1623 -- bug 4028827 , cursor optimization changes
1624 l_category_set_id := csi_datastructures_pub.g_install_param_rec.category_set_id;
1625 FOR get_category_attrib_rec IN get_category_attrib_cur(p_attrib_code, l_category_set_id)
1626 LOOP
1627 x_attribute_id := get_category_attrib_rec.attribute_id ;
1628 l_inv_item_id := get_category_attrib_rec.inventory_item_id ;
1629 l_inv_orgn_id := get_category_attrib_rec.inv_organization_id ;
1630 l_item_category := get_category_attrib_rec.item_category_id ;
1631 l_loop_count := l_loop_count+1 ;
1632 END LOOP ;
1633 IF l_loop_count > 1
1634 THEN
1635 x_error_msg := 'Unable to derive attribute id .....';
1636 FND_MESSAGE.set_name('CSI','CSI_TXN_TOO_MANY_EXT_ATT');
1637 FND_MESSAGE.set_token('ATTRIB_CODE',p_attrib_code);
1638 FND_MESSAGE.set_token('ATTRIB_LEVEL',p_attrib_level);
1639 RAISE fnd_api.g_exc_error ;
1640 END IF ;
1641 ELSIF p_attrib_level = 'ITEM'
1642 THEN
1643 FOR get_item_attrib_rec IN get_item_attrib_cur(p_attrib_code)
1644 LOOP
1645 x_attribute_id := get_item_attrib_rec.attribute_id ;
1646 l_inv_item_id := get_item_attrib_rec.inventory_item_id ;
1647 l_inv_orgn_id := get_item_attrib_rec.inv_organization_id ;
1648 l_loop_count := l_loop_count+1 ;
1649 END LOOP ;
1650 IF l_loop_count > 1
1651 THEN
1652 x_error_msg := 'Unable to derive attribute id .....';
1653 FND_MESSAGE.set_name('CSI','CSI_TXN_TOO_MANY_EXT_ATT');
1654 FND_MESSAGE.set_token('ATTRIB_CODE',p_attrib_code);
1655 FND_MESSAGE.set_token('ATTRIB_LEVEL',p_attrib_level);
1656 RAISE fnd_api.g_exc_error ;
1657 END IF ;
1658 ELSIF p_attrib_level = 'INSTANCE'
1659 THEN
1660 FOR get_instance_attrib_rec IN get_instance_attrib_cur(p_attrib_code)
1661 LOOP
1662 x_attribute_id := get_instance_attrib_rec.attribute_id ;
1663 l_instance_id := get_instance_attrib_rec.instance_id ;
1664 l_inv_item_id := get_instance_attrib_rec.inventory_item_id ;
1665 l_inv_orgn_id := get_instance_attrib_rec.inv_organization_id ;
1666 l_loop_count := l_loop_count+1 ;
1667 END LOOP ;
1668 IF l_loop_count > 1
1669 THEN
1670 x_error_msg := 'Unable to derive attribute id .....';
1671 FND_MESSAGE.set_name('CSI','CSI_TXN_TOO_MANY_EXT_ATT');
1672 FND_MESSAGE.set_token('ATTRIB_CODE',p_attrib_code);
1673 FND_MESSAGE.set_token('ATTRIB_LEVEL',p_attrib_level);
1674 RAISE fnd_api.g_exc_error ;
1675 END IF ;
1676 END IF ; --p_attrib_level
1677
1678 debug ('x_attribute_id :'|| x_attribute_id);
1679 IF x_attribute_id IS NOT NULL THEN
1680 OPEN instance_id_cur ;
1681 FETCH instance_id_cur INTO l_instance_id ;
1682 CLOSE instance_id_cur ;
1683
1684 debug ('l_instance_id :'|| l_instance_id);
1685
1686 l_loop_count := 0 ;
1687 IF l_instance_id IS NULL
1688 THEN
1689 x_source_table := 'CSI_I_EXTENDED_ATTRIBS' ;
1690 ELSE
1691 FOR iea_values_rec IN iea_values_cur(l_instance_id , x_attribute_id)
1692 LOOP
1693 l_attribute_value_id := iea_values_rec.attribute_value_id ;
1694 l_loop_count := l_loop_count+1 ;
1695 END LOOP ;
1696 IF l_loop_count > 1
1697 THEN
1698 FND_MESSAGE.set_name('CSI','CSI_TXN_TOO_MANY_EXT_ATT_VALS');
1699 FND_MESSAGE.set_token('ATTRIB_ID',x_attribute_id);
1700 FND_MESSAGE.set_token('INSTANCE_ID',l_instance_id);
1701 RAISE fnd_api.g_exc_error ;
1702 END IF ;
1703 IF l_attribute_value_id IS NOT NULL
1704 THEN
1705 x_source_table := 'CSI_IEA_VALUES' ;
1706 x_attribute_id := l_attribute_value_id ;
1707 ELSE
1708 x_source_table := 'CSI_I_EXTENDED_ATTRIBS' ;
1709 END IF ; --l_attribute_value_id
1710 END IF ; --l_instance_id
1711 ELSE
1712 l_attrib_param := 'Item ID:'||l_inv_item_id||' Orgn ID:'||l_inv_orgn_id||'Category ID: '||l_item_category||'Instance ID: '||l_instance_id;
1713 FND_MESSAGE.set_name('CSI','CSI_TXN_EXT_ATT_NOT_FOUND');
1714 FND_MESSAGE.set_token('ATTRIB_CODE',p_attrib_code);
1715 FND_MESSAGE.set_token('ATTRIB_PARAM',l_attrib_param);
1716 RAISE fnd_api.g_exc_error ;
1717 END IF ;
1718 EXCEPTION
1719 WHEN fnd_api.g_exc_error
1720 THEN
1721 x_attribute_id := NULL ;
1722 x_return_status := fnd_api.g_ret_sts_error;
1723 END get_ext_attrib_id ;
1724
1725 -- Added for CZ Integration (End)
1726
1727 END csi_t_txn_attribs_pvt;