[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_BLK_PARTS_CHANGE_PVT
Source
1 PACKAGE BODY AHL_PRD_BLK_PARTS_CHANGE_PVT AS
2 /* $Header: AHLVBPCB.pls 120.0.12020000.2 2012/12/10 12:29:08 shnatu noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_BLK_PARTS_CHANGE_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
6 ------------------------------------
7 -- Common constants and variables --
8 ------------------------------------
9 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
10 l_log_statement NUMBER := fnd_log.level_statement;
11 l_log_procedure NUMBER := fnd_log.level_procedure;
12 l_log_error NUMBER := fnd_log.level_error;
13 l_log_unexpected NUMBER := fnd_log.level_unexpected;
14
15 TYPE NBR_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
16
17 TYPE INSTANCE_REC IS RECORD(
18 INSTANCE_NUMBER NUMBER,
19 PARENT_INSTANCE_NUMBER NUMBER,
20 OPERATION_TYPE VARCHAR2(1)
21 );
22
23 TYPE INSTANCE_TBL_TYPE IS TABLE OF INSTANCE_REC INDEX BY BINARY_INTEGER;
24
25 -- Private Procedure
26 PROCEDURE get_uc_details(
27 p_unit_header_id IN NUMBER,
28 p_workorder_id IN NUMBER,
29 p_bulk_process_flag IN VARCHAR2,
30 x_uc_details_tbl OUT NOCOPY uc_details_hgrid_tbl_type);
31
32 -- Private Procedure
33 PROCEDURE Calc_Position_Qty (
34 p_mc_relationship_id IN NUMBER,
35 p_instance_id IN NUMBER,
36 x_position_qty OUT NOCOPY NUMBER,
37 x_uom_meaning OUT NOCOPY VARCHAR2
38 );
39
40 -- Private Procedure
41 PROCEDURE Calc_Control_Position_Qty (
42 p_workorder_id IN NUMBER,
43 p_unit_header_id IN NUMBER,
44 p_root_instance_id IN NUMBER,
45 p_bulk_process_flag IN VARCHAR2
46 );
47
48 -- Private Procedure
49 PROCEDURE GET_ORDERED_PCRECS(
50 P_PARTS_REC_TBL IN PART_CHANGE_TBL_TYPE,
51 X_INSTANCE_TBL OUT NOCOPY INSTANCE_TBL_TYPE);
52
53 -- Private Procedure
54 PROCEDURE INVOKE_PART_CHANGE(
55 P_X_PART_CHANGE_REC IN OUT NOCOPY PART_CHANGE_REC_TYPE,
56 P_MR_ASSO_TBL IN AHL_PRD_NONROUTINE_PVT.MR_ASSOCIATION_TBL_TYPE,
57 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
58 X_MSG_COUNT OUT NOCOPY NUMBER,
59 X_MSG_DATA OUT NOCOPY VARCHAR2,
60 X_WARNING_MSG_TBL OUT NOCOPY AHL_UC_VALIDATION_PUB.ERROR_TBL_TYPE
61 );
62
63 ------------------------------------------------------------------------------------
64 -- Start of Comments
65 -- Procedure name : Get_Bulk_Part_Chg_Details
66 -- Type : Public
67 -- Function : This procedure will update the temparory table to show data in hGrid.
68 -- Since this table is global temporary with ON COMMIT DELETE ROWS,
69 -- Always call this method with p_commit value : FND_API.G_FALSE
70 -- Pre-reqs :
71 -- Parameters :
72 --
73 -- Get_Bulk_Part_Chg_Details Parameters
74 -- p_workorder_id IN NUMBER
75 -- p_bulk_process_flag IN Either 'I' for Install or 'R' for Removal
76 --
77 -- End of Comments
78
79 PROCEDURE Get_Bulk_Part_Chg_Details
80 (
81 p_api_version IN NUMBER := 1.0,
82 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
83 p_commit IN VARCHAR2 := FND_API.G_FALSE,
84 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
85 p_default IN VARCHAR2 := FND_API.G_FALSE,
86 p_module_type IN VARCHAR2 := NULL,
87 p_workorder_id IN NUMBER,
88 p_bulk_process_flag IN VARCHAR2,
89 x_uc_details_tbl OUT NOCOPY uc_details_hgrid_tbl_type,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2
93 )IS
94
95 l_api_name CONSTANT VARCHAR2(30) := 'Get_Bulk_Part_Chg_Details';
96 l_full_name CONSTANT VARCHAR2(80) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
97 l_api_version CONSTANT NUMBER := 1.0;
98
99 CURSOR get_record_count IS
100 SELECT COUNT(1) from AHL_UC_DETAILS_FOR_HGRID;
101
102 CURSOR is_unit_present(c_unit_header_id IN NUMBER) IS
103 SELECT COUNT(UHC.UNIT_NODE_ID)
104 FROM AHL_UC_DETAILS_FOR_HGRID UHC,
105 AHL_UNIT_CONFIG_HEADERS UC
106 WHERE UC.unit_config_header_id = c_unit_header_id
107 AND UHC.root_instance_id = UC.csi_item_instance_id;
108
109 p_unit_header_id NUMBER;
110
111 l_count NUMBER;
112
113 BEGIN
114 --Initialize API return status to success
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116
117 --Standard call to check for call compatibility.
118 IF NOT FND_API.compatible_api_call(
119 l_api_version,
120 p_api_version,
121 l_api_name,
122 G_PKG_NAME)
123 THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 --Initialize message list if p_init_msg_list is set to TRUE.
128 IF FND_API.to_boolean( p_init_msg_list ) THEN
129 FND_MSG_PUB.initialize;
130 END IF;
131
132 -- Get unit header id for the given work order (derived using Visit followed by Task instance if required)
133 p_unit_header_id := AHL_PRD_PARTS_CHANGE_PVT.Get_UnitConfig_ID (p_workorder_id);
134
135 IF p_unit_header_id IS NOT NULL THEN
136 -- No need of clearing the data from this table
137 OPEN is_unit_present(p_unit_header_id);
138 FETCH is_unit_present INTO l_count;
139 CLOSE is_unit_present;
140
141 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
142 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
143 'Records having unit id ' || p_unit_header_id || ': ' || l_count);
144 END IF;
145
146 IF l_count = 0 THEN
147 get_uc_details(
148 p_unit_header_id => p_unit_header_id,
149 p_workorder_id => p_workorder_id,
150 p_bulk_process_flag => p_bulk_process_flag,
151 x_uc_details_tbl => x_uc_details_tbl);
152
153 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
155 'New row Count ' || x_uc_details_tbl.count);
156 END IF;
157
158 IF x_uc_details_tbl.FIRST IS NOT NULL THEN
159 FOR i IN x_uc_details_tbl.FIRST..x_uc_details_tbl.LAST LOOP
160
161 INSERT INTO AHL_UC_DETAILS_FOR_HGRID
162 (
163 unit_node_id,
164 root_instance_id,
165 path_position_id,
166 relationship_id,
167 parent_rel_id,
168 parent_instance_id,
169 parent_instance_number,
170 csi_ii_relationship_id,
171 csi_ii_object_version_num,
172 part_change_id,
173 curr_item_id,
174 curr_instance_id,
175 curr_item_number,
176 curr_instance_obj_ver_num,
177 curr_instance_number,
178 curr_serial_number,
179 curr_lot_number,
180 curr_quantity,
181 unit_of_measure,
182 ata,
183 position,
184 position_image,
185 is_position_srl_controlled,
186 is_controlled_position,
187 node_type,
188 qty_per_position,
189 operation_sequence_num,
190 inst_instance_id,
191 inst_item_number,
192 inst_instance_number,
193 inst_serial_number,
194 inst_instance_obj_ver_num,
195 instance_quantity,
196 install_quantity,
197 install_date,
198 removal_date,
199 reason_id,
200 reason_name,
201 removal_code_id,
202 removal_meaning,
203 not_leaf_node_flag,
204 node_level,
205 hgrid_node_path,
206 select_disable_flag,
207 search_flag,
208 disposition_id,
209 disposition_obj_ver_num,
210 disposition_status,
211 disposition_qty,
212 comments,
213 immediate_disposition_code,
214 immediate_disposition,
215 secondary_disposition_code,
216 secondary_disposition,
217 sr_incident_id,
218 collection_id,
219 condition_id,
220 condition_meaning,
221 problem_code,
222 problem_meaning,
223 severity_id,
224 severity_name,
225 resolution_code,
226 resolution_code_id,
227 estimated_duration,
228 service_type,
229 service_type_id,
230 summary,
231 disposition_count,
232 qty_ui_element_typ,
233 mrb_quality_typ,
234 bulk_process_flag
235 )VALUES
236 (
237 x_uc_details_tbl(i).unit_node_id,
238 x_uc_details_tbl(i).root_instance_id,
239 x_uc_details_tbl(i).path_position_id,
240 x_uc_details_tbl(i).relationship_id,
241 x_uc_details_tbl(i).parent_rel_id,
242 x_uc_details_tbl(i).parent_instance_id,
243 x_uc_details_tbl(i).parent_instance_number,
244 x_uc_details_tbl(i).csi_ii_relationship_id,
245 x_uc_details_tbl(i).csi_ii_object_version_num,
246 x_uc_details_tbl(i). part_change_id,
247 x_uc_details_tbl(i).curr_item_id,
248 x_uc_details_tbl(i).curr_instance_id,
249 x_uc_details_tbl(i).curr_item_number,
250 x_uc_details_tbl(i).curr_instance_obj_ver_num,
251 x_uc_details_tbl(i).curr_instance_number,
252 x_uc_details_tbl(i).curr_serial_number,
253 x_uc_details_tbl(i).curr_lot_number,
254 x_uc_details_tbl(i).curr_quantity,
255 x_uc_details_tbl(i).unit_of_measure,
256 x_uc_details_tbl(i).ata,
257 x_uc_details_tbl(i).position,
258 x_uc_details_tbl(i).position_image,
259 x_uc_details_tbl(i).is_position_srl_controlled,
260 x_uc_details_tbl(i).is_controlled_position,
261 x_uc_details_tbl(i).node_type,
262 x_uc_details_tbl(i).qty_per_position,
263 x_uc_details_tbl(i).operation_sequence_num,
264 x_uc_details_tbl(i).inst_instance_id,
265 x_uc_details_tbl(i).inst_item_number,
266 x_uc_details_tbl(i).inst_instance_number,
267 x_uc_details_tbl(i).inst_serial_number,
268 x_uc_details_tbl(i).inst_instance_obj_ver_num,
269 x_uc_details_tbl(i).instance_quantity,
270 x_uc_details_tbl(i).install_quantity,
271 x_uc_details_tbl(i).install_date,
272 x_uc_details_tbl(i).removal_date,
273 x_uc_details_tbl(i).reason_id,
274 x_uc_details_tbl(i).reason_name,
275 x_uc_details_tbl(i).removal_code_id,
276 x_uc_details_tbl(i).removal_meaning,
277 x_uc_details_tbl(i).not_leaf_node_flag,
278 x_uc_details_tbl(i).node_level,
279 x_uc_details_tbl(i).hgrid_node_path,
280 x_uc_details_tbl(i).select_disable_flag,
281 x_uc_details_tbl(i).search_flag,
282 x_uc_details_tbl(i).disposition_id,
283 x_uc_details_tbl(i).disposition_obj_ver_num,
284 x_uc_details_tbl(i).disposition_status,
285 x_uc_details_tbl(i).disposition_qty,
286 x_uc_details_tbl(i).comments,
287 x_uc_details_tbl(i).immediate_disposition_code,
288 x_uc_details_tbl(i).immediate_disposition,
289 x_uc_details_tbl(i).secondary_disposition_code,
290 x_uc_details_tbl(i).secondary_disposition,
291 x_uc_details_tbl(i).sr_incident_id,
292 x_uc_details_tbl(i).collection_id,
293 x_uc_details_tbl(i).condition_id,
294 x_uc_details_tbl(i).condition_meaning,
295 x_uc_details_tbl(i).problem_code,
296 x_uc_details_tbl(i).problem_meaning,
297 x_uc_details_tbl(i).severity_id,
298 x_uc_details_tbl(i).severity_name,
299 x_uc_details_tbl(i).resolution_code,
300 x_uc_details_tbl(i).resolution_code_id,
301 x_uc_details_tbl(i).estimated_duration,
302 x_uc_details_tbl(i).service_type,
303 x_uc_details_tbl(i).service_type_id,
304 x_uc_details_tbl(i).summary,
305 x_uc_details_tbl(i).disposition_count,
306 x_uc_details_tbl(i).qty_ui_element_typ,
307 x_uc_details_tbl(i).mrb_quality_typ,
308 p_bulk_process_flag
309 );
310 END LOOP;
311
312
313 -- Quantity calculations for Control Positions is an exception to normal flow
314 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
315 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Invoking API Calc_Control_Position_Qty');
316 END IF;
317
318 Calc_Control_Position_Qty
319 (
320 p_workorder_id => p_workorder_id,
321 p_unit_header_id => p_unit_header_id,
322 p_root_instance_id => x_uc_details_tbl(x_uc_details_tbl.FIRST).root_instance_id,
323 p_bulk_process_flag => p_bulk_process_flag
324 );
325
326 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
327 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'After call to API Calc_Control_Position_Qty');
328 END IF;
329
330 END IF; -- IF x_uc_details_tbl.FIRST IS NOT NULL
331
332 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
333 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
334 'Newly Inserted Rows' || x_uc_details_tbl.count);
335 END IF;
336 END IF;
337 END IF;
338
339 OPEN get_record_count;
340 FETCH get_record_count INTO l_count;
341 CLOSE get_record_count;
342
343 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
344 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
345 'Records in Table are ' || l_count);
346 END IF;
347
348 IF(p_commit = FND_API.G_TRUE) THEN
349 COMMIT WORK;
350 END IF;
351
352 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
353 x_msg_count := FND_MSG_PUB.count_msg;
354 IF x_msg_count > 0 THEN
355 RAISE FND_API.G_EXC_ERROR;
356 END IF;
357
358 -- Count and Get messages (optional)
359 FND_MSG_PUB.count_and_get(
360 p_encoded => FND_API.G_FALSE,
361 p_count => x_msg_count,
362 p_data => x_msg_data);
363
364 EXCEPTION
365 WHEN FND_API.G_EXC_ERROR THEN
366 x_return_status := FND_API.G_RET_STS_ERROR ;
367 FND_MSG_PUB.count_and_get(
368 p_encoded => FND_API.G_FALSE,
369 p_count => x_msg_count,
370 p_data => x_msg_data);
371
372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
374 FND_MSG_PUB.count_and_get(
375 p_encoded => FND_API.G_FALSE,
376 p_count => x_msg_count,
377 p_data => x_msg_data);
378
379 WHEN OTHERS THEN
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
381 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
382 THEN
383 FND_MSG_PUB.add_exc_msg(
384 p_pkg_name => G_PKG_NAME,
385 p_procedure_name => l_api_name,
386 p_error_text => SUBSTRB(SQLERRM,1,240));
387 END IF;
388 FND_MSG_PUB.count_and_get(
389 p_encoded => FND_API.G_FALSE,
390 p_count => x_msg_count,
391 p_data => x_msg_data);
392 END Get_Bulk_Part_Chg_Details;
393 ------------------------------------------------------------------------------------
394
395 ------------------------------------------------------------------------------------
396 -- Start of Comments
397 -- Procedure name : get_uc_details
398 -- Type : Private
399 -- Function : Procedure to update the all position details in UC.
400 -- Pre-reqs :
401 -- Parameters :
402 --
403 -- get_uc_details Parameters:
404 -- p_unit_header_id IN NUMBER Required
405 -- p_workorder_id
406 -- p_bulk_process_flag IN VARCHAR2,
407 -- End of Comments
408 PROCEDURE get_uc_details(
409 p_unit_header_id IN NUMBER,
410 p_workorder_id IN NUMBER,
411 p_bulk_process_flag IN VARCHAR2,
412 x_uc_details_tbl OUT NOCOPY uc_details_hgrid_tbl_type
413 ) IS
414
415 l_api_name CONSTANT VARCHAR2(30) := 'get_uc_details';
416 l_full_name CONSTANT VARCHAR2(80) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
417
418 l_uc_details_tbl uc_details_hgrid_tbl_type;
419 l_return_status VARCHAR2(1);
420 l_msg_count NUMBER;
421 l_msg_data VARCHAR2(2000);
422
423 l_uc_descendant_tbl AHL_UC_TREE_PVT.uc_descendant_tbl_type;
424 l_unit_name VARCHAR2(80);
425 l_root_instance_id NUMBER;
426 l_mc_header_id NUMBER;
427 l_visit_id NUMBER;
428 l_org_id NUMBER;
429
430 CURSOR uc_header_information(c_unit_header_id IN NUMBER) IS
431 SELECT name,csi_item_instance_id,master_config_id FROM AHL_UNIT_CONFIG_HEADERS
432 WHERE unit_config_header_id = c_unit_header_id;
433
434 --Cursor to get basic details
435 CURSOR get_visit_details_csr IS
436 SELECT WO.visit_id,VWP.organization_id FROM AHL_WORKORDERS WO,AHL_VISITS_B VWP
437 WHERE WO.workorder_id = p_workorder_id
438 AND WO.visit_id = VWP.visit_id;
439
440 CURSOR get_part_info(c_instance_id NUMBER) IS
441 SELECT MTL.inventory_item_id, MTL.concatenated_segments, CSI.instance_number, CSI.serial_number, CSI.quantity,
442 CSI.lot_number, UOM.unit_of_measure, CSI.object_version_number
443 FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI, MTL_UNITS_OF_MEASURE UOM
444 WHERE CSI.instance_id = c_instance_id
445 AND CSI.inventory_item_id = MTL.inventory_item_id
446 AND CSI.inv_master_organization_id = MTL.organization_id
447 AND CSI.unit_of_measure = UOM.uom_code;
448
449 CURSOR get_max_pk_count IS
450 SELECT (NVL(MAX(unit_node_id),0) + 1) from AHL_UC_DETAILS_FOR_HGRID;
451
452 CURSOR get_Disposition_Details(c_instance_id NUMBER, c_path_position_id NUMBER) IS
453 SELECT disp.disposition_id ,
454 disp.object_version_number ,
455 disp.condition_id ,
456 disp.condition_code ,
457 disp.status ,
458 disp.immediate_disposition_code ,
459 disp.secondary_disposition_code ,
460 disp.collection_id ,
461 disp.quantity ,
462 disp.comments ,
463 disp.operation_sequence ,
464 disp.part_change_id,
465 nr.sr_resolution_code,
466 nr.sr_resolution_meaning,
467 nr.sr_problem_code,
468 nr.sr_problem_meaning,
469 nr.sr_problem_summary,
470 nr.service_type_code,
471 nr.service_type,
472 nr.duration,
473 nr.sr_incident_severity,
474 nr.sr_incident_severity_id,
475 nr.sr_incident_id
476 FROM AHL_PRD_DISPOSITIONS_V disp,
477 AHL_MC_PATH_POSITIONS pp,
478 AHL_SR_NON_ROUTINES_V nr
479 WHERE disp.path_pos_common_id = pp.path_pos_common_id
480 AND pp.path_position_id = c_path_position_id
481 AND disp.part_change_id IS NULL
482 AND disp.workorder_id = p_workorder_id
483 AND NVL(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
484 AND (disp.status_code IS NULL
485 OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
486 AND disp.primary_service_request_id = nr.SR_INCIDENT_ID(+)
487 AND disp.instance_id IN
488 (SELECT CI1.instance_id
489 FROM CSI_ITEM_INSTANCES CI1,
490 CSI_ITEM_INSTANCES CI2
491 WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
492 AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
493 AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
494 AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
495 AND NVL(CI1.SERIAL_NUMBER,'x') = NVL(CI2.SERIAL_NUMBER,'x')
496 AND CI2.instance_id = c_instance_id
497 )
498 UNION
499 SELECT disp.disposition_id ,
500 disp.object_version_number ,
501 disp.condition_id ,
502 disp.condition_code ,
503 disp.status ,
504 disp.immediate_disposition_code ,
505 disp.secondary_disposition_code ,
506 disp.collection_id ,
507 disp.quantity ,
508 disp.comments ,
509 disp.operation_sequence,
510 disp.part_change_id,
511 nr.sr_resolution_code,
512 nr.sr_resolution_meaning,
513 nr.sr_problem_code,
514 nr.sr_problem_meaning,
515 nr.sr_problem_summary,
516 nr.service_type_code,
517 nr.service_type,
518 nr.duration,
519 nr.sr_incident_severity,
520 nr.sr_incident_severity_id,
521 nr.sr_incident_id
522 FROM AHL_PRD_DISPOSITIONS_V disp,AHL_SR_NON_ROUTINES_V nr
523 WHERE disp.path_position_id IS NULL
524 AND disp.part_change_id IS NULL
525 AND disp.workorder_id = p_workorder_id
526 AND NVL(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
527 AND (disp.status_code IS NULL
528 OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
529 AND disp.primary_service_request_id = nr.SR_INCIDENT_ID(+)
530 AND disp.instance_id IN
531 (SELECT CI1.instance_id
532 FROM CSI_ITEM_INSTANCES CI1,
533 CSI_ITEM_INSTANCES CI2
534 WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
535 AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
536 AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
537 AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
538 AND NVL(CI1.SERIAL_NUMBER,'x') = NVL(CI2.SERIAL_NUMBER,'x')
539 AND CI2.instance_id = c_instance_id
540 );
541
542
543 --Get NR Details
544 CURSOR get_NR_Details(c_sr_incident_id NUMBER) IS
545 SELECT nr.sr_resolution_code,
546 nr.sr_resolution_meaning,
547 nr.sr_problem_code,
548 nr.sr_problem_meaning,
549 nr.sr_problem_summary,
550 nr.service_type_code,
551 nr.service_type,
552 nr.duration,
553 nr.sr_incident_severity,
554 nr.sr_incident_severity_id
555 FROM AHL_SR_NON_ROUTINES_V nr
556 WHERE nr.SR_INCIDENT_ID = c_sr_incident_id;
557
558 -- Cursor to get MC details for the given MC relationship id
559 CURSOR get_mc_details_csr (c_relationship_id NUMBER) IS
560 SELECT ATA.meaning ata_meaning
561 FROM AHL_MC_RELATIONSHIPS MCR, FND_LOOKUP_VALUES_VL ATA
562 WHERE MCR.relationship_id = c_relationship_id
563 AND ATA.lookup_code (+) = MCR.ata_code
564 AND ATA.lookup_type (+) = 'AHL_ATA_CODE';
565
566 CURSOR get_paln_id (c_collection_id NUMBER) IS
567 SELECT plan_id FROM qa_results
568 WHERE collection_id = c_collection_id;
569
570 CURSOR get_csi_ii_details (c_instance_id NUMBER) IS
571 SELECT relationship_id,object_version_number
572 FROM csi_ii_relationships
573 WHERE subject_id = c_instance_id
574 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
575 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);--Modified by Sunil
576
577 CURSOR get_instance_number(c_instance_id NUMBER) IS
578 SELECT CSI.instance_number
579 FROM CSI_ITEM_INSTANCES CSI
580 WHERE CSI.instance_id = c_instance_id;
581
582 -- Cursor to Know if position is controlled(container) position
583 CURSOR get_controled_position_csr(c_mc_header_id IN NUMBER,
584 c_path_position_id IN NUMBER) IS
585 SELECT 'Y', MCRS.object_attribute1
586 FROM ahl_mc_path_position_nodes mcpp,
587 ahl_mc_rule_statements mcrs,
588 ahl_mc_headers_b mch,
589 ahl_mc_rules_b mcr
590 WHERE mch.mc_header_id = c_mc_header_id
591 AND mcpp.mc_id = mch.mc_id
592 AND mch.version_number = NVL(mcpp.version_number, mch.version_number)
593 AND mcpp.position_key IN (select position_key from ahl_mc_path_position_nodes where path_position_id = c_path_position_id)
594 AND mcrs.subject_id = mcpp.path_position_id
595 AND mcrs.subject_type = 'POSITION'
596 AND mcrs.operator IN ('MUST_HAVE', 'HAVE')
597 AND mcrs.object_type = 'TOT_CHILD_QUANTITY'
598 AND mch.config_status_code = 'COMPLETE'
599 AND mcr.rule_id = mcrs.rule_id
600 AND mcr.rule_type_code = 'MANDATORY'
601 AND NVL(mcr.active_start_date, SYSDATE - 1) < SYSDATE
602 AND NVL(mcr.active_end_date, SYSDATE + 1) > SYSDATE;
603
604 -- Cursor to Know Plan ID defined at Org level
605 CURSOR get_MRB_Quality_csr(c_org_id NUMBER) IS
606 SELECT QP.plan_id
607 FROM QA_PLANS_VAL_V QP,
608 QA_PLAN_TRANSACTIONS QPT,
609 QA_PLAN_COLLECTION_TRIGGERS QPCT
610 WHERE QP.plan_id = QPT.plan_id
611 AND QPT.plan_transaction_id = QPCT.plan_transaction_id
612 AND QP.organization_id = c_org_id
613 AND QPT.transaction_number = 2004 -- MRB_TRANSACTION_NUMBER
614 AND QPCT.collection_trigger_id = 87 -- Inspection_Type
615 AND QPCT.low_value = (FND_PROFILE.VALUE('AHL_MRB_DISP_PLAN_TYPE'));
616
617 -- Cursor to get part change details for last removal or install of the instance at given UC position
618 CURSOR get_last_part_chg_dtls_csr (c_path_position_id NUMBER,
619 c_instance_id NUMBER,
620 c_visit_id NUMBER) IS
621 SELECT OP.operation_sequence_num, DECODE(APC.part_change_type, 'R', APC.removal_date, 'I', APC.installation_date),
622 APC.removal_code,REM.meaning,APC.removal_reason_id,RSN.reason_name,APC.removed_instance_id
623 FROM AHL_PART_CHANGES APC,
624 AHL_WORKORDER_OPERATIONS OP,
625 MTL_TRANSACTION_REASONS RSN,
626 FND_LOOKUPS REM
627 WHERE APC.part_change_id = (SELECT max(APC1.part_change_id)
628 FROM AHL_PART_CHANGES APC1,
629 AHL_WORKORDER_OPERATIONS OP1
630 WHERE APC1.unit_config_header_id = p_unit_header_id
631 AND APC1.mc_relationship_id = c_path_position_id -- Table AHL_PART_CHANGES stores path position id in mc_relationship_id field
632 AND APC1.part_change_type = p_bulk_process_flag
633 AND DECODE(APC1.part_change_type, 'R', nvl(c_instance_id,-1), 'I', APC1.installed_instance_id) = nvl(c_instance_id,-1)
634 AND OP1.workorder_operation_id = APC1.workorder_operation_id
635 AND OP1.workorder_id IN (SELECT workorder_id
636 FROM AHL_WORKORDERS
637 WHERE visit_id = c_visit_id))
638 AND OP.workorder_operation_id = APC.workorder_operation_id
639 AND RSN.reason_id(+) = APC.removal_reason_id
640 AND REM.LOOKUP_CODE (+) = APC.removal_code
641 AND REM.lookup_type (+) = 'AHL_REMOVAL_CODE';
642
643 -- Cursor to get UOM meaning given UOM code
644 CURSOR get_uom_meaning_csr (c_uom_code VARCHAR2) IS
645 SELECT unit_of_measure
646 FROM MTL_UNITS_OF_MEASURE_VL
647 WHERE uom_code = c_uom_code;
648
649 l_disp_rec get_Disposition_Details%ROWTYPE;
650 l_temp_disp_rec get_Disposition_Details%ROWTYPE;
651
652 TYPE t_number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
653 TYPE t_varchar_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
654 l_root_path_tbl t_varchar_tbl; -- will be used as an associatve array, it stores the parents path from root
655 l_min_child_id_tbl t_number_tbl; -- will be used as an associatve array, it stores the child number of parent
656
657 l_disposition_rec AHL_PRD_DISPOSITION_PVT.DISPOSITION_REC_TYPE;
658 l_imm_disp_type_tbl AHL_PRD_DISP_UTIL_PVT.Disp_Type_Tbl_Type;
659 l_sec_disp_type_tbl AHL_PRD_DISP_UTIL_PVT.Disp_Type_Tbl_Type;
660 l_path_position_id NUMBER;
661 l_curr_row NUMBER;
662 l_curr_pk NUMBER;
663
664 l_mrb_profile_condition NUMBER;
665 l_rel_id NUMBER;
666 l_par_rel_id NUMBER;
667 l_instance_id NUMBER;
668 UC_NODE BOOLEAN;
669 UC_EXTRA_NODE BOOLEAN;
670 UC_EMPTY_NODE BOOLEAN;
671 l_dummy_val VARCHAR2(1);
672 l_control_pos_qty NUMBER;
673 l_plan_id NUMBER;
674 l_coll_plan_id NUMBER;
675 l_quality_setup VARCHAR2(1) := 'N';
676
677 l_available_instance_tbl AHL_UC_INSTANCE_PVT.Available_Instance_Tbl_Type;
678 l_total_available_instances NUMBER;
679 l_avai_inst_row NUMBER;
680
681 /* Following Record Type will be used to store PL/SQL table of UC Positions' row indexes (i.e. row indexes of
682 * table x_uc_details_tbl) for all those positions that have same defaulted installable instance.
683 */
684 TYPE UC_POS_ROW_INDEXES_REC IS RECORD (uc_pos_row_index_tbl t_number_tbl);
685 l_uc_pos_row_index NUMBER;
686
687 /* Following Table Type will be used as an Associative array indexed by instance id where
688 * each element of this array contain table of UC Positions' row indexes which have same defaulted
689 * installable instance.
690 * For Example - inst_uc_pos_row_indx_tbl[instance_id_1][1] = l_curr_row_indx of x_uc_details_tbl,
691 * inst_uc_pos_row_indx_tbl[instance_id_1][2] = l_curr_row_indx1 of x_uc_details_tbl,
692 * inst_uc_pos_row_indx_tbl[instance_id_2][1] = l_curr_row_indx2 of x_uc_details_tbl,
693 */
694 TYPE INST_UC_POS_ROW_INDX_TBL_TYP IS TABLE OF UC_POS_ROW_INDEXES_REC INDEX BY BINARY_INTEGER;
695 l_inst_id_index NUMBER;
696 l_inst_uc_pos_row_indx_tbl INST_UC_POS_ROW_INDX_TBL_TYP;
697
698 BEGIN
699
700 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
701 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
702 l_full_name||'.begin',
703 'Entering Procedure, p_workorder_id = '||p_workorder_id|| ' and p_unit_header_id = '||p_unit_header_id);
704 END IF;
705
706 OPEN uc_header_information(p_unit_header_id);
707 FETCH uc_header_information INTO l_unit_name,l_root_instance_id,l_mc_header_id;
708 CLOSE uc_header_information;
709
710
711 OPEN get_visit_details_csr;
712 FETCH get_visit_details_csr INTO l_visit_id,l_org_id;
713 CLOSE get_visit_details_csr;
714
715
716 -- MRB Quality
717 OPEN get_MRB_Quality_csr(l_org_id);
718 FETCH get_MRB_Quality_csr INTO l_plan_id;
719 CLOSE get_MRB_Quality_csr;
720 l_mrb_profile_condition := FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_MRB');
721
722 IF(l_plan_id IS NOT NULL AND l_mrb_profile_condition IS NOT NULL) THEN
723 l_quality_setup := 'Y';
724 END IF;
725
726 AHL_UC_TREE_PVT.get_whole_uc_tree(
727 p_api_version => 1.0,
728 p_init_msg_list => FND_API.G_TRUE,
729 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
730 x_return_status => l_return_status,
731 x_msg_count => l_msg_count,
732 x_msg_data => l_msg_data,
733 p_uc_header_id => p_unit_header_id,
734 x_uc_descendant_tbl => l_uc_descendant_tbl);
735 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738
739 OPEN get_max_pk_count;
740 FETCH get_max_pk_count INTO l_curr_pk;
741 CLOSE get_max_pk_count;
742
743
744 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
745 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Value of current pk ' || l_curr_pk);
746 END IF;
747
748 l_curr_row := 1;
749
750 IF l_uc_descendant_tbl.FIRST IS NOT NULL THEN
751 FOR i IN l_uc_descendant_tbl.FIRST..l_uc_descendant_tbl.LAST LOOP
752
753 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Processing Position (' || l_uc_descendant_tbl(i).position_reference || ') ' ||
755 'which has Relationship id = ' || l_uc_descendant_tbl(i).relationship_id);
756 END IF;
757
758 -- select_disable_flag will be set to N for generic behaviour
759 /* select_disable_flag will be 'Y' or HGrid rows in Bulk Part Change UIs will be read only for:
760 * - Root Node
761 * - Control Positions
762 * - In Bulk Part Install page, for:
763 * - Extra Nodes
764 * - Fully Installed Positions
765 * - Children of Control Positions if Control Position is Fully Installed
766 * - In Bulk Part Removal page, for:
767 * - Empty Positions
768 */
769 l_uc_details_tbl(l_curr_row).select_disable_flag := 'N';
770
771 l_uc_details_tbl(l_curr_row).unit_node_id := l_curr_pk;
772 l_uc_details_tbl(l_curr_row).root_instance_id := l_root_instance_id;
773
774 --Calculating path position id
775 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID (
776 p_api_version => 1.0,
777 x_return_status => l_return_status,
778 x_msg_count => l_msg_count,
779 x_msg_data => l_msg_data,
780 p_csi_item_instance_id => l_uc_descendant_tbl(i).parent_instance_id,
781 p_relationship_id => l_uc_descendant_tbl(i).relationship_id,
782 x_path_position_id => l_path_position_id);
783
784 l_uc_details_tbl(l_curr_row).path_position_id := l_path_position_id;
785
786 l_uc_details_tbl(l_curr_row).relationship_id := l_uc_descendant_tbl(i).relationship_id;
787 l_uc_details_tbl(l_curr_row).parent_rel_id := l_uc_descendant_tbl(i).parent_rel_id;
788
789 l_uc_details_tbl(l_curr_row).parent_instance_id := l_uc_descendant_tbl(i).parent_instance_id;
790
791 OPEN get_instance_number(l_uc_descendant_tbl(i).parent_instance_id);
792 FETCH get_instance_number INTO l_uc_details_tbl(l_curr_row).parent_instance_number;
793 CLOSE get_instance_number;
794
795 OPEN get_csi_ii_details(l_uc_descendant_tbl(i).instance_id);
796 FETCH get_csi_ii_details INTO l_uc_details_tbl(l_curr_row).csi_ii_relationship_id,l_uc_details_tbl(l_curr_row).csi_ii_object_version_num;
797 CLOSE get_csi_ii_details;
798
799 l_uc_details_tbl(l_curr_row).curr_instance_id := l_uc_descendant_tbl(i).instance_id;
800
801 l_uc_details_tbl(l_curr_row).position := l_uc_descendant_tbl(i).position_reference;
802 -- Complement leaf node flag to be used for optimizing HGrid in Controller
803 l_uc_details_tbl(l_curr_row).not_leaf_node_flag := CASE l_uc_descendant_tbl(i).leaf_node_flag
804 WHEN 'Y' THEN 'N'
805 ELSE 'Y'
806 END;
807
808 -- Position details image
809 IF l_uc_descendant_tbl(i).node_type = 'X' THEN
810 l_uc_details_tbl(l_curr_row).position_image := G_EXTRA_NODE_IMAGE;
811 ELSIF l_uc_descendant_tbl(i).node_type = 'E' THEN
812 l_uc_details_tbl(l_curr_row).position_image := G_MISSING_NODE_IMAGE;
813 ELSIF l_uc_descendant_tbl(i).node_type = 'S' THEN
814 l_uc_details_tbl(l_curr_row).position_image := G_SB_RULE_VIOLATED_NODE_IMAGE;
815 ELSE
816 l_uc_details_tbl(l_curr_row).position_image := G_FILLED_NODE_IMAGE;
817 END IF;
818
819 -- Populating Node Type and Serial Controlled Values
820 l_uc_details_tbl(l_curr_row).is_position_srl_controlled := AHL_MC_PATH_POSITION_PVT.Is_Position_Serial_Controlled(l_uc_descendant_tbl(i).relationship_id,l_path_position_id);
821 l_uc_details_tbl(l_curr_row).node_type := l_uc_descendant_tbl(i).node_type;
822
823 IF l_uc_details_tbl(l_curr_row).curr_instance_id IS NOT NULL THEN
824 -- Instance details, installed quantity and date
825 OPEN get_part_info(l_uc_details_tbl(l_curr_row).curr_instance_id);
826 FETCH get_part_info INTO l_uc_details_tbl(l_curr_row).curr_item_id,
827 l_uc_details_tbl(l_curr_row).curr_item_number,
828 l_uc_details_tbl(l_curr_row).curr_instance_number,
829 l_uc_details_tbl(l_curr_row).curr_serial_number,
830 l_uc_details_tbl(l_curr_row).curr_quantity,
831 l_uc_details_tbl(l_curr_row).curr_lot_number,
832 l_uc_details_tbl(l_curr_row).unit_of_measure,
833 l_uc_details_tbl(l_curr_row).curr_instance_obj_ver_num;
834 CLOSE get_part_info;
835 END IF;
836
837 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
838 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
839 'curr_item_number = '||l_uc_details_tbl(l_curr_row).curr_item_number||
840 'curr_instance_id :='|| l_uc_descendant_tbl(i).instance_id);
841 END IF;
842
843 -- ATA meaning
844 OPEN get_mc_details_csr (l_uc_details_tbl(l_curr_row).relationship_id);
845 FETCH get_mc_details_csr INTO l_uc_details_tbl(l_curr_row).ata;
846 CLOSE get_mc_details_csr;
847
848 -- hGrid root path, since instance can be null
849 IF l_uc_details_tbl(l_curr_row).relationship_id IS NOT NULL THEN
850 l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).relationship_id) := 0;
851 IF l_uc_details_tbl(l_curr_row).parent_rel_id IS NULL THEN
852 l_root_path_tbl(l_uc_details_tbl(l_curr_row).relationship_id) := '0';
853 ELSE
854 l_root_path_tbl(l_uc_details_tbl(l_curr_row).relationship_id) := l_root_path_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) || ',' ||
855 l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) ;
856 l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) := l_min_child_id_tbl(l_uc_details_tbl(l_curr_row).parent_rel_id) + 1;
857 END IF;
858 l_uc_details_tbl(l_curr_row).hgrid_node_path := l_root_path_tbl(l_uc_details_tbl(l_curr_row).relationship_id);
859 END IF;
860
861 --Controlled(Container) Position
862 l_uc_details_tbl(l_curr_row).is_controlled_position := 'N';
863 OPEN get_controled_position_csr (l_mc_header_id,l_path_position_id);
864 FETCH get_controled_position_csr INTO l_dummy_val, l_control_pos_qty;
865 IF (get_controled_position_csr%FOUND) THEN
866 l_uc_details_tbl(l_curr_row).is_controlled_position := 'Y';
867 l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
868 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_NOT_DEFINED';
869 l_uc_details_tbl(l_curr_row).qty_per_position := l_control_pos_qty;
870 END IF;
871 CLOSE get_controled_position_csr;
872
873 -- Position Quantity
874 -- Control Positions do not conform to calculations for normal positions
875 IF (l_uc_details_tbl(l_curr_row).is_controlled_position = 'N') THEN
876
877 -- In case of Non-Serial Controlled Position
878 IF (l_uc_details_tbl(l_curr_row).is_position_srl_controlled = 'N') THEN
879
880 -- Invoke API Calc_Position_Qty
881 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
882 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Invoking API Calc_Position_Qty');
883 END IF;
884
885 Calc_Position_Qty
886 (
887 p_mc_relationship_id => l_uc_details_tbl(l_curr_row).relationship_id,
888 p_instance_id => l_uc_details_tbl(l_curr_row).curr_instance_id,
889 x_position_qty => l_uc_details_tbl(l_curr_row).qty_per_position,
890 x_uom_meaning => l_uc_details_tbl(l_curr_row).unit_of_measure
891 );
892
893 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'After call to API Calc_Position_Qty');
895 END IF;
896
897 /* Install Quantity (maximum installable quantity)
898 * If no item is defaulted, then it will be Position quantity - Currently installed quantity.
899 * This field is used only for Empty and Partially Installed positions. For other positions which are
900 * displayed in read only mode in HGrid, Current quantity is displayed in Install Quantity column.
901 */
902 l_uc_details_tbl(l_curr_row).install_quantity := l_uc_details_tbl(l_curr_row).qty_per_position -
903 NVL(l_uc_details_tbl(l_curr_row).curr_quantity, 0);
904
905 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
906 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Install Quantity = ' || l_uc_details_tbl(l_curr_row).install_quantity);
907 END IF;
908
909 -- In case of Serial Controlled Position
910 ELSE
911 l_uc_details_tbl(l_curr_row).qty_per_position := 1;
912 l_uc_details_tbl(l_curr_row).install_quantity := 1;
913
914 OPEN get_uom_meaning_csr ('Ea');
915 FETCH get_uom_meaning_csr INTO l_uc_details_tbl(l_curr_row).unit_of_measure;
916 CLOSE get_uom_meaning_csr;
917 END IF;
918 END IF;
919
920
921 IF(l_uc_details_tbl(l_curr_row).is_position_srl_controlled = 'Y') THEN
922 --Populate Disposition Details
923 AHL_PRD_DISP_UTIL_PVT.Get_Part_Change_Disposition (
924 p_api_version => 1.0,
925 p_init_msg_list => FND_API.G_FALSE,
926 p_commit => FND_API.G_FALSE,
927 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
928 x_return_status => l_return_status,
929 x_msg_count => l_msg_count,
930 x_msg_data => l_msg_data,
931 p_workorder_id => p_workorder_id,
932 p_parent_instance_id => l_uc_descendant_tbl(i).parent_instance_id,
933 p_relationship_id => l_uc_descendant_tbl(i).relationship_id,
934 p_instance_id => l_uc_descendant_tbl(i).instance_id,
935 x_disposition_rec => l_disposition_rec,
936 x_imm_disp_type_tbl => l_imm_disp_type_tbl,
937 x_sec_disp_type_tbl => l_sec_disp_type_tbl);
938
939
940 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
941 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
942 l_full_name,
943 'Returned from call to AHL_PRD_DISP_UTIL_PVT.Get_Part_Change_Disposition: l_return_status ' || l_return_status);
944 END IF;
945
946 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
947 RAISE FND_API.G_EXC_ERROR;
948 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 END IF;
951
952 l_uc_details_tbl(l_curr_row).disposition_id := l_disposition_rec.disposition_id;
953 l_uc_details_tbl(l_curr_row).disposition_obj_ver_num := l_disposition_rec.object_version_number;
954 l_uc_details_tbl(l_curr_row).disposition_status := l_disposition_rec.disposition_status;
955 l_uc_details_tbl(l_curr_row).comments := l_disposition_rec.comments;
956 l_uc_details_tbl(l_curr_row).immediate_disposition_code := l_disposition_rec.immediate_disposition_code;
957 l_uc_details_tbl(l_curr_row).immediate_disposition := l_disposition_rec.immediate_disposition;
958 l_uc_details_tbl(l_curr_row).secondary_disposition_code := l_disposition_rec.secondary_disposition_code;
959 l_uc_details_tbl(l_curr_row).secondary_disposition := l_disposition_rec.secondary_disposition;
960 l_uc_details_tbl(l_curr_row).collection_id := l_disposition_rec.collection_id;
961 l_uc_details_tbl(l_curr_row).condition_id := l_disposition_rec.condition_id;
962 l_uc_details_tbl(l_curr_row).condition_meaning := l_disposition_rec.condition_meaning;
963 l_uc_details_tbl(l_curr_row).problem_code := l_disposition_rec.problem_code;
964 l_uc_details_tbl(l_curr_row).problem_meaning := l_disposition_rec.problem_meaning;
965 l_uc_details_tbl(l_curr_row).severity_id := l_disposition_rec.severity_id;
966 l_uc_details_tbl(l_curr_row).severity_name := l_disposition_rec.severity_name;
967 l_uc_details_tbl(l_curr_row).summary := l_disposition_rec.summary;
968 l_uc_details_tbl(l_curr_row).disposition_qty := l_disposition_rec.quantity;
969 l_uc_details_tbl(l_curr_row).operation_sequence_num := l_disposition_rec.operation_sequence;
970 l_uc_details_tbl(l_curr_row).part_change_id := l_disposition_rec.part_change_id;
971 l_uc_details_tbl(l_curr_row).sr_incident_id := l_disposition_rec.primary_service_request_id;
972 -- NR details are not passed from above API
973 IF(l_disposition_rec.primary_service_request_id IS NOT NULL) THEN
974 OPEN get_NR_Details(l_disposition_rec.primary_service_request_id);
975 FETCH get_NR_Details INTO l_uc_details_tbl(l_curr_row).resolution_code_id,
976 l_uc_details_tbl(l_curr_row).resolution_code,
977 l_uc_details_tbl(l_curr_row).problem_code,
978 l_uc_details_tbl(l_curr_row).problem_meaning,
979 l_uc_details_tbl(l_curr_row).summary,
980 l_uc_details_tbl(l_curr_row).service_type_id,
981 l_uc_details_tbl(l_curr_row).service_type,
982 l_uc_details_tbl(l_curr_row).estimated_duration,
983 l_uc_details_tbl(l_curr_row).severity_name,
984 l_uc_details_tbl(l_curr_row).severity_id;
985 CLOSE get_NR_Details;
986 END IF;
987
988 /* l_uc_details_tbl(l_curr_row).resolution_code := l_disposition_rec.resolution_meaning;
989 l_uc_details_tbl(l_curr_row).resolution_code_id := l_disposition_rec.resolution_code;
990 l_uc_details_tbl(l_curr_row).estimated_duration := l_disposition_rec.duration;
991 l_uc_details_tbl(l_curr_row).service_type := l_disposition_rec.service_type;
992 l_uc_details_tbl(l_curr_row).service_type_id := l_disposition_rec.service_type_code;
993 */
994 l_uc_details_tbl(l_curr_row).disposition_count := 1;
995
996
997 ELSE -- not serial controlled
998
999 l_uc_details_tbl(l_curr_row).disposition_count:= 0;
1000
1001 OPEN get_Disposition_Details (l_uc_descendant_tbl(i).instance_id,l_path_position_id);
1002 FETCH get_Disposition_Details INTO l_disp_rec;
1003 IF (get_Disposition_Details%FOUND) THEN
1004 l_uc_details_tbl(l_curr_row).disposition_count:= 1;
1005
1006 FETCH get_Disposition_Details INTO l_temp_disp_rec;
1007 -- If more than one matching disposition: Pass -1 so that Part Change can handle this appropriately.
1008 IF (get_Disposition_Details%FOUND) THEN
1009 l_uc_details_tbl(l_curr_row).disposition_id := -1;
1010 l_uc_details_tbl(l_curr_row).disposition_count:= get_Disposition_Details%ROWCOUNT;
1011 ELSE
1012 -- Only 1 row, populate details
1013 l_uc_details_tbl(l_curr_row).disposition_id := l_disp_rec.disposition_id;
1014 l_uc_details_tbl(l_curr_row).disposition_obj_ver_num := l_disp_rec.object_version_number;
1015 l_uc_details_tbl(l_curr_row).disposition_status := l_disp_rec.status;
1016 l_uc_details_tbl(l_curr_row).comments := l_disp_rec.comments;
1017 l_uc_details_tbl(l_curr_row).immediate_disposition_code := l_disp_rec.immediate_disposition_code;
1018 l_uc_details_tbl(l_curr_row).immediate_disposition := null;
1019 l_uc_details_tbl(l_curr_row).secondary_disposition_code := l_disp_rec.secondary_disposition_code;
1020 l_uc_details_tbl(l_curr_row).secondary_disposition := null;
1021 l_uc_details_tbl(l_curr_row).collection_id := l_disp_rec.collection_id;
1022 l_uc_details_tbl(l_curr_row).condition_id := l_disp_rec.condition_id;
1023 l_uc_details_tbl(l_curr_row).condition_meaning := l_disp_rec.condition_code;
1024 l_uc_details_tbl(l_curr_row).disposition_qty := l_disp_rec.quantity;
1025 l_uc_details_tbl(l_curr_row).operation_sequence_num := l_disp_rec.operation_sequence;
1026 l_uc_details_tbl(l_curr_row).part_change_id := l_disp_rec.part_change_id;
1027 l_uc_details_tbl(l_curr_row).resolution_code_id := l_disp_rec.sr_resolution_code;
1028 l_uc_details_tbl(l_curr_row).resolution_code := l_disp_rec.sr_resolution_meaning;
1029 l_uc_details_tbl(l_curr_row).problem_code := l_disp_rec.sr_problem_code;
1030 l_uc_details_tbl(l_curr_row).problem_meaning := l_disp_rec.sr_problem_meaning;
1031 l_uc_details_tbl(l_curr_row).summary := l_disp_rec.sr_problem_summary;
1032 l_uc_details_tbl(l_curr_row).service_type_id := l_disp_rec.service_type_code;
1033 l_uc_details_tbl(l_curr_row).service_type := l_disp_rec.service_type;
1034 l_uc_details_tbl(l_curr_row).estimated_duration := l_disp_rec.duration;
1035 l_uc_details_tbl(l_curr_row).severity_name := l_disp_rec.sr_incident_severity;
1036 l_uc_details_tbl(l_curr_row).severity_id := l_disp_rec.sr_incident_severity_id;
1037 l_uc_details_tbl(l_curr_row).sr_incident_id := l_disp_rec.sr_incident_id;
1038 l_uc_details_tbl(l_curr_row).disposition_count := 1;
1039 END IF;
1040 END IF;
1041 CLOSE get_Disposition_Details;
1042 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1043 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
1044 'Total Disp for non-serial position = ' || l_uc_details_tbl(l_curr_row).disposition_count);
1045 END IF;
1046 END IF; -- serial controlled check
1047
1048 -- MRB Quality
1049 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_NOT_ALLOWED';
1050 IF(l_quality_setup = 'Y') THEN
1051 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_DISABLED';
1052
1053 IF(l_uc_details_tbl(l_curr_row).condition_id = l_mrb_profile_condition) THEN
1054 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_NOT_ENTERED';
1055 IF(l_uc_details_tbl(l_curr_row).collection_id IS NOT NULL) THEN
1056 --May want to check collection id matches plan id
1057 OPEN get_paln_id(l_uc_details_tbl(l_curr_row).collection_id);
1058 FETCH get_paln_id into l_coll_plan_id;
1059 CLOSE get_paln_id;
1060 IF(l_plan_id = l_coll_plan_id)THEN
1061 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_ENTERED';
1062 ELSE
1063 l_uc_details_tbl(l_curr_row).collection_id := null;
1064 END IF;
1065 END IF;
1066 ELSE
1067 IF(l_uc_details_tbl(l_curr_row).collection_id IS NOT NULL) THEN
1068 --May want to check collection id matches plan id
1069 OPEN get_paln_id(l_uc_details_tbl(l_curr_row).collection_id);
1070 FETCH get_paln_id into l_coll_plan_id;
1071 CLOSE get_paln_id;
1072 IF(l_plan_id = l_coll_plan_id)THEN
1073 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QTY_ENT_DISABLED';
1074 ELSE
1075 l_uc_details_tbl(l_curr_row).collection_id := null;
1076 END IF;
1077
1078 END IF;
1079
1080 END IF;
1081
1082 END IF;
1083
1084 -- For setting Quantity as Text, input Text or LOV
1085 --Default always text (read only field)
1086 l_uc_details_tbl(l_curr_row).qty_ui_element_typ := 'text';
1087 l_rel_id := l_uc_descendant_tbl(i).relationship_id;
1088 l_par_rel_id := l_uc_descendant_tbl(i).parent_rel_id;
1089 l_instance_id := l_uc_descendant_tbl(i).instance_id;
1090
1091 UC_NODE := (l_rel_id IS NOT NULL AND l_par_rel_id IS NOT NULL AND l_instance_id IS NOT NULL);
1092 UC_EXTRA_NODE := (l_rel_id IS NULL AND l_par_rel_id IS NOT NULL AND l_instance_id IS NOT NULL);
1093 UC_EMPTY_NODE := (l_rel_id IS NOT NULL AND l_par_rel_id IS NOT NULL AND l_instance_id IS NULL);
1094
1095 -- For Removal Case :
1096 IF(p_bulk_process_flag = 'R') THEN
1097
1098 IF(UC_NODE OR UC_EXTRA_NODE) THEN
1099 IF(l_uc_details_tbl(l_curr_row).is_position_srl_controlled = 'Y' OR l_uc_details_tbl(l_curr_row).disposition_count = 1) THEN
1100 l_uc_details_tbl(l_curr_row).qty_ui_element_typ := 'text';
1101 ELSIF (l_uc_details_tbl(l_curr_row).disposition_count = 0) THEN
1102 l_uc_details_tbl(l_curr_row).qty_ui_element_typ := 'textInput';
1103 ELSE
1104 l_uc_details_tbl(l_curr_row).qty_ui_element_typ := 'lov';
1105 END IF;
1106 END IF;
1107 --Empty Position
1108 IF(UC_EMPTY_NODE) THEN
1109 l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
1110 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_NOT_ALLOWED';
1111
1112 OPEN get_last_part_chg_dtls_csr (l_uc_details_tbl(l_curr_row).path_position_id,
1113 l_uc_details_tbl(l_curr_row).curr_instance_id,
1114 l_visit_id);
1115 FETCH get_last_part_chg_dtls_csr INTO l_uc_details_tbl(l_curr_row).operation_sequence_num,
1116 l_uc_details_tbl(l_curr_row).removal_date,
1117 l_uc_details_tbl(l_curr_row).removal_code_id,
1118 l_uc_details_tbl(l_curr_row).removal_meaning,
1119 l_uc_details_tbl(l_curr_row).reason_id,
1120 l_uc_details_tbl(l_curr_row).reason_name,
1121 --removed instance id
1122 l_instance_id;
1123 CLOSE get_last_part_chg_dtls_csr;
1124
1125 IF(l_instance_id IS NOT NULL) THEN
1126 OPEN get_part_info(l_instance_id);
1127 FETCH get_part_info INTO l_uc_details_tbl(l_curr_row).curr_item_id,
1128 l_uc_details_tbl(l_curr_row).curr_item_number,
1129 l_uc_details_tbl(l_curr_row).curr_instance_number,
1130 l_uc_details_tbl(l_curr_row).curr_serial_number,
1131 l_uc_details_tbl(l_curr_row).curr_quantity,
1132 l_uc_details_tbl(l_curr_row).curr_lot_number,
1133 l_uc_details_tbl(l_curr_row).unit_of_measure,
1134 l_uc_details_tbl(l_curr_row).curr_instance_obj_ver_num;
1135 CLOSE get_part_info;
1136 END IF;
1137
1138 END IF;
1139
1140 ELSIF (p_bulk_process_flag = 'I') THEN -- Install Case
1141
1142 IF(UC_NODE OR UC_EXTRA_NODE OR UC_EMPTY_NODE) THEN
1143 IF(l_uc_details_tbl(l_curr_row).is_position_srl_controlled = 'Y') THEN
1144 l_uc_details_tbl(l_curr_row).qty_ui_element_typ := 'text';
1145 ELSE
1146 l_uc_details_tbl(l_curr_row).qty_ui_element_typ := 'textInput';
1147 END IF;
1148
1149 /* For Extra nodes which are already connected instances,
1150 * - HGrid rows should be read only in Bulk Part Install UI.
1151 * - Position quantity will be null as it doesn't have a position in UC.
1152 */
1153 IF (UC_EXTRA_NODE) THEN
1154 l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
1155 l_uc_details_tbl(l_curr_row).qty_per_position := null;
1156
1157 /* For Fully Installed positions,
1158 * - HGrid rows should be read only in Bulk Part Install UI.
1159 * - Fetch last part install details for the current instance in current UC position.
1160 */
1161 ELSIF (UC_NODE AND
1162 NVL(l_uc_details_tbl(l_curr_row).curr_quantity, 0) >= l_uc_details_tbl(l_curr_row).qty_per_position) THEN
1163 l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
1164
1165 OPEN get_last_part_chg_dtls_csr (l_uc_details_tbl(l_curr_row).path_position_id,
1166 l_uc_details_tbl(l_curr_row).curr_instance_id,
1167 l_visit_id);
1168 FETCH get_last_part_chg_dtls_csr INTO l_uc_details_tbl(l_curr_row).operation_sequence_num,
1169 l_uc_details_tbl(l_curr_row).install_date,
1170 l_uc_details_tbl(l_curr_row).removal_code_id,
1171 l_uc_details_tbl(l_curr_row).removal_meaning,
1172 l_uc_details_tbl(l_curr_row).reason_id,
1173 l_uc_details_tbl(l_curr_row).reason_name,
1174 l_instance_id;
1175 CLOSE get_last_part_chg_dtls_csr;
1176 END IF;
1177
1178 -- For Empty and Partially Installed positions (not control positions),
1179 -- Installable item instance needs to be defaulted.
1180 IF (UC_EMPTY_NODE OR
1181 (UC_NODE AND
1182 NVL(l_uc_details_tbl(l_curr_row).curr_quantity, 0) < l_uc_details_tbl(l_curr_row).qty_per_position) AND
1183 l_uc_details_tbl(l_curr_row).is_controlled_position = 'N') THEN
1184
1185 -- Call API AHL_UC_INSTANCE_PVT.Get_Available_Instances to find all available instances that
1186 -- can be installed at the current position.
1187 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1188 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Invoking API AHL_UC_INSTANCE_PVT.Get_Available_Instances for parameters: ');
1189 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Parent Instance Id = ' || l_uc_details_tbl(l_curr_row).parent_instance_id);
1190 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Relationship Id = ' || l_uc_details_tbl(l_curr_row).relationship_id);
1191 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Workorder Id = ' || p_workorder_id);
1192 END IF;
1193
1194 AHL_UC_INSTANCE_PVT.Get_Available_Instances
1195 (
1196 p_api_version => 1.0,
1197 p_init_msg_list => FND_API.G_FALSE,
1198 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1199 x_return_status => l_return_status,
1200 x_msg_count => l_msg_count,
1201 x_msg_data => l_msg_data,
1202 p_parent_instance_id => l_uc_details_tbl(l_curr_row).parent_instance_id,
1203 p_relationship_id => l_uc_details_tbl(l_curr_row).relationship_id,
1204 p_workorder_id => p_workorder_id,
1205 p_start_row_index => 1,
1206 -- if more than 1 instance is available, then we ignore them, so better not to fetch more than 2 rows
1207 p_max_rows => 2,
1208 x_available_instance_tbl => l_available_instance_tbl,
1209 x_tbl_count => l_total_available_instances
1210 );
1211
1212 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1213 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'After call to API AHL_UC_INSTANCE_PVT.Get_Available_Instances. Return Status=' || l_return_status
1214 || ' Total Available Instances=' || l_total_available_instances);
1215 END IF;
1216
1217 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1218 RAISE FND_API.G_EXC_ERROR;
1219 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222
1223 -- If there is only 1 available instance for the current position, then default it.
1224 -- Otherwise, no defaulting is required.
1225 IF (l_total_available_instances = 1) THEN
1226
1227 -- Default installable instance in uc_details_tbl
1228 l_avai_inst_row := l_available_instance_tbl.FIRST;
1229 l_uc_details_tbl(l_curr_row).inst_instance_id := l_available_instance_tbl(l_avai_inst_row).csi_item_instance_id;
1230 l_uc_details_tbl(l_curr_row).inst_item_number := l_available_instance_tbl(l_avai_inst_row).item_number;
1231 l_uc_details_tbl(l_curr_row).inst_instance_number := l_available_instance_tbl(l_avai_inst_row).csi_instance_number;
1232 l_uc_details_tbl(l_curr_row).inst_serial_number := l_available_instance_tbl(l_avai_inst_row).serial_number;
1233 l_uc_details_tbl(l_curr_row).inst_instance_obj_ver_num := l_available_instance_tbl(l_avai_inst_row).csi_object_version_number;
1234 l_uc_details_tbl(l_curr_row).instance_quantity := l_available_instance_tbl(l_avai_inst_row).quantity;
1235
1236 /* Also add the defaulted instance and row index of UC table for which it is defaulted in
1237 * table l_inst_uc_pos_row_indx_tbl as explained in comments while table declaration for modified
1238 * defaulting logic.
1239 */
1240 l_inst_id_index := l_uc_details_tbl(l_curr_row).inst_instance_id;
1241
1242 IF (l_inst_uc_pos_row_indx_tbl.EXISTS(l_inst_id_index)) THEN
1243 l_uc_pos_row_index := l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl.COUNT + 1;
1244 ELSE
1245 l_uc_pos_row_index := 1;
1246 END IF;
1247
1248 -- Array[instance_id][1] = UC details table index of the row under consideration
1249 l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl(l_uc_pos_row_index) := l_curr_row;
1250
1251 END IF; -- IF l_total_available_instances = 1
1252
1253 END IF; -- IF empty or partially installed position
1254 END IF; -- IF UC_NODE OR UC_EXTRA_NODE OR UC_EMPTY_NODE
1255
1256 END IF; -- IF p_bulk_process_flag = 'R'
1257
1258 -- search flag
1259 -- for root node set Y, N otherwise
1260 IF l_uc_details_tbl(l_curr_row).root_instance_id = l_uc_details_tbl(l_curr_row).curr_instance_id THEN
1261 l_uc_details_tbl(l_curr_row).search_flag := 'Y';
1262 l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
1263 l_uc_details_tbl(l_curr_row).mrb_quality_typ := 'MRB_QUALITY_NOT_ALLOWED';
1264 ELSE
1265 l_uc_details_tbl(l_curr_row).search_flag := 'N';
1266 END IF;
1267
1268 -- For future use : keep it null as of now
1269 -- node_level
1270
1271 l_curr_row := l_curr_row + 1;
1272 l_curr_pk := l_curr_pk + 1;
1273 l_path_position_id := null;
1274 l_coll_plan_id := null;
1275
1276 END LOOP; -- FOR l_uc_descendant_tbl.FIRST..l_uc_descendant_tbl.LAST
1277
1278 /* (Functional) According to modified logic for defaulting installable item for a position,
1279 * if same item instance is defaulted for more than 1 UC position, then defaulting should not happen for
1280 * any of these positions. In other words, care must be taken not to default same item instance for
1281 * multiple positions.
1282 */
1283 /* (Technical) Following logic is used to achieve this.
1284 * 1. Each row of an Associative Array indexed by Instance id contains all Positions' record
1285 * which have same instance id (associative array l_inst_uc_pos_row_indx_tbl is already populated above).
1286 * 2. If any row (instance id) of array contains record for more than 1 position
1287 * (same instance for more than 1 position) i.e. uc_pos_row_index_tbl.COUNT > 1, then nullify all such records.
1288 */
1289 l_inst_id_index := l_inst_uc_pos_row_indx_tbl.FIRST;
1290 WHILE l_inst_id_index IS NOT NULL LOOP
1291
1292 -- step 2
1293 IF (l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl.COUNT > 1) THEN
1294 -- nullify defaulting for all positions
1295 FOR i IN l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl.FIRST..l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl.LAST LOOP
1296
1297 l_uc_pos_row_index := l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl(i);
1298 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1299 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Defaulting should not happen for Position (' || l_uc_details_tbl(l_uc_pos_row_index).position || ')');
1300 END IF;
1301
1302 l_uc_details_tbl(l_uc_pos_row_index).inst_instance_id := null;
1303 l_uc_details_tbl(l_uc_pos_row_index).inst_item_number := null;
1304 l_uc_details_tbl(l_uc_pos_row_index).inst_instance_number := null;
1305 l_uc_details_tbl(l_uc_pos_row_index).inst_serial_number := null;
1306 l_uc_details_tbl(l_uc_pos_row_index).inst_instance_obj_ver_num := null;
1307 l_uc_details_tbl(l_uc_pos_row_index).instance_quantity := null;
1308 END LOOP;
1309
1310 -- defaulting should happen for this UC position
1311 ELSE
1312 l_uc_pos_row_index := l_inst_uc_pos_row_indx_tbl(l_inst_id_index).uc_pos_row_index_tbl(1);
1313 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1314 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Defaulting installable item instance (' || l_uc_details_tbl(l_uc_pos_row_index).inst_instance_id
1315 || ') for Position (' || l_uc_details_tbl(l_uc_pos_row_index).position || ')');
1316 END IF;
1317
1318 -- Recalculate quantities for Non-Serial Controlled Positions
1319 IF (l_uc_details_tbl(l_uc_pos_row_index).is_position_srl_controlled = 'N') THEN
1320
1321 /* Recalculate Position Quantity for positions where Item instance is defaulted,
1322 * since it depends on MC Relationship id and Instance id.
1323 */
1324 -- Invoke API Calc_Position_Qty
1325 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1326 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Again Invoking API Calc_Position_Qty');
1327 END IF;
1328
1329 Calc_Position_Qty
1330 (
1331 p_mc_relationship_id => l_uc_details_tbl(l_uc_pos_row_index).relationship_id,
1332 p_instance_id => l_uc_details_tbl(l_uc_pos_row_index).inst_instance_id,
1333 x_position_qty => l_uc_details_tbl(l_uc_pos_row_index).qty_per_position,
1334 x_uom_meaning => l_uc_details_tbl(l_uc_pos_row_index).unit_of_measure
1335 );
1336
1337 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1338 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'After call to API Calc_Position_Qty');
1339 END IF;
1340
1341 /* Recalculate Instance Quantity for positions where Item instance is defaulted. It will be minimum
1342 * of available instance qty and maximum qty that can be installed considering Current qty as well.
1343 */
1344 l_uc_details_tbl(l_uc_pos_row_index).install_quantity := l_uc_details_tbl(l_uc_pos_row_index).qty_per_position -
1345 NVL(l_uc_details_tbl(l_uc_pos_row_index).curr_quantity, 0);
1346
1347 l_uc_details_tbl(l_uc_pos_row_index).install_quantity := LEAST(l_uc_details_tbl(l_uc_pos_row_index).instance_quantity,
1348 l_uc_details_tbl(l_uc_pos_row_index).install_quantity);
1349
1350 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1351 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Install Quantity = ' || l_uc_details_tbl(l_uc_pos_row_index).install_quantity);
1352 END IF;
1353
1354 END IF; -- IF l_uc_details_tbl(l_curr_row).is_position_srl_controlled = 'N'
1355 END IF;
1356
1357 l_inst_id_index := l_inst_uc_pos_row_indx_tbl.NEXT(l_inst_id_index);
1358 END LOOP; -- WHILE l_inst_id_index IS NOT NULL
1359 END IF; -- IF l_uc_descendant_tbl.FIRST IS NOT NULL
1360
1361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Done fetching details');
1363 END IF;
1364
1365 x_uc_details_tbl := l_uc_details_tbl;
1366 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1367 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_full_name, 'Exiting Procedure');
1368 END IF;
1369
1370 END get_uc_details;
1371 ------------------------------------------------------------------------------------
1372
1373 ------------------------------------------------------------------------------------
1374 -- Procedure name : Calc_Position_Qty
1375 -- Type : Private
1376 -- Function : Procedure to calculate Position quantity based on MC Relationship id and Instance id.
1377 -- It does the following:
1378 -- 1. If instance id is null, then pick quantity and uom from MC Position.
1379 -- 2. If instance id is not null, then pick quantity and uom from correspoding Item group.
1380 -- 3. If instance uom is different from MC uom, then convert quantity to its equivalent
1381 -- in instance uom.
1382 --
1383 -- Pre-reqs :
1384 -- Parameters :
1385 --
1386 -- Calc_Position_Qty Parameters:
1387 -- p_mc_relationship_id IN Given mc relationship id. Required
1388 -- p_instance_id IN Given item instance id. Required
1389 -- x_position_qty OUT NOCOPY Calculated Position quantity. Required
1390 -- x_uom_meaning OUT NOCOPY Corr. Unit of Measure. Required
1391 --
1392 -- End of Comments
1393
1394 PROCEDURE Calc_Position_Qty (
1395 p_mc_relationship_id IN NUMBER,
1396 p_instance_id IN NUMBER,
1397 x_position_qty OUT NOCOPY NUMBER,
1398 x_uom_meaning OUT NOCOPY VARCHAR2
1399 ) IS
1400
1401 -- Cursor to get the Position Quantity details
1402 CURSOR get_pos_qty_details_csr IS
1403 SELECT iasso.quantity Itm_qty,
1404 iasso.uom_code Itm_uom_code,
1405 reln.quantity Posn_qty,
1406 reln.uom_code Posn_uom_code,
1407 csi.inventory_item_id,
1408 csi.quantity Inst_qty,
1409 csi.unit_of_measure Inst_uom_code
1410
1411 FROM AHL_MC_RELATIONSHIPS reln,
1412 AHL_ITEM_ASSOCIATIONS_B iasso,
1413 CSI_ITEM_INSTANCES csi
1414
1415 WHERE reln.relationship_id = p_mc_relationship_id
1416 AND csi.instance_id = p_instance_id
1417 AND iasso.item_group_id = reln.item_group_id
1418 AND iasso.inventory_item_id = csi.inventory_item_id
1419 AND (iasso.revision IS NULL OR iasso.revision = csi.inventory_revision)
1420 AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1421 UNION ALL
1422 SELECT to_number(null) Itm_qty,
1423 to_char(null) Itm_uom_code,
1424 quantity Posn_qty,
1425 uom_code Posn_uom_code,
1426 to_number(null) Inventory_item_id,
1427 to_number(null) Inst_qty,
1428 to_char(null) Inst_uom_code
1429 FROM AHL_MC_RELATIONSHIPS
1430 WHERE p_instance_id IS NULL
1431 AND relationship_id = p_mc_relationship_id;
1432
1433 -- Cursor to get UOM meaning given UOM code
1434 CURSOR get_uom_meaning_csr (c_uom_code VARCHAR2) IS
1435 SELECT unit_of_measure
1436 FROM MTL_UNITS_OF_MEASURE_VL
1437 WHERE uom_code = c_uom_code;
1438
1439 --
1440 l_api_name CONSTANT VARCHAR2(30) := 'Calc_Position_Qty';
1441 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1442
1443 l_pos_dtls_rec get_pos_qty_details_csr%ROWTYPE;
1444 l_quantity NUMBER;
1445 --
1446
1447 BEGIN
1448 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1449 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_full_name || '.begin', 'At the start of the API');
1450 END IF;
1451
1452 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1453 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'p_mc_relationship_id = ' || p_mc_relationship_id);
1454 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'p_instance_id = ' || p_instance_id);
1455 END IF;
1456
1457 IF (p_mc_relationship_id IS NOT NULL) THEN
1458
1459 -- 1. Pick quantity and uom from MC Position.
1460 OPEN get_pos_qty_details_csr;
1461 FETCH get_pos_qty_details_csr INTO l_pos_dtls_rec;
1462 IF (get_pos_qty_details_csr%FOUND) THEN
1463
1464 IF (p_instance_id IS NOT NULL) THEN
1465 -- 2. If instance id is not null, then pick quantity and uom from correspoding Item group.
1466 IF (NVL(l_pos_dtls_rec.Itm_qty, 0) > 0) THEN
1467 l_pos_dtls_rec.Posn_qty := l_pos_dtls_rec.Itm_qty;
1468 l_pos_dtls_rec.Posn_uom_code := l_pos_dtls_rec.Itm_uom_code;
1469 END IF;
1470
1471 -- 3. If UOMs are different, then convert quantity to its equivalent in instance uom.
1472 IF (l_pos_dtls_rec.Posn_uom_code <> l_pos_dtls_rec.Inst_uom_code) THEN
1473 l_quantity := INV_CONVERT.Inv_UM_Convert(item_id => l_pos_dtls_rec.inventory_item_id,
1474 precision => 6,
1475 from_quantity => l_pos_dtls_rec.Posn_qty,
1476 from_unit => l_pos_dtls_rec.Posn_uom_code,
1477 to_unit => l_pos_dtls_rec.Inst_uom_code,
1478 from_name => NULL,
1479 to_name => NULL);
1480 l_pos_dtls_rec.Posn_qty := l_quantity;
1481 l_pos_dtls_rec.Posn_uom_code := l_pos_dtls_rec.Inst_uom_code;
1482 END IF;
1483 END IF;
1484
1485 -- Assign quantity to OUT variable
1486 x_position_qty := l_pos_dtls_rec.Posn_qty;
1487
1488 -- Convert UOM code to meaning
1489 OPEN get_uom_meaning_csr (l_pos_dtls_rec.Posn_uom_code);
1490 FETCH get_uom_meaning_csr INTO x_uom_meaning;
1491 CLOSE get_uom_meaning_csr;
1492
1493 END IF; -- IF get_pos_qty_details_csr%FOUND
1494 CLOSE get_pos_qty_details_csr;
1495
1496 END IF; -- IF p_mc_relationship_id IS NOT NULL
1497
1498 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1499 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Position Quantity = ' || x_position_qty);
1500 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'UOM meaning = ' || x_uom_meaning);
1501 END IF;
1502
1503 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1504 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_full_name || '.end', 'At the end of the API');
1505 END IF;
1506
1507 END Calc_Position_Qty;
1508 ------------------------------------------------------------------------------------
1509
1510 ------------------------------------------------------------------------------------
1511 -- Procedure name : Calc_Control_Position_Qty
1512 -- Type : Private
1513 -- Function : Procedure to calculate Position and Install quantities Control Positions and their children.
1514 -- These calculations are exceptions to normal flow.
1515 --
1516 -- Position Quantity:
1517 -- For Control Position - Position qty = Quantity in MC Rule where object type is Total
1518 -- quantity of children.
1519 -- For Its Children - Position qty is not displayed.
1520 --
1521 -- Install Quantity:
1522 -- For Control Position - Install qty = Sum of all Installed instance quantities in
1523 -- child positions.
1524 -- For Its Children - Install qty = Position qty of Child Position - Install qty of Control Position
1525 --
1526 -- Pre-reqs :
1527 -- Parameters :
1528 --
1529 -- Calc_Control_Position_Qty Parameters:
1530 -- p_workorder_id IN Given workorder id. Required
1531 -- p_unit_header_id IN Given UC header id. Required
1532 -- p_root_instance_id IN Given root instance id of Unit. Required
1533 -- p_bulk_process_flag IN Flag for Installation or Removal. Required
1534 --
1535 -- End of Comments
1536
1537 PROCEDURE Calc_Control_Position_Qty (
1538 p_workorder_id IN NUMBER,
1539 p_unit_header_id IN NUMBER,
1540 p_root_instance_id IN NUMBER,
1541 p_bulk_process_flag IN VARCHAR2
1542 ) IS
1543
1544 -- Cursor to get all Control Positions' details in HGrid
1545 CURSOR get_control_pos_details_csr IS
1546 SELECT unit_node_id,
1547 relationship_id,
1548 position,
1549 curr_instance_id,
1550 parent_instance_id,
1551 qty_per_position,
1552 unit_of_measure,
1553 install_quantity
1554 FROM AHL_UC_DETAILS_FOR_HGRID
1555 WHERE root_instance_id = p_root_instance_id
1556 AND is_controlled_position = 'Y';
1557
1558 -- Cursor to get unit of measure for the given Control position
1559 CURSOR get_uom_for_cntrl_pos_csr (c_relationship_id NUMBER) IS
1560 SELECT unit_of_measure
1561 FROM AHL_MC_RELATIONSHIPS RELN,
1562 MTL_UNITS_OF_MEASURE_VL UOM
1563 WHERE RELN.relationship_id = c_relationship_id
1564 AND UOM.uom_code = RELN.uom_code;
1565
1566 -- Cursor to calculate Install qty for the given Control position
1567 CURSOR calc_inst_qty_for_cntrl_pos (c_relationship_id NUMBER, c_instance_id NUMBER, c_parent_instance_id NUMBER) IS
1568 SELECT NVL(SUM(curr_quantity), 0)
1569 FROM AHL_UC_DETAILS_FOR_HGRID
1570 WHERE root_instance_id = p_root_instance_id
1571 AND parent_rel_id = c_relationship_id
1572 AND NVL(parent_instance_id, -10) = NVL(c_instance_id, NVL(c_parent_instance_id, -10));
1573
1574 -- Cursor to get all Child positions for the given Control position
1575 CURSOR get_cntrl_pos_children_csr (c_relationship_id NUMBER, c_instance_id NUMBER, c_parent_instance_id NUMBER) IS
1576 SELECT unit_node_id,
1577 position,
1578 qty_per_position,
1579 install_quantity,
1580 curr_quantity,
1581 inst_instance_id,
1582 instance_quantity,
1583 select_disable_flag,
1584 path_position_id,
1585 curr_instance_id,
1586 operation_sequence_num,
1587 install_date
1588 FROM AHL_UC_DETAILS_FOR_HGRID
1589 WHERE root_instance_id = p_root_instance_id
1590 AND parent_rel_id = c_relationship_id
1591 AND NVL(parent_instance_id, -10) = NVL(c_instance_id, NVL(c_parent_instance_id, -10));
1592
1593 -- Cursor to get part change details for last removal or install of the instance at given UC position
1594 CURSOR get_last_part_chg_dtls_csr (c_path_position_id NUMBER,
1595 c_instance_id NUMBER) IS
1596 SELECT OP.operation_sequence_num,
1597 APC.installation_date
1598 FROM AHL_PART_CHANGES APC,
1599 AHL_WORKORDER_OPERATIONS OP
1600 WHERE APC.part_change_id = (SELECT max(APC1.part_change_id)
1601 FROM AHL_PART_CHANGES APC1,
1602 AHL_WORKORDER_OPERATIONS OP1
1603 WHERE APC1.unit_config_header_id = p_unit_header_id
1604 AND APC1.mc_relationship_id = c_path_position_id -- Table AHL_PART_CHANGES stores path position id in mc_relationship_id field
1605 AND APC1.part_change_type = 'I'
1606 AND APC1.installed_instance_id = c_instance_id
1607 AND OP1.workorder_operation_id = APC1.workorder_operation_id
1608 AND OP1.workorder_id IN (SELECT workorder_id
1609 FROM AHL_WORKORDERS
1610 WHERE visit_id = (SELECT visit_id
1611 FROM AHL_WORKORDERS
1612 WHERE workorder_id = p_workorder_id)))
1613 AND OP.workorder_operation_id = APC.workorder_operation_id;
1614
1615 --
1616 l_api_name CONSTANT VARCHAR2(30) := 'Calc_Control_Position_Qty';
1617 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1618
1619 l_install_qty NUMBER;
1620 l_select_disable_flag VARCHAR2(1);
1621 --
1622
1623 BEGIN
1624 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1625 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_full_name || '.begin', 'At the start of the API');
1626 END IF;
1627
1628 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'p_workorder_id = ' || p_workorder_id);
1630 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'p_unit_header_id = ' || p_unit_header_id);
1631 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'p_root_instance_id = ' || p_root_instance_id);
1632 END IF;
1633
1634 -- Iterate through all Control Positions in the given UC
1635 FOR control_pos_rec IN get_control_pos_details_csr LOOP
1636
1637 -- Position Quantity for Control Positions is already calculated in API Get_UC_Details
1638 -- while determining whether a position is Control position.
1639
1640 -- Find UOM for Control Positions
1641 OPEN get_uom_for_cntrl_pos_csr (control_pos_rec.relationship_id);
1642 FETCH get_uom_for_cntrl_pos_csr INTO control_pos_rec.unit_of_measure;
1643 CLOSE get_uom_for_cntrl_pos_csr;
1644
1645 -- Install Quantity for Control Position
1646 OPEN calc_inst_qty_for_cntrl_pos (control_pos_rec.relationship_id, control_pos_rec.curr_instance_id, control_pos_rec.parent_instance_id);
1647 FETCH calc_inst_qty_for_cntrl_pos INTO control_pos_rec.install_quantity;
1648 CLOSE calc_inst_qty_for_cntrl_pos;
1649
1650 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1651 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For Control Position (' || control_pos_rec.position || ')' ||
1652 ' Position Qty = ' || control_pos_rec.qty_per_position ||
1653 ' Install Qty = ' || control_pos_rec.install_quantity);
1654 END IF;
1655
1656 -- update install quantity for control position in HGrid
1657 UPDATE AHL_UC_DETAILS_FOR_HGRID
1658 SET install_quantity = control_pos_rec.install_quantity,
1659 curr_quantity = control_pos_rec.install_quantity,
1660 unit_of_measure = control_pos_rec.unit_of_measure
1661 WHERE unit_node_id = control_pos_rec.unit_node_id;
1662
1663 -- Check if Control Position is Fully Installed
1664 IF (control_pos_rec.install_quantity >= control_pos_rec.qty_per_position) THEN
1665 l_select_disable_flag := 'Y';
1666 ELSE
1667 l_select_disable_flag := 'N';
1668 END IF;
1669
1670 -- For Control Position's Children
1671 FOR child_pos_rec IN get_cntrl_pos_children_csr (control_pos_rec.relationship_id, control_pos_rec.curr_instance_id, control_pos_rec.parent_instance_id) LOOP
1672
1673 -- Position quantity for Child Position can never exceed that of Control Position.
1674 -- Though Position quantity for Child is not displayed, it is used for Install quantity calculations.
1675 child_pos_rec.qty_per_position := LEAST(control_pos_rec.qty_per_position, child_pos_rec.qty_per_position);
1676
1677 -- Install quantity
1678 child_pos_rec.install_quantity := child_pos_rec.qty_per_position - control_pos_rec.install_quantity;
1679
1680 -- if defaulting has occurred for this position, then it will be minimum of available instance quantity
1681 -- and install quantity (maximum quantity that can be installed)
1682 IF (child_pos_rec.inst_instance_id IS NOT NULL) THEN
1683 child_pos_rec.install_quantity := LEAST(child_pos_rec.instance_quantity,
1684 child_pos_rec.install_quantity);
1685 END IF;
1686
1687 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1688 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For Control Position Child (' || child_pos_rec.position || ')' ||
1689 ' Position Qty = ' || child_pos_rec.qty_per_position ||
1690 ' Install Qty = ' || child_pos_rec.install_quantity);
1691 END IF;
1692 child_pos_rec.qty_per_position := null;
1693
1694 -- If Control Position is fully installed, then even its children are considered as fully installed.
1695 /* For Fully Installed positions,
1696 * - HGrid rows should be read only in Bulk Part Install UI.
1697 * - Fetch last part install details for the current instance in current UC position.
1698 */
1699 IF (p_bulk_process_flag = 'I') THEN
1700 IF (l_select_disable_flag = 'Y' AND child_pos_rec.select_disable_flag = 'N') THEN
1701
1702 child_pos_rec.select_disable_flag := 'Y';
1703
1704 OPEN get_last_part_chg_dtls_csr (child_pos_rec.path_position_id,
1705 child_pos_rec.curr_instance_id);
1706 FETCH get_last_part_chg_dtls_csr INTO child_pos_rec.operation_sequence_num,
1707 child_pos_rec.install_date;
1708 CLOSE get_last_part_chg_dtls_csr;
1709 END IF;
1710 END IF;
1711
1712
1713 -- update quantities for control position's child in HGrid
1714 UPDATE AHL_UC_DETAILS_FOR_HGRID
1715 SET install_quantity = child_pos_rec.install_quantity,
1716 qty_per_position = child_pos_rec.qty_per_position,
1717 select_disable_flag = child_pos_rec.select_disable_flag,
1718 operation_sequence_num = child_pos_rec.operation_sequence_num,
1719 install_date = child_pos_rec.install_date
1720 WHERE unit_node_id = child_pos_rec.unit_node_id;
1721
1722 END LOOP; -- For all children of control positions
1723
1724 END LOOP; -- For all control positions
1725
1726 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1727 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_full_name || '.end', 'At the end of the API');
1728 END IF;
1729
1730 END Calc_Control_Position_Qty;
1731 ------------------------------------------------------------------------------------
1732
1733 ------------------------------------------------------------------------------------
1734 -- Procedure name : Calc_Qty_On_Inst_Selection
1735 -- Type : Public
1736 -- Function : Procedure to recalculate Position and Install quantity on Instance selection from Alternate Items UI.
1737 -- Rules to calculate the quantities differ for Child of Control positions.
1738 -- For Normal Positions,
1739 -- Position Quantity rules are given in comments of API Calc_Position_Qty in package body.
1740 -- Install Quantity = MIN(Available Instance Qty, Max Installable Qty)
1741 -- where Max Installable Qty = Position Qty - Current Qty
1742 --
1743 -- For Child of Control Positions,
1744 -- Rules for Position and Install Qty are given in comments of API Calc_Control_Position_Qty
1745 -- in package body.
1746 --
1747 -- Pre-reqs :
1748 -- Parameters :
1749 --
1750 -- Calc_Qty_On_Inst_Selection Parameters:
1751 -- p_x_uc_details_rec IN OUT NOCOPY Record for given UC position. Required
1752 --
1753 -- End of Comments
1754
1755 PROCEDURE Calc_Qty_On_Inst_Selection(
1756 p_x_uc_details_rec IN OUT NOCOPY uc_details_hgrid_rec_type
1757 ) IS
1758
1759 -- Cursor to determine whether the given position is a child of control position
1760 CURSOR is_child_control_pos_csr IS
1761 SELECT 'Y', path_position_id
1762 FROM AHL_UC_DETAILS_FOR_HGRID
1763 WHERE root_instance_id = p_x_uc_details_rec.root_instance_id
1764 AND relationship_id = p_x_uc_details_rec.parent_rel_id
1765 AND NVL(curr_instance_id, NVL(parent_instance_id, -10)) = NVL(p_x_uc_details_rec.parent_instance_id, -10)
1766 AND is_controlled_position = 'Y';
1767
1768 -- Cursot to find MC header id for the given root instance id
1769 CURSOR get_mc_header_id_csr IS
1770 SELECT master_config_id
1771 FROM AHL_UNIT_CONFIG_HEADERS
1772 WHERE csi_item_instance_id = p_x_uc_details_rec.root_instance_id;
1773
1774 -- Cursor to find position quantity of parent control position
1775 CURSOR get_control_pos_qty_csr (c_mc_header_id NUMBER,
1776 c_control_path_position_id NUMBER) IS
1777 SELECT MCRS.object_attribute1
1778 FROM AHL_MC_PATH_POSITION_NODES MCPP,
1779 AHL_MC_RULE_STATEMENTS MCRS,
1780 AHL_MC_HEADERS_B MCH,
1781 AHL_MC_RULES_B MCR
1782 WHERE MCH.mc_header_id = c_mc_header_id
1783 AND MCPP.mc_id = MCH.mc_id
1784 AND MCH.version_number = NVL(MCPP.version_number, MCH.version_number)
1785 AND MCPP.position_key IN (SELECT position_key
1786 FROM AHL_MC_PATH_POSITION_NODES
1787 WHERE path_position_id = c_control_path_position_id)
1788 AND MCRS.subject_id = MCPP.path_position_id
1789 AND MCRS.subject_type = 'POSITION'
1790 AND MCRS.operator IN ('MUST_HAVE', 'HAVE')
1791 AND MCRS.object_type = 'TOT_CHILD_QUANTITY'
1792 AND MCH.config_status_code = 'COMPLETE'
1793 AND MCR.rule_id = MCRS.rule_id
1794 AND MCR.rule_type_code = 'MANDATORY'
1795 AND NVL(MCR.active_start_date, SYSDATE - 1) < SYSDATE
1796 AND NVL(MCR.active_end_date, SYSDATE + 1) > SYSDATE;
1797
1798 -- Cursor to find install quantity of parent control position
1799 CURSOR get_cntl_pos_install_qty_csr IS
1800 SELECT NVL(SUM(curr_quantity), 0)
1801 FROM AHL_UC_DETAILS_FOR_HGRID
1802 WHERE root_instance_id = p_x_uc_details_rec.root_instance_id
1803 AND parent_rel_id = p_x_uc_details_rec.parent_rel_id
1804 AND NVL(parent_instance_id, -10) = NVL(p_x_uc_details_rec.parent_instance_id, -10);
1805
1806 --
1807 l_api_name CONSTANT VARCHAR2(30) := 'Calc_Qty_On_Inst_Selection';
1808 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1809
1810 l_mc_header_id NUMBER;
1811 l_is_child_control_pos VARCHAR2(1) := 'N';
1812 l_control_path_position_id NUMBER;
1813 l_control_position_qty NUMBER;
1814 l_control_pos_install_qty NUMBER;
1815 --
1816
1817 BEGIN
1818 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1819 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_full_name || '.begin', 'At the start of the API');
1820 END IF;
1821
1822 /* Calculation logic is relevant only for Non-Serialized Controlled Positions, for Serialized
1823 * Quanitities will always be 1 and will not change on Instance selection.
1824 */
1825 IF (p_x_uc_details_rec.is_position_srl_controlled = 'N') THEN
1826
1827 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1828 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Recalculating Position and Install Quantity for selected instance_id = ' || p_x_uc_details_rec.inst_instance_id);
1829 END IF;
1830
1831 IF (p_x_uc_details_rec.relationship_id IS NOT NULL) THEN
1832
1833 -- Calculate Position Quantity
1834 -- Invoke API Calc_Position_Qty
1835 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1836 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Invoking API Calc_Position_Qty');
1837 END IF;
1838
1839 Calc_Position_Qty
1840 (
1841 p_mc_relationship_id => p_x_uc_details_rec.relationship_id,
1842 p_instance_id => p_x_uc_details_rec.inst_instance_id,
1843 x_position_qty => p_x_uc_details_rec.qty_per_position,
1844 x_uom_meaning => p_x_uc_details_rec.unit_of_measure
1845 );
1846
1847 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1848 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'After call to API Calc_Position_Qty');
1849 END IF;
1850
1851
1852 -- find whether the given position is child of control position
1853 OPEN is_child_control_pos_csr;
1854 FETCH is_child_control_pos_csr INTO l_is_child_control_pos, l_control_path_position_id;
1855 CLOSE is_child_control_pos_csr;
1856
1857 -- for normal positions
1858 IF (l_is_child_control_pos = 'N') THEN
1859
1860 -- Calculate Install Quantity
1861 p_x_uc_details_rec.install_quantity := p_x_uc_details_rec.qty_per_position - NVL(p_x_uc_details_rec.curr_quantity, 0);
1862 p_x_uc_details_rec.install_quantity := LEAST(p_x_uc_details_rec.instance_quantity, p_x_uc_details_rec.install_quantity);
1863
1864 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1865 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For normal position, Position Quantity = ' || p_x_uc_details_rec.qty_per_position);
1866 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For normal position, Install Quantity = ' || p_x_uc_details_rec.install_quantity);
1867 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For normal position, Unit of Measure = ' || p_x_uc_details_rec.unit_of_measure);
1868 END IF;
1869
1870 -- for child of control positions
1871 ELSE
1872
1873 -- get MC header id for the given root instance id
1874 OPEN get_mc_header_id_csr;
1875 FETCH get_mc_header_id_csr INTO l_mc_header_id;
1876 CLOSE get_mc_header_id_csr;
1877
1878 -- First find Position and Install Quantity for Control Position
1879 OPEN get_control_pos_qty_csr (l_mc_header_id, l_control_path_position_id);
1880 FETCH get_control_pos_qty_csr INTO l_control_position_qty;
1881 CLOSE get_control_pos_qty_csr;
1882
1883 OPEN get_cntl_pos_install_qty_csr;
1884 FETCH get_cntl_pos_install_qty_csr INTO l_control_pos_install_qty;
1885 CLOSE get_cntl_pos_install_qty_csr;
1886
1887
1888 -- Then calculate for Child Position
1889 -- Calculate Position Quantity
1890 p_x_uc_details_rec.qty_per_position := LEAST(l_control_position_qty, p_x_uc_details_rec.qty_per_position);
1891
1892 -- Calculate Install quantity
1893 p_x_uc_details_rec.install_quantity := p_x_uc_details_rec.qty_per_position - l_control_pos_install_qty;
1894 p_x_uc_details_rec.install_quantity := LEAST(p_x_uc_details_rec.instance_quantity, p_x_uc_details_rec.install_quantity);
1895
1896 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1897 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For child control position, Position Quantity = ' || p_x_uc_details_rec.qty_per_position);
1898 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For child control position, Install Quantity = ' || p_x_uc_details_rec.install_quantity);
1899 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'For child control position, Unit of Measure = ' || p_x_uc_details_rec.unit_of_measure);
1900 END IF;
1901 p_x_uc_details_rec.qty_per_position := null; -- Position qty for child position is not displayed
1902
1903 END IF; -- IF l_is_child_control_pos = 'N'
1904
1905 END IF; -- IF p_mc_relationship_id IS NOT NULL
1906
1907 END IF; -- IF p_x_uc_details_rec.is_position_srl_controlled = 'N'
1908
1909 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1910 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_full_name || '.end', 'At the end of the API');
1911 END IF;
1912
1913 END Calc_Qty_On_Inst_Selection;
1914 ------------------------------------------------------------------------------------
1915
1916 ------------------------------------------------------------------------------------
1917 -- Start of Comments
1918 -- Procedure name : Filter_records_uc_details
1919 -- Type : Public
1920 -- Function : This procedure will update the temparory table to filter data details in hGrid.
1921 -- Since this table is global temporary with ON COMMIT DELETE ROWS,
1922 -- Always call this method with p_commit value : FND_API.G_FALSE
1923 -- It returns the hGrid_node path of the only node, null otherwise.
1924 -- Currently it filters positions based on following search params
1925 -- UnitHeaderId : Always required
1926 -- Position
1927 -- AtaCode
1928 -- Item
1929 -- SerialNumber
1930 -- Pre-reqs :
1931 -- Parameters :
1932 --
1933 -- Get_visit_ir_uc_details Parameters
1934 -- p_filter_records_tbl IN filter_records__tbl_type
1935 --
1936 -- End of Comments
1937
1938 PROCEDURE Filter_records_uc_details
1939 (
1940 p_api_version IN NUMBER := 1.0,
1941 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1942 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1943 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1944 p_default IN VARCHAR2 := FND_API.G_FALSE,
1945 p_module_type IN VARCHAR2 := NULL,
1946 p_filter_records_tbl IN filter_records_tbl_type,
1947 x_hGrid_node_path OUT NOCOPY VARCHAR2,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2
1951 )IS
1952
1953 l_api_name CONSTANT VARCHAR2(30) := 'Filter_records_uc_details';
1954 l_api_version CONSTANT NUMBER := 1.0;
1955
1956 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1957 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1958 l_sql_cri_str VARCHAR2(30000);
1959 l_buffer_limit NUMBER := 500;
1960 l_uc_node_id_tbl NBR_TBL_TYPE;
1961 l_current_unit_node_id NUMBER;
1962
1963 l_bind_index NUMBER;
1964 j NUMBER;
1965 l_count NUMBER;
1966 l_unit_header_id NUMBER;
1967 l_hGrid_node_path VARCHAR2(240);
1968
1969 CURSOR get_count(c_unit_header_id IN NUMBER) IS
1970 SELECT COUNT(UHC.unit_node_id)
1971 FROM AHL_UC_DETAILS_FOR_HGRID UHC,
1972 AHL_UNIT_CONFIG_HEADERS UC
1973 WHERE UC.unit_config_header_id = c_unit_header_id
1974 AND UHC.root_instance_id = UC.csi_item_instance_id;
1975
1976 CURSOR get_node_path(c_unit_node_id IN NUMBER) IS
1977 SELECT hgrid_node_path
1978 FROM AHL_UC_DETAILS_FOR_HGRID
1979 WHERE unit_node_id = c_unit_node_id;
1980
1981 BEGIN
1982
1983 IF p_filter_records_tbl.FIRST IS NOT NULL THEN
1984 l_sql_cri_str := ' SELECT unit_node_id FROM AHL_UC_DETAILS_FOR_HGRID WHERE 1 = 1 ';
1985 l_bind_index := 1;
1986 l_unit_header_id := -1;
1987 l_hGrid_node_path := null;
1988
1989 FOR i IN p_filter_records_tbl.FIRST..p_filter_records_tbl.LAST LOOP
1990 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1991 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1992 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1993 'Filter criteria ' || i || ' ' ||
1994 p_filter_records_tbl(i).param || ' '||
1995 p_filter_records_tbl(i).param_value);
1996 END IF;
1997 IF p_filter_records_tbl(i).param = 'UnitHeaderId' THEN
1998 l_sql_cri_str := l_sql_cri_str || ' AND root_instance_id = (SELECT csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS ';
1999 l_sql_cri_str := l_sql_cri_str || ' WHERE unit_config_header_id = :UNITHEADERID) ';
2000 l_bindvar_tbl(l_bind_index) := p_filter_records_tbl(i).param_value;
2001 l_bind_index := l_bind_index + 1;
2002 l_unit_header_id := p_filter_records_tbl(i).param_value;
2003 END IF;
2004
2005 IF p_filter_records_tbl(i).param = 'Position' THEN
2006 l_sql_cri_str := l_sql_cri_str || ' AND UPPER(position) like :POSITION ';
2007 l_bindvar_tbl(l_bind_index) := UPPER(p_filter_records_tbl(i).param_value);
2008 l_bind_index := l_bind_index + 1;
2009 END IF;
2010
2011 IF p_filter_records_tbl(i).param = 'Ata' THEN
2012 l_sql_cri_str := l_sql_cri_str || ' AND UPPER(ata) like :ATA ';
2013 l_bindvar_tbl(l_bind_index) := UPPER(p_filter_records_tbl(i).param_value);
2014 l_bind_index := l_bind_index + 1;
2015 END IF;
2016
2017 IF p_filter_records_tbl(i).param = 'Item' THEN
2018 l_sql_cri_str := l_sql_cri_str || ' AND UPPER(CURR_ITEM_NUMBER) like :ITEM';
2019 l_bindvar_tbl(l_bind_index) := UPPER(p_filter_records_tbl(i).param_value);
2020 l_bind_index := l_bind_index + 1;
2021 END IF;
2022
2023 IF p_filter_records_tbl(i).param = 'SerialNumber' THEN
2024 l_sql_cri_str := l_sql_cri_str || ' AND UPPER(CURR_SERIAL_NUMBER) like :SERIALNUMBER ';
2025 l_bindvar_tbl(l_bind_index) := UPPER(p_filter_records_tbl(i).param_value);
2026 l_bind_index := l_bind_index + 1;
2027 END IF;
2028
2029 END LOOP;
2030
2031
2032 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2033 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2034 'criteria query' || l_sql_cri_str);
2035 END IF;
2036
2037 OPEN get_count(l_unit_header_id);
2038 FETCH get_count INTO l_count;
2039 CLOSE get_count;
2040
2041 IF l_count <> 0 THEN
2042 AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
2043 (
2044 p_conditions_tbl => l_bindvar_tbl,
2045 p_sql_str => l_sql_cri_str,
2046 p_x_csr => l_cur
2047 );
2048
2049 FETCH l_cur BULK COLLECT INTO l_uc_node_id_tbl LIMIT l_buffer_limit;
2050 CLOSE l_cur;
2051
2052 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2053 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2054 'Unit header id:' || l_unit_header_id ||
2055 ' Total count:' || l_count ||
2056 ' Filtered count:' || l_uc_node_id_tbl.count);
2057 END IF;
2058
2059 --clear previous searchs if any
2060 UPDATE AHL_UC_DETAILS_FOR_HGRID
2061 SET search_flag = 'N'
2062 WHERE search_flag = 'Y';
2063
2064 -- set search flag 'Y' for the position
2065 -- if filtering returning all the positions then set only the root node as searchable position
2066 -- if filtering returning only one row then return its node path
2067 IF l_uc_node_id_tbl.count = l_count OR
2068 l_uc_node_id_tbl.count = 1
2069 THEN
2070 IF l_uc_node_id_tbl.count = 1 THEN
2071 l_current_unit_node_id := l_uc_node_id_tbl(1);
2072 OPEN get_node_path(l_current_unit_node_id);
2073 FETCH get_node_path INTO l_hGrid_node_path;
2074 CLOSE get_node_path;
2075 END IF;
2076 -- only root node will have search flag as 'Y'
2077 UPDATE AHL_UC_DETAILS_FOR_HGRID
2078 SET search_flag = 'Y'
2079 WHERE curr_instance_id = (SELECT csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS WHERE unit_config_header_id = l_unit_header_id);
2080 ELSE
2081 IF l_uc_node_id_tbl.FIRST IS NOT NULL THEN
2082 FOR j IN l_uc_node_id_tbl.FIRST..l_uc_node_id_tbl.LAST LOOP
2083 l_current_unit_node_id := l_uc_node_id_tbl(j);
2084 UPDATE AHL_UC_DETAILS_FOR_HGRID
2085 SET search_flag = 'Y'
2086 WHERE unit_node_id = l_current_unit_node_id;
2087 END LOOP;
2088 END IF;
2089 END IF; --end of if l_uc_node_id_tbl
2090 END IF; --end of if l_count
2091
2092 x_hGrid_node_path := l_hGrid_node_path;
2093 END IF;
2094
2095 EXCEPTION
2096 WHEN FND_API.G_EXC_ERROR THEN
2097 x_return_status := FND_API.G_RET_STS_ERROR ;
2098 FND_MSG_PUB.count_and_get(
2099 p_encoded => FND_API.G_FALSE,
2100 p_count => x_msg_count,
2101 p_data => x_msg_data);
2102
2103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2105 FND_MSG_PUB.count_and_get(
2106 p_encoded => FND_API.G_FALSE,
2107 p_count => x_msg_count,
2108 p_data => x_msg_data);
2109
2110 WHEN OTHERS THEN
2111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2112 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2113 THEN
2114 FND_MSG_PUB.add_exc_msg(
2115 p_pkg_name => G_PKG_NAME,
2116 p_procedure_name => l_api_name,
2117 p_error_text => SUBSTRB(SQLERRM,1,240));
2118 END IF;
2119 FND_MSG_PUB.count_and_get(
2120 p_encoded => FND_API.G_FALSE,
2121 p_count => x_msg_count,
2122 p_data => x_msg_data);
2123 END Filter_records_uc_details;
2124 ------------------------------------------------------------------------------------
2125
2126 ------------------------------------------------------------------------------------
2127 PROCEDURE PROCESS_PART_CHANGES(
2128 P_API_VERSION IN NUMBER DEFAULT 1.0,
2129 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2130 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2131 P_VALIDATION_LEVEL IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
2132 P_MODULE_TYPE IN VARCHAR2 DEFAULT NULL,
2133 P_DEFAULT IN VARCHAR2 DEFAULT FND_API.G_TRUE,
2134 P_X_PARTS_REC_TBL IN OUT NOCOPY PART_CHANGE_TBL_TYPE,
2135 P_MR_ASSO_TBL IN AHL_PRD_NONROUTINE_PVT.MR_ASSOCIATION_TBL_TYPE,
2136 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2137 X_MSG_COUNT OUT NOCOPY NUMBER,
2138 X_MSG_DATA OUT NOCOPY VARCHAR2,
2139 X_ERROR_MSG_TBL OUT NOCOPY ERROR_MSG_TBL_TYPE ) IS
2140
2141 L_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_PART_CHANGES';
2142 L_API_VERSION CONSTANT NUMBER := 1.0;
2143 L_INSTANCE_TBL INSTANCE_TBL_TYPE;
2144 L_ERR_INSTANCE_TBL INSTANCE_TBL_TYPE;
2145 l_msg_index_out NUMBER;
2146 l_msg_count NUMBER;
2147 l_return_status VARCHAR2(1);
2148 l_msg_data VARCHAR2(2000);
2149 l_error_msg_index NUMBER;
2150
2151 l_WARNING_MSG_TBL AHL_UC_VALIDATION_PUB.ERROR_TBL_TYPE;
2152
2153 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2154
2155 CURSOR item_info_csr(p_instance_number IN VARCHAR2) IS
2156 select MTL.concatenated_segments,csi.serial_number from csi_item_instances csi, mtl_system_items_kfv MTL
2157 where csi.instance_number like p_instance_number
2158 and csi.inventory_item_id = MTL.inventory_item_id
2159 and CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID;
2160
2161 l_item VARCHAR2(240);
2162 l_serial VARCHAR2(30);
2163
2164 BEGIN
2165 -- Standard start of API savepoint
2166 SAVEPOINT PRD_BLK_PARTS_CHNG_PVT;
2167 IF (l_log_procedure >= l_log_current_level) THEN
2168 fnd_log.string(l_log_procedure, l_debug_key,
2169 ' Entering procedure');
2170 END IF;
2171
2172 -- Standard call to check for call compatibility
2173 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION,
2174 P_API_VERSION,
2175 L_API_NAME,
2176 G_PKG_NAME) THEN
2177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2178 END IF;
2179
2180 -- Initialize message list if p_init_msg_list is set to TRUE
2181 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
2182 FND_MSG_PUB.INITIALIZE;
2183 END IF;
2184
2185 -- Initialize API return status to success
2186 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2187 l_error_msg_index := 0;
2188 IF(P_X_PARTS_REC_TBL IS NULL OR P_X_PARTS_REC_TBL.COUNT < 1)THEN
2189 RETURN;
2190 End if;
2191
2192 IF (l_log_statement >= l_log_current_level) THEN
2193 fnd_log.string(l_log_statement, l_debug_key,
2194 ' P_X_PARTS_REC_TBL.COUNT = ' || P_X_PARTS_REC_TBL.COUNT);
2195 FOR i IN P_X_PARTS_REC_TBL.FIRST..P_X_PARTS_REC_TBL.LAST LOOP
2196 fnd_log.string(l_log_statement, l_debug_key,
2197 ' P_X_PARTS_REC_TBL.(' || i || ').WORKORDER_ID' || ': ' || P_X_PARTS_REC_TBL(i).WORKORDER_ID);
2198 fnd_log.string(l_log_statement, l_debug_key,
2199 ' P_X_PARTS_REC_TBL.(' || i || ').OPERATION_SEQUENCE_NUM' || ': ' || P_X_PARTS_REC_TBL(i).OPERATION_SEQUENCE_NUM);
2200 fnd_log.string(l_log_statement, l_debug_key,
2201 ' P_X_PARTS_REC_TBL.(' || i || ').WORKORDER_OPERATION_ID' || ': ' || P_X_PARTS_REC_TBL(i).WORKORDER_OPERATION_ID);
2202 fnd_log.string(l_log_statement, l_debug_key,
2203 ' P_X_PARTS_REC_TBL.(' || i || ').OPERATION_TYPE' || ': ' || P_X_PARTS_REC_TBL(i).OPERATION_TYPE);
2204 fnd_log.string(l_log_statement, l_debug_key,
2205 ' P_X_PARTS_REC_TBL.(' || i || ').UNIT_CONFIG_HEADER_ID' || ': ' || P_X_PARTS_REC_TBL(i).UNIT_CONFIG_HEADER_ID);
2206 fnd_log.string(l_log_statement, l_debug_key,
2207 ' P_X_PARTS_REC_TBL.(' || i || ').MC_RELATIONSHIP_ID' || ': ' || P_X_PARTS_REC_TBL(i).MC_RELATIONSHIP_ID);
2208 fnd_log.string(l_log_statement, l_debug_key,
2209 ' P_X_PARTS_REC_TBL.(' || i || ').CSI_II_RELATIONSHIP_ID' || ': ' || P_X_PARTS_REC_TBL(i).CSI_II_RELATIONSHIP_ID);
2210 fnd_log.string(l_log_statement, l_debug_key,
2211 ' P_X_PARTS_REC_TBL.(' || i || ').CSI_II_OBJECT_VERSION_NUM' || ': ' || P_X_PARTS_REC_TBL(i).CSI_II_OBJECT_VERSION_NUM);
2212 fnd_log.string(l_log_statement, l_debug_key,
2213 ' P_X_PARTS_REC_TBL.(' || i || ').PARENT_INSTALLED_INSTANCE_ID' || ': ' || P_X_PARTS_REC_TBL(i).PARENT_INSTALLED_INSTANCE_ID);
2214 fnd_log.string(l_log_statement, l_debug_key,
2215 ' P_X_PARTS_REC_TBL.(' || i || ').PARENT_INSTALLED_INSTANCE_NUM' || ': ' || P_X_PARTS_REC_TBL(i).PARENT_INSTALLED_INSTANCE_NUM);
2216 fnd_log.string(l_log_statement, l_debug_key,
2217 ' P_X_PARTS_REC_TBL.(' || i || ').INSTALLED_INSTANCE_ID' || ': ' || P_X_PARTS_REC_TBL(i).INSTALLED_INSTANCE_ID);
2218 fnd_log.string(l_log_statement, l_debug_key,
2219 ' P_X_PARTS_REC_TBL.(' || i || ').INSTALLED_INSTANCE_NUM' || ': ' || P_X_PARTS_REC_TBL(i).INSTALLED_INSTANCE_NUM);
2220 fnd_log.string(l_log_statement, l_debug_key,
2221 ' P_X_PARTS_REC_TBL.(' || i || ').INSTALLED_INSTANCE_OBJ_VER_NUM' || ': ' || P_X_PARTS_REC_TBL(i).INSTALLED_INSTANCE_OBJ_VER_NUM);
2222 fnd_log.string(l_log_statement, l_debug_key,
2223 ' P_X_PARTS_REC_TBL.(' || i || ').INSTALLED_QUANTITY' || ': ' || P_X_PARTS_REC_TBL(i).INSTALLED_QUANTITY);
2224 fnd_log.string(l_log_statement, l_debug_key,
2225 ' P_X_PARTS_REC_TBL.(' || i || ').INSTALLATION_DATE' || ': ' || P_X_PARTS_REC_TBL(i).INSTALLATION_DATE);
2226 fnd_log.string(l_log_statement, l_debug_key,
2227 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVED_INSTANCE_ID' || ': ' || P_X_PARTS_REC_TBL(i).REMOVED_INSTANCE_ID);
2228 fnd_log.string(l_log_statement, l_debug_key,
2229 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVED_INSTANCE_NUM' || ': ' || P_X_PARTS_REC_TBL(i).REMOVED_INSTANCE_NUM);
2230 fnd_log.string(l_log_statement, l_debug_key,
2231 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVED_INSTANCE_OBJ_VER_NUM' || ': ' || P_X_PARTS_REC_TBL(i).REMOVED_INSTANCE_OBJ_VER_NUM);
2232 fnd_log.string(l_log_statement, l_debug_key,
2233 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVED_QUANTITY' || ': ' || P_X_PARTS_REC_TBL(i).REMOVED_QUANTITY);
2234 fnd_log.string(l_log_statement, l_debug_key,
2235 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVAL_CODE' || ': ' || P_X_PARTS_REC_TBL(i).REMOVAL_CODE);
2236 fnd_log.string(l_log_statement, l_debug_key,
2237 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVAL_MEANING' || ': ' || P_X_PARTS_REC_TBL(i).REMOVAL_MEANING);
2238 fnd_log.string(l_log_statement, l_debug_key,
2239 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVAL_REASON_ID' || ': ' || P_X_PARTS_REC_TBL(i).REMOVAL_REASON_ID);
2240 fnd_log.string(l_log_statement, l_debug_key,
2241 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVAL_REASON_NAME' || ': ' || P_X_PARTS_REC_TBL(i).REMOVAL_REASON_NAME);
2242 fnd_log.string(l_log_statement, l_debug_key,
2243 ' P_X_PARTS_REC_TBL.(' || i || ').REMOVAL_DATE' || ': ' || P_X_PARTS_REC_TBL(i).REMOVAL_DATE);
2244 fnd_log.string(l_log_statement, l_debug_key,
2245 ' P_X_PARTS_REC_TBL.(' || i || ').CONDITION_ID' || ': ' || P_X_PARTS_REC_TBL(i).CONDITION_ID);
2246 fnd_log.string(l_log_statement, l_debug_key,
2247 ' P_X_PARTS_REC_TBL.(' || i || ').CONDITION' || ': ' || P_X_PARTS_REC_TBL(i).CONDITION);
2248 fnd_log.string(l_log_statement, l_debug_key,
2249 ' P_X_PARTS_REC_TBL.(' || i || ').SEVERITY_ID' || ': ' || P_X_PARTS_REC_TBL(i).SEVERITY_ID);
2250 fnd_log.string(l_log_statement, l_debug_key,
2251 ' P_X_PARTS_REC_TBL.(' || i || ').SEVERITY_NAME' || ': ' || P_X_PARTS_REC_TBL(i).SEVERITY_NAME);
2252 fnd_log.string(l_log_statement, l_debug_key,
2253 ' P_X_PARTS_REC_TBL.(' || i || ').PROBLEM_CODE' || ': ' || P_X_PARTS_REC_TBL(i).PROBLEM_CODE);
2254 fnd_log.string(l_log_statement, l_debug_key,
2255 ' P_X_PARTS_REC_TBL.(' || i || ').PROBLEM_MEANING' || ': ' || P_X_PARTS_REC_TBL(i).PROBLEM_MEANING);
2256 fnd_log.string(l_log_statement, l_debug_key,
2257 ' P_X_PARTS_REC_TBL.(' || i || ').RESOLUTION_CODE' || ': ' || P_X_PARTS_REC_TBL(i).RESOLUTION_CODE);
2258 fnd_log.string(l_log_statement, l_debug_key,
2259 ' P_X_PARTS_REC_TBL.(' || i || ').RESOLUTION_MEANING' || ': ' || P_X_PARTS_REC_TBL(i).RESOLUTION_MEANING);
2260 fnd_log.string(l_log_statement, l_debug_key,
2261 ' P_X_PARTS_REC_TBL.(' || i || ').SUMMARY' || ': ' || P_X_PARTS_REC_TBL(i).SUMMARY);
2262 fnd_log.string(l_log_statement, l_debug_key,
2263 ' P_X_PARTS_REC_TBL.(' || i || ').ESTIMATED_DURATION' || ': ' || P_X_PARTS_REC_TBL(i).ESTIMATED_DURATION);
2264 fnd_log.string(l_log_statement, l_debug_key,
2265 ' P_X_PARTS_REC_TBL.(' || i || ').CREATE_WORK_ORDER_OPTION' || ': ' || P_X_PARTS_REC_TBL(i).CREATE_WORK_ORDER_OPTION);
2266 fnd_log.string(l_log_statement, l_debug_key,
2267 ' P_X_PARTS_REC_TBL.(' || i || ').CREATE_NR_FOR_PARENT' || ': ' || P_X_PARTS_REC_TBL(i).CREATE_NR_FOR_PARENT);
2268 fnd_log.string(l_log_statement, l_debug_key,
2269 ' P_X_PARTS_REC_TBL.(' || i || ').SERVICE_TYPE_CODE' || ': ' || P_X_PARTS_REC_TBL(i).SERVICE_TYPE_CODE);
2270 fnd_log.string(l_log_statement, l_debug_key,
2271 ' P_X_PARTS_REC_TBL.(' || i || ').SERVICE_TYPE' || ': ' || P_X_PARTS_REC_TBL(i).SERVICE_TYPE);
2272 fnd_log.string(l_log_statement, l_debug_key,
2273 ' P_X_PARTS_REC_TBL.(' || i || ').DISPOSITION_ID' || ': ' || P_X_PARTS_REC_TBL(i).DISPOSITION_ID);
2274 fnd_log.string(l_log_statement, l_debug_key,
2275 ' P_X_PARTS_REC_TBL.(' || i || ').DISP_OBJECT_VERSION_NUMBER' || ': ' || P_X_PARTS_REC_TBL(i).DISP_OBJECT_VERSION_NUMBER);
2276 fnd_log.string(l_log_statement, l_debug_key,
2277 ' P_X_PARTS_REC_TBL.(' || i || ').DISP_OPERATION_FLAG' || ': ' || P_X_PARTS_REC_TBL(i).DISP_OPERATION_FLAG);
2278 fnd_log.string(l_log_statement, l_debug_key,
2279 ' P_X_PARTS_REC_TBL.(' || i || ').IMMEDIATE_DISPOSITION_CODE' || ': ' || P_X_PARTS_REC_TBL(i).IMMEDIATE_DISPOSITION_CODE);
2280 fnd_log.string(l_log_statement, l_debug_key,
2281 ' P_X_PARTS_REC_TBL.(' || i || ').IMMEDIATE_DISPOSITION' || ': ' || P_X_PARTS_REC_TBL(i).IMMEDIATE_DISPOSITION);
2282 fnd_log.string(l_log_statement, l_debug_key,
2283 ' P_X_PARTS_REC_TBL.(' || i || ').SECONDARY_DISPOSITION_CODE' || ': ' || P_X_PARTS_REC_TBL(i).SECONDARY_DISPOSITION_CODE);
2284 fnd_log.string(l_log_statement, l_debug_key,
2285 ' P_X_PARTS_REC_TBL.(' || i || ').SECONDARY_DISPOSITION' || ': ' || P_X_PARTS_REC_TBL(i).SECONDARY_DISPOSITION);
2286 fnd_log.string(l_log_statement, l_debug_key,
2287 ' P_X_PARTS_REC_TBL.(' || i || ').DISP_COMMENTS' || ': ' || P_X_PARTS_REC_TBL(i).DISP_COMMENTS);
2288 fnd_log.string(l_log_statement, l_debug_key,
2289 ' P_X_PARTS_REC_TBL.(' || i || ').COLLECTION_ID' || ': ' || P_X_PARTS_REC_TBL(i).COLLECTION_ID);
2290 fnd_log.string(l_log_statement, l_debug_key,
2291 ' P_X_PARTS_REC_TBL.(' || i || ').PART_CHANGE_TXN_ID' || ': ' || P_X_PARTS_REC_TBL(i).PART_CHANGE_TXN_ID);
2292 fnd_log.string(l_log_statement, l_debug_key,
2293 ' P_X_PARTS_REC_TBL.(' || i || ').PATH_POSITION_ID' || ': ' || P_X_PARTS_REC_TBL(i).PATH_POSITION_ID);
2294 END LOOP;
2295 END IF;
2296
2297 for j IN P_X_PARTS_REC_TBL.FIRST..P_X_PARTS_REC_TBL.LAST LOOP
2298 IF (P_X_PARTS_REC_TBL(j).OPERATION_TYPE = 'C' AND
2299 (P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM IS NULL OR
2300 P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM IS NULL)) THEN
2301 FND_MESSAGE.SET_NAME('AHL', 'AHL_PRD_BLK_PC_INST_ERR_INPUT');
2302 FND_MESSAGE.SET_TOKEN('OPERATION_SEQUENCE_NUM',P_X_PARTS_REC_TBL(j).OPERATION_SEQUENCE_NUM,false);
2303 IF(P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM IS NOT NULL)THEN
2304 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM);
2305 FETCH item_info_csr INTO l_item,l_serial;
2306 CLOSE item_info_csr;
2307 ELSE
2308 l_item := '';
2309 l_serial := '';
2310 END IF;
2311 FND_MESSAGE.SET_TOKEN('INSTALLED_PART',l_item,false);
2312 FND_MESSAGE.SET_TOKEN('INSTALLED_SERIAL',l_serial,false);
2313 IF(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM IS NOT NULL)THEN
2314 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM);
2315 FETCH item_info_csr INTO l_item,l_serial;
2316 CLOSE item_info_csr;
2317 ELSE
2318 l_item := '';
2319 l_serial := '';
2320 END IF;
2321 FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_PART',l_item,false);
2322 FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_SERIAL',l_serial,false);
2323 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE_LEVEL := 'E' ;
2324 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE := FND_MESSAGE.get;
2325 l_error_msg_index := l_error_msg_index + 1;
2326 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2327 ELSIF(P_X_PARTS_REC_TBL(j).OPERATION_TYPE = 'D' AND
2328 (P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM IS NULL OR P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM IS NULL))THEN
2329 FND_MESSAGE.SET_NAME('AHL', 'AHL_PRD_BLK_PC_REM_ERR_INPUT');
2330 FND_MESSAGE.SET_TOKEN('OPERATION_SEQUENCE_NUM',P_X_PARTS_REC_TBL(j).OPERATION_SEQUENCE_NUM,false);
2331 IF(P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM IS NOT NULL)THEN
2332 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM);
2333 FETCH item_info_csr INTO l_item,l_serial;
2334 CLOSE item_info_csr;
2335 ELSE
2336 l_item := '';
2337 l_serial := '';
2338 END IF;
2339 FND_MESSAGE.SET_TOKEN('REMOVED_PART',l_item,false);
2340 FND_MESSAGE.SET_TOKEN('REMOVED_SERIAL',l_serial,false);
2341 IF(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM IS NOT NULL)THEN
2342 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM);
2343 FETCH item_info_csr INTO l_item,l_serial;
2344 CLOSE item_info_csr;
2345 ELSE
2346 l_item := '';
2347 l_serial := '';
2348 END IF;
2349 FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_PART',l_item,false);
2350 FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_SERIAL',l_serial,false);
2351 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE_LEVEL := 'E' ;
2352 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE := FND_MESSAGE.get;
2353 l_error_msg_index := l_error_msg_index + 1;
2354 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2355 END IF;
2356 END LOOP;
2357
2358 IF ( X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
2359 -- Initialize message list if p_init_msg_list is set to TRUE
2360 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
2361 FND_MSG_PUB.INITIALIZE;
2362 END IF;
2363 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_BLK_PC_ERRORS');
2364 FND_MSG_PUB.ADD;
2365 RAISE FND_API.G_EXC_ERROR;
2366 END IF;
2367
2368 GET_ORDERED_PCRECS(
2369 P_PARTS_REC_TBL => P_X_PARTS_REC_TBL,
2370 X_INSTANCE_TBL => L_INSTANCE_TBL);
2371
2372 IF (l_log_statement >= l_log_current_level) THEN
2373 fnd_log.string(l_log_statement, l_debug_key,
2374 ' L_INSTANCE_TBL.COUNT = ' || L_INSTANCE_TBL.COUNT);
2375 END IF;
2376
2377 IF(L_INSTANCE_TBL IS NOT NULL AND L_INSTANCE_TBL.COUNT > 0)THEN
2378 for i IN L_INSTANCE_TBL.FIRST..L_INSTANCE_TBL.LAST LOOP
2379 IF (l_log_statement >= l_log_current_level) THEN
2380 fnd_log.string(l_log_statement, l_debug_key,'cp 1 : ' || i);
2381 END IF;
2382 for j IN P_X_PARTS_REC_TBL.FIRST..P_X_PARTS_REC_TBL.LAST LOOP
2383 IF (l_log_statement >= l_log_current_level) THEN
2384 fnd_log.string(l_log_statement, l_debug_key,'cp 2 : ' || j);
2385 END IF;
2386 IF (l_log_statement >= l_log_current_level) THEN
2387 fnd_log.string(l_log_statement, l_debug_key,
2388 ' P_X_PARTS_REC_TBL.(' || j || ').OPERATION_TYPE' || ': ' || P_X_PARTS_REC_TBL(j).OPERATION_TYPE
2389 || ' ** .PARENT_INSTALLED_INSTANCE_NUM : '|| NVL(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM,P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_ID)
2390 || ' ** .INSTALLED_INSTANCE_NUM : '|| P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM
2391 || ' ** .REMOVED_INSTANCE_NUM : '|| P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM);
2392 END IF;
2393 IF((NVL(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM,P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_ID) = L_INSTANCE_TBL(i).PARENT_INSTANCE_NUMBER AND
2394 P_X_PARTS_REC_TBL(j).OPERATION_TYPE = L_INSTANCE_TBL(i).OPERATION_TYPE)
2395 AND ((P_X_PARTS_REC_TBL(j).OPERATION_TYPE = 'C' AND
2396 P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM = L_INSTANCE_TBL(i).INSTANCE_NUMBER) OR
2397 (P_X_PARTS_REC_TBL(j).OPERATION_TYPE = 'D' AND
2398 P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM = L_INSTANCE_TBL(i).INSTANCE_NUMBER)
2399 )) THEN
2400 IF (l_log_statement >= l_log_current_level) THEN
2401 fnd_log.string(l_log_statement, l_debug_key,
2402 ' Invoking parts change for P_X_PARTS_REC_TBL.(' || j || ').OPERATION_TYPE' || ': ' || P_X_PARTS_REC_TBL(j).OPERATION_TYPE
2403 || ' ** .PARENT_INSTALLED_INSTANCE_NUM : '|| NVL(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM,P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_ID)
2404 || ' ** .INSTALLED_INSTANCE_NUM : '|| P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM
2405 || ' ** .REMOVED_INSTANCE_NUM : '|| P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM);
2406 END IF;
2407 INVOKE_PART_CHANGE(
2408 P_X_PART_CHANGE_REC => P_X_PARTS_REC_TBL(j),
2409 P_MR_ASSO_TBL => P_MR_ASSO_TBL,
2410 X_RETURN_STATUS => l_RETURN_STATUS,
2411 X_MSG_COUNT => l_MSG_COUNT,
2412 X_MSG_DATA => l_MSG_DATA,
2413 X_WARNING_MSG_TBL => l_WARNING_MSG_TBL
2414 );
2415 IF (l_log_statement >= l_log_current_level) THEN
2416 fnd_log.string(l_log_statement, l_debug_key,
2417 ' parts change API Return status:' || l_RETURN_STATUS ||
2418 ': l_MSG_COUNT:' || l_MSG_COUNT || ': l_MSG_DATA:' || l_MSG_DATA);
2419 END IF;
2420 IF ( l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
2421 IF (l_log_error >= l_log_current_level) THEN
2422 fnd_log.string(l_log_error, l_debug_key,
2423 ' parts change API Return returned error status');
2424 END IF;
2425 X_RETURN_STATUS := l_RETURN_STATUS;
2426 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE_LEVEL := 'E' ;
2427 IF(P_X_PARTS_REC_TBL(j).OPERATION_TYPE = 'C')THEN
2428 FND_MESSAGE.SET_NAME('AHL', 'AHL_PRD_BLK_PC_INST_ERR');
2429 --FND_MESSAGE.SET_TOKEN('INSTALLED_INSTANCE_NUM',P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM,false);
2430 IF(P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM IS NOT NULL)THEN
2431 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).INSTALLED_INSTANCE_NUM);
2432 FETCH item_info_csr INTO l_item,l_serial;
2433 CLOSE item_info_csr;
2434 ELSE
2435 l_item := '';
2436 l_serial := '';
2437 END IF;
2438 FND_MESSAGE.SET_TOKEN('INSTALLED_PART',l_item,false);
2439 FND_MESSAGE.SET_TOKEN('INSTALLED_SERIAL',l_serial,false);
2440 ELSE
2441 FND_MESSAGE.SET_NAME('AHL', 'AHL_PRD_BLK_PC_REM_ERR');
2442 --FND_MESSAGE.SET_TOKEN('REMOVED_INSTANCE_NUM',P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM,false);
2443 IF(P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM IS NOT NULL)THEN
2444 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).REMOVED_INSTANCE_NUM);
2445 FETCH item_info_csr INTO l_item,l_serial;
2446 CLOSE item_info_csr;
2447 ELSE
2448 l_item := '';
2449 l_serial := '';
2450 END IF;
2451 FND_MESSAGE.SET_TOKEN('REMOVED_PART',l_item,false);
2452 FND_MESSAGE.SET_TOKEN('REMOVED_SERIAL',l_serial,false);
2453 END IF;
2454 FND_MESSAGE.SET_TOKEN('OPERATION_SEQUENCE_NUM',P_X_PARTS_REC_TBL(j).OPERATION_SEQUENCE_NUM,false);
2455 --FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_INSTANCE_NUM',P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM,false);
2456 IF(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM IS NOT NULL)THEN
2457 OPEN item_info_csr(P_X_PARTS_REC_TBL(j).PARENT_INSTALLED_INSTANCE_NUM);
2458 FETCH item_info_csr INTO l_item,l_serial;
2459 CLOSE item_info_csr;
2460 ELSE
2461 l_item := '';
2462 l_serial := '';
2463 END IF;
2464 FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_PART',l_item,false);
2465 FND_MESSAGE.SET_TOKEN('PARENT_INSTALLED_SERIAL',l_serial,false);
2466 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE := FND_MESSAGE.get;
2467 IF(l_MSG_COUNT = 1)THEN
2468 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE := X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE || l_MSG_DATA ;
2469 ELSE
2470 FOR i IN 1..l_MSG_COUNT LOOP
2471 FND_MSG_PUB.get (
2472 p_msg_index => i,
2473 p_encoded => FND_API.G_FALSE,
2474 p_data => l_MSG_DATA,
2475 p_msg_index_out => l_msg_index_out );
2476 X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE := X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE || l_MSG_DATA ;
2477 END LOOP;
2478 END IF;
2479 IF (l_log_error >= l_log_current_level) THEN
2480 fnd_log.string(l_log_error, l_debug_key,
2481 'X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE : ' || X_ERROR_MSG_TBL(l_error_msg_index).MESSAGE);
2482 END IF;
2483 l_error_msg_index := l_error_msg_index + 1;
2484 END IF;
2485 EXIT;
2486 END IF;
2487 END LOOP;
2488 END LOOP;
2489 END IF;
2490
2491 IF ( X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
2492 IF(X_ERROR_MSG_TBL IS NOT NULL AND X_ERROR_MSG_TBL.COUNT>0) THEN
2493 FOR i IN X_ERROR_MSG_TBL.FIRST..X_ERROR_MSG_TBL.LAST LOOP
2494 IF (l_log_error >= l_log_current_level) THEN
2495 fnd_log.string(l_log_error, l_debug_key,
2496 'Error Message[' || i || ']:' || X_ERROR_MSG_TBL(i).MESSAGE );
2497 END IF;
2498 END LOOP;
2499 END IF;
2500 -- Initialize message list if p_init_msg_list is set to TRUE
2501 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
2502 FND_MSG_PUB.INITIALIZE;
2503 END IF;
2504 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_BLK_PC_ERRORS');
2505 FND_MSG_PUB.ADD;
2506 RAISE FND_API.G_EXC_ERROR;
2507 END IF;
2508 -- Standard check of p_commit
2509 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
2510 COMMIT WORK;
2511 END IF;
2512 -- Standard call to get message count and if count is 1, get message
2513 FND_MSG_PUB.COUNT_AND_GET
2514 ( P_COUNT => X_MSG_COUNT,
2515 P_DATA => X_MSG_DATA,
2516 P_ENCODED => FND_API.G_FALSE);
2517
2518 IF (l_log_procedure >= l_log_current_level) THEN
2519 fnd_log.string(l_log_procedure, l_debug_key,
2520 ' Exiting procedure');
2521 END IF;
2522 EXCEPTION
2523 WHEN FND_API.G_EXC_ERROR THEN
2524 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2525 ROLLBACK TO PRD_BLK_PARTS_CHNG_PVT;
2526 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT,
2527 P_DATA => X_MSG_DATA,
2528 P_ENCODED => FND_API.G_FALSE);
2529
2530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2531 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2532 ROLLBACK TO PRD_BLK_PARTS_CHNG_PVT;
2533 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT,
2534 P_DATA => X_MSG_DATA,
2535 P_ENCODED => FND_API.G_FALSE);
2536
2537 WHEN OTHERS THEN
2538 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2539 ROLLBACK TO PRD_BLK_PARTS_CHNG_PVT;
2540
2541 FND_MSG_PUB.ADD_EXC_MSG(P_PKG_NAME => 'AHL_PRD_BLK_PARTS_CHANGE_PVT',
2542 P_PROCEDURE_NAME => 'PROCESS_PART_CHANGES',
2543 P_ERROR_TEXT => SQLERRM);
2544 --END IF;
2545 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT,
2546 P_DATA => X_MSG_DATA,
2547 P_ENCODED => FND_API.G_FALSE);
2548
2549
2550 END PROCESS_PART_CHANGES;
2551 ------------------------------------------------------------------------------------
2552
2553 ------------------------------------------------------------------------------------
2554 PROCEDURE GET_ORDERED_PCRECS(
2555 P_PARTS_REC_TBL IN PART_CHANGE_TBL_TYPE,
2556 X_INSTANCE_TBL OUT NOCOPY INSTANCE_TBL_TYPE) IS
2557
2558 CURSOR removed_instances_csr IS
2559 select object_id, subject_id, root_object_id
2560 from AHL_CONFIG_COMPONENTS A1 where root_object_id = -1
2561 start with A1.object_id IN (select A2.object_id from AHL_CONFIG_COMPONENTS A2 where not exists(
2562 select 'x' from AHL_CONFIG_COMPONENTS A3 where A3.subject_id = A2.object_id and root_object_id = -1)
2563 )
2564 AND root_object_id = -1
2565 connect by prior subject_id = object_id
2566 order by level desc;
2567
2568 CURSOR installed_instances_csr IS
2569 select object_id, subject_id, root_object_id
2570 from AHL_CONFIG_COMPONENTS A1 where root_object_id = -2
2571 start with A1.object_id IN (select A2.object_id from AHL_CONFIG_COMPONENTS A2 where not exists(
2572 select 'x' from AHL_CONFIG_COMPONENTS A3 where A3.subject_id = A2.object_id and root_object_id = -2)
2573 )
2574 AND root_object_id = -2
2575 connect by prior subject_id = object_id
2576 order by level asc;
2577
2578 l_has_removed_instances BOOLEAN;
2579 l_has_installed_instances BOOLEAN;
2580
2581 j integer;
2582
2583 BEGIN
2584 l_has_removed_instances := FALSE;
2585 l_has_installed_instances := FALSE;
2586 DELETE AHL_CONFIG_COMPONENTS;
2587 FOR i IN P_PARTS_REC_TBL.FIRST..P_PARTS_REC_TBL.LAST LOOP
2588 IF(P_PARTS_REC_TBL(i).OPERATION_TYPE = 'D')THEN
2589 l_has_removed_instances := TRUE;
2590 INSERT INTO ahl_config_components (object_id, subject_id, root_object_id)
2591 VALUES (P_PARTS_REC_TBL(i).PARENT_INSTALLED_INSTANCE_NUM,P_PARTS_REC_TBL(i).REMOVED_INSTANCE_NUM,-1);
2592 ELSIF(P_PARTS_REC_TBL(i).OPERATION_TYPE = 'C')THEN
2593 l_has_installed_instances := TRUE;
2594 INSERT INTO ahl_config_components (object_id, subject_id, root_object_id)
2595 VALUES (P_PARTS_REC_TBL(i).PARENT_INSTALLED_INSTANCE_NUM,P_PARTS_REC_TBL(i).INSTALLED_INSTANCE_NUM,-2);
2596 END IF;
2597 END LOOP;
2598 j :=0;
2599 IF(l_has_removed_instances) THEN
2600 FOR removed_instances_rec IN removed_instances_csr LOOP
2601 X_INSTANCE_TBL(j).INSTANCE_NUMBER := removed_instances_rec.subject_id;
2602 X_INSTANCE_TBL(j).PARENT_INSTANCE_NUMBER := removed_instances_rec.object_id;
2603 X_INSTANCE_TBL(j).OPERATION_TYPE := 'D';
2604 j := j+1;
2605 END LOOP;
2606 END IF;
2607 IF(l_has_installed_instances) THEN
2608 FOR installed_instances_rec IN installed_instances_csr LOOP
2609 X_INSTANCE_TBL(j).INSTANCE_NUMBER := installed_instances_rec.subject_id;
2610 X_INSTANCE_TBL(j).PARENT_INSTANCE_NUMBER := installed_instances_rec.object_id;
2611 X_INSTANCE_TBL(j).OPERATION_TYPE := 'C';
2612 j := j+1;
2613 END LOOP;
2614 END IF;
2615
2616 END GET_ORDERED_PCRECS;
2617 ------------------------------------------------------------------------------------
2618
2619 ------------------------------------------------------------------------------------
2620 PROCEDURE INVOKE_PART_CHANGE(
2621 P_X_PART_CHANGE_REC IN OUT NOCOPY PART_CHANGE_REC_TYPE,
2622 P_MR_ASSO_TBL IN AHL_PRD_NONROUTINE_PVT.MR_ASSOCIATION_TBL_TYPE,
2623 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2624 X_MSG_COUNT OUT NOCOPY NUMBER,
2625 X_MSG_DATA OUT NOCOPY VARCHAR2,
2626 X_WARNING_MSG_TBL OUT NOCOPY AHL_UC_VALIDATION_PUB.ERROR_TBL_TYPE
2627 )IS
2628 L_ERROR_CODE VARCHAR2(30);
2629 L_PARTS_REC_TBL AHL_PRD_PARTS_CHANGE_PVT.AHL_PARTS_TBL_TYPE;
2630 L_MR_ASSO_TBL AHL_PRD_NONROUTINE_PVT.MR_ASSOCIATION_TBL_TYPE;
2631 L_DISPOSITION_REC AHL_PRD_DISPOSITION_PVT.DISPOSITION_REC_TYPE;
2632 MR_TABLE_INDEX INTEGER;
2633 L_API_NAME CONSTANT VARCHAR2(30) := 'INVOKE_PART_CHANGE';
2634 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2635
2636
2637 BEGIN
2638 IF (l_log_procedure >= l_log_current_level) THEN
2639 fnd_log.string(l_log_procedure, l_debug_key,
2640 ' Entering procedure');
2641 END IF;
2642 L_PARTS_REC_TBL(0).WORKORDER_ID := P_X_PART_CHANGE_REC.WORKORDER_ID;
2643 L_PARTS_REC_TBL(0).OPERATION_SEQUENCE_NUM := P_X_PART_CHANGE_REC.OPERATION_SEQUENCE_NUM;
2644 L_PARTS_REC_TBL(0).WORKORDER_OPERATION_ID := P_X_PART_CHANGE_REC.WORKORDER_OPERATION_ID;
2645 L_PARTS_REC_TBL(0).UNIT_CONFIG_HEADER_ID := P_X_PART_CHANGE_REC.UNIT_CONFIG_HEADER_ID;
2646 L_PARTS_REC_TBL(0).MC_RELATIONSHIP_ID := P_X_PART_CHANGE_REC.MC_RELATIONSHIP_ID;
2647 L_PARTS_REC_TBL(0).INSTALLED_INSTANCE_ID := P_X_PART_CHANGE_REC.INSTALLED_INSTANCE_ID;
2648 L_PARTS_REC_TBL(0).INSTALLED_INSTANCE_NUM := P_X_PART_CHANGE_REC.INSTALLED_INSTANCE_NUM;
2649 L_PARTS_REC_TBL(0).INSTALLED_QUANTITY := P_X_PART_CHANGE_REC.INSTALLED_QUANTITY;
2650 L_PARTS_REC_TBL(0).INSTALLATION_DATE := P_X_PART_CHANGE_REC.INSTALLATION_DATE;
2651 L_PARTS_REC_TBL(0).PARENT_INSTALLED_INSTANCE_ID := P_X_PART_CHANGE_REC.PARENT_INSTALLED_INSTANCE_ID;
2652 L_PARTS_REC_TBL(0).PARENT_INSTALLED_INSTANCE_NUM := P_X_PART_CHANGE_REC.PARENT_INSTALLED_INSTANCE_NUM;
2653 L_PARTS_REC_TBL(0).REMOVED_INSTANCE_ID := P_X_PART_CHANGE_REC.REMOVED_INSTANCE_ID;
2654 L_PARTS_REC_TBL(0).REMOVED_INSTANCE_NUM := P_X_PART_CHANGE_REC.REMOVED_INSTANCE_NUM;
2655 L_PARTS_REC_TBL(0).REMOVED_QUANTITY := P_X_PART_CHANGE_REC.REMOVED_QUANTITY;
2656 L_PARTS_REC_TBL(0).REMOVAL_CODE := P_X_PART_CHANGE_REC.REMOVAL_CODE;
2657 L_PARTS_REC_TBL(0).REMOVAL_MEANING := P_X_PART_CHANGE_REC.REMOVAL_MEANING;
2658 L_PARTS_REC_TBL(0).REMOVAL_REASON_ID := P_X_PART_CHANGE_REC.REMOVAL_REASON_ID;
2659 L_PARTS_REC_TBL(0).REMOVAL_REASON_NAME := P_X_PART_CHANGE_REC.REMOVAL_REASON_NAME;
2660 L_PARTS_REC_TBL(0).REMOVAL_DATE := P_X_PART_CHANGE_REC.REMOVAL_DATE;
2661 L_PARTS_REC_TBL(0).CSI_II_RELATIONSHIP_ID := P_X_PART_CHANGE_REC.CSI_II_RELATIONSHIP_ID;
2662 L_PARTS_REC_TBL(0).CSI_II_OBJECT_VERSION_NUM := P_X_PART_CHANGE_REC.CSI_II_OBJECT_VERSION_NUM;
2663 L_PARTS_REC_TBL(0).OPERATION_TYPE := P_X_PART_CHANGE_REC.OPERATION_TYPE;
2664 L_PARTS_REC_TBL(0).INSTALLED_INSTANCE_OBJ_VER_NUM := P_X_PART_CHANGE_REC.INSTALLED_INSTANCE_OBJ_VER_NUM;
2665 L_PARTS_REC_TBL(0).REMOVED_INSTANCE_OBJ_VER_NUM := P_X_PART_CHANGE_REC.REMOVED_INSTANCE_OBJ_VER_NUM;
2666
2667 IF (l_log_statement >= l_log_current_level) THEN
2668 fnd_log.string(l_log_statement, l_debug_key,
2669 ' invoking AHL_PRD_PARTS_CHANGE_PVT.PROCESS_PART ');
2670 END IF;
2671 AHL_PRD_PARTS_CHANGE_PVT.PROCESS_PART (
2672 P_API_VERSION => 1.0,
2673 P_INIT_MSG_LIST => FND_API.G_TRUE,
2674 P_COMMIT => FND_API.G_FALSE,
2675 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2676 P_MODULE_TYPE => 'OAF',
2677 P_DEFAULT => FND_API.G_TRUE,
2678 P_X_PARTS_REC_TBL => L_PARTS_REC_TBL,
2679 X_ERROR_CODE => L_ERROR_CODE,
2680 X_RETURN_STATUS => X_RETURN_STATUS,
2681 X_MSG_COUNT => X_MSG_COUNT,
2682 X_MSG_DATA => X_MSG_DATA,
2683 X_WARNING_MSG_TBL => X_WARNING_MSG_TBL );
2684
2685 IF (l_log_statement >= l_log_current_level) THEN
2686 fnd_log.string(l_log_statement, l_debug_key,
2687 ' AHL_PRD_PARTS_CHANGE_PVT.PROCESS_PART return status : ' || X_RETURN_STATUS);
2688 END IF;
2689
2690 IF ( X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
2691 RETURN;
2692 ELSE
2693 IF (l_log_statement >= l_log_current_level) THEN
2694 fnd_log.string(l_log_statement, l_debug_key,
2695 'Initializing PART_CHANGE_TXN_ID');
2696 END IF;
2697 IF(P_X_PART_CHANGE_REC.PART_CHANGE_TXN_ID IS NULL)THEN
2698 P_X_PART_CHANGE_REC.PART_CHANGE_TXN_ID := L_PARTS_REC_TBL(0).PART_CHANGE_TXN_ID;
2699 END IF;
2700 P_X_PART_CHANGE_REC.PATH_POSITION_ID := L_PARTS_REC_TBL(0).PATH_POSITION_ID;
2701 P_X_PART_CHANGE_REC.REMOVED_INSTANCE_ID := L_PARTS_REC_TBL(0).REMOVED_INSTANCE_ID;
2702 END IF;
2703 IF (l_log_statement >= l_log_current_level) THEN
2704 fnd_log.string(l_log_statement, l_debug_key,
2705 'Initializing disposition record');
2706 END IF;
2707 L_DISPOSITION_REC.DISPOSITION_ID := P_X_PART_CHANGE_REC.DISPOSITION_ID;
2708 L_DISPOSITION_REC.OPERATION_FLAG := P_X_PART_CHANGE_REC.DISP_OPERATION_FLAG;
2709 L_DISPOSITION_REC.PART_CHANGE_ID := P_X_PART_CHANGE_REC.PART_CHANGE_TXN_ID;
2710 L_DISPOSITION_REC.OBJECT_VERSION_NUMBER := P_X_PART_CHANGE_REC.DISP_OBJECT_VERSION_NUMBER;
2711 L_DISPOSITION_REC.OPERATION_SEQUENCE := P_X_PART_CHANGE_REC.OPERATION_SEQUENCE_NUM;
2712 L_DISPOSITION_REC.WORKORDER_ID := P_X_PART_CHANGE_REC.WORKORDER_ID;
2713 L_DISPOSITION_REC.PATH_POSITION_ID := P_X_PART_CHANGE_REC.PATH_POSITION_ID;
2714 L_DISPOSITION_REC.IMMEDIATE_DISPOSITION_CODE := P_X_PART_CHANGE_REC.IMMEDIATE_DISPOSITION_CODE;
2715 L_DISPOSITION_REC.IMMEDIATE_DISPOSITION := P_X_PART_CHANGE_REC.IMMEDIATE_DISPOSITION;
2716 L_DISPOSITION_REC.SECONDARY_DISPOSITION_CODE := P_X_PART_CHANGE_REC.SECONDARY_DISPOSITION_CODE;
2717 L_DISPOSITION_REC.SECONDARY_DISPOSITION := P_X_PART_CHANGE_REC.SECONDARY_DISPOSITION;
2718 L_DISPOSITION_REC.COMMENTS := P_X_PART_CHANGE_REC.DISP_COMMENTS;
2719
2720 IF (l_log_statement >= l_log_current_level) THEN
2721 fnd_log.string(l_log_statement, l_debug_key,
2722 'Done Part 1 of Initializing disposition record');
2723 END IF;
2724 IF(P_X_PART_CHANGE_REC.OPERATION_TYPE ='C') THEN
2725 IF(L_DISPOSITION_REC.OPERATION_FLAG = 'C')THEN
2726 L_DISPOSITION_REC.QUANTITY := P_X_PART_CHANGE_REC.INSTALLED_QUANTITY;
2727 END IF;
2728 ELSE
2729 IF(L_DISPOSITION_REC.OPERATION_FLAG = 'C')THEN
2730 L_DISPOSITION_REC.INSTANCE_ID := P_X_PART_CHANGE_REC.REMOVED_INSTANCE_ID;
2731 END IF;
2732 L_DISPOSITION_REC.QUANTITY := P_X_PART_CHANGE_REC.REMOVED_QUANTITY;
2733 L_DISPOSITION_REC.CONDITION_ID := P_X_PART_CHANGE_REC.CONDITION_ID;
2734 L_DISPOSITION_REC.CONDITION_MEANING := P_X_PART_CHANGE_REC.CONDITION;
2735 L_DISPOSITION_REC.COLLECTION_ID := P_X_PART_CHANGE_REC.COLLECTION_ID;
2736 L_DISPOSITION_REC.PROBLEM_MEANING := P_X_PART_CHANGE_REC.PROBLEM_MEANING;
2737 L_DISPOSITION_REC.PROBLEM_CODE := P_X_PART_CHANGE_REC.PROBLEM_CODE;
2738 L_DISPOSITION_REC.RESOLUTION_CODE := P_X_PART_CHANGE_REC.RESOLUTION_CODE;
2739 L_DISPOSITION_REC.RESOLUTION_MEANING := P_X_PART_CHANGE_REC.RESOLUTION_MEANING;
2740 L_DISPOSITION_REC.SUMMARY := P_X_PART_CHANGE_REC.SUMMARY;
2741 L_DISPOSITION_REC.SEVERITY_ID := P_X_PART_CHANGE_REC.SEVERITY_ID;
2742 L_DISPOSITION_REC.SEVERITY_NAME := P_X_PART_CHANGE_REC.SEVERITY_NAME;
2743 L_DISPOSITION_REC.DURATION := P_X_PART_CHANGE_REC.ESTIMATED_DURATION;
2744 L_DISPOSITION_REC.CREATE_WORK_ORDER_OPTION := P_X_PART_CHANGE_REC.CREATE_WORK_ORDER_OPTION;
2745 IF(P_X_PART_CHANGE_REC.CREATE_NR_FOR_PARENT = 'Y') THEN
2746 L_DISPOSITION_REC.PARENT_INSTANCE_ID := P_X_PART_CHANGE_REC.PARENT_INSTALLED_INSTANCE_ID;
2747 END IF;
2748 L_DISPOSITION_REC.SERVICE_TYPE := P_X_PART_CHANGE_REC.SERVICE_TYPE;
2749 L_DISPOSITION_REC.SERVICE_TYPE_CODE := P_X_PART_CHANGE_REC.SERVICE_TYPE_CODE;
2750 IF (l_log_statement >= l_log_current_level) THEN
2751 fnd_log.string(l_log_statement, l_debug_key,
2752 'Done Part 2 of Initializing disposition record');
2753 END IF;
2754 IF(P_MR_ASSO_TBL IS NOT NULL AND P_MR_ASSO_TBL.COUNT > 0) THEN
2755 MR_TABLE_INDEX := 0;
2756 FOR I IN P_MR_ASSO_TBL.FIRST..P_MR_ASSO_TBL.LAST LOOP
2757 IF(P_MR_ASSO_TBL(I).CSI_INSTANCE_ID = P_X_PART_CHANGE_REC.REMOVED_INSTANCE_ID)THEN
2758 L_MR_ASSO_TBL(MR_TABLE_INDEX).MR_HEADER_ID := P_MR_ASSO_TBL(I).MR_HEADER_ID;
2759 L_MR_ASSO_TBL(MR_TABLE_INDEX).MR_TITLE := P_MR_ASSO_TBL(I).MR_TITLE;
2760 L_MR_ASSO_TBL(MR_TABLE_INDEX).MR_VERSION := P_MR_ASSO_TBL(I).MR_VERSION;
2761 L_MR_ASSO_TBL(MR_TABLE_INDEX).CSI_INSTANCE_ID := P_MR_ASSO_TBL(I).CSI_INSTANCE_ID;
2762 L_MR_ASSO_TBL(MR_TABLE_INDEX).CSI_INSTANCE_NUMBER := P_MR_ASSO_TBL(I).CSI_INSTANCE_NUMBER;
2763 L_MR_ASSO_TBL(MR_TABLE_INDEX).SR_TBL_INDEX := 0;
2764 MR_TABLE_INDEX := MR_TABLE_INDEX +1;
2765 END IF;
2766 END LOOP;
2767 END IF;
2768 END IF;
2769 IF (l_log_statement >= l_log_current_level) THEN
2770 fnd_log.string(l_log_statement, l_debug_key,
2771 ' invoking AHL_PRD_DISPOSITION_PVT.PROCESS_DISPOSITION ');
2772 END IF;
2773 AHL_PRD_DISPOSITION_PVT.PROCESS_DISPOSITION(
2774 P_API_VERSION => 1.0,
2775 P_INIT_MSG_LIST => FND_API.G_TRUE,
2776 P_COMMIT => FND_API.G_FALSE,
2777 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2778 P_MODULE_TYPE => 'JSP',
2779 P_X_DISPOSITION_REC => L_DISPOSITION_REC,
2780 P_MR_ASSO_TBL => L_MR_ASSO_TBL,
2781 X_RETURN_STATUS => X_RETURN_STATUS,
2782 X_MSG_COUNT => X_MSG_COUNT,
2783 X_MSG_DATA => X_MSG_DATA);
2784
2785 IF (l_log_procedure >= l_log_current_level) THEN
2786 fnd_log.string(l_log_procedure, l_debug_key,
2787 ' Exiting procedure');
2788 END IF;
2789 END INVOKE_PART_CHANGE;
2790 ------------------------------------------------------------------------------------
2791
2792 END AHL_PRD_BLK_PARTS_CHANGE_PVT;