The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Line_Item (p_item_composition_id IN NUMBER,
p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
);
PROCEDURE Delete_Line_Item (p_item_composition_id IN NUMBER,
p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
);
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 uom_code
from mtl_units_of_measure_vl
where uom_code = p_uom_code;
SELECT 'x'
FROM ahl_item_groups_vl
WHERE item_group_id = p_item_group_id
AND name = p_item_group_name
AND type_code = 'NON-TRACKED'
AND status_code = 'COMPLETE';
SELECT 'x'
FROM AHL_ITEM_COMP_DETAILS
WHERE ITEM_GROUP_ID = p_item_group_id
AND ITEM_COMPOSITION_ID= p_item_composition_id
AND EFFECTIVE_END_DATE is null;
SELECT 'x'
FROM AHL_ITEM_COMP_DETAILS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND INVENTORY_MASTER_ORG_ID = p_inventory_master_org_id
AND ITEM_COMPOSITION_ID= p_item_composition_id
AND EFFECTIVE_END_DATE is null;
SELECT 'x'
FROM AHL_ITEM_COMP_DETAILS
WHERE item_comp_detail_id = p_item_comp_detail_id
AND item_composition_id= p_item_composition_id;
SELECT 'x'
FROM AHL_ITEM_COMPOSITIONS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND INVENTORY_MASTER_ORG_ID = p_master_org_id;
Select AHL_ITEM_COMPOSITIONS_S.NEXTVAL
INTO l_item_composition_id
FROM DUAL;
INSERT INTO AHL_ITEM_COMPOSITIONS(
ITEM_COMPOSITION_ID ,
INVENTORY_ITEM_ID ,
INVENTORY_MASTER_ORG_ID,
DRAFT_FLAG ,
APPROVAL_STATUS_CODE ,
EFFECTIVE_END_DATE ,
LINK_COMP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
SECURITY_GROUP_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
VALUES
(
l_item_composition_id,
p_x_ic_header_rec.INVENTORY_ITEM_ID ,
p_x_ic_header_rec.INVENTORY_MASTER_ORG_ID,
'N',
'DRAFT',
NULL,
NULL,
sysdate,
l_user_id,
sysdate,
l_user_id ,
to_number(fnd_global.LOGIN_ID) ,
1 ,
NULL,
p_x_ic_header_rec.ATTRIBUTE_CATEGORY,
p_x_ic_header_rec.ATTRIBUTE1,
p_x_ic_header_rec.ATTRIBUTE2,
p_x_ic_header_rec.ATTRIBUTE3,
p_x_ic_header_rec.ATTRIBUTE4,
p_x_ic_header_rec.ATTRIBUTE5,
p_x_ic_header_rec.ATTRIBUTE6,
p_x_ic_header_rec.ATTRIBUTE7,
p_x_ic_header_rec.ATTRIBUTE8,
p_x_ic_header_rec.ATTRIBUTE9,
p_x_ic_header_rec.ATTRIBUTE10,
p_x_ic_header_rec.ATTRIBUTE11,
p_x_ic_header_rec.ATTRIBUTE12,
p_x_ic_header_rec.ATTRIBUTE13,
p_x_ic_header_rec.ATTRIBUTE14,
p_x_ic_header_rec.ATTRIBUTE15);
SELECT
item_composition_id ,
inventory_item_id ,
inventory_master_org_id,
draft_flag ,
approval_status_code ,
effective_end_date ,
link_comp_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
object_version_number ,
security_group_id ,
sourced_from_enigma_flag,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM AHL_ITEM_COMPOSITIONS
WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
UPDATE AHL_ITEM_COMPOSITIONS
SET
APPROVAL_STATUS_CODE = l_item_composition_rec.approval_status_code ,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID) ,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
SECURITY_GROUP_ID =NULL,
ATTRIBUTE_CATEGORY = p_x_ic_header_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = p_x_ic_header_rec.ATTRIBUTE1,
ATTRIBUTE2 = p_x_ic_header_rec.ATTRIBUTE2,
ATTRIBUTE3 = p_x_ic_header_rec.ATTRIBUTE3,
ATTRIBUTE4 = p_x_ic_header_rec.ATTRIBUTE4,
ATTRIBUTE5 = p_x_ic_header_rec.ATTRIBUTE5,
ATTRIBUTE6 = p_x_ic_header_rec.ATTRIBUTE6,
ATTRIBUTE7 = p_x_ic_header_rec.ATTRIBUTE7,
ATTRIBUTE8 = p_x_ic_header_rec.ATTRIBUTE8,
ATTRIBUTE9 = p_x_ic_header_rec.ATTRIBUTE9,
ATTRIBUTE10 = p_x_ic_header_rec.ATTRIBUTE10,
ATTRIBUTE11 = p_x_ic_header_rec.ATTRIBUTE11,
ATTRIBUTE12 = p_x_ic_header_rec.ATTRIBUTE12,
ATTRIBUTE13 = p_x_ic_header_rec.ATTRIBUTE13,
ATTRIBUTE14 = p_x_ic_header_rec.ATTRIBUTE14,
ATTRIBUTE15 = p_x_ic_header_rec.ATTRIBUTE15
WHERE ITEM_COMPOSITION_ID = p_x_ic_header_rec.ITEM_COMPOSITION_ID
AND OBJECT_VERSION_NUMBER = p_x_ic_header_rec.object_version_number;
Update_Line_Item (p_item_composition_id => p_x_ic_header_rec.ITEM_COMPOSITION_ID,
p_x_comp_det_rec => p_x_det_tbl(I)
);
Delete_Line_Item (p_item_composition_id => p_x_ic_header_rec.ITEM_COMPOSITION_ID,
p_x_comp_det_rec => p_x_det_tbl(I)
);
PROCEDURE Delete_Item_Composition (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_item_composition_ID IN NUMBER ,
p_object_version_number IN NUMBER
)
AS
CURSOR item_composition_det(p_item_composition_id IN NUMBER)
IS
SELECT
item_composition_id ,
inventory_item_id ,
inventory_master_org_id,
draft_flag ,
approval_status_code ,
effective_end_date ,
link_comp_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
object_version_number ,
security_group_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM AHL_ITEM_COMPOSITIONS
WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
SELECT 'x'
FROM ahl_route_effectivities re, AHL_ITEM_COMP_V icd
WHERE icd.ITEM_COMPOSITION_ID = p_item_composition_id
AND re.inventory_item_id = ICD.inventory_item_id
AND re.INVENTORY_MASTER_ORG_ID = ICD.INVENTORY_MASTER_ORG_ID
AND ICD.APPROVAL_STATUS_CODE = 'COMPLETE';
SELECT item_composition_id
FROM AHL_ITEM_COMPOSITIONS
WHERE link_comp_id = p_item_composition_id;
SAVEPOINT Delete_Item_Composition;
'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Begin of Delete_Item_Composition');
FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_STATUS_NO_DELETE');
FND_MESSAGE.Set_Name('AHL','AHL_MC_COMP_STATUS_NO_DELETE');
UPDATE AHL_ITEM_COMPOSITIONS
SET
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID) ,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
SECURITY_GROUP_ID =NULL,
EFFECTIVE_END_DATE = sysdate -1
WHERE ITEM_COMPOSITION_ID = p_item_composition_ID
AND OBJECT_VERSION_NUMBER = p_object_version_number;
DELETE FROM AHL_ITEM_COMP_DETAILS
WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
DELETE FROM AHL_ITEM_COMPOSITIONS
WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'End of Delete_Item_Composition');
Rollback to Delete_Item_Composition;
'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Error in Delete_Item_Composition');
Rollback to Delete_Item_Composition;
'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Unexpected Error in Delete_Item_Composition');
Rollback to Delete_Item_Composition;
p_procedure_name => 'Delete_Item_Composition',
p_error_text => SUBSTR(SQLERRM,1,240));
'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', 'Unknown Error in Delete_Item_Composition');
'ahl.plsql.'||G_PKG_NAME||'.Delete_Item_Composition', SQLERRM);
END Delete_Item_Composition;
SELECT
item_composition_id ,
inventory_item_id ,
inventory_master_org_id,
draft_flag ,
approval_status_code ,
effective_end_date ,
link_comp_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
object_version_number ,
security_group_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM AHL_ITEM_COMPOSITIONS
WHERE ITEM_COMPOSITION_ID = p_item_composition_id;
SELECT 'x'
FROM AHL_ITEM_COMPOSITIONS
WHERE inventory_item_id = p_inv_item_id
AND inventory_master_org_id = p_inv_master_org_id
AND TRUNC(NVL(EFFECTIVE_END_DATE,sysdate)) >= TRUNC(sysdate);
UPDATE AHL_ITEM_COMPOSITIONS
SET
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID) ,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
SECURITY_GROUP_ID =NULL,
EFFECTIVE_END_DATE = null
WHERE ITEM_COMPOSITION_ID = p_item_composition_ID
AND OBJECT_VERSION_NUMBER = p_object_version_number;
SELECT AHL_ITEM_COMP_DETAILS_S.NEXTVAL
INTO l_item_comp_detail_id
FROM DUAL;
INSERT INTO AHL_ITEM_COMP_DETAILS(
ITEM_COMP_DETAIL_ID ,
ITEM_COMPOSITION_ID ,
ITEM_GROUP_ID ,
INVENTORY_ITEM_ID ,
INVENTORY_MASTER_ORG_ID,
UOM_CODE ,
QUANTITY ,
EFFECTIVE_END_DATE ,
LINK_COMP_DETL_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
SECURITY_GROUP_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
VALUES
(
l_item_comp_detail_id ,
p_item_composition_id ,
p_x_comp_det_rec.ITEM_GROUP_ID ,
p_x_comp_det_rec.INVENTORY_ITEM_ID ,
p_x_comp_det_rec.INVENTORY_MASTER_ORG_ID,
p_x_comp_det_rec.UOM_CODE ,
p_x_comp_det_rec.QUANTITY ,
NULL ,
NULL,
sysdate,
l_user_id ,
sysdate,
l_user_id,
to_number(fnd_global.LOGIN_ID),
1,
NULL,
p_x_comp_det_rec.ATTRIBUTE_CATEGORY ,
p_x_comp_det_rec.ATTRIBUTE1 ,
p_x_comp_det_rec.ATTRIBUTE2 ,
p_x_comp_det_rec.ATTRIBUTE3 ,
p_x_comp_det_rec.ATTRIBUTE4 ,
p_x_comp_det_rec.ATTRIBUTE5 ,
p_x_comp_det_rec.ATTRIBUTE6 ,
p_x_comp_det_rec.ATTRIBUTE7 ,
p_x_comp_det_rec.ATTRIBUTE8 ,
p_x_comp_det_rec.ATTRIBUTE9 ,
p_x_comp_det_rec.ATTRIBUTE10 ,
p_x_comp_det_rec.ATTRIBUTE11 ,
p_x_comp_det_rec.ATTRIBUTE12 ,
p_x_comp_det_rec.ATTRIBUTE13 ,
p_x_comp_det_rec.ATTRIBUTE14 ,
p_x_comp_det_rec.ATTRIBUTE15
) RETURNING
ITEM_COMP_DETAIL_ID ,
ITEM_COMPOSITION_ID
INTO
p_x_comp_det_rec.ITEM_COMP_DETAIL_ID,
p_x_comp_det_rec.ITEM_COMPOSITION_ID
;
PROCEDURE Update_Line_Item (p_item_composition_id IN NUMBER,
p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
)
AS
BEGIN
UPDATE AHL_ITEM_COMP_DETAILS
SET
ITEM_GROUP_ID =p_x_comp_det_rec.ITEM_GROUP_ID,
INVENTORY_ITEM_ID =p_x_comp_det_rec.INVENTORY_ITEM_ID,
INVENTORY_MASTER_ORG_ID = p_x_comp_det_rec.INVENTORY_MASTER_ORG_ID,
UOM_CODE =p_x_comp_det_rec.UOM_CODE ,
QUANTITY =p_x_comp_det_rec.QUANTITY,
LAST_UPDATE_DATE =sysdate,
LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
LAST_UPDATE_LOGIN = to_number(fnd_global.LOGIN_ID),
OBJECT_VERSION_NUMBER =object_version_number+1,
SECURITY_GROUP_ID =NULL,
ATTRIBUTE_CATEGORY =p_x_comp_det_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 =p_x_comp_det_rec.ATTRIBUTE1,
ATTRIBUTE2 =p_x_comp_det_rec.ATTRIBUTE2,
ATTRIBUTE3 =p_x_comp_det_rec.ATTRIBUTE3,
ATTRIBUTE4 =p_x_comp_det_rec.ATTRIBUTE4,
ATTRIBUTE5 =p_x_comp_det_rec.ATTRIBUTE5,
ATTRIBUTE6 =p_x_comp_det_rec.ATTRIBUTE6,
ATTRIBUTE7 =p_x_comp_det_rec.ATTRIBUTE7,
ATTRIBUTE8 =p_x_comp_det_rec.ATTRIBUTE8,
ATTRIBUTE9 =p_x_comp_det_rec.ATTRIBUTE9,
ATTRIBUTE10 =p_x_comp_det_rec.ATTRIBUTE10,
ATTRIBUTE11 =p_x_comp_det_rec.ATTRIBUTE11,
ATTRIBUTE12 =p_x_comp_det_rec.ATTRIBUTE12,
ATTRIBUTE13 =p_x_comp_det_rec.ATTRIBUTE13,
ATTRIBUTE14 =p_x_comp_det_rec.ATTRIBUTE14,
ATTRIBUTE15 =p_x_comp_det_rec.ATTRIBUTE15
WHERE
ITEM_COMP_DETAIL_ID = p_x_comp_det_rec.item_comp_detail_id
AND ITEM_COMPOSITION_ID = p_item_composition_id ;
END Update_Line_Item;
PROCEDURE Delete_Line_Item (p_item_composition_id IN NUMBER,
p_x_comp_det_rec IN OUT NOCOPY AHL_MC_ITEM_COMP_PVT.Detail_Rec_Type
)
AS
BEGIN
DELETE FROM AHL_ITEM_COMP_DETAILS
WHERE ITEM_COMP_DETAIL_ID = p_x_comp_det_rec.item_comp_detail_id
AND ITEM_COMPOSITION_ID = p_item_composition_id;
END Delete_Line_Item;
Select item_composition_id,
approval_status_code,
object_version_number,
concatenated_segments
From ahl_item_comp_v
Where item_composition_id = c_item_comp_id;
Select count(*)
into l_count
from ahl_item_comp_details
where item_composition_id = p_Item_Composition_id;
select count(*)
into l_count
from ahl_item_comp_details
where item_composition_id = p_Item_Composition_id;
Update ahl_item_compositions
Set APPROVAL_STATUS_CODE=l_upd_status,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
Where ITEM_COMPOSITION_ID = p_Item_Composition_id
and OBJECT_VERSION_NUMBER=p_object_Version_number;
Select
item_composition_id,
approval_status_code,
draft_flag,
inventory_item_id,
inventory_master_org_id,
effective_end_date,
object_version_number
from ahl_item_compositions
Where item_composition_id = c_item_comp_id;
Select 'x'
from ahl_item_compositions
where link_comp_id = c_item_comp_id;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
l_last_updated_by := to_number(fnd_global.USER_ID);
l_last_update_login := to_number(fnd_global.LOGIN_ID);
Select AHL_ITEM_COMPOSITIONS_S.NEXTVAL
INTO l_item_composition_id
FROM DUAL;
INSERT INTO AHL_ITEM_COMPOSITIONS(
ITEM_COMPOSITION_ID ,
INVENTORY_ITEM_ID ,
INVENTORY_MASTER_ORG_ID,
DRAFT_FLAG ,
APPROVAL_STATUS_CODE ,
EFFECTIVE_END_DATE ,
LINK_COMP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
SECURITY_GROUP_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
SELECT l_item_composition_id,
inventory_item_id ,
inventory_master_org_id,
draft_flag ,
'DRAFT' ,
effective_end_date ,
p_Item_comp_id ,
sysdate ,
l_last_updated_by ,
sysdate ,
l_created_by ,
l_last_update_login ,
1 ,
security_group_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM ahl_item_compositions
WHERE item_composition_id = p_Item_comp_id
AND object_version_number = p_object_version_number
AND effective_end_date is null;
INSERT INTO AHL_ITEM_COMP_DETAILS(
ITEM_COMP_DETAIL_ID ,
ITEM_COMPOSITION_ID ,
ITEM_GROUP_ID ,
INVENTORY_ITEM_ID ,
INVENTORY_MASTER_ORG_ID,
UOM_CODE ,
QUANTITY ,
EFFECTIVE_END_DATE ,
LINK_COMP_DETL_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
SECURITY_GROUP_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
SELECT AHL_ITEM_COMP_DETAILS_S.NEXTVAL ,
l_item_composition_id ,
item_group_id ,
inventory_item_id ,
inventory_master_org_id,
uom_code ,
quantity ,
effective_end_date ,
item_comp_detail_id ,
sysdate ,
l_last_updated_by ,
sysdate ,
l_created_by ,
l_last_update_login ,
1 ,
security_group_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM ahl_item_comp_details
WHERE item_composition_id = p_Item_comp_id
AND effective_end_date is null;
UPDATE ahl_item_compositions
SET DRAFT_FLAG = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = to_number(fnd_global.USER_ID),
LAST_UPDATE_LOGIN =to_number(fnd_global.LOGIN_ID),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
SECURITY_GROUP_ID =NULL
WHERE item_composition_id = p_Item_comp_id
AND object_version_number = p_object_version_number;
Is Select
item_composition_id,
approval_status_code,
draft_flag,
link_comp_id,
inventory_item_id,
inventory_master_org_id,
effective_end_date,
object_version_number ,
-- manisaga added the following code for DFF implemenataion on 19-Feb-2010 -- start
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
-- manisaga added the following code for DFF implemenataion on 19-Feb-2010 -- End
from ahl_item_compositions
Where item_composition_id = c_item_comp_id;
-- Insert record into histroy table.
IF l_item_comp_det.link_comp_id IS NULL THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
THEN
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_Item_Composiiton', 'link_comp_id is null');
update ahl_item_compositions
set approval_status_code=l_status,
object_version_number = object_version_number+1
where item_composition_id=l_item_comp_det.item_composition_id
and object_version_number = l_item_comp_det.object_version_number;
select
item_group_id ,
inventory_item_id ,
inventory_master_org_id,
uom_code ,
quantity ,
link_comp_detl_id ,
object_version_number ,
-- manisaga added these attributes for DFF implementation om 22-Feb-2010 -- Start
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
-- manisaga added these attributes for DFF implementation om 22-Feb-2010 -- End
bulk collect
into
l_item_group_id ,
l_inventory_item_id ,
l_inventory_master_org_id,
l_uom_code ,
l_quantity ,
l_link_comp_detl_id ,
l_object_version_number ,
-- manisaga added these attributes for DFF implementation om 22-Feb-2010 -- Start
l_attribute_category ,
l_attribute1 ,
l_attribute2 ,
l_attribute3 ,
l_attribute4 ,
l_attribute5 ,
l_attribute6 ,
l_attribute7 ,
l_attribute8 ,
l_attribute9 ,
l_attribute10 ,
l_attribute11 ,
l_attribute12 ,
l_attribute13 ,
l_attribute14 ,
l_attribute15
-- manisaga added these attributes for DFF implementation om 22-Feb-2010 -- End
from ahl_item_comp_details
where item_composition_id = l_item_comp_det.item_composition_id
and link_comp_detl_id is not null
and effective_end_date is null;
update ahl_item_comp_details set
uom_code =l_uom_code(I),
quantity =l_quantity(I),
last_update_date = sysdate,
last_updated_by = to_number(fnd_global.user_id),
last_update_login = to_number(fnd_global.login_id),
object_version_number =l_object_version_number(I)+1 ,
-- manisaga added attributes for DFF implementation on 22-Feb-2010 -- Start
attribute_category = l_attribute_category(I),
attribute1 = l_attribute1(I),
attribute2 = l_attribute2(I),
attribute3 = l_attribute3(I),
attribute4 = l_attribute4(I),
attribute5 = l_attribute5(I),
attribute6 = l_attribute6(I),
attribute7 = l_attribute7(I),
attribute8 = l_attribute8(I),
attribute9 = l_attribute9(I),
attribute10 = l_attribute10(I),
attribute11 = l_attribute11(I),
attribute12 = l_attribute12(I),
attribute13 = l_attribute13(I),
attribute14 = l_attribute14(I),
attribute15 = l_attribute15(I)
-- manisaga added attributes for DFF implementation on 22-Feb-2010 -- End
where ITEM_COMP_DETAIL_ID = l_link_comp_detl_id(I)
and item_composition_id = l_item_comp_det.LINK_COMP_ID
and NVL(item_group_id,-3) = NVL(l_item_group_id(I),-3)
and NVL(inventory_item_id,-3) = NVL(l_inventory_item_id(I),-3)
and NVL(inventory_master_org_id,-3)= NVL(l_inventory_master_org_id(I),-3)
and effective_end_date is null;
Update ahl_item_comp_details
Set effective_end_date = sysdate-1,
last_update_date =sysdate,
last_updated_by = to_number(fnd_global.user_id),
last_update_login = to_number(fnd_global.login_id),
object_version_number =object_version_number+1
Where item_composition_id=l_item_comp_det.link_comp_id
and effective_end_date is null
and ITEM_COMP_DETAIL_ID not in (
Select link_comp_detl_id
from ahl_item_comp_details
where item_composition_id = l_item_comp_det.item_composition_id
and link_comp_detl_id is not null);
Update ahl_item_comp_details
Set item_composition_id = l_item_comp_det.link_comp_id,
last_update_date =sysdate,
last_updated_by = to_number(fnd_global.user_id),
last_update_login = to_number(fnd_global.login_id),
object_version_number =object_version_number+1
Where item_composition_id=l_item_comp_det.item_composition_id
and link_comp_detl_id is null;
Update ahl_item_compositions
Set last_update_date =sysdate,
last_updated_by = to_number(fnd_global.user_id),
last_update_login = to_number(fnd_global.login_id),
object_version_number =object_version_number+1 ,
-- manisaga added attributes for DFF implementation on 22-Feb-2010 -- Start
attribute_category = l_item_comp_det.attribute_category,
attribute1 = l_item_comp_det.attribute1,
attribute2 = l_item_comp_det.attribute2,
attribute3 = l_item_comp_det.attribute3,
attribute4 = l_item_comp_det.attribute4,
attribute5 = l_item_comp_det.attribute5,
attribute6 = l_item_comp_det.attribute6,
attribute7 = l_item_comp_det.attribute7,
attribute8 = l_item_comp_det.attribute8,
attribute9 = l_item_comp_det.attribute9,
attribute10 = l_item_comp_det.attribute10,
attribute11 = l_item_comp_det.attribute11,
attribute12 = l_item_comp_det.attribute12,
attribute13 = l_item_comp_det.attribute13,
attribute14 = l_item_comp_det.attribute14,
attribute15 = l_item_comp_det.attribute15
-- manisaga added attributes for DFF implementation on 22-Feb-2010 -- End
Where item_composition_id=l_item_comp_det.link_comp_id;
Delete from ahl_item_comp_details
where item_composition_id = l_item_comp_det.item_composition_id;
Delete from ahl_item_compositions
Where item_composition_id=l_item_comp_det.item_composition_id;
update ahl_item_compositions
set approval_status_code=l_status,
object_version_number = object_version_number+1
where item_composition_id=l_item_comp_det.item_composition_id
and object_version_number = l_item_comp_det.object_version_number;