1 PACKAGE BODY AHL_PRD_DISP_UTIL_PVT AS
2 /* $Header: AHLVDIUB.pls 120.8.12020000.2 2012/12/14 07:54:43 shnatu ship $ */
3
4 -- Define global internal variables and cursors
5 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_DISP_UTIL_PVT';
6 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
7 G_WO_STATUS_COMPLETE CONSTANT NUMBER := 1;
8
9 G_LOG_PREFIX VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.';
10
11 G_ISSUE_ID CONSTANT NUMBER := 35;
12 G_RETURN_ID CONSTANT NUMBER := 43;
13
14 -- Start of Comments --
15 -- Define procedure get_disposition_list
16 -- Procedure name: get_disposition_list
17 -- Type: Private
18 -- Function: API to get all dispositions for a job. This API is used to replace the
19 -- disposition view which is too complicated to build
20 -- Pre-reqs:
21 --
22 -- Parameters:
23 -- p_workorder_id IN NUMBER Required, to identify the job
24 -- p_start_row IN NUMBER specify the start row to populate into search result table
25 -- p_rows_per_page IN NUMBER specify the number of row to be populated in the search result table
26 -- p_disp_filter_rec IN disp_filter_rec_type, to store the record structure with which
27 -- to restrict the disposition list result
28 -- x_results_count OUT NUMBER, row count from the query, this number can be more than the
29 -- number of row in search result table
30 -- x_disp_list_tbl OUT disp_list_tbl_type, to store the disposition list result
31 -- Version: Initial Version 1.0
32 --
33 -- End of Comments --
34
35 PROCEDURE get_disposition_list(
36 p_api_version IN NUMBER := 1.0,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
38 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
39 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43 p_workorder_id IN NUMBER,
44 p_start_row IN NUMBER,
45 p_rows_per_page IN NUMBER,
46 p_disp_filter_rec IN disp_filter_rec_type,
47 x_results_count OUT NOCOPY NUMBER,
48 x_disp_list_tbl OUT NOCOPY disp_list_tbl_type)
49 IS
50 CURSOR get_job_attrs IS
51 --begin performance tuning
52 /* SELECT job_number,
53 organization_id,
54 job_status_code
55 FROM ahl_workorders_v
56 WHERE workorder_id = p_workorder_id;
57 */
58 SELECT 'x'
59 FROM ahl_workorders
60 WHERE workorder_id = p_workorder_id;
61 l_job_attrs get_job_attrs%ROWTYPE;
62 --end performance tuning
63
64 CURSOR get_item_group_id(c_item_group_name VARCHAR2) IS
65 SELECT item_group_id
66 FROM ahl_item_groups_b
67 WHERE name = c_item_group_name;
68
69 CURSOR check_inv_item_id(c_inventory_item_id NUMBER, c_organization_id NUMBER) IS
70 SELECT inventory_item_id
71 FROM mtl_system_items_kfv
72 WHERE inventory_item_id = c_inventory_item_id
73 AND organization_id = c_organization_id;
74
75 CURSOR get_inv_item_id(c_item_number VARCHAR2, c_organization_id NUMBER) IS
76 SELECT inventory_item_id
77 FROM mtl_system_items_kfv
78 WHERE concatenated_segments = c_item_number
79 AND organization_id = c_organization_id;
80
81 CURSOR check_con_status_id(c_status_id VARCHAR2) IS
82 SELECT status_id
83 FROM mtl_material_statuses
84 WHERE status_id = c_status_id;
85
86 CURSOR get_con_status_id(c_status_code VARCHAR2) IS
87 SELECT status_id
88 FROM mtl_material_statuses
89 WHERE status_code = c_status_code;
90
91 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
92 -- The OFF and ON quantity for the search UI table needs to be set with the
93 -- newly added columns of the view AHL_PART_CHANGES_V, instead of the instance quantity.
94 CURSOR get_part_change_instance(c_part_change_id NUMBER) IS
95 SELECT OFFC.inventory_item_id off_inv_item_id,
96 OFFI.concatenated_segments off_item_number,
97 OFFC.instance_number off_instance_number,
98 OFFC.serial_number off_serial_number,
99 OFFC.lot_number off_lot_number,
100 -- OFFC.quantity off_quantity,
101 PC.removed_quantity off_quantity,
102 OFFC.unit_of_measure off_uom,
103 ONC.inventory_item_id on_inv_item_id,
104 ONI.concatenated_segments on_item_number,
105 ONC.instance_number on_instance_number,
106 ONC.serial_number on_serial_number,
107 ONC.lot_number on_lot_number,
108 -- ONC.quantity on_quantity,
109 PC.installed_quantity on_quantity,
110 ONC.unit_of_measure on_uom
111 FROM ahl_part_changes_v PC,
112 csi_item_instances OFFC,
113 mtl_system_items_kfv OFFI,
114 csi_item_instances ONC,
115 mtl_system_items_kfv ONI
116 WHERE PC.part_change_id = c_part_change_id
117 AND PC.removed_instance_id = OFFC.instance_id (+)
118 AND PC.installed_instance_id = ONC.instance_id (+)
119 AND OFFC.inventory_item_id = OFFI.inventory_item_id (+)
120 AND OFFC.inv_master_organization_id = OFFI.organization_id (+)
121 AND ONC.inventory_item_id = ONI.inventory_item_id (+)
122 AND ONC.inv_master_organization_id = ONI.organization_id (+);
123 l_part_change_instance get_part_change_instance%ROWTYPE;
124
125 CURSOR get_disp_mtl_txn_assoc (c_disposition_id NUMBER) IS
126 SELECT MT.transaction_type_id transaction_type_id,
127 DM.uom uom,
128 sum(DM.quantity) quantity,
129 count(MT.transaction_type_id) rec_no
130 FROM ahl_workorder_mtl_txns MT,
131 ahl_prd_disp_mtl_txns DM
132 WHERE DM.disposition_id = c_disposition_id
133 AND MT.workorder_mtl_txn_id = DM.workorder_mtl_txn_id
134 GROUP BY MT.transaction_type_id, DM.uom
135 HAVING MT.transaction_type_id IN (G_ISSUE_ID, G_RETURN_ID);
136
137 CURSOR get_disp_mtl_txn(c_disposition_id NUMBER, c_txn_type_id NUMBER) IS
138 SELECT MT.transaction_type_id transaction_type_id,
139 MT.inventory_item_id inv_item_id,
140 IV.concatenated_segments item_number,
141 MT.serial_number serial_number,
142 MT.lot_number lot_number,
143 DM.quantity quantity,
144 DM.uom uom
145 FROM ahl_workorder_mtl_txns MT,
146 ahl_prd_disp_mtl_txns DM,
147 mtl_system_items_kfv IV
148 WHERE DM.disposition_id = c_disposition_id
149 AND MT.workorder_mtl_txn_id = DM.workorder_mtl_txn_id
150 AND MT.transaction_type_id = c_txn_type_id
151 AND MT.inventory_item_id = IV.inventory_item_id
152 AND MT.organization_id = IV.organization_id;
153 l_disp_mtl_txn get_disp_mtl_txn%ROWTYPE;
154
155 l_api_name CONSTANT VARCHAR2(30) := 'get_disposition_list';
156 l_api_version CONSTANT NUMBER := 1.0;
157 l_return_status VARCHAR2(1);
158 l_msg_count NUMBER;
159 l_msg_data VARCHAR2(2000);
160
161 l_disposition_id NUMBER;
162 l_part_change_id NUMBER;
163 l_path_position_id NUMBER;
164 l_path_pos_common_id NUMBER;
165 l_path_position_ref VARCHAR2(80);
166 l_inv_item_id NUMBER;
167 l_item_number VARCHAR2(40);
168 l_item_group_id NUMBER;
169 l_item_group_name VARCHAR2(80);
170 l_con_status_id NUMBER;
171 l_con_status_code VARCHAR2(80);
172 l_instance_id NUMBER;
173 l_instance_number VARCHAR2(30);
174 l_serial_number VARCHAR2(30);
175 l_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
176 l_quantity NUMBER;
177 l_uom VARCHAR2(3);
178 l_immediate_disp_code VARCHAR2(30);
179 l_immediate_disp VARCHAR2(80);
180 l_secondary_disp_code VARCHAR2(30);
181 l_secondary_disp VARCHAR2(80);
182 l_disp_status_code VARCHAR2(30);
183 l_disp_status VARCHAR2(80);
184 l_condition_id NUMBER;
185 l_condition_code VARCHAR2(80);
186 l_issue_no NUMBER;
187 l_return_no NUMBER;
188
189 l_sql_str VARCHAR2(10000);
190 l_from_string VARCHAR2(5000);
191 l_where_str VARCHAR2(10000);
192 l_count_query VARCHAR2(10000);
193 l_and_str VARCHAR(20);
194
195 i NUMBER;
196 l_bind_index NUMBER := 1;
197 l_count NUMBER;
198 l_cur_index NUMBER;
199 l_start_row NUMBER;
200 l_msg_index NUMBER;
201 l_translated_msg VARCHAR2(100);
202 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
203 l_conditions AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
204
205 BEGIN
206 --Initialize API return status to success
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208
209 --Standard Start of API savepoint
210 SAVEPOINT get_disposition_list;
211
212 --Standard call to check for call compatibility.
213 IF NOT FND_API.compatible_api_call(
214 l_api_version,
215 p_api_version,
216 l_api_name,
217 G_PKG_NAME)
218 THEN
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 END IF;
221
222 --Initialize message list if p_init_msg_list is set to TRUE.
223 IF FND_API.to_boolean( p_init_msg_list ) THEN
224 FND_MSG_PUB.initialize;
225 END IF;
226
227 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
228 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
229 G_LOG_PREFIX||l_api_name||': Begin API',
230 'At the start of the procedure and p_workorder_id = '||p_workorder_id||
231 ' p_start_row = '||p_start_row||' p_rows_per_page='||p_rows_per_page);
232 END IF;
233
234 --Validate the input parameter p_workorder_id
235 OPEN get_job_attrs;
236 FETCH get_job_attrs INTO l_job_attrs;
237 IF get_job_attrs%NOTFOUND THEN
238 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_JOB_ID_INVALID');
239 FND_MESSAGE.set_token('JOBID', p_workorder_id);
240 FND_MSG_PUB.add;
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 CLOSE get_job_attrs;
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 ELSE
245 CLOSE get_job_attrs;
246 END IF;
247
248 --For the field of path position, if the user wants to use the condition, then it must be
249 --triggered by LOV, and the search is based on the id instead of value. Also the search uses
250 --only the version neutral common id even if the user uses the version specific path position id.
251 IF (p_disp_filter_rec.path_position_ref IS NOT NULL AND p_disp_filter_rec.path_position_id IS NOT NULL) THEN
252 SELECT path_pos_common_id INTO l_path_pos_common_id
253 FROM ahl_mc_path_positions
254 WHERE path_position_id = p_disp_filter_rec.path_position_id;
255 END IF;
256
257 /* This is an API for search page, so the value to id conversion is really not necessary
258
259 --Convert values to IDs of the filter record
260
261 --Convert item_group_name to item_group_id
262
263 --Convert item_number to inventory_item_id
264 IF (p_disp_filter_rec.inventory_item_id IS NOT NULL) THEN
265 --This includes three cases: 1) id only 2) value and id while id is correct but value may be wrong
266 --3) value and id while value is correct but id maybe wrong. It is better to handle these three cases
267 --differently. But here we ignore the warning message which should be sent to user if value is wrong
268 --in case 20 and totally ignore the correct value of case 3). But this case will cover regular LOV
269 --return mode in which both value and id will be correct.
270 OPEN check_inv_item_id(p_disp_filter_rec.inventory_item_id, l_job_attrs.organization_id);
271 FETCH check_inv_item_id INTO l_inv_item_id;
272 IF check_inv_item_id%NOTFOUND THEN
273 FND_MESSAGE.set_name('AHL', 'AHL_PRD_ITEM_ID_INVALID');
274 FND_MESSAGE.set_token('ITEMID', p_disp_filter_rec.inventory_item_id);
275 FND_MSG_PUB.add;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 CLOSE check_inv_item_id;
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 ELSE
280 CLOSE check_inv_item_id;
281 END IF;
282 ELSIF (p_disp_filter_rec.inventory_item_id IS NULL AND
283 p_disp_filter_rec.item_number IS NOT NULL) THEN
284 --This includes the case in which only value is provided.
285 OPEN get_inv_item_id(p_disp_filter_rec.item_number, l_job_attrs.organization_id);
286 FETCH get_inv_item_id INTO l_inv_item_id;
287 IF get_inv_item_id%NOTFOUND THEN
288 FND_MESSAGE.set_name('AHL', 'AHL_PRD_ITEM_NUM_INVALID');
289 FND_MESSAGE.set_token('ITEM', p_disp_filter_rec.item_number);
290 FND_MSG_PUB.add;
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
292 CLOSE get_inv_item_id;
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 ELSE
295 CLOSE get_inv_item_id;
296 END IF;
297 END IF;
298 --Convert condition_code to condition_id
299 IF (p_disp_filter_rec.condition_id IS NOT NULL) THEN
300 OPEN check_con_status_id(p_disp_filter_rec.condition_id);
301 FETCH check_con_status_id INTO l_con_status_id;
302 IF check_con_status_id%NOTFOUND THEN
303 FND_MESSAGE.set_name('AHL', 'AHL_PRD_CON_STATUS_ID_INVALID');
304 FND_MESSAGE.set_token('STATUSID', p_disp_filter_rec.condition_id);
305 FND_MSG_PUB.add;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 CLOSE check_con_status_id;
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 ELSE
310 CLOSE check_con_status_id;
311 END IF;
312 ELSIF (p_disp_filter_rec.condition_id IS NULL AND
313 p_disp_filter_rec.condition_code IS NOT NULL) THEN
314 --This includes the case in which only value is provided.
315 OPEN get_con_status_id(p_disp_filter_rec.condition_code);
316 FETCH get_con_status_id INTO l_con_status_id;
317 IF get_con_status_id%NOTFOUND THEN
318 FND_MESSAGE.set_name('AHL', 'AHL_PRD_CON_STATUS_INVALID');
319 FND_MESSAGE.set_token('STAUS', p_disp_filter_rec.condition_code);
320 FND_MSG_PUB.add;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322 CLOSE get_con_status_id;
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 ELSE
325 CLOSE get_con_status_id;
326 END IF;
327 END IF;
328
329 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
330 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
331 G_LOG_PREFIX||l_api_name||': Finish the value to id conversion and validation',
332 'At the end of the procedure');
333 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
334 G_LOG_PREFIX||l_api_name||': l_path_position_id='||l_path_position_id||
335 'l_path_pos_comm_id='||l_path_pos_comm_id||'l_item_group_id='||l_item_group_id||
336 'l_inv_item_id='||l_inv_item_id||'l_con_status_id='||l_con_status_id);
337 END IF;
338 */
339 --Build dynamic query to get all dispositions related to the job
340
341 l_sql_str := 'SELECT disposition_id, part_change_id, path_position_id, position_reference, ';
342 l_sql_str := l_sql_str||'inventory_item_id, item_number, instance_id, instance_number, ';
343 l_sql_str := l_sql_str||'serial_number, lot_number, quantity, uom, ';
344 l_sql_str := l_sql_str||'immediate_disposition_code, immediate_type, secondary_disposition_code, ';
345 l_sql_str := l_sql_str||'secondary_type, status_code, status, condition_id, condition_code, item_group_id, item_group_name ';
346 l_sql_str := l_sql_str||'FROM ahl_prd_dispositions_v DIS ';
347
348 l_where_str := ' workorder_id = (:b' || l_bind_index || ')';
349 l_conditions(l_bind_index) := p_workorder_id;
350 l_bind_index := l_bind_index + 1;
351 l_and_str := ' AND ';
352
353 IF (l_path_pos_common_id IS NOT NULL) THEN
354 l_where_str := l_where_str ||l_and_str ||' path_pos_common_id = (:b' || l_bind_index || ')';
355 l_conditions(l_bind_index) := l_path_pos_common_id;
356 l_bind_index := l_bind_index + 1;
357 l_and_str := ' AND ';
358 END IF;
359
360 IF (p_disp_filter_rec.item_group_name IS NOT NULL) THEN
361 l_where_str := l_where_str || l_and_str || ' item_group_name = (:b' || l_bind_index || ')';
362 l_conditions(l_bind_index) := p_disp_filter_rec.item_group_name;
363 l_bind_index := l_bind_index + 1;
364 l_and_str := ' AND ';
365 END IF;
366
367 IF (p_disp_filter_rec.item_number IS NOT NULL) THEN
368 l_where_str := l_where_str || l_and_str || ' item_number = (:b' || l_bind_index || ')';
369 l_conditions(l_bind_index) := p_disp_filter_rec.item_number;
370 l_bind_index := l_bind_index + 1;
371 l_and_str := ' AND ';
372 END IF;
373
374 IF (p_disp_filter_rec.condition_code IS NOT NULL) THEN
375 l_where_str := l_where_str || l_and_str || ' condition_code = (:b' || l_bind_index || ')';
376 l_conditions(l_bind_index) := p_disp_filter_rec.condition_code;
377 l_bind_index := l_bind_index + 1;
378 l_and_str := ' AND ';
379 END IF;
380
381 IF (p_disp_filter_rec.immediate_disp_code IS NOT NULL AND p_disp_filter_rec.immediate_disp_code <> 'NULL') THEN
382 l_where_str := l_where_str || l_and_str || ' immediate_disposition_code = (:b' || l_bind_index || ')';
383 l_conditions(l_bind_index) := p_disp_filter_rec.immediate_disp_code;
384 l_bind_index := l_bind_index + 1;
385 l_and_str := ' AND ';
386 ELSIF (p_disp_filter_rec.immediate_disp_code = 'NULL') THEN
387 l_where_str := l_where_str || l_and_str || ' immediate_disposition_code IS NULL ';
388 l_and_str := ' AND ';
389 END IF;
390
391 IF (p_disp_filter_rec.secondary_disp_code IS NOT NULL AND p_disp_filter_rec.secondary_disp_code <> 'NULL') THEN
392 l_where_str := l_where_str || l_and_str || ' secondary_disposition_code = (:b' || l_bind_index || ')';
393 l_conditions(l_bind_index) := p_disp_filter_rec.secondary_disp_code;
394 l_bind_index := l_bind_index + 1;
395 l_and_str := ' AND ';
396 ELSIF (p_disp_filter_rec.secondary_disp_code = 'NULL') THEN
397 l_where_str := l_where_str || l_and_str || ' secondary_disposition_code IS NULL ';
398 l_and_str := ' AND ';
399 END IF;
400
401 IF (p_disp_filter_rec.disp_status_code IS NOT NULL AND p_disp_filter_rec.disp_status_code <> 'NULL') THEN
402 l_where_str := l_where_str || l_and_str || ' status_code = (:b' || l_bind_index || ')';
403 l_conditions(l_bind_index) := p_disp_filter_rec.disp_status_code;
404 l_bind_index := l_bind_index + 1;
405 l_and_str := ' AND ';
406 ELSIF (p_disp_filter_rec.disp_status_code = 'NULL') THEN
407 l_where_str := l_where_str || l_and_str || ' status_code IS NULL ';
408 l_and_str := ' AND ';
409 END IF;
410
411 IF (p_disp_filter_rec.item_type_code IS NOT NULL) THEN
412 l_where_str := l_where_str || l_and_str || ' decode(nvl(trackable_flag, ''N''), ''Y'', ''TRACKED'',''NON_TRACKED'') = (:b' || l_bind_index || ')';
413 l_conditions(l_bind_index) := p_disp_filter_rec.item_type_code;
414 l_bind_index := l_bind_index + 1;
415 --l_and_str := ' AND ';
416 END IF;
417
418 IF l_where_str IS NOT NULL THEN
419 l_sql_str := l_sql_str || ' WHERE ' || l_where_str ;
420 END IF;
421
422 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
424 G_LOG_PREFIX||l_api_name||': Within the API',
425 'p_disp_filter_rec.condition_code='||p_disp_filter_rec.condition_code);
426 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
427 G_LOG_PREFIX||l_api_name||': Within the API',
428 'l_sql_str='||substr(l_sql_str, 1, 254));
429 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
430 G_LOG_PREFIX||l_api_name||': Within the API',
431 'l_sql_str='||substr(l_sql_str, 255, 500));
432 END IF;
433 --dbms_output.put_line(substr(l_sql_str,1, 254));
434 --dbms_output.put_line(substr(l_sql_str,255, 500));
435
436 l_count_query := 'SELECT COUNT(*) FROM (' || l_sql_str || ')';
437 BEGIN
438 AHL_OSP_UTIL_PKG.EXEC_IMMEDIATE(l_conditions, l_count_query, x_results_count);
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 x_results_count := 0;
442 END;
443
444 IF x_results_count > 0 THEN
445 l_sql_str := l_sql_str || ' ORDER BY DIS.disposition_id ';
446
447 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR(l_cur, l_conditions, l_sql_str);
448 i := 1;
449 l_cur_index := 1;
450
451 IF p_start_row > x_results_count THEN
452 l_start_row := 1;
453 ELSE
454 l_start_row := p_start_row;
455 END IF;
456
457 LOOP
458 FETCH l_cur INTO l_disposition_id,
459 l_part_change_id,
460 l_path_position_id,
461 l_path_position_ref,
462 l_inv_item_id,
463 l_item_number,
464 l_instance_id,
465 l_instance_number,
466 l_serial_number,
467 l_lot_number,
468 l_quantity,
469 l_uom,
470 l_immediate_disp_code,
471 l_immediate_disp,
472 l_secondary_disp_code,
473 l_secondary_disp,
474 l_disp_status_code,
475 l_disp_status,
476 l_condition_id,
477 l_condition_code,
478 l_item_group_id,
479 l_item_group_name;
480
481
482 EXIT WHEN (l_cur%NOTFOUND OR l_cur_index = l_start_row + p_rows_per_page); -- stop fetching
483
484 IF (l_cur_index >= l_start_row AND l_cur_index < l_start_row + p_rows_per_page) THEN
485 x_disp_list_tbl(i).disposition_id := l_disposition_id;
486 x_disp_list_tbl(i).part_change_id := l_part_change_id;
487 x_disp_list_tbl(i).path_position_id := l_path_position_id;
488 x_disp_list_tbl(i).path_position_ref := l_path_position_ref;
489
490 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
491 -- OFF details should not be filled for dispositions for empty positions, which
492 -- are used only for installation.
493 IF (l_item_number IS NOT NULL) THEN
494 x_disp_list_tbl(i).off_inv_item_id := l_inv_item_id;
495 x_disp_list_tbl(i).off_item_number := l_item_number;
496 x_disp_list_tbl(i).off_instance_id := l_instance_id;
497 x_disp_list_tbl(i).off_instance_number := l_instance_number;
498 x_disp_list_tbl(i).off_serial_number := l_serial_number;
499 x_disp_list_tbl(i).off_lot_number := l_lot_number;
500 x_disp_list_tbl(i).off_quantity := l_quantity;
501 x_disp_list_tbl(i).off_uom := l_uom;
502 END IF;
503
504 x_disp_list_tbl(i).immediate_disp_code := l_immediate_disp_code;
505 x_disp_list_tbl(i).immediate_disp := l_immediate_disp;
506 x_disp_list_tbl(i).secondary_disp_code := l_secondary_disp_code;
507 x_disp_list_tbl(i).secondary_disp := l_secondary_disp;
508 x_disp_list_tbl(i).disp_status_code := l_disp_status_code;
509 x_disp_list_tbl(i).disp_status := l_disp_status;
510 x_disp_list_tbl(i).condition_id := l_condition_id;
511 x_disp_list_tbl(i).condition_code := l_condition_code;
512 x_disp_list_tbl(i).item_group_id := l_item_group_id;
513 x_disp_list_tbl(i).item_group_name := l_item_group_name;
514 i := i+1;
515 END IF;
516 l_cur_index := l_cur_index + 1;
517 END LOOP;
518 CLOSE l_cur;
519
520 --dbms_output.put_line('After getting the result table');
521
522 FOR i IN x_disp_list_tbl.FIRST..x_disp_list_tbl.LAST LOOP
523 IF x_disp_list_tbl(i).part_change_id IS NOT NULL THEN
524 --There is a part change associated with this disposition
525 OPEN get_part_change_instance(x_disp_list_tbl(i).part_change_id);
526 FETCH get_part_change_instance INTO l_part_change_instance;
527
528 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
529 -- ON details should not be filled for dispositions used for removal of non-serialised items.
530 IF (install_part_change_valid(x_disp_list_tbl(i).disposition_id)='Y' AND
531 NOT (x_disp_list_tbl(i).off_serial_number IS NULL AND
532 x_disp_list_tbl(i).off_item_number IS NOT NULL)) THEN
533 x_disp_list_tbl(i).on_inv_item_id := l_part_change_instance.on_inv_item_id;
534 x_disp_list_tbl(i).on_item_number := l_part_change_instance.on_item_number;
535 x_disp_list_tbl(i).on_instance_number := l_part_change_instance.on_instance_number;
536 x_disp_list_tbl(i).on_serial_number := l_part_change_instance.on_serial_number;
537 x_disp_list_tbl(i).on_lot_number := l_part_change_instance.on_lot_number;
538 x_disp_list_tbl(i).on_quantity := l_part_change_instance.on_quantity;
539 x_disp_list_tbl(i).on_uom := l_part_change_instance.on_uom;
540 ELSE
541 x_disp_list_tbl(i).on_inv_item_id := NULL;
542 x_disp_list_tbl(i).on_item_number := NULL;
543 x_disp_list_tbl(i).on_instance_number := NULL;
544 x_disp_list_tbl(i).on_serial_number := NULL;
545 x_disp_list_tbl(i).on_lot_number := NULL;
546 x_disp_list_tbl(i).on_quantity := NULL;
547 x_disp_list_tbl(i).on_uom := NULL;
548 END IF;
549
550 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
551 -- OFF details should not be filled for dispositions for empty positions, which
552 -- are used only for installation.
553 IF (x_disp_list_tbl(i).off_item_number IS NOT NULL) THEN
554
555 -- Updated by RBHAVSAR for Bug fix 6456199 on Oct 10, 2007.
556 -- If serial number is present for disposition then it should not be overwritten with
557 -- parts change serial number and part number because part number change could have changed
558 -- the part number and serial number. We need to display the original serial number and
559 -- part number as at the time of disposition.
560
561 -- Commented out the setting of item
562 -- x_disp_list_tbl(i).off_inv_item_id := l_part_change_instance.off_inv_item_id;
563 -- x_disp_list_tbl(i).off_item_number := l_part_change_instance.off_item_number;
564 -- Added If check for serial number so that the values are changed only for Non Serialized case
565 IF (x_disp_list_tbl(i).off_serial_number IS NULL) THEN
566 x_disp_list_tbl(i).off_instance_number := l_part_change_instance.off_instance_number;
567 x_disp_list_tbl(i).off_serial_number := l_part_change_instance.off_serial_number;
568 x_disp_list_tbl(i).off_lot_number := l_part_change_instance.off_lot_number;
569 x_disp_list_tbl(i).off_quantity := l_part_change_instance.off_quantity;
570 x_disp_list_tbl(i).off_uom := l_part_change_instance.off_uom;
571 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
572 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
573 G_LOG_PREFIX||l_api_name,
574 'x_disp_list_tbl(i).off_inv_item_id '|| x_disp_list_tbl(i).off_inv_item_id||
575 'x_disp_list_tbl(i).off_item_number ' || x_disp_list_tbl(i).off_item_number );
576 END IF;
577 END IF;
578
579 -- End of change by RBHAVSAR for Bug fix 6456199 on Oct 10, 2007
580
581 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
582 -- For installation against empty positions (typically for non-serialised items),
583 -- fetch the ON quantity directly from the base table ahl_part_changes.
584 ELSE
585 SELECT quantity
586 INTO x_disp_list_tbl(i).on_quantity
587 FROM ahl_part_changes
588 WHERE part_change_id = x_disp_list_tbl(i).part_change_id;
589 END IF;
590
591 CLOSE get_part_change_instance;
592 ELSE
593 l_issue_no := 0;
594 l_return_no := 0;
595 For l_disp_mtl_txn_assoc IN get_disp_mtl_txn_assoc(x_disp_list_tbl(i).disposition_id) LOOP
596 --This cursor can return only 0, 1, or 2 at most records
597 IF l_disp_mtl_txn_assoc.transaction_type_id = G_ISSUE_ID THEN
598 x_disp_list_tbl(i).on_quantity := l_disp_mtl_txn_assoc.quantity;
599 x_disp_list_tbl(i).on_uom := l_disp_mtl_txn_assoc.uom;
600 l_issue_no := l_disp_mtl_txn_assoc.rec_no;
601 ELSIF l_disp_mtl_txn_assoc.transaction_type_id = G_RETURN_ID THEN
602 x_disp_list_tbl(i).off_quantity := l_disp_mtl_txn_assoc.quantity;
603 x_disp_list_tbl(i).off_uom := l_disp_mtl_txn_assoc.uom;
604 l_return_no := l_disp_mtl_txn_assoc.rec_no;
605 END IF;
606 END LOOP;
607
608 IF l_return_no = 0 THEN
609 --There is no RETURN material transation associated to the disposition at all
610 --We have to clear the off_quantity and off_uom because the disposition entity
611 --may have them defined.
612 --And if l_issue_no = 0 then by default all ON related attributes will be null.
613 x_disp_list_tbl(i).off_quantity := NULL;
614 x_disp_list_tbl(i).off_uom := NULL;
615 END IF;
616 IF l_return_no >= 1 THEN
617 --If there exists one RETURN mtl txn associated to this disposition, it could be
618 --tracked item or non-tracked item
619 --If there exist multiple RETURN mtl txns associated to this disposition, they could
620 --only be non-tracked items, but these items are the same.
621 OPEN get_disp_mtl_txn(x_disp_list_tbl(i).disposition_id, G_RETURN_ID);
622 FETCH get_disp_mtl_txn INTO l_disp_mtl_txn;
623 x_disp_list_tbl(i).off_inv_item_id := l_disp_mtl_txn.inv_item_id;
624 x_disp_list_tbl(i).off_item_number := l_disp_mtl_txn.item_number;
625 IF l_return_no = 1 THEN
626 x_disp_list_tbl(i).off_serial_number := l_disp_mtl_txn.serial_number;
627 x_disp_list_tbl(i).off_lot_number := l_disp_mtl_txn.lot_number;
628 ELSE
629 x_disp_list_tbl(i).off_serial_number := null;
630 x_disp_list_tbl(i).off_lot_number := null;
631 END IF;
632 CLOSE get_disp_mtl_txn;
633 END IF;
634 IF l_issue_no = 1 THEN
635 --There exists one ISSUE mtl txn associated to this disposition, it could be
636 --tracked item or non-tracked item
637 OPEN get_disp_mtl_txn(x_disp_list_tbl(i).disposition_id, G_ISSUE_ID);
638 FETCH get_disp_mtl_txn INTO l_disp_mtl_txn;
639 x_disp_list_tbl(i).on_inv_item_id := l_disp_mtl_txn.inv_item_id;
640 x_disp_list_tbl(i).on_item_number := l_disp_mtl_txn.item_number;
641 x_disp_list_tbl(i).on_serial_number := l_disp_mtl_txn.serial_number;
642 x_disp_list_tbl(i).on_lot_number := l_disp_mtl_txn.lot_number;
643 CLOSE get_disp_mtl_txn;
644 END IF;
645 IF l_issue_no > 1 THEN
646 --There exist multiple ISSUE mtl txns associated to this disposition, they could
647 --only non-tracked items, and these items can be different. Not sure that the
648 --items could also be tracked, even if it is possible, then the items should be the same.
649 OPEN get_disp_mtl_txn(x_disp_list_tbl(i).disposition_id, G_ISSUE_ID);
650 FETCH get_disp_mtl_txn INTO l_disp_mtl_txn;
651 x_disp_list_tbl(i).on_inv_item_id := l_disp_mtl_txn.inv_item_id;
652 x_disp_list_tbl(i).on_item_number := l_disp_mtl_txn.item_number;
653 LOOP
654 FETCH get_disp_mtl_txn INTO l_disp_mtl_txn;
655 EXIT WHEN get_disp_mtl_txn%NOTFOUND;
656 IF x_disp_list_tbl(i).on_inv_item_id <> l_disp_mtl_txn.inv_item_id THEN
657 x_disp_list_tbl(i).on_inv_item_id := NULL;
658 --In this case we need to assign constant '<MULTIPLE>' to on_item_number,
659 --but it is translatable, so we put it into the message table and fetch back
660 --the translated one. And then we need to remove this particular message from
661 --the message otherwise it will interfere with other regular meaningful messages.
662 FND_MESSAGE.set_name('AHL', 'AHL_PRD_CONST_MULTIPLE');
663 FND_MSG_PUB.add;
664 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
665 p_encoded => FND_API.G_FALSE,
666 p_data => l_translated_msg,
667 p_msg_index_out => l_msg_index);
668 FND_MSG_PUB.delete_msg(l_msg_index);
669 x_disp_list_tbl(i).on_item_number := l_translated_msg;
670 EXIT;
671 END IF;
672 END LOOP;
673 CLOSE get_disp_mtl_txn;
674 END IF;
675 --Handle the special case in which a blank record will be displayed in the UI when
676 --creating default job dispositions. In this case, the disposition's item_group_id is not null but we
677 --don't display item_group_name in the UI and the off_item_id is null, so we use the
678 --off_item_number column to display the item_group_name to save the width of the table.
679 IF (x_disp_list_tbl(i).off_inv_item_id IS NULL AND
680 x_disp_list_tbl(i).item_group_id IS NOT NULL) THEN
681 --In this case we need to assign '<ITEM GROUP NAME>:'+item_group_name to off_item_number,
682 --but it is translatable, so we put it into the message table and fetch back
683 --the translated one. And then we need to remove this particular message from
684 --the message otherwise it will interfere with other regular meaningful messages.
685 /* After discussion with Jay, now decided to display it just as <Item Group Name> (09/22/04)
686 FND_MESSAGE.set_name('AHL', 'AHL_PRD_CONST_IG_NAME');
687 FND_MSG_PUB.add;
688 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
689 p_encoded => FND_API.G_FALSE,
690 p_data => l_translated_msg,
691 p_msg_index_out => l_msg_index);
692 FND_MSG_PUB.delete_msg(l_msg_index);
693 x_disp_list_tbl(i).off_item_number := '<'||l_translated_msg||'>:'||x_disp_list_tbl(i).item_group_name;
694 */
695 x_disp_list_tbl(i).off_item_number := '<'||x_disp_list_tbl(i).item_group_name||'>';
696
697 END IF;
698 END IF;
699 END LOOP;
700 END IF;
701
702 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
703 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
704 G_LOG_PREFIX||l_api_name||': After normal execution',
705 'At the end of the procedure');
706 END IF;
707
708 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
709 l_msg_count := FND_MSG_PUB.count_msg;
710 IF l_msg_count > 0 THEN
711 x_msg_count := l_msg_count;
712 RAISE FND_API.G_EXC_ERROR;
713 END IF;
714 -- Count and Get messages (optional)
715 FND_MSG_PUB.count_and_get(
716 p_encoded => FND_API.G_FALSE,
717 p_count => x_msg_count,
718 p_data => x_msg_data);
719
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR THEN
722 --ROLLBACK TO get_disposition_list;
723 x_return_status := FND_API.G_RET_STS_ERROR ;
724 FND_MSG_PUB.count_and_get(
725 p_encoded => FND_API.G_FALSE,
726 p_count => x_msg_count,
727 p_data => x_msg_data);
728
729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
730 --ROLLBACK TO get_disposition_list;
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
732 FND_MSG_PUB.count_and_get(
733 p_encoded => FND_API.G_FALSE,
734 p_count => x_msg_count,
735 p_data => x_msg_data);
736
737 WHEN OTHERS THEN
738 --ROLLBACK TO get_disposition_list;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
740 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
741 THEN
742 FND_MSG_PUB.add_exc_msg(
743 p_pkg_name => G_PKG_NAME,
744 p_procedure_name => l_api_name,
745 p_error_text => SUBSTRB(SQLERRM,1,240));
746 END IF;
747 FND_MSG_PUB.count_and_get(
748 p_encoded => FND_API.G_FALSE,
749 p_count => x_msg_count,
750 p_data => x_msg_data);
751
752 END get_disposition_list;
753
754 ------------------------
755 -- Declare Procedures --
756 ------------------------
757 -- Start of Comments --
758 -- Procedure name : Get_Part_Change_Disposition
759 -- Type : Private
760 -- Function : Fetch the disposition for part change UI
761 -- Pre-reqs :
762 -- Parameters : p_parent_instance_id: parent csi item instance_id
763 -- p_workorder_id: workorder_id
764 -- p_unit_config_header_id: top unit header id
765 -- p_relationship_id: position for installation/removal
766 -- x_disposition_rec: returning disposition record
767 -- x_imm_disp_type_tbl: returning immediate disposition type
768 -- x_sec_disp_type_tbl: returning secondary dispositions
769 --
770 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
771 -- p_instance_id: Added to support IB Trees. Pass the instance id to get the disposition for the given instance.
772 --
773 --
774 -- End of Comments.
775
776 PROCEDURE Get_Part_Change_Disposition (
777 p_api_version IN NUMBER,
778 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
779 p_commit IN VARCHAR2 := FND_API.G_FALSE,
780 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
781 x_return_status OUT NOCOPY VARCHAR2,
782 x_msg_count OUT NOCOPY NUMBER,
783 x_msg_data OUT NOCOPY VARCHAR2,
784 p_workorder_id IN NUMBER,
785 --p_unit_config_header_id IN NUMBER, replaced by p_workorder_id by Jerry on 09/20/04
786 p_parent_instance_id IN NUMBER,
787 p_relationship_id IN NUMBER,
788 p_instance_id IN NUMBER,
789 x_disposition_rec OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
790 x_imm_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type,
791 x_sec_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type)
792 IS
793 --
794 --Check if position is empty or filled
795 CURSOR check_installed_csr (p_parent_id IN NUMBER,
796 p_relationship_id IN NUMBER) IS
797 SELECT csi.instance_id
798 FROM csi_item_instances csi, csi_ii_relationships rel
799 WHERE csi.instance_id = rel.subject_id
800 AND REL.object_id = p_parent_id
801 AND REL.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
802 AND TRUNC(nvl(REL.ACTIVE_START_DATE, sysdate-1)) <= TRUNC(sysdate)
803 AND TRUNC(nvl(REL.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
804 AND TO_NUMBER(REL.position_reference) = p_relationship_id;
805 --
806 --Fetch all information for the disposition_rec object for path + item
807 --Must share same path position, same inventory item, but no part removal yet
808 --Also not complete or terminated
809 CURSOR get_rem_disposition_csr (p_path_position_id IN NUMBER,
810 p_instance_id IN NUMBER) IS
811 /*
812 SELECT disp.disposition_id,
813 disp.object_version_number,
814 disp.last_update_date,
815 disp.last_updated_by,
816 disp.creation_date,
817 disp.created_by,
818 disp.last_update_login,
819 disp.workorder_id,
820 disp.part_change_id,
821 disp.path_position_id,
822 disp.item_number,
823 disp.inventory_item_id,
824 disp.organization_id,
825 disp.instance_number,
826 disp.instance_id,
827 disp.item_group_id,
828 disp.item_group_name,
829 disp.serial_number,
830 disp.lot_number,
831 disp.quantity,
832 disp.uom,
833 disp.condition_id,
834 disp.condition_code,
835 disp.immediate_disposition_code,
836 disp.immediate_type,
837 disp.secondary_disposition_code,
838 disp.secondary_type,
839 disp.status_code,
840 disp.status,
841 disp.collection_id,
842 disp.primary_service_request_id,
843 disp.non_routine_workorder_id,
844 disp.comments,
845 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
846 SEV.NAME SEVERITY_NAME,
847 INC.PROBLEM_CODE,
848 PCODE.MEANING PROBLEM_MEANING,
849 INC.SUMMARY
850 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_MC_PATH_POSITIONS pp,
851 CS_INCIDENTS_ALL_VL INC, CS_INCIDENT_SEVERITIES_VL SEV,
852 FND_LOOKUP_VALUES_VL PCODE
853 WHERE disp.path_pos_common_id = pp.path_pos_common_id
854 AND pp.path_position_id = p_path_position_id
855 AND disp.part_change_id IS NULL
856 AND disp.workorder_id = p_workorder_id
857 AND nvl(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
858 AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
859 AND disp.instance_id = p_instance_id
860 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
861 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
862 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
863 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE;
864 */
865 SELECT disp.disposition_id,
866 disp.object_version_number,
867 disp.last_update_date,
868 disp.last_updated_by,
869 disp.creation_date,
870 disp.created_by,
871 disp.last_update_login,
872 disp.workorder_id,
873 disp.part_change_id,
874 disp.path_position_id,
875 MTL.CONCATENATED_SEGMENTS ITEM_NUMBER,
876 disp.inventory_item_id,
877 disp.organization_id,
878 CSI.INSTANCE_NUMBER,
879 disp.instance_id,
880 disp.item_group_id,
881 GRP.NAME ITEM_GROUP_NAME,
882 disp.serial_number,
883 disp.lot_number,
884 disp.quantity,
885 disp.uom,
886 disp.condition_id,
887 cond.STATUS_CODE CONDITION_CODE,
888 disp.immediate_disposition_code,
889 FND1.MEANING immediate_type,
890 disp.secondary_disposition_code,
891 FND2.MEANING secondary_type,
892 disp.status_code,
893 FND3.MEANING status,
894 disp.collection_id,
895 disp.primary_service_request_id,
896 disp.non_routine_workorder_id,
897 displ.comments,
898 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
899 SEV.NAME SEVERITY_NAME,
900 INC.PROBLEM_CODE,
901 PCODE.MEANING PROBLEM_MEANING,
902 INCL.SUMMARY
903 FROM AHL_PRD_DISPOSITIONS_b disp,
904 AHL_PRD_DISPOSITIONS_tl displ,
905 AHL_MC_PATH_POSITIONS pp,
906 MTL_SYSTEM_ITEMS_KFV MTL,
907 CSI_ITEM_INSTANCES CSI,
908 AHL_ITEM_GROUPS_B GRP,
909 MTL_MATERIAL_STATUSES_TL COND,
910 CS_INCIDENTS_ALL_B INC,
911 CS_INCIDENTS_ALL_TL INCL,
912 CS_INCIDENT_SEVERITIES_TL SEV,
913 FND_LOOKUP_VALUES PCODE,
914 FND_LOOKUP_VALUES FND1,
915 FND_LOOKUP_VALUES FND2,
916 FND_LOOKUP_VALUES FND3
917 WHERE pp.path_pos_common_id IN (
918 SELECT PATH_POS_COMMON_ID
919 FROM AHL_MC_PATH_POSITIONS
920 WHERE PATH_POSITION_ID = p_path_position_id
921 )
922 AND disp.path_position_id = pp.path_position_id
923 AND disp.workorder_id =p_workorder_id
924 AND disp.instance_id = p_instance_id
925 AND disp.part_change_id IS NULL
926 AND nvl(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
927 AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
928 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
929 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
930 AND SEV.language(+) = USERENV('LANG')
931 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
932 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
933 AND PCODE.language(+) = USERENV('LANG')
934 AND disp.disposition_id = displ.disposition_id
935 AND displ.language = USERENV('LANG')
936 AND disp.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
937 AND disp.ORGANIZATION_ID = MTL.organization_id (+)
938 AND disp.INSTANCE_ID = CSI.INSTANCE_ID (+)
939 AND disp.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
940 AND disp.condition_id = COND.status_id (+)
941 AND COND.language(+) = USERENV('LANG')
942 AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
943 AND disp.immediate_disposition_code = FND1.LOOKUP_CODE (+)
944 AND FND1.LANGUAGE(+) = USERENV('LANG')
945 AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
946 AND disp.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
947 AND FND2.LANGUAGE(+) = USERENV('LANG')
948 AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
949 AND disp.STATUS_CODE = FND3.LOOKUP_CODE (+)
950 AND FND3.LANGUAGE(+) = USERENV('LANG')
951 AND INCL.INCIDENT_ID (+) = INC.INCIDENT_ID
952 AND INCL.language(+) = USERENV('LANG');
953 --
954 --Fetch all information for the disposition_rec object for path + item
955 --Must share same path position for empty position
956 --Also not complete or terminated
957 CURSOR get_inst_disposition_csr (p_path_position_id IN NUMBER) IS
958 /*
959 SELECT disp.disposition_id,
960 disp.object_version_number,
961 disp.last_update_date,
962 disp.last_updated_by,
963 disp.creation_date,
964 disp.created_by,
965 disp.last_update_login,
966 disp.workorder_id,
967 disp.part_change_id,
968 disp.path_position_id,
969 disp.item_number,
970 disp.inventory_item_id,
971 disp.organization_id,
972 disp.instance_number,
973 disp.instance_id,
974 disp.item_group_id,
975 disp.item_group_name,
976 disp.serial_number,
977 disp.lot_number,
978 disp.quantity,
979 disp.uom,
980 disp.condition_id,
981 disp.condition_code,
982 disp.immediate_disposition_code,
983 disp.immediate_type,
984 disp.secondary_disposition_code,
985 disp.secondary_type,
986 disp.status_code,
987 disp.status,
988 disp.collection_id,
989 disp.primary_service_request_id,
990 disp.non_routine_workorder_id,
991 disp.comments,
992 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
993 SEV.NAME SEVERITY_NAME,
994 INC.PROBLEM_CODE,
995 PCODE.MEANING PROBLEM_MEANING,
996 INC.SUMMARY
997 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_MC_PATH_POSITIONS pp,
998 CS_INCIDENTS_ALL_VL INC,
999 CS_INCIDENT_SEVERITIES_VL SEV, FND_LOOKUP_VALUES_VL PCODE
1000 WHERE disp.path_pos_common_id =pp.path_pos_common_id
1001 AND pp.path_position_id = p_path_position_id
1002 AND disp.part_change_id IS NULL
1003 AND disp.workorder_id = p_workorder_id
1004 AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
1005 AND disp.immediate_disposition_code = 'NOT_RECEIVED'
1006 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
1007 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
1008 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
1009 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE;
1010 */
1011 SELECT disp.disposition_id,
1012 disp.object_version_number,
1013 disp.last_update_date,
1014 disp.last_updated_by,
1015 disp.creation_date,
1016 disp.created_by,
1017 disp.last_update_login,
1018 disp.workorder_id,
1019 disp.part_change_id,
1020 disp.path_position_id,
1021 MTL.CONCATENATED_SEGMENTS ITEM_NUMBER,
1022 disp.inventory_item_id,
1023 disp.organization_id,
1024 CSI.INSTANCE_NUMBER,
1025 disp.instance_id,
1026 disp.item_group_id,
1027 GRP.NAME ITEM_GROUP_NAME,
1028 disp.serial_number,
1029 disp.lot_number,
1030 disp.quantity,
1031 disp.uom,
1032 disp.condition_id,
1033 cond.STATUS_CODE CONDITION_CODE,
1034 disp.immediate_disposition_code,
1035 FND1.MEANING immediate_type,
1036 disp.secondary_disposition_code,
1037 FND2.MEANING secondary_type,
1038 disp.status_code,
1039 FND3.MEANING status,
1040 disp.collection_id,
1041 disp.primary_service_request_id,
1042 disp.non_routine_workorder_id,
1043 displ.comments,
1044 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
1045 SEV.NAME SEVERITY_NAME,
1046 INC.PROBLEM_CODE,
1047 PCODE.MEANING PROBLEM_MEANING,
1048 INCL.SUMMARY
1049 FROM AHL_PRD_DISPOSITIONS_b disp,
1050 AHL_PRD_DISPOSITIONS_tl displ,
1051 AHL_MC_PATH_POSITIONS pp,
1052 CS_INCIDENTS_ALL_B INC,
1053 CS_INCIDENTS_ALL_TL INCL,
1054 CS_INCIDENT_SEVERITIES_TL SEV,
1055 FND_LOOKUP_VALUES PCODE,
1056 MTL_SYSTEM_ITEMS_KFV MTL,
1057 CSI_ITEM_INSTANCES CSI,
1058 AHL_ITEM_GROUPS_B GRP,
1059 MTL_MATERIAL_STATUSES_TL COND,
1060 FND_LOOKUP_VALUES FND1,
1061 FND_LOOKUP_VALUES FND2,
1062 FND_LOOKUP_VALUES FND3
1063 WHERE pp.path_pos_common_id IN (
1064 SELECT PATH_POS_COMMON_ID
1065 FROM AHL_MC_PATH_POSITIONS
1066 WHERE PATH_POSITION_ID = p_path_position_id
1067 )
1068 AND disp.path_position_id = pp.path_position_id
1069 AND disp.part_change_id IS NULL
1070 AND disp.workorder_id = p_workorder_id
1071 AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
1072 AND disp.immediate_disposition_code = 'NOT_RECEIVED'
1073 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
1074 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
1075 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
1076 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
1077 AND PCODE.LANGUAGE(+) = USERENV('LANG')
1078 AND disp.disposition_id = displ.disposition_id
1079 AND displ.LANGUAGE = USERENV('LANG')
1080 AND disp.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
1081 AND disp.ORGANIZATION_ID = MTL.organization_id (+)
1082 AND disp.INSTANCE_ID = CSI.INSTANCE_ID (+)
1083 AND disp.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
1084 AND disp.condition_id = COND.status_id (+)
1085 AND COND.LANGUAGE(+) = USERENV('LANG')
1086 AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
1087 AND disp.immediate_disposition_code = FND1.LOOKUP_CODE (+)
1088 AND FND1.LANGUAGE(+) = USERENV('LANG')
1089 AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
1090 AND disp.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
1091 AND FND2.LANGUAGE(+) = USERENV('LANG')
1092 AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
1093 AND disp.STATUS_CODE = FND3.LOOKUP_CODE (+)
1094 AND FND3.LANGUAGE(+) = USERENV('LANG')
1095 AND INCL.INCIDENT_ID (+) = INC.INCIDENT_ID
1096 AND INCL.LANGUAGE(+) = USERENV('LANG');
1097
1098 --For the disposition whose immediate type is not necessary to be
1099 --Not Received, has Part Change Removal occurred but not installation
1100 --occurred yet. Added by Jerry on 01/06/2005
1101 CURSOR get_inst_disposition_csr1 (p_path_position_id IN NUMBER) IS
1102 -- AnRaj: Changed the query to improve performance,Bug# 4911881 Issue 3
1103 /*
1104 SELECT disp.disposition_id,
1105 disp.object_version_number,
1106 disp.last_update_date,
1107 disp.last_updated_by,
1108 disp.creation_date,
1109 disp.created_by,
1110 disp.last_update_login,
1111 disp.workorder_id,
1112 disp.part_change_id,
1113 disp.path_position_id,
1114 disp.item_number,
1115 disp.inventory_item_id,
1116 disp.organization_id,
1117 disp.instance_number,
1118 disp.instance_id,
1119 disp.item_group_id,
1120 disp.item_group_name,
1121 disp.serial_number,
1122 disp.lot_number,
1123 disp.quantity,
1124 disp.uom,
1125 disp.condition_id,
1126 disp.condition_code,
1127 disp.immediate_disposition_code,
1128 disp.immediate_type,
1129 disp.secondary_disposition_code,
1130 disp.secondary_type,
1131 disp.status_code,
1132 disp.status,
1133 disp.collection_id,
1134 disp.primary_service_request_id,
1135 disp.non_routine_workorder_id,
1136 disp.comments,
1137 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
1138 SEV.NAME SEVERITY_NAME,
1139 INC.PROBLEM_CODE,
1140 PCODE.MEANING PROBLEM_MEANING,
1141 INC.SUMMARY
1142 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_MC_PATH_POSITIONS pp,
1143 CS_INCIDENTS_ALL_VL INC,
1144 CS_INCIDENT_SEVERITIES_VL SEV, FND_LOOKUP_VALUES_VL PCODE,
1145 AHL_PART_CHANGES_V PC
1146 WHERE disp.path_pos_common_id =pp.path_pos_common_id
1147 AND pp.path_position_id = p_path_position_id
1148 AND disp.part_change_id IS NOT NULL
1149 AND disp.workorder_id = p_workorder_id
1150 AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
1151 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
1152 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
1153 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
1154 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
1155 AND disp.part_change_id = PC.part_change_id
1156 AND PC.removed_instance_id IS NOT NULL
1157 AND PC.installed_part_change_id IS NULL;
1158 */
1159 SELECT disp.disposition_id,
1160 disp.object_version_number,
1161 disp.last_update_date,
1162 disp.last_updated_by,
1163 disp.creation_date,
1164 disp.created_by,
1165 disp.last_update_login,
1166 disp.workorder_id,
1167 disp.part_change_id,
1168 disp.path_position_id,
1169 MTL.CONCATENATED_SEGMENTS ITEM_NUMBER,
1170 disp.inventory_item_id,
1171 disp.organization_id,
1172 CSI.INSTANCE_NUMBER,
1173 disp.instance_id,
1174 disp.item_group_id,
1175 GRP.NAME ITEM_GROUP_NAME,
1176 disp.serial_number,
1177 disp.lot_number,
1178 disp.quantity,
1179 disp.uom,
1180 disp.condition_id,
1181 COND.STATUS_CODE CONDITION_CODE,
1182 disp.immediate_disposition_code,
1183 FND1.MEANING immediate_type,
1184 disp.secondary_disposition_code,
1185 FND2.MEANING secondary_type,
1186 disp.status_code,
1187 FND3.MEANING status,
1188 disp.collection_id,
1189 disp.primary_service_request_id,
1190 disp.non_routine_workorder_id,
1191 displ.comments,
1192 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
1193 SEV.NAME SEVERITY_NAME,
1194 INC.PROBLEM_CODE,
1195 PCODE.MEANING PROBLEM_MEANING,
1196 INCL.SUMMARY
1197 FROM AHL_PRD_DISPOSITIONS_b disp,
1198 AHL_PRD_DISPOSITIONS_tl displ,
1199 AHL_MC_PATH_POSITIONS pp,
1200 FND_LOOKUP_VALUES FND1,
1201 FND_LOOKUP_VALUES FND2,
1202 FND_LOOKUP_VALUES FND3,
1203 MTL_MATERIAL_STATUSES_TL COND,
1204 AHL_ITEM_GROUPS_B GRP,
1205 MTL_SYSTEM_ITEMS_KFV MTL,
1206 CSI_ITEM_INSTANCES CSI,
1207 CS_INCIDENTS_ALL_B INC,
1208 CS_INCIDENTS_ALL_TL INCL,
1209 CS_INCIDENT_SEVERITIES_TL SEV,
1210 FND_LOOKUP_VALUES PCODE,
1211 AHL_PART_CHANGES_V PC
1212 WHERE pp.path_pos_common_id IN (
1213 SELECT PATH_POS_COMMON_ID
1214 FROM AHL_MC_PATH_POSITIONS
1215 WHERE PATH_POSITION_ID = p_path_position_id
1216 )
1217 AND disp.path_position_id = pp.path_position_id
1218 AND disp.disposition_id = displ.disposition_id
1219 AND displ.language = USERENV('LANG')
1220 AND disp.part_change_id IS NOT NULL
1221 AND disp.workorder_id = p_workorder_id
1222 AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE','TERMINATED'))
1223 AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
1224 AND disp.immediate_disposition_code = FND1.LOOKUP_CODE (+)
1225 AND FND1.LANGUAGE(+) = USERENV('LANG')
1226 AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
1227 AND disp.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
1228 AND FND2.LANGUAGE(+) = USERENV('LANG')
1229 AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
1230 AND disp.STATUS_CODE = FND3.LOOKUP_CODE (+)
1231 AND FND3.LANGUAGE(+) = USERENV('LANG')
1232 AND disp.condition_id = COND.status_id (+)
1233 AND COND.language(+) = USERENV('LANG')
1234 AND disp.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
1235 AND disp.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
1236 AND disp.ORGANIZATION_ID = MTL.organization_id (+)
1237 AND disp.INSTANCE_ID = CSI.INSTANCE_ID (+)
1238 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
1239 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
1240 AND SEV.language(+) = USERENV('LANG')
1241 AND INCL.INCIDENT_ID (+) = INC.INCIDENT_ID
1242 AND INCL.language(+) = USERENV('LANG')
1243 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
1244 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
1245 AND PCODE.language(+) = USERENV('LANG')
1246 AND disp.part_change_id = PC.part_change_id
1247 AND PC.removed_instance_id IS NOT NULL
1248 AND PC.installed_part_change_id IS NULL;
1249
1250 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
1251 -- Cursor to get all the non-complete and non-terminated dispositions matching the workorder_id and instance_id.
1252 -- Used for supporting IB trees and hierarchies.
1253 -- NOTE: If need be, then the cursor query can be tuned for better performance on the same lines as the other cursors above.
1254 CURSOR get_instance_disposition_csr (p_workorder_id IN NUMBER,
1255 p_instance_id IN NUMBER) IS
1256 SELECT disp.disposition_id,
1257 disp.object_version_number,
1258 disp.last_update_date,
1259 disp.last_updated_by,
1260 disp.creation_date,
1261 disp.created_by,
1262 disp.last_update_login,
1263 disp.workorder_id,
1264 disp.part_change_id,
1265 disp.path_position_id,
1266 disp.item_number,
1267 disp.inventory_item_id,
1268 disp.organization_id,
1269 disp.instance_number,
1270 disp.instance_id,
1271 disp.item_group_id,
1272 disp.item_group_name,
1273 disp.serial_number,
1274 disp.lot_number,
1275 disp.quantity,
1276 disp.uom,
1277 disp.condition_id,
1278 disp.condition_code,
1279 disp.immediate_disposition_code,
1280 disp.immediate_type,
1281 disp.secondary_disposition_code,
1282 disp.secondary_type,
1283 disp.status_code,
1284 disp.status,
1285 disp.collection_id,
1286 disp.primary_service_request_id,
1287 disp.non_routine_workorder_id,
1288 disp.comments,
1289 INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
1290 SEV.NAME SEVERITY_NAME,
1291 INC.PROBLEM_CODE,
1292 PCODE.MEANING PROBLEM_MEANING,
1293 INC.SUMMARY
1294 FROM AHL_PRD_DISPOSITIONS_V disp, CS_INCIDENTS_ALL_VL INC,
1295 CS_INCIDENT_SEVERITIES_VL SEV, FND_LOOKUP_VALUES_VL PCODE
1296 WHERE disp.workorder_id = p_workorder_id
1297 AND disp.instance_id = p_instance_id
1298 AND (disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
1299 AND disp.part_change_id IS NULL
1300 AND disp.path_position_id IS NULL
1301 AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
1302 AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
1303 AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
1304 AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE;
1305
1306 --
1307 l_api_version CONSTANT NUMBER := 1.0;
1308 l_api_name CONSTANT VARCHAR2(30) := 'Get_Part_Change_Disposition';
1309 l_path_position_ID NUMBER;
1310 l_csi_instance_id NUMBER;
1311 l_disp_rec get_rem_disposition_csr%ROWTYPE;
1312 l_found_flag boolean;
1313
1314 l_temp_disp_rec get_rem_disposition_csr%ROWTYPE;
1315
1316 --
1317 BEGIN
1318 -- Standard start of API savepoint
1319 SAVEPOINT Get_Part_Change_Disp_Pvt;
1320
1321 -- Initialize Procedure return status to success
1322 x_return_status := FND_API.G_RET_STS_SUCCESS;
1323 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1325 G_LOG_PREFIX||l_api_name||': Begin API',
1326 'Entering the Procedure.');
1327 END IF;
1328
1329 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1330 FND_MSG_PUB.initialize;
1331 END IF;
1332
1333 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1335 G_LOG_PREFIX||l_api_name,
1336 'About to call AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID with ' ||
1337 'p_csi_item_instance_id = ' || p_parent_instance_id ||
1338 ', p_relationship_id = ' || p_relationship_id);
1339 END IF;
1340
1341 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
1342 -- Check for p_relationship_id.
1343 IF (p_relationship_id IS NULL) THEN
1344 OPEN get_instance_disposition_csr (p_workorder_id, p_instance_id);
1345 FETCH get_instance_disposition_csr into l_disp_rec;
1346
1347 IF (get_instance_disposition_csr%FOUND) THEN
1348 l_found_flag := true;
1349 FETCH get_instance_disposition_csr into l_temp_disp_rec;
1350 -- If more than one matching disposition: Pass -1 so that Part Change can handle this appropriately.
1351 IF (get_instance_disposition_csr%FOUND) THEN
1352 x_disposition_rec.disposition_id := -1;
1353 END IF;
1354 ELSE
1355 l_found_flag := false;
1356 END IF;
1357 CLOSE get_instance_disposition_csr;
1358 ELSE
1359
1360 -- find the path_position_id for the position
1361 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID (
1362 p_api_version => 1.0,
1363 x_return_status => x_return_status,
1364 x_msg_count => x_msg_count,
1365 x_msg_data => x_msg_data,
1366 p_csi_item_instance_id => p_parent_instance_id,
1367 p_relationship_id => p_relationship_id,
1368 x_path_position_id => l_path_position_id);
1369
1370 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1372 G_LOG_PREFIX||l_api_name,
1373 'Returned from call to AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID.' ||
1374 ' x_path_position_id = ' || l_path_position_id);
1375 END IF;
1376 l_found_flag := false;
1377 --dbms_output.put_line ('POS ID'||l_path_position_id);
1378 --Check whether the position is installed or not
1379 OPEN check_installed_csr (p_parent_instance_id, p_relationship_id);
1380 FETCH check_installed_csr into l_csi_instance_id;
1381 --If installed
1382 IF (check_installed_csr%FOUND) THEN
1383 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1385 G_LOG_PREFIX||l_api_name,
1386 'Instance is installed. Getting disposition for ' ||
1387 ' l_path_position_id = ' || l_path_position_id ||
1388 ' and l_csi_instance_id = ' || l_csi_instance_id);
1389 END IF;
1390 --Fetch the disposition for Part Removal
1391 OPEN get_rem_disposition_csr (l_path_position_id, l_csi_instance_id);
1392 FETCH get_rem_disposition_csr INTO l_disp_rec;
1393 --Check if matching disposition is found..
1394 IF (get_rem_disposition_csr%FOUND) THEN
1395 l_found_flag := true;
1396
1397 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
1398 FETCH get_rem_disposition_csr INTO l_temp_disp_rec;
1399 -- If more than one matching disposition: Pass -1 so that Part Change can handle this appropriately.
1400 IF (get_rem_disposition_csr%FOUND) THEN
1401 x_disposition_rec.disposition_id := -1;
1402 END IF;
1403 END IF;
1404 CLOSE get_rem_disposition_csr;
1405 ELSE
1406 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1407 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1408 G_LOG_PREFIX||l_api_name,
1409 'Instance is NOT installed. Getting disposition for ' ||
1410 'l_path_position_id = ' || l_path_position_id);
1411 END IF;
1412 OPEN get_inst_disposition_csr (l_path_position_id);
1413 FETCH get_inst_disposition_csr INTO l_disp_rec;
1414 --Check if matching disposition is found..
1415 IF (get_inst_disposition_csr%FOUND) THEN
1416 l_found_flag := true;
1417 ELSE
1418 OPEN get_inst_disposition_csr1 (l_path_position_id);
1419 FETCH get_inst_disposition_csr1 INTO l_disp_rec;
1420 IF (get_inst_disposition_csr1%FOUND) THEN
1421 l_found_flag := true;
1422 END IF;
1423 CLOSE get_inst_disposition_csr1;
1424 END IF;
1425 CLOSE get_inst_disposition_csr;
1426 END IF;
1427 CLOSE check_installed_csr;
1428 END IF; -- (p_relationship_id IS NULL)
1429
1430 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
1431 -- Check for the disposition id not being set as -1 in the above checks.
1432 IF (l_found_flag = true AND (NVL(x_disposition_rec.disposition_id,0) <> -1)) THEN
1433 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1434 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1435 G_LOG_PREFIX||l_api_name,
1436 'Found matching Disposition. l_disp_rec.disposition_id = ' || l_disp_rec.disposition_id);
1437 END IF;
1438 x_disposition_rec.disposition_id := l_disp_rec.disposition_id;
1439 --x_disposition_rec.object_version_number:=x_disposition_rec.object_version_number;
1440 x_disposition_rec.object_version_number:= l_disp_rec.object_version_number;
1441 --x_disposition_rec.LAST_UPDATE_DATE := x_disposition_rec.LAST_UPDATE_DATE;
1442 x_disposition_rec.LAST_UPDATE_DATE := l_disp_rec.LAST_UPDATE_DATE;
1443 x_disposition_rec.LAST_UPDATED_BY :=l_disp_rec.LAST_UPDATED_BY;
1444 x_disposition_rec.CREATION_DATE:=l_disp_rec.CREATION_DATE;
1445 x_disposition_rec.CREATED_BY:=l_disp_rec.CREATED_BY;
1446 x_disposition_rec.LAST_UPDATE_LOGIN:=l_disp_rec.LAST_UPDATE_LOGIN;
1447 x_disposition_rec.WORKORDER_ID:=l_disp_rec.WORKORDER_ID;
1448 x_disposition_rec.PART_CHANGE_ID:=l_disp_rec.PART_CHANGE_ID;
1449 x_disposition_rec.PATH_POSITION_ID:=l_disp_rec.PATH_POSITION_ID;
1450 x_disposition_rec.INVENTORY_ITEM_ID:=l_disp_rec.INVENTORY_ITEM_ID;
1451 x_disposition_rec.ITEM_ORG_ID:=l_disp_rec.ORGANIZATION_ID;
1452 x_disposition_rec.ITEM_GROUP_ID:=l_disp_rec.ITEM_GROUP_ID;
1453 x_disposition_rec.CONDITION_ID:=l_disp_rec.CONDITION_ID;
1454 x_disposition_rec.INSTANCE_ID:=l_disp_rec.INSTANCE_ID;
1455 x_disposition_rec.COLLECTION_ID:=l_disp_rec.COLLECTION_ID;
1456 x_disposition_rec.PRIMARY_SERVICE_REQUEST_ID:=l_disp_rec.PRIMARY_SERVICE_REQUEST_ID;
1457 x_disposition_rec.NON_ROUTINE_WORKORDER_ID:=l_disp_rec.NON_ROUTINE_WORKORDER_ID;
1458 x_disposition_rec.SERIAL_NUMBER:=l_disp_rec.SERIAL_NUMBER;
1459 x_disposition_rec.LOT_NUMBER:=l_disp_rec.LOT_NUMBER;
1460 x_disposition_rec.IMMEDIATE_DISPOSITION_CODE:=l_disp_rec.IMMEDIATE_DISPOSITION_CODE;
1461 x_disposition_rec.SECONDARY_DISPOSITION_CODE:=l_disp_rec.SECONDARY_DISPOSITION_CODE;
1462 x_disposition_rec.STATUS_CODE:=l_disp_rec.STATUS_CODE;
1463 x_disposition_rec.QUANTITY:=l_disp_rec.QUANTITY;
1464 x_disposition_rec.UOM:=l_disp_rec.UOM;
1465 x_disposition_rec.COMMENTS:= l_disp_rec.comments;
1466 x_disposition_rec.SEVERITY_ID:=l_disp_rec.SEVERITY_ID;
1467 x_disposition_rec.PROBLEM_CODE:=l_disp_rec.PROBLEM_CODE;
1468 x_disposition_rec.SUMMARY:=l_disp_rec.SUMMARY;
1469 x_disposition_rec.IMMEDIATE_DISPOSITION:=l_disp_rec.IMMEDIATE_TYPE;
1470 x_disposition_rec.SECONDARY_DISPOSITION:=l_disp_rec.SECONDARY_TYPE;
1471 x_disposition_rec.CONDITION_MEANING:=l_disp_rec.CONDITION_CODE;
1472 x_disposition_rec.INSTANCE_NUMBER:=l_disp_rec.INSTANCE_NUMBER;
1473 x_disposition_rec.ITEM_NUMBER:=l_disp_rec.ITEM_NUMBER;
1474 x_disposition_rec.ITEM_GROUP_NAME:=l_disp_rec.ITEM_GROUP_NAME;
1475 x_disposition_rec.DISPOSITION_STATUS:=l_disp_rec.STATUS;
1476 x_disposition_rec.SEVERITY_NAME:=l_disp_rec.SEVERITY_NAME;
1477 x_disposition_rec.PROBLEM_MEANING:=l_disp_rec.PROBLEM_MEANING;
1478
1479 Ahl_prd_disp_util_pvt.Get_Available_Disp_Types (
1480 p_api_version => 1.0,
1481 p_commit => FND_API.G_FALSE,
1482 x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data,
1485 p_disposition_id => x_disposition_rec.disposition_id,
1486 x_imm_disp_type_tbl => x_imm_disp_type_tbl,
1487 x_sec_disp_type_tbl => x_sec_disp_type_tbl);
1488 ELSE
1489 --No matching disposition, must create new.
1490 --x_disposition_rec is null
1491 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1493 G_LOG_PREFIX||l_api_name,
1494 'Could not find any matching Disposition.');
1495 END IF;
1496 Ahl_prd_disp_util_pvt.Get_Available_Disp_Types (
1497 p_api_version => 1.0,
1498 p_commit => FND_API.G_FALSE,
1499 x_return_status => x_return_status,
1500 x_msg_count => x_msg_count,
1501 x_msg_data => x_msg_data,
1502 p_disposition_id => null,
1503 x_imm_disp_type_tbl => x_imm_disp_type_tbl,
1504 x_sec_disp_type_tbl => x_sec_disp_type_tbl);
1505 END IF;
1506
1507 EXCEPTION
1508 WHEN FND_API.G_EXC_ERROR THEN
1509 Rollback to Get_Part_Change_Disp_Pvt;
1510 x_return_status := FND_API.G_RET_STS_ERROR;
1511 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1512 p_data => x_msg_data,
1513 p_encoded => fnd_api.g_false);
1514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515 Rollback to Get_Part_Change_Disp_Pvt;
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1518 p_data => x_msg_data,
1519 p_encoded => fnd_api.g_false);
1520 WHEN OTHERS THEN
1521 Rollback to Get_Part_Change_Disp_Pvt;
1522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1524 p_procedure_name => l_api_name,
1525 p_error_text => SQLERRM);
1526 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1527 p_data => x_msg_data,
1528 p_encoded => fnd_api.g_false);
1529 END Get_Part_Change_Disposition;
1530
1531
1532
1533 ------------------------
1534 -- Start of Comments --
1535 -- Procedure name : Get_Available_Disp_Types
1536 -- Type : Private
1537 -- Function : Fetch the available disposition types for given disposition
1538 -- Pre-reqs :
1539 -- Parameters : p_disposition_id: The disposition id to fetch against
1540 -- x_imm_disp_type_tbl: returning immediate disposition type
1541 -- x_sec_disp_type_tbl: returning secondary dispositions
1542 --
1543 --
1544 -- End of Comments.
1545
1546 PROCEDURE Get_Available_Disp_Types (
1547 p_api_version IN NUMBER,
1548 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1549 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1550 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1551 x_return_status OUT NOCOPY VARCHAR2,
1552 x_msg_count OUT NOCOPY NUMBER,
1553 x_msg_data OUT NOCOPY VARCHAR2,
1554 p_disposition_id IN NUMBER,
1555 x_imm_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type,
1556 x_sec_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type)
1557 IS
1558 --
1559 --AnRaj: Changed for Performance Bug#4911881
1560 CURSOR get_disp_rec_csr (p_disp_id IN NUMBER) IS
1561 /* SELECT *
1562 FROM AHL_PRD_DISPOSITIONS_V
1563 WHERE disposition_id = p_disp_id;
1564 */
1565 SELECT B.status_code,
1566 B.immediate_disposition_code,
1567 FND1.MEANING IMMEDIATE_TYPE,
1568 B.secondary_disposition_code,
1569 FND2.MEANING SECONDARY_TYPE,
1570 B.part_change_id,
1571 decode(B.instance_id, null, decode(B.path_position_id, null, 'N', 'Y'), 'Y') TRACKABLE_FLAG
1572 FROM AHL_PRD_DISPOSITIONS_B B, FND_LOOKUPS FND1, FND_LOOKUPS FND2
1573 WHERE FND1.LOOKUP_TYPE (+) = 'AHL_IMMED_DISP_TYPE'
1574 AND B.immediate_disposition_code = FND1.LOOKUP_CODE (+)
1575 AND FND2.LOOKUP_TYPE (+) = 'AHL_SECND_DISP_TYPE'
1576 AND B.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
1577 AND B.disposition_id = p_disp_id;
1578 --
1579 CURSOR get_immed_disp_types_csr IS
1580 SELECT lookup_code, meaning
1581 FROM fnd_lookups
1582 WHERE lookup_type = 'AHL_IMMED_DISP_TYPE'
1583 AND Lookup_code <> 'NULL';
1584 --
1585 CURSOR get_second_disp_types_csr IS
1586 SELECT lookup_code, meaning
1587 FROM fnd_lookups
1588 WHERE lookup_type = 'AHL_SECND_DISP_TYPE'
1589 AND Lookup_code <> 'NULL';
1590
1591 --
1592 l_api_version CONSTANT NUMBER := 1.0;
1593 l_api_name CONSTANT VARCHAR2(30) := 'Get_Avail_Disp_Type';
1594 l_disp_rec get_disp_rec_csr%ROWTYPE;
1595 l_type_rec AHL_PRD_DISP_UTIL_PVT.Disp_Type_Rec_Type;
1596 i NUMBER;
1597 --
1598 BEGIN
1599
1600 -- Initialize Procedure return status to success
1601 x_return_status := FND_API.G_RET_STS_SUCCESS;
1602
1603 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1604 FND_MSG_PUB.initialize;
1605 END IF;
1606
1607 i:=0;
1608 --Fetch the current disposition types or ids
1609 OPEN get_disp_rec_csr (p_disposition_id);
1610 FETCH get_disp_rec_csr INTO l_disp_rec;
1611
1612 --dbms_output.put_line('status => ' ||l_disp_rec.status_code);
1613 --dbms_output.put_line('imm_type => ' ||l_disp_rec.immediate_disposition_code);
1614 --dbms_output.put_line('secondary_type => ' ||l_disp_rec.secondary_disposition_code);
1615
1616 IF (get_disp_rec_csr%FOUND) THEN
1617 --For No change in immed. or secondary type
1618 IF (l_disp_rec.status_code = 'TERMINATED' OR
1619 --l_disp_rec.immediate_disposition_code ='NOT_RECEIVED' OR
1620 --Jerry commented out the above line on 01/17/2005 for fixing bug 4094927
1621 l_disp_rec.immediate_disposition_code ='SCRAP' OR
1622 (l_disp_rec.status_code ='COMPLETE' AND --Complete but not NR,NA
1623 l_disp_rec.immediate_disposition_code NOT IN ('NOT_REMOVED','NA'))) THEN
1624
1625 IF (l_disp_rec.immediate_disposition_code IS NOT NULL and
1626 l_disp_rec.immediate_type IS NOT NULL) THEN
1627 l_type_rec.code := l_disp_rec.immediate_disposition_code;
1628 l_type_rec.meaning := l_disp_rec.immediate_type;
1629 x_imm_disp_type_tbl(0):= l_type_rec;
1630 END IF;
1631
1632 IF (l_disp_rec.secondary_disposition_code IS NOT NULL and
1633 l_disp_rec.secondary_type IS NOT NULL) THEN
1634 l_type_rec.code := l_disp_rec.secondary_disposition_code;
1635 l_type_rec.meaning := l_disp_rec.secondary_type;
1636 x_sec_disp_type_tbl(0):= l_type_rec;
1637 END IF;
1638
1639 --immediate is fixed.
1640 ELSIF(l_disp_rec.immediate_disposition_code IN ('NON_CONF','BFS')) THEN
1641
1642 -- Immediate disposition is fixed.
1643 IF (l_disp_rec.immediate_disposition_code IS NOT NULL and
1644 l_disp_rec.immediate_type IS NOT NULL) THEN
1645 l_type_rec.code := l_disp_rec.immediate_disposition_code;
1646 l_type_rec.meaning := l_disp_rec.immediate_type;
1647 x_imm_disp_type_tbl(0):= l_type_rec;
1648 END IF;
1649
1650 --For SCRAP, restrict secondary disposition
1651 IF (l_disp_rec.secondary_disposition_code = 'SCRAP' ) THEN
1652 IF (l_disp_rec.secondary_disposition_code IS NOT NULL and
1653 l_disp_rec.secondary_type IS NOT NULL) THEN
1654 l_type_rec.code := l_disp_rec.secondary_disposition_code;
1655 l_type_rec.meaning := l_disp_rec.secondary_type;
1656 x_sec_disp_type_tbl(0):= l_type_rec;
1657 END IF;
1658
1659 ELSE
1660 i:=0;
1661 OPEN get_second_disp_types_csr;
1662 LOOP
1663 FETCH get_second_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1664 EXIT WHEN get_second_disp_types_csr%NOTFOUND;
1665 --All others are valid.
1666 --For Rework-NR, it's either not trackable or not removed
1667 --IF (l_type_rec.CODE <> 'REWORK_NR' OR
1668 -- (l_type_rec.CODE = 'REWORK_NR' AND
1669 -- (l_disp_rec.trackable_flag <> 'Y' OR
1670 -- l_disp_rec.part_change_id IS NULL))) THEN
1671 -- Jerry update the condition on 02/17/2005 for fixing bug 4189553
1672 IF ((l_type_rec.code <> 'REWORK_NR' AND l_type_rec.code <> 'REWORK_RR') OR
1673 (l_type_rec.code = 'REWORK_NR' AND l_disp_rec.trackable_flag = 'Y'
1674 AND l_disp_rec.part_change_id IS NULL) OR
1675 (l_type_rec.code = 'REWORK_RR' AND l_disp_rec.trackable_flag = 'Y')) THEN
1676 x_sec_disp_type_tbl(i) := l_type_rec;
1677 i:=i+1;
1678 END IF;
1679 END LOOP;
1680 CLOSE get_second_disp_types_csr;
1681 END IF;
1682
1683 ELSIF(l_disp_rec.immediate_disposition_code IN ('USE_AS_IS','RTV','RTC')) THEN
1684
1685 i:=0;
1686 --Return all immediate except NRemoved,NA,NReceived
1687 OPEN get_immed_disp_types_csr;
1688 LOOP
1689 FETCH get_immed_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1690 EXIT WHEN get_immed_disp_types_csr%NOTFOUND;
1691 IF (l_type_rec.code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')) THEN
1692 x_imm_disp_type_tbl(i) := l_type_rec;
1693 i:=i+1;
1694 END IF;
1695 END LOOP;
1696 CLOSE get_immed_disp_types_csr;
1697
1698 i:=0;
1699 OPEN get_second_disp_types_csr;
1700 LOOP
1701 FETCH get_second_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1702 EXIT WHEN get_second_disp_types_csr%NOTFOUND;
1703 x_sec_disp_type_tbl(i) := l_type_rec;
1704 i:=i+1;
1705 END LOOP;
1706 CLOSE get_second_disp_types_csr;
1707
1708 ELSIF(l_disp_rec.immediate_disposition_code IN ('NOT_REMOVED', 'NA')) THEN
1709
1710 i:=0;
1711 --Return all immediate except Not Received
1712 OPEN get_immed_disp_types_csr;
1713 LOOP
1714 FETCH get_immed_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1715 EXIT WHEN get_immed_disp_types_csr%NOTFOUND;
1716 IF (l_type_rec.code NOT IN ('NOT_RECEIVED')) THEN
1717 x_imm_disp_type_tbl(i) := l_type_rec;
1718 i:=i+1;
1719 END IF;
1720 END LOOP;
1721 CLOSE get_immed_disp_types_csr;
1722 ELSE
1723 --Return every disposition type case
1724 i:=0;
1725 OPEN get_immed_disp_types_csr;
1726 LOOP
1727 FETCH get_immed_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1728 EXIT WHEN get_immed_disp_types_csr%NOTFOUND;
1729 x_imm_disp_type_tbl(i) := l_type_rec;
1730 i:=i+1;
1731 END LOOP;
1732 CLOSE get_immed_disp_types_csr;
1733
1734 --Fetch the secondary disposition types
1735 i:=0;
1736 OPEN get_second_disp_types_csr;
1737 LOOP
1738 FETCH get_second_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1739 EXIT WHEN get_second_disp_types_csr%NOTFOUND;
1740 x_sec_disp_type_tbl(i) := l_type_rec;
1741 i:=i+1;
1742 END LOOP;
1743 CLOSE get_second_disp_types_csr;
1744 END IF;
1745 ELSE
1746 --Return every disposition type case when disp view is not found
1747 i:=0;
1748 OPEN get_immed_disp_types_csr;
1749 LOOP
1750 FETCH get_immed_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1751 EXIT WHEN get_immed_disp_types_csr%NOTFOUND;
1752 x_imm_disp_type_tbl(i) := l_type_rec;
1753 i:=i+1;
1754 END LOOP;
1755 CLOSE get_immed_disp_types_csr;
1756
1757 --Fetch the secondary disposition types
1758 i:=0;
1759 OPEN get_second_disp_types_csr;
1760 LOOP
1761 FETCH get_second_disp_types_csr INTO l_type_rec.CODE, l_type_rec.meaning;
1762 EXIT WHEN get_second_disp_types_csr%NOTFOUND;
1763 x_sec_disp_type_tbl(i) := l_type_rec;
1764 i:=i+1;
1765 END LOOP;
1766 CLOSE get_second_disp_types_csr;
1767 END IF;
1768 CLOSE get_disp_rec_csr;
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1773 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1774 p_procedure_name => l_api_name,
1775 p_error_text => SQLERRM);
1776 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1777 p_data => x_msg_data,
1778 p_encoded => fnd_api.g_false);
1779 END Get_Available_Disp_Types;
1780
1781
1782 -- Function to determine if the incident specified is the
1783 -- primary Non Conformance for the disposition specified.
1784 -- If it is the primary NC, 'Y' is returned.
1785 -- If not, 'N' is returned.
1786 -- 'N' is returned in case of any invalid inputs also.
1787 FUNCTION Get_Primary_SR_Flag(p_disposition_id IN NUMBER,
1788 p_incident_id IN NUMBER)
1789 RETURN VARCHAR2
1790 IS
1791 CURSOR get_primary_sr_csr IS
1792 SELECT PRIMARY_SERVICE_REQUEST_ID
1793 FROM AHL_PRD_DISPOSITIONS_B
1794 WHERE DISPOSITION_ID = p_disposition_id;
1795
1796 l_primary_sr_id NUMBER;
1797 l_return_value VARCHAR2(1);
1798
1799 L_DEBUG_KEY CONSTANT VARCHAR2(150) :=
1800 'ahl.plsql.AHL_PRD_DISPOSITIONS_UTIL.Get_Primary_SR_Flag';
1801
1802 BEGIN
1803 OPEN get_primary_sr_csr;
1804 FETCH get_primary_sr_csr INTO l_primary_sr_id;
1805 IF(get_primary_sr_csr%NOTFOUND) THEN
1806 l_return_value := 'N';
1807 ELSIF (l_primary_sr_id = p_incident_id) THEN
1808 l_return_value := 'Y';
1809 ELSE
1810 l_return_value := 'N';
1811 END IF;
1812 CLOSE get_primary_sr_csr;
1813
1814 RETURN l_return_value;
1815 END Get_Primary_SR_Flag;
1816
1817 -- Start of Comments --
1818 -- Procedure name : Create_Disp_Mtl_Requirement
1819 -- Type : Private
1820 -- Function : Private API to create a Material requirements for a Disposition.
1821 -- If the disposition has neither an item nor a Position Path, an
1822 -- exception is raised. If the disposition is for a position that is
1823 -- empty, this API gets the item group for the position and picks one
1824 -- item from the item group and creates a material requirement for that item.
1825 -- If the requirement was created successfully, a message is returned
1826 -- via x_msg_data indicating the item, the quantity and the UOM of the
1827 -- requirement created.
1828 -- Pre-reqs :
1829 -- Parameters :
1830 --
1831 -- Standard IN Parameters :
1832 -- p_api_version IN NUMBER Required
1833 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1834 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1835 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1836 --
1837 -- Standard OUT Parameters :
1838 -- x_return_status OUT VARCHAR2 Required
1839 -- x_msg_count OUT NUMBER Required
1840 -- x_msg_data OUT VARCHAR2 Required
1841 --
1842 -- Create_Disp_Mtl_Requirement Parameters:
1843 -- p_disposition_id IN NUMBER Required
1844 -- The Id of disposition for which to create the material requirement.
1845 --
1846 -- Version :
1847 -- Initial Version 1.0
1848 --
1849 -- End of Comments.
1850
1851
1852 PROCEDURE Create_Disp_Mtl_Requirement (
1853 p_api_version IN NUMBER,
1854 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1855 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1856 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1857 x_return_status OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY NUMBER,
1859 x_msg_data OUT NOCOPY VARCHAR2,
1860 p_disposition_id IN NUMBER
1861 ) IS
1862
1863 --Begin Performance Tuning fix
1864 CURSOR get_disp_dtls_csr(p_disp_id IN NUMBER) IS
1865 /*
1866 SELECT disp.inventory_item_id, disp.item_number, disp.workorder_id,
1867 vi.organization_id, disp.path_position_id, disp.quantity, disp.uom,
1868 disp.wo_operation_id
1869 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_WORKORDERS wo, AHL_VISITS_B VI
1870 --the organization_id in ahl_prd_dispostions_b is not necessary to be the same as the job org,
1871 --especially when the disposition is for a non empty position(instance''s last_vld_org_id), but
1872 --here we need the job organization.
1873 WHERE disp.workorder_id = wo.workorder_id
1874 and wo.visit_id = vi.visit_id
1875 AND disp.disposition_id = p_disp_id
1876 */
1877 select disp.inventory_item_id,
1878 mtl.concatenated_segments item_number,
1879 disp.workorder_id,
1880 vi.organization_id,
1881 disp.path_position_id,
1882 disp.quantity,
1883 disp.uom,
1884 disp.wo_operation_id
1885 from ahl_prd_dispositions_vl disp,
1886 mtl_system_items_kfv mtl,
1887 ahl_workorders wo,
1888 ahl_visits_b vi
1889 where disp.workorder_id = wo.workorder_id
1890 and wo.visit_id = vi.visit_id
1891 and disp.disposition_id = p_disp_id
1892 AND disp.inventory_item_id = mtl.inventory_item_id(+)
1893 AND disp.organization_id = mtl.organization_id (+);
1894
1895 /*
1896 SELECT disp.inventory_item_id, disp.item_number, disp.workorder_id,
1897 wo.organization_id, disp.path_position_id, disp.quantity, disp.uom,
1898 disp.wo_operation_id
1899 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_WORKORDERS_V wo
1900 --the organization_id in ahl_prd_dispostions_b is not necessary to be the same as the job org,
1901 --especially when the disposition is for a non empty position(instance's last_vld_org_id), but
1902 --here we need the job organization.
1903 WHERE disp.workorder_id = wo.workorder_id
1904 AND disposition_id = p_disp_id;*/
1905 --End Performance Tuning fix
1906
1907
1908 CURSOR get_relnship_id_csr (p_path_position_id IN NUMBER) IS
1909 SELECT rel.relationship_id
1910 FROM AHL_MC_RELATIONSHIPS rel, AHL_MC_HEADERS_B mc, AHL_MC_PATH_POSITION_NODES node
1911 WHERE rel.mc_header_id = mc.mc_header_id
1912 AND node.position_key = rel.position_key
1913 AND node.mc_id = mc.mc_id
1914 AND nvl(node.version_number, mc.version_number) = mc.version_number
1915 AND node.path_position_id = p_path_position_id
1916 AND node.sequence = (select max(sequence)
1917 from AHL_MC_PATH_POSITION_NODES
1918 WHERE path_position_id = p_path_position_id)
1919 ORDER by mc.version_number desc;
1920
1921 --
1922 --Based on the position, fetch the highest priority item from item group.
1923 CURSOR get_empty_pos_item_csr(p_relationship_id IN NUMBER,
1924 p_org_id IN NUMBER ) IS
1925 SELECT ia.inventory_item_id, mtl.concatenated_segments, ia.quantity, ia.uom_code
1926 FROM AHL_ITEM_ASSOCIATIONS_B ia, MTL_SYSTEM_ITEMS_KFV mtl,
1927 AHL_MC_RELATIONSHIPS rel, MTL_PARAMETERS morgs
1928 WHERE ia.inventory_item_id = mtl.inventory_item_id
1929 AND mtl.organization_id = p_org_id --Make sure item is defined for p_org_id
1930 AND ia.inventory_org_id = morgs.master_organization_id
1931 AND morgs.organization_id = p_org_id
1932 AND ia.item_group_id = rel.item_group_id
1933 AND rel.relationship_id = p_relationship_id
1934 AND ia.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1935 ORDER by ia.priority asc;
1936
1937 --
1938 -- look into sub-unit top nodes for best matched item.
1939 CURSOR get_pos_subut_item_csr(p_relationship_id IN NUMBER,
1940 p_org_id IN NUMBER ) IS
1941 SELECT ia.inventory_item_id, mtl.concatenated_segments, ia.quantity, ia.uom_code
1942 FROM AHL_ITEM_ASSOCIATIONS_B ia, MTL_SYSTEM_ITEMS_KFV mtl,
1943 AHL_MC_RELATIONSHIPS rel, AHL_MC_CONFIG_RELATIONS crel, MTL_PARAMETERS morgs
1944 WHERE ia.inventory_item_id = mtl.inventory_item_id
1945 AND mtl.organization_id = p_org_id --Make sure item is defined for p_org_id
1946 AND ia.inventory_org_id = morgs.master_organization_id
1947 AND morgs.organization_id = p_org_id
1948 AND ia.item_group_id = rel.item_group_id
1949 AND rel.mc_header_id = crel.mc_header_id
1950 AND rel.parent_relationship_id IS NULL
1951 AND crel.relationship_id = p_relationship_id
1952 AND ia.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1953 ORDER by crel.priority asc, ia.priority asc;
1954
1955 --Fetch the 1st open workorder operation (not complete) for workorder
1956 CURSOR get_workorder_oper_csr (p_workorder_id IN NUMBER) IS
1957 SELECT workorder_operation_id
1958 FROM AHL_WORKORDER_OPERATIONS
1959 WHERE workorder_id = p_workorder_id
1960 AND status_code <> G_WO_STATUS_COMPLETE
1961 ORDER BY operation_sequence_num asc;
1962
1963 --
1964 --Check whether the material requirement for the same item has already existed
1965 CURSOR check_mtl_requirement(c_item_id NUMBER, c_org_id NUMBER,
1966 c_operation_id NUMBER) IS
1967 SELECT scheduled_material_id,
1968 object_version_number,
1969 requested_quantity,
1970 uom,
1971 inventory_item_id
1972 FROM AHL_SCHEDULE_MATERIALS
1973 WHERE inventory_item_id = c_item_id
1974 AND workorder_operation_id = c_operation_id
1975 AND organization_id = c_org_id
1976 AND requested_quantity <> 0
1977 AND status = 'ACTIVE';
1978 l_mtl_requirement check_mtl_requirement%ROWTYPE;
1979
1980 l_api_version CONSTANT NUMBER := 1.0;
1981 l_api_name CONSTANT VARCHAR2(30) := 'Create_Disp_Mtl_Requirement';
1982 l_req_material_rec AHL_PP_Materials_PVT.req_material_rec_type;
1983 l_req_material_tbl AHL_PP_Materials_PVT.req_material_tbl_type;
1984 l_req_material_u_rec AHL_PP_Materials_PVT.req_material_rec_type;
1985 l_req_material_u_tbl AHL_PP_Materials_PVT.req_material_tbl_type;
1986 l_disp_rec get_disp_dtls_csr%ROWTYPE;
1987 l_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1988 l_relationship_id NUMBER;
1989 l_job_return_status VARCHAR2(1);
1990 l_delta_quantity NUMBER;
1991 -- Added on 1/8/05 by JR to fix Bug 4097327
1992 -- To give a different confirmation message for update
1993 l_update_flag BOOLEAN := false;
1994
1995
1996 --
1997 BEGIN
1998
1999 -- Standard start of API savepoint
2000 SAVEPOINT Create_Disp_Mtl_Req_Pvt;
2001
2002 -- Initialize Procedure return status to success
2003 x_return_status := FND_API.G_RET_STS_SUCCESS;
2004
2005 IF (FND_API.to_boolean(p_init_msg_list)) THEN
2006 FND_MSG_PUB.initialize;
2007 END IF;
2008
2009 --Fetch the disposition for Part Removal
2010 OPEN get_disp_dtls_csr (p_disposition_id);
2011 FETCH get_disp_dtls_csr INTO l_disp_rec;
2012 --Check if matching disposition is found..
2013 IF (get_disp_dtls_csr%NOTFOUND) THEN
2014 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_ID_INVALID');
2015 FND_MESSAGE.Set_Token('DISPOSITION_ID', p_disposition_id);
2016 FND_MSG_PUB.ADD;
2017 CLOSE get_disp_dtls_csr;
2018 RAISE FND_API.G_EXC_ERROR;
2019 END IF;
2020 CLOSE get_disp_dtls_csr;
2021
2022 --Throw an error if both item and position are null
2023 IF (l_disp_rec.inventory_item_id IS NULL AND
2024 l_disp_rec.path_position_id IS NULL) THEN
2025 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_ITEM_POS_NULL');
2026 FND_MSG_PUB.ADD;
2027 RAISE FND_API.G_EXC_ERROR;
2028 ELSIF (l_disp_rec.inventory_item_id IS NULL AND
2029 l_disp_rec.path_position_id IS NOT NULL) THEN
2030 --get the position relationship id (Use the 1st one)
2031 OPEN get_relnship_id_csr (l_disp_rec.path_position_id);
2032 FETCH get_relnship_id_csr INTO l_relationship_id;
2033 CLOSE get_relnship_id_csr;
2034
2035 l_req_material_rec.organization_id := l_disp_rec.organization_id;
2036
2037 --Now fetch the item informations from item group of the position.
2038 OPEN get_empty_pos_item_csr (l_relationship_id, l_disp_rec.organization_id);
2039 FETCH get_empty_pos_item_csr INTO l_req_material_rec.inventory_item_id,
2040 l_item_number,
2041 l_disp_rec.quantity,
2042 l_disp_rec.uom;
2043
2044 --If no item group at position, check the subconfig associations.
2045 IF (get_empty_pos_item_csr%NOTFOUND) THEN
2046 OPEN get_pos_subut_item_csr (l_relationship_id, l_disp_rec.organization_id);
2047 FETCH get_pos_subut_item_csr INTO l_req_material_rec.inventory_item_id,
2048 l_item_number,
2049 l_disp_rec.quantity,
2050 l_disp_rec.uom;
2051 IF (get_pos_subut_item_csr%NOTFOUND) THEN
2052 CLOSE get_pos_subut_item_csr;
2053 CLOSE get_empty_pos_item_csr;
2054
2055 --Raise the exception because for workorder org, no matching item found.
2056 --Can not create Mtl Req because there is no inventory item
2057 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_ITEM_ORG_INV');
2058 FND_MSG_PUB.ADD;
2059 RAISE FND_API.G_EXC_ERROR;
2060 END IF;
2061 CLOSE get_pos_subut_item_csr;
2062 END IF;
2063 CLOSE get_empty_pos_item_csr;
2064
2065 ELSE
2066 l_req_material_rec.inventory_item_id := l_disp_rec.inventory_item_id;
2067 l_req_material_rec.organization_id := l_disp_rec.organization_id;
2068 l_item_number := l_disp_rec.item_number;
2069 END IF;
2070
2071
2072 --Convert to primary UOM
2073 l_req_material_rec.requested_quantity :=
2074 AHL_LTP_MTL_REQ_PVT.get_primary_uom_qty(l_req_material_rec.inventory_item_id,
2075 l_disp_rec.uom,l_disp_rec.quantity);
2076
2077 l_req_material_rec.UOM_CODE :=
2078 AHL_LTP_MTL_REQ_PVT.get_primary_uom(l_req_material_rec.inventory_item_id,
2079 l_disp_rec.organization_id);
2080
2081 --The default requested date is sysdate
2082 l_req_material_rec.requested_date := TRUNC(sysdate);
2083
2084 l_req_material_rec.workorder_id := l_disp_rec.workorder_id;
2085
2086 -- Get the workorder operation id and decide if one or the other
2087 IF (l_disp_rec.wo_operation_id IS NOT NULL) THEN
2088 l_req_material_rec.workorder_operation_id := l_disp_rec.wo_operation_id;
2089 ELSE
2090 --If null, fetch the 1st open workorder operation for given workorder
2091 OPEN get_workorder_oper_csr (l_disp_rec.workorder_id);
2092 FETCH get_workorder_oper_csr INTO l_req_material_rec.workorder_operation_id;
2093 CLOSE get_workorder_oper_csr;
2094 END IF;
2095
2096
2097 l_req_material_tbl(0) := l_req_material_rec;
2098
2099 OPEN check_mtl_requirement(l_req_material_rec.inventory_item_id,
2100 l_req_material_rec.organization_id,
2101 l_req_material_rec.workorder_operation_id);
2102 FETCH check_mtl_requirement INTO l_mtl_requirement;
2103 IF check_mtl_requirement%FOUND THEN
2104 close check_mtl_requirement;
2105 l_req_material_u_rec.schedule_material_id := l_mtl_requirement.scheduled_material_id;
2106 l_req_material_u_rec.object_version_number := l_mtl_requirement.object_version_number;
2107 l_delta_quantity := inv_convert.inv_um_convert(item_id => l_mtl_requirement.inventory_item_id,
2108 precision => 6,
2109 from_quantity => l_disp_rec.quantity,
2110 from_unit => l_disp_rec.uom,
2111 to_unit => l_mtl_requirement.uom,
2112 from_name => null,
2113 to_name => null);
2114 l_req_material_u_rec.requested_quantity := l_mtl_requirement.requested_quantity + l_delta_quantity;
2115 l_req_material_u_rec.OPERATION_FLAG := 'U';
2116 l_req_material_u_tbl(1) := l_req_material_u_rec;
2117
2118 IF (FND_LOG.LEVEL_EVENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2119 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
2120 G_LOG_PREFIX||l_api_name||': Within the API',
2121 'Just before calling API: Ahl_PP_Materials_PVT.Process_Material_Request');
2122 END IF;
2123 Ahl_PP_Materials_PVT.Process_Material_Request (
2124 p_api_version => 1.0,
2125 p_init_msg_list => p_init_msg_list,
2126 p_commit => FND_API.G_FALSE,
2127 p_validation_level => p_validation_level,
2128 p_module_type => 'API',
2129 p_x_req_material_tbl => l_req_material_u_tbl,
2130 x_return_status => x_return_status,
2131 x_msg_count => x_msg_count,
2132 x_msg_data => x_msg_data);
2133 -- Added on 1/8/05 by JR to fix Bug 4097327
2134 -- To give a different confirmation message for update
2135 l_update_flag := true;
2136
2137 ELSE
2138 close check_mtl_requirement;
2139 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2140 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2141 G_LOG_PREFIX||l_api_name||': Within the API',
2142 'Just before calling API: Ahl_PP_Materials_PVT.Create_Material_Reqst');
2143 END IF;
2144 Ahl_PP_Materials_PVT.Create_Material_Reqst (
2145 p_api_version => 1.0,
2146 p_init_msg_list => p_init_msg_list,
2147 p_commit => FND_API.G_FALSE,
2148 p_validation_level => p_validation_level,
2149 p_interface_flag => 'Y', --push to WIP
2150 p_x_req_material_tbl => l_req_material_tbl,
2151 x_job_return_status => l_job_return_status,
2152 x_return_status => x_return_status,
2153 x_msg_count => x_msg_count,
2154 x_msg_data => x_msg_data);
2155 END IF;
2156 --Standard check to count messages
2157 x_msg_count := Fnd_Msg_Pub.count_msg;
2158 IF x_msg_count > 0 THEN
2159 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2160 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2161 G_LOG_PREFIX||l_api_name||': Within the API',
2162 'Error occurred after calling the API in Ahl_PP_Materials_PVT and x_return_status='||
2163 x_return_status||', x_msg_count='||x_msg_count);
2164 END IF;
2165 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2166 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2167 END IF;
2168
2169 --Generate success error message for display purposes in UI.
2170 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2171 -- Added on 1/8/05 by JR to fix Bug 4097327
2172 -- To give a different confirmation message for update
2173 IF (l_update_flag = true) THEN
2174 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_MTL_REQ_UPDATED');
2175 FND_MESSAGE.Set_Token('QUANTITY',
2176 l_req_material_u_rec.requested_quantity);
2177 FND_MESSAGE.Set_Token('UOM', l_mtl_requirement.uom);
2178 ELSE
2179 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_MTL_REQ_SUCCESS');
2180 FND_MESSAGE.Set_Token('QUANTITY',
2181 l_req_material_rec.requested_quantity);
2182 FND_MESSAGE.Set_Token('UOM', l_req_material_rec.uom_code);
2183 END IF;
2184 FND_MESSAGE.Set_Token('ITEM', l_item_number);
2185 FND_MSG_PUB.ADD;
2186 END IF;
2187
2188 --Standard check for commit
2189 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2190 COMMIT;
2191 END IF;
2192
2193 --Count and Get messages (optional)
2194 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2195 p_data => x_msg_data,
2196 p_encoded => fnd_api.g_false);
2197
2198 EXCEPTION
2199 WHEN FND_API.G_EXC_ERROR THEN
2200 Rollback to Create_Disp_Mtl_Req_Pvt;
2201 x_return_status := FND_API.G_RET_STS_ERROR;
2202 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2203 p_data => x_msg_data,
2204 p_encoded => fnd_api.g_false);
2205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2206 Rollback to Create_Disp_Mtl_Req_Pvt;
2207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2208 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2209 p_data => x_msg_data,
2210 p_encoded => fnd_api.g_false);
2211 WHEN OTHERS THEN
2212 Rollback to Create_Disp_Mtl_Req_Pvt;
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2214 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
2215 p_procedure_name => l_api_name,
2216 p_error_text => SQLERRM);
2217 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2218 p_data => x_msg_data,
2219 p_encoded => fnd_api.g_false);
2220 END Create_Disp_Mtl_Requirement;
2221
2222 --------------------------------------------------------------------------------------
2223 -- Start of Comments --
2224 -- Procedure name : Create_SR_Disp_Link
2225 -- Type : Private
2226 -- Function : Private API to create a SR Link between the Disposition
2227 -- and the new SR object
2228 -- Pre-reqs :
2229 -- Parameters :
2230 --
2231 -- Standard IN Parameters :
2232 -- p_api_version IN NUMBER Required
2233 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2234 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2235 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2236 --
2237 -- Standard OUT Parameters :
2238 -- x_return_status OUT VARCHAR2 Required
2239 -- x_msg_count OUT NUMBER Required
2240 -- x_msg_data OUT VARCHAR2 Required
2241 --
2242 -- Create_SR_Disp_Link Parameters:
2243 -- p_disposition_id IN NUMBER Required
2244 -- The Id of disposition for which to create SR link.
2245 -- p_service_request_id IN Number Required
2246 -- The ID of Sevice Request for which to create SR link.
2247 --
2248 -- Version :
2249 -- Initial Version 1.0
2250 --
2251 -- End of Comments.
2252
2253 PROCEDURE Create_SR_Disp_Link (
2254 p_api_version IN NUMBER,
2255 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2256 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2257 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2258 x_return_status OUT NOCOPY VARCHAR2,
2259 x_msg_count OUT NOCOPY NUMBER,
2260 x_msg_data OUT NOCOPY VARCHAR2,
2261 p_service_request_id IN NUMBER,
2262 p_disposition_id IN NUMBER,
2263 x_link_id OUT NOCOPY NUMBER
2264 ) IS
2265 --
2266 l_api_version CONSTANT NUMBER := 1.0;
2267 l_api_name CONSTANT VARCHAR2(30) := 'Create_SR_Disp_Link';
2268 l_link_rec CS_INCIDENTLINKS_PUB.CS_INCIDENT_LINK_REC_TYPE;
2269 l_object_version_number NUMBER;
2270 l_reciprocal_link_id NUMBER;
2271 l_dummy_num NUMBER;
2272
2273 --
2274 BEGIN
2275
2276 -- Standard start of API savepoint
2277 SAVEPOINT Create_SR_Disp_Link_Pvt;
2278
2279 -- Initialize Procedure return status to success
2280 x_return_status := FND_API.G_RET_STS_SUCCESS;
2281
2282 IF (FND_API.to_boolean(p_init_msg_list)) THEN
2283 FND_MSG_PUB.initialize;
2284 END IF;
2285
2286 IF (FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2287 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2288 G_LOG_PREFIX||l_api_name||': Begining of the API',
2289 'At the start of the API and p_service_request_id ='||
2290 p_service_request_id||', p_disposition_id='||p_disposition_id);
2291 END IF;
2292
2293 --validate the input parameters
2294 BEGIN
2295 select 1 INTO l_dummy_num
2296 from ahl_prd_dispositions_b
2297 where disposition_id = p_disposition_id
2298 and nvl(status_code, ' ') <> 'TERMINATED';
2299 EXCEPTION
2300 WHEN NO_DATA_FOUND THEN
2301 fnd_message.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2302 fnd_message.set_token('NAME','p_disposition_id');
2303 fnd_message.set_token('VALUE',p_disposition_id);
2304 fnd_msg_pub.add;
2305 RAISE FND_API.G_EXC_ERROR;
2306 END;
2307
2308 BEGIN
2309 select 1 INTO l_dummy_num
2310 from cs_incidents
2311 where incident_id = p_service_request_id;
2312 EXCEPTION
2313 WHEN NO_DATA_FOUND THEN
2314 fnd_message.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2315 fnd_message.set_token('NAME','p_service_request_id');
2316 fnd_message.set_token('VALUE',p_service_request_id);
2317 fnd_msg_pub.add;
2318 RAISE FND_API.G_EXC_ERROR;
2319 END;
2320
2321 --- Create link between Disposition and Service Request
2322 l_link_rec := NULL;
2323 l_link_rec.subject_id := p_service_request_id;
2324 l_link_rec.subject_type := 'SR';
2325 l_link_rec.object_id := p_disposition_id;
2326 --l_link_rec.object_number := l_disp_rec.immediate_disposition_code;
2327 l_link_rec.object_type := 'AHL_PRD_DISP';
2328 l_link_rec.link_type_id := 6; --Refers to link type
2329 l_link_rec.request_id := fnd_global.conc_request_id;
2330 l_link_rec.program_application_id := fnd_global.prog_appl_id;
2331 l_link_rec.program_id := fnd_global.conc_program_id;
2332 l_link_rec.program_update_date := sysdate;
2333
2334 IF (FND_LOG.LEVEL_EVENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2335 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
2336 G_LOG_PREFIX||l_api_name||': Within the API',
2337 'Just before calling cs_incidentlinks_pub.create_incidentlink and subject_id='||
2338 p_service_request_id||', object_id='||p_disposition_id);
2339 END IF;
2340 cs_incidentlinks_pub.create_incidentlink(
2341 p_api_version => 2.0,
2342 p_init_msg_list => FND_API.G_FALSE,
2343 p_commit => FND_API.G_FALSE,
2344 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
2345 p_resp_id => FND_GLOBAL.RESP_ID,
2346 p_user_id => FND_GLOBAL.USER_ID,
2347 p_login_id => NULL,
2348 p_org_id => fnd_profile.value('ORG_ID'),
2349 p_link_rec => l_link_rec,
2350 x_return_status => x_return_status,
2351 x_msg_count => x_msg_count,
2352 x_msg_data => x_msg_data,
2353 x_object_version_number => l_object_version_number,
2354 x_reciprocal_link_id => l_reciprocal_link_id,
2355 x_link_id => x_link_id);
2356
2357 IF (FND_LOG.LEVEL_EVENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2358 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
2359 G_LOG_PREFIX||l_api_name||': Within the API',
2360 'After calling cs_incidentlinks_pub.create_incidentlink and x_return_status='||
2361 x_return_status||', x_reciprocal_link_id='||l_reciprocal_link_id||', x_link_id='||
2362 x_link_id);
2363 END IF;
2364 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) Then
2365 fnd_message.set_name('AHL','AHL_PRD_SR_LINK_CREATE_ERROR');
2366 fnd_message.set_token('VALUE1',p_disposition_id);
2367 fnd_msg_pub.add;
2368 RAISE FND_API.G_EXC_ERROR;
2369 End If;
2370
2371 IF (FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2372 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2373 G_LOG_PREFIX||l_api_name||': End of the API',
2374 'At the end of the API after normal execution.');
2375 END IF;
2376
2377 --Standard check for commit
2378 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2379 COMMIT;
2380 END IF;
2381
2382 --Count and Get messages (optional)
2383 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2384 p_data => x_msg_data,
2385 p_encoded => fnd_api.g_false);
2386
2387 EXCEPTION
2388 WHEN FND_API.G_EXC_ERROR THEN
2389 Rollback to Create_SR_Disp_Link_Pvt;
2390 x_return_status := FND_API.G_RET_STS_ERROR;
2391 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2392 p_data => x_msg_data,
2393 p_encoded => fnd_api.g_false);
2394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2395 Rollback to Create_SR_Disp_Link_Pvt;
2396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2397 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2398 p_data => x_msg_data,
2399 p_encoded => fnd_api.g_false);
2400 WHEN OTHERS THEN
2401 Rollback to Create_SR_Disp_Link_Pvt;
2402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2403 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
2404 p_procedure_name => l_api_name,
2405 p_error_text => SQLERRM);
2406 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2407 p_data => x_msg_data,
2408 p_encoded => fnd_api.g_false);
2409 END Create_SR_Disp_Link;
2410
2411
2412 -- Function to get the Unit Config Header Id from the workorder Id
2413 -- Tries to get the instance from the Workorder's Visit Task First.
2414 -- If not possible, gets the instance from the Visit.
2415 -- This instance is matched against top nodes of UCs and the matching
2416 -- UC's header id is returned.
2417 -- If no match is found, null is returned.
2418 FUNCTION Get_WO_Unit_Id(p_workorder_id IN NUMBER)
2419 RETURN NUMBER
2420 IS
2421
2422 CURSOR get_instance_id_csr IS
2423 SELECT VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID
2424 FROM AHL_WORKORDERS WO, AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
2425 WHERE WO.WORKORDER_ID = p_workorder_id AND
2426 VST.VISIT_ID = WO.VISIT_ID AND
2427 VTS.VISIT_TASK_ID = WO.VISIT_TASK_ID;
2428
2429
2430
2431 L_DEBUG_KEY CONSTANT VARCHAR2(150) := 'ahl.plsql.AHL_PRD_DISP_UTIL_PVT.Get_WO_Unit_Id';
2432 l_return_value NUMBER;
2433 l_visit_instance_id NUMBER;
2434 l_visit_task_instance_id NUMBER;
2435 l_curr_instance_id NUMBER;
2436
2437 BEGIN
2438 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2439 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2440 L_DEBUG_KEY || '.begin',
2441 'Getting UC Header Id for workorder with Id: ' || p_workorder_id);
2442 END IF;
2443 OPEN get_instance_id_csr;
2444 FETCH get_instance_id_csr INTO l_visit_task_instance_id, l_visit_instance_id;
2445 CLOSE get_instance_id_csr;
2446 l_curr_instance_id := NVL(l_visit_task_instance_id, l_visit_instance_id);
2447 l_return_value := AHL_UTIL_UC_PKG.get_uc_header_id(l_curr_instance_id);
2448 IF (l_return_value IS NULL) THEN
2449 IF (l_curr_instance_id = l_visit_instance_id) THEN
2450 -- Task Instance is null, Visit header instance does not belong to an UC.
2451 null; -- Return null
2452 ELSE
2453 -- Task instance does not belong to an UC. Try to get from Visit Header Level
2454 l_return_value := AHL_UTIL_UC_PKG.get_uc_header_id(l_visit_instance_id);
2455 END IF;
2456 END IF;
2457 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2458 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2459 L_DEBUG_KEY || '.end',
2460 'Got UC Header Id ' || l_return_value || ' for workorder with Id: ' || p_workorder_id);
2461 END IF;
2462 RETURN l_return_value;
2463 END Get_WO_Unit_Id;
2464
2465 -- Function added by Jerry on 01/05/2005 for fixing bug 4093642
2466 -- If the installation part change occurrs after the disposition was termindated,
2467 -- then even if the removal part change against which the disposition was created,
2468 -- is linked with this installation part change id, then this kind of link doesn't
2469 -- make sense and we should break it
2470
2471 -- Logic of this function simplified by jaramana on 27-FEB-2012 to fix the Perf bug 13775964
2472 -- Removed the use of ahl_part_changes_v
2473 -- Return N only if the Installation Part Change was created after the Disposition's Last Update Date
2474 -- This can happen only if the disp was terminated as otherwise a Disposition's status will be updated
2475 -- after a matching installation is done.
2476 FUNCTION install_part_change_valid(p_disposition_id IN NUMBER)
2477 RETURN VARCHAR2 IS
2478
2479 CURSOR get_curr_pc_dtls IS
2480 SELECT disp.workorder_id,
2481 disp.last_update_date,
2482 disp.part_change_id,
2483 disp.serial_number,
2484 pc.part_change_type,
2485 pc.removed_instance_id,
2486 pc.mc_relationship_id,
2487 pc.creation_date pc_creation_date
2488 FROM ahl_prd_dispositions_b disp,
2489 ahl_part_changes pc
2490 WHERE disp.part_change_id = pc.part_change_id;
2491 l_pc_dtls get_curr_pc_dtls%ROWTYPE;
2492
2493 CURSOR get_inst_pc_dtls(c_workorder_id IN NUMBER, c_relationship_id IN NUMBER, c_min_date IN DATE) IS
2494 SELECT pc.creation_date,
2495 pc.installed_instance_id
2496 FROM ahl_part_changes pc,
2497 ahl_workorder_operations wop
2498 WHERE pc.workorder_operation_id = wop.workorder_operation_id
2499 AND wop.workorder_id = c_workorder_id
2500 AND pc.mc_relationship_id = c_relationship_id
2501 AND pc.part_change_type = 'I'
2502 AND pc.creation_date >= c_min_date; -- Installation happened after the removal
2503 l_inst_pc_dtls get_inst_pc_dtls%ROWTYPE;
2504
2505 l_return_val VARCHAR2(1);
2506 BEGIN
2507 l_return_val := 'Y';
2508 OPEN get_curr_pc_dtls;
2509 FETCH get_curr_pc_dtls INTO l_pc_dtls;
2510 IF(get_curr_pc_dtls%FOUND AND l_pc_dtls.part_change_type = 'R' AND (l_pc_dtls.SERIAL_NUMBER IS NOT NULL)) THEN
2511 OPEN get_inst_pc_dtls(c_workorder_id => l_pc_dtls.workorder_id,
2512 c_relationship_id => l_pc_dtls.mc_relationship_id,
2513 c_min_date => l_pc_dtls.pc_creation_date);
2514 FETCH get_inst_pc_dtls INTO l_inst_pc_dtls;
2515 IF (get_inst_pc_dtls%FOUND AND l_inst_pc_dtls.creation_date > l_pc_dtls.last_update_date) THEN
2516 l_return_val := 'N';
2517 END IF;
2518 CLOSE get_inst_pc_dtls;
2519 END IF;
2520 CLOSE get_curr_pc_dtls;
2521 RETURN l_return_val;
2522 END install_part_change_valid;
2523
2524 End AHL_PRD_DISP_UTIL_PVT;
[Home] [Help]