PACKAGE BODY: APPS.EGO_ITM_GTN_MUL_CHG_ATTRS_PKG

Source

1 package body EGO_ITM_GTN_MUL_CHG_ATTRS_PKG as
2 /* $Header: EGOCGTMB.pls 120.0 2005/11/04 16:49 sshrikha noship $ */
3 
4 procedure ADD_LANGUAGE
5 is
6 begin
7   delete from EGO_GTN_MUL_ATTR_CHG_TL T
8   where not exists
9     (select NULL
10     from EGO_GTN_MUL_ATTR_CHG_B B
11     where B.EXTENSION_ID = T.EXTENSION_ID
12     );
13 
14   update EGO_GTN_MUL_ATTR_CHG_TL T set (
15       HANDLING_INSTRUCTIONS_CODE,
16       DANGEROUS_GOODS_TECHNICAL_NAME
17     ) = (select
18       B.HANDLING_INSTRUCTIONS_CODE,
19       B.DANGEROUS_GOODS_TECHNICAL_NAME
20     from EGO_GTN_MUL_ATTR_CHG_TL B
21     where B.EXTENSION_ID = T.EXTENSION_ID
22     and B.LANGUAGE = T.SOURCE_LANG)
23   where (
24       T.EXTENSION_ID,
25       T.LANGUAGE
26   ) in (select
27       SUBT.EXTENSION_ID,
28       SUBT.LANGUAGE
29     from EGO_GTN_MUL_ATTR_CHG_TL SUBB, EGO_GTN_MUL_ATTR_CHG_TL SUBT
30     where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
31     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
32     and (SUBB.HANDLING_INSTRUCTIONS_CODE <> SUBT.HANDLING_INSTRUCTIONS_CODE
33       or (SUBB.HANDLING_INSTRUCTIONS_CODE is null and SUBT.HANDLING_INSTRUCTIONS_CODE is not null)
34       or (SUBB.HANDLING_INSTRUCTIONS_CODE is not null and SUBT.HANDLING_INSTRUCTIONS_CODE is null)
35       or SUBB.DANGEROUS_GOODS_TECHNICAL_NAME <> SUBT.DANGEROUS_GOODS_TECHNICAL_NAME
36       or (SUBB.DANGEROUS_GOODS_TECHNICAL_NAME is null and SUBT.DANGEROUS_GOODS_TECHNICAL_NAME is not null)
37       or (SUBB.DANGEROUS_GOODS_TECHNICAL_NAME is not null and SUBT.DANGEROUS_GOODS_TECHNICAL_NAME is null)
38   ));
39 
40   insert into EGO_GTN_MUL_ATTR_CHG_TL (
41     EXTENSION_ID,
42     INVENTORY_ITEM_ID,
43     ORGANIZATION_ID,
44     ATTR_GROUP_ID,
45     CREATED_BY,
46     CREATION_DATE,
47     LAST_UPDATED_BY,
48     LAST_UPDATE_DATE,
49     LAST_UPDATE_LOGIN,
50     ITEM_CATALOG_GROUP_ID,
51     REVISION_ID,
52     HANDLING_INSTRUCTIONS_CODE,
53     DANGEROUS_GOODS_TECHNICAL_NAME,
54     LANGUAGE,
55     SOURCE_LANG,
56     CHANGE_ID,
57     CHANGE_LINE_ID,
58     IMPLEMENTATION_DATE,
59     ACD_TYPE
60   ) select /*+ ORDERED */
61     B.EXTENSION_ID,
62     B.INVENTORY_ITEM_ID,
63     B.ORGANIZATION_ID,
64     B.ATTR_GROUP_ID,
65     B.CREATED_BY,
66     B.CREATION_DATE,
67     B.LAST_UPDATED_BY,
68     B.LAST_UPDATE_DATE,
69     B.LAST_UPDATE_LOGIN,
70     B.ITEM_CATALOG_GROUP_ID,
71     B.REVISION_ID,
72     B.HANDLING_INSTRUCTIONS_CODE,
73     B.DANGEROUS_GOODS_TECHNICAL_NAME,
74     L.LANGUAGE_CODE,
75     B.SOURCE_LANG,
76     B.CHANGE_ID,
77     B.CHANGE_LINE_ID,
78     B.IMPLEMENTATION_DATE,
79     B.ACD_TYPE
80   from EGO_GTN_MUL_ATTR_CHG_TL B, FND_LANGUAGES L
81   where L.INSTALLED_FLAG in ('I', 'B')
82   and B.LANGUAGE = userenv('LANG')
83   and not exists
84     (select NULL
85     from EGO_GTN_MUL_ATTR_CHG_TL T
86     where T.EXTENSION_ID = B.EXTENSION_ID
87     and T.LANGUAGE = L.LANGUAGE_CODE);
88 end ADD_LANGUAGE;
89 
90 end EGO_ITM_GTN_MUL_CHG_ATTRS_PKG;