The best practice to handle fixed asset items and to eliminate the confusion of other types of items is to follow the below procedure:
- Define a FA item for each asset category you have, for example, let’s say we have two asset categories (Computers and Equipment), then we’ll define the following items:
Item Code: FA00001, Item Description: Computers
Item Code: FA00002, Item Description: Equipment
- Assign expense item for FA00001 with Asset Clearing Account attached to Computers Asset Category (Purchasing Tab)
- Assign Asset Category for FA00001 with Computers Asset Category (Purchasing Tab)
Now, let’s say we want to purchase a Dell laptop:
- Create PO and enter Item FA00001, You will notice that Description will be defaulted to “Computers”
- Change the description to “Dell laptop” (You have to enable the “Allow Description Update” in purchasing tab”)
- You will notice that PO charge account will be picked as the Asset Clearing Account
The rest is to create the invoice and match it to this PO and run create accounting, then run Mass Addition Create
By this you have distinguished FA items from other inventory and expense items
The following conditions must be met for the mass additions create process to import an invoice line distribution to Oracle Assets:
- The line is charged to an account set up as an Asset account
• The account is set up for an existing asset category as either the asset clearing account or the CIP clearing account
• The Track As Asset check box is checked. (It is automatically checked if the account is an Asset account)
• The invoice is approved
• The invoice line distribution is posted to Oracle General Ledger from Payables
• The general ledger date on the invoice line distribution is on or before the date you specify for the create program
• If you use the multiple organization feature, your Payables operating unit must be tied to the same ledger as the corporate book for which you want to create mass
additions.
If all the above conditions are met and the asset still not transferred to Oracle Assets, Please review the following documents and check whether it is related to the mentioned bugs:
Mass: AP Invoice Lines Failed To Be Picked Up By Mass Additions Create APMACR (Doc ID 869755.1)
R12. APMACR: Mass Additions Create Process Does Not Transfer Records From Payables (AP) To Fixed Assets (FA) (Doc ID 1078438.1)
SQL – To get background processes details
—SQL to find top5 active background processes
select sid, process, program
from v$session s join v$bgprocess using (paddr)
where s.status = ‘ACTIVE’
and rownum < 5;
select * from v$session s join v$bgprocess using (paddr)
where s.status = ‘ACTIVE’
and rownum < 5;
select *
from v$session ses
join v$sql sql on
sql.ADDRESS = ses.SQL_ADDRESS
where 1=1
and upper(ses.OSUSER) like ‘KAMAL.SHAH%’
;
SQL – EAM_Find_All_CP_name_for_Work_Order
select name from apps.QA_PLANS where plan_id in (select
QPCT1.Plan_id
–,QPCT1.transaction_number,
–QPCT1.collection_trigger_description,
–QPCT1.operator_meaning,
–QPCT1.low_value,
–QPCT1.High_value,
–QPCT2.low_value,
–QPCT2.High_value
from
apps.QA_PLAN_COLLECTION_TRIGGERS_V QPCT1,
apps.QA_PLAN_COLLECTION_TRIGGERS_V QPCT2
where 1=1
AND QPCT1.plan_id=QPCT2.plan_id
–and plan_id=23100;
and QPCT1.collection_trigger_description=’Asset Group’
and QPCT1.low_value in(select Asset_Group from
(SELECT
–we.WIP_ENTITY_NAME,
— we.WIP_ENTITY_ID,
— WDJ.Creation_date,
— WDJ.DESCRIPTION,
— WDJ.STATUS_TYPE,
— LU1.MEANING STATUS_TYPE_DISP ,
— WDJ.PRIMARY_ITEM_ID,
— WDJ.SCHEDULED_START_DATE,
— WDJ.SCHEDULED_COMPLETION_DATE,
— WDJ.DATE_RELEASED,
— WDJ.DATE_COMPLETED,
— WDJ.asset_number,
— (SELECT segment1
— FROM mtl_system_items_b
— WHERE inventory_item_id = wdj.primary_item_id
— AND ROWNUM =1
— ) Activity_Name,
— WDJ.asset_group_id,
(SELECT segment1
FROM apps.mtl_system_items_b
WHERE inventory_item_id = wdj.ASSET_GROUP_ID
AND ROWNUM =1
) Asset_Group
— ,WDJ.owning_department
FROM apps.WIP_DISCRETE_JOBS wdj,
apps.wip_entities we,
apps.MFG_LOOKUPS LU1
WHERE 1 =1
AND wdj.wip_entity_id =we.wip_entity_id
AND LU1.LOOKUP_TYPE = ‘WIP_JOB_STATUS’
AND LU1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND we.wip_entity_name=’DW10024′))
and QPCT2.collection_trigger_description=’Asset Activity’
and QPCT2.low_value in(select Activity_Name from
(SELECT
–we.WIP_ENTITY_NAME,
— we.WIP_ENTITY_ID,
— WDJ.Creation_date,
— WDJ.DESCRIPTION,
— WDJ.STATUS_TYPE,
— LU1.MEANING STATUS_TYPE_DISP ,
— WDJ.PRIMARY_ITEM_ID,
— WDJ.SCHEDULED_START_DATE,
— WDJ.SCHEDULED_COMPLETION_DATE,
— WDJ.DATE_RELEASED,
— WDJ.DATE_COMPLETED,
— WDJ.asset_number,
(SELECT segment1
FROM apps.mtl_system_items_b
WHERE inventory_item_id = wdj.primary_item_id
AND ROWNUM =1
) Activity_Name
— ,WDJ.asset_group_id,
— (SELECT segment1
— FROM mtl_system_items_b
— WHERE inventory_item_id = wdj.ASSET_GROUP_ID
— AND ROWNUM =1
— ) Asset_Group,
— WDJ.owning_department
FROM apps.WIP_DISCRETE_JOBS wdj,
apps.wip_entities we,
apps.MFG_LOOKUPS LU1
WHERE 1 =1
AND wdj.wip_entity_id =we.wip_entity_id
and LU1.LOOKUP_TYPE = ‘WIP_JOB_STATUS’
AND LU1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND we.wip_entity_name=’DW10024′)));
SQL – EAM_Find_All_CP_CE_CEV_for_WO
EAM_Find_All_CP_CE_CEV_for_WO
Find all collection plans, elements , values for a particular work order
select * from (SELECT WE.WIP_ENTITY_NAME AS Work_Order_No ,
WDJ.ASSET_NUMBER,
(SELECT segment1
FROM INV.mtl_system_items_b
WHERE inventory_item_id = wdj.ASSET_GROUP_ID
AND ROWNUM =1
) Asset_Group,
ACT.SEGMENT1 AS Activity_Name ,
QPCT1.Plan_id,
(select qp.name from QA.qa_plans qp where qp.plan_id=QPCT1.Plan_id)plan_name,
(SELECT LV.Meaning
FROM APPLSYS.FND_LOOKUP_VALUES LV
WHERE LV.LOOKUP_TYPE = ‘WIP_EAM_ACTIVITY_PRIORITY’
AND LV.LOOKUP_CODE = WDJ.PRIORITY
) AS WO_Priority ,
(SELECT LV.Meaning
FROM APPLSYS.FND_LOOKUP_VALUES LV
WHERE LV.LOOKUP_TYPE = ‘WIP_JOB_STATUS’
AND LV.LOOKUP_CODE = WDJ.STATUS_TYPE
) AS WO_Status ,
ACT.DESCRIPTION AS Activity_Description ,
TO_CHAR(WDJ.CREATION_DATE, ‘DD-Mon-YYYY’) AS Created_Date ,
TO_CHAR(WDJ.DATE_RELEASED, ‘DD-Mon-YYYY’) AS Released_Date ,
TO_CHAR(WDJ.SCHEDULED_START_DATE, ‘DD-Mon-YYYY’) AS Scheduled_Start_Date ,
TO_CHAR(WDJ.SCHEDULED_COMPLETION_DATE, ‘DD-Mon-YYYY’) AS Scheduled_Completion_Date ,
TO_CHAR(WDJ.DATE_COMPLETED, ‘DD-Mon-YYYY’) AS Completed_Date ,
(SELECT DP.DEPARTMENT_CODE AS Description
FROM APPS.BOM_DEPARTMENTS DP
WHERE DP.DEPARTMENT_ID = NVL(WDJ.OWNING_DEPARTMENT,
(SELECT MD.OWNING_DEPARTMENT_ID
FROM EAM.EAM_ORG_MAINT_DEFAULTS MD
WHERE MD.OBJECT_TYPE = ’60’
AND MD.ORGANIZATION_ID = CIII.LAST_VLD_ORGANIZATION_ID
AND MD.OBJECT_ID = CIII.INSTANCE_ID
))
) AS Assigned_Department ,
(SELECT DP.DESCRIPTION AS Description
FROM APPS.BOM_DEPARTMENTS DP
WHERE DP.DEPARTMENT_ID = NVL(WDJ.OWNING_DEPARTMENT,
(SELECT MD.OWNING_DEPARTMENT_ID
FROM EAM.EAM_ORG_MAINT_DEFAULTS MD
WHERE MD.OBJECT_TYPE = ’60’
AND MD.ORGANIZATION_ID = CIII.LAST_VLD_ORGANIZATION_ID
AND MD.OBJECT_ID = CIII.INSTANCE_ID
))
) AS Assigned_Department_Desc ,
(SELECT fuv.USER_NAME
FROM APPS.FND_USER_VIEW fuv
WHERE fuv.user_id = WDJ.LAST_UPDATED_BY
) AS Last_Update_by_User ,
TO_CHAR(WDJ.LAST_UPDATE_DATE, ‘DD-Mon-YYYY’) AS Last_Updated_Date
FROM WIP.WIP_DISCRETE_JOBS WDJ ,
WIP.WIP_ENTITIES WE ,
— APPS.EAM_WORK_ORDER_DETAILS WOD ,
CSI.CSI_ITEM_INSTANCES CIII ,
INV.MTL_SYSTEM_ITEMS_B ACT,
APPS.QA_PLAN_COLLECTION_TRIGGERS_V QPCT1,
APPS.QA_PLAN_COLLECTION_TRIGGERS_V QPCT2
WHERE WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE IN (6, 7)
–AND WDJ.ORGANIZATION_ID = WOD.ORGANIZATION_ID
–AND WDJ.WIP_ENTITY_ID = WOD.WIP_ENTITY_ID
–AND WDJ.STATUS_TYPE = WOD.USER_DEFINED_STATUS_ID
AND WDJ.ASSET_NUMBER = CIII.INSTANCE_NUMBER
AND WDJ.ORGANIZATION_ID = ACT.ORGANIZATION_ID (+)
AND WDJ.PRIMARY_ITEM_ID = ACT.INVENTORY_ITEM_ID (+)
–AND WDJ.ASSET_NUMBER = ‘SITE-00220114’–‘SITE-00272399′
AND WE.WIP_ENTITY_NAME=’26721′
AND QPCT1.plan_id=QPCT2.plan_id
and QPCT1.collection_trigger_description=’Asset Group’
and QPCT1.low_value=(SELECT segment1
FROM INV.mtl_system_items_b
WHERE inventory_item_id = wdj.ASSET_GROUP_ID
AND ROWNUM =1
)
and QPCT2.collection_trigger_description=’Asset Activity’
and QPCT2.low_value= ACT.SEGMENT1
ORDER BY WDJ.LAST_UPDATE_DATE DESC)x1,
(SELECT QAPL.PLAN_ID AS Plan_ID ,
QAPL.NAME AS Plan_Name ,
QAPL.VIEW_NAME AS Plan_View_Name ,
QAPL.DESCRIPTION AS Plan_Description ,
QAPC.PROMPT_SEQUENCE AS Question_Sort_Order ,
QAPC.PROMPT AS Question_Prompt ,
QACH.DATA_ENTRY_HINT AS Question_Hint ,
QAPC.MANDATORY_FLAG AS Mandatory_Flag ,
QAPC.ENABLED_FLAG AS Enabled_Flag ,
QAPC.READ_ONLY_FLAG AS Read_Only ,
QAPC.DISPLAYED_FLAG AS Diplayed ,
QAPC.INFORMATION_FLAG AS Information_Flag ,
QAPC.DEVICE_FLAG AS Device_Flag ,
QAPC.OVERRIDE_FLAG AS Override_Flag ,
QAPC.UOM_CODE AS UOM ,
QAPC.DEFAULT_VALUE AS Default_Value ,
(SELECT LV.Meaning
FROM APPLSYS.FND_LOOKUP_VALUES LV
WHERE LV.LOOKUP_TYPE = ‘ELEMENT_TYPE’
AND LV.LOOKUP_CODE = QACH.CHAR_TYPE_CODE
) AS Element_Type ,
QAPC.ATTRIBUTE_CATEGORY ,
QAPC.ATTRIBUTE1 ,
QAPC.ATTRIBUTE2 ,
QAPC.ATTRIBUTE3 ,
QAPC.ATTRIBUTE4,
QACH.Name,
qpcvl.short_code,
qpcvl.ATTRIBUTE_CATEGORY ATTR_CAT_VAL,
qpcvl.ATTRIBUTE6,
qpcvl.ATTRIBUTE7,
qpcvl.ATTRIBUTE8,
qpcvl.ATTRIBUTE10,
qpcvl.ATTRIBUTE11
FROM QA.QA_PLANS QAPL ,
QA.QA_PLAN_CHARS QAPC ,
QA.QA_CHARS QACH,
QA.QA_PLAN_CHAR_VALUE_LOOKUPS qpcvl
WHERE QAPL.PLAN_ID = QAPC.PLAN_ID
AND QAPC.CHAR_ID = QACH.CHAR_ID
and qapc.char_id=qpcvl.char_id(+)
and qapc.plan_id=qpcvl.plan_id(+)
ORDER BY QAPL.NAME ,
QAPC.PROMPT_SEQUENCE)x2
where X1.PLAN_ID=X2.PLAN_ID;
SQL – EAM_Find_All_Assets_Per_Asset_Group
–Find Asset Group’s Inventory_item_id
select * from APPS.MTL_SYSTEM_ITEMS_B
where ORGANIZATION_ID = 106
and ITEM_TYPE =’AG’
ORDER BY DESCRIPTION;
— Run Query to find all assets for particular asset group based on Inventory_item_id
select * from APPS.MTL_EAM_ASSET_NUMBERS_ALL_V where
INVENTORY_ITEM_ID =184563
–AND SERIAL_NUMBER =’RPSS-00001557′
order by SERIAL_NUMBER desc;
Select * from APPS.MTL_EAM_ASSET_NUMBERS_ALL_V where
INVENTORY_ITEM_ID =184600
–AND SERIAL_NUMBER =’RPSS-00001557′
order by SERIAL_NUMBER desc;
SQL – EAM_Find_All_Asset_Groups
dESC APPS.MTL_SYSTEM_ITEMS_VL
select * from APPS.MTL_SYSTEM_ITEMS_B
where ORGANIZATION_ID = 106
and ITEM_TYPE =’AG’
ORDER BY DESCRIPTION;
desc APPS.MTL_EAM_ASSET_NUMBERS_ALL_V
select * from APPS.MTL_EAM_ASSET_NUMBERS_ALL_V where
INVENTORY_ITEM_ID =184563
–AND SERIAL_NUMBER =’RPSS-00001557′
order by SERIAL_NUMBER desc;
Select * from APPS.MTL_EAM_ASSET_NUMBERS_ALL_V where
INVENTORY_ITEM_ID =184600
–AND SERIAL_NUMBER =’RPSS-00001557′
order by SERIAL_NUMBER desc;
SQL – Asset Number View
DESC MTL_EAM_ASSET_NUMBERS_ALL_V
select * from MTL_EAM_ASSET_NUMBERS_ALL_V
WHERE INSTANCE_NUMBER = ‘SITE-00220114’;
select * from MTL_EAM_ASSET_NUMBERS_ALL_V
WHERE INSTANCE_NUMBER = ‘RE-0001’;
select * from MTL_EAM_ASSET_NUMBERS_ALL_V
where EAM_ITEM_TYPE is not null;
SQL – Asset Hierarchy with associated activities
SELECT
Level AS Asset_Depth
,CIII.INSTANCE_ID AS Item_Instance_ID
,MSNI.SERIAL_NUMBER AS Item_Serial_No
,CIII.INSTANCE_NUMBER AS Item_Asset_Number
,NVL(CIII.INSTANCE_DESCRIPTION, MSNI.DESCRIPTIVE_TEXT) AS Item_Asset_Description
,MSNI.INVENTORY_ITEM_ID AS Item_Asset_Group_ID
,(SELECT min(MSI.DESCRIPTION) FROM INV.MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = MSNI.INVENTORY_ITEM_ID) AS Item_Asset_Group_Description
,CIII.NETWORK_ASSET_FLAG AS Network_Asset_Flag
,CIII.MAINTAINABLE_FLAG AS Maintainable_Flag
,Decode((SELECT Count(QAR.COLLECTION_ID) FROM QA.QA_RESULTS QAR, QA.QA_PLANS QAP WHERE QAR.PLAN_ID = QAP.PLAN_ID AND QAR.ASSET_NUMBER = MSNI.SERIAL_NUMBER),0,’N’,’Y’) AS Collection_Data
,CIIP.INSTANCE_ID AS Parent_Instance_ID
,MSNP.SERIAL_NUMBER AS Parent_Serial_No
,CIIP.INSTANCE_NUMBER AS Parent_Asset_Number
,NVL(CIIP.INSTANCE_DESCRIPTION, MSNP.DESCRIPTIVE_TEXT) AS Parent_Asset_Description
,MSNP.INVENTORY_ITEM_ID AS Parent_Asset_Group_ID
,(SELECT min(MSI.DESCRIPTION) FROM INV.MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = MSNP.INVENTORY_ITEM_ID) AS Parent_Asset_Group_Description
,(SELECT fuv.USER_NAME FROM APPS.FND_USER_VIEW fuv WHERE fuv.user_id = CIII.CREATED_BY) AS Item_Created_by_User
,CIII.CREATION_DATE AS Item_Created_Date
,MEAI.ACTIVITY_ASSOCIATION_ID
,MEAI.ASSET_ACTIVITY_ID
,MEASI.SEGMENT1 AS Assigned_Activity
FROM
INV.MTL_SERIAL_NUMBERS MSNI
,CSI.CSI_ITEM_INSTANCES CIII
,INV.MTL_OBJECT_GENEALOGY MOG
,INV.MTL_SERIAL_NUMBERS MSNP
,CSI.CSI_ITEM_INSTANCES CIIP
,INV.MTL_EAM_ASSET_ACTIVITIES MEAI
,INV.MTL_SYSTEM_ITEMS_B MEASI
WHERE
MSNI.SERIAL_NUMBER = CIII.SERIAL_NUMBER
AND MSNI.GEN_OBJECT_ID = MOG.OBJECT_ID (+)
AND MOG.PARENT_OBJECT_ID = MSNP.GEN_OBJECT_ID (+)
AND MSNP.SERIAL_NUMBER = CIIP.SERIAL_NUMBER (+)
AND CIII.INSTANCE_ID = MEAI.MAINTENANCE_OBJECT_ID (+)
AND MEAI.ASSET_ACTIVITY_ID = MEASI.INVENTORY_ITEM_ID (+)
START WITH
MOG.PARENT_OBJECT_ID = (SELECT GEN_OBJECT_ID FROM INV.MTL_SERIAL_NUMBERS WHERE SERIAL_NUMBER = ‘XSIT-00001095’)
AND NVL(MOG.START_DATE_ACTIVE,SYSDATE – 1) < SYSDATE
AND NVL(MOG.END_DATE_ACTIVE,SYSDATE + 1) > SYSDATE
CONNECT BY
MOG.PARENT_OBJECT_ID = PRIOR MOG.OBJECT_ID
AND NVL(MOG.START_DATE_ACTIVE,SYSDATE – 1) < SYSDATE
AND NVL(MOG.END_DATE_ACTIVE,SYSDATE + 1) > SYSDATE
SQL Query to find work orders based on PM Set
alter session set nls_language=’AMERICAN’;
commit;
SELECT count (*) FROM apps.EAM_WORK_ORDERS_V WHERE PM_SCHEDULE_ID IN
(select PM_SCHEDULE_ID from APPS.EAM_PM_SCHEDULINGS_V where SET_NAME=’GMSS_PMSET’);
No comments:
Post a Comment