[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_OUS_PVT
Source
1 PACKAGE BODY csi_t_txn_ous_pvt AS
2 /* $Header: csivtoub.pls 115.8 2002/11/12 00:31:37 rmamidip noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_ous_pvt';
5 g_file_name CONSTANT VARCHAR2(12) := 'csivtoub.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_org_assgn_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_org_assgn_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_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) := 'create_txn_org_assgn_dtls';
23 l_api_version CONSTANT NUMBER := 1.0;
24 l_debug_level NUMBER;
25 l_txn_operating_unit_id NUMBER;
26 l_preserve_detail_flag VARCHAR2(1);
27
28 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
29 l_valid BOOLEAN;
30
31 BEGIN
32
33 -- Standard Start of API savepoint
34 SAVEPOINT create_txn_org_assgn_dtls;
35
36 -- Initialize message list if p_init_msg_list is set to TRUE.
37 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
38 fnd_msg_pub.initialize;
39 END IF;
40
41 -- Initialize API return status to success
42 x_return_status := fnd_api.G_RET_STS_SUCCESS;
43
44 -- Standard call to check for call compatibility.
45 IF NOT
46
47 fnd_api.Compatible_API_Call (
48 p_current_version_number => l_api_version,
49 p_caller_version_number => p_api_version,
50 p_api_name => l_api_name,
51 p_pkg_name => G_PKG_NAME) THEN
52
53 RAISE fnd_api.g_exc_unexpected_error;
54
55 END IF;
56
57 --debug info
58 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
59
60 csi_t_gen_utility_pvt.dump_api_info(
61 p_pkg_name => g_pkg_name,
62 p_api_name => l_api_name);
63
64 csi_t_gen_utility_pvt.add(
65 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
66
67 IF l_debug_level > 1 THEN
68 csi_t_gen_utility_pvt.dump_org_assgn_rec(
69 p_org_assgn_rec => p_txn_org_assgn_rec);
70 END IF;
71
72 -- Main API code
73 csi_t_vldn_routines_pvt.check_reqd_param(
74 p_value => p_txn_org_assgn_rec.operating_unit_id,
75 p_param_name => 'p_txn_org_assgn_rec.operating_unit_id',
76 p_api_name => l_api_name);
77
78 csi_t_vldn_routines_pvt.check_reqd_param(
79 p_value => p_txn_org_assgn_rec.relationship_type_code,
80 p_param_name => 'p_txn_org_assgn_rec.relationship_type_code',
81 p_api_name => l_api_name);
82
83 -- validate operating unit
84 l_valid :=
85 csi_org_unit_vld_pvt.is_valid_operating_unit_id(
86 p_operating_unit_id => p_txn_org_assgn_rec.operating_unit_id);
87 IF NOT (l_valid) THEN
88 csi_t_gen_utility_pvt.add('Validate operating unit id failed.');
89 RAISE fnd_api.g_exc_error;
90 END IF;
91
92 -- validate relationship_type_code
93 l_valid :=
94 csi_org_unit_vld_pvt.is_valid_rel_type_code(
95 p_relationship_type_code => p_txn_org_assgn_rec.relationship_type_code);
96
97 IF NOT (l_valid) THEN
98 csi_t_gen_utility_pvt.add('Validate ou relationship type code failed.');
99 RAISE fnd_api.g_exc_error;
100 END IF;
101
102 IF nvl(p_txn_org_assgn_rec.instance_ou_id,fnd_api.g_miss_num) <>
103 fnd_api.g_miss_num
104 THEN
105
106 csi_t_vldn_routines_pvt.validate_instance_reference(
107 p_level => 'ORG_ASSGN',
108 p_level_dtl_id => p_txn_org_assgn_rec.txn_line_detail_id,
109 p_level_inst_ref_id => p_txn_org_assgn_rec.instance_ou_id,
110 x_return_status => l_return_status);
111
112 IF l_return_status <> fnd_api.g_ret_sts_success THEN
113 RAISE fnd_api.g_exc_error;
114 END IF;
115
116 END IF;
117
118 SELECT decode(nvl(p_txn_org_assgn_rec.preserve_detail_flag,fnd_api.g_miss_char),
119 fnd_api.g_miss_char, 'Y', p_txn_org_assgn_rec.preserve_detail_flag)
120 INTO l_preserve_detail_flag
121 FROM sys.dual;
122
123 -- call table handler
124
125 if nvl(p_txn_org_assgn_rec.txn_operating_unit_id,fnd_api.g_miss_num) <>
126 fnd_api.g_miss_num then
127 l_txn_operating_unit_id := p_txn_org_assgn_rec.txn_operating_unit_id;
128 end if;
129
130 --debug info
131 begin
132
133 csi_t_gen_utility_pvt.dump_api_info(
134 p_api_name => 'insert_row',
135 p_pkg_name => 'csi_t_org_assignments_pkg');
136
137 csi_t_org_assignments_pkg.insert_row(
138 px_txn_operating_unit_id => l_txn_operating_unit_id,
139 p_txn_line_detail_id => p_txn_org_assgn_rec.txn_line_detail_id,
140 p_operating_unit_id => p_txn_org_assgn_rec.operating_unit_id,
141 p_relationship_type_code => p_txn_org_assgn_rec.relationship_type_code,
142 p_active_start_date => p_txn_org_assgn_rec.active_start_date,
143 p_active_end_date => p_txn_org_assgn_rec.active_end_date,
144 p_preserve_detail_flag => l_preserve_detail_flag,
145 p_instance_ou_id => p_txn_org_assgn_rec.instance_ou_id,
146 p_attribute1 => p_txn_org_assgn_rec.attribute1,
147 p_attribute2 => p_txn_org_assgn_rec.attribute2,
148 p_attribute3 => p_txn_org_assgn_rec.attribute3,
149 p_attribute4 => p_txn_org_assgn_rec.attribute4,
150 p_attribute5 => p_txn_org_assgn_rec.attribute5,
151 p_attribute6 => p_txn_org_assgn_rec.attribute6,
152 p_attribute7 => p_txn_org_assgn_rec.attribute7,
153 p_attribute8 => p_txn_org_assgn_rec.attribute8,
154 p_attribute9 => p_txn_org_assgn_rec.attribute9,
155 p_attribute10 => p_txn_org_assgn_rec.attribute10,
156 p_attribute11 => p_txn_org_assgn_rec.attribute11,
157 p_attribute12 => p_txn_org_assgn_rec.attribute12,
158 p_attribute13 => p_txn_org_assgn_rec.attribute13,
159 p_attribute14 => p_txn_org_assgn_rec.attribute14,
160 p_attribute15 => p_txn_org_assgn_rec.attribute15,
161 p_created_by => g_user_id,
162 p_creation_date => sysdate,
163 p_last_updated_by => g_user_id,
164 p_last_update_date => sysdate,
165 p_last_update_login => g_login_id,
166 p_object_version_number => 1.0,
167 p_context => p_txn_org_assgn_rec.context);
168
169 exception
170 when others then
171 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
172 fnd_message.set_token('MESSAGE',
173 'csi_t_org_assignments_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
174 fnd_msg_pub.add;
175 raise fnd_api.g_exc_error;
176 end;
177
178 p_txn_org_assgn_rec.txn_operating_unit_id := l_txn_operating_unit_id;
179
180 -- Standard check of p_commit.
181 IF fnd_api.To_Boolean( p_commit ) THEN
182 COMMIT WORK;
183 END IF;
184
185 IF csi_t_gen_utility_pvt.g_debug = fnd_api.g_true THEN
186 csi_t_gen_utility_pvt.set_debug_off;
187 END IF;
188
189 -- Standard call to get message count and if count is get message info.
190 fnd_msg_pub.count_and_get(
191 p_count => x_msg_count,
192 p_data => x_msg_data);
193
194 EXCEPTION
195 WHEN fnd_api.G_EXC_ERROR THEN
196
197 ROLLBACK TO Create_Txn_Org_Assgn_Dtls;
198 x_return_status := fnd_api.G_RET_STS_ERROR ;
199 fnd_msg_pub.count_and_get (
200 p_count => x_msg_count,
201 p_data => x_msg_data);
202
203 WHEN fnd_api.g_exc_unexpected_error THEN
204
205 ROLLBACK TO Create_Txn_Org_Assgn_Dtls;
206 x_return_status := fnd_api.g_ret_sts_unexp_error ;
207
208 fnd_msg_pub.count_and_get(
209 p_count => x_msg_count,
210 p_data => x_msg_data);
211
212 WHEN OTHERS THEN
213
214 ROLLBACK TO Create_Txn_Org_Assgn_Dtls;
215 x_return_status := fnd_api.g_ret_sts_unexp_error ;
216
217 IF fnd_msg_pub.check_msg_level(
218 p_message_level => fnd_msg_pub.g_msg_lvl_unexp_error) THEN
219
220 fnd_msg_pub.add_exc_msg(
221 p_pkg_name => G_PKG_NAME,
222 p_procedure_name => l_api_name);
223
224 END IF;
225
226 fnd_msg_pub.count_and_get(
227 p_count => x_msg_count,
228 p_data => x_msg_data);
229
230 END create_txn_org_assgn_dtls;
231
232 PROCEDURE update_txn_org_assgn_dtls(
233 p_api_version IN NUMBER
234 ,p_commit IN VARCHAR2 := fnd_api.g_false
235 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
236 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
237 ,p_txn_org_assgn_tbl IN csi_t_datastructures_grp.txn_org_assgn_tbl
238 ,x_return_status OUT NOCOPY VARCHAR2
239 ,x_msg_count OUT NOCOPY NUMBER
240 ,x_msg_data OUT NOCOPY VARCHAR2)
241 IS
242
243 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_org_assgn_dtls';
244 l_api_version CONSTANT NUMBER := 1.0;
245 l_debug_level NUMBER;
246
247 l_return_status VARCHAR2(1);
248 l_ou_rec csi_t_org_assignments%rowtype;
249 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
250 l_valid BOOLEAN := TRUE;
251
252 CURSOR ou_cur (l_txn_ou_id in number) IS
253 SELECT *
254 FROM csi_t_org_assignments
255 WHERE txn_operating_unit_id = l_txn_ou_id;
256
257 BEGIN
258
259 -- Standard Start of API savepoint
260 SAVEPOINT update_txn_org_assgn_dtls;
261
262 -- Initialize message list if p_init_msg_list is set to TRUE.
263 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
264 fnd_msg_pub.initialize;
265 END IF;
266
267 -- Initialize API return status to success
268 x_return_status := fnd_api.G_RET_STS_SUCCESS;
269
270 -- Standard call to check for call compatibility.
271 IF NOT
272
273 fnd_api.Compatible_API_Call (
274 p_current_version_number => l_api_version,
275 p_caller_version_number => p_api_version,
276 p_api_name => l_api_name,
277 p_pkg_name => G_PKG_NAME) THEN
278
279 RAISE fnd_api.g_exc_unexpected_error;
280
281 END IF;
282
283 --debug info
284 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
285
286 csi_t_gen_utility_pvt.dump_api_info(
287 p_pkg_name => g_pkg_name,
288 p_api_name => l_api_name);
289
290 csi_t_gen_utility_pvt.add(
291 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
292
293 -- Main API code
294 IF p_txn_org_assgn_tbl.COUNT > 0 THEN
295
296 FOR l_ind in p_txn_org_assgn_tbl.FIRST..p_txn_org_assgn_tbl.LAST
297 LOOP
298
299 IF l_debug_level > 1 THEN
300 csi_t_gen_utility_pvt.dump_org_assgn_rec(
301 p_org_assgn_rec => p_txn_org_assgn_tbl(l_ind));
302 END IF;
303
304 l_ou_rec.txn_operating_unit_id :=
305 p_txn_org_assgn_tbl(l_ind).txn_operating_unit_id;
306
307 csi_t_vldn_routines_pvt.check_reqd_param(
308 p_value => l_ou_rec.txn_operating_unit_id,
309 p_param_name => 'l_ou_rec.txn_operating_unit_id',
310 p_api_name => l_api_name);
311
312 csi_t_vldn_routines_pvt.validate_txn_ou_id(
313 p_txn_operating_unit_id => l_ou_rec.txn_operating_unit_id,
314 x_return_status => l_return_status);
315
316 IF l_return_status <> fnd_api.g_ret_sts_success THEN
317
318 FND_MESSAGE.set_name('CSI','CSI_TXN_OU_ID_INVALID');
319 FND_MESSAGE.set_token('TXN_OU_ID',l_ou_rec.txn_operating_unit_id);
320 fnd_msg_pub.add;
321 RAISE fnd_api.g_exc_error;
322
323 END IF;
324
325 FOR l_ou_cur_rec in ou_cur(l_ou_rec.txn_operating_unit_id)
326 LOOP
327
328 l_ou_rec.txn_line_detail_id := l_ou_cur_rec.txn_line_detail_id;
329
330 csi_t_vldn_routines_pvt.get_processing_status(
331 p_level => 'ORG_ASSGN',
332 p_level_dtl_id => l_ou_rec.txn_line_detail_id,
333 x_processing_status => l_processing_status,
334 x_return_status => l_return_status);
335
336 IF l_processing_status = 'PROCESSED' THEN
337
338 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
339 FND_MESSAGE.set_token('LVL_ID', l_ou_rec.txn_operating_unit_id);
340 FND_MESSAGE.set_token('STATUS',l_processing_status);
341 fnd_msg_pub.add;
342 RAISE fnd_api.g_exc_error;
343
344 END IF;
345
346 l_ou_rec.operating_unit_id :=
347 p_txn_org_assgn_tbl(l_ind).operating_unit_id;
348
349 -- validate operating_unit_id
350 IF l_ou_rec.operating_unit_id <> fnd_api.g_miss_num THEN
351
352 l_valid :=
353 csi_org_unit_vld_pvt.is_valid_operating_unit_id(
354 p_operating_unit_id => l_ou_rec.operating_unit_id);
355 IF NOT (l_valid) THEN
356 csi_t_gen_utility_pvt.add('Validate operating unit id failed.');
357 RAISE fnd_api.g_exc_error;
358 END IF;
359
360 END IF;
361
362 l_ou_rec.relationship_type_code:=
363 p_txn_org_assgn_tbl(l_ind).relationship_type_code;
364
365 -- validate org relationship_type_code
366 IF l_ou_rec.relationship_type_code <> fnd_api.g_miss_char THEN
367
368 l_valid :=
369 csi_org_unit_vld_pvt.is_valid_rel_type_code(
370 p_relationship_type_code => l_ou_rec.relationship_type_code);
371 IF NOT (l_valid) THEN
372 csi_t_gen_utility_pvt.add('Validate ou relationship type code failed.');
373 RAISE fnd_api.g_exc_error;
374 END IF;
375
376 END IF;
377
378 l_ou_rec.active_start_date :=
379 p_txn_org_assgn_tbl(l_ind).active_start_date;
380
381 l_ou_rec.active_end_date :=
382 p_txn_org_assgn_tbl(l_ind).active_end_date;
383
384 l_ou_rec.preserve_detail_flag :=
385 p_txn_org_assgn_tbl(l_ind).preserve_detail_flag;
386
387 l_ou_rec.instance_ou_id :=
388 p_txn_org_assgn_tbl(l_ind).instance_ou_id;
389
390 -- validate instance_ou_id
391 IF nvl(l_ou_rec.instance_ou_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
392 THEN
393
394 l_valid :=
395 csi_org_unit_vld_pvt.is_valid_instance_ou_id(
396 p_instance_ou_id => l_ou_rec.instance_ou_id);
397 IF (l_valid) THEN
398 csi_t_gen_utility_pvt.add('Validate instance ou id failed.');
399 RAISE fnd_api.g_exc_error;
400 END IF;
401
402 END IF;
403
404 l_ou_rec.attribute1 := p_txn_org_assgn_tbl(l_ind).attribute1;
405 l_ou_rec.attribute2 := p_txn_org_assgn_tbl(l_ind).attribute2;
406 l_ou_rec.attribute3 := p_txn_org_assgn_tbl(l_ind).attribute3;
407 l_ou_rec.attribute4 := p_txn_org_assgn_tbl(l_ind).attribute4;
408 l_ou_rec.attribute5 := p_txn_org_assgn_tbl(l_ind).attribute5;
409 l_ou_rec.attribute6 := p_txn_org_assgn_tbl(l_ind).attribute6;
410 l_ou_rec.attribute7 := p_txn_org_assgn_tbl(l_ind).attribute7;
411 l_ou_rec.attribute8 := p_txn_org_assgn_tbl(l_ind).attribute8;
412 l_ou_rec.attribute9 := p_txn_org_assgn_tbl(l_ind).attribute9;
413 l_ou_rec.attribute10 := p_txn_org_assgn_tbl(l_ind).attribute10;
414 l_ou_rec.attribute11 := p_txn_org_assgn_tbl(l_ind).attribute11;
415 l_ou_rec.attribute12 := p_txn_org_assgn_tbl(l_ind).attribute12;
416 l_ou_rec.attribute13 := p_txn_org_assgn_tbl(l_ind).attribute13;
417 l_ou_rec.attribute14 := p_txn_org_assgn_tbl(l_ind).attribute14;
418 l_ou_rec.attribute15 := p_txn_org_assgn_tbl(l_ind).attribute15;
419 l_ou_rec.created_by := l_ou_cur_rec.created_by;
420 l_ou_rec.creation_date := l_ou_cur_rec.creation_date;
421 l_ou_rec.last_updated_by := g_user_id;
422 l_ou_rec.last_update_date := sysdate;
423 l_ou_rec.last_update_login := g_login_id;
424 l_ou_rec.object_version_number := p_txn_org_assgn_tbl(l_ind).object_version_number;
425 l_ou_rec.context := p_txn_org_assgn_tbl(l_ind).context;
426
427 --debug info
428 begin
429
430 csi_t_gen_utility_pvt.dump_api_info(
431 p_pkg_name => 'csi_t_org_assignments_pkg',
432 p_api_name => 'update_row');
433
434 csi_t_org_assignments_pkg.update_row(
435 p_txn_operating_unit_id => l_ou_rec.txn_operating_unit_id,
436 p_txn_line_detail_id => l_ou_rec.txn_line_detail_id,
437 p_operating_unit_id => l_ou_rec.operating_unit_id,
438 p_relationship_type_code=> l_ou_rec.relationship_type_code,
439 p_active_start_date => l_ou_rec.active_start_date,
440 p_active_end_date => l_ou_rec.active_end_date,
441 p_preserve_detail_flag => l_ou_rec.preserve_detail_flag,
442 p_instance_ou_id => l_ou_rec.instance_ou_id,
443 p_attribute1 => l_ou_rec.attribute1,
444 p_attribute2 => l_ou_rec.attribute2,
445 p_attribute3 => l_ou_rec.attribute3,
446 p_attribute4 => l_ou_rec.attribute4,
447 p_attribute5 => l_ou_rec.attribute5,
448 p_attribute6 => l_ou_rec.attribute6,
449 p_attribute7 => l_ou_rec.attribute7,
450 p_attribute8 => l_ou_rec.attribute8,
451 p_attribute9 => l_ou_rec.attribute9,
452 p_attribute10 => l_ou_rec.attribute10,
453 p_attribute11 => l_ou_rec.attribute11,
454 p_attribute12 => l_ou_rec.attribute12,
455 p_attribute13 => l_ou_rec.attribute13,
456 p_attribute14 => l_ou_rec.attribute14,
457 p_attribute15 => l_ou_rec.attribute15,
458 p_created_by => l_ou_rec.created_by,
459 p_creation_date => l_ou_rec.creation_date,
460 p_last_updated_by => l_ou_rec.last_updated_by,
461 p_last_update_date => l_ou_rec.last_update_date,
462 p_last_update_login => l_ou_rec.last_update_login,
463 p_object_version_number => l_ou_rec.object_version_number,
464 p_context => l_ou_rec.context);
465
466 exception
467 when others then
468 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
469 fnd_message.set_token('MESSAGE',
470 'csi_t_org_assignments_pkg.update_row failed. '||substr(sqlerrm,1,200));
471 fnd_msg_pub.add;
472 raise fnd_api.g_exc_error;
473 end;
474
475 END LOOP;
476 END LOOP;
477
478 END IF;
479
480 -- Standard check of p_commit.
481 IF fnd_api.To_Boolean( p_commit ) THEN
482 COMMIT WORK;
483 END IF;
484
485 -- Standard call to get message count and if count is get message info.
486 fnd_msg_pub.count_and_get(
487 p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 EXCEPTION
491 WHEN fnd_api.G_EXC_ERROR THEN
492
493 ROLLBACK TO update_txn_org_assgn_dtls;
494 x_return_status := fnd_api.G_RET_STS_ERROR ;
495 fnd_msg_pub.count_and_get (
496 p_count => x_msg_count,
497 p_data => x_msg_data);
498
499 WHEN fnd_api.g_exc_unexpected_error THEN
500
501 ROLLBACK TO update_txn_org_assgn_dtls;
502 x_return_status := fnd_api.g_ret_sts_unexp_error ;
503
504 fnd_msg_pub.count_and_get(
505 p_count => x_msg_count,
506 p_data => x_msg_data);
507
508 WHEN OTHERS THEN
509
510 ROLLBACK TO update_txn_org_assgn_dtls;
511 x_return_status := fnd_api.g_ret_sts_unexp_error ;
512
513 IF fnd_msg_pub.check_msg_level(
514 p_message_level => fnd_msg_pub.g_msg_lvl_unexp_error) THEN
515
516 fnd_msg_pub.add_exc_msg(
517 p_pkg_name => G_PKG_NAME,
518 p_procedure_name => l_api_name);
519
520 END IF;
521
522 fnd_msg_pub.count_and_get(
523 p_count => x_msg_count,
524 p_data => x_msg_data);
525
526 END update_txn_org_assgn_dtls;
527
528 PROCEDURE delete_txn_org_assgn_dtls(
529 p_api_version IN NUMBER
530 ,p_commit IN VARCHAR2 := fnd_api.g_false
531 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
532 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
533 ,p_txn_org_assgn_ids_tbl IN csi_t_datastructures_grp.txn_org_assgn_ids_tbl
534 ,x_return_status OUT NOCOPY VARCHAR2
535 ,x_msg_count OUT NOCOPY NUMBER
536 ,x_msg_data OUT NOCOPY VARCHAR2)
537 IS
538
539 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_org_assgn_dtls';
540 l_api_version CONSTANT NUMBER := 1.0;
541 l_debug_level NUMBER;
542 l_return_status VARCHAR2(1);
543 l_txn_ou_id NUMBER;
544
545 l_line_dtl_id NUMBER;
546
547 CURSOR ou_cur (p_line_dtl_id IN NUMBER) IS
548 SELECT txn_operating_unit_id
549 FROM csi_t_org_assignments
550 WHERE txn_line_detail_id = p_line_dtl_id;
551
552 BEGIN
553
554 -- Standard Start of API savepoint
555 SAVEPOINT delete_txn_org_assgn_dtls;
556
557 -- Initialize message list if p_init_msg_list is set to TRUE.
558 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
559 fnd_msg_pub.initialize;
560 END IF;
561
562 -- Initialize API return status to success
563 x_return_status := fnd_api.G_RET_STS_SUCCESS;
564
565 -- Standard call to check for call compatibility.
566 IF NOT
567
568 fnd_api.Compatible_API_Call (
569 p_current_version_number => l_api_version,
570 p_caller_version_number => p_api_version,
571 p_api_name => l_api_name,
572 p_pkg_name => G_PKG_NAME) THEN
573
574 RAISE fnd_api.g_exc_unexpected_error;
575
576 END IF;
577
578 --debug info
579 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
580
581 csi_t_gen_utility_pvt.dump_api_info(
582 p_pkg_name => g_pkg_name,
583 p_api_name => l_api_name);
584
585 csi_t_gen_utility_pvt.add(
586 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
587
588 -- Main API code
589 IF p_txn_org_assgn_ids_tbl.COUNT > 0 THEN
590
591 FOR l_ind in p_txn_org_assgn_ids_tbl.FIRST..p_txn_org_assgn_ids_tbl.LAST
592 LOOP
593
594 IF l_debug_level > 1 THEN
595 null; --##
596 END IF;
597
598 l_txn_ou_id := p_txn_org_assgn_ids_tbl(l_ind).txn_operating_unit_id;
599
600 IF nvl(l_txn_ou_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
601
602 csi_t_vldn_routines_pvt.validate_txn_ou_id(
603 p_txn_operating_unit_id => l_txn_ou_id,
604 x_return_status => l_return_status);
605
606 IF l_return_status <> fnd_api.g_ret_sts_success THEN
607
608 FND_MESSAGE.set_name('CSI','CSI_TXN_OU_ID_INVALID');
609 FND_MESSAGE.set_token('TXN_OU_ID',l_txn_ou_id);
610 fnd_msg_pub.add;
611 RAISE fnd_api.g_exc_error;
612
613 END IF;
614
615 --debug info
616 csi_t_gen_utility_pvt.dump_api_info(
617 p_api_name => 'delete_row',
618 p_pkg_name => 'csi_t_org_assignments_pkg');
619
620 csi_t_org_assignments_pkg.delete_row(
621 p_txn_operating_unit_id => l_txn_ou_id);
622
623 ELSE
624
625 l_line_dtl_id := p_txn_org_assgn_ids_tbl(l_ind).txn_line_detail_id;
626
627 csi_t_vldn_routines_pvt.check_reqd_param(
628 p_value => l_line_dtl_id,
629 p_param_name => 'p_txn_org_assgn_ids_tbl.txn_line_detail_id',
630 p_api_name => l_api_name);
631
632 -- validate txn_line_detail_id
633 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
634 p_txn_line_detail_id => l_line_dtl_id,
635 x_return_status => l_return_status);
636
637 IF l_return_status <> fnd_api.g_ret_sts_success THEN
638
639 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_DTL_ID_INVALID');
640 FND_MESSAGE.set_token('LINE_DTL_ID',l_line_dtl_id);
641 fnd_msg_pub.add;
642 RAISE fnd_api.g_exc_error;
643
644 END IF;
645
646 FOR ou_rec in ou_cur (l_line_dtl_id)
647 LOOP
648
649 csi_t_gen_utility_pvt.dump_api_info(
650 p_api_name => 'delete_row',
651 p_pkg_name => 'csi_t_org_assignments_pkg');
652
653 csi_t_org_assignments_pkg.delete_row(
654 p_txn_operating_unit_id => ou_rec.txn_operating_unit_id);
655
656 END LOOP;
657
658 END IF;
659
660 END LOOP;
661
662 END IF;
663
664 -- Standard check of p_commit.
665 IF fnd_api.To_Boolean( p_commit ) THEN
666 COMMIT WORK;
667 END IF;
668
669 -- Standard call to get message count and if count is get message info.
670 fnd_msg_pub.count_and_get(
671 p_count => x_msg_count,
672 p_data => x_msg_data);
673
674 EXCEPTION
675 WHEN fnd_api.G_EXC_ERROR THEN
676
677 ROLLBACK TO delete_txn_org_assgn_dtls;
678 x_return_status := fnd_api.G_RET_STS_ERROR ;
679 fnd_msg_pub.count_and_get (
680 p_count => x_msg_count,
681 p_data => x_msg_data);
682
683 WHEN fnd_api.g_exc_unexpected_error THEN
684
685 ROLLBACK TO delete_txn_org_assgn_dtls;
686 x_return_status := fnd_api.g_ret_sts_unexp_error ;
687
688 fnd_msg_pub.count_and_get(
689 p_count => x_msg_count,
690 p_data => x_msg_data);
691
692 WHEN OTHERS THEN
693
694 ROLLBACK TO delete_txn_org_assgn_dtls;
695 x_return_status := fnd_api.g_ret_sts_unexp_error ;
696
697 IF fnd_msg_pub.check_msg_level(
698 p_message_level => fnd_msg_pub.g_msg_lvl_unexp_error) THEN
699
700 fnd_msg_pub.add_exc_msg(
701 p_pkg_name => G_PKG_NAME,
702 p_procedure_name => l_api_name);
703
704 END IF;
705
706 fnd_msg_pub.count_and_get(
707 p_count => x_msg_count,
708 p_data => x_msg_data);
709
710 END delete_txn_org_assgn_dtls;
711
712 PROCEDURE get_org_assgn_dtls(
713 p_line_dtl_id in number,
714 x_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
715 x_return_status OUT NOCOPY varchar2)
716 IS
717
718 l_select_stmt varchar2(2000);
719 l_oa_cur_id integer;
720 l_oa_rec csi_t_datastructures_grp.txn_org_assgn_rec;
721 l_processed_rows number := 0;
722 l_ind binary_integer;
723
724 BEGIN
725
726 l_select_stmt :=
727 'select txn_operating_unit_id, txn_line_detail_id, instance_ou_id, '||
728 ' operating_unit_id, relationship_type_code, active_start_date, active_end_date, '||
729 ' preserve_detail_flag, context, attribute1, attribute2, attribute3, attribute4, '||
730 ' attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, '||
731 ' attribute11, attribute12, attribute13, attribute14, attribute15, object_version_number '||
732 'from csi_t_org_assignments '||
733 'where txn_line_detail_id = :line_dtl_id';
734
735 l_oa_cur_id := dbms_sql.open_cursor;
736
737 dbms_sql.parse(l_oa_cur_id, l_select_stmt , dbms_sql.native);
738
739 dbms_sql.bind_variable(l_oa_cur_id, 'line_dtl_id', p_line_dtl_id);
740
741 dbms_sql.define_column(l_oa_cur_id,1,l_oa_rec.txn_operating_unit_id);
742 dbms_sql.define_column(l_oa_cur_id,2,l_oa_rec.txn_line_detail_id);
743 dbms_sql.define_column(l_oa_cur_id,3,l_oa_rec.instance_ou_id);
744 dbms_sql.define_column(l_oa_cur_id,4,l_oa_rec.operating_unit_id);
745 dbms_sql.define_column(l_oa_cur_id,5,l_oa_rec.relationship_type_code,30);
746 dbms_sql.define_column(l_oa_cur_id,6,l_oa_rec.active_start_date);
747 dbms_sql.define_column(l_oa_cur_id,7,l_oa_rec.active_end_date);
748 dbms_sql.define_column(l_oa_cur_id,8,l_oa_rec.preserve_detail_flag,1);
749 dbms_sql.define_column(l_oa_cur_id,9,l_oa_rec.context,30);
750 dbms_sql.define_column(l_oa_cur_id,10,l_oa_rec.attribute1,150);
751 dbms_sql.define_column(l_oa_cur_id,11,l_oa_rec.attribute2,150);
752 dbms_sql.define_column(l_oa_cur_id,12,l_oa_rec.attribute3,150);
753 dbms_sql.define_column(l_oa_cur_id,13,l_oa_rec.attribute4,150);
754 dbms_sql.define_column(l_oa_cur_id,14,l_oa_rec.attribute5,150);
755 dbms_sql.define_column(l_oa_cur_id,15,l_oa_rec.attribute6,150);
756 dbms_sql.define_column(l_oa_cur_id,16,l_oa_rec.attribute7,150);
757 dbms_sql.define_column(l_oa_cur_id,17,l_oa_rec.attribute8,150);
758 dbms_sql.define_column(l_oa_cur_id,18,l_oa_rec.attribute9,150);
759 dbms_sql.define_column(l_oa_cur_id,19,l_oa_rec.attribute10,150);
760 dbms_sql.define_column(l_oa_cur_id,20,l_oa_rec.attribute11,150);
761 dbms_sql.define_column(l_oa_cur_id,21,l_oa_rec.attribute12,150);
762 dbms_sql.define_column(l_oa_cur_id,22,l_oa_rec.attribute13,150);
763 dbms_sql.define_column(l_oa_cur_id,23,l_oa_rec.attribute14,150);
764 dbms_sql.define_column(l_oa_cur_id,24,l_oa_rec.attribute15,150);
765 dbms_sql.define_column(l_oa_cur_id,25,l_oa_rec.object_version_number);
766 l_ind := 0;
767
768 l_processed_rows := dbms_sql.execute(l_oa_cur_id);
769 LOOP
770 exit when dbms_sql.fetch_rows(l_oa_cur_id) = 0;
771
772 l_ind := l_ind + 1;
773
774 dbms_sql.column_value(l_oa_cur_id,1,x_org_assgn_tbl(l_ind).txn_operating_unit_id);
775 dbms_sql.column_value(l_oa_cur_id,2,x_org_assgn_tbl(l_ind).txn_line_detail_id);
776 dbms_sql.column_value(l_oa_cur_id,3,x_org_assgn_tbl(l_ind).instance_ou_id);
777 dbms_sql.column_value(l_oa_cur_id,4,x_org_assgn_tbl(l_ind).operating_unit_id);
778 dbms_sql.column_value(l_oa_cur_id,5,x_org_assgn_tbl(l_ind).relationship_type_code);
779 dbms_sql.column_value(l_oa_cur_id,6,x_org_assgn_tbl(l_ind).active_start_date);
780 dbms_sql.column_value(l_oa_cur_id,7,x_org_assgn_tbl(l_ind).active_end_date);
781 dbms_sql.column_value(l_oa_cur_id,8,x_org_assgn_tbl(l_ind).preserve_detail_flag);
782 dbms_sql.column_value(l_oa_cur_id,9,x_org_assgn_tbl(l_ind).context);
783 dbms_sql.column_value(l_oa_cur_id,10,x_org_assgn_tbl(l_ind).attribute1);
784 dbms_sql.column_value(l_oa_cur_id,11,x_org_assgn_tbl(l_ind).attribute2);
785 dbms_sql.column_value(l_oa_cur_id,12,x_org_assgn_tbl(l_ind).attribute3);
786 dbms_sql.column_value(l_oa_cur_id,13,x_org_assgn_tbl(l_ind).attribute4);
787 dbms_sql.column_value(l_oa_cur_id,14,x_org_assgn_tbl(l_ind).attribute5);
788 dbms_sql.column_value(l_oa_cur_id,15,x_org_assgn_tbl(l_ind).attribute6);
789 dbms_sql.column_value(l_oa_cur_id,16,x_org_assgn_tbl(l_ind).attribute7);
790 dbms_sql.column_value(l_oa_cur_id,17,x_org_assgn_tbl(l_ind).attribute8);
791 dbms_sql.column_value(l_oa_cur_id,18,x_org_assgn_tbl(l_ind).attribute9);
792 dbms_sql.column_value(l_oa_cur_id,19,x_org_assgn_tbl(l_ind).attribute10);
793 dbms_sql.column_value(l_oa_cur_id,20,x_org_assgn_tbl(l_ind).attribute11);
794 dbms_sql.column_value(l_oa_cur_id,21,x_org_assgn_tbl(l_ind).attribute12);
795 dbms_sql.column_value(l_oa_cur_id,22,x_org_assgn_tbl(l_ind).attribute13);
796 dbms_sql.column_value(l_oa_cur_id,23,x_org_assgn_tbl(l_ind).attribute14);
797 dbms_sql.column_value(l_oa_cur_id,24,x_org_assgn_tbl(l_ind).attribute15);
798 dbms_sql.column_value(l_oa_cur_id,25,x_org_assgn_tbl(l_ind).object_version_number);
799
800 END LOOP;
801
802 dbms_sql.close_cursor(l_oa_cur_id);
803
804 EXCEPTION
805 WHEN others THEN
806
807 IF dbms_sql.is_open(l_oa_cur_id) THEN
808 dbms_sql.close_cursor(l_oa_cur_id);
809 END IF;
810 END get_org_assgn_dtls;
811
812 PROCEDURE get_all_org_assgn_dtls(
813 p_txn_line_detail_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
814 x_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
815 x_return_status OUT NOCOPY varchar2)
816 IS
817 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
818 l_c_ind binary_integer := 0;
819 l_oa_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
820 l_tmp_oa_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
821 BEGIN
822 x_return_status := fnd_api.g_ret_sts_success;
823
824 IF p_txn_line_detail_tbl.COUNT > 0 THEN
825 FOR l_ind IN p_txn_line_detail_tbl.FIRST .. p_txn_line_detail_tbl.LAST
826 LOOP
827 l_tmp_oa_tbl.delete;
828
829 get_org_assgn_dtls(
830 p_line_dtl_id => p_txn_line_detail_tbl(l_ind).txn_line_detail_id,
831 x_org_assgn_tbl => l_tmp_oa_tbl,
832 x_return_status => l_return_status);
833
834 IF x_return_status <> fnd_api.g_ret_sts_success THEN
835 RAISE fnd_api.g_exc_error;
836 END IF;
837
838 IF l_tmp_oa_tbl.count > 0 THEN
839 FOR l_t_ind IN l_tmp_oa_tbl.FIRST .. l_tmp_oa_tbl.LAST
840 LOOP
841 l_c_ind := l_oa_tbl.count + 1;
842 l_oa_tbl(l_c_ind) := l_tmp_oa_tbl(l_t_ind);
843 END LOOP;
844 END IF;
845
846 END LOOP;
847 END IF;
848 x_org_assgn_tbl := l_oa_tbl;
849 EXCEPTION
850 WHEN fnd_api.g_exc_error THEN
851 x_return_status := fnd_api.g_ret_sts_error;
852 END get_all_org_assgn_dtls;
853
854 END csi_t_txn_ous_pvt;