1 PACKAGE BODY AHL_UC_POS_NECES_PVT AS
2 /* $Header: AHLVNECB.pls 120.2 2007/12/21 13:29:24 sathapli ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UC_POS_NECES_PVT';
5
6
7 ----------------------------------------
8 -- Begin Local Procedures Declaration--
9 ----------------------------------------
10 -------------------------------------
11 -- End Local Procedures Declaration--
12 -------------------------------------
13
14 ------------------------
15 -- Define Procedures --
16 ------------------------
17 --------------------------------------------------------------------------------------------
18 -- Start of Comments --
19 --
20 -- Procedure name : List_Extra_Nodes
21 -- Type : Private
22 -- Function : List all the nodes in the unit configuration which are extra.
23 -- i.e. with no corresponding position reference
24 -- Pre-reqs :
25 --
26 -- Standard IN Parameters :
27 -- p_api_version IN NUMBER Required
28 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
29 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
30 -- Standard OUT Parameters :
31 -- x_return_status OUT VARCHAR2 Required
32 -- x_msg_count OUT NUMBER Required
33 -- x_msg_data OUT VARCHAR2 Required
34 --
35 -- List_Extra_Nodes parameters :
36 -- p_uc_header_id IN NUMBER Required
37 -- The header id of the unit configuration
38 -- p_csi_instance_id IN NUMBER the starting intance id. It's the
39 -- alternative to p_uc_header_id
40 -- x_evaluation_status OUT VARCHAR2
41 -- The flag which indicates whether the unit has extra nodes or not.
42 -- p_x_error_table IN OUT AHL_UC_POS_NECES_PVT.Error_Tbl_Type
43 -- An output table with the list of all the extra nodes.
44 --
45 -- History:
46 -- 05/19/03 SBethi CREATED
47 --
48 -- Version :
49 -- Initial Version 1.0
50 --
51 -- End of Comments.
52 --------------------------------------------------------------------------------------------
53
54 PROCEDURE List_Extra_Nodes(
55 p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
57 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 p_uc_header_id IN NUMBER,
62 p_csi_instance_id IN NUMBER,
63 p_x_error_table IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
64 x_evaluation_status OUT NOCOPY VARCHAR2
65 )
66 IS
67 ----
68 CURSOR get_uc_header_rec_csr(p_uc_header_id IN NUMBER) IS
69 SELECT csi_item_instance_id
70 FROM ahl_unit_config_headers
71 WHERE unit_config_header_id = p_uc_header_id
72 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
73 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
74 ---
75 --To get extra nodes
76 CURSOR get_extra_nodes_csr(p_csi_item_instance_id IN NUMBER) IS
77 SELECT subject_id
78 FROM csi_ii_relationships
79 WHERE position_reference is null
80 START WITH object_id = p_csi_item_instance_id
81 AND relationship_type_code = 'COMPONENT-OF'
82 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
83 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
84 CONNECT BY PRIOR subject_id = object_id
85 AND relationship_type_code = 'COMPONENT-OF'
86 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
87 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
88
89 --To get extra node children nodes
90 CURSOR get_extra_node_child_csr(p_csi_item_instance_id IN NUMBER) IS
91 SELECT subject_id
92 FROM csi_ii_relationships
93 START WITH object_id = p_csi_item_instance_id
94 AND relationship_type_code = 'COMPONENT-OF'
95 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
96 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
97 CONNECT BY PRIOR subject_id = object_id
98 AND relationship_type_code = 'COMPONENT-OF'
99 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
100 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
101 --
102 CURSOR get_node_details_csr (p_csi_instance_id IN NUMBER) IS
103 SELECT M.concatenated_segments, C.serial_number
104 FROM csi_item_instances C,
105 mtl_system_items_kfv M
106 WHERE instance_id = p_csi_instance_id
107 AND M.inventory_item_id = C.inventory_item_id
108 AND M.organization_id = c.inv_master_organization_id;
109 --
110 --Extra nodes can not be top nodes, so no need to check that.
111 CURSOR get_unit_instance_csr (p_csi_instance_id IN NUMBER) IS
112 SELECT object_id
113 FROM csi_ii_relationships
114 WHERE object_id IN
115 ( SELECT csi_item_instance_id
116 FROM ahl_unit_config_headers
117 WHERE trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
118 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
119 START WITH subject_id = p_csi_instance_id
120 AND relationship_type_code = 'COMPONENT-OF'
121 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
122 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
123 CONNECT BY subject_id = PRIOR object_id
124 AND relationship_type_code = 'COMPONENT-OF'
125 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
126 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
127 --
128 --With unit instance id, fetch the unit name.
129 CURSOR get_node_unit_csr (p_csi_instance_id IN NUMBER) IS
130 SELECT uch.name
131 FROM ahl_unit_config_headers uch
132 WHERE uch.csi_item_instance_id=p_csi_instance_id;
133
134 --
135 l_api_version CONSTANT NUMBER := 1.0;
136 l_api_name CONSTANT VARCHAR2(30) := 'List_Extra_Nodes';
137 l_top_instance_id NUMBER;
138 l_csi_id NUMBER;
139 l_unit_csi_id NUMBER;
140 l_unit_name ahl_unit_config_headers.name%TYPE;
141 l_item_number mtl_system_items_kfv.concatenated_segments%TYPE;
142 l_serial_number csi_item_instances.serial_number%TYPE;
143 --
144 BEGIN
145
146 -- Standard call to check for call compatibility
147 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
148 G_PKG_NAME) THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 -- Initialize message list if p_init_msg_list is set to TRUE
153 IF FND_API.To_Boolean(p_init_msg_list) THEN
154 FND_MSG_PUB.Initialize;
155 END IF;
156
157 -- Initialize API return status to success
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159
160 -- Begin Processing
161 x_evaluation_status := FND_API.G_TRUE;
162
163
164 --Call procedure to validate the uc header id
165 IF (p_csi_instance_id IS NOT NULL AND
166 p_uc_header_id IS NULL) THEN
167 l_top_instance_id := p_csi_instance_id;
168 ELSE
169 OPEN get_uc_header_rec_csr(p_uc_header_id);
170 FETCH get_uc_header_rec_csr INTO l_top_instance_id;
171 IF ( get_uc_header_rec_csr%NOTFOUND ) THEN
172 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
173 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
174 FND_MSG_PUB.add;
175 CLOSE get_uc_header_rec_csr;
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178 CLOSE get_uc_header_rec_csr;
179 END IF;
180
181 OPEN get_extra_nodes_csr(l_top_instance_id);
182 LOOP
183 FETCH get_extra_nodes_csr into l_csi_id;
184 EXIT WHEN get_extra_nodes_csr%NOTFOUND;
185
186
187 --Fetch the instance details for the error message
188 OPEN get_node_details_csr(l_csi_id);
189 FETCH get_node_details_csr into l_item_number, l_serial_number;
190 CLOSE get_node_details_csr;
191
192 OPEN get_unit_instance_csr(l_csi_id);
193 FETCH get_unit_instance_csr into l_unit_csi_id;
194 IF (get_unit_instance_csr%FOUND) THEN
195 OPEN get_node_unit_csr(l_unit_csi_id);
196 FETCH get_node_unit_csr into l_unit_name;
197 CLOSE get_node_unit_csr;
198 END IF;
199 CLOSE get_unit_instance_csr;
200
201 --Build the error message
202 FND_MESSAGE.Set_Name('AHL','AHL_UC_EXTRA_NODE');
203 FND_MESSAGE.Set_Token('ITEM_NO', l_item_number);
204 FND_MESSAGE.Set_Token('SERIAL_NO', l_serial_number);
205 FND_MESSAGE.Set_Token('UNIT_NAME', l_unit_name);
206
207 --Get the extra node childrens and list them.
208 OPEN get_extra_node_child_csr(l_csi_id);
209 LOOP
210 FETCH get_extra_node_child_csr into l_csi_id;
211 EXIT WHEN get_extra_node_child_csr%NOTFOUND;
212
213 --Fetch the instance details for the error message
214 OPEN get_node_details_csr(l_csi_id);
215 FETCH get_node_details_csr into l_item_number, l_serial_number;
216 CLOSE get_node_details_csr;
217
218 --Build the error message
219 FND_MESSAGE.Set_Name('AHL','AHL_UC_EXTRA_NODE');
220 FND_MESSAGE.Set_Token('ITEM_NO', l_item_number);
221 FND_MESSAGE.Set_Token('SERIAL_NO', l_serial_number);
222 FND_MESSAGE.Set_Token('UNIT_NAME', l_unit_name);
223
224 END LOOP;
225 CLOSE get_extra_node_child_csr;
226
227 -- Write the error message to the error table.
228 IF (p_x_error_table.COUNT >0) THEN
229 p_x_error_table(p_x_error_table.LAST+1) := FND_MESSAGE.get;
230 ELSE
231 p_x_error_table(0) := FND_MESSAGE.get;
232 END IF;
233 x_evaluation_status := FND_API.G_FALSE;
234
235 END LOOP;
236 CLOSE get_extra_nodes_csr;
237
238 -- Check Error Message stack.
239 x_msg_count := FND_MSG_PUB.count_msg;
240 IF x_msg_count > 0 THEN
241 RAISE FND_API.G_EXC_ERROR;
242 END IF;
243
244 -- Standard call to get message count and if count is 1, get message info
245 FND_MSG_PUB.Count_And_Get
246 ( p_count => x_msg_count,
247 p_data => x_msg_data,
248 p_encoded => fnd_api.g_false
249 );
250
251 EXCEPTION
252 WHEN FND_API.G_EXC_ERROR THEN
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
255 p_data => x_msg_data,
256 p_encoded => fnd_api.g_false);
257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
260 p_data => x_msg_data,
261 p_encoded => fnd_api.g_false);
262 WHEN OTHERS THEN
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
265 p_procedure_name => l_api_name,
266 p_error_text => SQLERRM);
267
268 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
269 p_data => x_msg_data,
270 p_encoded => fnd_api.g_false);
271
272 END List_Extra_Nodes;
273
274 --------------------------------------------------------------------------------------------
275 -- Start of Comments --
276 --
277 -- Procedure name : Check_Extra_Nodes
278 -- Type : Private
279 -- Function : Checks if there are any extra nodes in a unit configuration.
280 -- Pre-reqs :
281 --
282 -- Standard IN Parameters :
283 -- p_api_version IN NUMBER Required
284 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
285 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
286 -- Standard OUT Parameters :
287 -- x_return_status OUT VARCHAR2 Required
288 -- x_msg_count OUT NUMBER Required
289 -- x_msg_data OUT VARCHAR2 Required
290 --
291 -- check_extra_nodes parameters :
292 -- p_uc_header_id IN Required
293 -- The header id of the unit configuration
294 -- x_evaluation_status OUT VARCHAR2
295 -- The flag which indicates whether the unit configuration
296 -- has any extra nodes and returns FND_API.G_TRUE ot FND_API.G_FALSE accordingly.
297 -- History:
298 -- 05/19/03 SBethi CREATED
299 --
300 -- Version :
301 -- Initial Version 1.0
302 --
303 -- End of Comments.
304 --------------------------------------------------------------------------------------------
305
306 PROCEDURE Check_Extra_Nodes(
307 p_api_version IN NUMBER,
308 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
309 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_msg_count OUT NOCOPY NUMBER,
312 x_msg_data OUT NOCOPY VARCHAR2,
313 p_uc_header_id IN NUMBER,
314 x_evaluation_status OUT NOCOPY VARCHAR2
315 )
316 IS
317 --
318 CURSOR get_uc_header_rec_csr(p_uc_header_id IN NUMBER) IS
319 SELECT csi_item_instance_id
320 FROM ahl_unit_config_headers
321 WHERE unit_config_header_id = p_uc_header_id
322 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
323 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
324 ---
325 --To get extra nodes
326 CURSOR get_extra_nodes_csr(p_csi_item_instance_id IN NUMBER) IS
327 SELECT 'X'
328 FROM csi_ii_relationships
329 WHERE position_reference is null
330 START WITH object_id = p_csi_item_instance_id
331 AND relationship_type_code = 'COMPONENT-OF'
332 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
333 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
334 CONNECT BY PRIOR subject_id = object_id
335 AND relationship_type_code = 'COMPONENT-OF'
336 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
337 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
338
339 --
340 l_api_version CONSTANT NUMBER := 1.0;
341 l_api_name CONSTANT VARCHAR2(30) := 'Check_Extra_Nodes';
342 l_top_instance_id NUMBER;
343 l_dummy VARCHAR2(1);
344 --
345 BEGIN
346
347
348 -- Standard call to check for call compatibility
349 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
350 G_PKG_NAME) THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 -- Initialize message list if p_init_msg_list is set to TRUE
355 IF FND_API.To_Boolean(p_init_msg_list) THEN
356 FND_MSG_PUB.Initialize;
357 END IF;
358
359 -- Initialize API return status to success
360 x_return_status := FND_API.G_RET_STS_SUCCESS;
361
362 -- Begin Processing
363 --Call procedure to validate the uc header id
364 OPEN get_uc_header_rec_csr(p_uc_header_id);
365 FETCH get_uc_header_rec_csr INTO l_top_instance_id;
366 IF ( get_uc_header_rec_csr%NOTFOUND ) THEN
367 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
368 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
369 FND_MSG_PUB.add;
370 CLOSE get_uc_header_rec_csr;
371 RAISE FND_API.G_EXC_ERROR;
372 END IF;
373 CLOSE get_uc_header_rec_csr;
374
375
376 OPEN get_extra_nodes_csr(l_top_instance_id);
377 FETCH get_extra_nodes_csr INTO l_dummy;
378 IF (get_extra_nodes_csr%FOUND) THEN
379 x_evaluation_status := FND_API.G_FALSE;
380 ELSE
381 x_evaluation_status := FND_API.G_TRUE;
382 END IF;
383 CLOSE get_extra_nodes_csr;
384 --Completed Processing
385
386 -- Check Error Message stack.
387 x_msg_count := FND_MSG_PUB.count_msg;
388 IF x_msg_count > 0 THEN
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391
392 -- Standard call to get message count and if count is 1, get message info
393 FND_MSG_PUB.Count_And_Get
394 ( p_count => x_msg_count,
395 p_data => x_msg_data,
396 p_encoded => fnd_api.g_false
397 );
398 EXCEPTION
399 WHEN FND_API.G_EXC_ERROR THEN
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
402 p_data => x_msg_data,
403 p_encoded => fnd_api.g_false);
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
407 p_data => x_msg_data,
408 p_encoded => fnd_api.g_false);
409 WHEN OTHERS THEN
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
412 p_procedure_name => l_api_name,
413 p_error_text => SQLERRM);
414
415 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
416 p_data => x_msg_data,
417 p_encoded => fnd_api.g_false);
418 END Check_Extra_Nodes;
419
420 --------------------------------------------------------------------------------------------
421 -- Start of Comments --
422 --
423 -- Procedure name : Check_Missing_Positions
424 -- Type : Private
425 -- Function : List all the checks if the unit config has any missing
426 -- positions.
427 -- Pre-reqs :
428 --
429 -- Standard IN Parameters :
430 -- p_api_version IN NUMBER Required
431 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
432 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
433 -- Standard OUT Parameters :
434 -- x_return_status OUT VARCHAR2 Required
435 -- x_msg_count OUT NUMBER Required
436 -- x_msg_data OUT VARCHAR2 Required
437 --
438 -- list_missing_positions parameters :
439 -- p_uc_header_id IN Required
440 -- The header id of the unit configuration
441 -- x_evaluation_status OUT VARCHAR2
442 -- The flag which indicates whether the unit configuration
443 -- has any missing positions and returns FND_API.G_TRUE ot FND_API.G_FALSE accordingly.
444 -- History:
445 -- 05/19/03 SBethi CREATED
446 --
447 -- Version :
448 -- Initial Version 1.0
449 --
450 -- End of Comments.
451 --------------------------------------------------------------------------------------------
452
453 PROCEDURE Check_Missing_Positions(
454 p_api_version IN NUMBER,
455 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
456 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
457 x_return_status OUT NOCOPY VARCHAR2,
458 x_msg_count OUT NOCOPY NUMBER,
459 x_msg_data OUT NOCOPY VARCHAR2,
460 p_uc_header_id IN NUMBER,
461 x_evaluation_status OUT NOCOPY VARCHAR2
462 )
463 IS
464 --
465 CURSOR get_uc_header_rec_csr(p_uc_header_id IN NUMBER) IS
466 SELECT csi_item_instance_id
467 FROM ahl_unit_config_headers
468 WHERE unit_config_header_id = p_uc_header_id
469 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
470 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
471 --
472 --Get all of the unit's instances/positions that are NOT extra nodes
473 CURSOR get_unit_tree_csr(c_csi_instance_id IN NUMBER) IS
474 SELECT c_csi_instance_id FROM dual
475 UNION ALL
476 SELECT subject_id
477 FROM csi_ii_relationships
478 START WITH object_id = c_csi_instance_id
479 AND relationship_type_code = 'COMPONENT-OF'
480 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
481 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
482 CONNECT BY PRIOR subject_id = object_id
483 AND relationship_type_code = 'COMPONENT-OF'
484 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
485 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
486 AND position_reference is not null;
487
488
489 --Get the relationship id if instance is a uc header
490 CURSOR get_relnship_id_csr(c_csi_instance_id IN NUMBER) IS
491 SELECT mc.relationship_id
492 FROM ahl_unit_config_headers uc, ahl_mc_relationships mc
493 WHERE uc.master_config_id = mc.mc_header_id
494 AND mc.parent_relationship_id is null
495 AND uc.csi_item_instance_id = c_csi_instance_id
496 AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
497 AND trunc(sysdate) < trunc(nvl(uc.active_end_date, sysdate+1));
498
499 --If instance is not uc header, then get it based on instance
500 CURSOR get_pos_ref_reln_csr (c_csi_instance_id IN NUMBER) IS
501 SELECT TO_NUMBER(position_reference)
502 FROM csi_ii_relationships csi
503 WHERE position_reference is not null
504 AND subject_id = c_csi_instance_id
505 AND relationship_type_code = 'COMPONENT-OF'
506 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
507 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
508
509 --
510 --Check mandatory children
511 CURSOR check_mand_child_missing_csr(c_relationship_id IN NUMBER,
512 c_parent_instance_id IN NUMBER) IS
513 SELECT 'X'
514 FROM ahl_mc_relationships mc
515 WHERE mc.parent_relationship_id = c_relationship_id
516 AND mc.position_necessity_code = 'MANDATORY'
517 AND trunc(nvl(mc.active_start_date,sysdate)) <= trunc(sysdate)
518 AND trunc(sysdate) < trunc(nvl(mc.active_end_date, sysdate+1))
519 AND NOT EXISTS (
520 SELECT 'X'
521 FROM csi_ii_relationships csi
522 WHERE object_id = c_parent_instance_id
523 AND TO_NUMBER(position_reference) = mc.relationship_id
524 AND relationship_type_code = 'COMPONENT-OF'
525 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
526 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)));
527 --
528 l_api_version CONSTANT NUMBER := 1.0;
529 l_api_name CONSTANT VARCHAR2(30) := 'Check_Missing_Positions';
530 l_top_instance_id NUMBER;
531 l_csi_ii_id NUMBER;
532 l_rel_id NUMBER;
533 l_dummy VARCHAR2(1);
534 --
535 BEGIN
536
537
538 -- Standard call to check for call compatibility
539 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
540 G_PKG_NAME) THEN
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END IF;
543
544 -- Initialize message list if p_init_msg_list is set to TRUE
545 IF FND_API.To_Boolean(p_init_msg_list) THEN
546 FND_MSG_PUB.Initialize;
547 END IF;
548
549 -- Initialize API return status to success
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551 x_evaluation_status := FND_API.G_TRUE;
552
553 -- Begin Processing
554 --Call procedure to validate the uc header id
555 OPEN get_uc_header_rec_csr(p_uc_header_id);
556 FETCH get_uc_header_rec_csr INTO l_top_instance_id;
557 IF ( get_uc_header_rec_csr%NOTFOUND ) THEN
558 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
559 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
560 FND_MSG_PUB.add;
561 CLOSE get_uc_header_rec_csr;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564 CLOSE get_uc_header_rec_csr;
565
566 OPEN get_unit_tree_csr(l_top_instance_id);
567 <<l_unit_tree_loop>>
568 LOOP
569 FETCH get_unit_tree_csr into l_csi_ii_id;
570 EXIT WHEN get_unit_tree_csr%NOTFOUND;
571
572 --Fetch instance position if it is subunit
573 OPEN get_relnship_id_csr(l_csi_ii_id);
574 FETCH get_relnship_id_csr INTO l_rel_id;
575
576 --If not subunit, just fetch assuming instance.
577 IF (get_relnship_id_csr%NOTFOUND) THEN
578 OPEN get_pos_ref_reln_csr(l_csi_ii_id);
579 FETCH get_pos_ref_reln_csr INTO l_rel_id;
580 CLOSE get_pos_ref_reln_csr;
581 END IF;
582 CLOSE get_relnship_id_csr;
583
584 -- Check if all the mandatory positions have item instances mapped.
585 OPEN check_mand_child_missing_csr( l_rel_id,l_csi_ii_id);
586 FETCH check_mand_child_missing_csr INTO l_dummy;
587
588 --If found, then there are missing positions
589 IF (check_mand_child_missing_csr%FOUND) THEN
590 x_evaluation_status := FND_API.G_FALSE;
591 EXIT l_unit_tree_loop;
592 END IF;
593 CLOSE check_mand_child_missing_csr;
594
595 END LOOP;
596 CLOSE get_unit_tree_csr;
597
598 --Completed Processing
599
600 -- Check Error Message stack.
601 x_msg_count := FND_MSG_PUB.count_msg;
602 IF x_msg_count > 0 THEN
603 RAISE FND_API.G_EXC_ERROR;
604 END IF;
605
606 -- Standard call to get message count and if count is 1, get message info
607 FND_MSG_PUB.Count_And_Get
608 ( p_count => x_msg_count,
609 p_data => x_msg_data,
610 p_encoded => fnd_api.g_false
611 );
612
613
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 x_return_status := FND_API.G_RET_STS_ERROR;
617 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
618 p_data => x_msg_data,
619 p_encoded => fnd_api.g_false);
620
621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
624 p_data => x_msg_data,
625 p_encoded => fnd_api.g_false);
626
627 WHEN OTHERS THEN
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
630 p_procedure_name => l_api_name,
631 p_error_text => SQLERRM);
632
633 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
634 p_data => x_msg_data,
635 p_encoded => fnd_api.g_false);
636
637 END Check_Missing_Positions;
638
639 --------------------------------------------------------------------------------------------
640 -- Start of Comments --
641 --
642 -- Procedure name : List_Missing_Positions
643 -- Type : Private
644 -- Function : List all the mandatory positions that dont have instances mapped to it.
645 -- Pre-reqs :
646 --
647 -- Standard IN Parameters :
648 -- p_api_version IN NUMBER Required
649 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
650 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
651 -- Standard OUT Parameters :
652 -- x_return_status OUT VARCHAR2 Required
653 -- x_msg_count OUT NUMBER Required
654 -- x_msg_data OUT VARCHAR2 Required
655 --
656 -- list_missing_positions parameters :
657 -- p_uc_header_id IN Required
658 -- The header id of the unit configuration
659 -- x_evaluation_status OUT VARCHAR2
660 -- The flag which indicates whether the unit has any missing positions
661 -- History:
662 -- 05/19/03 SBethi CREATED
663 --
664 -- Version :
665 -- Initial Version 1.0
666 --
667 -- End of Comments.
668 --------------------------------------------------------------------------------------------
669
670 PROCEDURE List_Missing_Positions(
671 p_api_version IN NUMBER,
672 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
673 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
674 x_return_status OUT NOCOPY VARCHAR2,
675 x_msg_count OUT NOCOPY NUMBER,
676 x_msg_data OUT NOCOPY VARCHAR2,
677 p_uc_header_id IN NUMBER,
678 p_csi_instance_id IN NUMBER,
679 p_x_error_table IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
680 x_evaluation_status OUT NOCOPY VARCHAR2
681 ) IS
682 ----
683 CURSOR get_uc_header_rec_csr(p_uc_header_id IN NUMBER) IS
684 SELECT csi_item_instance_id
685 FROM ahl_unit_config_headers
686 WHERE unit_config_header_id = p_uc_header_id
687 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
688 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
689 --
690 --Get all of the unit's instances/positions. Get all non-extra nodes
691 CURSOR get_unit_tree_csr(c_csi_instance_id IN NUMBER) IS
692 SELECT c_csi_instance_id FROM dual
693 UNION ALL
694 SELECT subject_id
695 FROM csi_ii_relationships
696 START WITH object_id = c_csi_instance_id
697 AND relationship_type_code = 'COMPONENT-OF'
698 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
699 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
700 CONNECT BY PRIOR subject_id = object_id
701 AND relationship_type_code = 'COMPONENT-OF'
702 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
703 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
704 AND position_reference is not null;
705
706 --Get the relationship id if instance is a uc header
707 CURSOR get_relnship_id_csr(c_csi_instance_id IN NUMBER) IS
708 SELECT mc.relationship_id
709 FROM ahl_unit_config_headers uc, ahl_mc_relationships mc
710 WHERE uc.master_config_id = mc.mc_header_id
711 AND mc.parent_relationship_id is null
712 AND uc.csi_item_instance_id = c_csi_instance_id
713 AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
714 AND trunc(sysdate) < trunc(nvl(uc.active_end_date, sysdate+1));
715
716 --If instance is not uc header, then get it based on instance
717 CURSOR get_pos_ref_reln_csr (c_csi_instance_id IN NUMBER) IS
718 SELECT TO_NUMBER(position_reference)
719 FROM csi_ii_relationships csi
720 WHERE position_reference is not null
721 AND subject_id = c_csi_instance_id
722 AND relationship_type_code = 'COMPONENT-OF'
723 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
724 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
725
726 --
727 --Check mandatory children
728 CURSOR check_mand_child_missing_csr(c_relationship_id IN NUMBER,
729 c_parent_instance_id IN NUMBER) IS
730 SELECT mc.relationship_id, mc.position_ref_code
731 FROM ahl_mc_relationships mc
732 WHERE mc.parent_relationship_id = c_relationship_id
733 AND mc.position_necessity_code = 'MANDATORY'
734 AND trunc(nvl(mc.active_start_date,sysdate)) <= trunc(sysdate)
735 AND trunc(sysdate) < trunc(nvl(mc.active_end_date, sysdate+1))
736 AND NOT EXISTS (
737 SELECT 'X'
738 FROM csi_ii_relationships csi
739 WHERE object_id = c_parent_instance_id
740 AND TO_NUMBER(position_reference) = mc.relationship_id
741 AND relationship_type_code = 'COMPONENT-OF'
742 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
743 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)));
744 --
745 --Get mandatory children for missing position
746 CURSOR get_mand_pos_desc_csr(c_relationship_id IN NUMBER) IS
747 SELECT position_ref_code
748 FROM ahl_mc_relationships
749 START WITH parent_relationship_id = c_relationship_id
750 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
751 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
752 CONNECT BY PRIOR relationship_id = parent_relationship_id
753 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
754 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
755 AND position_necessity_code = 'MANDATORY';
756 --
757 l_api_version CONSTANT NUMBER := 1.0;
758 l_api_name CONSTANT VARCHAR2(30) := 'List_Missing_Positions';
759 l_top_instance_id NUMBER;
760 l_csi_ii_id NUMBER;
761 l_rel_id NUMBER;
762 l_miss_rel_id NUMBER;
763 l_return_val BOOLEAN DEFAULT TRUE;
764 l_pos_ref_code fnd_lookups.lookup_code%TYPE;
765 l_pos_ref_meaning fnd_lookups.meaning%TYPE;
766
767 BEGIN
768
769 -- Standard call to check for call compatibility
770 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
771 G_PKG_NAME) THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775 -- Initialize message list if p_init_msg_list is set to TRUE
776 IF FND_API.To_Boolean(p_init_msg_list) THEN
777 FND_MSG_PUB.Initialize;
778 END IF;
779
780 -- Initialize API return status to success
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782 --Setting output parameters
783 x_evaluation_status := FND_API.G_TRUE;
784
785 -- Begin Processing
786 IF (p_csi_instance_id IS NOT NULL AND
787 p_uc_header_id IS NULL) THEN
788 l_top_instance_id := p_csi_instance_id;
789 ELSE
790 --Call procedure to validate the uc header id
791 OPEN get_uc_header_rec_csr(p_uc_header_id);
792 FETCH get_uc_header_rec_csr INTO l_top_instance_id;
793 IF ( get_uc_header_rec_csr%NOTFOUND ) THEN
794 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
795 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
796 FND_MSG_PUB.add;
797 CLOSE get_uc_header_rec_csr;
798 RAISE FND_API.G_EXC_ERROR;
799 END IF;
800 CLOSE get_uc_header_rec_csr;
801 END IF;
802
803 OPEN get_unit_tree_csr(l_top_instance_id);
804 LOOP
805 FETCH get_unit_tree_csr into l_csi_ii_id;
806 EXIT WHEN get_unit_tree_csr%NOTFOUND;
807
808 --Fetch instance position if it is subunit
809 OPEN get_relnship_id_csr(l_csi_ii_id);
810 FETCH get_relnship_id_csr INTO l_rel_id;
811
812 --If not subunit, just fetch assuming instance.
813 IF (get_relnship_id_csr%NOTFOUND) THEN
814 OPEN get_pos_ref_reln_csr(l_csi_ii_id);
815 FETCH get_pos_ref_reln_csr INTO l_rel_id;
816 CLOSE get_pos_ref_reln_csr;
817 END IF;
818 CLOSE get_relnship_id_csr;
819
820 -- Check if all the mandatory positions have item instances mapped.
821 OPEN check_mand_child_missing_csr(l_rel_id, l_csi_ii_id);
822 LOOP
823 FETCH check_mand_child_missing_csr INTO l_miss_rel_id, l_pos_ref_code;
824 EXIT WHEN check_mand_child_missing_csr%NOTFOUND;
825
826 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning('AHL_POSITION_REFERENCE',
827 l_pos_ref_code,
828 l_pos_ref_meaning,
829 l_return_val);
830 IF NOT(l_return_val) THEN
831 l_pos_ref_meaning := l_pos_ref_code;
832 END IF;
833
834 --Building the error message
835 FND_MESSAGE.Set_Name('AHL','AHL_UC_NOTASSIGN_MANDATORY');
836 FND_MESSAGE.Set_Token('POSN_REF',l_pos_ref_meaning);
837
838 --Writing the message to the error table
839 IF (p_x_error_table.COUNT >0) THEN
840 p_x_error_table(p_x_error_table.LAST+1) := FND_MESSAGE.get;
841 ELSE
842 p_x_error_table(0) := FND_MESSAGE.get;
843 END IF;
844
845 x_evaluation_status := FND_API.G_FALSE;
846
847 --Now fetch the mandatory descendents of the mandatory position
848 OPEN get_mand_pos_desc_csr(l_miss_rel_id);
849 LOOP
850 FETCH get_mand_pos_desc_csr INTO l_pos_ref_code;
851 EXIT WHEN get_mand_pos_desc_csr%NOTFOUND;
852
853 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning('AHL_POSITION_REFERENCE',
854 l_pos_ref_code,
855 l_pos_ref_meaning,
856 l_return_val);
857 IF NOT(l_return_val) THEN
858 l_pos_ref_meaning := l_pos_ref_code;
859 END IF;
860
861 --Building the error message
862 FND_MESSAGE.Set_Name('AHL','AHL_UC_NOTASSIGN_MANDATORY');
863 FND_MESSAGE.Set_Token('POSN_REF',l_pos_ref_meaning);
864
865 --Writing the message to the error table
866 IF (p_x_error_table.COUNT >0) THEN
867 p_x_error_table(p_x_error_table.LAST+1) := FND_MESSAGE.get;
868 ELSE
869 p_x_error_table(0) := FND_MESSAGE.get;
870 END IF;
871
872 END LOOP;
873 CLOSE get_mand_pos_desc_csr;
874
875 END LOOP; -- End of missing mandatory nodes
876 CLOSE check_mand_child_missing_csr;
877
878 END LOOP;
879 CLOSE get_unit_tree_csr;
880
881 --Completed Processing
882
883 -- Check Error Message stack.
884 x_msg_count := FND_MSG_PUB.count_msg;
885 IF x_msg_count > 0 THEN
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888
889 -- Standard call to get message count and if count is 1, get message info
890 FND_MSG_PUB.Count_And_Get
891 ( p_count => x_msg_count,
892 p_data => x_msg_data,
893 p_encoded => fnd_api.g_false
894 );
895
896
897 EXCEPTION
898 WHEN FND_API.G_EXC_ERROR THEN
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
901 p_data => x_msg_data,
902 p_encoded => fnd_api.g_false);
903 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
905 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
906 p_data => x_msg_data,
907 p_encoded => fnd_api.g_false);
908 WHEN OTHERS THEN
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
911 p_procedure_name => l_api_name,
912 p_error_text => SQLERRM);
913
914 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
915 p_data => x_msg_data,
916 p_encoded => fnd_api.g_false);
917 END List_Missing_Positions;
918
919 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 05-Dec-2007
920 -- This procedure adds a Quantity specific message to the Error Table.
921 PROCEDURE Add_Qty_Message(p_position_ref_code IN VARCHAR2,
922 p_inst_qty IN NUMBER,
923 p_qty_less_flag IN VARCHAR2,
924 p_x_error_table IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type) IS
925
926 l_return_val BOOLEAN DEFAULT TRUE;
927 l_pos_ref_code fnd_lookups.lookup_code%TYPE;
928 l_pos_ref_meaning fnd_lookups.meaning%TYPE;
929
930 BEGIN
931 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning('AHL_POSITION_REFERENCE',
932 p_position_ref_code,
933 l_pos_ref_meaning,
934 l_return_val);
935 IF NOT(l_return_val) THEN
936 l_pos_ref_meaning := p_position_ref_code;
937 END IF;
938 -- Build the error message
939 IF (p_qty_less_flag = 'Y') THEN
940 FND_MESSAGE.Set_Name('AHL', 'AHL_UC_POS_QTY_LESS');
941 ELSE
942 FND_MESSAGE.Set_Name('AHL', 'AHL_UC_POS_QTY_MORE');
943 END IF;
944 FND_MESSAGE.Set_Token('POSN_REF', l_pos_ref_meaning);
945 --Writing the message to the error table
946 IF (p_x_error_table.COUNT > 0) THEN
947 p_x_error_table(p_x_error_table.LAST + 1) := FND_MESSAGE.get;
948 ELSE
949 p_x_error_table(0) := FND_MESSAGE.get;
950 END IF;
951
952 END Add_Qty_Message;
953
954 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 05-Dec-2007
955 -- This function checks if a Quantity type rule exists for the given instance's position
956 -- p_instance_id is the Parent Instance and is used to derive the Position Path to Rule Existence Check.
957 -- It returns 'Y' if a rule exists and 'N' if not.
958 FUNCTION Quantity_Rule_Exists(p_instance_id IN NUMBER) RETURN VARCHAR2 IS
959
960 CURSOR rule_exists_csr IS
961 SELECT distinct rul.rule_id, rul.rule_name, rul.mc_header_id
962 FROM AHL_MC_RULES_B rul, AHL_MC_RULE_STATEMENTS rst,
963 AHL_APPLICABLE_INSTANCES ap
964 WHERE rst.rule_id = rul.rule_id
965 AND rul.rule_type_code = 'MANDATORY'
966 AND rst.subject_type = 'POSITION'
967 AND rst.subject_id = ap.position_id
968 AND rst.object_type = 'TOT_CHILD_QUANTITY'
969 AND rst.operator = 'MUST_HAVE'
970 AND TRUNC(nvl(rul.ACTIVE_START_DATE, sysdate-1)) <= TRUNC(sysdate)
971 AND TRUNC(nvl(rul.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
972
973 --check that the instance is installed in a UC derived from the Rule's MC_HEADER_ID
974 CURSOR check_inst_in_uc_ofmc_csr(c_csi_instance_id IN NUMBER, c_mc_header_id IN NUMBER) IS
975 SELECT uch.csi_item_instance_id
976 FROM ahl_unit_config_headers uch
977 WHERE uch.master_config_id = c_mc_header_id
978 AND uch.csi_item_instance_id = c_csi_instance_id
979 UNION ALL
980 SELECT csi_ii.object_id
981 FROM csi_ii_relationships csi_ii
982 WHERE csi_ii.object_id IN
983 (SELECT csi_item_instance_id
984 FROM ahl_unit_config_headers
985 WHERE trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
986 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
987 AND master_config_id = c_mc_header_id)
988 START WITH csi_ii.subject_id = c_csi_instance_id
989 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
990 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
991 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
992 CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
993 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
994 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
995 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
996
997 l_return_status VARCHAR2(1);
998 l_msg_count NUMBER;
999 l_msg_data VARCHAR2(2000);
1000 l_path_position_id NUMBER;
1001 l_rule_id NUMBER;
1002 l_rule_name AHL_MC_RULES_B.RULE_NAME%TYPE;
1003 l_rule_uc_top_inst_id NUMBER;
1004 l_ret_val VARCHAR2(1) DEFAULT 'N';
1005 l_mc_header_id NUMBER;
1006 L_DEBUG_KEY VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.Quantity_Rule_Exists';
1007
1008 BEGIN
1009 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin',
1011 'At the start of the function, p_instance_id = ' || p_instance_id);
1012 END IF;
1013 EXECUTE IMMEDIATE 'DELETE FROM AHL_APPLICABLE_INSTANCES';
1014 -- Get all the Position Paths associated with the instance p_instance_id into AHL_APPLICABLE_INSTANCES.
1015 -- If there is a configuration like A.1-B.1-C.1 and path positions exist for C.1, B.1-C.1, A.1-B.1-C.1 all these
1016 -- will be picked up after the call below
1017 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Positions(p_api_version => 1.0,
1018 x_return_status => l_return_status,
1019 x_msg_count => l_msg_count,
1020 x_msg_data => l_msg_data,
1021 p_csi_item_instance_id => p_instance_id);
1022
1023 -- Check that a quantity rule exists at the installed poisition
1024 /*
1025 1. The rule_exists_csr returns all the rules for each of the path position applicable to the instance. Consider the case where
1026 a rule is defined for the position A.1-B.1-C.1 and it is NON-version specific for all the segments.
1027 For a case like A.2-B.2-C.2, the rule_exists_csr returns records. But this rule is actually applicable only to
1028 A.1-B.X-C.X. We check in the check_inst_in_uc_ofmc_csr that the instance under examination is derived from an UC got from an MC for which the rule is defined.
1029
1030 2. Additionally, say we have a rule 'RULE1' defined at B.1-C.X and a rule RULE2 defined at C.1 (as NON version specific)
1031 RULE1 will be applicable for B.1-C.2, but RULE2 will not be applicable to B.1-C.2
1032 But rule_exists_csr, will return both of the above rules when looking at B.1-C.2. But check_inst_in_uc_ofmc_csr will filter out
1033 RULE2 as we want.
1034 */
1035 OPEN rule_exists_csr;
1036 LOOP
1037 FETCH rule_exists_csr INTO l_rule_id, l_rule_name, l_mc_header_id;
1038 EXIT WHEN rule_exists_csr%NOTFOUND;
1039
1040 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1041 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1042 'Quantity Type Rule Exists: l_rule_id: ' || l_rule_id ||
1043 ', l_mc_header_id: ' || l_mc_header_id ||
1044 ', l_rule_name: ' || l_rule_name);
1045 END IF;
1046
1047 OPEN check_inst_in_uc_ofmc_csr(p_instance_id, l_mc_header_id);
1048 FETCH check_inst_in_uc_ofmc_csr INTO l_rule_uc_top_inst_id;
1049
1050 --Verify that the rule is applicable to currently installed instance
1051 IF (check_inst_in_uc_ofmc_csr%FOUND) THEN
1052 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1054 'Rule is applicable to the UC top instance: l_rule_uc_top_inst_id: ' || l_rule_uc_top_inst_id);
1055 END IF;
1056 l_ret_val := 'Y';
1057 CLOSE check_inst_in_uc_ofmc_csr;
1058 --It is enough if there exists at least one quantity rule for the position.
1059 EXIT;
1060 END IF;
1061 CLOSE check_inst_in_uc_ofmc_csr;
1062 END LOOP;
1063 CLOSE rule_exists_csr;
1064
1065 IF (l_ret_val = 'N') THEN
1066 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1067 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Quantity Rule does not exist?');
1068 END IF;
1069 END IF;
1070
1071 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1072 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
1073 'At the end of the function, About to return ' || l_ret_val);
1074 END IF;
1075 RETURN l_ret_val;
1076 END Quantity_Rule_Exists;
1077
1078 --------------------------------------------------------------------------------------------
1079 -- Start of Comments --
1080 --
1081 -- Procedure name : Validate_Position_Quantities
1082 -- Type : Private
1083 -- Function : This procedure was added for the FP OGMA Issue 105 to support Non Serialized Items.
1084 -- It validates the instance quantity against the position/item group.
1085 -- If there is a Quantity type rule at the Parent position, the floor validation
1086 -- is not done (Only ceiling validation is done). Since in this case the Rule
1087 -- overrides and obviates any need for Quantity validation and validating quantity
1088 -- based on position may actually contradict the rule.
1089 --
1090 -- Pre-reqs
1091 --
1092 -- Standard IN Parameters :
1093 -- p_api_version IN NUMBER Required
1094 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
1095 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1096 -- Standard OUT Parameters :
1097 -- x_return_status OUT VARCHAR2 Required
1098 -- x_msg_count OUT NUMBER Required
1099 -- x_msg_data OUT VARCHAR2 Required
1100 --
1101 -- Validate_Position_Quantities Parameters :
1102 -- p_uc_header_id IN NUMBER Conditionally Required
1103 -- The header id of the unit configuration. Not required if p_csi_instance_id is given.
1104 -- p_csi_instance_id IN NUMBER Conditionally Required
1105 -- The instance where the Quantity needs to be checked. Not required if p_uc_header_id is given.
1106 -- x_evaluation_status OUT VARCHAR2 The flag which indicates whether the unit has any Quantity mismatch.
1107 -- p_x_error_table IN OUT AHL_UC_POS_NECES_PVT.Error_Tbl_Type
1108 -- The output table with the list of Quantity based validation failures
1109 --
1110 -- History:
1111 -- 05-Dec-2007 SATHAPLI Created
1112 --
1113 -- Version:
1114 -- Initial Version 1.0
1115 --
1116 -- End of Comments.
1117 --------------------------------------------------------------------------------------------
1118
1119 PROCEDURE Validate_Position_Quantities(
1120 p_api_version IN NUMBER,
1121 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1122 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1123 x_return_status OUT NOCOPY VARCHAR2,
1124 x_msg_count OUT NOCOPY NUMBER,
1125 x_msg_data OUT NOCOPY VARCHAR2,
1126 p_uc_header_id IN NUMBER,
1127 p_csi_instance_id IN NUMBER,
1128 p_x_error_table IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
1129 x_evaluation_status OUT NOCOPY VARCHAR2) IS
1130
1131 -- Validate the UC and get the top instance
1132 CURSOR get_uc_header_rec_csr IS
1133 SELECT csi_item_instance_id
1134 FROM ahl_unit_config_headers
1135 WHERE unit_config_header_id = p_uc_header_id
1136 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1137 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
1138
1139 -- Get all leaf non-extra nodes and do not get sub-config root nodes, as these do not need the position quantity validation.
1140 -- Also such instances are inflection points for which get_pos_dtls_csr will not return any records.
1141 CURSOR get_unit_tree_csr(c_csi_instance_id IN NUMBER) IS
1142 SELECT c_csi_instance_id FROM dual
1143 WHERE NOT EXISTS (SELECT 1
1144 FROM csi_ii_relationships
1145 WHERE object_id = c_csi_instance_id
1146 AND relationship_type_code = 'COMPONENT-OF'
1147 AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
1148 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1149 UNION ALL
1150 SELECT 1
1151 FROM ahl_unit_config_headers
1152 WHERE csi_item_instance_id = c_csi_instance_id
1153 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1154 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
1155 UNION ALL
1156 SELECT OUTER.subject_id
1157 FROM csi_ii_relationships OUTER
1158 WHERE NOT EXISTS (SELECT 1
1159 FROM csi_ii_relationships
1160 WHERE object_id = OUTER.subject_id
1161 AND relationship_type_code = 'COMPONENT-OF'
1162 AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
1163 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1164 UNION ALL
1165 SELECT 1
1166 FROM ahl_unit_config_headers
1167 WHERE csi_item_instance_id = OUTER.subject_id
1168 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1169 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
1170 START WITH object_id = c_csi_instance_id
1171 AND relationship_type_code = 'COMPONENT-OF'
1172 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
1173 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1174 AND position_reference is not null
1175 CONNECT BY PRIOR subject_id = object_id
1176 AND relationship_type_code = 'COMPONENT-OF'
1177 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
1178 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1179 AND position_reference is not null;
1180
1181 -- Get the Relationship of the instance
1182 CURSOR get_pos_ref_reln_csr(c_csi_instance_id IN NUMBER) IS
1183 SELECT TO_NUMBER(position_reference)
1184 FROM csi_ii_relationships csi
1185 WHERE position_reference is not null
1186 AND subject_id = c_csi_instance_id
1187 AND relationship_type_code = 'COMPONENT-OF'
1188 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1189 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
1190
1191 -- Get the Position Quantity Details
1192 CURSOR get_pos_dtls_csr(c_mc_relationship_id IN NUMBER,
1193 c_instance_id IN NUMBER) IS
1194 SELECT iasso.quantity Itm_qty,
1195 iasso.uom_code Itm_uom_code,
1196 iasso.revision Itm_revision,
1197 iasso.item_association_id,
1198 reln.quantity Posn_qty,
1199 reln.uom_code Posn_uom_code,
1200 reln.parent_relationship_id,
1201 reln.position_ref_code,
1202 csi.INVENTORY_ITEM_ID,
1203 csi.QUANTITY Inst_qty,
1204 csi.UNIT_OF_MEASURE Inst_uom_code,
1205 (select object_id from csi_ii_relationships
1206 where subject_id = c_instance_id
1207 and relationship_type_code = 'COMPONENT-OF'
1208 and trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1209 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))) parent_instance_id
1210 FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
1211 WHERE csi.INSTANCE_ID = c_instance_id
1212 AND reln.relationship_id = c_mc_relationship_id
1213 AND iasso.item_group_id = reln.item_group_id
1214 AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
1215 AND (iasso.revision IS NULL OR iasso.revision = CSI.INVENTORY_REVISION)
1216 AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1217 AND trunc(nvl(reln.active_start_date, sysdate)) <= trunc(sysdate)
1218 AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate);
1219
1220 l_pos_dtls_rec get_pos_dtls_csr%ROWTYPE;
1221
1222 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.Validate_Position_Quantities';
1223 L_API_VERSION CONSTANT NUMBER := 1.0;
1224 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Position_Quantities';
1225 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1226 l_parent_rule_tbl T_ID_TBL;
1227 l_top_instance_id NUMBER;
1228 l_csi_ii_id NUMBER;
1229 l_rel_id NUMBER;
1230 l_rule_exists_flag VARCHAR2(1);
1231 l_quantity NUMBER;
1232
1233 BEGIN
1234
1235 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1236 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin',
1237 'At the start of the procedure, p_csi_instance_id = ' || p_csi_instance_id ||
1238 ', p_uc_header_id = ' || p_uc_header_id ||
1239 ', p_x_error_table.COUNT = ' || p_x_error_table.COUNT);
1240 END IF;
1241 -- Standard call to check for call compatibility
1242 IF NOT FND_API.Compatible_API_Call(L_API_VERSION, p_api_version, L_API_NAME,
1243 G_PKG_NAME) THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246
1247 -- Initialize message list if p_init_msg_list is set to TRUE
1248 IF FND_API.To_Boolean(p_init_msg_list) THEN
1249 FND_MSG_PUB.Initialize;
1250 END IF;
1251
1252 -- Initialize API return status to success
1253 x_return_status := FND_API.G_RET_STS_SUCCESS;
1254
1255 -- Begin Processing
1256 x_evaluation_status := FND_API.G_TRUE;
1257
1258 -- Call procedure to validate the uc header id
1259 IF (p_csi_instance_id IS NOT NULL AND p_uc_header_id IS NULL) THEN
1260 l_top_instance_id := p_csi_instance_id;
1261 ELSE
1262 OPEN get_uc_header_rec_csr;
1263 FETCH get_uc_header_rec_csr INTO l_top_instance_id;
1264 IF (get_uc_header_rec_csr%NOTFOUND) THEN
1265 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1266 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
1267 FND_MSG_PUB.add;
1268 CLOSE get_uc_header_rec_csr;
1269 RAISE FND_API.G_EXC_ERROR;
1270 END IF;
1271 CLOSE get_uc_header_rec_csr;
1272 END IF;
1273
1274 OPEN get_unit_tree_csr(l_top_instance_id);
1275 LOOP
1276 FETCH get_unit_tree_csr into l_csi_ii_id;
1277 EXIT WHEN get_unit_tree_csr%NOTFOUND;
1278
1279 l_rel_id := NULL;
1280 OPEN get_pos_ref_reln_csr(l_csi_ii_id);
1281 FETCH get_pos_ref_reln_csr INTO l_rel_id;
1282 CLOSE get_pos_ref_reln_csr;
1283
1284 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1285 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1286 'Checking Quantity for Instance ' || l_csi_ii_id ||
1287 ', l_rel_id = ' || l_rel_id);
1288 END IF;
1289 IF (l_rel_id IS NOT NULL) THEN
1290 OPEN get_pos_dtls_csr(c_mc_relationship_id => l_rel_id,
1291 c_instance_id => l_csi_ii_id);
1292 FETCH get_pos_dtls_csr INTO l_pos_dtls_rec;
1293 CLOSE get_pos_dtls_csr;
1294
1295 IF (l_pos_dtls_rec.Itm_qty IS NULL OR l_pos_dtls_rec.Itm_qty = 0) THEN
1296 -- Pick the Quantity and UOM from Position level.
1297 l_pos_dtls_rec.Itm_qty := l_pos_dtls_rec.Posn_qty;
1298 l_pos_dtls_rec.Itm_uom_code := l_pos_dtls_rec.Posn_uom_code;
1299 END IF;
1300
1301 IF (l_pos_dtls_rec.Itm_uom_code <> l_pos_dtls_rec.Inst_uom_code) THEN
1302 -- UOMs are different: Convert Item UOM Qty to Inst UOM Qty
1303 l_quantity := inv_convert.inv_um_convert(item_id => l_pos_dtls_rec.INVENTORY_ITEM_ID,
1304 precision => 6,
1305 from_quantity => l_pos_dtls_rec.Itm_qty,
1306 from_unit => l_pos_dtls_rec.Itm_uom_code,
1307 to_unit => l_pos_dtls_rec.Inst_uom_code,
1308 from_name => NULL,
1309 to_name => NULL);
1310 l_pos_dtls_rec.Itm_qty := l_quantity;
1311 l_pos_dtls_rec.Itm_uom_code := l_pos_dtls_rec.Inst_uom_code;
1312 END IF;
1313
1314 -- Now Compare and Validate the Quantities
1315 IF (l_pos_dtls_rec.Inst_qty = l_pos_dtls_rec.Itm_qty) THEN
1316 -- Quantity matches: Don't raise error
1317 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1318 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1319 'Quantities Match: ' || l_pos_dtls_rec.Itm_qty);
1320 END IF;
1321 ELSIF (l_pos_dtls_rec.Inst_qty > l_pos_dtls_rec.Itm_qty) THEN
1322 -- Instance Quantity can never be greater the Position quantity: Throw error
1323 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1325 'Instance Qty (' || l_pos_dtls_rec.Inst_qty || ') > Position Quantity (' || l_pos_dtls_rec.Itm_qty || ')');
1326 END IF;
1327 Add_Qty_Message(p_position_ref_code => l_pos_dtls_rec.position_ref_code,
1328 p_inst_qty => l_pos_dtls_rec.Inst_qty,
1329 p_qty_less_flag => 'N', -- Quantity is more
1330 p_x_error_table => p_x_error_table);
1331 x_evaluation_status := FND_API.G_FALSE;
1332
1333 ELSE
1334 -- Instance Quantity is less than Position Quantity
1335 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1336 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1337 'Need to check for rules: Instance Qty (' || l_pos_dtls_rec.Inst_qty || ') < Position Quantity (' || l_pos_dtls_rec.Itm_qty || ')');
1338 END IF;
1339
1340 -- Check l_parent_rule_tbl to see if an entry exists for the index l_pos_dtls_rec.parent_relationship_id
1341 IF (NOT (l_parent_rule_tbl.EXISTS(l_pos_dtls_rec.parent_instance_id))) THEN
1342 -- No entry exists in l_parent_rule_tbl
1343 l_rule_exists_flag := Quantity_Rule_Exists(p_instance_id => l_pos_dtls_rec.parent_instance_id);
1344 IF (l_rule_exists_flag = 'Y') THEN
1345 l_parent_rule_tbl(l_pos_dtls_rec.parent_instance_id) := 1;
1346 -- Ignore the quantity shortage.
1347 ELSE
1348 l_parent_rule_tbl(l_pos_dtls_rec.parent_instance_id) := -1;
1349 END IF;
1350 END IF; -- NOT of l_parent_rule_tbl.EXISTS
1351
1352 -- Raise error only if there is no Quantity Rule at Parent position
1353 IF (l_parent_rule_tbl(l_pos_dtls_rec.parent_instance_id) > 0) THEN
1354 -- A 'Quantity type' rule exists at the parent position: Ignore the quantity shortage.
1355 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1356 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1357 'Quantity Rule Exists. So not throwing error.');
1358 END IF;
1359 ELSE
1360 -- No 'Quantity type' rule exists at the parent position. So raise a validation error
1361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1363 'Quantity Rule Does not Exist. So throwing error.');
1364 END IF;
1365 Add_Qty_Message(p_position_ref_code => l_pos_dtls_rec.position_ref_code,
1366 p_inst_qty => l_pos_dtls_rec.Inst_qty,
1367 p_qty_less_flag => 'Y', -- Quantity is less
1368 p_x_error_table => p_x_error_table);
1369 x_evaluation_status := FND_API.G_FALSE;
1370 END IF; -- l_parent_rule_tbl entry is +ve or -ve
1371 END IF; -- Inst_qty checks
1372 END IF; -- l_rel_id IS NOT NULL
1373 END LOOP; -- All leaf non-extra nodes
1374 CLOSE get_unit_tree_csr;
1375
1376 -- Completed Processing
1377
1378 -- Check Error Message stack.
1379 x_msg_count := FND_MSG_PUB.count_msg;
1380 IF x_msg_count > 0 THEN
1381 RAISE FND_API.G_EXC_ERROR;
1382 END IF;
1383
1384 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1385 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
1386 'At the end of the procedure, About to return x_evaluation_status as ' || x_evaluation_status ||
1387 ', p_x_error_table.COUNT = ' || p_x_error_table.COUNT);
1388 END IF;
1389
1390 -- Standard call to get message count and if count is 1, get message info
1391 FND_MSG_PUB.Count_And_Get
1392 ( p_count => x_msg_count,
1393 p_data => x_msg_data,
1394 p_encoded => fnd_api.g_false
1395 );
1396
1397 EXCEPTION
1398 WHEN FND_API.G_EXC_ERROR THEN
1399 x_return_status := FND_API.G_RET_STS_ERROR;
1400 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1401 p_data => x_msg_data,
1402 p_encoded => fnd_api.g_false);
1403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1406 p_data => x_msg_data,
1407 p_encoded => fnd_api.g_false);
1408 WHEN OTHERS THEN
1409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1410 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
1411 p_procedure_name => l_api_name,
1412 p_error_text => SQLERRM);
1413
1414 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1415 p_data => x_msg_data,
1416 p_encoded => fnd_api.g_false);
1417 END Validate_Position_Quantities;
1418
1419 --------------------------------------------------------------------------------------------
1420 -- Start of Comments --
1421 --
1422 -- Procedure name : Check_Position_Quantities
1423 -- Type : Private
1424 -- Function : This procedure was added for the FP OGMA Issue 105 to support Non Serialized Items.
1425 -- It checks the instance quantity against the position/item group.
1426 -- If there is a Quantity type rule at the Parent position, the floor check
1427 -- is not done (Only ceiling check is done). Since in this case the Rule
1428 -- overrides and obviates any need for Quantity check and checking quantity
1429 -- based on position may actually contradict the rule.
1430 --
1431 -- Pre-reqs
1432 --
1433 -- Standard IN Parameters :
1434 -- p_api_version IN NUMBER Required
1435 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
1436 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1437 -- Standard OUT Parameters :
1438 -- x_return_status OUT VARCHAR2 Required
1439 -- x_msg_count OUT NUMBER Required
1440 -- x_msg_data OUT VARCHAR2 Required
1441 --
1442 -- Check_Position_Quantities Parameters :
1443 -- p_uc_header_id IN NUMBER Required
1444 -- The header id of the unit configuration.
1445 -- x_evaluation_status OUT VARCHAR2 The OUT flag which indicates whether the unit has any Quantity mismatch.
1446 --
1447 -- History:
1448 -- 05-Dec-2007 SATHAPLI Created
1449 --
1450 -- Version:
1451 -- Initial Version 1.0
1452 --
1453 -- End of Comments.
1454 --------------------------------------------------------------------------------------------
1455
1456 PROCEDURE Check_Position_Quantities(
1457 p_api_version IN NUMBER,
1458 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1459 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1460 x_return_status OUT NOCOPY VARCHAR2,
1461 x_msg_count OUT NOCOPY NUMBER,
1462 x_msg_data OUT NOCOPY VARCHAR2,
1463 p_uc_header_id IN NUMBER,
1464 x_evaluation_status OUT NOCOPY VARCHAR2) IS
1465
1466 -- Validate the UC and get the top instance
1467 CURSOR get_uc_header_rec_csr IS
1468 SELECT csi_item_instance_id
1469 FROM ahl_unit_config_headers
1470 WHERE unit_config_header_id = p_uc_header_id
1471 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1472 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
1473
1474 -- Get all leaf non-extra nodes and do not get sub-config root nodes, as these do not need the position quantity validation.
1475 -- Also such instances are inflection points for which get_pos_dtls_csr will not return any records.
1476 CURSOR get_unit_tree_csr(c_csi_instance_id IN NUMBER) IS
1477 SELECT c_csi_instance_id FROM dual
1478 WHERE NOT EXISTS (SELECT 1
1479 FROM csi_ii_relationships
1480 WHERE object_id = c_csi_instance_id
1481 AND relationship_type_code = 'COMPONENT-OF'
1482 AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
1483 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1484 UNION ALL
1485 SELECT 1
1486 FROM ahl_unit_config_headers
1487 WHERE csi_item_instance_id = c_csi_instance_id
1488 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1489 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
1490 UNION ALL
1491 SELECT OUTER.subject_id
1492 FROM csi_ii_relationships OUTER
1493 WHERE NOT EXISTS (SELECT 1
1494 FROM csi_ii_relationships
1495 WHERE object_id = OUTER.subject_id
1496 AND relationship_type_code = 'COMPONENT-OF'
1497 AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
1498 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1499 UNION ALL
1500 SELECT 1
1501 FROM ahl_unit_config_headers
1502 WHERE csi_item_instance_id = OUTER.subject_id
1503 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1504 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
1505 START WITH object_id = c_csi_instance_id
1506 AND relationship_type_code = 'COMPONENT-OF'
1507 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
1508 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1509 AND position_reference is not null
1510 CONNECT BY PRIOR subject_id = object_id
1511 AND relationship_type_code = 'COMPONENT-OF'
1512 AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
1513 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1514 AND position_reference is not null;
1515
1516 -- Get the Relationship of the instance
1517 CURSOR get_pos_ref_reln_csr(c_csi_instance_id IN NUMBER) IS
1518 SELECT TO_NUMBER(position_reference)
1519 FROM csi_ii_relationships csi
1520 WHERE position_reference is not null
1521 AND subject_id = c_csi_instance_id
1522 AND relationship_type_code = 'COMPONENT-OF'
1523 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1524 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
1525
1526 -- Get the Position Quantity Details
1527 CURSOR get_pos_dtls_csr(c_mc_relationship_id IN NUMBER,
1528 c_instance_id IN NUMBER) IS
1529 SELECT iasso.quantity Itm_qty,
1530 iasso.uom_code Itm_uom_code,
1531 iasso.revision Itm_revision,
1532 iasso.item_association_id,
1533 reln.quantity Posn_qty,
1534 reln.uom_code Posn_uom_code,
1535 reln.parent_relationship_id,
1536 reln.position_ref_code,
1537 csi.INVENTORY_ITEM_ID,
1538 csi.QUANTITY Inst_qty,
1539 csi.UNIT_OF_MEASURE Inst_uom_code,
1540 (select object_id from csi_ii_relationships
1541 where subject_id = c_instance_id
1542 and relationship_type_code = 'COMPONENT-OF'
1543 and trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1544 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))) parent_instance_id
1545 FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
1546 WHERE csi.INSTANCE_ID = c_instance_id
1547 AND reln.relationship_id = c_mc_relationship_id
1548 AND iasso.item_group_id = reln.item_group_id
1549 AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
1550 AND (iasso.revision IS NULL OR iasso.revision = CSI.INVENTORY_REVISION)
1551 AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1552 AND trunc(nvl(reln.active_start_date, sysdate)) <= trunc(sysdate)
1553 AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate);
1554
1555 l_pos_dtls_rec get_pos_dtls_csr%ROWTYPE;
1556
1557 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.Check_Position_Quantities';
1558 L_API_VERSION CONSTANT NUMBER := 1.0;
1559 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Position_Quantities';
1560 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1561 l_parent_rule_tbl T_ID_TBL;
1562 l_top_instance_id NUMBER;
1563 l_csi_ii_id NUMBER;
1564 l_rel_id NUMBER;
1565 l_rule_exists_flag VARCHAR2(1);
1566 l_quantity NUMBER;
1567
1568 BEGIN
1569
1570 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1571 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin',
1572 'At the start of the procedure, p_uc_header_id = ' || p_uc_header_id);
1573 END IF;
1574 -- Standard call to check for call compatibility
1575 IF NOT FND_API.Compatible_API_Call(L_API_VERSION, p_api_version, L_API_NAME,
1576 G_PKG_NAME) THEN
1577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1578 END IF;
1579
1580 -- Initialize message list if p_init_msg_list is set to TRUE
1581 IF FND_API.To_Boolean(p_init_msg_list) THEN
1582 FND_MSG_PUB.Initialize;
1583 END IF;
1584
1585 -- Initialize API return status to success
1586 x_return_status := FND_API.G_RET_STS_SUCCESS;
1587
1588 -- Begin Processing
1589 x_evaluation_status := FND_API.G_TRUE;
1590
1591 -- Call procedure to validate the uc header id
1592 OPEN get_uc_header_rec_csr;
1593 FETCH get_uc_header_rec_csr INTO l_top_instance_id;
1594 IF (get_uc_header_rec_csr%NOTFOUND) THEN
1595 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1596 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
1597 FND_MSG_PUB.add;
1598 CLOSE get_uc_header_rec_csr;
1599 RAISE FND_API.G_EXC_ERROR;
1600 END IF;
1601 CLOSE get_uc_header_rec_csr;
1602
1603 OPEN get_unit_tree_csr(l_top_instance_id);
1604 LOOP
1605 FETCH get_unit_tree_csr into l_csi_ii_id;
1606 EXIT WHEN get_unit_tree_csr%NOTFOUND;
1607
1608 l_rel_id := NULL;
1609 OPEN get_pos_ref_reln_csr(l_csi_ii_id);
1610 FETCH get_pos_ref_reln_csr INTO l_rel_id;
1611 CLOSE get_pos_ref_reln_csr;
1612
1613 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1614 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1615 'Checking Quantity for Instance ' || l_csi_ii_id ||
1616 ', l_rel_id = ' || l_rel_id);
1617 END IF;
1618 IF (l_rel_id IS NOT NULL) THEN
1619 OPEN get_pos_dtls_csr(c_mc_relationship_id => l_rel_id,
1620 c_instance_id => l_csi_ii_id);
1621 FETCH get_pos_dtls_csr INTO l_pos_dtls_rec;
1622 CLOSE get_pos_dtls_csr;
1623
1624 IF (l_pos_dtls_rec.Itm_qty IS NULL OR l_pos_dtls_rec.Itm_qty = 0) THEN
1625 -- Pick the Quantity and UOM from Position level.
1626 l_pos_dtls_rec.Itm_qty := l_pos_dtls_rec.Posn_qty;
1627 l_pos_dtls_rec.Itm_uom_code := l_pos_dtls_rec.Posn_uom_code;
1628 END IF;
1629
1630 IF (l_pos_dtls_rec.Itm_uom_code <> l_pos_dtls_rec.Inst_uom_code) THEN
1631 -- UOMs are different: Convert Item UOM Qty to Inst UOM Qty
1632 l_quantity := inv_convert.inv_um_convert(item_id => l_pos_dtls_rec.INVENTORY_ITEM_ID,
1633 precision => 6,
1634 from_quantity => l_pos_dtls_rec.Itm_qty,
1635 from_unit => l_pos_dtls_rec.Itm_uom_code,
1636 to_unit => l_pos_dtls_rec.Inst_uom_code,
1637 from_name => NULL,
1638 to_name => NULL);
1639 l_pos_dtls_rec.Itm_qty := l_quantity;
1640 l_pos_dtls_rec.Itm_uom_code := l_pos_dtls_rec.Inst_uom_code;
1641 END IF;
1642
1643 -- Now Compare and Validate the Quantities
1644 IF (l_pos_dtls_rec.Inst_qty = l_pos_dtls_rec.Itm_qty) THEN
1645 -- Quantity matches: Don't raise error
1646 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1647 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1648 'Quantities Match: ' || l_pos_dtls_rec.Itm_qty);
1649 END IF;
1650 ELSIF (l_pos_dtls_rec.Inst_qty > l_pos_dtls_rec.Itm_qty) THEN
1651 -- Instance Quantity can never be greater the Position quantity: Return error
1652 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1654 'Exiting Early: Instance Qty (' || l_pos_dtls_rec.Inst_qty || ') > Position Quantity (' || l_pos_dtls_rec.Itm_qty || ')');
1655 END IF;
1656 x_evaluation_status := FND_API.G_FALSE;
1657 EXIT; -- No need to process remaining instances
1658 ELSE
1659 -- Instance Quantity is less than Position Quantity
1660 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1661 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1662 'Need to check for rules: Instance Qty (' || l_pos_dtls_rec.Inst_qty || ') < Position Quantity (' || l_pos_dtls_rec.Itm_qty || ')');
1663 END IF;
1664 -- Raise error only if there is no Quantity Rule at Parent position
1665 -- Check l_parent_rule_tbl to see if an entry exists for the index l_pos_dtls_rec.parent_relationship_id
1666 IF (NOT(l_parent_rule_tbl.EXISTS(l_pos_dtls_rec.parent_instance_id))) THEN
1667 -- No entry exists in l_parent_rule_tbl
1668 l_rule_exists_flag := Quantity_Rule_Exists(p_instance_id => l_pos_dtls_rec.parent_instance_id);
1669 IF (l_rule_exists_flag = 'Y') THEN
1670 l_parent_rule_tbl(l_pos_dtls_rec.parent_instance_id) := 1;
1671 ELSE
1672 l_parent_rule_tbl(l_pos_dtls_rec.parent_instance_id) := -1;
1673 END IF;
1674 END IF; -- NOT of l_parent_rule_tbl entry exists
1675
1676 IF (l_parent_rule_tbl(l_pos_dtls_rec.parent_instance_id) > 0) THEN
1677 -- A 'Quantity type' rule exists at the parent position: Ignore the quantity shortage.
1678 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1679 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1680 'Quantity Rule Exists. So not throwing error.');
1681 END IF;
1682 ELSE
1683 -- No 'Quantity type' rule exists at the parent position. So return a validation error
1684 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1685 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1686 'Early Exit: Quantity Rule Does not Exist. So throwing error.');
1687 END IF;
1688 x_evaluation_status := FND_API.G_FALSE;
1689 EXIT; -- No need to process remaining instances
1690 END IF; -- l_parent_rule_tbl entry is +ve or -ve
1691 END IF; -- Inst_qty checks
1692 END IF; -- l_rel_id IS NOT NULL
1693 END LOOP; -- All leaf non-extra nodes
1694 CLOSE get_unit_tree_csr;
1695
1696 -- Completed Processing
1697
1698 -- Check Error Message stack.
1699 x_msg_count := FND_MSG_PUB.count_msg;
1700 IF x_msg_count > 0 THEN
1701 RAISE FND_API.G_EXC_ERROR;
1702 END IF;
1703
1704 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1705 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
1706 'At the end of the procedure, About to return x_evaluation_status as ' || x_evaluation_status);
1707 END IF;
1708
1709 -- Standard call to get message count and if count is 1, get message info
1710 FND_MSG_PUB.Count_And_Get
1711 ( p_count => x_msg_count,
1712 p_data => x_msg_data,
1713 p_encoded => fnd_api.g_false
1714 );
1715
1716 EXCEPTION
1717 WHEN FND_API.G_EXC_ERROR THEN
1718 x_return_status := FND_API.G_RET_STS_ERROR;
1719 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1720 p_data => x_msg_data,
1721 p_encoded => fnd_api.g_false);
1722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1725 p_data => x_msg_data,
1726 p_encoded => fnd_api.g_false);
1727 WHEN OTHERS THEN
1728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1729 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1730 p_procedure_name => l_api_name,
1731 p_error_text => SQLERRM);
1732
1733 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1734 p_data => x_msg_data,
1735 p_encoded => fnd_api.g_false);
1736 END Check_Position_Quantities;
1737
1738 END AHL_UC_POS_NECES_PVT;
[Home] [Help]