1 package body AHL_WARRANTY_TEMPLATES_PKG as
2 /* $Header: AHLLWTMB.pls 120.0 2011/04/20 11:28:46 jkjain noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_WARRANTY_TEMPLATE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_NAME in VARCHAR2,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_WARRANTY_TYPE in VARCHAR2,
10 X_WARRANTY_VENDOR_ID in NUMBER,
11 X_CONTRACT_START_DATE in DATE,
12 X_CONTRACT_END_DATE in DATE,
13 X_WARRANTY_PERIOD in NUMBER,
14 X_PERIOD_UOM_CODE in VARCHAR2,
15 X_CONTRACT_EXPIRY_TYPE in VARCHAR2,
16 X_CLAIM_LABOUR_HOURS in NUMBER,
17 X_ACTIVE_START_DATE in DATE,
18 X_ACTIVE_END_DATE in DATE,
19 X_ATTRIBUTE_CATEGORY in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2,
35 X_DESCRIPTION in VARCHAR2,
36 X_TERMS_AND_CONDITIONS in VARCHAR2,
37 X_REACTION_AND_RESOL_TIME in VARCHAR2,
38 X_SERVICE_LEVEL_AGREEMENT in VARCHAR2,
39 X_REMARKS in VARCHAR2,
40 X_CREATION_DATE in DATE,
41 X_CREATED_BY in NUMBER,
42 X_LAST_UPDATE_DATE in DATE,
43 X_LAST_UPDATED_BY in NUMBER,
44 X_LAST_UPDATE_LOGIN in NUMBER
45 ) is
46 cursor C is select ROWID from AHL_WARRANTY_TEMPLATES_B
47 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID
48 ;
49 begin
50 insert into AHL_WARRANTY_TEMPLATES_B (
51 ATTRIBUTE8,
52 ATTRIBUTE9,
53 ATTRIBUTE10,
54 ATTRIBUTE11,
55 ATTRIBUTE12,
56 ATTRIBUTE13,
57 ATTRIBUTE14,
58 ATTRIBUTE15,
59 WARRANTY_TEMPLATE_ID,
60 OBJECT_VERSION_NUMBER,
61 NAME,
62 ENABLED_FLAG,
63 WARRANTY_TYPE,
64 WARRANTY_VENDOR_ID,
65 CONTRACT_START_DATE,
66 CONTRACT_END_DATE,
67 WARRANTY_PERIOD,
68 PERIOD_UOM_CODE,
69 CONTRACT_EXPIRY_TYPE,
70 CLAIM_LABOUR_HOURS,
71 ACTIVE_START_DATE,
72 ACTIVE_END_DATE,
73 ATTRIBUTE_CATEGORY,
74 ATTRIBUTE1,
75 ATTRIBUTE2,
76 ATTRIBUTE3,
77 ATTRIBUTE4,
78 ATTRIBUTE5,
79 ATTRIBUTE6,
80 ATTRIBUTE7,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_LOGIN
86 ) values (
87 X_ATTRIBUTE8,
88 X_ATTRIBUTE9,
89 X_ATTRIBUTE10,
90 X_ATTRIBUTE11,
91 X_ATTRIBUTE12,
92 X_ATTRIBUTE13,
93 X_ATTRIBUTE14,
94 X_ATTRIBUTE15,
95 X_WARRANTY_TEMPLATE_ID,
96 X_OBJECT_VERSION_NUMBER,
97 X_NAME,
98 X_ENABLED_FLAG,
99 X_WARRANTY_TYPE,
100 X_WARRANTY_VENDOR_ID,
101 X_CONTRACT_START_DATE,
102 X_CONTRACT_END_DATE,
103 X_WARRANTY_PERIOD,
104 X_PERIOD_UOM_CODE,
105 X_CONTRACT_EXPIRY_TYPE,
106 X_CLAIM_LABOUR_HOURS,
107 X_ACTIVE_START_DATE,
108 X_ACTIVE_END_DATE,
109 X_ATTRIBUTE_CATEGORY,
110 X_ATTRIBUTE1,
111 X_ATTRIBUTE2,
112 X_ATTRIBUTE3,
113 X_ATTRIBUTE4,
114 X_ATTRIBUTE5,
115 X_ATTRIBUTE6,
116 X_ATTRIBUTE7,
117 X_CREATION_DATE,
118 X_CREATED_BY,
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATED_BY,
121 X_LAST_UPDATE_LOGIN
122 );
123
124 insert into AHL_WARRANTY_TEMPLATES_TL (
125 WARRANTY_TEMPLATE_ID,
126 DESCRIPTION,
127 TERMS_AND_CONDITIONS,
128 REACTION_AND_RESOL_TIME,
129 SERVICE_LEVEL_AGREEMENT,
130 REMARKS,
131 LAST_UPDATE_DATE,
132 LAST_UPDATED_BY,
133 CREATION_DATE,
134 CREATED_BY,
135 LAST_UPDATE_LOGIN,
136 LANGUAGE,
137 SOURCE_LANG
138 ) select
139 X_WARRANTY_TEMPLATE_ID,
140 X_DESCRIPTION,
141 X_TERMS_AND_CONDITIONS,
142 X_REACTION_AND_RESOL_TIME,
143 X_SERVICE_LEVEL_AGREEMENT,
144 X_REMARKS,
145 X_LAST_UPDATE_DATE,
146 X_LAST_UPDATED_BY,
147 X_CREATION_DATE,
148 X_CREATED_BY,
149 X_LAST_UPDATE_LOGIN,
150 L.LANGUAGE_CODE,
151 userenv('LANG')
152 from FND_LANGUAGES L
153 where L.INSTALLED_FLAG in ('I', 'B')
154 and not exists
155 (select NULL
156 from AHL_WARRANTY_TEMPLATES_TL T
157 where T.WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID
158 and T.LANGUAGE = L.LANGUAGE_CODE);
159
160 open c;
161 fetch c into X_ROWID;
162 if (c%notfound) then
163 close c;
164 raise no_data_found;
165 end if;
166 close c;
167
168 end INSERT_ROW;
169
170 procedure LOCK_ROW (
171 X_WARRANTY_TEMPLATE_ID in NUMBER,
172 X_OBJECT_VERSION_NUMBER in NUMBER,
173 X_NAME in VARCHAR2,
174 X_ENABLED_FLAG in VARCHAR2,
175 X_WARRANTY_TYPE in VARCHAR2,
176 X_WARRANTY_VENDOR_ID in NUMBER,
177 X_CONTRACT_START_DATE in DATE,
178 X_CONTRACT_END_DATE in DATE,
179 X_WARRANTY_PERIOD in NUMBER,
180 X_PERIOD_UOM_CODE in VARCHAR2,
181 X_CONTRACT_EXPIRY_TYPE in VARCHAR2,
182 X_CLAIM_LABOUR_HOURS in NUMBER,
183 X_ACTIVE_START_DATE in DATE,
184 X_ACTIVE_END_DATE in DATE,
185 X_ATTRIBUTE_CATEGORY in VARCHAR2,
186 X_ATTRIBUTE1 in VARCHAR2,
187 X_ATTRIBUTE2 in VARCHAR2,
188 X_ATTRIBUTE3 in VARCHAR2,
189 X_ATTRIBUTE4 in VARCHAR2,
190 X_ATTRIBUTE5 in VARCHAR2,
191 X_ATTRIBUTE6 in VARCHAR2,
192 X_ATTRIBUTE7 in VARCHAR2,
193 X_ATTRIBUTE8 in VARCHAR2,
194 X_ATTRIBUTE9 in VARCHAR2,
195 X_ATTRIBUTE10 in VARCHAR2,
196 X_ATTRIBUTE11 in VARCHAR2,
197 X_ATTRIBUTE12 in VARCHAR2,
198 X_ATTRIBUTE13 in VARCHAR2,
199 X_ATTRIBUTE14 in VARCHAR2,
200 X_ATTRIBUTE15 in VARCHAR2,
201 X_DESCRIPTION in VARCHAR2,
202 X_TERMS_AND_CONDITIONS in VARCHAR2,
203 X_REACTION_AND_RESOL_TIME in VARCHAR2,
204 X_SERVICE_LEVEL_AGREEMENT in VARCHAR2,
205 X_REMARKS in VARCHAR2
206 ) is
207 cursor c is select
208 ATTRIBUTE8,
209 ATTRIBUTE9,
210 ATTRIBUTE10,
211 ATTRIBUTE11,
212 ATTRIBUTE12,
213 ATTRIBUTE13,
214 ATTRIBUTE14,
215 ATTRIBUTE15,
216 OBJECT_VERSION_NUMBER,
217 NAME,
218 ENABLED_FLAG,
219 WARRANTY_TYPE,
220 WARRANTY_VENDOR_ID,
221 CONTRACT_START_DATE,
222 CONTRACT_END_DATE,
223 WARRANTY_PERIOD,
224 PERIOD_UOM_CODE,
225 CONTRACT_EXPIRY_TYPE,
226 CLAIM_LABOUR_HOURS,
227 ACTIVE_START_DATE,
228 ACTIVE_END_DATE,
229 ATTRIBUTE_CATEGORY,
230 ATTRIBUTE1,
231 ATTRIBUTE2,
232 ATTRIBUTE3,
233 ATTRIBUTE4,
234 ATTRIBUTE5,
235 ATTRIBUTE6,
236 ATTRIBUTE7
237 from AHL_WARRANTY_TEMPLATES_B
238 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID
239 for update of WARRANTY_TEMPLATE_ID nowait;
240 recinfo c%rowtype;
241
242 cursor c1 is select
243 DESCRIPTION,
244 TERMS_AND_CONDITIONS,
245 REACTION_AND_RESOL_TIME,
246 SERVICE_LEVEL_AGREEMENT,
247 REMARKS,
248 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
249 from AHL_WARRANTY_TEMPLATES_TL
250 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID
251 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
252 for update of WARRANTY_TEMPLATE_ID nowait;
253 begin
254 open c;
255 fetch c into recinfo;
256 if (c%notfound) then
257 close c;
258 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
259 app_exception.raise_exception;
260 end if;
261 close c;
262 if ( ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
263 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
264 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
265 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
266 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
267 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
268 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
269 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
270 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
271 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
272 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
273 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
274 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
275 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
276 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
277 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
278 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
279 AND (recinfo.NAME = X_NAME)
280 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
281 AND (recinfo.WARRANTY_TYPE = X_WARRANTY_TYPE)
282 AND (recinfo.WARRANTY_VENDOR_ID = X_WARRANTY_VENDOR_ID)
283 AND ((recinfo.CONTRACT_START_DATE = X_CONTRACT_START_DATE)
284 OR ((recinfo.CONTRACT_START_DATE is null) AND (X_CONTRACT_START_DATE is null)))
285 AND ((recinfo.CONTRACT_END_DATE = X_CONTRACT_END_DATE)
286 OR ((recinfo.CONTRACT_END_DATE is null) AND (X_CONTRACT_END_DATE is null)))
287 AND ((recinfo.WARRANTY_PERIOD = X_WARRANTY_PERIOD)
288 OR ((recinfo.WARRANTY_PERIOD is null) AND (X_WARRANTY_PERIOD is null)))
289 AND ((recinfo.PERIOD_UOM_CODE = X_PERIOD_UOM_CODE)
290 OR ((recinfo.PERIOD_UOM_CODE is null) AND (X_PERIOD_UOM_CODE is null)))
291 AND (recinfo.CONTRACT_EXPIRY_TYPE = X_CONTRACT_EXPIRY_TYPE)
292 AND ((recinfo.CLAIM_LABOUR_HOURS = X_CLAIM_LABOUR_HOURS)
293 OR ((recinfo.CLAIM_LABOUR_HOURS is null) AND (X_CLAIM_LABOUR_HOURS is null)))
294 AND ((recinfo.ACTIVE_START_DATE = X_ACTIVE_START_DATE)
295 OR ((recinfo.ACTIVE_START_DATE is null) AND (X_ACTIVE_START_DATE is null)))
296 AND ((recinfo.ACTIVE_END_DATE = X_ACTIVE_END_DATE)
297 OR ((recinfo.ACTIVE_END_DATE is null) AND (X_ACTIVE_END_DATE is null)))
298 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
299 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
300 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
301 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
302 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
303 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
304 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
305 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
306 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
307 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
308 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
309 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
310 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
311 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
312 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
313 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
314 ) then
315 null;
316 else
317 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
318 app_exception.raise_exception;
319 end if;
320
321 for tlinfo in c1 loop
322 if (tlinfo.BASELANG = 'Y') then
323 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
324 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
325 AND ((tlinfo.TERMS_AND_CONDITIONS = X_TERMS_AND_CONDITIONS)
326 OR ((tlinfo.TERMS_AND_CONDITIONS is null) AND (X_TERMS_AND_CONDITIONS is null)))
327 AND ((tlinfo.REACTION_AND_RESOL_TIME = X_REACTION_AND_RESOL_TIME)
328 OR ((tlinfo.REACTION_AND_RESOL_TIME is null) AND (X_REACTION_AND_RESOL_TIME is null)))
329 AND ((tlinfo.SERVICE_LEVEL_AGREEMENT = X_SERVICE_LEVEL_AGREEMENT)
330 OR ((tlinfo.SERVICE_LEVEL_AGREEMENT is null) AND (X_SERVICE_LEVEL_AGREEMENT is null)))
331 AND ((tlinfo.REMARKS = X_REMARKS)
332 OR ((tlinfo.REMARKS is null) AND (X_REMARKS is null)))
333 ) then
334 null;
335 else
336 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
337 app_exception.raise_exception;
338 end if;
339 end if;
340 end loop;
341 return;
342 end LOCK_ROW;
343
344 procedure UPDATE_ROW (
345 X_WARRANTY_TEMPLATE_ID in NUMBER,
346 X_OBJECT_VERSION_NUMBER in NUMBER,
347 X_NAME in VARCHAR2,
348 X_ENABLED_FLAG in VARCHAR2,
349 X_WARRANTY_TYPE in VARCHAR2,
350 X_WARRANTY_VENDOR_ID in NUMBER,
351 X_CONTRACT_START_DATE in DATE,
352 X_CONTRACT_END_DATE in DATE,
353 X_WARRANTY_PERIOD in NUMBER,
354 X_PERIOD_UOM_CODE in VARCHAR2,
355 X_CONTRACT_EXPIRY_TYPE in VARCHAR2,
356 X_CLAIM_LABOUR_HOURS in NUMBER,
357 X_ACTIVE_START_DATE in DATE,
358 X_ACTIVE_END_DATE in DATE,
359 X_ATTRIBUTE_CATEGORY in VARCHAR2,
360 X_ATTRIBUTE1 in VARCHAR2,
361 X_ATTRIBUTE2 in VARCHAR2,
362 X_ATTRIBUTE3 in VARCHAR2,
363 X_ATTRIBUTE4 in VARCHAR2,
364 X_ATTRIBUTE5 in VARCHAR2,
365 X_ATTRIBUTE6 in VARCHAR2,
366 X_ATTRIBUTE7 in VARCHAR2,
367 X_ATTRIBUTE8 in VARCHAR2,
368 X_ATTRIBUTE9 in VARCHAR2,
369 X_ATTRIBUTE10 in VARCHAR2,
370 X_ATTRIBUTE11 in VARCHAR2,
371 X_ATTRIBUTE12 in VARCHAR2,
372 X_ATTRIBUTE13 in VARCHAR2,
373 X_ATTRIBUTE14 in VARCHAR2,
374 X_ATTRIBUTE15 in VARCHAR2,
375 X_DESCRIPTION in VARCHAR2,
376 X_TERMS_AND_CONDITIONS in VARCHAR2,
377 X_REACTION_AND_RESOL_TIME in VARCHAR2,
378 X_SERVICE_LEVEL_AGREEMENT in VARCHAR2,
379 X_REMARKS in VARCHAR2,
380 X_LAST_UPDATE_DATE in DATE,
381 X_LAST_UPDATED_BY in NUMBER,
382 X_LAST_UPDATE_LOGIN in NUMBER
383 ) is
384 begin
385 update AHL_WARRANTY_TEMPLATES_B set
386 ATTRIBUTE8 = X_ATTRIBUTE8,
387 ATTRIBUTE9 = X_ATTRIBUTE9,
388 ATTRIBUTE10 = X_ATTRIBUTE10,
389 ATTRIBUTE11 = X_ATTRIBUTE11,
390 ATTRIBUTE12 = X_ATTRIBUTE12,
391 ATTRIBUTE13 = X_ATTRIBUTE13,
392 ATTRIBUTE14 = X_ATTRIBUTE14,
393 ATTRIBUTE15 = X_ATTRIBUTE15,
394 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
395 NAME = X_NAME,
396 ENABLED_FLAG = X_ENABLED_FLAG,
397 WARRANTY_TYPE = X_WARRANTY_TYPE,
398 WARRANTY_VENDOR_ID = X_WARRANTY_VENDOR_ID,
399 CONTRACT_START_DATE = X_CONTRACT_START_DATE,
400 CONTRACT_END_DATE = X_CONTRACT_END_DATE,
401 WARRANTY_PERIOD = X_WARRANTY_PERIOD,
402 PERIOD_UOM_CODE = X_PERIOD_UOM_CODE,
403 CONTRACT_EXPIRY_TYPE = X_CONTRACT_EXPIRY_TYPE,
404 CLAIM_LABOUR_HOURS = X_CLAIM_LABOUR_HOURS,
405 ACTIVE_START_DATE = X_ACTIVE_START_DATE,
406 ACTIVE_END_DATE = X_ACTIVE_END_DATE,
407 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
408 ATTRIBUTE1 = X_ATTRIBUTE1,
409 ATTRIBUTE2 = X_ATTRIBUTE2,
410 ATTRIBUTE3 = X_ATTRIBUTE3,
411 ATTRIBUTE4 = X_ATTRIBUTE4,
412 ATTRIBUTE5 = X_ATTRIBUTE5,
413 ATTRIBUTE6 = X_ATTRIBUTE6,
414 ATTRIBUTE7 = X_ATTRIBUTE7,
415 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
416 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
417 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
418 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID;
419
420 if (sql%notfound) then
421 raise no_data_found;
422 end if;
423
424 update AHL_WARRANTY_TEMPLATES_TL set
425 DESCRIPTION = X_DESCRIPTION,
426 TERMS_AND_CONDITIONS = X_TERMS_AND_CONDITIONS,
427 REACTION_AND_RESOL_TIME = X_REACTION_AND_RESOL_TIME,
428 SERVICE_LEVEL_AGREEMENT = X_SERVICE_LEVEL_AGREEMENT,
429 REMARKS = X_REMARKS,
430 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
431 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
432 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
433 SOURCE_LANG = userenv('LANG')
434 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID
435 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
436
437 if (sql%notfound) then
438 raise no_data_found;
439 end if;
440 end UPDATE_ROW;
441
442 procedure DELETE_ROW (
443 X_WARRANTY_TEMPLATE_ID in NUMBER
444 ) is
445 begin
446 delete from AHL_WARRANTY_TEMPLATES_TL
447 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID;
448
449 if (sql%notfound) then
450 raise no_data_found;
451 end if;
452
453 delete from AHL_WARRANTY_TEMPLATES_B
454 where WARRANTY_TEMPLATE_ID = X_WARRANTY_TEMPLATE_ID;
455
456 if (sql%notfound) then
457 raise no_data_found;
458 end if;
459 end DELETE_ROW;
460
461 procedure ADD_LANGUAGE
462 is
466 (select NULL
463 begin
464 delete from AHL_WARRANTY_TEMPLATES_TL T
465 where not exists
467 from AHL_WARRANTY_TEMPLATES_B B
468 where B.WARRANTY_TEMPLATE_ID = T.WARRANTY_TEMPLATE_ID
469 );
470
471 update AHL_WARRANTY_TEMPLATES_TL T set (
472 DESCRIPTION,
473 TERMS_AND_CONDITIONS,
474 REACTION_AND_RESOL_TIME,
475 SERVICE_LEVEL_AGREEMENT,
476 REMARKS
477 ) = (select
478 B.DESCRIPTION,
479 B.TERMS_AND_CONDITIONS,
480 B.REACTION_AND_RESOL_TIME,
481 B.SERVICE_LEVEL_AGREEMENT,
482 B.REMARKS
483 from AHL_WARRANTY_TEMPLATES_TL B
484 where B.WARRANTY_TEMPLATE_ID = T.WARRANTY_TEMPLATE_ID
485 and B.LANGUAGE = T.SOURCE_LANG)
486 where (
487 T.WARRANTY_TEMPLATE_ID,
488 T.LANGUAGE
489 ) in (select
490 SUBT.WARRANTY_TEMPLATE_ID,
491 SUBT.LANGUAGE
492 from AHL_WARRANTY_TEMPLATES_TL SUBB, AHL_WARRANTY_TEMPLATES_TL SUBT
493 where SUBB.WARRANTY_TEMPLATE_ID = SUBT.WARRANTY_TEMPLATE_ID
494 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
495 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
496 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
497 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
498 or SUBB.TERMS_AND_CONDITIONS <> SUBT.TERMS_AND_CONDITIONS
499 or (SUBB.TERMS_AND_CONDITIONS is null and SUBT.TERMS_AND_CONDITIONS is not null)
500 or (SUBB.TERMS_AND_CONDITIONS is not null and SUBT.TERMS_AND_CONDITIONS is null)
501 or SUBB.REACTION_AND_RESOL_TIME <> SUBT.REACTION_AND_RESOL_TIME
502 or (SUBB.REACTION_AND_RESOL_TIME is null and SUBT.REACTION_AND_RESOL_TIME is not null)
503 or (SUBB.REACTION_AND_RESOL_TIME is not null and SUBT.REACTION_AND_RESOL_TIME is null)
504 or SUBB.SERVICE_LEVEL_AGREEMENT <> SUBT.SERVICE_LEVEL_AGREEMENT
505 or (SUBB.SERVICE_LEVEL_AGREEMENT is null and SUBT.SERVICE_LEVEL_AGREEMENT is not null)
506 or (SUBB.SERVICE_LEVEL_AGREEMENT is not null and SUBT.SERVICE_LEVEL_AGREEMENT is null)
507 or SUBB.REMARKS <> SUBT.REMARKS
508 or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
509 or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
510 ));
511
512 insert into AHL_WARRANTY_TEMPLATES_TL (
513 WARRANTY_TEMPLATE_ID,
514 DESCRIPTION,
515 TERMS_AND_CONDITIONS,
516 REACTION_AND_RESOL_TIME,
517 SERVICE_LEVEL_AGREEMENT,
518 REMARKS,
519 LAST_UPDATE_DATE,
520 LAST_UPDATED_BY,
521 CREATION_DATE,
522 CREATED_BY,
523 LAST_UPDATE_LOGIN,
524 LANGUAGE,
525 SOURCE_LANG
526 ) select /*+ ORDERED */
527 B.WARRANTY_TEMPLATE_ID,
528 B.DESCRIPTION,
529 B.TERMS_AND_CONDITIONS,
530 B.REACTION_AND_RESOL_TIME,
531 B.SERVICE_LEVEL_AGREEMENT,
532 B.REMARKS,
533 B.LAST_UPDATE_DATE,
534 B.LAST_UPDATED_BY,
535 B.CREATION_DATE,
536 B.CREATED_BY,
537 B.LAST_UPDATE_LOGIN,
538 L.LANGUAGE_CODE,
539 B.SOURCE_LANG
540 from AHL_WARRANTY_TEMPLATES_TL B, FND_LANGUAGES L
541 where L.INSTALLED_FLAG in ('I', 'B')
542 and B.LANGUAGE = userenv('LANG')
543 and not exists
544 (select NULL
545 from AHL_WARRANTY_TEMPLATES_TL T
546 where T.WARRANTY_TEMPLATE_ID = B.WARRANTY_TEMPLATE_ID
547 and T.LANGUAGE = L.LANGUAGE_CODE);
548 end ADD_LANGUAGE;
549
550 end AHL_WARRANTY_TEMPLATES_PKG;
[Home] [Help]