[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM ahl_mc_headers_b hd, ahl_mc_relationships rel
WHERE hd.mc_header_id = rel.mc_header_id
AND hd.mc_id = p_mc_id
AND hd.version_number = nvl(p_ver_num, hd.version_number)
AND rel.position_key = p_pos_key;
SELECT 'X'
FROM ahl_mc_config_relations rel, ahl_mc_headers_b hd
WHERE rel.mc_header_id = hd.mc_header_id
AND hd.mc_id = p_child_mc_id
AND hd.version_number = nvl(p_child_ver_num, hd.version_number)
AND rel.relationship_id IN
(SELECT r.relationship_id
FROM ahl_mc_relationships r, ahl_mc_headers_b h
WHERE h.mc_header_id = r.mc_header_id
AND h.mc_id = p_mc_id
AND h.version_number = nvl(p_ver_num, h.version_number)
AND r.position_key = p_pos_key);
SELECT pos.path_position_id
FROM ahl_mc_path_positions pos
WHERE pos.encoded_path_position = p_encoded_path;
SELECT distinct r2.position_key
FROM ahl_mc_relationships r1, ahl_mc_relationships r2, ahl_mc_headers_b hdr
WHERE r1.parent_relationship_id = r2.parent_relationship_id
AND r1.position_key <> r2.position_key
AND r1.position_key = p_poskey
AND r1.mc_header_id = hdr.mc_header_id
AND hdr.mc_id = p_mc_id;
SELECT pos.path_pos_common_id
FROM AHL_MC_PATH_POSITIONS pos
WHERE pos.encoded_path_position like p_encoded_path
AND p_size = (select COUNT(path_position_node_id) from
AHL_MC_PATH_POSITION_NODES where path_position_id = pos.path_position_id);
SELECT ahl_mc_path_positions_s.nextval
FROM dual;
UPDATE ahl_mc_path_positions SET
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
POSITION_REF_CODE = l_position_ref_code
WHERE PATH_POSITION_ID = x_position_id;
INSERT INTO ahl_mc_path_positions(
PATH_POSITION_ID,
PATH_POS_COMMON_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ENCODED_PATH_POSITION,
POSITION_REF_CODE,
VER_SPEC_SCORE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) VALUES (
l_position_id,
l_common_id,
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_encoded_path,
l_position_ref_code,
l_ver_spec_score,
NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
)
RETURNING path_position_id INTO x_position_id;
INSERT INTO ahl_mc_path_position_nodes(
PATH_POSITION_NODE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PATH_POSITION_ID,
SEQUENCE,
MC_ID,
VERSION_NUMBER,
POSITION_KEY,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) VALUES (
ahl_mc_path_position_nodes_s.nextval,
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
x_position_id,
l_index,
p_path_position_tbl(i).mc_id,
p_path_position_tbl(i).version_number,
p_path_position_tbl(i).position_key,
p_path_position_tbl(i).attribute_category ,
p_path_position_tbl(i).attribute1 ,
p_path_position_tbl(i).attribute2 ,
p_path_position_tbl(i).attribute3 ,
p_path_position_tbl(i).attribute4 ,
p_path_position_tbl(i).attribute5 ,
p_path_position_tbl(i).attribute6 ,
p_path_position_tbl(i).attribute7 ,
p_path_position_tbl(i).attribute8 ,
p_path_position_tbl(i).attribute9 ,
p_path_position_tbl(i).attribute10 ,
p_path_position_tbl(i).attribute11 ,
p_path_position_tbl(i).attribute12 ,
p_path_position_tbl(i).attribute13 ,
p_path_position_tbl(i).attribute14 ,
p_path_position_tbl(i).attribute15 );
SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
FROM ahl_uc_header_paths_v up
WHERE up.csi_instance_id = p_csi_instance_id;
SELECT csi.object_id
FROM csi_ii_relationships csi
WHERE csi.object_id IN
( SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
START WITH csi.subject_id = p_csi_instance_id
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
CONNECT BY csi.subject_id = PRIOR csi.object_id
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND CSI.POSITION_REFERENCE IS NOT NULL;
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
csi_ii_relationships csi_ii
WHERE csi_ii.subject_id = p_csi_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
AND REL.mc_header_id = HDR.mc_header_id;
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
ahl_unit_config_headers uch, csi_unit_instances_v csi_u
WHERE uch.csi_item_instance_id = p_csi_instance_id
AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND hdr.mc_header_id = uch.master_config_id
AND rel.mc_header_id = hdr.mc_header_id
AND rel.parent_relationship_id IS NULL
AND uch.csi_item_instance_id = csi_u.instance_id;
SELECT pos.path_position_id
FROM AHL_MC_PATH_POSITIONS pos
WHERE p_encoded_path LIKE pos.encoded_path_position
AND p_size = (select COUNT(path_position_node_id) from
AHL_MC_PATH_POSITION_NODES where path_position_id = pos.path_position_id);
INSERT INTO AHL_APPLICABLE_INSTANCES (csi_item_instance_id,
position_id)
VALUES (p_csi_item_instance_id, l_position_id);
INSERT INTO AHL_APPLICABLE_INSTANCES (csi_item_instance_id,
position_id)
VALUES (p_csi_item_instance_id, l_position_id);
INSERT INTO AHL_APPLICABLE_INSTANCES (csi_item_instance_id,
position_id)
VALUES (p_csi_item_instance_id, l_position_id);
SELECT path.sequence, path.mc_id, path.version_number, path.position_key
FROM AHL_MC_PATH_POSITION_NODES path
WHERE path.path_position_id = p_position_id
order by sequence;
SELECT 'X'
FROM AHL_MC_RELATIONSHIPS rel, AHL_MC_HEADERS_B HDR
WHERE HDR.mc_header_id = REL.mc_header_id
AND REL.parent_relationship_id is NULL
AND REL.position_key = p_position_key
AND HDR.mc_id = p_mc_id
AND HDR.version_number = nvl(p_ver_num, HDR.version_number);
v_Select VARCHAR2(4000);
v_RowsInserted INTEGER;
v_Select := ' SELECT v'||l_path_tbl.LAST||'.csi_instance_id ';
fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_Select --'||v_Select);
v_Stmt := 'INSERT INTO AHL_APPLICABLE_INSTANCES '||
' SELECT uch.csi_instance_id ,'|| p_position_id ||
' FROM AHL_UC_HEADER_PATHS_V uch '||
' WHERE uch.position_key = :pos_key'||l_path_tbl.LAST
||' AND uch.csi_instance_id in ( '
|| v_Select || v_From || v_Where || ' ) ';
v_Stmt := 'INSERT INTO AHL_APPLICABLE_INSTANCES '||
' SELECT csi_ii.subject_id ,'|| p_position_id ||
' FROM ahl_mc_relationships rel, csi_ii_relationships csi_ii '||
' WHERE TO_NUMBER(CSI_II.POSITION_REFERENCE)=REL.RELATIONSHIP_ID '
||' AND REL.position_key = :pos_key'||l_path_tbl.LAST
||' START WITH csi_ii.object_id IN ( '
|| v_Select || v_From || v_Where || ' ) '
|| ' CONNECT BY PRIOR csi_ii.subject_id = csi_ii.object_id '
||' AND CSI_II.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF'' '
||' AND CSI_II.POSITION_REFERENCE IS NOT NULL '
||' AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate) '
||' AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate) ';
v_RowsInserted := DBMS_SQL.EXECUTE(v_CursorID);
fnd_log.string(fnd_log.level_statement,l_full_name,'-- v_RowsInserted --'||v_RowsInserted);
SELECT 'X'
FROM AHL_UNIT_CONFIG_HEADERS uch
WHERE uch.csi_item_instance_id = p_instance_id;
SELECT path.sequence, path.mc_id, path.version_number, path.position_key
FROM AHL_MC_PATH_POSITION_NODES path
WHERE path.path_position_id = p_position_id
order by sequence;
SELECT 'X'
FROM AHL_MC_HEADERS_B hdr, AHL_UNIT_CONFIG_HEADERS uch
WHERE uch.csi_item_instance_id = p_csi_ii_id
AND uch.master_config_id = hdr.mc_header_id
AND HDR.mc_id = p_mc_id
AND HDR.version_number = nvl(p_ver_num, HDR.version_number);
SELECT csi_instance_id
FROM AHL_UC_HEADER_PATHS_V
WHERE parent_instance_id = p_start_csi_ii_id
AND parent_position_key = p_parent_pos_key
AND mc_id = p_child_mc_id
AND mc_version_number = nvl(p_child_mc_ver_num, mc_version_number);
SELECT csi_ii.subject_id, csi_ii.object_id, TO_NUMBER(csi_ii.position_reference)
FROM csi_ii_relationships csi_ii
WHERE TO_NUMBER(CSI_II.POSITION_REFERENCE) in (select REL.RELATIONSHIP_ID
from ahl_mc_relationships rel
where REL.position_key = p_position_key)
START WITH csi_ii.object_id = p_lowest_uc_ii_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
--Jerry 03/03/2005 Added the following condition for fixing bug 4090856
AND CSI_II.POSITION_REFERENCE IS NOT NULL
CONNECT BY PRIOR csi_ii.subject_id = csi_ii.object_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND CSI_II.POSITION_REFERENCE IS NOT NULL
UNION ALL
SELECT uch.csi_item_instance_id, csi_ii.object_id, TO_NUMBER(csi_ii.position_reference)
FROM AHL_MC_RELATIONSHIPS rel, AHL_UNIT_CONFIG_HEADERS UCH, CSI_II_RELATIONSHIPS csi_ii
WHERE UCH.master_config_id = REL.mc_header_id
AND REL.parent_relationship_id is NULL
AND REL.position_key = p_position_key
AND uch.csi_item_instance_id = p_lowest_uc_ii_id
AND uch.csi_item_instance_id = csi_ii.subject_id (+)
AND CSI_II.RELATIONSHIP_TYPE_CODE (+) = 'COMPONENT-OF'
-- Changed by jaramana on July 13, 2006 to fix FP of bug 5368714
-- Make the active start date and active end date clauses also to be outer joins
-- AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
-- AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
SELECT rel.relationship_id, prel.position_key
FROM AHL_MC_RELATIONSHIPS rel, AHL_MC_HEADERS_B hdr, AHL_MC_RELATIONSHIPS prel
WHERE prel.relationship_id = rel.parent_relationship_id
AND rel.position_key = p_position_key
AND hdr.mc_header_id = rel.mc_header_id
AND hdr.mc_id = p_mc_id
AND hdr.version_number = nvl(p_version_number, hdr.version_number)
ORDER BY hdr.version_number desc;
SELECT 'X'
FROM csi_ii_relationships csi_ii
WHERE csi_ii.object_id = p_to_csi_instance_id
START WITH csi_ii.subject_id = p_csi_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
--Jerry 03/03/2005 Added the following condition for fixing bug 4090856
AND CSI_II.POSITION_REFERENCE IS NOT NULL
CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND CSI_II.POSITION_REFERENCE IS NOT NULL;
SELECT header.config_status_code, header.config_status_meaning
FROM ahl_mc_headers_v header
WHERE header.mc_header_id = p_header_id;
SELECT mc_id
FROM ahl_mc_headers_b
WHERE mc_header_id = p_mc_header_id;
SELECT pnodes.path_position_id
FROM ahl_mc_path_position_nodes pnodes, ahl_mc_headers_b hdr
WHERE pnodes.sequence = 0
AND pnodes.mc_id = hdr.mc_id
AND pnodes.version_number = hdr.version_number
AND hdr.mc_header_id = p_header_id;
SELECT pnodes.path_position_id
FROM ahl_mc_path_position_nodes pnodes, ahl_mc_headers_b hdr
WHERE pnodes.sequence = 0
AND pnodes.mc_id = hdr.mc_id
AND pnodes.version_number IS NULL
AND hdr.mc_header_id = p_header_id;
SELECT mc_id, version_number
FROM ahl_mc_headers_b
WHERE mc_header_id = p_mc_header_id;
SELECT *
FROM ahl_mc_path_position_nodes
WHERE path_position_id = p_position_id
order by sequence;
SELECT position_ref_code
FROM ahl_mc_path_positions
WHERE path_position_id = p_position_id;
PROCEDURE Delete_Positions_For_MC (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_mc_header_id IN NUMBER)
IS
--
CURSOR check_mc_status_csr (p_header_id IN NUMBER) IS
SELECT config_status_code, config_status_meaning
FROM ahl_mc_headers_v header
WHERE header.mc_header_id = p_header_id;
SELECT count(*)
FROM ahl_mc_headers_b header
WHERE header.mc_id = (select mc_id FROM ahl_mc_headers_b
where mc_header_id = p_header_id);
SELECT path.path_position_id
FROM AHL_MC_PATH_POSITION_NODES path, AHL_MC_HEADERS_B headers
WHERE path.MC_ID = headers.mc_id
AND path.sequence = 0
AND path.version_number = headers.version_number
AND headers.mc_header_id = p_mc_header_id;
SELECT path.path_position_id
FROM AHL_MC_PATH_POSITION_NODES path, AHL_MC_HEADERS_B headers
WHERE path.MC_ID = headers.mc_id
AND path.sequence = 0
AND path.version_number IS NULL
AND headers.mc_header_id = p_mc_header_id;
SELECT 'X'
FROM AHL_MC_RULE_STATEMENTS
WHERE (subject_ID = p_position_id
AND subject_type = 'POSITION')
OR (object_id = p_position_id
AND (object_type = 'ITEM_AS_POSITION'
OR object_type = 'CONFIG_AS_POSITION'));
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Positions_For_Mc';
SAVEPOINT Delete_Positions_For_Mc_pvt;
DELETE FROM AHL_MC_PATH_POSITION_NODES
WHERE path_position_id = l_position_id;
DELETE FROM AHL_MC_PATH_POSITIONS
WHERE path_position_id = l_position_id;
DELETE FROM AHL_MC_PATH_POSITION_NODES
WHERE path_position_id = l_position_id;
DELETE FROM AHL_MC_PATH_POSITIONS
WHERE path_position_id = l_position_id;
Rollback to Delete_Positions_For_Mc_pvt;
Rollback to Delete_Positions_For_Mc_pvt;
Rollback to Delete_Positions_For_Mc_pvt;
END Delete_Positions_For_MC;
SELECT position_ref_code
FROM AHL_MC_PATH_POSITIONS
WHERE path_position_id = p_position_id;
SELECT rel.position_ref_code
FROM AHL_MC_HEADERS_B hd, AHL_MC_RELATIONSHIPS rel,
AHL_MC_PATH_POSITION_NODES pnodes
WHERE hd.mc_header_id = rel.mc_header_id
AND rel.position_key = pnodes.position_key
AND hd.mc_id = pnodes.mc_id
AND pnodes.sequence = (SELECT MAX(sequence) FROM AHL_MC_PATH_POSITION_NODES
WHERE path_position_id = p_position_id)
AND pnodes.path_position_id = p_position_id
order by hd.version_number desc;
SELECT position_ref_code
FROM AHL_MC_PATH_POSITIONS
WHERE encoded_path_position = p_encoded_path;
SELECT rel.position_ref_code
FROM AHL_MC_HEADERS_B hd, AHL_MC_RELATIONSHIPS rel
WHERE rel.position_key = p_position_key
AND hd.mc_header_id = rel.mc_header_id
AND hd.mc_id = p_mc_id
AND hd.version_number = nvl(p_version_number, hd.version_number)
order by hd.version_number desc;
SELECT unit_config_header_id
FROM ahl_unit_config_headers
START WITH unit_config_header_id = p_uc_header_id
CONNECT BY PRIOR parent_uc_header_id = unit_config_header_id;
SELECT parent_mc_id, parent_position_key
FROM ahl_uc_header_paths_v
WHERE uc_header_id = p_uc_header_id;
SELECT a.mc_id, b.position_key
FROM AHL_MC_HEADERS_B a, AHL_MC_RELATIONSHIPS b
WHERE a.mc_header_id = b.mc_header_id
AND b.relationship_id = p_rel_id;
SELECT position_ref_code
FROM AHL_MC_PATH_POSITIONS
WHERE encoded_path_position = p_encoded_path;
SELECT rel.position_ref_code
FROM AHL_MC_RELATIONSHIPS rel
WHERE rel.relationship_id = p_relationship_id;
SELECT 'X'
FROM csi_ii_relationships csi_ii
WHERE csi_ii.object_id = p_to_csi_instance_id
START WITH csi_ii.subject_id = p_csi_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND CSI_II.POSITION_REFERENCE IS NOT NULL;
SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
FROM ahl_uc_header_paths_v up
WHERE up.csi_instance_id = p_csi_instance_id;
SELECT csi.object_id
FROM csi_ii_relationships csi
WHERE csi.object_id IN
( SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
START WITH csi.subject_id = p_csi_instance_id
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
CONNECT BY csi.subject_id = PRIOR csi.object_id
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND CSI.POSITION_REFERENCE IS NOT NULL;
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
csi_ii_relationships csi_ii
WHERE csi_ii.subject_id = p_csi_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
AND REL.mc_header_id = HDR.mc_header_id;
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
ahl_unit_config_headers uch, csi_unit_instances_v csi_u
WHERE uch.csi_item_instance_id = p_csi_instance_id
AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND hdr.mc_header_id = uch.master_config_id
AND rel.mc_header_id = hdr.mc_header_id
AND rel.parent_relationship_id IS NULL
AND uch.csi_item_instance_id = csi_u.instance_id;
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel,
ahl_unit_config_headers uch
WHERE uch.csi_item_instance_id = p_csi_instance_id
AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND hdr.mc_header_id = uch.master_config_id
AND rel.mc_header_id = hdr.mc_header_id
AND rel.parent_relationship_id IS NULL
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = uch.csi_item_instance_id AND
CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' AND
TRUNC(nvl(CIR.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate) AND
TRUNC(nvl(CIR.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate));
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel
WHERE hdr.mc_header_id = rel.mc_header_id
AND rel.relationship_id = p_relationship_id
--Jerry rewrite the following condition on 03/03/2005 in order to fix bug 4090856
--after verifying the bug fix on scmtsb2
AND rel.relationship_id IN (SELECT relationship_id
FROM ahl_mc_relationships
WHERE mc_header_id = (SELECT mc_header_id
FROM ahl_mc_relationships
WHERE relationship_id = (SELECT to_number(position_reference)
FROM csi_ii_relationships
WHERE subject_id = p_parent_instance_id
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)))
OR mc_header_id = (SELECT master_config_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = p_parent_instance_id
AND TRUNC(nvl(ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)));
( SELECT r.relationship_id
FROM AHL_MC_RELATIONSHIPS r, AHL_UNIT_CONFIG_HEADERS uch
WHERE uch.csi_item_instance_id = p_parent_instance_id
AND uch.master_config_id = r.mc_header_id
--AND r.parent_relationship_id IS NULL
--Jerry commented out the above condition on 01/14/2005 to fix bug 4090856
AND TRUNC(nvl(uch.active_end_date, sysdate+1)) > TRUNC(sysdate)
UNION ALL
SELECT TO_NUMBER(CSI_II.POSITION_REFERENCE)
FROM csi_ii_relationships csi_ii
WHERE csi_ii.subject_id = p_parent_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
UNION ALL
SELECT subrel.relationship_id
FROM ahl_mc_config_relations crel, ahl_mc_relationships subrel,
csi_ii_relationships csi_ii
WHERE csi_ii.subject_id = p_parent_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND crel.relationship_id = TO_NUMBER(CSI_II.POSITION_REFERENCE)
AND crel.mc_header_id = subrel.mc_header_id
AND subrel.parent_relationship_id IS NULL);
SELECT mcr.relationship_id
FROM ahl_mc_path_position_nodes mpn, ahl_mc_headers_b mch,
ahl_mc_relationships mcr
WHERE mpn.path_position_id = p_path_position_id
AND mpn.sequence = (
SELECT MAX(sequence)
FROM ahl_mc_path_position_nodes
WHERE path_position_id = mpn.path_position_id
)
AND mpn.mc_id = mch.mc_id
AND mch.version_number = NVL(mpn.version_number, mch.version_number)
AND mcr.mc_header_id = mch.mc_header_id
AND mcr.position_key = mpn.position_key;
SELECT item_group_id
FROM ahl_mc_relationships
WHERE relationship_id = p_relationship_id;
SELECT mtl.serial_number_control_code
FROM ahl_item_associations_b aia, mtl_system_items_b mtl
WHERE aia.item_group_id = p_item_group_id
AND aia.inventory_item_id = mtl.inventory_item_id
AND aia.inventory_org_id = mtl.organization_id
AND aia.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER BY aia.item_association_id;