[Home] [Help]
PACKAGE BODY: APPS.JTF_AM_TERR_QUALIFIERS_PKG
Source
1 package body JTF_AM_TERR_QUALIFIERS_PKG as
2 /* $Header: jtfamttb.pls 115.1 2003/04/23 23:08:28 sroychou noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TERR_QUALIFIER_ID in NUMBER,
6 X_QUAL_USG_ID in NUMBER,
7 X_QUAL_ATTRIBUTE_NAME in VARCHAR,
8 X_ATTRIBUTE1 in VARCHAR2,
9 X_ATTRIBUTE2 in VARCHAR2,
10 X_ATTRIBUTE3 in VARCHAR2,
11 X_ATTRIBUTE4 in VARCHAR2,
12 X_ATTRIBUTE5 in VARCHAR2,
13 X_ATTRIBUTE6 in VARCHAR2,
14 X_ATTRIBUTE7 in VARCHAR2,
15 X_ATTRIBUTE8 in VARCHAR2,
16 X_ATTRIBUTE9 in VARCHAR2,
17 X_ATTRIBUTE10 in VARCHAR2,
18 X_ATTRIBUTE11 in VARCHAR2,
19 X_ATTRIBUTE12 in VARCHAR2,
20 X_ATTRIBUTE13 in VARCHAR2,
21 X_ATTRIBUTE14 in VARCHAR2,
22 X_ATTRIBUTE15 in VARCHAR2,
23 X_ATTRIBUTE_CATEGORY in VARCHAR2,
24 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_DATE in DATE,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER,
29 X_SECURITY_GROUP_ID in NUMBER)
30 IS
31
32 cursor C is select ROWID from JTF_AM_TERR_QUALIFIERS
33 where TERR_QUALIFIER_ID = X_TERR_QUALIFIER_ID
34 ;
35
36 begin
37 insert into JTF_AM_TERR_QUALIFIERS (
38 TERR_QUALIFIER_ID,
39 QUAL_USG_ID ,
40 QUAL_ATTRIBUTE_NAME ,
41 ATTRIBUTE1 ,
42 ATTRIBUTE2 ,
43 ATTRIBUTE3 ,
44 ATTRIBUTE4 ,
45 ATTRIBUTE5 ,
46 ATTRIBUTE6 ,
47 ATTRIBUTE7 ,
48 ATTRIBUTE8 ,
49 ATTRIBUTE9 ,
50 ATTRIBUTE10,
51 ATTRIBUTE11,
52 ATTRIBUTE12,
53 ATTRIBUTE13,
54 ATTRIBUTE14,
55 ATTRIBUTE15,
56 ATTRIBUTE_CATEGORY ,
57 CREATION_DATE ,
58 CREATED_BY ,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY ,
61 LAST_UPDATE_LOGIN,
62 OBJECT_VERSION_NUMBER,
63 SECURITY_GROUP_ID
64 ) values (
65 X_TERR_QUALIFIER_ID ,
66 X_QUAL_USG_ID ,
67 X_QUAL_ATTRIBUTE_NAME,
68 X_ATTRIBUTE1 ,
69 X_ATTRIBUTE2 ,
70 X_ATTRIBUTE3 ,
71 X_ATTRIBUTE4 ,
72 X_ATTRIBUTE5 ,
73 X_ATTRIBUTE6 ,
74 X_ATTRIBUTE7 ,
75 X_ATTRIBUTE8 ,
76 X_ATTRIBUTE9 ,
77 X_ATTRIBUTE10,
78 X_ATTRIBUTE11,
79 X_ATTRIBUTE12,
80 X_ATTRIBUTE13,
81 X_ATTRIBUTE14,
82 X_ATTRIBUTE15,
83 X_ATTRIBUTE_CATEGORY ,
84 X_CREATION_DATE ,
85 X_CREATED_BY ,
86 X_LAST_UPDATE_DATE ,
87 X_LAST_UPDATED_BY ,
88 X_LAST_UPDATE_LOGIN ,
89 1,
90 X_SECURITY_GROUP_ID
91 );
92
93 open c;
94 fetch c into X_ROWID;
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99 close c;
100
101 end INSERT_ROW;
102
103
104 procedure LOCK_ROW (
105 X_TERR_QUALIFIER_ID in NUMBER,
106 X_OBJECT_VERSION_NUMBER in NUMBER
107 ) is
108 cursor c1 is select
109 OBJECT_VERSION_NUMBER
110 from JTF_AM_TERR_QUALIFIERS
111 where TERR_QUALIFIER_ID = X_TERR_QUALIFIER_ID
112 for update of TERR_QUALIFIER_ID nowait;
113 tlinfo c1%rowtype ;
114 begin
115 open c1;
116 fetch c1 into tlinfo;
117 if (c1%notfound) then
118 close c1;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c1;
123
124 if (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
125 then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131
132 end LOCK_ROW;
133
134 procedure UPDATE_ROW (
135 X_TERR_QUALIFIER_ID in NUMBER,
136 X_QUAL_USG_ID in NUMBER,
137 X_QUAL_ATTRIBUTE_NAME in VARCHAR,
138 X_ATTRIBUTE1 in VARCHAR2,
139 X_ATTRIBUTE2 in VARCHAR2,
140 X_ATTRIBUTE3 in VARCHAR2,
141 X_ATTRIBUTE4 in VARCHAR2,
142 X_ATTRIBUTE5 in VARCHAR2,
143 X_ATTRIBUTE6 in VARCHAR2,
144 X_ATTRIBUTE7 in VARCHAR2,
145 X_ATTRIBUTE8 in VARCHAR2,
146 X_ATTRIBUTE9 in VARCHAR2,
147 X_ATTRIBUTE10 in VARCHAR2,
148 X_ATTRIBUTE11 in VARCHAR2,
149 X_ATTRIBUTE12 in VARCHAR2,
150 X_ATTRIBUTE13 in VARCHAR2,
151 X_ATTRIBUTE14 in VARCHAR2,
152 X_ATTRIBUTE15 in VARCHAR2,
153 X_ATTRIBUTE_CATEGORY in VARCHAR2,
154 X_LAST_UPDATE_DATE in DATE,
155 X_LAST_UPDATED_BY in NUMBER,
156 X_LAST_UPDATE_LOGIN in NUMBER,
157 X_OBJECT_VERSION_NUMBER in NUMBER,
158 X_SECURITY_GROUP_ID in NUMBER
159 ) is
160 begin
161 update JTF_AM_TERR_QUALIFIERS set
162 ATTRIBUTE9 = X_ATTRIBUTE9,
163 ATTRIBUTE10 = X_ATTRIBUTE10,
164 ATTRIBUTE11 = X_ATTRIBUTE11,
165 ATTRIBUTE12 = X_ATTRIBUTE12,
166 ATTRIBUTE13 = X_ATTRIBUTE13,
167 ATTRIBUTE14 = X_ATTRIBUTE14,
168 ATTRIBUTE15 = X_ATTRIBUTE15,
169 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
170 TERR_QUALIFIER_ID = X_TERR_QUALIFIER_ID,
171 QUAL_USG_ID = X_QUAL_USG_ID,
172 QUAL_ATTRIBUTE_NAME = X_QUAL_ATTRIBUTE_NAME,
173 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
174 ATTRIBUTE2 = X_ATTRIBUTE2,
175 ATTRIBUTE3 = X_ATTRIBUTE3,
176 ATTRIBUTE4 = X_ATTRIBUTE4,
177 ATTRIBUTE5 = X_ATTRIBUTE5,
178 ATTRIBUTE6 = X_ATTRIBUTE6,
179 ATTRIBUTE7 = X_ATTRIBUTE7,
180 ATTRIBUTE8 = X_ATTRIBUTE8,
181 ATTRIBUTE1 = X_ATTRIBUTE1,
182 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
183 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
184 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
185 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
186 where TERR_QUALIFIER_ID = X_TERR_QUALIFIER_ID;
187
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191 end UPDATE_ROW;
192
193 procedure LOAD_ROW (
194 X_TERR_QUALIFIER_ID in NUMBER,
195 X_OWNER in VARCHAR2,
196 X_QUAL_USG_ID in NUMBER,
197 X_QUAL_ATTRIBUTE_NAME in VARCHAR2,
198 X_ATTRIBUTE1 in VARCHAR2,
199 X_ATTRIBUTE2 in VARCHAR2,
200 X_ATTRIBUTE3 in VARCHAR2,
201 X_ATTRIBUTE4 in VARCHAR2,
202 X_ATTRIBUTE5 in VARCHAR2,
203 X_ATTRIBUTE6 in VARCHAR2,
204 X_ATTRIBUTE7 in VARCHAR2,
205 X_ATTRIBUTE8 in VARCHAR2,
206 X_ATTRIBUTE9 in VARCHAR2,
207 X_ATTRIBUTE10 in VARCHAR2,
208 X_ATTRIBUTE11 in VARCHAR2,
209 X_ATTRIBUTE12 in VARCHAR2,
210 X_ATTRIBUTE13 in VARCHAR2,
211 X_ATTRIBUTE14 in VARCHAR2,
212 X_ATTRIBUTE15 in VARCHAR2,
213 X_ATTRIBUTE_CATEGORY in VARCHAR2
214 )
215 IS
216
217 l_row_id rowid;
218 l_user_id number;
219 l_last_updated_by number := -1;
220 l_object_version_number number := 1;
221
222 CURSOR c_last_updated IS
223 SELECT last_updated_by,
224 object_version_number
225 from JTF_AM_TERR_QUALIFIERS
226 WHERE terr_qualifier_id = x_terr_qualifier_id;
227
228 begin
229 if (X_OWNER = 'SEED') then
230 l_user_id := 1;
231 else
232 l_user_id := 0;
233 end if;
234
235 OPEN c_last_updated;
236 FETCH c_last_updated into l_last_updated_by, l_object_version_number ;
237 IF c_last_updated%NOTFOUND THEN
238 l_object_version_number := 1;
239 jtf_am_terr_qualifiers_pkg.insert_row(
240 X_ROWID => l_row_id ,
241 X_TERR_QUALIFIER_ID => X_TERR_QUALIFIER_ID,
242 X_ATTRIBUTE1 => X_ATTRIBUTE1 ,
243 X_ATTRIBUTE2 => X_ATTRIBUTE2 ,
244 X_ATTRIBUTE3 => X_ATTRIBUTE3 ,
245 X_ATTRIBUTE4 => X_ATTRIBUTE4 ,
246 X_ATTRIBUTE5 => X_ATTRIBUTE5 ,
247 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
248 X_ATTRIBUTE7 => X_ATTRIBUTE7 ,
249 X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
250 X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
251 X_ATTRIBUTE10 => X_ATTRIBUTE10 ,
252 X_ATTRIBUTE11 => X_ATTRIBUTE11 ,
253 X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
254 X_ATTRIBUTE13 => X_ATTRIBUTE13 ,
255 X_ATTRIBUTE14 => X_ATTRIBUTE14 ,
256 X_ATTRIBUTE15 => X_ATTRIBUTE15 ,
257 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY ,
258 X_QUAL_USG_ID => X_QUAL_USG_ID,
259 X_QUAL_ATTRIBUTE_NAME => X_QUAL_ATTRIBUTE_NAME,
260 X_CREATION_DATE => sysdate ,
261 X_CREATED_BY => l_user_id ,
262 X_LAST_UPDATE_DATE => sysdate ,
263 X_LAST_UPDATED_BY => l_user_id ,
264 X_SECURITY_GROUP_ID => null ,
265 X_LAST_UPDATE_LOGIN => 0 );
266
267
268
269 ELSIF c_last_updated%FOUND THEN
270
271 IF l_last_updated_by IN (1,0) THEN
272 l_object_version_number := l_object_version_number + 1;
273
274 jtf_am_terr_qualifiers_pkg.update_row(
275 X_TERR_QUALIFIER_ID => X_TERR_QUALIFIER_ID,
276 X_ATTRIBUTE1 => X_ATTRIBUTE1 ,
277 X_ATTRIBUTE2 => X_ATTRIBUTE2 ,
278 X_ATTRIBUTE3 => X_ATTRIBUTE3 ,
279 X_ATTRIBUTE4 => X_ATTRIBUTE4 ,
280 X_ATTRIBUTE5 => X_ATTRIBUTE5 ,
281 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
282 X_ATTRIBUTE7 => X_ATTRIBUTE7 ,
283 X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
284 X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
285 X_ATTRIBUTE10 => X_ATTRIBUTE10 ,
286 X_ATTRIBUTE11 => X_ATTRIBUTE11 ,
287 X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
288 X_ATTRIBUTE13 => X_ATTRIBUTE13 ,
289 X_ATTRIBUTE14 => X_ATTRIBUTE14 ,
290 X_ATTRIBUTE15 => X_ATTRIBUTE15 ,
291 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY ,
292 X_QUAL_USG_ID => X_QUAL_USG_ID,
293 X_QUAL_ATTRIBUTE_NAME => X_QUAL_ATTRIBUTE_NAME,
294 X_OBJECT_VERSION_NUMBER => l_object_version_number,
295 X_SECURITY_GROUP_ID => null ,
296 X_LAST_UPDATE_DATE => sysdate ,
297 X_LAST_UPDATED_BY => l_user_id ,
298 X_LAST_UPDATE_LOGIN => 0 );
299
300 END IF;
301 END IF;
302 CLOSE c_last_updated;
303
304
305 END;
306
307 procedure DELETE_ROW (
308 X_TERR_QUALIFIER_ID in NUMBER
309 ) is
310 begin
311 delete from JTF_AM_TERR_QUALIFIERS
312 where TERR_QUALIFIER_ID = X_TERR_QUALIFIER_ID;
313
314 if (sql%notfound) then
315 raise no_data_found;
316 end if;
317
318 end DELETE_ROW;
319 end JTF_AM_TERR_QUALIFIERS_PKG;