Query to get Formula wise All Resource in OPM
SELECT gor.resources, crm.resource_desc, crd.nominal_cost,
NVL (gor.resource_usage, 1) resource_usage,
NVL (gor.process_qty, 1) process_qty
FROM fm_rout_dtl frd,gmd_operation_activities goa,
gmd_operation_resources gor,
cr_rsrc_mst crm,
cm_rsrc_dtl crd
WHERE frd.oprn_id = goa.oprn_id
AND routing_id IN (
SELECT gr.routing_id
FROM mtl_system_items_kfv ic,
fm_matl_dtl dtl,
gmd_recipes gr
WHERE dtl.formula_id = x_formula_id
AND line_no = 1
AND line_type = 1
AND ic.organization_id = p_inv_org_id
AND ic.inventory_item_id = dtl.inventory_item_id
AND gr.formula_id = dtl.formula_id
AND ic.item_type not like '%TEA%' --added 28nov14 TEA Routing Resource will not appeared
)
AND gor.oprn_line_id = goa.oprn_line_id
AND crm.resources = gor.resources
AND crm.resources = crd.resources
AND crd.cost_type_id = p_cost_type_id
AND period_id = p_period_id
ORDER BY 1;
=========================================================================
#Oracle Apps#EBS #OPM #Process manufacturing #Formula #Resources
