APPS.EAM_MATERIAL_VALIDATE_PUB SQL Statements

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

Line: 54

    *             or not and then update that field at the work order
    *             level. API will return whether shortage
    *             exists in p_shortage_exists parameter.
    *********************************************************************/

PROCEDURE Check_Shortage
         (p_api_version                 IN  NUMBER
        , p_init_msg_lst                IN  VARCHAR2 :=  FND_API.G_FALSE
        , p_commit	                IN  VARCHAR2 :=  FND_API.G_FALSE
        , x_return_status               OUT NOCOPY VARCHAR2
        , x_msg_count                   OUT NOCOPY NUMBER
        , x_msg_data                    OUT NOCOPY VARCHAR2
        , p_wip_entity_id		IN  NUMBER
	, p_source_api			IN  VARCHAR2 DEFAULT null
        , x_shortage_exists		OUT NOCOPY VARCHAR2
        )
IS
     CURSOR get_materials_csr(p_wip_entity_id NUMBER) IS
     SELECT wro.organization_id,
            wro.wip_entity_id,
	    SUM(Get_Open_Qty(NVL(wro.required_quantity,0) ,
	                     eam_material_allocqty_pkg.allocated_quantity(wro.wip_entity_id , wro.operation_seq_num,wro.organization_id,wro.inventory_item_id),
			     NVL(wro.quantity_issued,0))) open_quantity,
            mtlbkfv.concatenated_segments inventory_item,
            wro.inventory_item_id,
            mtlbkfv.lot_control_code,
            mtlbkfv.serial_number_control_code,
            mtlbkfv.revision_qty_control_code
       FROM mtl_system_items_b_kfv mtlbkfv,
            wip_requirement_operations wro
      WHERE wro.inventory_item_id=mtlbkfv.inventory_item_id
        AND wro.organization_id = mtlbkfv.organization_id
        AND wro.wip_entity_id = p_wip_entity_id
        AND NVL(mtlbkfv.stock_enabled_flag,'N')='Y'
   GROUP BY  wro.organization_id,
            wro.wip_entity_id,
	    wro.inventory_item_id,
	    mtlbkfv.concatenated_segments,
            mtlbkfv.lot_control_code,
            mtlbkfv.serial_number_control_code,
            mtlbkfv.revision_qty_control_code;

Line: 97

     SELECT NVL(item_description, description) AS item_description,
	    SUM(Get_Open_Qty(required_quantity, quantity_received, 0)) open_quantity
       FROM eam_direct_item_recs_v
      WHERE wip_entity_id = p_wip_entity_id
      GROUP BY item_description, description;

Line: 104

     SELECT wip_entity_name
       FROM wip_entities
      WHERE wip_entity_id = p_wip_entity_id;

Line: 109

     SELECT meaning
       FROM mfg_lookups
      WHERE lookup_type = 'EAM_YES_NO'
        AND lookup_code = p_lookup_code
	AND enabled_flag = 'Y'
	AND (start_date_active is NULL OR sysdate >= start_date_active)
	AND (end_date_active is NULL OR sysdate <= end_date_active);

Line: 118

     SELECT instance_number
       FROM csi_item_instances cii, wip_discrete_jobs wdj
      WHERE decode(wdj.maintenance_object_type,3, wdj.maintenance_object_id,NULL) = cii.instance_id(+)
        AND wdj.wip_entity_id = p_wip_entity_id;

Line: 245

			-- The update statement will be replaced by call to Work Order API
			-- Waiting for necessary changed in Work Order API to be done
			UPDATE eam_work_order_details
			   SET material_shortage_flag = 1,
			       material_shortage_check_date = sysdate,
			       last_update_date  = sysdate,
			       last_updated_by   = fnd_global.user_id,
			       last_update_login = fnd_global.login_id
			WHERE wip_entity_id = p_wip_entity_id;

Line: 293

			-- The update statement will be replaced by call to Work Order API
			-- Waiting for necessary changed in Work Order API to be done
			UPDATE eam_work_order_details
			   SET material_shortage_flag = 1,
			       material_shortage_check_date = sysdate,
			       last_update_date  = sysdate,
			       last_updated_by   = fnd_global.user_id,
			       last_update_login = fnd_global.login_id
			 WHERE wip_entity_id = p_wip_entity_id;

Line: 342

		UPDATE eam_work_order_details
		   SET material_shortage_flag = 2,
		       material_shortage_check_date = sysdate,
		       last_update_date  = sysdate,
		       last_updated_by   = fnd_global.user_id,
		       last_update_login = fnd_global.login_id
		 WHERE wip_entity_id = p_wip_entity_id;

Line: 370

		UPDATE eam_work_order_details
		   SET material_shortage_flag = null,
		       material_shortage_check_date = sysdate,
		       last_update_date  = sysdate,
		       last_updated_by   = fnd_global.user_id,
		       last_update_login = fnd_global.login_id
		 WHERE wip_entity_id = p_wip_entity_id;

Line: 394

		UPDATE eam_work_order_details
		   SET material_shortage_flag = null,
		       material_shortage_check_date = sysdate,
		       last_update_date  = sysdate,
		       last_updated_by   = fnd_global.user_id,
		       last_update_login = fnd_global.login_id
		 WHERE wip_entity_id = p_wip_entity_id;

Line: 423

		UPDATE eam_work_order_details
		   SET material_shortage_flag = null,
		       material_shortage_check_date = sysdate,
		       last_update_date  = sysdate,
		       last_updated_by   = fnd_global.user_id,
		       last_update_login = fnd_global.login_id
		 WHERE wip_entity_id = p_wip_entity_id;