1 PACKAGE BODY AHL_COMPLEX_MX_RWSC_PVT AS
2 /* $Header: AHLVRWSB.pls 120.0.12020000.5 2013/02/19 23:22:07 sikumar noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_COMPLEX_MX_RWSC_PVT';
5
6 -- Private Procedure
7 PROCEDURE get_uc_details(
8 p_unit_header_id IN NUMBER,
9 p_visit_id IN NUMBER,
10 x_uc_details_tbl OUT NOCOPY visit_ir_uc_details_tbl_type);
11
12 PROCEDURE get_instance_details(
13 p_item_instance_id IN NUMBER,
14 p_visit_id IN NUMBER,
15 x_instance_details_tbl OUT NOCOPY visit_ir_uc_details_tbl_type);
16
17 PROCEDURE Create_Instance_Routing (
18 p_visit_id IN NUMBER,
19 p_item_instance_id IN NUMBER,
20 p_inst_routing_code IN VARCHAR2,
21 x_inst_routing_id OUT NOCOPY NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2);
23
24 PROCEDURE Update_Instance_Routing (
25 p_inst_routing_id IN NUMBER,
26 p_object_version_number IN NUMBER,
27 p_inst_routing_code IN VARCHAR2,
28 x_return_status OUT NOCOPY VARCHAR2);
29
30 PROCEDURE Delete_Instance_Routing (
31 p_inst_routing_id IN NUMBER,
32 x_return_status OUT NOCOPY VARCHAR2);
33
34 PROCEDURE Process_Inst_Rtng_Notes(
35 p_visit_id IN NUMBER,
36 p_instance_id IN NUMBER,
37 p_old_inst_rtng_code IN VARCHAR2 := NULL,
38 p_new_inst_rtng_code IN VARCHAR2 := NULL,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2
42 ) ;
43
44 -- Private Functions
45 FUNCTION Get_Build_Goal_Per_For_Pos(p_current_instance_id NUMBER) RETURN NUMBER;
46
47 ------------------------------------------------------------------------------------------------------
48 -- Start of Comments
49 -- Procedure name : Get_visit_ir_uc_details
50 -- Type : Public
51 -- Function : This procedure will update the temparory table to show data in hGrid.
52 -- Since this table is global temporary with ON COMMIT DELETE ROWS,
53 -- Always call this method with p_commit value : FND_API.G_FALSE
54 -- Pre-reqs :
55 -- Parameters :
56 --
57 -- Get_visit_ir_uc_details Parameters
58 -- p_unit_header_id IN NUMBER
59 -- p_item_instance_id IN NUMBER
60 -- p_visit_id IN NUMBER
61 --
62 -- End of Comments
63 -------------------------------------------------------------------------------------------------------
64 PROCEDURE Get_visit_ir_uc_details
65 (
66 p_api_version IN NUMBER := 1.0,
67 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
68 p_commit IN VARCHAR2 := FND_API.G_FALSE,
69 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
70 p_default IN VARCHAR2 := FND_API.G_FALSE,
71 p_module_type IN VARCHAR2 := NULL,
72 p_unit_header_id IN NUMBER,
73 p_item_instance_id IN NUMBER,
74 p_visit_id IN NUMBER,
75 x_visit_ir_uc_details_tbl OUT NOCOPY visit_ir_uc_details_tbl_type,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2
79 )IS
80
81 l_api_name CONSTANT VARCHAR2(30) := 'Get_visit_ir_uc_details';
82 l_api_version CONSTANT NUMBER := 1.0;
83
84 CURSOR get_record_count IS
85 SELECT COUNT(1) from ahl_visit_ir_uc_details;
86
87 CURSOR is_visit_present(c_visit_id IN NUMBER) IS
88 SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE visit_id = c_visit_id;
89
90 CURSOR is_inst_present(c_instance_id IN NUMBER) IS
91 SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE root_instance_id = c_instance_id;
92
93 l_count NUMBER;
94 l_count_instance NUMBER;
95 l_count_visit NUMBER;
96
97 BEGIN
98 --Initialize API return status to success
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 --Standard call to check for call compatibility.
102 IF NOT FND_API.compatible_api_call(
103 l_api_version,
104 p_api_version,
105 l_api_name,
106 G_PKG_NAME)
107 THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 END IF;
110
111 --Initialize message list if p_init_msg_list is set to TRUE.
112 IF FND_API.to_boolean( p_init_msg_list ) THEN
113 FND_MSG_PUB.initialize;
114 END IF;
115
116 -- This table stores details of only one visit temporarily.
117 OPEN is_visit_present(p_visit_id);
118 FETCH is_visit_present INTO l_count_visit;
119 CLOSE is_visit_present;
120
121 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
123 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
124 'Records having Visit id ' || p_visit_id || ': ' || l_count_visit);
125 END IF;
126
127 /* This is global temparary table with ON COMMIT DELETE ROWS, hence its all values will be in sync with respective origin always-
128 If the visit in context is changed then we need to populate the temp table again.
129 If the visit in context is same and user has selected another unit/instance, retain the values in the table
130 There will be records in table only for one visit
131 */
132 IF l_count_visit = 0 THEN
133 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
135 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
136 'Deleting all rows in ahl_visit_ir_uc_details');
137 END IF;
138
139 -- even if table rows are zero it will not throw error.
140 DELETE FROM ahl_visit_ir_uc_details;
141
142 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
144 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
145 'all rows in ahl_visit_ir_uc_details deleted');
146 END IF;
147
148 END IF;
149
150 OPEN is_inst_present(p_item_instance_id);
151 FETCH is_inst_present INTO l_count_instance;
152 CLOSE is_inst_present;
153
154 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
155 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
156 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
157 'Records having inst id ' || p_item_instance_id || ': ' || l_count_instance);
158 END IF;
159
160 -- if p_item_instance_id is not present: table does not have record for this.
161 -- insert new row in the table.
162 IF l_count_instance = 0 THEN
163 IF(p_unit_header_id IS NOT NULL)THEN
164 get_uc_details(
165 p_unit_header_id => p_unit_header_id,
166 p_visit_id => p_visit_id,
167 x_uc_details_tbl => x_visit_ir_uc_details_tbl);
168 ELSIF(p_item_instance_id IS NOT NULL)THEN
169 get_instance_details(
170 p_item_instance_id => p_item_instance_id,
171 p_visit_id => p_visit_id,
172 x_instance_details_tbl => x_visit_ir_uc_details_tbl);
173 END IF;
174
175 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
176 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
177 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
178 'New row Count ' || x_visit_ir_uc_details_tbl.count);
179 END IF;
180
181 IF x_visit_ir_uc_details_tbl.FIRST IS NOT NULL THEN
182 FOR i IN x_visit_ir_uc_details_tbl.FIRST..x_visit_ir_uc_details_tbl.LAST LOOP
183 INSERT INTO ahl_visit_ir_uc_details
184 (
185 visit_ir_uc_id,
186 root_instance_id,
187 unit_header_id,
188 unit_name,
189 path_position_id,
190 relationship_id,
191 parent_rel_id,
192 curr_item_id,
193 curr_instance_id,
194 parent_instance_id,
195 curr_item_number,
196 curr_instance_number,
197 curr_serial_number,
198 position,
199 position_image,
200 item_group_id,
201 item_group_name,
202 reserved_serial_num,
203 available_parts,
204 unit_nha,
205 is_unit,
206 is_warranty_available,
207 mr_count,
208 task_count,
209 is_visit_available,
210 mc_header_id,
211 is_sb_rule_available,
212 route_dispositions_flag,
213 rd_rework_percent,
214 rd_replace_percent,
215 position_material_flag,
216 bg_position,
217 bg_instance,
218 inst_routing_id,
219 inst_routing_ovn,
220 visit_id,
221 inst_routing_code,
222 inst_routing_meaning,
223 inst_routing_flag,
224 qty_per_position,
225 installed_qty,
226 leaf_node_flag,
227 node_level,
228 hgrid_node_path
229 )VALUES
230 (
231 x_visit_ir_uc_details_tbl(i).visit_ir_uc_id,
232 x_visit_ir_uc_details_tbl(i).root_instance_id,
233 x_visit_ir_uc_details_tbl(i).unit_header_id,
234 x_visit_ir_uc_details_tbl(i).unit_name,
235 x_visit_ir_uc_details_tbl(i).path_position_id,
236 x_visit_ir_uc_details_tbl(i).relationship_id,
237 x_visit_ir_uc_details_tbl(i).parent_rel_id,
238 x_visit_ir_uc_details_tbl(i).curr_item_id,
239 x_visit_ir_uc_details_tbl(i).curr_instance_id,
240 x_visit_ir_uc_details_tbl(i).parent_instance_id,
241 x_visit_ir_uc_details_tbl(i).curr_item_number,
242 x_visit_ir_uc_details_tbl(i).curr_instance_number,
243 x_visit_ir_uc_details_tbl(i).curr_serial_number,
244 x_visit_ir_uc_details_tbl(i).position,
245 x_visit_ir_uc_details_tbl(i).position_image,
246 x_visit_ir_uc_details_tbl(i).item_group_id,
247 x_visit_ir_uc_details_tbl(i).item_group_name,
248 x_visit_ir_uc_details_tbl(i).reserved_serial_num,
249 x_visit_ir_uc_details_tbl(i).available_parts,
250 x_visit_ir_uc_details_tbl(i).unit_nha,
251 x_visit_ir_uc_details_tbl(i).is_unit,
252 x_visit_ir_uc_details_tbl(i).is_warranty_available,
253 x_visit_ir_uc_details_tbl(i).mr_count,
254 x_visit_ir_uc_details_tbl(i).task_count,
255 x_visit_ir_uc_details_tbl(i).is_visit_available,
256 x_visit_ir_uc_details_tbl(i).mc_header_id,
257 x_visit_ir_uc_details_tbl(i).is_sb_rule_available,
258 x_visit_ir_uc_details_tbl(i).route_dispositions_flag,
259 x_visit_ir_uc_details_tbl(i).rd_rework_percent,
260 x_visit_ir_uc_details_tbl(i).rd_replace_percent,
261 x_visit_ir_uc_details_tbl(i).position_material_flag,
262 x_visit_ir_uc_details_tbl(i).bg_position,
263 x_visit_ir_uc_details_tbl(i).bg_instance,
264 x_visit_ir_uc_details_tbl(i).inst_routing_id,
265 x_visit_ir_uc_details_tbl(i).inst_routing_ovn,
266 x_visit_ir_uc_details_tbl(i).visit_id,
267 x_visit_ir_uc_details_tbl(i).inst_routing_code,
268 x_visit_ir_uc_details_tbl(i).inst_routing_meaning,
269 x_visit_ir_uc_details_tbl(i).inst_routing_flag,
270 x_visit_ir_uc_details_tbl(i).qty_per_position,
271 x_visit_ir_uc_details_tbl(i).installed_qty,
272 x_visit_ir_uc_details_tbl(i).leaf_node_flag,
273 x_visit_ir_uc_details_tbl(i).node_level,
274 x_visit_ir_uc_details_tbl(i).hgrid_node_path
275 );
276 END LOOP;
277 END IF;
278
279 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
281 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
282 'Newly Inserted Rows' || x_visit_ir_uc_details_tbl.count);
283 END IF;
284
285 -- Update Build goal for Position
286 l_count := Get_Build_Goal_Per_For_Pos(p_item_instance_id);
287
288 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
289 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
290 'Update Done root build goal ' || l_count);
291 END IF;
292
293 END IF; -- l_count_instance check if
294
295 OPEN get_record_count;
296 FETCH get_record_count INTO l_count;
297 CLOSE get_record_count;
298
299 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
300 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
301 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
302 'Records in Table are ' || l_count);
303 END IF;
304
305 IF(p_commit = FND_API.G_TRUE) THEN
306 COMMIT WORK;
307 END IF;
308
309 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
310 x_msg_count := FND_MSG_PUB.count_msg;
311 IF x_msg_count > 0 THEN
312 RAISE FND_API.G_EXC_ERROR;
313 END IF;
314
315 -- Count and Get messages (optional)
316 FND_MSG_PUB.count_and_get(
317 p_encoded => FND_API.G_FALSE,
318 p_count => x_msg_count,
319 p_data => x_msg_data);
320
321 EXCEPTION
322 WHEN FND_API.G_EXC_ERROR THEN
323 x_return_status := FND_API.G_RET_STS_ERROR ;
324 FND_MSG_PUB.count_and_get(
325 p_encoded => FND_API.G_FALSE,
326 p_count => x_msg_count,
327 p_data => x_msg_data);
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
331 FND_MSG_PUB.count_and_get(
332 p_encoded => FND_API.G_FALSE,
333 p_count => x_msg_count,
334 p_data => x_msg_data);
335
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
339 THEN
340 FND_MSG_PUB.add_exc_msg(
341 p_pkg_name => G_PKG_NAME,
342 p_procedure_name => l_api_name,
343 p_error_text => SUBSTRB(SQLERRM,1,240));
344 END IF;
345 FND_MSG_PUB.count_and_get(
346 p_encoded => FND_API.G_FALSE,
347 p_count => x_msg_count,
348 p_data => x_msg_data);
349 END Get_visit_ir_uc_details;
350
351 ------------------------------------------------------------------------------------------------------
352 -- Start of Comments
353 -- Procedure name : Process_instance_routings
354 -- Type : Public
355 -- Function : This procedure will update the table AHL_VISIT_INST_ROUTINGS
356 -- OLD_INST_ROUTING_CODE params is old INST_ROUTING_CODE, used for update/delete case
357 -- Pre-reqs :
358 -- Parameters :
359 --
360 -- Get_visit_ir_uc_details Parameters
361 -- p_x_instance_routings_tbl IN OUT NOCOPY instance_routings_tbl_type
362 --
363 -- End of Comments
364 -------------------------------------------------------------------------------------------------------
365 PROCEDURE Process_instance_routings
366 (
367 p_api_version IN NUMBER := 1.0,
368 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
369 p_commit IN VARCHAR2 := FND_API.G_FALSE,
370 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
371 p_default IN VARCHAR2 := FND_API.G_FALSE,
372 p_module_type IN VARCHAR2 := NULL,
373 p_x_instance_routings_tbl IN OUT NOCOPY instance_routings_tbl_type,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2
377 )IS
378
379 l_api_name CONSTANT VARCHAR2(30) := 'Process_instance_routings';
380 l_api_version CONSTANT NUMBER := 1.0;
381 l_inst_routing_id NUMBER;
382 l_msg_count NUMBER;
383 l_msg_data VARCHAR2(2000);
384 l_return_status VARCHAR2(1);
385
386 BEGIN
387
388 SAVEPOINT Process_instance_routings;
389
390 --Initialize API return status to success
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392
393 --Standard call to check for call compatibility.
394 IF NOT FND_API.compatible_api_call(
395 l_api_version,
396 p_api_version,
397 l_api_name,
398 G_PKG_NAME)
399 THEN
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 --Initialize message list if p_init_msg_list is set to TRUE.
404 IF FND_API.to_boolean( p_init_msg_list ) THEN
405 FND_MSG_PUB.initialize;
406 END IF;
407
408 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
409 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
410 'Rows Count ' || p_x_instance_routings_tbl.count);
411 END IF;
412
413 IF p_x_instance_routings_tbl.FIRST IS NOT NULL THEN
414 FOR i IN p_x_instance_routings_tbl.FIRST..p_x_instance_routings_tbl.LAST LOOP
415 IF p_x_instance_routings_tbl(i).inst_routing_id IS NOT NULL THEN
416 IF p_x_instance_routings_tbl(i).inst_routing_code IS NOT NULL THEN
417 -- update record in table
418 Update_Instance_Routing (
419 p_inst_routing_id => p_x_instance_routings_tbl(i).inst_routing_id,
420 p_object_version_number => p_x_instance_routings_tbl(i).object_version_number,
421 p_inst_routing_code => p_x_instance_routings_tbl(i).inst_routing_code,
422 x_return_status => l_return_status
423 );
424 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
425 RAISE FND_API.G_EXC_ERROR;
426 END IF;
427 ELSE
428 -- delete record from table
429 Delete_Instance_Routing (
430 p_inst_routing_id => p_x_instance_routings_tbl(i).inst_routing_id,
431 x_return_status => l_return_status
432 );
433 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
434 RAISE FND_API.G_EXC_ERROR;
435 END IF;
436 END IF;
437 ELSE
438 -- Create New record in table
439 Create_Instance_Routing (
440 p_visit_id => p_x_instance_routings_tbl(i).visit_id,
441 p_item_instance_id => p_x_instance_routings_tbl(i).instance_id,
442 p_inst_routing_code => p_x_instance_routings_tbl(i).inst_routing_code,
443 x_inst_routing_id => l_inst_routing_id,
444 x_return_status => l_return_status
445 );
446 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449
450 --Assign out param
451 p_x_instance_routings_tbl(i).inst_routing_id := l_inst_routing_id;
452 END IF;
453
454 --call WO turnover notes procedure
455 Process_Inst_Rtng_Notes(
456 p_visit_id => p_x_instance_routings_tbl(i).visit_id,
457 p_instance_id => p_x_instance_routings_tbl(i).instance_id,
458 p_old_inst_rtng_code => p_x_instance_routings_tbl(i).old_inst_routing_code,
459 p_new_inst_rtng_code => p_x_instance_routings_tbl(i).inst_routing_code,
460 x_return_status => l_return_status,
461 x_msg_count => l_msg_count,
462 x_msg_data => l_msg_data
463 );
464 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
465 RAISE FND_API.G_EXC_ERROR;
466 END IF;
467 END LOOP;
468 END IF;
469
470 IF(p_commit = FND_API.G_TRUE) THEN
471 COMMIT WORK;
472 END IF;
473
474 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
475 x_msg_count := FND_MSG_PUB.count_msg;
476 IF x_msg_count > 0 THEN
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480 -- Count and Get messages (optional)
481 FND_MSG_PUB.count_and_get(
482 p_encoded => FND_API.G_FALSE,
483 p_count => x_msg_count,
484 p_data => x_msg_data);
485
486 EXCEPTION
487 WHEN FND_API.G_EXC_ERROR THEN
488 ROLLBACK TO Process_instance_routings;
489 x_return_status := FND_API.G_RET_STS_ERROR ;
490 FND_MSG_PUB.count_and_get(
491 p_encoded => FND_API.G_FALSE,
492 p_count => x_msg_count,
493 p_data => x_msg_data);
494
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496 ROLLBACK TO Process_instance_routings;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
498 FND_MSG_PUB.count_and_get(
499 p_encoded => FND_API.G_FALSE,
500 p_count => x_msg_count,
501 p_data => x_msg_data);
502
503 WHEN OTHERS THEN
504 ROLLBACK TO Process_instance_routings;
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
506 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
507 THEN
508 FND_MSG_PUB.add_exc_msg(
509 p_pkg_name => G_PKG_NAME,
510 p_procedure_name => l_api_name,
511 p_error_text => SUBSTRB(SQLERRM,1,240));
512 END IF;
513 FND_MSG_PUB.count_and_get(
514 p_encoded => FND_API.G_FALSE,
515 p_count => x_msg_count,
516 p_data => x_msg_data);
517 END Process_instance_routings;
518
519
520 ------------------------------------------------------------------------------------
521 -- Start of Comments
522 -- Function name : Get_Build_Goal_Per_For_Pos
523 -- Type : Public
524 -- Function : Recursive function to update the build goal percentage for position.
525 -- : This function fetches the minimum bg per of all the children n itself
526 -- and returns it to the parent.
527 -- : To avoid calling this function many times for each non-leaf node,
528 -- It update the result value of sub nodes, in table directly.
529 -- Pre-reqs :
530 -- Parameters :
531 --
532 -- Get_Build_Goal_Per_For_Pos Parameters:
533 -- p_current_instance_id IN Required
534 -- End of Comments
535
536 FUNCTION Get_Build_Goal_Per_For_Pos(p_current_instance_id NUMBER) RETURN NUMBER IS
537
538 /*This query will return non null child instances, also it returns zero records for leaf nodes. */
539 CURSOR get_immediate_childern(c_instance_id IN NUMBER) IS
540 SELECT curr_instance_id
541 FROM AHL_VISIT_IR_UC_DETAILS
542 WHERE parent_instance_id = c_instance_id
543 AND curr_instance_id IS NOT NULL;
544
545 CURSOR get_bg_instance(c_instance_id IN NUMBER) IS
546 SELECT bg_instance, leaf_node_flag
547 FROM AHL_VISIT_IR_UC_DETAILS
548 WHERE curr_instance_id = c_instance_id;
549
550 TYPE t_child_inst_tbl IS TABLE OF get_immediate_childern%ROWTYPE INDEX BY BINARY_INTEGER;
551
552 l_child_inst_tbl t_child_inst_tbl;
553 l_child_bg_tbl get_bg_instance%ROWTYPE;
554
555 j NUMBER;
556 /* This stores the min value.*/
557 bg_per NUMBER;
558 curr_bg_per NUMBER;
559
560 BEGIN
561 OPEN get_bg_instance(p_current_instance_id);
562 FETCH get_bg_instance INTO l_child_bg_tbl;
563 CLOSE get_bg_instance;
564
565 OPEN get_immediate_childern(p_current_instance_id);
566 FETCH get_immediate_childern BULK COLLECT INTO l_child_inst_tbl;
567 CLOSE get_immediate_childern;
568
569 -- initialise with its own bg per value
570 bg_per := l_child_bg_tbl.bg_instance;
571 FOR i IN 1..l_child_inst_tbl.COUNT LOOP
572 -- get the min bg per value of i(th) child
573 curr_bg_per := Get_Build_Goal_Per_For_Pos(l_child_inst_tbl(i).curr_instance_id);
574 -- if this child 's per value is null then pick that value
575 IF bg_per IS NOT NULL THEN
576 IF curr_bg_per IS NOT NULL THEN
577 IF bg_per > curr_bg_per THEN
578 bg_per := curr_bg_per;
579 END IF;
580 END IF;
581 ELSE
582 IF curr_bg_per IS NOT NULL THEN
583 bg_per := curr_bg_per;
584 END IF;
585 END IF;
586 END LOOP;
587
588 -- if this is not leaf node and bg_per value is not null, then set the bg_per value in table
589 IF l_child_bg_tbl.leaf_node_flag = 'N' AND bg_per IS NOT NULL THEN
590 UPDATE AHL_VISIT_IR_UC_DETAILS
591 SET bg_position = bg_per
592 WHERE curr_instance_id = p_current_instance_id;
593 END IF;
594
595 RETURN bg_per;
596 END Get_Build_Goal_Per_For_Pos;
597
598 ------------------------------------------------------------------------------------
599 -- Start of Comments
600 -- Procedure name : get_uc_details
601 -- Type : Private
602 -- Function : Procedure to update the all position details in UC.
603 -- Pre-reqs :
604 -- Parameters :
605 --
606 -- Create_Instance_Routing Parameters:
607 -- p_visit_id IN Required
608 -- p_unit_header_id IN NUMBER Required
609 -- End of Comments
610 PROCEDURE get_uc_details(
611 p_unit_header_id IN NUMBER,
612 p_visit_id IN NUMBER,
613 x_uc_details_tbl OUT NOCOPY visit_ir_uc_details_tbl_type
614 ) IS
615
616
617 l_api_name CONSTANT VARCHAR2(30) := 'get_uc_details';
618 l_uc_details_tbl visit_ir_uc_details_tbl_type;
619 l_return_status VARCHAR2(1);
620 l_msg_count NUMBER;
621 l_msg_data VARCHAR2(2000);
622
623 l_uc_descendant_tbl AHL_UC_TREE_PVT.uc_descendant_tbl_type;
624 l_unit_name VARCHAR2(80);
625 l_root_instance_id NUMBER;
626
627 CURSOR get_max_pk_count IS
628 SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;
629
630 CURSOR uc_header_information(c_unit_header_id IN NUMBER) IS
631 SELECT name,csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS
632 WHERE unit_config_header_id = c_unit_header_id;
633
634 CURSOR is_instance_unit(c_instance_id IN NUMBER) IS
635 SELECT 'U' FROM AHL_UNIT_CONFIG_HEADERS WHERE csi_item_instance_id = c_instance_id;
636
637 CURSOR get_part_info(c_instance_id NUMBER) IS
638 SELECT MTL.inventory_item_id,MTL.concatenated_segments,CSI.instance_number,CSI.serial_number, CSI.quantity
639 FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI
640 WHERE CSI.instance_id = c_instance_id
641 AND CSI.inventory_item_id = MTL.inventory_item_id
642 AND CSI.inv_master_organization_id = MTL.organization_id;
643
644 CURSOR get_item_gr_info(c_relationship_id IN NUMBER) IS
645 SELECT MCR.mc_header_id, MCR.item_group_id, GRP.name item_group_name, MCR.quantity
646 FROM AHL_MC_RELATIONSHIPS MCR, AHL_ITEM_GROUPS_B GRP
647 WHERE MCR.item_group_id = GRP.item_group_id (+)
648 AND MCR.relationship_id = c_relationship_id;
649
650 CURSOR get_sb_rules_info(c_relationship_id IN NUMBER, c_mc_header_id IN NUMBER) IS
651 SELECT CASE COUNT(rule_id)
652 WHEN 0 THEN 'N'
653 ELSE 'Y'
654 END sb_rules_flag
655 FROM AHL_SB_POSITION_RULES
656 WHERE mc_header_id = c_mc_header_id
657 AND relationship_id = c_relationship_id
658 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
659 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
660
661 CURSOR get_inst_routings_info(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
662 SELECT VIR.inst_routing_id, VIR.object_version_number, VIR.inst_routing_code, IRC.meaning
663 FROM AHL_VISIT_INST_ROUTINGS VIR, FND_LOOKUP_VALUES_VL IRC
664 WHERE VIR.instance_id = c_instance_id
665 AND VIR.visit_id = c_visit_id
666 AND IRC.lookup_code (+) = VIR.inst_routing_code
667 AND IRC.lookup_type (+) = 'AHL_INSTANCE_ROUTING';
668
669 CURSOR get_mr_count_info(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
670 -- Keeping old query for reference
671 --SELECT COUNT(MRH.MR_HEADER_ID) MR_HEADER_ID_COUNT
672 --FROM AHL_VISIT_TASKS_B TSK, AHL_MR_HEADERS_B MRH
673 --WHERE TSK.instance_id = c_instance_id
674 --AND TSK.visit_id = c_visit_id
675 --AND TSK.mr_id = MRH.mr_header_id
676 --AND NVL(TSK.status_code,'X') <> 'DELETED'
677 --AND TSK.TASK_TYPE_CODE = 'SUMMARY';
678
679 SELECT COUNT(1) MR_COUNT
680 FROM (SELECT DISTINCT unit_effectivity_id
681 FROM AHL_VISIT_TASKS_B TSK
682 WHERE TSK.instance_id = c_instance_id
683 AND TSK.visit_id = c_visit_id
684 AND NVL(TSK.status_code,'X') <> 'DELETED'
685 AND TSK.TASK_TYPE_CODE = 'SUMMARY'
686 AND TSK.unit_effectivity_id IS NOT NULL
687 --Commenting this line as it is redundant check
688 --AND NVL(TSK.TASK_TYPE_CODE , 'X') <> 'STAGE'
689 );
690
691 CURSOR get_task_count_info(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
692 SELECT COUNT(TSK.VISIT_TASK_ID) TASK_ID_COUNT
693 FROM AHL_VISIT_TASKS_B TSK
694 WHERE TSK.instance_id = c_instance_id
695 AND TSK.visit_id = c_visit_id
696 AND NVL(TSK.status_code,'X') <> 'DELETED'
697 AND NVL(TSK.TASK_TYPE_CODE , 'X') <> 'STAGE';
698
699 CURSOR get_other_visits (c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
700 SELECT CASE COUNT(visit_id)
701 WHEN 0 THEN 'N'
702 ELSE 'Y'
703 END visits_count
704 FROM AHL_VISITS_B VST
705 WHERE VST.item_instance_id = c_instance_id
706 AND VST.visit_id <> c_visit_id;
707
708 -- 16 Nov 2011, for now, removing check for scheduled_quantity,
709 -- TODO: We may need to fix it when we finilise the quantity field(scheduled or requested) to consider here.
710 CURSOR get_replace_rework_per(c_inventory_item_id IN NUMBER, c_relationship_id IN NUMBER, c_visit_id IN NUMBER) IS
711 SELECT *
712 FROM (SELECT ROM.rework_percent, ROM.replace_percent
713 FROM AHL_SCHEDULE_MATERIALS ASL,
714 AHL_RT_OPER_MATERIALS ROM
715 WHERE ASL.position_path_id IS NOT NULL
716 AND ASL.relationship_id = c_relationship_id
717 AND ASL.visit_id = c_visit_id
718 AND ASL.rt_oper_material_id = ROM.rt_oper_material_id
719 AND ROM.association_type_code = 'DISPOSITION'
720 AND ASL.status = 'ACTIVE'
721 --AND ASL.scheduled_quantity > 0
722
723 UNION ALL
724
725 -- For item based dispositions
726 SELECT ROM.rework_percent, ROM.replace_percent
727 FROM AHL_SCHEDULE_MATERIALS ASL,
728 AHL_RT_OPER_MATERIALS ROM
729 WHERE ASL.position_path_id IS NULL
730 AND ASL.inventory_item_id = c_inventory_item_id
731 AND ASL.visit_id = c_visit_id
732 AND ASL.rt_oper_material_id = ROM.rt_oper_material_id
733 AND ROM.association_type_code = 'DISPOSITION'
734 AND ASL.status = 'ACTIVE'
735 --AND ASL.scheduled_quantity > 0
736 )
737 WHERE rownum < 3;
738 TYPE t_replace_rework_per_tbl IS TABLE OF get_replace_rework_per%ROWTYPE INDEX BY BINARY_INTEGER;
739 l_replace_rework_per_tbl t_replace_rework_per_tbl;
740
741 CURSOR get_position_material_flag(c_relationship_id IN NUMBER, c_visit_id IN NUMBER) IS
742 SELECT CASE COUNT(scheduled_material_id)
743 WHEN 0 THEN 'N'
744 WHEN 1 THEN 'Y'
745 ELSE 'M'
746 END position_material
747 FROM AHL_SCHEDULE_MATERIALS ASM, AHL_MC_RELATIONSHIPS MCR
748 WHERE MCR.relationship_id = c_relationship_id
749 AND ASM.visit_id = c_visit_id
750 AND ASM.mc_header_id = MCR.mc_header_id
751 AND ASM.position_key = MCR.position_key;
752
753 TYPE t_number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
754 TYPE t_varchar_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
755 l_root_path_tbl t_varchar_tbl; -- will be used as an associatve array, it stores the parents path from root
756 l_min_child_id_tbl t_number_tbl; -- will be used as an associatve array, it stores the child number of parent
757
758
759 j NUMBER;
760 l_curr_row NUMBER;
761 l_curr_pk NUMBER;
762 temp_bg_pos NUMBER;
763
764 l_parent_uc_header_id NUMBER; -- PRAKKUM :: 27/03/2012 :: Bug# : 13488251
765 l_parent_uc_instance_id NUMBER; -- PRAKKUM :: 27/03/2012 :: Bug# : 13488251
766
767 BEGIN
768 OPEN uc_header_information(p_unit_header_id);
769 FETCH uc_header_information INTO l_unit_name,l_root_instance_id;
770 CLOSE uc_header_information;
771
772 AHL_UC_TREE_PVT.get_whole_uc_tree(
773 p_api_version => 1.0,
774 p_init_msg_list => FND_API.G_TRUE,
775 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
776 x_return_status => l_return_status,
777 x_msg_count => l_msg_count,
778 x_msg_data => l_msg_data,
779 p_uc_header_id => p_unit_header_id,
780 x_uc_descendant_tbl => l_uc_descendant_tbl);
781 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
782 RAISE FND_API.G_EXC_ERROR;
783 END IF;
784
785 /*OPEN get_child_instances(l_root_instance_id);
786 FETCH get_child_instances BULK COLLECT INTO l_child_inst_tbl;
787 CLOSE get_child_instances;*/
788
789 OPEN get_max_pk_count;
790 FETCH get_max_pk_count INTO l_curr_pk;
791 CLOSE get_max_pk_count;
792
793 l_curr_row := 1;
794
795 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
796 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
797 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
798 'Value of current pk ' || l_curr_pk);
799 END IF;
800
801 IF l_uc_descendant_tbl.FIRST IS NOT NULL THEN
802 FOR i IN l_uc_descendant_tbl.FIRST..l_uc_descendant_tbl.LAST LOOP
803 l_uc_details_tbl(l_curr_row).visit_ir_uc_id := l_curr_pk;
804 l_uc_details_tbl(l_curr_row).root_instance_id := l_root_instance_id;
805 l_uc_details_tbl(l_curr_row).unit_header_id := p_unit_header_id;
806 l_uc_details_tbl(l_curr_row).unit_name := l_unit_name;
807 l_uc_details_tbl(l_curr_row).visit_id := p_visit_id;
808
809 l_uc_details_tbl(l_curr_row).path_position_id := null;
810
811 l_uc_details_tbl(l_curr_row).relationship_id := l_uc_descendant_tbl(i).relationship_id;
812 l_uc_details_tbl(l_curr_row).parent_rel_id := l_uc_descendant_tbl(i).parent_rel_id;
813
814 l_uc_details_tbl(l_curr_row).curr_instance_id := l_uc_descendant_tbl(i).instance_id;
815 l_uc_details_tbl(l_curr_row).parent_instance_id := l_uc_descendant_tbl(i).parent_instance_id;
816
817 -- Position
818 l_uc_details_tbl(l_curr_row).position := l_uc_descendant_tbl(i).position_reference;
819 -- Leaf node
820 l_uc_details_tbl(l_curr_row).leaf_node_flag := l_uc_descendant_tbl(i).leaf_node_flag;
821 -- Position details image
822 IF l_uc_descendant_tbl(i).node_type = 'X' THEN
823 l_uc_details_tbl(l_curr_row).position_image := G_EXTRA_NODE_IMAGE;
824 ELSIF l_uc_descendant_tbl(i).node_type = 'E' THEN
825 l_uc_details_tbl(l_curr_row).position_image := G_MISSING_NODE_IMAGE;
826 ELSIF l_uc_descendant_tbl(i).node_type = 'S' THEN
827 l_uc_details_tbl(l_curr_row).position_image := G_SB_RULE_VIOLATED_NODE_IMAGE;
828 ELSE
829 l_uc_details_tbl(l_curr_row).position_image := G_FILLED_NODE_IMAGE;
830 END IF;
831
832 IF l_uc_details_tbl(l_curr_row).curr_instance_id IS NOT NULL THEN
833 -- Instance details and installed quantity
834 OPEN get_part_info(l_uc_details_tbl(l_curr_row).curr_instance_id);
835 FETCH get_part_info INTO l_uc_details_tbl(l_curr_row).curr_item_id,
836 l_uc_details_tbl(l_curr_row).curr_item_number,
837 l_uc_details_tbl(l_curr_row).curr_instance_number,
838 l_uc_details_tbl(l_curr_row).curr_serial_number,
839 l_uc_details_tbl(l_curr_row).installed_qty;
840 CLOSE get_part_info;
841
842 -- Update Position reference
843 l_uc_details_tbl(l_curr_row).position := l_uc_details_tbl(l_curr_row).position || '(' ||
844 l_uc_details_tbl(l_curr_row).curr_item_number;
845 IF(l_uc_details_tbl(l_curr_row).curr_serial_number IS NOT NULL)THEN
846 l_uc_details_tbl(l_curr_row).position := l_uc_details_tbl(l_curr_row).position || '\ ' ||
847 l_uc_details_tbl(l_curr_row).curr_serial_number || ')';
848 ELSE
849 l_uc_details_tbl(l_curr_row).position := l_uc_details_tbl(l_curr_row).position || ')';
850 END IF;
851
852 -- PRAKKUM :: 27/03/2012 :: Bug# : 13488251 :: Fixed code to get parent instance id's unit name :: START
853 --unit NHA
854 --l_uc_details_tbl(l_curr_row).unit_nha := AHL_UTIL_UC_PKG.get_sub_unit_name(l_uc_details_tbl(l_curr_row).curr_instance_id);
855 AHL_UTIL_UC_PKG.get_parent_uc_header(l_uc_details_tbl(l_curr_row).curr_instance_id, l_parent_uc_header_id, l_parent_uc_instance_id);
856 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name, 'l_parent_uc_header_id --@>'||l_parent_uc_header_id);
858 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name, 'l_parent_uc_instance_id --@>'||l_parent_uc_instance_id);
859 END IF;
860 IF l_parent_uc_header_id IS NOT NULL THEN
861 l_uc_details_tbl(l_curr_row).unit_nha := AHL_UTIL_UC_PKG.get_sub_unit_name(l_parent_uc_instance_id);
862 ELSE
863 l_uc_details_tbl(l_curr_row).unit_nha := NULL;
864 END IF;
865 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
866 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name, 'unit_name --@>'||l_uc_details_tbl(l_curr_row).unit_name);
867 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name, 'unit_nha --@>'||l_uc_details_tbl(l_curr_row).unit_nha);
868 END IF;
869 -- PRAKKUM :: 27/03/2012 :: Bug# : 13488251 :: Fixed code to get parent instance id's unit name :: END
870
871 -- If it is same as unit_name then leave it as blank
872 IF l_uc_details_tbl(l_curr_row).unit_nha = l_uc_details_tbl(l_curr_row).unit_name THEN
873 l_uc_details_tbl(l_curr_row).unit_nha := null;
874 END IF;
875
876 -- is Unit : null means it is instance
877 OPEN is_instance_unit(l_uc_details_tbl(l_curr_row).curr_instance_id);
878 FETCH is_instance_unit INTO l_uc_details_tbl(l_curr_row).is_unit;
879 CLOSE is_instance_unit;
880
881 -- Is warranty Available
882 l_uc_details_tbl(l_curr_row).is_warranty_available := AHL_WARRANTY_CONTRACTS_PVT.Is_Instance_Warranty_Available(l_uc_details_tbl(l_curr_row).curr_instance_id);
883
884 -- Other Visits available
885 OPEN get_other_visits(l_uc_details_tbl(l_curr_row).curr_instance_id, p_visit_id);
886 FETCH get_other_visits INTO l_uc_details_tbl(l_curr_row).is_visit_available;
887 CLOSE get_other_visits;
888
889 -- MR number count
890 OPEN get_mr_count_info(l_uc_details_tbl(l_curr_row).curr_instance_id, p_visit_id);
891 FETCH get_mr_count_info INTO l_uc_details_tbl(l_curr_row).mr_count;
892 CLOSE get_mr_count_info;
893
894 -- Task Number count
895 OPEN get_task_count_info(l_uc_details_tbl(l_curr_row).curr_instance_id, p_visit_id);
896 FETCH get_task_count_info INTO l_uc_details_tbl(l_curr_row).task_count;
897 CLOSE get_task_count_info;
898
899 -- Instance rounting details
900 OPEN get_inst_routings_info(l_uc_details_tbl(l_curr_row).curr_instance_id, p_visit_id);
901 FETCH get_inst_routings_info INTO l_uc_details_tbl(l_curr_row).inst_routing_id,
902 l_uc_details_tbl(l_curr_row).inst_routing_ovn,
903 l_uc_details_tbl(l_curr_row).inst_routing_code,
904 l_uc_details_tbl(l_curr_row).inst_routing_meaning;
905 CLOSE get_inst_routings_info;
906
907 -- Build goal
908 l_uc_details_tbl(l_curr_row).bg_instance := AHL_COMPLEX_MX_PVT.CALC_BUILD_GOAL(l_uc_details_tbl(l_curr_row).curr_instance_id, p_visit_id);
909
910 /*--set nade level from the assc array
911 l_uc_details_tbl(l_curr_row).node_level := l_inst_level_tbl(l_uc_details_tbl(l_curr_row).curr_instance_id); */
912
913 END IF;
914
915 -- Item Group Details and position quantity
916 OPEN get_item_gr_info(l_uc_details_tbl(l_curr_row).relationship_id);
917 FETCH get_item_gr_info INTO l_uc_details_tbl(l_curr_row).mc_header_id,
918 l_uc_details_tbl(l_curr_row).item_group_id,
919 l_uc_details_tbl(l_curr_row).item_group_name,
920 l_uc_details_tbl(l_curr_row).qty_per_position;
921 CLOSE get_item_gr_info;
922
923 -- SB Rules
924 OPEN get_sb_rules_info(l_uc_details_tbl(l_curr_row).relationship_id, l_uc_details_tbl(l_curr_row).mc_header_id);
925 FETCH get_sb_rules_info INTO l_uc_details_tbl(l_curr_row).is_sb_rule_available;
926 CLOSE get_sb_rules_info;
927
928 -- Position Material
929 OPEN get_position_material_flag(l_uc_details_tbl(l_curr_row).relationship_id, p_visit_id);
930 FETCH get_position_material_flag INTO l_uc_details_tbl(l_curr_row).position_material_flag;
931 CLOSE get_position_material_flag;
932
933 -- Instance Routings flag, fetch with functional based security
934 -- Set it Y or N
935 IF FND_FUNCTION.test('AHL_WORKSCOPE_ROUTING') THEN
936 l_uc_details_tbl(l_curr_row).inst_routing_flag := 'Y';
937 ELSE
938 l_uc_details_tbl(l_curr_row).inst_routing_flag := 'N';
939 END IF;
940
941 -- hGrid root path, since instance can be null
942 l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).relationship_id) := 0;
943 IF l_uc_details_tbl(l_curr_row).parent_rel_id IS NULL THEN
944 l_root_path_tbl(l_uc_details_tbl(l_curr_row).relationship_id) := '0';
945 ELSE
946 l_root_path_tbl(l_uc_details_tbl(l_curr_row).relationship_id) := l_root_path_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) || ',' ||
947 l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) ;
948 l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) := l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) + 1;
949 END IF;
950 l_uc_details_tbl(l_curr_row).hgrid_node_path := l_root_path_tbl(l_uc_details_tbl(l_curr_row).relationship_id);
951
952 -- Rework and Replace precent
953 OPEN get_replace_rework_per(l_uc_details_tbl(l_curr_row).curr_item_id, l_uc_details_tbl(l_curr_row).relationship_id, p_visit_id);
954 FETCH get_replace_rework_per BULK COLLECT INTO l_replace_rework_per_tbl;
955 CLOSE get_replace_rework_per;
956
957 IF l_replace_rework_per_tbl.COUNT = 1 THEN
958 l_uc_details_tbl(l_curr_row).route_dispositions_flag := 'Y';
959 l_uc_details_tbl(l_curr_row).rd_rework_percent := l_replace_rework_per_tbl(1).rework_percent;
960 l_uc_details_tbl(l_curr_row).rd_replace_percent := l_replace_rework_per_tbl(1).replace_percent;
961 ELSIF l_replace_rework_per_tbl.COUNT = 0 THEN
962 l_uc_details_tbl(l_curr_row).route_dispositions_flag := 'N';
963 ELSE
964 l_uc_details_tbl(l_curr_row).route_dispositions_flag := NULL;
965 END IF;
966
967 /* l_uc_details_tbl(l_curr_row).reserved_item,
968 l_uc_details_tbl(l_curr_row).available_parts,
969 */
970
971 l_curr_row := l_curr_row + 1;
972 l_curr_pk := l_curr_pk + 1;
973 END LOOP;
974 END IF;
975
976
977 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
978 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
979 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
980 'Done fetching details');
981 END IF;
982
983 x_uc_details_tbl := l_uc_details_tbl;
984
985 END get_uc_details;
986
987
988 ------------------------------------------------------------------------------------
989 -- Start of Comments
990 -- Procedure name : get_instance_details
991 -- Type : Private
992 -- Function : Procedure to update the instance details.
993 -- Pre-reqs :
994 -- Parameters :
995 --
996 -- Create_Instance_Routing Parameters:
997 -- p_visit_id IN Required
998 -- p_item_instance_id IN Required
999 -- End of Comments
1000 PROCEDURE get_instance_details(
1001 p_item_instance_id IN NUMBER,
1002 p_visit_id IN NUMBER,
1003 x_instance_details_tbl OUT NOCOPY visit_ir_uc_details_tbl_type
1004 ) IS
1005
1006
1007 l_api_name CONSTANT VARCHAR2(30) := 'get_instance_details';
1008 l_inst_details_tbl visit_ir_uc_details_tbl_type;
1009 l_return_status VARCHAR2(1);
1010 l_msg_count NUMBER;
1011 l_msg_data VARCHAR2(2000);
1012
1013 CURSOR get_max_pk_count IS
1014 SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;
1015
1016 CURSOR get_part_info(c_instance_id NUMBER) IS
1017 SELECT MTL.inventory_item_id,MTL.concatenated_segments,CSI.instance_number,CSI.serial_number, CSI.quantity
1018 FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI
1019 WHERE CSI.instance_id = c_instance_id
1020 AND CSI.inventory_item_id = MTL.inventory_item_id
1021 AND CSI.inv_master_organization_id = MTL.organization_id;
1022
1023 CURSOR get_inst_routings_info(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
1024 SELECT VIR.inst_routing_id, VIR.object_version_number, VIR.inst_routing_code, IRC.meaning
1025 FROM AHL_VISIT_INST_ROUTINGS VIR, FND_LOOKUP_VALUES_VL IRC
1026 WHERE VIR.instance_id = c_instance_id
1027 AND VIR.visit_id = c_visit_id
1028 AND IRC.lookup_code (+) = VIR.inst_routing_code
1029 AND IRC.lookup_type (+) = 'AHL_INSTANCE_ROUTING';
1030
1031 CURSOR get_mr_count_info(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
1032 SELECT COUNT(MRH.MR_HEADER_ID) MR_HEADER_ID_COUNT
1033 FROM AHL_VISIT_TASKS_B TSK, AHL_MR_HEADERS_B MRH
1034 WHERE TSK.instance_id = c_instance_id
1035 AND TSK.visit_id = c_visit_id
1036 AND TSK.mr_id = MRH.mr_header_id
1037 AND NVL(TSK.status_code,'X') <> 'DELETED'
1038 AND TSK.TASK_TYPE_CODE = 'SUMMARY';
1039
1040 CURSOR get_task_count_info(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
1041 SELECT COUNT(TSK.VISIT_TASK_ID) TASK_ID_COUNT
1042 FROM AHL_VISIT_TASKS_B TSK
1043 WHERE TSK.instance_id = c_instance_id
1044 AND TSK.visit_id = c_visit_id
1045 AND NVL(TSK.status_code,'X') <> 'DELETED'
1046 AND NVL(TSK.TASK_TYPE_CODE , 'X') <> 'STAGE';
1047
1048 CURSOR get_other_visits (c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
1049 SELECT CASE COUNT(visit_id)
1050 WHEN 0 THEN 'F'
1051 ELSE 'T'
1052 END visits_count
1053 FROM AHL_VISITS_B VST
1054 WHERE VST.item_instance_id = c_instance_id
1055 AND VST.visit_id <> c_visit_id;
1056
1057 l_curr_row NUMBER;
1058 l_curr_pk NUMBER;
1059
1060 BEGIN
1061 OPEN get_max_pk_count;
1062 FETCH get_max_pk_count INTO l_curr_pk;
1063 CLOSE get_max_pk_count;
1064
1065 l_curr_row := 1;
1066
1067 IF p_item_instance_id IS NOT NULL THEN
1068 l_inst_details_tbl(l_curr_row).visit_ir_uc_id := l_curr_pk;
1069 l_inst_details_tbl(l_curr_row).visit_id := p_visit_id;
1070 l_inst_details_tbl(l_curr_row).curr_instance_id := p_item_instance_id;
1071 l_inst_details_tbl(l_curr_row).root_instance_id := p_item_instance_id;
1072 l_inst_details_tbl(l_curr_row).is_unit := NULL;
1073
1074 -- Instance details and installed quantity
1075 OPEN get_part_info(p_item_instance_id);
1076 FETCH get_part_info INTO l_inst_details_tbl(l_curr_row).curr_item_id,
1077 l_inst_details_tbl(l_curr_row).curr_item_number,
1078 l_inst_details_tbl(l_curr_row).curr_instance_number,
1079 l_inst_details_tbl(l_curr_row).curr_serial_number,
1080 l_inst_details_tbl(l_curr_row).installed_qty;
1081 CLOSE get_part_info;
1082
1083 --unit NHA
1084 l_inst_details_tbl(l_curr_row).unit_nha := AHL_UTIL_UC_PKG.get_sub_unit_name(p_item_instance_id);
1085
1086 -- Is warranty Available
1087 l_inst_details_tbl(l_curr_row).is_warranty_available := AHL_WARRANTY_CONTRACTS_PVT.Is_Instance_Warranty_Available(p_item_instance_id);
1088 -- Decode the values Y or N to T or F resp
1089 IF l_inst_details_tbl(l_curr_row).is_warranty_available = 'Y' THEN
1090 l_inst_details_tbl(l_curr_row).is_warranty_available := 'T';
1091 ELSE
1092 l_inst_details_tbl(l_curr_row).is_warranty_available := 'F';
1093 END IF;
1094
1095 -- Other Visits available
1096 OPEN get_other_visits(p_item_instance_id, p_visit_id);
1097 FETCH get_other_visits INTO l_inst_details_tbl(l_curr_row).is_visit_available;
1098 CLOSE get_other_visits;
1099
1100 -- MR number count
1101 OPEN get_mr_count_info(p_item_instance_id, p_visit_id);
1102 FETCH get_mr_count_info INTO l_inst_details_tbl(l_curr_row).mr_count;
1103 CLOSE get_mr_count_info;
1104
1105 -- Task Number count
1106 OPEN get_task_count_info(p_item_instance_id, p_visit_id);
1107 FETCH get_task_count_info INTO l_inst_details_tbl(l_curr_row).task_count;
1108 CLOSE get_task_count_info;
1109
1110 -- Instance rounting details
1111 OPEN get_inst_routings_info(p_item_instance_id, p_visit_id);
1112 FETCH get_inst_routings_info INTO l_inst_details_tbl(l_curr_row).inst_routing_id,
1113 l_inst_details_tbl(l_curr_row).inst_routing_ovn,
1114 l_inst_details_tbl(l_curr_row).inst_routing_code,
1115 l_inst_details_tbl(l_curr_row).inst_routing_meaning;
1116 CLOSE get_inst_routings_info;
1117
1118 -- Build goal
1119 l_inst_details_tbl(l_curr_row).bg_instance := AHL_COMPLEX_MX_PVT.CALC_BUILD_GOAL(p_item_instance_id, p_visit_id);
1120
1121 END IF;
1122
1123 -- Instance Routings flag, fetch with functional based security
1124 -- Set it T or F
1125 IF FND_FUNCTION.test('AHL_WORKSCOPE_ROUTING') THEN
1126 l_inst_details_tbl(l_curr_row).inst_routing_flag := 'T';
1127 ELSE
1128 l_inst_details_tbl(l_curr_row).inst_routing_flag := 'F';
1129 END IF;
1130
1131 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1132 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1133 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1134 'Done fetching details');
1135 END IF;
1136
1137 x_instance_details_tbl := l_inst_details_tbl;
1138
1139 END get_instance_details;
1140
1141 ------------------------------------------------------------------------------------
1142 -- Start of Comments
1143 -- Procedure name : Create_Instance_Routing
1144 -- Type : Private
1145 -- Function : Procedure to create instance routing.
1146 -- Pre-reqs :
1147 -- Parameters :
1148 --
1149 -- Create_Instance_Routing Parameters:
1150 -- p_visit_id IN Required
1151 -- p_item_instance_id IN Required
1152 -- p_inst_routing_code IN Required
1153 -- End of Comments
1154 PROCEDURE Create_Instance_Routing (
1155 p_visit_id IN NUMBER,
1156 p_item_instance_id IN NUMBER,
1157 p_inst_routing_code IN VARCHAR2,
1158 x_inst_routing_id OUT NOCOPY NUMBER,
1159 x_return_status OUT NOCOPY VARCHAR2
1160 )
1161 IS
1162
1163 l_api_name CONSTANT VARCHAR2(30) := 'Create_Instance_Routing';
1164 l_inst_routing_id NUMBER;
1165 l_dummy VARCHAR2(1);
1166
1167 -- cursor to validate the given instance id
1168 CURSOR validate_instance_csr (c_item_instance_id NUMBER) IS
1169 SELECT 'X'
1170 FROM CSI_ITEM_INSTANCES
1171 WHERE INSTANCE_ID = c_item_instance_id;
1172
1173 -- cursor to validate the given visit id
1174 CURSOR validate_visit_csr (c_visit_id NUMBER) IS
1175 SELECT 'X'
1176 FROM AHL_VISITS_B
1177 WHERE visit_id = c_visit_id;
1178
1179 BEGIN
1180
1181 -- Initialize API return status to success
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183
1184 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1185 FND_LOG.string(FND_LOG.level_procedure, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin',
1186 'Create_Instance_Routing. visit id = '||p_visit_id||
1187 'Item Instance id = '||p_item_instance_id||
1188 'Inst routing code = '||p_inst_routing_code
1189 );
1190 END IF;
1191
1192 -- validate the visit id
1193 OPEN validate_visit_csr (p_visit_id);
1194 FETCH validate_visit_csr INTO l_dummy;
1195 IF validate_visit_csr%NOTFOUND THEN
1196 CLOSE validate_visit_csr;
1197 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_VST' ); -- Visit id VISIT_ID is invalid.
1198 Fnd_Message.Set_Token('VISIT_ID', p_visit_id);
1199 Fnd_Msg_Pub.ADD;
1200
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 RETURN;
1203 END IF;
1204 CLOSE validate_visit_csr;
1205
1206 -- validate the instance id
1207 OPEN validate_instance_csr (p_item_instance_id);
1208 FETCH validate_instance_csr INTO l_dummy;
1209 IF validate_instance_csr%NOTFOUND THEN
1210 CLOSE validate_instance_csr;
1211 Fnd_Message.SET_NAME('AHL','AHL_FMP_INVALID_ITEM_INSTANCE' ); -- The item instance INSTANCE is invalid.
1212 Fnd_Message.Set_Token('INSTANCE', p_item_instance_id);
1213 Fnd_Msg_Pub.ADD;
1214
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216 RETURN;
1217 END IF;
1218 CLOSE validate_instance_csr;
1219
1220 -- fetch the next value from the sequence
1221 l_inst_routing_id := AHL_VISIT_INST_ROUTINGS_S.NEXTVAL;
1222
1223 -- insert a record in the AHL_VISIT_INST_ROUTINGS table
1224 INSERT INTO AHL_VISIT_INST_ROUTINGS
1225 (
1226 INST_ROUTING_ID,
1227 VISIT_ID,
1228 INSTANCE_ID,
1229 INST_ROUTING_CODE,
1230 OBJECT_VERSION_NUMBER,
1231 SECURITY_GROUP_ID,
1232 LAST_UPDATE_DATE,
1233 LAST_UPDATED_BY,
1234 CREATION_DATE,
1235 CREATED_BY,
1236 LAST_UPDATE_LOGIN
1237 )
1238 values
1239 (
1240 l_inst_routing_id,
1241 p_visit_id,
1242 p_item_instance_id,
1243 p_inst_routing_code,
1244 1,
1245 NULL,
1246 SYSDATE,
1247 Fnd_Global.USER_ID,
1248 SYSDATE,
1249 Fnd_Global.USER_ID,
1250 Fnd_Global.LOGIN_ID
1251 );
1252
1253 -- populate the OUT parameter
1254 x_inst_routing_id := l_inst_routing_id;
1255
1256 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1257 FND_LOG.string(FND_LOG.level_procedure, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1258 'Create_Instance_Routing, x_return_status=' || x_return_status);
1259 END IF;
1260 END Create_Instance_Routing;
1261
1262 ------------------------------------------------------------------------------------
1263 -- Start of Comments
1264 -- Procedure name : Update_Instance_Routing
1265 -- Type : Private
1266 -- Function : Procedure to update instance routing.
1267 -- Pre-reqs :
1268 -- Parameters :
1269 --
1270 -- Update_Instance_Routing Parameters:
1271 -- p_inst_routing_id IN Required
1272 -- p_object_version_number IN Required
1273 -- p_inst_routing_code IN Required
1274 -- End of Comments
1275
1276 PROCEDURE Update_Instance_Routing (
1277 p_inst_routing_id IN NUMBER,
1278 p_object_version_number IN NUMBER,
1279 p_inst_routing_code IN VARCHAR2,
1280 x_return_status OUT NOCOPY VARCHAR2
1281 )
1282 IS
1283
1284 -- cursor to validate the given routing id
1285 CURSOR validate_routing_csr (c_inst_routing_id NUMBER) IS
1286 SELECT *
1287 FROM AHL_VISIT_INST_ROUTINGS
1288 WHERE inst_routing_id = c_inst_routing_id;
1289
1290 l_api_name CONSTANT VARCHAR2(30) := 'Update_Instance_Routing';
1291 l_inst_routings_rec validate_routing_csr%ROWTYPE;
1292
1293 BEGIN
1294
1295 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1296 FND_LOG.string(FND_LOG.level_procedure, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name|| '.begin',
1297 'Update_Instance_Routing. instance routing id = ' || p_inst_routing_id||
1298 'inst_routing_code = ' || p_inst_routing_code
1299 );
1300 END IF;
1301
1302 -- Initialize API return status to success
1303 x_return_status := FND_API.G_RET_STS_SUCCESS;
1304
1305 -- validate the instance routing id
1306 OPEN validate_routing_csr (p_inst_routing_id);
1307 FETCH validate_routing_csr INTO l_inst_routings_rec;
1308 IF validate_routing_csr%NOTFOUND THEN
1309 CLOSE validate_routing_csr;
1310 Fnd_Message.SET_NAME('AHL','AHL_CAM_INVALID_INST_ROUTING' ); -- The Instance routing id ROUTID is invalid.
1311 Fnd_Message.Set_Token('ROUTID', p_inst_routing_id);
1312 Fnd_Msg_Pub.ADD;
1313
1314 x_return_status := FND_API.G_RET_STS_ERROR;
1315 RETURN;
1316 END IF;
1317 CLOSE validate_routing_csr;
1318
1319 -- check for object_version_number
1320 IF(p_object_version_number <> l_inst_routings_rec.object_version_number) THEN
1321 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1322 FND_LOG.string(FND_LOG.level_statement, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name, 'object_version_number mismatch');
1323 END IF;
1324 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED'); -- Record has been modified by another user.
1325 FND_MSG_PUB.ADD;
1326
1327 x_return_status := FND_API.G_RET_STS_ERROR;
1328 RETURN;
1329 END IF;
1330
1331 -- update a record in the AHL_VISIT_INST_ROUTINGS table
1332 UPDATE AHL_VISIT_INST_ROUTINGS SET
1333 inst_routing_code = p_inst_routing_code,
1334 object_version_number = object_version_number + 1,
1335 security_group_id = NULL,
1336 last_update_date = SYSDATE,
1337 last_updated_by = Fnd_Global.USER_ID,
1338 last_update_login = Fnd_Global.LOGIN_ID
1339 WHERE inst_routing_id = p_inst_routing_id;
1340
1341 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level)THEN
1342 fnd_log.string(FND_LOG.level_procedure,'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1343 'Update_Instance_Routing, x_return_status=' || x_return_status);
1344 END IF;
1345 END Update_Instance_Routing;
1346
1347 ------------------------------------------------------------------------------------
1348 -- Start of Comments
1349 -- Procedure name : Delete_Instance_Routing
1350 -- Type : Private
1351 -- Function : Procedure to delete instance routing.
1352 -- Pre-reqs :
1353 -- Parameters :
1354 --
1355 -- Update_Instance_Routing Parameters:
1356 -- p_inst_routing_id IN Required
1357 -- End of Comments
1358 PROCEDURE Delete_Instance_Routing (
1359 p_inst_routing_id IN NUMBER,
1360 x_return_status OUT NOCOPY VARCHAR2
1361 ) IS
1362
1363 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Instance_Routing';
1364 l_dummy VARCHAR2(1);
1365 -- cursor to validate the given routing id
1366 CURSOR validate_routing_csr (c_inst_routing_id NUMBER) IS
1367 SELECT 'X'
1368 FROM AHL_VISIT_INST_ROUTINGS
1369 WHERE inst_routing_id = c_inst_routing_id;
1370 l_inst_routings_rec validate_routing_csr%ROWTYPE;
1371
1372 BEGIN
1373 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1374 FND_LOG.string(FND_LOG.level_procedure, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin', 'Entering Procedure');
1375 END IF;
1376
1377 -- Initialize API return status to success
1378 x_return_status := FND_API.G_RET_STS_SUCCESS;
1379
1380 -- validate the instance routing id
1381 OPEN validate_routing_csr (p_inst_routing_id);
1382 FETCH validate_routing_csr INTO l_inst_routings_rec;
1383 IF validate_routing_csr%NOTFOUND THEN
1384 CLOSE validate_routing_csr;
1385 Fnd_Message.SET_NAME('AHL','AHL_CAM_INVALID_INST_ROUTING' ); -- The Instance routing id ROUTID is invalid.
1386 Fnd_Message.Set_Token('ROUTID', p_inst_routing_id);
1387 Fnd_Msg_Pub.ADD;
1388
1389 x_return_status := FND_API.G_RET_STS_ERROR;
1390 RETURN;
1391 END IF;
1392 CLOSE validate_routing_csr;
1393
1394 -- delete row
1395 DELETE FROM AHL_VISIT_INST_ROUTINGS
1396 WHERE inst_routing_id = p_inst_routing_id;
1397
1398 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1399 FND_LOG.string(FND_LOG.level_procedure, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1400 ' x_return_status=' || x_return_status);
1401 END IF;
1402 END Delete_Instance_Routing;
1403
1404
1405 /*
1406 Procedure Process_Inst_Rtng_Notes create workorder's turnover notes for instance routing attachted to workorder instance
1407 under given visit and instance.
1408
1409 Parameters:
1410
1411 visit_id Valid Visit id
1412 instance_id Instance_id for which instance routing is defined/updated/deleted.
1413 p_old_inst_rtng_code Old Instance routing (Lookup Code) for instance.
1414 p_new_inst_rtng_code New Instnace routing (Lookup Code) for instance.
1415
1416 */
1417
1418 PROCEDURE Process_Inst_Rtng_Notes(
1419 p_visit_id IN NUMBER,
1420 p_instance_id IN NUMBER,
1421 p_old_inst_rtng_code IN VARCHAR2 := NULL,
1422 p_new_inst_rtng_code IN VARCHAR2 := NULL,
1423 x_return_status OUT NOCOPY VARCHAR2,
1424 x_msg_count OUT NOCOPY NUMBER,
1425 x_msg_data OUT NOCOPY VARCHAR2
1426 ) IS
1427
1428 --Local Variable Declaration.
1429 l_msg_count NUMBER;
1430 l_msg_data VARCHAR2(2000);
1431 l_return_status VARCHAR2(1);
1432 l_api_name VARCHAR2(30) := 'Process_Inst_Rtng_Notes';
1433 l_jtf_note_id NUMBER;
1434 l_notes VARCHAR2(2000);
1435 CURSOR Get_Visit_Instance_Workorders(p_visit_id IN NUMBER, p_instance_id IN NUMBER) IS
1436 SELECT WO.WORKORDER_ID WORKORDER_ID
1437 FROM
1438 AHL_VISITS_B VST, AHL_VISIT_TASKS_B TSK, AHL_WORKORDERS WO
1439 WHERE
1440 VST.VISIT_ID = p_visit_id
1441 AND VST.VISIT_ID = TSK.VISIT_ID
1442 AND TSK.INSTANCE_ID = p_instance_id
1443 AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
1444 AND WO.STATUS_CODE NOT IN (7,12,4,5,22); -- Cancelled, Closed,Complete,Complete No-charge,Deleted
1445 BEGIN
1446 SAVEPOINT process_inst_rtng;
1447 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1448
1449 IF (p_visit_id IS NULL) THEN
1450 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_VISIT_NULL');
1451 FND_MSG_PUB.ADD;
1452 RAISE FND_API.G_EXC_ERROR;
1453 END IF;
1454
1455 IF (p_instance_id IS NULL) THEN
1456 FND_MESSAGE.SET_NAME('AHL','AHL_LTP_APS_INST_ID_NULL');
1457 FND_MSG_PUB.ADD;
1458 RAISE FND_API.G_EXC_ERROR;
1459 END IF;
1460
1461 IF (p_old_inst_rtng_code IS NULL AND p_new_inst_rtng_code IS NULL) THEN
1462 FND_MESSAGE.SET_NAME('AHL','AHL_CAM_INST_RTNG_NULL'); -- pending to define.
1463 FND_MSG_PUB.ADD;
1464 RAISE FND_API.G_EXC_ERROR;
1465 END IF;
1466
1467 IF (NVL(p_old_inst_rtng_code,'A') <> NVL(p_new_inst_rtng_code,'B')) THEN
1468 --Process instance routing only if new and old instance routing is not same
1469 FOR i in Get_Visit_Instance_Workorders(p_visit_id,p_instance_id )
1470 LOOP
1471 AHL_PRD_WORKORDER_PVT.Add_Inst_Rtng_Notes_To_WO(
1472 p_api_version => 1.0,
1473 p_init_msg_list => FND_API.G_FALSE,
1474 p_commit => FND_API.G_FALSE,
1475 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1476 x_return_status => l_return_status,
1477 x_msg_count => l_msg_count,
1478 x_msg_data => l_msg_data,
1479 p_workorder_id => i.workorder_id,
1480 p_old_inst_rtng => p_old_inst_rtng_code,
1481 p_new_inst_rtng => p_new_inst_rtng_code
1482 );
1483
1484 l_msg_count := FND_MSG_PUB.count_msg;
1485 IF l_msg_count > 0 THEN
1486 RAISE FND_API.G_EXC_ERROR;
1487 END IF;
1488
1489 END LOOP;
1490 END IF;
1491
1492 EXCEPTION
1493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1494 ROLLBACK TO process_inst_rtng;
1495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1496 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1497 p_count => x_msg_count,
1498 p_data => x_msg_data);
1499 WHEN FND_API.G_EXC_ERROR THEN
1500 ROLLBACK TO process_inst_rtng;
1501 x_return_status := FND_API.G_RET_STS_ERROR;
1502 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1503 p_count => x_msg_count,
1504 p_data => x_msg_data);
1505 WHEN OTHERS THEN
1506 ROLLBACK TO process_inst_rtng;
1507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1509 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1510 p_procedure_name => l_api_name,
1511 p_error_text => SUBSTRB(SQLERRM,1,240));
1512
1513 END IF;
1514 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1515 p_count => x_msg_count,
1516 p_data => x_msg_data);
1517 END Process_Inst_Rtng_Notes;
1518
1519 ------------------------------------------------------------------------------------------------------
1520 -- Start of Comments
1521 -- Procedure name : Get_Instance_Location
1522 -- Type : Public
1523 -- Function : Returns details of instance in Visit;workorder; inventory;locator;OSP format
1524 -- Pre-reqs :
1525 -- Parameters :
1526 --
1527 -- Get_visit_ir_uc_details Parameters
1528 -- p_visit_id IN NUMBER
1529 -- p_instance_id IN NUMBER
1530 --
1531 -- End of Comments
1532 -------------------------------------------------------------------------------------------------------
1533 FUNCTION Get_Instance_Location(
1534 p_visit_id IN NUMBER,
1535 p_instance_id IN NUMBER
1536 )RETURN VARCHAR2 IS
1537
1538 --Cursor to Get Instance Attributes
1539 -- fix for bug 16304850
1540 /* CURSOR Get_Instance_Attributes(c_instance_id IN NUMBER) IS
1541 SELECT CII.instance_id, CII.location_type_code,
1542 CII.instance_usage_code, CII.wip_job_id, CII.inv_locator_id,
1543 MIL.segment19, MIL.segment20, MIL.concatenated_segments inv_locator_name,
1544 MIL.subinventory_code, WO.workorder_name
1545 FROM CSI_ITEM_INSTANCES CII , MTL_ITEM_LOCATIONS_KFV MIL, AHL_WORKORDERS WO
1546 WHERE CII.instance_id = c_instance_id
1547 AND CII.inv_locator_id = MIL.inventory_location_id (+)
1548 AND CII.wip_job_id = WO.wip_entity_id(+);*/
1549
1550 CURSOR Get_Instance_Attributes(c_instance_id IN NUMBER) IS
1551 SELECT CII.instance_id, CII.location_type_code,
1552 CII.instance_usage_code, CII.wip_job_id, CII.inv_locator_id,
1553 MIL.segment19, MIL.segment20, MIL_kfv.concatenated_segments inv_locator_name,
1554 MIL.subinventory_code, WO.workorder_name
1555 FROM CSI_ITEM_INSTANCES CII , MTL_ITEM_LOCATIONS MIL, MTL_ITEM_LOCATIONS_KFV MIL_kfv, AHL_WORKORDERS WO
1556 WHERE CII.instance_id = c_instance_id
1557 AND CII.inv_locator_id = MIL.inventory_location_id (+)
1558 AND MIL_kfv.INVENTORY_LOCATION_ID(+) = MIL.INVENTORY_LOCATION_ID
1559 AND MIL_kfv.ORGANIZATION_ID(+) = MIL.ORGANIZATION_ID
1560 AND CII.wip_job_id = WO.wip_entity_id(+);
1561 -- end fix for bug 16304850
1562
1563 --Cursor to Get Visit Attributes
1564 CURSOR Get_Visit_Attributes(c_visit_id IN NUMBER) IS
1565 SELECT item_instance_id, visit_number
1566 FROM AHL_VISITS_B
1567 WHERE visit_id = c_visit_id;
1568
1569 --Cursor to Get Visit Number From Project Task ID
1570 Cursor Get_Visit_From_Prj_Task(c_project_id IN NUMBER, c_project_task_id IN NUMBER) IS
1571 SELECT vst.visit_number
1572 FROM ahl_visits_b vst, ahl_visit_tasks_b vts
1573 WHERE
1574 vst.project_id = c_project_id
1575 and vst.visit_id = vts.visit_id
1576 and vts.project_task_id = c_project_task_id
1577 and rownum < 2;
1578
1579 --Cursor to Get Visit Number and OSP number From Workorder.
1580 Cursor Get_Visit_And_OSP_From_Wip_Job(c_wip_entity_id IN NUMBER) IS
1581 SELECT vst.visit_number,osph.osp_order_number
1582 FROM ahl_visits_b vst, ahl_visit_tasks_b vts, ahl_workorders wo,ahl_osp_orders_b osph, ahl_osp_order_lines ospl
1583 WHERE
1584 wo.wip_entity_id = c_wip_entity_id
1585 and wo.visit_task_id IS NOT NULL
1586 and wo.visit_task_id = vts.visit_task_id
1587 and vts.visit_id = vst.visit_id
1588 and wo.workorder_id = ospl.workorder_id (+)
1589 and ospl.osp_order_id = osph.osp_order_id (+)
1590 --PRAKKUM :: 28/03/2012 :: Bug 13579487 :: OSP Line status is null (non null values are used only for cancelled, deleted cases)
1591 --and NVL(OSPL.STATUS_CODE(+), 'ENTERED') NOT IN ( 'PO_DELETED' , 'PO_CANCELLED' )
1592 and OSPL.STATUS_CODE IS NULL
1593 AND NVL(OSPH.STATUS_CODE(+), 'ENTERED') NOT IN ( 'CLOSED' );
1594
1595 --PRAKKUM :: 28/03/2012 :: Bug 13579487
1596 --Cursor to get last osp order number for the given item
1597 CURSOR Get_OSP_Number(c_instance_id IN NUMBER) IS
1598 select OSP_ORDER_NUMBER from
1599 (
1600 select OSP_ORDER_NUMBER
1601 from CSI_ITEM_INSTANCES CII , ahl_osp_orders_b osph, ahl_osp_order_lines ospl
1602 WHERE CII.instance_id = c_instance_id
1603 and CII.inventory_item_id = ospl.inventory_item_id
1604 and CII.serial_number = ospl.serial_number
1605 and ospl.osp_order_id = osph.osp_order_id (+)
1606 --and NVL(OSPL.STATUS_CODE(+), 'ENTERED') NOT IN ( 'PO_DELETED' , 'PO_CANCELLED' )
1607 and OSPL.STATUS_CODE IS NULL
1608 AND NVL(OSPH.STATUS_CODE(+), 'ENTERED') NOT IN ( 'CLOSED' )
1609 order by ospl.osp_order_line_id desc
1610 )
1611 where rownum=1;
1612
1613 --Local Variable Declaration.
1614 l_inst_attr Get_Instance_Attributes%ROWTYPE;
1615 l_inst_location VARCHAR2(200) := NULL;
1616 l_visit_attr Get_Visit_Attributes%ROWTYPE;
1617 l_visit_number NUMBER := NULL;
1618 -- fix for bug 16304850
1619 --l_sub_inventory mtl_item_locations_kfv.subinventory_code%TYPE;
1620 --l_locator_name mtl_item_locations_kfv.concatenated_segments%TYPE;
1621 l_sub_inventory mtl_item_locations.subinventory_code%TYPE;
1622 l_locator_name VARCHAR2(204);
1623 -- end fix for bug 16304850
1624 l_job_name ahl_workorders.workorder_name%TYPE;
1625 l_osp_order ahl_osp_orders_b.osp_order_number%TYPE;
1626
1627 BEGIN
1628
1629 --Return NULL if visit or instance is null
1630 IF (p_visit_id IS NULL OR p_instance_id IS NULL) THEN
1631 RETURN NULL;
1632 END IF;
1633
1634
1635 --Open Instance Attribute Cursor.
1636 OPEN Get_Instance_Attributes(p_instance_id);
1637 FETCH Get_Instance_Attributes INTO l_inst_attr;
1638 CLOSE Get_Instance_Attributes;
1639
1640 --Open Visit Cursor
1641 OPEN Get_Visit_Attributes(p_visit_id);
1642 FETCH Get_Visit_Attributes INTO l_visit_attr;
1643 CLOSE Get_Visit_Attributes;
1644
1645 --Return NULL if either of the input parameters are not deriving valid data from db.
1646 IF (l_visit_attr.item_instance_id IS NULL OR l_inst_attr.instance_id IS NULL) THEN
1647 RETURN NULL;
1648 END IF;
1649
1650 IF (l_inst_attr.location_type_code = 'HZ_PARTY_SITES') THEN
1651 --If instance is on Unit.
1652 l_inst_location := NULL;
1653 RETURN l_inst_location;
1654 ELSIF(l_inst_attr.location_type_code = 'INVENTORY') THEN
1655 --If instance is lying in physical locator or visit locator.
1656 IF(l_inst_attr.segment19 IS NOT NULL AND l_inst_attr.segment20 IS NOT NULL) THEN
1657 --Instance is on Visit locator. In this case get visit number also.
1658 IF(l_visit_attr.item_instance_id = l_inst_attr.instance_id) THEN
1659 --If instance is same as visit header instance. In this case get visit number based upon locator.
1660 OPEN Get_Visit_From_Prj_Task(l_inst_attr.segment19, l_inst_attr.segment20);
1661 FETCH Get_Visit_From_Prj_Task INTO l_visit_number;
1662 CLOSE Get_Visit_From_Prj_Task;
1663 ELSE
1664 --If instance is not same as visit header instance then visit number is same as sent as parameter.
1665 l_visit_number := l_visit_attr.visit_number;
1666 END IF;
1667 END IF;
1668 l_locator_name := l_inst_attr.inv_locator_name;
1669 l_sub_inventory := l_inst_attr.subinventory_code;
1670
1671 --PRAKKUM :: 28/03/2012 :: Bug 13579487 :: Since OSP order can be inventory based and as inventory based OSP doesn't have work order info.,
1672 --fetch details based on given instance.
1673 IF(l_visit_attr.item_instance_id = l_inst_attr.instance_id) THEN --Fetch only if instance is visit unit
1674 OPEN Get_OSP_Number(l_inst_attr.instance_id);
1675 FETCH Get_OSP_Number INTO l_osp_order;
1676 CLOSE Get_OSP_Number;
1677 END IF;
1678
1679 ELSIF(l_inst_attr.location_type_code = 'WIP') THEN
1680 --If instance is on WIP job. In this case... sub inventory and locator will not be shown.
1681 IF(l_visit_attr.item_instance_id = l_inst_attr.instance_id) THEN
1682 --If instance is same as visit header instance. In this case get visit number based upon Workorder.
1683 OPEN Get_Visit_And_OSP_From_Wip_Job(l_inst_attr.wip_job_id);
1684 FETCH Get_Visit_And_OSP_From_Wip_Job INTO l_visit_number, l_osp_order;
1685 CLOSE Get_Visit_And_OSP_From_Wip_Job;
1686 ELSE
1687 --If instance is not same as visit header instance then visit number is same as input parameter.
1688 l_visit_number := l_visit_attr.visit_number;
1689 END IF;
1690 l_job_name := l_inst_attr.workorder_name;
1691 END IF;
1692
1693 --Construct location detail
1694 l_inst_location := NVL(TO_CHAR(l_visit_number), '-') || ';' ||
1695 NVL(l_job_name, '-') || ';' ||
1696 NVL(l_sub_inventory, '-') || ';' ||
1697 NVL(l_locator_name, '-') || ';' ||
1698 NVL(TO_CHAR(l_osp_order), '-');
1699
1700 RETURN l_inst_location;
1701
1702 END Get_Instance_Location;
1703
1704 ------------------------------------------------------------------------------------------------------
1705 -- Start of Comments
1706 -- Procedure name : Get_disposition_count
1707 -- Type : Public
1708 -- Function : Returns disposition count of work orders associated with given visit n instacne
1709 -- Returns null if count is zero.
1710 -- Pre-reqs :
1711 -- Parameters :
1712 --
1713 -- Get_visit_ir_uc_details Parameters
1714 -- p_x_instance_routings_tbl IN OUT NOCOPY instance_routings_tbl_type
1715 --
1716 -- End of Comments
1717 -------------------------------------------------------------------------------------------------------
1718 FUNCTION Get_disposition_count(
1719 p_visit_id IN NUMBER,
1720 p_instance_id IN NUMBER
1721 )RETURN NUMBER IS
1722 -- c_instance_id is sufficient as an input parameter
1723 -- but keeping c_visit_id field
1724 CURSOR get_dispo_count(c_instance_id IN NUMBER, c_visit_id IN NUMBER) IS
1725 SELECT SUM(quantity)
1726 FROM (
1727 /* STHILAK bug #13641891 : commented the query and framed the modified query
1728 SELECT 1 quantity
1729 FROM AHL_PRD_DISPOSITIONS_B DISP,
1730 AHL_WORKORDERS WO,
1731 CSI_ITEM_INSTANCES CSI
1732 WHERE DISP.workorder_id = WO.workorder_id
1733 AND DISP.instance_id = CSI.instance_id
1734 AND CSI.wip_job_id = WO.wip_entity_id
1735 AND DISP.status_code NOT IN ('TERMINATED','COMPLETED')
1736 AND CSI.location_type_code = 'WIP'
1737 AND CSI.serial_number IS NOT NULL
1738 AND CSI.instance_id = c_instance_id
1739
1740 UNION ALL
1741
1742 -- For non serial controlled items
1743 SELECT DISP.quantity quantity
1744 FROM AHL_PRD_DISPOSITIONS_B DISP,
1745 AHL_WORKORDERS WO,
1746 CSI_ITEM_INSTANCES CSI
1747 WHERE DISP.workorder_id = WO.workorder_id
1748 AND DISP.inventory_item_id = CSI.inventory_item_id
1749 AND CSI.wip_job_id = WO.wip_entity_id
1750 AND DISP.status_code NOT IN ('TERMINATED','COMPLETED')
1751 AND CSI.location_type_code = 'WIP'
1752 AND NVL(DISP.lot_number,'x') = NVL(CSI.lot_number,'x')
1753 AND NVL(DISP.item_revision,'x') = NVL(CSI.inventory_revision,'x')
1754 AND CSI.serial_number IS NULL
1755 AND CSI.instance_id = c_instance_id */
1756
1757 SELECT 1 quantity
1758 FROM AHL_PRD_DISPOSITIONS_B DISP,
1759 CSI_ITEM_INSTANCES CSI
1760 WHERE DISP.instance_id = CSI.instance_id
1761 AND CSI.instance_id = c_instance_id
1762 AND CSI.location_type_code = 'WIP'
1763 AND CSI.serial_number IS NOT NULL
1764 AND disp.workorder_id IN
1765 (SELECT workorder_id FROM AHL_WORKORDERS WHERE visit_id =c_visit_id
1766 )
1767 AND NVL(DISP.status_code,'x') NOT IN ('TERMINATED','COMPLETED')
1768
1769 UNION ALL
1770
1771 SELECT DISP.quantity quantity
1772 FROM AHL_PRD_DISPOSITIONS_B DISP,
1773 CSI_ITEM_INSTANCES CSI
1774 WHERE DISP.inventory_item_id = CSI.inventory_item_id
1775 AND CSI.location_type_code = 'WIP'
1776 AND CSI.serial_number IS NULL
1777 AND CSI.instance_id = c_instance_id
1778 AND NVL(DISP.status_code,'x') NOT IN ('TERMINATED','COMPLETED')
1779 AND NVL(DISP.lot_number,'x') = NVL(CSI.lot_number,'x')
1780 AND NVL(DISP.item_revision,'x') = NVL(CSI.inventory_revision,'x')
1781 AND DISP.workorder_id IN
1782 (SELECT workorder_id FROM AHL_WORKORDERS WHERE visit_id =c_visit_id
1783 )
1784 );
1785
1786 l_count NUMBER;
1787
1788 BEGIN
1789
1790 OPEN get_dispo_count(p_instance_id, p_visit_id);
1791 FETCH get_dispo_count INTO l_count;
1792 CLOSE get_dispo_count;
1793
1794 IF l_count = 0 THEN
1795 RETURN NULL;
1796 ELSE
1797 RETURN l_count;
1798 END IF;
1799
1800 END Get_disposition_count;
1801
1802 END AHL_COMPLEX_MX_RWSC_PVT;
[Home] [Help]