[Home] [Help]
PACKAGE BODY: APPS.IBC_OBJECT_GRANT_GROUPS_PKG
Source
1 PACKAGE BODY Ibc_Object_Grant_Groups_Pkg AS
2 /* $Header: ibctoggb.pls 115.3 2003/12/09 22:44:10 enunez ship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- --------- ------ ------------------------------------------
7 -- vicho 11/05/2002 Remove G_MISS defaulting on UPDATE_ROW
8 -- enunez 02/12/2003 Updated to use new column INHERITANCE_TYPE
9 -- enunez 12/09/2003 default inheritance type when inserting
10
11
12 PROCEDURE INSERT_ROW (
13 PX_ROWID IN OUT NOCOPY VARCHAR2,
14 P_OBJECT_GRANT_GROUP_ID IN NUMBER,
15 P_OBJECT_VERSION_NUMBER IN NUMBER,
16 P_GRANT_GROUP_ID IN NUMBER,
17 P_OBJECT_ID IN NUMBER,
18 P_INHERITED_FLAG IN VARCHAR2,
19 P_INHERITED_FROM IN NUMBER,
20 P_INSTANCE_PK1_VALUE IN VARCHAR2,
21 P_INSTANCE_PK2_VALUE IN VARCHAR2,
22 P_INSTANCE_PK3_VALUE IN VARCHAR2,
23 P_INSTANCE_PK4_VALUE IN VARCHAR2,
24 P_INSTANCE_PK5_VALUE IN VARCHAR2,
25 P_INHERITANCE_TYPE IN VARCHAR2,
26 p_CREATION_DATE IN DATE,
27 p_CREATED_BY IN NUMBER,
28 p_LAST_UPDATE_DATE IN DATE,
29 p_LAST_UPDATED_BY IN NUMBER,
30 p_LAST_UPDATE_LOGIN IN NUMBER
31 ) IS
32 CURSOR C IS SELECT ROWID FROM IBC_OBJECT_GRANT_GROUPS
33 WHERE OBJECT_GRANT_GROUP_ID = P_OBJECT_GRANT_GROUP_ID
34 ;
35 BEGIN
36 INSERT INTO IBC_OBJECT_GRANT_GROUPS (
37 OBJECT_VERSION_NUMBER,
38 OBJECT_GRANT_GROUP_ID,
39 GRANT_GROUP_ID,
40 OBJECT_ID,
41 INHERITED_FLAG,
42 INHERITED_FROM,
43 INSTANCE_PK1_VALUE,
44 INSTANCE_PK2_VALUE,
45 INSTANCE_PK3_VALUE,
46 INSTANCE_PK4_VALUE,
47 INSTANCE_PK5_VALUE,
48 INHERITANCE_TYPE,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN
54 ) VALUES (
55 P_OBJECT_VERSION_NUMBER,
56 P_OBJECT_GRANT_GROUP_ID,
57 P_GRANT_GROUP_ID,
58 P_OBJECT_ID,
59 P_INHERITED_FLAG,
60 P_INHERITED_FROM,
61 P_INSTANCE_PK1_VALUE,
62 P_INSTANCE_PK2_VALUE,
63 P_INSTANCE_PK3_VALUE,
64 P_INSTANCE_PK4_VALUE,
65 P_INSTANCE_PK5_VALUE,
66 NVL(P_INHERITANCE_TYPE, 'FOLDER'),
67 DECODE(p_creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,p_creation_date),
68 DECODE(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,NULL, FND_GLOBAL.user_id, p_created_by),
69 DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,p_last_update_date),
70 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,NULL, FND_GLOBAL.user_id, p_last_updated_by),
71 DECODE(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,NULL, FND_GLOBAL.login_id, p_last_update_login)
72 );
73
74 OPEN c;
75 FETCH c INTO PX_ROWID;
76 IF (c%NOTFOUND) THEN
77 CLOSE c;
78 RAISE NO_DATA_FOUND;
79 END IF;
80 CLOSE c;
81
82 END INSERT_ROW;
83
84 PROCEDURE LOCK_ROW (
85 P_OBJECT_GRANT_GROUP_ID IN NUMBER,
86 P_OBJECT_VERSION_NUMBER IN NUMBER,
87 P_GRANT_GROUP_ID IN NUMBER,
88 P_OBJECT_ID IN NUMBER,
89 P_INHERITED_FLAG IN VARCHAR2,
90 P_INHERITED_FROM IN NUMBER,
91 P_INSTANCE_PK1_VALUE IN VARCHAR2,
92 P_INSTANCE_PK2_VALUE IN VARCHAR2,
93 P_INSTANCE_PK3_VALUE IN VARCHAR2,
94 P_INSTANCE_PK4_VALUE IN VARCHAR2,
95 P_INSTANCE_PK5_VALUE IN VARCHAR2,
96 P_INHERITANCE_TYPE IN VARCHAR2
97 ) IS
98 CURSOR c IS SELECT
99 OBJECT_VERSION_NUMBER,
100 GRANT_GROUP_ID,
101 OBJECT_ID,
102 INHERITED_FLAG,
103 INHERITED_FROM,
104 INSTANCE_PK1_VALUE,
105 INSTANCE_PK2_VALUE,
106 INSTANCE_PK3_VALUE,
107 INSTANCE_PK4_VALUE,
108 INSTANCE_PK5_VALUE,
109 INHERITANCE_TYPE
110 FROM IBC_OBJECT_GRANT_GROUPS
111 WHERE OBJECT_GRANT_GROUP_ID = P_OBJECT_GRANT_GROUP_ID
112 FOR UPDATE OF OBJECT_GRANT_GROUP_ID NOWAIT;
113 recinfo c%ROWTYPE;
114
115 BEGIN
116 OPEN c;
117 FETCH c INTO recinfo;
118 IF (c%NOTFOUND) THEN
119 CLOSE c;
120 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
121 app_exception.raise_exception;
122 END IF;
123 CLOSE c;
124 IF ( (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
125 AND (recinfo.GRANT_GROUP_ID = P_GRANT_GROUP_ID)
126 AND (recinfo.OBJECT_ID = P_OBJECT_ID)
127 AND (recinfo.INHERITED_FLAG = P_INHERITED_FLAG)
128 AND ((recinfo.INHERITED_FROM = P_INHERITED_FROM)
129 OR ((recinfo.INHERITED_FROM IS NULL) AND (P_INHERITED_FROM IS NULL)))
130 AND (recinfo.INSTANCE_PK1_VALUE = P_INSTANCE_PK1_VALUE)
131 AND ((recinfo.INSTANCE_PK2_VALUE = P_INSTANCE_PK2_VALUE)
132 OR ((recinfo.INSTANCE_PK2_VALUE IS NULL) AND (P_INSTANCE_PK2_VALUE IS NULL)))
133 AND ((recinfo.INSTANCE_PK3_VALUE = P_INSTANCE_PK3_VALUE)
134 OR ((recinfo.INSTANCE_PK3_VALUE IS NULL) AND (P_INSTANCE_PK3_VALUE IS NULL)))
135 AND ((recinfo.INSTANCE_PK4_VALUE = P_INSTANCE_PK4_VALUE)
136 OR ((recinfo.INSTANCE_PK4_VALUE IS NULL) AND (P_INSTANCE_PK4_VALUE IS NULL)))
137 AND ((recinfo.INSTANCE_PK5_VALUE = P_INSTANCE_PK5_VALUE)
138 OR ((recinfo.INSTANCE_PK5_VALUE IS NULL) AND (P_INSTANCE_PK5_VALUE IS NULL)))
139 AND ((recinfo.INHERITANCE_TYPE = P_INHERITANCE_TYPE)
140 OR ((recinfo.INHERITANCE_TYPE IS NULL) AND (P_INHERITANCE_TYPE IS NULL)))
141 ) THEN
142 NULL;
143 ELSE
144 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
145 app_exception.raise_exception;
146 END IF;
147
148 END LOCK_ROW;
149
150 PROCEDURE UPDATE_ROW (
151 P_OBJECT_GRANT_GROUP_ID IN NUMBER,
152 p_OBJECT_VERSION_NUMBER IN NUMBER,
153 P_GRANT_GROUP_ID IN NUMBER,
154 P_OBJECT_ID IN NUMBER,
155 P_INHERITED_FLAG IN VARCHAR2,
156 P_INHERITED_FROM IN NUMBER,
157 P_INSTANCE_PK1_VALUE IN VARCHAR2,
158 P_INSTANCE_PK2_VALUE IN VARCHAR2,
159 P_INSTANCE_PK3_VALUE IN VARCHAR2,
160 P_INSTANCE_PK4_VALUE IN VARCHAR2,
161 P_INSTANCE_PK5_VALUE IN VARCHAR2,
162 P_INHERITANCE_TYPE IN VARCHAR2,
163 p_LAST_UPDATED_BY IN NUMBER,
164 p_LAST_UPDATE_DATE IN DATE,
165 p_LAST_UPDATE_LOGIN IN NUMBER
166 ) IS
167 BEGIN
168 UPDATE IBC_OBJECT_GRANT_GROUPS SET
169 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
170 GRANT_GROUP_ID = P_GRANT_GROUP_ID,
171 OBJECT_ID = P_OBJECT_ID,
172 INHERITED_FLAG = P_INHERITED_FLAG,
173 INHERITED_FROM = P_INHERITED_FROM,
174 INSTANCE_PK1_VALUE = P_INSTANCE_PK1_VALUE,
175 INSTANCE_PK2_VALUE = P_INSTANCE_PK2_VALUE,
176 INSTANCE_PK3_VALUE = P_INSTANCE_PK3_VALUE,
177 INSTANCE_PK4_VALUE = P_INSTANCE_PK4_VALUE,
178 INSTANCE_PK5_VALUE = P_INSTANCE_PK5_VALUE,
179 INHERITANCE_TYPE = NVL(P_INHERITANCE_TYPE, 'FOLDER'),
180 last_update_date = DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,
181 NULL, SYSDATE, p_last_update_date),
182 last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM,
183 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
184 p_last_updated_by),
185 last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM,
186 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
187 p_last_update_login)
188 WHERE OBJECT_GRANT_GROUP_ID = P_OBJECT_GRANT_GROUP_ID
189 AND object_version_number = DECODE(p_object_version_number,
190 FND_API.G_MISS_NUM,object_version_number,
191 NULL,object_version_number,
192 p_object_version_number);
193
194 IF (SQL%NOTFOUND) THEN
195 RAISE NO_DATA_FOUND;
196 END IF;
197
198 END UPDATE_ROW;
199
200 PROCEDURE DELETE_ROW (
201 P_OBJECT_GRANT_GROUP_ID IN NUMBER
202 ) IS
203 BEGIN
204
205 DELETE FROM IBC_OBJECT_GRANT_GROUPS
206 WHERE OBJECT_GRANT_GROUP_ID = P_OBJECT_GRANT_GROUP_ID;
207
208 IF (SQL%NOTFOUND) THEN
209 RAISE NO_DATA_FOUND;
210 END IF;
211 END DELETE_ROW;
212
213
214 END Ibc_Object_Grant_Groups_Pkg;