Today we’ll demonstrate how to update EAM asset number using API EAM_AssetNumber_PUB.Update_Asset_Number :
API to update EAM Asset Numbers.
Now First of all this The table below provides the specifications for this API:
| Parameter | Type | Req | Default | Description |
|---|---|---|---|---|
| p_api_version | NUMBER | Yes | – | Standard API parameter |
| p_init_msg_list | VARCHAR2 | – | FND_API.G_FALSE | Standard API parameter |
| p_commit | VARCHAR2 | – | FND_API.G_FALSE | Standard API parameter |
| p_validation_level | NUMBER | – | FND_API.G_VALID_LEVEL_FULL | Standard API parameter |
| x_return_status | VARCHAR2 | – | – | Standard API output parameter |
| x_msg_count | NUMBER | – | – | Standard API output parameter |
| x_msg_data | VARCHAR2 | – | – | Standard Oracle API output parameter |
| p_inventory_item_id | NUMBER | Yes | – | Inventory Item ID |
| p_serial_number | VARCHAR2 | Yes | – | Serial Number |
| p_current_status | NUMBER | – | 3 | Current Status 3 – activated 4 – deactivated |
| p_descriptive_text | VARCHAR2 | – | NULL | Description |
| p_current_organization_id | NUMBER | Yes | – | Current Organization ID |
| p_attribute_category | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute1 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute2 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute3 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute4 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute5 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute6 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute7 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute8 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute9 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute10 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute11 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute12 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute13 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute14 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_attribute15 | VARCHAR2 | – | – | Descriptive Flexfield |
| p_wip_accounting_class_code | VARCHAR2 | – | NULL | WIP Accounting Class Code |
| p_maintainable_flag | VARCHAR2 | – | NULL | Maintainable Flag: Y, N, or Null |
| p_owning_department_id | NUMBER | Yes | – | Owning Department ID |
| p_network_asset_flag | VARCHAR2 | – | NULL | Network Asset Flag: Y, N, or Null |
| p_fa_asset_id | NUMBER | – | NULL | Fixed Asset ID |
| p_pn_location_id | NUMBER | – | NULL | PN Location ID |
| p_eam_location_id | NUMBER | – | NULL | EAM Location ID |
| p_asset_criticality_code | VARCHAR2 | – | NULL | Asset Criticality Code |
| p_category_id | NUMBER | – | NULL | Category ID |
| p_prod_organization_id | NUMBER | – | NULL | Production Organization ID |
| p_equipment_item_id | NUMBER | – | NULL | Equipment Item ID |
| p_eqp_serial_number | VARCHAR2 | – | NULL | Equipment Serial Number |
And this the form we want to update :
Now this the API we are using to do this task : EAM_AssetNumber_PUB.Update_Asset_Number
And this the example to updating Asset Number Field in (Others) Tab in this form or anything else except Asset Serial Number this the only field wouldn’t updated by API as mentioned by ORACLE :
“The API EAM_AssetNumber_PUB.Update_Asset_Number cannot update the Asset Serial Number as seen in the Asset Number Definition form. This is the intended behavior of the API.” Reference : Doc ID 1362277.1
DECLARE
o_return_status VARCHAR2 (32767);
o_msg_count NUMBER;
o_msg_data VARCHAR2 (32767);
l_output_mesg VARCHAR2 (32767);
BEGIN
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 => 11017
,p_serial_number => 'Compressor'
,p_instance_number => 'Compressor'
,p_instance_id => 1768487 -- MAINTENANCE_OBJECT_ID in the form
,p_current_organization_id => 1645
,p_owning_department_id => 708
,p_fa_asset_id => 100075);
IF (o_return_status = 'S') THEN
--
COMMIT;
l_output_mesg := 'EAM Asset Updated Successfully with return status : ' || ' ' || o_return_status;
dbms_output.put_line (l_output_mesg);
--
ELSE
--
FOR i IN 1 .. (o_msg_count)
LOOP
l_output_mesg := substr (fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false), 1, 250);
dbms_output.put_line (l_mesg);
END LOOP;
--
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('API Faild with error code: ' || sqlcode || ' and error message is: ' || sqlerrm);
END;
This is the End
Hope this help.
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;