[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_PARTIES_PVT
Source
1 PACKAGE BODY csi_t_txn_parties_pvt as
2 /* $Header: csivtpab.pls 120.4 2006/01/04 17:57:30 shegde noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_parties_pvt';
5 g_file_name CONSTANT VARCHAR2(12) := 'csivtpab.pls';
6
7 g_user_id NUMBER := FND_GLOBAL.User_Id;
8 g_login_id NUMBER := FND_GLOBAL.Login_Id;
9
10 PROCEDURE debug(
11 p_message IN varchar2)
12 IS
13 BEGIN
14 csi_t_gen_utility_pvt.add(p_message);
15 END debug;
16
17 PROCEDURE api_log(
18 p_api_name IN varchar2)
19 IS
20 BEGIN
21 csi_t_gen_utility_pvt.dump_api_info(
22 p_pkg_name => 'csi_t_txn_parties_pvt',
23 p_api_name => p_api_name);
24 END api_log;
25
26 PROCEDURE create_txn_party_dtls(
27 p_api_version IN number,
28 p_commit IN varchar2 := fnd_api.g_false,
29 p_init_msg_list IN varchar2 := fnd_api.g_false,
30 p_validation_level IN number := fnd_api.g_valid_level_full,
31 p_txn_party_dtl_index IN number,
32 p_txn_party_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_rec,
33 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
34 x_return_status OUT NOCOPY varchar2,
35 x_msg_count OUT NOCOPY number,
36 x_msg_data OUT NOCOPY varchar2)
37 IS
38
39 l_api_name CONSTANT varchar2(30) := 'create_txn_party_dtls';
40 l_api_version CONSTANT number := 1.0;
41 l_debug_level number;
42 l_index number;
43 l_txn_party_detail_id number;
44 l_pty_acct_rec csi_t_datastructures_grp.txn_pty_acct_detail_rec;
45 l_preserve_detail_flag varchar2(1);
46
47 l_contact_flag varchar2(1) := 'P';
48
49 l_return_status varchar2(1);
50 l_msg_count number;
51 l_msg_data varchar2(512);
52
53 BEGIN
54
55 -- Standard Start of API savepoint
56 savepoint create_txn_party_dtls;
57
58 -- Initialize message list if p_init_msg_list is set to TRUE.
59 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
60 FND_MSG_PUB.initialize;
61 END IF;
62
63 -- Initialize API return status to success
64 x_return_status := fnd_api.G_RET_STS_SUCCESS;
65
66 -- Standard call to check for call compatibility.
67 IF NOT
68
69 fnd_api.Compatible_API_Call (
70 p_current_version_number => l_api_version,
71 p_caller_version_number => p_api_version,
72 p_api_name => l_api_name,
73 p_pkg_name => G_PKG_NAME) THEN
74
75 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
76
77 END IF;
78
79 -- debug messages
80 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
81
82 csi_t_gen_utility_pvt.dump_api_info(
83 p_pkg_name => g_pkg_name,
84 p_api_name => l_api_name);
85
86 IF l_debug_level > 1 then
87
88 csi_t_gen_utility_pvt.add(
89 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
90
91 csi_t_gen_utility_pvt.dump_party_detail_rec(
92 p_party_detail_rec => p_txn_party_detail_rec);
93
94 END IF;
95
96 -- Main API code
97
98 csi_t_vldn_routines_pvt.check_reqd_param(
99 p_value => p_txn_party_detail_rec.txn_line_detail_id,
100 p_param_name => 'p_txn_party_detail_rec.txn_line_detail_id',
101 p_api_name => l_api_name);
102
103 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
104 p_txn_line_detail_id => p_txn_party_detail_rec.txn_line_detail_id,
105 x_return_status => l_return_status);
106
107 IF l_return_status <> fnd_api.g_ret_sts_success THEN
108
109 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_DTL_ID_INVALID');
110 FND_MESSAGE.set_token('LINE_DTL_ID',
111 p_txn_party_detail_rec.txn_line_detail_id);
112 FND_MSG_PUB.add;
113 RAISE fnd_api.g_exc_error;
114
115 END IF;
116
117
118 IF NVL(p_txn_party_detail_rec.instance_party_id , fnd_api.g_miss_num) =
119 fnd_api.g_miss_num
120 THEN
121
122 csi_t_vldn_routines_pvt.check_reqd_param(
123 p_value => p_txn_party_detail_rec.party_source_table,
124 p_param_name => 'p_txn_party_detail_rec.party_source_table',
125 p_api_name => l_api_name);
126
127 csi_t_vldn_routines_pvt.check_reqd_param(
128 p_value => p_txn_party_detail_rec.party_source_id,
129 p_param_name => 'p_txn_party_detail_rec.party_source_id',
130 p_api_name => l_api_name);
131
132 END IF;
133
134
135 csi_t_vldn_routines_pvt.check_reqd_param(
136 p_value => p_txn_party_detail_rec.relationship_type_code,
137 p_param_name => 'p_txn_party_detail_rec.relationship_type_code',
138 p_api_name => l_api_name);
139
140
141 -- validate party_source_table from cs_lookups
142 IF NVL(p_txn_party_detail_rec.party_source_table, fnd_api.g_miss_char) <>
143 fnd_api.g_miss_char
144 THEN
145
146 IF NOT
147 csi_instance_parties_vld_pvt.is_pty_source_tab_valid(
148 p_party_source_table => p_txn_party_detail_rec.party_source_table)
149 THEN
150 csi_t_gen_utility_pvt.add('Validate party source table failed.');
151 RAISE fnd_api.g_exc_error;
152 END IF;
153
154 END IF;
155
156
157 -- validate party_source_id for party_source_table
158 IF NVL(p_txn_party_detail_rec.party_source_id, fnd_api.g_miss_num) <>
159 fnd_api.g_miss_num
160 THEN
161
162 IF NOT
163 csi_instance_parties_vld_pvt.is_party_valid(
164 p_party_source_table => p_txn_party_detail_rec.party_source_table,
165 p_party_id => p_txn_party_detail_rec.party_source_id,
166 p_contact_flag => p_txn_party_detail_rec.contact_flag)
167 THEN
168 csi_t_gen_utility_pvt.add('Validate party source id failed.');
169 RAISE fnd_api.g_exc_error;
170 END IF;
171
172 END IF;
173
174 -- mandate contact flag
175 csi_t_vldn_routines_pvt.check_reqd_param(
176 p_value => p_txn_party_detail_rec.contact_flag,
177 p_param_name => 'p_txn_party_detail_rec.contact_flag',
178 p_api_name => l_api_name);
179
180 -- contact_flag should be Y or N
181 IF nvl(p_txn_party_detail_rec.contact_flag,fnd_api.g_miss_char) <> fnd_api.g_miss_char
182 THEN
183 csi_t_vldn_routines_pvt.validate_contact_flag(
184 p_contact_flag => p_txn_party_detail_rec.contact_flag,
185 x_return_status => l_return_status);
186
187 IF l_return_status <> fnd_api.g_ret_sts_success THEN
188
189 FND_MESSAGE.set_name('CSI','CSI_TXN_CONTACT_FLAG_INVALID');
190 FND_MESSAGE.set_token ('CONTACT_FLAG', p_txn_party_detail_rec.contact_flag);
191
192 FND_MSG_PUB.add;
193 RAISE fnd_api.g_exc_error;
194
195 END IF;
196 END IF;
197
198 -- validate relationship_type_code from cs_lookups
199 IF NVL(p_txn_party_detail_rec.relationship_type_code, fnd_api.g_miss_char) <>
200 fnd_api.g_miss_char
201 THEN
202
203 IF p_txn_party_detail_rec.contact_flag = 'Y' THEN
204 l_contact_flag := 'C';
205 ELSE
206 l_contact_flag := 'P';
207 END IF;
208
209 IF NOT
210 csi_instance_parties_vld_pvt.is_pty_rel_type_valid(
211 p_party_rel_type_code => p_txn_party_detail_rec.relationship_type_code,
212 p_contact_flag => l_contact_flag)
213 THEN
214 csi_t_gen_utility_pvt.add('Validate party relationship type code failed.');
215 RAISE fnd_api.g_exc_error;
216 END IF;
217
218 END IF;
219
220
221 -- validate instance_party_id from csi_i_parties
222 IF NVL(p_txn_party_detail_rec.instance_party_id, fnd_api.g_miss_num) <>
223 fnd_api.g_miss_num
224 THEN
225
226 csi_t_vldn_routines_pvt.validate_instance_reference(
227 p_level => 'PARTY',
228 p_level_dtl_id => p_txn_party_detail_rec.txn_line_detail_id,
229 p_level_inst_ref_id => p_txn_party_detail_rec.instance_party_id,
230 x_return_status => l_return_status);
231
232 IF l_return_status <> fnd_api.g_ret_sts_error THEN
233 csi_t_gen_utility_pvt.add('Validate instance reference failed.');
234 RAISE fnd_api.g_exc_error;
235 END IF;
236
237 END IF;
238
239 --validate contact_party_id
240
241 -- validate owner
242
243 IF p_txn_party_detail_rec.relationship_type_code = 'OWNER' THEN
244
245 csi_t_vldn_routines_pvt.is_valid_owner_for_create(
246 p_txn_line_detail_id => p_txn_party_detail_rec.txn_line_detail_id,
247 p_instance_party_id => p_txn_party_detail_rec.instance_party_id,
248 x_return_status => l_return_status);
249
250 IF l_return_status <> fnd_api.g_ret_sts_success THEN
251
252 FND_MESSAGE.set_name('CSI','CSI_TXN_OWNER_ERROR');
253 FND_MESSAGE.set_token('PTY_SRC_NAME',
254 p_txn_party_detail_rec.party_source_table);
255 FND_MESSAGE.set_token('PTY_SRC_ID',
256 p_txn_party_detail_rec.party_source_id);
257 FND_MSG_PUB.add;
258 RAISE fnd_api.g_exc_error;
259
260 END IF;
261
262 END IF;
263
264 SELECT decode(nvl(p_txn_party_detail_rec.preserve_detail_flag,fnd_api.g_miss_char),
265 fnd_api.g_miss_char, 'Y', p_txn_party_detail_rec.preserve_detail_flag)
266 INTO l_preserve_detail_flag
267 FROM sys.dual;
268
269 -- call table handler to create row in the table
270 IF nvl(p_txn_party_detail_rec.txn_party_detail_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
271 THEN
272 l_txn_party_detail_id := p_txn_party_detail_rec.txn_party_detail_id;
273 END IF;
274
275 BEGIN
276
277 csi_t_gen_utility_pvt.dump_api_info(
278 p_api_name => 'insert_row',
279 p_pkg_name => 'csi_t_party_detailss_pkg');
280
281 csi_t_party_details_pkg.insert_row(
282 px_txn_party_detail_id => l_txn_party_detail_id,
283 p_txn_line_detail_id => p_txn_party_detail_rec.txn_line_detail_id,
284 p_party_source_table => p_txn_party_detail_rec.party_source_table,
285 p_party_source_id => p_txn_party_detail_rec.party_source_id,
286 p_relationship_type_code => p_txn_party_detail_rec.relationship_type_code,
287 p_contact_flag => p_txn_party_detail_rec.contact_flag,
288 p_active_start_date => p_txn_party_detail_rec.active_start_date,
289 p_active_end_date => p_txn_party_detail_rec.active_end_date,
290 p_preserve_detail_flag => l_preserve_detail_flag,
291 p_instance_party_id => p_txn_party_detail_rec.instance_party_id,
292 p_attribute1 => p_txn_party_detail_rec.attribute1,
293 p_attribute2 => p_txn_party_detail_rec.attribute2,
294 p_attribute3 => p_txn_party_detail_rec.attribute3,
295 p_attribute4 => p_txn_party_detail_rec.attribute4,
296 p_attribute5 => p_txn_party_detail_rec.attribute5,
297 p_attribute6 => p_txn_party_detail_rec.attribute6,
298 p_attribute7 => p_txn_party_detail_rec.attribute7,
299 p_attribute8 => p_txn_party_detail_rec.attribute8,
300 p_attribute9 => p_txn_party_detail_rec.attribute9,
301 p_attribute10 => p_txn_party_detail_rec.attribute10,
302 p_attribute11 => p_txn_party_detail_rec.attribute11,
303 p_attribute12 => p_txn_party_detail_rec.attribute12,
304 p_attribute13 => p_txn_party_detail_rec.attribute13,
305 p_attribute14 => p_txn_party_detail_rec.attribute14,
306 p_attribute15 => p_txn_party_detail_rec.attribute15,
307 p_created_by => g_user_id,
308 p_creation_date => sysdate,
309 p_last_updated_by => g_user_id,
310 p_last_update_date => sysdate,
311 p_last_update_login => g_login_id,
312 p_object_version_number => 1.0,
313 p_context => p_txn_party_detail_rec.context,
314 p_contact_party_id => null,
315 p_primary_flag => p_txn_party_detail_rec.primary_flag,
316 p_preferred_flag => p_txn_party_detail_rec.preferred_flag);
317
318 --p_txn_party_detail_rec.contact_party_id);
319
320 exception
321 when others then
322 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
323 fnd_message.set_token('MESSAGE',
324 'csi_t_party_details_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
325 fnd_msg_pub.add;
326 raise fnd_api.g_exc_error;
327 end;
328
329 p_txn_party_detail_rec.txn_party_detail_id := l_txn_party_detail_id;
330
331 if px_txn_pty_acct_detail_tbl.COUNT > 0 then
332 -- loop thru party account detail
333 FOR l_index IN px_txn_pty_acct_detail_tbl.FIRST..px_txn_pty_acct_detail_tbl.LAST
334 LOOP
335
336 if px_txn_pty_acct_detail_tbl(l_index).txn_party_details_index =
337 p_txn_party_dtl_index then
338 -- assign values to the record type variable
339
340 l_pty_acct_rec := px_txn_pty_acct_detail_tbl(l_index);
341 l_pty_acct_rec.txn_party_detail_id := l_txn_party_detail_id;
342
343 -- call api to create txn_party_account details
344 csi_t_txn_parties_pvt.create_txn_pty_acct_dtls(
345 p_api_version => p_api_version,
346 p_commit => p_commit,
347 p_init_msg_list => p_init_msg_list,
348 p_validation_level => p_validation_level,
349 p_txn_pty_acct_detail_rec => l_pty_acct_rec,
350 x_return_status => l_return_status,
351 x_msg_count => l_msg_count,
352 x_msg_data => l_msg_data);
353
354 IF l_return_status <> fnd_api.g_ret_sts_success THEN
355 csi_t_gen_utility_pvt.add('Create txn party accounts failed.');
356 RAISE fnd_api.g_exc_error;
357 END IF;
358
359 px_txn_pty_acct_detail_tbl(l_index).txn_party_detail_id :=
360 l_pty_acct_rec.txn_party_detail_id;
361 px_txn_pty_acct_detail_tbl(l_index).txn_account_detail_id :=
362 l_pty_acct_rec.txn_account_detail_id;
363
364 END IF;
365
366 END LOOP;
367
368 END IF;
369 -- Standard check of p_commit.
370 IF fnd_api.To_Boolean( p_commit ) THEN
371 COMMIT WORK;
372 END IF;
373
374 IF csi_t_gen_utility_pvt.g_debug = fnd_api.g_true THEN
375 csi_t_gen_utility_pvt.set_debug_off;
376 END IF;
377
378 -- Standard call to get message count and if count is get message info.
379 FND_MSG_PUB.Count_And_Get(
380 p_count => x_msg_count,
381 p_data => x_msg_data);
382
383 EXCEPTION
384 WHEN fnd_api.G_EXC_ERROR THEN
385
386 ROLLBACK TO create_txn_party_dtls;
387 x_return_status := fnd_api.G_RET_STS_ERROR ;
388 FND_MSG_PUB.Count_And_Get (
389 p_count => x_msg_count,
390 p_data => x_msg_data);
391
392 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
393
394 ROLLBACK TO create_txn_party_dtls;
395 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
396
397 FND_MSG_PUB.Count_And_Get(
398 p_count => x_msg_count,
399 p_data => x_msg_data);
400
401 WHEN OTHERS THEN
402
403 ROLLBACK TO create_txn_party_dtls;
404 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
405
406 IF FND_MSG_PUB.Check_Msg_Level(
407 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
408
409 FND_MSG_PUB.Add_Exc_Msg(
410 p_pkg_name => G_PKG_NAME,
411 p_procedure_name => l_api_name);
412
413 END IF;
414
415 FND_MSG_PUB.Count_And_Get(
416 p_count => x_msg_count,
417 p_data => x_msg_data);
418
419 END create_txn_party_dtls;
420
421
422 PROCEDURE create_txn_pty_acct_dtls(
423 p_api_version IN number,
424 p_commit IN varchar2 := fnd_api.g_false,
425 p_init_msg_list IN varchar2 := fnd_api.g_false,
426 p_validation_level IN number := fnd_api.g_valid_level_full,
427 p_txn_pty_acct_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_rec,
428 x_return_status OUT NOCOPY varchar2,
429 x_msg_count OUT NOCOPY number,
430 x_msg_data OUT NOCOPY varchar2)
431
432 IS
433
434 l_api_name CONSTANT varchar2(30) := 'create_txn_pty_acct_dtls';
435 l_api_version CONSTANT number := 1.0;
436 l_debug_level number;
437 l_txn_account_detail_id number;
438 l_pty_dtl_rec csi_t_party_details%rowtype;
439
440 l_preserve_detail_flag varchar2(1);
441 l_return_status varchar2(1);
442
443 BEGIN
444
445 -- Standard Start of API savepoint
446 SAVEPOINT create_txn_pty_acct_dtls;
447
448 -- Initialize message list if p_init_msg_list is set to TRUE.
449 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
450 FND_MSG_PUB.initialize;
451 END IF;
452
453 -- Initialize API return status to success
454 x_return_status := fnd_api.G_RET_STS_SUCCESS;
455
456 -- Standard call to check for call compatibility.
457 IF NOT
458
459 fnd_api.compatible_api_call (
460 p_current_version_number => l_api_version,
461 p_caller_version_number => p_api_version,
462 p_api_name => l_api_name,
463 p_pkg_name => g_pkg_name) THEN
464
465 RAISE fnd_api.g_exc_unexpected_error;
466
467 END IF;
468
469 -- Check the profile option debug_level for debug message reporting
470 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
471
472 csi_t_gen_utility_pvt.dump_api_info(
473 p_pkg_name => g_pkg_name,
474 p_api_name => l_api_name);
475
476 IF l_debug_level > 1 THEN
477 csi_t_gen_utility_pvt.add(
478 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
479
480 csi_t_gen_utility_pvt.dump_pty_acct_rec(
481 p_pty_acct_rec => p_txn_pty_acct_detail_rec);
482 END IF;
483
484 -- Main API code
485
486 csi_t_vldn_routines_pvt.check_reqd_param(
487 p_value => p_txn_pty_acct_detail_rec.account_id,
488 p_param_name => 'p_txn_pty_acct_detail_rec.account_id',
489 p_api_name => 'create_txn_pty_acct_dtls');
490
491 csi_t_vldn_routines_pvt.check_reqd_param(
492 p_value => p_txn_pty_acct_detail_rec.relationship_type_code,
493 p_param_name => 'p_txn_pty_acct_detail_rec.relationship_type_code',
494 p_api_name => 'create_txn_pty_acct_dtls');
495
496 -- validate account_id from hz_cust_accounts table
497 csi_t_vldn_routines_pvt.validate_account_id(
498 p_account_id => p_txn_pty_acct_detail_rec.account_id,
499 x_return_status => l_return_status);
500
501 IF l_return_status <> fnd_api.g_ret_sts_success THEN
502
503 FND_MESSAGE.set_name('CSI','CSI_TXN_ACCOUNT_ID_INVALID');
504 FND_MESSAGE.set_token('ACCT_ID',p_txn_pty_acct_detail_rec.account_id);
505 FND_MSG_PUB.add;
506 RAISE fnd_api.g_exc_error;
507
508 END IF;
509
510 -- validate relationship_type_code from cs_lookups table
511 IF NOT
512 csi_instance_parties_vld_pvt.is_pty_rel_type_valid(
513 p_party_rel_type_code => p_txn_pty_acct_detail_rec.relationship_type_code,
514 p_contact_flag => 'A')
515 THEN
516 RAISE fnd_api.g_exc_error;
517 END IF;
518
519
520 --##
521 -- validate ip_acount_id from the csi_instance_party_account_table
522 IF NVL(p_txn_pty_acct_detail_rec.ip_account_id, fnd_api.g_miss_num) <>
523 fnd_api.g_miss_num
524 THEN
525
526 csi_t_vldn_routines_pvt.validate_ip_account_id(
527 p_ip_account_id => p_txn_pty_acct_detail_rec.ip_account_id,
528 x_return_status => l_return_status);
529
530 IF x_return_status <> fnd_api.g_ret_sts_success THEN
531
532 FND_MESSAGE.set_name('CSI','CSI_API_INVALID_IP_ACCOUNT_ID');
533 FND_MESSAGE.set_token('IP_ACCOUNT_ID',p_txn_pty_acct_detail_rec.ip_account_id);
534 FND_MSG_PUB.add;
535 RAISE fnd_api.g_exc_error;
536
537 END IF;
538
539 END IF;
540
541 -- validate# the party account id
542 csi_t_vldn_routines_pvt.get_party_detail_rec(
543 p_party_detail_id => p_txn_pty_acct_detail_rec.txn_party_detail_id,
544 x_party_detail_rec => l_pty_dtl_rec,
545 x_return_status => l_return_status);
546 IF l_return_status <> fnd_api.g_ret_sts_success THEN
547 -- raise error
548 RAISE fnd_api.g_exc_error;
549 END IF;
550
551 csi_t_vldn_routines_pvt.validate_party_account_id(
552 p_party_id => l_pty_dtl_rec.party_source_id,
553 p_party_account_id => p_txn_pty_acct_detail_rec.account_id,
554 x_return_status => l_return_status);
555
556 IF l_return_status <> fnd_api.g_ret_sts_success THEN
557
558 FND_MESSAGE.set_name('CSI','CSI_TXN_PTY_ACCT_INVALID');
559 FND_MESSAGE.set_token('PTY_ID',l_pty_dtl_rec.party_source_id);
560 FND_MESSAGE.set_token('PTY_ACC_ID',p_txn_pty_acct_detail_rec.account_id);
561 FND_MSG_PUB.add;
562 RAISE fnd_api.g_exc_error;
563
564 END IF;
565
566 -- validate bill_to and ship_to address
567 IF p_txn_pty_acct_detail_rec.bill_to_address_id <> fnd_api.g_miss_num THEN
568
569 csi_t_vldn_routines_pvt.validate_site_use_id(
570 p_account_id => p_txn_pty_acct_detail_rec.account_id,
571 p_site_use_id => p_txn_pty_acct_detail_rec.bill_to_address_id,
572 p_site_use_code => 'BILL_TO',
573 x_return_status => l_return_status);
574
575 IF l_return_status <> fnd_api.g_ret_sts_success THEN
576 FND_MESSAGE.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
577 FND_MESSAGE.set_token('SITE_USE_ID',p_txn_pty_acct_detail_rec.
578 bill_to_address_id);
579 FND_MESSAGE.set_token('SITE_USE_CODE','BILL_TO');
580 FND_MSG_PUB.add;
581 RAISE fnd_api.g_exc_error;
582 END IF;
583
584 END IF;
585
586 IF p_txn_pty_acct_detail_rec.ship_to_address_id <> fnd_api.g_miss_num THEN
587
588 csi_t_vldn_routines_pvt.validate_site_use_id(
589 p_account_id => p_txn_pty_acct_detail_rec.account_id,
590 p_site_use_id => p_txn_pty_acct_detail_rec.ship_to_address_id,
591 p_site_use_code => 'SHIP_TO',
592 x_return_status => l_return_status);
593
594 IF l_return_status <> fnd_api.g_ret_sts_success THEN
595 FND_MESSAGE.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
596 FND_MESSAGE.set_token('SITE_USE_ID',p_txn_pty_acct_detail_rec.
597 ship_to_address_id);
598 FND_MESSAGE.set_token('SITE_USE_CODE','SHIP_TO');
599 FND_MSG_PUB.add;
600 RAISE fnd_api.g_exc_error;
601 END IF;
602
603 END IF;
604
605 SELECT decode(nvl(p_txn_pty_acct_detail_rec.preserve_detail_flag,fnd_api.g_miss_char),
606 fnd_api.g_miss_char, 'Y', p_txn_pty_acct_detail_rec.preserve_detail_flag)
607 INTO l_preserve_detail_flag
608 FROM sys.dual;
609
610 -- call the table handler to insert row in to the database table
611 IF nvl(p_txn_pty_acct_detail_rec.txn_account_detail_id, fnd_api.g_miss_num) <>
612 fnd_api.g_miss_num THEN
613 l_txn_account_detail_id := p_txn_pty_acct_detail_rec.txn_account_detail_id;
614 END IF;
615
616 begin
617 csi_t_gen_utility_pvt.dump_api_info(
618 p_api_name => 'insert_row',
619 p_pkg_name => 'csi_t_party_accounts_pkg');
620
621 csi_t_party_accounts_pkg.insert_row(
622 px_txn_account_detail_id => l_txn_account_detail_id,
623 p_txn_party_detail_id => p_txn_pty_acct_detail_rec.txn_party_detail_id,
624 p_ip_account_id => p_txn_pty_acct_detail_rec.ip_account_id,
625 p_account_id => p_txn_pty_acct_detail_rec.account_id,
626 p_relationship_type_code => p_txn_pty_acct_detail_rec.relationship_type_code,
627 p_bill_to_address_id => p_txn_pty_acct_detail_rec.bill_to_address_id,
628 p_ship_to_address_id => p_txn_pty_acct_detail_rec.ship_to_address_id,
629 p_active_start_date => p_txn_pty_acct_detail_rec.active_start_date,
630 p_active_end_date => p_txn_pty_acct_detail_rec.active_end_date,
631 p_preserve_detail_flag => l_preserve_detail_flag,
632 p_attribute1 => p_txn_pty_acct_detail_rec.attribute1,
633 p_attribute2 => p_txn_pty_acct_detail_rec.attribute2,
634 p_attribute3 => p_txn_pty_acct_detail_rec.attribute3,
635 p_attribute4 => p_txn_pty_acct_detail_rec.attribute4,
636 p_attribute5 => p_txn_pty_acct_detail_rec.attribute5,
637 p_attribute6 => p_txn_pty_acct_detail_rec.attribute6,
638 p_attribute7 => p_txn_pty_acct_detail_rec.attribute7,
639 p_attribute8 => p_txn_pty_acct_detail_rec.attribute8,
640 p_attribute9 => p_txn_pty_acct_detail_rec.attribute9,
641 p_attribute10 => p_txn_pty_acct_detail_rec.attribute10,
642 p_attribute11 => p_txn_pty_acct_detail_rec.attribute11,
643 p_attribute12 => p_txn_pty_acct_detail_rec.attribute12,
644 p_attribute13 => p_txn_pty_acct_detail_rec.attribute13,
645 p_attribute14 => p_txn_pty_acct_detail_rec.attribute14,
646 p_attribute15 => p_txn_pty_acct_detail_rec.attribute15,
647 p_created_by => g_user_id,
648 p_creation_date => sysdate,
649 p_last_updated_by => g_user_id,
650 p_last_update_date => sysdate,
651 p_last_update_login => g_login_id,
652 p_object_version_number => 1.0,
653 p_context => p_txn_pty_acct_detail_rec.context);
654
655 exception
656 when others then
657 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
658 fnd_message.set_token('MESSAGE',
659 'csi_t_party_accounts_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
660 fnd_msg_pub.add;
661 raise fnd_api.g_exc_error;
662 end;
663
664 p_txn_pty_acct_detail_rec.txn_account_detail_id := l_txn_account_detail_id;
665
666 -- Standard check of p_commit.
667 IF fnd_api.To_Boolean( p_commit ) THEN
668 COMMIT WORK;
669 END IF;
670
671 csi_t_gen_utility_pvt.set_debug_off;
672
673 -- Standard call to get message count and if count is get message info.
674 FND_MSG_PUB.Count_And_Get(
675 p_count => x_msg_count,
676 p_data => x_msg_data);
677
678 EXCEPTION
679 WHEN fnd_api.G_EXC_ERROR THEN
680
681 ROLLBACK TO Create_Txn_Pty_Acct_Dtls;
682 x_return_status := fnd_api.G_RET_STS_ERROR ;
683 FND_MSG_PUB.Count_And_Get (
684 p_count => x_msg_count,
685 p_data => x_msg_data);
686 csi_t_gen_utility_pvt.set_debug_off;
687
688 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
689
690 ROLLBACK TO Create_Txn_Pty_Acct_Dtls;
691 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
692
693 FND_MSG_PUB.Count_And_Get(
694 p_count => x_msg_count,
695 p_data => x_msg_data);
696 csi_t_gen_utility_pvt.set_debug_off;
697
698 WHEN OTHERS THEN
699
700 ROLLBACK TO Create_Txn_Pty_Acct_Dtls;
701 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
702
703 IF FND_MSG_PUB.Check_Msg_Level(
704 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
705
706 FND_MSG_PUB.Add_Exc_Msg(
707 p_pkg_name => G_PKG_NAME,
708 p_procedure_name => l_api_name);
709
710 END IF;
711
712 FND_MSG_PUB.Count_And_Get(
713 p_count => x_msg_count,
714 p_data => x_msg_data);
715 csi_t_gen_utility_pvt.set_debug_off;
716
717 END create_txn_pty_acct_dtls;
718
719 PROCEDURE update_txn_party_dtls(
720 p_api_version IN NUMBER,
721 p_commit IN VARCHAR2 := fnd_api.g_false,
722 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
723 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
724 p_txn_party_detail_tbl IN csi_t_datastructures_grp.txn_party_detail_tbl,
725 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
726 x_return_status OUT NOCOPY VARCHAR2,
727 x_msg_count OUT NOCOPY NUMBER,
728 x_msg_data OUT NOCOPY VARCHAR2)
729 IS
730
731 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_party_dtls';
732 l_api_version CONSTANT NUMBER := 1.0;
733 l_debug_level NUMBER;
734
735 l_pty_rec csi_t_party_details%ROWTYPE;
736 l_pty_acct_rec csi_t_datastructures_grp.txn_pty_acct_detail_rec;
737 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
738 l_msg_count number;
739 l_msg_data varchar2(512);
740 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
741 l_del_pa_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
742 l_del_pa_ind binary_integer;
743 l_instance_exists_flag csi_t_txn_line_details.instance_exists_flag%TYPE;
744 l_instance_id csi_t_txn_line_details.instance_id%TYPE;
745 l_contact_flag varchar2(1) := 'P';
746 l_u_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
747 l_u_acct_ind binary_integer;
748
749 CURSOR pty_cur(p_pty_dtl_id IN number) IS
750 SELECT *
751 FROM csi_t_party_details
752 WHERE txn_party_detail_id = p_pty_dtl_id;
753
754 CURSOR pa_cur(p_pty_dtl_id in number) IS
755 SELECT *
756 FROM csi_t_party_accounts
757 WHERE txn_party_detail_id = p_pty_dtl_id;
758
759 CURSOR cont_pty_cur(p_txn_party_detail_id in number) IS
760 SELECT txn_party_detail_id, preserve_detail_flag
761 FROM csi_t_party_details
762 WHERE contact_party_id = p_txn_party_detail_id
763 AND contact_flag = 'Y';
764
765 BEGIN
766
767 -- Standard Start of API savepoint
768 SAVEPOINT update_txn_party_dtls;
769
770 -- Initialize message list if p_init_msg_list is set to TRUE.
771 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
772 FND_MSG_PUB.initialize;
773 END IF;
774
775 -- Initialize API return status to success
776 x_return_status := fnd_api.G_RET_STS_SUCCESS;
777
778 -- Standard call to check for call compatibility.
779 IF NOT
780
781 fnd_api.Compatible_API_Call (
782 p_current_version_number => l_api_version,
783 p_caller_version_number => p_api_version,
784 p_api_name => l_api_name,
785 p_pkg_name => G_PKG_NAME) THEN
786
787 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
788
789 END IF;
790
791 -- debug messages
792 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
793
794 csi_t_gen_utility_pvt.dump_api_info(
795 p_pkg_name => g_pkg_name,
796 p_api_name => l_api_name);
797
798 csi_t_gen_utility_pvt.add(
799 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
800
801 -- Main API code
802
803 IF p_txn_party_detail_tbl.COUNT > 0 THEN
804
805 FOR l_ind IN p_txn_party_detail_tbl.FIRST .. p_txn_party_detail_tbl.LAST
806 LOOP
807
808 IF l_debug_level > 1 THEN
809 csi_t_gen_utility_pvt.dump_party_detail_rec(
810 p_party_detail_rec => p_txn_party_detail_tbl(l_ind));
811 END IF;
812
813 l_pty_rec.txn_party_detail_id :=
814 p_txn_party_detail_tbl(l_ind).txn_party_detail_id;
815
816 csi_t_vldn_routines_pvt.check_reqd_param(
817 p_value => l_pty_rec.txn_party_detail_id,
818 p_param_name => 'l_pty_rec.txn_party_detail_id',
819 p_api_name => l_api_name);
820
821 -- validate txn_party_detail_id
822 csi_t_vldn_routines_pvt.validate_txn_party_detail_id(
823 p_txn_party_detail_id => l_pty_rec.txn_party_detail_id,
824 x_return_status => l_return_status);
825
826 IF l_return_status <> fnd_api.g_ret_sts_success THEN
827
828 FND_MESSAGE.set_name('CSI','CSI_TXN_PARTY_DTL_ID_INVALID');
829 FND_MESSAGE.set_token('PTY_DTL_ID',l_pty_rec.txn_party_detail_id);
830 FND_MSG_PUB.add;
831 RAISE fnd_api.g_exc_error;
832
833 END IF;
834
835 FOR l_pty_cur_rec IN pty_cur (l_pty_rec.txn_party_detail_id)
836 LOOP
837
838 l_pty_rec.txn_line_detail_id := l_pty_cur_rec.txn_line_detail_id;
839
840 -- check the processing status
841
842 csi_t_vldn_routines_pvt.get_processing_status(
843 p_level => 'PARTY',
844 p_level_dtl_id => l_pty_rec.txn_line_detail_id,
845 x_processing_status => l_processing_status,
846 x_return_status => l_return_status);
847
848 IF l_processing_status = 'PROCESSED' THEN
849
850 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
851 FND_MESSAGE.set_token('LVL_ID', l_pty_rec.txn_line_detail_id);
852 FND_MESSAGE.set_token('STATUS',l_processing_status);
853 FND_MSG_PUB.add;
854 RAISE fnd_api.g_exc_error;
855
856 END IF;
857
858 l_pty_rec.party_source_table :=
859 p_txn_party_detail_tbl(l_ind).party_source_table;
860
861 IF l_pty_rec.party_source_table <> fnd_api.g_miss_char
862 THEN
863
864 -- validate party_source_table
865 csi_t_vldn_routines_pvt.check_reqd_param(
866 p_value => l_pty_rec.party_source_table,
867 p_param_name => 'l_pty_rec.party_source_table',
868 p_api_name => l_api_name);
869
870 IF NOT
871 csi_instance_parties_vld_pvt.is_pty_source_tab_valid(
872 p_party_source_table => l_pty_rec.party_source_table)
873 THEN
874 csi_t_gen_utility_pvt.add('Validate party source table failed.');
875 RAISE fnd_api.g_exc_error;
876 END IF;
877
878 END IF;
879
880 l_pty_rec.party_source_id :=
881 p_txn_party_detail_tbl(l_ind).party_source_id;
882
883 l_pty_rec.contact_flag :=
884 p_txn_party_detail_tbl(l_ind).contact_flag;
885
886
887 IF l_pty_rec.party_source_id <> fnd_api.g_miss_num THEN
888
889 csi_t_vldn_routines_pvt.check_reqd_param(
890 p_value => l_pty_rec.party_source_id,
891 p_param_name => 'l_pty_rec.party_source_id',
892 p_api_name => l_api_name);
893
894 -- validate party_source_id
895 IF NOT
896 csi_instance_parties_vld_pvt.is_party_valid(
897 p_party_source_table => l_pty_rec.party_source_table,
898 p_party_id => l_pty_rec.party_source_id,
899 p_contact_flag => l_pty_rec.contact_flag)
900 THEN
901 csi_t_gen_utility_pvt.add('Validate party source table failed.');
902 RAISE fnd_api.g_exc_error;
903 END IF;
904 END IF;
905
906 l_pty_rec.instance_party_id :=
907 p_txn_party_detail_tbl(l_ind).instance_party_id;
908
909 IF NVL(l_pty_rec.instance_party_id , fnd_api.g_miss_num) <>
910 fnd_api.g_miss_num
911 THEN
912
913 --check if the instance_party_id is a valid dtl of
914 --referenced instance id (from the txn_line_details)
915
916 csi_t_vldn_routines_pvt.validate_instance_reference(
917 p_level => 'PARTY',
918 p_level_dtl_id => l_pty_rec.txn_line_detail_id,
919 p_level_inst_ref_id => l_pty_rec.instance_party_id,
920 x_return_status => l_return_status);
921
922 IF l_return_status <> fnd_api.g_ret_sts_success THEN
923 csi_t_gen_utility_pvt.add('Validate instance party id failed.');
924 RAISE fnd_api.g_exc_error;
925 END IF;
926
927 END IF;
928
929 l_pty_rec.relationship_type_code :=
930 p_txn_party_detail_tbl(l_ind).relationship_type_code;
931
932 IF l_pty_rec.relationship_type_code <> fnd_api.g_miss_char THEN
933
934 csi_t_vldn_routines_pvt.check_reqd_param(
935 p_value => l_pty_rec.relationship_type_code,
936 p_param_name => 'l_pty_rec.relationship_type_code',
937 p_api_name => l_api_name);
938
939 IF l_pty_rec.contact_flag = 'Y' THEN
940 l_contact_flag := 'C';
941 ELSE
942 l_contact_flag := 'P';
943 END IF;
944
945 --validate relationship_type_code
946 IF NOT
947 csi_instance_parties_vld_pvt.is_pty_rel_type_valid(
948 p_party_rel_type_code => l_pty_rec.relationship_type_code,
949 p_contact_flag => l_contact_flag)
950 THEN
951 csi_t_gen_utility_pvt.add('Validate party relationship type code failed.');
952 RAISE fnd_api.g_exc_error;
953 END IF;
954
955 END IF;
956
957 l_pty_rec.active_start_date :=
958 p_txn_party_detail_tbl(l_ind).active_start_date;
959
960 l_pty_rec.active_end_date :=
961 p_txn_party_detail_tbl(l_ind).active_end_date;
962
963 l_pty_rec.preserve_detail_flag :=
964 p_txn_party_detail_tbl(l_ind).preserve_detail_flag;
965
966 l_pty_rec.attribute1 :=
967 p_txn_party_detail_tbl(l_ind).attribute1;
968
969 l_pty_rec.attribute2 :=
970 p_txn_party_detail_tbl(l_ind).attribute2;
971
972 l_pty_rec.attribute3 :=
973 p_txn_party_detail_tbl(l_ind).attribute3;
974
975 l_pty_rec.attribute4 :=
976 p_txn_party_detail_tbl(l_ind).attribute4;
977
978 l_pty_rec.attribute5 :=
979 p_txn_party_detail_tbl(l_ind).attribute5;
980
981 l_pty_rec.attribute6 :=
982 p_txn_party_detail_tbl(l_ind).attribute6;
983
984 l_pty_rec.attribute7 :=
985 p_txn_party_detail_tbl(l_ind).attribute7;
986
987 l_pty_rec.attribute8 :=
988 p_txn_party_detail_tbl(l_ind).attribute8;
989
990 l_pty_rec.attribute9 :=
991 p_txn_party_detail_tbl(l_ind).attribute9;
992
993 l_pty_rec.attribute10 :=
994 p_txn_party_detail_tbl(l_ind).attribute10;
995
996 l_pty_rec.attribute11 :=
997 p_txn_party_detail_tbl(l_ind).attribute11;
998
999 l_pty_rec.attribute12 :=
1000 p_txn_party_detail_tbl(l_ind).attribute12;
1001
1002 l_pty_rec.attribute13 :=
1003 p_txn_party_detail_tbl(l_ind).attribute13;
1004
1005 l_pty_rec.attribute14 :=
1006 p_txn_party_detail_tbl(l_ind).attribute14;
1007
1008 l_pty_rec.attribute15 :=
1009 p_txn_party_detail_tbl(l_ind).attribute15;
1010
1011 l_pty_rec.created_by := l_pty_cur_rec.created_by;
1012 l_pty_rec.creation_date := l_pty_cur_rec.creation_date;
1013 l_pty_rec.last_updated_by := g_user_id;
1014 l_pty_rec.last_update_date := sysdate;
1015 l_pty_rec.last_update_login := g_login_id;
1016
1017 l_pty_rec.object_version_number :=
1018 p_txn_party_detail_tbl(l_ind).object_version_number;
1019
1020 l_pty_rec.context :=
1021 p_txn_party_detail_tbl(l_ind).context;
1022
1023 l_pty_rec.contact_party_id :=
1024 p_txn_party_detail_tbl(l_ind).contact_party_id;
1025
1026 l_pty_rec.primary_flag :=
1027 p_txn_party_detail_tbl(l_ind).primary_flag;
1028
1029 l_pty_rec.preferred_flag :=
1030 p_txn_party_detail_tbl(l_ind).preferred_flag;
1031
1032
1033 IF (l_pty_rec.party_source_id <> l_pty_cur_rec.party_source_id) THEN
1034
1035 --check for the existing party_accounts if found then see if
1036 --loop thru the pty accounts and identify the candidated for deletion
1037
1038 l_del_pa_ind := 0;
1039
1040 FOR l_pa_cur_rec in pa_cur(l_pty_rec.txn_party_detail_id)
1041 LOOP
1042
1043 /* see if the preserve detail flag is set for any of the child */
1044 IF nvl(l_pa_cur_rec.preserve_detail_flag,'N') = 'Y' THEN
1045
1046 /* check if the account id is valid for the party */
1047 csi_t_vldn_routines_pvt.validate_party_account_id(
1048 p_party_id => l_pty_rec.party_source_id,
1049 p_party_account_id => l_pa_cur_rec.account_id,
1050 x_return_status => l_return_status);
1051
1052 /* if not valid then mark the record for deletion */
1053 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1054
1055 l_del_pa_ind := l_del_pa_ind + 1;
1056
1057 l_del_pa_ids_tbl(l_del_pa_ind).txn_party_detail_id :=
1058 l_pa_cur_rec.txn_party_detail_id;
1059 l_del_pa_ids_tbl(l_del_pa_ind).txn_account_detail_id :=
1060 l_pa_cur_rec.txn_account_detail_id;
1061
1062 END IF;
1063
1064 ELSE
1065 -- mark record for deletion (populate the ids table)
1066 l_del_pa_ind := l_del_pa_ind + 1;
1067
1068 l_del_pa_ids_tbl(l_del_pa_ind).txn_party_detail_id :=
1069 l_pa_cur_rec.txn_party_detail_id;
1070 l_del_pa_ids_tbl(l_del_pa_ind).txn_account_detail_id :=
1071 l_pa_cur_rec.txn_account_detail_id;
1072
1073 END IF;
1074
1075 END LOOP;
1076
1077
1078 IF l_del_pa_ids_tbl.COUNT > 0 THEN
1079 --call the delete party accounts api
1080
1081 csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
1082 p_api_version => p_api_version,
1083 p_commit => p_commit,
1084 p_init_msg_list => p_init_msg_list,
1085 p_validation_level => p_validation_level,
1086 p_txn_pty_acct_ids_tbl => l_del_pa_ids_tbl,
1087 x_return_status => l_return_status,
1088 x_msg_count => l_msg_count,
1089 x_msg_data => l_msg_data);
1090
1091 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1092 RAISE fnd_api.g_exc_error;
1093 END IF;
1094
1095 END IF;
1096
1097 -- Self bug - added during Mass update testing....
1098 --check for the existing party contacts if found then see if they need to, can be preserved
1099 -- If the vldn fails for the party contacts, then delete them too
1100 --loop thru the pty contacts and identify the candidated for deletion
1101 FOR cont_pty_rec in cont_pty_cur(l_pty_rec.txn_party_detail_id)
1102 LOOP
1103 IF nvl(cont_pty_rec.preserve_detail_flag,'N') = 'Y' THEN
1104
1105 /* check if the contact is valid for the new party */
1106 -- currently we do not seem to validate the contacts in txn details for even normal creation
1107 -- at the time of fixing it, code needs to be added here too
1108 debug('Party Contact needs to be preserved : '||cont_pty_rec.txn_party_detail_id);
1109 ELSE
1110 debug('Deleting the Party Contact, must not be preserved : '||cont_pty_rec.txn_party_detail_id);
1111 csi_t_gen_utility_pvt.dump_api_info(
1112 p_api_name => 'delete_row',
1113 p_pkg_name => 'csi_t_party_details_pkg');
1114
1115 csi_t_party_details_pkg.delete_row(
1116 p_txn_party_detail_id => cont_pty_rec.txn_party_detail_id);
1117 END IF;
1118
1119 END LOOP;
1120
1121 END IF;
1122
1123 begin
1124 csi_t_gen_utility_pvt.dump_api_info(
1125 p_api_name => 'update_row',
1126 p_pkg_name => 'csi_t_party_details_pkg');
1127
1128 csi_t_party_details_pkg.update_row(
1129 p_txn_party_detail_id => l_pty_rec.txn_party_detail_id,
1130 p_txn_line_detail_id => l_pty_rec.txn_line_detail_id,
1131 p_party_source_table => l_pty_rec.party_source_table,
1132 p_party_source_id => l_pty_rec.party_source_id,
1133 p_relationship_type_code => l_pty_rec.relationship_type_code,
1134 p_contact_flag => l_pty_rec.contact_flag,
1135 p_active_start_date => l_pty_rec.active_start_date,
1136 p_active_end_date => l_pty_rec.active_end_date,
1137 p_preserve_detail_flag => l_pty_rec.preserve_detail_flag,
1138 p_instance_party_id => l_pty_rec.instance_party_id,
1139 p_attribute1 => l_pty_rec.attribute1,
1140 p_attribute2 => l_pty_rec.attribute2,
1141 p_attribute3 => l_pty_rec.attribute3,
1142 p_attribute4 => l_pty_rec.attribute4,
1143 p_attribute5 => l_pty_rec.attribute5,
1144 p_attribute6 => l_pty_rec.attribute6,
1145 p_attribute7 => l_pty_rec.attribute7,
1146 p_attribute8 => l_pty_rec.attribute8,
1147 p_attribute9 => l_pty_rec.attribute9,
1148 p_attribute10 => l_pty_rec.attribute10,
1149 p_attribute11 => l_pty_rec.attribute11,
1150 p_attribute12 => l_pty_rec.attribute12,
1151 p_attribute13 => l_pty_rec.attribute13,
1152 p_attribute14 => l_pty_rec.attribute14,
1153 p_attribute15 => l_pty_rec.attribute15,
1154 p_created_by => l_pty_rec.created_by,
1155 p_creation_date => l_pty_rec.creation_date,
1156 p_last_updated_by => l_pty_rec.last_updated_by,
1157 p_last_update_date => l_pty_rec.last_update_date,
1158 p_last_update_login => l_pty_rec.last_update_login,
1159 p_object_version_number => l_pty_rec.object_version_number,
1160 p_context => l_pty_rec.context,
1161 p_contact_party_id => l_pty_rec.contact_party_id,
1162 p_primary_flag => l_pty_rec.primary_flag,
1163 p_preferred_flag => l_pty_rec.preferred_flag);
1164
1165 exception
1166 when others then
1167 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1168 fnd_message.set_token('MESSAGE',
1169 'csi_t_party_details_pkg.update_row Failed. '||substr(sqlerrm,1,200));
1170 fnd_msg_pub.add;
1171 raise fnd_api.g_exc_error;
1172 end;
1173
1174 END LOOP;
1175
1176
1177 IF px_txn_pty_acct_detail_tbl.COUNT > 0 THEN
1178 l_u_acct_ind := 0;
1179
1180 FOR l_pa_ind IN px_txn_pty_acct_detail_tbl.FIRST..
1181 px_txn_pty_acct_detail_tbl.LAST
1182 LOOP
1183 -- Self-Bug, part of Mass update. Handle create Account within a party update...
1184 IF nvl(px_txn_pty_acct_detail_tbl(l_pa_ind).txn_account_detail_id,fnd_api.g_miss_num) <>
1185 fnd_api.g_miss_num THEN -- it is a update account
1186
1187 -- Build the acct tbl to call it at the end...
1188
1189 l_u_pty_acct_tbl(l_u_acct_ind).txn_account_detail_id :=
1190 px_txn_pty_acct_detail_tbl(l_pa_ind).txn_account_detail_id;
1191
1192 l_u_pty_acct_tbl(l_u_acct_ind).txn_party_detail_id :=
1193 l_pty_rec.txn_party_detail_id;
1194
1195 l_u_pty_acct_tbl(l_u_acct_ind).ip_account_id :=
1196 px_txn_pty_acct_detail_tbl(l_pa_ind).ip_account_id;
1197
1198 l_u_pty_acct_tbl(l_u_acct_ind).account_id :=
1199 px_txn_pty_acct_detail_tbl(l_pa_ind).account_id;
1200
1201 l_u_pty_acct_tbl(l_u_acct_ind).relationship_type_code :=
1202 px_txn_pty_acct_detail_tbl(l_pa_ind).relationship_type_code;
1203
1204 l_u_pty_acct_tbl(l_u_acct_ind).bill_to_address_id :=
1205 px_txn_pty_acct_detail_tbl(l_pa_ind).bill_to_address_id;
1206
1207 l_u_pty_acct_tbl(l_u_acct_ind).ship_to_address_id :=
1208 px_txn_pty_acct_detail_tbl(l_pa_ind).ship_to_address_id;
1209
1210 l_u_pty_acct_tbl(l_u_acct_ind).active_start_date :=
1211 px_txn_pty_acct_detail_tbl(l_pa_ind).active_start_date;
1212
1213 l_u_pty_acct_tbl(l_u_acct_ind).active_end_date :=
1214 px_txn_pty_acct_detail_tbl(l_pa_ind).active_end_date;
1215
1216 l_u_pty_acct_tbl(l_u_acct_ind).preserve_detail_flag :=
1217 px_txn_pty_acct_detail_tbl(l_pa_ind).preserve_detail_flag;
1218
1219 l_u_pty_acct_tbl(l_u_acct_ind).context :=
1220 px_txn_pty_acct_detail_tbl(l_pa_ind).context;
1221
1222 l_u_pty_acct_tbl(l_u_acct_ind).attribute1 :=
1223 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute1;
1224
1225 l_u_pty_acct_tbl(l_u_acct_ind).attribute2 :=
1226 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute2;
1227
1228 l_u_pty_acct_tbl(l_u_acct_ind).attribute3 :=
1229 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute3;
1230
1231 l_u_pty_acct_tbl(l_u_acct_ind).attribute4 :=
1232 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute4;
1233
1234 l_u_pty_acct_tbl(l_u_acct_ind).attribute5 :=
1235 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute5;
1236
1237 l_u_pty_acct_tbl(l_u_acct_ind).attribute6 :=
1238 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute6;
1239
1240 l_u_pty_acct_tbl(l_u_acct_ind).attribute7 :=
1241 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute7;
1242
1243 l_u_pty_acct_tbl(l_u_acct_ind).attribute8 :=
1244 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute8;
1245
1246 l_u_pty_acct_tbl(l_u_acct_ind).attribute9 :=
1247 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute9;
1248
1249 l_u_pty_acct_tbl(l_u_acct_ind).attribute10 :=
1250 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute10;
1251
1252 l_u_pty_acct_tbl(l_u_acct_ind).attribute11 :=
1253 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute11;
1254
1255 l_u_pty_acct_tbl(l_u_acct_ind).attribute12 :=
1256 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute12;
1257
1258 l_u_pty_acct_tbl(l_u_acct_ind).attribute13 :=
1259 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute13;
1260
1261 l_u_pty_acct_tbl(l_u_acct_ind).attribute14 :=
1262 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute14;
1263
1264 l_u_pty_acct_tbl(l_u_acct_ind).attribute15 :=
1265 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute15;
1266
1267 l_u_pty_acct_tbl(l_u_acct_ind).txn_party_details_index :=
1268 px_txn_pty_acct_detail_tbl(l_pa_ind).txn_party_details_index;
1269
1270 l_u_pty_acct_tbl(l_u_acct_ind).object_version_number :=
1271 px_txn_pty_acct_detail_tbl(l_pa_ind).object_version_number;
1272 l_u_acct_ind := l_u_acct_ind + 1;
1273
1274 ELSIF px_txn_pty_acct_detail_tbl(l_pa_ind).txn_party_detail_id =
1275 l_pty_rec.txn_party_detail_id THEN
1276
1277 --populate row type variable
1278
1279 l_pty_acct_rec.txn_account_detail_id :=
1280 px_txn_pty_acct_detail_tbl(l_pa_ind).txn_account_detail_id;
1281
1282 l_pty_acct_rec.txn_party_detail_id :=
1283 l_pty_rec.txn_party_detail_id;
1284
1285 l_pty_acct_rec.ip_account_id :=
1286 px_txn_pty_acct_detail_tbl(l_pa_ind).ip_account_id;
1287
1288 l_pty_acct_rec.account_id :=
1289 px_txn_pty_acct_detail_tbl(l_pa_ind).account_id;
1290
1291 l_pty_acct_rec.relationship_type_code :=
1292 px_txn_pty_acct_detail_tbl(l_pa_ind).relationship_type_code;
1293
1294 l_pty_acct_rec.bill_to_address_id :=
1295 px_txn_pty_acct_detail_tbl(l_pa_ind).bill_to_address_id;
1296
1297 l_pty_acct_rec.ship_to_address_id :=
1298 px_txn_pty_acct_detail_tbl(l_pa_ind).ship_to_address_id;
1299
1300 l_pty_acct_rec.active_start_date :=
1301 px_txn_pty_acct_detail_tbl(l_pa_ind).active_start_date;
1302
1303 l_pty_acct_rec.active_end_date :=
1304 px_txn_pty_acct_detail_tbl(l_pa_ind).active_end_date;
1305
1306 l_pty_acct_rec.preserve_detail_flag :=
1307 px_txn_pty_acct_detail_tbl(l_pa_ind).preserve_detail_flag;
1308
1309 l_pty_acct_rec.context :=
1310 px_txn_pty_acct_detail_tbl(l_pa_ind).context;
1311
1312 l_pty_acct_rec.attribute1 :=
1313 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute1;
1314
1315 l_pty_acct_rec.attribute2 :=
1316 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute2;
1317
1318 l_pty_acct_rec.attribute3 :=
1319 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute3;
1320
1321 l_pty_acct_rec.attribute4 :=
1322 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute4;
1323
1324 l_pty_acct_rec.attribute5 :=
1325 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute5;
1326
1327 l_pty_acct_rec.attribute6 :=
1328 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute6;
1329
1330 l_pty_acct_rec.attribute7 :=
1331 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute7;
1332
1333 l_pty_acct_rec.attribute8 :=
1334 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute8;
1335
1336 l_pty_acct_rec.attribute9 :=
1337 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute9;
1338
1339 l_pty_acct_rec.attribute10 :=
1340 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute10;
1341
1342 l_pty_acct_rec.attribute11 :=
1343 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute11;
1344
1345 l_pty_acct_rec.attribute12 :=
1346 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute12;
1347
1348 l_pty_acct_rec.attribute13 :=
1349 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute13;
1350
1351 l_pty_acct_rec.attribute14 :=
1352 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute14;
1353
1354 l_pty_acct_rec.attribute15 :=
1355 px_txn_pty_acct_detail_tbl(l_pa_ind).attribute15;
1356
1357 l_pty_acct_rec.txn_party_details_index :=
1358 px_txn_pty_acct_detail_tbl(l_pa_ind).txn_party_details_index;
1359
1360 l_pty_acct_rec.object_version_number :=
1361 px_txn_pty_acct_detail_tbl(l_pa_ind).object_version_number;
1362
1363
1364 -- call create party_account private API
1365 csi_t_txn_parties_pvt.create_txn_pty_acct_dtls(
1366 p_api_version => p_api_version,
1367 p_commit => p_commit,
1368 p_init_msg_list => p_init_msg_list,
1369 p_validation_level => p_validation_level,
1370 p_txn_pty_acct_detail_rec => l_pty_acct_rec,
1371 x_return_status => l_return_status,
1372 x_msg_count => l_msg_count,
1373 x_msg_data => l_msg_data);
1374
1375 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1376 RAISE fnd_api.g_exc_error;
1377 END IF;
1378
1379 END IF;
1380
1381 END LOOP;
1382
1383 IF l_u_pty_acct_tbl.count > 0 THEN
1384 -- call update party_account private API
1385 csi_t_txn_parties_pvt.update_txn_pty_acct_dtls(
1386 p_api_version => p_api_version,
1387 p_commit => p_commit,
1388 p_init_msg_list => p_init_msg_list,
1389 p_validation_level => p_validation_level,
1390 p_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
1391 x_return_status => l_return_status,
1392 x_msg_count => l_msg_count,
1393 x_msg_data => l_msg_data);
1394
1395 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1396 RAISE fnd_api.g_exc_error;
1397 END IF;
1398
1399 END IF;
1400 END IF;
1401
1402 END LOOP;
1403
1404 END IF;
1405
1406 -- Standard check of p_commit.
1407 IF fnd_api.To_Boolean( p_commit ) THEN
1408 COMMIT WORK;
1409 END IF;
1410
1411 -- Standard call to get message count and if count is get message info.
1412 FND_MSG_PUB.Count_And_Get(
1413 p_count => x_msg_count,
1414 p_data => x_msg_data);
1415
1416 EXCEPTION
1417 WHEN fnd_api.G_EXC_ERROR THEN
1418
1419 ROLLBACK TO update_txn_party_dtls;
1420 x_return_status := fnd_api.G_RET_STS_ERROR ;
1421 FND_MSG_PUB.Count_And_Get (
1422 p_count => x_msg_count,
1423 p_data => x_msg_data);
1424
1425 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1426
1427 ROLLBACK TO update_txn_party_dtls;
1428 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1429
1430 FND_MSG_PUB.Count_And_Get(
1431 p_count => x_msg_count,
1432 p_data => x_msg_data);
1433
1434 WHEN OTHERS THEN
1435
1436 ROLLBACK TO update_txn_party_dtls;
1437 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1438
1439 IF FND_MSG_PUB.Check_Msg_Level(
1440 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1441
1442 FND_MSG_PUB.Add_Exc_Msg(
1443 p_pkg_name => G_PKG_NAME,
1444 p_procedure_name => l_api_name);
1445
1446 END IF;
1447
1448 FND_MSG_PUB.Count_And_Get(
1449 p_count => x_msg_count,
1450 p_data => x_msg_data);
1451
1452 END update_txn_party_dtls;
1453
1454 /*
1455 */
1456 PROCEDURE update_txn_pty_acct_dtls (
1457 p_api_version IN NUMBER
1458 ,p_commit IN VARCHAR2 := fnd_api.g_false
1459 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1460 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1461 ,p_txn_pty_acct_detail_tbl IN csi_t_datastructures_grp.txn_pty_acct_detail_tbl
1462 ,x_return_status OUT NOCOPY VARCHAR2
1463 ,x_msg_count OUT NOCOPY NUMBER
1464 ,x_msg_data OUT NOCOPY VARCHAR2)
1465 IS
1466
1467 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_pty_acct_dtls';
1468 l_api_version CONSTANT NUMBER := 1.0;
1469 l_debug_level NUMBER;
1470
1471 l_processing_status csi_t_transaction_lines.processing_status%TYPE;
1472 l_return_status varchar2(1);
1473 l_pa_rec csi_t_party_accounts%rowtype;
1474
1475 CURSOR pa_cur (p_acct_dtl_id in number) IS
1476 SELECT *
1477 FROM csi_t_party_accounts
1478 where txn_account_detail_id = p_acct_dtl_id;
1479
1480 BEGIN
1481
1482 -- Standard Start of API savepoint
1483 SAVEPOINT update_txn_pty_acct_dtls;
1484
1485 -- Initialize message list if p_init_msg_list is set to TRUE.
1486 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
1487 FND_MSG_PUB.initialize;
1488 END IF;
1489
1490 -- Initialize API return status to success
1491 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1492
1493 -- Standard call to check for call compatibility.
1494 IF NOT
1495
1496 fnd_api.Compatible_API_Call (
1497 p_current_version_number => l_api_version,
1498 p_caller_version_number => p_api_version,
1499 p_api_name => l_api_name,
1500 p_pkg_name => G_PKG_NAME) THEN
1501
1502 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
1503
1504 END IF;
1505
1506 -- debug messages
1507 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1508
1509 csi_t_gen_utility_pvt.dump_api_info(
1510 p_pkg_name => g_pkg_name,
1511 p_api_name => l_api_name);
1512
1513 csi_t_gen_utility_pvt.add(
1514 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
1515
1516 -- Main API code
1517
1518 IF p_txn_pty_acct_detail_tbl.COUNT > 0 THEN
1519 FOR l_ind IN p_txn_pty_acct_detail_tbl.FIRST ..
1520 p_txn_pty_acct_detail_tbl.LAST
1521 LOOP
1522
1523 IF l_debug_level > 1 THEN
1524 csi_t_gen_utility_pvt.dump_pty_acct_rec(
1525 p_pty_acct_rec => p_txn_pty_acct_detail_tbl(l_ind));
1526 END IF;
1527
1528 l_pa_rec.txn_account_detail_id :=
1529 p_txn_pty_acct_detail_tbl(l_ind).txn_account_detail_id;
1530
1531 csi_t_vldn_routines_pvt.check_reqd_param(
1532 p_value => l_pa_rec.txn_account_detail_id,
1533 p_param_name => 'l_pa_rec.txn_account_detail_id',
1534 p_api_name => l_api_name);
1535
1536 -- validate party_detail_id
1537 csi_t_vldn_routines_pvt.validate_txn_acct_detail_id(
1538 p_txn_acct_detail_id => l_pa_rec.txn_account_detail_id,
1539 x_return_status => l_return_status);
1540
1541 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1542
1543 FND_MESSAGE.set_name('CSI','CSI_TXN_PARTY_ACCT_ID_INVALID');
1544 FND_MESSAGE.set_token('PTY_DTL_ID',l_pa_rec.txn_account_detail_id);
1545 FND_MSG_PUB.add;
1546 RAISE fnd_api.g_exc_error;
1547
1548 END IF;
1549
1550 FOR l_pa_cur_rec in pa_cur (l_pa_rec.txn_account_detail_id)
1551 LOOP
1552
1553 l_pa_rec.txn_party_detail_id := l_pa_cur_rec.txn_party_detail_id;
1554
1555 csi_t_vldn_routines_pvt.get_processing_status(
1556 p_level => 'PARTY_ACCT',
1557 p_level_dtl_id => l_pa_rec.txn_party_detail_id,
1558 x_processing_status => l_processing_status,
1559 x_return_status => l_return_status);
1560
1561 IF l_processing_status = 'PROCESSED' THEN
1562
1563 FND_MESSAGE.set_name('CSI','CSI_TXN_UPD_DEL_NOT_ALLOWED');
1564 FND_MESSAGE.set_token('LVL_ID', l_pa_rec.txn_account_detail_id);
1565 FND_MESSAGE.set_token('STATUS',l_processing_status);
1566 FND_MSG_PUB.add;
1567 RAISE fnd_api.g_exc_error;
1568
1569 END IF;
1570
1571 l_pa_rec.ip_account_id :=
1572 p_txn_pty_acct_detail_tbl(l_ind).ip_account_id;
1573
1574 IF nvl(l_pa_rec.ip_account_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1575 THEN
1576
1577 --validate ip_account_id
1578 IF NOT csi_instance_parties_vld_pvt.is_ip_account_valid(
1579 p_ip_account_id => l_pa_rec.ip_account_id) THEN
1580 RAISE fnd_api.g_exc_error;
1581 csi_t_gen_utility_pvt.add('Validate ip account id failed.');
1582 END IF;
1583
1584 END IF;
1585
1586 l_pa_rec.account_id :=
1587 p_txn_pty_acct_detail_tbl(l_ind).account_id;
1588
1589 IF l_pa_rec.account_id <> fnd_api.g_miss_num THEN
1590
1591 -- validate account_id from hz_cust_accounts table
1592 csi_t_vldn_routines_pvt.validate_account_id(
1593 p_account_id => l_pa_rec.account_id,
1594 x_return_status => l_return_status);
1595
1596 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1597
1598 FND_MESSAGE.set_name('CSI','CSI_TXN_ACCOUNT_ID_INVALID');
1599 FND_MESSAGE.set_token('ACCT_ID',l_pa_rec.account_id);
1600 FND_MSG_PUB.add;
1601 RAISE fnd_api.g_exc_error;
1602
1603 END IF;
1604
1605 END IF;
1606
1607 l_pa_rec.relationship_type_code :=
1608 p_txn_pty_acct_detail_tbl(l_ind).relationship_type_code;
1609
1610 IF l_pa_rec.relationship_type_code <> fnd_api.g_miss_char THEN
1611 -- validate relationship_type_code
1612 IF NOT
1613 csi_instance_parties_vld_pvt.is_pty_rel_type_valid(
1614 p_party_rel_type_code => l_pa_rec.relationship_type_code,
1615 p_contact_flag => 'A')
1616 THEN
1617 csi_t_gen_utility_pvt.add('Validate party relationship type code failed.');
1618 RAISE fnd_api.g_exc_error;
1619 END IF;
1620
1621 END IF;
1622
1623 l_pa_rec.bill_to_address_id := p_txn_pty_acct_detail_tbl(l_ind).bill_to_address_id;
1624 l_pa_rec.ship_to_address_id := p_txn_pty_acct_detail_tbl(l_ind).ship_to_address_id;
1625 l_pa_rec.active_start_date := p_txn_pty_acct_detail_tbl(l_ind).active_start_date;
1626 l_pa_rec.active_end_date := p_txn_pty_acct_detail_tbl(l_ind).active_end_date;
1627 l_pa_rec.preserve_detail_flag := p_txn_pty_acct_detail_tbl(l_ind).preserve_detail_flag;
1628 l_pa_rec.attribute1 := p_txn_pty_acct_detail_tbl(l_ind).attribute1;
1629 l_pa_rec.attribute2 := p_txn_pty_acct_detail_tbl(l_ind).attribute2;
1630 l_pa_rec.attribute3 := p_txn_pty_acct_detail_tbl(l_ind).attribute3;
1631 l_pa_rec.attribute4 := p_txn_pty_acct_detail_tbl(l_ind).attribute4;
1632 l_pa_rec.attribute5 := p_txn_pty_acct_detail_tbl(l_ind).attribute5;
1633 l_pa_rec.attribute6 := p_txn_pty_acct_detail_tbl(l_ind).attribute6;
1634 l_pa_rec.attribute7 := p_txn_pty_acct_detail_tbl(l_ind).attribute7;
1635 l_pa_rec.attribute8 := p_txn_pty_acct_detail_tbl(l_ind).attribute8;
1636 l_pa_rec.attribute9 := p_txn_pty_acct_detail_tbl(l_ind).attribute9;
1637 l_pa_rec.attribute10 := p_txn_pty_acct_detail_tbl(l_ind).attribute10;
1638 l_pa_rec.attribute11 := p_txn_pty_acct_detail_tbl(l_ind).attribute11;
1639 l_pa_rec.attribute12 := p_txn_pty_acct_detail_tbl(l_ind).attribute12;
1640 l_pa_rec.attribute13 := p_txn_pty_acct_detail_tbl(l_ind).attribute13;
1641 l_pa_rec.attribute14 := p_txn_pty_acct_detail_tbl(l_ind).attribute14;
1642 l_pa_rec.attribute15 := p_txn_pty_acct_detail_tbl(l_ind).attribute15;
1643 l_pa_rec.created_by := l_pa_cur_rec.created_by;
1644 l_pa_rec.creation_date := l_pa_cur_rec.creation_date;
1645 l_pa_rec.last_updated_by := g_user_id;
1646 l_pa_rec.last_update_date := sysdate;
1647 l_pa_rec.last_update_login := g_login_id;
1648
1649 l_pa_rec.object_version_number :=
1650 p_txn_pty_acct_detail_tbl(l_ind).object_version_number;
1651
1652 l_pa_rec.context :=
1653 p_txn_pty_acct_detail_tbl(l_ind).context;
1654
1655 begin
1656
1657 csi_t_gen_utility_pvt.dump_api_info(
1658 p_api_name => 'update_row',
1659 p_pkg_name => 'csi_t_party_details_pkg');
1660
1661 csi_t_party_accounts_pkg.update_row (
1662 p_txn_account_detail_id => l_pa_rec.txn_account_detail_id,
1663 p_txn_party_detail_id => l_pa_rec.txn_party_detail_id,
1664 p_ip_account_id => l_pa_rec.ip_account_id,
1665 p_account_id => l_pa_rec.account_id,
1666 p_relationship_type_code => l_pa_rec.relationship_type_code,
1667 p_bill_to_address_id => l_pa_rec.bill_to_address_id,
1668 p_ship_to_address_id => l_pa_rec.ship_to_address_id,
1669 p_active_start_date => l_pa_rec.active_start_date,
1670 p_active_end_date => l_pa_rec.active_end_date,
1671 p_preserve_detail_flag => l_pa_rec.preserve_detail_flag,
1672 p_attribute1 => l_pa_rec.attribute1,
1673 p_attribute2 => l_pa_rec.attribute2,
1674 p_attribute3 => l_pa_rec.attribute3,
1675 p_attribute4 => l_pa_rec.attribute4,
1676 p_attribute5 => l_pa_rec.attribute5,
1677 p_attribute6 => l_pa_rec.attribute6,
1678 p_attribute7 => l_pa_rec.attribute7,
1679 p_attribute8 => l_pa_rec.attribute8,
1680 p_attribute9 => l_pa_rec.attribute9,
1681 p_attribute10 => l_pa_rec.attribute10,
1682 p_attribute11 => l_pa_rec.attribute11,
1683 p_attribute12 => l_pa_rec.attribute12,
1684 p_attribute13 => l_pa_rec.attribute13,
1685 p_attribute14 => l_pa_rec.attribute14,
1686 p_attribute15 => l_pa_rec.attribute15,
1687 p_created_by => l_pa_rec.created_by,
1688 p_creation_date => l_pa_rec.creation_date,
1689 p_last_updated_by => l_pa_rec.last_updated_by,
1690 p_last_update_date => l_pa_rec.last_update_date,
1691 p_last_update_login => l_pa_rec.last_update_login,
1692 p_object_version_number => l_pa_rec.object_version_number,
1693 p_context => l_pa_rec.context);
1694
1695 exception
1696 when others then
1697 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1698 fnd_message.set_token('MESSAGE',
1699 'csi_t_party_accounts_pkg.update_row Failed. '||substr(sqlerrm,1,200));
1700 fnd_msg_pub.add;
1701 raise fnd_api.g_exc_error;
1702 end;
1703
1704 END LOOP;
1705
1706 END LOOP;
1707
1708 END IF;
1709
1710 -- Standard check of p_commit.
1711 IF fnd_api.To_Boolean( p_commit ) THEN
1712 COMMIT WORK;
1713 END IF;
1714
1715 -- Standard call to get message count and if count is get message info.
1716 FND_MSG_PUB.Count_And_Get(
1717 p_count => x_msg_count,
1718 p_data => x_msg_data);
1719
1720 EXCEPTION
1721 WHEN fnd_api.G_EXC_ERROR THEN
1722
1723 ROLLBACK TO update_txn_pty_acct_dtls;
1724 x_return_status := fnd_api.G_RET_STS_ERROR ;
1725 FND_MSG_PUB.Count_And_Get (
1726 p_count => x_msg_count,
1727 p_data => x_msg_data);
1728
1729 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1730
1731 ROLLBACK TO update_txn_pty_acct_dtls;
1732 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1733
1734 FND_MSG_PUB.Count_And_Get(
1735 p_count => x_msg_count,
1736 p_data => x_msg_data);
1737
1738 WHEN OTHERS THEN
1739
1740
1741 ROLLBACK TO update_txn_pty_acct_dtls;
1742 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1743
1744 IF FND_MSG_PUB.Check_Msg_Level(
1745 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1746
1747 FND_MSG_PUB.Add_Exc_Msg(
1748 p_pkg_name => G_PKG_NAME,
1749 p_procedure_name => l_api_name);
1750
1751 END IF;
1752
1753 FND_MSG_PUB.Count_And_Get(
1754 p_count => x_msg_count,
1755 p_data => x_msg_data);
1756 END update_txn_pty_acct_dtls;
1757
1758 PROCEDURE delete_txn_party_dtls(
1759 p_api_version IN NUMBER
1760 ,p_commit IN VARCHAR2 := fnd_api.g_false
1761 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1762 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1763 ,p_txn_party_ids_tbl IN csi_t_datastructures_grp.txn_party_ids_tbl
1764 ,x_txn_pty_acct_ids_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_ids_tbl
1765 ,x_return_status OUT NOCOPY VARCHAR2
1766 ,x_msg_count OUT NOCOPY NUMBER
1767 ,x_msg_data OUT NOCOPY VARCHAR2)
1768 IS
1769
1770 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_party_dtls';
1771 l_api_version CONSTANT NUMBER := 1.0;
1772 l_debug_level NUMBER;
1773
1774 l_line_dtl_id NUMBER;
1775 l_pty_dtl_id NUMBER;
1776 l_pty_acc_ind NUMBER;
1777
1778 l_return_status VARCHAR2(1);
1779 l_msg_count NUMBER;
1780 l_msg_data VARCHAR2(2000);
1781
1782 CURSOR pty_acc_cur(p_txn_party_detail_id in number) IS
1783 SELECT txn_account_detail_id
1784 FROM csi_t_party_accounts
1785 WHERE txn_party_detail_id = p_txn_party_detail_id;
1786
1787 CURSOR pty_cur (p_line_dtl_id IN NUMBER) IS
1788 SELECT txn_party_detail_id
1789 FROM csi_t_party_details
1790 WHERE txn_line_detail_id = p_line_dtl_id;
1791
1792 CURSOR cont_pty_cur(p_txn_party_detail_id in number) IS
1793 SELECT txn_party_detail_id
1794 FROM csi_t_party_details
1795 WHERE contact_party_id = p_txn_party_detail_id
1796 AND contact_flag = 'Y';
1797
1798 BEGIN
1799
1800 -- Standard Start of API savepoint
1801 SAVEPOINT delete_txn_party_dtls;
1802
1803 -- Initialize message list if p_init_msg_list is set to TRUE.
1804 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
1805 FND_MSG_PUB.initialize;
1806 END IF;
1807
1808 -- Initialize API return status to success
1809 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1810
1811 -- Standard call to check for call compatibility.
1812 IF NOT
1813
1814 fnd_api.Compatible_API_Call (
1815 p_current_version_number => l_api_version,
1816 p_caller_version_number => p_api_version,
1817 p_api_name => l_api_name,
1818 p_pkg_name => G_PKG_NAME) THEN
1819
1820 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
1821
1822 END IF;
1823
1824 l_pty_acc_ind := 0;
1825
1826 -- debug messages
1827 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1828
1829 csi_t_gen_utility_pvt.dump_api_info(
1830 p_pkg_name => g_pkg_name,
1831 p_api_name => l_api_name);
1832
1833 csi_t_gen_utility_pvt.add(
1834 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
1835
1836 -- Main API code
1837 IF p_txn_party_ids_tbl.COUNT > 0 THEN
1838
1839 FOR l_ind IN p_txn_party_ids_tbl.FIRST..p_txn_party_ids_tbl.LAST
1840 LOOP
1841
1842 l_pty_dtl_id := p_txn_party_ids_tbl(l_ind).txn_party_detail_id;
1843
1844 IF nvl(l_pty_dtl_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1845
1846 -- validate the transaction party detail ID
1847 csi_t_vldn_routines_pvt.validate_txn_party_detail_id(
1848 p_txn_party_detail_id => l_pty_dtl_id,
1849 x_return_status => l_return_status);
1850
1851 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1852
1853 FND_MESSAGE.set_name('CSI','CSI_TXN_PARTY_DTL_ID_INVALID');
1854 FND_MESSAGE.set_token('PTY_DTL_ID',l_pty_dtl_id);
1855 FND_MSG_PUB.add;
1856 RAISE fnd_api.g_exc_error;
1857
1858 END IF;
1859
1860 -- populate the party account table
1861 FOR pty_acc_rec in pty_acc_cur(l_pty_dtl_id)
1862 LOOP
1863
1864 l_pty_acc_ind := l_pty_acc_ind + 1;
1865
1866 x_txn_pty_acct_ids_tbl(l_pty_acc_ind).txn_party_detail_id
1867 := l_pty_dtl_id;
1868 x_txn_pty_acct_ids_tbl(l_pty_acc_ind).txn_account_detail_id
1869 := pty_acc_rec.txn_account_detail_id;
1870
1871 END LOOP;
1872
1873 csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
1874 p_api_version => p_api_version,
1875 p_commit => p_commit,
1876 p_init_msg_list => p_init_msg_list,
1877 p_validation_level => p_validation_level,
1878 p_txn_pty_acct_ids_tbl => x_txn_pty_acct_ids_tbl,
1879 x_return_status => l_return_status,
1880 x_msg_count => l_msg_count,
1881 x_msg_data => l_msg_data);
1882
1883 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1884 RAISE fnd_api.g_exc_error;
1885 END IF;
1886
1887 -- If the party deleted has contacts, delete them too
1888 FOR cont_pty_rec in cont_pty_cur(l_pty_dtl_id)
1889 LOOP
1890
1891 debug('Deleting Party Contact rec: '||cont_pty_rec.txn_party_detail_id);
1892 csi_t_gen_utility_pvt.dump_api_info(
1893 p_api_name => 'delete_row',
1894 p_pkg_name => 'csi_t_party_details_pkg');
1895
1896 csi_t_party_details_pkg.delete_row(
1897 p_txn_party_detail_id => cont_pty_rec.txn_party_detail_id);
1898
1899 END LOOP;
1900
1901 csi_t_gen_utility_pvt.dump_api_info(
1902 p_api_name => 'delete_row',
1903 p_pkg_name => 'csi_t_party_details_pkg');
1904
1905 csi_t_party_details_pkg.delete_row(
1906 p_txn_party_detail_id => l_pty_dtl_id);
1907
1908 ELSE
1909
1910 l_line_dtl_id := p_txn_party_ids_tbl(l_ind).txn_line_detail_id;
1911
1912 csi_t_vldn_routines_pvt.check_reqd_param(
1913 p_value => l_line_dtl_id,
1914 p_param_name => 'p_txn_party_ids_tbl.txn_line_detail_id',
1915 p_api_name => l_api_name);
1916
1917 -- validate txn_line_detail_id
1918 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
1919 p_txn_line_detail_id => l_line_dtl_id,
1920 x_return_status => l_return_status);
1921
1922 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1923
1924 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_DTL_ID_INVALID');
1925 FND_MESSAGE.set_token('LINE_DTL_ID',l_line_dtl_id);
1926 FND_MSG_PUB.add;
1927 RAISE fnd_api.g_exc_error;
1928
1929 END IF;
1930
1931 FOR pty_rec in pty_cur (l_line_dtl_id)
1932 LOOP
1933
1934 l_pty_dtl_id := pty_rec.txn_party_detail_id;
1935
1936 FOR pty_acc_rec in pty_acc_cur(l_pty_dtl_id)
1937 LOOP
1938
1939 l_pty_acc_ind := l_pty_acc_ind + 1;
1940
1941 x_txn_pty_acct_ids_tbl(l_pty_acc_ind).txn_party_detail_id
1942 := l_pty_dtl_id;
1943 x_txn_pty_acct_ids_tbl(l_pty_acc_ind).txn_account_detail_id
1944 := pty_acc_rec.txn_account_detail_id;
1945
1946 END LOOP;
1947
1948 csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
1949 p_api_version => p_api_version,
1950 p_commit => p_commit,
1951 p_init_msg_list => p_init_msg_list,
1952 p_validation_level => p_validation_level,
1953 p_txn_pty_acct_ids_tbl => x_txn_pty_acct_ids_tbl,
1954 x_return_status => l_return_status,
1955 x_msg_count => l_msg_count,
1956 x_msg_data => l_msg_data);
1957
1958 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1959 RAISE fnd_api.g_exc_error;
1960 END IF;
1961 -- If the party deleted has contacts, delete them too
1962 FOR cont_pty_rec in cont_pty_cur(l_pty_dtl_id)
1963 LOOP
1964
1965 debug('Deleting Party Contact rec: '||cont_pty_rec.txn_party_detail_id);
1966 csi_t_gen_utility_pvt.dump_api_info(
1967 p_api_name => 'delete_row',
1968 p_pkg_name => 'csi_t_party_details_pkg');
1969
1970 csi_t_party_details_pkg.delete_row(
1971 p_txn_party_detail_id => cont_pty_rec.txn_party_detail_id);
1972
1973 END LOOP;
1974
1975 csi_t_gen_utility_pvt.dump_api_info(
1976 p_api_name => 'delete_row',
1977 p_pkg_name => 'csi_t_party_details_pkg');
1978
1979 csi_t_party_details_pkg.delete_row(
1980 p_txn_party_detail_id => l_pty_dtl_id);
1981
1982 END LOOP;
1983 END IF;
1984 END LOOP;
1985 END IF;
1986
1987 -- Standard check of p_commit.
1988 IF fnd_api.To_Boolean( p_commit ) THEN
1989 COMMIT WORK;
1990 END IF;
1991
1992 -- Standard call to get message count and if count is get message info.
1993 FND_MSG_PUB.Count_And_Get(
1994 p_count => x_msg_count,
1995 p_data => x_msg_data);
1996
1997 EXCEPTION
1998 WHEN fnd_api.G_EXC_ERROR THEN
1999
2000 ROLLBACK TO delete_txn_party_dtls;
2001 x_return_status := fnd_api.G_RET_STS_ERROR ;
2002 FND_MSG_PUB.Count_And_Get (
2003 p_count => x_msg_count,
2004 p_data => x_msg_data);
2005
2006 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2007
2008 ROLLBACK TO delete_txn_party_dtls;
2009 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2010
2011 FND_MSG_PUB.Count_And_Get(
2012 p_count => x_msg_count,
2013 p_data => x_msg_data);
2014
2015 WHEN OTHERS THEN
2016
2017 ROLLBACK TO delete_txn_party_dtls;
2018 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2019
2020 IF FND_MSG_PUB.Check_Msg_Level(
2021 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2022
2023 FND_MSG_PUB.Add_Exc_Msg(
2024 p_pkg_name => G_PKG_NAME,
2025 p_procedure_name => l_api_name);
2026
2027 END IF;
2028
2029 FND_MSG_PUB.Count_And_Get(
2030 p_count => x_msg_count,
2031 p_data => x_msg_data);
2032
2033 END delete_txn_party_dtls;
2034
2035 /* deletes the party accounts based on the ids passed */
2036 PROCEDURE delete_txn_pty_acct_dtls(
2037 p_api_version IN NUMBER
2038 ,p_commit IN VARCHAR2 := fnd_api.g_false
2039 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2040 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2041 ,p_txn_pty_acct_ids_tbl IN csi_t_datastructures_grp.txn_pty_acct_ids_tbl
2042 ,x_return_status OUT NOCOPY VARCHAR2
2043 ,x_msg_count OUT NOCOPY NUMBER
2044 ,x_msg_data OUT NOCOPY VARCHAR2)
2045 IS
2046
2047 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_pty_acct_dtls';
2048 l_api_version CONSTANT NUMBER := 1.0;
2049 l_debug_level NUMBER;
2050 l_return_status VARCHAR2(1);
2051
2052 l_acct_dtl_id NUMBER;
2053 l_pty_dtl_id NUMBER;
2054
2055 CURSOR pty_acc_cur (p_pty_dtl_id IN NUMBER) IS
2056 SELECT txn_account_detail_id
2057 FROM csi_t_party_accounts
2058 WHERE txn_party_detail_id = p_pty_dtl_id;
2059
2060 BEGIN
2061
2062 -- Standard Start of API savepoint
2063 SAVEPOINT delete_txn_pty_acct_dtls;
2064
2065 -- Initialize message list if p_init_msg_list is set to TRUE.
2066 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
2067 FND_MSG_PUB.initialize;
2068 END IF;
2069
2070 -- Initialize API return status to success
2071 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2072
2073 -- Standard call to check for call compatibility.
2074 IF NOT
2075
2076 fnd_api.Compatible_API_Call (
2077 p_current_version_number => l_api_version,
2078 p_caller_version_number => p_api_version,
2079 p_api_name => l_api_name,
2080 p_pkg_name => G_PKG_NAME) THEN
2081
2082 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
2083
2084 END IF;
2085
2086 -- debug messages
2087 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
2088
2089 csi_t_gen_utility_pvt.dump_api_info(
2090 p_pkg_name => g_pkg_name,
2091 p_api_name => l_api_name);
2092
2093 csi_t_gen_utility_pvt.add(
2094 p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
2095
2096 -- Main API code
2097 IF p_txn_pty_acct_ids_tbl.COUNT > 0 THEN
2098 FOR l_ind IN p_txn_pty_acct_ids_tbl.FIRST ..
2099 p_txn_pty_acct_ids_tbl.LAST
2100 LOOP
2101
2102 l_acct_dtl_id := p_txn_pty_acct_ids_tbl(l_ind).txn_account_detail_id;
2103
2104 IF nvl(l_acct_dtl_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2105 THEN
2106
2107 csi_t_vldn_routines_pvt.validate_txn_acct_detail_id(
2108 p_txn_acct_detail_id => l_acct_dtl_id,
2109 x_return_status => l_return_status);
2110
2111 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2112
2113 FND_MESSAGE.set_name('CSI','CSI_TXN_PARTY_ACCT_ID_INVALID');
2114 FND_MESSAGE.set_token('PTY_ACCT_ID',l_acct_dtl_id);
2115 FND_MSG_PUB.add;
2116 RAISE fnd_api.g_exc_error;
2117
2118 END IF;
2119
2120 csi_t_gen_utility_pvt.dump_api_info(
2121 p_api_name => 'delete_row',
2122 p_pkg_name => 'csi_t_party_accounts_pkg');
2123
2124 csi_t_party_accounts_pkg.delete_row(
2125 p_txn_account_detail_id => l_acct_dtl_id);
2126
2127 ELSE
2128
2129 l_pty_dtl_id := p_txn_pty_acct_ids_tbl(l_ind).txn_party_detail_id;
2130
2131 -- validate txn_party_detail_id
2132 csi_t_vldn_routines_pvt.validate_txn_party_detail_id(
2133 p_txn_party_detail_id => l_pty_dtl_id,
2134 x_return_status => l_return_status);
2135
2136 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2137
2138 FND_MESSAGE.set_name('CSI','CSI_TXN_PARTY_DTL_ID_INVALID');
2139 FND_MESSAGE.set_token('PTY_DTL_ID',l_pty_dtl_id);
2140 FND_MSG_PUB.add;
2141 RAISE fnd_api.g_exc_error;
2142
2143 END IF;
2144
2145 FOR pty_acc_rec in pty_acc_cur (l_pty_dtl_id)
2146 LOOP
2147
2148 csi_t_gen_utility_pvt.dump_api_info(
2149 p_api_name => 'delete_row',
2150 p_pkg_name => 'csi_t_party_accounts_pkg');
2151
2152 csi_t_party_accounts_pkg.delete_row(
2153 p_txn_account_detail_id => pty_acc_rec.txn_account_detail_id);
2154
2155 END LOOP;
2156
2157 END IF;
2158
2159 END LOOP;
2160 END IF;
2161
2162 -- Standard check of p_commit.
2163 IF fnd_api.To_Boolean( p_commit ) THEN
2164 COMMIT WORK;
2165 END IF;
2166
2167 -- Standard call to get message count and if count is get message info.
2168 FND_MSG_PUB.Count_And_Get(
2169 p_count => x_msg_count,
2170 p_data => x_msg_data);
2171
2172 EXCEPTION
2173 WHEN fnd_api.G_EXC_ERROR THEN
2174
2175 ROLLBACK TO delete_txn_pty_acct_dtls;
2176 x_return_status := fnd_api.G_RET_STS_ERROR ;
2177 FND_MSG_PUB.Count_And_Get (
2178 p_count => x_msg_count,
2179 p_data => x_msg_data);
2180
2181 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2182
2183 ROLLBACK TO delete_txn_pty_acct_dtls;
2184 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2185
2186 FND_MSG_PUB.Count_And_Get(
2187 p_count => x_msg_count,
2188 p_data => x_msg_data);
2189
2190 WHEN OTHERS THEN
2191
2192 ROLLBACK TO delete_txn_pty_acct_dtls;
2193 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2194
2195 IF FND_MSG_PUB.Check_Msg_Level(
2196 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2197
2198 FND_MSG_PUB.Add_Exc_Msg(
2199 p_pkg_name => G_PKG_NAME,
2200 p_procedure_name => l_api_name);
2201
2202 END IF;
2203
2204 FND_MSG_PUB.Count_And_Get(
2205 p_count => x_msg_count,
2206 p_data => x_msg_data);
2207
2208 END delete_txn_pty_acct_dtls;
2209
2210 PROCEDURE get_pty_acct_dtls(
2211 p_party_dtl_id in number,
2212 x_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
2213 x_return_status OUT NOCOPY varchar2)
2214 IS
2215
2216 l_select_stmt varchar2(2000);
2217 l_pty_acct_cur_id integer;
2218 l_pty_acct_rec csi_t_datastructures_grp.txn_pty_acct_detail_rec;
2219 l_processed_rows number := 0;
2220 l_ind binary_integer;
2221
2222 BEGIN
2223
2224 x_return_status := fnd_api.g_ret_sts_success;
2225
2226 api_log('get_pty_acct_dtls');
2227
2228 l_select_stmt :=
2229 'select txn_account_detail_id, txn_party_detail_id, ip_account_id, '||
2230 ' account_id, relationship_type_code, bill_to_address_id, ship_to_address_id, '||
2231 ' active_start_date, active_end_date, preserve_detail_flag, context, '||
2232 ' attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, '||
2233 ' attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, '||
2234 ' attribute13, attribute14, attribute15, object_version_number '||
2235 'from csi_t_party_accounts '||
2236 'where txn_party_detail_id = :party_dtl_id';
2237
2238 l_pty_acct_cur_id := dbms_sql.open_cursor;
2239
2240 dbms_sql.parse(l_pty_acct_cur_id, l_select_stmt , dbms_sql.native);
2241
2242 dbms_sql.bind_variable(l_pty_acct_cur_id, 'party_dtl_id', p_party_dtl_id);
2243
2244 dbms_sql.define_column(l_pty_acct_cur_id,1,
2245 l_pty_acct_rec.txn_account_detail_id);
2246 dbms_sql.define_column(l_pty_acct_cur_id,2,
2247 l_pty_acct_rec.txn_party_detail_id);
2248 dbms_sql.define_column(l_pty_acct_cur_id,3,
2249 l_pty_acct_rec.ip_account_id);
2250 dbms_sql.define_column(l_pty_acct_cur_id,4,
2251 l_pty_acct_rec.account_id);
2252 dbms_sql.define_column(l_pty_acct_cur_id,5,
2253 l_pty_acct_rec.relationship_type_code,30);
2254 dbms_sql.define_column(l_pty_acct_cur_id,6,
2255 l_pty_acct_rec.bill_to_address_id);
2256 dbms_sql.define_column(l_pty_acct_cur_id,7,
2257 l_pty_acct_rec.ship_to_address_id);
2258 dbms_sql.define_column(l_pty_acct_cur_id,8,
2259 l_pty_acct_rec.active_start_date);
2260 dbms_sql.define_column(l_pty_acct_cur_id,9,
2261 l_pty_acct_rec.active_end_date);
2262 dbms_sql.define_column(l_pty_acct_cur_id,10,
2263 l_pty_acct_rec.preserve_detail_flag, 1);
2264 dbms_sql.define_column(l_pty_acct_cur_id,11,
2265 l_pty_acct_rec.context,30);
2266 dbms_sql.define_column(l_pty_acct_cur_id,12,
2267 l_pty_acct_rec.attribute1,150);
2268 dbms_sql.define_column(l_pty_acct_cur_id,13,
2269 l_pty_acct_rec.attribute2,150);
2270 dbms_sql.define_column(l_pty_acct_cur_id,14,
2271 l_pty_acct_rec.attribute3,150);
2272 dbms_sql.define_column(l_pty_acct_cur_id,15,
2273 l_pty_acct_rec.attribute4,150);
2274 dbms_sql.define_column(l_pty_acct_cur_id,16,
2275 l_pty_acct_rec.attribute5,150);
2276 dbms_sql.define_column(l_pty_acct_cur_id,17,
2277 l_pty_acct_rec.attribute6,150);
2278 dbms_sql.define_column(l_pty_acct_cur_id,18,
2279 l_pty_acct_rec.attribute7,150);
2280 dbms_sql.define_column(l_pty_acct_cur_id,19,
2281 l_pty_acct_rec.attribute8,150);
2282 dbms_sql.define_column(l_pty_acct_cur_id,20,
2283 l_pty_acct_rec.attribute9,150);
2284 dbms_sql.define_column(l_pty_acct_cur_id,21,
2285 l_pty_acct_rec.attribute10,150);
2286 dbms_sql.define_column(l_pty_acct_cur_id,22,
2287 l_pty_acct_rec.attribute11,150);
2288 dbms_sql.define_column(l_pty_acct_cur_id,23,
2289 l_pty_acct_rec.attribute12,150);
2290 dbms_sql.define_column(l_pty_acct_cur_id,24,
2291 l_pty_acct_rec.attribute13,150);
2292 dbms_sql.define_column(l_pty_acct_cur_id,25,
2293 l_pty_acct_rec.attribute14,150);
2294 dbms_sql.define_column(l_pty_acct_cur_id,26,
2295 l_pty_acct_rec.attribute15,150);
2296 dbms_sql.define_column(l_pty_acct_cur_id,27,
2297 l_pty_acct_rec.object_version_number);
2298
2299 l_ind := 0;
2300
2301 l_processed_rows := dbms_sql.execute(l_pty_acct_cur_id);
2302 LOOP
2303 exit when dbms_sql.fetch_rows(l_pty_acct_cur_id) = 0;
2304
2305 l_ind := l_ind + 1;
2306
2307 dbms_sql.column_value(l_pty_acct_cur_id,1,
2308 x_pty_acct_detail_tbl(l_ind).txn_account_detail_id);
2309 dbms_sql.column_value(l_pty_acct_cur_id,2,
2310 x_pty_acct_detail_tbl(l_ind).txn_party_detail_id);
2311 dbms_sql.column_value(l_pty_acct_cur_id,3,
2312 x_pty_acct_detail_tbl(l_ind).ip_account_id);
2313 dbms_sql.column_value(l_pty_acct_cur_id,4,
2314 x_pty_acct_detail_tbl(l_ind).account_id);
2315 dbms_sql.column_value(l_pty_acct_cur_id,5,
2316 x_pty_acct_detail_tbl(l_ind).relationship_type_code);
2317 dbms_sql.column_value(l_pty_acct_cur_id,6,
2318 x_pty_acct_detail_tbl(l_ind).bill_to_address_id);
2319 dbms_sql.column_value(l_pty_acct_cur_id,7,
2320 x_pty_acct_detail_tbl(l_ind).ship_to_address_id);
2321 dbms_sql.column_value(l_pty_acct_cur_id,8,
2322 x_pty_acct_detail_tbl(l_ind).active_start_date);
2323 dbms_sql.column_value(l_pty_acct_cur_id,9,
2324 x_pty_acct_detail_tbl(l_ind).active_end_date);
2325 dbms_sql.column_value(l_pty_acct_cur_id,10,
2326 x_pty_acct_detail_tbl(l_ind).preserve_detail_flag);
2327 dbms_sql.column_value(l_pty_acct_cur_id,11,
2328 x_pty_acct_detail_tbl(l_ind).context);
2329 dbms_sql.column_value(l_pty_acct_cur_id,12,
2330 x_pty_acct_detail_tbl(l_ind).attribute1);
2331 dbms_sql.column_value(l_pty_acct_cur_id,13,
2332 x_pty_acct_detail_tbl(l_ind).attribute2);
2333 dbms_sql.column_value(l_pty_acct_cur_id,14,
2334 x_pty_acct_detail_tbl(l_ind).attribute3);
2335 dbms_sql.column_value(l_pty_acct_cur_id,15,
2336 x_pty_acct_detail_tbl(l_ind).attribute4);
2337 dbms_sql.column_value(l_pty_acct_cur_id,16,
2338 x_pty_acct_detail_tbl(l_ind).attribute5);
2339 dbms_sql.column_value(l_pty_acct_cur_id,17,
2340 x_pty_acct_detail_tbl(l_ind).attribute6);
2341 dbms_sql.column_value(l_pty_acct_cur_id,18,
2342 x_pty_acct_detail_tbl(l_ind).attribute7);
2343 dbms_sql.column_value(l_pty_acct_cur_id,19,
2344 x_pty_acct_detail_tbl(l_ind).attribute8);
2345 dbms_sql.column_value(l_pty_acct_cur_id,20,
2346 x_pty_acct_detail_tbl(l_ind).attribute9);
2347 dbms_sql.column_value(l_pty_acct_cur_id,21,
2348 x_pty_acct_detail_tbl(l_ind).attribute10);
2349 dbms_sql.column_value(l_pty_acct_cur_id,22,
2350 x_pty_acct_detail_tbl(l_ind).attribute11);
2351 dbms_sql.column_value(l_pty_acct_cur_id,23,
2352 x_pty_acct_detail_tbl(l_ind).attribute12);
2353 dbms_sql.column_value(l_pty_acct_cur_id,24,
2354 x_pty_acct_detail_tbl(l_ind).attribute13);
2355 dbms_sql.column_value(l_pty_acct_cur_id,25,
2356 x_pty_acct_detail_tbl(l_ind).attribute14);
2357 dbms_sql.column_value(l_pty_acct_cur_id,26,
2358 x_pty_acct_detail_tbl(l_ind).attribute15);
2359 dbms_sql.column_value(l_pty_acct_cur_id,27,
2360 x_pty_acct_detail_tbl(l_ind).object_version_number);
2361
2362 END LOOP;
2363
2364 dbms_sql.close_cursor(l_pty_acct_cur_id);
2365
2366 EXCEPTION
2367 WHEN others THEN
2368
2369 IF dbms_sql.is_open(l_pty_acct_cur_id) THEN
2370 dbms_sql.close_cursor(l_pty_acct_cur_id);
2371 END IF;
2372 END get_pty_acct_dtls;
2373
2374 PROCEDURE get_party_dtls(
2375 p_line_dtl_id in number,
2376 x_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
2377 x_return_status OUT NOCOPY varchar2)
2378 IS
2379
2380 l_select_stmt varchar2(2000);
2381 l_pty_cur_id integer;
2382 l_pty_rec csi_t_datastructures_grp.txn_party_detail_rec;
2383 l_processed_rows number := 0;
2384 l_ind binary_integer;
2385
2386 BEGIN
2387
2388 x_return_status := fnd_api.g_ret_sts_success;
2389
2390 api_log('get_party_dtls');
2391
2392 l_select_stmt :=
2393 'select txn_party_detail_id, txn_line_detail_id, instance_party_id, '||
2394 ' party_source_table, party_source_id, relationship_type_code, contact_flag, '||
2395 ' contact_party_id, active_start_date, active_end_date, preserve_detail_flag, '||
2396 ' context, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, '||
2397 ' attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, '||
2398 ' attribute13, attribute14, attribute15, object_version_number, '||
2399 ' primary_flag, preferred_flag '||
2400 'from csi_t_party_details '||
2401 'where txn_line_detail_id = :line_dtl_id';
2402
2403 l_pty_cur_id := dbms_sql.open_cursor;
2404
2405 dbms_sql.parse(l_pty_cur_id, l_select_stmt , dbms_sql.native);
2406
2407 dbms_sql.bind_variable(l_pty_cur_id, 'line_dtl_id', p_line_dtl_id);
2408
2409 dbms_sql.define_column(l_pty_cur_id,1,l_pty_rec.txn_party_detail_id);
2410 dbms_sql.define_column(l_pty_cur_id,2,l_pty_rec.txn_line_detail_id);
2411 dbms_sql.define_column(l_pty_cur_id,3,l_pty_rec.instance_party_id);
2412 dbms_sql.define_column(l_pty_cur_id,4,l_pty_rec.party_source_table,30);
2413 dbms_sql.define_column(l_pty_cur_id,5,l_pty_rec.party_source_id);
2414 dbms_sql.define_column(l_pty_cur_id,6,l_pty_rec.relationship_type_code,30);
2415 dbms_sql.define_column(l_pty_cur_id,7,l_pty_rec.contact_flag,1);
2416 dbms_sql.define_column(l_pty_cur_id,8,l_pty_rec.contact_party_id);
2417 dbms_sql.define_column(l_pty_cur_id,9,l_pty_rec.active_start_date);
2418 dbms_sql.define_column(l_pty_cur_id,10,l_pty_rec.active_end_date);
2419 dbms_sql.define_column(l_pty_cur_id,11,l_pty_rec.preserve_detail_flag,1);
2420 dbms_sql.define_column(l_pty_cur_id,12,l_pty_rec.context,30);
2421 dbms_sql.define_column(l_pty_cur_id,13,l_pty_rec.attribute1,150);
2422 dbms_sql.define_column(l_pty_cur_id,14,l_pty_rec.attribute2,150);
2423 dbms_sql.define_column(l_pty_cur_id,15,l_pty_rec.attribute3,150);
2424 dbms_sql.define_column(l_pty_cur_id,16,l_pty_rec.attribute4,150);
2425 dbms_sql.define_column(l_pty_cur_id,17,l_pty_rec.attribute5,150);
2426 dbms_sql.define_column(l_pty_cur_id,18,l_pty_rec.attribute6,150);
2427 dbms_sql.define_column(l_pty_cur_id,19,l_pty_rec.attribute7,150);
2428 dbms_sql.define_column(l_pty_cur_id,20,l_pty_rec.attribute8,150);
2429 dbms_sql.define_column(l_pty_cur_id,21,l_pty_rec.attribute9,150);
2430 dbms_sql.define_column(l_pty_cur_id,22,l_pty_rec.attribute10,150);
2431 dbms_sql.define_column(l_pty_cur_id,23,l_pty_rec.attribute11,150);
2432 dbms_sql.define_column(l_pty_cur_id,24,l_pty_rec.attribute12,150);
2433 dbms_sql.define_column(l_pty_cur_id,25,l_pty_rec.attribute13,150);
2434 dbms_sql.define_column(l_pty_cur_id,26,l_pty_rec.attribute14,150);
2435 dbms_sql.define_column(l_pty_cur_id,27,l_pty_rec.attribute15,150);
2436 dbms_sql.define_column(l_pty_cur_id,28,l_pty_rec.object_version_number);
2437 dbms_sql.define_column(l_pty_cur_id,29,l_pty_rec.primary_flag,1);
2438 dbms_sql.define_column(l_pty_cur_id,30,l_pty_rec.preferred_flag,1);
2439
2440 l_ind := 0;
2441
2442 l_processed_rows := dbms_sql.execute(l_pty_cur_id);
2443 LOOP
2444 exit when dbms_sql.fetch_rows(l_pty_cur_id) = 0;
2445
2446 l_ind := l_ind + 1;
2447
2448 dbms_sql.column_value(l_pty_cur_id,1, x_party_detail_tbl(l_ind).txn_party_detail_id);
2449 dbms_sql.column_value(l_pty_cur_id,2, x_party_detail_tbl(l_ind).txn_line_detail_id);
2450 dbms_sql.column_value(l_pty_cur_id,3, x_party_detail_tbl(l_ind).instance_party_id);
2451 dbms_sql.column_value(l_pty_cur_id,4, x_party_detail_tbl(l_ind).party_source_table);
2452 dbms_sql.column_value(l_pty_cur_id,5, x_party_detail_tbl(l_ind).party_source_id);
2453 dbms_sql.column_value(l_pty_cur_id,6, x_party_detail_tbl(l_ind).relationship_type_code);
2454 dbms_sql.column_value(l_pty_cur_id,7, x_party_detail_tbl(l_ind).contact_flag);
2455 dbms_sql.column_value(l_pty_cur_id,8, x_party_detail_tbl(l_ind).contact_party_id);
2456 dbms_sql.column_value(l_pty_cur_id,9, x_party_detail_tbl(l_ind).active_start_date);
2457 dbms_sql.column_value(l_pty_cur_id,10, x_party_detail_tbl(l_ind).active_end_date);
2458 dbms_sql.column_value(l_pty_cur_id,11, x_party_detail_tbl(l_ind).preserve_detail_flag);
2459 dbms_sql.column_value(l_pty_cur_id,12, x_party_detail_tbl(l_ind).context);
2460 dbms_sql.column_value(l_pty_cur_id,13, x_party_detail_tbl(l_ind).attribute1);
2461 dbms_sql.column_value(l_pty_cur_id,14, x_party_detail_tbl(l_ind).attribute2);
2462 dbms_sql.column_value(l_pty_cur_id,15, x_party_detail_tbl(l_ind).attribute3);
2463 dbms_sql.column_value(l_pty_cur_id,16, x_party_detail_tbl(l_ind).attribute4);
2464 dbms_sql.column_value(l_pty_cur_id,17, x_party_detail_tbl(l_ind).attribute5);
2465 dbms_sql.column_value(l_pty_cur_id,18, x_party_detail_tbl(l_ind).attribute6);
2466 dbms_sql.column_value(l_pty_cur_id,19, x_party_detail_tbl(l_ind).attribute7);
2467 dbms_sql.column_value(l_pty_cur_id,20, x_party_detail_tbl(l_ind).attribute8);
2468 dbms_sql.column_value(l_pty_cur_id,21, x_party_detail_tbl(l_ind).attribute9);
2469 dbms_sql.column_value(l_pty_cur_id,22, x_party_detail_tbl(l_ind).attribute10);
2470 dbms_sql.column_value(l_pty_cur_id,23, x_party_detail_tbl(l_ind).attribute11);
2471 dbms_sql.column_value(l_pty_cur_id,24, x_party_detail_tbl(l_ind).attribute12);
2472 dbms_sql.column_value(l_pty_cur_id,25, x_party_detail_tbl(l_ind).attribute13);
2473 dbms_sql.column_value(l_pty_cur_id,26, x_party_detail_tbl(l_ind).attribute14);
2474 dbms_sql.column_value(l_pty_cur_id,27, x_party_detail_tbl(l_ind).attribute15);
2475 dbms_sql.column_value(l_pty_cur_id,28, x_party_detail_tbl(l_ind).object_version_number);
2476 dbms_sql.column_value(l_pty_cur_id,29, x_party_detail_tbl(l_ind).primary_flag);
2477 dbms_sql.column_value(l_pty_cur_id,30, x_party_detail_tbl(l_ind).preferred_flag);
2478
2479 END LOOP;
2480
2481 dbms_sql.close_cursor(l_pty_cur_id);
2482
2483 EXCEPTION
2484 WHEN fnd_api.g_exc_error THEN
2485 x_return_status := fnd_api.g_ret_sts_error;
2486 IF dbms_sql.is_open(l_pty_cur_id) THEN
2487 dbms_sql.close_cursor(l_pty_cur_id);
2488 END IF;
2489 WHEN others THEN
2490 x_return_status := fnd_api.g_ret_sts_unexp_error;
2491
2492 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2493 fnd_message.set_token('MESSAGE',substr(sqlerrm, 1, 255));
2494 fnd_msg_pub.add;
2495
2496 IF dbms_sql.is_open(l_pty_cur_id) THEN
2497 dbms_sql.close_cursor(l_pty_cur_id);
2498 END IF;
2499 END get_party_dtls;
2500
2501 PROCEDURE get_all_party_dtls(
2502 p_line_detail_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
2503 x_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
2504 x_return_status OUT NOCOPY varchar2)
2505 IS
2506 l_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2507 l_tmp_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2508 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2509 l_p_ind binary_integer := 0;
2510 BEGIN
2511 x_return_status := fnd_api.g_ret_sts_success;
2512 IF p_line_detail_tbl.count > 0 THEN
2513
2514 FOR l_ind in p_line_detail_tbl.FIRST .. p_line_detail_tbl.LAST
2515 LOOP
2516
2517 l_tmp_party_detail_tbl.delete;
2518 get_party_dtls(
2519 p_line_dtl_id => p_line_detail_tbl(l_ind).txn_line_detail_id,
2520 x_party_detail_tbl => l_tmp_party_detail_tbl,
2521 x_return_status => l_return_status);
2522
2523 IF l_tmp_party_detail_tbl.COUNT > 0 THEN
2524 FOR l_t_ind IN l_tmp_party_detail_tbl.FIRST .. l_tmp_party_detail_tbl.LAST
2525 LOOP
2526 l_p_ind := l_party_detail_tbl.count + 1;
2527 l_party_detail_tbl(l_p_ind) := l_tmp_party_detail_tbl(l_t_ind);
2528 END LOOP;
2529 END IF;
2530
2531 END LOOP;
2532 END IF;
2533 x_party_detail_tbl := l_party_detail_tbl;
2534 EXCEPTION
2535 WHEN fnd_api.g_exc_error THEN
2536 x_return_status := fnd_api.g_ret_sts_error;
2537 END get_all_party_dtls;
2538
2539 PROCEDURE get_all_pty_acct_dtls(
2540 p_party_detail_tbl in csi_t_datastructures_grp.txn_party_detail_tbl,
2541 x_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
2542 x_return_status OUT NOCOPY varchar2)
2543 IS
2544 l_pa_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2545 l_tmp_pa_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2546 l_pa_ind binary_integer := 0;
2547 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2548
2549 BEGIN
2550
2551 x_return_status := fnd_api.g_ret_sts_success;
2552 IF p_party_detail_tbl.count > 0 THEN
2553 FOR l_ind IN p_party_detail_tbl.FIRST .. p_party_detail_tbl.LAST
2554 LOOP
2555
2556 l_tmp_pa_tbl.delete;
2557
2558 get_pty_acct_dtls(
2559 p_party_dtl_id => p_party_detail_tbl(l_ind).txn_party_detail_id,
2560 x_pty_acct_detail_tbl => l_tmp_pa_tbl,
2561 x_return_status => l_return_status);
2562
2563 IF l_tmp_pa_tbl.count > 0 THEN
2564 FOR l_t_ind IN l_tmp_pa_tbl.FIRST .. l_tmp_pa_tbl.LAST
2565 LOOP
2566 l_pa_ind := l_pa_tbl.count + 1;
2567 l_pa_tbl(l_pa_ind) := l_tmp_pa_tbl(l_t_ind);
2568 END LOOP;
2569 END IF;
2570
2571 END LOOP;
2572
2573 END IF;
2574 x_pty_acct_detail_tbl := l_pa_tbl;
2575
2576 EXCEPTION
2577 WHEN fnd_api.g_exc_error THEN
2578 x_return_status := fnd_api.g_ret_sts_error;
2579 END get_all_pty_acct_dtls;
2580
2581 END csi_t_txn_parties_pvt;