[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x'
from ahl_item_groups_b
where name = p_name and
nvl(p_item_group_id, -1) <> item_group_id and
nvl(p_source_id, -1) <> item_group_id;
SELECT priority
FROM ahl_item_associations_b
WHERE item_group_id = p_item_group_id
group by priority
having count(item_group_id) > 1;
SELECT revision
FROM ahl_item_associations_b
WHERE item_group_id = p_item_group_id
group by inventory_item_id,revision
having count(INVENTORY_ITEM_ID) > 1;
SELECT SOURCE.item_group_id item_group_id,
SOURCE.inventory_item_id inventory_item_id,
SOURCE.inventory_org_id inventory_org_id,
SOURCE.revision revision,
MTL.concatenated_segments concatenated_segments
FROM AHL_ITEM_ASSOCIATIONS_B SOURCE, AHL_ITEM_ASSOCIATIONS_B REVISION,
AHL_ITEM_GROUPS_B IGROUP, MTL_SYSTEM_ITEMS_KFV MTL
WHERE REVISION.item_association_id = p_assoc_id AND
IGROUP.item_group_id = REVISION.item_group_id AND
SOURCE.item_group_id = IGROUP.source_item_group_id AND
SOURCE.inventory_item_id = REVISION.inventory_item_id AND
SOURCE.inventory_org_id = REVISION.inventory_org_id AND
MTL.inventory_item_id = SOURCE.inventory_item_id AND
MTL.organization_id = SOURCE.inventory_org_id;
PROCEDURE validate_IG_update(
p_ItemGroup_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_IG_update';
SELECT SOURCE.item_group_id,
SOURCE.inventory_item_id,
SOURCE.inventory_org_id,
SOURCE.revision,
MTL.concatenated_segments,
FL.meaning
BULK COLLECT
INTO l_item_group_id_tbl,
l_inventory_item_id_tbl,
l_inventory_org_id_tbl,
l_revision_tbl,
l_concatenated_segments_tbl,
l_interchange_type_tbl
FROM AHL_ITEM_ASSOCIATIONS_B SOURCE, AHL_ITEM_ASSOCIATIONS_B REVISION,
AHL_ITEM_GROUPS_B IGROUP, MTL_SYSTEM_ITEMS_KFV MTL,
FND_LOOKUP_VALUES_VL FL
WHERE IGROUP.item_group_id = p_ItemGroup_id AND
REVISION.item_group_id = IGROUP.item_group_id AND
SOURCE.item_group_id = IGROUP.source_item_group_id AND
SOURCE.inventory_item_id = REVISION.inventory_item_id AND
SOURCE.inventory_org_id = REVISION.inventory_org_id AND
MTL.inventory_item_id = SOURCE.inventory_item_id AND
MTL.organization_id = SOURCE.inventory_org_id AND
FL.lookup_type = 'AHL_INTERCHANGE_ITEM_TYPE' AND
FL.lookup_code = REVISION.interchange_type_code AND
NVL(REVISION.interchange_type_code, 'DELETED') IN ('REFERENCE', 'DELETED');
' items in the IG for update.');
END validate_IG_update;
SELECT NVL(comms_nl_trackable_flag,'N'), concatenated_segments,
SERIAL_NBR_CNTRL_CODE,revision_qty_cntrl_code
FROM ahl_mtl_items_non_ou_v
WHERE inventory_item_id = p_inventory_item_id
AND inventory_org_id = p_inventory_org_id;
SELECT NVL(comms_nl_trackable_flag,'N'), concatenated_segments,
SERIAL_NBR_CNTRL_CODE,revision_qty_cntrl_code
FROM ahl_mtl_items_non_ou_v
WHERE inventory_item_id = p_inventory_item_id
AND inventory_org_id = p_inventory_org_id;
SELECT organization_code,
master_organization_id
FROM mtl_parameters
WHERE organization_id = p_inventory_org_id;
SELECT 'x'
FROM mtl_item_revisions
WHERE inventory_item_id = p_inventory_id
AND organization_id = p_organization_id
AND revision = p_revision;
SELECT REVISION
FROM AHL_ITEM_ASSOCIATIONS_VL
WHERE item_group_id = p_item_group_id
AND inventory_org_id = ( Select master_organization_id
from mtl_parameters
where organization_id = p_inventory_org_id)
AND inventory_item_id = p_inventory_item_id;
SELECT 'x'
FROM AHL_ITEM_ASSOCIATIONS_VL
WHERE item_group_id = p_item_group_id
--AND (REVISION IS NULL OR REVISION = p_item_revision)
AND (REVISION IS NULL)
AND inventory_org_id = ( Select master_organization_id
from mtl_parameters
where organization_id = p_inventory_org_id)
AND inventory_item_id = p_inventory_item_id;
SELECT 'x'
FROM AHL_ITEM_ASSOCIATIONS_VL
WHERE item_group_id = p_item_group_id
AND (REVISION = p_item_revision)
AND inventory_org_id = (Select master_organization_id
from mtl_parameters
where organization_id = p_inventory_org_id)
AND inventory_item_id = p_inventory_item_id;
SELECT 'x'
INTO
l_junk
FROM
AHL_ITEM_ASSOCIATIONS_VL
WHERE
item_group_id = p_item_group_id
AND inventory_org_id = (Select master_organization_id
from mtl_parameters
where organization_id = p_inventory_org_id)
AND inventory_item_id = p_inventory_item_id;
/*ELSE --commented out for mass update changes --Priyan
-- While updating check is made to see if the (itemgroup,item,org and rev) combination already exists in the DB,
-- if so the user is not allowed to update the revision. Else the revision is updated
OPEN rev_exists_csr(p_item_group_id,
p_inventory_item_id,
p_inventory_org_id,
p_item_revision);
SELECT 'x'
FROM AHL_ITEM_ASSOCIATIONS_VL
WHERE item_group_id = p_item_group_id
AND NVL(REVISION,'000') = p_item_revision
AND inventory_org_id = ( Select master_organization_id
from mtl_parameters
where organization_id = p_inventory_org_id)
AND inventory_item_id = p_inventory_item_id;
SELECT posn.relationship_id
FROM ahl_relationships_b posn, ahl_item_associations_b iassoc,
ahl_relationships_b topnode
WHERE trunc(nvl(posn.active_end_date,sysdate+1)) > trunc(sysdate)
AND iassoc.item_group_id = posn.item_group_id
AND iassoc.item_group_id = p_item_group_id
AND topnode.relationship_id = (SELECT reln.relationship_id
FROM ahl_relationships_b reln
WHERE parent_relationship_id is null
START WITH relationship_id = posn.relationship_id
AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
CONNECT BY PRIOR parent_relationship_id = relationship_id
AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
);
SELECT relationship_id
FROM ahl_mc_relationships
WHERE trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate)
AND item_group_id = p_item_group_id;
SELECT instance_id csi_item_instance_id
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 reln.relationship_type_code = 'COMPONENT-OF'
AND reln.position_reference = p_position_reference
AND csi.inventory_item_id = p_inventory_item_id
AND csi.last_vld_organization_id = p_inventory_org_id;
SELECT 'x'
FROM DUAL
WHERE EXISTS (SELECT name
FROM ahl_unit_config_headers unit, csi_item_instances csi
WHERE unit.csi_item_instance_id = csi.instance_id
AND master_config_id = p_relationship_id
AND TRUNC(SYSDATE) < TRUNC(NVL(unit.active_end_date, SYSDATE+1))
AND csi.inventory_item_id = p_inventory_item_id
AND csi.last_vld_organization_id = p_inventory_org_id
);
SELECT
row_id,
item_association_id,
item_group_id,
inventory_item_id,
inventory_org_id,
uom_code,
quantity,
concatenated_segments,
revision_qty_cntrl_code,
serial_nbr_cntrl_code ,
organization_code,
REVISION
FROM AHL_ITEM_ASSOCIATIONS_V
WHERE item_association_id = p_item_assoc_id;
FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
PROCEDURE Insert_Item_Group(p_x_item_grp_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type) IS
l_item_grp_id NUMBER;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
SELECT AHL_ITEM_GROUPS_B_S.nextval INTO
l_item_grp_id from DUAL;
l_last_updated_by := to_number(fnd_global.USER_ID);
l_last_update_login := to_number(fnd_global.LOGIN_ID);
AHL_ITEM_GROUPS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_ITEM_GROUP_ID => l_item_grp_id,
X_TYPE_CODE => p_x_item_grp_rec.type_code,
X_STATUS_CODE => 'DRAFT',
X_SOURCE_ITEM_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => 1,
X_NAME => p_x_item_grp_rec.name,
X_ATTRIBUTE_CATEGORY => p_x_item_grp_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => p_x_item_grp_rec.attribute1,
X_ATTRIBUTE2 => p_x_item_grp_rec.attribute2,
X_ATTRIBUTE3 => p_x_item_grp_rec.attribute3,
X_ATTRIBUTE4 => p_x_item_grp_rec.attribute4,
X_ATTRIBUTE5 => p_x_item_grp_rec.attribute5,
X_ATTRIBUTE6 => p_x_item_grp_rec.attribute6,
X_ATTRIBUTE7 => p_x_item_grp_rec.attribute7,
X_ATTRIBUTE8 => p_x_item_grp_rec.attribute8,
X_ATTRIBUTE9 => p_x_item_grp_rec.attribute9,
X_ATTRIBUTE10 => p_x_item_grp_rec.attribute10,
X_ATTRIBUTE11 => p_x_item_grp_rec.attribute11,
X_ATTRIBUTE12 => p_x_item_grp_rec.attribute12,
X_ATTRIBUTE13 => p_x_item_grp_rec.attribute13,
X_ATTRIBUTE14 => p_x_item_grp_rec.attribute14,
X_ATTRIBUTE15 => p_x_item_grp_rec.attribute15,
X_DESCRIPTION => p_x_item_grp_rec.description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => to_number(fnd_global.USER_ID),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_last_updated_by,
X_LAST_UPDATE_LOGIN => l_last_update_login);
p_x_item_grp_rec.ITEM_GROUP_ID := l_item_grp_id; -- update id in record variable.
END Insert_Item_Group;
SELECT AHL_ITEM_ASSOCIATIONS_B_S.nextval INTO
l_item_association_id from DUAL;
AHL_ITEM_ASSOCIATIONS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_ITEM_ASSOCIATION_ID => l_item_association_id,
X_SOURCE_ITEM_ASSOCIATION_ID => NULL,
X_OBJECT_VERSION_NUMBER => 1,
X_ITEM_GROUP_ID => l_item_assoc_rec.ITEM_GROUP_ID,
X_INVENTORY_ITEM_ID => l_item_assoc_rec.INVENTORY_ITEM_ID,
X_INVENTORY_ORG_ID => l_item_assoc_rec.INVENTORY_ORG_ID,
X_PRIORITY => l_item_assoc_rec.PRIORITY,
X_UOM_CODE => l_item_assoc_rec.UOM_CODE,
X_QUANTITY => l_item_assoc_rec.QUANTITY,
X_REVISION => l_item_assoc_rec.REVISION,
X_INTERCHANGE_TYPE_CODE => l_item_assoc_rec.INTERCHANGE_TYPE_CODE,
X_ITEM_TYPE_CODE => NULL,
X_ATTRIBUTE_CATEGORY => l_item_assoc_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_item_assoc_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_item_assoc_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_item_assoc_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_item_assoc_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_item_assoc_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_item_assoc_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_item_assoc_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_item_assoc_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_item_assoc_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_item_assoc_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_item_assoc_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_item_assoc_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_item_assoc_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_item_assoc_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_item_assoc_rec.ATTRIBUTE15,
X_INTERCHANGE_REASON => l_item_assoc_rec.INTERCHANGE_REASON,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.USER_ID,
X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID
);
PROCEDURE Update_Association(p_item_assoc_rec IN AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
p_row_id IN UROWID) IS
CURSOR Item_assoc_csr(p_rowid UROWID) IS
SELECT
b.ROWID ROW_ID,
b.ITEM_ASSOCIATION_ID,
b.SOURCE_ITEM_ASSOCIATION_ID,
b.ITEM_GROUP_ID,
b.INVENTORY_ITEM_ID,
b.INVENTORY_ORG_ID,
b.REVISION,
b.PRIORITY,
b.QUANTITY,
b.UOM_CODE,
b.INTERCHANGE_TYPE_CODE,
tl.INTERCHANGE_REASON,
-- b.ITEM_TYPE_CODE,
b.OBJECT_VERSION_NUMBER,
tl.LANGUAGE,
tl.SOURCE_LANG,
b.ATTRIBUTE_CATEGORY,
b.ATTRIBUTE1,
b.ATTRIBUTE2,
b.ATTRIBUTE3,
b.ATTRIBUTE4,
b.ATTRIBUTE5,
b.ATTRIBUTE6 ,
b.ATTRIBUTE7 ,
b.ATTRIBUTE8,
b.ATTRIBUTE9 ,
b.ATTRIBUTE10,
b.ATTRIBUTE11 ,
b.ATTRIBUTE12,
b.ATTRIBUTE13,
b.ATTRIBUTE14,
b.ATTRIBUTE15,
b.LAST_UPDATE_DATE,
b.LAST_UPDATED_BY,
b.LAST_UPDATE_LOGIN
FROM ahl_item_associations_b b, ahl_item_associations_tl tl
WHERE b.item_association_id = tl.item_association_id
and b.rowid = p_rowid
AND tl.LANGUAGE = USERENV('LANG')
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
AHL_ITEM_ASSOCIATIONS_PKG.UPDATE_ROW(
X_ITEM_ASSOCIATION_ID => l_item_assoc_rec.item_association_id,
X_SOURCE_ITEM_ASSOCIATION_ID => l_old_item_assoc_rec.SOURCE_ITEM_ASSOCIATION_ID,
X_OBJECT_VERSION_NUMBER => l_old_item_assoc_rec.object_version_number + 1,
X_ITEM_GROUP_ID => l_item_assoc_rec.item_group_id,
X_INVENTORY_ITEM_ID => l_item_assoc_rec.inventory_item_id,
X_INVENTORY_ORG_ID => l_item_assoc_rec.inventory_org_id,
X_PRIORITY => l_item_assoc_rec.priority,
X_UOM_CODE => l_item_assoc_rec.uom_code,
X_QUANTITY => l_item_assoc_rec.quantity,
X_REVISION => l_item_assoc_rec.revision,
X_INTERCHANGE_TYPE_CODE => l_item_assoc_rec.interchange_type_code,
X_ITEM_TYPE_CODE => NULL,
X_ATTRIBUTE_CATEGORY => l_item_assoc_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_item_assoc_rec.attribute1,
X_ATTRIBUTE2 => l_item_assoc_rec.attribute2,
X_ATTRIBUTE3 => l_item_assoc_rec.attribute3,
X_ATTRIBUTE4 => l_item_assoc_rec.attribute4,
X_ATTRIBUTE5 => l_item_assoc_rec.attribute5,
X_ATTRIBUTE6 => l_item_assoc_rec.attribute6,
X_ATTRIBUTE7 => l_item_assoc_rec.attribute7,
X_ATTRIBUTE8 => l_item_assoc_rec.attribute8,
X_ATTRIBUTE9 => l_item_assoc_rec.attribute9,
X_ATTRIBUTE10 => l_item_assoc_rec.attribute10,
X_ATTRIBUTE11 => l_item_assoc_rec.attribute11,
X_ATTRIBUTE12 => l_item_assoc_rec.attribute12,
X_ATTRIBUTE13 => l_item_assoc_rec.attribute13,
X_ATTRIBUTE14 => l_item_assoc_rec.attribute14,
X_ATTRIBUTE15 => l_item_assoc_rec.attribute15,
X_INTERCHANGE_REASON => l_item_assoc_rec.interchange_reason,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.USER_ID,
X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID);
END Update_Association;
PROCEDURE Delete_Association(p_item_assoc_rec IN AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
p_row_id IN UROWID) IS
CURSOR Item_assoc_csr(p_row_id UROWID) IS
SELECT
Object_version_number
FROM ahl_item_associations_vl
WHERE row_id = p_row_id
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
DELETE ahl_item_associations_b
WHERE item_association_id = p_item_assoc_rec.item_association_id;
DELETE ahl_item_associations_tl
WHERE item_association_id = p_item_assoc_rec.item_association_id;
END Delete_Association;
l_inventory_list.DELETE;
'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before calling Insert_Item_group');
Insert_Item_group(p_x_item_group_rec);
'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After calling Insert_Item_group');
SELECT
b.ROWID ROW_ID,
b.ITEM_GROUP_ID,
b.source_item_group_id,
b.NAME,
b.type_code,
b.status_code,
b.OBJECT_VERSION_NUMBER,
b.ATTRIBUTE_CATEGORY,
b.ATTRIBUTE1,
b.ATTRIBUTE2,
b.ATTRIBUTE3,
b.ATTRIBUTE4,
b.ATTRIBUTE5,
b.ATTRIBUTE6,
b.ATTRIBUTE7,
b.ATTRIBUTE8,
b.ATTRIBUTE9,
b.ATTRIBUTE10,
b.ATTRIBUTE11,
b.ATTRIBUTE12,
b.ATTRIBUTE13,
b.ATTRIBUTE14,
b.ATTRIBUTE15,
b.LAST_UPDATE_DATE,
b.LAST_UPDATED_BY,
b.CREATION_DATE,
b.CREATED_BY,
b.LAST_UPDATE_LOGIN,
TL.LANGUAGE,
TL.SOURCE_LANG,
TL.DESCRIPTION
FROM
AHL_ITEM_GROUPS_B b, AHL_ITEM_GROUPS_TL tl
WHERE
b.ITEM_GROUP_ID = tl.ITEM_GROUP_ID
AND b.ITEM_GROUP_ID = p_item_group_id
AND tl.LANGUAGE = USERENV('LANG')
FOR UPDATE OF b.OBJECT_VERSION_NUMBER NOWAIT;
l_update_flag VARCHAR2(1) := 'N';
FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_NOUPDATE');
FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_STAT_NOUPDATE');
FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NOUPDATE');
l_inventory_list.DELETE;
AHL_ITEM_GROUPS_PKG.UPDATE_ROW(
X_ITEM_GROUP_ID => l_old_item_group_rec.item_group_id,
X_TYPE_CODE => l_old_item_group_rec.type_code,
X_STATUS_CODE => l_old_item_group_rec.status_code,
X_SOURCE_ITEM_GROUP_ID => l_old_item_group_rec.source_item_group_id,
X_OBJECT_VERSION_NUMBER => l_old_item_group_rec.OBJECT_VERSION_NUMBER + 1,
X_NAME => l_old_item_group_rec.name,
X_ATTRIBUTE_CATEGORY => l_item_group_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_item_group_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_item_group_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_item_group_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_item_group_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_item_group_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_item_group_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_item_group_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_item_group_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_item_group_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_item_group_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_item_group_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_item_group_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_item_group_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_item_group_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_item_group_rec.ATTRIBUTE15,
X_DESCRIPTION => l_item_group_rec.DESCRIPTION,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.USER_ID,
X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID);
END IF; /* update only if operation_flag set */
Delete_Association(p_x_items_tbl(i),l_rowid_tbl(i));
Update_Association(p_x_items_tbl(i), l_rowid_tbl(i));
l_update_flag := 'Y';
IF (l_update_flag = 'Y') THEN
IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling validate_IG_update.');
validate_IG_update
(
p_ItemGroup_id => p_item_group_rec.item_group_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT
b.ROWID ROW_ID,
b.ITEM_GROUP_ID,
b.OBJECT_VERSION_NUMBER,
b.NAME,
b.Status_Code,
b.source_item_group_id
FROM
AHL_ITEM_GROUPS_B b
WHERE
b.ITEM_GROUP_ID = p_item_group_id
FOR UPDATE OF b.OBJECT_VERSION_NUMBER NOWAIT;
SELECT 'x'
from ahl_mc_relationships_v posn, ahl_mc_relationships_v topnode
where trunc(nvl(posn.active_end_date,sysdate+1)) > trunc(sysdate)
and posn.item_group_id = p_item_group_id
and topnode.relationship_id = (SELECT reln.relationship_id from
ahl_mc_relationships reln
where parent_relationship_id is null
start with relationship_id = posn.relationship_id
and trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
connect by prior parent_relationship_id = relationship_id
and trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
);
SELECT 'x'
FROM ahl_mc_relationships
WHERE trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate)
AND item_group_id = p_item_group_id;
SELECT 'x'
FROM ahl_item_comp_details
WHERE trunc(nvl(effective_end_date, sysdate + 1)) > trunc(sysdate)
AND item_group_id = p_item_group_id;
UPDATE ahl_mc_relationships
SET temp_item_group_id = null
WHERE item_group_id = p_item_group_rec.item_group_id;
/* AHL_ITEM_ASSOCIATIONS_PKG.DELETE_ROW(
X_ITEM_ASSOCIATION_ID => p_item_group_rec.item_group_id
);
DELETE AHL_ITEM_ASSOCIATIONS_TL
WHERE item_association_id IN ( SELECT item_association_id
FROM ahl_item_associations_b
WHERE item_group_id = p_item_group_rec.item_group_id );
DELETE AHL_ITEM_ASSOCIATIONS_B
WHERE item_group_id = p_item_group_rec.item_group_id;
AHL_ITEM_GROUPS_PKG.DELETE_ROW(
X_ITEM_GROUP_ID => p_item_group_rec.item_group_id
);
Update Ahl_Item_groups_b
set status_code ='REMOVED',
object_version_number = object_version_number +1
Where item_group_id = p_item_group_rec.item_group_id;
Select Name,
Status_code,
Object_version_number,
source_item_group_id
From ahl_item_groups_vl
Where item_group_id = c_itemgroup_id;
Select 'x' from dual
Where exists ( select item_association_id from ahl_item_associations_vl
Where Item_Group_id = c_itemgroup_id and
INTERCHANGE_TYPE_CODE in ('1-WAY INTERCHANGEABLE','2-WAY INTERCHANGEABLE') );
select 'x'
from ahl_mc_relationships a, ahl_mc_relationships b
where b.RELATIONSHIP_ID = a.PARENT_RELATIONSHIP_ID
and a.ITEM_GROUP_ID = c_itemgroup_id
and exists
( select 'x'
from ahl_item_associations_b
where item_group_id = c_itemgroup_id
and quantity > 1 );
select 'x'
from ahl_item_groups_b
where name = p_name and
p_source_item_group_id <> item_group_id;
Update AHL_ITEM_GROUPS_B
Set STATUS_CODE=l_upd_status,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
Where ITEM_GROUP_ID = p_source_item_group_id
and OBJECT_VERSION_NUMBER=p_object_Version_number;
Select
Name,
Status_Code,
Type_Code,
Description,
object_version_number ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from ahl_item_groups_vl
Where Item_Group_id = c_itemgroup_id;
Select
item_association_id ,
object_version_number ,
item_group_id ,
inventory_item_id ,
inventory_org_id ,
priority ,
uom_code ,
quantity ,
revision ,
interchange_type_code ,
interchange_reason ,
source_item_association_id,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from ahl_item_associations_vl
where item_group_id = c_itemgroup_id;
Select 'x'
from ahl_item_groups_vl
where source_item_group_id = c_itemgroup_id and
status_code <> 'COMPLETE';
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
SELECT relationship_id, object_version_number
FROM ahl_mc_relationships
WHERE item_group_id = c_item_group_id;
Select AHL_ITEM_GROUPS_B_S.nextval
into l_item_group_id
from dual;
l_last_updated_by := to_number(fnd_global.USER_ID);
l_last_update_login := to_number(fnd_global.LOGIN_ID);
ahl_item_groups_pkg.insert_row(
x_rowid => l_rowid,
x_item_group_id => l_item_group_id,
x_type_code => l_itemgroup_det.type_code,
x_status_code => 'DRAFT',
x_source_item_group_id => p_source_ItemGroup_id,
x_object_version_number => 1,
x_name => l_itemgroup_det.name,
x_attribute_category => l_itemgroup_det.attribute_category,
x_attribute1 => l_itemgroup_det.attribute1,
x_attribute2 => l_itemgroup_det.attribute2,
x_attribute3 => l_itemgroup_det.attribute3,
x_attribute4 => l_itemgroup_det.attribute4,
x_attribute5 => l_itemgroup_det.attribute5,
x_attribute6 => l_itemgroup_det.attribute6,
x_attribute7 => l_itemgroup_det.attribute7,
x_attribute8 => l_itemgroup_det.attribute8,
x_attribute9 => l_itemgroup_det.attribute9,
x_attribute10 => l_itemgroup_det.attribute10,
x_attribute11 => l_itemgroup_det.attribute11,
x_attribute12 => l_itemgroup_det.attribute12,
x_attribute13 => l_itemgroup_det.attribute13,
x_attribute14 => l_itemgroup_det.attribute14,
x_attribute15 => l_itemgroup_det.attribute15,
x_description => l_itemgroup_det.description,
x_creation_date => sysdate,
x_created_by => l_created_by,
x_last_update_date => sysdate,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login);
UPDATE ahl_mc_relationships
SET temp_item_group_id = x_itemgroup_id,
object_version_number = item_group_rec.object_version_number,
last_update_date = sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE relationship_id = item_group_rec.relationship_id and
trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate);
'Updated MC position '||item_group_rec.relationship_id||' with temp_item_group_id '||x_itemgroup_id
);
SELECT AHL_ITEM_ASSOCIATIONS_B_S.nextval INTO
l_item_association_id from DUAL;
ahl_item_associations_pkg.insert_row
(
x_rowid => l_rowid,
x_item_association_id => l_item_association_id,
x_source_item_association_id => I.source_item_association_id,
x_object_version_number => 1,
x_item_group_id => l_item_group_id,
x_inventory_item_id => I.inventory_item_id,
x_inventory_org_id => I.INVENTORY_ORG_ID ,
x_priority => I.PRIORITY ,
x_uom_code => I.UOM_CODE ,
x_quantity => I.QUANTITY ,
x_revision => I.REVISION ,
x_interchange_type_code => I.INTERCHANGE_TYPE_CODE ,
x_item_type_code => null,
x_attribute_category => I.ATTRIBUTE_CATEGORY,
x_attribute1 => i.attribute1,
x_attribute2 => i.attribute2,
x_attribute3 => i.attribute3,
x_attribute4 => i.attribute4,
x_attribute5 => i.attribute5,
x_attribute6 => i.attribute6,
x_attribute7 => i.attribute7,
x_attribute8 => i.attribute8,
x_attribute9 => i.attribute9,
x_attribute10 => i.attribute10,
x_attribute11 => i.attribute11,
x_attribute12 => i.attribute12,
x_attribute13 => i.attribute13,
x_attribute14 => i.attribute14,
x_attribute15 => i.attribute15,
x_interchange_reason => I.INTERCHANGE_REASON,
x_creation_date => sysdate,
x_created_by => l_created_by,
x_last_update_date => sysdate,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login
);
PROCEDURE update_histroy (
p_ItemGroups_id IN NUMBER,
p_action IN VARCHAR2
)IS
--
cursor get_item_assos_det_csr(c_itemgroup_id in number)
is
Select
item_association_id ,
object_version_number ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
item_group_id ,
inventory_item_id ,
inventory_org_id ,
priority ,
uom_code ,
quantity ,
revision ,
interchange_type_code ,
interchange_reason ,
item_type_code ,
source_item_association_id,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from ahl_item_associations_vl
where item_group_id = c_itemgroup_id;
Select NVl(max(VERSION_NUMBER),0)
into l_version_number
from ahl_item_groups_b_h
where ITEM_GROUP_ID = p_ItemGroups_id;
'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Inserting into History Tables');
Select ahl_item_associations_b_h_s.nextval
into l_item_associations_h_id from dual;
INSERT INTO ahl_item_groups_b_h
(item_group_h_id ,
item_group_id ,
object_version_number,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
name ,
type_code ,
status_code ,
version_number ,
transaction_date ,
action ,
source_item_group_id ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 )
SELECT
AHL_ITEM_GROUPS_B_H_S.NEXTVAL ,
item_group_id ,
object_version_number,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
name ,
type_code ,
status_code ,
l_version_number+1 ,
sysdate ,
p_action ,
source_item_group_id ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM ahl_item_groups_b
WHERE item_group_id = p_ItemGroups_id;
INSERT INTO ahl_item_groups_tl_h
(item_group_h_id ,
language ,
last_update_date ,
last_updated_by ,
source_lang ,
creation_date ,
created_by ,
description ,
last_update_login )
SELECT
AHL_ITEM_GROUPS_B_H_S.CURRVAL ,
language ,
last_update_date ,
last_updated_by ,
source_lang ,
creation_date ,
created_by ,
description ,
last_update_login
FROM ahl_item_groups_tl
WHERE item_group_id = p_ItemGroups_id;
'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Inserting Item Association into history table Start of Loop');
Select ahl_item_associations_b_h_s.nextval
into l_item_associations_h_id from dual;
AHL_ITEM_ASSOCIATIONS_H_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_ITEM_ASSOCIATION_H_ID => l_item_associations_h_id ,
X_ITEM_ASSOCIATION_ID => l_item_assos_det.ITEM_ASSOCIATION_ID ,
X_ITEM_GROUP_ID => l_item_assos_det.ITEM_GROUP_ID ,
X_OBJECT_VERSION_NUMBER => l_item_assos_det.OBJECT_VERSION_NUMBER ,
X_INVENTORY_ITEM_ID => l_item_assos_det.INVENTORY_ITEM_ID ,
X_INVENTORY_ORG_ID => l_item_assos_det.INVENTORY_ORG_ID ,
X_PRIORITY => l_item_assos_det.PRIORITY ,
X_TRANSACTION_DATE => sysdate,
X_ACTION => p_action,
X_SOURCE_ITEM_ASSOCIATION_ID => l_item_assos_det.SOURCE_ITEM_ASSOCIATION_ID,
X_VERSION_NUMBER => l_version_number ,
X_UOM_CODE => l_item_assos_det.UOM_CODE ,
X_QUANTITY => l_item_assos_det.QUANTITY ,
X_REVISION => l_item_assos_det.REVISION ,
X_INTERCHANGE_TYPE_CODE => l_item_assos_det.INTERCHANGE_TYPE_CODE ,
X_ATTRIBUTE_CATEGORY => l_item_assos_det.ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => l_item_assos_det.ATTRIBUTE1 ,
X_ATTRIBUTE2 => l_item_assos_det.ATTRIBUTE2 ,
X_ATTRIBUTE3 => l_item_assos_det.ATTRIBUTE3 ,
X_ATTRIBUTE4 => l_item_assos_det.ATTRIBUTE4 ,
X_ATTRIBUTE5 => l_item_assos_det.ATTRIBUTE5 ,
X_ATTRIBUTE6 => l_item_assos_det.ATTRIBUTE6 ,
X_ATTRIBUTE7 => l_item_assos_det.ATTRIBUTE7 ,
X_ATTRIBUTE8 => l_item_assos_det.ATTRIBUTE8 ,
X_ATTRIBUTE9 => l_item_assos_det.ATTRIBUTE9 ,
X_ATTRIBUTE10 => l_item_assos_det.ATTRIBUTE10 ,
X_ATTRIBUTE11 => l_item_assos_det.ATTRIBUTE11 ,
X_ATTRIBUTE12 => l_item_assos_det.ATTRIBUTE12 ,
X_ATTRIBUTE13 => l_item_assos_det.ATTRIBUTE13 ,
X_ATTRIBUTE14 => l_item_assos_det.ATTRIBUTE14 ,
X_ATTRIBUTE15 => l_item_assos_det.ATTRIBUTE15 ,
X_INTERCHANGE_REASON => l_item_assos_det.INTERCHANGE_REASON ,
X_CREATION_DATE => l_item_assos_det.CREATION_DATE ,
X_CREATED_BY => l_item_assos_det.CREATED_BY ,
X_LAST_UPDATE_DATE => l_item_assos_det.LAST_UPDATE_DATE ,
X_LAST_UPDATED_BY => l_item_assos_det.LAST_UPDATED_BY ,
X_LAST_UPDATE_LOGIN => l_item_assos_det.LAST_UPDATE_LOGIN );
END update_histroy;
Is Select
item_group_id,
Name,
Status_Code,
Type_Code,
Source_Item_group_id,
object_version_number,
Description,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from ahl_item_groups_vl
Where Item_Group_id = c_itemgroup_id;
Select
item_association_id ,
source_item_association_id ,
object_version_number ,
item_group_id ,
inventory_item_id ,
inventory_org_id ,
priority ,
uom_code ,
quantity ,
revision ,
interchange_type_code ,
interchange_reason ,
source_item_association_id,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from ahl_item_associations_vl
where item_group_id = c_itemgroup_id;
update ahl_item_groups_b
set status_code=l_status,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = to_number(fnd_global.user_id),
last_update_login = to_number(fnd_global.login_id)
where item_group_id=l_itemgroup_det.item_group_id
and object_version_number = l_itemgroup_det.object_version_number;
update_histroy (
p_ItemGroups_id => l_itemgroup_det.item_group_id,
p_action => l_action
);
update ahl_item_groups_b
set status_code = 'COMPLETE',
object_version_number = object_version_number + 1,
source_item_group_id = NULL,
last_update_date = sysdate,
last_updated_by = to_number(fnd_global.user_id),
last_update_login = to_number(fnd_global.login_id)
where item_group_id = l_itemgroup_det.item_group_id and
object_version_number = l_itemgroup_det.object_version_number;
update ahl_mc_relationships
set item_group_id = l_itemgroup_det.item_group_id,
temp_item_group_id = null
where item_group_id = l_itemgroup_det.source_item_group_id and
temp_item_group_id is not null and
trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);
update_histroy
(
p_itemgroups_id => l_itemgroup_det.item_group_id,
p_action => 'U'
);
ahl_item_groups_pkg.update_row(
x_item_group_id => l_itemgroup_det.Source_Item_group_id,
x_type_code => l_itemgroup_det.type_code,
x_status_code => 'COMPLETE',
x_source_item_group_id => null,
x_object_version_number => l_itemgroup_det.object_version_number + 1,
x_name => l_itemgroup_det.name,
x_attribute_category => l_itemgroup_det.attribute_category,
x_attribute1 => l_itemgroup_det.attribute1,
x_attribute2 => l_itemgroup_det.attribute2,
x_attribute3 => l_itemgroup_det.attribute3,
x_attribute4 => l_itemgroup_det.attribute4,
x_attribute5 => l_itemgroup_det.attribute5,
x_attribute6 => l_itemgroup_det.attribute6,
x_attribute7 => l_itemgroup_det.attribute7,
x_attribute8 => l_itemgroup_det.attribute8,
x_attribute9 => l_itemgroup_det.attribute9,
x_attribute10 => l_itemgroup_det.attribute10,
x_attribute11 => l_itemgroup_det.attribute11,
x_attribute12 => l_itemgroup_det.attribute12,
x_attribute13 => l_itemgroup_det.attribute13,
x_attribute14 => l_itemgroup_det.attribute14,
x_attribute15 => l_itemgroup_det.attribute15,
x_description => l_itemgroup_det.description,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);
Delete from ahl_item_associations_tl
where item_association_id in
( Select item_association_id
from ahl_item_associations_b
where item_group_id = l_itemgroup_det.Source_Item_group_id);
Delete from ahl_item_associations_b
where item_group_id = l_itemgroup_det.Source_Item_group_id;
update ahl_item_associations_b
set item_group_id = l_itemgroup_det.Source_Item_group_id,
object_version_number = object_version_number+1
Where item_group_id = l_itemgroup_det.item_group_id;
update ahl_mc_relationships
set ITEM_GROUP_ID = l_itemgroup_det.Source_Item_group_id,
object_version_number = object_version_number+1
Where item_group_id = l_itemgroup_det.item_group_id;
update_histroy (
p_ItemGroups_id => l_itemgroup_det.item_group_id,
p_action => l_action
);
Delete from ahl_item_groups_tl
where item_group_id = l_itemgroup_det.item_group_id;
Delete from ahl_item_groups_b
where item_group_id = l_itemgroup_det.item_group_id;
update ahl_item_groups_b
set status_code=l_status,
object_version_number = object_version_number+1
where item_group_id=l_itemgroup_det.item_group_id
and object_version_number = l_itemgroup_det.object_version_number;
SELECT object_version_number, source_item_group_id, status_code
FROM ahl_item_groups_b
WHERE item_group_id = p_item_group_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE relationship_id = p_relationship_id;
FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_DELETED');
FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOUPDATE');
UPDATE ahl_mc_relationships
SET temp_item_group_id = p_item_group_id
WHERE relationship_id = p_nodes_tbl(i).relationship_id;
UPDATE ahl_mc_relationships
SET temp_item_group_id = null
WHERE relationship_id = p_nodes_tbl(i).relationship_id;
SELECT source_item_group_id, status_code
FROM ahl_item_groups_b
WHERE item_group_id = p_item_group_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE temp_item_group_id is null and
item_group_id = p_parent_ig_id and
trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate);