[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT active_end_date
FROM csi_item_instances
WHERE instance_id = c_instance_id;
SELECT 'X'
FROM mtl_serial_numbers
WHERE inventory_item_id = c_inventory_id
AND serial_number = c_serial_number;
SELECT 'X'
FROM mtl_lot_numbers
WHERE inventory_item_id = c_inventory_id
AND organization_id = c_organization_id
AND lot_number = c_lot_number
AND nvl(disable_flag,2) = 2;
SELECT 'X'
FROM mtl_item_revisions
WHERE inventory_item_id = c_inventory_id
AND organization_id = c_organization_id
AND revision = c_revision;
SELECT serial_number_control_code,
lot_control_code,
concatenated_segments,
revision_qty_control_code,
comms_nl_trackable_flag
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inventory_id
AND organization_id = c_organization_id;
SELECT o.operating_unit
FROM org_organization_definitions o,
mtl_system_items_kfv m,
csi_item_instances c
WHERE c.instance_id = p_instance_id
AND c.inventory_item_id = m.inventory_item_id
AND c.inv_master_organization_id = m.organization_id
AND m.organization_id = o.organization_id;
SELECT i.operating_unit
FROM inv_organization_info_v i, mtl_system_items_kfv m,
csi_item_instances c
WHERE c.instance_id = p_instance_id AND
c.inventory_item_id = m.inventory_item_id AND
c.inv_master_organization_id = m.organization_id AND
m.organization_id = i.organization_id;
SELECT unit_config_header_id,
object_version_number,
unit_config_status_code,
active_uc_status_code,
csi_item_instance_id
FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT relationship_id,
object_version_number,
object_id,
subject_id
FROM csi_ii_relationships
START WITH object_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
CSI_II_RELATIONSHIPS_PUB.update_relationship(
p_api_version => 1.0,
p_relationship_tbl => l_csi_relationship_tbl,
p_txn_rec => l_csi_transaction_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'DRAFT',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
SELECT unit_config_header_id,
object_version_number,
unit_config_status_code,
active_uc_status_code,
csi_item_instance_id
FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT relationship_id,
object_version_number,
object_id,
subject_id,
to_number(position_reference) position_reference
FROM csi_ii_relationships
START WITH object_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT object_version_number
FROM csi_item_instances
WHERE instance_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT subject_id
FROM csi_ii_relationships
WHERE object_id = c_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);
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 position_necessity_code
FROM ahl_mc_relationships
WHERE relationship_id = c_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT subject_id
FROM csi_ii_relationships
WHERE subject_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id = c_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 object_id = PRIOR 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)
MINUS
SELECT subject_id
FROM csi_ii_relationships
WHERE subject_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id IN (SELECT subject_id
FROM csi_ii_relationships
WHERE subject_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date,SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id = c_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 object_id = PRIOR 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 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 object_id = PRIOR 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);
SELECT i.subject_id
FROM csi_ii_relationships i
WHERE EXISTS (SELECT 'x'
FROM ahl_unit_config_headers u
WHERE u.csi_item_instance_id = i.subject_id
AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
AND NOT EXISTS (SELECT ci.object_id
FROM csi_ii_relationships ci
WHERE (EXISTS (SELECT 'x'
FROM ahl_unit_config_headers ui
WHERE ui.csi_item_instance_id = ci.object_id)
AND ci.object_id <> c_instance_id)
START WITH ci.subject_id = i.subject_id
AND ci.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ci.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY ci.subject_id = prior ci.object_id
AND ci.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ci.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND ci.subject_id <> c_instance_id)
START WITH i.object_id = c_instance_id
AND i.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY i.object_id = PRIOR i.subject_id
AND i.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i.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);
AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');
UPDATE ahl_unit_config_headers
SET parent_uc_header_id = NULL,
unit_config_status_code = l_uc_status_code,
active_uc_status_code = l_active_uc_status_code,
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
WHERE unit_config_header_id = l_sub_uc_header_id;
UPDATE ahl_unit_config_headers
SET parent_uc_header_id = NULL,
unit_config_status_code = l_uc_status_code,
active_uc_status_code = l_active_uc_status_code,
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
WHERE unit_config_header_id = l_sub_uc_header_id;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'DRAFT',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
PROCEDURE update_instance_attr(
p_api_version IN NUMBER := 1.0,
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_uc_header_id IN NUMBER,
p_uc_instance_rec IN uc_instance_rec_type,
p_prod_user_flag IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_instance_attr';
SELECT unit_config_header_id,
object_version_number,
unit_config_status_code,
active_uc_status_code,
csi_item_instance_id
FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT relationship_id,
object_version_number,
object_id,
subject_id,
position_reference
FROM csi_ii_relationships
START WITH object_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT instance_id,
instance_number,
inventory_item_id,
last_vld_organization_id,
serial_number,
lot_number,
quantity,
unit_of_measure,
install_date,
inventory_revision,
object_version_number
FROM csi_item_instances
WHERE instance_id = c_instance_id;
SAVEPOINT update_instance_attr;
FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_val);
CSI_ITEM_INSTANCE_PUB.update_item_instance(
p_api_version => 1.0,
p_instance_rec => l_csi_instance_rec,
p_txn_rec => l_csi_transaction_rec,
p_ext_attrib_values_tbl => l_csi_ext_attrib_values_tbl,
p_party_tbl => l_csi_party_tbl,
p_account_tbl => l_csi_account_tbl,
p_pricing_attrib_tbl => l_csi_pricing_attrib_tbl,
p_org_assignments_tbl => l_csi_org_assignments_tbl,
p_asset_assignment_tbl => l_csi_asset_assignment_tbl,
x_instance_id_lst => l_csi_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'DRAFT',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
ROLLBACK to update_instance_attr;
ROLLBACK to update_instance_attr;
ROLLBACK to update_instance_attr;
SELECT A.unit_config_header_id,
A.object_version_number,
A.unit_config_status_code,
A.active_uc_status_code,
A.csi_item_instance_id,
B.relationship_id
FROM ahl_unit_config_headers A,
ahl_mc_relationships B
WHERE A.unit_config_header_id = p_uc_header_id
AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND A.master_config_id = B.mc_header_id
AND B.parent_relationship_id IS NULL;
SELECT relationship_id,
object_version_number,
object_id,
subject_id,
to_number(position_reference) position_id
FROM csi_ii_relationships
START WITH object_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT 'X'
FROM ahl_mc_relationships
WHERE relationship_id = c_child_relationship_id
AND (parent_relationship_id = c_parent_relationship_id OR
mc_header_id IN (SELECT mc_header_id
FROM ahl_mc_config_relations
WHERE relationship_id = c_parent_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)));
SELECT subject_id
FROM csi_ii_relationships
WHERE object_id = c_parent_instance_id
AND position_reference = to_char(c_relationship_id)
AND subject_id IS NOT NULL
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 location_id,
location_type_code,
party_id,
party_source_table,
instance_party_id,
csi.wip_job_id
FROM csi_item_instances csi, csi_i_parties p
WHERE csi.instance_id = p.instance_id
AND p.relationship_type_code = 'OWNER'
AND csi.instance_id = c_csi_instance_id
AND trunc(SYSDATE) < trunc(nvl(csi.active_end_date, SYSDATE+1));
SELECT party_account_id
FROM csi_ip_accounts
WHERE relationship_type_code = 'OWNER'
AND instance_party_id = c_instance_party_id
AND trunc(SYSDATE) >= trunc(nvl(active_start_date, SYSDATE))
AND trunc(SYSDATE) < trunc(nvl(active_end_date, SYSDATE+1));
SELECT 'X'
FROM ahl_unit_config_headers
WHERE name = c_uc_name
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT H.mc_header_id,
R.relationship_id
FROM ahl_mc_headers_b H,
ahl_mc_relationships R
WHERE H.mc_header_id = R.mc_header_id
AND R.parent_relationship_id IS NULL
AND trunc(nvl(R.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND H.name = c_mc_name
AND H.revision = c_mc_revision
AND H.mc_header_id IN (SELECT mc_header_id
FROM ahl_mc_config_relations
WHERE relationship_id = c_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));
SELECT i.interchange_type_code
FROM csi_item_instances c,
ahl_item_associations_b i,
ahl_mc_relationships m
WHERE m.relationship_id = c_relationship_id
AND c.instance_id = c_instance_id
AND m.item_group_id = i.item_group_id
AND c.inventory_item_id = i.inventory_item_id
AND c.inv_master_organization_id = i.inventory_org_id
AND (c.inventory_revision IS NULL OR
i.revision is NULL OR
(c.inventory_revision IS NOT NULL AND
i.revision IS NOT NULL AND
c.inventory_revision = i.revision));
SELECT f.meaning INTO l_position_ref_meaning
FROM ahl_mc_relationships a,
fnd_lookups f
WHERE a.relationship_id = p_x_uc_instance_rec.relationship_id
AND f.lookup_code (+) = A.position_ref_code
AND f.lookup_type (+) = 'AHL_POSITION_REFERENCE' ;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'DRAFT',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
SELECT A.unit_config_header_id,
A.object_version_number,
A.unit_config_status_code,
A.active_uc_status_code,
A.csi_item_instance_id,
B.relationship_id
FROM ahl_unit_config_headers A,
ahl_mc_relationships B
WHERE A.unit_config_header_id = p_uc_header_id
AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND A.master_config_id = B.mc_header_id
AND B.parent_relationship_id IS NULL;
SELECT relationship_id,
object_version_number,
object_id,
subject_id,
to_number(position_reference) position_id
FROM csi_ii_relationships
START WITH object_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT 1
FROM ahl_mc_relationships
WHERE relationship_id = c_child_relationship_id
AND (parent_relationship_id = c_parent_relationship_id OR
mc_header_id IN (SELECT mc_header_id
FROM ahl_mc_config_relations
WHERE relationship_id = c_parent_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)));
SELECT subject_id
FROM csi_ii_relationships
WHERE object_id = c_parent_instance_id
AND position_reference = to_char(c_relationship_id)
AND subject_id IS NOT NULL
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 subject_id
FROM csi_ii_relationships
WHERE object_id = c_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);
SELECT unit_config_header_id, master_config_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 subject_id
FROM csi_ii_relationships
WHERE subject_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id = c_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 object_id = PRIOR 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)
MINUS
SELECT subject_id
FROM csi_ii_relationships
WHERE subject_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id IN (SELECT subject_id
FROM csi_ii_relationships
WHERE subject_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id = c_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 object_id = PRIOR 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 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 object_id = PRIOR 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);
SELECT i.subject_id
FROM csi_ii_relationships i
WHERE EXISTS (SELECT 'x'
FROM ahl_unit_config_headers u
WHERE u.csi_item_instance_id = i.subject_id
AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
AND NOT EXISTS (SELECT ci.object_id
FROM csi_ii_relationships ci
WHERE (EXISTS (SELECT 'x'
FROM ahl_unit_config_headers ui
WHERE ui.csi_item_instance_id = ci.object_id)
AND ci.object_id <> c_instance_id)
START WITH ci.subject_id = i.subject_id
AND ci.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ci.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY ci.subject_id = prior ci.object_id
AND ci.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ci.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND ci.subject_id <> c_instance_id)
START WITH i.object_id = c_instance_id
AND i.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY i.object_id = PRIOR i.subject_id
AND i.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT C.inventory_item_id,
C.inv_master_organization_id inventory_org_id,
C.quantity,
C.unit_of_measure uom_code,
C.inventory_revision revision,
C.install_date,
C.instance_usage_code,
C.location_type_code,
C.object_version_number,
U.unit_config_header_id uc_header_id
FROM csi_item_instances C,
ahl_unit_config_headers U
WHERE C.instance_id = c_instance_id
AND C.instance_id = U.csi_item_instance_id (+)
--AND U.parent_uc_header_id (+) IS NULL
--Comment out in order to include the extra sibling subunits whose parent_uc_header_id
--is not null
AND trunc(SYSDATE) < trunc(nvl(C.active_end_date,SYSDATE+1))
AND trunc(SYSDATE) < trunc(nvl(U.active_end_date (+),SYSDATE+1));
SELECT mc_header_id
FROM ahl_mc_config_relations
WHERE relationship_id = c_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT relationship_id, object_version_number
FROM csi_ii_relationships
WHERE object_id = c_object_id
AND subject_id = c_subject_id
AND position_reference IS NULL
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 relationship_id, object_version_number
FROM csi_ii_relationships
WHERE object_id IN (
SELECT ii.object_id
FROM csi_ii_relationships ii
START WITH ii.subject_id = p_parent_instance_id
AND ii.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ii.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(ii.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY ii.subject_id = PRIOR ii.object_id
AND ii.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ii.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(ii.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
AND subject_id = p_instance_id
AND position_reference IS NULL
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 serial_number, mfg_serial_number_flag
FROM csi_item_instances
WHERE instance_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT 'X'
FROM csi_ii_relationships
WHERE subject_id = c_instance_id
AND position_reference IS NOT NULL
--for extra node, it is still available for its sibling nodes even
--if it is installed and not removed
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 i.interchange_type_code
FROM csi_item_instances c,
ahl_item_associations_b i,
ahl_mc_relationships m
WHERE m.relationship_id = c_relationship_id
AND c.instance_id = c_instance_id
AND m.item_group_id = i.item_group_id
AND c.inventory_item_id = i.inventory_item_id
AND c.inv_master_organization_id = i.inventory_org_id
AND (c.inventory_revision IS NULL OR
i.revision is NULL OR
(c.inventory_revision IS NOT NULL AND
i.revision IS NOT NULL AND
c.inventory_revision = i.revision));
AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_val);
CSI_ITEM_INSTANCE_PUB.update_item_instance(
p_api_version => 1.0,
p_instance_rec => l_csi_instance_rec,
p_txn_rec => l_csi_transaction_rec,
p_ext_attrib_values_tbl => l_csi_ext_attrib_values_tbl,
p_party_tbl => l_csi_party_tbl,
p_account_tbl => l_csi_account_tbl,
p_pricing_attrib_tbl => l_csi_pricing_attrib_tbl,
p_org_assignments_tbl => l_csi_org_assignments_tbl,
p_asset_assignment_tbl => l_csi_asset_assignment_tbl,
x_instance_id_lst => l_csi_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
CSI_II_RELATIONSHIPS_PUB.update_relationship(
p_api_version => 1.0,
p_relationship_tbl => l_csi_relationship_tbl,
p_txn_rec => l_csi_transaction_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE ahl_unit_config_headers
--SET parent_uc_header_id = p_uc_header_id
--The parameter p_uc_header_id is not necessarily the parent uc_header_id of the newly
--installed instance.
SET parent_uc_header_id = l_parent_uc_header_id,
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
WHERE csi_item_instance_id = p_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
UPDATE ahl_unit_config_headers
SET parent_uc_header_id = l_parent_uc_header_id,
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
WHERE csi_item_instance_id = l_get_1st_level_subunit.subject_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT f.meaning INTO l_position_ref_meaning
FROM ahl_mc_relationships a,
fnd_lookups f
WHERE a.relationship_id = p_relationship_id
AND f.lookup_code (+) = A.position_ref_code
AND f.lookup_type (+) = 'AHL_POSITION_REFERENCE' ;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET active_uc_status_code = 'UNAPPROVED',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'DRAFT',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
UPDATE ahl_unit_config_headers
SET unit_config_status_code = 'INCOMPLETE',
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
WHERE unit_config_header_id = l_root_uc_header_id
AND object_version_number = l_root_uc_ovn;
SELECT 'X'
FROM csi_ii_relationships
WHERE subject_id = c_subject_id
AND position_reference = to_char(c_relationship_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
VST.visit_id,
VST.project_id,
VST.inv_locator_id,
AWO.wip_entity_id
FROM
AHL_VISITS_B VST,
AHL_WORKORDERS AWO
WHERE
VST.status_code NOT IN ('DELETED', 'CANCELLED')
AND AWO.visit_id = VST.visit_id
AND AWO.workorder_id = c_workorder_id;
SELECT C.instance_id,
C.instance_number,
C.inventory_item_id,
C.inv_master_organization_id,
C.quantity,
C.inventory_revision,
C.unit_of_measure uom_code,
C.inv_subinventory_name,
C.inv_locator_id,
to_number(NULL) uc_header_id
FROM csi_item_instances C,
mtl_system_items_kfv M,
ahl_mc_relationships R,
ahl_item_associations_b A
WHERE C.inventory_item_id = M.inventory_item_id
AND C.inv_master_organization_id = M.organization_id
AND R.item_group_id = A.item_group_id
AND C.inventory_item_id = A.inventory_item_id
AND R.relationship_id = c_relationship_id
AND trunc(nvl(R.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(nvl(C.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND C.location_type_code IN ('INVENTORY')
--AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
AND (A.revision IS NULL OR A.revision = C.inventory_revision) --Added by Jerry on 03/31/2005
--
-- not installed in any position so far.
--
AND NOT EXISTS (
SELECT 1
FROM csi_ii_relationships i1
WHERE i1.subject_id = C.instance_id
AND i1.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i1.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i1.active_end_date, SYSDATE+1)) >trunc(SYSDATE)
)
--
-- its not issued to any workorder already.
--
AND C.wip_job_id IS NULL
--
-- Its not in the top node of any UC.
--
AND NOT EXISTS (
SELECT 1
FROM ahl_unit_config_headers H
WHERE H.csi_item_instance_id = C.instance_id
AND trunc(nvl(H.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
--
-- and it satisfies other checks like serial number, instance and item number that are passed
--
AND upper(M.concatenated_segments) LIKE nvl(c_item_number,'%')
AND upper(C.instance_number) LIKE nvl(c_instance_number,'%')
AND upper(nvl(C.serial_number, '%')) LIKE nvl(c_serial_number,'%')
AND M.organization_id IN (SELECT mp.master_organization_id
FROM mtl_parameters mp, org_organization_definitions ood
WHERE mp.organization_Id = ood.organization_id
-- jaramana on Feb 14, 2008
-- Removed reference to CLIENT_INFO
AND NVL(ood.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
AND C.inv_locator_id IN (
SELECT
ILOC.inventory_location_id
FROM
-- jaramana on Feb 14, 2008 for bug 6819370
-- Changed MTL_ITEM_LOCATIONS_KFV to MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS ILOC,
AHL_VISITS_B VST
WHERE
ILOC.subinventory_code = C.inv_subinventory_name
AND ILOC.organization_id = C.inv_organization_id
AND (ILOC.end_date_active IS NULL OR ILOC.end_date_active >= SYSDATE)
AND ILOC.segment19 = c_project_id
AND ILOC.physical_location_id = c_inv_locator_id
)
AND EXISTS(--If serial number is present then check the status is "in stores"
(SELECT
'X'
FROM
MTL_SERIAL_NUMBERS MSLN,
MFG_LOOKUPS SL
WHERE
C.serial_number is not null
AND MSLN.serial_number = C.serial_number
AND MSLN.inventory_item_id = C.inventory_item_id
AND MSLN.CURRENT_ORGANIZATION_ID = C.INV_ORGANIZATION_ID
AND MSLN.CURRENT_STATUS = SL.lookup_code
AND SL.lookup_type = 'SERIAL_NUM_STATUS'
AND MSLN.current_status = '3' -- "in stores"
)
UNION
--If serial number not present then check on hand quantity > 0
(
SELECT
'X'
FROM
MTL_ONHAND_QUANTITIES MOQ
WHERE
C.serial_number is null
AND MOQ.inventory_item_id = C.inventory_item_id
AND MOQ.ORGANIZATION_ID = C.INV_ORGANIZATION_ID
AND MOQ.TRANSACTION_QUANTITY > 0
)
)
UNION ALL
--
-- A position can include alternate subconfigurations.
-- This part of select clause is for picking top node instances of all alternate subconfigs.
--
SELECT C.instance_id,
C.instance_number,
C.inventory_item_id,
C.inv_master_organization_id,
C.quantity,
C.inventory_revision,
C.unit_of_measure uom_code,
C.inv_subinventory_name,
C.inv_locator_id,
U.uc_header_id uc_header_id
FROM ahl_unit_config_headers_v U,
csi_item_instances C,
mtl_system_items_kfv M
WHERE U.csi_instance_id = C.instance_id
AND C.inventory_item_id = M.inventory_item_id
AND C.inv_master_organization_id = M.organization_id
AND trunc(nvl(U.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND C.location_type_code IN ('INVENTORY')
--
-- Check to see this UC is a subconfig
--
AND EXISTS (
SELECT 1
FROM ahl_mc_config_relations R
WHERE R.mc_header_id = U.mc_header_id
AND R.relationship_id = c_relationship_id
AND trunc(nvl(R.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
--
-- sub config is in valid status
--
AND (
U.parent_instance_id IS NULL
AND U.uc_status_code in ('COMPLETE', 'INCOMPLETE')
)
--
-- its not issued to any workorder already.
--
AND C.wip_job_id IS NULL
--
-- its not a parent for any other mc position.
--
AND NOT EXISTS (
SELECT 1
FROM ahl_mc_relationships MR
WHERE MR.parent_relationship_id = c_relationship_id
AND trunc(nvl(MR.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(MR.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
--
-- and it satisfies other checks like serial number, instance and item number that are passed
--
AND upper(M.concatenated_segments) LIKE nvl(c_item_number,'%')
AND upper(C.instance_number) LIKE nvl(c_instance_number,'%')
AND upper(nvl(C.serial_number, '%')) LIKE nvl(c_serial_number,'%')
AND M.organization_id IN (SELECT mp.master_organization_id
FROM mtl_parameters mp, org_organization_definitions ood
WHERE mp.organization_Id = ood.organization_id
-- jaramana on Feb 14, 2008
-- Removed reference to CLIENT_INFO
AND NVL(ood.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
AND C.inv_locator_id IN (
SELECT
ILOC.inventory_location_id
FROM
-- jaramana on Feb 14, 2008 for bug 6819370
-- Changed MTL_ITEM_LOCATIONS_KFV to MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS ILOC,
AHL_VISITS_B VST
WHERE
ILOC.subinventory_code = C.inv_subinventory_name
AND ILOC.organization_id = C.inv_organization_id
AND (ILOC.end_date_active IS NULL OR ILOC.end_date_active >= SYSDATE)
AND ILOC.segment19 = c_project_id
AND ILOC.physical_location_id = c_inv_locator_id
)
AND EXISTS(--If serial number is present then check the status is "in stores"
(SELECT
'X'
FROM
MTL_SERIAL_NUMBERS MSLN,
MFG_LOOKUPS SL
WHERE
C.serial_number is not null
AND MSLN.serial_number = C.serial_number
AND MSLN.inventory_item_id = C.inventory_item_id
AND MSLN.CURRENT_ORGANIZATION_ID = C.INV_ORGANIZATION_ID
AND MSLN.CURRENT_STATUS = SL.lookup_code
AND SL.lookup_type = 'SERIAL_NUM_STATUS'
AND MSLN.current_status = '3' -- "in stores"
)
UNION
--If serial number not present then check on hand quantity > 0
(
SELECT
'X'
FROM
MTL_ONHAND_QUANTITIES MOQ
WHERE
C.serial_number is null
AND MOQ.inventory_item_id = C.inventory_item_id
AND MOQ.ORGANIZATION_ID = C.INV_ORGANIZATION_ID
AND MOQ.TRANSACTION_QUANTITY > 0
)
);
SELECT
relationship_id
FROM
ahl_mc_relationships
WHERE
relationship_id = p_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND mc_header_id IN (
SELECT
mc_header_id
FROM
ahl_mc_headers_b
WHERE
config_status_code = 'COMPLETE'
);
SELECT
'x'
FROM
ahl_unit_config_headers
WHERE
csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
UNION ALL
SELECT
'x'
FROM
csi_ii_relationships
WHERE
subject_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE);
SELECT
object_id
FROM
csi_ii_relationships
WHERE
object_id NOT IN (SELECT subject_id
FROM csi_ii_relationships
WHERE relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = c_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_status_code
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT A.csi_item_instance_id,
A.csi_object_version csi_object_version_number,
A.item_number,
A.item_description,
A.csi_instance_number,
A.inventory_item_id,
A.inventory_org_id,
A.organization_code,
A.serial_number,
A.revision,
A.lot_number,
A.uom_code,
A.quantity,
A.install_date,
A.mfg_date,
A.location_description,
A.party_type,
A.owner_id,
A.owner_number,
A.owner_name,
A.csi_location_id owner_site_id,
A.owner_site_number,
A.csi_party_object_version_num,
A.status,
A.condition,
A.wip_entity_name,
B.uc_header_id,
B.uc_name,
B.uc_status,
B.mc_header_id,
B.mc_name,
B.mc_revision,
B.mc_status,
B.position_ref,
B.root_uc_header_id
FROM ahl_unit_installed_details_v A,
ahl_unit_config_headers_v B
WHERE csi_item_instance_id = c_csi_item_instance_id
AND A.csi_item_instance_id = B.csi_instance_id (+)
AND trunc(nvl(B.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE);
SELECT priority
FROM ahl_item_associations_b
WHERE item_association_id = c_item_association_id;
SELECT object_version_number
FROM csi_ii_relationships
WHERE subject_id = c_instance_id
AND position_reference IS NULL
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
concatenated_segments
FROM
MTL_ITEM_LOCATIONS_KFV
WHERE
inventory_location_id = c_inv_location_id;
SELECT uc_status INTO l_status
FROM ahl_unit_config_headers_v
WHERE uc_header_id = l_instance_details_rec.root_uc_header_id;
SELECT
C.instance_id,
C.instance_number,
C.inventory_item_id,
C.inv_master_organization_id,
C.quantity,
C.inventory_revision,
C.unit_of_measure uom_code,
to_number(NULL) uc_header_id
FROM csi_item_instances C,
mtl_system_items_kfv M,
ahl_mc_relationships R,
ahl_item_associations_b A
WHERE C.inventory_item_id = M.inventory_item_id
AND C.inv_master_organization_id = M.organization_id
AND R.item_group_id = A.item_group_id
AND C.inventory_item_id = A.inventory_item_id
AND R.relationship_id = c_relationship_id
AND trunc(nvl(R.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(nvl(C.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
-- SATHAPLI::FP Bug 7498459, 27-Nov-2008 - For the root position, i.e. for UC header creation, make the inventory
-- instances available, which are not issued to any job. This should be done only for the edit UC flow, i.e.
-- when c_wip_job_id is NULL.
-- AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT', (DECODE(c_parent_instance_id, NULL, 'X', 'INVENTORY')))
AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
AND nvl(A.revision, nvl(C.inventory_revision,-1)) = nvl(C.inventory_revision,-1)
AND (
(c_wip_job_id IS NULL -- SATHAPLI, 30-Jan-2008 :: extra nodes should not come up during parts change
AND
EXISTS
(
SELECT 1
FROM csi_ii_relationships i2
WHERE i2.subject_id = C.instance_id
AND i2.position_reference IS NULL --because parent is not extra
-- SATHAPLI::FP ER 6504147, 18-Nov-2008
-- include extra nodes of all the parents uptill root
-- AND i2.object_id = NVL(c_parent_instance_id, -1)
AND i2.object_id IN (
SELECT i3.object_id
FROM csi_ii_relationships i3
START WITH i3.subject_id = nvl(c_parent_instance_id, -1)
AND i3.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY i3.subject_id = PRIOR i3.object_id
AND i3.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
UNION ALL
SELECT nvl(c_parent_instance_id, -1)
FROM DUAL
)
AND i2.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i2.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i2.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
)
OR
(NOT EXISTS
(
SELECT 1
FROM csi_ii_relationships i1
WHERE i1.subject_id = C.instance_id
AND i1.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i1.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i1.active_end_date, SYSDATE+1)) >trunc(SYSDATE)
)
-- SATHAPLI, 30-Jan-2008 :: If c_wip_job_id is not passed, instances issued to any job should not be fetched.
-- If c_wip_job_id is passed, then fetch only those instances which are issued to this job.
-- AND nvl(C.wip_job_id, -1) = nvl(c_wip_job_id, nvl(C.wip_job_id, -1))
AND ((c_wip_job_id IS NULL AND C.wip_job_id IS NULL)
OR
(c_wip_job_id IS NOT NULL AND c_wip_job_id = NVL(C.wip_job_id, -1))
)
)
)
--This wip_entity check is not necessary for an extra sibling nodes even for
--so just include it here.
AND NOT EXISTS
(
SELECT 1
FROM ahl_unit_config_headers H
WHERE H.csi_item_instance_id = C.instance_id
AND trunc(nvl(H.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
AND upper(M.concatenated_segments) LIKE c_item_number
AND upper(C.instance_number) LIKE c_instance_number
AND upper(nvl(C.serial_number, '%')) LIKE c_serial_number
AND EXISTS
(
SELECT 'X'
FROM mtl_parameters mp, inv_organization_info_v io
WHERE mp.master_organization_id = M.organization_id AND
mp.organization_Id = io.organization_id AND
NVL(io.operating_unit, mo_global.get_current_org_id()) =
mo_global.get_current_org_id()
)
--Plus those units could be installed
UNION ALL
SELECT
C.instance_id,
C.instance_number,
C.inventory_item_id,
C.inv_master_organization_id,
C.quantity,
C.inventory_revision,
C.unit_of_measure uom_code,
U.uc_header_id uc_header_id
FROM (
SELECT UH.unit_config_header_id uc_header_id,
UH.csi_item_instance_id csi_instance_id,
UH.master_config_id mc_header_id,
UH.unit_config_status_code uc_status_code,
UH.active_end_date,
CR.object_id parent_instance_id
FROM ahl_unit_config_headers UH, csi_ii_relationships CR
WHERE UH.csi_item_instance_id = CR.subject_id (+) AND
CR.relationship_type_code (+) = 'COMPONENT-OF' AND
trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)
) U,
csi_item_instances C,
mtl_system_items_kfv M
WHERE U.csi_instance_id = C.instance_id
AND C.inventory_item_id = M.inventory_item_id
AND C.inv_master_organization_id = M.organization_id
AND trunc(nvl(U.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
--Per Barry, if the top instance is expired then this UC is also taken as
--Either JOIN or IN, performance cost is bigger than EXISTS
AND EXISTS
(
SELECT 1
FROM ahl_mc_config_relations R
WHERE R.mc_header_id = U.mc_header_id
AND R.relationship_id = c_relationship_id
AND trunc(nvl(R.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
--Either a separate unit or extra sibling subunit
AND (
(
U.parent_instance_id IS NULL
AND decode(U.uc_status_code, 'DRAFT', 'DRAFT',
'APPROVAL_REJECTED', 'DRAFT',
'COMPLETE','COMPLETE',
'INCOMPLETE','COMPLETE', NULL) = c_uc_status
-- SATHAPLI, 30-Jan-2008 :: If c_wip_job_id is not passed, instances issued to any job should not be fetched.
-- If c_wip_job_id is passed, then fetch only those instances which are issued to this job.
-- AND nvl(C.wip_job_id, -1) = nvl(c_wip_job_id, nvl(C.wip_job_id, -1))
AND ((c_wip_job_id IS NULL AND C.wip_job_id IS NULL)
OR
(c_wip_job_id IS NOT NULL AND c_wip_job_id = NVL(C.wip_job_id, -1))
)
)
--This wip_entity check is not necessary for an extra sibling nodes even
--so just include it here.
OR
(
c_wip_job_id IS NULL -- SATHAPLI::ER# 6504147 :: extra nodes should not come up during parts change
AND
-- SATHAPLI::FP ER 6504147, 18-Nov-2008
-- include extra nodes of all the parents uptill root
-- U.parent_instance_id = nvl(c_parent_instance_id, -1)
U.parent_instance_id IN (
SELECT i3.object_id
FROM csi_ii_relationships i3
START WITH i3.subject_id = nvl(c_parent_instance_id, -1)
AND i3.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY i3.subject_id = PRIOR i3.object_id
AND i3.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
UNION ALL
SELECT nvl(c_parent_instance_id, -1)
FROM DUAL
)
AND EXISTS
(SELECT 1
FROM csi_ii_relationships CI
WHERE CI.object_id = U.parent_instance_id
AND CI.subject_id = U.csi_instance_id
AND CI.position_reference IS NULL
AND CI.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(CI.active_start_date,SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(CI.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
)
)
AND NOT EXISTS
(SELECT 1
FROM ahl_mc_relationships MR
WHERE MR.parent_relationship_id = c_relationship_id
AND trunc(nvl(MR.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(MR.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
AND upper(M.concatenated_segments) LIKE c_item_number
AND upper(C.instance_number) LIKE c_instance_number
AND upper(nvl(C.serial_number, '%')) LIKE c_serial_number
AND EXISTS
(
SELECT 'X'
FROM mtl_parameters mp, inv_organization_info_v io
WHERE mp.master_organization_id = M.organization_id AND
mp.organization_Id = io.organization_id AND
NVL(io.operating_unit, mo_global.get_current_org_id()) =
mo_global.get_current_org_id()
)
AND ahl_util_uc_pkg.IS_UNIT_QUARANTINED(U.uc_header_id , null) =
FND_API.G_FALSE
ORDER BY 2;
SELECT relationship_id
FROM ahl_mc_relationships
WHERE relationship_id = p_relationship_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND mc_header_id IN (SELECT mc_header_id
FROM ahl_mc_headers_b
WHERE config_status_code = 'COMPLETE');
SELECT priority
FROM ahl_item_associations_b
WHERE item_association_id = c_item_association_id;
select a.csi_item_instance_id,
a.csi_object_version csi_object_version_number,
a.item_number,
a.item_description,
a.csi_instance_number,
a.inventory_item_id,
a.inventory_org_id,
a.organization_code,
a.serial_number,
a.revision,
a.lot_number,
a.uom_code,
a.quantity,
a.install_date,
a.mfg_date,
a.location_description,
a.party_type,
a.owner_id,
a.owner_number,
a.owner_name,
a.csi_location_id owner_site_id,
a.owner_site_number,
a.csi_party_object_version_num,
a.status,
a.condition,
a.wip_entity_name,
b.uc_header_id,
b.uc_name,
b.uc_status,
b.mc_header_id,
b.mc_name,
b.mc_revision,
b.mc_status,
b.position_ref,
b.root_uc_header_id
from ahl_unit_installed_details_v a,
(
SELECT U.unit_config_header_id uc_header_id,
U.name uc_name,
UCSC.meaning uc_status,
U.master_config_id mc_header_id,
M.name mc_name,
M.revision mc_revision,
MCSC.meaning mc_status,
MRSC.meaning position_ref,
(
SELECT unit_config_header_id
FROM ahl_unit_config_headers
WHERE parent_uc_header_id IS NULL
START WITH
unit_config_header_id = U.unit_config_header_id
CONNECT BY
unit_config_header_id = PRIOR parent_uc_header_id
) root_uc_header_id,
U.csi_item_instance_id csi_instance_id,
U.active_end_date active_end_date
FROM AHL_UNIT_CONFIG_HEADERS U, AHL_MC_HEADERS_B M,
AHL_MC_RELATIONSHIPS R, FND_LOOKUP_VALUES UCSC,
FND_LOOKUP_VALUES MRSC, FND_LOOKUP_VALUES MCSC
WHERE U.master_config_id = M.mc_header_id AND
M.mc_header_id = R.mc_header_id AND
R.parent_relationship_id IS NULL AND
U.unit_config_status_code = UCSC.lookup_code AND
'AHL_CONFIG_STATUS' = UCSC.lookup_type AND
UCSC.language = USERENV('LANG') AND
M.config_status_code = MCSC.lookup_code AND
'AHL_CONFIG_STATUS' = MCSC.lookup_type AND
MCSC.language = USERENV('LANG') AND
R.position_ref_code = MRSC.lookup_code AND
'AHL_POSITION_REFERENCE' = MRSC.lookup_type AND
MRSC.language = USERENV('LANG')
) b
where a.csi_item_instance_id = c_csi_item_instance_id
and a.csi_item_instance_id = b.csi_instance_id (+)
and trunc(nvl(b.active_end_date (+), sysdate+1)) > trunc(sysdate);
SELECT wip_entity_id
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT 'x'
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
UNION ALL
SELECT 'x'
FROM csi_ii_relationships
WHERE subject_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE);
SELECT object_id
FROM csi_ii_relationships
WHERE object_id NOT IN (SELECT subject_id
FROM csi_ii_relationships
WHERE relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = c_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 object_id
FROM csi_ii_relationships co
WHERE NOT EXISTS
(
SELECT 'X'
FROM csi_ii_relationships ci
WHERE ci.relationship_type_code = 'COMPONENT-OF' AND
ci.subject_id = co.object_id AND
trunc(nvl(ci.active_start_date,SYSDATE)) <= trunc(SYSDATE) AND
trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
)
START WITH co.subject_id = c_instance_id AND
co.relationship_type_code = 'COMPONENT-OF' AND
trunc(nvl(co.active_start_date,SYSDATE)) <= trunc(SYSDATE) AND
trunc(nvl(co.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY co.subject_id = PRIOR co.object_id AND
co.relationship_type_code = 'COMPONENT-OF' AND
trunc(nvl(co.active_start_date,SYSDATE)) <= trunc(SYSDATE) AND
trunc(nvl(co.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT unit_config_status_code
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_version_number
FROM csi_ii_relationships
WHERE subject_id = c_instance_id
AND position_reference IS NULL
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 decode(l_top_uc_status, 'DRAFT', 'DRAFT',
'APPROVAL_REJECTED', 'DRAFT',
'COMPLETE','COMPLETE',
'INCOMPLETE','COMPLETE', NULL) INTO l_top_uc_status
FROM dual;
SELECT uc_status INTO l_status
FROM ahl_unit_config_headers_v
WHERE uc_header_id = l_instance_details_rec.root_uc_header_id;
SELECT FLV.meaning INTO l_status
FROM AHL_UNIT_CONFIG_HEADERS AUCH, FND_LOOKUP_VALUES FLV
WHERE AUCH.unit_config_header_id = l_instance_details_rec.root_uc_header_id AND
AUCH.unit_config_status_code = FLV.lookup_code AND
FLV.lookup_type = 'AHL_CONFIG_STATUS' AND
FLV.language = USERENV('LANG');
SELECT unit_config_header_id,
unit_config_status_code,
active_uc_status_code,
csi_item_instance_id,
parent_uc_header_id
FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT location_id,
location_type_code,
party_id,
party_source_table,
instance_party_id,
csi.wip_job_id
FROM csi_item_instances csi, csi_i_parties p
WHERE csi.instance_id = p.instance_id
AND p.relationship_type_code = 'OWNER'
AND csi.instance_id = p_csi_instance_id
AND trunc(nvl(csi.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT party_account_id
FROM csi_ip_accounts
WHERE relationship_type_code = 'OWNER'
AND instance_party_id = p_instance_party_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);