1 PACKAGE BODY IGS_PE_VAL_PIGS AS
2 /* $Header: IGSPE02B.pls 120.1 2006/01/18 22:41:39 skpandey noship $ */
3
4 --
5 -- Validate IGS_PE_PERSON id group security ins/upd/del security
6 FUNCTION idgp_val_pigs_iud(
7 p_group_id IN IGS_PE_PERSID_GROUP_ALL.group_id%TYPE ,
8 p_person_id IN NUMBER ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN IS
11 gv_other_detail VARCHAR2(255);
12 BEGIN -- idgp_val_pigs_iud
13 -- Validate that only the group creator and application owner can
14 -- insert, update and delete IGS_PE_PERSON ID Group Security records.
15 DECLARE
16 v_creator_person_id NUMBER;
17 v_user VARCHAR2(30);
18
19 -- skpandey, Bug#4937960: changed the definition of cursor
20 CURSOR c_pig IS
21 SELECT pig.creator_person_id
22 FROM IGS_PE_PERSID_GROUP_ALL pig, igs_pe_hz_parties pe, hz_parties hp
23 WHERE pig.group_id = p_group_id AND
24 pig.creator_person_id = hp.party_id AND
25 hp.party_id = pe.party_id (+) AND
26 pig.creator_person_id = pe.party_id AND
27 (pe.oracle_username = fnd_global.user_name OR pe.oracle_username IS NULL);
28
29 BEGIN
30 -- 1.
31 IF (p_group_id IS NULL) THEN
32 p_message_name := null;
33 RETURN TRUE;
34 END IF;
35 -- 2. Check if the user is the application owner.
36
37 -- 3. Check if the current user is the group creator.
38 -- The group_creator has full insert/update/delete priviledges.
39 OPEN c_pig;
40 FETCH c_pig INTO v_creator_person_id;
41 IF (c_pig%NOTFOUND) THEN
42 CLOSE c_pig;
43 p_message_name := 'IGS_PE_GRP_CREATOR_CANNOT_IUD';
44 RETURN FALSE;
45 END IF;
46 CLOSE c_pig;
47 -- 4. Check if the security record is being created for the group creator
48 IF v_creator_person_id = p_person_id THEN
49 p_message_name := 'IGS_PE_GRP_SEC_REC_CANNOT_INS';
50 RETURN FALSE;
51 END IF;
52 p_message_name := null;
53 RETURN TRUE;
54 EXCEPTION
55 WHEN OTHERS THEN
56 IF (c_pig%ISOPEN) THEN
57 CLOSE c_pig;
58 END IF;
59 END;
60 EXCEPTION
61 WHEN OTHERS THEN
62 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
63 IGS_GE_MSG_STACK.ADD;
64 App_Exception.Raise_Exception;
65 END idgp_val_pigs_iud;
66 END IGS_PE_VAL_PIGS;