[Home] [Help]
PACKAGE BODY: APPS.AHL_UMP_NONROUTINES_PVT
Source
1 PACKAGE BODY AHL_UMP_NONROUTINES_PVT AS
2 /* $Header: AHLVNRTB.pls 120.51.12010000.3 2008/12/28 00:05:37 sracha ship $ */
3
4 ------------------------------------
5 -- Common constants and variables --
6 ------------------------------------
7 l_dummy_varchar VARCHAR2(1);
8 l_dummy_number NUMBER;
9 G_SR_OPEN_STATUS_ID CONSTANT NUMBER := 1;
10
11 -- Yes/no flags
12 G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
13 G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
14
15 -- FND Logging Constants
16 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
17 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
18 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
19 G_DEBUG_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
20
21 ----------------------------------
22 -- Non-spec Function Signatures --
23 ----------------------------------
24 FUNCTION Is_MEL_CDL_Approved
25 (
26 p_unit_effectivity_id NUMBER
27 )
28 RETURN BOOLEAN;
29
30 -----------------------------------
31 -- Non-spec Procedure Signatures --
32 -----------------------------------
33 PROCEDURE Validate_SR_Details
34 (
35 p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type,
36 p_dml_operation IN VARCHAR2
37 );
38
39 PROCEDURE Validate_UE_Details
40 (
41 p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type,
42 p_unit_effectivity_id IN NUMBER,
43 p_dml_operation IN VARCHAR2
44 );
45
46 PROCEDURE Get_Ata_Sequence
47 (
48 p_unit_effectivity_id IN NUMBER,
49 p_ata_code IN VARCHAR2,
50 x_ata_sequence_id OUT NOCOPY NUMBER
51 );
52
53 /* Moved to specification
54 PROCEDURE Process_MO_procedures
55 (
56 p_unit_effectivity_id IN NUMBER,
57 p_unit_deferral_id IN NUMBER,
58 p_unit_deferral_ovn IN NUMBER,
59 p_ata_sequence_id IN NUMBER,
60 p_cs_incident_id IN NUMBER,
61 p_csi_item_instance_id IN NUMBER);
62 */
63
64 ------------------------------
65 -- Spec Procedure Create_SR --
66 ------------------------------
67 PROCEDURE Create_SR
68 (
69 -- Standard IN params
70 p_api_version IN NUMBER,
71 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
72 p_commit IN VARCHAR2 := FND_API.G_FALSE,
73 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
74 p_default IN VARCHAR2 := FND_API.G_FALSE,
75 p_module_type IN VARCHAR2 := NULL,
76 -- Standard OUT params
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 -- Procedure IN, OUT, IN/OUT params
81 p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type
82 )
83 IS
84 -- Declare local variables
85 l_api_name CONSTANT VARCHAR2(30) := 'Create_SR';
86 l_api_version CONSTANT NUMBER := 1.0;
87 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
88
89 l_return_status VARCHAR2(1);
90 l_msg_count NUMBER;
91 l_msg_data VARCHAR2(2000);
92
93 -- Define cursors
94 l_unit_effectivity_id NUMBER;
95 l_ata_sequence_id NUMBER;
96 l_deferral_id NUMBER;
97 l_row_id VARCHAR2(2000);
98
99 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
100 l_notes_table CS_ServiceRequest_PUB.notes_table;
101 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
102
103 l_inventory_item_id NUMBER;
104 l_serial_number VARCHAR2(30);
105 l_inv_master_org_id NUMBER;
106
107 l_individual_owner NUMBER;
108 l_group_owner NUMBER;
109 l_individual_type VARCHAR2(30);
110
111 l_workflow_process_id NUMBER;
112 l_interaction_id NUMBER;
113
114 l_cs_incident_id NUMBER;
115 l_ata_rep_time NUMBER;
116
117 BEGIN
118 -- Standard start of API savepoint
119 SAVEPOINT Create_SR_SP;
120
121 -- Initialize return status to success before any code logic/validation
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 -- Standard call to check for call compatibility
125 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
131 IF FND_API.TO_BOOLEAN(p_init_msg_list)
132 THEN
133 FND_MSG_PUB.INITIALIZE;
134 END IF;
135
136 -- Log API entry point
137 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
138 THEN
139 fnd_log.string
140 (
141 G_DEBUG_PROC,
142 l_debug_module||'.begin',
143 'At the start of PLSQL procedure'
144 );
145 END IF;
146 -- API body starts here
147
148 Validate_SR_Details
149 (
150 p_x_nonroutine_rec => p_x_nonroutine_rec,
151 p_dml_operation => 'C'
152 );
153
154 -- Check Error Message stack.
155 x_msg_count := FND_MSG_PUB.count_msg;
156 IF (x_msg_count > 0)
157 THEN
158 RAISE FND_API.G_EXC_ERROR;
159 END IF;
160
161 -- Initialize the SR record.
162 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
163
164 -- Assign the SR rec values
165 l_service_request_rec.request_date := trunc(sysdate);
166 IF (p_x_nonroutine_rec.incident_date IS NULL OR p_x_nonroutine_rec.incident_date = FND_API.G_MISS_DATE)
167 THEN
168 l_service_request_rec.incident_occurred_date := l_service_request_rec.request_date;
169 ELSE
170 l_service_request_rec.incident_occurred_date := p_x_nonroutine_rec.incident_date;
171 END IF;
172 l_service_request_rec.type_id := p_x_nonroutine_rec.type_id;
173 l_service_request_rec.status_id := p_x_nonroutine_rec.status_id;
174 l_service_request_rec.caller_type := p_x_nonroutine_rec.customer_type;
175 l_service_request_rec.customer_id := p_x_nonroutine_rec.customer_id;
176 l_service_request_rec.severity_id := p_x_nonroutine_rec.severity_id;
177 l_service_request_rec.urgency_id := p_x_nonroutine_rec.urgency_id;
178 l_service_request_rec.problem_code := p_x_nonroutine_rec.problem_code;
179 l_service_request_rec.summary := p_x_nonroutine_rec.problem_summary;
180
181 SELECT inventory_item_id,
182 inventory_item_id,
183 inv_master_organization_id
184 INTO p_x_nonroutine_rec.inventory_item_id,
185 l_service_request_rec.inventory_item_id,
186 l_service_request_rec.inventory_org_id
187 FROM csi_item_instances
188 WHERE instance_id = p_x_nonroutine_rec.instance_id;
189
190 l_service_request_rec.customer_product_id := p_x_nonroutine_rec.instance_id;
191 l_service_request_rec.creation_program_code := 'AHL_NONROUTINE';
192
193 -- Handle the contact if any
194 IF
195 (
196 p_x_nonroutine_rec.contact_type IS NOT NULL AND p_x_nonroutine_rec.contact_type <> FND_API.G_MISS_CHAR
197 AND
198 p_x_nonroutine_rec.contact_id IS NOT NULL AND p_x_nonroutine_rec.contact_id <> FND_API.G_MISS_NUM
199 )
200 THEN
201 l_contacts_table(1).contact_type := p_x_nonroutine_rec.contact_type;
202 l_contacts_table(1).party_id := p_x_nonroutine_rec.contact_id;
203 l_contacts_table(1).primary_flag := 'Y';
204 END IF;
205
206 l_service_request_rec.resolution_code := p_x_nonroutine_rec.resolution_code;
207 l_service_request_rec.exp_resolution_date := p_x_nonroutine_rec.expected_resolution_date;
208 l_service_request_rec.act_resolution_date := p_x_nonroutine_rec.actual_resolution_date;
209
210 -- Call to Service Request API
211 CS_SERVICEREQUEST_PUB.Create_ServiceRequest
212 (
213 p_api_version => 3.0,
214 p_init_msg_list => FND_API.G_FALSE,
215 p_commit => FND_API.G_FALSE,
216 x_return_status => l_return_status,
217 x_msg_count => l_msg_count,
218 x_msg_data => l_msg_data,
219 p_resp_appl_id => fnd_global.resp_appl_id,
220 p_resp_id => fnd_global.resp_id,
221 p_user_id => fnd_global.user_id,
222 p_login_id => fnd_global.login_id,
223 p_org_id => NULL,
224 p_request_id => p_x_nonroutine_rec.incident_id,
225 p_request_number => NULL,
226 p_service_request_rec => l_service_request_rec,
227 p_notes => l_notes_table,
228 p_contacts => l_contacts_table,
229 p_auto_assign => 'N',
230 x_request_id => l_cs_incident_id,
231 x_request_number => p_x_nonroutine_rec.incident_number,
232 x_interaction_id => l_interaction_id,
233 x_workflow_process_id => l_workflow_process_id,
234 x_individual_owner => l_individual_owner,
235 x_group_owner => l_group_owner,
236 x_individual_type => l_individual_type
237 );
238 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
239 THEN
240 IF (G_DEBUG_UEXP >= G_DEBUG_LEVEL)
241 THEN
242 fnd_log.string
243 (
244 G_DEBUG_UEXP,
245 l_debug_module,
246 'Call to CS_SERVICEREQUEST_PUB.Create_ServiceRequest failed...'
247 );
248 END IF;
249
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252
253 -- l_cs_incident_id is anyway expected to be the same as p_x_nonroutine_rec.incident_id, still to be extra sure...
254 p_x_nonroutine_rec.incident_id := l_cs_incident_id;
255 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
256 THEN
257 fnd_log.string
258 (
259 G_DEBUG_STMT,
260 l_debug_module,
261 'New non-routine created ['||p_x_nonroutine_rec.incident_id||']'
262 );
263 END IF;
264
265 -- Retrieve the UE created by the SR, and update the necessary information...
266 SELECT unit_effectivity_id, unit_effectivity_id, object_version_number
267 INTO l_unit_effectivity_id, p_x_nonroutine_rec.unit_effectivity_id, p_x_nonroutine_rec.ue_object_version_number
268 FROM ahl_unit_effectivities_b
269 WHERE object_type = 'SR' and
270 cs_incident_id = p_x_nonroutine_rec.incident_id;
271
272 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
273 THEN
274 fnd_log.string
275 (
276 G_DEBUG_STMT,
277 l_debug_module,
278 'UE ['||l_unit_effectivity_id||'] is created for non-routine ['||p_x_nonroutine_rec.incident_id||']'
279 );
280 END IF;
281
282 -- Validate NR specific UE information passed from the frontend...
283 Validate_UE_Details(p_x_nonroutine_rec, l_unit_effectivity_id, 'C');
284
285 -- Check Error Message stack.
286 x_msg_count := FND_MSG_PUB.count_msg;
287 IF (x_msg_count > 0)
288 THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
293 THEN
294 fnd_log.string
295 (
296 G_DEBUG_STMT,
297 l_debug_module,
298 'UE validations for non-routines done'
299 );
300 END IF;
301
302 -- Update the UE record with the NR specific information
303 UPDATE ahl_unit_effectivities_b
304 SET log_series_code = p_x_nonroutine_rec.log_series_code,
305 log_series_number = p_x_nonroutine_rec.log_series_number,
306 flight_number = p_x_nonroutine_rec.flight_number,
307 -- clear_station_org_id = p_x_nonroutine_rec.clear_station_org_id,
308 -- clear_station_dept_id = p_x_nonroutine_rec.clear_station_dept_id,
309 mel_cdl_type_code = p_x_nonroutine_rec.mel_cdl_type_code,
310 position_path_id = p_x_nonroutine_rec.position_path_id,
311 ata_code = p_x_nonroutine_rec.ata_code,
312 unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id
313 WHERE unit_effectivity_id = l_unit_effectivity_id;
314
315 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
316 THEN
317 fnd_log.string
318 (
319 G_DEBUG_STMT,
320 l_debug_module,
321 'UE details updated for MEL/CDL qualification'
322 );
323 END IF;
324
325 IF (p_x_nonroutine_rec.mel_cdl_qual_flag ='C')
326 THEN
327 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
328 THEN
329 fnd_log.string
330 (
331 G_DEBUG_STMT,
332 l_debug_module,
333 'Attach MEL/CDL instructions for non-routines'
334 );
335 END IF;
336
337 /* Behavior of Unit, Item, Serial and Instance LOVs in "Unit / Component Details" sub-header
338 * validate unit is availale and active
339 * Behavior of Log Series and Number in "Unit / Component Details" sub-header
340 * validate log_series is not null
341 */
342 IF (p_x_nonroutine_rec.unit_config_header_id is null or p_x_nonroutine_rec.unit_config_header_id = FND_API.G_MISS_NUM)
343 THEN
344 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_UNIT_MAND');
345 -- Unit is mandatory for associating MEL/CDL instructions
346 FND_MSG_PUB.ADD;
347 END IF;
348
349 IF (
350 p_x_nonroutine_rec.log_series_code IS NULL OR p_x_nonroutine_rec.log_series_code = FND_API.G_MISS_CHAR
351 AND
352 p_x_nonroutine_rec.log_series_number IS NULL OR p_x_nonroutine_rec.log_series_number = FND_API.G_MISS_NUM
353 )
354 THEN
355 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_MAND');
356 -- Log Series and Number are mandatory for associating MEL/CDL instructions
357 FND_MSG_PUB.ADD;
358 END IF;
359
360 -- Retrieve relevant MEL/CDL ata sequence
361 Get_Ata_Sequence(l_unit_effectivity_id,p_x_nonroutine_rec.ata_code, l_ata_sequence_id);
362
363 -- Check Error Message stack.
364 x_msg_count := FND_MSG_PUB.count_msg;
365 IF (x_msg_count > 0)
366 THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 IF (l_ata_sequence_id IS NOT NULL)
371 THEN
372 -- Bug #5230869 - validate inc_occ_date + rep_time >= inc_date
373 SELECT repcat.repair_time
374 INTO l_ata_rep_time
375 FROM ahl_mel_cdl_ata_sequences ata, ahl_repair_categories repcat
376 WHERE ata.repair_category_id = repcat.repair_category_id and ata.mel_cdl_ata_sequence_id = l_ata_sequence_id;
377
378 IF (NVL(l_ata_rep_time, 0) <> 0 AND trunc(l_service_request_rec.incident_occurred_date) + trunc(l_ata_rep_time/24) < trunc(l_service_request_rec.request_date))
379 THEN
380 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_NO_ACCOM');
381 -- Repair Time of the associated MEL/CDL Instructions cannot accomodate resolution of the Non-routine before Log Date
382 FND_MSG_PUB.ADD;
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385
386 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
387 THEN
388 fnd_log.string
389 (
390 G_DEBUG_STMT,
391 l_debug_module,
392 'Qualification [ata_sequence_id='||l_ata_sequence_id||'] computed from [unit_config_header_id='||
393 p_x_nonroutine_rec.unit_config_header_id||'][mel_cdl_type_code='||p_x_nonroutine_rec.mel_cdl_type_code||
394 '][position_path_id='||p_x_nonroutine_rec.position_path_id||'][ata_code='||p_x_nonroutine_rec.ata_code||']'
395 );
396 END IF;
397
398 AHL_UNIT_DEFERRALS_PKG.INSERT_ROW
399 (
400 X_ROWID => l_row_id,
401 X_UNIT_DEFERRAL_ID => l_deferral_id,
402 X_ATTRIBUTE14 => null,
403 X_ATTRIBUTE15 => null,
404 X_ATTRIBUTE_CATEGORY => null,
405 X_ATTRIBUTE1 => null,
406 X_ATTRIBUTE2 => null,
407 X_ATTRIBUTE3 => null,
408 X_ATTRIBUTE4 => null,
409 X_ATTRIBUTE5 => null,
410 X_ATTRIBUTE6 => null,
411 X_ATTRIBUTE7 => null,
412 X_ATTRIBUTE8 => null,
413 X_ATTRIBUTE9 => null,
414 X_ATTRIBUTE10 => null,
415 X_ATTRIBUTE11 => null,
416 X_ATTRIBUTE12 => null,
417 X_ATTRIBUTE13 => null,
418 X_AFFECT_DUE_CALC_FLAG => 'N',
419 X_DEFER_REASON_CODE => null,
420 X_USER_DEFERRAL_TYPE => null,
421 X_DEFERRAL_EFFECTIVE_ON => l_service_request_rec.incident_occurred_date,
422 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
423 X_UNIT_DEFERRAL_TYPE => p_x_nonroutine_rec.mel_cdl_type_code,
424 X_SET_DUE_DATE => null,
425 X_APPROVAL_STATUS_CODE => 'DRAFT',
426 X_SKIP_MR_FLAG => null,
427 X_OBJECT_VERSION_NUMBER => 1,
428 X_ATA_SEQUENCE_ID => l_ata_sequence_id,
429 X_REMARKS => null,
430 X_APPROVER_NOTES => null,
431 X_CREATION_DATE => sysdate,
432 X_CREATED_BY => fnd_global.user_id,
433 X_LAST_UPDATE_DATE => sysdate,
434 X_LAST_UPDATED_BY => fnd_global.user_id,
435 X_LAST_UPDATE_LOGIN => fnd_global.login_id
436 );
437
438 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
439 THEN
440 fnd_log.string
441 (
442 G_DEBUG_STMT,
443 l_debug_module,
444 'Insert unit_deferral ['||l_deferral_id||'] with relevant MEL/CDL qualification information'
445 );
446 END IF;
447 END IF;
448 END IF;
449
450 -- API body ends here
451 -- Log API exit point
452 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
453 THEN
454 fnd_log.string
455 (
456 G_DEBUG_PROC,
457 l_debug_module||'.end',
458 'At the end of PLSQL procedure'
459 );
460 END IF;
461
462 -- Check Error Message stack.
463 x_msg_count := FND_MSG_PUB.count_msg;
464 IF (x_msg_count > 0)
465 THEN
466 RAISE FND_API.G_EXC_ERROR;
467 END IF;
468
469 -- Commit if p_commit = FND_API.G_TRUE
470 IF FND_API.TO_BOOLEAN(p_commit)
471 THEN
472 COMMIT WORK;
473 END IF;
474
475 -- Standard call to get message count and if count is 1, get message info
476 FND_MSG_PUB.count_and_get
477 (
478 p_count => x_msg_count,
479 p_data => x_msg_data,
480 p_encoded => FND_API.G_FALSE
481 );
482
483 EXCEPTION
484 WHEN FND_API.G_EXC_ERROR THEN
485 x_return_status := FND_API.G_RET_STS_ERROR;
486 Rollback to Create_SR_SP;
487 FND_MSG_PUB.count_and_get
488 (
489 p_count => x_msg_count,
490 p_data => x_msg_data,
491 p_encoded => FND_API.G_FALSE
492 );
493
494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 Rollback to Create_SR_SP;
497 FND_MSG_PUB.count_and_get
498 (
499 p_count => x_msg_count,
500 p_data => x_msg_data,
501 p_encoded => FND_API.G_FALSE
502 );
503
504 WHEN OTHERS THEN
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 Rollback to Create_SR_SP;
507 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
508 THEN
509 FND_MSG_PUB.add_exc_msg
510 (
511 p_pkg_name => G_PKG_NAME,
512 p_procedure_name => 'Create_SR',
513 p_error_text => SUBSTR(SQLERRM,1,240)
514 );
515 END IF;
516 FND_MSG_PUB.count_and_get
517 (
518 p_count => x_msg_count,
519 p_data => x_msg_data,
520 p_encoded => FND_API.G_FALSE
521 );
522 END Create_SR;
523
524 ------------------------------
525 -- Spec Procedure Update_SR --
526 ------------------------------
527 PROCEDURE Update_SR
528 (
529 -- Standard IN params
530 p_api_version IN NUMBER,
531 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
532 p_commit IN VARCHAR2 := FND_API.G_FALSE,
533 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
534 p_default IN VARCHAR2 := FND_API.G_FALSE,
535 p_module_type IN VARCHAR2 := NULL,
536 -- Standard OUT params
537 x_return_status OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_msg_data OUT NOCOPY VARCHAR2,
540 -- Procedure IN, OUT, IN/OUT params
541 p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type
542 )
543 IS
544 -- Declare local variables
545 l_api_name CONSTANT VARCHAR2(30) := 'Update_SR';
546 l_api_version CONSTANT NUMBER := 1.0;
547 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
548
549 l_return_status VARCHAR2(1);
550 l_msg_count NUMBER;
551 l_msg_data VARCHAR2(2000);
552
553 -- Define cursors
554 l_unit_effectivity_id NUMBER;
555 l_ata_sequence_id NUMBER;
556
557 CURSOR get_ue_details
558 (
559 c_incident_id number
560 )
561 IS
562 SELECT unit_effectivity_id, object_version_number
563 FROM ahl_unit_effectivities_b
564 WHERE object_type = 'SR' and
565 cs_incident_id = c_incident_id and
566 nvl(status_code, 'X') <> 'DEFERRED';
567
568 CURSOR get_deferral_rec
569 (
570 c_unit_effectivity_id number
571 )
572 IS
573 SELECT *
574 FROM ahl_unit_deferrals_vl
575 WHERE unit_effectivity_id = c_unit_effectivity_id AND
576 unit_deferral_type IN ('MEL','CDL');
577 -- may need to add more validations here to get the right deferral record...
578
579 l_deferral_rec get_deferral_rec%rowtype;
580 l_deferral_id NUMBER;
581 l_row_id VARCHAR2(2000);
582
583 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
584 l_notes_table CS_ServiceRequest_PUB.notes_table;
585 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
586
587 l_contact_primary_flag CONSTANT VARCHAR2(1) := 'Y';
588
589 l_workflow_process_id NUMBER ;
590 l_interaction_id NUMBER ;
591 l_unit_instance NUMBER;
592 l_ue_ovn NUMBER;
593 l_ata_rep_time NUMBER;
594
595 CURSOR get_contact_details
596 (
597 c_incident_id number
598 )
599 IS
600 SELECT contact_type, party_id
601 FROM CS_HZ_SR_CONTACT_POINTS
602 WHERE incident_id = c_incident_id
603 AND primary_flag = 'Y';
604
605 l_contact_rec get_contact_details%rowtype;
606
607 -- Balaji added for the bug that SR ovn is not correctly returned from the call
608 -- to CS_SERVICEREQUEST_PUB.Update_ServiceRequest.
609 -- Begin change
610 CURSOR c_get_sr_ovn(c_incident_id NUMBER)
611 IS
612 SELECT object_version_number
613 FROM CS_INCIDENTS
614 WHERE incident_id = c_incident_id;
615 -- End change
616 BEGIN
617 -- Standard start of API savepoint
618 SAVEPOINT Update_SR_SP;
619
620 -- Initialize return status to success before any code logic/validation
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 -- Standard call to check for call compatibility
624 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
625 THEN
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
627 END IF;
628
629 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
630 IF FND_API.TO_BOOLEAN(p_init_msg_list)
631 THEN
632 FND_MSG_PUB.INITIALIZE;
633 END IF;
634
635 -- Log API entry point
636 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
637 THEN
638 fnd_log.string
639 (
640 G_DEBUG_PROC,
641 l_debug_module||'.begin',
642 'At the start of PLSQL procedure'
643 );
644 END IF;
645 -- API body starts here
646
647 Validate_SR_Details
648 (
649 p_x_nonroutine_rec => p_x_nonroutine_rec,
650 p_dml_operation => 'U'
651 );
652
653 -- Check Error Message stack.
654 x_msg_count := FND_MSG_PUB.count_msg;
655 IF (x_msg_count > 0)
656 THEN
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659
660 -- Initialize the SR record.
661 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
662
663 l_service_request_rec.type_id := p_x_nonroutine_rec.type_id;
664 l_service_request_rec.status_id := p_x_nonroutine_rec.status_id;
665 -- l_service_request_rec.caller_type := p_x_nonroutine_rec.customer_type;
666 l_service_request_rec.customer_id := p_x_nonroutine_rec.customer_id;
667 l_service_request_rec.severity_id := p_x_nonroutine_rec.severity_id;
668 l_service_request_rec.urgency_id := p_x_nonroutine_rec.urgency_id;
669 l_service_request_rec.problem_code := p_x_nonroutine_rec.problem_code;
670 l_service_request_rec.summary := p_x_nonroutine_rec.problem_summary;
671
672 SELECT incident_date, incident_occurred_date, incident_occurred_date
673 INTO l_service_request_rec.request_date, l_service_request_rec.incident_occurred_date, p_x_nonroutine_rec.incident_date
674 FROM cs_incidents_all_b
675 WHERE incident_id = p_x_nonroutine_rec.incident_id;
676
677 SELECT p_x_nonroutine_rec.inventory_item_id,
678 inv_master_organization_id
679 INTO l_service_request_rec.inventory_item_id,
680 l_service_request_rec.inventory_org_id
681 FROM csi_item_instances
682 WHERE instance_id = p_x_nonroutine_rec.instance_id;
683
684 l_service_request_rec.customer_product_id := p_x_nonroutine_rec.instance_id;
685 l_service_request_rec.creation_program_code := 'AHL_NONROUTINE';
686
687 -- Handle the contact if any (code below changed per new R12 CS package's CS_SRCONTACT_PKG.check_duplicates() method)
688 IF
689 (
690 p_x_nonroutine_rec.contact_type IS NOT NULL AND p_x_nonroutine_rec.contact_type <> FND_API.G_MISS_CHAR
691 AND
692 p_x_nonroutine_rec.contact_id IS NOT NULL AND p_x_nonroutine_rec.contact_id <> FND_API.G_MISS_NUM
693 )
694 THEN
695 OPEN get_contact_details(p_x_nonroutine_rec.incident_id);
696 FETCH get_contact_details INTO l_contact_rec;
697 IF (get_contact_details%NOTFOUND OR (get_contact_details%FOUND AND (l_contact_rec.contact_type <> p_x_nonroutine_rec.contact_type OR l_contact_rec.party_id <> p_x_nonroutine_rec.contact_id)))
698 THEN
699 l_contacts_table(1).contact_type := p_x_nonroutine_rec.contact_type;
700 l_contacts_table(1).party_id := p_x_nonroutine_rec.contact_id;
701 l_contacts_table(1).primary_flag := 'Y';
702 END IF;
703 CLOSE get_contact_details;
704 END IF;
705
706 l_service_request_rec.resolution_code := p_x_nonroutine_rec.resolution_code;
707 l_service_request_rec.exp_resolution_date := p_x_nonroutine_rec.expected_resolution_date;
708 l_service_request_rec.act_resolution_date := p_x_nonroutine_rec.actual_resolution_date;
709
710 -- Call to Service Request API
711 CS_SERVICEREQUEST_PUB.Update_ServiceRequest
712 (
713 p_api_version => 3.0,
714 p_init_msg_list => FND_API.G_FALSE,
715 p_commit => FND_API.G_FALSE,
716 x_return_status => l_return_status,
717 x_msg_count => l_msg_count,
718 x_msg_data => l_msg_data,
719 p_request_id => p_x_nonroutine_rec.incident_id,
720 p_request_number => NULL,
721 p_audit_comments => NULL,
722 p_object_version_number => p_x_nonroutine_rec.incident_object_version_number,
723 p_resp_appl_id => fnd_global.resp_appl_id,
724 p_resp_id => fnd_global.resp_id,
725 p_last_updated_by => fnd_global.user_id,
726 p_last_update_login => fnd_global.login_id,
727 p_last_update_date => sysdate,
728 p_service_request_rec => l_service_request_rec,
729 p_notes => l_notes_table,
730 p_contacts => l_contacts_table,
731 p_called_by_workflow => NULL,
732 p_workflow_process_id => NULL,
733 x_workflow_process_id => l_workflow_process_id,
734 x_interaction_id => l_interaction_id
735 );
736 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
737 THEN
738 IF (G_DEBUG_UEXP >= G_DEBUG_LEVEL)
739 THEN
740 fnd_log.string
741 (
742 G_DEBUG_UEXP,
743 l_debug_module,
744 'Call to CS_SERVICEREQUEST_PUB.Update_ServiceRequest failed...'
745 );
746 END IF;
747
748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
749 ELSE
750
751 IF (G_DEBUG_UEXP >= G_DEBUG_LEVEL)
752 THEN
753 fnd_log.string
754 (
755 G_DEBUG_UEXP,
756 l_debug_module,
757 'l_msg_count->'||l_msg_count||' , '||'l_msg_data->'||l_msg_data
758 );
759 fnd_log.string
760 (
761 G_DEBUG_UEXP,
762 l_debug_module,
763 'l_return_status->'||l_return_status
764 );
765 END IF;
766 -- re-initialize stack to get rid of warnings.
767 FND_MSG_PUB.INITIALIZE;
768 END IF;
769
770 -- Retrieve ue_id and ovn accordingly...
771 IF (p_x_nonroutine_rec.unit_effectivity_id IS NOT NULL AND p_x_nonroutine_rec.unit_effectivity_id <> FND_API.G_MISS_NUM)
772 THEN
773 SELECT object_version_number
774 INTO p_x_nonroutine_rec.ue_object_version_number
775 FROM ahl_unit_effectivities_b
776 WHERE unit_effectivity_id = p_x_nonroutine_rec.unit_effectivity_id;
777 ELSE
778 OPEN get_ue_details (p_x_nonroutine_rec.incident_id);
779 FETCH get_ue_details INTO p_x_nonroutine_rec.unit_effectivity_id, p_x_nonroutine_rec.ue_object_version_number;
780 CLOSE get_ue_details;
781 END IF;
782
783 l_unit_effectivity_id := p_x_nonroutine_rec.unit_effectivity_id;
784
785 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
786 THEN
787 fnd_log.string
788 (
789 G_DEBUG_STMT,
790 l_debug_module,
791 'UE ['||l_unit_effectivity_id||'] is updated for non-routine ['||p_x_nonroutine_rec.incident_id||']'
792 );
793 END IF;
794
795 -- Validate NR specific UE information passed from the frontend...
796 Validate_UE_Details(p_x_nonroutine_rec, l_unit_effectivity_id, 'U');
797
798 -- Check Error Message stack.
799 x_msg_count := FND_MSG_PUB.count_msg;
800 IF (x_msg_count > 0)
801 THEN
802 RAISE FND_API.G_EXC_ERROR;
803 END IF;
804
805 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
806 THEN
807 fnd_log.string
808 (
809 G_DEBUG_STMT,
810 l_debug_module,
811 'UE validations for MEL/CDL non-routines done'
812 );
813 END IF;
814
815 -- Update the UE record with the NR specific information
816 -- Note: Log Series, Number cannot be modified once created...
817 UPDATE ahl_unit_effectivities_b
818 SET log_series_code = p_x_nonroutine_rec.log_series_code,
819 log_series_number = p_x_nonroutine_rec.log_series_number,
820 flight_number = p_x_nonroutine_rec.flight_number,
821 -- clear_station_org_id = p_x_nonroutine_rec.clear_station_org_id,
822 -- clear_station_dept_id = p_x_nonroutine_rec.clear_station_dept_id,
823 unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id
824 WHERE unit_effectivity_id = l_unit_effectivity_id;
825
826 IF NOT Is_MEL_CDL_Approved(l_unit_effectivity_id)
827 THEN
828 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
829 THEN
830 fnd_log.string
831 (
832 G_DEBUG_STMT,
833 l_debug_module,
834 'UE ['||l_unit_effectivity_id||'] is not MEL/CDL approved / pending approval'
835 );
836 END IF;
837
838 -- Update the UE record with the MEL/CDL Qualification specific information
839 UPDATE ahl_unit_effectivities_b
840 SET mel_cdl_type_code = p_x_nonroutine_rec.mel_cdl_type_code,
841 position_path_id = p_x_nonroutine_rec.position_path_id,
842 ata_code = p_x_nonroutine_rec.ata_code
843 WHERE unit_effectivity_id = l_unit_effectivity_id;
844
845 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
846 THEN
847 fnd_log.string
848 (
849 G_DEBUG_STMT,
850 l_debug_module,
851 'UE details updated for MEL/CDL qualification'
852 );
853 END IF;
854
855 IF (p_x_nonroutine_rec.mel_cdl_qual_flag ='C')
856 THEN
857 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
858 THEN
859 fnd_log.string
860 (
861 G_DEBUG_STMT,
862 l_debug_module,
863 'Attach/Change MEL/CDL instructions for non-routines'
864 );
865 END IF;
866
867 /* Behavior of Unit, Item, Serial and Instance LOVs in "Unit / Component Details" sub-header
868 * validate unit is availale and active
869 * Behavior of Log Series and Number in "Unit / Component Details" sub-header
870 * validate log_series is not null
871 */
872 IF (p_x_nonroutine_rec.unit_config_header_id is null or p_x_nonroutine_rec.unit_config_header_id = FND_API.G_MISS_NUM)
873 THEN
874 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_UNIT_MAND');
875 -- Unit is mandatory for associating MEL/CDL instructions
876 FND_MSG_PUB.ADD;
877 END IF;
878
879 IF (
880 p_x_nonroutine_rec.log_series_code IS NULL OR p_x_nonroutine_rec.log_series_code = FND_API.G_MISS_CHAR
881 AND
882 p_x_nonroutine_rec.log_series_number IS NULL OR p_x_nonroutine_rec.log_series_number = FND_API.G_MISS_NUM
883 )
884 THEN
885 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_MAND');
886 -- Log Series and Number are mandatory for associating MEL/CDL instructions
887 FND_MSG_PUB.ADD;
888 END IF;
889
890 -- Retrieve relevant MEL/CDL ata sequence
891 Get_Ata_Sequence(l_unit_effectivity_id,p_x_nonroutine_rec.ata_code, l_ata_sequence_id);
892
893 -- Check Error Message stack.
894 x_msg_count := FND_MSG_PUB.count_msg;
895 IF (x_msg_count > 0)
896 THEN
897 RAISE FND_API.G_EXC_ERROR;
898 END IF;
899
900 IF (l_ata_sequence_id IS NOT NULL)
901 THEN
902 -- Bug #5230869 - validate inc_occ_date + rep_time >= inc_date
903 SELECT repcat.repair_time
904 INTO l_ata_rep_time
905 FROM ahl_mel_cdl_ata_sequences ata, ahl_repair_categories repcat
906 WHERE ata.repair_category_id = repcat.repair_category_id and ata.mel_cdl_ata_sequence_id = l_ata_sequence_id;
907
908 IF (NVL(l_ata_rep_time, 0) <> 0 AND trunc(l_service_request_rec.incident_occurred_date) + trunc(l_ata_rep_time/24) < trunc(l_service_request_rec.request_date))
909 THEN
910 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_NO_ACCOM');
911 -- Repair Time of the associated MEL/CDL Instructions cannot accomodate resolution of the Non-routine before Log Date
912 FND_MSG_PUB.ADD;
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915
916 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
917 THEN
918 fnd_log.string
919 (
920 G_DEBUG_STMT,
921 l_debug_module,
922 'Qualification ata_sequence_id ['||l_ata_sequence_id||'] computed from unit_config_header_id ['||p_x_nonroutine_rec.unit_config_header_id||'],
923 mel_cdl_type_code ['||p_x_nonroutine_rec.mel_cdl_type_code||'],position_path_id ['||p_x_nonroutine_rec.position_path_id||']
924 ,ata_code ['||p_x_nonroutine_rec.ata_code||']'
925 );
926 END IF;
927
928 OPEN get_deferral_rec(l_unit_effectivity_id);
929 FETCH get_deferral_rec INTO l_deferral_rec;
930 IF (get_deferral_rec%FOUND)
931 THEN
932 AHL_UNIT_DEFERRALS_PKG.UPDATE_ROW
933 (
934 X_UNIT_DEFERRAL_ID => l_deferral_rec.unit_deferral_id,
935 X_ATTRIBUTE14 => l_deferral_rec.attribute14,
936 X_ATTRIBUTE15 => l_deferral_rec.attribute15,
937 X_ATTRIBUTE_CATEGORY => l_deferral_rec.attribute_category,
938 X_ATTRIBUTE1 => l_deferral_rec.attribute1,
939 X_ATTRIBUTE2 => l_deferral_rec.attribute2,
940 X_ATTRIBUTE3 => l_deferral_rec.attribute3,
941 X_ATTRIBUTE4 => l_deferral_rec.attribute4,
942 X_ATTRIBUTE5 => l_deferral_rec.attribute5,
943 X_ATTRIBUTE6 => l_deferral_rec.attribute6,
944 X_ATTRIBUTE7 => l_deferral_rec.attribute7,
945 X_ATTRIBUTE8 => l_deferral_rec.attribute8,
946 X_ATTRIBUTE9 => l_deferral_rec.attribute9,
947 X_ATTRIBUTE10 => l_deferral_rec.attribute10,
948 X_ATTRIBUTE11 => l_deferral_rec.attribute11,
949 X_ATTRIBUTE12 => l_deferral_rec.attribute12,
950 X_ATTRIBUTE13 => l_deferral_rec.attribute13,
951 X_AFFECT_DUE_CALC_FLAG => 'N',
952 X_DEFER_REASON_CODE => l_deferral_rec.defer_reason_code,
953 X_DEFERRAL_EFFECTIVE_ON => l_deferral_rec.deferral_effective_on,
954 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
955 X_UNIT_DEFERRAL_TYPE => p_x_nonroutine_rec.mel_cdl_type_code,
956 X_SET_DUE_DATE => l_deferral_rec.set_due_date,
957 X_APPROVAL_STATUS_CODE => 'DRAFT',
958 X_SKIP_MR_FLAG => l_deferral_rec.skip_mr_flag,
959 X_OBJECT_VERSION_NUMBER => l_deferral_rec.object_version_number + 1,
960 X_ATA_SEQUENCE_ID => l_ata_sequence_id,
961 X_REMARKS => l_deferral_rec.remarks,
962 X_APPROVER_NOTES => l_deferral_rec.approver_notes,
963 X_USER_DEFERRAL_TYPE => null,
964 X_LAST_UPDATE_DATE => sysdate,
965 X_LAST_UPDATED_BY => fnd_global.user_id,
966 X_LAST_UPDATE_LOGIN => fnd_global.login_id
967 );
968
969 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
970 THEN
971 fnd_log.string
972 (
973 G_DEBUG_STMT,
974 l_debug_module,
975 'Updated unit_deferral ['||l_deferral_rec.unit_deferral_id||'] with relevant MEL/CDL qualification information'
976 );
977 END IF;
978 ELSE
979 AHL_UNIT_DEFERRALS_PKG.INSERT_ROW
980 (
981 X_ROWID => l_row_id,
982 X_UNIT_DEFERRAL_ID => l_deferral_id,
983 X_ATTRIBUTE14 => null,
984 X_ATTRIBUTE15 => null,
985 X_ATTRIBUTE_CATEGORY => null,
986 X_ATTRIBUTE1 => null,
987 X_ATTRIBUTE2 => null,
988 X_ATTRIBUTE3 => null,
989 X_ATTRIBUTE4 => null,
990 X_ATTRIBUTE5 => null,
991 X_ATTRIBUTE6 => null,
992 X_ATTRIBUTE7 => null,
993 X_ATTRIBUTE8 => null,
994 X_ATTRIBUTE9 => null,
995 X_ATTRIBUTE10 => null,
996 X_ATTRIBUTE11 => null,
997 X_ATTRIBUTE12 => null,
998 X_ATTRIBUTE13 => null,
999 X_AFFECT_DUE_CALC_FLAG => 'N',
1000 X_DEFER_REASON_CODE => null,
1001 X_DEFERRAL_EFFECTIVE_ON => l_service_request_rec.incident_occurred_date,
1002 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
1003 X_UNIT_DEFERRAL_TYPE => p_x_nonroutine_rec.mel_cdl_type_code,
1004 X_SET_DUE_DATE => null,
1005 X_APPROVAL_STATUS_CODE => 'DRAFT',
1006 X_SKIP_MR_FLAG => null,
1007 X_OBJECT_VERSION_NUMBER => 1,
1008 X_ATA_SEQUENCE_ID => l_ata_sequence_id,
1009 X_REMARKS => null,
1010 X_APPROVER_NOTES => null,
1011 X_USER_DEFERRAL_TYPE => null,
1012 X_CREATION_DATE => sysdate,
1013 X_CREATED_BY => fnd_global.user_id,
1014 X_LAST_UPDATE_DATE => sysdate,
1015 X_LAST_UPDATED_BY => fnd_global.user_id,
1016 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1017 );
1018
1019 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
1020 THEN
1021 fnd_log.string
1022 (
1023 G_DEBUG_STMT,
1024 l_debug_module,
1025 'Insert unit_deferral ['||l_deferral_id||'] with relevant MEL/CDL qualification information'
1026 );
1027 END IF;
1028 END IF;
1029 CLOSE get_deferral_rec;
1030 END IF;
1031
1032 ELSIF (p_x_nonroutine_rec.mel_cdl_qual_flag ='D')
1033 THEN
1034
1035 DELETE FROM ahl_unit_deferrals_tl
1036 WHERE unit_deferral_id IN
1037 (
1038 SELECT unit_deferral_id
1039 FROM ahl_unit_deferrals_b
1040 WHERE unit_effectivity_id = l_unit_effectivity_id
1041 );
1042
1043 DELETE FROM ahl_unit_deferrals_b
1044 WHERE unit_effectivity_id = l_unit_effectivity_id;
1045
1046 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
1047 THEN
1048 fnd_log.string
1049 (
1050 G_DEBUG_STMT,
1051 l_debug_module,
1052 'Deleted unit_deferral ['||l_deferral_rec.unit_deferral_id||']'
1053 );
1054 END IF;
1055
1056 END IF;
1057 /*
1058 ELSE
1059 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_QUAL_APPR');
1060 -- Cannot modify MEL/CDL Instructions for Non-routine pending for MEL/CDL approval or already approved
1061 FND_MSG_PUB.ADD;
1062 */
1063 END IF;
1064
1065 -- Balaji added for the bug that SR ovn is not correctly returned from the call
1066 -- to CS_SERVICEREQUEST_PUB.Update_ServiceRequest.
1067 -- Begin change
1068 OPEN c_get_sr_ovn(p_x_nonroutine_rec.incident_id);
1069 FETCH c_get_sr_ovn INTO p_x_nonroutine_rec.incident_object_version_number;
1070 CLOSE c_get_sr_ovn;
1071 -- End change
1072
1073 -- API body ends here
1074 -- Log API exit point
1075 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1076 THEN
1077 fnd_log.string
1078 (
1079 G_DEBUG_PROC,
1080 l_debug_module||'.end',
1081 'At the end of PLSQL procedure'
1082 );
1083 END IF;
1084
1085 -- Check Error Message stack.
1086 x_msg_count := FND_MSG_PUB.count_msg;
1087 IF (x_msg_count > 0)
1088 THEN
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091
1092 -- Commit if p_commit = FND_API.G_TRUE
1093 IF FND_API.TO_BOOLEAN(p_commit)
1094 THEN
1095 COMMIT WORK;
1096 END IF;
1097
1098 -- Standard call to get message count and if count is 1, get message info
1099 FND_MSG_PUB.count_and_get
1100 (
1101 p_count => x_msg_count,
1102 p_data => x_msg_data,
1103 p_encoded => FND_API.G_FALSE
1104 );
1105
1106 EXCEPTION
1107 WHEN FND_API.G_EXC_ERROR THEN
1108 x_return_status := FND_API.G_RET_STS_ERROR;
1109 Rollback to Update_SR_SP;
1110 FND_MSG_PUB.count_and_get
1111 (
1112 p_count => x_msg_count,
1113 p_data => x_msg_data,
1114 p_encoded => FND_API.G_FALSE
1115 );
1116
1117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1119 Rollback to Update_SR_SP;
1120 FND_MSG_PUB.count_and_get
1121 (
1122 p_count => x_msg_count,
1123 p_data => x_msg_data,
1124 p_encoded => FND_API.G_FALSE
1125 );
1126
1127 WHEN OTHERS THEN
1128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1129 Rollback to Update_SR_SP;
1130 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1131 THEN
1132 FND_MSG_PUB.add_exc_msg
1133 (
1134 p_pkg_name => G_PKG_NAME,
1135 p_procedure_name => 'Update_SR',
1136 p_error_text => SUBSTR(SQLERRM,1,240)
1137 );
1138 END IF;
1139 FND_MSG_PUB.count_and_get
1140 (
1141 p_count => x_msg_count,
1142 p_data => x_msg_data,
1143 p_encoded => FND_API.G_FALSE
1144 );
1145 END Update_SR;
1146
1147 ----------------------------------------------
1148 -- Spec Procedure Initiate_Mel_Cdl_Approval --
1149 ----------------------------------------------
1150 PROCEDURE Initiate_Mel_Cdl_Approval
1151 (
1152 -- Standard IN params
1153 p_api_version IN NUMBER,
1154 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1155 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1156 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1157 p_default IN VARCHAR2 := FND_API.G_FALSE,
1158 p_module_type IN VARCHAR2 := NULL,
1159 -- Standard OUT params
1160 x_return_status OUT NOCOPY VARCHAR2,
1161 x_msg_count OUT NOCOPY NUMBER,
1162 x_msg_data OUT NOCOPY VARCHAR2,
1163 -- Procedure IN, OUT, IN/OUT params
1164 p_ue_id IN NUMBER,
1165 p_ue_object_version IN NUMBER
1166 )
1167 IS
1168 -- Declare local variables
1169 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Mel_Cdl_Approval';
1170 l_api_version CONSTANT NUMBER := 1.0;
1171 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1172
1173 l_return_status VARCHAR2(1);
1174 l_msg_count NUMBER;
1175 l_msg_data VARCHAR2(2000);
1176
1177 l_junk VARCHAR2(1);
1178 l_NR_count NUMBER;
1179 l_count NUMBER;
1180 l_new_status_code VARCHAR2(30);
1181
1182 -- Define cursors
1183
1184 -- get deferral details.
1185 cursor ue_deferral_csr(p_ue_id IN NUMBER)
1186 is
1187 select unit_deferral_id, object_version_number, unit_deferral_type,
1188 approval_status_code, ata_sequence_id, deferral_effective_on
1189 from ahl_unit_deferrals_b
1190 where unit_effectivity_id = p_ue_id and
1191 unit_deferral_type in ('MEL', 'CDL')
1192 for update of object_version_number;
1193
1194 -- get ue details.
1195 cursor unit_effect_csr (p_ue_id IN NUMBER)
1196 is
1197 select unit_effectivity_id, object_version_number, status_code,
1198 cs_incident_id, mel_cdl_type_code, csi_item_instance_id,
1199 unit_config_header_id, log_series_code, log_series_number
1200 from ahl_unit_effectivities_b
1201 where unit_effectivity_id = p_ue_id
1202 and object_type = 'SR'
1203 and (status_code IS NULL or status_code = 'INIT_DUE')
1204 for update of object_version_number;
1205
1206 -- get visit details.
1207 cursor visit_det_csr(p_ue_id IN NUMBER)
1208 is
1209 select 'x'
1210 from ahl_visit_tasks_b vts, ahl_visits_b vst
1211 where vst.visit_id = vts.visit_id
1212 and NVL(vst.status_code,'x') IN ('PARTIALLY RELEASED','RELEASED')
1213 and NVL(vts.status_code,'x') = 'RELEASED'
1214 and vts.unit_effectivity_id = p_ue_id ;
1215
1216 -- query for defer details.
1217 cursor ue_defer_csr (p_ue_id IN NUMBER)
1218 is
1219 select 'x'
1220 from ahl_unit_deferrals_b
1221 where unit_effectivity_id = p_ue_id
1222 and unit_deferral_type = 'DEFERRAL'
1223 and approval_status_code = 'DEFERRAL_PENDING';
1224
1225 -- query for defer details for child UEs.
1226 cursor ue_defer_child_csr (p_ue_id IN NUMBER)
1227 is
1228 select 'x'
1229 from ahl_unit_deferrals_b
1230 where unit_effectivity_id IN (select related_ue_id
1231 from ahl_ue_relationships
1232 start with ue_id = p_ue_id
1233 connect by PRIOR related_ue_id = ue_id)
1234 and unit_deferral_type = 'DEFERRAL'
1235 and approval_status_code = 'DEFERRAL_PENDING';
1236
1237 -- get mel/cdl details.
1238 cursor mel_cdl_header_csr (p_ata_sequence_id IN NUMBER)
1239 is
1240 select seq.INSTALLED_NUMBER, seq.DISPATCH_NUMBER, nvl(rc.repair_time,0)
1241 from ahl_mel_cdl_ata_sequences seq, ahl_mel_cdl_headers hdr,
1242 ahl_repair_categories rc
1243 where seq.mel_cdl_header_id = hdr.mel_cdl_header_id
1244 and seq.mel_cdl_ata_sequence_id = p_ata_sequence_id
1245 and nvl(hdr.expired_date, sysdate+1) > sysdate
1246 and seq.repair_category_id = rc.repair_category_id;
1247
1248 -- get open NRs for the ata sequence.
1249 cursor get_open_NRs_csr (p_ata_sequence_id IN NUMBER,
1250 p_unit_config_header_id IN NUMBER)
1251 --p_cs_incident_id IN NUMBER)
1252 is
1253 select count(ue.cs_incident_id)
1254 from AHL_UNIT_EFFECTIVITIES_B UE, CS_INCIDENTS_ALL_B CS,
1255 CS_INCIDENT_STATUSES_B STATUS, AHL_UNIT_DEFERRALS_B UDF,
1256 AHL_MEL_CDL_ATA_SEQUENCES SEQ
1257 where SEQ.MEL_CDL_ATA_SEQUENCE_ID = UDF.ATA_SEQUENCE_ID
1258 AND UDF.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
1259 AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
1260 AND CS.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
1261 AND NVL(STATUS.CLOSE_FLAG, 'N') = 'N'
1262 AND SEQ.mel_cdl_ata_sequence_id = p_ata_sequence_id
1263 AND UE.unit_config_header_id = p_unit_config_header_id
1264 AND (UE.status_code IS NULL OR UE.status_code = 'INIT_DUE')
1265 AND UDF.approval_status_code IN ('DEFERRED','DEFERRAL_PENDING');
1266 --AND UE.cs_incident_id <> p_cs_incident_id;
1267
1268 -- validate interrelationships.
1269 cursor get_ata_relationship_csr (p_ata_sequence_id IN NUMBER)
1270 is
1271 select related_ata_sequence_id ata_sequence_id
1272 from ahl_mel_cdl_relationships
1273 where ata_sequence_id = p_ata_sequence_id
1274 UNION ALL
1275 select ata_sequence_id
1276 from ahl_mel_cdl_relationships
1277 where related_ata_sequence_id = p_ata_sequence_id;
1278
1279 -- check repair category for time limit.
1280 cursor get_exp_resolution_csr(p_cs_incident_id IN NUMBER)
1281 is
1282 select EXPECTED_RESOLUTION_DATE
1283 from cs_incidents_all_b cs
1284 where cs.incident_id = p_cs_incident_id;
1285
1286 l_deferral_id NUMBER;
1287 l_deferral_ovn NUMBER;
1288 l_deferral_type VARCHAR2(30);
1289 l_ue_rec unit_effect_csr%ROWTYPE;
1290 l_deferral_rec ue_deferral_csr%ROWTYPE;
1291 l_installed_number NUMBER;
1292 l_dispatch_number NUMBER;
1293 l_repair_time NUMBER;
1294 l_expected_resolu_date DATE;
1295
1296 l_object VARCHAR2(30):= 'NR_MEL_CDL';
1297 l_approval_type VARCHAR2(100):='CONCEPT';
1298 l_active VARCHAR2(50):= 'N';
1299 l_process_name VARCHAR2(50);
1300 l_item_type VARCHAR2(50);
1301
1302 BEGIN
1303 -- Standard start of API savepoint
1304 SAVEPOINT Initiate_Mel_Cdl_Approval_SP;
1305
1306 -- Initialize return status to success before any code logic/validation
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 -- Standard call to check for call compatibility
1310 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1311 THEN
1312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1313 END IF;
1314
1315 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1316 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1317 THEN
1318 FND_MSG_PUB.INITIALIZE;
1319 END IF;
1320
1321 -- Log API entry point
1322 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1323 THEN
1324 fnd_log.string
1325 (
1326 G_DEBUG_PROC,
1327 l_debug_module ||'.begin', 'At the start of PLSQL procedure'
1328 );
1329 END IF;
1330 -- API body starts here
1331
1332 -- MOAC initialization.
1333 MO_GLOBAL.init('AHL');
1334
1335 -- Validate UE id + ovn exist and corresponding NR is planned in PRD
1336 OPEN unit_effect_csr (p_ue_id);
1337 FETCH unit_effect_csr INTO l_ue_rec;
1338 IF (unit_effect_csr%NOTFOUND) THEN
1339 CLOSE unit_effect_csr;
1340 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_UE_INVALID');
1341 FND_MESSAGE.Set_Token('UE_ID',p_ue_id);
1342 FND_MSG_PUB.ADD;
1343 RAISE FND_API.G_EXC_ERROR;
1344 ELSIF (l_ue_rec.object_version_number <> p_ue_object_version) THEN
1345 CLOSE unit_effect_csr;
1346 FND_MESSAGE.Set_Name('AHL','AHL_COM_CHANGED');
1347 FND_MSG_PUB.ADD;
1348 RAISE FND_API.G_EXC_ERROR;
1349 END IF;
1350 CLOSE unit_effect_csr;
1351
1352 -- Check Unit locked.
1353 IF AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => null,
1354 p_ue_id => p_ue_id,
1355 p_visit_id => null,
1356 p_item_instance_id => null) = FND_API.g_true THEN
1357 -- Unit is locked, therefore cannot proceed for approval.
1358 -- and cannot login to the workorder
1359 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UNITLCKED');
1360 FND_MESSAGE.set_token('UE_ID', p_ue_id);
1361 FND_MSG_PUB.ADD;
1362 RAISE FND_API.G_EXC_ERROR;
1363 END IF;
1364
1365 -- check UE status.
1366 OPEN visit_det_csr(p_ue_id);
1367 FETCH visit_det_csr INTO l_junk;
1368 IF (visit_det_csr%NOTFOUND) THEN
1369 CLOSE visit_det_csr;
1370 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_NOT_PRD');
1371 FND_MESSAGE.Set_Token('UE_ID',p_ue_id);
1372 FND_MSG_PUB.ADD;
1373 RAISE FND_API.G_EXC_ERROR;
1374 END IF;
1375 CLOSE visit_det_csr;
1376
1377 -- Validate UE is already not pending for MEL/CDL approval / already not approved
1378 OPEN ue_deferral_csr(p_ue_id);
1379 FETCH ue_deferral_csr INTO l_deferral_rec;
1380 IF (ue_deferral_csr%NOTFOUND) THEN
1381 CLOSE ue_deferral_csr;
1382 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_NOT_DEFER');
1383 FND_MESSAGE.Set_Token('UE_ID',p_ue_id);
1384 FND_MSG_PUB.ADD;
1385 RAISE FND_API.G_EXC_ERROR;
1386 END IF;
1387 CLOSE ue_deferral_csr;
1388
1389 IF (l_deferral_rec.approval_status_code IN ('DEFERRED','DEFERRAL_PENDING')) THEN
1390 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_APPR_STATUS_INVALID');
1391 FND_MSG_PUB.ADD;
1392 RAISE FND_API.G_EXC_ERROR;
1393 END IF;
1394
1395 IF (l_deferral_rec.ata_sequence_id IS NULL) THEN
1396 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_NO_SYS_SEQ_ASSOC');
1397 FND_MSG_PUB.ADD;
1398 RAISE FND_API.G_EXC_ERROR;
1399 END IF;
1400
1401 -- validate ue is not being deferred.
1402 OPEN ue_defer_csr(p_ue_id);
1403 FETCH ue_defer_csr INTO l_junk;
1404 IF (ue_defer_csr%FOUND) THEN
1405 CLOSE ue_defer_csr;
1406 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_IN_DEFER');
1407 FND_MSG_PUB.ADD;
1408 RAISE FND_API.G_EXC_ERROR;
1409 END IF;
1410 CLOSE ue_defer_csr;
1411
1412 -- validate there are no child UEs being deferred.
1413 OPEN ue_defer_child_csr(p_ue_id);
1414 FETCH ue_defer_child_csr INTO l_junk;
1415 IF (ue_defer_child_csr%FOUND) THEN
1416 CLOSE ue_defer_child_csr;
1417 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_CHILD_UE_DEFER');
1418 FND_MSG_PUB.ADD;
1419 RAISE FND_API.G_EXC_ERROR;
1420 END IF;
1421 CLOSE ue_defer_child_csr;
1422
1423 -- Perform pre-MEL/CDL approval validations
1424 -- validate mel/cdl header id.
1425 OPEN mel_cdl_header_csr(l_deferral_rec.ata_sequence_id);
1426 FETCH mel_cdl_header_csr INTO l_installed_number, l_dispatch_number, l_repair_time;
1427 IF (mel_cdl_header_csr%NOTFOUND) THEN
1428 CLOSE mel_cdl_header_csr;
1429 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_MEL_CDL_INVALID');
1430 FND_MSG_PUB.ADD;
1431 RAISE FND_API.G_EXC_ERROR;
1432 END IF;
1433
1434 -- validate repair category.
1435 OPEN get_exp_resolution_csr(l_ue_rec.cs_incident_id);
1436 FETCH get_exp_resolution_csr INTO l_expected_resolu_date;
1437 IF (get_exp_resolution_csr%NOTFOUND) THEN
1438 CLOSE get_exp_resolution_csr;
1439 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_CS_INC_MISSING');
1440 FND_MSG_PUB.ADD;
1441 RAISE FND_API.G_EXC_ERROR;
1442 ELSIF (nvl(l_repair_time, 0) = 0) AND (l_expected_resolu_date IS NULL) THEN
1443 CLOSE get_exp_resolution_csr;
1444 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_RESOLUTION_MAND');
1445 FND_MSG_PUB.ADD;
1446 RAISE FND_API.G_EXC_ERROR;
1447 END IF;
1448 CLOSE get_exp_resolution_csr;
1449
1450 -- Bug #5230869 - validate inc_occ_date + rep_time >= inc_date
1451 IF (NVL(l_repair_time, 0) <> 0 AND trunc(l_deferral_rec.deferral_effective_on) + trunc(l_repair_time/24) < trunc(sysdate))
1452 THEN
1453 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_NO_ACCOM');
1454 -- Repair Time of the associated MEL/CDL Instructions cannot accomodate resolution of the Non-routine before Log Date
1455 FND_MSG_PUB.ADD;
1456 RAISE FND_API.G_EXC_ERROR;
1457 END IF;
1458
1459 -- Validate log_series, number + unit_config are not null for NR that is being submitted for MEL/CDL deferral
1460 IF (l_ue_rec.unit_config_header_id is null or l_ue_rec.log_series_code is null or l_ue_rec.log_series_number is null)
1461 THEN
1462 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_APPR_MAND_INV');
1463 -- Unit, Log Series and Number are mandatory for submitting for MEL/CDL approval
1464 FND_MSG_PUB.ADD;
1465 RAISE FND_API.G_EXC_ERROR;
1466 END IF;
1467
1468 --amsriniv. Bug 6659422. Adding condition below only when dispatch_number and installed_number
1469 --are both NOT NULL and dispatch_number is > 0.
1470 IF (l_installed_number IS NOT NULL and l_dispatch_number IS NOT NULL) AND
1471 (l_installed_number <> l_dispatch_number) AND -- ignore check when equal.
1472 (l_dispatch_number > 0) -- ignore check if none are required.
1473 THEN
1474 -- validate openNRs with installed and dispatch rules.
1475 OPEN get_open_NRs_csr(l_deferral_rec.ata_sequence_id,
1476 l_ue_rec.unit_config_header_id);
1477 --l_ue_rec.cs_incident_id);
1478 FETCH get_open_NRs_csr INTO l_NR_count;
1479 CLOSE get_open_NRs_csr;
1480
1481 IF ((l_installed_number - l_NR_count) <= l_dispatch_number) THEN
1482 FND_MESSAGE.Set_Name('AHL','AHL_UMP_OPEN_NR_EXCEEDS');
1483 FND_MSG_PUB.ADD;
1484 RAISE FND_API.G_EXC_ERROR;
1485 END IF;
1486 END IF;
1487
1488 -- validate workorder dependency.
1489 AHL_PRD_WORKORDER_PVT.validate_dependencies
1490 (
1491 p_api_version => 1.0,
1492 p_init_msg_list => FND_API.G_TRUE,
1493 p_commit => FND_API.G_FALSE,
1494 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1495 p_default => FND_API.G_FALSE,
1496 p_module_type => NULL,
1497 x_return_status => l_return_status,
1498 x_msg_count => l_msg_count,
1499 x_msg_data => l_msg_data,
1500 p_visit_id => NULL,
1501 p_unit_effectivity_id => p_ue_id,
1502 p_workorder_id => NULL
1503 );
1504
1505 -- if workorders under UE has external dependencies, dont submit for approval, raise error.
1506 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1507 IF (fnd_log.level_error >= G_DEBUG_LEVEL)THEN
1508 fnd_log.string
1509 (
1510 fnd_log.level_error,
1511 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
1512 'Can not go ahead with submission of approval because Workorder dependencies exists'
1513 );
1514 END IF;
1515 RAISE FND_API.G_EXC_ERROR;
1516 END IF;
1517
1518 -- Kick off approval process if active, else complete approval process (post-MEL/CDL approval updations)
1519 ahl_utility_pvt.get_wf_process_name(
1520 p_object =>l_object,
1521 x_active =>l_active,
1522 x_process_name =>l_process_name ,
1523 x_item_type =>l_item_type,
1524 x_return_status=>l_return_status,
1525 x_msg_count =>l_msg_count,
1526 x_msg_data =>l_msg_data);
1527 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1528 fnd_log.string
1529 (
1530 G_DEBUG_STMT,
1531 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
1532 'Workflow active flag : ' || l_active
1533 );
1534 fnd_log.string
1535 (
1536 G_DEBUG_STMT,
1537 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
1538 'l_process_name : ' || l_process_name
1539 );
1540 fnd_log.string
1541 (
1542 G_DEBUG_STMT,
1543 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
1544 'l_item_type : ' || l_item_type
1545 );
1546
1547 END IF;
1548
1549 IF((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
1550 ( l_active <> G_YES_FLAG))THEN
1551 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1552 fnd_log.string
1553 (
1554 G_DEBUG_STMT,
1555 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
1556 'Workflow is not active so going for automatic approval'
1557 );
1558 END IF;
1559 l_active := G_NO_FLAG;
1560 END IF;
1561
1562 -- make a call to update job status to pending deferral approval and update approval status
1563 AHL_PRD_DF_PVT.process_approval_initiated(
1564 p_unit_deferral_id => l_deferral_rec.unit_deferral_id,
1565 p_object_version_number => l_deferral_rec.object_version_number,
1566 p_new_status => 'DEFERRAL_PENDING',
1567 x_return_status => l_return_status);
1568
1569 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1570 IF (fnd_log.level_error >= G_DEBUG_LEVEL)THEN
1571 fnd_log.string
1572 (
1573 fnd_log.level_error,
1574 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.submit_for_approval',
1575 'Can not go ahead with approval because AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval threw error'
1576 );
1577 END IF;
1578 RAISE FND_API.G_EXC_ERROR;
1579 END IF;
1580
1581 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1582 fnd_log.string
1583 (
1584 G_DEBUG_STMT,
1585 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
1586 'Workflow active flag : ' || l_active
1587 );
1588 END IF;
1589
1590 l_new_status_code := 'DEFERRED';
1591
1592 IF(l_active <> G_NO_FLAG)THEN
1593 Ahl_generic_aprv_pvt.Start_Wf_Process(
1594 P_OBJECT => l_object,
1595 P_APPROVAL_TYPE => 'CONCEPT',
1596 P_ACTIVITY_ID => l_deferral_rec.unit_deferral_id,
1597 P_OBJECT_VERSION_NUMBER => l_deferral_rec.object_version_number,
1598 P_ORIG_STATUS_CODE => l_deferral_rec.approval_status_code,
1599 P_NEW_STATUS_CODE => l_new_status_code ,
1600 P_REJECT_STATUS_CODE => 'DEFERRAL_REJECTED',
1601 P_REQUESTER_USERID => fnd_global.user_id,
1602 P_NOTES_FROM_REQUESTER => '',
1603 P_WORKFLOWPROCESS => 'AHL_GEN_APPROVAL',
1604 P_ITEM_TYPE => 'AHLGAPP');
1605 ELSE
1606
1607 -- process for M and O procedures.
1608 Process_MO_procedures (p_ue_id,
1609 l_deferral_rec.unit_deferral_id,
1610 l_deferral_rec.object_version_number,
1611 l_deferral_rec.ata_sequence_id,
1612 l_ue_rec.cs_incident_id,
1613 l_ue_rec.csi_item_instance_id);
1614
1615
1616 END IF;
1617
1618 /*
1619 -- validate interrelationships and set return status to warning.
1620 FOR ata_seq_rec IN get_ata_relationship_csr(l_deferral_rec.ata_sequence_id)
1621 LOOP
1622 OPEN get_open_NRs_csr(ata_seq_rec.ata_sequence_id,
1623 l_ue_rec.unit_config_header_id);
1624 FETCH get_open_NRs_csr INTO l_count;
1625 CLOSE get_open_NRs_csr;
1626 IF (l_count > 0) THEN
1627 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_INTERRELATION_ERROR');
1628 FND_MESSAGE.Set_token('SEQ1',l_deferral_rec.ata_sequence_id);
1629 FND_MESSAGE.Set_token('SEQ2',ata_seq_rec.ata_sequence_id);
1630 FND_MSG_PUB.ADD;
1631 END IF;
1632
1633 END LOOP;
1634
1635 -- Check Error Message stack.
1636 x_msg_count := FND_MSG_PUB.count_msg;
1637 IF (x_msg_count > 0)
1638 THEN
1639 x_return_status := 'W'; -- warning.
1640 END IF;
1641 */
1642
1643 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1644 fnd_log.string
1645 (
1646 G_DEBUG_PROC,
1647 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval.end',
1648 'At the end of PLSQL procedure'
1649 );
1650 END IF;
1651
1652 -- API body ends here
1653 -- Log API exit point
1654 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1655 THEN
1656 fnd_log.string
1657 (
1658 G_DEBUG_PROC,
1659 l_debug_module||'.end',
1660 'At the end of PLSQL procedure'
1661 );
1662 END IF;
1663
1664 -- Commit if p_commit = FND_API.G_TRUE
1665 IF FND_API.TO_BOOLEAN(p_commit)
1666 THEN
1667 COMMIT WORK;
1668 END IF;
1669
1670 -- Standard call to get message count and if count is 1, get message info
1671 FND_MSG_PUB.count_and_get
1672 (
1673 p_count => x_msg_count,
1674 p_data => x_msg_data,
1675 p_encoded => FND_API.G_FALSE
1676 );
1677
1678 EXCEPTION
1679 WHEN FND_API.G_EXC_ERROR THEN
1680 x_return_status := FND_API.G_RET_STS_ERROR;
1681 Rollback to Initiate_Mel_Cdl_Approval_SP;
1682 FND_MSG_PUB.count_and_get
1683 (
1684 p_count => x_msg_count,
1685 p_data => x_msg_data,
1686 p_encoded => FND_API.G_FALSE
1687 );
1688
1689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691 Rollback to Initiate_Mel_Cdl_Approval_SP;
1692 FND_MSG_PUB.count_and_get
1693 (
1694 p_count => x_msg_count,
1695 p_data => x_msg_data,
1696 p_encoded => FND_API.G_FALSE
1697 );
1698
1699 WHEN OTHERS THEN
1700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701 Rollback to Initiate_Mel_Cdl_Approval_SP;
1702 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1703 THEN
1704 FND_MSG_PUB.add_exc_msg
1705 (
1706 p_pkg_name => G_PKG_NAME,
1707 p_procedure_name => 'Initiate_Mel_Cdl_Approval',
1708 p_error_text => SUBSTR(SQLERRM,1,240)
1709 );
1710 END IF;
1711 FND_MSG_PUB.count_and_get
1712 (
1713 p_count => x_msg_count,
1714 p_data => x_msg_data,
1715 p_encoded => FND_API.G_FALSE
1716 );
1717 END Initiate_Mel_Cdl_Approval;
1718
1719 ----------------------------------------------
1720 -- Spec Procedure Check_Open_NRs --
1721 ----------------------------------------------
1722 PROCEDURE Check_Open_NRs
1723 (
1724 -- Standard OUT params
1725 x_return_status OUT NOCOPY VARCHAR2,
1726 -- Procedure IN, OUT, IN/OUT params
1727 p_mel_cdl_header_id IN NUMBER DEFAULT NULL,
1728 p_pc_node_id IN NUMBER DEFAULT NULL
1729 )
1730 IS
1731 -- Declare local variables
1732 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.Check_Open_NRs';
1733 l_junk NUMBER;
1734 l_return_status VARCHAR2(1);
1735
1736 -- Define cursors
1737 -- query to check existence of open NRs for a mel_cdl_header_id.
1738 -- modified for perf fix bug# 7442102
1739 CURSOR nr_mel_cdl_csr(p_mel_cdl_header_id IN NUMBER) IS
1740 SELECT UE.unit_effectivity_id
1741 FROM
1742 AHL_UNIT_EFFECTIVITIES_APP_V UE, CS_INCIDENTS_ALL_B CS,
1743 CS_INCIDENT_STATUSES_B STATUS, AHL_UNIT_DEFERRALS_B UDF--,
1744 --AHL_MEL_CDL_ATA_SEQUENCES SEQ
1745 WHERE
1746 --SEQ.MEL_CDL_ATA_SEQUENCE_ID = UDF.ATA_SEQUENCE_ID
1747 --AND
1748 UDF.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
1749 AND UDF.ATA_SEQUENCE_ID = p_mel_cdl_header_id
1750 AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
1751 AND CS.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
1752 AND NVL(STATUS.CLOSE_FLAG, 'N') = 'N'
1753 --AND SEQ.MEL_CDL_HEADER_ID = p_mel_cdl_header_id
1754 AND (UE.STATUS_CODE IS NULL OR UE.STATUS_CODE = 'INIT_DUE')
1755 AND UDF.APPROVAL_STATUS_CODE IN ('DRAFT','DEFERRAL_PENDING','DEFERRAL_REJECTED')
1756 AND ROWNUM = 1;
1757
1758 -- query to check existence of open NRs for a pc_node_id.
1759 CURSOR nr_pc_node_csr(p_pc_node_id IN NUMBER) IS
1760 SELECT UE.unit_effectivity_id
1761 FROM
1762 AHL_UNIT_EFFECTIVITIES_APP_V UE, CS_INCIDENTS_ALL_B CS,
1763 CS_INCIDENT_STATUSES_B STATUS, AHL_UNIT_DEFERRALS_B UDF,
1764 AHL_MEL_CDL_ATA_SEQUENCES SEQ, AHL_MEL_CDL_HEADERS HDR
1765 WHERE
1766 SEQ.MEL_CDL_ATA_SEQUENCE_ID = UDF.ATA_SEQUENCE_ID
1767 AND UDF.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
1768 AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
1769 AND CS.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
1770 AND NVL(STATUS.CLOSE_FLAG, 'N') = 'N'
1771 AND (UE.STATUS_CODE IS NULL OR UE.STATUS_CODE = 'INIT_DUE')
1772 AND UDF.APPROVAL_STATUS_CODE IN ('DRAFT','DEFERRAL_PENDING','DEFERRAL_REJECTED')
1773 AND SEQ.MEL_CDL_HEADER_ID = HDR.MEL_CDL_HEADER_ID
1774 AND HDR.PC_NODE_ID IN
1775 -- priyan : bug #5302804
1776 (
1777 -- traverse up the branch, for MEL/CDL, from the PC node being deleted
1778 SELECT PC_NODE_ID
1779 FROM AHL_PC_NODES_B
1780 CONNECT BY PRIOR PARENT_NODE_ID= PC_NODE_ID
1781 START WITH PC_NODE_ID = p_pc_node_id
1782 UNION
1783 -- traverse down the tree, for MEL/CDL, from the PC node being deleted
1784 SELECT PC_NODE_ID
1785 FROM AHL_PC_NODES_B
1786 CONNECT BY PRIOR PC_NODE_ID = PARENT_NODE_ID
1787 START WITH PC_NODE_ID = p_pc_node_id
1788 )
1789 AND ROWNUM = 1;
1790 BEGIN
1791 -- Initialize return status to success before any code logic/validation
1792 x_return_status := FND_API.G_RET_STS_SUCCESS;
1793
1794 -- Log API entry point
1795 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1796 THEN
1797 fnd_log.string
1798 (
1799 G_DEBUG_PROC,
1800 l_debug_module||'.begin',
1801 'At the start of PLSQL procedure'
1802 );
1803 END IF;
1804
1805 -- API body starts here
1806 -- Check for multiple inputs.
1807 IF (p_mel_cdl_header_id IS NOT NULL AND p_pc_node_id IS NOT NULL)
1808 THEN
1809 -- return error.
1810 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_NR_MULTI_PARAM');
1811 FND_MSG_PUB.ADD;
1812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1813 END IF;
1814
1815 -- if p_mel_cdl_header_id is not null
1816 IF (p_mel_cdl_header_id IS NOT NULL)
1817 THEN
1818 OPEN nr_mel_cdl_csr(p_mel_cdl_header_id);
1819 FETCH nr_mel_cdl_csr INTO l_junk;
1820 IF (nr_mel_cdl_csr%FOUND)
1821 THEN
1822 x_return_status := FND_API.G_RET_STS_ERROR;
1823 END IF;
1824 CLOSE nr_mel_cdl_csr;
1825 END IF; -- p_mel_cdl_header_id IS NOT NULL
1826
1827 -- if p_pc_node_id is not null
1828 IF (p_pc_node_id IS NOT NULL)
1829 THEN
1830 OPEN nr_pc_node_csr(p_pc_node_id);
1831 FETCH nr_pc_node_csr INTO l_junk;
1832 IF (nr_pc_node_csr%FOUND)
1833 THEN
1834 x_return_status := FND_API.G_RET_STS_ERROR;
1835 END IF;
1836 CLOSE nr_pc_node_csr;
1837 END IF; -- p_pc_node_id IS NOT NULL
1838
1839 -- API body ends here
1840 -- Log API exit point
1841 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1842 THEN
1843 fnd_log.string
1844 (
1845 G_DEBUG_PROC,
1846 l_debug_module||'.end',
1847 'At the end of PLSQL procedure'
1848 );
1849 END IF;
1850
1851 EXCEPTION
1852 WHEN FND_API.G_EXC_ERROR THEN
1853 x_return_status := FND_API.G_RET_STS_ERROR;
1854
1855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1857
1858 WHEN OTHERS THEN
1859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860 END Check_Open_NRs;
1861
1862 -----------------------------------------
1863 -- Spec Function Get_Mel_Cdl_Header_Id --
1864 -----------------------------------------
1865 FUNCTION Get_Mel_Cdl_Header_Id
1866 (
1867 p_unit_effectivity_id NUMBER,
1868 p_csi_instance_id NUMBER,
1869 p_mel_cdl_type_code VARCHAR2
1870 )
1871 RETURN NUMBER
1872 IS
1873 CURSOR get_ue_details
1874 IS
1875 SELECT ahl_util_uc_pkg.get_uc_header_id(csi_item_instance_id),
1876 mel_cdl_type_code
1877 FROM ahl_unit_effectivities_b
1878 WHERE unit_effectivity_id = p_unit_effectivity_id;
1879
1880 l_unit_config_id number;
1881 l_mel_cdl_type_code varchar2(30);
1882
1883 CURSOR get_mel_cdl
1884 (
1885 l_unit_config_id number,
1886 l_mel_cdl_type_code varchar2
1887 )
1888 IS
1889 select mel.mel_cdl_header_id, mel.version_number, pcn.pc_node_id
1890 from ahl_pc_nodes_b pcn,
1891 ahl_mel_cdl_headers mel
1892 --where pcn.pc_node_id = mel.pc_node_id (+) and -- perf fix for bug# 7442102
1893 where pcn.pc_node_id = mel.pc_node_id and
1894 mel.mel_cdl_type_code = l_mel_cdl_type_code and
1895 mel.status_code = 'COMPLETE' and
1896 trunc(sysdate) between trunc(revision_date) and trunc(nvl(expired_date, sysdate + 1))
1897 connect by pcn.pc_node_id = prior pcn.parent_node_id
1898 start with pcn.pc_node_id in
1899 (
1900 select node.pc_node_id
1901 from ahl_pc_associations assos,
1902 ahl_pc_nodes_b node,
1903 ahl_pc_headers_b hdr
1904 where assos.unit_item_id = l_unit_config_id and
1905 assos.pc_node_id = node.pc_node_id and
1906 node.pc_header_id = hdr.pc_header_id and
1907 hdr.status = 'COMPLETE' and
1908 hdr.primary_flag = 'Y' and
1909 hdr.association_type_flag = 'U'
1910 )
1911 --order by pcn.pc_node_id desc, mel.version_number desc;
1912 order by pcn.pc_node_id desc, mel.mel_cdl_type_code, mel.version_number desc;
1913
1914 l_mel_cdl_header_id NUMBER;
1915 l_pc_node_id NUMBER;
1916 l_mel_version_number NUMBER;
1917
1918 BEGIN
1919
1920 IF (p_unit_effectivity_id IS NOT NULL AND p_unit_effectivity_id <> FND_API.G_MISS_NUM)
1921 THEN
1922 OPEN get_ue_details;
1923 FETCH get_ue_details INTO l_unit_config_id, l_mel_cdl_type_code;
1924 CLOSE get_ue_details;
1925 ELSIF (
1926 p_csi_instance_id IS NOT NULL AND p_csi_instance_id <> FND_API.G_MISS_NUM AND
1927 p_mel_cdl_type_code IS NOT NULL AND p_mel_cdl_type_code <> FND_API.G_MISS_CHAR
1928 )
1929 THEN
1930 --SELECT ahl_util_uc_pkg.get_uc_header_id(p_csi_instance_id) INTO l_unit_config_id FROM DUAL;
1931 l_unit_config_id := ahl_util_uc_pkg.get_uc_header_id(p_csi_instance_id);
1932 l_mel_cdl_type_code := p_mel_cdl_type_code;
1933 END IF;
1934
1935 IF (l_unit_config_id IS NOT NULL AND l_mel_cdl_type_code IS NOT NULL AND ahl_util_uc_pkg.get_uc_status_code(l_unit_config_id) IN ('COMPLETE', 'INCOMPLETE'))
1936 THEN
1937 OPEN get_mel_cdl(l_unit_config_id, l_mel_cdl_type_code);
1938 FETCH get_mel_cdl INTO l_mel_cdl_header_id, l_mel_version_number, l_pc_node_id;
1939 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
1940 THEN
1941 fnd_log.string
1942 (
1943 G_DEBUG_STMT,
1944 'ahl.plsql.'||G_PKG_NAME||'.Get_Mel_Cdl_Header_Id.end',
1945 'Retrieved mel_cdl_header_id ['||l_mel_cdl_header_id||'] with version ['||l_mel_version_number||'] for pc_node_id ['||l_pc_node_id||'] given uc_id ['||l_unit_config_id||'] and type ['||l_mel_cdl_type_code||']'
1946 );
1947 END IF;
1948 CLOSE get_mel_cdl;
1949 END IF;
1950
1951 RETURN l_mel_cdl_header_id;
1952
1953 END Get_Mel_Cdl_Header_Id;
1954
1955 --------------------------------------
1956 -- Spec Function Get_Mel_Cdl_Status --
1957 --------------------------------------
1958 FUNCTION Get_Mel_Cdl_Status
1959 (
1960 p_unit_effectivity_id NUMBER,
1961 p_get_code VARCHAR2 := FND_API.G_FALSE
1962 )
1963 RETURN VARCHAR2
1964 IS
1965 l_mel_cdl_status VARCHAR2(30) := 'OPEN';
1966 l_mel_cdl_status_mean VARCHAR2(80);
1967
1968 CURSOR get_ue_details
1969 IS
1970 SELECT unit_deferral_type, approval_status_code
1971 FROM ahl_unit_deferrals_b
1972 WHERE unit_effectivity_id = p_unit_effectivity_id AND
1973 unit_deferral_type IN ('MEL', 'CDL');
1974
1975 l_deferral_type VARCHAR2(30);
1976 l_approval_code VARCHAR2(30);
1977
1978 l_ret_val BOOLEAN;
1979
1980 BEGIN
1981
1982 IF (p_unit_effectivity_id IS NOT NULL AND p_unit_effectivity_id <> FND_API.G_MISS_NUM)
1983 THEN
1984 OPEN get_ue_details;
1985 FETCH get_ue_details INTO l_deferral_type, l_approval_code;
1986 IF (get_ue_details%FOUND)
1987 THEN
1988 l_mel_cdl_status := l_deferral_type||':'||l_approval_code;
1989
1990 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_NR_MELCDL_STATUS_CODE', l_mel_cdl_status))
1991 THEN
1992 l_mel_cdl_status := 'OPEN';
1993 END IF;
1994 END IF;
1995 END IF;
1996
1997 IF (p_get_code = FND_API.G_TRUE)
1998 THEN
1999 RETURN l_mel_cdl_status;
2000 ELSE
2001 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning
2002 (
2003 p_lookup_type => 'AHL_NR_MELCDL_STATUS_CODE',
2004 p_lookup_code => l_mel_cdl_status,
2005 x_lookup_meaning => l_mel_cdl_status_mean,
2006 x_return_val => l_ret_val
2007 );
2008 RETURN l_mel_cdl_status_mean;
2009 END IF;
2010
2011 END Get_Mel_Cdl_Status;
2012
2013 -------------------------------------------
2014 -- Non-spec Function Is_MEL_CDL_Approved --
2015 -------------------------------------------
2016 FUNCTION Is_MEL_CDL_Approved
2017 (
2018 p_unit_effectivity_id NUMBER
2019 )
2020 RETURN BOOLEAN
2021 IS
2022
2023 l_is_approved BOOLEAN := false;
2024
2025 CURSOR get_mel_cdl_status
2026 IS
2027 SELECT approval_status_code, unit_deferral_type
2028 FROM ahl_unit_deferrals_b
2029 WHERE unit_effectivity_id = p_unit_effectivity_id;
2030
2031 l_deferral_type VARCHAR2(30);
2032 l_approval_code VARCHAR2(30);
2033
2034 BEGIN
2035
2036 OPEN get_mel_cdl_status;
2037 FETCH get_mel_cdl_status INTO l_approval_code, l_deferral_type;
2038 IF (
2039 get_mel_cdl_status%FOUND AND
2040 l_approval_code IN ('DEFERRAL_PENDING', 'DEFERRED') AND
2041 l_deferral_type IN ('MEL', 'CDL')
2042 )
2043 THEN
2044 l_is_approved := true;
2045 END IF;
2046 CLOSE get_mel_cdl_status;
2047
2048 RETURN l_is_approved;
2049
2050 END Is_MEL_CDL_Approved;
2051
2052 --------------------------------------------
2053 -- Non-spec Procedure Validate_UE_Details --
2054 --------------------------------------------
2055 PROCEDURE Validate_UE_Details
2056 (
2057 p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type,
2058 p_unit_effectivity_id IN NUMBER,
2059 p_dml_operation IN VARCHAR2
2060 )
2061 IS
2062 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.Validate_UE_Details';
2063
2064 l_return_status VARCHAR2(1);
2065 l_msg_count NUMBER;
2066 l_msg_data VARCHAR2(2000);
2067
2068 l_ret_val BOOLEAN;
2069 l_uc_header_id NUMBER;
2070 l_uc_status_code VARCHAR2(30);
2071
2072 CURSOR check_pos_path_exists
2073 IS
2074 SELECT 'x'
2075 FROM ahl_applicable_instances appl,
2076 csi_ii_relationships cii,
2077 ahl_mc_relationships mch,
2078 ahl_unit_config_headers uch
2079 WHERE appl.position_id = p_x_nonroutine_rec.position_path_id and
2080 (appl.csi_item_instance_id = cii.subject_id or appl.csi_item_instance_id = cii.object_id) and
2081 to_number(cii.position_reference) = mch.relationship_id and
2082 mch.mc_header_id = uch.master_config_id and
2083 uch.unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id
2084 UNION ALL
2085 SELECT 'x'
2086 FROM ahl_applicable_instances appl,
2087 ahl_unit_config_headers uch
2088 WHERE appl.position_id = p_x_nonroutine_rec.position_path_id and
2089 appl.csi_item_instance_id = uch.csi_item_instance_id and
2090 uch.unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id;
2091
2092 CURSOR get_ue_details
2093 (
2094 p_unit_effectivity_id number
2095 )
2096 IS
2097 SELECT log_series_code, log_series_number, position_path_id, mel_cdl_type_code, ata_code
2098 FROM ahl_unit_effectivities_b
2099 WHERE unit_effectivity_id = p_unit_effectivity_id;
2100
2101 l_ue_detail_rec get_ue_details%rowtype;
2102
2103 /* Behavior of Log Series and Number in "Unit / Component Details" sub-header
2104 * log_series and log_number are non-mandatory (except for MEL/CDL qualification)
2105 * log_series and log_number exist in combination
2106 * log_series and log_number are always user-editable, but the combination is unique
2107 */
2108 CURSOR check_lognum_unique
2109 IS
2110 SELECT 'x'
2111 FROM ahl_unit_effectivities_b
2112 WHERE log_series_number = p_x_nonroutine_rec.log_series_number and
2113 log_series_code = p_x_nonroutine_rec.log_series_code and
2114 unit_effectivity_id <> nvl(p_unit_effectivity_id, unit_effectivity_id) and
2115 nvl(status_code, 'X') <> 'DEFERRED';
2116
2117 CURSOR get_org_id_from_name
2118 (
2119 p_org_code varchar2
2120 )
2121 IS
2122 -- Bug #5208033 Replacing view "org_organization_definitions" with "inv_organization_name_v"
2123 select ORG.organization_id
2124 from inv_organization_name_v ORG,
2125 mtl_parameters MP
2126 where MP.organization_id = ORG.organization_id AND
2127 MP.EAM_enabled_flag = 'Y' AND
2128 ORG.organization_code = p_org_code;
2129
2130 CURSOR check_org_id
2131 (
2132 p_org_id varchar2
2133 )
2134 IS
2135 -- Bug #5208033 Replacing view "org_organization_definitions" with "inv_organization_name_v"
2136 select 'x'
2137 from inv_organization_name_v ORG,
2138 mtl_parameters MP
2139 where MP.organization_id = ORG.organization_id AND
2140 MP.EAM_enabled_flag = 'Y' AND
2141 ORG.organization_id = p_org_id;
2142
2143 CURSOR get_dept_id_from_name
2144 (
2145 p_dept_code varchar2,
2146 p_org_id number
2147 )
2148 IS
2149 select department_id
2150 from bom_departments
2151 where organization_id = p_org_id and
2152 department_code = p_dept_code;
2153
2154 CURSOR check_dept_id
2155 (
2156 p_dept_id varchar2,
2157 p_org_id number
2158 )
2159 IS
2160 select 'x'
2161 from bom_departments
2162 where organization_id = p_org_id and
2163 department_id = p_dept_id;
2164
2165 CURSOR check_ue_mel_cdl_approved
2166 IS
2167 SELECT 'x'
2168 FROM ahl_unit_deferrals_b
2169 WHERE unit_effectivity_id = p_unit_effectivity_id AND
2170 unit_deferral_type IN ('MEL', 'CDL') AND
2171 approval_status_code IN ('DEFERRAL_PENDING', 'DEFERRED');
2172
2173 --priyan
2174 --Fix for Bug# 5350385
2175 CURSOR is_ue_mel_cdl_qual
2176 IS
2177 SELECT 'x'
2178 FROM ahl_unit_deferrals_b
2179 WHERE unit_effectivity_id = p_unit_effectivity_id AND
2180 unit_deferral_type IN ('MEL', 'CDL');
2181
2182 BEGIN
2183 -- Retrieve details of the non-routine unit effectivity, if record is being updated
2184 -- During create the information would not already have been committed, so no point opening this cursor
2185 IF (p_dml_operation = 'U')
2186 THEN
2187 OPEN get_ue_details(p_unit_effectivity_id);
2188 FETCH get_ue_details INTO l_ue_detail_rec;
2189 CLOSE get_ue_details;
2190 END IF;
2191
2192 /*
2193 -- Validate unit_config_header_id NOT NULL
2194 IF (p_x_nonroutine_rec.unit_config_header_id is null or p_x_nonroutine_rec.unit_config_header_id = FND_API.G_MISS_NUM)
2195 THEN
2196 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_UNIT_NULL');
2197 FND_MSG_PUB.ADD;
2198 RAISE FND_API.G_EXC_ERROR;
2199 */
2200
2201 /* Behavior of Unit, Item, Serial and Instance LOVs in "Unit / Component Details" sub-header
2202 * if unit_config_header_id is not null, then it needs to be an active unit and instance should exist on the unit
2203 * if unit_config_header_id is null, try to derive an active unit from the instance
2204 * if unit_config_header_id is still not null, consider this a case of logging NR for IB component only
2205 * if either UI unit/derived unit is in quarantine/deactive_quarantine, throw error
2206 */
2207 IF (p_x_nonroutine_rec.unit_config_header_id is null or p_x_nonroutine_rec.unit_config_header_id = FND_API.G_MISS_NUM)
2208 THEN
2209 l_uc_header_id := ahl_util_uc_pkg.get_uc_header_id(p_x_nonroutine_rec.instance_id);
2210 l_uc_status_code := ahl_util_uc_pkg.get_uc_status_code(l_uc_header_id);
2211 IF (l_uc_status_code IN ('COMPLETE', 'INCOMPLETE'))
2212 THEN
2213 p_x_nonroutine_rec.unit_config_header_id := l_uc_header_id;
2214 ELSIF (l_uc_status_code IN ('QUARANTINE', 'DEACTIVATE_QUARANTINE'))
2215 THEN
2216 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_UNIT_QUAR_INV');
2217 FND_MSG_PUB.ADD;
2218 RAISE FND_API.G_EXC_ERROR;
2219 END IF;
2220 ELSE
2221 l_uc_status_code := ahl_util_uc_pkg.get_uc_status_code(p_x_nonroutine_rec.unit_config_header_id);
2222 -- Check for not active and/or quarantined unit
2223 IF (l_uc_status_code IN ('QUARANTINE', 'DEACTIVATE_QUARANTINE'))
2224 THEN
2225 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_UNIT_QUAR_INV');
2226 FND_MSG_PUB.ADD;
2227 RAISE FND_API.G_EXC_ERROR;
2228 ELSIF (l_uc_status_code IN ('COMPLETE', 'INCOMPLETE'))
2229 THEN
2230 -- Validate instance exists on the unit specified... Assume instance is validated before this is called...
2231 l_uc_header_id := ahl_util_uc_pkg.get_uc_header_id(p_x_nonroutine_rec.instance_id);
2232 IF (NVL(l_uc_header_id, -1 ) <> p_x_nonroutine_rec.unit_config_header_id)
2233 THEN
2234 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_UNIT_NOMATCH');
2235 FND_MSG_PUB.ADD;
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238 ELSE
2239 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_UNIT_ACTV_INV');
2240 FND_MSG_PUB.ADD;
2241 RAISE FND_API.G_EXC_ERROR;
2242 END IF;
2243 END IF;
2244
2245 /* Behavior of Log Series and Number in "Unit / Component Details" sub-header
2246 * log_series and log_number are non-mandatory (except for MEL/CDL qualification)
2247 * log_series and log_number exist in combination
2248 * log_series and log_number are always user-editable, but the combination is unique
2249 * post association of MEL/CDL instr, log_series and log_number cannot be NULL
2250 */
2251 IF (p_x_nonroutine_rec.log_series_code IS NULL OR p_x_nonroutine_rec.log_series_code = FND_API.G_MISS_CHAR)
2252 THEN
2253 IF (p_x_nonroutine_rec.log_series_meaning IS NOT NULL AND p_x_nonroutine_rec.log_series_meaning <> FND_API.G_MISS_CHAR)
2254 THEN
2255 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2256 (
2257 p_lookup_type => 'AHL_LOG_SERIES_CODE',
2258 p_lookup_meaning => p_x_nonroutine_rec.log_series_meaning,
2259 x_lookup_code => p_x_nonroutine_rec.log_series_code,
2260 x_return_val => l_ret_val
2261 );
2262 IF NOT (l_ret_val)
2263 THEN
2264 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_INV');
2265 -- Log Series is invalid
2266 FND_MSG_PUB.ADD;
2267 END IF;
2268 END IF;
2269 ELSE
2270 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_LOG_SERIES_CODE', p_x_nonroutine_rec.log_series_code))
2271 THEN
2272 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_INV');
2273 -- Log Series is invalid
2274 FND_MSG_PUB.ADD;
2275 END IF;
2276 END IF;
2277
2278 IF (p_x_nonroutine_rec.log_series_number IS NOT NULL AND p_x_nonroutine_rec.log_series_number <> FND_API.G_MISS_NUM AND p_x_nonroutine_rec.log_series_number < 0)
2279 THEN
2280 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_LOGNUM_INV');
2281 -- Non-routine Log Number must be a positive integer
2282 FND_MSG_PUB.ADD;
2283 END IF;
2284
2285 IF (
2286 (p_x_nonroutine_rec.log_series_code IS NULL OR p_x_nonroutine_rec.log_series_code = FND_API.G_MISS_CHAR)
2287 AND
2288 (p_x_nonroutine_rec.log_series_number IS NOT NULL AND p_x_nonroutine_rec.log_series_number <> FND_API.G_MISS_NUM)
2289 )
2290 OR
2291 (
2292 (p_x_nonroutine_rec.log_series_code IS NOT NULL AND p_x_nonroutine_rec.log_series_code <> FND_API.G_MISS_CHAR)
2293 AND
2294 (p_x_nonroutine_rec.log_series_number IS NULL OR p_x_nonroutine_rec.log_series_number = FND_API.G_MISS_NUM)
2295 )
2296 THEN
2297 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_COMB');
2298 -- If Log Series is selected, Log Number is mandatory and vice-versa.
2299 FND_MSG_PUB.ADD;
2300 END IF;
2301
2302 IF (
2303 p_x_nonroutine_rec.log_series_code IS NOT NULL AND p_x_nonroutine_rec.log_series_code <> FND_API.G_MISS_CHAR
2304 AND
2305 p_x_nonroutine_rec.log_series_number IS NOT NULL AND p_x_nonroutine_rec.log_series_number <> FND_API.G_MISS_NUM
2306 )
2307 THEN
2308 OPEN check_lognum_unique;
2309 FETCH check_lognum_unique INTO l_dummy_varchar;
2310 IF (check_lognum_unique%FOUND)
2311 THEN
2312 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGNUM_INV');
2313 FND_MESSAGE.SET_TOKEN('LOGNUM', p_x_nonroutine_rec.log_series_code||'-'||p_x_nonroutine_rec.log_series_number);
2314 -- Log Number already exists for another non-routine
2315 FND_MSG_PUB.ADD;
2316 END IF;
2317 CLOSE check_lognum_unique;
2318 END IF;
2319
2320 --priyan
2321 --Fix for Bug# 5350385
2322 IF (p_dml_operation = 'U')
2323 THEN
2324 OPEN is_ue_mel_cdl_qual;
2325 FETCH is_ue_mel_cdl_qual INTO l_dummy_varchar;
2326 IF (is_ue_mel_cdl_qual%FOUND
2327 AND
2328 (
2329 p_x_nonroutine_rec.log_series_code IS NULL OR p_x_nonroutine_rec.log_series_code = FND_API.G_MISS_CHAR
2330 OR
2331 p_x_nonroutine_rec.log_series_number IS NULL OR p_x_nonroutine_rec.log_series_number = FND_API.G_MISS_NUM
2332 )
2333 )
2334 THEN
2335 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_NOCHG');
2336 -- Log Series is mandatory for Non-Routines that have MEL/CDL Instruction associated.
2337 FND_MSG_PUB.ADD;
2338 END IF;
2339 CLOSE is_ue_mel_cdl_qual;
2340 END IF;
2341
2342 /*
2343 -- Validate log series code
2344 IF (p_dml_operation = 'C')
2345 THEN
2346 IF (p_x_nonroutine_rec.log_series_code IS NULL OR p_x_nonroutine_rec.log_series_code = FND_API.G_MISS_CHAR)
2347 THEN
2348 IF (p_x_nonroutine_rec.log_series_meaning IS NULL OR p_x_nonroutine_rec.log_series_meaning = FND_API.G_MISS_CHAR)
2349 THEN
2350 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_MAND');
2351 -- Log Series is mandatory
2352 FND_MSG_PUB.ADD;
2353 ELSE
2354 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2355 (
2356 p_lookup_type => 'AHL_LOG_SERIES_CODE',
2357 p_lookup_meaning => p_x_nonroutine_rec.log_series_meaning,
2358 x_lookup_code => p_x_nonroutine_rec.log_series_code,
2359 x_return_val => l_ret_val
2360 );
2361 IF NOT (l_ret_val)
2362 THEN
2363 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_INV');
2364 -- Log Series is invalid
2365 FND_MSG_PUB.ADD;
2366 END IF;
2367 END IF;
2368 ELSE
2369 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_LOG_SERIES_CODE', p_x_nonroutine_rec.log_series_code))
2370 THEN
2371 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_INV');
2372 -- Log Series is invalid
2373 FND_MSG_PUB.ADD;
2374 END IF;
2375 END IF;
2376 ELSIF (p_dml_operation = 'U' AND l_ue_detail_rec.log_series_code IS NOT NULL)
2377 THEN
2378 IF (l_ue_detail_rec.log_series_code <> p_x_nonroutine_rec.log_series_code)
2379 THEN
2380 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGSER_NOCHG');
2381 -- Log Series cannot be modified after non-routine is created
2382 FND_MSG_PUB.ADD;
2383 END IF;
2384 END IF;
2385
2386 -- Validate log series number
2387 IF (p_dml_operation = 'C')
2388 THEN
2389 IF (p_x_nonroutine_rec.log_series_number IS NULL OR p_x_nonroutine_rec.log_series_number = FND_API.G_MISS_NUM)
2390 THEN
2391 SELECT ahl_log_series_s.NEXTVAL INTO p_x_nonroutine_rec.log_series_number FROM DUAL;
2392 ELSE
2393 OPEN check_lognum_unique;
2394 FETCH check_lognum_unique INTO l_dummy_varchar;
2395 IF (check_lognum_unique%FOUND)
2396 THEN
2397 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGNUM_INV');
2398 -- Log Number already exists for another non-routine
2399 FND_MSG_PUB.ADD;
2400 END IF;
2401 CLOSE check_lognum_unique;
2402 END IF;
2403 ELSIF (p_dml_operation = 'U' AND l_ue_detail_rec.log_series_number IS NOT NULL)
2404 THEN
2405 IF (l_ue_detail_rec.log_series_number <> p_x_nonroutine_rec.log_series_number)
2406 THEN
2407 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_LOGNUM_NOCHG');
2408 -- Log Number cannot be modified after non-routine is created
2409 FND_MSG_PUB.ADD;
2410 END IF;
2411 END IF;
2412 */
2413
2414 -- Validate MEL/CDL type
2415 IF (p_x_nonroutine_rec.mel_cdl_type_code IS NULL OR p_x_nonroutine_rec.mel_cdl_type_code = FND_API.G_MISS_CHAR)
2416 THEN
2417 IF (p_x_nonroutine_rec.mel_cdl_type_meaning IS NOT NULL AND p_x_nonroutine_rec.mel_cdl_type_meaning <> FND_API.G_MISS_CHAR)
2418 THEN
2419 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2420 (
2421 p_lookup_type => 'AHL_MEL_CDL_TYPE',
2422 p_lookup_meaning => p_x_nonroutine_rec.mel_cdl_type_meaning,
2423 x_lookup_code => p_x_nonroutine_rec.mel_cdl_type_code,
2424 x_return_val => l_ret_val
2425 );
2426 IF NOT (l_ret_val)
2427 THEN
2428 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_TYPE_INV');
2429 -- Position ATA is invalid
2430 FND_MSG_PUB.ADD;
2431 END IF;
2432 END IF;
2433 ELSE
2434 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_MEL_CDL_TYPE', p_x_nonroutine_rec.mel_cdl_type_code))
2435 THEN
2436 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_TYPE_INV');
2437 -- Position ATA is invalid
2438 FND_MSG_PUB.ADD;
2439 END IF;
2440 END IF;
2441
2442 -- Validate ata code
2443 IF (p_x_nonroutine_rec.ata_code IS NULL OR p_x_nonroutine_rec.ata_code = FND_API.G_MISS_CHAR)
2444 THEN
2445 IF (p_x_nonroutine_rec.ata_meaning IS NOT NULL AND p_x_nonroutine_rec.ata_meaning <> FND_API.G_MISS_CHAR)
2446 THEN
2447 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2448 (
2449 p_lookup_type => 'AHL_ATA_CODE',
2450 p_lookup_meaning => p_x_nonroutine_rec.ata_meaning,
2451 x_lookup_code => p_x_nonroutine_rec.ata_code,
2452 x_return_val => l_ret_val
2453 );
2454 IF NOT (l_ret_val)
2455 THEN
2456 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_ATA_INV');
2457 -- Position ATA is invalid
2458 FND_MSG_PUB.ADD;
2459 END IF;
2460 END IF;
2461 ELSE
2462 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_ATA_CODE', p_x_nonroutine_rec.ata_code))
2463 THEN
2464 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_ATA_INV');
2465 -- Position ATA is invalid
2466 FND_MSG_PUB.ADD;
2467 END IF;
2468 END IF;
2469
2470 -- Validate position path exists on the unit
2471 IF (p_x_nonroutine_rec.position_path_id IS NOT NULL AND p_x_nonroutine_rec.position_path_id <> FND_API.G_MISS_NUM)
2472 THEN
2473 AHL_MC_PATH_POSITION_PVT.Map_Position_To_Instances
2474 (
2475 p_api_version => 1.0,
2476 p_init_msg_list => FND_API.G_FALSE,
2477 p_commit => FND_API.G_FALSE,
2478 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2479 x_return_status => l_return_status,
2480 x_msg_count => l_msg_count,
2481 x_msg_data => l_msg_data,
2482 p_position_id => p_x_nonroutine_rec.position_path_id
2483 );
2484
2485 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
2486 THEN
2487 OPEN check_pos_path_exists;
2488 FETCH check_pos_path_exists INTO l_dummy_varchar;
2489 IF (check_pos_path_exists%NOTFOUND)
2490 THEN
2491 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_POSPATH_INV');
2492 -- Position path does not exist on the unit
2493 FND_MSG_PUB.ADD;
2494 END IF;
2495 CLOSE check_pos_path_exists;
2496 ELSE
2497 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2498 THEN
2499 fnd_log.string
2500 (
2501 G_DEBUG_STMT,
2502 l_debug_module,
2503 'Call to AHL_MC_PATH_POSITION_PVT.Map_Position_To_Instances failed with error ['||l_msg_data||']'
2504 );
2505 END IF;
2506 END IF;
2507 END IF;
2508
2509 -- Verify MEL/CDL qualification information cannot be modified if NR is pending approval / approved
2510 OPEN check_ue_mel_cdl_approved;
2511 FETCH check_ue_mel_cdl_approved INTO l_dummy_varchar;
2512 IF (p_dml_operation = 'U' AND check_ue_mel_cdl_approved%FOUND)
2513 THEN
2514 IF
2515 (
2516 l_ue_detail_rec.mel_cdl_type_code <> p_x_nonroutine_rec.mel_cdl_type_code
2517 AND
2518 l_ue_detail_rec.ata_code <> p_x_nonroutine_rec.ata_code
2519 AND
2520 l_ue_detail_rec.position_path_id <> p_x_nonroutine_rec.position_path_id
2521 )
2522 THEN
2523 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_QUAL_APPR');
2524 -- Cannot modify either of MEL/CDL Type, Position and Position ATA since Non-routine is either pending for MEL/CDL approval or already approved.
2525 FND_MSG_PUB.ADD;
2526 END IF;
2527 END IF;
2528
2529 /*
2530 -- Validate clear station org
2531 IF (p_x_nonroutine_rec.clear_station_org_id IS NULL OR p_x_nonroutine_rec.clear_station_org_id = FND_API.G_MISS_NUM)
2532 THEN
2533 IF (p_x_nonroutine_rec.clear_station_org IS NOT NULL AND p_x_nonroutine_rec.clear_station_org <> FND_API.G_MISS_CHAR)
2534 THEN
2535 OPEN get_org_id_from_name(p_x_nonroutine_rec.clear_station_org);
2536 FETCH get_org_id_from_name INTO p_x_nonroutine_rec.clear_station_org_id;
2537 IF (get_org_id_from_name%NOTFOUND)
2538 THEN
2539 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_ORG_INV');
2540 -- Clear station organization is invalid
2541 FND_MSG_PUB.ADD;
2542 END IF;
2543 CLOSE get_org_id_from_name;
2544 END IF;
2545 ELSE
2546 OPEN check_org_id(p_x_nonroutine_rec.clear_station_org_id);
2547 FETCH check_org_id INTO l_dummy_varchar;
2548 IF (check_org_id%NOTFOUND)
2549 THEN
2550 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_ORG_INV');
2551 -- Clear station organization is invalid
2552 FND_MSG_PUB.ADD;
2553 END IF;
2554 CLOSE check_org_id;
2555 END IF;
2556
2557 -- Validate clear station dept
2558 IF (p_x_nonroutine_rec.clear_station_dept_id IS NULL OR p_x_nonroutine_rec.clear_station_dept_id = FND_API.G_MISS_NUM)
2559 THEN
2560 IF (p_x_nonroutine_rec.clear_station_dept IS NOT NULL AND p_x_nonroutine_rec.clear_station_dept <> FND_API.G_MISS_CHAR)
2561 THEN
2562 OPEN get_dept_id_from_name(p_x_nonroutine_rec.clear_station_dept, p_x_nonroutine_rec.clear_station_org_id);
2563 FETCH get_dept_id_from_name INTO p_x_nonroutine_rec.clear_station_dept_id;
2564 IF (get_dept_id_from_name%NOTFOUND)
2565 THEN
2566 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_DEPT_INV');
2567 -- Clear station department is invalid
2568 FND_MSG_PUB.ADD;
2569 END IF;
2570 CLOSE get_dept_id_from_name;
2571 END IF;
2572 ELSE
2573 OPEN check_dept_id(p_x_nonroutine_rec.clear_station_dept_id, p_x_nonroutine_rec.clear_station_org_id);
2574 FETCH check_dept_id INTO l_dummy_varchar;
2575 IF (check_dept_id%NOTFOUND)
2576 THEN
2577 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NR_DEPT_INV');
2578 -- Clear station department is invalid
2579 FND_MSG_PUB.ADD;
2580 END IF;
2581 CLOSE check_dept_id;
2582 END IF;
2583 */
2584
2585 END Validate_UE_Details;
2586
2587 -----------------------------------------
2588 -- Non-spec Procedure Get_Ata_Sequence --
2589 -----------------------------------------
2590 PROCEDURE Get_Ata_Sequence
2591 (
2592 p_unit_effectivity_id IN NUMBER,
2593 p_ata_code IN VARCHAR2,
2594 x_ata_sequence_id OUT NOCOPY NUMBER
2595 )
2596 IS
2597 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.Get_Ata_Sequence';
2598
2599 l_return_status VARCHAR2(1);
2600 l_msg_count NUMBER;
2601 l_msg_data VARCHAR2(2000);
2602
2603 l_mel_cdl_header_id NUMBER;
2604
2605 CURSOR get_ue_details
2606 IS
2607 SELECT ahl_util_uc_pkg.get_uc_header_id(csi_item_instance_id) unit_config_id,
2608 ahl_util_uc_pkg.get_unit_name(csi_item_instance_id) unit_config_name,
2609 mel_cdl_type_code,
2610 position_path_id,
2611 ata_code
2612 FROM ahl_unit_effectivities_b
2613 WHERE unit_effectivity_id = p_unit_effectivity_id;
2614
2615 l_ue_details_rec get_ue_details%rowtype;
2616
2617 CURSOR check_ata_exists
2618 (
2619 p_mel_cdl_header_id number,
2620 p_ata_code varchar2
2621 )
2622 IS
2623 SELECT mel_cdl_ata_sequence_id
2624 FROM ahl_mel_cdl_ata_sequences
2625 WHERE mel_cdl_header_id = p_mel_cdl_header_id and
2626 ata_code = p_ata_code;
2627
2628 CURSOR get_ata_for_position
2629 (
2630 p_position_path_id number,
2631 p_unit_config_id number
2632 )
2633 IS
2634 SELECT mch.ata_code
2635 FROM ahl_applicable_instances appl,
2636 csi_ii_relationships cii,
2637 ahl_mc_relationships mch,
2638 ahl_unit_config_headers uch
2639 WHERE appl.position_id = p_position_path_id and
2640 appl.csi_item_instance_id = cii.subject_id and
2641 cii.position_reference = mch.relationship_id and
2642 mch.mc_header_id = uch.master_config_id and
2643 uch.unit_config_header_id = p_unit_config_id
2644 UNION ALL
2645 SELECT mch.ata_code
2646 FROM ahl_applicable_instances appl,
2647 ahl_mc_relationships mch,
2648 ahl_unit_config_headers uch
2649 WHERE appl.position_id = p_position_path_id and
2650 appl.csi_item_instance_id = uch.csi_item_instance_id and
2651 mch.mc_header_id = uch.master_config_id and
2652 uch.unit_config_header_id = p_unit_config_id;
2653
2654 l_ata_for_position varchar2(30);
2655
2656 BEGIN
2657
2658 IF (p_unit_effectivity_id IS NOT NULL AND p_unit_effectivity_id <> FND_API.G_MISS_NUM)
2659 THEN
2660 OPEN get_ue_details;
2661 FETCH get_ue_details INTO l_ue_details_rec;
2662 CLOSE get_ue_details;
2663
2664 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2665 THEN
2666 fnd_log.string
2667 (
2668 G_DEBUG_STMT,
2669 l_debug_module,
2670 'l_ue_details_rec [unit_config_id='||l_ue_details_rec.unit_config_id||'][unit_config_name='
2671 ||l_ue_details_rec.unit_config_name||']
2672 [mel_cdl_type_code='||l_ue_details_rec.mel_cdl_type_code||'][position_path_id='||l_ue_details_rec.position_path_id||']
2673 [ata_code='||l_ue_details_rec.ata_code||']'
2674 );
2675 END IF;
2676
2677 IF (l_ue_details_rec.mel_cdl_type_code IS NULL OR l_ue_details_rec.mel_cdl_type_code = FND_API.G_MISS_CHAR)
2678 THEN
2679 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_QUAL_TYPE_MAND');
2680 -- MEL/CDL Type is mandatory, hence cannot qualify for MEL/CDL
2681 FND_MSG_PUB.ADD;
2682 END IF;
2683
2684 l_mel_cdl_header_id := Get_Mel_Cdl_Header_Id(p_unit_effectivity_id, null, null);
2685 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2686 THEN
2687 fnd_log.string
2688 (
2689 G_DEBUG_STMT,
2690 l_debug_module,
2691 'l_mel_cdl_header_id='||l_mel_cdl_header_id||']'
2692 );
2693 END IF;
2694
2695 IF (l_mel_cdl_header_id IS NULL)
2696 THEN
2697 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_QUAL_SETUP_MAND');
2698 FND_MESSAGE.SET_TOKEN('UCNAME', l_ue_details_rec.unit_config_name);
2699 -- No MEL/CDL has been setup for the unit "UCNAME", hence cannot qualify for MEL/CDL
2700 FND_MSG_PUB.ADD;
2701 ELSE
2702 IF (
2703 (l_ue_details_rec.ata_code IS NULL OR l_ue_details_rec.ata_code = FND_API.G_MISS_CHAR)
2704 AND
2705 (l_ue_details_rec.position_path_id IS NULL OR l_ue_details_rec.position_path_id = FND_API.G_MISS_NUM)
2706 )
2707 THEN
2708 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_QUAL_ALL_NULL');
2709 -- One of Position ATA and Position is mandatory, hence cannot qualify for MEL/CDL
2710 FND_MSG_PUB.ADD;
2711 END IF;
2712
2713 IF (l_ue_details_rec.position_path_id IS NOT NULL AND l_ue_details_rec.position_path_id <> FND_API.G_MISS_NUM)
2714 THEN
2715 AHL_MC_PATH_POSITION_PVT.Map_Position_To_Instances
2716 (
2717 p_api_version => 1.0,
2718 p_init_msg_list => FND_API.G_FALSE,
2719 p_commit => FND_API.G_FALSE,
2720 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2721 x_return_status => l_return_status,
2722 x_msg_count => l_msg_count,
2723 x_msg_data => l_msg_data,
2724 p_position_id => l_ue_details_rec.position_path_id
2725 );
2726
2727
2728 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
2729 THEN
2730 OPEN get_ata_for_position(l_ue_details_rec.position_path_id, l_ue_details_rec.unit_config_id);
2731 FETCH get_ata_for_position INTO l_ata_for_position;
2732 CLOSE get_ata_for_position;
2733
2734 IF (l_ata_for_position IS NOT NULL)
2735 THEN
2736
2737 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2738 THEN
2739 fnd_log.string
2740 (
2741 G_DEBUG_STMT,
2742 l_debug_module,
2743 'ATA Code of Position ATA is ='||p_ata_code||']'
2744 );
2745 END IF;
2746
2747 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2748 THEN
2749 fnd_log.string
2750 (
2751 G_DEBUG_STMT,
2752 l_debug_module,
2753 'ATA Code of Position ='||l_ata_for_position||']'
2754 );
2755 END IF;
2756
2757 --Priyan Fix for Bug # 5359840
2758 -- Check made to validate whether the ATA Code of the MC Position (if any)
2759 -- matches with the Position ATA Code entered.
2760 -- If it does not match , throw an error .
2761 IF (l_ata_for_position <> p_ata_code)
2762 THEN
2763 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_POS_ATA_NO_MATCH');
2764 -- ATA code of the Position ATA and MC Position does not match .
2765 FND_MSG_PUB.ADD;
2766 RAISE FND_API.G_EXC_ERROR;
2767 END IF;
2768 --End of changes by Priyan
2769
2770 OPEN check_ata_exists(l_mel_cdl_header_id, l_ata_for_position);
2771 FETCH check_ata_exists INTO x_ata_sequence_id;
2772 CLOSE check_ata_exists;
2773
2774 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2775 THEN
2776 fnd_log.string
2777 (
2778 G_DEBUG_STMT,
2779 l_debug_module,
2780 'From position_path_id -- x_ata_sequence_id='||x_ata_sequence_id||']'
2781 );
2782 END IF;
2783
2784 IF x_ata_sequence_id IS NOT NULL
2785 THEN
2786 UPDATE ahl_unit_effectivities_b
2787 SET ata_code = l_ata_for_position
2788 WHERE unit_effectivity_id = p_unit_effectivity_id;
2789 END IF;
2790 END IF;
2791 ELSE
2792 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2793 THEN
2794 fnd_log.string
2795 (
2796 G_DEBUG_STMT,
2797 l_debug_module,
2798 'Call to AHL_MC_PATH_POSITION_PVT.Map_Position_To_Instances failed with error ['||l_msg_data||']'
2799 );
2800 END IF;
2801 END IF;
2802 END IF;
2803
2804 IF (x_ata_sequence_id IS NULL AND l_ue_details_rec.ata_code IS NOT NULL AND l_ue_details_rec.ata_code <> FND_API.G_MISS_CHAR)
2805 THEN
2806 OPEN check_ata_exists(l_mel_cdl_header_id, l_ue_details_rec.ata_code);
2807 FETCH check_ata_exists INTO x_ata_sequence_id;
2808 CLOSE check_ata_exists;
2809
2810 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
2811 THEN
2812 fnd_log.string
2813 (
2814 G_DEBUG_STMT,
2815 l_debug_module,
2816 'From ata_code -- x_ata_sequence_id='||x_ata_sequence_id||']'
2817 );
2818 END IF;
2819 END IF;
2820
2821 IF (x_ata_sequence_id IS NULL)
2822 THEN
2823 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_QUAL_SEQ_FAIL');
2824 -- Cannot retrieve any System Sequence information for non-routine, hence cannot qualify for MEL/CDL
2825 FND_MSG_PUB.ADD;
2826 END IF;
2827 END IF;
2828 END IF;
2829
2830 END Get_Ata_Sequence;
2831
2832 PROCEDURE Validate_SR_Details
2833 (
2834 p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type,
2835 p_dml_operation IN VARCHAR2
2836 )
2837 IS
2838
2839 CURSOR cs_incident_exists
2840 IS
2841 SELECT incident_number, incident_date
2842 FROM cs_incidents_all_b
2843 WHERE incident_id = p_x_nonroutine_rec.incident_id AND
2844 object_version_number = p_x_nonroutine_rec.incident_object_version_number;
2845
2846 l_incident_number VARCHAR2(64);
2847 l_incident_date DATE;
2848 l_instance_owner_id NUMBER;
2849
2850 CURSOR cs_severity_in_eam_priority
2851 IS
2852 SELECT 'x'
2853 FROM cs_incident_severities_vl csv,
2854 mfg_lookups mfl
2855 WHERE mfl.lookup_code = csv.incident_severity_id AND
2856 csv.incident_severity_id = p_x_nonroutine_rec.severity_id AND
2857 csv.incident_subtype = 'INC' AND
2858 mfl.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' AND
2859 trunc(sysdate) between trunc(nvl(csv.start_date_active,sysdate)) AND trunc(nvl(csv.end_date_active,sysdate));
2860
2861 l_incident_urgency NUMBER;
2862
2863 -- Note: Need to perform all SR invalid validations, since it looks like the CS APIs do not give good messages
2864
2865 BEGIN
2866
2867 -- Validate instance_number and instance_id
2868 IF (p_x_nonroutine_rec.instance_number IS NULL OR p_x_nonroutine_rec.instance_number = FND_API.G_MISS_CHAR)
2869 THEN
2870 IF (p_x_nonroutine_rec.instance_id is null or p_x_nonroutine_rec.instance_id = FND_API.G_MISS_NUM)
2871 THEN
2872 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_INSTANCE_NULL');
2873 FND_MSG_PUB.ADD;
2874 RAISE FND_API.G_EXC_ERROR;
2875 END IF;
2876 ELSE
2877 BEGIN
2878 -- Bug #4918818: APPSPERF fix
2879 -- There is no need to join with MTL/HOU since this is just a instance_number to instance_id validation,
2880 -- the check for whether the instance exists on an active unit is being done in Validate_UE_Details
2881 SELECT csi.instance_id
2882 INTO p_x_nonroutine_rec.instance_id
2883 FROM csi_item_instances csi
2884 WHERE trunc(nvl(csi.active_start_date, sysdate)) <= trunc(sysdate) and
2885 trunc(nvl(csi.active_end_date, sysdate+1)) > trunc(sysdate) and
2886 csi.instance_number = p_x_nonroutine_rec.instance_number;
2887 EXCEPTION
2888 WHEN OTHERS THEN
2889 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_INSTANCE_INV');
2890 FND_MESSAGE.SET_TOKEN('INSTANCE', p_x_nonroutine_rec.instance_number);
2891 FND_MSG_PUB.ADD;
2892 END;
2893 END IF;
2894
2895 IF (p_dml_operation = 'C')
2896 THEN
2897 -- Changes made for Bug # 5183032
2898 -- Commenting as the incident date can be any date prior to sysdate.
2899 -- p_x_nonroutine_rec.incident_date := sysdate;
2900
2901 -- Validate and default SR type
2902 IF (p_x_nonroutine_rec.type_name is not null and p_x_nonroutine_rec.type_name <> FND_API.G_MISS_CHAR)
2903 THEN
2904 BEGIN
2905 SELECT incident_type_id
2906 INTO p_x_nonroutine_rec.type_id
2907 FROM cs_incident_types_vl
2908 WHERE name = p_x_nonroutine_rec.type_name and
2909 cmro_flag = 'Y' and
2910 incident_subtype = 'INC' and
2911 trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
2912 EXCEPTION
2913 WHEN NO_DATA_FOUND THEN
2914 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_TYPE_INV');
2915 FND_MESSAGE.SET_TOKEN('TYPE', p_x_nonroutine_rec.type_name);
2916 FND_MSG_PUB.ADD;
2917 END;
2918 ELSIF (p_x_nonroutine_rec.type_id is null or p_x_nonroutine_rec.type_id = FND_API.G_MISS_NUM)
2919 THEN
2920 p_x_nonroutine_rec.type_id := fnd_profile.value('AHL_PRD_SR_TYPE');
2921
2922 IF (p_x_nonroutine_rec.type_id is null or p_x_nonroutine_rec.type_id = FND_API.G_MISS_NUM)
2923 THEN
2924 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_TYPE_NULL');
2925 FND_MSG_PUB.ADD;
2926 END IF;
2927 END IF;
2928
2929 -- Validate and default SR status
2930 IF (p_x_nonroutine_rec.status_name is not null and p_x_nonroutine_rec.status_name <> FND_API.G_MISS_CHAR)
2931 THEN
2932 BEGIN
2933 SELECT incident_status_id
2934 INTO p_x_nonroutine_rec.status_id
2935 FROM cs_incident_statuses_vl
2936 WHERE name = p_x_nonroutine_rec.status_name and
2937 trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
2938 EXCEPTION
2939 WHEN NO_DATA_FOUND THEN
2940 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_STATUS_INV');
2941 FND_MESSAGE.SET_TOKEN('STATUS', p_x_nonroutine_rec.status_name);
2942 FND_MSG_PUB.ADD;
2943 END;
2944 ELSIF (p_x_nonroutine_rec.status_id is null or p_x_nonroutine_rec.status_id = FND_API.G_MISS_NUM)
2945 THEN
2946 p_x_nonroutine_rec.status_id := nvl(fnd_profile.value('AHL_PRD_SR_STATUS'), G_SR_OPEN_STATUS_ID);
2947 END IF;
2948
2949 -- Validate and default SR severity
2950 IF (p_x_nonroutine_rec.severity_name is not null and p_x_nonroutine_rec.severity_name <> FND_API.G_MISS_CHAR)
2951 THEN
2952 BEGIN
2953 SELECT incident_severity_id
2954 INTO p_x_nonroutine_rec.severity_id
2955 FROM cs_incident_severities_vl
2956 WHERE name = p_x_nonroutine_rec.severity_name and
2957 trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
2958 EXCEPTION
2959 WHEN NO_DATA_FOUND THEN
2960 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_SEVERITY_INV');
2961 FND_MESSAGE.SET_TOKEN('SEVERITY', p_x_nonroutine_rec.severity_name);
2962 FND_MSG_PUB.ADD;
2963 END;
2964 ELSIF (p_x_nonroutine_rec.severity_id is null or p_x_nonroutine_rec.severity_id = FND_API.G_MISS_NUM)
2965 THEN
2966 p_x_nonroutine_rec.severity_id := fnd_profile.value('AHL_PRD_SR_SEVERITY');
2967
2968 IF (p_x_nonroutine_rec.severity_id is null or p_x_nonroutine_rec.severity_id = FND_API.G_MISS_NUM)
2969 THEN
2970 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_SEVERITY_NULL');
2971 FND_MSG_PUB.ADD;
2972 END IF;
2973 END IF;
2974
2975 -- Validate severity against WIP_EAM_ACTIVITY_PRIORITY
2976 /*IF (p_x_nonroutine_rec.severity_id is not null and p_x_nonroutine_rec.severity_id <> FND_API.G_MISS_NUM)
2977 THEN
2978 OPEN cs_severity_in_eam_priority;
2979 FETCH cs_severity_in_eam_priority INTO l_dummy_varchar;
2980 IF (cs_severity_in_eam_priority%NOTFOUND) THEN
2981 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_SEV_EAM_INV');
2982 FND_MSG_PUB.ADD;
2983 END IF;
2984 CLOSE cs_severity_in_eam_priority;
2985 END IF;*/
2986
2987 -- Retrieve instance customer id
2988 SELECT NVL(OWNER_PARTY_ID, -1)
2989 INTO l_instance_owner_id
2990 FROM csi_item_instances
2991 WHERE instance_id = p_x_nonroutine_rec.instance_id;
2992
2993 -- Validate and default customer...
2994 IF (p_x_nonroutine_rec.customer_name IS NOT NULL AND p_x_nonroutine_rec.customer_name <> FND_API.G_MISS_CHAR)
2995 THEN
2996 BEGIN
2997 SELECT party_id, party_type
2998 INTO p_x_nonroutine_rec.customer_id, p_x_nonroutine_rec.customer_type
2999 FROM hz_parties
3000 WHERE status = 'A' AND
3001 party_type IN ('PERSON', 'ORGANIZATION') AND
3002 party_name = p_x_nonroutine_rec.customer_name AND
3003 party_id = NVL(p_x_nonroutine_rec.customer_id, party_id);
3004 --AMSRINIV. Bug 5199456. Added last AND condition to the above query.
3005 EXCEPTION
3006 WHEN NO_DATA_FOUND THEN
3007 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_CUST_NAME_INVALID');
3008 FND_MESSAGE.SET_TOKEN('CUST_NAME', p_x_nonroutine_rec.customer_name);
3009 FND_MSG_PUB.ADD;
3010 WHEN TOO_MANY_ROWS THEN
3011 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_CUST_NAME_NOT_UNIQUE');
3012 FND_MESSAGE.SET_TOKEN('CUST_NAME', p_x_nonroutine_rec.customer_name);
3013 FND_MSG_PUB.ADD;
3014 END;
3015
3016 -- Validate user-input customer against instance owner...
3017 IF (nvl(p_x_nonroutine_rec.customer_id, -1) <> l_instance_owner_id)
3018 THEN
3019 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_CUSTOMER_NOMATCH');
3020 FND_MSG_PUB.ADD;
3021 END IF;
3022 ELSIF (p_x_nonroutine_rec.customer_id IS NULL or p_x_nonroutine_rec.customer_id = FND_API.G_MISS_NUM)
3023 THEN
3024 p_x_nonroutine_rec.customer_id := l_instance_owner_id;
3025
3026 -- Default customer_type if customer_id is read from profile
3027 IF (p_x_nonroutine_rec.customer_id IS NOT NULL AND p_x_nonroutine_rec.customer_id <> FND_API.G_MISS_NUM)
3028 THEN
3029 SELECT party_name, party_type
3030 INTO p_x_nonroutine_rec.customer_name, p_x_nonroutine_rec.customer_type
3031 FROM hz_parties
3032 WHERE status = 'A' AND
3033 party_type IN ('PERSON', 'ORGANIZATION') AND
3034 party_id = p_x_nonroutine_rec.customer_id;
3035 END IF;
3036 END IF;
3037
3038 -- Error if customer_type is NULL and customer_id NOT NULL
3039 IF
3040 (
3041 p_x_nonroutine_rec.customer_id is not null AND p_x_nonroutine_rec.customer_id <> FND_API.G_MISS_NUM
3042 AND
3043 (p_x_nonroutine_rec.customer_type is null or p_x_nonroutine_rec.customer_type = FND_API.G_MISS_CHAR)
3044 )
3045 THEN
3046 FND_MESSAGE.SET_NAME(G_APP_NAME,'AHL_UMP_NR_CUST_TYPE_NULL');
3047 FND_MSG_PUB.ADD;
3048 END IF;
3049
3050 -- Validate and contact name and type... Error if contact_type is NULL and contact_id NOT NULL
3051 IF (p_x_nonroutine_rec.contact_name IS NOT NULL AND p_x_nonroutine_rec.contact_name <> FND_API.G_MISS_CHAR)
3052 THEN
3053 BEGIN
3054 IF (p_x_nonroutine_rec.contact_type in ('PARTY_RELATIONSHIP', 'PERSON'))
3055 THEN
3056 SELECT party_id
3057 INTO p_x_nonroutine_rec.contact_id
3058 FROM hz_parties
3059 WHERE status = 'A' AND
3060 party_name = p_x_nonroutine_rec.contact_name;
3061 ELSIF (p_x_nonroutine_rec.contact_type = 'EMPLOYEE')
3062 THEN
3063 -- Bug #4918818: APPSPERF fix
3064 -- Using per_people_x here, since it already has the inactive person check on per_people_f
3065 SELECT person_id
3066 INTO p_x_nonroutine_rec.contact_id
3067 FROM per_people_x
3068 WHERE full_name = p_x_nonroutine_rec.contact_name;
3069 ELSE
3070 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_CONT_TYPE_INV');
3071 FND_MESSAGE.SET_TOKEN('CONT_TYPE', p_x_nonroutine_rec.contact_type);
3072 FND_MSG_PUB.ADD;
3073 END IF;
3074 EXCEPTION
3075 WHEN NO_DATA_FOUND THEN
3076 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_CONT_NAME_INVALID');
3077 FND_MESSAGE.SET_TOKEN('CONT_NAME', p_x_nonroutine_rec.contact_name);
3078 FND_MSG_PUB.ADD;
3079 WHEN TOO_MANY_ROWS THEN
3080 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_CONT_NAME_NOT_UNIQUE');
3081 FND_MESSAGE.SET_TOKEN('CONT_NAME', p_x_nonroutine_rec.contact_name);
3082 FND_MSG_PUB.ADD;
3083 END;
3084 END IF;
3085
3086 -- Error if problem_summary NULL
3087 IF (p_x_nonroutine_rec.problem_summary is null or p_x_nonroutine_rec.problem_summary = FND_API.G_MISS_CHAR)
3088 THEN
3089 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_SUMMARY_NULL');
3090 FND_MSG_PUB.ADD;
3091 END IF;
3092
3093 -- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
3094 -- Bug # 6447467 -- start
3095 -- Check if resolution_code is not null. If resolution_code
3096 -- is null then return error message.
3097
3098 IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
3099
3100 IF ( p_x_nonroutine_rec.resolution_code IS NULL OR
3101 p_x_nonroutine_rec.resolution_code = FND_API.G_MISS_CHAR) THEN
3102
3103 Fnd_Message.SET_NAME(G_APP_NAME,'AHL_PRD_RESL_CODE_REQ');
3104 Fnd_Msg_Pub.ADD;
3105 END IF;
3106
3107 END IF;
3108
3109 -- Validate if expected resolution date is passed, it is greater than the incident date
3110 IF (p_x_nonroutine_rec.expected_resolution_date is not null and p_x_nonroutine_rec.expected_resolution_date <> FND_API.G_MISS_DATE and trunc(p_x_nonroutine_rec.expected_resolution_date) < trunc(sysdate))
3111 THEN
3112 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_EXP_RES_DATE_INV');
3113 FND_MSG_PUB.ADD;
3114 END IF;
3115
3116 -- Validate if the incident date is greater than the sysdate
3117 -- Changes made for Bug # 5183032
3118 IF (p_x_nonroutine_rec.incident_date is not null and p_x_nonroutine_rec.incident_date <> FND_API.G_MISS_DATE and trunc(p_x_nonroutine_rec.incident_date) > trunc(sysdate))
3119 THEN
3120 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_INC_DATE_INV');
3121 FND_MSG_PUB.ADD;
3122 END IF;
3123
3124 ELSIF(p_dml_operation = 'U')
3125 THEN
3126 -- Incident exists, number is not changed, date is not changed
3127 OPEN cs_incident_exists;
3128 FETCH cs_incident_exists INTO l_incident_number, l_incident_date;
3129 IF (cs_incident_exists%NOTFOUND)
3130 THEN
3131 FND_MESSAGE.SET_NAME(G_APP_NAME,'AHL_UMP_NR_INC_INV');
3132 FND_MSG_PUB.ADD;
3133 CLOSE cs_incident_exists;
3134 RAISE FND_API.G_EXC_ERROR;
3135 ELSIF (l_incident_number <> p_x_nonroutine_rec.incident_number)
3136 THEN
3137 FND_MESSAGE.SET_NAME(G_APP_NAME,'AHL_UMP_NR_NUM_INV');
3138 FND_MSG_PUB.ADD;
3139 CLOSE cs_incident_exists;
3140 RAISE FND_API.G_EXC_ERROR;
3141 END IF;
3142 CLOSE cs_incident_exists;
3143
3144 -- Validate status_id is not null
3145 IF (p_x_nonroutine_rec.status_id is null or p_x_nonroutine_rec.status_id = FND_API.G_MISS_NUM)
3146 THEN
3147 FND_MESSAGE.SET_NAME(G_APP_NAME,'AHL_UMP_NR_STATUS_NULL');
3148 FND_MSG_PUB.ADD;
3149 END IF;
3150
3151 -- Validate type_id is not null
3152 IF (p_x_nonroutine_rec.type_id is null or p_x_nonroutine_rec.type_id = FND_API.G_MISS_NUM)
3153 THEN
3154 FND_MESSAGE.SET_NAME(G_APP_NAME,'AHL_UMP_NR_TYPE_NULL');
3155 FND_MSG_PUB.ADD;
3156 END IF;
3157
3158 -- Error if problem_summary NULL
3159 IF (p_x_nonroutine_rec.problem_summary is null or p_x_nonroutine_rec.problem_summary = FND_API.G_MISS_CHAR)
3160 THEN
3161 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_SUMMARY_NULL');
3162 FND_MSG_PUB.ADD;
3163 END IF;
3164
3165 -- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
3166 -- Bug # 6447467 -- start
3167 -- Check if resolution_code is not null. If resolution_code
3168 -- is null then return error message.
3169
3170 IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
3171
3172 IF ( p_x_nonroutine_rec.resolution_code IS NULL OR
3173 p_x_nonroutine_rec.resolution_code = FND_API.G_MISS_CHAR) THEN
3174
3175 Fnd_Message.SET_NAME(G_APP_NAME,'AHL_PRD_RESL_CODE_REQ');
3176 Fnd_Msg_Pub.ADD;
3177 END IF;
3178
3179 END IF;
3180
3181 -- Validate if the expected resolution date is not null and that it is not lesser than the Incident Request Date
3182 -- Changes made for Bug # 5183032
3183 IF (p_x_nonroutine_rec.expected_resolution_date is not null and p_x_nonroutine_rec.expected_resolution_date <> FND_API.G_MISS_DATE and trunc(p_x_nonroutine_rec.expected_resolution_date) < trunc(l_incident_date))
3184 THEN
3185 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_NR_EXP_RES_DATE_INV');
3186 FND_MSG_PUB.ADD;
3187 END IF;
3188 END IF;
3189
3190 -- Validate resolution_code and problem_code...
3191
3192 END Validate_SR_Details;
3193
3194 PROCEDURE Process_MO_procedures
3195 (
3196 p_unit_effectivity_id IN NUMBER,
3197 p_unit_deferral_id IN NUMBER,
3198 p_unit_deferral_ovn IN NUMBER,
3199 p_ata_sequence_id IN NUMBER,
3200 p_cs_incident_id IN NUMBER,
3201 p_csi_item_instance_id IN NUMBER)
3202 IS
3203
3204 -- get service request details.
3205 cursor cs_inc_csr (p_cs_incident_id IN NUMBER)
3206 is
3207 select incident_severity_id, customer_id, caller_type,
3208 nvl(incident_occurred_date,incident_date) incident_occurred_date,
3209 expected_resolution_date , object_version_number, incident_number
3210 from cs_incidents_all_b
3211 where incident_id = p_cs_incident_id;
3212
3213 CURSOR GetWoName(p_ue_id IN NUMBER)
3214 Is
3215 Select wo.workorder_name, tsk.visit_id
3216 from ahl_workorders wo, ahl_visit_tasks_b tsk
3217 where wo.visit_task_id = tsk.visit_task_id
3218 and tsk.unit_effectivity_id = p_ue_id
3219 and tsk.task_type_code IN ('SUMMARY','UNASSOCIATED');
3220
3221 -- get primary contact if exists.
3222 cursor prim_contact_csr (p_cs_incident_id IN NUMBER)
3223 is
3224 select party_id, contact_type
3225 from cs_sr_contact_points_v
3226 where incident_id = p_cs_incident_id
3227 and primary_flag = 'Y';
3228
3229 -- get ue details for SR created.
3230 cursor get_ue_detls(p_cs_incident_id IN NUMBER)
3231 is
3232 select cs.object_version_number, ue.unit_effectivity_id
3233 from ahl_unit_effectivities_b UE, cs_incidents_all_b cs
3234 where ue.cs_incident_id = cs.incident_id
3235 and cs.incident_id = p_cs_incident_id;
3236
3237 -- get default incident type.
3238 cursor default_incident_type_csr
3239 is
3240 SELECT INCIDENT_TYPE_ID
3241 FROM cs_incident_types_vl
3242 where INCIDENT_SUBTYPE = 'INC'
3243 AND CMRO_FLAG = 'Y'
3244 AND incident_type_id=fnd_profile.value('AHL_MCL_M_AND_O_SR_TYPE')
3245 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
3246 AND trunc(nvl(end_date_active,sysdate));
3247
3248 -- query m and o procedures.
3249 cursor mo_procedures_csr (p_ata_sequence_id IN NUMBER)
3250 is
3251 select mr_header_id
3252 from ahl_mel_cdl_mo_procedures
3253 where ata_sequence_id = p_ata_sequence_id;
3254
3255 -- get inventory item and organization id.
3256 CURSOR default_item_org_id(p_ue_id IN NUMBER) IS
3257 SELECT A.inventory_item_id,
3258 vst.organization_id
3259 FROM AHL_VISIT_TASKS_B A, ahl_visits_b vst
3260 WHERE a.visit_id = vst.visit_id
3261 and A.unit_effectivity_id = p_ue_id
3262 AND A.task_type_code IN ('SUMMARY','UNASSOCIATED')
3263 AND rownum = 1;
3264
3265 -- get urgency for the ata sequence.
3266 CURSOR get_urgency_details_csr (p_ata_sequence_id IN NUMBER)
3267 is
3268 select repair_time, sr_urgency_id
3269 from ahl_mel_cdl_ata_sequences seq, ahl_repair_categories rep
3270 where mel_cdl_ata_sequence_id = p_ata_sequence_id
3271 and seq.repair_category_id = rep.repair_category_id;
3272
3273 -- get deferral details.
3274 CURSOR deferral_ue_csr (p_deferral_id IN NUMBER) IS
3275 SELECT unit_deferral_id,
3276 ata_sequence_id,
3277 unit_deferral_type,
3278 defer_reason_code,
3279 skip_mr_flag,
3280 affect_due_calc_flag,
3281 set_due_date,
3282 deferral_effective_on,
3283 remarks,approver_notes, user_deferral_type,
3284 attribute_category, attribute1,
3285 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
3286 attribute8, attribute9, attribute10, attribute11, attribute12,
3287 attribute13, attribute14, attribute15
3288 FROM ahl_unit_deferrals_vl
3289 WHERE unit_deferral_id = p_deferral_id;
3290
3291 -- get new UE for the incident ID deferred.
3292 CURSOR get_new_ue_csr (p_cs_incident_id IN NUMBER) IS
3293 SELECT unit_effectivity_id
3294 FROM ahl_unit_effectivities_b
3295 WHERE cs_incident_id = p_cs_incident_id
3296 AND status_code IS NULL;
3297
3298 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
3299 l_notes_table CS_ServiceRequest_PUB.notes_table;
3300 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
3301 l_contact_primary_flag CONSTANT VARCHAR2(1) := 'Y';
3302 l_auto_assign CONSTANT VARCHAR2(1) := 'N';
3303
3304 l_return_status VARCHAR2(1);
3305 l_msg_count NUMBER;
3306 l_msg_data VARCHAR2(2000);
3307 l_summary VARCHAR2(2000); --cs_incidents_all_b.summary%TYPE;
3308
3309 l_wo_name ahl_workorders.workorder_name%TYPE;
3310 l_individual_owner NUMBER;
3311 l_group_owner NUMBER;
3312 l_individual_type VARCHAR2(30);
3313
3314 l_prim_contact_rec prim_contact_csr%ROWTYPE;
3315 l_inc_rec cs_inc_csr%ROWTYPE;
3316 l_incident_type_id NUMBER;
3317 l_mr_sr_assoc_tbl AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type;
3318 l_visit_id NUMBER;
3319 l_new_incident_id NUMBER;
3320 l_new_incident_number cs_incidents_all_b.incident_number%TYPE;
3321 l_new_interaction_id NUMBER;
3322 l_new_workflow_process_id NUMBER;
3323 l_cs_object_version NUMBER;
3324 l_new_ue_id NUMBER;
3325
3326 l_vwp_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type;
3327 l_deferral_rec deferral_ue_csr%ROWTYPE;
3328
3329 i NUMBER;
3330 l_wo_id NUMBER;
3331 l_sr_urgency_id NUMBER;
3332 l_repair_time NUMBER;
3333 l_rowid VARCHAR2(30);
3334 l_unit_deferral_id NUMBER;
3335 l_workflow_process_id NUMBER;
3336 l_interaction_id NUMBER;
3337 l_new_cs_ue_id NUMBER;
3338
3339
3340 BEGIN
3341
3342 -- log debug message.
3343 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3344 fnd_log.string(G_DEBUG_PROC,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3345 'At Start of procedure AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures');
3346 END IF;
3347
3348 -- read sr attributes.
3349 OPEN cs_inc_csr(p_cs_incident_id);
3350 FETCH cs_inc_csr INTO l_inc_rec;
3351 IF (cs_inc_csr%NOTFOUND) THEN
3352 CLOSE cs_inc_csr;
3353 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_INC_ERROR');
3354 FND_MESSAGE.set_token('INC_ID', p_cs_incident_id);
3355 FND_MSG_PUB.ADD;
3356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3357 END IF;
3358
3359 -- Debug Checkpoint.
3360 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3361 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3362 'Starting SR Update');
3363 END IF;
3364
3365 -- Update urgency and expected resolution date.
3366 OPEN get_urgency_details_csr (p_ata_sequence_id);
3367 FETCH get_urgency_details_csr INTO l_repair_time, l_sr_urgency_id;
3368 IF (get_urgency_details_csr%NOTFOUND) THEN
3369 CLOSE get_urgency_details_csr;
3370 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_ATA_ERROR');
3371 FND_MESSAGE.set_token('ATA_ID', p_ata_sequence_id);
3372 FND_MSG_PUB.ADD;
3373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3374 END IF;
3375 CLOSE get_urgency_details_csr;
3376
3377 -- Update SR for the urgency and exp. resolution date.
3378 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
3379
3380 --l_service_request_rec.incident_id := p_cs_incident_id;
3381 --l_service_request_rec.object_version_number := l_inc_rec.object_version_number;
3382 l_service_request_rec.urgency_id := l_sr_urgency_id;
3383 IF (l_repair_time <> 0) THEN
3384 l_service_request_rec.exp_resolution_date := l_inc_rec.incident_occurred_date + trunc(l_repair_time/24);
3385 END IF;
3386
3387 -- Call SR API.
3388 CS_SERVICEREQUEST_PUB.Update_ServiceRequest
3389 (
3390 p_api_version => 3.0,
3391 p_init_msg_list => FND_API.G_FALSE,
3392 p_commit => FND_API.G_FALSE,
3393 x_return_status => l_return_status,
3394 x_msg_count => l_msg_count,
3395 x_msg_data => l_msg_data,
3396 p_request_id => p_cs_incident_id,
3397 p_request_number => NULL,
3398 p_audit_comments => NULL,
3399 p_object_version_number => l_inc_rec.object_version_number,
3400 p_resp_appl_id => fnd_global.resp_appl_id,
3401 p_resp_id => fnd_global.resp_id,
3402 p_last_updated_by => fnd_global.user_id,
3403 p_last_update_login => fnd_global.login_id,
3404 p_last_update_date => sysdate,
3405 p_service_request_rec => l_service_request_rec,
3406 p_notes => l_notes_table,
3407 p_contacts => l_contacts_table,
3408 p_called_by_workflow => NULL,
3409 p_workflow_process_id => NULL,
3410 x_workflow_process_id => l_workflow_process_id,
3411 x_interaction_id => l_interaction_id
3412 );
3413
3414 -- log debug message.
3415 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3416 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3417 'After call to Update Service Request :return_status:' || l_return_status);
3418 END IF;
3419
3420 -- Raise errors if exceptions occur
3421 IF (upper(l_return_status) = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3423 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3424 RAISE FND_API.G_EXC_ERROR;
3425 ELSE
3426 FND_MSG_PUB.INITIALIZE;
3427 END IF;
3428
3429 -- form summary.
3430 Open GetWoName(p_unit_effectivity_id);
3431 Fetch GetWoName into l_wo_name, l_visit_id;
3432 Close GetWoName;
3433
3434 fnd_message.set_name('AHL','AHL_PRD_SR_SUMMARY');
3435 fnd_message.set_token('WORKORDER_NUM',rtrim(l_wo_name));
3436 fnd_message.set_token('INC_NUM', rtrim(l_inc_rec.incident_number));
3437 l_summary := fnd_message.get;
3438
3439 -- log debug message.
3440 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3441 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3442 'New Sr Summary is:' || l_summary);
3443 END IF;
3444
3445 -- get incident type.
3446 Open default_incident_type_csr;
3447 Fetch default_incident_type_csr INTO l_incident_type_id;
3448 IF ( default_incident_type_csr%NOTFOUND) THEN
3449 CLOSE default_incident_type_csr;
3450 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_INCIDENT_ERROR');
3451 Fnd_Msg_Pub.ADD;
3452 RAISE FND_API.G_EXC_ERROR;
3453 END IF;
3454 CLOSE default_incident_type_csr;
3455
3456 --Initialize the SR record.
3457 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
3458
3459 -- Assign the SR rec values
3460 l_service_request_rec.type_id := l_incident_type_id;
3461 l_service_request_rec.request_date := sysdate;
3462 l_service_request_rec.status_id := G_SR_OPEN_STATUS_ID;
3463 l_service_request_rec.severity_id := l_inc_rec.incident_severity_id;
3464 l_service_request_rec.urgency_id := l_sr_urgency_id;
3465 l_service_request_rec.summary := substr(l_summary,1,240);
3466 l_service_request_rec.caller_type := l_inc_rec.caller_type;
3467 l_service_request_rec.customer_id := l_inc_rec.customer_id;
3468 l_service_request_rec.creation_program_code := 'AHL_ROUTINE';
3469 l_service_request_rec.customer_product_id := p_csi_item_instance_id;
3470 -- added for bug fix - 5330932
3471 l_service_request_rec.incident_occurred_date := sysdate;
3472
3473 OPEN prim_contact_csr(p_cs_incident_id);
3474 FETCH prim_contact_csr INTO l_prim_contact_rec;
3475 IF (prim_contact_csr%FOUND) THEN
3476 l_contacts_table(1).party_id := l_prim_contact_rec.party_id;
3477 l_contacts_table(1).contact_type := l_prim_contact_rec.contact_type;
3478 l_contacts_table(1).primary_flag := 'Y';
3479 END IF;
3480 CLOSE prim_contact_csr;
3481
3482 -- item/org.
3483 open default_item_org_id(p_unit_effectivity_id);
3484 Fetch default_item_org_id INTO l_service_request_rec.inventory_item_id,
3485 l_service_request_rec.inventory_org_id;
3486 IF (default_item_org_id%NOTFOUND ) THEN
3487 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_DEFAULT_ORG_ERROR');
3488 Fnd_Msg_Pub.ADD;
3489 RAISE FND_API.G_EXC_ERROR;
3490 END IF;
3491
3492 -- Call to Service Request API
3493
3494 CS_SERVICEREQUEST_PUB.Create_ServiceRequest(
3495 p_api_version => 3.0,
3496 p_init_msg_list => FND_API.G_TRUE,
3497 p_commit => FND_API.G_FALSE,
3498 x_return_status => l_return_status,
3499 x_msg_count => l_msg_count,
3500 x_msg_data => l_msg_data,
3501 p_resp_appl_id => NULL,
3502 p_resp_id => NULL,
3503 p_user_id => fnd_global.user_id,
3504 p_login_id => fnd_global.conc_login_id,
3505 p_org_id => NULL,
3506 p_request_id => NULL,
3507 p_request_number => NULL,
3508 p_service_request_rec => l_service_request_rec,
3509 p_notes => l_notes_table,
3510 p_contacts => l_contacts_table,
3511 p_auto_assign => l_auto_assign,
3512 x_request_id => l_new_incident_id,
3513 x_request_number => l_new_incident_number,
3514 x_interaction_id => l_new_interaction_id,
3515 x_workflow_process_id => l_new_workflow_process_id,
3516 x_individual_owner => l_individual_owner,
3517 x_group_owner => l_individual_owner,
3518 x_individual_type => l_individual_type
3519 );
3520
3521
3522 -- log debug message.
3523 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3524 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3525 'After call to Create Service Request :return_status:' || l_return_status);
3526 END IF;
3527
3528 -- Raise errors if exceptions occur
3529 IF (upper(l_return_status) = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3531 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3532 RAISE FND_API.G_EXC_ERROR;
3533 ELSE
3534 FND_MSG_PUB.INITIALIZE;
3535 END IF;
3536
3537 -- get object version number for the service request and the new ue id.
3538 OPEN get_ue_detls (l_new_incident_id);
3539 FETCH get_ue_detls INTO l_cs_object_version, l_new_ue_id;
3540 IF (get_ue_detls%NOTFOUND) THEN
3541 CLOSE get_ue_detls;
3542 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UE_ERROR');
3543 FND_MESSAGE.set_token('INC_ID', p_cs_incident_id);
3544 FND_MSG_PUB.ADD;
3545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3546 END IF;
3547
3548 -- Add M and O procedures to the SR.
3549 i := 1;
3550
3551 FOR mo_proc_rec IN mo_procedures_csr(p_ata_sequence_id) LOOP
3552 l_mr_sr_assoc_tbl(i).mr_header_id := mo_proc_rec.mr_header_id;
3553 l_mr_sr_assoc_tbl(i).OPERATION_FLAG := 'C';
3554 l_mr_sr_assoc_tbl(i).RELATIONSHIP_CODE := 'PARENT';
3555 l_mr_sr_assoc_tbl(i).CSI_INSTANCE_ID := p_csi_item_instance_id;
3556
3557 i := i + 1;
3558
3559 END LOOP;
3560
3561 IF (l_mr_sr_assoc_tbl.count > 0) THEN
3562 AHL_UMP_SR_PVT.Process_SR_MR_Associations
3563 (
3564 p_api_version => 1.0,
3565 p_init_msg_list => FND_API.G_FALSE,
3566 p_commit => FND_API.G_FALSE,
3567 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3568 --p_module_type => 'MEL_CDL',
3569 x_return_status => l_return_status,
3570 x_msg_count => l_msg_count,
3571 x_msg_data => l_msg_data,
3572 p_user_id => fnd_global.user_id,
3573 p_login_id => fnd_global.login_id,
3574 p_request_id => l_new_incident_id,
3575 p_object_version_number => l_cs_object_version,
3576 p_request_number => null,
3577 p_x_sr_mr_association_tbl => l_mr_sr_assoc_tbl
3578 );
3579
3580 -- log debug message.
3581 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3582 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3583 'After call to Process_SR_MR_Associations :return_status:' || l_return_status);
3584 END IF;
3585
3586 -- Raise errors if exceptions occur
3587 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3589 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3590 RAISE FND_API.G_EXC_ERROR;
3591 END IF;
3592
3593 END IF;
3594
3595 -- Call VWP api to add the SR into the visit.
3596 l_vwp_task_rec.visit_id := l_visit_id;
3597 l_vwp_task_rec.unit_effectivity_id := l_new_ue_id;
3598 l_vwp_task_rec.service_request_id := l_new_incident_id;
3599 l_vwp_task_rec.task_type_code := 'PLANNED';
3600
3601 AHL_VWP_TASKS_PVT.Create_Task (
3602 p_api_version => 1.0,
3603 p_init_msg_list => Fnd_Api.g_false,
3604 p_commit => Fnd_Api.g_false,
3605 p_validation_level => Fnd_Api.g_valid_level_full,
3606 p_module_type => 'SR',
3607 p_x_task_rec => l_vwp_task_rec,
3608 x_return_status => l_return_status,
3609 x_msg_count => l_msg_count,
3610 x_msg_data => l_msg_data);
3611
3612 -- log debug message.
3613 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3614 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3615 'After call to Create Task API:return_status:' || l_return_status);
3616 END IF;
3617
3618 -- Raise errors if exceptions occur
3619 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3621 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3622 RAISE FND_API.G_EXC_ERROR;
3623 END IF;
3624
3625
3626 -- Release MR.
3627 AHL_VWP_PROJ_PROD_PVT.Release_MR(
3628 p_api_version => 1.0,
3629 p_init_msg_list => Fnd_Api.G_FALSE,
3630 p_commit => Fnd_Api.G_FALSE,
3631 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
3632 p_module_type => 'PROD',
3633 p_visit_id => l_visit_id,
3634 p_unit_effectivity_id => l_new_ue_id,
3635 -- fix for bug# 5498884. Created work orders should be in released status.
3636 p_release_flag => 'Y',
3637 x_workorder_id => l_wo_id,
3638 x_return_status => l_return_status,
3639 x_msg_count => l_msg_count,
3640 x_msg_data => l_msg_data);
3641
3642 -- log debug message.
3643 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3644 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3645 'After call to release workorders:return_status:' || l_return_status);
3646 END IF;
3647
3648 -- Raise errors if exceptions occur
3649 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3651 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3652 RAISE FND_API.G_EXC_ERROR;
3653 END IF;
3654
3655 IF (fnd_msg_pub.count_msg > 0) THEN
3656 RAISE FND_API.G_EXC_ERROR;
3657 END IF;
3658
3659 -- make a call for automatic approval
3660 AHL_PRD_DF_PVT.process_approval_approved(
3661 p_unit_deferral_id => p_unit_deferral_id,
3662 p_object_version_number => p_unit_deferral_ovn,
3663 --l_deferral_rec.object_version_number,
3664 p_new_status => 'DEFERRED',
3665 x_return_status => l_return_status);
3666
3667 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3668 IF (fnd_log.level_error >= G_DEBUG_LEVEL)THEN
3669 fnd_log.string
3670 (
3671 fnd_log.level_error,
3672 'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval',
3673 'Can not go ahead with automatic approval because AHL_UMP_NONROUTINES_PVT.Initiate_Mel_Cdl_Approval threw error');
3674 END IF;
3675 RAISE FND_API.G_EXC_ERROR;
3676 END IF;
3677
3678 -- create a new deferral row for the new ue ID. Copy the attributes from the old deferral record.
3679 OPEN deferral_ue_csr(p_unit_deferral_id);
3680 FETCH deferral_ue_csr INTO l_deferral_rec;
3681 IF (deferral_ue_csr%NOTFOUND) THEN
3682 CLOSE deferral_ue_csr;
3683 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UE_DEF_MISSING');
3684 FND_MESSAGE.set_token('UE_DEF', p_unit_deferral_id);
3685 FND_MSG_PUB.ADD;
3686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3687 END IF;
3688 CLOSE deferral_ue_csr;
3689
3690 -- find the new ue ID.
3691 OPEN get_new_ue_csr(p_cs_incident_id);
3692 FETCH get_new_ue_csr INTO l_new_cs_ue_id;
3693 IF (get_new_ue_csr%NOTFOUND) THEN
3694 CLOSE get_new_ue_csr;
3695 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_NEW_UE_MISSING');
3696 FND_MESSAGE.set_token('INC_ID', p_cs_incident_id);
3697 FND_MSG_PUB.ADD;
3698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3699 END IF;
3700 CLOSE get_new_ue_csr;
3701
3702 -- Insert row.
3703 AHL_UNIT_DEFERRALS_PKG.insert_row(
3704 x_rowid => l_rowid,
3705 x_unit_deferral_id => l_unit_deferral_id,
3706 x_ata_sequence_id => l_deferral_rec.ata_sequence_id,
3707 x_object_version_number => 1,
3708 x_created_by => fnd_global.user_id,
3709 x_creation_date => sysdate,
3710 x_last_updated_by => fnd_global.user_id,
3711 x_last_update_date => sysdate,
3712 x_last_update_login => fnd_global.login_id,
3713 x_unit_effectivity_id => l_new_cs_ue_id,
3714 x_unit_deferral_type => l_deferral_rec.unit_deferral_type,
3715 x_set_due_date => l_deferral_rec.set_due_date,
3716 x_deferral_effective_on => l_deferral_rec.deferral_effective_on,
3717 x_approval_status_code => 'DEFERRED',
3718 x_defer_reason_code => l_deferral_rec.defer_reason_code,
3719 x_affect_due_calc_flag => l_deferral_rec.affect_due_calc_flag,
3720 x_skip_mr_flag => l_deferral_rec.skip_mr_flag,
3721 x_remarks => l_deferral_rec.remarks,
3722 x_approver_notes => l_deferral_rec.approver_notes,
3723 x_user_deferral_type => l_deferral_rec.user_deferral_type,
3724 x_attribute_category => null,
3725 x_attribute1 => null,
3726 x_attribute2 => null,
3727 x_attribute3 => null,
3728 x_attribute4 => null,
3729 x_attribute5 => null,
3730 x_attribute6 => null,
3731 x_attribute7 => null,
3732 x_attribute8 => null,
3733 x_attribute9 => null,
3734 x_attribute10 => null,
3735 x_attribute11 => null,
3736 x_attribute12 => null,
3737 x_attribute13 => null,
3738 x_attribute14 => null,
3739 x_attribute15 => null
3740 );
3741
3742 -- log debug message.
3743 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3744 fnd_log.string(G_DEBUG_STMT,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3745 'After insert into ahl_unit_deferrals table: deferral ID:' || l_unit_deferral_id);
3746 END IF;
3747
3748 -- log debug message.
3749 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3750 fnd_log.string(G_DEBUG_PROC,'ahl.plsql.AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures',
3751 'At End of procedure AHL_UMP_NONROUTINES_PVT.Process_MO_Procedures');
3752 END IF;
3753
3754 END Process_MO_procedures;
3755
3756 End AHL_UMP_NONROUTINES_PVT;