1 PACKAGE BODY IGS_PE_VAL_PIGM AS
2 /* $Header: IGSPE01B.pls 120.1 2006/01/18 22:50:56 skpandey noship $ */
3
4 ------------------------------------------------------------------------------------------
5 --Created by :
6 --Date created:
7 --
8 --Purpose:
9 --
10 --
11 --Known limitations/enhancements and/or remarks:
12 --
13 --Change History:
14 --Who When What
15 --sarakshi 27-sep-2001 Added function merged_ind as a part of person Detail build.bug no:2000408
16 ----------------------------------------------------------------------------------------------
17 -- Validate IGS_PE_PERSON id group member ins/upd/del security
18 FUNCTION idgp_val_pigm_iud(
19 p_group_id IN IGS_PE_PERSID_GROUP_ALL.group_id%TYPE ,
20 p_transaction_type IN VARCHAR2 ,
21 p_message_name OUT NOCOPY VARCHAR2 )
22 RETURN BOOLEAN IS
23 gv_other_detail VARCHAR2(255);
24 BEGIN -- idgp_val_pigm_iud
25 -- Validate that the current user has permission to insert/update/delete
26 -- IGS_PE_PRSID_GRP_MEM records for the IGS_PE_PERSID_GROUP specified by the
27 -- group_id. The application owner has full permissions. The IGS_PE_PERSID_GROUP
28 -- creator identified by IGS_PE_PERSID_GROUP.creator_person_id also has full
29 -- permissions. Any other users must have a record in the
30 -- IGS_PE_PRSID_GRP_SEC table with the insert, update and delete
31 -- permissions specified.
32 DECLARE
33 cst_person_id_group CONSTANT
34 user_synonyms.synonym_name%TYPE := 'IGS_PE_PERSID_GROUP';
35 cst_insert CONSTANT VARCHAR2(10) := 'INSERT';
36 cst_update CONSTANT VARCHAR2(10) := 'UPDATE';
37 cst_delete CONSTANT VARCHAR2(10) := 'DELETE';
38 v_dummy IGS_PE_PERSID_GROUP_ALL.creator_person_id%TYPE;
39 v_user VARCHAR2(30);
40 v_insert_ind IGS_PE_PRSID_GRP_SEC.insert_ind%TYPE;
41 v_update_ind IGS_PE_PRSID_GRP_SEC.update_ind%TYPE;
42 v_delete_ind IGS_PE_PRSID_GRP_SEC.delete_ind%TYPE;
43
44 --skpandey, Bug#4937960: Changed c_pig cursor definition to optimize query
45 CURSOR c_pig(cp_group_id IGS_PE_PERSID_GROUP_ALL.group_id%TYPE) IS
46 SELECT pig.creator_person_id
47 FROM IGS_PE_PERSID_GROUP_ALL pig, igs_pe_hz_parties pe
48 WHERE pig.group_id = cp_group_id
49 AND pig.creator_person_id = pe.party_id
50 AND pe.oracle_username = fnd_global.user_name;
51
52 --skpandey, Bug#4937960: Changed c_pigs cursor definition to optimize query
53 CURSOR c_pigs(cp_group_id IGS_PE_PRSID_GRP_SEC.group_id%TYPE) IS
54 SELECT pigs.insert_ind,
55 pigs.update_ind,
56 pigs.delete_ind
57 FROM IGS_PE_PRSID_GRP_SEC pigs,
58 IGS_PE_HZ_PARTIES pe
59 WHERE pigs.group_id = cp_group_id
60 AND pigs.person_id = pe.party_id
61 AND pe.oracle_username = fnd_global.user_name;
62
63 BEGIN
64 -- 1.
65 IF (p_group_id IS NULL) THEN
66 p_message_name := null;
67 RETURN TRUE;
68 END IF;
69 -- 2. Check if the user is the application owner.
70
71 -- 3. Check if the current user is the group creator.
72 -- The group_creator has full insert/update/delete priviledges.
73 OPEN c_pig(p_group_id);
74 FETCH c_pig INTO v_dummy;
75 IF (c_pig%FOUND) THEN
76 CLOSE c_pig;
77 p_message_name := null;
78 RETURN TRUE;
79 END IF;
80 CLOSE c_pig;
81 -- 4. Get the permissions of the current user.
82 OPEN c_pigs(p_group_id);
83 FETCH c_pigs INTO v_insert_ind,
84 v_update_ind,
85 v_delete_ind;
86 IF (c_pigs%NOTFOUND) THEN
87 CLOSE c_pigs;
88 p_message_name := 'IGS_PE_NO_PRIV_INS_UPD_DEL';
89 RETURN FALSE;
90 END IF;
91 CLOSE c_pigs;
92 IF (p_transaction_type = cst_insert AND
93 v_insert_ind = 'N') THEN
94 p_message_name:= 'IGS_PE_NO_PRIV_INS';
95 RETURN FALSE;
96 END IF;
97 IF (p_transaction_type = cst_update AND
98 v_update_ind = 'N') THEN
99 p_message_name:= 'IGS_PE_NO_PRIV_UPD';
100 RETURN FALSE;
101 END IF;
102 IF (p_transaction_type = cst_delete AND
103 v_delete_ind = 'N') THEN
104 p_message_name:= 'IGS_PE_NO_PRIV_DEL';
105 RETURN FALSE;
106 END IF;
107 p_message_name := null;
108 RETURN TRUE;
109 EXCEPTION
110 WHEN OTHERS THEN
111 IF (c_pig%ISOPEN) THEN
112 CLOSE c_pig;
113 END IF;
114 IF (c_pigs%ISOPEN) THEN
115 CLOSE c_pigs;
116 END IF;
117 END;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
121 IGS_GE_MSG_STACK.ADD;
122 App_Exception.Raise_Exception;
123 END idgp_val_pigm_iud;
124
125 FUNCTION merged_ind(p_person_id NUMBER) RETURN VARCHAR2 IS
126 ------------------------------------------------------------------------------------------
127 --Created by : sarakshi
128 --Date created:27-sep-2001
129 --
130 --Purpose:
131 --
132 --
133 --Known limitations/enhancements and/or remarks:
134 --
135 --Change History:
136 --Who When What
137 ----------------------------------------------------------------------------------------------
138
139 CURSOR cur_pers_obsolete IS
140 SELECT 'X'
141 FROM IGS_PE_ALT_PERS_ID pa,IGS_PE_PERSON_ID_TYP pt
142 WHERE pa.PE_PERSON_ID=p_person_id
143 AND pa.person_id_type=pt.person_id_type
144 AND pt.s_person_id_type='MERGE-INTO';
145 l_cur_pers_obsolete VARCHAR2(1);
146
147 BEGIN
148 OPEN cur_pers_obsolete;
149 FETCH cur_pers_obsolete INTO l_cur_pers_obsolete;
150 IF cur_pers_obsolete%FOUND THEN
151 CLOSE cur_pers_obsolete;
152 RETURN 'Y';
153 ELSE
154 CLOSE cur_pers_obsolete;
155 RETURN 'N';
156 END IF;
157 END merged_ind;
158
159 END IGS_PE_VAL_PIGM;
[Home] [Help]