[Home] [Help]
PACKAGE BODY: APPS.AMS_COMPETITOR_PRODUCTS_B_PKG
Source
1 PACKAGE BODY AMS_COMPETITOR_PRODUCTS_B_PKG as
2 /* $Header: amstcprb.pls 120.3 2005/11/14 02:07:27 inanaiah ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_COMPETITOR_PRODUCTS_B_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- 01-Oct-2001 musman created
12 -- 05-Nov-2001 musman Commented out the reference to security_group_id
13 -- 10-Sep-2003 Musman Added Changes reqd for interest type to category
14 -- 04-Aug-2005 inanaiah R12 change - added a DFF
15 -- NOTE
16 --
17 -- End of Comments
18 -- ===============================================================
19
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_COMPETITOR_PRODUCTS_B_PKG';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstcprb.pls';
23
24
25 ----------------------------------------------------------
26 ---- MEDIA ----
27 ----------------------------------------------------------
28
29 -- ========================================================
30 --
31 -- NAME
32 -- createInsertBody
33 --
34 -- PURPOSE
35 --
36 -- NOTES
37 --
38 -- HISTORY
39 --
40 -- ========================================================
41 PROCEDURE Insert_Row(
42 px_competitor_product_id IN OUT NOCOPY NUMBER,
43 px_object_version_number IN OUT NOCOPY NUMBER,
44 p_last_update_date DATE,
45 p_last_updated_by NUMBER,
46 p_creation_date DATE,
47 p_created_by NUMBER,
48 p_last_update_login NUMBER,
49 p_competitor_party_id NUMBER,
50 p_competitor_product_code VARCHAR2,
51 p_interest_type_id NUMBER,
52 p_inventory_item_id NUMBER,
53 p_organization_id NUMBER,
54 p_comp_product_url VARCHAR2,
55 p_original_system_ref VARCHAR2,
56 --p_security_group_id NUMBER,
57 p_competitor_product_name VARCHAR2,
58 p_description VARCHAR2,
59 p_start_date DATE,
60 p_end_date DATE,
61 p_category_id NUMBER,
62 p_category_set_id NUMBER,
63 p_context VARCHAR2,
64 p_attribute1 VARCHAR2,
65 p_attribute2 VARCHAR2,
66 p_attribute3 VARCHAR2,
67 p_attribute4 VARCHAR2,
68 p_attribute5 VARCHAR2,
69 p_attribute6 VARCHAR2,
70 p_attribute7 VARCHAR2,
71 p_attribute8 VARCHAR2,
72 p_attribute9 VARCHAR2,
73 p_attribute10 VARCHAR2,
74 p_attribute11 VARCHAR2,
75 p_attribute12 VARCHAR2,
76 p_attribute13 VARCHAR2,
77 p_attribute14 VARCHAR2,
78 p_attribute15 VARCHAR2
79 )
80 IS
81 x_rowid VARCHAR2(30);
82
83
84 BEGIN
85
86
87 px_object_version_number := 1;
88
89
90 INSERT INTO AMS_COMPETITOR_PRODUCTS_B(
91 competitor_product_id,
92 object_version_number,
93 last_update_date,
94 last_updated_by,
95 creation_date,
96 created_by,
97 last_update_login,
98 competitor_party_id,
99 competitor_product_code,
100 interest_type_id,
101 inventory_item_id,
102 organization_id,
103 comp_product_url,
104 original_system_ref
105 --,security_group_id
106 ,start_date
107 ,end_date
108 ,category_id
109 ,category_set_id
110 , context
111 , attribute1
112 , attribute2
113 , attribute3
114 , attribute4
115 , attribute5
116 , attribute6
117 , attribute7
118 , attribute8
119 , attribute9
120 , attribute10
121 , attribute11
122 , attribute12
123 , attribute13
124 , attribute14
125 , attribute15
126
127
128 ) VALUES (
129 DECODE( px_competitor_product_id, FND_API.g_miss_num, NULL, px_competitor_product_id),
130 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
131 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
132 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
133 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
134 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
135 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
136 DECODE( p_competitor_party_id, FND_API.g_miss_num, NULL, p_competitor_party_id),
137 DECODE( p_competitor_product_code, FND_API.g_miss_char, NULL, p_competitor_product_code),
138 DECODE( p_interest_type_id, FND_API.g_miss_num, NULL, p_interest_type_id),
139 DECODE( p_inventory_item_id, FND_API.g_miss_num, NULL, p_inventory_item_id),
140 DECODE( p_organization_id, FND_API.g_miss_num, NULL, p_organization_id),
141 DECODE( p_comp_product_url, FND_API.g_miss_char, NULL, p_comp_product_url),
142 DECODE( p_original_system_ref, FND_API.g_miss_char, NULL, p_original_system_ref)
143 --,DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id)
144 ,DECODE( p_start_date, FND_API.g_miss_date, NULL, p_start_date)
145 ,DECODE( p_end_date, FND_API.g_miss_date, NULL, p_end_date)
146 ,DECODE( p_category_id, FND_API.g_miss_num, NULL, p_category_id)
147 ,DECODE( p_category_set_id, FND_API.g_miss_num, NULL, p_category_set_id)
148 , DECODE(p_context , FND_API.G_MISS_CHAR , NULL , p_context)
149 , DECODE(p_attribute1 , FND_API.G_MISS_CHAR, NULL , p_attribute1)
150 , DECODE(p_attribute2 , FND_API.G_MISS_CHAR, NULL , p_attribute2)
151 , DECODE(p_attribute3 , FND_API.G_MISS_CHAR, NULL , p_attribute3)
152 , DECODE(p_attribute4 , FND_API.G_MISS_CHAR, NULL , p_attribute4)
153 , DECODE(p_attribute5 , FND_API.G_MISS_CHAR, NULL , p_attribute5)
154 , DECODE(p_attribute6 , FND_API.G_MISS_CHAR, NULL , p_attribute6)
155 , DECODE(p_attribute7 , FND_API.G_MISS_CHAR, NULL , p_attribute7)
156 , DECODE(p_attribute8 , FND_API.G_MISS_CHAR, NULL , p_attribute8)
157 , DECODE(p_attribute9 , FND_API.G_MISS_CHAR, NULL , p_attribute9)
158 , DECODE(p_attribute10 , FND_API.G_MISS_CHAR, NULL , p_attribute10)
159 , DECODE(p_attribute11 , FND_API.G_MISS_CHAR, NULL , p_attribute11)
160 , DECODE(p_attribute12 , FND_API.G_MISS_CHAR, NULL , p_attribute12)
161 , DECODE(p_attribute13 , FND_API.G_MISS_CHAR, NULL , p_attribute13)
162 , DECODE(p_attribute14 , FND_API.G_MISS_CHAR, NULL , p_attribute14)
163 , DECODE(p_attribute15 , FND_API.G_MISS_CHAR, NULL , p_attribute15)
164 );
165
166 INSERT INTO AMS_COMPETITOR_PRODUCTS_TL(
167 competitor_product_id,
168 language,
169 source_lang,
170 last_update_date,
171 last_updated_by,
172 last_update_login,
173 creation_date,
174 created_by,
175 competitor_product_name,
176 description
177 --,security_group_id
178 )
179 SELECT
180 DECODE( px_competitor_product_id, FND_API.g_miss_num, NULL, px_competitor_product_id),
181 l.language_code,
182 USERENV('LANG'),
183 sysdate,
184 FND_GLOBAL.user_id,
185 FND_GLOBAL.conc_login_id,
186 sysdate,
187 FND_GLOBAL.user_id,
188 DECODE( p_competitor_product_name, FND_API.g_miss_char, NULL, p_competitor_product_name),
189 DECODE( p_description, FND_API.g_miss_char, NULL, p_description)
190 --,DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id)
191 FROM fnd_languages l
192 WHERE l.installed_flag IN ('I','B')
193 AND NOT EXISTS(
194 SELECT NULL
195 FROM ams_competitor_products_tl t
196 WHERE t.competitor_product_id = DECODE( px_competitor_product_id, FND_API.g_miss_num, NULL, px_competitor_product_id)
197 AND t.language = l.language_code ) ;
198
199
200 END Insert_Row;
201
202
203 -- ========================================================
204 --
205 -- NAME
206 -- createUpdateBody
207 --
208 -- PURPOSE
209 --
210 -- NOTES
211 --
212 -- HISTORY
213 --
214 -- ========================================================
215 PROCEDURE Update_Row(
216 p_competitor_product_id NUMBER,
217 p_object_version_number NUMBER,
218 p_last_update_date DATE,
219 p_last_updated_by NUMBER,
220 p_creation_date DATE,
221 p_created_by NUMBER,
222 p_last_update_login NUMBER,
223 p_competitor_party_id NUMBER,
224 p_competitor_product_code VARCHAR2,
225 p_interest_type_id NUMBER,
226 p_inventory_item_id NUMBER,
227 p_organization_id NUMBER,
228 p_comp_product_url VARCHAR2,
229 p_original_system_ref VARCHAR2,
230 --p_security_group_id NUMBER,
231 p_competitor_product_name VARCHAR2,
232 p_description VARCHAR2,
233 p_start_date DATE,
234 p_end_date DATE,
235 p_category_id NUMBER,
236 p_category_set_id NUMBER,
237 p_context VARCHAR2,
238 p_attribute1 VARCHAR2,
239 p_attribute2 VARCHAR2,
240 p_attribute3 VARCHAR2,
241 p_attribute4 VARCHAR2,
242 p_attribute5 VARCHAR2,
243 p_attribute6 VARCHAR2,
244 p_attribute7 VARCHAR2,
245 p_attribute8 VARCHAR2,
246 p_attribute9 VARCHAR2,
247 p_attribute10 VARCHAR2,
248 p_attribute11 VARCHAR2,
249 p_attribute12 VARCHAR2,
250 p_attribute13 VARCHAR2,
251 p_attribute14 VARCHAR2,
252 p_attribute15 VARCHAR2
253 )
254 IS
255 BEGIN
256
257 AMS_UTILITY_PVT.debug_message('Pub update: start');
258
259 Update AMS_COMPETITOR_PRODUCTS_B
260 SET
261 competitor_product_id = DECODE( p_competitor_product_id, FND_API.g_miss_num, null, null, competitor_product_id, p_competitor_product_id),
262 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, null, null, object_version_number, p_object_version_number),
263 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, to_date(null), to_date(null), last_update_date, p_last_update_date),
264 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, null, null, last_updated_by, p_last_updated_by),
265 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, to_date(null) , to_date(null), creation_date, p_creation_date),
266 created_by = DECODE( p_created_by, FND_API.g_miss_num, null, null, created_by, p_created_by),
267 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num,null, null, last_update_login, p_last_update_login),
268 competitor_party_id = DECODE( p_competitor_party_id, FND_API.g_miss_num, null, null, competitor_party_id, p_competitor_party_id),
269 competitor_product_code = DECODE( p_competitor_product_code, FND_API.g_miss_char, null, null, competitor_product_code, p_competitor_product_code),
270 interest_type_id = DECODE( p_interest_type_id, FND_API.g_miss_num, null, null, interest_type_id, p_interest_type_id),
271 inventory_item_id = DECODE( p_inventory_item_id, FND_API.g_miss_num, null, p_inventory_item_id),
272 organization_id = DECODE( p_organization_id, FND_API.g_miss_num, null, p_organization_id),
273 comp_product_url = DECODE( p_comp_product_url, FND_API.g_miss_char, null , null, comp_product_url, p_comp_product_url),
274 original_system_ref = DECODE( p_original_system_ref, FND_API.g_miss_char, null , null , original_system_ref, p_original_system_ref)
275 --,security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, null , null , security_group_id, p_security_group_id)
276 ,start_date = DECODE( p_start_date, FND_API.g_miss_date, to_date(null), to_date(null), start_date, p_start_date)
277 ,end_date = DECODE( p_end_date, FND_API.g_miss_date, to_date(null), to_date(null), end_date, p_end_date)
278 ,category_id = DECODE( p_category_id, FND_API.g_miss_num, null, p_category_id)
279 ,category_set_id = DECODE( p_category_set_id, FND_API.g_miss_num, null, p_category_Set_id)
280 , context = DECODE(p_context, FND_API.G_MISS_CHAR, null, null , context, p_context )
281 , attribute1 = DECODE(p_attribute1, FND_API.G_MISS_CHAR, null, null , attribute1 , p_attribute1)
282 , attribute2 = DECODE(p_attribute2, FND_API.G_MISS_CHAR, null, null , attribute2 , p_attribute2)
283 , attribute3 = DECODE(p_attribute3, FND_API.G_MISS_CHAR, null, null , attribute3 , p_attribute3)
284 , attribute4 = DECODE(p_attribute4, FND_API.G_MISS_CHAR, null, null , attribute4 , p_attribute4)
285 , attribute5 = DECODE(p_attribute5, FND_API.G_MISS_CHAR, null, null , attribute5 , p_attribute5)
286 , attribute6 = DECODE(p_attribute6, FND_API.G_MISS_CHAR, null, null , attribute6 , p_attribute6)
290 , attribute10 = DECODE(p_attribute10, FND_API.G_MISS_CHAR, null, null , attribute10 , p_attribute10)
287 , attribute7 = DECODE(p_attribute7, FND_API.G_MISS_CHAR, null, null , attribute7 , p_attribute7)
288 , attribute8 = DECODE(p_attribute8, FND_API.G_MISS_CHAR, null, null , attribute8 , p_attribute8)
289 , attribute9 = DECODE(p_attribute9, FND_API.G_MISS_CHAR, null, null , attribute9 , p_attribute9)
291 , attribute11 = DECODE(p_attribute11, FND_API.G_MISS_CHAR, null, null , attribute12 , p_attribute11)
292 , attribute12 = DECODE(p_attribute12, FND_API.G_MISS_CHAR, null, null , attribute12 , p_attribute12)
293 , attribute13 = DECODE(p_attribute13, FND_API.G_MISS_CHAR, null, null , attribute13 , p_attribute13)
294 , attribute14 = DECODE(p_attribute14, FND_API.G_MISS_CHAR, null, null , attribute14 , p_attribute14)
295 , attribute15 = DECODE(p_attribute15, FND_API.G_MISS_CHAR, null, null , attribute15 , p_attribute15)
296 WHERE COMPETITOR_PRODUCT_ID = p_COMPETITOR_PRODUCT_ID
297 AND object_version_number = p_object_version_number;
298
299 IF (SQL%NOTFOUND) THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303 UPDATE AMS_COMPETITOR_PRODUCTS_TL
304 SET
305 competitor_product_name = DECODE( p_competitor_product_name, FND_API.g_miss_char, competitor_product_name, p_competitor_product_name),
306 description = DECODE(p_description,FND_API.g_miss_char,description,p_description),
307 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
308 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
309 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
310 source_lang = USERENV('LANG')
311 WHERE competitor_product_id = p_competitor_product_id
312 AND USERENV('LANG') IN (language, source_lang);
313
314 IF (SQL%NOTFOUND) THEN
315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
316 END IF;
317
318
319
320 END Update_Row;
321
322
323 -- ========================================================
324 --
325 -- NAME
326 -- createDeleteBody
327 --
328 -- PURPOSE
329 --
330 -- NOTES
331 --
332 -- HISTORY
333 --
334 -- ========================================================
335 PROCEDURE Delete_Row(
336 p_COMPETITOR_PRODUCT_ID NUMBER,
337 p_object_version_number NUMBER)
338 IS
339 BEGIN
340 DELETE FROM AMS_COMPETITOR_PRODUCTS_B
341 WHERE COMPETITOR_PRODUCT_ID = p_COMPETITOR_PRODUCT_ID
342 AND object_version_number = p_object_version_number;
343 If (SQL%NOTFOUND) then
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 End If;
346
347 DELETE FROM AMS_COMPETITOR_PRODUCTS_TL
348 WHERE COMPETITOR_PRODUCT_ID = p_COMPETITOR_PRODUCT_ID;
349 If (SQL%NOTFOUND) then
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 End If;
352
353
354 END Delete_Row ;
355
356
357 -- ========================================================
358 --
359 -- NAME
360 -- createLockBody
361 --
362 -- PURPOSE
363 --
364 -- NOTES
365 --
366 -- HISTORY
367 --
368 -- ========================================================
369 PROCEDURE Lock_Row(
370 p_competitor_product_id NUMBER,
371 p_object_version_number NUMBER,
372 p_last_update_date DATE,
373 p_last_updated_by NUMBER,
374 p_creation_date DATE,
375 p_created_by NUMBER,
376 p_last_update_login NUMBER,
377 p_competitor_party_id NUMBER,
378 p_competitor_product_code VARCHAR2,
379 p_interest_type_id NUMBER,
380 p_inventory_item_id NUMBER,
381 p_organization_id NUMBER,
382 p_comp_product_url VARCHAR2,
383 p_original_system_ref VARCHAR2,
384 --p_security_group_id NUMBER ,
385 p_competitor_product_name VARCHAR2,
386 p_description VARCHAR2,
387 p_start_date DATE,
388 p_end_date DATE,
389 p_category_id NUMBER,
390 p_category_set_id NUMBER
391 )
392
393 IS
394 CURSOR C IS
395 SELECT *
396 FROM AMS_COMPETITOR_PRODUCTS_VL
397 WHERE COMPETITOR_PRODUCT_ID = p_COMPETITOR_PRODUCT_ID
398 and object_version_number = p_object_version_number
399 FOR UPDATE of COMPETITOR_PRODUCT_ID NOWAIT;
400 Recinfo C%ROWTYPE;
401 BEGIN
402 OPEN c;
403 FETCH c INTO Recinfo;
404 If (c%NOTFOUND) then
405 CLOSE c;
406 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
407 APP_EXCEPTION.RAISE_EXCEPTION;
408 END IF;
409 CLOSE C;
410 IF (
411 ( Recinfo.competitor_product_id = p_competitor_product_id)
412 AND ( ( Recinfo.object_version_number = p_object_version_number)
413 OR ( ( Recinfo.object_version_number IS NULL )
414 AND ( p_object_version_number IS NULL )))
415 AND ( ( Recinfo.last_update_date = p_last_update_date)
419 OR ( ( Recinfo.last_updated_by IS NULL )
416 OR ( ( Recinfo.last_update_date IS NULL )
417 AND ( p_last_update_date IS NULL )))
418 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
420 AND ( p_last_updated_by IS NULL )))
421 AND ( ( Recinfo.creation_date = p_creation_date)
422 OR ( ( Recinfo.creation_date IS NULL )
423 AND ( p_creation_date IS NULL )))
424 AND ( ( Recinfo.created_by = p_created_by)
425 OR ( ( Recinfo.created_by IS NULL )
426 AND ( p_created_by IS NULL )))
427 AND ( ( Recinfo.last_update_login = p_last_update_login)
428 OR ( ( Recinfo.last_update_login IS NULL )
429 AND ( p_last_update_login IS NULL )))
430 AND ( ( Recinfo.competitor_party_id = p_competitor_party_id)
431 OR ( ( Recinfo.competitor_party_id IS NULL )
432 AND ( p_competitor_party_id IS NULL )))
433 AND ( ( Recinfo.competitor_product_code = p_competitor_product_code)
434 OR ( ( Recinfo.competitor_product_code IS NULL )
435 AND ( p_competitor_product_code IS NULL )))
436 AND ( ( Recinfo.interest_type_id = p_interest_type_id)
437 OR ( ( Recinfo.interest_type_id IS NULL )
438 AND ( p_interest_type_id IS NULL )))
439 AND ( ( Recinfo.inventory_item_id = p_inventory_item_id)
440 OR ( ( Recinfo.inventory_item_id IS NULL )
441 AND ( p_inventory_item_id IS NULL )))
442 AND ( ( Recinfo.organization_id = p_organization_id)
443 OR ( ( Recinfo.organization_id IS NULL )
444 AND ( p_organization_id IS NULL )))
445 AND ( ( Recinfo.comp_product_url = p_comp_product_url)
446 OR ( ( Recinfo.comp_product_url IS NULL )
447 AND ( p_comp_product_url IS NULL )))
448 AND ( ( Recinfo.original_system_ref = p_original_system_ref)
449 OR ( ( Recinfo.original_system_ref IS NULL )
450 AND ( p_original_system_ref IS NULL )))
451 AND ( ( Recinfo.competitor_product_name = p_competitor_product_name)
452 OR ( ( Recinfo.competitor_product_name IS NULL )
453 AND ( p_competitor_product_name IS NULL )))
454 AND ( ( Recinfo.description = p_description)
455 OR ( ( Recinfo.description IS NULL )
456 AND ( p_description IS NULL )))
457 AND ( ( Recinfo.start_date = p_start_date)
458 OR ( ( Recinfo.start_date IS NULL )
459 AND ( p_start_date IS NULL )))
460 AND ( ( Recinfo.end_date = p_end_date)
461 OR ( ( Recinfo.end_date IS NULL )
462 AND ( p_end_date IS NULL )))
463 AND ( ( Recinfo.category_id = p_category_id)
464 OR ( ( Recinfo.category_id IS NULL )
465 AND ( p_category_id IS NULL )))
466 AND ( ( Recinfo.category_set_id = p_category_set_id)
467 OR ( ( Recinfo.category_set_id IS NULL )
468 AND ( p_category_set_id IS NULL )))
469 ) THEN
470 RETURN;
471 ELSE
472 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
473 APP_EXCEPTION.RAISE_EXCEPTION;
474 END IF;
475 END Lock_Row;
476 -- ===========================================
477 -- ADD_LANGUAGE
478 --=============================================
479 procedure ADD_LANGUAGE
480 is
481 begin
482 delete from AMS_COMPETITOR_PRODUCTS_TL T
483 where not exists
484 (select NULL
485 from AMS_COMPETITOR_PRODUCTS_B B
486 where B.competitor_product_id = T.competitor_product_id
487 );
488
489 update AMS_COMPETITOR_PRODUCTS_TL T set (
490 competitor_product_id
491 ) = (select
492 B.competitor_product_id
493 from AMS_COMPETITOR_PRODUCTS_TL B
494 where B.competitor_product_id = T.competitor_product_id
495 and B.LANGUAGE = T.SOURCE_LANG)
496 where (
497 T.competitor_product_id,
498 T.LANGUAGE
499 ) in (select
500 SUBT.competitor_product_id,
501 SUBT.LANGUAGE
502 from AMS_COMPETITOR_PRODUCTS_TL SUBB, AMS_COMPETITOR_PRODUCTS_TL SUBT
506 ));
503 where SUBB.competitor_product_id = SUBT.competitor_product_id
504 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
505 and (SUBB.competitor_product_id <> SUBT.competitor_product_id
507
508 INSERT INTO AMS_COMPETITOR_PRODUCTS_TL(
509 competitor_product_id,
510 language,
511 source_lang,
512 last_update_date,
513 last_updated_by,
514 last_update_login,
515 creation_date,
516 created_by,
517 competitor_product_name,
518 description
519 --,security_group_id
520 )
521 SELECT
522 B.competitor_product_id,
523 l.language_code,
524 B.SOURCE_LANG,
525 B.LAST_UPDATE_DATE,
526 B.LAST_UPDATED_BY,
527 B.LAST_UPDATE_LOGIN,
528 B.CREATION_DATE,
529 B.CREATED_BY,
530 B.competitor_product_name,
531 B.DESCRIPTION
532 from AMS_COMPETITOR_PRODUCTS_TL B, FND_LANGUAGES L
533 where L.INSTALLED_FLAG in ('I', 'B')
534 and B.LANGUAGE = userenv('LANG')
535 and not exists
536 (select NULL
537 from AMS_COMPETITOR_PRODUCTS_TL T
538 where T.competitor_product_id = B.competitor_product_id
539 and T.LANGUAGE = L.LANGUAGE_CODE);
540
541 end ADD_LANGUAGE;
542
543
544 END AMS_COMPETITOR_PRODUCTS_B_PKG;