1 PACKAGE BODY IEX_CHECKLIST_ITEMS_PKG AS
2 /* $Header: iextchkb.pls 120.0 2005/06/22 14:26:05 jypark noship $ */
3 PG_DEBUG NUMBER(2) ;
4
5 PROCEDURE ADD_LANGUAGE
6 IS
7 BEGIN
8 DELETE FROM IEX_CHECKLIST_ITEMS_TL T
9 WHERE NOT EXISTS
10 (SELECT NULL
11 FROM IEX_CHECKLIST_ITEMS_B B
12 WHERE B.CHECKLIST_ITEM_ID = T.CHECKLIST_ITEM_ID
13 );
14
15 UPDATE IEX_CHECKLIST_ITEMS_TL T SET
16 (CHECKLIST_ITEM_TEXT ) =
17 (SELECT
18 B.CHECKLIST_ITEM_TEXT
19 FROM IEX_CHECKLIST_ITEMS_TL B
20 WHERE B.CHECKLIST_ITEM_ID = T.CHECKLIST_ITEM_ID
21 AND B.LANGUAGE = T.SOURCE_LANG)
22 WHERE (T.CHECKLIST_ITEM_ID,
23 T.LANGUAGE) IN
24 (SELECT
25 SUBT.CHECKLIST_ITEM_ID,
26 SUBT.LANGUAGE
27 FROM IEX_CHECKLIST_ITEMS_TL SUBB, IEX_CHECKLIST_ITEMS_TL SUBT
28 WHERE SUBB.CHECKLIST_ITEM_ID = SUBT.CHECKLIST_ITEM_ID
29 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
30 AND (SUBB.CHECKLIST_ITEM_TEXT <> SUBT.CHECKLIST_ITEM_TEXT)
31 OR (SUBB.CHECKLIST_ITEM_TEXT IS NULL AND SUBT.CHECKLIST_ITEM_TEXT IS NOT NULL)
32 OR (SUBB.CHECKLIST_ITEM_TEXT IS NOT NULL AND SUBT.CHECKLIST_ITEM_TEXT IS NULL)
33 );
34
35 INSERT INTO IEX_CHECKLIST_ITEMS_TL (
36 CHECKLIST_ITEM_ID,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN,
40 CREATION_DATE,
41 CREATED_BY,
42 CHECKLIST_ITEM_TEXT,
43 DESCRIPTION,
44 LANGUAGE,
45 SOURCE_LANG
46 ) SELECT
47 B.CHECKLIST_ITEM_ID,
48 B.LAST_UPDATE_DATE,
49 B.LAST_UPDATED_BY,
50 B.LAST_UPDATE_LOGIN,
51 B.CREATION_DATE,
52 B.CREATED_BY,
53 B.CHECKLIST_ITEM_TEXT,
54 B.DESCRIPTION,
55 L.LANGUAGE_CODE,
56 B.SOURCE_LANG
57 FROM IEX_CHECKLIST_ITEMS_TL B, FND_LANGUAGES L
58 WHERE L.INSTALLED_FLAG IN ('I', 'B')
59 AND B.LANGUAGE = userenv('LANG')
60 AND NOT EXISTS
61 (SELECT NULL
62 FROM IEX_CHECKLIST_ITEMS_TL T
63 WHERE T.CHECKLIST_ITEM_ID = B.CHECKLIST_ITEM_ID
64 AND T.LANGUAGE = L.LANGUAGE_CODE);
65 END ADD_LANGUAGE;
66
67 BEGIN
68 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
69
70 END IEX_CHECKLIST_ITEMS_PKG;