eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JTF_AM_SKILL_RULE_DTLS_PKG

Source


1 package body JTF_AM_SKILL_RULE_DTLS_PKG as
2 /* $Header: jtfamtdb.pls 115.0 2003/01/30 23:58:25 nsinghai noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_DETAIL_ID in NUMBER,
6   X_ATTRIBUTE6 in VARCHAR2,
7   X_ATTRIBUTE12 in VARCHAR2,
8   X_RULE_ID in NUMBER,
9   X_RANK in NUMBER,
10   X_CHECK_PRODUCT_ID in NUMBER,
11   X_CHECK_CATEGORY_ID in NUMBER,
12   X_CHECK_PROBLEM_CODE in NUMBER,
13   X_CHECK_COMPONENT_ID in NUMBER,
14   X_ACTIVE_FLAG in VARCHAR2,
15   X_ATTRIBUTE15 in VARCHAR2,
16   X_SECURITY_GROUP_ID in NUMBER,
17   X_ATTRIBUTE3 in VARCHAR2,
18   X_ATTRIBUTE4 in VARCHAR2,
19   X_ATTRIBUTE5 in VARCHAR2,
20   X_OBJECT_VERSION_NUMBER in NUMBER,
21   X_ATTRIBUTE_CATEGORY in VARCHAR2,
22   X_ATTRIBUTE1 in VARCHAR2,
23   X_ATTRIBUTE2 in VARCHAR2,
24   X_ATTRIBUTE13 in VARCHAR2,
25   X_ATTRIBUTE7 in VARCHAR2,
26   X_ATTRIBUTE8 in VARCHAR2,
27   X_ATTRIBUTE9 in VARCHAR2,
28   X_ATTRIBUTE10 in VARCHAR2,
29   X_ATTRIBUTE11 in VARCHAR2,
30   X_ATTRIBUTE14 in VARCHAR2,
31   X_CREATION_DATE in DATE,
32   X_CREATED_BY in NUMBER,
33   X_LAST_UPDATE_DATE in DATE,
34   X_LAST_UPDATED_BY in NUMBER,
35   X_LAST_UPDATE_LOGIN in NUMBER
36 ) is
37   cursor C is select ROWID from JTF_AM_SKILL_RULE_DTLS
38     where DETAIL_ID = X_DETAIL_ID
39     ;
40 begin
41   insert into JTF_AM_SKILL_RULE_DTLS (
42     ATTRIBUTE6,
43     ATTRIBUTE12,
44     DETAIL_ID,
45     RULE_ID,
46     RANK,
47     CHECK_PRODUCT_ID,
48     CHECK_CATEGORY_ID,
49     CHECK_PROBLEM_CODE,
50     CHECK_COMPONENT_ID,
51     ACTIVE_FLAG,
52     CREATED_BY,
53     CREATION_DATE,
54     ATTRIBUTE15,
55     SECURITY_GROUP_ID,
56     ATTRIBUTE3,
57     ATTRIBUTE4,
58     ATTRIBUTE5,
59     OBJECT_VERSION_NUMBER,
60     ATTRIBUTE_CATEGORY,
61     ATTRIBUTE1,
62     ATTRIBUTE2,
63     ATTRIBUTE13,
64     ATTRIBUTE7,
65     ATTRIBUTE8,
66     ATTRIBUTE9,
67     ATTRIBUTE10,
68     ATTRIBUTE11,
69     LAST_UPDATE_LOGIN,
70     LAST_UPDATED_BY,
71     LAST_UPDATE_DATE,
72     ATTRIBUTE14
73   ) values (
74     X_ATTRIBUTE6,
75     X_ATTRIBUTE12,
76     X_DETAIL_ID,
77     X_RULE_ID,
78     X_RANK,
79     X_CHECK_PRODUCT_ID,
80     X_CHECK_CATEGORY_ID,
81     X_CHECK_PROBLEM_CODE,
82     X_CHECK_COMPONENT_ID,
83     X_ACTIVE_FLAG,
84     X_CREATED_BY,
85     X_CREATION_DATE,
86     X_ATTRIBUTE15,
87     X_SECURITY_GROUP_ID,
88     X_ATTRIBUTE3,
89     X_ATTRIBUTE4,
90     X_ATTRIBUTE5,
91     X_OBJECT_VERSION_NUMBER,
92     X_ATTRIBUTE_CATEGORY,
93     X_ATTRIBUTE1,
94     X_ATTRIBUTE2,
95     X_ATTRIBUTE13,
96     X_ATTRIBUTE7,
97     X_ATTRIBUTE8,
98     X_ATTRIBUTE9,
99     X_ATTRIBUTE10,
100     X_ATTRIBUTE11,
101     X_LAST_UPDATE_LOGIN,
102     X_LAST_UPDATED_BY,
103     X_LAST_UPDATE_DATE,
104     X_ATTRIBUTE14
105     );
106 
107   open c;
108   fetch c into X_ROWID;
109   if (c%notfound) then
110     close c;
111     raise no_data_found;
112   end if;
113   close c;
114 
115 end INSERT_ROW;
116 
117 procedure LOCK_ROW (
118   X_DETAIL_ID in NUMBER,
119   X_ATTRIBUTE6 in VARCHAR2,
120   X_ATTRIBUTE12 in VARCHAR2,
121   X_RULE_ID in NUMBER,
122   X_RANK in NUMBER,
123   X_CHECK_PRODUCT_ID in NUMBER,
124   X_CHECK_CATEGORY_ID in NUMBER,
125   X_CHECK_PROBLEM_CODE in NUMBER,
126   X_CHECK_COMPONENT_ID in NUMBER,
127   X_ACTIVE_FLAG in VARCHAR2,
128   X_ATTRIBUTE15 in VARCHAR2,
129   X_SECURITY_GROUP_ID in NUMBER,
130   X_ATTRIBUTE3 in VARCHAR2,
131   X_ATTRIBUTE4 in VARCHAR2,
132   X_ATTRIBUTE5 in VARCHAR2,
133   X_OBJECT_VERSION_NUMBER in NUMBER,
134   X_ATTRIBUTE_CATEGORY in VARCHAR2,
135   X_ATTRIBUTE1 in VARCHAR2,
136   X_ATTRIBUTE2 in VARCHAR2,
137   X_ATTRIBUTE13 in VARCHAR2,
138   X_ATTRIBUTE7 in VARCHAR2,
139   X_ATTRIBUTE8 in VARCHAR2,
140   X_ATTRIBUTE9 in VARCHAR2,
141   X_ATTRIBUTE10 in VARCHAR2,
142   X_ATTRIBUTE11 in VARCHAR2,
143   X_ATTRIBUTE14 in VARCHAR2
144 ) is
145   cursor c1 is select
146       ATTRIBUTE6,
147       ATTRIBUTE12,
148       RULE_ID,
149       RANK,
150       CHECK_PRODUCT_ID,
151       CHECK_CATEGORY_ID,
152       CHECK_PROBLEM_CODE,
153       CHECK_COMPONENT_ID,
154       ACTIVE_FLAG,
155       ATTRIBUTE15,
156       SECURITY_GROUP_ID,
157       ATTRIBUTE3,
158       ATTRIBUTE4,
159       ATTRIBUTE5,
160       OBJECT_VERSION_NUMBER,
161       ATTRIBUTE_CATEGORY,
162       ATTRIBUTE1,
163       ATTRIBUTE2,
164       ATTRIBUTE13,
165       ATTRIBUTE7,
166       ATTRIBUTE8,
167       ATTRIBUTE9,
168       ATTRIBUTE10,
169       ATTRIBUTE11,
170       ATTRIBUTE14,
171       DETAIL_ID
172     from JTF_AM_SKILL_RULE_DTLS
173     where DETAIL_ID = X_DETAIL_ID
174     for update of DETAIL_ID nowait;
175 begin
176   for tlinfo in c1 loop
177       if (    (tlinfo.DETAIL_ID = X_DETAIL_ID)
178           AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
179                OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
180           AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
181                OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
182           AND (tlinfo.RULE_ID = X_RULE_ID)
183           AND (tlinfo.RANK = X_RANK)
184           AND (tlinfo.CHECK_PRODUCT_ID = X_CHECK_PRODUCT_ID)
185           AND (tlinfo.CHECK_CATEGORY_ID = X_CHECK_CATEGORY_ID)
186           AND (tlinfo.CHECK_PROBLEM_CODE = X_CHECK_PROBLEM_CODE)
187           AND (tlinfo.CHECK_COMPONENT_ID = X_CHECK_COMPONENT_ID)
188           AND (tlinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
189           AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
190                OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
191           AND ((tlinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
192                OR ((tlinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
193           AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
194                OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
195           AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
196                OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
197           AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
198                OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
199           AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
200           AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
201                OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
202           AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
203                OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
204           AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
205                OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
206           AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
207                OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
208           AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
209                OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
210           AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
211                OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
212           AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
213                OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
214           AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
215                OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
216           AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
217                OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
218           AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
219                OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
220       ) then
221         null;
222       else
223         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
224         app_exception.raise_exception;
225       end if;
226   end loop;
227   return;
228 end LOCK_ROW;
229 
230 procedure UPDATE_ROW (
231   X_DETAIL_ID in NUMBER,
232   X_ATTRIBUTE6 in VARCHAR2,
233   X_ATTRIBUTE12 in VARCHAR2,
234   X_RULE_ID in NUMBER,
235   X_RANK in NUMBER,
236   X_CHECK_PRODUCT_ID in NUMBER,
237   X_CHECK_CATEGORY_ID in NUMBER,
238   X_CHECK_PROBLEM_CODE in NUMBER,
239   X_CHECK_COMPONENT_ID in NUMBER,
240   X_ACTIVE_FLAG in VARCHAR2,
241   X_ATTRIBUTE15 in VARCHAR2,
242   X_SECURITY_GROUP_ID in NUMBER,
243   X_ATTRIBUTE3 in VARCHAR2,
244   X_ATTRIBUTE4 in VARCHAR2,
245   X_ATTRIBUTE5 in VARCHAR2,
246   X_OBJECT_VERSION_NUMBER in NUMBER,
247   X_ATTRIBUTE_CATEGORY in VARCHAR2,
248   X_ATTRIBUTE1 in VARCHAR2,
249   X_ATTRIBUTE2 in VARCHAR2,
250   X_ATTRIBUTE13 in VARCHAR2,
251   X_ATTRIBUTE7 in VARCHAR2,
252   X_ATTRIBUTE8 in VARCHAR2,
253   X_ATTRIBUTE9 in VARCHAR2,
254   X_ATTRIBUTE10 in VARCHAR2,
255   X_ATTRIBUTE11 in VARCHAR2,
256   X_ATTRIBUTE14 in VARCHAR2,
257   X_LAST_UPDATE_DATE in DATE,
258   X_LAST_UPDATED_BY in NUMBER,
259   X_LAST_UPDATE_LOGIN in NUMBER
260 ) is
261 begin
262   update JTF_AM_SKILL_RULE_DTLS set
263     ATTRIBUTE6 = X_ATTRIBUTE6,
264     ATTRIBUTE12 = X_ATTRIBUTE12,
265     RULE_ID = X_RULE_ID,
266     RANK = X_RANK,
267     CHECK_PRODUCT_ID = X_CHECK_PRODUCT_ID,
268     CHECK_CATEGORY_ID = X_CHECK_CATEGORY_ID,
269     CHECK_PROBLEM_CODE = X_CHECK_PROBLEM_CODE,
270     CHECK_COMPONENT_ID = X_CHECK_COMPONENT_ID,
271     ACTIVE_FLAG = X_ACTIVE_FLAG,
272     ATTRIBUTE15 = X_ATTRIBUTE15,
273     SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
274     ATTRIBUTE3 = X_ATTRIBUTE3,
275     ATTRIBUTE4 = X_ATTRIBUTE4,
276     ATTRIBUTE5 = X_ATTRIBUTE5,
277     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
278     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
279     ATTRIBUTE1 = X_ATTRIBUTE1,
280     ATTRIBUTE2 = X_ATTRIBUTE2,
281     ATTRIBUTE13 = X_ATTRIBUTE13,
282     ATTRIBUTE7 = X_ATTRIBUTE7,
283     ATTRIBUTE8 = X_ATTRIBUTE8,
284     ATTRIBUTE9 = X_ATTRIBUTE9,
285     ATTRIBUTE10 = X_ATTRIBUTE10,
286     ATTRIBUTE11 = X_ATTRIBUTE11,
287     ATTRIBUTE14 = X_ATTRIBUTE14,
288     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
289     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
290     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
291   where DETAIL_ID = X_DETAIL_ID
292   ;
293 
294   if (sql%notfound) then
295     raise no_data_found;
296   end if;
297 end UPDATE_ROW;
298 
299 procedure DELETE_ROW (
300   X_DETAIL_ID in NUMBER
301 ) is
302 begin
303   delete from JTF_AM_SKILL_RULE_DTLS
304   where DETAIL_ID = X_DETAIL_ID;
305 
306   if (sql%notfound) then
307     raise no_data_found;
308   end if;
309 
310 end DELETE_ROW;
311 
312 procedure LOAD_ROW (
313   X_OWNER   in VARCHAR2,
314   X_DETAIL_ID in NUMBER,
315   X_RULE_ID in NUMBER,
316   X_RANK  in NUMBER,
317   X_CHECK_PRODUCT_ID in NUMBER,
318   X_CHECK_CATEGORY_ID in NUMBER,
319   X_CHECK_PROBLEM_CODE in NUMBER,
320   X_CHECK_COMPONENT_ID in NUMBER,
321   X_ACTIVE_FLAG in VARCHAR2,
322   X_OBJECT_VERSION_NUMBER in NUMBER,
323   X_ATTRIBUTE1 in VARCHAR2,
324   X_ATTRIBUTE2 in VARCHAR2,
325   X_ATTRIBUTE3 in VARCHAR2,
326   X_ATTRIBUTE4 in VARCHAR2,
327   X_ATTRIBUTE5 in VARCHAR2,
328   X_ATTRIBUTE6 in VARCHAR2,
329   X_ATTRIBUTE7 in VARCHAR2,
330   X_ATTRIBUTE8 in VARCHAR2,
331   X_ATTRIBUTE9 in VARCHAR2,
332   X_ATTRIBUTE10 in VARCHAR2,
333   X_ATTRIBUTE11 in VARCHAR2,
334   X_ATTRIBUTE12 in VARCHAR2,
335   X_ATTRIBUTE13 in VARCHAR2,
336   X_ATTRIBUTE14 in VARCHAR2,
337   X_ATTRIBUTE15 in VARCHAR2,
338   X_ATTRIBUTE_CATEGORY in VARCHAR2,
339   X_SECURITY_GROUP_ID in NUMBER) is
340 
341   l_row_id rowid;
342   l_last_updated_by number := -1;
343   l_object_version_number number := 1;
344   l_user_id  number ;
345 
346   CURSOR c_last_updated IS
347     SELECT last_updated_by,
348            object_version_number
349       from JTF_AM_SKILL_RULE_DTLS
350      WHERE detail_id = x_detail_id;
351 
352 begin
353   if (X_OWNER = 'SEED') then
354         l_user_id := 1;
355   else
356         l_user_id := 0;
357   end if;
358 
359   OPEN c_last_updated;
360   FETCH c_last_updated into l_last_updated_by, l_object_version_number ;
361       IF c_last_updated%NOTFOUND THEN
362          l_object_version_number := 1;
363          jtf_am_skill_rule_dtls_pkg.insert_row(
364                 X_ROWID                 => l_row_id ,
365                 X_DETAIL_ID             => X_DETAIL_ID,
366                 X_RULE_ID               => X_RULE_ID,
367                 X_RANK                  => X_RANK,
368                 X_CHECK_PRODUCT_ID      => X_CHECK_PRODUCT_ID,
369                 X_CHECK_CATEGORY_ID     => X_CHECK_CATEGORY_ID,
370                 X_CHECK_PROBLEM_CODE    => X_CHECK_PROBLEM_CODE,
371                 X_CHECK_COMPONENT_ID    => X_CHECK_COMPONENT_ID,
372                 X_ACTIVE_FLAG           => X_ACTIVE_FLAG,
373                 X_ATTRIBUTE1            => X_ATTRIBUTE1 ,
374                 X_ATTRIBUTE2            => X_ATTRIBUTE2 ,
375                 X_ATTRIBUTE3            => X_ATTRIBUTE3 ,
376                 X_ATTRIBUTE4            => X_ATTRIBUTE4 ,
377                 X_ATTRIBUTE5            => X_ATTRIBUTE5 ,
378                 X_ATTRIBUTE6            => X_ATTRIBUTE6 ,
379                 X_ATTRIBUTE7            => X_ATTRIBUTE7 ,
380                 X_ATTRIBUTE8            => X_ATTRIBUTE8 ,
381                 X_ATTRIBUTE9            => X_ATTRIBUTE9 ,
382                 X_ATTRIBUTE10           => X_ATTRIBUTE10 ,
383                 X_ATTRIBUTE11           => X_ATTRIBUTE11 ,
384                 X_ATTRIBUTE12           => X_ATTRIBUTE12 ,
385                 X_ATTRIBUTE13           => X_ATTRIBUTE13 ,
386                 X_ATTRIBUTE14           => X_ATTRIBUTE14 ,
387                 X_ATTRIBUTE15           => X_ATTRIBUTE15 ,
388                 X_ATTRIBUTE_CATEGORY    => X_ATTRIBUTE_CATEGORY ,
389                 X_SECURITY_GROUP_ID     => X_SECURITY_GROUP_ID,
390                 X_OBJECT_VERSION_NUMBER => l_object_version_number ,
391                 X_CREATION_DATE         => sysdate      ,
392                 X_CREATED_BY            => l_user_id ,
393                 X_LAST_UPDATE_DATE      => sysdate      ,
394                 X_LAST_UPDATED_BY       => l_user_id ,
395                 X_LAST_UPDATE_LOGIN     => 0 );
396       ELSIF c_last_updated%FOUND THEN
397          IF l_last_updated_by IN (1,0) THEN
398             l_object_version_number :=   l_object_version_number + 1;
399             jtf_am_skill_rule_dtls_pkg.update_row(
400                 X_DETAIL_ID             => X_DETAIL_ID,
401                 X_RULE_ID               => X_RULE_ID,
402                 X_RANK                  => X_RANK,
403                 X_CHECK_PRODUCT_ID      => X_CHECK_PRODUCT_ID,
404                 X_CHECK_CATEGORY_ID     => X_CHECK_CATEGORY_ID,
405                 X_CHECK_PROBLEM_CODE    => X_CHECK_PROBLEM_CODE,
406                 X_CHECK_COMPONENT_ID    => X_CHECK_COMPONENT_ID,
407                 X_ACTIVE_FLAG           => X_ACTIVE_FLAG,
408                 X_ATTRIBUTE1            => X_ATTRIBUTE1 ,
409                 X_ATTRIBUTE2            => X_ATTRIBUTE2 ,
410                 X_ATTRIBUTE3            => X_ATTRIBUTE3 ,
411                 X_ATTRIBUTE4            => X_ATTRIBUTE4 ,
412                 X_ATTRIBUTE5            => X_ATTRIBUTE5 ,
413                 X_ATTRIBUTE6            => X_ATTRIBUTE6 ,
414                 X_ATTRIBUTE7            => X_ATTRIBUTE7 ,
415                 X_ATTRIBUTE8            => X_ATTRIBUTE8 ,
416                 X_ATTRIBUTE9            => X_ATTRIBUTE9 ,
417                 X_ATTRIBUTE10           => X_ATTRIBUTE10 ,
418                 X_ATTRIBUTE11           => X_ATTRIBUTE11 ,
419                 X_ATTRIBUTE12           => X_ATTRIBUTE12 ,
420                 X_ATTRIBUTE13           => X_ATTRIBUTE13 ,
421                 X_ATTRIBUTE14           => X_ATTRIBUTE14 ,
422                 X_ATTRIBUTE15           => X_ATTRIBUTE15 ,
423                 X_ATTRIBUTE_CATEGORY    => X_ATTRIBUTE_CATEGORY ,
424                 X_SECURITY_GROUP_ID     => X_SECURITY_GROUP_ID,
425                 X_OBJECT_VERSION_NUMBER => l_object_version_number,
426                 X_LAST_UPDATE_DATE      => sysdate      ,
427                 X_LAST_UPDATED_BY       => l_user_id ,
428                 X_LAST_UPDATE_LOGIN     => 0 );
429            END IF;
430       END IF;
431   CLOSE c_last_updated;
432 
433 end LOAD_ROW;
434 
435 end JTF_AM_SKILL_RULE_DTLS_PKG;