[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_DETAILS_PVT
Source
1 PACKAGE BODY CSI_T_TXN_DETAILS_PVT as
2 /* $Header: csivttxb.pls 120.12.12000000.3 2007/06/12 09:48:46 smrsharm ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_details_pvt';
5 g_file_name CONSTANT VARCHAR2(12) := 'csivttxb.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 FUNCTION check_txn_details_exist(
19 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec)
20 RETURN BOOLEAN
21 IS
22 l_debug_level number;
23 l_api_name varchar2(30) := 'check_txn_details_exist';
24 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
25 l_found BOOLEAN := FALSE;
26 l_select_stmt varchar2(4000);
27 l_lines_where_clause varchar2(2000);
28 l_cursor_id integer;
29 l_processed_rows number := 0;
30 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
31 l_lines_restrict varchar2(1) := 'N';
32 l_value number;
33
34 BEGIN
35
36 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
37
38 csi_t_gen_utility_pvt.dump_api_info(
39 p_pkg_name => g_pkg_name,
40 p_api_name => l_api_name);
41
42 IF l_debug_level > 1 THEN
43 csi_t_gen_utility_pvt.dump_txn_line_rec(
44 p_txn_line_rec => p_txn_line_rec);
45 END IF;
46
47 l_txn_line_query_rec.transaction_line_id := p_txn_line_rec.transaction_line_id;
48 l_txn_line_query_rec.source_txn_header_id := p_txn_line_rec.source_txn_header_id;
49 l_txn_line_query_rec.source_transaction_id := p_txn_line_rec.source_transaction_id;
50 l_txn_line_query_rec.source_transaction_table := p_txn_line_rec.source_transaction_table;
51 l_txn_line_query_rec.processing_status := p_txn_line_rec.processing_status;
52
53 csi_t_txn_line_dtls_pvt.build_txn_lines_select(
54 p_txn_line_query_rec => l_txn_line_query_rec,
55 x_lines_select_stmt => l_lines_where_clause,
56 x_lines_restrict => l_lines_restrict,
57 x_return_status => l_return_status);
58
59 IF l_lines_restrict = 'Y' THEN
60 l_select_stmt :=
61 'select 1 l_value from csi_t_transaction_lines where '||l_lines_where_clause;
62
63 IF l_debug_level > 10 THEN
64 csi_t_gen_utility_pvt.add(' Select Stmt:'||l_select_stmt);
65 END IF;
66
67 l_cursor_id := dbms_sql.open_cursor;
68
69 dbms_sql.parse(l_cursor_id, l_select_stmt , dbms_sql.native);
70
71 IF nvl(l_txn_line_query_rec.transaction_line_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
72 THEN
73 dbms_sql.bind_variable(l_cursor_id, 'transaction_line_id',
74 l_txn_line_query_rec.transaction_line_id);
75 END IF;
76
77 IF nvl(l_txn_line_query_rec.source_txn_header_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
78 THEN
79 dbms_sql.bind_variable(l_cursor_id, 'source_txn_header_id',
80 l_txn_line_query_rec.source_txn_header_id);
81 END IF;
82
83 IF nvl(l_txn_line_query_rec.source_transaction_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
84 THEN
85 dbms_sql.bind_variable(l_cursor_id, 'source_transaction_id',
86 l_txn_line_query_rec.source_transaction_id);
87 END IF;
88
89 IF nvl(l_txn_line_query_rec.source_transaction_table, fnd_api.g_miss_char) <> fnd_api.g_miss_char
90 THEN
91 dbms_sql.bind_variable(l_cursor_id, 'source_transaction_table',
92 l_txn_line_query_rec.source_transaction_table);
93 END IF;
94
95 IF nvl(l_txn_line_query_rec.processing_status, fnd_api.g_miss_char) <> fnd_api.g_miss_char
96 THEN
97 dbms_sql.bind_variable(l_cursor_id, 'processing_status',
98 l_txn_line_query_rec.processing_status);
99 END IF;
100
101 dbms_sql.define_column(l_cursor_id,1,l_value);
102
103 l_processed_rows := dbms_sql.execute(l_cursor_id);
104
105 LOOP
106 exit when dbms_sql.fetch_rows(l_cursor_id) = 0;
107 l_found := TRUE;
108 dbms_sql.column_value(l_cursor_id,1,l_value);
109 END LOOP;
110
111 dbms_sql.close_cursor(l_cursor_id);
112
113 END IF;
114
115 csi_t_gen_utility_pvt.set_debug_off;
116
117 IF l_found THEN
118 debug('Transaction Details found.');
119 ELSE
120 debug('Transaction Details not found.');
121 END IF;
122
123 return l_found;
124
125 EXCEPTION
126 WHEN others THEN
127
128 IF dbms_sql.is_open(l_cursor_id) THEN
129 dbms_sql.close_cursor(l_cursor_id);
130 END IF;
131 return false;
132
133 END check_txn_details_exist;
134
135 PROCEDURE create_transaction_dtls(
136 p_api_version IN NUMBER,
137 p_commit IN VARCHAR2 := fnd_api.g_false,
138 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
139 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
140 p_split_source_flag IN VARCHAR2 := fnd_api.g_false,
141 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec ,
142 px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
143 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
144 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
145 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
146 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
147 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
148 px_txn_systems_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_msg_count OUT NOCOPY NUMBER,
151 x_msg_data OUT NOCOPY VARCHAR2)
152 IS
153
154 l_api_name CONSTANT VARCHAR2(30) := 'create_transaction_dtls';
155 l_api_version CONSTANT NUMBER := 1.0;
156 l_debug_level NUMBER;
157
158 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
159 l_msg_count NUMBER;
160 l_msg_data VARCHAR2(2000);
161
162 l_td_found char;
163 l_txn_line_id NUMBER;
164 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
165 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
166 l_txn_ii_rltns_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
167
168 l_txn_system_rec csi_t_datastructures_grp.txn_system_rec;
169 l_txn_system_id NUMBER;
170
171 l_txn_source_name csi_txn_types.source_transaction_type%type;
172 l_obj_tld_id NUMBER ;
173 l_sub_instance_id NUMBER ;
174 l_sub_tld_id NUMBER ;
175 l_obj_instance_id NUMBER ;
176 l_s_index NUMBER ;
177 l_skip_tl_create varchar2(1) := 'N';
178
179 BEGIN
180
181 -- Standard Start of API savepoint
182 SAVEPOINT create_transaction_dtls;
183
184 -- Initialize message list if p_init_msg_list is set to TRUE.
185 IF FND_API.To_Boolean( p_init_msg_list ) THEN
186 FND_MSG_PUB.Initialize;
187 END IF;
188
189 -- Initialize API return status to success
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 -- Standard call to check for call compatibility.
193 IF NOT
194
195 FND_API.Compatible_API_Call (
196 p_current_version_number => l_api_version,
197 p_caller_version_number => p_api_version,
198 p_api_name => l_api_name,
199 p_pkg_name => g_pkg_name) THEN
200
201 RAISE FND_API.G_Exc_Unexpected_Error;
202
203 END IF;
204
205 -- debug messages
206 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
207
208 csi_t_gen_utility_pvt.dump_api_info(
209 p_pkg_name => g_pkg_name,
210 p_api_name => l_api_name);
211
212 csi_t_gen_utility_pvt.add(
213 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
214
215 IF l_debug_level > 1 then
216
217 csi_t_gen_utility_pvt.dump_txn_line_rec(
218 p_txn_line_rec => px_txn_line_rec);
219
220 END IF;
221
222 -- Main API code starts here
223 -- Added for CZ Integration (Begin)
224 IF px_txn_line_rec.source_transaction_type_id = 401 THEN
225 IF px_txn_line_rec.source_transaction_table <> 'WSH_DELIVERY_DETAILS' THEN --Added for bug5194812
226 SELECT csi_t_transaction_lines_s2.nextval
227 INTO px_txn_line_rec.source_transaction_id
228 FROM dual;
229 px_txn_line_rec.source_transaction_table := 'CONFIGURATOR' ; --????
230 END IF;--Added for bug5194812
231 END IF ;
232
233 -- check for the required parameters
234 csi_t_vldn_routines_pvt.check_reqd_param(
235 p_value => px_txn_line_rec.source_transaction_table,
236 p_param_name => 'px_txn_line_rec.source_transaction_table',
237 p_api_name => l_api_name);
238
239 IF px_txn_line_rec.source_transaction_type_id <> 401 THEN
240 csi_t_vldn_routines_pvt.check_reqd_param(
241 p_value => px_txn_line_rec.source_transaction_id,
242 p_param_name => 'px_txn_line_rec.source_transaction_id',
243 p_api_name => l_api_name);
244
245 END IF;
246
247 csi_t_vldn_routines_pvt.check_reqd_param(
248 p_value => px_txn_line_rec.source_transaction_type_id,
249 p_param_name => 'px_txn_line_rec.source_transaction_type_id',
250 p_api_name => l_api_name);
251
252 -- duplicate check(will have to move to the validation routines)
253 IF nvl(px_txn_line_rec.transaction_line_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
254 BEGIN
255 SELECT 'Y' INTO l_skip_tl_create
256 FROM csi_t_transaction_lines
257 WHERE transaction_line_id = px_txn_line_rec.transaction_line_id;
258 EXCEPTION
259 WHEN no_data_found THEN
260 l_skip_tl_create := 'N';
261 END;
262 END IF;
263
264 IF l_skip_tl_create <> 'Y' AND
265 px_txn_line_rec.source_transaction_type_id <> 401 THEN
266 csi_t_vldn_routines_pvt.check_duplicate(
267 p_txn_line_rec => px_txn_line_rec,
268 x_return_status => l_return_status);
269
270 IF l_return_status <> fnd_api.g_ret_sts_success THEN
271
272 FND_MESSAGE.set_name('CSI','CSI_TXN_DETAIL_DUPLICATE');
273 FND_MESSAGE.set_token('SRC_TABLE',px_txn_line_rec.source_transaction_table);
274 FND_MESSAGE.set_token('SRC_ID',px_txn_line_rec.source_transaction_id);
275 FND_MSG_PUB.add;
276
277 RAISE FND_API.g_exc_error;
278
279 END IF;
280 END IF;
281
282 -- Added for CZ Integration (Begin)
283 -- validate against CZ view
284 IF NVL(px_txn_line_rec.config_session_hdr_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
285 THEN
286 csi_t_gen_utility_pvt.add('Validating against CZ view ');
287 csi_t_vldn_routines_pvt.check_cz_session_keys(
288 p_config_session_hdr_id => px_txn_line_rec.config_session_hdr_id,
289 p_config_session_rev_num => px_txn_line_rec.config_session_rev_num,
290 p_config_session_item_id => px_txn_line_rec.config_session_item_id,
291 x_return_status => l_return_status);
292 IF l_return_status <> fnd_api.g_ret_sts_success THEN
293 RAISE FND_API.g_exc_error;
294 END IF;
295
296 csi_t_vldn_routines_pvt.check_exists_in_cz(
297 p_txn_line_dtl_tbl => px_txn_line_detail_tbl,
298 x_return_status => l_return_status);
299
300 IF l_return_status <> fnd_api.g_ret_sts_success THEN
301 RAISE FND_API.g_exc_error;
302 END IF;
303 END IF ; ---px_txn_line_rec.source_transaction_type_id=401
304 -- Added for CZ Integration (End)
305
306 -- mandatory entities check
307
308 IF p_validation_level = fnd_api.g_valid_level_full THEN
309
310 IF px_txn_line_detail_tbl.COUNT = 0 THEN
311 FND_MESSAGE.set_name('CSI','CSI_TXN_MISSING_ENTITY');
312 FND_MESSAGE.set_token('ENTITY','Line Detail Info');
313 FND_MSG_PUB.add;
314 RAISE FND_API.g_exc_error;
315
316 END IF;
317
318 END IF;
319
320 /* this condition is used only for the interface program */
321 IF p_split_source_flag = fnd_api.g_false THEN
322
323 IF p_validation_level = fnd_api.g_valid_level_full THEN
324
325 -- business object validations
326 -- Added for CZ Integration
327 -- Bypass this check for Configurator, Mass edit as it is for OM.
328 IF px_txn_line_rec.source_transaction_type_id not in (401,3)
329 THEN
330 csi_t_gen_utility_pvt.add('Validating source integrity.');
331
332 csi_t_vldn_routines_pvt.check_source_integrity(
333 p_validation_level => p_validation_level,
334 p_txn_line_rec => px_txn_line_rec,
335 p_txn_line_dtl_tbl => px_txn_line_detail_tbl,
336 x_return_status => l_return_status);
337
338 IF l_return_status <> fnd_api.g_ret_sts_success THEN
339 RAISE fnd_api.g_exc_error;
340 END IF;
341 csi_t_gen_utility_pvt.add('Validating party integrity.');
342
343 csi_t_vldn_routines_pvt.check_party_integrity(
344 p_txn_line_rec => px_txn_line_rec,
345 p_txn_line_dtl_tbl => px_txn_line_detail_tbl,
346 p_party_dtl_tbl => px_txn_party_detail_tbl,
347 x_return_status => l_return_status);
348
349 IF l_return_status <> fnd_api.g_ret_sts_success THEN
350 RAISE fnd_api.g_exc_error;
351 END IF;
352
353 -- end of business object validation
354 csi_t_gen_utility_pvt.add('End of business object validations.');
355
356 END IF ; ---<>401,3
357 END IF;
358
359 IF nvl(px_txn_line_rec.transaction_line_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
360 l_txn_line_id := px_txn_line_rec.transaction_line_id;
361 END IF;
362
363
364 IF l_skip_tl_create <> 'Y' THEN
365 -- call table handler to populate the csi_t_transaction_lines table
366
367 csi_t_gen_utility_pvt.dump_api_info(
368 p_api_name => 'insert_row',
369 p_pkg_name => 'csi_t_transaction_lines_pkg');
370
371 BEGIN
372
373 -- Added for CZ Integration (End)
374 csi_t_transaction_lines_pkg.insert_row(
375 px_transaction_line_id => l_txn_line_id,
376 p_source_transaction_type_id => px_txn_line_rec.source_transaction_type_id,
377 p_source_transaction_table => px_txn_line_rec.source_transaction_table,
378 p_source_txn_header_id => px_txn_line_rec.source_txn_header_id,
379 p_source_transaction_id => px_txn_line_rec.source_transaction_id,
380 -- Added for CZ Integration (Begin)
381 p_config_session_hdr_id => px_txn_line_rec.config_session_hdr_id ,
382 p_config_session_rev_num => px_txn_line_rec.config_session_rev_num ,
383 p_config_session_item_id => px_txn_line_rec.config_session_item_id ,
384 p_config_valid_status => px_txn_line_rec.config_valid_status ,
385 p_source_transaction_status => px_txn_line_rec.source_transaction_status ,
386 -- Added for CZ Integration (End)
387 p_error_code => px_txn_line_rec.error_code,
388 p_error_explanation => px_txn_line_rec.error_explanation,
389 p_processing_status => 'SUBMIT',
390 p_attribute1 => px_txn_line_rec.attribute1,
391 p_attribute2 => px_txn_line_rec.attribute2,
392 p_attribute3 => px_txn_line_rec.attribute3,
393 p_attribute4 => px_txn_line_rec.attribute4,
394 p_attribute5 => px_txn_line_rec.attribute5,
395 p_attribute6 => px_txn_line_rec.attribute6,
396 p_attribute7 => px_txn_line_rec.attribute7,
397 p_attribute8 => px_txn_line_rec.attribute8,
398 p_attribute9 => px_txn_line_rec.attribute9,
399 p_attribute10 => px_txn_line_rec.attribute10,
400 p_attribute11 => px_txn_line_rec.attribute11,
401 p_attribute12 => px_txn_line_rec.attribute12,
402 p_attribute13 => px_txn_line_rec.attribute13,
403 p_attribute14 => px_txn_line_rec.attribute14,
404 p_attribute15 => px_txn_line_rec.attribute15,
405 p_created_by => g_user_id,
406 p_creation_date => sysdate,
407 p_last_updated_by => g_user_id,
408 p_last_update_date => sysdate,
409 p_last_update_login => g_login_id,
410 p_object_version_number => 1.0,
411 p_context => px_txn_line_rec.context);
412
413 EXCEPTION
414 WHEN others THEN
415 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
416 fnd_message.set_token('MESSAGE','insert_row failed '||sqlerrm);
417 fnd_msg_pub.add;
418 raise fnd_api.g_exc_error;
419 END;
420
421 END IF;
422
423 px_txn_line_rec.transaction_line_id := l_txn_line_id;
424
425 IF px_txn_systems_tbl.COUNT > 0 THEN
426 FOR l_ind in px_txn_systems_tbl.FIRST..px_txn_systems_tbl.LAST
427 LOOP
428
429 l_txn_system_rec := px_txn_systems_tbl(l_ind);
430 l_txn_system_rec.transaction_line_id := l_txn_line_id;
431
432 csi_t_txn_systems_grp.create_txn_system(
433 p_api_version => p_api_version,
434 p_commit => p_commit,
435 p_init_msg_list => p_init_msg_list,
436 p_validation_level => p_validation_level,
437 p_txn_system_rec => l_txn_system_rec,
438 x_txn_system_id => l_txn_system_id,
439 x_return_status => l_return_status,
440 x_msg_count => l_msg_count,
441 x_msg_data => l_msg_data);
442
443 IF l_return_status <> fnd_api.g_ret_sts_success THEN
444
445 FND_MESSAGE.set_name('FND','FND_GENERIC_MESSAGE');
446 FND_MESSAGE.set_token('MESSAGE',
447 'csi_t_txn_systems_grp.create_txn_system API failed.');
448 FND_MSG_PUB.add;
449 RAISE fnd_api.g_exc_error;
450 END IF;
451
452 px_txn_systems_tbl(l_ind).transaction_line_id := l_txn_line_id;
453 px_txn_systems_tbl(l_ind).transaction_system_id := l_txn_system_id;
454
455 END LOOP;
456
457 END IF;
458
459 ELSE
460 l_txn_line_id := px_txn_line_rec.transaction_line_id;
461 END IF;
462
463 IF px_txn_line_detail_tbl.count > 0 THEN
464
465 -- loop through txn_line_details table
466 FOR l_index IN px_txn_line_detail_tbl.FIRST..px_txn_line_detail_tbl.LAST
467 LOOP
468
469 l_line_dtl_rec := px_txn_line_detail_tbl(l_index);
470 l_line_dtl_rec.transaction_line_id := l_txn_line_id;
471 --Following IF has been added as part of changes for bug 3600950
472 IF l_line_dtl_rec.source_transaction_flag = 'N' THEN
473 IF nvl(l_line_dtl_rec.assc_txn_line_detail_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
474 l_s_index := l_line_dtl_rec.assc_txn_line_detail_id;
475 debug('IB Transaction type id referenced, Non-Source: '|| l_line_dtl_rec.sub_type_id
476 ||' Source: '|| px_txn_line_detail_tbl(l_s_index).sub_type_id);
477 -- assigning the sub_type_id from Source to Non-source
478 l_line_dtl_rec.sub_type_id := px_txn_line_detail_tbl(l_s_index).sub_type_id;
479 IF nvl(px_txn_line_detail_tbl(l_s_index).txn_line_detail_id, fnd_api.g_miss_num)
480 <> fnd_api.g_miss_num THEN
481 l_line_dtl_rec.assc_txn_line_detail_id :=
482 px_txn_line_detail_tbl(l_s_index).txn_line_detail_id;
483 END IF;
484 END IF;
485 END IF;
486
487 IF nvl(l_line_dtl_rec.txn_systems_index, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
488
489 -- convert txn_systems_index in to txn_system_id
490 csi_t_vldn_routines_pvt.get_txn_system_id(
491 p_txn_systems_index => l_line_dtl_rec.txn_systems_index,
492 p_txn_systems_tbl => px_txn_systems_tbl,
493 x_txn_system_id => l_line_dtl_rec.transaction_system_id,
494 x_return_status => l_return_status);
495
496 IF l_return_status <> fnd_api.g_ret_sts_success THEN
497
498 FND_MESSAGE.set_name('FND','FND_GENERIC_MESSAGE');
499 FND_MESSAGE.set_token('MESSAGE','Failed to convert txn system index to ID');
500 FND_MSG_PUB.add;
501 RAISE FND_API.g_exc_error;
502
503 END IF;
504
505 END IF;
506
507 /* Adding this IF piece so that the API will default value for this based on the value for instance id column - shegde*/
508
509 IF ( l_line_dtl_rec.instance_id <> fnd_api.g_miss_num
510 AND l_line_dtl_rec.instance_id is NOT NULL ) THEN
511 l_line_dtl_rec.instance_exists_flag := 'Y';
512 ELSE
513 l_line_dtl_rec.instance_exists_flag := 'N';
514 END IF;
515
516 -- call api to create the transaction line details
517 csi_t_txn_line_dtls_pvt.create_txn_line_dtls(
518 p_api_version => p_api_version,
519 p_commit => p_commit,
520 p_init_msg_list => p_init_msg_list,
521 p_validation_level => p_validation_level,
522 p_txn_line_dtl_index => l_index,
523 p_txn_line_dtl_rec => l_line_dtl_rec,
524 px_txn_party_dtl_tbl => px_txn_party_detail_tbl,
525 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
526 px_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
527 px_txn_org_assgn_tbl => px_txn_org_assgn_tbl,
528 px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
529 x_return_status => l_return_status,
530 x_msg_count => l_msg_count,
531 x_msg_data => l_msg_data);
532
533 IF l_return_status <> fnd_api.g_ret_sts_success THEN
534 raise fnd_api.g_exc_error;
535 END IF;
536
537 px_txn_line_detail_tbl(l_index).transaction_line_id :=
538 l_line_dtl_rec.transaction_line_id;
539 px_txn_line_detail_tbl(l_index).txn_line_detail_id :=
540 l_line_dtl_rec.txn_line_detail_id;
541
542 END LOOP;
543
544 /* I have to do the relationship create after completing the creation
545 of transaction details record. This is to translate the object and
546 subject ids has to be translated to txn_line_detail_id s before
547 storing in to the database. The caller will pass the object and the
548 subject ids as the index reference of the txn line detail pl/sql
549 table (cos he does not have the txn_line_detail_id at the time of
550 calling the api). txn_line_detail_id gets generated at the time of
551 actual call to the table handler within the api
552 */
553
554 -- Added for CZ Integration (Begin)
555 IF px_txn_ii_rltns_tbl.COUNT > 0
556 THEN
557 FOR i IN px_txn_ii_rltns_tbl.FIRST .. px_txn_ii_rltns_tbl.LAST
558 LOOP
559 l_sub_instance_id := NULL ;
560 l_sub_tld_id := NULL ;
561 IF (NVL(px_txn_ii_rltns_tbl(i).sub_config_inst_hdr_id , fnd_api.g_miss_num)
562 <> fnd_api.g_miss_num
563 AND NVL(px_txn_ii_rltns_tbl(i).subject_id, fnd_api.g_miss_num)
564 = fnd_api.g_miss_num)
565 THEN
566 -- Now assuming that user is passing only config keys , get the associated instance/txn_detail_line.
567
568 csi_t_gen_utility_pvt.add('Calling get_cz_inst_or_tld_id for subject ');
569 csi_t_vldn_routines_pvt.get_cz_inst_or_tld_id (
570 p_config_inst_hdr_id => px_txn_ii_rltns_tbl(i).sub_config_inst_hdr_id,
571 p_config_inst_rev_num => px_txn_ii_rltns_tbl(i).sub_config_inst_rev_num,
572 p_config_inst_item_id => px_txn_ii_rltns_tbl(i).sub_config_inst_item_id ,
573 x_instance_id => l_sub_instance_id ,
574 x_txn_line_detail_id => l_sub_tld_id ,
575 x_return_status => x_return_status );
576
577 IF x_return_status <> fnd_api.g_ret_sts_success
578 THEN
579 RAISE fnd_api.g_exc_error ;
580 END IF ;
581
582 IF l_sub_instance_id IS NULL
583 AND l_sub_tld_id IS NULL
584 THEN
585 RAISE fnd_api.g_exc_error ;
586 END IF ;
587
588 IF l_sub_instance_id IS NOT NULL
589 THEN
590 px_txn_ii_rltns_tbl(i).subject_id := l_sub_instance_id;
591 px_txn_ii_rltns_tbl(i).subject_type := 'I' ;
592 ELSIF l_sub_tld_id IS NOT NULL
593 THEN
594 px_txn_ii_rltns_tbl(i).subject_id := l_sub_tld_id;
595 px_txn_ii_rltns_tbl(i).subject_type := 'T' ;
596 px_txn_ii_rltns_tbl(i).subject_index_flag := 'N' ;
597 END IF ;
598 csi_t_gen_utility_pvt.add('tld/instance id for subject ' || l_sub_instance_id || l_sub_tld_id);
599 END IF ; ---Subject_id is NULL
600
601 IF (NVL(px_txn_ii_rltns_tbl(i).obj_config_inst_hdr_id , fnd_api.g_miss_num)
602 <> fnd_api.g_miss_num
603 AND NVL(px_txn_ii_rltns_tbl(i).object_id, fnd_api.g_miss_num)
604 = fnd_api.g_miss_num)
605 THEN
606 csi_t_gen_utility_pvt.add('Calling get_cz_inst_or_tld_id for OBJECT');
607
608 csi_t_vldn_routines_pvt.get_cz_inst_or_tld_id (
609 p_config_inst_hdr_id => px_txn_ii_rltns_tbl(i).obj_config_inst_hdr_id,
610 p_config_inst_rev_num => px_txn_ii_rltns_tbl(i).obj_config_inst_rev_num,
611 p_config_inst_item_id => px_txn_ii_rltns_tbl(i).obj_config_inst_item_id ,
612 x_instance_id => l_obj_instance_id ,
613 x_txn_line_detail_id => l_obj_tld_id ,
614 x_return_status => x_return_status );
615
616 IF x_return_status <> fnd_api.g_ret_sts_success
617 THEN
618 RAISE fnd_api.g_exc_error ;
619 END IF ;
620
621 IF l_obj_instance_id IS NOT NULL
622 THEN
623 px_txn_ii_rltns_tbl(i).object_id := l_obj_instance_id;
624 px_txn_ii_rltns_tbl(i).object_type := 'I' ;
625 ELSIF l_obj_tld_id IS NOT NULL
626 THEN
627 px_txn_ii_rltns_tbl(i).object_id := l_obj_tld_id;
628 px_txn_ii_rltns_tbl(i).object_type := 'T' ;
629 px_txn_ii_rltns_tbl(i).object_index_flag := 'N' ;
630 END IF ;
631
632 IF l_obj_instance_id IS NULL
633 AND l_obj_tld_id IS NULL
634 THEN
635 RAISE fnd_api.g_exc_error ;
636 END IF ;
637 csi_t_gen_utility_pvt.add('tld/instance id for OBJECT' || l_obj_instance_id || l_obj_tld_id);
638 END IF ; --object is null
639 END LOOP ; --px_txn_ii_rltns_tbl loop
640 END IF ; ---px_txn_ii_rltns_tbl.count > 0
641 -- Added for CZ Integration (End)
642 IF px_txn_ii_rltns_tbl.COUNT > 0 THEN
643
644 /*
645 -- Commented the call to pvt and instead performing the check_rltns_integrity and then a call to the relationships grp since it simplifies the validations calls for M-M.
646 -- loop thru instance relationship table
647 FOR l_index IN px_txn_ii_rltns_tbl.FIRST..px_txn_ii_rltns_tbl.LAST
648 LOOP
649
650 -- initialize record type variable
651 l_txn_ii_rltns_rec := px_txn_ii_rltns_tbl(l_index);
652 l_txn_ii_rltns_rec.transaction_line_id := l_txn_line_id;
653
654 -- call api to create intance relationship records
655 csi_t_txn_rltnshps_pvt.create_txn_ii_rltns_dtls(
656 p_api_version => p_api_version,
657 p_commit => p_commit,
658 p_init_msg_list => p_init_msg_list,
659 p_validation_level => p_validation_level,
660 p_txn_ii_rltns_rec => l_txn_ii_rltns_rec,
661 x_return_status => l_return_status,
662 x_msg_count => l_msg_count,
663 x_msg_data => l_msg_data);
664
665 IF l_return_status <> fnd_api.g_ret_sts_success THEN
666 raise fnd_api.g_exc_error;
667 END IF;
668 px_txn_ii_rltns_tbl(l_index).txn_relationship_id :=
669 l_txn_ii_rltns_rec.txn_relationship_id;
670 END LOOP; -- rltns tbl loop
671
672 */
673
674 debug( 'create_txn pvt Dumping all the processing tables...');
675
676 csi_t_gen_utility_pvt.dump_txn_tables(
677 p_ids_or_index_based => 'I',
678 p_line_detail_tbl => px_txn_line_detail_tbl,
679 p_party_detail_tbl => px_txn_party_detail_tbl,
680 p_pty_acct_tbl => px_txn_pty_acct_detail_tbl,
681 p_ii_rltns_tbl => px_txn_ii_rltns_tbl,
682 p_org_assgn_tbl => px_txn_org_assgn_tbl,
683 p_ea_vals_tbl => px_txn_ext_attrib_vals_tbl);
684
685 csi_t_vldn_routines_pvt.convert_rltns_index_to_ids(
686 p_line_dtl_tbl => px_txn_line_detail_tbl,
687 px_ii_rltns_tbl => px_txn_ii_rltns_tbl,
688 x_return_status => l_return_status);
689 IF l_return_status <> fnd_api.g_ret_sts_success THEN
690 RAISE fnd_api.g_exc_error;
691 END IF;
692
693 -- Added M-M changes The check rltns integrity has now been moved here .
694 /* this routine checks the ii relationships data integrity*/
695
696 csi_t_gen_utility_pvt.add('Validating relations integrity.');
697
698 csi_t_vldn_routines_pvt.check_rltns_integrity(
699 p_txn_line_detail_tbl => px_txn_line_detail_tbl,
700 p_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
701 x_return_status => l_return_status);
702
703 IF l_return_status <> fnd_api.g_ret_sts_success THEN
704 RAISE fnd_api.g_exc_error;
705 END IF;
706
707 -- call api to create intance relationship records
708 csi_t_txn_rltnshps_grp.create_txn_ii_rltns_dtls(
709 p_api_version => p_api_version,
710 p_commit => p_commit,
711 p_init_msg_list => p_init_msg_list,
712 p_validation_level => p_validation_level,
713 px_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
714 x_return_status => l_return_status,
715 x_msg_count => l_msg_count,
716 x_msg_data => l_msg_data);
717
718 IF l_return_status <> fnd_api.g_ret_sts_success THEN
719 raise fnd_api.g_exc_error;
720 END IF;
721
722 -- Added M-M changes . End
723
724 END IF; -- if for rltns tbl count > 0
725
726 END IF; -- main if for td tbl count > 0
727
728
729 -- Standard check of p_commit.
730 IF FND_API.To_Boolean( p_commit ) THEN
731 COMMIT WORK;
732 END IF;
733
734 csi_t_gen_utility_pvt.add('transaction_line_id : '||l_txn_line_id);
735 csi_t_gen_utility_pvt.add('Transaction details created successfully.');
736 csi_t_gen_utility_pvt.set_debug_off;
737
738 -- Standard call to get message count and if count is get message info.
739 FND_MSG_PUB.Count_And_Get(
740 p_count => x_msg_count,
741 p_data => x_msg_data);
742
743 EXCEPTION
744 WHEN FND_API.G_EXC_ERROR THEN
745
746 ROLLBACK TO create_transaction_dtls;
747 x_return_status := FND_API.G_RET_STS_ERROR ;
748 FND_MSG_PUB.Count_And_Get (
749 p_count => x_msg_count,
750 p_data => x_msg_data);
751 csi_t_gen_utility_pvt.set_debug_off;
752
753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
754
755 ROLLBACK TO create_transaction_dtls;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
757
758 FND_MSG_PUB.Count_And_Get(
759 p_count => x_msg_count,
760 p_data => x_msg_data);
761 csi_t_gen_utility_pvt.set_debug_off;
762
763 WHEN OTHERS THEN
764
765 ROLLBACK TO create_transaction_dtls;
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
767
768 IF FND_MSG_PUB.Check_Msg_Level(
769 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
770
771 FND_MSG_PUB.Add_Exc_Msg(
772 p_pkg_name => G_PKG_NAME,
773 p_procedure_name => l_api_name);
774
775 END IF;
776
777 FND_MSG_PUB.Count_And_Get(
778 p_count => x_msg_count,
779 p_data => x_msg_data);
780 csi_t_gen_utility_pvt.set_debug_off;
781
782 END create_transaction_dtls;
783
784 /*
785 This API is for managing the transaction details entities incl. the line details.
786 */
787
788 PROCEDURE update_transaction_dtls (
789 p_api_version IN NUMBER
790 ,p_commit IN VARCHAR2 := fnd_api.g_false
791 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
792 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
793 ,p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec
794 ,px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
795 ,px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
796 ,px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
797 ,px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
798 ,px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
799 ,px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
800 ,x_return_status OUT NOCOPY VARCHAR2
801 ,x_msg_count OUT NOCOPY NUMBER
802 ,x_msg_data OUT NOCOPY VARCHAR2)
803 IS
804
805 l_api_name CONSTANT VARCHAR2(30) := 'update_transaction_dtls';
806 l_api_version CONSTANT NUMBER := 1.0;
807 l_debug_level NUMBER;
808
809 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
810 l_msg_count NUMBER;
811 l_msg_data VARCHAR2(2000);
812
813 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
814 l_c_tld_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
815 l_u_tld_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
816 l_d_tld_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
817 l_c_tld_ind binary_integer;
818 l_u_tld_ind binary_integer;
819 l_d_tld_ind binary_integer;
820
821 l_c_pty_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
822 l_u_pty_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
823 l_d_pty_ids_tbl csi_t_datastructures_grp.txn_party_ids_tbl;
824 l_c_pty_ind binary_integer;
825 l_u_pty_ind binary_integer;
826 l_d_pty_ind binary_integer;
827
828 l_c_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
829 l_u_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
830 l_d_pty_acct_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
831 l_c_pa_ind binary_integer;
832 l_u_pa_ind binary_integer;
833 l_d_pa_ind binary_integer;
834
835 l_c_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
836 l_u_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
837 l_c_ea_ind binary_integer;
838 l_u_ea_ind binary_integer;
839
840 l_c_oa_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
841 l_u_oa_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
842 l_c_oa_ind binary_integer;
843 l_u_oa_ind binary_integer;
844
845 l_c_ii_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
846 l_u_ii_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
847 l_c_ii_ind binary_integer;
848
849 l_indx PLS_INTEGER ;
850 i PLS_INTEGER ;
851 x_tmp_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl ;
852 x_tmp_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl ;
853 x_tmp_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl ;
854 x_tmp_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl ;
855 x_tmp_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl ;
856 x_tmp_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl ;
857 x_tmp_systems_tbl csi_t_datastructures_grp.txn_systems_tbl ;
858 x_tmp_pty_acct_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
859 l_tmp_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl ;
860 l_exists VARCHAR2(1);
861
862 BEGIN
863 -- Standard Start of API savepoint
864 SAVEPOINT update_transaction_dtls;
865
866 -- Initialize message list if p_init_msg_list is set to TRUE.
867 IF FND_API.To_Boolean( p_init_msg_list ) THEN
868 FND_MSG_PUB.Initialize;
869 END IF;
870
871 -- Initialize API return status to success
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873
874 -- Standard call to check for call compatibility.
875 IF NOT
876 FND_API.Compatible_API_Call (
877 p_current_version_number => l_api_version,
878 p_caller_version_number => p_api_version,
879 p_api_name => l_api_name,
880 p_pkg_name => g_pkg_name)
881 THEN
882 RAISE FND_API.G_Exc_Unexpected_Error;
883 END IF;
884
885 -- debug messages
886 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
887
888 csi_t_gen_utility_pvt.dump_api_info(
889 p_pkg_name => g_pkg_name,
890 p_api_name => l_api_name);
891
892 csi_t_gen_utility_pvt.add(
893 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
894
895 IF l_debug_level > 1 then
896 csi_t_gen_utility_pvt.dump_txn_line_rec(
897 p_txn_line_rec => p_txn_line_rec);
898 END IF;
899
900 -- Main API code
901 --
902 -- This procedure check if the installed base is active, If not active
903 -- populates the error message in the message queue and raises the
904 -- fnd_api.g_exc_error exception
905 --
906
907 csi_utility_grp.check_ib_active;
908
909 -- Since this API handles the Create , Update and Delete operations
910 -- first read through the pl/sql tables to identify the operation
911 -- and bundle them and re-index the passed table accordingly
912 -- Assumption is the pl/sql indexes start with 1 and are
913 -- continuous.
914 l_txn_line_rec := p_txn_line_rec;
915
916 IF px_txn_line_detail_tbl.count > 0 THEN
917
918 debug('Transaction Details count:'||px_txn_line_detail_tbl.count);
919
920 -- delete all the local pl/sql tables
921 l_c_tld_tbl.delete;
922 l_u_tld_tbl.delete;
923 l_d_tld_tbl.delete;
924 l_c_pty_tbl.delete;
925 l_u_pty_tbl.delete;
926 l_c_pty_acct_tbl.delete;
927 l_u_pty_acct_tbl.delete;
928 l_c_eav_tbl.delete;
929 l_u_eav_tbl.delete;
930 l_c_oa_tbl.delete;
931 l_u_oa_tbl.delete;
932 l_c_ii_tbl.delete;
933 l_u_ii_tbl.delete;
934 l_d_pty_ids_tbl.delete;
935 l_d_pty_acct_ids_tbl.delete;
936
937 x_tmp_line_detail_tbl.delete;
938 x_tmp_party_detail_tbl.delete;
939 x_tmp_pty_acct_detail_tbl.delete;
940 x_tmp_ii_rltns_tbl.delete;
941 x_tmp_org_assgn_tbl.delete;
942 x_tmp_ext_attrib_vals_tbl.delete;
943 x_tmp_pty_acct_ids_tbl.delete;
944
945 l_c_tld_ind := 0;
946 l_u_tld_ind := 0;
947 l_d_tld_ind := 0;
948 l_c_pty_ind := 0;
949 l_u_pty_ind := 0;
950 l_d_pty_ind := 0;
951 l_c_pa_ind := 0;
952 l_u_pa_ind := 0;
953 l_d_pa_ind := 0;
954 l_c_oa_ind := 0;
955 l_u_oa_ind := 0;
956 l_c_ea_ind := 0;
957 l_u_ea_ind := 0;
958 l_c_ii_ind := 0;
959
960 FOR i IN px_txn_line_detail_tbl.FIRST .. px_txn_line_detail_tbl.LAST
961 LOOP
962 IF nvl(px_txn_line_detail_tbl(i).txn_line_detail_id,fnd_api.g_miss_num)= fnd_api.g_miss_num THEN
963 l_c_tld_tbl(l_c_tld_ind) := px_txn_line_detail_tbl(i);
964 -- re-index the child pl/sql tables for the appropriate operation
965 -- Transaction Party details table
966 IF px_txn_party_detail_tbl.count > 0 THEN
967 FOR pc_ind IN px_txn_party_detail_tbl.FIRST .. px_txn_party_detail_tbl.LAST
968 LOOP
969 IF ((nvl(px_txn_party_detail_tbl(pc_ind).txn_line_detail_id,fnd_api.g_miss_num) = fnd_api.g_miss_num )
970 AND (px_txn_party_detail_tbl(pc_ind).txn_line_details_index = i ))
971 THEN
972 px_txn_party_detail_tbl(pc_ind).txn_line_details_index := l_c_tld_ind;
973
974 -- Transaction Party account details table
975 IF px_txn_pty_acct_detail_tbl.count > 0 THEN
976 FOR pac_ind IN px_txn_pty_acct_detail_tbl.FIRST .. px_txn_pty_acct_detail_tbl.LAST
977 LOOP
978 IF ((nvl(px_txn_pty_acct_detail_tbl(pac_ind).txn_account_detail_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
979 AND (px_txn_pty_acct_detail_tbl(pac_ind).txn_party_details_index = pc_ind)) THEN
980
981 px_txn_pty_acct_detail_tbl(pac_ind).txn_party_details_index := l_c_pty_ind;
982 l_c_pty_acct_tbl(l_c_pa_ind) := px_txn_pty_acct_detail_tbl(pac_ind);
983 l_c_pa_ind := l_c_pa_ind + 1;
984 END IF;
985 END LOOP;
986 END IF;
987
988 -- Resetting the Transaction Party Contacts table
989 l_tmp_party_detail_tbl := px_txn_party_detail_tbl;
990
991 FOR con_ind IN l_tmp_party_detail_tbl.FIRST .. l_tmp_party_detail_tbl.LAST
992 LOOP
993 IF nvl(l_tmp_party_detail_tbl(con_ind).txn_party_detail_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
994 AND nvl(l_tmp_party_detail_tbl(con_ind).contact_party_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
995 AND nvl(l_tmp_party_detail_tbl(con_ind).contact_flag, 'N') = 'Y' THEN
996 IF nvl(px_txn_party_detail_tbl(pc_ind).txn_contact_party_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num
997 AND ( nvl(px_txn_party_detail_tbl(pc_ind).contact_flag, fnd_api.g_miss_char) = fnd_api.g_miss_char
998 OR nvl(px_txn_party_detail_tbl(pc_ind).contact_flag, 'N') = 'N') THEN
999 IF l_tmp_party_detail_tbl(con_ind).contact_party_id =
1000 px_txn_party_detail_tbl(pc_ind).txn_contact_party_index THEN
1001
1002 l_tmp_party_detail_tbl(con_ind).contact_party_id := l_c_pty_ind;
1003 END IF;
1004 END IF;
1005 END IF;
1006 END LOOP;
1007 px_txn_party_detail_tbl := l_tmp_party_detail_tbl;
1008
1009 IF nvl(px_txn_party_detail_tbl(pc_ind).txn_contact_party_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1010 AND ( nvl(px_txn_party_detail_tbl(pc_ind).contact_flag, fnd_api.g_miss_char) = fnd_api.g_miss_char
1011 OR nvl(px_txn_party_detail_tbl(pc_ind).contact_flag, 'N') = 'N') THEN
1012
1013 px_txn_party_detail_tbl(pc_ind).txn_contact_party_index := l_c_pty_ind;
1014 END IF;
1015
1016 l_c_pty_tbl(l_c_pty_ind) := px_txn_party_detail_tbl(pc_ind);
1017 l_c_pty_ind := l_c_pty_ind + 1;
1018 END IF;
1019 END LOOP;
1020 END IF;
1021 -- Transaction extended atttribute values table
1022 IF px_txn_ext_attrib_vals_tbl.count > 0 THEN
1023 FOR eac_ind IN px_txn_ext_attrib_vals_tbl.FIRST .. px_txn_ext_attrib_vals_tbl.LAST
1024 LOOP
1025 IF px_txn_ext_attrib_vals_tbl(eac_ind).txn_line_details_index = i THEN
1026 px_txn_ext_attrib_vals_tbl(eac_ind).txn_line_details_index := l_c_tld_ind;
1027 l_c_eav_tbl(l_c_ea_ind) := px_txn_ext_attrib_vals_tbl(eac_ind);
1028 l_c_ea_ind := l_c_ea_ind + 1;
1029 END IF;
1030 END LOOP;
1031 END IF;
1032 -- Transaction Org assignments table
1033 IF px_txn_org_assgn_tbl.count > 0 THEN
1034 FOR oac_ind IN px_txn_org_assgn_tbl.FIRST .. px_txn_org_assgn_tbl.LAST
1035 LOOP
1036 IF px_txn_org_assgn_tbl(oac_ind).txn_line_details_index = i THEN
1037 px_txn_org_assgn_tbl(oac_ind).txn_line_details_index := l_c_tld_ind;
1038 l_c_oa_tbl(l_c_oa_ind) := px_txn_org_assgn_tbl(oac_ind);
1039 l_c_oa_ind := l_c_oa_ind + 1;
1040 END IF;
1041 END LOOP;
1042 END IF;
1043 -- Transaction details relationships table
1044 IF px_txn_ii_rltns_tbl.count > 0 THEN
1045 FOR iic_ind IN px_txn_ii_rltns_tbl.FIRST .. px_txn_ii_rltns_tbl.LAST
1046 LOOP
1047 IF px_txn_ii_rltns_tbl(iic_ind).subject_id = i
1048 AND nvl(px_txn_ii_rltns_tbl(iic_ind).subject_type, 'T') = 'T' THEN
1049 px_txn_ii_rltns_tbl(iic_ind).subject_index_flag := 'Y';
1050 px_txn_ii_rltns_tbl(iic_ind).subject_id := l_c_tld_ind;
1051 l_c_ii_tbl(l_c_ii_ind) := px_txn_ii_rltns_tbl(iic_ind);
1052 l_c_ii_ind := l_c_ii_ind + 1;
1053 ELSIF px_txn_ii_rltns_tbl(iic_ind).object_id = i
1054 AND nvl(px_txn_ii_rltns_tbl(iic_ind).object_type, 'T') = 'T' THEN
1055 px_txn_ii_rltns_tbl(iic_ind).object_index_flag := 'Y';
1056 px_txn_ii_rltns_tbl(iic_ind).object_id := l_c_tld_ind;
1057 l_c_ii_tbl(l_c_ii_ind) := px_txn_ii_rltns_tbl(iic_ind);
1058 l_c_ii_ind := l_c_ii_ind + 1;
1059 END IF;
1060 END LOOP;
1061 END IF;
1062 l_c_tld_ind := l_c_tld_ind +1;
1063 ELSE
1064 -- modified the IF below for bug 4769442
1065 IF nvl(px_txn_line_detail_tbl(i).active_end_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date
1066 AND l_txn_line_rec.source_transaction_type_id = 3
1067 THEN
1068 -- Check to see if Mass Update, delete operation and is allowed
1069 Debug('Line Detail ('||px_txn_line_detail_tbl(i).txn_line_detail_id||
1070 ') has Active End Date set..Check Instance ID');
1071 BEGIN
1072
1073 SELECT instance_id
1074 INTO px_txn_line_detail_tbl(i).instance_id
1075 FROM csi_t_txn_line_details
1076 WHERE txn_line_detail_id = px_txn_line_detail_tbl(i).txn_line_detail_id
1077 AND instance_id is NOT null;
1078
1079 Debug('Instance ID ('||px_txn_line_detail_tbl(i).instance_id||') - Source Txn Type ID ('||
1080 l_txn_line_rec.source_transaction_type_id ||') found so add this instance to be deleted');
1081 EXCEPTION
1082 when no_data_found then
1083 Debug('This instance will just be updated and not deleted ..'||
1084 ' Either the Instance ID was NULL or not a Mass Update Transaction Type');
1085 NULL;
1086 END;
1087 END IF;
1088 IF nvl(px_txn_line_detail_tbl(i).active_end_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date
1089 AND nvl(px_txn_line_detail_tbl(i).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1090 AND l_txn_line_rec.source_transaction_type_id = 3
1091 THEN
1092 -- Check to see if Mass Update, delete operation and is allowed
1093 Debug('Instance ID ('||px_txn_line_detail_tbl(i).instance_id
1094 ||') - Source Txn Type ID ('||l_txn_line_rec.source_transaction_type_id
1095 ||') found so add this instance to be deleted');
1096 l_d_tld_tbl(l_d_tld_ind) := px_txn_line_detail_tbl(i);
1097 l_d_tld_ind := l_d_tld_ind +1;
1098
1099 ELSE
1100 l_u_tld_tbl(l_u_tld_ind) := px_txn_line_detail_tbl(i);
1101
1102 csi_t_gen_utility_pvt.dump_line_detail_rec(
1103 p_line_detail_rec => l_u_tld_tbl(l_u_tld_ind));
1104
1105 -- re-index the child pl/sql tables
1106 -- Transaction Party details table
1107 IF px_txn_party_detail_tbl.count > 0 THEN
1108 FOR pu_ind IN px_txn_party_detail_tbl.FIRST .. px_txn_party_detail_tbl.LAST
1109 LOOP
1110 Debug('Upd Txn line detail, pu_ind: '|| pu_ind);
1111 csi_t_gen_utility_pvt.dump_party_detail_rec(px_txn_party_detail_tbl(pu_ind));
1112 IF nvl(px_txn_party_detail_tbl(pu_ind).txn_party_detail_id,fnd_api.g_miss_num)
1113 = fnd_api.g_miss_num -- New Party creation
1114 THEN
1115 Debug('Party ID: '|| px_txn_party_detail_tbl(pu_ind).party_source_id||'-'||pu_ind);
1116 IF px_txn_party_detail_tbl(pu_ind).txn_line_detail_id =
1117 px_txn_line_detail_tbl(i).txn_line_detail_id
1118 THEN
1119
1120 l_u_pty_tbl(l_u_pty_ind) := px_txn_party_detail_tbl(pu_ind);
1121 -- Transaction Party account details table
1122 IF px_txn_pty_acct_detail_tbl.count > 0 THEN
1123 FOR pau_ind IN px_txn_pty_acct_detail_tbl.FIRST .. px_txn_pty_acct_detail_tbl.LAST
1124 LOOP
1125 Debug('Upd Txn acct detail, pau_ind: '|| pau_ind);
1126 IF nvl(px_txn_pty_acct_detail_tbl(pau_ind).txn_account_detail_id,fnd_api.g_miss_num)
1127 = fnd_api.g_miss_num
1128 THEN -- New account creation
1129 IF px_txn_pty_acct_detail_tbl(pau_ind).txn_party_details_index = pu_ind THEN
1130
1131 px_txn_pty_acct_detail_tbl(pau_ind).txn_party_details_index := l_u_pty_ind;
1132 l_u_pty_acct_tbl(l_u_pa_ind) := px_txn_pty_acct_detail_tbl(pau_ind);
1133 l_u_pa_ind := l_u_pa_ind + 1;
1134 END IF;
1135 END IF;
1136 END LOOP;
1137 END IF;
1138
1139 -- Resetting the Transaction Party Contacts table
1140 l_tmp_party_detail_tbl := px_txn_party_detail_tbl;
1141
1142 FOR con_ind IN l_tmp_party_detail_tbl.FIRST .. l_tmp_party_detail_tbl.LAST
1143 LOOP
1144 IF nvl(l_tmp_party_detail_tbl(con_ind).txn_party_detail_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
1145 -- New Party contact creation
1146 AND nvl(l_tmp_party_detail_tbl(con_ind).contact_party_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1147 AND nvl(l_tmp_party_detail_tbl(con_ind).contact_flag, 'N') = 'Y' THEN
1148 IF nvl(px_txn_party_detail_tbl(pu_ind).txn_contact_party_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1149 AND ( nvl(px_txn_party_detail_tbl(pu_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char
1150 OR nvl(px_txn_party_detail_tbl(pu_ind).contact_flag, 'N') = 'N') THEN
1151 IF l_tmp_party_detail_tbl(con_ind).contact_party_id
1152 = px_txn_party_detail_tbl(pu_ind).txn_contact_party_index THEN
1153 l_tmp_party_detail_tbl(con_ind).contact_party_id := l_u_pty_ind;
1154 END IF;
1155 END IF;
1156 END IF;
1157 END LOOP;
1158 px_txn_party_detail_tbl := l_tmp_party_detail_tbl;
1159 debug('l_u_pty_ind: '||l_u_pty_ind);
1160
1161 IF nvl(px_txn_party_detail_tbl(pu_ind).txn_contact_party_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1162 AND ( nvl(px_txn_party_detail_tbl(pu_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char
1163 OR nvl(px_txn_party_detail_tbl(pu_ind).contact_flag, 'N') = 'N')
1164 THEN
1165 -- reset the txn_contact_party_index of the update table too
1166 px_txn_party_detail_tbl(pu_ind).txn_contact_party_index := l_u_pty_ind;
1167 END IF;
1168 l_u_pty_tbl(l_u_pty_ind) := px_txn_party_detail_tbl(pu_ind);
1169 l_u_pty_ind := l_u_pty_ind + 1;
1170 END IF;
1171 ELSE -- update to an existing party
1172 Debug('Upd Txn party detail, pu_ind: '|| pu_ind||'type id:'||l_txn_line_rec.source_transaction_type_id);
1173 l_exists := 'N';
1174 IF nvl(px_txn_party_detail_tbl(pu_ind).preserve_detail_flag,fnd_api.g_miss_char) = 'N'
1175 AND l_txn_line_rec.source_transaction_type_id = 3
1176 -- Mass Update allows usage of preserve details flag for party deletion..
1177 THEN
1178 If l_d_pty_ids_tbl.count > 0 THEN
1179 For d in l_d_pty_ids_tbl.first .. l_d_pty_ids_tbl.last Loop
1180 If l_d_pty_ids_tbl(d).txn_party_detail_id = px_txn_party_detail_tbl(pu_ind).txn_party_detail_id
1181 Then
1182 l_exists := 'Y';
1183 exit;
1184 End if;
1185 End Loop;
1186 Else
1187 l_exists := 'N';
1188 End If;
1189 If l_exists <> 'Y' THEN
1190 l_d_pty_ids_tbl(l_d_pty_ind).txn_party_detail_id := px_txn_party_detail_tbl(pu_ind).txn_party_detail_id;
1191 l_d_pty_ind := l_d_pty_ind + 1 ;
1192 l_exists := 'N';
1193 Debug('marked for deletion:'||to_char(px_txn_party_detail_tbl(pu_ind).txn_party_detail_id));
1194 End If;
1195 ELSE
1196 If l_u_pty_tbl.count > 0 THEN
1197 For u in l_u_pty_tbl.first .. l_u_pty_tbl.last Loop
1198 If l_u_pty_tbl(u).txn_party_detail_id = px_txn_party_detail_tbl(pu_ind).txn_party_detail_id
1199 Then
1200 l_exists := 'Y';
1201 exit;
1202 End if;
1203 End Loop;
1204 Else
1205 l_exists := 'N';
1206 End If;
1207 If l_exists <> 'Y' THEN
1208 l_u_pty_tbl(l_u_pty_ind) := px_txn_party_detail_tbl(pu_ind);
1209 l_u_pty_ind := l_u_pty_ind + 1;
1210 l_exists := 'N';
1211 End If;
1212 END IF;
1213
1214 IF px_txn_pty_acct_detail_tbl.count > 0 THEN
1215 FOR pau_ind IN px_txn_pty_acct_detail_tbl.FIRST .. px_txn_pty_acct_detail_tbl.LAST
1216 LOOP
1217 IF nvl(px_txn_pty_acct_detail_tbl(pau_ind).txn_account_detail_id,fnd_api.g_miss_num)
1218 = fnd_api.g_miss_num THEN -- New account creation
1219
1220 Debug('Upd Txn line detail Update Pty Crt Acct, pau_ind: '|| pau_ind);
1221 csi_t_gen_utility_pvt.dump_pty_acct_rec(px_txn_pty_acct_detail_tbl(pau_ind));
1222 IF px_txn_pty_acct_detail_tbl(pau_ind).txn_party_detail_id
1223 = px_txn_party_detail_tbl(pu_ind).txn_party_detail_id THEN
1224 IF l_u_pty_acct_tbl.count > 0 THEN
1225 l_exists := 'N';
1226 For u in l_u_pty_acct_tbl.first .. l_u_pty_acct_tbl.last Loop
1227 IF ( (l_u_pty_acct_tbl(u).account_id = px_txn_pty_acct_detail_tbl(pau_ind).account_id)
1228 AND (l_u_pty_acct_tbl(u).relationship_type_code =
1229 px_txn_pty_acct_detail_tbl(pau_ind).relationship_type_code)
1230 AND (l_u_pty_acct_tbl(u).txn_party_detail_id =
1231 px_txn_pty_acct_detail_tbl(pau_ind).txn_party_detail_id) )
1232 THEN -- not already in the pl/sql table .. append
1233
1234 l_exists := 'Y';
1235 exit;
1236 END IF;
1237 End Loop;
1238 IF l_exists <> 'Y' THEN
1239 l_u_pty_acct_tbl(l_u_pa_ind) := px_txn_pty_acct_detail_tbl(pau_ind);
1240 l_u_pa_ind := l_u_pa_ind + 1;
1241 exit;
1242 ELSE
1243 exit; -- already in the pl/sql table .. ignore and exit
1244 END IF;
1245 ELSE
1246 l_u_pty_acct_tbl(l_u_pa_ind) := px_txn_pty_acct_detail_tbl(pau_ind);
1247 l_u_pa_ind := l_u_pa_ind + 1;
1248 END IF;
1249 END IF;
1250 ELSE -- update to an existing account detail
1251 Debug('Upd line detail Update Pty Upd Acct, pau_ind: '
1252 || pau_ind||px_txn_pty_acct_detail_tbl(pau_ind).preserve_detail_flag);
1253 IF nvl(px_txn_pty_acct_detail_tbl(pau_ind).preserve_detail_flag,fnd_api.g_miss_char) = 'N'
1254 AND l_txn_line_rec.source_transaction_type_id = 3 THEN
1255 -- Mass Update allows usage of preserve details flag for account deletion..
1256
1257 Debug('Update Acct, preserve flag: N');
1258 IF l_d_pty_acct_ids_tbl.count > 0 THEN
1259 For d in l_d_pty_acct_ids_tbl.first .. l_d_pty_acct_ids_tbl.last Loop
1260 If l_d_pty_acct_ids_tbl(d).txn_account_detail_id =
1261 px_txn_pty_acct_detail_tbl(pau_ind).txn_account_detail_id
1262 Then
1263 l_exists := 'Y';
1264 exit;
1265 End if;
1266 End Loop;
1267 Else
1268 l_exists := 'N';
1269 End If;
1270 If l_exists <> 'Y' THEN
1271 Debug('account marked for deletion: '|| px_txn_pty_acct_detail_tbl(pau_ind).txn_account_detail_id);
1272 l_d_pty_acct_ids_tbl(l_d_pa_ind).txn_account_detail_id := px_txn_pty_acct_detail_tbl(pau_ind).txn_account_detail_id;
1273 l_d_pa_ind := l_d_pa_ind + 1 ;
1274 l_exists := 'N';
1275 End If;
1276 ELSE
1277 IF l_u_pty_acct_tbl.count > 0 THEN
1278 For u in l_u_pty_acct_tbl.first .. l_u_pty_acct_tbl.last Loop
1279 If l_u_pty_acct_tbl(u).txn_account_detail_id =
1280 px_txn_pty_acct_detail_tbl(pau_ind).txn_account_detail_id
1281 Then
1282 l_exists := 'Y';
1283 exit;
1284 End if;
1285 End Loop;
1286 Else
1287 l_exists := 'N';
1288 End If;
1289 If l_exists <> 'Y' THEN
1290 Debug('Upd Txn line detail Update Pty Upd acct, pau_ind: '|| pau_ind);
1291 l_u_pty_acct_tbl(l_u_pa_ind) := px_txn_pty_acct_detail_tbl(pau_ind);
1292 l_u_pa_ind := l_u_pa_ind + 1;
1293 l_exists := 'N';
1294 End If;
1295 END IF;
1296 END IF;
1297 END LOOP;
1298 END IF;
1299 END IF; -- tpd.party detail id = g_miss
1300 END LOOP;
1301 END IF;
1302 -- Transaction extended atttribute values table
1303 IF px_txn_ext_attrib_vals_tbl.count > 0 THEN
1304 l_exists := 'N';
1305 FOR eau_ind IN px_txn_ext_attrib_vals_tbl.FIRST .. px_txn_ext_attrib_vals_tbl.LAST
1306 LOOP
1307 If l_u_eav_tbl.count > 0 THEN
1308 For u in l_u_eav_tbl.first .. l_u_eav_tbl.last Loop
1309 if ((l_u_eav_tbl(u).txn_attrib_detail_id <> fnd_api.g_miss_num
1310 and px_txn_ext_attrib_vals_tbl(eau_ind).txn_attrib_detail_id <> fnd_api.g_miss_num)
1311 and ( l_u_eav_tbl(u).txn_attrib_detail_id= px_txn_ext_attrib_vals_tbl(eau_ind).txn_attrib_detail_id))
1312 Then
1313 l_exists := 'Y';
1314 exit;
1315 End if;
1316 End Loop;
1317 Else
1318 l_exists := 'N';
1319 End If;
1320 If l_exists <> 'Y' THEN
1321 l_u_eav_tbl(l_u_ea_ind) := px_txn_ext_attrib_vals_tbl(eau_ind);
1322 l_u_ea_ind := l_u_ea_ind + 1;
1323 l_exists := 'N';
1324 END IF;
1325 END LOOP;
1326 END IF;
1327 -- Transaction Org assignments table
1328 IF px_txn_org_assgn_tbl.count > 0 THEN
1329 FOR oau_ind IN px_txn_org_assgn_tbl.FIRST .. px_txn_org_assgn_tbl.LAST
1330 LOOP
1331 If l_u_oa_tbl.count > 0 THEN
1332 For u in l_u_oa_tbl.first .. l_u_oa_tbl.last Loop
1333 If l_u_oa_tbl(u).txn_operating_unit_id = px_txn_org_assgn_tbl(oau_ind).txn_operating_unit_id
1334 Then
1335 l_exists := 'Y';
1336 exit;
1337 End if;
1338 End Loop;
1339 Else
1340 l_exists := 'N';
1341 End If;
1342 If l_exists <> 'Y' THEN
1343 l_u_oa_tbl(l_u_oa_ind) := px_txn_org_assgn_tbl(oau_ind);
1344 l_u_oa_ind := l_u_oa_ind + 1;
1345 END IF;
1346 END LOOP;
1347 END IF;
1348 l_u_tld_ind := l_u_tld_ind +1;
1349 END IF;
1350 END IF;
1351 END LOOP ;
1352 END IF;
1353
1354
1355 debug('Transaction Details count By operation:'||px_txn_line_detail_tbl.count);
1356 debug(' Create:'||l_c_tld_tbl.count);
1357 debug(' Update:'||l_u_tld_tbl.count);
1358 debug(' Delete:'||l_d_tld_tbl.count);
1359
1360
1361 IF l_c_tld_tbl.count > 0 THEN
1362 -- Calling the Create Txn details for all the new Transaction details...
1363 csi_t_txn_details_pvt.create_transaction_dtls(
1364 p_api_version => p_api_version,
1365 p_commit => fnd_api.g_false,
1366 p_init_msg_list => p_init_msg_list,
1367 p_validation_level => p_validation_level,
1368 px_txn_line_rec => l_txn_line_rec,
1369 px_txn_line_detail_tbl => l_c_tld_tbl,
1370 px_txn_party_detail_tbl => l_c_pty_tbl,
1371 px_txn_pty_acct_detail_tbl => l_c_pty_acct_tbl,
1372 px_txn_ii_rltns_tbl => l_c_ii_tbl,
1373 px_txn_org_assgn_tbl => l_c_oa_tbl,
1374 px_txn_ext_attrib_vals_tbl => l_c_eav_tbl,
1375 px_txn_systems_tbl => x_tmp_systems_tbl,
1376 x_return_status => l_return_status,
1377 x_msg_count => l_msg_count,
1378 x_msg_data => l_msg_data);
1379
1380 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1381 RAISE fnd_api.g_exc_error;
1382 END IF;
1383 -- need to use tmp tables and then reassign the ID's back to sync up the same px tables so that
1384 -- they can be passed back to the callers
1385
1386 x_tmp_line_detail_tbl := l_c_tld_tbl;
1387 x_tmp_party_detail_tbl := l_c_pty_tbl;
1388 x_tmp_pty_acct_detail_tbl := l_c_pty_acct_tbl;
1389 x_tmp_ii_rltns_tbl := l_c_ii_tbl;
1390 x_tmp_org_assgn_tbl := l_c_oa_tbl;
1391 x_tmp_ext_attrib_vals_tbl := l_c_eav_tbl;
1392
1393 END IF;
1394
1395 IF l_u_tld_tbl.count > 0 THEN
1396
1397 csi_t_gen_utility_pvt.dump_txn_tables(
1398 p_ids_or_index_based => 'I',
1399 p_line_detail_tbl => l_u_tld_tbl,
1400 p_party_detail_tbl => l_u_pty_tbl,
1401 p_pty_acct_tbl => l_u_pty_acct_tbl,
1402 p_ii_rltns_tbl => x_tmp_ii_rltns_tbl,
1403 p_org_assgn_tbl => x_tmp_org_assgn_tbl,
1404 p_ea_vals_tbl => l_u_eav_tbl);
1405
1406 -- Calling the Update Txn details for the updates to Transaction details...
1407 csi_t_txn_line_dtls_pvt.update_txn_line_dtls(
1408 p_api_version => p_api_version,
1409 p_commit => fnd_api.g_false,
1410 p_init_msg_list => p_init_msg_list,
1411 p_validation_level => p_validation_level,
1412 p_txn_line_rec => l_txn_line_rec,
1413 p_txn_line_detail_tbl => l_u_tld_tbl,
1414 px_txn_ii_rltns_tbl => l_u_ii_tbl,
1415 px_txn_party_detail_tbl => l_u_pty_tbl,
1416 px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
1417 px_txn_org_assgn_tbl => l_u_oa_tbl,
1418 px_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
1419 x_return_status => l_return_status,
1420 x_msg_count => l_msg_count,
1421 x_msg_data => l_msg_data);
1422
1423 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1424 RAISE fnd_api.g_exc_error;
1425 END IF;
1426
1427 IF x_tmp_line_detail_tbl.COUNT > 0
1428 THEN
1429 l_indx := NVL(px_txn_line_detail_tbl.LAST,0)+1 ;
1430 FOR i IN x_tmp_line_detail_tbl.FIRST .. x_tmp_line_detail_tbl.LAST
1431 LOOP
1432 px_txn_line_detail_tbl(l_indx) := x_tmp_line_detail_tbl(i) ;
1433 l_indx := l_indx + 1 ;
1434 END LOOP ; ---x_tmp_line_detail_tbl
1435
1436 ELSE -- No creates performed earlier
1437 px_txn_line_detail_tbl := x_tmp_line_detail_tbl;
1438 END IF ; ---x_tmp_line_detail_tbl.COUNT > 0
1439
1440 IF x_tmp_party_detail_tbl.COUNT > 0
1441 THEN
1442 l_indx := NVL(px_txn_party_detail_tbl.LAST,0)+1 ;
1443 FOR i IN x_tmp_party_detail_tbl.FIRST .. x_tmp_party_detail_tbl.LAST
1444 LOOP
1445 px_txn_party_detail_tbl(l_indx) := x_tmp_party_detail_tbl(i) ;
1446 l_indx := l_indx + 1 ;
1447 END LOOP ; ---x_tmp_party_detail_tbl
1448
1449 ELSE -- No creates performed earlier
1450 px_txn_party_detail_tbl := x_tmp_party_detail_tbl;
1451 END IF ; ---x_tmp_party_detail_tbl.COUNT > 0
1452
1453 IF x_tmp_pty_acct_detail_tbl.COUNT > 0
1454 THEN
1455 l_indx := NVL(px_txn_pty_acct_detail_tbl.LAST,0)+1 ;
1456 FOR i IN x_tmp_pty_acct_detail_tbl.FIRST .. x_tmp_pty_acct_detail_tbl.LAST
1457 LOOP
1458 px_txn_pty_acct_detail_tbl(l_indx) := x_tmp_pty_acct_detail_tbl(i) ;
1459 l_indx := l_indx + 1 ;
1460 END LOOP ; ---x_tmp_pty_acct_detail_tbl
1461
1462 ELSE -- No creates performed earlier
1463 px_txn_pty_acct_detail_tbl := x_tmp_pty_acct_detail_tbl;
1464 END IF ; ---x_tmp_pty_acct_detail_tbl.COUNT > 0
1465
1466 IF x_tmp_ii_rltns_tbl.COUNT > 0
1467 THEN
1468 px_txn_ii_rltns_tbl := x_tmp_ii_rltns_tbl; --cause we only allow relationship creates right now
1469 END IF ; ---x_tmp_ii_rltns_tbl.COUNT > 0
1470
1471 IF x_tmp_org_assgn_tbl.COUNT > 0
1472 THEN
1473 l_indx := NVL(px_txn_org_assgn_tbl.LAST,0)+1 ;
1474 FOR i IN x_tmp_org_assgn_tbl.FIRST .. x_tmp_org_assgn_tbl.LAST
1475 LOOP
1476 px_txn_org_assgn_tbl(l_indx) := x_tmp_org_assgn_tbl(i) ;
1477 l_indx := l_indx + 1 ;
1478 END LOOP ; ---x_tmp_org_assgn_tbl
1479
1480 ELSE -- No creates performed earlier
1481 px_txn_org_assgn_tbl := x_tmp_org_assgn_tbl;
1482 END IF ; ---x_tmp_org_assgn_tbl.COUNT > 0
1483
1484 IF x_tmp_ext_attrib_vals_tbl.COUNT > 0
1485 THEN
1486 l_indx := NVL(px_txn_ext_attrib_vals_tbl.LAST,0)+1 ;
1487 FOR i IN x_tmp_ext_attrib_vals_tbl.FIRST .. x_tmp_ext_attrib_vals_tbl.LAST
1488 LOOP
1489 px_txn_ext_attrib_vals_tbl(l_indx) := x_tmp_ext_attrib_vals_tbl(i) ;
1490 l_indx := l_indx + 1 ;
1491 END LOOP ; ---x_tmp_ext_attrib_vals_tbl
1492
1493 ELSE -- No creates performed earlier
1494
1495 px_txn_ext_attrib_vals_tbl := x_tmp_ext_attrib_vals_tbl;
1496
1497 END IF ; ---x_tmp_ext_attrib_vals_tbl.COUNT > 0
1498
1499 IF l_d_pty_acct_ids_tbl.count > 0 THEN -- Mass Update; remove new accounts
1500 debug(' deleting acct :'||l_d_pty_acct_ids_tbl.count);
1501 csi_t_txn_parties_grp.delete_txn_pty_acct_dtls(
1502 p_api_version => 1.0
1503 ,p_commit => fnd_api.g_false
1504 ,p_init_msg_list => p_init_msg_list
1505 ,p_validation_level => p_validation_level
1506 ,p_txn_pty_acct_ids_tbl => l_d_pty_acct_ids_tbl
1507 ,x_return_status => l_return_status
1508 ,x_msg_count => l_msg_count
1509 ,x_msg_data => l_msg_data);
1510
1511 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1512 RAISE fnd_api.g_exc_error;
1513 END IF;
1514 END IF;
1515
1516 IF l_d_pty_ids_tbl.count > 0 THEN -- Mass Update; remove current and new associations
1517
1518 debug(' deleting Pty :'||l_d_pty_ids_tbl.count);
1519
1520 csi_t_txn_parties_grp.delete_txn_party_dtls(
1521 p_api_version => 1.0
1522 ,p_commit => fnd_api.g_false
1523 ,p_init_msg_list => p_init_msg_list
1524 ,p_validation_level => p_validation_level
1525 ,p_txn_party_ids_tbl => l_d_pty_ids_tbl
1526 ,x_txn_pty_acct_ids_tbl => x_tmp_pty_acct_ids_tbl
1527 ,x_return_status => l_return_status
1528 ,x_msg_count => l_msg_count
1529 ,x_msg_data => l_msg_data);
1530
1531 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1532 RAISE fnd_api.g_exc_error;
1533 END IF;
1534 END IF;
1535 ELSE -- No updates being procesed..
1536
1537 px_txn_line_detail_tbl := x_tmp_line_detail_tbl;
1538 px_txn_party_detail_tbl := x_tmp_party_detail_tbl;
1539 px_txn_pty_acct_detail_tbl := x_tmp_pty_acct_detail_tbl;
1540 px_txn_ii_rltns_tbl := x_tmp_ii_rltns_tbl;
1541 px_txn_org_assgn_tbl := x_tmp_org_assgn_tbl;
1542 px_txn_ext_attrib_vals_tbl := x_tmp_ext_attrib_vals_tbl;
1543
1544 END IF; -- l_u_tld_tbl.count > 0
1545
1546 IF l_d_tld_tbl.count > 0 THEN
1547 -- Calling the Delete Txn details for deletion of the Txn Line Detail and it's child entities
1548 For d in l_d_tld_tbl.FIRST .. l_d_tld_tbl.LAST Loop
1549 csi_t_txn_details_pvt.delete_transaction_dtls(
1550 p_api_version => p_api_version,
1551 p_commit => p_commit,
1552 p_init_msg_list => p_init_msg_list,
1553 p_validation_level => p_validation_level,
1554 p_transaction_line_id => l_d_tld_tbl(d).transaction_line_id,
1555 p_txn_line_detail_id => l_d_tld_tbl(d).txn_line_detail_id,
1556 x_return_status => l_return_status,
1557 x_msg_count => l_msg_count,
1558 x_msg_data => l_msg_data);
1559
1560 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1561 RAISE fnd_api.g_exc_error;
1562 END IF;
1563 END Loop;
1564
1565 IF px_txn_line_detail_tbl.COUNT > 0
1566 THEN
1567 l_indx := NVL(px_txn_line_detail_tbl.LAST,0)+1 ;
1568 FOR i IN l_d_tld_tbl.FIRST .. l_d_tld_tbl.LAST
1569 LOOP
1570 px_txn_line_detail_tbl(l_indx) := l_d_tld_tbl(i) ;
1571 l_indx := l_indx + 1 ;
1572 END LOOP ; ---l_d_tld_tbl
1573 END IF ; ---px_txn_line_detail_tbl.COUNT > 0
1574 END IF;
1575
1576 -- Standard check of p_commit.
1577 IF FND_API.To_Boolean( p_commit ) THEN
1578 COMMIT WORK;
1579 END IF;
1580
1581 csi_t_gen_utility_pvt.add('Transaction details updated successfully.');
1582 csi_t_gen_utility_pvt.set_debug_off;
1583
1584 -- Standard call to get message count and if count is get message info.
1585 FND_MSG_PUB.Count_And_Get(
1586 p_count => x_msg_count,
1587 p_data => x_msg_data);
1588
1589 EXCEPTION
1590 WHEN FND_API.G_EXC_ERROR THEN
1591
1592 ROLLBACK TO update_transaction_dtls;
1593 x_return_status := FND_API.G_RET_STS_ERROR ;
1594 FND_MSG_PUB.Count_And_Get (
1595 p_count => x_msg_count,
1596 p_data => x_msg_data);
1597 csi_t_gen_utility_pvt.set_debug_off;
1598
1599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1600
1601 ROLLBACK TO update_transaction_dtls;
1602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1603
1604 FND_MSG_PUB.Count_And_Get(
1605 p_count => x_msg_count,
1606 p_data => x_msg_data);
1607 csi_t_gen_utility_pvt.set_debug_off;
1608
1609 WHEN OTHERS THEN
1610
1611 ROLLBACK TO update_transaction_dtls;
1612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1613
1614 IF FND_MSG_PUB.Check_Msg_Level(
1615 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1616
1617 FND_MSG_PUB.Add_Exc_Msg(
1618 p_pkg_name => G_PKG_NAME,
1619 p_procedure_name => l_api_name);
1620
1621 END IF;
1622
1623 FND_MSG_PUB.Count_And_Get(
1624 p_count => x_msg_count,
1625 p_data => x_msg_data);
1626 csi_t_gen_utility_pvt.set_debug_off;
1627
1628 END update_transaction_dtls;
1629
1630 PROCEDURE delete_transaction_dtls(
1631 p_api_version IN NUMBER
1632 ,p_commit IN VARCHAR2 := fnd_api.g_false
1633 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1634 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1635 ,p_transaction_line_id IN NUMBER
1636 ,p_txn_line_detail_id IN NUMBER
1637 ,x_return_status OUT NOCOPY VARCHAR2
1638 ,x_msg_count OUT NOCOPY NUMBER
1639 ,x_msg_data OUT NOCOPY VARCHAR2)
1640 IS
1641
1642 l_api_name CONSTANT VARCHAR2(30) := 'delete_transaction_dtls';
1643 l_api_version CONSTANT NUMBER := 1.0;
1644
1645 l_return_status VARCHAR2(1);
1646 l_msg_count NUMBER;
1647 l_msg_data VARCHAR2(2000);
1648 l_transaction_line_id NUMBER;
1649 l_rltn_exists VARCHAR2(1) := 'N';
1650
1651
1652 l_td_ind BINARY_INTEGER;
1653 l_pty_ind BINARY_INTEGER;
1654 l_pty_acc_ind BINARY_INTEGER;
1655 l_ii_ind BINARY_INTEGER;
1656 l_oa_ind BINARY_INTEGER;
1657 l_ea_ind BINARY_INTEGER;
1658
1659
1660 /* table type definitions */
1661 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
1662 l_line_dtl_ids_tbl csi_t_datastructures_grp.txn_line_detail_ids_tbl;
1663 l_party_ids_tbl csi_t_datastructures_grp.txn_party_ids_tbl;
1664 l_pty_acct_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
1665 l_ii_rltns_ids_tbl csi_t_datastructures_grp.txn_ii_rltns_ids_tbl;
1666 l_org_assgn_ids_tbl csi_t_datastructures_grp.txn_org_assgn_ids_tbl;
1667 l_ext_attrib_ids_tbl csi_t_datastructures_grp.txn_ext_attrib_ids_tbl;
1668
1669 l_txn_pty_acct_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
1670
1671
1672 CURSOR td_cur IS
1673 SELECT txn_line_detail_id
1674 FROM csi_t_txn_line_details
1675 WHERE transaction_line_id = p_transaction_line_id
1676 AND decode(txn_line_detail_id, p_txn_line_detail_id,p_txn_line_detail_id,-99999)
1677 = nvl(p_txn_line_detail_id,-99999); -- Added for Mass update R12
1678
1679 CURSOR pty_cur(p_txn_line_dtl_id in number) IS
1680 SELECT txn_party_detail_id
1681 FROM csi_t_party_details
1682 WHERE txn_line_detail_id = p_txn_line_dtl_id;
1683
1684 CURSOR pty_acc_cur(p_txn_party_dtl_id in number) IS
1685 SELECT txn_account_detail_id
1686 FROM csi_t_party_accounts
1687 WHERE txn_party_detail_id = p_txn_party_dtl_id;
1688
1689 CURSOR ii_cur IS
1690 SELECT txn_relationship_id
1691 FROM csi_t_ii_relationships
1692 WHERE transaction_line_id = p_transaction_line_id;
1693 /* Added for M-M since transaction line id no longer carries importance now Start */
1694
1695 CURSOR txn_hdr_cur1 (p_txn_line_dtl_id in number) IS
1696 SELECT csit.transaction_line_id, csii.txn_relationship_id
1697 FROM csi_t_ii_relationships csii , csi_t_txn_line_details csit
1698 WHERE csit.txn_line_detail_id = csii.subject_id
1699 AND csii.subject_type = 'T'
1700 AND csii.subject_id in ( SELECT subject_id
1701 FROM csi_t_ii_relationships
1702 WHERE object_type ='T' AND object_id = p_txn_line_dtl_id)
1703 AND csii.object_id = p_txn_line_dtl_id ;
1704
1705 CURSOR txn_hdr_cur2 (p_txn_line_dtl_id in number) IS
1706 SELECT csit.transaction_line_id, csii.txn_relationship_id
1707 FROM csi_t_ii_relationships csii , csi_t_txn_line_details csit
1708 WHERE csit.txn_line_detail_id = csii.object_id
1709 AND csii.object_type = 'T'
1710 AND csii.object_id in ( SELECT object_id
1711 FROM csi_t_ii_relationships
1712 WHERE subject_type ='T' AND subject_id = p_txn_line_dtl_id)
1713 AND csii.subject_id = p_txn_line_dtl_id ;
1714
1715 /* Added for M-M since transaction line id no longer carries importance now End */
1716
1717 CURSOR oa_cur(p_txn_line_dtl_id in number) IS
1718 SELECT txn_operating_unit_id
1719 FROM csi_t_org_assignments
1720 WHERE txn_line_detail_id = p_txn_line_dtl_id;
1721
1722 CURSOR ea_cur(p_txn_line_dtl_id in number) IS
1723 SELECT txn_attrib_detail_id
1724 FROM csi_t_extend_attribs
1725 WHERE txn_line_detail_id = p_txn_line_dtl_id;
1726
1727 CURSOR sys_cur IS
1728 SELECT transaction_system_id
1729 FROM csi_t_txn_systems
1730 WHERE transaction_line_id = p_transaction_line_id;
1731
1732 BEGIN
1733
1734 -- Standard Start of API savepoint
1735 SAVEPOINT delete_transaction_dtls;
1736
1737 -- Initialize message list if p_init_msg_list is set to TRUE.
1738 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1739 FND_MSG_PUB.initialize;
1740 END IF;
1741
1742 -- Initialize API return status to success
1743 x_return_status := FND_API.G_RET_STS_SUCCESS;
1744
1745 -- Standard call to check for call compatibility.
1746 IF NOT
1747
1748 FND_API.Compatible_API_Call (
1749 p_current_version_number => l_api_version,
1750 p_caller_version_number => p_api_version,
1751 p_api_name => l_api_name,
1752 p_pkg_name => G_PKG_NAME) THEN
1753
1754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1755
1756 END IF;
1757 -- debug messages
1758
1759 csi_t_gen_utility_pvt.dump_api_info(
1760 p_pkg_name => g_pkg_name,
1761 p_api_name => l_api_name);
1762
1763 csi_t_gen_utility_pvt.add(
1764 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
1765
1766 csi_t_gen_utility_pvt.add(' p_transaction_line_id: '||
1767 to_char(p_transaction_line_id));
1768
1769 csi_t_gen_utility_pvt.add(' p_txn_line_detail_id: '||
1770 to_char(p_txn_line_detail_id));
1771
1772 -- Main API code
1773 csi_t_vldn_routines_pvt.check_reqd_param(
1774 p_value => p_transaction_line_id,
1775 p_param_name => 'p_transaction_line_id',
1776 p_api_name => l_api_name);
1777
1778 -- validate transaction line id
1779 csi_t_vldn_routines_pvt.validate_transaction_line_id(
1780 p_transaction_line_id => p_transaction_line_id,
1781 x_transaction_line_rec => l_txn_line_rec,-- Added for Mass update R12
1782 x_return_status => l_return_status);
1783
1784 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1785
1786 fnd_message.set_name('CSI','CSI_TXN_LINE_ID_INVALID');
1787 fnd_message.set_token('TXN_LINE_ID', p_transaction_line_id);
1788 fnd_msg_pub.add;
1789 RAISE fnd_api.g_exc_error;
1790
1791 END IF;
1792
1793 -- put the business logics here
1794
1795 -- cannot delete the converted transaction details.
1796 -- (Ones that are converted in to item instances) These records are
1797 -- identified by value in the csi_transaction_id.
1798 -- If the CSI_TRANSACTION_ID is not null or an appropriate value in the
1799 -- processing_status flag
1800
1801 -- Added check for validation for Purge
1802 IF p_validation_level <> 999 THEN
1803 IF l_txn_line_rec.source_transaction_type_id <> 3 THEN -- Added for Mass update R12
1804 -- excluding the Mass Update Transactions
1805
1806 csi_t_vldn_routines_pvt.check_ib_creation(
1807 p_transaction_line_id => p_transaction_line_id,
1808 x_return_status => l_return_status);
1809
1810 IF l_return_status = fnd_api.g_true THEN
1811
1812 fnd_message.set_name('CSI','CSI_TXN_DELETION_NOT_ALLOWED');
1813 fnd_message.set_token('TXN_LINE_ID', p_transaction_line_id);
1814 fnd_msg_pub.add;
1815 RAISE fnd_api.g_exc_error;
1816
1817 END IF;
1818 END IF;
1819 END IF;
1820
1821 l_pty_ind := 0;
1822 l_pty_acc_ind := 0;
1823 l_oa_ind := 0;
1824 l_ea_ind := 0;
1825 l_ii_ind := 0;
1826
1827 FOR td_rec in td_cur
1828 LOOP
1829
1830 l_td_ind := td_cur%rowcount;
1831 debug ('deleting line detail ID:'||td_rec.txn_line_detail_id);
1832
1833 -- populate txn_line_detail table
1834
1835 l_line_dtl_ids_tbl(l_td_ind).
1836 transaction_line_id := p_transaction_line_id;
1837 l_line_dtl_ids_tbl(l_td_ind).
1838 txn_line_detail_id := td_rec.txn_line_detail_id;
1839
1840 FOR pty_rec IN pty_cur(td_rec.txn_line_detail_id)
1841 LOOP
1842
1843 l_pty_ind := l_pty_ind + 1;
1844
1845 -- populate txn_party_detail_ids table
1846
1847 l_party_ids_tbl(l_pty_ind).
1848 txn_line_detail_id := td_rec.txn_line_detail_id;
1849 l_party_ids_tbl(l_pty_ind).
1850 txn_party_detail_id := pty_rec.txn_party_detail_id;
1851
1852 FOR pty_acc_rec IN pty_acc_cur(pty_rec.txn_party_detail_id)
1853 LOOP
1854
1855 l_pty_acc_ind := l_pty_acc_ind + 1;
1856
1857 -- populate txn_pty_account_ids table
1858
1859 l_pty_acct_ids_tbl(l_pty_acc_ind).
1860 txn_party_detail_id := pty_rec.txn_party_detail_id;
1861 l_pty_acct_ids_tbl(l_pty_acc_ind).
1862 txn_account_detail_id := pty_acc_rec.txn_account_detail_id;
1863
1864 END LOOP;
1865
1866 END LOOP;
1867
1868 FOR oa_rec IN oa_cur(td_rec.txn_line_detail_id)
1869 LOOP
1870
1871 l_oa_ind := l_oa_ind + 1;
1872
1873 l_org_assgn_ids_tbl(l_oa_ind).
1874 txn_line_detail_id := td_rec.txn_line_detail_id;
1875 l_org_assgn_ids_tbl(l_oa_ind).
1876 txn_operating_unit_id := oa_rec.txn_operating_unit_id;
1877
1878 END LOOP;
1879
1880 FOR ea_rec IN ea_cur(td_rec.txn_line_detail_id)
1881 LOOP
1882
1883 l_ea_ind := l_ea_ind + 1;
1884
1885 l_ext_attrib_ids_tbl(l_ea_ind).
1886 txn_line_detail_id := td_rec.txn_line_detail_id;
1887 l_ext_attrib_ids_tbl(l_ea_ind).
1888 txn_attrib_detail_id := ea_rec.txn_attrib_detail_id;
1889
1890 END LOOP;
1891
1892 /* Added for M-M since transaction line id no longer carries importance now Start */
1893
1894 FOR txn_hdr_rec IN txn_hdr_cur1(td_rec.txn_line_detail_id)
1895 LOOP
1896 l_transaction_line_id := txn_hdr_rec.transaction_line_id;
1897
1898 IF l_transaction_line_id <> p_transaction_line_id THEN
1899
1900 csi_t_vldn_routines_pvt.check_ib_creation(
1901 p_transaction_line_id => l_transaction_line_id,
1902 x_return_status => l_return_status);
1903
1904 IF l_return_status = fnd_api.g_true THEN
1905 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_ID_PROCESSED');
1906 FND_MESSAGE.set_token('TXN_LINE_ID',l_transaction_line_id);
1907 FND_MSG_PUB.add;
1908 exit; -- if a relationship is found and that has already been processed to IB then skip it else mark it for deletion
1909 END IF;
1910
1911 l_ii_ind := l_ii_ind + 1;
1912
1913 l_ii_rltns_ids_tbl(l_ii_ind).transaction_line_id := txn_hdr_rec.transaction_line_id;
1914 l_ii_rltns_ids_tbl(l_ii_ind).txn_relationship_id := txn_hdr_rec.txn_relationship_id;
1915
1916 END IF;
1917 END LOOP;
1918
1919 FOR txn_hdr_rec IN txn_hdr_cur2(td_rec.txn_line_detail_id)
1920 LOOP
1921 l_transaction_line_id := txn_hdr_rec.transaction_line_id;
1922
1923
1924 IF l_transaction_line_id <> p_transaction_line_id THEN
1925
1926 csi_t_vldn_routines_pvt.check_ib_creation(
1927 p_transaction_line_id => l_transaction_line_id,
1928 x_return_status => l_return_status);
1929
1930 IF l_return_status = fnd_api.g_true THEN
1931 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_ID_PROCESSED');
1932 FND_MESSAGE.set_token('TXN_LINE_ID',l_transaction_line_id);
1933 FND_MSG_PUB.add;
1934 exit; -- if a relationship is found and that has already been processed to IB then skip it else mark it for deletion
1935 END IF;
1936 l_ii_ind := l_ii_ind + 1;
1937
1938 l_ii_rltns_ids_tbl(l_ii_ind).transaction_line_id := txn_hdr_rec.transaction_line_id;
1939 l_ii_rltns_ids_tbl(l_ii_ind).txn_relationship_id := txn_hdr_rec.txn_relationship_id;
1940
1941 END IF;
1942 END LOOP;
1943
1944 /* Added for M-M since transaction line id no longer carries importance now End */
1945
1946 END LOOP;
1947
1948 FOR ii_rec IN ii_cur
1949 LOOP
1950 IF l_ii_rltns_ids_tbl.count > 0 THEN
1951 For i in l_ii_rltns_ids_tbl.FIRST .. l_ii_rltns_ids_tbl.LAST
1952 LOOP
1953 IF l_ii_rltns_ids_tbl(i).txn_relationship_id = ii_rec.txn_relationship_id THEN
1954 l_rltn_exists := 'Y';
1955 END IF;
1956 END LOOP;
1957 END IF;
1958 IF l_rltn_exists = 'N' THEN -- this pulls up all the relationships that are within the same txn line whereas the existing l_ii_rltns_ids_tbl are for across the txn line rltns
1959 l_ii_ind := l_ii_ind + 1;
1960
1961 l_ii_rltns_ids_tbl(l_ii_ind).transaction_line_id := p_transaction_line_id;
1962 l_ii_rltns_ids_tbl(l_ii_ind).txn_relationship_id := ii_rec.txn_relationship_id;
1963 END IF;
1964
1965 END LOOP;
1966
1967
1968 FOR sys_rec in sys_cur
1969 LOOP
1970
1971 csi_t_txn_systems_grp.delete_txn_system(
1972 p_api_version => 1.0,
1973 p_commit => fnd_api.g_false,
1974 p_init_msg_list => fnd_api.g_false,
1975 p_validation_level => fnd_api.g_valid_level_full,
1976 p_txn_system_id => sys_rec.transaction_system_id,
1977 x_return_status => l_return_status,
1978 x_msg_count => l_msg_count,
1979 x_msg_data => l_msg_data);
1980
1981 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1982 RAISE fnd_api.g_exc_error;
1983 END IF;
1984
1985 END LOOP;
1986
1987 csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
1988 p_api_version => p_api_version,
1989 p_commit => p_commit,
1990 p_init_msg_list => p_init_msg_list,
1991 p_validation_level => p_validation_level,
1992 p_txn_pty_acct_ids_tbl => l_pty_acct_ids_tbl,
1993 x_return_status => l_return_status,
1994 x_msg_count => l_msg_count,
1995 x_msg_data => l_msg_data);
1996
1997 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1998 RAISE fnd_api.g_exc_error;
1999 END IF;
2000
2001 csi_t_txn_parties_pvt.delete_txn_party_dtls(
2002 p_api_version => p_api_version,
2003 p_commit => p_commit,
2004 p_init_msg_list => p_init_msg_list,
2005 p_validation_level => p_validation_level,
2006 p_txn_party_ids_tbl => l_party_ids_tbl,
2007 x_txn_pty_acct_ids_tbl => l_txn_pty_acct_ids_tbl,
2008 x_return_status => l_return_status,
2009 x_msg_count => l_msg_count,
2010 x_msg_data => l_msg_data);
2011
2012 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2013 RAISE fnd_api.g_exc_error;
2014 END IF;
2015
2016 csi_t_txn_rltnshps_pvt.delete_txn_ii_rltns_dtls(
2017 p_api_version => p_api_version,
2018 p_commit => p_commit,
2019 p_init_msg_list => p_init_msg_list,
2020 p_validation_level => p_validation_level,
2021 p_txn_ii_rltns_ids_tbl => l_ii_rltns_ids_tbl,
2022 x_return_status => l_return_status,
2023 x_msg_count => l_msg_count,
2024 x_msg_data => l_msg_data);
2025
2026 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2027 RAISE fnd_api.g_exc_error;
2028 END IF;
2029
2030 csi_t_txn_ous_pvt.delete_txn_org_assgn_dtls(
2031 p_api_version => p_api_version,
2032 p_commit => p_commit,
2033 p_init_msg_list => p_init_msg_list,
2034 p_validation_level => p_validation_level,
2035 p_txn_org_assgn_ids_tbl => l_org_assgn_ids_tbl,
2036 x_return_status => l_return_status,
2037 x_msg_count => l_msg_count,
2038 x_msg_data => l_msg_data);
2039
2040 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2041 RAISE fnd_api.g_exc_error;
2042 END IF;
2043
2044 csi_t_txn_attribs_pvt.delete_txn_ext_attrib_dtls(
2045 p_api_version => p_api_version,
2046 p_commit => p_commit,
2047 p_init_msg_list => p_init_msg_list,
2048 p_validation_level => p_validation_level,
2049 p_txn_ext_attrib_ids_tbl => l_ext_attrib_ids_tbl,
2050 x_return_status => l_return_status,
2051 x_msg_count => l_msg_count,
2052 x_msg_data => l_msg_data);
2053
2054 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2055 RAISE fnd_api.g_exc_error;
2056 END IF;
2057
2058 csi_t_txn_line_dtls_pvt.delete_txn_line_dtls(
2059 p_api_version => p_api_version,
2060 p_commit => p_commit,
2061 p_init_msg_list => p_init_msg_list,
2062 p_validation_level => p_validation_level,
2063 p_txn_line_detail_ids_tbl => l_line_dtl_ids_tbl,
2064 x_return_status => l_return_status,
2065 x_msg_count => l_msg_count,
2066 x_msg_data => l_msg_data);
2067
2068 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2069 RAISE fnd_api.g_exc_error;
2070 END IF;
2071
2072 -- call table handler to delete transaction lines
2073 IF nvl(p_txn_line_detail_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2074 csi_t_transaction_lines_pkg.delete_row(
2075 p_transaction_line_id => p_transaction_line_id);
2076 END IF;
2077
2078 -- Standard check of p_commit.
2079 IF FND_API.To_Boolean( p_commit ) THEN
2080 COMMIT WORK;
2081 END IF;
2082
2083 csi_t_gen_utility_pvt.add('Transaction Details Deleted Successfully
2084 for Transaction Line ID:'||to_char(p_transaction_line_id)||
2085 ' Txn line detail ID:'||to_char(p_txn_line_detail_id));
2086
2087 -- Standard call to get message count and if count is get message info.
2088 FND_MSG_PUB.Count_And_Get(
2089 p_count => x_msg_count,
2090 p_data => x_msg_data);
2091
2092 EXCEPTION
2093 WHEN FND_API.G_EXC_ERROR THEN
2094
2095 ROLLBACK TO delete_transaction_dtls;
2096 x_return_status := FND_API.G_RET_STS_ERROR ;
2097 FND_MSG_PUB.Count_And_Get (
2098 p_count => x_msg_count,
2099 p_data => x_msg_data);
2100
2101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2102
2103 ROLLBACK TO delete_transaction_dtls;
2104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2105
2106 FND_MSG_PUB.Count_And_Get(
2107 p_count => x_msg_count,
2108 p_data => x_msg_data);
2109
2110 WHEN OTHERS THEN
2111
2112 ROLLBACK TO delete_transaction_dtls;
2113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2114
2115 IF FND_MSG_PUB.Check_Msg_Level(
2116 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2117
2118 FND_MSG_PUB.Add_Exc_Msg(
2119 p_pkg_name => G_PKG_NAME,
2120 p_procedure_name => l_api_name);
2121
2122 END IF;
2123
2124 FND_MSG_PUB.Count_And_Get(
2125 p_count => x_msg_count,
2126 p_data => x_msg_data);
2127
2128 END delete_transaction_dtls;
2129
2130 /* Overloaded routine added and used : Bug 2543266 */
2131
2132 PROCEDURE get_txn_systems(
2133 p_txn_line_dtl_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
2134 x_txn_systems_tbl OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
2135 x_return_status OUT NOCOPY varchar2)
2136 IS
2137
2138 l_txn_system_id number ;
2139 l_sys_not_found varchar2(1) := 'Y';
2140 l_ind binary_integer := 0;
2141 s_ind binary_integer;
2142
2143 Cursor txn_sys(p_txn_system_id IN Number) is
2144 Select transaction_system_id, system_name , description,
2145 system_type_code, system_number, bill_to_contact_id,
2146 ship_to_contact_id, technical_contact_id, config_system_type,
2147 service_admin_contact_id, ship_to_site_use_id,
2148 bill_to_site_use_id, coterminate_day_month, customer_id,
2149 install_site_use_id, transaction_line_id, start_date_active,
2150 end_date_active,context, attribute1, attribute2, attribute3,
2151 attribute4, attribute5, attribute6, attribute7, attribute8,
2152 attribute9, attribute10, attribute11, attribute12,
2153 attribute13, attribute14, attribute15, object_version_number
2154 From csi_t_txn_systems
2155 Where transaction_system_id = p_txn_system_id;
2156
2157 BEGIN
2158 IF p_txn_line_dtl_tbl.count > 0 THEN
2159 For i in p_txn_line_dtl_tbl.FIRST .. p_txn_line_dtl_tbl.LAST
2160 Loop
2161 l_txn_system_id := p_txn_line_dtl_tbl(i).transaction_system_id;
2162
2163 IF x_txn_systems_tbl.count > 0 THEN
2164 For j in x_txn_systems_tbl.FIRST ..x_txn_systems_tbl.LAST
2165 Loop
2166 IF l_txn_system_id = nvl(x_txn_systems_tbl(j).transaction_system_id, -999)
2167 THEN
2168 l_sys_not_found := 'N';
2169 END IF;
2170
2171 End Loop;
2172 END IF;
2173
2174 IF l_sys_not_found = 'Y' THEN
2175 For sys_cur in txn_sys(l_txn_system_id)
2176 Loop
2177
2178 l_ind := l_ind + 1;
2179
2180 x_txn_systems_tbl(l_ind).transaction_system_id := sys_cur.transaction_system_id;
2181 x_txn_systems_tbl(l_ind).transaction_line_id := sys_cur.transaction_line_id;
2182 x_txn_systems_tbl(l_ind).system_name := sys_cur.system_name;
2183 x_txn_systems_tbl(l_ind).description := sys_cur.description;
2184 x_txn_systems_tbl(l_ind).system_type_code := sys_cur.system_type_code;
2185 x_txn_systems_tbl(l_ind).system_number := sys_cur.system_number;
2186 x_txn_systems_tbl(l_ind).customer_id := sys_cur.customer_id;
2187 x_txn_systems_tbl(l_ind).bill_to_contact_id := sys_cur.bill_to_contact_id;
2188 x_txn_systems_tbl(l_ind).ship_to_contact_id := sys_cur.ship_to_contact_id;
2189 x_txn_systems_tbl(l_ind).technical_contact_id := sys_cur.technical_contact_id;
2190 x_txn_systems_tbl(l_ind).ship_to_site_use_id := sys_cur.ship_to_site_use_id;
2191 x_txn_systems_tbl(l_ind).bill_to_site_use_id := sys_cur.bill_to_site_use_id;
2192 x_txn_systems_tbl(l_ind).install_site_use_id := sys_cur.install_site_use_id;
2193 x_txn_systems_tbl(l_ind).coterminate_day_month := sys_cur.coterminate_day_month;
2194 x_txn_systems_tbl(l_ind).config_system_type := sys_cur.config_system_type;
2195 x_txn_systems_tbl(l_ind).object_version_number := sys_cur.object_version_number;
2196 x_txn_systems_tbl(l_ind).service_admin_contact_id := sys_cur.service_admin_contact_id;
2197 x_txn_systems_tbl(l_ind).context := sys_cur.context;
2198 x_txn_systems_tbl(l_ind).attribute1 := sys_cur.attribute1;
2199 x_txn_systems_tbl(l_ind).attribute2 := sys_cur.attribute2;
2200 x_txn_systems_tbl(l_ind).attribute3 := sys_cur.attribute3;
2201 x_txn_systems_tbl(l_ind).attribute4 := sys_cur.attribute4;
2202 x_txn_systems_tbl(l_ind).attribute5 := sys_cur.attribute5;
2203 x_txn_systems_tbl(l_ind).attribute6 := sys_cur.attribute6;
2204 x_txn_systems_tbl(l_ind).attribute7 := sys_cur.attribute7;
2205 x_txn_systems_tbl(l_ind).attribute8 := sys_cur.attribute8;
2206 x_txn_systems_tbl(l_ind).attribute9 := sys_cur.attribute9;
2207 x_txn_systems_tbl(l_ind).attribute10 := sys_cur.attribute10;
2208 x_txn_systems_tbl(l_ind).attribute11 := sys_cur.attribute11;
2209 x_txn_systems_tbl(l_ind).attribute12 := sys_cur.attribute12;
2210 x_txn_systems_tbl(l_ind).attribute13 := sys_cur.attribute13;
2211 x_txn_systems_tbl(l_ind).attribute14 := sys_cur.attribute14;
2212 x_txn_systems_tbl(l_ind).attribute15 := sys_cur.attribute15;
2213
2214 End Loop;
2215 END IF;
2216 End Loop;
2217 END IF;
2218
2219 --END;
2220
2221 EXCEPTION
2222 WHEN others THEN
2223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2224
2225 End get_txn_systems;
2226
2227 /* This routine builds the txn systems query and makes a dynamic sql query
2228 to fetch the txn systems. ROutine rerurns a table of txn systems
2229 */
2230 /*
2231 Commented for Bug 2543266 . Using overloaded routine above instead.
2232
2233 PROCEDURE get_txn_systems(
2234 p_txn_system_id_list in varchar2,
2235 x_txn_systems_tbl OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
2236 x_return_status OUT NOCOPY varchar2)
2237 IS
2238
2239 l_select_stmt varchar2(2000);
2240 l_sys_cur_id integer;
2241 l_sys_rec csi_t_datastructures_grp.txn_system_rec;
2242 l_processed_rows number := 0;
2243 l_ind binary_integer;
2244
2245 BEGIN
2246
2247 l_select_stmt :=
2248 'select transaction_system_id, transaction_line_id, system_name,'||
2249 ' description, system_type_code, system_number, customer_id,'||
2250 ' bill_to_contact_id, ship_to_contact_id, technical_contact_id,'||
2251 ' service_admin_contact_id, ship_to_site_use_id, bill_to_site_use_id,'||
2252 ' install_site_use_id, coterminate_day_month, config_system_type,'||
2253 ' context, attribute1, attribute2, attribute3, attribute4, attribute5,'||
2254 ' attribute6, attribute7, attribute8, attribute9, attribute10,'||
2255 ' attribute11, attribute12, attribute13, attribute14, attribute15,'||
2256 ' object_version_number '||
2257 'from csi_t_txn_systems '||
2258 'where transaction_system_id in '||p_txn_system_id_list;
2259
2260 --csi_t_gen_utility_pvt.add(l_select_stmt);
2261
2262 l_sys_cur_id := dbms_sql.open_cursor;
2263
2264 dbms_sql.parse(l_sys_cur_id, l_select_stmt , dbms_sql.native);
2265
2266 dbms_sql.define_column(l_sys_cur_id,1,l_sys_rec.transaction_system_id);
2267 dbms_sql.define_column(l_sys_cur_id,2,l_sys_rec.transaction_line_id);
2268 dbms_sql.define_column(l_sys_cur_id,3,l_sys_rec.system_name,50);
2269 dbms_sql.define_column(l_sys_cur_id,4,l_sys_rec.description,240);
2270 dbms_sql.define_column(l_sys_cur_id,5,l_sys_rec.system_type_code,30);
2271 dbms_sql.define_column(l_sys_cur_id,6,l_sys_rec.system_number,30);
2272 dbms_sql.define_column(l_sys_cur_id,7,l_sys_rec.customer_id);
2273 dbms_sql.define_column(l_sys_cur_id,8,l_sys_rec.bill_to_contact_id);
2274 dbms_sql.define_column(l_sys_cur_id,9,l_sys_rec.ship_to_contact_id);
2275 dbms_sql.define_column(l_sys_cur_id,10,l_sys_rec.technical_contact_id);
2276 dbms_sql.define_column(l_sys_cur_id,11,l_sys_rec.service_admin_contact_id);
2277 dbms_sql.define_column(l_sys_cur_id,12,l_sys_rec.ship_to_site_use_id);
2278 dbms_sql.define_column(l_sys_cur_id,13,l_sys_rec.bill_to_site_use_id);
2279 dbms_sql.define_column(l_sys_cur_id,14,l_sys_rec.install_site_use_id);
2280 dbms_sql.define_column(l_sys_cur_id,15,l_sys_rec.coterminate_day_month,6);
2281 dbms_sql.define_column(l_sys_cur_id,16,l_sys_rec.config_system_type,30);
2282 dbms_sql.define_column(l_sys_cur_id,17,l_sys_rec.context,30);
2283 dbms_sql.define_column(l_sys_cur_id,18,l_sys_rec.attribute1,150);
2284 dbms_sql.define_column(l_sys_cur_id,19,l_sys_rec.attribute2,150);
2285 dbms_sql.define_column(l_sys_cur_id,20,l_sys_rec.attribute3,150);
2286 dbms_sql.define_column(l_sys_cur_id,21,l_sys_rec.attribute4,150);
2287 dbms_sql.define_column(l_sys_cur_id,22,l_sys_rec.attribute5,150);
2288 dbms_sql.define_column(l_sys_cur_id,23,l_sys_rec.attribute6,150);
2289 dbms_sql.define_column(l_sys_cur_id,24,l_sys_rec.attribute7,150);
2290 dbms_sql.define_column(l_sys_cur_id,25,l_sys_rec.attribute8,150);
2291 dbms_sql.define_column(l_sys_cur_id,26,l_sys_rec.attribute9,150);
2292 dbms_sql.define_column(l_sys_cur_id,27,l_sys_rec.attribute10,150);
2293 dbms_sql.define_column(l_sys_cur_id,28,l_sys_rec.attribute11,150);
2294 dbms_sql.define_column(l_sys_cur_id,29,l_sys_rec.attribute12,150);
2295 dbms_sql.define_column(l_sys_cur_id,30,l_sys_rec.attribute13,150);
2296 dbms_sql.define_column(l_sys_cur_id,31,l_sys_rec.attribute14,150);
2297 dbms_sql.define_column(l_sys_cur_id,32,l_sys_rec.attribute15,150);
2298 dbms_sql.define_column(l_sys_cur_id,33,l_sys_rec.object_version_number);
2299
2300 l_ind := 0;
2301
2302 l_processed_rows := dbms_sql.execute(l_sys_cur_id);
2303 LOOP
2304 exit when dbms_sql.fetch_rows(l_sys_cur_id) = 0;
2305
2306 l_ind := l_ind + 1;
2307
2308 dbms_sql.column_value(l_sys_cur_id,1, x_txn_systems_tbl(l_ind).transaction_system_id);
2309 dbms_sql.column_value(l_sys_cur_id,2, x_txn_systems_tbl(l_ind).transaction_line_id);
2310 dbms_sql.column_value(l_sys_cur_id,3, x_txn_systems_tbl(l_ind).system_name);
2311 dbms_sql.column_value(l_sys_cur_id,4, x_txn_systems_tbl(l_ind).description);
2312 dbms_sql.column_value(l_sys_cur_id,5, x_txn_systems_tbl(l_ind).system_type_code);
2313 dbms_sql.column_value(l_sys_cur_id,6, x_txn_systems_tbl(l_ind).system_number);
2314 dbms_sql.column_value(l_sys_cur_id,7, x_txn_systems_tbl(l_ind).customer_id);
2315 dbms_sql.column_value(l_sys_cur_id,8, x_txn_systems_tbl(l_ind).bill_to_contact_id);
2316 dbms_sql.column_value(l_sys_cur_id,9, x_txn_systems_tbl(l_ind).ship_to_contact_id);
2317 dbms_sql.column_value(l_sys_cur_id,10, x_txn_systems_tbl(l_ind).technical_contact_id);
2318 dbms_sql.column_value(l_sys_cur_id,11, x_txn_systems_tbl(l_ind).service_admin_contact_id);
2319 dbms_sql.column_value(l_sys_cur_id,12, x_txn_systems_tbl(l_ind).ship_to_site_use_id);
2320 dbms_sql.column_value(l_sys_cur_id,13, x_txn_systems_tbl(l_ind).bill_to_site_use_id);
2321 dbms_sql.column_value(l_sys_cur_id,14, x_txn_systems_tbl(l_ind).install_site_use_id);
2322 dbms_sql.column_value(l_sys_cur_id,15, x_txn_systems_tbl(l_ind).coterminate_day_month);
2323 dbms_sql.column_value(l_sys_cur_id,16, x_txn_systems_tbl(l_ind).config_system_type);
2324 dbms_sql.column_value(l_sys_cur_id,17, x_txn_systems_tbl(l_ind).context);
2325 dbms_sql.column_value(l_sys_cur_id,18, x_txn_systems_tbl(l_ind).attribute1);
2326 dbms_sql.column_value(l_sys_cur_id,19, x_txn_systems_tbl(l_ind).attribute2);
2327 dbms_sql.column_value(l_sys_cur_id,20, x_txn_systems_tbl(l_ind).attribute3);
2328 dbms_sql.column_value(l_sys_cur_id,21, x_txn_systems_tbl(l_ind).attribute4);
2329 dbms_sql.column_value(l_sys_cur_id,22, x_txn_systems_tbl(l_ind).attribute5);
2330 dbms_sql.column_value(l_sys_cur_id,23, x_txn_systems_tbl(l_ind).attribute6);
2331 dbms_sql.column_value(l_sys_cur_id,24, x_txn_systems_tbl(l_ind).attribute7);
2332 dbms_sql.column_value(l_sys_cur_id,25, x_txn_systems_tbl(l_ind).attribute8);
2333 dbms_sql.column_value(l_sys_cur_id,26, x_txn_systems_tbl(l_ind).attribute9);
2334 dbms_sql.column_value(l_sys_cur_id,27, x_txn_systems_tbl(l_ind).attribute10);
2335 dbms_sql.column_value(l_sys_cur_id,28, x_txn_systems_tbl(l_ind).attribute11);
2336 dbms_sql.column_value(l_sys_cur_id,29, x_txn_systems_tbl(l_ind).attribute12);
2337 dbms_sql.column_value(l_sys_cur_id,30, x_txn_systems_tbl(l_ind).attribute13);
2338 dbms_sql.column_value(l_sys_cur_id,31, x_txn_systems_tbl(l_ind).attribute14);
2339 dbms_sql.column_value(l_sys_cur_id,32, x_txn_systems_tbl(l_ind).attribute15);
2340 dbms_sql.column_value(l_sys_cur_id,33, x_txn_systems_tbl(l_ind).object_version_number);
2341
2342 END LOOP;
2343
2344 dbms_sql.close_cursor(l_sys_cur_id);
2345
2346 EXCEPTION
2347 WHEN others THEN
2348
2349 csi_t_gen_utility_pvt.add(sqlerrm);
2350
2351 IF dbms_sql.is_open(l_sys_cur_id) THEN
2352 dbms_sql.close_cursor(l_sys_cur_id);
2353 END IF;
2354
2355 END get_txn_systems;
2356
2357 */
2358
2359 PROCEDURE get_transaction_details(
2360 p_api_version IN NUMBER
2361 ,p_commit IN VARCHAR2 := fnd_api.g_false
2362 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2363 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2364 ,p_txn_line_query_rec IN csi_t_datastructures_grp.txn_line_query_rec
2365 ,p_txn_line_detail_query_rec IN csi_t_datastructures_grp.txn_line_detail_query_rec
2366 ,x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
2367 ,p_get_parties_flag IN VARCHAR2 := fnd_api.g_false
2368 ,x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
2369 ,p_get_pty_accts_flag IN VARCHAR2 := fnd_api.g_false
2370 ,x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
2371 ,p_get_ii_rltns_flag IN VARCHAR2 := fnd_api.g_false
2372 ,x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
2373 ,p_get_org_assgns_flag IN VARCHAR2 := fnd_api.g_false
2374 ,x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
2375 ,p_get_ext_attrib_vals_flag IN VARCHAR2 := fnd_api.g_false
2376 ,x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
2377 ,p_get_csi_attribs_flag IN VARCHAR2 := fnd_api.g_false
2378 ,x_csi_ext_attribs_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attribs_tbl
2379 ,p_get_csi_iea_values_flag IN VARCHAR2 := fnd_api.g_false
2380 ,x_csi_iea_values_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attrib_vals_tbl
2381 ,p_get_txn_systems_flag IN VARCHAR2 := fnd_api.g_false
2382 ,x_txn_systems_tbl OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl
2383 ,x_return_status OUT NOCOPY VARCHAR2
2384 ,x_msg_count OUT NOCOPY NUMBER
2385 ,x_msg_data OUT NOCOPY VARCHAR2)
2386 IS
2387
2388 l_api_name CONSTANT VARCHAR2(30) := 'get_transaction_dtls';
2389 l_api_version CONSTANT NUMBER := 1.0;
2390 l_debug_level NUMBER;
2391
2392 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2393
2394 l_line_dtl_id_list varchar2(1000);
2395 l_party_dtl_id_list varchar2(1000);
2396 l_txn_line_id_list varchar2(1000);
2397 l_instance_id_list varchar2(1000);
2398 l_txn_system_id_list varchar2(1000);
2399
2400 l_txn_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
2401 l_txn_party_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2402 l_txn_pty_acct_dtl_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2403 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
2404 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
2405 l_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
2406 l_csi_ext_attribs_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
2407 l_csi_ea_vals_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
2408 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
2409 l_index NUMBER := 0 ;
2410 l_txn_line_id NUMBER ;
2411 l_relation_exists BOOLEAN ;
2412
2413 CURSOR txn_ii_rel_cur (c_txn_line_detail_id IN NUMBER)
2414 IS
2415 SELECT a.txn_relationship_id, a.transaction_line_id,a.csi_inst_relationship_id,
2416 a.subject_id, a.subject_type, a.object_id, a.object_type , a.relationship_type_code, a.display_order,
2417 a.position_reference, a.mandatory_flag, a.active_start_date, a.active_end_date,
2418 a.context, a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5,
2419 a.attribute6, a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11,
2420 a.attribute12, a.attribute13, a.attribute14, a.attribute15, a.object_version_number ,
2421 a.sub_config_inst_hdr_id , a.sub_config_inst_rev_num , a.sub_config_inst_item_id ,
2422 a.obj_config_inst_hdr_id , a.obj_config_inst_rev_num , a.obj_config_inst_item_id, a.target_commitment_date , a.transfer_components_flag
2423 FROM csi_t_ii_relationships a
2424 WHERE ((a.object_id = c_txn_line_detail_id
2425 AND a.object_type = 'T') OR (a.subject_id = c_txn_line_detail_id
2426 AND a.subject_type = 'T'))
2427 GROUP BY a.txn_relationship_id, a.transaction_line_id,a.csi_inst_relationship_id, a.subject_id, a.subject_type, a.object_id, a.object_type , a.relationship_type_code, a.display_order,
2428 a.position_reference, a.mandatory_flag, a.active_start_date, a.active_end_date,
2429 a.context, a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5,
2430 a.attribute6, a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11,
2431 a.attribute12, a.attribute13, a.attribute14, a.attribute15, a.object_version_number ,
2432 a.sub_config_inst_hdr_id , a.sub_config_inst_rev_num , a.sub_config_inst_item_id ,
2433 a.obj_config_inst_hdr_id , a.obj_config_inst_rev_num , a.obj_config_inst_item_id , a.target_commitment_date , a.transfer_components_flag;
2434
2435 l_swap_loc_type VARCHAR2(60);
2436 l_swap_loc_id NUMBER;
2437
2438 BEGIN
2439
2440 -- Standard Start of API savepoint
2441 SAVEPOINT Get_Transaction_Dtls;
2442
2443 -- Initialize message list if p_init_msg_list is set to TRUE.
2444 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2445 FND_MSG_PUB.initialize;
2446 END IF;
2447
2448 -- Initialize API return status to success
2449 x_return_status := FND_API.G_RET_STS_SUCCESS;
2450
2451 -- Standard call to check for call compatibility.
2452 IF NOT
2453
2454 FND_API.Compatible_API_Call (
2455 p_current_version_number => l_api_version,
2456 p_caller_version_number => p_api_version,
2457 p_api_name => l_api_name,
2458 p_pkg_name => g_pkg_name) THEN
2459
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461
2462 END IF;
2463
2464 -- debug messages
2465 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
2466
2467 csi_t_gen_utility_pvt.dump_api_info(
2468 p_pkg_name => g_pkg_name,
2469 p_api_name => l_api_name);
2470
2471 csi_t_gen_utility_pvt.add(
2472 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
2473
2474 IF l_debug_level > 1 THEN
2475
2476 csi_t_gen_utility_pvt.dump_txn_line_query_rec(
2477 p_txn_line_query_rec => p_txn_line_query_rec);
2478
2479 csi_t_gen_utility_pvt.dump_txn_line_detail_query_rec(
2480 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec);
2481
2482 END IF;
2483
2484 -- Main API code
2485
2486 csi_t_txn_line_dtls_pvt.get_txn_line_dtls(
2487 p_txn_line_query_rec => p_txn_line_query_rec,
2488 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
2489 x_txn_line_dtl_tbl => l_txn_line_dtl_tbl,
2490 x_return_status => l_return_status);
2491
2492 -- 07-17 Modifed while CZ enhancements
2493 -- Logic for getting relations changed, now it is driven
2494 -- by the transaction line details return by the csi_t_txn_line_dtls_pvt.get_txn_line_dtls API
2495 IF l_txn_line_dtl_tbl.COUNT > 0
2496 THEN
2497
2498 csi_t_gen_utility_pvt.add('l_txn_line_dtl_tbl.COUNT :'||l_txn_line_dtl_tbl.COUNT);
2499
2500 FOR i IN l_txn_line_dtl_tbl.FIRST .. l_txn_line_dtl_tbl.LAST
2501 LOOP
2502
2503 /* Commented for Bug 3419098
2504 -- Begin swap values for location_id and Install_location_id
2505
2506 IF l_txn_line_dtl_tbl(i).location_id is not null
2507 THEN
2508 l_swap_loc_type := l_txn_line_dtl_tbl(i).Install_location_type_code;
2509 l_swap_loc_id := l_txn_line_dtl_tbl(i).Install_location_id;
2510
2511 l_txn_line_dtl_tbl(i).Install_location_type_code := l_txn_line_dtl_tbl(i).location_type_code;
2512 l_txn_line_dtl_tbl(i).Install_location_id := l_txn_line_dtl_tbl(i).location_id;
2513
2514 l_txn_line_dtl_tbl(i).location_id := l_swap_loc_id;
2515 l_txn_line_dtl_tbl(i).location_type_code := l_swap_loc_type;
2516 END IF;
2517
2518 -- End swap values for location_id and Install_location_id
2519 End comment for Bug 3419098 */
2520
2521 csi_t_gen_utility_pvt.add('Line Dtl ID'||i||': '||l_txn_line_dtl_tbl(i).txn_line_detail_id);
2522 FOR txn_ii_rel_rec IN txn_ii_rel_cur(l_txn_line_dtl_tbl(i).txn_line_detail_id)
2523 LOOP
2524 l_relation_exists := FALSE ;
2525 IF x_txn_ii_rltns_tbl.COUNT > 0
2526 THEN
2527 FOR j IN x_txn_ii_rltns_tbl.FIRST .. x_txn_ii_rltns_tbl.LAST
2528 LOOP
2529 IF txn_ii_rel_rec.txn_relationship_id =
2530 x_txn_ii_rltns_tbl(j).txn_relationship_id
2531 THEN
2532 l_relation_exists := TRUE ;
2533 EXIT ;
2534 END IF ;
2535 END LOOP ; --j IN x_txn_ii_rltns_tbl.FIRST
2536 END IF ; --x_txn_ii_rltns_tbl.COUNT > 0
2537
2538 IF NOT l_relation_exists
2539 THEN
2540 l_index := NVL(x_txn_ii_rltns_tbl.LAST,0) + 1 ;
2541
2542 x_txn_ii_rltns_tbl(l_index).txn_relationship_id := txn_ii_rel_rec.txn_relationship_id ;
2543 x_txn_ii_rltns_tbl(l_index).transaction_line_id := txn_ii_rel_rec.transaction_line_id;
2544 x_txn_ii_rltns_tbl(l_index).csi_inst_relationship_id := txn_ii_rel_rec.csi_inst_relationship_id;
2545 x_txn_ii_rltns_tbl(l_index).subject_id := txn_ii_rel_rec.subject_id ;
2546 x_txn_ii_rltns_tbl(l_index).subject_type := txn_ii_rel_rec.subject_type ;
2547 x_txn_ii_rltns_tbl(l_index).object_id := txn_ii_rel_rec.object_id ;
2548 x_txn_ii_rltns_tbl(l_index).object_type := txn_ii_rel_rec.object_type ;
2549 x_txn_ii_rltns_tbl(l_index).relationship_type_code := txn_ii_rel_rec.relationship_type_code ;
2550 x_txn_ii_rltns_tbl(l_index).display_order := txn_ii_rel_rec.display_order ;
2551 x_txn_ii_rltns_tbl(l_index).position_reference := txn_ii_rel_rec.position_reference ;
2552 x_txn_ii_rltns_tbl(l_index).mandatory_flag := txn_ii_rel_rec.mandatory_flag ;
2553 x_txn_ii_rltns_tbl(l_index).active_start_date := txn_ii_rel_rec.active_start_date ;
2554 x_txn_ii_rltns_tbl(l_index).active_end_date := txn_ii_rel_rec.active_end_date ;
2555 x_txn_ii_rltns_tbl(l_index).attribute2 := txn_ii_rel_rec.attribute2 ;
2556 x_txn_ii_rltns_tbl(l_index).attribute3 := txn_ii_rel_rec.attribute3 ;
2557 x_txn_ii_rltns_tbl(l_index).attribute4 := txn_ii_rel_rec.attribute4 ;
2558 x_txn_ii_rltns_tbl(l_index).attribute5 := txn_ii_rel_rec.attribute5 ;
2559 x_txn_ii_rltns_tbl(l_index).attribute6 := txn_ii_rel_rec.attribute6 ;
2560 x_txn_ii_rltns_tbl(l_index).attribute7 := txn_ii_rel_rec.attribute7 ;
2561 x_txn_ii_rltns_tbl(l_index).attribute8 := txn_ii_rel_rec.attribute8 ;
2562 x_txn_ii_rltns_tbl(l_index).attribute9 := txn_ii_rel_rec.attribute9 ;
2563 x_txn_ii_rltns_tbl(l_index).attribute10 := txn_ii_rel_rec.attribute10 ;
2564 x_txn_ii_rltns_tbl(l_index).attribute11 := txn_ii_rel_rec.attribute11 ;
2565 x_txn_ii_rltns_tbl(l_index).attribute12 := txn_ii_rel_rec.attribute12 ;
2566 x_txn_ii_rltns_tbl(l_index).attribute13 := txn_ii_rel_rec.attribute13 ;
2567 x_txn_ii_rltns_tbl(l_index).attribute14 := txn_ii_rel_rec.attribute14 ;
2568 x_txn_ii_rltns_tbl(l_index).attribute15 := txn_ii_rel_rec.attribute15 ;
2569 x_txn_ii_rltns_tbl(l_index).object_version_number := txn_ii_rel_rec.object_version_number ;
2570 x_txn_ii_rltns_tbl(l_index).sub_config_inst_hdr_id := txn_ii_rel_rec.sub_config_inst_hdr_id ;
2571 x_txn_ii_rltns_tbl(l_index).sub_config_inst_rev_num := txn_ii_rel_rec.sub_config_inst_rev_num ;
2572 x_txn_ii_rltns_tbl(l_index).sub_config_inst_item_id := txn_ii_rel_rec.sub_config_inst_item_id ;
2573 x_txn_ii_rltns_tbl(l_index).obj_config_inst_hdr_id := txn_ii_rel_rec.obj_config_inst_hdr_id ;
2574 x_txn_ii_rltns_tbl(l_index).obj_config_inst_rev_num := txn_ii_rel_rec.obj_config_inst_rev_num ;
2575 x_txn_ii_rltns_tbl(l_index).obj_config_inst_item_id := txn_ii_rel_rec.obj_config_inst_item_id ;
2576 x_txn_ii_rltns_tbl(l_index).target_commitment_date := txn_ii_rel_rec.target_commitment_date ;
2577 x_txn_ii_rltns_tbl(l_index).transfer_components_flag := txn_ii_rel_rec.transfer_components_flag ;
2578
2579 csi_t_gen_utility_pvt.add('Transaction Relationship ID'||l_index||': '||x_txn_ii_rltns_tbl(l_index).txn_relationship_id);
2580
2581 END IF ; --l_retaionhip_exists
2582 END LOOP ; --txn_ii_rel_rec IN txn_ii_rel_cur
2583 END LOOP ; --l_txn_line_dtl_tbl.FIRST .. l_txn_line_dtl_tbl.LAST
2584 END IF ; ---l_txn_line_dtl_tbl.COUNT>0
2585
2586 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2587 csi_t_gen_utility_pvt.add('csi_t_txn_line_dtls_pvt.get_txn_line_dtls failed');
2588 RAISE fnd_api.g_exc_error;
2589 END IF;
2590
2591 x_txn_line_detail_tbl := l_txn_line_dtl_tbl;
2592
2593 IF l_txn_line_dtl_tbl.COUNT > 0 THEN
2594
2595 l_line_dtl_id_list := null;
2596
2597 IF p_get_parties_flag = fnd_api.g_true THEN
2598
2599 csi_t_txn_parties_pvt.get_all_party_dtls(
2600 p_line_detail_tbl => l_txn_line_dtl_tbl,
2601 x_party_detail_tbl => l_txn_party_dtl_tbl,
2602 x_return_status => l_return_status);
2603
2604 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2605 RAISE fnd_api.g_exc_error;
2606 END IF;
2607
2608 x_txn_party_detail_tbl := l_txn_party_dtl_tbl;
2609
2610 IF l_txn_party_dtl_tbl.COUNT > 0 THEN
2611
2612 /* Commented for Bug 2543266
2613
2614 l_party_dtl_id_list := null;
2615
2616 csi_t_utilities_pvt.build_party_dtl_id_list(
2617 p_txn_party_detial_tbl => l_txn_party_dtl_tbl,
2618 x_party_dtl_id_list => l_party_dtl_id_list,
2619 x_return_status => l_return_status);
2620
2621 csi_t_gen_utility_pvt.add('Party Dtl IDs: '||l_party_dtl_id_list);
2622 */
2623
2624 For tdp in l_txn_party_dtl_tbl.FIRST .. l_txn_party_dtl_tbl.LAST
2625 LOOP
2626 -- Displaying the TD Party Detail's resultset... Replacement for the Commented code below . Bug 2543266
2627
2628 csi_t_gen_utility_pvt.add('Party Dtl ID'||tdp||': '||l_txn_party_dtl_tbl(tdp).txn_party_detail_id);
2629
2630 END LOOP;
2631
2632 IF p_get_pty_accts_flag = fnd_api.g_true THEN
2633
2634 csi_t_txn_parties_pvt.get_all_pty_acct_dtls(
2635 p_party_detail_tbl => l_txn_party_dtl_tbl,
2636 x_pty_acct_detail_tbl => l_txn_pty_acct_dtl_tbl,
2637 x_return_status => l_return_status);
2638
2639 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2640 RAISE fnd_api.g_exc_error;
2641 END IF;
2642
2643 x_txn_pty_acct_detail_tbl := l_txn_pty_acct_dtl_tbl;
2644
2645 END IF;
2646
2647 END IF;
2648 END IF;
2649
2650 IF p_get_ext_attrib_vals_flag = fnd_api.g_true THEN
2651
2652 csi_t_txn_attribs_pvt.get_all_ext_attrib_dtls(
2653 p_txn_line_detail_tbl => l_txn_line_dtl_tbl,
2654 x_ext_attrib_tbl => l_ext_attrib_tbl,
2655 x_return_status => l_return_status);
2656
2657 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2658 RAISE fnd_api.g_exc_error;
2659 END IF;
2660
2661 x_txn_ext_attrib_vals_tbl := l_ext_attrib_tbl;
2662
2663 END IF;
2664
2665 IF p_get_csi_attribs_flag = fnd_api.g_true THEN
2666
2667 csi_t_txn_attribs_pvt.get_all_csi_ext_attribs(
2668 p_txn_line_detail_tbl => l_txn_line_dtl_tbl,
2669 x_csi_ext_attribs_tbl => l_csi_ext_attribs_tbl,
2670 x_return_status => l_return_status);
2671
2672 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2673 RAISE fnd_api.g_exc_error;
2674 END IF;
2675
2676 x_csi_ext_attribs_tbl := l_csi_ext_attribs_tbl;
2677
2678 END IF;
2679
2680 IF p_get_csi_iea_values_flag = fnd_api.g_true THEN
2681
2682 csi_t_txn_attribs_pvt.get_all_csi_ext_attrib_vals(
2683 p_txn_line_detail_tbl => l_txn_line_dtl_tbl,
2684 x_csi_ea_vals_tbl => l_csi_ea_vals_tbl,
2685 x_return_status => l_return_status);
2686
2687 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2688 RAISE fnd_api.g_exc_error;
2689 END IF;
2690
2691 x_csi_iea_values_tbl := l_csi_ea_vals_tbl;
2692
2693 END IF;
2694
2695 IF p_get_org_assgns_flag = fnd_api.g_true THEN
2696
2697 csi_t_txn_ous_pvt.get_all_org_assgn_dtls(
2698 p_txn_line_detail_tbl => l_txn_line_dtl_tbl,
2699 x_org_assgn_tbl => l_org_assgn_tbl,
2700 x_return_status => l_return_status);
2701
2702 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2703 RAISE fnd_api.g_exc_error;
2704 END IF;
2705
2706 x_txn_org_assgn_tbl := l_org_assgn_tbl;
2707
2708 END IF;
2709
2710 END IF;
2711
2712 ---Kishor 05/09
2713 IF l_txn_line_dtl_tbl.COUNT > 0
2714 THEN
2715 IF NVL(p_txn_line_query_rec.transaction_line_id,fnd_api.g_miss_num) <>
2716 fnd_api.g_miss_num
2717 THEN
2718 l_txn_line_id := p_txn_line_query_rec.transaction_line_id ;
2719 ELSE
2720 l_txn_line_id := l_txn_line_dtl_tbl(1).transaction_line_id ;
2721 END IF ;
2722 END IF ;
2723
2724 -- get txn systems
2725 IF p_get_txn_systems_flag = fnd_api.g_true THEN
2726
2727 /* Changes for Bug 2543266
2728 l_txn_system_id_list := null;
2729
2730 csi_t_utilities_pvt.build_txn_system_id_list(
2731 p_txn_line_detial_tbl => l_txn_line_dtl_tbl,
2732 x_txn_system_id_list => l_txn_system_id_list,
2733 x_return_status => l_return_status);
2734
2735 IF l_txn_system_id_list is not null then
2736
2737 csi_t_gen_utility_pvt.add('Txn System IDs List: '||l_txn_system_id_list);
2738
2739 get_txn_systems(
2740 p_txn_system_id_list => l_txn_system_id_list,
2741 x_txn_systems_tbl => l_txn_systems_tbl,
2742 x_return_status => l_return_status);
2743
2744 x_txn_systems_tbl := l_txn_systems_tbl;
2745
2746 END IF;
2747 */
2748
2749 get_txn_systems(
2750 p_txn_line_dtl_tbl => l_txn_line_dtl_tbl,
2751 x_txn_systems_tbl => l_txn_systems_tbl,
2752 x_return_status => l_return_status);
2753
2754 x_txn_systems_tbl := l_txn_systems_tbl;
2755
2756 IF l_txn_systems_tbl.COUNT > 0 THEN
2757 For tds in l_txn_systems_tbl.FIRST .. l_txn_systems_tbl.LAST
2758 LOOP
2759 -- Displaying the TD Systems's resultset... Replacement for the Commented code below . Bug 2543266
2760
2761 csi_t_gen_utility_pvt.add('Transaction System ID'||tds||': '||l_txn_systems_tbl(tds).transaction_system_id);
2762
2763 END LOOP;
2764 END IF;
2765
2766 END IF;
2767
2768 -- Standard check of p_commit.
2769 IF FND_API.To_Boolean( p_commit ) THEN
2770 COMMIT WORK;
2771 END IF;
2772
2773 csi_t_gen_utility_pvt.add('Get Transaction Details Successful');
2774
2775 -- Standard call to get message count and if count is get message info.
2776 FND_MSG_PUB.Count_And_Get(
2777 p_count => x_msg_count,
2778 p_data => x_msg_data);
2779
2780 EXCEPTION
2781 WHEN FND_API.G_EXC_ERROR THEN
2782
2783 ROLLBACK TO Get_Transaction_Dtls;
2784 x_return_status := FND_API.G_RET_STS_ERROR ;
2785
2786 FND_MSG_PUB.Count_And_Get (
2787 p_count => x_msg_count,
2788 p_data => x_msg_data);
2789
2790 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2791
2792 ROLLBACK TO Get_Transaction_Dtls;
2793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2794
2795 FND_MSG_PUB.Count_And_Get(
2796 p_count => x_msg_count,
2797 p_data => x_msg_data);
2798
2799 WHEN OTHERS THEN
2800
2801 ROLLBACK TO Get_Transaction_Dtls;
2802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2803
2804 IF FND_MSG_PUB.Check_Msg_Level(
2805 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2806
2807 FND_MSG_PUB.Add_Exc_Msg(
2808 p_pkg_name => G_PKG_NAME,
2809 p_procedure_name => l_api_name);
2810
2811 END IF;
2812
2813 FND_MSG_PUB.Count_And_Get(
2814 p_count => x_msg_count,
2815 p_data => x_msg_data);
2816
2817 END get_transaction_details;
2818
2819 FUNCTION check_td_for_create(
2820 p_txn_line_dtl_id in number,
2821 p_txn_line_dtl_tbl in csi_t_datastructures_grp.txn_line_detail_tbl)
2822 RETURN boolean
2823 IS
2824 l_return boolean := FALSE;
2825 BEGIN
2826 IF p_txn_line_dtl_tbl.count > 0 THEN
2827 FOR l_ind in p_txn_line_dtl_tbl.first .. p_txn_line_dtl_tbl.LAST
2828 LOOP
2829 IF p_txn_line_dtl_id = p_txn_line_dtl_tbl(l_ind).txn_line_detail_id
2830 THEN
2831 l_return := TRUE;
2832 exit;
2833 END IF;
2834 END LOOP;
2835 END IF;
2836 return l_return;
2837 END check_td_for_create;
2838
2839 PROCEDURE split_transaction_dtls(
2840 p_api_version IN NUMBER,
2841 p_commit IN VARCHAR2 := fnd_api.g_false,
2842 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2843 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2844 p_upd_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
2845 p_upd_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
2846 px_crt_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
2847 px_crt_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
2848 x_return_status OUT NOCOPY VARCHAR2,
2849 x_msg_count OUT NOCOPY NUMBER,
2850 x_msg_data OUT NOCOPY VARCHAR2)
2851 IS
2852
2853 l_api_name CONSTANT VARCHAR2(30) := 'split_transaction_dtls';
2854 l_api_version CONSTANT NUMBER := 1.0;
2855 l_debug_level NUMBER;
2856
2857 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
2858 l_msg_count NUMBER;
2859 l_msg_data VARCHAR2(2000);
2860
2861 l_c_line_ind binary_integer;
2862 l_c_pty_ind binary_integer;
2863 l_c_pa_ind binary_integer;
2864 l_c_ii_ind binary_integer;
2865 l_c_oa_ind binary_integer;
2866 l_c_ea_ind binary_integer;
2867
2868 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
2869 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
2870
2871 l_c_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
2872 l_c_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2873 l_c_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2874 l_c_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
2875 l_c_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
2876 l_c_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
2877 l_c_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
2878
2879 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
2880 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2881 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2882 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
2883 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
2884 l_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
2885 l_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
2886 l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
2887 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
2888
2889 l_ln_id_ind binary_integer;
2890
2891 l_line_dtl_ids_tbl csi_t_datastructures_grp.txn_line_detail_ids_tbl;
2892 l_txn_line_id csi_t_transaction_lines.transaction_line_id%type;
2893
2894 l_create boolean := FALSE;
2895
2896 BEGIN
2897
2898
2899 -- Standard Start of API savepoint
2900 SAVEPOINT split_transaction_dtls;
2901
2902 -- Initialize message list if p_init_msg_list is set to TRUE.
2903 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2904 FND_MSG_PUB.Initialize;
2905 END IF;
2906
2907 -- Initialize API return status to success
2908 x_return_status := FND_API.G_RET_STS_SUCCESS;
2909
2910 -- Standard call to check for call compatibility.
2911 IF NOT
2912
2913 FND_API.Compatible_API_Call (
2914 p_current_version_number => l_api_version,
2915 p_caller_version_number => p_api_version,
2916 p_api_name => l_api_name,
2917 p_pkg_name => g_pkg_name) THEN
2918
2919 RAISE FND_API.G_Exc_Unexpected_Error;
2920
2921 END IF;
2922
2923 -- debug messages
2924 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
2925
2926 csi_t_gen_utility_pvt.dump_api_info(
2927 p_pkg_name => g_pkg_name,
2928 p_api_name => l_api_name);
2929
2930 csi_t_gen_utility_pvt.add(
2931 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
2932
2933 csi_t_gen_utility_pvt.add('p_upd_txn_line_rec');
2934
2935 IF l_debug_level > 1 THEN
2936
2937 csi_t_gen_utility_pvt.dump_txn_line_rec(
2938 p_txn_line_rec => p_upd_txn_line_rec);
2939
2940 END IF;
2941
2942 -- Main API code starts here
2943
2944 -- call the update api for the update information
2945 csi_t_txn_details_grp.update_txn_line_dtls(
2946 p_api_version => p_api_version,
2947 p_commit => p_commit,
2948 p_init_msg_list => p_init_msg_list,
2949 p_validation_level => p_validation_level,
2950 p_txn_line_rec => p_upd_txn_line_rec,
2951 p_txn_line_detail_tbl => p_upd_txn_line_dtl_tbl,
2952 px_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2953 px_txn_party_detail_tbl => l_pty_dtl_tbl,
2954 px_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2955 px_txn_org_assgn_tbl => l_org_assgn_tbl,
2956 px_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
2957 x_return_status => l_return_status,
2958 x_msg_count => l_msg_count,
2959 x_msg_data => l_msg_data);
2960
2961 --loop the create table
2962 l_ln_id_ind := 0;
2963
2964 IF px_crt_txn_line_dtl_tbl.COUNT > 0 THEN
2965 FOR l_ind in px_crt_txn_line_dtl_tbl.FIRST .. px_crt_txn_line_dtl_tbl.LAST
2966 LOOP
2967
2968 l_create :=
2969 check_td_for_create(
2970 p_txn_line_dtl_id =>
2971 px_crt_txn_line_dtl_tbl(l_ind).txn_line_detail_id,
2972 p_txn_line_dtl_tbl => p_upd_txn_line_dtl_tbl);
2973
2974 IF (l_create) THEN
2975
2976 l_txn_line_detail_query_rec.txn_line_detail_id :=
2977 px_crt_txn_line_dtl_tbl(l_ind).txn_line_detail_id;
2978
2979 --call the get api to fetch all the child entities
2980 csi_t_txn_details_grp.get_transaction_details(
2981 p_api_version => p_api_version,
2982 p_commit => p_commit,
2983 p_init_msg_list => p_init_msg_list,
2984 p_validation_level => p_validation_level,
2985 p_txn_line_query_rec => l_txn_line_query_rec,
2986 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2987 x_txn_line_detail_tbl => l_line_dtl_tbl,
2988 p_get_parties_flag => 'Y',
2989 x_txn_party_detail_tbl => l_pty_dtl_tbl,
2990 p_get_pty_accts_flag => 'Y',
2991 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2992 p_get_ii_rltns_flag => 'Y',
2993 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2994 p_get_org_assgns_flag => 'Y',
2995 x_txn_org_assgn_tbl => l_org_assgn_tbl,
2996 p_get_ext_attrib_vals_flag => 'Y',
2997 x_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
2998 p_get_csi_attribs_flag => 'N',
2999 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
3000 p_get_csi_iea_values_flag => 'N',
3001 x_csi_iea_values_tbl => l_csi_eav_tbl,
3002 p_get_txn_systems_flag => 'Y',
3003 x_txn_systems_tbl => l_txn_systems_tbl,
3004 x_return_status => l_return_status,
3005 x_msg_count => l_msg_count,
3006 x_msg_data => l_msg_data);
3007
3008 l_line_dtl_tbl(1).quantity :=
3009 px_crt_txn_line_dtl_tbl(l_ind).quantity;
3010
3011 -- append to the table for create (call merge tables)
3012
3013 csi_t_utilities_pvt.merge_tables(
3014 px_line_dtl_tbl => l_c_line_dtl_tbl,
3015 px_pty_dtl_tbl => l_c_pty_dtl_tbl,
3016 px_pty_acct_tbl => l_c_pty_acct_tbl,
3017 px_ii_rltns_tbl => l_c_ii_rltns_tbl,
3018 px_org_assgn_tbl => l_c_org_assgn_tbl,
3019 px_ext_attrib_tbl => l_c_ext_attrib_tbl,
3020 px_txn_systems_tbl => l_c_txn_systems_tbl,
3021 p_line_dtl_tbl => l_line_dtl_tbl,
3022 p_pty_dtl_tbl => l_pty_dtl_tbl,
3023 p_pty_acct_tbl => l_pty_acct_tbl,
3024 p_ii_rltns_tbl => l_ii_rltns_tbl,
3025 p_org_assgn_tbl => l_org_assgn_tbl,
3026 p_ext_attrib_tbl => l_ext_attrib_tbl,
3027 p_txn_systems_tbl => l_txn_systems_tbl);
3028
3029 ELSE
3030 -- populate txn_line_dtl_ids table to switch the txn_line_id
3031 l_ln_id_ind := l_ln_id_ind +1;
3032
3033 l_line_dtl_ids_tbl(l_ln_id_ind).txn_line_detail_id :=
3034 px_crt_txn_line_dtl_tbl(l_ind).txn_line_detail_id;
3035
3036 END IF;
3037
3038 END LOOP;
3039
3040 END IF;
3041
3042 IF l_c_line_dtl_tbl.count > 0 THEN
3043
3044 csi_t_utilities_pvt.convert_ids_to_index(
3045 px_line_dtl_tbl => l_c_line_dtl_tbl,
3046 px_pty_dtl_tbl => l_c_pty_dtl_tbl,
3047 px_pty_acct_tbl => l_c_pty_acct_tbl,
3048 px_ii_rltns_tbl => l_c_ii_rltns_tbl,
3049 px_org_assgn_tbl => l_c_org_assgn_tbl,
3050 px_ext_attrib_tbl => l_c_ext_attrib_tbl,
3051 px_txn_systems_tbl => l_c_txn_systems_tbl);
3052
3053 csi_t_txn_details_grp.create_transaction_dtls(
3054 p_api_version => p_api_version,
3055 p_commit => p_commit,
3056 p_init_msg_list => p_init_msg_list,
3057 p_validation_level => p_validation_level,
3058 px_txn_line_rec => px_crt_txn_line_rec,
3059 px_txn_line_detail_tbl => l_c_line_dtl_tbl,
3060 px_txn_party_detail_tbl => l_c_pty_dtl_tbl,
3061 px_txn_pty_acct_detail_tbl => l_c_pty_acct_tbl,
3062 px_txn_ii_rltns_tbl => l_c_ii_rltns_tbl,
3063 px_txn_org_assgn_tbl => l_c_org_assgn_tbl,
3064 px_txn_ext_attrib_vals_tbl => l_c_ext_attrib_tbl,
3065 px_txn_systems_tbl => l_c_txn_systems_tbl,
3066 x_return_status => l_return_status,
3067 x_msg_count => l_msg_count,
3068 x_msg_data => l_msg_data);
3069
3070 IF l_return_status = fnd_api.g_ret_sts_success THEN
3071
3072 SELECT transaction_line_id
3073 INTO l_txn_line_id
3074 FROM csi_t_transaction_lines
3075 WHERE source_transaction_table = px_crt_txn_line_rec.source_transaction_table
3076 AND source_transaction_id = px_crt_txn_line_rec.source_transaction_id;
3077
3078 ELSE
3079 raise fnd_api.g_exc_error;
3080 END IF;
3081
3082 ELSE
3083
3084 csi_t_gen_utility_pvt.dump_api_info(
3085 p_api_name => 'insert_row',
3086 p_pkg_name => 'csi_t_transaction_lines_pkg');
3087
3088 csi_t_transaction_lines_pkg.insert_row(
3089 px_transaction_line_id => l_txn_line_id,
3090 p_source_transaction_type_id => px_crt_txn_line_rec.source_transaction_type_id,
3091 p_source_transaction_table => px_crt_txn_line_rec.source_transaction_table,
3092 p_source_txn_header_id => px_crt_txn_line_rec.source_txn_header_id,
3093 p_source_transaction_id => px_crt_txn_line_rec.source_transaction_id,
3094 -- Added for CZ Integration (Begin)
3095 p_config_session_hdr_id => px_crt_txn_line_rec.config_session_hdr_id ,
3096 p_config_session_rev_num => px_crt_txn_line_rec.config_session_rev_num ,
3097 p_config_session_item_id => px_crt_txn_line_rec.config_session_item_id ,
3098 p_config_valid_status => px_crt_txn_line_rec.config_valid_status ,
3099 p_source_transaction_status => px_crt_txn_line_rec.source_transaction_status ,
3100 -- Added for CZ Integration (End)
3101 p_error_code => px_crt_txn_line_rec.error_code,
3102 p_error_explanation => px_crt_txn_line_rec.error_explanation,
3103 p_processing_status => px_crt_txn_line_rec.processing_status,
3104 p_attribute1 => px_crt_txn_line_rec.attribute1,
3105 p_attribute2 => px_crt_txn_line_rec.attribute2,
3106 p_attribute3 => px_crt_txn_line_rec.attribute3,
3107 p_attribute4 => px_crt_txn_line_rec.attribute4,
3108 p_attribute5 => px_crt_txn_line_rec.attribute5,
3109 p_attribute6 => px_crt_txn_line_rec.attribute6,
3110 p_attribute7 => px_crt_txn_line_rec.attribute7,
3111 p_attribute8 => px_crt_txn_line_rec.attribute8,
3112 p_attribute9 => px_crt_txn_line_rec.attribute9,
3113 p_attribute10 => px_crt_txn_line_rec.attribute10,
3114 p_attribute11 => px_crt_txn_line_rec.attribute11,
3115 p_attribute12 => px_crt_txn_line_rec.attribute12,
3116 p_attribute13 => px_crt_txn_line_rec.attribute13,
3117 p_attribute14 => px_crt_txn_line_rec.attribute14,
3118 p_attribute15 => px_crt_txn_line_rec.attribute15,
3119 p_created_by => fnd_global.user_id,
3120 p_creation_date => sysdate,
3121 p_last_updated_by => fnd_global.user_id,
3122 p_last_update_date => sysdate,
3123 p_last_update_login => fnd_global.login_id,
3124 p_object_version_number => 1.0,
3125 p_context => px_crt_txn_line_rec.context);
3126
3127 END IF;
3128
3129 IF l_line_dtl_ids_tbl.count > 0 THEN
3130
3131 FOR l_ind in l_line_dtl_ids_tbl.FIRST .. l_line_dtl_ids_tbl.LAST
3132 LOOP
3133
3134 UPDATE csi_t_txn_line_details
3135 SET transaction_line_id = l_txn_line_id
3136 WHERE txn_line_detail_id = l_line_dtl_ids_tbl(l_ind).txn_line_detail_id;
3137
3138 END LOOP;
3139
3140 END IF;
3141
3142 -- Standard check of p_commit.
3143 IF FND_API.To_Boolean( p_commit ) THEN
3144 COMMIT WORK;
3145 END IF;
3146
3147 -- Standard call to get message count and if count is get message info.
3148 FND_MSG_PUB.Count_And_Get(
3149 p_count => x_msg_count,
3150 p_data => x_msg_data);
3151
3152 EXCEPTION
3153 WHEN fnd_api.g_exc_error THEN
3154
3155 ROLLBACK TO split_transaction_dtls;
3156 x_return_status := FND_API.G_RET_STS_ERROR ;
3157 FND_MSG_PUB.Count_And_Get (
3158 p_count => x_msg_count,
3159 p_data => x_msg_data);
3160
3161 WHEN fnd_api.g_exc_unexpected_error THEN
3162
3163 ROLLBACK TO split_transaction_dtls;
3164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3165
3166 FND_MSG_PUB.Count_And_Get(
3167 p_count => x_msg_count,
3168 p_data => x_msg_data);
3169
3170 WHEN others THEN
3171
3172 ROLLBACK TO split_transaction_dtls;
3173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174
3175 IF FND_MSG_PUB.Check_Msg_Level(
3176 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3177
3178 FND_MSG_PUB.Add_Exc_Msg(
3179 p_pkg_name => G_PKG_NAME,
3180 p_procedure_name => l_api_name);
3181
3182 END IF;
3183
3184 FND_MSG_PUB.Count_And_Get(
3185 p_count => x_msg_count,
3186 p_data => x_msg_data);
3187
3188 END split_transaction_dtls;
3189
3190 PROCEDURE copy_transaction_dtls(
3191 p_api_version IN NUMBER,
3192 p_commit IN VARCHAR2 := fnd_api.g_false,
3193 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3194 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3195 p_src_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
3196 px_new_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
3197 p_copy_parties_flag IN varchar2 := fnd_api.g_true,
3198 p_copy_pty_accts_flag IN varchar2 := fnd_api.g_true,
3199 p_copy_ii_rltns_flag IN varchar2 := fnd_api.g_true,
3200 p_copy_org_assgn_flag IN varchar2 := fnd_api.g_true,
3201 p_copy_ext_attribs_flag IN varchar2 := fnd_api.g_true,
3202 p_copy_txn_systems_flag IN varchar2 := fnd_api.g_true,
3203 x_return_status OUT NOCOPY VARCHAR2,
3204 x_msg_count OUT NOCOPY NUMBER,
3205 x_msg_data OUT NOCOPY VARCHAR2)
3206 IS
3207
3208 l_api_name CONSTANT VARCHAR2(30) := 'copy_transaction_dtls';
3209 l_api_version CONSTANT NUMBER := 1.0;
3210 l_debug_level NUMBER;
3211
3212 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
3213 l_msg_count NUMBER;
3214 l_msg_data VARCHAR2(2000);
3215
3216 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
3217 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
3218 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
3219 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
3220 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
3221 l_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
3222 l_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
3223 l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
3224 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
3225
3226 l_txn_line_id csi_t_transaction_lines.transaction_line_id%type;
3227 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
3228 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
3229
3230 BEGIN
3231
3232 -- Standard Start of API savepoint
3233 SAVEPOINT copy_transaction_dtls;
3234
3235 -- Initialize message list if p_init_msg_list is set to TRUE.
3236 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3237 FND_MSG_PUB.Initialize;
3238 END IF;
3239
3240 -- Initialize API return status to success
3241 x_return_status := FND_API.G_RET_STS_SUCCESS;
3242
3243 -- Standard call to check for call compatibility.
3244 IF NOT
3245
3246 FND_API.Compatible_API_Call (
3247 p_current_version_number => l_api_version,
3248 p_caller_version_number => p_api_version,
3249 p_api_name => l_api_name,
3250 p_pkg_name => g_pkg_name) THEN
3251
3252 RAISE FND_API.G_Exc_Unexpected_Error;
3253
3254 END IF;
3255 --debug messages
3256 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
3257
3258 csi_t_gen_utility_pvt.dump_api_info(
3259 p_api_name => l_api_name,
3260 p_pkg_name => g_pkg_name);
3261
3262 csi_t_gen_utility_pvt.add(
3263 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
3264
3265 IF l_debug_level > 1 THEN
3266
3267 csi_t_gen_utility_pvt.add('Source transaction line record :');
3268
3269 csi_t_gen_utility_pvt.dump_txn_line_rec(
3270 p_txn_line_rec => p_src_txn_line_rec);
3271
3272 csi_t_gen_utility_pvt.add('Destination transaction line record :');
3273
3274 csi_t_gen_utility_pvt.dump_txn_line_rec(
3275 p_txn_line_rec => px_new_txn_line_rec);
3276
3277 END IF;
3278
3279 -- Main API code starts here
3280
3281 -- build the txn_line_query_rec
3282
3283 l_txn_line_query_rec.transaction_line_id :=
3284 p_src_txn_line_rec.transaction_line_id;
3285 l_txn_line_query_rec.source_transaction_table :=
3286 p_src_txn_line_rec.source_transaction_table;
3287 l_txn_line_query_rec.source_transaction_id :=
3288 p_src_txn_line_rec.source_transaction_id;
3289
3290 -- call the get api
3291
3292 csi_t_txn_details_grp.get_transaction_details(
3293 p_api_version => p_api_version,
3294 p_commit => p_commit,
3295 p_init_msg_list => p_init_msg_list,
3296 p_validation_level => p_validation_level,
3297 p_txn_line_query_rec => l_txn_line_query_rec,
3298 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
3299 x_txn_line_detail_tbl => l_line_dtl_tbl,
3300 p_get_parties_flag => p_copy_parties_flag,
3301 x_txn_party_detail_tbl => l_pty_dtl_tbl,
3302 p_get_pty_accts_flag => p_copy_pty_accts_flag,
3303 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
3304 ---05/20 Kishor DONT copy the relations , so dont get it.
3305 p_get_ii_rltns_flag => fnd_api.g_false ,
3306 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
3307 p_get_org_assgns_flag => p_copy_org_assgn_flag,
3308 x_txn_org_assgn_tbl => l_org_assgn_tbl,
3309 p_get_ext_attrib_vals_flag => p_copy_ext_attribs_flag,
3310 x_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
3311 p_get_csi_attribs_flag => fnd_api.g_false,
3312 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
3313 p_get_csi_iea_values_flag => fnd_api.g_false,
3314 x_csi_iea_values_tbl => l_csi_eav_tbl,
3315 p_get_txn_systems_flag => p_copy_txn_systems_flag,
3316 x_txn_systems_tbl => l_txn_systems_tbl,
3317 x_return_status => l_return_status,
3318 x_msg_count => l_msg_count,
3319 x_msg_data => l_msg_data);
3320
3321 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3322 csi_t_gen_utility_pvt.add('Getting the source transaction details for copy failed.');
3323 raise fnd_api.g_exc_error;
3324 END IF;
3325 csi_t_gen_utility_pvt.add('II rltns count before copying : '||l_ii_rltns_tbl.count);
3326
3327 csi_t_gen_utility_pvt.add(l_txn_systems_tbl.count);
3328
3329 IF l_txn_systems_tbl.count > 0 THEN
3330 csi_t_gen_utility_pvt.dump_txn_systems_rec(l_txn_systems_tbl(1));
3331 END IF;
3332
3333 --Kishor 05/15 set the source txn line detail id
3334 FOR i IN l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
3335 LOOP
3336 csi_t_gen_utility_pvt.add('Copying src txn line detail id');
3337 l_line_dtl_tbl(i).source_txn_line_detail_id := l_line_dtl_tbl(i).txn_line_detail_id ;
3338 csi_t_gen_utility_pvt.add('l_line_dtl_tbl(i).source_txn_line_detail_id :'|| l_line_dtl_tbl(i).source_txn_line_detail_id);
3339 END LOOP ;
3340
3341 -- translate the ids in to index
3342 csi_t_utilities_pvt.convert_ids_to_index(
3343 px_line_dtl_tbl => l_line_dtl_tbl,
3344 px_pty_dtl_tbl => l_pty_dtl_tbl,
3345 px_pty_acct_tbl => l_pty_acct_tbl,
3346 px_ii_rltns_tbl => l_ii_rltns_tbl,
3347 px_org_assgn_tbl => l_org_assgn_tbl,
3348 px_ext_attrib_tbl => l_ext_attrib_tbl,
3349 px_txn_systems_tbl => l_txn_systems_tbl);
3350
3351 csi_t_gen_utility_pvt.add(l_txn_systems_tbl.count);
3352 csi_t_gen_utility_pvt.add('II rltns count after converting ids to indexes: '||l_ii_rltns_tbl.count);
3353 csi_t_gen_utility_pvt.add('Line Dtl index :'|| l_line_dtl_tbl(1).txn_line_detail_id);
3354
3355 IF l_txn_systems_tbl.count > 0 THEN
3356 csi_t_gen_utility_pvt.dump_txn_systems_rec(l_txn_systems_tbl(1));
3357 END IF;
3358
3359 -- call the create transaction dtls api
3360 csi_t_txn_details_grp.create_transaction_dtls(
3361 p_api_version => p_api_version,
3362 p_commit => p_commit,
3363 p_init_msg_list => p_init_msg_list,
3364 p_validation_level => p_validation_level,
3365 px_txn_line_rec => px_new_txn_line_rec,
3366 px_txn_line_detail_tbl => l_line_dtl_tbl,
3367 px_txn_party_detail_tbl => l_pty_dtl_tbl,
3368 px_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
3369 px_txn_ii_rltns_tbl => l_ii_rltns_tbl,
3370 px_txn_org_assgn_tbl => l_org_assgn_tbl,
3371 px_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
3372 px_txn_systems_tbl => l_txn_systems_tbl,
3373 x_return_status => l_return_status,
3374 x_msg_count => l_msg_count,
3375 x_msg_data => l_msg_data);
3376
3377 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3378 csi_t_gen_utility_pvt.add('Creating the transaction details for copy failed.');
3379 raise fnd_api.g_exc_error;
3380 END IF;
3381
3382 csi_t_gen_utility_pvt.add('Transaction details copied successfully.');
3383 csi_t_gen_utility_pvt.add('II rltns count after copying : '||l_ii_rltns_tbl.count);
3384
3385 -- Standard check of p_commit.
3386 IF FND_API.To_Boolean( p_commit ) THEN
3387 COMMIT WORK;
3388 END IF;
3389
3390 -- Standard call to get message count and if count is get message info.
3391 FND_MSG_PUB.Count_And_Get(
3392 p_count => x_msg_count,
3393 p_data => x_msg_data);
3394
3395 EXCEPTION
3396 WHEN FND_API.G_EXC_ERROR THEN
3397
3398 ROLLBACK TO copy_transaction_dtls;
3399 x_return_status := FND_API.G_RET_STS_ERROR ;
3400 FND_MSG_PUB.Count_And_Get (
3401 p_count => x_msg_count,
3402 p_data => x_msg_data);
3403
3404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3405
3406 ROLLBACK TO copy_transaction_dtls;
3407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3408
3409 FND_MSG_PUB.Count_And_Get(
3410 p_count => x_msg_count,
3411 p_data => x_msg_data);
3412
3413 WHEN OTHERS THEN
3414
3415 ROLLBACK TO copy_transaction_dtls;
3416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3417
3418 IF FND_MSG_PUB.Check_Msg_Level(
3419 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3420
3421 FND_MSG_PUB.Add_Exc_Msg(
3422 p_pkg_name => G_PKG_NAME,
3423 p_procedure_name => l_api_name);
3424
3425 END IF;
3426
3427 FND_MSG_PUB.Count_And_Get(
3428 p_count => x_msg_count,
3429 p_data => x_msg_data);
3430
3431 END copy_transaction_dtls;
3432
3433 PROCEDURE split_transaction_details(
3434 p_api_version IN NUMBER,
3435 p_commit IN VARCHAR2 := fnd_api.g_false,
3436 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3437 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3438 p_src_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
3439 px_split_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
3440 px_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
3441 x_pty_dtl_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
3442 x_pty_acct_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
3443 x_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
3444 x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
3445 x_txn_systems_tbl OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
3446 x_return_status OUT NOCOPY VARCHAR2,
3447 x_msg_count OUT NOCOPY NUMBER,
3448 x_msg_data OUT NOCOPY VARCHAR2
3449 ) IS
3450
3451 l_item_control_rec csi_order_ship_pub.item_control_rec;
3452 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
3453 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
3454 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
3455 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
3456 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
3457 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
3458 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
3459 l_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
3460 l_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
3461 l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
3462 l_txn_line_id csi_t_transaction_lines.transaction_line_id%type;
3463 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
3464
3465 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
3466 l_msg_count number;
3467 l_msg_data varchar2(2000);
3468 l_api_name CONSTANT varchar2(30) := 'split_transaction_details';
3469 l_api_version CONSTANT number := 1.0;
3470 l_debug_level number;
3471 l_split_line_qty number := 0;
3472 l_match_txn_dtl_qty number := 0;
3473 l_tab_ind number;
3474 BEGIN
3475
3476 -- Standard Start of API savepoint
3477 SAVEPOINT split_transaction_details;
3478
3479 -- Initialize message list if p_init_msg_list is set to TRUE.
3480 IF FND_API.To_Boolean( p_init_msg_list ) THEN
3481 FND_MSG_PUB.Initialize;
3482 END IF;
3483
3484 -- Initialize API return status to success
3485 x_return_status := FND_API.G_RET_STS_SUCCESS;
3486
3487 -- Standard call to check for call compatibility.
3488 IF NOT
3489
3490 FND_API.Compatible_API_Call (
3491 p_current_version_number => l_api_version,
3492 p_caller_version_number => p_api_version,
3493 p_api_name => l_api_name,
3494 p_pkg_name => g_pkg_name) THEN
3495
3496 RAISE FND_API.G_Exc_Unexpected_Error;
3497
3498 END IF;
3499
3500 --debug messages
3501 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
3502
3503 csi_t_gen_utility_pvt.dump_api_info(
3504 p_api_name => l_api_name,
3505 p_pkg_name => g_pkg_name);
3506
3507 csi_t_gen_utility_pvt.add(
3508 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
3509
3510 IF nvl(px_split_txn_line_rec.source_transaction_id,fnd_api.g_miss_num) = fnd_api.g_miss_num OR
3511 nvl(px_split_txn_line_rec.source_transaction_type_id,fnd_api.g_miss_num) = fnd_api.g_miss_num OR
3512 nvl(p_src_txn_line_rec.source_transaction_id,fnd_api.g_miss_num) = fnd_api.g_miss_num OR
3513 nvl(p_src_txn_line_rec.source_transaction_type_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3514
3515 x_msg_data := 'Invalid parameters passed';
3516 x_return_status := fnd_api.g_ret_sts_error;
3517 RAISE FND_API.G_EXC_ERROR;
3518 END IF;
3519
3520 IF l_debug_level > 1 THEN
3521
3522 csi_t_gen_utility_pvt.add('Source transaction line record :');
3523
3524 csi_t_gen_utility_pvt.dump_txn_line_rec(
3525 p_txn_line_rec => p_src_txn_line_rec);
3526
3527 csi_t_gen_utility_pvt.add('Destination transaction line record :');
3528
3529 csi_t_gen_utility_pvt.dump_txn_line_rec(
3530 p_txn_line_rec => px_split_txn_line_rec);
3531
3532 END IF;
3533
3534 -- Main API code starts here
3535 -- build the txn_line_query_rec
3536
3537 l_txn_line_query_rec.transaction_line_id :=
3538 p_src_txn_line_rec.transaction_line_id;
3539 l_txn_line_query_rec.source_transaction_table :=
3540 p_src_txn_line_rec.source_transaction_table;
3541 l_txn_line_query_rec.source_transaction_id :=
3542 p_src_txn_line_rec.source_transaction_id;
3543
3544
3545 csi_t_txn_details_grp.get_transaction_details(
3546 p_api_version => l_api_version,
3547 p_commit => fnd_api.g_false,
3548 p_init_msg_list => fnd_api.g_false,
3549 p_validation_level => fnd_api.g_valid_level_full,
3550 p_txn_line_query_rec => l_txn_line_query_rec,
3551 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
3552 x_txn_line_detail_tbl => l_line_dtl_tbl,
3553 p_get_parties_flag => fnd_api.g_true,
3554 x_txn_party_detail_tbl => l_pty_dtl_tbl,
3555 p_get_pty_accts_flag => fnd_api.g_true,
3556 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
3557 p_get_ii_rltns_flag => fnd_api.g_false ,
3558 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
3559 p_get_org_assgns_flag => fnd_api.g_true,
3560 x_txn_org_assgn_tbl => l_org_assgn_tbl,
3561 p_get_ext_attrib_vals_flag => fnd_api.g_true,
3562 x_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
3563 p_get_csi_attribs_flag => fnd_api.g_false,
3564 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
3565 p_get_csi_iea_values_flag => fnd_api.g_false,
3566 x_csi_iea_values_tbl => l_csi_eav_tbl,
3567 p_get_txn_systems_flag => fnd_api.g_true,
3568 x_txn_systems_tbl => l_txn_systems_tbl,
3569 x_return_status => l_return_status,
3570 x_msg_count => l_msg_count,
3571 x_msg_data => l_msg_data);
3572
3573 IF l_debug_level > 1 AND l_return_status <> fnd_api.g_ret_sts_success THEN
3574 debug('Getting the source transaction details for copy failed.');
3575 raise fnd_api.g_exc_error;
3576 END IF;
3577
3578 BEGIN
3579 SELECT ordered_quantity
3580 INTO l_split_line_qty
3581 FROM oe_order_lines_all
3582 WHERE line_id = px_split_txn_line_rec.source_transaction_id
3583 AND header_id = nvl(px_split_txn_line_rec.source_txn_header_id,header_id);
3584
3585 IF l_debug_level > 1 THEN
3586 debug(' Split RMA line Quantity :'|| l_split_line_qty);
3587 END IF;
3588
3589 IF nvl(l_split_line_qty,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3590 x_msg_data := 'Invalid OM Line quantity '||px_split_txn_line_rec.source_transaction_id;
3591 x_return_status := fnd_api.g_ret_sts_error;
3592 raise FND_API.G_EXC_ERROR;
3593 END IF;
3594 EXCEPTION
3595 WHEN OTHERS THEN
3596 x_msg_data := 'Invalid OM Line id '||px_split_txn_line_rec.source_transaction_id;
3597 x_return_status := fnd_api.g_ret_sts_error;
3598 raise FND_API.G_EXC_ERROR;
3599 END;
3600 csi_utl_pkg.get_item_control_rec(
3601 p_mtl_txn_id => px_line_dtl_tbl(1).inv_mtl_transaction_id,
3602 x_item_control_rec => l_item_control_rec,
3603 x_return_status => l_return_status);
3604
3605 px_line_dtl_tbl.delete; -- Role of input table is complete
3606
3607 IF l_line_dtl_tbl.count = 1 AND
3608 nvl(l_line_dtl_tbl(1).serial_number,fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
3609 px_line_dtl_tbl := l_line_dtl_tbl;
3610 px_line_dtl_tbl(1).processing_status := 'SUBMIT';
3611
3612 IF p_src_txn_line_rec.source_transaction_type_id = 53 THEN
3613 px_line_dtl_tbl(1).quantity := -1 * l_split_line_qty;
3614 ELSE
3615 px_line_dtl_tbl(1).quantity := l_split_line_qty;
3616 END IF;
3617 ELSE -- l_line_dtl_tbl.count > 1
3618 /*-- Processing for serial control and lot-serial control items --*/
3619 IF l_item_control_rec.serial_control_code <> 1 THEN
3620 l_tab_ind := px_line_dtl_tbl.count ;
3621 FOR i IN l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
3622 LOOP
3623 IF l_line_dtl_tbl(i).processing_status <> 'PROCESSED' THEN
3624 l_match_txn_dtl_qty := l_match_txn_dtl_qty + abs(l_line_dtl_tbl(i).quantity);
3625 l_tab_ind := nvl(l_tab_ind,0) +1 ;
3626 px_line_dtl_tbl(l_tab_ind) := l_line_dtl_tbl(i);
3627 px_line_dtl_tbl(l_tab_ind).source_txn_line_detail_id := l_line_dtl_tbl(i).txn_line_detail_id ;
3628 END IF;
3629 END LOOP ;
3630
3631 IF l_debug_level > 1 AND nvl(l_match_txn_dtl_qty,0) <> nvl(l_split_line_qty,0) THEN
3632 debug('Total matching Transaction detail line quantity '||l_match_txn_dtl_qty ||' Does not matches OM line quantity '||l_split_line_qty);
3633 debug('Matching all pending Transaction detail lines for serial/lot control items');
3634 END IF;
3635
3636 ELSE /*-- Processing for non-serial, lot control items --*/
3637 l_tab_ind := px_line_dtl_tbl.count ;
3638 FOR i IN l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
3639 LOOP
3640
3641 IF l_line_dtl_tbl(i).processing_status <> 'PROCESSED' THEN
3642 l_match_txn_dtl_qty := l_match_txn_dtl_qty + abs(l_line_dtl_tbl(i).quantity);
3643 l_tab_ind := nvl(l_tab_ind,0) +1 ;
3644 px_line_dtl_tbl(l_tab_ind) := l_line_dtl_tbl(i);
3645 px_line_dtl_tbl(l_tab_ind).source_txn_line_detail_id := l_line_dtl_tbl(i).txn_line_detail_id ;
3646 END IF;
3647 END LOOP ;
3648
3649 IF l_debug_level > 1 and nvl(l_match_txn_dtl_qty,0) <> nvl(l_split_line_qty,0) THEN
3650 debug('Total matching Transaction detail line quantity '||l_match_txn_dtl_qty ||' Does not matches OM line quantity '||l_split_line_qty);
3651 debug('No match processing for split OM line for non-serial/lot control items');
3652 END IF;
3653
3654 END IF; -- for item serial control
3655 END IF;
3656
3657 FOR l_txn_ind IN 1..px_line_dtl_tbl.count
3658 LOOP
3659 l_tab_ind := x_pty_dtl_tbl.count;
3660 FOR l_pty_ind IN 1..l_pty_dtl_tbl.count
3661 LOOP
3662 IF l_pty_dtl_tbl(l_pty_ind).txn_line_detail_id = px_line_dtl_tbl(l_txn_ind).txn_line_detail_id THEN
3663 l_tab_ind := nvl(l_tab_ind,0) +1;
3664 x_pty_dtl_tbl(l_tab_ind ) := l_pty_dtl_tbl(l_pty_ind);
3665 END IF;
3666 END LOOP;
3667 l_tab_ind := x_org_assgn_tbl.count;
3668 FOR l_org_ass_ind IN 1..l_org_assgn_tbl.count
3669 LOOP
3670 IF l_org_assgn_tbl(l_org_ass_ind).txn_line_detail_id = px_line_dtl_tbl(l_txn_ind).txn_line_detail_id THEN
3671 l_tab_ind := nvl(l_tab_ind,0) +1;
3672 x_org_assgn_tbl(l_tab_ind ) := l_org_assgn_tbl(l_org_ass_ind);
3673 END IF;
3674 END LOOP;
3675 l_tab_ind := x_txn_ext_attrib_vals_tbl.count;
3676 FOR l_extn_attr_ind IN 1..l_ext_attrib_tbl.count
3677 LOOP
3678 IF l_ext_attrib_tbl(l_extn_attr_ind).txn_line_detail_id = px_line_dtl_tbl(l_txn_ind).txn_line_detail_id THEN
3679 l_tab_ind := nvl(l_tab_ind,0) +1;
3680 x_txn_ext_attrib_vals_tbl(l_tab_ind ) := l_ext_attrib_tbl(l_extn_attr_ind);
3681 END IF;
3682 END LOOP;
3683 l_tab_ind := x_txn_systems_tbl.count;
3684
3685 END LOOP;
3686
3687 FOR l_pty_ind IN 1..x_pty_dtl_tbl.count
3688 LOOP
3689 l_tab_ind := x_pty_acct_tbl.count;
3690 FOR l_acc_ind IN 1..l_pty_acct_tbl.count
3691 LOOP
3692 IF l_pty_acct_tbl(l_acc_ind).txn_party_detail_id = x_pty_dtl_tbl(l_pty_ind).txn_party_detail_id THEN
3693 l_tab_ind := nvl(l_tab_ind,0) +1;
3694 x_pty_acct_tbl(l_tab_ind ) := l_pty_acct_tbl(l_acc_ind);
3695 END IF;
3696 END LOOP;
3697 END LOOP;
3698 IF px_line_dtl_tbl.count >0 THEN
3699 x_txn_systems_tbl := l_txn_systems_tbl;
3700 END IF;
3701
3702 -- translate the ids in to index
3703 csi_t_utilities_pvt.convert_ids_to_index(
3704 px_line_dtl_tbl => px_line_dtl_tbl,
3705 px_pty_dtl_tbl => x_pty_dtl_tbl,
3706 px_pty_acct_tbl => x_pty_acct_tbl,
3707 px_ii_rltns_tbl => l_ii_rltns_tbl,
3708 px_org_assgn_tbl => x_org_assgn_tbl,
3709 px_ext_attrib_tbl => x_txn_ext_attrib_vals_tbl,
3710 px_txn_systems_tbl => x_txn_systems_tbl);
3711
3712 IF l_line_dtl_tbl.count > 0 THEN
3713 l_ii_rltns_tbl.delete;
3714
3715 IF l_debug_level > 1 THEN
3716 csi_t_gen_utility_pvt.dump_txn_tables
3717 (
3718 p_ids_or_index_based => 'I'
3719 ,p_line_detail_tbl => px_line_dtl_tbl
3720 ,p_party_detail_tbl => x_pty_dtl_tbl
3721 ,p_pty_acct_tbl => x_pty_acct_tbl
3722 ,p_ii_rltns_tbl => l_ii_rltns_tbl
3723 ,p_org_assgn_tbl => x_org_assgn_tbl
3724 ,p_ea_vals_tbl => x_txn_ext_attrib_vals_tbl
3725 );
3726 END IF;
3727
3728 csi_t_txn_details_grp.create_transaction_dtls
3729 (
3730 p_api_version => p_api_version
3731 ,p_commit => p_commit
3732 ,p_init_msg_list => p_init_msg_list
3733 ,p_validation_level => p_validation_level
3734 ,px_txn_line_rec => px_split_txn_line_rec
3735 ,px_txn_line_detail_tbl => px_line_dtl_tbl
3736 ,px_txn_party_detail_tbl => x_pty_dtl_tbl
3737 ,px_txn_pty_acct_detail_tbl => x_pty_acct_tbl
3738 ,px_txn_ii_rltns_tbl => l_ii_rltns_tbl
3739 ,px_txn_org_assgn_tbl => x_org_assgn_tbl
3740 ,px_txn_ext_attrib_vals_tbl => x_txn_ext_attrib_vals_tbl
3741 ,px_txn_systems_tbl => x_txn_systems_tbl
3742 ,x_return_status => x_return_status
3743 ,x_msg_count => x_msg_count
3744 ,x_msg_data => x_msg_data
3745 );
3746 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3747 debug('Creating the transaction details for copy failed.');
3748 raise fnd_api.g_exc_error;
3749 END IF;
3750
3751 END IF;
3752
3753 -- Standard check of p_commit.
3754 IF FND_API.To_Boolean( p_commit ) THEN
3755 COMMIT WORK;
3756 END IF;
3757
3758 -- Standard call to get message count and if count is get message info.
3759 FND_MSG_PUB.Count_And_Get(
3760 p_count => x_msg_count,
3761 p_data => x_msg_data);
3762
3763 EXCEPTION
3764 WHEN FND_API.G_EXC_ERROR THEN
3765
3766 ROLLBACK TO split_transaction_details;
3767 x_return_status := FND_API.G_RET_STS_ERROR ;
3768 FND_MSG_PUB.Count_And_Get (
3769 p_count => x_msg_count,
3770 p_data => x_msg_data);
3771
3772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3773
3774 ROLLBACK TO split_transaction_details;
3775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3776
3777 FND_MSG_PUB.Count_And_Get(
3778 p_count => x_msg_count,
3779 p_data => x_msg_data);
3780
3781 WHEN OTHERS THEN
3782
3783 ROLLBACK TO split_transaction_details;
3784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3785
3786 IF FND_MSG_PUB.Check_Msg_Level(
3787 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3788
3789 FND_MSG_PUB.Add_Exc_Msg(
3790 p_pkg_name => G_PKG_NAME,
3791 p_procedure_name => l_api_name);
3792
3793 END IF;
3794
3795 FND_MSG_PUB.Count_And_Get(
3796 p_count => x_msg_count,
3797 p_data => x_msg_data);
3798 END split_transaction_details;
3799
3800 END csi_t_txn_details_pvt;