1 PACKAGE BODY Jtf_Msite_Prty_Accss_Mgr_Pvt AS
2 /* $Header: JTFVMPMB.pls 115.2 2001/03/06 12:36:33 pkm ship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- Jtf_Msite_Prty_Accss_Mgr_Pvt
10 --
11 -- PURPOSE
12 --
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- 01/24/01 VPALAIYA Created
18 -- **************************************************************************
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_MSITE_PRTY_ACCSS_MGR_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12):= 'JTFVMPMB.pls';
22
23 --
24 -- Associate (p_party_ids) with p_msite_id.
25 -- x_is_any_duplicate_status will be FND_API.G_RET_STS_SUCCESS, if there is
26 -- no duplicate and will be FND_API.G_RET_STS_ERROR when there is at least 1
27 -- duplicate association attempted
28 --
29 PROCEDURE Associate_Parties_To_MSite
30 (
31 p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
33 p_commit IN VARCHAR2 := FND_API.G_FALSE,
34 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
35 p_msite_id IN NUMBER,
36 p_party_ids IN JTF_NUMBER_TABLE,
37 p_start_date_actives IN JTF_DATE_TABLE,
38 p_end_date_actives IN JTF_DATE_TABLE,
39 x_msite_prty_accss_ids OUT JTF_NUMBER_TABLE,
40 x_duplicate_association_status OUT JTF_VARCHAR2_TABLE_100,
41 x_is_any_duplicate_status OUT VARCHAR2,
42 x_return_status OUT VARCHAR2,
43 x_msg_count OUT NUMBER,
44 x_msg_data OUT VARCHAR2
45 )
46 IS
47 l_api_name CONSTANT VARCHAR2(30) :=
48 'Associate_Parties_To_MSite';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_tmp_id NUMBER;
51
52 CURSOR c1(l_c_msite_id IN NUMBER, l_c_party_id IN NUMBER)
53 IS SELECT msite_prty_accss_id FROM jtf_msite_prty_accss
54 WHERE msite_id = l_c_msite_id
55 AND party_id = l_c_party_id;
56
57 BEGIN
58
59 -- Standard Start of API savepoint
60 SAVEPOINT Associate_Parties_To_Msite_Pvt;
61
62 -- Standard call to check for call compatibility.
63 IF NOT FND_API.Compatible_API_Call(l_api_version,
64 p_api_version,
65 l_api_name,
66 G_PKG_NAME)
67 THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70
71 -- Initialize message list if p_init_msg_list is set to TRUE.
72 IF FND_API.to_Boolean(p_init_msg_list) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 -- Initialize API return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 -- Check if the association already exists. Populate the
80 -- x_duplicate_association_status with the appropriate information
81 x_duplicate_association_status := JTF_VARCHAR2_TABLE_100();
82 x_msite_prty_accss_ids := JTF_NUMBER_TABLE();
83 x_is_any_duplicate_status := FND_API.G_RET_STS_SUCCESS;
84
85 FOR i IN 1..p_party_ids.COUNT LOOP
86
87 x_duplicate_association_status.EXTEND();
88 x_msite_prty_accss_ids.EXTEND();
89
90 OPEN c1(p_msite_id, p_party_ids(i));
91 FETCH c1 INTO l_tmp_id;
92 IF(c1%FOUND) THEN
93 CLOSE c1;
94 -- duplicate exists
95 x_duplicate_association_status(i) := FND_API.G_RET_STS_ERROR;
96 x_is_any_duplicate_status := FND_API.G_RET_STS_ERROR;
97 x_msite_prty_accss_ids(i) := l_tmp_id;
98 ELSE
99 CLOSE c1;
100 -- no duplicate exists, create new entry
101 x_duplicate_association_status(i) := FND_API.G_RET_STS_SUCCESS;
102
103 Jtf_Msite_Prty_Accss_Pvt.Create_Msite_Prty_Accss
104 (
105 p_api_version => p_api_version,
106 p_init_msg_list => FND_API.G_FALSE,
107 p_commit => FND_API.G_FALSE,
108 p_validation_level => p_validation_level,
109 p_msite_id => p_msite_id,
110 p_party_id => p_party_ids(i),
111 p_start_date_active => p_start_date_actives(i),
112 p_end_date_active => p_end_date_actives(i),
113 x_msite_prty_accss_id => x_msite_prty_accss_ids(i),
114 x_return_status => x_return_status,
115 x_msg_count => x_msg_count,
116 x_msg_data => x_msg_data
117 );
118
119 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
120 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_PRTY_FL');
121 FND_MSG_PUB.Add;
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
124 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_PRTY_FL');
125 FND_MSG_PUB.Add;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128
129 END IF;
130
131 END LOOP; -- end for i
132
133 --
134 -- End of main API body.
135
136 -- Standard check of p_commit.
137 IF (FND_API.To_Boolean(p_commit)) THEN
138 COMMIT WORK;
139 END IF;
140
141 -- Standard call to get message count and if count is 1, get message info.
142 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
143 p_data => x_msg_data,
144 p_encoded => 'F');
145
146 EXCEPTION
147
148 WHEN FND_API.G_EXC_ERROR THEN
149 ROLLBACK TO Associate_Parties_To_Msite_Pvt;
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
152 p_data => x_msg_data,
153 p_encoded => 'F');
154
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156 ROLLBACK TO Associate_Parties_To_Msite_Pvt;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
159 p_data => x_msg_data,
160 p_encoded => 'F');
161
162 WHEN OTHERS THEN
163 ROLLBACK TO Associate_Parties_To_Msite_Pvt;
164 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
165 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
166 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
167 FND_MESSAGE.Set_Token('REASON', SQLERRM);
168 FND_MSG_PUB.Add;
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170
171 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
172 THEN
173 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
174 END IF;
175
176 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
177 p_data => x_msg_data,
178 p_encoded => 'F');
179
180 END Associate_Parties_To_MSite;
181
182 --
183 -- to update and delete multiple entries.
184 --
185 PROCEDURE Update_Delete_Msite_Prty
186 (
187 p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
189 p_commit IN VARCHAR2 := FND_API.G_FALSE,
190 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
191 p_msite_prty_accss_ids IN JTF_NUMBER_TABLE,
192 p_object_version_numbers IN JTF_NUMBER_TABLE,
193 p_msite_ids IN JTF_NUMBER_TABLE,
194 p_party_ids IN JTF_NUMBER_TABLE,
195 p_start_date_actives IN JTF_DATE_TABLE,
196 p_end_date_actives IN JTF_DATE_TABLE,
197 p_delete_flags IN JTF_VARCHAR2_TABLE_100,
198 p_msite_id IN NUMBER,
199 p_party_access_code IN VARCHAR2,
200 x_return_status OUT VARCHAR2,
201 x_msg_count OUT NUMBER,
202 x_msg_data OUT VARCHAR2
203 )
204 IS
205 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delete_Msite_Prty';
206 l_api_version CONSTANT NUMBER := 1.0;
207
208 BEGIN
209
210 -- Standard Start of API savepoint
211 SAVEPOINT Update_Delete_Msite_Prty_Pvt;
212
213 -- Standard call to check for call compatibility.
214 IF NOT FND_API.Compatible_API_Call(l_api_version,
215 p_api_version,
216 l_api_name,
217 G_PKG_NAME)
218 THEN
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 END IF;
221
222 -- Initialize message list if p_init_msg_list is set to TRUE.
223 IF FND_API.to_Boolean(p_init_msg_list) THEN
224 FND_MSG_PUB.initialize;
225 END IF;
226
227 -- Initialize API return status to success
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229
230 -- API body
231 -- CALL FLOW :
232 -- 1.
233
234 FOR i IN 1..p_msite_prty_accss_ids.COUNT LOOP
235
236 IF (p_delete_flags(i) = 'Y') THEN
237
238 Jtf_Msite_Prty_Accss_Pvt.Delete_Msite_Prty_Accss
239 (
240 p_api_version => p_api_version,
241 p_init_msg_list => FND_API.G_FALSE,
242 p_commit => FND_API.G_FALSE,
243 p_validation_level => p_validation_level,
244 p_msite_prty_accss_id => p_msite_prty_accss_ids(i),
245 p_msite_id => FND_API.G_MISS_NUM,
246 p_party_id => FND_API.G_MISS_NUM,
247 x_return_status => x_return_status,
248 x_msg_count => x_msg_count,
249 x_msg_data => x_msg_data
250 );
251
252 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
253 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_PRTY_FL');
254 FND_MSG_PUB.Add;
255 RAISE FND_API.G_EXC_ERROR;
256 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
257 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_PRTY_FL');
258 FND_MSG_PUB.Add;
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261
262 ELSE
263
264 Jtf_Msite_Prty_Accss_Pvt.Update_Msite_Prty_Accss
265 (
266 p_api_version => p_api_version,
267 p_init_msg_list => FND_API.G_FALSE,
268 p_commit => FND_API.G_FALSE,
269 p_validation_level => p_validation_level,
270 p_msite_prty_accss_id => p_msite_prty_accss_ids(i),
271 p_object_version_number => p_object_version_numbers(i),
272 p_msite_id => FND_API.G_MISS_NUM,
273 p_party_id => FND_API.G_MISS_NUM,
274 p_start_date_active => p_start_date_actives(i),
275 p_end_date_active => p_end_date_actives(i),
276 x_return_status => x_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data
279 );
280
281 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
282 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_PRTY_FL');
283 FND_MSG_PUB.Add;
284 RAISE FND_API.G_EXC_ERROR;
285 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
286 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_PRTY_FL');
287 FND_MSG_PUB.Add;
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290
291 END IF;
292
293 END LOOP; -- end for i
294
295 --
296 -- Update jtf_msites_b's party_access_code flag
297 --
298 -- Check for validity of party access code
299 IF ((p_party_access_code IS NULL) OR
300 (p_party_access_code = FND_API.G_MISS_CHAR))
301 THEN
302 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_PRTY_ACSS_CODE');
303 FND_MSG_PUB.Add;
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306
307 BEGIN
308 UPDATE jtf_msites_b
309 SET party_access_code = p_party_access_code
310 WHERE msite_id = p_msite_id;
311 EXCEPTION
312 WHEN OTHERS THEN
313 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
314 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
315 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
316 FND_MESSAGE.Set_Token('REASON', SQLERRM);
317 FND_MSG_PUB.Add;
318
319 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_PRTY_ACCSS_CODE_FAIL');
320 FND_MESSAGE.Set_Token('PARTY_ACCESS_CODE', p_party_access_code);
321 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
322 FND_MSG_PUB.Add;
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 END;
325
326 -- End of main API body.
327
328 -- Standard check of p_commit.
332
329 IF (FND_API.To_Boolean(p_commit)) THEN
330 COMMIT WORK;
331 END IF;
333 -- Standard call to get message count and if count is 1, get message info.
334 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
335 p_data => x_msg_data,
336 p_encoded => 'F');
337
338 EXCEPTION
339
340 WHEN FND_API.G_EXC_ERROR THEN
341 ROLLBACK TO Update_Delete_Msite_Prty_Pvt;
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
344 p_data => x_msg_data,
345 p_encoded => 'F');
346
347 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
348 ROLLBACK TO Update_Delete_Msite_Prty_Pvt;
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
351 p_data => x_msg_data,
352 p_encoded => 'F');
353
354 WHEN OTHERS THEN
355 ROLLBACK TO Update_Delete_Msite_Prty_Pvt;
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357
358 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
359 THEN
360 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
361 END IF;
362
363 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
364 p_data => x_msg_data,
365 p_encoded => 'F');
366
367 END Update_Delete_Msite_Prty;
368
369 --
370 -- Return data (association + minisite data + party data) belonging to
371 -- the p_msite_id
372 --
373 PROCEDURE Load_MsiteParties_For_Msite
374 (
375 p_api_version IN NUMBER,
376 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
377 p_commit IN VARCHAR2 := FND_API.G_FALSE,
378 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
379 p_msite_id IN NUMBER,
380 x_party_access_code_csr OUT PARTY_ACCESS_CODE_CSR,
381 x_msite_csr OUT MSITE_CSR,
382 x_msite_prty_accss_csr OUT MSITE_PRTY_ACCSS_CSR,
383 x_cust_account_csr OUT CUST_ACCOUNT_CSR,
384 x_return_status OUT VARCHAR2,
385 x_msg_count OUT NUMBER,
386 x_msg_data OUT VARCHAR2
387 )
388 IS
389 l_api_name CONSTANT VARCHAR2(30) :=
390 'Load_MsiteParties_For_Msite';
391 l_api_version CONSTANT NUMBER := 1.0;
392 BEGIN
393
394 -- Initialize message list if p_init_msg_list is set to TRUE.
395 IF FND_API.to_Boolean(p_init_msg_list) THEN
396 FND_MSG_PUB.initialize;
397 END IF;
398
399 -- Initialize API return status to success
400 x_return_status := FND_API.G_RET_STS_SUCCESS;
401
402 -- Get the party access code data
403 OPEN x_party_access_code_csr FOR SELECT lookup_code, meaning
404 FROM fnd_lookups
405 WHERE lookup_type = 'JTF_PARTY_ACCESS_CODE'
406 ORDER BY lookup_code;
407
408 -- Get the mini-site data
409 OPEN x_msite_csr FOR SELECT msite_id, msite_name, party_access_code
410 FROM jtf_msites_vl
411 WHERE msite_id = p_msite_id;
412
413 -- Get the msite-party access data and party data
414 OPEN x_msite_prty_accss_csr FOR SELECT MP.msite_prty_accss_id,
415 MP.object_version_number, MP.msite_id, MP.party_id,
416 P.party_name, P.party_type, L.meaning, MP.start_date_active,
417 MP.end_date_active
418 FROM jtf_msite_prty_accss MP, hz_parties P, ar_lookups L
419 WHERE MP.msite_id = p_msite_id
420 AND MP.party_id = P.party_id
421 AND P.party_type = 'ORGANIZATION'
422 AND L.lookup_type = 'PARTY_TYPE'
423 AND P.party_type = L.lookup_code;
424
425 -- Get the party account data
426 OPEN x_cust_account_csr FOR SELECT party_id, account_number
427 FROM hz_cust_accounts_all
428 WHERE party_id IN
429 (SELECT party_id FROM jtf_msite_prty_accss
430 WHERE msite_id = p_msite_id)
431 ORDER BY party_id, account_number;
432
433 EXCEPTION
434
435 WHEN FND_API.G_EXC_ERROR THEN
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
438 p_data => x_msg_data,
439 p_encoded => 'F');
440
441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
444 p_data => x_msg_data,
445 p_encoded => 'F');
446
447 WHEN OTHERS THEN
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449
450 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
451 THEN
452 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
453 END IF;
454
455 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
456 p_data => x_msg_data,
457 p_encoded => 'F');
458
459 END Load_MsiteParties_For_Msite;
460
461 --
462 -- Get the cursor which returns the (party_id)
463 -- based on the query criteria and the query value
464 --
465 -- Query criteria (p_query_criteria) can have the following values:
466 -- 1. PARTY_NAME (uses p_criteria_value_str)
467 -- 2. ACCOUNT_NUMBER (uses p_criteria_value_str)
468 --
469 -- p_criteria_value_str will be passed as input if the criteria value is string
470 -- Note: p_criteria_value_str might have "'" in it, so we are calling to
471 -- replace any "'" with "''" so that the SQL query is constructed ok
472 --
473 PROCEDURE Get_Party_Id_List
474 (
475 p_api_version IN NUMBER,
476 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
477 p_commit IN VARCHAR2 := FND_API.G_FALSE,
478 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
479 p_query_criteria IN VARCHAR2,
480 p_criteria_value_str IN VARCHAR2,
481 x_party_csr OUT PARTY_CSR,
482 x_return_status OUT VARCHAR2,
483 x_msg_count OUT NUMBER,
484 x_msg_data OUT VARCHAR2
485 )
486 IS
487 l_api_name CONSTANT VARCHAR2(30) :=
488 'Get_Party_Id_List';
489 l_api_version CONSTANT NUMBER := 1.0;
490
491 l_db_sql VARCHAR2(2000);
492 l_criteria_value_str VARCHAR2(256);
493
494 BEGIN
495
496 -- Standard call to check for call compatibility.
497 IF NOT FND_API.Compatible_API_Call(l_api_version,
498 p_api_version,
499 l_api_name,
500 G_PKG_NAME)
501 THEN
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END IF;
504
505 -- Initialize message list if p_init_msg_list is set to TRUE.
509
506 IF FND_API.to_Boolean(p_init_msg_list) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
510 -- Initialize API return status to success
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 --
514 -- Assign criteria value to local variable (so that it can be modified)
515 --
516 l_criteria_value_str := p_criteria_value_str;
517
518 --
519 -- Handle null value of criteria value
520 --
521 IF (l_criteria_value_str IS NULL) THEN
522 l_criteria_value_str := '%';
523 END IF;
524
525 --
526 -- Replace any occurence of "'" with "''", so that the SQL query
527 -- constructed is OK
528 --
529 l_criteria_value_str := replace(l_criteria_value_str, '''', '''''');
530
531 --
532 -- Construct the database sql query
533 --
534
535 --
536 -- Based on the query criteria
537 --
538 IF (p_query_criteria IS NULL) THEN
539
540 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_QUERY_CRIT_NULL');
541 FND_MSG_PUB.Add;
542 RAISE FND_API.G_EXC_ERROR;
543
544 ELSIF (p_query_criteria = 'PARTY_NAME') THEN
545
546 OPEN x_party_csr FOR SELECT party_id FROM hz_parties
547 WHERE party_type = 'ORGANIZATION'
548 AND UPPER(party_name) LIKE UPPER(l_criteria_value_str);
549
550 ELSIF (p_query_criteria = 'ACCOUNT_NUMBER') THEN
551
552 OPEN x_party_csr FOR SELECT A.party_id FROM hz_cust_accounts_all A
553 WHERE UPPER(A.account_number) LIKE UPPER(l_criteria_value_str)
554 AND EXISTS
555 (SELECT 1 FROM hz_parties P
556 WHERE P.party_id = A.party_id
557 AND P.party_type = 'ORGANIZATION');
558
559 ELSE
560 -- none of the query criteria specified
561 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_QUERY_CRIT');
562 FND_MESSAGE.Set_Token('QUERY_CRITERIA', p_query_criteria);
563 FND_MSG_PUB.Add;
564 RAISE FND_API.G_EXC_ERROR;
565 END IF;
566
567 --
568 -- End of main API body.
569
570 -- Standard check of p_commit.
571 IF (FND_API.To_Boolean(p_commit)) THEN
572 COMMIT WORK;
573 END IF;
574
575 -- Standard call to get message count and if count is 1, get message info.
576 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
577 p_data => x_msg_data,
578 p_encoded => 'F');
579
580 EXCEPTION
581
582 WHEN FND_API.G_EXC_ERROR THEN
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
585 p_data => x_msg_data,
586 p_encoded => 'F');
587
588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
591 p_data => x_msg_data,
592 p_encoded => 'F');
593
594 WHEN OTHERS THEN
595 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
596 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
597 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
598 FND_MESSAGE.Set_Token('REASON', SQLERRM);
599 FND_MSG_PUB.Add;
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601
602 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
603 THEN
604 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
605 END IF;
606
607 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
608 p_data => x_msg_data,
609 p_encoded => 'F');
610
611 END Get_Party_Id_List;
612
613 --
614 -- Get the cursor which returns the x_party_csr with info for lookup
615 -- page for parties in (p_parties_ids)
616 --
617 PROCEDURE Get_Party_Info_For_Lookup
618 (
619 p_api_version IN NUMBER,
620 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
621 p_commit IN VARCHAR2 := FND_API.G_FALSE,
622 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
623 p_party_ids IN JTF_NUMBER_TABLE,
624 x_party_csr OUT PARTY_CSR,
625 x_cust_account_csr OUT CUST_ACCOUNT_CSR,
626 x_return_status OUT VARCHAR2,
627 x_msg_count OUT NUMBER,
631 l_api_name CONSTANT VARCHAR2(30) :=
628 x_msg_data OUT VARCHAR2
629 )
630 IS
632 'Get_Party_Info_For_Lookup';
633 l_api_version CONSTANT NUMBER := 1.0;
634
635 l_db_sql VARCHAR2(2000);
636 l_tmp_str VARCHAR2(2000);
637
638 BEGIN
639
640 -- Standard call to check for call compatibility.
641 IF NOT FND_API.Compatible_API_Call(l_api_version,
642 p_api_version,
643 l_api_name,
644 G_PKG_NAME)
645 THEN
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648
649 -- Initialize message list if p_init_msg_list is set to TRUE.
650 IF FND_API.to_Boolean(p_init_msg_list) THEN
651 FND_MSG_PUB.initialize;
652 END IF;
653
654 -- Initialize API return status to success
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657 --
658 -- If there are no parties in the input, then return error
659 --
660 IF (p_party_ids.COUNT <= 0) THEN
661 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_NO_PRTYS_SPECIFIED');
662 FND_MSG_PUB.Add;
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666 --
667 -- Prepare the part of the sql query which does selection based on the input
668 --
669 l_tmp_str := ' party_id IN (';
670
671 FOR i IN 1..p_party_ids.COUNT LOOP
672
673 IF (i <> 1) THEN
674 l_tmp_str := l_tmp_str || ',';
675 END IF;
676
677 l_tmp_str := l_tmp_str || p_party_ids(i);
678
679 END LOOP; -- end loop i
680
681 -- end construction of part of sql query
682 l_tmp_str := l_tmp_str || ')';
683
684 --
685 -- Construct the database sql query
686 --
687 l_db_sql :=
688 'SELECT P.party_id, P.party_name, P.party_type, L.meaning FROM hz_parties P, ar_lookups L WHERE ' ||
689 ' P.party_type = L.lookup_code AND L.lookup_type = ''PARTY_TYPE'' AND ' ||
690 l_tmp_str;
691
692 --
693 -- Get the party data
694 --
695 OPEN x_party_csr FOR l_db_sql;
696
697 --
698 -- Get the account data
699 --
700 OPEN x_cust_account_csr FOR 'SELECT party_id, account_number FROM hz_cust_accounts_all WHERE ' || l_tmp_str;
701
702 --
703 -- End of main API body.
704
705 -- Standard check of p_commit.
706 IF (FND_API.To_Boolean(p_commit)) THEN
707 COMMIT WORK;
708 END IF;
709
710 -- Standard call to get message count and if count is 1, get message info.
711 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
712 p_data => x_msg_data,
713 p_encoded => 'F');
714
715 EXCEPTION
716
717 WHEN FND_API.G_EXC_ERROR THEN
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
720 p_data => x_msg_data,
721 p_encoded => 'F');
722
723 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
726 p_data => x_msg_data,
727 p_encoded => 'F');
728
729 WHEN OTHERS THEN
730 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
731 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
732 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
733 FND_MESSAGE.Set_Token('REASON', SQLERRM);
734 FND_MSG_PUB.Add;
735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
736
737 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
738 THEN
739 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
740 END IF;
741
742 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
743 p_data => x_msg_data,
744 p_encoded => 'F');
745
746 END Get_Party_Info_For_Lookup;
747
748 END Jtf_Msite_Prty_Accss_Mgr_Pvt;
[Home] [Help]