1 PACKAGE BODY AHL_MC_PATH_POSITION_PVT AS
2 /* $Header: AHLVPOSB.pls 120.5 2008/01/29 14:12:16 sathapli ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ahl_MC_Path_Position_Pvt';
4
5 --Generic separators for the fields.
6 G_ID_SEPARATOR CONSTANT VARCHAR2(1) := ':';
7 G_NODE_SEPARATOR CONSTANT VARCHAR2(1) := '/';
8
9
10
11 --------------------------------
12 -- Start of Comments --
13 -- Procedure name : Create_Position_ID
14 -- Type : Private
15 -- Function : API to create the new path position or if matches
16 -- existing one, return the existing path_position_id
17 -- Pre-reqs :
18 -- Parameters :
19 --
20 -- Create_Position_ID Parameters:
21 -- p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type Required
22 -- p_pos_ref_meaning IN VARCHAR2 Optional. Position ref for the path
23 --
24 -- End of Comments.
25
26 PROCEDURE Create_Position_ID (
27 p_api_version IN NUMBER,
28 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
29 p_commit IN VARCHAR2 := FND_API.G_FALSE,
30 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2,
34 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type,
35 p_position_ref_meaning IN VARCHAR2,
36 p_position_ref_code IN VARCHAR2,
37 x_position_id OUT NOCOPY NUMBER)
38 IS
39 --
40 --Check that all 3 ids are valid.
41 CURSOR check_mc_ids_csr (p_mc_id IN NUMBER,
42 p_ver_num IN NUMBER,
43 p_pos_key IN NUMBER) IS
44 SELECT 'X'
45 FROM ahl_mc_headers_b hd, ahl_mc_relationships rel
46 WHERE hd.mc_header_id = rel.mc_header_id
47 AND hd.mc_id = p_mc_id
48 AND hd.version_number = nvl(p_ver_num, hd.version_number)
49 AND rel.position_key = p_pos_key;
50 --
51 --Check that the config/subconfig mapping is valid.
52 CURSOR check_mc_relationships_csr (p_mc_id IN NUMBER,
53 p_ver_num IN NUMBER,
54 p_pos_key IN NUMBER,
55 p_child_mc_id IN NUMBER,
56 p_child_ver_num IN NUMBER) IS
57 SELECT 'X'
58 FROM ahl_mc_config_relations rel, ahl_mc_headers_b hd
59 WHERE rel.mc_header_id = hd.mc_header_id
60 AND hd.mc_id = p_child_mc_id
61 AND hd.version_number = nvl(p_child_ver_num, hd.version_number)
62 AND rel.relationship_id IN
63 (SELECT r.relationship_id
64 FROM ahl_mc_relationships r, ahl_mc_headers_b h
65 WHERE h.mc_header_id = r.mc_header_id
66 AND h.mc_id = p_mc_id
67 AND h.version_number = nvl(p_ver_num, h.version_number)
68 AND r.position_key = p_pos_key);
69 --
70 --Check the encoded path position csr
71 CURSOR get_position_id_csr (p_encoded_path IN VARCHAR2) IS
72 SELECT pos.path_position_id
73 FROM ahl_mc_path_positions pos
74 WHERE pos.encoded_path_position = p_encoded_path;
75 --
76 CURSOR get_sibling_poskey_csr(p_mc_id IN NUMBER, p_poskey IN NUMBER) IS
77 SELECT distinct r2.position_key
78 FROM ahl_mc_relationships r1, ahl_mc_relationships r2, ahl_mc_headers_b hdr
79 WHERE r1.parent_relationship_id = r2.parent_relationship_id
80 AND r1.position_key <> r2.position_key
81 AND r1.position_key = p_poskey
82 AND r1.mc_header_id = hdr.mc_header_id
83 AND hdr.mc_id = p_mc_id;
84 --
85 CURSOR get_pos_common_id_csr (p_encoded_path IN VARCHAR2, p_size IN NUMBER) IS
86 SELECT pos.path_pos_common_id
87 FROM AHL_MC_PATH_POSITIONS pos
88 WHERE pos.encoded_path_position like p_encoded_path
89 AND p_size = (select COUNT(path_position_node_id) from
90 AHL_MC_PATH_POSITION_NODES where path_position_id = pos.path_position_id);
91 --
92 CURSOR get_next_path_pos_id_csr IS
93 SELECT ahl_mc_path_positions_s.nextval
94 FROM dual;
95 --
96 l_junk VARCHAR2(1);
97 l_api_version CONSTANT NUMBER := 1.0;
98 l_api_name CONSTANT VARCHAR2(30) := 'Create_Position_ID';
99 l_pos_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
100 l_position_id NUMBER;
101 l_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
102 l_position_ref_code VARCHAR2(30);
103 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
104 l_msg_count NUMBER;
105 l_return_val BOOLEAN;
106 l_index NUMBER;
107 l_sib_pos_ref_code VARCHAR2(30);
108 l_poskey NUMBER;
109 l_no_ver_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
110 l_common_id NUMBER;
111 l_ver_spec_score NUMBER;
112 l_count NUMBER;
113 --
114 BEGIN
115 -- Standard start of API savepoint
116 SAVEPOINT Create_Position_ID_pvt;
117
118 -- Initialize Procedure return status to success
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120 -- Standard call to check for call compatibility
121 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
122 G_PKG_NAME) THEN
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END IF;
125
126 -- Initialize message list if p_init_msg_list is set to TRUE
127 IF FND_API.To_Boolean(p_init_msg_list) THEN
128 FND_MSG_PUB.Initialize;
129 END IF;
130
131 --initialize ver spec score;
132 l_ver_spec_score := 0;
133 l_count :=0;
134
135 --Do id validations
136 FOR i IN p_path_position_tbl.FIRST..p_path_position_tbl.LAST LOOP
137 l_pos_rec := p_path_position_tbl(i);
138 l_path_tbl(i) := l_pos_rec;
139 l_no_ver_path_tbl(i) := l_pos_rec;
140 l_no_ver_path_tbl(i).version_number := NULL;
141 l_count := l_count +1;
142
143 OPEN check_mc_ids_csr(l_pos_rec.mc_id, l_pos_rec.version_number,
144 l_pos_rec.position_key);
145 FETCH check_mc_ids_csr INTO l_junk;
146 IF (check_mc_ids_csr%NOTFOUND) THEN
147 FND_MESSAGE.Set_Name('AHL','AHL_MC_PATH_NODE_INV');
148 FND_MESSAGE.Set_Token('MC_ID',l_pos_rec.mc_id);
149 FND_MESSAGE.Set_Token('VER',l_pos_rec.version_number);
150 FND_MESSAGE.Set_Token('POS_KEY',l_pos_rec.position_key);
151 FND_MSG_PUB.ADD;
152 END IF;
153 CLOSE check_mc_ids_csr;
154
155 IF (l_pos_rec.version_number IS NOT NULL AND
156 l_count <= p_path_position_tbl.COUNT ) THEN
157 l_ver_spec_score := l_ver_spec_score + POWER(2,(p_path_position_tbl.COUNT-l_count));
158 END IF;
159
160 IF (i< p_path_position_tbl.LAST) THEN
161 OPEN check_mc_relationships_csr(l_pos_rec.mc_id,
162 l_pos_rec.version_number,
163 l_pos_rec.position_key,
164 p_path_position_tbl(i+1).mc_id,
165 p_path_position_tbl(i+1).version_number);
166 FETCH check_mc_relationships_csr INTO l_junk;
167 IF (check_mc_relationships_csr%NOTFOUND) THEN
168 FND_MESSAGE.Set_Name('AHL','AHL_MC_PATH_RELNSHIP_INV');
169 FND_MESSAGE.Set_Token('MC_ID',p_path_position_tbl(i+1).mc_id);
170 FND_MESSAGE.Set_Token('VER',p_path_position_tbl(i+1).version_number);
171 FND_MESSAGE.Set_Token('POS_KEY',l_pos_rec.position_key);
172 FND_MSG_PUB.ADD;
173 END IF;
174 CLOSE check_mc_relationships_csr;
175 END IF;
176
177 END LOOP;
178
179 --Convert the position ref meaning only if position ref code is undefined.
180 IF (p_position_ref_code = FND_API.G_MISS_CHAR) THEN
181 IF (p_position_ref_meaning = FND_API.G_MISS_CHAR) THEN
182 l_position_ref_code := NULL;
183 ELSIF (p_position_ref_meaning IS NULL) THEN
184 l_position_ref_code := NULL;
185 ELSIF (p_position_ref_meaning <> FND_API.G_MISS_CHAR) THEN
186 AHL_UTIL_MC_PKG.Convert_To_LookupCode('AHL_POSITION_REFERENCE',
187 p_position_ref_meaning,
188 l_position_ref_code,
189 l_return_val);
190 IF NOT(l_return_val) THEN
191 FND_MESSAGE.Set_Name('AHL','AHL_MC_POSREF_INVALID');
192 FND_MESSAGE.Set_Token('POSREF',p_position_ref_meaning);
193 FND_MSG_PUB.ADD;
194 END IF;
195 END IF;
196 ELSE
197 l_position_ref_code := p_position_ref_code;
198 END IF;
199
200 -- Check Error Message stack.
201 l_msg_count := FND_MSG_PUB.count_msg;
202 IF l_msg_count > 0 THEN
203 RAISE FND_API.G_EXC_ERROR;
204 END IF;
205
206 --Convert to find all same level paths
207 IF (l_position_ref_code <> FND_API.G_MISS_CHAR AND
208 l_position_ref_code IS NOT NULL) THEN
209 l_poskey := l_path_tbl(l_path_tbl.LAST).position_key;
210 OPEN get_sibling_poskey_csr(l_path_tbl(l_path_tbl.LAST).mc_id,
211 l_poskey);
212 LOOP
213 FETCH get_sibling_poskey_csr INTO l_path_tbl(l_path_tbl.LAST).position_key;
214 EXIT WHEN get_sibling_poskey_csr%NOTFOUND;
215 l_sib_pos_ref_code := get_posref_by_path(l_path_tbl, FND_API.G_TRUE);
216 IF (l_sib_pos_ref_code = l_position_ref_code) THEN
217 FND_MESSAGE.Set_Name('AHL','AHL_MC_POSREF_DUPLICATE');
218 FND_MESSAGE.Set_Token('POSREF',l_position_ref_code);
219 FND_MSG_PUB.ADD;
220 END IF;
221 END LOOP;
222 CLOSE get_sibling_poskey_csr;
223 END IF;
224
225 -- Check Error Message stack.
226 l_msg_count := FND_MSG_PUB.count_msg;
227 IF l_msg_count > 0 THEN
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230
231 --Encode the path_position_tbl
232 l_encoded_path := Encode(p_path_position_tbl);
233 l_index :=0;
234
235
236 --Compare and find matching path_position
237 OPEN get_position_id_csr(l_encoded_path);
238 FETCH get_position_id_csr INTO x_position_id;
239 IF (get_position_id_csr%FOUND) THEN
240 IF (l_position_ref_code <> FND_API.G_MISS_CHAR OR
241 l_position_ref_code IS NULL) THEN
242 UPDATE ahl_mc_path_positions SET
243 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
244 LAST_UPDATE_DATE = sysdate,
245 LAST_UPDATED_BY = fnd_global.USER_ID,
246 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
247 POSITION_REF_CODE = l_position_ref_code
248 WHERE PATH_POSITION_ID = x_position_id;
249 END IF;
250 ELSE
251
252 OPEN get_next_path_pos_id_csr;
253 FETCH get_next_path_pos_id_csr INTO l_position_id;
254 CLOSE get_next_path_pos_id_csr;
255
256 --Determine the path_position_common_id
257 OPEN get_pos_common_id_csr(Encode(l_no_ver_path_tbl),
258 p_path_position_tbl.COUNT);
259 FETCH get_pos_common_id_csr INTO l_common_id;
260 --If not found, generate new common id
261 IF (get_pos_common_id_csr%NOTFOUND OR l_common_id IS NULL) THEN
262 l_common_id := l_position_id;
263 END IF;
264 CLOSE get_pos_common_id_csr;
265
266 --Do inserts
267 INSERT INTO ahl_mc_path_positions(
268 PATH_POSITION_ID,
269 PATH_POS_COMMON_ID,
270 OBJECT_VERSION_NUMBER,
271 LAST_UPDATE_DATE,
272 LAST_UPDATED_BY,
273 CREATION_DATE,
274 CREATED_BY,
275 LAST_UPDATE_LOGIN,
276 ENCODED_PATH_POSITION,
277 POSITION_REF_CODE,
278 VER_SPEC_SCORE,
279 ATTRIBUTE_CATEGORY,
280 ATTRIBUTE1,
281 ATTRIBUTE2,
282 ATTRIBUTE3,
283 ATTRIBUTE4,
284 ATTRIBUTE5,
285 ATTRIBUTE6,
286 ATTRIBUTE7,
287 ATTRIBUTE8,
288 ATTRIBUTE9,
289 ATTRIBUTE10,
290 ATTRIBUTE11,
291 ATTRIBUTE12,
292 ATTRIBUTE13,
293 ATTRIBUTE14,
294 ATTRIBUTE15
295 ) VALUES (
296 l_position_id,
297 l_common_id,
298 1,
299 sysdate,
300 fnd_global.user_id,
301 sysdate,
302 fnd_global.user_id,
303 fnd_global.login_id,
304 l_encoded_path,
305 l_position_ref_code,
306 l_ver_spec_score,
307 NULL,
308 NULL, NULL, NULL, NULL, NULL,
309 NULL, NULL, NULL, NULL, NULL,
310 NULL, NULL, NULL, NULL, NULL
311 )
312 RETURNING path_position_id INTO x_position_id;
313
314 --Insert the path position nodes
315 FOR i IN p_path_position_tbl.FIRST..p_path_position_tbl.LAST LOOP
316 INSERT INTO ahl_mc_path_position_nodes(
317 PATH_POSITION_NODE_ID,
318 OBJECT_VERSION_NUMBER,
319 LAST_UPDATE_DATE,
320 LAST_UPDATED_BY,
321 CREATION_DATE,
322 CREATED_BY,
323 LAST_UPDATE_LOGIN,
324 PATH_POSITION_ID,
325 SEQUENCE,
326 MC_ID,
327 VERSION_NUMBER,
328 POSITION_KEY,
329 ATTRIBUTE_CATEGORY,
330 ATTRIBUTE1,
331 ATTRIBUTE2,
332 ATTRIBUTE3,
333 ATTRIBUTE4,
334 ATTRIBUTE5,
335 ATTRIBUTE6,
336 ATTRIBUTE7,
337 ATTRIBUTE8,
338 ATTRIBUTE9,
339 ATTRIBUTE10,
340 ATTRIBUTE11,
341 ATTRIBUTE12, ATTRIBUTE13,
342 ATTRIBUTE14,
343 ATTRIBUTE15
344 ) VALUES (
345 ahl_mc_path_position_nodes_s.nextval,
346 1,
347 sysdate,
348 fnd_global.user_id,
349 sysdate,
350 fnd_global.user_id,
351 fnd_global.login_id,
352 x_position_id,
353 l_index,
354 p_path_position_tbl(i).mc_id,
355 p_path_position_tbl(i).version_number,
356 p_path_position_tbl(i).position_key,
357 p_path_position_tbl(i).attribute_category ,
358 p_path_position_tbl(i).attribute1 ,
359 p_path_position_tbl(i).attribute2 ,
360 p_path_position_tbl(i).attribute3 ,
361 p_path_position_tbl(i).attribute4 ,
362 p_path_position_tbl(i).attribute5 ,
363 p_path_position_tbl(i).attribute6 ,
364 p_path_position_tbl(i).attribute7 ,
365 p_path_position_tbl(i).attribute8 ,
366 p_path_position_tbl(i).attribute9 ,
367 p_path_position_tbl(i).attribute10 ,
368 p_path_position_tbl(i).attribute11 ,
369 p_path_position_tbl(i).attribute12 ,
370 p_path_position_tbl(i).attribute13 ,
371 p_path_position_tbl(i).attribute14 ,
372 p_path_position_tbl(i).attribute15 );
373 l_index := l_index +1;
374 END LOOP;
375 END IF;
376 CLOSE get_position_id_csr;
377
378 -- Standard check of p_commit
379 IF FND_API.TO_BOOLEAN(p_commit) THEN
380 COMMIT WORK;
381 END IF;
382 -- Standard call to get message count and if count is 1, get message info
383 FND_MSG_PUB.Count_And_Get
384 ( p_count => x_msg_count,
385 p_data => x_msg_data,
386 p_encoded => fnd_api.g_false
387 );
388 EXCEPTION
389 WHEN FND_API.G_EXC_ERROR THEN
390 Rollback to Create_Position_ID_pvt;
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
393 p_data => x_msg_data,
394 p_encoded => fnd_api.g_false);
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396 Rollback to Create_Position_ID_pvt;
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
399 p_data => x_msg_data,
400 p_encoded => fnd_api.g_false);
401 WHEN OTHERS THEN
402 Rollback to Create_Position_ID_pvt;
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
405 p_procedure_name => l_api_name,
406 p_error_text => SQLERRM);
407 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
408 p_data => x_msg_data,
409 p_encoded => fnd_api.g_false);
410
411 END Create_Position_ID;
412
413 -----------------------------
414 -- Start of Comments --
415 -- Procedure name : Map_Instance_To_Positions
416 -- Type : Private
417 -- Function : Writes a list of positions that maps to instance
418 -- into AHL_APPLICABLE_INSTANCES
419 -- Pre-reqs :
420 -- Parameters :
421 --
422 -- Map_Instance_To_Positions Parameters:
423 -- p_csi_item_instance_id IN NUMBER Required. instance for the path
424 --
425 -- End of Comments.
426
427 PROCEDURE Map_Instance_To_Positions (
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
430 p_commit IN VARCHAR2 := FND_API.G_FALSE,
431 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2,
435 p_csi_item_instance_id IN NUMBER)
436 IS
437 --
438 --Fetch the unit and unit header info for instance
439 CURSOR get_uc_headers_csr (p_csi_instance_id IN NUMBER) IS
440 SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
441 FROM ahl_uc_header_paths_v up
442 WHERE up.csi_instance_id = p_csi_instance_id;
443 --
444 CURSOR get_unit_instance_csr (p_csi_instance_id IN NUMBER) IS
445 SELECT csi.object_id
446 FROM csi_ii_relationships csi
447 WHERE csi.object_id IN
448 ( SELECT csi_item_instance_id
449 FROM ahl_unit_config_headers
450 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
451 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
452 START WITH csi.subject_id = p_csi_instance_id
453 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
454 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
455 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
456 CONNECT BY csi.subject_id = PRIOR csi.object_id
457 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
458 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
459 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
460 AND CSI.POSITION_REFERENCE IS NOT NULL;
461 --
462 --Fetches lowest level info
463 CURSOR get_last_uc_rec_csr (p_csi_instance_id IN NUMBER) IS
464 SELECT hdr.mc_id, hdr.version_number, rel.position_key
465 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
466 csi_ii_relationships csi_ii
467 WHERE csi_ii.subject_id = p_csi_instance_id
468 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
469 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
470 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
471 AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
472 AND REL.mc_header_id = HDR.mc_header_id;
473 --
474 CURSOR get_top_unit_inst_csr (p_csi_instance_id IN NUMBER) IS
475 SELECT hdr.mc_id, hdr.version_number, rel.position_key
476 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
477 ahl_unit_config_headers uch, csi_unit_instances_v csi_u
478 WHERE uch.csi_item_instance_id = p_csi_instance_id
479 AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
480 AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
481 AND hdr.mc_header_id = uch.master_config_id
482 AND rel.mc_header_id = hdr.mc_header_id
483 AND rel.parent_relationship_id IS NULL
484 AND uch.csi_item_instance_id = csi_u.instance_id;
485
486 --
487 --Fetch all encoded path positions like the generated path
488 CURSOR get_matching_pos_csr (p_encoded_path IN VARCHAR2, p_size IN NUMBER) IS
489 SELECT pos.path_position_id
490 FROM AHL_MC_PATH_POSITIONS pos
491 WHERE p_encoded_path LIKE pos.encoded_path_position
492 AND p_size = (select COUNT(path_position_node_id) from
493 AHL_MC_PATH_POSITION_NODES where path_position_id = pos.path_position_id);
494 --
495 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
496 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
497 l_index NUMBER;
498 l_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
499 l_api_version CONSTANT NUMBER := 1.0;
500 l_api_name CONSTANT VARCHAR2(30) := 'Map_Instance_To_Positions';
501 l_position_id NUMBER;
502 l_unit_csi_id NUMBER;
503 --
504 BEGIN
505 -- Standard start of API savepoint
506 SAVEPOINT Map_Instance_To_Positions_pvt;
507
508 -- Initialize Procedure return status to success
509 x_return_status := FND_API.G_RET_STS_SUCCESS;
510 -- Standard call to check for call compatibility
511 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
512 G_PKG_NAME) THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 END IF;
515
516 -- Initialize message list if p_init_msg_list is set to TRUE
517 IF FND_API.To_Boolean(p_init_msg_list) THEN
518 FND_MSG_PUB.Initialize;
519 END IF;
520
521 --Fetch the position informations for the instance
522 OPEN get_last_uc_rec_csr(p_csi_item_instance_id);
523 FETCH get_last_uc_rec_csr INTO l_path_rec.mc_id,
524 l_path_rec.version_number,
525 l_path_rec.position_key;
526 IF (get_last_uc_rec_csr%NOTFOUND) THEN
527
528 --Fetch the position informations for the unit instance
529 OPEN get_top_unit_inst_csr(p_csi_item_instance_id);
530 FETCH get_top_unit_inst_csr INTO l_path_rec.mc_id,
531 l_path_rec.version_number,
532 l_path_rec.position_key;
533 --Check top node only
534 IF (get_top_unit_inst_csr%FOUND) THEN
535 --Sunil found the following line was missing and was added on 12/08/2004
536 l_path_tbl(1) := l_path_rec;
537 --Encode the path_position_tbl
538 l_encoded_path := Encode(l_path_tbl);
539
540 OPEN get_matching_pos_csr(l_encoded_path, l_path_tbl.COUNT);
541 LOOP
542 FETCH get_matching_pos_csr INTO l_position_id;
543 EXIT WHEN get_matching_pos_csr%NOTFOUND;
544 INSERT INTO AHL_APPLICABLE_INSTANCES (csi_item_instance_id,
545 position_id)
546 VALUES (p_csi_item_instance_id, l_position_id);
547 END LOOP;
548 CLOSE get_matching_pos_csr;
549
550 END IF;
551 CLOSE get_top_unit_inst_csr;
552
553 ELSE
554 l_path_tbl(1) := l_path_rec;
555
556 --Add positions matching lowest level
557 l_encoded_path := Encode(l_path_tbl);
558
559 OPEN get_matching_pos_csr(l_encoded_path, l_path_tbl.COUNT);
560 LOOP
561 FETCH get_matching_pos_csr INTO l_position_id;
562 EXIT WHEN get_matching_pos_csr%NOTFOUND;
563 INSERT INTO AHL_APPLICABLE_INSTANCES (csi_item_instance_id,
564 position_id)
565 VALUES (p_csi_item_instance_id, l_position_id);
566 END LOOP;
567 CLOSE get_matching_pos_csr;
568
569
570 --Now fetch the position paths which match at higher levels.
571 l_index := 0;
572 --Fetch the header rec info for the instance
573 OPEN get_unit_instance_csr(p_csi_item_instance_id);
574 LOOP
575 FETCH get_unit_instance_csr INTO l_unit_csi_id;
576 EXIT WHEN get_unit_instance_csr%NOTFOUND;
577
578 OPEN get_uc_headers_csr(l_unit_csi_id);
579 FETCH get_uc_headers_csr INTO l_path_rec.mc_id,
580 l_path_rec.version_number,
581 l_path_rec.position_key;
582 CLOSE get_uc_headers_csr;
583 IF (l_path_rec.mc_id is not null AND
584 l_path_rec.position_key is not null) THEN
585 l_path_tbl(l_index) := l_path_rec;
586 l_index := l_index - 1;
587
588 --Encode the modified with new params path_position_tbl
589 l_encoded_path := Encode(l_path_tbl);
590
591 --dbms_output.put_line (l_encoded_path);
592
593 OPEN get_matching_pos_csr(l_encoded_path, l_path_tbl.COUNT);
594 LOOP
595 FETCH get_matching_pos_csr INTO l_position_id;
596 EXIT WHEN get_matching_pos_csr%NOTFOUND;
597 INSERT INTO AHL_APPLICABLE_INSTANCES (csi_item_instance_id,
598 position_id)
599 VALUES (p_csi_item_instance_id, l_position_id);
600 END LOOP;
601 CLOSE get_matching_pos_csr;
602
603 END IF;
604 END LOOP;
605 CLOSE get_unit_instance_csr;
606
607 END IF;
608 CLOSE get_last_uc_rec_csr;
609
610 -- Standard call to get message count and if count is 1, get message info
611 FND_MSG_PUB.Count_And_Get
612 ( p_count => x_msg_count,
613 p_data => x_msg_data,
614 p_encoded => fnd_api.g_false
615 );
616 EXCEPTION
617 WHEN FND_API.G_EXC_ERROR THEN
618 Rollback to Map_Instance_To_Positions_pvt;
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
621 p_data => x_msg_data,
622 p_encoded => fnd_api.g_false);
623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
624 Rollback to Map_Instance_To_Positions_pvt;
625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
626 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
627 p_data => x_msg_data,
628 p_encoded => fnd_api.g_false);
629 WHEN OTHERS THEN
630 Rollback to Map_Instance_To_Positions_pvt;
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
632 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
633 p_procedure_name => l_api_name,
634 p_error_text => SQLERRM);
635 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
636 p_data => x_msg_data,
637 p_encoded => fnd_api.g_false);
638 END Map_Instance_To_Positions;
639
640
641 -----------------------------
642 -- Start of Comments --
643 -- Procedure name : Map_Position_To_Instance
644 -- Type : Private
645 -- Function : Writes a list of instances that maps to position path
646 --into AHL_APPLICABLE_INSTANCES
647 -- Pre-reqs :
648 -- Parameters :
649 --
650 -- Map_Position_To_Instances Parameters:
651 -- p_position_id IN NUMBER Required
652 --
653 -- End of Comments.
654
655 PROCEDURE Map_Position_To_Instances (
656 p_api_version IN NUMBER,
657 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
658 p_commit IN VARCHAR2 := FND_API.G_FALSE,
659 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
660 x_return_status OUT NOCOPY VARCHAR2,
661 x_msg_count OUT NOCOPY NUMBER,
662 x_msg_data OUT NOCOPY VARCHAR2,
663 p_position_id IN NUMBER)
664 IS
665 --
666 --Fetch all the position path information for given position_id
667 CURSOR get_position_path_csr (p_position_id IN NUMBER) IS
668 SELECT path.sequence, path.mc_id, path.version_number, path.position_key
669 FROM AHL_MC_PATH_POSITION_NODES path
670 WHERE path.path_position_id = p_position_id
671 order by sequence;
672 --
673 --Determine if position_key maps to top node of the configuration
674 CURSOR check_pos_key_top_csr(p_mc_id IN NUMBER,
675 p_ver_num IN NUMBER,
676 p_position_key IN NUMBER) IS
677 SELECT 'X'
678 FROM AHL_MC_RELATIONSHIPS rel, AHL_MC_HEADERS_B HDR
679 WHERE HDR.mc_header_id = REL.mc_header_id
680 AND REL.parent_relationship_id is NULL
681 AND REL.position_key = p_position_key
682 AND HDR.mc_id = p_mc_id
683 AND HDR.version_number = nvl(p_ver_num, HDR.version_number);
684
685 --
686
687 l_api_version CONSTANT NUMBER := 1.0;
688 l_api_name CONSTANT VARCHAR2(30) := 'map_position_to_instances';
689 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.' || g_pkg_name || '.' || l_api_name;
690 l_index NUMBER;
691 l_dummy VARCHAR2(1);
692 l_top_flag BOOLEAN;
693 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
694 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
695 v_CursorID NUMBER;
696 v_Stmt VARCHAR2(4000);
697 v_Select VARCHAR2(4000);
698 v_From VARCHAR2(4000);
699 v_Where VARCHAR2(4000);
700 v_RowsInserted INTEGER;
701
702 --
703 BEGIN
704
705 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
706 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
707 END IF;
708
709 -- Standard start of API savepoint
710 SAVEPOINT map_position_to_instances_pvt;
711
712 -- Initialize Procedure return status to success
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 -- Standard call to check for call compatibility
715 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
716 G_PKG_NAME) THEN
717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
718 END IF;
719
720 -- Initialize message list if p_init_msg_list is set to TRUE
721 IF FND_API.To_Boolean(p_init_msg_list) THEN
722 FND_MSG_PUB.Initialize;
723 END IF;
724
725 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
726 fnd_log.string(fnd_log.level_statement,l_full_name,'-- p_position_id --'||p_position_id);
727 fnd_log.string(fnd_log.level_statement,l_full_name,'--Populate position path info--');
728 END IF;
729
730 l_index :=0;
731 --Populate position path info starting at index =0
732 OPEN get_position_path_csr(p_position_id);
733 LOOP
734 FETCH get_position_path_csr INTO l_path_rec.sequence,
735 l_path_rec.mc_id,
736 l_path_rec.version_number,
737 l_path_rec.position_key;
738 EXIT WHEN get_position_path_csr%NOTFOUND;
739 l_path_tbl(l_index):= l_path_rec;
740 l_index := l_index +1;
741 END LOOP;
742 CLOSE get_position_path_csr;
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,'-- Opening Cursor check_pos_key_top_csr --');
746 END IF;
747
748 OPEN check_pos_key_top_csr(l_path_tbl(l_path_tbl.LAST).mc_id,
749 l_path_tbl(l_path_tbl.LAST).version_number,
750 l_path_tbl(l_path_tbl.LAST).position_key);
751 FETCH check_pos_key_top_csr INTO l_dummy;
752 IF (check_pos_key_top_csr%FOUND) THEN
753 l_top_flag := true;
754 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
755 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Set Top FLag to True --');
756 END IF;
757 ELSE
758 l_top_flag := false;
759 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
760 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Set Top FLag to False --');
761 END IF;
762 END IF;
763 CLOSE check_pos_key_top_csr;
764
765
766 v_Select := ' SELECT v'||l_path_tbl.LAST||'.csi_instance_id ';
767 v_From := ' FROM AHL_UC_HEADER_PATHS_V v0 ';
768 v_Where := ' WHERE v0.mc_id = :mc_id0 ' ||
769 ' AND v0.mc_version_number = nvl(:ver0,v0.mc_version_number) ';
770
771 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
772 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_Select --'||v_Select);
773 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_From -1-'||v_From);
774 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_Where -1-'||v_Where);
775 END IF;
776
777 --Build the dynamic sql statement
778 FOR i IN l_path_tbl.FIRST+1..l_path_tbl.LAST LOOP
779 v_From := v_From || ', AHL_UC_HEADER_PATHS_V v'||TO_CHAR(i)||' ';
780
781 -- R12 Dev changes : Modified Function Call to CHECK_POS_REF_PATH_CHAR and
782 -- modified where clause join between uc_header_id and parent_uc_header_id
783
784 v_Where := v_Where ||
785 ' AND v'||TO_CHAR(i)||'.mc_id = :mc_id'||TO_CHAR(i)||' ' ||
786 ' AND v'||TO_CHAR(i)||'.mc_version_number = '||
787 ' nvl(:ver'||TO_CHAR(i)||', v'||TO_CHAR(i)||'.mc_version_number) ' ||
788 ' AND v'||TO_CHAR(i-1)||'.uc_header_id = v'||TO_CHAR(i)||'.parent_uc_header_id '
789 ||' AND v'||TO_CHAR(i)||'.parent_position_key = :pos_key'||TO_CHAR(i-1)||' '
790 ||' AND AHL_MC_PATH_POSITION_PVT.CHECK_POS_REF_PATH_CHAR(v'||TO_CHAR(i)||'.csi_instance_id, v'||TO_CHAR(i)||'.parent_instance_id) = ''T'' ';
791
792 END LOOP;
793
794 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
795 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_From -2-'||v_From);
796 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_Where -2-'||v_Where);
797 END IF;
798
799 IF (l_top_flag) THEN
800 v_Stmt := 'INSERT INTO AHL_APPLICABLE_INSTANCES '||
801 ' SELECT uch.csi_instance_id ,'|| p_position_id ||
802 ' FROM AHL_UC_HEADER_PATHS_V uch '||
803 ' WHERE uch.position_key = :pos_key'||l_path_tbl.LAST
804 ||' AND uch.csi_instance_id in ( '
805 || v_Select || v_From || v_Where || ' ) ';
806
807 ELSE
808 v_Stmt := 'INSERT INTO AHL_APPLICABLE_INSTANCES '||
809 ' SELECT csi_ii.subject_id ,'|| p_position_id ||
810 ' FROM ahl_mc_relationships rel, csi_ii_relationships csi_ii '||
811 ' WHERE TO_NUMBER(CSI_II.POSITION_REFERENCE)=REL.RELATIONSHIP_ID '
812 ||' AND REL.position_key = :pos_key'||l_path_tbl.LAST
813 ||' START WITH csi_ii.object_id IN ( '
814 || v_Select || v_From || v_Where || ' ) '
815 || ' CONNECT BY PRIOR csi_ii.subject_id = csi_ii.object_id '
816 ||' AND CSI_II.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF'' '
817 ||' AND CSI_II.POSITION_REFERENCE IS NOT NULL '
818 ||' AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate) '
819 ||' AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate) ';
820
821 END IF;
822
823 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
824 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_Stmt --'||v_Stmt);
825 END IF;
826
827 /*for i in 0..(length(v_Stmt)/255) LOOP
828 dbms_output.put_line(substr(v_Stmt,i*255,255));
829 end loop;
830 */
831 ----------------------------------------
832 --Due to performance considerations, doing dynamic sql
833 --------------------------------------
834 v_CursorID := DBMS_SQL.OPEN_CURSOR;
835
836 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
837 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Parsing Sql --');
838 END IF;
839
840 DBMS_SQL.PARSE(v_CursorID, v_Stmt, DBMS_SQL.V7);
841
842 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
843 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Binding Params Sql --');
844 END IF;
845
846 --Bind the dynamic sql statement
847 FOR i IN l_path_tbl.FIRST..l_path_tbl.LAST LOOP
848 DBMS_SQL.BIND_VARIABLE (v_CursorID, ':mc_id'||i, l_path_tbl(i).mc_id);
849 DBMS_SQL.BIND_VARIABLE (v_CursorID, ':ver'||i, l_path_tbl(i).version_number);
850 DBMS_SQL.BIND_VARIABLE (v_CursorID, ':pos_key'||i, l_path_tbl(i).position_key);
851 END LOOP;
852
853 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
854 fnd_log.string(fnd_log.level_statement,l_full_name,'-- After Binding - Before Executing Sql --');
855 END IF;
856
857 --Execute the dynamic sql
858 v_RowsInserted := DBMS_SQL.EXECUTE(v_CursorID);
859
860 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
861 fnd_log.string(fnd_log.level_statement,l_full_name,'-- After Executing Sql --');
862 fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_RowsInserted --'||v_RowsInserted);
863 END IF;
864
865 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
866
867 -- Standard check of p_commit
868 IF FND_API.TO_BOOLEAN(p_commit) THEN
869 COMMIT WORK;
870 END IF;
871
872 -- Standard call to get message count and if count is 1, get message info
873 FND_MSG_PUB.Count_And_Get
874 ( p_count => x_msg_count,
875 p_data => x_msg_data,
876 p_encoded => fnd_api.g_false
877 );
878
879 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
880 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','At the end of PLSQL procedure');
881 END IF;
882
883 EXCEPTION
884 WHEN FND_API.G_EXC_ERROR THEN
885 Rollback to map_position_to_instances_pvt;
886 x_return_status := FND_API.G_RET_STS_ERROR;
887 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
888 p_data => x_msg_data,
889 p_encoded => fnd_api.g_false);
890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
891 Rollback to map_position_to_instances_pvt;
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
894 p_data => x_msg_data,
895 p_encoded => fnd_api.g_false);
896 WHEN OTHERS THEN
897 Rollback to map_position_to_instances_pvt;
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
900 p_procedure_name => l_api_name,
901 p_error_text => SQLERRM);
902 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
903 p_data => x_msg_data,
904 p_encoded => fnd_api.g_false);
905 END Map_Position_To_Instances;
906
907 -----------------------------
908 -- Start of Comments --
909 -- Procedure name : Get_Pos_Instance
910 -- Type : Private
911 -- Function : Returns the instance that maps to position path
912 -- Pre-reqs :
913 -- Parameters :
914 --
915 -- Get_Pos_Instance Parameters:
916 -- p_position_id IN NUMBER Required
917 -- p_csi_item_instance_id IN NUMBER Required starting instance
918 --
919 -- x_item_instance_id the instance that the position_id + instance maps to
920 -- Returns the parent instance_id if the position is empty
921 -- x_relationship_id returns the position relationship id for empty positions
922 -- x_lowest_uc_csi_id returns the leaf level UC id
923 -- x_mapping_status OUT VARCHAR2 Returns either NA (Not applicable),
924 -- EMPTY (Empty position) or MATCH (if matching instance found)
925 -- End of Comments.
926
927 PROCEDURE Get_Pos_Instance (
928 p_api_version IN NUMBER,
929 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
930 p_commit IN VARCHAR2 := FND_API.G_FALSE,
931 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
932 x_return_status OUT NOCOPY VARCHAR2,
933 x_msg_count OUT NOCOPY NUMBER,
934 x_msg_data OUT NOCOPY VARCHAR2,
935 p_position_id IN NUMBER,
936 p_csi_item_instance_id IN NUMBER,
937 x_parent_instance_id OUT NOCOPY NUMBER,
938 x_item_instance_id OUT NOCOPY NUMBER,
939 x_relationship_id OUT NOCOPY NUMBER,
940 x_lowest_uc_csi_id OUT NOCOPY NUMBER,
941 x_mapping_status OUT NOCOPY VARCHAR2)
942 IS
943 --
944 --Step 0) Check instance is defined in uc headers
945 CURSOR check_instance_top_csr(p_instance_id IN NUMBER) IS
946 SELECT 'X'
947 FROM AHL_UNIT_CONFIG_HEADERS uch
948 WHERE uch.csi_item_instance_id = p_instance_id;
949
950 --
951 --Step 1) Build the path Position table object
952 --Fetch all the position path information for given position_id
953 CURSOR get_position_path_csr (p_position_id IN NUMBER) IS
954 SELECT path.sequence, path.mc_id, path.version_number, path.position_key
955 FROM AHL_MC_PATH_POSITION_NODES path
956 WHERE path.path_position_id = p_position_id
957 order by sequence;
958
959 --
960 --Step 2a) Validate if the top instance matches the
961 -- initial path position mc_id and version number (mc_header_id)
962 CURSOR check_top_mc_csr (p_csi_ii_id IN NUMBER, p_mc_id IN NUMBER,
963 p_ver_num IN NUMBER) IS
964 SELECT 'X'
965 FROM AHL_MC_HEADERS_B hdr, AHL_UNIT_CONFIG_HEADERS uch
966 WHERE uch.csi_item_instance_id = p_csi_ii_id
967 AND uch.master_config_id = hdr.mc_header_id
968 AND HDR.mc_id = p_mc_id
969 AND HDR.version_number = nvl(p_ver_num, HDR.version_number);
970
971 --Step 2b) Fetch the subunit tree for given unit.
972 CURSOR get_subunit_csi_id_csr (p_start_csi_ii_id IN NUMBER,
973 p_parent_pos_key IN NUMBER,
974 p_child_mc_id IN NUMBER,
975 p_child_mc_ver_num IN NUMBER) IS
976 SELECT csi_instance_id
977 FROM AHL_UC_HEADER_PATHS_V
978 WHERE parent_instance_id = p_start_csi_ii_id
979 AND parent_position_key = p_parent_pos_key
980 AND mc_id = p_child_mc_id
981 AND mc_version_number = nvl(p_child_mc_ver_num, mc_version_number);
982
983 --
984 --Step 3a) Derive the instance that maps to unit or subunit
985 --Fetch the final csi id given starting subunit. Could be subunit
986 -- top node or a subnode.
987 CURSOR get_last_csi_id_csr (p_lowest_uc_ii_id IN NUMBER,
988 p_position_key IN NUMBER) IS
989 SELECT csi_ii.subject_id, csi_ii.object_id, TO_NUMBER(csi_ii.position_reference)
990 FROM csi_ii_relationships csi_ii
991 WHERE TO_NUMBER(CSI_II.POSITION_REFERENCE) in (select REL.RELATIONSHIP_ID
992 from ahl_mc_relationships rel
993 where REL.position_key = p_position_key)
994 START WITH csi_ii.object_id = p_lowest_uc_ii_id
995 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
996 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
997 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
998 --Jerry 03/03/2005 Added the following condition for fixing bug 4090856
999 AND CSI_II.POSITION_REFERENCE IS NOT NULL
1000 CONNECT BY PRIOR csi_ii.subject_id = csi_ii.object_id
1001 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1002 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
1003 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
1004 AND CSI_II.POSITION_REFERENCE IS NOT NULL
1005 UNION ALL
1006 SELECT uch.csi_item_instance_id, csi_ii.object_id, TO_NUMBER(csi_ii.position_reference)
1007 FROM AHL_MC_RELATIONSHIPS rel, AHL_UNIT_CONFIG_HEADERS UCH, CSI_II_RELATIONSHIPS csi_ii
1008 WHERE UCH.master_config_id = REL.mc_header_id
1009 AND REL.parent_relationship_id is NULL
1010 AND REL.position_key = p_position_key
1011 AND uch.csi_item_instance_id = p_lowest_uc_ii_id
1012 AND uch.csi_item_instance_id = csi_ii.subject_id (+)
1013 AND CSI_II.RELATIONSHIP_TYPE_CODE (+) = 'COMPONENT-OF'
1014 -- Changed by jaramana on July 13, 2006 to fix FP of bug 5368714
1015 -- Make the active start date and active end date clauses also to be outer joins
1016 -- AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
1017 -- AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
1018 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE(+), sysdate)) <= TRUNC(sysdate)
1019 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE(+), sysdate+1)) > TRUNC(sysdate);
1020
1021 --Step 4)
1022 --Get the parent relationship information for empty node search
1023 CURSOR get_parent_poskey_csr (p_mc_id IN NUMBER,
1024 p_version_number IN NUMBER,
1025 p_position_key IN NUMBER)
1026 IS
1027 SELECT rel.relationship_id, prel.position_key
1028 FROM AHL_MC_RELATIONSHIPS rel, AHL_MC_HEADERS_B hdr, AHL_MC_RELATIONSHIPS prel
1029 WHERE prel.relationship_id = rel.parent_relationship_id
1030 AND rel.position_key = p_position_key
1031 AND hdr.mc_header_id = rel.mc_header_id
1032 AND hdr.mc_id = p_mc_id
1033 AND hdr.version_number = nvl(p_version_number, hdr.version_number)
1034 ORDER BY hdr.version_number desc;
1035
1036 --
1037 --Step 5) Check that the path from instance to top has position key each step
1038 -- and that position key is not null for all relnships.
1039 CURSOR check_pos_ref_csr (p_csi_instance_id IN NUMBER,
1040 p_to_csi_instance_id IN NUMBER) IS
1041 SELECT 'X'
1042 FROM csi_ii_relationships csi_ii
1043 WHERE csi_ii.object_id = p_to_csi_instance_id
1044 START WITH csi_ii.subject_id = p_csi_instance_id
1045 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1046 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
1047 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
1048 --Jerry 03/03/2005 Added the following condition for fixing bug 4090856
1049 AND CSI_II.POSITION_REFERENCE IS NOT NULL
1050 CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
1051 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1052 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
1053 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
1054 AND CSI_II.POSITION_REFERENCE IS NOT NULL;
1055 --
1056 l_api_version CONSTANT NUMBER := 1.0;
1057 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pos_Instance';
1058 l_csi_ii_id NUMBER;
1059 l_index NUMBER;
1060 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
1061 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
1062 l_dummy VARCHAR2(1);
1063 l_dummy_id NUMBER;
1064 l_top_flag BOOLEAN;
1065 l_found_flag BOOLEAN;
1066 l_child_rel_id NUMBER;
1067 l_parent_pos_key NUMBER;
1068 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.' || g_pkg_name || '.' || l_api_name;
1069
1070 --
1071 BEGIN
1072 -- Standard start of API savepoint
1073 SAVEPOINT Get_Pos_Empty_Instance_pvt;
1074
1075 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1076 fnd_log.string(fnd_log.level_procedure, l_full_name||'.begin', 'At the start of PLSQL procedure. p_csi_item_instance_id = ' || p_csi_item_instance_id ||
1077 ', p_position_id = ' || p_position_id);
1078 END IF;
1079 -- Initialize Procedure return status to success
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081 x_mapping_status := 'NULL';
1082
1083 -- Standard call to check for call compatibility
1084 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1085 G_PKG_NAME) THEN
1086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1087 END IF;
1088
1089 -- Initialize message list if p_init_msg_list is set to TRUE
1090 IF FND_API.To_Boolean(p_init_msg_list) THEN
1091 FND_MSG_PUB.Initialize;
1092 END IF;
1093
1094 --Step 0) Validate that p_csi_item_instance is not null and valid
1095 IF (p_csi_item_instance_id IS NULL) THEN
1096 FND_MESSAGE.Set_Name('AHL','AHL_MC_POS_INSTANCE_ID_NULL');
1097 FND_MSG_PUB.ADD;
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100 --Validate that the input instance is a unit
1101 OPEN check_instance_top_csr (p_csi_item_instance_id);
1102 FETCH check_instance_top_csr INTO l_dummy;
1103 IF (check_instance_top_csr%NOTFOUND) THEN
1104 FND_MESSAGE.Set_Name('AHL','AHL_MC_POS_INSTANCE_ID_INV');
1105 FND_MSG_PUB.ADD;
1106 RAISE FND_API.G_EXC_ERROR;
1107 END IF;
1108 CLOSE check_instance_top_csr;
1109
1110 --Step 1) Build the path position table object based on path position id
1111 l_index :=0;
1112 OPEN get_position_path_csr(p_position_id);
1113 LOOP
1114 FETCH get_position_path_csr INTO l_path_rec.sequence,
1115 l_path_rec.mc_id,
1116 l_path_rec.version_number,
1117 l_path_rec.position_key;
1118 EXIT WHEN get_position_path_csr%NOTFOUND;
1119 l_path_tbl(l_index):= l_path_rec;
1120 l_index := l_index +1;
1121 END LOOP;
1122 CLOSE get_position_path_csr;
1123
1124 --Step 2) Traverse the table if there are multiple levels.
1125 -- Populate the lowest level instance id based on this query.
1126 --If table is less than 1 in size, this is invalid path
1127 IF (l_path_tbl.COUNT<1) THEN
1128 FND_MESSAGE.Set_Name('AHL','AHL_MC_POS_PATH_ID_INV');
1129 FND_MSG_PUB.ADD;
1130 RAISE FND_API.G_EXC_ERROR;
1131
1132 --If there is only 1 row, then lowest level UC = p_csi_instance_id
1133 ELSIF (l_path_tbl.COUNT =1) THEN
1134 OPEN check_top_mc_csr ( p_csi_item_instance_id,
1135 l_path_tbl(l_path_tbl.FIRST).mc_id,
1136 l_path_tbl(l_path_tbl.FIRST).version_number);
1137 FETCH check_top_mc_csr INTO l_dummy;
1138 IF (check_top_mc_csr%FOUND) THEN
1139 x_lowest_uc_csi_id := p_csi_item_instance_id;
1140 ELSE
1141 x_mapping_status := 'NA';
1142 END IF;
1143 CLOSE check_top_mc_csr;
1144
1145 --If there are multiple rows, then need to verify multiple levels.
1146 ELSE
1147 l_csi_ii_id := p_csi_item_instance_id;
1148 OPEN check_top_mc_csr (l_csi_ii_id,
1149 l_path_tbl(l_path_tbl.FIRST).mc_id,
1150 l_path_tbl(l_path_tbl.FIRST).version_number);
1151 FETCH check_top_mc_csr INTO l_dummy;
1152
1153 --If the top mc and top instance are valid match
1154 IF (check_top_mc_csr%FOUND) THEN
1155 l_found_flag := true;
1156 --Traverse down the path tree.Starting with the 1st subconfig. since
1157 -- top config is not relevant for traversal..
1158 FOR i IN l_path_tbl.FIRST+1..l_path_tbl.LAST LOOP
1159 OPEN get_subunit_csi_id_csr(l_csi_ii_id,
1160 l_path_tbl(i-1).position_key,
1161 l_path_tbl(i).mc_id,
1162 l_path_tbl(i).version_number);
1163 FETCH get_subunit_csi_id_csr INTO l_csi_ii_id;
1164 IF (get_subunit_csi_id_csr%NOTFOUND) THEN
1165 l_found_flag := false;
1166 CLOSE get_subunit_csi_id_csr;
1167 EXIT;
1168 END IF;
1169 CLOSE get_subunit_csi_id_csr;
1170 END LOOP;
1171
1172 --If l_found_flag Meaning all MCs are correct in traversal down MC path.
1173 IF (l_found_flag ) THEN
1174 x_lowest_uc_csi_id := l_csi_ii_id;
1175 ELSE
1176 x_mapping_status := 'NA';
1177 END IF;
1178 ELSE
1179 l_found_flag := false;
1180 x_mapping_status := 'NA';
1181 END IF;
1182 CLOSE check_top_mc_csr;
1183 END IF;
1184
1185 --Step 3) If we are able to derive the lowest Unit
1186 IF (x_lowest_uc_csi_id IS NOT NULL
1187 AND nvl(x_mapping_status,'NULL') <>'NA') THEN
1188 --Fetch the child instance that matches
1189 OPEN get_last_csi_id_csr (x_lowest_uc_csi_id,
1190 l_path_tbl(l_path_tbl.LAST).position_key);
1191 FETCH get_last_csi_id_csr INTO x_item_instance_id,
1192 x_parent_instance_id,
1193 x_relationship_id;
1194 IF (get_last_csi_id_csr%FOUND) THEN
1195 x_mapping_status := 'MATCH';
1196 ELSE
1197 x_mapping_status := 'EMPTY';
1198 END IF;
1199 CLOSE get_last_csi_id_csr;
1200
1201 IF (x_mapping_status ='EMPTY') THEN
1202 --Step 4) Do the empty position parent instance + relationship_id search
1203 --For empty position try to find parent instance's position key
1204 OPEN get_parent_poskey_csr (l_path_tbl(l_path_tbl.LAST).mc_id,
1205 l_path_tbl(l_path_tbl.LAST).version_number,
1206 l_path_tbl(l_path_tbl.LAST).position_key);
1207 FETCH get_parent_poskey_csr INTO l_child_rel_id, l_parent_pos_key;
1208
1209 --If parent instance is found, try to map it to an instance.
1210 IF (get_parent_poskey_csr%FOUND) THEN
1211 --map the parent position key to an instance in the lowest unit
1212 OPEN get_last_csi_id_csr (x_lowest_uc_csi_id, l_parent_pos_key);
1213 FETCH get_last_csi_id_csr INTO x_parent_instance_id,
1214 l_dummy_id,
1215 l_dummy_id;
1216
1217 --Check if parent instance is found or not.
1218 IF (get_last_csi_id_csr%FOUND) THEN
1219 x_mapping_status := 'EMPTY';
1220 x_item_instance_id := null;
1221 x_relationship_id := l_child_rel_id;
1222 ELSE
1223 --Parent position has no matching instance. Position not considered empty
1224 x_mapping_status := 'PARENT_EMPTY';
1225 END IF;
1226 CLOSE get_last_csi_id_csr;
1227
1228 ELSE
1229 --parent position is not defined. This is not correct.
1230 x_mapping_status := 'NA';
1231 END IF;
1232 CLOSE get_parent_poskey_csr;
1233 END IF;
1234 END IF; --If empty
1235
1236 --Step 5) Check the position reference is valid for entire path
1237 IF (x_item_instance_id <> p_csi_item_instance_id) THEN
1238 OPEN check_pos_ref_csr (x_item_instance_id,
1239 p_csi_item_instance_id);
1240 FETCH check_pos_ref_csr INTO l_dummy;
1241 IF (check_pos_ref_csr%NOTFOUND) THEN
1242 x_item_instance_id:= null;
1243 x_mapping_status := 'NA';
1244 END IF;
1245 CLOSE check_pos_ref_csr;
1246 END IF;
1247
1248 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1249 fnd_log.string(fnd_log.level_procedure, l_full_name||'.end', 'At the end of PLSQL procedure. About to count and get error messages.');
1250 END IF;
1251 -- Standard call to get message count and if count is 1, get message info
1252 FND_MSG_PUB.Count_And_Get
1253 ( p_count => x_msg_count,
1254 p_data => x_msg_data,
1255 p_encoded => fnd_api.g_false
1256 );
1257 EXCEPTION
1258 WHEN FND_API.G_EXC_ERROR THEN
1259 Rollback to Get_Pos_Empty_Instance_pvt;
1260 x_return_status := FND_API.G_RET_STS_ERROR;
1261 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1262 p_data => x_msg_data,
1263 p_encoded => fnd_api.g_false);
1264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1265 Rollback to Get_Pos_Empty_Instance_pvt;
1266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1267 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1268 p_data => x_msg_data,
1269 p_encoded => fnd_api.g_false);
1270 WHEN OTHERS THEN
1271 Rollback to Get_Pos_Empty_Instance_pvt;
1272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1273 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1274 p_procedure_name => l_api_name,
1275 p_error_text => SQLERRM);
1276 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1277 p_data => x_msg_data,
1278 p_encoded => fnd_api.g_false);
1279 END Get_Pos_Instance;
1280
1281 ----------------------------
1282 -- Start of Comments --
1283 -- Procedure name : Get_Pos_Instance
1284 -- Type : Private
1285 -- Function : Returns the instance that maps to position path
1286 -- Pre-reqs :
1287 -- Parameters :
1288 --
1289 -- Map_Position_To_Instances Parameters:
1290 -- p_position_id IN NUMBER Required
1291 -- p_csi_item_instance_id IN NUMBER Required starting instance
1292 --
1293 -- x_item_instance_id the instance that the position_id + instance maps to
1294 -- x_lowest_uc_csi_id returns the leaf level UC id
1295 -- x_mapping_status OUT VARCHAR2 Returns either NA (Not applicable),
1296 -- EMPTY (Empty position) or MATCH (if matching instance found)
1297 -- End of Comments.
1298
1299 PROCEDURE Get_Pos_Instance (
1300 p_api_version IN NUMBER,
1301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1302 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1303 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_count OUT NOCOPY NUMBER,
1306 x_msg_data OUT NOCOPY VARCHAR2,
1307 p_position_id IN NUMBER,
1308 p_csi_item_instance_id IN NUMBER,
1309 x_item_instance_id OUT NOCOPY NUMBER,
1310 x_lowest_uc_csi_id OUT NOCOPY NUMBER,
1311 x_mapping_status OUT NOCOPY VARCHAR2)
1312 IS
1313 --
1314 l_instance_id NUMBER;
1315 l_relationship_id NUMBER;
1316 l_parent_instance_id NUMBER;
1317 --
1318 BEGIN
1319
1320 Get_Pos_Instance (
1321 p_api_version => 1.0,
1322 p_position_id => p_position_id,
1323 p_csi_item_instance_id => p_csi_item_instance_id,
1324 x_parent_instance_id => l_parent_instance_id,
1325 x_item_instance_id => l_instance_id,
1326 x_relationship_id => l_relationship_id,
1327 x_lowest_uc_csi_id => x_lowest_uc_csi_id,
1328 x_mapping_status => x_mapping_status,
1329 x_return_status => x_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data);
1332
1333 --Remaps the instance to null or no match
1334 IF (x_mapping_status <> 'MATCH') THEN
1335 x_item_instance_id := null;
1336
1337 --Remap the PARENT_EMPTY status to NA. Bad code due to need of extra type in
1338 --overloaded Get_Pos_Instance method
1339 IF (x_mapping_status = 'PARENT_EMPTY') THEN
1340 x_mapping_status := 'NA';
1341 END IF;
1342 ELSE
1343 x_item_instance_id := l_instance_id;
1344 END IF;
1345
1346 END Get_Pos_Instance;
1347
1348
1349
1350 -----------------------------
1351 -- Start of Comments --
1352 -- Procedure name : Copy_Positions_For_MC
1353 -- Type : Private
1354 -- Function : Copies all path positions for 1 MC to another MC
1355 -- Pre-reqs :
1356 -- Parameters :
1357 --
1358 -- Copy_Positions_For_MC Parameters:
1359 -- p_from_mc_header_id IN NUMBER Required
1360 -- p_to_mc_header_id IN NUMBER Required
1361 --
1362 -- End of Comments.
1363
1364 PROCEDURE Copy_Positions_For_MC (
1365 p_api_version IN NUMBER,
1366 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1367 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1368 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1369 x_return_status OUT NOCOPY VARCHAR2,
1370 x_msg_count OUT NOCOPY NUMBER,
1371 x_msg_data OUT NOCOPY VARCHAR2,
1372 p_from_mc_header_id IN NUMBER,
1373 p_to_mc_header_id IN NUMBER)
1374 IS
1375 --
1376 CURSOR check_mc_status_csr (p_header_id IN NUMBER) IS
1377 SELECT header.config_status_code, header.config_status_meaning
1378 FROM ahl_mc_headers_v header
1379 WHERE header.mc_header_id = p_header_id;
1380 --
1381 CURSOR get_mc_id_csr (p_mc_header_id IN NUMBER) IS
1382 SELECT mc_id
1383 FROM ahl_mc_headers_b
1384 WHERE mc_header_id = p_mc_header_id;
1385 --
1386 CURSOR get_ver_position_ids_csr (p_header_id IN NUMBER) IS
1387 SELECT pnodes.path_position_id
1388 FROM ahl_mc_path_position_nodes pnodes, ahl_mc_headers_b hdr
1389 WHERE pnodes.sequence = 0
1390 AND pnodes.mc_id = hdr.mc_id
1391 AND pnodes.version_number = hdr.version_number
1392 AND hdr.mc_header_id = p_header_id;
1393 --
1394 CURSOR get_nover_position_ids_csr(p_header_id IN NUMBER) IS
1395 SELECT pnodes.path_position_id
1396 FROM ahl_mc_path_position_nodes pnodes, ahl_mc_headers_b hdr
1397 WHERE pnodes.sequence = 0
1398 AND pnodes.mc_id = hdr.mc_id
1399 AND pnodes.version_number IS NULL
1400 AND hdr.mc_header_id = p_header_id;
1401 --
1402 l_api_version CONSTANT NUMBER := 1.0;
1403 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Positions_For_Mc';
1404 l_from_mc_id NUMBER;
1405 l_to_mc_id NUMBER;
1406 l_status_code VARCHAR2(30);
1407 l_status VARCHAR2(80);
1408 l_position_id NUMBER;
1409 l_new_position_id NUMBER;
1410 l_msg_count NUMBER;
1411 --
1412 BEGIN
1413 -- Standard start of API savepoint
1414 SAVEPOINT Copy_Positions_For_Mc_pvt;
1415
1416 -- Initialize Procedure return status to success
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418 -- Standard call to check for call compatibility
1419 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1420 G_PKG_NAME) THEN
1421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1422 END IF;
1423
1424 -- Initialize message list if p_init_msg_list is set to TRUE
1425 IF FND_API.To_Boolean(p_init_msg_list) THEN
1426 FND_MSG_PUB.Initialize;
1427 END IF;
1428
1429 --Check Status of MC allows for editing
1430 OPEN check_mc_status_csr(p_to_mc_header_id);
1431 FETCH check_mc_status_csr INTO l_status_code, l_status;
1432 IF (check_mc_status_csr%NOTFOUND) THEN
1433 FND_MESSAGE.Set_Name('AHL','AHL_UC_MC_HEADER_ID_INVALID');
1434 FND_MESSAGE.Set_Token('MC_HEADER_ID',p_to_mc_header_id);
1435 FND_MSG_PUB.ADD;
1436 CLOSE check_mc_status_csr;
1437 RAISE FND_API.G_EXC_ERROR;
1438 ELSIF ( l_status_code <> 'DRAFT' AND
1439 l_status_code <> 'APPROVAL_REJECTED') THEN
1440 FND_MESSAGE.Set_Name('AHL','AHL_MC_EDIT_INV_MC');
1441 FND_MESSAGE.Set_Token('STATUS', l_status);
1442 FND_MSG_PUB.ADD;
1443 CLOSE check_mc_status_csr;
1444 RAISE FND_API.G_EXC_ERROR;
1445 END IF;
1446 CLOSE check_mc_status_csr;
1447
1448 --Copy all version specific paths
1449 OPEN get_ver_position_ids_csr(p_from_mc_header_id);
1450 LOOP
1451 FETCH get_ver_position_ids_csr INTO l_position_id;
1452 EXIT WHEN get_ver_position_ids_csr%NOTFOUND;
1453
1454 Copy_Position (
1455 p_api_version => 1.0,
1456 p_commit => FND_API.G_FALSE,
1457 p_position_id => l_position_id,
1458 p_to_mc_header_id => p_to_mc_header_id,
1459 x_position_id => l_new_position_id,
1460 x_return_status => x_return_status,
1461 x_msg_count => x_msg_count,
1462 x_msg_data => x_msg_data);
1463 -- Check return status.
1464 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1465 RAISE FND_API.G_EXC_ERROR;
1466 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1468 END IF;
1469 END LOOP;
1470 CLOSE get_ver_position_ids_csr;
1471
1472 OPEN get_mc_id_csr(p_to_mc_header_id);
1473 FETCH get_mc_id_csr INTO l_to_mc_id;
1474 CLOSE get_mc_id_csr;
1475
1476 OPEN get_mc_id_csr(p_from_mc_header_id);
1477 FETCH get_mc_id_csr INTO l_from_mc_id;
1478 CLOSE get_mc_id_csr;
1479
1480 IF (l_to_mc_id <> l_from_mc_id) THEN
1481 --Copy the non version specific paths as well
1482 OPEN get_nover_position_ids_csr(p_from_mc_header_id);
1483 LOOP
1484 FETCH get_nover_position_ids_csr INTO l_position_id;
1485 EXIT WHEN get_nover_position_ids_csr%NOTFOUND;
1486
1487 Copy_Position (
1488 p_api_version => 1.0,
1489 p_commit => FND_API.G_FALSE,
1490 p_position_id => l_position_id,
1491 p_to_mc_header_id => p_to_mc_header_id,
1492 x_position_id => l_new_position_id,
1493 x_return_status => x_return_status,
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data);
1496 -- Check return status.
1497 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1498 RAISE FND_API.G_EXC_ERROR;
1499 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1501 END IF;
1502 END LOOP;
1503 CLOSE get_nover_position_ids_csr;
1504 END IF;
1505
1506 -- Check return status.
1507 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1508 RAISE FND_API.G_EXC_ERROR;
1509 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1511 END IF;
1512
1513
1514 -- Standard check of p_commit
1515 IF FND_API.TO_BOOLEAN(p_commit) THEN
1516 COMMIT WORK;
1517 END IF;
1518 -- Standard call to get message count and if count is 1, get message info
1519 FND_MSG_PUB.Count_And_Get
1520 ( p_count => x_msg_count,
1521 p_data => x_msg_data,
1522 p_encoded => fnd_api.g_false
1523 );
1524 EXCEPTION
1525 WHEN FND_API.G_EXC_ERROR THEN
1526 Rollback to Copy_Positions_For_Mc_pvt;
1527 x_return_status := FND_API.G_RET_STS_ERROR;
1528 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1529 p_data => x_msg_data,
1530 p_encoded => fnd_api.g_false);
1531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1532 Rollback to Copy_Positions_For_Mc_pvt;
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1534 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1535 p_data => x_msg_data,
1536 p_encoded => fnd_api.g_false);
1537 WHEN OTHERS THEN
1538 Rollback to Copy_Positions_For_Mc_pvt;
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1540 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1541 p_procedure_name => l_api_name,
1542 p_error_text => SQLERRM);
1543 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1544 p_data => x_msg_data,
1545 p_encoded => fnd_api.g_false);
1546 END Copy_Positions_For_MC;
1547
1548 -----------------------------
1549 -- Start of Comments --
1550 -- Procedure name : Copy_Position
1551 -- Type : Private
1552 -- Function : Copies 1 path positions to 1 MC
1553 -- Pre-reqs :
1554 -- Parameters :
1555 --
1556 -- Copy_Position
1557 -- p_position_id IN NUMBER Required
1558 -- p_to_mc_header_id IN NUMBER Required
1559 -- x_positioN_id OUT NUMBER
1560 --
1561 -- End of Comments.
1562
1563 PROCEDURE Copy_Position (
1564 p_api_version IN NUMBER,
1565 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1566 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1567 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1568 x_return_status OUT NOCOPY VARCHAR2,
1569 x_msg_count OUT NOCOPY NUMBER,
1570 x_msg_data OUT NOCOPY VARCHAR2,
1571 p_position_id IN NUMBER,
1572 p_to_mc_header_id IN NUMBER,
1573 x_position_id OUT NOCOPY NUMBER)
1574 IS
1575 --
1576 CURSOR get_mc_id_ver_csr (p_mc_header_id IN NUMBER) IS
1577 SELECT mc_id, version_number
1578 FROM ahl_mc_headers_b
1579 WHERE mc_header_id = p_mc_header_id;
1580 --
1581 CURSOR get_mc_path_position_nodes_csr (p_position_id IN NUMBER) IS
1582 SELECT *
1583 FROM ahl_mc_path_position_nodes
1584 WHERE path_position_id = p_position_id
1585 order by sequence;
1586 --
1587 CURSOR get_path_position_ref_csr (p_position_id IN NUMBER) IS
1588 SELECT position_ref_code
1589 FROM ahl_mc_path_positions
1590 WHERE path_position_id = p_position_id;
1591 --
1592 l_api_version CONSTANT NUMBER := 1.0;
1593 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Position';
1594 l_position_node get_mc_path_position_nodes_csr%ROWTYPE;
1595 l_pos_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
1596 l_pos_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
1597 l_mc_id NUMBER;
1598 l_version_number NUMBER;
1599 l_pos_ref_code VARCHAR2(30);
1600 l_index NUMBER;
1601 l_return_status VARCHAR2(1);
1602 l_msg_count NUMBER;
1603 l_msg_data varchar2(2000);
1604 --
1605 BEGIN
1606 -- Standard start of API savepoint
1607 SAVEPOINT Copy_Position_pvt;
1608
1609 -- Initialize Procedure return status to success
1610 x_return_status := FND_API.G_RET_STS_SUCCESS;
1611 -- Standard call to check for call compatibility
1612 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1613 G_PKG_NAME) THEN
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615 END IF;
1616
1617 -- Initialize message list if p_init_msg_list is set to TRUE
1618 IF FND_API.To_Boolean(p_init_msg_list) THEN
1619 FND_MSG_PUB.Initialize;
1620 END IF;
1621
1622 --Fetch the to mc_header_id information
1623 OPEN get_mc_id_ver_csr(p_to_mc_header_id);
1624 FETCH get_mc_id_ver_csr INTO l_mc_id, l_version_number;
1625 IF (get_mc_id_ver_csr%NOTFOUND) THEN
1626 FND_MESSAGE.Set_Name('AHL','AHL_MC_HEADER_ID_INVALID');
1627 FND_MESSAGE.Set_Token('NAME','');
1628 FND_MESSAGE.Set_Token('MC_HEADER_ID',p_to_mc_header_id);
1629 FND_MSG_PUB.ADD;
1630 CLOSE get_mc_id_ver_csr;
1631 RAISE FND_API.G_EXC_ERROR;
1632 END IF;
1633 CLOSE get_mc_id_ver_csr;
1634
1635 --Fetch the position path information
1636 l_index:=0;
1637 OPEN get_mc_path_position_nodes_csr(p_position_id);
1638 LOOP
1639 FETCH get_mc_path_position_nodes_csr INTO l_position_node;
1640 EXIT WHEN get_mc_path_position_nodes_csr%NOTFOUND;
1641
1642 --Copy to our table record
1643 IF (l_position_node.sequence = 0) THEN
1644 l_pos_rec.mc_id := l_mc_id;
1645 IF (l_position_node.version_number IS NOT NULL) THEN
1646 l_pos_rec.version_number := l_version_number;
1647 ELSE
1648 l_pos_rec.version_number := NULL;
1649 END IF;
1650 ELSE
1651 l_pos_rec.mc_id := l_position_node.mc_id;
1652 l_pos_rec.version_number := l_position_node.version_number ;
1653 END IF;
1654
1655 l_pos_rec.position_key := l_position_node.position_key ;
1656 l_pos_rec.attribute_category := l_position_node.attribute_category ;
1657 l_pos_rec.attribute1 := l_position_node.attribute1 ;
1658 l_pos_rec.attribute2 := l_position_node.attribute2 ;
1659 l_pos_rec.attribute3 := l_position_node.attribute3 ;
1660 l_pos_rec.attribute4 := l_position_node.attribute4 ;
1661 l_pos_rec.attribute5 := l_position_node.attribute5 ;
1662 l_pos_rec.attribute6 := l_position_node.attribute6 ;
1663 l_pos_rec.attribute7 := l_position_node.attribute7 ;
1664 l_pos_rec.attribute8 := l_position_node.attribute8 ;
1665 l_pos_rec.attribute9 := l_position_node.attribute9 ;
1666 l_pos_rec.attribute10 := l_position_node.attribute10 ;
1667 l_pos_rec.attribute11 := l_position_node.attribute11 ;
1668 l_pos_rec.attribute12 := l_position_node.attribute12 ;
1669 l_pos_rec.attribute13 := l_position_node.attribute13 ;
1670 l_pos_rec.attribute14 := l_position_node.attribute14 ;
1671 l_pos_rec.attribute15 := l_position_node.attribute15 ;
1672 l_pos_tbl(l_index) := l_pos_rec;
1673 l_index:= l_index+1;
1674
1675 END LOOP;
1676 CLOSE get_mc_path_position_nodes_csr;
1677
1678 OPEN get_path_position_ref_csr(p_position_id);
1679 FETCH get_path_position_ref_csr INTO l_pos_ref_code;
1680 CLOSE get_path_position_ref_csr;
1681
1682 -- Check Error Message stack.
1683 x_msg_count := FND_MSG_PUB.count_msg;
1684 IF x_msg_count > 0 THEN
1685 RAISE FND_API.G_EXC_ERROR;
1686 END IF;
1687
1688 --Create position with new error message stack.
1689 --This stack will not be useful
1690 Create_Position_ID (
1691 p_api_version => 1.0,
1692 p_init_msg_list => FND_API.G_TRUE,
1693 p_commit => FND_API.G_FALSE,
1694 p_path_position_tbl => l_pos_tbl,
1695 p_position_ref_meaning => FND_API.G_MISS_CHAR,
1696 p_position_ref_code => l_pos_ref_code,
1697 x_position_id => x_position_id,
1698 x_return_status => l_return_status,
1699 x_msg_count => l_msg_count,
1700 x_msg_data => l_msg_data);
1701
1702 -- Suppress the validation errors from Create API.
1703 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1704 --Suppress the expected errors
1705 --Clean out the messages
1706 FND_MSG_PUB.Initialize;
1707 x_position_id := null;
1708 x_return_status := FND_API.G_RET_STS_SUCCESS;
1709 x_msg_count := 0;
1710 x_msg_data := null;
1711 ELSE
1712 --Use normal error handling
1713 x_return_status := l_return_status;
1714 x_msg_count := l_msg_count;
1715 x_msg_data := l_msg_data;
1716 END IF;
1717
1718 -- Check return status.
1719 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1720 RAISE FND_API.G_EXC_ERROR;
1721 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1723 END IF;
1724
1725 -- Standard check of p_commit
1726 IF FND_API.TO_BOOLEAN(p_commit) THEN
1727 COMMIT WORK;
1728 END IF;
1729
1730 -- Standard call to get message count and if count is 1, get message info
1731 FND_MSG_PUB.Count_And_Get
1732 ( p_count => x_msg_count,
1733 p_data => x_msg_data,
1734 p_encoded => fnd_api.g_false
1735 );
1736 EXCEPTION
1737 WHEN FND_API.G_EXC_ERROR THEN
1738 Rollback to Copy_Position_pvt;
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1741 p_data => x_msg_data,
1742 p_encoded => fnd_api.g_false);
1743 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1744 Rollback to Copy_Position_pvt;
1745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1746 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1747 p_data => x_msg_data,
1748 p_encoded => fnd_api.g_false);
1749 WHEN OTHERS THEN
1750 Rollback to Copy_Position_pvt;
1751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1753 p_procedure_name => l_api_name,
1754 p_error_text => SQLERRM);
1755 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1756 p_data => x_msg_data,
1757 p_encoded => fnd_api.g_false);
1758 END Copy_Position;
1759
1760 -----------------------------
1761 -- Start of Comments --
1762 -- Procedure name : Delete_Positions_For_MC
1763 -- Type : Private
1764 -- Function : Deletes the Positions corresponding to 1 MC
1765 -- Pre-reqs :
1766 -- Parameters :
1767 --
1768 -- Delete_Positions_For_MC Parameters:
1769 -- p_mc_header_id IN NUMBER Required
1770 --
1771 -- End of Comments.
1772
1773 PROCEDURE Delete_Positions_For_MC (
1774 p_api_version IN NUMBER,
1775 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1776 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1777 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1778 x_return_status OUT NOCOPY VARCHAR2,
1779 x_msg_count OUT NOCOPY NUMBER,
1780 x_msg_data OUT NOCOPY VARCHAR2,
1781 p_mc_header_id IN NUMBER)
1782 IS
1783 --
1784 CURSOR check_mc_status_csr (p_header_id IN NUMBER) IS
1785 SELECT config_status_code, config_status_meaning
1786 FROM ahl_mc_headers_v header
1787 WHERE header.mc_header_id = p_header_id;
1788 --
1789 CURSOR get_num_of_version_csr (p_header_id IN NUMBER) IS
1790 SELECT count(*)
1791 FROM ahl_mc_headers_b header
1792 WHERE header.mc_id = (select mc_id FROM ahl_mc_headers_b
1793 where mc_header_id = p_header_id);
1794 --
1795 --Fetch all version specific path positions
1796 CURSOR get_ver_position_ids_csr (p_mc_header_id IN NUMBER) IS
1797 SELECT path.path_position_id
1798 FROM AHL_MC_PATH_POSITION_NODES path, AHL_MC_HEADERS_B headers
1799 WHERE path.MC_ID = headers.mc_id
1800 AND path.sequence = 0
1801 AND path.version_number = headers.version_number
1802 AND headers.mc_header_id = p_mc_header_id;
1803 --
1804 --Fetch all non-version specific path positions
1805 CURSOR get_nover_position_ids_csr (p_mc_header_id IN NUMBER) IS
1806 SELECT path.path_position_id
1807 FROM AHL_MC_PATH_POSITION_NODES path, AHL_MC_HEADERS_B headers
1808 WHERE path.MC_ID = headers.mc_id
1809 AND path.sequence = 0
1810 AND path.version_number IS NULL
1811 AND headers.mc_header_id = p_mc_header_id;
1812 --
1813 --Fetch all version specific path positions
1814 CURSOR check_posid_in_rstmts_csr (p_position_id IN NUMBER) IS
1815 SELECT 'X'
1816 FROM AHL_MC_RULE_STATEMENTS
1817 WHERE (subject_ID = p_position_id
1818 AND subject_type = 'POSITION')
1819 OR (object_id = p_position_id
1820 AND (object_type = 'ITEM_AS_POSITION'
1821 OR object_type = 'CONFIG_AS_POSITION'));
1822 --
1823 --
1824 l_api_version CONSTANT NUMBER := 1.0;
1825 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Positions_For_Mc';
1826 l_position_id NUMBER;
1827 l_junk VARCHAR2(1);
1828 l_status_code VARCHAR2(30);
1829 l_status VARCHAR2(80);
1830 l_num_of_version NUMBER;
1831 --
1832 BEGIN
1833 -- Standard start of API savepoint
1834 SAVEPOINT Delete_Positions_For_Mc_pvt;
1835
1836 -- Initialize Procedure return status to success
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838 -- Standard call to check for call compatibility
1839 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1840 G_PKG_NAME) THEN
1841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1842 END IF;
1843
1844 -- Initialize message list if p_init_msg_list is set to TRUE
1845 IF FND_API.To_Boolean(p_init_msg_list) THEN
1846 FND_MSG_PUB.Initialize;
1847 END IF;
1848
1849 --Check Status of MC allows for editing
1850 OPEN check_mc_status_csr(p_mc_header_id);
1851 FETCH check_mc_status_csr INTO l_status_code, l_status;
1852 IF (check_mc_status_csr%NOTFOUND) THEN
1853 FND_MESSAGE.Set_Name('AHL','AHL_MC_HEADER_ID_INVALID');
1854 FND_MESSAGE.Set_Token('NAME','');
1855 FND_MESSAGE.Set_Token('MC_HEADER_ID',p_mc_header_id);
1856 FND_MSG_PUB.ADD;
1857 CLOSE check_mc_status_csr;
1858 RAISE FND_API.G_EXC_ERROR;
1859 ELSIF ( l_status_code <> 'DRAFT' AND
1860 l_status_code <> 'APPROVAL_REJECTED') THEN
1861 FND_MESSAGE.Set_Name('AHL','AHL_MC_EDIT_INV_MC');
1862 FND_MESSAGE.Set_Token('STATUS', l_status);
1863 FND_MSG_PUB.ADD;
1864 CLOSE check_mc_status_csr;
1865 RAISE FND_API.G_EXC_ERROR;
1866 END IF;
1867 CLOSE check_mc_status_csr;
1868
1869 --Delete version specific positions
1870 OPEN get_ver_position_ids_csr(p_mc_header_id);
1871 LOOP
1872 FETCH get_ver_position_ids_csr INTO l_position_id;
1873 EXIT WHEN get_ver_position_ids_csr%NOTFOUND;
1874
1875 --Validate position_id is not used in other tables.
1876 --Only need to validate MC rules since other paths are only in
1877 -- complete MCs, which can not delete position paths or rules.
1878 OPEN check_posid_in_rstmts_csr(l_position_id);
1879 FETCH check_posid_in_rstmts_csr INTO l_junk;
1880 IF (check_posid_in_rstmts_csr%NOTFOUND) THEN
1881
1882 --Delete the position_id
1883 DELETE FROM AHL_MC_PATH_POSITION_NODES
1884 WHERE path_position_id = l_position_id;
1885
1886 DELETE FROM AHL_MC_PATH_POSITIONS
1887 WHERE path_position_id = l_position_id;
1888 END IF;
1889 CLOSE check_posid_in_rstmts_csr;
1890
1891 END LOOP;
1892 CLOSE get_ver_position_ids_csr;
1893
1894 --Delete non-version specific positions
1895 OPEN get_num_of_version_csr(p_mc_header_id);
1896 FETCH get_num_of_version_csr INTO l_num_of_version;
1897 CLOSE get_num_of_version_csr;
1898
1899 IF (l_num_of_version = 1) THEN
1900 OPEN get_nover_position_ids_csr(p_mc_header_id);
1901 LOOP
1902 FETCH get_nover_position_ids_csr INTO l_position_id;
1903 EXIT WHEN get_nover_position_ids_csr%NOTFOUND;
1904
1905 --Validate position_id is not used in other tables.
1906 --Only need to validate MC rules since other paths are only in
1907 -- complete MCs, which can not delete position paths or rules.
1908 OPEN check_posid_in_rstmts_csr(l_position_id);
1909 FETCH check_posid_in_rstmts_csr INTO l_junk;
1910 IF (check_posid_in_rstmts_csr%NOTFOUND) THEN
1911
1912 --Delete the position_id
1913 DELETE FROM AHL_MC_PATH_POSITION_NODES
1914 WHERE path_position_id = l_position_id;
1915
1916 DELETE FROM AHL_MC_PATH_POSITIONS
1917 WHERE path_position_id = l_position_id;
1918
1919 END IF;
1920 CLOSE check_posid_in_rstmts_csr;
1921
1922 END LOOP;
1923 CLOSE get_nover_position_ids_csr;
1924 END IF;
1925
1926 -- Standard check of p_commit
1927 IF FND_API.TO_BOOLEAN(p_commit) THEN
1928 COMMIT WORK;
1929 END IF;
1930 -- Standard call to get message count and if count is 1, get message info
1931 FND_MSG_PUB.Count_And_Get
1932 ( p_count => x_msg_count,
1933 p_data => x_msg_data,
1934 p_encoded => fnd_api.g_false
1935 );
1936 EXCEPTION
1937 WHEN FND_API.G_EXC_ERROR THEN
1938 Rollback to Delete_Positions_For_Mc_pvt;
1939 x_return_status := FND_API.G_RET_STS_ERROR;
1940 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1941 p_data => x_msg_data,
1942 p_encoded => fnd_api.g_false);
1943 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1944 Rollback to Delete_Positions_For_Mc_pvt;
1945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1946 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1947 p_data => x_msg_data,
1948 p_encoded => fnd_api.g_false);
1949 WHEN OTHERS THEN
1950 Rollback to Delete_Positions_For_Mc_pvt;
1951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1953 p_procedure_name => l_api_name,
1954 p_error_text => SQLERRM);
1955 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1956 p_data => x_msg_data,
1957 p_encoded => fnd_api.g_false);
1958 END Delete_Positions_For_MC;
1959
1960
1961 ---------------------------------------------------------------------
1962 -- Start of Comments --
1963 -- Function name: get_posref_by_id
1964 -- Type : Private
1965 -- Function : Fetches the position path position ref code
1966 -- Pre-reqs :
1967 -- Parameters :
1968 --
1969 -- get_position_ref_code Parameters:
1970 -- p_position_id IN NUMBER the path position id
1971 -- p_code_flag IN VARHCAR2 If Equal to FND_API.G_TRUE, then return
1972 -- pos ref code, else return pos ref meaning. Default to False.
1973 --
1974 FUNCTION get_posref_by_id(
1975 p_path_position_ID IN NUMBER,
1976 p_code_flag IN VARCHAR2 := FND_API.G_FALSE)
1977 RETURN VARCHAR2 -- Position Ref Code or Meaning
1978 IS
1979 --
1980 CURSOR get_pos_ref_csr (p_position_id IN NUMBER) IS
1981 SELECT position_ref_code
1982 FROM AHL_MC_PATH_POSITIONS
1983 WHERE path_position_id = p_position_id;
1984 --
1985 --Select the default position reference.
1986 CURSOR get_def_pos_ref_csr (p_position_id IN NUMBER) IS
1987 SELECT rel.position_ref_code
1988 FROM AHL_MC_HEADERS_B hd, AHL_MC_RELATIONSHIPS rel,
1989 AHL_MC_PATH_POSITION_NODES pnodes
1990 WHERE hd.mc_header_id = rel.mc_header_id
1991 AND rel.position_key = pnodes.position_key
1992 AND hd.mc_id = pnodes.mc_id
1993 AND pnodes.sequence = (SELECT MAX(sequence) FROM AHL_MC_PATH_POSITION_NODES
1994 WHERE path_position_id = p_position_id)
1995 AND pnodes.path_position_id = p_position_id
1996 order by hd.version_number desc;
1997 --
1998 l_pos_ref_code VARCHAR2(30);
1999 l_pos_ref_meaning VARCHAR2(80);
2000 l_return_val BOOLEAN;
2001 --
2002 BEGIN
2003
2004 OPEN get_pos_ref_csr(p_path_position_ID);
2005 FETCH get_pos_ref_csr INTO l_pos_ref_code;
2006
2007 --If there are no pos ref defined for path
2008 IF (l_pos_ref_code IS NULL) OR
2009 (l_pos_ref_code = FND_API.G_MISS_CHAR) THEN
2010 OPEN get_def_pos_ref_csr(p_path_position_ID);
2011 FETCH get_def_pos_ref_csr INTO l_pos_ref_code;
2012 CLOSE get_def_pos_ref_csr;
2013 END IF;
2014 CLOSE get_pos_ref_csr;
2015
2016 IF (p_code_flag = FND_API.G_TRUE) THEN
2017 RETURN l_pos_ref_code;
2018 ELSE
2019 --Convert to meaning.
2020 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning('AHL_POSITION_REFERENCE',
2021 l_pos_ref_code,
2022 l_pos_ref_meaning,
2023 l_return_val);
2024
2025 Return l_pos_ref_meaning;
2026 END IF;
2027 END Get_Posref_By_ID;
2028
2029 ---------------------------------------------------------------------
2030 -- Start of Comments --
2031 -- Function name: get_posref_by_path
2032 -- Type : Private
2033 -- Function : Fetches the position path position ref code
2034 -- Pre-reqs :
2035 -- Parameters :
2036 --
2037 -- get_position_ref_code Parameters:
2038 -- p_position_path_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type Required
2039 -- p_code_flag IN VARHCAR2 If Equal to FND_API.G_TRUE, then return
2040 -- pos ref code, else return pos ref meaning. Default to False.
2041 --
2042 FUNCTION get_posref_by_path(
2043 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type,
2044 p_code_flag IN VARCHAR2 := FND_API.G_FALSE
2045 )
2046 RETURN VARCHAR2 -- Position Ref Meaning/Code
2047 IS
2048 --
2049 CURSOR get_path_pos_ref_csr (p_encoded_path IN VARCHAR2) IS
2050 SELECT position_ref_code
2051 FROM AHL_MC_PATH_POSITIONS
2052 WHERE encoded_path_position = p_encoded_path;
2053 --
2054 --Select the default position reference.
2055 CURSOR get_def_path_pos_ref_csr (p_mc_id IN NUMBER,
2056 p_version_number IN NUMBER,
2057 p_position_key IN NUMBER) IS
2058 SELECT rel.position_ref_code
2059 FROM AHL_MC_HEADERS_B hd, AHL_MC_RELATIONSHIPS rel
2060 WHERE rel.position_key = p_position_key
2061 AND hd.mc_header_id = rel.mc_header_id
2062 AND hd.mc_id = p_mc_id
2063 AND hd.version_number = nvl(p_version_number, hd.version_number)
2064 order by hd.version_number desc;
2065 --
2066 l_pos_ref_code VARCHAR2(30);
2067 l_pos_ref_meaning VARCHAR2(80);
2068 l_pos_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
2069 l_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
2070 l_return_val BOOLEAN;
2071 --
2072 BEGIN
2073 --Remove the version numbers if any.
2074 FOR i IN p_path_position_tbl.FIRST..p_path_position_tbl.LAST LOOP
2075 l_pos_tbl(i) := p_path_position_tbl(i);
2076 l_pos_tbl(i).version_number := NULL;
2077 END LOOP;
2078
2079 --Encode the path_position_tbl
2080 l_encoded_path := Encode(l_pos_tbl);
2081
2082 OPEN get_path_pos_ref_csr(l_encoded_path);
2083 FETCH get_path_pos_ref_csr INTO l_pos_ref_code;
2084
2085 --If there are no pos ref defined for path
2086 IF (l_pos_ref_code IS NULL) THEN
2087 OPEN get_def_path_pos_ref_csr(
2088 p_path_position_tbl(p_path_position_tbl.LAST).mc_id,
2089 p_path_position_tbl(p_path_position_tbl.LAST).version_number,
2090 p_path_position_tbl(p_path_position_tbl.LAST).position_key);
2091 FETCH get_def_path_pos_ref_csr INTO l_pos_ref_code;
2092 CLOSE get_def_path_pos_ref_csr;
2093 END IF;
2094 CLOSE get_path_pos_ref_csr;
2095
2096 IF (p_code_flag = FND_API.G_TRUE) THEN
2097 RETURN l_pos_ref_code;
2098 ELSE
2099 --Convert to meaning.
2100 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning('AHL_POSITION_REFERENCE',
2101 l_pos_ref_code,
2102 l_pos_ref_meaning,
2103 l_return_val);
2104
2105 Return l_pos_ref_meaning;
2106 END IF;
2107
2108 END Get_Posref_By_Path;
2109
2110
2111 ---------------------------------------------------------------------
2112 -- Start of Comments --
2113 -- Function name: get_posref_for_uc
2114 -- Type : Private
2115 -- Function : Fetches the position path position ref code
2116 -- Pre-reqs :
2117 -- Parameters :
2118 --
2119 -- get_position_ref_code Parameters:
2120 -- p_uc_header_id IN NUMBER UNIT CONFIG header id
2121 -- p_relationship_id IN NUMBER position of subunit
2122 --
2123 FUNCTION get_posref_for_uc(
2124 p_uc_header_id IN NUMBER,
2125 p_relationship_id IN NUMBER
2126 )
2127 RETURN VARCHAR2 -- Position Ref Meaning
2128 IS
2129 --
2130 --Fetch the unit and unit header info for instance
2131 CURSOR get_uc_headers_csr (p_uc_header_id IN NUMBER) IS
2132 SELECT unit_config_header_id
2133 FROM ahl_unit_config_headers
2134 START WITH unit_config_header_id = p_uc_header_id
2135 CONNECT BY PRIOR parent_uc_header_id = unit_config_header_id;
2136 --
2137 CURSOR get_header_details_csr (p_uc_header_id IN NUMBER) IS
2138 SELECT parent_mc_id, parent_position_key
2139 FROM ahl_uc_header_paths_v
2140 WHERE uc_header_id = p_uc_header_id;
2141 --
2142 CURSOR get_rel_info_csr (p_rel_id IN VARCHAR2) IS
2143 SELECT a.mc_id, b.position_key
2144 FROM AHL_MC_HEADERS_B a, AHL_MC_RELATIONSHIPS b
2145 WHERE a.mc_header_id = b.mc_header_id
2146 AND b.relationship_id = p_rel_id;
2147 --
2148 CURSOR get_path_pos_ref_csr (p_encoded_path IN VARCHAR2) IS
2149 SELECT position_ref_code
2150 FROM AHL_MC_PATH_POSITIONS
2151 WHERE encoded_path_position = p_encoded_path;
2152 --
2153 --Select the default position reference.
2154 CURSOR get_def_path_pos_ref_csr (p_relationship_id IN NUMBER) IS
2155 SELECT rel.position_ref_code
2156 FROM AHL_MC_RELATIONSHIPS rel
2157 WHERE rel.relationship_id = p_relationship_id;
2158 --
2159 l_pos_ref_code VARCHAR2(30);
2160 l_pos_ref_meaning VARCHAR2(80);
2161 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
2162 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
2163 l_index NUMBER;
2164 l_encoded_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
2165 l_return_val BOOLEAN;
2166 l_uc_header NUMBER;
2167 --
2168 BEGIN
2169
2170 l_index := 0;
2171
2172 -- Construct the position path table
2173 OPEN get_uc_headers_csr(p_uc_header_id);
2174 LOOP
2175 FETCH get_uc_headers_csr INTO l_uc_header;
2176 EXIT WHEN get_uc_headers_csr%NOTFOUND;
2177
2178 OPEN get_header_details_csr(l_uc_header);
2179 FETCH get_header_details_csr INTO l_path_rec.mc_id,
2180 l_path_rec.position_key;
2181 CLOSE get_header_details_csr;
2182
2183 --Make it nonversion specific
2184 l_path_rec.version_number := null;
2185
2186 IF (l_path_rec.mc_id is not null AND
2187 l_path_rec.position_key is not null) THEN
2188 l_path_tbl(l_index) := l_path_rec;
2189 l_index := l_index - 1;
2190 END IF;
2191
2192 END LOOP;
2193 CLOSE get_uc_headers_csr;
2194 --If subunit definition
2195 IF (l_path_tbl.COUNT>0) THEN
2196
2197 OPEN get_rel_info_csr(p_relationship_id);
2198 FETCH get_rel_info_csr INTO l_path_rec.mc_id, l_path_rec.position_key;
2199 IF (get_rel_info_csr%FOUND) THEN
2200 l_path_tbl(l_path_tbl.LAST+1) := l_path_rec;
2201 END IF;
2202 CLOSE get_rel_info_csr;
2203
2204 --Encode the modified with new params path_position_tbl
2205 l_encoded_path := Encode(l_path_tbl);
2206
2207 OPEN get_path_pos_ref_csr(l_encoded_path);
2208 FETCH get_path_pos_ref_csr INTO l_pos_ref_code;
2209 CLOSE get_path_pos_ref_csr;
2210
2211 --If there are no pos ref defined for path
2212 IF (l_pos_ref_code IS NULL) THEN
2213 OPEN get_def_path_pos_ref_csr(p_relationship_id);
2214 FETCH get_def_path_pos_ref_csr INTO l_pos_ref_code;
2215 CLOSE get_def_path_pos_ref_csr;
2216 END IF;
2217
2218 ELSE
2219 OPEN get_def_path_pos_ref_csr(p_relationship_id);
2220 FETCH get_def_path_pos_ref_csr INTO l_pos_ref_code;
2221 CLOSE get_def_path_pos_ref_csr;
2222 END IF;
2223
2224 --Convert to meaning.
2225 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning('AHL_POSITION_REFERENCE',
2226 l_pos_ref_code,
2227 l_pos_ref_meaning,
2228 l_return_val);
2229 Return l_pos_ref_meaning;
2230
2231 END Get_Posref_For_UC;
2232
2233 ----------------
2234 ----------------
2235 FUNCTION Encode(
2236 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
2237 )
2238 RETURN VARCHAR2
2239 IS
2240 --
2241 l_path AHL_MC_PATH_POSITIONS.ENCODED_PATH_POSITION%TYPE;
2242 l_path_pos_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
2243 --
2244 BEGIN
2245 l_path := '';
2246
2247 FOR i IN p_path_position_tbl.FIRST..p_path_position_tbl.LAST LOOP
2248 l_path_pos_rec := p_path_position_tbl(i);
2249
2250 --Append the ids.
2251 l_path := l_path || to_char(l_path_pos_rec.mc_id) || G_ID_SEPARATOR;
2252 IF (l_path_pos_rec.version_number IS NULL) THEN
2253 l_path := l_path || '%';
2254 ELSE
2255 l_path := l_path || to_char(l_path_pos_rec.version_number);
2256 END IF;
2257 l_path :=l_path ||G_ID_SEPARATOR ||to_char(l_path_pos_rec.position_key);
2258
2259 --Append the node separators.
2260 IF (i < p_path_position_tbl.LAST) THEN
2261 l_path := l_path || G_NODE_SEPARATOR;
2262 END IF;
2263 END LOOP;
2264
2265 RETURN l_path;
2266
2267 END Encode;
2268
2269 ---------------------------------------------------------------------
2270 -- Start of Comments --
2271 -- Function name: get_encoded_path
2272 -- Type : Private
2273 -- Function : Fetches the position path encoding based on input
2274 -- Pre-reqs :
2275 -- Parameters :
2276 --
2277 -- get_encoded_path Parameters:
2278 -- p_parent_path IN VARCHAR2. encoded parent position path
2279 -- p_mc_id IN NUMBER.
2280 -- p_ver_num IN NUMBER.
2281 -- p_position_key IN NUMBER.
2282 -- p_subconfig_flag IN BOOLEAN indicates whether this is new subconfig
2283 --
2284 FUNCTION get_encoded_path(
2285 p_parent_path IN VARCHAR2,
2286 p_mc_id IN NUMBER,
2287 p_ver_num IN NUMBER,
2288 p_position_key IN NUMBER,
2289 p_subconfig_flag IN VARCHAR2
2290 )
2291 RETURN VARCHAR2
2292 IS
2293 BEGIN
2294
2295 IF (p_subconfig_flag IS NOT NULL AND
2296 p_subconfig_flag = 'T') THEN
2297 RETURN p_parent_path||G_NODE_SEPARATOR||TO_CHAR(p_mc_id) ||G_ID_SEPARATOR||NVL(TO_CHAR(p_ver_num), '%')||G_ID_SEPARATOR|| TO_CHAR(p_position_key);
2298 ELSE
2299 RETURN SUBSTR(p_parent_path,0,INSTR(p_parent_path,G_ID_SEPARATOR,-1)) || p_position_key;
2300 END IF;
2301
2302 END get_encoded_path;
2303
2304
2305 ---------------------------------------------------------------------
2306 -- Start of Comments --
2307 -- Function name: check_pos_ref_path
2308 -- Type : Private
2309 -- Function :
2310 -- Check that the path from instance to to instance has position ref each step
2311 -- and that position ref is not null for all relnships.
2312 -- Pre-reqs :
2313 -- Parameters : p_from_csi_id NUMBER the from instance id
2314 -- p_to_csi_id NUMBER the instance id that it reaches
2315 --
2316 FUNCTION check_pos_ref_path(
2317 p_from_csi_id IN NUMBER,
2318 p_to_csi_id IN NUMBER)
2319 RETURN BOOLEAN
2320 IS
2321 --
2322 CURSOR check_pos_ref_csr (p_csi_instance_id IN NUMBER,
2323 p_to_csi_instance_id IN NUMBER) IS
2324 SELECT 'X'
2325 FROM csi_ii_relationships csi_ii
2326 WHERE csi_ii.object_id = p_to_csi_instance_id
2327 START WITH csi_ii.subject_id = p_csi_instance_id
2328 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2329 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2330 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2331 CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
2332 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2333 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2334 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2335 AND CSI_II.POSITION_REFERENCE IS NOT NULL;
2336 --
2337 l_dummy VARCHAR2(1);
2338 BEGIN
2339 --Check the position reference is valid for entire path
2340 OPEN check_pos_ref_csr (p_from_csi_id,
2341 p_to_csi_id);
2342 FETCH check_pos_ref_csr INTO l_dummy;
2343 IF (check_pos_ref_csr%NOTFOUND) THEN
2344 CLOSE check_pos_ref_csr;
2345 RETURN false;
2346 ELSE
2347 CLOSE check_pos_ref_csr;
2348 RETURN true;
2349 END IF;
2350
2351 END Check_pos_ref_path;
2352
2353 ---------------------------------------------------------------------
2354 -- Start of Comments --
2355 -- Function name: check_pos_ref_path_char
2356 -- Type : Private
2357 -- Function : Calls private function Check_pos_ref_path and returns
2358 -- value as FND_API.G_TRUE for Boolean TRUE and
2359 -- FND_API.G_FALSE for Boolean False.
2360 -- Pre-reqs :
2361 -- Parameters : p_from_csi_id NUMBER the from instance id
2362 -- p_to_csi_id NUMBER the instance id that it reaches
2363 --
2364 FUNCTION check_pos_ref_path_char(
2365 p_from_csi_id IN NUMBER,
2366 p_to_csi_id IN NUMBER)
2367 RETURN VARCHAR2 IS
2368 BEGIN
2369 IF (AHL_MC_PATH_POSITION_PVT.CHECK_POS_REF_PATH(p_from_csi_id,p_to_csi_id)) THEN
2370 RETURN 'T';
2371 ELSE
2372 RETURN 'F';
2373 END IF;
2374 END check_pos_ref_path_char;
2375
2376 -----------------------------
2377 -- Start of Comments --
2378 -- Procedure name : Map_Instance_To_Pos_id
2379 -- Type : Private
2380 -- Function : For an instance map the position path and return
2381 -- version specific path_pos_id. Reverse of the Get_Pos_Instance function
2382 -- Pre-reqs :
2383 -- Parameters :
2384 --
2385 -- Map_Instance_To_Pos_id Parameters:
2386 -- p_csi_item_instance_id IN NUMBER Required. instance for the pos
2387 -- p_relationship_id IN NUMBER Optional. Used for empty position
2388 -- x_path_position_id OUT NUMBER the existing or new path pos id
2389 --
2390 -- End of Comments.
2391
2392 PROCEDURE Map_Instance_To_Pos_ID (
2393 p_api_version IN NUMBER,
2394 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2395 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2396 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2397 x_return_status OUT NOCOPY VARCHAR2,
2398 x_msg_count OUT NOCOPY NUMBER,
2399 x_msg_data OUT NOCOPY VARCHAR2,
2400 p_csi_item_instance_id IN NUMBER,
2401 p_relationship_id IN NUMBER := FND_API.G_MISS_NUM,
2402 x_path_position_id OUT NOCOPY NUMBER)
2403 IS
2404 --
2405 --Fetch the unit and unit header info for each instance
2406 CURSOR get_uc_headers_csr (p_csi_instance_id IN NUMBER) IS
2407 SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
2408 FROM ahl_uc_header_paths_v up
2409 WHERE up.csi_instance_id = p_csi_instance_id;
2410 --
2411 --Traverse up and fetch all unit instance ids
2412 CURSOR get_unit_instance_csr (p_csi_instance_id IN NUMBER) IS
2413 SELECT csi.object_id
2414 FROM csi_ii_relationships csi
2415 WHERE csi.object_id IN
2416 ( SELECT csi_item_instance_id
2417 FROM ahl_unit_config_headers
2418 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
2419 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
2420 START WITH csi.subject_id = p_csi_instance_id
2421 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2422 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2423 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2424 CONNECT BY csi.subject_id = PRIOR csi.object_id
2425 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2426 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2427 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2428 AND CSI.POSITION_REFERENCE IS NOT NULL;
2429 --
2430 --Fetches lowest level info
2431 CURSOR get_last_uc_rec_csr (p_csi_instance_id IN NUMBER) IS
2432 SELECT hdr.mc_id, hdr.version_number, rel.position_key
2433 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
2434 csi_ii_relationships csi_ii
2435 WHERE csi_ii.subject_id = p_csi_instance_id
2436 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2437 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2438 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2439 AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
2440 AND REL.mc_header_id = HDR.mc_header_id;
2441 --
2442 -- Changed by jaramana on July 13, 2006 to fix FP of bug 5368714
2443 -- Do not join with csi_unit_instances_v for filtering out non top level nodes
2444 -- since it does not take dates into consideration
2445 /*
2446 CURSOR get_top_unit_inst_csr (p_csi_instance_id IN NUMBER) IS
2447 SELECT hdr.mc_id, hdr.version_number, rel.position_key
2448 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
2449 ahl_unit_config_headers uch, csi_unit_instances_v csi_u
2450 WHERE uch.csi_item_instance_id = p_csi_instance_id
2451 AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2452 AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2453 AND hdr.mc_header_id = uch.master_config_id
2454 AND rel.mc_header_id = hdr.mc_header_id
2455 AND rel.parent_relationship_id IS NULL
2456 AND uch.csi_item_instance_id = csi_u.instance_id;
2457 */
2458 CURSOR get_top_unit_inst_csr (p_csi_instance_id IN NUMBER) IS
2459 SELECT hdr.mc_id, hdr.version_number, rel.position_key
2460 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
2461 ahl_unit_config_headers uch
2462 WHERE uch.csi_item_instance_id = p_csi_instance_id
2463 AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2464 AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2465 AND hdr.mc_header_id = uch.master_config_id
2466 AND rel.mc_header_id = hdr.mc_header_id
2467 AND rel.parent_relationship_id IS NULL
2468 AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
2469 WHERE CIR.SUBJECT_ID = uch.csi_item_instance_id AND
2470 CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' AND
2471 TRUNC(nvl(CIR.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate) AND
2472 TRUNC(nvl(CIR.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate));
2473
2474 --
2475 --Fetch relationship_id's params
2476 --Making sure that the relationship_id is direct child of the parent instance
2477 -- position.
2478 CURSOR get_mc_relationship_csr (p_relationships_id IN NUMBER,
2479 p_parent_instance_id IN NUMBER) IS
2480 SELECT hdr.mc_id, hdr.version_number, rel.position_key
2481 FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel
2482 WHERE hdr.mc_header_id = rel.mc_header_id
2483 AND rel.relationship_id = p_relationship_id
2484 --Jerry rewrite the following condition on 03/03/2005 in order to fix bug 4090856
2485 --after verifying the bug fix on scmtsb2
2486 AND rel.relationship_id IN (SELECT relationship_id
2487 FROM ahl_mc_relationships
2488 WHERE mc_header_id = (SELECT mc_header_id
2489 FROM ahl_mc_relationships
2490 WHERE relationship_id = (SELECT to_number(position_reference)
2491 FROM csi_ii_relationships
2492 WHERE subject_id = p_parent_instance_id
2493 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2494 AND TRUNC(nvl(ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2495 AND TRUNC(nvl(ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)))
2496 OR mc_header_id = (SELECT master_config_id
2497 FROM ahl_unit_config_headers
2498 WHERE csi_item_instance_id = p_parent_instance_id
2499 AND TRUNC(nvl(ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)));
2500
2501 /*
2502 AND rel.parent_relationship_id IN
2503 ( SELECT r.relationship_id
2504 FROM AHL_MC_RELATIONSHIPS r, AHL_UNIT_CONFIG_HEADERS uch
2505 WHERE uch.csi_item_instance_id = p_parent_instance_id
2506 AND uch.master_config_id = r.mc_header_id
2507 --AND r.parent_relationship_id IS NULL
2508 --Jerry commented out the above condition on 01/14/2005 to fix bug 4090856
2509 AND TRUNC(nvl(uch.active_end_date, sysdate+1)) > TRUNC(sysdate)
2510 UNION ALL
2511 SELECT TO_NUMBER(CSI_II.POSITION_REFERENCE)
2512 FROM csi_ii_relationships csi_ii
2513 WHERE csi_ii.subject_id = p_parent_instance_id
2514 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2515 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2516 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2517 UNION ALL
2518 SELECT subrel.relationship_id
2519 FROM ahl_mc_config_relations crel, ahl_mc_relationships subrel,
2520 csi_ii_relationships csi_ii
2521 WHERE csi_ii.subject_id = p_parent_instance_id
2522 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2523 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
2524 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
2525 AND crel.relationship_id = TO_NUMBER(CSI_II.POSITION_REFERENCE)
2526 AND crel.mc_header_id = subrel.mc_header_id
2527 AND subrel.parent_relationship_id IS NULL);
2528 */
2529
2530 l_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
2531 l_path_rec AHL_MC_PATH_POSITION_PVT.Path_Position_Rec_Type;
2532 l_index NUMBER;
2533 l_api_version CONSTANT NUMBER := 1.0;
2534 l_api_name CONSTANT VARCHAR2(30) := 'Map_Instance_To_Pos_Id';
2535 l_unit_csi_id NUMBER;
2536 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.' || g_pkg_name || '.' || l_api_name;
2537
2538 --
2539 BEGIN
2540 -- Standard start of API savepoint
2541 SAVEPOINT Map_Instance_To_Pos_ID_pvt;
2542
2543 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2544 fnd_log.string(fnd_log.level_procedure, l_full_name||'.begin', 'At the start of PLSQL procedure. p_csi_item_instance_id = ' || p_csi_item_instance_id ||
2545 ', p_relationship_id = ' || p_relationship_id);
2546 END IF;
2547 -- Initialize Procedure return status to success
2548 x_return_status := FND_API.G_RET_STS_SUCCESS;
2549 x_path_position_id := null;
2550
2551 -- Standard call to check for call compatibility
2552 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2553 G_PKG_NAME) THEN
2554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2555 END IF;
2556
2557 -- Initialize message list if p_init_msg_list is set to TRUE
2558 IF FND_API.To_Boolean(p_init_msg_list) THEN
2559 FND_MSG_PUB.Initialize;
2560 END IF;
2561
2562 --Fetch the position informations for the instance
2563 OPEN get_last_uc_rec_csr(p_csi_item_instance_id);
2564 FETCH get_last_uc_rec_csr INTO l_path_rec.mc_id,
2565 l_path_rec.version_number,
2566 l_path_rec.position_key;
2567 IF (get_last_uc_rec_csr%FOUND) THEN
2568 l_path_tbl(1) := l_path_rec;
2569
2570 --Now fetch the position paths which match at higher levels.
2571 l_index := 0;
2572 --Fetch the header rec info for the instance
2573 OPEN get_unit_instance_csr(p_csi_item_instance_id);
2574 LOOP
2575 FETCH get_unit_instance_csr INTO l_unit_csi_id;
2576 EXIT WHEN get_unit_instance_csr%NOTFOUND;
2577
2578 OPEN get_uc_headers_csr(l_unit_csi_id);
2579 FETCH get_uc_headers_csr INTO l_path_rec.mc_id,
2580 l_path_rec.version_number,
2581 l_path_rec.position_key;
2582 CLOSE get_uc_headers_csr;
2583
2584 --Add the path up the tree, decrementing index for each node.
2585 IF (l_path_rec.mc_id is not null AND
2586 l_path_rec.position_key is not null) THEN
2587 l_path_tbl(l_index) := l_path_rec;
2588 l_index := l_index - 1;
2589 END IF;
2590 END LOOP;
2591 CLOSE get_unit_instance_csr;
2592
2593 --if not position node then check if instance is the top unit node
2594 ELSE
2595 --Fetch the position informations for the unit instance
2596 OPEN get_top_unit_inst_csr(p_csi_item_instance_id);
2597 FETCH get_top_unit_inst_csr INTO l_path_rec.mc_id,
2598 l_path_rec.version_number,
2599 l_path_rec.position_key;
2600 IF (get_top_unit_inst_csr%FOUND) THEN
2601 l_path_tbl(1) := l_path_rec;
2602 END IF;
2603 CLOSE get_top_unit_inst_csr;
2604 END IF;
2605 CLOSE get_last_uc_rec_csr;
2606
2607
2608 --For the empty position, build path information for last node of path.
2609 IF (p_relationship_id <> FND_API.G_MISS_NUM AND
2610 p_relationship_id IS NOT NULL) THEN
2611 OPEN get_mc_relationship_csr (p_relationship_id, p_csi_item_instance_id);
2612 FETCH get_mc_relationship_csr INTO l_path_rec.mc_id,
2613 l_path_rec.version_number,
2614 l_path_rec.position_key;
2615 IF (get_mc_relationship_csr%FOUND) THEN
2616
2617 --Either add a new row or replace position key of last row.
2618 IF (l_path_rec.mc_id = l_path_tbl(l_path_tbl.LAST).mc_id AND
2619 l_path_rec.version_number =
2620 NVL(l_path_tbl(l_path_tbl.LAST).version_number,
2621 l_path_rec.version_number)) THEN
2622 l_path_tbl(l_path_tbl.LAST) := l_path_rec;
2623 ELSE
2624 l_path_tbl(l_path_tbl.LAST+1) := l_path_rec;
2625 END IF;
2626 END IF;
2627 CLOSE get_mc_relationship_csr;
2628 END IF;
2629
2630 IF (l_path_tbl.COUNT > 0) THEN
2631 --Create position with generated instance path position id
2632 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)THEN
2633 fnd_log.string(fnd_log.level_event, l_full_name, 'About to call Create_Position_ID API. l_path_tbl.COUNT = ' || l_path_tbl.COUNT);
2634 end if;
2635
2636 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 21-Dec-2007
2637 -- There is no need to call the API Create_Position_ID, if there are pending validation errors.
2638 x_msg_count := FND_MSG_PUB.count_msg;
2639 IF x_msg_count > 0 THEN
2640 RAISE FND_API.G_EXC_ERROR;
2641 END IF;
2642
2643 Create_Position_ID (
2644 p_api_version => 1.0,
2645 p_init_msg_list => FND_API.G_TRUE,
2646 p_commit => FND_API.G_FALSE,
2647 p_path_position_tbl => l_path_tbl,
2648 p_position_ref_meaning => FND_API.G_MISS_CHAR,
2649 p_position_ref_code => FND_API.G_MISS_CHAR,
2650 x_position_id => x_path_position_id,
2651 x_return_status => x_return_status,
2652 x_msg_count => x_msg_count,
2653 x_msg_data => x_msg_data);
2654 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)THEN
2655 fnd_log.string(fnd_log.level_event, l_full_name, 'Returned from call to Create_Position_ID API. x_return_status = ' || x_return_status ||
2656 ', x_position_id = ' || x_path_position_id);
2657 end if;
2658 END IF;
2659
2660 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2661 fnd_log.string(fnd_log.level_procedure, l_full_name||'.end', 'At the end of PLSQL procedure. About to commit work.');
2662 END IF;
2663 -- Standard check of p_commit
2664 IF FND_API.TO_BOOLEAN(p_commit) THEN
2665 COMMIT WORK;
2666 END IF;
2667
2668 -- Standard call to get message count and if count is 1, get message info
2669 FND_MSG_PUB.Count_And_Get
2670 ( p_count => x_msg_count,
2671 p_data => x_msg_data,
2672 p_encoded => fnd_api.g_false
2673 );
2674 EXCEPTION
2675 WHEN FND_API.G_EXC_ERROR THEN
2676 Rollback to Map_Instance_To_Pos_ID_pvt;
2677 x_return_status := FND_API.G_RET_STS_ERROR;
2678 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2679 p_data => x_msg_data,
2680 p_encoded => fnd_api.g_false);
2681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2682 Rollback to Map_Instance_To_Pos_ID_pvt;
2683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2684 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2685 p_data => x_msg_data,
2686 p_encoded => fnd_api.g_false);
2687 WHEN OTHERS THEN
2688 Rollback to Map_Instance_To_Pos_ID_pvt;
2689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2690 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
2691 p_procedure_name => l_api_name,
2692 p_error_text => SQLERRM);
2693 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2694 p_data => x_msg_data,
2695 p_encoded => fnd_api.g_false);
2696
2697 END Map_Instance_To_Pos_Id;
2698
2699 ---------------------------------------------------------------------
2700 -- Start of Comments --
2701 -- Function name: Is_Position_Serial_Controlled
2702 -- Type : Private
2703 -- Function : Cretaed for FP OGMA Issue# 105 - Non-Serialized Item Maintenance.
2704 -- Checks whether a position accepts a serialized item instance or not.
2705 -- Returns 'Y' if item group attached to the position has first associated item as serialized.
2706 -- Returns 'N' otherwise.
2707 -- Pre-reqs :
2708 -- Parameters : p_relationship_id NUMBER relationship id
2709 -- p_path_position_id NUMBER path posiiton id
2710 --
2711 -- If relationship id is passed, it will be taken to determine the result.
2712 -- Position id will be used only when relationship id is NULL.
2713 --
2714
2715 FUNCTION Is_Position_Serial_Controlled(
2716 p_relationship_id IN NUMBER,
2717 p_path_position_id IN NUMBER
2718 ) RETURN VARCHAR2 IS
2719 --
2720 -- for a given path position id, get the mc id and version no from ahl_mc_path_position_nodes
2721 -- from the mc id and version no, get the mc header id from ahl_mc_headers_b
2722 -- from the mc header id and position key (from ahl_mc_path_position_nodes), get the relationship id from ahl_mc_relationships
2723 CURSOR get_rel_id_csr (p_path_position_id NUMBER) IS
2724 SELECT mcr.relationship_id
2725 FROM ahl_mc_path_position_nodes mpn, ahl_mc_headers_b mch,
2726 ahl_mc_relationships mcr
2727 WHERE mpn.path_position_id = p_path_position_id
2728 AND mpn.sequence = (
2729 SELECT MAX(sequence)
2730 FROM ahl_mc_path_position_nodes
2731 WHERE path_position_id = mpn.path_position_id
2732 )
2733 AND mpn.mc_id = mch.mc_id
2734 AND mch.version_number = NVL(mpn.version_number, mch.version_number)
2735 AND mcr.mc_header_id = mch.mc_header_id
2736 AND mcr.position_key = mpn.position_key;
2737
2738 -- for a given relationship id, get the item group id from ahl_mc_relationships
2739 CURSOR get_item_group_id_csr (p_relationship_id NUMBER) IS
2740 SELECT item_group_id
2741 FROM ahl_mc_relationships
2742 WHERE relationship_id = p_relationship_id;
2743
2744 -- for a given item group id, get the serial control code of the associated items
2745 CURSOR get_serial_cntrl_code_csr (p_item_group_id NUMBER) IS
2746 SELECT mtl.serial_number_control_code
2747 FROM ahl_item_associations_b aia, mtl_system_items_b mtl
2748 WHERE aia.item_group_id = p_item_group_id
2749 AND aia.inventory_item_id = mtl.inventory_item_id
2750 AND aia.inventory_org_id = mtl.organization_id
2751 AND aia.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
2752 ORDER BY aia.item_association_id;
2753
2754 --
2755 l_api_name CONSTANT VARCHAR2(30) := 'Is_Position_Serial_Controlled';
2756 l_full_name CONSTANT VARCHAR2(70) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2757
2758 l_ret_val VARCHAR2(1) := 'Y';
2759 l_relationship_id NUMBER := p_relationship_id;
2760 l_item_group_id NUMBER;
2761 l_serial_number_control_code NUMBER;
2762 --
2763 BEGIN
2764 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2765 FND_LOG.string(FND_LOG.level_procedure,l_full_name||'.begin','Start of the API.'||
2766 ' Input parameters p_relationship_id => '||p_relationship_id||
2767 ', p_path_position_id => '||p_path_position_id);
2768 END IF;
2769
2770 IF (l_relationship_id IS NULL) THEN
2771 -- get the relationship id from the given path position id
2772 OPEN get_rel_id_csr(p_path_position_id);
2773 FETCH get_rel_id_csr INTO l_relationship_id;
2774 CLOSE get_rel_id_csr;
2775
2776 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2777 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
2778 ' After cursor call l_relationship_id => '||l_relationship_id);
2779 END IF;
2780 END IF;
2781
2782 -- do the rest only if relationship id is not NULL
2783 IF (l_relationship_id IS NOT NULL) THEN
2784 -- get the item group id
2785 OPEN get_item_group_id_csr(l_relationship_id);
2786 FETCH get_item_group_id_csr INTO l_item_group_id;
2787
2788 IF (get_item_group_id_csr%NOTFOUND) THEN
2789 -- relationship id is invalid
2790 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2791 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
2792 ' Relationship id is invalid.');
2793 END IF;
2794 END IF;
2795
2796 CLOSE get_item_group_id_csr;
2797
2798 -- check for the fetched item group id
2799 IF (l_item_group_id IS NOT NULL) THEN
2800 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2801 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
2802 ' Fetched item group id => '||l_item_group_id);
2803 END IF;
2804
2805 -- the MC position doesn't have subconfigurations attached
2806 -- get the serial control code for the first associated item
2807 OPEN get_serial_cntrl_code_csr(l_item_group_id);
2808 FETCH get_serial_cntrl_code_csr INTO l_serial_number_control_code;
2809 CLOSE get_serial_cntrl_code_csr;
2810
2811 -- check for the fetched serial control_code
2812 IF (l_serial_number_control_code IS NOT NULL) THEN
2813 -- if 1, then it is non-serialized; else serialized
2814 IF (l_serial_number_control_code = 1) THEN
2815 l_ret_val := 'N';
2816 END IF;
2817 END IF;
2818 END IF;
2819 END IF; -- relationship id check
2820
2821 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2822 FND_LOG.string(FND_LOG.level_procedure,l_full_name||'.end','End of the API with return value => '||l_ret_val);
2823 END IF;
2824
2825 RETURN l_ret_val;
2826 END Is_Position_Serial_Controlled;
2827
2828 End AHL_MC_PATH_POSITION_PVT;
[Home] [Help]