1 PACKAGE BODY AHL_PRD_NONROUTINE_PVT AS
2 /* $Header: AHLVPNRB.pls 120.26.12020000.2 2012/12/07 07:59:01 sareepar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_PRD_NONROUTINE_PVT';
5 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
6 -----------------------------------
7 -- Declare Local Procedures --
8 -----------------------------------
9
10 -- Convert value to id
11 PROCEDURE Convert_val_to_id(
12 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
13 x_return_status OUT NOCOPY VARCHAR2
14 );
15
16 -- Default and validate the parameters
17 PROCEDURE Default_and_validate_param(
18 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
19 p_mr_asso_count IN NUMBER,
20 p_module_type IN VARCHAR2,
21 x_return_status OUT NOCOPY VARCHAR2
22 );
23
24 -- Create Service Request
25 PROCEDURE Create_sr(
26 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
27 x_return_status OUT NOCOPY VARCHAR2
28 );
29
30 -- MR NR ER -- start
31 PROCEDURE Process_Mr(
32 p_x_task_tbl IN OUT NOCOPY sr_task_tbl_type,
33 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
34 p_module_type IN VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 );
39
40 -- Update Warranty Entitlement records
41 PROCEDURE Update_warranty_entitlements
42 (
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2,
45 p_commit IN VARCHAR2,
46 p_validation_level IN NUMBER,
47 p_module_type IN VARCHAR2,
48 p_mr_asso_count IN NUMBER,
49 p_x_sr_task_tbl IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2
53 );
54
55 PROCEDURE Copy_Mr_Details(
56 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
57 p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type,
58 p_sr_table_index IN NUMBER
59 );
60 -- MR NR ER -- end
61
62 -- Create VWP Task
63 PROCEDURE Create_task(
64 p_x_task_tbl IN OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_tbl_type,
65 x_return_status OUT NOCOPY VARCHAR2
66 );
67
68 -- Update Service Request
69 PROCEDURE Update_sr(
70 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
71 x_return_status OUT NOCOPY VARCHAR2
72 );
73
74 -- Get Message Token
75 PROCEDURE get_msg_token(
76 p_wo_id IN Number,
77 p_instance_id IN Number,
78 x_wo_name OUT NOCOPY VARCHAR2,
79 x_instance_number OUT NOCOPY VARCHAR2
80 );
81
82 -- Get Note and note detail from Message.
83 Procedure get_note_value(p_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
84 x_note OUT NOCOPY VARCHAR2,
85 x_note_detail OUT NOCOPY VARCHAR2
86 );
87 -- procedure to write the input parameters to log
88 Procedure write_to_log(p_sr_tasK_tbl IN ahl_prd_nonroutine_pvt.sr_task_tbl_type
89 );
90 -- procedure to write the SR API input parameters to log
91 Procedure write_sr_to_log
92 (
93 p_service_request_rec IN CS_SERVICEREQUEST_PUB.service_request_rec_type,
94 p_notes_table IN CS_SERVICEREQUEST_PUB.notes_table,
95 p_contacts_table IN CS_SERVICEREQUEST_PUB.contacts_table
96 );
97
98 -- Define global variables here.
99 -- SR status id for status PLANNED
100 G_SR_PLANNED_STATUS_ID CONSTANT NUMBER := 52;
101
102 -- SR status id for status OPEN
103 G_SR_OPEN_STATUS_ID CONSTANT NUMBER := 1;
104
105 --------------------------------------
106 -- End Local Procedures Declaration --
107 --------------------------------------
108
109 --------------------------------------------------------------------
110 -- Procedure name : Process_nonroutine_job
111 -- Type : Private
112 -- Function : To Create or Update Service request based on
113 -- operation_type and to create vwp task for
114 -- a nonroutine job.
115 -- Parameters :
116 --
117 -- Standard IN Parameters :
118 -- p_api_version IN NUMBER Required
119 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
120 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
121 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
122 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
123 -- p_module_type IN VARCHAR2 Default NULL.
124 --
125 -- Standard OUT Parameters :
126 -- x_return_status OUT VARCHAR2 Required
127 -- x_msg_count OUT NUMBER Required
128 -- x_msg_data OUT VARCHAR2 Required
129 --
130 -- Process_nonroutine_job Parameters:
131 -- p_x_sr_task_tbl IN OUT Sr_task_tbl_type Required
132 -- The table of records for creation / updation of Service
133 -- request and creation of vwp task.
134 --
135 -- Version :
136 -- Initial Version 1.0
137 -------------------------------------------------------------------
138 PROCEDURE Process_nonroutine_job (
139 p_api_version IN NUMBER,
140 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
141 p_commit IN VARCHAR2 := Fnd_Api.g_false,
142 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
143 p_module_type IN VARCHAR2 := 'JSP',
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 p_x_sr_task_tbl IN OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_tbl_type,
148 p_x_mr_asso_tbl IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type
149 )
150 IS
151 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_NONROUTINE_JOB';
152 l_api_version CONSTANT NUMBER := 1.0;
153 l_return_status VARCHAR2(3);
154 l_msg_count NUMBER;
155 l_msg_data VARCHAR2(2000);
156 l_sr_task_rec ahl_prd_nonroutine_pvt.sr_task_rec_type;
157 l_err_msg_count NUMBER;
158 l_convert_validate_status VARCHAR2(3);
159 l_sr_status_id NUMBER;
160 -- Removing this cursor as status_code is obsoleted as per the update from SR Team and we should use
161 -- incident_status_id directly. - Balaji
162 /*
163 -- Begin Changes Vasu For SR Integration
164 CURSOR cs_sr_status IS
165 SELECT incident_status_id FROM
166 cs_incident_statuses
167 WHERE status_code = 'PLANNED'
168 AND incident_subtype = 'INC'
169 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
170 AND trunc(nvl(end_date_active,sysdate));
171
172 -- End Changes Vasu for SR Integration
173 */
174 -- NR MR ER - start
175 CURSOR c_get_sr_details(p_incident_id NUMBER)
176 IS
177 SELECT object_version_number
178 FROM CS_INCIDENTS
179 WHERE incident_id = p_incident_id;
180 -- NR MR ER - end
181
182 -- FP for ER 5716489 -- start
183 -- Cursor to fetch the workorder details .
184 CURSOR c_does_wo_exist (p_incident_id NUMBER)
185 IS
186 SELECT
187 wo.workorder_id,
188 wo.status_code
189 FROM
190 ahl_visit_tasks_b vtsk,
191 ahl_workorders wo,
192 ahl_unit_effectivities_b ue
193 WHERE
194 ue.cs_incident_id = p_incident_id
195 AND ue.unit_effectivity_id = vtsk.unit_effectivity_id
196 AND vtsk.visit_task_id = wo.visit_task_id
197 AND upper(vtsk.task_type_code) = 'SUMMARY';
198
199 l_does_wo_exist c_does_wo_exist%ROWTYPE;
200 -- FP for ER 5716489 -- end
201
202 BEGIN
203
204 -- Standard start of API savepoint
205 SAVEPOINT AHL_PROCESS_NONROUTINE_JOB_PVT;
206
207 -- Standard call to check for call compatibility
208 IF NOT FND_API.Compatible_API_Call(l_api_version,
209 p_api_version,
210 l_api_name,
211 G_PKG_NAME) THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214
215 -- Initialize message list if p_init_msg_list is set to TRUE
216 IF FND_API.To_Boolean(p_init_msg_list) THEN
217 FND_MSG_PUB.Initialize;
218 END IF;
219
220 -- Initialize Procedure return status to success
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 -- Enable Debug.
224 IF (G_DEBUG = 'Y') THEN
225 AHL_DEBUG_PUB.enable_debug;
226 END IF;
227
228 -- Add debug mesg.
229 IF (G_DEBUG = 'Y') THEN
230 AHL_DEBUG_PUB.debug('Begin private API:' || G_PKG_NAME || '.' || l_api_name);
231 END IF;
232
233 --------------------------------------------------------------------------------
234 -- Clear id's if the module type is 'JSP'.
235 -- Call value to id conversion and default_and_validate_param procedure.
236 -- If defaulting is successfully then call create service request
237 -- and create task api if operation_type is 'CREATE' else
238 -- call update service request if operation_type is 'UPDATE'
239 --------------------------------------------------------------------------------
240 IF ( p_x_sr_task_tbl.COUNT > 0) THEN
241
242 -- Call write to log procedure to log the input parameter
243 -- values for debug
244 IF (G_DEBUG = 'Y') THEN
245 write_to_log(p_sr_tasK_tbl => p_x_sr_task_tbl);
246 AHL_DEBUG_PUB.debug('INPUT - module_type :'||p_module_type);
247 END IF;
248
249
250 l_convert_validate_status := FND_API.G_RET_STS_SUCCESS;
251
252 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
253
254 -- Add the logic
255
256 l_sr_task_rec := p_x_sr_task_tbl(i);
257
258 IF upper(p_module_type) = 'JSP' THEN
259
260 IF upper(l_sr_task_rec.operation_type) = 'CREATE' THEN
261
262 l_sr_task_rec.type_id := FND_API.G_MISS_NUM;
263 l_sr_task_rec.severity_id := FND_API.G_MISS_NUM;
264 l_sr_task_rec.urgency_id := FND_API.G_MISS_NUM;
265 l_sr_task_rec.problem_code := FND_API.G_MISS_CHAR;
266
267 -- VLAKKU :: ER # 13787940
268 -- l_sr_task_rec.Quality_inspection_type_code := FND_API.G_MISS_CHAR;
269
270 -- NR MR ER -- start
271 --l_sr_task_rec.resolution_code := FND_API.G_MISS_CHAR;
272 -- NR MR ER -- end
273 l_sr_task_rec.visit_id := FND_API.G_MISS_NUM;
274 l_sr_task_rec.instance_id := FND_API.G_MISS_NUM;
275
276 ELSIF upper(l_sr_task_rec.operation_type) = 'UPDATE' THEN
277
278 l_sr_task_rec.urgency_id := FND_API.G_MISS_NUM;
279 l_sr_task_rec.problem_code := FND_API.G_MISS_CHAR;
280 -- NR MR ER -- start
281 --l_sr_task_rec.resolution_code := FND_API.G_MISS_CHAR;
282 -- NR MR ER -- end
283 END IF;
284
285 END IF;
286
287 IF (G_DEBUG = 'Y') THEN
288 AHL_DEBUG_PUB.debug('PROC : Calling Convert_val_to_id procedure');
289 END IF;
290
291 -- Call value to id conversion
292 Convert_val_to_id( p_x_sr_task_rec => l_sr_task_rec,
293 x_return_status => l_return_status);
294
295 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
296 l_convert_validate_status := FND_API.G_RET_STS_ERROR;
297 END IF;
298
299 IF (G_DEBUG = 'Y') THEN
300 AHL_DEBUG_PUB.debug('PROC : Calling Default_and_validate_param procedure');
301 END IF;
302
303 -- Call the Default and validate param procedure
304 Default_and_validate_param( p_x_sr_task_rec => l_sr_task_rec,
305 p_mr_asso_count => p_x_mr_asso_tbl.COUNT,
306 p_module_type => p_module_type,
307 x_return_status => l_return_status);
308
309 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
310 l_convert_validate_status := FND_API.G_RET_STS_ERROR;
311 END IF;
312
313 p_x_sr_task_tbl(i) := l_sr_task_rec;
314
315 END LOOP;
316
317 -- Check For Errors.
318 IF( l_convert_validate_status <> FND_API.G_RET_STS_SUCCESS ) THEN
319 RAISE FND_API.G_EXC_ERROR;
320 END IF;
321
322 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
323
324 l_sr_task_rec := p_x_sr_task_tbl(i);
325
326 IF ( upper(l_sr_task_rec.operation_type) = 'CREATE') THEN
327
328 IF (G_DEBUG = 'Y') THEN
329 AHL_DEBUG_PUB.debug('PROC : Calling the Create SR procedure');
330 END IF;
331
332 -- Call Create Service Request procedure
333
334 Create_sr( p_x_sr_task_rec => l_sr_task_rec,
335 x_return_status => l_return_status);
336
337
338 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
339 RAISE FND_API.G_EXC_ERROR;
340 END IF;
341
342 ELSIF ( upper(l_sr_task_rec.operation_type) = 'UPDATE' ) THEN
343
344 IF (G_DEBUG = 'Y') THEN
345 AHL_DEBUG_PUB.debug('PROC : Calling the Update SR procedure');
346 END IF;
347
348 -- Call Update Service Request procedure
349 -- MR NR ER -- start
350 IF p_module_type IS NULL OR p_module_type <> 'SR_OA' THEN
351 Update_sr( p_x_sr_task_rec => l_sr_task_rec,
352 x_return_status => l_return_status);
353
354 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
355 RAISE FND_API.G_EXC_ERROR;
356 END IF;
357
358 -- JKJain, Bug 8540538 start
359
360 END IF; -- MR NR ER -- end
361
362 -- FP for ER 5716489 -- start
363 --- If the mode is update , then check if the NR has a corresponding workorder created or not
364 -- and set the flags accordingly .
365
366 --1. Query if the NR has a workorder created !
367
368 OPEN c_does_wo_exist(l_sr_task_rec.Incident_id);
369 FETCH c_does_wo_exist INTO l_does_wo_exist;
370 CLOSE c_does_wo_exist;
371
372 --2. Check if a workorder exists for the NR
373 IF ( l_does_wo_exist.workorder_id IS NOT NULL ) THEN
374
375 -- Set the p_x_task_tbl(i).WO_Create_flag to Y
376 l_sr_task_rec.WO_Create_flag := 'Y';
377
378 --Check the Status of the workorder and set the p_x_sr_task_tbl(i).WO_Release_flag
379 IF ( l_does_wo_exist.status_code = '3') THEN
380 l_sr_task_rec.WO_Release_flag := 'Y';
381 ELSE
382 l_sr_task_rec.WO_Release_flag := 'N';
383 END IF;
384 ELSE
385 -- If the workorder Id is null, ie; a work order does nt exist for the NR
386 -- Set the he p_x_task_tbl(i).WO_Create_flag to N
387 l_sr_task_rec.WO_Create_flag := 'N';
388 END IF;
389 -- FP for ER 5716489 -- end
390 -- JKJain, Bug 8540538 end
391 END IF;
392
393 p_x_sr_task_tbl(i) := l_sr_task_rec;
394
395 END LOOP;
396
397 -- initialize stack if any warning messages from CS APIs exist.
398 IF (FND_MSG_PUB.count_msg > 0) THEN
399 FND_MSG_PUB.Initialize;
400 END IF;
401
402 -- NR MR ER -- start
403 /*
404 IF (G_DEBUG = 'Y') THEN
405 AHL_DEBUG_PUB.debug('PROC : Calling the Create Task procedure');
406 END IF;
407
408 -- call Create VWP Task Api
409
410 Create_task( p_x_task_tbl => p_x_sr_task_tbl,
411 x_return_status => l_return_status);
412
413 l_msg_count := FND_MSG_PUB.count_msg;
414
415 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS or l_msg_count>0) THEN
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418 */
419 -- NR MR ER - End
420
421 -- NR MR ER - start
422 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
423 fnd_log.string(
424 fnd_log.level_statement,
425 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
426 'p_x_mr_asso_tbl.COUNT ->'||p_x_mr_asso_tbl.COUNT
427 );
428 END IF;
429
430 IF (
431 upper(l_sr_task_rec.operation_type) = 'CREATE'
432 OR
433 (
434 upper(l_sr_task_rec.operation_type) = 'UPDATE'
435 AND
436 p_x_mr_asso_tbl.COUNT > 0
437 )
438 )
439 THEN
440 Process_Mr(
441 p_x_task_tbl => p_x_sr_task_tbl,
442 p_mr_assoc_tbl => p_x_mr_asso_tbl,
443 p_module_type => p_module_type,
444 x_return_status => x_return_status,
445 x_msg_count => x_msg_count,
446 x_msg_data => x_msg_data
447 );
448
449 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
450 fnd_log.string(
451 fnd_log.level_statement,
452 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
453 'After calling Process_Mr...Return status->'||x_return_status
454 );
455 END IF;
456
457 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
458 RAISE FND_API.G_EXC_ERROR;
459 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462
463 -- Bug # 8267142 (FP for KAL Bug # 7667326) -- start
464 IF ( p_x_sr_task_tbl.COUNT > 0) THEN
465
466 FOR l_sr_count IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
467
468 OPEN c_get_sr_details(p_x_sr_task_tbl(l_sr_count).Incident_id);
469 FETCH c_get_sr_details INTO p_x_sr_task_tbl(l_sr_count).Incident_object_version_number;
470 CLOSE c_get_sr_details;
471
472 END LOOP;
473
474 END IF;
475 -- Bug # 8267142 (FP for KAL Bug # 7667326) -- end
476 END IF;
477
478 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
479 fnd_log.string(
480 fnd_log.level_statement,
481 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
482 'After Process_Mr API'
483 );
484 END IF;
485 -- NR MR ER - end
486
487 -- MANESING::Supplier Warranty, 04-Oct-2010, call procedure to update warranty entitlement records if required
488 Update_warranty_entitlements(p_api_version => p_api_version,
489 p_init_msg_list => p_init_msg_list,
490 p_commit => p_commit,
491 p_validation_level => p_validation_level,
492 p_module_type => p_module_type,
493 p_mr_asso_count => p_x_mr_asso_tbl.COUNT,
494 p_x_sr_task_tbl => p_x_sr_task_tbl,
495 x_return_status => x_return_status,
496 x_msg_count => x_msg_count,
497 x_msg_data => x_msg_data);
498
499 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
500 RAISE FND_API.G_EXC_ERROR;
501 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END IF;
504
505 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
506 fnd_log.string(fnd_log.level_statement,
507 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
508 'After Update_warranty_entitlements API');
509 END IF;
510
511 -- Modified by VSUNDARA For SR Integration
512 --- Change the SR STATUS as Planned
513 -- Removing the code as status_code is obsoleted as per the update from SR Team and we should use
514 -- incident_status_id directly. - Balaji
515 /*
516 OPEN cs_sr_status;
517 FETCH cs_sr_status INTO l_sr_status_id;
518 IF ( cs_sr_status%NOTFOUND) THEN
519 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_STATUS_ERROR');
520 Fnd_Msg_Pub.ADD;
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 END IF;
523 */
524
525 l_sr_status_id := G_SR_PLANNED_STATUS_ID;
526
527 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
528
529 l_sr_task_rec := p_x_sr_task_tbl(i);
530
531 IF ( upper(l_sr_task_rec.operation_type) = 'CREATE') THEN
532
533 -- FP for ER 5716489 -- start
534 -- Do not call the update SR Api in cases where a non-routine is created without workorder .
535 IF (nvl(upper(l_sr_task_rec.WO_Create_flag),'Y') = 'Y')
536 THEN
537
538 l_sr_task_rec.Status_id := l_sr_status_id;
539 -- NR MR ER -- start
540 --l_sr_task_rec.incident_object_version_number := 1 ;
541 OPEN c_get_sr_details(l_sr_task_rec.Incident_id);
542 FETCH c_get_sr_details INTO l_sr_task_rec.incident_object_version_number;
543 CLOSE c_get_sr_details;
544
545 -- NR MR ER -- end
546 IF (G_DEBUG = 'Y') THEN
547 AHL_DEBUG_PUB.debug('PROC : Calling the Update SR procedure');
548 END IF;
549 -- Call Update Service Request procedure
550 Update_sr( p_x_sr_task_rec => l_sr_task_rec,
551 x_return_status => l_return_status);
552
553 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556
557 END IF;
558 -- FP for ER 5716489 -- end
559
560 IF(( l_sr_task_rec.object_id IS NOT NULL AND l_sr_task_rec.object_id <> FND_API.G_MISS_NUM )
561 AND (l_sr_task_rec.object_type = 'AHL_PRD_DISP')) THEN
562
563 AHL_PRD_DISP_UTIL_PVT.Create_SR_Disp_Link (
564
565 p_api_version => 1.0,
566 p_init_msg_list => FND_API.G_TRUE,
567 p_commit => FND_API.G_FALSE,
568 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
569 x_return_status => x_return_status,
570 x_msg_count => l_msg_count,
571 x_msg_data => l_msg_data,
572 p_service_request_id => l_sr_task_rec.incident_id,
573 p_disposition_id => l_sr_task_rec.object_id,
574 x_link_id => l_sr_task_rec.link_id
575 );
576
577 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580 END IF ;
581 END IF;
582
583 END LOOP;
584
585
586
587
588 -- END Changes
589
590
591
592
593 END IF;
594
595 -- initialize stack if any warning messages from CS APIs exist.
596 IF (FND_MSG_PUB.count_msg > 0) THEN
597 FND_MSG_PUB.Initialize;
598 END IF;
599
600 -- Standard check of p_commit
601 IF FND_API.TO_BOOLEAN(p_commit) THEN
602 COMMIT WORK;
603 END IF;
604
605 IF (G_DEBUG = 'Y') THEN
606 AHL_DEBUG_PUB.debug('END - Successfully completion of '||G_PKG_NAME||'.'||l_api_name||' API ');
607 END IF;
608
609 -- Count and Get messages
610 FND_MSG_PUB.count_and_get
611 ( p_encoded => fnd_api.g_false,
612 p_count => x_msg_count,
613 p_data => x_msg_data
614 );
615
616 -- Disable debug (if enabled)
617 IF (G_DEBUG = 'Y') THEN
618 AHL_DEBUG_PUB.disable_debug;
619 END IF;
620
621 EXCEPTION
622 WHEN FND_API.G_EXC_ERROR THEN
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 Rollback to AHL_PROCESS_NONROUTINE_JOB_PVT;
625 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
626 p_data => x_msg_data,
627 p_encoded => fnd_api.g_false);
628
629 -- Disable debug
630 IF (G_DEBUG = 'Y') THEN
631 AHL_DEBUG_PUB.disable_debug;
632 END IF;
633
634
635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
637 Rollback to AHL_PROCESS_NONROUTINE_JOB_PVT;
638 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
639 p_data => x_msg_data,
640 p_encoded => fnd_api.g_false);
641
642 -- Disable debug
643 IF (G_DEBUG = 'Y') THEN
644 AHL_DEBUG_PUB.disable_debug;
645 END IF;
646
647 WHEN OTHERS THEN
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 Rollback to AHL_PROCESS_NONROUTINE_JOB_PVT;
650 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
651 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
652 p_procedure_name => 'Process_Nonroutine_Job',
653 p_error_text => SUBSTR(SQLERRM,1,240));
654 END IF;
655 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
656 p_data => x_msg_data,
657 p_encoded => fnd_api.g_false);
658
659 -- Disable debug
660 IF (G_DEBUG = 'Y') THEN
661 AHL_DEBUG_PUB.disable_debug;
662 END IF;
663
664 END Process_nonroutine_job;
665
666 --------------------------------------------
667 -- Local Procedure Definitions follow --
668 --------------------------------------------
669 --------------------------------------------
670 -- Convert value to id --
671 --------------------------------------------
672
673 ----------------------------------------------
674 -- Convert_val_to_id procedure will convert
675 -- values to id's only if the id's are null
676 ----------------------------------------------
677 PROCEDURE Convert_val_to_id(
678 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
679 x_return_status OUT NOCOPY VARCHAR2
680 ) IS
681
682 l_customer_id NUMBER;
683 l_customer_name VARCHAR2(360);
684 l_contact_id NUMBER;
685 l_contact_name VARCHAR2(360);
686
687 CURSOR sr_problem_code (p_meaning IN VARCHAR2) IS
688 SELECT lookup_code FROM fnd_lookup_values_vl
689 WHERE lookup_type = 'REQUEST_PROBLEM_CODE'
690 AND enabled_flag = 'Y'
691 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
692 AND trunc(nvl(end_date_active,sysdate))
693 AND upper(meaning) = upper(p_meaning);
694
695 -- ::VLAKKU::ER # 13787940 :: Quality inspection type -- Start
696 CURSOR sr_qality_inspection_type (p_qa_inspection_type_desc IN VARCHAR2) IS
697 SELECT short_code FROM qa_char_value_lookups_v
698 WHERE char_id = 87
699 AND upper(description) = upper(ltrim(rtrim(p_qa_inspection_type_desc)));
700 -- ::VLAKKU::ER # 13787940 :: Quality inspection type -- End
701
702 CURSOR sr_resolution_code (p_meaning IN VARCHAR2) IS
703 SELECT lookup_code FROM fnd_lookup_values_vl
704 WHERE lookup_type = 'REQUEST_RESOLUTION_CODE'
705 AND enabled_flag = 'Y'
706 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
707 AND trunc(nvl(end_date_active,sysdate))
708 AND upper(meaning) = upper(p_meaning);
709
710 CURSOR sr_customer_product(p_instance_number IN VARCHAR2) IS
711 SELECT instance_id FROM csi_item_instances
712 WHERE instance_number = p_instance_number;
713
714 CURSOR ahl_visit(p_visit_number IN NUMBER) IS
715 SELECT visit_id FROM ahl_visits_b
716 WHERE visit_number = p_visit_number;
717 -- Begin Changes by VSUNDARA
718 -- TO validate the Instance Owner
719 CURSOR ahl_instance_owner(p_instance_number IN VARCHAR2) IS
720 SELECT OWNER_PARTY_ID
721 FROM csi_item_instances
722 WHERE instance_number = p_instance_number;
723 -- END Changes
724
725 -- added to fix bug# 8265902
726 CURSOR get_status_id (p_status_name IN VARCHAR2) IS
727 SELECT incident_status_id
728 FROM cs_incident_statuses_vl
729 WHERE incident_subtype = 'INC'
730 AND UPPER(name) = UPPER(p_status_name)
731 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
732 AND TRUNC(NVL(end_date_active, SYSDATE))
733 AND rownum<2;
734
735 l_status_id NUMBER;
736
737 BEGIN
738
739 -- Initialize Procedure return status to success
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 IF ( upper(p_x_sr_task_rec.operation_type) = 'CREATE' ) THEN
743
744 -- added to fix bug# 8265902
745 IF ((p_x_sr_task_rec.status_id is null or p_x_sr_task_rec.status_id = FND_API.G_MISS_NUM) and
746 (p_x_sr_task_rec.status_name is not null AND p_x_sr_task_rec.status_name <> FND_API.G_MISS_CHAR)) THEN
747 OPEN get_status_id(p_x_sr_task_rec.status_name);
748 FETCH get_status_id INTO l_status_id;
749 IF (get_status_id%FOUND) THEN
750 p_x_sr_task_rec.status_id := l_status_id;
751 END IF;
752 CLOSE get_status_id;
753 END IF;
754
755 -- Derive the Customer id, if its null.
756 -- If customer id is not null derive the customer name and id
757 -- and check the customer name against the input value, if <>
758 -- return error msg. If only customer name is passed then
759 -- derive the customer id and name.
760
761 IF( p_x_sr_task_rec.customer_id is not null and
762 p_x_sr_task_rec.customer_id <> FND_API.G_MISS_NUM
763 and (p_x_sr_task_rec.customer_name is not null and
764 p_x_sr_task_rec.customer_name <> FND_API.G_MISS_CHAR)) THEN
765 BEGIN
766 Select party_name
767 into
768 l_customer_name
769 from hz_parties
770 where party_id
771 = p_x_sr_task_rec.customer_id;
772
773 IF (l_customer_name <> nvl(p_x_sr_task_rec.customer_name,l_customer_name) and
774 (p_x_sr_task_rec.customer_name <> FND_API.G_MISS_CHAR) ) THEN
775 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_NAME_MISMATCH');
776 Fnd_Message.SET_TOKEN('CUST_NAME',p_x_sr_task_rec.customer_name);
777 Fnd_Message.SET_TOKEN('CUST_ID',p_x_sr_task_rec.customer_id);
778 Fnd_Msg_Pub.ADD;
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 END IF;
781
782 EXCEPTION
783 WHEN NO_DATA_FOUND THEN
784 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_ID_INVALID');
785 Fnd_Message.SET_TOKEN('CUST_ID',p_x_sr_task_rec.customer_id);
786 Fnd_Msg_Pub.ADD;
787 x_return_status := FND_API.G_RET_STS_ERROR;
788 WHEN TOO_MANY_ROWS THEN
789 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_ID_NOT_UNIQUE');
790 Fnd_Message.SET_TOKEN('CUST_ID',p_x_sr_task_rec.customer_id);
791 Fnd_Msg_Pub.ADD;
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 END;
794
795
796 ELSIF ( (p_x_sr_task_rec.customer_id is null or
797 p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM)
798 and (p_x_sr_task_rec.customer_name is not null and
799 p_x_sr_task_rec.customer_name <> FND_API.G_MISS_CHAR)) THEN
800
801 BEGIN
802 Select party_id
803 into
804 l_customer_id
805 from hz_parties
806 where party_name = p_x_sr_task_rec.customer_name;
807 p_x_sr_task_rec.customer_id := l_customer_id;
808
809 EXCEPTION
810 WHEN NO_DATA_FOUND THEN
811 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_NAME_INVALID');
812 Fnd_Message.SET_TOKEN('CUST_NAME',p_x_sr_task_rec.customer_name);
813 Fnd_Msg_Pub.ADD;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 WHEN TOO_MANY_ROWS THEN
816 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_NAME_NOT_UNIQUE');
817 Fnd_Message.SET_TOKEN('CUST_NAME',p_x_sr_task_rec.customer_name);
818 Fnd_Msg_Pub.ADD;
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 END;
821 -- NR MR ER - start
822 -- Balaji added following elseif clause to explicitly pass null to
823 -- SR API so that appropriate error msg is thrown.
824 -- Part of ER # 5550702
825 ELSIF p_x_sr_task_rec.customer_name is null THEN
826
827 p_x_sr_task_rec.customer_id := NULL;
828
829 END IF;
830 -- NR MR ER - end
831
832
833
834 -- Derive the Contact id, if its null and contact type in
835 -- 'RELATIONSHIP' or 'PERSON'.
836 -- If contact id is not null derive the contact name and id
837 -- and check the contact name against the input value, if <>
838 -- return error msg. If only contact name is passed then
839 -- derive the contact id and name.
840
841 IF (upper(p_x_sr_task_rec.contact_type) in ('PARTY_RELATIONSHIP','PERSON')) THEN
842
843 IF( p_x_sr_task_rec.contact_id is not null and
844 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
845 and ( p_x_sr_task_rec.contact_name is not null and
846 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
847 BEGIN
848 Select party_name
849 into
850 l_contact_name
851 from hz_parties
852 where party_id
853 = p_x_sr_task_rec.contact_id;
854
855
856 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
857 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
858 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
859 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
860 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
861 Fnd_Msg_Pub.ADD;
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 END IF;
864
865 EXCEPTION
866 WHEN NO_DATA_FOUND THEN
867 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
868 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
869 Fnd_Msg_Pub.ADD;
870 x_return_status := FND_API.G_RET_STS_ERROR;
871 WHEN TOO_MANY_ROWS THEN
872 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
873 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
874 Fnd_Msg_Pub.ADD;
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 END;
877
878
879 ELSIF ( (p_x_sr_task_rec.contact_id is null or
880 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
881 and ( p_x_sr_task_rec.contact_name is not null and
882 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
883
884 BEGIN
885 Select party_id
886 into
887 l_contact_id
888 from hz_parties
889 where party_name = p_x_sr_task_rec.contact_name;
890
891 p_x_sr_task_rec.contact_id := l_contact_id;
892
893
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
897 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
898 Fnd_Msg_Pub.ADD;
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 WHEN TOO_MANY_ROWS THEN
901 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
902 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
903 Fnd_Msg_Pub.ADD;
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 END;
906 -- NR MR ER -- start
907 -- Balaji added following elseif clause to explicitly pass null to
908 -- SR API so that appropriate error msg is thrown.
909 -- Part of MR NR ER
910 ELSIF p_x_sr_task_rec.contact_name is null THEN
911
912 p_x_sr_task_rec.contact_id := NULL;
913
914 END IF;
915 -- NR MR ER -- end
916
917 END IF;
918
919 -- Derive the Contact id, if its null and contact type
920 -- 'EMPLOYEE'.
921 -- If contact id is not null derive the contact name and id
922 -- and check the contact name against the input value, if <>
923 -- return error msg. If only contact name is passed then
924 -- derive the contact id and name.
925
926 IF (upper(p_x_sr_task_rec.contact_type) = 'EMPLOYEE') THEN
927
928 IF( p_x_sr_task_rec.contact_id is not null and
929 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
930 and ( p_x_sr_task_rec.contact_name is not null and
931 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
932 BEGIN
933 Select full_name
934 into
935 l_contact_name
936 from per_people_f
937 where person_id
938 = p_x_sr_task_rec.contact_id
939 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
940 and trunc(nvl(effective_end_date,sysdate));
941
942
943 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
944 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) ) THEN
945 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
946 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
947 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
948 Fnd_Msg_Pub.ADD;
949 x_return_status := FND_API.G_RET_STS_ERROR;
950 END IF;
951
952 EXCEPTION
953 WHEN NO_DATA_FOUND THEN
954 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
955 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
956 Fnd_Msg_Pub.ADD;
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 WHEN TOO_MANY_ROWS THEN
959 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
960 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
961 Fnd_Msg_Pub.ADD;
962 x_return_status := FND_API.G_RET_STS_ERROR;
963 END;
964
965
966 ELSIF ( (p_x_sr_task_rec.contact_id is null or
967 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
968 and ( p_x_sr_task_rec.contact_name is not null and
969 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
970
971 BEGIN
972 Select person_id
973 into
974 l_contact_id
975 from per_people_f
976 where full_name = p_x_sr_task_rec.contact_name
977 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
978 and trunc(nvl(effective_end_date,sysdate));
979
980 p_x_sr_task_rec.contact_id := l_contact_id;
981
982 EXCEPTION
983 WHEN NO_DATA_FOUND THEN
984 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
985 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
986 Fnd_Msg_Pub.ADD;
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 WHEN TOO_MANY_ROWS THEN
989 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
990 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
991 Fnd_Msg_Pub.ADD;
992 x_return_status := FND_API.G_RET_STS_ERROR;
993 END;
994 -- NR MR ER -- start
995 -- Balaji added following elseif clause to explicitly pass null to
996 -- SR API so that appropriate error msg is thrown.
997 -- Part of NR MR ER.
998 ELSIF p_x_sr_task_rec.contact_name is null THEN
999
1000 p_x_sr_task_rec.contact_id := NULL;
1001
1002 END IF;
1003 -- NR MR ER -- end
1004 END IF;
1005
1006
1007 -- Derive the visit id from visit number
1008 IF ((p_x_sr_task_rec.visit_id is null or
1009 p_x_sr_task_rec.visit_id = FND_API.G_MISS_NUM) and
1010 p_x_sr_task_rec.visit_number is not null) THEN
1011
1012 OPEN ahl_visit(p_x_sr_task_rec.visit_number);
1013 FETCH ahl_visit INTO p_x_sr_task_rec.visit_id;
1014 CLOSE ahl_visit;
1015
1016 END IF;
1017
1018
1019 -- Derive the instance id from instance number
1020 IF ((p_x_sr_task_rec.instance_id is null or
1021 p_x_sr_task_rec.instance_id = FND_API.G_MISS_NUM) and
1022 p_x_sr_task_rec.instance_number is not null ) THEN
1023
1024 OPEN sr_customer_product(p_x_sr_task_rec.instance_number);
1025 FETCH sr_customer_product INTO p_x_sr_task_rec.instance_id;
1026 CLOSE sr_customer_product;
1027
1028 END IF;
1029
1030
1031 -- Derive the problem code from problem meaning
1032 IF ((p_x_sr_task_rec.problem_code is null or
1033 p_x_sr_task_rec.problem_code = FND_API.G_MISS_CHAR) and
1034 p_x_sr_task_rec.problem_meaning is not null ) THEN
1035
1036 OPEN sr_problem_code(p_x_sr_task_rec.problem_meaning);
1037 FETCH sr_problem_code INTO p_x_sr_task_rec.problem_code;
1038 CLOSE sr_problem_code;
1039
1040 -- NR MR ER - start
1041 -- Balaji added following check to throw appropriate
1042 -- error message if invalid problem code is entered.
1043 -- SR API doesnt throw any error if the problem code is invalid neither
1044 -- it updates invalid value. Hence throwing error explicitly
1045 -- Part of NR MR ER.
1046 IF p_x_sr_task_rec.problem_code IS NULL THEN
1047 Fnd_Message.SET_NAME('AHL','AHL_PRD_PROBLEM_CODE_INVALID');
1048 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.problem_meaning);
1049 Fnd_Msg_Pub.ADD;
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 END IF;
1052 -- NR MR ER - end
1053
1054 END IF;
1055
1056 -- :: VLAKKU :: ER # 13787940 --START
1057 -- Derive the Quality inspection type code from Quality inspection type description
1058 IF ((p_x_sr_task_rec.quality_inspection_type_code is null or
1059 p_x_sr_task_rec.quality_inspection_type_code = FND_API.G_MISS_CHAR) and
1060 p_x_sr_task_rec.quality_inspection_type is not null ) THEN
1061
1062 OPEN sr_qality_inspection_type(p_x_sr_task_rec.quality_inspection_type);
1063 FETCH sr_qality_inspection_type INTO p_x_sr_task_rec.quality_inspection_type_code;
1064 IF (sr_qality_inspection_type%NOTFOUND ) THEN
1065 Fnd_Message.SET_NAME('AHL','AHL_PRD_QA_INSP_TYPE_INVALID');
1066 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.quality_inspection_type);
1067 Fnd_Msg_Pub.ADD;
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 END IF;
1070 CLOSE sr_qality_inspection_type;
1071
1072 END IF;
1073 -- :: VLAKKU :: ER # 13787940 -- END
1074
1075 -- Derive the resolution code from resolution meaning.
1076 IF ((p_x_sr_task_rec.resolution_code is null or
1077 p_x_sr_task_rec.resolution_code = FND_API.G_MISS_CHAR) and
1078 p_x_sr_task_rec.resolution_meaning is not null ) THEN
1079
1080 OPEN sr_resolution_code(p_x_sr_task_rec.resolution_meaning);
1081 FETCH sr_resolution_code INTO p_x_sr_task_rec.resolution_code;
1082 CLOSE sr_resolution_code;
1083
1084 -- NR MR ER -- start
1085 -- Balaji added following check to throw appropriate
1086 -- error message if invalid resolution code is entered.
1087 -- SR API doesnt throw any error if the resolution code is invalid neither
1088 -- it updates invalid value. Hence throwing error explicitly
1089 -- Part of NR MR ER
1090 IF p_x_sr_task_rec.resolution_code IS NULL THEN
1091 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_INV');
1092 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.resolution_meaning);
1093 Fnd_Msg_Pub.ADD;
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095 END IF;
1096 -- NR MR ER -- end
1097
1098 END IF;
1099
1100 -- MANESING::Supplier Warranty, 04-Oct-2010,
1101 -- Derive the warranty contract id from warranty contract number
1102 BEGIN
1103 -- following If condition will be useful while Creating SR from Public APIs
1104 IF ((p_x_sr_task_rec.warranty_contract_number is null or
1105 p_x_sr_task_rec.warranty_contract_number = FND_API.G_MISS_NUM) and
1106 (p_x_sr_task_rec.warranty_contract_id is not null and
1107 p_x_sr_task_rec.warranty_contract_id <> FND_API.G_MISS_NUM)) THEN
1108
1109 Select warranty_contract_id into p_x_sr_task_rec.warranty_contract_id
1110 From ahl_warranty_contracts_b
1111 Where warranty_contract_id = p_x_sr_task_rec.warranty_contract_id
1112 and item_instance_id = p_x_sr_task_rec.instance_id
1113 and contract_status_code = 'ACTIVE';
1114
1115 ELSIF (p_x_sr_task_rec.warranty_contract_number is not null and
1116 p_x_sr_task_rec.warranty_contract_number <> FND_API.G_MISS_NUM) THEN
1117
1118 Select warranty_contract_id into p_x_sr_task_rec.warranty_contract_id
1119 From ahl_warranty_contracts_b
1120 Where contract_number = p_x_sr_task_rec.warranty_contract_number
1121 and item_instance_id = p_x_sr_task_rec.instance_id
1122 and contract_status_code = 'ACTIVE';
1123 END IF;
1124
1125 EXCEPTION
1126 WHEN NO_DATA_FOUND THEN
1127 Fnd_Message.SET_NAME('AHL', 'AHL_WARRANTY_INVALID_CNTR_NUM');
1128 Fnd_Message.SET_TOKEN('CNTR_NUM', p_x_sr_task_rec.warranty_contract_number);
1129 Fnd_Msg_Pub.ADD;
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 END;
1132
1133 ELSIF ( upper(p_x_sr_task_rec.operation_type) = 'UPDATE') THEN
1134
1135
1136 -- Derive the Contact id, if its null and contact type in
1137 -- 'RELATIONSHIP' or 'PERSON'.
1138 -- If contact id is not null derive the contact name and id
1139 -- and check the contact name against the input value, if <>
1140 -- return error msg. If only contact name is passed then
1141 -- derive the contact id and name.
1142
1143 IF (upper(p_x_sr_task_rec.contact_type) in ('PARTY_RELATIONSHIP','PERSON')) THEN
1144
1145 IF( p_x_sr_task_rec.contact_id is not null and
1146 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
1147 and ( p_x_sr_task_rec.contact_name is not null and
1148 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
1149 BEGIN
1150 Select party_name
1151 into
1152 l_contact_name
1153 from hz_parties
1154 where party_id
1155 = p_x_sr_task_rec.contact_id;
1156
1157 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
1158 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
1159 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
1160 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1161 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1162 Fnd_Msg_Pub.ADD;
1163 x_return_status := FND_API.G_RET_STS_ERROR;
1164 END IF;
1165
1166
1167 EXCEPTION
1168 WHEN NO_DATA_FOUND THEN
1169 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
1170 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1171 Fnd_Msg_Pub.ADD;
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 WHEN TOO_MANY_ROWS THEN
1174 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
1175 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1176 Fnd_Msg_Pub.ADD;
1177 x_return_status := FND_API.G_RET_STS_ERROR;
1178 END;
1179
1180
1181 ELSIF ( (p_x_sr_task_rec.contact_id is null or
1182 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
1183 and ( p_x_sr_task_rec.contact_name is not null and
1184 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
1185
1186 BEGIN
1187 Select party_id
1188 into
1189 l_contact_id
1190 from hz_parties
1191 where party_name = p_x_sr_task_rec.contact_name;
1192
1193 p_x_sr_task_rec.contact_id := l_contact_id;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
1198 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1199 Fnd_Msg_Pub.ADD;
1200 x_return_status := FND_API.G_RET_STS_ERROR;
1201 WHEN TOO_MANY_ROWS THEN
1202 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
1203 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1204 Fnd_Msg_Pub.ADD;
1205 x_return_status := FND_API.G_RET_STS_ERROR;
1206 END;
1207 -- NR MR ER -- start
1208 -- Balaji added following elseif clause to explicitly pass null to
1209 -- SR API so that appropriate error msg is thrown.
1210 -- Part of NR MR ER.
1211 ELSIF p_x_sr_task_rec.contact_name is null THEN
1212
1213 p_x_sr_task_rec.contact_id := NULL;
1214
1215 END IF;
1216 -- NR MR ER -- end
1217
1218 END IF;
1219
1220
1221 -- Derive the Contact id, if its null and contact type
1222 -- 'EMPLOYEE'.
1223 -- If contact id is not null derive the contact name and id
1224 -- and check the contact name against the input value, if <>
1225 -- return error msg. If only contact name is passed then
1226 -- derive the contact id and name.
1227
1228 IF (upper(p_x_sr_task_rec.contact_type) = 'EMPLOYEE') THEN
1229
1230 IF( p_x_sr_task_rec.contact_id is not null and
1231 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
1232 and ( p_x_sr_task_rec.contact_name is not null and
1233 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR)) THEN
1234 BEGIN
1235 Select full_name
1236 into
1237 l_contact_name
1238 from per_people_f
1239 where person_id
1240 = p_x_sr_task_rec.contact_id
1241 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
1242 and trunc(nvl(effective_end_date,sysdate));
1243
1244 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
1245 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
1246 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
1247 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1248 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1249 Fnd_Msg_Pub.ADD;
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251 END IF;
1252
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
1256 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1257 Fnd_Msg_Pub.ADD;
1258 x_return_status := FND_API.G_RET_STS_ERROR;
1259 WHEN TOO_MANY_ROWS THEN
1260 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
1261 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1262 Fnd_Msg_Pub.ADD;
1263 x_return_status := FND_API.G_RET_STS_ERROR;
1264 END;
1265
1266
1267 ELSIF ( (p_x_sr_task_rec.contact_id is null or
1268 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
1269 and ( p_x_sr_task_rec.contact_name is not null and
1270 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR)) THEN
1271
1272 BEGIN
1273 Select person_id
1274 into
1275 l_contact_id
1276 from per_people_f
1277 where full_name = p_x_sr_task_rec.contact_name
1278 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
1279 and trunc(nvl(effective_end_date,sysdate));
1280
1281 p_x_sr_task_rec.contact_id := l_contact_id;
1282
1283 EXCEPTION
1284 WHEN NO_DATA_FOUND THEN
1285 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
1286 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1287 Fnd_Msg_Pub.ADD;
1288 x_return_status := FND_API.G_RET_STS_ERROR;
1289 WHEN TOO_MANY_ROWS THEN
1290 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
1291 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1292 Fnd_Msg_Pub.ADD;
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 END;
1295 -- NR MR ER -- start
1296 -- Balaji added following elseif clause to explicitly pass null to
1297 -- SR API so that appropriate error msg is thrown.
1298 -- Part of NR MR ER.
1299 ELSIF p_x_sr_task_rec.contact_name is null THEN
1300
1301 p_x_sr_task_rec.contact_id := NULL;
1302
1303 END IF;
1304 -- NR MR ER -- end
1305
1306 END IF;
1307
1308
1309 -- Derive the problem code from problem meaning.
1310 IF ((p_x_sr_task_rec.problem_code is null or
1311 p_x_sr_task_rec.problem_code = FND_API.G_MISS_CHAR) and
1312 p_x_sr_task_rec.problem_meaning is not null ) THEN
1313
1314 OPEN sr_problem_code(p_x_sr_task_rec.problem_meaning);
1315 FETCH sr_problem_code INTO p_x_sr_task_rec.problem_code;
1316 CLOSE sr_problem_code;
1317
1318 -- NR MR ER -- start
1319 -- Balaji added following check to throw appropriate
1320 -- error message if invalid problem code is entered.
1321 -- SR API doesnt throw any error if the problem code is invalid neither
1322 -- it updates invalid value. Hence throwing error explicitly
1323 -- Part of NR MR ER.
1324 IF p_x_sr_task_rec.problem_code IS NULL THEN
1325 Fnd_Message.SET_NAME('AHL','AHL_PRD_PROBLEM_CODE_INVALID');
1326 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.problem_meaning);
1327 Fnd_Msg_Pub.ADD;
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 END IF;
1330 -- NR MR ER -- end
1331
1332 END IF;
1333
1334 -- Derive the resolution code from resolution meaning.
1335 IF ((p_x_sr_task_rec.resolution_code is null or
1336 p_x_sr_task_rec.resolution_code = FND_API.G_MISS_CHAR) and
1337 p_x_sr_task_rec.resolution_meaning is not null ) THEN
1338
1339 OPEN sr_resolution_code(p_x_sr_task_rec.resolution_meaning);
1340 FETCH sr_resolution_code INTO p_x_sr_task_rec.resolution_code;
1341 CLOSE sr_resolution_code;
1342
1343 -- NR MR ER -- start
1344 -- Balaji added following check to throw appropriate
1345 -- error message if invalid resolution code is entered.
1346 -- SR API doesnt throw any error if the resolution code is invalid neither
1347 -- it updates invalid value. Hence throwing error explicitly
1348 -- Part of NR MR ER
1349 IF p_x_sr_task_rec.resolution_code IS NULL THEN
1350 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_INV');
1351 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.resolution_meaning);
1352 Fnd_Msg_Pub.ADD;
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 END IF;
1355 -- NR MR ER -- end
1356
1357 END IF;
1358
1359 -- NR MR ER -- start
1360 -- Balaji added the code for OGMA ER (Adding MRs to Non-Routine)
1361 -- Begin
1362 -- Derive the visit id from visit number
1363 IF ((p_x_sr_task_rec.visit_id is null or
1364 p_x_sr_task_rec.visit_id = FND_API.G_MISS_NUM) and
1365 p_x_sr_task_rec.visit_number is not null) THEN
1366
1367 OPEN ahl_visit(p_x_sr_task_rec.visit_number);
1368 FETCH ahl_visit INTO p_x_sr_task_rec.visit_id;
1369 CLOSE ahl_visit;
1370
1371 END IF;
1372 -- NR MR ER -- end
1373
1374 END IF;
1375
1376 END Convert_val_to_id;
1377
1378
1379 --------------------------------------------
1380 -- Default and validate the parameters
1381 --------------------------------------------
1382
1383 ------------------------------------------------------------
1384 -- Default_and_validate_param procedure checks if
1385 -- required id's/values are passed, If not will derive
1386 -- from profile. If the profile values are null then
1387 -- it will either default the values or return an
1388 -- error message and status.
1389 ------------------------------------------------------------
1390 PROCEDURE Default_and_validate_param(
1391 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
1392 p_mr_asso_count IN NUMBER,
1393 p_module_type IN VARCHAR2,
1394 x_return_status OUT NOCOPY VARCHAR2
1395 ) IS
1396
1397 l_incident_status_id NUMBER;
1398 l_employee_id NUMBER;
1399 dummy VARCHAR2(3);
1400 l_wo_name VARCHAR2(80);
1401 l_instance_num VARCHAR2(30);
1402 l_quantity NUMBER; --amsriniv. ER 6014567
1403 l_owner_id NUMBER;
1404 l_return_status VARCHAR2(1);
1405 l_dummy VARCHAR2(1); --amsriniv. ER 6014567
1406
1407 -- Removing cursor cs_sr_status as status_code is obsoleted as per the update from SR Team
1408 -- and we should use incident_status_id directly. - Balaji
1409 /*
1410 CURSOR cs_sr_status IS
1411 SELECT incident_status_id FROM
1412 cs_incident_statuses
1413 WHERE status_code = 'OPEN'
1414 AND incident_subtype = 'INC'
1415 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
1416 AND trunc(nvl(end_date_active,sysdate));
1417 */
1418
1419 CURSOR cs_sr_severity_validate(p_severity_id IN NUMBER) IS
1420 SELECT csv.incident_severity_id
1421 FROM cs_incident_severities_vl csv,
1422 mfg_lookups mfl
1423 WHERE csv.incident_severity_id = p_severity_id
1424 AND mfl.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
1425 AND trunc(sysdate) between trunc(nvl(csv.start_date_active,sysdate))
1426 AND trunc(nvl(csv.end_date_active,sysdate));
1427
1428 CURSOR default_contact IS
1429 SELECT employee_id
1430 FROM fnd_user
1431 WHERE user_id = fnd_global.user_id;
1432
1433 CURSOR default_customer_type IS
1434 SELECT party_type
1435 FROM hz_parties
1436 WHERE party_id = fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME');
1437
1438 -- Changes made by by VSUNDARA For SR Integration
1439 CURSOR default_party_id(p_item_instance_number IN NUMBER) IS
1440 SELECT OWNER_PARTY_ID
1441 FROM csi_item_instances
1442 WHERE instance_id = p_item_instance_number;
1443
1444 CURSOR owner_customer_type(p_cust_id IN NUMBER) IS
1445 SELECT party_type
1446 FROM hz_parties
1447 WHERE party_id = p_cust_id;
1448
1449 -- STHILAK PIE Integration
1450 CURSOR sr_service_type_code (p_service_type IN VARCHAR2) IS
1451 SELECT LOOKUP_CODE
1452 FROM pa_lookups
1453 WHERE upper(MEANING) = p_service_type
1454 AND LOOKUP_TYPE = 'SERVICE TYPE'
1455 AND ENABLED_FLAG = 'Y'
1456 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE - 1))
1457 AND TRUNC(NVL(END_DATE_ACTIVE, SYSDATE));
1458
1459 -- End Changes
1460
1461 -- End Changes
1462
1463 --amsriniv ER 6014567 Begin
1464 CURSOR check_inst_nonserial(c_instance_id IN NUMBER, c_workorder_id IN NUMBER) IS
1465 SELECT 'X'
1466 FROM mtl_system_items_b mtl, csi_item_instances csi
1467 WHERE csi.instance_id = c_instance_id
1468 AND csi.inventory_item_id = mtl.inventory_item_id
1469 AND mtl.organization_id = (SELECT organization_id from wip_discrete_jobs wdj, ahl_workorders awo where wdj.wip_entity_id = awo.wip_entity_id and awo.workorder_id = c_workorder_id)
1470 AND mtl.serial_number_control_code = 1;
1471
1472 CURSOR validate_quantity(c_instance_id IN NUMBER, c_wo_id IN NUMBER) IS
1473 SELECT csi.quantity
1474 FROM CSI_ITEM_INSTANCES csi,
1475 ahl_workorders wo
1476 WHERE csi.instance_id = c_instance_id
1477 AND csi.wip_job_id = WO.WIP_ENTITY_ID
1478 AND wo.workorder_id = c_wo_id
1479 AND csi.location_type_code = 'WIP'
1480 AND not exists (select 'x' from csi_ii_relationships
1481 where subject_id = csi.instance_id
1482 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1483 AND TRUNC(NVL(ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
1484 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE));
1485 --amsriniv ER 6014567 End
1486
1487 BEGIN
1488
1489 -- Initialize Procedure return status to success
1490 x_return_status := FND_API.G_RET_STS_SUCCESS;
1491
1492
1493 IF (upper(p_x_sr_task_rec.operation_type) = 'CREATE') THEN
1494
1495
1496 -- Check if instance id is not null. If instance id
1497 -- is null then return error message.
1498 IF (p_x_sr_task_rec.instance_id is null or p_x_sr_task_rec.instance_id = FND_API.G_MISS_NUM) THEN
1499
1500 Fnd_Message.SET_NAME('AHL','AHL_PRD_INSTANCE_VALUE_REQ');
1501 Fnd_Msg_Pub.ADD;
1502 RAISE FND_API.G_EXC_ERROR;
1503
1504 END IF;
1505
1506 -- If originating work order id is null then
1507 -- return an error message.
1508 IF (p_x_sr_task_rec.originating_wo_id is null or p_x_sr_task_rec.originating_wo_id = FND_API.G_MISS_NUM) THEN
1509
1510 Fnd_Message.SET_NAME('AHL','AHL_PRD_TASK_ORG_WOID_REQ');
1511 Fnd_Msg_Pub.ADD;
1512 RAISE FND_API.G_EXC_ERROR;
1513
1514 END IF;
1515
1516 -- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
1517 -- Bug # 6447467 -- start
1518 -- Check if resolution_meaning is not null. If resolution_meaning
1519 -- is null then return error message.
1520
1521 IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
1522
1523 IF ( p_x_sr_task_rec.resolution_meaning IS NULL OR
1524 p_x_sr_task_rec.resolution_meaning = FND_API.G_MISS_CHAR) THEN
1525
1526 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_REQ');
1527 Fnd_Msg_Pub.ADD;
1528 RAISE FND_API.G_EXC_ERROR;
1529
1530 END IF;
1531 END IF;
1532 -- Bug # 6447467 -- End
1533
1534 -- Get message tokens
1535 --
1536 get_msg_token ( p_wo_id => p_x_sr_task_rec.originating_wo_id,
1537 p_instance_id => p_x_sr_task_rec.instance_id,
1538 x_wo_name => l_wo_name,
1539 x_instance_number => l_instance_num);
1540
1541
1542 -- rroy
1543 -- ACL Changes
1544 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_sr_task_rec.originating_wo_id,
1545 p_ue_id => NULL,
1546 p_visit_id => NULL,
1547 p_item_instance_id => NULL);
1548 IF l_return_status = FND_API.G_TRUE THEN
1549 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_CRT_SR_UNTLCKD');
1550 FND_MESSAGE.Set_Token('WO_NAME', l_wo_name);
1551 FND_MSG_PUB.ADD;
1552 RAISE FND_API.G_EXC_ERROR;
1553 END IF;
1554 -- rroy
1555 -- ACL Changes
1556
1557 -- If type id is null then derive it
1558 -- from profile. If profile value is null then
1559 -- return an error message
1560
1561 IF ((p_x_sr_task_rec.type_id is null or p_x_sr_task_rec.type_id = FND_API.G_MISS_NUM)and
1562 (p_x_sr_task_rec.type_name is null or p_x_sr_task_rec.type_name = FND_API.G_MISS_CHAR)) THEN
1563 IF (fnd_profile.value('AHL_PRD_SR_TYPE') is not null) THEN
1564 p_x_sr_task_rec.type_id := fnd_profile.value('AHL_PRD_SR_TYPE');
1565 p_x_sr_task_rec.type_name := fnd_profile.value('AHL_PRD_SR_TYPE');
1566 ELSE
1567 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_TYPE_REQ');
1568 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1569 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1570 Fnd_Msg_Pub.ADD;
1571 x_return_status := FND_API.G_RET_STS_ERROR;
1572 END IF;
1573
1574 END IF;
1575
1576
1577 -- If status is null then derive it from profile.
1578 -- If profile value is null then default it to OPEN.
1579 IF ((p_x_sr_task_rec.status_id is null or p_x_sr_task_rec.status_id = FND_API.G_MISS_NUM)and
1580 (p_x_sr_task_rec.status_name is null or p_x_sr_task_rec.status_name = FND_API.G_MISS_CHAR)) THEN
1581
1582 IF (fnd_profile.value('AHL_PRD_SR_STATUS') is not null) THEN
1583 p_x_sr_task_rec.status_id := fnd_profile.value('AHL_PRD_SR_STATUS');
1584 ELSE
1585 -- Removing cursor cs_sr_status as status_code is obsoleted as per the update from SR Team
1586 -- and we should use incident_status_id directly. - Balaji.
1587 /*
1588 OPEN cs_sr_status;
1589 IF( cs_sr_status%NOTFOUND) THEN
1590 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_STATUS_DEFAULT_ERR');
1591 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1592 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1593 Fnd_Msg_Pub.ADD;
1594 x_return_status := FND_API.G_RET_STS_ERROR;
1595 ELSE
1596 FETCH cs_sr_status INTO p_x_sr_task_rec.status_id;
1597 END IF;
1598
1599 CLOSE cs_sr_status;
1600 */
1601 p_x_sr_task_rec.status_id := G_SR_OPEN_STATUS_ID;
1602 END IF;
1603 END IF;
1604
1605 --MANESING::Supplier Warranty, 04-Oct-2010, cannot modify warranty contract when the SR is in Closed status
1606 IF (p_mr_asso_count = 0 and
1607 p_x_sr_task_rec.status_id = 2 and p_x_sr_task_rec.warranty_contract_id IS NOT null) THEN
1608 x_return_status := FND_API.G_RET_STS_ERROR;
1609 FND_MESSAGE.set_name('AHL', 'AHL_PRD_NR_WARR_CNTR_RO');
1610 FND_MSG_PUB.ADD;
1611 END IF;
1612
1613 -- If severity is null then derive it from profile.
1614 -- If profile value is null then return an error message.
1615 IF ((p_x_sr_task_rec.severity_id is null or p_x_sr_task_rec.severity_id = FND_API.G_MISS_NUM)and
1616 (p_x_sr_task_rec.severity_name is null or p_x_sr_task_rec.severity_name = FND_API.G_MISS_CHAR)) THEN
1617
1618 IF (fnd_profile.value('AHL_PRD_SR_SEVERITY') is not null) THEN
1619 p_x_sr_task_rec.severity_id := fnd_profile.value('AHL_PRD_SR_SEVERITY');
1620
1621 -- Validate the severity value
1622 OPEN cs_sr_severity_validate(p_x_sr_task_rec.severity_id);
1623
1624 IF ( cs_sr_severity_validate%NOTFOUND) THEN
1625 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_SEVERITY_INVALID');
1626 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1627 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1628 Fnd_Msg_Pub.ADD;
1629 x_return_status := FND_API.G_RET_STS_ERROR;
1630 END IF;
1631
1632 CLOSE cs_sr_severity_validate;
1633 ELSE
1634 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_SEVERITY_REQ');
1635 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1636 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1637 Fnd_Msg_Pub.ADD;
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 END IF;
1640
1641 END IF;
1642
1643 -- Default incident date to sysdate if
1644 -- request date is null
1645 IF (p_x_sr_task_rec.request_date is null or
1646 p_x_sr_task_rec.request_date = FND_API.G_MISS_DATE) THEN
1647
1648 p_x_sr_task_rec.request_date := sysdate;
1649
1650 -- modified to default based on workorder scheduled start date to fix bug# 7697685 .
1651 IF ((nvl(UPPER(p_x_sr_task_rec.WO_Create_flag),'Y') = 'Y') AND
1652 (p_x_sr_task_rec.workorder_start_time IS NOT NULL AND
1653 p_x_sr_task_rec.workorder_start_time <> FND_API.G_MISS_DATE) AND
1654 p_x_sr_task_rec.workorder_start_time < sysdate ) THEN
1655 p_x_sr_task_rec.request_date := p_x_sr_task_rec.workorder_start_time;
1656 END IF;
1657
1658 END IF;
1659
1660
1661 -- If summary is null then return an
1662 -- error message.
1663 IF (p_x_sr_task_rec.summary is null or p_x_sr_task_rec.summary = FND_API.G_MISS_CHAR) THEN
1664
1665 Fnd_Message.SET_NAME('AHL','AHL_PRD_SUMMARY_REQ');
1666 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1667 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1668 Fnd_Msg_Pub.ADD;
1669 x_return_status := FND_API.G_RET_STS_ERROR;
1670
1671 END IF;
1672
1673
1674 -- If duration is null then derive it from profile.
1675 -- If profile value is null then default it to 1.
1676 --apattark changed for ER 9368251 to pass 0 if the duration is null
1677 IF (p_x_sr_task_rec.duration is null or p_x_sr_task_rec.duration = FND_API.G_MISS_NUM) THEN
1678 p_x_sr_task_rec.duration := 0;
1679 /*IF ( fnd_profile.value('AHL_PRD_TASK_EST_DURATION') is not null) THEN
1680 p_x_sr_task_rec.duration := fnd_profile.value('AHL_PRD_TASK_EST_DURATION');
1681 ELSE
1682 p_x_sr_task_rec.duration := 1;
1683 END IF;
1684 */
1685 END IF;
1686
1687 -- Changes made by VSUNDARA For SR Integration
1688 IF (p_x_sr_task_rec.customer_id IS NULL or p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM ) THEN
1689 OPEN default_party_id(p_x_sr_task_rec.instance_id);
1690 FETCH default_party_id INTO p_x_sr_task_rec.customer_id ;
1691 --Just to Check
1692 Select party_name
1693 into
1694 p_x_sr_task_rec.customer_name
1695 from hz_parties
1696 where party_id = p_x_sr_task_rec.customer_id;
1697 IF ( default_party_id%NOTFOUND) THEN
1698 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CUST_ERROR');
1699 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1700 Fnd_Message.SET_TOKEN('INSTANCE_NUM',null);
1701 Fnd_Msg_Pub.ADD;
1702 x_return_status := FND_API.G_RET_STS_ERROR;
1703 END IF;
1704 OPEN owner_customer_type(p_x_sr_task_rec.customer_id);
1705 FETCH owner_customer_type INTO p_x_sr_task_rec.customer_type;
1706 IF ( owner_customer_type%NOTFOUND) THEN
1707 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CUST_ERROR'); -- Customer Type is Invalid
1708 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1709 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1710 Fnd_Msg_Pub.ADD;
1711 x_return_status := FND_API.G_RET_STS_ERROR;
1712 END IF;
1713
1714 CLOSE owner_customer_type;
1715 -- Defalut contact is Same
1716 OPEN default_contact;
1717 FETCH default_contact INTO p_x_sr_task_rec.contact_id;
1718 IF ( default_contact%NOTFOUND ) THEN
1719 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CONT_ERROR');
1720 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1721 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1722 Fnd_Msg_Pub.ADD;
1723 x_return_status := FND_API.G_RET_STS_ERROR;
1724 ELSE
1725 p_x_sr_task_rec.contact_type := 'EMPLOYEE';
1726 END IF;
1727
1728 CLOSE default_contact;
1729
1730 ELSE
1731 --- Validation of the Customer ID with Owner of the Instance
1732 OPEN default_party_id(p_x_sr_task_rec.instance_id);
1733 FETCH default_party_id INTO l_owner_id ;
1734 IF( l_owner_id <> nvl(p_x_sr_task_rec.customer_id,l_owner_id) and
1735 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
1736 Fnd_Message.SET_NAME('AHL','AHL_PRD_INVALID_OWNER'); -- New Error Message Should be added
1737 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1738 Fnd_Msg_Pub.ADD;
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 END IF;
1741
1742 END IF;
1743
1744 --- End Changes
1745
1746 -- If Customer id and Contact id is null then
1747 -- Derive the Customer info from Profile and
1748 -- Contact info from fnd_user.user_id
1749 IF ((p_x_sr_task_rec.customer_id IS NULL or p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM ) and
1750 (p_x_sr_task_rec.contact_id IS NULL or p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)) THEN
1751 IF ( fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME') is not null ) THEN
1752
1753 OPEN default_customer_type;
1754 FETCH default_customer_type INTO p_x_sr_task_rec.customer_type;
1755
1756 IF ( default_customer_type%NOTFOUND) THEN
1757 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CUST_ERROR');
1758 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1759 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1760 Fnd_Msg_Pub.ADD;
1761 x_return_status := FND_API.G_RET_STS_ERROR;
1762 ELSE
1763 p_x_sr_task_rec.customer_id := fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME');
1764 END IF;
1765
1766 CLOSE default_customer_type;
1767
1768 OPEN default_contact;
1769 FETCH default_contact INTO p_x_sr_task_rec.contact_id;
1770 IF ( default_contact%NOTFOUND ) THEN
1771 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CONT_ERROR');
1772 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1773 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1774 Fnd_Msg_Pub.ADD;
1775 x_return_status := FND_API.G_RET_STS_ERROR;
1776 ELSE
1777 p_x_sr_task_rec.contact_type := 'EMPLOYEE';
1778 END IF;
1779
1780 CLOSE default_contact;
1781
1782 ELSE
1783
1784 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_PROFILE_REQ');
1785 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1786 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1787 Fnd_Msg_Pub.ADD;
1788 x_return_status := FND_API.G_RET_STS_ERROR;
1789
1790 END IF;
1791
1792 END IF;
1793
1794
1795 -- If Customer id is not null but Customer type is
1796 -- null then return an error message.
1797 IF (p_x_sr_task_rec.customer_id is not null and
1798 (p_x_sr_task_rec.customer_type is null or p_x_sr_task_rec.customer_type = FND_API.G_MISS_CHAR)) THEN
1799
1800 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_TYPE_REQ');
1801 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1802 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1803 Fnd_Msg_Pub.ADD;
1804 x_return_status := FND_API.G_RET_STS_ERROR;
1805
1806 END IF;
1807
1808
1809 -- If Contact id is not null but Contact type is
1810 -- null then return an error message.
1811 IF (p_x_sr_task_rec.contact_id is not null and
1812 (p_x_sr_task_rec.contact_type is null or p_x_sr_task_rec.contact_type = FND_API.G_MISS_CHAR)) THEN
1813
1814 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_TYPE_REQ');
1815 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1816 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1817 Fnd_Msg_Pub.ADD;
1818 x_return_status := FND_API.G_RET_STS_ERROR;
1819
1820 END IF;
1821
1822
1823 -- If Customer value is not null but Contact
1824 -- is null then return an error message.
1825 IF (p_x_sr_task_rec.customer_id is not null and
1826 (p_x_sr_task_rec.contact_id is null or p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)) THEN
1827
1828 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONTACT_REQ');
1829 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1830 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1831 Fnd_Msg_Pub.ADD;
1832 x_return_status := FND_API.G_RET_STS_ERROR;
1833
1834 -- If Contact is not null but Customer is null then
1835 -- return an error message.
1836 ELSIF((p_x_sr_task_rec.customer_id is null or p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM) and
1837 p_x_sr_task_rec.contact_id is not null ) THEN
1838
1839 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUSTOMER_REQ');
1840 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1841 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1842 Fnd_Msg_Pub.ADD;
1843 x_return_status := FND_API.G_RET_STS_ERROR;
1844
1845 END IF;
1846
1847
1848 -- If visit id is null then return an error
1849 -- message.
1850 IF (p_x_sr_task_rec.visit_id is null or p_x_sr_task_rec.visit_id = FND_API.G_MISS_NUM) THEN
1851
1852 Fnd_Message.SET_NAME('AHL','AHL_PRD_VISIT_VALUE_REQ');
1853 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1854 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1855 Fnd_Msg_Pub.ADD;
1856 x_return_status := FND_API.G_RET_STS_ERROR;
1857
1858 END IF;
1859
1860 -- STHILAK PIE Integration
1861 --If Service Type is passed then derive the Service Type Code.
1862 -- If Service Type Code does not exists for Service Type, throw error.
1863
1864 IF(p_x_sr_task_rec.service_type is not null) THEN
1865 BEGIN
1866
1867 OPEN sr_service_type_code(p_x_sr_task_rec.service_type);
1868 FETCH sr_service_type_code INTO p_x_sr_task_rec.service_type_code;
1869 CLOSE sr_service_type_code;
1870
1871 EXCEPTION
1872 WHEN NO_DATA_FOUND THEN
1873 Fnd_Message.SET_NAME('AHL','AHL_PRD_SERVICE_TYPE_INVALID');
1874 -- Fnd_Message.SET_TOKEN('SERVICE_TYPE',p_x_sr_task_rec.service_type);
1875 Fnd_Msg_Pub.ADD;
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 END;
1878 ELSE
1879 p_x_sr_task_rec.service_type_code := NULL;
1880 END IF;
1881
1882 --amsriniv. Issue 105. Begin ER 6014567
1883 IF (p_x_sr_task_rec.instance_quantity IS NOT NULL AND p_x_sr_task_rec.instance_quantity <= 0) THEN
1884 Fnd_Message.SET_NAME('AHL','AHL_POSITIVE_TSK_QTY');
1885 Fnd_Msg_Pub.ADD;
1886 x_return_status := FND_API.G_RET_STS_ERROR;
1887 END IF;
1888
1889 IF (upper(p_x_sr_task_rec.WO_Create_flag) = 'N') THEN
1890 OPEN check_inst_nonserial(p_x_sr_task_rec.Instance_id, p_x_sr_task_rec.originating_wo_id);
1891 FETCH check_inst_nonserial INTO l_dummy;
1892 IF (check_inst_nonserial%FOUND) THEN
1893 Fnd_Message.SET_NAME('AHL','AHL_NO_CREATE_WO_NONSER');
1894 Fnd_Message.SET_TOKEN('FIELD',l_instance_num);
1895 Fnd_Msg_Pub.ADD;
1896 x_return_status := FND_API.G_RET_STS_ERROR;
1897 END IF;
1898 CLOSE check_inst_nonserial;
1899 END IF;
1900
1901 IF (nvl(upper(p_x_sr_task_rec.move_qty_to_nr_workorder),'N') = 'Y') THEN
1902 IF (upper(p_x_sr_task_rec.WO_Create_flag) = 'Y' and upper(p_x_sr_task_rec.WO_Release_flag) = 'Y') THEN
1903 OPEN validate_quantity(p_x_sr_task_rec.Instance_id , p_x_sr_task_rec.originating_wo_id);
1904 FETCH validate_quantity INTO l_quantity;
1905 IF (validate_quantity%NOTFOUND) THEN
1906 Fnd_Message.SET_NAME('AHL','AHL_INST_NOT_ISSUED');
1907 Fnd_Message.SET_TOKEN('INS_NUM',l_instance_num);
1908 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1909 Fnd_Msg_Pub.ADD;
1910 x_return_status := FND_API.G_RET_STS_ERROR;
1911 ELSIF (l_quantity < p_x_sr_task_rec.instance_quantity) THEN
1912 Fnd_Message.SET_NAME('AHL','AHL_INST_NOT_AVAIL');
1913 Fnd_Message.SET_TOKEN('INS_NAME',l_instance_num);
1914 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1915 Fnd_Message.SET_TOKEN('QUANT_USER',TO_CHAR(p_x_sr_task_rec.instance_quantity));
1916 Fnd_Message.SET_TOKEN('QUANT_AVAIL',TO_CHAR(l_quantity));
1917 Fnd_Msg_Pub.ADD;
1918 x_return_status := FND_API.G_RET_STS_ERROR;
1919 END IF;
1920 ELSE
1921 Fnd_Message.SET_NAME('AHL','AHL_NR_WO_NOT_RELEASED');
1922 Fnd_Msg_Pub.ADD;
1923 x_return_status := FND_API.G_RET_STS_ERROR;
1924 END IF;
1925 END IF;
1926 --amsriniv. Issue 105. End ER 6014567
1927
1928 ELSIF(upper(p_x_sr_task_rec.operation_type) = 'UPDATE') THEN
1929
1930
1931 -- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
1932 -- Bug # 6447467 -- start
1933 -- Check if resolution_meaning is not null. If resolution_meaning
1934 -- is null then return error message.
1935
1936 IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
1937
1938 IF ( p_x_sr_task_rec.resolution_meaning IS NULL OR
1939 p_x_sr_task_rec.resolution_meaning = FND_API.G_MISS_CHAR) THEN
1940
1941 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_REQ');
1942 Fnd_Msg_Pub.ADD;
1943 RAISE FND_API.G_EXC_ERROR;
1944
1945 END IF;
1946 END IF;
1947 -- Bug # 6447467 -- end
1948
1949 -- Get message tokens
1950 --
1951 get_msg_token ( p_wo_id => p_x_sr_task_rec.originating_wo_id,
1952 p_instance_id => p_x_sr_task_rec.instance_id,
1953 x_wo_name => l_wo_name,
1954 x_instance_number => l_instance_num);
1955
1956 -- rroy
1957 -- ACL Changes
1958 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_sr_task_rec.originating_wo_id,
1959 p_ue_id => NULL,
1960 p_visit_id => NULL,
1961 p_item_instance_id => NULL);
1962 IF l_return_status = FND_API.G_TRUE THEN
1963 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_UPD_SR_UNTLCKD');
1964 FND_MESSAGE.Set_Token('WO_NAME', l_wo_name);
1965 FND_MSG_PUB.ADD;
1966 RAISE FND_API.G_EXC_ERROR;
1967 END IF;
1968 -- rroy
1969 -- ACL Changes
1970
1971 IF p_module_type IS NULL OR p_module_type <> 'SR_OA' THEN
1972
1973 -- If contact id is null then return an
1974 -- error message.
1975 IF (p_x_sr_task_rec.contact_id is null or p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM) THEN
1976
1977 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONTACT_REQ');
1978 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1979 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1980 Fnd_Msg_Pub.ADD;
1981 x_return_status := FND_API.G_RET_STS_ERROR;
1982
1983 END IF;
1984
1985
1986 -- If Contact type is null then return an error
1987 -- message.
1988 IF (p_x_sr_task_rec.contact_id is not null and
1989 (p_x_sr_task_rec.contact_type is null or p_x_sr_task_rec.contact_type = FND_API.G_MISS_CHAR)) THEN
1990
1991 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_TYPE_REQ');
1992 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1993 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1994 Fnd_Msg_Pub.ADD;
1995 x_return_status := FND_API.G_RET_STS_ERROR;
1996
1997 END IF;
1998
1999
2000 -- If status is null then return an
2001 -- error message.
2002 IF ((p_x_sr_task_rec.status_id is null or p_x_sr_task_rec.status_id = FND_API.G_MISS_NUM)and
2003 (p_x_sr_task_rec.status_name is null or p_x_sr_task_rec.status_name = FND_API.G_MISS_CHAR)) THEN
2004
2005 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_STATUS_REQ');
2006 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
2007 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
2008 Fnd_Msg_Pub.ADD;
2009 x_return_status := FND_API.G_RET_STS_ERROR;
2010
2011 END IF;
2012
2013 END IF;
2014
2015 -- If object version number is null then
2016 -- return an error message.
2017 IF (p_x_sr_task_rec.incident_object_version_number is null or
2018 p_x_sr_task_rec.incident_object_version_number = FND_API.G_MISS_NUM) THEN
2019
2020 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_OBJ_VER_ID_REQ');
2021 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
2022 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
2023 Fnd_Msg_Pub.ADD;
2024 x_return_status := FND_API.G_RET_STS_ERROR;
2025
2026 END IF;
2027
2028 -- If Incident number and incident id is null then
2029 -- return an error message.
2030 IF (p_x_sr_task_rec.incident_number is null or
2031 p_x_sr_task_rec.incident_number = FND_API.G_MISS_CHAR) and
2032 (p_x_sr_task_rec.incident_id is null or
2033 p_x_sr_task_rec.incident_id = FND_API.G_MISS_NUM)THEN
2034
2035 Fnd_Message.SET_NAME('AHL','AHL_PRD_INCIDENT_VALUE_REQ');
2036 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
2037 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
2038 Fnd_Msg_Pub.ADD;
2039 x_return_status := FND_API.G_RET_STS_ERROR;
2040 END IF;
2041
2042 END IF;
2043
2044 IF (p_x_sr_task_rec.source_program_code is null or
2045 p_x_sr_task_rec.source_program_code = FND_API.G_MISS_CHAR) then
2046 p_x_sr_task_rec.source_program_code := 'AHL_ROUTINE';
2047 END IF;
2048
2049 EXCEPTION
2050 WHEN FND_API.G_EXC_ERROR THEN
2051 x_return_status := FND_API.G_RET_STS_ERROR;
2052 END Default_and_validate_param;
2053
2054
2055 --------------------------------------------
2056 -- Create Service Request
2057 --------------------------------------------
2058
2059 ----------------------------------------------
2060 -- Create_sr procedure assigns the values to
2061 -- service request record and calls the
2062 -- Create service request public api.
2063 ----------------------------------------------
2064
2065 PROCEDURE Create_sr(
2066 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
2067 x_return_status OUT NOCOPY VARCHAR2
2068 )IS
2069
2070 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
2071 l_notes_table CS_ServiceRequest_PUB.notes_table;
2072 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
2073 l_contact_primary_flag CONSTANT VARCHAR2(1) := 'Y';
2074 l_auto_assign CONSTANT VARCHAR2(1) := 'N';
2075
2076 l_msg_count NUMBER;
2077 l_msg_data VARCHAR2(2000);
2078 l_inventory_item_id NUMBER;
2079 l_serial_number VARCHAR2(30);
2080 l_inv_master_org_id NUMBER;
2081 l_note VARCHAR2(2000);
2082 l_note_detail VARCHAR2(2000);
2083
2084 l_individual_owner NUMBER;
2085 l_group_owner NUMBER;
2086 l_individual_type VARCHAR2(30);
2087 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_SR';
2088
2089 -- Begin Changes by VSUNDARA for SR Integration
2090 CURSOR default_item_org_id(p_workorder_id IN NUMBER) IS
2091 SELECT A.inventory_item_id,
2092 A.item_organization_id
2093 FROM AHL_VISIT_TASKS_B A,
2094 AHL_WORKORDERS B
2095 WHERE A.visit_task_id = B.visit_task_id
2096 AND B.workorder_id = p_workorder_id;
2097
2098 CURSOR default_incident_type_id is
2099 SELECT INCIDENT_TYPE_ID,NAME
2100 FROM cs_incident_types_vl
2101 where INCIDENT_SUBTYPE = 'INC'
2102 AND CMRO_FLAG = 'Y'
2103 -- Check added by balaji for bug # 4146503.
2104 -- always has to pick up the SR type id from AHL default SR Type profile.
2105 AND incident_type_id=fnd_profile.value('AHL_PRD_SR_TYPE')
2106 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
2107 AND trunc(nvl(end_date_active,sysdate));
2108 -- END Changes
2109
2110 -- added to fix bug# 8265902
2111 CURSOR get_inc_type_id (p_name IN VARCHAR2) IS
2112 SELECT INCIDENT_TYPE_ID
2113 FROM cs_incident_types_vl
2114 where INCIDENT_SUBTYPE = 'INC'
2115 AND CMRO_FLAG = 'Y'
2116 AND NAME = p_name
2117 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
2118 AND trunc(nvl(end_date_active,sysdate));
2119
2120 l_default_sr_flag BOOLEAN;
2121
2122 BEGIN
2123
2124 -- Initialize the SR record.
2125 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
2126
2127 get_note_value(p_sr_task_rec => p_x_sr_task_rec,
2128 x_note => l_note,
2129 x_note_detail => l_note_detail);
2130
2131 -- Assign the SR rec values
2132 l_service_request_rec.request_date := p_x_sr_task_rec.request_date;
2133 l_service_request_rec.status_id := p_x_sr_task_rec.status_id;
2134 l_service_request_rec.status_name := p_x_sr_task_rec.status_name;
2135 l_service_request_rec.severity_id := p_x_sr_task_rec.severity_id;
2136 l_service_request_rec.severity_name := p_x_sr_task_rec.severity_name;
2137 l_service_request_rec.urgency_id := p_x_sr_task_rec.urgency_id;
2138 l_service_request_rec.urgency_name := p_x_sr_task_rec.urgency_name;
2139 l_service_request_rec.summary := p_x_sr_task_rec.summary;
2140 l_service_request_rec.caller_type := p_x_sr_task_rec.customer_type;
2141 l_service_request_rec.customer_id := p_x_sr_task_rec.customer_id;
2142 l_service_request_rec.problem_code := p_x_sr_task_rec.problem_code;
2143 l_service_request_rec.resolution_code := p_x_sr_task_rec.resolution_code;
2144 l_service_request_rec.creation_program_code := p_x_sr_task_rec.source_program_code;
2145
2146 -- MANESING::DFF Project, 16-Feb-2010, assigned attributes to local record for Creating Service Request
2147 l_service_request_rec.request_context := p_x_sr_task_rec.attribute_category;
2148 l_service_request_rec.request_attribute_1 := p_x_sr_task_rec.attribute1;
2149 l_service_request_rec.request_attribute_2 := p_x_sr_task_rec.attribute2;
2150 l_service_request_rec.request_attribute_3 := p_x_sr_task_rec.attribute3;
2151 l_service_request_rec.request_attribute_4 := p_x_sr_task_rec.attribute4;
2152 l_service_request_rec.request_attribute_5 := p_x_sr_task_rec.attribute5;
2153 l_service_request_rec.request_attribute_6 := p_x_sr_task_rec.attribute6;
2154 l_service_request_rec.request_attribute_7 := p_x_sr_task_rec.attribute7;
2155 l_service_request_rec.request_attribute_8 := p_x_sr_task_rec.attribute8;
2156 l_service_request_rec.request_attribute_9 := p_x_sr_task_rec.attribute9;
2157 l_service_request_rec.request_attribute_10 := p_x_sr_task_rec.attribute10;
2158 l_service_request_rec.request_attribute_11 := p_x_sr_task_rec.attribute11;
2159 l_service_request_rec.request_attribute_12 := p_x_sr_task_rec.attribute12;
2160 l_service_request_rec.request_attribute_13 := p_x_sr_task_rec.attribute13;
2161 l_service_request_rec.request_attribute_14 := p_x_sr_task_rec.attribute14;
2162 l_service_request_rec.request_attribute_15 := p_x_sr_task_rec.attribute15;
2163
2164 -- bug# 5450359. Default incident date.
2165 l_service_request_rec.incident_occurred_date := l_service_request_rec.request_date;
2166
2167 l_service_request_rec.customer_product_id := p_x_sr_task_rec.instance_id;
2168 open default_item_org_id(p_x_sr_task_rec.Originating_wo_id);
2169 Fetch default_item_org_id INTO l_service_request_rec.inventory_item_id,l_service_request_rec.inventory_org_id;
2170 IF (default_item_org_id%NOTFOUND ) THEN
2171 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_ORG_ERROR');
2172 Fnd_Msg_Pub.ADD;
2173 x_return_status := FND_API.G_RET_STS_ERROR;
2174 END IF;
2175 CLOSE default_item_org_id;
2176
2177 -- By default set to true. If type_name is valid, then reset flag to FALSE
2178 l_default_sr_flag := TRUE;
2179
2180 -- if type_name is not null, validate it(fix for bug# 8265902)
2181 IF (p_x_sr_task_rec.type_name IS NOT NULL AND p_x_sr_task_rec.type_name <> FND_API.G_MISS_CHAR) THEN
2182 OPEN get_inc_type_id(p_x_sr_task_rec.type_name);
2183 FETCH get_inc_type_id INTO l_service_request_rec.type_id;
2184 IF (get_inc_type_id%FOUND) THEN
2185 l_service_request_rec.type_name := p_x_sr_task_rec.type_name;
2186 l_default_sr_flag := FALSE;
2187 IF (G_DEBUG = 'Y') THEN
2188 AHL_DEBUG_PUB.debug('Input SR type:ID:' || l_service_request_rec.type_name || ':' || l_service_request_rec.type_id );
2189 END IF;
2190 END IF;
2191 CLOSE get_inc_type_id;
2192 END IF;
2193
2194 IF (l_default_sr_flag) THEN
2195 -- default
2196 open default_incident_type_id;
2197 Fetch default_incident_type_id INTO l_service_request_rec.type_id,l_service_request_rec.type_name;
2198
2199 IF ( default_incident_type_id%NOTFOUND) THEN
2200 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_INCIDENT_ERROR');
2201 Fnd_Msg_Pub.ADD;
2202 x_return_status := FND_API.G_RET_STS_ERROR;
2203 END IF;
2204
2205 IF (G_DEBUG = 'Y') THEN
2206 AHL_DEBUG_PUB.debug('Defaulting SR type:ID:' || l_service_request_rec.type_name || ':' || l_service_request_rec.type_id );
2207 END IF;
2208
2209 CLOSE default_incident_type_id;
2210 END IF;
2211
2212 --- End Changes by VSUNDARA for SR Integration
2213 -- Contacts
2214 l_contacts_table(1).party_id := p_x_sr_task_rec.contact_id;
2215 l_contacts_table(1).contact_type := p_x_sr_task_rec.contact_type;
2216 l_contacts_table(1).primary_flag := l_contact_primary_flag;
2217
2218 -- Notes
2219 /*
2220 l_notes_table(1).note := l_note;
2221 l_notes_table(1).note_detail := l_note_detail;
2222 l_notes_table(1).note_type := 'CS_PROBLEM';
2223 l_notes_table(1).note_context_type_01 := 'SR';
2224
2225 -- Call write to log procedure to log the input parameter
2226 -- values for debug
2227
2228 IF (G_DEBUG = 'Y') THEN
2229 AHL_DEBUG_PUB.debug('Inputs for CS_SERVICEREQUEST_PUB.Create_ServiceRequest:');
2230 write_sr_to_log(
2231 p_service_request_rec => l_service_request_rec,
2232 p_notes_table => l_notes_table,
2233 p_contacts_table => l_contacts_table
2234 );
2235 END IF;
2236 */
2237 -- Call to Service Request API
2238
2239 CS_SERVICEREQUEST_PUB.Create_ServiceRequest(
2240 p_api_version => 3.0,
2241 p_init_msg_list => FND_API.G_TRUE,
2242 p_commit => FND_API.G_FALSE,
2243 x_return_status => x_return_status,
2244 x_msg_count => l_msg_count,
2245 x_msg_data => l_msg_data,
2246 p_resp_appl_id => NULL,
2247 p_resp_id => NULL,
2248 p_user_id => fnd_global.user_id,
2249 p_login_id => fnd_global.conc_login_id,
2250 p_org_id => NULL,
2251 p_request_id => NULL,
2252 p_request_number => NULL,
2253 p_service_request_rec => l_service_request_rec,
2254 p_notes => l_notes_table,
2255 p_contacts => l_contacts_table,
2256 p_auto_assign => l_auto_assign,
2257 x_request_id => p_x_sr_task_rec.incident_id,
2258 x_request_number => p_x_sr_task_rec.incident_number,
2259 x_interaction_id => p_x_sr_task_rec.interaction_id,
2260 x_workflow_process_id => p_x_sr_task_rec.workflow_process_id,
2261 x_individual_owner => l_individual_owner,
2262 x_group_owner => l_individual_owner,
2263 x_individual_type => l_individual_type
2264 );
2265
2266 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2267 RAISE FND_API.G_EXC_ERROR;
2268 END IF;
2269
2270 ---Changes by VSUNDARA FOR TRANSIT CHECK
2271
2272 -- Tamal [MEL/CDL PRD Integration] Begins here...
2273 -- After creating the SR on the instance, need to populate unit_config_id for the newly created UE
2274 update ahl_unit_effectivities_b
2275 set unit_config_header_id = AHL_UTIL_UC_PKG.get_uc_header_id(p_x_sr_task_rec.instance_id),
2276 -- VLAKKU ER # 13787940
2277 qa_inspection_type = p_x_sr_task_rec.quality_inspection_type_code
2278 where unit_effectivity_id in
2279 (
2280 select unit_effectivity_id
2281 from ahl_unit_effectivities_b
2282 where object_type = 'SR' and cs_incident_id = p_x_sr_task_rec.incident_id
2283 );
2284 -- Tamal [MEL/CDL PRD Integration] Ends here...
2285
2286 -- MR NR ER -- start
2287
2288 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2289 fnd_log.string(
2290 fnd_log.level_statement,
2291 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2292 'Before Updating Unit Effectivity with Originating WO detail..'
2293 );
2294 fnd_log.string(
2295 fnd_log.level_statement,
2296 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2297 'p_x_sr_task_rec.Originating_wo_id->'||p_x_sr_task_rec.Originating_wo_id||' , '
2298 ||'p_x_sr_task_rec.incident_id->'||p_x_sr_task_rec.incident_id
2299 );
2300 END IF;
2301
2302 -- update ump table with originating wo id in AHL_UNIT_EFFECTIVITIES_B.ORIGINATING_WO_ID
2303 IF
2304 (
2305 p_x_sr_task_rec.incident_id IS NOT NULL
2306 AND
2307 p_x_sr_task_rec.Originating_wo_id IS NOT NULL
2308 )
2309 THEN
2310
2311 BEGIN
2312
2313 UPDATE AHL_UNIT_EFFECTIVITIES_B
2314 SET ORIGINATING_WO_ID = p_x_sr_task_rec.Originating_wo_id
2315 WHERE CS_INCIDENT_ID = p_x_sr_task_rec.incident_id;
2316
2317 EXCEPTION
2318
2319 WHEN OTHERS THEN
2320 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_ORIGINWO_UPD_FAILED');
2321 Fnd_Msg_Pub.ADD;
2322 x_return_status := FND_API.G_RET_STS_ERROR;
2323
2324 END;
2325
2326 END IF;
2327
2328 -- MR NR ER -- end
2329
2330 END Create_sr;
2331
2332 ------------------------
2333 -- MANESING::Supplier Warranty, 04-Oct-2010, added following local procedure.
2334 -- Start of Comments --
2335 -- Procedure name : Update_warranty_entitlements
2336 -- Type : Private
2337 -- Function : Update warranty entitlement records (records are created when visit tasks are created)
2338 -- while Creating non routines if following conditions are met:
2339 -- 1. No MR's are attached to the non routine
2340 -- 2. Non routine is not in Closed status
2341 -- 3. Instance warranty is Yes
2342 -- Pre-reqs :
2343 -- Parameters :
2344 --
2345 -- Update_warranty_entitlements Parameters:
2346 -- p_mr_asso_count IN NUMBER Required
2347 -- p_x_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.sr_task_rec_type Required
2348 --
2349 -- End of Comments.
2350 PROCEDURE Update_warranty_entitlements
2351 (
2352 p_api_version IN NUMBER,
2353 p_init_msg_list IN VARCHAR2,
2354 p_commit IN VARCHAR2,
2355 p_validation_level IN NUMBER,
2356 p_module_type IN VARCHAR2,
2357 p_mr_asso_count IN NUMBER,
2358 p_x_sr_task_tbl IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type,
2359 x_return_status OUT NOCOPY VARCHAR2,
2360 x_msg_count OUT NOCOPY NUMBER,
2361 x_msg_data OUT NOCOPY VARCHAR2
2362 )
2363 IS
2364 --
2365 CURSOR Get_warranty_entl_rec_csr (c_sr_incident_id NUMBER)
2366 IS
2367 SELECT warranty_entitlement_id, object_version_number, visit_task_id
2368 FROM AHL_WARRANTY_ENTITLEMENTS
2369 WHERE sr_incident_id = c_sr_incident_id;
2370 --
2371 CURSOR Get_contract_id_csr (c_originating_wo_id NUMBER)
2372 IS
2373 SELECT warranty_contract_id
2374 FROM AHL_WARRANTY_ENTITLEMENTS
2375 WHERE visit_task_id = (SELECT visit_task_id
2376 FROM AHL_WORKORDERS
2377 WHERE workorder_id = c_originating_wo_id);
2378 --
2379 CURSOR Get_workorder_id_csr (c_visit_task_id NUMBER)
2380 IS
2381 SELECT workorder_id
2382 FROM AHL_WORKORDERS
2383 WHERE visit_task_id = c_visit_task_id;
2384 --
2385 l_api_name CONSTANT VARCHAR2(30) := 'Update_warranty_entitlements';
2386 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2387 l_tranit_tech_resp_id CONSTANT NUMBER := 64156;
2388 l_tech_resp_id CONSTANT NUMBER := 64157;
2389 l_data_clerk_resp_id CONSTANT NUMBER := 64158;
2390 l_log_procedure NUMBER := FND_LOG.level_procedure;
2391 l_log_statement NUMBER := FND_LOG.level_statement;
2392 l_log_current_level NUMBER := FND_LOG.g_current_runtime_level;
2393
2394 l_rec_count NUMBER := 0;
2395 l_user_role VARCHAR2(5);
2396 l_warranty_entl_id NUMBER;
2397 l_object_version_number NUMBER;
2398 l_visit_task_id NUMBER;
2399 l_workorder_id NUMBER;
2400 l_sr_task_rec ahl_prd_nonroutine_pvt.sr_task_rec_type;
2401 l_warranty_entl_tbl ahl_warranty_entl_pvt.warranty_entl_tbl_type;
2402 --
2403 BEGIN
2404
2405 IF (l_log_procedure >= l_log_current_level) THEN
2406 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'Entering Procedure');
2407 END IF;
2408
2409 -- condition 1 (conditions are given in Procedure Function above)
2410 IF (p_mr_asso_count = 0) THEN
2411 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
2412
2413 l_sr_task_rec := p_x_sr_task_tbl(i);
2414
2415 -- condition 2
2416 IF (upper(l_sr_task_rec.operation_type) = 'CREATE' and l_sr_task_rec.status_id <> 2) THEN
2417 -- condition 3
2418 IF (ahl_warranty_contracts_pvt.is_instance_warranty_available(l_sr_task_rec.instance_id) = 'Y') THEN
2419
2420 OPEN Get_warranty_entl_rec_csr(l_sr_task_rec.incident_id);
2421 FETCH Get_warranty_entl_rec_csr INTO l_warranty_entl_id, l_object_version_number, l_visit_task_id;
2422
2423 IF (Get_warranty_entl_rec_csr%NOTFOUND) THEN
2424 -- invalid sr incident id
2425 IF (l_log_statement >= l_log_current_level) THEN
2426 FND_LOG.string(l_log_statement, l_full_name, 'invalid SR id for updating warranty entitlement record');
2427 END IF;
2428
2429 x_return_status := FND_API.G_RET_STS_ERROR;
2430 FND_MESSAGE.set_name('AHL', 'AHL_UMP_INVALID_INCIDENT_ID');
2431 FND_MESSAGE.set_token('INCIDENT_ID', l_sr_task_rec.incident_id);
2432 FND_MSG_PUB.ADD;
2433 RETURN;
2434 END IF;
2435 CLOSE Get_warranty_entl_rec_csr;
2436
2437 l_warranty_entl_tbl(l_rec_count).warranty_entitlement_id := l_warranty_entl_id;
2438 l_warranty_entl_tbl(l_rec_count).object_version_number := l_object_version_number;
2439 l_warranty_entl_tbl(l_rec_count).operation_flag := ahl_warranty_entl_pvt.G_OP_UPDATE;
2440 l_warranty_entl_tbl(l_rec_count).warranty_contract_id := l_sr_task_rec.warranty_contract_id;
2441
2442 -- for Part Changes and Create Disposition UIs (module_type is null, since API is called internally),
2443 -- warranty contract id for Non Routine will be derived from the Originating work order
2444 IF (p_module_type IS null) THEN
2445
2446 OPEN Get_contract_id_csr (l_sr_task_rec.Originating_wo_id);
2447 FETCH Get_contract_id_csr INTO l_warranty_entl_tbl(l_rec_count).warranty_contract_id;
2448 CLOSE Get_contract_id_csr;
2449
2450 END IF;
2451
2452 -- for planner
2453 IF (fnd_global.resp_id <> l_tranit_tech_resp_id and
2454 fnd_global.resp_id <> l_tech_resp_id and
2455 fnd_global.resp_id <> l_data_clerk_resp_id) THEN
2456
2457 l_user_role := ahl_warranty_entl_pvt.G_USER_PLANNER;
2458 IF (l_warranty_entl_tbl(l_rec_count).warranty_contract_id IS NOT null) THEN
2459 l_warranty_entl_tbl(l_rec_count).entitlement_status_code := 'APPROVED';
2460 ELSE
2461 l_warranty_entl_tbl(l_rec_count).entitlement_status_code := 'NOT_APPLICABLE';
2462 END IF;
2463
2464 -- for Transit Technician, Technician and Data Clerk
2465 ELSE
2466 l_user_role := ahl_warranty_entl_pvt.G_USER_NONPLANNER;
2467 l_warranty_entl_tbl(l_rec_count).entitlement_status_code := 'APPROVAL_PENDING';
2468 END IF;
2469
2470 l_warranty_entl_tbl(l_rec_count).sr_incident_id := l_sr_task_rec.incident_id;
2471 l_warranty_entl_tbl(l_rec_count).visit_task_id := l_visit_task_id;
2472
2473 OPEN Get_workorder_id_csr(l_visit_task_id);
2474 FETCH Get_workorder_id_csr INTO l_workorder_id;
2475
2476 IF (Get_workorder_id_csr%NOTFOUND) THEN
2477 -- invalid visit task id
2478 IF (l_log_statement >= l_log_current_level) THEN
2479 FND_LOG.string(l_log_statement, l_full_name, 'invalid visit task id for updating warranty entitlement record');
2480 END IF;
2481
2482 x_return_status := FND_API.G_RET_STS_ERROR;
2483 FND_MESSAGE.set_name('AHL', 'AHL_LTP_TASK_ID_INVALID');
2484 FND_MESSAGE.set_token('TASK_ID', l_visit_task_id);
2485 FND_MSG_PUB.ADD;
2486 RETURN;
2487 END IF;
2488 CLOSE Get_workorder_id_csr;
2489
2490 l_warranty_entl_tbl(l_rec_count).workorder_id := l_workorder_id;
2491 l_rec_count := l_rec_count + 1;
2492 END IF;
2493 END IF;
2494 END LOOP;
2495 END IF;
2496
2497 IF (l_rec_count > 0) THEN
2498 -- call API to update warranty entitlement records
2499 AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements
2500 (
2501 p_api_version => p_api_version,
2502 p_init_msg_list => p_init_msg_list,
2503 p_commit => p_commit,
2504 p_validation_level => p_validation_level,
2505 p_module_type => p_module_type,
2506 p_user_role => l_user_role,
2507 p_appr_action => null, -- this flow does not have Approval functionality
2508 p_x_warranty_entl_tbl => l_warranty_entl_tbl,
2509 x_return_status => x_return_status,
2510 x_msg_count => x_msg_count,
2511 x_msg_data => x_msg_data
2512 );
2513 END IF;
2514
2515 IF (l_log_procedure >= l_log_current_level) THEN
2516 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'Exiting Procedure');
2517 END IF;
2518
2519 END Update_warranty_entitlements;
2520
2521 -- MR NR ER -- start
2522 -----------------------------------------------------------------------------------
2523 -- Balaji added this piece of code for OGMA ER # 6459697(Adding MRs to Non-Routine)
2524 -- This local procedure processes all MRs associated to a SR. Essentially does following
2525 -- 1. Creates and associates UE hierarchy for the MRs added to SR.
2526 -- 2. Creates Task hierarchy required in VWP
2527 -- 3. Releases the new tasks added in VWP to production.
2528 -----------------------------------------------------------------------------------
2529 PROCEDURE Process_Mr(
2530 p_x_task_tbl IN OUT NOCOPY sr_task_tbl_type,
2531 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
2532 p_module_type IN VARCHAR2,
2533 x_return_status OUT NOCOPY VARCHAR2,
2534 x_msg_count OUT NOCOPY NUMBER,
2535 x_msg_data OUT NOCOPY VARCHAR2
2536 )
2537 IS
2538
2539 -- declare all cursors here
2540 --*************************
2541
2542 --1. cursor for getting visit task id corresponding originating workorder
2543 cursor c_visit_task_csr(c_Nonroutine_wo_id IN NUMBER) IS
2544 SELECT
2545 WO.visit_task_id
2546 FROM
2547 AHL_WORKORDERS WO
2548 WHERE
2549 WO.workorder_id = c_Nonroutine_wo_id;
2550
2551 -- Added by jaramana on Oct 15
2552 CURSOR c_NR_wo_details(p_unit_effectivity_id IN NUMBER)
2553 IS
2554 SELECT
2555 awo.workorder_id
2556 FROM
2557 ahl_workorders awo,
2558 ahl_visit_tasks_b vtsk
2559 WHERE
2560 awo.visit_task_id = vtsk.visit_task_id
2561 AND awo.master_workorder_flag = 'Y'
2562 AND vtsk.task_type_code = 'SUMMARY'
2563 AND vtsk.mr_id is NULL
2564 AND vtsk.unit_effectivity_id = p_unit_effectivity_id;
2565
2566 -- 3. cursor for retrieving unit effectivity id corresponding to the SR created.
2567 CURSOR c_get_ue_id(p_incident_id NUMBER)
2568 IS
2569 Select unit_effectivity_id
2570 from AHL_UNIT_EFFECTIVITIES_B
2571 where cs_incident_id = p_incident_id;
2572
2573 CURSOR c_get_sr_details(p_incident_id NUMBER)
2574 IS
2575 SELECT object_version_number
2576 FROM CS_INCIDENTS
2577 WHERE incident_id = p_incident_id;
2578
2579 --STHILAK, PIE, cursor to fetch Service Type corresponding to the NR.
2580 CURSOR c_get_nr_service_type_code (ue_id NUMBER)
2581 IS
2582 Select service_type_code
2583 from ahl_visit_tasks_b
2584 where unit_effectivity_id = ue_id;
2585
2586 --amsriniv ER 6014567 Begin
2587 --5. cursor for retrieving the non master workorder id which is passed as to_workorder_id when calling move_intance_location
2588 CURSOR get_nonmaster_wo_id(p_nr_wo_id IN NUMBER)
2589 IS
2590 SELECT workorder_id
2591 FROM ahl_workorders
2592 WHERE MASTER_WORKORDER_FLAG = 'N'
2593 AND wip_entity_id in
2594 (SELECT rel.child_object_id
2595 FROM wip_sched_relationships rel START
2596 WITH REL.parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_nr_wo_id)
2597 CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
2598 AND REL.parent_object_type_id = PRIOR REL.child_object_type_id
2599 AND REL.relationship_type = 1
2600 )
2601 ORDER BY workorder_id;
2602 --amsriniv ER 6014567 End
2603
2604 --apattark added for ER #9368251
2605 CURSOR get_firm_planned_flag_csr(c_sr_id IN NUMBER) IS
2606 SELECT WIP.FIRM_PLANNED_FLAG
2607 FROM AHL_VISIT_TASKS_B VST,
2608 AHL_WORKORDERS WO,
2609 WIP_DISCRETE_JOBS WIP,
2610 AHL_UNIT_EFFECTIVITIES_B UE
2611 WHERE VST.SERVICE_REQUEST_ID = c_sr_id
2612 AND VST.TASK_TYPE_CODE IN ('SUMMARY')
2613 AND VST.VISIT_TASK_ID = WO.VISIT_TASK_ID
2614 AND WO.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
2615 AND VST.mr_id IS NULL
2616 AND UE.CS_INCIDENT_ID = c_sr_id
2617 AND UE.UNIT_EFFECTIVITY_ID= VST.UNIT_EFFECTIVITY_ID
2618 AND (UE.STATUS_CODE IS NULL OR UE.STATUS_CODE = 'INIT-DUE');
2619
2620 -- Kasridha: Changes for Bug#13739171 Begins
2621 -- Cursor to get the visit details
2622 CURSOR c_get_visit_dtls(p_visit_id_csr NUMBER) IS
2623 SELECT organization_id, status_code FROM ahl_visits_b WHERE
2624 visit_id = p_visit_id_csr;
2625 -- To find the repair batch task ID for a given instance
2626 CURSOR c_get_rprbatch_task (p_instance_id IN NUMBER, p_org_id NUMBER) IS
2627 SELECT visit_task_id FROM ahl_visit_tasks_b
2628 WHERE repair_batch_name = (AHL_CMP_UTIL_PKG.Get_Rpr_Batch_For_Inst(p_instance_id,
2629 p_org_id));
2630 CURSOR c_task_dtls (p_task_id IN NUMBER) IS
2631 SELECT instance_id FROM ahl_visit_tasks_b
2632 WHERE visit_task_id = p_task_id;
2633
2634 -- Cursor to get the RTS workorder for a repair batch
2635 CURSOR c_get_rts_wo_id(p_rpr_batch_task_id NUMBER, p_instance_id NUMBER) IS
2636 SELECT wip_entity_id FROM ahl_workorders
2637 WHERE status_code IN ('1', '3', '6', '19', '20', '17')
2638 --unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
2639 AND visit_task_id IN
2640 (SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt
2641 WHERE vt.cost_parent_id IS NOT NULL
2642 AND NVL(vt.return_to_supply_flag,'N') = 'Y'
2643 AND vt.instance_id = p_instance_id
2644 START WITH vt.visit_task_id = p_rpr_batch_task_id
2645 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
2646 );
2647 --Cursor to get the repair batch name for a given task
2648 CURSOR c_get_repair_batch_name(p_task_id IN NUMBER) IS
2649 SELECT repair_batch_name FROM ahl_visit_tasks_b WHERE
2650 visit_task_id = p_task_id
2651 AND repair_batch_name IS NOT NULL;
2652 -- Kasridha: Changes for Bug#13739171 Ends
2653
2654 --declare all local variables here
2655 --**********************************
2656 l_tasks_tbl AHL_VWP_PROJ_PROD_PVT.Task_Tbl_Type;
2657 l_visit_task_id NUMBER;
2658 l_create_task_tbl AHL_VWP_RULES_PVT.Task_Tbl_Type;
2659 l_x_sr_mr_association_tbl AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type;
2660 l_move_item_ins_tbl AHL_PRD_PARTS_CHANGE_PVT.move_item_instance_tbl_type;--amsriniv ER 6014567
2661 l_ins_cntr NUMBER := 0;--amsriniv ER 6014567
2662 i NUMBER;
2663 l_count NUMBER;
2664 l_unit_effectivity_id NUMBER;
2665 l_tsk_count NUMBER := 1;
2666 l_nmo_wo_id NUMBER; --amsriniv ER 6014567
2667 l_api_name VARCHAR2(200) := 'PROCESS_MR';
2668 l_workorder_id NUMBER;
2669 -- SKPATHAK :: Bug 8343599 :: 04-AUG-2009
2670 l_recalculate_vwp_dates VARCHAR2(1) := 'Y';
2671 l_firm_planned_flag NUMBER;
2672 l_service_type_code VARCHAR2(30);
2673 -- Kasridha: Changes for Bug#13739171
2674 l_is_comp_visit VARCHAR2(1);
2675 l_rts_wip_id NUMBER;
2676 l_rpr_batch_task_id NUMBER;
2677 l_summary_task_id NUMBER;
2678 l_repair_batch_name VARCHAR2(100);
2679 l_instance_id NUMBER := -1;
2680 l_task_type VARCHAR2(30);
2681 l_visit_org_id NUMBER;
2682 l_visit_status_code VARCHAR2(30);
2683 l_return_status VARCHAR2(1);
2684 L_API_VERSION CONSTANT NUMBER := 1.0;
2685 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2686 l_msg_count NUMBER;
2687 l_msg_data VARCHAR2(2000);
2688
2689 BEGIN
2690
2691 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2692 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.begin', 'Entering Procedure');
2693 END IF;
2694
2695 FOR i in p_x_task_tbl.FIRST .. p_x_task_tbl.LAST
2696 LOOP
2697
2698 IF p_mr_assoc_tbl.COUNT > 0 AND (p_module_type IS NULL OR p_module_type <> 'SR_OA')
2699 THEN
2700
2701 copy_mr_details(
2702 p_mr_assoc_tbl,
2703 l_x_sr_mr_association_tbl,
2704 i
2705 );
2706
2707 OPEN c_get_sr_details(p_x_task_tbl(i).Incident_id);
2708 FETCH c_get_sr_details INTO p_x_task_tbl(i).Incident_object_version_number;
2709 CLOSE c_get_sr_details;
2710
2711 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2712 fnd_log.string(
2713 fnd_log.level_statement,
2714 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2715 'Before calling AHL_UMP_SR_PVT.Process_SR_MR_Associations...'
2716 );
2717 fnd_log.string(
2718 fnd_log.level_statement,
2719 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2720 'p_x_task_tbl(i).Incident_id ->'||p_x_task_tbl(i).Incident_id
2721 );
2722 fnd_log.string(
2723 fnd_log.level_statement,
2724 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2725 'p_x_task_tbl(i).Incident_object_version_number'||p_x_task_tbl(i).Incident_object_version_number
2726 );
2727 fnd_log.string(
2728 fnd_log.level_statement,
2729 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2730 'p_x_task_tbl(i).Incident_number'||p_x_task_tbl(i).Incident_number
2731 );
2732 END IF;
2733
2734 -- 1. Create Unit Effectivity hierarchy for the SR - MR hieararchy.
2735 AHL_UMP_SR_PVT.Process_SR_MR_Associations(
2736 p_api_version => 1.0,
2737 p_init_msg_list => FND_API.G_TRUE,-- verify the value to be passed here
2738 p_commit => FND_API.G_FALSE,
2739 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2740 x_return_status => x_return_status,
2741 x_msg_count => x_msg_count,
2742 x_msg_data => x_msg_data,
2743 p_user_id => fnd_global.user_id,
2744 p_login_id => fnd_global.login_id,
2745 p_request_id => p_x_task_tbl(i).Incident_id,
2746 p_object_version_number => p_x_task_tbl(i).Incident_object_version_number,
2747 p_request_number => p_x_task_tbl(i).Incident_number,
2748 p_x_sr_mr_association_tbl => l_x_sr_mr_association_tbl
2749 );
2750
2751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2752 fnd_log.string(
2753 fnd_log.level_statement,
2754 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2755 'After calling AHL_UMP_SR_PVT.Process_SR_MR_Associations...Return status->'||x_return_status
2756 );
2757 END IF;
2758
2759 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2760 RAISE FND_API.G_EXC_ERROR;
2761 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
2762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2763 END IF;
2764
2765 END IF;
2766 -- 2. Call VWP API to create task Hiearchy.
2767
2768 --FP for ER 5716489 -- start
2769 -- Call the VWP API to create task hierarchy only when the create Workorder
2770 -- flag is selected . If it is not checked ie:N, then do not create the tasks and WOs
2771
2772 IF (nvl(UPPER(p_x_task_tbl(i).WO_Create_flag),'Y') = 'Y')
2773 THEN
2774
2775 -- retrieve unit effectivity id corresponding to the SR
2776 OPEN c_get_ue_id(p_x_task_tbl(i).incident_id);
2777 FETCH c_get_ue_id into l_unit_effectivity_id ;
2778 IF c_get_ue_id%NotFound
2779 THEN
2780 x_return_status := FND_API.G_RET_STS_ERROR;
2781 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_SR');
2782 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
2783 FND_MSG_PUB.ADD;
2784 RAISE FND_API.G_EXC_ERROR;
2785 END IF;
2786 CLOSE c_get_ue_id;
2787
2788 OPEN c_visit_task_csr(p_x_task_tbl(i).Originating_wo_id);
2789 FETCH c_visit_task_csr INTO l_visit_task_id;
2790 CLOSE c_visit_task_csr;
2791
2792 l_create_task_tbl(l_tsk_count).originating_task_id := l_visit_task_id;
2793
2794 l_create_task_tbl(l_tsk_count).visit_id := p_x_task_tbl(i).visit_id;
2795 l_create_task_tbl(l_tsk_count).service_request_id := p_x_task_tbl(i).incident_id;
2796 l_create_task_tbl(l_tsk_count).unit_effectivity_id := l_unit_effectivity_id;
2797 l_create_task_tbl(l_tsk_count).task_type_code := 'PLANNED';
2798 l_create_task_tbl(l_tsk_count).operation_flag := 'C';
2799 --apattark start for bug #9368251
2800 l_create_task_tbl(l_tsk_count).duration := p_x_task_tbl(i).duration;
2801 l_firm_planned_flag := p_x_task_tbl(i).firmWo_flag;
2802
2803 -- STHILAK, PIE Changes, ER 9048699
2804
2805 IF (p_x_task_tbl(i).service_type IS NOT NULL)
2806 THEN
2807 l_create_task_tbl(l_tsk_count).service_type := p_x_task_tbl(i).service_type;
2808 l_create_task_tbl(l_tsk_count).service_type_code := p_x_task_tbl(i).service_type_code;
2809 ELSE
2810 OPEN c_get_nr_service_type_code(l_unit_effectivity_id);
2811 FETCH c_get_nr_service_type_code INTO l_service_type_code;
2812 CLOSE c_get_nr_service_type_code;
2813 l_create_task_tbl(l_tsk_count).service_type_code := l_service_type_code;
2814 END IF;
2815
2816
2817 IF (l_firm_planned_flag is null) THEN
2818 OPEN get_firm_planned_flag_csr(l_create_task_tbl(l_tsk_count).service_request_id);
2819 FETCH get_firm_planned_flag_csr into l_firm_planned_flag;
2820 CLOSE get_firm_planned_flag_csr;
2821 END IF;
2822
2823 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2824 fnd_log.string(
2825 fnd_log.level_statement,
2826 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2827 'abh wo firm flag'|| p_x_task_tbl(i).firmWo_flag || 'l_firm_planned_flag' || l_firm_planned_flag ||
2828 'wo id' || l_workorder_id
2829 );
2830 END IF;
2831 -- l_create_task_tbl(l_tsk_count).duration := p_x_task_tbl(i).duration;
2832 --apattark end for bug #9368251
2833 l_create_task_tbl(l_tsk_count).quantity := p_x_task_tbl(i).instance_quantity; --amsriniv. Issue 105 ER 6014567
2834
2835 -- FP Bug # 7720088 (Mexicana Bug # 7697685) -- start
2836 IF p_x_task_tbl(i).workorder_start_time IS NOT NULL
2837 THEN
2838 l_create_task_tbl(l_tsk_count).task_start_date := p_x_task_tbl(i).workorder_start_time;
2839 END IF;
2840 -- FP Bug # 7720088 (Mexicana Bug # 7697685) -- end
2841
2842 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2843 fnd_log.string(
2844 fnd_log.level_statement,
2845 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2846 'Before calling AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS...'
2847 );
2848 fnd_log.string(
2849 fnd_log.level_statement,
2850 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2851 'p_x_task_tbl(i).visit_id->'||p_x_task_tbl(i).visit_id
2852 );
2853 fnd_log.string(
2854 fnd_log.level_statement,
2855 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2856 'p_x_task_tbl(i).incident_id->'||p_x_task_tbl(i).incident_id
2857 );
2858 fnd_log.string(
2859 fnd_log.level_statement,
2860 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2861 'l_unit_effectivity_id->'||l_unit_effectivity_id
2862 );
2863 fnd_log.string(
2864 fnd_log.level_statement,
2865 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2866 'p_x_task_tbl(i).service_type ->'||p_x_task_tbl(i).service_type
2867 );
2868 fnd_log.string(
2869 fnd_log.level_statement,
2870 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2871 'l_create_task_tbl(l_tsk_count).service_type_code ->'||l_create_task_tbl(l_tsk_count).service_type_code
2872 );
2873
2874 -- FP Bug # 7720088 (Mexicana Bug # 7697685) -- start
2875 fnd_log.string(
2876 fnd_log.level_statement,
2877 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2878 'p_x_task_tbl(i).workorder_start_time->'||p_x_task_tbl(i).workorder_start_time
2879 );
2880 -- FP Bug # 7720088 (Mexicana Bug # 7697685) -- end
2881 END IF;
2882
2883 AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS(
2884 p_api_version => 1.0,
2885 p_init_msg_list => Fnd_Api.G_TRUE,
2886 p_module_type => 'SR',
2887 p_x_task_tbl => l_create_task_tbl,
2888 x_return_status => x_return_status,
2889 x_msg_count => x_msg_count,
2890 x_msg_data => x_msg_data
2891 );
2892
2893 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2894 fnd_log.string(
2895 fnd_log.level_statement,
2896 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2897 'After calling AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS...Return status->'||x_return_status
2898 );
2899 END IF;
2900
2901 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2902 RAISE FND_API.G_EXC_ERROR;
2903 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
2904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2905 END IF;
2906
2907 -- 3. Call VWP API to push tasks into production.
2908
2909 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2910 fnd_log.string(
2911 fnd_log.level_statement,
2912 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2913 'Before calling AHL_VWP_PROJ_PROD_PVT.Release_MR...'
2914 );
2915 END IF;
2916 -- FP for ER 5716489 -- start
2917 -- SKPATHAK :: Bug 8343599 :: 04-AUG-2009
2918 IF p_x_task_tbl(i).workorder_start_time IS NOT NULL THEN
2919 -- User has entered a start date for the non-routine.
2920 -- Need to honor this date
2921 l_recalculate_vwp_dates := 'N';
2922 END IF;
2923
2924 IF ( nvl(UPPER(p_x_task_tbl(i).WO_Release_flag), 'Y') = 'Y' )
2925 THEN
2926
2927 AHL_VWP_PROJ_PROD_PVT.Release_MR(
2928 p_api_version => 1.0,
2929 p_init_msg_list => Fnd_Api.G_FALSE,
2930 p_commit => Fnd_Api.G_FALSE,
2931 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2932 p_module_type => 'SR',
2933 p_visit_id => p_x_task_tbl(i).visit_id,
2934 p_unit_effectivity_id => l_unit_effectivity_id,
2935 p_release_flag => 'Y',
2936 -- SKPATHAK :: Bug 8343599 :: 04-AUG-2009
2937 p_recalculate_dates => l_recalculate_vwp_dates,
2938 x_workorder_id => l_workorder_id,
2939 x_return_status => x_return_status,
2940 x_msg_count => x_msg_count,
2941 x_msg_data => x_msg_data,
2942 p_firm_planned_flag => l_firm_planned_flag
2943 );
2944
2945 ELSE
2946 AHL_VWP_PROJ_PROD_PVT.Release_MR(
2947 p_api_version => 1.0,
2948 p_init_msg_list => Fnd_Api.G_FALSE,
2949 p_commit => Fnd_Api.G_FALSE,
2950 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2951 p_module_type => 'SR',
2952 p_visit_id => p_x_task_tbl(i).visit_id,
2953 p_unit_effectivity_id => l_unit_effectivity_id,
2954 p_release_flag => 'N',
2955 -- SKPATHAK :: Bug 8343599 :: 04-AUG-2009
2956 p_recalculate_dates => l_recalculate_vwp_dates,
2957 x_workorder_id => l_workorder_id,
2958 x_return_status => x_return_status,
2959 x_msg_count => x_msg_count,
2960 x_msg_data => x_msg_data,
2961 p_firm_planned_flag => l_firm_planned_flag
2962 );
2963
2964 END IF;
2965 --FP for ER 5716489 -- end
2966
2967 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2968 fnd_log.string(
2969 fnd_log.level_statement,
2970 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2971 'After calling AHL_VWP_PROJ_PROD_PVT.Release_MR...Return status ->'||x_return_status
2972 );
2973 END IF;
2974
2975 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2976 RAISE FND_API.G_EXC_ERROR;
2977 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
2978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2979 END IF;
2980
2981 -- Added by jaramana on Oct 15
2982 IF ( upper(p_x_task_tbl(i).operation_type) = 'CREATE') THEN
2983 OPEN c_NR_wo_details(l_unit_effectivity_id);
2984 FETCH c_NR_wo_details INTO p_x_task_tbl(i).Nonroutine_wo_id;
2985 CLOSE c_NR_wo_details;
2986 END IF;
2987 --amsriniv ER 6014567 Begin
2988 IF ((nvl(upper(p_x_task_tbl(i).WO_Release_flag), 'Y') = 'Y') AND (nvl(upper(p_x_task_tbl(i).move_qty_to_nr_workorder),'N') = 'Y') AND
2989 upper(p_x_task_tbl(i).operation_type) = 'CREATE' AND
2990 p_x_task_tbl(i).nonroutine_wo_id IS NOT NULL)
2991 THEN
2992 OPEN get_nonmaster_wo_id(p_x_task_tbl(i).nonroutine_wo_id);
2993 FETCH get_nonmaster_wo_id into l_nmo_wo_id ;
2994 IF get_nonmaster_wo_id%FOUND
2995 THEN
2996 l_move_item_ins_tbl(l_ins_cntr).instance_id := p_x_task_tbl(i).instance_id;
2997 l_move_item_ins_tbl(l_ins_cntr).quantity := p_x_task_tbl(i).instance_quantity;
2998 l_move_item_ins_tbl(l_ins_cntr).from_workorder_id := p_x_task_tbl(i).originating_wo_id;
2999 l_move_item_ins_tbl(l_ins_cntr).to_workorder_id := l_nmo_wo_id;
3000 l_ins_cntr := l_ins_cntr + 1;
3001 END IF;
3002 END IF;
3003 --amsriniv ER 6014567 End
3004 END IF;
3005 --FP for ER 5716489 -- end
3006
3007 END LOOP;
3008 --amsriniv ER 6014567 Begin
3009 IF (l_ins_cntr > 0)
3010 THEN
3011 AHL_PRD_PARTS_CHANGE_PVT.move_instance_location(
3012 p_api_version => 1.0,
3013 p_init_msg_list => Fnd_Api.G_FALSE,
3014 p_commit => Fnd_Api.G_FALSE,
3015 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
3016 p_module_type => NULL,
3017 p_default => FND_API.G_TRUE,
3018 p_move_item_instance_tbl => l_move_item_ins_tbl,
3019 x_return_status => x_return_status,
3020 x_msg_count => x_msg_count,
3021 x_msg_data => x_msg_data
3022 );
3023 END IF;
3024 FOR i in p_x_task_tbl.FIRST .. p_x_task_tbl.LAST
3025 LOOP
3026 /* Kasridha :Changes for bug# 13739171
3027 * Call API for moving materials from planning to in-repair locator
3028 * when adding an MR to the repair batch.
3029 */
3030 -- Get Visit Details
3031 OPEN c_get_visit_dtls (p_x_task_tbl(i).visit_id);
3032 FETCH c_get_visit_dtls INTO l_visit_org_id, l_visit_status_code;
3033 CLOSE c_get_visit_dtls;
3034
3035 l_rpr_batch_task_id := NULL;
3036 l_rts_wip_id := NULL;
3037 l_repair_batch_name := NULL;
3038
3039 l_is_comp_visit := AHL_CMP_UTIL_PKG.Is_Comp_Visit(p_x_task_tbl(i).visit_id);
3040
3041 IF l_is_comp_visit = 'Y'
3042 AND l_visit_status_code IN ('PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
3043 AND p_x_task_tbl(i).instance_id IS NOT NULL
3044 THEN
3045
3046 -- Get the repair batch task ID
3047 OPEN c_get_rprbatch_task (p_x_task_tbl(i).instance_id, l_visit_org_id);
3048 FETCH c_get_rprbatch_task INTO l_rpr_batch_task_id;
3049 CLOSE c_get_rprbatch_task;
3050
3051 -- Get the RTS workorder ID
3052 OPEN c_get_rts_wo_id(l_rpr_batch_task_id, p_x_task_tbl(i).instance_id);
3053 FETCH c_get_rts_wo_id INTO l_rts_wip_id;
3054 CLOSE c_get_rts_wo_id;
3055
3056 -- Get the repair batch name
3057 OPEN c_get_repair_batch_name(l_rpr_batch_task_id);
3058 FETCH c_get_repair_batch_name INTO l_repair_batch_name;
3059 CLOSE c_get_repair_batch_name;
3060
3061
3062 IF l_rts_wip_id IS NOT NULL
3063 AND l_repair_batch_name IS NOT NULL THEN
3064 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3065 fnd_log.string(fnd_log.level_statement,
3066 L_DEBUG_KEY,
3067 'Calling AHL_CMP_PVT.Move_To_InRepair_Locator');
3068 END IF;
3069
3070 AHL_CMP_PVT.Move_To_InRepair_Locator(
3071 p_api_version => 1.0,
3072 p_init_msg_list => Fnd_Api.g_false,
3073 p_commit => Fnd_Api.g_false,
3074 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
3075 p_wip_entity_id => l_rts_wip_id,
3076 p_instance_id => p_x_task_tbl(i).instance_id,
3077 p_repair_batch_name => l_repair_batch_name,
3078 x_return_status => l_return_status,
3079 x_msg_count => l_msg_count,
3080 x_msg_data => l_msg_data);
3081
3082
3083 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3084 fnd_log.string(fnd_log.level_statement,
3085 L_DEBUG_KEY,
3086 'After Calling AHL_VWP_PROJ_PROD_PVT.Move_To_InRepair_Locator.' ||
3087 'Return Status = ' || l_return_status );
3088 END IF;
3089 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3090 x_msg_count := l_msg_count;
3091 x_return_status := l_return_status;
3092 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3093 RAISE Fnd_Api.g_exc_error;
3094 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3095 RAISE Fnd_Api.g_exc_unexpected_error;
3096 END IF;
3097 ELSE
3098 FND_MSG_PUB.Initialize;
3099 END IF;
3100 END IF;
3101 END IF;
3102 END LOOP;
3103 -- Kasridha : Changes for Bug# 13739171 Ends
3104 --amsriniv ER 6014567 End
3105 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3106 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.end', 'Exiting Procedure');
3107 END IF;
3108
3109 END Process_Mr;
3110 -- MR NR ER -- end
3111
3112 -- MR NR ER -- start
3113 PROCEDURE Copy_Mr_Details(
3114 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
3115 p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type,
3116 p_sr_table_index IN NUMBER
3117 )
3118 IS
3119 -- declare all local variables here.
3120 l_count NUMBER;
3121 l_api_name VARCHAR2(200) := 'COPY_MR_DETAILS';
3122
3123 BEGIN
3124 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3125 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.begin', 'Entering Procedure');
3126 END IF;
3127
3128 l_count := 0;
3129
3130 FOR j IN p_mr_assoc_tbl.FIRST .. p_mr_assoc_tbl.LAST
3131 LOOP
3132 IF p_sr_table_index = p_mr_assoc_tbl(j).sr_tbl_index THEN
3133
3134 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3135 fnd_log.string(
3136 fnd_log.level_statement,
3137 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
3138 'p_sr_table_index->'||p_sr_table_index
3139 );
3140 END IF;
3141
3142 l_count := l_count + 1;
3143 p_x_sr_mr_association_tbl(l_count).mr_header_id := p_mr_assoc_tbl(j).mr_header_id;
3144 p_x_sr_mr_association_tbl(l_count).mr_title := p_mr_assoc_tbl(j).mr_title;
3145 p_x_sr_mr_association_tbl(l_count).mr_version := p_mr_assoc_tbl(j).mr_version;
3146 p_x_sr_mr_association_tbl(l_count).relationship_code := 'PARENT';
3147 p_x_sr_mr_association_tbl(l_count).csi_instance_id := p_mr_assoc_tbl(j).csi_instance_id;
3148 p_x_sr_mr_association_tbl(l_count).csi_instance_number := p_mr_assoc_tbl(j).csi_instance_number;
3149 p_x_sr_mr_association_tbl(l_count).operation_flag := 'C';
3150 END IF;
3151 END LOOP;
3152
3153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.end', 'Exiting Procedure');
3155 END IF;
3156
3157 END Copy_Mr_Details;
3158 -- MR NR ER -- end
3159
3160 --------------------------------------------
3161 -- Create VWP Task
3162 --------------------------------------------
3163 PROCEDURE Create_task(
3164 p_x_task_tbl IN OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_tbl_type,
3165 x_return_status OUT NOCOPY VARCHAR2
3166 ) IS
3167
3168 l_create_job_task_tbl AHL_VWP_PROJ_PROD_PVT.Task_tbl_type;
3169 l_msg_count NUMBER;
3170 l_msg_data VARCHAR2(2000);
3171 l_org_task_id NUMBER;
3172 l_request_type VARCHAR2(60);
3173 l_visit_task_name VARCHAR2(80);
3174 l_task_type_code VARCHAR2(30) := 'UNASSOCIATED';
3175 l_operation_flag VARCHAR2(3) := 'C';
3176 l_unit_effectivity_id NUMBER;
3177 CURSOR GetRequestType(c_req_type_id NUMBER)
3178 Is
3179 Select name
3180 FROM cs_incident_types_vl
3181 WHERE incident_type_id = c_req_type_id;
3182 CURSOR GetOrgTaskDet(c_org_wo_id NUMBER)
3183 Is
3184 Select visit_task_id
3185 from ahl_workorders
3186 where workorder_id = c_org_wo_id;
3187
3188 CURSOR getUnitEffectivity(p_incident_id NUMBER)
3189 IS
3190 Select unit_effectivity_id
3191 from AHL_UNIT_EFFECTIVITIES_B
3192 where cs_incident_id = p_incident_id;
3193
3194 -- FND Logging Constants
3195 l_debug_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3196 l_debug_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
3197 l_debug_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
3198 l_debug_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
3199
3200 BEGIN
3201
3202 IF (l_debug_PROC >= l_debug_LEVEL) THEN
3203 fnd_log.string
3204 (l_debug_PROC,
3205 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task.begin',
3206 'At the start of PLSQL procedure');
3207 END IF;
3208
3209 FOR i IN p_x_task_tbl.FIRST..p_x_task_tbl.LAST LOOP
3210 IF ( upper(p_x_task_tbl(i).operation_type) = 'CREATE') THEN
3211 -- Initialize the Record type
3212 --
3213 l_request_type := null;
3214 l_org_task_id := null;
3215
3216 -- Derive the request type
3217 IF (p_x_task_tbl(i).type_name is null or
3218 p_x_task_tbl(i).type_name = FND_API.G_MISS_CHAR)
3219 THEN
3220 Open GetRequestType(p_x_task_tbl(i).type_id);
3221 Fetch GetRequestType into l_request_type;
3222 Close GetRequestType;
3223 ELSE
3224 l_request_type := p_x_task_tbl(i).type_name;
3225 END IF;
3226
3227 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3228 fnd_log.string
3229 (l_debug_STMT,
3230 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3231 'After deriving request type:' || l_request_type);
3232 END IF;
3233
3234 -- Derive the originating visit id
3235
3236 Open GetOrgTaskDet(p_x_task_tbl(i).originating_wo_id);
3237 Fetch GetOrgTaskDet into l_org_task_id;
3238
3239 If GetOrgTaskDet%Found and GetOrgTaskDet%rowcount >1
3240 Then
3241 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_TASK_ID_NOT_UNIQUE');
3242 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
3243 Fnd_Msg_Pub.ADD;
3244 RAISE FND_API.G_EXC_ERROR;
3245 ElsIf GetOrgTaskDet%NotFound
3246 Then
3247 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_WO_ID');
3248 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
3249 Fnd_Msg_Pub.ADD;
3250 RAISE FND_API.G_EXC_ERROR;
3251 End if;
3252 Close GetOrgTaskDet;
3253
3254 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3255 fnd_log.string
3256 (l_debug_STMT,
3257 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3258 'After deriving originating visit task id:' || l_org_task_id);
3259
3260 END IF;
3261
3262 -- If visit task name is null then default the values
3263 IF( p_x_task_tbl(i).visit_task_name is null or
3264 p_x_task_tbl(i).visit_task_name = FND_API.G_MISS_CHAR) THEN
3265 l_visit_task_name := substr(l_request_type,1,(78-length(p_x_task_tbl(i).incident_number)))||'-'
3266 ||p_x_task_tbl(i).incident_number;
3267
3268 p_x_task_tbl(i).visit_task_name := l_visit_task_name;
3269
3270 ELSE
3271 l_visit_task_name := p_x_task_tbl(i).visit_task_name;
3272 END IF;
3273
3274 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3275 fnd_log.string
3276 (l_debug_STMT,
3277 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3278 'After defaulting task name');
3279 END IF;
3280
3281 --- Begin Changes by VSUNDARA for SR INTEGRATION
3282 Open getUnitEffectivity(p_x_task_tbl(i).incident_id);
3283 Fetch getUnitEffectivity into l_unit_effectivity_id ;
3284 IF getUnitEffectivity%NotFound
3285 Then
3286 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_SR'); -- New Message needed to be added
3287 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
3288 Fnd_Msg_Pub.ADD;
3289 RAISE FND_API.G_EXC_ERROR;
3290 End if;
3291 Close getUnitEffectivity;
3292 l_create_job_task_tbl(i).unit_effectivity_id := l_unit_effectivity_id;
3293 --- END Changes by VSUNDARA for SR INTEGRATION
3294
3295 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3296 fnd_log.string
3297 (l_debug_STMT,
3298 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3299 'After deriving UE ID:' || l_unit_effectivity_id);
3300 END IF;
3301
3302 -- Assign the Create Job Tasks values
3303 l_create_job_task_tbl(i).visit_id := p_x_task_tbl(i).visit_id;
3304 l_create_job_task_tbl(i).visit_task_name := l_visit_task_name;
3305 l_create_job_task_tbl(i).duration := p_x_task_tbl(i).duration;
3306 l_create_job_task_tbl(i).instance_id := p_x_task_tbl(i).instance_id;
3307 l_create_job_task_tbl(i).service_request_id := p_x_task_tbl(i).incident_id;
3308 l_create_job_task_tbl(i).originating_task_id := l_org_task_id;
3309 l_create_job_task_tbl(i).task_type_code := l_task_type_code;
3310 l_create_job_task_tbl(i).operation_flag := l_operation_flag;
3311
3312
3313 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3314 fnd_log.string
3315 (l_debug_STMT,
3316 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3317 'End loop for visit task name: ' || l_visit_task_name);
3318 END IF;
3319
3320 END IF;
3321 END LOOP;
3322
3323 IF l_create_job_task_tbl.count > 0 THEN
3324
3325 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3326 fnd_log.string
3327 (l_debug_STMT,
3328 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3329 'Before calling AHL_VWP_PROJ_PROD_PVT.Create_job_tasks');
3330 END IF;
3331
3332 AHL_VWP_PROJ_PROD_PVT.Create_job_tasks(
3333 p_api_version => 1.0,
3334 p_init_msg_list => FND_API.G_TRUE,
3335 p_commit => FND_API.G_FALSE,
3336 p_validation_level => Fnd_API.G_VALID_LEVEL_FULL,
3337 p_module_type => NULL,
3338 p_x_task_tbl => l_create_job_task_tbl,
3339 x_return_status => x_return_status,
3340 x_msg_count => l_msg_count,
3341 x_msg_data => l_msg_data
3342 );
3343
3344 -- AHL_VWP_PROJ_PROD_PVT.Create_job_tasks returns x_return_status as success
3345 -- even though visit validation fails. The validation errors are put in the
3346 -- error stack. In this case, the WO creation api will not be called and
3347 -- wo_id returned is null. Task is created.
3348
3349 l_msg_count := FND_MSG_PUB.count_msg;
3350
3351 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS OR l_msg_count > 0) THEN
3352
3353 IF (l_debug_UEXP >= l_debug_LEVEL) THEN
3354 fnd_log.string
3355 (l_debug_UEXP,
3356 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3357 'Error ' || x_return_status ||' returned from AHL_VWP_PROJ_PROD_PVT.Create_job_tasks');
3358 END IF;
3359
3360 RAISE FND_API.G_EXC_ERROR;
3361 END IF;
3362
3363 END IF;
3364
3365 /*
3366 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
3367 x_return_status := FND_API.G_RET_STS_ERROR;
3368 END IF;
3369 */
3370
3371 FOR i IN p_x_task_tbl.FIRST..p_x_task_tbl.LAST LOOP
3372 IF ( upper(p_x_task_tbl(i).operation_type) = 'CREATE') THEN
3373
3374 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3375 fnd_log.string
3376 (l_debug_STMT,
3377 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3378 'Now processing for WO: ' || l_create_job_task_tbl(i).workorder_id);
3379 END IF;
3380
3381 p_x_task_tbl(i).visit_task_id := l_create_job_task_tbl(i).visit_task_id;
3382 p_x_task_tbl(i).visit_task_number := l_create_job_task_tbl(i).visit_task_number;
3383 p_x_task_tbl(i).Nonroutine_wo_id := l_create_job_task_tbl(i).workorder_id;
3384
3385 -- R12: modified for bug# 5261150.
3386 IF (nvl(p_x_task_tbl(i).WO_Release_flag,'Y') = 'Y' AND
3387 l_create_job_task_tbl(i).workorder_id IS NOT NULL) THEN
3388
3389 -- Fix for bug# 5261150.
3390 -- release workorder if user chooses to release wo.
3391 -- Default is to release wo.
3392 IF (l_debug_STMT >= l_debug_LEVEL) THEN
3393 fnd_log.string
3394 (l_debug_STMT,
3395 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3396 'Before calling AHL_PRD_WORKORDER_PVT.Release_visit_jobs for WO: ' || l_create_job_task_tbl(i).workorder_id);
3397 END IF;
3398
3399 AHL_PRD_WORKORDER_PVT.Release_visit_jobs
3400 (
3401 p_api_version => 1.0,
3402 p_init_msg_list => FND_API.G_TRUE,
3403 p_commit => FND_API.G_FALSE,
3404 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3405 p_default => FND_API.G_FALSE,
3406 p_module_type => NULL,
3407 x_return_status => x_return_status,
3408 x_msg_count => l_msg_count,
3409 x_msg_data => l_msg_data,
3410 p_visit_id => NULL,
3411 p_unit_effectivity_id => NULL,
3412 p_workorder_id => l_create_job_task_tbl(i).workorder_id
3413 );
3414
3415 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3416 IF (l_debug_UEXP >= l_debug_LEVEL) THEN
3417 fnd_log.string
3418 (l_debug_UEXP,
3419 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
3420 'Error ' || x_return_status ||' returned from AHL_PRD_WORKORDER_PVT.Release_visit_jobs');
3421 END IF;
3422 RAISE FND_API.G_EXC_ERROR;
3423 END IF;
3424
3425 END IF; -- p_x_task_tbl(i).WO_Release_flag = 'Y'
3426
3427 END IF;
3428 END LOOP;
3429
3430 IF (l_debug_PROC >= l_debug_LEVEL) THEN
3431 fnd_log.string
3432 (l_debug_PROC,
3433 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task.End',
3434 'At the end of PLSQL procedure');
3435 END IF;
3436
3437 EXCEPTION
3438 WHEN FND_API.G_EXC_ERROR THEN
3439 x_return_status := FND_API.G_RET_STS_ERROR;
3440 END Create_task;
3441
3442
3443 --------------------------------------------
3444 -- Update Service Request
3445 --------------------------------------------
3446
3447 ----------------------------------------------
3448 -- Update_sr procedure assigns the values to
3449 -- the service request record and calls the
3450 -- update_servicerquest public api.
3451 ----------------------------------------------
3452 PROCEDURE Update_sr(
3453 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
3454 x_return_status OUT NOCOPY VARCHAR2
3455 ) IS
3456
3457 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
3458 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
3459 l_notes_table CS_ServiceRequest_PUB.notes_table;
3460 l_contact_primary_flag CONSTANT VARCHAR2(1) := 'Y';
3461
3462 l_msg_count NUMBER;
3463 l_msg_data VARCHAR2(2000);
3464
3465 BEGIN
3466
3467 -- Initialize the SR record.
3468 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
3469
3470
3471 -- Assign the SR rec values
3472 l_service_request_rec.status_id := p_x_sr_task_rec.status_id;
3473 l_service_request_rec.status_name := p_x_sr_task_rec.status_name;
3474
3475 l_service_request_rec.urgency_id := p_x_sr_task_rec.urgency_id;
3476 l_service_request_rec.urgency_name := p_x_sr_task_rec.urgency_name;
3477 l_service_request_rec.problem_code := p_x_sr_task_rec.problem_code;
3478 l_service_request_rec.resolution_code := p_x_sr_task_rec.resolution_code;
3479 l_service_request_rec.last_update_program_code := p_x_sr_task_rec.source_program_code;
3480
3481 /* R12(xbuild#1): Commenting out passing contacts table as CS API raises an error:
3482 API programming error ( CS_SRCONTACT_PKG.check_duplicates): This contact is
3483 a duplicate of a contact already associated with the service request. Each
3484 contact you associate must have a unique combination of party name and
3485 contact point.
3486
3487 -- Contacts
3488 l_contacts_table(1).party_id := p_x_sr_task_rec.contact_id;
3489 l_contacts_table(1).contact_type := p_x_sr_task_rec.contact_type;
3490 l_contacts_table(1).primary_flag := l_contact_primary_flag;
3491 */
3492
3493 -- Call to Service Request API
3494 CS_SERVICEREQUEST_PUB.Update_ServiceRequest(
3495 p_api_version => 3.0,
3496 p_init_msg_list => FND_API.G_TRUE,
3497 p_commit => FND_API.G_FALSE,
3498 x_return_status => x_return_status,
3499 x_msg_count => l_msg_count,
3500 x_msg_data => l_msg_data,
3501 p_request_id => p_x_sr_task_rec.incident_id,
3502 --p_request_number => p_x_sr_task_rec.incident_number,
3503 p_audit_comments => Null,
3504 p_object_version_number => p_x_sr_task_rec.incident_object_version_number,
3505 p_resp_appl_id => NULL,
3506 p_resp_id => NULL,
3507 p_last_updated_by => NULL,
3508 p_last_update_login => NULL,
3509 p_last_update_date => NULL,
3510 p_service_request_rec => l_service_request_rec,
3511 p_notes => l_notes_table,
3512 p_contacts => l_contacts_table,
3513 p_called_by_workflow => NULL,
3514 p_workflow_process_id => NULL,
3515 x_workflow_process_id => p_x_sr_task_rec.workflow_process_id,
3516 x_interaction_id => p_x_sr_task_rec.interaction_id
3517 );
3518
3519 END Update_sr;
3520
3521 -----------------------------
3522 -- Get Message Token
3523 -----------------------------
3524 Procedure get_msg_token(p_wo_id in number,
3525 p_instance_id in number,
3526 x_wo_name out NOCOPY varchar2,
3527 x_instance_number out NOCOPY varchar2)
3528 IS
3529 CURSOR GetWoName
3530 Is
3531 Select workorder_name
3532 from ahl_workorders
3533 where workorder_id = p_wo_id;
3534 Cursor GetInstanceNumber
3535 Is
3536 Select instance_number
3537 from csi_item_instances
3538 where instance_id = p_instance_id;
3539 BEGIN
3540 Open GetWoName;
3541 Fetch GetWoName into x_wo_name;
3542 Close GetWoName;
3543
3544 -- No exceptions were handled in previous code.
3545 -- Changed the big lengthy Begin------End; code for each sql to Cursor.
3546 -- Too many (Invalid) exception handling got reduced.
3547
3548 Open GetInstanceNumber;
3549 Fetch GetInstanceNumber into x_instance_number;
3550 Close GetInstanceNumber;
3551
3552 END get_msg_token;
3553
3554
3555 -----------------------------------------
3556 -- Get Note Information from the Message
3557 -----------------------------------------
3558 Procedure get_note_value(p_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
3559 x_note OUT NOCOPY VARCHAR2,
3560 x_note_detail OUT NOCOPY VARCHAR2)
3561 IS
3562 l_part_number VARCHAR2(80);
3563 l_serial_number VARCHAR2(30);
3564 l_wo_name VARCHAR2(80);
3565 l_instance_number VARCHAR2(30);
3566
3567 CURSOR GetWoName
3568 Is
3569 Select workorder_name
3570 from ahl_workorders
3571 where workorder_id = p_sr_task_rec.originating_wo_id;
3572
3573 CURSOR GetInstanceDet
3574 Is
3575 Select ci.instance_number,
3576 ci.serial_number,
3577 msi.concatenated_segments
3578 from csi_item_instances ci,
3579 mtl_system_items_kfv msi
3580 where ci.instance_id = p_sr_task_rec.instance_id
3581 and ci.inventory_item_id = msi.inventory_item_id
3582 and ci.inv_master_organizatiOn_id = msi.organization_id;
3583
3584 BEGIN
3585 Open GetWoName;
3586 Fetch GetWoName into l_wo_name;
3587 Close GetWoName;
3588
3589 Open GetInstanceDet;
3590 Fetch GetInstanceDet into l_instance_number,l_serial_number,l_part_number;
3591 Close GetInstanceDet;
3592
3593 -- No exceptions were handled in previous code.
3594 -- Changed the big lengthy Begin------End; code for each sql to Cursor.
3595 -- Too many (Invalid) exception handling got reduced.
3596
3597 fnd_message.set_name('AHL','AHL_PRD_SR_NOTE');
3598 fnd_message.set_token('PART_NUMBER',l_part_number);
3599 fnd_message.set_token('SERIAL_NUMBER',l_serial_number);
3600 x_note := fnd_message.get;
3601
3602 fnd_message.set_name('AHL','AHL_PRD_SR_NOTE_DETAIL');
3603 fnd_message.set_token('WO_NAME',l_wo_name);
3604 fnd_message.set_token('INSTANCE_NUMBER',l_instance_number);
3605 x_note_detail := fnd_message.get;
3606
3607 END get_note_value;
3608
3609 -----------------------------------
3610 -- Write to Log
3611 -- This procedure writes the input
3612 -- values to a log file
3613 -----------------------------------
3614 Procedure write_to_log(p_sr_tasK_tbl IN ahl_prd_nonroutine_pvt.sr_task_tbl_type)
3615 IS
3616 BEGIN
3617 FOR i IN p_sr_task_tbl.FIRST..p_sr_task_tbl.LAST LOOP
3618 AHL_DEBUG_PUB.debug('INPUT - Type Id('||i||'):'||p_sr_task_tbl(i).type_id);
3619 AHL_DEBUG_PUB.debug('INPUT - Type Name('||i||'):'||p_sr_task_tbl(i).type_name);
3620 AHL_DEBUG_PUB.debug('INPUT - Status Id('||i||'):'||p_sr_task_tbl(i).status_id);
3621 AHL_DEBUG_PUB.debug('INPUT - Status Name('||i||'):'||p_sr_task_tbl(i).status_name);
3622 AHL_DEBUG_PUB.debug('INPUT - Severity Id('||i||'):'||p_sr_task_tbl(i).severity_id);
3623 AHL_DEBUG_PUB.debug('INPUT - Severity Name('||i||'):'||p_sr_task_tbl(i).severity_name);
3624 AHL_DEBUG_PUB.debug('INPUT - Urgency id('||i||'):'||p_sr_task_tbl(i).Urgency_id);
3625 AHL_DEBUG_PUB.debug('INPUT - Urgency name('||i||'):'||p_sr_task_tbl(i).Urgency_name);
3626 AHL_DEBUG_PUB.debug('INPUT - Customer type('||i||'):'||p_sr_task_tbl(i).Customer_type);
3627 AHL_DEBUG_PUB.debug('INPUT - Customer id('||i||'):'||p_sr_task_tbl(i).Customer_id);
3628 AHL_DEBUG_PUB.debug('INPUT - Customer name('||i||'):'||p_sr_task_tbl(i).Customer_name);
3629 AHL_DEBUG_PUB.debug('INPUT - Contact type('||i||'):'||p_sr_task_tbl(i).Contact_type);
3630 AHL_DEBUG_PUB.debug('INPUT - Contact Id('||i||'):'||p_sr_task_tbl(i).Contact_id);
3631 AHL_DEBUG_PUB.debug('INPUT - Contact name('||i||'):'||p_sr_task_tbl(i).Contact_name);
3632 AHL_DEBUG_PUB.debug('INPUT - Summary ('||i||'):'||p_sr_task_tbl(i).Summary);
3633 AHL_DEBUG_PUB.debug('INPUT - Instance Id('||i||'):'||p_sr_task_tbl(i).Instance_id);
3634 AHL_DEBUG_PUB.debug('INPUT - Instance number('||i||'):'||p_sr_task_tbl(i).Instance_number);
3635 AHL_DEBUG_PUB.debug('INPUT - Visit Id('||i||'):'||p_sr_task_tbl(i).visit_id);
3636 AHL_DEBUG_PUB.debug('INPUT - Visit number('||i||'):'||p_sr_task_tbl(i).visit_number);
3637 AHL_DEBUG_PUB.debug('INPUT - Originating wo id('||i||'):'||p_sr_task_tbl(i).originating_wo_id);
3638 AHL_DEBUG_PUB.debug('INPUT - Incident obj ver num('||i||'):'||p_sr_task_tbl(i).incident_object_version_number);
3639 AHL_DEBUG_PUB.debug('INPUT - Operation type('||i||'):'||p_sr_task_tbl(i).operation_type);
3640 END LOOP;
3641
3642 END write_to_log;
3643
3644 -----------------------------------
3645 -- Write SR Rec to Log
3646 -- This procedure writes the input
3647 -- values of the SR API to a log file
3648 -----------------------------------
3649 Procedure write_sr_to_log
3650 (
3651 p_service_request_rec IN CS_SERVICEREQUEST_PUB.service_request_rec_type,
3652 p_notes_table IN CS_SERVICEREQUEST_PUB.notes_table,
3653 p_contacts_table IN CS_SERVICEREQUEST_PUB.contacts_table
3654 )
3655 IS
3656 BEGIN
3657 AHL_DEBUG_PUB.debug('SR Rec:');
3658 AHL_DEBUG_PUB.debug('request_date:'||p_service_request_rec.request_date);
3659 AHL_DEBUG_PUB.debug('type_id:'||p_service_request_rec.type_id);
3660 AHL_DEBUG_PUB.debug('type_name:'||p_service_request_rec.type_name);
3661 AHL_DEBUG_PUB.debug('status_id:'||p_service_request_rec.status_id);
3662 AHL_DEBUG_PUB.debug('status_name:'||p_service_request_rec.status_name);
3663 AHL_DEBUG_PUB.debug('severity_id:'||p_service_request_rec.severity_id);
3664 AHL_DEBUG_PUB.debug('severity_name:'||p_service_request_rec.severity_name);
3665 AHL_DEBUG_PUB.debug('urgency_id:'||p_service_request_rec.urgency_id);
3666 AHL_DEBUG_PUB.debug('summary:'||p_service_request_rec.summary);
3667 AHL_DEBUG_PUB.debug('caller_type:'||p_service_request_rec.caller_type);
3668 AHL_DEBUG_PUB.debug('customer_id:'||p_service_request_rec.customer_id);
3669 AHL_DEBUG_PUB.debug('problem_code:'||p_service_request_rec.problem_code);
3670 AHL_DEBUG_PUB.debug('resolution_code:'||p_service_request_rec.resolution_code);
3671 AHL_DEBUG_PUB.debug('creation_program_code:'||p_service_request_rec.creation_program_code);
3672 AHL_DEBUG_PUB.debug('urgency_name:'||p_service_request_rec.urgency_name);
3673
3674 -- Contacts
3675 AHL_DEBUG_PUB.debug('Contacts:');
3676 AHL_DEBUG_PUB.debug('party_id:'||p_contacts_table(1).party_id);
3677 AHL_DEBUG_PUB.debug('contact_type:'||p_contacts_table(1).contact_type);
3678 AHL_DEBUG_PUB.debug('primary_flag:'||p_contacts_table(1).primary_flag);
3679
3680 -- Notes
3681 AHL_DEBUG_PUB.debug('Notes:');
3682 AHL_DEBUG_PUB.debug('note:'||p_notes_table(1).note);
3683 AHL_DEBUG_PUB.debug('note_detail:'||p_notes_table(1).note_detail);
3684 AHL_DEBUG_PUB.debug('note_type:'||p_notes_table(1).note_type);
3685 AHL_DEBUG_PUB.debug('note_context_type_01:'||p_notes_table(1).note_context_type_01);
3686 END write_sr_to_log;
3687
3688
3689 END AHL_PRD_NONROUTINE_PVT;
[Home] [Help]