1 PACKAGE BODY EGO_ITEMS_ATTRS_CHANGES_PKG AS
2 /* $Header: EGOIUACB.pls 115.1 2004/07/09 05:08:09 srajapar noship $ */
3
4 ----------------------------------------------------------------------
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'EGO_ITEMS_ATTRS_CHANGES_PKG';
6
7 PROCEDURE code_debug(msg VARCHAR2) IS
8 BEGIN
9 -- sri_debug( G_PKG_NAME ||' '|| msg);
10 null;
11 EXCEPTION
12 WHEN OTHERS THEN
13 NULL;
14 END code_debug;
15
16 PROCEDURE ADD_LANGUAGE
17 IS
18 BEGIN
19 delete from EGO_ITEMS_ATTRS_CHANGES_TL T
20 where not exists
21 (select NULL
22 from EGO_ITEMS_ATTRS_CHANGES_B B
23 where B.EXTENSION_ID = T.EXTENSION_ID
24 and B.ACD_TYPE = T.ACD_TYPE
25 and B.CHANGE_LINE_ID = T.CHANGE_LINE_ID
26 );
27
28 update EGO_ITEMS_ATTRS_CHANGES_TL T set (
29 TL_EXT_ATTR1,
30 TL_EXT_ATTR2,
31 TL_EXT_ATTR3,
32 TL_EXT_ATTR4,
33 TL_EXT_ATTR5,
34 TL_EXT_ATTR6,
35 TL_EXT_ATTR7,
36 TL_EXT_ATTR8,
37 TL_EXT_ATTR9,
38 TL_EXT_ATTR10,
39 TL_EXT_ATTR11,
40 TL_EXT_ATTR12,
41 TL_EXT_ATTR13,
42 TL_EXT_ATTR14,
43 TL_EXT_ATTR15,
44 TL_EXT_ATTR16,
45 TL_EXT_ATTR17,
46 TL_EXT_ATTR18,
47 TL_EXT_ATTR19,
48 TL_EXT_ATTR20,
49 TL_EXT_ATTR21,
50 TL_EXT_ATTR22,
51 TL_EXT_ATTR23,
52 TL_EXT_ATTR24,
53 TL_EXT_ATTR25,
54 TL_EXT_ATTR26,
55 TL_EXT_ATTR27,
56 TL_EXT_ATTR28,
57 TL_EXT_ATTR29,
58 TL_EXT_ATTR30,
59 TL_EXT_ATTR31,
60 TL_EXT_ATTR32,
61 TL_EXT_ATTR33,
62 TL_EXT_ATTR34,
63 TL_EXT_ATTR35,
64 TL_EXT_ATTR36,
65 TL_EXT_ATTR37,
66 TL_EXT_ATTR38,
67 TL_EXT_ATTR39,
68 TL_EXT_ATTR40
69 ) =
70 (select
71 B.TL_EXT_ATTR1,
72 B.TL_EXT_ATTR2,
73 B.TL_EXT_ATTR3,
74 B.TL_EXT_ATTR4,
75 B.TL_EXT_ATTR5,
76 B.TL_EXT_ATTR6,
77 B.TL_EXT_ATTR7,
78 B.TL_EXT_ATTR8,
79 B.TL_EXT_ATTR9,
80 B.TL_EXT_ATTR10,
81 B.TL_EXT_ATTR11,
82 B.TL_EXT_ATTR12,
83 B.TL_EXT_ATTR13,
84 B.TL_EXT_ATTR14,
85 B.TL_EXT_ATTR15,
86 B.TL_EXT_ATTR16,
87 B.TL_EXT_ATTR17,
88 B.TL_EXT_ATTR18,
89 B.TL_EXT_ATTR19,
90 B.TL_EXT_ATTR20,
91 B.TL_EXT_ATTR21,
92 B.TL_EXT_ATTR22,
93 B.TL_EXT_ATTR23,
94 B.TL_EXT_ATTR24,
95 B.TL_EXT_ATTR25,
96 B.TL_EXT_ATTR26,
97 B.TL_EXT_ATTR27,
98 B.TL_EXT_ATTR28,
99 B.TL_EXT_ATTR29,
100 B.TL_EXT_ATTR30,
101 B.TL_EXT_ATTR31,
102 B.TL_EXT_ATTR32,
103 B.TL_EXT_ATTR33,
104 B.TL_EXT_ATTR34,
105 B.TL_EXT_ATTR35,
106 B.TL_EXT_ATTR36,
107 B.TL_EXT_ATTR37,
108 B.TL_EXT_ATTR38,
109 B.TL_EXT_ATTR39,
110 B.TL_EXT_ATTR40
111 from EGO_ITEMS_ATTRS_CHANGES_TL B
112 where B.EXTENSION_ID = T.EXTENSION_ID
113 and B.ACD_TYPE = T.ACD_TYPE
114 and B.CHANGE_LINE_ID = T.CHANGE_LINE_ID
115 and B.LANGUAGE = T.SOURCE_LANG)
116 where (
117 T.EXTENSION_ID,
118 T.ACD_TYPE,
119 T.CHANGE_LINE_ID,
120 T.LANGUAGE
121 ) in (select
122 SUBT.EXTENSION_ID,
123 SUBT.ACD_TYPE,
124 SUBT.CHANGE_LINE_ID,
125 SUBT.LANGUAGE
126 from EGO_ITEMS_ATTRS_CHANGES_TL SUBB, EGO_ITEMS_ATTRS_CHANGES_TL SUBT
127 where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
128 and SUBB.ACD_TYPE = SUBT.ACD_TYPE
129 and SUBB.CHANGE_LINE_ID = SUBT.CHANGE_LINE_ID
130 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
131 and (SUBB.TL_EXT_ATTR1 <> SUBT.TL_EXT_ATTR1
132 or (SUBB.TL_EXT_ATTR1 is null and SUBT.TL_EXT_ATTR1 is not null)
133 or (SUBB.TL_EXT_ATTR1 is not null and SUBT.TL_EXT_ATTR1 is null)
134 or SUBB.TL_EXT_ATTR2 <> SUBT.TL_EXT_ATTR2
135 or (SUBB.TL_EXT_ATTR2 is null and SUBT.TL_EXT_ATTR2 is not null)
136 or (SUBB.TL_EXT_ATTR2 is not null and SUBT.TL_EXT_ATTR2 is null)
137 or SUBB.TL_EXT_ATTR3 <> SUBT.TL_EXT_ATTR3
138 or (SUBB.TL_EXT_ATTR3 is null and SUBT.TL_EXT_ATTR3 is not null)
139 or (SUBB.TL_EXT_ATTR3 is not null and SUBT.TL_EXT_ATTR3 is null)
140 or SUBB.TL_EXT_ATTR4 <> SUBT.TL_EXT_ATTR4
141 or (SUBB.TL_EXT_ATTR4 is null and SUBT.TL_EXT_ATTR4 is not null)
142 or (SUBB.TL_EXT_ATTR4 is not null and SUBT.TL_EXT_ATTR4 is null)
143 or SUBB.TL_EXT_ATTR5 <> SUBT.TL_EXT_ATTR5
144 or (SUBB.TL_EXT_ATTR5 is null and SUBT.TL_EXT_ATTR5 is not null)
145 or (SUBB.TL_EXT_ATTR5 is not null and SUBT.TL_EXT_ATTR5 is null)
146 or SUBB.TL_EXT_ATTR6 <> SUBT.TL_EXT_ATTR6
147 or (SUBB.TL_EXT_ATTR6 is null and SUBT.TL_EXT_ATTR6 is not null)
148 or (SUBB.TL_EXT_ATTR6 is not null and SUBT.TL_EXT_ATTR6 is null)
149 or SUBB.TL_EXT_ATTR7 <> SUBT.TL_EXT_ATTR7
150 or (SUBB.TL_EXT_ATTR7 is null and SUBT.TL_EXT_ATTR7 is not null)
151 or (SUBB.TL_EXT_ATTR7 is not null and SUBT.TL_EXT_ATTR7 is null)
152 or SUBB.TL_EXT_ATTR8 <> SUBT.TL_EXT_ATTR8
153 or (SUBB.TL_EXT_ATTR8 is null and SUBT.TL_EXT_ATTR8 is not null)
154 or (SUBB.TL_EXT_ATTR8 is not null and SUBT.TL_EXT_ATTR8 is null)
155 or SUBB.TL_EXT_ATTR9 <> SUBT.TL_EXT_ATTR9
156 or (SUBB.TL_EXT_ATTR9 is null and SUBT.TL_EXT_ATTR9 is not null)
157 or (SUBB.TL_EXT_ATTR9 is not null and SUBT.TL_EXT_ATTR9 is null)
158 or SUBB.TL_EXT_ATTR10 <> SUBT.TL_EXT_ATTR10
159 or (SUBB.TL_EXT_ATTR10 is null and SUBT.TL_EXT_ATTR10 is not null)
160 or (SUBB.TL_EXT_ATTR10 is not null and SUBT.TL_EXT_ATTR10 is null)
161 or SUBB.TL_EXT_ATTR11 <> SUBT.TL_EXT_ATTR11
162 or (SUBB.TL_EXT_ATTR11 is null and SUBT.TL_EXT_ATTR11 is not null)
163 or (SUBB.TL_EXT_ATTR11 is not null and SUBT.TL_EXT_ATTR11 is null)
164 or SUBB.TL_EXT_ATTR12 <> SUBT.TL_EXT_ATTR12
165 or (SUBB.TL_EXT_ATTR12 is null and SUBT.TL_EXT_ATTR12 is not null)
166 or (SUBB.TL_EXT_ATTR12 is not null and SUBT.TL_EXT_ATTR12 is null)
167 or SUBB.TL_EXT_ATTR13 <> SUBT.TL_EXT_ATTR13
168 or (SUBB.TL_EXT_ATTR13 is null and SUBT.TL_EXT_ATTR13 is not null)
169 or (SUBB.TL_EXT_ATTR13 is not null and SUBT.TL_EXT_ATTR13 is null)
170 or SUBB.TL_EXT_ATTR14 <> SUBT.TL_EXT_ATTR14
171 or (SUBB.TL_EXT_ATTR14 is null and SUBT.TL_EXT_ATTR14 is not null)
172 or (SUBB.TL_EXT_ATTR14 is not null and SUBT.TL_EXT_ATTR14 is null)
173 or SUBB.TL_EXT_ATTR15 <> SUBT.TL_EXT_ATTR15
174 or (SUBB.TL_EXT_ATTR15 is null and SUBT.TL_EXT_ATTR15 is not null)
175 or (SUBB.TL_EXT_ATTR15 is not null and SUBT.TL_EXT_ATTR15 is null)
176 or SUBB.TL_EXT_ATTR16 <> SUBT.TL_EXT_ATTR16
177 or (SUBB.TL_EXT_ATTR16 is null and SUBT.TL_EXT_ATTR16 is not null)
178 or (SUBB.TL_EXT_ATTR16 is not null and SUBT.TL_EXT_ATTR16 is null)
179 or SUBB.TL_EXT_ATTR17 <> SUBT.TL_EXT_ATTR17
180 or (SUBB.TL_EXT_ATTR17 is null and SUBT.TL_EXT_ATTR17 is not null)
181 or (SUBB.TL_EXT_ATTR17 is not null and SUBT.TL_EXT_ATTR17 is null)
182 or SUBB.TL_EXT_ATTR18 <> SUBT.TL_EXT_ATTR18
183 or (SUBB.TL_EXT_ATTR18 is null and SUBT.TL_EXT_ATTR18 is not null)
184 or (SUBB.TL_EXT_ATTR18 is not null and SUBT.TL_EXT_ATTR18 is null)
185 or SUBB.TL_EXT_ATTR19 <> SUBT.TL_EXT_ATTR19
186 or (SUBB.TL_EXT_ATTR19 is null and SUBT.TL_EXT_ATTR19 is not null)
187 or (SUBB.TL_EXT_ATTR19 is not null and SUBT.TL_EXT_ATTR19 is null)
188 or SUBB.TL_EXT_ATTR20 <> SUBT.TL_EXT_ATTR20
189 or (SUBB.TL_EXT_ATTR20 is null and SUBT.TL_EXT_ATTR20 is not null)
190 or (SUBB.TL_EXT_ATTR20 is not null and SUBT.TL_EXT_ATTR20 is null)
191 or SUBB.TL_EXT_ATTR21 <> SUBT.TL_EXT_ATTR21
192 or (SUBB.TL_EXT_ATTR21 is null and SUBT.TL_EXT_ATTR21 is not null)
193 or (SUBB.TL_EXT_ATTR21 is not null and SUBT.TL_EXT_ATTR21 is null)
194 or SUBB.TL_EXT_ATTR22 <> SUBT.TL_EXT_ATTR22
195 or (SUBB.TL_EXT_ATTR22 is null and SUBT.TL_EXT_ATTR22 is not null)
196 or (SUBB.TL_EXT_ATTR22 is not null and SUBT.TL_EXT_ATTR22 is null)
197 or SUBB.TL_EXT_ATTR23 <> SUBT.TL_EXT_ATTR23
198 or (SUBB.TL_EXT_ATTR23 is null and SUBT.TL_EXT_ATTR23 is not null)
199 or (SUBB.TL_EXT_ATTR23 is not null and SUBT.TL_EXT_ATTR23 is null)
200 or SUBB.TL_EXT_ATTR24 <> SUBT.TL_EXT_ATTR24
201 or (SUBB.TL_EXT_ATTR24 is null and SUBT.TL_EXT_ATTR24 is not null)
202 or (SUBB.TL_EXT_ATTR24 is not null and SUBT.TL_EXT_ATTR24 is null)
203 or SUBB.TL_EXT_ATTR25 <> SUBT.TL_EXT_ATTR25
204 or (SUBB.TL_EXT_ATTR25 is null and SUBT.TL_EXT_ATTR25 is not null)
205 or (SUBB.TL_EXT_ATTR25 is not null and SUBT.TL_EXT_ATTR25 is null)
206 or SUBB.TL_EXT_ATTR26 <> SUBT.TL_EXT_ATTR26
207 or (SUBB.TL_EXT_ATTR26 is null and SUBT.TL_EXT_ATTR26 is not null)
208 or (SUBB.TL_EXT_ATTR26 is not null and SUBT.TL_EXT_ATTR26 is null)
209 or SUBB.TL_EXT_ATTR27 <> SUBT.TL_EXT_ATTR27
210 or (SUBB.TL_EXT_ATTR27 is null and SUBT.TL_EXT_ATTR27 is not null)
211 or (SUBB.TL_EXT_ATTR27 is not null and SUBT.TL_EXT_ATTR27 is null)
212 or SUBB.TL_EXT_ATTR28 <> SUBT.TL_EXT_ATTR28
213 or (SUBB.TL_EXT_ATTR28 is null and SUBT.TL_EXT_ATTR28 is not null)
214 or (SUBB.TL_EXT_ATTR28 is not null and SUBT.TL_EXT_ATTR28 is null)
215 or SUBB.TL_EXT_ATTR29 <> SUBT.TL_EXT_ATTR29
216 or (SUBB.TL_EXT_ATTR29 is null and SUBT.TL_EXT_ATTR29 is not null)
217 or (SUBB.TL_EXT_ATTR29 is not null and SUBT.TL_EXT_ATTR29 is null)
218 or SUBB.TL_EXT_ATTR30 <> SUBT.TL_EXT_ATTR30
219 or (SUBB.TL_EXT_ATTR30 is null and SUBT.TL_EXT_ATTR30 is not null)
220 or (SUBB.TL_EXT_ATTR30 is not null and SUBT.TL_EXT_ATTR30 is null)
221 or SUBB.TL_EXT_ATTR31 <> SUBT.TL_EXT_ATTR31
222 or (SUBB.TL_EXT_ATTR31 is null and SUBT.TL_EXT_ATTR31 is not null)
223 or (SUBB.TL_EXT_ATTR31 is not null and SUBT.TL_EXT_ATTR31 is null)
224 or SUBB.TL_EXT_ATTR32 <> SUBT.TL_EXT_ATTR32
225 or (SUBB.TL_EXT_ATTR32 is null and SUBT.TL_EXT_ATTR32 is not null)
226 or (SUBB.TL_EXT_ATTR32 is not null and SUBT.TL_EXT_ATTR32 is null)
227 or SUBB.TL_EXT_ATTR33 <> SUBT.TL_EXT_ATTR33
228 or (SUBB.TL_EXT_ATTR33 is null and SUBT.TL_EXT_ATTR33 is not null)
229 or (SUBB.TL_EXT_ATTR33 is not null and SUBT.TL_EXT_ATTR33 is null)
230 or SUBB.TL_EXT_ATTR34 <> SUBT.TL_EXT_ATTR34
231 or (SUBB.TL_EXT_ATTR34 is null and SUBT.TL_EXT_ATTR34 is not null)
232 or (SUBB.TL_EXT_ATTR34 is not null and SUBT.TL_EXT_ATTR34 is null)
233 or SUBB.TL_EXT_ATTR35 <> SUBT.TL_EXT_ATTR35
234 or (SUBB.TL_EXT_ATTR35 is null and SUBT.TL_EXT_ATTR35 is not null)
235 or (SUBB.TL_EXT_ATTR35 is not null and SUBT.TL_EXT_ATTR35 is null)
236 or SUBB.TL_EXT_ATTR36 <> SUBT.TL_EXT_ATTR36
237 or (SUBB.TL_EXT_ATTR36 is null and SUBT.TL_EXT_ATTR36 is not null)
238 or (SUBB.TL_EXT_ATTR36 is not null and SUBT.TL_EXT_ATTR36 is null)
239 or SUBB.TL_EXT_ATTR37 <> SUBT.TL_EXT_ATTR37
240 or (SUBB.TL_EXT_ATTR37 is null and SUBT.TL_EXT_ATTR37 is not null)
241 or (SUBB.TL_EXT_ATTR37 is not null and SUBT.TL_EXT_ATTR37 is null)
242 or SUBB.TL_EXT_ATTR38 <> SUBT.TL_EXT_ATTR38
243 or (SUBB.TL_EXT_ATTR38 is null and SUBT.TL_EXT_ATTR38 is not null)
244 or (SUBB.TL_EXT_ATTR38 is not null and SUBT.TL_EXT_ATTR38 is null)
245 or SUBB.TL_EXT_ATTR39 <> SUBT.TL_EXT_ATTR39
246 or (SUBB.TL_EXT_ATTR39 is null and SUBT.TL_EXT_ATTR39 is not null)
247 or (SUBB.TL_EXT_ATTR39 is not null and SUBT.TL_EXT_ATTR39 is null)
248 or SUBB.TL_EXT_ATTR40 <> SUBT.TL_EXT_ATTR40
249 or (SUBB.TL_EXT_ATTR40 is null and SUBT.TL_EXT_ATTR40 is not null)
250 or (SUBB.TL_EXT_ATTR40 is not null and SUBT.TL_EXT_ATTR40 is null)
251 ));
252
253 insert into EGO_ITEMS_ATTRS_CHANGES_TL (
254 TL_EXT_ATTR23,
255 TL_EXT_ATTR24,
256 TL_EXT_ATTR25,
257 TL_EXT_ATTR26,
258 TL_EXT_ATTR27,
259 TL_EXT_ATTR28,
260 TL_EXT_ATTR29,
261 TL_EXT_ATTR30,
262 TL_EXT_ATTR31,
263 TL_EXT_ATTR32,
264 TL_EXT_ATTR33,
265 TL_EXT_ATTR34,
266 TL_EXT_ATTR35,
267 TL_EXT_ATTR36,
268 TL_EXT_ATTR37,
269 TL_EXT_ATTR38,
270 TL_EXT_ATTR39,
271 TL_EXT_ATTR40,
272 EXTENSION_ID,
273 ORGANIZATION_ID,
274 INVENTORY_ITEM_ID,
275 REVISION_ID,
276 ITEM_CATALOG_GROUP_ID,
277 ATTR_GROUP_ID,
278 CREATED_BY,
279 CREATION_DATE,
280 LAST_UPDATED_BY,
281 LAST_UPDATE_DATE,
282 LAST_UPDATE_LOGIN,
283 ACD_TYPE,
284 CHANGE_ID,
285 CHANGE_LINE_ID,
286 IMPLEMENTATION_DATE,
287 TL_EXT_ATTR1,
288 TL_EXT_ATTR2,
289 TL_EXT_ATTR3,
290 TL_EXT_ATTR4,
291 TL_EXT_ATTR5,
292 TL_EXT_ATTR6,
293 TL_EXT_ATTR7,
294 TL_EXT_ATTR8,
295 TL_EXT_ATTR9,
296 TL_EXT_ATTR10,
297 TL_EXT_ATTR11,
298 TL_EXT_ATTR12,
299 TL_EXT_ATTR13,
300 TL_EXT_ATTR14,
301 TL_EXT_ATTR15,
302 TL_EXT_ATTR16,
303 TL_EXT_ATTR17,
304 TL_EXT_ATTR18,
305 TL_EXT_ATTR19,
306 TL_EXT_ATTR20,
307 TL_EXT_ATTR21,
308 TL_EXT_ATTR22,
309 LANGUAGE,
310 SOURCE_LANG
311 ) select
312 B.TL_EXT_ATTR23,
313 B.TL_EXT_ATTR24,
314 B.TL_EXT_ATTR25,
315 B.TL_EXT_ATTR26,
316 B.TL_EXT_ATTR27,
317 B.TL_EXT_ATTR28,
318 B.TL_EXT_ATTR29,
319 B.TL_EXT_ATTR30,
320 B.TL_EXT_ATTR31,
324 B.TL_EXT_ATTR35,
321 B.TL_EXT_ATTR32,
322 B.TL_EXT_ATTR33,
323 B.TL_EXT_ATTR34,
325 B.TL_EXT_ATTR36,
326 B.TL_EXT_ATTR37,
327 B.TL_EXT_ATTR38,
328 B.TL_EXT_ATTR39,
329 B.TL_EXT_ATTR40,
330 B.EXTENSION_ID,
331 B.ORGANIZATION_ID,
332 B.INVENTORY_ITEM_ID,
333 B.REVISION_ID,
334 B.ITEM_CATALOG_GROUP_ID,
335 B.ATTR_GROUP_ID,
336 B.CREATED_BY,
337 B.CREATION_DATE,
338 B.LAST_UPDATED_BY,
339 B.LAST_UPDATE_DATE,
340 B.LAST_UPDATE_LOGIN,
341 B.ACD_TYPE,
342 B.CHANGE_ID,
343 B.CHANGE_LINE_ID,
344 B.IMPLEMENTATION_DATE,
345 B.TL_EXT_ATTR1,
346 B.TL_EXT_ATTR2,
347 B.TL_EXT_ATTR3,
348 B.TL_EXT_ATTR4,
349 B.TL_EXT_ATTR5,
350 B.TL_EXT_ATTR6,
351 B.TL_EXT_ATTR7,
352 B.TL_EXT_ATTR8,
353 B.TL_EXT_ATTR9,
354 B.TL_EXT_ATTR10,
355 B.TL_EXT_ATTR11,
356 B.TL_EXT_ATTR12,
357 B.TL_EXT_ATTR13,
358 B.TL_EXT_ATTR14,
359 B.TL_EXT_ATTR15,
360 B.TL_EXT_ATTR16,
361 B.TL_EXT_ATTR17,
362 B.TL_EXT_ATTR18,
363 B.TL_EXT_ATTR19,
364 B.TL_EXT_ATTR20,
365 B.TL_EXT_ATTR21,
366 B.TL_EXT_ATTR22,
367 L.LANGUAGE_CODE,
368 B.SOURCE_LANG
369 from EGO_ITEMS_ATTRS_CHANGES_TL B, FND_LANGUAGES L
370 where L.INSTALLED_FLAG in ('I', 'B')
371 and B.LANGUAGE = userenv('LANG')
372 and not exists
373 (select NULL
374 from EGO_ITEMS_ATTRS_CHANGES_TL T
375 where T.EXTENSION_ID = B.EXTENSION_ID
376 and T.ACD_TYPE = B.ACD_TYPE
377 and T.CHANGE_LINE_ID = B.CHANGE_LINE_ID
378 and T.LANGUAGE = L.LANGUAGE_CODE);
379 END ADD_LANGUAGE;
380
381 ----------------------------------------------------------------------
382 PROCEDURE Deleting_Obj_Pending_Changes
383 (p_api_version IN NUMBER
384 ,p_object_name IN VARCHAR2
385 ,p_instance_pk1_value IN VARCHAR2
386 ,p_instance_pk2_value IN VARCHAR2
387 ,p_instance_pk3_value IN VARCHAR2
388 ,p_instance_pk4_value IN VARCHAR2
389 ,p_instance_pk5_value IN VARCHAR2
390 ,p_change_id IN NUMBER
391 ,p_change_line_id IN NUMBER
392 ,x_return_status OUT NOCOPY VARCHAR2
393 ,x_msg_count OUT NOCOPY NUMBER
394 ,x_msg_data OUT NOCOPY VARCHAR2
395 ) IS
396 ----------------------------------------------------------------------------
397 -- Start of Comments
398 -- API name : Deleting_Pending_Changes
399 -- TYPE : Public
400 -- Pre-reqs : None
401 -- FUNCTION : Delete the pending changes from EGO_ITEMS_ATTRS_CHANGES_B
402 --
403 -- Parameters:
404 -- IN : p_api_version NUMBER
405 -- : p_object_name VARCHAR2
406 -- : p_instance_pk1_value VARCHAR2
407 -- : p_instance_pk2_value VARCHAR2
408 -- : p_instance_pk3_value VARCHAR2
409 -- : p_instance_pk4_value VARCHAR2
410 -- : p_instance_pk5_value VARCHAR2
411 -- : p_change_id NUMBER
412 -- : p_change_line_id NUMBER
413 --
414 -- OUT : x_return_status VARCHAR2
415 -- x_msg_count NUMBER
416 -- x_msg_data VARCHAR2
417 --
418 ----------------------------------------------------------------------------
419 l_api_version NUMBER;
420 l_api_name VARCHAR2(50);
421
422 l_object_row FND_OBJECTS%ROWTYPE;
423 l_delete_table_b tab.tname%TYPE;
424 l_delete_table_tl tab.tname%TYPE;
425
426 l_dynamic_sql VARCHAR2(4000);
427 l_dynamic_sql_b VARCHAR2(4000);
428 l_dynamic_sql_tl VARCHAR2(4000);
429
430 CURSOR get_obj_row (cp_object_name IN VARCHAR2) IS
431 SELECT *
432 FROM fnd_objects
433 WHERE obj_name = cp_object_name;
434
435 BEGIN
436 code_debug(' Start ');
437 code_debug(' params p_object_name ' ||p_object_name||' p_change_id '||p_change_id||' p_change_line_id '||p_change_line_id);
438 code_debug(' params p_instance_pk1_value ' ||p_instance_pk1_value||' p_instance_pk2_value '||p_instance_pk2_value||' p_instance_pk3_value '||p_instance_pk3_value);
439 l_api_version := 1.0;
440 l_api_name := 'DELETE_OBJ_PENDING_CHANGES';
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 l_dynamic_sql_b := NULL;
443 l_dynamic_sql_tl := NULL;
444 -- standard check for API validation
445 IF NOT FND_API.Compatible_API_Call (l_api_version,
446 p_api_version,
447 l_api_name,
448 G_PKG_NAME) THEN
449 code_debug(' Invalid Params Passed ');
450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
451 END IF;
452
453 IF ( p_object_name IS NULL
454 OR
455 (p_change_line_id IS NULL AND p_change_id IS NULL)
456 ) THEN
457 -- what are you planning to delete?
458 fnd_message.set_name('EGO', 'EGO_IPI_INSUFFICIENT_PARAMS');
459 fnd_message.set_token('PROG_NAME', G_PKG_NAME||'.'||l_api_name);
460 x_msg_data := fnd_message.get;
461 x_msg_count := 1;
462 code_debug(x_msg_data );
463 RETURN;
464 END IF;
465
466 OPEN get_obj_row (cp_object_name => p_object_name);
467 FETCH get_obj_row INTO l_object_row;
468 IF get_obj_row%NOTFOUND THEN
469 CLOSE get_obj_row;
470 x_msg_count := 1;
471 x_msg_data := 'Invalid Object passed '||p_object_name;
472 RETURN;
473 ELSE
474 CLOSE get_obj_row;
475 END IF;
476
480 IF p_change_id IS NULL THEN
477 IF l_object_row.obj_name IN ('EGO_ITEM', 'EGO_ITEM_REVISION') THEN
478 l_delete_table_b := 'EGO_ITEMS_ATTRS_CHANGES_B';
479 l_delete_table_tl := 'EGO_ITEMS_ATTRS_CHANGES_TL';
481 IF p_change_line_id IS NULL THEN
482 -- you will never come here
483 l_dynamic_sql := ' WHERE 1 = 1 ';
484 ELSE
485 l_dynamic_sql := ' WHERE change_line_id = '||p_change_line_id;
486 END IF;
487 ELSE
488 l_dynamic_sql := ' WHERE change_id = '||p_change_id;
489 IF p_change_id IS NULL THEN
490 -- do nothing
491 NULL;
492 ELSE
493 l_dynamic_sql := l_dynamic_sql || ' AND change_line_id = '||p_change_line_id;
494 END IF;
495 END IF;
496 ELSE
497 -- todo
498 -- other objects not yet considered
499 x_msg_count := 1;
500 x_msg_data := 'Contact EXTFWK dev team to include your object case';
501 code_debug(' Invalid obj type '||x_msg_data);
502 RETURN;
503 END IF;
504
505 IF (p_instance_pk1_value IS NOT NULL
506 OR
507 p_instance_pk2_value IS NOT NULL
508 OR
509 p_instance_pk3_value IS NOT NULL
510 OR
511 p_instance_pk4_value IS NOT NULL
512 OR
513 p_instance_pk5_value IS NOT NULL
514 ) THEN
515 code_debug(' Binding Pk Values ');
516 --
517 -- assumed that the delete table column names are same as that primary column names.
518 --
519 IF p_instance_pk1_value IS NOT NULL THEN
520 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk1_column_name||' IS NULL';
521 ELSE
522 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk1_column_name||' = ' ||p_instance_pk1_value;
523 END IF;
524 IF l_object_row.pk2_column_name IS NOT NULL THEN
525 IF p_instance_pk2_value IS NULL THEN
526 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk2_column_name||' IS NULL';
527 ELSE
528 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk2_column_name||' = ' ||p_instance_pk2_value;
529 END IF;
530 END IF;
531 IF l_object_row.pk3_column_name IS NOT NULL THEN
532 IF p_instance_pk3_value IS NULL THEN
533 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk3_column_name||' IS NULL';
534 ELSE
535 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk3_column_name||' = ' ||p_instance_pk3_value;
536 END IF;
537 END IF;
538 IF l_object_row.pk4_column_name IS NOT NULL THEN
539 IF p_instance_pk4_value IS NULL THEN
540 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk4_column_name||' IS NULL';
541 ELSE
542 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk4_column_name||' = ' ||p_instance_pk4_value;
543 END IF;
544 END IF;
545 IF l_object_row.pk5_column_name IS NOT NULL THEN
546 IF p_instance_pk5_value IS NULL THEN
547 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk5_column_name||' IS NULL';
548 ELSE
549 l_dynamic_sql := l_dynamic_sql ||' AND '||l_object_row.pk5_column_name||' = ' ||p_instance_pk5_value;
550 END IF;
551 END IF;
552 END IF;
553
554 l_dynamic_sql_tl := 'DELETE '||l_delete_table_tl||l_dynamic_sql;
555 code_debug(' Deleting from the TL table ');
556 EXECUTE IMMEDIATE l_dynamic_sql_tl;
557 l_dynamic_sql_b := 'DELETE '||l_delete_table_b||l_dynamic_sql;
558 code_debug(' Deleting from the base table ');
559 EXECUTE IMMEDIATE l_dynamic_sql_b;
560 x_return_status := FND_API.G_RET_STS_SUCCESS;
561 EXCEPTION
562 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
563 code_debug( 'EXCEPTION why this ');
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
565 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
566 ,p_count => x_msg_count
567 ,p_data => x_msg_data);
568 WHEN OTHERS THEN
569 code_debug( 'EXCEPTION others ');
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571 fnd_message.set_name('EGO', 'EGO_PLSQL_ERR');
572 fnd_message.set_token('PKG_NAME', G_PKG_NAME);
573 fnd_message.set_token('API_NAME', l_api_name);
574 fnd_message.set_token('SQL_ERR_MSG', SQLERRM);
575 x_msg_data := fnd_message.get;
576 x_msg_count := 1;
577 END Deleting_Obj_Pending_Changes;
578 ----------------------------------------------------------------------
579
580
581 END EGO_ITEMS_ATTRS_CHANGES_PKG;
582
[Home] [Help]