1 package body BSC_TAB_CSF_PKG as
2 /* $Header: BSCTABCB.pls 115.6 2003/02/12 14:29:45 adeulgao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TAB_ID in NUMBER,
6 X_CSF_ID in NUMBER,
7 X_CSF_TYPE in NUMBER,
8 X_INTERMEDIATE_FLAG in NUMBER,
9 X_NAME in VARCHAR2,
10 X_HELP in VARCHAR2
11 ) is
12 cursor C is select ROWID from BSC_TAB_CSF_B
13 where TAB_ID = X_TAB_ID
14 and CSF_ID = X_CSF_ID
15 ;
16 begin
17 insert into BSC_TAB_CSF_B (
18 TAB_ID,
19 CSF_ID,
20 CSF_TYPE,
21 INTERMEDIATE_FLAG
22 ) values (
23 X_TAB_ID,
24 X_CSF_ID,
25 X_CSF_TYPE,
26 X_INTERMEDIATE_FLAG
27 );
28
29 insert into BSC_TAB_CSF_TL (
30 TAB_ID,
31 CSF_ID,
32 NAME,
33 HELP,
34 LANGUAGE,
35 SOURCE_LANG
36 ) select
37 X_TAB_ID,
38 X_CSF_ID,
39 X_NAME,
40 X_HELP,
41 L.LANGUAGE_CODE,
42 userenv('LANG')
43 from FND_LANGUAGES L
44 where L.INSTALLED_FLAG in ('I', 'B')
45 and not exists
46 (select NULL
47 from BSC_TAB_CSF_TL T
48 where T.TAB_ID = X_TAB_ID
49 and T.CSF_ID = X_CSF_ID
50 and T.LANGUAGE = L.LANGUAGE_CODE);
51
52 open c;
53 fetch c into X_ROWID;
54 if (c%notfound) then
55 close c;
56 raise no_data_found;
57 end if;
58 close c;
59
60 end INSERT_ROW;
61
62 procedure LOCK_ROW (
63 X_TAB_ID in NUMBER,
64 X_CSF_ID in NUMBER,
65 X_CSF_TYPE in NUMBER,
66 X_INTERMEDIATE_FLAG in NUMBER,
67 X_NAME in VARCHAR2,
68 X_HELP in VARCHAR2
69 ) is
70 cursor c is select
71 CSF_TYPE,
72 INTERMEDIATE_FLAG
73 from BSC_TAB_CSF_B
74 where TAB_ID = X_TAB_ID
75 and CSF_ID = X_CSF_ID
76 for update of TAB_ID nowait;
77 recinfo c%rowtype;
78
79 cursor c1 is select
80 NAME,
81 HELP,
82 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
83 from BSC_TAB_CSF_TL
84 where TAB_ID = X_TAB_ID
85 and CSF_ID = X_CSF_ID
86 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
87 for update of TAB_ID nowait;
88 begin
89 open c;
90 fetch c into recinfo;
91 if (c%notfound) then
92 close c;
93 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
94 app_exception.raise_exception;
95 end if;
96 close c;
97 if ( ((recinfo.CSF_TYPE = X_CSF_TYPE)
98 OR ((recinfo.CSF_TYPE is null) AND (X_CSF_TYPE is null)))
99 AND (recinfo.INTERMEDIATE_FLAG = X_INTERMEDIATE_FLAG)
100 ) then
101 null;
102 else
103 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
104 app_exception.raise_exception;
105 end if;
106
107 for tlinfo in c1 loop
108 if (tlinfo.BASELANG = 'Y') then
109 if ( ((tlinfo.NAME = X_NAME)
110 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
111 AND ((tlinfo.HELP = X_HELP)
112 OR ((tlinfo.HELP is null) AND (X_HELP is null)))
113 ) then
114 null;
115 else
116 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
117 app_exception.raise_exception;
118 end if;
119 end if;
120 end loop;
121 return;
122 end LOCK_ROW;
123
124 procedure UPDATE_ROW (
125 X_TAB_ID in NUMBER,
126 X_CSF_ID in NUMBER,
127 X_CSF_TYPE in NUMBER,
128 X_INTERMEDIATE_FLAG in NUMBER,
129 X_NAME in VARCHAR2,
130 X_HELP in VARCHAR2
131 ) is
132 begin
133 update BSC_TAB_CSF_B set
134 CSF_TYPE = X_CSF_TYPE,
135 INTERMEDIATE_FLAG = X_INTERMEDIATE_FLAG
136 where TAB_ID = X_TAB_ID
137 and CSF_ID = X_CSF_ID;
138
139 if (sql%notfound) then
140 raise no_data_found;
141 end if;
142
143 update BSC_TAB_CSF_TL set
144 NAME = X_NAME,
145 HELP = X_HELP,
146 SOURCE_LANG = userenv('LANG')
147 where TAB_ID = X_TAB_ID
148 and CSF_ID = X_CSF_ID
149 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
150
151 if (sql%notfound) then
152 raise no_data_found;
153 end if;
154 end UPDATE_ROW;
155
156 procedure DELETE_ROW (
157 X_TAB_ID in NUMBER,
158 X_CSF_ID in NUMBER
159 ) is
160 begin
161 delete from BSC_TAB_CSF_TL
162 where TAB_ID = X_TAB_ID
163 and CSF_ID = X_CSF_ID;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169 delete from BSC_TAB_CSF_B
170 where TAB_ID = X_TAB_ID
171 and CSF_ID = X_CSF_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end DELETE_ROW;
177
178 procedure ADD_LANGUAGE
179 is
180 begin
181 delete from BSC_TAB_CSF_TL T
182 where not exists
183 (select NULL
184 from BSC_TAB_CSF_B B
185 where B.TAB_ID = T.TAB_ID
186 and B.CSF_ID = T.CSF_ID
187 );
188
189 update BSC_TAB_CSF_TL T set (
190 NAME,
191 HELP
192 ) = (select
193 B.NAME,
194 B.HELP
195 from BSC_TAB_CSF_TL B
196 where B.TAB_ID = T.TAB_ID
197 and B.CSF_ID = T.CSF_ID
198 and B.LANGUAGE = T.SOURCE_LANG)
199 where (
200 T.TAB_ID,
201 T.CSF_ID,
202 T.LANGUAGE
203 ) in (select
204 SUBT.TAB_ID,
205 SUBT.CSF_ID,
206 SUBT.LANGUAGE
207 from BSC_TAB_CSF_TL SUBB, BSC_TAB_CSF_TL SUBT
208 where SUBB.TAB_ID = SUBT.TAB_ID
209 and SUBB.CSF_ID = SUBT.CSF_ID
210 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
211 and (SUBB.NAME <> SUBT.NAME
212 or (SUBB.NAME is null and SUBT.NAME is not null)
213 or (SUBB.NAME is not null and SUBT.NAME is null)
214 or SUBB.HELP <> SUBT.HELP
215 or (SUBB.HELP is null and SUBT.HELP is not null)
216 or (SUBB.HELP is not null and SUBT.HELP is null)
217 ));
218
219 insert into BSC_TAB_CSF_TL (
220 TAB_ID,
221 CSF_ID,
222 NAME,
223 HELP,
224 LANGUAGE,
225 SOURCE_LANG
226 ) select
227 B.TAB_ID,
228 B.CSF_ID,
229 B.NAME,
230 B.HELP,
231 L.LANGUAGE_CODE,
232 B.SOURCE_LANG
233 from BSC_TAB_CSF_TL B, FND_LANGUAGES L
234 where L.INSTALLED_FLAG in ('I', 'B')
235 and B.LANGUAGE = userenv('LANG')
236 and not exists
237 (select NULL
238 from BSC_TAB_CSF_TL T
239 where T.TAB_ID = B.TAB_ID
240 and T.CSF_ID = B.CSF_ID
241 and T.LANGUAGE = L.LANGUAGE_CODE);
242 end ADD_LANGUAGE;
243
244 end BSC_TAB_CSF_PKG;
[Home] [Help]