[Home] [Help]
PACKAGE BODY: APPS.AHL_WARRANTY_CONTRACTS_PVT
Source
1 PACKAGE BODY AHL_WARRANTY_CONTRACTS_PVT AS
2 /* $Header: AHLVSWCB.pls 120.10.12020000.3 2013/05/29 04:27:18 sareepar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_WARRANTY_CONTRACTS_PVT';
5
6 ------------------------------------
7 -- Common constants and variables --
8 ------------------------------------
9 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
10 l_log_statement NUMBER := fnd_log.level_statement;
11 l_log_procedure NUMBER := fnd_log.level_procedure;
12 l_log_error NUMBER := fnd_log.level_error;
13 l_log_unexpected NUMBER := fnd_log.level_unexpected;
14
15 ------------------------------------------------------------------------------------
16
17 ------------------------------------------------------------------------------------
18 -- Start of Comments
19 -- Procedure name : Auto_Create_Warranty_Contracts
20 -- Type : Public
21 -- Function : Procedure to auto create warranty contracts. Will be called by CP :
22 -- Pre-reqs :
23 -- Parameters :
24 --
25 -- Auto_Create_Warranty_Contracts Parameters:
26 -- p_api_version IN NUMBER Required
27 -- End of Comments
28
29 PROCEDURE Auto_Create_Warranty_Contracts (
30 errbuf OUT NOCOPY VARCHAR2,
31 retcode OUT NOCOPY NUMBER,
32 p_api_version IN NUMBER )
33 IS
34
35 l_api_version CONSTANT NUMBER := 1.0;
36 l_api_name VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.Auto_Create_Warranty_Contracts';
37 l_return_status VARCHAR2(1);
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(2000);
40 l_err_msg VARCHAR2(2000);
41 l_msg_index_out NUMBER;
42 l_warranty_contract_id NUMBER;
43
44 l_warranty_cont_id_tbl AHL_WARRANTY_UTILS_PVT.warranty_contract_id_tbl_type;
45 l_index NUMBER := 1;
46
47 -- cursor to fetch all the IB instances for which:
48 -- 1) there's a valid template assigned with 'Auto Assign' marked
49 -- 2) there's no existing contract with that valid template
50 CURSOR auto_assign_item_instances_csr
51 IS
52 SELECT ASSOC.WARRANTY_TEMPLATE_ID,
53 INSTANCE.INSTANCE_ID
54 FROM AHL_WARRANTY_TMPL_ITEMS ASSOC,
55 CSI_ITEM_INSTANCES INSTANCE
56 WHERE ASSOC.INVENTORY_ITEM_ID = INSTANCE.INVENTORY_ITEM_ID
57 AND ASSOC.INVENTORY_ORG_ID = INSTANCE.INV_MASTER_ORGANIZATION_ID
58 -- avikukum :: changes for bug# 11664006 :: START
59 AND TRUNC(NVL(INSTANCE.ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
60 AND TRUNC(NVL(INSTANCE.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
61 -- avikukum :: changes for bug# 11664006 :: END
62 AND ASSOC.AUTO_ASSIGN_FLAG = 'Y'
63 AND AHL_WARRANTY_CONTRACTS_PVT.Can_Contract_Be_Created -- utility function defined in this package
64 (
65 ASSOC.WARRANTY_TEMPLATE_ID,
66 ASSOC.INVENTORY_ITEM_ID,
67 ASSOC.INVENTORY_ORG_ID
68 ) = 'Y'
69 AND NOT EXISTS
70 (
71 SELECT 'X'
72 FROM AHL_WARRANTY_CONTRACTS_B
73 WHERE WARRANTY_TEMPLATE_ID = ASSOC.WARRANTY_TEMPLATE_ID
74 AND ITEM_INSTANCE_ID = INSTANCE.INSTANCE_ID
75 )
76 -- avikukum :: changes for bug# 11664006 :: START
77 AND EXISTS
78 (
79 SELECT 'X'
80 FROM AHL_UNIT_CONFIG_HEADERS UCH
81 WHERE UCH.NAME = AHL_UTIL_UC_PKG.GET_UNIT_NAME(INSTANCE.INSTANCE_ID)
82 AND UCH.UNIT_CONFIG_STATUS_CODE IN ('INCOMPLETE', 'COMPLETE')
83 UNION ALL
84 SELECT 'X'
85 FROM dual
86 WHERE AHL_UTIL_UC_PKG.GET_UNIT_NAME(INSTANCE.INSTANCE_ID) IS NULL);
87 -- avikukum :: changes for bug# 11664006 :: END;
88
89 auto_create_details_rec auto_assign_item_instances_csr%ROWTYPE;
90
91 BEGIN
92
93 -- Standard start of API savepoint
94 SAVEPOINT Auto_Create_Warranty_Contracts;
95
96 -- initialize return status to success
97 retcode := 0;
98
99 -- Initialize error message stack by default
100 FND_MSG_PUB.Initialize;
101
102 -- Standard call to check for call compatibility
103 FND_FILE.put_line(FND_FILE.LOG, 'p_api_version -> '|| p_api_version);
104 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
105 FND_FILE.put_line(FND_FILE.LOG, 'l_api_version -> '|| l_api_version);
106 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
107 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
108 retcode := 2;
109 errbuf := FND_MSG_PUB.Get;
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 --------------------Start of API Body-----------------------------------
114
115 -- fetch all the applicable instances and call the create API
116 OPEN auto_assign_item_instances_csr;
117 LOOP
118 FETCH auto_assign_item_instances_csr INTO auto_create_details_rec ;
119 EXIT WHEN auto_assign_item_instances_csr%NOTFOUND;
120
121 FND_FILE.put_line(FND_FILE.LOG, 'before calling AHL_SW_CONTRACTS_PVT.Create_Contract');
122 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
123 FND_FILE.put_line(FND_FILE.LOG, 'p_warranty_template_id -> '|| auto_create_details_rec.WARRANTY_TEMPLATE_ID );
124 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
125 FND_FILE.put_line(FND_FILE.LOG, 'p_item_instance_id -> '|| auto_create_details_rec.INSTANCE_ID);
126 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
127
128 -- NOTE: p_init_msg_list, p_commit and p_validation_level will take their default values, and
129 -- commit will take place only after all the contracts have been created
130 AHL_WARRANTY_CONTRACTS_PVT.Create_Contract (
131 p_warranty_template_id => auto_create_details_rec.WARRANTY_TEMPLATE_ID,
132 p_item_instance_id => auto_create_details_rec.INSTANCE_ID,
133 p_api_version => l_api_version,
134 x_warranty_contract_id => l_warranty_contract_id,
135 x_return_status => l_return_status,
136 x_msg_count => l_msg_count,
137 x_msg_data => l_msg_data);
138
139 l_warranty_cont_id_tbl(l_index) := l_warranty_contract_id;
140 l_index := l_index + 1;
141
142 FND_FILE.put_line(FND_FILE.LOG, 'l_index -> '||(l_index-1)||', x_warranty_contract_id -> '|| l_warranty_contract_id);
143 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
144
145 l_msg_count := FND_MSG_PUB.Count_Msg;
146 IF (l_msg_count > 0) THEN
147 CLOSE auto_assign_item_instances_csr;
148 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
149 RAISE FND_API.G_EXC_ERROR;
150 ELSE
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 END IF;
153 END IF;
154
155 FND_FILE.put_line(FND_FILE.LOG, 'after calling AHL_SW_CONTRACTS_PVT.Create_Contract');
156 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
157 END LOOP;
158 CLOSE auto_assign_item_instances_csr;
159
160 IF (l_warranty_cont_id_tbl.COUNT > 0) THEN
161 -- call AHL_WARRANTY_UTILS_PVT.Calc_Contract_Exp_Date API to calculate the expiration date
162 -- of the created contracts, including all their counters
163 FND_FILE.put_line(FND_FILE.LOG, 'before calling AHL_WARRANTY_UTILS_PVT.Calc_Contract_Exp_Date');
164 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
165 FND_FILE.put_line(FND_FILE.LOG, 'l_warranty_cont_id_tbl.COUNT -> '||l_warranty_cont_id_tbl.COUNT);
166 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
167
168 AHL_WARRANTY_UTILS_PVT.Calc_Contract_Exp_Date(
169 p_api_version => l_api_version,
170 p_warranty_contract_id_tbl => l_warranty_cont_id_tbl,
171 x_return_status => l_return_status,
172 x_msg_count => l_msg_count,
173 x_msg_data => l_msg_data);
174
175 l_msg_count := FND_MSG_PUB.Count_Msg;
176 IF (l_msg_count > 0) THEN
177 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
178 RAISE FND_API.G_EXC_ERROR;
179 ELSE
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182 END IF;
183
184 FND_FILE.put_line(FND_FILE.LOG, 'after calling AHL_WARRANTY_UTILS_PVT.Calc_Contract_Exp_Date');
185 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
186 END IF;
187
188 -- set the output
189 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (l_index-1) || ' warranty contracts auto-created successfully.');
190
191 -- commit the work
192 COMMIT;
193
194 ---------------------------End of Body-------------------------------------
195
196 EXCEPTION
197 WHEN FND_API.G_EXC_ERROR THEN
198 ROLLBACK TO Auto_Create_Warranty_Contracts;
199 l_msg_count := FND_MSG_PUB.Count_Msg;
200 retcode := 2;
201
202 -- set the output
203 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract auto-creation process failed. Please refer the program logs for details.');
204
205 FND_FILE.put_line(FND_FILE.LOG, 'Following expected error(s) occured while creating warranty contract records..');
206 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
207 FOR i IN 1..l_msg_count
208 LOOP
209 FND_MSG_PUB.get(
210 p_msg_index => i,
211 p_encoded => FND_API.G_FALSE,
212 p_data => l_err_msg,
213 p_msg_index_out => l_msg_index_out);
214
215 FND_FILE.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
216 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
217 END LOOP;
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 ROLLBACK TO Auto_Create_Warranty_Contracts;
221 l_msg_count := FND_MSG_PUB.Count_Msg;
222 retcode := 2;
223
224 -- set the output
225 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract auto-creation process failed. Please refer the program logs for details.');
226
227 FND_FILE.put_line(FND_FILE.LOG, 'Following unexpected error(s) occured while creating warranty contract records..');
228 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
229 FOR i IN 1..l_msg_count
230 LOOP
231 FND_MSG_PUB.get(
232 p_msg_index => i,
233 p_encoded => FND_API.G_FALSE,
234 p_data => l_err_msg,
235 p_msg_index_out => l_msg_index_out);
236
237 FND_FILE.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
238 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
239 END LOOP;
240
241 WHEN OTHERS THEN
242 ROLLBACK TO Auto_Create_Warranty_Contracts;
243 retcode := 2;
244 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
245 FND_MSG_PUB.add_exc_msg(
246 p_pkg_name => G_PKG_NAME,
247 p_procedure_name => L_API_NAME,
248 p_error_text => SUBSTR(SQLERRM,1,500));
249 END IF;
250
251 l_msg_count := Fnd_Msg_Pub.count_msg;
252
253 -- set the output
254 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract auto-creation process failed. Please refer the program logs for details.');
255
256 FND_FILE.put_line(FND_FILE.LOG, 'Following error occured while creating warranty contract records..');
257 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
258 FOR i IN 1..l_msg_count
259 LOOP
260 fnd_msg_pub.get(
261 p_msg_index => i,
262 p_encoded => FND_API.G_FALSE,
263 p_data => l_err_msg,
264 p_msg_index_out => l_msg_index_out);
265
266 FND_FILE.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
267 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
268 END LOOP;
269
270 END Auto_Create_Warranty_Contracts;
271
272 ------------------------------------------------------------------------------------
273
274 ------------------------------------------------------------------------------------
275 -- Start of Comments
276 -- Procedure name : Create_Contract
277 -- Type : Public
278 -- Function : Procedure to create warranty contracts based on an exisiting warranty template.
279 -- Pre-reqs :
280 -- Parameters :
281 --
282 -- Create_Contract Parameters:
283 -- p_api_version IN NUMBER Required
284 -- p_warranty_template_id IN NUMBER Required
285 -- p_item_instance_id IN NUMBER Required
286 --
287 -- End of Comments
288
289
290 PROCEDURE Create_Contract (
291 p_api_version IN NUMBER,
292 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
293 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
294 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
295 p_module_type IN VARCHAR2 := NULL,
296 p_warranty_template_id IN NUMBER,
297 p_item_instance_id IN NUMBER,
298 x_warranty_contract_id OUT NOCOPY NUMBER,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2 )
302 IS
303
304 L_API_VERSION CONSTANT NUMBER := 1;
305 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Contract';
306 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
307 l_warranty_contract_id NUMBER;
308 l_dummy VARCHAR2(1);
309 l_warranty_cont_ctr_id NUMBER;
310 l_active_end_date DATE;
311
312 -- cursor to validate the given instance id
313 CURSOR validate_instance_csr (c_item_instance_id NUMBER) IS
314 SELECT 'X'
315 FROM CSI_ITEM_INSTANCES
316 WHERE INSTANCE_ID = c_item_instance_id;
317
318 -- cursor to fetch the details of the given template id
319 CURSOR warranty_template_csr (c_warranty_template_id NUMBER, c_item_instance_id NUMBER ) IS
320 SELECT
321 AWTB.WARRANTY_TYPE,
322 AWTB.WARRANTY_VENDOR_ID,
323 AWTB.CONTRACT_START_DATE,
324 AWTB.CONTRACT_END_DATE,
325 AWTB.WARRANTY_PERIOD,
326 AWTB.PERIOD_UOM_CODE,
327 AWTB.CONTRACT_EXPIRY_TYPE,
328 AWTB.CLAIM_LABOUR_HOURS,
329 ASSOC.OSP_CLAIM_FLAG
330 FROM AHL_WARRANTY_TEMPLATES_B AWTB,
331 AHL_WARRANTY_TMPL_ITEMS ASSOC,
332 CSI_ITEM_INSTANCES INSTANCE
333 WHERE AWTB.WARRANTY_TEMPLATE_ID = c_warranty_template_id
334 AND INSTANCE.INSTANCE_ID = c_item_instance_id
335 AND ASSOC.WARRANTY_TEMPLATE_ID = AWTB.WARRANTY_TEMPLATE_ID
336 AND ASSOC.INVENTORY_ITEM_ID = INSTANCE.INVENTORY_ITEM_ID
337 AND ASSOC.INVENTORY_ORG_ID = INSTANCE.INV_MASTER_ORGANIZATION_ID;
338
339
340 warranty_template_rec warranty_template_csr%ROWTYPE;
341
342 -- cursor to fetch the details of the counter instances which belong to the counter templates
343 -- that are associated with a given item and a given warranty template id
344 CURSOR warranty_contract_counter_csr (c_warranty_template_id NUMBER, c_item_instance_id NUMBER) IS
345 SELECT counter.counter_id,
346 tmpl_ctr.warranty_tmpl_counter_id,
347 tmpl_ctr.threshold
348 FROM ahl_warranty_tmpl_ctr_b tmpl_ctr,
349 csi_counters_vl counter,
350 CSI_COUNTER_ASSOCIATIONS CCA
351 WHERE TRUNC(NVL(counter.start_date_active, sysdate)) <= TRUNC(sysdate)
352 AND TRUNC(NVL(counter.end_date_active, sysdate+1)) > TRUNC(sysdate)
353 AND counter.COUNTER_ID = CCA.COUNTER_ID
354 AND CCA.SOURCE_OBJECT_CODE = 'CP'
355 AND counter.created_from_counter_tmpl_id = tmpl_ctr.counter_id
356 AND tmpl_ctr.WARRANTY_TEMPLATE_ID = c_warranty_template_id
357 AND CCA.SOURCE_OBJECT_ID = c_item_instance_id;
358
359 warranty_contract_counter_rec warranty_contract_counter_csr%ROWTYPE;
360 BEGIN
361
362 -- Standard start of API savepoint
363 SAVEPOINT Create_Contract;
364
365 IF (l_log_procedure >= l_log_current_level) THEN
366 FND_LOG.string(l_log_procedure, L_DEBUG_KEY || '.begin',
367 'At the start of the PLSQL procedure Create_Contract. Warranty Template ID = ' || p_warranty_template_id ||
368 'Item Instance ID' || p_item_instance_id);
369 END IF;
370
371 -- Initialize message list if p_init_msg_list is set to TRUE
372 IF FND_API.To_Boolean(p_init_msg_list) THEN
373 FND_MSG_PUB.Initialize;
374 END IF;
375
376 -- Initialize API return status to success
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 -- Standard call to check for call compatibility.
380 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
381 p_api_version,
382 l_api_name,G_PKG_NAME) THEN
383 IF (l_log_statement >= l_log_current_level) THEN
384 fnd_log.string(l_log_statement,L_DEBUG_KEY||'UNEXPECTED ERROR',
385 'l_api_version :' || l_api_version || 'p_api_version :' || p_api_version );
386 END IF;
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END IF;
389
390 --------------------Start of API Body-----------------------------------
391
392 -- validate the instance id
393 OPEN validate_instance_csr (p_item_instance_id);
394 FETCH validate_instance_csr INTO l_dummy;
395 IF validate_instance_csr%NOTFOUND THEN
396 CLOSE validate_instance_csr;
397 Fnd_Message.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE' ); -- The item instance INSTANCE is invalid.
398 Fnd_Message.Set_Token('INSTANCE', p_item_instance_id);
399 Fnd_Msg_Pub.ADD;
400
401 IF (l_log_statement >= l_log_current_level) THEN
402 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Item Instance ID :' || p_item_instance_id || 'is invalid' );
403 END IF;
404
405 RAISE Fnd_Api.G_EXC_ERROR;
406 END IF;
407 CLOSE validate_instance_csr;
408
409 -- fetch the template details
410 OPEN warranty_template_csr(p_warranty_template_id, p_item_instance_id );
411 FETCH warranty_template_csr INTO warranty_template_rec;
412 IF warranty_template_csr%NOTFOUND THEN
413 CLOSE warranty_template_csr;
414 Fnd_Message.SET_NAME('AHL','AHL_WARRANTY_INV_TEMP_ID' ); -- Invalid Warranty Template.
415 Fnd_Msg_Pub.ADD;
416
417 IF (l_log_statement >= l_log_current_level) THEN
418 fnd_log.string(l_log_statement,L_DEBUG_KEY,'warranty template: ' || p_warranty_template_id || 'is invalid' );
419 END IF;
420
421 RAISE Fnd_Api.G_EXC_ERROR;
422 END IF;
423 CLOSE warranty_template_csr;
424
425 -- store the contract's end date for populating in the counter's table, before expiration calculation API is called
426 l_active_end_date := warranty_template_rec.CONTRACT_END_DATE;
427
428 -- fetch the next value from the sequence
429 l_warranty_contract_id := AHL_WARRANTY_CONTRACTS_B_S.NEXTVAL;
430
431 -- insert a record in the AHL_WARRANTY_CONTRACTS_B table
432 INSERT INTO AHL_WARRANTY_CONTRACTS_B
433 (
434 WARRANTY_CONTRACT_ID,
435 OBJECT_VERSION_NUMBER,
436 CONTRACT_NUMBER,
437 CONTRACT_STATUS_CODE,
438 ITEM_INSTANCE_ID,
439 WARRANTY_TEMPLATE_ID,
440 EXPIRATION_DATE,
441 OSP_CLAIM_FLAG,
442 WARRANTY_TYPE,
443 WARRANTY_VENDOR_ID,
444 ACTIVE_START_DATE,
445 ACTIVE_END_DATE,
446 WARRANTY_PERIOD,
447 PERIOD_UOM_CODE,
448 CONTRACT_EXPIRY_TYPE,
449 CLAIM_LABOUR_HOURS,
450 LAST_UPDATE_DATE,
451 LAST_UPDATED_BY,
452 CREATION_DATE,
453 CREATED_BY,
454 LAST_UPDATE_LOGIN,
455 ATTRIBUTE_CATEGORY,
456 ATTRIBUTE1,
457 ATTRIBUTE2,
458 ATTRIBUTE3,
459 ATTRIBUTE4,
460 ATTRIBUTE5,
461 ATTRIBUTE6,
462 ATTRIBUTE7,
463 ATTRIBUTE8,
464 ATTRIBUTE9,
465 ATTRIBUTE10,
466 ATTRIBUTE11,
467 ATTRIBUTE12,
468 ATTRIBUTE13,
469 ATTRIBUTE14,
470 ATTRIBUTE15
471 )
472 values
473 (
474 l_warranty_contract_id,
475 1,
476 l_warranty_contract_id,
477 'PENDING',
478 p_item_instance_id,
479 p_warranty_template_id,
480 NULL,
481 warranty_template_rec.OSP_CLAIM_FLAG,
482 warranty_template_rec.WARRANTY_TYPE,
483 warranty_template_rec.WARRANTY_VENDOR_ID,
484 NVL(warranty_template_rec.CONTRACT_START_DATE, SYSDATE),
485 warranty_template_rec.CONTRACT_END_DATE,
486 warranty_template_rec.WARRANTY_PERIOD,
487 warranty_template_rec.PERIOD_UOM_CODE,
488 warranty_template_rec.CONTRACT_EXPIRY_TYPE,
489 warranty_template_rec.CLAIM_LABOUR_HOURS,
490 SYSDATE,
491 Fnd_Global.USER_ID,
492 SYSDATE,
493 Fnd_Global.USER_ID,
494 Fnd_Global.LOGIN_ID,
495 NULL,
496 NULL,
497 NULL,
498 NULL,
499 NULL,
500 NULL,
501 NULL,
502 NULL,
503 NULL,
504 NULL,
505 NULL,
506 NULL,
507 NULL,
508 NULL,
509 NULL,
510 NULL
511 );
512
513 -- insert a record in the AHL_WARRANTY_CONTRACTS_TL table
514 INSERT INTO AHL_WARRANTY_CONTRACTS_TL
515 (
516 WARRANTY_CONTRACT_ID,
517 LANGUAGE,
518 SOURCE_LANG,
519 LAST_UPDATE_DATE,
520 LAST_UPDATED_BY,
521 CREATION_DATE,
522 CREATED_BY,
523 LAST_UPDATE_LOGIN,
524 DESCRIPTION,
525 TERMS_AND_CONDITIONS,
526 REACTION_AND_RESOL_TIME,
527 SERVICE_LEVEL_AGREEMENT
528 )
529 SELECT l_warranty_contract_id,
530 L.LANGUAGE_CODE,
531 userenv('LANG'),
532 SYSDATE,
533 Fnd_Global.USER_ID,
534 SYSDATE,
535 Fnd_Global.USER_ID,
536 Fnd_Global.LOGIN_ID,
537 AWTL.DESCRIPTION,
538 AWTL.TERMS_AND_CONDITIONS,
539 AWTL.REACTION_AND_RESOL_TIME,
540 AWTL.SERVICE_LEVEL_AGREEMENT
541 FROM AHL_WARRANTY_TEMPLATES_TL AWTL,
542 FND_LANGUAGES L
543 WHERE AWTL.WARRANTY_TEMPLATE_ID (+) = P_WARRANTY_TEMPLATE_ID
544 AND AWTL.LANGUAGE (+) = L.LANGUAGE_CODE
545 AND L.INSTALLED_FLAG IN ('I', 'B')
546 AND NOT EXISTS
547 (SELECT 'X'
548 FROM AHL_WARRANTY_CONTRACTS_TL T
549 WHERE T.WARRANTY_CONTRACT_ID = l_warranty_contract_id
550 AND T.LANGUAGE = L.LANGUAGE_CODE);
551
552 -- populate the OUT parameter
553 x_warranty_contract_id := l_warranty_contract_id;
554
555 -- insert the matching counter instances
556 OPEN warranty_contract_counter_csr(p_warranty_template_id, p_item_instance_id);
557 LOOP
558 FETCH warranty_contract_counter_csr INTO warranty_contract_counter_rec ;
559 EXIT WHEN warranty_contract_counter_csr%NOTFOUND;
560 l_warranty_cont_ctr_id := AHL_WARRANTY_CONT_CTR_B_S.NEXTVAL;
561
562 INSERT INTO AHL_WARRANTY_CONT_CTR_B
563 (
564 WARRANTY_CNTRT_COUNTER_ID,
565 OBJECT_VERSION_NUMBER,
566 LAST_UPDATE_DATE,
567 LAST_UPDATED_BY,
568 CREATION_DATE,
569 CREATED_BY,
570 LAST_UPDATE_LOGIN,
571 WARRANTY_CONTRACT_ID,
572 COUNTER_ID,
573 THRESHOLD,
574 START_VALUE,
575 EXPIRATION_DATE,
576 ATTRIBUTE_CATEGORY,
577 ATTRIBUTE1,
578 ATTRIBUTE2,
579 ATTRIBUTE3,
580 ATTRIBUTE4,
581 ATTRIBUTE5,
582 ATTRIBUTE6,
583 ATTRIBUTE7,
584 ATTRIBUTE8,
585 ATTRIBUTE9,
586 ATTRIBUTE10,
587 ATTRIBUTE11,
588 ATTRIBUTE12,
589 ATTRIBUTE13,
590 ATTRIBUTE14,
591 ATTRIBUTE15
592 )
593 values
594 (
595 l_warranty_cont_ctr_id,
596 1,
597 SYSDATE,
598 Fnd_Global.USER_ID,
599 SYSDATE,
600 Fnd_Global.USER_ID,
601 Fnd_Global.LOGIN_ID,
602 l_warranty_contract_id,
603 warranty_contract_counter_rec.counter_id,
604 warranty_contract_counter_rec.threshold,
605 0,
606 l_active_end_date,
607 NULL,
608 NULL,
609 NULL,
610 NULL,
611 NULL,
612 NULL,
613 NULL,
614 NULL,
615 NULL,
616 NULL,
617 NULL,
618 NULL,
619 NULL,
620 NULL,
621 NULL,
622 NULL
623 );
624
625 INSERT INTO AHL_WARRANTY_CONT_CTR_TL
626 (
627 WARRANTY_CNTRT_COUNTER_ID,
628 LANGUAGE,
629 SOURCE_LANG,
630 TERMS_AND_CONDITIONS,
631 REACTION_AND_RESOL_TIME,
632 SERVICE_LEVEL_AGREEMENT,
633 REMARKS,
634 LAST_UPDATE_DATE,
635 LAST_UPDATED_BY,
636 CREATION_DATE,
637 CREATED_BY,
638 LAST_UPDATE_LOGIN
639 )
640 SELECT l_warranty_cont_ctr_id,
641 L.LANGUAGE_CODE,
642 userenv('LANG'),
643 AWTCL.terms_and_conditions,
644 AWTCL.reaction_and_resol_time,
645 AWTCL.service_level_agreement,
646 AWTCL.remarks,
647 SYSDATE,
648 Fnd_Global.USER_ID,
649 SYSDATE,
650 Fnd_Global.USER_ID,
651 Fnd_Global.LOGIN_ID
652 FROM AHL_WARRANTY_TMPL_CTR_TL AWTCL,
653 FND_LANGUAGES L
654 WHERE AWTCL.WARRANTY_TMPL_COUNTER_ID (+) = warranty_contract_counter_rec.warranty_tmpl_counter_id
655 AND AWTCL.LANGUAGE (+) = L.LANGUAGE_CODE
656 AND L.INSTALLED_FLAG IN ('I', 'B')
657 AND NOT EXISTS
658 (SELECT 'X'
659 FROM AHL_WARRANTY_CONT_CTR_TL T
660 WHERE T.WARRANTY_CNTRT_COUNTER_ID = l_warranty_cont_ctr_id
661 AND T.LANGUAGE = L.LANGUAGE_CODE);
662
663 END LOOP;
664 CLOSE warranty_contract_counter_csr;
665 ---------------------------End of Body-------------------------------------
666 --
667 -- END of API body.
668 --
669 -- Standard check of p_commit.
670 IF Fnd_Api.To_Boolean ( p_commit ) THEN
671 COMMIT WORK;
672 END IF;
673
674 IF (l_log_procedure >= l_log_current_level)THEN
675 fnd_log.string(l_log_procedure,L_DEBUG_KEY ||'.end',
676 'At the end of PLSQL procedure Create_Contract, x_return_status=' || x_return_status);
677 END IF;
678
679 EXCEPTION
680 WHEN FND_API.G_EXC_ERROR THEN
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 ROLLBACK TO Create_Contract;
683 FND_MSG_PUB.count_and_get(
684 p_count => x_msg_count,
685 p_data => x_msg_data,
686 p_encoded => fnd_api.g_false);
687
688 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 ROLLBACK TO Create_Contract;
691 FND_MSG_PUB.count_and_get(
692 p_count => x_msg_count,
693 p_data => x_msg_data,
694 p_encoded => fnd_api.g_false);
695
696 WHEN OTHERS THEN
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698 ROLLBACK TO Create_Contract;
699 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
700 fnd_msg_pub.add_exc_msg(
701 p_pkg_name => G_PKG_NAME,
702 p_procedure_name => L_API_NAME,
703 p_error_text => SUBSTR(SQLERRM,1,500));
704 END IF;
705 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
706 p_data => x_msg_data,
707 p_encoded => fnd_api.g_false);
708
709 END Create_Contract;
710
711
712 ------------------------------------------------------------------------------------
713 -- Start of Comments
714 -- Function name : Can_Contract_Be_Created
715 -- Type : Public
716 -- Function : Function to check if a given template and item combination is valid
717 -- for contract creation or not. Returns 'Y' or 'N'.
718 -- Pre-reqs :
719 -- Parameters :
720 --
721 -- Can_Contract_Be_Created Parameters:
722 -- p_warranty_tmpl_id IN NUMBER Required : Warranty template id
723 -- p_warranty_item_id IN NUMBER Required : Warranty item id, associated to the template
724 -- p_warranty_item_org_id IN NUMBER Required : Warranty item org id (master org id)
725 --
726 -- End of Comments
727
728 FUNCTION Can_Contract_Be_Created (
729 p_warranty_tmpl_id IN NUMBER,
730 p_warranty_item_id IN NUMBER,
731 p_warranty_item_org_id IN NUMBER
732 ) RETURN VARCHAR2 IS
733
734 -- cursor to check template's end date validity
735 -- SATHAPLI::Bug 11655758, 27-Jan-2011, consider the template's default contract end date too
736 CURSOR chk_tmpl_end_date_csr (c_warranty_tmpl_id NUMBER) IS
737 SELECT 'X'
738 FROM AHL_WARRANTY_TEMPLATES_B
739 WHERE warranty_template_id = c_warranty_tmpl_id
740 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
741 AND TRUNC(NVL(contract_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
742
743 -- cursor to check template's enabled flag
744 CURSOR chk_tmpl_flag_csr (c_warranty_tmpl_id NUMBER) IS
745 SELECT 'X'
746 FROM AHL_WARRANTY_TEMPLATES_B
747 WHERE warranty_template_id = c_warranty_tmpl_id
748 AND enabled_flag = 'Y';
749
750 -- cursor to check template item's enabled flag
751 CURSOR chk_tmpl_item_flag_csr (c_warranty_tmpl_id NUMBER, c_warranty_item_id NUMBER,
752 c_item_org_id NUMBER) IS
753 SELECT 'X'
754 FROM AHL_WARRANTY_TMPL_ITEMS
755 WHERE warranty_template_id = c_warranty_tmpl_id
756 AND inventory_item_id = c_warranty_item_id
757 AND inventory_org_id = c_item_org_id
758 AND enabled_flag = 'Y';
759
760 --
761 l_tmpl_valid BOOLEAN DEFAULT FALSE;
762 l_item_valid BOOLEAN DEFAULT FALSE;
763 l_ret_val VARCHAR2(1) := 'N';
764 l_dummy VARCHAR2(1);
765 --
766
767 BEGIN
768 -- check for template's end date
769 OPEN chk_tmpl_end_date_csr (p_warranty_tmpl_id);
770 FETCH chk_tmpl_end_date_csr INTO l_dummy;
771 IF (chk_tmpl_end_date_csr%FOUND) THEN
772 l_tmpl_valid := TRUE; -- set the flag to true
773 END IF;
774 CLOSE chk_tmpl_end_date_csr;
775
776 -- check for template's enabled flag only if the above is true
777 IF (l_tmpl_valid) THEN
778 OPEN chk_tmpl_flag_csr (p_warranty_tmpl_id);
779 FETCH chk_tmpl_flag_csr INTO l_dummy;
780 IF (chk_tmpl_flag_csr%NOTFOUND) THEN
781 l_tmpl_valid := FALSE; -- set the flag to false
782 END IF;
783 CLOSE chk_tmpl_flag_csr;
784 END IF;
785
786 -- check for template item's enabled flag if the above is still true
787 IF (l_tmpl_valid) THEN
788 OPEN chk_tmpl_item_flag_csr (p_warranty_tmpl_id, p_warranty_item_id,
789 p_warranty_item_org_id);
790 FETCH chk_tmpl_item_flag_csr INTO l_dummy;
791 IF (chk_tmpl_item_flag_csr%FOUND) THEN
792 l_item_valid := TRUE; -- set the flag to true
793 END IF;
794 CLOSE chk_tmpl_item_flag_csr;
795 END IF;
796
797 -- return 'Y' if both the flags are true, otherwise return 'N'
798 IF (l_tmpl_valid AND l_item_valid) THEN
799 l_ret_val := 'Y';
800 END IF;
801 RETURN l_ret_val;
802
803 END Can_Contract_Be_Created;
804
805 ------------------------------------------------------------------------------------
806 -- Start of Comments
807 -- Function name : Is_Instance_Warranty_Available
808 -- Type : Public
809 -- Function : Function to check if a given instance has at least one 'ACTIVE'
810 -- contract available or not. Returns 'Y' or 'N'.
811 -- Pre-reqs :
812 -- Parameters :
813 --
814 -- Is_Instance_Warranty_Available Parameters:
815 -- p_warranty_instance_id IN NUMBER Required : Warranty instance id
816 --
817 -- End of Comments
818
819 FUNCTION Is_Instance_Warranty_Available (
820 p_warranty_instance_id IN NUMBER
821 ) RETURN VARCHAR2 IS
822
823 -- cursor to check if there is an 'ACTIVE' contract for the instance
824 CURSOR chk_active_cont_csr (c_instance_id NUMBER) IS
825 SELECT 'X'
826 FROM AHL_WARRANTY_CONTRACTS_B
827 WHERE item_instance_id = c_instance_id
828 AND contract_status_code = 'ACTIVE';
829
830 --
831 l_ret_val VARCHAR2(1) := 'N';
832 l_dummy VARCHAR2(1);
833 --
834
835 BEGIN
836 -- check for any active contract
837 OPEN chk_active_cont_csr (p_warranty_instance_id);
838 FETCH chk_active_cont_csr INTO l_dummy;
839 IF (chk_active_cont_csr%FOUND) THEN
840 l_ret_val := 'Y'; -- set the return value to 'Y'
841 END IF;
842 CLOSE chk_active_cont_csr;
843
844 RETURN l_ret_val;
845
846 END Is_Instance_Warranty_Available;
847
848
849 ------------------------------------------------------------------------------------
850 -- Start of Comments
851 -- Procedure name : ADD_LANGUAGE
852 -- Type : Public
853 -- Function : Procedure created using utility:$FND_TOP/bin/AFTBLGEN
854 -- To add language dependent rows to tables.
855 -- This procedure adds rows in below two tables
856 -- AHL_WARRANTY_CONTRACTS_TL
857 -- AHL_WARRANTY_CONT_CTR_TL
858 -- Pre-reqs :
859 -- Parameters :
860 --
861 -- End of Comments
862
863 PROCEDURE ADD_LANGUAGE
864 is
865 begin
866 -- for table AHL_WARRANTY_CONTRACTS_TL
867 delete from AHL_WARRANTY_CONTRACTS_TL T
868 where not exists
869 (select NULL
870 from AHL_WARRANTY_CONTRACTS_B B
871 where B.WARRANTY_CONTRACT_ID = T.WARRANTY_CONTRACT_ID
872 );
873
874 update AHL_WARRANTY_CONTRACTS_TL T set (
875 REMARKS,
876 DESCRIPTION,
877 TERMS_AND_CONDITIONS,
878 REACTION_AND_RESOL_TIME,
879 SERVICE_LEVEL_AGREEMENT
880 ) = (select
881 B.REMARKS,
882 B.DESCRIPTION,
883 B.TERMS_AND_CONDITIONS,
884 B.REACTION_AND_RESOL_TIME,
885 B.SERVICE_LEVEL_AGREEMENT
886 from AHL_WARRANTY_CONTRACTS_TL B
887 where B.WARRANTY_CONTRACT_ID = T.WARRANTY_CONTRACT_ID
888 and B.LANGUAGE = T.SOURCE_LANG)
889 where (
890 T.WARRANTY_CONTRACT_ID,
891 T.LANGUAGE
892 ) in (select
893 SUBT.WARRANTY_CONTRACT_ID,
894 SUBT.LANGUAGE
895 from AHL_WARRANTY_CONTRACTS_TL SUBB, AHL_WARRANTY_CONTRACTS_TL SUBT
896 where SUBB.WARRANTY_CONTRACT_ID = SUBT.WARRANTY_CONTRACT_ID
897 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
898 and (SUBB.REMARKS <> SUBT.REMARKS
899 or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
900 or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
901 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
902 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
903 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
904 or SUBB.TERMS_AND_CONDITIONS <> SUBT.TERMS_AND_CONDITIONS
905 or (SUBB.TERMS_AND_CONDITIONS is null and SUBT.TERMS_AND_CONDITIONS is not null)
906 or (SUBB.TERMS_AND_CONDITIONS is not null and SUBT.TERMS_AND_CONDITIONS is null)
907 or SUBB.REACTION_AND_RESOL_TIME <> SUBT.REACTION_AND_RESOL_TIME
908 or (SUBB.REACTION_AND_RESOL_TIME is null and SUBT.REACTION_AND_RESOL_TIME is not null)
909 or (SUBB.REACTION_AND_RESOL_TIME is not null and SUBT.REACTION_AND_RESOL_TIME is null)
910 or SUBB.SERVICE_LEVEL_AGREEMENT <> SUBT.SERVICE_LEVEL_AGREEMENT
911 or (SUBB.SERVICE_LEVEL_AGREEMENT is null and SUBT.SERVICE_LEVEL_AGREEMENT is not null)
912 or (SUBB.SERVICE_LEVEL_AGREEMENT is not null and SUBT.SERVICE_LEVEL_AGREEMENT is null)
913 ));
914
915 insert into AHL_WARRANTY_CONTRACTS_TL (
916 WARRANTY_CONTRACT_ID,
917 LAST_UPDATE_DATE,
918 LAST_UPDATED_BY,
919 CREATION_DATE,
920 CREATED_BY,
921 LAST_UPDATE_LOGIN,
922 REMARKS,
923 DESCRIPTION,
924 TERMS_AND_CONDITIONS,
925 REACTION_AND_RESOL_TIME,
926 SERVICE_LEVEL_AGREEMENT,
927 LANGUAGE,
928 SOURCE_LANG
929 ) select /*+ ORDERED */
930 B.WARRANTY_CONTRACT_ID,
931 B.LAST_UPDATE_DATE,
932 B.LAST_UPDATED_BY,
933 B.CREATION_DATE,
934 B.CREATED_BY,
935 B.LAST_UPDATE_LOGIN,
936 B.REMARKS,
937 B.DESCRIPTION,
938 B.TERMS_AND_CONDITIONS,
939 B.REACTION_AND_RESOL_TIME,
940 B.SERVICE_LEVEL_AGREEMENT,
941 L.LANGUAGE_CODE,
942 B.SOURCE_LANG
943 from AHL_WARRANTY_CONTRACTS_TL B, FND_LANGUAGES L
944 where L.INSTALLED_FLAG in ('I', 'B')
945 and B.LANGUAGE = userenv('LANG')
946 and not exists
947 (select NULL
948 from AHL_WARRANTY_CONTRACTS_TL T
949 where T.WARRANTY_CONTRACT_ID = B.WARRANTY_CONTRACT_ID
950 and T.LANGUAGE = L.LANGUAGE_CODE);
951
952 -- for table AHL_WARRANTY_CONT_CTR_TL
953 delete from AHL_WARRANTY_CONT_CTR_TL T
954 where not exists
955 (select NULL
956 from AHL_WARRANTY_CONT_CTR_B B
957 where B.WARRANTY_CNTRT_COUNTER_ID = T.WARRANTY_CNTRT_COUNTER_ID
958 );
959
960 update AHL_WARRANTY_CONT_CTR_TL T set (
961 REMARKS,
962 TERMS_AND_CONDITIONS,
963 REACTION_AND_RESOL_TIME,
964 SERVICE_LEVEL_AGREEMENT
965 ) = (select
966 B.REMARKS,
967 B.TERMS_AND_CONDITIONS,
968 B.REACTION_AND_RESOL_TIME,
969 B.SERVICE_LEVEL_AGREEMENT
970 from AHL_WARRANTY_CONT_CTR_TL B
971 where B.WARRANTY_CNTRT_COUNTER_ID = T.WARRANTY_CNTRT_COUNTER_ID
972 and B.LANGUAGE = T.SOURCE_LANG)
973 where (
974 T.WARRANTY_CNTRT_COUNTER_ID,
975 T.LANGUAGE
976 ) in (select
977 SUBT.WARRANTY_CNTRT_COUNTER_ID,
978 SUBT.LANGUAGE
979 from AHL_WARRANTY_CONT_CTR_TL SUBB, AHL_WARRANTY_CONT_CTR_TL SUBT
980 where SUBB.WARRANTY_CNTRT_COUNTER_ID = SUBT.WARRANTY_CNTRT_COUNTER_ID
981 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
982 and (SUBB.REMARKS <> SUBT.REMARKS
983 or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
984 or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
985 or SUBB.TERMS_AND_CONDITIONS <> SUBT.TERMS_AND_CONDITIONS
986 or (SUBB.TERMS_AND_CONDITIONS is null and SUBT.TERMS_AND_CONDITIONS is not null)
987 or (SUBB.TERMS_AND_CONDITIONS is not null and SUBT.TERMS_AND_CONDITIONS is null)
988 or SUBB.REACTION_AND_RESOL_TIME <> SUBT.REACTION_AND_RESOL_TIME
989 or (SUBB.REACTION_AND_RESOL_TIME is null and SUBT.REACTION_AND_RESOL_TIME is not null)
990 or (SUBB.REACTION_AND_RESOL_TIME is not null and SUBT.REACTION_AND_RESOL_TIME is null)
991 or SUBB.SERVICE_LEVEL_AGREEMENT <> SUBT.SERVICE_LEVEL_AGREEMENT
992 or (SUBB.SERVICE_LEVEL_AGREEMENT is null and SUBT.SERVICE_LEVEL_AGREEMENT is not null)
993 or (SUBB.SERVICE_LEVEL_AGREEMENT is not null and SUBT.SERVICE_LEVEL_AGREEMENT is null)
994 ));
995
996 insert into AHL_WARRANTY_CONT_CTR_TL (
997 WARRANTY_CNTRT_COUNTER_ID,
998 LAST_UPDATE_DATE,
999 LAST_UPDATED_BY,
1000 CREATION_DATE,
1001 CREATED_BY,
1002 LAST_UPDATE_LOGIN,
1003 REMARKS,
1004 TERMS_AND_CONDITIONS,
1005 REACTION_AND_RESOL_TIME,
1006 SERVICE_LEVEL_AGREEMENT,
1007 LANGUAGE,
1008 SOURCE_LANG
1009 ) select /*+ ORDERED */
1010 B.WARRANTY_CNTRT_COUNTER_ID,
1011 B.LAST_UPDATE_DATE,
1012 B.LAST_UPDATED_BY,
1013 B.CREATION_DATE,
1014 B.CREATED_BY,
1015 B.LAST_UPDATE_LOGIN,
1016 B.REMARKS,
1017 B.TERMS_AND_CONDITIONS,
1018 B.REACTION_AND_RESOL_TIME,
1019 B.SERVICE_LEVEL_AGREEMENT,
1020 L.LANGUAGE_CODE,
1021 B.SOURCE_LANG
1022 from AHL_WARRANTY_CONT_CTR_TL B, FND_LANGUAGES L
1023 where L.INSTALLED_FLAG in ('I', 'B')
1024 and B.LANGUAGE = userenv('LANG')
1025 and not exists
1026 (select NULL
1027 from AHL_WARRANTY_CONT_CTR_TL T
1028 where T.WARRANTY_CNTRT_COUNTER_ID = B.WARRANTY_CNTRT_COUNTER_ID
1029 and T.LANGUAGE = L.LANGUAGE_CODE);
1030 END ADD_LANGUAGE;
1031
1032 END AHL_WARRANTY_CONTRACTS_PVT;