1 package body BSC_SYS_DIM_GROUPS_PKG as
2 /* $Header: BSCSDMGB.pls 120.0 2005/06/01 16:49:55 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DIM_GROUP_ID in NUMBER,
6 X_SHORT_NAME in VARCHAR2,
7 X_NAME in VARCHAR2,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from BSC_SYS_DIM_GROUPS_TL
15 where DIM_GROUP_ID = X_DIM_GROUP_ID
16 and LANGUAGE = userenv('LANG')
17 ;
18 begin
19 insert into BSC_SYS_DIM_GROUPS_TL (
20 DIM_GROUP_ID,
21 SHORT_NAME,
22 NAME,
23 LANGUAGE,
24 SOURCE_LANG,
25 CREATION_DATE ,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY ,
29 LAST_UPDATE_LOGIN
30 ) select
31 X_DIM_GROUP_ID,
32 X_SHORT_NAME,
33 X_NAME,
34 L.LANGUAGE_CODE,
35 userenv('LANG'),
36 X_CREATION_DATE ,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY ,
40 X_LAST_UPDATE_LOGIN
41 from FND_LANGUAGES L
42 where L.INSTALLED_FLAG in ('I', 'B')
43 and not exists
44 (select NULL
45 from BSC_SYS_DIM_GROUPS_TL T
46 where T.DIM_GROUP_ID = X_DIM_GROUP_ID
47 and T.LANGUAGE = L.LANGUAGE_CODE);
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOCK_ROW (
60 X_DIM_GROUP_ID in NUMBER,
61 X_NAME in VARCHAR2
62 ) is
63 cursor c1 is select
64 NAME,
65 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
66 from BSC_SYS_DIM_GROUPS_TL
67 where DIM_GROUP_ID = X_DIM_GROUP_ID
68 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
69 for update of DIM_GROUP_ID nowait;
70 begin
71 for tlinfo in c1 loop
72 if (tlinfo.BASELANG = 'Y') then
73 if ( (tlinfo.NAME = X_NAME)
74 ) then
75 null;
76 else
77 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
78 app_exception.raise_exception;
79 end if;
80 end if;
81 end loop;
82 return;
83 end LOCK_ROW;
84
85 procedure UPDATE_ROW (
86 X_DIM_GROUP_ID in NUMBER,
87 X_NAME in VARCHAR2,
88 X_CREATION_DATE in DATE,
89 X_CREATED_BY in NUMBER,
90 X_LAST_UPDATE_DATE in DATE,
91 X_LAST_UPDATED_BY in NUMBER,
92 X_LAST_UPDATE_LOGIN in NUMBER
93 ) is
94 begin
95 update BSC_SYS_DIM_GROUPS_TL set
96 NAME = X_NAME,
97 SOURCE_LANG = userenv('LANG'),
98 LAST_UPDATE_DATE = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_DATE,LAST_UPDATE_DATE),
99 LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATED_BY,LAST_UPDATED_BY),
100 LAST_UPDATE_LOGIN = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN)
101 where DIM_GROUP_ID = X_DIM_GROUP_ID
102 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
103
104 if (sql%notfound) then
105 raise no_data_found;
106 end if;
107 end UPDATE_ROW;
108
109 procedure DELETE_ROW (
110 X_DIM_GROUP_ID in NUMBER
111 ) is
112 begin
113 delete from BSC_SYS_DIM_GROUPS_TL
114 where DIM_GROUP_ID = X_DIM_GROUP_ID;
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119
120 end DELETE_ROW;
121
122 procedure ADD_LANGUAGE
123 is
124 begin
125 update BSC_SYS_DIM_GROUPS_TL T set (
126 NAME
127 ) = (select
128 B.NAME
129 from BSC_SYS_DIM_GROUPS_TL B
130 where B.DIM_GROUP_ID = T.DIM_GROUP_ID
131 and B.LANGUAGE = T.SOURCE_LANG)
132 where (
133 T.DIM_GROUP_ID,
134 T.LANGUAGE
135 ) in (select
136 SUBT.DIM_GROUP_ID,
137 SUBT.LANGUAGE
138 from BSC_SYS_DIM_GROUPS_TL SUBB, BSC_SYS_DIM_GROUPS_TL SUBT
139 where SUBB.DIM_GROUP_ID = SUBT.DIM_GROUP_ID
140 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
141 and (SUBB.NAME <> SUBT.NAME
142 ));
143
144 insert into BSC_SYS_DIM_GROUPS_TL (
145 DIM_GROUP_ID,
146 SHORT_NAME,
147 NAME,
148 LANGUAGE,
149 SOURCE_LANG,
150 CREATION_DATE ,
151 CREATED_BY,
152 LAST_UPDATE_DATE,
153 LAST_UPDATED_BY
154 ) select
155 B.DIM_GROUP_ID,
156 B.SHORT_NAME,
157 B.NAME,
158 L.LANGUAGE_CODE,
159 B.SOURCE_LANG,
160 B.CREATION_DATE ,
161 B.CREATED_BY,
162 B.LAST_UPDATE_DATE,
163 B.LAST_UPDATED_BY
164 from BSC_SYS_DIM_GROUPS_TL B, FND_LANGUAGES L
165 where L.INSTALLED_FLAG in ('I', 'B')
166 and B.LANGUAGE = userenv('LANG')
167 and not exists
168 (select NULL
169 from BSC_SYS_DIM_GROUPS_TL T
170 where T.DIM_GROUP_ID = B.DIM_GROUP_ID
171 and T.LANGUAGE = L.LANGUAGE_CODE);
172 end ADD_LANGUAGE;
173
174 end BSC_SYS_DIM_GROUPS_PKG;
[Home] [Help]