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