eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.GMD_SPEC_MATCH_GRP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 73


SELECT /*+ INDEX ( b GMD_INVENTORY_SPEC_VRS_N1 ) */
    a.spec_id,b.spec_vr_id,a.grade_code,revision,decode(a.grade_code,
    p_inventory_spec_rec.grade_code,1,2),
    b.locator_id,b.subinventory,b.parent_lot_number,b.lot_number,
    b.organization_id
FROM gmd_specifications_b a,gmd_inventory_spec_vrs b
WHERE
     a.inventory_item_id = p_inventory_spec_rec.inventory_item_id
AND ((p_inventory_spec_rec.revision = a.revision) OR ( p_inventory_spec_rec.revision IS NULL AND a.revision IS NULL)
     OR (p_inventory_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between  400 and 499) OR (a.spec_status between  700 and 799) OR (a.spec_status between  900 and 999))
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade_code = a.grade_code ) OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade_code = l_item_default_grade_code))
AND  a.spec_id = b.spec_id
AND ((b.spec_vr_status between  400 and 499) OR (b.spec_vr_status between  700 and 799) OR (b.spec_vr_status between  900 and 999))
AND  b.delete_mark = 0
AND  p_inventory_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_inventory_spec_rec.date_effective)
AND ((p_inventory_spec_rec.locator_id = b.locator_id) OR ( p_inventory_spec_rec.locator_id IS NULL AND b.locator_id IS NULL)
     OR (p_inventory_spec_rec.locator_id IS NOT NULL AND b.locator_id IS NULL ))
AND ((p_inventory_spec_rec.subinventory = b.subinventory) OR ( p_inventory_spec_rec.subinventory IS NULL AND b.subinventory IS NULL)
     OR (p_inventory_spec_rec.subinventory IS NOT NULL AND b.subinventory IS NULL ))
AND ((p_inventory_spec_rec.parent_lot_number = b.parent_lot_number) OR ( p_inventory_spec_rec.parent_lot_number IS NULL AND b.parent_lot_number IS NULL)
     OR (p_inventory_spec_rec.parent_lot_number IS NOT NULL AND b.parent_lot_number IS NULL ))
AND ((p_inventory_spec_rec.lot_number = b.lot_number) OR ( p_inventory_spec_rec.lot_number IS NULL AND b.lot_number IS NULL)
     OR (p_inventory_spec_rec.lot_number IS NOT NULL AND b.lot_number IS NULL ))
AND ((p_inventory_spec_rec.organization_id = b.organization_id)
     OR (p_inventory_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
ORDER BY decode(a.grade_code,p_inventory_spec_rec.grade_code,1,2),b.lot_number,b.location,b.subinventory,b.organization_id ;

Line: 105


SELECT a.spec_id,b.spec_vr_id,a.grade_code,revision,decode(a.grade_code,
    p_inventory_spec_rec.grade_code,1,2),
    b.locator_id,b.subinventory,b.parent_lot_number,b.lot_number,
    b.organization_id
FROM gmd_specifications_b a,gmd_inventory_spec_vrs b , gmd_spec_tests_b c
WHERE
     a.inventory_item_id = p_inventory_spec_rec.inventory_item_id
AND ((p_inventory_spec_rec.revision = a.revision) OR ( p_inventory_spec_rec.revision IS NULL AND a.revision IS NULL)
     OR (p_inventory_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between  400 and 499) OR (a.spec_status between  700 and 799) OR (a.spec_status between  900 and 999))
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade_code = a.grade_code ) OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade_code = l_item_default_grade_code))
AND  a.spec_id = c.spec_id
AND  c.test_id = p_inventory_spec_rec.test_id
AND  a.spec_id = b.spec_id
AND ((b.spec_vr_status between  400 and 499) OR (b.spec_vr_status between  700 and 799) OR (b.spec_vr_status between  900 and 999))
AND  b.delete_mark = 0
AND  p_inventory_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_inventory_spec_rec.date_effective)
AND ((p_inventory_spec_rec.locator_id = b.locator_id) OR ( p_inventory_spec_rec.locator_id IS NULL AND b.locator_id IS NULL)
     OR (p_inventory_spec_rec.locator_id IS NOT NULL AND b.locator_id IS NULL ))
