[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_PKG
Source
1 PACKAGE BODY JTF_TERR_PKG AS
2 /* $Header: jtfvtryb.pls 120.2 2008/05/13 06:05:07 vpalle ship $ */
3
4 -- 01/25/00 vnedunga chnaging lock_row to use terr_id
5 -- 02/24/00 vnedunga fixing decode for date fields
6 -- 02/29/00 jdochert changing tank from varchar2(30) to number
7 -- 09/17/00 jdochert adding num_winners column
8 --
9
10 PROCEDURE Insert_Row(
11 x_Rowid IN OUT NOCOPY VARCHAR2,
12 x_TERR_ID IN OUT NOCOPY NUMBER,
13 x_LAST_UPDATE_DATE IN DATE,
14 x_LAST_UPDATED_BY IN NUMBER,
15 x_CREATION_DATE IN DATE,
16 x_CREATED_BY IN NUMBER,
17 x_LAST_UPDATE_LOGIN IN NUMBER,
18 x_REQUEST_ID IN NUMBER,
19 x_PROGRAM_APPLICATION_ID IN NUMBER,
20 x_PROGRAM_ID IN NUMBER,
21 x_PROGRAM_UPDATE_DATE IN DATE,
22 x_APPLICATION_SHORT_NAME IN VARCHAR2,
23 x_NAME IN VARCHAR2,
24 x_ENABLED_FLAG IN VARCHAR2,
25 x_START_DATE_ACTIVE IN DATE,
26 x_END_DATE_ACTIVE IN DATE,
27 x_PLANNED_FLAG IN VARCHAR2,
28 x_PARENT_TERRITORY_ID IN NUMBER,
29 x_TERRITORY_TYPE_ID IN NUMBER,
30 x_TEMPLATE_TERRITORY_ID IN NUMBER,
31 x_TEMPLATE_FLAG IN VARCHAR2,
32 x_ESCALATION_TERRITORY_ID IN NUMBER,
33 x_ESCALATION_TERRITORY_FLAG IN VARCHAR2,
34 x_OVERLAP_ALLOWED_FLAG IN VARCHAR2,
35 x_RANK IN NUMBER,
36 x_DESCRIPTION IN VARCHAR2,
37 x_UPDATE_FLAG IN VARCHAR2,
38 x_AUTO_ASSIGN_RESOURCES_FLAG IN VARCHAR2,
39 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
40 x_ATTRIBUTE1 IN VARCHAR2,
41 x_ATTRIBUTE2 IN VARCHAR2,
42 x_ATTRIBUTE3 IN VARCHAR2,
43 x_ATTRIBUTE4 IN VARCHAR2,
44 x_ATTRIBUTE5 IN VARCHAR2,
45 x_ATTRIBUTE6 IN VARCHAR2,
46 x_ATTRIBUTE7 IN VARCHAR2,
47 x_ATTRIBUTE8 IN VARCHAR2,
48 x_ATTRIBUTE9 IN VARCHAR2,
49 x_ATTRIBUTE10 IN VARCHAR2,
50 x_ATTRIBUTE11 IN VARCHAR2,
51 x_ATTRIBUTE12 IN VARCHAR2,
52 x_ATTRIBUTE13 IN VARCHAR2,
53 x_ATTRIBUTE14 IN VARCHAR2,
54 x_ATTRIBUTE15 IN VARCHAR2,
55 x_ORG_ID IN NUMBER,
56 x_NUM_WINNERS IN NUMBER,
57 x_NUM_QUAL IN NUMBER
58 ) IS
59 CURSOR C IS SELECT rowid FROM JTF_TERR_ALL
60 WHERE TERR_ID = x_TERR_ID;
61 CURSOR C2 IS SELECT JTF_TERR_s.nextval FROM sys.dual;
62 BEGIN
63 If (x_TERR_ID IS NULL) then
64 OPEN C2;
65 FETCH C2 INTO x_TERR_ID;
66 CLOSE C2;
67 End If;
68 INSERT INTO JTF_TERR_ALL(
69 TERR_ID,
70 LAST_UPDATE_DATE,
71 LAST_UPDATED_BY,
72 CREATION_DATE,
73 CREATED_BY,
74 LAST_UPDATE_LOGIN,
75 REQUEST_ID,
76 PROGRAM_APPLICATION_ID,
77 PROGRAM_ID,
78 PROGRAM_UPDATE_DATE,
79 APPLICATION_SHORT_NAME,
80 NAME,
81 ENABLED_FLAG,
82 START_DATE_ACTIVE,
83 END_DATE_ACTIVE,
84 PLANNED_FLAG,
85 PARENT_TERRITORY_ID,
86 TERRITORY_TYPE_ID,
87 TEMPLATE_TERRITORY_ID,
88 TEMPLATE_FLAG,
89 ESCALATION_TERRITORY_ID,
90 ESCALATION_TERRITORY_FLAG,
91 OVERLAP_ALLOWED_FLAG,
92 RANK,
93 DESCRIPTION,
94 UPDATE_FLAG,
95 AUTO_ASSIGN_RESOURCES_FLAG,
96 ATTRIBUTE_CATEGORY,
97 ATTRIBUTE1,
98 ATTRIBUTE2,
99 ATTRIBUTE3,
100 ATTRIBUTE4,
101 ATTRIBUTE5,
102 ATTRIBUTE6,
103 ATTRIBUTE7,
104 ATTRIBUTE8,
105 ATTRIBUTE9,
106 ATTRIBUTE10,
107 ATTRIBUTE11,
108 ATTRIBUTE12,
109 ATTRIBUTE13,
110 ATTRIBUTE14,
111 ATTRIBUTE15,
112 ORG_ID,
113 NUM_WINNERS,
114 NUM_QUAL
115 ) VALUES (
116 x_TERR_ID,
117 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
118 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
119 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
120 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
121 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
122 decode( x_REQUEST_ID, FND_API.G_MISS_NUM, NULL,x_REQUEST_ID),
123 decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_APPLICATION_ID),
124 decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_ID),
125 decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,x_PROGRAM_UPDATE_DATE),
126 decode( x_APPLICATION_SHORT_NAME, FND_API.G_MISS_CHAR, NULL,x_APPLICATION_SHORT_NAME),
127 decode( x_NAME, FND_API.G_MISS_CHAR, NULL,x_NAME),
128 decode( x_ENABLED_FLAG, FND_API.G_MISS_CHAR, 'Y',x_ENABLED_FLAG),
129 decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_START_DATE_ACTIVE),
130 decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_END_DATE_ACTIVE),
131 decode( x_PLANNED_FLAG, FND_API.G_MISS_CHAR, NULL,x_PLANNED_FLAG),
132 decode( x_PARENT_TERRITORY_ID, FND_API.G_MISS_NUM, NULL,x_PARENT_TERRITORY_ID),
133 decode( x_TERRITORY_TYPE_ID, FND_API.G_MISS_NUM, NULL,x_TERRITORY_TYPE_ID),
134 decode( x_TEMPLATE_TERRITORY_ID, FND_API.G_MISS_NUM, NULL,x_TEMPLATE_TERRITORY_ID),
135 decode( x_TEMPLATE_FLAG, FND_API.G_MISS_CHAR, NULL,x_TEMPLATE_FLAG),
136 decode( x_ESCALATION_TERRITORY_ID, FND_API.G_MISS_NUM, NULL,x_ESCALATION_TERRITORY_ID),
137 decode( x_ESCALATION_TERRITORY_FLAG, FND_API.G_MISS_CHAR, NULL,x_ESCALATION_TERRITORY_FLAG),
138 decode( x_OVERLAP_ALLOWED_FLAG, FND_API.G_MISS_CHAR, NULL,x_OVERLAP_ALLOWED_FLAG),
139 decode( x_RANK, FND_API.G_MISS_NUM, NULL,x_RANK),
140 decode( x_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,x_DESCRIPTION),
141 decode( x_UPDATE_FLAG, FND_API.G_MISS_CHAR, NULL,x_UPDATE_FLAG),
142 decode( x_AUTO_ASSIGN_RESOURCES_FLAG, FND_API.G_MISS_CHAR, NULL,x_AUTO_ASSIGN_RESOURCES_FLAG),
143 decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_CATEGORY),
144 decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE1),
145 decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE2),
146 decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE3),
147 decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE4),
148 decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE5),
149 decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE6),
150 decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE7),
151 decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE8),
152 decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE9),
153 decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE10),
154 decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE11),
155 decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE12),
156 decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE13),
157 decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE14),
158 decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE15),
159 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID),
160 decode( x_NUM_WINNERS, FND_API.G_MISS_NUM, NULL,x_NUM_WINNERS),
161 decode( x_NUM_QUAL, FND_API.G_MISS_NUM, NULL,x_NUM_QUAL) );
162 OPEN C;
163 FETCH C INTO x_Rowid;
164 If (C%NOTFOUND) then
165 CLOSE C;
166 RAISE NO_DATA_FOUND;
167 End If;
168 End Insert_Row;
169
170
171
172 PROCEDURE Delete_Row( x_TERR_ID IN NUMBER
173 ) IS
174 BEGIN
175 DELETE FROM JTF_TERR_ALL
176 WHERE TERR_ID = x_TERR_ID;
177 If (SQL%NOTFOUND) then
178 RAISE NO_DATA_FOUND;
179 End If;
180 END Delete_Row;
181
182
183
184 PROCEDURE Update_Row(
185 x_Rowid IN VARCHAR2,
186 x_TERR_ID IN NUMBER,
187 x_LAST_UPDATE_DATE IN DATE,
188 x_LAST_UPDATED_BY IN NUMBER,
189 x_CREATION_DATE IN DATE,
190 x_CREATED_BY IN NUMBER,
191 x_LAST_UPDATE_LOGIN IN NUMBER,
192 x_REQUEST_ID IN NUMBER,
193 x_PROGRAM_APPLICATION_ID IN NUMBER,
194 x_PROGRAM_ID IN NUMBER,
195 x_PROGRAM_UPDATE_DATE IN DATE,
196 x_APPLICATION_SHORT_NAME IN VARCHAR2,
197 x_NAME IN VARCHAR2,
198 -- x_ENABLED_FLAG IN VARCHAR2,
199 x_START_DATE_ACTIVE IN DATE,
200 x_END_DATE_ACTIVE IN DATE,
201 x_PLANNED_FLAG IN VARCHAR2,
202 x_PARENT_TERRITORY_ID IN NUMBER,
203 -- x_TERRITORY_TYPE_ID IN NUMBER,
204 x_TEMPLATE_TERRITORY_ID IN NUMBER,
205 x_TEMPLATE_FLAG IN VARCHAR2,
206 x_ESCALATION_TERRITORY_ID IN NUMBER,
207 x_ESCALATION_TERRITORY_FLAG IN VARCHAR2,
208 x_OVERLAP_ALLOWED_FLAG IN VARCHAR2,
209 x_RANK IN NUMBER,
210 x_DESCRIPTION IN VARCHAR2,
211 x_UPDATE_FLAG IN VARCHAR2,
212 x_AUTO_ASSIGN_RESOURCES_FLAG IN VARCHAR2,
213 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
214 x_ATTRIBUTE1 IN VARCHAR2,
215 x_ATTRIBUTE2 IN VARCHAR2,
216 x_ATTRIBUTE3 IN VARCHAR2,
217 x_ATTRIBUTE4 IN VARCHAR2,
218 x_ATTRIBUTE5 IN VARCHAR2,
219 x_ATTRIBUTE6 IN VARCHAR2,
220 x_ATTRIBUTE7 IN VARCHAR2,
221 x_ATTRIBUTE8 IN VARCHAR2,
222 x_ATTRIBUTE9 IN VARCHAR2,
223 x_ATTRIBUTE10 IN VARCHAR2,
224 x_ATTRIBUTE11 IN VARCHAR2,
225 x_ATTRIBUTE12 IN VARCHAR2,
226 x_ATTRIBUTE13 IN VARCHAR2,
227 x_ATTRIBUTE14 IN VARCHAR2,
228 x_ATTRIBUTE15 IN VARCHAR2,
229 x_ORG_ID IN NUMBER,
230 x_NUM_WINNERS IN NUMBER,
231 x_NUM_QUAL IN NUMBER
232 ) IS
233 BEGIN
234 Update JTF_TERR_ALL
235 SET
239 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
236 TERR_ID = decode( x_TERR_ID, FND_API.G_MISS_NUM,TERR_ID,x_TERR_ID),
237 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
238 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
240 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
241 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
242 REQUEST_ID = decode( x_REQUEST_ID, FND_API.G_MISS_NUM,REQUEST_ID,x_REQUEST_ID),
243 PROGRAM_APPLICATION_ID = decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM,PROGRAM_APPLICATION_ID,x_PROGRAM_APPLICATION_ID),
244 PROGRAM_ID = decode( x_PROGRAM_ID, FND_API.G_MISS_NUM,PROGRAM_ID,x_PROGRAM_ID),
245 PROGRAM_UPDATE_DATE = decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE,PROGRAM_UPDATE_DATE,x_PROGRAM_UPDATE_DATE),
246 APPLICATION_SHORT_NAME = decode( x_APPLICATION_SHORT_NAME, FND_API.G_MISS_CHAR,APPLICATION_SHORT_NAME,x_APPLICATION_SHORT_NAME),
247 NAME = decode( x_NAME, FND_API.G_MISS_CHAR,NAME,x_NAME),
248 --ENABLED_FLAG = decode( x_ENABLED_FLAG,FND_API.G_MISS_CHAR,ENABLED_FLAG,x_ENABLED_FLAG),
249 START_DATE_ACTIVE = decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE,START_DATE_ACTIVE,x_START_DATE_ACTIVE),
250 END_DATE_ACTIVE = decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE,END_DATE_ACTIVE,x_END_DATE_ACTIVE),
251 PLANNED_FLAG = decode( x_PLANNED_FLAG, FND_API.G_MISS_CHAR,PLANNED_FLAG,x_PLANNED_FLAG),
252 PARENT_TERRITORY_ID = decode( x_PARENT_TERRITORY_ID, FND_API.G_MISS_NUM,PARENT_TERRITORY_ID,x_PARENT_TERRITORY_ID),
253 -- TERRITORY_TYPE_ID = decode( x_TERRITORY_TYPE_ID, FND_API.G_MISS_NUM,TERRITORY_TYPE_ID,x_TERRITORY_TYPE_ID),
254 TEMPLATE_TERRITORY_ID = decode( x_TEMPLATE_TERRITORY_ID, FND_API.G_MISS_NUM,TEMPLATE_TERRITORY_ID,x_TEMPLATE_TERRITORY_ID),
255 TEMPLATE_FLAG = decode( x_TEMPLATE_FLAG, FND_API.G_MISS_CHAR,TEMPLATE_FLAG,x_TEMPLATE_FLAG),
256 ESCALATION_TERRITORY_ID = decode( x_ESCALATION_TERRITORY_ID, FND_API.G_MISS_NUM,ESCALATION_TERRITORY_ID,x_ESCALATION_TERRITORY_ID),
257 ESCALATION_TERRITORY_FLAG = decode( x_ESCALATION_TERRITORY_FLAG, FND_API.G_MISS_CHAR,ESCALATION_TERRITORY_FLAG,x_ESCALATION_TERRITORY_FLAG),
258 OVERLAP_ALLOWED_FLAG = decode( x_OVERLAP_ALLOWED_FLAG, FND_API.G_MISS_CHAR,OVERLAP_ALLOWED_FLAG,x_OVERLAP_ALLOWED_FLAG),
259 RANK = decode( x_RANK, FND_API.G_MISS_NUM,RANK,x_RANK),
260 DESCRIPTION = decode( x_DESCRIPTION, FND_API.G_MISS_CHAR,DESCRIPTION,x_DESCRIPTION),
261 UPDATE_FLAG = decode( x_UPDATE_FLAG, FND_API.G_MISS_CHAR,UPDATE_FLAG,x_UPDATE_FLAG),
262 AUTO_ASSIGN_RESOURCES_FLAG = decode( x_AUTO_ASSIGN_RESOURCES_FLAG, FND_API.G_MISS_CHAR,AUTO_ASSIGN_RESOURCES_FLAG,x_AUTO_ASSIGN_RESOURCES_FLAG),
263 ATTRIBUTE_CATEGORY = decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR,ATTRIBUTE_CATEGORY,x_ATTRIBUTE_CATEGORY),
264 ATTRIBUTE1 = decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE1,x_ATTRIBUTE1),
265 ATTRIBUTE2 = decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR,ATTRIBUTE2,x_ATTRIBUTE2),
266 ATTRIBUTE3 = decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR,ATTRIBUTE3,x_ATTRIBUTE3),
267 ATTRIBUTE4 = decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR,ATTRIBUTE4,x_ATTRIBUTE4),
268 ATTRIBUTE5 = decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR,ATTRIBUTE5,x_ATTRIBUTE5),
269 ATTRIBUTE6 = decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR,ATTRIBUTE6,x_ATTRIBUTE6),
270 ATTRIBUTE7 = decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR,ATTRIBUTE7,x_ATTRIBUTE7),
271 ATTRIBUTE8 = decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR,ATTRIBUTE8,x_ATTRIBUTE8),
272 ATTRIBUTE9 = decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR,ATTRIBUTE9,x_ATTRIBUTE9),
273 ATTRIBUTE10 = decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR,ATTRIBUTE10,x_ATTRIBUTE10),
274 ATTRIBUTE11 = decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR,ATTRIBUTE11,x_ATTRIBUTE11),
275 ATTRIBUTE12 = decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR,ATTRIBUTE12,x_ATTRIBUTE12),
276 ATTRIBUTE13 = decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR,ATTRIBUTE13,x_ATTRIBUTE13),
277 ATTRIBUTE14 = decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR,ATTRIBUTE14,x_ATTRIBUTE14),
278 ATTRIBUTE15 = decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR,ATTRIBUTE15,x_ATTRIBUTE15),
279 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM, ORG_ID, x_ORG_ID),
280 NUM_WINNERS = decode( x_NUM_WINNERS, FND_API.G_MISS_NUM, NUM_WINNERS, x_NUM_WINNERS),
281 NUM_QUAL = decode( x_NUM_QUAL, FND_API.G_MISS_NUM, NUM_QUAL, x_NUM_QUAL)
282 where terr_id = X_Terr_Id;
283
284 If (SQL%NOTFOUND) then
285 RAISE NO_DATA_FOUND;
286 End If;
287 END Update_Row;
288
289
290
291 PROCEDURE Lock_Row(
292 x_Rowid IN VARCHAR2,
293 x_TERR_ID IN NUMBER,
294 x_LAST_UPDATE_DATE IN DATE,
295 x_LAST_UPDATED_BY IN NUMBER,
296 x_CREATION_DATE IN DATE,
297 x_CREATED_BY IN NUMBER,
298 x_LAST_UPDATE_LOGIN IN NUMBER,
299 x_REQUEST_ID IN NUMBER,
300 x_PROGRAM_APPLICATION_ID IN NUMBER,
301 x_PROGRAM_ID IN NUMBER,
302 x_PROGRAM_UPDATE_DATE IN DATE,
303 x_APPLICATION_SHORT_NAME IN VARCHAR2,
304 x_NAME IN VARCHAR2,
305 x_ENABLED_FLAG IN VARCHAR2,
306 x_START_DATE_ACTIVE IN DATE,
307 x_END_DATE_ACTIVE IN DATE,
308 x_PLANNED_FLAG IN VARCHAR2,
312 x_TEMPLATE_FLAG IN VARCHAR2,
309 x_PARENT_TERRITORY_ID IN NUMBER,
310 x_TERRITORY_TYPE_ID IN NUMBER,
311 x_TEMPLATE_TERRITORY_ID IN NUMBER,
313 x_ESCALATION_TERRITORY_ID IN NUMBER,
314 x_ESCALATION_TERRITORY_FLAG IN VARCHAR2,
315 x_OVERLAP_ALLOWED_FLAG IN VARCHAR2,
316 x_RANK IN NUMBER,
317 x_DESCRIPTION IN VARCHAR2,
318 x_UPDATE_FLAG IN VARCHAR2,
319 x_AUTO_ASSIGN_RESOURCES_FLAG IN VARCHAR2,
320 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
321 x_ATTRIBUTE1 IN VARCHAR2,
322 x_ATTRIBUTE2 IN VARCHAR2,
323 x_ATTRIBUTE3 IN VARCHAR2,
324 x_ATTRIBUTE4 IN VARCHAR2,
325 x_ATTRIBUTE5 IN VARCHAR2,
326 x_ATTRIBUTE6 IN VARCHAR2,
327 x_ATTRIBUTE7 IN VARCHAR2,
328 x_ATTRIBUTE8 IN VARCHAR2,
329 x_ATTRIBUTE9 IN VARCHAR2,
330 x_ATTRIBUTE10 IN VARCHAR2,
331 x_ATTRIBUTE11 IN VARCHAR2,
332 x_ATTRIBUTE12 IN VARCHAR2,
333 x_ATTRIBUTE13 IN VARCHAR2,
334 x_ATTRIBUTE14 IN VARCHAR2,
335 x_ATTRIBUTE15 IN VARCHAR2,
336 X_ORG_ID IN NUMBER,
337 x_NUM_WINNERS IN NUMBER
338 ) IS
339 CURSOR C IS
340 SELECT *
341 FROM JTF_TERR_ALL
342 WHERE TERR_ID = x_TERR_ID
343 FOR UPDATE of TERR_ID NOWAIT;
344 Recinfo C%ROWTYPE;
345 BEGIN
346 OPEN C;
347 FETCH C INTO Recinfo;
348 If (C%NOTFOUND) then
349 CLOSE C;
350 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
351 APP_EXCEPTION.RAISE_EXCEPTION;
352 End If;
353 CLOSE C;
354 if (
355 ( ( Recinfo.TERR_ID = x_TERR_ID)
356 OR ( ( Recinfo.TERR_ID is NULL )
357 AND ( x_TERR_ID is NULL )))
358 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
359 OR ( ( Recinfo.LAST_UPDATE_DATE is NULL )
360 AND ( x_LAST_UPDATE_DATE is NULL )))
361 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
362 OR ( ( Recinfo.LAST_UPDATED_BY is NULL )
363 AND ( x_LAST_UPDATED_BY is NULL )))
364 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
365 OR ( ( Recinfo.CREATION_DATE is NULL )
366 AND ( x_CREATION_DATE is NULL )))
367 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
368 OR ( ( Recinfo.CREATED_BY is NULL )
369 AND ( x_CREATED_BY is NULL )))
370 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
371 OR ( ( Recinfo.LAST_UPDATE_LOGIN is NULL )
372 AND ( x_LAST_UPDATE_LOGIN is NULL )))
373 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
374 OR ( ( Recinfo.REQUEST_ID is NULL )
375 AND ( x_REQUEST_ID is NULL )))
376 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
377 OR ( ( Recinfo.PROGRAM_APPLICATION_ID is NULL )
378 AND ( x_PROGRAM_APPLICATION_ID is NULL )))
379 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
380 OR ( ( Recinfo.PROGRAM_ID is NULL )
381 AND ( x_PROGRAM_ID is NULL )))
382 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
383 OR ( ( Recinfo.PROGRAM_UPDATE_DATE is NULL )
384 AND ( x_PROGRAM_UPDATE_DATE is NULL )))
385 AND ( ( Recinfo.APPLICATION_SHORT_NAME = x_APPLICATION_SHORT_NAME)
386 OR ( ( Recinfo.APPLICATION_SHORT_NAME is NULL )
387 AND ( x_APPLICATION_SHORT_NAME is NULL )))
388 AND ( ( Recinfo.NAME = x_NAME)
389 OR ( ( Recinfo.NAME is NULL )
390 AND ( x_NAME is NULL )))
391 AND ( ( Recinfo.ENABLED_FLAG = x_ENABLED_FLAG)
392 OR ( ( Recinfo.ENABLED_FLAG is NULL )
393 AND ( x_ENABLED_FLAG is NULL )))
394 AND ( ( Recinfo.START_DATE_ACTIVE = x_START_DATE_ACTIVE)
395 OR ( ( Recinfo.START_DATE_ACTIVE is NULL )
396 AND ( x_START_DATE_ACTIVE is NULL )))
397 AND ( ( Recinfo.END_DATE_ACTIVE = x_END_DATE_ACTIVE)
398 OR ( ( Recinfo.END_DATE_ACTIVE is NULL )
399 AND ( x_END_DATE_ACTIVE is NULL )))
400 AND ( ( Recinfo.PLANNED_FLAG = x_PLANNED_FLAG)
401 OR ( ( Recinfo.PLANNED_FLAG is NULL )
402 AND ( x_PLANNED_FLAG is NULL )))
403 AND ( ( Recinfo.PARENT_TERRITORY_ID = x_PARENT_TERRITORY_ID)
404 OR ( ( Recinfo.PARENT_TERRITORY_ID is NULL )
405 AND ( x_PARENT_TERRITORY_ID is NULL )))
406 AND ( ( Recinfo.TERRITORY_TYPE_ID = x_TERRITORY_TYPE_ID)
407 OR ( ( Recinfo.TERRITORY_TYPE_ID is NULL )
408 AND ( x_TERRITORY_TYPE_ID is NULL )))
409 AND ( ( Recinfo.TEMPLATE_TERRITORY_ID = x_TEMPLATE_TERRITORY_ID)
410 OR ( ( Recinfo.TEMPLATE_TERRITORY_ID is NULL )
411 AND ( x_TEMPLATE_TERRITORY_ID is NULL )))
412 AND ( ( Recinfo.TEMPLATE_FLAG = x_TEMPLATE_FLAG)
413 OR ( ( Recinfo.TEMPLATE_FLAG is NULL )
414 AND ( x_TEMPLATE_FLAG is NULL )))
415 AND ( ( Recinfo.ESCALATION_TERRITORY_ID = x_ESCALATION_TERRITORY_ID)
416 OR ( ( Recinfo.ESCALATION_TERRITORY_ID is NULL )
417 AND ( x_ESCALATION_TERRITORY_ID is NULL )))
418 AND ( ( Recinfo.ESCALATION_TERRITORY_FLAG = x_ESCALATION_TERRITORY_FLAG)
419 OR ( ( Recinfo.ESCALATION_TERRITORY_FLAG is NULL )
420 AND ( x_ESCALATION_TERRITORY_FLAG is NULL )))
421 AND ( ( Recinfo.OVERLAP_ALLOWED_FLAG = x_OVERLAP_ALLOWED_FLAG)
422 OR ( ( Recinfo.OVERLAP_ALLOWED_FLAG is NULL )
423 AND ( x_OVERLAP_ALLOWED_FLAG is NULL )))
424 AND ( ( Recinfo.RANK = x_RANK)
425 OR ( ( Recinfo.RANK is NULL )
426 AND ( x_RANK is NULL )))
427 AND ( ( Recinfo.DESCRIPTION = x_DESCRIPTION)
428 OR ( ( Recinfo.DESCRIPTION is NULL )
429 AND ( x_DESCRIPTION is NULL )))
430 AND ( ( Recinfo.UPDATE_FLAG = x_UPDATE_FLAG)
431 OR ( ( Recinfo.UPDATE_FLAG is NULL )
432 AND ( x_UPDATE_FLAG is NULL )))
433 AND ( ( Recinfo.AUTO_ASSIGN_RESOURCES_FLAG = x_AUTO_ASSIGN_RESOURCES_FLAG)
434 OR ( ( Recinfo.AUTO_ASSIGN_RESOURCES_FLAG is NULL )
435 AND ( x_AUTO_ASSIGN_RESOURCES_FLAG is NULL )))
436 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY)
437 OR ( ( Recinfo.ATTRIBUTE_CATEGORY is NULL )
438 AND ( x_ATTRIBUTE_CATEGORY is NULL )))
439 AND ( ( Recinfo.ATTRIBUTE1 = x_ATTRIBUTE1)
440 OR ( ( Recinfo.ATTRIBUTE1 is NULL )
441 AND ( x_ATTRIBUTE1 is NULL )))
442 AND ( ( Recinfo.ATTRIBUTE2 = x_ATTRIBUTE2)
443 OR ( ( Recinfo.ATTRIBUTE2 is NULL )
444 AND ( x_ATTRIBUTE2 is NULL )))
445 AND ( ( Recinfo.ATTRIBUTE3 = x_ATTRIBUTE3)
446 OR ( ( Recinfo.ATTRIBUTE3 is NULL )
447 AND ( x_ATTRIBUTE3 is NULL )))
448 AND ( ( Recinfo.ATTRIBUTE4 = x_ATTRIBUTE4)
449 OR ( ( Recinfo.ATTRIBUTE4 is NULL )
450 AND ( x_ATTRIBUTE4 is NULL )))
451 AND ( ( Recinfo.ATTRIBUTE5 = x_ATTRIBUTE5)
452 OR ( ( Recinfo.ATTRIBUTE5 is NULL )
453 AND ( x_ATTRIBUTE5 is NULL )))
454 AND ( ( Recinfo.ATTRIBUTE6 = x_ATTRIBUTE6)
455 OR ( ( Recinfo.ATTRIBUTE6 is NULL )
456 AND ( x_ATTRIBUTE6 is NULL )))
457 AND ( ( Recinfo.ATTRIBUTE7 = x_ATTRIBUTE7)
458 OR ( ( Recinfo.ATTRIBUTE7 is NULL )
459 AND ( x_ATTRIBUTE7 is NULL )))
460 AND ( ( Recinfo.ATTRIBUTE8 = x_ATTRIBUTE8)
461 OR ( ( Recinfo.ATTRIBUTE8 is NULL )
462 AND ( x_ATTRIBUTE8 is NULL )))
463 AND ( ( Recinfo.ATTRIBUTE9 = x_ATTRIBUTE9)
464 OR ( ( Recinfo.ATTRIBUTE9 is NULL )
465 AND ( x_ATTRIBUTE9 is NULL )))
466 AND ( ( Recinfo.ATTRIBUTE10 = x_ATTRIBUTE10)
467 OR ( ( Recinfo.ATTRIBUTE10 is NULL )
468 AND ( x_ATTRIBUTE10 is NULL )))
469 AND ( ( Recinfo.ATTRIBUTE11 = x_ATTRIBUTE11)
470 OR ( ( Recinfo.ATTRIBUTE11 is NULL )
471 AND ( x_ATTRIBUTE11 is NULL )))
472 AND ( ( Recinfo.ATTRIBUTE12 = x_ATTRIBUTE12)
473 OR ( ( Recinfo.ATTRIBUTE12 is NULL )
474 AND ( x_ATTRIBUTE12 is NULL )))
475 AND ( ( Recinfo.ATTRIBUTE13 = x_ATTRIBUTE13)
476 OR ( ( Recinfo.ATTRIBUTE13 is NULL )
477 AND ( x_ATTRIBUTE13 is NULL )))
478 AND ( ( Recinfo.ATTRIBUTE14 = x_ATTRIBUTE14)
479 OR ( ( Recinfo.ATTRIBUTE14 is NULL )
480 AND ( x_ATTRIBUTE14 is NULL )))
481 AND ( ( Recinfo.ATTRIBUTE15 = x_ATTRIBUTE15)
482 OR ( ( Recinfo.ATTRIBUTE15 is NULL )
483 AND ( x_ATTRIBUTE15 is NULL )))
484 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
485 OR ( ( Recinfo.ORG_ID is NULL )
486 AND ( x_ORG_ID is NULL )))
487 AND ( ( Recinfo.NUM_WINNERS = x_NUM_WINNERS)
488 OR ( ( Recinfo.NUM_WINNERS is NULL )
489 AND ( x_NUM_WINNERS is NULL )))
490 ) then
491 return;
492 else
493 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
494 APP_EXCEPTION.RAISE_EXCEPTION;
495 End If;
496 END Lock_Row;
497
498 END JTF_TERR_PKG;
499