APPS.GMO_SWORKBENCH_PVT SQL Statements

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

Line: 4

PROCEDURE UPDATE_PLANNING_STATUS
(
    P_API_VERSION           IN NUMBER,
    P_INIT_MSG_LIST         IN VARCHAR2 DEFAULT FND_API.G_FALSE,
    X_RETURN_STATUS         OUT NOCOPY VARCHAR2,
    X_MSG_COUNT             OUT NOCOPY NUMBER,
    X_MSG_DATA              OUT NOCOPY VARCHAR2,
    P_RESERVATION_ID        IN NUMBER,
    P_DISPENSE_ID           IN NUMBER,
    P_DISPENSED_DATE        IN DATE,
    P_DISPENSE_TYPE         IN VARCHAR2,
    P_DISPENSE_AREA_ID      IN NUMBER,
    P_DISP_ORG_ID           IN NUMBER
) is
 gmo_dispensing_planning_rec GMO_DISPENSING_PLANNING%ROWTYPE;

Line: 23

 L_LAST_UPDATE_DATE DATE;

Line: 24

 L_LAST_UPDATED_BY NUMBER;

Line: 25

 L_LAST_UPDATE_LOGIN NUMBER;

Line: 28

select count(*) into l_count from gmo_dispensing_planning
where reservation_id = P_RESERVATION_ID
and status = 'PLANNED';

Line: 33

  	update gmo_dispensing_planning
    set
    status = 'DISPENSD',
    dispense_id = P_DISPENSE_ID,
    dispensed_date = P_DISPENSED_DATE
   	where reservation_id = P_RESERVATION_ID
   	and status ='PLANNED';

Line: 44

    select * into gmo_dispensing_planning_rec from gmo_dispensing_planning
   	where reservation_id = P_RESERVATION_ID
   	and status ='PLANNED';

Line: 49

   	update gmo_dispensing_planning
    set
    status = 'DISPENSD',
    dispense_id = P_DISPENSE_ID,
    dispensed_date = P_DISPENSED_DATE
   	where reservation_id = P_RESERVATION_ID
   	and status ='PLANNED';

Line: 61

	X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
	X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
	X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
    );

Line: 65

  	insert into gmo_dispensing_planning (
	PLANNED_TASK_ID,
	RESERVATION_ID,
	DISPENSE_AREA_ID ,
	PRIORITY ,
	PLANNED_DISPENSING_DATE,
	OPER_ID,
	STATUS ,
	DISPENSE_ID ,
    DISPENSED_DATE,
	CREATED_BY  ,
	CREATION_DATE   ,
	LAST_UPDATED_BY ,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN
   	)
   	values
 	(
         GMO_DISPENSING_PLANNING_S.nextval, --- get the next value from sequence
         P_RESERVATION_ID,
         gmo_dispensing_planning_rec.DISPENSE_AREA_ID,
 	     gmo_dispensing_planning_rec.PRIORITY,
         gmo_dispensing_planning_rec.PLANNED_DISPENSING_DATE,
         gmo_dispensing_planning_rec.OPER_ID,
         --'PDISPENSE', ---signifying partial dispense----
         gmo_dispensing_planning_rec.status,
         null,
         gmo_dispensing_planning_rec.DISPENSED_DATE,
         --L_CREATED_BY,
         gmo_dispensing_planning_rec.created_by,
         L_CREATION_DATE,
  	     --L_LAST_UPDATED_BY,
  	     gmo_dispensing_planning_rec.last_updated_by,
         L_LAST_UPDATE_DATE,
	     --L_LAST_UPDATE_LOGIN
	     gmo_dispensing_planning_rec.last_update_login
  	);

Line: 108

	X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
	X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
	X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
    	);

Line: 113

   select count(*) into l_count from gmo_dispensing_planning
    where reservation_id = P_RESERVATION_ID
    and status = 'UNPLANNED';

Line: 118

      	insert into gmo_dispensing_planning (
    	PLANNED_TASK_ID,
    	RESERVATION_ID,
    	DISPENSE_AREA_ID,
    	PRIORITY,
    	PLANNED_DISPENSING_DATE,
    	OPER_ID,
    	STATUS,
    	DISPENSE_ID,
                      dispensed_date,
    	CREATED_BY,
    	CREATION_DATE,
    	LAST_UPDATED_BY,
    	LAST_UPDATE_DATE,
    	LAST_UPDATE_LOGIN
   	    )
       	values
 	      (
         GMO_DISPENSING_PLANNING_S.nextval, --- get the next value from sequence
         P_RESERVATION_ID,
         P_DISPENSE_AREA_ID,
         'MEDIUM',
         null,
         null,
         --'UNPDISPENSE', ---signifying unplanned dispense----
         'DISPENSD',
         P_DISPENSE_ID,
         P_DISPENSED_DATE,
         L_CREATED_BY,
         L_CREATION_DATE,
  	      L_LAST_UPDATED_BY,
     	 L_LAST_UPDATE_DATE,
    	 L_LAST_UPDATE_LOGIN
  	  );

Line: 153

             update gmo_dispensing_planning
            set
            status = 'DISPENSD',
            dispense_id = P_DISPENSE_ID,
            dispense_area_id = P_DISPENSE_AREA_ID,
            dispensed_date = P_DISPENSED_DATE
           	where reservation_id = P_RESERVATION_ID
           	and status ='UNPLANNED';

Line: 163

       select DISPENSE_AREA_ID into L_DISP_AREA_ID
        from gmo_dispense_area_b ar
        where ar.organization_id = P_DISP_ORG_ID and ar.default_area_ind = 'Y';

