[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_DETAILS_GRP
Source
1 PACKAGE BODY csi_t_txn_details_grp as
2 /*$Header: csigttxb.pls 120.3 2007/02/09 22:00:41 jpwilson ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_details_grp';
5 g_file_name CONSTANT VARCHAR2(12) := 'csigttxb.pls';
6
7 g_user_id NUMBER := FND_GLOBAL.user_id;
8 g_login_id NUMBER := FND_GLOBAL.login_id;
9
10 /*
11 This procedure checks for the existence of a transaction details
12 record in the database . The key to identify the txn line record is
13 transaction_source_table, transaction_source_id.
14 Returns a 'Y' or 'N'
15 */
16
17 FUNCTION check_txn_details_exist(
18 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec)
19 RETURN BOOLEAN
20 IS
21
22 l_found boolean := FALSE;
23 l_api_name CONSTANT VARCHAR2(30) := 'check_txn_details_exist';
24
25 BEGIN
26
27 /**** Commented for Bug 3304439
28 -- Check for the profile option and disable the trace
29 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
30 dbms_session.set_sql_trace(true);
31 END IF;
32 ****/
33
34 l_found := csi_t_txn_details_pvt.check_txn_details_exist(
35 p_txn_line_rec => p_txn_line_rec);
36
37 /**** Commented for Bug 3304439
38 -- Check for the profile option and disable the trace
39 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
40 dbms_session.set_sql_trace(false);
41 END IF;
42 ****/
43
44 csi_t_gen_utility_pvt.set_debug_off;
45 return l_found;
46
47 EXCEPTION
48 WHEN FND_API.G_EXC_ERROR THEN
49
50 csi_t_gen_utility_pvt.set_debug_off;
51 RETURN false;
52
53 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
54
55 csi_t_gen_utility_pvt.set_debug_off;
56 RETURN false;
57
58 WHEN OTHERS THEN
59
60 csi_t_gen_utility_pvt.set_debug_off;
61 RETURN false;
62
63 END check_txn_details_exist;
64
65
66 /*
67 This procedure gets the transaction details for the given transaction lines.
68 */
69 PROCEDURE get_transaction_details(
70 p_api_version IN NUMBER
71 ,p_commit IN VARCHAR2 := fnd_api.g_false
72 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
73 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
74 ,p_txn_line_query_rec IN csi_t_datastructures_grp.txn_line_query_rec
75 ,p_txn_line_detail_query_rec IN csi_t_datastructures_grp.txn_line_detail_query_rec
76 ,x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
77 ,p_get_parties_flag IN VARCHAR2 := fnd_api.g_false
78 ,x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
79 ,p_get_pty_accts_flag IN VARCHAR2 := fnd_api.g_false
80 ,x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
81 ,p_get_ii_rltns_flag IN VARCHAR2 := fnd_api.g_false
82 ,x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
83 ,p_get_org_assgns_flag IN VARCHAR2 := fnd_api.g_false
84 ,x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
85 ,p_get_ext_attrib_vals_flag IN VARCHAR2 := fnd_api.g_false
86 ,x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
87 ,p_get_csi_attribs_flag IN VARCHAR2 := fnd_api.g_false
88 ,x_csi_ext_attribs_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attribs_tbl
89 ,p_get_csi_iea_values_flag IN VARCHAR2 := fnd_api.g_false
90 ,x_csi_iea_values_tbl OUT NOCOPY csi_t_datastructures_grp.csi_ext_attrib_vals_tbl
91 ,p_get_txn_systems_flag IN VARCHAR2 := fnd_api.g_false
92 ,x_txn_systems_tbl OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl
93 ,x_return_status OUT NOCOPY VARCHAR2
94 ,x_msg_count OUT NOCOPY NUMBER
95 ,x_msg_data OUT NOCOPY VARCHAR2)
96 IS
97
98 l_api_name CONSTANT VARCHAR2(30) := 'get_transaction_dtls';
99 l_api_version CONSTANT NUMBER := 1.0;
100 l_debug_level NUMBER;
101
102 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(512);
105
106 ---Added (Start) for m-to-m enhancements
107 l_get_ii_rltns_flag VARCHAR2(10);
108 l_indx PLS_INTEGER ;
109 i PLS_INTEGER ;
110 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec ;
111 l_loop_cnt NUMBER ;
112 x_tmp_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl ;
113 x_tmp_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl ;
114 x_tmp_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl ;
115 x_tmp_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl ;
116 x_tmp_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl ;
117 x_tmp_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl ;
118 x_tmp_ext_attribs_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl ;
119 x_tmp_iea_values_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl ;
120 x_tmp_systems_tbl csi_t_datastructures_grp.txn_systems_tbl ;
121 l_relation_exists BOOLEAN ;
122
123 CURSOR txn_line_cur (c_header_id IN NUMBER, c_line_id IN NUMBER)
124 IS
125 SELECT a.transaction_line_id,
126 a.source_transaction_id
127 FROM csi_t_transaction_lines a
128 WHERE a.source_txn_header_id = c_header_id
129 AND a.transaction_line_id = DECODE(c_line_id,fnd_api.g_miss_num, a.transaction_line_id,NULL, a.transaction_line_id, c_line_id);
130
131 ---Added (End) for m-to-m enhancements
132
133 BEGIN
134
135 -- Standard Start of API savepoint
136 SAVEPOINT get_transaction_dtls;
137
138 csi_t_gen_utility_pvt.add('API Being Executed : Get Transaction Details');
139 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
140
141 -- Initialize message list if p_init_msg_list is set to TRUE.
142 IF FND_API.to_Boolean( p_init_msg_list ) THEN
143 FND_MSG_PUB.initialize;
144 END IF;
145
146 -- Initialize API return status to success
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148
149 -- Standard call to check for call compatibility.
150 IF NOT
151
152 FND_API.Compatible_API_Call (
153 p_current_version_number => l_api_version,
154 p_caller_version_number => p_api_version,
155 p_api_name => l_api_name,
156 p_pkg_name => G_PKG_NAME) THEN
157
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159
160 END IF;
161
162 /**** Commented for Bug 3304439
163 -- Check for the profile option and enable trace
164 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
165 dbms_session.set_sql_trace(TRUE);
166 END IF;
167 ****/
168
169 -- Main API code
170 --
171 -- This procedure check if the installed base is active, If not active
172 -- populates the error message in the message queue and raises the
173 -- fnd_api.g_exc_error exception
174
175 csi_utility_grp.check_ib_active;
176
177 -- Added for CZ Integration (Begin)
178 l_txn_line_query_rec := p_txn_line_query_rec ;
179 IF NVL(l_txn_line_query_rec.config_session_hdr_id , fnd_api.g_miss_num)
180 <> fnd_api.g_miss_num
181 THEN
182 csi_t_gen_utility_pvt.add ('getting txn line id for session keys');
183 ---Get the line id associated with the given Config attribs.
184 csi_t_vldn_routines_pvt.get_cz_txn_line_id(
185 p_config_session_hdr_id => p_txn_line_query_rec.config_session_hdr_id,
186 p_config_session_rev_num => p_txn_line_query_rec.config_session_rev_num,
187 p_config_session_item_id => p_txn_line_query_rec.config_session_item_id ,
188 x_txn_line_id => l_txn_line_query_rec.transaction_line_id,
189 x_return_status => x_return_status);
190
191 IF x_return_status <> fnd_api.g_ret_sts_success
192 THEN
193 RAISE FND_API.g_exc_error;
194 END IF ;
195
196 END IF ;
197 -- Added for CZ Integration (End)
198
199 ---Added (Start) for m-to-m enhancements
200 l_loop_cnt := 0;
201 IF l_txn_line_query_rec.source_txn_header_id IS NOT NULL
202 AND l_txn_line_query_rec.source_txn_header_id <> fnd_api.g_miss_num
203 THEN
204 --IF l_txn_line_query_rec.source_transaction_type_id = 51
205 --THEN
206 FOR txn_line_rec IN txn_line_cur (l_txn_line_query_rec.source_txn_header_id, l_txn_line_query_rec.transaction_line_id)
207 LOOP
208 ---This is to make sure that we get relationships for the header
209 ---only once
210
211 l_loop_cnt := l_loop_cnt+1 ;
212
213 IF l_txn_line_query_rec.transaction_line_id IS NULL
214 OR l_txn_line_query_rec.transaction_line_id=fnd_api.g_miss_num
215 THEN
216 l_txn_line_query_rec.transaction_line_id := txn_line_rec.transaction_line_id ;
217 END IF ;
218
219 csi_t_txn_details_pvt.get_transaction_details(
220 p_api_version => p_api_version,
221 p_commit => p_commit,
222 p_init_msg_list => p_init_msg_list,
223 p_validation_level => p_validation_level,
224 p_txn_line_query_rec => l_txn_line_query_rec,
225 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
226 x_txn_line_detail_tbl => x_tmp_line_detail_tbl,
227 p_get_parties_flag => p_get_parties_flag,
228 x_txn_party_detail_tbl => x_tmp_party_detail_tbl,
229 p_get_pty_accts_flag => p_get_pty_accts_flag,
230 x_txn_pty_acct_detail_tbl => x_tmp_pty_acct_detail_tbl,
231 p_get_ii_rltns_flag => l_get_ii_rltns_flag,
232 x_txn_ii_rltns_tbl => x_tmp_ii_rltns_tbl,
233 p_get_org_assgns_flag => p_get_org_assgns_flag,
234 x_txn_org_assgn_tbl => x_tmp_org_assgn_tbl,
235 p_get_ext_attrib_vals_flag => p_get_ext_attrib_vals_flag,
236 x_txn_ext_attrib_vals_tbl => x_tmp_ext_attrib_vals_tbl,
237 p_get_csi_attribs_flag => p_get_csi_attribs_flag,
238 x_csi_ext_attribs_tbl => x_tmp_ext_attribs_tbl,
239 p_get_csi_iea_values_flag => p_get_csi_iea_values_flag,
240 x_csi_iea_values_tbl => x_tmp_iea_values_tbl,
241 p_get_txn_systems_flag => p_get_txn_systems_flag,
242 x_txn_systems_tbl => x_tmp_systems_tbl,
243 x_return_status => l_return_status,
244 x_msg_count => l_msg_count,
245 x_msg_data => l_msg_data);
246
247 IF l_return_status <> fnd_api.g_ret_sts_success THEN
248 RAISE fnd_api.g_exc_error;
249 END IF;
250
251 IF x_tmp_line_detail_tbl.COUNT > 0
252 THEN
253 l_indx := NVL(x_txn_line_detail_tbl.LAST,0)+1 ;
254 FOR i IN x_tmp_line_detail_tbl.FIRST .. x_tmp_line_detail_tbl.LAST
255 LOOP
256 x_txn_line_detail_tbl(l_indx) := x_tmp_line_detail_tbl(i) ;
257 l_indx := l_indx + 1 ;
258 END LOOP ; ---x_tmp_line_detail_tbl.FIRST
259
260 END IF ; ---x_tmp_line_detail_tbl.COUNT > 0
261
262 IF x_tmp_party_detail_tbl.COUNT > 0
263 THEN
264 l_indx := NVL(x_txn_party_detail_tbl.LAST,0)+1 ;
265 FOR i IN x_tmp_party_detail_tbl.FIRST .. x_tmp_party_detail_tbl.LAST
266 LOOP
267 x_txn_party_detail_tbl(l_indx) := x_tmp_party_detail_tbl(i) ;
268 l_indx := l_indx + 1 ;
269 END LOOP ; ---x_tmp_party_detail_tbl.FIRST
270
271 END IF ; ---x_tmp_party_detail_tbl.COUNT > 0
272
273 IF x_tmp_pty_acct_detail_tbl.COUNT > 0
274 THEN
275 l_indx := NVL(x_txn_pty_acct_detail_tbl.LAST,0)+1 ;
276 FOR i IN x_tmp_pty_acct_detail_tbl.FIRST .. x_tmp_pty_acct_detail_tbl.LAST
277 LOOP
278 x_txn_pty_acct_detail_tbl(l_indx) := x_tmp_pty_acct_detail_tbl(i) ;
279 l_indx := l_indx + 1 ;
280 END LOOP ; ---x_tmp_pty_acct_detail_tbl.FIRST
281
282 END IF ; ---x_tmp_pty_acct_detail_tbl.COUNT > 0
283
284 IF x_tmp_ii_rltns_tbl.COUNT > 0
285 THEN
286 FOR i IN x_tmp_ii_rltns_tbl.FIRST .. x_tmp_ii_rltns_tbl.LAST
287 LOOP
288 l_relation_exists := FALSE ;
289 -- bug 2795136 added the if condition
290 IF x_txn_ii_rltns_tbl.COUNT > 0 THEN
291 FOR j IN x_txn_ii_rltns_tbl.FIRST .. x_txn_ii_rltns_tbl.LAST
292 LOOP
293 IF x_txn_ii_rltns_tbl(j).txn_relationship_id
294 = x_tmp_ii_rltns_tbl(i).txn_relationship_id
295 THEN
296 --relationship already exists
297 l_relation_exists := TRUE ;
298 EXIT ;
299 END IF ;
300 END LOOP ; --x_txn_ii_rltns_tbl.FIRST
301 END IF;
302 IF NOT l_relation_exists
303 THEN
304 l_indx := NVL(x_txn_ii_rltns_tbl.LAST,0)+1 ;
305 x_txn_ii_rltns_tbl(l_indx) := x_tmp_ii_rltns_tbl(i) ;
306 END IF ;
307 END LOOP ; ---x_tmp_ii_rltns_tbl.FIRST
308
309 END IF ; ---x_tmp_ii_rltns_tbl.COUNT > 0
310
311 IF x_tmp_org_assgn_tbl.COUNT > 0
312 THEN
313 l_indx := NVL(x_txn_org_assgn_tbl.LAST,0)+1 ;
314 FOR i IN x_tmp_org_assgn_tbl.FIRST .. x_tmp_org_assgn_tbl.LAST
315 LOOP
316 x_txn_org_assgn_tbl(l_indx) := x_tmp_org_assgn_tbl(i) ;
317 l_indx := l_indx + 1 ;
318 END LOOP ; ---x_tmp_org_assgn_tbl.FIRST
319
320 END IF ; ---x_tmp_org_assgn_tbl.COUNT > 0
321
322 IF x_tmp_ext_attrib_vals_tbl.COUNT > 0
323 THEN
324 l_indx := NVL(x_txn_ext_attrib_vals_tbl.LAST,0)+1 ;
325 FOR i IN x_tmp_ext_attrib_vals_tbl.FIRST .. x_tmp_ext_attrib_vals_tbl.LAST
326 LOOP
327 x_txn_ext_attrib_vals_tbl(l_indx) := x_tmp_ext_attrib_vals_tbl(i) ;
328 l_indx := l_indx + 1 ;
329 END LOOP ; ---x_tmp_ext_attrib_vals_tbl.FIRST
330
331 END IF ; ---x_tmp_ext_attrib_vals_tbl.COUNT > 0
332
333 IF x_tmp_ext_attribs_tbl.COUNT > 0
334 THEN
335 l_indx := NVL(x_csi_ext_attribs_tbl.LAST,0)+1 ;
336 FOR i IN x_tmp_ext_attribs_tbl.FIRST .. x_tmp_ext_attribs_tbl.LAST
337 LOOP
338 x_csi_ext_attribs_tbl(l_indx) := x_tmp_ext_attribs_tbl(i) ;
339 l_indx := l_indx + 1 ;
340 END LOOP ; ---x_tmp_ext_attribs_tbl.FIRST
341
342 END IF ; ---x_tmp_ext_attribs_tbl.COUNT > 0
343
344 IF x_tmp_iea_values_tbl.COUNT > 0
345 THEN
346 l_indx := NVL(x_csi_iea_values_tbl.LAST,0)+1 ;
347 FOR i IN x_tmp_iea_values_tbl.FIRST .. x_tmp_iea_values_tbl.LAST
348 LOOP
349 x_csi_iea_values_tbl(l_indx) := x_tmp_iea_values_tbl(i) ;
350 l_indx := l_indx + 1 ;
351 END LOOP ; ---x_tmp_iea_values_tbl.FIRST
352
353 END IF ; ---x_tmp_iea_values_tbl.COUNT > 0
354
355 IF x_tmp_systems_tbl.COUNT > 0
356 THEN
357 l_indx := NVL(x_txn_systems_tbl.LAST,0)+1 ;
358 FOR i IN x_tmp_systems_tbl.FIRST .. x_tmp_systems_tbl.LAST
359 LOOP
360 x_txn_systems_tbl(l_indx) := x_tmp_systems_tbl(i) ;
361 l_indx := l_indx + 1 ;
362 END LOOP ; ---x_tmp_systems_tbl.FIRST
363
364 END IF ; ---x_tmp_systems_tbl.COUNT > 0
365
366 END LOOP ; ---txn_line_cur
367
368 -- Standard check of p_commit.
369 IF FND_API.To_Boolean( p_commit ) THEN
370 COMMIT WORK;
371 END IF;
372
373 /**** Commented for Bug 3304439
374 -- Check for the profile option and disable the trace
375 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y')
376 THEN
377 dbms_session.set_sql_trace(false);
378 END IF;
379 ****/
380
381 -- Standard call to get message count and if count is get message info.
382 FND_MSG_PUB.Count_And_Get(
383 p_count => x_msg_count,
384 p_data => x_msg_data);
385
386 --END IF ; ---p_txn_line_query_rec.source_transaction_type_id=51
387 ELSE
388 ---Added (End) for m-to-m enhancements
389
390 csi_t_txn_details_pvt.get_transaction_details(
391 p_api_version => p_api_version,
392 p_commit => p_commit,
393 p_init_msg_list => p_init_msg_list,
394 p_validation_level => p_validation_level,
395 p_txn_line_query_rec => l_txn_line_query_rec,
396 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
397 x_txn_line_detail_tbl => x_txn_line_detail_tbl,
398 p_get_parties_flag => p_get_parties_flag,
399 x_txn_party_detail_tbl => x_txn_party_detail_tbl,
400 p_get_pty_accts_flag => p_get_pty_accts_flag,
401 x_txn_pty_acct_detail_tbl => x_txn_pty_acct_detail_tbl,
402 p_get_ii_rltns_flag => p_get_ii_rltns_flag,
403 x_txn_ii_rltns_tbl => x_txn_ii_rltns_tbl,
404 p_get_org_assgns_flag => p_get_org_assgns_flag,
405 x_txn_org_assgn_tbl => x_txn_org_assgn_tbl,
406 p_get_ext_attrib_vals_flag => p_get_ext_attrib_vals_flag,
407 x_txn_ext_attrib_vals_tbl => x_txn_ext_attrib_vals_tbl,
408 p_get_csi_attribs_flag => p_get_csi_attribs_flag,
409 x_csi_ext_attribs_tbl => x_csi_ext_attribs_tbl,
410 p_get_csi_iea_values_flag => p_get_csi_iea_values_flag,
411 x_csi_iea_values_tbl => x_csi_iea_values_tbl,
412 p_get_txn_systems_flag => p_get_txn_systems_flag,
413 x_txn_systems_tbl => x_txn_systems_tbl,
414 x_return_status => l_return_status,
415 x_msg_count => l_msg_count,
416 x_msg_data => l_msg_data);
417
418 IF l_return_status <> fnd_api.g_ret_sts_success THEN
419 RAISE fnd_api.g_exc_error;
420 END IF;
421
422 -- Standard check of p_commit.
423 IF FND_API.To_Boolean( p_commit ) THEN
424 COMMIT WORK;
425 END IF;
426
427 /**** Commented for Bug 3304439
428 -- Check for the profile option and disable the trace
429 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
430 dbms_session.set_sql_trace(false);
431 END IF;
432 ****/
433
434 -- Standard call to get message count and if count is get message info.
435 FND_MSG_PUB.Count_And_Get(
436 p_count => x_msg_count,
437 p_data => x_msg_data);
438 END IF ; --p_txn_line_query_rec.source_txn_header_id is NOT NULL
439
440 csi_t_gen_utility_pvt.add('API Executed : Get Transaction Details');
441 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
442
443 EXCEPTION
444 WHEN FND_API.G_EXC_ERROR THEN
445
446 ROLLBACK TO Get_Transaction_Dtls;
447 x_return_status := FND_API.G_RET_STS_ERROR ;
448 FND_MSG_PUB.Count_And_Get (
449 p_count => x_msg_count,
450 p_data => x_msg_data);
451
452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
453
454 ROLLBACK TO Get_Transaction_Dtls;
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
456
457 FND_MSG_PUB.Count_And_Get(
458 p_count => x_msg_count,
459 p_data => x_msg_data);
460
461 WHEN OTHERS THEN
462
463 ROLLBACK TO Get_Transaction_Dtls;
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
465
466 IF FND_MSG_PUB.Check_Msg_Level(
467 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
468
469 FND_MSG_PUB.Add_Exc_Msg(
470 p_pkg_name => G_PKG_NAME,
471 p_procedure_name => l_api_name);
472
473 END IF;
474
475 FND_MSG_PUB.Count_And_Get(
476 p_count => x_msg_count,
477 p_data => x_msg_data);
478
479 END get_transaction_details;
480 /*
481 This procedure creates new transaction line details, party associations,
482 configuration details, org assignments and extended attributes for
483 a transaction line
484 */
485 PROCEDURE create_transaction_dtls
486 (
487 p_api_version IN NUMBER
488 ,p_commit IN VARCHAR2 := fnd_api.g_false
489 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
490 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
491 ,px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec
492 ,px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
493 ,px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
494 ,px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
495 ,px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
496 ,px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
497 ,px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
498 ,px_txn_systems_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl
499 ,x_return_status OUT NOCOPY VARCHAR2
500 ,x_msg_count OUT NOCOPY NUMBER
501 ,x_msg_data OUT NOCOPY VARCHAR2
502 )
503 IS
504
505 l_api_name CONSTANT VARCHAR2(30) := 'create_transaction_dtls';
506 l_api_version CONSTANT NUMBER := 1.0;
507 l_debug_level NUMBER;
508
509 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
510 l_msg_count NUMBER;
511 l_msg_data VARCHAR2(512);
512
513 l_count NUMBER;
514
515 l_txn_line_id NUMBER;
516 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
517 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
518
519 BEGIN
520
521 -- Standard Start of API savepoint
522 SAVEPOINT create_transaction_dtls;
523
524 csi_t_gen_utility_pvt.add('API Being Executed : Create Transaction Details');
525 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
526
527 -- Initialize message list if p_init_msg_list is set to TRUE.
528 IF FND_API.To_Boolean( p_init_msg_list ) THEN
529 FND_MSG_PUB.Initialize;
530 END IF;
531
532 -- Initialize API return status to success
533 x_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 -- Standard call to check for call compatibility.
536 IF NOT
537
538 FND_API.Compatible_API_Call (
539 p_current_version_number => l_api_version,
540 p_caller_version_number => p_api_version,
541 p_api_name => l_api_name,
542 p_pkg_name => g_pkg_name) THEN
543
544 RAISE FND_API.G_Exc_Unexpected_Error;
545
546 END IF;
547
548 /**** Commented for Bug 3304439
549 -- Check for the profile option and enable trace
550 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
551 dbms_session.set_sql_trace(TRUE);
552 END IF;
553 ****/
554
555 -- main code starts here
556 --
557 -- This procedure check if the installed base is active, If not active
558 -- populates the error message in the message queue and raises the
559 -- fnd_api.g_exc_error exception
560 --
561
562 csi_utility_grp.check_ib_active;
563
564 -- Added for CZ Integration (Begin)
565 IF NVL(px_txn_line_rec.config_session_hdr_id , fnd_api.g_miss_num)
566 <> fnd_api.g_miss_num
567 AND px_txn_line_rec.api_caller_identity <> 'CONFIG'
568 THEN
569 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
570 FND_MESSAGE.set_token('API_CALLER',px_txn_line_rec.api_caller_identity) ;
571 FND_MSG_PUB.add;
572 RAISE FND_API.g_exc_error;
573 END IF ;
574
575 IF NVL(px_txn_line_rec.source_transaction_type_id , -1) = 401
576 AND NVL(px_txn_line_rec.config_session_hdr_id , fnd_api.g_miss_num)
577 = fnd_api.g_miss_num
578 THEN
579 FND_MESSAGE.set_name('CSI','CSI_TXN_CZ_KEYS_NOT_GIVEN');
580 FND_MSG_PUB.add;
581 RAISE FND_API.g_exc_error;
582 END IF ;
583
584 IF NVL(px_txn_line_rec.source_transaction_type_id , -1) <> 401
585 AND NVL(px_txn_line_rec.config_session_hdr_id , fnd_api.g_miss_num)
586 <> fnd_api.g_miss_num
587 THEN
588 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_SOURCE');
589 FND_MSG_PUB.add;
590 RAISE FND_API.g_exc_error;
591 END IF ;
592 -- Added for CZ Integration (End)
593
594
595 -- 07-12 Changed as a bug fix
596 -- Initilize the txn_line_detail_id with PL/SQL index
597 -- Assumption is the pl/sql indexes start with 1 and are
598 -- continuous.
599 IF px_txn_line_detail_tbl.count > 0 THEN
600 FOR i IN px_txn_line_detail_tbl.FIRST .. px_txn_line_detail_tbl.LAST
601 LOOP
602 px_txn_line_detail_tbl(i).txn_line_detail_id := i ;
603 END LOOP ;
604 END IF;
605
606 csi_t_txn_details_pvt.create_transaction_dtls(
607 p_api_version => p_api_version,
608 p_commit => fnd_api.g_false,
609 p_init_msg_list => p_init_msg_list,
610 p_validation_level => p_validation_level,
611 px_txn_line_rec => px_txn_line_rec,
612 px_txn_line_detail_tbl => px_txn_line_detail_tbl,
613 px_txn_party_detail_tbl => px_txn_party_detail_tbl,
614 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
615 px_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
616 px_txn_org_assgn_tbl => px_txn_org_assgn_tbl,
617 px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
618 px_txn_systems_tbl => px_txn_systems_tbl,
619 x_return_status => l_return_status,
620 x_msg_count => l_msg_count,
621 x_msg_data => l_msg_data);
622
623 IF l_return_status <> fnd_api.g_ret_sts_success THEN
624 RAISE fnd_api.g_exc_error;
625 END IF;
626
627 -- Standard check of p_commit.
628 IF FND_API.To_Boolean( p_commit ) THEN
629 COMMIT WORK;
630 END IF;
631
632 /**** Commented for Bug 3304439
633 -- Check for the profile option and disable the trace
634 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
635 dbms_session.set_sql_trace(false);
636 END IF;
637 ****/
638
639 -- Standard call to get message count and if count is get message info.
640 FND_MSG_PUB.Count_And_Get(
641 p_count => x_msg_count,
642 p_data => x_msg_data);
643
644 csi_t_gen_utility_pvt.add('API Executed : Create Transaction Details');
645 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
646
647 csi_t_gen_utility_pvt.set_debug_off;
648
649 EXCEPTION
650 WHEN FND_API.G_EXC_ERROR THEN
651
652 ROLLBACK TO create_transaction_dtls;
653 x_return_status := FND_API.G_RET_STS_ERROR ;
654 FND_MSG_PUB.Count_And_Get (
655 p_count => x_msg_count,
656 p_data => x_msg_data);
657
658 csi_t_gen_utility_pvt.dump_error_stack;
659 csi_t_gen_utility_pvt.set_debug_off;
660
661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
662
663 ROLLBACK TO create_transaction_dtls;
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
665
666 FND_MSG_PUB.Count_And_Get(
667 p_count => x_msg_count,
668 p_data => x_msg_data);
669
670 csi_t_gen_utility_pvt.dump_error_stack;
671 csi_t_gen_utility_pvt.set_debug_off;
672
673 WHEN OTHERS THEN
674
675 ROLLBACK TO create_transaction_dtls;
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
677
678 IF FND_MSG_PUB.Check_Msg_Level(
679 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
680
681 FND_MSG_PUB.Add_Exc_Msg(
682 p_pkg_name => G_PKG_NAME,
683 p_procedure_name => l_api_name);
684
685 END IF;
686
687 FND_MSG_PUB.Count_And_Get(
688 p_count => x_msg_count,
689 p_data => x_msg_data);
690
691 csi_t_gen_utility_pvt.dump_error_stack;
692 csi_t_gen_utility_pvt.set_debug_off;
693
694 END create_transaction_dtls;
695
696 /*
697 This procedure is used to update the transaction line details.
698 */
699 PROCEDURE update_txn_line_dtls(
700 p_api_version IN NUMBER
701 ,p_commit IN VARCHAR2 := fnd_api.g_false
702 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
703 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
704 ,p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec
705 ,p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl
706 ,px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
707 ,px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
708 ,px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
709 ,px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
710 ,px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
711 ,x_return_status OUT NOCOPY VARCHAR2
712 ,x_msg_count OUT NOCOPY NUMBER
713 ,x_msg_data OUT NOCOPY VARCHAR2
714 )
715
716 IS
717
718 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_line_dtls';
719 l_api_version CONSTANT NUMBER := 1.0;
720 l_debug_level NUMBER;
721
722 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
723 l_msg_count NUMBER;
724 l_msg_data VARCHAR2(512);
725
726 BEGIN
727
728 -- Standard Start of API savepoint
729 SAVEPOINT update_txn_line_dtls;
730
731 csi_t_gen_utility_pvt.add('API Being Executed : Update Transaction Details');
732 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
733
734 -- Initialize message list if p_init_msg_list is set to TRUE.
735 IF FND_API.to_Boolean( p_init_msg_list ) THEN
736 FND_MSG_PUB.initialize;
737 END IF;
738
739 -- Initialize API return status to success
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 -- Standard call to check for call compatibility.
743 IF NOT
744
745 FND_API.Compatible_API_Call (
746 p_current_version_number => l_api_version,
747 p_caller_version_number => p_api_version,
748 p_api_name => l_api_name,
749 p_pkg_name => G_PKG_NAME) THEN
750
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752
753 END IF;
754
755 /**** Commented for Bug 3304439
756 -- Check for the profile option and enable trace
757 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
758 dbms_session.set_sql_trace(TRUE);
759 END IF;
760 ****/
761
762 -- Main API code
763 --
764 -- This procedure check if the installed base is active, If not active
765 -- populates the error message in the message queue and raises the
766 -- fnd_api.g_exc_error exception
767 --
768
769 csi_utility_grp.check_ib_active;
770
771 -- Added for CZ Integration (Begin)
772 IF NVL(p_txn_line_rec.config_session_hdr_id , fnd_api.g_miss_num)
773 <> fnd_api.g_miss_num
774 AND p_txn_line_rec.api_caller_identity <> 'CONFIG'
775 THEN
776 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
777 FND_MESSAGE.set_token('API_CALLER',p_txn_line_rec.api_caller_identity);
778 FND_MSG_PUB.add;
779 RAISE FND_API.g_exc_error;
780 END IF ;
781 -- Added for CZ Integration (End)
782
783 csi_t_txn_line_dtls_pvt.update_txn_line_dtls(
784 p_api_version => p_api_version,
785 p_commit => fnd_api.g_false,
786 p_init_msg_list => p_init_msg_list,
787 p_validation_level => p_validation_level,
788 p_txn_line_rec => p_txn_line_rec,
789 p_txn_line_detail_tbl => p_txn_line_detail_tbl,
790 px_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
791 px_txn_party_detail_tbl => px_txn_party_detail_tbl,
792 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
793 px_txn_org_assgn_tbl => px_txn_org_assgn_tbl,
794 px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
795 x_return_status => l_return_status,
796 x_msg_count => l_msg_count,
797 x_msg_data => l_msg_data);
798
799 IF l_return_status <> fnd_api.g_ret_sts_success THEN
800 RAISE fnd_api.g_exc_error;
801 END IF;
802
803 -- Standard check of p_commit.
804 IF FND_API.To_Boolean( p_commit ) THEN
805 COMMIT WORK;
806 END IF;
807
808 /**** Commented for Bug 3304439
809 -- Check for the profile option and disable the trace
810 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
811 dbms_session.set_sql_trace(false);
812 END IF;
813 ****/
814
815 -- Standard call to get message count and if count is get message info.
816 FND_MSG_PUB.Count_And_Get(
817 p_count => x_msg_count,
818 p_data => x_msg_data);
819
820 csi_t_gen_utility_pvt.dump_error_stack;
821
822 csi_t_gen_utility_pvt.add('API Executed : Update Transaction Details');
823 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
824
825 csi_t_gen_utility_pvt.set_debug_off;
826
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR THEN
829
830 ROLLBACK TO Update_Txn_Line_Dtls;
831 x_return_status := FND_API.G_RET_STS_ERROR ;
832 FND_MSG_PUB.Count_And_Get (
833 p_count => x_msg_count,
834 p_data => x_msg_data);
835
836 csi_t_gen_utility_pvt.dump_error_stack;
837 csi_t_gen_utility_pvt.set_debug_off;
838
839 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
840
841 ROLLBACK TO Update_Txn_Line_Dtls;
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
843
844 FND_MSG_PUB.Count_And_Get(
845 p_count => x_msg_count,
846 p_data => x_msg_data);
847
848 csi_t_gen_utility_pvt.dump_error_stack;
849 csi_t_gen_utility_pvt.set_debug_off;
850
851 WHEN OTHERS THEN
852
853 ROLLBACK TO Update_Txn_Line_Dtls;
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
855
856 IF FND_MSG_PUB.Check_Msg_Level(
857 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
858
859 FND_MSG_PUB.Add_Exc_Msg(
860 p_pkg_name => G_PKG_NAME,
861 p_procedure_name => l_api_name);
862
863 END IF;
864
865 FND_MSG_PUB.Count_And_Get(
866 p_count => x_msg_count,
867 p_data => x_msg_data);
868
869 csi_t_gen_utility_pvt.dump_error_stack;
870 csi_t_gen_utility_pvt.set_debug_off;
871
872 END update_txn_line_dtls;
873
874 /*
875 */
876 PROCEDURE delete_transaction_dtls
877 (
878 p_api_version IN NUMBER
879 ,p_commit IN VARCHAR2 := fnd_api.g_false
880 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
881 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
882 ,p_transaction_line_id IN NUMBER
883 ,p_api_caller_identity IN VARCHAR2
884 ,x_return_status OUT NOCOPY VARCHAR2
885 ,x_msg_count OUT NOCOPY NUMBER
886 ,x_msg_data OUT NOCOPY VARCHAR2
887 )
888 IS
889
890 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Transaction_Dtls';
891 l_api_version CONSTANT NUMBER := 1.0;
892 l_debug_level NUMBER;
893
894 l_return_status VARCHAR2(1);
895 l_msg_count NUMBER;
896 l_msg_data VARCHAR2(2000);
897 l_config_session_hdr_id NUMBER ;
898 l_validation_level NUMBER := NULL;
899
900 CURSOR txn_line_cur
901 IS
902 SELECT config_session_hdr_id
903 FROM csi_t_transaction_lines
904 WHERE transaction_line_id = p_transaction_line_id ;
905
906 BEGIN
907
908 -- Standard Start of API savepoint
909 SAVEPOINT Delete_Transaction_Dtls;
910
911
912 csi_t_gen_utility_pvt.add('API Being Executed : Delete Transaction Details');
913 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
914
915 -- Initialize message list if p_init_msg_list is set to TRUE.
916 IF FND_API.to_Boolean( p_init_msg_list ) THEN
917 FND_MSG_PUB.initialize;
918 END IF;
919
920 -- Initialize API return status to success
921 x_return_status := FND_API.G_RET_STS_SUCCESS;
922
923 -- Standard call to check for call compatibility.
924 IF NOT
925
926 FND_API.Compatible_API_Call (
927 p_current_version_number => l_api_version,
928 p_caller_version_number => p_api_version,
929 p_api_name => l_api_name,
930 p_pkg_name => G_PKG_NAME) THEN
931
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933
934 END IF;
935
936 /**** Commented for Bug 3304439
937 -- Check for the profile option and enable trace
938 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
939 dbms_session.set_sql_trace(TRUE);
940 END IF;
941 ****/
942
943 -- Main API code
944 --
945 -- This procedure check if the installed base is active, If not active
946 -- populates the error message in the message queue and raises the
947 -- fnd_api.g_exc_error exception
948 --
949 csi_utility_grp.check_ib_active;
950 IF p_api_caller_identity <> 'CONFIG'
951 THEN
952 ---check whether txn_line_id has config parameters set
953 OPEN txn_line_cur ;
954 FETCH txn_line_cur INTO l_config_session_hdr_id;
955 CLOSE txn_line_cur ;
956 IF l_config_session_hdr_id IS NOT NULL
957 THEN
958 FND_MESSAGE.set_name('CSI','CSI_TXN_NOT_CZ_CALLER');
959 FND_MESSAGE.set_token('API_CALLER',p_api_caller_identity);
960 FND_MSG_PUB.add;
961 RAISE FND_API.g_exc_error;
962 END IF ;
963 END IF ;
964
965 -- Needed to pass some parameter to identify that this is being called from the PURGE program
966 IF p_api_caller_identity = 'PURGE' THEN
967 l_validation_level := 999;
968 ELSE
969 l_validation_level := NULL;
970 END IF;
971
972 csi_t_txn_details_pvt.delete_transaction_dtls(
973 p_api_version => p_api_version,
974 p_commit => p_commit,
975 p_init_msg_list => p_init_msg_list,
976 p_validation_level => l_validation_level,
977 p_transaction_line_id => p_transaction_line_id,
978 p_txn_line_detail_id => null,
979 x_return_status => l_return_status,
980 x_msg_count => l_msg_count,
981 x_msg_data => l_msg_data);
982
983 IF l_return_status <> fnd_api.g_ret_sts_success THEN
984 RAISE fnd_api.g_exc_error;
985 END IF;
986
987 -- Standard check of p_commit.
988 IF FND_API.To_Boolean( p_commit ) THEN
989 COMMIT WORK;
990 END IF;
991
992 /**** Commented for Bug 3304439
993 -- Check for the profile option and disable the trace
994 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
995 dbms_session.set_sql_trace(false);
996 END IF;
997 ****/
998
999
1000 -- Standard call to get message count and if count is get message info.
1001 FND_MSG_PUB.Count_And_Get(
1002 p_count => x_msg_count,
1003 p_data => x_msg_data);
1004
1005
1006 csi_t_gen_utility_pvt.add('API Executed : Delete Transaction Details');
1007 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1008
1009 csi_t_gen_utility_pvt.set_debug_off;
1010
1011 EXCEPTION
1012 WHEN FND_API.G_EXC_ERROR THEN
1013
1014 ROLLBACK TO Delete_Transaction_Dtls;
1015 x_return_status := FND_API.G_RET_STS_ERROR ;
1016 FND_MSG_PUB.Count_And_Get (
1017 p_count => x_msg_count,
1018 p_data => x_msg_data);
1019
1020 csi_t_gen_utility_pvt.set_debug_off;
1021
1022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1023
1024 ROLLBACK TO Delete_Transaction_Dtls;
1025 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1026
1027 FND_MSG_PUB.Count_And_Get(
1028 p_count => x_msg_count,
1029 p_data => x_msg_data);
1030
1031 csi_t_gen_utility_pvt.set_debug_off;
1032
1033 WHEN OTHERS THEN
1034
1035 ROLLBACK TO Delete_Transaction_Dtls;
1036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1037
1038 IF FND_MSG_PUB.Check_Msg_Level(
1039 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1040
1041 FND_MSG_PUB.Add_Exc_Msg(
1042 p_pkg_name => G_PKG_NAME,
1043 p_procedure_name => l_api_name);
1044
1045 END IF;
1046
1047 FND_MSG_PUB.Count_And_Get(
1048 p_count => x_msg_count,
1049 p_data => x_msg_data);
1050
1051 csi_t_gen_utility_pvt.set_debug_off;
1052
1053 END delete_transaction_dtls;
1054
1055
1056 PROCEDURE copy_transaction_dtls(
1057 p_api_version IN NUMBER,
1058 p_commit IN VARCHAR2 := fnd_api.g_false,
1059 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1060 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1061 p_src_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
1062 px_new_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
1063 p_copy_parties_flag IN varchar2 := fnd_api.g_true,
1064 p_copy_pty_accts_flag IN varchar2 := fnd_api.g_true,
1065 p_copy_ii_rltns_flag IN varchar2 := fnd_api.g_true,
1066 p_copy_org_assgn_flag IN varchar2 := fnd_api.g_true,
1067 p_copy_ext_attribs_flag IN varchar2 := fnd_api.g_true,
1068 p_copy_txn_systems_flag IN varchar2 := fnd_api.g_true,
1069 x_return_status OUT NOCOPY VARCHAR2,
1070 x_msg_count OUT NOCOPY NUMBER,
1071 x_msg_data OUT NOCOPY VARCHAR2)
1072 IS
1073
1074 l_api_name CONSTANT VARCHAR2(30) := 'copy_transaction_dtls';
1075 l_api_version CONSTANT NUMBER := 1.0;
1076 l_debug_level NUMBER;
1077
1078 l_return_status VARCHAR2(1);
1079 l_msg_count NUMBER;
1080 l_msg_data VARCHAR2(2000);
1081
1082 BEGIN
1083
1084 -- Standard Start of API savepoint
1085 SAVEPOINT copy_transaction_dtls;
1086
1087 csi_t_gen_utility_pvt.add('API Being Executed : Copy Transaction Details');
1088 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1089
1090 -- Initialize message list if p_init_msg_list is set to TRUE.
1091 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1092 FND_MSG_PUB.initialize;
1093 END IF;
1094
1095 -- Initialize API return status to success
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 -- Standard call to check for call compatibility.
1099 IF NOT
1100
1101 FND_API.Compatible_API_Call (
1102 p_current_version_number => l_api_version,
1103 p_caller_version_number => p_api_version,
1104 p_api_name => l_api_name,
1105 p_pkg_name => G_PKG_NAME) THEN
1106
1107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1108
1109 END IF;
1110
1111 /**** Commented for Bug 3304439
1112 -- Check for the profile option and enable trace
1113 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
1114 dbms_session.set_sql_trace(TRUE);
1115 END IF;
1116 ****/
1117
1118 -- Main API code
1119 --
1120 -- This procedure check if the installed base is active, If not active
1121 -- populates the error message in the message queue and raises the
1122 -- fnd_api.g_exc_error exception
1123 --
1124
1125 csi_utility_grp.check_ib_active;
1126
1127 csi_t_txn_details_pvt.copy_transaction_dtls(
1128 p_api_version => p_api_version,
1129 p_commit => p_commit,
1130 p_init_msg_list => p_init_msg_list,
1131 p_validation_level => p_validation_level,
1132 p_src_txn_line_rec => p_src_txn_line_rec,
1133 px_new_txn_line_rec => px_new_txn_line_rec,
1134 p_copy_parties_flag => p_copy_parties_flag,
1135 p_copy_pty_accts_flag => p_copy_pty_accts_flag,
1136 p_copy_ii_rltns_flag => p_copy_ii_rltns_flag,
1137 p_copy_org_assgn_flag => p_copy_org_assgn_flag,
1138 p_copy_ext_attribs_flag => p_copy_ext_attribs_flag,
1139 p_copy_txn_systems_flag => p_copy_txn_systems_flag,
1140 x_return_status => l_return_status,
1141 x_msg_count => l_msg_count,
1142 x_msg_data => l_msg_data);
1143
1144 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1145 RAISE fnd_api.g_exc_error;
1146 END IF;
1147
1148 -- Standard check of p_commit.
1149 IF FND_API.To_Boolean( p_commit ) THEN
1150 COMMIT WORK;
1151 END IF;
1152
1153 /**** Commented for Bug 3304439
1154 -- Check for the profile option and disable the trace
1155 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
1156 dbms_session.set_sql_trace(false);
1157 END IF;
1158 ****/
1159
1160 -- Standard call to get message count and if count is get message info.
1161 FND_MSG_PUB.Count_And_Get(
1162 p_count => x_msg_count,
1163 p_data => x_msg_data);
1164
1165
1166 csi_t_gen_utility_pvt.add('API Executed : Copy Transaction Details');
1167 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1168
1169 csi_t_gen_utility_pvt.set_debug_off;
1170
1171 EXCEPTION
1172 WHEN FND_API.G_EXC_ERROR THEN
1173
1174 ROLLBACK TO copy_Transaction_Dtls;
1175 x_return_status := FND_API.G_RET_STS_ERROR ;
1176 FND_MSG_PUB.Count_And_Get (
1177 p_count => x_msg_count,
1178 p_data => x_msg_data);
1179
1180 csi_t_gen_utility_pvt.set_debug_off;
1181
1182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1183
1184 ROLLBACK TO copy_Transaction_Dtls;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1186
1187 FND_MSG_PUB.Count_And_Get(
1188 p_count => x_msg_count,
1189 p_data => x_msg_data);
1190
1191 csi_t_gen_utility_pvt.set_debug_off;
1192
1193 WHEN OTHERS THEN
1194
1195 ROLLBACK TO copy_Transaction_Dtls;
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1197
1198 IF FND_MSG_PUB.Check_Msg_Level(
1199 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1200
1201 FND_MSG_PUB.Add_Exc_Msg(
1202 p_pkg_name => G_PKG_NAME,
1203 p_procedure_name => l_api_name);
1204
1205 END IF;
1206
1207 FND_MSG_PUB.Count_And_Get(
1208 p_count => x_msg_count,
1209 p_data => x_msg_data);
1210
1211 csi_t_gen_utility_pvt.set_debug_off;
1212
1213 END copy_transaction_dtls;
1214
1215
1216 /*
1217 This API is for managing the transaction details entities incl. the line details.
1218 */
1219 PROCEDURE update_transaction_dtls(
1220 p_api_version IN NUMBER
1221 ,p_commit IN VARCHAR2 := fnd_api.g_false
1222 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1223 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1224 ,p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec
1225 ,px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
1226 ,px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
1227 ,px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
1228 ,px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
1229 ,px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
1230 ,px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
1231 ,x_return_status OUT NOCOPY VARCHAR2
1232 ,x_msg_count OUT NOCOPY NUMBER
1233 ,x_msg_data OUT NOCOPY VARCHAR2
1234 )
1235 IS
1236
1237 l_api_name CONSTANT VARCHAR2(30) := 'update_transaction_dtls';
1238 l_api_version CONSTANT NUMBER := 1.0;
1239 l_debug_level NUMBER;
1240
1241 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
1242 l_msg_count NUMBER;
1243 l_msg_data VARCHAR2(512);
1244
1245 BEGIN
1246
1247 -- Standard Start of API savepoint
1248 SAVEPOINT update_transaction_dtls;
1249
1250 csi_t_gen_utility_pvt.add('API Being Executed : Update Transaction Details');
1251 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1252
1253 -- Initialize message list if p_init_msg_list is set to TRUE.
1254 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1255 FND_MSG_PUB.initialize;
1256 END IF;
1257
1258 -- Initialize API return status to success
1259 x_return_status := FND_API.G_RET_STS_SUCCESS;
1260
1261 -- Standard call to check for call compatibility.
1262 IF NOT
1263
1264 FND_API.Compatible_API_Call (
1265 p_current_version_number => l_api_version,
1266 p_caller_version_number => p_api_version,
1267 p_api_name => l_api_name,
1268 p_pkg_name => G_PKG_NAME) THEN
1269
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271
1272 END IF;
1273
1274 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1275
1276 csi_t_gen_utility_pvt.dump_api_info(
1277 p_pkg_name => g_pkg_name,
1278 p_api_name => l_api_name);
1279
1280 IF l_debug_level > 1 THEN
1281 csi_t_gen_utility_pvt.dump_txn_line_rec(
1282 p_txn_line_rec => p_txn_line_rec);
1283 END IF;
1284
1285 -- Main API code
1286 --
1287 -- This procedure check if the installed base is active, If not active
1288 -- populates the error message in the message queue and raises the
1289 -- fnd_api.g_exc_error exception
1290 --
1291
1292 csi_utility_grp.check_ib_active;
1293
1294 csi_t_vldn_routines_pvt.check_reqd_param(
1295 p_value => p_txn_line_rec.transaction_line_id,
1296 p_param_name => 'p_txn_line_rec.transaction_line_id',
1297 p_api_name => l_api_name);
1298
1299
1300 -- Calling the Private ...
1301 csi_t_txn_details_pvt.update_transaction_dtls(
1302 p_api_version => p_api_version,
1303 p_commit => fnd_api.g_false,
1304 p_init_msg_list => p_init_msg_list,
1305 p_validation_level => p_validation_level,
1306 p_txn_line_rec => p_txn_line_rec,
1307 px_txn_line_detail_tbl => px_txn_line_detail_tbl,
1308 px_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
1309 px_txn_party_detail_tbl => px_txn_party_detail_tbl,
1310 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
1311 px_txn_org_assgn_tbl => px_txn_org_assgn_tbl,
1312 px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
1313 x_return_status => l_return_status,
1314 x_msg_count => l_msg_count,
1315 x_msg_data => l_msg_data);
1316
1317 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1318 RAISE fnd_api.g_exc_error;
1319 END IF;
1320
1321 -- Standard check of p_commit.
1322 IF FND_API.To_Boolean( p_commit ) THEN
1323 COMMIT WORK;
1324 END IF;
1325
1326 -- Standard call to get message count and if count is get message info.
1327 FND_MSG_PUB.Count_And_Get(
1328 p_count => x_msg_count,
1329 p_data => x_msg_data);
1330
1331 csi_t_gen_utility_pvt.dump_error_stack;
1332
1333 csi_t_gen_utility_pvt.add('API Executed : Update Transaction Details');
1334 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1335
1336 csi_t_gen_utility_pvt.set_debug_off;
1337
1338 EXCEPTION
1339 WHEN FND_API.G_EXC_ERROR THEN
1340
1341 ROLLBACK TO update_transaction_dtls;
1342 x_return_status := FND_API.G_RET_STS_ERROR ;
1343 FND_MSG_PUB.Count_And_Get (
1344 p_count => x_msg_count,
1345 p_data => x_msg_data);
1346
1347 csi_t_gen_utility_pvt.dump_error_stack;
1348 csi_t_gen_utility_pvt.set_debug_off;
1349
1350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1351
1352 ROLLBACK TO update_transaction_dtls;
1353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1354
1355 FND_MSG_PUB.Count_And_Get(
1356 p_count => x_msg_count,
1357 p_data => x_msg_data);
1358
1359 csi_t_gen_utility_pvt.dump_error_stack;
1360 csi_t_gen_utility_pvt.set_debug_off;
1361
1362 WHEN OTHERS THEN
1363
1364 ROLLBACK TO update_transaction_dtls;
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1366
1367 IF FND_MSG_PUB.Check_Msg_Level(
1368 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1369
1370 FND_MSG_PUB.Add_Exc_Msg(
1371 p_pkg_name => G_PKG_NAME,
1372 p_procedure_name => l_api_name);
1373
1374 END IF;
1375
1376 FND_MSG_PUB.Count_And_Get(
1377 p_count => x_msg_count,
1378 p_data => x_msg_data);
1379
1380 csi_t_gen_utility_pvt.dump_error_stack;
1381 csi_t_gen_utility_pvt.set_debug_off;
1382
1383 END update_transaction_dtls;
1384
1385 PROCEDURE split_transaction_details(
1386 p_api_version IN NUMBER,
1387 p_commit IN VARCHAR2 := fnd_api.g_false,
1388 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1389 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1390 p_src_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
1391 px_split_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
1392 px_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
1393 x_pty_dtl_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
1394 x_pty_acct_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
1395 x_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
1396 x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
1397 x_txn_systems_tbl OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_count OUT NOCOPY NUMBER,
1400 x_msg_data OUT NOCOPY VARCHAR2) IS
1401
1402 l_api_name CONSTANT VARCHAR2(30) := 'split_transaction_details';
1403 l_api_version CONSTANT NUMBER := 1.0;
1404
1405 BEGIN
1406 -- Standard Start of API savepoint
1407 SAVEPOINT split_transaction_details;
1408
1409 csi_t_gen_utility_pvt.add('API Being Executed : Split Transaction Details');
1410 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1411
1412 -- Initialize message list if p_init_msg_list is set to TRUE.
1413 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1414 FND_MSG_PUB.initialize;
1415 END IF;
1416
1417 -- Initialize API return status to success
1418 x_return_status := FND_API.G_RET_STS_SUCCESS;
1419
1420 -- Standard call to check for call compatibility.
1421 IF NOT
1422 FND_API.Compatible_API_Call (
1423 p_current_version_number => l_api_version,
1424 p_caller_version_number => p_api_version,
1425 p_api_name => l_api_name,
1426 p_pkg_name => G_PKG_NAME) THEN
1427
1428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1429
1430 END IF;
1431
1432 -- Check for the profile option and enable trace
1433 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
1434 dbms_session.set_sql_trace(TRUE);
1435 END IF;
1436
1437 -- Main API code
1438 --
1439 -- This procedure check if the installed base is active, If not active
1440 -- populates the error message in the message queue and raises the
1441 -- fnd_api.g_exc_error exception
1442 --
1443
1444 csi_utility_grp.check_ib_active;
1445
1446 csi_t_txn_details_pvt.split_transaction_details
1447 (
1448 p_api_version => p_api_version,
1449 p_commit => p_commit,
1450 p_init_msg_list => p_init_msg_list,
1451 p_validation_level => p_validation_level,
1452 p_src_txn_line_rec => p_src_txn_line_rec,
1453 px_split_txn_line_rec => px_split_txn_line_rec,
1454 px_line_dtl_tbl => px_line_dtl_tbl,
1455 x_pty_dtl_tbl => x_pty_dtl_tbl,
1456 x_pty_acct_tbl => x_pty_acct_tbl,
1457 x_org_assgn_tbl => x_org_assgn_tbl,
1458 x_txn_ext_attrib_vals_tbl => x_txn_ext_attrib_vals_tbl,
1459 x_txn_systems_tbl => x_txn_systems_tbl,
1460 x_return_status => x_return_status,
1461 x_msg_count => x_msg_count,
1462 x_msg_data => x_msg_data
1463 );
1464 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1465 RAISE fnd_api.g_exc_error;
1466 END IF;
1467
1468 IF FND_API.To_Boolean( p_commit ) THEN
1469 COMMIT WORK;
1470 END IF;
1471
1472 -- Check for the profile option and disable the trace
1473 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
1474 dbms_session.set_sql_trace(false);
1475 END IF;
1476
1477 -- Standard call to get message count and if count is get message info.
1478 FND_MSG_PUB.Count_And_Get(
1479 p_count => x_msg_count,
1480 p_data => x_msg_data);
1481
1482 csi_t_gen_utility_pvt.add('API Executed : Copy Transaction Details');
1483 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1484
1485 csi_t_gen_utility_pvt.set_debug_off;
1486
1487 EXCEPTION
1488 WHEN FND_API.G_EXC_ERROR THEN
1489
1490 ROLLBACK TO split_transaction_details;
1491 x_return_status := FND_API.G_RET_STS_ERROR ;
1492 FND_MSG_PUB.Count_And_Get (
1493 p_count => x_msg_count,
1494 p_data => x_msg_data);
1495
1496 csi_t_gen_utility_pvt.set_debug_off;
1497
1498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1499
1500 ROLLBACK TO split_transaction_details;
1501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1502
1503 FND_MSG_PUB.Count_And_Get (
1504 p_count => x_msg_count,
1505 p_data => x_msg_data);
1506
1507 csi_t_gen_utility_pvt.set_debug_off;
1508
1509 WHEN OTHERS THEN
1510
1511 ROLLBACK TO split_transaction_details;
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1513
1514 FND_MSG_PUB.Count_And_Get (
1515 p_count => x_msg_count,
1516 p_data => x_msg_data);
1517
1518 csi_t_gen_utility_pvt.set_debug_off;
1519
1520 END split_transaction_details;
1521
1522 END csi_t_txn_details_grp;