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