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%'
;
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
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
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
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
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
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
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment