[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_CNR_GROUPS_PKG
Source
1 PACKAGE BODY JTF_TERR_CNR_GROUPS_PKG AS
2 /* $Header: jtfvtcgb.pls 120.0 2005/06/02 18:22:38 appldev ship $ */
3
4 -- 01/23/01 Amit Patel - Created package body for JTF_TERR_CNR_GROUPS_PKG
5
6 PROCEDURE Insert_Row(
7 x_Rowid IN OUT NOCOPY VARCHAR2,
8 x_CNR_GROUP_ID IN OUT NOCOPY NUMBER,
9 x_LAST_UPDATED_BY IN NUMBER,
10 x_LAST_UPDATE_DATE IN DATE,
11 x_CREATED_BY IN NUMBER,
12 x_CREATION_DATE IN DATE,
13 x_LAST_UPDATE_LOGIN IN NUMBER,
14 x_NAME IN VARCHAR2,
15 x_DESCRIPTION IN VARCHAR2,
16 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
17 x_ATTRIBUTE1 IN VARCHAR2,
18 x_ATTRIBUTE2 IN VARCHAR2,
19 x_ATTRIBUTE3 IN VARCHAR2,
20 x_ATTRIBUTE4 IN VARCHAR2,
21 x_ATTRIBUTE5 IN VARCHAR2,
22 x_ATTRIBUTE6 IN VARCHAR2,
23 x_ATTRIBUTE7 IN VARCHAR2,
24 x_ATTRIBUTE8 IN VARCHAR2,
25 x_ATTRIBUTE9 IN VARCHAR2,
26 x_ATTRIBUTE10 IN VARCHAR2,
27 x_ATTRIBUTE11 IN VARCHAR2,
28 x_ATTRIBUTE12 IN VARCHAR2,
29 x_ATTRIBUTE13 IN VARCHAR2,
30 x_ATTRIBUTE14 IN VARCHAR2,
31 x_ATTRIBUTE15 IN VARCHAR2
32 )IS
33 CURSOR C IS SELECT rowid FROM JTF_TERR_CNR_GROUPS
34 WHERE CNR_GROUP_ID = x_CNR_GROUP_ID;
35 CURSOR C2 IS SELECT JTF_TERR_CNR_GROUPS_s.nextval FROM sys.dual;
36 BEGIN
37 If (x_CNR_GROUP_ID IS NULL) then
38 OPEN C2;
39 FETCH C2 INTO x_CNR_GROUP_ID;
40 CLOSE C2;
41 End If;
42 INSERT INTO JTF_TERR_CNR_GROUPS(
43 CNR_GROUP_ID,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATE_LOGIN,
49 NAME,
50 DESCRIPTION,
51 ATTRIBUTE_CATEGORY,
52 ATTRIBUTE1,
53 ATTRIBUTE2,
54 ATTRIBUTE3,
55 ATTRIBUTE4,
56 ATTRIBUTE5,
57 ATTRIBUTE6,
58 ATTRIBUTE7,
59 ATTRIBUTE8,
60 ATTRIBUTE9,
61 ATTRIBUTE10,
62 ATTRIBUTE11,
63 ATTRIBUTE12,
64 ATTRIBUTE13,
65 ATTRIBUTE14,
66 ATTRIBUTE15
67 ) VALUES (
68 x_CNR_GROUP_ID,
69 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
70 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
71 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
72 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
73 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
74 decode( x_NAME, FND_API.G_MISS_CHAR, NULL,x_NAME),
75 decode( x_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,x_DESCRIPTION),
76 decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_CATEGORY),
77 decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE1),
78 decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE2),
79 decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE3),
80 decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE4),
81 decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE5),
82 decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE6),
83 decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE7),
84 decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE8),
85 decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE9),
86 decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE10),
87 decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE11),
88 decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE12),
89 decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE13),
90 decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE14),
91 decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE15) );
92 OPEN C;
93 FETCH C INTO x_Rowid;
94 If (C%NOTFOUND) then
95 CLOSE C;
96 RAISE NO_DATA_FOUND;
97 End If;
98 End Insert_Row;
99
100
101
102 PROCEDURE Delete_Row( x_CNR_GROUP_ID IN NUMBER
103 ) IS
104 BEGIN
105 DELETE FROM JTF_TERR_CNR_GROUPS
106 WHERE CNR_GROUP_ID = x_CNR_GROUP_ID;
107 If (SQL%NOTFOUND) then
108 RAISE NO_DATA_FOUND;
109 End If;
110 END Delete_Row;
111
112
113
114 PROCEDURE Update_Row(
115 x_Rowid IN OUT NOCOPY VARCHAR2,
116 x_CNR_GROUP_ID IN OUT NOCOPY NUMBER,
117 x_LAST_UPDATED_BY IN NUMBER,
118 x_LAST_UPDATE_DATE IN DATE,
119 x_CREATED_BY IN NUMBER,
120 x_CREATION_DATE IN DATE,
121 x_LAST_UPDATE_LOGIN IN NUMBER,
122 x_NAME IN VARCHAR2,
123 x_DESCRIPTION IN VARCHAR2,
124 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
125 x_ATTRIBUTE1 IN VARCHAR2,
126 x_ATTRIBUTE2 IN VARCHAR2,
127 x_ATTRIBUTE3 IN VARCHAR2,
128 x_ATTRIBUTE4 IN VARCHAR2,
129 x_ATTRIBUTE5 IN VARCHAR2,
130 x_ATTRIBUTE6 IN VARCHAR2,
131 x_ATTRIBUTE7 IN VARCHAR2,
132 x_ATTRIBUTE8 IN VARCHAR2,
133 x_ATTRIBUTE9 IN VARCHAR2,
134 x_ATTRIBUTE10 IN VARCHAR2,
135 x_ATTRIBUTE11 IN VARCHAR2,
136 x_ATTRIBUTE12 IN VARCHAR2,
137 x_ATTRIBUTE13 IN VARCHAR2,
138 x_ATTRIBUTE14 IN VARCHAR2,
139 x_ATTRIBUTE15 IN VARCHAR2
140 ) IS
141 BEGIN
142 Update JTF_TERR_CNR_GROUPS
143 SET
144 CNR_GROUP_ID = decode( x_CNR_GROUP_ID, FND_API.G_MISS_NUM,CNR_GROUP_ID,x_CNR_GROUP_ID),
145 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
146 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
147 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
148 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
149 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
150 NAME = decode( x_NAME, FND_API.G_MISS_CHAR,NAME,x_NAME),
151 DESCRIPTION = decode( x_DESCRIPTION, FND_API.G_MISS_CHAR,DESCRIPTION,x_DESCRIPTION),
152 ATTRIBUTE_CATEGORY = decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR,ATTRIBUTE_CATEGORY,x_ATTRIBUTE_CATEGORY),
153 ATTRIBUTE1 = decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE1,x_ATTRIBUTE1),
154 ATTRIBUTE2 = decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR,ATTRIBUTE2,x_ATTRIBUTE2),
155 ATTRIBUTE3 = decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR,ATTRIBUTE3,x_ATTRIBUTE3),
156 ATTRIBUTE4 = decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR,ATTRIBUTE4,x_ATTRIBUTE4),
157 ATTRIBUTE5 = decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR,ATTRIBUTE5,x_ATTRIBUTE5),
158 ATTRIBUTE6 = decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR,ATTRIBUTE6,x_ATTRIBUTE6),
159 ATTRIBUTE7 = decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR,ATTRIBUTE7,x_ATTRIBUTE7),
160 ATTRIBUTE8 = decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR,ATTRIBUTE8,x_ATTRIBUTE8),
161 ATTRIBUTE9 = decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR,ATTRIBUTE9,x_ATTRIBUTE9),
162 ATTRIBUTE10 = decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR,ATTRIBUTE10,x_ATTRIBUTE10),
163 ATTRIBUTE11 = decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR,ATTRIBUTE11,x_ATTRIBUTE11),
164 ATTRIBUTE12 = decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR,ATTRIBUTE12,x_ATTRIBUTE12),
165 ATTRIBUTE13 = decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR,ATTRIBUTE13,x_ATTRIBUTE13),
166 ATTRIBUTE14 = decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR,ATTRIBUTE14,x_ATTRIBUTE14),
167 ATTRIBUTE15 = decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR,ATTRIBUTE15,x_ATTRIBUTE15)
168 where cnr_group_id = X_cnr_group_id;
169
170 If (SQL%NOTFOUND) then
171 RAISE NO_DATA_FOUND;
172 End If;
173 END Update_Row;
174
175
176
177 PROCEDURE Lock_Row(
178 x_Rowid IN OUT NOCOPY VARCHAR2,
179 x_CNR_GROUP_ID IN OUT NOCOPY NUMBER,
180 x_LAST_UPDATED_BY IN NUMBER,
181 x_LAST_UPDATE_DATE IN DATE,
182 x_CREATED_BY IN NUMBER,
183 x_CREATION_DATE IN DATE,
184 x_LAST_UPDATE_LOGIN IN NUMBER,
185 x_NAME IN VARCHAR2,
186 x_DESCRIPTION IN VARCHAR2,
187 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
188 x_ATTRIBUTE1 IN VARCHAR2,
189 x_ATTRIBUTE2 IN VARCHAR2,
190 x_ATTRIBUTE3 IN VARCHAR2,
191 x_ATTRIBUTE4 IN VARCHAR2,
192 x_ATTRIBUTE5 IN VARCHAR2,
193 x_ATTRIBUTE6 IN VARCHAR2,
194 x_ATTRIBUTE7 IN VARCHAR2,
195 x_ATTRIBUTE8 IN VARCHAR2,
196 x_ATTRIBUTE9 IN VARCHAR2,
197 x_ATTRIBUTE10 IN VARCHAR2,
198 x_ATTRIBUTE11 IN VARCHAR2,
199 x_ATTRIBUTE12 IN VARCHAR2,
200 x_ATTRIBUTE13 IN VARCHAR2,
201 x_ATTRIBUTE14 IN VARCHAR2,
202 x_ATTRIBUTE15 IN VARCHAR2
203 ) IS
204 CURSOR C IS
205 SELECT *
206 FROM JTF_TERR_CNR_GROUPS
207 WHERE CNR_GROUP_ID = x_CNR_GROUP_ID
208 FOR UPDATE of CNR_GROUP_ID NOWAIT;
209 Recinfo C%ROWTYPE;
210 BEGIN
211 OPEN C;
212 FETCH C INTO Recinfo;
213 If (C%NOTFOUND) then
214 CLOSE C;
215 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
216 APP_EXCEPTION.RAISE_EXCEPTION;
217 End If;
218 CLOSE C;
219 if (
220 ( ( Recinfo.CNR_GROUP_ID = x_CNR_GROUP_ID)
221 OR ( ( Recinfo.CNR_GROUP_ID is NULL )
222 AND ( x_CNR_GROUP_ID is NULL )))
223 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
224 OR ( ( Recinfo.LAST_UPDATE_DATE is NULL )
225 AND ( x_LAST_UPDATE_DATE is NULL )))
226 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
227 OR ( ( Recinfo.LAST_UPDATED_BY is NULL )
228 AND ( x_LAST_UPDATED_BY is NULL )))
229 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
230 OR ( ( Recinfo.CREATION_DATE is NULL )
231 AND ( x_CREATION_DATE is NULL )))
232 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
233 OR ( ( Recinfo.CREATED_BY is NULL )
234 AND ( x_CREATED_BY is NULL )))
235 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
236 OR ( ( Recinfo.LAST_UPDATE_LOGIN is NULL )
237 AND ( x_LAST_UPDATE_LOGIN is NULL )))
238 AND ( ( Recinfo.NAME = x_NAME)
239 OR ( ( Recinfo.NAME is NULL )
240 AND ( x_NAME is NULL )))
241 AND ( ( Recinfo.DESCRIPTION = x_DESCRIPTION)
242 OR ( ( Recinfo.DESCRIPTION is NULL )
243 AND ( x_DESCRIPTION is NULL )))
244 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY)
245 OR ( ( Recinfo.ATTRIBUTE_CATEGORY is NULL )
246 AND ( x_ATTRIBUTE_CATEGORY is NULL )))
247 AND ( ( Recinfo.ATTRIBUTE1 = x_ATTRIBUTE1)
248 OR ( ( Recinfo.ATTRIBUTE1 is NULL )
249 AND ( x_ATTRIBUTE1 is NULL )))
250 AND ( ( Recinfo.ATTRIBUTE2 = x_ATTRIBUTE2)
251 OR ( ( Recinfo.ATTRIBUTE2 is NULL )
252 AND ( x_ATTRIBUTE2 is NULL )))
253 AND ( ( Recinfo.ATTRIBUTE3 = x_ATTRIBUTE3)
254 OR ( ( Recinfo.ATTRIBUTE3 is NULL )
255 AND ( x_ATTRIBUTE3 is NULL )))
256 AND ( ( Recinfo.ATTRIBUTE4 = x_ATTRIBUTE4)
257 OR ( ( Recinfo.ATTRIBUTE4 is NULL )
258 AND ( x_ATTRIBUTE4 is NULL )))
259 AND ( ( Recinfo.ATTRIBUTE5 = x_ATTRIBUTE5)
260 OR ( ( Recinfo.ATTRIBUTE5 is NULL )
261 AND ( x_ATTRIBUTE5 is NULL )))
262 AND ( ( Recinfo.ATTRIBUTE6 = x_ATTRIBUTE6)
263 OR ( ( Recinfo.ATTRIBUTE6 is NULL )
264 AND ( x_ATTRIBUTE6 is NULL )))
265 AND ( ( Recinfo.ATTRIBUTE7 = x_ATTRIBUTE7)
266 OR ( ( Recinfo.ATTRIBUTE7 is NULL )
267 AND ( x_ATTRIBUTE7 is NULL )))
268 AND ( ( Recinfo.ATTRIBUTE8 = x_ATTRIBUTE8)
269 OR ( ( Recinfo.ATTRIBUTE8 is NULL )
270 AND ( x_ATTRIBUTE8 is NULL )))
271 AND ( ( Recinfo.ATTRIBUTE9 = x_ATTRIBUTE9)
272 OR ( ( Recinfo.ATTRIBUTE9 is NULL )
273 AND ( x_ATTRIBUTE9 is NULL )))
274 AND ( ( Recinfo.ATTRIBUTE10 = x_ATTRIBUTE10)
275 OR ( ( Recinfo.ATTRIBUTE10 is NULL )
276 AND ( x_ATTRIBUTE10 is NULL )))
277 AND ( ( Recinfo.ATTRIBUTE11 = x_ATTRIBUTE11)
278 OR ( ( Recinfo.ATTRIBUTE11 is NULL )
279 AND ( x_ATTRIBUTE11 is NULL )))
280 AND ( ( Recinfo.ATTRIBUTE12 = x_ATTRIBUTE12)
281 OR ( ( Recinfo.ATTRIBUTE12 is NULL )
282 AND ( x_ATTRIBUTE12 is NULL )))
283 AND ( ( Recinfo.ATTRIBUTE13 = x_ATTRIBUTE13)
284 OR ( ( Recinfo.ATTRIBUTE13 is NULL )
285 AND ( x_ATTRIBUTE13 is NULL )))
286 AND ( ( Recinfo.ATTRIBUTE14 = x_ATTRIBUTE14)
287 OR ( ( Recinfo.ATTRIBUTE14 is NULL )
288 AND ( x_ATTRIBUTE14 is NULL )))
289 AND ( ( Recinfo.ATTRIBUTE15 = x_ATTRIBUTE15)
290 OR ( ( Recinfo.ATTRIBUTE15 is NULL )
291 AND ( x_ATTRIBUTE15 is NULL )))
292 ) then
293 return;
294 else
295 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
296 APP_EXCEPTION.RAISE_EXCEPTION;
297 End If;
298 END Lock_Row;
299
300 END JTF_TERR_CNR_GROUPS_PKG;