PACKAGE BODY: APPS.XLE_HISTORY_COLUMNS_PKG

Source

1 PACKAGE BODY XLE_HISTORY_COLUMNS_PKG AS
2 /* $Header: xlehiscolb.pls 120.0 2005/10/07 13:29:29 rbasker ship $ */
3 
4 PROCEDURE ADD_LANGUAGE IS
5 BEGIN
6 
7  delete from XLE_HISTORY_COLUMNS_TL T
8   where not exists
9     (select NULL
10     from XLE_HISTORY_COLUMNS_B B
11     where B.HISTORY_COLUMN_ID = T.HISTORY_COLUMN_ID
12     );
13 
14  update XLE_HISTORY_COLUMNS_TL T set (
15       SOURCE_COLUMN_PROMPT,
16       SOURCE_TABLE,
17       SOURCE_COLUMN_NAME
18     ) = (select
19       B.SOURCE_COLUMN_PROMPT,
20       B.SOURCE_TABLE,
21       B.SOURCE_COLUMN_NAME
22     from XLE_HISTORY_COLUMNS_TL B
23     where B.HISTORY_COLUMN_ID = T.HISTORY_COLUMN_ID
24     and B.LANGUAGE = T.SOURCE_LANG)
25   where (
26       T.HISTORY_COLUMN_ID,
27       T.LANGUAGE
28   ) in (select
29       SUBT.HISTORY_COLUMN_ID,
30       SUBT.LANGUAGE
31     from XLE_HISTORY_COLUMNS_TL SUBB, XLE_HISTORY_COLUMNS_TL SUBT
32     where SUBB.HISTORY_COLUMN_ID = SUBT.HISTORY_COLUMN_ID
33     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
34     and (SUBB.SOURCE_COLUMN_PROMPT <> SUBT.SOURCE_COLUMN_PROMPT
35       or SUBB.SOURCE_TABLE <> SUBT.SOURCE_TABLE
36       or SUBB.SOURCE_COLUMN_NAME <> SUBT.SOURCE_COLUMN_NAME
37   ));
38 
39 insert into XLE_HISTORY_COLUMNS_TL (
40     HISTORY_COLUMN_ID,
41     SOURCE_COLUMN_PROMPT,
42     LAST_UPDATED_BY,
43     CREATION_DATE,
44     LAST_UPDATE_LOGIN,
45     LAST_UPDATE_DATE,
46     CREATED_BY,
47     SOURCE_TABLE,
48     SOURCE_COLUMN_NAME,
49     LANGUAGE,
50     SOURCE_LANG
51   ) select /*+ ORDERED */
52     B.HISTORY_COLUMN_ID,
53     B.SOURCE_COLUMN_PROMPT,
54     B.LAST_UPDATED_BY,
55     B.CREATION_DATE,
56     B.LAST_UPDATE_LOGIN,
57     B.LAST_UPDATE_DATE,
58     B.CREATED_BY,
59     B.SOURCE_TABLE,
60     B.SOURCE_COLUMN_NAME,
61     L.LANGUAGE_CODE,
62     B.SOURCE_LANG
63   from XLE_HISTORY_COLUMNS_TL B, FND_LANGUAGES L
64   where L.INSTALLED_FLAG in ('I', 'B')
65   and B.LANGUAGE = userenv('LANG')
66   and not exists
67     (select NULL
68     from XLE_HISTORY_COLUMNS_TL T
69     where T.HISTORY_COLUMN_ID = B.HISTORY_COLUMN_ID
70     and T.LANGUAGE = L.LANGUAGE_CODE);
71 end ADD_LANGUAGE;
72 
73 END  XLE_HISTORY_COLUMNS_PKG;