OPM Recipe, Routing, Formula, Operation, Activity and Resource Data Extraction Script

--Recipe, Routing, Formula, Operation, Activity and Resource Find in a Single Query:

SELECT   ood.organization_code, recipe_no, recipe_version, recipe_description,
         (SELECT meaning FROM gmd_status WHERE status_code = gr.recipe_status) recipe_status
         , grv.orgn_code validity_orgn_code
         ,(SELECT meaning FROM gmd_status WHERE status_code = grv.validity_rule_status) validity_rule_status
         ,ff.formula_no, ff.formula_vers,ff.formula_description, ff.product_qty,ff.formula_status
         ,rr.routing_no, rr.routing_vers,rr.routing_description, rr.routing_qty, routing_uom, rr.routing_status
         ,aa.routingstep_no,aa.step_qty, aa.oprn_no, aa.oprn_vers
         ,aa.operation_status
         ,aa.activity, aa.resources
    FROM gmd_recipes gr
         ,org_organization_definitions ood                   
         ,gmd_recipe_validity_rules grv
         ---------Routing----------
         ,(SELECT   frh.routing_id,organization_code, routing_no, routing_vers
                    ,routing_desc routing_description, frh.routing_qty, routing_uom
                    ,(SELECT meaning FROM gmd_status WHERE status_code = frh.routing_status) routing_status
                FROM fm_rout_hdr frh, org_organization_definitions ood
               WHERE frh.owner_organization_id = ood.organization_id
            GROUP BY organization_code,frh.routing_id,
                     routing_no,
                     routing_vers,
                     routing_desc,
                     routing_uom,
                     routing_qty,frh.routing_status
            ORDER BY organization_code, routing_no) RR
        ------------Formula---------------------------
        ,(SELECT  ffm.formula_id, organization_code, formula_no, formula_vers,
                 formula_desc1 formula_description, qty product_qty,
                 fmd.detail_uom uom, total_input_qty, total_output_qty, yield_uom
                 ,(SELECT meaning FROM gmd_status WHERE status_code = ffm.formula_status) FORMULA_STATUS
            FROM fm_form_mst ffm,
                 (SELECT *
                    FROM fm_matl_dtl
                   WHERE line_type = 1) fmd,
                 org_organization_definitions ood
           WHERE ffm.formula_id = fmd.formula_id
             AND ffm.owner_organization_id = ood.organization_id
        GROUP BY organization_code,
                 formula_no,
                 formula_desc1,
                 formula_vers,
                 yield_uom,
                 qty,
                 fmd.detail_uom,
                 total_input_qty,
                 total_output_qty,ffm.formula_id,ffm.formula_status
        ORDER BY organization_code, formula_no)FF
        ------------Operation and Activity and Resource ---------------------------
        ,(SELECT gov.oprn_id, gor.oprn_line_id, frd.routing_id, frd.routingstep_no,
               frd.step_qty, gov.oprn_no, gov.oprn_vers
               ,(SELECT meaning FROM gmd_status WHERE status_code =  gov.operation_status) operation_status
               ,goa.activity, gor.resources
          FROM gmd_operation_resources gor,
               gmd_operation_activities goa,
               gmd_operations_vl gov,
               fm_rout_dtl frd
         WHERE -1 = -1
           AND gov.oprn_id = frd.oprn_id
           AND gov.oprn_id = goa.oprn_id
           AND gor.oprn_line_id = goa.oprn_line_id
        Order BY   frd.routingstep_no) AA
        ----------- END Resource and Operation -----------------
   WHERE gr.owner_organization_id = ood.organization_id
     AND gr.recipe_id = grv.recipe_id
     AND gr.routing_id = rr.routing_id(+)
     AND gr.formula_id = ff.formula_id(+)
     AND rr.routing_id = aa.routing_id(+)
GROUP BY ood.organization_code,recipe_no,
         recipe_version,
         recipe_description,  gr.recipe_status
         ,grv.orgn_code, grv.validity_rule_status
         ,ff.formula_no, ff.formula_vers,ff.formula_description, ff.product_qty,ff.formula_status
         ,rr.routing_no, rr.routing_vers,rr.routing_description, rr.routing_qty, rr.routing_uom, rr.routing_status       
         ,aa.routingstep_no,aa.step_qty, aa.oprn_no, aa.oprn_vers
         ,aa.operation_status
         ,aa.activity, aa.resources
ORDER BY ood.organization_code, recipe_no

Best Sellers in Music