1 package body AHL_WARRANTY_CLAIMS_PKG as
2 /* $Header: AHLLWCLB.pls 120.0 2011/04/20 11:22:31 sukhwsin noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_WARRANTY_CLAIM_ID in NUMBER,
6 X_R_ATTRIBUTE13 in VARCHAR2,
7 X_R_ATTRIBUTE14 in VARCHAR2,
8 X_R_ATTRIBUTE15 in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_WARRANTY_CLAIM_NUMBER in NUMBER,
11 X_CLAIM_NAME in VARCHAR2,
12 X_CLAIM_STATUS_CODE in VARCHAR2,
13 X_WARRANTY_VENDOR_ID in NUMBER,
14 X_CLAIM_DATE in DATE,
15 X_OSP_FLAG in VARCHAR2,
16 X_AMOUNT in NUMBER,
17 X_AMOUNT_CURRENCY_CODE in VARCHAR2,
18 X_TOTAL_ORDER_AMOUNT in NUMBER,
19 X_REIMBURSEMENT_AMOUNT in NUMBER,
20 X_REIMBURSEMENT_CURR_CODE in VARCHAR2,
21 X_REIMBURSEMENT_TYPE in VARCHAR2,
22 X_REIMBURSEMENT_REF_DOC in VARCHAR2,
23 X_REIMBURSEMENT_DATE in DATE,
24 X_ATTRIBUTE_CATEGORY in VARCHAR2,
25 X_ATTRIBUTE1 in VARCHAR2,
26 X_ATTRIBUTE2 in VARCHAR2,
27 X_ATTRIBUTE3 in VARCHAR2,
28 X_ATTRIBUTE4 in VARCHAR2,
29 X_ATTRIBUTE5 in VARCHAR2,
30 X_ATTRIBUTE6 in VARCHAR2,
31 X_ATTRIBUTE7 in VARCHAR2,
32 X_ATTRIBUTE8 in VARCHAR2,
33 X_ATTRIBUTE9 in VARCHAR2,
34 X_ATTRIBUTE10 in VARCHAR2,
35 X_ATTRIBUTE11 in VARCHAR2,
36 X_ATTRIBUTE12 in VARCHAR2,
37 X_ATTRIBUTE13 in VARCHAR2,
38 X_ATTRIBUTE14 in VARCHAR2,
39 X_ATTRIBUTE15 in VARCHAR2,
40 X_R_ATTRIBUTE_CATEGORY in VARCHAR2,
41 X_R_ATTRIBUTE1 in VARCHAR2,
42 X_R_ATTRIBUTE2 in VARCHAR2,
43 X_R_ATTRIBUTE3 in VARCHAR2,
44 X_R_ATTRIBUTE4 in VARCHAR2,
45 X_R_ATTRIBUTE5 in VARCHAR2,
46 X_R_ATTRIBUTE6 in VARCHAR2,
47 X_R_ATTRIBUTE7 in VARCHAR2,
48 X_R_ATTRIBUTE8 in VARCHAR2,
49 X_R_ATTRIBUTE9 in VARCHAR2,
50 X_R_ATTRIBUTE10 in VARCHAR2,
51 X_R_ATTRIBUTE11 in VARCHAR2,
52 X_R_ATTRIBUTE12 in VARCHAR2,
53 X_NOTES in VARCHAR2,
54 X_REIMBURSEMENT_NOTES in VARCHAR2,
55 X_CREATION_DATE in DATE,
56 X_CREATED_BY in NUMBER,
57 X_LAST_UPDATE_DATE in DATE,
58 X_LAST_UPDATED_BY in NUMBER,
59 X_LAST_UPDATE_LOGIN in NUMBER
60 ) is
61 cursor C is select ROWID from AHL_WARRANTY_CLAIMS_B
62 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID
63 ;
64 begin
65 insert into AHL_WARRANTY_CLAIMS_B (
66 R_ATTRIBUTE13,
67 R_ATTRIBUTE14,
68 R_ATTRIBUTE15,
69 WARRANTY_CLAIM_ID,
70 OBJECT_VERSION_NUMBER,
71 WARRANTY_CLAIM_NUMBER,
72 CLAIM_NAME,
73 CLAIM_STATUS_CODE,
74 WARRANTY_VENDOR_ID,
75 CLAIM_DATE,
76 OSP_FLAG,
77 AMOUNT,
78 AMOUNT_CURRENCY_CODE,
79 TOTAL_ORDER_AMOUNT,
80 REIMBURSEMENT_AMOUNT,
81 REIMBURSEMENT_CURR_CODE,
82 REIMBURSEMENT_TYPE,
83 REIMBURSEMENT_REF_DOC,
84 REIMBURSEMENT_DATE,
85 ATTRIBUTE_CATEGORY,
86 ATTRIBUTE1,
87 ATTRIBUTE2,
88 ATTRIBUTE3,
89 ATTRIBUTE4,
90 ATTRIBUTE5,
91 ATTRIBUTE6,
92 ATTRIBUTE7,
93 ATTRIBUTE8,
94 ATTRIBUTE9,
95 ATTRIBUTE10,
96 ATTRIBUTE11,
97 ATTRIBUTE12,
98 ATTRIBUTE13,
99 ATTRIBUTE14,
100 ATTRIBUTE15,
101 R_ATTRIBUTE_CATEGORY,
102 R_ATTRIBUTE1,
103 R_ATTRIBUTE2,
104 R_ATTRIBUTE3,
105 R_ATTRIBUTE4,
106 R_ATTRIBUTE5,
107 R_ATTRIBUTE6,
108 R_ATTRIBUTE7,
109 R_ATTRIBUTE8,
110 R_ATTRIBUTE9,
111 R_ATTRIBUTE10,
112 R_ATTRIBUTE11,
113 R_ATTRIBUTE12,
114 CREATION_DATE,
115 CREATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN
119 ) values (
120 X_R_ATTRIBUTE13,
121 X_R_ATTRIBUTE14,
122 X_R_ATTRIBUTE15,
123 X_WARRANTY_CLAIM_ID,
124 X_OBJECT_VERSION_NUMBER,
125 X_WARRANTY_CLAIM_NUMBER,
126 X_CLAIM_NAME,
127 X_CLAIM_STATUS_CODE,
128 X_WARRANTY_VENDOR_ID,
129 X_CLAIM_DATE,
130 X_OSP_FLAG,
131 X_AMOUNT,
132 X_AMOUNT_CURRENCY_CODE,
133 X_TOTAL_ORDER_AMOUNT,
134 X_REIMBURSEMENT_AMOUNT,
135 X_REIMBURSEMENT_CURR_CODE,
136 X_REIMBURSEMENT_TYPE,
137 X_REIMBURSEMENT_REF_DOC,
138 X_REIMBURSEMENT_DATE,
139 X_ATTRIBUTE_CATEGORY,
140 X_ATTRIBUTE1,
141 X_ATTRIBUTE2,
142 X_ATTRIBUTE3,
143 X_ATTRIBUTE4,
144 X_ATTRIBUTE5,
145 X_ATTRIBUTE6,
146 X_ATTRIBUTE7,
147 X_ATTRIBUTE8,
148 X_ATTRIBUTE9,
149 X_ATTRIBUTE10,
150 X_ATTRIBUTE11,
151 X_ATTRIBUTE12,
152 X_ATTRIBUTE13,
153 X_ATTRIBUTE14,
154 X_ATTRIBUTE15,
155 X_R_ATTRIBUTE_CATEGORY,
156 X_R_ATTRIBUTE1,
157 X_R_ATTRIBUTE2,
158 X_R_ATTRIBUTE3,
159 X_R_ATTRIBUTE4,
160 X_R_ATTRIBUTE5,
161 X_R_ATTRIBUTE6,
162 X_R_ATTRIBUTE7,
163 X_R_ATTRIBUTE8,
164 X_R_ATTRIBUTE9,
165 X_R_ATTRIBUTE10,
166 X_R_ATTRIBUTE11,
167 X_R_ATTRIBUTE12,
168 X_CREATION_DATE,
169 X_CREATED_BY,
170 X_LAST_UPDATE_DATE,
171 X_LAST_UPDATED_BY,
172 X_LAST_UPDATE_LOGIN
173 );
174
175 insert into AHL_WARRANTY_CLAIMS_TL (
176 WARRANTY_CLAIM_ID,
177 LAST_UPDATE_DATE,
178 LAST_UPDATED_BY,
179 CREATION_DATE,
180 CREATED_BY,
181 LAST_UPDATE_LOGIN,
182 NOTES,
183 REIMBURSEMENT_NOTES,
184 LANGUAGE,
185 SOURCE_LANG
186 ) select
187 X_WARRANTY_CLAIM_ID,
188 X_LAST_UPDATE_DATE,
189 X_LAST_UPDATED_BY,
190 X_CREATION_DATE,
191 X_CREATED_BY,
192 X_LAST_UPDATE_LOGIN,
193 X_NOTES,
194 X_REIMBURSEMENT_NOTES,
195 L.LANGUAGE_CODE,
196 userenv('LANG')
197 from FND_LANGUAGES L
198 where L.INSTALLED_FLAG in ('I', 'B')
199 and not exists
200 (select NULL
201 from AHL_WARRANTY_CLAIMS_TL T
202 where T.WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID
203 and T.LANGUAGE = L.LANGUAGE_CODE);
204
205 open c;
206 fetch c into X_ROWID;
207 if (c%notfound) then
208 close c;
209 raise no_data_found;
210 end if;
211 close c;
212
213 end INSERT_ROW;
214
215 procedure LOCK_ROW (
216 X_WARRANTY_CLAIM_ID in NUMBER,
217 X_R_ATTRIBUTE13 in VARCHAR2,
218 X_R_ATTRIBUTE14 in VARCHAR2,
219 X_R_ATTRIBUTE15 in VARCHAR2,
220 X_OBJECT_VERSION_NUMBER in NUMBER,
221 X_WARRANTY_CLAIM_NUMBER in NUMBER,
222 X_CLAIM_NAME in VARCHAR2,
223 X_CLAIM_STATUS_CODE in VARCHAR2,
224 X_WARRANTY_VENDOR_ID in NUMBER,
225 X_CLAIM_DATE in DATE,
226 X_OSP_FLAG in VARCHAR2,
227 X_AMOUNT in NUMBER,
228 X_AMOUNT_CURRENCY_CODE in VARCHAR2,
229 X_TOTAL_ORDER_AMOUNT in NUMBER,
230 X_REIMBURSEMENT_AMOUNT in NUMBER,
231 X_REIMBURSEMENT_CURR_CODE in VARCHAR2,
232 X_REIMBURSEMENT_TYPE in VARCHAR2,
233 X_REIMBURSEMENT_REF_DOC in VARCHAR2,
234 X_REIMBURSEMENT_DATE in DATE,
235 X_ATTRIBUTE_CATEGORY in VARCHAR2,
239 X_ATTRIBUTE4 in VARCHAR2,
236 X_ATTRIBUTE1 in VARCHAR2,
237 X_ATTRIBUTE2 in VARCHAR2,
238 X_ATTRIBUTE3 in VARCHAR2,
240 X_ATTRIBUTE5 in VARCHAR2,
241 X_ATTRIBUTE6 in VARCHAR2,
242 X_ATTRIBUTE7 in VARCHAR2,
243 X_ATTRIBUTE8 in VARCHAR2,
244 X_ATTRIBUTE9 in VARCHAR2,
245 X_ATTRIBUTE10 in VARCHAR2,
246 X_ATTRIBUTE11 in VARCHAR2,
247 X_ATTRIBUTE12 in VARCHAR2,
248 X_ATTRIBUTE13 in VARCHAR2,
249 X_ATTRIBUTE14 in VARCHAR2,
250 X_ATTRIBUTE15 in VARCHAR2,
251 X_R_ATTRIBUTE_CATEGORY in VARCHAR2,
252 X_R_ATTRIBUTE1 in VARCHAR2,
253 X_R_ATTRIBUTE2 in VARCHAR2,
254 X_R_ATTRIBUTE3 in VARCHAR2,
255 X_R_ATTRIBUTE4 in VARCHAR2,
256 X_R_ATTRIBUTE5 in VARCHAR2,
257 X_R_ATTRIBUTE6 in VARCHAR2,
258 X_R_ATTRIBUTE7 in VARCHAR2,
259 X_R_ATTRIBUTE8 in VARCHAR2,
260 X_R_ATTRIBUTE9 in VARCHAR2,
261 X_R_ATTRIBUTE10 in VARCHAR2,
262 X_R_ATTRIBUTE11 in VARCHAR2,
263 X_R_ATTRIBUTE12 in VARCHAR2,
264 X_NOTES in VARCHAR2,
265 X_REIMBURSEMENT_NOTES in VARCHAR2
266 ) is
267 cursor c is select
268 R_ATTRIBUTE13,
269 R_ATTRIBUTE14,
270 R_ATTRIBUTE15,
271 OBJECT_VERSION_NUMBER,
272 WARRANTY_CLAIM_NUMBER,
273 CLAIM_NAME,
274 CLAIM_STATUS_CODE,
275 WARRANTY_VENDOR_ID,
276 CLAIM_DATE,
277 OSP_FLAG,
278 AMOUNT,
279 AMOUNT_CURRENCY_CODE,
280 TOTAL_ORDER_AMOUNT,
281 REIMBURSEMENT_AMOUNT,
282 REIMBURSEMENT_CURR_CODE,
283 REIMBURSEMENT_TYPE,
284 REIMBURSEMENT_REF_DOC,
285 REIMBURSEMENT_DATE,
286 ATTRIBUTE_CATEGORY,
287 ATTRIBUTE1,
288 ATTRIBUTE2,
289 ATTRIBUTE3,
290 ATTRIBUTE4,
291 ATTRIBUTE5,
292 ATTRIBUTE6,
293 ATTRIBUTE7,
294 ATTRIBUTE8,
295 ATTRIBUTE9,
296 ATTRIBUTE10,
297 ATTRIBUTE11,
298 ATTRIBUTE12,
299 ATTRIBUTE13,
300 ATTRIBUTE14,
301 ATTRIBUTE15,
302 R_ATTRIBUTE_CATEGORY,
303 R_ATTRIBUTE1,
304 R_ATTRIBUTE2,
305 R_ATTRIBUTE3,
306 R_ATTRIBUTE4,
307 R_ATTRIBUTE5,
308 R_ATTRIBUTE6,
309 R_ATTRIBUTE7,
310 R_ATTRIBUTE8,
311 R_ATTRIBUTE9,
312 R_ATTRIBUTE10,
313 R_ATTRIBUTE11,
314 R_ATTRIBUTE12
315 from AHL_WARRANTY_CLAIMS_B
316 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID
317 for update of WARRANTY_CLAIM_ID nowait;
318 recinfo c%rowtype;
319
320 cursor c1 is select
321 NOTES,
322 REIMBURSEMENT_NOTES,
323 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
324 from AHL_WARRANTY_CLAIMS_TL
325 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID
326 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
327 for update of WARRANTY_CLAIM_ID nowait;
328 begin
329 open c;
330 fetch c into recinfo;
331 if (c%notfound) then
332 close c;
333 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
334 app_exception.raise_exception;
335 end if;
336 close c;
337 if ( ((recinfo.R_ATTRIBUTE13 = X_R_ATTRIBUTE13)
338 OR ((recinfo.R_ATTRIBUTE13 is null) AND (X_R_ATTRIBUTE13 is null)))
339 AND ((recinfo.R_ATTRIBUTE14 = X_R_ATTRIBUTE14)
340 OR ((recinfo.R_ATTRIBUTE14 is null) AND (X_R_ATTRIBUTE14 is null)))
341 AND ((recinfo.R_ATTRIBUTE15 = X_R_ATTRIBUTE15)
342 OR ((recinfo.R_ATTRIBUTE15 is null) AND (X_R_ATTRIBUTE15 is null)))
343 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
344 AND (recinfo.WARRANTY_CLAIM_NUMBER = X_WARRANTY_CLAIM_NUMBER)
345 AND (recinfo.CLAIM_NAME = X_CLAIM_NAME)
346 AND (recinfo.CLAIM_STATUS_CODE = X_CLAIM_STATUS_CODE)
347 AND (recinfo.WARRANTY_VENDOR_ID = X_WARRANTY_VENDOR_ID)
348 AND (recinfo.CLAIM_DATE = X_CLAIM_DATE)
349 AND ((recinfo.OSP_FLAG = X_OSP_FLAG)
350 OR ((recinfo.OSP_FLAG is null) AND (X_OSP_FLAG is null)))
351 AND ((recinfo.AMOUNT = X_AMOUNT)
352 OR ((recinfo.AMOUNT is null) AND (X_AMOUNT is null)))
353 AND (recinfo.AMOUNT_CURRENCY_CODE = X_AMOUNT_CURRENCY_CODE)
354 AND ((recinfo.TOTAL_ORDER_AMOUNT = X_TOTAL_ORDER_AMOUNT)
355 OR ((recinfo.TOTAL_ORDER_AMOUNT is null) AND (X_TOTAL_ORDER_AMOUNT is null)))
356 AND ((recinfo.REIMBURSEMENT_AMOUNT = X_REIMBURSEMENT_AMOUNT)
357 OR ((recinfo.REIMBURSEMENT_AMOUNT is null) AND (X_REIMBURSEMENT_AMOUNT is null)))
358 AND ((recinfo.REIMBURSEMENT_CURR_CODE = X_REIMBURSEMENT_CURR_CODE)
359 OR ((recinfo.REIMBURSEMENT_CURR_CODE is null) AND (X_REIMBURSEMENT_CURR_CODE is null)))
360 AND ((recinfo.REIMBURSEMENT_TYPE = X_REIMBURSEMENT_TYPE)
361 OR ((recinfo.REIMBURSEMENT_TYPE is null) AND (X_REIMBURSEMENT_TYPE is null)))
362 AND ((recinfo.REIMBURSEMENT_REF_DOC = X_REIMBURSEMENT_REF_DOC)
363 OR ((recinfo.REIMBURSEMENT_REF_DOC is null) AND (X_REIMBURSEMENT_REF_DOC is null)))
364 AND ((recinfo.REIMBURSEMENT_DATE = X_REIMBURSEMENT_DATE)
365 OR ((recinfo.REIMBURSEMENT_DATE is null) AND (X_REIMBURSEMENT_DATE is null)))
366 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
367 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
368 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
369 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
370 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
371 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
372 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
376 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
373 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
374 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
375 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
377 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
378 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
379 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
380 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
381 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
382 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
383 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
384 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
388 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
385 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
386 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
387 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
389 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
390 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
391 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
392 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
393 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
394 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
395 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
396 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
397 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
398 AND ((recinfo.R_ATTRIBUTE_CATEGORY = X_R_ATTRIBUTE_CATEGORY)
399 OR ((recinfo.R_ATTRIBUTE_CATEGORY is null) AND (X_R_ATTRIBUTE_CATEGORY is null)))
400 AND ((recinfo.R_ATTRIBUTE1 = X_R_ATTRIBUTE1)
401 OR ((recinfo.R_ATTRIBUTE1 is null) AND (X_R_ATTRIBUTE1 is null)))
402 AND ((recinfo.R_ATTRIBUTE2 = X_R_ATTRIBUTE2)
403 OR ((recinfo.R_ATTRIBUTE2 is null) AND (X_R_ATTRIBUTE2 is null)))
404 AND ((recinfo.R_ATTRIBUTE3 = X_R_ATTRIBUTE3)
405 OR ((recinfo.R_ATTRIBUTE3 is null) AND (X_R_ATTRIBUTE3 is null)))
406 AND ((recinfo.R_ATTRIBUTE4 = X_R_ATTRIBUTE4)
407 OR ((recinfo.R_ATTRIBUTE4 is null) AND (X_R_ATTRIBUTE4 is null)))
408 AND ((recinfo.R_ATTRIBUTE5 = X_R_ATTRIBUTE5)
409 OR ((recinfo.R_ATTRIBUTE5 is null) AND (X_R_ATTRIBUTE5 is null)))
410 AND ((recinfo.R_ATTRIBUTE6 = X_R_ATTRIBUTE6)
411 OR ((recinfo.R_ATTRIBUTE6 is null) AND (X_R_ATTRIBUTE6 is null)))
412 AND ((recinfo.R_ATTRIBUTE7 = X_R_ATTRIBUTE7)
413 OR ((recinfo.R_ATTRIBUTE7 is null) AND (X_R_ATTRIBUTE7 is null)))
414 AND ((recinfo.R_ATTRIBUTE8 = X_R_ATTRIBUTE8)
415 OR ((recinfo.R_ATTRIBUTE8 is null) AND (X_R_ATTRIBUTE8 is null)))
416 AND ((recinfo.R_ATTRIBUTE9 = X_R_ATTRIBUTE9)
417 OR ((recinfo.R_ATTRIBUTE9 is null) AND (X_R_ATTRIBUTE9 is null)))
418 AND ((recinfo.R_ATTRIBUTE10 = X_R_ATTRIBUTE10)
419 OR ((recinfo.R_ATTRIBUTE10 is null) AND (X_R_ATTRIBUTE10 is null)))
420 AND ((recinfo.R_ATTRIBUTE11 = X_R_ATTRIBUTE11)
421 OR ((recinfo.R_ATTRIBUTE11 is null) AND (X_R_ATTRIBUTE11 is null)))
422 AND ((recinfo.R_ATTRIBUTE12 = X_R_ATTRIBUTE12)
423 OR ((recinfo.R_ATTRIBUTE12 is null) AND (X_R_ATTRIBUTE12 is null)))
424 ) then
425 null;
426 else
427 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
428 app_exception.raise_exception;
429 end if;
430
431 for tlinfo in c1 loop
432 if (tlinfo.BASELANG = 'Y') then
433 if ( ((tlinfo.NOTES = X_NOTES)
434 OR ((tlinfo.NOTES is null) AND (X_NOTES is null)))
435 AND ((tlinfo.REIMBURSEMENT_NOTES = X_REIMBURSEMENT_NOTES)
436 OR ((tlinfo.REIMBURSEMENT_NOTES is null) AND (X_REIMBURSEMENT_NOTES is null)))
437 ) then
438 null;
439 else
440 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
444 end loop;
441 app_exception.raise_exception;
442 end if;
443 end if;
445 return;
446 end LOCK_ROW;
447
448 procedure UPDATE_ROW (
449 X_WARRANTY_CLAIM_ID in NUMBER,
450 X_R_ATTRIBUTE13 in VARCHAR2,
451 X_R_ATTRIBUTE14 in VARCHAR2,
452 X_R_ATTRIBUTE15 in VARCHAR2,
453 X_OBJECT_VERSION_NUMBER in NUMBER,
454 X_WARRANTY_CLAIM_NUMBER in NUMBER,
455 X_CLAIM_NAME in VARCHAR2,
456 X_CLAIM_STATUS_CODE in VARCHAR2,
457 X_WARRANTY_VENDOR_ID in NUMBER,
458 X_CLAIM_DATE in DATE,
459 X_OSP_FLAG in VARCHAR2,
460 X_AMOUNT in NUMBER,
461 X_AMOUNT_CURRENCY_CODE in VARCHAR2,
462 X_TOTAL_ORDER_AMOUNT in NUMBER,
463 X_REIMBURSEMENT_AMOUNT in NUMBER,
464 X_REIMBURSEMENT_CURR_CODE in VARCHAR2,
465 X_REIMBURSEMENT_TYPE in VARCHAR2,
466 X_REIMBURSEMENT_REF_DOC in VARCHAR2,
467 X_REIMBURSEMENT_DATE in DATE,
468 X_ATTRIBUTE_CATEGORY in VARCHAR2,
469 X_ATTRIBUTE1 in VARCHAR2,
470 X_ATTRIBUTE2 in VARCHAR2,
471 X_ATTRIBUTE3 in VARCHAR2,
472 X_ATTRIBUTE4 in VARCHAR2,
473 X_ATTRIBUTE5 in VARCHAR2,
474 X_ATTRIBUTE6 in VARCHAR2,
475 X_ATTRIBUTE7 in VARCHAR2,
476 X_ATTRIBUTE8 in VARCHAR2,
477 X_ATTRIBUTE9 in VARCHAR2,
478 X_ATTRIBUTE10 in VARCHAR2,
479 X_ATTRIBUTE11 in VARCHAR2,
480 X_ATTRIBUTE12 in VARCHAR2,
481 X_ATTRIBUTE13 in VARCHAR2,
482 X_ATTRIBUTE14 in VARCHAR2,
483 X_ATTRIBUTE15 in VARCHAR2,
484 X_R_ATTRIBUTE_CATEGORY in VARCHAR2,
485 X_R_ATTRIBUTE1 in VARCHAR2,
486 X_R_ATTRIBUTE2 in VARCHAR2,
487 X_R_ATTRIBUTE3 in VARCHAR2,
488 X_R_ATTRIBUTE4 in VARCHAR2,
489 X_R_ATTRIBUTE5 in VARCHAR2,
490 X_R_ATTRIBUTE6 in VARCHAR2,
491 X_R_ATTRIBUTE7 in VARCHAR2,
492 X_R_ATTRIBUTE8 in VARCHAR2,
493 X_R_ATTRIBUTE9 in VARCHAR2,
494 X_R_ATTRIBUTE10 in VARCHAR2,
495 X_R_ATTRIBUTE11 in VARCHAR2,
496 X_R_ATTRIBUTE12 in VARCHAR2,
497 X_NOTES in VARCHAR2,
498 X_REIMBURSEMENT_NOTES in VARCHAR2,
499 X_LAST_UPDATE_DATE in DATE,
500 X_LAST_UPDATED_BY in NUMBER,
501 X_LAST_UPDATE_LOGIN in NUMBER
502 ) is
503 begin
504 update AHL_WARRANTY_CLAIMS_B set
505 R_ATTRIBUTE13 = X_R_ATTRIBUTE13,
506 R_ATTRIBUTE14 = X_R_ATTRIBUTE14,
507 R_ATTRIBUTE15 = X_R_ATTRIBUTE15,
508 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
509 WARRANTY_CLAIM_NUMBER = X_WARRANTY_CLAIM_NUMBER,
510 CLAIM_NAME = X_CLAIM_NAME,
511 CLAIM_STATUS_CODE = X_CLAIM_STATUS_CODE,
512 WARRANTY_VENDOR_ID = X_WARRANTY_VENDOR_ID,
513 CLAIM_DATE = X_CLAIM_DATE,
514 OSP_FLAG = X_OSP_FLAG,
515 AMOUNT = X_AMOUNT,
516 AMOUNT_CURRENCY_CODE = X_AMOUNT_CURRENCY_CODE,
517 TOTAL_ORDER_AMOUNT = X_TOTAL_ORDER_AMOUNT,
518 REIMBURSEMENT_AMOUNT = X_REIMBURSEMENT_AMOUNT,
519 REIMBURSEMENT_CURR_CODE = X_REIMBURSEMENT_CURR_CODE,
520 REIMBURSEMENT_TYPE = X_REIMBURSEMENT_TYPE,
521 REIMBURSEMENT_REF_DOC = X_REIMBURSEMENT_REF_DOC,
522 REIMBURSEMENT_DATE = X_REIMBURSEMENT_DATE,
523 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
524 ATTRIBUTE1 = X_ATTRIBUTE1,
525 ATTRIBUTE2 = X_ATTRIBUTE2,
526 ATTRIBUTE3 = X_ATTRIBUTE3,
527 ATTRIBUTE4 = X_ATTRIBUTE4,
528 ATTRIBUTE5 = X_ATTRIBUTE5,
529 ATTRIBUTE6 = X_ATTRIBUTE6,
530 ATTRIBUTE7 = X_ATTRIBUTE7,
531 ATTRIBUTE8 = X_ATTRIBUTE8,
532 ATTRIBUTE9 = X_ATTRIBUTE9,
533 ATTRIBUTE10 = X_ATTRIBUTE10,
534 ATTRIBUTE11 = X_ATTRIBUTE11,
535 ATTRIBUTE12 = X_ATTRIBUTE12,
536 ATTRIBUTE13 = X_ATTRIBUTE13,
537 ATTRIBUTE14 = X_ATTRIBUTE14,
543 R_ATTRIBUTE4 = X_R_ATTRIBUTE4,
538 ATTRIBUTE15 = X_ATTRIBUTE15,
539 R_ATTRIBUTE_CATEGORY = X_R_ATTRIBUTE_CATEGORY,
540 R_ATTRIBUTE1 = X_R_ATTRIBUTE1,
541 R_ATTRIBUTE2 = X_R_ATTRIBUTE2,
542 R_ATTRIBUTE3 = X_R_ATTRIBUTE3,
544 R_ATTRIBUTE5 = X_R_ATTRIBUTE5,
545 R_ATTRIBUTE6 = X_R_ATTRIBUTE6,
546 R_ATTRIBUTE7 = X_R_ATTRIBUTE7,
547 R_ATTRIBUTE8 = X_R_ATTRIBUTE8,
548 R_ATTRIBUTE9 = X_R_ATTRIBUTE9,
549 R_ATTRIBUTE10 = X_R_ATTRIBUTE10,
550 R_ATTRIBUTE11 = X_R_ATTRIBUTE11,
551 R_ATTRIBUTE12 = X_R_ATTRIBUTE12,
552 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
553 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
554 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
555 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID;
556
557 if (sql%notfound) then
558 raise no_data_found;
559 end if;
560
561 update AHL_WARRANTY_CLAIMS_TL set
562 NOTES = X_NOTES,
563 REIMBURSEMENT_NOTES = X_REIMBURSEMENT_NOTES,
564 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
565 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
566 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
567 SOURCE_LANG = userenv('LANG')
568 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID
569 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
570
571 if (sql%notfound) then
572 raise no_data_found;
573 end if;
574 end UPDATE_ROW;
575
576 procedure DELETE_ROW (
577 X_WARRANTY_CLAIM_ID in NUMBER
578 ) is
579 begin
580 delete from AHL_WARRANTY_CLAIMS_TL
581 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID;
582
583 if (sql%notfound) then
584 raise no_data_found;
585 end if;
586
587 delete from AHL_WARRANTY_CLAIMS_B
588 where WARRANTY_CLAIM_ID = X_WARRANTY_CLAIM_ID;
589
590 if (sql%notfound) then
591 raise no_data_found;
592 end if;
593 end DELETE_ROW;
594
595 procedure ADD_LANGUAGE
596 is
597 begin
598 delete from AHL_WARRANTY_CLAIMS_TL T
599 where not exists
600 (select NULL
601 from AHL_WARRANTY_CLAIMS_B B
602 where B.WARRANTY_CLAIM_ID = T.WARRANTY_CLAIM_ID
603 );
604
605 update AHL_WARRANTY_CLAIMS_TL T set (
606 NOTES,
607 REIMBURSEMENT_NOTES
608 ) = (select
609 B.NOTES,
610 B.REIMBURSEMENT_NOTES
611 from AHL_WARRANTY_CLAIMS_TL B
612 where B.WARRANTY_CLAIM_ID = T.WARRANTY_CLAIM_ID
613 and B.LANGUAGE = T.SOURCE_LANG)
614 where (
615 T.WARRANTY_CLAIM_ID,
616 T.LANGUAGE
617 ) in (select
618 SUBT.WARRANTY_CLAIM_ID,
619 SUBT.LANGUAGE
620 from AHL_WARRANTY_CLAIMS_TL SUBB, AHL_WARRANTY_CLAIMS_TL SUBT
621 where SUBB.WARRANTY_CLAIM_ID = SUBT.WARRANTY_CLAIM_ID
622 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
623 and (SUBB.NOTES <> SUBT.NOTES
624 or (SUBB.NOTES is null and SUBT.NOTES is not null)
625 or (SUBB.NOTES is not null and SUBT.NOTES is null)
626 or SUBB.REIMBURSEMENT_NOTES <> SUBT.REIMBURSEMENT_NOTES
627 or (SUBB.REIMBURSEMENT_NOTES is null and SUBT.REIMBURSEMENT_NOTES is not null)
628 or (SUBB.REIMBURSEMENT_NOTES is not null and SUBT.REIMBURSEMENT_NOTES is null)
629 ));
630
631 insert into AHL_WARRANTY_CLAIMS_TL (
632 WARRANTY_CLAIM_ID,
633 LAST_UPDATE_DATE,
634 LAST_UPDATED_BY,
635 CREATION_DATE,
636 CREATED_BY,
637 LAST_UPDATE_LOGIN,
638 NOTES,
639 REIMBURSEMENT_NOTES,
640 LANGUAGE,
641 SOURCE_LANG
642 ) select /*+ ORDERED */
643 B.WARRANTY_CLAIM_ID,
644 B.LAST_UPDATE_DATE,
645 B.LAST_UPDATED_BY,
646 B.CREATION_DATE,
647 B.CREATED_BY,
648 B.LAST_UPDATE_LOGIN,
649 B.NOTES,
650 B.REIMBURSEMENT_NOTES,
651 L.LANGUAGE_CODE,
652 B.SOURCE_LANG
653 from AHL_WARRANTY_CLAIMS_TL B, FND_LANGUAGES L
654 where L.INSTALLED_FLAG in ('I', 'B')
655 and B.LANGUAGE = userenv('LANG')
656 and not exists
657 (select NULL
658 from AHL_WARRANTY_CLAIMS_TL T
659 where T.WARRANTY_CLAIM_ID = B.WARRANTY_CLAIM_ID
660 and T.LANGUAGE = L.LANGUAGE_CODE);
661 end ADD_LANGUAGE;
662
663 end AHL_WARRANTY_CLAIMS_PKG;
[Home] [Help]