1 PACKAGE BODY AHL_UMP_SR_PVT AS
2 /* $Header: AHLVUSRB.pls 120.18.12020000.2 2012/12/11 03:23:54 prakkum ship $ */
3
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UMP_SR_PVT';
8
9 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_UMP_SR_PVT';
10
11 G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
12 G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
13 G_SR_CLOSED_FLAG CONSTANT VARCHAR2(1) := 'C';
14 G_SR_OPEN_FLAG CONSTANT VARCHAR2(1) := 'O';
15
16 G_APP_MODULE CONSTANT VARCHAR2(30) := 'AHL';
17
18 -- UMP Statuses
19 G_UMP_TERMINATED_STATUS CONSTANT VARCHAR2(30) := 'TERMINATED';
20 G_UMP_SR_CLOSED_STATUS CONSTANT VARCHAR2(30) := 'SR-CLOSED';
21 G_UMP_ACCOMPLISHED_STATUS CONSTANT VARCHAR2(30) := 'ACCOMPLISHED';
22 G_UMP_DEFERRED_STATUS CONSTANT VARCHAR2(30) := 'DEFERRED';
23 G_UMP_EXCEPTION_STATUS CONSTANT VARCHAR2(30) := 'EXCEPTION';
24 G_UMP_MR_TERMINATE_STATUS CONSTANT VARCHAR2(30) := 'MR-TERMINATE';
25
26 -- SR Statuses
27 --JR: Modified on 10/29/2003: Using Status Id instead of Code since STATUS_CODE
28 -- column of CS_INCIDENT_STATUSES_B is obsoleted per SR Team (Thomas Alex)
29 G_SR_PLANNED_STATUS_ID CONSTANT NUMBER := 52;
30
31 -- Operation Codes
32 G_OPR_CREATE CONSTANT VARCHAR2(1) := 'C';
33 G_OPR_DELETE CONSTANT VARCHAR2(1) := 'D';
34
35 -- UE Relationship Code
36 G_UE_PARENT_REL_CODE CONSTANT VARCHAR2(30) := 'PARENT';
37
38 -- UE Object Types
39 G_UE_MR_OBJECT_TYPE CONSTANT VARCHAR2(2) := 'MR';
40 G_UE_SR_OBJECT_TYPE CONSTANT VARCHAR2(2) := 'SR';
41
42 -------------------------------------------------
43 -- Declare Locally used Record and Table Types --
44 -------------------------------------------------
45
46 ------------------------------
47 -- Declare Local Procedures --
48 ------------------------------
49 -- This Procedure validates the request for the SR-MR Association API
50 PROCEDURE Validate_Associated_Request(
51 p_x_request_id IN OUT NOCOPY NUMBER,
52 p_request_number IN VARCHAR2,
53 p_object_version_number IN NUMBER,
54 x_sr_ue_id OUT NOCOPY NUMBER,
55 x_sr_instance_id OUT NOCOPY NUMBER,
56 x_sr_exp_resol_date OUT NOCOPY DATE);
57
58 -- This Procedure validates SR-MR Association Records
59 PROCEDURE Validate_Association_Records(
60 p_request_id IN NUMBER,
61 p_sr_ue_id IN NUMBER,
62 p_sr_instance_id IN NUMBER,
63 p_x_sr_mr_association_tbl IN OUT NOCOPY SR_MR_Association_Tbl_Type);
64
65 -- This Procedure deletes SR-MR associations
66 PROCEDURE Process_Disassociations(
67 p_sr_ue_id IN NUMBER,
68 p_sr_mr_association_tbl IN SR_MR_Association_Tbl_Type);
69
70 -- This Procedure Creates New SR-MR associations
71 PROCEDURE Process_New_Associations(
72 p_sr_ue_id IN NUMBER,
73 p_sr_instance_id IN NUMBER,
74 p_sr_exp_resol_date IN DATE,
75 p_user_id IN NUMBER,
76 p_login_id IN NUMBER,
77 p_x_sr_mr_association_tbl IN OUT NOCOPY SR_MR_Association_Tbl_Type);
78
79 -- This Procedure Gets the Unit Effectivity Id
80 PROCEDURE Get_MR_UnitEffectivity(
81 p_sr_ue_id IN NUMBER,
82 p_x_sr_mr_association_rec IN OUT NOCOPY SR_MR_Association_Rec_Type);
83
84 -- This Procedure does Value to Id Conversion for New Associations
85 PROCEDURE Get_New_Asso_Val_To_Id(
86 p_x_sr_mr_association_rec IN OUT NOCOPY SR_MR_Association_Rec_Type);
87
88 -- This Procedure creates a new unit effectivity
89 PROCEDURE Create_MR_Unit_Effectivity(
90 p_instance_id IN NUMBER,
91 p_due_date IN DATE,
92 p_mr_header_id IN NUMBER,
93 p_user_id IN NUMBER,
94 p_login_id IN NUMBER,
95 p_fleet_header_id IN NUMBER, -- Bug # 13916897
96 x_ue_id OUT NOCOPY NUMBER);
97
98 -- This Procedure creates a new UE Relationship
99 PROCEDURE Create_UE_Relationship(
100 p_ue_id IN NUMBER,
101 p_related_ue_id IN NUMBER,
102 p_relationship_code IN VARCHAR2,
103 p_originator_id IN NUMBER,
104 p_user_id IN NUMBER,
105 p_login_id IN NUMBER,
106 x_ue_rel_id OUT NOCOPY NUMBER);
107
108 -- This Procedure processes group MRs
109 PROCEDURE Process_Group_MR(
110 p_mr_header_id IN NUMBER,
111 p_csi_instance_id IN NUMBER,
112 p_mr_ue_id IN NUMBER,
113 p_sr_ue_id IN NUMBER,
114 p_due_date IN DATE,
115 p_user_id IN NUMBER,
116 p_login_id IN NUMBER,
117 p_fleet_header_id IN NUMBER, -- Bug # 13916897
118 p_x_valid_flag IN OUT NOCOPY BOOLEAN);
119
120 -- This Function gets the MR Title from the MR Header Id
121 FUNCTION Get_MR_Title_From_MR_Id(
122 p_mr_header_id IN NUMBER) RETURN VARCHAR2;
123
124 -- This Function gets the MR Title from the Unit Effectivity Id
125 FUNCTION Get_MR_Title_From_UE_Id(
126 p_unit_effectivity_id IN NUMBER) RETURN VARCHAR2;
127
128 -- This Procedure updates the due date and tolerance exceeded flag of UEs
129 -- of the MRs associated to the SR in response to change in Exp. Resolution Date of the SR
130 PROCEDURE Handle_MR_UE_Date_Change(
131 p_sr_ue_id IN NUMBER,
132 p_assigned_to_visit_flag IN BOOLEAN,
133 p_new_tolerance_flag IN VARCHAR2,
134 p_new_due_date IN DATE);
135
136 -- This Procedure updates the Unit Effectivity associated with the Service Request.
137 -- It updates the Status, Instance, Due Date and Tolerance Flag in response to updates in a SR
138 PROCEDURE Update_SR_Unit_Effectivity(
139 p_sr_ue_id IN NUMBER,
140 p_due_date_flag IN BOOLEAN,
141 p_new_due_date IN DATE,
142 p_instance_flag IN BOOLEAN,
143 p_new_instance_id IN NUMBER,
144 p_status_flag IN BOOLEAN,
145 p_new_status_code IN VARCHAR2,
146 x_assigned_to_visit_flag OUT NOCOPY BOOLEAN,
147 x_new_tolerance_flag OUT NOCOPY VARCHAR2);
148
149 -- This Procedure validates the Service Request during the Post Update process.
150 -- It retrieves some information as part of the validation process to be used by subsequent processes
151 -- added x_defer_from_ue_id to fix bug# 9166304
152 PROCEDURE Validate_Request_For_Update(
153 x_sr_ue_id OUT NOCOPY NUMBER,
154 x_sr_ue_ovn OUT NOCOPY NUMBER,
155 x_defer_from_ue_id OUT NOCOPY NUMBER);
156
157 -- This Procedure handles type (CMRO to Non-CMRO and vice-versa) changes to a SR
158 -- added p_defer_from_ue_id to fix bug# 9166304
159 PROCEDURE Handle_Type_Change(
160 p_sr_ue_id IN NUMBER,
161 p_defer_from_ue_id IN NUMBER);
162
163 -- This Procedure handles other attribute (Instance, Resolution Date and Status) changes to a SR
164 PROCEDURE Handle_Attribute_Changes(
165 p_sr_ue_id IN NUMBER);
166
167 -- This Procedure handles the change in the item instance (customer product) of the SR
168 -- added p_defer_from_ue_id to fix bug# 9166304
169 PROCEDURE Handle_Instance_Change(
170 p_sr_ue_id IN NUMBER,
171 p_old_instance_id IN NUMBER,
172 p_x_valid_flag IN OUT NOCOPY BOOLEAN, -- This flag will never be set to true in this procedure
173 x_instance_changed OUT NOCOPY BOOLEAN,
174 p_defer_from_ue_id IN NUMBER);
175
176 -- This Procedure handles the change in the status of the Service Request
177 PROCEDURE Handle_Status_Change(
178 p_sr_ue_id IN NUMBER,
179 p_old_ue_status IN AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE,
180 p_x_valid_flag IN OUT NOCOPY BOOLEAN, -- This flag will never be set to true in this procedure
181 x_status_changed OUT NOCOPY BOOLEAN,
182 x_new_ue_status OUT NOCOPY AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE);
183
184 -- This procedures handles updating the Description of the Workorders to SR Summary
185 -- for following cases
186 -- 1. Non-Routines(NR) created on the shop floor.
187 -- 2. Non-Routines with no MRs associated and planned from UMP into a Visit.
188 -- Balaji added this procedure for BAE ER # 4462462.
189 PROCEDURE Handle_Summary_Update(
190 p_sr_ue_id IN NUMBER
191 );
192
193 -------------------------------------
194 -- End Local Procedures Declaration--
195 -------------------------------------
196
197 -----------------------------------------
198 -- Public Procedure Definitions follow --
199 -----------------------------------------
200 -- Start of Comments --
201 -- Procedure name : Create_SR_Unit_Effectivity
202 -- Type : Private
203 -- Function : Private API to create a SR type unit effectivity. Called by corresponding Public procedure.
204 -- Uses CS_SERVICEREQUEST_PVT.USER_HOOKS_REC to get SR Details.
205 -- Pre-reqs :
206 -- Parameters :
207 -- x_return_status OUT VARCHAR2 Required
208 --
209 -- Version :
210 -- Initial Version 1.0
211 --
212 -- End of Comments.
213
214 PROCEDURE Create_SR_Unit_Effectivity
215 (
216 x_return_status OUT NOCOPY VARCHAR2) IS
217 l_api_name CONSTANT VARCHAR2(30) := 'Create_SR_Unit_Effectivity';
218 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_SR_Unit_Effectivity';
219
220 -- Validate Incident ID
221 CURSOR validate_incident_id ( c_request_id in NUMBER )
222 IS
223 SELECT incident_id
224 FROM cs_incidents_all_b
225 WHERE incident_id = c_request_id;
226
227 -- Validate Item Instance
228 CURSOR validate_instance ( c_instance_id in NUMBER)
229 IS
230 SELECT instance_id, instance_number, active_end_date
231 FROM csi_item_instances
232 WHERE instance_id = c_instance_id;
233
234 -- bachandr Bug # 13916897
235 CURSOR c_get_incident_occ_date(c_incident_id IN NUMBER) IS
236 SELECT incident_occurred_date FROM cs_incidents
237 WHERE incident_id = c_incident_id;
238
239 l_occurred_date DATE;
240
241 -- bachandr Bug # 13916897
242
243 --Cursor Variables
244 l_incident_id NUMBER;
245 l_instance_id NUMBER;
246 l_instance_number VARCHAR2(30);
247 l_active_end_date DATE;
248 l_name VARCHAR2(30);
249 --Procedure Returned Variables
250 l_appln_code VARCHAR2(20);
251 l_return_status VARCHAR2(1);
252 -- User Hook Variables
253 l_request_id NUMBER;
254 l_status_flag VARCHAR2(3);
255 l_old_type_cmro_flag VARCHAR2(3);
256 l_new_type_cmro_flag VARCHAR2(3);
257 l_customer_product_id NUMBER;
258 l_status_id NUMBER;
259 l_exp_resolution_date DATE;
260 -- Local Variables
261 l_ump_status fnd_lookups.lookup_code%TYPE;
262 l_unit_effectivity_id NUMBER;
263 l_rowid VARCHAR2(30);
264 l_accomplished_date DATE := null;
265
266 l_uc_hdr_id NUMBER;
267 l_uc_status_code VARCHAR2(30);
268
269 BEGIN
270
271 SAVEPOINT Create_SR_Unit_Effectivity_Pvt;
272
273 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Create_SR_Unit_Effectivity Procedure');
275 END IF;
276
277 -- Initialize message list
278 --AMSRINIV. Bug 5470730. Removing message initialization.
279 -- FND_MSG_PUB.Initialize;
280
281 -- Initialize API return status to success
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284 -- Begin Processing
285
286 --Getting all the required values from the user hook record
287 l_request_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
288 l_status_flag := cs_servicerequest_pvt.user_hooks_rec.status_flag;
289 l_old_type_cmro_flag := cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag;
290 l_new_type_cmro_flag := cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag;
291 l_customer_product_id := cs_servicerequest_pvt.user_hooks_rec.customer_product_id;
292 l_status_id := cs_servicerequest_pvt.user_hooks_rec.status_id;
293 l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
294
295 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
304 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'incident_occurred_date:' || cs_servicerequest_pvt.user_hooks_rec.incident_occurred_date);
305 END IF;
306
307
308 --******************************
309 --Validating the Request
310 --******************************
311
312 --Raising an error if the request id null
313 IF (l_request_id IS NULL OR l_request_id = FND_API.G_MISS_NUM) THEN
314 x_return_status := FND_API.G_RET_STS_ERROR;
315 FND_MESSAGE.set_name('AHL', 'AHL_UMP_MISSING_REQUEST_ID');
316 FND_MSG_PUB.add;
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319
320 --Opening cursor to validate incident id
321 OPEN validate_incident_id ( l_request_id );
322 FETCH validate_incident_id into l_incident_id;
323
324 --Raise an error if the incident id is not valid.
325 IF (validate_incident_id%NOTFOUND) THEN
326 fnd_message.set_name('AHL', 'AHL_UMP_INVALID_INCIDENT_ID');
327 fnd_message.set_token('INCIDENT_ID', l_request_id, false);
328 FND_MSG_PUB.add;
329 CLOSE validate_instance;
330 RAISE FND_API.G_EXC_ERROR;
331 END IF;
332
333 CLOSE validate_incident_id;
334
335 --Validating CMRO Type. If not CMRO type, return without processing.
336 IF (nvl(l_new_type_cmro_flag, 'N') <> 'Y') THEN
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338 RETURN;
339 END IF;
340
341 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
343 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
344 END IF;
345
346 --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
347 AHL_UTIL_PKG.Get_Appln_Usage
348 (
349 x_appln_code => l_appln_code,
350 x_return_status => l_return_status
351 );
352
353 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
355 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
356 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
357 END IF;
358
359
360 --Set the return status to an error and raise an error message if the return status is an error.
361 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 FND_MESSAGE.set_name('AHL', 'AHL_COM_APPL_USG_PROF_NOT_SET');
364 FND_MSG_PUB.add;
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367
368 --Set the return status to an error and raise an error message if the application code returned is not AHL
369 IF (l_appln_code <> 'AHL') THEN
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 FND_MESSAGE.set_name('AHL', 'AHL_COM_APPL_USG_MODE_INVALID');
372 FND_MESSAGE.set_token('TASK', l_name, false);
373 FND_MSG_PUB.add;
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376
377 --******************************
378 --Validating the status
379 --******************************
380 -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
381 -- Raise an error if the status is PLANNED.
382 IF (l_status_id = G_SR_PLANNED_STATUS_ID) THEN
383 FND_MESSAGE.set_name('AHL', 'AHL_UMP_INVALID_STATUS_CODE');
384 FND_MSG_PUB.add;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388 --******************************
389 --Validating the item instance
390 --******************************
391 --Raise an error if the item instance id null
392 IF (l_customer_product_id IS NULL OR l_customer_product_id = FND_API.G_MISS_NUM) THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MESSAGE.set_name('AHL', 'AHL_UMP_INSTANCE_MANDATORY');
395 FND_MSG_PUB.add;
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398
399 --Ensuring that the instance exists in csi_item_instances.
400 OPEN validate_instance(l_customer_product_id);
401 FETCH validate_instance into l_instance_id, l_instance_number, l_active_end_date;
402 --Raise an error if the instance is not a valid CSI Instance
403 IF (validate_instance%NOTFOUND) THEN
404 fnd_message.set_name('AHL', 'AHL_UMP_INVALID_CSI_INSTANCE');
405 fnd_message.set_token('CSI_INSTANCE_ID', l_customer_product_id, false);
406 FND_MSG_PUB.add;
407 CLOSE validate_instance;
408 RAISE FND_API.G_EXC_ERROR;
409 END IF;
410
411 --Raise an error if the instance is inactive.
412 IF ( l_active_end_date IS NOT NULL AND l_active_end_date <= SYSDATE ) THEN
413 fnd_message.set_name('AHL', 'AHL_UMP_INACTIVE_INSTANCE');
414 fnd_message.set_token('INSTANCE_NUMBER', l_instance_number, false);
415 FND_MSG_PUB.add;
416 CLOSE validate_instance;
417 RAISE FND_API.G_EXC_ERROR;
418 END IF;
419
420 CLOSE validate_instance;
421
422 IF (l_status_flag = 'O') THEN
423 l_ump_status := NULL;
424 ELSE
425 l_ump_status := 'SR-CLOSED';
426 /* JR: Modified on 10/21/2003 */
427 l_accomplished_date := SYSDATE;
428 END IF;
429
430 /* retrieve active unit on which SR instance is installed */
431 SELECT ahl_util_uc_pkg.get_uc_header_id(l_customer_product_id) into l_uc_hdr_id from dual;
432 IF (l_uc_hdr_id is not null)
433 THEN
434 -- if the instance's unit is in QUARANTINE/DEACTIVATE_QUARANTINE throw error
435 -- if the instance's unit is COMPLETE/INCOMPLETE, it is active hence use the unit
436 -- for all other cases treat the NR as being created for IB component only, i.e. no unit info
437 l_uc_status_code := ahl_util_uc_pkg.get_uc_status_code(l_uc_hdr_id);
438 IF (l_uc_status_code IN ('QUARANTINE', 'DEACTIVATE_QUARANTINE'))
439 THEN
440 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_NR_UNIT_QUAR_INV');
441 FND_MSG_PUB.ADD;
442 RAISE FND_API.G_EXC_ERROR;
443 ELSIF (l_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE'))
444 THEN
445 l_uc_hdr_id := null;
446 END IF;
447 END IF;
448
449 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
450 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
451 END IF;
452
453
454 -- bachandr Bug # 13916897
455 IF l_exp_resolution_date IS NULL
456 THEN
457 OPEN c_get_incident_occ_date(l_request_id);
458 FETCH c_get_incident_occ_date INTO l_occurred_date;
459 CLOSE c_get_incident_occ_date;
460 END IF;
461 -- bachandr Bug # 13916897
462
463 --******************************
464 -- Call Table Handler.
465 --******************************
466 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
467 X_ROWID => l_rowid,
468 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
469 X_MANUALLY_PLANNED_FLAG => 'Y',
470 X_LOG_SERIES_CODE => null,
471 X_LOG_SERIES_NUMBER => null,
472 X_FLIGHT_NUMBER => null,
473 X_MEL_CDL_TYPE_CODE => null,
474 X_POSITION_PATH_ID => null,
475 X_ATA_CODE => null,
476 --X_CLEAR_STATION_ORG_ID => null,
477 --X_CLEAR_STATION_DEPT_ID => null,
478 X_UNIT_CONFIG_HEADER_ID => l_uc_hdr_id,
479 X_QA_COLLECTION_ID => null,
480 X_CS_INCIDENT_ID => l_request_id,
481 X_OBJECT_TYPE => 'SR',
482 X_APPLICATION_USG_CODE => l_appln_code,
483 X_COUNTER_ID => null,
484 X_EARLIEST_DUE_DATE => null,
485 X_LATEST_DUE_DATE => null,
486 X_FORECAST_SEQUENCE => null,
487 X_REPETITIVE_MR_FLAG => null,
488 X_TOLERANCE_FLAG => null,
489 X_MESSAGE_CODE => null,
490 X_DATE_RUN => null,
491 X_PRECEDING_UE_ID => null,
492 X_SET_DUE_DATE => null,
493 X_ACCOMPLISHED_DATE => l_accomplished_date,
494 X_SERVICE_LINE_ID => null,
495 X_PROGRAM_MR_HEADER_ID => null,
496 X_CANCEL_REASON_CODE => null,
497 X_ATTRIBUTE_CATEGORY => null,
498 X_ATTRIBUTE1 => null,
499 X_ATTRIBUTE2 => null,
500 X_ATTRIBUTE3 => null,
501 X_ATTRIBUTE4 => null,
502 X_ATTRIBUTE5 => null,
503 X_ATTRIBUTE6 => null,
504 X_ATTRIBUTE7 => null,
505 X_ATTRIBUTE8 => null,
506 X_ATTRIBUTE9 => null,
507 X_ATTRIBUTE10 => null,
508 X_ATTRIBUTE11 => null,
509 X_ATTRIBUTE12 => null,
510 X_ATTRIBUTE13 => null,
511 X_ATTRIBUTE14 => null,
512 X_ATTRIBUTE15 => null,
513 X_OBJECT_VERSION_NUMBER => 1,
514 X_CSI_ITEM_INSTANCE_ID => l_customer_product_id,
515 X_MR_HEADER_ID => null,
516 X_MR_EFFECTIVITY_ID => null,
517 X_MR_INTERVAL_ID => null,
518 X_STATUS_CODE => l_ump_status,
519 X_DUE_DATE => l_exp_resolution_date,
520 X_DUE_COUNTER_VALUE => null,
521 X_DEFER_FROM_UE_ID => null,
522 X_ORIG_DEFERRAL_UE_ID => null,
523 X_REMARKS => null,
524 X_CREATION_DATE => sysdate,
525 X_CREATED_BY => fnd_global.user_id,
526 X_LAST_UPDATE_DATE => sysdate,
527 X_LAST_UPDATED_BY => fnd_global.user_id,
528 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
529 -- BACHANDR - NR project start
530 X_FLEET_HEADER_ID => AHL_UMP_UTIL_PKG.Get_Associated_Fleet(l_uc_hdr_id,nvl(l_exp_resolution_date,l_occurred_date),null)
531 -- BACHANDR - NR project end
532 );
533
534 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
536 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
537 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
538 END IF;
539
540 -- End Processing
541
542 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
543 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Create_SR_Unit_Effectivity Procedure');
544 END IF;
545
546 EXCEPTION
547 WHEN FND_API.G_EXC_ERROR THEN
548 ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
552 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
553 END IF;
554
555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556 ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
559 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
560 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
561 END IF;
562
563 WHEN OTHERS THEN
564 ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
566 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
567 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
568 p_procedure_name => 'Create_SR_Unit_Effectivity',
569 p_error_text => SUBSTRB(SQLERRM,1,240));
570 END IF;
571
572 END Create_SR_Unit_Effectivity;
573
574 ----------------------------------------
575 -- Start of Comments --
576 -- Procedure name : Process_SR_Updates
577 -- Type : Private
578 -- Function : Private API to process changes to a (current or former) CMRO type SR
579 -- by adding, removing or updating SR type unit effectivities.
580 -- Called by the corresponding public procedure.
581 -- Uses CS_SERVICEREQUEST_PVT.USER_HOOKS_REC to get SR Details.
582 -- Pre-reqs :
583 -- Parameters :
584 -- x_return_status OUT VARCHAR2 Required
585 --
586 -- Version :
587 -- Initial Version 1.0
588 --
589 -- End of Comments.
590
591 PROCEDURE Process_SR_Updates
592 (
593 x_return_status OUT NOCOPY VARCHAR2) IS
594
595 l_api_name CONSTANT VARCHAR2(30) := 'Process_SR_Updates';
596 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_SR_Updates';
597
598 l_appln_code VARCHAR2(30);
599 l_skip_processing BOOLEAN := false;
600 l_sr_ue_id NUMBER;
601 l_sr_ue_ovn NUMBER;
602 -- added l_defer_from_ue_id to fix bug# 9166304
603 l_defer_from_ue_id NUMBER;
604 BEGIN
605
606 SAVEPOINT Process_SR_Updates_Pvt;
607
608 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
610 END IF;
611
612 -- JR: Added the following log on 10/21/2003 to help in debugging
613 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
615 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
616 ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
617 ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
618 ', status_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id ||
619 ', status_flag = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag ||
620 ', customer_product_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.customer_product_id ||
621 ', exp_resolution_date = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.exp_resolution_date);
622 END IF;
623
624 -- Initialize API return status to success
625 x_return_status := FND_API.G_RET_STS_SUCCESS;
626
627 -- Initialize message list
628 --AMSRINIV. Bug 5470730. Removing message initialization.
629 --FND_MSG_PUB.Initialize;
630
631 -- Begin Processing
632 IF NOT (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG OR
633 NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG)= G_YES_FLAG) THEN
634 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a CMRO Type Service Request.');
636 END IF;
637 -- Not a CMRO Type Service Request
638 -- Just return 'SUCCESS' without doing any processing.
639 l_skip_processing := true;
640 ELSE
641 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'From USER_HOOKS_REC: old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
643 ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag);
644 END IF;
645 END IF;
646
647 IF (l_skip_processing = false) THEN
648 IF (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG) THEN
649 -- Since this is a CMRO type SR, ensure that the Application Usage profile option has been set to AHL
650 AHL_UTIL_PKG.Get_Appln_Usage(x_appln_code => l_appln_code,
651 x_return_status => x_return_status);
652
653 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
654 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
655 FND_MSG_PUB.ADD;
656 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
658 END IF;
659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660 ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
661 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
662 FND_MSG_PUB.ADD;
663 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
665 END IF;
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 ELSE
668 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
670 END IF;
671 END IF; -- Appln_Usage Valid
672 END IF; -- new_type_cmro_flag = 'Y'
673
674 -- First Validate the request
675 Validate_Request_For_Update(x_sr_ue_id => l_sr_ue_id,
676 x_sr_ue_ovn => l_sr_ue_ovn,
677 x_defer_from_ue_id => l_defer_from_ue_id);
678
679 IF (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag, G_NO_FLAG) <> NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG)) THEN
680 -- Handle Type Change
681 Handle_Type_Change(p_sr_ue_id => l_sr_ue_id,
682 p_defer_from_ue_id => l_defer_from_ue_id);
683
684 ELSE
685 -- Handle other attribute (instance, status and resolution date) changes
686 Handle_Attribute_Changes(p_sr_ue_id => l_sr_ue_id);
687 END IF; -- Type change or other change
688 END IF; -- If l_skip_processing = false
689
690 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
692 END IF;
693
694 EXCEPTION
695
696 WHEN FND_API.G_EXC_ERROR THEN
697 ROLLBACK TO Process_SR_Updates_Pvt;
698 x_return_status := FND_API.G_RET_STS_ERROR;
699
700 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
701 ROLLBACK TO Process_SR_Updates_Pvt;
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703
704 WHEN OTHERS THEN
705 ROLLBACK TO Process_SR_Updates_Pvt;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
708 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
709 p_procedure_name => 'Process_SR_Updates',
710 p_error_text => SUBSTRB(SQLERRM,1,240));
711 END IF;
712
713 END Process_SR_Updates;
714
715 ----------------------------------------
716
717 -- Start of Comments --
718 -- Procedure name : Process_SR_MR_Associations
719 -- Type : Private
720 -- Function : Processes new and removed MR associations with a CMRO type SR by
721 -- creating or removing unit effectivities and corresponding relationships.
722 -- Called by the corresponding public procedure.
723 -- Pre-reqs :
724 -- Parameters :
725 --
726 -- Standard IN Parameters :
727 -- p_api_version IN NUMBER Required
728 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
729 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
730 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
731 --
732 -- Standard OUT Parameters :
733 -- x_return_status OUT VARCHAR2 Required
734 -- x_msg_count OUT NUMBER Required
735 -- x_msg_data OUT VARCHAR2 Required
736 --
737 -- Process_SR_MR_Associations Parameters:
738 -- p_user_id IN NUMBER Required
739 -- The Id of the user calling this API
740 -- p_login_id IN NUMBER Required
741 -- The Login Id of the user calling this API
742 -- p_request_id IN NUMBER Required if p_request_number is not given
743 -- The Id of the Service Request
744 -- p_object_version_number IN NUMBER Required
745 -- The object version number of the Service Request
746 -- p_request_number IN VARCHAR2 Required if p_request_id is not given
747 -- The request number of the Service Request
748 -- p_x_sr_mr_association_tbl IN OUT AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type Required
749 -- The Table of records containing the details about the associations and disassociations
750 --
751 -- Version :
752 -- Initial Version 1.0
753 --
754 -- End of Comments.
755
756 PROCEDURE Process_SR_MR_Associations
757 (
758 p_api_version IN NUMBER,
759 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
760 p_commit IN VARCHAR2 := FND_API.G_FALSE,
761 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
762 x_return_status OUT NOCOPY VARCHAR2,
763 x_msg_count OUT NOCOPY NUMBER,
764 x_msg_data OUT NOCOPY VARCHAR2,
765 p_user_id IN NUMBER,
766 p_login_id IN NUMBER,
767 p_request_id IN NUMBER,
768 p_object_version_number IN NUMBER,
769 p_request_number IN VARCHAR2,
770 p_x_sr_mr_association_tbl IN OUT NOCOPY AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type) IS
771
772 l_api_version CONSTANT NUMBER := 1.0;
773 l_api_name CONSTANT VARCHAR2(30) := 'Process_SR_MR_Associations';
774 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_SR_MR_Associations';
775
776 l_appln_code VARCHAR2(30);
777 l_request_id NUMBER := p_request_id;
778 l_sr_ue_id NUMBER := NULL;
779 l_sr_instance_id NUMBER := NULL;
780 l_sr_exp_resol_date DATE := NULL;
781
782 BEGIN
783 -- Standard start of API savepoint
784 SAVEPOINT Process_SR_MR_Associations_pvt;
785
786 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
787 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
788 END IF;
789
790 -- Standard call to check for call compatibility
791 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793 END IF;
794
795 -- Initialize message list if p_init_msg_list is set to TRUE
796 IF FND_API.To_Boolean(p_init_msg_list) THEN
797 FND_MSG_PUB.Initialize;
798 END IF;
799
800 -- Initialize API return status to success
801 x_return_status := FND_API.G_RET_STS_SUCCESS;
802
803 -- Begin Processing
804 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Beginning Processing with p_request_id = ' || p_request_id ||
806 ', p_object_version_number = ' || p_object_version_number ||
807 ' and p_x_sr_mr_association_tbl.COUNT = ' || p_x_sr_mr_association_tbl.COUNT);
808 END IF;
809 -- Check if the Application Usage profile option has been set
810 AHL_UTIL_PKG.Get_Appln_Usage(x_appln_code => l_appln_code,
811 x_return_status => x_return_status);
812
813 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
814 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
815 FND_MSG_PUB.ADD;
816 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
818 END IF;
819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820 ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
821 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
822 FND_MSG_PUB.ADD;
823 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
825 END IF;
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
829 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
830 END IF;
831
832 -- Validate the Request
833 Validate_Associated_Request(p_x_request_id => l_request_id,
834 p_request_number => p_request_number,
835 p_object_version_number => p_object_version_number,
836 x_sr_ue_id => l_sr_ue_id,
837 x_sr_instance_id => l_sr_instance_id,
838 x_sr_exp_resol_date => l_sr_exp_resol_date);
839
840 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Completed Validating the Request');
842 END IF;
843
844 IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
845 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
846 FND_MSG_PUB.ADD;
847 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
849 END IF;
850 RAISE FND_API.G_EXC_ERROR;
851 END IF;
852
853 -- Validate the SR-MR Association records
854 Validate_Association_Records(p_request_id => l_request_id,
855 p_sr_ue_id => l_sr_ue_id,
856 p_sr_instance_id => l_sr_instance_id,
857 p_x_sr_mr_association_tbl => p_x_sr_mr_association_tbl);
858
859 IF (FND_MSG_PUB.Count_Msg > 0) THEN
860 -- There are validation errors: No need to process further
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863
864
865 -- Process all Disassociations First
866 Process_Disassociations(p_sr_ue_id => l_sr_ue_id,
867 p_sr_mr_association_tbl => p_x_sr_mr_association_tbl);
868
869 -- Process all New Associations Next
870 Process_New_Associations(p_sr_ue_id => l_sr_ue_id,
871 p_sr_instance_id => l_sr_instance_id,
872 p_sr_exp_resol_date => l_sr_exp_resol_date,
873 p_user_id => p_user_id,
874 p_login_id => p_login_id,
875 p_x_sr_mr_association_tbl => p_x_sr_mr_association_tbl);
876
877 IF (FND_MSG_PUB.Count_Msg > 0) THEN
878 -- There are validation errors from Process_New_Associations: Raise error
879 RAISE FND_API.G_EXC_ERROR;
880 END IF;
881
882 -- Standard check of p_commit
883 IF FND_API.TO_BOOLEAN(p_commit) THEN
884 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
885 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Commit.');
886 END IF;
887 COMMIT WORK;
888 END IF;
889
890 -- Standard call to get message count and if count is 1, get message info
891 FND_MSG_PUB.Count_And_Get
892 ( p_count => x_msg_count,
893 p_data => x_msg_data,
894 p_encoded => fnd_api.g_false
895 );
896
897 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
899 END IF;
900
901 EXCEPTION
902 WHEN FND_API.G_EXC_ERROR THEN
903 ROLLBACK TO Process_SR_MR_Associations_pvt;
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
906 p_data => x_msg_data,
907 p_encoded => fnd_api.g_false);
908 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
909
910 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
911 ROLLBACK TO Process_SR_MR_Associations_pvt;
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
914 p_data => x_msg_data,
915 p_encoded => fnd_api.g_false);
916 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
917
918 WHEN OTHERS THEN
919 ROLLBACK TO Process_SR_MR_Associations_pvt;
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
922 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
923 p_procedure_name => 'Process_SR_MR_Associations',
924 p_error_text => SUBSTRB(SQLERRM,1,240));
925 END IF;
926 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
927 p_data => x_msg_data,
928 p_encoded => fnd_api.g_false);
929 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
930
931 END Process_SR_MR_Associations;
932
933 ----------------------------------------
934
935 --------------------------------------
936 -- End Public Procedure Definitions --
937 --------------------------------------
938
939 ----------------------------------------
940 -- Local Procedure Definitions follow --
941 ----------------------------------------
942 ----------------------------------------
943 -- This Procedure validates the request for the SR-MR Association API
944 ----------------------------------------
945 PROCEDURE Validate_Associated_Request(
946 p_x_request_id IN OUT NOCOPY NUMBER,
947 p_request_number IN VARCHAR2,
948 p_object_version_number IN NUMBER,
949 x_sr_ue_id OUT NOCOPY NUMBER,
950 x_sr_instance_id OUT NOCOPY NUMBER,
951 x_sr_exp_resol_date OUT NOCOPY DATE) IS
952
953 CURSOR get_request_dtls_csr(p_request_id IN NUMBER,
954 p_request_number IN VARCHAR2,
955 p_object_version_number IN NUMBER) IS
956 /*SELECT incident_id, incident_status_id, closed_flag, customer_product_id, expected_resolution_date
957 FROM CS_INCIDENTS_V
958 WHERE INCIDENT_ID like DECODE(p_request_id, null, '%', p_request_id)
959 AND INCIDENT_NUMBER like NVL(p_request_number, '%')
960 AND OBJECT_VERSION_NUMBER = p_object_version_number;*/
961
962 SELECT INC.incident_id, INC.incident_status_id, NVL(STATUS.CLOSE_FLAG, 'N') closed_flag, INC.customer_product_id, INC.expected_resolution_date
963 FROM CS_INCIDENT_STATUSES_B STATUS,CS_INCIDENTS_ALL_B INC
964 WHERE INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
965 AND INC.INCIDENT_ID like DECODE(p_request_id, null, '%', p_request_id)
966 AND INC.INCIDENT_NUMBER like NVL(p_request_number, '%')
967 AND INC.OBJECT_VERSION_NUMBER = p_object_version_number;
968
969 CURSOR get_ue_dtls_csr(p_request_id IN NUMBER) IS
970 SELECT UNIT_EFFECTIVITY_ID
971 FROM AHL_UNIT_EFFECTIVITIES_APP_V
972 WHERE CS_INCIDENT_ID = p_request_id
973 AND (STATUS_CODE IS NULL OR STATUS_CODE NOT IN (G_UMP_DEFERRED_STATUS, G_UMP_EXCEPTION_STATUS));
974
975 CURSOR get_tasks_for_ue_csr(p_ue_id IN NUMBER) IS
976 SELECT 'x' from AHL_VISIT_TASKS_B
977 where UNIT_EFFECTIVITY_ID = p_ue_id;
978
979 l_sr_status_id NUMBER := NULL;
980 l_closed_flag CS_INCIDENTS_V.CLOSED_FLAG%TYPE := NULL;
981 l_dummy VARCHAR2(1);
982
983 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Associated_Request';
984
985 BEGIN
986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
988 END IF;
989
990 IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
991 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
992 FND_MSG_PUB.ADD;
993 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
995 END IF;
996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997 ELSIF (p_object_version_number IS NULL) THEN
998 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
999 FND_MSG_PUB.ADD;
1000 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1002 END IF;
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 ELSE
1005 OPEN get_request_dtls_csr(p_request_id => p_x_request_id,
1006 p_request_number => p_request_number,
1007 p_object_version_number => p_object_version_number);
1008 FETCH get_request_dtls_csr INTO p_x_request_id,
1009 l_sr_status_id,
1010 l_closed_flag,
1011 x_sr_instance_id, -- OUT Parameter
1012 x_sr_exp_resol_date; -- OUT Parameter
1013 IF get_request_dtls_csr%NOTFOUND THEN
1014 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_INVALID');
1015 FND_MESSAGE.Set_Token('ID', p_x_request_id);
1016 FND_MESSAGE.Set_Token('OVN', p_object_version_number);
1017 FND_MSG_PUB.ADD;
1018 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1020 END IF;
1021 CLOSE get_request_dtls_csr;
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024 CLOSE get_request_dtls_csr;
1025 END IF;
1026
1027 IF(l_closed_flag = G_YES_FLAG) THEN
1028 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
1029 FND_MSG_PUB.ADD;
1030 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1031 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1032 END IF;
1033 RAISE FND_API.G_EXC_ERROR;
1034 END IF;
1035 /*
1036 Balaji commented out this cursor validation for OGMA ER : Adding MRs to Non-Routines
1037 Since as per this ER requirements, we are going to allow SR UEs to be modified and plan
1038 new UEs in the hierarchy in a visit.
1039 Please refer the design document for more information.
1040
1041 -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
1042 IF (l_sr_status_id = G_SR_PLANNED_STATUS_ID) THEN
1043 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
1044 FND_MSG_PUB.ADD;
1045 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1046 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1047 END IF;
1048 RAISE FND_API.G_EXC_ERROR;
1049 END IF;
1050 */
1051 OPEN get_ue_dtls_csr(p_request_id => p_x_request_id);
1052 FETCH get_ue_dtls_csr INTO x_sr_ue_id; -- OUT Parameter
1053 IF (get_ue_dtls_csr%NOTFOUND) THEN
1054 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NO_UE_FOR_REQ');
1055 FND_MESSAGE.Set_Token('ID', p_x_request_id);
1056 FND_MSG_PUB.ADD;
1057 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1059 END IF;
1060 CLOSE get_ue_dtls_csr;
1061 RAISE FND_API.G_EXC_ERROR;
1062 END IF;
1063 CLOSE get_ue_dtls_csr;
1064
1065 /*
1066 Balaji commented out this cursor validation for OGMA ER : Adding MRs to Non-Routines
1067 Since as per this ER requirements, we are going to allow SR UEs to be modified and plan
1068 new UEs in the hierarchy in a visit.
1069 Please refer the design document for more information.
1070 OPEN get_tasks_for_ue_csr(p_ue_id => x_sr_ue_id);
1071 FETCH get_tasks_for_ue_csr INTO l_dummy;
1072 IF (get_tasks_for_ue_csr%FOUND) THEN
1073 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
1074 FND_MSG_PUB.ADD;
1075 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1077 END IF;
1078 CLOSE get_tasks_for_ue_csr;
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081 CLOSE get_tasks_for_ue_csr;
1082 */
1083 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1085 END IF;
1086 END Validate_Associated_Request;
1087
1088 ----------------------------------------
1089 -- This Procedure validates SR-MR Association Records
1090 ----------------------------------------
1091 PROCEDURE Validate_Association_Records(
1092 p_request_id IN NUMBER,
1093 p_sr_ue_id IN NUMBER,
1094 p_sr_instance_id IN NUMBER,
1095 p_x_sr_mr_association_tbl IN OUT NOCOPY SR_MR_Association_Tbl_Type) IS
1096
1097 CURSOR validate_mr_ue_csr(p_mr_ue_id IN NUMBER,
1098 p_mr_ue_ovn IN NUMBER,
1099 p_sr_ue_id IN NUMBER) IS
1100 SELECT 'x'
1101 FROM AHL_UNIT_EFFECTIVITIES_APP_V UE
1102 WHERE UE.UNIT_EFFECTIVITY_ID = p_mr_ue_id AND
1103 UE.OBJECT_VERSION_NUMBER = p_mr_ue_ovn AND
1104 EXISTS (SELECT 'x' from AHL_UE_RELATIONSHIPS UR
1105 WHERE UR.UE_ID = p_sr_ue_id AND
1106 UR.RELATED_UE_ID = p_mr_ue_id AND
1107 UR.relationship_code = G_UE_PARENT_REL_CODE);
1108
1109 CURSOR validate_mr_id_csr(p_mr_header_id IN NUMBER) IS
1110 SELECT TITLE, PROGRAM_TYPE_CODE
1111 FROM AHL_MR_HEADERS_APP_V
1112 WHERE MR_HEADER_ID = p_mr_header_id;
1113
1114 CURSOR validate_instance_id_csr(p_csi_instance_id IN NUMBER) IS
1115 SELECT INSTANCE_NUMBER
1116 FROM CSI_ITEM_INSTANCES
1117 WHERE INSTANCE_ID = p_csi_instance_id;
1118
1119 --amsriniv. adding cursor to check if the instances on which the MRs are applicable are part of the SR Instance tree. ER 5883257
1120 CURSOR validate_sr_mr_intance_rel(c_mr_instance_id IN NUMBER,c_sr_instance_id IN NUMBER) IS
1121 select 'X'
1122 from csi_ii_relationships
1123 where subject_id = c_mr_instance_id
1124 start with object_id = c_sr_instance_id
1125 and RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1126 and trunc(nvl(ACTIVE_START_DATE, sysdate)) <= trunc(sysdate)
1127 and trunc(nvl(ACTIVE_END_DATE, sysdate + 1)) > trunc(sysdate)
1128 connect by prior subject_id = object_id
1129 and RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1130 and trunc(nvl(ACTIVE_START_DATE, sysdate)) <= trunc(sysdate)
1131 and trunc(nvl(ACTIVE_END_DATE, sysdate + 1)) > trunc(sysdate)
1132 UNION ALL
1133 select 'X'
1134 from csi_item_instances
1135 where instance_id = c_sr_instance_id
1136 and instance_id = c_mr_instance_id;
1137
1138 l_dummy VARCHAR2(1);
1139 l_mr_header_id NUMBER := NULL;
1140 l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE := NULL;
1141 l_mr_version_number NUMBER := NULL;
1142 l_valid BOOLEAN;
1143 l_flag_sr_mr_inst VARCHAR2(1); --amsriniv ER 5883257
1144 l_temp_return_status VARCHAR2(1);
1145 l_temp_msg_count NUMBER;
1146 l_temp_msg_data VARCHAR2(2000);
1147 l_applicable_mr_tbl AHL_FMP_PVT.APPLICABLE_MR_TBL_TYPE;
1148 l_mr_applicable BOOLEAN;
1149
1150 l_program_type_code AHL_MR_HEADERS_B.PROGRAM_TYPE_CODE%TYPE;
1151
1152 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Association_Records';
1153
1154 BEGIN
1155 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1157 END IF;
1158 FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1159 l_valid := true;
1160 IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG NOT IN (G_OPR_CREATE, G_OPR_DELETE)) THEN
1161 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_OPR_INVALID');
1162 FND_MESSAGE.Set_Token('OPR_FLAG', p_x_sr_mr_association_tbl(i).OPERATION_FLAG);
1163 FND_MESSAGE.Set_Token('INDEX', i);
1164 FND_MSG_PUB.ADD;
1165 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1167 END IF;
1168 l_valid := false;
1169 ELSIF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1170 -- Delete Operation
1171 Get_MR_UnitEffectivity(p_sr_ue_id => p_sr_ue_id,
1172 p_x_sr_mr_association_rec => p_x_sr_mr_association_tbl(i));
1173 IF (p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID IS NULL) THEN
1174 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_ID_NULL');
1175 FND_MESSAGE.Set_Token('INDEX', i);
1176 FND_MSG_PUB.ADD;
1177 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1178 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1179 END IF;
1180 l_valid := false;
1181 ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1182 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1183 FND_MESSAGE.Set_Token('INDEX', i);
1184 FND_MSG_PUB.ADD;
1185 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1186 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1187 END IF;
1188 l_valid := false;
1189 ELSE
1190 OPEN validate_mr_ue_csr(p_mr_ue_id => p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID,
1191 p_mr_ue_ovn => p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER,
1192 p_sr_ue_id => p_sr_ue_id);
1193 FETCH validate_mr_ue_csr INTO l_dummy;
1194 IF (validate_mr_ue_csr%NOTFOUND) THEN
1195 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_INVALID');
1196 FND_MESSAGE.Set_Token('UE_ID', p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1197 FND_MESSAGE.Set_Token('OVN', p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1198 FND_MESSAGE.Set_Token('INDEX', i);
1199 FND_MSG_PUB.ADD;
1200 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1201 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1202 END IF;
1203 l_valid := false;
1204 END IF; -- UE Id, OVN Not valid
1205 CLOSE validate_mr_ue_csr;
1206 END IF; -- UE Id, OVN Not null
1207 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208 IF (l_valid) THEN
1209 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1210 ELSE
1211 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1212 END IF;
1213 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1214 ', UE OVN: ' || p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1215 END IF; -- Log Statement
1216 ELSE
1217 -- Create Operation
1218 Get_New_Asso_Val_To_Id(p_x_sr_mr_association_rec => p_x_sr_mr_association_tbl(i));
1219 IF (p_x_sr_mr_association_tbl(i).MR_HEADER_ID IS NULL) THEN
1220 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_INVALID');
1221 FND_MESSAGE.Set_Token('TITLE', p_x_sr_mr_association_tbl(i).MR_TITLE);
1222 FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1223 FND_MESSAGE.Set_Token('INDEX', i);
1224 FND_MSG_PUB.ADD;
1225 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1226 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1227 END IF;
1228 l_valid := false;
1229 ELSIF (p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID IS NULL) THEN
1230 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NUM_INVALID');
1231 FND_MESSAGE.Set_Token('INST_NUM', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_NUMBER);
1232 FND_MESSAGE.Set_Token('INDEX', i);
1233 FND_MSG_PUB.ADD;
1234 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1236 END IF;
1237 l_valid := false;
1238 ELSE
1239 -- Validate the MR Header Id
1240 OPEN validate_mr_id_csr(p_mr_header_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1241 FETCH validate_mr_id_csr INTO p_x_sr_mr_association_tbl(i).MR_TITLE, l_program_type_code;
1242 IF (validate_mr_id_csr%NOTFOUND) THEN
1243 CLOSE validate_mr_id_csr;
1244 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_ID_INVALID');
1245 FND_MESSAGE.Set_Token('MR_ID', p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1246 FND_MESSAGE.Set_Token('INDEX', i);
1247 FND_MSG_PUB.ADD;
1248 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1249 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1250 END IF;
1251 l_valid := false;
1252 ELSE
1253 CLOSE validate_mr_id_csr;
1254 -- Validate the Instance Id
1255 OPEN validate_instance_id_csr(p_csi_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1256 FETCH validate_instance_id_csr INTO p_x_sr_mr_association_tbl(i).CSI_INSTANCE_NUMBER;
1257 IF (validate_instance_id_csr%NOTFOUND) THEN
1258 CLOSE validate_instance_id_csr;
1259 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_ID_INVALID');
1260 FND_MESSAGE.Set_Token('INST_ID', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1261 FND_MESSAGE.Set_Token('INDEX', i);
1262 FND_MSG_PUB.ADD;
1263 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1264 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1265 END IF;
1266 l_valid := false;
1267 ELSE
1268 CLOSE validate_instance_id_csr;
1269 --amsriniv. adding cursor to check if the instances on which the MRs are applicable are part of the SR Instance tree. ER 5883257
1270 --Validate SR-MR instance associations
1271 OPEN validate_sr_mr_intance_rel(c_mr_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,c_sr_instance_id => p_sr_instance_id);
1272 FETCH validate_sr_mr_intance_rel INTO l_flag_sr_mr_inst;
1273 IF (validate_sr_mr_intance_rel%NOTFOUND) THEN
1274 CLOSE validate_sr_mr_intance_rel;
1275 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_INVALID');
1276 FND_MESSAGE.Set_Token('TITLE', p_x_sr_mr_association_tbl(i).MR_TITLE);
1277 FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1278 FND_MESSAGE.Set_Token('INDEX', i);
1279 FND_MSG_PUB.ADD;
1280 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1282 END IF;
1283 l_valid := false;
1284 ELSE
1285 CLOSE validate_sr_mr_intance_rel;
1286 --amsriniv
1287 IF (l_program_type_code <> 'MO_PROC') THEN
1288 -- Check MR - Instance Applicability by calling FMP API
1289 AHL_FMP_PVT.GET_APPLICABLE_MRS(p_api_version => 1.0,
1290 x_return_status => l_temp_return_status,
1291 x_msg_count => l_temp_msg_count,
1292 x_msg_data => l_temp_msg_data,
1293 p_item_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1294 p_mr_header_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1295 x_applicable_mr_tbl => l_applicable_mr_tbl);
1296 IF (l_temp_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1297 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_FAILED');
1298 FND_MESSAGE.Set_Token('INDEX', i);
1299 FND_MSG_PUB.ADD;
1300 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1302 END IF;
1303 l_valid := false;
1304 ELSE
1305 l_mr_applicable := false;
1306 IF (l_applicable_mr_tbl.COUNT > 0) THEN
1307 FOR j IN l_applicable_mr_tbl.FIRST .. l_applicable_mr_tbl.LAST LOOP
1308 IF ((l_applicable_mr_tbl(j).MR_HEADER_ID = p_x_sr_mr_association_tbl(i).MR_HEADER_ID) AND
1309 (l_applicable_mr_tbl(j).ITEM_INSTANCE_ID = p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID))THEN
1310 l_mr_applicable := true;
1311 EXIT;
1312 END IF; -- Applicable
1313 END LOOP; -- All Applicable MRs
1314 END IF; -- Table Count > 0
1315 IF (l_mr_applicable = false) THEN
1316 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_NOT_APPLCBL');
1317 FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1318 FND_MSG_PUB.ADD;
1319 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1320 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1321 END IF;
1322 l_valid := false;
1323 END IF; -- MR applicable
1324 END IF; -- GET_APPLICABLE_MRS successful
1325 END IF; --amsriniv
1326 end if; -- program_type_code
1327 END IF; -- Instance Id Valid
1328 END IF; -- MR Header Id Valid
1329 END IF; -- MR Id and Instance Id Not null
1330 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331 IF (l_valid) THEN
1332 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1333 ELSE
1334 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1335 END IF;
1336 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: MR Header ID: ' || p_x_sr_mr_association_tbl(i).MR_HEADER_ID ||
1337 ', CSI Instance ID: ' || p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1338 END IF; -- Log Statement
1339 END IF; -- Valid Operation flag
1340 END LOOP; -- All Association/Disassociation Records
1341
1342 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1343 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1344 END IF;
1345 END Validate_Association_Records;
1346
1347 ----------------------------------------
1348 -- This Procedure deletes SR-MR associations
1349 ----------------------------------------
1350 PROCEDURE Process_Disassociations(
1351 p_sr_ue_id IN NUMBER,
1352 p_sr_mr_association_tbl IN SR_MR_Association_Tbl_Type) IS
1353
1354 CURSOR get_relationship_id_csr(p_sr_ue_id IN NUMBER,
1355 p_mr_ue_id IN NUMBER) IS
1356 SELECT UE_RELATIONSHIP_ID
1357 FROM AHL_UE_RELATIONSHIPS
1358 WHERE UE_ID = p_sr_ue_id AND
1359 RELATED_UE_ID = p_mr_ue_id AND
1360 RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE;
1361
1362 CURSOR get_rel_dtls_csr(p_mr_ue_id IN NUMBER) IS
1363 SELECT UE_RELATIONSHIP_ID, RELATED_UE_ID, level
1364 FROM AHL_UE_RELATIONSHIPS
1365 START WITH UE_ID = p_mr_ue_id
1366 CONNECT BY PRIOR RELATED_UE_ID = UE_ID
1367 ORDER BY LEVEL DESC; /* Bottom Up */
1368
1369 l_relationship_id NUMBER;
1370 l_temp_rel_id NUMBER;
1371 l_temp_ue_id NUMBER;
1372 l_temp_level NUMBER;
1373
1374 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Disassociations';
1375
1376 BEGIN
1377 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1379 END IF;
1380
1381 FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1382 IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1383 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Disassociate Record with index ' || i);
1385 END IF;
1386 OPEN get_relationship_id_csr(p_sr_ue_id => p_sr_ue_id,
1387 p_mr_ue_id => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1388 FETCH get_relationship_id_csr INTO l_relationship_id;
1389 CLOSE get_relationship_id_csr;
1390
1391 -- Delete the Dependents (If Group MR)
1392 FOR l_rel_dtls_rec IN get_rel_dtls_csr(p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID) LOOP
1393 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1394 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_rel_dtls_rec.UE_RELATIONSHIP_ID || ' for a Group MR');
1395 END IF;
1396 AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1397
1398 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1399 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || l_rel_dtls_rec.RELATED_UE_ID || ' for a Group MR');
1400 END IF;
1401 AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1402 END LOOP;
1403 -- Delete the MR Relationship and UE
1404 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1405 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1406 END IF;
1407 AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1408
1409 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1410 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1411 END IF;
1412 AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1413
1414 END IF; -- Disassociation Record
1415 END LOOP; -- All Association/Disassociation Records
1416
1417 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1418 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1419 END IF;
1420 END Process_Disassociations;
1421
1422 ----------------------------------------
1423 -- This Procedure Creates New SR-MR associations
1424 ----------------------------------------
1425 PROCEDURE Process_New_Associations(
1426 p_sr_ue_id IN NUMBER,
1427 p_sr_instance_id IN NUMBER,
1428 p_sr_exp_resol_date IN DATE,
1429 p_user_id IN NUMBER,
1430 p_login_id IN NUMBER,
1431 p_x_sr_mr_association_tbl IN OUT NOCOPY SR_MR_Association_Tbl_Type) IS
1432
1433 CURSOR get_dup_mrs_csr(p_mr_id IN NUMBER,
1434 p_instance_id IN NUMBER,
1435 p_sr_ue_id IN NUMBER) IS
1436 SELECT 'x' from AHL_UNIT_EFFECTIVITIES_B ue
1437 WHERE ue.mr_header_id = p_mr_id AND
1438 ue.csi_item_instance_id = p_instance_id AND
1439 EXISTS (SELECT 'x' FROM ahl_ue_relationships ur
1440 WHERE ur.related_ue_id = ue.unit_effectivity_id
1441 START WITH ur.ue_id = p_sr_ue_id
1442 CONNECT BY PRIOR ur.related_ue_id = ur.ue_id);
1443
1444 --pdoki added for ER 9583373
1445 Cursor Chk_UE_Init_Due (p_mr_header_id NUMBER, p_instance_id NUMBER)
1446 IS
1447 SELECT 'x'
1448 FROM AHL_UNIT_EFFECTIVITIES_B UE,
1449 AHL_MR_HEADERS_B MRH
1450 WHERE UE.mr_header_id = p_mr_header_id
1451 AND MRH.mr_header_id = UE.mr_header_id
1452 AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','OPTIONAL_IMPLEMENT')
1453 AND UE.csi_item_instance_id = p_instance_id
1454 AND UE.status_code = 'INIT-DUE';
1455
1456 l_mr_title VARCHAR2(80);
1457
1458 -- Cursor added for ER # 6123671.
1459 -- Start changes for ER -- 6123671
1460 CURSOR c_get_origin_wo_id(p_sr_ue_id IN NUMBER)
1461 IS
1462 SELECT
1463 ORIGINATING_WO_ID
1464 FROM
1465 AHL_UNIT_EFFECTIVITIES_B
1466 WHERE
1467 UNIT_EFFECTIVITY_ID = p_sr_ue_id
1468 AND OBJECT_TYPE = 'SR';
1469
1470 l_origin_wo_id NUMBER;
1471 -- End changes for ER -- 6123671
1472
1473 CURSOR get_num_descendents_csr(p_mr_id IN NUMBER) IS
1474 SELECT COUNT(*) from AHL_MR_RELATIONSHIPS
1475 WHERE MR_HEADER_ID = p_mr_id;
1476
1477 -- bachandr Bug # 13916897 - start
1478 CURSOR c_get_sr_fleet(p_sr_ue_id IN NUMBER) IS
1479 SELECT fleet_header_id FROM AHL_UNIT_EFFECTIVITIES_B
1480 WHERE unit_effectivity_id = p_sr_ue_id;
1481
1482 l_fleet_header_id NUMBER;
1483 -- bachandr Bug # 13916897 - end
1484
1485 l_temp_level NUMBER;
1486 l_dummy VARCHAR2(1);
1487 l_valid_flag BOOLEAN := true;
1488 l_mr_ue_id NUMBER;
1489 l_mr_rel_id NUMBER;
1490 l_temp_count NUMBER;
1491
1492 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_New_Associations';
1493
1494 BEGIN
1495 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1496 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1497 END IF;
1498
1499 FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1500 IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1501 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is a Create Record. Checking for Duplicates first.');
1503 END IF;
1504 -- Check for duplicates
1505 OPEN get_dup_mrs_csr(p_mr_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1506 p_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1507 p_sr_ue_id => p_sr_ue_id);
1508 FETCH get_dup_mrs_csr INTO l_dummy;
1509 IF (get_dup_mrs_csr%FOUND) THEN
1510 l_valid_flag := false;
1511 CLOSE get_dup_mrs_csr;
1512 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1513 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' with MR Id ' ||
1514 p_x_sr_mr_association_tbl(i).MR_HEADER_ID || ' and Instance Id ' ||
1515 p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID || ' is a Duplicate.');
1516 END IF;
1517 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1518 FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1519 FND_MSG_PUB.ADD;
1520 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1522 END IF;
1523 ELSE
1524 CLOSE get_dup_mrs_csr;
1525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Duplicate. About to create MR UE and UE Relationship.');
1527 END IF;
1528
1529 --pdoki added for ER 9583373
1530 OPEN Chk_UE_Init_Due (p_x_sr_mr_association_tbl(i).MR_HEADER_ID, p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1531 FETCH Chk_UE_Init_Due INTO l_dummy;
1532 IF (Chk_UE_Init_Due%FOUND) THEN
1533 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_CANNOT_ADD_MRS');
1534 FND_MESSAGE.SET_TOKEN('MR_TITLE',p_x_sr_mr_association_tbl(i).MR_TITLE);
1535 FND_MSG_PUB.ADD;
1536 END IF;
1537 CLOSE Chk_UE_Init_Due;
1538
1539 -- bachandr Bug # 13916897 - start
1540 OPEN c_get_sr_fleet(p_sr_ue_id);
1541 FETCH c_get_sr_fleet INTO l_fleet_header_id;
1542 CLOSE c_get_sr_fleet;
1543 -- bachandr Bug # 13916897 - end
1544
1545 Create_MR_Unit_Effectivity(p_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1546 p_due_date => p_sr_exp_resol_date,
1547 p_mr_header_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1548 p_user_id => p_user_id,
1549 p_login_id => p_login_id,
1550 p_fleet_header_id => l_fleet_header_id, -- Bug # 13916897
1551 x_ue_id => l_mr_ue_id);
1552
1553 Create_UE_Relationship(p_ue_id => p_sr_ue_id,
1554 p_related_ue_id => l_mr_ue_id,
1555 p_relationship_code => G_UE_PARENT_REL_CODE,
1556 p_originator_id => p_sr_ue_id,
1557 p_user_id => p_user_id,
1558 p_login_id => p_login_id,
1559 x_ue_rel_id => l_mr_rel_id);
1560
1561 p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID := l_mr_ue_id;
1562 p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER := 1;
1563 p_x_sr_mr_association_tbl(i).UE_RELATIONSHIP_ID := l_mr_rel_id;
1564
1565 OPEN get_num_descendents_csr(p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1566 FETCH get_num_descendents_csr INTO l_temp_count;
1567 CLOSE get_num_descendents_csr;
1568
1569 IF(l_valid_flag = true AND l_temp_count > 0) THEN
1570 -- Process Group MR
1571 Process_Group_MR(p_mr_header_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1572 p_csi_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1573 p_mr_ue_id => l_mr_ue_id,
1574 p_sr_ue_id => p_sr_ue_id,
1575 p_due_date => p_sr_exp_resol_date,
1576 p_user_id => p_user_id,
1577 p_login_id => p_login_id,
1578 p_fleet_header_id => l_fleet_header_id, -- Bug # 13916897
1579 p_x_valid_flag => l_valid_flag);
1580
1581 END IF;
1582 END IF; -- Duplicate MR Check
1583 ELSE
1584 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1585 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Create Record. Skipping.');
1586 END IF;
1587 END IF; -- New Association Record
1588 END LOOP; -- All Association/Disassociation Records
1589
1590 -- Balaji added the code for ER # 6123671
1591 -- UEs corresponding to new MRs added to the SR need to inherit the
1592 -- Originating Work Order Id of the SR UE. This code need to be here because
1593 -- MRs can be added independent of Production Non Routine API.
1594 -- Start Changes ER # 6123671
1595 OPEN c_get_origin_wo_id(p_sr_ue_id);
1596 FETCH c_get_origin_wo_id INTO l_origin_wo_id;
1597 CLOSE c_get_origin_wo_id;
1598
1599 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FND_LOG.STRING(
1601 FND_LOG.LEVEL_STATEMENT,
1602 L_DEBUG_KEY,
1603 'Before updating Originating Work Order Id');
1604 FND_LOG.STRING(
1605 FND_LOG.LEVEL_STATEMENT,
1606 L_DEBUG_KEY,
1607 'p_sr_ue_id->'||p_sr_ue_id||' , '||'SR origin_wo_id->'||l_origin_wo_id);
1608 END IF;
1609
1610 IF p_sr_ue_id IS NOT NULL AND l_origin_wo_id IS NOT NULL
1611 THEN
1612 BEGIN
1613 UPDATE
1614 AHL_UNIT_EFFECTIVITIES_B
1615 SET
1616 ORIGINATING_WO_ID = l_origin_wo_id
1617 WHERE
1618 UNIT_EFFECTIVITY_ID IN (
1619 SELECT
1620 RELATED_UE_ID
1621 FROM
1622 AHL_UE_RELATIONSHIPS
1623 WHERE
1624 ORIGINATOR_UE_ID = p_sr_ue_id
1625 )
1626 AND ORIGINATING_WO_ID IS NULL;
1627
1628 EXCEPTION
1629
1630 WHEN OTHERS THEN
1631 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_ORIGINWO_UPD_FAILED');
1632 Fnd_Msg_Pub.ADD;
1633 END;
1634 END IF;
1635 -- End Changes ER # 6123671
1636
1637 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1638 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1639 END IF;
1640 END Process_New_Associations;
1641
1642 ----------------------------------------
1643 -- This Procedure Gets the Unit Effectivity Id
1644 ----------------------------------------
1645 PROCEDURE Get_MR_UnitEffectivity(
1646 p_sr_ue_id IN NUMBER,
1647 p_x_sr_mr_association_rec IN OUT NOCOPY SR_MR_Association_Rec_Type) IS
1648
1649 CURSOR get_ue_id_csr(p_mr_title IN VARCHAR2,
1650 p_mr_version IN NUMBER,
1651 p_mr_header_id IN NUMBER,
1652 p_csi_instance_number IN VARCHAR2,
1653 p_csi_instance_id IN NUMBER,
1654 p_sr_ue_id IN NUMBER) IS
1655 /* AMSRINIV : Bug 5208411 : Below Query Tuned */
1656
1657 /* SELECT UE.UNIT_EFFECTIVITY_ID
1658 FROM AHL_UNIT_EFFECTIVITIES_APP_V UE
1659 WHERE UE.MR_HEADER_ID = (SELECT MR_HEADER_ID FROM AHL_MR_HEADERS_APP_V
1660 WHERE MR_HEADER_ID like DECODE(p_mr_header_id, null, '%', p_mr_header_id) AND
1661 TITLE like DECODE(p_mr_header_id, null, p_mr_title, '%') AND
1662 VERSION_NUMBER like DECODE(p_mr_header_id, null, p_mr_version, '%')) AND
1663 VERSION_NUMBER like DECODE(p_mr_header_id, null, '' || p_mr_version || '', '%')) AND
1664 UE.CSI_ITEM_INSTANCE_ID = (SELECT INSTANCE_ID FROM CSI_ITEM_INSTANCES
1665 WHERE INSTANCE_ID like DECODE(p_csi_instance_id, null, '%', p_csi_instance_id) AND
1666 INSTANCE_NUMBER like DECODE(p_csi_instance_id, null, p_csi_instance_number, '%')) AND
1667 EXISTS (SELECT 'x' FROM AHL_UE_RELATIONSHIPS
1668 WHERE UE_ID = p_sr_ue_id AND
1669 RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID AND
1670 RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE);*/
1671
1672 /* AMSRINIV : Bug 5208411 : Tuned query */
1673 SELECT UE.UNIT_EFFECTIVITY_ID
1674 FROM AHL_UNIT_EFFECTIVITIES_APP_V UE
1675 WHERE UE.MR_HEADER_ID =
1676 (SELECT MR_HEADER_ID
1677 FROM AHL_MR_HEADERS_APP_V
1678 WHERE MR_HEADER_ID = NVL(p_mr_header_id,MR_HEADER_ID)
1679 AND TITLE like DECODE(p_mr_header_id, null, p_mr_title, '%')
1680 AND VERSION_NUMBER like DECODE(p_mr_header_id, null, '' || p_mr_version || '', '%')
1681 )
1682 AND UE.CSI_ITEM_INSTANCE_ID =
1683 (SELECT INSTANCE_ID
1684 FROM CSI_ITEM_INSTANCES
1685 WHERE INSTANCE_ID = NVL(p_csi_instance_id,INSTANCE_ID)
1686 AND INSTANCE_NUMBER like DECODE(p_csi_instance_id, null, p_csi_instance_number, '%')
1687 )
1688 AND EXISTS
1689 (SELECT 'x'
1690 FROM AHL_UE_RELATIONSHIPS
1691 WHERE UE_ID = p_sr_ue_id
1692 AND RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID
1693 AND RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE
1694 );
1695
1696 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_MR_UnitEffectivity';
1697 l_get_ue_flag BOOLEAN := true;
1698
1699 BEGIN
1700 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1701 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1702 END IF;
1703
1704 IF(p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
1705 l_get_ue_flag := false; -- No need to get the UE Id: It is already available
1706 END IF;
1707
1708 IF((p_x_sr_mr_association_rec.MR_HEADER_ID IS NULL AND p_x_sr_mr_association_rec.MR_TITLE IS NULL) OR
1709 (p_x_sr_mr_association_rec.MR_HEADER_ID IS NULL AND p_x_sr_mr_association_rec.MR_VERSION IS NULL) OR
1710 (p_x_sr_mr_association_rec.CSI_INSTANCE_ID IS NULL AND p_x_sr_mr_association_rec.CSI_INSTANCE_NUMBER IS NULL)) THEN
1711 l_get_ue_flag := false; -- No need to get the UE Id: There is insufficient info to derive it
1712 END IF;
1713
1714 IF(l_get_ue_flag = true) THEN
1715 OPEN get_ue_id_csr(p_mr_title => p_x_sr_mr_association_rec.MR_TITLE,
1716 p_mr_version => p_x_sr_mr_association_rec.MR_VERSION,
1717 p_mr_header_id => p_x_sr_mr_association_rec.MR_HEADER_ID,
1718 p_csi_instance_number => p_x_sr_mr_association_rec.CSI_INSTANCE_NUMBER,
1719 p_csi_instance_id => p_x_sr_mr_association_rec.CSI_INSTANCE_ID,
1720 p_sr_ue_id => p_sr_ue_id);
1721 FETCH get_ue_id_csr INTO p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID;
1722 CLOSE get_ue_id_csr;
1723 END IF;
1724
1725 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1726 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1727 END IF;
1728 END Get_MR_UnitEffectivity;
1729
1730 ----------------------------------------
1731 -- This Procedure does Value to Id Conversion for New Associations
1732 -- It derives the MR Header Id and the CSI Instance Id
1733 ----------------------------------------
1734 PROCEDURE Get_New_Asso_Val_To_Id(
1735 p_x_sr_mr_association_rec IN OUT NOCOPY SR_MR_Association_Rec_Type) IS
1736
1737 CURSOR get_mr_id_csr(p_mr_title IN VARCHAR2,
1738 p_mr_version IN NUMBER) IS
1739 SELECT MR_HEADER_ID
1740 FROM AHL_MR_HEADERS_APP_V
1741 WHERE UPPER(TITLE) = UPPER(p_mr_title) AND
1742 VERSION_NUMBER = p_mr_version;
1743
1744 CURSOR get_csi_instance_id_csr(p_csi_instance_number IN VARCHAR2) IS
1745 SELECT INSTANCE_ID
1746 FROM CSI_ITEM_INSTANCES
1747 WHERE INSTANCE_NUMBER = p_csi_instance_number;
1748
1749 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_New_Asso_Val_To_Id';
1750 l_get_mr_flag BOOLEAN := true;
1751 l_get_inst_flag BOOLEAN := true;
1752
1753 BEGIN
1754 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1755 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1756 END IF;
1757
1758 IF (p_x_sr_mr_association_rec.MR_HEADER_ID IS NOT NULL) THEN
1759 l_get_mr_flag := false; -- No need to get the MR Id: It is already available
1760 END IF;
1761
1762 IF ((p_x_sr_mr_association_rec.MR_HEADER_ID IS NULL AND p_x_sr_mr_association_rec.MR_TITLE IS NULL) OR
1763 (p_x_sr_mr_association_rec.MR_HEADER_ID IS NULL AND p_x_sr_mr_association_rec.MR_VERSION IS NULL)) THEN
1764 l_get_mr_flag := false; -- No need to get the MR Id: There is insufficient info to derive it
1765 END IF;
1766
1767 IF (l_get_mr_flag = true) THEN
1768 OPEN get_mr_id_csr(p_mr_title => p_x_sr_mr_association_rec.MR_TITLE,
1769 p_mr_version => p_x_sr_mr_association_rec.MR_VERSION);
1770 FETCH get_mr_id_csr INTO p_x_sr_mr_association_rec.MR_HEADER_ID;
1771 CLOSE get_mr_id_csr;
1772 END IF;
1773
1774 IF (p_x_sr_mr_association_rec.CSI_INSTANCE_ID IS NOT NULL) THEN
1775 l_get_inst_flag := false; -- No need to get the Instance Id: It is already available
1776 END IF;
1777
1778 IF (p_x_sr_mr_association_rec.CSI_INSTANCE_ID IS NULL AND p_x_sr_mr_association_rec.CSI_INSTANCE_NUMBER IS NULL) THEN
1779 l_get_inst_flag := false; -- No need to get the Instance Id: There is insufficient info to derive it
1780 END IF;
1781
1782 IF (l_get_inst_flag = true) THEN
1783 OPEN get_csi_instance_id_csr(p_csi_instance_number => p_x_sr_mr_association_rec.CSI_INSTANCE_NUMBER);
1784 FETCH get_csi_instance_id_csr INTO p_x_sr_mr_association_rec.CSI_INSTANCE_ID;
1785 CLOSE get_csi_instance_id_csr;
1786 END IF;
1787
1788 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1789 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1790 END IF;
1791 END Get_New_Asso_Val_To_Id;
1792
1793 ----------------------------------------
1794 -- This Procedure creates a new unit effectivity
1795 -- It returns the id of the newly created UE
1796 ----------------------------------------
1797 PROCEDURE Create_MR_Unit_Effectivity(
1798 p_instance_id IN NUMBER,
1799 p_due_date IN DATE,
1800 p_mr_header_id IN NUMBER,
1801 p_user_id IN NUMBER,
1802 p_login_id IN NUMBER,
1803 p_fleet_header_id IN NUMBER,
1804 x_ue_id OUT NOCOPY NUMBER) IS
1805
1806 l_temp_row_id VARCHAR2(30);
1807 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_MR_Unit_Effectivity';
1808
1809 BEGIN
1810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1812 END IF;
1813
1814
1815 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
1816 X_ROWID => l_temp_row_id,
1817 X_UNIT_EFFECTIVITY_ID => x_ue_id,
1818 X_MANUALLY_PLANNED_FLAG => 'Y',
1819 X_LOG_SERIES_CODE => null,
1820 X_LOG_SERIES_NUMBER => null,
1821 X_FLIGHT_NUMBER => null,
1822 X_MEL_CDL_TYPE_CODE => null,
1823 X_POSITION_PATH_ID => null,
1824 X_ATA_CODE => null,
1825 --X_CLEAR_STATION_ORG_ID => null,
1826 --X_CLEAR_STATION_DEPT_ID => null,
1827 X_UNIT_CONFIG_HEADER_ID => null,
1828 X_QA_COLLECTION_ID => null,
1829 X_CS_INCIDENT_ID => null,
1830 X_OBJECT_TYPE => G_UE_MR_OBJECT_TYPE,
1831 X_APPLICATION_USG_CODE => G_APP_MODULE,
1832 X_COUNTER_ID => null,
1833 X_EARLIEST_DUE_DATE => null,
1834 X_LATEST_DUE_DATE => null,
1835 X_FORECAST_SEQUENCE => null,
1836 X_REPETITIVE_MR_FLAG => null,
1837 X_TOLERANCE_FLAG => null,
1838 X_MESSAGE_CODE => null,
1839 X_DATE_RUN => null,
1840 X_PRECEDING_UE_ID => null,
1841 X_SET_DUE_DATE => null,
1842 X_ACCOMPLISHED_DATE => null,
1843 X_SERVICE_LINE_ID => null,
1844 X_PROGRAM_MR_HEADER_ID => null,
1845 X_CANCEL_REASON_CODE => null,
1846 X_ATTRIBUTE_CATEGORY => null,
1847 X_ATTRIBUTE1 => null,
1848 X_ATTRIBUTE2 => null,
1849 X_ATTRIBUTE3 => null,
1850 X_ATTRIBUTE4 => null,
1851 X_ATTRIBUTE5 => null,
1852 X_ATTRIBUTE6 => null,
1853 X_ATTRIBUTE7 => null,
1854 X_ATTRIBUTE8 => null,
1855 X_ATTRIBUTE9 => null,
1856 X_ATTRIBUTE10 => null,
1857 X_ATTRIBUTE11 => null,
1858 X_ATTRIBUTE12 => null,
1859 X_ATTRIBUTE13 => null,
1860 X_ATTRIBUTE14 => null,
1861 X_ATTRIBUTE15 => null,
1862 X_OBJECT_VERSION_NUMBER => 1,
1863 X_CSI_ITEM_INSTANCE_ID => p_instance_id,
1864 X_MR_HEADER_ID => p_mr_header_id,
1865 X_MR_EFFECTIVITY_ID => null,
1866 X_MR_INTERVAL_ID => null,
1867 X_STATUS_CODE => null,
1868 X_DUE_DATE => p_due_date,
1869 X_DUE_COUNTER_VALUE => null,
1870 X_DEFER_FROM_UE_ID => null,
1871 X_ORIG_DEFERRAL_UE_ID => null,
1872 X_REMARKS => null,
1873 X_CREATION_DATE => sysdate,
1874 X_CREATED_BY => fnd_global.user_id,
1875 X_LAST_UPDATE_DATE => sysdate,
1876 X_LAST_UPDATED_BY => fnd_global.user_id,
1877 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1878 -- BACHANDR - NR project start
1879 X_FLEET_HEADER_ID => p_fleet_header_id
1880 -- BACHANDR - NR project end
1881 );
1882
1883 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1884 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1885 END IF;
1886
1887 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1889 END IF;
1890 END Create_MR_Unit_Effectivity;
1891
1892 ----------------------------------------
1893 -- This Procedure creates a new UE Relationship
1894 -- It returns the id of the newly created Relationship
1895 ----------------------------------------
1896 PROCEDURE Create_UE_Relationship(
1897 p_ue_id IN NUMBER,
1898 p_related_ue_id IN NUMBER,
1899 p_relationship_code IN VARCHAR2,
1900 p_originator_id IN NUMBER,
1901 p_user_id IN NUMBER,
1902 p_login_id IN NUMBER,
1903 x_ue_rel_id OUT NOCOPY NUMBER) IS
1904
1905 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_UE_Relationship';
1906
1907 BEGIN
1908 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1909 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1910 END IF;
1911
1912 AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW(
1913 X_UE_RELATIONSHIP_ID => x_ue_rel_id,
1914 X_UE_ID => p_ue_id,
1915 X_RELATED_UE_ID => p_related_ue_id,
1916 X_RELATIONSHIP_CODE => p_relationship_code,
1917 X_ORIGINATOR_UE_ID => p_originator_id,
1918 X_ATTRIBUTE_CATEGORY => null,
1919 X_ATTRIBUTE1 => null,
1920 X_ATTRIBUTE2 => null,
1921 X_ATTRIBUTE3 => null,
1922 X_ATTRIBUTE4 => null,
1923 X_ATTRIBUTE5 => null,
1924 X_ATTRIBUTE6 => null,
1925 X_ATTRIBUTE7 => null,
1926 X_ATTRIBUTE8 => null,
1927 X_ATTRIBUTE9 => null,
1928 X_ATTRIBUTE10 => null,
1929 X_ATTRIBUTE11 => null,
1930 X_ATTRIBUTE12 => null,
1931 X_ATTRIBUTE13 => null,
1932 X_ATTRIBUTE14 => null,
1933 X_ATTRIBUTE15 => null,
1934 X_OBJECT_VERSION_NUMBER => 1,
1935 X_LAST_UPDATE_DATE => SYSDATE,
1936 X_LAST_UPDATED_BY => p_user_id,
1937 X_CREATION_DATE => SYSDATE,
1938 X_CREATED_BY => p_user_id,
1939 X_LAST_UPDATE_LOGIN => p_login_id);
1940
1941 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1942 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1943 END IF;
1944
1945 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1947 END IF;
1948 END Create_UE_Relationship;
1949
1950 ----------------------------------------
1951 -- This Procedure processes group MRs
1952 -- It creates a hierarchy of unit effectivities and UE relationships
1953 -- If any descendent MR is already present for the SR, the p_x_valid_flag
1954 -- is set to false and the error message is added to the stack.
1955 ----------------------------------------
1956 PROCEDURE Process_Group_MR(
1957 p_mr_header_id IN NUMBER,
1958 p_csi_instance_id IN NUMBER,
1959 p_mr_ue_id IN NUMBER,
1960 p_sr_ue_id IN NUMBER,
1961 p_due_date IN DATE,
1962 p_user_id IN NUMBER,
1963 p_login_id IN NUMBER,
1964 p_fleet_header_id IN NUMBER,
1965 p_x_valid_flag IN OUT NOCOPY BOOLEAN) IS
1966
1967 CURSOR get_duplicate_mrs_csr(p_sr_ue_id IN NUMBER) IS
1968 SELECT UE.MR_HEADER_ID, UE.CSI_ITEM_INSTANCE_ID
1969 FROM AHL_UNIT_EFFECTIVITIES_B UE, AHL_UE_RELATIONSHIPS UR
1970 WHERE UR.ORIGINATOR_UE_ID = p_sr_ue_id AND
1971 UR.RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE AND
1972 UE.UNIT_EFFECTIVITY_ID = UR.RELATED_UE_ID
1973 INTERSECT
1974 SELECT RELATED_MR_HEADER_ID, RELATED_CSI_ITEM_INSTANCE_ID
1975 FROM AHL_APPLICABLE_MR_RELNS;
1976
1977 CURSOR mr_relns_upd_csr IS
1978 SELECT * FROM AHL_APPLICABLE_MR_RELNS
1979 FOR UPDATE OF UE_ID;
1980
1981 CURSOR get_mr_reln_dtls_csr(p_mr_ue_id IN NUMBER,
1982 p_mr_header_id IN NUMBER,
1983 p_csi_instance_id IN NUMBER) IS
1984 SELECT child.ue_id child_ue_id, NVL(parent.ue_id, p_mr_ue_id) parent_ue_id, child.relationship_code
1985 FROM AHL_APPLICABLE_MR_RELNS child, AHL_APPLICABLE_MR_RELNS parent
1986 WHERE child.ORIG_MR_HEADER_ID = p_mr_header_id AND -- Filter condition
1987 child.ORIG_CSI_ITEM_INSTANCE_ID = p_csi_instance_id AND -- Filter condition
1988 parent.RELATED_MR_HEADER_ID (+) = child.MR_HEADER_ID AND -- Join condition
1989 parent.RELATED_CSI_ITEM_INSTANCE_ID (+) = child.CSI_ITEM_INSTANCE_ID; -- Join condition
1990
1991 CURSOR get_app_mr_table_count_csr IS
1992 SELECT count(*) from AHL_APPLICABLE_MRS;
1993
1994 /*CURSOR get_relns_table_count_csr IS
1995 SELECT count(*) from AHL_APPLICABLE_MR_RELNS;*/
1996
1997 --pdoki added for ER 9583373
1998 Cursor Chk_UE_Init_Due (p_mr_header_id NUMBER, p_instance_id NUMBER)
1999 IS
2000 SELECT 'x'
2001 FROM AHL_UNIT_EFFECTIVITIES_B UE,
2002 AHL_MR_HEADERS_B MRH
2003 WHERE UE.mr_header_id = p_mr_header_id
2004 AND MRH.mr_header_id = UE.mr_header_id
2005 AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','OPTIONAL_IMPLEMENT')
2006 AND UE.csi_item_instance_id = p_instance_id
2007 AND UE.status_code = 'INIT-DUE';
2008
2009 l_duplicate_mr_id NUMBER;
2010 l_dup_mr_title AHL_MR_HEADERS_B.TITLE%TYPE;
2011 l_curr_mr_title AHL_MR_HEADERS_B.TITLE%TYPE;
2012 l_duplicate_inst_id NUMBER;
2013 l_new_ue_id NUMBER;
2014 l_new_rel_id NUMBER;
2015 l_temp_count NUMBER := 0;
2016 l_temp_return_status VARCHAR2(1);
2017 l_temp_msg_count NUMBER;
2018 l_temp_msg_data VARCHAR2(2000);
2019
2020 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Group_MR';
2021
2022 --pdoki added for ER 9583373
2023 l_dummy VARCHAR2(1);
2024 l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE := NULL;
2025
2026 BEGIN
2027 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2029 END IF;
2030 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
2032 END IF;
2033
2034 -- Evaluate Group MRs
2035 -- Populate AHL_APPLICABLE_MRS if required
2036 OPEN get_app_mr_table_count_csr;
2037 FETCH get_app_mr_table_count_csr INTO l_temp_count;
2038 CLOSE get_app_mr_table_count_csr;
2039 IF (l_temp_count > 0) THEN
2040 -- AHL_APPLICABLE_MRS is already populated. No need to Call Populate_Appl_MRs
2041 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2042 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count || '. Not calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs.');
2043 END IF;
2044 ELSE
2045 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2046 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS is zero. Calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs with p_csi_ii_id = ' || p_csi_instance_id);
2047 END IF;
2048 AHL_UMP_UTIL_PKG.Populate_Appl_MRs(p_csi_ii_id => p_csi_instance_id,
2049 x_return_status => l_temp_return_status,
2050 x_msg_count => l_temp_msg_count,
2051 x_msg_data => l_temp_msg_data);
2052 IF NOT (l_temp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2053 p_x_valid_flag := false;
2054 l_curr_mr_title := Get_MR_Title_From_MR_Id(p_mr_header_id);
2055 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GRP_MR_FAILED');
2056 FND_MESSAGE.Set_Token('MR_TITLE', l_curr_mr_title);
2057 FND_MSG_PUB.ADD;
2058 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2059 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2060 END IF;
2061 RETURN; -- No need to process this Group MR further
2062 END IF; -- Populate_Appl_MRs Failed
2063 OPEN get_app_mr_table_count_csr;
2064 FETCH get_app_mr_table_count_csr INTO l_temp_count;
2065 CLOSE get_app_mr_table_count_csr;
2066 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2067 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'After calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs, Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count);
2068 END IF;
2069 END IF; -- AHL_APPLICABLE_MRS temp table is empty
2070
2071 -- Call process_Group_MR_Instance
2072 /*OPEN get_relns_table_count_csr;
2073 FETCH get_relns_table_count_csr INTO l_temp_count;
2074 CLOSE get_relns_table_count_csr;
2075 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2076 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
2077 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_UTIL_PKG.process_Group_MR_Instance with p_top_mr_id = ' ||
2078 p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
2079 END IF;*/
2080 AHL_UMP_UTIL_PKG.process_Group_MR_Instance(p_top_mr_id => p_mr_header_id,
2081 p_top_item_instance_id => p_csi_instance_id,
2082 p_init_temp_table => G_YES_FLAG); -- To clean up temp table first
2083 /*OPEN get_relns_table_count_csr;
2084 FETCH get_relns_table_count_csr INTO l_temp_count;
2085 CLOSE get_relns_table_count_csr;
2086 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2087 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS after calling AHL_UMP_UTIL_PKG.process_Group_MR_Instance: ' || l_temp_count);
2088 END IF;*/
2089
2090 -- Check for duplicates
2091 OPEN get_duplicate_mrs_csr(p_sr_ue_id => p_sr_ue_id);
2092 FETCH get_duplicate_mrs_csr INTO l_duplicate_mr_id, l_duplicate_inst_id;
2093 IF(get_duplicate_mrs_csr%FOUND) THEN
2094 -- At least one duplicate found
2095 CLOSE get_duplicate_mrs_csr;
2096 l_dup_mr_title := Get_MR_Title_From_MR_Id(l_duplicate_mr_id);
2097 l_curr_mr_title := Get_MR_Title_From_MR_Id(p_mr_header_id);
2098 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GROUP_DUP_MR');
2099 FND_MESSAGE.Set_Token('NEW_MR', l_curr_mr_title);
2100 FND_MESSAGE.Set_Token('DUP_MR', l_dup_mr_title);
2101 FND_MSG_PUB.ADD;
2102 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2103 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2104 END IF;
2105 p_x_valid_flag := false;
2106 RETURN; -- No need to process this Group MR further
2107 END IF;
2108 CLOSE get_duplicate_mrs_csr;
2109
2110 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2111 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding this Group MR will not cause any duplicates.');
2112 END IF;
2113
2114 -- First create all the Unit Effectivities
2115 l_temp_count := 0;
2116 FOR mr_rel_rec IN mr_relns_upd_csr LOOP
2117
2118 --pdoki added for ER 9583373
2119 OPEN Chk_UE_Init_Due (mr_rel_rec.RELATED_MR_HEADER_ID, mr_rel_rec.RELATED_CSI_ITEM_INSTANCE_ID);
2120 FETCH Chk_UE_Init_Due INTO l_dummy;
2121 IF (Chk_UE_Init_Due%FOUND) THEN
2122 --pick the MR Title
2123 SELECT title
2124 INTO l_mr_title
2125 FROM AHL_MR_HEADERS_B
2126 WHERE mr_header_id = mr_rel_rec.RELATED_MR_HEADER_ID;
2127
2128 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_CANNOT_ADD_MRS');
2129 FND_MESSAGE.SET_TOKEN('MR_TITLE',l_mr_title);
2130 FND_MSG_PUB.ADD;
2131 p_x_valid_flag := false;
2132 CLOSE Chk_UE_Init_Due;
2133 RETURN; -- No need to process this Group MR further
2134 END IF;
2135 CLOSE Chk_UE_Init_Due;
2136
2137 -- Create the new Unit Effectivity Record
2138 Create_MR_Unit_Effectivity(p_instance_id => mr_rel_rec.RELATED_CSI_ITEM_INSTANCE_ID,
2139 p_due_date => p_due_date,
2140 p_mr_header_id => mr_rel_rec.RELATED_MR_HEADER_ID,
2141 p_user_id => p_user_id,
2142 p_login_id => p_login_id,
2143 p_fleet_header_id => p_fleet_header_id, -- Bug # 13916897
2144 x_ue_id => l_new_ue_id);
2145
2146 l_temp_count := l_temp_count + 1;
2147 -- Update AHL_APPLICABLE_MR_RELNS with the new UE Id
2148 UPDATE AHL_APPLICABLE_MR_RELNS
2149 SET UE_ID = l_new_ue_id
2150 WHERE CURRENT OF mr_relns_upd_csr;
2151 END LOOP;
2152 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2153 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' Unit Effectivities for the Group MR.');
2154 END IF;
2155
2156 -- Next, Create the UE Relationships
2157 l_temp_count := 0;
2158 FOR rel_dtls_rec IN get_mr_reln_dtls_csr(p_mr_ue_id => p_mr_ue_id,
2159 p_mr_header_id => p_mr_header_id,
2160 p_csi_instance_id => p_csi_instance_id) LOOP
2161
2162 Create_UE_Relationship(p_ue_id => rel_dtls_rec.parent_ue_id,
2163 p_related_ue_id => rel_dtls_rec.child_ue_id,
2164 p_relationship_code => rel_dtls_rec.relationship_code,
2165 p_originator_id => p_sr_ue_id,
2166 p_user_id => p_user_id,
2167 p_login_id => p_login_id,
2168 x_ue_rel_id => l_new_rel_id);
2169
2170 l_temp_count := l_temp_count + 1;
2171
2172 END LOOP;
2173 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2175 END IF;
2176
2177 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2178 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2179 END IF;
2180 END Process_Group_MR;
2181
2182 ----------------------------------------
2183 -- This Function gets the MR Title from the MR Header Id
2184 ----------------------------------------
2185 FUNCTION Get_MR_Title_From_MR_Id(p_mr_header_id IN NUMBER)
2186 RETURN VARCHAR2 IS
2187
2188 CURSOR get_mr_title_csr IS
2189 SELECT TITLE
2190 FROM AHL_MR_HEADERS_B
2191 WHERE MR_HEADER_ID = p_mr_header_id;
2192
2193 l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE := null;
2194
2195 BEGIN
2196 OPEN get_mr_title_csr;
2197 FETCH get_mr_title_csr INTO l_mr_title;
2198 CLOSE get_mr_title_csr;
2199 RETURN l_mr_title;
2200 END Get_MR_Title_From_MR_Id;
2201
2202 ----------------------------------------
2203 -- This Function gets the MR Title from the Unit Effectivity Id
2204 ----------------------------------------
2205 FUNCTION Get_MR_Title_From_UE_Id(p_unit_effectivity_id IN NUMBER)
2206 RETURN VARCHAR2 IS
2207
2208 CURSOR get_mr_title_csr IS
2209 SELECT MR.TITLE
2210 FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_APP_V UE
2211 WHERE UE.UNIT_EFFECTIVITY_ID = p_unit_effectivity_id AND
2212 UE.MR_HEADER_ID = MR.MR_HEADER_ID;
2213
2214 l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE := null;
2215
2216 BEGIN
2217 OPEN get_mr_title_csr;
2218 FETCH get_mr_title_csr INTO l_mr_title;
2219 CLOSE get_mr_title_csr;
2220 RETURN l_mr_title;
2221 END Get_MR_Title_From_UE_Id;
2222
2223 ----------------------------------------
2224 -- This Procedure updates the due date and tolerance exceeded flag of UEs
2225 -- of the MRs associated to the SR in response to change in Exp. Resolution Date of the SR
2226 ----------------------------------------
2227 PROCEDURE Handle_MR_UE_Date_Change(
2228 p_sr_ue_id IN NUMBER,
2229 p_assigned_to_visit_flag IN BOOLEAN,
2230 p_new_tolerance_flag IN VARCHAR2,
2231 p_new_due_date IN DATE) IS
2232
2233 CURSOR get_MR_UEs_csr IS
2234 SELECT * FROM AHL_UNIT_EFFECTIVITIES_APP_V
2235 WHERE UNIT_EFFECTIVITY_ID IN (SELECT RELATED_UE_ID from AHL_UE_RELATIONSHIPS
2236 WHERE ORIGINATOR_UE_ID = p_sr_ue_id
2237 AND RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE);
2238
2239 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_MR_UE_Date_Change';
2240
2241 l_temp_tolerance_flag AHL_UNIT_EFFECTIVITIES_B.TOLERANCE_FLAG%TYPE;
2242 l_temp_count NUMBER := 0;
2243
2244
2245 BEGIN
2246 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2247 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2248 END IF;
2249
2250 FOR l_curr_ue_rec in get_MR_UEs_csr LOOP
2251 IF (p_assigned_to_visit_flag = TRUE) THEN
2252 l_temp_tolerance_flag := p_new_tolerance_flag;
2253 ELSE
2254 l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2255 END IF;
2256 -- Update the unit effectivity
2257 AHL_UNIT_EFFECTIVITIES_PKG.UPDATE_ROW(
2258 X_UNIT_EFFECTIVITY_ID => l_curr_ue_rec.UNIT_EFFECTIVITY_ID,
2259 X_MANUALLY_PLANNED_FLAG => l_curr_ue_rec.MANUALLY_PLANNED_FLAG,
2260 X_LOG_SERIES_CODE => l_curr_ue_rec.LOG_SERIES_CODE,
2261 X_LOG_SERIES_NUMBER => l_curr_ue_rec.LOG_SERIES_NUMBER,
2262 X_FLIGHT_NUMBER => l_curr_ue_rec.FLIGHT_NUMBER,
2263 X_MEL_CDL_TYPE_CODE => l_curr_ue_rec.MEL_CDL_TYPE_CODE,
2264 X_POSITION_PATH_ID => l_curr_ue_rec.POSITION_PATH_ID,
2265 X_ATA_CODE => l_curr_ue_rec.ATA_CODE,
2266 --X_CLEAR_STATION_ORG_ID => l_curr_ue_rec.CLEAR_STATION_ORG_ID,
2267 --X_CLEAR_STATION_DEPT_ID => l_curr_ue_rec.CLEAR_STATION_DEPT_ID,
2268 X_UNIT_CONFIG_HEADER_ID => l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,
2269 X_QA_COLLECTION_ID => l_curr_ue_rec.QA_COLLECTION_ID,
2270 X_CS_INCIDENT_ID => l_curr_ue_rec.CS_INCIDENT_ID,
2271 X_OBJECT_TYPE => l_curr_ue_rec.OBJECT_TYPE,
2272 X_APPLICATION_USG_CODE => l_curr_ue_rec.APPLICATION_USG_CODE,
2273 X_COUNTER_ID => l_curr_ue_rec.COUNTER_ID,
2274 X_EARLIEST_DUE_DATE => l_curr_ue_rec.EARLIEST_DUE_DATE,
2275 X_LATEST_DUE_DATE => l_curr_ue_rec.LATEST_DUE_DATE,
2276 X_FORECAST_SEQUENCE => l_curr_ue_rec.FORECAST_SEQUENCE,
2277 X_REPETITIVE_MR_FLAG => l_curr_ue_rec.REPETITIVE_MR_FLAG,
2278 X_TOLERANCE_FLAG => l_temp_tolerance_flag, -- Updated
2279 X_MESSAGE_CODE => l_curr_ue_rec.MESSAGE_CODE,
2280 X_DATE_RUN => l_curr_ue_rec.DATE_RUN,
2281 X_PRECEDING_UE_ID => l_curr_ue_rec.PRECEDING_UE_ID,
2282 X_SET_DUE_DATE => l_curr_ue_rec.SET_DUE_DATE,
2283 X_ACCOMPLISHED_DATE => l_curr_ue_rec.ACCOMPLISHED_DATE,
2284 X_SERVICE_LINE_ID => l_curr_ue_rec.SERVICE_LINE_ID,
2285 X_PROGRAM_MR_HEADER_ID => l_curr_ue_rec.PROGRAM_MR_HEADER_ID,
2286 X_CANCEL_REASON_CODE => l_curr_ue_rec.CANCEL_REASON_CODE,
2287 X_ATTRIBUTE_CATEGORY => l_curr_ue_rec.ATTRIBUTE_CATEGORY,
2288 X_ATTRIBUTE1 => l_curr_ue_rec.ATTRIBUTE1,
2289 X_ATTRIBUTE2 => l_curr_ue_rec.ATTRIBUTE2,
2290 X_ATTRIBUTE3 => l_curr_ue_rec.ATTRIBUTE3,
2291 X_ATTRIBUTE4 => l_curr_ue_rec.ATTRIBUTE4,
2292 X_ATTRIBUTE5 => l_curr_ue_rec.ATTRIBUTE5,
2293 X_ATTRIBUTE6 => l_curr_ue_rec.ATTRIBUTE6,
2294 X_ATTRIBUTE7 => l_curr_ue_rec.ATTRIBUTE7,
2295 X_ATTRIBUTE8 => l_curr_ue_rec.ATTRIBUTE8,
2296 X_ATTRIBUTE9 => l_curr_ue_rec.ATTRIBUTE9,
2297 X_ATTRIBUTE10 => l_curr_ue_rec.ATTRIBUTE10,
2298 X_ATTRIBUTE11 => l_curr_ue_rec.ATTRIBUTE11,
2299 X_ATTRIBUTE12 => l_curr_ue_rec.ATTRIBUTE12,
2300 X_ATTRIBUTE13 => l_curr_ue_rec.ATTRIBUTE13,
2301 X_ATTRIBUTE14 => l_curr_ue_rec.ATTRIBUTE14,
2302 X_ATTRIBUTE15 => l_curr_ue_rec.ATTRIBUTE15,
2303 X_OBJECT_VERSION_NUMBER => l_curr_ue_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
2304 X_CSI_ITEM_INSTANCE_ID => l_curr_ue_rec.CSI_ITEM_INSTANCE_ID,
2305 X_MR_HEADER_ID => l_curr_ue_rec.MR_HEADER_ID,
2306 X_MR_EFFECTIVITY_ID => l_curr_ue_rec.MR_EFFECTIVITY_ID,
2307 X_MR_INTERVAL_ID => l_curr_ue_rec.MR_INTERVAL_ID,
2308 X_STATUS_CODE => l_curr_ue_rec.STATUS_CODE,
2309 X_DUE_DATE => p_new_due_date, -- Updated
2310 X_DUE_COUNTER_VALUE => l_curr_ue_rec.DUE_COUNTER_VALUE,
2311 X_DEFER_FROM_UE_ID => l_curr_ue_rec.DEFER_FROM_UE_ID,
2312 X_ORIG_DEFERRAL_UE_ID => l_curr_ue_rec.ORIG_DEFERRAL_UE_ID,
2313 X_REMARKS => l_curr_ue_rec.REMARKS,
2314 X_LAST_UPDATE_DATE => SYSDATE, -- Updated
2315 X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
2316 X_LAST_UPDATE_LOGIN => fnd_global.login_id, -- Updated
2317 -- BACHANDR - NR project start
2318 X_FLEET_HEADER_ID => AHL_UMP_UTIL_PKG.Get_Associated_Fleet(l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,p_new_due_date,null)
2319 -- BACHANDR - NR project end
2320 );
2321 l_temp_count := l_temp_count + 1;
2322 END LOOP;
2323 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2324 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2325 END IF;
2326
2327 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2329 END IF;
2330 END Handle_MR_UE_Date_Change;
2331
2332 ----------------------------------------
2333 -- This Procedure updates the Unit Effectivity associated with the Service Request.
2334 -- It updates the Status, Instance, Due Date and Tolerance Flag in response to updates in a SR
2335 -- It returns two flags: The first flag indicated if the UE is assigned to a visit.
2336 -- The second flag (which applies only if date has changed and only if assigned to a visit)
2337 -- indicates if date tolerance has been exceeded (Visit Start Date vs. New UE Due Date)
2338 ----------------------------------------
2339 PROCEDURE Update_SR_Unit_Effectivity(
2340 p_sr_ue_id IN NUMBER,
2341 p_due_date_flag IN BOOLEAN,
2342 p_new_due_date IN DATE,
2343 p_instance_flag IN BOOLEAN,
2344 p_new_instance_id IN NUMBER,
2345 p_status_flag IN BOOLEAN,
2346 p_new_status_code IN VARCHAR2,
2347 x_assigned_to_visit_flag OUT NOCOPY BOOLEAN,
2348 x_new_tolerance_flag OUT NOCOPY VARCHAR2) IS
2349
2350 CURSOR get_UE_dtls_csr IS
2351 SELECT * FROM AHL_UNIT_EFFECTIVITIES_APP_V
2352 WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id;
2353
2354 -- bachandr Bug # 13916897
2355 CURSOR c_get_incident_occ_date(c_incident_id IN NUMBER) IS
2356 SELECT incident_occurred_date FROM cs_incidents
2357 WHERE incident_id = c_incident_id;
2358
2359 l_occurred_date DATE;
2360 -- bachandr Bug # 13916897
2361
2362 CURSOR is_ue_assigned_to_visit_csr IS
2363 SELECT 'x' FROM AHL_VISIT_TASKS_B
2364 WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id;
2365
2366 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_SR_Unit_Effectivity';
2367
2368 l_temp_tolerance_flag AHL_UNIT_EFFECTIVITIES_B.TOLERANCE_FLAG%TYPE;
2369 l_temp_instance_id NUMBER;
2370 l_temp_status_code AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE;
2371 l_temp_due_date DATE;
2372 l_curr_ue_rec get_UE_dtls_csr%ROWTYPE;
2373 l_dummy VARCHAR2(1);
2374 l_visit_start_date DATE;
2375 l_visit_end_date DATE;
2376 l_visit_assign_code VARCHAR2(30);
2377 l_accomplished_date DATE;
2378
2379 BEGIN
2380 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2382 END IF;
2383
2384 OPEN get_UE_dtls_csr;
2385 FETCH get_UE_dtls_csr INTO l_curr_ue_rec;
2386 CLOSE get_UE_dtls_csr;
2387
2388 IF (p_instance_flag = TRUE) THEN
2389 l_temp_instance_id := p_new_instance_id;
2390 ELSE
2391 l_temp_instance_id := l_curr_ue_rec.CSI_ITEM_INSTANCE_ID;
2392 END IF;
2393
2394 l_accomplished_date := l_curr_ue_rec.ACCOMPLISHED_DATE;
2395 IF (p_status_flag = TRUE) THEN
2396 l_temp_status_code := p_new_status_code;
2397 -- JR: Added the following on 10/21/2003 to set the accomplished date for SR-CLOSED UEs
2398 IF (p_new_status_code = G_UMP_SR_CLOSED_STATUS) THEN
2399 l_accomplished_date := SYSDATE;
2400 END IF;
2401 ELSE
2402 l_temp_status_code := l_curr_ue_rec.STATUS_CODE;
2403 END IF;
2404
2405 x_assigned_to_visit_flag := FALSE;
2406 x_new_tolerance_flag := null;
2407 IF (p_due_date_flag = TRUE) THEN
2408 l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2409 l_temp_due_date := p_new_due_date;
2410 OPEN is_ue_assigned_to_visit_csr;
2411 FETCH is_ue_assigned_to_visit_csr INTO l_dummy;
2412 IF (is_ue_assigned_to_visit_csr%FOUND) THEN
2413 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2414 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been assigned to a visit.');
2415 END IF;
2416 x_assigned_to_visit_flag := TRUE;
2417 -- Get the Visit Start Date
2418 AHL_UMP_UTIL_PKG.Get_Visit_Details(p_unit_effectivity_id => p_sr_ue_id,
2419 x_visit_Start_date => l_visit_start_date,
2420 x_visit_End_date => l_visit_end_date,
2421 x_visit_Assign_code => l_visit_assign_code);
2422 IF (p_new_due_date is not null and l_visit_start_date is not null and (l_visit_start_date > p_new_due_date)) THEN
2423 l_temp_tolerance_flag := G_YES_FLAG;
2424 x_new_tolerance_flag := G_YES_FLAG;
2425 ELSE
2426 l_temp_tolerance_flag := null;
2427 END IF;
2428 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2429 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Visit Start Date: ' || l_visit_start_date ||
2430 ', New Expected Resolution date of SR: ' || p_new_due_date ||
2431 ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2432 END IF;
2433 ELSE
2434 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2435 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has not been assigned to a visit.');
2436 END IF;
2437 END IF; -- Assigned to a visit or not
2438 CLOSE is_ue_assigned_to_visit_csr;
2439 ELSE
2440 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Due Date change for Unit Effectivity ' || p_sr_ue_id);
2442 END IF;
2443 l_temp_due_date := l_curr_ue_rec.DUE_DATE;
2444 l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2445 END IF;
2446
2447 -- bachandr Bug # 13916897
2448 IF l_temp_due_date IS NULL
2449 THEN
2450 OPEN c_get_incident_occ_date(l_curr_ue_rec.CS_INCIDENT_ID);
2451 FETCH c_get_incident_occ_date INTO l_occurred_date;
2452 CLOSE c_get_incident_occ_date;
2453 END IF;
2454 -- bachandr Bug # 13916897
2455
2456 -- Update the unit effectivity
2457 AHL_UNIT_EFFECTIVITIES_PKG.UPDATE_ROW(
2458 X_UNIT_EFFECTIVITY_ID => l_curr_ue_rec.UNIT_EFFECTIVITY_ID,
2459 X_MANUALLY_PLANNED_FLAG => l_curr_ue_rec.MANUALLY_PLANNED_FLAG,
2460 X_LOG_SERIES_CODE => l_curr_ue_rec.LOG_SERIES_CODE,
2461 X_LOG_SERIES_NUMBER => l_curr_ue_rec.LOG_SERIES_NUMBER,
2462 X_FLIGHT_NUMBER => l_curr_ue_rec.FLIGHT_NUMBER,
2463 X_MEL_CDL_TYPE_CODE => l_curr_ue_rec.MEL_CDL_TYPE_CODE,
2464 X_POSITION_PATH_ID => l_curr_ue_rec.POSITION_PATH_ID,
2465 X_ATA_CODE => l_curr_ue_rec.ATA_CODE,
2466 --X_CLEAR_STATION_ORG_ID => l_curr_ue_rec.CLEAR_STATION_ORG_ID,
2467 --X_CLEAR_STATION_DEPT_ID => l_curr_ue_rec.CLEAR_STATION_DEPT_ID,
2468 X_UNIT_CONFIG_HEADER_ID => l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,
2469 X_QA_COLLECTION_ID => l_curr_ue_rec.QA_COLLECTION_ID,
2470 X_CS_INCIDENT_ID => l_curr_ue_rec.CS_INCIDENT_ID,
2471 X_OBJECT_TYPE => l_curr_ue_rec.OBJECT_TYPE,
2472 X_APPLICATION_USG_CODE => l_curr_ue_rec.APPLICATION_USG_CODE,
2473 X_COUNTER_ID => l_curr_ue_rec.COUNTER_ID,
2474 X_EARLIEST_DUE_DATE => l_curr_ue_rec.EARLIEST_DUE_DATE,
2475 X_LATEST_DUE_DATE => l_curr_ue_rec.LATEST_DUE_DATE,
2476 X_FORECAST_SEQUENCE => l_curr_ue_rec.FORECAST_SEQUENCE,
2477 X_REPETITIVE_MR_FLAG => l_curr_ue_rec.REPETITIVE_MR_FLAG,
2478 X_TOLERANCE_FLAG => l_temp_tolerance_flag, -- Updated
2479 X_MESSAGE_CODE => l_curr_ue_rec.MESSAGE_CODE,
2480 X_DATE_RUN => l_curr_ue_rec.DATE_RUN,
2481 X_PRECEDING_UE_ID => l_curr_ue_rec.PRECEDING_UE_ID,
2482 X_SET_DUE_DATE => l_curr_ue_rec.SET_DUE_DATE,
2483 X_ACCOMPLISHED_DATE => l_accomplished_date, -- Updated
2484 X_SERVICE_LINE_ID => l_curr_ue_rec.SERVICE_LINE_ID,
2485 X_PROGRAM_MR_HEADER_ID => l_curr_ue_rec.PROGRAM_MR_HEADER_ID,
2486 X_CANCEL_REASON_CODE => l_curr_ue_rec.CANCEL_REASON_CODE,
2487 X_ATTRIBUTE_CATEGORY => l_curr_ue_rec.ATTRIBUTE_CATEGORY,
2488 X_ATTRIBUTE1 => l_curr_ue_rec.ATTRIBUTE1,
2489 X_ATTRIBUTE2 => l_curr_ue_rec.ATTRIBUTE2,
2490 X_ATTRIBUTE3 => l_curr_ue_rec.ATTRIBUTE3,
2491 X_ATTRIBUTE4 => l_curr_ue_rec.ATTRIBUTE4,
2492 X_ATTRIBUTE5 => l_curr_ue_rec.ATTRIBUTE5,
2493 X_ATTRIBUTE6 => l_curr_ue_rec.ATTRIBUTE6,
2494 X_ATTRIBUTE7 => l_curr_ue_rec.ATTRIBUTE7,
2495 X_ATTRIBUTE8 => l_curr_ue_rec.ATTRIBUTE8,
2496 X_ATTRIBUTE9 => l_curr_ue_rec.ATTRIBUTE9,
2497 X_ATTRIBUTE10 => l_curr_ue_rec.ATTRIBUTE10,
2498 X_ATTRIBUTE11 => l_curr_ue_rec.ATTRIBUTE11,
2499 X_ATTRIBUTE12 => l_curr_ue_rec.ATTRIBUTE12,
2500 X_ATTRIBUTE13 => l_curr_ue_rec.ATTRIBUTE13,
2501 X_ATTRIBUTE14 => l_curr_ue_rec.ATTRIBUTE14,
2502 X_ATTRIBUTE15 => l_curr_ue_rec.ATTRIBUTE15,
2503 X_OBJECT_VERSION_NUMBER => l_curr_ue_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
2504 X_CSI_ITEM_INSTANCE_ID => l_temp_instance_id,
2505 X_MR_HEADER_ID => l_curr_ue_rec.MR_HEADER_ID,
2506 X_MR_EFFECTIVITY_ID => l_curr_ue_rec.MR_EFFECTIVITY_ID,
2507 X_MR_INTERVAL_ID => l_curr_ue_rec.MR_INTERVAL_ID,
2508 X_STATUS_CODE => l_temp_status_code, -- Updated
2509 X_DUE_DATE => l_temp_due_date, -- Updated
2510 X_DUE_COUNTER_VALUE => l_curr_ue_rec.DUE_COUNTER_VALUE,
2511 X_DEFER_FROM_UE_ID => l_curr_ue_rec.DEFER_FROM_UE_ID,
2512 X_ORIG_DEFERRAL_UE_ID => l_curr_ue_rec.ORIG_DEFERRAL_UE_ID,
2513 X_REMARKS => l_curr_ue_rec.REMARKS,
2514 X_LAST_UPDATE_DATE => SYSDATE, -- Updated
2515 X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
2516 X_LAST_UPDATE_LOGIN => fnd_global.login_id, -- Updated
2517 -- BACHANDR - NR project start
2518 X_FLEET_HEADER_ID => AHL_UMP_UTIL_PKG.Get_Associated_Fleet(l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,nvl(l_temp_due_date,l_occurred_date),null)
2519 -- BACHANDR - NR project end
2520 );
2521 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2522 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2523 END IF;
2524
2525 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2526 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2527 END IF;
2528 END Update_SR_Unit_Effectivity;
2529
2530 ----------------------------------------
2531 -- This Procedure validates the Service Request during the Post Update process.
2532 -- It retrieves some information as part of the validation process to be used by subsequent routines
2533 ----------------------------------------
2534 PROCEDURE Validate_Request_For_Update(
2535 x_sr_ue_id OUT NOCOPY NUMBER,
2536 x_sr_ue_ovn OUT NOCOPY NUMBER,
2537 x_defer_from_ue_id OUT NOCOPY NUMBER) IS
2538
2539 CURSOR check_request_exists_csr IS
2540 SELECT 'x' FROM CS_INCIDENTS
2541 WHERE INCIDENT_ID = CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id;
2542
2543 CURSOR get_ue_dtls_csr IS
2544 SELECT UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, DEFER_FROM_UE_ID
2545 FROM AHL_UNIT_EFFECTIVITIES_APP_V
2546 WHERE CS_INCIDENT_ID = CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id
2547 AND (STATUS_CODE IS NULL OR STATUS_CODE NOT IN (G_UMP_DEFERRED_STATUS, G_UMP_EXCEPTION_STATUS));
2548
2549 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Request_For_Update';
2550
2551 l_dummy VARCHAR2(1);
2552
2553 BEGIN
2554 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2555 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2556 END IF;
2557
2558 -- Ensure that request id is not null
2559 IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2560 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NULL');
2561 FND_MSG_PUB.ADD;
2562 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2563 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2564 END IF;
2565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2566 END IF;
2567
2568 -- Ensure that the request id is valid
2569 OPEN check_request_exists_csr;
2570 FETCH check_request_exists_csr INTO l_dummy;
2571 IF(check_request_exists_csr%NOTFOUND) THEN
2572 CLOSE check_request_exists_csr;
2573 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2574 FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2575 FND_MSG_PUB.ADD;
2576 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2578 END IF;
2579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580 END IF;
2581 CLOSE check_request_exists_csr;
2582
2583 -- If already a CMRO SR, ensure that an unit effectivity exists for the SR
2584 IF (NVL(CS_SERVICEREQUEST_PVT.user_hooks_rec.old_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG) THEN
2585 -- apattark added x_defer_from_ue_id for bug #9166304
2586 OPEN get_ue_dtls_csr;
2587 FETCH get_ue_dtls_csr INTO x_sr_ue_id, x_sr_ue_ovn, x_defer_from_ue_id;
2588 IF (get_ue_dtls_csr%NOTFOUND) THEN
2589 CLOSE get_ue_dtls_csr;
2590 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2591 FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2592 FND_MSG_PUB.ADD;
2593 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2595 END IF;
2596 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2597 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not find any valid Unit Effectivity for this CS_INCIDENT_ID: ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2598 END IF;
2599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2600 END IF; -- UE Not Found
2601 CLOSE get_ue_dtls_csr;
2602 END IF; -- CMRO Type SR
2603
2604 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2606 END IF;
2607
2608 END Validate_Request_For_Update;
2609
2610 ----------------------------------------
2611 -- This Procedure handles type (CMRO to Non-CMRO and vice-versa) changes to a SR
2612 ----------------------------------------
2613 PROCEDURE Handle_Type_Change(
2614 p_sr_ue_id IN NUMBER,
2615 p_defer_from_ue_id IN NUMBER) IS
2616
2617 CURSOR get_tasks_for_ue_csr IS
2618 SELECT 'x' from AHL_VISIT_TASKS_B
2619 WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id;
2620
2621 CURSOR get_all_ue_and_rel_ids_csr IS
2622 SELECT UE_RELATIONSHIP_ID, RELATED_UE_ID, LEVEL
2623 FROM AHL_UE_RELATIONSHIPS
2624 WHERE originator_ue_id = p_sr_ue_id
2625 START WITH UE_ID = p_sr_ue_id
2626 CONNECT BY PRIOR RELATED_UE_ID = UE_ID
2627 ORDER BY LEVEL DESC; /* Bottom Up ordering*/
2628
2629 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Type_Change';
2630
2631 l_dummy VARCHAR2(1);
2632 l_temp_return_status VARCHAR2(1);
2633
2634 BEGIN
2635 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2636 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2637 END IF;
2638
2639 -- Ensure that the SR is not closed
2640 IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2641 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
2642 FND_MSG_PUB.ADD;
2643 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2644 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2645 END IF;
2646 RAISE FND_API.G_EXC_ERROR;
2647 END IF;
2648
2649 IF (NVL(CS_SERVICEREQUEST_PVT.user_hooks_rec.new_type_cmro_flag, G_NO_FLAG) = G_NO_FLAG) THEN
2650 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2651 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'CMRO to Non-CMRO type conversion');
2652 END IF;
2653 -- CMRO to Non-CMRO type
2654 -- apattark start changes for bug #9166304
2655 IF (p_defer_from_ue_id IS NOT NULL) THEN
2656 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2657 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'UE was deferred.');
2658 END IF;
2659 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_DEFERRED');
2660 FND_MSG_PUB.ADD;
2661 RAISE FND_API.G_EXC_ERROR;
2662 END IF;
2663 -- apattark end changes for bug #9166304
2664 -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
2665 IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2666 OPEN get_tasks_for_ue_csr;
2667 FETCH get_tasks_for_ue_csr INTO l_dummy;
2668 IF (get_tasks_for_ue_csr%FOUND) THEN
2669 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
2670 FND_MSG_PUB.ADD;
2671 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2672 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2673 END IF;
2674 ELSE
2675 -- This condition of a (originally) CMRO SR in Planned Status but not associated
2676 -- to a visit is not possible
2677 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2678 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Odd case of originally CMRO type SR in Planned status but not assigned to a visit');
2679 END IF;
2680 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2681 FND_MSG_PUB.ADD;
2682 END IF; -- tasks found
2683 CLOSE get_tasks_for_ue_csr;
2684 RAISE FND_API.G_EXC_ERROR;
2685 ELSE
2686 -- Not yet Planned, but open
2687 -- Delete all the dependent UEs and UE relationships
2688 FOR rel_and_ue_id_rec in get_all_ue_and_rel_ids_csr LOOP
2689 -- First delete the relationship
2690 AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2691 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2692 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2693 END IF;
2694 -- Next delete the unit effectivity
2695 AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2696 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2697 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || rel_and_ue_id_rec.RELATED_UE_ID);
2698 END IF;
2699 END LOOP;
2700
2701 -- Delete the SR's Unit Effectivity
2702 AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_ue_id);
2703 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2704 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_ue_id);
2705 END IF;
2706 END IF; -- Planned or Not
2707 ELSE
2708 -- Non-CMRO to CMRO type
2709 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2710 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Non-CMRO to CMRO type conversion');
2711 END IF;
2712 IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2713 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2714 FND_MSG_PUB.ADD;
2715 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2716 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2717 END IF;
2718 RAISE FND_API.G_EXC_ERROR;
2719 ELSE
2720 -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2721 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2722 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Create_SR_Unit_Effectivity.');
2723 END IF;
2724 Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2725 /* For Testing
2726 UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2727 */
2728 IF (l_temp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2729 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2731 END IF;
2732 ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2733 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2735 END IF;
2736 RAISE FND_API.G_EXC_ERROR;
2737 ELSE
2738 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2739 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Unexpected Error.');
2740 END IF;
2741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2742 END IF;
2743 END IF; -- Planned or not
2744 END IF; -- CMRO to Non-CMRO or vice-versa
2745
2746 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2747 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2748 END IF;
2749
2750 END Handle_Type_Change;
2751
2752 ----------------------------------------
2753 -- This Procedure handles other attribute (Instance, Resolution Date and Status) changes to a SR
2754 ----------------------------------------
2755 PROCEDURE Handle_Attribute_Changes(
2756 p_sr_ue_id IN NUMBER) IS
2757 --apattark added DEFER_FROM_UE_ID to check if it is null
2758 CURSOR get_and_lock_ue_dtls_csr IS
2759 SELECT CSI_ITEM_INSTANCE_ID, DUE_DATE, STATUS_CODE, DEFER_FROM_UE_ID from AHL_UNIT_EFFECTIVITIES_B
2760 WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id
2761 FOR UPDATE NOWAIT;
2762
2763 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Attribute_Changes';
2764
2765 l_instance_changed BOOLEAN := false;
2766 l_due_date_changed BOOLEAN := false;
2767 l_status_changed BOOLEAN := false;
2768 l_old_instance NUMBER;
2769 l_old_due_date DATE;
2770 l_old_status AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE;
2771 l_valid_flag BOOLEAN := true;
2772 l_new_instance NUMBER := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.customer_product_id;
2773 l_new_ue_status AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE;
2774 l_new_due_date DATE := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.exp_resolution_date;
2775 l_assigned BOOLEAN;
2776 l_new_tolerance_flag VARCHAR2(1);
2777 --apattark added for bug #9166304
2778 l_defer_from_ue_id NUMBER;
2779 l_return_status VARCHAR2(1);
2780 l_msg_data VARCHAR2(400);
2781 l_msg_count NUMBER;
2782 l_calc_due_date DATE;
2783
2784 BEGIN
2785 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2786 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2787 END IF;
2788
2789 -- Get the original (old) values and lock the row
2790 OPEN get_and_lock_ue_dtls_csr; -- Will throw exception (ORA-00054) if cannot acquire lock
2791 FETCH get_and_lock_ue_dtls_csr into l_old_instance, l_old_due_date, l_old_status, l_defer_from_ue_id;
2792 CLOSE get_and_lock_ue_dtls_csr;
2793
2794 -- Handle Instance Changes
2795 Handle_Instance_Change(p_sr_ue_id => p_sr_ue_id,
2796 p_old_instance_id => l_old_instance,
2797 p_x_valid_flag => l_valid_flag,
2798 x_instance_changed => l_instance_changed,
2799 p_defer_from_ue_id => l_defer_from_ue_id);
2800
2801 -- Handle Status Change
2802 Handle_Status_Change(p_sr_ue_id => p_sr_ue_id,
2803 p_old_ue_status => l_old_status,
2804 p_x_valid_flag => l_valid_flag,
2805 x_status_changed => l_status_changed,
2806 x_new_ue_status => l_new_ue_status);
2807
2808 -- Handle Expected Resolution Date (Due Date) Change
2809 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2810 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_due_date = ' || l_old_due_date || ', l_new_due_date = ' || l_new_due_date);
2811 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_status = ' || l_old_status || ', l_new_ue_status = ' || l_new_ue_status);
2812 END IF;
2813
2814 -- added for Complex Assembly Enh: Initialize a SR.
2815 IF (l_old_status = 'INIT-DUE' AND (l_new_ue_status IS NULL OR l_new_ue_status <> G_UMP_SR_CLOSED_STATUS)) THEN
2816 AHL_UMP_PROCESSUNIT_PVT.Calculate_Init_DueDate(
2817 x_return_status => l_return_status,
2818 x_msg_data => l_msg_data,
2819 x_msg_count => l_msg_count,
2820 p_init_msg_list => FND_API.G_FALSE,
2821 p_unit_effectivity_id => p_sr_ue_id,
2822 x_due_date => l_calc_due_date);
2823 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2824 l_new_due_date := l_calc_due_date;
2825 END IF;
2826 END IF;
2827
2828 --apattark added condition to check defer from ue id
2829 IF(l_defer_from_ue_id IS NULL) THEN
2830 IF((l_old_due_date IS NOT NULL AND l_new_due_date IS NULL) OR
2831 (l_old_due_date IS NULL AND l_new_due_date IS NOT NULL) OR
2832 (l_old_due_date IS NOT NULL AND l_new_due_date IS NOT NULL AND l_old_due_date <> l_new_due_date)) THEN
2833 -- Date has changed
2834 IF (l_old_status IS NULL OR l_old_status <> G_UMP_DEFERRED_STATUS) THEN
2835 -- Unit Effectivity has not been deferred: uptake
2836 l_due_date_changed := true;
2837
2838 END IF;
2839 END IF;
2840 END IF;
2841
2842 IF (l_valid_flag = false) THEN
2843 -- At least one Validation has failed: No need to do any further processing
2844 RAISE FND_API.G_EXC_ERROR;
2845 END IF;
2846
2847 IF (l_instance_changed = true OR l_status_changed = true OR l_due_date_changed = true) THEN
2848 -- Apply changes to the SR Unit Effectivity
2849 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2850 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2851 IF (l_instance_changed = true) THEN
2852 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2853 ELSE
2854 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2855 END IF;
2856 IF (l_status_changed = true) THEN
2857 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2858 ELSE
2859 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2860 END IF;
2861 IF (l_due_date_changed = true) THEN
2862 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2863 ELSE
2864 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = N');
2865 END IF;
2866 END IF;
2867 Update_SR_Unit_Effectivity(p_sr_ue_id => p_sr_ue_id,
2868 p_due_date_flag => l_due_date_changed,
2869 p_new_due_date => l_new_due_date,
2870 p_instance_flag => l_instance_changed,
2871 p_new_instance_id => l_new_instance,
2872 p_status_flag => l_status_changed,
2873 p_new_status_code => l_new_ue_status,
2874 x_assigned_to_visit_flag => l_assigned,
2875 x_new_tolerance_flag => l_new_tolerance_flag);
2876
2877 IF (l_due_date_changed = true) THEN
2878 -- Propagate Due Date changes to the UEs of all associated MRs
2879 Handle_MR_UE_Date_Change(p_sr_ue_id => p_sr_ue_id,
2880 p_assigned_to_visit_flag => l_assigned,
2881 p_new_tolerance_flag => l_new_tolerance_flag,
2882 p_new_due_date => l_new_due_date);
2883 END IF;
2884 ELSE
2885 -- No Changes to Update
2886 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2887 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2888 END IF;
2889 END IF;
2890
2891 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2892 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2893 END IF;
2894
2895 -- Call this API to Sync up SR summary updates with Workorder Description
2896 Handle_Summary_Update(
2897 p_sr_ue_id => p_sr_ue_id
2898 );
2899
2900 END Handle_Attribute_Changes;
2901
2902 ----------------------------------------
2903 -- This Procedure handles the change in the item instance (customer product) of the Service Request
2904 ----------------------------------------
2905 PROCEDURE Handle_Instance_Change(
2906 p_sr_ue_id IN NUMBER,
2907 p_old_instance_id IN NUMBER,
2908 p_x_valid_flag IN OUT NOCOPY BOOLEAN, -- This flag will never be set to true in this procedure
2909 x_instance_changed OUT NOCOPY BOOLEAN,
2910 p_defer_from_ue_id IN NUMBER) IS
2911
2912 CURSOR get_rel_count_csr IS
2913 SELECT COUNT(*) FROM AHL_UE_RELATIONSHIPS
2914 WHERE UE_ID = p_sr_ue_id;
2915
2916 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Instance_Change';
2917
2918 l_temp_count NUMBER := 0;
2919 l_new_instance_id NUMBER := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.customer_product_id;
2920
2921 BEGIN
2922 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2923 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2924 END IF;
2925 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2926 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_old_instance_id = ' || p_old_instance_id || ', l_new_instance_id = ' || l_new_instance_id);
2927 END IF;
2928 -- Initialize the Instance Changed Flag to false
2929 x_instance_changed := false;
2930
2931 IF (l_new_instance_id IS NULL) THEN
2932 p_x_valid_flag := false;
2933 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NULL');
2934 FND_MSG_PUB.ADD;
2935 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2936 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2937 END IF;
2938 ELSIF (l_new_instance_id <> p_old_instance_id) THEN
2939 -- Instance has changed: Validate
2940 --apattark begin changes for bug #9166304
2941 IF (p_defer_from_ue_id IS NOT NULL) THEN
2942 p_x_valid_flag := false;
2943 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_DEFERRED');
2944 FND_MSG_PUB.ADD;
2945 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2946 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2947 END IF;
2948 END IF;
2949 --apattark end changes for bug #9166304
2950 IF (CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.status_flag = G_SR_CLOSED_FLAG) THEN
2951 p_x_valid_flag := false;
2952 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_CLOSED');
2953 FND_MSG_PUB.ADD;
2954 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2955 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2956 END IF;
2957
2958 ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2959 p_x_valid_flag := false;
2960 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_PLANNED');
2961 FND_MSG_PUB.ADD;
2962 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2963 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2964 END IF;
2965 ELSE
2966 OPEN get_rel_count_csr;
2967 FETCH get_rel_count_csr INTO l_temp_count;
2968 CLOSE get_rel_count_csr;
2969 IF (l_temp_count > 0) THEN
2970 p_x_valid_flag := false;
2971 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_HAS_MRS');
2972 FND_MSG_PUB.ADD;
2973 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2974 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2975 END IF;
2976 ELSE
2977 -- All validations passed
2978 x_instance_changed := true;
2979 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2980 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY, 'Instance has changed from ' || p_old_instance_id || ' to ' || l_new_instance_id || '. All instance validations have succeeded.');
2981 END IF;
2982 END IF; -- Status Check
2983 END IF; -- Old <> New Check
2984 END IF; -- New Instance Null Check
2985
2986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2988 END IF;
2989
2990 END Handle_Instance_Change;
2991
2992 ----------------------------------------
2993 -- This Procedure handles the change in the status of the Service Request
2994 ----------------------------------------
2995 PROCEDURE Handle_Status_Change(
2996 p_sr_ue_id IN NUMBER,
2997 p_old_ue_status IN AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE,
2998 p_x_valid_flag IN OUT NOCOPY BOOLEAN, -- This flag will never be set to true in this procedure
2999 x_status_changed OUT NOCOPY BOOLEAN,
3000 x_new_ue_status OUT NOCOPY AHL_UNIT_EFFECTIVITIES_B.STATUS_CODE%TYPE) IS
3001
3002 CURSOR get_tasks_for_ue_csr IS
3003 SELECT 'x' from AHL_VISIT_TASKS_B
3004 where UNIT_EFFECTIVITY_ID = p_sr_ue_id;
3005
3006 CURSOR get_rel_count_csr IS
3007 SELECT COUNT(*) FROM AHL_UE_RELATIONSHIPS
3008 WHERE UE_ID = p_sr_ue_id;
3009
3010 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Status_Change';
3011
3012 l_temp_count NUMBER := 0;
3013 l_dummy VARCHAR2(1);
3014 l_assigned BOOLEAN;
3015
3016 BEGIN
3017 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3018 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
3019 END IF;
3020 -- Initialize the Status Changed Flag to false
3021 x_status_changed := false;
3022
3023 IF (CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.status_flag = G_SR_CLOSED_FLAG) THEN
3024 -- SR is or is being Closed
3025 OPEN get_tasks_for_ue_csr;
3026 FETCH get_tasks_for_ue_csr INTO l_dummy;
3027 IF(get_tasks_for_ue_csr%FOUND) THEN
3028 -- SR UE Assigned to a visit
3029 IF (p_old_ue_status IS NULL OR (p_old_ue_status NOT IN (G_UMP_ACCOMPLISHED_STATUS, G_UMP_TERMINATED_STATUS, G_UMP_MR_TERMINATE_STATUS))) THEN
3030 -- UE Neither accomplished nor terminated
3031 p_x_valid_flag := false;
3032 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_TASK_NOT_ACC');
3033 FND_MSG_PUB.ADD;
3034 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3035 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3036 END IF;
3037 ELSE
3038 -- Unit Effectivities already accomplished: No impact on UMP, Allow the SR to be closed.
3039 null;
3040 END IF; -- UE accomplished or terminated Status check
3041 ELSE
3042 -- Not assigned to any Visit: Check if MRs are associated
3043 OPEN get_rel_count_csr;
3044 FETCH get_rel_count_csr INTO l_temp_count;
3045 CLOSE get_rel_count_csr;
3046 IF (l_temp_count > 0) THEN
3047 -- SR has associated MRs, but the SR UE has not been assigned to any visit.
3048 p_x_valid_flag := false;
3049 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MRS_NOT_ACC');
3050 FND_MSG_PUB.ADD;
3051 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3053 END IF;
3054 ELSE
3055 -- No associated MRs
3056 IF ((p_old_ue_status IS NULL) OR (p_old_ue_status <> G_UMP_SR_CLOSED_STATUS)) THEN
3057 x_status_changed := true;
3058 x_new_ue_status := G_UMP_SR_CLOSED_STATUS;
3059 ELSE
3060 -- Already Closed: No need to update UE.
3061 null;
3062 END IF; -- Old UE Status is SR-CLOSED or not
3063 END IF; -- Has associated MRs or not
3064 END IF; -- SR UE assigned to a visit or not
3065 CLOSE get_tasks_for_ue_csr;
3066 ELSE
3067 -- SR is Open
3068 -- JR: Added the following check on 10/21/2003 to prevent
3069 -- reopening an SR whose UE is already SR_CLOSED
3070 IF (p_old_ue_status = G_UMP_SR_CLOSED_STATUS) THEN
3071 -- Attempting to open an SR whose UE is already SR_CLOSED.
3072 p_x_valid_flag := false;
3073 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_SR_CLSD');
3074 FND_MSG_PUB.ADD;
3075 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3076 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3077 END IF;
3078 ELSE
3079 OPEN get_tasks_for_ue_csr;
3080 FETCH get_tasks_for_ue_csr INTO l_dummy;
3081 IF(get_tasks_for_ue_csr%FOUND) THEN
3082 l_assigned := true;
3083 ELSE
3084 l_assigned := false;
3085 END IF;
3086 CLOSE get_tasks_for_ue_csr;
3087 IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
3088 IF (l_assigned = false) THEN
3089 -- SR is being moved to Planned status without assigning SR UE to any visit
3090 p_x_valid_flag := false;
3091 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NOT_ASSND');
3092 FND_MSG_PUB.ADD;
3093 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3094 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3095 END IF;
3096 END IF; -- Not assigned
3097 ELSE
3098 IF (l_assigned = true) THEN
3099 -- SR is being moved to a not-planned status while SR UE has already been assigned to a visit
3100 p_x_valid_flag := false;
3101 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ALREADY_ASSND');
3102 FND_MSG_PUB.ADD;
3103 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3104 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3105 END IF;
3106 END IF; -- Already Assigned
3107 END IF; -- SR is in Planned Status or Not
3108 END IF; -- Old UE Status SR-CLOSED or not
3109 END IF; -- If SR is Closed or not
3110
3111 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3113 END IF;
3114
3115 END Handle_Status_Change;
3116
3117 --------------------------------------------------------------------------------
3118 -- This Procedure Updates WO Description if corresponding SR Summary changes.
3119 -- Happens only for following cases
3120 -- 1. Non-Routines(NR) created on the shop floor.
3121 -- 2. Non-Routines with no MRs associated and planned from UMP into a Visit.
3122 --------------------------------------------------------------------------------
3123 PROCEDURE Handle_Summary_Update(
3124 p_sr_ue_id IN NUMBER
3125 )IS
3126
3127 --Check if the SR fits in any of below conditions
3128 -- 1. Non-Routines(NR) created on the shop floor.
3129 -- 2. Non-Routines with no MRs associated and planned from UMP into a Visit.
3130
3131 --Get Corresponding NR Workorders for the given SR Ue id.
3132 CURSOR c_get_ue_wo(p_sr_ue_id IN NUMBER)
3133 IS
3134 SELECT
3135 WO.workorder_id,
3136 WIPJ.description,
3137 VTSK.visit_task_number,
3138 VST.visit_number
3139 FROM
3140 AHL_WORKORDERS WO,
3141 WIP_DISCRETE_JOBS WIPJ,
3142 AHL_VISIT_TASKS_B VTSK,
3143 AHL_VISITS_B VST,
3144 AHL_UNIT_EFFECTIVITIES_B UE
3145 WHERE
3146 WO.status_code not in (4,12,7,5) AND -- Completed/Closed/Cancelled/Completed_No_Charge
3147 WO.wip_entity_id = WIPJ.wip_entity_id AND
3148 WO.visit_task_id = VTSK.visit_task_id AND
3149 WO.visit_id = VST.visit_id AND
3150 VTSK.unit_effectivity_id = UE.Unit_effectivity_id AND
3151 UE.Unit_effectivity_id = p_sr_ue_id AND
3152 UE.manually_planned_flag = 'Y' AND
3153 UE.cs_incident_id IS NOT NULL AND
3154 NOT EXISTS (SELECT
3155 'X'
3156 FROM
3157 AHL_UE_RELATIONSHIPS UER
3158 WHERE
3159 UER.related_ue_id = UE.Unit_effectivity_id OR
3160 UER.ue_id = UE.Unit_effectivity_id);
3161
3162
3163 --Get Summary corresponding to SR UE.
3164 CURSOR c_get_SR_details(p_sr_ue_id IN NUMBER)
3165 IS
3166 SELECT
3167 CSIA.summary,
3168 CSIT.name -- incident type name.
3169 FROM
3170 CS_INCIDENTS_ALL CSIA,
3171 CS_INCIDENT_TYPES_VL CSIT,
3172 AHL_UNIT_EFFECTIVITIES_B UE
3173 WHERE
3174 UE.unit_effectivity_id = p_sr_ue_id AND
3175 CSIA.incident_id = ue.cs_incident_id AND
3176 CSIT.incident_type_id = CSIA.incident_type_id;
3177
3178
3179 -- declare all local variables for the procedure.
3180 l_return_status VARCHAR2(1);
3181 l_msg_count NUMBER;
3182 l_msg_data VARCHAR2(40);
3183 l_sr_summary VARCHAR2(240);
3184 l_sr_type VARCHAR2(30);
3185 l_wo_description VARCHAR2(240);
3186 l_count NUMBER;
3187 --l_exists VARCHAR2(1);
3188 l_prd_workorder_tbl AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_TBL;
3189 l_prd_workorder_rel_tbl AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_REL_TBL;
3190
3191 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Summary_Update';
3192
3193 BEGIN
3194
3195 l_count := 1;
3196
3197 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3198 FND_LOG.STRING(
3199 FND_LOG.LEVEL_STATEMENT,
3200 L_DEBUG_KEY,
3201 'Entering the procedure Handle_Summary_Update ..'
3202 );
3203 END IF;
3204
3205 -- Check if the SR is one of the following
3206 -- 1. Non-Routines(NR) created on the shop floor.
3207 -- 2. Non-Routines with no MRs associated and planned from UMP into a Visit.
3208 --OPEN c_check_SR_rel(p_sr_ue_id);
3209 --FETCH c_check_SR_rel INTO l_exists;
3210 --CLOSE c_check_SR_rel;
3211
3212 -- Manually fetching the SR Summary from UE table because SR doesnt
3213 OPEN c_get_SR_details(p_sr_ue_id);
3214 FETCH c_get_SR_details INTO l_sr_summary, l_sr_type;
3215 CLOSE c_get_SR_details;
3216 --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3217 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3218
3219 FND_LOG.STRING(
3220 FND_LOG.LEVEL_STATEMENT,
3221 L_DEBUG_KEY,
3222 'l_sr_summary -->'||l_sr_summary
3223 );
3224 FND_LOG.STRING(
3225 FND_LOG.LEVEL_STATEMENT,
3226 L_DEBUG_KEY,
3227 'l_sr_type -->'||l_sr_type
3228 );
3229
3230 END IF;
3231
3232 --Concatenate SR summary and SR type to be of size <= 240 chars
3233 --since workorder description is of size 240 chars.
3234 l_wo_description := SUBSTRB(l_sr_type || ' - ' ||l_sr_summary, 1, 240);
3235
3236 -- Get the workorders for SR if it exists.
3237 FOR ue_wo_rec IN c_get_ue_wo(p_sr_ue_id)
3238 LOOP
3239 IF ue_wo_rec.description <> l_wo_description THEN
3240 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3241 FND_LOG.STRING(
3242 FND_LOG.LEVEL_STATEMENT,
3243 L_DEBUG_KEY,
3244 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3245 );
3246 END IF;
3247 l_prd_workorder_tbl(l_count).batch_id := ue_wo_rec.visit_number;
3248 l_prd_workorder_tbl(l_count).header_id := ue_wo_rec.visit_task_number;
3249 l_prd_workorder_tbl(l_count).workorder_id := ue_wo_rec.workorder_id;
3250 l_prd_workorder_tbl(l_count).job_description := l_wo_description;
3251 l_prd_workorder_tbl(l_count).dml_operation := 'U';
3252 l_count := l_count + 1;
3253 END IF;
3254 END LOOP;
3255
3256 IF l_prd_workorder_tbl.COUNT > 0
3257 THEN
3258 -- Update the workorders
3259 AHL_PRD_WORKORDER_PVT.Process_Jobs
3260 (
3261 p_api_version => 1.0,
3262 p_init_msg_list => FND_API.G_FALSE,
3263 p_commit => FND_API.G_FALSE,
3264 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3265 p_default => FND_API.G_TRUE,
3266 p_module_type => 'API',
3267 x_return_status => l_return_status,
3268 x_msg_count => l_msg_count,
3269 x_msg_data => l_msg_data,
3270 p_x_prd_workorder_tbl => l_prd_workorder_tbl,
3271 p_prd_workorder_rel_tbl=> l_prd_workorder_rel_tbl
3272 );
3273
3274 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3275 THEN
3276 RAISE FND_API.G_EXC_ERROR;
3277 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3278 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3279 END IF;
3280 END IF;
3281 END IF;
3282
3283 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3284 FND_LOG.STRING(
3285 FND_LOG.LEVEL_STATEMENT,
3286 L_DEBUG_KEY,
3287 'Exiting the procedure Handle_Summary_Update ..'
3288 );
3289 END IF;
3290 END Handle_Summary_Update;
3291
3292 -------------------------------------
3293 -- End Local Procedure Definitions --
3294 -------------------------------------
3295
3296 END AHL_UMP_SR_PVT;
[Home] [Help]