PACKAGE BODY: APPS.AMW_WORK_CATEGORIES_PKG

Source

1 package body AMW_WORK_CATEGORIES_PKG as
2 /* $Header: amwtwctb.pls 120.0 2005/10/26 07:07:11 appldev noship $ */
3 
4 
5 procedure ADD_LANGUAGE
6 is
7 begin
8   delete from AMW_WORK_CATEGORIES_TL T
9   where not exists
10     (select NULL
11     from AMW_WORK_CATEGORIES_B B
12     where B.CATEGORY_ID = T.CATEGORY_ID
13     );
14 
15   update AMW_WORK_CATEGORIES_TL T set (
16       CATEGORY_NAME,
17       PLURAL_NAME,
18       DESCRIPTION
19     ) = (select
20       B.CATEGORY_NAME,
21       B.PLURAL_NAME,
22       B.DESCRIPTION
23     from AMW_WORK_CATEGORIES_TL B
24     where B.CATEGORY_ID = T.CATEGORY_ID
25     and B.LANGUAGE = T.SOURCE_LANG)
26   where (
27       T.CATEGORY_ID,
28       T.LANGUAGE
29   ) in (select
30       SUBT.CATEGORY_ID,
31       SUBT.LANGUAGE
32     from AMW_WORK_CATEGORIES_TL SUBB, AMW_WORK_CATEGORIES_TL SUBT
33     where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
34     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
35     and (SUBB.CATEGORY_NAME <> SUBT.CATEGORY_NAME
36       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
37       or SUBB.PLURAL_NAME <> SUBT.PLURAL_NAME
38       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
39       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
40       or (SUBB.PLURAL_NAME is null and SUBT.PLURAL_NAME is not null)
41       or (SUBB.PLURAL_NAME is not null and SUBT.PLURAL_NAME is null)
42   ));
43 
44   insert into AMW_WORK_CATEGORIES_TL (
45     OBJECT_VERSION_NUMBER,
46     LAST_UPDATED_BY,
47     LAST_UPDATE_DATE,
48     CREATED_BY,
49     DESCRIPTION,
50     SOURCE_LANG,
51     CREATION_DATE,
52     LAST_UPDATE_LOGIN,
53     CATEGORY_ID,
54     CATEGORY_NAME,
55     PLURAL_NAME,
56     LANGUAGE
57  ) select /*+ ORDERED */
58     B.OBJECT_VERSION_NUMBER,
59     B.LAST_UPDATED_BY,
60     B.LAST_UPDATE_DATE,
61     B.CREATED_BY,
62     B.DESCRIPTION,
63     B.SOURCE_LANG,
64     B.CREATION_DATE,
65     B.LAST_UPDATE_LOGIN,
66     B.CATEGORY_ID,
67     B.CATEGORY_NAME,
68     B.PLURAL_NAME,
69     L.LANGUAGE_CODE
70   from AMW_WORK_CATEGORIES_TL B, FND_LANGUAGES L
71   where L.INSTALLED_FLAG in ('I', 'B')
72   and B.LANGUAGE = userenv('LANG')
73   and not exists
74     (select NULL
75     from AMW_WORK_CATEGORIES_TL T
76     where T.CATEGORY_ID = B.CATEGORY_ID
77     and T.LANGUAGE = L.LANGUAGE_CODE);
78 end ADD_LANGUAGE;
79 
80 
81 end AMW_WORK_CATEGORIES_PKG;