[Home] [Help]
PACKAGE BODY: APPS.AZ_TAXONOMIES_PKG
Source
1 package body AZ_TAXONOMIES_PKG as
2 /* $Header: azttaxonomyb.pls 120.2 2008/03/26 11:30:55 hboda noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TAXONOMY_CODE in VARCHAR2,
6 X_USER_ID in NUMBER,
7 X_ENABLED_FLAG in VARCHAR2,
8 X_TAXONOMY_NAME in VARCHAR2,
9 X_TAXONOMY_DESC in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from AZ_TAXONOMIES_B
17 where TAXONOMY_CODE = X_TAXONOMY_CODE
18 and USER_ID = X_USER_ID ;
19
20 begin
21 insert into AZ_TAXONOMIES_B (
22 TAXONOMY_CODE,
23 USER_ID ,
24 ENABLED_FLAG,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_TAXONOMY_CODE,
32 X_USER_ID,
33 X_ENABLED_FLAG,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into AZ_TAXONOMIES_TL (
42 TAXONOMY_CODE,
43 USER_ID,
44 TAXONOMY_NAME,
45 TAXONOMY_DESC,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATE_LOGIN,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_TAXONOMY_CODE,
55 X_USER_ID,
56 X_TAXONOMY_NAME,
57 X_TAXONOMY_DESC,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from AZ_TAXONOMIES_TL T
70 where T.TAXONOMY_CODE = X_TAXONOMY_CODE
71 and T.USER_ID = X_USER_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE) ;
73
74 open c;
75 fetch c into X_ROWID;
76 if (c%notfound) then
77 close c;
78 raise no_data_found;
79 end if;
80 close c;
81
82 end INSERT_ROW;
83
84
85 procedure UPDATE_ROW (
86 X_TAXONOMY_CODE in VARCHAR2,
87 X_USER_ID in NUMBER,
88 X_ENABLED_FLAG in VARCHAR2,
89 X_TAXONOMY_NAME in VARCHAR2,
90 X_TAXONOMY_DESC in VARCHAR2,
91 X_LAST_UPDATE_DATE in DATE,
92 X_LAST_UPDATED_BY in NUMBER,
93 X_LAST_UPDATE_LOGIN in NUMBER
94 ) is
95 begin
96 update AZ_TAXONOMIES_B set
97 ENABLED_FLAG = X_ENABLED_FLAG,
98 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
99 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
100 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
101 where TAXONOMY_CODE = X_TAXONOMY_CODE
102 and USER_ID = X_USER_ID;
103
104 if (sql%notfound) then
105 raise no_data_found;
106 end if;
107
108 update AZ_TAXONOMIES_TL set
109 TAXONOMY_NAME = X_TAXONOMY_NAME,
110 TAXONOMY_DESC = X_TAXONOMY_DESC,
111 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
112 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
114 SOURCE_LANG = userenv('LANG')
115 where TAXONOMY_CODE = X_TAXONOMY_CODE
116 and USER_ID = X_USER_ID
117 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
118
119 if (sql%notfound) then
120 raise no_data_found;
121 end if;
122 end UPDATE_ROW;
123
124 procedure DELETE_ROW (
125 X_TAXONOMY_CODE in VARCHAR2,
126 X_USER_ID in NUMBER
127 ) is
128 begin
129 delete from AZ_TAXONOMIES_TL
130 where TAXONOMY_CODE = X_TAXONOMY_CODE
131 and USER_ID = X_USER_ID;
132
133 if (sql%notfound) then
134 raise no_data_found;
135 end if;
136
137 delete from AZ_TAXONOMIES_B
138 where TAXONOMY_CODE = X_TAXONOMY_CODE
139 and USER_ID = X_USER_ID;
140
141 if (sql%notfound) then
142 raise no_data_found;
143 end if;
144 end DELETE_ROW;
145
146 procedure ADD_LANGUAGE
147 is
148 begin
149 delete from AZ_TAXONOMIES_TL T
150 where not exists
151 (select NULL
152 from AZ_TAXONOMIES_B B
153 where B.TAXONOMY_CODE = T.TAXONOMY_CODE
154 and B.USER_ID = T.USER_ID
155 );
156
157 update AZ_TAXONOMIES_TL T set (
158 TAXONOMY_NAME,
159 TAXONOMY_DESC
160 ) = (select
161 B.TAXONOMY_NAME,
162 B.TAXONOMY_DESC
163 from AZ_TAXONOMIES_TL B
164 where B.TAXONOMY_CODE = T.TAXONOMY_CODE
165 and B.USER_ID = T.USER_ID
166 and B.LANGUAGE = T.SOURCE_LANG)
167 where (
168 T.TAXONOMY_CODE,
169 T.LANGUAGE
170 ) in (select
171 SUBT.TAXONOMY_CODE,
172 SUBT.LANGUAGE
173 from AZ_TAXONOMIES_TL SUBB, AZ_TAXONOMIES_TL SUBT
174 where SUBB.TAXONOMY_CODE = SUBT.TAXONOMY_CODE
175 and SUBB.USER_ID = SUBT.USER_ID
176 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
177 and (SUBB.TAXONOMY_NAME <> SUBT.TAXONOMY_NAME
178 or (SUBB.TAXONOMY_NAME is null and SUBT.TAXONOMY_NAME is not null)
179 or (SUBB.TAXONOMY_NAME is not null and SUBT.TAXONOMY_NAME is null)
180 or SUBB.TAXONOMY_DESC <> SUBT.TAXONOMY_DESC
181 or (SUBB.TAXONOMY_DESC is null and SUBT.TAXONOMY_DESC is not null)
182 or (SUBB.TAXONOMY_DESC is not null and SUBT.TAXONOMY_DESC is null)
183 ));
184
185 insert into AZ_TAXONOMIES_TL (
186 CREATED_BY,
187 CREATION_DATE,
188 LAST_UPDATED_BY,
189 LAST_UPDATE_DATE,
190 LAST_UPDATE_LOGIN,
191 TAXONOMY_NAME,
192 TAXONOMY_DESC,
193 TAXONOMY_CODE,
194 USER_ID,
195 LANGUAGE,
196 SOURCE_LANG
197 ) select
198 B.CREATED_BY,
199 B.CREATION_DATE,
200 B.LAST_UPDATED_BY,
201 B.LAST_UPDATE_DATE,
202 B.LAST_UPDATE_LOGIN,
203 B.TAXONOMY_NAME,
204 B.TAXONOMY_DESC,
205 B.TAXONOMY_CODE,
206 B.USER_ID,
207 L.LANGUAGE_CODE,
208 B.SOURCE_LANG
209 from AZ_TAXONOMIES_TL B, FND_LANGUAGES L
210 where L.INSTALLED_FLAG in ('I', 'B')
211 and B.LANGUAGE = userenv('LANG')
212 and not exists
213 (select NULL
214 from AZ_TAXONOMIES_TL T
215 where T.TAXONOMY_CODE = B.TAXONOMY_CODE
216 and T.USER_ID = B.USER_ID
217 and T.LANGUAGE = L.LANGUAGE_CODE);
218 end ADD_LANGUAGE;
219
220 procedure TRANSLATE_ROW (
221 X_TAXONOMY_CODE in VARCHAR2,
222 X_USER_ID in NUMBER,
223 X_OWNER in VARCHAR2,
224 X_TAXONOMY_NAME in VARCHAR2,
225 X_TAXONOMY_DESC in VARCHAR2 ) is
226 begin
227 update AZ_TAXONOMIES_TL set
228 TAXONOMY_NAME = X_TAXONOMY_NAME,
229 TAXONOMY_DESC = X_TAXONOMY_DESC,
230 last_update_date = sysdate,
231 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
232 last_update_login = 0,
233 source_lang = userenv('LANG')
234 where TAXONOMY_CODE = X_TAXONOMY_CODE
235 and USER_ID = X_USER_ID
236 and userenv('LANG') in (language, source_lang);
237
238 end TRANSLATE_ROW;
239
240 procedure LOAD_ROW (
241 X_TAXONOMY_CODE in VARCHAR2,
242 X_USER_ID in NUMBER,
243 X_OWNER in VARCHAR2,
244 X_ENABLED_FLAG in VARCHAR2,
245 X_TAXONOMY_NAME in VARCHAR2,
246 X_TAXONOMY_DESC in VARCHAR2) IS
247 begin
248 declare
249 l_owner_id number := 0;
250 l_row_id varchar2(64);
251 luby number := null;
252 begin
253 if (X_OWNER = 'SEED') then
254 l_owner_id := 1;
255 end if;
256
257 select last_updated_by into luby
258 from AZ_TAXONOMIES_B
259 where TAXONOMY_CODE = X_TAXONOMY_CODE
260 and USER_ID = X_USER_ID;
261
262 if (luby = 1) then
263 AZ_TAXONOMIES_PKG.UPDATE_ROW(
264 X_TAXONOMY_CODE => X_TAXONOMY_CODE,
265 X_USER_ID => X_USER_ID,
266 X_ENABLED_FLAG => X_ENABLED_FLAG,
267 X_TAXONOMY_NAME => X_TAXONOMY_NAME,
268 X_TAXONOMY_DESC => X_TAXONOMY_DESC,
269 X_LAST_UPDATE_DATE => sysdate,
270 X_LAST_UPDATED_BY => l_owner_id,
271 X_LAST_UPDATE_LOGIN => 0
272 );
273 end if; -- if luby = 1
274
275 exception
276 when NO_DATA_FOUND then
277
278 AZ_TAXONOMIES_PKG.INSERT_ROW(
279 X_ROWID => l_row_id,
280 X_TAXONOMY_CODE => X_TAXONOMY_CODE,
281 X_USER_ID => X_USER_ID,
282 X_ENABLED_FLAG => X_ENABLED_FLAG,
283 X_TAXONOMY_NAME => X_TAXONOMY_NAME,
284 X_TAXONOMY_DESC => X_TAXONOMY_DESC,
285 X_CREATION_DATE => sysdate,
286 X_CREATED_BY => l_owner_id,
287 X_LAST_UPDATE_DATE => sysdate,
288 X_LAST_UPDATED_BY => l_owner_id,
289 X_LAST_UPDATE_LOGIN => 0
290 );
291
292 end;
293
294 end LOAD_ROW;
295
296 end AZ_TAXONOMIES_PKG;