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