Line: 167

       	insert into gmo_dispensing_planning (
	       PLANNED_TASK_ID,
        	RESERVATION_ID,
        	DISPENSE_AREA_ID,
        	PRIORITY,
        	PLANNED_DISPENSING_DATE,
        	OPER_ID,
        	STATUS,
        	DISPENSE_ID,
                         dispensed_date,
        	CREATED_BY,
        	CREATION_DATE,
        	LAST_UPDATED_BY,
        	LAST_UPDATE_DATE,
        	LAST_UPDATE_LOGIN
       	)
   	    values
     	(
        	GMO_DISPENSING_PLANNING_S.nextval,
     	    P_RESERVATION_ID,
             L_DISP_AREA_ID,
             'MEDIUM',
             null,
             null,
             --'UNPDISPENSE', ---signifying unplanned dispense----
             'UNPLANNED',
             null,
             null,
             L_CREATED_BY,
             L_CREATION_DATE,
      	     L_LAST_UPDATED_BY,
         	 L_LAST_UPDATE_DATE,
    	     L_LAST_UPDATE_LOGIN
     	);

Line: 223

select count(*) into plannedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and to_char(planned_dispensing_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
and status = 'PLANNED'
and dispense_id is null;

Line: 229

select count(*) into dispensedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and to_char(dispensed_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
and dispense_id is not null;

Line: 256

select count(*) into plannedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and to_char(planned_dispensing_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
and OPER_ID = oper
and status = 'PLANNED'
and dispense_id is null;

Line: 263

select count(*) into dispensedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and to_char(dispensed_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
and OPER_ID = oper
and dispense_id is not null;

Line: 291

select count(*) into plannedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and planned_dispensing_date between week_start_date and week_end_date
and status = 'PLANNED'
and dispense_id is null;

Line: 297

select count(*) into dispensedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and dispensed_date between week_start_date and week_end_date
and dispense_id is not null;

Line: 325

select count(*) into plannedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and OPER_ID = oper
and planned_dispensing_date between week_start_date and week_end_date
and status = 'PLANNED'
and dispense_id is null;

Line: 332

select count(*) into dispensedPer from gmo_dispensing_planning
where dispense_area_id = area_id
and OPER_ID = oper
and dispensed_date between week_start_date and week_end_date
and dispense_id is not null;

Line: 449

   SELECT *
     FROM gme_material_details
    WHERE material_detail_id = v_material_detail_id
      AND organization_id = v_org_id;

Line: 483

select reservation_id into x_res_id from mtl_reservations RES,gme_material_details GMD where GMD.organization_id =p_org_id and
GMD.material_detail_id =p_material_detail_id and  RES.DEMAND_SOURCE_HEADER_ID (+) = GMD.BATCH_ID
AND RES.DEMAND_SOURCE_LINE_ID(+) = GMD.MATERIAL_DETAIL_ID;

Line: 523

   SELECT *
     FROM mtl_onhand_quantities
    WHERE inventory_item_id = v_inventory_item_id
      AND organization_id = v_org_id
      AND subinventory_code = v_subinv
      AND ((v_lot IS NULL AND lot_number is null) or (lot_number = v_lot))
      AND ((v_locator IS NULL AND locator_id is null) or (locator_id = v_locator));

Line: 750

      PROCEDURE update_reservation (
      p_reservation_id   IN              NUMBER
     ,p_revision         IN              VARCHAR2 DEFAULT NULL
     ,p_subinventory     IN              VARCHAR2 DEFAULT NULL
     ,p_locator_id       IN              NUMBER DEFAULT NULL
     ,p_lot_number       IN              VARCHAR2 DEFAULT NULL
     ,p_new_qty          IN              NUMBER DEFAULT NULL
     ,p_new_sec_qty      IN              NUMBER DEFAULT NULL
     ,p_new_uom          IN              VARCHAR2 DEFAULT NULL
     ,p_new_date         IN              DATE DEFAULT NULL
     ,x_return_status    OUT NOCOPY      VARCHAR2)
   IS
      l_api_name   CONSTANT VARCHAR2 (30)             := 'update_reservation';

Line: 769

      update_resvn_error    EXCEPTION;

Line: 774

   SELECT *
     FROM mtl_onhand_quantities
    WHERE inventory_item_id = v_inventory_item_id
      AND organization_id = v_org_id
      AND subinventory_code = v_subinv
      AND ((v_lot IS NULL AND lot_number is null) or (lot_number = v_lot))
      AND ((v_locator IS NULL AND locator_id is null) or (locator_id = v_locator));

Line: 815

             || ':Calling inv_reservation_pub.update_reservation with reservation_id = '
             || p_reservation_id);

Line: 818

      inv_reservation_pub.update_reservation
                                 (p_api_version_number          => 1.0
                                 ,p_init_msg_lst                => fnd_api.g_false
                                 ,x_return_status               => l_return_status
                                 ,x_msg_count                   => l_msg_count
                                 ,x_msg_data                    => l_msg_data
                                 ,p_original_rsv_rec            => l_orig_rsv_rec
                                 ,p_to_rsv_rec                  => l_rsv_rec
                                 ,p_original_serial_number      => l_serial_number
                                 ,p_to_serial_number            => l_serial_number
                                 ,p_validation_flag             => fnd_api.g_true
                                 ,p_check_availability          => fnd_api.g_true);

Line: 835

             || 'Return status from inv_reservation_pub.update_reservation is '
             || l_return_status);

Line: 844

         RAISE update_resvn_error;

Line: 852

      WHEN update_resvn_error THEN
         x_return_status := l_return_status;

Line: 865

   END update_reservation;

Line: 881

      update_resvn_error    EXCEPTION;

Line: 924

         RAISE update_resvn_error;

Line: 931

      WHEN update_resvn_error THEN
         x_return_status := l_return_status;