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_RULES_PKG

Source


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