[Home] [Help]
PACKAGE BODY: APPS.AHL_AVF_PRIM_VSTS_PVT
Source
1 PACKAGE BODY AHL_AVF_PRIM_VSTS_PVT AS
2 /* $Header: AHLVPRVB.pls 120.0.12020000.3 2013/03/19 09:28:40 satrajen noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_AVF_PRIM_VSTS_PVT';
6
7 ------------------------------------
8 -- Common constants and variables --
9 ------------------------------------
10 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
11 l_log_statement NUMBER := fnd_log.level_statement;
12 l_log_procedure NUMBER := fnd_log.level_procedure;
13
14 ---------------------------------------------------------------------
15 -- Define Record Types for record structures needed by the APIs --
16 ---------------------------------------------------------------------
17 -- NO RECORD TYPES *************
18
19 --------------------------------------------------------------------
20 -- Define Table Type for Records Structures --
21 --------------------------------------------------------------------
22 -- NO TABLE TYPES **************
23
24 --------------------------------------------------------------------
25 -- START: Defining local functions and procedures BODY --
26 --------------------------------------------------------------------
27
28 -- Validate MR operating organization and returns maintenance organization defined for it
29 -------------------------------------------------------------------
30 -- Procedure name : Validate_MR_Operating_Org
31 -- Type : Private
32 -- Function : Validate MR operating organization and returns maintenance organization defined for it
33 -- Parameters :
34 --
35 -- Standard IN Parameters :
36 -- p_api_version IN NUMBER Required
37 -- p_init_msg_list IN VARCHAR2 Optional
38 -- p_commit IN VARCHAR2 Optional
39 -- p_validation_level IN NUMBER Optional
40 -- p_mr_header_id IN NUMBER Required
41 -- p_operating_org_id OUT NUMBER
42 -- x_maintenance_dept_id OUT NUMBER
43 -- x_return_status OUT VARCHAR2
44 -- x_msg_count OUT NUMBER
45 -- x_msg_data OUT VARCHAR2
46 --
47 -- Version :
48 -- Initial Version 12.1.3
49 -------------------------------------------------------------------
50 PROCEDURE Validate_MR_Operating_Org (
51 p_api_version IN NUMBER,
52 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
53 p_commit IN VARCHAR2 := Fnd_Api.g_false,
54 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
55 p_mr_header_id IN NUMBER,
56 p_operating_org_id IN NUMBER,
57 x_maintenance_org_id OUT NOCOPY NUMBER,
58 x_maintenance_dept_id OUT NOCOPY NUMBER,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2
62 );
63
64 -------------------------------------------------------------------------------------------------------------------------------
65 -- PROCEDURE
66 -- Add_Planned_MRs
67 -- TYPE
68 -- Public
69 -- PURPOSE
70 -- To Add Planned maintainence requirements to the visits.
71 -- PARAMETERS
72 -- p_visit_id Input Number Optional(Any One Required)
73 -- p_snapshot_id Input Number Optional(Any One Required)
74 -------------------------------------------------------------------------------------------------------------------------------
75
76 PROCEDURE Add_Planned_MRs
77 (
78 p_api_version IN NUMBER := 1.0,
79 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
80 p_commit IN VARCHAR2 := FND_API.G_FALSE,
81 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
82 p_visit_id IN NUMBER := NULL,
83 p_snapshot_id IN NUMBER := NULL,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2) IS
87
88 -- Local variables and constants.
89
90 L_API_NAME CONSTANT VARCHAR2(30) := 'Add_Planned_MRs';
91 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
92 L_MASTER_CODE CONSTANT VARCHAR2(20) := 'MASTER_CONFIGURATION';
93 L_API_VERSION CONSTANT NUMBER := 1.0;
94 l_start_date DATE;
95 l_end_date DATE;
96 l_mc_id NUMBER;
97 l_ue_id NUMBER;
98 l_due_date DATE;
99 l_lat_due_date DATE;
100 l_instance_id NUMBER;
101 l_category_ue NUMBER;
102 l_visit_id NUMBER;
103 l_category_visit VARCHAR2(2);
104 l_parent_check VARCHAR2(1);
105 l_task_rec AHL_VWP_RULES_PVT.Task_Tbl_Type;
106 l_valid_visit_flag VARCHAR2(1);
107 l_valid_snapshot_flag VARCHAR2(1);
108 l_return_status VARCHAR2(1);
109 l_msg_count NUMBER;
110 l_msg_data VARCHAR2(2000);
111 l_count_ues NUMBER;
112
113
114 -- Starting of Cursors definition
115
116 -- Check The Validity of the visit_id passed.
117 CURSOR check_validity_visit (c_visit_id IN NUMBER) is
118 SELECT 'X'
119 FROM ahl_visits_b
120 WHERE visit_id = c_visit_id
121 AND nvl(locked_flag,'N') = 'N'
122 AND nvl(firmed_flag,'N') = 'N'
123 AND start_date_time IS NOT NULL
124 AND close_date_time IS NOT NULL;
125
126 -- Check The Validity of the snapshot_id passed.
127 CURSOR check_validity_snapshot(c_snapshot_id in NUMBER) IS
128 SELECT 'X'
129 FROM ahl_autovst_snpsht_hdr
130 WHERE snapshot_id = c_snapshot_id;
131
132 -- Getting Visit Details for a particular Visit.
133 CURSOR get_visit_details(c_visit_id IN NUMBER)IS
134 SELECT item_instance_id, start_date_time, close_date_time,nvl(space_category_code,0)
135 FROM ahl_visits_b
136 WHERE visit_id = c_visit_id;
137
138 -- Check for the Parent MR
139
140 CURSOR check_parent_mr(c_ue_id in NUMBER,
141 c_instance_id in NUMBER,
142 c_visit_id in NUMBER)IS
143 SELECT 'X'
144 FROM ahl_ue_relationships uer, ahl_visit_tasks_b vtk, ahl_unit_effectivities_b uet
145 WHERE uer.ue_id = c_ue_id
146 AND vtk.instance_id = c_instance_id
147 AND vtk.visit_id = c_visit_id
148 AND uer.ue_id = uet.unit_effectivity_id
149 AND uet.mr_header_id = vtk.mr_id;
150
151 -- Getting the UE_IDs which has the same Instance Id as the visit.
152 CURSOR get_ue_details_visit(c_instance_id in NUMBER,
153 c_start_date in DATE,
154 c_end_date in DATE) IS
155 SELECT une.unit_effectivity_id, nvl(mrh.service_category_rank,0)
156 FROM ahl_unit_effectivities_b une, ahl_mr_headers_b mrh
157 WHERE une.csi_item_instance_id = c_instance_id
158 AND (une.status_code ='INIT-DUE' OR une.status_code IS NULL)
159 AND une.due_date is NOT NULL
160 AND (une.due_date between c_start_date AND c_end_date or (une.earliest_due_date between c_start_date AND c_end_date OR une.latest_due_date between c_start_date AND c_end_date))
161 AND NOT EXISTS (SELECT 'X' FROM ahl_ue_relationships uer WHERE uer.related_ue_id = une.unit_effectivity_id)
162 AND une.mr_header_id = mrh.mr_header_id
163 AND NOT exists (SELECT 'X' FROM ahl_visit_tasks_b task, ahl_visits_b visit
164 WHERE task.visit_id = visit.visit_id AND task.unit_effectivity_id = une.unit_effectivity_id
165 AND task.status_code NOT IN ('CANCELLED', 'DELETED'));
166
167 -- Get the start and end dates of the Snapshot
168 CURSOR get_dates(c_snapshot_id in NUMBER) IS
169 SELECT from_date,to_date
170 FROM ahl_autovst_snpsht_hdr
171 WHERE snapshot_id = c_snapshot_id;
172
173 -- Get the MC_IDs for the given Snapshot
174 CURSOR get_mc_ids(c_snapshot_id in NUMBER) IS
175 SELECT mc_id
176 FROM ahl_autovisit_hierarchy
177 WHERE autovisit_flag = 'Y'
178 AND snapshot_id = c_snapshot_id
179 AND hierarchy_type_code = L_MASTER_CODE;
180
181 -- Get the UE_IDs for a particular MC_ID.
182 CURSOR get_ue_details(c_mc_id in NUMBER,
183 c_start_date in DATE,
184 c_end_date in DATE) IS
185 SELECT une.unit_effectivity_id, une.due_date, une.latest_due_date, une.csi_item_instance_id, nvl(mrh.service_category_rank,0)
186 FROM ahl_unit_effectivities_b une, ahl_mr_headers_b mrh, ahl_unit_config_headers uch,ahl_mc_headers_b mc
187 WHERE mc.mc_id = c_mc_id
188 AND uch.master_config_id = mc.mc_header_id --tchimira :: 21 May 2012 :: compare correct columns
189 AND une.csi_item_instance_id = uch.csi_item_instance_id
190 AND (une.status_code ='INIT-DUE' OR une.status_code IS NULL)
191 AND une.due_date IS NOT NULL
192 AND (une.due_date between c_start_date AND c_end_date or (une.earliest_due_date between c_start_date AND c_end_date OR une.latest_due_date between c_start_date AND c_end_date))
193 AND NOT EXISTS (SELECT 'X' FROM ahl_ue_relationships uer WHERE uer.related_ue_id = une.unit_effectivity_id)
194 AND une.mr_header_id = mrh.mr_header_id
195 AND NOT exists (SELECT 'X' FROM ahl_visit_tasks_b task, ahl_visits_b visit
196 WHERE task.visit_id = visit.visit_id AND task.unit_effectivity_id = une.unit_effectivity_id
197 AND task.status_code NOT IN ('CANCELLED', 'DELETED'));
198
199 -- Get the correct VISIT_ID for the given UE_ID.
200 CURSOR associating_visit(c_instance_id in NUMBER,
201 c_category in NUMBER,
202 c_due_date in DATE,
203 c_lat_due_date in DATE) IS
204 SELECT visit_id
205 FROM ahl_visits_b
206 WHERE status_code IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
207 AND item_instance_id = c_instance_id
208 AND nvl(locked_flag,'N') = 'N'
209 AND nvl(firmed_flag,'N') = 'N'
210 AND (c_due_date between start_date_time AND close_date_time or c_lat_due_date between start_date_time AND close_date_time)
211 AND TO_NUMBER(nvl(space_category_code,0)) <= c_category
212 ORDER BY start_date_time;
213
214 -- End of cursors definition
215
216 BEGIN
217
218 -- Save Point declaration.
219 SAVEPOINT Save_Add_MRs;
220
221 --------------------- Initialize -----------------------
222 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
223 fnd_log.string(fnd_log.level_procedure,L_DEBUG_KEY||'.begin','At the start of PLSQL procedure');
224 END IF;
225
226 -- Printing The input Parameters
227 IF (l_log_statement >= l_log_current_level) THEN
228 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' p_api_version => ' || p_api_version ||
229 ' , p_init_msg_list => ' || p_init_msg_list ||
230 ' , p_commit => ' || p_commit ||
231 ' , p_validation_level => ' || p_validation_level ||
232 ' , p_visit_id => ' || p_visit_id ||
233 ' , p_snapshot_id => ' || p_snapshot_id);
234 END IF;
235
236 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
237 Fnd_Msg_Pub.initialize;
238 END IF;
239
240 IF NOT Fnd_Api.compatible_api_call (
241 l_api_version,
242 p_api_version,
243 l_api_name,
244 G_PKG_NAME
245 ) THEN
246 RAISE Fnd_Api.g_exc_unexpected_error;
247 END IF;
248 x_return_status := Fnd_Api.g_ret_sts_success;
249
250 ----------------------------------------------------------
251
252 -- Checking which parameter is sent.
253 -- SATRAJEN :: Bug 16048246 :: 04-01-2013 :: Chenged in order to pass the condition when called from create_primary_visits.
254 -- IF((p_visit_id IS NOT NULL OR p_visit_id <> FND_API.G_MISS_NUM) AND (p_snapshot_id IS NULL or p_snapshot_id = FND_API.G_MISS_NUM)) THEN
255 IF(p_visit_id IS NOT NULL OR p_visit_id <> FND_API.G_MISS_NUM) THEN
256 -- Visit_id is passed
257
258 OPEN check_validity_visit(p_visit_id);
259 FETCH check_validity_visit INTO l_valid_visit_flag;
260 CLOSE check_validity_visit;
261
262 IF l_valid_visit_flag = 'X' THEN
263
264 IF (l_log_statement >= l_log_current_level) THEN
265 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Visit Id = ' || p_visit_id);
266 END IF;
267 l_count_ues:= 0;
268 -- Getting the Visit details(Instance id (csi_id), Start date, End date and Visit category for the particular visit_id
269 OPEN get_visit_details(p_visit_id);
270 FETCH get_visit_details INTO l_instance_id, l_start_date,l_end_date, l_category_visit;
271 CLOSE get_visit_details;
272
273 -- Getting all the UE_IDs which has the same Instance Id and the due date of the UE_ID falls between start date and end date of the visit.
274 -- A number of UE_IDs will be the outcome. Each UE_ID is associated to this Visit_id.
275
276 -- Checking whether the Visit is planned or not.
277 OPEN get_ue_details_visit(l_instance_id,l_start_date,l_end_date);
278 LOOP
279 FETCH get_ue_details_visit INTO l_ue_id, l_category_ue;
280 exit when get_ue_details_visit%NOTFOUND;
281
282 -- Check whether the MR is a parent MR and is already associated or not. If already associated to the same visit id then it is not added.
283 l_parent_check := NULL;
284 OPEN check_parent_mr(l_ue_id, l_instance_id, p_visit_id);
285 FETCH check_parent_mr INTO l_parent_check;
286 CLOSE check_parent_mr;
287
288 -- If the MR is a parent MR and is associated with the some visit, Visit_id is returned.
289 IF l_parent_check IS NULL THEN
290
291 IF (l_log_statement >= l_log_current_level) THEN
292 fnd_log.string(l_log_statement,L_DEBUG_KEY,
293 '(UE_ID,CATEGORY_UE,CATEGORY_VISIT) = ' || l_ue_id || ', ' || l_category_ue || ', ' || l_category_visit );
294 END IF;
295
296 IF TO_NUMBER(l_category_visit) <= l_category_ue THEN
297 l_task_rec(1).visit_id := p_visit_id ;
298 l_task_rec(1).unit_effectivity_id := l_ue_id;
299 l_task_rec(1).task_type_code := 'PLANNED';
300 -- assign l_ue_id to p_visit_id;
301 -- Calling the API which associates.
302
303 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
304 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_api_version--@>'||L_API_VERSION);
305 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_init_msg_list--@>'||Fnd_Api.g_false);
306 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_commit--@>'||Fnd_Api.g_false);
307 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_validation_level--@>'||Fnd_Api.g_valid_level_full);
308 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_module_type--@>'||'NULL');
309 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: l_ue_id--@>'||'l_ue_id');
310 END IF;
311
312 l_return_status := NULL;
313 l_msg_count := 0;
314 l_msg_data := NULL;
315
316
317 AHL_VWP_TASKS_PVT.Create_PUP_Tasks(
318 p_api_version => L_API_VERSION,
319 p_init_msg_list => Fnd_Api.g_false,
320 p_commit => Fnd_Api.g_false,
321 p_validation_level => Fnd_Api.g_valid_level_full,
322 p_module_type => NULL,
323 p_x_task_tbl => l_task_rec,
324 x_return_status => l_return_status,
325 x_msg_count => l_msg_count,
326 x_msg_data => l_msg_data);
327
328 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
329 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Create_PUP_Tasks :: x_return_status--@>'||l_return_status);
330 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Create_PUP_Tasks :: x_msg_count--@>'||l_msg_count);
331 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Create_PUP_Tasks :: x_msg_data--@>'||l_msg_data);
332 END IF;
333
334 IF nvl(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
335 IF (l_log_statement >= l_log_current_level) THEN
336 FND_LOG.string(l_log_statement, L_DEBUG_KEY, ' Error Occured while associating Unit effectivity '||l_ue_id||' to VISIT_ID : ' || l_visit_id || ' with x-return-status:' || l_return_status);
337 END IF;
338 ELSE
339 IF (l_log_statement >= l_log_current_level) THEN
340 FND_LOG.string(l_log_statement, L_DEBUG_KEY, ' Successfully associated Unit effectivity '||l_ue_id||' to VISIT_ID : ' || p_visit_id);
341 END IF;
342 l_count_ues:=l_count_ues+1;
343 END IF;
344
345 ELSE
346 IF (l_log_statement >= l_log_current_level) THEN
347 fnd_log.string(l_log_statement,L_DEBUG_KEY, 'UE_ID : ' || l_ue_id ||' not assigned to ' || p_visit_id || ' as category is higher ');
348 END IF;
349 END IF;
350 ELSE
351
352 IF (l_log_statement >= l_log_current_level) THEN
353 fnd_log.string(l_log_statement,L_DEBUG_KEY,
354 'UE_ID(Parent MR) ' || l_ue_id || ' is already associated to Visit_id ' || p_visit_id);
355 END IF;
356
357 END IF;
358 END LOOP;
359 CLOSE get_ue_details_visit;
360 -- SATRAJEN :: Bug 13707339 :: Added for Seperate return status according to the UEs associated.
361 IF l_count_ues = 0 THEN
362 x_return_status:='V';
363 END IF;
364 -- End of Bug 13707339
365 ELSE
366 IF (l_log_statement >= l_log_current_level) THEN
367 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Invalid Visit Id : ' || p_visit_id);
368 END IF;
369 Fnd_Message.SET_NAME('AHL','AHL_VISIT_ID_INVALID');
370 Fnd_Msg_Pub.ADD;
371 RAISE Fnd_Api.G_EXC_ERROR;
372 END IF;
373 ELSIF((p_snapshot_id IS NOT NULL OR p_snapshot_id <> FND_API.G_MISS_NUM) AND (p_visit_id IS NULL or p_visit_id = FND_API.G_MISS_NUM)) THEN
374 -- Snapshot_id is passed
375
376 OPEN check_validity_snapshot(p_snapshot_id);
377 FETCH check_validity_snapshot INTO l_valid_snapshot_flag;
378 CLOSE check_validity_snapshot;
379
380 IF l_valid_snapshot_flag = 'X' THEN
381
382 IF (l_log_statement >= l_log_current_level) THEN
383 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Snapshot Id = ' || p_snapshot_id);
384 END IF;
385
386 -- Getting the start and end date of the Snapshot
387 OPEN get_dates(p_snapshot_id);
388 FETCH get_dates INTO l_start_date, l_end_date;
389 CLOSE get_dates;
390
391 -- Getting the MC_IDs of the snapshot. Number of MC_IDs are retrieved.
392 OPEN get_mc_ids(p_snapshot_id);
393 LOOP
394 FETCH get_mc_ids INTO l_mc_id;
395 exit when get_mc_ids%NOTFOUND;
396
397 IF (l_log_statement >= l_log_current_level) THEN
398 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Process for MC_ID : ' || l_mc_id);
399 END IF;
400
401 -- Getting the UE_IDs for the particular MC_ID. Number of UE_IDs are retrieved.
402 OPEN get_ue_details(l_mc_id, l_start_date, l_end_date);
403 LOOP
404 FETCH get_ue_details INTO l_ue_id, l_due_date, l_lat_due_date, l_instance_id, l_category_ue;
405 exit when get_ue_details%NOTFOUND;
406
407 IF (l_log_statement >= l_log_current_level) THEN
408 fnd_log.string(l_log_statement,L_DEBUG_KEY,
409 '(UE_ID, INSTANCE_ID, CATEGORY_UE, DUE_DATE, LATEST_DUE_DATE) = ' || l_ue_id || ', ' || l_instance_id ||
410 ', ' || l_category_ue || ', ' || l_due_date || ', '|| l_lat_due_date );
411 END IF;
412
413 -- Getting the correct Visit_id to associate the UE_ID.
414 l_visit_id := NULL;
415 OPEN associating_visit(l_instance_id, l_category_ue, l_due_date, l_lat_due_date);
416 LOOP
417 FETCH associating_visit INTO l_visit_id;
418 IF associating_visit%NOTFOUND THEN
419 IF (l_log_statement >= l_log_current_level) THEN
420 fnd_log.string(l_log_statement,L_DEBUG_KEY, 'UE_ID : ' || l_ue_id || ' cannot be associated to any Visit_id' );
421 END IF;
422 EXIT;
423 END IF;
424 -- Check whether the MR is a parent MR and is already associated or not. If already associated to the same visit id then it is not added.
425 l_parent_check := NULL;
426 OPEN check_parent_mr(l_ue_id, l_instance_id, l_visit_id);
427 FETCH check_parent_mr INTO l_parent_check;
428 CLOSE check_parent_mr;
429
430 -- If the MR is a parent MR and is associated with the some visit, Visit_id is returned.
431 IF l_parent_check IS NULL THEN
432 l_task_rec(1).visit_id := l_visit_id;
433 l_task_rec(1).unit_effectivity_id := l_ue_id;
434 l_task_rec(1).task_type_code := 'PLANNED';
435 --assign l_ue_id to l_visit_id
436
437 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
438 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_api_version--@>'||L_API_VERSION);
439 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_init_msg_list--@>'||Fnd_Api.g_false);
440 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_commit--@>'||Fnd_Api.g_false);
441 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_validation_level--@>'||Fnd_Api.g_valid_level_full);
442 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: p_module_type--@>'||'NULL');
443 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Create_PUP_Tasks :: visit_id--@>'||'l_visit_id');
444 END IF;
445
446 l_return_status := NULL;
447 l_msg_count := 0;
448 l_msg_data := NULL;
449
450
451 AHL_VWP_TASKS_PVT.Create_PUP_Tasks(
452 p_api_version => L_API_VERSION,
453 p_init_msg_list => Fnd_Api.g_false,
454 p_commit => Fnd_Api.g_false,
455 p_validation_level => Fnd_Api.g_valid_level_full,
456 p_module_type => NULL,
457 p_x_task_tbl => l_task_rec,
458 x_return_status => l_return_status,
459 x_msg_count => l_msg_count,
460 x_msg_data => l_msg_data);
461
462 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
463 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Create_PUP_Tasks :: x_return_status--@>'||l_return_status);
464 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Create_PUP_Tasks :: x_msg_count--@>'||l_msg_count);
465 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Create_PUP_Tasks :: x_msg_data--@>'||l_msg_data);
466 END IF;
467
468 IF nvl(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
469 IF (l_log_statement >= l_log_current_level) THEN
470 FND_LOG.string(l_log_statement, L_DEBUG_KEY, ' Error Occured while associating Unit effectivity '||l_ue_id||' to VISIT_ID : ' || l_visit_id || ' with x-return-status:' || l_return_status);
471 END IF;
472 ELSE
473 IF (l_log_statement >= l_log_current_level) THEN
474 FND_LOG.string(l_log_statement, L_DEBUG_KEY, ' Successfully associated Unit effectivity '||l_ue_id||' to VISIT_ID : ' || l_visit_id);
475 END IF;
476 END IF;
477 EXIT;
478
479 ELSE
480
481 IF (l_log_statement >= l_log_current_level) THEN
482 fnd_log.string(l_log_statement,L_DEBUG_KEY,
483 'UE_ID(Parent MR) ' || l_ue_id || ' is already associated to Visit_id ' || l_visit_id);
484 END IF;
485 END IF;
486
487 END LOOP;
488 CLOSE associating_visit;
489
490 END LOOP; -- UE_ID loop ends
491 CLOSE get_ue_details;
492 END LOOP; -- MC_ID loop ends
493 CLOSE get_mc_ids;
494 ELSE
495 IF (l_log_statement >= l_log_current_level) THEN
496 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Invalid Snapshot Id : ' || p_snapshot_id);
497 END IF;
498 Fnd_Message.SET_NAME('AHL','AHL_AVF_INVALID_SNAPSHOT');
499 Fnd_Msg_Pub.ADD;
500 RAISE Fnd_Api.G_EXC_ERROR;
501 END IF;
502 ELSE
503 -- The input flag is not valid. Need to raise exception
504 IF (l_log_statement >= l_log_current_level) THEN
505 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Invalid Paramters ');
506 END IF;
507 Fnd_Message.SET_NAME('AHL','AHL_PAGE_PARAMETERS_INVALID');
508 Fnd_Msg_Pub.ADD;
509 RAISE Fnd_Api.G_EXC_ERROR;
510 END IF; -- Valid Input parameters check ends
511
512 -------------------- finish --------------------------
513 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
514 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Add_Planned_MRs :: p_encoded--@>'||Fnd_Api.g_false);
515 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Add_Planned_MRs :: p_count--@>'||x_msg_count);
516 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Add_Planned_MRs :: p_data--@>'||x_msg_data);
517 END IF;
518 Fnd_Msg_Pub.count_and_get (
519 p_encoded => Fnd_Api.g_false,
520 p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523
524
525 IF (l_log_procedure >= l_log_current_level) THEN
526 fnd_log.string(l_log_procedure,
527 L_DEBUG_KEY ||'.end',
528 'At the end of PL SQL Procedure.');
529 END IF;
530
531 -- Proceed to commit if status is 'S' or if there were only validation errors
532 --Standard check for commit
533 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
534 COMMIT;
535 END IF;
536 ----- EXCEPTIONS--------------------------------------
537 EXCEPTION
538 WHEN Fnd_Api.G_EXC_ERROR THEN
539 x_return_status := Fnd_Api.G_RET_STS_ERROR;
540 ROLLBACK TO Save_Add_MRs;
541 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
542 p_data => x_msg_data,
543 p_encoded => Fnd_Api.g_false);
544
545 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
546 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
547 ROLLBACK TO Save_Add_MRs;
548 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
549 p_data => x_msg_data,
550 p_encoded => Fnd_Api.g_false);
551
552 WHEN OTHERS THEN
553 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
554 ROLLBACK TO Save_Add_MRs;
555 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
556 p_procedure_name => 'Add_Planned_MRs',
557 p_error_text => SQLERRM);
558 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
559 p_data => x_msg_data,
560 p_encoded => Fnd_Api.g_false);
561
562 END Add_Planned_MRs; -- End of begin/procedure.
563
564 -----------------------------------------------------------------------------------------------
565 -- PROCEDURE
566 -- Cancel_Visits
567 -- TYPE
568 -- Public
569 -- PURPOSE
570 -- To Cancel the existing visits according to the Master configuration provided and the date ranges provided by the user.
571 -- PARAMETERS
572 -- p_snapshot_id Input Number Required
573 -----------------------------------------------------------------------------------------------
574
575 PROCEDURE Cancel_Visits
576 (
577 p_api_version IN NUMBER := 1.0,
578 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
579 p_commit IN VARCHAR2 := FND_API.G_FALSE,
580 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
581 p_snapshot_id IN NUMBER,
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_msg_count OUT NOCOPY NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2) IS
585
586 -- Local variables and constants.
587 L_API_NAME CONSTANT VARCHAR2(30) := 'Cancel_Visits';
588 L_MASTER_CODE CONSTANT VARCHAR2(20) := 'MASTER_CONFIGURATION';
589 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
590 L_STATUS_CODE CONSTANT VARCHAR2(9) := 'PLANNING';
591 L_API_VERSION CONSTANT NUMBER := 1.0;
592 l_valid_flag VARCHAR2(1) := 'N';
593 l_visit_id NUMBER;
594 l_obj_ver_no NUMBER;
595 l_return_status VARCHAR2(1);
596 l_msg_count NUMBER;
597 l_msg_data VARCHAR2(2000);
598
599 -- Starting of Cursors definition
600
601 -- Check the snapshot_id is valid or not.
602 CURSOR check_validity(c_snapshot_id IN NUMBER) IS
603 SELECT 'X'
604 FROM ahl_autovst_snpsht_hdr
605 WHERE snapshot_id = c_snapshot_id;
606
607 -- Get the Visits corresponding to the Start and End dates and Master configuration ids provided.
608 CURSOR get_visit_ids(c_snapshot_id IN NUMBER) IS
609 SELECT visit.visit_id, visit.object_version_number
610 FROM ahl_visits_b visit, ahl_unit_config_headers uch, ahl_autovisit_hierarchy avh, ahl_autovst_snpsht_hdr snap,ahl_mc_headers_b mc
611 WHERE visit.start_date_time IS NOT null AND visit.close_date_time IS NOT NULL
612 AND start_date_time >= snap.from_date
613 AND close_date_time <= snap.to_date
614 AND visit.status_code = L_STATUS_CODE
615 AND visit.item_instance_id IS NOT NULL
616 AND visit.item_instance_id = uch.csi_item_instance_id
617 AND nvl(visit.locked_flag,'N') = 'N'
618 AND nvl(visit.firmed_flag,'N') = 'N'
619 AND uch.master_config_id = mc.mc_header_id --tchimira :: 21 May 2012 :: bug 14082494
620 AND mc.mc_id = avh.mc_id
621 AND avh.snapshot_id = snap.snapshot_id
622 AND avh.autovisit_flag = 'Y'
623 AND avh.hierarchy_type_code = L_MASTER_CODE
624 AND snap.snapshot_id = c_snapshot_id;
625
626 -- End of cursors definition
627
628 BEGIN
629
630 -- Save Point Declaration
631 SAVEPOINT Save_Cancel_visits;
632
633 --------------------- Initialize -----------------------
634 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
635 fnd_log.string(fnd_log.level_procedure,L_DEBUG_KEY||'.begin','At the start of PLSQL procedure');
636 END IF;
637
638 -- Printing The input Parameters
639 IF (l_log_statement >= l_log_current_level) THEN
640 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' p_api_version => ' || p_api_version ||
641 ' , p_init_msg_list => ' || p_init_msg_list ||
642 ' , p_commit => ' || p_commit ||
643 ' , p_validation_level => ' || p_validation_level ||
644 ' , p_snapshot_id => ' || p_snapshot_id);
645 END IF;
646
647 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
648 Fnd_Msg_Pub.initialize;
649 END IF;
650
651 IF NOT Fnd_Api.compatible_api_call (
652 l_api_version,
653 p_api_version,
654 l_api_name,
655 G_PKG_NAME
656 ) THEN
657 RAISE Fnd_Api.g_exc_unexpected_error;
658 END IF;
659 x_return_status := Fnd_Api.g_ret_sts_success;
660
661 ----------------------------------------------------------
662 IF (l_log_procedure >= l_log_current_level) THEN
663 fnd_log.string(l_log_procedure,
664 L_DEBUG_KEY ||'.begin',
665 'At the start of PL SQL procedure.');
666 END IF;
667
668 -- Checking whether the parameter is valid or not.
669 IF(p_snapshot_id IS NOT NULL OR p_snapshot_id <> FND_API.G_MISS_NUM) THEN
670
671 -- Check the Validity of the snapshot Id in the table.
672 OPEN check_validity(p_snapshot_id);
673 FETCH check_validity INTO l_valid_flag;
674 CLOSE check_validity;
675
676 IF (l_valid_flag = 'X') THEN
677 -- Getting the VISIT_IDs of the snapshot. Number of VISIT_IDs might be retrieved.
678 IF (l_log_statement >= l_log_current_level) THEN
679 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Snapshot Id = ' || p_snapshot_id);
680 END IF;
681
682 OPEN get_visit_ids(p_snapshot_id);
683 LOOP
684 FETCH get_visit_ids into l_visit_id,l_obj_ver_no;
685 EXIT WHEN get_visit_ids%NOTFOUND;
686
687 IF (l_log_statement >= l_log_current_level) THEN
688 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' VISIT_ID : ' || l_visit_id || ' Object Version No: ' || l_obj_ver_no);
689 END IF;
690
691 -- Calling the procedure to cancel the visit.
692
693 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
694 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_api_version--@>'||L_API_VERSION);
695 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_init_msg_list--@>'||Fnd_Api.g_false);
696 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_commit--@>'||Fnd_Api.g_false);
697 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_validation_level--@>'||Fnd_Api.g_valid_level_full);
698 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Calling Delete_visit :: p_visit_id--@>'||l_visit_id);
699 END IF;
700
701 l_return_status := NULL;
702 l_msg_count := 0;
703 l_msg_data := NULL;
704
705
706 AHL_VWP_VISITS_PVT.Delete_Visit(
707 p_api_version => L_API_VERSION,
708 p_init_msg_list => Fnd_Api.g_false,
709 p_commit => Fnd_Api.g_false,
710 p_validation_level => Fnd_Api.g_valid_level_full,
711 p_visit_id => l_visit_id,
712 x_return_status => l_return_status,
713 x_msg_count => l_msg_count,
714 x_msg_data => l_msg_data);
715
716
717 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
718 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Delete_visit :: x_return_status--@>'||l_return_status);
719 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Delete_visit :: x_msg_count--@>'||l_msg_count);
720 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Delete_visit :: x_msg_data--@>'||l_msg_data);
721 END IF;
722
723 IF nvl(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
724 IF (l_log_statement >= l_log_current_level) THEN
725 FND_LOG.string(l_log_statement, L_DEBUG_KEY, ' Error Occured while cancelling VISIT_ID : ' || l_visit_id || ' with x-return-status:' || l_return_status);
726 END IF;
727 ELSE
728 IF (l_log_statement >= l_log_current_level) THEN
729 FND_LOG.string(l_log_statement, L_DEBUG_KEY, ' Successfully cancelled VISIT_ID : ' || l_visit_id);
730 END IF;
731 END IF;
732
733 END LOOP;
734 close get_visit_ids;
735 ELSE
736 IF (l_log_statement >= l_log_current_level) THEN
737 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Invalid Snapshot Id : ' || p_snapshot_id);
738 END IF;
739 Fnd_Message.SET_NAME('AHL','AHL_AVF_INVALID_SNAPSHOT');
740 Fnd_Msg_Pub.ADD;
741 RAISE Fnd_Api.G_EXC_ERROR;
742 END IF;
743
744 ELSE
748 Fnd_Message.SET_NAME('AHL','AHL_PAGE_PARAMETERS_INVALID');
745 IF (l_log_statement >= l_log_current_level) THEN
746 fnd_log.string(l_log_statement,L_DEBUG_KEY, ' Null Paramter ');
747 END IF;
749 Fnd_Msg_Pub.ADD;
750 RAISE Fnd_Api.G_EXC_ERROR;
751 END IF;
752 -------------------- finish --------------------------
753 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
754 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Cancel_Visits :: p_encoded--@>'||Fnd_Api.g_false);
755 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Cancel_Visits :: p_count--@>'||x_msg_count);
756 fnd_log.string(fnd_log.level_statement,L_DEBUG_KEY,'Return Cancel_Visits :: p_data--@>'||x_msg_data);
757 END IF;
758 Fnd_Msg_Pub.count_and_get (
759 p_encoded => Fnd_Api.g_false,
760 p_count => x_msg_count,
761 p_data => x_msg_data
762 );
763
764
765 IF (l_log_procedure >= l_log_current_level) THEN
766 fnd_log.string(l_log_procedure,
767 L_DEBUG_KEY ||'.end',
768 'At the end of PL SQL Procedure.');
769 END IF;
770
771 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
772 COMMIT;
773 END IF;
774 ----- EXCEPTIONS--------------------------------------
775
776 EXCEPTION
777 WHEN Fnd_Api.G_EXC_ERROR THEN
778 x_return_status := Fnd_Api.G_RET_STS_ERROR;
779 ROLLBACK TO Save_Cancel_visits;
780 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
781 p_data => x_msg_data,
782 p_encoded => Fnd_Api.g_false);
783
784 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
785 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
786 ROLLBACK TO Save_Cancel_visits;
787 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
788 p_data => x_msg_data,
789 p_encoded => Fnd_Api.g_false);
790
791 WHEN OTHERS THEN
792 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
793 ROLLBACK TO Save_Cancel_visits;
794 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
795 p_procedure_name => 'Cancel_Visits',
796 p_error_text => SQLERRM);
797 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
798 p_data => x_msg_data,
799 p_encoded => Fnd_Api.g_false);
800
801 END Cancel_Visits; -- End of begin/procedure.
802
803 -------------------------------------------------------------------------------------------------------------------------
804 -- Validate MR operating organization and returns maintenance organization defined for it
805 -------------------------------------------------------------------
806 -- Procedure name : Validate_MR_Operating_Org
807 -- Type : Private
808 -- Function : Validate MR operating organization and returns maintenance organization defined for it
809 -- Parameters :
810 --
811 -- Standard IN Parameters :
812 -- p_api_version IN NUMBER Required
813 -- p_init_msg_list IN VARCHAR2 Optional
814 -- p_commit IN VARCHAR2 Optional
815 -- p_validation_level IN NUMBER Optional
816 -- p_mr_header_id IN NUMBER Required
817 -- p_operating_org_id OUT NUMBER
818 -- x_maintenance_dept_id OUT NUMBER
819 -- x_return_status OUT VARCHAR2
820 -- x_msg_count OUT NUMBER
821 -- x_msg_data OUT VARCHAR2
822 --
823 -- Version :
824 -- Initial Version 12.1.3
825 -------------------------------------------------------------------
826 PROCEDURE Validate_MR_Operating_Org (
827 p_api_version IN NUMBER,
828 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
829 p_commit IN VARCHAR2 := Fnd_Api.g_false,
830 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
831 p_mr_header_id IN NUMBER,
832 p_operating_org_id IN NUMBER,
833 x_maintenance_org_id OUT NOCOPY NUMBER,
834 x_maintenance_dept_id OUT NOCOPY NUMBER,
835 x_return_status OUT NOCOPY VARCHAR2,
836 x_msg_count OUT NOCOPY NUMBER,
837 x_msg_data OUT NOCOPY VARCHAR2
838 )
839 IS
840 -- Define local Variables
841 L_API_VERSION CONSTANT NUMBER := 1.0;
842 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_MR_Operating_Org';
843 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
844 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
845
846 l_return_status VARCHAR2(1);
847 l_msg_data VARCHAR2(2000);
848
849 l_mr_header_id NUMBER;
850 l_operating_org_id NUMBER;
851
852 /* Cursor to find maintenance org. from the given operating org. and MR. */
853 Cursor get_operating_org_details(c_mr_header_id IN NUMBER, c_operating_org_id IN NUMBER) IS
854 SELECT MROrg.MR_ORGANIZATION_ID,
855 MROrg.OPERATING_ORG_ID OPERATING_ORG_ID,
856 MROrg.MAINTENANCE_ORG_ID,
857 HRU.NAME MAINTENANCE_ORG_NAME,
858 MROrg.MAINTENANCE_DEPT_ID,
859 ADSV.DEPT_DESCRIPTION MAINTENANCE_DEPT_NAME
860 FROM AHL_MR_HEADERS_B AMH,
861 AHL_MR_ORGANIZATIONS MROrg,
862 HR_ORGANIZATION_UNITS HRU,
863 AHL_DEPARTMENT_SHIFTS_V ADSV
864 WHERE MROrg.MR_TITLE = AMH.TITLE
865 AND MROrg.MAINTENANCE_ORG_ID = HRU.ORGANIZATION_ID
866 AND MROrg.MAINTENANCE_DEPT_ID = ADSV.DEPARTMENT_ID
867 AND AMH.MR_HEADER_ID = c_mr_header_id
868 AND ( MROrg.OPERATING_ORG_ID = c_operating_org_id
872 l_oper_org_dets get_operating_org_details%RowType;
869 OR MROrg.OPERATING_ORG_ID IS NULL )
870 order by MROrg.OPERATING_ORG_ID NULLS LAST;
871
873
874 /* Cursor to get MR Header details*/
875 Cursor get_mr_header_details(c_mr_header_id IN NUMBER) IS
876 SELECT mr_header_id FROM ahl_mr_headers_vl
877 WHERE mr_header_id = c_mr_header_id;
878
879 l_mr_header_dets get_mr_header_details%RowType;
880
881 BEGIN
882 --------------------- Initialize -----------------------
883 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
884 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
885 END IF;
886
887 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
888 Fnd_Msg_Pub.initialize;
889 END IF;
890
891 IF NOT Fnd_Api.compatible_api_call (
892 l_api_version,
893 p_api_version,
894 l_api_name,
895 G_PKG_NAME
896 ) THEN
897 RAISE Fnd_Api.g_exc_unexpected_error;
898 END IF;
899 x_return_status := Fnd_Api.g_ret_sts_success;
900
901 ---------------------- Validate MR Header ID ------------------------
902 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
903 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validating MR Header ID');
904 END IF;
905
906 --Validate MR
907 OPEN get_mr_header_details(p_mr_header_id);
908 FETCH get_mr_header_details INTO l_mr_header_dets;
909 CLOSE get_mr_header_details;
910
911 l_mr_header_id := l_mr_header_dets.mr_header_id;
912 IF l_mr_header_id IS NULL THEN
913
914 Fnd_Message.SET_NAME('AHL','AHL_FMP_INVALID_MR');
915 Fnd_Msg_Pub.ADD;
916 RAISE Fnd_Api.G_EXC_ERROR;
917
918 END IF;
919
920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
921 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Org Validation :: p_mr_header_id--@>'||p_mr_header_id);
922 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Org Validation :: l_mr_header_id--@>'||l_mr_header_id);
923 END IF;
924
925 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
926 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validated MR Header ID');
927 END IF;
928
929 ---------------------- Validate Operating Organization ------------------------
930 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
931 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validating Operating Organization');
932 END IF;
933
934 --Validate Organization
935 AHL_VWP_RULES_PVT.Check_Org_Name_Or_Id
936 (p_organization_id => p_operating_org_id,
937 p_org_name => null,
938 x_organization_id => l_operating_org_id,
939 x_return_status => l_return_status,
940 x_error_msg_code => l_msg_data);
941
942 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
946 END IF;
943 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Org Validation :: p_operating_org_id--@>'||p_operating_org_id);
944 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Org Validation :: l_operating_org_id--@>'||l_operating_org_id);
945 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Org Validation :: l_return_status--@>'||l_return_status);
947
948 IF NVL(l_return_status,'x') <> 'S' THEN
949 Fnd_Message.SET_NAME('AHL','AHL_VWP_ORG_NOT_EXISTS');
950 Fnd_Msg_Pub.ADD;
951 RAISE Fnd_Api.G_EXC_ERROR;
952 END IF;
953
954 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
955 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validated Operating Organization');
956 END IF;
957
958 x_maintenance_org_id := NULL;
959 x_maintenance_dept_id := NULL;
960
961 --Get maintenance organization details for the given operating organization
962 OPEN get_operating_org_details(l_mr_header_id, l_operating_org_id);
963 FETCH get_operating_org_details INTO l_oper_org_dets;
964 CLOSE get_operating_org_details;
965
966 --Set OUT variables
967 x_maintenance_org_id := l_oper_org_dets.MAINTENANCE_ORG_ID;
968 x_maintenance_dept_id := l_oper_org_dets.MAINTENANCE_DEPT_ID;
969
970 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
971 fnd_log.string(fnd_log.level_statement,L_DEBUG,'x_maintenance_org_id ** x_maintenance_dept_id ='||x_maintenance_org_id||' ** '||x_maintenance_dept_id);
972 END IF;
973
974 --Commented out to leave the null situation for caller to handle :: PRAKKUM :: Bug 13589168 :: 01/16/2012
975 /*
976 --If maintenance organization or department not found, then set status as failure and raise error
977 IF x_maintenance_org_id is NULL or x_maintenance_dept_id is NULL THEN
978
979 Fnd_Message.SET_NAME('AHL','AHL_OPER_NO_MAINTENANCE_DETS');
980 Fnd_Msg_Pub.ADD;
981 x_return_status := Fnd_Api.g_ret_sts_error;
982
983 END IF;
984 */
988 p_encoded => Fnd_Api.g_false,
985
986 -------------------- finish --------------------------
987 Fnd_Msg_Pub.count_and_get (
989 p_count => x_msg_count,
990 p_data => x_msg_data
991 );
992
993 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
994 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
995 END IF;
996
997 EXCEPTION
998 WHEN Fnd_Api.g_exc_error THEN
999 x_return_status := Fnd_Api.g_ret_sts_error;
1000 Fnd_Msg_Pub.count_and_get (
1001 p_encoded => Fnd_Api.g_false,
1002 p_count => x_msg_count,
1003 p_data => x_msg_data
1004 );
1005 WHEN Fnd_Api.g_exc_unexpected_error THEN
1006 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1007 Fnd_Msg_Pub.count_and_get (
1008 p_encoded => Fnd_Api.g_false,
1009 p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012 WHEN OTHERS THEN
1013 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
1014 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
1015 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1016 END IF;
1017 Fnd_Msg_Pub.count_and_get (
1018 p_encoded => Fnd_Api.g_false,
1019 p_count => x_msg_count,
1020 p_data => x_msg_data
1021 );
1022 END Validate_MR_Operating_Org;
1023
1024
1025 -------------------------------------------------------------------
1026 -- Procedure name : Create_Primary_Visits
1027 -- Type : Private
1028 -- Function : Procedure to create primary visits based on auto visit hierarchy and on primary UEs
1029 -- Parameters :
1030 --
1031 -- Standard IN Parameters :
1032 -- p_api_version IN NUMBER Required
1033 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1034 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1035 --
1036 -- Standard OUT Parameters :
1037 -- x_return_status OUT VARCHAR2 Required
1038 -- x_msg_count OUT NUMBER Required
1039 -- x_msg_data OUT VARCHAR2 Required
1040 --
1041 -- Create_Primary_Visits Parameters:
1042 -- p_snapshot_id IN NUMBER Required
1043 --------------------------------------------------------------------------------------------------------------------
1044 PROCEDURE Create_Primary_Visits (
1045 p_api_version IN NUMBER := 1.0,
1046 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1047 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1048 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1049 p_snapshot_id IN NUMBER,
1050 p_add_plan_flag IN VARCHAR2 := 'N',
1051 x_return_status OUT NOCOPY VARCHAR2,
1052 x_msg_count OUT NOCOPY NUMBER,
1053 x_msg_data OUT NOCOPY VARCHAR2)IS
1054
1055 -- Local Variables
1056
1057 -- Standard in/out parameters
1058 l_api_name VARCHAR2(30) := 'Create_Primary_Visits';
1059 l_api_version NUMBER := 1.0;
1060 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1061 l_msg_count NUMBER;
1062 l_msg_data VARCHAR2(2000);
1063 l_return_status VARCHAR2(1);
1064
1065 l_maintenance_org_id NUMBER;
1066 l_maintenance_dept_id NUMBER;
1067 l_start_date DATE;
1068 l_start_hour NUMBER;
1069 l_start_min NUMBER;
1070 l_end_date DATE;
1071 l_end_hour NUMBER;
1072 l_end_min NUMBER;
1073 l_operating_org_id NUMBER;
1074 -- SATRAJEN :: Bug 16048246 :: Added to call Add_planned_MRs after every creation of visit if required.
1075 l_requery_flag VARCHAR2(1) := 'Y';
1076 l_visit_rec AHL_VWP_VISITS_PVT.Visit_Rec_Type;
1077 l_task_rec AHL_VWP_RULES_PVT.Task_Tbl_Type;
1078 l_visit_number NUMBER;
1079
1080 l_start_date_ts TIMESTAMP; --PRAKKUM :: 09/03/2012 :: Bug 13797431
1081 l_end_date_ts TIMESTAMP; --PRAKKUM :: 09/03/2012 :: Bug 13797431
1082
1083 --Cursors
1084
1085 CURSOR get_visit_number(c_visit_id IN NUMBER) IS
1086 SELECT visit_number
1087 FROM ahl_visits_b
1088 WHERE visit_id = c_visit_id;
1089
1090 Cursor get_mc_ids (c_snapshot_id IN NUMBER)
1091 IS
1092 SELECT MC_ID
1093 FROM ahl_autovisit_hierarchy
1094 WHERE HIERARCHY_TYPE_CODE = 'MASTER_CONFIGURATION'
1095 AND SNAPSHOT_ID = c_snapshot_id
1096 AND AUTOVISIT_FLAG = 'Y'
1097 ORDER BY SEQUENCE;
1098
1099 Cursor get_program_type_code (c_snapshot_id IN NUMBER)
1100 IS
1101 SELECT PROGRAM_TYPE_CODE
1102 FROM ahl_autovisit_hierarchy
1103 WHERE HIERARCHY_TYPE_CODE = 'PROGRAM'
1104 AND SNAPSHOT_ID = c_snapshot_id
1105 AND AUTOVISIT_FLAG = 'Y'
1106 ORDER BY SEQUENCE;
1107
1108 Cursor get_prog_subtyp_code (c_snapshot_id IN NUMBER, c_prog_typ_code IN VARCHAR2)
1109 IS
1110 SELECT PROGRAM_SUBTYPE_CODE
1111 FROM ahl_autovisit_hierarchy
1112 WHERE HIERARCHY_TYPE_CODE = 'SUBTYPE'
1113 AND PROGRAM_TYPE_CODE = c_prog_typ_code
1114 AND SNAPSHOT_ID = c_snapshot_id
1115 AND AUTOVISIT_FLAG = 'Y'
1116 ORDER BY SEQUENCE;
1117
1118 Cursor get_count_prog_subtyp (c_snapshot_id IN NUMBER, c_prog_typ_code IN VARCHAR2)
1119 IS
1120 SELECT count(PROGRAM_SUBTYPE_CODE)
1121 FROM ahl_autovisit_hierarchy
1122 WHERE HIERARCHY_TYPE_CODE = 'SUBTYPE'
1126 l_count NUMBER;
1123 AND PROGRAM_TYPE_CODE = c_prog_typ_code
1124 AND SNAPSHOT_ID = c_snapshot_id
1125 AND AUTOVISIT_FLAG = 'Y';
1127
1128 -- Get the start and end dates of a given Snapshot
1129 cursor get_dates(c_snapshot_id IN NUMBER) is
1130 select from_date,to_date
1131 from ahl_autovst_snpsht_hdr
1132 where snapshot_id = c_snapshot_id;
1133 dates_rec get_dates%ROWTYPE;
1134
1135
1136 -- Get the UE_IDs of primary MRs
1137 cursor get_primary_ues(c_mc_id IN NUMBER,
1138 c_pt_code IN VARCHAR2,
1139 c_pst_code IN VARCHAR2,
1140 c_start_date IN DATE,
1141 c_end_date IN DATE) is
1142 SELECT ue.unit_effectivity_id, ue.due_date, ue.csi_item_instance_id, mr.mr_header_id,
1143 mr_vtyp.mr_visit_type_code, vtyp.estimated_duration
1144 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr, ahl_mr_visit_types_app_v mr_vtyp, ahl_visit_types_b vtyp,
1145 AHL_UNIT_CONFIG_HEADERS unit, ahl_mc_headers_b mc
1146 WHERE ue.csi_item_instance_id = unit.csi_item_instance_id
1147 AND unit.master_config_id = mc.mc_header_id
1148 AND unit.unit_config_status_code NOT IN ('QUARANTINE', 'EXPIRED')
1149 AND mc.mc_id = c_mc_id
1150 AND ue.mr_header_id = mr.mr_header_id
1151 AND mr.program_type_code = c_pt_code
1152 AND (mr.program_subtype_code = c_pst_code
1153 OR c_pst_code IS NULL)
1154 AND mr.implement_status_code IN ('MANDATORY', 'OPTIONAL_IMPLEMENT')
1155 AND mr.mr_header_id = mr_vtyp.mr_header_id
1156 AND mr_vtyp.mr_visit_type_code = vtyp.visit_type_code
1157 AND vtyp.mc_id = c_mc_id
1158 AND vtyp.status_code = 'COMPLETE'
1159 AND NOT EXISTS (SELECT 'X' from ahl_visit_tasks_b task
1160 WHERE task.unit_effectivity_id = ue.unit_effectivity_id
1161 AND task.status_code NOT IN ('CANCELLED', 'DELETED') )
1162 AND ue.due_date BETWEEN c_start_date AND c_end_date
1163 AND NVL(ue.earliest_due_date, c_end_date) BETWEEN c_start_date AND c_end_date
1164 AND NVL(ue.latest_due_date, c_end_date) BETWEEN c_start_date AND c_end_date;
1165 l_primary_ue_rec get_primary_ues%ROWTYPE;
1166
1167 Cursor get_ue_operating_org (c_ue_id IN NUMBER) IS
1168 SELECT fleet.operating_org_id
1169 FROM ahl_fleet_headers_b fleet, ahl_unit_effectivities_b ue
1170 WHERE fleet.fleet_header_id = ue.fleet_header_id
1171 and ue.unit_effectivity_id = c_ue_id;
1172
1173
1174 BEGIN
1175
1176 IF (l_log_procedure >= l_log_current_level)THEN
1177 fnd_log.string
1178 (
1179 l_log_procedure,
1180 'ahl.plsql.AHL_AVF_PRIM_VSTS_PVT.Create_Primary_Visits.begin',
1181 'At the start of PLSQL procedure, snapshot ID : ' || p_snapshot_id
1182 );
1183 END IF;
1184
1185 -- Standard start of API savepoint
1186 SAVEPOINT Create_Primary_Visits_pvt;
1187
1188 -- Initialize message list if p_init_msg_list is set to TRUE
1189
1190 IF FND_API.To_Boolean( p_init_msg_list) THEN
1191 FND_MSG_PUB.Initialize;
1192 END IF;
1193
1194 -- Initialize API return status to success
1195 x_return_status := FND_API.G_RET_STS_SUCCESS;
1196
1197 -- Standard call to check for call compatibility.
1198 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
1199 p_api_version,
1200 l_api_name,G_PKG_NAME)
1201 THEN
1202 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1203 END IF;
1204
1205 -- make sure that snapshot id is present in the input and is valid
1206 IF(p_snapshot_id IS NULL OR p_snapshot_id = FND_API.G_MISS_NUM) THEN
1207 FND_MESSAGE.Set_Name('AHL','AHL_AVF_SNPSHT_INPUT_MISS');
1208 FND_MSG_PUB.ADD;
1209
1210 IF (fnd_log.level_exception >= l_log_current_level)THEN
1211 fnd_log.string
1212 (
1213 fnd_log.level_exception,
1214 L_DEBUG_KEY,
1215 'Snapshot id is mandatory but found null in input '
1216 );
1217 END IF;
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219
1220 ELSE
1221 OPEN get_dates (p_snapshot_id);
1222 FETCH get_dates INTO dates_rec;
1223 IF get_dates%NOTFOUND THEN
1224 CLOSE get_dates;
1225 FND_MESSAGE.Set_Name('AHL','AHL_AVF_SNPSHT_INPUT_INVLD');
1226 FND_MSG_PUB.ADD;
1227
1228 IF (fnd_log.level_exception >= l_log_current_level)THEN
1229 fnd_log.string
1230 (
1231 fnd_log.level_exception,
1232 L_DEBUG_KEY,
1233 'Snapshot id is invalid '
1234 );
1235 END IF;
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238 CLOSE get_dates;
1239 END IF;
1240
1241 -- Added for logging visit numbers :: SATRAJEN
1242 fnd_file.put_line(fnd_file.log, 'List of Visit Numbers created ');
1243
1244
1245 FOR l_prog_typ_rec IN get_program_type_code( p_snapshot_id ) LOOP
1246 OPEN get_count_prog_subtyp ( p_snapshot_id, l_prog_typ_rec.PROGRAM_TYPE_CODE );
1247 FETCH get_count_prog_subtyp INTO l_count;
1248 CLOSE get_count_prog_subtyp;
1249
1250 l_operating_org_id := null;
1251 IF l_count > 0 THEN
1252 --Get the program subtypes for the given program type and snapshot
1253 FOR l_prog_subtyp_rec IN get_prog_subtyp_code( p_snapshot_id, l_prog_typ_rec.PROGRAM_TYPE_CODE ) LOOP
1254 FOR l_mc_rec IN get_mc_ids( p_snapshot_id ) LOOP
1255 -- Get all the primary UEs for a given set of parameters
1256 -- SATRAJEN :: Changes Start : Bug 16048246 : To call Add_Planned_MRs when ever a Primary visit gets created. :: 28-12-2012
1262 -- Cursor get_primary_ues has to be closed inorder to requery the new list of UEs.
1257 -- FOR l_primary_ue_rec IN get_primary_ues( l_mc_rec.mc_id, l_prog_typ_rec.PROGRAM_TYPE_CODE, l_prog_subtyp_rec.program_subtype_code, dates_rec.from_date, dates_rec.to_date ) LOOP
1258 l_requery_flag := 'Y';
1259 LOOP
1260 -- Checking the l_requery_flag as 'Y' in order to requery/Query for the first time, the list of UEs that are to be processed.(As Add planned MRs would have associated some UEs to visits)
1261 IF l_requery_flag = 'Y' THEN
1263 IF get_primary_ues%ISOPEN THEN
1264 CLOSE get_primary_ues;
1265 END IF;
1266 OPEN get_primary_ues(l_mc_rec.mc_id, l_prog_typ_rec.PROGRAM_TYPE_CODE, l_prog_subtyp_rec.program_subtype_code, dates_rec.from_date, dates_rec.to_date);
1267 FETCH get_primary_ues INTO l_primary_ue_rec;
1268 l_requery_flag := 'N';
1269 ELSE -- l_requery_flag = 'N'. Add_planned_MRs was not called. So No need to requery, but fetch the next record.
1270 FETCH get_primary_ues INTO l_primary_ue_rec;
1271 END IF;
1272 EXIT WHEN get_primary_ues%NOTFOUND;
1273 -- SATRAJEN :: END OF Changes for Bug 16048246.:: 28-12-2012
1274 OPEN get_ue_operating_org(l_primary_ue_rec.unit_effectivity_id);
1275 FETCH get_ue_operating_org INTO l_operating_org_id;
1276 CLOSE get_ue_operating_org;
1277 IF l_operating_org_id IS NOT NULL THEN
1278 -- Now call Validate_MR_Operating_Org to get the organization and department of the primary visit
1279 IF (l_log_statement >= l_log_current_level) THEN
1280 fnd_log.string
1281 ( l_log_statement,
1282 L_DEBUG_KEY,
1283 'Before calling Validate_MR_Operating_Org, mr_header_id= '||l_primary_ue_rec.mr_header_id||', operating_org_id= '||l_operating_org_id);
1284 END IF;
1285 Validate_MR_Operating_Org (
1286 p_api_version => l_api_version,
1287 p_init_msg_list => Fnd_Api.g_false,
1288 p_commit => Fnd_Api.g_false,
1289 p_validation_level => p_validation_level,
1290 p_mr_header_id => l_primary_ue_rec.mr_header_id,
1291 p_operating_org_id => l_operating_org_id,
1292 x_maintenance_org_id => l_maintenance_org_id,
1293 x_maintenance_dept_id => l_maintenance_dept_id,
1294 x_return_status => l_return_status,
1295 x_msg_count => l_msg_count,
1296 x_msg_data => l_msg_data
1297 );
1298 IF (l_log_statement >= l_log_current_level) THEN
1299 fnd_log.string(l_log_statement,
1300 L_DEBUG_KEY,
1301 'After calling Validate_MR_Operating_Org l_return_status= '||l_return_status);
1302 END IF;
1303
1304 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1305 x_msg_count := FND_MSG_PUB.count_msg;
1306 IF (l_log_statement >= l_log_current_level) THEN
1307 fnd_log.string(l_log_statement,
1308 L_DEBUG_KEY,
1309 'Errors from Validate_MR_Operating_Org. Message count: ' || x_msg_count);
1310 END IF;
1311 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1312 RAISE FND_API.G_EXC_ERROR;
1313 ELSE
1314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1315 END IF;
1316 END IF; -- Return Status is not Success
1317
1318 -- PRAKKUM :: Bug 13589168 :: 01/16/2012 :: START
1319 IF (l_log_statement >= l_log_current_level) THEN
1320 fnd_log.string(l_log_statement,
1321 L_DEBUG_KEY,
1322 'l_maintenance_org_id ** l_maintenance_dept_id: ' || l_maintenance_org_id || ' ** '|| l_maintenance_dept_id);
1323 END IF;
1324 IF l_maintenance_org_id is NULL or l_maintenance_dept_id is NULL THEN
1325 CONTINUE;
1326 END IF;
1327 -- PRAKKUM :: Bug 13589168 :: 01/16/2012 :: END
1328
1329 -- Calculate visits start and end date and time based on department shift and UE due date
1330 l_start_date := AHL_VWP_TIMES_PVT.Compute_Date(l_primary_ue_rec.due_date, l_maintenance_dept_id,0);
1331
1332 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1333 l_start_date_ts := cast(l_start_date as timestamp);
1337 l_start_min := (extract(MINUTE from l_start_date_ts));
1334 --l_start_hour := (l_start_date - trunc(l_start_date) )*24;
1335 --l_start_min := (l_start_hour - trunc (l_start_hour))*60;
1336 l_start_hour := (extract(HOUR from l_start_date_ts));
1338
1339 l_end_date := AHL_VWP_TIMES_PVT.Compute_Date(l_primary_ue_rec.due_date, l_maintenance_dept_id, l_primary_ue_rec.estimated_duration);
1340
1341 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1342 l_end_date_ts := cast(l_end_date as timestamp);
1343 --l_end_hour := (l_end_date - trunc(l_end_date) )*24;
1344 --l_end_min := (l_end_hour - trunc (l_end_hour))*60;
1345 l_end_hour := (extract(HOUR from l_end_date_ts));
1346 l_end_min := (extract(MINUTE from l_end_date_ts));
1347
1348 -- Nullify the visit record before populating it
1349 l_visit_rec := null;
1350
1351 -- Populate all the visit attributes in the visit record l_visit_rec
1352 SELECT NAME INTO l_visit_rec.UNIT_NAME FROM AHL_UNIT_CONFIG_HEADERS
1353 WHERE csi_item_instance_id= l_primary_ue_rec.csi_item_instance_id;
1354 l_visit_rec.VISIT_TYPE_CODE := l_primary_ue_rec.mr_visit_type_code;
1355 l_visit_rec.VISIT_NAME := 'Auto-Visit';
1356 l_visit_rec.ORGANIZATION_ID := l_maintenance_org_id;
1357 l_visit_rec.DEPARTMENT_ID := l_maintenance_dept_id;
1358 l_visit_rec.START_DATE := trunc(l_start_date);
1359 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1360 --l_visit_rec.START_HOUR := to_number(trunc(l_start_hour));
1361 --l_visit_rec.START_MIN := to_number(trunc (l_start_min));
1362 l_visit_rec.START_HOUR := l_start_hour;
1363 l_visit_rec.START_MIN := l_start_min;
1364 l_visit_rec.PLAN_END_DATE := trunc(l_end_date);
1365 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1366 --l_visit_rec.PLAN_END_HOUR := to_number(trunc(l_end_hour));
1367 --l_visit_rec.PLAN_END_MIN := to_number(trunc (l_end_min));
1368 l_visit_rec.PLAN_END_HOUR := l_end_hour;
1369 l_visit_rec.PLAN_END_MIN := l_end_min;
1370
1371 l_visit_rec.VISIT_CREATE_TYPE := 'PLANNING';
1372
1373 --PRAKKUM :: 09/03/2012 :: Bug 13797431 -- Added Log Messages
1374 IF (l_log_statement >= l_log_current_level) THEN
1375 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'Visit Details ..');
1376 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.VISIT_TYPE_CODE --@>'|| l_visit_rec.VISIT_TYPE_CODE);
1377 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.VISIT_NAME --@>'|| l_visit_rec.VISIT_NAME);
1378 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.UNIT_NAME --@>'|| l_visit_rec.UNIT_NAME);
1379 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.ORGANIZATION_ID --@>'|| l_visit_rec.ORGANIZATION_ID);
1380 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.DEPARTMENT_ID --@>'|| l_visit_rec.DEPARTMENT_ID);
1381 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'Computed Start Date --@>'|| to_char(l_start_date,'DD-MON-YY hh24:mi:ss'));
1382 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.START_DATE --@>'|| to_char(l_visit_rec.START_DATE,'DD-MON-YY hh24:mi:ss'));
1383 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.START_HOUR --@>'|| l_visit_rec.START_HOUR);
1384 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.START_MIN --@>'|| l_visit_rec.START_MIN);
1385 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'Computed End Date --@>'|| to_char(l_end_date,'DD-MON-YY hh24:mi:ss'));
1386 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.PLAN_END_DATE --@>'|| to_char(l_visit_rec.PLAN_END_DATE,'DD-MON-YY hh24:mi:ss'));
1387 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.PLAN_END_HOUR --@>'|| l_visit_rec.PLAN_END_HOUR);
1388 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.PLAN_END_MIN --@>'|| l_visit_rec.PLAN_END_MIN);
1389 END IF;
1390
1391 -- Now create the primary visit
1392 IF (l_log_statement >= l_log_current_level) THEN
1393 fnd_log.string
1394 ( l_log_statement,
1395 L_DEBUG_KEY,
1396 'Before calling AHL_VWP_VISITS_PVT.Create_Visit');
1397 END IF;
1398 AHL_VWP_VISITS_PVT.Create_Visit (
1399 p_api_version => l_api_version,
1400 p_init_msg_list => Fnd_Api.g_false,
1401 p_commit => Fnd_Api.g_false,
1402 p_validation_level => p_validation_level,
1403 p_module_type => 'API',
1404 p_x_visit_rec => l_visit_rec,
1405 x_return_status => l_return_status,
1406 x_msg_count => l_msg_count,
1407 x_msg_data => l_msg_data
1408 );
1409 IF (l_log_statement >= l_log_current_level) THEN
1410 fnd_log.string(l_log_statement,
1411 L_DEBUG_KEY,
1412 'After calling Create_Visit l_return_status= '||l_return_status
1413 ||', visit id: '||l_visit_rec.visit_id);
1414 END IF;
1415
1416 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1417 x_msg_count := FND_MSG_PUB.count_msg;
1418 IF (l_log_statement >= l_log_current_level) THEN
1419 fnd_log.string(l_log_statement,
1420 L_DEBUG_KEY,
1421 'Errors from Create_Visit. Message count: ' || x_msg_count);
1422 END IF;
1423 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1424 RAISE FND_API.G_EXC_ERROR;
1425 ELSE
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427 END IF;
1428 -- Added for logging the visit numbers :: SATRAJEN
1429 ELSE
1430 l_visit_number := NULL;
1431 OPEN get_visit_number(l_visit_rec.visit_id);
1432 FETCH get_visit_number INTO l_visit_number;
1433 CLOSE get_visit_number;
1434 fnd_file.put_line(fnd_file.log, l_visit_number);
1435 -- End of logging
1436 END IF; -- Return Status is not Success
1437
1438
1439 IF (l_log_statement >= l_log_current_level) THEN
1440 fnd_log.string
1441 ( l_log_statement,
1442 L_DEBUG_KEY,
1443 'Before calling AHL_VWP_TASKS_PVT.Create_PUP_Tasks, l_visit_rec.visit_id = '
1444 ||l_visit_rec.visit_id ||', unit_effectivity_id= '||l_primary_ue_rec.unit_effectivity_id);
1445 END IF;
1446 -- Now after the primary visit is created, associate the corresponding primary UE to the visit created
1447 l_task_rec(1).visit_id := l_visit_rec.visit_id ;
1448 l_task_rec(1).unit_effectivity_id := l_primary_ue_rec.unit_effectivity_id;
1449 l_task_rec(1).task_type_code := 'PLANNED';
1450 AHL_VWP_TASKS_PVT.Create_PUP_Tasks(
1451 p_api_version => L_API_VERSION,
1452 p_init_msg_list => Fnd_Api.g_false,
1453 p_commit => Fnd_Api.g_false,
1454 p_validation_level => Fnd_Api.g_valid_level_full,
1455 p_module_type => 'API',
1456 p_x_task_tbl => l_task_rec,
1457 x_return_status => l_return_status,
1458 x_msg_count => l_msg_count,
1459 x_msg_data => l_msg_data);
1460 IF (l_log_statement >= l_log_current_level) THEN
1461 fnd_log.string(l_log_statement,
1462 L_DEBUG_KEY,
1463 'After calling AHL_VWP_TASKS_PVT.Create_PUP_Tasks l_return_status= '||l_return_status);
1464 END IF;
1465
1466 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1467 x_msg_count := FND_MSG_PUB.count_msg;
1468 IF (l_log_statement >= l_log_current_level) THEN
1469 fnd_log.string(l_log_statement,
1470 L_DEBUG_KEY,
1474 RAISE FND_API.G_EXC_ERROR;
1471 'Errors from Create_PUP_Tasks. Message count: ' || x_msg_count);
1472 END IF;
1473 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1475 ELSE
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477 END IF;
1478 END IF; -- Return Status is not Success
1482 Add_Planned_MRs (
1479 -- Start of change SATRAJEN :: Bug 16048246 : To call Add_Planned_MRs when ever a Primary visit gets created.:: 28-12-2012
1480 -- Calling ADD_PLANNED_MRs to associate UEs.
1481 IF p_add_plan_flag = 'Y' THEN
1483 p_api_version => 1.0,
1484 p_init_msg_list => FND_API.G_FALSE,
1485 p_commit => FND_API.G_FALSE,
1486 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1487 p_visit_id => l_visit_rec.visit_id,
1488 p_snapshot_id => p_snapshot_id,
1489 x_return_status => l_return_status,
1490 x_msg_count => l_msg_count,
1491 x_msg_data => l_msg_data
1492 );
1493 IF (l_log_statement >= l_log_current_level) THEN
1494 fnd_log.string(l_log_statement,
1495 L_DEBUG_KEY,
1496 'After calling Add_Planned_MRs l_return_status= '||l_return_status);
1497 END IF;
1498 -- l_return_status = 'V' then number of UEs for the visit is 0.
1499 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS AND l_return_status <> 'V') THEN
1500 x_msg_count := FND_MSG_PUB.count_msg;
1501 IF (l_log_statement >= l_log_current_level) THEN
1502 fnd_log.string(l_log_statement,
1503 L_DEBUG_KEY,
1504 'Errors from Add_Planned_MRs. Message count: ' || x_msg_count);
1505 END IF;
1506 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1507 RAISE FND_API.G_EXC_ERROR;
1508 ELSE
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1510 END IF;
1511 ELSE
1512 l_requery_flag := 'Y';
1513 END IF; -- Return Status is not Success
1514 END IF; -- p_add_plan_flag = 'Y'
1515 -- End of Change SATRAJEN :: Bug 16048246 : To call Add_Planned_MRs when ever a Primary visit gets created.:: 28-12-2012
1516 END IF; -- IF l_operating_org_id IS NOT NULL THEN
1517 END LOOP; --end loop for UEs
1518 -- SATRAJEN :: Bug 16048246 : To close the cursor get_primary_ues in case its open after the looping. ( Last record turns out be l_requery_flag := 'N' ):: 28-12-2012
1519 IF get_primary_ues%ISOPEN THEN
1520 CLOSE get_primary_ues;
1521 END IF;
1522 END LOOP; --loop for MCs
1523 END LOOP; --loop for program subtypes
1524 ELSE -- IF l_count > 0
1525 -- If there are no program subtype for te given program type, then no need to fetch the subtype
1526 -- Also pass null as program subtype to get_primary_ues so that all the subtypes will be considered
1527 FOR l_mc_rec IN get_mc_ids( p_snapshot_id ) LOOP
1528 -- SATRAJEN :: Changes Start for Bug 16048246 : To call Add_Planned_MRs when ever a Primary visit gets created.:: 28-12-2012
1529 -- FOR l_primary_ue_rec IN get_primary_ues( l_mc_rec.mc_id, l_prog_typ_rec.PROGRAM_TYPE_CODE, NULL, dates_rec.from_date, dates_rec.to_date ) LOOP
1530 l_requery_flag := 'Y';
1531 LOOP
1532 -- Checking the l_fetch_flag as 'Y' in order to requery the list of UEs that are to be processed.(As Add planned MRs would have associated some UEs to visits)
1533 IF l_requery_flag = 'Y' THEN
1534 -- Cursor get_primary_ues has to be closed inorder to requery the new list of UEs.
1535 IF get_primary_ues%ISOPEN THEN
1536 CLOSE get_primary_ues;
1537 END IF;
1538 OPEN get_primary_ues(l_mc_rec.mc_id, l_prog_typ_rec.PROGRAM_TYPE_CODE, NULL, dates_rec.from_date, dates_rec.to_date);
1539 FETCH get_primary_ues INTO l_primary_ue_rec;
1540 l_requery_flag := 'N';
1541 ELSE -- l_requery_flag = 'N'. Add_planned_MRs was not called. So No need to requery, but fetch the next record.
1542 FETCH get_primary_ues INTO l_primary_ue_rec;
1543 END IF;
1544 EXIT WHEN get_primary_ues%NOTFOUND;
1545 -- SATRAJEN :: END OF Changes for Bug 16048246:: 28-12-2012
1546
1547 OPEN get_ue_operating_org(l_primary_ue_rec.unit_effectivity_id);
1548 FETCH get_ue_operating_org INTO l_operating_org_id;
1549 CLOSE get_ue_operating_org;
1550 IF l_operating_org_id IS NOT NULL THEN
1551 -- Now call Validate_MR_Operating_Org to get the organization and department of the primary visit
1552 IF (l_log_statement >= l_log_current_level) THEN
1553 fnd_log.string
1554 ( l_log_statement,
1555 L_DEBUG_KEY,
1556 'Before calling Validate_MR_Operating_Org, mr_header_id= '||l_primary_ue_rec.mr_header_id||', operating_org_id= '||l_operating_org_id);
1557 END IF;
1558 Validate_MR_Operating_Org (
1559 p_api_version => l_api_version,
1560 p_init_msg_list => Fnd_Api.g_false,
1561 p_commit => Fnd_Api.g_false,
1562 p_validation_level => p_validation_level,
1563 p_mr_header_id => l_primary_ue_rec.mr_header_id,
1564 p_operating_org_id => l_operating_org_id,
1565 x_maintenance_org_id => l_maintenance_org_id,
1566 x_maintenance_dept_id => l_maintenance_dept_id,
1567 x_return_status => l_return_status,
1568 x_msg_count => l_msg_count,
1569 x_msg_data => l_msg_data
1570 );
1571 IF (l_log_statement >= l_log_current_level) THEN
1572 fnd_log.string(l_log_statement,
1573 L_DEBUG_KEY,
1574 'After calling Validate_MR_Operating_Org l_return_status= '||l_return_status);
1575 END IF;
1576
1577 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1578 x_msg_count := FND_MSG_PUB.count_msg;
1579 IF (l_log_statement >= l_log_current_level) THEN
1580 fnd_log.string(l_log_statement,
1581 L_DEBUG_KEY,
1582 'Errors from Validate_MR_Operating_Org. Message count: ' || x_msg_count);
1583 END IF;
1584 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1585 RAISE FND_API.G_EXC_ERROR;
1586 ELSE
1587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1588 END IF;
1589 END IF; -- Return Status is not Success
1590
1591 -- PRAKKUM :: Bug 13589168 :: 01/16/2012 :: START
1592 IF (l_log_statement >= l_log_current_level) THEN
1593 fnd_log.string(l_log_statement,
1594 L_DEBUG_KEY,
1595 'l_maintenance_org_id ** l_maintenance_dept_id: ' || l_maintenance_org_id || ' ** '|| l_maintenance_dept_id);
1596 END IF;
1597 IF l_maintenance_org_id is NULL or l_maintenance_dept_id is NULL THEN
1598 CONTINUE;
1599 END IF;
1600 -- PRAKKUM :: Bug 13589168 :: 01/16/2012 :: END
1601
1602 -- Calculate visits start and end date and time based on department shift and UE due date
1603
1604 l_start_date := AHL_VWP_TIMES_PVT.Compute_Date(l_primary_ue_rec.due_date, l_maintenance_dept_id,0);
1605 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1606 l_start_date_ts := cast(l_start_date as timestamp);
1607 --l_start_hour := (l_start_date - trunc(l_start_date) )*24;
1608 --l_start_min := (l_start_hour - trunc (l_start_hour))*60;
1609 l_start_hour := (extract(HOUR from l_start_date_ts));
1610 l_start_min := (extract(MINUTE from l_start_date_ts));
1611
1612 l_end_date := AHL_VWP_TIMES_PVT.Compute_Date(l_primary_ue_rec.due_date, l_maintenance_dept_id, l_primary_ue_rec.estimated_duration);
1613 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1614 l_end_date_ts := cast(l_end_date as timestamp);
1615 --l_end_hour := (l_end_date - trunc(l_end_date) )*24;
1616 --l_end_min := (l_end_hour - trunc (l_end_hour))*60;
1617 l_end_hour := (extract(HOUR from l_end_date_ts));
1618 l_end_min := (extract(MINUTE from l_end_date_ts));
1619
1620 -- Nullify the visit record before populating it
1621 l_visit_rec := null;
1622
1623 -- Populate all the visit attributes
1624 SELECT NAME INTO l_visit_rec.UNIT_NAME FROM AHL_UNIT_CONFIG_HEADERS
1625 WHERE csi_item_instance_id= l_primary_ue_rec.csi_item_instance_id;
1626 l_visit_rec.VISIT_TYPE_CODE := l_primary_ue_rec.mr_visit_type_code;
1627 l_visit_rec.VISIT_NAME := 'Auto-Visit';
1628 l_visit_rec.ORGANIZATION_ID := l_maintenance_org_id;
1629 l_visit_rec.DEPARTMENT_ID := l_maintenance_dept_id;
1630 l_visit_rec.START_DATE := trunc(l_start_date);
1631 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1632 --l_visit_rec.START_HOUR := to_number(trunc(l_start_hour));
1633 --l_visit_rec.START_MIN := to_number(trunc (l_start_min));
1634 l_visit_rec.START_HOUR := l_start_hour;
1635 l_visit_rec.START_MIN := l_start_min;
1636 l_visit_rec.PLAN_END_DATE := trunc(l_end_date);
1637 --PRAKKUM :: 09/03/2012 :: Bug 13797431
1638 --l_visit_rec.PLAN_END_HOUR := to_number(trunc(l_end_hour));
1639 --l_visit_rec.PLAN_END_MIN := to_number(trunc (l_end_min));
1640 l_visit_rec.PLAN_END_HOUR := l_end_hour;
1641 l_visit_rec.PLAN_END_MIN := l_end_min;
1642
1643 l_visit_rec.VISIT_CREATE_TYPE := 'PLANNING';
1644
1645 --PRAKKUM :: 09/03/2012 :: Bug 13797431 -- Added Log Messages
1646 IF (l_log_statement >= l_log_current_level) THEN
1647 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'Visit Details ..');
1648 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.VISIT_TYPE_CODE --@>'|| l_visit_rec.VISIT_TYPE_CODE);
1649 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.VISIT_NAME --@>'|| l_visit_rec.VISIT_NAME);
1650 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.UNIT_NAME --@>'|| l_visit_rec.UNIT_NAME);
1651 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.ORGANIZATION_ID --@>'|| l_visit_rec.ORGANIZATION_ID);
1652 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.DEPARTMENT_ID --@>'|| l_visit_rec.DEPARTMENT_ID);
1653 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'Computed Start Date --@>'|| to_char(l_start_date,'DD-MON-YY hh24:mi:ss'));
1654 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.START_DATE --@>'|| to_char(l_visit_rec.START_DATE,'DD-MON-YY hh24:mi:ss'));
1655 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.START_HOUR --@>'|| l_visit_rec.START_HOUR);
1656 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.START_MIN --@>'|| l_visit_rec.START_MIN);
1657 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'Computed End Date --@>'|| to_char(l_end_date,'DD-MON-YY hh24:mi:ss'));
1658 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.PLAN_END_DATE --@>'|| to_char(l_visit_rec.PLAN_END_DATE,'DD-MON-YY hh24:mi:ss'));
1659 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.PLAN_END_HOUR --@>'|| l_visit_rec.PLAN_END_HOUR);
1660 fnd_log.string( l_log_statement, L_DEBUG_KEY, 'l_visit_rec.PLAN_END_MIN --@>'|| l_visit_rec.PLAN_END_MIN);
1661 END IF;
1662
1663 IF (l_log_statement >= l_log_current_level) THEN
1664 fnd_log.string
1665 ( l_log_statement,
1666 L_DEBUG_KEY,
1667 'Before calling AHL_VWP_VISITS_PVT.Create_Visit');
1668 END IF;
1669 AHL_VWP_VISITS_PVT.Create_Visit (
1670 p_api_version => l_api_version,
1671 p_init_msg_list => Fnd_Api.g_false,
1672 p_commit => Fnd_Api.g_false,
1673 p_validation_level => p_validation_level,
1674 p_module_type => 'API',
1675 p_x_visit_rec => l_visit_rec,
1676 x_return_status => l_return_status,
1677 x_msg_count => l_msg_count,
1678 x_msg_data => l_msg_data
1679 );
1680 IF (l_log_statement >= l_log_current_level) THEN
1681 fnd_log.string(l_log_statement,
1682 L_DEBUG_KEY,
1683 'After calling Create_Visit l_return_status= '||l_return_status
1684 ||', visit id: '||l_visit_rec.visit_id);
1685 END IF;
1686
1687 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1688 x_msg_count := FND_MSG_PUB.count_msg;
1689 IF (l_log_statement >= l_log_current_level) THEN
1690 fnd_log.string(l_log_statement,
1691 L_DEBUG_KEY,
1692 'Errors from Create_Visit. Message count: ' || x_msg_count);
1693 END IF;
1694 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1695 RAISE FND_API.G_EXC_ERROR;
1696 ELSE
1697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1698 END IF;
1699 -- Added for logging the visit numbers :: SATRAJEN
1700 ELSE
1701 l_visit_number := NULL;
1702 OPEN get_visit_number(l_visit_rec.visit_id);
1703 FETCH get_visit_number INTO l_visit_number;
1704 CLOSE get_visit_number;
1705 fnd_file.put_line(fnd_file.log, l_visit_number);
1706 -- End of logging
1707 END IF; -- Return Status is not Success
1708
1709 IF (l_log_statement >= l_log_current_level) THEN
1710 fnd_log.string
1711 ( l_log_statement,
1712 L_DEBUG_KEY,
1713 'Before calling AHL_VWP_TASKS_PVT.Create_PUP_Tasks, l_visit_rec.visit_id = '
1714 ||l_visit_rec.visit_id ||', unit_effectivity_id= '||l_primary_ue_rec.unit_effectivity_id);
1715 END IF;
1716
1717 l_task_rec(1).visit_id := l_visit_rec.visit_id ;
1718 l_task_rec(1).unit_effectivity_id := l_primary_ue_rec.unit_effectivity_id;
1719 l_task_rec(1).task_type_code := 'PLANNED';
1720 AHL_VWP_TASKS_PVT.Create_PUP_Tasks(
1721 p_api_version => L_API_VERSION,
1722 p_init_msg_list => Fnd_Api.g_false,
1723 p_commit => Fnd_Api.g_false,
1724 p_validation_level => Fnd_Api.g_valid_level_full,
1725 p_module_type => 'API',
1726 p_x_task_tbl => l_task_rec,
1727 x_return_status => l_return_status,
1728 x_msg_count => l_msg_count,
1729 x_msg_data => l_msg_data);
1730 IF (l_log_statement >= l_log_current_level) THEN
1731 fnd_log.string(l_log_statement,
1732 L_DEBUG_KEY,
1736 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1733 'After calling AHL_VWP_TASKS_PVT.Create_PUP_Tasks l_return_status= '||l_return_status);
1734 END IF;
1735
1737 x_msg_count := FND_MSG_PUB.count_msg;
1738 IF (l_log_statement >= l_log_current_level) THEN
1739 fnd_log.string(l_log_statement,
1740 L_DEBUG_KEY,
1741 'Errors from Create_PUP_Tasks. Message count: ' || x_msg_count);
1742 END IF;
1743 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1744 RAISE FND_API.G_EXC_ERROR;
1745 ELSE
1746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1747 END IF;
1748 END IF; -- Return Status is not Success
1749 -- Start of change SATRAJEN :: Bug 16048246 : To call Add_Planned_MRs when ever a Primary visit gets created.:: 28-12-2012
1750 -- Calling ADD_PLANNED_MRs to associate UEs.
1751 IF p_add_plan_flag = 'Y' THEN
1752 Add_Planned_MRs (
1753 p_api_version => 1.0,
1754 p_init_msg_list => FND_API.G_FALSE,
1755 p_commit => FND_API.G_FALSE,
1756 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1757 p_visit_id => l_visit_rec.visit_id,
1758 p_snapshot_id => p_snapshot_id,
1759 x_return_status => l_return_status,
1760 x_msg_count => l_msg_count,
1761 x_msg_data => l_msg_data
1762 );
1763 IF (l_log_statement >= l_log_current_level) THEN
1764 fnd_log.string(l_log_statement,
1765 L_DEBUG_KEY,
1766 'After calling Add_Planned_MRs l_return_status= '||l_return_status);
1767 END IF;
1768 -- l_return_status = 'V' then number of UEs for the visit is 0.
1769 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS AND l_return_status <> 'V') THEN
1770 x_msg_count := FND_MSG_PUB.count_msg;
1771 IF (l_log_statement >= l_log_current_level) THEN
1772 fnd_log.string(l_log_statement,
1773 L_DEBUG_KEY,
1774 'Errors from Add_Planned_MRs. Message count: ' || x_msg_count);
1775 END IF;
1776 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1777 RAISE FND_API.G_EXC_ERROR;
1778 ELSE
1779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1780 END IF;
1781 ELSE
1782 l_requery_flag := 'Y';
1783 END IF; -- Return Status is not Success
1784 END IF; -- p_add_plan_flag = 'Y'
1785 -- End of Change SATRAJEN :: Bug 16048246 : To call Add_Planned_MRs when ever a Primary visit gets created. :: 28-12-2012
1786 END IF; --l_operating_org_id IS NOT NULL THEN
1787
1788 END LOOP; -- loop for UEs
1789 -- SATRAJEN :: Bug 16048246: To close the cursor get_primary_ues in case its open after the looping. ( Last record turns out be l_fetch_flag := 'N' ):: 28-12-2012
1790 IF get_primary_ues%ISOPEN THEN
1791 CLOSE get_primary_ues;
1792 END IF;
1793 END LOOP; --loop for MC
1794 END IF; -- IF l_count > 0
1795 END LOOP; -- loop for program type
1796 -- Added for logging visit numbers :: SATRAJEN
1797 fnd_file.put_line(fnd_file.log, '** END of Visits Created ** ');
1798
1799
1800 ---------------------------End of Body-------------------------------------
1801 -- END of API body.
1802 -- Standard check of p_commit.
1803
1804 IF Fnd_Api.To_Boolean (p_commit) THEN
1805 COMMIT WORK;
1806 END IF;
1807
1808 Fnd_Msg_Pub.count_and_get(
1809 p_encoded => Fnd_Api.g_false,
1810 p_count => x_msg_count,
1811 p_data => x_msg_data
1812 );
1813
1814 IF (l_log_procedure >= l_log_current_level)THEN
1815 fnd_log.string
1816 (
1817 l_log_procedure,
1818 'ahl.plsql.AHL_VWP_PRIM_VSTS_PVT.Create_Primary_Visits.end',
1819 'At the end of PLSQL procedure'
1820 );
1821 END IF;
1822
1823 EXCEPTION
1824
1825 WHEN FND_API.G_EXC_ERROR THEN
1826
1827 x_return_status := FND_API.G_RET_STS_ERROR;
1828 ROLLBACK TO Create_Primary_Visits_pvt;
1829 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1830 p_data => x_msg_data,
1831 p_encoded => fnd_api.g_false);
1832
1833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1835 ROLLBACK TO Create_Primary_Visits_pvt;
1836 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1837 p_data => x_msg_data,
1838 p_encoded => fnd_api.g_false);
1839
1840 WHEN OTHERS THEN
1841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1842 ROLLBACK TO Create_Primary_Visits_pvt;
1843
1844 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1845 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1846 p_procedure_name => 'Create_Primary_Visits',
1847 p_error_text => SUBSTR(SQLERRM,1,500));
1848 END IF;
1849
1850 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1851 p_data => x_msg_data,
1852 p_encoded => fnd_api.g_false);
1853 END Create_Primary_Visits;
1854
1855 --------------------------------------------------------------------
1856 -- PROCEDURE
1857 -- Process_Primary_visits
1858 --
1859 -- PURPOSE
1860 -- Made as an executable for the concurrent program for processing primary visits
1861 -- Process_Primary_visits Parameters :
1862 -- p_snapshot_id IN NUMBER
1863 -- errbuf OUT VARCHAR2 Required
1864 -- Defines in pl/sql to store procedure to get error messages into log file
1865 -- retcode OUT NUMBER Required
1866 -- To get the status of the concurrent program
1867
1868 --------------------------------------------------------------------
1869 PROCEDURE Process_Primary_visits(
1870 errbuf OUT NOCOPY VARCHAR2,
1871 retcode OUT NOCOPY NUMBER,
1872 p_api_version IN NUMBER,
1873 p_snapshot_id IN NUMBER
1874 )
1875 IS
1876
1877
1878 -- Local variables section
1879 l_msg_count NUMBER;
1880 l_msg_data VARCHAR2(2000);
1881 l_return_status VARCHAR2(1);
1882 l_api_version NUMBER := 1.0;
1883 l_api_name VARCHAR2(30) := 'Process_Primary_visits';
1884 l_err_msg VARCHAR2(2000);
1885 l_msg_index_out NUMBER;
1886 l_create_visit_flag VARCHAR2(1);
1887 l_add_planned_reqs_flag VARCHAR2(1);
1888 l_cancel_visit_flag VARCHAR2(1);
1889
1890 BEGIN
1891
1892 -- Standard start of API savepoint
1893 SAVEPOINT Process_Primary_visits;
1894
1895 -- 1. Initialize error message stack by default
1896 FND_MSG_PUB.Initialize;
1897
1898 -- Standard call to check for call compatibility
1899 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1900 retcode := 2;
1901 errbuf := FND_MSG_PUB.Get;
1902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1903 END IF;
1904
1905 -- 2. Dump all input parameters
1906 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
1907 fnd_file.put_line(fnd_file.log, 'p_snapshot_id -> '|| p_snapshot_id);
1908 fnd_file.put_line(fnd_file.log, 'fnd_global.USER_ID -> '|| fnd_global.USER_ID);
1909 fnd_file.put_line(fnd_file.log, 'fnd_global.RESP_ID -> '||fnd_global.RESP_ID);
1910 fnd_file.put_line(fnd_file.log, 'fnd_global.PROG_APPL_ID -> '|| fnd_global.PROG_APPL_ID);
1911 fnd_file.put_line(fnd_file.log, 'mo_global.get_current_org_id -> '|| mo_global.get_current_org_id());
1912
1913 SELECT create_visit_flag, add_planned_reqs_flag, cancel_visit_flag
1914 INTO l_create_visit_flag, l_add_planned_reqs_flag, l_cancel_visit_flag
1915 FROM ahl_autovst_snpsht_hdr WHERE snapshot_id = p_snapshot_id;
1916
1917 fnd_file.put_line(fnd_file.log, 'l_create_visit_flag -> '|| l_create_visit_flag);
1918 fnd_file.put_line(fnd_file.log, 'l_add_planned_reqs_flag -> '|| l_add_planned_reqs_flag);
1919 fnd_file.put_line(fnd_file.log, 'l_cancel_visit_flag -> '|| l_cancel_visit_flag);
1920
1921
1922 IF NVL(l_cancel_visit_flag,'N') = 'Y' THEN
1923 fnd_file.put_line(fnd_file.log, 'before calling Cancel_Visits');
1924 Cancel_Visits (
1925 p_api_version => 1.0,
1926 p_init_msg_list => FND_API.G_FALSE,
1927 p_commit => FND_API.G_FALSE,
1928 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1929 p_snapshot_id => p_snapshot_id,
1930 x_return_status => l_return_status,
1931 x_msg_count => l_msg_count,
1932 x_msg_data => l_msg_data
1933 );
1934
1935 l_msg_count := FND_MSG_PUB.Count_Msg;
1936 IF (l_msg_count > 0) THEN
1937 fnd_file.put_line(fnd_file.log, 'Following error occured during the call to Cancel_Visits..');
1938 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1939 RAISE FND_API.G_EXC_ERROR;
1940 ELSE
1941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1942 END IF;
1943 ELSE
1944 COMMIT WORK;
1945 END IF;
1946
1947 END IF;
1948
1949 -- SATRAJEN : Bug 16048246 : Procedure Call based on the number of options selected.:: Added p_add_plan_flag parameter. :: 28-12-2012
1950 IF NVL(l_create_visit_flag,'N') = 'Y' THEN
1951 fnd_file.put_line(fnd_file.log, 'before calling Create_Primary_Visits');
1952 Create_Primary_Visits (
1953 p_api_version => 1.0,
1954 p_init_msg_list => FND_API.G_FALSE,
1955 p_commit => FND_API.G_FALSE,
1956 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1957 p_snapshot_id => p_snapshot_id,
1958 p_add_plan_flag => NVL(l_add_planned_reqs_flag,'N'),
1959 x_return_status => l_return_status,
1960 x_msg_count => l_msg_count,
1961 x_msg_data => l_msg_data
1962 );
1963
1964 l_msg_count := FND_MSG_PUB.Count_Msg;
1965 IF (l_msg_count > 0) THEN
1966 fnd_file.put_line(fnd_file.log, 'Following error occured during the call to Create_Primary_Visits..');
1967 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1968 RAISE FND_API.G_EXC_ERROR;
1969 ELSE
1970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1971 END IF;
1972 ELSE
1973 COMMIT WORK;
1974 END IF;
1975 END IF;
1976
1977 -- SATRAJEN : Bug 16048246 : Procedure Call based on the number of options selected. :: 28-12-2012
1978 -- IF NVL(l_add_planned_reqs_flag,'N') = 'Y' THEN
1979 IF ((NVL(l_add_planned_reqs_flag,'N') = 'Y') AND (NVL(l_create_visit_flag,'N') = 'N')) THEN
1980 fnd_file.put_line(fnd_file.log, 'before calling Add_Planned_MRs');
1981 Add_Planned_MRs (
1982 p_api_version => 1.0,
1983 p_init_msg_list => FND_API.G_FALSE,
1984 p_commit => FND_API.G_FALSE,
1985 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1986 p_snapshot_id => p_snapshot_id,
1987 x_return_status => l_return_status,
1988 x_msg_count => l_msg_count,
1989 x_msg_data => l_msg_data
1990 );
1991
1992 l_msg_count := FND_MSG_PUB.Count_Msg;
1993 IF (l_msg_count > 0) THEN
1994 fnd_file.put_line(fnd_file.log, 'Following error occured during the call to Add_Planned_MRs..');
1995 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1996 RAISE FND_API.G_EXC_ERROR;
1997 ELSE
1998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1999 END IF;
2000 ELSE
2001 COMMIT WORK;
2002 END IF;
2003 END IF;
2004
2005 EXCEPTION
2006 WHEN FND_API.G_EXC_ERROR THEN
2007 ROLLBACK TO Process_Primary_visits;
2008 retcode := 2;
2009 FOR i IN 1..l_msg_count
2010 LOOP
2011 fnd_msg_pub.get( p_msg_index => i,
2012 p_encoded => FND_API.G_FALSE,
2013 p_data => l_err_msg,
2014 p_msg_index_out => l_msg_index_out);
2015
2016 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
2017 END LOOP;
2018
2019
2020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2021 ROLLBACK TO Process_Primary_visits;
2022 retcode := 2;
2023 l_msg_count := Fnd_Msg_Pub.count_msg;
2024 FOR i IN 1..l_msg_count
2025 LOOP
2026 fnd_msg_pub.get( p_msg_index => i,
2027 p_encoded => FND_API.G_FALSE,
2028 p_data => l_err_msg,
2029 p_msg_index_out => l_msg_index_out);
2030
2031 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
2032 END LOOP;
2033
2034
2035 WHEN OTHERS THEN
2036 ROLLBACK TO Process_Primary_visits;
2037 retcode := 2;
2038 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2039 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2040 p_procedure_name => 'Process_Primary_visits',
2041 p_error_text => SUBSTR(SQLERRM,1,500));
2042 END IF;
2043 l_msg_count := Fnd_Msg_Pub.count_msg;
2044 FOR i IN 1..l_msg_count
2045 LOOP
2046 fnd_msg_pub.get( p_msg_index => i,
2047 p_encoded => FND_API.G_FALSE,
2048 p_data => l_err_msg,
2049 p_msg_index_out => l_msg_index_out);
2050
2051 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_err_msg);
2052 END LOOP;
2053
2054
2055 END Process_Primary_visits;
2056
2057
2058 ---------------------------------------------------------------------------------------------------------------------------
2059
2060 END AHL_AVF_PRIM_VSTS_PVT;