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.CS_KB_SET_ELE_TYPES_PKG

Source


1 PACKAGE BODY CS_KB_SET_ELE_TYPES_PKG AS
2 /* $Header: cskbsetb.pls 115.15 2003/11/19 23:39:36 mkettle ship $ */
3 
4 function Create_Set_Ele_Type(
5   P_SET_TYPE_ID in NUMBER,
6   P_ELEMENT_TYPE_ID in NUMBER,
7   P_ELEMENT_TYPE_ORDER in NUMBER,
8   P_OPTIONAL_FLAG in VARCHAR2,
9   P_ATTRIBUTE_CATEGORY in VARCHAR2,
10   P_ATTRIBUTE1 in VARCHAR2,
11   P_ATTRIBUTE2 in VARCHAR2,
12   P_ATTRIBUTE3 in VARCHAR2,
13   P_ATTRIBUTE4 in VARCHAR2,
14   P_ATTRIBUTE5 in VARCHAR2,
15   P_ATTRIBUTE6 in VARCHAR2,
16   P_ATTRIBUTE7 in VARCHAR2,
17   P_ATTRIBUTE8 in VARCHAR2,
18   P_ATTRIBUTE9 in VARCHAR2,
19   P_ATTRIBUTE10 in VARCHAR2,
20   P_ATTRIBUTE11 in VARCHAR2,
21   P_ATTRIBUTE12 in VARCHAR2,
22   P_ATTRIBUTE13 in VARCHAR2,
23   P_ATTRIBUTE14 in VARCHAR2,
24   P_ATTRIBUTE15 in VARCHAR2
25 ) return number IS
26   l_date  date;
27   l_created_by number;
28   l_login number;
29 BEGIN
30 
31   -- Check params
32   if(P_SET_TYPE_ID is null OR P_ELEMENT_TYPE_ID is NULL) then
33     goto error_found;
34   end if;
35 
36   l_date := sysdate;
37   l_created_by := fnd_global.user_id;
38   l_login := fnd_global.login_id;
39 
40   insert into CS_KB_SET_ELE_TYPES (
41     SET_TYPE_ID,
42     ELEMENT_TYPE_ID,
43     ELEMENT_TYPE_ORDER,
44     OPTIONAL_FLAG,
45     CREATION_DATE,
46     CREATED_BY,
47     LAST_UPDATE_DATE,
48     LAST_UPDATED_BY,
49     LAST_UPDATE_LOGIN,
50     ATTRIBUTE_CATEGORY,
51     ATTRIBUTE1,
52     ATTRIBUTE2,
53     ATTRIBUTE3,
54     ATTRIBUTE4,
55     ATTRIBUTE5,
56     ATTRIBUTE6,
57     ATTRIBUTE7,
58     ATTRIBUTE8,
59     ATTRIBUTE9,
60     ATTRIBUTE10,
61     ATTRIBUTE11,
62     ATTRIBUTE12,
63     ATTRIBUTE13,
64     ATTRIBUTE14,
65     ATTRIBUTE15
66   ) values (
67     P_SET_TYPE_ID,
68     P_ELEMENT_TYPE_ID,
69     P_ELEMENT_TYPE_ORDER,
70     P_OPTIONAL_FLAG,
71     l_date,
72     l_created_by,
73     l_date,
74     l_created_by,
75     l_login,
76     P_ATTRIBUTE_CATEGORY,
77     P_ATTRIBUTE1,
78     P_ATTRIBUTE2,
79     P_ATTRIBUTE3,
80     P_ATTRIBUTE4,
81     P_ATTRIBUTE5,
82     P_ATTRIBUTE6,
83     P_ATTRIBUTE7,
84     P_ATTRIBUTE8,
85     P_ATTRIBUTE9,
86     P_ATTRIBUTE10,
87     P_ATTRIBUTE11,
88     P_ATTRIBUTE12,
89     P_ATTRIBUTE13,
90     P_ATTRIBUTE14,
91     P_ATTRIBUTE15
92     );
93 
94   return OKAY_STATUS;
95   <<error_found>>
96   return ERROR_STATUS;
97 
98 END Create_Set_Ele_Type;
99 
100 
101 function Update_Set_Ele_Type(
102   P_SET_TYPE_ID in NUMBER,
103   P_ELEMENT_TYPE_ID in NUMBER,
104   P_ELEMENT_TYPE_ORDER in NUMBER,
105   P_OPTIONAL_FLAG in VARCHAR2,
106   P_ATTRIBUTE_CATEGORY in VARCHAR2,
107   P_ATTRIBUTE1 in VARCHAR2,
108   P_ATTRIBUTE2 in VARCHAR2,
109   P_ATTRIBUTE3 in VARCHAR2,
110   P_ATTRIBUTE4 in VARCHAR2,
111   P_ATTRIBUTE5 in VARCHAR2,
112   P_ATTRIBUTE6 in VARCHAR2,
113   P_ATTRIBUTE7 in VARCHAR2,
114   P_ATTRIBUTE8 in VARCHAR2,
115   P_ATTRIBUTE9 in VARCHAR2,
116   P_ATTRIBUTE10 in VARCHAR2,
117   P_ATTRIBUTE11 in VARCHAR2,
118   P_ATTRIBUTE12 in VARCHAR2,
119   P_ATTRIBUTE13 in VARCHAR2,
120   P_ATTRIBUTE14 in VARCHAR2,
121   P_ATTRIBUTE15 in VARCHAR2
122 ) return number is
123   l_ret number;
124   l_date  date;
125   l_updated_by number;
126   l_login number;
127 begin
128 
129   -- validate params
130   if(P_SET_TYPE_ID is null OR P_ELEMENT_TYPE_ID is null) then
131     goto error_found;
132   end if;
133 
134   l_date := sysdate;
135   l_updated_by := fnd_global.user_id;
136   l_login := fnd_global.login_id;
137 
138   update CS_KB_SET_ELE_TYPES set
139     ELEMENT_TYPE_ORDER = P_ELEMENT_TYPE_ORDER,
140     OPTIONAL_FLAG = P_OPTIONAL_FLAG,
141     LAST_UPDATE_DATE = l_date,
142     LAST_UPDATED_BY = l_updated_by,
143     LAST_UPDATE_LOGIN = l_login,
144     ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
145     ATTRIBUTE1 = P_ATTRIBUTE1,
146     ATTRIBUTE2 = P_ATTRIBUTE2,
147     ATTRIBUTE3 = P_ATTRIBUTE3,
148     ATTRIBUTE4 = P_ATTRIBUTE4,
149     ATTRIBUTE5 = P_ATTRIBUTE5,
150     ATTRIBUTE6 = P_ATTRIBUTE6,
151     ATTRIBUTE7 = P_ATTRIBUTE7,
152     ATTRIBUTE8 = P_ATTRIBUTE8,
153     ATTRIBUTE9 = P_ATTRIBUTE9,
154     ATTRIBUTE10 = P_ATTRIBUTE10,
155     ATTRIBUTE11 = P_ATTRIBUTE11,
156     ATTRIBUTE12 = P_ATTRIBUTE12,
157     ATTRIBUTE13 = P_ATTRIBUTE13,
158     ATTRIBUTE14 = P_ATTRIBUTE14,
159     ATTRIBUTE15 = P_ATTRIBUTE15
160   where SET_TYPE_ID = P_SET_TYPE_ID
161   and ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
162 
163   if (sql%notfound) then
164     raise no_data_found;
165   end if;
166 
167   return OKAY_STATUS;
168   <<error_found>>
169   return ERROR_STATUS;
170 exception
171   when others then
172     return ERROR_STATUS;
173 end Update_Set_Ele_Type;
174 
175 function Delete_Set_Ele_Type (
176   P_SET_TYPE_ID in NUMBER,
177   P_ELEMENT_TYPE_ID in NUMBER
178 ) return number is
179 l_count number;
180 begin
181   if (P_SET_TYPE_ID is null or P_ELEMENT_TYPE_ID is null) then return ERROR_STATUS;  end if;
182 
183 --  select count(*) into l_count
184 --    from cs_kb_set_eles where Set_Id In (
185 --    Select e.Set_Id from cs_kb_set_eles e, cs_kb_sets_b s, cs_kb_elements_b t
186 --    where t.element_type_id = p_element_type_id and s.set_type_id = p_set_type_id and
187 --    e.Set_Id = s.Set_Id and e.Element_Id = t.Element_Id);
188   select /*+ INDEX(s) */ count(*) into l_count
189     from cs_kb_sets_b s
190     where s.set_type_id = p_set_type_id
191     and s.status <> 'OBS'
192     and (s.latest_version_flag = 'Y' OR s.viewable_version_flag = 'Y')
193     and exists (select 'x'
194                 from cs_kb_set_eles se,
195                      cs_kb_elements_b e
196                 where se.element_id = e.element_id
197                 and se.set_id = s.set_id
198                 and e.element_type_id = p_element_type_id
199                 and e.status <> 'OBS');
200   if(l_count > 0) then
201     fnd_message.set_name('CS', 'CS_KB_C_SET_TYPE_WITH_SET');
202  --   fnd_msg_pub.Add;
203  --   raise FND_API.G_EXC_ERROR;
204     return ERROR_STATUS;
205   end if;
206 
207   delete from CS_KB_SET_ELE_TYPES
208   where SET_TYPE_ID = P_SET_TYPE_ID
209   and ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
210 
211   if (sql%notfound) then
212     raise no_data_found;
213   end if;
214    return OKAY_STATUS;
215   <<error_found>>
216   return ERROR_STATUS;
217 
218 end Delete_Set_Ele_Type;
219 
220 procedure INSERT_ROW (
221   X_ROWID in OUT NOCOPY VARCHAR2,
222   X_SET_TYPE_ID in NUMBER,
223   X_ELEMENT_TYPE_ID in NUMBER,
224   X_ELEMENT_TYPE_ORDER in NUMBER,
225   X_OPTIONAL_FLAG in VARCHAR2,
226   X_CREATION_DATE in DATE,
227   X_CREATED_BY in NUMBER,
228   X_LAST_UPDATE_DATE in DATE,
229   X_LAST_UPDATED_BY in NUMBER,
230   X_LAST_UPDATE_LOGIN in NUMBER,
231   X_ATTRIBUTE_CATEGORY in VARCHAR2,
232   X_ATTRIBUTE1 in VARCHAR2,
233   X_ATTRIBUTE2 in VARCHAR2,
234   X_ATTRIBUTE3 in VARCHAR2,
235   X_ATTRIBUTE4 in VARCHAR2,
236   X_ATTRIBUTE5 in VARCHAR2,
237   X_ATTRIBUTE6 in VARCHAR2,
238   X_ATTRIBUTE7 in VARCHAR2,
239   X_ATTRIBUTE8 in VARCHAR2,
240   X_ATTRIBUTE9 in VARCHAR2,
241   X_ATTRIBUTE10 in VARCHAR2,
242   X_ATTRIBUTE11 in VARCHAR2,
243   X_ATTRIBUTE12 in VARCHAR2,
244   X_ATTRIBUTE13 in VARCHAR2,
245   X_ATTRIBUTE14 in VARCHAR2,
246   X_ATTRIBUTE15 in VARCHAR2) IS
247 
248   cursor C is select ROWID from CS_KB_SET_ELE_TYPES where SET_TYPE_ID = X_SET_TYPE_ID
249                                                     AND   ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID;
250 
251 BEGIN
252 
253 
254   insert into CS_KB_SET_ELE_TYPES (
255     SET_TYPE_ID,
256     ELEMENT_TYPE_ID,
257     ELEMENT_TYPE_ORDER,
258     OPTIONAL_FLAG,
259     CREATION_DATE,
260     CREATED_BY,
261     LAST_UPDATE_DATE,
262     LAST_UPDATED_BY,
263     LAST_UPDATE_LOGIN,
264     ATTRIBUTE_CATEGORY,
265     ATTRIBUTE1,
266     ATTRIBUTE2,
267     ATTRIBUTE3,
268     ATTRIBUTE4,
269     ATTRIBUTE5,
270     ATTRIBUTE6,
271     ATTRIBUTE7,
272     ATTRIBUTE8,
273     ATTRIBUTE9,
274     ATTRIBUTE10,
275     ATTRIBUTE11,
276     ATTRIBUTE12,
277     ATTRIBUTE13,
278     ATTRIBUTE14,
279     ATTRIBUTE15
280   ) values (
281     X_SET_TYPE_ID,
282     X_ELEMENT_TYPE_ID,
283     X_ELEMENT_TYPE_ORDER,
284     X_OPTIONAL_FLAG,
285     X_CREATION_DATE,
286     X_CREATED_BY,
287     X_LAST_UPDATE_DATE,
288     X_LAST_UPDATED_BY,
289     X_LAST_UPDATE_LOGIN,
290     X_ATTRIBUTE_CATEGORY,
291     X_ATTRIBUTE1,
292     X_ATTRIBUTE2,
293     X_ATTRIBUTE3,
294     X_ATTRIBUTE4,
295     X_ATTRIBUTE5,
296     X_ATTRIBUTE6,
297     X_ATTRIBUTE7,
298     X_ATTRIBUTE8,
299     X_ATTRIBUTE9,
300     X_ATTRIBUTE10,
301     X_ATTRIBUTE11,
302     X_ATTRIBUTE12,
303     X_ATTRIBUTE13,
304     X_ATTRIBUTE14,
305     X_ATTRIBUTE15
306     );
307 
308   open c;
309   fetch c into X_ROWID;
310 
311   if (c%notfound) then
312       close c;
313       raise no_data_found;
314   end if;
315 
316   close c;
317 
318 END INSERT_ROW;
319 
320 procedure UPDATE_ROW (
321   X_SET_TYPE_ID in NUMBER,
322   X_ELEMENT_TYPE_ID in NUMBER,
323   X_ELEMENT_TYPE_ORDER in NUMBER,
324   X_OPTIONAL_FLAG in VARCHAR2,
325   X_LAST_UPDATE_DATE in DATE,
326   X_LAST_UPDATED_BY in NUMBER,
327   X_LAST_UPDATE_LOGIN in NUMBER,
328   X_ATTRIBUTE_CATEGORY in VARCHAR2,
329   X_ATTRIBUTE1 in VARCHAR2,
330   X_ATTRIBUTE2 in VARCHAR2,
331   X_ATTRIBUTE3 in VARCHAR2,
332   X_ATTRIBUTE4 in VARCHAR2,
333   X_ATTRIBUTE5 in VARCHAR2,
334   X_ATTRIBUTE6 in VARCHAR2,
335   X_ATTRIBUTE7 in VARCHAR2,
336   X_ATTRIBUTE8 in VARCHAR2,
337   X_ATTRIBUTE9 in VARCHAR2,
338   X_ATTRIBUTE10 in VARCHAR2,
339   X_ATTRIBUTE11 in VARCHAR2,
340   X_ATTRIBUTE12 in VARCHAR2,
341   X_ATTRIBUTE13 in VARCHAR2,
342   X_ATTRIBUTE14 in VARCHAR2,
343   X_ATTRIBUTE15 in VARCHAR2
344 ) IS
345 
346 BEGIN
347 
348   update CS_KB_SET_ELE_TYPES set
349 
350     ELEMENT_TYPE_ORDER = X_ELEMENT_TYPE_ORDER,
351     OPTIONAL_FLAG = X_OPTIONAL_FLAG,
352     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
353     LAST_UPDATED_BY =  X_LAST_UPDATED_BY,
354     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
355     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
356     ATTRIBUTE1 = X_ATTRIBUTE1,
357     ATTRIBUTE2 = X_ATTRIBUTE2,
358     ATTRIBUTE3 = X_ATTRIBUTE3,
359     ATTRIBUTE4 = X_ATTRIBUTE4,
360     ATTRIBUTE5 = X_ATTRIBUTE5,
361     ATTRIBUTE6 = X_ATTRIBUTE6,
362     ATTRIBUTE7 = X_ATTRIBUTE7,
363     ATTRIBUTE8 = X_ATTRIBUTE8,
364     ATTRIBUTE9 = X_ATTRIBUTE9,
365     ATTRIBUTE10 = X_ATTRIBUTE10,
366     ATTRIBUTE11 = X_ATTRIBUTE11,
367     ATTRIBUTE12 = X_ATTRIBUTE12,
368     ATTRIBUTE13 = X_ATTRIBUTE13,
369     ATTRIBUTE14 = X_ATTRIBUTE14,
370     ATTRIBUTE15 = X_ATTRIBUTE15
371   where SET_TYPE_ID = X_SET_TYPE_ID
372   and ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID;
373 
374   if (sql%notfound) then
375     raise no_data_found;
376   end if;
377 
378 END UPDATE_ROW;
379 
380 PROCEDURE LOAD_ROW(
381         x_set_type_id in number,
382         x_element_type_id in number,
383         x_element_type_order in number,
384         x_optional_flag in varchar2,
385 	x_owner in varchar2) IS
386    l_user_id number;
387    l_rowid varchar2(100);
388 begin
389 
390     if (x_owner = 'SEED') then
391            l_user_id := 1;
392     else
396     CS_KB_SET_ELE_TYPES_PKG.Update_Row(
393            l_user_id := 0;
394     end if;
395 
397     X_Set_Type_Id => x_set_type_id,
398     X_Element_Type_Id => x_element_type_id,
399     X_ELEMENT_TYPE_ORDER => x_element_type_order,
400     X_OPTIONAL_FLAG => x_optional_flag,
401     X_LAST_UPDATE_DATE => sysdate,
402     X_LAST_UPDATED_BY => l_user_id,
403     X_LAST_UPDATE_LOGIN => 0);
404 
405     exception
406       when no_data_found then
407   	CS_KB_SET_ELE_TYPES_PKG.Insert_Row(
408     		X_Rowid => l_rowid,
409     		X_SET_TYPE_ID => x_set_type_id,
410                 X_ELEMENT_TYPE_ID => x_element_type_id,
411 	        X_ELEMENT_TYPE_ORDER => x_element_type_order,
412 	        X_OPTIONAL_FLAG => x_optional_flag,
413 	        X_CREATION_DATE => sysdate,
414 	        X_CREATED_BY => l_user_id,
415 		X_LAST_UPDATE_DATE => sysdate,
416 	        X_LAST_UPDATED_BY => l_user_id,
417                 X_LAST_UPDATE_LOGIN => 0);
418 
419 end;
420 
421 end CS_KB_SET_ELE_TYPES_PKG;