APPS.EAM_WO_COMP_VALIDATE_PVT SQL Statements

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

Line: 41

	  SELECT wip_entity_name
	    INTO l_wip_entity_name
            FROM wip_entities
           WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;

Line: 47

	   SELECT status_type,shutdown_type
	     INTO l_status_type,l_shutdown_type
             FROM wip_discrete_jobs
            WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;

Line: 221

	SELECT  cii.inventory_item_id
	  FROM  wip_discrete_jobs wdj,csi_item_instances cii
	 WHERE  wdj.wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
	   AND  wdj.maintenance_object_type = 3
	   AND  wdj.maintenance_object_id = cii.instance_id
	 UNION
        SELECT  wdj.maintenance_object_id
	  FROM  wip_discrete_jobs wdj
	 WHERE  wdj.wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
	   AND  wdj.maintenance_object_type = 2;

Line: 240

	  SELECT wip_entity_name
	    INTO l_wip_entity_name
            FROM WIP_ENTITIES
           WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;

Line: 245

	   SELECT maintenance_object_type
	     INTO l_maintenance_object_type
	     FROM wip_discrete_jobs
	    WHERE wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;

Line: 286

		 SELECT NVL(MIN(period_start_date), sysdate+2)
		   INTO l_min_open_period
		   FROM org_acct_periods
		  WHERE organization_id = l_org_id
		    AND open_flag = 'Y';

Line: 357

	      select nvl(max(actual_end_date), sysdate - 200000)
	      into l_max_compl_op_date
	      from eam_op_completion_txns eoct
	      where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
	      --fix for 3543834.added  where clause so that the last completion date will be fetched if the operation is complete
	      and transaction_type=1
	      and transaction_id = (select max(transaction_id)
				  from eam_op_completion_txns
				  where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
					and operation_seq_num = eoct.operation_seq_num
					);

Line: 370

	      select nvl(min(actual_start_date), sysdate + 200000)
	      into l_min_compl_op_date
	      from eam_op_completion_txns eoct
	      where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
	      --fix for 3543834.added  where clause so that the last completion date will be fetched if the operation is complete
	      and transaction_type=1
	      and transaction_id = (select max(transaction_id)
				    from eam_op_completion_txns
				    where wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
					  and operation_seq_num = eoct.operation_seq_num
					 );

Line: 506

				SELECT count(*) into l_count
				  FROM wip_discrete_jobs wdj,
				       mtl_system_items_b msib
				 WHERE wdj.organization_id = msib.organization_id
				   AND wdj.maintenance_object_id = msib.inventory_item_id
				   AND wdj.organization_id = p_eam_wo_comp_rec.organization_id
				   AND wdj.wip_entity_id   = p_eam_wo_comp_rec.wip_entity_id ;

Line: 521

				SELECT count(*) into l_count
				  FROM wip_discrete_jobs wdj,
				       csi_item_instances cii
				 WHERE wdj.organization_id = cii.last_vld_organization_id
				   AND wdj.maintenance_object_id = cii.instance_id
				   AND wdj.organization_id = p_eam_wo_comp_rec.organization_id
				   AND wdj.wip_entity_id   = p_eam_wo_comp_rec.wip_entity_id ;

Line: 532

					   SELECT  cii.serial_number ,
						   msn.current_status
					    INTO   l_serial_number ,
						   l_serial_status
					     FROM  wip_discrete_jobs wdj,
						   csi_item_instances cii,
						   mtl_serial_numbers msn
					    WHERE  wdj.wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
					      AND  wdj.maintenance_object_type = 3
					      AND  wdj.maintenance_object_id = cii.instance_id
					      AND  cii.serial_number = msn.serial_number
					      AND  cii.inventory_item_id = msn.inventory_item_id;

Line: 557

			   SELECT lot_control_code into l_lot_ctrl_code
			     FROM mtl_system_items_b
			    WHERE inventory_item_id = l_inventory_item_id
			      AND organization_id = l_org_id ;

Line: 564

					select restrict_subinventories_code
					  into p_subinv_ctrl
					  from mtl_system_items_kfv
					 where organization_id = l_org_id
					   and inventory_item_id = l_inventory_item_id;

