EAM- SQLs

 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;

————————————————————————————————————————–
EAM – Find all work orders for asset number

SELECT
WE.WIP_ENTITY_NAME AS Work_Order_No
,(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.SEGMENT1 AS Activity_Name
,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
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-00233800’
ORDER BY WDJ.LAST_UPDATE_DATE DESC

————————————————————————————————————————

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;


————————————————————————————————————————
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;

————————————————————————————————————————

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 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%’
;

————————————————————————————————————————

I and M – collection plans by work order

select
en.wip_entity_name as WO_ID
,QAR.*
,en.*
FROM
QA.QA_RESULTS QAR
,wip.wip_entities en
where
qar.WORK_ORDER_ID = en.WIP_ENTITY_ID
–and qar.work_order_id =  41054
and en.wip_entity_name = ‘DW8709’
order by qar.LAST_UPDATE_DATE desc

————————————————————————————————————————

I and M – List All collections Plans By WO Status and Activity

select
en.wip_entity_name as WO_ID
,WO.*
,QAR.*
,en.*
FROM
QA.QA_RESULTS QAR
,wip.wip_entities en

,(
SELECT
–WE.WIP_ENTITY_NAME AS Work_Order_No
CIII.INSTANCE_NUMBER AS Item_Asset_Number
,CIII.INSTANCE_DESCRIPTION AS Item_Asset_Description
,(SELECT MSI.SEGMENT1 FROM INV.MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = CIII.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = CIII.LAST_VLD_ORGANIZATION_ID) AS Item_Asset_Group

,WE.WIP_ENTITY_NAME AS Work_Order_No
,(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.SEGMENT1 AS Activity_Name
,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
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 CIII.INSTANCE_NUMBER = ‘SITE-00220114’

AND (SELECT LV.Meaning FROM APPLSYS.FND_LOOKUP_VALUES LV WHERE LV.LOOKUP_TYPE = ‘WIP_JOB_STATUS’ AND LV.LOOKUP_CODE = WDJ.STATUS_TYPE) =
— ‘Unreleased’
‘Released’
AND ACT.SEGMENT1 = ‘SS_EHV_SITE_OVERALL INSP’ — ACTIVITY NAME

) WO
where
qar.WORK_ORDER_ID = en.WIP_ENTITY_ID
–and qar.work_order_id =  41054
and en.wip_entity_name = WO.Work_Order_No –‘DW8709’
order by en.wip_entity_name –,qar.LAST_UPDATE_DATE desc

————————————————————————————————————————

I and M – List WO by WO Status and Activity Name

SELECT
CIII.INSTANCE_NUMBER AS Item_Asset_Number
,CIII.INSTANCE_DESCRIPTION AS Item_Asset_Description
,(SELECT MSI.SEGMENT1 FROM INV.MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = CIII.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = CIII.LAST_VLD_ORGANIZATION_ID) AS Item_Asset_Group

,WE.WIP_ENTITY_NAME AS Work_Order_No
,(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.SEGMENT1 AS Activity_Name
,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
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 CIII.INSTANCE_NUMBER = ‘SITE-00220114’

AND (SELECT LV.Meaning FROM APPLSYS.FND_LOOKUP_VALUES LV WHERE LV.LOOKUP_TYPE = ‘WIP_JOB_STATUS’ AND LV.LOOKUP_CODE = WDJ.STATUS_TYPE) = ‘Unreleased’

AND ACT.SEGMENT1 = ‘SS_EHV_SITE_OVERALL INSP’ — ACTIVITY NAME

–ORDER BY WDJ.LAST_UPDATE_DATE DESC

————————————————————————————————————————

I and M – Query 1 – Released WO Collection Plan Details

select
QAR.COLLECTION_ID
,en.wip_entity_name as WO_ID
,QAR.PLAN_ID
,QAR.STATUS as collection_pland_status
,QAR.asset_number
,QAR.ASSET_GROUP_ID
,en.DESCRIPTION as Activity_description
–,WO.*
–,QAR.*
–,en.*
FROM
QA.QA_RESULTS QAR
,wip.wip_entities en

,(
SELECT
–WE.WIP_ENTITY_NAME AS Work_Order_No
CIII.INSTANCE_NUMBER AS Item_Asset_Number
,CIII.INSTANCE_DESCRIPTION AS Item_Asset_Description
,(SELECT MSI.SEGMENT1 FROM INV.MTL_SYSTEM_ITEMS_B MSI WHERE MSI.INVENTORY_ITEM_ID = CIII.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = CIII.LAST_VLD_ORGANIZATION_ID) AS Item_Asset_Group

,WE.WIP_ENTITY_NAME AS Work_Order_No
,(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.SEGMENT1 AS Activity_Name
,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
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 CIII.INSTANCE_NUMBER = ‘SITE-00220114’

and (select LV.MEANING from APPLSYS.FND_LOOKUP_VALUES LV where LV.LOOKUP_TYPE = ‘WIP_JOB_STATUS’
AND LV.LOOKUP_CODE = WDJ.STATUS_TYPE) =
— ‘Unreleased’
—

‘Released’
–AND ACT.SEGMENT1 = ‘SS_EHV_SITE_OVERALL INSP’ — ACTIVITY NAME

) WO
where
qar.WORK_ORDER_ID = en.WIP_ENTITY_ID
–and qar.work_order_id =  41054
and en.wip_entity_name = WO.Work_Order_No –‘DW8709’
order by en.wip_entity_name –,qar.LAST_UPDATE_DATE desc

————————————————————————————————————————

I and M – Query 2 – List All collections Plans and Elements for Templates

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

FROM

QA.QA_PLANS QAPL
,QA.QA_PLAN_CHARS QAPC
,QA.QA_CHARS QACH
WHERE
QAPL.PLAN_ID = QAPC.PLAN_ID
AND QAPC.CHAR_ID = QACH.CHAR_ID

AND QAPL.NAME = ‘SS_EHV_OVR INSP_ESQCR_CP’

–AND QAPL.NAME = ‘SS_HV_SITE_INSP_AC’

ORDER BY QAPL.NAME , QAPC.PROMPT_SEQUENCE