1 package body AHL_FLEET_HEADERS_PKG as
2 /* $Header: AHLLFLTB.pls 120.0.12020000.4 2013/05/29 10:10:09 sansatpa noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_FLEET_HEADER_ID in NUMBER,
6 X_ATTRIBUTE12 in VARCHAR2,
7 X_ATTRIBUTE5 in VARCHAR2,
8 X_DEPARTMENT_ID in NUMBER,
9 X_OPERATING_ORG_ID in NUMBER,
10 X_NAME in VARCHAR2,
11 X_ATTRIBUTE11 in VARCHAR2,
12 X_ATTRIBUTE10 in VARCHAR2,
13 X_ATTRIBUTE9 in VARCHAR2,
14 X_ATTRIBUTE8 in VARCHAR2,
15 X_ATTRIBUTE7 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE15 in VARCHAR2,
18 X_ATTRIBUTE14 in VARCHAR2,
19 X_ATTRIBUTE13 in VARCHAR2,
20 X_STATUS_CODE in VARCHAR2,
21 X_OPERATIONS_TYPE_CODE in VARCHAR2,
22 X_ATTRIBUTE4 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE2 in VARCHAR2,
25 X_ATTRIBUTE1 in VARCHAR2,
26 X_ATTRIBUTE_CATEGORY in VARCHAR2,
27 X_OBJECT_VERSION_NUMBER in NUMBER,
28 X_COMPLETION_DATE in DATE,
29 X_DESCRIPTION in VARCHAR2,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER
35 ) is
36 cursor C is select ROWID from AHL_FLEET_HEADERS_B
37 where FLEET_HEADER_ID = X_FLEET_HEADER_ID
38 ;
39 begin
40 insert into AHL_FLEET_HEADERS_B (
41 ATTRIBUTE12,
42 ATTRIBUTE5,
43 FLEET_HEADER_ID,
44 DEPARTMENT_ID,
45 OPERATING_ORG_ID,
46 NAME,
47 ATTRIBUTE11,
48 ATTRIBUTE10,
49 ATTRIBUTE9,
50 ATTRIBUTE8,
51 ATTRIBUTE7,
52 ATTRIBUTE6,
53 ATTRIBUTE15,
54 ATTRIBUTE14,
55 ATTRIBUTE13,
56 STATUS_CODE,
57 OPERATIONS_TYPE_CODE,
58 ATTRIBUTE4,
59 ATTRIBUTE3,
60 ATTRIBUTE2,
61 ATTRIBUTE1,
62 ATTRIBUTE_CATEGORY,
63 OBJECT_VERSION_NUMBER,
64 COMPLETION_DATE,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN
70 ) values (
71 X_ATTRIBUTE12,
72 X_ATTRIBUTE5,
73 X_FLEET_HEADER_ID,
74 X_DEPARTMENT_ID,
75 X_OPERATING_ORG_ID,
76 X_NAME,
77 X_ATTRIBUTE11,
78 X_ATTRIBUTE10,
79 X_ATTRIBUTE9,
80 X_ATTRIBUTE8,
81 X_ATTRIBUTE7,
82 X_ATTRIBUTE6,
83 X_ATTRIBUTE15,
84 X_ATTRIBUTE14,
85 X_ATTRIBUTE13,
86 X_STATUS_CODE,
87 X_OPERATIONS_TYPE_CODE,
88 X_ATTRIBUTE4,
89 X_ATTRIBUTE3,
90 X_ATTRIBUTE2,
91 X_ATTRIBUTE1,
92 X_ATTRIBUTE_CATEGORY,
93 X_OBJECT_VERSION_NUMBER,
94 X_COMPLETION_DATE,
95 X_CREATION_DATE,
96 X_CREATED_BY,
97 X_LAST_UPDATE_DATE,
98 X_LAST_UPDATED_BY,
99 X_LAST_UPDATE_LOGIN
100 );
101
102 insert into AHL_FLEET_HEADERS_TL (
103 LAST_UPDATE_LOGIN,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_DATE,
108 DESCRIPTION,
109 FLEET_HEADER_ID,
110 LANGUAGE,
111 SOURCE_LANG
112 ) select
113 X_LAST_UPDATE_LOGIN,
114 X_CREATED_BY,
115 X_CREATION_DATE,
116 X_LAST_UPDATED_BY,
117 X_LAST_UPDATE_DATE,
118 X_DESCRIPTION,
119 X_FLEET_HEADER_ID,
120 L.LANGUAGE_CODE,
121 userenv('LANG')
122 from FND_LANGUAGES L
123 where L.INSTALLED_FLAG in ('I', 'B')
124 and not exists
125 (select NULL
126 from AHL_FLEET_HEADERS_TL T
127 where T.FLEET_HEADER_ID = X_FLEET_HEADER_ID
128 and T.LANGUAGE = L.LANGUAGE_CODE);
129
130 open c;
131 fetch c into X_ROWID;
132 if (c%notfound) then
133 close c;
134 raise no_data_found;
135 end if;
136 close c;
137
138 end INSERT_ROW;
139
140 procedure LOCK_ROW (
141 X_FLEET_HEADER_ID in NUMBER,
142 X_ATTRIBUTE12 in VARCHAR2,
143 X_ATTRIBUTE5 in VARCHAR2,
144 X_DEPARTMENT_ID in NUMBER,
145 X_OPERATING_ORG_ID in NUMBER,
146 X_NAME in VARCHAR2,
147 X_ATTRIBUTE11 in VARCHAR2,
148 X_ATTRIBUTE10 in VARCHAR2,
149 X_ATTRIBUTE9 in VARCHAR2,
150 X_ATTRIBUTE8 in VARCHAR2,
151 X_ATTRIBUTE7 in VARCHAR2,
152 X_ATTRIBUTE6 in VARCHAR2,
153 X_ATTRIBUTE15 in VARCHAR2,
154 X_ATTRIBUTE14 in VARCHAR2,
155 X_ATTRIBUTE13 in VARCHAR2,
156 X_STATUS_CODE in VARCHAR2,
157 X_OPERATIONS_TYPE_CODE in VARCHAR2,
158 X_ATTRIBUTE4 in VARCHAR2,
159 X_ATTRIBUTE3 in VARCHAR2,
160 X_ATTRIBUTE2 in VARCHAR2,
161 X_ATTRIBUTE1 in VARCHAR2,
162 X_ATTRIBUTE_CATEGORY in VARCHAR2,
163 X_OBJECT_VERSION_NUMBER in NUMBER,
164 X_COMPLETION_DATE in DATE,
165 X_DESCRIPTION in VARCHAR2
166 ) is
167 cursor c is select
168 ATTRIBUTE12,
169 ATTRIBUTE5,
170 DEPARTMENT_ID,
171 OPERATING_ORG_ID,
172 NAME,
173 ATTRIBUTE11,
174 ATTRIBUTE10,
175 ATTRIBUTE9,
176 ATTRIBUTE8,
177 ATTRIBUTE7,
178 ATTRIBUTE6,
179 ATTRIBUTE15,
180 ATTRIBUTE14,
181 ATTRIBUTE13,
182 STATUS_CODE,
183 OPERATIONS_TYPE_CODE,
184 ATTRIBUTE4,
185 ATTRIBUTE3,
186 ATTRIBUTE2,
187 ATTRIBUTE1,
188 ATTRIBUTE_CATEGORY,
189 OBJECT_VERSION_NUMBER,
190 COMPLETION_DATE
191 from AHL_FLEET_HEADERS_B
192 where FLEET_HEADER_ID = X_FLEET_HEADER_ID
193 for update of FLEET_HEADER_ID nowait;
194 recinfo c%rowtype;
195
196 cursor c1 is select
197 DESCRIPTION,
198 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
199 from AHL_FLEET_HEADERS_TL
200 where FLEET_HEADER_ID = X_FLEET_HEADER_ID
201 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
202 for update of FLEET_HEADER_ID nowait;
203 begin
204 open c;
205 fetch c into recinfo;
206 if (c%notfound) then
207 close c;
208 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
209 app_exception.raise_exception;
210 end if;
211 close c;
212 if ( ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
213 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
214 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
215 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
216 AND ((recinfo.DEPARTMENT_ID = X_DEPARTMENT_ID)
217 OR ((recinfo.DEPARTMENT_ID is null) AND (X_DEPARTMENT_ID is null)))
218 AND ((recinfo.OPERATING_ORG_ID = X_OPERATING_ORG_ID)
219 OR ((recinfo.OPERATING_ORG_ID is null) AND (X_OPERATING_ORG_ID is null)))
220 AND (recinfo.NAME = X_NAME)
221 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
222 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
223 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
224 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
225 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
226 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
227 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
228 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
229 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
230 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
231 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
232 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
233 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
234 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
235 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
236 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
237 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
238 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
239 AND (recinfo.STATUS_CODE = X_STATUS_CODE)
240 AND (recinfo.OPERATIONS_TYPE_CODE = X_OPERATIONS_TYPE_CODE)
241 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
242 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
243 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
244 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
245 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
246 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
247 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
248 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
249 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
250 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
251 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
252 AND ((recinfo.COMPLETION_DATE = X_COMPLETION_DATE)
253 OR ((recinfo.COMPLETION_DATE is null) AND (X_COMPLETION_DATE is null)))
254 ) then
255 null;
256 else
257 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
258 app_exception.raise_exception;
259 end if;
260
261 for tlinfo in c1 loop
262 if (tlinfo.BASELANG = 'Y') then
263 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
264 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
265 ) then
266 null;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271 end if;
272 end loop;
273 return;
274 end LOCK_ROW;
275
276 procedure UPDATE_ROW (
277 X_FLEET_HEADER_ID in NUMBER,
278 X_ATTRIBUTE12 in VARCHAR2,
279 X_ATTRIBUTE5 in VARCHAR2,
280 X_DEPARTMENT_ID in NUMBER,
281 X_OPERATING_ORG_ID in NUMBER,
282 X_NAME in VARCHAR2,
283 X_ATTRIBUTE11 in VARCHAR2,
284 X_ATTRIBUTE10 in VARCHAR2,
285 X_ATTRIBUTE9 in VARCHAR2,
286 X_ATTRIBUTE8 in VARCHAR2,
287 X_ATTRIBUTE7 in VARCHAR2,
288 X_ATTRIBUTE6 in VARCHAR2,
289 X_ATTRIBUTE15 in VARCHAR2,
290 X_ATTRIBUTE14 in VARCHAR2,
291 X_ATTRIBUTE13 in VARCHAR2,
292 X_STATUS_CODE in VARCHAR2,
293 X_OPERATIONS_TYPE_CODE in VARCHAR2,
294 X_ATTRIBUTE4 in VARCHAR2,
295 X_ATTRIBUTE3 in VARCHAR2,
296 X_ATTRIBUTE2 in VARCHAR2,
297 X_ATTRIBUTE1 in VARCHAR2,
298 X_ATTRIBUTE_CATEGORY in VARCHAR2,
299 X_OBJECT_VERSION_NUMBER in NUMBER,
300 X_COMPLETION_DATE in DATE,
301 X_DESCRIPTION in VARCHAR2,
302 X_LAST_UPDATE_DATE in DATE,
303 X_LAST_UPDATED_BY in NUMBER,
304 X_LAST_UPDATE_LOGIN in NUMBER
305 ) is
306 begin
307 update AHL_FLEET_HEADERS_B set
308 ATTRIBUTE12 = X_ATTRIBUTE12,
309 ATTRIBUTE5 = X_ATTRIBUTE5,
310 DEPARTMENT_ID = X_DEPARTMENT_ID,
311 OPERATING_ORG_ID = X_OPERATING_ORG_ID,
312 NAME = X_NAME,
313 ATTRIBUTE11 = X_ATTRIBUTE11,
314 ATTRIBUTE10 = X_ATTRIBUTE10,
315 ATTRIBUTE9 = X_ATTRIBUTE9,
316 ATTRIBUTE8 = X_ATTRIBUTE8,
317 ATTRIBUTE7 = X_ATTRIBUTE7,
318 ATTRIBUTE6 = X_ATTRIBUTE6,
319 ATTRIBUTE15 = X_ATTRIBUTE15,
320 ATTRIBUTE14 = X_ATTRIBUTE14,
321 ATTRIBUTE13 = X_ATTRIBUTE13,
322 STATUS_CODE = X_STATUS_CODE,
323 OPERATIONS_TYPE_CODE = X_OPERATIONS_TYPE_CODE,
324 ATTRIBUTE4 = X_ATTRIBUTE4,
325 ATTRIBUTE3 = X_ATTRIBUTE3,
326 ATTRIBUTE2 = X_ATTRIBUTE2,
327 ATTRIBUTE1 = X_ATTRIBUTE1,
328 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
329 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
330 COMPLETION_DATE = X_COMPLETION_DATE,
331 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
332 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
333 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
334 where FLEET_HEADER_ID = X_FLEET_HEADER_ID;
335
336 if (sql%notfound) then
337 raise no_data_found;
338 end if;
339
340 update AHL_FLEET_HEADERS_TL set
341 DESCRIPTION = X_DESCRIPTION,
342 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
343 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
344 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
345 SOURCE_LANG = userenv('LANG')
346 where FLEET_HEADER_ID = X_FLEET_HEADER_ID
347 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
348
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352 end UPDATE_ROW;
353
354 procedure DELETE_ROW (
355 X_FLEET_HEADER_ID in NUMBER
356 ) is
357 begin
358 delete from AHL_FLEET_HEADERS_TL
359 where FLEET_HEADER_ID = X_FLEET_HEADER_ID;
360
361 if (sql%notfound) then
362 raise no_data_found;
363 end if;
364
365 delete from AHL_FLEET_HEADERS_B
366 where FLEET_HEADER_ID = X_FLEET_HEADER_ID;
367
368 if (sql%notfound) then
369 raise no_data_found;
370 end if;
371 end DELETE_ROW;
372
373 procedure ADD_LANGUAGE
374 is
375 begin
376 delete from AHL_FLEET_HEADERS_TL T
377 where not exists
378 (select NULL
379 from AHL_FLEET_HEADERS_B B
380 where B.FLEET_HEADER_ID = T.FLEET_HEADER_ID
381 );
382
383 update AHL_FLEET_HEADERS_TL T set (
384 DESCRIPTION
385 ) = (select
386 B.DESCRIPTION
387 from AHL_FLEET_HEADERS_TL B
388 where B.FLEET_HEADER_ID = T.FLEET_HEADER_ID
389 and B.LANGUAGE = T.SOURCE_LANG)
390 where (
391 T.FLEET_HEADER_ID,
392 T.LANGUAGE
393 ) in (select
394 SUBT.FLEET_HEADER_ID,
395 SUBT.LANGUAGE
396 from AHL_FLEET_HEADERS_TL SUBB, AHL_FLEET_HEADERS_TL SUBT
397 where SUBB.FLEET_HEADER_ID = SUBT.FLEET_HEADER_ID
398 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
399 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
400 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
401 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
402 ));
403
404 insert into AHL_FLEET_HEADERS_TL (
405 LAST_UPDATE_LOGIN,
406 CREATED_BY,
407 CREATION_DATE,
408 LAST_UPDATED_BY,
409 LAST_UPDATE_DATE,
410 DESCRIPTION,
411 FLEET_HEADER_ID,
412 LANGUAGE,
413 SOURCE_LANG
414 ) select /*+ ORDERED */
415 B.LAST_UPDATE_LOGIN,
416 B.CREATED_BY,
417 B.CREATION_DATE,
418 B.LAST_UPDATED_BY,
419 B.LAST_UPDATE_DATE,
420 B.DESCRIPTION,
421 B.FLEET_HEADER_ID,
422 L.LANGUAGE_CODE,
423 B.SOURCE_LANG
424 from AHL_FLEET_HEADERS_TL B, FND_LANGUAGES L
425 where L.INSTALLED_FLAG in ('I', 'B')
426 and B.LANGUAGE = userenv('LANG')
427 and not exists
428 (select NULL
429 from AHL_FLEET_HEADERS_TL T
430 where T.FLEET_HEADER_ID = B.FLEET_HEADER_ID
431 and T.LANGUAGE = L.LANGUAGE_CODE);
432 end ADD_LANGUAGE;
433
434 end AHL_FLEET_HEADERS_PKG;
[Home] [Help]