1 PACKAGE BODY csi_party_relationships_pub AS
2 /* $Header: csipipb.pls 120.8 2007/10/22 21:57:37 fli ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSI_PARTY_RELATIONSHIPS_PUB';
5
6 /*------------------------------------------------------------*/
7 /* Procedure name: Get_inst_party_relationships */
8 /* Description : Procedure used to get party relationships */
9 /* for an item instance */
10 /*------------------------------------------------------------*/
11
12 PROCEDURE get_inst_party_relationships
13 ( p_api_version IN NUMBER
14 ,p_commit IN VARCHAR2
15 ,p_init_msg_list IN VARCHAR2
16 ,p_validation_level IN NUMBER
17 ,p_party_query_rec IN csi_datastructures_pub.party_query_rec
18 ,p_resolve_id_columns IN VARCHAR2
19 ,p_time_stamp IN DATE
20 ,x_party_header_tbl OUT NOCOPY csi_datastructures_pub.party_header_tbl
21 ,x_return_status OUT NOCOPY VARCHAR2
22 ,x_msg_count OUT NOCOPY NUMBER
23 ,x_msg_data OUT NOCOPY VARCHAR2
24 ) IS
25
26 l_api_name CONSTANT VARCHAR2(30) := 'GET_INST_PARTY_RELATIONSHIP' ;
27 l_api_version CONSTANT NUMBER := 1.0 ;
28 l_csi_debug_level NUMBER ;
29 l_instance_party_id NUMBER ;
30 l_contact_party_id NUMBER ;
31 l_contact_flag VARCHAR2(1);
32 l_party_source_tbl VARCHAR2(30) ;
33 l_contact_details csi_datastructures_pub.contact_details_rec ;
34 l_count NUMBER := 0 ;
35 l_where_clause VARCHAR2(2000) := '' ;
36 l_get_party_cursor_id NUMBER ;
37 l_party_rec csi_datastructures_pub.party_header_rec ;
38 l_flag VARCHAR2(1) :='N' ;
39 l_rows_processed NUMBER ;
40 l_pty_lookup_type VARCHAR2(30) := 'CSI_PARTY_SOURCE_TABLE' ;
41 l_select_stmt VARCHAR2(20000) := ' SELECT instance_party_id, instance_id, party_source_table, '||
42 ' party_id, relationship_type_code,contact_flag ,contact_ip_id, active_start_date, '||
43 ' active_end_date, context,attribute1,attribute2,attribute3, attribute4,attribute5, '||
44 ' attribute6, attribute7, attribute8, attribute9, attribute10 ,attribute11, '||
45 ' attribute12,attribute13,attribute14,attribute15 ,object_version_number, '||
46 ' primary_flag, preferred_flag'||
47 ' FROM CSI_I_PARTIES ';
48 l_pty_name VARCHAR2(360);
49
50 BEGIN
51 -- Standard Start of API savepoint
52 -- SAVEPOINT get_inst_party_rel_pub;
53
54 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
55
56 csi_utility_grp.check_ib_active;
57
58 -- Standard call to check for call compatibility.
59 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
60 p_api_version ,
61 l_api_name ,
62 g_pkg_name )
63 THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67 -- Initialize message list if p_init_msg_list is set to TRUE.
68 IF FND_API.to_Boolean( p_init_msg_list ) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72 -- Initialize API return status to success
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
76 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
77
78 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
79 IF (l_csi_debug_level > 0) THEN
80 csi_gen_utility_pvt.put_line( 'get_inst_party_relationship');
81 END IF;
82
83 -- If the debug level = 2 then dump all the parameters values.
84 IF (l_csi_debug_level > 1) THEN
85 csi_gen_utility_pvt.put_line( 'get_inst_party_relationship' ||
86 p_api_version ||'-'||
87 p_commit ||'-'||
88 p_init_msg_list ||'-'||
89 p_validation_level ||'-'||
90 p_time_stamp );
91 -- dump the in parameter in the log file
92 csi_gen_utility_pvt.dump_party_query_rec(p_party_query_rec) ;
93 END IF;
94
95 /***** srramakr commented for bug # 3304439
96 -- Check for the profile option and enable trace
97 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
98 -- End enable trace
99 ****/
100
101 -- Start API body
102 -- check if atleast one query parameters are passed else
103 -- raise an error
104 IF (p_party_query_rec.instance_party_id = FND_API.G_MISS_NUM)
105 AND (p_party_query_rec.instance_id = FND_API.G_MISS_NUM)
106 AND (p_party_query_rec.party_id = FND_API.G_MISS_NUM)
107 AND (p_party_query_rec.relationship_type_code = FND_API.G_MISS_CHAR) THEN
108
109 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
110 FND_MSG_PUB.Add;
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113
114 -- Generate the where clause dynamically
115 csi_party_relationships_pvt.Gen_Pty_Where_Clause
116 ( p_party_query_rec => p_party_query_rec,
117 x_where_clause => l_where_clause );
118
119 -- Build the select statement
120 l_select_stmt := l_select_stmt || ' where '||l_where_clause;
121
122 -- Open the cursor
123 l_get_party_cursor_id := dbms_sql.open_cursor;
124
125 --Parse the select statement
126 dbms_sql.parse(l_get_party_cursor_id, l_select_stmt , dbms_sql.native);
127
128 -- Bind the variables
129 csi_party_relationships_pvt.Bind_pty_variable(p_party_query_rec, l_get_party_cursor_id);
130
131 -- Define output variables
132 csi_party_relationships_pvt.Define_Pty_Columns(l_get_party_cursor_id);
133
134 -- execute the select statement
135 l_rows_processed := dbms_sql.execute(l_get_party_cursor_id);
136
137 LOOP
138 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_get_party_cursor_id) = 0;
139 -- get the values after executing the selecl statement
140 csi_party_relationships_pvt.Get_pty_Column_Values(l_get_party_cursor_id, l_party_rec);
141 l_count := l_count + 1;
142 x_party_header_tbl(l_count) := l_party_rec;
143 END LOOP;
144
145 -- Close the cursor
146 DBMS_SQL.CLOSE_CURSOR(l_get_party_cursor_id);
147
148 IF ((p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE)) THEN
149
150 IF p_time_stamp <= sysdate THEN
151 -- contruct from the history if the p_time_stamp
152 -- is < than sysdate
153 csi_party_relationships_pvt.Construct_pty_from_hist(x_party_header_tbl, p_time_stamp);
154 ELSE
155 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
156 FND_MSG_PUB.ADD;
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 END IF;
160 --start of the new code for resolve_id_columns 08/24/01
161
162 IF p_resolve_id_columns = fnd_api.g_true THEN
163
164 IF x_party_header_tbl.count > 0 THEN
165
166 FOR i IN x_party_header_tbl.FIRST.. x_party_header_tbl.LAST
167 LOOP
168 --l_contact_party_id := x_party_header_tbl(i).party_id ;
169 l_contact_party_id := x_party_header_tbl(i).instance_party_id ;
170 l_contact_flag := x_party_header_tbl(i).contact_flag;
171 l_party_source_tbl := x_party_header_tbl(i).party_source_table;
172
173 csi_party_relationships_pvt.get_contact_details
174 (
175 p_api_version => p_api_version
176 ,p_commit => p_commit
177 ,p_init_msg_list => p_init_msg_list
178 ,p_validation_level => p_validation_level
179 ,p_contact_party_id => l_contact_party_id
180 ,p_contact_flag => l_contact_flag
181 ,p_party_tbl => l_party_source_tbl
182 ,x_contact_details => l_contact_details
183 ,x_return_status => x_return_status
184 ,x_msg_count => x_msg_count
185 ,x_msg_data => x_msg_data
186 );
187
188 x_party_header_tbl(i).party_name := l_contact_details.party_name;
189 x_party_header_tbl(i).work_phone_number := l_contact_details.officephone;
190 x_party_header_tbl(i).address1 := l_contact_details.address1;
191 x_party_header_tbl(i).address2 := l_contact_details.address2;
192 x_party_header_tbl(i).address3 := l_contact_details.address3;
193 x_party_header_tbl(i).address4 := l_contact_details.address4;
194 x_party_header_tbl(i).city := l_contact_details.city;
195 x_party_header_tbl(i).postal_code := l_contact_details.postal_code;
196 x_party_header_tbl(i).state := l_contact_details.state;
197 x_party_header_tbl(i).country := l_contact_details.country;
198 x_party_header_tbl(i).email_address := l_contact_details.email;
199 -- Start of bug fix 2092790
200 IF l_party_source_tbl = 'EMPLOYEE' THEN
201 BEGIN
202 SELECT pf.employee_number
203 ,cl.meaning --party_type
204 INTO x_party_header_tbl(i).party_number
205 ,x_party_header_tbl(i).party_type
206 FROM per_all_people_f pf
207 ,csi_lookups cl
208 ,csi_item_instances cii
209 WHERE pf.person_id = x_party_header_tbl(i).party_id
210 AND cl.lookup_type=l_pty_lookup_type
211 AND cl.lookup_code=l_party_source_tbl
212 AND cii.instance_id=x_party_header_tbl(i).instance_id
213 AND pf.effective_end_date > SYSDATE
214 AND ROWNUM = 1 ;
215 EXCEPTION
216 WHEN OTHERS THEN
217 NULL;
218 END;
219 ELSIF l_party_source_tbl = 'HZ_PARTIES' THEN
220 BEGIN
221 SELECT hz.party_number
222 ,cl.meaning --party_type
223 ,hz.party_name
224 INTO x_party_header_tbl(i).party_number
225 ,x_party_header_tbl(i).party_type
226 ,l_pty_name
227 FROM hz_parties hz
228 ,csi_lookups cl
229 ,csi_item_instances cii
230 WHERE party_id = x_party_header_tbl(i).party_id
231 AND cl.lookup_type=l_pty_lookup_type
232 AND cl.lookup_code=l_party_source_tbl
233 AND cii.instance_id=x_party_header_tbl(i).instance_id;
234
235 IF x_party_header_tbl(i).party_name IS NULL OR
236 x_party_header_tbl(i).party_name = fnd_api.g_miss_char
237 THEN
238 x_party_header_tbl(i).party_name :=l_pty_name;
239 END IF;
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 NULL;
244 END;
245 ELSIF l_party_source_tbl = 'PO_VENDORS' THEN
246 BEGIN
247 SELECT po.segment1
248 ,cl.meaning --party_type
249 INTO x_party_header_tbl(i).party_number
250 ,x_party_header_tbl(i).party_type
251 FROM csi_lookups cl
252 ,csi_item_instances cii
253 ,po_vendors po
254 WHERE cl.lookup_type=l_pty_lookup_type
255 AND cl.lookup_code=l_party_source_tbl
256 AND cii.instance_id=x_party_header_tbl(i).instance_id
257 AND po.vendor_id = x_party_header_tbl(i).party_id;
258 EXCEPTION
259 WHEN OTHERS THEN
260 NULL;
261 END;
262 ELSIF l_party_source_tbl = 'TEAM' THEN
263 BEGIN
264 SELECT jt.team_number
265 ,cl.meaning --party_type
266 INTO x_party_header_tbl(i).party_number
267 ,x_party_header_tbl(i).party_type
268 FROM jtf_rs_teams_vl jt
269 ,csi_lookups cl
270 ,csi_item_instances cii
271 WHERE jt.team_id = x_party_header_tbl(i).party_id
272 and cl.lookup_type=l_pty_lookup_type
273 and cl.lookup_code=l_party_source_tbl
274 and cii.instance_id=x_party_header_tbl(i).instance_id;
275 EXCEPTION
276 WHEN OTHERS THEN
277 NULL;
278 END;
279 ELSIF l_party_source_tbl = 'GROUP' THEN
280 BEGIN
281 SELECT jg.group_number
282 ,cl.meaning --party_type
283 INTO x_party_header_tbl(i).party_number
284 ,x_party_header_tbl(i).party_type
285 FROM jtf_rs_groups_vl jg
286 ,csi_lookups cl
287 ,csi_item_instances cii
288 WHERE jg.group_id = x_party_header_tbl(i).party_id
289 and cl.lookup_type=l_pty_lookup_type
290 and cl.lookup_code=l_party_source_tbl
291 and cii.instance_id=x_party_header_tbl(i).instance_id;
292 EXCEPTION
293 WHEN OTHERS THEN
294 NULL;
295 END;
296 END IF;
297 -- End of bug fix 2092790
298
299
300 END LOOP;
301 END IF;
302 END IF;
303 --end of the new code for resolve_id_columns 08/24/01
304 --
305 -- End of API body
306
307 -- Standard check of p_commit.
308 /*
309 IF FND_API.To_Boolean( p_commit ) THEN
310 COMMIT WORK;
311 END IF;
312 */
313
314 /***** srramakr commented for bug # 3304439
315 -- Check for the profile option and disable the trace
316 IF (l_flag = 'Y') THEN
317 dbms_session.set_sql_trace(false);
318 END IF;
319 -- End disable trace
320 ****/
321
322 -- Standard call to get message count and if count is get message info.
323 FND_MSG_PUB.Count_And_Get
324 (p_count => x_msg_count ,
325 p_data => x_msg_data );
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 -- ROLLBACK TO get_inst_party_rel_pub;
329 x_return_status := FND_API.G_RET_STS_ERROR ;
330 FND_MSG_PUB.Count_And_Get
331 ( p_count => x_msg_count,
332 p_data => x_msg_data );
333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
334 -- ROLLBACK TO get_inst_party_rel_pub;
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
336 FND_MSG_PUB.Count_And_Get
337 ( p_count => x_msg_count,
338 p_data => x_msg_data );
339 WHEN OTHERS THEN
340 IF DBMS_SQL.IS_OPEN(l_get_party_cursor_id) THEN
341 DBMS_SQL.CLOSE_CURSOR(l_get_party_cursor_id);
342 END IF;
343 -- ROLLBACK TO get_inst_party_rel_pub;
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
345 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
346 FND_MSG_PUB.Add_Exc_Msg
347 ( g_pkg_name ,
348 l_api_name );
349 END IF;
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count,
352 p_data => x_msg_data );
353
354 END get_inst_party_relationships;
355
356
357 /*-------------------------------------------------------------*/
358 /* Procedure name: Create_inst_party_relationships */
359 /* Description : Procedure used to create new instance-party */
360 /* relationships */
361 /*-------------------------------------------------------------*/
362
363 PROCEDURE create_inst_party_relationship
364 ( p_api_version IN NUMBER
365 ,p_commit IN VARCHAR2
366 ,p_init_msg_list IN VARCHAR2
367 ,p_validation_level IN NUMBER
368 ,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
369 ,p_party_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
370 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
371 ,p_oks_txn_inst_tbl IN OUT NOCOPY oks_ibint_pub.txn_instance_tbl
372 ,x_return_status OUT NOCOPY VARCHAR2
373 ,x_msg_count OUT NOCOPY NUMBER
374 ,x_msg_data OUT NOCOPY VARCHAR2
375 ) IS
376 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INST_PARTY_RELATIONSHIP';
377 l_api_version CONSTANT NUMBER := 1.0;
378 l_csi_debug_level NUMBER;
379 l_party_rec csi_datastructures_pub.party_rec;
380 l_party_has_correct_acct BOOLEAN := FALSE;
381 l_internal_party_id NUMBER;
382 l_msg_index NUMBER;
383 l_msg_count NUMBER;
384 l_flag VARCHAR2(1) :='N';
385 l_party_source_tbl csi_party_relationships_pvt.party_source_tbl;
386 l_party_id_tbl csi_party_relationships_pvt.party_id_tbl;
387 l_contact_tbl csi_party_relationships_pvt.contact_tbl;
388 l_party_rel_type_tbl csi_party_relationships_pvt.party_rel_type_tbl;
389 l_party_count_rec csi_party_relationships_pvt.party_count_rec;
390 l_inst_party_tbl csi_party_relationships_pvt.inst_party_tbl;
391 l_acct_rel_type_tbl csi_party_relationships_pvt.acct_rel_type_tbl;
392 l_site_use_tbl csi_party_relationships_pvt.site_use_tbl;
393 l_account_count_rec csi_party_relationships_pvt.account_count_rec;
394 l_account_found VARCHAR2(1) :=NULL;
395 BEGIN
396 -- Standard Start of API savepoint
397 SAVEPOINT create_inst_party_rel_pub;
398
399 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
400
401 csi_utility_grp.check_ib_active;
402
403 -- Standard call to check for call compatibility.
404 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
405 p_api_version ,
406 l_api_name ,
407 g_pkg_name )
408 THEN
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 END IF;
411
412 -- Initialize message list if p_init_msg_list is set to TRUE.
413 IF FND_API.to_Boolean( p_init_msg_list ) THEN
414 FND_MSG_PUB.initialize;
415 END IF;
416
417 -- Initialize API return status to success
418 x_return_status := FND_API.G_RET_STS_SUCCESS;
419
420 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
421 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
422
423 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
424 IF (l_csi_debug_level > 0) THEN
425 csi_gen_utility_pvt.put_line( 'create_inst_party_relationship');
426 END IF;
427
428
429 -- If the debug level = 2 then dump all the parameters values.
430 IF (l_csi_debug_level > 1) THEN
431 csi_gen_utility_pvt.put_line( 'create_inst_party_relationship'||
432 p_api_version ||'-'||
433 p_commit ||'-'||
434 p_init_msg_list ||'-'||
435 p_validation_level );
436 -- Dump the records in the log file
437 csi_gen_utility_pvt.dump_party_tbl(p_party_tbl);
438 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
439 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
440 END IF;
441
442 /***** srramakr commented for bug # 3304439
443 -- Check for the profile option and enable trace
444 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
445 -- End enable trace
446 ****/
447
448 -- Start API body
449 --
450 -- Grab the internal party id from csi_installed paramters
451 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
452 csi_gen_utility_pvt.populate_install_param_rec;
453 END IF;
454 --
455 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
456 --
457 IF l_internal_party_id IS NULL THEN
458 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
459 FND_MSG_PUB.ADD;
460 RAISE FND_API.G_EXC_ERROR;
461 END IF;
462 -- Check if the table is not empty and then loop
463 IF p_party_tbl.count > 0 THEN
464 FOR l_party_row IN p_party_tbl.FIRST..p_party_tbl.LAST LOOP
465 IF p_party_tbl.EXISTS(l_party_row) -- Added for bug 3776650
466 THEN
467 -- Find out if one of the accounts for external parties is an owner account
468 IF ((p_party_tbl(l_party_row).party_source_table = 'HZ_PARTIES')
469 AND (p_party_tbl(l_party_row).party_id <> l_internal_party_id)
470 AND (p_party_tbl(l_party_row).relationship_type_code = 'OWNER')) THEN
471
472 l_party_has_correct_acct := FALSE;
473 IF p_party_account_tbl.COUNT > 0 THEN
474 FOR l_acct_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
475 IF p_party_account_tbl.EXISTS(l_acct_row) -- Added for bug 3776650
476 THEN
477 -- Check if the party and its accounts are mapped
478 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index IS NULL)
479 OR (p_party_account_tbl(l_acct_row).parent_tbl_index = FND_API.G_MISS_NUM)
480 OR (NOT(p_party_tbl.EXISTS(p_party_account_tbl(l_acct_row).parent_tbl_index)))
481 ) THEN
482 FND_MESSAGE.SET_NAME('CSI','CSI_API_PARTY_ACCT_NOT_MAPPED');
483 FND_MSG_PUB.Add;
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486
487 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index = l_party_row)
488 AND (p_party_account_tbl(l_acct_row).relationship_type_code = 'OWNER')) THEN
489 l_party_has_correct_acct := TRUE;
490 END IF;
491 END IF;
492 END LOOP;
493 END IF;
494
495 -- Raise an exception if external parties don't have an owner account
496 IF NOT l_party_has_correct_acct THEN
497 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OWNER_ACCT');
498 FND_MESSAGE.SET_TOKEN('PARTY_ID',p_party_tbl(l_party_row).party_id);
499 FND_MSG_PUB.ADD;
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502
503 END IF;
504
505 -- Adding att enhancements by sguthiva
506 IF p_party_tbl(l_party_row).contact_flag='Y'
507 AND (p_party_tbl(l_party_row).contact_ip_id IS NULL OR
508 p_party_tbl(l_party_row).contact_ip_id=fnd_api.g_miss_num )
509 AND (p_party_tbl(l_party_row).contact_parent_tbl_index IS NOT NULL AND
510 p_party_tbl(l_party_row).contact_parent_tbl_index <> fnd_api.g_miss_num )
511 THEN
512 FOR cont_row IN p_party_tbl.FIRST .. p_party_tbl.LAST
513 LOOP
514 IF cont_row=p_party_tbl(l_party_row).contact_parent_tbl_index
515 THEN
516 p_party_tbl(l_party_row).contact_ip_id:=p_party_tbl(cont_row).instance_party_id;
517 END IF;
518 END LOOP;
519 END IF;
520
521 -- End of addition by sguthiva
522
523 -- Call Private package to validate and create party relationship
524 csi_party_relationships_pvt.create_inst_party_relationship
525 ( p_api_version => p_api_version
526 ,p_commit => p_commit
527 ,p_init_msg_list => p_init_msg_list
528 ,p_validation_level => p_validation_level
529 ,p_party_rec => p_party_tbl(l_party_row)
530 ,p_txn_rec => p_txn_rec
531 ,x_return_status => x_return_status
532 ,x_msg_count => x_msg_count
533 ,x_msg_data => x_msg_data
534 ,p_party_source_tbl => l_party_source_tbl
535 ,p_party_id_tbl => l_party_id_tbl
536 ,p_contact_tbl => l_contact_tbl
537 ,p_party_rel_type_tbl => l_party_rel_type_tbl
538 ,p_party_count_rec => l_party_count_rec
539 ) ;
540
541 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
542 l_msg_index := 1;
543 l_msg_count := x_msg_count;
544 WHILE l_msg_count > 0 LOOP
545 x_msg_data := FND_MSG_PUB.GET
546 ( l_msg_index,
547 FND_API.G_FALSE );
548 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
549 l_msg_index := l_msg_index + 1;
550 l_msg_count := l_msg_count - 1;
551 END LOOP;
552 RAISE FND_API.G_EXC_ERROR;
553 END IF;
554 END IF;
555 END LOOP; -- End of party loop
556 END IF; -- End of party count if
557
558 -- Create party accounts
559 IF (p_party_account_tbl.COUNT > 0) THEN
560 FOR l_acct_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
561 IF p_party_account_tbl.EXISTS(l_acct_row) THEN
562 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index IS NULL)
563 OR (p_party_account_tbl(l_acct_row).parent_tbl_index = FND_API.G_MISS_NUM)
564 OR (NOT(p_party_tbl.EXISTS(p_party_account_tbl(l_acct_row).parent_tbl_index)))
565 ) THEN
566 FND_MESSAGE.SET_NAME('CSI','CSI_API_PARTY_ACCT_NOT_MAPPED');
567 FND_MSG_PUB.Add;
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570
571 p_party_account_tbl(l_acct_row).instance_party_id :=
572 p_party_tbl(p_party_account_tbl(l_acct_row).parent_tbl_index).instance_party_id;
573 -- Strat Addition for bug 1893100
574 IF p_party_account_tbl(l_acct_row).active_start_date IS NULL
575 OR p_party_account_tbl(l_acct_row).active_start_date = fnd_api.g_miss_date
576 AND (p_party_account_tbl(l_acct_row).ip_account_id IS NULL
577 OR p_party_account_tbl(l_acct_row).ip_account_id = fnd_api.g_miss_num)
578 THEN
579 p_party_account_tbl(l_acct_row).active_start_date :=
580 p_party_tbl(p_party_account_tbl(l_acct_row).parent_tbl_index).active_start_date;
581 END IF;
582 -- End Addition for bug 1893100
583 -- The following code has been added for bug 2990027
584 -- to avoid lock record error.
585 BEGIN
586 l_account_found:=NULL;
587 SELECT 'x'
588 INTO l_account_found
589 FROM csi_ip_accounts
590 WHERE ip_account_id =p_party_account_tbl(l_acct_row).ip_account_id;
591 EXCEPTION
592 WHEN OTHERS THEN
593 l_account_found:=NULL;
594 END ;
595 -- End addition.
596
597 -- Call Private package to validate and create party accounts
598 IF p_party_account_tbl(l_acct_row).ip_account_id IS NOT NULL
599 AND p_party_account_tbl(l_acct_row).ip_account_id <> fnd_api.g_miss_num
600 AND l_account_found IS NOT NULL
601 THEN
602 csi_party_relationships_pvt.update_inst_party_account
603 ( p_api_version => p_api_version
604 ,p_commit => p_commit
605 ,p_init_msg_list => p_init_msg_list
606 ,p_validation_level => p_validation_level
607 ,p_party_account_rec => p_party_account_tbl(l_acct_row)
608 ,p_txn_rec => p_txn_rec
609 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
610 ,x_return_status => x_return_status
611 ,x_msg_count => x_msg_count
612 ,x_msg_data => x_msg_data);
613 ELSE
614 csi_party_relationships_pvt.create_inst_party_account
615 ( p_api_version => p_api_version
616 ,p_commit => p_commit
617 ,p_init_msg_list => p_init_msg_list
618 ,p_validation_level => p_validation_level
619 ,p_party_account_rec => p_party_account_tbl(l_acct_row)
620 ,p_txn_rec => p_txn_rec
621 ,x_return_status => x_return_status
622 ,x_msg_count => x_msg_count
623 ,x_msg_data => x_msg_data
624 ,p_inst_party_tbl => l_inst_party_tbl
625 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
626 ,p_site_use_tbl => l_site_use_tbl
627 ,p_account_count_rec => l_account_count_rec
628 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
629 );
630
631 END IF;
632
633 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
634 l_msg_index := 1;
635 l_msg_count := x_msg_count;
636 WHILE l_msg_count > 0 LOOP
637 x_msg_data := FND_MSG_PUB.GET(
638 l_msg_index,
639 FND_API.G_FALSE );
640 csi_gen_utility_pvt.put_line( 'message data = '||x_msg_data);
641 l_msg_index := l_msg_index + 1;
642 l_msg_count := l_msg_count - 1;
643 END LOOP;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646 END IF;
647 END LOOP;
648 END IF;
649
650
651 --
652 -- End of API body
653 -- Standard check of p_commit.
654
655 IF FND_API.To_Boolean( p_commit ) THEN
656 COMMIT WORK;
657 END IF;
658
659 /***** srramakr commented for bug # 3304439
660 -- Check for the profile option and disable the trace
661 IF (l_flag = 'Y') THEN
662 dbms_session.set_sql_trace(false);
663 END IF;
664 -- End disable trace
665 ****/
666
667 -- Standard call to get message count and if count is get message info.
668 FND_MSG_PUB.Count_And_Get
669 (p_count => x_msg_count ,
670 p_data => x_msg_data );
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 x_return_status := FND_API.G_RET_STS_ERROR ;
674 ROLLBACK TO create_inst_party_rel_pub;
675 FND_MSG_PUB.Count_And_Get
676 ( p_count => x_msg_count,
677 p_data => x_msg_data );
678
679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
681 ROLLBACK TO create_inst_party_rel_pub;
682 FND_MSG_PUB.Count_And_Get
683 ( p_count => x_msg_count,
684 p_data => x_msg_data );
685
686 WHEN OTHERS THEN
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688 ROLLBACK TO create_inst_party_rel_pub;
689 IF FND_MSG_PUB.Check_Msg_Level
690 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
691 THEN
692 FND_MSG_PUB.Add_Exc_Msg
693 ( g_pkg_name ,
694 l_api_name );
695 END IF;
696 FND_MSG_PUB.Count_And_Get
697 ( p_count => x_msg_count,
698 p_data => x_msg_data );
699 END create_inst_party_relationship;
700
701 /*-------------------------------------------------------------*/
702 /* Procedure name: Update_inst_party_relationship */
703 /* Description : Procedure used to update the existing */
704 /* instance -party relationships */
705 /*-------------------------------------------------------------*/
706
707
708 PROCEDURE update_inst_party_relationship
709 ( p_api_version IN NUMBER
710 ,p_commit IN VARCHAR2
711 ,p_init_msg_list IN VARCHAR2
712 ,p_validation_level IN NUMBER
713 ,p_party_tbl IN csi_datastructures_pub.party_tbl
714 ,p_party_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
715 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
716 ,p_oks_txn_inst_tbl IN OUT NOCOPY oks_ibint_pub.txn_instance_tbl
717 ,x_return_status OUT NOCOPY VARCHAR2
718 ,x_msg_count OUT NOCOPY NUMBER
719 ,x_msg_data OUT NOCOPY VARCHAR2
720 ) IS
721 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_RELATIONSHIP';
722 l_api_version CONSTANT NUMBER := 1.0;
723 l_csi_debug_level NUMBER;
724 l_party_rec csi_datastructures_pub.party_rec;
725 l_temp_party_rec csi_datastructures_pub.party_rec;
726 l_curr_party_rec csi_datastructures_pub.party_rec;
727 l_exp_party_rec csi_datastructures_pub.party_rec;
728 l_party_tbl csi_datastructures_pub.party_tbl;
729 l_internal_party_id NUMBER;
730 l_party_has_correct_acct BOOLEAN := FALSE;
731 l_msg_index NUMBER;
732 l_msg_count NUMBER;
733 l_line_count NUMBER;
734 l_flag VARCHAR2(1) :='N';
735 l_inst_party_tbl csi_party_relationships_pvt.inst_party_tbl;
736 l_acct_rel_type_tbl csi_party_relationships_pvt.acct_rel_type_tbl;
737 l_site_use_tbl csi_party_relationships_pvt.site_use_tbl;
738 l_account_count_rec csi_party_relationships_pvt.account_count_rec;
739 p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
740 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
741 l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
742
743 -- Added by sk on 12/06/01 for contracts TRF fix bug 2133944
744
745 CURSOR acct_csr (p_ins_pty_id IN NUMBER) IS
746 SELECT acct.party_account_id
747 ,acct.active_end_date
748 ,pty.instance_id
749 FROM csi_ip_accounts acct
750 ,csi_i_parties pty
751 WHERE acct.instance_party_id = p_ins_pty_id
752 AND acct.relationship_type_code = 'OWNER'
753 AND ((acct.active_end_date IS NULL) OR (acct.active_end_date>SYSDATE))
754 AND pty.instance_party_id= acct.instance_party_id;
755 -- Following cursor has been added for fixing the bug 2151750
756 /* CURSOR party_csr (p_object_id IN NUMBER) IS
757 SELECT instance_id subject_id -- added by sguthiva for 2608706
758 FROM csi_item_instances
759 WHERE instance_id IN(
760 SELECT subject_id
761 FROM csi_ii_relationships
762 WHERE relationship_type_code = 'COMPONENT-OF'
763 START WITH object_id = p_object_id
764 CONNECT BY object_id = PRIOR subject_id)
765 AND (active_end_date IS NULL OR active_end_date> SYSDATE); */
766
767 CURSOR old_party_csr (p_ins_pty_id IN NUMBER) IS
768 SELECT instance_party_id,
769 party_id
770 FROM csi_i_parties
771 WHERE instance_party_id = p_ins_pty_id
772 AND relationship_type_code = 'OWNER'
773 AND (active_end_date IS NULL OR active_end_date > sysdate);
774
775 CURSOR exp_pty_csr (p_ins_id IN NUMBER) IS
776 SELECT instance_party_id,
777 party_id,
778 relationship_type_code,
779 object_version_number
780 FROM csi_i_parties
781 WHERE instance_id = p_ins_id
782 AND relationship_type_code<>'OWNER'
783 AND (active_end_date IS NULL OR active_end_date > sysdate);
784
785 --included for bug 5511689
786 CURSOR exp_acct_csr (p_inst_party_id IN NUMBER) IS
787 SELECT ip_account_id,
788 relationship_type_code,
789 object_version_number
790 FROM csi_ip_accounts
791 WHERE instance_party_id=p_inst_party_id
792 AND relationship_type_code <>'OWNER'
793 AND nvl(active_end_date, sysdate+1) >= sysdate;
794 --end of fix
795
796 l_acct_csr acct_csr%ROWTYPE;
797 l_old_party_csr old_party_csr%ROWTYPE;
798 l_acct_tbl csi_datastructures_pub.party_account_tbl;
799 l_count NUMBER;
800 la_count NUMBER;
801 l_act_tbl csi_datastructures_pub.party_account_tbl;
802 l_row NUMBER;
803 l_obj_ver_number NUMBER;
804 l_found BOOLEAN := FALSE;
805 l_end_date DATE;
806
807 -- End Addition by sk on 12/06/01 for contracts TRF fix bug 2133944
808 -- Start of code addition for fixing the bug 2151750
809 l_old_party_tbl csi_datastructures_pub.party_tbl := p_party_tbl;
810 l_cld_party_rec csi_datastructures_pub.party_rec;
811 l_new_curr_party_rec csi_datastructures_pub.party_rec;
812 l_ip_acct_rec csi_datastructures_pub.party_account_rec;
813 l_temp_acct_rec csi_datastructures_pub.party_account_rec;
814 l_new_ip_acct_rec csi_datastructures_pub.party_account_rec;
815 l_pty_count NUMBER;
816 lp_count NUMBER;
817 lpa_count NUMBER;
818 l_cld_party_id NUMBER;
819 l_cld_party_acct_id NUMBER;
820 l_last_vld_org NUMBER;
821 l_last_vld_org1 NUMBER;
822 l_exp_acct_rec csi_datastructures_pub.party_account_rec; -- Added by sguthiva for bug 2307804
823 l_grp_call_contracts VARCHAR2(1);
824 -- End of code addition for fixing the bug 2151750
825 -- Start of code addition for fixing bug 6368172, section 1 of 5
826 l_old_parent_owner_pty_acct_id NUMBER;
827 l_old_child_owner_pty_acct_id NUMBER;
828 -- End of code addition for fixing bug 6368172, section 1 of 5
829 BEGIN
830 -- Standard Start of API savepoint
831 SAVEPOINT update_inst_party_rel_pub ;
832
833 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
834
835 csi_utility_grp.check_ib_active;
836
837 -- Standard call to check for call compatibility.
838 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
839 p_api_version ,
840 l_api_name ,
841 g_pkg_name )
842 THEN
843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
844 END IF;
845
846 -- Initialize message list if p_init_msg_list is set to TRUE.
847 IF FND_API.to_Boolean( p_init_msg_list ) THEN
848 FND_MSG_PUB.initialize;
849 END IF;
850
851
852 -- Initialize API return status to success
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854
855 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
856 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
857
858 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
859 IF (l_csi_debug_level > 0) THEN
860 csi_gen_utility_pvt.put_line( 'update_inst_party_relationship ');
861 END IF;
862
863
864 -- If the debug level = 2 then dump all the parameters values.
865 IF (l_csi_debug_level > 1) THEN
866 csi_gen_utility_pvt.put_line( 'update_inst_party_relationship:' ||
867 p_api_version ||'-'||
868 p_commit ||'-'||
869 p_init_msg_list ||'-'||
870 p_validation_level );
871 -- Dump the records in the log file
872 csi_gen_utility_pvt.dump_party_tbl(p_party_tbl);
873 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
874 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
875
876 END IF;
877
878 /***** srramakr commented for bug # 3304439
879 -- Check for the profile option and enable trace
880 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
881 -- End enable trace
882 ****/
883
884 -- Start API body
885 --
886 -- Assign the value for l_grp_call_contracts
887 -- Since all the records will have the same value for grp_call_contracts, we just take the first one.
888 l_grp_call_contracts := FND_API.G_FALSE;
889 IF p_party_account_tbl.count > 0 THEN
890 FOR tab_row in p_party_account_tbl.FIRST .. p_party_account_tbl.LAST
891 LOOP
892 IF p_party_account_tbl.EXISTS(tab_row) THEN
893 l_grp_call_contracts := p_party_account_tbl(tab_row).grp_call_contracts;
894 EXIT;
895 END IF;
896 END LOOP;
897 END IF;
898 --
899 -- Grab the internal party id from csi_installed paramters
900 -- Added by sk on 12/06/01 for contracts TRF fix bug 2133944
901 -- End Added by sk on 12/06/01 for contracts TRF fix bug 2133944
902 -- Added for bug 2151750
903 -- The following code has been written to grab the old owner party_id
904 -- for an instance.
905 l_pty_count:= p_party_tbl.count;
906 lp_count:=0;
907 IF l_pty_count > 0 THEN
908 FOR p_csr IN 1..l_pty_count
909 LOOP
910 IF p_party_tbl(p_csr).relationship_type_code = 'OWNER'
911 THEN
912 OPEN old_party_csr (p_party_tbl(p_csr).instance_party_id);
913 FETCH old_party_csr into l_old_party_csr;
914 IF old_party_csr%FOUND
915 THEN
916 lp_count:=lp_count+1;
917 l_old_party_tbl(lp_count).instance_party_id := l_old_party_csr.instance_party_id;
918 l_old_party_tbl(lp_count).party_id := l_old_party_csr.party_id;
919 l_old_party_tbl(lp_count).cascade_ownership_flag := nvl(p_party_tbl(p_csr).cascade_ownership_flag,'N'); --Added for cascade 2972082
920 -- The following code has been written to grab the old owner party_id
921 -- for an instance.
922 lpa_count:= p_party_account_tbl.count;
923 IF lpa_count > 0 THEN
924 FOR pa_csr IN 1..lpa_count
925 LOOP
926 IF p_party_account_tbl(pa_csr).instance_party_id = l_old_party_tbl(lp_count).instance_party_id AND
927 p_party_account_tbl(pa_csr).relationship_type_code ='OWNER'
928 THEN
929 -- The following line has been written to grab the vld_organization_id
930 -- of the account.
931 l_old_party_tbl(lp_count).attribute1 := p_party_account_tbl(pa_csr).vld_organization_id;
932 EXIT;
933 END IF;
934 END LOOP;
935 END IF; -- end if for lpa_count > 0
936 END IF; -- end if for old_party_csr%FOUND
937 CLOSE old_party_csr;
938 END IF; -- end if for p_party_tbl(p_csr).relationship_type_code = 'OWNER'
939 END LOOP; -- end loop for p_csr IN 1..l_count
940 END IF; -- end if for l_pty_count > 0
941 -- End addition for bug 2151750
942 --
943 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
944 csi_gen_utility_pvt.populate_install_param_rec;
945 END IF;
946 --
947 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
948 --
949 IF l_internal_party_id IS NULL THEN
950 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
951 FND_MSG_PUB.ADD;
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954 -- Check if the table is not empty
955 IF p_party_tbl.count > 0 THEN
956 FOR l_party_row IN p_party_tbl.FIRST..p_party_tbl.LAST LOOP
957 IF p_party_tbl.EXISTS(l_party_row) THEN
958
959 -- Get Current Party record
960 IF NOT(CSI_Instance_parties_vld_pvt.Get_Party_Record
961 ( p_party_tbl(l_party_row).instance_party_id,
962 l_curr_party_rec)) THEN
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965
966 -- Check if this is Transfer of ownership (i.e. owner to a new owner)
967 -- If so, then a new owner account is needed for the new party
968 IF ((p_party_tbl(l_party_row).PARTY_ID <> FND_API.G_MISS_NUM)
969 AND (p_party_tbl(l_party_row).PARTY_ID IS NOT NULL)
970 AND (p_party_tbl(l_party_row).party_id <> l_internal_party_id)
971 AND (p_party_tbl(l_party_row).PARTY_ID <> l_curr_party_rec.PARTY_ID)
972 AND (p_party_tbl(l_party_row).PARTY_SOURCE_TABLE = 'HZ_PARTIES')
973 AND (p_party_tbl(l_party_row).RELATIONSHIP_TYPE_CODE = l_curr_party_rec.RELATIONSHIP_TYPE_CODE )
974 AND (p_party_tbl(l_party_row).RELATIONSHIP_TYPE_CODE = 'OWNER'
975 ))
976 THEN
977
978 -- Find out if one of the accounts for external parties is an owner account
979 l_party_has_correct_acct := FALSE;
980 IF p_party_account_tbl.COUNT > 0 THEN
981 FOR l_acct_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
982 IF p_party_account_tbl.EXISTS(l_acct_row) -- Added for bug 3776650
983 THEN
984 -- Check if the party and its accounts are mapped
985 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index IS NULL)
986 OR (p_party_account_tbl(l_acct_row).parent_tbl_index = FND_API.G_MISS_NUM)
987 OR (NOT(p_party_tbl.EXISTS(p_party_account_tbl(l_acct_row).parent_tbl_index)))
988 ) THEN
989 FND_MESSAGE.SET_NAME('CSI','CSI_API_PARTY_ACCT_NOT_MAPPED');
990 FND_MSG_PUB.Add;
991 RAISE FND_API.G_EXC_ERROR;
992 END IF;
993
994 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index = l_party_row)
995 AND (p_party_account_tbl(l_acct_row).relationship_type_code = 'OWNER')) THEN
996 l_party_has_correct_acct := TRUE;
997 -- Check whether bill_to and ship_to are passed. If not make them null
998 IF p_party_account_tbl(l_acct_row).bill_to_address IS NULL OR
999 p_party_account_tbl(l_acct_row).bill_to_address = FND_API.G_MISS_NUM THEN
1000 p_party_account_tbl(l_acct_row).bill_to_address := NULL;
1001 END IF;
1002 --
1003 IF p_party_account_tbl(l_acct_row).ship_to_address IS NULL OR
1004 p_party_account_tbl(l_acct_row).ship_to_address = FND_API.G_MISS_NUM THEN
1005 p_party_account_tbl(l_acct_row).ship_to_address := NULL;
1006 END IF;
1007 --
1008 END IF;
1009 END IF;
1010 END LOOP;
1011 END IF; -- End of Transfer of Ownership check
1012
1013 -- Raise an exception if external parties don't have an owner account
1014 IF NOT l_party_has_correct_acct THEN
1015 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OWNER_ACCT');
1016 FND_MESSAGE.SET_TOKEN('PARTY_ID',p_party_tbl(l_party_row).party_id);
1017 FND_MSG_PUB.ADD;
1018 RAISE FND_API.G_EXC_ERROR;
1019 END IF;
1020
1021 END IF;
1022
1023
1024 l_party_rec.instance_party_id := p_party_tbl(l_party_row).instance_party_id ;
1025 l_party_rec.instance_id := p_party_tbl(l_party_row).instance_id;
1026 l_party_rec.party_source_table := p_party_tbl(l_party_row).party_source_table ;
1027 l_party_rec.party_id := p_party_tbl(l_party_row).party_id ;
1028 l_party_rec.relationship_type_code := p_party_tbl(l_party_row).relationship_type_code;
1029 l_party_rec.contact_flag := p_party_tbl(l_party_row).contact_flag;
1030 l_party_rec.contact_ip_id := p_party_tbl(l_party_row).contact_ip_id;
1031 l_party_rec.active_start_date := p_party_tbl(l_party_row).active_start_date;
1032 l_party_rec.active_end_date := p_party_tbl(l_party_row).active_end_date;
1033 l_party_rec.context := p_party_tbl(l_party_row).context;
1034 l_party_rec.attribute1 := p_party_tbl(l_party_row). attribute1;
1035 l_party_rec.attribute2 := p_party_tbl(l_party_row).attribute2;
1036 l_party_rec.attribute3 := p_party_tbl(l_party_row).attribute3;
1037 l_party_rec.attribute4 := p_party_tbl(l_party_row).attribute4;
1038 l_party_rec.attribute5 := p_party_tbl(l_party_row).attribute5;
1039 l_party_rec.attribute6 := p_party_tbl(l_party_row).attribute6;
1040 l_party_rec.attribute7 := p_party_tbl(l_party_row).attribute7;
1041 l_party_rec.attribute8 := p_party_tbl(l_party_row).attribute8;
1042 l_party_rec.attribute9 := p_party_tbl(l_party_row).attribute9;
1043 l_party_rec.attribute10 := p_party_tbl(l_party_row).attribute10;
1044 l_party_rec.attribute11 := p_party_tbl(l_party_row).attribute11;
1045 l_party_rec.attribute12 := p_party_tbl(l_party_row).attribute12;
1046 l_party_rec.attribute13 := p_party_tbl(l_party_row).attribute13;
1047 l_party_rec.attribute14 := p_party_tbl(l_party_row).attribute14;
1048 l_party_rec.attribute15 := p_party_tbl(l_party_row).attribute15;
1049 l_party_rec.preferred_flag := p_party_tbl(l_party_row).preferred_flag;
1050 l_party_rec.primary_flag := p_party_tbl(l_party_row).primary_flag;
1051 l_party_rec.object_version_number := p_party_tbl(l_party_row).object_version_number;
1052
1053 -- Start of code addition for fixing bug 6368172, section 2 of 5
1054 -- Need to grab the account id before it gets changed
1055 IF l_party_rec.instance_party_id IS NOT NULL THEN
1056 BEGIN
1057 SELECT party_account_id
1058 INTO l_old_parent_owner_pty_acct_id
1059 FROM csi_ip_accounts
1060 WHERE instance_party_id = l_party_rec.instance_party_id
1061 AND relationship_type_code = 'OWNER';
1062 EXCEPTION
1063 WHEN NO_DATA_FOUND THEN
1064 l_old_parent_owner_pty_acct_id := NULL;
1065 END;
1066 END IF;
1067 -- End of code addition for fixing bug 6368172, section 2 of 5
1068
1069 -- added by sk on 12/07 for accounts fix
1070 IF p_party_account_tbl.COUNT > 0 THEN
1071 FOR l_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
1072 IF p_party_account_tbl.EXISTS(l_row) THEN
1073 l_obj_ver_number := NULL;
1074 BEGIN
1075 IF p_party_account_tbl(l_row).ip_account_id IS NOT NULL
1076 AND p_party_account_tbl(l_row).ip_account_id <> fnd_api.g_miss_num
1077 THEN
1078 SELECT acct.object_version_number
1079 INTO l_obj_ver_number
1080 FROM csi_ip_accounts acct
1081 WHERE acct.ip_account_id= p_party_account_tbl(l_row).ip_account_id;
1082 END IF;
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 l_obj_ver_number := NULL;
1086 END;
1087 IF (p_party_account_tbl(l_row).ip_account_id IS NOT NULL AND p_party_account_tbl(l_row).ip_account_id <> fnd_api.g_miss_num)
1088 AND p_party_account_tbl(l_row).relationship_type_code = 'OWNER'
1089 AND p_party_account_tbl(l_row).instance_party_id = l_party_rec.instance_party_id
1090 AND p_party_account_tbl(l_row).object_version_number = l_obj_ver_number
1091 THEN
1092 l_act_tbl(l_party_row).attribute1:='Y';
1093 l_act_tbl(l_party_row).ip_account_id :=p_party_account_tbl(l_row).ip_account_id;
1094 END IF;
1095 END IF;
1096 END LOOP;
1097 END IF;
1098 -- end of addition by sk on 12/07 for accounts fix
1099
1100 csi_party_relationships_pvt.update_inst_party_relationship
1101 ( p_api_version => p_api_version
1102 ,p_commit => p_commit
1103 ,p_init_msg_list => p_init_msg_list
1104 ,p_validation_level => p_validation_level
1105 ,p_party_rec => l_party_rec
1106 ,p_txn_rec => p_txn_rec
1107 ,x_return_status => x_return_status
1108 ,x_msg_count => x_msg_count
1109 ,x_msg_data => x_msg_data ) ;
1110
1111 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1112 l_msg_index := 1;
1113 l_msg_count := x_msg_count;
1114 WHILE l_msg_count > 0 LOOP
1115 x_msg_data := FND_MSG_PUB.GET(
1116 l_msg_index,
1117 FND_API.G_FALSE );
1118 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1119 l_msg_index := l_msg_index + 1;
1120 l_msg_count := l_msg_count - 1;
1121 END LOOP;
1122 RAISE FND_API.G_EXC_ERROR;
1123 ELSE
1124 -- Grab the new party rec
1125 l_party_tbl(l_party_row) := l_party_rec;
1126 END IF;
1127 END IF;
1128 END LOOP;
1129
1130 END IF;
1131
1132 -- Update accounts
1133 -- Check if the table is not empty
1134 IF p_party_account_tbl.count > 0 THEN
1135 FOR l_acct_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
1136 IF p_party_account_tbl.EXISTS(l_acct_row) THEN
1137
1138
1139 IF ( (p_party_account_tbl(l_acct_row).ip_account_id IS NULL)
1140 OR
1141 (p_party_account_tbl(l_acct_row).ip_account_id = FND_API.G_MISS_NUM) ) THEN
1142 -- Call Private package to validate and create party accounts
1143 csi_party_relationships_pvt.create_inst_party_account
1144 ( p_api_version => p_api_version
1145 ,p_commit => p_commit
1146 ,p_init_msg_list => p_init_msg_list
1147 ,p_validation_level => p_validation_level
1148 ,p_party_account_rec => p_party_account_tbl(l_acct_row)
1149 ,p_txn_rec => p_txn_rec
1150 ,x_return_status => x_return_status
1151 ,x_msg_count => x_msg_count
1152 ,x_msg_data => x_msg_data
1153 ,p_inst_party_tbl => l_inst_party_tbl
1154 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
1155 ,p_site_use_tbl => l_site_use_tbl
1156 ,p_account_count_rec => l_account_count_rec
1157 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1158 );
1159 ELSE
1160 -- dbms_output.put_line('PUB: caling update_inst_party_account');
1161 -- added by sk on 12/07 for accounts fix
1162
1163 l_found := FALSE;
1164 IF l_act_tbl.COUNT > 0 THEN
1165 FOR l_arow IN l_act_tbl.FIRST..l_act_tbl.LAST LOOP
1166 IF l_found
1167 THEN
1168 EXIT;
1169 END IF;
1170 IF l_act_tbl.EXISTS(l_arow) THEN
1171 IF l_act_tbl(l_arow).ip_account_id = p_party_account_tbl(l_acct_row).ip_account_id
1172 AND l_act_tbl(l_arow).attribute1 = 'Y'
1173 THEN
1174 l_found := TRUE;
1175 BEGIN
1176 SELECT acct.object_version_number
1177 INTO p_party_account_tbl(l_arow).object_version_number
1178 FROM csi_ip_accounts acct
1179 WHERE acct.ip_account_id= p_party_account_tbl(l_arow).ip_account_id;
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 NULL;
1183 END;
1184 END IF;
1185 END IF;
1186 END LOOP;
1187 END IF;
1188 -- End addition by sk on 12/07 for accounts fix
1189 -- srramakr Fix for Bug # 3117552
1190 IF p_txn_rec.transaction_type_id = 7 THEN -- only for Account Merge
1191 BEGIN
1192 SELECT acct.object_version_number,acct.active_end_date
1193 INTO p_party_account_tbl(l_acct_row).object_version_number,l_end_date
1194 FROM csi_ip_accounts acct
1195 WHERE acct.ip_account_id= p_party_account_tbl(l_acct_row).ip_account_id;
1196 -- Commenting for bug 3692167 as it will fail the unique constraint.
1197 -- At any time there should be one active record in combination of
1198 -- (party_account_id,relationship_type_code) associated to an
1199 -- party entity.
1200 /*
1201 IF nvl(l_end_date,(sysdate+1)) <= sysdate THEN
1202 p_party_account_tbl(l_acct_row).active_end_date := NULL;
1203 END IF;
1204 */
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 NULL;
1208 END;
1209 END IF;
1210 --
1211 csi_party_relationships_pvt.update_inst_party_account
1212 ( p_api_version => p_api_version
1213 ,p_commit => p_commit
1214 ,p_init_msg_list => p_init_msg_list
1215 ,p_validation_level => p_validation_level
1216 ,p_party_account_rec => p_party_account_tbl(l_acct_row)
1217 ,p_txn_rec => p_txn_rec
1218 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1219 ,x_return_status => x_return_status
1220 ,x_msg_count => x_msg_count
1221 ,x_msg_data => x_msg_data);
1222 END IF;
1223
1224 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1225 l_msg_index := 1;
1226 l_msg_count := x_msg_count;
1227 WHILE l_msg_count > 0 LOOP
1228 x_msg_data := FND_MSG_PUB.GET(
1229 l_msg_index,
1230 FND_API.G_FALSE );
1231 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1232 l_msg_index := l_msg_index + 1;
1233 l_msg_count := l_msg_count - 1;
1234 END LOOP;
1235 RAISE FND_API.G_EXC_ERROR;
1236 END IF;
1237 END IF;
1238 END LOOP;
1239 -- Added by sguthiva for bug 2307804
1240 ELSE
1241 -- The following code has wrritten specifically for expiring the ownership of
1242 -- an external party during the transfer of ownership from external to internal.
1243
1244 FOR p_row IN p_party_tbl.FIRST..p_party_tbl.LAST
1245 LOOP
1246 FOR l_row IN l_old_party_tbl.FIRST..l_old_party_tbl.LAST
1247 LOOP
1248 IF l_old_party_tbl(l_row).instance_party_id = p_party_tbl(p_row).instance_party_id
1249 AND l_old_party_tbl(l_row).party_id <> l_internal_party_id
1250 AND p_party_tbl(p_row).party_id = l_internal_party_id
1251 OR ((l_old_party_tbl(l_row).instance_party_id = p_party_tbl(p_row).instance_party_id)
1252 AND (p_party_tbl(p_row).party_source_table IN ('EMPLOYEE', 'PO_VENDORS')))
1253 THEN
1254 -- Adding for bug 3294748
1255 -- Wrote the following code to expire all non-owner parties and its contacts (if any)
1256 -- in the case of ownership transfer from external-to-internal party,
1257 -- however we do not expire non-owner accounts.
1258 IF l_old_party_tbl(l_row).instance_party_id = p_party_tbl(p_row).instance_party_id
1259 AND l_old_party_tbl(l_row).party_id <> l_internal_party_id
1260 AND p_party_tbl(p_row).party_id = l_internal_party_id
1261 AND ( p_party_tbl(p_row).instance_id IS NOT NULL AND
1262 p_party_tbl(p_row).instance_id <> fnd_api.g_miss_num)
1263 AND p_party_tbl(p_row).relationship_type_code='OWNER'
1264 THEN
1265 FOR l_exp_pty IN exp_pty_csr(p_party_tbl(p_row).instance_id)
1266 LOOP
1267 l_exp_party_rec:= l_temp_party_rec;
1268 l_exp_party_rec.instance_id:= p_party_tbl(p_row).instance_id;
1269 l_exp_party_rec.instance_party_id := l_exp_pty.instance_party_id;
1270 l_exp_party_rec.relationship_type_code := l_exp_pty.relationship_type_code;
1271 l_exp_party_rec.object_version_number := l_exp_pty.object_version_number;
1272 -- Bug 3804960
1273 -- srramakr Need to use the same the date used by the item instance
1274 IF p_txn_rec.src_txn_creation_date IS NULL OR
1275 p_txn_rec.src_txn_creation_date = FND_API.G_MISS_DATE THEN
1276 l_exp_party_rec.active_end_date := sysdate;
1277 ELSE
1278 l_exp_party_rec.active_end_date := p_txn_rec.src_txn_creation_date;
1279 END IF;
1280 -- End of 3804960
1281 csi_party_relationships_pvt.update_inst_party_relationship
1282 ( p_api_version => p_api_version
1283 ,p_commit => p_commit
1284 ,p_init_msg_list => p_init_msg_list
1285 ,p_validation_level => p_validation_level
1286 ,p_party_rec => l_exp_party_rec
1287 ,p_txn_rec => p_txn_rec
1288 ,x_return_status => x_return_status
1289 ,x_msg_count => x_msg_count
1290 ,x_msg_data => x_msg_data ) ;
1291
1292 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1293 l_msg_index := 1;
1294 l_msg_count := x_msg_count;
1295 WHILE l_msg_count > 0
1296 LOOP
1297 x_msg_data := FND_MSG_PUB.GET(
1298 l_msg_index,
1299 FND_API.G_FALSE );
1300 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1301 l_msg_index := l_msg_index + 1;
1302 l_msg_count := l_msg_count - 1;
1303 END LOOP;
1304 RAISE FND_API.G_EXC_ERROR;
1305 END IF;
1306 --fix for bug 5511689:To expire non-owner accounts while expiring non-owner parties.
1307 FOR exp_acct_rec IN exp_acct_csr(l_exp_pty.instance_party_id) LOOP
1308 l_exp_acct_rec:=l_temp_acct_rec;
1309 l_exp_acct_rec.ip_account_id := exp_acct_rec.ip_account_id;
1310 l_exp_acct_rec.relationship_type_code := exp_acct_rec.relationship_type_code;
1311 l_exp_acct_rec.object_version_number := exp_acct_rec.object_version_number ;
1312 l_exp_acct_rec.active_end_date :=SYSDATE;
1313 l_exp_acct_rec.expire_flag :=fnd_api.g_true;
1314 csi_gen_utility_pvt.put_line('Expiring account record '||l_exp_acct_rec.ip_account_id||' of account type '||l_exp_acct_rec.relationship_type_code);
1315 IF l_exp_acct_rec.ip_account_id IS NOT NULL
1316 AND l_exp_acct_rec.ip_account_id <> fnd_api.g_miss_num
1317 THEN
1318 l_exp_acct_rec.grp_call_contracts := l_grp_call_contracts;
1319 csi_party_relationships_pvt.update_inst_party_account
1320 ( p_api_version => p_api_version
1321 ,p_commit => p_commit
1322 ,p_init_msg_list => p_init_msg_list
1323 ,p_validation_level => p_validation_level
1324 ,p_party_account_rec => l_exp_acct_rec
1325 ,p_txn_rec => p_txn_rec
1326 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1327 ,x_return_status => x_return_status
1328 ,x_msg_count => x_msg_count
1329 ,x_msg_data => x_msg_data);
1330
1331 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1332 l_msg_index := 1;
1333 l_msg_count := x_msg_count;
1334 WHILE l_msg_count > 0 LOOP
1335 x_msg_data := FND_MSG_PUB.GET(
1336 l_msg_index,
1337 FND_API.G_FALSE );
1338 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1339 l_msg_index := l_msg_index + 1;
1340 l_msg_count := l_msg_count - 1;
1341 END LOOP;
1342 RAISE FND_API.G_EXC_ERROR;
1343 END IF;
1344 END IF;
1345 END LOOP;
1346 --end of fix 5511689
1347 END LOOP;
1348 END IF;
1349 --End addition for bug 3294748
1350
1351 l_exp_acct_rec:=l_temp_acct_rec;
1352 BEGIN
1353 SELECT ip_account_id,
1354 relationship_type_code,
1355 object_version_number
1356 INTO l_exp_acct_rec.ip_account_id,
1357 l_exp_acct_rec.relationship_type_code,
1358 l_exp_acct_rec.object_version_number
1359 FROM csi_ip_accounts
1360 WHERE instance_party_id=p_party_tbl(p_row).instance_party_id
1361 AND relationship_type_code ='OWNER'
1362 AND nvl(active_end_date, sysdate+1) >= sysdate;
1363 l_exp_acct_rec.active_end_date :=SYSDATE;
1364 l_exp_acct_rec.expire_flag :=fnd_api.g_true;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 NULL;
1368 END;
1369
1370 IF l_exp_acct_rec.ip_account_id IS NOT NULL
1371 AND l_exp_acct_rec.ip_account_id <> fnd_api.g_miss_num
1372 THEN
1373 l_exp_acct_rec.grp_call_contracts := l_grp_call_contracts;
1374 csi_party_relationships_pvt.update_inst_party_account
1375 ( p_api_version => p_api_version
1376 ,p_commit => p_commit
1377 ,p_init_msg_list => p_init_msg_list
1378 ,p_validation_level => p_validation_level
1379 ,p_party_account_rec => l_exp_acct_rec
1380 ,p_txn_rec => p_txn_rec
1381 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1382 ,x_return_status => x_return_status
1383 ,x_msg_count => x_msg_count
1384 ,x_msg_data => x_msg_data);
1385
1386 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1387 l_msg_index := 1;
1388 l_msg_count := x_msg_count;
1389 WHILE l_msg_count > 0 LOOP
1390 x_msg_data := FND_MSG_PUB.GET(
1391 l_msg_index,
1392 FND_API.G_FALSE );
1393 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1394 l_msg_index := l_msg_index + 1;
1395 l_msg_count := l_msg_count - 1;
1396 END LOOP;
1397 RAISE FND_API.G_EXC_ERROR;
1398 END IF;
1399 END IF;
1400 END IF;
1401 END LOOP;
1402 END LOOP;
1403 -- End addition by sguthiva for bug 2307804
1404
1405 END IF;
1406 -- Start of code addition for fixing the bug 2151750
1407 /* If the owner of the root of a configuration changes, the ownership should
1408 be inherited by all child component (children in component-of tree) having the
1409 same initial owner as the root.
1410 Here grab the root instance owner party and its owner account.
1411 Retreive if it has any child instances from csi_ii_relationships table of relationship_type_code
1412 as 'COMPONENT-OF'.
1413 */
1414
1415 IF l_old_party_tbl.count > 0 THEN
1416 FOR l_old_party_row IN l_old_party_tbl.FIRST..l_old_party_tbl.LAST
1417 LOOP
1418 IF l_old_party_tbl.EXISTS(l_old_party_row) THEN
1419 IF l_old_party_tbl(l_old_party_row).relationship_type_code = 'OWNER' AND
1420 l_old_party_tbl(l_old_party_row).party_id IS NOT NULL AND
1421 l_old_party_tbl(l_old_party_row).party_id <> fnd_api.g_miss_num
1422 THEN
1423 -- Get Current Party record
1424 IF NOT(CSI_Instance_parties_vld_pvt.Get_Party_Record
1425 ( l_old_party_tbl(l_old_party_row).instance_party_id,
1426 l_new_curr_party_rec)) THEN
1427 RAISE FND_API.G_EXC_ERROR;
1428 END IF;
1429
1430 -- Start of code addition for fixing bug 6368172, section 3 of 5
1431 -- grab the owner account of the parent
1432 l_ip_acct_rec := l_temp_acct_rec;
1433 l_new_ip_acct_rec := l_temp_acct_rec;
1434 BEGIN
1435 SELECT instance_party_id
1436 ,party_account_id
1437 ,relationship_type_code
1438 ,bill_to_address
1439 ,ship_to_address
1440 ,active_start_date
1441 ,active_end_date
1442 ,context
1443 ,attribute1
1444 ,attribute2
1445 ,attribute3
1446 ,attribute4
1447 ,attribute5
1448 ,attribute6
1449 ,attribute7
1450 ,attribute8
1451 ,attribute9
1452 ,attribute10
1453 ,attribute11
1454 ,attribute12
1455 ,attribute13
1456 ,attribute14
1457 ,attribute15
1458 INTO l_ip_acct_rec.instance_party_id
1459 ,l_ip_acct_rec.party_account_id
1460 ,l_ip_acct_rec.relationship_type_code
1461 ,l_ip_acct_rec.bill_to_address
1462 ,l_ip_acct_rec.ship_to_address
1463 ,l_ip_acct_rec.active_start_date
1464 ,l_ip_acct_rec.active_end_date
1465 ,l_ip_acct_rec.context
1466 ,l_ip_acct_rec.attribute1
1467 ,l_ip_acct_rec.attribute2
1468 ,l_ip_acct_rec.attribute3
1469 ,l_ip_acct_rec.attribute4
1470 ,l_ip_acct_rec.attribute5
1471 ,l_ip_acct_rec.attribute6
1472 ,l_ip_acct_rec.attribute7
1473 ,l_ip_acct_rec.attribute8
1474 ,l_ip_acct_rec.attribute9
1475 ,l_ip_acct_rec.attribute10
1476 ,l_ip_acct_rec.attribute11
1477 ,l_ip_acct_rec.attribute12
1478 ,l_ip_acct_rec.attribute13
1479 ,l_ip_acct_rec.attribute14
1480 ,l_ip_acct_rec.attribute15
1481 FROM csi_ip_accounts
1482 WHERE instance_party_id = l_old_party_tbl(l_old_party_row).instance_party_id
1483 AND relationship_type_code = 'OWNER'
1484 AND SYSDATE BETWEEN nvl(active_start_date, SYSDATE-1)
1485 AND nvl(active_end_date, SYSDATE+1);
1486
1487 l_new_ip_acct_rec := l_ip_acct_rec;
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 l_ip_acct_rec := l_temp_acct_rec;
1491 l_new_ip_acct_rec :=l_temp_acct_rec;
1492 END;
1493 -- End of code addition for fixing bug 6368172, section 3 of 5
1494
1495 -- If the retreived party records party_id has been changed then
1496 -- we can assume that a transfer of ownership has taken place in
1497 -- the above procedure.
1498 IF l_old_party_tbl(l_old_party_row).party_id <> l_new_curr_party_rec.party_id
1499 -- Start of code addition for fixing bug 6368172, section 4 of 5
1500 OR (l_old_party_tbl(l_old_party_row).party_id = l_new_curr_party_rec.party_id
1501 AND l_old_parent_owner_pty_acct_id IS NOT NULL
1502 AND l_old_parent_owner_pty_acct_id <> fnd_api.g_miss_num
1503 AND l_old_parent_owner_pty_acct_id <> l_new_ip_acct_rec.party_account_id)
1504 -- End of code addition for fixing bug 6368172, section 4 of 5
1505 OR nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y' -- Added for cascade 2972082
1506 THEN
1507 -- the l_new_ip_acct_rec account needs to be passed to all the children
1508 -- we got an instance whose owner party, or owner account has been changed
1509 -- we need to grab all its children if it has any and change the
1510 -- ownership of them also.
1511 -- Here I'm grabbing the children
1512
1513 -- Added for cascade bug 2972082
1514 IF l_old_party_tbl(l_old_party_row).instance_id IS NULL OR
1515 l_old_party_tbl(l_old_party_row).instance_id = fnd_api.g_miss_num
1516 THEN
1517 BEGIN
1518 SELECT instance_id
1519 INTO l_old_party_tbl(l_old_party_row).instance_id
1520 FROM csi_i_parties
1521 WHERE instance_party_id=l_old_party_tbl(l_old_party_row).instance_party_id;
1522 EXCEPTION
1523 WHEN NO_DATA_FOUND THEN
1524 NULL;
1525 END;
1526 END IF;
1527 -- End addition for bug 2972082
1528
1529 p_rel_query_rec.object_id := l_old_party_tbl(l_old_party_row).instance_id;
1530 p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
1531 --
1532 IF p_rel_query_rec.object_id IS NOT NULL AND
1533 p_rel_query_rec.object_id <> FND_API.G_MISS_NUM THEN
1534 csi_ii_relationships_pvt.Get_Children
1535 ( p_relationship_query_rec => p_rel_query_rec,
1536 p_rel_tbl => l_rel_tbl,
1537 p_depth => NULL,
1538 p_active_relationship_only => FND_API.G_TRUE,
1539 p_time_stamp => FND_API.G_MISS_DATE,
1540 p_get_dfs => FND_API.G_FALSE,
1541 p_ii_relationship_level_tbl => l_ii_relationship_level_tbl,
1542 x_return_status => x_return_status,
1543 x_msg_count => x_msg_count,
1544 x_msg_data => x_msg_data
1545 );
1546 --
1547 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1548 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_CHILDREN_ERROR');
1549 FND_MSG_PUB.Add;
1550 RAISE FND_API.G_EXC_ERROR;
1551 END IF;
1552 END IF;
1553 --
1554 IF l_rel_tbl.count > 0 THEN
1555 -- FOR l_old_pty_csr IN party_csr(l_old_party_tbl(l_old_party_row).instance_id)
1556 FOR j in l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
1557 -- After grabbing the child instances one by one I'll call
1558 -- the pvt.update_inst_party_relationship for a transfer
1559 -- to new owner.
1560 -- Now grab the child instances instance_party_id
1561 BEGIN
1562 l_cld_party_rec := l_new_curr_party_rec;
1563 l_cld_party_id := null;
1564 l_cld_party_rec.active_start_date := fnd_api.g_miss_date;
1565 l_cld_party_rec.active_end_date := fnd_api.g_miss_date;
1566 SELECT instance_party_id,
1567 instance_id,
1568 party_id,
1569 object_version_number
1570 INTO l_cld_party_rec.instance_party_id,
1571 l_cld_party_rec.instance_id,
1572 l_cld_party_id,
1573 l_cld_party_rec.object_version_number
1574 FROM csi_i_parties
1575 WHERE instance_id = l_rel_tbl(j).subject_id
1576 AND relationship_type_code = 'OWNER'
1577 AND (active_end_date IS NULL OR active_end_date > SYSDATE);
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 l_cld_party_rec := l_temp_party_rec;
1582 END;
1583
1584 BEGIN
1585 SELECT party_account_id
1586 INTO l_cld_party_acct_id
1587 FROM csi_ip_accounts
1588 WHERE instance_party_id = l_cld_party_rec.instance_party_id
1589 AND relationship_type_code = 'OWNER'
1590 AND SYSDATE BETWEEN nvl(active_start_date, sysdate-1)
1591 AND nvl(active_end_date, sysdate+1);
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 NULL;
1595 END;
1596
1597 -- The following is modified for cascade bug 2972082
1598 -- to make sure parties were updated only for new party
1599 -- which is different from the original party if
1600 -- cascade_ownership_flag=fnd_api.g_true.
1601 /*
1602 IF l_cld_party_rec.instance_party_id IS NOT NULL AND
1603 l_cld_party_rec.instance_party_id <> fnd_api.g_miss_num AND
1604 ((l_cld_party_id = l_old_party_tbl(l_old_party_row).party_id AND
1605 l_cld_party_id <> l_cld_party_rec.party_id)
1606 OR
1607 (l_cld_party_id <> l_old_party_tbl(l_old_party_row).party_id AND
1608 l_cld_party_id <> l_cld_party_rec.party_id AND
1609 nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y') -- Added for cascade 2972082
1610 )
1611 THEN
1612 */
1613 -- Start of code addition for fixing bug 6368172, section 5 of 5
1614 -- Need to grab the old child account id before it gets changed
1615 IF l_cld_party_rec.instance_party_id IS NOT NULL THEN
1616 BEGIN
1617 SELECT party_account_id
1618 INTO l_old_child_owner_pty_acct_id
1619 FROM csi_ip_accounts
1620 WHERE instance_party_id = l_cld_party_rec.instance_party_id
1621 AND relationship_type_code = 'OWNER';
1622 EXCEPTION
1623 WHEN NO_DATA_FOUND THEN
1624 l_old_child_owner_pty_acct_id := NULL;
1625 END;
1626 END IF;
1627 IF l_cld_party_rec.instance_party_id IS NOT NULL AND
1628 l_cld_party_rec.instance_party_id <> fnd_api.g_miss_num AND
1629 ((l_cld_party_id = l_old_party_tbl(l_old_party_row).party_id AND
1630 l_cld_party_id <> l_cld_party_rec.party_id)
1631 OR
1632 (l_cld_party_id = l_old_party_tbl(l_old_party_row).party_id AND
1633 l_cld_party_id = l_cld_party_rec.party_id AND
1634 l_old_parent_owner_pty_acct_id IS NOT NULL AND
1635 l_old_parent_owner_pty_acct_id <> fnd_api.g_miss_num AND
1636 l_old_child_owner_pty_acct_id IS NOT NULL AND
1637 l_old_child_owner_pty_acct_id <> fnd_api.g_miss_num AND
1638 l_old_child_owner_pty_acct_id = l_old_parent_owner_pty_acct_id AND
1639 l_old_child_owner_pty_acct_id <> l_new_ip_acct_rec.party_account_id)
1640 OR
1641 (l_cld_party_id <> l_old_party_tbl(l_old_party_row).party_id AND
1642 l_cld_party_id <> l_cld_party_rec.party_id AND
1643 nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y') -- Added for cascade 2972082
1644 )
1645 THEN
1646 -- End of code addition for fixing bug 6368172, section 5 of 5
1647
1648 csi_party_relationships_pvt.update_inst_party_relationship
1649 ( p_api_version => p_api_version
1650 ,p_commit => p_commit
1651 ,p_init_msg_list => p_init_msg_list
1652 ,p_validation_level => p_validation_level
1653 ,p_party_rec => l_cld_party_rec
1654 ,p_txn_rec => p_txn_rec
1655 ,x_return_status => x_return_status
1656 ,x_msg_count => x_msg_count
1657 ,x_msg_data => x_msg_data ) ;
1658
1659 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1660 l_msg_index := 1;
1661 l_msg_count := x_msg_count;
1662 WHILE l_msg_count > 0 LOOP
1663 x_msg_data := FND_MSG_PUB.GET(
1664 l_msg_index,
1665 FND_API.G_FALSE );
1666 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1667 l_msg_index := l_msg_index + 1;
1668 l_msg_count := l_msg_count - 1;
1669 END LOOP;
1670 RAISE FND_API.G_EXC_ERROR;
1671 END IF;
1672
1673 --added may15
1674 -- Added by sguthiva for bug 2307804
1675 -- The following code has wrritten specifically for expiring the ownership of
1676 -- an external party during the transfer of ownership from external to internal.
1677 IF l_cld_party_rec.party_id = l_internal_party_id
1678 THEN
1679 -- Adding for bug 3294748
1680 -- Wrote the following code to expire all non-owner parties and its contacts (if any)
1681 -- in the case of ext-to-int ownership transfer, however we do not expire non-owner accounts.
1682 IF l_cld_party_rec.relationship_type_code='OWNER'
1683 THEN
1684 FOR l_exp_pty IN exp_pty_csr(l_cld_party_rec.instance_id)
1685 LOOP
1686 l_exp_party_rec:= l_temp_party_rec;
1687 l_exp_party_rec.instance_id:= l_cld_party_rec.instance_id;
1688 l_exp_party_rec.instance_party_id := l_exp_pty.instance_party_id;
1689 l_exp_party_rec.relationship_type_code := l_exp_pty.relationship_type_code;
1690 l_exp_party_rec.object_version_number := l_exp_pty.object_version_number;
1691 -- Bug 3804960
1692 -- srramakr Need to use the same the date used by the item instance
1693 IF p_txn_rec.src_txn_creation_date IS NULL OR
1694 p_txn_rec.src_txn_creation_date = FND_API.G_MISS_DATE THEN
1695 l_exp_party_rec.active_end_date := sysdate;
1696 ELSE
1697 l_exp_party_rec.active_end_date := p_txn_rec.src_txn_creation_date;
1698 END IF;
1699 -- End of 3804960
1700 csi_party_relationships_pvt.update_inst_party_relationship
1701 ( p_api_version => p_api_version
1702 ,p_commit => p_commit
1703 ,p_init_msg_list => p_init_msg_list
1704 ,p_validation_level => p_validation_level
1705 ,p_party_rec => l_exp_party_rec
1706 ,p_txn_rec => p_txn_rec
1707 ,x_return_status => x_return_status
1708 ,x_msg_count => x_msg_count
1709 ,x_msg_data => x_msg_data ) ;
1710
1711 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1712 l_msg_index := 1;
1713 l_msg_count := x_msg_count;
1714 WHILE l_msg_count > 0
1715 LOOP
1716 x_msg_data := FND_MSG_PUB.GET(
1717 l_msg_index,
1718 FND_API.G_FALSE );
1719 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1720 l_msg_index := l_msg_index + 1;
1721 l_msg_count := l_msg_count - 1;
1722 END LOOP;
1723 RAISE FND_API.G_EXC_ERROR;
1724 END IF;
1725 END LOOP;
1726 END IF;
1727 --End addition for bug 3294748
1728 l_exp_acct_rec:=l_temp_acct_rec;
1729 BEGIN
1730 SELECT ip_account_id,
1731 relationship_type_code,
1732 object_version_number
1733 INTO l_exp_acct_rec.ip_account_id,
1734 l_exp_acct_rec.relationship_type_code,
1735 l_exp_acct_rec.object_version_number
1736 FROM csi_ip_accounts
1737 WHERE instance_party_id=l_cld_party_rec.instance_party_id
1738 AND relationship_type_code ='OWNER';
1739 -- Bug 3804960
1740 -- srramakr Need to use the same the date used by the item instance
1741 IF p_txn_rec.src_txn_creation_date IS NULL OR
1742 p_txn_rec.src_txn_creation_date = FND_API.G_MISS_DATE THEN
1743 l_exp_acct_rec.active_end_date := sysdate;
1744 ELSE
1745 l_exp_acct_rec.active_end_date := p_txn_rec.src_txn_creation_date;
1746 END IF;
1747 -- End of 3804960
1748 l_exp_acct_rec.expire_flag :=fnd_api.g_true;
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 NULL;
1752 END;
1753
1754 IF l_exp_acct_rec.ip_account_id IS NOT NULL
1755 AND l_exp_acct_rec.ip_account_id <> fnd_api.g_miss_num
1756 THEN
1757 l_exp_acct_rec.grp_call_contracts := l_grp_call_contracts;
1758 csi_party_relationships_pvt.update_inst_party_account
1759 ( p_api_version => p_api_version
1760 ,p_commit => p_commit
1761 ,p_init_msg_list => p_init_msg_list
1762 ,p_validation_level => p_validation_level
1763 ,p_party_account_rec => l_exp_acct_rec
1764 ,p_txn_rec => p_txn_rec
1765 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1766 ,x_return_status => x_return_status
1767 ,x_msg_count => x_msg_count
1768 ,x_msg_data => x_msg_data);
1769
1770 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1771 l_msg_index := 1;
1772 l_msg_count := x_msg_count;
1773 WHILE l_msg_count > 0 LOOP
1774 x_msg_data := FND_MSG_PUB.GET(
1775 l_msg_index,
1776 FND_API.G_FALSE );
1777 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1778 l_msg_index := l_msg_index + 1;
1779 l_msg_count := l_msg_count - 1;
1780 END LOOP;
1781 RAISE FND_API.G_EXC_ERROR;
1782 END IF;
1783 END IF;
1784 END IF;
1785 -- End addition by sguthiva for bug 2307804
1786 IF l_cld_party_rec.party_id <> l_internal_party_id
1787 THEN
1788 IF l_ip_acct_rec.instance_party_id IS NOT NULL AND
1789 l_ip_acct_rec.instance_party_id <> fnd_api.g_miss_num
1790 THEN
1791 l_ip_acct_rec:=l_new_ip_acct_rec; -- added for 2608706
1792 l_ip_acct_rec.instance_party_id := l_cld_party_rec.instance_party_id;
1793 l_ip_acct_rec.ip_account_id := fnd_api.g_miss_num;
1794 -- srramakr Bug 3621181 need to cascade Bill_to and Ship_to address to the children
1795 -- l_ip_acct_rec.bill_to_address := fnd_api.g_miss_num;
1796 -- l_ip_acct_rec.ship_to_address := fnd_api.g_miss_num;
1797 l_ip_acct_rec.active_start_date := fnd_api.g_miss_date; -- added for 2608706
1798 l_ip_acct_rec.active_end_date := fnd_api.g_miss_date; -- added for 2608706
1799 l_ip_acct_rec.grp_call_contracts := l_grp_call_contracts;
1800 -- Added the following code for bug 2972082
1801 IF nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y'
1802 AND l_cld_party_id <> l_cld_party_rec.party_id
1803 AND l_cld_party_id <> l_internal_party_id
1804 THEN
1805 l_ip_acct_rec.cascade_ownership_flag:='Y';
1806 END IF;
1807 -- End of addition for bug 2972082
1808 -- Need to pass the system_id for components also if the Xfer of ownership is
1809 -- initiated from xfer of system. This will be true if both parent and child instances
1810 -- belong to the same system.
1811 l_ip_acct_rec.system_id := fnd_api.g_miss_num;
1812 --
1813 IF p_party_account_tbl.EXISTS(1) AND
1814 p_party_account_tbl(1).system_id IS NOT NULL AND
1815 p_party_account_tbl(1).system_id <> FND_API.G_MISS_NUM AND
1816 NVL(p_party_account_tbl(1).relationship_type_code,FND_API.G_MISS_CHAR) = 'OWNER' THEN
1817 Begin
1818 select system_id
1819 into l_ip_acct_rec.system_id
1820 from csi_item_instances
1821 where instance_id = l_rel_tbl(j).subject_id
1822 and nvl(system_id,fnd_api.g_miss_num) = p_party_account_tbl(1).system_id;
1823 Exception
1824 when no_data_found then
1825 l_ip_acct_rec.system_id := fnd_api.g_miss_num;
1826 End;
1827 END IF;
1828 csi_party_relationships_pvt.create_inst_party_account
1829 ( p_api_version => p_api_version
1830 ,p_commit => p_commit
1831 ,p_init_msg_list => p_init_msg_list
1832 ,p_validation_level => p_validation_level
1833 ,p_party_account_rec => l_ip_acct_rec
1834 ,p_txn_rec => p_txn_rec
1835 ,x_return_status => x_return_status
1836 ,x_msg_count => x_msg_count
1837 ,x_msg_data => x_msg_data
1838 ,p_inst_party_tbl => l_inst_party_tbl
1839 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
1840 ,p_site_use_tbl => l_site_use_tbl
1841 ,p_account_count_rec => l_account_count_rec
1842 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1843 );
1844
1845 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1846 l_msg_index := 1;
1847 l_msg_count := x_msg_count;
1848 WHILE l_msg_count > 0 LOOP
1849 x_msg_data := FND_MSG_PUB.GET(
1850 l_msg_index,
1851 FND_API.G_FALSE );
1852 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1853 l_msg_index := l_msg_index + 1;
1854 l_msg_count := l_msg_count - 1;
1855 END LOOP;
1856 RAISE FND_API.G_EXC_ERROR;
1857 END IF;
1858 END IF;
1859 END IF;
1860 -- End commentation by sguthiva for bug 2307804
1861 END IF;
1862
1863 END LOOP;
1864 END IF; -- l_rel_tbl count check
1865
1866 END IF;
1867 END IF;
1868 END IF;
1869 END LOOP;
1870 END IF;
1871 -- End of code addition for fixing the bug 2151750
1872
1873 -- code written by sk on 12/06/01 for fixing TRF bug 2133944
1874 -- End of API body
1875
1876
1877 -- Standard check of p_commit.
1878 IF FND_API.To_Boolean( p_commit ) THEN
1879 COMMIT WORK;
1880 END IF;
1881
1882 /***** srramakr commented for bug # 3304439
1883 -- Check for the profile option and disable the trace
1884 IF (l_flag = 'Y') THEN
1885 dbms_session.set_sql_trace(false);
1886 END IF;
1887 -- End disable trace
1888 ****/
1889
1890 -- Standard call to get message count and if count is get message info.
1891 FND_MSG_PUB.Count_And_Get
1892 (p_count => x_msg_count ,
1893 p_data => x_msg_data );
1894 EXCEPTION
1895 WHEN FND_API.G_EXC_ERROR THEN
1896 ROLLBACK TO update_inst_party_rel_pub;
1897 x_return_status := FND_API.G_RET_STS_ERROR ;
1898 FND_MSG_PUB.Count_And_Get
1899 ( p_count => x_msg_count,
1900 p_data => x_msg_data );
1901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1902 ROLLBACK TO update_inst_party_rel_pub;
1903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1904 FND_MSG_PUB.Count_And_Get
1905 ( p_count => x_msg_count,
1906 p_data => x_msg_data );
1907 WHEN OTHERS THEN
1908 ROLLBACK TO update_inst_party_rel_pub;
1909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1910 IF FND_MSG_PUB.Check_Msg_Level
1911 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1912 THEN
1913 FND_MSG_PUB.Add_Exc_Msg
1914 ( g_pkg_name, l_api_name );
1915 END IF;
1916 FND_MSG_PUB.Count_And_Get
1917 ( p_count => x_msg_count,
1918 p_data => x_msg_data );
1919 END update_inst_party_relationship ;
1920
1921
1922 /*-------------------------------------------------------------*/
1923 /* Procedure name: Expire_inst_party_relationship */
1924 /* Description : Procedure used to expire an existing */
1925 /* instance -party relationships */
1926 /*-------------------------------------------------------------*/
1927
1928 PROCEDURE expire_inst_party_relationship
1929 ( p_api_version IN NUMBER
1930 ,p_commit IN VARCHAR2
1931 ,p_init_msg_list IN VARCHAR2
1932 ,p_validation_level IN NUMBER
1933 ,p_instance_party_tbl IN csi_datastructures_pub.party_tbl
1934 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
1935 ,x_return_status OUT NOCOPY VARCHAR2
1936 ,x_msg_count OUT NOCOPY NUMBER
1937 ,x_msg_data OUT NOCOPY VARCHAR2
1938 ) IS
1939 l_api_name CONSTANT VARCHAR2(30) := 'EXPIRE_INST_PARTY_RELATIONSHIP';
1940 l_api_version CONSTANT NUMBER := 1.0;
1941 l_csi_debug_level NUMBER;
1942 l_party_rec csi_datastructures_pub.party_rec;
1943 l_msg_index NUMBER;
1944 l_msg_count NUMBER;
1945 l_line_count NUMBER;
1946 l_flag VARCHAR2(1) :='N';
1947
1948 BEGIN
1949 -- Standard Start of API savepoint
1950 SAVEPOINT expire_inst_party_rel_pub;
1951
1952 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
1953
1954 csi_utility_grp.check_ib_active;
1955
1956 -- Standard call to check for call compatibility.
1957 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1958 p_api_version ,
1959 l_api_name ,
1960 g_pkg_name )
1961 THEN
1962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1963 END IF;
1964
1965 -- Initialize message list if p_init_msg_list is set to TRUE.
1966 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1967 FND_MSG_PUB.initialize;
1968 END IF;
1969
1970 -- Initialize API return status to success
1971 x_return_status := FND_API.G_RET_STS_SUCCESS;
1972
1973 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
1974 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1975
1976 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
1977 IF (l_csi_debug_level > 0) THEN
1978 csi_gen_utility_pvt.put_line( 'expire_inst_party_relationship');
1979 END IF;
1980
1981 -- If the debug level = 2 then dump all the parameters values.
1982 IF (l_csi_debug_level > 1) THEN
1983 csi_gen_utility_pvt.put_line( 'expire_inst_party_relationship:' ||
1984 p_api_version ||'-'||
1985 p_commit ||'-'||
1986 p_init_msg_list ||'-'||
1987 p_validation_level );
1988 -- Dump the records in the log file
1989 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
1990 csi_gen_utility_pvt.dump_party_tbl(p_instance_party_tbl);
1991 END IF;
1992
1993 /***** srramakr commented for bug # 3304439
1994 -- Check for the profile option and enable trace
1995 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
1996 -- End enable trace
1997 ****/
1998
1999 -- Start API body
2000 --
2001 IF p_instance_party_tbl.count > 0 THEN
2002 FOR l_count IN p_instance_party_tbl.FIRST..p_instance_party_tbl.LAST LOOP
2003 IF p_instance_party_tbl.EXISTS(l_count) THEN
2004 csi_party_relationships_pvt.expire_inst_party_relationship
2005 ( p_api_version => p_api_version,
2006 p_commit => p_commit,
2007 p_init_msg_list => p_init_msg_list,
2008 p_validation_level => p_validation_level,
2009 p_instance_party_rec=> p_instance_party_tbl(l_count),
2010 p_txn_rec => p_txn_rec,
2011 x_return_status => x_return_status ,
2012 x_msg_count => x_msg_count ,
2013 x_msg_data => x_msg_data ) ;
2014
2015 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2016 l_msg_index := 1;
2017 l_msg_count := x_msg_count;
2018 WHILE l_msg_count > 0 LOOP
2019 x_msg_data := FND_MSG_PUB.GET(
2020 l_msg_index,
2021 FND_API.G_FALSE );
2022 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2023 l_msg_index := l_msg_index + 1;
2024 l_msg_count := l_msg_count - 1;
2025 END LOOP;
2026 RAISE FND_API.G_EXC_ERROR;
2027 END IF;
2028 END IF;
2029 END LOOP;
2030 END IF;
2031 --
2032 -- End of API body
2033
2034 -- Standard check of p_commit.
2035 IF FND_API.To_Boolean( p_commit ) THEN
2036 COMMIT WORK;
2037 END IF;
2038
2039 /***** srramakr commented for bug # 3304439
2040 -- Check for the profile option and disable the trace
2041 IF (l_flag = 'Y') THEN
2042 dbms_session.set_sql_trace(false);
2043 END IF;
2044 -- End disable trace
2045 ****/
2046
2047 -- Standard call to get message count and if count is get message info.
2048 FND_MSG_PUB.Count_And_Get
2049 (p_count => x_msg_count ,
2050 p_data => x_msg_data );
2051 EXCEPTION
2052 WHEN FND_API.G_EXC_ERROR THEN
2053 ROLLBACK TO expire_inst_party_rel_pub;
2054 x_return_status := FND_API.G_RET_STS_ERROR ;
2055 FND_MSG_PUB.Count_And_Get
2056 ( p_count => x_msg_count,
2057 p_data => x_msg_data );
2058
2059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2060 ROLLBACK TO expire_inst_party_rel_pub;
2061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2062 FND_MSG_PUB.Count_And_Get
2063 ( p_count => x_msg_count,
2064 p_data => x_msg_data );
2065
2066 WHEN OTHERS THEN
2067 ROLLBACK TO expire_inst_party_relationship;
2068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2069 IF FND_MSG_PUB.Check_Msg_Level
2070 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2071 THEN
2072 FND_MSG_PUB.Add_Exc_Msg
2073 ( g_pkg_name, l_api_name );
2074 END IF;
2075 FND_MSG_PUB.Count_And_Get
2076 ( p_count => x_msg_count,
2077 p_data => x_msg_data );
2078
2079 END expire_inst_party_relationship;
2080
2081 /*---------------------------------------------------------*/
2082 /* Procedure name: Get_inst_party_account */
2083 /* Description : Procedure used to get information about */
2084 /* the accounts related to an instance-party */
2085 /*---------------------------------------------------------*/
2086
2087 PROCEDURE get_inst_party_accounts
2088 ( p_api_version IN NUMBER
2089 ,p_commit IN VARCHAR2
2090 ,p_init_msg_list IN VARCHAR2
2091 ,p_validation_level IN NUMBER
2092 ,p_account_query_rec IN csi_datastructures_pub.party_account_query_rec
2093 ,p_resolve_id_columns IN VARCHAR2
2094 ,p_time_stamp IN DATE
2095 ,x_account_header_tbl OUT NOCOPY csi_datastructures_pub.party_account_header_tbl
2096 ,x_return_status OUT NOCOPY VARCHAR2
2097 ,x_msg_count OUT NOCOPY NUMBER
2098 ,x_msg_data OUT NOCOPY VARCHAR2
2099 ) IS
2100
2101 l_api_name CONSTANT VARCHAR2(30) := 'GET_INST_PARTY_ACCOUNT';
2102 l_api_version CONSTANT NUMBER := 1.0;
2103 l_csi_debug_level NUMBER;
2104 l_instance_party_account_id NUMBER;
2105 l_party_account_tbl csi_datastructures_pub.party_account_tbl;
2106 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
2107 l_line_count NUMBER;
2108 l_msg_index NUMBER;
2109 l_count NUMBER := 0;
2110 l_where_clause VARCHAR2(2000) ;
2111 l_get_acct_cursor_id NUMBER ;
2112 l_rows_processed NUMBER ;
2113 l_flag VARCHAR2(1) :='N';
2114 l_party_account_rec csi_datastructures_pub.party_account_header_rec;
2115 l_select_stmt VARCHAR2(2000) := ' SELECT ip_account_id , instance_party_id, party_account_id, '||
2116 ' relationship_type_code, active_start_date, active_end_date,context , attribute1, '||
2117 ' attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,attribute8, '||
2118 ' attribute9, attribute10,attribute11, attribute12,attribute13,attribute14,attribute15, '||
2119 ' object_version_number, bill_to_address, ship_to_address from csi_ip_accounts ';
2120
2121
2122 BEGIN
2123 -- Standard Start of API savepoint
2124 -- SAVEPOINT get_inst_party_acct_pub;
2125
2126 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2127
2128 csi_utility_grp.check_ib_active;
2129
2130
2131 -- Standard call to check for call compatibility.
2132 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2133 p_api_version ,
2134 l_api_name ,
2135 g_pkg_name )
2136 THEN
2137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2138 END IF;
2139
2140 -- Initialize message list if p_init_msg_list is set to TRUE.
2141 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2142 FND_MSG_PUB.initialize;
2143 END IF;
2144
2145 -- Initialize API return status to success
2146 x_return_status := FND_API.G_RET_STS_SUCCESS;
2147
2148 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2149 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2150
2151 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2152 IF (l_csi_debug_level > 0) THEN
2153 csi_gen_utility_pvt.put_line( 'get_inst_party_accounts');
2154 END IF;
2155
2156 -- If the debug level = 2 then dump all the parameters values.
2157 IF (l_csi_debug_level > 1) THEN
2158
2159 csi_gen_utility_pvt.put_line( 'get_inst_party_account:' ||
2160 p_api_version ||'-'||
2161 p_commit ||'-'||
2162 p_init_msg_list ||'-'||
2163 p_validation_level ||'-'||
2164 p_time_stamp );
2165 -- Dump the account query records
2166 csi_gen_utility_pvt.dump_account_query_rec(p_account_query_rec);
2167
2168 END IF;
2169
2170 /***** srramakr commented for bug # 3304439
2171 -- Check for the profile option and enable trace
2172 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2173 -- End enable trace
2174 ****/
2175
2176 -- Start API body
2177 --
2178 IF (p_account_query_rec.ip_account_id = FND_API.G_MISS_NUM)
2179 AND (p_account_query_rec.instance_party_id = FND_API.G_MISS_NUM)
2180 AND (p_account_query_rec.party_account_id = FND_API.G_MISS_NUM)
2181 AND (p_account_query_rec.relationship_type_code = FND_API.G_MISS_CHAR) THEN
2182
2183 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
2184 FND_MSG_PUB.ADD;
2185 RAISE FND_API.G_EXC_ERROR;
2186 END IF;
2187
2188 -- Generate the where clause
2189 csi_party_relationships_pvt.Gen_Acct_Where_Clause
2190 ( p_pty_acct_query_rec => p_account_query_rec,
2191 x_where_clause => l_where_clause );
2192
2193 -- Build the select statement
2194 l_select_stmt := l_select_stmt || ' where '||l_where_clause;
2195
2196 -- Open the cursor
2197 l_get_acct_cursor_id := dbms_sql.open_cursor;
2198
2199 --Parse the select statement
2200 dbms_sql.parse(l_get_acct_cursor_id, l_select_stmt , dbms_sql.native);
2201
2202 -- Bind the variables
2203 csi_party_relationships_pvt.Bind_acct_variable(p_account_query_rec, l_get_acct_cursor_id);
2204
2205 -- Define output variables
2206 csi_party_relationships_pvt.Define_Acct_Columns(l_get_acct_cursor_id);
2207
2208 -- execute the select statement
2209 l_rows_processed := dbms_sql.execute(l_get_acct_cursor_id);
2210
2211 LOOP
2212 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_get_acct_cursor_id) = 0;
2213 csi_party_relationships_pvt.Get_acct_Column_Values(l_get_acct_cursor_id, l_party_account_rec);
2214 l_count := l_count + 1;
2215 x_account_header_tbl(l_count) := l_party_account_rec;
2216 END LOOP;
2217
2218 -- Close the cursor
2219 DBMS_SQL.CLOSE_CURSOR(l_get_acct_cursor_id);
2220
2221 IF ((p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE)) THEN
2222 IF p_time_stamp <= sysdate THEN
2223 -- Contruct from the history if p_time_stamp is less than sysdate
2224 csi_party_relationships_pvt.Construct_acct_from_hist(x_account_header_tbl, p_time_stamp);
2225 ELSE
2226 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
2227 FND_MSG_PUB.ADD;
2228 RAISE FND_API.G_EXC_ERROR;
2229 END IF;
2230 END IF;
2231
2232 -- foreign key resolution for the id columns
2233 IF p_resolve_id_columns = fnd_api.g_true THEN
2234 IF x_account_header_tbl.count > 0 THEN
2235 l_account_header_tbl := x_account_header_tbl;
2236 csi_party_relationships_pvt.Resolve_id_columns(l_account_header_tbl);
2237
2238 x_account_header_tbl := l_account_header_tbl;
2239 END IF;
2240 END IF;
2241
2242 --
2243 -- End of API body
2244
2245 -- Standard check of p_commit.
2246 /*
2247 IF FND_API.To_Boolean( p_commit ) THEN
2248 COMMIT WORK;
2249 END IF;
2250 */
2251
2252 /***** srramakr commented for bug # 3304439
2253 -- Check for the profile option and disable the trace
2254 IF (l_flag = 'Y') THEN
2255 dbms_session.set_sql_trace(false);
2256 END IF;
2257 -- End disable trace
2258 ****/
2259
2260 -- Standard call to get message count and if count is get message info.
2261 FND_MSG_PUB.Count_And_Get
2262 (p_count => x_msg_count ,
2263 p_data => x_msg_data );
2264 EXCEPTION
2265 WHEN FND_API.G_EXC_ERROR THEN
2266 -- ROLLBACK TO get_inst_party_acct_pub;
2267 x_return_status := FND_API.G_RET_STS_ERROR ;
2268 FND_MSG_PUB.Count_And_Get
2269 ( p_count => x_msg_count,
2270 p_data => x_msg_data );
2271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2272 -- ROLLBACK TO get_inst_party_acct_pub;
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2274 FND_MSG_PUB.Count_And_Get
2275 ( p_count => x_msg_count,
2276 p_data => x_msg_data );
2277 WHEN OTHERS THEN
2278 IF dbms_sql.is_open(l_get_acct_cursor_id) then
2279 dbms_sql.close_cursor(l_get_acct_cursor_id);
2280 END IF;
2281 -- ROLLBACK TO get_inst_party_acct_pub;
2282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2283 IF FND_MSG_PUB.Check_Msg_Level
2284 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2285 THEN
2286 FND_MSG_PUB.Add_Exc_Msg
2287 ( g_pkg_name, l_api_name );
2288 END IF;
2289 FND_MSG_PUB.Count_And_Get
2290 ( p_count => x_msg_count,
2291 p_data => x_msg_data );
2292 END get_inst_party_accountS ;
2293
2294 /*----------------------------------------------------------*/
2295 /* Procedure name: Create_inst_party_account */
2296 /* Description : Procedure used to create new */
2297 /* instance-party account relationships */
2298 /*----------------------------------------------------------*/
2299
2300 PROCEDURE create_inst_party_account
2301 ( p_api_version IN NUMBER
2302 ,p_commit IN VARCHAR2
2303 ,p_init_msg_list IN VARCHAR2
2304 ,p_validation_level IN NUMBER
2305 ,p_party_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
2306 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2307 ,x_return_status OUT NOCOPY VARCHAR2
2308 ,x_msg_count OUT NOCOPY NUMBER
2309 ,x_msg_data OUT NOCOPY VARCHAR2
2310 ) IS
2311
2312 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INST_PARTY_ACCOUNT';
2313 l_api_version CONSTANT NUMBER := 1.0;
2314 l_csi_debug_level NUMBER;
2315 l_party_account_rec csi_datastructures_pub.party_account_rec;
2316 l_msg_index NUMBER;
2317 l_msg_count NUMBER;
2318 l_line_count NUMBER;
2319 l_flag VARCHAR2(1) :='N';
2320 l_inst_party_tbl csi_party_relationships_pvt.inst_party_tbl;
2321 l_acct_rel_type_tbl csi_party_relationships_pvt.acct_rel_type_tbl;
2322 l_site_use_tbl csi_party_relationships_pvt.site_use_tbl;
2323 l_account_count_rec csi_party_relationships_pvt.account_count_rec;
2324 --
2325 px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
2326
2327 BEGIN
2328 -- Standard Start of API savepoint
2329 SAVEPOINT create_inst_party_acct_pub;
2330
2331 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2332
2333 csi_utility_grp.check_ib_active;
2334
2335
2336 -- Standard call to check for call compatibility.
2337 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2338 p_api_version ,
2339 l_api_name ,
2340 g_pkg_name )
2341 THEN
2342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2343 END IF;
2344
2345 -- Initialize message list if p_init_msg_list is set to TRUE.
2346 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2347 FND_MSG_PUB.initialize;
2348 END IF;
2349
2350 -- Initialize API return status to success
2351 x_return_status := FND_API.G_RET_STS_SUCCESS;
2352
2353 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2354 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2355
2356 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2357 IF (l_csi_debug_level > 0) THEN
2358 csi_gen_utility_pvt.put_line( 'create_inst_party_account');
2359 END IF;
2360
2361 -- If the debug level = 2 then dump all the parameters values.
2362 IF (l_csi_debug_level > 1) THEN
2363 csi_gen_utility_pvt.put_line( 'create_inst_party_account:'||
2364 p_api_version ||'-'||
2365 p_commit ||'-'||
2366 p_init_msg_list );
2367 -- Dump the records in the log file
2368 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
2369 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2370 END IF;
2371
2372 /***** srramakr commented for bug # 3304439
2373 -- Check for the profile option and enable trace
2374 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2375 -- End enable trace
2376 ****/
2377
2378 -- Start API body
2379 --
2380 -- Check if the table is not empty
2381 IF p_party_account_tbl.count > 0 THEN
2382 FOR l_count IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
2383 IF p_party_account_tbl.EXISTS(l_count) THEN
2384
2385 -- Call Private package to validate and create party accounts
2386 csi_party_relationships_pvt.create_inst_party_account
2387 ( p_api_version => p_api_version
2388 ,p_commit => p_commit
2389 ,p_init_msg_list => p_init_msg_list
2390 ,p_validation_level => p_validation_level
2391 ,p_party_account_rec => p_party_account_tbl(l_count)
2392 ,p_txn_rec => p_txn_rec
2393 ,x_return_status => x_return_status
2394 ,x_msg_count => x_msg_count
2395 ,x_msg_data => x_msg_data
2396 ,p_inst_party_tbl => l_inst_party_tbl
2397 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
2398 ,p_site_use_tbl => l_site_use_tbl
2399 ,p_account_count_rec => l_account_count_rec
2400 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2401 );
2402
2403 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2404 l_msg_index := 1;
2405 l_msg_count := x_msg_count;
2406 WHILE l_msg_count > 0 LOOP
2407 x_msg_data := FND_MSG_PUB.GET(
2408 l_msg_index,
2409 FND_API.G_FALSE );
2410 csi_gen_utility_pvt.put_line( 'message data = '||x_msg_data);
2411 l_msg_index := l_msg_index + 1;
2412 l_msg_count := l_msg_count - 1;
2413 END LOOP;
2414 RAISE FND_API.G_EXC_ERROR;
2415 END IF;
2416 END IF;
2417 END LOOP;
2418 END IF;
2419 --
2420 -- End of API body
2421
2422 -- Standard check of p_commit.
2423 IF FND_API.To_Boolean( p_commit ) THEN
2424 COMMIT WORK;
2425 END IF;
2426
2427 /***** srramakr commented for bug # 3304439
2428 -- Check for the profile option and disable the trace
2429 IF (l_flag = 'Y') THEN
2430 dbms_session.set_sql_trace(false);
2431 END IF;
2432 -- End disable trace
2433 ****/
2434
2435 -- Standard call to get message count and if count is get message info.
2436 FND_MSG_PUB.Count_And_Get
2437 (p_count => x_msg_count ,
2438 p_data => x_msg_data );
2439 EXCEPTION
2440 WHEN FND_API.G_EXC_ERROR THEN
2441 ROLLBACK TO create_inst_party_acct_pub;
2442 x_return_status := FND_API.G_RET_STS_ERROR ;
2443 FND_MSG_PUB.Count_And_Get
2444 ( p_count => x_msg_count,
2445 p_data => x_msg_data );
2446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2447 ROLLBACK TO create_inst_party_acct_pub;
2448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2449 FND_MSG_PUB.Count_And_Get
2450 ( p_count => x_msg_count,
2451 p_data => x_msg_data );
2452 WHEN OTHERS THEN
2453 ROLLBACK TO create_inst_party_acct_pub;
2454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2455 IF FND_MSG_PUB.Check_Msg_Level
2456 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2457 THEN
2458 FND_MSG_PUB.Add_Exc_Msg
2459 ( g_pkg_name, l_api_name );
2460 END IF;
2461 FND_MSG_PUB.Count_And_Get
2462 ( p_count => x_msg_count,
2463 p_data => x_msg_data );
2464 END create_inst_party_account;
2465
2466 /*------------------------------------------------------------*/
2467 /* Procedure name: Update_inst_party_account */
2468 /* Description : Procedure used to update the existing */
2469 /* instance-party account relationships */
2470 /*------------------------------------------------------------*/
2471
2472 PROCEDURE update_inst_party_account
2473 ( p_api_version IN NUMBER
2474 ,p_commit IN VARCHAR2
2475 ,p_init_msg_list IN VARCHAR2
2476 ,p_validation_level IN NUMBER
2477 ,p_party_account_tbl IN csi_datastructures_pub.party_account_tbl
2478 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2479 ,x_return_status OUT NOCOPY VARCHAR2
2480 ,x_msg_count OUT NOCOPY NUMBER
2481 ,x_msg_data OUT NOCOPY VARCHAR2
2482 ) IS
2483 /*** CURSOR party_account_csr (p_ins_pty_id IN NUMBER) IS
2484 SELECT subject_id
2485 FROM csi_ii_relationships
2486 WHERE relationship_type_code = 'COMPONENT-OF'
2487 START WITH object_id = (SELECT instance_id+0
2488 FROM csi_i_parties
2489 WHERE instance_party_id = p_ins_pty_id
2490 AND relationship_type_code = 'OWNER'
2491 AND (active_end_date IS NULL OR active_end_date > sysdate)
2492 )
2493 CONNECT BY object_id = PRIOR subject_id; ***/
2494
2495 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_ACCOUNT';
2496 l_api_version CONSTANT NUMBER := 1.0;
2497 l_csi_debug_level NUMBER;
2498 l_party_account_rec csi_datastructures_pub.party_account_rec;
2499 l_msg_index NUMBER;
2500 l_msg_count NUMBER;
2501 l_line_count NUMBER;
2502 l_flag VARCHAR2(1) :='N';
2503 l_party_account_tbl csi_datastructures_pub.party_account_tbl := p_party_account_tbl;
2504 l_temp_account_tbl csi_datastructures_pub.party_account_tbl;
2505 old_party_account_id NUMBER;
2506 l_acct_row NUMBER :=1;
2507 old_party_id NUMBER;
2508 l_party_id NUMBER;
2509 p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
2510 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
2511 l_object_id NUMBER;
2512 l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
2513 px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
2514 BEGIN
2515 -- Standard Start of API savepoint
2516 SAVEPOINT update_inst_party_acct_pub;
2517
2518 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2519
2520 csi_utility_grp.check_ib_active;
2521
2522
2523 -- Standard call to check for call compatibility.
2524 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2525 p_api_version ,
2526 l_api_name ,
2527 g_pkg_name )
2528 THEN
2529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2530 END IF;
2531
2532 -- Initialize message list if p_init_msg_list is set to TRUE.
2533 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2534 FND_MSG_PUB.initialize;
2535 END IF;
2536
2537 -- Initialize API return status to success
2538 x_return_status := FND_API.G_RET_STS_SUCCESS;
2539
2540 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2541 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2542
2543 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2544 IF (l_csi_debug_level > 0) THEN
2545 csi_gen_utility_pvt.put_line( 'update_inst_party_account');
2546 END IF;
2547
2548 -- If the debug level = 2 then dump all the parameters values.
2549 IF (l_csi_debug_level > 1) THEN
2550 csi_gen_utility_pvt.put_line( 'update_inst_party_account:'||
2551 p_api_version ||'-'||
2552 p_commit ||'-'||
2553 p_init_msg_list );
2554 -- Dump the records in the log file
2555 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
2556 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2557 END IF;
2558
2559 /***** srramakr commented for bug # 3304439
2560 -- Check for the profile option and enable trace
2561 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2562 -- End enable trace
2563 ****/
2564
2565 -- Start API body
2566 --
2567 -- Check if the table is not empty
2568 IF p_party_account_tbl.count > 0 THEN
2569 FOR l_count IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
2570 IF p_party_account_tbl.EXISTS(l_count) THEN
2571 IF p_party_account_tbl(l_count).ip_account_id IS NOT NULL AND
2572 p_party_account_tbl(l_count).ip_account_id <> fnd_api.g_miss_num
2573 THEN
2574 BEGIN
2575 SELECT acct.ip_account_id,
2576 acct.party_account_id,
2577 pty.party_id
2578 INTO l_temp_account_tbl(l_acct_row).ip_account_id,
2579 l_temp_account_tbl(l_acct_row).party_account_id,
2580 l_temp_account_tbl(l_acct_row).attribute1
2581 FROM csi_ip_accounts acct,
2582 csi_i_parties pty
2583 WHERE acct.ip_account_id = p_party_account_tbl(l_count).ip_account_id
2584 AND acct.instance_party_id = pty.instance_party_id;
2585
2586 l_acct_row := l_acct_row+1;
2587 EXCEPTION
2588 WHEN OTHERS THEN
2589 NULL;
2590 END;
2591 END IF;
2592 -- dbms_output.put_line('PUB: caling update_inst_party_account');
2593 csi_party_relationships_pvt.update_inst_party_account
2594 ( p_api_version => p_api_version
2595 ,p_commit => p_commit
2596 ,p_init_msg_list => p_init_msg_list
2597 ,p_validation_level => p_validation_level
2598 ,p_party_account_rec => p_party_account_tbl(l_count)
2599 ,p_txn_rec => p_txn_rec
2600 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2601 ,x_return_status => x_return_status
2602 ,x_msg_count => x_msg_count
2603 ,x_msg_data => x_msg_data);
2604
2605 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2606 l_msg_index := 1;
2607 l_msg_count := x_msg_count;
2608 WHILE l_msg_count > 0 LOOP
2609 x_msg_data := FND_MSG_PUB.GET(
2610 l_msg_index,
2611 FND_API.G_FALSE );
2612 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2613 l_msg_index := l_msg_index + 1;
2614 l_msg_count := l_msg_count - 1;
2615 END LOOP;
2616 RAISE FND_API.G_EXC_ERROR;
2617 END IF;
2618 END IF;
2619 END LOOP;
2620 END IF;
2621
2622 -- If the owner account (party_account_id) has been changed then if the Instance has any children
2623 -- then all the children (in COMPONENT-OF relationship_type_code) has to get the same party_account_id.
2624 -- Start of fix for bug 2151750
2625 IF l_party_account_tbl.count > 0
2626 THEN
2627 FOR l_old_party_row IN l_party_account_tbl.FIRST..l_party_account_tbl.LAST
2628 LOOP
2629 IF l_party_account_tbl.EXISTS(l_old_party_row) -- Added for bug 3776650
2630 THEN
2631 old_party_account_id := NULL;
2632 old_party_id := NULL;
2633 IF l_temp_account_tbl.COUNT > 0
2634 THEN
2635 FOR l_old_acct IN l_temp_account_tbl.FIRST..l_temp_account_tbl.LAST
2636 LOOP
2637 IF l_temp_account_tbl.EXISTS(l_old_acct) -- Added for bug 3776650
2638 THEN
2639 IF l_temp_account_tbl(l_old_acct).ip_account_id = l_party_account_tbl(l_old_party_row).ip_account_id
2640 THEN
2641 old_party_account_id := l_temp_account_tbl(l_old_acct).ip_account_id;
2642 old_party_id := l_temp_account_tbl(l_old_acct).attribute1;
2643 EXIT;
2644 END IF;
2645 END IF;
2646 END LOOP;
2647 END IF;
2648
2649 IF l_party_account_tbl(l_old_party_row).party_account_id IS NOT NULL AND
2650 l_party_account_tbl(l_old_party_row).party_account_id <> FND_API.G_MISS_NUM AND
2651 l_party_account_tbl(l_old_party_row).relationship_type_code = 'OWNER' AND
2652 old_party_account_id IS NOT NULL AND
2653 l_party_account_tbl(l_old_party_row).party_account_id <> old_party_account_id
2654 THEN
2655 l_object_id := null;
2656 Begin
2657 select instance_id
2658 into l_object_id
2659 from CSI_I_PARTIES
2660 where instance_party_id = l_party_account_tbl(l_old_party_row).instance_party_id
2661 and relationship_type_code = 'OWNER'
2662 and (active_end_date IS NULL OR active_end_date > sysdate);
2663 Exception
2664 when no_data_found then
2665 l_object_id := null;
2666 End;
2667 --
2668 IF l_object_id IS NOT NULL THEN
2669 p_rel_query_rec.object_id := l_object_id;
2670 p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
2671 --
2672 csi_ii_relationships_pvt.Get_Children
2673 ( p_relationship_query_rec => p_rel_query_rec,
2674 p_rel_tbl => l_rel_tbl,
2675 p_depth => NULL,
2676 p_active_relationship_only => FND_API.G_TRUE,
2677 p_time_stamp => FND_API.G_MISS_DATE,
2678 p_get_dfs => FND_API.G_FALSE,
2679 p_ii_relationship_level_tbl => l_ii_relationship_level_tbl,
2680 x_return_status => x_return_status,
2681 x_msg_count => x_msg_count,
2682 x_msg_data => x_msg_data
2683 );
2684 --
2685 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2686 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_CHILDREN_ERROR');
2687 FND_MSG_PUB.Add;
2688 RAISE FND_API.G_EXC_ERROR;
2689 END IF;
2690 --
2691 IF l_rel_tbl.count > 0 THEN
2692 FOR j in l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
2693 -- FOR l_pty_acct_csr IN party_account_csr(l_party_account_tbl(l_old_party_row).instance_party_id)
2694 l_party_account_tbl(l_old_party_row).ip_account_id := fnd_api.g_miss_num;
2695 l_party_account_tbl(l_old_party_row).instance_party_id := fnd_api.g_miss_num;
2696 l_party_account_tbl(l_old_party_row).object_version_number := fnd_api.g_miss_num;
2697 l_party_account_tbl(l_old_party_row).active_start_date :=fnd_api.g_miss_date;
2698 l_party_account_tbl(l_old_party_row).active_end_date :=fnd_api.g_miss_date;
2699 BEGIN
2700 l_party_id := NULL;
2701 SELECT acct.ip_account_id,
2702 acct.object_version_number,
2703 pty.party_id
2704 INTO l_party_account_tbl(l_old_party_row).ip_account_id,
2705 l_party_account_tbl(l_old_party_row).object_version_number,
2706 l_party_id
2707 FROM csi_ip_accounts acct,
2708 csi_i_parties pty
2709 WHERE pty.instance_party_id = acct.instance_party_id
2710 AND pty.instance_id = l_rel_tbl(j).subject_id
2711 AND acct.relationship_type_code = 'OWNER'
2712 AND (acct.active_end_date IS NULL OR
2713 acct.active_end_date > SYSDATE);
2714 EXCEPTION
2715 WHEN OTHERS THEN
2716 NULL;
2717 END;
2718 --
2719 IF l_party_account_tbl(l_old_party_row).ip_account_id IS NOT NULL AND
2720 l_party_account_tbl(l_old_party_row).ip_account_id <> fnd_api.g_miss_num AND
2721 old_party_id = l_party_id
2722 THEN
2723 csi_party_relationships_pvt.update_inst_party_account
2724 ( p_api_version => p_api_version
2725 ,p_commit => p_commit
2726 ,p_init_msg_list => p_init_msg_list
2727 ,p_validation_level => p_validation_level
2728 ,p_party_account_rec => l_party_account_tbl(l_old_party_row)
2729 ,p_txn_rec => p_txn_rec
2730 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2731 ,x_return_status => x_return_status
2732 ,x_msg_count => x_msg_count
2733 ,x_msg_data => x_msg_data);
2734
2735 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2736 l_msg_index := 1;
2737 l_msg_count := x_msg_count;
2738 WHILE l_msg_count > 0
2739 LOOP
2740 x_msg_data := FND_MSG_PUB.GET(
2741 l_msg_index,
2742 FND_API.G_FALSE );
2743 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2744 l_msg_index := l_msg_index + 1;
2745 l_msg_count := l_msg_count - 1;
2746 END LOOP;
2747 RAISE FND_API.G_EXC_ERROR;
2748 END IF;
2749 END IF; -- ip_account_id not null check
2750 END LOOP; -- l_rel_tbl loop
2751 END IF; -- l_rel_tbl count check
2752 END IF; -- l_object_id check
2753 END IF;
2754 END IF;
2755 END LOOP;
2756 END IF;
2757
2758 -- End of fix for bug 2151750
2759 --
2760 -- End of API body
2761
2762 -- Standard check of p_commit.
2763 IF FND_API.To_Boolean( p_commit ) THEN
2764 COMMIT WORK;
2765 END IF;
2766
2767 /***** srramakr commented for bug # 3304439
2768 -- Check for the profile option and disable the trace
2769 IF (l_flag = 'Y') THEN
2770 dbms_session.set_sql_trace(false);
2771 END IF;
2772 -- End disable trace
2773 ****/
2774
2775 -- Standard call to get message count and if count is get message info.
2776 FND_MSG_PUB.Count_And_Get
2777 (p_count => x_msg_count ,
2778 p_data => x_msg_data );
2779 EXCEPTION
2780 WHEN FND_API.G_EXC_ERROR THEN
2781 ROLLBACK TO update_inst_party_acct_pub;
2782 x_return_status := FND_API.G_RET_STS_ERROR ;
2783 FND_MSG_PUB.Count_And_Get
2784 ( p_count => x_msg_count,
2785 p_data => x_msg_data );
2786 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2787 ROLLBACK TO update_inst_party_acct_pub;
2788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2789 FND_MSG_PUB.Count_And_Get
2790 ( p_count => x_msg_count,
2791 p_data => x_msg_data );
2792 WHEN OTHERS THEN
2793 ROLLBACK TO update_inst_party_acct_pub;
2794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2795 IF FND_MSG_PUB.Check_Msg_Level
2796 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2797 THEN
2798 FND_MSG_PUB.Add_Exc_Msg
2799 ( g_pkg_name, l_api_name );
2800 END IF;
2801
2802 FND_MSG_PUB.Count_And_Get
2803 ( p_count => x_msg_count,
2804 p_data => x_msg_data );
2805 END update_inst_party_account ;
2806
2807 /*--------------------------------------------------------*/
2808 /* Procedure name: Expire_inst_party_account */
2809 /* Description : Procedure used to expire an existing */
2810 /* instance-party account relationships */
2811 /*--------------------------------------------------------*/
2812
2813 PROCEDURE expire_inst_party_account
2814 ( p_api_version IN NUMBER
2815 ,p_commit IN VARCHAR2
2816 ,p_init_msg_list IN VARCHAR2
2817 ,p_validation_level IN NUMBER
2818 ,p_party_account_tbl IN csi_datastructures_pub.party_account_tbl
2819 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2820 ,x_return_status OUT NOCOPY VARCHAR2
2821 ,x_msg_count OUT NOCOPY NUMBER
2822 ,x_msg_data OUT NOCOPY VARCHAR2
2823 ) IS
2824
2825 l_api_name CONSTANT VARCHAR2(30) := 'EXPIRE_INST_PARTY_ACCOUNT';
2826 l_api_version CONSTANT NUMBER := 1.0;
2827 l_csi_debug_level NUMBER;
2828 l_msg_index NUMBER ;
2829 l_msg_count NUMBER;
2830 l_party_account_rec csi_datastructures_pub.party_account_rec;
2831 l_line_count NUMBER := 0 ;
2832 l_ip_account_id NUMBER ;
2833 l_flag VARCHAR2(1) :='N';
2834
2835 BEGIN
2836 -- Standard Start of API savepoint
2837 SAVEPOINT expire_inst_party_acct_pub;
2838
2839 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2840
2841 csi_utility_grp.check_ib_active;
2842
2843
2844 -- Standard call to check for call compatibility.
2845 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2846 p_api_version ,
2847 l_api_name ,
2848 g_pkg_name )
2849 THEN
2850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2851 END IF;
2852
2853 -- Initialize message list if p_init_msg_list is set to TRUE.
2854 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2855 FND_MSG_PUB.initialize;
2856 END IF;
2857
2858 -- Initialize API return status to success
2859 x_return_status := FND_API.G_RET_STS_SUCCESS;
2860
2861 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2862 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2863
2864 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2865 IF (l_csi_debug_level > 0) THEN
2866 csi_gen_utility_pvt.put_line( 'expire_inst_party_account');
2867 END IF;
2868
2869
2870 -- If the debug level = 2 then dump all the parameters values.
2871 IF (l_csi_debug_level > 1) THEN
2872 csi_gen_utility_pvt.put_line( 'expire_inst_party_account:'||
2873 p_api_version ||'-'||
2874 p_commit ||'-'||
2875 p_init_msg_list );
2876 -- Dump the records in the log file
2877 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
2878 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2879 END IF;
2880
2881 /***** srramakr commented for bug # 3304439
2882 -- Check for the profile option and enable trace
2883 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2884 -- End enable trace
2885 ****/
2886
2887 -- Start API body
2888 --
2889 IF p_party_account_tbl.count > 0 THEN
2890 FOR l_count IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
2891 IF p_party_account_tbl.EXISTS(l_count) THEN
2892 csi_party_relationships_pvt.expire_inst_party_account
2893 ( p_api_version => p_api_version
2894 ,p_commit => p_commit
2895 ,p_init_msg_list => p_init_msg_list
2896 ,p_validation_level => p_validation_level
2897 ,p_party_account_rec => p_party_account_tbl(l_count)
2898 ,p_txn_rec => p_txn_rec
2899 ,x_return_status => x_return_status
2900 ,x_msg_count => x_msg_count
2901 ,x_msg_data => x_msg_data );
2902
2903
2904 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2905 l_msg_index := 1;
2906 l_msg_count := x_msg_count;
2907 WHILE l_msg_count > 0 LOOP
2908 x_msg_data := FND_MSG_PUB.GET(
2909 l_msg_index,
2910 FND_API.G_FALSE);
2911 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2912 l_msg_index := l_msg_index + 1;
2913 l_msg_count := l_msg_count - 1;
2914 END LOOP;
2915 RAISE FND_API.G_EXC_ERROR;
2916 END IF;
2917 END IF;
2918 END LOOP;
2919 END IF;
2920 --
2921 -- End of API body
2922
2923 -- Standard check of p_commit.
2924 IF FND_API.To_Boolean( p_commit ) THEN
2925 COMMIT WORK;
2926 END IF;
2927
2928 /***** srramakr commented for bug # 3304439
2929 -- Check for the profile option and disable the trace
2930 IF (l_flag = 'Y') THEN
2931 dbms_session.set_sql_trace(false);
2932 END IF;
2933 -- End disable trace
2934 ****/
2935
2936 -- Standard call to get message count and if count is get message info.
2937 FND_MSG_PUB.Count_And_Get
2938 (p_count => x_msg_count ,
2939 p_data => x_msg_data );
2940 EXCEPTION
2941 WHEN FND_API.G_EXC_ERROR THEN
2942 ROLLBACK TO expire_inst_party_acct_pub;
2943 x_return_status := FND_API.G_RET_STS_ERROR ;
2944 FND_MSG_PUB.Count_And_Get
2945 ( p_count => x_msg_count,
2946 p_data => x_msg_data );
2947
2948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2949 ROLLBACK TO expire_inst_party_acct_pub;
2950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2951 FND_MSG_PUB.Count_And_Get
2952 ( p_count => x_msg_count,
2953 p_data => x_msg_data );
2954 WHEN OTHERS THEN
2955 ROLLBACK TO expire_inst_party_acct_pub;
2956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2957
2958 IF FND_MSG_PUB.Check_Msg_Level
2959 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2960 THEN
2961 FND_MSG_PUB.Add_Exc_Msg
2962 ( g_pkg_name, l_api_name );
2963 END IF;
2964 FND_MSG_PUB.Count_And_Get
2965 ( p_count => x_msg_count,
2966 p_data => x_msg_data );
2967 END expire_inst_party_account ;
2968 END csi_party_relationships_pub ;
2969
[Home] [Help]