[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_RLTNSHPS_PVT
Source
1 PACKAGE BODY csi_t_txn_rltnshps_pvt as
2 /* $Header: csivtiib.pls 115.19 2003/09/02 21:56:41 shegde ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_rltnshps_pvt';
5 g_file_name CONSTANT VARCHAR2(12) := 'csivtiib.pls';
6
7 g_user_id NUMBER := FND_GLOBAL.User_Id;
8 g_login_id NUMBER := FND_GLOBAL.Login_Id;
9
10 PROCEDURE create_txn_ii_rltns_dtls(
11 p_api_version IN NUMBER,
12 p_commit IN VARCHAR2 := fnd_api.g_false,
13 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
14 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
15 p_txn_ii_rltns_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_rec,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2)
19
20 IS
21
22 l_api_name CONSTANT VARCHAR2(30) := 'pvt.create_txn_ii_rltns_dtls';
23 l_api_version CONSTANT NUMBER := 1.0;
24 l_debug_level NUMBER;
25 l_txn_relationship_id NUMBER;
26 l_return_status VARCHAR2(1);
27 l_txn_line_dtl_rec1 csi_t_datastructures_grp.txn_line_detail_rec;
28 l_txn_line_dtl_rec2 csi_t_datastructures_grp.txn_line_detail_rec;
29 l_txn_line_dtl_g_miss csi_t_datastructures_grp.txn_line_detail_rec;
30 l_sub_instance_id NUMBER ;
31 l_sub_tld_id NUMBER ;
32 l_obj_instance_id NUMBER ;
33 l_obj_tld_id NUMBER ;
34
35 BEGIN
36
37 csi_t_gen_utility_pvt.add('Begin : '||l_api_name);
38 -- Standard Start of API savepoint
39 SAVEPOINT create_txn_ii_rltns_dtls;
40
41
42 -- Initialize message list if p_init_msg_list is set to TRUE.
43 IF FND_API.to_Boolean( p_init_msg_list ) THEN
44 FND_MSG_PUB.initialize;
45 END IF;
46
47 -- Initialize API return status to success
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 -- Standard call to check for call compatibility.
51 IF NOT
52
53 FND_API.Compatible_API_Call (
54 p_current_version_number => l_api_version,
55 p_caller_version_number => p_api_version,
56 p_api_name => l_api_name,
57 p_pkg_name => G_PKG_NAME) THEN
58
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60
61 END IF;
62
63 -- Check the profile option debug_level for debug message reporting
64
65 csi_t_gen_utility_pvt.dump_api_info(
66 p_pkg_name => g_pkg_name,
67 p_api_name => l_api_name);
68
69 csi_t_gen_utility_pvt.add(
70 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
71
72 -- Added for CZ Integration (Begin)
73
74 IF ( NVL(p_txn_ii_rltns_rec.sub_config_inst_hdr_id , fnd_api.g_miss_num)
75 <> fnd_api.g_miss_num
76 OR NVL(p_txn_ii_rltns_rec.obj_config_inst_hdr_id , fnd_api.g_miss_num)
77 <> fnd_api.g_miss_num )
78 AND p_txn_ii_rltns_rec.api_caller_identity <> 'CONFIG'
79 THEN
80 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
81 FND_MESSAGE.set_token('API_CALLER',p_txn_ii_rltns_rec.api_caller_identity) ;
82 FND_MSG_PUB.add;
83 RAISE FND_API.g_exc_error;
84 END IF ;
85
86 IF (NVL(p_txn_ii_rltns_rec.sub_config_inst_hdr_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num
87 AND NVL(p_txn_ii_rltns_rec.subject_id , fnd_api.g_miss_num)
88 = fnd_api.g_miss_num)
89 THEN
90 -- Now assuming that user is passing only config keys , get the associated instance/txn_detail_line.
91
92 csi_t_vldn_routines_pvt.get_cz_inst_or_tld_id (
93 p_config_inst_hdr_id => p_txn_ii_rltns_rec.sub_config_inst_hdr_id,
94 p_config_inst_rev_num => p_txn_ii_rltns_rec.sub_config_inst_rev_num,
95 p_config_inst_item_id => p_txn_ii_rltns_rec.sub_config_inst_item_id ,
96 x_instance_id => l_sub_instance_id ,
97 x_txn_line_detail_id => l_sub_tld_id ,
98 x_return_status => x_return_status );
99
100 IF x_return_status <> fnd_api.g_ret_sts_success
101 THEN
102 RAISE fnd_api.g_exc_error ;
103 END IF ;
104 IF l_sub_instance_id IS NULL
105 AND l_sub_tld_id IS NULL
106 THEN
107 RAISE fnd_api.g_exc_error ;
108 END IF ;
109
110 IF l_sub_instance_id IS NOT NULL
111 THEN
112 p_txn_ii_rltns_rec.subject_id := l_sub_instance_id;
113 p_txn_ii_rltns_rec.subject_type := 'I' ;
114 ELSIF l_sub_tld_id IS NOT NULL
115 THEN
116 p_txn_ii_rltns_rec.subject_id := l_sub_tld_id;
117 p_txn_ii_rltns_rec.subject_type := 'T' ;
118 END IF ;
119 -- get the display order for the subject
120 Begin
121 Select to_number(substr(cz.bom_sort_order,length(bom_sort_order)-3,4))
122 into p_txn_ii_rltns_rec.display_order
123 from cz_config_items_v cz
124 where cz.instance_hdr_id = p_txn_ii_rltns_rec.sub_config_inst_hdr_id
125 and cz.instance_rev_nbr = p_txn_ii_rltns_rec.sub_config_inst_rev_num
126 and cz.config_item_id = p_txn_ii_rltns_rec.sub_config_inst_item_id;
127 Exception when others then
128 Null;
129 End;
130
131 END IF ; --subject_id is null
132
133
134 IF (NVL(p_txn_ii_rltns_rec.obj_config_inst_hdr_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num
135 AND NVL(p_txn_ii_rltns_rec.object_id , fnd_api.g_miss_num)
136 = fnd_api.g_miss_num)
137 THEN
138 csi_t_vldn_routines_pvt.get_cz_inst_or_tld_id (
139 p_config_inst_hdr_id => p_txn_ii_rltns_rec.obj_config_inst_hdr_id,
140 p_config_inst_rev_num => p_txn_ii_rltns_rec.obj_config_inst_rev_num,
141 p_config_inst_item_id => p_txn_ii_rltns_rec.obj_config_inst_item_id ,
142 x_instance_id => l_obj_instance_id ,
143 x_txn_line_detail_id => l_obj_tld_id ,
144 x_return_status => x_return_status );
145
146 IF x_return_status <> fnd_api.g_ret_sts_success
147 THEN
148 RAISE fnd_api.g_exc_error ;
149 END IF ;
150
151 IF l_obj_instance_id IS NOT NULL
152 THEN
153 p_txn_ii_rltns_rec.object_id := l_obj_instance_id;
154 p_txn_ii_rltns_rec.object_type := 'I' ;
155 ELSIF l_obj_tld_id IS NOT NULL
156 THEN
157 p_txn_ii_rltns_rec.object_id := l_obj_tld_id;
158 p_txn_ii_rltns_rec.object_type := 'T' ;
159 END IF ;
160
161 IF l_obj_instance_id IS NULL
162 AND l_obj_tld_id IS NULL
163 THEN
164 RAISE fnd_api.g_exc_error ;
165 END IF ;
166 END IF ; --object_id is null
167 -- Added for CZ Integration (End)
168
169 IF csi_t_gen_utility_pvt.g_debug_level > 1 THEN
170
171 csi_t_gen_utility_pvt.dump_ii_rltns_rec(
172 p_ii_rltns_rec => p_txn_ii_rltns_rec);
173
174 END IF;
175
176 -- Main API code
177
178 csi_t_vldn_routines_pvt.check_reqd_param(
179 p_value => p_txn_ii_rltns_rec.object_type,
180 p_param_name => 'p_txn_ii_rltns_rec.object_type',
181 p_api_name => l_api_name);
182
183 csi_t_vldn_routines_pvt.check_reqd_param(
184 p_value => p_txn_ii_rltns_rec.object_id,
185 p_param_name => 'p_txn_ii_rltns_rec.object_id',
186 p_api_name => l_api_name);
187
188 csi_t_vldn_routines_pvt.check_reqd_param(
189 p_value => p_txn_ii_rltns_rec.subject_type,
190 p_param_name => 'p_txn_ii_rltns_rec.subject_type',
191 p_api_name => l_api_name);
192
193 csi_t_vldn_routines_pvt.check_reqd_param(
194 p_value => p_txn_ii_rltns_rec.subject_id,
195 p_param_name => 'p_txn_ii_rltns_rec.subject_id',
196 p_api_name => l_api_name);
197
198 csi_t_vldn_routines_pvt.check_reqd_param(
199 p_value => p_txn_ii_rltns_rec.relationship_type_code,
200 p_param_name => 'p_txn_ii_rltns_rec.relationship_type_code',
201 p_api_name => l_api_name);
202
203 -- Added for M-M to make sure correctly initialized params are passed for the validate_txn_rltnshp routine call down below Start
204
205 IF p_txn_ii_rltns_rec.subject_type not in ('T', 'I') THEN
206 FND_MESSAGE.set_name('CSI','CSI_TXN_II_INVALID_RLTNS_TYPE');
207 FND_MESSAGE.set_token('RLTNS_TYPE',p_txn_ii_rltns_rec.subject_type);
208 FND_MSG_PUB.add;
209 RAISE fnd_api.g_exc_error;
210 ELSIF p_txn_ii_rltns_rec.object_type not in ('T', 'I') THEN
211 FND_MESSAGE.set_name('CSI','CSI_TXN_II_INVALID_RLTNS_TYPE');
212 FND_MESSAGE.set_token('RLTNS_TYPE',p_txn_ii_rltns_rec.object_type);
213 FND_MSG_PUB.add;
214 RAISE fnd_api.g_exc_error;
215 ELSIF (p_txn_ii_rltns_rec.subject_type = 'I'
216 AND p_txn_ii_rltns_rec.object_type = 'I' ) THEN
217 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
218 FND_MESSAGE.set_token('TXN_DTL_ID',p_txn_ii_rltns_rec.object_id);
219 FND_MSG_PUB.add;
220 x_return_status := fnd_api.g_ret_sts_error;
221 RAISE fnd_api.g_exc_error;
222 ELSIF NOT(p_txn_ii_rltns_rec.subject_type = 'T'
223 AND p_txn_ii_rltns_rec.object_type = 'T' ) THEN
224 IF p_txn_ii_rltns_rec.subject_type = 'T' THEN
225 l_txn_line_dtl_rec1 := l_txn_line_dtl_g_miss;-- subject TLD record
226 ELSE
227 l_txn_line_dtl_rec2 := l_txn_line_dtl_g_miss; --object TLD record
228 END IF;
229 END IF;
230
231
232 IF p_txn_ii_rltns_rec.subject_type = 'T' THEN
233 /* IF nvl(p_txn_ii_rltns_rec.subject_index_flag,fnd_api.g_miss_char)
234 = fnd_api.g_miss_char THEN -- Added the IF for M-M . checking the index flags so that records passed by the txn details grp API are not validated again.
235 */
236 csi_t_gen_utility_pvt.add('Validate subject_id.');
237
238 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
239 p_txn_line_detail_id => p_txn_ii_rltns_rec.subject_id,
240 x_txn_line_detail_rec =>l_txn_line_dtl_rec2, -- calling the overloaded routine. M-M change
241 x_return_status => l_return_status);
242
243 IF l_return_status <> fnd_api.g_ret_sts_success THEN
244
245 FND_MESSAGE.set_name('CSI','CSI_TXN_SUBJECT_ID_INVALID');
246 FND_MESSAGE.set_token('SUBJECT_ID',p_txn_ii_rltns_rec.subject_id);
247 FND_MSG_PUB.add;
248 RAISE fnd_api.g_exc_error;
249
250 END IF;
251 -- END IF;
252 END IF;
253
254 IF p_txn_ii_rltns_rec.object_type = 'T' THEN
255 /* IF nvl(p_txn_ii_rltns_rec.object_index_flag,fnd_api.g_miss_char)
256 = fnd_api.g_miss_char THEN -- Added for M-M . checking the index flags so that records passed by the txn details grp API are not validated again.
257 */
258 csi_t_gen_utility_pvt.add('Validate object_id.');
259
260 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
261 p_txn_line_detail_id => p_txn_ii_rltns_rec.object_id,
262 x_txn_line_detail_rec =>l_txn_line_dtl_rec1, -- calling the overloaded routine. M-M change
263 x_return_status => l_return_status);
264
265 IF l_return_status <> fnd_api.g_ret_sts_success THEN
266
267 FND_MESSAGE.set_name('CSI','CSI_TXN_OBJECT_ID_INVALID');
268 FND_MESSAGE.set_token('OBJECT_ID',p_txn_ii_rltns_rec.object_id);
269 FND_MSG_PUB.add;
270 RAISE fnd_api.g_exc_error;
271
272 END IF;
273 -- END IF;
274 END IF;
275
276 csi_t_gen_utility_pvt.add('Validate relationship_type code.');
277
278 -- validate relationship_type_code csi_ii_relation_types table
279 csi_t_vldn_routines_pvt.validate_ii_rltns_type_code(
280 p_rltns_type_code => p_txn_ii_rltns_rec.relationship_type_code,
281 x_return_status => l_return_status);
282
283 IF l_return_status = fnd_api.g_ret_sts_error THEN
284 FND_MESSAGE.set_name('CSI','CSI_TXN_II_RLTNS_CODE_INVALID');
285 FND_MESSAGE.set_token('RLTNS_CODE',
286 p_txn_ii_rltns_rec.relationship_type_code);
287 FND_MSG_PUB.add;
288 RAISE fnd_api.g_exc_error;
289 END IF;
290
291 -- validate instance reference
292
293 IF nvl(p_txn_ii_rltns_rec.csi_inst_relationship_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
294 csi_t_gen_utility_pvt.add('Validate instance reference.');
295 csi_t_vldn_routines_pvt.validate_instance_reference(
296 p_level => 'II_RLTNS',
297 p_level_dtl_id => p_txn_ii_rltns_rec.object_id,
298 p_level_inst_ref_id => p_txn_ii_rltns_rec.csi_inst_relationship_id,
299 x_return_status => l_return_status);
300
301 IF l_return_status <> fnd_api.g_ret_sts_success THEN
302 csi_t_gen_utility_pvt.add('Error in here.');
303 RAISE fnd_api.g_exc_error;
304 END IF;
305 END IF;
306
307 -- Validate transfer_components
308 IF p_txn_ii_rltns_rec.transfer_components_flag <> fnd_api.g_miss_char THEN
309 IF p_txn_ii_rltns_rec.relationship_type_code
310 NOT IN ('REPLACED-BY', 'REPLACEMENT-FOR','UPGRADED-FROM') THEN
311 FND_MESSAGE.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
312 FND_MESSAGE.set_token('PARAM','transfer_components_flag');
313 FND_MESSAGE.set_token('VALUE',p_txn_ii_rltns_rec.transfer_components_flag);
314 FND_MESSAGE.set_token('REASON','This attribute is applicable to
315 REPLACED-BY, REPLACEMENT-FOR,UPGRADED-FROM relationship types only');
316 FND_MSG_PUB.add;
317 p_txn_ii_rltns_rec.transfer_components_flag := fnd_api.g_miss_char;
318 END IF;
319 END IF;
320
321 -- validate mandatory_flag
322
323 -- validate display_order
324
325 -- validate position_reference
326
327 /* Added M-M changes Validate the relationship record */
328 csi_t_gen_utility_pvt.dump_line_detail_rec
329 ( p_line_detail_rec => l_txn_line_dtl_rec1
330 );
331
332 csi_t_gen_utility_pvt.dump_line_detail_rec
333 ( p_line_detail_rec => l_txn_line_dtl_rec2
334 );
335
336 csi_t_vldn_routines_pvt.validate_txn_rltnshp (
337 p_txn_line_detail_rec1 => l_txn_line_dtl_rec1,
338 p_txn_line_detail_rec2 => l_txn_line_dtl_rec2,
339 p_iir_rec => p_txn_ii_rltns_rec,
340 x_return_status => l_return_status);
341
342 IF l_return_status <> fnd_api.g_ret_sts_success THEN
343 RAISE fnd_api.g_exc_error;
344 END IF;
345
346 -- call table handler
347
348 if nvl(p_txn_ii_rltns_rec.txn_relationship_id,fnd_api.g_miss_num) <>
349 fnd_api.g_miss_num then
350 l_txn_relationship_id := p_txn_ii_rltns_rec.txn_relationship_id;
351 end if;
352
353 begin
354
355 csi_t_gen_utility_pvt.dump_api_info(
356 p_api_name => 'insert_row',
357 p_pkg_name => 'csi_t_ii_relationships_pkg');
358
359 csi_t_ii_relationships_pkg.insert_row(
360 px_txn_relationship_id => l_txn_relationship_id,
361 p_transaction_line_id => p_txn_ii_rltns_rec.transaction_line_id,
362 p_object_type => p_txn_ii_rltns_rec.object_type,
363 p_object_id => p_txn_ii_rltns_rec.object_id,
364 p_relationship_type_code => p_txn_ii_rltns_rec.relationship_type_code,
365 p_display_order => p_txn_ii_rltns_rec.display_order,
366 p_position_reference => p_txn_ii_rltns_rec.position_reference,
367 p_mandatory_flag => p_txn_ii_rltns_rec.mandatory_flag,
368 p_active_start_date => p_txn_ii_rltns_rec.active_start_date,
369 p_active_end_date => p_txn_ii_rltns_rec.active_end_date,
370 p_csi_inst_relationship_id => p_txn_ii_rltns_rec.csi_inst_relationship_id,
371 p_subject_type => p_txn_ii_rltns_rec.subject_type,
372 p_subject_id => p_txn_ii_rltns_rec.subject_id,
373
374 -- Added for CZ Integration (Begin)
375 p_sub_config_inst_hdr_id => p_txn_ii_rltns_rec.sub_config_inst_hdr_id ,
376 p_sub_config_inst_rev_num => p_txn_ii_rltns_rec.sub_config_inst_rev_num ,
377 p_sub_config_inst_item_id => p_txn_ii_rltns_rec.sub_config_inst_item_id ,
378 p_obj_config_inst_hdr_id => p_txn_ii_rltns_rec.obj_config_inst_hdr_id ,
379 p_obj_config_inst_rev_num => p_txn_ii_rltns_rec.obj_config_inst_rev_num ,
380 p_obj_config_inst_item_id => p_txn_ii_rltns_rec.obj_config_inst_item_id ,
381 p_target_commitment_date => p_txn_ii_rltns_rec.target_commitment_date ,
382 -- Added for CZ Integration (End)
383 p_attribute1 => p_txn_ii_rltns_rec.attribute1,
384 p_attribute2 => p_txn_ii_rltns_rec.attribute2,
385 p_attribute3 => p_txn_ii_rltns_rec.attribute3,
386 p_attribute4 => p_txn_ii_rltns_rec.attribute4,
387 p_attribute5 => p_txn_ii_rltns_rec.attribute5,
388 p_attribute6 => p_txn_ii_rltns_rec.attribute6,
389 p_attribute7 => p_txn_ii_rltns_rec.attribute7,
390 p_attribute8 => p_txn_ii_rltns_rec.attribute8,
391 p_attribute9 => p_txn_ii_rltns_rec.attribute9,
392 p_attribute10 => p_txn_ii_rltns_rec.attribute10,
393 p_attribute11 => p_txn_ii_rltns_rec.attribute11,
394 p_attribute12 => p_txn_ii_rltns_rec.attribute12,
395 p_attribute13 => p_txn_ii_rltns_rec.attribute13,
396 p_attribute14 => p_txn_ii_rltns_rec.attribute14,
397 p_attribute15 => p_txn_ii_rltns_rec.attribute15,
398 p_created_by => g_user_id,
399 p_creation_date => sysdate,
400 p_last_updated_by => g_user_id,
401 p_last_update_date => sysdate,
402 p_last_update_login => g_login_id,
403 p_object_version_number => 1.0,
404 p_context => p_txn_ii_rltns_rec.context,
405 p_transfer_components_flag => p_txn_ii_rltns_rec.transfer_components_flag);
406
407 exception
408 when others then
409 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
410 fnd_message.set_token('MESSAGE',
411 'csi_t_ii_relationships_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
412 fnd_msg_pub.add;
413 raise fnd_api.g_exc_error;
414 end;
415
416 p_txn_ii_rltns_rec.txn_relationship_id := l_txn_relationship_id;
417
418 -- Standard check of p_commit.
419 IF FND_API.To_Boolean( p_commit ) THEN
420 COMMIT WORK;
421 END IF;
422
423 IF csi_t_gen_utility_pvt.g_debug = fnd_api.g_true THEN
424 csi_t_gen_utility_pvt.set_debug_off;
425 END IF;
426
427 -- Standard call to get message count and if count is get message info.
428 FND_MSG_PUB.Count_And_Get(
429 p_count => x_msg_count,
430 p_data => x_msg_data);
431
432 EXCEPTION
433 WHEN FND_API.G_EXC_ERROR THEN
434
435 ROLLBACK TO create_txn_ii_rltns_dtls;
436
437 x_return_status := FND_API.G_RET_STS_ERROR ;
438 FND_MSG_PUB.Count_And_Get (
439 p_count => x_msg_count,
440 p_data => x_msg_data);
441
442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
443
444 ROLLBACK TO create_txn_ii_rltns_dtls;
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
446
447 FND_MSG_PUB.Count_And_Get(
448 p_count => x_msg_count,
449 p_data => x_msg_data);
450
451 WHEN OTHERS THEN
452
453 ROLLBACK TO create_txn_ii_rltns_dtls;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
455
456 IF FND_MSG_PUB.Check_Msg_Level(
457 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
458
459 FND_MSG_PUB.Add_Exc_Msg(
460 p_pkg_name => G_PKG_NAME,
461 p_procedure_name => l_api_name);
462
463 END IF;
464
465 FND_MSG_PUB.Count_And_Get(
466 p_count => x_msg_count,
467 p_data => x_msg_data);
468
469 END create_txn_ii_rltns_dtls;
470
471 PROCEDURE update_txn_ii_rltns_dtls (
472 p_api_version IN NUMBER,
473 p_commit IN VARCHAR2 := fnd_api.g_false,
474 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
475 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
476 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2)
480 IS
481
482 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_ii_rltns_dtls';
483 l_api_version CONSTANT NUMBER := 1.0;
484 l_debug_level NUMBER;
485
486 l_return_status VARCHAR2(1);
487 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
488 --l_iir_rec csi_t_ii_relationships%ROWTYPE; M-M change . referencing datastructure record instead
489 l_iir_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
490 l_txn_line_dtl_rec1 csi_t_datastructures_grp.txn_line_detail_rec;
491 l_txn_line_dtl_rec2 csi_t_datastructures_grp.txn_line_detail_rec;
492 l_txn_line_dtl_g_miss csi_t_datastructures_grp.txn_line_detail_rec;
493 l_sub_instance_id NUMBER ;
494 l_sub_tld_id NUMBER ;
495 l_obj_instance_id NUMBER ;
496 l_obj_tld_id NUMBER ;
497
498 CURSOR iir_cur(p_rltns_id IN NUMBER) IS
499 SELECT *
500 FROM csi_t_ii_relationships
501 WHERE txn_relationship_id = p_rltns_id;
502
503 BEGIN
504
505 -- Standard Start of API savepoint
506 SAVEPOINT update_txn_ii_rltns_dtls;
507
508 -- Initialize message list if p_init_msg_list is set to TRUE.
509 IF FND_API.to_Boolean( p_init_msg_list ) THEN
510 FND_MSG_PUB.initialize;
511 END IF;
512
513 -- Initialize API return status to success
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515
516 -- Standard call to check for call compatibility.
517 IF NOT
518
519 FND_API.Compatible_API_Call (
520 p_current_version_number => l_api_version,
521 p_caller_version_number => p_api_version,
522 p_api_name => l_api_name,
523 p_pkg_name => G_PKG_NAME) THEN
524
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526
527 END IF;
528
529 --debug messages
530 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
531
532 csi_t_gen_utility_pvt.dump_api_info(
533 p_pkg_name => g_pkg_name,
534 p_api_name => l_api_name);
535
536 csi_t_gen_utility_pvt.add(
537 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
538
539 -- Main API code
540 IF p_txn_ii_rltns_tbl.count > 0 THEN
541 FOR l_ind IN p_txn_ii_rltns_tbl.FIRST .. p_txn_ii_rltns_tbl.LAST
542 LOOP
543
544 IF l_debug_level > 1 THEN
545 csi_t_gen_utility_pvt.dump_ii_rltns_rec(
546 p_ii_rltns_rec => p_txn_ii_rltns_tbl(l_ind));
547 END IF;
548
549 l_iir_rec.txn_relationship_id :=
550 p_txn_ii_rltns_tbl(l_ind).txn_relationship_id;
551
552 csi_t_vldn_routines_pvt.check_reqd_param(
553 p_value => l_iir_rec.txn_relationship_id,
554 p_param_name => 'l_iir_rec.txn_relationship_id',
555 p_api_name => l_api_name);
556
557 csi_t_vldn_routines_pvt.validate_txn_relationship_id(
558 p_txn_relationship_id => l_iir_rec.txn_relationship_id,
559 x_return_status => l_return_status);
560
561 IF l_return_status <> fnd_api.g_ret_sts_success THEN
562
563 FND_MESSAGE.set_name('CSI','CSI_TXN_RLTNS_ID_INVALID');
564 FND_MESSAGE.set_token('RLTNS_ID',l_iir_rec.txn_relationship_id);
565 FND_MSG_PUB.add;
566 RAISE fnd_api.g_exc_error;
567
568 END IF;
569
570 FOR l_iir_cur_rec in iir_cur(l_iir_rec.txn_relationship_id)
571 LOOP
572
573 l_iir_rec.transaction_line_id := l_iir_cur_rec.transaction_line_id;
574 l_iir_rec.subject_id := p_txn_ii_rltns_tbl(l_ind).subject_id;
575 l_iir_rec.object_id := p_txn_ii_rltns_tbl(l_ind).object_id;
576 l_iir_rec.subject_type := p_txn_ii_rltns_tbl(l_ind).subject_type;
577 l_iir_rec.object_type := p_txn_ii_rltns_tbl(l_ind).object_type;
578
579
580 -- Added for CZ Integration (Begin)
581
582 IF ( NVL(p_txn_ii_rltns_tbl(l_ind).sub_config_inst_hdr_id , fnd_api.g_miss_num)
583 <> fnd_api.g_miss_num
584 OR NVL(p_txn_ii_rltns_tbl(l_ind).obj_config_inst_hdr_id , fnd_api.g_miss_num)
585 <> fnd_api.g_miss_num )
586 AND p_txn_ii_rltns_tbl(l_ind).api_caller_identity <> 'CONFIG'
587 THEN
588 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
589 FND_MESSAGE.set_token('API_CALLER',p_txn_ii_rltns_tbl(l_ind).api_caller_identity) ;
590 FND_MSG_PUB.add;
591 RAISE FND_API.g_exc_error;
592 END IF ;
593
594 IF (NVL(p_txn_ii_rltns_tbl(l_ind).sub_config_inst_hdr_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num
595 AND NVL(p_txn_ii_rltns_tbl(l_ind).subject_id , fnd_api.g_miss_num)
596 = fnd_api.g_miss_num)
597 THEN
598 -- Now assuming that user is passing only config keys , get the associated instance/txn_detail_line.
599
600 csi_t_vldn_routines_pvt.get_cz_inst_or_tld_id (
601 p_config_inst_hdr_id => p_txn_ii_rltns_tbl(l_ind).sub_config_inst_hdr_id,
602 p_config_inst_rev_num => p_txn_ii_rltns_tbl(l_ind).sub_config_inst_rev_num,
603 p_config_inst_item_id => p_txn_ii_rltns_tbl(l_ind).sub_config_inst_item_id ,
604 x_instance_id => l_sub_instance_id ,
605 x_txn_line_detail_id => l_sub_tld_id ,
606 x_return_status => x_return_status );
607
608 IF x_return_status <> fnd_api.g_ret_sts_success
609 THEN
610 RAISE fnd_api.g_exc_error ;
611 END IF ;
612 IF l_sub_instance_id IS NULL
613 AND l_sub_tld_id IS NULL
614 THEN
615 RAISE fnd_api.g_exc_error ;
616 END IF ;
617
618 IF l_sub_instance_id IS NOT NULL
619 THEN
620 l_iir_rec.subject_id := l_sub_instance_id;
621 l_iir_rec.subject_type := 'I' ;
622 ELSIF l_sub_tld_id IS NOT NULL
623 THEN
624 l_iir_rec.subject_id := l_sub_tld_id;
625 l_iir_rec.subject_type := 'T' ;
626 END IF ;
627 -- get the display order for the subject
628 Begin
629 Select to_number(substr(cz.bom_sort_order,length(bom_sort_order)-3,4))
630 into l_iir_rec.display_order
631 from cz_config_items_v cz
632 where cz.instance_hdr_id = p_txn_ii_rltns_tbl(l_ind).sub_config_inst_hdr_id
633 and cz.instance_rev_nbr= p_txn_ii_rltns_tbl(l_ind).sub_config_inst_rev_num
634 and cz.config_item_id = p_txn_ii_rltns_tbl(l_ind).sub_config_inst_item_id;
635 Exception when others then
636 Null;
637 End;
638
639 END IF ; --subject_id is null
640
641
642 IF (NVL(p_txn_ii_rltns_tbl(l_ind).obj_config_inst_hdr_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num
643 AND NVL(p_txn_ii_rltns_tbl(l_ind).object_id , fnd_api.g_miss_num)
644 = fnd_api.g_miss_num)
645 THEN
646 csi_t_vldn_routines_pvt.get_cz_inst_or_tld_id (
647 p_config_inst_hdr_id => p_txn_ii_rltns_tbl(l_ind).obj_config_inst_hdr_id,
648 p_config_inst_rev_num => p_txn_ii_rltns_tbl(l_ind).obj_config_inst_rev_num,
649 p_config_inst_item_id => p_txn_ii_rltns_tbl(l_ind).obj_config_inst_item_id ,
650 x_instance_id => l_obj_instance_id ,
651 x_txn_line_detail_id => l_obj_tld_id ,
652 x_return_status => x_return_status );
653
654 IF x_return_status <> fnd_api.g_ret_sts_success
655 THEN
656 RAISE fnd_api.g_exc_error ;
657 END IF ;
658
659 IF l_obj_instance_id IS NOT NULL
660 THEN
661 l_iir_rec.object_id := l_obj_instance_id;
662 l_iir_rec.object_type := 'I' ;
663 ELSIF l_obj_tld_id IS NOT NULL
664 THEN
665 l_iir_rec.object_id := l_obj_tld_id;
666 l_iir_rec.object_type := 'T' ;
667 END IF ;
668
669 IF l_obj_instance_id IS NULL
670 AND l_obj_tld_id IS NULL
671 THEN
672 RAISE fnd_api.g_exc_error ;
673 END IF ;
674 END IF ; --object_id is null
675 -- Added for CZ Integration (End)
676
677 -- Added for M-M to make sure correctly initialized params are passed for the validate_txn_rltnshp routine call down below
678
679 IF l_iir_rec.subject_type not in ('T', 'I') THEN
680 FND_MESSAGE.set_name('CSI','CSI_TXN_II_INVALID_RLTNS_TYPE');
681 FND_MESSAGE.set_token('RLTNS_TYPE',l_iir_rec.subject_type);
682 FND_MSG_PUB.add;
683 RAISE fnd_api.g_exc_error;
684 ELSIF l_iir_rec.object_type not in ('T', 'I') THEN
685 FND_MESSAGE.set_name('CSI','CSI_TXN_II_INVALID_RLTNS_TYPE');
686 FND_MESSAGE.set_token('RLTNS_TYPE',l_iir_rec.object_type);
687 FND_MSG_PUB.add;
688 RAISE fnd_api.g_exc_error;
689 ELSIF (l_iir_rec.subject_type = 'I'
690 AND l_iir_rec.object_type = 'I' ) THEN
691 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_SUB_OBJ_TYPES');
692 FND_MESSAGE.set_token('TXN_DTL_ID',l_iir_rec.object_id);
693 FND_MSG_PUB.add;
694 x_return_status := fnd_api.g_ret_sts_error;
695 RAISE fnd_api.g_exc_error;
696 ELSIF NOT(l_iir_rec.subject_type = 'T'
697 AND l_iir_rec.object_type = 'T' ) THEN
698 IF l_iir_rec.subject_type = 'T' THEN
699 l_txn_line_dtl_rec1 := l_txn_line_dtl_g_miss;-- subject TLD record
700 ELSE
701 l_txn_line_dtl_rec2 := l_txn_line_dtl_g_miss; --object TLD record
702 END IF;
703 END IF;
704
705 csi_t_gen_utility_pvt.add('Validate subject_id.');
706
707 IF l_iir_rec.subject_type = 'T' THEN
708 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
709 p_txn_line_detail_id => l_iir_rec.subject_id,
710 x_txn_line_detail_rec =>l_txn_line_dtl_rec2, -- calling the overloaded routine. M-M change
711 x_return_status => l_return_status);
712
713 IF l_return_status <> fnd_api.g_ret_sts_success THEN
714
715 FND_MESSAGE.set_name('CSI','CSI_TXN_SUBJECT_ID_INVALID');
716 FND_MESSAGE.set_token('SUBJECT_ID',l_iir_rec.subject_id);
717 FND_MSG_PUB.add;
718 RAISE fnd_api.g_exc_error;
719
720 END IF;
721 END IF;
722
723 csi_t_gen_utility_pvt.add('Validate object_id.');
724
725 IF l_iir_rec.object_type = 'T' THEN
726 -- validate object_id
727 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
728 p_txn_line_detail_id => l_iir_rec.object_id,
729 x_txn_line_detail_rec =>l_txn_line_dtl_rec1, -- calling the overloaded routine. M-M change
730 x_return_status => l_return_status);
731
732 IF l_return_status <> fnd_api.g_ret_sts_success THEN
733
734 FND_MESSAGE.set_name('CSI','CSI_TXN_OBJECT_ID_INVALID');
735 FND_MESSAGE.set_token('OBJECT_ID',l_iir_rec.object_id);
736 FND_MSG_PUB.add;
737 RAISE fnd_api.g_exc_error;
738
739 END IF;
740 END IF;
741
742 BEGIN
743 IF l_iir_rec.subject_type = 'T' THEN
744 SELECT tl.processing_status
745 INTO l_processing_status
746 FROM csi_t_transaction_lines tl, csi_t_txn_line_details tld
747 WHERE tld.txn_line_detail_id = l_iir_rec.subject_id
748 AND tl.transaction_line_id = l_iir_rec.transaction_line_id
749 AND tl.transaction_line_id = tld.transaction_line_id;
750
751 IF l_processing_status = 'PROCESSED' THEN
752
753 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
754 FND_MESSAGE.set_token('LVL_ID', l_iir_rec.transaction_line_id);
755 FND_MESSAGE.set_token('STATUS',l_processing_status);
756 FND_MSG_PUB.add;
757 RAISE FND_API.g_exc_error;
758
759 ELSIF l_iir_rec.object_type = 'T' THEN
760 SELECT tl.processing_status
761 INTO l_processing_status
762 FROM csi_t_transaction_lines tl, csi_t_txn_line_details tld
763 WHERE tld.txn_line_detail_id = l_iir_rec.object_id
764 AND tl.transaction_line_id = l_iir_rec.transaction_line_id
765 AND tl.transaction_line_id = tld.transaction_line_id;
766
767 IF l_processing_status = 'PROCESSED' THEN
768
769 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
770 FND_MESSAGE.set_token('LVL_ID', l_iir_rec.transaction_line_id);
771 FND_MESSAGE.set_token('STATUS',l_processing_status);
772 FND_MSG_PUB.add;
773 RAISE FND_API.g_exc_error;
774 END IF;
775 END IF;
776 ELSIF l_iir_rec.object_type = 'T' THEN
777 SELECT tl.processing_status
778 INTO l_processing_status
779 FROM csi_t_transaction_lines tl, csi_t_txn_line_details tld
780 WHERE tld.txn_line_detail_id = l_iir_rec.object_id
781 AND tl.transaction_line_id = l_iir_rec.transaction_line_id
782 AND tl.transaction_line_id = tld.transaction_line_id;
783
784 IF l_processing_status = 'PROCESSED' THEN
785
786 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
787 FND_MESSAGE.set_token('LVL_ID', l_iir_rec.transaction_line_id);
788 FND_MESSAGE.set_token('STATUS',l_processing_status);
789 FND_MSG_PUB.add;
790 RAISE FND_API.g_exc_error;
791 END IF;
792
793 END IF;
794 Exception when others then
795 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
796 fnd_message.set_token('MESSAGE',
797 'Unhandled exception in Update Txn reltns'||substr(sqlerrm,1,200));
798 fnd_msg_pub.add;
799 raise fnd_api.g_exc_error;
800 END;
801
802 l_iir_rec.relationship_type_code :=
803 p_txn_ii_rltns_tbl(l_ind).relationship_type_code;
804
805 IF l_iir_rec.relationship_type_code <> fnd_api.g_miss_char THEN
806
807 csi_t_vldn_routines_pvt.validate_ii_rltns_type_code(
808 p_rltns_type_code => l_iir_rec.relationship_type_code,
809 x_return_status => l_return_status);
810
811 IF l_return_status <> fnd_api.g_ret_sts_success THEN
812
813 FND_MESSAGE.set_name('CSI','CSI_TXN_II_RLTNS_CODE_INVALID');
814 FND_MESSAGE.set_token('RLTNS_CODE',l_iir_rec.relationship_type_code);
815 FND_MSG_PUB.add;
816 RAISE fnd_api.g_exc_error;
817
818 END IF;
819
820 END IF;
821
822 -- Validate transfer_components
823
824 l_iir_rec.transfer_components_flag :=
825 p_txn_ii_rltns_tbl(l_ind).transfer_components_flag;
826
827 IF l_iir_rec.transfer_components_flag <> fnd_api.g_miss_char THEN
828 IF l_iir_rec.relationship_type_code
829 NOT IN ('REPLACED-BY', 'REPLACEMENT-FOR','UPGRADED-FROM') THEN
830 FND_MESSAGE.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
831 FND_MESSAGE.set_token('PARAM','transfer_components_flag');
832 FND_MESSAGE.set_token('VALUE',l_iir_rec.transfer_components_flag);
833 FND_MESSAGE.set_token('REASON','This attribute is applicable to
834 REPLACED-BY, REPLACEMENT-FOR,UPGRADED-FROM relationship types only');
835 FND_MSG_PUB.add;
836 l_iir_rec.transfer_components_flag := fnd_api.g_miss_char;
837 END IF;
838 END IF;
839
840 l_iir_rec.csi_inst_relationship_id :=
841 p_txn_ii_rltns_tbl(l_ind).csi_inst_relationship_id;
842
843 -- ##validate instance relationship_id
844 IF l_iir_rec.csi_inst_relationship_id <> fnd_api.g_miss_num
845 AND
846 l_iir_rec.csi_inst_relationship_id is not null
847 THEN
848 null;
849 END IF;
850
851
852 /* M-M changes Validate the relationship record */
853
854 csi_t_vldn_routines_pvt.validate_txn_rltnshp (
855 p_txn_line_detail_rec1 => l_txn_line_dtl_rec1,
856 p_txn_line_detail_rec2 => l_txn_line_dtl_rec2,
857 p_iir_rec => l_iir_rec,
858 x_return_status => l_return_status);
859
860 IF l_return_status <> fnd_api.g_ret_sts_success THEN
861 RAISE fnd_api.g_exc_error;
862 END IF;
863
864
865 l_iir_rec.position_reference := p_txn_ii_rltns_tbl(l_ind).position_reference;
866 l_iir_rec.mandatory_flag := p_txn_ii_rltns_tbl(l_ind).mandatory_flag;
867 l_iir_rec.active_start_date := p_txn_ii_rltns_tbl(l_ind).active_start_date;
868 l_iir_rec.active_end_date := p_txn_ii_rltns_tbl(l_ind).active_end_date;
869 l_iir_rec.attribute1 := p_txn_ii_rltns_tbl(l_ind).attribute1;
870 l_iir_rec.attribute2 := p_txn_ii_rltns_tbl(l_ind).attribute2;
871 l_iir_rec.attribute3 := p_txn_ii_rltns_tbl(l_ind).attribute3;
872 l_iir_rec.attribute4 := p_txn_ii_rltns_tbl(l_ind).attribute4;
873 l_iir_rec.attribute5 := p_txn_ii_rltns_tbl(l_ind).attribute5;
874 l_iir_rec.attribute6 := p_txn_ii_rltns_tbl(l_ind).attribute6;
875 l_iir_rec.attribute7 := p_txn_ii_rltns_tbl(l_ind).attribute7;
876 l_iir_rec.attribute8 := p_txn_ii_rltns_tbl(l_ind).attribute8;
877 l_iir_rec.attribute9 := p_txn_ii_rltns_tbl(l_ind).attribute9;
878 l_iir_rec.attribute10 := p_txn_ii_rltns_tbl(l_ind).attribute10;
879 l_iir_rec.attribute11 := p_txn_ii_rltns_tbl(l_ind).attribute11;
880 l_iir_rec.attribute12 := p_txn_ii_rltns_tbl(l_ind).attribute12;
881 l_iir_rec.attribute13 := p_txn_ii_rltns_tbl(l_ind).attribute13;
882 l_iir_rec.attribute14 := p_txn_ii_rltns_tbl(l_ind).attribute14;
883 l_iir_rec.attribute15 := p_txn_ii_rltns_tbl(l_ind).attribute15;
884 /* -- M-M change . Passing these directly in the table handler below
885 l_iir_rec.created_by := l_iir_cur_rec.created_by;
886 l_iir_rec.creation_date := l_iir_cur_rec.creation_date;
887 l_iir_rec.last_updated_by := g_user_id;
888 l_iir_rec.last_update_date := sysdate;
889 l_iir_rec.last_update_login := g_login_id;
890 */
891 l_iir_rec.context := p_txn_ii_rltns_tbl(l_ind).context;
892 -- Added for CZ Integration (Begin)
893 l_iir_rec.sub_config_inst_hdr_id := p_txn_ii_rltns_tbl(l_ind).sub_config_inst_hdr_id ;
894 l_iir_rec.sub_config_inst_rev_num := p_txn_ii_rltns_tbl(l_ind).sub_config_inst_rev_num ;
895 l_iir_rec.sub_config_inst_item_id := p_txn_ii_rltns_tbl(l_ind).sub_config_inst_item_id ;
896 l_iir_rec.obj_config_inst_hdr_id := p_txn_ii_rltns_tbl(l_ind).obj_config_inst_hdr_id ;
897 l_iir_rec.obj_config_inst_rev_num := p_txn_ii_rltns_tbl(l_ind).obj_config_inst_rev_num ;
898 l_iir_rec.obj_config_inst_item_id := p_txn_ii_rltns_tbl(l_ind).obj_config_inst_item_id ;
899 l_iir_rec.target_commitment_date := p_txn_ii_rltns_tbl(l_ind).target_commitment_date ;
900 -- Added for CZ Integration (End)
901 l_iir_rec.object_version_number :=
902 p_txn_ii_rltns_tbl(l_ind).object_version_number;
903
904
905 begin
906
907 csi_t_gen_utility_pvt.dump_api_info(
908 p_api_name => 'update_row',
909 p_pkg_name => 'csi_t_ii_relationships_pkg');
910
911 csi_t_ii_relationships_pkg.update_row(
912 p_txn_relationship_id => l_iir_rec.txn_relationship_id,
913 p_transaction_line_id => l_iir_rec.transaction_line_id,
914 p_object_type => l_iir_rec.object_type,
915 p_object_id => l_iir_rec.object_id,
916 p_relationship_type_code => l_iir_rec.relationship_type_code,
917 p_display_order => l_iir_rec.display_order,
918 p_position_reference => l_iir_rec.position_reference,
919 p_mandatory_flag => l_iir_rec.mandatory_flag,
920 p_active_start_date => l_iir_rec.active_start_date,
921 p_active_end_date => l_iir_rec.active_end_date,
922 p_csi_inst_relationship_id => l_iir_rec.csi_inst_relationship_id,
923 p_subject_type => l_iir_rec.subject_type,
924 p_subject_id => l_iir_rec.subject_id,
925 -- Added for CZ Integration (Begin)
926 p_sub_config_inst_hdr_id => l_iir_rec.sub_config_inst_hdr_id ,
927 p_sub_config_inst_rev_num => l_iir_rec.sub_config_inst_rev_num ,
928 p_sub_config_inst_item_id => l_iir_rec.sub_config_inst_item_id ,
929 p_obj_config_inst_hdr_id => l_iir_rec.obj_config_inst_hdr_id ,
930 p_obj_config_inst_rev_num => l_iir_rec.obj_config_inst_rev_num ,
931 p_obj_config_inst_item_id => l_iir_rec.obj_config_inst_item_id ,
932 p_target_commitment_date => l_iir_rec.target_commitment_date ,
933 -- Added for CZ Integration (End)
934 p_attribute1 => l_iir_rec.attribute1,
935 p_attribute2 => l_iir_rec.attribute2,
936 p_attribute3 => l_iir_rec.attribute3,
937 p_attribute4 => l_iir_rec.attribute4,
938 p_attribute5 => l_iir_rec.attribute5,
939 p_attribute6 => l_iir_rec.attribute6,
940 p_attribute7 => l_iir_rec.attribute7,
941 p_attribute8 => l_iir_rec.attribute8,
942 p_attribute9 => l_iir_rec.attribute9,
943 p_attribute10 => l_iir_rec.attribute10,
944 p_attribute11 => l_iir_rec.attribute11,
945 p_attribute12 => l_iir_rec.attribute12,
946 p_attribute13 => l_iir_rec.attribute13,
947 p_attribute14 => l_iir_rec.attribute14,
948 p_attribute15 => l_iir_rec.attribute15,
949 p_created_by => l_iir_cur_rec.created_by,
950 p_creation_date => l_iir_cur_rec.creation_date,
951 p_last_updated_by => g_user_id,
952 p_last_update_date => sysdate,
953 p_last_update_login => g_login_id,
954 p_object_version_number => l_iir_rec.object_version_number,
955 p_context => l_iir_rec.context,
956 p_transfer_components_flag => l_iir_rec.transfer_components_flag);
957
958 exception
959 when others then
960 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
961 fnd_message.set_token('MESSAGE',
962 'csi_t_ii_relationships_pkg.update_row Failed. '||substr(sqlerrm,1,200));
963 fnd_msg_pub.add;
964 raise fnd_api.g_exc_error;
965 end;
966
967 END LOOP;
968 END LOOP;
969 END IF;
970
971 -- Standard check of p_commit.
972 IF FND_API.To_Boolean( p_commit ) THEN
973 COMMIT WORK;
974 END IF;
975
976 -- Standard call to get message count and if count is get message info.
977 FND_MSG_PUB.Count_And_Get(
978 p_count => x_msg_count,
979 p_data => x_msg_data);
980
981 EXCEPTION
982 WHEN FND_API.G_EXC_ERROR THEN
983
984 ROLLBACK TO update_txn_ii_rltns_dtls;
985 x_return_status := FND_API.G_RET_STS_ERROR ;
986 FND_MSG_PUB.Count_And_Get (
987 p_count => x_msg_count,
988 p_data => x_msg_data);
989
990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
991
992 ROLLBACK TO update_txn_ii_rltns_dtls;
993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
994
995 FND_MSG_PUB.Count_And_Get(
996 p_count => x_msg_count,
997 p_data => x_msg_data);
998
999 WHEN OTHERS THEN
1000
1001 ROLLBACK TO update_txn_ii_rltns_dtls;
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1003
1004 IF FND_MSG_PUB.Check_Msg_Level(
1005 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1006
1007 FND_MSG_PUB.Add_Exc_Msg(
1008 p_pkg_name => G_PKG_NAME,
1009 p_procedure_name => l_api_name);
1010
1011 END IF;
1012
1013 FND_MSG_PUB.Count_And_Get(
1014 p_count => x_msg_count,
1015 p_data => x_msg_data);
1016
1017 END update_txn_ii_rltns_dtls;
1018
1019 PROCEDURE delete_txn_ii_rltns_dtls(
1020 p_api_version IN NUMBER
1021 ,p_commit IN VARCHAR2 := fnd_api.g_false
1022 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1023 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1024 ,p_txn_ii_rltns_ids_tbl IN csi_t_datastructures_grp.txn_ii_rltns_ids_tbl
1025 ,x_return_status OUT NOCOPY VARCHAR2
1026 ,x_msg_count OUT NOCOPY NUMBER
1027 ,x_msg_data OUT NOCOPY VARCHAR2)
1028
1029 IS
1030
1031 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_ii_rltns_dtls';
1032 l_api_version CONSTANT NUMBER := 1.0;
1033 l_debug_level NUMBER;
1034 l_return_status VARCHAR2(1);
1035
1036 l_rltns_id NUMBER;
1037
1038 l_txn_line_id NUMBER;
1039
1040 CURSOR ii_cur (p_txn_line_id IN NUMBER) IS
1041 SELECT txn_relationship_id
1042 FROM csi_t_ii_relationships
1043 WHERE transaction_line_id = p_txn_line_id;
1044
1045 BEGIN
1046 -- Standard Start of API savepoint
1047 SAVEPOINT delete_txn_ii_rltns_dtls;
1048
1049 -- Initialize message list if p_init_msg_list is set to TRUE.
1050 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1051 FND_MSG_PUB.initialize;
1052 END IF;
1053
1054 -- Initialize API return status to success
1055 x_return_status := FND_API.G_RET_STS_SUCCESS;
1056
1057 -- Standard call to check for call compatibility.
1058 IF NOT
1059
1060 FND_API.Compatible_API_Call (
1061 p_current_version_number => l_api_version,
1062 p_caller_version_number => p_api_version,
1063 p_api_name => l_api_name,
1064 p_pkg_name => G_PKG_NAME) THEN
1065
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067
1068 END IF;
1069
1070 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1071
1072 csi_t_gen_utility_pvt.dump_api_info(
1073 p_pkg_name => g_pkg_name,
1074 p_api_name => l_api_name);
1075
1076 csi_t_gen_utility_pvt.add(
1077 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
1078
1079 -- Main API code
1080
1081 IF p_txn_ii_rltns_ids_tbl.COUNT > 0 THEN
1082
1083 FOR l_ind IN p_txn_ii_rltns_ids_tbl.FIRST.. p_txn_ii_rltns_ids_tbl.LAST
1084 LOOP
1085
1086 IF l_debug_level > 1 THEN
1087 null; --##
1088 END IF;
1089
1090 l_rltns_id := p_txn_ii_rltns_ids_tbl(l_ind).txn_relationship_id;
1091
1092 IF NVL(l_rltns_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1093
1094 csi_t_vldn_routines_pvt.validate_txn_relationship_id(
1095 p_txn_relationship_id => l_rltns_id,
1096 x_return_status => l_return_status);
1097
1098 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1099
1100 FND_MESSAGE.set_name('CSI','CSI_TXN_RLTNS_ID_INVALID');
1101 FND_MESSAGE.set_token('RLTNS_ID',l_rltns_id);
1102 FND_MSG_PUB.add;
1103 RAISE fnd_api.g_exc_error;
1104
1105 END IF;
1106
1107 csi_t_gen_utility_pvt.dump_api_info(
1108 p_api_name => 'delete_row',
1109 p_pkg_name => 'csi_t_ii_relationships_pkg');
1110
1111 csi_t_ii_relationships_pkg.delete_row(
1112 p_txn_relationship_id => l_rltns_id);
1113
1114 ELSE
1115
1116 l_txn_line_id := p_txn_ii_rltns_ids_tbl(l_ind).transaction_line_id;
1117
1118 csi_t_vldn_routines_pvt.check_reqd_param(
1119 p_value => l_txn_line_id,
1120 p_param_name => 'p_txn_ii_rltns_ids_tbl.transaction_line_id',
1121 p_api_name => l_api_name);
1122
1123 -- #validate transaction_line_id
1124
1125
1126 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1127
1128 null;
1129 -- #populate error message
1130
1131 END IF;
1132
1133 FOR ii_rec in ii_cur (l_txn_line_id)
1134 LOOP
1135
1136 csi_t_ii_relationships_pkg.delete_row(
1137 p_txn_relationship_id => ii_rec.txn_relationship_id);
1138
1139 END LOOP;
1140
1141 END IF;
1142
1143 END LOOP;
1144 END IF;
1145
1146 -- Standard check of p_commit.
1147 IF FND_API.To_Boolean( p_commit ) THEN
1148 COMMIT WORK;
1149 END IF;
1150
1151 -- Standard call to get message count and if count is get message info.
1152 FND_MSG_PUB.Count_And_Get(
1153 p_count => x_msg_count,
1154 p_data => x_msg_data);
1155
1156 EXCEPTION
1157 WHEN FND_API.G_EXC_ERROR THEN
1158
1159 ROLLBACK TO delete_txn_ii_rltns_dtls;
1160 x_return_status := FND_API.G_RET_STS_ERROR ;
1161 FND_MSG_PUB.Count_And_Get (
1162 p_count => x_msg_count,
1163 p_data => x_msg_data);
1164
1165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1166
1167 ROLLBACK TO delete_txn_ii_rltns_dtls;
1168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1169
1170 FND_MSG_PUB.Count_And_Get(
1171 p_count => x_msg_count,
1172 p_data => x_msg_data);
1173
1174 WHEN OTHERS THEN
1175
1176 ROLLBACK TO delete_txn_ii_rltns_dtls;
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1178
1179 IF FND_MSG_PUB.Check_Msg_Level(
1180 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1181
1182 FND_MSG_PUB.Add_Exc_Msg(
1183 p_pkg_name => G_PKG_NAME,
1184 p_procedure_name => l_api_name);
1185
1186 END IF;
1187
1188 FND_MSG_PUB.Count_And_Get(
1189 p_count => x_msg_count,
1190 p_data => x_msg_data);
1191
1192 END delete_txn_ii_rltns_dtls;
1193
1194 PROCEDURE get_ii_rltns_dtls(
1195 p_txn_line_id_list in varchar2,
1196 x_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
1197 x_return_status OUT NOCOPY varchar2)
1198 IS
1199
1200 l_select_stmt varchar2(2000);
1201 l_iir_cur_id integer;
1202 l_iir_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
1203 l_processed_rows number := 0;
1204 l_ind binary_integer;
1205
1206 BEGIN
1207
1208 l_select_stmt :=
1209 'select txn_relationship_id, transaction_line_id,csi_inst_relationship_id, '||
1210 ' subject_id, object_id, relationship_type_code, display_order,'||
1211 ' position_reference, mandatory_flag, active_start_date, active_end_date, '||
1212 ' context, attribute1, attribute2, attribute3, attribute4, attribute5, '||
1213 ' attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, '||
1214 ' attribute12, attribute13, attribute14, attribute15, object_version_number,transfer_components_flag '||
1215 'from csi_t_ii_relationships '||
1216 'where transaction_line_id in '||p_txn_line_id_list;
1217
1218 l_iir_cur_id := dbms_sql.open_cursor;
1219
1220 dbms_sql.parse(l_iir_cur_id, l_select_stmt , dbms_sql.native);
1221
1222 dbms_sql.define_column(l_iir_cur_id,1,l_iir_rec.txn_relationship_id);
1223 dbms_sql.define_column(l_iir_cur_id,2,l_iir_rec.transaction_line_id);
1224 dbms_sql.define_column(l_iir_cur_id,3,l_iir_rec.csi_inst_relationship_id);
1225 dbms_sql.define_column(l_iir_cur_id,4,l_iir_rec.subject_id);
1226 dbms_sql.define_column(l_iir_cur_id,5,l_iir_rec.object_id);
1227 dbms_sql.define_column(l_iir_cur_id,6,l_iir_rec.relationship_type_code,30);
1228 dbms_sql.define_column(l_iir_cur_id,7,l_iir_rec.display_order);
1229 dbms_sql.define_column(l_iir_cur_id,8,l_iir_rec.position_reference,30);
1230 dbms_sql.define_column(l_iir_cur_id,9,l_iir_rec.mandatory_flag,1);
1231 dbms_sql.define_column(l_iir_cur_id,10,l_iir_rec.active_start_date);
1232 dbms_sql.define_column(l_iir_cur_id,11,l_iir_rec.active_end_date);
1233 dbms_sql.define_column(l_iir_cur_id,12,l_iir_rec.context,30);
1234 dbms_sql.define_column(l_iir_cur_id,13,l_iir_rec.attribute1,150);
1235 dbms_sql.define_column(l_iir_cur_id,14,l_iir_rec.attribute2,150);
1236 dbms_sql.define_column(l_iir_cur_id,15,l_iir_rec.attribute3,150);
1237 dbms_sql.define_column(l_iir_cur_id,16,l_iir_rec.attribute4,150);
1238 dbms_sql.define_column(l_iir_cur_id,17,l_iir_rec.attribute5,150);
1239 dbms_sql.define_column(l_iir_cur_id,18,l_iir_rec.attribute6,150);
1240 dbms_sql.define_column(l_iir_cur_id,19,l_iir_rec.attribute7,150);
1241 dbms_sql.define_column(l_iir_cur_id,20,l_iir_rec.attribute8,150);
1242 dbms_sql.define_column(l_iir_cur_id,21,l_iir_rec.attribute9,150);
1243 dbms_sql.define_column(l_iir_cur_id,22,l_iir_rec.attribute10,150);
1244 dbms_sql.define_column(l_iir_cur_id,23,l_iir_rec.attribute11,150);
1245 dbms_sql.define_column(l_iir_cur_id,24,l_iir_rec.attribute12,150);
1246 dbms_sql.define_column(l_iir_cur_id,25,l_iir_rec.attribute13,150);
1247 dbms_sql.define_column(l_iir_cur_id,26,l_iir_rec.attribute14,150);
1248 dbms_sql.define_column(l_iir_cur_id,27,l_iir_rec.attribute15,150);
1249 dbms_sql.define_column(l_iir_cur_id,28,l_iir_rec.object_version_number);
1250 dbms_sql.define_column(l_iir_cur_id,29,l_iir_rec.transfer_components_flag,1);
1251
1252 l_ind := 0;
1253
1254 l_processed_rows := dbms_sql.execute(l_iir_cur_id);
1255 LOOP
1256 exit when dbms_sql.fetch_rows(l_iir_cur_id) = 0;
1257
1258 l_ind := l_ind + 1;
1259
1260
1261 dbms_sql.column_value(l_iir_cur_id,1,x_ii_rltns_tbl(l_ind).txn_relationship_id);
1262 dbms_sql.column_value(l_iir_cur_id,2,x_ii_rltns_tbl(l_ind).transaction_line_id);
1263 dbms_sql.column_value(l_iir_cur_id,3,x_ii_rltns_tbl(l_ind).csi_inst_relationship_id);
1264 dbms_sql.column_value(l_iir_cur_id,4,x_ii_rltns_tbl(l_ind).subject_id);
1265 dbms_sql.column_value(l_iir_cur_id,5,x_ii_rltns_tbl(l_ind).object_id);
1266 dbms_sql.column_value(l_iir_cur_id,6,x_ii_rltns_tbl(l_ind).relationship_type_code);
1267 dbms_sql.column_value(l_iir_cur_id,7,x_ii_rltns_tbl(l_ind).display_order);
1268 dbms_sql.column_value(l_iir_cur_id,8,x_ii_rltns_tbl(l_ind).position_reference);
1269 dbms_sql.column_value(l_iir_cur_id,9,x_ii_rltns_tbl(l_ind).mandatory_flag);
1270 dbms_sql.column_value(l_iir_cur_id,10,x_ii_rltns_tbl(l_ind).active_start_date);
1271 dbms_sql.column_value(l_iir_cur_id,11,x_ii_rltns_tbl(l_ind).active_end_date);
1272 dbms_sql.column_value(l_iir_cur_id,12,x_ii_rltns_tbl(l_ind).context);
1273 dbms_sql.column_value(l_iir_cur_id,13,x_ii_rltns_tbl(l_ind).attribute1);
1274 dbms_sql.column_value(l_iir_cur_id,14,x_ii_rltns_tbl(l_ind).attribute2);
1275 dbms_sql.column_value(l_iir_cur_id,15,x_ii_rltns_tbl(l_ind).attribute3);
1276 dbms_sql.column_value(l_iir_cur_id,16,x_ii_rltns_tbl(l_ind).attribute4);
1277 dbms_sql.column_value(l_iir_cur_id,17,x_ii_rltns_tbl(l_ind).attribute5);
1278 dbms_sql.column_value(l_iir_cur_id,18,x_ii_rltns_tbl(l_ind).attribute6);
1279 dbms_sql.column_value(l_iir_cur_id,19,x_ii_rltns_tbl(l_ind).attribute7);
1280 dbms_sql.column_value(l_iir_cur_id,20,x_ii_rltns_tbl(l_ind).attribute8);
1281 dbms_sql.column_value(l_iir_cur_id,21,x_ii_rltns_tbl(l_ind).attribute9);
1282 dbms_sql.column_value(l_iir_cur_id,22,x_ii_rltns_tbl(l_ind).attribute10);
1283 dbms_sql.column_value(l_iir_cur_id,23,x_ii_rltns_tbl(l_ind).attribute11);
1284 dbms_sql.column_value(l_iir_cur_id,24,x_ii_rltns_tbl(l_ind).attribute12);
1285 dbms_sql.column_value(l_iir_cur_id,25,x_ii_rltns_tbl(l_ind).attribute13);
1286 dbms_sql.column_value(l_iir_cur_id,26,x_ii_rltns_tbl(l_ind).attribute14);
1287 dbms_sql.column_value(l_iir_cur_id,27,x_ii_rltns_tbl(l_ind).attribute15);
1288 dbms_sql.column_value(l_iir_cur_id,28,x_ii_rltns_tbl(l_ind).object_version_number);
1289 dbms_sql.column_value(l_iir_cur_id,29,x_ii_rltns_tbl(l_ind).transfer_components_flag);
1290 END LOOP;
1291
1292 dbms_sql.close_cursor(l_iir_cur_id);
1293
1294 EXCEPTION
1295 WHEN others THEN
1296
1297 IF dbms_sql.is_open(l_iir_cur_id) THEN
1298 dbms_sql.close_cursor(l_iir_cur_id);
1299 END IF;
1300 END get_ii_rltns_dtls;
1301
1302 END csi_t_txn_rltnshps_pvt;