Line: 571

						 select secondary_inventory_name into l_subinv
						 from mtl_secondary_inventories
						 where
						 secondary_inventory_name = p_eam_wo_comp_rec.completion_subinventory
						 and organization_id = l_org_id
						 and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
						 and Asset_inventory = 2;

Line: 579

						 select secondary_inventory_name into l_subinv
						 from mtl_secondary_inventories
						 where
						 secondary_inventory_name = p_eam_wo_comp_rec.completion_subinventory
						 and organization_id = l_org_id
						 and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
						 and Asset_inventory = 2
						 and EXISTS (select secondary_inventory from mtl_item_sub_inventories
											   where secondary_inventory = secondary_inventory_name
											   and  inventory_item_id = l_inventory_item_id
											   and organization_id = l_org_id);

Line: 638

				 select
				    lot_number into l_lot_number
				    from
				    mtl_lot_numbers
				   where
				    inventory_item_id = l_inventory_item_id
					and organization_id = l_org_id;

Line: 800

	      SELECT '1'
		INTO child_job_var
		FROM dual
	       WHERE EXISTS (SELECT '1'
			       FROM wip_discrete_jobs wdj, wip_entities we
			      WHERE wdj.wip_entity_id =  we.wip_entity_id
				AND wdj.parent_wip_entity_id = p_eam_wo_comp_rec.wip_entity_id
				AND wdj.manual_rebuild_flag = 'Y'
				AND wdj.status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
				WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED));

Line: 843

		       SELECT mlu.lookup_code
			 INTO l_reconciliation_code
			 FROM mfg_lookups mlu
			WHERE mlu.lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
			  AND mlu.lookup_code = p_eam_wo_comp_rec.reconciliation_code;

Line: 873

			 SELECT '1'
			   INTO network_child_job_var
			   FROM dual
			  WHERE EXISTS ( SELECT '1'
					   FROM wip_discrete_jobs
					  WHERE wip_entity_id IN
					  (
					 SELECT DISTINCT  child_object_id
					   FROM eam_wo_relationships
					  WHERE parent_relationship_type =1
						START WITH parent_object_id =    p_eam_wo_comp_rec.wip_entity_id AND parent_relationship_type = 1
						CONNECT BY  parent_object_id  = prior child_object_id   AND parent_relationship_type = 1
					 )
				       AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
					WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED, WIP_CONSTANTS.CANCELLED )

				     );

Line: 922

		 SELECT '1'
		    INTO sibling_parent_job_var
		    FROM dual
		 WHERE EXISTS (SELECT '1'
				   FROM wip_discrete_jobs
				 WHERE wip_entity_id IN
				 (
				 SELECT DISTINCT  parent_object_id
					FROM eam_wo_relationships
				  WHERE parent_relationship_type =2 and
					child_object_id  =    p_eam_wo_comp_rec.wip_entity_id
				 )
			       AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
				WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED)
			     );

Line: 966

		SELECT	manual_rebuild_flag,
			parent_wip_entity_id
		  INTO  l_manual_rebuild_flag,
			l_parent_wip_entity_id
		  FROM	WIP_DISCRETE_JOBS
		 WHERE	wip_entity_id = p_eam_wo_comp_rec.wip_entity_id;

Line: 974

			SELECT status_type
			  INTO l_parent_status_type
			  FROM WIP_DISCRETE_JOBS
			 WHERE wip_entity_id = l_parent_wip_entity_id;

Line: 1035

    select 1
      into g_dummy
      from mtl_parameters mp
     where mp.organization_id = p_eam_wo_comp_rec.organization_id;

Line: 1040

    select nvl(hou.date_to,sysdate+1)
      into l_disable_date
      from hr_organization_units hou
      where organization_id =  p_eam_wo_comp_rec.organization_id;

Line: 1078

    select 1
      into g_dummy
      from wip_eam_parameters wep, mtl_parameters mp
     where wep.organization_id = mp.organization_id
       and mp.eam_enabled_flag = 'Y'
       and wep.organization_id = p_eam_wo_comp_rec.organization_id;