PACKAGE BODY: APPS.EGO_MTL_SY_ITEMS_CHG_PKG

Source

1 PACKAGE BODY EGO_MTL_SY_ITEMS_CHG_PKG AS
2 /* $Header: EGOCMSIB.pls 120.0 2005/11/04 16:51:32 sshrikha noship $ */
3 G_PKG_NAME       CONSTANT   VARCHAR2(30)  :=  'EGO_MTL_SY_ITEMS_CHG_PKG';
4 
5 -- =============================================================================
6 --                   Package variables, constants and cursors
7 -- =============================================================================
8 
9 
10 -- ------------------- ADD_LANGUAGE --------------------
11 
12 PROCEDURE ADD_LANGUAGE IS
13 BEGIN
14 
15 /*   DELETE FROM MTL_SYSTEM_ITEMS_TL T
16    WHERE  NOT EXISTS ( SELECT NULL
17                        FROM   MTL_SYSTEM_ITEMS_B  B
18                        WHERE  B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
19                        AND    B.ORGANIZATION_ID   = T.ORGANIZATION_ID);
20 
21    UPDATE MTL_SYSTEM_ITEMS_TL T
22    SET(  DESCRIPTION
23       ,  LONG_DESCRIPTION) = (SELECT  ltrim(rtrim(B.DESCRIPTION))
24    			           ,  ltrim(rtrim(B.LONG_DESCRIPTION))
25 		              FROM  MTL_SYSTEM_ITEMS_TL  B
26                               WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
27                               AND   B.ORGANIZATION_ID   = T.ORGANIZATION_ID
28                               AND   B.LANGUAGE          = T.SOURCE_LANG)
29    WHERE(T.INVENTORY_ITEM_ID
30       ,  T.ORGANIZATION_ID
31       ,  T.LANGUAGE) IN (SELECT  SUBT.INVENTORY_ITEM_ID,
32                                  SUBT.ORGANIZATION_ID,
33                                  SUBT.LANGUAGE
34                          FROM    MTL_SYSTEM_ITEMS_TL  SUBB,
35                                  MTL_SYSTEM_ITEMS_TL  SUBT
36                          WHERE   SUBB.INVENTORY_ITEM_ID = SUBT.INVENTORY_ITEM_ID
37                          AND     SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
38                          AND     SUBB.LANGUAGE = SUBT.SOURCE_LANG
39                          AND  (( SUBB.DESCRIPTION <> SUBT.DESCRIPTION
40                                 or ( SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
41                                 or ( SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
42                          OR   ( SUBB.LONG_DESCRIPTION <> SUBT.LONG_DESCRIPTION
43                            or ( SUBB.LONG_DESCRIPTION is null and SUBT.LONG_DESCRIPTION is not null )
44                            or ( SUBB.LONG_DESCRIPTION is not null and SUBT.LONG_DESCRIPTION is null ))));
45 
46 */
47    INSERT INTO EGO_MTL_SY_ITEMS_CHG_TL  (
48     INVENTORY_ITEM_ID,
49     ORGANIZATION_ID,
50     DESCRIPTION,
51     LONG_DESCRIPTION,
52     LAST_UPDATE_DATE,
53     LAST_UPDATED_BY,
54     CREATION_DATE,
55     CREATED_BY,
56     LAST_UPDATE_LOGIN,
57     LANGUAGE,
58     SOURCE_LANG,
59     CHANGE_ID,
60     CHANGE_LINE_ID,
61     ACD_TYPE,
62     IMPLEMENTATION_DATE)
63    SELECT
64     B.INVENTORY_ITEM_ID,
65     B.ORGANIZATION_ID,
66     ltrim(rtrim(B.DESCRIPTION)),
67     ltrim(rtrim(B.LONG_DESCRIPTION)),
68     B.LAST_UPDATE_DATE,
69     B.LAST_UPDATED_BY,
70     B.CREATION_DATE,
71     B.CREATED_BY,
72     B.LAST_UPDATE_LOGIN,
73     L.LANGUAGE_CODE,
74     B.SOURCE_LANG,
75     B.CHANGE_ID,
76     B.CHANGE_LINE_ID,
77     B.ACD_TYPE,
78     B.IMPLEMENTATION_DATE
79    FROM EGO_MTL_SY_ITEMS_CHG_TL  B,
80         FND_LANGUAGES        L
81    WHERE L.INSTALLED_FLAG in ('I', 'B')
82    AND   B.LANGUAGE = userenv('LANG')
83    AND   NOT EXISTS( SELECT NULL
84                      FROM   EGO_MTL_SY_ITEMS_CHG_TL  T
85                      WHERE  T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
86                      AND    T.ORGANIZATION_ID   = B.ORGANIZATION_ID
87                      AND    T.LANGUAGE          = L.LANGUAGE_CODE);
88 
89 
90    COMMIT;
91 
92 END ADD_LANGUAGE;
93 
94 
95 
96 
97 
98 
99 
100 END EGO_MTL_SY_ITEMS_CHG_PKG;