[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_DF_PVT
Source
1 PACKAGE BODY AHL_PRD_DF_PVT AS
2 /* $Header: AHLVPDFB.pls 120.17.12010000.2 2008/12/27 00:49:43 sracha ship $ */
3 -- Package/App Name
4 G_PKG_NAME CONSTANT VARCHAR(30) := 'AHL_PRD_DF_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
6
7 G_OP_SUBMIT_FOR_APPROVAL BOOLEAN := false;
8 G_MODULE_TYPE VARCHAR2(30) := 'JSP';
9
10 -- job statuses
11 G_JOB_UNRELEASED CONSTANT VARCHAR2(30) := '1';
12 G_JOB_RELEASED CONSTANT VARCHAR2(30) := '3';
13 G_JOB_CLOSED CONSTANT VARCHAR2(30) := '12';
14 G_JOB_DRAFT CONSTANT VARCHAR2(30) := '17';
15 G_JOB_PARTS_HOLD CONSTANT VARCHAR2(30) := '19';
16 G_JOB_COMPLETE CONSTANT VARCHAR2(30) := '4';
17 G_JOB_COMPLETE_NC CONSTANT VARCHAR2(30) := '5';
18 G_JOB_ON_HOLD CONSTANT VARCHAR2(30) := '6';
19 G_JOB_CANCELLED CONSTANT VARCHAR2(30) := '7';
20 G_JOB_PEND_DFR_APPR CONSTANT VARCHAR2(30) := '21';
21 G_JOB_PEND_QA_APPR CONSTANT VARCHAR2(20) := '20';
22 G_JOB_DELETED CONSTANT VARCHAR2(30) := '22';
23
24 -- approval actions
25 G_DEFERRAL_INITIATED CONSTANT VARCHAR2(1) := 'I';
26 G_DEFERRAL_REJECTED CONSTANT VARCHAR2(1) := 'R';
27 G_DEFERRAL_APPROVED CONSTANT VARCHAR2(1) := 'A';
28 G_DEFERRAL_ERROR CONSTANT VARCHAR2(1) := 'E';
29
30 ------------------------------------------------------------------------------------
31 -- Declare Procedures --
32 ------------------------------------------------------------------------------------
33 -- Internal procedure that this API uses For procedures defined in specs of this API
34 ------------------------------------------------------------------------------------
35 PROCEDURE process_df_header(
36 p_x_df_header_rec IN OUT NOCOPY AHL_PRD_DF_PVT.df_header_rec_type);
37
38 PROCEDURE log_df_header(
39 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type);
40
41 PROCEDURE validate_df_header(
42 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type);
43
44 PROCEDURE validate_reason_codes(
45 p_defer_reason_code IN VARCHAR2);
46
47 PROCEDURE default_unchanged_df_header(
48 p_x_df_header_rec IN OUT NOCOPY AHL_PRD_DF_PVT.df_header_rec_type);
49
50 PROCEDURE process_df_schedules(
51 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
52 p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type);
53
54 PROCEDURE log_df_schedules(
55 p_df_schedules_tbl IN AHL_PRD_DF_PVT.df_schedules_tbl_type);
56
57 PROCEDURE validate_df_schedules(
58 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
59 p_df_schedules_tbl IN AHL_PRD_DF_PVT.df_schedules_tbl_type);
60
61 PROCEDURE default_unchanged_df_schedules(
62 p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type);
63
64 PROCEDURE validate_deferral_updates(
65 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
66 x_warning_msg_data OUT NOCOPY VARCHAR2);
67
68 /* R12: moved to spec.
69 PROCEDURE process_approval_initiated (
70 p_unit_deferral_id IN NUMBER,
71 p_object_version_number IN NUMBER,
72 p_new_status IN VARCHAR2,
73 x_return_status OUT NOCOPY VARCHAR2);
74 */
75
76 PROCEDURE submit_for_approval(
77 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type);
78
79 FUNCTION valid_for_submission(
80 p_unit_effectivity_id IN NUMBER) RETURN BOOLEAN;
81
82 FUNCTION get_applicable_ue(p_unit_effectivity_id IN NUMBER)RETURN NUMBER;
83
84 PROCEDURE process_workorders(
85 p_unit_deferral_id IN NUMBER,
86 p_object_version_number IN NUMBER,
87 p_approval_result_code IN VARCHAR2,
88 x_return_status OUT NOCOPY VARCHAR2);
89
90 PROCEDURE process_unit_maint_plan(
91 p_unit_deferral_id IN NUMBER,
92 p_object_version_number IN NUMBER,
93 p_approval_result_code IN VARCHAR2,
94 p_new_status IN VARCHAR2,
95 x_return_status OUT NOCOPY VARCHAR2);
96
97 PROCEDURE process_prior_ump_deferrals(
98 p_unit_effectivity_id IN NUMBER);
99
100 PROCEDURE calculate_due_date(
101 x_return_status OUT NOCOPY VARCHAR2,
102 p_csi_item_instance_id IN NUMBER);
103
104
105 FUNCTION getLastStatus(p_workorder_id IN NUMBER) RETURN VARCHAR2;
106
107 FUNCTION isValidStatusUpdate(
108 operation_code VARCHAR2,
109 status_code VARCHAR2)RETURN BOOLEAN;
110
111 -- function to check if source of deferral is UMP or Production.
112 FUNCTION Is_UMP_Deferral(p_unit_deferral_id IN NUMBER) RETURN BOOLEAN;
113
114
115 -- ------------------------------------------------------------------------------------------------
116 -- Procedure name : process_deferral
117 -- Type : private
118 -- Function :
119 -- Pre-reqs :
120 -- Parameters :
121 --
122 -- Standard IN Parameters :
123 -- p_api_version IN NUMBER Default 1.0
124 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
125 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
126 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
127 -- Standard OUT Parameters :
128 -- x_return_status OUT VARCHAR2 Required
129 -- x_msg_count OUT NUMBER Required
130 -- x_msg_data OUT VARCHAR2 Required
131 --
132 -- p_module_type IN VARCHAR2 Required.
133 --
134 -- This parameter indicates the front-end form interface. The default value is 'JSP'. If the value
135 -- is JSP, then this API clears out all id columns and validations are done using the values based
136 -- on which the Id's are populated.
137 --
138 -- process_deferral Parameters:
139 --
140 --
141 --
142 --
143 -- Version :
144 -- Initial Version 1.0
145 --
146 -- End of Comments.
147
148 PROCEDURE process_deferral(
149 p_api_version IN NUMBER := 1.0,
150 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
151 p_commit IN VARCHAR2 := FND_API.G_FALSE,
152 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
153 p_module_type IN VARCHAR2 := NULL,
154 p_x_df_header_rec IN OUT NOCOPY AHL_PRD_DF_PVT.df_header_rec_type,
155 p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2) IS
159
160 l_api_version CONSTANT NUMBER := 1.0;
161 l_api_name CONSTANT VARCHAR2(30) := 'process_deferral';
162 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
163 l_warning_msg_data VARCHAR2(4000);
164
165 BEGIN
166 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
167 fnd_log.string
168 (
169 fnd_log.level_procedure,
170 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral.begin',
171 'At the start of PLSQL procedure'
172 );
173 END IF;
174 -- Standard start of API savepoint
175 SAVEPOINT process_deferral;
176
177 -- Standard call to check for call compatibility
178 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181 -- setting up module type
182 G_MODULE_TYPE := p_module_type;
183 -- Initialize message list if p_init_msg_list is set to TRUE
184 IF FND_API.To_Boolean( p_init_msg_list) THEN
185 FND_MSG_PUB.Initialize;
186 END IF;
187
188 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
189 fnd_log.string
190 (
191 fnd_log.level_statement,
192 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
193 'p_init_message_list : ' || p_init_msg_list
194 );
195 fnd_log.string
196 (
197 fnd_log.level_statement,
198 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
199 'p_commit : ' || p_commit
200 );
201 fnd_log.string
202 (
203 fnd_log.level_statement,
204 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
205 'p_validation_level : ' || p_validation_level
206 );
207 fnd_log.string
208 (
209 fnd_log.level_statement,
210 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
211 'p_module_type : ' || p_module_type
212 );
213 END IF;
214 -- Initialize API return status to success
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
218 fnd_log.string
219 (
220 fnd_log.level_statement,
221 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
222 'Logging deferral header record prior to processing'
223 );
224 log_df_header(p_df_header_rec => p_x_df_header_rec);
225 END IF;
226
227 process_df_header(
228 p_x_df_header_rec => p_x_df_header_rec
229 );
230
231 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
232 fnd_log.string
233 (
234 fnd_log.level_statement,
235 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
236 'Logging deferral header record after processing'
237 );
238 log_df_header(p_df_header_rec => p_x_df_header_rec);
239 END IF;
240
241
242 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
243 fnd_log.string
244 (
245 fnd_log.level_statement,
246 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
247 'Number of records in schedules : ' || p_x_df_schedules_tbl.count
248 );
249 fnd_log.string
250 (
251 fnd_log.level_statement,
252 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
253 'Logging deferral schedule records before processing'
254 );
255 IF(p_x_df_schedules_tbl.count > 0)THEN
256 log_df_schedules(p_df_schedules_tbl => p_x_df_schedules_tbl);
257 END IF;
258 END IF;
259
260 -- PROCESS deferral schedules
261 IF (p_x_df_schedules_tbl.count > 0 AND p_x_df_header_rec.skip_mr_flag = G_NO_FLAG AND
262 (p_x_df_header_rec.operation_flag IS NULL OR
263 p_x_df_header_rec.operation_flag IN (G_OP_CREATE,G_OP_UPDATE))) THEN
264 process_df_schedules(
265 p_df_header_rec => p_x_df_header_rec,
266 p_x_df_schedules_tbl => p_x_df_schedules_tbl
267 );
268 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
269 fnd_log.string
270 (
271 fnd_log.level_statement,
272 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
273 'Logging deferral schedule records after processing'
274 );
275 log_df_schedules(p_df_schedules_tbl => p_x_df_schedules_tbl);
276 END IF;
277 END IF;
278
279 -- validating the updates as a whole
280 IF(p_x_df_header_rec.skip_mr_flag = G_NO_FLAG AND (p_x_df_header_rec.operation_flag IS NULL OR
281 p_x_df_header_rec.operation_flag IN (G_OP_CREATE,G_OP_UPDATE))) THEN
282 validate_deferral_updates(
283 p_df_header_rec => p_x_df_header_rec,
284 x_warning_msg_data => l_warning_msg_data
285 );
286 END IF;
287
288 IF G_OP_SUBMIT_FOR_APPROVAL THEN
289 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)THEN
290 fnd_log.string
291 (
292 fnd_log.level_event,
293 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral',
294 'Submitting for Aprroval Unit Deferral ID : ' || p_x_df_header_rec.unit_deferral_id ||
295 ' Object Version Number : ' || p_x_df_header_rec.object_version_number
296 );
297 END IF;
298 submit_for_approval(
299 p_df_header_rec => p_x_df_header_rec
300 );
301 END IF;
302
303 -- Check Error Message stack.
304 x_msg_count := FND_MSG_PUB.count_msg;
305 IF x_msg_count > 0 THEN
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308
309 -- Standard check of p_commit
310 IF FND_API.TO_BOOLEAN(p_commit) THEN
311 COMMIT WORK;
312 END IF;
313
314
315
316 -- Standard call to get message count and if count is 1, get message info
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data,
320 p_encoded => fnd_api.g_false
321 );
322 IF(x_msg_count = 0 AND l_warning_msg_data IS NOT NULL)THEN
323 x_msg_count := 1;
324 x_msg_data := l_warning_msg_data;
325 END IF;
326
327 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
328 fnd_log.string
329 (
330 fnd_log.level_procedure,
331 'ahl.plsql.AHL_PRD_DF_PVT.process_deferral.end',
332 'At the end of PLSQL procedure'
333 );
334 END IF;
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 Rollback to process_deferral;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
341 p_data => x_msg_data,
342 p_encoded => fnd_api.g_false);
343
344
345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
346 Rollback to process_deferral;
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
348 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
349 p_data => x_msg_data,
350 p_encoded => fnd_api.g_false);
351
352
353 WHEN OTHERS THEN
354 Rollback to process_deferral;
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
357 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
358 p_procedure_name => l_api_name,
359 p_error_text => SUBSTR(SQLERRM,1,500));
360 END IF;
361 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
362 p_data => x_msg_data,
363 p_encoded => fnd_api.g_false);
364 END process_deferral;
365
366 -------------------------------------------------------------------------------------
367 -- procedure processes the header information
368 -- handle dml updates to the deferral context information
369 -- Note : Value to id conversion is done only for unit_effectivity_id and unit_deferral_type to fetch
370 -- the unit_deferral_id. Hence no validation level check and no sperate procedure for value
371 -- to id conversion process.
372 -------------------------------------------------------------------------------------
373 PROCEDURE process_df_header(
374 p_x_df_header_rec IN OUT NOCOPY AHL_PRD_DF_PVT.df_header_rec_type)
375 IS
376
377 l_rowid VARCHAR2(30);
378
379 CURSOR unit_deferral_id_csr(p_unit_effectivity_id In NUMBER) IS
380 SELECT unit_deferral_id, object_version_number
381 from ahl_unit_deferrals_b
382 WHERE unit_deferral_type = 'DEFERRAL'
383 AND unit_effectivity_id = p_unit_effectivity_id;
384
385 l_unit_deferral_id NUMBER;
386 l_object_version_number NUMBER;
387 l_input_key_error_flag BOOLEAN := false;
388
389 /*
390 * Application usage code AND condition has been added to resolve all application usage code related issues here
391 * in the cursor select of "unit_effectivity_info_csr".
392 * Here onwards in this program we dont have to worry about any app usage code related issues.
393 */
394
395 CURSOR unit_effectivity_info_csr(p_unit_effectivity_id IN NUMBER) IS
396 SELECT mr_header_id,cs_incident_id,repetitive_mr_flag,orig_deferral_ue_id,ue_status_code,
397 def_status_code,manually_planned_flag
398 FROM ahl_ue_deferral_details_v
399 WHERE unit_effectivity_id = p_unit_effectivity_id
400 AND APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) ;
401
402 l_mr_header_id NUMBER;
403 l_incident_id NUMBER;
404 l_orig_deferral_ue_id NUMBER;
405 l_ue_status_code VARCHAR2(30);
406 l_def_status_code VARCHAR2(30);
407 l_repetitive_mr_flag VARCHAR2(1);
408 l_manually_planned_flag VARCHAR2(1);
409
410 -- to check whether MR or any of its children has resettable counters
411 CURSOR reset_counter_csr(p_unit_effectivity_id IN NUMBER) IS
412 --SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES_APP_V MRE, AHL_UNIT_EFFECTIVITIES_APP_V UE
413 /* In R12, modified to use csi_counters_vl instead of csi_cp_counters_v.
414 SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
415 WHERE CP.customer_product_id = UE.csi_item_instance_id
416 AND CP.counter_name = MRI.counter_name
417 AND MRI.reset_value IS NOT NULL
418 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
419 AND MRE.mr_header_id = UE.mr_header_id
420 AND UE.unit_effectivity_id = p_unit_effectivity_id
421 UNION
422 --SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES_APP_V MRE, AHL_UNIT_EFFECTIVITIES_APP_V UE
423 SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
424 WHERE CP.customer_product_id = UE.csi_item_instance_id
425 AND CP.counter_name = MRI.counter_name
426 AND MRI.reset_value IS NOT NULL
427 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
428 AND MRE.mr_header_id = UE.mr_header_id
429 AND UE.unit_effectivity_id IN
430 (
431
432 SELECT related_ue_id
433 FROM AHL_UE_RELATIONSHIPS
434 WHERE relationship_code = 'PARENT'
435 START WITH ue_id = p_unit_effectivity_id
436 CONNECT BY ue_id = PRIOR related_ue_id
437
438 );
439 */
440 SELECT 'x'
441 from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
442 AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
443 WHERE CA.source_object_id = UE.csi_item_instance_id
444 AND ca.source_object_code = 'CP'
445 AND CP.counter_template_name = MRI.counter_name
446 AND MRI.reset_value IS NOT NULL
447 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
448 AND MRE.mr_header_id = UE.mr_header_id
449 AND UE.unit_effectivity_id = p_unit_effectivity_id
450 UNION
451 SELECT 'x'
452 from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
453 AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
454 WHERE CA.source_object_id = UE.csi_item_instance_id
455 AND ca.source_object_code = 'CP'
456 AND CP.counter_template_name = MRI.counter_name
457 AND MRI.reset_value IS NOT NULL
458 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
459 AND MRE.mr_header_id = UE.mr_header_id
460 AND UE.unit_effectivity_id IN
461 (
462
463 SELECT related_ue_id
464 FROM AHL_UE_RELATIONSHIPS
465 WHERE relationship_code = 'PARENT'
466 START WITH ue_id = p_unit_effectivity_id
467 CONNECT BY ue_id = PRIOR related_ue_id
468
469 );
470
471 l_exists VARCHAR2(1);
472
473
474 BEGIN
475 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
476 fnd_log.string
477 (
478 fnd_log.level_procedure,
479 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header.begin',
480 'At the start of PLSQL procedure'
481 );
482 END IF;
483 -- initializaing submit for approval flag
484 G_OP_SUBMIT_FOR_APPROVAL := false;
485 -- value to id conversion based on unit_effectivity_id
486 IF( p_x_df_header_rec.unit_effectivity_id IS NULL AND
487 (p_x_df_header_rec.unit_deferral_id IS NULL OR p_x_df_header_rec.object_version_number IS NULL))THEN
488 l_input_key_error_flag := true;
489 ELSIF(p_x_df_header_rec.unit_effectivity_id IS NOT NULL)THEN
490 IF(NVL(p_x_df_header_rec.operation_flag,'x') <> G_OP_CREATE)THEN
491 OPEN unit_deferral_id_csr(p_x_df_header_rec.unit_effectivity_id);
492 FETCH unit_deferral_id_csr INTO p_x_df_header_rec.unit_deferral_id,p_x_df_header_rec.object_version_number;
493 IF(unit_deferral_id_csr%NOTFOUND) THEN
494 IF(p_x_df_header_rec.operation_flag = G_OP_SUBMIT)THEN
495 p_x_df_header_rec.operation_flag := G_OP_CREATE;
496 G_OP_SUBMIT_FOR_APPROVAL := TRUE;
497 ELSE
498 l_input_key_error_flag := true;
499 END IF;
500 ELSIF(p_x_df_header_rec.operation_flag = G_OP_SUBMIT)THEN
501 p_x_df_header_rec.operation_flag := G_OP_UPDATE;
502 G_OP_SUBMIT_FOR_APPROVAL := TRUE;
503 END IF;
504 CLOSE unit_deferral_id_csr;
505 END IF;
506 END IF;
507 -- raise error if input keys are wrong.
508 IF(l_input_key_error_flag)THEN
509 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HREC_KMISS');
510 FND_MSG_PUB.ADD;
511 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
512 fnd_log.string
513 (
514 fnd_log.level_unexpected,
515 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header',
516 'Input Keys are missing or invalid'
517 );
518 END IF;
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520 END IF;
521 -- check whether unit effectivity is valid
522 OPEN unit_effectivity_info_csr(p_x_df_header_rec.unit_effectivity_id);
523 FETCH unit_effectivity_info_csr INTO l_mr_header_id,
524 l_incident_id,
525 l_repetitive_mr_flag,
526 l_orig_deferral_ue_id,
527 l_ue_status_code,
528 l_def_status_code,
529 l_manually_planned_flag;
530 IF(unit_effectivity_info_csr%NOTFOUND) THEN
531 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HREC_UE_ID');
532 FND_MSG_PUB.ADD;
533 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
534 fnd_log.string
535 (
536 fnd_log.level_unexpected,
537 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header',
538 'unit effectivity record not found'
539 );
540 END IF;
541 CLOSE unit_effectivity_info_csr;
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 ELSE
544 IF(l_orig_deferral_ue_id IS NOT NULL) THEN
545 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HREC_UE_ID');
546 FND_MSG_PUB.ADD;
547 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
548 fnd_log.string
549 (
550 fnd_log.level_unexpected,
551 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header',
552 'unit effectivity record not found'
553 );
554 END IF;
555 CLOSE unit_effectivity_info_csr;
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 ELSIF((NVL(l_ue_status_code,'x') IN ('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE'))
558 OR (NVL(l_def_status_code,'x')IN ('DEFERRED','DEFERRAL_PENDING','TERMINATED','CANCELLED')))THEN
559 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MRSR_STATUS');
560 FND_MSG_PUB.ADD;
561 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
562 fnd_log.string
563 (
564 fnd_log.level_error,
565 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header',
566 'Status of MR or SR is not valid for deferral'
567 );
568 END IF;
569 CLOSE unit_effectivity_info_csr;
570 RAISE FND_API.G_EXC_ERROR;
571 ELSE
572 -- filling in additional info
573 IF(l_mr_header_id IS NULL)THEN
574 p_x_df_header_rec.deferral_type := G_DEFERRAL_TYPE_SR;
575 ELSE
576 p_x_df_header_rec.deferral_type := G_DEFERRAL_TYPE_MR;
577 END IF;
578 p_x_df_header_rec.mr_repetitive_flag := NVL(l_repetitive_mr_flag,G_NO_FLAG);
579 p_x_df_header_rec.manually_planned_flag := NVL(l_manually_planned_flag,G_NO_FLAG);
580 p_x_df_header_rec.reset_counter_flag := G_YES_FLAG;
581 IF(p_x_df_header_rec.deferral_type = G_DEFERRAL_TYPE_MR)THEN
582 OPEN reset_counter_csr(p_x_df_header_rec.unit_effectivity_id);
583 FETCH reset_counter_csr INTO l_exists;
584 IF(reset_counter_csr%NOTFOUND)THEN
585 p_x_df_header_rec.reset_counter_flag := G_NO_FLAG;
586 END IF;
587 CLOSE reset_counter_csr;
588 /*
589 ELSE
590 p_x_df_header_rec.skip_mr_flag := G_NO_FLAG;
591 p_x_df_header_rec.affect_due_calc_flag := G_YES_FLAG;
592 */
593 IF(p_x_df_header_rec.skip_mr_flag = G_YES_FLAG) THEN
594 p_x_df_header_rec.set_due_date := NULL;
595 p_x_df_header_rec.affect_due_calc_flag := G_NO_FLAG;
596 END IF;
597 ELSE
598 --Enable SR cancellation for non-serialized items.
599 --p_x_df_header_rec.skip_mr_flag := G_NO_FLAG;
600 p_x_df_header_rec.affect_due_calc_flag := G_YES_FLAG;
601 END IF;
602 END IF;
603 END IF;
604 -- doing defaulting before validation because of future OA needs
605 default_unchanged_df_header(p_x_df_header_rec => p_x_df_header_rec);
606
607 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
608 fnd_log.string
609 (
610 fnd_log.level_statement,
611 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header',
612 'Dumping deferral header record prior to validating'
613 );
614 log_df_header(p_df_header_rec => p_x_df_header_rec);
615 END IF;
616
617 IF(p_x_df_header_rec.operation_flag IS NOT NULL) THEN
618 validate_df_header(p_df_header_rec => p_x_df_header_rec);
619 END IF;
620
621 IF(p_x_df_header_rec.operation_flag = G_OP_DELETE) THEN
622 -- delete schedules records
623 DELETE AHL_UNIT_THRESHOLDS WHERE UNIT_DEFERRAL_ID = p_x_df_header_rec.unit_deferral_id;
624 -- delete header records.
625 AHL_UNIT_DEFERRALS_PKG.delete_row(x_unit_deferral_id => p_x_df_header_rec.unit_deferral_id);
626 ELSIF(p_x_df_header_rec.operation_flag = G_OP_CREATE) THEN
627 --setting object version number for create
628 p_x_df_header_rec.object_version_number := 1;
629 --setting up user/create/update information
630 p_x_df_header_rec.created_by := fnd_global.user_id;
631 p_x_df_header_rec.creation_date := SYSDATE;
632 p_x_df_header_rec.last_updated_by := fnd_global.user_id;
633 p_x_df_header_rec.last_update_date := SYSDATE;
634 p_x_df_header_rec.last_update_login := fnd_global.user_id;
635
636 IF(p_x_df_header_rec.skip_mr_flag = G_YES_FLAG)THEN
637 p_x_df_header_rec.set_due_date := null;
638 p_x_df_header_rec.affect_due_calc_flag := G_NO_FLAG;
639 END IF;
640
641 AHL_UNIT_DEFERRALS_PKG.insert_row(
642 x_rowid => l_rowid,
643 x_unit_deferral_id => p_x_df_header_rec.unit_deferral_id,
644 x_object_version_number => p_x_df_header_rec.object_version_number,
645 x_created_by => p_x_df_header_rec.created_by,
646 x_creation_date => p_x_df_header_rec.creation_date,
647 x_last_updated_by => p_x_df_header_rec.last_updated_by,
648 x_last_update_date => p_x_df_header_rec.last_update_date,
649 x_last_update_login => p_x_df_header_rec.last_update_login,
650 x_unit_effectivity_id => p_x_df_header_rec.unit_effectivity_id,
651 x_unit_deferral_type => p_x_df_header_rec.unit_deferral_type,
652 x_set_due_date => p_x_df_header_rec.set_due_date,
653 x_deferral_effective_on => p_x_df_header_rec.deferral_effective_on,
654 x_approval_status_code => p_x_df_header_rec.approval_status_code,
655 x_defer_reason_code => p_x_df_header_rec.defer_reason_code,
656 x_affect_due_calc_flag => p_x_df_header_rec.affect_due_calc_flag,
657 x_skip_mr_flag => p_x_df_header_rec.skip_mr_flag,
658 x_remarks => p_x_df_header_rec.remarks,
659 x_approver_notes => p_x_df_header_rec.approver_notes,
660 x_ata_sequence_id => NULL,
661 x_user_deferral_type => p_x_df_header_rec.user_deferral_type_code,
662 x_attribute_category => p_x_df_header_rec.attribute_category,
663 x_attribute1 => p_x_df_header_rec.attribute1,
664 x_attribute2 => p_x_df_header_rec.attribute2,
665 x_attribute3 => p_x_df_header_rec.attribute3,
666 x_attribute4 => p_x_df_header_rec.attribute4,
667 x_attribute5 => p_x_df_header_rec.attribute5,
668 x_attribute6 => p_x_df_header_rec.attribute6,
669 x_attribute7 => p_x_df_header_rec.attribute7,
670 x_attribute8 => p_x_df_header_rec.attribute8,
671 x_attribute9 => p_x_df_header_rec.attribute9,
672 x_attribute10 => p_x_df_header_rec.attribute10,
673 x_attribute11 => p_x_df_header_rec.attribute11,
674 x_attribute12 => p_x_df_header_rec.attribute12,
675 x_attribute13 => p_x_df_header_rec.attribute13,
676 x_attribute14 => p_x_df_header_rec.attribute14,
677 x_attribute15 => p_x_df_header_rec.attribute15
678 );
679 ELSIF (p_x_df_header_rec.operation_flag = G_OP_UPDATE) THEN
680
681 -- setting up object version number
682 p_x_df_header_rec.object_version_number := p_x_df_header_rec.object_version_number + 1;
683 --setting up user/create/update information
684 p_x_df_header_rec.last_updated_by := fnd_global.user_id;
685 p_x_df_header_rec.last_update_date := SYSDATE;
686 p_x_df_header_rec.last_update_login := fnd_global.user_id;
687
688 IF(p_x_df_header_rec.skip_mr_flag = G_YES_FLAG)THEN
689 p_x_df_header_rec.set_due_date := null;
690 p_x_df_header_rec.affect_due_calc_flag := G_NO_FLAG;
691 -- Delete all records in unit thresholds
692 DELETE AHL_UNIT_THRESHOLDS WHERE UNIT_DEFERRAL_ID = p_x_df_header_rec.unit_deferral_id;
693 END IF;
694 p_x_df_header_rec.approval_status_code := 'DRAFT';
695 AHL_UNIT_DEFERRALS_PKG.update_row(
696 x_unit_deferral_id => p_x_df_header_rec.unit_deferral_id,
697 x_object_version_number => p_x_df_header_rec.object_version_number,
698 x_last_updated_by => p_x_df_header_rec.last_updated_by,
699 x_last_update_date => p_x_df_header_rec.last_update_date,
700 x_last_update_login => p_x_df_header_rec.last_update_login,
701 x_unit_effectivity_id => p_x_df_header_rec.unit_effectivity_id,
702 x_unit_deferral_type => p_x_df_header_rec.unit_deferral_type,
703 x_set_due_date => p_x_df_header_rec.set_due_date,
704 x_deferral_effective_on => p_x_df_header_rec.deferral_effective_on,
705 x_approval_status_code => p_x_df_header_rec.approval_status_code,
706 x_defer_reason_code => p_x_df_header_rec.defer_reason_code,
707 x_affect_due_calc_flag => p_x_df_header_rec.affect_due_calc_flag,
708 x_skip_mr_flag => p_x_df_header_rec.skip_mr_flag,
709 x_remarks => p_x_df_header_rec.remarks,
710 x_approver_notes => p_x_df_header_rec.approver_notes,
711 x_ata_sequence_id => null,
712 x_user_deferral_type => p_x_df_header_rec.user_deferral_type_code,
713 x_attribute_category => p_x_df_header_rec.attribute_category,
714 x_attribute1 => p_x_df_header_rec.attribute1,
715 x_attribute2 => p_x_df_header_rec.attribute2,
716 x_attribute3 => p_x_df_header_rec.attribute3,
717 x_attribute4 => p_x_df_header_rec.attribute4,
718 x_attribute5 => p_x_df_header_rec.attribute5,
719 x_attribute6 => p_x_df_header_rec.attribute6,
720 x_attribute7 => p_x_df_header_rec.attribute7,
721 x_attribute8 => p_x_df_header_rec.attribute8,
722 x_attribute9 => p_x_df_header_rec.attribute9,
723 x_attribute10 => p_x_df_header_rec.attribute10,
724 x_attribute11 => p_x_df_header_rec.attribute11,
725 x_attribute12 => p_x_df_header_rec.attribute12,
726 x_attribute13 => p_x_df_header_rec.attribute13,
727 x_attribute14 => p_x_df_header_rec.attribute14,
728 x_attribute15 => p_x_df_header_rec.attribute15
729 );
730 END IF;
731
732 IF(FND_MSG_PUB.count_msg > 0)THEN
733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
734 END IF;
735
736 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
737 fnd_log.string
738 (
739 fnd_log.level_procedure,
740 'ahl.plsql.AHL_PRD_DF_PVT.process_df_header.end',
741 'At the end of PLSQL procedure'
742 );
743 END IF;
744
745 END process_df_header;
746 --------------------------------------------------------------------------------
747 -- Procedure to dump deferral header record
748 --------------------------------------------------------------------------------
749 PROCEDURE log_df_header(
750 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type) IS
751
752 BEGIN
753 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
754 fnd_log.string
755 (
756 fnd_log.level_statement,
757 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
758 'p_x_df_header_rec.operation_flag : ' || p_df_header_rec.operation_flag
759 );
760 fnd_log.string
761 (
762 fnd_log.level_statement,
763 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
764 'p_x_df_header_rec.unit_deferral_id : ' || p_df_header_rec.unit_deferral_id
765 );
766 fnd_log.string
767 (
768 fnd_log.level_statement,
769 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
770 'p_x_df_header_rec.object_version_number : ' || p_df_header_rec.object_version_number
771 );
772 fnd_log.string
773 (
774 fnd_log.level_statement,
775 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
776 'p_x_df_header_rec.unit_effectivity_id : ' || p_df_header_rec.unit_effectivity_id
777 );
778 fnd_log.string
779 (
780 fnd_log.level_statement,
781 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
782 'p_x_df_header_rec.defer_reason_code : ' || p_df_header_rec.defer_reason_code
783 );
784 fnd_log.string
785 (
786 fnd_log.level_statement,
787 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
788 'p_x_df_header_rec.remarks : ' || p_df_header_rec.remarks
789 );
790 fnd_log.string
791 (
792 fnd_log.level_statement,
793 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
794 'p_x_df_header_rec.skip_mr_flag : ' || p_df_header_rec.skip_mr_flag
795 );
796 fnd_log.string
797 (
798 fnd_log.level_statement,
799 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
800 'p_x_df_header_rec.affect_due_calc_flag : ' || p_df_header_rec.affect_due_calc_flag
801 );
802 fnd_log.string
803 (
804 fnd_log.level_statement,
805 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
806 'p_x_df_header_rec.set_due_date : ' || p_df_header_rec.set_due_date
807 );
808 fnd_log.string
809 (
810 fnd_log.level_statement,
811 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
812 'p_x_df_header_rec.deferral_effective_on : ' || p_df_header_rec.deferral_effective_on
813 );
814 fnd_log.string
815 (
816 fnd_log.level_statement,
817 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
818 'p_x_df_header_rec.deferral_type : ' || p_df_header_rec.deferral_type
819 );
820 fnd_log.string
821 (
822 fnd_log.level_statement,
823 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
824 'p_x_df_header_rec.mr_repetitive_flag : ' || p_df_header_rec.mr_repetitive_flag
825 );
826 fnd_log.string
827 (
828 fnd_log.level_statement,
829 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
830 'p_x_df_header_rec.reset_counter_flag : ' || p_df_header_rec.reset_counter_flag
831 );
832 fnd_log.string
833 (
834 fnd_log.level_statement,
835 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
836 'p_x_df_header_rec.manually_planned_flag : ' || p_df_header_rec.manually_planned_flag
837 );
838 fnd_log.string
839 (
840 fnd_log.level_statement,
841 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
842 'user id : ' || FND_GLOBAL.USER_ID()
843 );
844 fnd_log.string
845 (
846 fnd_log.level_statement,
847 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
848 'resp id : ' || FND_GLOBAL.RESP_ID()
849 );
850 fnd_log.string
851 (
852 fnd_log.level_statement,
853 'ahl.plsql.AHL_PRD_DF_PVT.log_df_header',
854 'resp appl id : ' || FND_GLOBAL.resp_appl_id()
855 );
856 END IF;
857 END log_df_header;
858
859 PROCEDURE validate_df_header(
860 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type)IS
861
862 -- check whether any record already exists related to deferral
863 CURSOR deferral_rec_exists_csr(p_unit_effectivity_id IN NUMBER) IS
864 SELECT 'x' from ahl_unit_deferrals_vl
865 WHERE unit_deferral_type = 'DEFERRAL'
866 AND unit_effectivity_id = p_unit_effectivity_id;
867
868 -- to check whether ue is in production
869 CURSOR valid_deferral_csr(p_unit_effectivity_id IN NUMBER) IS
870 SELECT 'x' from ahl_workorder_tasks_v WO,AHL_UNIT_EFFECTIVITIES_B UE --Undid App usage related BLIND changes
871 WHERE WO.unit_effectivity_id = UE.unit_effectivity_id
872 AND NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
873 AND UE.unit_effectivity_id = p_unit_effectivity_id;
874
875 -- check whether MR is not terminated
876 /*CURSOR valid_mr_csr(p_unit_effectivity_id IN NUMBER) IS
877 select 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def, ahl_unit_effectivities_b UE
878 where UE.unit_effectivity_id = p_unit_effectivity_id
879 AND def.mr_header_id = UE.mr_header_id
880 and def.title = mr.title
881 and trunc(sysdate) between trunc(mr.effective_from)
882 and trunc(nvl(mr.effective_to, sysdate))
883 and mr.version_number >= def.version_number;*/
884
885 -- to check whether this deferral record can be updated or deleted
886 CURSOR valid_deferral_up_csr(p_unit_deferral_id IN NUMBER) IS
887 SELECT 'x' from ahl_unit_deferrals_b
888 WHERE approval_status_code IN ('DRAFT','DEFERRAL_REJECTED')
889 AND unit_deferral_type = 'DEFERRAL'
890 AND unit_deferral_id = p_unit_deferral_id;
891
892 -- TAMAL -- Begin changes for ER #3356804
893 -- This cursor specifically checks whether the UE for an SR with MRs is available for deferral processing
894 -- Contrast this with the earlier cursor valid_deferral_csr, which handles SRs with no MRs and plain MRs
895 CURSOR valid_sr_deferral_csr (p_ue_id in number)
896 IS
897 SELECT 'x'
898 FROM ahl_workorders WO, ahl_visits_b VS, ahl_visit_tasks_b VST, ahl_unit_effectivities_b UE
899 WHERE WO.master_workorder_flag = 'Y'
900 /* to filter out draft / deleted WOs */
901 AND WO.STATUS_CODE NOT IN ( '17' , '22' )
902 /* to check whether visit available in client's organization */
903 AND WO.visit_id = VS.visit_id
904 AND VS.ORGANIZATION_ID IN
905 (SELECT ORGANIZATION_ID FROM org_organization_definitions
906 WHERE NVL (operating_unit, mo_global.get_current_org_id())
907 = mo_global.get_current_org_id())
908 AND VST.visit_id = VS.visit_id
909 /* */
910 AND WO.visit_task_id = VST.visit_task_id
911 AND VST.unit_effectivity_id = UE.unit_effectivity_id
912 AND VST.mr_id IS NULL
913 AND NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
914 AND UE.cs_incident_id IS NOT NULL
915 AND UE.unit_effectivity_id = p_ue_id;
916 -- TAMAL -- End changes for ER #3356804
917
918 -- R12: UMP Deferral
919 CURSOR valid_ue_csr (p_unit_effectivity_id IN NUMBER) IS
920 SELECT 'x'
921 FROM AHL_Unit_Effectivities_B UE
922 WHERE status_code IS NULL OR status_code = 'INIT-DUE'
923 AND unit_effectivity_id = p_unit_effectivity_id
924 AND NOT EXISTS (SELECT 'x'
925 FROM ahl_visit_tasks_b vts
926 WHERE vts.unit_effectivity_id = UE.unit_effectivity_id
927 AND NVL(vts.status_code,'x') IN ('PLANNED')
928 AND EXISTS (select 'x'
929 from ahl_visits_b vst, ahl_simulation_plans_b sim
930 where vst.simulation_plan_id = sim.simulation_plan_id(+)
931 and vst.visit_id = vts.visit_id
932 and sim.primary_plan_flag(+) = 'Y')
933 );
934
935 -- R12: UMP Deferral.
936 -- Validate user deferral type.
937 CURSOR validate_user_defer_csr(p_user_defer_type IN VARCHAR2) IS
938 SELECT 'x'
939 FROM fnd_lookup_values_vl
940 WHERE lookup_type = 'AHL_PRD_DEFERRAL_TYPE'
941 AND lookup_code = p_user_defer_type
942 AND enabled_flag = 'Y'
943 AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, sysdate+1);
944
945 -- SR Cancellation for nonserialized items.
946 CURSOR is_orig_ue_nonserial(p_ue_id in number)
947 IS
948 SELECT cii.serial_number
949 FROM ahl_unit_effectivities_b ue, csi_item_instances cii
950 WHERE unit_effectivity_id in (select originator_ue_id
951 from ahl_ue_relationships
952 where related_ue_id = p_ue_id)
953 AND ue.csi_item_instance_id = cii.instance_id
954 AND cii.quantity > 1
955 AND ue.object_type = 'SR';
956
957 l_exists VARCHAR2(1);
958 l_serial_number csi_item_instances.serial_number%TYPE;
959
960 BEGIN
961
962 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
963 fnd_log.string
964 (
965 fnd_log.level_procedure,
966 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header.begin',
967 'At the start of PLSQL procedure'
968 );
969 END IF;
970
971 IF(p_df_header_rec.operation_flag IS NOT NULL AND p_df_header_rec.operation_flag NOT IN(G_OP_CREATE,G_OP_UPDATE,G_OP_DELETE))THEN
972 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HD_OP_FLAG');
973 FND_MSG_PUB.ADD;
974 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
975 fnd_log.string
976 (
977 fnd_log.level_unexpected,
978 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
979 'Operation Flag is invalid in the header record'
980 );
981 END IF;
982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983 ELSE
984 -- R12: UMP Deferral.
985 -- First validate UE ID. If UMP Deferral, then UE must not be in a 'planned' status.
986 OPEN valid_ue_csr(p_df_header_rec.unit_effectivity_id);
987 FETCH valid_ue_csr INTO l_exists;
988 IF (valid_ue_csr%FOUND) THEN
989 -- ump deferral.
990 CLOSE valid_ue_csr;
991 ELSE
992
993 -- Production Validations.
994 -- TAMAL -- Begin changes for ER #3356804
995 -- This splitting of cursors is needed to handle the case of SRs with MRs v/s SRs without MRs
996 -- First check whether the UE corresponds to a SR with MRs that is available for deferral processing
997 -- If yes, fine
998 -- If no, then check whether the UE corresponds to a SR without MRs or plain MRs that is available for deferral processing
999 -- If yes, fine
1000 -- If no, raise unexpected error
1001 OPEN valid_sr_deferral_csr (p_df_header_rec.unit_effectivity_id);
1002 FETCH valid_sr_deferral_csr INTO l_exists;
1003 IF (valid_sr_deferral_csr%NOTFOUND)
1004 THEN
1005 CLOSE valid_sr_deferral_csr;
1006 OPEN valid_deferral_csr(p_df_header_rec.unit_effectivity_id);
1007 FETCH valid_deferral_csr INTO l_exists;
1008 IF(valid_deferral_csr%NOTFOUND)
1009 THEN
1010 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MRSR_STATUS');
1011 FND_MSG_PUB.ADD;
1012 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1013 THEN
1014 fnd_log.string
1015 (
1016 fnd_log.level_unexpected,
1017 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1018 'invalid mr or sr status invalid for update or delete 1'
1019 );
1020 END IF;
1021 CLOSE valid_deferral_csr;
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 ELSE
1024 CLOSE valid_deferral_csr;
1025 END IF; -- valid_deferral_csr
1026 ELSE
1027 CLOSE valid_sr_deferral_csr;
1028 END IF; -- valid_sr_deferral_csr
1029
1030 -- SR Cancellation - child MR cannot be deferred if originator ue is a SR
1031 -- based on non-serialized instance.
1032 IF (p_df_header_rec.skip_mr_flag = G_NO_FLAG) THEN
1033 OPEN is_orig_ue_nonserial(p_df_header_rec.unit_effectivity_id);
1034 FETCH is_orig_ue_nonserial INTO l_serial_number;
1035 IF (is_orig_ue_nonserial%FOUND AND l_serial_number IS NULL) THEN
1036 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_NS_DF_INVALID');
1037 FND_MSG_PUB.ADD;
1038 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1039 fnd_log.string
1040 (
1041 fnd_log.level_unexpected,
1042 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1043 'cannot defer child MR when parent MR is based on non-serial SR'
1044 );
1045 END IF;
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047
1048 END IF;
1049
1050 CLOSE is_orig_ue_nonserial;
1051 END IF;
1052
1053 END IF; -- valid_ue_csr
1054
1055
1056 /*-- check whether this MR or SR (basically UE) available for deferral processing
1057 OPEN valid_deferral_csr(p_df_header_rec.unit_effectivity_id);
1058 FETCH valid_deferral_csr INTO l_exists;
1059 IF(valid_deferral_csr%NOTFOUND)THEN
1060 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MRSR_STATUS');
1061 FND_MSG_PUB.ADD;
1062 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1063 fnd_log.string
1064 (
1065 fnd_log.level_unexpected,
1066 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1067 'invalid mr or sr status invalid for update or delete 1'
1068 );
1069 END IF;
1070 CLOSE valid_deferral_csr;
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073 CLOSE valid_deferral_csr;*/
1074 -- TAMAL -- End changes for ER #3356804
1075 END IF;
1076
1077
1078 IF(p_df_header_rec.operation_flag = G_OP_CREATE) THEN
1079 ----------------VALIDATION for DEFERRAL RECORD CREATION----------------
1080 -- check whether any record alreasy exists for the UE, IF yes raise error
1081 OPEN deferral_rec_exists_csr(p_df_header_rec.unit_effectivity_id);
1082 FETCH deferral_rec_exists_csr INTO l_exists;
1083 IF(deferral_rec_exists_csr%FOUND)THEN
1084 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HD_OP_REXIST');
1085 FND_MSG_PUB.ADD;
1086 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1087 fnd_log.string
1088 (
1089 fnd_log.level_unexpected,
1090 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1091 'Deferral record exist while operation flag is create'
1092 );
1093 END IF;
1094 CLOSE deferral_rec_exists_csr;
1095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1096 END IF;
1097 CLOSE deferral_rec_exists_csr;
1098
1099 -- check whether status is nothing but draft while creating record for deferral
1100 IF(p_df_header_rec.approval_status_code <> 'DRAFT')THEN
1101 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_APPR_STATUS');
1102 FND_MSG_PUB.ADD;
1103 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1104 fnd_log.string
1105 (
1106 fnd_log.level_unexpected,
1107 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1108 'approval status is not DRAFT while creating a deferral record'
1109 );
1110 END IF;
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 END IF;
1113
1114 -- update and delete validations
1115 ELSIF (p_df_header_rec.operation_flag IN (G_OP_UPDATE,G_OP_DELETE)) THEN
1116 -- check whether deferral record can be updated or deleted
1117 OPEN valid_deferral_up_csr(p_df_header_rec.unit_deferral_id);
1118 FETCH valid_deferral_up_csr INTO l_exists;
1119 IF(valid_deferral_up_csr%NOTFOUND)THEN
1120 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MRSR_STATUS');
1121 FND_MSG_PUB.ADD;
1122 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1123 fnd_log.string
1124 (
1125 fnd_log.level_unexpected,
1126 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1127 'approval status invalid for update or delete : ue_id : ' || p_df_header_rec.unit_deferral_id
1128 );
1129 END IF;
1130 CLOSE valid_deferral_up_csr;
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133 CLOSE valid_deferral_up_csr;
1134 END IF;
1135
1136 --Data validation for creates and updates.
1137 IF (p_df_header_rec.operation_flag IN (G_OP_CREATE, G_OP_UPDATE)) THEN
1138
1139 /*-- check MR status now
1140 IF(p_df_header_rec.deferral_type = G_DEFERRAL_TYPE_MR) THEN
1141 OPEN valid_mr_csr(p_df_header_rec.unit_effectivity_id);
1142 FETCH valid_mr_csr INTO l_exists;
1143 IF(valid_mr_csr%NOTFOUND)THEN
1144 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MR_TERM');
1145 FND_MSG_PUB.ADD;
1146 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1147 fnd_log.string
1148 (
1149 fnd_log.level_error,
1150 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1151 'Associated MR has been terminated in FMP'
1152 );
1153 END IF;
1154 CLOSE valid_mr_csr;
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157 CLOSE valid_mr_csr;
1158 END IF;*/
1159
1160 -- validate deferral reason codes
1161 IF(p_df_header_rec.defer_reason_code IS NOT NULL)THEN
1162 validate_reason_codes(p_df_header_rec.defer_reason_code);
1163 END IF;
1164 -- general validations for flags
1165 IF(p_df_header_rec.skip_mr_flag NOT IN(G_YES_FLAG,G_NO_FLAG))THEN
1166 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SKIP_FLAG');
1167 FND_MSG_PUB.ADD;
1168 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1169 fnd_log.string
1170 (
1171 fnd_log.level_unexpected,
1172 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1173 'Skip flag is not Y or N'
1174 );
1175 END IF;
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178 IF(p_df_header_rec.affect_due_calc_flag NOT IN(G_YES_FLAG,G_NO_FLAG))THEN
1179 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_AFFDUE_FLAG');
1180 FND_MSG_PUB.ADD;
1181 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1182 fnd_log.string
1183 (
1184 fnd_log.level_unexpected,
1185 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1186 'Affect Due Calc Flag is not Y or N'
1187 );
1188 END IF;
1189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1190 END IF;
1191
1192 -- Check user deferral type.(R12: UMP Deferral)
1193 IF (p_df_header_rec.user_deferral_type_code IS NOT NULL AND
1194 p_df_header_rec.user_deferral_type_code <> FND_API.G_MISS_CHAR) THEN
1195 OPEN validate_user_defer_csr(p_df_header_rec.user_deferral_type_code);
1196 FETCH validate_user_defer_csr INTO l_exists;
1197 IF (validate_user_defer_csr%NOTFOUND) THEN
1198 FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_DEF_TYPE');
1199 FND_MESSAGE.Set_token('CODE', p_df_header_rec.user_deferral_type_code);
1200 FND_MSG_PUB.ADD;
1201 END IF;
1202 CLOSE validate_user_defer_csr;
1203 END IF;
1204
1205 -- MR/SR specific validations
1206 IF(p_df_header_rec.deferral_type = G_DEFERRAL_TYPE_MR) THEN
1207 -- check validity of skip MR flag for MR
1208 IF(p_df_header_rec.mr_repetitive_flag = G_NO_FLAG AND p_df_header_rec.manually_planned_flag = G_NO_FLAG)THEN
1209 IF(p_df_header_rec.skip_mr_flag = G_YES_FLAG) THEN
1210 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SKIP_FLAG');
1211 FND_MSG_PUB.ADD;
1212 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1213 fnd_log.string
1214 (
1215 fnd_log.level_unexpected,
1216 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1217 'Skip MR flag is Y while MR is not repetitive'
1218 );
1219 END IF;
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222 IF(p_df_header_rec.affect_due_calc_flag = G_NO_FLAG AND p_df_header_rec.reset_counter_flag = G_YES_FLAG) THEN
1223 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_AFFDUE_FLAG');
1224 FND_MSG_PUB.ADD;
1225 END IF;
1226 ELSE -- MR is repetitive or manually planned
1227 IF(p_df_header_rec.skip_mr_flag = G_NO_FLAG) THEN
1228 IF(p_df_header_rec.affect_due_calc_flag = G_NO_FLAG AND
1229 p_df_header_rec.reset_counter_flag = G_YES_FLAG) THEN
1230 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_AFFDUE_FLAG');
1231 FND_MSG_PUB.ADD;
1232 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1233 fnd_log.string
1234 (
1235 fnd_log.level_unexpected,
1236 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1237 'MR has resettable counters so affect due cal flag cant be N '
1238 );
1239 END IF;
1240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1241 END IF;
1242 ELSE
1243 IF(p_df_header_rec.affect_due_calc_flag = G_YES_FLAG) THEN
1244 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_AFFDUE_FLAG');
1245 FND_MSG_PUB.ADD;
1246 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1247 fnd_log.string
1248 (
1249 fnd_log.level_unexpected,
1250 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1251 'MR skip flag is Y so affect due cal flag can not be Y '
1252 );
1253 END IF;
1254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1255 END IF;
1256 END IF;
1257 END IF;
1258 ELSIF (p_df_header_rec.deferral_type = G_DEFERRAL_TYPE_SR) THEN
1259 /* SR cancellation allowed for non-serialized items.
1260 IF(p_df_header_rec.skip_mr_flag = G_YES_FLAG) THEN
1261 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SKIP_FLAG');
1262 FND_MSG_PUB.ADD;
1263 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1264 fnd_log.string
1265 (
1266 fnd_log.level_unexpected,
1267 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1268 'For SR, skip flag cant be Y '
1269 );
1270 END IF;
1271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1272 END IF;
1273 */
1274 IF(p_df_header_rec.affect_due_calc_flag = G_NO_FLAG)THEN
1275 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_AFFDUE_FLAG');
1276 FND_MSG_PUB.ADD;
1277 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1278 fnd_log.string
1279 (
1280 fnd_log.level_unexpected,
1281 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1282 'For SR, affect due date flag can not be N '
1283 );
1284 END IF;
1285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1286 END IF;
1287 END IF;
1288 -- set due date and deferral effective on date validations
1289 -- part of this validation has been moved for post processing
1290 IF(p_df_header_rec.skip_mr_flag = G_NO_FLAG AND
1291 (p_df_header_rec.set_due_date IS NOT NULL AND
1292 trunc(p_df_header_rec.set_due_date) < trunc(SYSDATE))) THEN
1293 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SET_DUE_DT');
1294 FND_MESSAGE.Set_Token('SET_DUE_DATE',p_df_header_rec.set_due_date);
1295 FND_MSG_PUB.ADD;
1296 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1297 fnd_log.string
1298 (
1299 fnd_log.level_error,
1300 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1301 'Set due date cant be null or less than system date '
1302 );
1303 END IF;
1304 END IF;
1305 IF(p_df_header_rec.deferral_effective_on IS NULL OR
1306 p_df_header_rec.deferral_effective_on > SYSDATE) THEN
1307 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_DF_EFF_DT');
1308 FND_MESSAGE.Set_Token('DEFERRAL_EFFECTIVE_ON',p_df_header_rec.deferral_effective_on);
1309 FND_MSG_PUB.ADD;
1310 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1311 fnd_log.string
1312 (
1313 fnd_log.level_error,
1314 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header',
1315 'Deferral Effective On Date can not be null or greater than system date '
1316 );
1317 END IF;
1318 END IF;
1319 END IF;
1320
1321 -- raise expected error
1322 IF(FND_MSG_PUB.count_msg > 0)THEN
1323 RAISE FND_API.G_EXC_ERROR;
1324 END IF;
1325
1326 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1327 fnd_log.string
1328 (
1329 fnd_log.level_procedure,
1330 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_header.end',
1331 'At the end of PLSQL procedure'
1332 );
1333 END IF;
1334
1335 END validate_df_header;
1336 --------------------------------------------------------------------------------------------------------
1337 -- Reason code validation
1338 --------------------------------------------------------------------------------------------------------
1339
1340 PROCEDURE validate_reason_codes(
1341 p_defer_reason_code IN VARCHAR2) IS
1342
1343 l_temp1 NUMBER := 1;
1344 l_temp2 NUMBER;
1345 l_index NUMBER := 1;
1346 exit_flag boolean := false;
1347 l_string VARCHAR2(30);
1348
1349 CURSOR val_reason_code_csr(p_reason_code IN VARCHAR2) IS
1350 SELECT 'x' FROM fnd_lookup_values_vl fnd
1351 WHERE fnd.lookup_code = p_reason_code
1352 AND fnd.lookup_type = 'AHL_PRD_DF_REASON_TYPES';
1353
1354 l_exists VARCHAR2(1);
1355
1356 BEGIN
1357 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1358 fnd_log.string
1359 (
1360 fnd_log.level_procedure,
1361 'ahl.plsql.AHL_PRD_DF_PVT.validate_reason_codes.begin',
1362 'At the start of PLSQL procedure'
1363 );
1364 END IF;
1365
1366 LOOP
1367 l_temp2 := instr(p_defer_reason_code,G_REASON_CODE_DELIM,1,l_index);
1368 IF(l_temp2 = 0) THEN
1369 l_string := substr(p_defer_reason_code,l_temp1);
1370 OPEN val_reason_code_csr(l_string);
1371 FETCH val_reason_code_csr INTO l_exists;
1372 IF(val_reason_code_csr%NOTFOUND) THEN
1373 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_RSN_CODE');
1374 FND_MSG_PUB.ADD;
1375 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1376 fnd_log.string
1377 (
1378 fnd_log.level_unexpected,
1379 'ahl.plsql.AHL_PRD_DF_PVT.validate_reason_codes',
1380 'Reason code is not defined in lookups '
1381 );
1382 END IF;
1383 CLOSE val_reason_code_csr;
1384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1385 END IF;
1386 CLOSE val_reason_code_csr;
1387 exit_flag := true;
1388 ELSE
1389 l_string := substr(p_defer_reason_code,l_temp1,l_temp2 - l_temp1);
1390 OPEN val_reason_code_csr(l_string);
1391 FETCH val_reason_code_csr INTO l_exists;
1392 IF(val_reason_code_csr%NOTFOUND) THEN
1393 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_RSN_CODE');
1394 FND_MSG_PUB.ADD;
1395 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1396 fnd_log.string
1397 (
1398 fnd_log.level_unexpected,
1399 'ahl.plsql.AHL_PRD_DF_PVT.validate_reason_codes',
1400 'Reason code is not defined in lookups '
1401 );
1402 END IF;
1403 CLOSE val_reason_code_csr;
1404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1405 END IF;
1406 CLOSE val_reason_code_csr;
1407 l_index := l_index + 1;
1408 l_temp1 := l_temp2 + 1;
1409 END IF;
1410 EXIT WHEN exit_flag;
1411 END LOOP;
1412
1413 IF(FND_MSG_PUB.count_msg > 0)THEN
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 END IF;
1416
1417 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1418 fnd_log.string
1419 (
1420 fnd_log.level_procedure,
1421 'ahl.plsql.AHL_PRD_DF_PVT.validate_reason_codes.end',
1422 'At the end of PLSQL procedure'
1423 );
1424 END IF;
1425
1426 END validate_reason_codes;
1427
1428
1429 ---------------------------------------------------------------------------------------------------------
1430 -- defaulting values in case of create and update mode
1431 ---------------------------------------------------------------------------------------------------------
1432
1433 PROCEDURE default_unchanged_df_header(
1434 p_x_df_header_rec IN OUT NOCOPY AHL_PRD_DF_PVT.df_header_rec_type)IS
1435
1436 CURSOR df_header_csr(p_unit_deferral_id IN NUMBER, p_object_version_number IN NUMBER) IS
1437 SELECT unit_effectivity_id, unit_deferral_type, approval_status_code, defer_reason_code,skip_mr_flag,
1438 affect_due_calc_flag, set_due_date, deferral_effective_on,remarks,approver_notes,attribute_category, attribute1,
1439 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
1440 attribute8, attribute9, attribute10, attribute11, attribute12,
1441 attribute13, attribute14, attribute15
1442 FROM ahl_unit_deferrals_vl
1443 WHERE object_version_number= p_object_version_number
1444 AND unit_deferral_id = p_unit_deferral_id;
1445
1446 l_df_header_rec AHL_PRD_DF_PVT.df_header_Rec_type;
1447
1448 BEGIN
1449 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1450 fnd_log.string
1451 (
1452 fnd_log.level_procedure,
1453 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header.begin',
1454 'At the start of PLSQL procedure'
1455 );
1456 END IF;
1457
1458 IF(p_x_df_header_rec.operation_flag IS NULL OR p_x_df_header_rec.operation_flag = G_OP_UPDATE) THEN
1459 OPEN df_header_csr(p_x_df_header_rec.unit_deferral_id, p_x_df_header_rec.object_version_number);
1460 FETCH df_header_csr INTO l_df_header_rec.unit_effectivity_id, l_df_header_rec.unit_deferral_type,
1461 l_df_header_rec.approval_status_code,l_df_header_rec.defer_reason_code,
1462 l_df_header_rec.skip_mr_flag,l_df_header_rec.affect_due_calc_flag,l_df_header_rec.set_due_date,
1463 l_df_header_rec.deferral_effective_on,l_df_header_rec.remarks,l_df_header_rec.approver_notes,
1464 l_df_header_rec.attribute_category,l_df_header_rec.attribute1,l_df_header_rec.attribute2,
1465 l_df_header_rec.attribute3, l_df_header_rec.attribute4, l_df_header_rec.attribute5,
1466 l_df_header_rec.attribute6, l_df_header_rec.attribute7, l_df_header_rec.attribute8,
1467 l_df_header_rec.attribute9, l_df_header_rec.attribute10, l_df_header_rec.attribute11,
1468 l_df_header_rec.attribute12, l_df_header_rec.attribute13, l_df_header_rec.attribute14,
1469 l_df_header_rec.attribute15;
1470 IF (df_header_csr%NOTFOUND) THEN
1471 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INVOP_HREC_MISS');
1472 FND_MSG_PUB.ADD;
1473 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1474 fnd_log.string
1475 (
1476 fnd_log.level_error,
1477 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header',
1478 'Missing Deferral Header Record'
1479 );
1480 END IF;
1481 ELSE
1482 IF (p_x_df_header_rec.unit_effectivity_id IS NULL) THEN
1483 p_x_df_header_rec.unit_effectivity_id := l_df_header_rec.unit_effectivity_id;
1484 ELSIF(p_x_df_header_rec.unit_effectivity_id <> l_df_header_rec.unit_effectivity_id ) THEN
1485 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_UE');
1486 FND_MSG_PUB.ADD;
1487 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1488 fnd_log.string
1489 (
1490 fnd_log.level_unexpected,
1491 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header',
1492 'Unit Effectivity ID does not match with deferral header record'
1493 );
1494 END IF;
1495 CLOSE df_header_csr;
1496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1497 END IF;
1498
1499 IF (p_x_df_header_rec.unit_deferral_type IS NULL) THEN
1500 p_x_df_header_rec.unit_deferral_type := l_df_header_rec.unit_deferral_type;
1501 ELSIF(p_x_df_header_rec.unit_deferral_type <> l_df_header_rec.unit_deferral_type) THEN
1502 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_UDF_TYPE');
1503 FND_MSG_PUB.ADD;
1504 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1505 fnd_log.string
1506 (
1507 fnd_log.level_unexpected,
1508 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header',
1509 'Unit Deferral Type does not match with deferral header record'
1510 );
1511 END IF;
1512 CLOSE df_header_csr;
1513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1514 END IF;
1515
1516 IF (p_x_df_header_rec.approval_status_code IS NULL) THEN
1517 p_x_df_header_rec.approval_status_code := l_df_header_rec.approval_status_code;
1518 ELSIF(p_x_df_header_rec.approval_status_code <> l_df_header_rec.approval_status_code) THEN
1519 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_APPR_STATUS');
1520 FND_MSG_PUB.ADD;
1521 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1522 fnd_log.string
1523 (
1524 fnd_log.level_unexpected,
1525 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header',
1526 'Approval status code can not be modified'
1527 );
1528 END IF;
1529 CLOSE df_header_csr;
1530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1531 END IF;
1532
1533 IF (p_x_df_header_rec.defer_reason_code IS NULL) THEN
1534 p_x_df_header_rec.defer_reason_code := l_df_header_rec.defer_reason_code;
1535 ELSIF(p_x_df_header_rec.defer_reason_code = FND_API.G_MISS_CHAR) THEN
1536 p_x_df_header_rec.defer_reason_code := NULL;
1537 END IF;
1538
1539 IF (p_x_df_header_rec.skip_mr_flag IS NULL) THEN
1540 p_x_df_header_rec.skip_mr_flag := l_df_header_rec.skip_mr_flag;
1541 ELSIF(p_x_df_header_rec.skip_mr_flag = FND_API.G_MISS_CHAR) THEN
1542 p_x_df_header_rec.skip_mr_flag := G_NO_FLAG;
1543 END IF;
1544
1545 IF (p_x_df_header_rec.affect_due_calc_flag IS NULL) THEN
1546 p_x_df_header_rec.affect_due_calc_flag := l_df_header_rec.affect_due_calc_flag;
1547 ELSIF(p_x_df_header_rec.affect_due_calc_flag = FND_API.G_MISS_CHAR) THEN
1548 p_x_df_header_rec.affect_due_calc_flag := G_NO_FLAG;
1549 END IF;
1550
1551 IF (p_x_df_header_rec.set_due_date IS NULL) THEN
1552 p_x_df_header_rec.set_due_date := l_df_header_rec.set_due_date;
1553 ELSIF(p_x_df_header_rec.set_due_date = FND_API.G_MISS_DATE) THEN
1554 p_x_df_header_rec.set_due_date := NULL;
1555 END IF;
1556
1557 IF (p_x_df_header_rec.deferral_effective_on IS NULL) THEN
1558 p_x_df_header_rec.deferral_effective_on := l_df_header_rec.deferral_effective_on;
1559 ELSIF(p_x_df_header_rec.deferral_effective_on = FND_API.G_MISS_DATE) THEN
1560 p_x_df_header_rec.deferral_effective_on := NULL;
1561 END IF;
1562
1563 IF (p_x_df_header_rec.remarks IS NULL) THEN
1564 p_x_df_header_rec.remarks := l_df_header_rec.remarks;
1565 ELSIF(p_x_df_header_rec.remarks = FND_API.G_MISS_CHAR) THEN
1566 p_x_df_header_rec.remarks := NULL;
1567 END IF;
1568
1569 IF (p_x_df_header_rec.approver_notes IS NULL) THEN
1570 p_x_df_header_rec.approver_notes := l_df_header_rec.approver_notes;
1571 ELSIF(p_x_df_header_rec.approver_notes <> l_df_header_rec.approver_notes) THEN
1572 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_APPR_NOTES');
1573 FND_MSG_PUB.ADD;
1574 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1575 fnd_log.string
1576 (
1577 fnd_log.level_unexpected,
1578 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header',
1579 'approver notes can not be updated by this API'
1580 );
1581 END IF;
1582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1583 END IF;
1584
1585 IF (p_x_df_header_rec.attribute_category IS NULL) THEN
1586 p_x_df_header_rec.attribute_category := l_df_header_rec.attribute_category;
1587 ELSIF(p_x_df_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1588 p_x_df_header_rec.attribute_category := NULL;
1589 END IF;
1590
1591 IF (p_x_df_header_rec.attribute1 IS NULL) THEN
1592 p_x_df_header_rec.attribute1 := l_df_header_rec.attribute1;
1593 ELSIF(p_x_df_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1594 p_x_df_header_rec.attribute1 := NULL;
1595 END IF;
1596
1597 IF (p_x_df_header_rec.attribute2 IS NULL) THEN
1598 p_x_df_header_rec.attribute2 := l_df_header_rec.attribute2;
1599 ELSIF(p_x_df_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1600 p_x_df_header_rec.attribute2 := NULL;
1601 END IF;
1602
1603 IF (p_x_df_header_rec.attribute3 IS NULL) THEN
1604 p_x_df_header_rec.attribute3 := l_df_header_rec.attribute3;
1605 ELSIF(p_x_df_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1606 p_x_df_header_rec.attribute3 := NULL;
1607 END IF;
1608
1609 IF (p_x_df_header_rec.attribute4 IS NULL) THEN
1610 p_x_df_header_rec.attribute4 := l_df_header_rec.attribute4;
1611 ELSIF(p_x_df_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1612 p_x_df_header_rec.attribute4 := NULL;
1613 END IF;
1614
1615 IF (p_x_df_header_rec.attribute5 IS NULL) THEN
1616 p_x_df_header_rec.attribute5 := l_df_header_rec.attribute5;
1617 ELSIF(p_x_df_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1618 p_x_df_header_rec.attribute5 := NULL;
1619 END IF;
1620
1621 IF (p_x_df_header_rec.attribute6 IS NULL) THEN
1622 p_x_df_header_rec.attribute6 := l_df_header_rec.attribute6;
1623 ELSIF(p_x_df_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1624 p_x_df_header_rec.attribute6 := NULL;
1625 END IF;
1626
1627 IF (p_x_df_header_rec.attribute7 IS NULL) THEN
1628 p_x_df_header_rec.attribute7 := l_df_header_rec.attribute7;
1629 ELSIF(p_x_df_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1630 p_x_df_header_rec.attribute7 := NULL;
1631 END IF;
1632
1633 IF (p_x_df_header_rec.attribute8 IS NULL) THEN
1634 p_x_df_header_rec.attribute8 := l_df_header_rec.attribute8;
1635 ELSIF(p_x_df_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1636 p_x_df_header_rec.attribute8 := NULL;
1637 END IF;
1638
1639 IF (p_x_df_header_rec.attribute9 IS NULL) THEN
1640 p_x_df_header_rec.attribute9 := l_df_header_rec.attribute9;
1641 ELSIF(p_x_df_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1642 p_x_df_header_rec.attribute9 := NULL;
1643 END IF;
1644
1645 IF (p_x_df_header_rec.attribute10 IS NULL) THEN
1646 p_x_df_header_rec.attribute10 := l_df_header_rec.attribute10;
1647 ELSIF(p_x_df_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1648 p_x_df_header_rec.attribute10 := NULL;
1649 END IF;
1650
1651 IF (p_x_df_header_rec.attribute11 IS NULL) THEN
1652 p_x_df_header_rec.attribute11 := l_df_header_rec.attribute11;
1653 ELSIF(p_x_df_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1654 p_x_df_header_rec.attribute11 := NULL;
1655 END IF;
1656
1657 IF (p_x_df_header_rec.attribute12 IS NULL) THEN
1658 p_x_df_header_rec.attribute12 := l_df_header_rec.attribute12;
1659 ELSIF(p_x_df_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1660 p_x_df_header_rec.attribute12 := NULL;
1661 END IF;
1662
1663 IF (p_x_df_header_rec.attribute13 IS NULL) THEN
1664 p_x_df_header_rec.attribute13 := l_df_header_rec.attribute13;
1665 ELSIF(p_x_df_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1666 p_x_df_header_rec.attribute13 := NULL;
1667 END IF;
1668
1669 IF (p_x_df_header_rec.attribute14 IS NULL) THEN
1670 p_x_df_header_rec.attribute14 := l_df_header_rec.attribute14;
1671 ELSIF(p_x_df_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1672 p_x_df_header_rec.attribute14 := NULL;
1673 END IF;
1674
1675 IF (p_x_df_header_rec.attribute15 IS NULL) THEN
1676 p_x_df_header_rec.attribute15 := l_df_header_rec.attribute15;
1677 ELSIF(p_x_df_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1678 p_x_df_header_rec.attribute15 := NULL;
1679 END IF;
1680
1681 END IF;
1682 CLOSE df_header_csr;
1683 ELSIF (p_x_df_header_rec.operation_flag = G_OP_CREATE) THEN
1684
1685 IF (p_x_df_header_rec.unit_effectivity_id IS NULL OR
1686 p_x_df_header_rec.unit_effectivity_id = FND_API.G_MISS_NUM) THEN
1687 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_UE');
1688 FND_MSG_PUB.ADD;
1689 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1690 fnd_log.string
1691 (
1692 fnd_log.level_unexpected,
1693 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header',
1694 'Unit effectivity ID can not be null while creating deferral header record'
1695 );
1696 END IF;
1697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1698 END IF;
1699
1700 IF (p_x_df_header_rec.unit_deferral_type IS NULL OR
1701 p_x_df_header_rec.unit_deferral_type = FND_API.G_MISS_CHAR) THEN
1702 p_x_df_header_rec.unit_deferral_type := 'DEFERRAL';
1703 END IF;
1704
1705 IF(p_x_df_header_rec.approval_status_code IS NULL OR
1706 p_x_df_header_rec.approval_status_code = FND_API.G_MISS_CHAR) THEN
1707 p_x_df_header_rec.approval_status_code := 'DRAFT';
1708 END IF;
1709
1710 IF(p_x_df_header_rec.defer_reason_code = FND_API.G_MISS_CHAR) THEN
1711 p_x_df_header_rec.defer_reason_code := NULL;
1712 END IF;
1713
1714 IF(p_x_df_header_rec.skip_mr_flag IS NULL OR
1715 p_x_df_header_rec.skip_mr_flag = FND_API.G_MISS_CHAR) THEN
1716 p_x_df_header_rec.skip_mr_flag := G_NO_FLAG;
1717 END IF;
1718
1719 IF(p_x_df_header_rec.affect_due_calc_flag IS NULL OR
1720 p_x_df_header_rec.affect_due_calc_flag = FND_API.G_MISS_CHAR) THEN
1721 p_x_df_header_rec.affect_due_calc_flag := G_YES_FLAG;
1722 END IF;
1723
1724 IF(p_x_df_header_rec.set_due_date = FND_API.G_MISS_DATE) THEN
1725 p_x_df_header_rec.set_due_date := NULL;
1726 END IF;
1727
1728 IF(p_x_df_header_rec.deferral_effective_on = FND_API.G_MISS_DATE) THEN
1729 p_x_df_header_rec.deferral_effective_on := NULL;
1730 END IF;
1731
1732 IF(p_x_df_header_rec.remarks = FND_API.G_MISS_CHAR) THEN
1733 p_x_df_header_rec.remarks := NULL;
1734 END IF;
1735
1736 IF(p_x_df_header_rec.approver_notes = FND_API.G_MISS_CHAR) THEN
1737 p_x_df_header_rec.approver_notes := NULL;
1738 END IF;
1739
1740 IF(p_x_df_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1741 p_x_df_header_rec.attribute_category := NULL;
1742 END IF;
1743
1744 IF(p_x_df_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1745 p_x_df_header_rec.attribute1 := NULL;
1746 END IF;
1747
1748 IF(p_x_df_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1749 p_x_df_header_rec.attribute2 := NULL;
1750 END IF;
1751
1752 IF(p_x_df_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1753 p_x_df_header_rec.attribute3 := NULL;
1754 END IF;
1755
1756 IF(p_x_df_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1757 p_x_df_header_rec.attribute4 := NULL;
1758 END IF;
1759
1760 IF(p_x_df_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1761 p_x_df_header_rec.attribute5 := NULL;
1762 END IF;
1763
1764 IF(p_x_df_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1765 p_x_df_header_rec.attribute6 := NULL;
1766 END IF;
1767
1768 IF(p_x_df_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1769 p_x_df_header_rec.attribute7 := NULL;
1770 END IF;
1771
1772 IF(p_x_df_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1773 p_x_df_header_rec.attribute8 := NULL;
1774 END IF;
1775
1776 IF(p_x_df_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1777 p_x_df_header_rec.attribute9 := NULL;
1778 END IF;
1779
1780 IF(p_x_df_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1781 p_x_df_header_rec.attribute10 := NULL;
1782 END IF;
1783
1784 IF(p_x_df_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1785 p_x_df_header_rec.attribute11 := NULL;
1786 END IF;
1787
1788 IF(p_x_df_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1789 p_x_df_header_rec.attribute12 := NULL;
1790 END IF;
1791
1792 IF(p_x_df_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1793 p_x_df_header_rec.attribute13 := NULL;
1794 END IF;
1795
1796 IF(p_x_df_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1797 p_x_df_header_rec.attribute14 := NULL;
1798 END IF;
1799
1800 IF(p_x_df_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1801 p_x_df_header_rec.attribute15 := NULL;
1802 END IF;
1803 END IF;
1804
1805 -- raise expected error
1806 IF(FND_MSG_PUB.count_msg > 0)THEN
1807 RAISE FND_API.G_EXC_ERROR;
1808 END IF;
1809
1810 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1811 fnd_log.string
1812 (
1813 fnd_log.level_procedure,
1814 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_header.end',
1815 'At the end of PLSQL procedure'
1816 );
1817 END IF;
1818
1819 END default_unchanged_df_header;
1820
1821 PROCEDURE process_df_schedules(
1822 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
1823 p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type)IS
1824
1825 CURSOR counter_id_csr(p_counter_name IN VARCHAR2,p_unit_effectivity_id IN NUMBER) IS
1826 --SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE
1827 SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE -- Undid App usage BLIND changes
1828 WHERE UPPER(co.counter_name) like UPPER(p_counter_name)
1829 AND co.customer_product_id = ue.csi_item_instance_id
1830 AND UE.unit_effectivity_id = p_unit_effectivity_id;
1831
1832 l_counter_id NUMBER;
1833
1834 BEGIN
1835
1836 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1837 fnd_log.string
1838 (
1839 fnd_log.level_procedure,
1840 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules.begin',
1841 'At the start of PLSQL procedure'
1842 );
1843 END IF;
1844 -- record dml validations and key requirement validations
1845 FOR i IN p_x_df_schedules_tbl.FIRST..p_x_df_schedules_tbl.LAST LOOP
1846 -- key requirements
1847 IF(p_x_df_schedules_tbl(i).operation_flag NOT IN (G_OP_CREATE,G_OP_UPDATE,G_OP_DELETE)) THEN
1848 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_INV_OP');
1849 FND_MSG_PUB.ADD;
1850 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1851 fnd_log.string
1852 (
1853 fnd_log.level_unexpected,
1854 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1855 'Operation Flag is invalid in the schedule record : ' || i
1856 );
1857 END IF;
1858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1859 ELSIF(p_x_df_schedules_tbl(i).operation_flag IN (G_OP_UPDATE,G_OP_DELETE)) THEN
1860 IF(p_x_df_schedules_tbl(i).unit_threshold_id IS NULL OR
1861 p_x_df_schedules_tbl(i).object_version_number IS NULL) THEN
1862 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_REC_KEY_MISS');
1863 FND_MSG_PUB.ADD;
1864 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1865 fnd_log.string
1866 (
1867 fnd_log.level_unexpected,
1868 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1869 'Object version number or key missing in schedule record : ' || i
1870 );
1871 END IF;
1872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1873 ELSIF (p_x_df_schedules_tbl(i).unit_deferral_id IS NOT NULL AND
1874 p_x_df_schedules_tbl(i).unit_deferral_id <> p_df_header_rec.unit_deferral_id) THEN
1875 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_HDR_MISS');
1876 FND_MSG_PUB.ADD;
1877 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1878 fnd_log.string
1879 (
1880 fnd_log.level_unexpected,
1881 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1882 'Unit Deferral ID does not match in schedule record : ' || i
1883 );
1884 END IF;
1885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1886 END IF;
1887 ELSIF(p_x_df_schedules_tbl(i).operation_flag = G_OP_CREATE) THEN
1888 IF(p_x_df_schedules_tbl(i).unit_threshold_id IS NOT NULL OR
1889 p_x_df_schedules_tbl(i).object_version_number IS NOT NULL) THEN
1890 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_INV_OP');
1891 FND_MSG_PUB.ADD;
1892 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1893 fnd_log.string
1894 (
1895 fnd_log.level_unexpected,
1896 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1897 'For Create Operation, Unit Threshold ID or Object Version Number is not null in schedule record : ' || i
1898 );
1899 END IF;
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902 IF(p_x_df_schedules_tbl(i).unit_deferral_id IS NULL OR
1903 p_x_df_schedules_tbl(i).unit_deferral_id = FND_API.G_MISS_NUM) THEN
1904 p_x_df_schedules_tbl(i).unit_deferral_id := p_df_header_rec.unit_deferral_id;
1905 ELSIF(p_x_df_schedules_tbl(i).unit_deferral_id <> p_df_header_rec.unit_deferral_id)THEN
1906 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_HDR_MISS');
1907 FND_MSG_PUB.ADD;
1908 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1909 fnd_log.string
1910 (
1911 fnd_log.level_unexpected,
1912 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1913 'For Create Operation, Header Unit Deferral ID does not match with same in schedule record : ' || i
1914 );
1915 END IF;
1916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1917 END IF;
1918 END IF;
1919 -- value to id conversion for counter name
1920 IF(G_MODULE_TYPE = 'JSP' AND p_x_df_schedules_tbl(i).operation_flag IN (G_OP_CREATE,G_OP_UPDATE))THEN
1921 OPEN counter_id_csr(p_x_df_schedules_tbl(i).counter_name, p_df_header_rec.unit_effectivity_id);
1922 FETCH counter_id_csr INTO l_counter_id;
1923 IF(counter_id_csr%NOTFOUND)THEN
1924 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNT_NAME');
1925 FND_MESSAGE.Set_Token('COUNTER_NAME',p_x_df_schedules_tbl(i).counter_name);
1926 FND_MSG_PUB.ADD;
1927 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1928 fnd_log.string
1929 (
1930 fnd_log.level_error,
1931 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1932 'Invalid Counter name for schedule record : ' || i
1933 );
1934 END IF;
1935 ELSE
1936 p_x_df_schedules_tbl(i).counter_id := l_counter_id;
1937 END IF;
1938 CLOSE counter_id_csr;
1939 END IF;
1940 END LOOP;
1941
1942 -- raise expected error
1943 IF(FND_MSG_PUB.count_msg > 0)THEN
1944 RAISE FND_API.G_EXC_ERROR;
1945 END IF;
1946
1947 default_unchanged_df_schedules(p_x_df_schedules_tbl => p_x_df_schedules_tbl);
1948
1949 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1950 fnd_log.string
1951 (
1952 fnd_log.level_statement,
1953 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules',
1954 'Logging deferral schedule records prior to validations'
1955 );
1956 log_df_schedules(p_df_schedules_tbl => p_x_df_schedules_tbl);
1957 END IF;
1958
1959 validate_df_schedules(
1960 p_df_header_rec => p_df_header_rec,
1961 p_df_schedules_tbl => p_x_df_schedules_tbl
1962 );
1963
1964 IF(FND_MSG_PUB.count_msg > 0)THEN
1965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1966 END IF;
1967
1968
1969
1970 FOR i IN p_x_df_schedules_tbl.FIRST..p_x_df_schedules_tbl.LAST LOOP
1971 IF(p_x_df_schedules_tbl(i).operation_flag = G_OP_DELETE) THEN
1972 AHL_UNIT_THRESHOLDS_PKG.delete_row(p_x_df_schedules_tbl(i).unit_threshold_id);
1973 END IF;
1974 END LOOP;
1975
1976 FOR i IN p_x_df_schedules_tbl.FIRST..p_x_df_schedules_tbl.LAST LOOP
1977 IF (p_x_df_schedules_tbl(i).operation_flag = G_OP_UPDATE) THEN
1978
1979 p_x_df_schedules_tbl(i).object_version_number := p_x_df_schedules_tbl(i).object_version_number + 1;
1980
1981 p_x_df_schedules_tbl(i).last_updated_by := fnd_global.user_id;
1982 p_x_df_schedules_tbl(i).last_update_date := SYSDATE;
1983 p_x_df_schedules_tbl(i).last_update_login := fnd_global.user_id;
1984
1985 AHL_UNIT_THRESHOLDS_PKG.update_row(
1986 p_unit_threshold_id => p_x_df_schedules_tbl(i).unit_threshold_id,
1987 p_object_version_number => p_x_df_schedules_tbl(i).object_version_number,
1988 p_last_updated_by => p_x_df_schedules_tbl(i).last_updated_by,
1989 p_last_update_date => p_x_df_schedules_tbl(i).last_update_date,
1990 p_last_update_login => p_x_df_schedules_tbl(i).last_update_login,
1991 p_unit_deferral_id => p_x_df_schedules_tbl(i).unit_deferral_id,
1992 p_counter_id => p_x_df_schedules_tbl(i).counter_id,
1993 p_counter_value => p_x_df_schedules_tbl(i).counter_value,
1994 p_ctr_value_type_code => p_x_df_schedules_tbl(i).ctr_value_type_code,
1995 p_attribute_category => p_x_df_schedules_tbl(i).attribute_category,
1996 p_attribute1 => p_x_df_schedules_tbl(i).attribute1,
1997 p_attribute2 => p_x_df_schedules_tbl(i).attribute2,
1998 p_attribute3 => p_x_df_schedules_tbl(i).attribute3,
1999 p_attribute4 => p_x_df_schedules_tbl(i).attribute4,
2000 p_attribute5 => p_x_df_schedules_tbl(i).attribute5,
2001 p_attribute6 => p_x_df_schedules_tbl(i).attribute6,
2002 p_attribute7 => p_x_df_schedules_tbl(i).attribute7,
2003 p_attribute8 => p_x_df_schedules_tbl(i).attribute8,
2004 p_attribute9 => p_x_df_schedules_tbl(i).attribute9,
2005 p_attribute10 => p_x_df_schedules_tbl(i).attribute10,
2006 p_attribute11 => p_x_df_schedules_tbl(i).attribute11,
2007 p_attribute12 => p_x_df_schedules_tbl(i).attribute12,
2008 p_attribute13 => p_x_df_schedules_tbl(i).attribute13,
2009 p_attribute14 => p_x_df_schedules_tbl(i).attribute14,
2010 p_attribute15 => p_x_df_schedules_tbl(i).attribute15
2011 );
2012 END IF;
2013 END LOOP;
2014 FOR i IN p_x_df_schedules_tbl.FIRST..p_x_df_schedules_tbl.LAST LOOP
2015 IF(p_x_df_schedules_tbl(i).operation_flag = G_OP_CREATE) THEN
2016
2017 p_x_df_schedules_tbl(i).object_version_number := 1;
2018
2019 p_x_df_schedules_tbl(i).created_by := fnd_global.user_id;
2020 p_x_df_schedules_tbl(i).creation_date := SYSDATE;
2021 p_x_df_schedules_tbl(i).last_updated_by := fnd_global.user_id;
2022 p_x_df_schedules_tbl(i).last_update_date := SYSDATE;
2023 p_x_df_schedules_tbl(i).last_update_login := fnd_global.user_id;
2024
2025 AHL_UNIT_THRESHOLDS_PKG.insert_row(
2026 p_x_unit_threshold_id => p_x_df_schedules_tbl(i).unit_threshold_id,
2027 p_object_version_number => p_x_df_schedules_tbl(i).object_version_number,
2028 p_created_by => p_x_df_schedules_tbl(i).created_by,
2029 p_creation_date => p_x_df_schedules_tbl(i).creation_date,
2030 p_last_updated_by => p_x_df_schedules_tbl(i).last_updated_by,
2031 p_last_update_date => p_x_df_schedules_tbl(i).last_update_date,
2032 p_last_update_login => p_x_df_schedules_tbl(i).last_update_login,
2033 p_unit_deferral_id => p_x_df_schedules_tbl(i).unit_deferral_id,
2034 p_counter_id => p_x_df_schedules_tbl(i).counter_id,
2035 p_counter_value => p_x_df_schedules_tbl(i).counter_value,
2036 p_ctr_value_type_code => p_x_df_schedules_tbl(i).ctr_value_type_code,
2037 p_attribute_category => p_x_df_schedules_tbl(i).attribute_category,
2038 p_attribute1 => p_x_df_schedules_tbl(i).attribute1,
2039 p_attribute2 => p_x_df_schedules_tbl(i).attribute2,
2040 p_attribute3 => p_x_df_schedules_tbl(i).attribute3,
2041 p_attribute4 => p_x_df_schedules_tbl(i).attribute4,
2042 p_attribute5 => p_x_df_schedules_tbl(i).attribute5,
2043 p_attribute6 => p_x_df_schedules_tbl(i).attribute6,
2044 p_attribute7 => p_x_df_schedules_tbl(i).attribute7,
2045 p_attribute8 => p_x_df_schedules_tbl(i).attribute8,
2046 p_attribute9 => p_x_df_schedules_tbl(i).attribute9,
2047 p_attribute10 => p_x_df_schedules_tbl(i).attribute10,
2048 p_attribute11 => p_x_df_schedules_tbl(i).attribute11,
2049 p_attribute12 => p_x_df_schedules_tbl(i).attribute12,
2050 p_attribute13 => p_x_df_schedules_tbl(i).attribute13,
2051 p_attribute14 => p_x_df_schedules_tbl(i).attribute14,
2052 p_attribute15 => p_x_df_schedules_tbl(i).attribute15
2053 );
2054 END IF;
2055 END LOOP;
2056
2057 IF(FND_MSG_PUB.count_msg > 0)THEN
2058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2059 END IF;
2060
2061 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2062 fnd_log.string
2063 (
2064 fnd_log.level_procedure,
2065 'ahl.plsql.AHL_PRD_DF_PVT.process_df_schedules.end',
2066 'At the end of PLSQL procedure'
2067 );
2068 END IF;
2069
2070 END process_df_schedules;
2071 -----------------------------------------------------------------------------------
2072 -- Procedure to dump deferral schedules records
2073 -----------------------------------------------------------------------------------
2074 PROCEDURE log_df_schedules(
2075 p_df_schedules_tbl IN AHL_PRD_DF_PVT.df_schedules_tbl_type) IS
2076
2077 BEGIN
2078 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2079 FOR i IN p_df_schedules_tbl.FIRST..p_df_schedules_tbl.LAST LOOP
2080 fnd_log.string
2081 (
2082 fnd_log.level_statement,
2083 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2084 'p_df_schedules_tbl('|| i ||').operation_flag : ' || p_df_schedules_tbl(i).operation_flag
2085 );
2086 fnd_log.string
2087 (
2088 fnd_log.level_statement,
2089 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2090 'p_df_schedules_tbl('|| i ||').unit_threshold_id : ' || p_df_schedules_tbl(i).unit_threshold_id
2091 );
2092 fnd_log.string
2093 (
2094 fnd_log.level_statement,
2095 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2096 'p_df_schedules_tbl('|| i ||').object_version_number : ' || p_df_schedules_tbl(i).object_version_number
2097 );
2098 fnd_log.string
2099 (
2100 fnd_log.level_statement,
2101 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2102 'p_df_schedules_tbl('|| i ||').unit_deferral_id : ' || p_df_schedules_tbl(i).unit_deferral_id
2103 );
2104 fnd_log.string
2105 (
2106 fnd_log.level_statement,
2107 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2108 'p_df_schedules_tbl('|| i ||').counter_id : ' || p_df_schedules_tbl(i).counter_id
2109 );
2110 fnd_log.string
2111 (
2112 fnd_log.level_statement,
2113 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2114 'p_df_schedules_tbl('|| i ||').counter_name : ' || p_df_schedules_tbl(i).counter_name
2115 );
2116 fnd_log.string
2117 (
2118 fnd_log.level_statement,
2119 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2120 'p_df_schedules_tbl('|| i ||').ctr_value_type_code : ' || p_df_schedules_tbl(i).ctr_value_type_code
2121 );
2122 fnd_log.string
2123 (
2124 fnd_log.level_statement,
2125 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2126 'p_df_schedules_tbl('|| i ||').counter_value : ' || p_df_schedules_tbl(i).counter_value
2127 );
2128 fnd_log.string
2129 (
2130 fnd_log.level_statement,
2131 'ahl.plsql.AHL_PRD_DF_PVT.log_df_schedules',
2132 'p_df_schedules_tbl('|| i ||').unit_of_measure : ' || p_df_schedules_tbl(i).unit_of_measure
2133 );
2134 END LOOP;
2135 END IF;
2136 END log_df_schedules;
2137
2138 PROCEDURE validate_df_schedules(
2139 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
2140 p_df_schedules_tbl IN AHL_PRD_DF_PVT.df_schedules_tbl_type)IS
2141
2142
2143 CURSOR valid_counter_csr(p_unit_deferral_id IN NUMBER,p_counter_id IN NUMBER) IS
2144 --SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
2145 SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
2146 WHERE co.customer_product_id = ue.csi_item_instance_id
2147 AND co.counter_id = p_counter_id
2148 AND UE.unit_effectivity_id = UD.unit_effectivity_id
2149 AND UD.unit_deferral_id = p_unit_deferral_id;
2150
2151 /*CURSOR mr_valid_counter_csr(p_unit_deferral_id IN NUMBER,p_counter_id IN NUMBER) IS
2152 SELECT 'x' from ahl_unit_effectivities_b UE, ahl_unit_deferrals_b UD,AHL_MR_INTERVALS_V MR,CSI_CP_COUNTERS_V CO
2153 WHERE UD.unit_deferral_id = p_unit_deferral_id
2154 AND UE.unit_effectivity_id = UD.unit_effectivity_id
2155 AND co.customer_product_id = ue.csi_item_instance_id
2156 AND UE.mr_effectivity_id = MR.mr_effectivity_id
2157 AND CO.counter_id = p_counter_id
2158 AND CO.counter_name = MR.counter_name; */
2159
2160
2161 CURSOR valid_df_rec_del_csr(p_unit_threshold_id IN NUMBER, p_object_version_number IN NUMBER) IS
2162 SELECT 'x' FROM ahl_unit_thresholds
2163 WHERE object_version_number = p_object_version_number
2164 AND unit_threshold_id = p_unit_threshold_id;
2165
2166 l_exists VARCHAR2(1);
2167 --l_current_counter_value NUMBER;
2168
2169 BEGIN
2170 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2171 fnd_log.string
2172 (
2173 fnd_log.level_procedure,
2174 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_schedules.begin',
2175 'At the start of PLSQL procedure'
2176 );
2177 END IF;
2178
2179 FOR i IN p_df_schedules_tbl.FIRST..p_df_schedules_tbl.LAST LOOP
2180 -- not needed when module type is JSP as value to id conversion took care of that
2181 IF(p_df_schedules_tbl(i).operation_flag IN (G_OP_CREATE,G_OP_UPDATE) AND NVL(G_MODULE_TYPE,'x') <> 'JSP') THEN
2182 -- validate whether valid items' counter
2183 OPEN valid_counter_csr(p_df_schedules_tbl(i).unit_deferral_id,p_df_schedules_tbl(i).counter_id);
2184 FETCH valid_counter_csr INTO l_exists;
2185 IF(valid_counter_csr%NOTFOUND)THEN
2186 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNT_NAME');
2187 FND_MESSAGE.Set_Token('COUNTER_NAME',p_df_schedules_tbl(i).counter_name);
2188 FND_MSG_PUB.ADD;
2189 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2190 fnd_log.string
2191 (
2192 fnd_log.level_error,
2193 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_schedules',
2194 'Invalid Counter name for associated item instance in schedule record : ' || i
2195 );
2196 END IF;
2197 END IF;
2198 CLOSE valid_counter_csr;
2199 -- validate whether counter defined for this MR at FMP level
2200 /*IF(p_df_header_rec.deferral_type = 'MR')THEN
2201 OPEN mr_valid_counter_csr(p_df_schedules_tbl(i).unit_deferral_id,p_df_schedules_tbl(i).counter_id);
2202 FETCH mr_valid_counter_csr INTO l_exists;
2203 IF(mr_valid_counter_csr%NOTFOUND)THEN
2204 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_INV_CNT');
2205 FND_MSG_PUB.ADD;
2206 END IF;
2207 CLOSE mr_valid_counter_csr;
2208 END IF;*/
2209
2210 ELSIF(p_df_schedules_tbl(i).operation_flag = G_OP_DELETE) THEN
2211 -- validate whether record exists for delete
2212 OPEN valid_df_rec_del_csr(p_df_schedules_tbl(i).unit_threshold_id,p_df_schedules_tbl(i).object_version_number);
2213 FETCH valid_df_rec_del_csr INTO l_exists;
2214 IF(valid_df_rec_del_csr%NOTFOUND) THEN
2215 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_REC_MISS');
2216 FND_MSG_PUB.ADD;
2217 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2218 fnd_log.string
2219 (
2220 fnd_log.level_error,
2221 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_schedules',
2222 'Record for delete operation not found with keys in schedule record : ' || i
2223 );
2224 END IF;
2225 END IF;
2226 CLOSE valid_df_rec_del_csr;
2227 END IF;
2228 END LOOP;
2229
2230 -- raise expected error
2231 IF(FND_MSG_PUB.count_msg > 0)THEN
2232 RAISE FND_API.G_EXC_ERROR;
2233 END IF;
2234
2235 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2236 fnd_log.string
2237 (
2238 fnd_log.level_procedure,
2239 'ahl.plsql.AHL_PRD_DF_PVT.validate_df_schedules.end',
2240 'At the end of PLSQL procedure'
2241 );
2242 END IF;
2243
2244 END validate_df_schedules;
2245
2246 PROCEDURE default_unchanged_df_schedules(
2247 p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type)IS
2248
2249 CURSOR df_schedules_csr(p_unit_threshold_id IN NUMBER, p_object_version_number IN NUMBER) IS
2250 SELECT unit_deferral_id,counter_id,counter_value,ctr_value_type_code,attribute_category, attribute1,attribute2, attribute3, attribute4,
2251 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11,
2252 attribute12, attribute13, attribute14, attribute15
2253 FROM ahl_unit_thresholds
2254 WHERE object_version_number= p_object_version_number
2255 AND unit_threshold_id = p_unit_threshold_id;
2256
2257 l_df_schedules_rec AHL_PRD_DF_PVT.df_schedules_rec_type;
2258
2259 BEGIN
2260 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2261 fnd_log.string
2262 (
2263 fnd_log.level_procedure,
2264 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules.begin',
2265 'At the start of PLSQL procedure'
2266 );
2267 END IF;
2268
2269 FOR i IN p_x_df_schedules_tbl.FIRST..p_x_df_schedules_tbl.LAST LOOP
2270 IF(p_x_df_schedules_tbl(i).operation_flag = G_OP_UPDATE) THEN
2271 OPEN df_schedules_csr(p_x_df_schedules_tbl(i).unit_threshold_id, p_x_df_schedules_tbl(i).object_version_number);
2272 FETCH df_schedules_csr INTO l_df_schedules_rec.unit_deferral_id,l_df_schedules_rec.counter_id,
2273 l_df_schedules_rec.counter_value, l_df_schedules_rec.ctr_value_type_code,
2274 l_df_schedules_rec.attribute_category,l_df_schedules_rec.attribute1,l_df_schedules_rec.attribute2,
2275 l_df_schedules_rec.attribute3, l_df_schedules_rec.attribute4, l_df_schedules_rec.attribute5,
2276 l_df_schedules_rec.attribute6, l_df_schedules_rec.attribute7, l_df_schedules_rec.attribute8,
2277 l_df_schedules_rec.attribute9, l_df_schedules_rec.attribute10, l_df_schedules_rec.attribute11,
2278 l_df_schedules_rec.attribute12, l_df_schedules_rec.attribute13, l_df_schedules_rec.attribute14, l_df_schedules_rec.attribute15;
2279 IF (df_schedules_csr%NOTFOUND) THEN
2280 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_REC_MISS');
2281 FND_MSG_PUB.ADD;
2282 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2283 fnd_log.string
2284 (
2285 fnd_log.level_error,
2286 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2287 'Missing Deferral Schedule Record : ' || i
2288 );
2289 END IF;
2290 ELSE
2291 IF (p_x_df_schedules_tbl(i).unit_deferral_id IS NULL) THEN
2292 p_x_df_schedules_tbl(i).unit_deferral_id := l_df_schedules_rec.unit_deferral_id;
2293 ELSIF(p_x_df_schedules_tbl(i).unit_deferral_id <> l_df_schedules_rec.unit_deferral_id) THEN
2294 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_HDR_MISS');
2295 FND_MSG_PUB.ADD;
2296 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2297 fnd_log.string
2298 (
2299 fnd_log.level_unexpected,
2300 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2301 'Unit Deferral ID does not match with deferral schedule record : ' || i
2302 );
2303 END IF;
2304 CLOSE df_schedules_csr;
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 END IF;
2307
2308 IF (p_x_df_schedules_tbl(i).counter_id IS NULL) THEN
2309 p_x_df_schedules_tbl(i).counter_id := l_df_schedules_rec.counter_id;
2310 ELSIF(p_x_df_schedules_tbl(i).counter_id = FND_API.G_MISS_NUM) THEN
2311 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNT_NAME');
2312 FND_MESSAGE.Set_Token('COUNTER_NAME',p_x_df_schedules_tbl(i).counter_name);
2313 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2314 fnd_log.string
2315 (
2316 fnd_log.level_error,
2317 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2318 'Missing counter ID in schedule Record : ' || i
2319 );
2320 END IF;
2321 END IF;
2322
2323 IF (p_x_df_schedules_tbl(i).counter_value IS NULL) THEN
2324 p_x_df_schedules_tbl(i).counter_value := l_df_schedules_rec.counter_value;
2325 ELSIF(p_x_df_schedules_tbl(i).counter_value = FND_API.G_MISS_NUM OR
2326 p_x_df_schedules_tbl(i).counter_value <= 0) THEN
2327 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNTVAL');
2328 FND_MESSAGE.Set_Token('COUNTER_NAME',p_x_df_schedules_tbl(i).counter_name);
2329 FND_MESSAGE.Set_Token('COUNTER_VALUE',p_x_df_schedules_tbl(i).counter_value);
2330 FND_MSG_PUB.ADD;
2331 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2332 fnd_log.string
2333 (
2334 fnd_log.level_error,
2335 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2336 'Missing counter Value in schedule Record : ' || i
2337 );
2338 END IF;
2339 END IF;
2340
2341 IF (p_x_df_schedules_tbl(i).ctr_value_type_code IS NULL) THEN
2342 p_x_df_schedules_tbl(i).ctr_value_type_code := l_df_schedules_rec.ctr_value_type_code;
2343 ELSIF(p_x_df_schedules_tbl(i).ctr_value_type_code NOT IN(G_DEFER_BY,G_DEFER_TO)) THEN
2344 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNTVL_TPCD');
2345 FND_MSG_PUB.ADD;
2346 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2347 fnd_log.string
2348 (
2349 fnd_log.level_unexpected,
2350 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2351 'Invalid counter value type code in deferral schedule record : ' || i
2352 );
2353 END IF;
2354 CLOSE df_schedules_csr;
2355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2356 END IF;
2357
2358 IF (p_x_df_schedules_tbl(i).attribute_category IS NULL) THEN
2359 p_x_df_schedules_tbl(i).attribute_category := l_df_schedules_rec.attribute_category;
2360 ELSIF(p_x_df_schedules_tbl(i).attribute_category = FND_API.G_MISS_CHAR) THEN
2361 p_x_df_schedules_tbl(i).attribute_category := NULL;
2362 END IF;
2363
2364 IF (p_x_df_schedules_tbl(i).attribute1 IS NULL) THEN
2365 p_x_df_schedules_tbl(i).attribute1 := l_df_schedules_rec.attribute1;
2366 ELSIF(p_x_df_schedules_tbl(i).attribute1 = FND_API.G_MISS_CHAR) THEN
2367 p_x_df_schedules_tbl(i).attribute1 := NULL;
2368 END IF;
2369
2370 IF (p_x_df_schedules_tbl(i).attribute2 IS NULL) THEN
2371 p_x_df_schedules_tbl(i).attribute2 := l_df_schedules_rec.attribute2;
2372 ELSIF(p_x_df_schedules_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
2373 p_x_df_schedules_tbl(i).attribute2 := NULL;
2374 END IF;
2375
2376 IF (p_x_df_schedules_tbl(i).attribute3 IS NULL) THEN
2377 p_x_df_schedules_tbl(i).attribute3 := l_df_schedules_rec.attribute3;
2378 ELSIF(p_x_df_schedules_tbl(i).attribute3 = FND_API.G_MISS_CHAR) THEN
2379 p_x_df_schedules_tbl(i).attribute3 := NULL;
2380 END IF;
2381
2382 IF (p_x_df_schedules_tbl(i).attribute4 IS NULL) THEN
2383 p_x_df_schedules_tbl(i).attribute4 := l_df_schedules_rec.attribute4;
2384 ELSIF(p_x_df_schedules_tbl(i).attribute4 = FND_API.G_MISS_CHAR) THEN
2385 p_x_df_schedules_tbl(i).attribute4 := NULL;
2386 END IF;
2387
2388 IF (p_x_df_schedules_tbl(i).attribute5 IS NULL) THEN
2389 p_x_df_schedules_tbl(i).attribute5 := l_df_schedules_rec.attribute5;
2390 ELSIF(p_x_df_schedules_tbl(i).attribute5 = FND_API.G_MISS_CHAR) THEN
2391 p_x_df_schedules_tbl(i).attribute5 := NULL;
2392 END IF;
2393
2394 IF (p_x_df_schedules_tbl(i).attribute6 IS NULL) THEN
2395 p_x_df_schedules_tbl(i).attribute6 := l_df_schedules_rec.attribute6;
2396 ELSIF(p_x_df_schedules_tbl(i).attribute6 = FND_API.G_MISS_CHAR) THEN
2397 p_x_df_schedules_tbl(i).attribute6 := NULL;
2398 END IF;
2399
2400 IF (p_x_df_schedules_tbl(i).attribute7 IS NULL) THEN
2401 p_x_df_schedules_tbl(i).attribute7 := l_df_schedules_rec.attribute7;
2402 ELSIF(p_x_df_schedules_tbl(i).attribute7 = FND_API.G_MISS_CHAR) THEN
2403 p_x_df_schedules_tbl(i).attribute7 := NULL;
2404 END IF;
2405
2406 IF (p_x_df_schedules_tbl(i).attribute8 IS NULL) THEN
2407 p_x_df_schedules_tbl(i).attribute8 := l_df_schedules_rec.attribute8;
2408 ELSIF(p_x_df_schedules_tbl(i).attribute8 = FND_API.G_MISS_CHAR) THEN
2409 p_x_df_schedules_tbl(i).attribute8 := NULL;
2410 END IF;
2411
2412 IF (p_x_df_schedules_tbl(i).attribute9 IS NULL) THEN
2413 p_x_df_schedules_tbl(i).attribute9 := l_df_schedules_rec.attribute9;
2414 ELSIF(p_x_df_schedules_tbl(i).attribute9 = FND_API.G_MISS_CHAR) THEN
2415 p_x_df_schedules_tbl(i).attribute9 := NULL;
2416 END IF;
2417
2418 IF (p_x_df_schedules_tbl(i).attribute10 IS NULL) THEN
2419 p_x_df_schedules_tbl(i).attribute10 := l_df_schedules_rec.attribute10;
2420 ELSIF(p_x_df_schedules_tbl(i).attribute10 = FND_API.G_MISS_CHAR) THEN
2421 p_x_df_schedules_tbl(i).attribute10 := NULL;
2422 END IF;
2423
2424 IF (p_x_df_schedules_tbl(i).attribute11 IS NULL) THEN
2425 p_x_df_schedules_tbl(i).attribute11 := l_df_schedules_rec.attribute11;
2426 ELSIF(p_x_df_schedules_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
2427 p_x_df_schedules_tbl(i).attribute11 := NULL;
2428 END IF;
2429
2430 IF (p_x_df_schedules_tbl(i).attribute12 IS NULL) THEN
2431 p_x_df_schedules_tbl(i).attribute12 := l_df_schedules_rec.attribute12;
2432 ELSIF(p_x_df_schedules_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
2433 p_x_df_schedules_tbl(i).attribute12 := NULL;
2434 END IF;
2435
2436 IF (p_x_df_schedules_tbl(i).attribute13 IS NULL) THEN
2437 p_x_df_schedules_tbl(i).attribute13 := l_df_schedules_rec.attribute13;
2438 ELSIF(p_x_df_schedules_tbl(i).attribute13 = FND_API.G_MISS_CHAR) THEN
2439 p_x_df_schedules_tbl(i).attribute13 := NULL;
2440 END IF;
2441
2442 IF (p_x_df_schedules_tbl(i).attribute14 IS NULL) THEN
2443 p_x_df_schedules_tbl(i).attribute14 := l_df_schedules_rec.attribute14;
2444 ELSIF(p_x_df_schedules_tbl(i).attribute14 = FND_API.G_MISS_CHAR) THEN
2445 p_x_df_schedules_tbl(i).attribute14 := NULL;
2446 END IF;
2447
2448 IF (p_x_df_schedules_tbl(i).attribute15 IS NULL) THEN
2449 p_x_df_schedules_tbl(i).attribute15 := l_df_schedules_rec.attribute15;
2450 ELSIF(p_x_df_schedules_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
2451 p_x_df_schedules_tbl(i).attribute15 := NULL;
2452 END IF;
2453
2454 END IF;
2455 CLOSE df_schedules_csr;
2456 ELSIF (p_x_df_schedules_tbl(i).operation_flag = G_OP_CREATE) THEN
2457
2458 IF (p_x_df_schedules_tbl(i).unit_deferral_id IS NULL OR
2459 p_x_df_schedules_tbl(i).unit_deferral_id = FND_API.G_MISS_NUM) THEN
2460 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_UDID');
2461 FND_MSG_PUB.ADD;
2462 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2463 fnd_log.string
2464 (
2465 fnd_log.level_unexpected,
2466 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2467 'Missing Unit Deferral ID for create operation in deferral schedule record : ' || i
2468 );
2469 END IF;
2470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2471 END IF;
2472
2473 IF (p_x_df_schedules_tbl(i).counter_id IS NULL OR
2474 p_x_df_schedules_tbl(i).counter_id = FND_API.G_MISS_NUM) THEN
2475 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNT_NAME');
2476 FND_MESSAGE.Set_Token('COUNTER_NAME',p_x_df_schedules_tbl(i).counter_name);
2477 FND_MSG_PUB.ADD;
2478 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2479 fnd_log.string
2480 (
2481 fnd_log.level_error,
2482 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2483 'Missing counter ID in schedule Record : ' || i
2484 );
2485 END IF;
2486 END IF;
2487
2488 IF (p_x_df_schedules_tbl(i).counter_value IS NULL OR
2489 p_x_df_schedules_tbl(i).counter_value = FND_API.G_MISS_NUM OR
2490 p_x_df_schedules_tbl(i).counter_value <= 0 ) THEN
2491 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNTVAL');
2492 FND_MESSAGE.Set_Token('COUNTER_NAME',p_x_df_schedules_tbl(i).counter_name);
2493 FND_MESSAGE.Set_Token('COUNTER_VALUE',p_x_df_schedules_tbl(i).counter_value);
2494 FND_MSG_PUB.ADD;
2495 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2496 fnd_log.string
2497 (
2498 fnd_log.level_error,
2499 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2500 'Missing or invalid counter Value in schedule Record : ' || i
2501 );
2502 END IF;
2503 END IF;
2504
2505 IF (p_x_df_schedules_tbl(i).ctr_value_type_code IS NULL OR
2506 p_x_df_schedules_tbl(i).ctr_value_type_code NOT IN(G_DEFER_BY,G_DEFER_TO)) THEN
2507 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_CNTVL_TPCD');
2508 FND_MSG_PUB.ADD;
2509 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2510 fnd_log.string
2511 (
2512 fnd_log.level_unexpected,
2513 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules',
2514 'Invalid counter value type code in deferral schedule record : ' || i
2515 );
2516 END IF;
2517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2518 END IF;
2519
2520 IF (p_x_df_schedules_tbl(i).attribute_category = FND_API.G_MISS_CHAR) THEN
2521 p_x_df_schedules_tbl(i).attribute_category := NULL;
2522 END IF;
2523
2524 IF (p_x_df_schedules_tbl(i).attribute1 = FND_API.G_MISS_CHAR) THEN
2525 p_x_df_schedules_tbl(i).attribute1 := NULL;
2526 END IF;
2527
2528 IF (p_x_df_schedules_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
2529 p_x_df_schedules_tbl(i).attribute2 := NULL;
2530 END IF;
2531
2532 IF (p_x_df_schedules_tbl(i).attribute3 = FND_API.G_MISS_CHAR) THEN
2533 p_x_df_schedules_tbl(i).attribute3 := NULL;
2534 END IF;
2535
2536 IF (p_x_df_schedules_tbl(i).attribute4 = FND_API.G_MISS_CHAR) THEN
2537 p_x_df_schedules_tbl(i).attribute4 := NULL;
2538 END IF;
2539
2540 IF (p_x_df_schedules_tbl(i).attribute5 = FND_API.G_MISS_CHAR) THEN
2541 p_x_df_schedules_tbl(i).attribute5 := NULL;
2542 END IF;
2543
2544 IF (p_x_df_schedules_tbl(i).attribute6 = FND_API.G_MISS_CHAR) THEN
2545 p_x_df_schedules_tbl(i).attribute6 := NULL;
2546 END IF;
2547
2548 IF (p_x_df_schedules_tbl(i).attribute7 = FND_API.G_MISS_CHAR) THEN
2549 p_x_df_schedules_tbl(i).attribute7 := NULL;
2550 END IF;
2551
2552 IF (p_x_df_schedules_tbl(i).attribute8 = FND_API.G_MISS_CHAR) THEN
2553 p_x_df_schedules_tbl(i).attribute8 := NULL;
2554 END IF;
2555
2556 IF (p_x_df_schedules_tbl(i).attribute9 = FND_API.G_MISS_CHAR) THEN
2557 p_x_df_schedules_tbl(i).attribute9 := NULL;
2558 END IF;
2559
2560 IF (p_x_df_schedules_tbl(i).attribute10 = FND_API.G_MISS_CHAR) THEN
2561 p_x_df_schedules_tbl(i).attribute10 := NULL;
2562 END IF;
2563
2564 IF (p_x_df_schedules_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
2565 p_x_df_schedules_tbl(i).attribute11 := NULL;
2566 END IF;
2567
2568 IF (p_x_df_schedules_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
2569 p_x_df_schedules_tbl(i).attribute12 := NULL;
2570 END IF;
2571
2572 IF (p_x_df_schedules_tbl(i).attribute13 = FND_API.G_MISS_CHAR) THEN
2573 p_x_df_schedules_tbl(i).attribute13 := NULL;
2574 END IF;
2575
2576 IF (p_x_df_schedules_tbl(i).attribute14 = FND_API.G_MISS_CHAR) THEN
2577 p_x_df_schedules_tbl(i).attribute14 := NULL;
2578 END IF;
2579
2580 IF (p_x_df_schedules_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
2581 p_x_df_schedules_tbl(i).attribute15 := NULL;
2582 END IF;
2583
2584 END IF;
2585 END LOOP;
2586
2587 -- raise expected error
2588 IF(FND_MSG_PUB.count_msg > 0)THEN
2589 RAISE FND_API.G_EXC_ERROR;
2590 END IF;
2591
2592 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2593 fnd_log.string
2594 (
2595 fnd_log.level_procedure,
2596 'ahl.plsql.AHL_PRD_DF_PVT.default_unchanged_df_schedules.end',
2597 'At the end of PLSQL procedure'
2598 );
2599 END IF;
2600
2601 END default_unchanged_df_schedules;
2602 --------------------------------------------------------------------------------
2603 -- Validate deferral record as a whole
2604 --------------------------------------------------------------------------------
2605
2606 PROCEDURE validate_deferral_updates(
2607 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
2608 x_warning_msg_data OUT NOCOPY VARCHAR2)IS
2609
2610 l_count1 NUMBER;
2611 l_count2 NUMBER;
2612
2613 CURSOR counter_values_csr(p_unit_deferral_id IN NUMBER) IS
2614 SELECT UT.counter_id, CO.name, UT.counter_value, UT.ctr_value_type_code,CO.uom_code
2615 FROM CS_COUNTERS CO,ahl_unit_thresholds UT
2616 WHERE CO.counter_id = UT.counter_id
2617 AND UT.unit_deferral_id = p_unit_deferral_id;
2618
2619 l_counter_id NUMBER;
2620 l_counter_name VARCHAR2(30);
2621 l_uom_code VARCHAR2(3);
2622 l_counter_value NUMBER;
2623 l_ctr_value_type_code VARCHAR2(30);
2624 i NUMBER := 0;
2625 l_defer_due_date DATE;
2626 l_calc_due_date_flag BOOLEAN := false;
2627 l_current_counter_value NUMBER := 0;
2628
2629 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2630 l_msg_data VARCHAR2(4000);
2631 l_msg_count NUMBER;
2632
2633 CURSOR curr_counter_val_csr(p_unit_deferral_id IN NUMBER, p_counter_id IN NUMBER,p_deferral_effective_on IN DATE) IS
2634 SELECT NVL(net_reading, 0) FROM cs_ctr_counter_values_v ctrread, cs_counter_groups_v ctrgrp,
2635 -- AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
2636 AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
2637 WHERE ctrread.VALUE_TIMESTAMP <= p_deferral_effective_on
2638 AND ctrread.counter_group_id = ctrgrp.counter_group_id
2639 AND SOURCE_OBJECT_CODE = 'CP'
2640 AND SOURCE_OBJECT_ID = UE.csi_item_instance_id
2641 AND ctrread.counter_id = p_counter_id
2642 AND UE.unit_effectivity_id = UD.unit_effectivity_id
2643 AND UD.unit_deferral_id = p_unit_deferral_id
2644 ORDER BY ctrread.counter_id asc, ctrread.VALUE_TIMESTAMP desc;
2645
2646 l_counter_values_tbl AHL_UMP_PROCESSUNIT_PVT.counter_values_tbl_type;
2647
2648 CURSOR whichever_first_code_csr (p_unit_effectivity_id IN NUMBER) IS
2649 SELECT whichever_first_code
2650 --FROM AHL_MR_HEADERS_APP_V MR, AHL_UNIT_EFFECTIVITIES_APP_V UE
2651 FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B UE -- Undid blind changes for app_usage code
2652 WHERE MR.mr_header_id = UE.mr_header_id
2653 AND UE.unit_effectivity_id = p_unit_effectivity_id;
2654
2655 l_whichever_first_code ahl_mr_headers_b.whichever_first_code%TYPE;
2656
2657
2658 CURSOR next_due_date_csr(p_unit_effectivity_id IN NUMBER) IS
2659 SELECT UE.due_date
2660 FROM AHL_UNIT_EFFECTIVITIES_B UE
2661 WHERE UE.mr_header_id = (
2662 SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2663 AND UE.csi_item_instance_id = (
2664 SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2665 AND UE.unit_effectivity_id <> p_unit_effectivity_id
2666 AND ( UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2667 ORDER BY DUE_DATE ASC;
2668
2669 l_next_due_date DATE;
2670
2671
2672 BEGIN
2673 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2674 fnd_log.string
2675 (
2676 fnd_log.level_procedure,
2677 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates.begin',
2678 'At the start of PLSQL procedure'
2679 );
2680 END IF;
2681
2682 -- df schedule validity for the the unit_deferral_id.
2683 SELECT count(*) INTO l_count1 from (SELECT counter_id FROM ahl_unit_thresholds
2684 WHERE unit_deferral_id = p_df_header_rec.unit_deferral_id);
2685
2686 SELECT count(*) INTO l_count2 from (SELECT DISTINCT counter_id FROM ahl_unit_thresholds
2687 WHERE unit_deferral_id = p_df_header_rec.unit_deferral_id);
2688 IF(l_count1 <> l_count2) THEN
2689 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_CNT_NAME_REP');
2690 FND_MSG_PUB.ADD;
2691 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2692 fnd_log.string
2693 (
2694 fnd_log.level_error,
2695 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
2696 'Counters are repeating in schedules'
2697 );
2698 END IF;
2699 END IF;
2700
2701 IF(l_count2 = 0)THEN
2702 IF(p_df_header_rec.set_due_date IS NULL)THEN
2703 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SET_DUE_MAND');
2704 FND_MSG_PUB.ADD;
2705 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2706 fnd_log.string
2707 (
2708 fnd_log.level_error,
2709 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
2710 'Set due date or counter values are mandatory '
2711 );
2712 END IF;
2713 ELSE
2714 l_defer_due_date := p_df_header_rec.set_due_date;
2715 END IF;
2716 ELSE
2717 l_calc_due_date_flag := true;
2718 IF(p_df_header_rec.deferral_effective_on IS NULL OR p_df_header_rec.deferral_effective_on > SYSDATE)THEN
2719 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_DF_EFF_DT');
2720 FND_MESSAGE.Set_Token('DEFERRAL_EFFECTIVE_ON',p_df_header_rec.deferral_effective_on);
2721 FND_MSG_PUB.ADD;
2722 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2723 fnd_log.string
2724 (
2725 fnd_log.level_error,
2726 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
2727 'Deferral Effective On Date can not be null or greater than system date '
2728 );
2729 END IF;
2730 END IF;
2731 END IF;
2732
2733 IF(FND_MSG_PUB.count_msg > 0)THEN
2734 RAISE FND_API.G_EXC_ERROR;
2735 END IF;
2736
2737 IF(l_calc_due_date_flag)THEN
2738 -- validate counter values and populate table of counter, values to calculate due date
2739 OPEN counter_values_csr(p_df_header_rec.unit_deferral_id);
2740 LOOP
2741 FETCH counter_values_csr INTO l_counter_id,l_counter_name,
2742 l_counter_value,l_ctr_value_type_code,
2743 l_uom_code;
2744 IF(counter_values_csr%NOTFOUND) THEN
2745 EXIT;
2746 END IF;
2747 OPEN curr_counter_val_csr(p_df_header_rec.unit_deferral_id,l_counter_id,p_df_header_rec.deferral_effective_on);
2748 FETCH curr_counter_val_csr INTO l_current_counter_value;
2749 IF(curr_counter_val_csr%NOTFOUND) THEN
2750 l_current_counter_value := 0;
2751 END IF;
2752 CLOSE curr_counter_val_csr;
2753 IF(l_ctr_value_type_code = G_DEFER_BY) THEN
2754 l_counter_value := l_current_counter_value + l_counter_value;
2755 ELSE
2756 IF(l_counter_value < l_current_counter_value) THEN
2757 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_LS_CNTVAL');
2758 FND_MESSAGE.Set_Token('COUNTER_NAME',l_counter_name);
2759 FND_MESSAGE.Set_Token('COUNTER_VALUE',l_counter_value);
2760 FND_MSG_PUB.ADD;
2761 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2762 fnd_log.string
2763 (
2764 fnd_log.level_error,
2765 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
2766 'Defer to counter value is less than current counter value for counter name : ' || l_counter_name
2767 );
2768 END IF;
2769 END IF;
2770 END IF;
2771 l_counter_values_tbl(i).counter_id := l_counter_id;
2772 l_counter_values_tbl(i).counter_name := l_counter_name;
2773 l_counter_values_tbl(i).counter_value := l_counter_value;
2774 l_counter_values_tbl(i).uom_code := l_uom_code;
2775 i := i + 1;
2776 END LOOP;
2777 CLOSE counter_values_csr;
2778 -- throw errors if any here and do not proceed.
2779 IF(FND_MSG_PUB.count_msg > 0)THEN
2780 RAISE FND_API.G_EXC_ERROR;
2781 END IF;
2782
2783 IF((p_df_header_rec.deferral_type = G_DEFERRAL_TYPE_MR) AND
2784 (p_df_header_rec.mr_repetitive_flag = G_YES_FLAG)) THEN
2785 -- make a call to calculate due date with the table if mr is repetitive
2786 AHL_UMP_PROCESSUNIT_PVT.Get_Deferred_Due_Date (
2787 p_unit_effectivity_id => p_df_header_rec.unit_effectivity_id,
2788 p_deferral_threshold_tbl => l_counter_values_tbl,
2789 x_due_date => l_defer_due_date,
2790 x_return_status => l_return_status ,
2791 x_msg_data => l_msg_data,
2792 x_msg_count => l_msg_count);
2793 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2794 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_DUE_DT_CALC_ERR');
2795 FND_MSG_PUB.ADD;
2796 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2797 fnd_log.string
2798 (
2799 fnd_log.level_unexpected,
2800 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
2801 'Calculate Due Date API threw Error'
2802 );
2803 END IF;
2804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2805 END IF;
2806 IF(l_defer_due_date IS NOT NULL)THEN
2807 -- read whicever comes first or last code
2808 OPEN whichever_first_code_csr(p_df_header_rec.unit_effectivity_id);
2809 FETCH whichever_first_code_csr INTO l_whichever_first_code;
2810 IF(whichever_first_code_csr%NOTFOUND)THEN
2811 l_whichever_first_code := 'FIRST';
2812 END IF;
2813 CLOSE whichever_first_code_csr;
2814
2815 IF(l_whichever_first_code = 'FIRST') THEN
2816 IF(TRUNC(p_df_header_rec.set_due_date) < TRUNC(l_defer_due_date)) THEN
2817 l_defer_due_date := p_df_header_rec.set_due_date;
2818 END IF;
2819 ELSE
2820 IF(TRUNC(p_df_header_rec.set_due_date) > TRUNC(l_defer_due_date)) THEN
2821 l_defer_due_date := p_df_header_rec.set_due_date;
2822 END IF;
2823 END IF;
2824 -- read next due date if available
2825 OPEN next_due_date_csr(p_df_header_rec.unit_effectivity_id);
2826 FETCH next_due_date_csr INTO l_next_due_date;
2827 IF(next_due_date_csr%NOTFOUND)THEN
2828 NULL;-- thorw warning here
2829 ELSIF( TRUNC(l_next_due_date) < TRUNC(l_defer_due_date))THEN
2830 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_DUE_DATE_WARN');
2831 FND_MESSAGE.SET_TOKEN('DEFER_DUE_DATE',l_defer_due_date,false);
2832 FND_MESSAGE.SET_TOKEN('NEXT_DUE_DATE',l_next_due_date,false);
2833 l_msg_data := FND_MESSAGE.get;
2834 END IF;
2835 CLOSE next_due_date_csr;
2836 END IF;
2837
2838 END IF;
2839 END IF;
2840 -- add validations here if needed in future
2841 -- throw errors if any
2842 IF(FND_MSG_PUB.count_msg > 0)THEN
2843 RAISE FND_API.G_EXC_ERROR;
2844 END IF;
2845
2846 x_warning_msg_data := l_msg_data;
2847
2848 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2849 fnd_log.string
2850 (
2851 fnd_log.level_procedure,
2852 'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates.end',
2853 'At the end of PLSQL procedure'
2854 );
2855 END IF;
2856
2857 END validate_deferral_updates;
2858
2859 PROCEDURE submit_for_approval(
2860 p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type)IS
2861
2862 l_object VARCHAR2(30):= G_WORKFLOW_OBJECT_KEY;
2863 l_approval_type VARCHAR2(100):='CONCEPT';
2864 l_active VARCHAR2(50):= 'N';
2865 l_process_name VARCHAR2(50);
2866 l_item_type VARCHAR2(50);
2867 l_return_status VARCHAR2(50) := FND_API.G_RET_STS_SUCCESS;
2868 l_msg_count NUMBER;
2869 l_msg_data VARCHAR2(2000);
2870
2871 l_new_status_code VARCHAR2(30);
2872
2873 BEGIN
2874 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2875 fnd_log.string
2876 (
2877 fnd_log.level_procedure,
2878 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval.begin',
2879 'At the start of PLSQL procedure'
2880 );
2881 END IF;
2882
2883 IF(p_df_header_rec.approval_status_code NOT IN('DRAFT','DEFERRAL_REJECTED'))THEN
2884 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SUB_APPR_STS');
2885 FND_MSG_PUB.ADD;
2886 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2887 fnd_log.string
2888 (
2889 fnd_log.level_unexpected,
2890 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2891 'Can not submit for approval because current status is : ' || p_df_header_rec.approval_status_code
2892 );
2893 END IF;
2894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2895 END IF;
2896
2897 IF(valid_for_submission( p_df_header_rec.unit_effectivity_id) = FALSE)THEN
2898 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_SUB_PRC_STS');
2899 FND_MSG_PUB.ADD;
2900 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2901 fnd_log.string
2902 (
2903 fnd_log.level_error,
2904 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2905 'Can not submit for approval because a parent or child is in pending deferral approval status'
2906 );
2907 END IF;
2908 RAISE FND_API.G_EXC_ERROR;
2909 END IF;
2910
2911 -- Skip for UMP Deferral
2912 IF NOT(is_ump_deferral(p_df_header_rec.unit_deferral_id)) THEN
2913
2914 AHL_PRD_WORKORDER_PVT.validate_dependencies
2915 (
2916 p_api_version => 1.0,
2917 p_init_msg_list => FND_API.G_TRUE,
2918 p_commit => FND_API.G_FALSE,
2919 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2920 p_default => FND_API.G_FALSE,
2921 p_module_type => NULL,
2922 x_return_status =>l_return_status,
2923 x_msg_count =>l_msg_count,
2924 x_msg_data =>l_msg_data,
2925 p_visit_id => NULL,
2926 p_unit_effectivity_id =>p_df_header_rec.unit_effectivity_id,
2927 p_workorder_id => NULL
2928 );
2929 -- if workorders under UE has external dependencies, dont submit for approval, raise error.
2930 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2931 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2932 fnd_log.string
2933 (
2934 fnd_log.level_error,
2935 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2936 'Can not go ahead with aubmission of approval because Workorder dependencies exists'
2937 );
2938 END IF;
2939 RAISE FND_API.G_EXC_ERROR;
2940 END IF; -- l_return_status
2941 END IF; -- is_ump_deferral.
2942
2943 ahl_utility_pvt.get_wf_process_name(
2944 p_object =>l_object,
2945 x_active =>l_active,
2946 x_process_name =>l_process_name ,
2947 x_item_type =>l_item_type,
2948 x_return_status=>l_return_status,
2949 x_msg_count =>l_msg_count,
2950 x_msg_data =>l_msg_data);
2951 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2952 fnd_log.string
2953 (
2954 fnd_log.level_statement,
2955 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2956 'Workflow active flag : ' || l_active
2957 );
2958 fnd_log.string
2959 (
2960 fnd_log.level_statement,
2961 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2962 'l_process_name : ' || l_process_name
2963 );
2964 fnd_log.string
2965 (
2966 fnd_log.level_statement,
2967 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2968 'l_item_type : ' || l_item_type
2969 );
2970
2971 END IF;
2972
2973 IF((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
2974 ( l_active <> G_YES_FLAG))THEN
2975 /*FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_WF_INACTIVE');
2976 FND_MSG_PUB.ADD;
2977 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2978 fnd_log.string
2979 (
2980 fnd_log.level_error,
2981 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2982 'Can not submit for approval because workflow is not active for Deferral'
2983 );
2984 END IF;
2985 RAISE FND_API.G_EXC_ERROR;*/
2986 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2987 fnd_log.string
2988 (
2989 fnd_log.level_statement,
2990 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
2991 'Workflow is not active so going for automatic approval'
2992 );
2993 END IF;
2994 l_active := G_NO_FLAG;
2995 END IF;
2996
2997 -- make a call to update job status to pending deferral approval and update approval status
2998 AHL_PRD_DF_PVT.process_approval_initiated(
2999 p_unit_deferral_id => p_df_header_rec.unit_deferral_id,
3000 p_object_version_number => p_df_header_rec.object_version_number,
3001 p_new_status => 'DEFERRAL_PENDING',
3002 x_return_status => l_return_status);
3003
3004
3005 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3006 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3007 fnd_log.string
3008 (
3009 fnd_log.level_error,
3010 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
3011 'Can not go ahead with approval because AHL_PRD_DF_PVT.process_approval_initiated threw error'
3012 );
3013 END IF;
3014 RAISE FND_API.G_EXC_ERROR;
3015 END IF;
3016
3017 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3018 fnd_log.string
3019 (
3020 fnd_log.level_statement,
3021 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
3022 'Workflow active flag : ' || l_active
3023 );
3024 END IF;
3025
3026 IF(p_df_header_rec.skip_mr_flag = G_YES_FLAG AND p_df_header_rec.manually_planned_flag = G_NO_FLAG)THEN
3027 l_new_status_code := 'TERMINATED';
3028 ELSIF(p_df_header_rec.skip_mr_flag = G_YES_FLAG AND p_df_header_rec.manually_planned_flag = G_YES_FLAG)THEN
3029 l_new_status_code := 'CANCELLED';
3030 ELSE
3031 l_new_status_code := 'DEFERRED';
3032 END IF;
3033
3034 IF(l_active <> G_NO_FLAG)THEN
3035 Ahl_generic_aprv_pvt.Start_Wf_Process(
3036 P_OBJECT => l_object,
3037 P_APPROVAL_TYPE => 'CONCEPT',
3038 P_ACTIVITY_ID => p_df_header_rec.unit_deferral_id,--unit_deferral_id
3039 P_OBJECT_VERSION_NUMBER => p_df_header_rec.object_version_number,
3040 P_ORIG_STATUS_CODE => p_df_header_rec.approval_status_code,
3041 P_NEW_STATUS_CODE => l_new_status_code ,
3042 P_REJECT_STATUS_CODE => 'DEFERRAL_REJECTED',
3043 P_REQUESTER_USERID => fnd_global.user_id,--1003259,--
3044 P_NOTES_FROM_REQUESTER => '',
3045 P_WORKFLOWPROCESS => 'AHL_GEN_APPROVAL',
3046 P_ITEM_TYPE => 'AHLGAPP');
3047 ELSE
3048 -- make a call for automatic approval
3049 AHL_PRD_DF_PVT.process_approval_approved(
3050 p_unit_deferral_id => p_df_header_rec.unit_deferral_id,
3051 p_object_version_number => p_df_header_rec.object_version_number,
3052 p_new_status => l_new_status_code,
3053 x_return_status => l_return_status);
3054 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3055 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3056 fnd_log.string
3057 (
3058 fnd_log.level_error,
3059 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval',
3060 'Can not go ahead with automatic approval because AHL_PRD_DF_PVT.process_approval_approved threw error'
3061 );
3062 END IF;
3063 RAISE FND_API.G_EXC_ERROR;
3064 END IF;
3065 END IF;
3066
3067 -- throw errors if any
3068 IF(FND_MSG_PUB.count_msg > 0)THEN
3069 RAISE FND_API.G_EXC_ERROR;
3070 END IF;
3071
3072
3073 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3074 fnd_log.string
3075 (
3076 fnd_log.level_procedure,
3077 'ahl.plsql.AHL_PRD_DF_PVT.submit_for_approval.end',
3078 'At the end of PLSQL procedure'
3079 );
3080 END IF;
3081
3082 END submit_for_approval;
3083
3084 -------------------------------------------------------------------------
3085 FUNCTION valid_for_submission(
3086 p_unit_effectivity_id IN NUMBER) RETURN BOOLEAN IS
3087
3088
3089 CURSOR status_code_csr(p_unit_effectivity_id IN NUMBER)IS
3090 SELECT approval_status_code FROM ahl_unit_deferrals_b
3091 WHERE unit_effectivity_id = p_unit_effectivity_id
3092 UNION
3093 SELECT approval_status_code FROM ahl_unit_deferrals_b
3094 WHERE unit_effectivity_id IN
3095 (
3096
3097 /*SELECT ue_id
3098 FROM AHL_UE_RELATIONSHIPS
3099 WHERE relationship_code = 'PARENT'
3100 START WITH related_ue_id = p_unit_effectivity_id
3101 CONNECT BY related_ue_id = PRIOR ue_id
3102 UNION*/--parents are taken care of by now
3103 SELECT distinct related_ue_id
3104 FROM AHL_UE_RELATIONSHIPS
3105 WHERE relationship_code = 'PARENT'
3106 START WITH ue_id = p_unit_effectivity_id
3107 CONNECT BY ue_id = PRIOR related_ue_id
3108 );
3109
3110 l_approval_status_code VARCHAR2(30);
3111
3112
3113 BEGIN
3114
3115 OPEN status_code_csr(p_unit_effectivity_id);
3116 LOOP
3117 FETCH status_code_csr INTO l_approval_status_code;
3118 IF(l_approval_status_code = 'DEFERRAL_PENDING')THEN
3119 CLOSE status_code_csr;
3120 RETURN FALSE;
3121 END IF;
3122 EXIT WHEN status_code_csr%NOTFOUND;
3123 END LOOP;
3124
3125 RETURN TRUE;
3126
3127 END valid_for_submission;
3128
3129
3130 PROCEDURE process_approval_initiated (
3131
3132 p_unit_deferral_id IN NUMBER,
3133 p_object_version_number IN NUMBER,
3134 p_new_status IN VARCHAR2,
3135 x_return_status OUT NOCOPY VARCHAR2)IS
3136
3137 BEGIN
3138 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3139 fnd_log.string
3140 (
3141 fnd_log.level_procedure,
3142 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated.begin',
3143 'At the start of PLSQL procedure'
3144 );
3145 END IF;
3146 x_return_status := FND_API.G_RET_STS_SUCCESS;
3147
3148 IF NOT(is_ump_deferral(p_unit_deferral_id)) THEN
3149 process_workorders(
3150 p_unit_deferral_id => p_unit_deferral_id,
3151 p_object_version_number => p_object_version_number,
3152 p_approval_result_code => G_DEFERRAL_INITIATED,
3153 x_return_status => x_return_status );
3154
3155 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3156 fnd_log.string
3157 (
3158 fnd_log.level_statement,
3159 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated',
3160 'unit_deferral_id : ' || p_unit_deferral_id
3161 );
3162 fnd_log.string
3163 (
3164 fnd_log.level_statement,
3165 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated',
3166 'object_version_number : ' || p_object_version_number
3167 );
3168 fnd_log.string
3169 (
3170 fnd_log.level_statement,
3171 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated',
3172 'Return status after process_workorders API call : ' || x_return_status
3173 );
3174 END IF;
3175
3176 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3177 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3178 fnd_log.string
3179 (
3180 fnd_log.level_unexpected,
3181 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated',
3182 'process_workorders API API threw error'
3183 );
3184 END IF;
3185 RETURN;
3186 END IF;
3187 END IF; -- ump deferral.
3188
3189 process_unit_maint_plan(
3190 p_unit_deferral_id => p_unit_deferral_id,
3191 p_object_version_number => p_object_version_number,
3192 p_approval_result_code => G_DEFERRAL_INITIATED,
3193 p_new_status => NULL,
3194 x_return_status => x_return_status);
3195
3196 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3197 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3198 fnd_log.string
3199 (
3200 fnd_log.level_unexpected,
3201 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated',
3202 'process_unit_maint_plan API threw error'
3203 );
3204 END IF;
3205 RETURN;
3206 END IF;
3207
3208 UPDATE ahl_unit_deferrals_b
3209 SET approval_status_code = p_new_status
3210 WHERE unit_deferral_id = p_unit_deferral_id
3211 AND object_version_number = p_object_version_number;--same transaction of caller API and update already happened
3212
3213 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3214 fnd_log.string
3215 (
3216 fnd_log.level_procedure,
3217 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_initiated.end',
3218 'At the end of PLSQL procedure'
3219 );
3220 END IF;
3221
3222
3223 END process_approval_initiated;
3224
3225 PROCEDURE process_approval_approved (
3226
3227 p_unit_deferral_id IN NUMBER,
3228 p_object_version_number IN NUMBER,
3229 p_new_status IN VARCHAR2,
3230 x_return_status OUT NOCOPY VARCHAR2)IS
3231
3232 CURSOR csi_item_instance_id_csr(p_unit_deferral_id IN NUMBER)
3233 --,p_object_version_number IN NUMBER)
3234 IS
3235 SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B UE, ahl_unit_deferrals_b UD
3236 WHERE UE.unit_effectivity_id = UD.unit_effectivity_id
3237 --AND UD.object_version_number = p_object_version_number
3238 AND UD.unit_deferral_id = p_unit_deferral_id;
3239
3240 l_csi_item_instance_id NUMBER;
3241
3242 -- to check whether MR is not terminated already
3243 /*CURSOR valid_mr_csr(p_unit_deferral_id IN NUMBER,p_object_version_number IN NUMBER) IS
3244 SELECT 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def,
3245 ahl_unit_effectivities_b UE,ahl_unit_deferrals_b UD
3246 WHERE UD.unit_deferral_id = p_unit_deferral_id
3247 AND UD.object_version_number = p_object_version_number
3248 AND UE.unit_effectivity_id = UD.unit_effectivity_id
3249 AND def.mr_header_id = NVL(UE.mr_header_id,def.mr_header_id)
3250 AND def.title = mr.title
3251 AND trunc(sysdate) between trunc(mr.effective_from)
3252 AND trunc(nvl(mr.effective_to, sysdate))
3253 AND mr.version_number >= def.version_number;
3254
3255 l_exists VARCHAR2(1);*/
3256
3257
3258 BEGIN
3259 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3260 fnd_log.string
3261 (
3262 fnd_log.level_procedure,
3263 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved.begin',
3264 'At the start of PLSQL procedure'
3265 );
3266 END IF;
3267
3268 x_return_status := FND_API.G_RET_STS_SUCCESS;
3269
3270
3271 /*OPEN valid_mr_csr(p_unit_deferral_id ,p_object_version_number);
3272 FETCH valid_mr_csr INTO l_exists;
3273 IF(valid_mr_csr%NOTFOUND)THEN
3274 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MR_TERM');
3275 FND_MSG_PUB.ADD;
3276 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3277 fnd_log.string
3278 (
3279 fnd_log.level_error,
3280 'ahl.plsql.AHL_PRD_DF_PVT.process_unit_maint_plan',
3281 'Associated MR has been terminated in FMP'
3282 );
3283 END IF;
3284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3285 CLOSE valid_mr_csr;
3286 RETURN;
3287 END IF;
3288 CLOSE valid_mr_csr; */
3289 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3290 fnd_log.string
3291 (
3292 fnd_log.level_statement,
3293 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3294 'unit_deferral_id : ' || p_unit_deferral_id
3295 );
3296 fnd_log.string
3297 (
3298 fnd_log.level_statement,
3299 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3300 'object_version_number : ' || p_object_version_number
3301 );
3302 END IF;
3303
3304 IF NOT(is_ump_deferral(p_unit_deferral_id)) THEN
3305 --update workorders
3306 process_workorders(
3307 p_unit_deferral_id => p_unit_deferral_id,
3308 p_object_version_number => p_object_version_number,
3309 p_approval_result_code => G_DEFERRAL_APPROVED,
3310 x_return_status => x_return_status);
3311
3312 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3313 fnd_log.string
3314 (
3315 fnd_log.level_statement,
3316 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3317 'Return status after process_workorders API call : ' || x_return_status
3318 );
3319 END IF;
3320
3321 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3322 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3323 fnd_log.string
3324 (
3325 fnd_log.level_unexpected,
3326 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3327 'process_workorders API threw error'
3328 );
3329 END IF;
3330 RETURN;
3331 END IF;
3332 END IF; -- UMP deferral.
3333
3334 -- copy unit effectivities and update ue status(update unit maintenance plan)
3335 process_unit_maint_plan(
3336 p_unit_deferral_id => p_unit_deferral_id,
3337 p_object_version_number => p_object_version_number,
3338 p_approval_result_code => G_DEFERRAL_APPROVED,
3339 p_new_status => p_new_status,
3340 x_return_status => x_return_status);
3341
3342 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3343
3344 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3345 fnd_log.string
3346 (
3347 fnd_log.level_unexpected,
3348 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3349 'process_unit_maint_plan API threw error'
3350 );
3351 END IF;
3352 RETURN;
3353 END IF;
3354
3355 -- update unit_effectivity_status
3356 UPDATE ahl_unit_deferrals_b
3357 SET approval_status_code = 'DEFERRED',
3358 object_version_number = p_object_version_number + 1
3359 WHERE unit_deferral_id = p_unit_deferral_id
3360 AND object_version_number = p_object_version_number;
3361
3362
3363 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)THEN
3364 fnd_log.string
3365 (
3366 fnd_log.level_event,
3367 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3368 'Succesfully approved deferral for unit_deferral_id : ' || p_unit_deferral_id
3369 );
3370 END IF;
3371
3372 -- fetch item instance id
3373 OPEN csi_item_instance_id_csr(p_unit_deferral_id --,p_object_version_number
3374 );
3375 FETCH csi_item_instance_id_csr INTO l_csi_item_instance_id;
3376 IF(csi_item_instance_id_csr%NOTFOUND)THEN
3377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3378 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3379 fnd_log.string
3380 (
3381 fnd_log.level_unexpected,
3382 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3383 'Unit Effectivity record not found when fetching item instance id'
3384 );
3385 END IF;
3386 CLOSE csi_item_instance_id_csr;
3387 RETURN;
3388 ELSE
3389 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3390 fnd_log.string
3391 (
3392 fnd_log.level_statement,
3393 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3394 'p_csi_item_instance_id : ' || l_csi_item_instance_id
3395 );
3396 END IF;
3397 -- then call due date calc concurrent request
3398 calculate_due_date(
3399 x_return_status => x_return_status ,
3400 p_csi_item_instance_id => l_csi_item_instance_id
3401 );
3402
3403 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3404 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3405 fnd_log.string
3406 (
3407 fnd_log.level_unexpected,
3408 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3409 'Could not calculate due date'
3410 );
3411 END IF;
3412 RETURN;
3413 END IF;
3414 END IF;
3415
3416 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3417 fnd_log.string
3418 (
3419 fnd_log.level_procedure,
3420 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved.end',
3421 'At the end of PLSQL procedure'
3422 );
3423 END IF;
3424
3425 END process_approval_approved;
3426
3427 PROCEDURE calculate_due_date(
3428 x_return_status OUT NOCOPY VARCHAR2,
3429 p_csi_item_instance_id IN NUMBER
3430 ) IS
3431
3432 l_targetp NUMBER;
3433 l_activep NUMBER;
3434 l_targetp1 NUMBER;
3435 l_activep1 NUMBER;
3436 l_pmon_method VARCHAR2(30);
3437 l_callstat NUMBER;
3438 l_req_id NUMBER;
3439
3440 l_can_submit_request BOOLEAN := TRUE;
3441 l_concurrent_request_sucess BOOLEAN := FALSE;
3442
3443 l_msg_count NUMBER;
3444 l_msg_data VARCHAR2(4000);
3445
3446 BEGIN
3447 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3448 fnd_log.string
3449 (
3450 fnd_log.level_procedure,
3451 'ahl.plsql.AHL_PRD_DF_PVT.calculate_due_date.begin',
3452 'At the start of PLSQL procedure'
3453 );
3454 END IF;
3455 -- check whether Internal Concurrent Manager is up
3456 fnd_concurrent.get_manager_status(applid => 0,
3457 managerid => 1,
3458 targetp => l_targetp1,
3459 activep => l_activep1,
3460 pmon_method => l_pmon_method,
3461 callstat => l_callstat);
3462 -- check whether Standard Concurrent Manager is up, this is not optional.
3463 fnd_concurrent.get_manager_status(applid => 0,
3464 managerid => 0,
3465 targetp => l_targetp,
3466 activep => l_activep,
3467 pmon_method => l_pmon_method,
3468 callstat => l_callstat);
3469 IF (l_activep <= 0 OR l_activep1 <= 0) THEN
3470 l_can_submit_request := FALSE;
3471 ELSIF NOT fnd_program.program_exists('AHLUEFF','AHL') THEN
3472 l_can_submit_request := FALSE;
3473 ELSIF NOT fnd_program.executable_exists('AHLUEFF','AHL') THEN
3474 l_can_submit_request := FALSE;
3475 END IF;
3476
3477 -- submit request
3478 --IF(l_can_submit_request)THEN
3479 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3480 fnd_log.string
3481 (
3482 fnd_log.level_statement,
3483 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3484 'Submitting concurrent request to calculate due date for p_csi_item_instance_id : ' || p_csi_item_instance_id
3485 );
3486 END IF;
3487 --l_req_id := fnd_request.submit_request('AHL','AHLUEFF',NULL,NULL,FALSE,NULL,NULL,p_csi_item_instance_id );
3488 -- modification due to additional parameters added to AHLUEFF
3489 l_req_id := fnd_request.submit_request('AHL','AHLUEFF',NULL,NULL,FALSE,NULL,NULL,p_csi_item_instance_id,NULL,NULL,1);
3490
3491 IF (l_req_id = 0 ) THEN
3492 l_concurrent_request_sucess := FALSE;
3493 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3494 fnd_log.string
3495 (
3496 fnd_log.level_statement,
3497 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3498 'Tried to submit concurrent request but failed'
3499 );
3500 END IF;
3501 ELSE
3502 l_concurrent_request_sucess := TRUE;
3503 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3504 fnd_log.string
3505 (
3506 fnd_log.level_statement,
3507 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3508 'Concurrent request to calculate due date successful'
3509 );
3510 END IF;
3511 END IF;
3512 --END IF;
3513
3514 /* -- launching concurrent program always
3515 IF NOT (l_concurrent_request_sucess) THEN
3516 -- submit online request
3517 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3518 fnd_log.string
3519 (
3520 fnd_log.level_statement,
3521 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_approved',
3522 'Calculating due date online'
3523 );
3524 END IF;
3525
3526 AHL_UMP_PROCESSUNIT_PVT.Process_Unit (
3527 p_commit => FND_API.G_FALSE,
3528 p_init_msg_list => FND_API.G_FALSE,
3529 x_msg_count => l_msg_count,
3530 x_msg_data => l_msg_data,
3531 x_return_status => x_return_status,
3532 p_csi_item_instance_id => p_csi_item_instance_id,
3533 p_concurrent_flag => 'N');
3534
3535 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3536 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3537 fnd_log.string
3538 (
3539 fnd_log.level_unexpected,
3540 'ahl.plsql.AHL_PRD_DF_PVT.calculate_due_date',
3541 'AHL_UMP_PROCESSUNIT_PVT.Process_Unit API threw error'
3542 );
3543 END IF;
3544 RETURN;
3545 END IF;
3546 END IF; */
3547
3548 x_return_status := FND_API.G_RET_STS_SUCCESS;
3549
3550 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3551 fnd_log.string
3552 (
3553 fnd_log.level_procedure,
3554 'ahl.plsql.AHL_PRD_DF_PVT.calculate_due_date.end',
3555 'At the end of PLSQL procedure'
3556 );
3557 END IF;
3558
3559 END calculate_due_date;
3560
3561
3562 PROCEDURE process_approval_rejected (
3563
3564 p_unit_deferral_id IN NUMBER,
3565 p_object_version_number IN NUMBER,
3566 p_new_status IN VARCHAR2,
3567 x_return_status OUT NOCOPY VARCHAR2)IS
3568 BEGIN
3569 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3570 fnd_log.string
3571 (
3572 fnd_log.level_procedure,
3573 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected.begin',
3574 'At the start of PLSQL procedure'
3575 );
3576 END IF;
3577
3578 x_return_status := FND_API.G_RET_STS_SUCCESS;
3579
3580 -- bypass for UMP Deferral
3581 IF NOT(Is_UMP_Deferral(p_unit_deferral_id)) THEN
3582
3583 process_workorders(
3584 p_unit_deferral_id => p_unit_deferral_id,
3585 p_object_version_number => p_object_version_number,
3586 p_approval_result_code => G_DEFERRAL_REJECTED,
3587 x_return_status => x_return_status);
3588
3589 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3590 fnd_log.string
3591 (
3592 fnd_log.level_statement,
3593 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected',
3594 'unit_deferral_id : ' || p_unit_deferral_id
3595 );
3596 fnd_log.string
3597 (
3598 fnd_log.level_statement,
3599 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected',
3600 'object_version_number : ' || p_object_version_number
3601 );
3602 fnd_log.string
3603 (
3604 fnd_log.level_statement,
3605 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected',
3606 'Return status after process_workorders API call : ' || x_return_status
3607 );
3608 fnd_log.string
3609 (
3610 fnd_log.level_statement,
3611 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected',
3612 'New approval status : ' || p_new_status
3613 );
3614 END IF;
3615
3616 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3617 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3618 fnd_log.string
3619 (
3620 fnd_log.level_unexpected,
3621 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected',
3622 'process_workorders API threw error'
3623 );
3624 END IF;
3625 RETURN;
3626 END IF; -- x_return_status
3627 END IF; -- is_ump_deferral.
3628
3629 -- update unit maintenance plan)
3630 process_unit_maint_plan(
3631 p_unit_deferral_id => p_unit_deferral_id,
3632 p_object_version_number => p_object_version_number,
3633 p_approval_result_code => G_DEFERRAL_REJECTED,
3634 p_new_status => NULL,
3635 x_return_status => x_return_status);
3636
3637 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3638 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3639 fnd_log.string
3640 (
3641 fnd_log.level_unexpected,
3642 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected',
3643 'process_unit_maint_plan API threw error'
3644 );
3645 END IF;
3646 RETURN;
3647 END IF;
3648
3649 UPDATE ahl_unit_deferrals_b
3650 SET approval_status_code = p_new_status,
3651 object_version_number = p_object_version_number + 1
3652 WHERE unit_deferral_id = p_unit_deferral_id
3653 AND object_version_number = p_object_version_number;
3654
3655 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3656 fnd_log.string
3657 (
3658 fnd_log.level_procedure,
3659 'ahl.plsql.AHL_PRD_DF_PVT.process_approval_rejected.end',
3660 'At the end of PLSQL procedure'
3661 );
3662 END IF;
3663
3664 END process_approval_rejected;
3665
3666
3667 PROCEDURE process_workorders(
3668 p_unit_deferral_id IN NUMBER,
3669 p_object_version_number IN NUMBER,
3670 p_approval_result_code IN VARCHAR2,
3671 x_return_status OUT NOCOPY VARCHAR2) IS
3672
3673 l_prd_workorder_rec AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_REC;
3674 l_temp_prd_workorder_rec AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_REC;
3675
3676
3677 l_prd_workoper_tbl AHL_PRD_WORKORDER_PVT.PRD_WORKOPER_TBL;
3678
3679 --rroy
3680 --ACL Changes
3681 -- Added object_type to query
3682 CURSOR unit_effectivity_id_csr(p_unit_deferral_id IN NUMBER,
3683 p_object_version_number IN NUMBER)IS
3684 SELECT UD.unit_effectivity_id, ue.object_type
3685 from ahl_unit_deferrals_b UD, AHL_UNIT_EFFECTIVITIES_B UE
3686 WHERE NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
3687 AND UE.unit_effectivity_id = UD.unit_effectivity_id
3688 AND UD.object_version_number = p_object_version_number
3689 AND UD.unit_deferral_id = p_unit_deferral_id;
3690 -- rroy
3691 -- ACL Changes
3692
3693 l_unit_effectivity_id NUMBER;
3694 l_ue_title VARCHAR2(155);
3695 -- rroy
3696 -- ACL Changes
3697 l_object_type VARCHAR2(3);
3698 l_return_status VARCHAR2(1);
3699 -- rroy
3700 -- ACL Changes
3701
3702 CURSOR validate_approver_privilages(p_unit_effectivity_id IN NUMBER)IS
3703
3704 /* replaced as this query does not pick up master workorders.
3705 SELECT WO.workorder_id
3706 FROM ahl_workorder_tasks_v WO
3707 WHERE WO.job_status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3708 AND WO.unit_effectivity_id = p_unit_effectivity_id; */
3709
3710 SELECT WO.workorder_id
3711 FROM ahl_workorders wo, ahl_visit_tasks_b vts,
3712 ahl_visits_b vst,
3713 (SELECT ORGANIZATION_ID FROM INV_ORGANIZATION_INFO_V
3714 WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
3715 WHERE wo.visit_task_id = vts.visit_task_id
3716 AND vts.visit_id = vst.visit_id
3717 AND vst.organization_id = org.organization_id
3718 AND WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3719 AND vts.unit_effectivity_id = p_unit_effectivity_id
3720 AND vts.task_type_code IN ('SUMMARY','UNASSOCIATED');
3721
3722 l_workorder_id NUMBER;
3723
3724 -- rroy
3725 -- ACL Changes
3726 -- Added workorder_name to select clause
3727 CURSOR workorder_csr(p_unit_effectivity_id IN NUMBER)IS
3728 SELECT WO.workorder_id,
3729 WO.object_version_number,
3730 WO.status_code,
3731 WO.actual_start_date,
3732 WO.actual_end_date,
3733 WO.workorder_name
3734 FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3735 WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3736 AND WO.master_workorder_flag = 'N'
3737 AND WO.visit_task_id = VST.visit_task_id
3738 AND VST.unit_effectivity_id = UE.unit_effectivity_id
3739 AND UE.unit_effectivity_id = p_unit_effectivity_id
3740 UNION
3741 SELECT WO.workorder_id,
3742 WO.object_version_number,
3743 WO.status_code,
3744 WO.actual_start_date,
3745 WO.actual_end_date,
3746 WO.workorder_name
3747 FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3748 WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3749 AND WO.master_workorder_flag = 'N'
3750 AND WO.visit_task_id = VST.visit_task_id
3751 AND VST.unit_effectivity_id = UE.unit_effectivity_id
3752 AND UE.unit_effectivity_id IN
3753 (
3754
3755 SELECT distinct related_ue_id
3756 FROM AHL_UE_RELATIONSHIPS
3757 WHERE relationship_code = 'PARENT'
3758 START WITH ue_id = p_unit_effectivity_id
3759 CONNECT BY ue_id = PRIOR related_ue_id
3760 );
3761 -- rroy
3762 -- ACL Changes
3763
3764 CURSOR workorder_objver_csr(p_workorder_id IN NUMBER) IS
3765 SELECT object_version_number from ahl_workorders
3766 WHERE workorder_id = p_workorder_id;
3767
3768 --Changes by nsikka for Bug 5324101
3769 --Cursor added to fetch UE Title to be passed as token
3770
3771 CURSOR ue_title_csr(p_unit_effectivity_id IN NUMBER) IS
3772 SELECT title from ahl_unit_effectivities_v
3773 WHERE UNIT_EFFECTIVITY_ID = p_unit_effectivity_id;
3774
3775 l_update_flag BOOLEAN := false;
3776 --l_complete_flag BOOLEAN := false; /* commented out as workorder completion is no longer needed. */
3777
3778 l_msg_count NUMBER;
3779 l_msg_data VARCHAR2(4000);
3780
3781 l_temp VARCHAR2(30);
3782
3783 -- fix for bug number 6990380
3784 CURSOR chk_inst_in_job (p_workorder_id IN NUMBER) IS
3785 SELECT 'x'
3786 FROM CSI_ITEM_INSTANCES CII, AHL_WORKORDERS AWO
3787 WHERE CII.WIP_JOB_ID = AWO.WIP_ENTITY_ID
3788 AND AWO.workorder_id = p_workorder_id
3789 AND ACTIVE_START_DATE <= SYSDATE
3790 AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
3791 AND LOCATION_TYPE_CODE = 'WIP'
3792 AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
3793 WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
3794 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3795 AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
3796
3797 l_status_meaning VARCHAR2(80);
3798
3799 BEGIN
3800 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3801 fnd_log.string
3802 (
3803 fnd_log.level_procedure,
3804 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders.begin',
3805 'At the start of PLSQL procedure'
3806 );
3807 END IF;
3808
3809 x_return_status := FND_API.G_RET_STS_SUCCESS;
3810
3811 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3812 fnd_log.string
3813 (
3814 fnd_log.level_statement,
3815 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3816 'unit_deferral_id : ' || p_unit_deferral_id
3817 );
3818 fnd_log.string
3819 (
3820 fnd_log.level_statement,
3821 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3822 'object_version_number : ' || p_object_version_number
3823 );
3824 fnd_log.string
3825 (
3826 fnd_log.level_statement,
3827 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3828 'Approval Result Code : ' || p_approval_result_code
3829 );
3830 END IF;
3831 -- validating ue and getting it
3832 OPEN unit_effectivity_id_csr(p_unit_deferral_id, p_object_version_number);
3833 FETCH unit_effectivity_id_csr INTO l_unit_effectivity_id, l_object_type;
3834 IF(unit_effectivity_id_csr%NOTFOUND)THEN
3835 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INVOP_HREC_MISS');
3836 FND_MSG_PUB.ADD;
3837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3838 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3839 fnd_log.string
3840 (
3841 fnd_log.level_unexpected,
3842 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3843 'Unit Effectivity Record not found for unit deferral id : ' || p_unit_deferral_id
3844 );
3845 END IF;
3846 END IF;
3847 CLOSE unit_effectivity_id_csr;
3848
3849 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3850 RETURN;
3851 END IF;
3852
3853 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3854 fnd_log.string
3855 (
3856 fnd_log.level_statement,
3857 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3858 'unit_effectivity_id : ' || l_unit_effectivity_id
3859 );
3860 END IF;
3861
3862 IF (p_approval_result_code IN ( G_DEFERRAL_REJECTED,G_DEFERRAL_APPROVED ))THEN
3863 OPEN validate_approver_privilages(l_unit_effectivity_id);
3864 FETCH validate_approver_privilages INTO l_workorder_id;
3865 IF(validate_approver_privilages%NOTFOUND)THEN
3866 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_APPR_SETUP');
3867 FND_MSG_PUB.ADD;
3868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3869 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3870 fnd_log.string
3871 (
3872 fnd_log.level_unexpected,
3873 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3874 'Approver client information not same as the requester. workorders not found for approver'
3875 );
3876 END IF;
3877 END IF;
3878 CLOSE validate_approver_privilages;
3879 END IF;
3880
3881 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3882 RETURN;
3883 END IF;
3884
3885 --OPEN workorder_csr(l_unit_effectivity_id);
3886 FOR workorder_rec IN workorder_csr(l_unit_effectivity_id) LOOP
3887 l_update_flag := FALSE;
3888 --l_complete_flag := FALSE;
3889 l_prd_workorder_rec := l_temp_prd_workorder_rec;--initialize it
3890 l_prd_workorder_rec.workorder_id := workorder_rec.workorder_id;
3891 l_prd_workorder_rec.object_version_number := workorder_rec.object_version_number;
3892 l_prd_workorder_rec.status_code := workorder_rec.status_code;
3893 l_prd_workorder_rec.actual_start_date := workorder_rec.actual_start_date;
3894 l_prd_workorder_rec.actual_end_date := workorder_rec.actual_end_date;
3895
3896 -- rroy
3897 -- ACL Changes
3898 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_prd_workorder_rec.workorder_id,
3899 p_ue_id => NULL, p_visit_id => NULL,
3900 p_item_instance_id => NULL);
3901
3902
3903 --nsikka
3904 --Changes made for Bug 5324101 .
3905 --tokens passed changed to MR_TITLE
3906
3907 IF l_return_status = FND_API.G_TRUE THEN
3908 IF l_object_type IS NOT NULL AND l_object_type = 'SR' THEN
3909 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_DFSR_UNTLCKD');
3910 FND_MESSAGE.Set_Token('WO_NAME', workorder_rec.workorder_name);
3911 FND_MSG_PUB.ADD;
3912 ELSE
3913 OPEN ue_title_csr(l_unit_effectivity_id);
3914 FETCH ue_title_csr into l_ue_title;
3915 CLOSE ue_title_csr;
3916 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_DFMR_UNTLCKD');
3917 FND_MESSAGE.Set_Token('MR_TITLE', l_ue_title);
3918 FND_MSG_PUB.ADD;
3919 END IF;
3920 EXIT;
3921 END IF;
3922 -- rroy
3923 -- ACL Changes
3924
3925 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3926 fnd_log.string
3927 (
3928 fnd_log.level_statement,
3929 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3930 'workorder_id : ' || l_prd_workorder_rec.workorder_id
3931 );
3932 fnd_log.string
3933 (
3934 fnd_log.level_statement,
3935 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3936 'object_version_number : ' || l_prd_workorder_rec.object_version_number
3937 );
3938 fnd_log.string
3939 (
3940 fnd_log.level_statement,
3941 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3942 'Current Workorder status : ' || l_prd_workorder_rec.status_code
3943 );
3944 END IF;
3945
3946 IF(p_approval_result_code = G_DEFERRAL_INITIATED)THEN
3947 --l_prd_workorder_rec.status_code := G_JOB_UNRELEASED;
3948 --l_update_flag := TRUE;
3949 IF(isValidStatusUpdate(G_DEFERRAL_INITIATED, l_prd_workorder_rec.status_code))THEN
3950 l_update_flag := TRUE;
3951 l_prd_workorder_rec.status_code := G_JOB_PEND_DFR_APPR;
3952 END IF;
3953 ELSIF (p_approval_result_code = G_DEFERRAL_REJECTED)THEN
3954 IF(isValidStatusUpdate(G_DEFERRAL_REJECTED, l_prd_workorder_rec.status_code))THEN
3955 l_update_flag := TRUE;
3956 -- find out the old status and set it here
3957 l_prd_workorder_rec.status_code := getLastStatus(l_prd_workorder_rec.workorder_id);
3958 END IF;
3959 /* sracha: commented out as workorder completion is no longer needed.*/
3960 /*ELSIF (p_approval_result_code = G_DEFERRAL_APPROVED)THEN
3961 -- find out the old status and populate here
3962 IF(l_prd_workorder_rec.status_code = G_JOB_PEND_DFR_APPR)THEN
3963 l_prd_workorder_rec.status_code := getLastStatus(l_prd_workorder_rec.workorder_id);
3964 END IF;
3965 --IF(isValidStatusUpdate(G_DEFERRAL_APPROVED, l_prd_workorder_rec.status_code))THEN
3966 -- l_update_flag := TRUE;
3967 -- l_prd_workorder_rec.status_code := G_JOB_CANCELLED;
3968 --ELSIF (l_prd_workorder_rec.status_code IN(G_JOB_RELEASED,G_JOB_PARTS_HOLD,G_JOB_ON_HOLD,G_JOB_PEND_QA_APPR))THEN
3969 -- l_complete_flag := TRUE;
3970 --END IF;
3971 IF (l_prd_workorder_rec.status_code IN(G_JOB_UNRELEASED,G_JOB_RELEASED,G_JOB_PARTS_HOLD,G_JOB_ON_HOLD,G_JOB_PEND_QA_APPR))THEN
3972 l_complete_flag := TRUE;
3973 END IF; */
3974 /* sracha - end */
3975 END IF;
3976
3977 -- call production API to update job status or complete job
3978 IF(l_update_flag)THEN
3979 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3980 fnd_log.string
3981 (
3982 fnd_log.level_statement,
3983 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3984 'Workorder Update Flag :TRUE '
3985 );
3986 fnd_log.string
3987 (
3988 fnd_log.level_statement,
3989 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
3990 'new Workorder status/Update : ' || l_prd_workorder_rec.status_code
3991 );
3992 END IF;
3993
3994 IF (l_prd_workorder_rec.status_code IN (G_JOB_CANCELLED,G_JOB_PEND_DFR_APPR)) THEN
3995 OPEN chk_inst_in_job(l_prd_workorder_rec.workorder_id);
3996 FETCH chk_inst_in_job INTO l_temp;
3997 IF (chk_inst_in_job%FOUND) THEN
3998 --Get status meaning
3999 SELECT meaning INTO l_status_meaning
4000 FROM fnd_lookup_values_vl
4001 WHERE lookup_type = 'AHL_JOB_STATUS'
4002 AND LOOKUP_CODE = l_prd_workorder_rec.status_code;
4003 FND_MESSAGE.set_name('AHL','AHL_PRD_MAT_NOT_RETURN');
4004 FND_MESSAGE.set_token('WO_STATUS', l_status_meaning);
4005 FND_MESSAGE.set_token('WO_NAME', workorder_rec.workorder_name);
4006 FND_MSG_PUB.add;
4007 x_return_status := FND_API.G_RET_STS_ERROR;
4008 END IF;
4009 CLOSE chk_inst_in_job;
4010 END IF;
4011 IF(x_return_status = FND_API.G_RET_STS_SUCCESS)THEN
4012 -- call update job API
4013 AHL_PRD_WORKORDER_PVT.update_job
4014 (
4015 p_api_version => 1.0,
4016 p_init_msg_list => FND_API.G_FALSE,
4017 p_commit => FND_API.G_FALSE,
4018 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4019 p_default => FND_API.G_TRUE,
4020 p_module_type => 'API',
4021 x_return_status => x_return_status,
4022 x_msg_count => l_msg_count,
4023 x_msg_data => l_msg_data,
4024 p_x_prd_workorder_rec => l_prd_workorder_rec,
4025 p_x_prd_workoper_tbl => l_prd_workoper_tbl
4026 );
4027 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4028 fnd_log.string
4029 (
4030 fnd_log.level_statement,
4031 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4032 'Status after AHL_PRD_WORKORDER_PVT.update_job API call : ' || x_return_status
4033 );
4034 END IF;
4035 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4036 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_UPD_JB_FAIL');
4037 FND_MSG_PUB.ADD;
4038 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
4039 fnd_log.string
4040 (
4041 fnd_log.level_unexpected,
4042 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4043 'AHL_PRD_WORKORDER_PVT.update_job API returned error '
4044 );
4045 END IF;
4046 EXIT;
4047 END IF;
4048 /* sracha: commented out as workorder completion is no longer needed. */
4049 /* ELSIF(l_complete_flag)THEN
4050 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4051 fnd_log.string
4052 (
4053 fnd_log.level_statement,
4054 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4055 'Workorder Complete Flag :TRUE '
4056 );
4057 fnd_log.string
4058 (
4059 fnd_log.level_statement,
4060 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4061 'new Workorder status/Complete : ' || l_prd_workorder_rec.status_code
4062 );
4063 END IF;
4064 l_prd_workorder_rec.status_code := G_JOB_RELEASED;
4065
4066 -- modified 4/24/06 to not pass actual start dates --FP bug# 5114848.
4067 -- actual dates are not needed if WO is being cancelled.
4068 --l_prd_workorder_rec.actual_start_date := NVL(l_prd_workorder_rec.actual_start_date,SYSDATE);
4069 --l_prd_workorder_rec.actual_end_date := NVL(l_prd_workorder_rec.actual_end_date,SYSDATE);
4070
4071 -- call update job API
4072 AHL_PRD_WORKORDER_PVT.update_job
4073 (
4074 p_api_version => 1.0,
4075 p_init_msg_list => FND_API.G_FALSE,
4076 p_commit => FND_API.G_FALSE,
4077 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4078 p_default => FND_API.G_TRUE,
4079 p_module_type => 'API',
4080 x_return_status => x_return_status,
4081 x_msg_count => l_msg_count,
4082 x_msg_data => l_msg_data,
4083 p_x_prd_workorder_rec => l_prd_workorder_rec,
4084 p_x_prd_workoper_tbl => l_prd_workoper_tbl
4085 );
4086 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4087 fnd_log.string
4088 (
4089 fnd_log.level_statement,
4090 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4091 'Status after AHL_PRD_WORKORDER_PVT.update_job API call : ' || x_return_status
4092 );
4093 END IF;
4094 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4095 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_UPD_JB_FAIL');
4096 FND_MSG_PUB.ADD;
4097 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
4098 fnd_log.string
4099 (
4100 fnd_log.level_unexpected,
4101 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4102 'AHL_PRD_WORKORDER_PVT.update_job API returned error '
4103 );
4104 END IF;
4105 EXIT;
4106 END IF; */
4107 -- sure to find record here
4108 -- Following code has been commented because we have got an API which will cancel all jobs for us
4109 -- after approval of deferral.
4110 -- also in 11.5.10+ we will cancel all workorders inspite of their statuses.
4111 /*OPEN workorder_objver_csr(l_prd_workorder_rec.workorder_id);
4112 FETCH workorder_objver_csr INTO l_prd_workorder_rec.object_version_number;
4113 CLOSE workorder_objver_csr;
4114
4115 AHL_COMPLETIONS_PVT.defer_workorder
4116 (
4117 p_api_version => 1.0,
4118 p_init_msg_list => FND_API.G_FALSE,
4119 p_commit => FND_API.G_FALSE,
4120 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4121 p_default => FND_API.G_TRUE,
4122 p_module_type => 'API',
4123 x_return_status => x_return_status,
4124 x_msg_count => l_msg_count,
4125 x_msg_data => l_msg_data,
4126 p_workorder_id => l_prd_workorder_rec.workorder_id,
4127 p_object_version_no => l_prd_workorder_rec.object_version_number
4128 );
4129 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4130 fnd_log.string
4131 (
4132 fnd_log.level_statement,
4133 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4134 'Status after AHL_COMPLETIONS_PVT.defer_workorder API call : ' || x_return_status
4135 );
4136 END IF;
4137 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4138 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_CMP_JB_FAIL');
4139 FND_MSG_PUB.ADD;
4140 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
4141 fnd_log.string
4142 (
4143 fnd_log.level_unexpected,
4144 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4145 'AHL_COMPLETIONS_PVT.defer_workorder API returned error '
4146 );
4147 END IF;
4148 EXIT;
4149 END IF;*/
4150 END IF;
4151 END IF;
4152 END LOOP;
4153
4154 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4155 fnd_log.string
4156 (
4157 fnd_log.level_statement,
4158 'before ahl.plsql.AHL_PRD_WORKORDER_PVT.cancel_visit_jobs',
4159 'Workorder Cancel Visits Jobs '
4160 );
4161 END IF;
4162
4163 -- throw errors if any
4164 IF(FND_MSG_PUB.count_msg > 0)THEN
4165 RETURN;
4166 END IF;
4167
4168 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4169 fnd_log.string
4170 (
4171 fnd_log.level_statement,
4172 'ahl.plsql.AHL_PRD_WORKORDER_PVT.cancel_visit_jobs',
4173 'Workorder Cancel Visits Jobs '
4174 );
4175 END IF;
4176
4177 -- if approval was approved, cancel all workorders
4178 IF (p_approval_result_code = G_DEFERRAL_APPROVED)THEN
4179 AHL_PRD_WORKORDER_PVT.cancel_visit_jobs
4180 (
4181 p_api_version => 1.0,
4182 p_init_msg_list => FND_API.G_TRUE,
4183 p_commit => FND_API.G_FALSE,
4184 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4185 p_default => FND_API.G_FALSE,
4186 p_module_type => 'API',
4187 x_return_status => x_return_status,
4188 x_msg_count => l_msg_count,
4189 x_msg_data => l_msg_data,
4190 p_visit_id => NULL,
4191 p_unit_effectivity_id => l_unit_effectivity_id,
4192 p_workorder_id => NULL
4193 );
4194 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4195 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_CMP_JB_FAIL');
4196 FND_MSG_PUB.ADD;
4197 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
4198 fnd_log.string
4199 (
4200 fnd_log.level_unexpected,
4201 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
4202 'AHL_PRD_WORKORDERS_PVT.cancel_visit_jobs API returned error '
4203 );
4204 END IF;
4205 END IF;
4206 END IF;
4207
4208 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4209 fnd_log.string
4210 (
4211 fnd_log.level_procedure,
4212 'ahl.plsql.AHL_PRD_DF_PVT.process_workorders.end',
4213 'At the end of PLSQL procedure'
4214 );
4215 END IF;
4216 END process_workorders;
4217
4218 FUNCTION isValidStatusUpdate(
4219 operation_code VARCHAR2,
4220 status_code VARCHAR2)RETURN BOOLEAN IS
4221
4222 l_yes_flag boolean := FALSE;
4223
4224 BEGIN
4225 IF(operation_code = G_DEFERRAL_INITIATED)THEN
4226 IF(status_code IN (G_JOB_UNRELEASED,G_JOB_RELEASED,G_JOB_PARTS_HOLD,G_JOB_ON_HOLD,G_JOB_PEND_QA_APPR))THEN
4227 l_yes_flag := TRUE;
4228 END IF;
4229 ELSIF(operation_code = G_DEFERRAL_REJECTED)THEN
4230 IF(status_code = G_JOB_PEND_DFR_APPR)THEN
4231 l_yes_flag := TRUE;
4232 END IF;
4233 /*ELSIF(operation_code = G_DEFERRAL_APPROVED)THEN
4234 IF(status_code IN (G_JOB_UNRELEASED))THEN
4235 l_yes_flag := TRUE;
4236 END IF;*/
4237 END IF;
4238 RETURN l_yes_flag;
4239 END isValidStatusUpdate;
4240
4241 FUNCTION getLastStatus(p_workorder_id IN NUMBER) RETURN VARCHAR2 IS
4242
4243 CURSOR last_status_code_csr(p_workorder_id IN NUMBER) IS
4244 SELECT status_code,last_update_date FROM ahl_workorder_txns
4245 WHERE workorder_id = p_workorder_id ORDER BY last_update_date DESC;
4246
4247 l_junk_date DATE;
4248 l_last_status_code VARCHAR2(30);
4249 BEGIN
4250 OPEN last_status_code_csr(p_workorder_id);
4251 LOOP
4252 FETCH last_status_code_csr INTO l_last_status_code,l_junk_date;
4253 IF(last_status_code_csr%NOTFOUND)THEN
4254 l_last_status_code := G_JOB_UNRELEASED;
4255 EXIT;
4256 ELSIF (l_last_status_code <> G_JOB_PEND_DFR_APPR)THEN
4257 EXIT;
4258 END IF;
4259 END LOOP;
4260 CLOSE last_status_code_csr;
4261 RETURN l_last_status_code;
4262 END getLastStatus;
4263
4264
4265 PROCEDURE process_unit_maint_plan(
4266 p_unit_deferral_id IN NUMBER,
4267 p_object_version_number IN NUMBER,
4268 p_approval_result_code IN VARCHAR2,
4269 p_new_status IN VARCHAR2,
4270 x_return_status OUT NOCOPY VARCHAR2)IS
4271
4272 -- to fetch unit effectivity id
4273 CURSOR unit_effectivity_id_csr(p_unit_deferral_id IN NUMBER,p_object_version_number IN NUMBER) IS
4274 SELECT UD.unit_effectivity_id from ahl_unit_deferrals_b UD
4275 WHERE UD.object_version_number = p_object_version_number
4276 AND UD.unit_deferral_id = p_unit_deferral_id;
4277
4278 l_unit_effectivity_id NUMBER;
4279
4280 CURSOR unit_effectivity_csr (p_unit_effectivity_id IN NUMBER) IS
4281 SELECT
4282 UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, CSI_ITEM_INSTANCE_ID, MR_INTERVAL_ID,
4283 MR_EFFECTIVITY_ID, MR_HEADER_ID, STATUS_CODE, SET_DUE_DATE, ACCOMPLISHED_DATE,
4284 DUE_DATE, DUE_COUNTER_VALUE, FORECAST_SEQUENCE, REPETITIVE_MR_FLAG,
4285 TOLERANCE_FLAG, DATE_RUN, PRECEDING_UE_ID, MESSAGE_CODE, REMARKS,
4286 SERVICE_LINE_ID, PROGRAM_MR_HEADER_ID, CANCEL_REASON_CODE, EARLIEST_DUE_DATE,
4287 LATEST_DUE_DATE, DEFER_FROM_UE_ID, CS_INCIDENT_ID, QA_COLLECTION_ID,
4288 ORIG_DEFERRAL_UE_ID, COUNTER_ID,OBJECT_TYPE,MANUALLY_PLANNED_FLAG,
4289 LOG_SERIES_CODE,LOG_SERIES_NUMBER,FLIGHT_NUMBER, MEL_CDL_TYPE_CODE,
4290 POSITION_PATH_ID, ATA_CODE, UNIT_CONFIG_HEADER_ID,
4291 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
4292 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,ATTRIBUTE9,
4293 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,ATTRIBUTE15
4294 FROM AHL_UNIT_EFFECTIVITIES_VL UE
4295 WHERE unit_effectivity_id = p_unit_effectivity_id
4296 UNION
4297 SELECT
4298 UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, CSI_ITEM_INSTANCE_ID, MR_INTERVAL_ID,
4299 MR_EFFECTIVITY_ID, MR_HEADER_ID, STATUS_CODE, SET_DUE_DATE, ACCOMPLISHED_DATE,
4300 DUE_DATE, DUE_COUNTER_VALUE, FORECAST_SEQUENCE, REPETITIVE_MR_FLAG,
4301 TOLERANCE_FLAG, DATE_RUN, PRECEDING_UE_ID, MESSAGE_CODE, REMARKS,
4302 SERVICE_LINE_ID, PROGRAM_MR_HEADER_ID, CANCEL_REASON_CODE, EARLIEST_DUE_DATE,
4303 LATEST_DUE_DATE, DEFER_FROM_UE_ID, CS_INCIDENT_ID, QA_COLLECTION_ID,
4304 ORIG_DEFERRAL_UE_ID, COUNTER_ID,OBJECT_TYPE,MANUALLY_PLANNED_FLAG,
4305 LOG_SERIES_CODE,LOG_SERIES_NUMBER,FLIGHT_NUMBER, MEL_CDL_TYPE_CODE,
4306 POSITION_PATH_ID, ATA_CODE, UNIT_CONFIG_HEADER_ID,
4307 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
4308 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,ATTRIBUTE9,
4309 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,ATTRIBUTE15
4310 FROM AHL_UNIT_EFFECTIVITIES_VL UE
4311 WHERE unit_effectivity_id IN
4312 (
4313
4314 SELECT distinct related_ue_id
4315 FROM AHL_UE_RELATIONSHIPS
4316 WHERE relationship_code = 'PARENT'
4317 START WITH ue_id = p_unit_effectivity_id
4318 CONNECT BY ue_id = PRIOR related_ue_id
4319 );
4320 --FOR UPDATE OF OBJECT_VERSION_NUMBER;
4321
4322 l_rowid VARCHAR2(80);
4323 l_new_unit_effectivity_id NUMBER;
4324
4325 CURSOR ue_relns_csr(p_unit_effectivity_id IN NUMBER)IS
4326 SELECT distinct ue_id, related_ue_id, originator_ue_id
4327 FROM AHL_UE_RELATIONSHIPS
4328 WHERE relationship_code = 'PARENT'
4329 START WITH ue_id = p_unit_effectivity_id
4330 CONNECT BY ue_id = PRIOR related_ue_id;
4331
4332 TYPE ue_relns_rec_type IS RECORD(
4333 ue_id NUMBER,
4334 related_ue_id NUMBER,
4335 originator_ue_id NUMBER
4336 );
4337
4338 TYPE ue_relns_tbl_type IS TABLE OF ue_relns_rec_type INDEX BY BINARY_INTEGER;
4339
4340 l_ue_relns_tbl ue_relns_tbl_type;
4341
4342 i NUMBER := 0;
4343 l_ue_relationship_id NUMBER;
4344 l_new_parent_ue_id NUMBER;
4345 l_orig_deferral_ue_id NUMBER;
4346 l_update_status VARCHAR2(30);
4347
4348 -- TAMAL -- Begin changes for ER #3356804
4349 -- TAMAL -- Adding cursor to retrieve UE and SR details
4350 CURSOR get_ue_sr_details
4351 (
4352 p_ue_id in number
4353 )
4354 IS
4355 SELECT ue.object_type,
4356 sr.incident_id,
4357 sr.incident_number,
4358 sr.object_version_number
4359 FROM
4360 ahl_unit_effectivities_b ue,
4361 cs_incidents_all_b sr
4362 WHERE
4363 ue.cs_incident_id = sr.incident_id (+) and
4364 ue.unit_effectivity_id = p_ue_id;
4365
4366 l_ue_obj_type VARCHAR2(3);
4367 l_cs_incident_id NUMBER;
4368 l_cs_incident_number VARCHAR2(64);
4369 l_cs_incident_ovn NUMBER;
4370 l_interaction_id NUMBER;
4371 l_return_status VARCHAR2(1);
4372 l_msg_count NUMBER;
4373 l_msg_data VARCHAR2(4000);
4374 -- TAMAL -- End changes for ER #3356804
4375
4376 -- Support for SR cancellation.
4377 l_status_id NUMBER;
4378
4379 BEGIN
4380
4381 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4382 fnd_log.string
4383 (
4384 fnd_log.level_procedure,
4385 'ahl.plsql.AHL_PRD_DF_PVT.process_unit_maint_plan.begin',
4386 'At the start of PLSQL procedure'
4387 );
4388 END IF;
4389
4390 x_return_status := FND_API.G_RET_STS_SUCCESS;
4391
4392 OPEN unit_effectivity_id_csr(p_unit_deferral_id ,p_object_version_number);
4393 FETCH unit_effectivity_id_csr INTO l_unit_effectivity_id;
4394 IF(unit_effectivity_id_csr%NOTFOUND)THEN
4395 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_INV_DF');
4396 FND_MESSAGE.SET_TOKEN('DEFERRAL_ID',p_unit_deferral_id);
4397 FND_MSG_PUB.ADD;
4398 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
4399 fnd_log.string
4400 (
4401 fnd_log.level_unexpected,
4402 'ahl.plsql.AHL_PRD_DF_PVT.process_unit_maint_plan',
4403 'Deferral record details not found for unit deferral id : ' || p_unit_deferral_id
4404 );
4405 END IF;
4406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4407 END IF;
4408 CLOSE unit_effectivity_id_csr;
4409
4410 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4411 RETURN;
4412 END IF;
4413
4414 IF(p_approval_result_code = G_DEFERRAL_APPROVED)THEN
4415 -- clean up prior deferrals
4416 process_prior_ump_deferrals(l_unit_effectivity_id);
4417 -- get relationship tree snapshot
4418 IF(p_new_status = 'DEFERRED')THEN
4419 FOR ue_relns_rec IN ue_relns_csr(l_unit_effectivity_id) LOOP
4420 l_ue_relns_tbl(i).ue_id := ue_relns_rec.ue_id;
4421 l_ue_relns_tbl(i).related_ue_id := ue_relns_rec.related_ue_id;
4422 l_ue_relns_tbl(i).originator_ue_id := ue_relns_rec.originator_ue_id;
4423 i := i + 1;
4424 END LOOP;
4425 END IF;
4426 -- Loop through old records.update its status and insert new ones, update ue relationships table
4427 FOR ue_rec IN unit_effectivity_csr(l_unit_effectivity_id) LOOP
4428
4429 IF(p_new_status = 'DEFERRED')THEN
4430 -- copy records here
4431 l_new_unit_effectivity_id := NULL;
4432 l_rowid := NULL;
4433
4434 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
4435 X_ROWID => l_rowid,
4436 X_UNIT_EFFECTIVITY_ID => l_new_unit_effectivity_id,
4437 X_CSI_ITEM_INSTANCE_ID => ue_rec.csi_item_instance_id,
4438 X_MR_HEADER_ID => ue_rec.mr_header_id,
4439 X_REPETITIVE_MR_FLAG => ue_rec.repetitive_mr_flag,
4440 X_REMARKS => ue_rec.remarks,
4441 X_SERVICE_LINE_ID => ue_rec.service_line_id,
4442 X_PROGRAM_MR_HEADER_ID => ue_rec.program_mr_header_id,
4443 X_CS_INCIDENT_ID => ue_rec.cs_incident_id,
4444 X_DEFER_FROM_UE_ID => l_unit_effectivity_id,
4445 X_ORIG_DEFERRAL_UE_ID => NULL,
4446 X_QA_COLLECTION_ID => NULL, --ue_rec.qa_collection_id,
4447 X_MR_INTERVAL_ID => null,
4448 X_MR_EFFECTIVITY_ID => null,
4449 X_STATUS_CODE => null,
4450 X_DUE_DATE => null,
4451 X_DUE_COUNTER_VALUE => null,
4452 X_FORECAST_SEQUENCE => null,
4453 X_TOLERANCE_FLAG => null,
4454 X_MESSAGE_CODE => null,
4455 X_PRECEDING_UE_ID => null,
4456 X_DATE_RUN => null,
4457 X_SET_DUE_DATE => null,
4458 X_ACCOMPLISHED_DATE => null,
4459 X_CANCEL_REASON_CODE => null,
4460 X_EARLIEST_DUE_DATE => null,
4461 X_LATEST_DUE_DATE => null,
4462 X_ATTRIBUTE_CATEGORY => null,
4463 X_ATTRIBUTE1 => null,
4464 X_ATTRIBUTE2 => null,
4465 X_ATTRIBUTE3 => null,
4466 X_ATTRIBUTE4 => null,
4467 X_ATTRIBUTE5 => null,
4468 X_ATTRIBUTE6 => null,
4469 X_ATTRIBUTE7 => null,
4470 X_ATTRIBUTE8 => null,
4471 X_ATTRIBUTE9 => null,
4472 X_ATTRIBUTE10 => null,
4473 X_ATTRIBUTE11 => null,
4474 X_ATTRIBUTE12 => null,
4475 X_ATTRIBUTE13 => null,
4476 X_ATTRIBUTE14 => null,
4477 X_ATTRIBUTE15 => null,
4478 X_OBJECT_VERSION_NUMBER => 1,
4479 X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
4480 X_OBJECT_TYPE => ue_rec.object_type,
4481 X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
4482 X_COUNTER_ID => ue_rec.counter_id,
4483 X_LOG_SERIES_CODE => ue_rec.log_series_code,
4484 X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
4485 X_FLIGHT_NUMBER => ue_rec.flight_number,
4486 X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
4487 X_POSITION_PATH_ID => ue_rec.position_path_id,
4488 X_ATA_CODE => ue_rec.ATA_CODE,
4489 X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
4490 X_CREATION_DATE => sysdate,
4491 X_CREATED_BY => fnd_global.user_id,
4492 X_LAST_UPDATE_DATE => sysdate,
4493 X_LAST_UPDATED_BY => fnd_global.user_id,
4494 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
4495
4496 IF(ue_rec.unit_effectivity_id = l_unit_effectivity_id)THEN
4497 l_new_parent_ue_id := l_new_unit_effectivity_id;
4498 END IF;
4499
4500 -- update l_ue_relns_tbl with the new unit effectivity id
4501 IF(l_ue_relns_tbl.count > 0)THEN
4502 FOR j IN l_ue_relns_tbl.FIRST..l_ue_relns_tbl.LAST LOOP
4503 IF(l_ue_relns_tbl(j).ue_id = ue_rec.unit_effectivity_id)THEN
4504 l_ue_relns_tbl(j).ue_id := l_new_unit_effectivity_id;
4505 END IF;
4506 IF(l_ue_relns_tbl(j).related_ue_id = ue_rec.unit_effectivity_id)THEN
4507 l_ue_relns_tbl(j).related_ue_id := l_new_unit_effectivity_id;
4508 END IF;
4509 END LOOP;
4510 END IF;
4511 END IF;--
4512
4513 IF(ue_rec.status_code IS NULL OR ue_rec.status_code IN ('INIT-DUE','DEFERRED','TERMINATED','CANCELLED'))THEN
4514 l_update_status := p_new_status;
4515 ELSE
4516 l_update_status := ue_rec.status_code;
4517 END IF;
4518 -- update status here
4519 AHL_UNIT_EFFECTIVITIES_PKG.update_row(
4520 x_unit_effectivity_id => ue_rec.UNIT_EFFECTIVITY_ID,
4521 x_csi_item_instance_id => ue_rec.CSI_ITEM_INSTANCE_ID,
4522 x_mr_interval_id => ue_rec.MR_INTERVAL_ID,
4523 x_mr_effectivity_id => ue_rec.MR_EFFECTIVITY_ID,
4524 x_mr_header_id => ue_rec.MR_HEADER_ID,
4525 x_status_code => l_update_status,
4526 x_due_date => ue_rec.DUE_DATE,
4527 x_due_counter_value => ue_rec.DUE_COUNTER_VALUE,
4528 x_forecast_sequence => ue_rec.FORECAST_SEQUENCE,
4529 x_repetitive_mr_flag => ue_rec.REPETITIVE_MR_FLAG,
4530 x_tolerance_flag => ue_rec.TOLERANCE_FLAG,
4531 x_remarks => ue_rec.REMARKS,
4532 x_message_code => ue_rec.MESSAGE_CODE,
4533 x_preceding_ue_id => ue_rec.PRECEDING_UE_ID,
4534 x_date_run => ue_rec.DATE_RUN,
4535 x_set_due_date => ue_rec.set_due_date,
4536 x_accomplished_date => ue_rec.accomplished_date,
4537 x_service_line_id => ue_rec.service_line_id,
4538 x_program_mr_header_id => ue_rec.program_mr_header_id,
4539 x_cancel_reason_code => ue_rec.cancel_reason_code,
4540 x_earliest_due_date => ue_rec.earliest_due_date,
4541 x_latest_due_date => ue_rec.latest_due_date,
4542 x_defer_from_ue_id => ue_rec.defer_from_ue_id,
4543 x_qa_collection_id => ue_rec.qa_collection_id,
4544 x_cs_incident_id => ue_rec.cs_incident_id,
4545 x_orig_deferral_ue_id => ue_rec.orig_deferral_ue_id,
4546 X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
4547 X_COUNTER_ID => ue_rec.counter_id,
4548 X_OBJECT_TYPE => ue_rec.object_type,
4549 X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
4550 X_LOG_SERIES_CODE => ue_rec.log_series_code,
4551 X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
4552 X_FLIGHT_NUMBER => ue_rec.flight_number,
4553 X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
4554 X_POSITION_PATH_ID => ue_rec.position_path_id,
4555 X_ATA_CODE => ue_rec.ATA_CODE,
4556 X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
4557 x_attribute_category => ue_rec.ATTRIBUTE_CATEGORY,
4558 x_attribute1 => ue_rec.ATTRIBUTE1,
4559 x_attribute2 => ue_rec.ATTRIBUTE2,
4560 x_attribute3 => ue_rec.ATTRIBUTE3,
4561 x_attribute4 => ue_rec.ATTRIBUTE4,
4562 x_attribute5 => ue_rec.ATTRIBUTE5,
4563 x_attribute6 => ue_rec.ATTRIBUTE6,
4564 x_attribute7 => ue_rec.ATTRIBUTE7,
4565 x_attribute8 => ue_rec.ATTRIBUTE8,
4566 x_attribute9 => ue_rec.ATTRIBUTE9,
4567 x_attribute10 => ue_rec.ATTRIBUTE10,
4568 x_attribute11 => ue_rec.ATTRIBUTE11,
4569 x_attribute12 => ue_rec.ATTRIBUTE12,
4570 x_attribute13 => ue_rec.ATTRIBUTE13,
4571 x_attribute14 => ue_rec.ATTRIBUTE14,
4572 x_attribute15 => ue_rec.ATTRIBUTE15,
4573 x_object_version_number => ue_rec.OBJECT_VERSION_NUMBER + 1,
4574 x_last_update_date => sysdate,
4575 x_last_updated_by => fnd_global.user_id,
4576 x_last_update_login => fnd_global.login_id
4577 );
4578
4579 /* Insert SR update status call here after making sure that object type is 'SR' for p_unit_effectivity_id
4580 * This call should be made only when p_new_status = 'DEFERRED' as SR can not be terminated
4581 * IF SR need to be updated even when deferral has been rejected then make the same call in ELSIF
4582 * statement below
4583 */
4584 -- TAMAL -- Begin changes for ER #3356804
4585 -- TAMAL -- Get UE and SR details
4586 OPEN get_ue_sr_details (ue_rec.UNIT_EFFECTIVITY_ID);
4587 FETCH get_ue_sr_details INTO l_ue_obj_type, l_cs_incident_id, l_cs_incident_number, l_cs_incident_ovn;
4588 CLOSE get_ue_sr_details;
4589
4590 -- TAMAL -- Checking for SR type
4591 -- TAMAL -- Checking for DEFERRED status
4592 -- Add Cancelled status to support SR cancellation for nonserialized items.
4593 IF (l_ue_obj_type IS NOT NULL AND l_ue_obj_type = 'SR' AND p_new_status IS NOT NULL
4594 AND p_new_status IN ('DEFERRED','CANCELLED'))
4595 THEN
4596 IF NOT (l_cs_incident_id IS NOT NULL AND l_cs_incident_id > 0)
4597 THEN
4598 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_UMP_INVALID_INCIDENT_ID');
4599 FND_MESSAGE.SET_TOKEN('INCIDENT_ID', l_cs_incident_id);
4600 FND_MSG_PUB.ADD;
4601 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
4602 fnd_log.string
4603 (
4604 fnd_log.level_unexpected,
4605 'ahl.plsql.AHL_PRD_DF_PVT.process_unit_maint_plan',
4606 'Wrong SR incident id: ' || l_cs_incident_id
4607 );
4608 END IF;
4609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4610 RETURN;
4611 END IF;
4612
4613 -- Support for cancelled SRs.
4614 IF (p_new_status = 'CANCELLED') THEN
4615 l_status_id := FND_PROFILE.VALUE('AHL_PRD_SR_CANCELLED_STATUS');
4616 IF (l_status_id IS NULL) THEN
4617 l_status_id := 2; -- closed.
4618 END IF;
4619 ELSE -- Deferred
4620 l_status_id := 1; -- open.
4621 END IF;
4622
4623 -- yazhou 29-Jun-2006 starts
4624 -- bug#5359943
4625 -- Pass p_status_id as 1 (OPEN)
4626
4627 -- Call SR Update_Status API
4628 CS_ServiceRequest_PUB.Update_Status
4629 (
4630 p_api_version => 2.0,
4631 p_init_msg_list => FND_API.G_FALSE,
4632 p_commit => FND_API.G_FALSE,
4633 p_resp_appl_id => NULL,
4634 p_resp_id => NULL,
4635 p_user_id => NULL,
4636 p_login_id => NULL,
4637 --p_status_id => 1, --OPEN
4638 p_status_id => l_status_id,
4639 p_closed_date => NULL,
4640 p_audit_comments => NULL,
4641 p_called_by_workflow => FND_API.G_FALSE,
4642 p_workflow_process_id => NULL,
4643 p_comments => NULL,
4644 p_public_comment_flag => FND_API.G_FALSE,
4645 p_validate_sr_closure => 'N',
4646 p_auto_close_child_entities => 'N',
4647 p_request_id => NULL,
4648 p_request_number => l_cs_incident_number,
4649 x_return_status => l_return_status,
4650 x_msg_count => l_msg_count,
4651 x_msg_data => l_msg_data,
4652 p_object_version_number => l_cs_incident_ovn,
4653 -- p_status => 'OPEN',
4654 x_interaction_id => l_interaction_id
4655 );
4656 -- yazhou 29-Jun-2006 ends
4657
4658 -- Abort if any error in calling the SR Update_Status API...
4659 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
4660 THEN
4661 x_return_status := l_return_status;
4662 RETURN;
4663 END IF;
4664 END IF;
4665 -- TAMAL -- End changes for ER #3356804
4666 END LOOP;
4667 -- create tree here
4668 IF (l_ue_relns_tbl.count > 0 AND p_new_status = 'DEFERRED')THEN
4669 FOR k IN l_ue_relns_tbl.FIRST..l_ue_relns_tbl.LAST LOOP
4670 -- Insert into ahl_ue_relationships.
4671 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
4672 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
4673 X_UE_ID => l_ue_relns_tbl(k).ue_id,
4674 X_RELATED_UE_ID => l_ue_relns_tbl(k).related_ue_id,
4675 X_RELATIONSHIP_CODE => 'PARENT',
4676 X_ORIGINATOR_UE_ID => l_new_parent_ue_id,
4677 X_ATTRIBUTE_CATEGORY => null,
4678 X_ATTRIBUTE1 => null,
4679 X_ATTRIBUTE2 => null,
4680 X_ATTRIBUTE3 => null,
4681 X_ATTRIBUTE4 => null,
4682 X_ATTRIBUTE5 => null,
4683 X_ATTRIBUTE6 => null,
4684 X_ATTRIBUTE7 => null,
4685 X_ATTRIBUTE8 => null,
4686 X_ATTRIBUTE9 => null,
4687 X_ATTRIBUTE10 => null,
4688 X_ATTRIBUTE11 => null,
4689 X_ATTRIBUTE12 => null,
4690 X_ATTRIBUTE13 => null,
4691 X_ATTRIBUTE14 => null,
4692 X_ATTRIBUTE15 => null,
4693 X_OBJECT_VERSION_NUMBER => 1,
4694 X_LAST_UPDATE_DATE => sysdate,
4695 X_LAST_UPDATED_BY => fnd_global.user_id,
4696 X_CREATION_DATE => sysdate,
4697 X_CREATED_BY => fnd_global.user_id,
4698 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
4699 END LOOP;
4700 END IF;
4701 /* Insert SR update status call here after making sure that object type is 'SR' for p_unit_effectivity_id
4702 * This call should be made only when p_new_status = 'DEFERRED' as SR can not be terminated
4703 * IF SR need to be updated even when deferral has been rejected then make the same call in ELSIF
4704 * statement below
4705 */
4706 ELSIF(p_approval_result_code IN (G_DEFERRAL_INITIATED,G_DEFERRAL_REJECTED))THEN
4707 IF(p_approval_result_code = G_DEFERRAL_INITIATED)THEN
4708 l_orig_deferral_ue_id := l_unit_effectivity_id;
4709 ELSE
4710 l_orig_deferral_ue_id := NULL;
4711 END IF;
4712
4713 FOR ue_rec IN unit_effectivity_csr(l_unit_effectivity_id) LOOP
4714 --update applicable unit deferral id for all children
4715 -- and removing it if deferral got rejected.
4716 IF(ue_rec.UNIT_EFFECTIVITY_ID <> l_unit_effectivity_id)THEN
4717 -- update status here
4718 AHL_UNIT_EFFECTIVITIES_PKG.update_row(
4719 x_unit_effectivity_id => ue_rec.UNIT_EFFECTIVITY_ID,
4720 x_csi_item_instance_id => ue_rec.CSI_ITEM_INSTANCE_ID,
4721 x_mr_interval_id => ue_rec.MR_INTERVAL_ID,
4722 x_mr_effectivity_id => ue_rec.MR_EFFECTIVITY_ID,
4723 x_mr_header_id => ue_rec.MR_HEADER_ID,
4724 x_status_code => ue_rec.status_code,
4725 x_due_date => ue_rec.DUE_DATE,
4726 x_due_counter_value => ue_rec.DUE_COUNTER_VALUE,
4727 x_forecast_sequence => ue_rec.FORECAST_SEQUENCE,
4728 x_repetitive_mr_flag => ue_rec.REPETITIVE_MR_FLAG,
4729 x_tolerance_flag => ue_rec.TOLERANCE_FLAG,
4730 x_remarks => ue_rec.REMARKS,
4731 x_message_code => ue_rec.MESSAGE_CODE,
4732 x_preceding_ue_id => ue_rec.PRECEDING_UE_ID,
4733 x_date_run => ue_rec.DATE_RUN,
4734 x_set_due_date => ue_rec.set_due_date,
4735 x_accomplished_date => ue_rec.accomplished_date,
4736 x_service_line_id => ue_rec.service_line_id,
4737 x_program_mr_header_id => ue_rec.program_mr_header_id,
4738 x_cancel_reason_code => ue_rec.cancel_reason_code,
4739 x_earliest_due_date => ue_rec.earliest_due_date,
4740 x_latest_due_date => ue_rec.latest_due_date,
4741 x_defer_from_ue_id => ue_rec.defer_from_ue_id,
4742 x_qa_collection_id => ue_rec.qa_collection_id,
4743 x_cs_incident_id => ue_rec.cs_incident_id,
4744 x_orig_deferral_ue_id => l_orig_deferral_ue_id,
4745 X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
4746 X_OBJECT_TYPE => ue_rec.object_type,
4747 X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
4748 X_COUNTER_ID => ue_rec.counter_id,
4749 X_LOG_SERIES_CODE => ue_rec.log_series_code,
4750 X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
4751 X_FLIGHT_NUMBER => ue_rec.flight_number,
4752 X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
4753 X_POSITION_PATH_ID => ue_rec.position_path_id,
4754 X_ATA_CODE => ue_rec.ATA_CODE,
4755 X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
4756 x_attribute_category => ue_rec.ATTRIBUTE_CATEGORY,
4757 x_attribute1 => ue_rec.ATTRIBUTE1,
4758 x_attribute2 => ue_rec.ATTRIBUTE2,
4759 x_attribute3 => ue_rec.ATTRIBUTE3,
4760 x_attribute4 => ue_rec.ATTRIBUTE4,
4761 x_attribute5 => ue_rec.ATTRIBUTE5,
4762 x_attribute6 => ue_rec.ATTRIBUTE6,
4763 x_attribute7 => ue_rec.ATTRIBUTE7,
4764 x_attribute8 => ue_rec.ATTRIBUTE8,
4765 x_attribute9 => ue_rec.ATTRIBUTE9,
4766 x_attribute10 => ue_rec.ATTRIBUTE10,
4767 x_attribute11 => ue_rec.ATTRIBUTE11,
4768 x_attribute12 => ue_rec.ATTRIBUTE12,
4769 x_attribute13 => ue_rec.ATTRIBUTE13,
4770 x_attribute14 => ue_rec.ATTRIBUTE14,
4771 x_attribute15 => ue_rec.ATTRIBUTE15,
4772 x_object_version_number => ue_rec.OBJECT_VERSION_NUMBER + 1,
4773 x_last_update_date => sysdate,
4774 x_last_updated_by => fnd_global.user_id,
4775 x_last_update_login => fnd_global.login_id);
4776 END IF;
4777 END LOOP;
4778 END IF;
4779
4780 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4781 fnd_log.string
4782 (
4783 fnd_log.level_procedure,
4784 'ahl.plsql.AHL_PRD_DF_PVT.process_unit_maint_plan.end',
4785 'At the end of PLSQL procedure'
4786 );
4787 END IF;
4788
4789 END process_unit_maint_plan;
4790
4791 -------------------------------------------------------------------------
4792 -------------------------------------------------------------------------
4793 PROCEDURE process_prior_ump_deferrals(
4794 p_unit_effectivity_id IN NUMBER) IS
4795
4796 CURSOR prior_child_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
4797 SELECT UE.unit_effectivity_id FROM ahl_unit_effectivities_b UE
4798 WHERE defer_from_ue_id IS NOT NULL
4799 --AND NOT EXISTS (Select 'x' from ahl_visit_tasks_b VST where VST.unit_effectivity_id = UE.unit_effectivity_id)
4800 AND defer_from_ue_id IN (
4801 SELECT related_ue_id
4802 FROM AHL_UE_RELATIONSHIPS
4803 WHERE relationship_code = 'PARENT'
4804 START WITH ue_id = p_unit_effectivity_id
4805 CONNECT BY ue_id = PRIOR related_ue_id
4806 );
4807
4808 CURSOR assigned_to_visit_csr(p_unit_effectivity_id IN NUMBER) IS
4809 SELECT 'x' FROM ahl_visit_tasks_b VST
4810 WHERE VST.unit_effectivity_id = p_unit_effectivity_id;
4811
4812 l_exists VARCHAR2(1);
4813
4814 CURSOR ue_rel_id_csr(p_unit_effectivity_id IN NUMBER) IS
4815 SELECT ue_relationship_id FROM AHL_UE_RELATIONSHIPS
4816 WHERE relationship_code = 'PARENT'
4817 AND related_ue_id = p_unit_effectivity_id;
4818
4819 l_ue_relationship_id NUMBER;
4820
4821 CURSOR unit_effectivity_csr (p_unit_effectivity_id IN NUMBER) IS
4822 SELECT
4823 UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, CSI_ITEM_INSTANCE_ID, MR_INTERVAL_ID,
4824 MR_EFFECTIVITY_ID, MR_HEADER_ID, STATUS_CODE, SET_DUE_DATE, ACCOMPLISHED_DATE,
4825 DUE_DATE, DUE_COUNTER_VALUE, FORECAST_SEQUENCE, REPETITIVE_MR_FLAG,
4826 TOLERANCE_FLAG, DATE_RUN, PRECEDING_UE_ID, MESSAGE_CODE, REMARKS,
4827 SERVICE_LINE_ID, PROGRAM_MR_HEADER_ID, CANCEL_REASON_CODE, EARLIEST_DUE_DATE,
4828 LATEST_DUE_DATE, DEFER_FROM_UE_ID, CS_INCIDENT_ID, QA_COLLECTION_ID,
4829 ORIG_DEFERRAL_UE_ID, COUNTER_ID,OBJECT_TYPE,MANUALLY_PLANNED_FLAG,
4830 LOG_SERIES_CODE,LOG_SERIES_NUMBER,FLIGHT_NUMBER, MEL_CDL_TYPE_CODE,
4831 POSITION_PATH_ID, ATA_CODE, UNIT_CONFIG_HEADER_ID,
4832 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
4833 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,ATTRIBUTE9,
4834 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,ATTRIBUTE15
4835 FROM AHL_UNIT_EFFECTIVITIES_VL UE
4836 WHERE unit_effectivity_id = p_unit_effectivity_id;
4837
4838 CURSOR redundant_deferral_ue_csr(p_unit_effectivity_id IN NUMBER) IS
4839 SELECT UD.unit_deferral_id FROM ahl_unit_deferrals_b UD
4840 WHERE UD.unit_deferral_type = 'DEFERRAL'
4841 AND UD.unit_effectivity_id IN (
4842 SELECT related_ue_id
4843 FROM AHL_UE_RELATIONSHIPS
4844 WHERE relationship_code = 'PARENT'
4845 START WITH ue_id = p_unit_effectivity_id
4846 CONNECT BY ue_id = PRIOR related_ue_id
4847 )
4848 AND NOT EXISTS (
4849 SELECT 'x' FROM ahl_unit_effectivities_b
4850 WHERE defer_from_ue_id = UD.unit_effectivity_id
4851 );
4852
4853 CURSOR redundant_threshold_csr(p_unit_deferral_id IN NUMBER) IS
4854 SELECT unit_threshold_id FROM ahl_unit_thresholds
4855 WHERE unit_deferral_id = p_unit_deferral_id;
4856
4857
4858 BEGIN
4859 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4860 fnd_log.string
4861 (
4862 fnd_log.level_procedure,
4863 'ahl.plsql.AHL_PRD_DF_PVT.process_prior_ump_deferrals.begin',
4864 'At the start of PLSQL procedure'
4865 );
4866 END IF;
4867
4868 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4869 fnd_log.string
4870 (
4871 fnd_log.level_statement,
4872 'ahl.plsql.AHL_PRD_DF_PVT.process_prior_ump_deferrals',
4873 'deleting redundant ue, relationships and marking as exception if assigned to visit'
4874 );
4875 END IF;
4876
4877 FOR ue_id_rec IN prior_child_defer_to_ue_csr(p_unit_effectivity_id) LOOP
4878 -- determine whether assigned to a visit
4879 OPEN assigned_to_visit_csr(ue_id_rec.unit_effectivity_id);
4880 FETCH assigned_to_visit_csr INTO l_exists;
4881 IF(assigned_to_visit_csr%NOTFOUND)THEN
4882 -- if not assigned to a visit delete the ue rec and relationships
4883 AHL_UNIT_EFFECTIVITIES_PKG.delete_row(ue_id_rec.unit_effectivity_id);
4884 OPEN ue_rel_id_csr(ue_id_rec.unit_effectivity_id);
4885 FETCH ue_rel_id_csr INTO l_ue_relationship_id;
4886 IF(ue_rel_id_csr%FOUND) THEN
4887 AHL_UE_RELATIONSHIPS_PKG.delete_row(l_ue_relationship_id);
4888 END IF;
4889 CLOSE ue_rel_id_csr;
4890 ELSE -- assigned to visit -- mark as exception
4891 FOR ue_rec IN unit_effectivity_csr(ue_id_rec.unit_effectivity_id) LOOP
4892 -- update status
4893 AHL_UNIT_EFFECTIVITIES_PKG.update_row(
4894 x_unit_effectivity_id => ue_rec.UNIT_EFFECTIVITY_ID,
4895 x_csi_item_instance_id => ue_rec.CSI_ITEM_INSTANCE_ID,
4896 x_mr_interval_id => ue_rec.MR_INTERVAL_ID,
4897 x_mr_effectivity_id => ue_rec.MR_EFFECTIVITY_ID,
4898 x_mr_header_id => ue_rec.MR_HEADER_ID,
4899 x_status_code => 'EXCEPTION',
4900 x_due_date => ue_rec.DUE_DATE,
4901 x_due_counter_value => ue_rec.DUE_COUNTER_VALUE,
4902 x_forecast_sequence => ue_rec.FORECAST_SEQUENCE,
4903 x_repetitive_mr_flag => ue_rec.REPETITIVE_MR_FLAG,
4904 x_tolerance_flag => ue_rec.TOLERANCE_FLAG,
4905 x_remarks => ue_rec.REMARKS,
4906 x_message_code => ue_rec.MESSAGE_CODE,
4907 x_preceding_ue_id => ue_rec.PRECEDING_UE_ID,
4908 x_date_run => ue_rec.DATE_RUN,
4909 x_set_due_date => ue_rec.set_due_date,
4910 x_accomplished_date => ue_rec.accomplished_date,
4911 x_service_line_id => ue_rec.service_line_id,
4912 x_program_mr_header_id => ue_rec.program_mr_header_id,
4913 x_cancel_reason_code => ue_rec.cancel_reason_code,
4914 x_earliest_due_date => ue_rec.earliest_due_date,
4915 x_latest_due_date => ue_rec.latest_due_date,
4916 x_defer_from_ue_id => ue_rec.defer_from_ue_id,
4917 x_qa_collection_id => ue_rec.qa_collection_id,
4918 x_cs_incident_id => ue_rec.cs_incident_id,
4919 x_orig_deferral_ue_id => ue_rec.orig_deferral_ue_id,
4920 X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
4921 X_OBJECT_TYPE => ue_rec.object_type,
4922 X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
4923 X_COUNTER_ID => ue_rec.counter_id,
4924 X_LOG_SERIES_CODE => ue_rec.log_series_code,
4925 X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
4926 X_FLIGHT_NUMBER => ue_rec.flight_number,
4927 X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
4928 X_POSITION_PATH_ID => ue_rec.position_path_id,
4929 X_ATA_CODE => ue_rec.ATA_CODE,
4930 X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
4931 x_attribute_category => ue_rec.ATTRIBUTE_CATEGORY,
4932 x_attribute1 => ue_rec.ATTRIBUTE1,
4933 x_attribute2 => ue_rec.ATTRIBUTE2,
4934 x_attribute3 => ue_rec.ATTRIBUTE3,
4935 x_attribute4 => ue_rec.ATTRIBUTE4,
4936 x_attribute5 => ue_rec.ATTRIBUTE5,
4937 x_attribute6 => ue_rec.ATTRIBUTE6,
4938 x_attribute7 => ue_rec.ATTRIBUTE7,
4939 x_attribute8 => ue_rec.ATTRIBUTE8,
4940 x_attribute9 => ue_rec.ATTRIBUTE9,
4941 x_attribute10 => ue_rec.ATTRIBUTE10,
4942 x_attribute11 => ue_rec.ATTRIBUTE11,
4943 x_attribute12 => ue_rec.ATTRIBUTE12,
4944 x_attribute13 => ue_rec.ATTRIBUTE13,
4945 x_attribute14 => ue_rec.ATTRIBUTE14,
4946 x_attribute15 => ue_rec.ATTRIBUTE15,
4947 x_object_version_number => ue_rec.OBJECT_VERSION_NUMBER + 1,
4948 x_last_update_date => sysdate,
4949 x_last_updated_by => fnd_global.user_id,
4950 x_last_update_login => fnd_global.login_id);
4951 END LOOP;
4952 END IF;
4953 CLOSE assigned_to_visit_csr;
4954 END LOOP;
4955
4956 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4957 fnd_log.string
4958 (
4959 fnd_log.level_statement,
4960 'ahl.plsql.AHL_PRD_DF_PVT.process_prior_ump_deferrals',
4961 'deleting un-necessary deferral records'
4962 );
4963 END IF;
4964
4965 FOR redundant_deferral_rec IN redundant_deferral_ue_csr(p_unit_effectivity_id) LOOP
4966 FOR redundant_threshold_rec IN redundant_threshold_csr(redundant_deferral_rec.unit_deferral_id) LOOP
4967 AHL_UNIT_THRESHOLDS_PKG.delete_row(redundant_threshold_rec.unit_threshold_id);
4968 END LOOP;
4969 AHL_UNIT_DEFERRALS_PKG.delete_row(redundant_deferral_rec.unit_deferral_id);
4970 END LOOP;
4971
4972
4973 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4974 fnd_log.string
4975 (
4976 fnd_log.level_procedure,
4977 'ahl.plsql.AHL_PRD_DF_PVT.process_prior_ump_deferrals.end',
4978 'At the end of PLSQL procedure'
4979 );
4980 END IF;
4981 END process_prior_ump_deferrals;
4982
4983
4984
4985 -------------------------------------------------------------------------
4986 -- Procedure to get deferral details attached to any unit effectivity --
4987 --------------------------------------------------------------------------
4988 PROCEDURE get_deferral_details (
4989
4990 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4991 p_unit_effectivity_id IN NUMBER,
4992 x_df_header_info_rec OUT NOCOPY AHL_PRD_DF_PVT.df_header_info_rec_type,
4993 x_df_schedules_tbl OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type,
4994 x_return_status OUT NOCOPY VARCHAR2,
4995 x_msg_count OUT NOCOPY NUMBER,
4996 x_msg_data OUT NOCOPY VARCHAR2) IS
4997
4998 l_api_name CONSTANT VARCHAR2(30) := 'get_deferral_details';
4999
5000 l_df_header_info_rec AHL_PRD_DF_PVT.df_header_info_rec_type;
5001 l_applicable_ue_id NUMBER;
5002
5003 -- to fecth context information
5004 CURSOR context_info_csr(p_unit_effectivity_id IN NUMBER) IS
5005 SELECT due_date, mr_header_id,title,description,repetitive_mr_flag,cs_incident_id,cs_incident_number,
5006 cs_incident_summary,manually_planned_flag
5007 FROM ahl_ue_deferral_details_v
5008 WHERE unit_effectivity_id = p_unit_effectivity_id
5009 AND APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) ;--this takes care of app_usage changes
5010
5011 -- mr status meaning has to be fecthed seperately because function returns only code
5012 CURSOR mr_status_meaning_csr(p_status_code IN VARCHAR2)IS
5013 SELECT meaning FROM FND_LOOKUP_VALUES_VL
5014 WHERE lookup_code = p_status_code
5015 AND lookup_type = 'AHL_PRD_MR_STATUS';
5016
5017 -- to fetch visit info
5018 CURSOR visit_info_csr(p_unit_effectivity_id IN NUMBER) IS
5019 SELECT VS.visit_id, VS.visit_number FROM ahl_visits_b VS,ahl_visit_tasks_b VST
5020 WHERE VST.visit_id = VS.visit_id
5021 AND VST.unit_effectivity_id = p_unit_effectivity_id;
5022
5023 -- to check whether MR or any of its children has resettable counters
5024 CURSOR reset_counter_csr(p_unit_effectivity_id IN NUMBER) IS
5025 /* In R12, modified to use csi_counters_vl instead of csi_cp_counters_v.
5026 SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5027 WHERE CP.customer_product_id = UE.csi_item_instance_id
5028 AND CP.counter_name = MRI.counter_name
5029 AND MRI.reset_value IS NOT NULL
5030 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5031 AND MRE.mr_header_id = UE.mr_header_id
5032 AND UE.unit_effectivity_id = p_unit_effectivity_id
5033 UNION
5034 SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5035 WHERE CP.customer_product_id = UE.csi_item_instance_id
5036 AND CP.counter_name = MRI.counter_name
5037 AND MRI.reset_value IS NOT NULL
5038 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5039 AND MRE.mr_header_id = UE.mr_header_id
5040 AND UE.unit_effectivity_id IN
5041 (
5042
5043 SELECT related_ue_id
5044 FROM AHL_UE_RELATIONSHIPS
5045 WHERE relationship_code = 'PARENT'
5046 START WITH ue_id = p_unit_effectivity_id
5047 CONNECT BY ue_id = PRIOR related_ue_id
5048
5049 );
5050 */
5051
5052 SELECT 'x'
5053 from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
5054 AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5055 WHERE CA.source_object_id = UE.csi_item_instance_id
5056 AND ca.source_object_code = 'CP'
5057 AND CP.counter_template_name = MRI.counter_name
5058 AND MRI.reset_value IS NOT NULL
5059 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5060 AND MRE.mr_header_id = UE.mr_header_id
5061 AND UE.unit_effectivity_id = p_unit_effectivity_id
5062 UNION
5063 SELECT 'x'
5064 from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
5065 AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5066 WHERE CA.source_object_id = UE.csi_item_instance_id
5067 AND ca.source_object_code = 'CP'
5068 AND CP.counter_template_name = MRI.counter_name
5069 AND MRI.reset_value IS NOT NULL
5070 AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5071 AND MRE.mr_header_id = UE.mr_header_id
5072 AND UE.unit_effectivity_id IN
5073 (
5074
5075 SELECT related_ue_id
5076 FROM AHL_UE_RELATIONSHIPS
5077 WHERE relationship_code = 'PARENT'
5078 START WITH ue_id = p_unit_effectivity_id
5079 CONNECT BY ue_id = PRIOR related_ue_id
5080
5081 );
5082
5083 l_exists VARCHAR2(1);
5084
5085 -- to fetch df_header_rec
5086 CURSOR df_header_info_csr(p_unit_effectivity_id IN NUMBER) IS
5087 SELECT unit_deferral_id, object_version_number, approval_status_code,FLV.meaning approval_status_meaning,defer_reason_code,skip_mr_flag,
5088 affect_due_calc_flag, set_due_date, deferral_effective_on,remarks,approver_notes, user_deferral_type, DTYP.meaning user_deferral_mean
5089 FROM ahl_unit_deferrals_vl,fnd_lookup_values_vl FLV, fnd_lookup_values_vl DTYP
5090 WHERE unit_deferral_type = 'DEFERRAL'
5091 AND unit_effectivity_id = p_unit_effectivity_id
5092 AND FLV.lookup_code = approval_status_code
5093 AND FLV.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
5094 AND DTYP.lookup_type(+) = 'AHL_PRD_DEFERRAL_TYPE'
5095 AND DTYP.lookup_code(+) = user_deferral_type ;
5096
5097 -- fetch deferral schedule rec
5098 CURSOR df_schedule_tbl_csr(p_unit_deferral_id IN NUMBER) IS
5099 SELECT UT.unit_threshold_id,UT.object_version_number,UT.unit_deferral_id, UT.counter_id,
5100 CO.name, UT.counter_value, UT.ctr_value_type_code,MU.unit_of_measure
5101 FROM MTL_UNITS_OF_MEASURE_VL MU, CS_COUNTERS CO,ahl_unit_thresholds UT
5102 WHERE MU.uom_code = CO.uom_code
5103 AND CO.counter_id = UT.counter_id
5104 AND UT.unit_deferral_id = p_unit_deferral_id
5105 ORDER BY CO.name;
5106
5107
5108 i NUMBER := 0;
5109 l_df_schedules_tbl AHL_PRD_DF_PVT.df_schedules_tbl_type;
5110
5111 BEGIN
5112 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
5113 fnd_log.string
5114 (
5115 fnd_log.level_procedure,
5116 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details.begin',
5117 'At the start of PLSQL procedure'
5118 );
5119 END IF;
5120 -- Initialize message list if p_init_msg_list is set to TRUE
5121 IF FND_API.To_Boolean( p_init_msg_list) THEN
5122 FND_MSG_PUB.Initialize;
5123 END IF;
5124 -- Initialize API return status to success
5125 x_return_status := FND_API.G_RET_STS_SUCCESS;
5126
5127 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
5128 fnd_log.string
5129 (
5130 fnd_log.level_statement,
5131 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details',
5132 'Got request for deferral record of Unit effectivity ID : ' || p_unit_effectivity_id
5133 );
5134 END IF;
5135
5136 IF(p_unit_effectivity_id IS NULL OR p_unit_effectivity_id = FND_API.G_MISS_NUM)THEN
5137 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HREC_KMISS');
5138 FND_MSG_PUB.ADD;
5139 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
5140 fnd_log.string
5141 (
5142 fnd_log.level_unexpected,
5143 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details',
5144 'Invalid request, Unit Effectivity IS NULL'
5145 );
5146 END IF;
5147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5148 END IF;
5149 --get applicable ue id and find out whether deferral record should be shown.
5150 l_applicable_ue_id := get_applicable_ue(p_unit_effectivity_id);
5151
5152 -- throw errors if any
5153 IF(FND_MSG_PUB.count_msg > 0)THEN
5154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5155 END IF;
5156
5157 IF(l_applicable_ue_id IS NULL)THEN
5158 l_applicable_ue_id := p_unit_effectivity_id;
5159 /*FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_MRSR_STATUS');
5160 FND_MSG_PUB.ADD;
5161 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
5162 fnd_log.string
5163 (
5164 fnd_log.level_error,
5165 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details',
5166 'Status of MR or SR is not valid for deferral'
5167 );
5168 END IF;*/
5169 END IF;
5170 l_df_header_info_rec.unit_effectivity_id := l_applicable_ue_id;
5171 --dbms_output.put_line('l_applicable_ue_id : ' || l_applicable_ue_id );
5172 -- fill in context information
5173 OPEN context_info_csr(l_applicable_ue_id);
5174 FETCH context_info_csr INTO l_df_header_info_rec.due_date,l_df_header_info_rec.mr_header_id,
5175 l_df_header_info_rec.mr_title,l_df_header_info_rec.mr_description,
5176 l_df_header_info_rec.mr_repetitive_flag,l_df_header_info_rec.incident_id,
5177 l_df_header_info_rec.incident_number,l_df_header_info_rec.summary,
5178 l_df_header_info_rec.manually_planned_flag;
5179
5180 IF(context_info_csr%NOTFOUND)THEN
5181 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HREC_UE_ID');
5182 FND_MSG_PUB.ADD;
5183 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
5184 fnd_log.string
5185 (
5186 fnd_log.level_unexpected,
5187 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details',
5188 'Unit Effectivity record not found'
5189 );
5190 END IF;
5191 CLOSE context_info_csr;
5192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5193 END IF;
5194 CLOSE context_info_csr;
5195
5196
5197 l_df_header_info_rec.ue_status_code := AHL_COMPLETIONS_PVT.get_mr_status(l_df_header_info_rec.unit_effectivity_id);
5198
5199 OPEN mr_status_meaning_csr(l_df_header_info_rec.ue_status_code);
5200 FETCH mr_status_meaning_csr INTO l_df_header_info_rec.ue_status_meaning;
5201 IF(mr_status_meaning_csr%NOTFOUND)THEN
5202 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_UE_MR');
5203 FND_MSG_PUB.ADD;
5204 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
5205 fnd_log.string
5206 (
5207 fnd_log.level_unexpected,
5208 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details',
5209 'Invalid unit effectivity record, mr status meaning not found'
5210 );
5211 END IF;
5212 CLOSE mr_status_meaning_csr;
5213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5214 END IF;
5215 CLOSE mr_status_meaning_csr;
5216
5217 -- fill in visit information
5218 OPEN visit_info_csr(l_applicable_ue_id);
5219 FETCH visit_info_csr INTO l_df_header_info_rec.visit_id,l_df_header_info_rec.visit_number;
5220 /* R12: UMP Deferral.
5221 IF(visit_info_csr%NOTFOUND)THEN
5222 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_UE_VISIT');
5223 FND_MSG_PUB.ADD;
5224 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
5225 fnd_log.string
5226 (
5227 fnd_log.level_unexpected,
5228 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details',
5229 'Visit Information not found'
5230 );
5231 END IF;
5232 CLOSE visit_info_csr;
5233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5234 END IF;
5235 */
5236 CLOSE visit_info_csr;
5237
5238 -- filling in mr/sr type and whether mr or its children has restettable counters
5239 IF(l_df_header_info_rec.mr_header_id IS NOT NULL) THEN
5240 l_df_header_info_rec.deferral_type := G_DEFERRAL_TYPE_MR;
5241 l_df_header_info_rec.reset_counter_flag := G_YES_FLAG;
5242 IF(l_df_header_info_rec.mr_repetitive_flag = G_YES_FLAG)THEN
5243 OPEN reset_counter_csr(l_applicable_ue_id);
5244 FETCH reset_counter_csr INTO l_exists;
5245 IF(reset_counter_csr%NOTFOUND)THEN
5246 l_df_header_info_rec.reset_counter_flag := G_NO_FLAG;
5247 END IF;
5248 CLOSE reset_counter_csr;
5249 END IF;
5250 ELSIF l_df_header_info_rec.incident_id IS NOT NULL THEN
5251 l_df_header_info_rec.deferral_type := G_DEFERRAL_TYPE_SR;
5252 l_df_header_info_rec.mr_repetitive_flag := G_NO_FLAG;
5253 l_df_header_info_rec.reset_counter_flag := G_YES_FLAG;
5254 END IF;
5255
5256 -- throw errors if any
5257 IF(FND_MSG_PUB.count_msg > 0)THEN
5258 RAISE FND_API.G_EXC_ERROR;
5259 END IF;
5260 -- fetch deferral header record
5261 OPEN df_header_info_csr(l_applicable_ue_id);
5262 FETCH df_header_info_csr INTO l_df_header_info_rec.unit_deferral_id,
5263 l_df_header_info_rec.object_version_number,
5264 l_df_header_info_rec.approval_status_code,
5265 l_df_header_info_rec.approval_status_meaning,
5266 l_df_header_info_rec.defer_reason_code,
5267 l_df_header_info_rec.skip_mr_flag,
5268 l_df_header_info_rec.affect_due_calc_flag,
5269 l_df_header_info_rec.set_due_date,
5270 l_df_header_info_rec.deferral_effective_on,
5271 l_df_header_info_rec.remarks,
5272 l_df_header_info_rec.approver_notes,
5273 l_df_header_info_rec.user_deferral_type_code,
5274 l_df_header_info_rec.user_deferral_type_mean;
5275 IF(df_header_info_csr%NOTFOUND)THEN
5276 l_df_header_info_rec.skip_mr_flag := G_NO_FLAG;
5277 l_df_header_info_rec.affect_due_calc_flag := G_YES_FLAG;
5278 l_df_header_info_rec.approval_status_code := 'DRAFT';
5279 IF(l_df_header_info_rec.deferral_type = G_DEFERRAL_TYPE_MR AND
5280 l_df_header_info_rec.reset_counter_flag = G_NO_FLAG)THEN
5281 l_df_header_info_rec.affect_due_calc_flag := G_NO_FLAG;
5282 END IF;
5283 l_df_header_info_rec.deferral_effective_on := SYSDATE;
5284 ELSIF(l_df_header_info_rec.approval_status_code IN ('DEFERRAL_REJECTED'))THEN --,'DEFERRAL_PENDING','DEFERRED'))THEN
5285 l_df_header_info_rec.ue_status_code := l_df_header_info_rec.approval_status_code;
5286 l_df_header_info_rec.ue_status_meaning := l_df_header_info_rec.approval_status_meaning;
5287 END IF;
5288 CLOSE df_header_info_csr;
5289
5290 -- throw errors if any
5291 IF(FND_MSG_PUB.count_msg > 0)THEN
5292 RAISE FND_API.G_EXC_ERROR;
5293 END IF;
5294
5295 -- fetch deferral schedule rec
5296 IF(l_df_header_info_rec.unit_deferral_id IS NOT NULL)THEN
5297 OPEN df_schedule_tbl_csr(l_df_header_info_rec.unit_deferral_id);
5298 LOOP
5299 FETCH df_schedule_tbl_csr INTO
5300 l_df_schedules_tbl(i).unit_threshold_id,
5301 l_df_schedules_tbl(i).object_version_number,
5302 l_df_schedules_tbl(i).unit_deferral_id,
5303 l_df_schedules_tbl(i).counter_id,
5304 l_df_schedules_tbl(i).counter_name,
5305 l_df_schedules_tbl(i).counter_value,
5306 l_df_schedules_tbl(i).ctr_value_type_code,
5307 l_df_schedules_tbl(i).unit_of_measure;
5308 EXIT WHEN df_schedule_tbl_csr%NOTFOUND;
5309 i := i + 1;
5310 END LOOP;
5311 CLOSE df_schedule_tbl_csr;
5312 END IF;
5313
5314 -- throw errors if any
5315 IF(FND_MSG_PUB.count_msg > 0)THEN
5316 RAISE FND_API.G_EXC_ERROR;
5317 END IF;
5318
5319 x_df_header_info_rec := l_df_header_info_rec;
5320 x_df_schedules_tbl := l_df_schedules_tbl;
5321
5322
5323 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
5324 fnd_log.string
5325 (
5326 fnd_log.level_procedure,
5327 'ahl.plsql.AHL_PRD_DF_PVT.get_deferral_details.end',
5328 'At the end of PLSQL procedure'
5329 );
5330 END IF;
5331
5332 EXCEPTION
5333 WHEN FND_API.G_EXC_ERROR THEN
5334 x_df_header_info_rec := l_df_header_info_rec;
5335 x_df_schedules_tbl := l_df_schedules_tbl;
5336 x_return_status := FND_API.G_RET_STS_ERROR;
5337 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
5338 p_data => x_msg_data,
5339 p_encoded => fnd_api.g_false);
5340
5341
5342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5343 x_df_header_info_rec := l_df_header_info_rec;
5344 x_df_schedules_tbl := l_df_schedules_tbl;
5345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5346 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
5347 p_data => x_msg_data,
5348 p_encoded => fnd_api.g_false);
5349
5350
5351 WHEN OTHERS THEN
5352 x_df_header_info_rec := l_df_header_info_rec;
5353 x_df_schedules_tbl := l_df_schedules_tbl;
5354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5355 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5356 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
5357 p_procedure_name => l_api_name,
5358 p_error_text => SUBSTR(SQLERRM,1,500));
5359 END IF;
5360 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
5361 p_data => x_msg_data,
5362 p_encoded => fnd_api.g_false);
5363 END get_deferral_details;
5364
5365 FUNCTION get_applicable_ue(
5366 p_unit_effectivity_id NUMBER) RETURN NUMBER IS
5367
5368 -- to fecth context information
5369 CURSOR applicable_ue_csr(p_unit_effectivity_id IN NUMBER) IS
5370 SELECT orig_deferral_ue_id,ue_status_code,def_status_code
5371 FROM ahl_ue_deferral_details_v
5372 WHERE unit_effectivity_id = p_unit_effectivity_id;
5373
5374 l_orig_deferral_ue_id NUMBER;
5375 l_ue_status_code VARCHAR2(30);
5376 l_def_status_code VARCHAR2(30);
5377 l_applicable_ue_id NUMBER;
5378 BEGIN
5379 OPEN applicable_ue_csr(p_unit_effectivity_id);
5380 FETCH applicable_ue_csr INTO l_orig_deferral_ue_id,
5381 l_ue_status_code,
5382 l_def_status_code;
5383 IF(applicable_ue_csr%NOTFOUND)THEN
5384 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HREC_UE_ID');
5385 FND_MSG_PUB.ADD;
5386 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
5387 fnd_log.string
5388 (
5389 fnd_log.level_unexpected,
5390 'ahl.plsql.AHL_PRD_DF_PVT.get_applicable_ue',
5391 'Unit Effectivity record not found'
5392 );
5393 END IF;
5394 ELSE
5395 l_applicable_ue_id := l_orig_deferral_ue_id;
5396 IF(l_orig_deferral_ue_id IS NULL)THEN
5397 IF((NVL(l_def_status_code,'x') IN ('DEFERRAL_PENDING','DEFERRED','TERMINATED','CANCELLED')) OR
5398 (NVL(l_ue_status_code,'x') IN ('DEFERRED','TERMINATED','CANCELLED')) OR
5399 l_ue_status_code IS NULL )THEN
5400 l_applicable_ue_id := p_unit_effectivity_id;
5401 END IF;
5402 END IF;
5403 END IF;
5404 CLOSE applicable_ue_csr;
5405 RETURN l_applicable_ue_id;
5406
5407 END get_applicable_ue;
5408
5409 --------------------------------------------------------------------------------
5410 FUNCTION process_deferred_exceptions(p_unit_effectivity_id IN NUMBER) RETURN BOOLEAN IS
5411
5412 CURSOR unit_deferral_csr(p_unit_effectivity_id IN NUMBER) IS
5413 SELECT UD.unit_deferral_id FROM ahl_unit_deferrals_b UD
5414 WHERE UD.unit_deferral_type = 'DEFERRAL'
5415 AND UD.unit_effectivity_id = p_unit_effectivity_id;
5416
5417 l_unit_deferral_id NUMBER;
5418
5419 CURSOR redundant_threshold_csr(p_unit_deferral_id IN NUMBER) IS
5420 SELECT unit_threshold_id FROM ahl_unit_thresholds
5421 WHERE unit_deferral_id = p_unit_deferral_id;
5422
5423 BEGIN
5424 OPEN unit_deferral_csr(p_unit_effectivity_id);
5425 FETCH unit_deferral_csr INTO l_unit_deferral_id;
5426 IF(unit_deferral_csr%NOTFOUND)THEN
5427 RETURN FALSE;
5428 ELSE
5429 FOR redundant_threshold_rec IN redundant_threshold_csr(l_unit_deferral_id) LOOP
5430 AHL_UNIT_THRESHOLDS_PKG.delete_row(redundant_threshold_rec.unit_threshold_id);
5431 END LOOP;
5432 AHL_UNIT_DEFERRALS_PKG.delete_row(l_unit_deferral_id);
5433 END IF;
5434 RETURN TRUE;
5435 END process_deferred_exceptions;
5436
5437
5438 FUNCTION Is_UMP_Deferral(p_unit_deferral_id IN NUMBER) RETURN BOOLEAN
5439 IS
5440 CURSOR wo_exists_csr(p_unit_deferral_id IN NUMBER) IS
5441 /* -- fix for bug# 6849943 (FP for Bug # 6815689).
5442 SELECT 'x'
5443 FROM ahl_workorder_tasks_v wo, ahl_unit_deferrals_b udf
5444 WHERE wo.unit_effectivity_id = udf.unit_effectivity_id
5445 AND udf.unit_deferral_id = p_unit_deferral_id;
5446 */
5447
5448 SELECT 'x'
5449 FROM ahl_workorders wo, ahl_unit_deferrals_b udf,
5450 ahl_visit_tasks_b vts, ahl_visits_b vst,
5451 (SELECT ORGANIZATION_ID
5452 FROM INV_ORGANIZATION_INFO_V
5453 WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
5454 WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
5455 AND VST.VISIT_ID=VTS.VISIT_ID
5456 AND VST.ORGANIZATION_ID=ORG.ORGANIZATION_ID
5457 AND vts.unit_effectivity_id = udf.unit_effectivity_id
5458 AND udf.unit_deferral_id = p_unit_deferral_id
5459 AND rownum < 2;
5460
5461 l_exists VARCHAR2(1);
5462 l_found BOOLEAN;
5463
5464 BEGIN
5465 OPEN wo_exists_csr(p_unit_deferral_id);
5466 FETCH wo_exists_csr INTO l_exists;
5467 IF (wo_exists_csr%FOUND) THEN
5468 l_found := FALSE;
5469 ELSE
5470 l_found := TRUE;
5471 END IF;
5472 CLOSE wo_exists_csr;
5473
5474 RETURN l_found;
5475
5476 END Is_UMP_Deferral;
5477
5478
5479 END AHL_PRD_DF_PVT; -- Package body
5480