DBA Data[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;