[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_RSC_PKG
Source
1 PACKAGE BODY JTF_TERR_RSC_PKG AS
2 /* $Header: jtfvtrcb.pls 120.2 2006/05/24 18:43:45 mhtran ship $ */
3
4 -- 01/20/99 VNEDUNGA Changing update/lock row procedure to use
5 -- 01/20/00 VNEDUNGA Changing = NULL to IS NULL
6 -- 02/22/00 JDOCHERT Passing in ORG_ID to Insert/Update/Lock
7 -- 03/16/00 VNEDUNGA Adding Full access flag
8 -- 06/08/00 VNEDUNGA Adding group_id flag
9 -- 06/26/02 ARPATEL Adding person_id column to Insert row
10 -- 01/09/03 JDOCHERT BUG#2739970
11
12
13 PROCEDURE Insert_Row(
14 x_Rowid IN OUT NOCOPY VARCHAR2,
15 x_TERR_RSC_ID IN OUT NOCOPY NUMBER,
16 x_LAST_UPDATE_DATE IN DATE,
17 x_LAST_UPDATED_BY IN NUMBER,
18 x_CREATION_DATE IN DATE,
19 x_CREATED_BY IN NUMBER,
20 x_LAST_UPDATE_LOGIN IN NUMBER,
21 x_TERR_ID IN NUMBER,
22 x_RESOURCE_ID IN NUMBER,
23 x_GROUP_ID IN NUMBER,
24 x_RESOURCE_TYPE IN VARCHAR2,
25 x_ROLE IN VARCHAR2,
26 x_PRIMARY_CONTACT_FLAG IN VARCHAR2,
27 x_START_DATE_ACTIVE IN DATE,
28 x_END_DATE_ACTIVE IN DATE,
29 x_FULL_ACCESS_FLAG IN VARCHAR2,
30 x_ORG_ID IN NUMBER
31 ) IS
32 CURSOR C IS SELECT rowid FROM JTF_TERR_RSC_ALL
33 WHERE TERR_RSC_ID = x_TERR_RSC_ID;
34 CURSOR C2 IS SELECT JTF_TERR_RSC_s.nextval FROM sys.dual;
35 BEGIN
36 If (x_TERR_RSC_ID IS NULL) then
37 OPEN C2;
38 FETCH C2 INTO x_TERR_RSC_ID;
39 CLOSE C2;
40 End If;
41 INSERT INTO JTF_TERR_RSC_ALL(
42 TERR_RSC_ID,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_LOGIN,
48 TERR_ID,
49 RESOURCE_ID,
50 GROUP_ID,
51 RESOURCE_TYPE,
52 ROLE,
53 PRIMARY_CONTACT_FLAG,
54 START_DATE_ACTIVE,
55 END_DATE_ACTIVE,
56 FULL_ACCESS_FLAG,
57 ORG_ID
58 ) VALUES (
59 x_TERR_RSC_ID,
60 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
61 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
62 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
63 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
64 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
65 decode( x_TERR_ID, FND_API.G_MISS_NUM, NULL,x_TERR_ID),
66 decode( x_RESOURCE_ID, FND_API.G_MISS_NUM, NULL,x_RESOURCE_ID),
67 decode( x_GROUP_ID, FND_API.G_MISS_NUM, NULL,x_GROUP_ID),
68 decode( x_RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, x_RESOURCE_TYPE),
69 decode( x_ROLE, FND_API.G_MISS_CHAR, NULL, x_ROLE),
70 decode( x_PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,x_PRIMARY_CONTACT_FLAG),
71 decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,x_START_DATE_ACTIVE),
72 decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,x_END_DATE_ACTIVE),
73 decode( x_FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,x_FULL_ACCESS_FLAG),
74 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID)
75 );
76 OPEN C;
77 FETCH C INTO x_Rowid;
78 If (C%NOTFOUND) then
79 CLOSE C;
80 RAISE NO_DATA_FOUND;
81 End If;
82 End Insert_Row;
83
84
85 PROCEDURE Insert_Row(
86 x_Rowid IN OUT NOCOPY VARCHAR2,
87 x_TERR_RSC_ID IN OUT NOCOPY NUMBER,
88 x_LAST_UPDATE_DATE IN DATE,
89 x_LAST_UPDATED_BY IN NUMBER,
90 x_CREATION_DATE IN DATE,
91 x_CREATED_BY IN NUMBER,
92 x_LAST_UPDATE_LOGIN IN NUMBER,
93 x_TERR_ID IN NUMBER,
94 x_RESOURCE_ID IN NUMBER,
95 x_GROUP_ID IN NUMBER,
96 x_RESOURCE_TYPE IN VARCHAR2,
97 x_ROLE IN VARCHAR2,
98 x_PRIMARY_CONTACT_FLAG IN VARCHAR2,
99 x_START_DATE_ACTIVE IN DATE,
100 x_END_DATE_ACTIVE IN DATE,
101 x_FULL_ACCESS_FLAG IN VARCHAR2,
102 x_ORG_ID IN NUMBER,
103 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
104 x_ATTRIBUTE1 IN VARCHAR2,
105 x_ATTRIBUTE2 IN VARCHAR2,
106 x_ATTRIBUTE3 IN VARCHAR2,
107 x_ATTRIBUTE4 IN VARCHAR2,
108 x_ATTRIBUTE5 IN VARCHAR2,
109 x_ATTRIBUTE6 IN VARCHAR2,
110 x_ATTRIBUTE7 IN VARCHAR2,
111 x_ATTRIBUTE8 IN VARCHAR2,
112 x_ATTRIBUTE9 IN VARCHAR2,
113 x_ATTRIBUTE10 IN VARCHAR2,
114 x_ATTRIBUTE11 IN VARCHAR2,
115 x_ATTRIBUTE12 IN VARCHAR2,
116 x_ATTRIBUTE13 IN VARCHAR2,
117 x_ATTRIBUTE14 IN VARCHAR2,
118 x_ATTRIBUTE15 IN VARCHAR2
119 ) IS
120 CURSOR C IS SELECT rowid FROM JTF_TERR_RSC_ALL
121 WHERE TERR_RSC_ID = x_TERR_RSC_ID;
122 CURSOR C2 IS SELECT JTF_TERR_RSC_s.nextval FROM sys.dual;
123 BEGIN
124 If (x_TERR_RSC_ID IS NULL) then
125 OPEN C2;
126 FETCH C2 INTO x_TERR_RSC_ID;
127 CLOSE C2;
128 End If;
129 INSERT INTO JTF_TERR_RSC_ALL(
130 TERR_RSC_ID,
131 LAST_UPDATE_DATE,
132 LAST_UPDATED_BY,
133 CREATION_DATE,
134 CREATED_BY,
135 LAST_UPDATE_LOGIN,
136 TERR_ID,
137 RESOURCE_ID,
138 GROUP_ID,
139 RESOURCE_TYPE,
140 ROLE,
141 PRIMARY_CONTACT_FLAG,
142 START_DATE_ACTIVE,
143 END_DATE_ACTIVE,
144 FULL_ACCESS_FLAG,
145 ORG_ID,
146 ATTRIBUTE_CATEGORY,
147 ATTRIBUTE1,
148 ATTRIBUTE2,
149 ATTRIBUTE3,
150 ATTRIBUTE4,
151 ATTRIBUTE5,
152 ATTRIBUTE6,
153 ATTRIBUTE7,
154 ATTRIBUTE8,
155 ATTRIBUTE9,
156 ATTRIBUTE10,
157 ATTRIBUTE11,
158 ATTRIBUTE12,
159 ATTRIBUTE13,
160 ATTRIBUTE14,
161 ATTRIBUTE15
162 ) VALUES (
163 x_TERR_RSC_ID,
164 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
165 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
166 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
167 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
168 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
169 decode( x_TERR_ID, FND_API.G_MISS_NUM, NULL,x_TERR_ID),
170 decode( x_RESOURCE_ID, FND_API.G_MISS_NUM, NULL,x_RESOURCE_ID),
171 decode( x_GROUP_ID, FND_API.G_MISS_NUM, NULL,x_GROUP_ID),
172 decode( x_RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, x_RESOURCE_TYPE),
173 decode( x_ROLE, FND_API.G_MISS_CHAR, NULL, x_ROLE),
174 decode( x_PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,x_PRIMARY_CONTACT_FLAG),
175 decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,x_START_DATE_ACTIVE),
176 decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,x_END_DATE_ACTIVE),
177 decode( x_FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,x_FULL_ACCESS_FLAG),
178 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID),
179 decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_CATEGORY),
180 decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE1),
181 decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE2),
182 decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE3),
183 decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE4),
184 decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE5),
185 decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE6),
186 decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE7),
187 decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE8),
188 decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE9),
189 decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE10),
190 decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE11),
191 decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE12),
192 decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE13),
193 decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE14),
194 decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE15)
195 );
196 OPEN C;
197 FETCH C INTO x_Rowid;
198 If (C%NOTFOUND) then
199 CLOSE C;
200 RAISE NO_DATA_FOUND;
201 End If;
202 End Insert_Row;
203
204
205 PROCEDURE Delete_Row( x_TERR_RSC_ID IN NUMBER
206 ) IS
207 BEGIN
208 DELETE FROM JTF_TERR_RSC_ALL
209 WHERE TERR_RSC_ID = x_TERR_RSC_ID;
210 If (SQL%NOTFOUND) then
211 RAISE NO_DATA_FOUND;
212 End If;
213 END Delete_Row;
214
215
216
217 PROCEDURE Update_Row(
218 x_Rowid IN VARCHAR2,
219 x_TERR_RSC_ID IN NUMBER,
220 x_LAST_UPDATE_DATE IN DATE,
221 x_LAST_UPDATED_BY IN NUMBER,
222 x_CREATION_DATE IN DATE,
223 x_CREATED_BY IN NUMBER,
224 x_LAST_UPDATE_LOGIN IN NUMBER,
225 x_TERR_ID IN NUMBER,
226 x_RESOURCE_ID IN NUMBER,
227 x_GROUP_ID IN NUMBER,
228 x_RESOURCE_TYPE IN VARCHAR2,
229 x_ROLE IN VARCHAR2,
230 x_PRIMARY_CONTACT_FLAG IN VARCHAR2,
231 x_START_DATE_ACTIVE IN DATE,
232 x_END_DATE_ACTIVE IN DATE,
233 x_FULL_ACCESS_FLAG IN VARCHAR2,
234 x_ORG_ID IN NUMBER
235 ) IS
236 BEGIN
237 Update JTF_TERR_RSC_ALL
238 SET
239 TERR_RSC_ID = decode( x_TERR_RSC_ID, FND_API.G_MISS_NUM,TERR_RSC_ID,x_TERR_RSC_ID),
240 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
241 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
242 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
243 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
244 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
245 TERR_ID = decode( x_TERR_ID, FND_API.G_MISS_NUM,TERR_ID,x_TERR_ID),
246 RESOURCE_ID = decode( x_RESOURCE_ID, FND_API.G_MISS_NUM, RESOURCE_ID, x_RESOURCE_ID),
247 GROUP_ID = decode( x_GROUP_ID, FND_API.G_MISS_NUM, GROUP_ID, x_GROUP_ID),
248 RESOURCE_TYPE = decode( x_RESOURCE_TYPE, FND_API.G_MISS_CHAR, RESOURCE_TYPE, x_RESOURCE_TYPE),
249 ROLE = decode( x_ROLE, FND_API.G_MISS_CHAR, ROLE, x_ROLE),
250 PRIMARY_CONTACT_FLAG = decode( x_PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR,PRIMARY_CONTACT_FLAG,x_PRIMARY_CONTACT_FLAG),
251 START_DATE_ACTIVE = decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE,START_DATE_ACTIVE,x_START_DATE_ACTIVE),
252 END_DATE_ACTIVE = decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE,END_DATE_ACTIVE,x_END_DATE_ACTIVE),
253 FULL_ACCESS_FLAG = decode( x_FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, FULL_ACCESS_FLAG,x_FULL_ACCESS_FLAG),
254 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM,ORG_ID,x_ORG_ID)
255 where TERR_RSC_ID = x_TERR_RSC_ID;
256
257 If (SQL%NOTFOUND) then
258 RAISE NO_DATA_FOUND;
259 End If;
260 END Update_Row;
261
262
263
264 PROCEDURE Lock_Row(
265 x_Rowid IN VARCHAR2,
266 x_TERR_RSC_ID IN NUMBER,
267 x_LAST_UPDATE_DATE IN DATE,
268 x_LAST_UPDATED_BY IN NUMBER,
269 x_CREATION_DATE IN DATE,
270 x_CREATED_BY IN NUMBER,
271 x_LAST_UPDATE_LOGIN IN NUMBER,
272 x_TERR_ID IN NUMBER,
273 x_RESOURCE_ID IN NUMBER,
274 x_GROUP_ID IN NUMBER,
275 x_RESOURCE_TYPE IN VARCHAR2,
276 x_ROLE IN VARCHAR2,
277 x_PRIMARY_CONTACT_FLAG IN VARCHAR2,
278 x_START_DATE_ACTIVE IN DATE,
279 x_END_DATE_ACTIVE IN DATE,
280 x_FULL_ACCESS_FLAG IN VARCHAR2,
281 x_ORG_ID IN NUMBER
282 ) IS
283 CURSOR C IS
284 SELECT *
285 FROM JTF_TERR_RSC_ALL
286 WHERE TERR_RSC_ID = x_TERR_RSC_ID
287 FOR UPDATE of TERR_RSC_ID NOWAIT;
288 Recinfo C%ROWTYPE;
289 BEGIN
290 OPEN C;
291 FETCH C INTO Recinfo;
292 If (C%NOTFOUND) then
293 CLOSE C;
294 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
295 APP_EXCEPTION.RAISE_EXCEPTION;
296 End If;
297 CLOSE C;
298 if (
299 ( ( Recinfo.TERR_RSC_ID = x_TERR_RSC_ID)
300 OR ( ( Recinfo.TERR_RSC_ID IS NULL )
301 AND ( x_TERR_RSC_ID IS NULL )))
302 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
303 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
304 AND ( x_LAST_UPDATE_DATE IS NULL )))
305 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
306 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
307 AND ( x_LAST_UPDATED_BY IS NULL )))
308 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
309 OR ( ( Recinfo.CREATION_DATE IS NULL )
310 AND ( x_CREATION_DATE IS NULL )))
311 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
312 OR ( ( Recinfo.CREATED_BY IS NULL )
313 AND ( x_CREATED_BY IS NULL )))
314 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
315 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
316 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
317 AND ( ( Recinfo.TERR_ID = x_TERR_ID)
318 OR ( ( Recinfo.TERR_ID IS NULL )
319 AND ( x_TERR_ID IS NULL )))
320 AND ( ( Recinfo.RESOURCE_ID = x_RESOURCE_ID)
321 OR ( ( Recinfo.RESOURCE_ID IS NULL )
322 AND ( x_RESOURCE_ID IS NULL )))
323 AND ( ( Recinfo.GROUP_ID = x_GROUP_ID)
324 OR ( ( Recinfo.GROUP_ID IS NULL )
325 AND ( x_GROUP_ID IS NULL )))
326 AND ( ( Recinfo.RESOURCE_TYPE = x_RESOURCE_TYPE)
327 OR ( ( Recinfo.RESOURCE_TYPE IS NULL )
328 AND ( x_RESOURCE_TYPE IS NULL )))
329 AND ( ( Recinfo.ROLE = x_ROLE)
330 OR ( ( Recinfo.ROLE IS NULL )
331 AND ( x_ROLE IS NULL )))
332 AND ( ( Recinfo.PRIMARY_CONTACT_FLAG = x_PRIMARY_CONTACT_FLAG)
333 OR ( ( Recinfo.PRIMARY_CONTACT_FLAG IS NULL )
334 AND ( x_PRIMARY_CONTACT_FLAG IS NULL )))
335 AND ( ( Recinfo.START_DATE_ACTIVE = x_START_DATE_ACTIVE)
336 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
337 AND ( x_START_DATE_ACTIVE IS NULL )))
338 AND ( ( Recinfo.END_DATE_ACTIVE = x_END_DATE_ACTIVE)
339 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
340 AND ( x_END_DATE_ACTIVE IS NULL )))
341 AND ( ( Recinfo.FULL_ACCESS_FLAG = x_FULL_ACCESS_FLAG)
342 OR ( ( Recinfo.FULL_ACCESS_FLAG IS NULL )
343 AND ( x_FULL_ACCESS_FLAG IS NULL )))
344 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
345 OR ( ( Recinfo.ORG_ID IS NULL )
346 AND ( x_ORG_ID IS NULL )))
347 ) then
348 return;
349 else
350 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
351 APP_EXCEPTION.RAISE_EXCEPTION;
352 End If;
353 END Lock_Row;
354
355 END JTF_TERR_RSC_PKG;