[Home] [Help]
PACKAGE BODY: APPS.AZ_STRUCTURES_PKG
Source
1 package body AZ_STRUCTURES_PKG as
2 /* $Header: aztstrctb.pls 120.2 2006/01/13 07:39:58 sbandi noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_STRUCTURE_CODE in VARCHAR2,
6 X_HIERARCHICAL_FLAG in VARCHAR2,
7 X_ACTIVE in VARCHAR2,
8 X_STRUCTURE_NAME in VARCHAR2,
9 X_STRUCTURE_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_STRUCTURES_B
17 where STRUCTURE_CODE = X_STRUCTURE_CODE
18 ;
19 begin
20 insert into AZ_STRUCTURES_B (
21 STRUCTURE_CODE,
22 HIERARCHICAL_FLAG,
23 ACTIVE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_STRUCTURE_CODE,
31 X_HIERARCHICAL_FLAG,
32 X_ACTIVE,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN
38 );
39
40 insert into AZ_STRUCTURES_TL (
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 STRUCTURE_DESC,
47 STRUCTURE_CODE,
48 STRUCTURE_NAME,
49 LANGUAGE,
50 SOURCE_LANG
51 ) select
52 X_CREATED_BY,
53 X_CREATION_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATE_LOGIN,
57 X_STRUCTURE_DESC,
58 X_STRUCTURE_CODE,
59 X_STRUCTURE_NAME,
60 L.LANGUAGE_CODE,
61 userenv('LANG')
62 from FND_LANGUAGES L
63 where L.INSTALLED_FLAG in ('I', 'B')
64 and not exists
65 (select NULL
66 from AZ_STRUCTURES_TL T
67 where T.STRUCTURE_CODE = X_STRUCTURE_CODE
68 and T.LANGUAGE = L.LANGUAGE_CODE);
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_STRUCTURE_CODE in VARCHAR2,
82 X_HIERARCHICAL_FLAG in VARCHAR2,
83 X_STRUCTURE_NAME in VARCHAR2,
84 X_STRUCTURE_DESC in VARCHAR2
85 ) is
86 cursor c is select
87 HIERARCHICAL_FLAG
88 from AZ_STRUCTURES_B
89 where STRUCTURE_CODE = X_STRUCTURE_CODE
90 for update of STRUCTURE_CODE nowait;
91 recinfo c%rowtype;
92
93 cursor c1 is select
94 STRUCTURE_NAME,
95 STRUCTURE_DESC,
96 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
97 from AZ_STRUCTURES_TL
98 where STRUCTURE_CODE = X_STRUCTURE_CODE
99 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
100 for update of STRUCTURE_CODE nowait;
101 begin
102 open c;
103 fetch c into recinfo;
104 if (c%notfound) then
105 close c;
106 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
107 app_exception.raise_exception;
108 end if;
109 close c;
110 if ( (recinfo.HIERARCHICAL_FLAG = X_HIERARCHICAL_FLAG)
111 ) then
112 null;
113 else
114 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
115 app_exception.raise_exception;
116 end if;
117
118 for tlinfo in c1 loop
119 if (tlinfo.BASELANG = 'Y') then
120 if ( ((tlinfo.STRUCTURE_NAME = X_STRUCTURE_NAME)
121 OR ((tlinfo.STRUCTURE_NAME is null) AND (X_STRUCTURE_NAME is null)))
122 AND ((tlinfo.STRUCTURE_DESC = X_STRUCTURE_DESC)
123 OR ((tlinfo.STRUCTURE_DESC is null) AND (X_STRUCTURE_DESC is null)))
124 ) then
125 null;
126 else
127 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128 app_exception.raise_exception;
129 end if;
130 end if;
131 end loop;
132 return;
133 end LOCK_ROW;
134
135 procedure UPDATE_ROW (
136 X_STRUCTURE_CODE in VARCHAR2,
137 X_HIERARCHICAL_FLAG in VARCHAR2,
138 X_ACTIVE in VARCHAR2,
139 X_STRUCTURE_NAME in VARCHAR2,
140 X_STRUCTURE_DESC in VARCHAR2,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145 begin
146 update AZ_STRUCTURES_B set
147 HIERARCHICAL_FLAG = X_HIERARCHICAL_FLAG,
148 ACTIVE = X_ACTIVE,
149 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
150 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
152 where STRUCTURE_CODE = X_STRUCTURE_CODE;
153
154 if (sql%notfound) then
155 raise no_data_found;
156 end if;
157
158 update AZ_STRUCTURES_TL set
159 STRUCTURE_NAME = X_STRUCTURE_NAME,
160 STRUCTURE_DESC = X_STRUCTURE_DESC,
161 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
162 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
164 SOURCE_LANG = userenv('LANG')
165 where STRUCTURE_CODE = X_STRUCTURE_CODE
166 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
167
168 if (sql%notfound) then
169 raise no_data_found;
170 end if;
171 end UPDATE_ROW;
172
173 procedure DELETE_ROW (
174 X_STRUCTURE_CODE in VARCHAR2
175 ) is
176 begin
177 delete from AZ_STRUCTURES_TL
178 where STRUCTURE_CODE = X_STRUCTURE_CODE;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183
184 delete from AZ_STRUCTURES_B
185 where STRUCTURE_CODE = X_STRUCTURE_CODE;
186
187 if (sql%notfound) then
188 raise no_data_found;
189 end if;
190 end DELETE_ROW;
191
192 procedure ADD_LANGUAGE
193 is
194 begin
195 delete from AZ_STRUCTURES_TL T
196 where not exists
197 (select NULL
198 from AZ_STRUCTURES_B B
199 where B.STRUCTURE_CODE = T.STRUCTURE_CODE
200 );
201
202 update AZ_STRUCTURES_TL T set (
203 STRUCTURE_NAME,
204 STRUCTURE_DESC
205 ) = (select
206 B.STRUCTURE_NAME,
207 B.STRUCTURE_DESC
208 from AZ_STRUCTURES_TL B
209 where B.STRUCTURE_CODE = T.STRUCTURE_CODE
210 and B.LANGUAGE = T.SOURCE_LANG)
211 where (
212 T.STRUCTURE_CODE,
213 T.LANGUAGE
214 ) in (select
215 SUBT.STRUCTURE_CODE,
216 SUBT.LANGUAGE
217 from AZ_STRUCTURES_TL SUBB, AZ_STRUCTURES_TL SUBT
218 where SUBB.STRUCTURE_CODE = SUBT.STRUCTURE_CODE
219 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
220 and (SUBB.STRUCTURE_NAME <> SUBT.STRUCTURE_NAME
221 or (SUBB.STRUCTURE_NAME is null and SUBT.STRUCTURE_NAME is not null)
222 or (SUBB.STRUCTURE_NAME is not null and SUBT.STRUCTURE_NAME is null)
223 or SUBB.STRUCTURE_DESC <> SUBT.STRUCTURE_DESC
224 or (SUBB.STRUCTURE_DESC is null and SUBT.STRUCTURE_DESC is not null)
225 or (SUBB.STRUCTURE_DESC is not null and SUBT.STRUCTURE_DESC is null)
226 ));
227
228 insert into AZ_STRUCTURES_TL (
229 CREATED_BY,
230 CREATION_DATE,
231 LAST_UPDATED_BY,
232 LAST_UPDATE_DATE,
233 LAST_UPDATE_LOGIN,
234 STRUCTURE_DESC,
235 STRUCTURE_CODE,
236 STRUCTURE_NAME,
237 LANGUAGE,
238 SOURCE_LANG
239 ) select
240 B.CREATED_BY,
241 B.CREATION_DATE,
242 B.LAST_UPDATED_BY,
243 B.LAST_UPDATE_DATE,
244 B.LAST_UPDATE_LOGIN,
245 B.STRUCTURE_DESC,
246 B.STRUCTURE_CODE,
247 B.STRUCTURE_NAME,
248 L.LANGUAGE_CODE,
249 B.SOURCE_LANG
250 from AZ_STRUCTURES_TL B, FND_LANGUAGES L
251 where L.INSTALLED_FLAG in ('I', 'B')
252 and B.LANGUAGE = userenv('LANG')
253 and not exists
254 (select NULL
255 from AZ_STRUCTURES_TL T
256 where T.STRUCTURE_CODE = B.STRUCTURE_CODE
257 and T.LANGUAGE = L.LANGUAGE_CODE);
258 end ADD_LANGUAGE;
259
260 procedure TRANSLATE_ROW (
261 X_STRUCTURE_CODE in VARCHAR2,
262 X_STRUCTURE_NAME in VARCHAR2,
263 X_OWNER in VARCHAR2,
264 X_STRUCTURE_DESC in VARCHAR2 ) is
265 begin
266 update AZ_STRUCTURES_TL set
267 STRUCTURE_NAME = X_STRUCTURE_NAME,
268 STRUCTURE_DESC = X_STRUCTURE_DESC,
269 last_update_date = sysdate,
270 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
271 last_update_login = 0,
272 source_lang = userenv('LANG')
273 where STRUCTURE_CODE = X_STRUCTURE_CODE
274 and userenv('LANG') in (language, source_lang);
275
276 end TRANSLATE_ROW;
277
278 procedure LOAD_ROW (
279 X_STRUCTURE_CODE in VARCHAR2,
280 X_OWNER in VARCHAR2,
281 X_HIERARCHICAL_FLAG in VARCHAR2,
282 X_ACTIVE in VARCHAR2,
283 X_STRUCTURE_NAME in VARCHAR2,
284 X_STRUCTURE_DESC in VARCHAR2) IS
285 begin
286 declare
287 l_owner_id number := 0;
288 l_row_id varchar2(64);
289 luby number := null;
290 begin
291 if (X_OWNER = 'SEED') then
292 l_owner_id := 1;
293 end if;
294
295 select last_updated_by into luby
296 from AZ_STRUCTURES_B
297 where STRUCTURE_CODE = X_STRUCTURE_CODE;
298
299 IF luby = 1 THEN
300 AZ_STRUCTURES_PKG.UPDATE_ROW(
301 X_STRUCTURE_CODE => X_STRUCTURE_CODE,
302 X_HIERARCHICAL_FLAG => X_HIERARCHICAL_FLAG,
303 X_ACTIVE => X_ACTIVE,
304 X_STRUCTURE_NAME => X_STRUCTURE_NAME,
305 X_STRUCTURE_DESC => X_STRUCTURE_DESC,
306 X_LAST_UPDATE_DATE => sysdate,
307 X_LAST_UPDATED_BY => l_owner_id,
308 X_LAST_UPDATE_LOGIN => 0
309 );
310 END IF; -- if luby = 1
311
312 exception
313 when NO_DATA_FOUND then
314
315 AZ_STRUCTURES_PKG.INSERT_ROW(
316 X_ROWID => l_row_id,
317 X_STRUCTURE_CODE => X_STRUCTURE_CODE,
318 X_HIERARCHICAL_FLAG => X_HIERARCHICAL_FLAG,
319 X_ACTIVE => X_ACTIVE,
320 X_STRUCTURE_NAME => X_STRUCTURE_NAME,
321 X_STRUCTURE_DESC => X_STRUCTURE_DESC,
322 X_CREATION_DATE => sysdate,
323 X_CREATED_BY => l_owner_id,
324 X_LAST_UPDATE_DATE => sysdate,
325 X_LAST_UPDATED_BY => l_owner_id,
326 X_LAST_UPDATE_LOGIN => 0
327 );
328
329 end;
330
331 end LOAD_ROW;
332
333 end AZ_STRUCTURES_PKG;