[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SET_ELES_PKG
Source
1 PACKAGE BODY CS_KB_SET_ELES_PKG AS
2 /* $Header: cskbseb.pls 120.1 2005/07/19 17:01:18 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in OUT NOCOPY VARCHAR2,
6 X_SET_ID in NUMBER,
7 X_ELEMENT_ID in NUMBER,
8 X_ELEMENT_ORDER in NUMBER,
9 X_ASSOC_DEGREE in NUMBER,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER,
15 X_ATTRIBUTE_CATEGORY in VARCHAR2,
16 X_ATTRIBUTE1 in VARCHAR2,
17 X_ATTRIBUTE2 in VARCHAR2,
18 X_ATTRIBUTE3 in VARCHAR2,
19 X_ATTRIBUTE4 in VARCHAR2,
20 X_ATTRIBUTE5 in VARCHAR2,
21 X_ATTRIBUTE6 in VARCHAR2,
22 X_ATTRIBUTE7 in VARCHAR2,
23 X_ATTRIBUTE8 in VARCHAR2,
24 X_ATTRIBUTE9 in VARCHAR2,
25 X_ATTRIBUTE10 in VARCHAR2,
26 X_ATTRIBUTE11 in VARCHAR2,
27 X_ATTRIBUTE12 in VARCHAR2,
28 X_ATTRIBUTE13 in VARCHAR2,
29 X_ATTRIBUTE14 in VARCHAR2,
30 X_ATTRIBUTE15 in VARCHAR2) IS
31
32 cursor C is select ROWID from CS_KB_SET_ELES where SET_ID = X_SET_ID
33 AND ELEMENT_ID = X_ELEMENT_ID;
34
35 BEGIN
36
37
38 insert into CS_KB_SET_ELES (
39 SET_ID,
40 ELEMENT_ID,
41 ELEMENT_ORDER,
42 ASSOC_DEGREE,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 ATTRIBUTE_CATEGORY,
49 ATTRIBUTE1,
50 ATTRIBUTE2,
51 ATTRIBUTE3,
52 ATTRIBUTE4,
53 ATTRIBUTE5,
54 ATTRIBUTE6,
55 ATTRIBUTE7,
56 ATTRIBUTE8,
57 ATTRIBUTE9,
58 ATTRIBUTE10,
59 ATTRIBUTE11,
60 ATTRIBUTE12,
61 ATTRIBUTE13,
62 ATTRIBUTE14,
63 ATTRIBUTE15
64 ) values (
65 X_SET_ID,
66 X_ELEMENT_ID,
67 X_ELEMENT_ORDER,
68 X_ASSOC_DEGREE,
69 X_CREATION_DATE,
70 X_CREATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_LOGIN,
74 X_ATTRIBUTE_CATEGORY,
75 X_ATTRIBUTE1,
76 X_ATTRIBUTE2,
77 X_ATTRIBUTE3,
78 X_ATTRIBUTE4,
79 X_ATTRIBUTE5,
80 X_ATTRIBUTE6,
81 X_ATTRIBUTE7,
82 X_ATTRIBUTE8,
83 X_ATTRIBUTE9,
84 X_ATTRIBUTE10,
85 X_ATTRIBUTE11,
86 X_ATTRIBUTE12,
87 X_ATTRIBUTE13,
88 X_ATTRIBUTE14,
89 X_ATTRIBUTE15
90 );
91
92 open c;
93 fetch c into X_ROWID;
94
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99
100 close c;
101
102 END INSERT_ROW;
103
104 procedure UPDATE_ROW (
105 X_SET_ID in NUMBER,
106 X_ELEMENT_ID in NUMBER,
107 X_ELEMENT_ORDER in NUMBER,
108 X_ASSOC_DEGREE in NUMBER,
109 X_LAST_UPDATE_DATE in DATE,
110 X_LAST_UPDATED_BY in NUMBER,
111 X_LAST_UPDATE_LOGIN in NUMBER,
112 X_ATTRIBUTE_CATEGORY in VARCHAR2,
113 X_ATTRIBUTE1 in VARCHAR2,
114 X_ATTRIBUTE2 in VARCHAR2,
115 X_ATTRIBUTE3 in VARCHAR2,
116 X_ATTRIBUTE4 in VARCHAR2,
117 X_ATTRIBUTE5 in VARCHAR2,
118 X_ATTRIBUTE6 in VARCHAR2,
119 X_ATTRIBUTE7 in VARCHAR2,
120 X_ATTRIBUTE8 in VARCHAR2,
121 X_ATTRIBUTE9 in VARCHAR2,
122 X_ATTRIBUTE10 in VARCHAR2,
123 X_ATTRIBUTE11 in VARCHAR2,
124 X_ATTRIBUTE12 in VARCHAR2,
125 X_ATTRIBUTE13 in VARCHAR2,
126 X_ATTRIBUTE14 in VARCHAR2,
127 X_ATTRIBUTE15 in VARCHAR2
128 ) IS
129
130 BEGIN
131
132 update CS_KB_SET_ELES set
133
134 ELEMENT_ORDER = X_ELEMENT_ORDER,
135 ASSOC_DEGREE = X_ASSOC_DEGREE,
136 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
137 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
138 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
139 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
140 ATTRIBUTE1 = X_ATTRIBUTE1,
141 ATTRIBUTE2 = X_ATTRIBUTE2,
142 ATTRIBUTE3 = X_ATTRIBUTE3,
143 ATTRIBUTE4 = X_ATTRIBUTE4,
144 ATTRIBUTE5 = X_ATTRIBUTE5,
145 ATTRIBUTE6 = X_ATTRIBUTE6,
146 ATTRIBUTE7 = X_ATTRIBUTE7,
147 ATTRIBUTE8 = X_ATTRIBUTE8,
148 ATTRIBUTE9 = X_ATTRIBUTE9,
149 ATTRIBUTE10 = X_ATTRIBUTE10,
150 ATTRIBUTE11 = X_ATTRIBUTE11,
151 ATTRIBUTE12 = X_ATTRIBUTE12,
152 ATTRIBUTE13 = X_ATTRIBUTE13,
153 ATTRIBUTE14 = X_ATTRIBUTE14,
154 ATTRIBUTE15 = X_ATTRIBUTE15
155 where SET_ID = X_SET_ID
156 and ELEMENT_ID = X_ELEMENT_ID;
157
158 if (sql%notfound) then
159 raise no_data_found;
160 end if;
161
162 END UPDATE_ROW;
163
164 PROCEDURE LOAD_ROW(
165 x_set_id in number,
166 x_element_id in number,
167 x_element_order in number,
168 x_assoc_degree in number,
169 x_owner in varchar2) IS
170 l_user_id number;
171 l_rowid varchar2(100);
172 begin
173
174 if (x_owner = 'SEED') then
175 l_user_id := 1;
176 else
177 l_user_id := 0;
178 end if;
179
180 CS_KB_SET_ELES_PKG.Update_Row(
181 X_Set_Id => x_set_id,
182 X_Element_Id => x_element_id,
183 X_Element_ORDER => x_element_order,
184 X_Assoc_Degree => x_assoc_degree,
185 X_LAST_UPDATE_DATE => sysdate,
186 X_LAST_UPDATED_BY => l_user_id,
187 X_LAST_UPDATE_LOGIN => 0);
188
189 exception
190 when no_data_found then
191 CS_KB_SET_ELES_PKG.Insert_Row(
192 X_Rowid => l_rowid,
193 X_Set_Id => x_set_id,
194 X_Element_Id => x_element_id,
195 X_Element_ORDER => x_element_order,
196 X_Assoc_Degree => x_assoc_degree,
197 X_CREATION_DATE => sysdate,
198 X_CREATED_BY => l_user_id,
199 X_LAST_UPDATE_DATE => sysdate,
200 X_LAST_UPDATED_BY => l_user_id,
201 X_LAST_UPDATE_LOGIN => 0);
202
203 end;
204
205 FUNCTION GET_FIRST_ELEMENT_ID
206 ( set_id in number) return number as
207
208 cursor get_min_ele_order (setid number) is
209 select se.element_id
210 from cs_kb_Set_eles se
211 where se.set_id = setid
212 order by se.element_order;
213 x_return number;
214 begin
215 x_return := null;
216 open get_min_ele_order(set_id);
217 fetch get_min_ele_order into x_return;
218 close get_min_ele_order;
219
220 if x_return is null then
221 x_return := -1;
222 end if;
223 return x_return;
224 END;
225
226 PROCEDURE Clone_Rows( P_SET_SOURCE_ID IN NUMBER,
227 P_SET_TARGET_ID IN NUMBER) IS
228
229 l_sysdate DATE;
230 l_created_by NUMBER;
231 l_login_id NUMBER;
232
233 l_rowid VARCHAR2(30);
234
235
236 CURSOR set_eles_cur is
237 SELECT *
238 FROM CS_KB_SET_ELES
239 WHERE set_id = P_SET_SOURCE_ID;
240
241 BEGIN
242
243 l_sysdate := SYSDATE;
244 l_created_by := FND_GLOBAL.user_id;
245 l_login_id := FND_GLOBAL.login_id;
246
247 for rec IN set_eles_cur loop
248
249 Insert_row(
250 x_rowid => l_rowid,
251 x_set_id => P_SET_TARGET_ID,
252 x_element_id => rec.element_id,
253 x_element_order => rec.element_order,
254 x_assoc_degree => rec.assoc_degree,
255 x_creation_date => l_sysdate,
256 x_created_by => l_created_by,
257 x_last_update_date => l_sysdate,
258 x_last_updated_by => l_created_by,
259 x_last_update_login => l_login_id,
260 x_attribute_category => rec.attribute_category,
261 x_attribute1 => rec.attribute1,
262 x_attribute2 => rec.attribute2,
263 x_attribute3 => rec.attribute3,
264 x_attribute4 => rec.attribute4,
265 x_attribute5 => rec.attribute5,
266 x_attribute6 => rec.attribute6,
267 x_attribute7 => rec.attribute7,
268 x_attribute8 => rec.attribute8,
269 x_attribute9 => rec.attribute9,
270 x_attribute10 => rec.attribute10,
271 x_attribute11 => rec.attribute11,
272 x_attribute12 => rec.attribute12,
273 x_attribute13 => rec.attribute13,
274 x_attribute14 => rec.attribute14,
275 x_attribute15 => rec.attribute15
276 );
277 END LOOP;
278 END Clone_Rows;
279
280
281 end CS_KB_SET_ELES_PKG;