Below is the query to find the resources and its associated alternate resource in manufacturing module.
SELECT A.RESOURCES,ALTERNATE_RESOURCE.ALTERNATE_RESOURCE,A.RESOURCE_DESC,A.RESOURCE_CLASS,
B.MIN_CAPACITY,B.MAX_CAPACITY,
B.IDEAL_CAPACITY,B.CAPACITY_UM,B.GROUP_RESOURCE,MP.ORGANIZATION_CODE
FROM
CR_RSRC_MST_B A,CR_RSRC_DTL B,MTL_PARAMETERS MP,APPS.CR_ARES_MST ALTERNATE_RESOURCE
WHERE A.RESOURCES=B.RESOURCES AND B.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND A.RESOURCES=ALTERNATE_RESOURCE.PRIMARY_RESOURCE
ORDER BY 1 DESC;
SELECT A.RESOURCES,ALTERNATE_RESOURCE.ALTERNATE_RESOURCE,A.RESOURCE_DESC,A.RESOURCE_CLASS,
B.MIN_CAPACITY,B.MAX_CAPACITY,
B.IDEAL_CAPACITY,B.CAPACITY_UM,B.GROUP_RESOURCE,MP.ORGANIZATION_CODE
FROM
CR_RSRC_MST_B A,CR_RSRC_DTL B,MTL_PARAMETERS MP,APPS.CR_ARES_MST ALTERNATE_RESOURCE
WHERE A.RESOURCES=B.RESOURCES AND B.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND A.RESOURCES=ALTERNATE_RESOURCE.PRIMARY_RESOURCE
ORDER BY 1 DESC;