EAM De-Activate Asset Number Parent Child Relationship using API in Oracle Apps
Hi Developers,
Kindly find the script that i have created for where it will remove the Maintainable Flag and Deactivate the Asset at the same time.
This API takes out the Parent Child Geneology and then Deactivate the Asset.
Hope this helps the Developers
Note: 1: You cannot Deactivate a Route Asset as this is intended functionality
2: You cannot Deactivate an Asset if there is Open Work Order or Work Requests for the Asset
create or replace procedure check_geneology (p_item_instance in varchar2)
is
lc_exception varchar2 (1000);
--Added for EAM geneology end date API
lc_child_serial varchar2 (200);
ln_child_inv_id number;
ln_child_org_id number;
ln_child_inst_number varchar2 (3000);
ln_child_inst_id number;
l_return_status varchar2 (1) := 'S';
l_msg_count number;
l_msg_data varchar2 (1000);
o_return_status varchar2 (32767);
o_msg_count number;
o_msg_data varchar2 (32767);
-- Cursor to fetch all the item instance for the parameter passed
cursor lcu_details is
select
cii.instance_id item_instance
,cii.serial_number
,cii.instance_number
,cii.inventory_item_id
,cii.last_vld_organization_id
from
csi_item_instances cii
where
1 = 1
and cii.instance_number =p_item_instance
and cii.active_end_date is null
and nvl (cii.maintainable_flag, 'N') = 'Y'
and trunc (sysdate) between trunc (nvl (cii.active_start_date, sysdate))
and trunc (nvl (cii.active_end_date, sysdate));
--Cursor that will find the EAM parent child for instance
cursor lcu_parent_child_eam is
select
mog.parent_object_id parent
,mog.object_id child
from
mtl_object_genealogy mog
,mtl_serial_numbers msn
,csi_item_instances csi
where
mog.parent_object_id = msn.gen_object_id
and msn.serial_number = csi.serial_number
and msn.inventory_item_id = csi.inventory_item_id
and csi.instance_id in
(select
cii.instance_id
from
csi_item_instances cii
where
1 = 1
and cii.instance_number =p_item_instance
and cii.active_end_date is null
and nvl (cii.maintainable_flag, 'N') = 'Y'
and trunc (sysdate) between trunc (nvl (cii.active_start_date, sysdate))
and trunc (nvl (cii.active_end_date, sysdate)))
and trunc (sysdate) between trunc (nvl (mog.start_date_active, sysdate))
and trunc (nvl (mog.end_date_active, sysdate))
union
select
mog.parent_object_id parent
,mog.object_id child
from
mtl_object_genealogy mog
,mtl_serial_numbers msn
,csi_item_instances csi
where
mog.object_id = msn.gen_object_id
and msn.serial_number = csi.serial_number
and msn.inventory_item_id = csi.inventory_item_id
and csi.instance_id in
(select
cii.instance_id
from
csi_item_instances cii
where
1 = 1
and cii.instance_number =p_item_instance
and cii.active_end_date is null
and nvl (cii.maintainable_flag, 'N') = 'Y'
and trunc (sysdate) between trunc (nvl (cii.active_start_date, sysdate))
and trunc (nvl (cii.active_end_date, sysdate)))
and trunc (sysdate) between trunc (nvl (mog.start_date_active, sysdate))
and trunc (nvl (mog.end_date_active, sysdate));
begin
fnd_file.put_line (fnd_file.log
,'Inside Procedure');
dbms_output.put_line ('inside Procedure');
for lr_details in lcu_details loop
dbms_output.put_line ('inside first loop');
dbms_output.put_line ('first begin instance_id:-' || lr_details.item_instance);
-- Pass the parent instance and cursor will fetch all the child
if l_return_status = 'S' then
begin -- end date EAM Genealogy for each child
for lr_parent_child_eam in lcu_parent_child_eam loop
dbms_output.put_line ('lr_parent_child_eam.l_return_status:-' ||
l_return_status || '-' || sqlerrm);
if (l_return_status = 'S') then
dbms_output.put_line ('lr_parent_child_eam.if condition');
begin
dbms_output.put_line ('first begin for parent child: ' ||
lr_parent_child_eam.child);
dbms_output.put_line (' EAM l_return_status: ' || l_return_status);
select
cii.serial_number
,cii.inventory_item_id
,cii.last_vld_organization_id
,cii.instance_number
,cii.instance_id
into
lc_child_serial
,ln_child_inv_id
,ln_child_org_id
,ln_child_inst_number
,ln_child_inst_id
from
mtl_serial_numbers msn1
,csi_item_instances cii
where
msn1.serial_number = cii.serial_number
and msn1.inventory_item_id = cii.inventory_item_id
and msn1.gen_object_id = lr_parent_child_eam.child;
inv_genealogy_pub.update_genealogy (p_api_version => 1.0
,p_init_msg_list =>
fnd_api.g_false
,p_commit => fnd_api.g_false
,p_validation_level =>
fnd_api.g_valid_level_full
,p_object_type => 2
,p_object_id =>
lr_parent_child_eam.child
,p_object_number =>
lc_child_serial
,p_inventory_item_id =>
ln_child_inv_id
,p_org_id => ln_child_org_id
,p_genealogy_origin => 3
,p_genealogy_type => 5
,p_end_date_active =>
sysdate - 0.01
,p_update_txn_id => null
,x_return_status =>
l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
commit;
--set maintain flag No and End date active No
exception
when others then
lc_exception := sqlerrm;
end;
apps.eam_assetnumber_pub.update_asset_number (p_api_version => 1.0
,x_return_status =>
o_return_status
,x_msg_count =>
o_msg_count
,x_msg_data => o_msg_data
,p_inventory_item_id =>
ln_child_inv_id
,p_serial_number =>
lc_child_serial
,p_instance_number =>
ln_child_inst_number
,p_instance_id =>
ln_child_inst_id -- MAINTENANCE_OBJECT_ID in the form
,p_current_organization_id =>
ln_child_org_id
,p_owning_department_id =>
null
,p_maintainable_flag =>
'N'
,p_active_end_date =>
trunc (sysdate));
dbms_output.put_line ('lc_child_serial: ' || lc_child_serial);
-- dbms_output.put_line ('ln_child_inv_id: ' || ln_child_inv_id);
-- dbms_output.put_line ('ln_child_inv_id: ' || ln_child_inv_id);
-- dbms_output.put_line ('ln_child_org_id: ' || ln_child_org_id);
dbms_output.put_line ('ln_child_inst_number: ' || ln_child_inst_number);
-- dbms_output.put_line ('ln_child_inst_id: ' || ln_child_inst_id);
--dbms_output.put_line ('lr_parent_child_eam.child' ||
-- lr_parent_child_eam.child);
fnd_file.put_line (fnd_file.log
,'EAM l_return_status' || l_return_status);
commit;
end if;
end loop;
end;
end if;
-- end if;
if l_return_status = 'S' or l_return_status = 'E' then
apps.eam_assetnumber_pub.update_asset_number (p_api_version => 1.0
,x_return_status => o_return_status
,x_msg_count => o_msg_count
,x_msg_data => o_msg_data
,p_inventory_item_id =>
lr_details.inventory_item_id
,p_serial_number =>
lr_details.serial_number
,p_instance_number =>
lr_details.instance_number
,p_instance_id =>
lr_details.item_instance -- MAINTENANCE_OBJECT_ID in the form
,p_current_organization_id =>
lr_details.last_vld_organization_id
,p_owning_department_id => null
,p_maintainable_flag => 'N'
,p_active_end_date => trunc (sysdate));
dbms_output.put_line ('lr_details.inventory_item_id: ' || lr_details.
inventory_item_id);
dbms_output.put_line ('lr_details.serial_number: ' || lr_details.serial_number);
dbms_output.put_line (' lr_details.instance_number: ' || lr_details.instance_number);
fnd_file.put_line (fnd_file.log
,'EAM l_return_status' || l_return_status || '-' || sqlerrm);
commit;
dbms_output.put_line (
' last if Update Maintainable flag and end date Successfully '
|| ln_child_inst_number);
commit;
fnd_file.put_line (fnd_file.log
,'Transactions Committed.');
fnd_file.put_line (fnd_file.log
,l_return_status);
else
rollback;
fnd_file.put_line (fnd_file.log
,'Transactions Rollbacked.');
fnd_file.put_line (fnd_file.log
,l_return_status);
end if;
end loop;
end ;
Kindly find the script that i have created for where it will remove the Maintainable Flag and Deactivate the Asset at the same time.
This API takes out the Parent Child Geneology and then Deactivate the Asset.
Hope this helps the Developers
Note: 1: You cannot Deactivate a Route Asset as this is intended functionality
2: You cannot Deactivate an Asset if there is Open Work Order or Work Requests for the Asset
create or replace procedure check_geneology (p_item_instance in varchar2)
is
lc_exception varchar2 (1000);
--Added for EAM geneology end date API
lc_child_serial varchar2 (200);
ln_child_inv_id number;
ln_child_org_id number;
ln_child_inst_number varchar2 (3000);
ln_child_inst_id number;
l_return_status varchar2 (1) := 'S';
l_msg_count number;
l_msg_data varchar2 (1000);
o_return_status varchar2 (32767);
o_msg_count number;
o_msg_data varchar2 (32767);
-- Cursor to fetch all the item instance for the parameter passed
cursor lcu_details is
select
cii.instance_id item_instance
,cii.serial_number
,cii.instance_number
,cii.inventory_item_id
,cii.last_vld_organization_id
from
csi_item_instances cii
where
1 = 1
and cii.instance_number =p_item_instance
and cii.active_end_date is null
and nvl (cii.maintainable_flag, 'N') = 'Y'
and trunc (sysdate) between trunc (nvl (cii.active_start_date, sysdate))
and trunc (nvl (cii.active_end_date, sysdate));
--Cursor that will find the EAM parent child for instance
cursor lcu_parent_child_eam is
select
mog.parent_object_id parent
,mog.object_id child
from
mtl_object_genealogy mog
,mtl_serial_numbers msn
,csi_item_instances csi
where
mog.parent_object_id = msn.gen_object_id
and msn.serial_number = csi.serial_number
and msn.inventory_item_id = csi.inventory_item_id
and csi.instance_id in
(select
cii.instance_id
from
csi_item_instances cii
where
1 = 1
and cii.instance_number =p_item_instance
and cii.active_end_date is null
and nvl (cii.maintainable_flag, 'N') = 'Y'
and trunc (sysdate) between trunc (nvl (cii.active_start_date, sysdate))
and trunc (nvl (cii.active_end_date, sysdate)))
and trunc (sysdate) between trunc (nvl (mog.start_date_active, sysdate))
and trunc (nvl (mog.end_date_active, sysdate))
union
select
mog.parent_object_id parent
,mog.object_id child
from
mtl_object_genealogy mog
,mtl_serial_numbers msn
,csi_item_instances csi
where
mog.object_id = msn.gen_object_id
and msn.serial_number = csi.serial_number
and msn.inventory_item_id = csi.inventory_item_id
and csi.instance_id in
(select
cii.instance_id
from
csi_item_instances cii
where
1 = 1
and cii.instance_number =p_item_instance
and cii.active_end_date is null
and nvl (cii.maintainable_flag, 'N') = 'Y'
and trunc (sysdate) between trunc (nvl (cii.active_start_date, sysdate))
and trunc (nvl (cii.active_end_date, sysdate)))
and trunc (sysdate) between trunc (nvl (mog.start_date_active, sysdate))
and trunc (nvl (mog.end_date_active, sysdate));
begin
fnd_file.put_line (fnd_file.log
,'Inside Procedure');
dbms_output.put_line ('inside Procedure');
for lr_details in lcu_details loop
dbms_output.put_line ('inside first loop');
dbms_output.put_line ('first begin instance_id:-' || lr_details.item_instance);
-- Pass the parent instance and cursor will fetch all the child
if l_return_status = 'S' then
begin -- end date EAM Genealogy for each child
for lr_parent_child_eam in lcu_parent_child_eam loop
dbms_output.put_line ('lr_parent_child_eam.l_return_status:-' ||
l_return_status || '-' || sqlerrm);
if (l_return_status = 'S') then
dbms_output.put_line ('lr_parent_child_eam.if condition');
begin
dbms_output.put_line ('first begin for parent child: ' ||
lr_parent_child_eam.child);
dbms_output.put_line (' EAM l_return_status: ' || l_return_status);
select
cii.serial_number
,cii.inventory_item_id
,cii.last_vld_organization_id
,cii.instance_number
,cii.instance_id
into
lc_child_serial
,ln_child_inv_id
,ln_child_org_id
,ln_child_inst_number
,ln_child_inst_id
from
mtl_serial_numbers msn1
,csi_item_instances cii
where
msn1.serial_number = cii.serial_number
and msn1.inventory_item_id = cii.inventory_item_id
and msn1.gen_object_id = lr_parent_child_eam.child;
inv_genealogy_pub.update_genealogy (p_api_version => 1.0
,p_init_msg_list =>
fnd_api.g_false
,p_commit => fnd_api.g_false
,p_validation_level =>
fnd_api.g_valid_level_full
,p_object_type => 2
,p_object_id =>
lr_parent_child_eam.child
,p_object_number =>
lc_child_serial
,p_inventory_item_id =>
ln_child_inv_id
,p_org_id => ln_child_org_id
,p_genealogy_origin => 3
,p_genealogy_type => 5
,p_end_date_active =>
sysdate - 0.01
,p_update_txn_id => null
,x_return_status =>
l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
commit;
--set maintain flag No and End date active No
exception
when others then
lc_exception := sqlerrm;
end;
apps.eam_assetnumber_pub.update_asset_number (p_api_version => 1.0
,x_return_status =>
o_return_status
,x_msg_count =>
o_msg_count
,x_msg_data => o_msg_data
,p_inventory_item_id =>
ln_child_inv_id
,p_serial_number =>
lc_child_serial
,p_instance_number =>
ln_child_inst_number
,p_instance_id =>
ln_child_inst_id -- MAINTENANCE_OBJECT_ID in the form
,p_current_organization_id =>
ln_child_org_id
,p_owning_department_id =>
null
,p_maintainable_flag =>
'N'
,p_active_end_date =>
trunc (sysdate));
dbms_output.put_line ('lc_child_serial: ' || lc_child_serial);
-- dbms_output.put_line ('ln_child_inv_id: ' || ln_child_inv_id);
-- dbms_output.put_line ('ln_child_inv_id: ' || ln_child_inv_id);
-- dbms_output.put_line ('ln_child_org_id: ' || ln_child_org_id);
dbms_output.put_line ('ln_child_inst_number: ' || ln_child_inst_number);
-- dbms_output.put_line ('ln_child_inst_id: ' || ln_child_inst_id);
--dbms_output.put_line ('lr_parent_child_eam.child' ||
-- lr_parent_child_eam.child);
fnd_file.put_line (fnd_file.log
,'EAM l_return_status' || l_return_status);
commit;
end if;
end loop;
end;
end if;
-- end if;
if l_return_status = 'S' or l_return_status = 'E' then
apps.eam_assetnumber_pub.update_asset_number (p_api_version => 1.0
,x_return_status => o_return_status
,x_msg_count => o_msg_count
,x_msg_data => o_msg_data
,p_inventory_item_id =>
lr_details.inventory_item_id
,p_serial_number =>
lr_details.serial_number
,p_instance_number =>
lr_details.instance_number
,p_instance_id =>
lr_details.item_instance -- MAINTENANCE_OBJECT_ID in the form
,p_current_organization_id =>
lr_details.last_vld_organization_id
,p_owning_department_id => null
,p_maintainable_flag => 'N'
,p_active_end_date => trunc (sysdate));
dbms_output.put_line ('lr_details.inventory_item_id: ' || lr_details.
inventory_item_id);
dbms_output.put_line ('lr_details.serial_number: ' || lr_details.serial_number);
dbms_output.put_line (' lr_details.instance_number: ' || lr_details.instance_number);
fnd_file.put_line (fnd_file.log
,'EAM l_return_status' || l_return_status || '-' || sqlerrm);
commit;
dbms_output.put_line (
' last if Update Maintainable flag and end date Successfully '
|| ln_child_inst_number);
commit;
fnd_file.put_line (fnd_file.log
,'Transactions Committed.');
fnd_file.put_line (fnd_file.log
,l_return_status);
else
rollback;
fnd_file.put_line (fnd_file.log
,'Transactions Rollbacked.');
fnd_file.put_line (fnd_file.log
,l_return_status);
end if;
end loop;
end ;
Comments
Post a Comment