All Books for Oracle Cloud
https://docs.oracle.com/cloud/latest/allbooks.htm#Implementation
Tuesday 1 August 2017
Monday 31 July 2017
How do we specify an item as Inventory Asset item?
How do we specify an item as Inventory Asset item?
The best practice to handle fixed asset items and to eliminate the confusion of other types of items is to follow the below procedure:
1. 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
2. Assign expense item for FA00001 with Asset Clearing Account attached to Computers Asset Category (Purchasing Tab)
3. Assign Asset Category for FA00001 with Computers Asset Category (Purchasing Tab)
Now, let's say we want to purchase a Dell laptop:
1. Create PO and enter Item FA00001, You will notice that Description will be defaulted to "Computers"
2. Change the description to "Dell laptop" (You have to enable the "Allow Description Update" in purchasing tab")
3. 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.
• 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)
R12. APMACR: Mass Additions Create Process Does Not Transfer Records From Payables (AP) To Fixed Assets (FA) (Doc ID 1078438.1)
Thursday 27 July 2017
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%'
;
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
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
Friday 21 July 2017
Order Management - SQL Collection
- Find Order details that are only in ENTERED status
select * from APPS.OE_ORDER_HEADERS_ALL where
flow_status_code = 'ENTERED' and booked_flag = 'N;'
-- Find Order details that are booked
Select * from APPS.OE_ORDER_HEADERS_ALL where
FLOW_STATUS_CODE = 'BOOKED' and BOOKED_FLAG = 'Y'
order by order_number desc;
-- Find Order Line details through Order number
select * from APPS.OE_ORDER_LINES_ALL
where HEADER_ID in (select HEADER_ID from APPS.OE_ORDER_HEADERS_ALL where ORDER_NUMBER = '2283301');
--when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table are populated
Select * from apps.OE_ORDER_HEADERS_ALL;
select * from APPS.OE_ORDER_LINES_ALL;
select * from APPS. WSH_DELIVERY_DETAILS where source_header_id = '2283301';
select * from apps.wsh_delivery_assignments;
Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text
Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
select * from APPS.OE_ORDER_HEADERS_ALL where
flow_status_code = 'ENTERED' and booked_flag = 'N;'
-- Find Order details that are booked
Select * from APPS.OE_ORDER_HEADERS_ALL where
FLOW_STATUS_CODE = 'BOOKED' and BOOKED_FLAG = 'Y'
order by order_number desc;
-- Find Order Line details through Order number
select * from APPS.OE_ORDER_LINES_ALL
where HEADER_ID in (select HEADER_ID from APPS.OE_ORDER_HEADERS_ALL where ORDER_NUMBER = '2283301');
--when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table are populated
Select * from apps.OE_ORDER_HEADERS_ALL;
select * from APPS.OE_ORDER_LINES_ALL;
select * from APPS. WSH_DELIVERY_DETAILS where source_header_id = '2283301';
select * from apps.wsh_delivery_assignments;
-- Order Management Tables. Entered
oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release
oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned
mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated
Price Detailsqp_list_headers_b To Get Item Price Details.
qp_list_lines
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated
Price Detailsqp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.
mtl_onhand_quantities TO check On Hand Qty Items.
Payment Terms
ra_terms Payment terms
ra_terms Payment terms
AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details
csi_t_party_details To capture End user Details
Sales Credit Sales Credit Information(How much credit can get)oe_sales_credits
Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text
Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
Hold Relaese
oe_hold_releases_all Hold released Sales Order.
oe_hold_releases_all Hold released Sales Order.
Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Ordersoe_order_lines_all Cancel Order Details.
Subscribe to:
Posts (Atom)