1 PACKAGE BODY csi_party_relationships_pub AS
2 /* $Header: csipipb.pls 120.10.12020000.4 2012/11/12 10:57:13 sjawaji 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 l_cascade_ownership_uimu VARCHAR2(1) :='N'; --Added for Bug 10414588
743 l_force_cascade VARCHAR2(1) :='N'; --Added for Bug 10414588
744
745 -- Added by sk on 12/06/01 for contracts TRF fix bug 2133944
746
747 CURSOR acct_csr (p_ins_pty_id IN NUMBER) IS
748 SELECT acct.party_account_id
749 ,acct.active_end_date
750 ,pty.instance_id
751 FROM csi_ip_accounts acct
752 ,csi_i_parties pty
753 WHERE acct.instance_party_id = p_ins_pty_id
754 AND acct.relationship_type_code = 'OWNER'
755 AND ((acct.active_end_date IS NULL) OR (acct.active_end_date>SYSDATE))
756 AND pty.instance_party_id= acct.instance_party_id;
757 -- Following cursor has been added for fixing the bug 2151750
758 /* CURSOR party_csr (p_object_id IN NUMBER) IS
759 SELECT instance_id subject_id -- added by sguthiva for 2608706
760 FROM csi_item_instances
761 WHERE instance_id IN(
762 SELECT subject_id
763 FROM csi_ii_relationships
764 WHERE relationship_type_code = 'COMPONENT-OF'
765 START WITH object_id = p_object_id
766 CONNECT BY object_id = PRIOR subject_id)
767 AND (active_end_date IS NULL OR active_end_date> SYSDATE); */
768
769 CURSOR old_party_csr (p_ins_pty_id IN NUMBER) IS
770 SELECT instance_party_id,
771 party_id
772 FROM csi_i_parties
773 WHERE instance_party_id = p_ins_pty_id
774 AND relationship_type_code = 'OWNER'
775 AND (active_end_date IS NULL OR active_end_date > sysdate);
776
777 CURSOR exp_pty_csr (p_ins_id IN NUMBER) IS
778 SELECT instance_party_id,
779 party_id,
780 relationship_type_code,
781 object_version_number,
782 active_end_date -- Added for bug 7333900
783 FROM csi_i_parties
784 WHERE instance_id = p_ins_id
785 AND relationship_type_code<>'OWNER'
786 AND (active_end_date IS NULL
787 OR (trunc(active_end_date,'MI') > trunc(sysdate,'MI'))); -- Modified for bug 7333900 -- changed to trunc for bug 12564475
788
789 --included for bug 5511689
790 CURSOR exp_acct_csr (p_inst_party_id IN NUMBER) IS
791 SELECT ip_account_id,
792 relationship_type_code,
793 object_version_number,
794 active_end_date -- Added for bug 7333900
795 FROM csi_ip_accounts
796 WHERE instance_party_id=p_inst_party_id
797 AND relationship_type_code <>'OWNER'
798 AND nvl(active_end_date, sysdate+1) >= sysdate;
799 --end of fix
800
801 l_acct_csr acct_csr%ROWTYPE;
802 l_old_party_csr old_party_csr%ROWTYPE;
803 l_acct_tbl csi_datastructures_pub.party_account_tbl;
804 l_count NUMBER;
805 la_count NUMBER;
806 l_act_tbl csi_datastructures_pub.party_account_tbl;
807 l_row NUMBER;
808 l_obj_ver_number NUMBER;
809 l_found BOOLEAN := FALSE;
810 l_end_date DATE;
811
812 -- End Addition by sk on 12/06/01 for contracts TRF fix bug 2133944
813 -- Start of code addition for fixing the bug 2151750
814 l_old_party_tbl csi_datastructures_pub.party_tbl := p_party_tbl;
815 l_cld_party_rec csi_datastructures_pub.party_rec;
816 l_new_curr_party_rec csi_datastructures_pub.party_rec;
817 l_ip_acct_rec csi_datastructures_pub.party_account_rec;
818 l_temp_acct_rec csi_datastructures_pub.party_account_rec;
819 l_new_ip_acct_rec csi_datastructures_pub.party_account_rec;
820 l_pty_count NUMBER;
821 lp_count NUMBER;
822 lpa_count NUMBER;
823 l_cld_party_id NUMBER;
824 l_cld_party_acct_id NUMBER;
825 l_last_vld_org NUMBER;
826 l_last_vld_org1 NUMBER;
827 l_exp_acct_rec csi_datastructures_pub.party_account_rec; -- Added by sguthiva for bug 2307804
828 l_grp_call_contracts VARCHAR2(1);
829 -- End of code addition for fixing the bug 2151750
830 -- Start of code addition for fixing bug 6368172, section 1 of 5
831 l_old_parent_owner_pty_acct_id NUMBER;
832 l_old_child_owner_pty_acct_id NUMBER;
833 -- End of code addition for fixing bug 6368172, section 1 of 5
834 BEGIN
835 -- Standard Start of API savepoint
836 SAVEPOINT update_inst_party_rel_pub ;
837
838 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
839
840 csi_utility_grp.check_ib_active;
841
842 -- Standard call to check for call compatibility.
843 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
844 p_api_version ,
845 l_api_name ,
846 g_pkg_name )
847 THEN
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849 END IF;
850
851 -- Initialize message list if p_init_msg_list is set to TRUE.
852 IF FND_API.to_Boolean( p_init_msg_list ) THEN
853 FND_MSG_PUB.initialize;
854 END IF;
855
856
857 -- Initialize API return status to success
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859
860 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
861 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
862
863 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
864 IF (l_csi_debug_level > 0) THEN
865 csi_gen_utility_pvt.put_line( 'update_inst_party_relationship ');
866 END IF;
867
868
869 -- If the debug level = 2 then dump all the parameters values.
870 IF (l_csi_debug_level > 1) THEN
871 csi_gen_utility_pvt.put_line( 'update_inst_party_relationship:' ||
872 p_api_version ||'-'||
873 p_commit ||'-'||
874 p_init_msg_list ||'-'||
875 p_validation_level );
876 -- Dump the records in the log file
877 csi_gen_utility_pvt.dump_party_tbl(p_party_tbl);
878 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
879 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
880
881 END IF;
882
883 /***** srramakr commented for bug # 3304439
884 -- Check for the profile option and enable trace
885 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
886 -- End enable trace
887 ****/
888
889 -- Start API body
890 --
891 -- Assign the value for l_grp_call_contracts
892 -- Since all the records will have the same value for grp_call_contracts, we just take the first one.
893 l_grp_call_contracts := FND_API.G_FALSE;
894 IF p_party_account_tbl.count > 0 THEN
895 FOR tab_row in p_party_account_tbl.FIRST .. p_party_account_tbl.LAST
896 LOOP
897 IF p_party_account_tbl.EXISTS(tab_row) THEN
898 l_grp_call_contracts := p_party_account_tbl(tab_row).grp_call_contracts;
899 EXIT;
900 END IF;
901 END LOOP;
902 END IF;
903 --
904 -- Grab the internal party id from csi_installed paramters
905 -- Added by sk on 12/06/01 for contracts TRF fix bug 2133944
906 -- End Added by sk on 12/06/01 for contracts TRF fix bug 2133944
907 -- Added for bug 2151750
908 -- The following code has been written to grab the old owner party_id
909 -- for an instance.
910 l_pty_count:= p_party_tbl.count;
911 lp_count:=0;
912 IF l_pty_count > 0 THEN
913 FOR p_csr IN 1..l_pty_count
914 LOOP
915 IF p_party_tbl(p_csr).relationship_type_code = 'OWNER'
916 THEN
917 OPEN old_party_csr (p_party_tbl(p_csr).instance_party_id);
918 FETCH old_party_csr into l_old_party_csr;
919 IF old_party_csr%FOUND
920 THEN
921 lp_count:=lp_count+1;
922 l_old_party_tbl(lp_count).instance_party_id := l_old_party_csr.instance_party_id;
923 l_old_party_tbl(lp_count).party_id := l_old_party_csr.party_id;
924 l_old_party_tbl(lp_count).cascade_ownership_flag := nvl(p_party_tbl(p_csr).cascade_ownership_flag,'N'); --Added for cascade 2972082
925 l_old_party_tbl(lp_count).call_contracts := p_party_tbl(p_csr).call_contracts; -- added for bug #14843691
926 csi_gen_utility_pvt.put_line('l_old_party_tbl(lp_count).call_contracts'||l_old_party_tbl(lp_count).call_contracts);
927 -- The following code has been written to grab the old owner party_id
928 -- for an instance.
929 lpa_count:= p_party_account_tbl.count;
930 IF lpa_count > 0 THEN
931 FOR pa_csr IN 1..lpa_count
932 LOOP
933 IF p_party_account_tbl(pa_csr).instance_party_id = l_old_party_tbl(lp_count).instance_party_id AND
934 p_party_account_tbl(pa_csr).relationship_type_code ='OWNER'
935 THEN
936 -- The following line has been written to grab the vld_organization_id
937 -- of the account.
938 l_old_party_tbl(lp_count).attribute1 := p_party_account_tbl(pa_csr).vld_organization_id;
939 EXIT;
940 END IF;
941 END LOOP;
942 END IF; -- end if for lpa_count > 0
943 END IF; -- end if for old_party_csr%FOUND
944 CLOSE old_party_csr;
945 END IF; -- end if for p_party_tbl(p_csr).relationship_type_code = 'OWNER'
946 END LOOP; -- end loop for p_csr IN 1..l_count
947 END IF; -- end if for l_pty_count > 0
948 -- End addition for bug 2151750
949 --
950 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
951 csi_gen_utility_pvt.populate_install_param_rec;
952 END IF;
953 --
954 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
955 --
956 IF l_internal_party_id IS NULL THEN
957 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
958 FND_MSG_PUB.ADD;
959 RAISE FND_API.G_EXC_ERROR;
960 END IF;
961 -- Check if the table is not empty
962 IF p_party_tbl.count > 0 THEN
963 FOR l_party_row IN p_party_tbl.FIRST..p_party_tbl.LAST LOOP
964 IF p_party_tbl.EXISTS(l_party_row) THEN
965
966 -- Get Current Party record
967 IF NOT(CSI_Instance_parties_vld_pvt.Get_Party_Record
968 ( p_party_tbl(l_party_row).instance_party_id,
969 l_curr_party_rec)) THEN
970 RAISE FND_API.G_EXC_ERROR;
971 END IF;
972
973 -- Check if this is Transfer of ownership (i.e. owner to a new owner)
974 -- If so, then a new owner account is needed for the new party
975 IF ((p_party_tbl(l_party_row).PARTY_ID <> FND_API.G_MISS_NUM)
976 AND (p_party_tbl(l_party_row).PARTY_ID IS NOT NULL)
977 AND (p_party_tbl(l_party_row).party_id <> l_internal_party_id)
978 AND (p_party_tbl(l_party_row).PARTY_ID <> l_curr_party_rec.PARTY_ID)
979 AND (p_party_tbl(l_party_row).PARTY_SOURCE_TABLE = 'HZ_PARTIES')
980 AND (p_party_tbl(l_party_row).RELATIONSHIP_TYPE_CODE = l_curr_party_rec.RELATIONSHIP_TYPE_CODE )
981 AND (p_party_tbl(l_party_row).RELATIONSHIP_TYPE_CODE = 'OWNER'
982 ))
983 THEN
984
985 -- Find out if one of the accounts for external parties is an owner account
986 l_party_has_correct_acct := FALSE;
987 IF p_party_account_tbl.COUNT > 0 THEN
988 FOR l_acct_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
989 IF p_party_account_tbl.EXISTS(l_acct_row) -- Added for bug 3776650
990 THEN
991 -- Check if the party and its accounts are mapped
992 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index IS NULL)
993 OR (p_party_account_tbl(l_acct_row).parent_tbl_index = FND_API.G_MISS_NUM)
994 OR (NOT(p_party_tbl.EXISTS(p_party_account_tbl(l_acct_row).parent_tbl_index)))
995 ) THEN
996 FND_MESSAGE.SET_NAME('CSI','CSI_API_PARTY_ACCT_NOT_MAPPED');
997 FND_MSG_PUB.Add;
998 RAISE FND_API.G_EXC_ERROR;
999 END IF;
1000
1001 IF ((p_party_account_tbl(l_acct_row).parent_tbl_index = l_party_row)
1002 AND (p_party_account_tbl(l_acct_row).relationship_type_code = 'OWNER')) THEN
1003 l_party_has_correct_acct := TRUE;
1004 -- Check whether bill_to and ship_to are passed. If not make them null
1005 IF p_party_account_tbl(l_acct_row).bill_to_address IS NULL OR
1006 p_party_account_tbl(l_acct_row).bill_to_address = FND_API.G_MISS_NUM THEN
1007 p_party_account_tbl(l_acct_row).bill_to_address := NULL;
1008 END IF;
1009 --
1010 IF p_party_account_tbl(l_acct_row).ship_to_address IS NULL OR
1011 p_party_account_tbl(l_acct_row).ship_to_address = FND_API.G_MISS_NUM THEN
1012 p_party_account_tbl(l_acct_row).ship_to_address := NULL;
1013 END IF;
1014 --
1015 END IF;
1016 END IF;
1017 END LOOP;
1018 END IF; -- End of Transfer of Ownership check
1019
1020 -- Raise an exception if external parties don't have an owner account
1021 IF NOT l_party_has_correct_acct THEN
1022 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OWNER_ACCT');
1023 FND_MESSAGE.SET_TOKEN('PARTY_ID',p_party_tbl(l_party_row).party_id);
1024 FND_MSG_PUB.ADD;
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027
1028 END IF;
1029
1030
1031 l_party_rec.instance_party_id := p_party_tbl(l_party_row).instance_party_id ;
1032 l_party_rec.instance_id := p_party_tbl(l_party_row).instance_id;
1033 l_party_rec.party_source_table := p_party_tbl(l_party_row).party_source_table ;
1034 l_party_rec.party_id := p_party_tbl(l_party_row).party_id ;
1035 l_party_rec.relationship_type_code := p_party_tbl(l_party_row).relationship_type_code;
1036 l_party_rec.contact_flag := p_party_tbl(l_party_row).contact_flag;
1037 l_party_rec.contact_ip_id := p_party_tbl(l_party_row).contact_ip_id;
1038 l_party_rec.active_start_date := p_party_tbl(l_party_row).active_start_date;
1039 l_party_rec.active_end_date := p_party_tbl(l_party_row).active_end_date;
1040 l_party_rec.context := p_party_tbl(l_party_row).context;
1041 l_party_rec.attribute1 := p_party_tbl(l_party_row). attribute1;
1042 l_party_rec.attribute2 := p_party_tbl(l_party_row).attribute2;
1043 l_party_rec.attribute3 := p_party_tbl(l_party_row).attribute3;
1044 l_party_rec.attribute4 := p_party_tbl(l_party_row).attribute4;
1045 l_party_rec.attribute5 := p_party_tbl(l_party_row).attribute5;
1046 l_party_rec.attribute6 := p_party_tbl(l_party_row).attribute6;
1047 l_party_rec.attribute7 := p_party_tbl(l_party_row).attribute7;
1048 l_party_rec.attribute8 := p_party_tbl(l_party_row).attribute8;
1049 l_party_rec.attribute9 := p_party_tbl(l_party_row).attribute9;
1050 l_party_rec.attribute10 := p_party_tbl(l_party_row).attribute10;
1051 l_party_rec.attribute11 := p_party_tbl(l_party_row).attribute11;
1052 l_party_rec.attribute12 := p_party_tbl(l_party_row).attribute12;
1053 l_party_rec.attribute13 := p_party_tbl(l_party_row).attribute13;
1054 l_party_rec.attribute14 := p_party_tbl(l_party_row).attribute14;
1055 l_party_rec.attribute15 := p_party_tbl(l_party_row).attribute15;
1056 l_party_rec.preferred_flag := p_party_tbl(l_party_row).preferred_flag;
1057 l_party_rec.primary_flag := p_party_tbl(l_party_row).primary_flag;
1058 l_party_rec.object_version_number := p_party_tbl(l_party_row).object_version_number;
1059
1060 -- Start of code addition for fixing bug 6368172, section 2 of 5
1061 -- Need to grab the account id before it gets changed
1062 IF l_party_rec.instance_party_id IS NOT NULL THEN
1063 BEGIN
1064 SELECT party_account_id
1065 INTO l_old_parent_owner_pty_acct_id
1066 FROM csi_ip_accounts
1067 WHERE instance_party_id = l_party_rec.instance_party_id
1068 AND relationship_type_code = 'OWNER';
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND THEN
1071 l_old_parent_owner_pty_acct_id := NULL;
1072 END;
1073 END IF;
1074 -- End of code addition for fixing bug 6368172, section 2 of 5
1075
1076 -- added by sk on 12/07 for accounts fix
1077 IF p_party_account_tbl.COUNT > 0 THEN
1078 FOR l_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
1079 IF p_party_account_tbl.EXISTS(l_row) THEN
1080 l_obj_ver_number := NULL;
1081 BEGIN
1082 IF p_party_account_tbl(l_row).ip_account_id IS NOT NULL
1083 AND p_party_account_tbl(l_row).ip_account_id <> fnd_api.g_miss_num
1084 THEN
1085 SELECT acct.object_version_number
1086 INTO l_obj_ver_number
1087 FROM csi_ip_accounts acct
1088 WHERE acct.ip_account_id= p_party_account_tbl(l_row).ip_account_id;
1089 END IF;
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 l_obj_ver_number := NULL;
1093 END;
1094 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)
1095 AND p_party_account_tbl(l_row).relationship_type_code = 'OWNER'
1096 AND p_party_account_tbl(l_row).instance_party_id = l_party_rec.instance_party_id
1097 AND p_party_account_tbl(l_row).object_version_number = l_obj_ver_number
1098 THEN
1099 l_act_tbl(l_party_row).attribute1:='Y';
1100 l_act_tbl(l_party_row).ip_account_id :=p_party_account_tbl(l_row).ip_account_id;
1101 END IF;
1102 END IF;
1103 END LOOP;
1104 END IF;
1105 -- end of addition by sk on 12/07 for accounts fix
1106
1107 csi_party_relationships_pvt.update_inst_party_relationship
1108 ( p_api_version => p_api_version
1109 ,p_commit => p_commit
1110 ,p_init_msg_list => p_init_msg_list
1111 ,p_validation_level => p_validation_level
1112 ,p_party_rec => l_party_rec
1113 ,p_txn_rec => p_txn_rec
1114 ,x_return_status => x_return_status
1115 ,x_msg_count => x_msg_count
1116 ,x_msg_data => x_msg_data ) ;
1117
1118 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1119 l_msg_index := 1;
1120 l_msg_count := x_msg_count;
1121 WHILE l_msg_count > 0 LOOP
1122 x_msg_data := FND_MSG_PUB.GET(
1123 l_msg_index,
1124 FND_API.G_FALSE );
1125 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1126 l_msg_index := l_msg_index + 1;
1127 l_msg_count := l_msg_count - 1;
1128 END LOOP;
1129 RAISE FND_API.G_EXC_ERROR;
1130 ELSE
1131 -- Grab the new party rec
1132 l_party_tbl(l_party_row) := l_party_rec;
1133 END IF;
1134 END IF;
1135 END LOOP;
1136
1137 END IF;
1138
1139 -- Update accounts
1140 -- Check if the table is not empty
1141 IF p_party_account_tbl.count > 0 THEN
1142 FOR l_acct_row IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
1143 IF p_party_account_tbl.EXISTS(l_acct_row) THEN
1144
1145
1146 IF ( (p_party_account_tbl(l_acct_row).ip_account_id IS NULL)
1147 OR
1148 (p_party_account_tbl(l_acct_row).ip_account_id = FND_API.G_MISS_NUM) ) THEN
1149 -- Call Private package to validate and create party accounts
1150 csi_party_relationships_pvt.create_inst_party_account
1151 ( p_api_version => p_api_version
1152 ,p_commit => p_commit
1153 ,p_init_msg_list => p_init_msg_list
1154 ,p_validation_level => p_validation_level
1155 ,p_party_account_rec => p_party_account_tbl(l_acct_row)
1156 ,p_txn_rec => p_txn_rec
1157 ,x_return_status => x_return_status
1158 ,x_msg_count => x_msg_count
1159 ,x_msg_data => x_msg_data
1160 ,p_inst_party_tbl => l_inst_party_tbl
1161 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
1162 ,p_site_use_tbl => l_site_use_tbl
1163 ,p_account_count_rec => l_account_count_rec
1164 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1165 );
1166 ELSE
1167 -- dbms_output.put_line('PUB: caling update_inst_party_account');
1168 -- added by sk on 12/07 for accounts fix
1169
1170 l_found := FALSE;
1171 IF l_act_tbl.COUNT > 0 THEN
1172 FOR l_arow IN l_act_tbl.FIRST..l_act_tbl.LAST LOOP
1173 IF l_found
1174 THEN
1175 EXIT;
1176 END IF;
1177 IF l_act_tbl.EXISTS(l_arow) THEN
1178 IF l_act_tbl(l_arow).ip_account_id = p_party_account_tbl(l_acct_row).ip_account_id
1179 AND l_act_tbl(l_arow).attribute1 = 'Y'
1180 THEN
1181 l_found := TRUE;
1182 BEGIN
1183 SELECT acct.object_version_number
1184 INTO p_party_account_tbl(l_arow).object_version_number
1185 FROM csi_ip_accounts acct
1186 WHERE acct.ip_account_id= p_party_account_tbl(l_arow).ip_account_id;
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 NULL;
1190 END;
1191 END IF;
1192 END IF;
1193 END LOOP;
1194 END IF;
1195 -- End addition by sk on 12/07 for accounts fix
1196 -- srramakr Fix for Bug # 3117552
1197 IF p_txn_rec.transaction_type_id = 7 THEN -- only for Account Merge
1198 BEGIN
1199 SELECT acct.object_version_number,acct.active_end_date
1200 INTO p_party_account_tbl(l_acct_row).object_version_number,l_end_date
1201 FROM csi_ip_accounts acct
1202 WHERE acct.ip_account_id= p_party_account_tbl(l_acct_row).ip_account_id;
1203 -- Commenting for bug 3692167 as it will fail the unique constraint.
1204 -- At any time there should be one active record in combination of
1205 -- (party_account_id,relationship_type_code) associated to an
1206 -- party entity.
1207 /*
1208 IF nvl(l_end_date,(sysdate+1)) <= sysdate THEN
1209 p_party_account_tbl(l_acct_row).active_end_date := NULL;
1210 END IF;
1211 */
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 NULL;
1215 END;
1216 END IF;
1217 --
1218 csi_party_relationships_pvt.update_inst_party_account
1219 ( p_api_version => p_api_version
1220 ,p_commit => p_commit
1221 ,p_init_msg_list => p_init_msg_list
1222 ,p_validation_level => p_validation_level
1223 ,p_party_account_rec => p_party_account_tbl(l_acct_row)
1224 ,p_txn_rec => p_txn_rec
1225 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1226 ,x_return_status => x_return_status
1227 ,x_msg_count => x_msg_count
1228 ,x_msg_data => x_msg_data);
1229 END IF;
1230
1231 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1232 l_msg_index := 1;
1233 l_msg_count := x_msg_count;
1234 WHILE l_msg_count > 0 LOOP
1235 x_msg_data := FND_MSG_PUB.GET(
1236 l_msg_index,
1237 FND_API.G_FALSE );
1238 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1239 l_msg_index := l_msg_index + 1;
1240 l_msg_count := l_msg_count - 1;
1241 END LOOP;
1242 RAISE FND_API.G_EXC_ERROR;
1243 END IF;
1244 END IF;
1245 END LOOP;
1246 -- Added by sguthiva for bug 2307804
1247 ELSE
1248 -- The following code has wrritten specifically for expiring the ownership of
1249 -- an external party during the transfer of ownership from external to internal.
1250
1251 FOR p_row IN p_party_tbl.FIRST..p_party_tbl.LAST
1252 LOOP
1253 FOR l_row IN l_old_party_tbl.FIRST..l_old_party_tbl.LAST
1254 LOOP
1255 IF l_old_party_tbl(l_row).instance_party_id = p_party_tbl(p_row).instance_party_id
1256 AND l_old_party_tbl(l_row).party_id <> l_internal_party_id
1257 AND p_party_tbl(p_row).party_id = l_internal_party_id
1258 OR ((l_old_party_tbl(l_row).instance_party_id = p_party_tbl(p_row).instance_party_id)
1259 AND (p_party_tbl(p_row).party_source_table IN ('EMPLOYEE', 'PO_VENDORS')))
1260 THEN
1261 -- Adding for bug 3294748
1262 -- Wrote the following code to expire all non-owner parties and its contacts (if any)
1263 -- in the case of ownership transfer from external-to-internal party,
1264 -- however we do not expire non-owner accounts.
1265 IF l_old_party_tbl(l_row).instance_party_id = p_party_tbl(p_row).instance_party_id
1266 AND l_old_party_tbl(l_row).party_id <> l_internal_party_id
1267 AND p_party_tbl(p_row).party_id = l_internal_party_id
1268 AND ( p_party_tbl(p_row).instance_id IS NOT NULL AND
1269 p_party_tbl(p_row).instance_id <> fnd_api.g_miss_num)
1270 AND p_party_tbl(p_row).relationship_type_code='OWNER'
1271 THEN
1272 FOR l_exp_pty IN exp_pty_csr(p_party_tbl(p_row).instance_id)
1273 LOOP
1274 -- Bug 10007311
1275 -- Modified the order in which the party and accounts are expired
1276 -- The correct order is to expire the account followed by the
1277 -- party
1278
1279 -- Bug 10007311
1280 -- Start of account expiration code
1281 --fix for bug 5511689:To expire non-owner accounts while expiring non-owner parties.
1282 FOR exp_acct_rec IN exp_acct_csr(l_exp_pty.instance_party_id) LOOP
1283 l_exp_acct_rec:=l_temp_acct_rec;
1284 l_exp_acct_rec.ip_account_id := exp_acct_rec.ip_account_id;
1285 l_exp_acct_rec.relationship_type_code := exp_acct_rec.relationship_type_code;
1286 l_exp_acct_rec.object_version_number := exp_acct_rec.object_version_number ;
1287 l_exp_acct_rec.active_end_date :=SYSDATE;
1288 l_exp_acct_rec.expire_flag :=fnd_api.g_true;
1289 -- Add log output for bug 7333900
1290 IF (l_csi_debug_level > 0) THEN
1291 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);
1292 csi_gen_utility_pvt.put_line(' account old active_end_date : '||exp_acct_rec.active_end_date);
1293 csi_gen_utility_pvt.put_line(' account new active_end_date : '||l_exp_acct_rec.active_end_date);
1294 csi_gen_utility_pvt.put_line(' sysdate : '||SYSDATE);
1295 END IF;
1296 IF l_exp_acct_rec.ip_account_id IS NOT NULL
1297 AND l_exp_acct_rec.ip_account_id <> fnd_api.g_miss_num
1298 THEN
1299 l_exp_acct_rec.grp_call_contracts := l_grp_call_contracts;
1300 csi_party_relationships_pvt.update_inst_party_account
1301 ( p_api_version => p_api_version
1302 ,p_commit => p_commit
1303 ,p_init_msg_list => p_init_msg_list
1304 ,p_validation_level => p_validation_level
1305 ,p_party_account_rec => l_exp_acct_rec
1306 ,p_txn_rec => p_txn_rec
1307 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1308 ,x_return_status => x_return_status
1309 ,x_msg_count => x_msg_count
1310 ,x_msg_data => x_msg_data);
1311
1312 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1313 l_msg_index := 1;
1314 l_msg_count := x_msg_count;
1315 WHILE l_msg_count > 0 LOOP
1316 x_msg_data := FND_MSG_PUB.GET(
1317 l_msg_index,
1318 FND_API.G_FALSE );
1319 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1320 l_msg_index := l_msg_index + 1;
1321 l_msg_count := l_msg_count - 1;
1322 END LOOP;
1323 RAISE FND_API.G_EXC_ERROR;
1324 END IF;
1325 END IF;
1326 END LOOP;-- exp_acct_rec IN exp_acct_csr
1327 --end of fix 5511689
1328 -- End of party account expiration code
1329 -- Start of Party expiration code
1330
1331 l_exp_party_rec:= l_temp_party_rec;
1332 l_exp_party_rec.instance_id:= p_party_tbl(p_row).instance_id;
1333 l_exp_party_rec.instance_party_id := l_exp_pty.instance_party_id;
1334 l_exp_party_rec.relationship_type_code := l_exp_pty.relationship_type_code;
1335 l_exp_party_rec.object_version_number := l_exp_pty.object_version_number;
1336 -- Bug 3804960
1337 -- srramakr Need to use the same the date used by the item instance
1338 IF p_txn_rec.src_txn_creation_date IS NULL OR
1339 p_txn_rec.src_txn_creation_date = FND_API.G_MISS_DATE THEN
1340 l_exp_party_rec.active_end_date := sysdate;
1341 ELSE
1342 l_exp_party_rec.active_end_date := p_txn_rec.src_txn_creation_date;
1343 END IF;
1344 -- End of 3804960
1345 -- Add log output for bug 7333900
1346 IF (l_csi_debug_level > 0) THEN
1347 csi_gen_utility_pvt.put_line('Expiring party record '||l_exp_party_rec.instance_party_id||' of party type '||l_exp_party_rec.relationship_type_code);
1348 csi_gen_utility_pvt.put_line(' party old active_end_date : '||l_exp_pty.active_end_date);
1349 csi_gen_utility_pvt.put_line(' party new active_end_date : '||l_exp_party_rec.active_end_date);
1350 csi_gen_utility_pvt.put_line(' sysdate : '||SYSDATE);
1351 END IF;
1352 csi_party_relationships_pvt.update_inst_party_relationship
1353 ( p_api_version => p_api_version
1354 ,p_commit => p_commit
1355 ,p_init_msg_list => p_init_msg_list
1356 ,p_validation_level => p_validation_level
1357 ,p_party_rec => l_exp_party_rec
1358 ,p_txn_rec => p_txn_rec
1359 ,x_return_status => x_return_status
1360 ,x_msg_count => x_msg_count
1361 ,x_msg_data => x_msg_data ) ;
1362
1363 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1364 l_msg_index := 1;
1365 l_msg_count := x_msg_count;
1366 WHILE l_msg_count > 0
1367 LOOP
1368 x_msg_data := FND_MSG_PUB.GET(
1369 l_msg_index,
1370 FND_API.G_FALSE );
1371 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1372 l_msg_index := l_msg_index + 1;
1373 l_msg_count := l_msg_count - 1;
1374 END LOOP;
1375 RAISE FND_API.G_EXC_ERROR;
1376 END IF;
1377 -- Bug 10007311 End of party expiration code
1378 END LOOP;
1379 END IF;
1380 --End addition for bug 3294748
1381
1382 l_exp_acct_rec:=l_temp_acct_rec;
1383 BEGIN
1384 SELECT ip_account_id,
1385 relationship_type_code,
1386 object_version_number
1387 INTO l_exp_acct_rec.ip_account_id,
1388 l_exp_acct_rec.relationship_type_code,
1389 l_exp_acct_rec.object_version_number
1390 FROM csi_ip_accounts
1391 WHERE instance_party_id=p_party_tbl(p_row).instance_party_id
1392 AND relationship_type_code ='OWNER'
1393 AND nvl(active_end_date, sysdate+1) >= sysdate;
1394 l_exp_acct_rec.active_end_date :=SYSDATE;
1395 l_exp_acct_rec.expire_flag :=fnd_api.g_true;
1396 EXCEPTION
1397 WHEN OTHERS THEN
1398 NULL;
1399 END;
1400
1401 IF l_exp_acct_rec.ip_account_id IS NOT NULL
1402 AND l_exp_acct_rec.ip_account_id <> fnd_api.g_miss_num
1403 THEN
1404 l_exp_acct_rec.grp_call_contracts := l_grp_call_contracts;
1405 csi_party_relationships_pvt.update_inst_party_account
1406 ( p_api_version => p_api_version
1407 ,p_commit => p_commit
1408 ,p_init_msg_list => p_init_msg_list
1409 ,p_validation_level => p_validation_level
1410 ,p_party_account_rec => l_exp_acct_rec
1411 ,p_txn_rec => p_txn_rec
1412 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1413 ,x_return_status => x_return_status
1414 ,x_msg_count => x_msg_count
1415 ,x_msg_data => x_msg_data);
1416
1417 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1418 l_msg_index := 1;
1419 l_msg_count := x_msg_count;
1420 WHILE l_msg_count > 0 LOOP
1421 x_msg_data := FND_MSG_PUB.GET(
1422 l_msg_index,
1423 FND_API.G_FALSE );
1424 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1425 l_msg_index := l_msg_index + 1;
1426 l_msg_count := l_msg_count - 1;
1427 END LOOP;
1428 RAISE FND_API.G_EXC_ERROR;
1429 END IF;
1430 END IF;
1431 END IF;
1432 END LOOP;
1433 END LOOP;
1434 -- End addition by sguthiva for bug 2307804
1435
1436 END IF;
1437 -- Start of code addition for fixing the bug 2151750
1438 /* If the owner of the root of a configuration changes, the ownership should
1439 be inherited by all child component (children in component-of tree) having the
1440 same initial owner as the root.
1441 Here grab the root instance owner party and its owner account.
1442 Retreive if it has any child instances from csi_ii_relationships table of relationship_type_code
1443 as 'COMPONENT-OF'.
1444 */
1445
1446 IF l_old_party_tbl.count > 0 THEN
1447 FOR l_old_party_row IN l_old_party_tbl.FIRST..l_old_party_tbl.LAST
1448 LOOP
1449 IF l_old_party_tbl.EXISTS(l_old_party_row) THEN
1450 IF l_old_party_tbl(l_old_party_row).relationship_type_code = 'OWNER' AND
1451 l_old_party_tbl(l_old_party_row).party_id IS NOT NULL AND
1452 l_old_party_tbl(l_old_party_row).party_id <> fnd_api.g_miss_num
1453 THEN
1454 -- Get Current Party record
1455 IF NOT(CSI_Instance_parties_vld_pvt.Get_Party_Record
1456 ( l_old_party_tbl(l_old_party_row).instance_party_id,
1457 l_new_curr_party_rec)) THEN
1458 RAISE FND_API.G_EXC_ERROR;
1459 END IF;
1460
1461 -- Start of code addition for fixing bug 6368172, section 3 of 5
1462 -- grab the owner account of the parent
1463 l_ip_acct_rec := l_temp_acct_rec;
1464 l_new_ip_acct_rec := l_temp_acct_rec;
1465 BEGIN
1466 SELECT instance_party_id
1467 ,party_account_id
1468 ,relationship_type_code
1469 ,bill_to_address
1470 ,ship_to_address
1471 ,active_start_date
1472 ,active_end_date
1473 ,context
1474 ,attribute1
1475 ,attribute2
1476 ,attribute3
1477 ,attribute4
1478 ,attribute5
1479 ,attribute6
1480 ,attribute7
1481 ,attribute8
1482 ,attribute9
1483 ,attribute10
1484 ,attribute11
1485 ,attribute12
1486 ,attribute13
1487 ,attribute14
1488 ,attribute15
1489 INTO l_ip_acct_rec.instance_party_id
1490 ,l_ip_acct_rec.party_account_id
1491 ,l_ip_acct_rec.relationship_type_code
1492 ,l_ip_acct_rec.bill_to_address
1493 ,l_ip_acct_rec.ship_to_address
1494 ,l_ip_acct_rec.active_start_date
1495 ,l_ip_acct_rec.active_end_date
1496 ,l_ip_acct_rec.context
1497 ,l_ip_acct_rec.attribute1
1498 ,l_ip_acct_rec.attribute2
1499 ,l_ip_acct_rec.attribute3
1500 ,l_ip_acct_rec.attribute4
1501 ,l_ip_acct_rec.attribute5
1502 ,l_ip_acct_rec.attribute6
1503 ,l_ip_acct_rec.attribute7
1504 ,l_ip_acct_rec.attribute8
1505 ,l_ip_acct_rec.attribute9
1506 ,l_ip_acct_rec.attribute10
1507 ,l_ip_acct_rec.attribute11
1508 ,l_ip_acct_rec.attribute12
1509 ,l_ip_acct_rec.attribute13
1510 ,l_ip_acct_rec.attribute14
1511 ,l_ip_acct_rec.attribute15
1512 FROM csi_ip_accounts
1513 WHERE instance_party_id = l_old_party_tbl(l_old_party_row).instance_party_id
1514 AND relationship_type_code = 'OWNER'
1515 AND SYSDATE BETWEEN nvl(active_start_date, SYSDATE-1)
1516 AND nvl(active_end_date, SYSDATE+1);
1517
1518 l_new_ip_acct_rec := l_ip_acct_rec;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 l_ip_acct_rec := l_temp_acct_rec;
1522 l_new_ip_acct_rec :=l_temp_acct_rec;
1523 END;
1524 l_ip_acct_rec.call_contracts := l_old_party_tbl(l_old_party_row).call_contracts;--Added for Bug#14843691
1525 csi_gen_utility_pvt.put_line('l_ip_acct_rec.call_contracts : '||l_ip_acct_rec.call_contracts);
1526 -- End of code addition for fixing bug 6368172, section 3 of 5
1527
1528 -- If the retreived party records party_id has been changed then
1529 -- we can assume that a transfer of ownership has taken place in
1530 -- the above procedure.
1531 IF l_old_party_tbl(l_old_party_row).party_id <> l_new_curr_party_rec.party_id
1532 -- Start of code addition for fixing bug 6368172, section 4 of 5
1533 OR (l_old_party_tbl(l_old_party_row).party_id = l_new_curr_party_rec.party_id
1534 AND l_old_parent_owner_pty_acct_id IS NOT NULL
1535 AND l_old_parent_owner_pty_acct_id <> fnd_api.g_miss_num
1536 AND l_old_parent_owner_pty_acct_id <> l_new_ip_acct_rec.party_account_id)
1537 -- End of code addition for fixing bug 6368172, section 4 of 5
1538 OR nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y' -- Added for cascade 2972082
1539 THEN
1540 -- the l_new_ip_acct_rec account needs to be passed to all the children
1541 -- we got an instance whose owner party, or owner account has been changed
1542 -- we need to grab all its children if it has any and change the
1543 -- ownership of them also.
1544 -- Here I'm grabbing the children
1545
1546 -- Added for cascade bug 2972082
1547 IF l_old_party_tbl(l_old_party_row).instance_id IS NULL OR
1548 l_old_party_tbl(l_old_party_row).instance_id = fnd_api.g_miss_num
1549 THEN
1550 BEGIN
1551 SELECT instance_id
1552 INTO l_old_party_tbl(l_old_party_row).instance_id
1553 FROM csi_i_parties
1554 WHERE instance_party_id=l_old_party_tbl(l_old_party_row).instance_party_id;
1555 EXCEPTION
1556 WHEN NO_DATA_FOUND THEN
1557 NULL;
1558 END;
1559 END IF;
1560 -- End addition for bug 2972082
1561
1562 p_rel_query_rec.object_id := l_old_party_tbl(l_old_party_row).instance_id;
1563 p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
1564 --
1565 IF p_rel_query_rec.object_id IS NOT NULL AND
1566 p_rel_query_rec.object_id <> FND_API.G_MISS_NUM THEN
1567 csi_ii_relationships_pvt.Get_Children
1568 ( p_relationship_query_rec => p_rel_query_rec,
1569 p_rel_tbl => l_rel_tbl,
1570 p_depth => NULL,
1571 p_active_relationship_only => FND_API.G_TRUE,
1572 p_time_stamp => FND_API.G_MISS_DATE,
1573 p_get_dfs => FND_API.G_FALSE,
1574 p_ii_relationship_level_tbl => l_ii_relationship_level_tbl,
1575 x_return_status => x_return_status,
1576 x_msg_count => x_msg_count,
1577 x_msg_data => x_msg_data
1578 );
1579 --
1580 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1581 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_CHILDREN_ERROR');
1582 FND_MSG_PUB.Add;
1583 RAISE FND_API.G_EXC_ERROR;
1584 END IF;
1585 END IF;
1586 --
1587 IF l_rel_tbl.count > 0 THEN
1588 -- FOR l_old_pty_csr IN party_csr(l_old_party_tbl(l_old_party_row).instance_id)
1589 FOR j in l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
1590 -- After grabbing the child instances one by one I'll call
1591 -- the pvt.update_inst_party_relationship for a transfer
1592 -- to new owner.
1593 -- Now grab the child instances instance_party_id
1594 BEGIN
1595 l_cld_party_rec := l_new_curr_party_rec;
1596 l_cld_party_id := null;
1597 l_cld_party_rec.active_start_date := fnd_api.g_miss_date;
1598 l_cld_party_rec.active_end_date := fnd_api.g_miss_date;
1599 SELECT instance_party_id,
1600 instance_id,
1601 party_id,
1602 object_version_number
1603 INTO l_cld_party_rec.instance_party_id,
1604 l_cld_party_rec.instance_id,
1605 l_cld_party_id,
1606 l_cld_party_rec.object_version_number
1607 FROM csi_i_parties
1608 WHERE instance_id = l_rel_tbl(j).subject_id
1609 AND relationship_type_code = 'OWNER'
1610 AND (active_end_date IS NULL OR active_end_date > SYSDATE);
1611
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 l_cld_party_rec := l_temp_party_rec;
1615 END;
1616
1617 BEGIN
1618 SELECT party_account_id
1619 INTO l_cld_party_acct_id
1620 FROM csi_ip_accounts
1621 WHERE instance_party_id = l_cld_party_rec.instance_party_id
1622 AND relationship_type_code = 'OWNER'
1623 AND SYSDATE BETWEEN nvl(active_start_date, sysdate-1)
1624 AND nvl(active_end_date, sysdate+1);
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 NULL;
1628 END;
1629
1630 -- The following is modified for cascade bug 2972082
1631 -- to make sure parties were updated only for new party
1632 -- which is different from the original party if
1633 -- cascade_ownership_flag=fnd_api.g_true.
1634 /*
1635 IF l_cld_party_rec.instance_party_id IS NOT NULL AND
1636 l_cld_party_rec.instance_party_id <> fnd_api.g_miss_num AND
1637 ((l_cld_party_id = l_old_party_tbl(l_old_party_row).party_id AND
1638 l_cld_party_id <> l_cld_party_rec.party_id)
1639 OR
1640 (l_cld_party_id <> l_old_party_tbl(l_old_party_row).party_id AND
1641 l_cld_party_id <> l_cld_party_rec.party_id AND
1642 nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y') -- Added for cascade 2972082
1643 )
1644 THEN
1645 */
1646 -- Start of code addition for fixing bug 6368172, section 5 of 5
1647 -- Need to grab the old child account id before it gets changed
1648 IF l_cld_party_rec.instance_party_id IS NOT NULL THEN
1649 BEGIN
1650 SELECT party_account_id
1651 INTO l_old_child_owner_pty_acct_id
1652 FROM csi_ip_accounts
1653 WHERE instance_party_id = l_cld_party_rec.instance_party_id
1654 AND relationship_type_code = 'OWNER';
1655 EXCEPTION
1656 WHEN NO_DATA_FOUND THEN
1657 l_old_child_owner_pty_acct_id := NULL;
1658 END;
1659 END IF;
1660
1661 --Added for Bug 10414588
1662 l_cascade_ownership_uimu := FND_PROFILE.VALUE('CSI_FORCE_CASCADE_OWNER_UIMU');
1663 csi_gen_utility_pvt.put_line('l_cascade_ownership_uimu: ' || l_cascade_ownership_uimu);
1664 csi_gen_utility_pvt.put_line('cascade_ownership_flag: ' || l_old_party_tbl(l_old_party_row).cascade_ownership_flag);
1665
1666 l_force_cascade := 'N';
1667 IF p_txn_rec.transaction_type_id IN (1,3) THEN
1668 l_force_cascade := NVL(l_cascade_ownership_uimu, 'N');
1669 ELSE
1670 l_force_cascade := nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N');
1671 END IF;
1672
1673 csi_gen_utility_pvt.put_line('l_force_cascade: ' || l_force_cascade);
1674 --End of code for Bug 10414588
1675
1676 IF l_cld_party_rec.instance_party_id IS NOT NULL AND
1677 l_cld_party_rec.instance_party_id <> fnd_api.g_miss_num AND
1678 ((l_cld_party_id = l_old_party_tbl(l_old_party_row).party_id AND
1679 l_cld_party_id <> l_cld_party_rec.party_id)
1680 OR
1681 (l_cld_party_id = l_old_party_tbl(l_old_party_row).party_id AND
1682 l_cld_party_id = l_cld_party_rec.party_id AND
1683 l_old_parent_owner_pty_acct_id IS NOT NULL AND
1684 l_old_parent_owner_pty_acct_id <> fnd_api.g_miss_num AND
1685 l_old_child_owner_pty_acct_id IS NOT NULL AND
1686 l_old_child_owner_pty_acct_id <> fnd_api.g_miss_num AND
1687 (l_old_child_owner_pty_acct_id = l_old_parent_owner_pty_acct_id OR nvl(l_force_cascade,'N')='Y') AND --Changed for Bug 10414588
1688 l_old_child_owner_pty_acct_id <> l_new_ip_acct_rec.party_account_id)
1689 OR
1690 (l_cld_party_id <> l_old_party_tbl(l_old_party_row).party_id AND
1691 l_cld_party_id <> l_cld_party_rec.party_id AND
1692 nvl(l_force_cascade,'N')='Y') -- Added for cascade 2972082 --Changed for Bug 10414588
1693 )
1694 THEN
1695 -- End of code addition for fixing bug 6368172, section 5 of 5
1696
1697 csi_party_relationships_pvt.update_inst_party_relationship
1698 ( p_api_version => p_api_version
1699 ,p_commit => p_commit
1700 ,p_init_msg_list => p_init_msg_list
1701 ,p_validation_level => p_validation_level
1702 ,p_party_rec => l_cld_party_rec
1703 ,p_txn_rec => p_txn_rec
1704 ,x_return_status => x_return_status
1705 ,x_msg_count => x_msg_count
1706 ,x_msg_data => x_msg_data ) ;
1707
1708 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1709 l_msg_index := 1;
1710 l_msg_count := x_msg_count;
1711 WHILE l_msg_count > 0 LOOP
1712 x_msg_data := FND_MSG_PUB.GET(
1713 l_msg_index,
1714 FND_API.G_FALSE );
1715 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1716 l_msg_index := l_msg_index + 1;
1717 l_msg_count := l_msg_count - 1;
1718 END LOOP;
1719 RAISE FND_API.G_EXC_ERROR;
1720 END IF;
1721
1722 --added may15
1723 -- Added by sguthiva for bug 2307804
1724 -- The following code has wrritten specifically for expiring the ownership of
1725 -- an external party during the transfer of ownership from external to internal.
1726 IF l_cld_party_rec.party_id = l_internal_party_id
1727 THEN
1728 -- Adding for bug 3294748
1729 -- Wrote the following code to expire all non-owner parties and its contacts (if any)
1730 -- in the case of ext-to-int ownership transfer, however we do not expire non-owner accounts.
1731 IF l_cld_party_rec.relationship_type_code='OWNER'
1732 THEN
1733 FOR l_exp_pty IN exp_pty_csr(l_cld_party_rec.instance_id)
1734 LOOP
1735 l_exp_party_rec:= l_temp_party_rec;
1736 l_exp_party_rec.instance_id:= l_cld_party_rec.instance_id;
1737 l_exp_party_rec.instance_party_id := l_exp_pty.instance_party_id;
1738 l_exp_party_rec.relationship_type_code := l_exp_pty.relationship_type_code;
1739 l_exp_party_rec.object_version_number := l_exp_pty.object_version_number;
1740 -- Bug 3804960
1741 -- srramakr Need to use the same the date used by the item instance
1742 IF p_txn_rec.src_txn_creation_date IS NULL OR
1743 p_txn_rec.src_txn_creation_date = FND_API.G_MISS_DATE THEN
1744 l_exp_party_rec.active_end_date := sysdate;
1745 ELSE
1746 l_exp_party_rec.active_end_date := p_txn_rec.src_txn_creation_date;
1747 END IF;
1748 -- End of 3804960
1749 csi_party_relationships_pvt.update_inst_party_relationship
1750 ( p_api_version => p_api_version
1751 ,p_commit => p_commit
1752 ,p_init_msg_list => p_init_msg_list
1753 ,p_validation_level => p_validation_level
1754 ,p_party_rec => l_exp_party_rec
1755 ,p_txn_rec => p_txn_rec
1756 ,x_return_status => x_return_status
1757 ,x_msg_count => x_msg_count
1758 ,x_msg_data => x_msg_data ) ;
1759
1760 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1761 l_msg_index := 1;
1762 l_msg_count := x_msg_count;
1763 WHILE l_msg_count > 0
1764 LOOP
1765 x_msg_data := FND_MSG_PUB.GET(
1766 l_msg_index,
1767 FND_API.G_FALSE );
1768 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1769 l_msg_index := l_msg_index + 1;
1770 l_msg_count := l_msg_count - 1;
1771 END LOOP;
1772 RAISE FND_API.G_EXC_ERROR;
1773 END IF;
1774 END LOOP;
1775 END IF;
1776 --End addition for bug 3294748
1777 l_exp_acct_rec:=l_temp_acct_rec;
1778 BEGIN
1779 SELECT ip_account_id,
1780 relationship_type_code,
1781 object_version_number
1782 INTO l_exp_acct_rec.ip_account_id,
1783 l_exp_acct_rec.relationship_type_code,
1784 l_exp_acct_rec.object_version_number
1785 FROM csi_ip_accounts
1786 WHERE instance_party_id=l_cld_party_rec.instance_party_id
1787 AND relationship_type_code ='OWNER';
1788 -- Bug 3804960
1789 -- srramakr Need to use the same the date used by the item instance
1790 IF p_txn_rec.src_txn_creation_date IS NULL OR
1791 p_txn_rec.src_txn_creation_date = FND_API.G_MISS_DATE THEN
1792 l_exp_acct_rec.active_end_date := sysdate;
1793 ELSE
1794 l_exp_acct_rec.active_end_date := p_txn_rec.src_txn_creation_date;
1795 END IF;
1796 -- End of 3804960
1797 l_exp_acct_rec.expire_flag :=fnd_api.g_true;
1798 EXCEPTION
1799 WHEN OTHERS THEN
1800 NULL;
1801 END;
1802
1803 IF l_exp_acct_rec.ip_account_id IS NOT NULL
1804 AND l_exp_acct_rec.ip_account_id <> fnd_api.g_miss_num
1805 THEN
1806 l_exp_acct_rec.grp_call_contracts := l_grp_call_contracts;
1807 csi_party_relationships_pvt.update_inst_party_account
1808 ( p_api_version => p_api_version
1809 ,p_commit => p_commit
1810 ,p_init_msg_list => p_init_msg_list
1811 ,p_validation_level => p_validation_level
1812 ,p_party_account_rec => l_exp_acct_rec
1813 ,p_txn_rec => p_txn_rec
1814 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1815 ,x_return_status => x_return_status
1816 ,x_msg_count => x_msg_count
1817 ,x_msg_data => x_msg_data);
1818
1819 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1820 l_msg_index := 1;
1821 l_msg_count := x_msg_count;
1822 WHILE l_msg_count > 0 LOOP
1823 x_msg_data := FND_MSG_PUB.GET(
1824 l_msg_index,
1825 FND_API.G_FALSE );
1826 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1827 l_msg_index := l_msg_index + 1;
1828 l_msg_count := l_msg_count - 1;
1829 END LOOP;
1830 RAISE FND_API.G_EXC_ERROR;
1831 END IF;
1832 END IF;
1833 END IF;
1834 -- End addition by sguthiva for bug 2307804
1835 IF l_cld_party_rec.party_id <> l_internal_party_id
1836 THEN
1837 IF l_ip_acct_rec.instance_party_id IS NOT NULL AND
1838 l_ip_acct_rec.instance_party_id <> fnd_api.g_miss_num
1839 THEN
1840 l_ip_acct_rec:=l_new_ip_acct_rec; -- added for 2608706
1841 l_ip_acct_rec.instance_party_id := l_cld_party_rec.instance_party_id;
1842 l_ip_acct_rec.ip_account_id := fnd_api.g_miss_num;
1843 -- srramakr Bug 3621181 need to cascade Bill_to and Ship_to address to the children
1844 -- l_ip_acct_rec.bill_to_address := fnd_api.g_miss_num;
1845 -- l_ip_acct_rec.ship_to_address := fnd_api.g_miss_num;
1846 l_ip_acct_rec.active_start_date := fnd_api.g_miss_date; -- added for 2608706
1847 l_ip_acct_rec.active_end_date := fnd_api.g_miss_date; -- added for 2608706
1848 l_ip_acct_rec.grp_call_contracts := l_grp_call_contracts;
1849 -- Added the following code for bug 2972082
1850 IF nvl(l_old_party_tbl(l_old_party_row).cascade_ownership_flag,'N')='Y'
1851 AND l_cld_party_id <> l_cld_party_rec.party_id
1852 AND l_cld_party_id <> l_internal_party_id
1853 THEN
1854 l_ip_acct_rec.cascade_ownership_flag:='Y';
1855 END IF;
1856 -- End of addition for bug 2972082
1857 -- Need to pass the system_id for components also if the Xfer of ownership is
1858 -- initiated from xfer of system. This will be true if both parent and child instances
1859 -- belong to the same system.
1860 l_ip_acct_rec.system_id := fnd_api.g_miss_num;
1861 --
1862 IF p_party_account_tbl.EXISTS(1) AND
1863 p_party_account_tbl(1).system_id IS NOT NULL AND
1864 p_party_account_tbl(1).system_id <> FND_API.G_MISS_NUM AND
1865 NVL(p_party_account_tbl(1).relationship_type_code,FND_API.G_MISS_CHAR) = 'OWNER' THEN
1866 Begin
1867 select system_id
1868 into l_ip_acct_rec.system_id
1869 from csi_item_instances
1870 where instance_id = l_rel_tbl(j).subject_id
1871 and nvl(system_id,fnd_api.g_miss_num) = p_party_account_tbl(1).system_id;
1872 Exception
1873 when no_data_found then
1874 l_ip_acct_rec.system_id := fnd_api.g_miss_num;
1875 End;
1876 END IF;
1877 csi_party_relationships_pvt.create_inst_party_account
1878 ( p_api_version => p_api_version
1879 ,p_commit => p_commit
1880 ,p_init_msg_list => p_init_msg_list
1881 ,p_validation_level => p_validation_level
1882 ,p_party_account_rec => l_ip_acct_rec
1883 ,p_txn_rec => p_txn_rec
1884 ,x_return_status => x_return_status
1885 ,x_msg_count => x_msg_count
1886 ,x_msg_data => x_msg_data
1887 ,p_inst_party_tbl => l_inst_party_tbl
1888 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
1889 ,p_site_use_tbl => l_site_use_tbl
1890 ,p_account_count_rec => l_account_count_rec
1891 ,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
1892 );
1893
1894 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1895 l_msg_index := 1;
1896 l_msg_count := x_msg_count;
1897 WHILE l_msg_count > 0 LOOP
1898 x_msg_data := FND_MSG_PUB.GET(
1899 l_msg_index,
1900 FND_API.G_FALSE );
1901 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1902 l_msg_index := l_msg_index + 1;
1903 l_msg_count := l_msg_count - 1;
1904 END LOOP;
1905 RAISE FND_API.G_EXC_ERROR;
1906 END IF;
1907 END IF;
1908 END IF;
1909 -- End commentation by sguthiva for bug 2307804
1910 END IF;
1911
1912 END LOOP;
1913 END IF; -- l_rel_tbl count check
1914
1915 END IF;
1916 END IF;
1917 END IF;
1918 END LOOP;
1919 END IF;
1920 -- End of code addition for fixing the bug 2151750
1921
1922 -- code written by sk on 12/06/01 for fixing TRF bug 2133944
1923 -- End of API body
1924
1925
1926 -- Standard check of p_commit.
1927 IF FND_API.To_Boolean( p_commit ) THEN
1928 COMMIT WORK;
1929 END IF;
1930
1931 /***** srramakr commented for bug # 3304439
1932 -- Check for the profile option and disable the trace
1933 IF (l_flag = 'Y') THEN
1934 dbms_session.set_sql_trace(false);
1935 END IF;
1936 -- End disable trace
1937 ****/
1938
1939 -- Standard call to get message count and if count is get message info.
1940 FND_MSG_PUB.Count_And_Get
1941 (p_count => x_msg_count ,
1942 p_data => x_msg_data );
1943 EXCEPTION
1944 WHEN FND_API.G_EXC_ERROR THEN
1945 ROLLBACK TO update_inst_party_rel_pub;
1946 x_return_status := FND_API.G_RET_STS_ERROR ;
1947 FND_MSG_PUB.Count_And_Get
1948 ( p_count => x_msg_count,
1949 p_data => x_msg_data );
1950 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1951 ROLLBACK TO update_inst_party_rel_pub;
1952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1953 FND_MSG_PUB.Count_And_Get
1954 ( p_count => x_msg_count,
1955 p_data => x_msg_data );
1956 WHEN OTHERS THEN
1957 ROLLBACK TO update_inst_party_rel_pub;
1958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1959 IF FND_MSG_PUB.Check_Msg_Level
1960 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1961 THEN
1962 FND_MSG_PUB.Add_Exc_Msg
1963 ( g_pkg_name, l_api_name );
1964 END IF;
1965 FND_MSG_PUB.Count_And_Get
1966 ( p_count => x_msg_count,
1967 p_data => x_msg_data );
1968 END update_inst_party_relationship ;
1969
1970
1971 /*-------------------------------------------------------------*/
1972 /* Procedure name: Expire_inst_party_relationship */
1973 /* Description : Procedure used to expire an existing */
1974 /* instance -party relationships */
1975 /*-------------------------------------------------------------*/
1976
1977 PROCEDURE expire_inst_party_relationship
1978 ( p_api_version IN NUMBER
1979 ,p_commit IN VARCHAR2
1980 ,p_init_msg_list IN VARCHAR2
1981 ,p_validation_level IN NUMBER
1982 ,p_instance_party_tbl IN csi_datastructures_pub.party_tbl
1983 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
1984 ,x_return_status OUT NOCOPY VARCHAR2
1985 ,x_msg_count OUT NOCOPY NUMBER
1986 ,x_msg_data OUT NOCOPY VARCHAR2
1987 ) IS
1988 l_api_name CONSTANT VARCHAR2(30) := 'EXPIRE_INST_PARTY_RELATIONSHIP';
1989 l_api_version CONSTANT NUMBER := 1.0;
1990 l_csi_debug_level NUMBER;
1991 l_party_rec csi_datastructures_pub.party_rec;
1992 l_msg_index NUMBER;
1993 l_msg_count NUMBER;
1994 l_line_count NUMBER;
1995 l_flag VARCHAR2(1) :='N';
1996
1997 BEGIN
1998 -- Standard Start of API savepoint
1999 SAVEPOINT expire_inst_party_rel_pub;
2000
2001 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2002
2003 csi_utility_grp.check_ib_active;
2004
2005 -- Standard call to check for call compatibility.
2006 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2007 p_api_version ,
2008 l_api_name ,
2009 g_pkg_name )
2010 THEN
2011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2012 END IF;
2013
2014 -- Initialize message list if p_init_msg_list is set to TRUE.
2015 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2016 FND_MSG_PUB.initialize;
2017 END IF;
2018
2019 -- Initialize API return status to success
2020 x_return_status := FND_API.G_RET_STS_SUCCESS;
2021
2022 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2023 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2024
2025 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2026 IF (l_csi_debug_level > 0) THEN
2027 csi_gen_utility_pvt.put_line( 'expire_inst_party_relationship');
2028 END IF;
2029
2030 -- If the debug level = 2 then dump all the parameters values.
2031 IF (l_csi_debug_level > 1) THEN
2032 csi_gen_utility_pvt.put_line( 'expire_inst_party_relationship:' ||
2033 p_api_version ||'-'||
2034 p_commit ||'-'||
2035 p_init_msg_list ||'-'||
2036 p_validation_level );
2037 -- Dump the records in the log file
2038 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2039 csi_gen_utility_pvt.dump_party_tbl(p_instance_party_tbl);
2040 END IF;
2041
2042 /***** srramakr commented for bug # 3304439
2043 -- Check for the profile option and enable trace
2044 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2045 -- End enable trace
2046 ****/
2047
2048 -- Start API body
2049 --
2050 IF p_instance_party_tbl.count > 0 THEN
2051 FOR l_count IN p_instance_party_tbl.FIRST..p_instance_party_tbl.LAST LOOP
2052 IF p_instance_party_tbl.EXISTS(l_count) THEN
2053 csi_party_relationships_pvt.expire_inst_party_relationship
2054 ( p_api_version => p_api_version,
2055 p_commit => p_commit,
2056 p_init_msg_list => p_init_msg_list,
2057 p_validation_level => p_validation_level,
2058 p_instance_party_rec=> p_instance_party_tbl(l_count),
2059 p_txn_rec => p_txn_rec,
2060 x_return_status => x_return_status ,
2061 x_msg_count => x_msg_count ,
2062 x_msg_data => x_msg_data ) ;
2063
2064 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2065 l_msg_index := 1;
2066 l_msg_count := x_msg_count;
2067 WHILE l_msg_count > 0 LOOP
2068 x_msg_data := FND_MSG_PUB.GET(
2069 l_msg_index,
2070 FND_API.G_FALSE );
2071 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2072 l_msg_index := l_msg_index + 1;
2073 l_msg_count := l_msg_count - 1;
2074 END LOOP;
2075 RAISE FND_API.G_EXC_ERROR;
2076 END IF;
2077 END IF;
2078 END LOOP;
2079 END IF;
2080 --
2081 -- End of API body
2082
2083 -- Standard check of p_commit.
2084 IF FND_API.To_Boolean( p_commit ) THEN
2085 COMMIT WORK;
2086 END IF;
2087
2088 /***** srramakr commented for bug # 3304439
2089 -- Check for the profile option and disable the trace
2090 IF (l_flag = 'Y') THEN
2091 dbms_session.set_sql_trace(false);
2092 END IF;
2093 -- End disable trace
2094 ****/
2095
2096 -- Standard call to get message count and if count is get message info.
2097 FND_MSG_PUB.Count_And_Get
2098 (p_count => x_msg_count ,
2099 p_data => x_msg_data );
2100 EXCEPTION
2101 WHEN FND_API.G_EXC_ERROR THEN
2102 ROLLBACK TO expire_inst_party_rel_pub;
2103 x_return_status := FND_API.G_RET_STS_ERROR ;
2104 FND_MSG_PUB.Count_And_Get
2105 ( p_count => x_msg_count,
2106 p_data => x_msg_data );
2107
2108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2109 ROLLBACK TO expire_inst_party_rel_pub;
2110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2111 FND_MSG_PUB.Count_And_Get
2112 ( p_count => x_msg_count,
2113 p_data => x_msg_data );
2114
2115 WHEN OTHERS THEN
2116 ROLLBACK TO expire_inst_party_relationship;
2117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2118 IF FND_MSG_PUB.Check_Msg_Level
2119 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2120 THEN
2121 FND_MSG_PUB.Add_Exc_Msg
2122 ( g_pkg_name, l_api_name );
2123 END IF;
2124 FND_MSG_PUB.Count_And_Get
2125 ( p_count => x_msg_count,
2126 p_data => x_msg_data );
2127
2128 END expire_inst_party_relationship;
2129
2130 /*---------------------------------------------------------*/
2131 /* Procedure name: Get_inst_party_account */
2132 /* Description : Procedure used to get information about */
2133 /* the accounts related to an instance-party */
2134 /*---------------------------------------------------------*/
2135
2136 PROCEDURE get_inst_party_accounts
2137 ( p_api_version IN NUMBER
2138 ,p_commit IN VARCHAR2
2139 ,p_init_msg_list IN VARCHAR2
2140 ,p_validation_level IN NUMBER
2141 ,p_account_query_rec IN csi_datastructures_pub.party_account_query_rec
2142 ,p_resolve_id_columns IN VARCHAR2
2143 ,p_time_stamp IN DATE
2144 ,x_account_header_tbl OUT NOCOPY csi_datastructures_pub.party_account_header_tbl
2145 ,x_return_status OUT NOCOPY VARCHAR2
2146 ,x_msg_count OUT NOCOPY NUMBER
2147 ,x_msg_data OUT NOCOPY VARCHAR2
2148 ) IS
2149
2150 l_api_name CONSTANT VARCHAR2(30) := 'GET_INST_PARTY_ACCOUNT';
2151 l_api_version CONSTANT NUMBER := 1.0;
2152 l_csi_debug_level NUMBER;
2153 l_instance_party_account_id NUMBER;
2154 l_party_account_tbl csi_datastructures_pub.party_account_tbl;
2155 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
2156 l_line_count NUMBER;
2157 l_msg_index NUMBER;
2158 l_count NUMBER := 0;
2159 l_where_clause VARCHAR2(2000) ;
2160 l_get_acct_cursor_id NUMBER ;
2161 l_rows_processed NUMBER ;
2162 l_flag VARCHAR2(1) :='N';
2163 l_party_account_rec csi_datastructures_pub.party_account_header_rec;
2164 l_select_stmt VARCHAR2(2000) := ' SELECT ip_account_id , instance_party_id, party_account_id, '||
2165 ' relationship_type_code, active_start_date, active_end_date,context , attribute1, '||
2166 ' attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,attribute8, '||
2167 ' attribute9, attribute10,attribute11, attribute12,attribute13,attribute14,attribute15, '||
2168 ' object_version_number, bill_to_address, ship_to_address from csi_ip_accounts ';
2169
2170
2171 BEGIN
2172 -- Standard Start of API savepoint
2173 -- SAVEPOINT get_inst_party_acct_pub;
2174
2175 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2176
2177 csi_utility_grp.check_ib_active;
2178
2179
2180 -- Standard call to check for call compatibility.
2181 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2182 p_api_version ,
2183 l_api_name ,
2184 g_pkg_name )
2185 THEN
2186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2187 END IF;
2188
2189 -- Initialize message list if p_init_msg_list is set to TRUE.
2190 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2191 FND_MSG_PUB.initialize;
2192 END IF;
2193
2194 -- Initialize API return status to success
2195 x_return_status := FND_API.G_RET_STS_SUCCESS;
2196
2197 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2198 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2199
2200 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2201 IF (l_csi_debug_level > 0) THEN
2202 csi_gen_utility_pvt.put_line( 'get_inst_party_accounts');
2203 END IF;
2204
2205 -- If the debug level = 2 then dump all the parameters values.
2206 IF (l_csi_debug_level > 1) THEN
2207
2208 csi_gen_utility_pvt.put_line( 'get_inst_party_account:' ||
2209 p_api_version ||'-'||
2210 p_commit ||'-'||
2211 p_init_msg_list ||'-'||
2212 p_validation_level ||'-'||
2213 p_time_stamp );
2214 -- Dump the account query records
2215 csi_gen_utility_pvt.dump_account_query_rec(p_account_query_rec);
2216
2217 END IF;
2218
2219 /***** srramakr commented for bug # 3304439
2220 -- Check for the profile option and enable trace
2221 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2222 -- End enable trace
2223 ****/
2224
2225 -- Start API body
2226 --
2227 IF (p_account_query_rec.ip_account_id = FND_API.G_MISS_NUM)
2228 AND (p_account_query_rec.instance_party_id = FND_API.G_MISS_NUM)
2229 AND (p_account_query_rec.party_account_id = FND_API.G_MISS_NUM)
2230 AND (p_account_query_rec.relationship_type_code = FND_API.G_MISS_CHAR) THEN
2231
2232 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
2233 FND_MSG_PUB.ADD;
2234 RAISE FND_API.G_EXC_ERROR;
2235 END IF;
2236
2237 -- Generate the where clause
2238 csi_party_relationships_pvt.Gen_Acct_Where_Clause
2239 ( p_pty_acct_query_rec => p_account_query_rec,
2240 x_where_clause => l_where_clause );
2241
2242 -- Build the select statement
2243 l_select_stmt := l_select_stmt || ' where '||l_where_clause;
2244
2245 -- Open the cursor
2246 l_get_acct_cursor_id := dbms_sql.open_cursor;
2247
2248 --Parse the select statement
2249 dbms_sql.parse(l_get_acct_cursor_id, l_select_stmt , dbms_sql.native);
2250
2251 -- Bind the variables
2252 csi_party_relationships_pvt.Bind_acct_variable(p_account_query_rec, l_get_acct_cursor_id);
2253
2254 -- Define output variables
2255 csi_party_relationships_pvt.Define_Acct_Columns(l_get_acct_cursor_id);
2256
2257 -- execute the select statement
2258 l_rows_processed := dbms_sql.execute(l_get_acct_cursor_id);
2259
2260 LOOP
2261 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_get_acct_cursor_id) = 0;
2262 csi_party_relationships_pvt.Get_acct_Column_Values(l_get_acct_cursor_id, l_party_account_rec);
2263 l_count := l_count + 1;
2264 x_account_header_tbl(l_count) := l_party_account_rec;
2265 END LOOP;
2266
2267 -- Close the cursor
2268 DBMS_SQL.CLOSE_CURSOR(l_get_acct_cursor_id);
2269
2270 IF ((p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE)) THEN
2271 IF p_time_stamp <= sysdate THEN
2272 -- Contruct from the history if p_time_stamp is less than sysdate
2273 csi_party_relationships_pvt.Construct_acct_from_hist(x_account_header_tbl, p_time_stamp);
2274 ELSE
2275 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
2276 FND_MSG_PUB.ADD;
2277 RAISE FND_API.G_EXC_ERROR;
2278 END IF;
2279 END IF;
2280
2281 -- foreign key resolution for the id columns
2282 IF p_resolve_id_columns = fnd_api.g_true THEN
2283 IF x_account_header_tbl.count > 0 THEN
2284 l_account_header_tbl := x_account_header_tbl;
2285 csi_party_relationships_pvt.Resolve_id_columns(l_account_header_tbl);
2286
2287 x_account_header_tbl := l_account_header_tbl;
2288 END IF;
2289 END IF;
2290
2291 --
2292 -- End of API body
2293
2294 -- Standard check of p_commit.
2295 /*
2296 IF FND_API.To_Boolean( p_commit ) THEN
2297 COMMIT WORK;
2298 END IF;
2299 */
2300
2301 /***** srramakr commented for bug # 3304439
2302 -- Check for the profile option and disable the trace
2303 IF (l_flag = 'Y') THEN
2304 dbms_session.set_sql_trace(false);
2305 END IF;
2306 -- End disable trace
2307 ****/
2308
2309 -- Standard call to get message count and if count is get message info.
2310 FND_MSG_PUB.Count_And_Get
2311 (p_count => x_msg_count ,
2312 p_data => x_msg_data );
2313 EXCEPTION
2314 WHEN FND_API.G_EXC_ERROR THEN
2315 -- ROLLBACK TO get_inst_party_acct_pub;
2316 x_return_status := FND_API.G_RET_STS_ERROR ;
2317 FND_MSG_PUB.Count_And_Get
2318 ( p_count => x_msg_count,
2319 p_data => x_msg_data );
2320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2321 -- ROLLBACK TO get_inst_party_acct_pub;
2322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2323 FND_MSG_PUB.Count_And_Get
2324 ( p_count => x_msg_count,
2325 p_data => x_msg_data );
2326 WHEN OTHERS THEN
2327 IF dbms_sql.is_open(l_get_acct_cursor_id) then
2328 dbms_sql.close_cursor(l_get_acct_cursor_id);
2329 END IF;
2330 -- ROLLBACK TO get_inst_party_acct_pub;
2331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2332 IF FND_MSG_PUB.Check_Msg_Level
2333 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2334 THEN
2335 FND_MSG_PUB.Add_Exc_Msg
2336 ( g_pkg_name, l_api_name );
2337 END IF;
2338 FND_MSG_PUB.Count_And_Get
2339 ( p_count => x_msg_count,
2340 p_data => x_msg_data );
2341 END get_inst_party_accountS ;
2342
2343 /*----------------------------------------------------------*/
2344 /* Procedure name: Create_inst_party_account */
2345 /* Description : Procedure used to create new */
2346 /* instance-party account relationships */
2347 /*----------------------------------------------------------*/
2348
2349 PROCEDURE create_inst_party_account
2350 ( p_api_version IN NUMBER
2351 ,p_commit IN VARCHAR2
2352 ,p_init_msg_list IN VARCHAR2
2353 ,p_validation_level IN NUMBER
2354 ,p_party_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
2355 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2356 ,x_return_status OUT NOCOPY VARCHAR2
2357 ,x_msg_count OUT NOCOPY NUMBER
2358 ,x_msg_data OUT NOCOPY VARCHAR2
2359 ) IS
2360
2361 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INST_PARTY_ACCOUNT';
2362 l_api_version CONSTANT NUMBER := 1.0;
2363 l_csi_debug_level NUMBER;
2364 l_party_account_rec csi_datastructures_pub.party_account_rec;
2365 l_msg_index NUMBER;
2366 l_msg_count NUMBER;
2367 l_line_count NUMBER;
2368 l_flag VARCHAR2(1) :='N';
2369 l_inst_party_tbl csi_party_relationships_pvt.inst_party_tbl;
2370 l_acct_rel_type_tbl csi_party_relationships_pvt.acct_rel_type_tbl;
2371 l_site_use_tbl csi_party_relationships_pvt.site_use_tbl;
2372 l_account_count_rec csi_party_relationships_pvt.account_count_rec;
2373 --
2374 px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
2375
2376 BEGIN
2377 -- Standard Start of API savepoint
2378 SAVEPOINT create_inst_party_acct_pub;
2379
2380 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2381
2382 csi_utility_grp.check_ib_active;
2383
2384
2385 -- Standard call to check for call compatibility.
2386 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2387 p_api_version ,
2388 l_api_name ,
2389 g_pkg_name )
2390 THEN
2391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2392 END IF;
2393
2394 -- Initialize message list if p_init_msg_list is set to TRUE.
2395 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2396 FND_MSG_PUB.initialize;
2397 END IF;
2398
2399 -- Initialize API return status to success
2400 x_return_status := FND_API.G_RET_STS_SUCCESS;
2401
2402 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2403 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2404
2405 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2406 IF (l_csi_debug_level > 0) THEN
2407 csi_gen_utility_pvt.put_line( 'create_inst_party_account');
2408 END IF;
2409
2410 -- If the debug level = 2 then dump all the parameters values.
2411 IF (l_csi_debug_level > 1) THEN
2412 csi_gen_utility_pvt.put_line( 'create_inst_party_account:'||
2413 p_api_version ||'-'||
2414 p_commit ||'-'||
2415 p_init_msg_list );
2416 -- Dump the records in the log file
2417 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
2418 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2419 END IF;
2420
2421 /***** srramakr commented for bug # 3304439
2422 -- Check for the profile option and enable trace
2423 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2424 -- End enable trace
2425 ****/
2426
2427 -- Start API body
2428 --
2429 -- Check if the table is not empty
2430 IF p_party_account_tbl.count > 0 THEN
2431 FOR l_count IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
2432 IF p_party_account_tbl.EXISTS(l_count) THEN
2433
2434 -- Call Private package to validate and create party accounts
2435 csi_party_relationships_pvt.create_inst_party_account
2436 ( p_api_version => p_api_version
2437 ,p_commit => p_commit
2438 ,p_init_msg_list => p_init_msg_list
2439 ,p_validation_level => p_validation_level
2440 ,p_party_account_rec => p_party_account_tbl(l_count)
2441 ,p_txn_rec => p_txn_rec
2442 ,x_return_status => x_return_status
2443 ,x_msg_count => x_msg_count
2444 ,x_msg_data => x_msg_data
2445 ,p_inst_party_tbl => l_inst_party_tbl
2446 ,p_acct_rel_type_tbl => l_acct_rel_type_tbl
2447 ,p_site_use_tbl => l_site_use_tbl
2448 ,p_account_count_rec => l_account_count_rec
2449 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2450 );
2451
2452 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2453 l_msg_index := 1;
2454 l_msg_count := x_msg_count;
2455 WHILE l_msg_count > 0 LOOP
2456 x_msg_data := FND_MSG_PUB.GET(
2457 l_msg_index,
2458 FND_API.G_FALSE );
2459 csi_gen_utility_pvt.put_line( 'message data = '||x_msg_data);
2460 l_msg_index := l_msg_index + 1;
2461 l_msg_count := l_msg_count - 1;
2462 END LOOP;
2463 RAISE FND_API.G_EXC_ERROR;
2464 END IF;
2465 END IF;
2466 END LOOP;
2467 END IF;
2468 --
2469 -- End of API body
2470
2471 -- Standard check of p_commit.
2472 IF FND_API.To_Boolean( p_commit ) THEN
2473 COMMIT WORK;
2474 END IF;
2475
2476 /***** srramakr commented for bug # 3304439
2477 -- Check for the profile option and disable the trace
2478 IF (l_flag = 'Y') THEN
2479 dbms_session.set_sql_trace(false);
2480 END IF;
2481 -- End disable trace
2482 ****/
2483
2484 -- Standard call to get message count and if count is get message info.
2485 FND_MSG_PUB.Count_And_Get
2486 (p_count => x_msg_count ,
2487 p_data => x_msg_data );
2488 EXCEPTION
2489 WHEN FND_API.G_EXC_ERROR THEN
2490 ROLLBACK TO create_inst_party_acct_pub;
2491 x_return_status := FND_API.G_RET_STS_ERROR ;
2492 FND_MSG_PUB.Count_And_Get
2493 ( p_count => x_msg_count,
2494 p_data => x_msg_data );
2495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2496 ROLLBACK TO create_inst_party_acct_pub;
2497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2498 FND_MSG_PUB.Count_And_Get
2499 ( p_count => x_msg_count,
2500 p_data => x_msg_data );
2501 WHEN OTHERS THEN
2502 ROLLBACK TO create_inst_party_acct_pub;
2503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2504 IF FND_MSG_PUB.Check_Msg_Level
2505 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2506 THEN
2507 FND_MSG_PUB.Add_Exc_Msg
2508 ( g_pkg_name, l_api_name );
2509 END IF;
2510 FND_MSG_PUB.Count_And_Get
2511 ( p_count => x_msg_count,
2512 p_data => x_msg_data );
2513 END create_inst_party_account;
2514
2515 /*------------------------------------------------------------*/
2516 /* Procedure name: Update_inst_party_account */
2517 /* Description : Procedure used to update the existing */
2518 /* instance-party account relationships */
2519 /*------------------------------------------------------------*/
2520
2521 PROCEDURE update_inst_party_account
2522 ( p_api_version IN NUMBER
2523 ,p_commit IN VARCHAR2
2524 ,p_init_msg_list IN VARCHAR2
2525 ,p_validation_level IN NUMBER
2526 ,p_party_account_tbl IN csi_datastructures_pub.party_account_tbl
2527 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2528 ,x_return_status OUT NOCOPY VARCHAR2
2529 ,x_msg_count OUT NOCOPY NUMBER
2530 ,x_msg_data OUT NOCOPY VARCHAR2
2531 ) IS
2532 /*** CURSOR party_account_csr (p_ins_pty_id IN NUMBER) IS
2533 SELECT subject_id
2534 FROM csi_ii_relationships
2535 WHERE relationship_type_code = 'COMPONENT-OF'
2536 START WITH object_id = (SELECT instance_id+0
2537 FROM csi_i_parties
2538 WHERE instance_party_id = p_ins_pty_id
2539 AND relationship_type_code = 'OWNER'
2540 AND (active_end_date IS NULL OR active_end_date > sysdate)
2541 )
2542 CONNECT BY object_id = PRIOR subject_id; ***/
2543
2544 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_ACCOUNT';
2545 l_api_version CONSTANT NUMBER := 1.0;
2546 l_csi_debug_level NUMBER;
2547 l_party_account_rec csi_datastructures_pub.party_account_rec;
2548 l_msg_index NUMBER;
2549 l_msg_count NUMBER;
2550 l_line_count NUMBER;
2551 l_flag VARCHAR2(1) :='N';
2552 l_party_account_tbl csi_datastructures_pub.party_account_tbl := p_party_account_tbl;
2553 l_temp_account_tbl csi_datastructures_pub.party_account_tbl;
2554 old_party_account_id NUMBER;
2555 l_acct_row NUMBER :=1;
2556 old_party_id NUMBER;
2557 l_party_id NUMBER;
2558 p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
2559 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
2560 l_object_id NUMBER;
2561 l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
2562 px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
2563 BEGIN
2564 -- Standard Start of API savepoint
2565 SAVEPOINT update_inst_party_acct_pub;
2566
2567 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2568
2569 csi_utility_grp.check_ib_active;
2570
2571
2572 -- Standard call to check for call compatibility.
2573 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2574 p_api_version ,
2575 l_api_name ,
2576 g_pkg_name )
2577 THEN
2578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2579 END IF;
2580
2581 -- Initialize message list if p_init_msg_list is set to TRUE.
2582 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2583 FND_MSG_PUB.initialize;
2584 END IF;
2585
2586 -- Initialize API return status to success
2587 x_return_status := FND_API.G_RET_STS_SUCCESS;
2588
2589 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2590 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2591
2592 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2593 IF (l_csi_debug_level > 0) THEN
2594 csi_gen_utility_pvt.put_line( 'update_inst_party_account');
2595 END IF;
2596
2597 -- If the debug level = 2 then dump all the parameters values.
2598 IF (l_csi_debug_level > 1) THEN
2599 csi_gen_utility_pvt.put_line( 'update_inst_party_account:'||
2600 p_api_version ||'-'||
2601 p_commit ||'-'||
2602 p_init_msg_list );
2603 -- Dump the records in the log file
2604 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
2605 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2606 END IF;
2607
2608 /***** srramakr commented for bug # 3304439
2609 -- Check for the profile option and enable trace
2610 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2611 -- End enable trace
2612 ****/
2613
2614 -- Start API body
2615 --
2616 -- Check if the table is not empty
2617 IF p_party_account_tbl.count > 0 THEN
2618 FOR l_count IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
2619 IF p_party_account_tbl.EXISTS(l_count) THEN
2620 IF p_party_account_tbl(l_count).ip_account_id IS NOT NULL AND
2621 p_party_account_tbl(l_count).ip_account_id <> fnd_api.g_miss_num
2622 THEN
2623 BEGIN
2624 SELECT acct.ip_account_id,
2625 acct.party_account_id,
2626 pty.party_id
2627 INTO l_temp_account_tbl(l_acct_row).ip_account_id,
2628 l_temp_account_tbl(l_acct_row).party_account_id,
2629 l_temp_account_tbl(l_acct_row).attribute1
2630 FROM csi_ip_accounts acct,
2631 csi_i_parties pty
2632 WHERE acct.ip_account_id = p_party_account_tbl(l_count).ip_account_id
2633 AND acct.instance_party_id = pty.instance_party_id;
2634
2635 l_acct_row := l_acct_row+1;
2636 EXCEPTION
2637 WHEN OTHERS THEN
2638 NULL;
2639 END;
2640 END IF;
2641 -- dbms_output.put_line('PUB: caling update_inst_party_account');
2642 csi_party_relationships_pvt.update_inst_party_account
2643 ( p_api_version => p_api_version
2644 ,p_commit => p_commit
2645 ,p_init_msg_list => p_init_msg_list
2646 ,p_validation_level => p_validation_level
2647 ,p_party_account_rec => p_party_account_tbl(l_count)
2648 ,p_txn_rec => p_txn_rec
2649 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2650 ,x_return_status => x_return_status
2651 ,x_msg_count => x_msg_count
2652 ,x_msg_data => x_msg_data);
2653
2654 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2655 l_msg_index := 1;
2656 l_msg_count := x_msg_count;
2657 WHILE l_msg_count > 0 LOOP
2658 x_msg_data := FND_MSG_PUB.GET(
2659 l_msg_index,
2660 FND_API.G_FALSE );
2661 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2662 l_msg_index := l_msg_index + 1;
2663 l_msg_count := l_msg_count - 1;
2664 END LOOP;
2665 RAISE FND_API.G_EXC_ERROR;
2666 END IF;
2667 END IF;
2668 END LOOP;
2669 END IF;
2670
2671 -- If the owner account (party_account_id) has been changed then if the Instance has any children
2672 -- then all the children (in COMPONENT-OF relationship_type_code) has to get the same party_account_id.
2673 -- Start of fix for bug 2151750
2674 IF l_party_account_tbl.count > 0
2675 THEN
2676 FOR l_old_party_row IN l_party_account_tbl.FIRST..l_party_account_tbl.LAST
2677 LOOP
2678 IF l_party_account_tbl.EXISTS(l_old_party_row) -- Added for bug 3776650
2679 THEN
2680 old_party_account_id := NULL;
2681 old_party_id := NULL;
2682 IF l_temp_account_tbl.COUNT > 0
2683 THEN
2684 FOR l_old_acct IN l_temp_account_tbl.FIRST..l_temp_account_tbl.LAST
2685 LOOP
2686 IF l_temp_account_tbl.EXISTS(l_old_acct) -- Added for bug 3776650
2687 THEN
2688 IF l_temp_account_tbl(l_old_acct).ip_account_id = l_party_account_tbl(l_old_party_row).ip_account_id
2689 THEN
2690 old_party_account_id := l_temp_account_tbl(l_old_acct).ip_account_id;
2691 old_party_id := l_temp_account_tbl(l_old_acct).attribute1;
2692 EXIT;
2693 END IF;
2694 END IF;
2695 END LOOP;
2696 END IF;
2697
2698 IF l_party_account_tbl(l_old_party_row).party_account_id IS NOT NULL AND
2699 l_party_account_tbl(l_old_party_row).party_account_id <> FND_API.G_MISS_NUM AND
2700 l_party_account_tbl(l_old_party_row).relationship_type_code = 'OWNER' AND
2701 old_party_account_id IS NOT NULL AND
2702 l_party_account_tbl(l_old_party_row).party_account_id <> old_party_account_id
2703 THEN
2704 l_object_id := null;
2705 Begin
2706 select instance_id
2707 into l_object_id
2708 from CSI_I_PARTIES
2709 where instance_party_id = l_party_account_tbl(l_old_party_row).instance_party_id
2710 and relationship_type_code = 'OWNER'
2711 and (active_end_date IS NULL OR active_end_date > sysdate);
2712 Exception
2713 when no_data_found then
2714 l_object_id := null;
2715 End;
2716 --
2717 IF l_object_id IS NOT NULL THEN
2718 p_rel_query_rec.object_id := l_object_id;
2719 p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
2720 --
2721 csi_ii_relationships_pvt.Get_Children
2722 ( p_relationship_query_rec => p_rel_query_rec,
2723 p_rel_tbl => l_rel_tbl,
2724 p_depth => NULL,
2725 p_active_relationship_only => FND_API.G_TRUE,
2726 p_time_stamp => FND_API.G_MISS_DATE,
2727 p_get_dfs => FND_API.G_FALSE,
2728 p_ii_relationship_level_tbl => l_ii_relationship_level_tbl,
2729 x_return_status => x_return_status,
2730 x_msg_count => x_msg_count,
2731 x_msg_data => x_msg_data
2732 );
2733 --
2734 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2735 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_CHILDREN_ERROR');
2736 FND_MSG_PUB.Add;
2737 RAISE FND_API.G_EXC_ERROR;
2738 END IF;
2739 --
2740 IF l_rel_tbl.count > 0 THEN
2741 FOR j in l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
2742 -- FOR l_pty_acct_csr IN party_account_csr(l_party_account_tbl(l_old_party_row).instance_party_id)
2743 l_party_account_tbl(l_old_party_row).ip_account_id := fnd_api.g_miss_num;
2744 l_party_account_tbl(l_old_party_row).instance_party_id := fnd_api.g_miss_num;
2745 l_party_account_tbl(l_old_party_row).object_version_number := fnd_api.g_miss_num;
2746 l_party_account_tbl(l_old_party_row).active_start_date :=fnd_api.g_miss_date;
2747 l_party_account_tbl(l_old_party_row).active_end_date :=fnd_api.g_miss_date;
2748 BEGIN
2749 l_party_id := NULL;
2750 SELECT acct.ip_account_id,
2751 acct.object_version_number,
2752 pty.party_id
2753 INTO l_party_account_tbl(l_old_party_row).ip_account_id,
2754 l_party_account_tbl(l_old_party_row).object_version_number,
2755 l_party_id
2756 FROM csi_ip_accounts acct,
2757 csi_i_parties pty
2758 WHERE pty.instance_party_id = acct.instance_party_id
2759 AND pty.instance_id = l_rel_tbl(j).subject_id
2760 AND acct.relationship_type_code = 'OWNER'
2761 AND (acct.active_end_date IS NULL OR
2762 acct.active_end_date > SYSDATE);
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 NULL;
2766 END;
2767 --
2768 IF l_party_account_tbl(l_old_party_row).ip_account_id IS NOT NULL AND
2769 l_party_account_tbl(l_old_party_row).ip_account_id <> fnd_api.g_miss_num AND
2770 old_party_id = l_party_id
2771 THEN
2772 csi_party_relationships_pvt.update_inst_party_account
2773 ( p_api_version => p_api_version
2774 ,p_commit => p_commit
2775 ,p_init_msg_list => p_init_msg_list
2776 ,p_validation_level => p_validation_level
2777 ,p_party_account_rec => l_party_account_tbl(l_old_party_row)
2778 ,p_txn_rec => p_txn_rec
2779 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2780 ,x_return_status => x_return_status
2781 ,x_msg_count => x_msg_count
2782 ,x_msg_data => x_msg_data);
2783
2784 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2785 l_msg_index := 1;
2786 l_msg_count := x_msg_count;
2787 WHILE l_msg_count > 0
2788 LOOP
2789 x_msg_data := FND_MSG_PUB.GET(
2790 l_msg_index,
2791 FND_API.G_FALSE );
2792 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2793 l_msg_index := l_msg_index + 1;
2794 l_msg_count := l_msg_count - 1;
2795 END LOOP;
2796 RAISE FND_API.G_EXC_ERROR;
2797 END IF;
2798 END IF; -- ip_account_id not null check
2799 END LOOP; -- l_rel_tbl loop
2800 END IF; -- l_rel_tbl count check
2801 END IF; -- l_object_id check
2802 END IF;
2803 END IF;
2804 END LOOP;
2805 END IF;
2806
2807 -- End of fix for bug 2151750
2808 --
2809 -- End of API body
2810
2811 -- Standard check of p_commit.
2812 IF FND_API.To_Boolean( p_commit ) THEN
2813 COMMIT WORK;
2814 END IF;
2815
2816 /***** srramakr commented for bug # 3304439
2817 -- Check for the profile option and disable the trace
2818 IF (l_flag = 'Y') THEN
2819 dbms_session.set_sql_trace(false);
2820 END IF;
2821 -- End disable trace
2822 ****/
2823
2824 -- Standard call to get message count and if count is get message info.
2825 FND_MSG_PUB.Count_And_Get
2826 (p_count => x_msg_count ,
2827 p_data => x_msg_data );
2828 EXCEPTION
2829 WHEN FND_API.G_EXC_ERROR THEN
2830 ROLLBACK TO update_inst_party_acct_pub;
2831 x_return_status := FND_API.G_RET_STS_ERROR ;
2832 FND_MSG_PUB.Count_And_Get
2833 ( p_count => x_msg_count,
2834 p_data => x_msg_data );
2835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2836 ROLLBACK TO update_inst_party_acct_pub;
2837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2838 FND_MSG_PUB.Count_And_Get
2839 ( p_count => x_msg_count,
2840 p_data => x_msg_data );
2841 WHEN OTHERS THEN
2842 ROLLBACK TO update_inst_party_acct_pub;
2843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2844 IF FND_MSG_PUB.Check_Msg_Level
2845 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2846 THEN
2847 FND_MSG_PUB.Add_Exc_Msg
2848 ( g_pkg_name, l_api_name );
2849 END IF;
2850
2851 FND_MSG_PUB.Count_And_Get
2852 ( p_count => x_msg_count,
2853 p_data => x_msg_data );
2854 END update_inst_party_account ;
2855
2856 /*--------------------------------------------------------*/
2857 /* Procedure name: Expire_inst_party_account */
2858 /* Description : Procedure used to expire an existing */
2859 /* instance-party account relationships */
2860 /*--------------------------------------------------------*/
2861
2862 PROCEDURE expire_inst_party_account
2863 ( p_api_version IN NUMBER
2864 ,p_commit IN VARCHAR2
2865 ,p_init_msg_list IN VARCHAR2
2866 ,p_validation_level IN NUMBER
2867 ,p_party_account_tbl IN csi_datastructures_pub.party_account_tbl
2868 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2869 ,x_return_status OUT NOCOPY VARCHAR2
2870 ,x_msg_count OUT NOCOPY NUMBER
2871 ,x_msg_data OUT NOCOPY VARCHAR2
2872 ) IS
2873
2874 l_api_name CONSTANT VARCHAR2(30) := 'EXPIRE_INST_PARTY_ACCOUNT';
2875 l_api_version CONSTANT NUMBER := 1.0;
2876 l_csi_debug_level NUMBER;
2877 l_msg_index NUMBER ;
2878 l_msg_count NUMBER;
2879 l_party_account_rec csi_datastructures_pub.party_account_rec;
2880 l_line_count NUMBER := 0 ;
2881 l_ip_account_id NUMBER ;
2882 l_flag VARCHAR2(1) :='N';
2883
2884 BEGIN
2885 -- Standard Start of API savepoint
2886 SAVEPOINT expire_inst_party_acct_pub;
2887
2888 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
2889
2890 csi_utility_grp.check_ib_active;
2891
2892
2893 -- Standard call to check for call compatibility.
2894 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2895 p_api_version ,
2896 l_api_name ,
2897 g_pkg_name )
2898 THEN
2899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2900 END IF;
2901
2902 -- Initialize message list if p_init_msg_list is set to TRUE.
2903 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2904 FND_MSG_PUB.initialize;
2905 END IF;
2906
2907 -- Initialize API return status to success
2908 x_return_status := FND_API.G_RET_STS_SUCCESS;
2909
2910 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2911 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2912
2913 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2914 IF (l_csi_debug_level > 0) THEN
2915 csi_gen_utility_pvt.put_line( 'expire_inst_party_account');
2916 END IF;
2917
2918
2919 -- If the debug level = 2 then dump all the parameters values.
2920 IF (l_csi_debug_level > 1) THEN
2921 csi_gen_utility_pvt.put_line( 'expire_inst_party_account:'||
2922 p_api_version ||'-'||
2923 p_commit ||'-'||
2924 p_init_msg_list );
2925 -- Dump the records in the log file
2926 csi_gen_utility_pvt.dump_party_account_tbl(p_party_account_tbl);
2927 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2928 END IF;
2929
2930 /***** srramakr commented for bug # 3304439
2931 -- Check for the profile option and enable trace
2932 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2933 -- End enable trace
2934 ****/
2935
2936 -- Start API body
2937 --
2938 IF p_party_account_tbl.count > 0 THEN
2939 FOR l_count IN p_party_account_tbl.FIRST..p_party_account_tbl.LAST LOOP
2940 IF p_party_account_tbl.EXISTS(l_count) THEN
2941 csi_party_relationships_pvt.expire_inst_party_account
2942 ( p_api_version => p_api_version
2943 ,p_commit => p_commit
2944 ,p_init_msg_list => p_init_msg_list
2945 ,p_validation_level => p_validation_level
2946 ,p_party_account_rec => p_party_account_tbl(l_count)
2947 ,p_txn_rec => p_txn_rec
2948 ,x_return_status => x_return_status
2949 ,x_msg_count => x_msg_count
2950 ,x_msg_data => x_msg_data );
2951
2952
2953 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2954 l_msg_index := 1;
2955 l_msg_count := x_msg_count;
2956 WHILE l_msg_count > 0 LOOP
2957 x_msg_data := FND_MSG_PUB.GET(
2958 l_msg_index,
2959 FND_API.G_FALSE);
2960 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2961 l_msg_index := l_msg_index + 1;
2962 l_msg_count := l_msg_count - 1;
2963 END LOOP;
2964 RAISE FND_API.G_EXC_ERROR;
2965 END IF;
2966 END IF;
2967 END LOOP;
2968 END IF;
2969 --
2970 -- End of API body
2971
2972 -- Standard check of p_commit.
2973 IF FND_API.To_Boolean( p_commit ) THEN
2974 COMMIT WORK;
2975 END IF;
2976
2977 /***** srramakr commented for bug # 3304439
2978 -- Check for the profile option and disable the trace
2979 IF (l_flag = 'Y') THEN
2980 dbms_session.set_sql_trace(false);
2981 END IF;
2982 -- End disable trace
2983 ****/
2984
2985 -- Standard call to get message count and if count is get message info.
2986 FND_MSG_PUB.Count_And_Get
2987 (p_count => x_msg_count ,
2988 p_data => x_msg_data );
2989 EXCEPTION
2990 WHEN FND_API.G_EXC_ERROR THEN
2991 ROLLBACK TO expire_inst_party_acct_pub;
2992 x_return_status := FND_API.G_RET_STS_ERROR ;
2993 FND_MSG_PUB.Count_And_Get
2994 ( p_count => x_msg_count,
2995 p_data => x_msg_data );
2996
2997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2998 ROLLBACK TO expire_inst_party_acct_pub;
2999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3000 FND_MSG_PUB.Count_And_Get
3001 ( p_count => x_msg_count,
3002 p_data => x_msg_data );
3003 WHEN OTHERS THEN
3004 ROLLBACK TO expire_inst_party_acct_pub;
3005 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3006
3007 IF FND_MSG_PUB.Check_Msg_Level
3008 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3009 THEN
3010 FND_MSG_PUB.Add_Exc_Msg
3011 ( g_pkg_name, l_api_name );
3012 END IF;
3013 FND_MSG_PUB.Count_And_Get
3014 ( p_count => x_msg_count,
3015 p_data => x_msg_data );
3016 END expire_inst_party_account ;
3017 END csi_party_relationships_pub ;
3018
[Home] [Help]