AND ((p_inventory_spec_rec.subinventory = b.subinventory) OR ( p_inventory_spec_rec.subinventory IS NULL AND b.subinventory IS NULL)
     OR (p_inventory_spec_rec.subinventory IS NOT NULL AND b.subinventory IS NULL ))
AND ((p_inventory_spec_rec.parent_lot_number = b.parent_lot_number) OR ( p_inventory_spec_rec.parent_lot_number IS NULL AND b.parent_lot_number IS NULL)
     OR (p_inventory_spec_rec.parent_lot_number IS NOT NULL AND b.parent_lot_number IS NULL ))
AND ((p_inventory_spec_rec.lot_number = b.lot_number) OR ( p_inventory_spec_rec.lot_number IS NULL AND b.lot_number IS NULL)
     OR (p_inventory_spec_rec.lot_number IS NOT NULL AND b.lot_number IS NULL ))
AND ((p_inventory_spec_rec.organization_id = b.organization_id) OR ( p_inventory_spec_rec.organization_id IS NULL AND b.organization_id IS NULL)
     OR (p_inventory_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
ORDER BY decode(a.grade_code,p_inventory_spec_rec.grade_code,1,2),b.lot_number,b.locator_id,b.subinventory,b.organization_id ;

Line: 152


       SELECT grade_control_flag, default_grade INTO  l_grade_ctl ,l_item_default_grade_code
       FROM   mtl_system_items_b
       WHERE  inventory_item_id = p_inventory_spec_rec.inventory_item_id
       AND organization_id = p_inventory_spec_rec.organization_id;

Line: 304


SELECT /*+  INDEX (b gmd_customer_spec_vrs_n1 )    */
     a.spec_id,b.spec_vr_id,a.revision,a.grade_code ,DECODE(a.grade_code,p_customer_spec_rec.grade_code,1,2) grade_order_by,
     b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id
FROM gmd_specifications_b a,gmd_customer_spec_vrs b
WHERE
     a.inventory_item_id = p_customer_spec_rec.inventory_item_id
AND ((p_customer_spec_rec.revision = a.revision) OR ( p_customer_spec_rec.revision IS NULL AND a.revision IS NULL)
     OR (p_customer_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between  700 and 799) OR (a.spec_status between  900 and 999))
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_customer_spec_rec.grade_code = a.grade_code ) OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL ))
AND  a.spec_id = b.spec_id
AND ((b.spec_vr_status between  700 and 799) OR (b.spec_vr_status between  900 and 999))
AND  b.delete_mark = 0
AND  b.cust_id = p_customer_spec_rec.cust_id
AND  p_customer_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_customer_spec_rec.date_effective + 1)
AND ((p_customer_spec_rec.organization_id = b.organization_id) OR ( p_customer_spec_rec.organization_id IS NULL AND b.organization_id IS NULL)
     OR (p_customer_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
AND ((p_customer_spec_rec.org_id = b.org_id) OR ( p_customer_spec_rec.org_id IS NULL AND b.org_id IS NULL)
     OR (p_customer_spec_rec.org_id IS NOT NULL AND b.org_id IS NULL ))
AND ((p_customer_spec_rec.ship_to_site_id = b.ship_to_site_id) OR ( p_customer_spec_rec.ship_to_site_id IS NULL AND b.ship_to_site_id IS NULL)
     OR (p_customer_spec_rec.ship_to_site_id IS NOT NULL AND b.ship_to_site_id IS NULL ))
AND ((p_customer_spec_rec.order_id = b.order_id) OR ( p_customer_spec_rec.order_id IS NULL AND b.order_id IS NULL)
     OR (p_customer_spec_rec.order_id IS NOT NULL AND b.order_id IS NULL ))
AND ((l_order_line_number = b.order_line) OR ( l_order_line_number IS NULL AND b.order_line IS NULL)
     OR (l_order_line_number IS NOT NULL AND b.order_line IS NULL ))
ORDER BY grade_order_by,b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id;

Line: 361


           SELECT line_number + (shipment_number/10) INTO l_order_line_number
           FROM OE_ORDER_LINES_ALL
           WHERE  line_id  =  p_customer_spec_rec.order_line_id ;

Line: 562


SELECT                          /*+  INDEX ( b gmd_wip_spec_vrs_n1 )  */
    a.spec_id,
    b.spec_vr_id,
    a.revision,
    a.grade_code,
    DECODE(a.grade_code,p_wip_spec_rec.grade_code,1,2) grade_order_by,
    b.charge,
    b.step_no,
    b.routing_vers,
    b.routing_no,
    b.formulaline_id,
    b.material_detail_id,
    b.formula_vers,
    b.formula_no,
    b.recipe_version,
    b.recipe_no,
    b.batch_id,
    b.oprn_vers,
    b.oprn_no,
    b.organization_id
FROM gmd_specifications_b a,
     gmd_wip_spec_vrs b
WHERE
     a.inventory_item_id = p_wip_spec_rec.inventory_item_id
AND ((p_wip_spec_rec.revision = a.revision)
     OR ( p_wip_spec_rec.revision IS NULL AND a.revision IS NULL)
     OR (p_wip_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between  400 and 499)
     OR (a.spec_status between  700 and 799)
     OR (a.spec_status between  900 and 999))
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_wip_spec_rec.grade_code = a.grade_code )
     OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade_code = l_item_default_grade_code))
AND  a.spec_id = b.spec_id
AND ((b.spec_vr_status between  400 and 499)
     OR (b.spec_vr_status between  700 and 799)
     OR (b.spec_vr_status between  900 and 999))
AND  b.delete_mark = 0
AND  p_wip_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_wip_spec_rec.date_effective)
AND (p_wip_spec_rec.organization_id = NVL(b.organization_id,p_wip_spec_rec.organization_id))
AND ((p_wip_spec_rec.batch_id = b.batch_id)
     OR ( p_wip_spec_rec.batch_id IS NULL AND b.batch_id IS NULL)
     OR (p_wip_spec_rec.batch_id IS NOT NULL AND b.batch_id IS NULL ))
AND ((l_formula_no = b.formula_no)
     OR ( l_formula_no IS NULL AND b.formula_no IS NULL)
     OR (l_formula_no IS NOT NULL AND b.formula_no IS NULL ))
AND ((l_formula_vers = b.formula_vers)
     OR ( l_formula_vers IS NULL AND b.formula_vers IS NULL)
     OR (l_formula_vers IS NOT NULL AND b.formula_vers IS NULL ))
AND ((l_recipe_no = b.recipe_no)
     OR ( l_recipe_no IS NULL AND b.recipe_no IS NULL)
     OR (l_recipe_no IS NOT NULL AND b.recipe_no IS NULL ))
AND ((l_recipe_version = b.recipe_version)
     OR ( l_recipe_version IS NULL AND b.recipe_version IS NULL)
     OR (l_recipe_version IS NOT NULL AND b.recipe_version IS NULL ))
AND ((p_wip_spec_rec.charge = b.charge)
     OR ( p_wip_spec_rec.charge IS NULL AND b.charge IS NULL)
     OR (p_wip_spec_rec.charge IS NOT NULL AND b.charge IS NULL ))
AND ((l_step_no = b.step_no)
     OR (l_step_no IS NULL AND b.step_no IS NULL)
     OR (nvl(p_wip_spec_rec.find_spec_with_step,'N') = 'N' and l_step_no IS NOT NULL AND b.step_no IS NULL ))
AND ((l_routing_no = b.routing_no)
     OR ( l_routing_no IS NULL AND b.routing_no IS NULL)
     OR (l_routing_no IS NOT NULL AND b.routing_no IS NULL ))
AND ((l_routing_vers = b.routing_vers)
     OR ( l_routing_vers IS NULL AND b.routing_vers IS NULL)
     OR (l_routing_vers IS NOT NULL AND b.routing_vers IS NULL ))
AND ((p_wip_spec_rec.formulaline_id = b.formulaline_id)
     OR ( p_wip_spec_rec.formulaline_id IS NULL AND b.formulaline_id IS NULL)
     OR (p_wip_spec_rec.formulaline_id IS NOT NULL AND b.formulaline_id IS NULL )
     OR (p_wip_spec_rec.batch_id IS NOT NULL ))
AND ((p_wip_spec_rec.material_detail_id = b.material_detail_id)
     OR ( p_wip_spec_rec.material_detail_id IS NULL AND b.material_detail_id IS NULL)
     OR (p_wip_spec_rec.material_detail_id IS NOT NULL AND b.material_detail_id IS NULL ))
AND ((l_oprn_no = b.oprn_no)
     OR ( l_oprn_no IS NULL AND b.oprn_no IS NULL)
     OR (l_oprn_no IS NOT NULL AND b.oprn_no IS NULL ))
AND ((l_oprn_vers = b.oprn_vers)
     OR ( l_oprn_vers IS NULL AND b.oprn_vers IS NULL)
     OR (l_oprn_vers IS NOT NULL AND b.oprn_vers IS NULL ))
ORDER BY grade_order_by, b.charge, b.step_no, b.routing_id, b.routing_no, b.material_detail_id,
         b.formulaline_id, b.formula_id, b.formula_no, b.recipe_id, b.recipe_no, b.batch_id, b.oprn_id,
         b.oprn_no, b.organization_id ;

Line: 661


     SELECT recipe_no,recipe_version INTO l_recipe_no,l_recipe_version
     FROM   GMD_RECIPES_B
     WHERE  recipe_id = p_wip_spec_rec.recipe_id ;

Line: 676


     SELECT formula_no,formula_vers INTO l_formula_no,l_formula_vers
     FROM   fm_form_mst_b
     WHERE  formula_id = p_wip_spec_rec.formula_id;

Line: 691


     SELECT routing_no,routing_vers INTO l_routing_no,l_routing_vers
     FROM   gmd_routings_b
     WHERE  routing_id = p_wip_spec_rec.routing_id ;

Line: 706


     SELECT oprn_no,oprn_vers INTO l_oprn_no,l_oprn_vers
     FROM   gmd_operations_b
     WHERE  oprn_id = p_wip_spec_rec.oprn_id;

Line: 723


        SELECT BATCHSTEP_NO INTO l_step_no
     	FROM   gme_batch_steps
     	WHERE  batchstep_id = p_wip_spec_rec.step_id
     	AND    batch_id = p_wip_spec_rec.batch_id;

Line: 732


           SELECT ROUTINGSTEP_NO INTO l_step_no
     	   FROM   fm_rout_dtl
     	   WHERE  routingstep_id = p_wip_spec_rec.step_id
     	   AND    routing_id = p_wip_spec_rec.routing_id;

Line: 743


      	SELECT ROUTINGSTEP_NO INTO l_step_no
     	FROM   fm_rout_dtl
     	WHERE  routingstep_id = p_wip_spec_rec.step_id
     	AND    routing_id = p_wip_spec_rec.routing_id ;

Line: 751


           SELECT BATCHSTEP_NO INTO l_step_no
     	   FROM   gme_batch_steps
     	   WHERE  batchstep_id = p_wip_spec_rec.step_id
     	   AND    batch_id = p_wip_spec_rec.batch_id;

Line: 780


       SELECT grade_control_flag, default_grade INTO  l_grade_ctl ,l_item_default_grade_code
       FROM   mtl_system_items_b
       WHERE  inventory_item_id = p_wip_spec_rec.inventory_item_id
       AND organization_id = p_wip_spec_rec.organization_id;

Line: 1012


SELECT /*+  INDEX ( b gmd_supplier_spec_vrs_n1)   */
     a.spec_id,b.spec_vr_id,a.revision,a.grade_code,
	 decode(a.grade_code,p_supplier_spec_rec.grade_code,1,2),b.po_line_id,
	 b.po_header_id,b.supplier_site_id,b.supplier_id
FROM gmd_specifications_b a,gmd_supplier_spec_vrs b
WHERE
     a.inventory_item_id = p_supplier_spec_rec.inventory_item_id
AND ((p_supplier_spec_rec.revision = a.revision) OR ( p_supplier_spec_rec.revision IS NULL AND a.revision IS NULL)
     OR (p_supplier_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between  700 and 799) OR (a.spec_status between  900 and 999))
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_supplier_spec_rec.grade_code = a.grade_code ) OR
      (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL ))
AND  a.spec_id = b.spec_id
AND ((b.spec_vr_status between  700 and 799) OR (b.spec_vr_status between  900 and 999))
AND  b.delete_mark = 0
AND  b.supplier_id = p_supplier_spec_rec.supplier_id
AND  p_supplier_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_supplier_spec_rec.date_effective)
AND ((p_supplier_spec_rec.organization_id = b.organization_id) OR ( p_supplier_spec_rec.organization_id IS NULL AND b.organization_id IS NULL)
     OR (p_supplier_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
AND ((p_supplier_spec_rec.org_id = b.org_id) OR ( p_supplier_spec_rec.org_id IS NULL AND b.org_id IS NULL)
     OR (p_supplier_spec_rec.org_id IS NOT NULL AND b.org_id IS NULL ))
AND ((p_supplier_spec_rec.po_line_id = b.po_line_id) OR ( p_supplier_spec_rec.po_line_id IS NULL AND b.po_line_id IS NULL)
     OR (p_supplier_spec_rec.po_line_id IS NOT NULL AND b.po_line_id IS NULL ))
AND ((p_supplier_spec_rec.po_header_id = b.po_header_id) OR ( p_supplier_spec_rec.po_header_id IS NULL AND b.po_header_id IS NULL)
     OR (p_supplier_spec_rec.po_header_id IS NOT NULL AND b.po_header_id IS NULL ))
AND ((p_supplier_spec_rec.supplier_site_id = b.supplier_site_id) OR ( p_supplier_spec_rec.supplier_site_id IS NULL AND b.supplier_site_id IS NULL)
     OR (p_supplier_spec_rec.supplier_site_id IS NOT NULL AND b.supplier_site_id IS NULL ))
ORDER BY DECODE(a.grade_code,p_supplier_spec_rec.grade_code,1,2),b.po_line_id,b.po_header_id,b.supplier_site_id,b.supplier_id;

Line: 1247


  SELECT gst.test_id
  FROM   GMD_SPEC_TESTS_B gst
  WHERE  gst.spec_id = p_spec_id
  AND    gst.optional_ind is NULL  ;

Line: 1253


  SELECT nvl(gs.lot_number,-1), gs.sample_id,gr.test_id,gr.result_value_num,gr.result_value_char   --  9744927  added nvl(gs.lot_number,-1)
  FROM   GMD_SAMPLES gs , GMD_RESULTS gr
  WHERE  gs.sample_id = gr.sample_id
  AND    gs.delete_mark = 0
  AND    gs.sample_id IN ( SELECT ssd.sample_id FROM gmd_sample_spec_disp ssd
  			  WHERE ssd.sample_id = gs.sample_id
  			  AND ssd.disposition IN ('3C','4A','5AV','6RJ') )
  AND    gs.inventory_item_id  	= l_inventory_item_id
  AND    gs.organization_id = l_organization_id
  AND   (((gs.lot_number 	= l_lot_number  OR gs.lot_number IS NULL)
  and  (gs.parent_lot_number = l_parent_lot_number or  gs.parent_lot_number is NULL ))  --    l_parent_lot_number is null) )   -- 9744927
  or (gs.parent_lot_number = l_parent_lot_number and   gs.lot_number is null) ) -- 9744927  added latest
  AND   (gs.subinventory 	= l_subinventory OR gs.subinventory IS NULL)
  AND   (gs.locator_id  	= l_locator_id OR gs.locator_id IS NULL )
  AND    gr.delete_mark = 0
  AND   (gr.result_value_num IS NOT NULL or gr.result_value_char IS NOT NULL)
  ORDER BY 1 desc,gs.date_drawn desc,gs.location,gs.subinventory,gr.result_date desc ; -- -- 9744927 added desc to gs.lot_number (now 1 column  )

Line: 1275


  SELECT parent_lot_number
  FROM mtl_lot_numbers
  WHERE inventory_item_id = l_inventory_item_id
  and lot_number  = l_lot_number
  and organization_id = l_organization_id;

Line: 1323


          	SELECT lot_no,sublot_no INTO l_lot_no,l_sublot_no
           	FROM IC_LOTS_MST
           	WHERE  lot_id  =  p_lots(l_lot_counter).lot_id
           	AND    item_id =  p_lots(l_lot_counter).item_id;

Line: 1353


       result_test_list.DELETE;

Line: 1372


             result_test_list.DELETE;

Line: 1471


SELECT a.spec_id, b.spec_vr_id,
       b.locator_organization_id,
       b.subinventory,
       b.locator_id
FROM   gmd_specifications_b a,
       gmd_monitoring_spec_vrs b,
       gmd_qc_status qs1,
       gmd_qc_status qs2
WHERE  (a.spec_status = qs1.status_code AND
        qs1.entity_type = 'S' AND
        qs1.status_type in (400, 700, 900)
       )
AND    a.delete_mark = 0
AND    a.spec_id = b.spec_id
AND    (b.spec_vr_status = qs2.status_code AND
        qs2.entity_type = 'S' AND
        qs2.status_type in (400, 700, 900)
       )
AND    b.delete_mark = 0
AND    b.rule_type = 'L'
AND    p_location_spec_rec.date_effective between b.start_date and nvl(b.end_date, p_location_spec_rec.date_effective)
AND    ((p_location_spec_rec.locator_organization_id = b.locator_organization_id) OR
        (p_location_spec_rec.locator_organization_id IS NULL AND b.locator_organization_id IS NULL) OR
        (p_location_spec_rec.locator_organization_id IS NOT NULL AND b.locator_organization_id IS NULL)
       )
AND    ((p_location_spec_rec.subinventory = b.subinventory) OR
        (p_location_spec_rec.subinventory IS NULL AND b.subinventory IS NULL) OR
        (p_location_spec_rec.subinventory IS NOT NULL AND b.subinventory IS NULL)
       )
AND    ((p_location_spec_rec.locator_id = b.locator_id) OR
        (p_location_spec_rec.locator_id IS NULL AND b.locator_id IS NULL) OR
        (p_location_spec_rec.locator_id IS NOT NULL AND b.locator_id IS NULL )
       )
ORDER BY b.locator_id,b.subinventory,b.locator_organization_id ;

Line: 1583


SELECT a.spec_id, b.spec_vr_id,
       b.resource_organization_id,
       b.resources,
       b.resource_instance_id
FROM   gmd_specifications_b a,
       gmd_monitoring_spec_vrs b,
       gmd_qc_status qs1,
       gmd_qc_status qs2
WHERE  (a.spec_status = qs1.status_code AND
        qs1.entity_type = 'S' AND
        qs1.status_type in (400, 700, 900)
       )
AND    a.delete_mark = 0
AND    a.spec_id = b.spec_id
AND    (b.spec_vr_status = qs2.status_code AND
        qs2.entity_type = 'S' AND
        qs2.status_type in (400, 700, 900)
       )
AND    b.delete_mark = 0
AND    b.rule_type = 'R'
AND    p_resource_spec_rec.date_effective between b.start_date and nvl(b.end_date, p_resource_spec_rec.date_effective)
AND    ((p_resource_spec_rec.resource_organization_id = b.resource_organization_id) OR
        (p_resource_spec_rec.resource_organization_id IS NULL AND b.resource_organization_id IS NULL) OR
        (p_resource_spec_rec.resource_organization_id IS NOT NULL AND b.resource_organization_id IS NULL)
       )
AND    ((p_resource_spec_rec.resources = b.resources) OR
        (p_resource_spec_rec.resources IS NULL AND b.resources IS NULL) OR
        (p_resource_spec_rec.resources IS NOT NULL AND b.resources IS NULL)
       )
AND    ((p_resource_spec_rec.resource_instance_id = b.resource_instance_id) OR
        (p_resource_spec_rec.resource_instance_id IS NULL AND b.resource_instance_id IS NULL) OR
        (p_resource_spec_rec.resource_instance_id IS NOT NULL AND b.resource_instance_id IS NULL )
       )
ORDER BY b.resource_instance_id, b.resources, b.resource_organization_id ;