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