[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_csi_ii_relationships(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_subject_id IN NUMBER
);
SELECT INV_PROJECT.GET_LOCSEGS(LOC.inventory_location_id, LOC.ORGANIZATION_ID) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_project_number(LOC.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_task_number(LOC.segment20)
--FROM mtl_item_locations_kfv
FROM mtl_item_locations LOC
WHERE inventory_location_id = p_inventory_locator_id
AND organization_id = p_inventory_org_id;
SELECT address
FROM ahl_owner_locations_v
WHERE owner_site_id = p_location_id
AND party_type = p_party_type;
SELECT hzloc.address1 ||
decode(hzloc.address2,null,null,';'||hzloc.address2) ||
SELECT decode(address_line1,null,null,address_line1) ||
decode(address_line2,null,null,';'||address_line2) ||
SELECT decode(address_line_1,null,null,address_line_1) ||
decode(address_line_2,null,null,';'||address_line_2) ||
SELECT hzloc.address1 ||
decode(hzloc.address2,null,null,';'||hzloc.address2) ||
SELECT f.meaning || ';' || hou.name
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_organization_id;
SELECT owner_site_number
FROM ahl_owner_locations_v
WHERE owner_site_id = p_location_id
AND party_type = p_party_type;
SELECT party_site_number
FROM hz_party_sites
WHERE party_site_id = p_location_id
AND status <> 'I';
SELECT vendor_site_code
FROM po_vendor_sites_all
WHERE vendor_site_id = p_location_id;
SELECT ctxn.transaction_type_id
FROM csi_txn_types ctxn, fnd_application app
WHERE ctxn.source_application_id = app.application_id
AND app.APPLICATION_SHORT_NAME = 'AHL'
AND ctxn.source_transaction_type = p_txn_code;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = p_status_name;
SELECT name
FROM csi_instance_statuses
WHERE instance_status_id = p_status_id;
SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = 'GLOBAL'
AND attribute_code = p_attribute_code;
SELECT iea.attribute_value, iea.attribute_value_id, iea.object_version_number
FROM csi_i_extended_attribs attb, csi_iea_values iea
WHERE attb.attribute_id = iea.attribute_id
AND attb.attribute_code = p_attribute_code
AND iea.instance_id = p_csi_instance_id
AND trunc(sysdate) >= trunc(nvl(iea.active_start_date, sysdate))
AND trunc(sysdate) < trunc(nvl(iea.active_end_date, sysdate+1));
SELECT instance_status_id, location_type_code
FROM csi_item_instances csi
WHERE csi.instance_id = p_csi_item_instance_id;
SELECT name
FROM csi_instance_statuses
WHERE instance_status_id = p_instance_status_id;
SELECT meaning
FROM csi_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND TRUNC(SYSDATE) >= TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(end_date_active, SYSDATE+1));
SELECT position_ref_code
FROM AHL_MC_RELATIONSHIPS
WHERE relationship_id = p_relationship_id
AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
SELECT iasso.quantity Itm_qty,
iasso.uom_code Itm_uom_code,
reln.quantity Posn_Qty,
reln.uom_code Posn_uom_code,
iasso.revision Itm_revision,
iasso.item_association_id
FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
WHERE reln.item_group_id = iasso.item_group_id
AND reln.relationship_id = p_mc_relationship_id
AND iasso.inventory_item_id = p_Inventory_id
AND iasso.inventory_org_id = p_Organization_id
AND (iasso.revision IS NULL OR iasso.revision = p_revision)
AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
--Added by Jerry on 04/26/2005
AND trunc(sysdate) >= trunc(nvl(reln.active_start_date,sysdate))
AND trunc(sysdate) < trunc(nvl(reln.active_end_date, sysdate+1));
SELECT iasso.quantity Itm_qty,
iasso.uom_code Itm_uom_code,
reln.quantity Posn_Qty,
reln.uom_code Posn_uom_code,
iasso.revision Itm_revision,
iasso.item_association_id
FROM ahl_mc_relationships reln, ahl_item_associations_b iasso
WHERE reln.item_group_id = iasso.item_group_id
AND reln.relationship_id = p_mc_relationship_id
AND iasso.inventory_item_id = p_Inventory_id
AND (iasso.revision IS NULL OR iasso.revision = p_revision)
AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
--Added by Jerry on 04/26/2005
AND trunc(sysdate) >= trunc(nvl(reln.active_start_date,sysdate))
AND trunc(sysdate) < trunc(nvl(reln.active_end_date, sysdate+1))
order by iasso.inventory_org_id;
SELECT fnd.lookup_code, fnd.meaning position_ref_meaning
FROM ahl_mc_relationships mcr, fnd_lookup_values_vl fnd
WHERE mcr.relationship_id = p_mc_relationship_id
AND mcr.position_ref_code = fnd.lookup_code
AND fnd.lookup_type = 'AHL_POSITION_REFERENCE';
SELECT subject_id
FROM csi_ii_relationships
WHERE position_reference = to_char(p_mc_relationship_id)
START WITH object_id = p_csi_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
SELECT object_id, subject_id, position_reference, level, relationship_id csi_ii_relationship_id,
object_version_number csi_ii_object_version_number
FROM csi_ii_relationships
START WITH object_id = p_csi_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
ORDER BY level;
SELECT relationship_id, position_ref_code, level
FROM ahl_mc_relationships
START WITH parent_relationship_id = p_mc_relationship_id
AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1))
CONNECT BY PRIOR relationship_id = parent_relationship_id
AND TRUNC(SYSDATE) >= TRUNC(NVL(active_start_date, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1))
ORDER BY level;
SELECT inventory_item_id, last_vld_organization_id, quantity, unit_of_measure,
inventory_revision, instance_number
FROM csi_item_instances csi
WHERE instance_id = p_csi_item_instance_id;
SELECT posn.position_ref_code, f.meaning
FROM ahl_relationships_vl posn, fnd_lookups f
WHERE posn.relationship_id = p_mc_relationship_id
AND posn.position_ref_code = f.lookup_code;
FND_MESSAGE.Set_Name('AHL','AHL_UC_CHILD_DELETED');
l_mc_posn_tbl.DELETE(j);
l_part_tbl.DELETE(i);
SELECT item_group_id
FROM ahl_item_associations_b
WHERE item_group_id = c_item_group_id
AND inventory_item_id = c_inventory_item_id
AND inventory_org_id = c_inventory_org_id;
SELECT relationship_id
FROM ahl_mc_relationships rel, ahl_mc_headers_b hdr
WHERE trunc(nvl(rel.active_end_date,sysdate+1)) > trunc(sysdate)
AND trunc(nvl(rel.active_start_date,sysdate-1)) < trunc(sysdate)
AND hdr.mc_header_id = rel.mc_header_id
AND rel.item_group_id = c_item_group_id
AND hdr.config_status_code not in ('EXPIRED','CLOSED');
SELECT instance_id csi_item_instance_id, csi.object_version_number
FROM csi_ii_relationships reln, csi_item_instances csi
WHERE reln.subject_id = csi.instance_id
AND TRUNC(SYSDATE) < TRUNC(NVL(reln.active_end_date, SYSDATE+1))
AND trunc(nvl(reln.active_start_date,sysdate-1)) < trunc(sysdate)
AND reln.relationship_type_code = 'COMPONENT-OF'
AND reln.position_reference = c_position_reference
AND csi.inventory_item_id = c_inventory_item_id
AND csi.inv_master_organization_id = c_inventory_org_id;
SELECT uc.csi_item_instance_id, uc.unit_config_header_id, uc.parent_uc_header_id, uc.unit_config_status_code
FROM ahl_unit_config_headers uc, csi_item_instances csi, ahl_mc_relationships mc
WHERE uc.csi_item_instance_id = csi.instance_id
AND uc.master_config_id = mc.mc_header_id
AND mc.relationship_id = c_relationship_id
AND TRUNC(SYSDATE) < TRUNC(NVL(uc.active_end_date, SYSDATE+1))
AND trunc(nvl(uc.active_start_date,sysdate-1)) < trunc(sysdate)
AND csi.inventory_item_id = c_inventory_item_id
AND csi.inv_master_organization_id = c_inventory_org_id;
SELECT uc.unit_config_header_id, uc.unit_config_status_code
FROM ahl_unit_config_headers uc
WHERE csi_item_instance_id in
( SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_csi_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR object_id = subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
AND uc.parent_uc_header_id IS NULL
AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(uc.active_end_date, sysdate+1));
update ahl_unit_config_headers
set active_end_date = sysdate,
object_version_number=object_version_number+1
where unit_config_header_id = l_chk_top_node_csr.unit_config_header_id;
update_csi_ii_relationships(x_return_status =>x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subject_id=>l_chk_top_node_csr.csi_item_instance_id);
update_csi_ii_relationships(x_return_status =>x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subject_id=>item_instance_rec.csi_item_instance_id);
update ahl_unit_config_headers
set unit_config_status_code = 'APPROVAL_REJECTED',
object_version_number=object_version_number+1
where unit_config_header_id = l_unit_config_header_id;
update ahl_unit_config_headers
set unit_config_status_code = 'INCOMPLETE',
object_version_number=object_version_number+1
where unit_config_header_id = l_unit_config_header_id;
PROCEDURE update_csi_ii_relationships(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_subject_id IN NUMBER
)
IS
--
CURSOR get_csi_record_csr(c_subject_id NUMBER) IS
SELECT relationship_id, object_id, object_version_number
FROM csi_ii_relationships
WHERE subject_id = c_subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
AND trunc(sysdate) > trunc(nvl(active_start_date, sysdate-1));
AHL_Util_UC_Pkg.GetCSI_Transaction_ID('UC_UPDATE',l_transaction_type_id,
l_return_val);
CSI_II_Relationships_PUB.Update_Relationship(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_relationship_tbl => l_csi_relationship_tbl,
p_txn_rec => l_csi_transaction_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
END update_csi_ii_relationships;
SELECT UC.ROOT_UC_HEADER_ID,
UC.UC_STATUS_CODE,
UC.UC_STATUS,
UC.ACTIVE_UC_STATUS,
NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(UC.INSTANCE_END_DATE, SYSDATE + 1),
ROOT_UC.UNIT_CONFIG_STATUS_CODE,
FL.MEANING,
FLA.MEANING,
NVL(ROOT_UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(CSI.ACTIVE_END_DATE, SYSDATE + 1)
FROM AHL_UNIT_CONFIG_HEADERS_V UC, AHL_UNIT_CONFIG_HEADERS ROOT_UC,
FND_LOOKUP_VALUES_VL FL, FND_LOOKUP_VALUES_VL FLA,
CSI_ITEM_INSTANCES CSI
WHERE UC.UC_HEADER_ID = p_uc_header_id AND
ROOT_UC.UNIT_CONFIG_HEADER_ID = UC.ROOT_UC_HEADER_ID AND
ROOT_UC.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND
FL.lookup_type = 'AHL_CONFIG_STATUS' AND
ROOT_UC.unit_config_status_code = FL.lookup_code AND
FLA.lookup_type (+) = 'AHL_CONFIG_STATUS' AND
ROOT_UC.active_uc_status_code = FLA.lookup_code (+);
SELECT MEANING CsifutLookupMeaning
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_CONFIG_STATUS' AND
LOOKUP_CODE = G_STATUS_EXPIRED;
SELECT unit_config_header_id
INTO l_root_uc_header_id
FROM ahl_unit_config_headers
WHERE parent_uc_header_id IS NULL
START WITH unit_config_header_id = p_uc_header_id
-- Commented out by jaramana on August 23, 2006 to show the status of Expired units correctly
-- AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id;
SELECT UC.UNIT_CONFIG_STATUS_CODE,
UCSC.MEANING,
UASC.MEANING,
NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(CSI.active_end_date,SYSDATE + 1)
INTO l_uc_status_code,
l_uc_status,
l_uc_active_status,
l_uc_end_date,
l_uc_inst_end_date
FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
CSI_ITEM_INSTANCES CSI, FND_LOOKUP_VALUES UASC
WHERE UC.UNIT_CONFIG_HEADER_ID = p_uc_header_id
AND UC.csi_item_instance_id = CSI.instance_id
AND UC.unit_config_status_code = UCSC.lookup_code
AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
AND UCSC.language = USERENV('LANG')
AND UC.active_uc_status_code = UASC.lookup_code (+)
AND 'AHL_CONFIG_STATUS' = UASC.lookup_type (+)
AND UASC.language (+) = USERENV('LANG');
SELECT UC.UNIT_CONFIG_STATUS_CODE,
UCSC.MEANING,
UASC.MEANING,
NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(CSI.active_end_date,SYSDATE + 1)
INTO l_root_status_code,
l_root_status,
l_root_active_status,
l_root_end_date,
l_root_inst_end_date
FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
CSI_ITEM_INSTANCES CSI, FND_LOOKUP_VALUES UASC
WHERE UC.UNIT_CONFIG_HEADER_ID = l_root_uc_header_id
AND UC.csi_item_instance_id = CSI.instance_id
AND UC.unit_config_status_code = UCSC.lookup_code
AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
AND UCSC.language = USERENV('LANG')
AND UC.active_uc_status_code = UASC.lookup_code (+)
AND 'AHL_CONFIG_STATUS' = UASC.lookup_type (+)
AND UASC.language (+) = USERENV('LANG');
SELECT UC.ROOT_UC_HEADER_ID,
UC.UC_STATUS_CODE,
UC.UC_STATUS,
NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(UC.INSTANCE_END_DATE, SYSDATE + 1),
ROOT_UC.UNIT_CONFIG_STATUS_CODE,
FL.meaning,
NVL(ROOT_UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(CSI.ACTIVE_END_DATE, SYSDATE + 1)
FROM AHL_UNIT_CONFIG_HEADERS_V UC, AHL_UNIT_CONFIG_HEADERS ROOT_UC,
CSI_ITEM_INSTANCES CSI, FND_LOOKUP_VALUES_VL FL
WHERE UC.UC_HEADER_ID = p_uc_header_id AND
ROOT_UC.UNIT_CONFIG_HEADER_ID = UC.ROOT_UC_HEADER_ID AND
ROOT_UC.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND
ROOT_UC.UNIT_CONFIG_STATUS_CODE = FL.LOOKUP_CODE AND
FL.LOOKUP_TYPE = 'AHL_CONFIG_STATUS';
SELECT unit_config_header_id
INTO l_root_uc_header_id
FROM ahl_unit_config_headers
WHERE parent_uc_header_id IS NULL
START WITH unit_config_header_id = p_uc_header_id
-- Commented out by jaramana on August 23, 2006 to show the status of Expired units correctly
-- AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id;
SELECT UC.UNIT_CONFIG_STATUS_CODE,
UCSC.MEANING,
NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(CSI.active_end_date,SYSDATE + 1)
INTO l_uc_status_code,
l_uc_status,
l_uc_end_date,
l_uc_inst_end_date
FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
CSI_ITEM_INSTANCES CSI
WHERE UC.UNIT_CONFIG_HEADER_ID = p_uc_header_id
AND UC.csi_item_instance_id = CSI.instance_id
AND UC.unit_config_status_code = UCSC.lookup_code
AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
AND UCSC.language = USERENV('LANG');
SELECT UC.UNIT_CONFIG_STATUS_CODE,
UCSC.MEANING,
NVL(UC.ACTIVE_END_DATE, SYSDATE + 1),
NVL(CSI.active_end_date,SYSDATE + 1)
INTO l_root_status_code,
l_root_status,
l_root_end_date,
l_root_inst_end_date
FROM AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES UCSC,
CSI_ITEM_INSTANCES CSI
WHERE UC.UNIT_CONFIG_HEADER_ID = l_root_uc_header_id
AND UC.csi_item_instance_id = CSI.instance_id
AND UC.unit_config_status_code = UCSC.lookup_code
AND 'AHL_CONFIG_STATUS' = UCSC.lookup_type
AND UCSC.language = USERENV('LANG');
SELECT *
FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id;
SELECT nvl(max(version_no), 0) INTO l_version_no
FROM ahl_uc_headers_h
WHERE unit_config_header_id = p_uc_header_id;
INSERT INTO ahl_uc_headers_h(
unit_config_header_id,
version_no,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
name,
master_config_id,
csi_item_instance_id,
unit_config_status_code,
active_start_date,
active_end_date,
active_uc_status_code,
parent_uc_header_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
VALUES(
l_uc_header_rec.unit_config_header_id,
l_version_no+1,
l_uc_header_rec.object_version_number,
l_uc_header_rec.creation_date,
l_uc_header_rec.created_by,
l_uc_header_rec.last_update_date,
l_uc_header_rec.last_updated_by,
l_uc_header_rec.last_update_login,
l_uc_header_rec.name,
l_uc_header_rec.master_config_id,
l_uc_header_rec.csi_item_instance_id,
l_uc_header_rec.unit_config_status_code,
l_uc_header_rec.active_start_date,
l_uc_header_rec.active_end_date,
l_uc_header_rec.active_uc_status_code,
l_uc_header_rec.parent_uc_header_id,
l_uc_header_rec.attribute_category,
l_uc_header_rec.attribute1,
l_uc_header_rec.attribute2,
l_uc_header_rec.attribute3,
l_uc_header_rec.attribute4,
l_uc_header_rec.attribute5,
l_uc_header_rec.attribute6,
l_uc_header_rec.attribute7,
l_uc_header_rec.attribute8,
l_uc_header_rec.attribute9,
l_uc_header_rec.attribute10,
l_uc_header_rec.attribute11,
l_uc_header_rec.attribute12,
l_uc_header_rec.attribute13,
l_uc_header_rec.attribute14,
l_uc_header_rec.attribute15);
SELECT unit_config_header_id,
csi_item_instance_id,
unit_config_status_code,
active_uc_status_code,
object_version_number
FROM ahl_unit_config_headers
WHERE parent_uc_header_id IS NULL
START WITH unit_config_header_id = c_uc_header_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT subject_id
FROM csi_ii_relationships
WHERE position_reference IS NULL
START WITH subject_id = p_instance_id
AND subject_id <> p_top_instance_id
--And this one more condition just in order to avoid p_instance_id = p_top_instance_id
--and it is a subunit or installed unit
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)
CONNECT BY subject_id = PRIOR object_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)
AND subject_id <> p_top_instance_id;
SELECT A.quantity item_quantity,
A.uom_code item_uom_code,
A.revision item_revision,
R.quantity position_quantity,
R.uom_code position_uom_code
FROM ahl_mc_relationships R,
ahl_item_associations_b A
WHERE R.item_group_id = A.item_group_id
AND R.relationship_id = c_mc_relationship_id
AND A.inventory_item_id = c_inventory_item_id
AND A.inventory_org_id = c_organization_id
AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
--Added by Jerry on 04/26/2005
AND trunc(nvl(R.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(R.active_end_date, sysdate+1)) > trunc(sysdate);
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = p_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)
CONNECT BY subject_id = PRIOR object_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);
SELECT unit_config_header_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT rel.relationship_id
FROM AHL_MC_RELATIONSHIPS rel,
AHL_UNIT_CONFIG_HEADERS uch
WHERE rel.mc_header_id = uch.master_config_id
AND rel.parent_relationship_id IS NULL
AND uch.csi_item_instance_id = csi_id;
SELECT TO_NUMBER(position_reference)
FROM csi_ii_relationships
WHERE SUBJECT_ID = csi_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = p_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)
CONNECT BY subject_id = PRIOR object_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);
SELECT name
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = p_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)
CONNECT BY subject_id = PRIOR object_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);
SELECT unit_config_header_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = p_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)
CONNECT BY subject_id = PRIOR object_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);
SELECT name
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = p_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)
CONNECT BY subject_id = PRIOR object_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);
SELECT unit_config_header_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT item_group_id
FROM AHL_ITEM_ASSOCIATIONS_B
WHERE item_group_id = c_item_group_id AND
inventory_item_id = c_inventory_item_id AND
inventory_org_id = c_inventory_org_id;
SELECT relationship_id
FROM AHL_MC_RELATIONSHIPS REL, AHL_MC_HEADERS_B HDR
WHERE HDR.mc_header_id = REL.mc_header_id AND
REL.item_group_id = c_item_group_id AND
HDR.config_status_code NOT IN ('EXPIRED','CLOSED') AND
TRUNC(NVL(REL.active_end_date,SYSDATE+1)) > TRUNC(SYSDATE) AND
TRUNC(NVL(REL.active_start_date,SYSDATE)) <= TRUNC(SYSDATE);
SELECT CSI.instance_id
FROM CSI_II_RELATIONSHIPS RELN, CSI_ITEM_INSTANCES CSI
WHERE RELN.subject_id = CSI.instance_id AND
RELN.relationship_type_code = 'COMPONENT-OF' AND
RELN.position_reference = c_position_reference AND
CSI.inventory_item_id = c_inventory_item_id AND
CSI.inv_master_organization_id = c_inventory_org_id AND
TRUNC(NVL(RELN.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE) AND
TRUNC(NVL(RELN.active_start_date,SYSDATE)) <= TRUNC(SYSDATE);
SELECT UC.name,
UC.unit_config_header_id
FROM AHL_UNIT_CONFIG_HEADERS UC, CSI_ITEM_INSTANCES CSI,
AHL_MC_RELATIONSHIPS MC
WHERE UC.csi_item_instance_id = CSI.instance_id AND
UC.master_config_id = MC.mc_header_id AND
UC.parent_uc_header_id IS NULL AND
MC.parent_relationship_id IS NULL AND
MC.relationship_id = c_relationship_id AND
CSI.inventory_item_id = c_inventory_item_id AND
CSI.inv_master_organization_id = c_inventory_org_id AND
TRUNC(NVL(UC.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE) AND
TRUNC(NVL(UC.active_start_date,SYSDATE)) <= TRUNC(SYSDATE) AND
TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT UC.name,
UC.unit_config_header_id
FROM AHL_UNIT_CONFIG_HEADERS UC, CSI_ITEM_INSTANCES CSI
WHERE UC.csi_item_instance_id IN
(SELECT object_id
FROM CSI_II_RELATIONSHIPS
START WITH
subject_id = p_csi_item_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)
CONNECT BY
PRIOR object_id = subject_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)
) AND
UC.parent_uc_header_id IS NULL AND
UC.csi_item_instance_id = CSI.instance_id AND
TRUNC(NVL(UC.active_start_date,SYSDATE)) <= TRUNC(SYSDATE) AND
TRUNC(NVL(UC.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE) AND
TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT unit_config_header_id, master_config_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = p_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT reln.relationship_id, reln.item_group_id
FROM ahl_mc_relationships reln
WHERE reln.mc_header_id = c_mc_header_id
AND nvl(reln.active_start_date, sysdate - 1) <= sysdate
AND nvl(reln.active_end_date, sysdate + 1) > sysdate
AND reln.parent_relationship_id is null;
SELECT position_reference
FROM csi_ii_relationships
WHERE subject_id = p_instance_id
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND NVL(ACTIVE_START_DATE, sysdate - 1) <= sysdate
AND NVL(ACTIVE_END_DATE, sysdate + 1) > sysdate;
SELECT reln.item_group_id
FROM ahl_mc_relationships reln
WHERE relationship_id = TO_NUMBER(c_pos_ref);
SELECT 1 from ahl_item_associations_b
WHERE item_group_id = c_item_group_id
AND inventory_item_id = p_inventory_item_id
AND ((revision IS NULL) OR (revision = p_item_revision))
AND interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE');