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_MIG_GRP SQL Statements

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

Line: 72


SELECT /*+ INDEX ( b GMD_INVENTORY_SPEC_VRS_N1 ) */
    a.spec_id,b.spec_vr_id,a.grade,decode(a.grade,
    p_inventory_spec_rec.grade,1,2),
    b.location,b.whse_code,b.sublot_no,b.lot_no,
    b.orgn_code
FROM gmd_specifications_b a,gmd_inventory_spec_vrs b
WHERE
     a.item_id = p_inventory_spec_rec.item_id
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade = a.grade ) OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
AND  a.spec_id = b.spec_id
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.location = b.location) OR ( p_inventory_spec_rec.location IS NULL AND b.location IS NULL)
     OR (p_inventory_spec_rec.location IS NOT NULL AND b.location IS NULL ))
AND ((p_inventory_spec_rec.whse_code = b.whse_code) OR ( p_inventory_spec_rec.whse_code IS NULL AND b.whse_code IS NULL)
     OR (p_inventory_spec_rec.whse_code IS NOT NULL AND b.whse_code IS NULL ))
AND ((l_lot_no = b.lot_no) OR ( l_lot_no IS NULL AND b.lot_no IS NULL)
     OR (l_lot_no IS NOT NULL AND b.lot_no IS NULL ))
AND ((l_sublot_no = b.sublot_no) OR ( l_sublot_no IS NULL AND b.sublot_no IS NULL)
     OR (l_sublot_no IS NOT NULL AND b.sublot_no IS NULL ))
AND ((p_inventory_spec_rec.orgn_code = b.orgn_code) OR ( p_inventory_spec_rec.orgn_code IS NULL AND b.orgn_code IS NULL)
     OR (p_inventory_spec_rec.orgn_code IS NOT NULL AND b.orgn_code IS NULL ))
ORDER BY decode(a.grade,p_inventory_spec_rec.grade,1,2),b.sublot_no,b.lot_no,b.location,b.whse_code,b.orgn_code ;

Line: 100


SELECT a.spec_id,b.spec_vr_id,a.grade,decode(a.grade,
    p_inventory_spec_rec.grade,1,2),
    b.location,b.whse_code,b.sublot_no,b.lot_no,
    b.orgn_code
FROM gmd_specifications_b a,gmd_inventory_spec_vrs b , gmd_spec_tests_b c
WHERE
     a.item_id = p_inventory_spec_rec.item_id
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade = a.grade )
 OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
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.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.location = b.location)
     OR ( p_inventory_spec_rec.location IS NULL AND b.location IS NULL)
     OR (p_inventory_spec_rec.location IS NOT NULL AND b.location IS NULL ))
AND ((p_inventory_spec_rec.whse_code = b.whse_code) OR ( p_inventory_spec_rec.whse_code IS NULL AND b.whse_code IS NULL)
     OR (p_inventory_spec_rec.whse_code IS NOT NULL AND b.whse_code IS NULL ))
AND ((l_lot_no = b.lot_no) OR ( l_lot_no IS NULL AND b.lot_no IS NULL)
     OR (l_lot_no IS NOT NULL AND b.lot_no IS NULL ))
AND ((l_sublot_no = b.sublot_no) OR ( l_sublot_no IS NULL AND b.sublot_no IS NULL)
     OR (l_sublot_no IS NOT NULL AND b.sublot_no IS NULL ))
AND ((p_inventory_spec_rec.orgn_code = b.orgn_code) OR ( p_inventory_spec_rec.orgn_code IS NULL AND b.orgn_code IS NULL)
     OR (p_inventory_spec_rec.orgn_code IS NOT NULL AND b.orgn_code IS NULL ))
ORDER BY decode(a.grade,p_inventory_spec_rec.grade,1,2),b.sublot_no,b.lot_no,b.location,b.whse_code,b.orgn_code ;

Line: 147


           SELECT lot_no,sublot_no INTO l_lot_no,l_sublot_no
           FROM IC_LOTS_MST
           WHERE  lot_id  =  p_inventory_spec_rec.lot_id ;

Line: 161


       SELECT grade_ctl,qc_grade INTO  l_grade_ctl ,l_item_default_grade
       FROM   IC_ITEM_MST_B
       WHERE  ITEM_ID = p_inventory_spec_rec.item_id ;

Line: 317


SELECT /*+  INDEX (b gmd_customer_spec_vrs_n1 )    */
     a.spec_id,b.spec_vr_id,a.grade grade,DECODE(a.grade,p_customer_spec_rec.grade,1,2) grade_order_by,b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id,DECODE(b.orgn_code,l_orgn_code,1,NULL,2,3) orgn_code_order_by,b.orgn_code
FROM gmd_specifications_b a,gmd_customer_spec_vrs b
WHERE
     a.item_id = p_customer_spec_rec.item_id
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_customer_spec_rec.grade = a.grade )
      OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
AND  a.spec_id = b.spec_id
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 ((l_orgn_code = NVL(b.orgn_code,l_orgn_code))  OR ( p_customer_spec_rec.look_in_other_orgn = 'Y' and l_orgn_code <> b.orgn_code ))
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 DECODE(a.grade,p_customer_spec_rec.grade,1,2),b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id,DECODE(b.orgn_code,l_orgn_code,1,NULL,2,3);

Line: 362


           SELECT orgn_code INTO l_orgn_code
           FROM   IC_WHSE_MST
           WHERE  whse_code = p_customer_spec_rec.whse_code;

Line: 389


           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: 403


       SELECT grade_ctl,qc_grade INTO  l_grade_ctl ,l_item_default_grade
       FROM   IC_ITEM_MST_B
       WHERE  ITEM_ID = p_customer_spec_rec.item_id ;

Line: 615


SELECT /*+  INDEX ( b gmd_wip_spec_vrs_n1 )  */
    a.spec_id,b.spec_vr_id,a.grade grade,
	DECODE(a.grade,p_wip_spec_rec.grade,1,2) grade_order_by,
	b.charge,b.step_no,
    b.routing_vers,b.routing_no,b.formulaline_id,
	b.formula_vers,b.formula_no,b.recipe_version,
	b.recipe_no,b.batch_id,b.oprn_vers,b.oprn_no,b.orgn_code
FROM gmd_specifications_b a,gmd_wip_spec_vrs b
WHERE
     a.item_id = p_wip_spec_rec.item_id
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_wip_spec_rec.grade = a.grade ) OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
AND  a.spec_id = b.spec_id
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.orgn_code = NVL(b.orgn_code,p_wip_spec_rec.orgn_code))
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 ))
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 DECODE(a.grade,p_wip_spec_rec.grade,1,2),b.charge,b.step_no,
b.routing_id,b.routing_no,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.orgn_code ;

Line: 676


     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: 691


     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: 706


     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: 721


     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: 738


        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: 747


           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: 758


      	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: 766


           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: 795


       SELECT grade_ctl,qc_grade INTO  l_grade_ctl ,l_item_default_grade
       FROM   IC_ITEM_MST_B
       WHERE  ITEM_ID = p_wip_spec_rec.item_id ;

Line: 1024


SELECT /*+  INDEX ( b gmd_supplier_spec_vrs_n1)   */
     a.spec_id,b.spec_vr_id,a.grade,
	 decode(a.grade,p_supplier_spec_rec.grade,1,2),b.po_line_id,
	 b.po_header_id,b.supplier_site_id,b.supplier_id,b.orgn_code
FROM gmd_specifications_b a,gmd_supplier_spec_vrs b
WHERE
     a.item_id = p_supplier_spec_rec.item_id
AND  a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_supplier_spec_rec.grade = a.grade )
      OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
      OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
AND  a.spec_id = b.spec_id
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.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 ))
AND (l_orgn_code = NVL(b.orgn_code,l_orgn_code))
ORDER BY DECODE(a.grade,p_supplier_spec_rec.grade,1,2),b.po_line_id,
b.po_header_id,b.supplier_site_id,b.supplier_id,b.orgn_code ;

Line: 1069


           SELECT orgn_code INTO l_orgn_code
           FROM   IC_WHSE_MST
           WHERE  whse_code = p_supplier_spec_rec.whse_code;

Line: 1085


       SELECT grade_ctl,qc_grade INTO  l_grade_ctl ,l_item_default_grade
       FROM   IC_ITEM_MST_B
       WHERE  ITEM_ID = p_supplier_spec_rec.item_id ;

Line: 1296


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

Line: 1302


  SELECT gs.sample_id,gr.test_id,gr.result_value_num,gr.result_value_char
  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.item_id  	= l_item_id
  AND   (gs.lot_no   	= l_lot_no  OR gs.lot_no IS NULL)
  AND   (gs.sublot_no  	= l_sublot_no  OR gs.sublot_no IS NULL)
  AND   (gs.whse_code 	= l_whse_code OR gs.whse_code IS NULL)
  AND   (gs.location  	= l_location OR gs.location 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 gs.lot_no,gs.date_drawn desc,gs.sublot_no,gs.location,gs.whse_code,gr.result_date desc ;

Line: 1362


          	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: 1380


       result_test_list.DELETE;

Line: 1399


             result_test_list.DELETE;