PACKAGE BODY: APPS.LNS_LOAN_PRODUCTS_ALL_PKG

Source

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