1 PACKAGE BODY AHL_PRD_NONROUTINE_PVT AS
2 /* $Header: AHLVPNRB.pls 120.10.12010000.3 2008/12/25 15:20:54 bachandr 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_module_type IN VARCHAR2,
20 x_return_status OUT NOCOPY VARCHAR2
21 );
22
23 -- Create Service Request
24 PROCEDURE Create_sr(
25 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
26 x_return_status OUT NOCOPY VARCHAR2
27 );
28
29 -- MR NR ER -- start
30 PROCEDURE Process_Mr(
31 p_x_task_tbl IN OUT NOCOPY sr_task_tbl_type,
32 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
33 p_module_type IN VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2
37 );
38
39 PROCEDURE Copy_Mr_Details(
40 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
41 p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type,
42 p_sr_table_index IN NUMBER
43 );
44 -- MR NR ER -- end
45
46 -- Create VWP Task
47 PROCEDURE Create_task(
48 p_x_task_tbl IN OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_tbl_type,
49 x_return_status OUT NOCOPY VARCHAR2
50 );
51
52 -- Update Service Request
53 PROCEDURE Update_sr(
54 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
55 x_return_status OUT NOCOPY VARCHAR2
56 );
57
58 -- Get Message Token
59 PROCEDURE get_msg_token(
60 p_wo_id IN Number,
61 p_instance_id IN Number,
62 x_wo_name OUT NOCOPY VARCHAR2,
63 x_instance_number OUT NOCOPY VARCHAR2
64 );
65
66 -- Get Note and note detail from Message.
67 Procedure get_note_value(p_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
68 x_note OUT NOCOPY VARCHAR2,
69 x_note_detail OUT NOCOPY VARCHAR2
70 );
71 -- procedure to write the input parameters to log
72 Procedure write_to_log(p_sr_tasK_tbl IN ahl_prd_nonroutine_pvt.sr_task_tbl_type
73 );
74 -- procedure to write the SR API input parameters to log
75 Procedure write_sr_to_log
76 (
77 p_service_request_rec IN CS_SERVICEREQUEST_PUB.service_request_rec_type,
78 p_notes_table IN CS_SERVICEREQUEST_PUB.notes_table,
79 p_contacts_table IN CS_SERVICEREQUEST_PUB.contacts_table
80 );
81
82 -- Define global variables here.
83 -- SR status id for status PLANNED
84 G_SR_PLANNED_STATUS_ID CONSTANT NUMBER := 52;
85
86 -- SR status id for status OPEN
87 G_SR_OPEN_STATUS_ID CONSTANT NUMBER := 1;
88
89 --------------------------------------
90 -- End Local Procedures Declaration --
91 --------------------------------------
92
93 --------------------------------------------------------------------
94 -- Procedure name : Process_nonroutine_job
95 -- Type : Private
96 -- Function : To Create or Update Service request based on
97 -- operation_type and to create vwp task for
98 -- a nonroutine job.
99 -- Parameters :
100 --
101 -- Standard IN Parameters :
102 -- p_api_version IN NUMBER Required
103 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
104 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
105 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
106 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
107 -- p_module_type IN VARCHAR2 Default NULL.
108 --
109 -- Standard OUT Parameters :
110 -- x_return_status OUT VARCHAR2 Required
111 -- x_msg_count OUT NUMBER Required
112 -- x_msg_data OUT VARCHAR2 Required
113 --
114 -- Process_nonroutine_job Parameters:
115 -- p_x_sr_task_tbl IN OUT Sr_task_tbl_type Required
116 -- The table of records for creation / updation of Service
117 -- request and creation of vwp task.
118 --
119 -- Version :
120 -- Initial Version 1.0
121 -------------------------------------------------------------------
122 PROCEDURE Process_nonroutine_job (
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
125 p_commit IN VARCHAR2 := Fnd_Api.g_false,
126 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
127 p_module_type IN VARCHAR2 := 'JSP',
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_count OUT NOCOPY NUMBER,
130 x_msg_data OUT NOCOPY VARCHAR2,
131 p_x_sr_task_tbl IN OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_tbl_type,
132 p_x_mr_asso_tbl IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type
133 )
134 IS
135 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_NONROUTINE_JOB';
136 l_api_version CONSTANT NUMBER := 1.0;
137 l_return_status VARCHAR2(3);
138 l_msg_count NUMBER;
139 l_msg_data VARCHAR2(2000);
140 l_sr_task_rec ahl_prd_nonroutine_pvt.sr_task_rec_type;
141 l_err_msg_count NUMBER;
142 l_convert_validate_status VARCHAR2(3);
143 l_sr_status_id NUMBER;
144 -- Removing this cursor as status_code is obsoleted as per the update from SR Team and we should use
145 -- incident_status_id directly. - Balaji
146 /*
147 -- Begin Changes Vasu For SR Integration
148 CURSOR cs_sr_status IS
149 SELECT incident_status_id FROM
150 cs_incident_statuses
151 WHERE status_code = 'PLANNED'
152 AND incident_subtype = 'INC'
153 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
154 AND trunc(nvl(end_date_active,sysdate));
155
156 -- End Changes Vasu for SR Integration
157 */
158 -- NR MR ER - start
159 CURSOR c_get_sr_details(p_incident_id NUMBER)
160 IS
161 SELECT object_version_number
162 FROM CS_INCIDENTS
163 WHERE incident_id = p_incident_id;
164 -- NR MR ER - end
165
166 -- FP for ER 5716489 -- start
167 -- Cursor to fetch the workorder details .
168 CURSOR c_does_wo_exist (p_incident_id NUMBER)
169 IS
170 SELECT
171 wo.workorder_id,
172 wo.status_code
173 FROM
174 ahl_visit_tasks_b vtsk,
175 ahl_workorders wo,
176 ahl_unit_effectivities_b ue
177 WHERE
178 ue.cs_incident_id = p_incident_id
179 AND ue.unit_effectivity_id = vtsk.unit_effectivity_id
180 AND vtsk.visit_task_id = wo.visit_task_id
181 AND upper(vtsk.task_type_code) = 'SUMMARY';
182
183 l_does_wo_exist c_does_wo_exist%ROWTYPE;
184 -- FP for ER 5716489 -- end
185
186 BEGIN
187
188 -- Standard start of API savepoint
189 SAVEPOINT AHL_PROCESS_NONROUTINE_JOB_PVT;
190
191 -- Standard call to check for call compatibility
192 IF NOT FND_API.Compatible_API_Call(l_api_version,
193 p_api_version,
194 l_api_name,
195 G_PKG_NAME) THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 END IF;
198
199 -- Initialize message list if p_init_msg_list is set to TRUE
200 IF FND_API.To_Boolean(p_init_msg_list) THEN
201 FND_MSG_PUB.Initialize;
202 END IF;
203
204 -- Initialize Procedure return status to success
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 -- Enable Debug.
208 IF (G_DEBUG = 'Y') THEN
209 AHL_DEBUG_PUB.enable_debug;
210 END IF;
211
212 -- Add debug mesg.
213 IF (G_DEBUG = 'Y') THEN
214 AHL_DEBUG_PUB.debug('Begin private API:' || G_PKG_NAME || '.' || l_api_name);
215 END IF;
216
217 --------------------------------------------------------------------------------
218 -- Clear id's if the module type is 'JSP'.
219 -- Call value to id conversion and default_and_validate_param procedure.
220 -- If defaulting is successfully then call create service request
221 -- and create task api if operation_type is 'CREATE' else
222 -- call update service request if operation_type is 'UPDATE'
223 --------------------------------------------------------------------------------
224 IF ( p_x_sr_task_tbl.COUNT > 0) THEN
225
226 -- Call write to log procedure to log the input parameter
227 -- values for debug
228 IF (G_DEBUG = 'Y') THEN
229 write_to_log(p_sr_tasK_tbl => p_x_sr_task_tbl);
230 AHL_DEBUG_PUB.debug('INPUT - module_type :'||p_module_type);
231 END IF;
232
233
234 l_convert_validate_status := FND_API.G_RET_STS_SUCCESS;
235
236 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
237
238 -- Add the logic
239
240 l_sr_task_rec := p_x_sr_task_tbl(i);
241
242 IF upper(p_module_type) = 'JSP' THEN
243
244 IF upper(l_sr_task_rec.operation_type) = 'CREATE' THEN
245
246 l_sr_task_rec.type_id := FND_API.G_MISS_NUM;
247 l_sr_task_rec.severity_id := FND_API.G_MISS_NUM;
248 l_sr_task_rec.urgency_id := FND_API.G_MISS_NUM;
249 --problem code lov is modified to return problem_code instead of meaning
250 --for bug #4729005. Hence no need to convert value to id.
251 --l_sr_task_rec.problem_code := FND_API.G_MISS_CHAR;
252 -- NR MR ER -- start
253 --l_sr_task_rec.resolution_code := FND_API.G_MISS_CHAR;
254 -- NR MR ER -- end
255 l_sr_task_rec.visit_id := FND_API.G_MISS_NUM;
256 l_sr_task_rec.instance_id := FND_API.G_MISS_NUM;
257
258 ELSIF upper(l_sr_task_rec.operation_type) = 'UPDATE' THEN
259
260 l_sr_task_rec.urgency_id := FND_API.G_MISS_NUM;
261 --problem code lov is modified to return problem_code instead of meaning
262 --for bug #4729005. Hence no need to convert value to id.
263 --l_sr_task_rec.problem_code := FND_API.G_MISS_CHAR;
264 -- NR MR ER -- start
265 --l_sr_task_rec.resolution_code := FND_API.G_MISS_CHAR;
266 -- NR MR ER -- end
267 END IF;
268
269 END IF;
270
271 IF (G_DEBUG = 'Y') THEN
272 AHL_DEBUG_PUB.debug('PROC : Calling Convert_val_to_id procedure');
273 END IF;
274
275 -- Call value to id conversion
276 Convert_val_to_id( p_x_sr_task_rec => l_sr_task_rec,
277 x_return_status => l_return_status);
278
279 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
280 l_convert_validate_status := FND_API.G_RET_STS_ERROR;
281 END IF;
282
283 IF (G_DEBUG = 'Y') THEN
284 AHL_DEBUG_PUB.debug('PROC : Calling Default_and_validate_param procedure');
285 END IF;
286
287 -- Call the Default and validate param procedure
288 Default_and_validate_param( p_x_sr_task_rec => l_sr_task_rec,
289 p_module_type => p_module_type,
290 x_return_status => l_return_status);
291
292 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
293 l_convert_validate_status := FND_API.G_RET_STS_ERROR;
294 END IF;
295
296 p_x_sr_task_tbl(i) := l_sr_task_rec;
297
298 END LOOP;
299
300 -- Check For Errors.
301 IF( l_convert_validate_status <> FND_API.G_RET_STS_SUCCESS ) THEN
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304
305 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
306
307 l_sr_task_rec := p_x_sr_task_tbl(i);
308
309 IF ( upper(l_sr_task_rec.operation_type) = 'CREATE') THEN
310
311 IF (G_DEBUG = 'Y') THEN
312 AHL_DEBUG_PUB.debug('PROC : Calling the Create SR procedure');
313 END IF;
314
315 -- Call Create Service Request procedure
316
317 Create_sr( p_x_sr_task_rec => l_sr_task_rec,
318 x_return_status => l_return_status);
319
320
321 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
322 RAISE FND_API.G_EXC_ERROR;
323 END IF;
324
325 ELSIF ( upper(l_sr_task_rec.operation_type) = 'UPDATE' ) THEN
326
327 IF (G_DEBUG = 'Y') THEN
328 AHL_DEBUG_PUB.debug('PROC : Calling the Update SR procedure');
329 END IF;
330
331 -- Call Update Service Request procedure
332 -- MR NR ER -- start
333 IF p_module_type IS NULL OR p_module_type <> 'SR_OA' THEN
334 Update_sr( p_x_sr_task_rec => l_sr_task_rec,
335 x_return_status => l_return_status);
336
337 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
338 RAISE FND_API.G_EXC_ERROR;
339 END IF;
340
341 -- FP for ER 5716489 -- start
342 --- If the mode is update , then check if the NR has a corresponding workorder created or not
343 -- and set the flags accordingly .
344
345 --1. Query if the NR has a workorder created !
346
347 OPEN c_does_wo_exist(l_sr_task_rec.Incident_id);
348 FETCH c_does_wo_exist INTO l_does_wo_exist;
349 CLOSE c_does_wo_exist;
350
351 --2. Check if a workorder exists for the NR
352 IF ( l_does_wo_exist.workorder_id IS NOT NULL ) THEN
353
354 -- Set the p_x_task_tbl(i).WO_Create_flag to Y
355 p_x_sr_task_tbl(i).WO_Create_flag := 'Y';
356
357 --Check the Status of the workorder and set the p_x_sr_task_tbl(i).WO_Release_flag
358 IF ( l_does_wo_exist.status_code = '3') THEN
359 p_x_sr_task_tbl(i).WO_Release_flag := 'Y';
360 ELSE
361 p_x_sr_task_tbl(i).WO_Release_flag := 'N';
362 END IF;
363 ELSE
364 -- If the workorder Id is null, ie; a work order does nt exist for the NR
365 -- Set the he p_x_task_tbl(i).WO_Create_flag to N
366 p_x_sr_task_tbl(i).WO_Create_flag := 'N';
367 END IF;
368 -- FP for ER 5716489 -- end
369
370 END IF;
371 -- MR NR ER -- end
372 END IF;
373
374 p_x_sr_task_tbl(i) := l_sr_task_rec;
375
376 END LOOP;
377
378 -- NR MR ER -- start
379 /*
380 -- initialize stack if any warning messages from CS APIs exist.
381 IF (FND_MSG_PUB.count_msg > 0) THEN
382 FND_MSG_PUB.Initialize;
383 END IF;
384
385 IF (G_DEBUG = 'Y') THEN
386 AHL_DEBUG_PUB.debug('PROC : Calling the Create Task procedure');
387 END IF;
388
389 -- call Create VWP Task Api
390
391 Create_task( p_x_task_tbl => p_x_sr_task_tbl,
392 x_return_status => l_return_status);
393
394 l_msg_count := FND_MSG_PUB.count_msg;
395
396 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS or l_msg_count>0) THEN
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399 */
400 -- NR MR ER - End
401
402 -- NR MR ER - start
403 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
404 fnd_log.string(
405 fnd_log.level_statement,
406 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
407 'p_x_mr_asso_tbl.COUNT ->'||p_x_mr_asso_tbl.COUNT
408 );
409 END IF;
410
411 IF (
412 upper(l_sr_task_rec.operation_type) = 'CREATE'
413 OR
414 (
415 upper(l_sr_task_rec.operation_type) = 'UPDATE'
416 AND
417 p_x_mr_asso_tbl.COUNT > 0
418 )
419 )
420 THEN
421 Process_Mr(
422 p_x_task_tbl => p_x_sr_task_tbl,
423 p_mr_assoc_tbl => p_x_mr_asso_tbl,
424 p_module_type => p_module_type,
425 x_return_status => x_return_status,
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data
428 );
429
430 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
431 fnd_log.string(
432 fnd_log.level_statement,
433 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
434 'After calling Process_Mr...Return status->'||x_return_status
435 );
436 END IF;
437
438 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
439 RAISE FND_API.G_EXC_ERROR;
440 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442 END IF;
443 END IF;
444
445 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
446 fnd_log.string(
447 fnd_log.level_statement,
448 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
449 'After Process_Mr API'
450 );
451 END IF;
452 -- NR MR ER - end
453
454 -- Modified by VSUNDARA For SR Integration
455 --- Change the SR STATUS as Planned
456 -- Removing the code as status_code is obsoleted as per the update from SR Team and we should use
457 -- incident_status_id directly. - Balaji
458 /*
459 OPEN cs_sr_status;
460 FETCH cs_sr_status INTO l_sr_status_id;
461 IF ( cs_sr_status%NOTFOUND) THEN
462 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_STATUS_ERROR');
463 Fnd_Msg_Pub.ADD;
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 END IF;
466 */
467
468 l_sr_status_id := G_SR_PLANNED_STATUS_ID;
469
470 FOR i IN p_x_sr_task_tbl.FIRST..p_x_sr_task_tbl.LAST LOOP
471
472 l_sr_task_rec := p_x_sr_task_tbl(i);
473
474 IF ( upper(l_sr_task_rec.operation_type) = 'CREATE') THEN
475
476 -- FP for ER 5716489 -- start
477 -- Do not call the update SR Api in cases where a non-routine is created without workorder .
478 IF (nvl(upper(l_sr_task_rec.WO_Create_flag),'Y') = 'Y')
479 THEN
480
481 l_sr_task_rec.Status_id := l_sr_status_id;
482 -- NR MR ER -- start
483 --l_sr_task_rec.incident_object_version_number := 1 ;
484 OPEN c_get_sr_details(l_sr_task_rec.Incident_id);
485 FETCH c_get_sr_details INTO l_sr_task_rec.incident_object_version_number;
486 CLOSE c_get_sr_details;
487
488 -- NR MR ER -- end
489 IF (G_DEBUG = 'Y') THEN
490 AHL_DEBUG_PUB.debug('PROC : Calling the Update SR procedure');
491 END IF;
492 -- Call Update Service Request procedure
493 Update_sr( p_x_sr_task_rec => l_sr_task_rec,
494 x_return_status => l_return_status);
495
496 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499
500 END IF;
501 -- FP for ER 5716489 -- end
502
503 IF(( l_sr_task_rec.object_id IS NOT NULL AND l_sr_task_rec.object_id <> FND_API.G_MISS_NUM )
504 AND (l_sr_task_rec.object_type = 'AHL_PRD_DISP')) THEN
505
506 AHL_PRD_DISP_UTIL_PVT.Create_SR_Disp_Link (
507
508 p_api_version => 1.0,
509 p_init_msg_list => FND_API.G_TRUE,
510 p_commit => FND_API.G_FALSE,
511 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
512 x_return_status => x_return_status,
513 x_msg_count => l_msg_count,
514 x_msg_data => l_msg_data,
515 p_service_request_id => l_sr_task_rec.incident_id,
516 p_disposition_id => l_sr_task_rec.object_id,
517 x_link_id => l_sr_task_rec.link_id
518 );
519
520 IF ( upper(l_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523 END IF ;
524 END IF;
525
526 END LOOP;
527
528
529
530
531 -- END Changes
532
533
534
535
536 END IF;
537
538 -- initialize stack if any warning messages from CS APIs exist.
539 IF (FND_MSG_PUB.count_msg > 0) THEN
540 FND_MSG_PUB.Initialize;
541 END IF;
542
543 -- Standard check of p_commit
544 IF FND_API.TO_BOOLEAN(p_commit) THEN
545 COMMIT WORK;
546 END IF;
547
548 IF (G_DEBUG = 'Y') THEN
549 AHL_DEBUG_PUB.debug('END - Successfully completion of '||G_PKG_NAME||'.'||l_api_name||' API ');
550 END IF;
551
552 -- Count and Get messages
553 FND_MSG_PUB.count_and_get
554 ( p_encoded => fnd_api.g_false,
555 p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558
559 -- Disable debug (if enabled)
560 IF (G_DEBUG = 'Y') THEN
561 AHL_DEBUG_PUB.disable_debug;
562 END IF;
563
564 EXCEPTION
565 WHEN FND_API.G_EXC_ERROR THEN
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 Rollback to AHL_PROCESS_NONROUTINE_JOB_PVT;
568 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
569 p_data => x_msg_data,
570 p_encoded => fnd_api.g_false);
571
572 -- Disable debug
573 IF (G_DEBUG = 'Y') THEN
574 AHL_DEBUG_PUB.disable_debug;
575 END IF;
576
577
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
580 Rollback to AHL_PROCESS_NONROUTINE_JOB_PVT;
581 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
582 p_data => x_msg_data,
583 p_encoded => fnd_api.g_false);
584
585 -- Disable debug
586 IF (G_DEBUG = 'Y') THEN
587 AHL_DEBUG_PUB.disable_debug;
588 END IF;
589
590 WHEN OTHERS THEN
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 Rollback to AHL_PROCESS_NONROUTINE_JOB_PVT;
593 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
594 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
595 p_procedure_name => 'Process_Nonroutine_Job',
596 p_error_text => SUBSTR(SQLERRM,1,240));
597 END IF;
598 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
599 p_data => x_msg_data,
600 p_encoded => fnd_api.g_false);
601
602 -- Disable debug
603 IF (G_DEBUG = 'Y') THEN
604 AHL_DEBUG_PUB.disable_debug;
605 END IF;
606
607 END Process_nonroutine_job;
608
609 --------------------------------------------
610 -- Local Procedure Definitions follow --
611 --------------------------------------------
612 --------------------------------------------
613 -- Convert value to id --
614 --------------------------------------------
615
616 ----------------------------------------------
617 -- Convert_val_to_id procedure will convert
618 -- values to id's only if the id's are null
619 ----------------------------------------------
620 PROCEDURE Convert_val_to_id(
621 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
622 x_return_status OUT NOCOPY VARCHAR2
623 ) IS
624
625 l_customer_id NUMBER;
626 l_customer_name VARCHAR2(360);
627 l_contact_id NUMBER;
628 l_contact_name VARCHAR2(360);
629
630 CURSOR sr_problem_code (p_meaning IN VARCHAR2) IS
631 SELECT lookup_code FROM fnd_lookup_values_vl
632 WHERE lookup_type = 'REQUEST_PROBLEM_CODE'
633 AND enabled_flag = 'Y'
634 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
635 AND trunc(nvl(end_date_active,sysdate))
636 AND upper(meaning) = upper(p_meaning);
637
638 CURSOR sr_resolution_code (p_meaning IN VARCHAR2) IS
639 SELECT lookup_code FROM fnd_lookup_values_vl
640 WHERE lookup_type = 'REQUEST_RESOLUTION_CODE'
641 AND enabled_flag = 'Y'
642 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
643 AND trunc(nvl(end_date_active,sysdate))
644 AND upper(meaning) = upper(p_meaning);
645
646 CURSOR sr_customer_product(p_instance_number IN VARCHAR2) IS
647 SELECT instance_id FROM csi_item_instances
648 WHERE instance_number = p_instance_number;
649
650 CURSOR ahl_visit(p_visit_number IN NUMBER) IS
651 SELECT visit_id FROM ahl_visits_b
652 WHERE visit_number = p_visit_number;
653 -- Begin Changes by VSUNDARA
654 -- TO validate the Instance Owner
655 CURSOR ahl_instance_owner(p_instance_number IN VARCHAR2) IS
656 SELECT OWNER_PARTY_ID
657 FROM csi_item_instances
658 WHERE instance_number = p_instance_number;
659 -- END Changes
660
661 BEGIN
662
663 -- Initialize Procedure return status to success
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665
666 IF ( upper(p_x_sr_task_rec.operation_type) = 'CREATE' ) THEN
667
668 -- Derive the Customer id, if its null.
669 -- If customer id is not null derive the customer name and id
670 -- and check the customer name against the input value, if <>
671 -- return error msg. If only customer name is passed then
672 -- derive the customer id and name.
673
674 IF( p_x_sr_task_rec.customer_id is not null and
675 p_x_sr_task_rec.customer_id <> FND_API.G_MISS_NUM
676 and (p_x_sr_task_rec.customer_name is not null and
677 p_x_sr_task_rec.customer_name <> FND_API.G_MISS_CHAR)) THEN
678 BEGIN
679 Select party_name
680 into
681 l_customer_name
682 from hz_parties
683 where party_id
684 = p_x_sr_task_rec.customer_id;
685
686 IF (l_customer_name <> nvl(p_x_sr_task_rec.customer_name,l_customer_name) and
687 (p_x_sr_task_rec.customer_name <> FND_API.G_MISS_CHAR) ) THEN
688 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_NAME_MISMATCH');
689 Fnd_Message.SET_TOKEN('CUST_NAME',p_x_sr_task_rec.customer_name);
690 Fnd_Message.SET_TOKEN('CUST_ID',p_x_sr_task_rec.customer_id);
691 Fnd_Msg_Pub.ADD;
692 x_return_status := FND_API.G_RET_STS_ERROR;
693 END IF;
694
695 EXCEPTION
696 WHEN NO_DATA_FOUND THEN
697 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_ID_INVALID');
698 Fnd_Message.SET_TOKEN('CUST_ID',p_x_sr_task_rec.customer_id);
699 Fnd_Msg_Pub.ADD;
700 x_return_status := FND_API.G_RET_STS_ERROR;
701 WHEN TOO_MANY_ROWS THEN
702 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_ID_NOT_UNIQUE');
703 Fnd_Message.SET_TOKEN('CUST_ID',p_x_sr_task_rec.customer_id);
704 Fnd_Msg_Pub.ADD;
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 END;
707
708
709 ELSIF ( (p_x_sr_task_rec.customer_id is null or
710 p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM)
711 and (p_x_sr_task_rec.customer_name is not null and
712 p_x_sr_task_rec.customer_name <> FND_API.G_MISS_CHAR)) THEN
713
714 BEGIN
715 Select party_id
716 into
717 l_customer_id
718 from hz_parties
719 where party_name = p_x_sr_task_rec.customer_name;
720 p_x_sr_task_rec.customer_id := l_customer_id;
721
722 EXCEPTION
723 WHEN NO_DATA_FOUND THEN
724 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_NAME_INVALID');
725 Fnd_Message.SET_TOKEN('CUST_NAME',p_x_sr_task_rec.customer_name);
726 Fnd_Msg_Pub.ADD;
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 WHEN TOO_MANY_ROWS THEN
729 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_NAME_NOT_UNIQUE');
730 Fnd_Message.SET_TOKEN('CUST_NAME',p_x_sr_task_rec.customer_name);
731 Fnd_Msg_Pub.ADD;
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 END;
734 -- NR MR ER - start
735 -- Balaji added following elseif clause to explicitly pass null to
736 -- SR API so that appropriate error msg is thrown.
737 -- Part of ER # 5550702
738 ELSIF p_x_sr_task_rec.customer_name is null THEN
739
740 p_x_sr_task_rec.customer_id := NULL;
741
742 END IF;
743 -- NR MR ER - end
744
745
746
747 -- Derive the Contact id, if its null and contact type in
748 -- 'RELATIONSHIP' or 'PERSON'.
749 -- If contact id is not null derive the contact name and id
750 -- and check the contact name against the input value, if <>
751 -- return error msg. If only contact name is passed then
752 -- derive the contact id and name.
753
754 IF (upper(p_x_sr_task_rec.contact_type) in ('PARTY_RELATIONSHIP','PERSON')) THEN
755
756 IF( p_x_sr_task_rec.contact_id is not null and
757 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
758 and ( p_x_sr_task_rec.contact_name is not null and
759 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
760 BEGIN
761 Select party_name
762 into
763 l_contact_name
764 from hz_parties
765 where party_id
766 = p_x_sr_task_rec.contact_id;
767
768
769 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
770 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
771 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
772 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
773 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
774 Fnd_Msg_Pub.ADD;
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 END IF;
777
778 EXCEPTION
779 WHEN NO_DATA_FOUND THEN
780 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
781 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
782 Fnd_Msg_Pub.ADD;
783 x_return_status := FND_API.G_RET_STS_ERROR;
784 WHEN TOO_MANY_ROWS THEN
785 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
786 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
787 Fnd_Msg_Pub.ADD;
788 x_return_status := FND_API.G_RET_STS_ERROR;
789 END;
790
791
792 ELSIF ( (p_x_sr_task_rec.contact_id is null or
793 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
794 and ( p_x_sr_task_rec.contact_name is not null and
795 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
796
797 BEGIN
798 Select party_id
799 into
800 l_contact_id
801 from hz_parties
802 where party_name = p_x_sr_task_rec.contact_name;
803
804 p_x_sr_task_rec.contact_id := l_contact_id;
805
806
807 EXCEPTION
808 WHEN NO_DATA_FOUND THEN
809 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
810 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
811 Fnd_Msg_Pub.ADD;
812 x_return_status := FND_API.G_RET_STS_ERROR;
813 WHEN TOO_MANY_ROWS THEN
814 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
815 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
816 Fnd_Msg_Pub.ADD;
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 END;
819 -- NR MR ER -- start
820 -- Balaji added following elseif clause to explicitly pass null to
821 -- SR API so that appropriate error msg is thrown.
822 -- Part of MR NR ER
823 ELSIF p_x_sr_task_rec.contact_name is null THEN
824
825 p_x_sr_task_rec.contact_id := NULL;
826
827 END IF;
828 -- NR MR ER -- end
829
830 END IF;
831
832 -- Derive the Contact id, if its null and contact type
833 -- 'EMPLOYEE'.
834 -- If contact id is not null derive the contact name and id
835 -- and check the contact name against the input value, if <>
836 -- return error msg. If only contact name is passed then
837 -- derive the contact id and name.
838
839 IF (upper(p_x_sr_task_rec.contact_type) = 'EMPLOYEE') THEN
840
841 IF( p_x_sr_task_rec.contact_id is not null and
842 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
843 and ( p_x_sr_task_rec.contact_name is not null and
844 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
845 BEGIN
846 Select full_name
847 into
848 l_contact_name
849 from per_people_f
850 where person_id
851 = p_x_sr_task_rec.contact_id
852 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
853 and trunc(nvl(effective_end_date,sysdate));
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 person_id
886 into
887 l_contact_id
888 from per_people_f
889 where full_name = p_x_sr_task_rec.contact_name
890 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
891 and trunc(nvl(effective_end_date,sysdate));
892
893 p_x_sr_task_rec.contact_id := l_contact_id;
894
895 EXCEPTION
896 WHEN NO_DATA_FOUND THEN
897 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
898 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
899 Fnd_Msg_Pub.ADD;
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 WHEN TOO_MANY_ROWS THEN
902 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
903 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
904 Fnd_Msg_Pub.ADD;
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 END;
907 -- NR MR ER -- start
908 -- Balaji added following elseif clause to explicitly pass null to
909 -- SR API so that appropriate error msg is thrown.
910 -- Part of NR MR ER.
911 ELSIF p_x_sr_task_rec.contact_name is null THEN
912
913 p_x_sr_task_rec.contact_id := NULL;
914
915 END IF;
916 -- NR MR ER -- end
917 END IF;
918
919
920 -- Derive the visit id from visit number
921 IF ((p_x_sr_task_rec.visit_id is null or
922 p_x_sr_task_rec.visit_id = FND_API.G_MISS_NUM) and
923 p_x_sr_task_rec.visit_number is not null) THEN
924
925 OPEN ahl_visit(p_x_sr_task_rec.visit_number);
926 FETCH ahl_visit INTO p_x_sr_task_rec.visit_id;
927 CLOSE ahl_visit;
928
929 END IF;
930
931
932 -- Derive the instance id from instance number
933 IF ((p_x_sr_task_rec.instance_id is null or
934 p_x_sr_task_rec.instance_id = FND_API.G_MISS_NUM) and
935 p_x_sr_task_rec.instance_number is not null ) THEN
936
937 OPEN sr_customer_product(p_x_sr_task_rec.instance_number);
938 FETCH sr_customer_product INTO p_x_sr_task_rec.instance_id;
939 CLOSE sr_customer_product;
940
941 END IF;
942
943
944 -- Derive the problem code from problem meaning
945 IF ((p_x_sr_task_rec.problem_code is null or
946 p_x_sr_task_rec.problem_code = FND_API.G_MISS_CHAR) and
947 p_x_sr_task_rec.problem_meaning is not null ) THEN
948
949 OPEN sr_problem_code(p_x_sr_task_rec.problem_meaning);
950 FETCH sr_problem_code INTO p_x_sr_task_rec.problem_code;
951 CLOSE sr_problem_code;
952
953 -- NR MR ER - start
954 -- Balaji added following check to throw appropriate
955 -- error message if invalid problem code is entered.
956 -- SR API doesnt throw any error if the problem code is invalid neither
957 -- it updates invalid value. Hence throwing error explicitly
958 -- Part of NR MR ER.
959 IF p_x_sr_task_rec.problem_code IS NULL THEN
960 Fnd_Message.SET_NAME('AHL','AHL_PRD_PROBLEM_CODE_INVALID');
961 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.problem_meaning);
962 Fnd_Msg_Pub.ADD;
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 END IF;
965 -- NR MR ER - end
966
967 END IF;
968
969
970 -- Derive the resolution code from resolution meaning.
971 IF ((p_x_sr_task_rec.resolution_code is null or
972 p_x_sr_task_rec.resolution_code = FND_API.G_MISS_CHAR) and
973 p_x_sr_task_rec.resolution_meaning is not null ) THEN
974
975 OPEN sr_resolution_code(p_x_sr_task_rec.resolution_meaning);
976 FETCH sr_resolution_code INTO p_x_sr_task_rec.resolution_code;
977 CLOSE sr_resolution_code;
978
979 -- NR MR ER -- start
980 -- Balaji added following check to throw appropriate
981 -- error message if invalid resolution code is entered.
982 -- SR API doesnt throw any error if the resolution code is invalid neither
983 -- it updates invalid value. Hence throwing error explicitly
984 -- Part of NR MR ER
985 IF p_x_sr_task_rec.resolution_code IS NULL THEN
986 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_INV');
987 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.resolution_meaning);
988 Fnd_Msg_Pub.ADD;
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 END IF;
991 -- NR MR ER -- end
992
993 END IF;
994
995
996
997 ELSIF ( upper(p_x_sr_task_rec.operation_type) = 'UPDATE') THEN
998
999
1000 -- Derive the Contact id, if its null and contact type in
1001 -- 'RELATIONSHIP' or 'PERSON'.
1002 -- If contact id is not null derive the contact name and id
1003 -- and check the contact name against the input value, if <>
1004 -- return error msg. If only contact name is passed then
1005 -- derive the contact id and name.
1006
1007 IF (upper(p_x_sr_task_rec.contact_type) in ('PARTY_RELATIONSHIP','PERSON')) THEN
1008
1009 IF( p_x_sr_task_rec.contact_id is not null and
1010 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
1011 and ( p_x_sr_task_rec.contact_name is not null and
1012 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
1013 BEGIN
1014 Select party_name
1015 into
1016 l_contact_name
1017 from hz_parties
1018 where party_id
1019 = p_x_sr_task_rec.contact_id;
1020
1021 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
1022 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
1023 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
1024 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1025 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1026 Fnd_Msg_Pub.ADD;
1027 x_return_status := FND_API.G_RET_STS_ERROR;
1028 END IF;
1029
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
1034 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1035 Fnd_Msg_Pub.ADD;
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037 WHEN TOO_MANY_ROWS THEN
1038 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
1039 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1040 Fnd_Msg_Pub.ADD;
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042 END;
1043
1044
1045 ELSIF ( (p_x_sr_task_rec.contact_id is null or
1046 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
1047 and ( p_x_sr_task_rec.contact_name is not null and
1048 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
1049
1050 BEGIN
1051 Select party_id
1052 into
1053 l_contact_id
1054 from hz_parties
1055 where party_name = p_x_sr_task_rec.contact_name;
1056
1057 p_x_sr_task_rec.contact_id := l_contact_id;
1058
1059 EXCEPTION
1060 WHEN NO_DATA_FOUND THEN
1061 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
1062 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1063 Fnd_Msg_Pub.ADD;
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 WHEN TOO_MANY_ROWS THEN
1066 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
1067 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1068 Fnd_Msg_Pub.ADD;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 END;
1071 -- NR MR ER -- start
1072 -- Balaji added following elseif clause to explicitly pass null to
1073 -- SR API so that appropriate error msg is thrown.
1074 -- Part of NR MR ER.
1075 ELSIF p_x_sr_task_rec.contact_name is null THEN
1076
1077 p_x_sr_task_rec.contact_id := NULL;
1078
1079 END IF;
1080 -- NR MR ER -- end
1081
1082 END IF;
1083
1084
1085 -- Derive the Contact id, if its null and contact type
1086 -- 'EMPLOYEE'.
1087 -- If contact id is not null derive the contact name and id
1088 -- and check the contact name against the input value, if <>
1089 -- return error msg. If only contact name is passed then
1090 -- derive the contact id and name.
1091
1092 IF (upper(p_x_sr_task_rec.contact_type) = 'EMPLOYEE') THEN
1093
1094 IF( p_x_sr_task_rec.contact_id is not null and
1095 p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
1096 and ( p_x_sr_task_rec.contact_name is not null and
1097 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR)) THEN
1098 BEGIN
1099 Select full_name
1100 into
1101 l_contact_name
1102 from per_people_f
1103 where person_id
1104 = p_x_sr_task_rec.contact_id
1105 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
1106 and trunc(nvl(effective_end_date,sysdate));
1107
1108 IF(l_contact_name <> nvl(p_x_sr_task_rec.contact_name,l_contact_name) and
1109 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
1110 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_MISMATCH');
1111 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1112 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1113 Fnd_Msg_Pub.ADD;
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115 END IF;
1116
1117 EXCEPTION
1118 WHEN NO_DATA_FOUND THEN
1119 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_INVALID');
1120 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1121 Fnd_Msg_Pub.ADD;
1122 x_return_status := FND_API.G_RET_STS_ERROR;
1123 WHEN TOO_MANY_ROWS THEN
1124 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_ID_NOT_UNIQUE');
1125 Fnd_Message.SET_TOKEN('CONT_ID',p_x_sr_task_rec.contact_id);
1126 Fnd_Msg_Pub.ADD;
1127 x_return_status := FND_API.G_RET_STS_ERROR;
1128 END;
1129
1130
1131 ELSIF ( (p_x_sr_task_rec.contact_id is null or
1132 p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)
1133 and ( p_x_sr_task_rec.contact_name is not null and
1134 p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR)) THEN
1135
1136 BEGIN
1137 Select person_id
1138 into
1139 l_contact_id
1140 from per_people_f
1141 where full_name = p_x_sr_task_rec.contact_name
1142 and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
1143 and trunc(nvl(effective_end_date,sysdate));
1144
1145 p_x_sr_task_rec.contact_id := l_contact_id;
1146
1147 EXCEPTION
1148 WHEN NO_DATA_FOUND THEN
1149 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_INVALID');
1150 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1151 Fnd_Msg_Pub.ADD;
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 WHEN TOO_MANY_ROWS THEN
1154 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_NAME_NOT_UNIQUE');
1155 Fnd_Message.SET_TOKEN('CONT_NAME',p_x_sr_task_rec.contact_name);
1156 Fnd_Msg_Pub.ADD;
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 END;
1159 -- NR MR ER -- start
1160 -- Balaji added following elseif clause to explicitly pass null to
1161 -- SR API so that appropriate error msg is thrown.
1162 -- Part of NR MR ER.
1163 ELSIF p_x_sr_task_rec.contact_name is null THEN
1164
1165 p_x_sr_task_rec.contact_id := NULL;
1166
1167 END IF;
1168 -- NR MR ER -- end
1169
1170 END IF;
1171
1172
1173 -- Derive the problem code from problem meaning.
1174 IF ((p_x_sr_task_rec.problem_code is null or
1175 p_x_sr_task_rec.problem_code = FND_API.G_MISS_CHAR) and
1176 p_x_sr_task_rec.problem_meaning is not null ) THEN
1177
1178 OPEN sr_problem_code(p_x_sr_task_rec.problem_meaning);
1179 FETCH sr_problem_code INTO p_x_sr_task_rec.problem_code;
1180 CLOSE sr_problem_code;
1181
1182 -- NR MR ER -- start
1183 -- Balaji added following check to throw appropriate
1184 -- error message if invalid problem code is entered.
1185 -- SR API doesnt throw any error if the problem code is invalid neither
1186 -- it updates invalid value. Hence throwing error explicitly
1187 -- Part of NR MR ER.
1188 IF p_x_sr_task_rec.problem_code IS NULL THEN
1189 Fnd_Message.SET_NAME('AHL','AHL_PRD_PROBLEM_CODE_INVALID');
1190 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.problem_meaning);
1191 Fnd_Msg_Pub.ADD;
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193 END IF;
1194 -- NR MR ER -- end
1195
1196 END IF;
1197
1198 -- Derive the resolution code from resolution meaning.
1199 IF ((p_x_sr_task_rec.resolution_code is null or
1200 p_x_sr_task_rec.resolution_code = FND_API.G_MISS_CHAR) and
1201 p_x_sr_task_rec.resolution_meaning is not null ) THEN
1202
1203 OPEN sr_resolution_code(p_x_sr_task_rec.resolution_meaning);
1204 FETCH sr_resolution_code INTO p_x_sr_task_rec.resolution_code;
1205 CLOSE sr_resolution_code;
1206
1207 -- NR MR ER -- start
1208 -- Balaji added following check to throw appropriate
1209 -- error message if invalid resolution code is entered.
1210 -- SR API doesnt throw any error if the resolution code is invalid neither
1211 -- it updates invalid value. Hence throwing error explicitly
1212 -- Part of NR MR ER
1213 IF p_x_sr_task_rec.resolution_code IS NULL THEN
1214 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_INV');
1215 Fnd_Message.SET_TOKEN('CODE',p_x_sr_task_rec.resolution_meaning);
1216 Fnd_Msg_Pub.ADD;
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 END IF;
1219 -- NR MR ER -- end
1220
1221 END IF;
1222
1223 -- NR MR ER -- start
1224 -- Balaji added the code for OGMA ER (Adding MRs to Non-Routine)
1225 -- Begin
1226 -- Derive the visit id from visit number
1227 IF ((p_x_sr_task_rec.visit_id is null or
1228 p_x_sr_task_rec.visit_id = FND_API.G_MISS_NUM) and
1229 p_x_sr_task_rec.visit_number is not null) THEN
1230
1231 OPEN ahl_visit(p_x_sr_task_rec.visit_number);
1232 FETCH ahl_visit INTO p_x_sr_task_rec.visit_id;
1233 CLOSE ahl_visit;
1234
1235 END IF;
1236 -- NR MR ER -- end
1237
1238 END IF;
1239
1240 END Convert_val_to_id;
1241
1242
1243 --------------------------------------------
1244 -- Default and validate the parameters
1245 --------------------------------------------
1246
1247 ------------------------------------------------------------
1248 -- Default_and_validate_param procedure checks if
1249 -- required id's/values are passed, If not will derive
1250 -- from profile. If the profile values are null then
1251 -- it will either default the values or return an
1252 -- error message and status.
1253 ------------------------------------------------------------
1254 PROCEDURE Default_and_validate_param(
1255 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
1256 p_module_type IN VARCHAR2,
1257 x_return_status OUT NOCOPY VARCHAR2
1258 ) IS
1259
1260 l_incident_status_id NUMBER;
1261 l_employee_id NUMBER;
1262 dummy VARCHAR2(3);
1263 l_wo_name VARCHAR2(80);
1264 l_instance_num VARCHAR2(30);
1265 l_quantity NUMBER; --amsriniv. ER 6014567
1266 l_owner_id NUMBER;
1267 l_return_status VARCHAR2(1);
1268 l_dummy VARCHAR2(1); --amsriniv. ER 6014567
1269
1270 -- Removing cursor cs_sr_status as status_code is obsoleted as per the update from SR Team
1271 -- and we should use incident_status_id directly. - Balaji
1272 /*
1273 CURSOR cs_sr_status IS
1274 SELECT incident_status_id FROM
1275 cs_incident_statuses
1276 WHERE status_code = 'OPEN'
1277 AND incident_subtype = 'INC'
1278 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
1279 AND trunc(nvl(end_date_active,sysdate));
1280 */
1281
1282 CURSOR cs_sr_severity_validate(p_severity_id IN NUMBER) IS
1283 SELECT csv.incident_severity_id
1284 FROM cs_incident_severities_vl csv,
1285 mfg_lookups mfl
1286 WHERE csv.incident_severity_id = p_severity_id
1287 AND mfl.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
1288 AND trunc(sysdate) between trunc(nvl(csv.start_date_active,sysdate))
1289 AND trunc(nvl(csv.end_date_active,sysdate));
1290
1291 CURSOR default_contact IS
1292 SELECT employee_id
1293 FROM fnd_user
1294 WHERE user_id = fnd_global.user_id;
1295
1296 CURSOR default_customer_type IS
1297 SELECT party_type
1298 FROM hz_parties
1299 WHERE party_id = fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME');
1300
1301 -- Changes made by by VSUNDARA For SR Integration
1302 CURSOR default_party_id(p_item_instance_number IN NUMBER) IS
1303 SELECT OWNER_PARTY_ID
1304 FROM csi_item_instances
1305 WHERE instance_id = p_item_instance_number;
1306
1307 CURSOR owner_customer_type(p_cust_id IN NUMBER) IS
1308 SELECT party_type
1309 FROM hz_parties
1310 WHERE party_id = p_cust_id;
1311
1312
1313 -- End Changes
1314
1315 --amsriniv ER 6014567 Begin
1316 CURSOR check_inst_nonserial(c_instance_id IN NUMBER, c_workorder_id IN NUMBER) IS
1317 SELECT 'X'
1318 FROM mtl_system_items_b mtl, csi_item_instances csi
1319 WHERE csi.instance_id = c_instance_id
1320 AND csi.inventory_item_id = mtl.inventory_item_id
1321 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)
1322 AND mtl.serial_number_control_code = 1;
1323
1324 CURSOR validate_quantity(c_instance_id IN NUMBER, c_wo_id IN NUMBER) IS
1325 SELECT csi.quantity
1326 FROM CSI_ITEM_INSTANCES csi,
1327 ahl_workorders wo
1328 WHERE csi.instance_id = c_instance_id
1329 AND csi.wip_job_id = WO.WIP_ENTITY_ID
1330 AND wo.workorder_id = c_wo_id
1331 AND csi.location_type_code = 'WIP'
1332 AND not exists (select 'x' from csi_ii_relationships
1333 where subject_id = csi.instance_id
1334 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1335 AND TRUNC(NVL(ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
1336 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE));
1337 --amsriniv ER 6014567 End
1338
1339 BEGIN
1340
1341 -- Initialize Procedure return status to success
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344
1345 IF (upper(p_x_sr_task_rec.operation_type) = 'CREATE') THEN
1346
1347
1348 -- Check if instance id is not null. If instance id
1349 -- is null then return error message.
1350 IF (p_x_sr_task_rec.instance_id is null or p_x_sr_task_rec.instance_id = FND_API.G_MISS_NUM) THEN
1351
1352 Fnd_Message.SET_NAME('AHL','AHL_PRD_INSTANCE_VALUE_REQ');
1353 Fnd_Msg_Pub.ADD;
1354 RAISE FND_API.G_EXC_ERROR;
1355
1356 END IF;
1357
1358 -- If originating work order id is null then
1359 -- return an error message.
1360 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
1361
1362 Fnd_Message.SET_NAME('AHL','AHL_PRD_TASK_ORG_WOID_REQ');
1363 Fnd_Msg_Pub.ADD;
1364 RAISE FND_API.G_EXC_ERROR;
1365
1366 END IF;
1367
1368 -- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
1369 -- Bug # 6447467 -- start
1370 -- Check if resolution_meaning is not null. If resolution_meaning
1371 -- is null then return error message.
1372
1373 IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
1374
1375 IF ( p_x_sr_task_rec.resolution_meaning IS NULL OR
1376 p_x_sr_task_rec.resolution_meaning = FND_API.G_MISS_CHAR) THEN
1377
1378 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_REQ');
1379 Fnd_Msg_Pub.ADD;
1380 RAISE FND_API.G_EXC_ERROR;
1381
1382 END IF;
1383 END IF;
1384 -- Bug # 6447467 -- End
1385
1386 -- Get message tokens
1387 --
1388 get_msg_token ( p_wo_id => p_x_sr_task_rec.originating_wo_id,
1389 p_instance_id => p_x_sr_task_rec.instance_id,
1390 x_wo_name => l_wo_name,
1391 x_instance_number => l_instance_num);
1392
1393
1394 -- rroy
1395 -- ACL Changes
1396 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_sr_task_rec.originating_wo_id,
1397 p_ue_id => NULL,
1398 p_visit_id => NULL,
1399 p_item_instance_id => NULL);
1400 IF l_return_status = FND_API.G_TRUE THEN
1401 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_CRT_SR_UNTLCKD');
1402 FND_MESSAGE.Set_Token('WO_NAME', l_wo_name);
1403 FND_MSG_PUB.ADD;
1404 RAISE FND_API.G_EXC_ERROR;
1405 END IF;
1406 -- rroy
1407 -- ACL Changes
1408
1409
1410 -- If type id is null then derive it
1411 -- from profile. If profile value is null then
1412 -- return an error message
1413
1414 IF ((p_x_sr_task_rec.type_id is null or p_x_sr_task_rec.type_id = FND_API.G_MISS_NUM)and
1415 (p_x_sr_task_rec.type_name is null or p_x_sr_task_rec.type_name = FND_API.G_MISS_CHAR)) THEN
1416 IF (fnd_profile.value('AHL_PRD_SR_TYPE') is not null) THEN
1417 p_x_sr_task_rec.type_id := fnd_profile.value('AHL_PRD_SR_TYPE');
1418 p_x_sr_task_rec.type_name := fnd_profile.value('AHL_PRD_SR_TYPE');
1419 ELSE
1420 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_TYPE_REQ');
1421 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1422 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1423 Fnd_Msg_Pub.ADD;
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 END IF;
1426
1427 END IF;
1428
1429
1430 -- If status is null then derive it from profile.
1431 -- If profile value is null then default it to OPEN.
1432 IF ((p_x_sr_task_rec.status_id is null or p_x_sr_task_rec.status_id = FND_API.G_MISS_NUM)and
1433 (p_x_sr_task_rec.status_name is null or p_x_sr_task_rec.status_name = FND_API.G_MISS_CHAR)) THEN
1434
1435 IF (fnd_profile.value('AHL_PRD_SR_STATUS') is not null) THEN
1436 p_x_sr_task_rec.status_id := fnd_profile.value('AHL_PRD_SR_STATUS');
1437 ELSE
1438 -- Removing cursor cs_sr_status as status_code is obsoleted as per the update from SR Team
1439 -- and we should use incident_status_id directly. - Balaji.
1440 /*
1441 OPEN cs_sr_status;
1442 IF( cs_sr_status%NOTFOUND) THEN
1443 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_STATUS_DEFAULT_ERR');
1444 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1445 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1446 Fnd_Msg_Pub.ADD;
1447 x_return_status := FND_API.G_RET_STS_ERROR;
1448 ELSE
1449 FETCH cs_sr_status INTO p_x_sr_task_rec.status_id;
1450 END IF;
1451
1452 CLOSE cs_sr_status;
1453 */
1454 p_x_sr_task_rec.status_id := G_SR_OPEN_STATUS_ID;
1455 END IF;
1456
1457 END IF;
1458
1459
1460 -- If severity is null then derive it from profile.
1461 -- If profile value is null then return an error message.
1462 IF ((p_x_sr_task_rec.severity_id is null or p_x_sr_task_rec.severity_id = FND_API.G_MISS_NUM)and
1463 (p_x_sr_task_rec.severity_name is null or p_x_sr_task_rec.severity_name = FND_API.G_MISS_CHAR)) THEN
1464
1465 IF (fnd_profile.value('AHL_PRD_SR_SEVERITY') is not null) THEN
1466 p_x_sr_task_rec.severity_id := fnd_profile.value('AHL_PRD_SR_SEVERITY');
1467
1468 -- Validate the severity value
1469 OPEN cs_sr_severity_validate(p_x_sr_task_rec.severity_id);
1470
1471 IF ( cs_sr_severity_validate%NOTFOUND) THEN
1472 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_SEVERITY_INVALID');
1473 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1474 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1475 Fnd_Msg_Pub.ADD;
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 END IF;
1478
1479 CLOSE cs_sr_severity_validate;
1480 ELSE
1481 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_SEVERITY_REQ');
1482 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1483 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1484 Fnd_Msg_Pub.ADD;
1485 x_return_status := FND_API.G_RET_STS_ERROR;
1486 END IF;
1487
1488 END IF;
1489
1490 -- Default incident date to sysdate if
1491 -- request date is null
1492 IF (p_x_sr_task_rec.request_date is null or
1493 p_x_sr_task_rec.request_date = FND_API.G_MISS_DATE) THEN
1494
1495 p_x_sr_task_rec.request_date := sysdate;
1496
1497 END IF;
1498
1499
1500 -- If summary is null then return an
1501 -- error message.
1502 IF (p_x_sr_task_rec.summary is null or p_x_sr_task_rec.summary = FND_API.G_MISS_CHAR) THEN
1503
1504 Fnd_Message.SET_NAME('AHL','AHL_PRD_SUMMARY_REQ');
1505 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1506 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1507 Fnd_Msg_Pub.ADD;
1508 x_return_status := FND_API.G_RET_STS_ERROR;
1509
1510 END IF;
1511
1512
1513 -- If duration is null then derive it from profile.
1514 -- If profile value is null then default it to 1.
1515 IF (p_x_sr_task_rec.duration is null or p_x_sr_task_rec.duration = FND_API.G_MISS_NUM) THEN
1516
1517 IF ( fnd_profile.value('AHL_PRD_TASK_EST_DURATION') is not null) THEN
1518 p_x_sr_task_rec.duration := fnd_profile.value('AHL_PRD_TASK_EST_DURATION');
1519 ELSE
1520 p_x_sr_task_rec.duration := 1;
1521 END IF;
1522 END IF;
1523
1524 -- Changes made by VSUNDARA For SR Integration
1525 IF (p_x_sr_task_rec.customer_id IS NULL or p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM ) THEN
1526 OPEN default_party_id(p_x_sr_task_rec.instance_id);
1527 FETCH default_party_id INTO p_x_sr_task_rec.customer_id ;
1528 --Just to Check
1529 Select party_name
1530 into
1531 p_x_sr_task_rec.customer_name
1532 from hz_parties
1533 where party_id = p_x_sr_task_rec.customer_id;
1534 IF ( default_party_id%NOTFOUND) THEN
1535 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CUST_ERROR');
1536 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1537 Fnd_Message.SET_TOKEN('INSTANCE_NUM',null);
1538 Fnd_Msg_Pub.ADD;
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 END IF;
1541 OPEN owner_customer_type(p_x_sr_task_rec.customer_id);
1542 FETCH owner_customer_type INTO p_x_sr_task_rec.customer_type;
1543 IF ( owner_customer_type%NOTFOUND) THEN
1544 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CUST_ERROR'); -- Customer Type is Invalid
1545 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1546 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1547 Fnd_Msg_Pub.ADD;
1548 x_return_status := FND_API.G_RET_STS_ERROR;
1549 END IF;
1550
1551 CLOSE owner_customer_type;
1552 -- Defalut contact is Same
1553 OPEN default_contact;
1554 FETCH default_contact INTO p_x_sr_task_rec.contact_id;
1555 IF ( default_contact%NOTFOUND ) THEN
1556 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CONT_ERROR');
1557 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1558 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1559 Fnd_Msg_Pub.ADD;
1560 x_return_status := FND_API.G_RET_STS_ERROR;
1561 ELSE
1562 p_x_sr_task_rec.contact_type := 'EMPLOYEE';
1563 END IF;
1564
1565 CLOSE default_contact;
1566
1567 ELSE
1568 --- Validation of the Customer ID with Owner of the Instance
1569 OPEN default_party_id(p_x_sr_task_rec.instance_id);
1570 FETCH default_party_id INTO l_owner_id ;
1571 IF( l_owner_id <> nvl(p_x_sr_task_rec.customer_id,l_owner_id) and
1572 (p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR) )THEN
1573 Fnd_Message.SET_NAME('AHL','AHL_PRD_INVALID_OWNER'); -- New Error Message Should be added
1574 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1575 Fnd_Msg_Pub.ADD;
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 END IF;
1578
1579 END IF;
1580
1581 --- End Changes
1582
1583 -- If Customer id and Contact id is null then
1584 -- Derive the Customer info from Profile and
1585 -- Contact info from fnd_user.user_id
1586 IF ((p_x_sr_task_rec.customer_id IS NULL or p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM ) and
1587 (p_x_sr_task_rec.contact_id IS NULL or p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)) THEN
1588 IF ( fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME') is not null ) THEN
1589
1590 OPEN default_customer_type;
1591 FETCH default_customer_type INTO p_x_sr_task_rec.customer_type;
1592
1593 IF ( default_customer_type%NOTFOUND) THEN
1594 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CUST_ERROR');
1595 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1596 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1597 Fnd_Msg_Pub.ADD;
1598 x_return_status := FND_API.G_RET_STS_ERROR;
1599 ELSE
1600 p_x_sr_task_rec.customer_id := fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME');
1601 END IF;
1602
1603 CLOSE default_customer_type;
1604
1605 OPEN default_contact;
1606 FETCH default_contact INTO p_x_sr_task_rec.contact_id;
1607 IF ( default_contact%NOTFOUND ) THEN
1608 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_CONT_ERROR');
1609 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1610 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1611 Fnd_Msg_Pub.ADD;
1612 x_return_status := FND_API.G_RET_STS_ERROR;
1613 ELSE
1614 p_x_sr_task_rec.contact_type := 'EMPLOYEE';
1615 END IF;
1616
1617 CLOSE default_contact;
1618
1619 ELSE
1620
1621 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_PROFILE_REQ');
1622 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1623 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1624 Fnd_Msg_Pub.ADD;
1625 x_return_status := FND_API.G_RET_STS_ERROR;
1626
1627 END IF;
1628
1629 END IF;
1630
1631
1632 -- If Customer id is not null but Customer type is
1633 -- null then return an error message.
1634 IF (p_x_sr_task_rec.customer_id is not null and
1635 (p_x_sr_task_rec.customer_type is null or p_x_sr_task_rec.customer_type = FND_API.G_MISS_CHAR)) THEN
1636
1637 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUST_TYPE_REQ');
1638 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1639 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1640 Fnd_Msg_Pub.ADD;
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642
1643 END IF;
1644
1645
1646 -- If Contact id is not null but Contact type is
1647 -- null then return an error message.
1648 IF (p_x_sr_task_rec.contact_id is not null and
1649 (p_x_sr_task_rec.contact_type is null or p_x_sr_task_rec.contact_type = FND_API.G_MISS_CHAR)) THEN
1650
1651 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_TYPE_REQ');
1652 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1653 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1654 Fnd_Msg_Pub.ADD;
1655 x_return_status := FND_API.G_RET_STS_ERROR;
1656
1657 END IF;
1658
1659
1660 -- If Customer value is not null but Contact
1661 -- is null then return an error message.
1662 IF (p_x_sr_task_rec.customer_id is not null and
1663 (p_x_sr_task_rec.contact_id is null or p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM)) THEN
1664
1665 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONTACT_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 -- If Contact is not null but Customer is null then
1672 -- return an error message.
1673 ELSIF((p_x_sr_task_rec.customer_id is null or p_x_sr_task_rec.customer_id = FND_API.G_MISS_NUM) and
1674 p_x_sr_task_rec.contact_id is not null ) THEN
1675
1676 Fnd_Message.SET_NAME('AHL','AHL_PRD_CUSTOMER_REQ');
1677 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1678 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1679 Fnd_Msg_Pub.ADD;
1680 x_return_status := FND_API.G_RET_STS_ERROR;
1681
1682 END IF;
1683
1684
1685 -- If visit id is null then return an error
1686 -- message.
1687 IF (p_x_sr_task_rec.visit_id is null or p_x_sr_task_rec.visit_id = FND_API.G_MISS_NUM) THEN
1688
1689 Fnd_Message.SET_NAME('AHL','AHL_PRD_VISIT_VALUE_REQ');
1690 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1691 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1692 Fnd_Msg_Pub.ADD;
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694
1695 END IF;
1696 --amsriniv. Issue 105. Begin ER 6014567
1697 IF (p_x_sr_task_rec.instance_quantity IS NOT NULL AND p_x_sr_task_rec.instance_quantity <= 0) THEN
1698 Fnd_Message.SET_NAME('AHL','AHL_POSITIVE_TSK_QTY');
1699 Fnd_Msg_Pub.ADD;
1700 x_return_status := FND_API.G_RET_STS_ERROR;
1701 END IF;
1702
1703 IF (upper(p_x_sr_task_rec.WO_Create_flag) = 'N') THEN
1704 OPEN check_inst_nonserial(p_x_sr_task_rec.Instance_id, p_x_sr_task_rec.originating_wo_id);
1705 FETCH check_inst_nonserial INTO l_dummy;
1706 IF (check_inst_nonserial%FOUND) THEN
1707 Fnd_Message.SET_NAME('AHL','AHL_NO_CREATE_WO_NONSER');
1708 Fnd_Message.SET_TOKEN('FIELD',l_instance_num);
1709 Fnd_Msg_Pub.ADD;
1710 x_return_status := FND_API.G_RET_STS_ERROR;
1711 END IF;
1712 CLOSE check_inst_nonserial;
1713 END IF;
1714
1715 IF (nvl(upper(p_x_sr_task_rec.move_qty_to_nr_workorder),'N') = 'Y') THEN
1716 IF (upper(p_x_sr_task_rec.WO_Create_flag) = 'Y' and upper(p_x_sr_task_rec.WO_Release_flag) = 'Y') THEN
1717 OPEN validate_quantity(p_x_sr_task_rec.Instance_id , p_x_sr_task_rec.originating_wo_id);
1718 FETCH validate_quantity INTO l_quantity;
1719 IF (validate_quantity%NOTFOUND) THEN
1720 Fnd_Message.SET_NAME('AHL','AHL_INST_NOT_ISSUED');
1721 Fnd_Message.SET_TOKEN('INS_NUM',l_instance_num);
1722 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1723 Fnd_Msg_Pub.ADD;
1724 x_return_status := FND_API.G_RET_STS_ERROR;
1725 ELSIF (l_quantity < p_x_sr_task_rec.instance_quantity) THEN
1726 Fnd_Message.SET_NAME('AHL','AHL_INST_NOT_AVAIL');
1727 Fnd_Message.SET_TOKEN('INS_NAME',l_instance_num);
1728 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1729 Fnd_Message.SET_TOKEN('QUANT_USER',TO_CHAR(p_x_sr_task_rec.instance_quantity));
1730 Fnd_Message.SET_TOKEN('QUANT_AVAIL',TO_CHAR(l_quantity));
1731 Fnd_Msg_Pub.ADD;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 END IF;
1734 ELSE
1735 Fnd_Message.SET_NAME('AHL','AHL_NR_WO_NOT_RELEASED');
1736 Fnd_Msg_Pub.ADD;
1737 x_return_status := FND_API.G_RET_STS_ERROR;
1738 END IF;
1739 END IF;
1740 --amsriniv. Issue 105. End ER 6014567
1741
1742 ELSIF(upper(p_x_sr_task_rec.operation_type) = 'UPDATE') THEN
1743
1744
1745 -- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
1746 -- Bug # 6447467 -- start
1747 -- Check if resolution_meaning is not null. If resolution_meaning
1748 -- is null then return error message.
1749
1750 IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
1751
1752 IF ( p_x_sr_task_rec.resolution_meaning IS NULL OR
1753 p_x_sr_task_rec.resolution_meaning = FND_API.G_MISS_CHAR) THEN
1754
1755 Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_REQ');
1756 Fnd_Msg_Pub.ADD;
1757 RAISE FND_API.G_EXC_ERROR;
1758
1759 END IF;
1760 END IF;
1761 -- Bug # 6447467 -- end
1762
1763 -- Get message tokens
1764 --
1765 get_msg_token ( p_wo_id => p_x_sr_task_rec.originating_wo_id,
1766 p_instance_id => p_x_sr_task_rec.instance_id,
1767 x_wo_name => l_wo_name,
1768 x_instance_number => l_instance_num);
1769
1770 -- rroy
1771 -- ACL Changes
1772 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_sr_task_rec.originating_wo_id,
1773 p_ue_id => NULL,
1774 p_visit_id => NULL,
1775 p_item_instance_id => NULL);
1776 IF l_return_status = FND_API.G_TRUE THEN
1777 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_UPD_SR_UNTLCKD');
1778 FND_MESSAGE.Set_Token('WO_NAME', l_wo_name);
1779 FND_MSG_PUB.ADD;
1780 RAISE FND_API.G_EXC_ERROR;
1781 END IF;
1782 -- rroy
1783 -- ACL Changes
1784
1785 IF p_module_type IS NULL OR p_module_type <> 'SR_OA' THEN
1786
1787 -- If contact id is null then return an
1788 -- error message.
1789 IF (p_x_sr_task_rec.contact_id is null or p_x_sr_task_rec.contact_id = FND_API.G_MISS_NUM) THEN
1790
1791 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONTACT_REQ');
1792 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1793 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1794 Fnd_Msg_Pub.ADD;
1795 x_return_status := FND_API.G_RET_STS_ERROR;
1796
1797 END IF;
1798
1799
1800 -- If Contact type is null then return an error
1801 -- message.
1802 IF (p_x_sr_task_rec.contact_id is not null and
1803 (p_x_sr_task_rec.contact_type is null or p_x_sr_task_rec.contact_type = FND_API.G_MISS_CHAR)) THEN
1804
1805 Fnd_Message.SET_NAME('AHL','AHL_PRD_CONT_TYPE_REQ');
1806 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1807 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1808 Fnd_Msg_Pub.ADD;
1809 x_return_status := FND_API.G_RET_STS_ERROR;
1810
1811 END IF;
1812
1813
1814 -- If status is null then return an
1815 -- error message.
1816 IF ((p_x_sr_task_rec.status_id is null or p_x_sr_task_rec.status_id = FND_API.G_MISS_NUM)and
1817 (p_x_sr_task_rec.status_name is null or p_x_sr_task_rec.status_name = FND_API.G_MISS_CHAR)) THEN
1818
1819 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_STATUS_REQ');
1820 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1821 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1822 Fnd_Msg_Pub.ADD;
1823 x_return_status := FND_API.G_RET_STS_ERROR;
1824
1825 END IF;
1826
1827 END IF;
1828
1829 -- If object version number is null then
1830 -- return an error message.
1831 IF (p_x_sr_task_rec.incident_object_version_number is null or
1832 p_x_sr_task_rec.incident_object_version_number = FND_API.G_MISS_NUM) THEN
1833
1834 Fnd_Message.SET_NAME('AHL','AHL_PRD_SR_OBJ_VER_ID_REQ');
1835 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1836 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1837 Fnd_Msg_Pub.ADD;
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839
1840 END IF;
1841
1842 -- If Incident number and incident id is null then
1843 -- return an error message.
1844 IF (p_x_sr_task_rec.incident_number is null or
1845 p_x_sr_task_rec.incident_number = FND_API.G_MISS_CHAR) and
1846 (p_x_sr_task_rec.incident_id is null or
1847 p_x_sr_task_rec.incident_id = FND_API.G_MISS_NUM)THEN
1848
1849 Fnd_Message.SET_NAME('AHL','AHL_PRD_INCIDENT_VALUE_REQ');
1850 Fnd_Message.SET_TOKEN('WO_NAME',l_wo_name);
1851 Fnd_Message.SET_TOKEN('INSTANCE_NUM',l_instance_num);
1852 Fnd_Msg_Pub.ADD;
1853 x_return_status := FND_API.G_RET_STS_ERROR;
1854 END IF;
1855
1856 END IF;
1857
1858 IF (p_x_sr_task_rec.source_program_code is null or
1859 p_x_sr_task_rec.source_program_code = FND_API.G_MISS_CHAR) then
1860 p_x_sr_task_rec.source_program_code := 'AHL_ROUTINE';
1861 END IF;
1862
1863 EXCEPTION
1864 WHEN FND_API.G_EXC_ERROR THEN
1865 x_return_status := FND_API.G_RET_STS_ERROR;
1866 END Default_and_validate_param;
1867
1868
1869 --------------------------------------------
1870 -- Create Service Request
1871 --------------------------------------------
1872
1873 ----------------------------------------------
1874 -- Create_sr procedure assigns the values to
1875 -- service request record and calls the
1876 -- Create service request public api.
1877 ----------------------------------------------
1878
1879 PROCEDURE Create_sr(
1880 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
1881 x_return_status OUT NOCOPY VARCHAR2
1882 )IS
1883
1884 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
1885 l_notes_table CS_ServiceRequest_PUB.notes_table;
1886 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
1887 l_contact_primary_flag CONSTANT VARCHAR2(1) := 'Y';
1888 l_auto_assign CONSTANT VARCHAR2(1) := 'N';
1889
1890 l_msg_count NUMBER;
1891 l_msg_data VARCHAR2(2000);
1892 l_inventory_item_id NUMBER;
1893 l_serial_number VARCHAR2(30);
1894 l_inv_master_org_id NUMBER;
1895 l_note VARCHAR2(2000);
1896 l_note_detail VARCHAR2(2000);
1897
1898 l_individual_owner NUMBER;
1899 l_group_owner NUMBER;
1900 l_individual_type VARCHAR2(30);
1901 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_SR';
1902
1903 -- Begin Changes by VSUNDARA for SR Integration
1904 CURSOR default_item_org_id(p_workorder_id IN NUMBER) IS
1905 SELECT A.inventory_item_id,
1906 A.item_organization_id
1907 FROM AHL_VISIT_TASKS_B A,
1908 AHL_WORKORDERS B
1909 WHERE A.visit_task_id = B.visit_task_id
1910 AND B.workorder_id = p_workorder_id;
1911
1912 CURSOR default_incident_type_id is
1913 SELECT INCIDENT_TYPE_ID,NAME
1914 FROM cs_incident_types_vl
1915 where INCIDENT_SUBTYPE = 'INC'
1916 AND CMRO_FLAG = 'Y'
1917 -- Check added by balaji for bug # 4146503.
1918 -- always has to pick up the SR type id from AHL default SR Type profile.
1919 AND incident_type_id=fnd_profile.value('AHL_PRD_SR_TYPE')
1920 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
1921 AND trunc(nvl(end_date_active,sysdate));
1922 -- END Changes
1923 BEGIN
1924
1925 -- Initialize the SR record.
1926 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
1927
1928 get_note_value(p_sr_task_rec => p_x_sr_task_rec,
1929 x_note => l_note,
1930 x_note_detail => l_note_detail);
1931
1932 -- Assign the SR rec values
1933 l_service_request_rec.request_date := p_x_sr_task_rec.request_date;
1934 l_service_request_rec.status_id := p_x_sr_task_rec.status_id;
1935 l_service_request_rec.status_name := p_x_sr_task_rec.status_name;
1936 l_service_request_rec.severity_id := p_x_sr_task_rec.severity_id;
1937 l_service_request_rec.severity_name := p_x_sr_task_rec.severity_name;
1938 l_service_request_rec.urgency_id := p_x_sr_task_rec.urgency_id;
1939 l_service_request_rec.urgency_name := p_x_sr_task_rec.urgency_name;
1940 l_service_request_rec.summary := p_x_sr_task_rec.summary;
1941 l_service_request_rec.caller_type := p_x_sr_task_rec.customer_type;
1942 l_service_request_rec.customer_id := p_x_sr_task_rec.customer_id;
1943 l_service_request_rec.problem_code := p_x_sr_task_rec.problem_code;
1944 l_service_request_rec.resolution_code := p_x_sr_task_rec.resolution_code;
1945 l_service_request_rec.creation_program_code := p_x_sr_task_rec.source_program_code;
1946
1947 -- bug# 5450359. Default incident date.
1948 l_service_request_rec.incident_occurred_date := l_service_request_rec.request_date;
1949
1950 l_service_request_rec.customer_product_id := p_x_sr_task_rec.instance_id;
1951 open default_item_org_id(p_x_sr_task_rec.Originating_wo_id);
1952 Fetch default_item_org_id INTO l_service_request_rec.inventory_item_id,l_service_request_rec.inventory_org_id;
1953 IF (default_item_org_id%NOTFOUND ) THEN
1954 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_ORG_ERROR');
1955 Fnd_Msg_Pub.ADD;
1956 x_return_status := FND_API.G_RET_STS_ERROR;
1957 END IF;
1958
1959 open default_incident_type_id;
1960 Fetch default_incident_type_id INTO l_service_request_rec.type_id,l_service_request_rec.type_name;
1961
1962 IF ( default_incident_type_id%NOTFOUND) THEN
1963 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_INCIDENT_ERROR');
1964 Fnd_Msg_Pub.ADD;
1965 x_return_status := FND_API.G_RET_STS_ERROR;
1966 END IF;
1967 CLOSE default_item_org_id;
1968 CLOSE default_incident_type_id;
1969
1970 --- End Changes by VSUNDARA for SR Integration
1971 -- Contacts
1972 l_contacts_table(1).party_id := p_x_sr_task_rec.contact_id;
1973 l_contacts_table(1).contact_type := p_x_sr_task_rec.contact_type;
1974 l_contacts_table(1).primary_flag := l_contact_primary_flag;
1975
1976 -- Notes
1977 /*
1978 l_notes_table(1).note := l_note;
1979 l_notes_table(1).note_detail := l_note_detail;
1980 l_notes_table(1).note_type := 'CS_PROBLEM';
1981 l_notes_table(1).note_context_type_01 := 'SR';
1982
1983 -- Call write to log procedure to log the input parameter
1984 -- values for debug
1985
1986 IF (G_DEBUG = 'Y') THEN
1987 AHL_DEBUG_PUB.debug('Inputs for CS_SERVICEREQUEST_PUB.Create_ServiceRequest:');
1988 write_sr_to_log(
1989 p_service_request_rec => l_service_request_rec,
1990 p_notes_table => l_notes_table,
1991 p_contacts_table => l_contacts_table
1992 );
1993 END IF;
1994 */
1995 -- Call to Service Request API
1996
1997 CS_SERVICEREQUEST_PUB.Create_ServiceRequest(
1998 p_api_version => 3.0,
1999 p_init_msg_list => FND_API.G_TRUE,
2000 p_commit => FND_API.G_FALSE,
2001 x_return_status => x_return_status,
2002 x_msg_count => l_msg_count,
2003 x_msg_data => l_msg_data,
2004 p_resp_appl_id => NULL,
2005 p_resp_id => NULL,
2006 p_user_id => fnd_global.user_id,
2007 p_login_id => fnd_global.conc_login_id,
2008 p_org_id => NULL,
2009 p_request_id => NULL,
2010 p_request_number => NULL,
2011 p_service_request_rec => l_service_request_rec,
2012 p_notes => l_notes_table,
2013 p_contacts => l_contacts_table,
2014 p_auto_assign => l_auto_assign,
2015 x_request_id => p_x_sr_task_rec.incident_id,
2016 x_request_number => p_x_sr_task_rec.incident_number,
2017 x_interaction_id => p_x_sr_task_rec.interaction_id,
2018 x_workflow_process_id => p_x_sr_task_rec.workflow_process_id,
2019 x_individual_owner => l_individual_owner,
2020 x_group_owner => l_individual_owner,
2021 x_individual_type => l_individual_type
2022 );
2023
2024 ---Changes by VSUNDARA FOR TRANSIT CHECK
2025
2026 -- Tamal [MEL/CDL PRD Integration] Begins here...
2027 -- After creating the SR on the instance, need to populate unit_config_id for the newly created UE
2028 update ahl_unit_effectivities_b
2029 set unit_config_header_id = AHL_UTIL_UC_PKG.get_uc_header_id(p_x_sr_task_rec.instance_id)
2030 where unit_effectivity_id in
2031 (
2032 select unit_effectivity_id
2033 from ahl_unit_effectivities_b
2034 where object_type = 'SR' and cs_incident_id = p_x_sr_task_rec.incident_id
2035 );
2036 -- Tamal [MEL/CDL PRD Integration] Ends here...
2037
2038 -- MR NR ER -- start
2039
2040 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2041 fnd_log.string(
2042 fnd_log.level_statement,
2043 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2044 'Before Updating Unit Effectivity with Originating WO detail..'
2045 );
2046 fnd_log.string(
2047 fnd_log.level_statement,
2048 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2049 'p_x_sr_task_rec.Originating_wo_id->'||p_x_sr_task_rec.Originating_wo_id||' , '
2050 ||'p_x_sr_task_rec.incident_id->'||p_x_sr_task_rec.incident_id
2051 );
2052 END IF;
2053
2054 -- update ump table with originating wo id in AHL_UNIT_EFFECTIVITIES_B.ORIGINATING_WO_ID
2055 IF
2056 (
2057 p_x_sr_task_rec.incident_id IS NOT NULL
2058 AND
2059 p_x_sr_task_rec.Originating_wo_id IS NOT NULL
2060 )
2061 THEN
2062
2063 BEGIN
2064
2065 UPDATE AHL_UNIT_EFFECTIVITIES_B
2066 SET ORIGINATING_WO_ID = p_x_sr_task_rec.Originating_wo_id
2067 WHERE CS_INCIDENT_ID = p_x_sr_task_rec.incident_id;
2068
2069 EXCEPTION
2070
2071 WHEN OTHERS THEN
2072 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_ORIGINWO_UPD_FAILED');
2073 Fnd_Msg_Pub.ADD;
2074 x_return_status := FND_API.G_RET_STS_ERROR;
2075
2076 END;
2077
2078 END IF;
2079
2080 -- MR NR ER -- end
2081
2082 END Create_sr;
2083
2084 -- MR NR ER -- start
2085 -----------------------------------------------------------------------------------
2086 -- Balaji added this piece of code for OGMA ER # 6459697(Adding MRs to Non-Routine)
2087 -- This local procedure processes all MRs associated to a SR. Essentially does following
2088 -- 1. Creates and associates UE hierarchy for the MRs added to SR.
2089 -- 2. Creates Task hierarchy required in VWP
2090 -- 3. Releases the new tasks added in VWP to production.
2091 -----------------------------------------------------------------------------------
2092 PROCEDURE Process_Mr(
2093 p_x_task_tbl IN OUT NOCOPY sr_task_tbl_type,
2094 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
2095 p_module_type IN VARCHAR2,
2096 x_return_status OUT NOCOPY VARCHAR2,
2097 x_msg_count OUT NOCOPY NUMBER,
2098 x_msg_data OUT NOCOPY VARCHAR2
2099 )
2100 IS
2101
2102 -- declare all cursors here
2103 --*************************
2104
2105 --1. cursor for getting visit task id corresponding originating workorder
2106 cursor c_visit_task_csr(c_Nonroutine_wo_id IN NUMBER) IS
2107 SELECT
2108 WO.visit_task_id
2109 FROM
2110 AHL_WORKORDERS WO
2111 WHERE
2112 WO.workorder_id = c_Nonroutine_wo_id;
2113
2114 -- Added by jaramana on Oct 15
2115 CURSOR c_NR_wo_details(p_unit_effectivity_id IN NUMBER)
2116 IS
2117 SELECT
2118 awo.workorder_id
2119 FROM
2120 ahl_workorders awo,
2121 ahl_visit_tasks_b vtsk
2122 WHERE
2123 awo.visit_task_id = vtsk.visit_task_id
2124 AND awo.master_workorder_flag = 'Y'
2125 AND vtsk.task_type_code = 'SUMMARY'
2126 AND vtsk.mr_id is NULL
2127 AND vtsk.unit_effectivity_id = p_unit_effectivity_id;
2128
2129 -- 3. cursor for retrieving unit effectivity id corresponding to the SR created.
2130 CURSOR c_get_ue_id(p_incident_id NUMBER)
2131 IS
2132 Select unit_effectivity_id
2133 from AHL_UNIT_EFFECTIVITIES_B
2134 where cs_incident_id = p_incident_id;
2135
2136 CURSOR c_get_sr_details(p_incident_id NUMBER)
2137 IS
2138 SELECT object_version_number
2139 FROM CS_INCIDENTS
2140 WHERE incident_id = p_incident_id;
2141
2142 --amsriniv ER 6014567 Begin
2143 --5. cursor for retrieving the non master workorder id which is passed as to_workorder_id when calling move_intance_location
2144 CURSOR get_nonmaster_wo_id(p_nr_wo_id IN NUMBER)
2145 IS
2146 SELECT workorder_id
2147 FROM ahl_workorders
2148 WHERE MASTER_WORKORDER_FLAG = 'N'
2149 AND wip_entity_id in
2150 (SELECT rel.child_object_id
2151 FROM wip_sched_relationships rel START
2152 WITH REL.parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_nr_wo_id)
2153 CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
2154 AND REL.parent_object_type_id = PRIOR REL.child_object_type_id
2155 AND REL.relationship_type = 1
2156 )
2157 ORDER BY workorder_id;
2158 --amsriniv ER 6014567 End
2159
2160 --declare all local variables here
2161 --**********************************
2162 l_tasks_tbl AHL_VWP_PROJ_PROD_PVT.Task_Tbl_Type;
2163 l_visit_task_id NUMBER;
2164 l_create_task_tbl AHL_VWP_RULES_PVT.Task_Tbl_Type;
2165 l_x_sr_mr_association_tbl AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type;
2166 l_move_item_ins_tbl AHL_PRD_PARTS_CHANGE_PVT.move_item_instance_tbl_type;--amsriniv ER 6014567
2167 l_ins_cntr NUMBER := 0;--amsriniv ER 6014567
2168 i NUMBER;
2169 l_count NUMBER;
2170 l_unit_effectivity_id NUMBER;
2171 l_tsk_count NUMBER := 1;
2172 l_nmo_wo_id NUMBER; --amsriniv ER 6014567
2173 l_api_name VARCHAR2(200) := 'PROCESS_MR';
2174 l_workorder_id NUMBER;
2175
2176 BEGIN
2177
2178 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2179 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.begin', 'Entering Procedure');
2180 END IF;
2181
2182
2183 FOR i in p_x_task_tbl.FIRST .. p_x_task_tbl.LAST
2184 LOOP
2185
2186 IF p_mr_assoc_tbl.COUNT > 0 AND (p_module_type IS NULL OR p_module_type <> 'SR_OA')
2187 THEN
2188
2189 copy_mr_details(
2190 p_mr_assoc_tbl,
2191 l_x_sr_mr_association_tbl,
2192 i
2193 );
2194
2195 OPEN c_get_sr_details(p_x_task_tbl(i).Incident_id);
2196 FETCH c_get_sr_details INTO p_x_task_tbl(i).Incident_object_version_number;
2197 CLOSE c_get_sr_details;
2198
2199 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2200 fnd_log.string(
2201 fnd_log.level_statement,
2202 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2203 'Before calling AHL_UMP_SR_PVT.Process_SR_MR_Associations...'
2204 );
2205 fnd_log.string(
2206 fnd_log.level_statement,
2207 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2208 'p_x_task_tbl(i).Incident_id ->'||p_x_task_tbl(i).Incident_id
2209 );
2210 fnd_log.string(
2211 fnd_log.level_statement,
2212 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2213 'p_x_task_tbl(i).Incident_object_version_number'||p_x_task_tbl(i).Incident_object_version_number
2214 );
2215 fnd_log.string(
2216 fnd_log.level_statement,
2217 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2218 'p_x_task_tbl(i).Incident_number'||p_x_task_tbl(i).Incident_number
2219 );
2220 END IF;
2221
2222 -- 1. Create Unit Effectivity hierarchy for the SR - MR hieararchy.
2223 AHL_UMP_SR_PVT.Process_SR_MR_Associations(
2224 p_api_version => 1.0,
2225 p_init_msg_list => FND_API.G_TRUE,-- verify the value to be passed here
2226 p_commit => FND_API.G_FALSE,
2227 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2228 x_return_status => x_return_status,
2229 x_msg_count => x_msg_count,
2230 x_msg_data => x_msg_data,
2231 p_user_id => fnd_global.user_id,
2232 p_login_id => fnd_global.login_id,
2233 p_request_id => p_x_task_tbl(i).Incident_id,
2234 p_object_version_number => p_x_task_tbl(i).Incident_object_version_number,
2235 p_request_number => p_x_task_tbl(i).Incident_number,
2236 p_x_sr_mr_association_tbl => l_x_sr_mr_association_tbl
2237 );
2238
2239 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2240 fnd_log.string(
2241 fnd_log.level_statement,
2242 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2243 'After calling AHL_UMP_SR_PVT.Process_SR_MR_Associations...Return status->'||x_return_status
2244 );
2245 END IF;
2246
2247 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2248 RAISE FND_API.G_EXC_ERROR;
2249 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
2250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2251 END IF;
2252
2253 END IF;
2254 -- 2. Call VWP API to create task Hiearchy.
2255
2256 --FP for ER 5716489 -- start
2257 -- Call the VWP API to create task hierarchy only when the create Workorder
2258 -- flag is selected . If it is not checked ie:N, then do not create the tasks and WOs
2259
2260 IF (nvl(UPPER(p_x_task_tbl(i).WO_Create_flag),'Y') = 'Y')
2261 THEN
2262
2263 -- retrieve unit effectivity id corresponding to the SR
2264 OPEN c_get_ue_id(p_x_task_tbl(i).incident_id);
2265 FETCH c_get_ue_id into l_unit_effectivity_id ;
2266 IF c_get_ue_id%NotFound
2267 THEN
2268 x_return_status := FND_API.G_RET_STS_ERROR;
2269 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_SR');
2270 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
2271 FND_MSG_PUB.ADD;
2272 RAISE FND_API.G_EXC_ERROR;
2273 END IF;
2274 CLOSE c_get_ue_id;
2275
2276 OPEN c_visit_task_csr(p_x_task_tbl(i).Originating_wo_id);
2277 FETCH c_visit_task_csr INTO l_visit_task_id;
2278 CLOSE c_visit_task_csr;
2279
2280 l_create_task_tbl(l_tsk_count).originating_task_id := l_visit_task_id;
2281
2282 l_create_task_tbl(l_tsk_count).visit_id := p_x_task_tbl(i).visit_id;
2283 l_create_task_tbl(l_tsk_count).service_request_id := p_x_task_tbl(i).incident_id;
2284 l_create_task_tbl(l_tsk_count).unit_effectivity_id := l_unit_effectivity_id;
2285 l_create_task_tbl(l_tsk_count).task_type_code := 'PLANNED';
2286 l_create_task_tbl(l_tsk_count).operation_flag := 'C';
2287 l_create_task_tbl(l_tsk_count).quantity := p_x_task_tbl(i).instance_quantity; --amsriniv. Issue 105 ER 6014567
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 calling AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS...'
2293 );
2294 fnd_log.string(
2295 fnd_log.level_statement,
2296 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2297 'p_x_task_tbl(i).visit_id->'||p_x_task_tbl(i).visit_id
2298 );
2299 fnd_log.string(
2300 fnd_log.level_statement,
2301 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2302 'p_x_task_tbl(i).incident_id->'||p_x_task_tbl(i).incident_id
2303 );
2304 fnd_log.string(
2305 fnd_log.level_statement,
2306 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2307 'l_unit_effectivity_id->'||l_unit_effectivity_id
2308 );
2309 END IF;
2310
2311 AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS(
2312 p_api_version => 1.0,
2313 p_init_msg_list => Fnd_Api.G_TRUE,
2314 p_module_type => 'SR',
2315 p_x_task_tbl => l_create_task_tbl,
2316 x_return_status => x_return_status,
2317 x_msg_count => x_msg_count,
2318 x_msg_data => x_msg_data
2319 );
2320
2321 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2322 fnd_log.string(
2323 fnd_log.level_statement,
2324 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2325 'After calling AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS...Return status->'||x_return_status
2326 );
2327 END IF;
2328
2329 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2330 RAISE FND_API.G_EXC_ERROR;
2331 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
2332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2333 END IF;
2334
2335 -- 3. Call VWP API to push tasks into production.
2336
2337 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2338 fnd_log.string(
2339 fnd_log.level_statement,
2340 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2341 'Before calling AHL_VWP_PROJ_PROD_PVT.Release_MR...'
2342 );
2343 END IF;
2344 --FP for ER 5716489 -- start
2345 IF ( nvl(UPPER(p_x_task_tbl(i).WO_Release_flag), 'Y') = 'Y' )
2346 THEN
2347
2348 AHL_VWP_PROJ_PROD_PVT.Release_MR(
2349 p_api_version => 1.0,
2350 p_init_msg_list => Fnd_Api.G_FALSE,
2351 p_commit => Fnd_Api.G_FALSE,
2352 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2353 p_module_type => 'SR',
2354 p_visit_id => p_x_task_tbl(i).visit_id,
2355 p_unit_effectivity_id => l_unit_effectivity_id,
2356 p_release_flag => 'Y',
2357 x_workorder_id => l_workorder_id,
2358 x_return_status => x_return_status,
2359 x_msg_count => x_msg_count,
2360 x_msg_data => x_msg_data
2361 );
2362
2363 ELSE
2364 AHL_VWP_PROJ_PROD_PVT.Release_MR(
2365 p_api_version => 1.0,
2366 p_init_msg_list => Fnd_Api.G_FALSE,
2367 p_commit => Fnd_Api.G_FALSE,
2368 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2369 p_module_type => 'SR',
2370 p_visit_id => p_x_task_tbl(i).visit_id,
2371 p_unit_effectivity_id => l_unit_effectivity_id,
2372 p_release_flag => 'N',
2373 x_workorder_id => l_workorder_id,
2374 x_return_status => x_return_status,
2375 x_msg_count => x_msg_count,
2376 x_msg_data => x_msg_data
2377 );
2378
2379 END IF;
2380 --FP for ER 5716489 -- end
2381
2382 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2383 fnd_log.string(
2384 fnd_log.level_statement,
2385 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2386 'After calling AHL_VWP_PROJ_PROD_PVT.Release_MR...Return status ->'||x_return_status
2387 );
2388 END IF;
2389
2390 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2391 RAISE FND_API.G_EXC_ERROR;
2392 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
2393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2394 END IF;
2395
2396 -- Added by jaramana on Oct 15
2397 IF ( upper(p_x_task_tbl(i).operation_type) = 'CREATE') THEN
2398 OPEN c_NR_wo_details(l_unit_effectivity_id);
2399 FETCH c_NR_wo_details INTO p_x_task_tbl(i).Nonroutine_wo_id;
2400 CLOSE c_NR_wo_details;
2401 END IF;
2402 --amsriniv ER 6014567 Begin
2403 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
2404 upper(p_x_task_tbl(i).operation_type) = 'CREATE' AND
2405 p_x_task_tbl(i).nonroutine_wo_id IS NOT NULL)
2406 THEN
2407 OPEN get_nonmaster_wo_id(p_x_task_tbl(i).nonroutine_wo_id);
2408 FETCH get_nonmaster_wo_id into l_nmo_wo_id ;
2409 IF get_nonmaster_wo_id%FOUND
2410 THEN
2411 l_move_item_ins_tbl(l_ins_cntr).instance_id := p_x_task_tbl(i).instance_id;
2412 l_move_item_ins_tbl(l_ins_cntr).quantity := p_x_task_tbl(i).instance_quantity;
2413 l_move_item_ins_tbl(l_ins_cntr).from_workorder_id := p_x_task_tbl(i).originating_wo_id;
2414 l_move_item_ins_tbl(l_ins_cntr).to_workorder_id := l_nmo_wo_id;
2415 l_ins_cntr := l_ins_cntr + 1;
2416 END IF;
2417 END IF;
2418 --amsriniv ER 6014567 End
2419 END IF;
2420 --FP for ER 5716489 -- end
2421
2422 END LOOP;
2423 --amsriniv ER 6014567 Begin
2424 IF (l_ins_cntr > 0)
2425 THEN
2426 AHL_PRD_PARTS_CHANGE_PVT.move_instance_location(
2427 p_api_version => 1.0,
2428 p_init_msg_list => Fnd_Api.G_FALSE,
2429 p_commit => Fnd_Api.G_FALSE,
2430 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2431 p_module_type => NULL,
2432 p_default => FND_API.G_TRUE,
2433 p_move_item_instance_tbl => l_move_item_ins_tbl,
2434 x_return_status => x_return_status,
2435 x_msg_count => x_msg_count,
2436 x_msg_data => x_msg_data
2437 );
2438 END IF;
2439 --amsriniv ER 6014567 End
2440 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.end', 'Exiting Procedure');
2442 END IF;
2443
2444 END Process_Mr;
2445 -- MR NR ER -- end
2446
2447 -- MR NR ER -- start
2448 PROCEDURE Copy_Mr_Details(
2449 p_mr_assoc_tbl IN OUT NOCOPY MR_Association_tbl_type,
2450 p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type,
2451 p_sr_table_index IN NUMBER
2452 )
2453 IS
2454 -- declare all local variables here.
2455 l_count NUMBER;
2456 l_api_name VARCHAR2(200) := 'COPY_MR_DETAILS';
2457
2458 BEGIN
2459 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.begin', 'Entering Procedure');
2461 END IF;
2462
2463 l_count := 0;
2464
2465 FOR j IN p_mr_assoc_tbl.FIRST .. p_mr_assoc_tbl.LAST
2466 LOOP
2467 IF p_sr_table_index = p_mr_assoc_tbl(j).sr_tbl_index THEN
2468
2469 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2470 fnd_log.string(
2471 fnd_log.level_statement,
2472 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||':',
2473 'p_sr_table_index->'||p_sr_table_index
2474 );
2475 END IF;
2476
2477 l_count := l_count + 1;
2478 p_x_sr_mr_association_tbl(l_count).mr_header_id := p_mr_assoc_tbl(j).mr_header_id;
2479 p_x_sr_mr_association_tbl(l_count).mr_title := p_mr_assoc_tbl(j).mr_title;
2480 p_x_sr_mr_association_tbl(l_count).mr_version := p_mr_assoc_tbl(j).mr_version;
2481 p_x_sr_mr_association_tbl(l_count).relationship_code := 'PARENT';
2482 p_x_sr_mr_association_tbl(l_count).csi_instance_id := p_mr_assoc_tbl(j).csi_instance_id;
2483 p_x_sr_mr_association_tbl(l_count).csi_instance_number := p_mr_assoc_tbl(j).csi_instance_number;
2484 p_x_sr_mr_association_tbl(l_count).operation_flag := 'C';
2485 END IF;
2486 END LOOP;
2487
2488 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2489 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || l_api_name || '.end', 'Exiting Procedure');
2490 END IF;
2491
2492 END Copy_Mr_Details;
2493 -- MR NR ER -- end
2494
2495 --------------------------------------------
2496 -- Create VWP Task
2497 --------------------------------------------
2498 PROCEDURE Create_task(
2499 p_x_task_tbl IN OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_tbl_type,
2500 x_return_status OUT NOCOPY VARCHAR2
2501 ) IS
2502
2503 l_create_job_task_tbl AHL_VWP_PROJ_PROD_PVT.Task_tbl_type;
2504 l_msg_count NUMBER;
2505 l_msg_data VARCHAR2(2000);
2506 l_org_task_id NUMBER;
2507 l_request_type VARCHAR2(60);
2508 l_visit_task_name VARCHAR2(80);
2509 l_task_type_code VARCHAR2(30) := 'UNASSOCIATED';
2510 l_operation_flag VARCHAR2(3) := 'C';
2511 l_unit_effectivity_id NUMBER;
2512 CURSOR GetRequestType(c_req_type_id NUMBER)
2513 Is
2514 Select name
2515 FROM cs_incident_types_vl
2516 WHERE incident_type_id = c_req_type_id;
2517 CURSOR GetOrgTaskDet(c_org_wo_id NUMBER)
2518 Is
2519 Select visit_task_id
2520 from ahl_workorders
2521 where workorder_id = c_org_wo_id;
2522
2523 CURSOR getUnitEffectivity(p_incident_id NUMBER)
2524 IS
2525 Select unit_effectivity_id
2526 from AHL_UNIT_EFFECTIVITIES_B
2527 where cs_incident_id = p_incident_id;
2528
2529 -- FND Logging Constants
2530 l_debug_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2531 l_debug_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
2532 l_debug_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
2533 l_debug_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
2534
2535 BEGIN
2536
2537 IF (l_debug_PROC >= l_debug_LEVEL) THEN
2538 fnd_log.string
2539 (l_debug_PROC,
2540 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task.begin',
2541 'At the start of PLSQL procedure');
2542 END IF;
2543
2544 FOR i IN p_x_task_tbl.FIRST..p_x_task_tbl.LAST LOOP
2545 IF ( upper(p_x_task_tbl(i).operation_type) = 'CREATE') THEN
2546 -- Initialize the Record type
2547 --
2548 l_request_type := null;
2549 l_org_task_id := null;
2550
2551 -- Derive the request type
2552 IF (p_x_task_tbl(i).type_name is null or
2553 p_x_task_tbl(i).type_name = FND_API.G_MISS_CHAR)
2554 THEN
2555 Open GetRequestType(p_x_task_tbl(i).type_id);
2556 Fetch GetRequestType into l_request_type;
2557 Close GetRequestType;
2558 ELSE
2559 l_request_type := p_x_task_tbl(i).type_name;
2560 END IF;
2561
2562 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2563 fnd_log.string
2564 (l_debug_STMT,
2565 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2566 'After deriving request type:' || l_request_type);
2567 END IF;
2568
2569 -- Derive the originating visit id
2570
2571 Open GetOrgTaskDet(p_x_task_tbl(i).originating_wo_id);
2572 Fetch GetOrgTaskDet into l_org_task_id;
2573
2574 If GetOrgTaskDet%Found and GetOrgTaskDet%rowcount >1
2575 Then
2576 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_TASK_ID_NOT_UNIQUE');
2577 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
2578 Fnd_Msg_Pub.ADD;
2579 RAISE FND_API.G_EXC_ERROR;
2580 ElsIf GetOrgTaskDet%NotFound
2581 Then
2582 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_WO_ID');
2583 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
2584 Fnd_Msg_Pub.ADD;
2585 RAISE FND_API.G_EXC_ERROR;
2586 End if;
2587 Close GetOrgTaskDet;
2588
2589 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2590 fnd_log.string
2591 (l_debug_STMT,
2592 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2593 'After deriving originating visit task id:' || l_org_task_id);
2594
2595 END IF;
2596
2597 -- If visit task name is null then default the values
2598 IF( p_x_task_tbl(i).visit_task_name is null or
2599 p_x_task_tbl(i).visit_task_name = FND_API.G_MISS_CHAR) THEN
2600 l_visit_task_name := substr(l_request_type,1,(78-length(p_x_task_tbl(i).incident_number)))||'-'
2601 ||p_x_task_tbl(i).incident_number;
2602
2603 p_x_task_tbl(i).visit_task_name := l_visit_task_name;
2604
2605 ELSE
2606 l_visit_task_name := p_x_task_tbl(i).visit_task_name;
2607 END IF;
2608
2609 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2610 fnd_log.string
2611 (l_debug_STMT,
2612 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2613 'After defaulting task name');
2614 END IF;
2615
2616 --- Begin Changes by VSUNDARA for SR INTEGRATION
2617 Open getUnitEffectivity(p_x_task_tbl(i).incident_id);
2618 Fetch getUnitEffectivity into l_unit_effectivity_id ;
2619 IF getUnitEffectivity%NotFound
2620 Then
2621 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_SR'); -- New Message needed to be added
2622 FND_MESSAGE.SET_TOKEN('WO_ID',p_x_task_tbl(i).originating_wo_id);
2623 Fnd_Msg_Pub.ADD;
2624 RAISE FND_API.G_EXC_ERROR;
2625 End if;
2626 Close getUnitEffectivity;
2627 l_create_job_task_tbl(i).unit_effectivity_id := l_unit_effectivity_id;
2628 --- END Changes by VSUNDARA for SR INTEGRATION
2629
2630 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2631 fnd_log.string
2632 (l_debug_STMT,
2633 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2634 'After deriving UE ID:' || l_unit_effectivity_id);
2635 END IF;
2636
2637 -- Assign the Create Job Tasks values
2638 l_create_job_task_tbl(i).visit_id := p_x_task_tbl(i).visit_id;
2639 l_create_job_task_tbl(i).visit_task_name := l_visit_task_name;
2640 l_create_job_task_tbl(i).duration := p_x_task_tbl(i).duration;
2641 l_create_job_task_tbl(i).instance_id := p_x_task_tbl(i).instance_id;
2642 l_create_job_task_tbl(i).service_request_id := p_x_task_tbl(i).incident_id;
2643 l_create_job_task_tbl(i).originating_task_id := l_org_task_id;
2644 l_create_job_task_tbl(i).task_type_code := l_task_type_code;
2645 l_create_job_task_tbl(i).operation_flag := l_operation_flag;
2646
2647
2648 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2649 fnd_log.string
2650 (l_debug_STMT,
2651 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2652 'End loop for visit task name: ' || l_visit_task_name);
2653 END IF;
2654
2655 END IF;
2656 END LOOP;
2657
2658 IF l_create_job_task_tbl.count > 0 THEN
2659
2660 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2661 fnd_log.string
2662 (l_debug_STMT,
2663 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2664 'Before calling AHL_VWP_PROJ_PROD_PVT.Create_job_tasks');
2665 END IF;
2666
2667 AHL_VWP_PROJ_PROD_PVT.Create_job_tasks(
2668 p_api_version => 1.0,
2669 p_init_msg_list => FND_API.G_TRUE,
2670 p_commit => FND_API.G_FALSE,
2671 p_validation_level => Fnd_API.G_VALID_LEVEL_FULL,
2672 p_module_type => NULL,
2673 p_x_task_tbl => l_create_job_task_tbl,
2674 x_return_status => x_return_status,
2675 x_msg_count => l_msg_count,
2676 x_msg_data => l_msg_data
2677 );
2678
2679 -- AHL_VWP_PROJ_PROD_PVT.Create_job_tasks returns x_return_status as success
2680 -- even though visit validation fails. The validation errors are put in the
2681 -- error stack. In this case, the WO creation api will not be called and
2682 -- wo_id returned is null. Task is created.
2683
2684 l_msg_count := FND_MSG_PUB.count_msg;
2685
2686 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS OR l_msg_count > 0) THEN
2687
2688 IF (l_debug_UEXP >= l_debug_LEVEL) THEN
2689 fnd_log.string
2690 (l_debug_UEXP,
2691 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2692 'Error ' || x_return_status ||' returned from AHL_VWP_PROJ_PROD_PVT.Create_job_tasks');
2693 END IF;
2694
2695 RAISE FND_API.G_EXC_ERROR;
2696 END IF;
2697
2698 END IF;
2699
2700 /*
2701 IF (x_return_status = FND_API.G_RET_STS_ERROR ) THEN
2702 x_return_status := FND_API.G_RET_STS_ERROR;
2703 END IF;
2704 */
2705
2706 FOR i IN p_x_task_tbl.FIRST..p_x_task_tbl.LAST LOOP
2707 IF ( upper(p_x_task_tbl(i).operation_type) = 'CREATE') THEN
2708
2709 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2710 fnd_log.string
2711 (l_debug_STMT,
2712 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2713 'Now processing for WO: ' || l_create_job_task_tbl(i).workorder_id);
2714 END IF;
2715
2716 p_x_task_tbl(i).visit_task_id := l_create_job_task_tbl(i).visit_task_id;
2717 p_x_task_tbl(i).visit_task_number := l_create_job_task_tbl(i).visit_task_number;
2718 p_x_task_tbl(i).Nonroutine_wo_id := l_create_job_task_tbl(i).workorder_id;
2719
2720 -- R12: modified for bug# 5261150.
2721 IF (nvl(p_x_task_tbl(i).WO_Release_flag,'Y') = 'Y' AND
2722 l_create_job_task_tbl(i).workorder_id IS NOT NULL) THEN
2723
2724 -- Fix for bug# 5261150.
2725 -- release workorder if user chooses to release wo.
2726 -- Default is to release wo.
2727 IF (l_debug_STMT >= l_debug_LEVEL) THEN
2728 fnd_log.string
2729 (l_debug_STMT,
2730 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2731 'Before calling AHL_PRD_WORKORDER_PVT.Release_visit_jobs for WO: ' || l_create_job_task_tbl(i).workorder_id);
2732 END IF;
2733
2734 AHL_PRD_WORKORDER_PVT.Release_visit_jobs
2735 (
2736 p_api_version => 1.0,
2737 p_init_msg_list => FND_API.G_TRUE,
2738 p_commit => FND_API.G_FALSE,
2739 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2740 p_default => FND_API.G_FALSE,
2741 p_module_type => NULL,
2742 x_return_status => x_return_status,
2743 x_msg_count => l_msg_count,
2744 x_msg_data => l_msg_data,
2745 p_visit_id => NULL,
2746 p_unit_effectivity_id => NULL,
2747 p_workorder_id => l_create_job_task_tbl(i).workorder_id
2748 );
2749
2750 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2751 IF (l_debug_UEXP >= l_debug_LEVEL) THEN
2752 fnd_log.string
2753 (l_debug_UEXP,
2754 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task',
2755 'Error ' || x_return_status ||' returned from AHL_PRD_WORKORDER_PVT.Release_visit_jobs');
2756 END IF;
2757 RAISE FND_API.G_EXC_ERROR;
2758 END IF;
2759
2760 END IF; -- p_x_task_tbl(i).WO_Release_flag = 'Y'
2761
2762 END IF;
2763 END LOOP;
2764
2765 IF (l_debug_PROC >= l_debug_LEVEL) THEN
2766 fnd_log.string
2767 (l_debug_PROC,
2768 'ahl.plsql.AHL_PRD_NONROUTINE_PVT.Create_task.End',
2769 'At the end of PLSQL procedure');
2770 END IF;
2771
2772 EXCEPTION
2773 WHEN FND_API.G_EXC_ERROR THEN
2774 x_return_status := FND_API.G_RET_STS_ERROR;
2775 END Create_task;
2776
2777
2778 --------------------------------------------
2779 -- Update Service Request
2780 --------------------------------------------
2781
2782 ----------------------------------------------
2783 -- Update_sr procedure assigns the values to
2784 -- the service request record and calls the
2785 -- update_servicerquest public api.
2786 ----------------------------------------------
2787 PROCEDURE Update_sr(
2788 p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
2789 x_return_status OUT NOCOPY VARCHAR2
2790 ) IS
2791
2792 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
2793 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
2794 l_notes_table CS_ServiceRequest_PUB.notes_table;
2795 l_contact_primary_flag CONSTANT VARCHAR2(1) := 'Y';
2796
2797 l_msg_count NUMBER;
2798 l_msg_data VARCHAR2(2000);
2799
2800 BEGIN
2801
2802 -- Initialize the SR record.
2803 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
2804
2805
2806 -- Assign the SR rec values
2807 l_service_request_rec.status_id := p_x_sr_task_rec.status_id;
2808 l_service_request_rec.status_name := p_x_sr_task_rec.status_name;
2809
2810 l_service_request_rec.urgency_id := p_x_sr_task_rec.urgency_id;
2811 l_service_request_rec.urgency_name := p_x_sr_task_rec.urgency_name;
2812 l_service_request_rec.problem_code := p_x_sr_task_rec.problem_code;
2813 l_service_request_rec.resolution_code := p_x_sr_task_rec.resolution_code;
2814 l_service_request_rec.last_update_program_code := p_x_sr_task_rec.source_program_code;
2815
2816 /* R12(xbuild#1): Commenting out passing contacts table as CS API raises an error:
2817 API programming error ( CS_SRCONTACT_PKG.check_duplicates): This contact is
2818 a duplicate of a contact already associated with the service request. Each
2819 contact you associate must have a unique combination of party name and
2820 contact point.
2821
2822 -- Contacts
2823 l_contacts_table(1).party_id := p_x_sr_task_rec.contact_id;
2824 l_contacts_table(1).contact_type := p_x_sr_task_rec.contact_type;
2825 l_contacts_table(1).primary_flag := l_contact_primary_flag;
2826 */
2827
2828 -- Call to Service Request API
2829 CS_SERVICEREQUEST_PUB.Update_ServiceRequest(
2830 p_api_version => 3.0,
2831 p_init_msg_list => FND_API.G_TRUE,
2832 p_commit => FND_API.G_FALSE,
2833 x_return_status => x_return_status,
2834 x_msg_count => l_msg_count,
2835 x_msg_data => l_msg_data,
2836 p_request_id => p_x_sr_task_rec.incident_id,
2837 --p_request_number => p_x_sr_task_rec.incident_number,
2838 p_audit_comments => Null,
2839 p_object_version_number => p_x_sr_task_rec.incident_object_version_number,
2840 p_resp_appl_id => NULL,
2841 p_resp_id => NULL,
2842 p_last_updated_by => NULL,
2843 p_last_update_login => NULL,
2844 p_last_update_date => NULL,
2845 p_service_request_rec => l_service_request_rec,
2846 p_notes => l_notes_table,
2847 p_contacts => l_contacts_table,
2848 p_called_by_workflow => NULL,
2849 p_workflow_process_id => NULL,
2850 x_workflow_process_id => p_x_sr_task_rec.workflow_process_id,
2851 x_interaction_id => p_x_sr_task_rec.interaction_id
2852 );
2853
2854 END Update_sr;
2855
2856 -----------------------------
2857 -- Get Message Token
2858 -----------------------------
2859 Procedure get_msg_token(p_wo_id in number,
2860 p_instance_id in number,
2861 x_wo_name out NOCOPY varchar2,
2862 x_instance_number out NOCOPY varchar2)
2863 IS
2864 CURSOR GetWoName
2865 Is
2866 Select workorder_name
2867 from ahl_workorders
2868 where workorder_id = p_wo_id;
2869 Cursor GetInstanceNumber
2870 Is
2871 Select instance_number
2872 from csi_item_instances
2873 where instance_id = p_instance_id;
2874 BEGIN
2875 Open GetWoName;
2876 Fetch GetWoName into x_wo_name;
2877 Close GetWoName;
2878
2879 -- No exceptions were handled in previous code.
2880 -- Changed the big lengthy Begin------End; code for each sql to Cursor.
2881 -- Too many (Invalid) exception handling got reduced.
2882
2883 Open GetInstanceNumber;
2884 Fetch GetInstanceNumber into x_instance_number;
2885 Close GetInstanceNumber;
2886
2887 END get_msg_token;
2888
2889
2890 -----------------------------------------
2891 -- Get Note Information from the Message
2892 -----------------------------------------
2893 Procedure get_note_value(p_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
2894 x_note OUT NOCOPY VARCHAR2,
2895 x_note_detail OUT NOCOPY VARCHAR2)
2896 IS
2897 l_part_number VARCHAR2(80);
2898 l_serial_number VARCHAR2(30);
2899 l_wo_name VARCHAR2(80);
2900 l_instance_number VARCHAR2(30);
2901
2902 CURSOR GetWoName
2903 Is
2904 Select workorder_name
2905 from ahl_workorders
2906 where workorder_id = p_sr_task_rec.originating_wo_id;
2907
2908 CURSOR GetInstanceDet
2909 Is
2910 Select ci.instance_number,
2911 ci.serial_number,
2912 msi.concatenated_segments
2913 from csi_item_instances ci,
2914 mtl_system_items_kfv msi
2915 where ci.instance_id = p_sr_task_rec.instance_id
2916 and ci.inventory_item_id = msi.inventory_item_id
2917 and ci.inv_master_organizatiOn_id = msi.organization_id;
2918
2919 BEGIN
2920 Open GetWoName;
2921 Fetch GetWoName into l_wo_name;
2922 Close GetWoName;
2923
2924 Open GetInstanceDet;
2925 Fetch GetInstanceDet into l_instance_number,l_serial_number,l_part_number;
2926 Close GetInstanceDet;
2927
2928 -- No exceptions were handled in previous code.
2929 -- Changed the big lengthy Begin------End; code for each sql to Cursor.
2930 -- Too many (Invalid) exception handling got reduced.
2931
2932 fnd_message.set_name('AHL','AHL_PRD_SR_NOTE');
2933 fnd_message.set_token('PART_NUMBER',l_part_number);
2934 fnd_message.set_token('SERIAL_NUMBER',l_serial_number);
2935 x_note := fnd_message.get;
2936
2937 fnd_message.set_name('AHL','AHL_PRD_SR_NOTE_DETAIL');
2938 fnd_message.set_token('WO_NAME',l_wo_name);
2939 fnd_message.set_token('INSTANCE_NUMBER',l_instance_number);
2940 x_note_detail := fnd_message.get;
2941
2942 END get_note_value;
2943
2944 -----------------------------------
2945 -- Write to Log
2946 -- This procedure writes the input
2947 -- values to a log file
2948 -----------------------------------
2949 Procedure write_to_log(p_sr_tasK_tbl IN ahl_prd_nonroutine_pvt.sr_task_tbl_type)
2950 IS
2951 BEGIN
2952 FOR i IN p_sr_task_tbl.FIRST..p_sr_task_tbl.LAST LOOP
2953 AHL_DEBUG_PUB.debug('INPUT - Type Id('||i||'):'||p_sr_task_tbl(i).type_id);
2954 AHL_DEBUG_PUB.debug('INPUT - Type Name('||i||'):'||p_sr_task_tbl(i).type_name);
2955 AHL_DEBUG_PUB.debug('INPUT - Status Id('||i||'):'||p_sr_task_tbl(i).status_id);
2956 AHL_DEBUG_PUB.debug('INPUT - Status Name('||i||'):'||p_sr_task_tbl(i).status_name);
2957 AHL_DEBUG_PUB.debug('INPUT - Severity Id('||i||'):'||p_sr_task_tbl(i).severity_id);
2958 AHL_DEBUG_PUB.debug('INPUT - Severity Name('||i||'):'||p_sr_task_tbl(i).severity_name);
2959 AHL_DEBUG_PUB.debug('INPUT - Urgency id('||i||'):'||p_sr_task_tbl(i).Urgency_id);
2960 AHL_DEBUG_PUB.debug('INPUT - Urgency name('||i||'):'||p_sr_task_tbl(i).Urgency_name);
2961 AHL_DEBUG_PUB.debug('INPUT - Customer type('||i||'):'||p_sr_task_tbl(i).Customer_type);
2962 AHL_DEBUG_PUB.debug('INPUT - Customer id('||i||'):'||p_sr_task_tbl(i).Customer_id);
2963 AHL_DEBUG_PUB.debug('INPUT - Customer name('||i||'):'||p_sr_task_tbl(i).Customer_name);
2964 AHL_DEBUG_PUB.debug('INPUT - Contact type('||i||'):'||p_sr_task_tbl(i).Contact_type);
2965 AHL_DEBUG_PUB.debug('INPUT - Contact Id('||i||'):'||p_sr_task_tbl(i).Contact_id);
2966 AHL_DEBUG_PUB.debug('INPUT - Contact name('||i||'):'||p_sr_task_tbl(i).Contact_name);
2967 AHL_DEBUG_PUB.debug('INPUT - Instance Id('||i||'):'||p_sr_task_tbl(i).Instance_id);
2968 AHL_DEBUG_PUB.debug('INPUT - Instance number('||i||'):'||p_sr_task_tbl(i).Instance_number);
2969 AHL_DEBUG_PUB.debug('INPUT - Visit Id('||i||'):'||p_sr_task_tbl(i).visit_id);
2970 AHL_DEBUG_PUB.debug('INPUT - Visit number('||i||'):'||p_sr_task_tbl(i).visit_number);
2971 AHL_DEBUG_PUB.debug('INPUT - Originating wo id('||i||'):'||p_sr_task_tbl(i).originating_wo_id);
2972 AHL_DEBUG_PUB.debug('INPUT - Incident obj ver num('||i||'):'||p_sr_task_tbl(i).incident_object_version_number);
2973 AHL_DEBUG_PUB.debug('INPUT - Operation type('||i||'):'||p_sr_task_tbl(i).operation_type);
2974 END LOOP;
2975
2976 END write_to_log;
2977
2978 -----------------------------------
2979 -- Write SR Rec to Log
2980 -- This procedure writes the input
2981 -- values of the SR API to a log file
2982 -----------------------------------
2983 Procedure write_sr_to_log
2984 (
2985 p_service_request_rec IN CS_SERVICEREQUEST_PUB.service_request_rec_type,
2986 p_notes_table IN CS_SERVICEREQUEST_PUB.notes_table,
2987 p_contacts_table IN CS_SERVICEREQUEST_PUB.contacts_table
2988 )
2989 IS
2990 BEGIN
2991 AHL_DEBUG_PUB.debug('SR Rec:');
2992 AHL_DEBUG_PUB.debug('request_date:'||p_service_request_rec.request_date);
2993 AHL_DEBUG_PUB.debug('type_id:'||p_service_request_rec.type_id);
2994 AHL_DEBUG_PUB.debug('type_name:'||p_service_request_rec.type_name);
2995 AHL_DEBUG_PUB.debug('status_id:'||p_service_request_rec.status_id);
2996 AHL_DEBUG_PUB.debug('status_name:'||p_service_request_rec.status_name);
2997 AHL_DEBUG_PUB.debug('severity_id:'||p_service_request_rec.severity_id);
2998 AHL_DEBUG_PUB.debug('severity_name:'||p_service_request_rec.severity_name);
2999 AHL_DEBUG_PUB.debug('urgency_id:'||p_service_request_rec.urgency_id);
3000 AHL_DEBUG_PUB.debug('summary:'||p_service_request_rec.summary);
3001 AHL_DEBUG_PUB.debug('caller_type:'||p_service_request_rec.caller_type);
3002 AHL_DEBUG_PUB.debug('customer_id:'||p_service_request_rec.customer_id);
3003 AHL_DEBUG_PUB.debug('problem_code:'||p_service_request_rec.problem_code);
3004 AHL_DEBUG_PUB.debug('resolution_code:'||p_service_request_rec.resolution_code);
3005 AHL_DEBUG_PUB.debug('creation_program_code:'||p_service_request_rec.creation_program_code);
3006 AHL_DEBUG_PUB.debug('urgency_name:'||p_service_request_rec.urgency_name);
3007
3008 -- Contacts
3009 AHL_DEBUG_PUB.debug('Contacts:');
3010 AHL_DEBUG_PUB.debug('party_id:'||p_contacts_table(1).party_id);
3011 AHL_DEBUG_PUB.debug('contact_type:'||p_contacts_table(1).contact_type);
3012 AHL_DEBUG_PUB.debug('primary_flag:'||p_contacts_table(1).primary_flag);
3013
3014 -- Notes
3015 AHL_DEBUG_PUB.debug('Notes:');
3016 AHL_DEBUG_PUB.debug('note:'||p_notes_table(1).note);
3017 AHL_DEBUG_PUB.debug('note_detail:'||p_notes_table(1).note_detail);
3018 AHL_DEBUG_PUB.debug('note_type:'||p_notes_table(1).note_type);
3019 AHL_DEBUG_PUB.debug('note_context_type_01:'||p_notes_table(1).note_context_type_01);
3020 END write_sr_to_log;
3021
3022
3023 END AHL_PRD_NONROUTINE_PVT;
[Home] [Help]