[Home] [Help]
PACKAGE BODY: APPS.FUN_SECURITY
Source
1 PACKAGE BODY FUN_SECURITY AS
2 /* $Header: FUNSECAB.pls 120.13 2006/09/15 10:04:10 bsilveir noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FUN_SECURITY';
5 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7
8
9 /*-----------------------------------------------------
10 * PRIVATE PROCEDURE get_instance_set_ids
11 * ----------------------------------------------------
12 * Gets the instance set ids of the instances sets
13 * names specified in the cursor.
14 * ---------------------------------------------------*/
15 PROCEDURE get_instance_set_ids(
16 p_trx_batches_id OUT NOCOPY NUMBER,
17 p_trx_headers_id OUT NOCOPY NUMBER,
18 p_dist_lines_id OUT NOCOPY NUMBER)
19 IS
20 CURSOR c_get_instance_set_id
21 IS
22 SELECT instance_set_id, instance_set_name
23 FROM fnd_object_instance_sets
24 WHERE instance_set_name IN ('FUN_TRX_BATCHES_SET','FUN_TRX_HEADERS_SET','FUN_DIST_LINES_SET');
25 BEGIN
26
27 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
28 THEN
29 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
30 'fun.plsql.fun_security.get_instance_set_ids','begin');
31 END IF;
32
33 FOR c_record IN c_get_instance_set_id
34 LOOP
35 IF (c_record.instance_set_name = 'FUN_TRX_BATCHES_SET') THEN
36 p_trx_batches_id := c_record.instance_set_id;
37 ELSIF (c_record.instance_set_name = 'FUN_TRX_HEADERS_SET') THEN
38 p_trx_headers_id := c_record.instance_set_id;
39 ELSIF (c_record.instance_set_name = 'FUN_DIST_LINES_SET') THEN
40 p_dist_lines_id := c_record.instance_set_id;
41 END IF;
42 END LOOP;
43
44 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
45 THEN
46 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
47 'fun.plsql.fun_security.get_instance_set_ids','end');
48 END IF;
49
50 END get_instance_set_ids;
51
52
53
54 /*-----------------------------------------------------
55 * PROCEDURE create_assign
56 * ----------------------------------------------------
57 * Create grants for the specified person to the
58 * specified organization.
59 * ---------------------------------------------------*/
60 PROCEDURE create_assign (
61 p_api_version IN NUMBER,
62 p_init_msg_list IN VARCHAR2 ,
63 p_commit IN VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2,
67 p_org_id IN NUMBER DEFAULT NULL,
68 p_person_id IN NUMBER,
69 p_create_all IN VARCHAR2,
70 p_create_contact IN VARCHAR2,
71 p_enabled_flag IN VARCHAR2
72 )
73 IS
74 l_fnd_grant_guid fnd_grants.grant_guid%TYPE;
75 l_fnd_errorcode NUMBER;
76 l_menu_name VARCHAR2(30);
77 l_grant_enabled_flag VARCHAR2(5);
78 l_instance_set_batches fnd_object_instance_sets.instance_set_id%TYPE;
79 l_instance_set_headers fnd_object_instance_sets.instance_set_id%TYPE;
80 l_instance_set_dist fnd_object_instance_sets.instance_set_id%TYPE;
81 l_api_version CONSTANT NUMBER := 1.0;
82 l_api_name CONSTANT VARCHAR2(30) := 'create_assign';
83 l_end_date DATE := NULL;
84 l_module VARCHAR2(100);
85
86 CURSOR c_get_ice_orgs
87 IS
88 select * from (SELECT hzp.party_id AS partyid,
89 DECODE(xfi.party_id,NULL,'REMOTE','LOCAL') AS local
90 FROM HZ_PARTIES HZP,
91 XLE_ENTITY_PROFILES XFI,
92 HZ_PARTY_USG_ASSIGNMENTS HUA
93 WHERE HZP.PARTY_TYPE='ORGANIZATION'
94 AND FUN_TCA_PKG.GET_LE_ID(HZP.PARTY_ID)=XFI.PARTY_ID
95 AND HUA.PARTY_ID=HZP.PARTY_ID
96 AND HUA.PARTY_USAGE_CODE ='INTERCOMPANY_ORG'
97 AND XFI.TRANSACTING_ENTITY_FLAG = 'Y') QRSLT
98 WHERE (PARTYID not in ( select object_id
99 from hz_relationships where subject_id = -999
100 and subject_table_name like 'HZ_PARTIES'
101 and relationship_code like 'CONTACT_OF'
102 and Directional_flag = 'F'
103 and start_date < sysdate and end_date > sysdate ));
104
105 BEGIN
106
107 l_module := 'fun.plsql.fun_security.create_assign';
108
109 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
110 THEN
111 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
112 l_module,'begin');
113 END IF;
114
115 --Standard call to check for call compatibility.
116 IF NOT FND_API.Compatible_API_Call(
117 l_api_version,
118 p_api_version,
119 l_api_name,
120 G_PKG_NAME)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 SAVEPOINT create_fun_grant;
126
127
128 -- initialize message list if p_init_msg_list is set to TRUE.
129 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133
134 -- initialize API return status to success.
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137
138 IF nvl(p_enabled_flag,'Y')='N' THEN
139 l_end_date := SYSDATE-1;
140 END IF;
141
142
143 l_menu_name := 'FUN_DATA_ACCESS' ;
144
145 get_instance_set_ids( l_instance_set_batches,
146 l_instance_set_headers,
147 l_instance_set_dist);
148
149
150 IF p_create_all = 'Y' THEN
151 FOR v_org_record IN c_get_ice_orgs
152 LOOP
153 IF v_org_record.local = 'LOCAL' THEN
154 -- Check if grant previously exists.
155 IF (is_access_allow(
156 p_person_id,
157 v_org_record.partyid) = 'Y') THEN
158 IF(is_access_valid(
159 p_person_id,
160 v_org_record.partyid) = 'N') THEN
161 -- Grant has been disabled
162 -- Need to update grant
163 update_assign(
164 p_api_version,
165 nvl(p_init_msg_list, FND_API.G_FALSE),
166 p_commit,
167 x_return_status,
168 x_msg_count,
169 x_msg_data,
170 v_org_record.partyid,
171 p_person_id,
172 'Y');
173 -- Section of code which updates the TCA relationship
174 -- TCA relationship needs to be updated only for the Assign All Local Organizations
175 -- Relationship updation in update_assign done in the JAVA code.
176
177 DECLARE
178 l_relationship_id NUMBER(15);
179 l_object_version_number hz_relationships.object_version_number%TYPE;
180 l_party_object_version_number hz_relationships.object_version_number%TYPE := NULL;
181 l_cont_object_version_number hz_relationships.object_version_number%TYPE;
182 l_rel_object_version_number hz_relationships.object_version_number%TYPE;
183 l_tca_relationship_record HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
184 l_tca_contact_role_record HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_ROLE_REC_TYPE;
185 l_tca_contact_record HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
186 l_org_contact_role_id NUMBER;
187 l_org_contact_id NUMBER;
188 l_primary_flag VARCHAR2(1);
189 l_status VARCHAR2(1);
190
191 CURSOR get_relationship_id_c(p_cursor_personid NUMBER,
192 p_cursor_orgid NUMBER)
193 IS
194 SELECT relationship_id
195 FROM hz_relationships hzr
196 WHERE hzr.subject_id=p_cursor_personid
197 AND hzr.object_id=p_cursor_orgid
198 AND hzr.relationship_code='CONTACT_OF'
199 AND hzr.relationship_type='CONTACT'
200 AND hzr.directional_flag='F'
201 AND hzr.subject_type='PERSON' ;
202
203 CURSOR get_object_version_num_c (p_rel_id NUMBER)
204 IS
205 SELECT object_version_number
206 FROM hz_relationships
207 WHERE relationship_id=p_rel_id ;
208
209 BEGIN
210
211 OPEN get_relationship_id_c(p_person_id,v_org_record.partyid);
212 FETCH get_relationship_id_c INTO l_relationship_id;
213 CLOSE get_relationship_id_c;
214
215 l_tca_relationship_record.relationship_id := l_relationship_id;
216 l_tca_relationship_record.relationship_type:= 'CONTACT';
217 l_tca_relationship_record.status := 'A';
218 l_tca_relationship_record.comments := 'Updated from Oracle Intercompany';
219
220 OPEN get_object_version_num_c(l_relationship_id);
221 FETCH get_object_version_num_c INTO l_object_version_number;
222 CLOSE get_object_version_num_c;
223
224 --bug 4228791, added call to update org contact and contact role
225
226 l_tca_contact_record.party_rel_rec.relationship_id := l_relationship_id;
227 l_tca_contact_record.party_rel_rec.relationship_type := 'CONTACT';
228 l_tca_contact_record.party_rel_rec.status := 'A';
229
230 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
231 THEN
232 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
233 l_module,'calling HZ_PARTY_CONTACT_V2PUB.update_org_contact');
234 END IF;
235
236 HZ_PARTY_CONTACT_V2PUB.update_org_contact(
237 p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
238 p_org_contact_rec => l_tca_contact_record,
239 p_cont_object_version_number => l_cont_object_version_number,
240 p_rel_object_version_number => l_rel_object_version_number,
241 p_party_object_version_number => l_party_object_version_number,
242 x_return_status => x_return_status,
243 x_msg_count => x_msg_count,
244 x_msg_data => x_msg_data
245 );
246 --dbms_output.put_line('1 : '||x_return_status||x_msg_count||x_msg_data);
247
248 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
249 THEN
250 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
251 l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.update_org_contact'
252 || ' Status ' || x_return_status);
253 END IF;
254
255 IF x_return_status <> 'S'
256 THEN
257 RAISE FND_API.G_EXC_ERROR;
258 END IF;
259
260 SELECT hcr.org_contact_role_id, hcr.org_contact_id, hcr.primary_flag, hcr.status
261 INTO l_org_contact_role_id, l_org_contact_id, l_primary_flag, l_status
262 FROM hz_relationships hzr, hz_org_contacts hc, hz_org_contact_roles hcr
263 WHERE hzr.subject_id=p_person_id
264 AND hzr.object_id=v_org_record.partyid
265 AND hzr.relationship_code='CONTACT_OF'
266 AND hzr.relationship_type='CONTACT'
267 AND hzr.directional_flag='F'
268 AND hzr.subject_type='PERSON'
269 AND hzr.relationship_id = hc.party_relationship_id
270 AND hc.org_contact_id = hcr.org_contact_id;
271
272 l_tca_contact_role_record.org_contact_role_id := l_org_contact_role_id;
273 l_tca_contact_role_record.role_type := 'INTERCOMPANY_CONTACT_FOR';
274 l_tca_contact_role_record.primary_flag := l_primary_flag;
275 l_tca_contact_role_record.org_contact_id := l_org_contact_id;
276 l_tca_contact_role_record.status := l_status;
277 l_tca_contact_role_record.created_by_module := 'FUN_AGIS';
278
279 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
280 THEN
281 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
282 l_module,'calling HZ_PARTY_CONTACT_V2PUB.update_org_contact_role');
283 END IF;
284
285 HZ_PARTY_CONTACT_V2PUB.update_org_contact_role(
286 p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
287 p_org_contact_role_rec => l_tca_contact_role_record,
288 p_object_version_number => l_object_version_number,
289 x_return_status => x_return_status,
290 x_msg_count => x_msg_count,
291 x_msg_data => x_msg_data
292 );
293 --dbms_output.put_line('2 : '||x_return_status||x_msg_count||x_msg_data);
294
295 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
296 THEN
297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
298 l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.update_org_contact_role'
299 || ' Status ' || x_return_status);
300 END IF;
301
302 IF x_return_status <> 'S'
303 THEN
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306
307 END;
308 END IF; -- is_access_valid = 'N'
309 ELSE
310
311 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
312 THEN
313 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
314 l_module,'calling fnd_grants_pkg.grant_function for FUN_TRX_BATCHES');
315 END IF;
316
317 fnd_grants_pkg.grant_function (
318 p_api_version => 1,
319 p_menu_name => l_menu_name,
320 p_object_name => 'FUN_TRX_BATCHES',
321 p_instance_type => 'SET',
322 p_instance_set_id => l_instance_set_batches,
323 p_grantee_type => 'USER',
324 p_grantee_key => 'HZ_PARTY:'||p_person_id,
325 p_start_date => SYSDATE,
326 p_end_date => l_end_date,
327 x_grant_guid => l_fnd_grant_guid,
328 x_success => x_return_status,
329 x_errorcode => l_fnd_errorcode,
330 p_parameter1 => v_org_record.partyid
331 );
332
333 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
334 THEN
335 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
336 l_module, 'completed call to fnd_grants_pkg.grant_function for FUN_TRX_BATCHES'
337 || ' Status ' || x_return_status);
338 END IF;
339
340 IF x_return_status <> 'T'
341 THEN
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344
345 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
346 THEN
347 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
348 l_module,'calling fnd_grants_pkg.grant_function for FUN_TRX_HEADERS');
349 END IF;
350 fnd_grants_pkg.grant_function (
351 p_api_version => 1,
352 p_menu_name => l_menu_name,
353 p_object_name => 'FUN_TRX_HEADERS',
354 p_instance_type => 'SET',
355 p_instance_set_id => l_instance_set_headers,
356 p_grantee_type => 'USER',
357 p_grantee_key => 'HZ_PARTY:'||p_person_id,
358 p_start_date => SYSDATE,
359 p_end_date => l_end_date,
360 x_grant_guid => l_fnd_grant_guid,
361 x_success => x_return_status,
362 x_errorcode => l_fnd_errorcode,
363 p_parameter1 => v_org_record.partyid
364 );
365
366 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
367 THEN
368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
369 l_module, 'completed call to fnd_grants_pkg.grant_function for FUN_TRX_HEADERS'
370 || ' Status ' || x_return_status);
371 END IF;
372
373 IF x_return_status <> 'T'
374 THEN
375 RAISE FND_API.G_EXC_ERROR;
376 END IF;
377
378 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
379 THEN
380 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
381 l_module,'calling fnd_grants_pkg.grant_function for FUN_DIST_LINES');
382 END IF;
383 fnd_grants_pkg.grant_function (
384 p_api_version => 1,
385 p_menu_name => l_menu_name,
386 p_object_name => 'FUN_DIST_LINES',
387 p_instance_type => 'SET',
388 p_instance_set_id => l_instance_set_dist,
389 p_grantee_type => 'USER',
390 p_grantee_key => 'HZ_PARTY:'||p_person_id,
391 p_start_date => SYSDATE,
392 p_end_date => l_end_date,
393 x_grant_guid => l_fnd_grant_guid,
394 x_success => x_return_status,
395 x_errorcode => l_fnd_errorcode,
396 p_parameter1 => v_org_record.partyid
397 );
398
399
400 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
401 THEN
402 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
403 l_module, 'completed call to fnd_grants_pkg.grant_function for FUN_DIST_LINES'
404 || ' Status ' || x_return_status);
405 END IF;
406
407 IF x_return_status <> 'T'
408 THEN
409 RAISE FND_API.G_EXC_ERROR;
410 END IF;
411
412 --Creating the TCA relationship between the person and the organization
413 -- with the check for if TCA relationship reqd
414 IF (nvl(p_create_contact,'N')= 'Y') THEN
415 DECLARE
416 l_relationship_id NUMBER(15);
417 l_party_id NUMBER(15);
418 l_party_number VARCHAR2(30);
419 l_tca_relationship_record HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
420 l_tca_contact_role_record HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_ROLE_REC_TYPE;
421 l_tca_contact_record HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
422 l_org_contact_role_id NUMBER;
423 l_org_contact_id NUMBER;
424 l_party_rel_id NUMBER;
425
426
427 BEGIN
428 l_tca_relationship_record.subject_id := p_person_id;
429 l_tca_relationship_record.subject_type := 'PERSON';
430 l_tca_relationship_record.subject_table_name := 'HZ_PARTIES';
431 l_tca_relationship_record.object_id := v_org_record.partyid;
432 l_tca_relationship_record.object_type := 'ORGANIZATION';
433 l_tca_relationship_record.object_table_name := 'HZ_PARTIES';
434 l_tca_relationship_record.relationship_code := 'CONTACT_OF';
435 l_tca_relationship_record.relationship_type := 'CONTACT';
436 l_tca_relationship_record.start_date := SYSDATE;
437 l_tca_relationship_record.created_by_module := 'FUN_AGIS';
438
439 --bug 4228791, added call to create org contact and contact role
440
441 l_tca_contact_record.created_by_module := 'FUN_AGIS';
442 l_tca_contact_record.party_rel_rec.subject_id := p_person_id;
443 l_tca_contact_record.party_rel_rec.subject_type := 'PERSON';
444 l_tca_contact_record.party_rel_rec.subject_table_name := 'HZ_PARTIES';
445 l_tca_contact_record.party_rel_rec.object_id := v_org_record.partyid;
446 l_tca_contact_record.party_rel_rec.object_type := 'ORGANIZATION';
447 l_tca_contact_record.party_rel_rec.object_table_name := 'HZ_PARTIES';
448 l_tca_contact_record.party_rel_rec.relationship_code := 'CONTACT_OF';
449 l_tca_contact_record.party_rel_rec.relationship_type := 'CONTACT';
450 l_tca_contact_record.party_rel_rec.start_date := SYSDATE;
451 l_tca_contact_record.party_rel_rec.created_by_module := 'FUN_AGIS';
452 l_tca_contact_record.party_rel_rec.status := 'A';
453
454 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
455 THEN
456 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
457 l_module,'calling HZ_PARTY_CONTACT_V2PUB.create_org_contact');
458 END IF;
459
460 HZ_PARTY_CONTACT_V2PUB.create_org_contact(
461 p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
462 p_org_contact_rec => l_tca_contact_record,
463 x_org_contact_id => l_org_contact_id,
464 x_party_rel_id => l_party_rel_id,
465 x_party_id => l_party_id,
466 x_party_number => l_party_number,
467 x_return_status => x_return_status,
468 x_msg_count => x_msg_count,
469 x_msg_data => x_msg_data
470 );
471
472 --dbms_output.put_line('3 : '||x_return_status||x_msg_count||x_msg_data);
473 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
474 THEN
475 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
476 l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.create_org_contact'
477 || ' Status ' || x_return_status);
478 END IF;
479
480 IF x_return_status <> 'S'
481 THEN
482 RAISE FND_API.G_EXC_ERROR;
483 END IF;
484
485 l_tca_contact_role_record.role_type := 'INTERCOMPANY_CONTACT_FOR';
486 l_tca_contact_role_record.status := 'A';
487 l_tca_contact_role_record.org_contact_id := l_org_contact_id;
488 l_tca_contact_role_record.created_by_module := 'FUN_AGIS';
489
490 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
491 THEN
492 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
493 l_module,'calling HZ_PARTY_CONTACT_V2PUB.create_org_contact_role');
494 END IF;
495
496 HZ_PARTY_CONTACT_V2PUB.create_org_contact_role(
497 p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
498 p_org_contact_role_rec => l_tca_contact_role_record,
499 x_org_contact_role_id => l_org_contact_role_id,
500 x_return_status => x_return_status,
501 x_msg_count => x_msg_count,
502 x_msg_data => x_msg_data
503 );
504
505 --dbms_output.put_line('4 : '||x_return_status||x_msg_count||x_msg_data);
506 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
507 THEN
508 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
509 l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.create_org_contact_role'
510 || ' Status ' || x_return_status);
511 END IF;
512
513 IF x_return_status <> 'S'
514 THEN
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518
519 END;
520 END IF; -- p_create_contact ='y'
521 END IF; -- is_access_allow= 'y'
522 END IF; -- is local
523 END LOOP;
524 ELSIF p_create_all = 'N' THEN
525 IF p_org_id IS NULL THEN
526 RAISE FND_API.G_EXC_ERROR;
527 ELSE
528 IF (is_access_allow(
529 p_person_id,
530 p_org_id) = 'Y') THEN
531 IF(is_access_valid(
532 p_person_id,
533 p_org_id) = 'N') THEN
534 -- Grant has been disabled
535 -- Need to update grant
536 update_assign(
537 p_api_version,
538 nvl(p_init_msg_list, FND_API.G_FALSE),
539 p_commit,
540 x_return_status,
541 x_msg_count,
542 x_msg_data,
543 p_org_id,
544 p_person_id,
545 'Y');
546 END IF;
547 ELSE
548 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
549 THEN
550 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
551 l_module,'calling fnd_grants_pkg.grant_function for FUN_TRX_BATCHES');
552 END IF;
553 --Grant does not exist
554 fnd_grants_pkg.grant_function (
555 p_api_version => 1,
556 p_menu_name => l_menu_name,
557 p_object_name => 'FUN_TRX_BATCHES',
558 p_instance_type => 'SET',
559 p_instance_set_id => l_instance_set_batches,
560 p_grantee_type => 'USER',
561 p_grantee_key => 'HZ_PARTY:'||p_person_id,
562 p_start_date => SYSDATE,
563 p_end_date => l_end_date,
564 x_grant_guid => l_fnd_grant_guid,
565 x_success => x_return_status,
566 x_errorcode => l_fnd_errorcode,
567 p_parameter1 => p_org_id
568 );
569
570 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
571 THEN
572 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
573 l_module, 'completed call to fnd_grants_pkg.grant_function for FUN_TRX_BATCHES'
574 || ' Status ' || x_return_status);
575 END IF;
576
577 IF x_return_status <> 'T'
578 THEN
579 RAISE FND_API.G_EXC_ERROR;
580 END IF;
581
582 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
583 THEN
584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
585 l_module,'calling fnd_grants_pkg.grant_function for FUN_TRX_HEADERS');
586 END IF;
587
588 fnd_grants_pkg.grant_function (
589 p_api_version => 1,
590 p_menu_name => l_menu_name,
591 p_object_name => 'FUN_TRX_HEADERS',
592 p_instance_type => 'SET',
593 p_instance_set_id => l_instance_set_headers,
594 p_grantee_type => 'USER',
595 p_grantee_key => 'HZ_PARTY:'||p_person_id,
596 p_start_date => SYSDATE,
597 p_end_date => l_end_date,
598 x_grant_guid => l_fnd_grant_guid,
599 x_success => x_return_status,
600 x_errorcode => l_fnd_errorcode,
601 p_parameter1 => p_org_id
602 );
603
604
605 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
606 THEN
607 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
608 l_module, 'completed call to fnd_grants_pkg.grant_function for FUN_TRX_HEADERS'
609 || ' Status ' || x_return_status);
610 END IF;
611
612 IF x_return_status <> 'T'
613 THEN
614 RAISE FND_API.G_EXC_ERROR;
615 END IF;
616
617 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
618 THEN
619 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
620 l_module,'calling fnd_grants_pkg.grant_function for FUN_DIST_LINES');
621 END IF;
622
623 fnd_grants_pkg.grant_function (
624 p_api_version => 1,
625 p_menu_name => l_menu_name,
626 p_object_name => 'FUN_DIST_LINES',
627 p_instance_type => 'SET',
628 p_instance_set_id => l_instance_set_dist,
629 p_grantee_type => 'USER',
630 p_grantee_key => 'HZ_PARTY:'||p_person_id,
631 p_start_date => SYSDATE,
632 p_end_date => l_end_date,
633 x_grant_guid => l_fnd_grant_guid,
634 x_success => x_return_status,
635 x_errorcode => l_fnd_errorcode,
636 p_parameter1 => p_org_id
637 );
638
639
640 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
641 THEN
642 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
643 l_module, 'completed call to fnd_grants_pkg.grant_function for FUN_DIST_LINES'
644 || ' Status ' || x_return_status);
645 END IF;
646
647 IF x_return_status <> 'T'
648 THEN
649 RAISE FND_API.G_EXC_ERROR;
650 END IF;
651
652 --Creating the TCA relationship between the person and the organization
653 -- with the check for if TCA relationship reqd
654
655
656 IF (nvl(p_create_contact,'N')= 'Y') THEN
657 DECLARE
658 l_relationship_id NUMBER(15);
659 l_party_id NUMBER(15);
660 l_party_number VARCHAR2(30);
661 l_tca_relationship_record HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
662 l_tca_contact_role_record HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_ROLE_REC_TYPE;
663 l_tca_contact_record HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
664 l_org_contact_role_id NUMBER;
665 l_org_contact_id NUMBER;
666 l_party_rel_id NUMBER;
667
668
669 BEGIN
670 l_tca_relationship_record.subject_id := p_person_id;
671 l_tca_relationship_record.subject_type := 'PERSON';
672 l_tca_relationship_record.subject_table_name:= 'HZ_PARTIES';
673 l_tca_relationship_record.object_id := p_org_id;
674 l_tca_relationship_record.object_type := 'ORGANIZATION';
675 l_tca_relationship_record.object_table_name := 'HZ_PARTIES';
676 l_tca_relationship_record.relationship_code := 'CONTACT_OF';
677 l_tca_relationship_record.relationship_type := 'CONTACT';
678 l_tca_relationship_record.start_date := SYSDATE;
679 l_tca_relationship_record.created_by_module := 'FUN_AGIS';
680
681
682 --bug 4228791, added call to create org contact and contact role
683
684 l_tca_contact_record.created_by_module := 'FUN_AGIS';
685 l_tca_contact_record.party_rel_rec.subject_id := p_person_id;
686 l_tca_contact_record.party_rel_rec.subject_type := 'PERSON';
687 l_tca_contact_record.party_rel_rec.subject_table_name := 'HZ_PARTIES';
688 l_tca_contact_record.party_rel_rec.object_id := p_org_id;
689 l_tca_contact_record.party_rel_rec.object_type := 'ORGANIZATION';
690 l_tca_contact_record.party_rel_rec.object_table_name := 'HZ_PARTIES';
691 l_tca_contact_record.party_rel_rec.relationship_code := 'CONTACT_OF';
692 l_tca_contact_record.party_rel_rec.relationship_type := 'CONTACT';
693 l_tca_contact_record.party_rel_rec.start_date := SYSDATE;
694 l_tca_contact_record.party_rel_rec.created_by_module := 'FUN_AGIS';
695 l_tca_contact_record.party_rel_rec.status := 'A';
696
697 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
698 THEN
699 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
700 l_module,'calling HZ_PARTY_CONTACT_V2PUB.create_org_contact');
701 END IF;
702
703 HZ_PARTY_CONTACT_V2PUB.create_org_contact(
704 p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
705 p_org_contact_rec => l_tca_contact_record,
706 x_org_contact_id => l_org_contact_id,
707 x_party_rel_id => l_party_rel_id,
708 x_party_id => l_party_id,
709 x_party_number => l_party_number,
710 x_return_status => x_return_status,
711 x_msg_count => x_msg_count,
712 x_msg_data => x_msg_data
713 );
714 --dbms_output.put_line('5 : '||x_return_status||x_msg_count||x_msg_data);
715
716 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
717 THEN
718 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
719 l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.create_org_contact'
720 || ' Status ' || x_return_status);
721 END IF;
722
723 IF x_return_status <> 'S'
724 THEN
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727
728 l_tca_contact_role_record.role_type := 'INTERCOMPANY_CONTACT_FOR';
729 l_tca_contact_role_record.status := 'A';
730 l_tca_contact_role_record.org_contact_id := l_org_contact_id;
731 l_tca_contact_role_record.created_by_module := 'FUN_AGIS';
732
733 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
734 THEN
735 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
736 l_module,'calling HZ_PARTY_CONTACT_V2PUB.create_org_contact_role');
737 END IF;
738
739
740 HZ_PARTY_CONTACT_V2PUB.create_org_contact_role(
741 p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
742 p_org_contact_role_rec => l_tca_contact_role_record,
743 x_org_contact_role_id => l_org_contact_role_id,
744 x_return_status => x_return_status,
745 x_msg_count => x_msg_count,
746 x_msg_data => x_msg_data
747 );
748 --dbms_output.put_line('6 : '||x_return_status||x_msg_count||x_msg_data);
749 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
750 THEN
751 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
752 l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.create_org_contact_role'
753 || ' Status ' || x_return_status);
754 END IF;
755
756 IF x_return_status <> 'S'
757 THEN
758 RAISE FND_API.G_EXC_ERROR;
759 END IF;
760
761
762 END;
763 END IF; -- p_create_contact='y'
764 END IF; -- is_access_allow='y'
765 END IF;-- p_org_id IS NULL
766
767 END IF; --p_create_all='n'
768
769 IF FND_API.To_Boolean( p_commit )
770 THEN
771 COMMIT WORK;
772 END IF;
773
774 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
775 THEN
776 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
777 l_module,'end');
778 END IF;
779
780 -- standard call to get message count and if count is 1, get message info.
781 FND_MSG_PUB.Count_And_Get(
782 p_encoded => FND_API.G_FALSE,
783 p_count => x_msg_count,
784 p_data => x_msg_data);
785
786 EXCEPTION
787 WHEN FND_API.G_EXC_ERROR THEN
788 ROLLBACK TO create_fun_grant;
789 x_return_status := FND_API.G_RET_STS_ERROR;
790 FND_MSG_PUB.Count_And_Get(
791 p_encoded => FND_API.G_FALSE,
792 p_count => x_msg_count,
793 p_data => x_msg_data);
794
795 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
796 THEN
797 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
798 l_module,'Execution exception raised');
799 END IF;
800
801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
802 ROLLBACK TO create_fun_grant;
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 FND_MSG_PUB.Count_And_Get(
805 p_encoded => FND_API.G_FALSE,
806 p_count => x_msg_count,
807 p_data => x_msg_data);
808
809 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
810 THEN
811 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
812 l_module,'Unexpected exception raised - ' || SQLERRM);
813 END IF;
814
815 WHEN OTHERS THEN
816 ROLLBACK TO create_fun_grant;
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818 FND_MSG_PUB.Count_And_Get(
819 p_encoded => FND_API.G_FALSE,
820 p_count => x_msg_count,
821 p_data => x_msg_data);
822
823 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
824 THEN
825 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
826 l_module,'Unexpected exception raised - ' || SQLERRM);
827 END IF;
828 END create_assign;
829
830
831
832
833
834
835 /*-----------------------------------------------------
836 * PROCEDURE update_assign
837 * ----------------------------------------------------
838 * Updates grants for the specified person to the
839 * specified organization.
840 * ---------------------------------------------------*/
841
842 PROCEDURE update_assign (
843 p_api_version IN NUMBER,
844 p_init_msg_list IN VARCHAR2 ,
845 p_commit IN VARCHAR2,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2,
849 p_org_id IN NUMBER,
850 p_person_id IN NUMBER,
851 p_status IN VARCHAR2
852 )
853 IS
854 l_fnd_grant_guid fnd_grants.grant_guid%TYPE;
855 l_fnd_errorcode NUMBER;
856 l_menu_name VARCHAR2(30);
857 l_start_date DATE;
858 l_instance_set_batches fnd_object_instance_sets.instance_set_id%TYPE;
859 l_instance_set_headers fnd_object_instance_sets.instance_set_id%TYPE;
860 l_instance_set_dist fnd_object_instance_sets.instance_set_id%TYPE;
861 l_grant_guid fnd_grants.grant_guid%TYPE;
862 l_api_version CONSTANT NUMBER := 1.0;
863 l_api_name CONSTANT VARCHAR2(30) := 'create_assign';
864 l_module VARCHAR2(100) := 'fun.plsql.fun_security.update_assign';
865
866 CURSOR c_grant_info(
867 p_person_id NUMBER,
868 p_org_id NUMBER,
869 p_instance_set_id NUMBER)
870 IS
871 SELECT grant_guid,start_date
872 FROM fnd_grants fg
873 WHERE instance_set_id = p_instance_set_id
874 AND parameter1 = p_org_id
875 AND grantee_key = 'HZ_PARTY:'||p_person_id ;
876
877 BEGIN
878
879 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
880 THEN
881 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
882 l_module,'begin');
883 END IF;
884
885 --Standard call to check for call compatibility.
886 IF NOT FND_API.Compatible_API_Call(
887 l_api_version,
888 p_api_version,
889 l_api_name,
890 G_PKG_NAME)
891 THEN
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 END IF;
894
895 SAVEPOINT update_fun_grant;
896 --initialize message list if p_init_msg_list is set to TRUE.
897
898 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
899 FND_MSG_PUB.initialize;
900 END IF;
901
902
903 -- initialize API return status to success.
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905
906 l_menu_name := 'FUN_DATA_ACCESS' ;
907
908 get_instance_set_ids(
909 l_instance_set_batches,
910 l_instance_set_headers,
911 l_instance_set_dist);
912
913
914 OPEN c_grant_info(
915 p_person_id,
916 p_org_id,
917 l_instance_set_batches);
918
919 IF c_grant_info%NOTFOUND THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922
923 FETCH c_grant_info INTO l_grant_guid,l_start_date ;
924 CLOSE c_grant_info ;
925
926 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
927 THEN
928 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
929 l_module, 'callinge fnd_grants_pkg.update_grant');
930 END IF;
931
932 IF p_status = 'Y' THEN
933 fnd_grants_pkg.update_grant (
934 p_api_version => 1,
935 p_grant_guid => l_grant_guid,
936 p_start_date => l_start_date,
937 p_end_date => NULL,
938 x_success => x_return_status
939 );
940 ELSIF p_status ='N' THEN
941 fnd_grants_pkg.update_grant (
942 p_api_version => 1,
943 p_grant_guid => l_grant_guid,
944 p_start_date => l_start_date,
945 p_end_date => SYSDATE-1,
946 x_success => x_return_status
947 );
948 END IF;
949
950 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
951 THEN
952 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
953 l_module, 'completed call to fnd_grants_pkg.update_grant '
954 || ' Status ' || x_return_status);
955 END IF;
956
957 IF x_return_status <> 'T'
958 THEN
959 RAISE FND_API.G_EXC_ERROR;
960 END IF;
961
962 OPEN c_grant_info(
963 p_person_id,
964 p_org_id,
965 l_instance_set_headers);
966 FETCH c_grant_info INTO l_grant_guid,l_start_date ;
967 CLOSE c_grant_info ;
968
969 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
970 THEN
971 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
972 l_module, 'callinge fnd_grants_pkg.update_grant for headers');
973 END IF;
974
975 IF p_status = 'Y' THEN
976 fnd_grants_pkg.update_grant (
977 p_api_version => 1,
978 p_grant_guid => l_grant_guid,
979 p_start_date => l_start_date,
980 p_end_date => NULL,
981 x_success => x_return_status
982 );
983 ELSIF p_status ='N' THEN
984 fnd_grants_pkg.update_grant (
985 p_api_version => 1,
986 p_grant_guid => l_grant_guid,
987 p_start_date => l_start_date,
988 p_end_date => SYSDATE-1,
989 x_success => x_return_status
990 );
991 END IF;
992
993 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
994 THEN
995 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
996 l_module, 'completed call to fnd_grants_pkg.update_grant for headers'
997 || ' Status ' || x_return_status);
998 END IF;
999
1000 IF x_return_status <> 'T'
1001 THEN
1002 RAISE FND_API.G_EXC_ERROR;
1003 END IF;
1004
1005 OPEN c_grant_info(
1006 p_person_id,
1007 p_org_id,
1008 l_instance_set_dist);
1009 FETCH c_grant_info INTO l_grant_guid,l_start_date ;
1010 CLOSE c_grant_info ;
1011
1012 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1013 THEN
1014 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1015 l_module, 'calling fnd_grants_pkg.update_grant for dists');
1016 END IF;
1017
1018 IF p_status = 'Y' THEN
1019 fnd_grants_pkg.update_grant (
1020 p_api_version => 1,
1021 p_grant_guid => l_grant_guid,
1022 p_start_date => l_start_date,
1023 p_end_date => NULL,
1024 x_success => x_return_status
1025 );
1026 ELSIF p_status='N' THEN
1027 fnd_grants_pkg.update_grant (
1028 p_api_version => 1,
1029 p_grant_guid => l_grant_guid,
1030 p_start_date => l_start_date,
1031 p_end_date => SYSDATE-1,
1032 x_success => x_return_status
1033 );
1034 END IF;
1035
1036 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1037 THEN
1038 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1039 l_module, 'completed call to fnd_grants_pkg.update_grant for dists'
1040 || ' Status ' || x_return_status);
1041 END IF;
1042
1043 IF x_return_status <> 'T'
1044 THEN
1045 RAISE FND_API.G_EXC_ERROR;
1046 END IF;
1047
1048 IF FND_API.To_Boolean( p_commit )
1049 THEN
1050 COMMIT WORK;
1051 END IF;
1052
1053 -- standard call to get message count and if count is 1, get message info.
1054 FND_MSG_PUB.Count_And_Get(
1055 p_encoded => FND_API.G_FALSE,
1056 p_count => x_msg_count,
1057 p_data => x_msg_data);
1058
1059 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1060 THEN
1061 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1062 l_module,'end');
1063 END IF;
1064
1065 EXCEPTION
1066 WHEN FND_API.G_EXC_ERROR THEN
1067 ROLLBACK TO update_fun_grant;
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 FND_MSG_PUB.Count_And_Get(
1070 p_encoded => FND_API.G_FALSE,
1071 p_count => x_msg_count,
1072 p_data => x_msg_data);
1073
1074 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1075 THEN
1076 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1077 l_module,'Execution exception raised');
1078 END IF;
1079
1080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1081 ROLLBACK TO update_fun_grant;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 FND_MSG_PUB.Count_And_Get(
1084 p_encoded => FND_API.G_FALSE,
1085 p_count => x_msg_count,
1086 p_data => x_msg_data);
1087
1088 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
1089 THEN
1090 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1091 l_module,'Unexpected exception raised - ' || SQLERRM);
1092 END IF;
1093
1094 WHEN OTHERS THEN
1095 ROLLBACK TO update_fun_grant;
1096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1097 FND_MSG_PUB.Count_And_Get(
1098 p_encoded => FND_API.G_FALSE,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data);
1101
1102 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
1103 THEN
1104 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1105 l_module,'Unexpected exception raised - ' || SQLERRM);
1106 END IF;
1107
1108 END update_assign;
1109
1110
1111 /*-----------------------------------------------------
1112 * PROCEDURE is_access_allow
1113 * ----------------------------------------------------
1114 * Checks whether an FND grant on intercompany objects
1115 * exists for the person.
1116 * ---------------------------------------------------*/
1117
1118 FUNCTION is_access_allow (
1119 p_person_id IN NUMBER,
1120 p_org_id IN NUMBER)
1121 RETURN VARCHAR2
1122 IS
1123 l_exists NUMBER;
1124 BEGIN
1125 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1126 THEN
1127 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1128 'fun.plsql.fun_security.is_access_allow','begin');
1129 END IF;
1130
1131 SELECT 1
1132 INTO l_exists
1133 from DUAL
1134 where exists (select 1
1135 FROM fnd_grants fg,
1136 fnd_object_instance_sets fis,
1137 fnd_menus fm,
1138 fnd_objects fo
1139 WHERE fm.menu_name='FUN_DATA_ACCESS'
1140 AND fo.obj_name IN ('FUN_TRX_BATCHES','FUN_TRX_HEADERS','FUN_DIST_LINES')
1141 AND fis.instance_set_name IN ('FUN_TRX_BATCHES_SET','FUN_TRX_HEADERS_SET','FUN_DIST_LINES_SET')
1142 AND fg.object_id=fo.object_id
1143 AND fg.instance_set_id=fis.instance_set_id
1144 AND fg.menu_id=fm.menu_id
1145 AND fg.grantee_type='USER'
1146 AND fg.grantee_key='HZ_PARTY:'||p_person_id
1147 AND fg.parameter1=p_org_id) ;
1148
1149 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1150 THEN
1151 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1152 'fun.plsql.fun_security.is_access_allow','end');
1153 END IF;
1154
1155 IF l_exists > 0 THEN
1156 RETURN 'Y';
1157 ELSE
1158 RETURN 'N';
1159 END IF;
1160
1161
1162 EXCEPTION
1163 WHEN NO_DATA_FOUND THEN
1164 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1165 THEN
1166 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1167 'fun.plsql.fun_security.is_access_allow','end');
1168 END IF;
1169 RETURN 'N';
1170 END is_access_allow;
1171
1172
1173
1174 /*-----------------------------------------------------
1175 * PROCEDURE is_access_valid
1176 * ----------------------------------------------------
1177 * Checks whether an FND grant on intercompany objects
1178 * is valid or not.
1179 * ---------------------------------------------------*/
1180
1181 FUNCTION is_access_valid (
1182 p_person_id IN NUMBER,
1183 p_org_id IN NUMBER
1184 ) RETURN VARCHAR2
1185 IS
1186 l_start_date DATE;
1187 l_end_date DATE;
1188 BEGIN
1189
1190
1191 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1192 THEN
1193 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1194 'fun.plsql.fun_security.is_access_valid','begin');
1195 END IF;
1196
1197 SELECT start_date,end_date
1198 INTO l_start_date,l_end_date
1199 FROM fnd_grants fg,
1200 fnd_object_instance_sets fis,
1201 fnd_menus fm,
1202 fnd_objects fo
1203 WHERE fm.menu_name = 'FUN_DATA_ACCESS'
1204 AND fo.obj_name = 'FUN_TRX_BATCHES'
1205 AND fis.instance_set_name = 'FUN_TRX_BATCHES_SET'
1206 AND fg.object_id = fo.object_id
1207 AND fg.instance_set_id = fis.instance_set_id
1208 AND fg.menu_id = fm.menu_id
1209 AND fg.grantee_type = 'USER'
1210 AND fg.grantee_key = 'HZ_PARTY:'||p_person_id
1211 AND fg.parameter1 = p_org_id
1212 AND ROWNUM = 1;
1213
1214 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_level)
1215 THEN
1216 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1217 'fun.plsql.fun_security.is_access_valid','end');
1218 END IF;
1219
1220 IF ( (l_start_date IS NULL OR l_start_date <= SYSDATE)
1221 AND
1222 (l_end_date IS NULL OR l_end_date >=SYSDATE) ) THEN
1223 RETURN 'Y';
1224 ELSE
1225 RETURN 'N';
1226 END IF;
1227
1228 EXCEPTION
1229 WHEN NO_DATA_FOUND THEN
1230 RETURN 'N';
1231
1232 END is_access_valid;
1233
1234 END FUN_SECURITY;