APPS.GMD_STABILITY_STUDIES_GRP SQL Statements

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

Line: 7

      SELECT '1'
      FROM  gmd_stability_studies_b
      WHERE ss_no = p_stability_study_no;

Line: 33

     SELECT MAX(tipp.simulated_date - tip.simulation_start_date) + p_start_date
     FROM  gmd_storage_plan_details spd,gmd_test_interval_plans_b tip, gmd_test_interval_plan_periods tipp
     WHERE
     	 spd.storage_plan_id 	   = p_storage_plan_id
     AND spd.test_interval_plan_id = tip.test_interval_plan_id
     AND tip.test_interval_plan_id = tipp.test_interval_plan_id ;

Line: 66

   SELECT variant_id , variant_no,retained_samples ,sample_qty , sample_quantity_uom , storage_organization_id -- INVCONV
   FROM   gmd_ss_variants
   WHERE  material_source_id = p_source_id
   AND    delete_mark = 0 ;

Line: 106

     SELECT inventory_item_id, organization_id INTO l_inventory_item_id, l_organization_id -- INVCONV
      FROM gmd_stability_studies_b
      WHERE ss_id = (SELECT ss_id FROM gmd_ss_material_sources WHERE source_id = p_source_id);

Line: 110

     SELECT source_organization_id INTO l_source_organization_id
     FROM gmd_ss_material_sources
     WHERE source_id = p_source_id;

Line: 114

     SELECT primary_uom_code INTO l_item_uom  -- INVCONV
     FROM mtl_system_items_b
     WHERE inventory_item_id = l_inventory_item_id
     AND organization_id = l_source_organization_id;

Line: 119

     SELECT NVL(lot_number,0) INTO l_lot_number -- INVCONV
     FROM   gmd_ss_material_sources
     WHERE  source_id = p_source_id ;

Line: 139

     	 SELECT SUM(samples_per_time_point) , SUM(DECODE(samples_per_time_point,NULL,0,1)) , SUM(1)
     	 INTO   l_tl_samples , l_tl_time_points_with_samples,l_tl_time_points
    	 FROM   gmd_ss_time_points
    	 WHERE  variant_id = cr_all_variants_rec.variant_id
    	 AND    delete_mark = 0 ;

Line: 263

SELECT source_id,source_organization_id, lot_number,sample_qty FROM gmd_ss_material_sources -- INVCONV
WHERE ss_id = p_ss_id  and lot_number IS NOT NULL ;

Line: 279

     SELECT inventory_item_id INTO l_inventory_item_id  -- INVCONV
     FROM gmd_stability_studies_b
     WHERE  ss_id = p_ss_id;

Line: 288

     	  /*SELECT nvl(sum(loct_onhand),0) INTO l_onhand_qty -- INVCONV
     	  FROM   ic_loct_inv
     	  WHERE  item_id = p_item_id
     	  AND    lot_id  = cr_material_sources_lot_rec.lot_id ; */

Line: 293

     	  SELECT nvl(sum(transaction_quantity),0) INTO l_onhand_qty --INVOCNV
				FROM mtl_onhand_quantities
				WHERE inventory_item_id = l_inventory_item_id
				AND organization_id = cr_material_sources_lot_rec.source_organization_id
				AND lot_number = cr_material_sources_lot_rec.lot_number;

Line: 331

SELECT source_id FROM gmd_ss_material_sources
WHERE ss_id = p_ss_id ;

Line: 335

SELECT source_organization_id,lot_number,recipe_no
FROM gmd_ss_material_sources -- INVCONV
WHERE ss_id = p_ss_id
and not exists
( select 'x' from gmd_ss_variants
  where material_source_id = source_id ) ;

Line: 343

SELECT a.storage_spec_id,b.spec_name,b.spec_vers
FROM gmd_ss_variants a,gmd_specifications b
WHERE  a.ss_id = p_ss_id
and    a.storage_spec_id = b.spec_id
and    b.spec_status not in (400,700) ;

Line: 351

SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;

Line: 370

     SELECT organization_id,material_sources_cnt, inventory_item_id
     INTO   l_organization_id ,l_material_sources_cnt , l_inventory_item_id -- INVCONV
     FROM   gmd_stability_studies_b
     WHERE  ss_id = p_ss_id ;

Line: 396

     SELECT count(1) into l_actual_mtrl_src_cnt
     FROM   gmd_ss_material_sources
     WHERE  ss_id = p_ss_id ;

Line: 419

	    SELECT source_id INTO l_temp
	    FROM gmd_ss_material_sources
	    WHERE source_id = cr_material_sources_rec.source_id
	    FOR UPDATE OF sample_qty NOWAIT ;

Line: 425

	        UPDATE gmd_ss_material_sources
	        SET sample_qty = l_sample_qty_out,
	             sample_quantity_uom = l_sample_uom_out, -- INVCONV
	             last_updated_by  = fnd_global.user_id,
		         last_update_date  = sysdate,
		         last_update_login = fnd_global.login_id
	        WHERE source_id = cr_material_sources_rec.source_id ;

Line: 459

          SELECT l_source_organization_code ||
             decode(l_source_organization_code,NULL,NULL,decode(cr_material_src_variant_rec.lot_number || cr_material_src_variant_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
     	  	 cr_material_src_variant_rec.lot_number || decode(cr_material_src_variant_rec.lot_number,NULL,NULL,decode(cr_material_src_variant_rec.recipe_no,NULL,NULL,'-')) ||  -- INVCONV
     	  	 cr_material_src_variant_rec.recipe_no  INTO l_source_label
     	  FROM  DUAL ;

Line: 504

SELECT source_organization_id,lot_number,recipe_no,sampling_event_id
FROM gmd_ss_material_sources -- INVCONV
WHERE ss_id = p_ss_id
AND (sampling_event_id IS NULL OR lot_number IS NULL) ; -- INVCONV

Line: 513

SELECT source_organization_id,lot_number,recipe_no,yield_date,variant_no
FROM gmd_ss_material_sources a , gmd_ss_variants b -- INVCONV
WHERE a.ss_id = p_ss_id
AND   a.source_id = b.material_source_id
AND ((a.yield_date IS NULL) OR (a.yield_date > b.scheduled_start_date)) ;

Line: 520

SELECT source_organization_id,lot_number,recipe_no,variant_no
FROM gmd_ss_material_sources a , gmd_ss_variants b -- INVCONV
WHERE  a.ss_id = p_ss_id
AND    a.ss_id = b.ss_id
AND    b.storage_date IS NULL ;

Line: 528

SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;

Line: 549

     SELECT inventory_item_id INTO l_inventory_item_id -- INVCONV
     FROM   gmd_stability_studies_b
     WHERE  ss_id = p_ss_id ;

Line: 564

          SELECT l_source_organization_code ||
             decode(l_source_organization_code,NULL,NULL,decode(cr_material_src_smpl_event_rec.lot_number || cr_material_src_smpl_event_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
     	  	 cr_material_src_smpl_event_rec.lot_number || decode(cr_material_src_smpl_event_rec.lot_number,NULL,NULL,decode(cr_material_src_smpl_event_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
     	  	 cr_material_src_smpl_event_rec.recipe_no  INTO l_source_label
     	  FROM  DUAL ;

Line: 590

          SELECT l_source_organization_code ||
             decode(l_source_organization_code,NULL,NULL,decode(cr_material_src_yield_date_rec.lot_number || cr_material_src_yield_date_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
     	  	 cr_material_src_yield_date_rec.lot_number || decode(cr_material_src_yield_date_rec.lot_number,NULL,NULL,decode(cr_material_src_yield_date_rec.recipe_no,NULL,NULL,'-')) ||
     	  	 cr_material_src_yield_date_rec.recipe_no  INTO l_source_label
     	  FROM  DUAL ;

Line: 658

SELECT 'X' from wf_event_subscriptions
WHERE wf_process_name = 'STABILITY_STS_CHANGE'
and status = 'ENABLED' ;

Line: 670

      SELECT pending_status,rework_status
      INTO   l_pending_status,l_rework_status
      FROM   gmd_qc_status_next
      WHERE  current_status = p_start_status
      AND    target_status = p_target_status
      AND    entity_type = 'STABILITY' ;

Line: 693

           SELECT status into l_event_status from wf_events where name = 'oracle.apps.gmd.qm.ss.csts' ;

Line: 734

	   -- as long as the subscription and event is enabled, update the stability status
	   -- to request for approval and kick off the workflow.
	   -- if no approvals are setup , workflow will send notification to the owner of stability study.
	   -- removing the call to AME api's.

           GMD_SPEC_GRP.change_status( p_table_name    => 'GMD_STABILITY_STUDIES_B'
                	                , p_id            => p_ss_id
                        	        , p_source_status => p_start_status
                                	, p_target_status => p_target_status
                                	, p_mode          => 'P'
                                	, p_entity_type   => 'STABILITY'
           			              	, x_return_status => x_return_status
                                	, x_message       => x_message );