APPS.EAM_ASSETNUMBER_PUB SQL Statements

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

Line: 6

PROCEDURE Insert_Asset_Number
( 	p_api_version           	IN	NUMBER				,
  	p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE	,
	p_commit	    		IN  	VARCHAR2 := FND_API.G_FALSE	,
	p_validation_level		IN  	NUMBER	:=
						FND_API.G_VALID_LEVEL_FULL	,
	x_return_status		OUT NOCOPY	VARCHAR2		  	,
	x_msg_count		OUT NOCOPY	NUMBER				,
	x_msg_data		OUT NOCOPY	VARCHAR2			,
	x_object_id		OUT	NOCOPY 	NUMBER,
	p_INVENTORY_ITEM_ID	IN 	NUMBER,
	p_SERIAL_NUMBER		IN	VARCHAR2,
	p_INSTANCE_NUMBER	IN 	VARCHAR2,
	--p_INITIALIZATION_DATE	IN	DATE:=NULL,   -- always use sysdate
	p_CURRENT_STATUS	IN 	NUMBER:=3,
	p_DESCRIPTIVE_TEXT		IN	VARCHAR2:=NULL,
	p_CURRENT_ORGANIZATION_ID 	IN 	NUMBER,
	p_ATTRIBUTE_CATEGORY	IN	VARCHAR2:=NULL,
	p_ATTRIBUTE1		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE2		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE3		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE4		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE5		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE6		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE7		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE8		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE9		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE10		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE11		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE12		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE13		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE14		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE15		IN	VARCHAR2:=NULL,
	P_ATTRIBUTE16                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE17                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE18                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE19                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE20                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE21                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE22                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE23                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE24                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE25                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE26                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE27                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE28                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE29                   VARCHAR2 DEFAULT NULL,
        P_ATTRIBUTE30                   VARCHAR2 DEFAULT NULL,
	--p_STATUS_ID		IN 	NUMBER:=1,
	--p_PREVIOUS_STATUS		IN 	NUMBER:=NULL,
	p_WIP_ACCOUNTING_CLASS_CODE	IN	VARCHAR2:=NULL,
	p_MAINTAINABLE_FLAG		IN	VARCHAR2:=NULL,
	p_OWNING_DEPARTMENT_ID		IN 	NUMBER,
	p_NETWORK_ASSET_FLAG		IN	VARCHAR2:=NULL,
	p_FA_ASSET_ID			IN 	NUMBER:=NULL,
	p_PN_LOCATION_ID		IN 	NUMBER:=NULL,
	p_EAM_LOCATION_ID		IN 	NUMBER:=NULL,
	p_ASSET_CRITICALITY_CODE	IN	VARCHAR2:=NULL,
	p_CATEGORY_ID			IN 	NUMBER:=NULL,
	p_PROD_ORGANIZATION_ID 		IN 	NUMBER:=NULL,
	p_EQUIPMENT_ITEM_ID		IN 	NUMBER:=NULL,
	p_EQP_SERIAL_NUMBER		IN	VARCHAR2:=NULL,
	p_EQUIPMENT_GEN_OBJECT_ID	IN 	NUMBER,
       	p_instantiate_flag              IN      BOOLEAN:=FALSE,
	p_eam_linear_id			IN	NUMBER:=NULL
	,p_active_start_date	        DATE
	,p_active_end_date	        DATE
	,p_location		        NUMBER
	,p_operational_log_flag	  	VARCHAR2
	,p_checkin_status		NUMBER
	,p_supplier_warranty_exp_date   DATE
)
IS
l_api_name			CONSTANT VARCHAR2(30)	:= 'Insert_Asset_Number';

Line: 103

    SAVEPOINT	 Insert_Asset_Number_Pub;

Line: 121

		select eam_item_type,serial_number_control_code
		into l_eam_item_type, l_serial_number_control_code
		from mtl_system_items where inventory_item_id = p_inventory_item_id
		and organization_id = p_CURRENT_ORGANIZATION_ID;

Line: 137

	-- select meaning for capital asset
	select meaning into l_asset_meaning
	from mfg_lookups
	where lookup_type = 'MTL_EAM_ASSET_TYPE'
	and lookup_code=1;

Line: 143

	--select meaning for rebuild asset
	select meaning into l_rebuild_meaning
	from mfg_lookups
	where lookup_type = 'MTL_EAM_ASSET_TYPE'
  	and lookup_code=3;

Line: 264

   	   SELECT count(*) INTO l_count FROM csi_item_instances
   	   WHERE linear_location_id = p_eam_linear_id AND ROWNUM = 1;

Line: 322

		 ,P_LAST_UPDATE_DATE         =>     SYSDATE
		 ,P_LAST_UPDATED_BY          =>     FND_GLOBAL.LOGIN_ID
		 ,P_CREATION_DATE            =>     SYSDATE
		 ,P_CREATED_BY               =>     FND_GLOBAL.USER_ID
		 ,P_LAST_UPDATE_LOGIN        =>     FND_GLOBAL.LOGIN_ID
		 ,p_active_start_date	     =>	  SYSDATE
		 ,p_active_end_date	     =>  NULL
		 ,p_location		     =>  NULL
		 ,p_linear_location_id	     =>	  p_eam_linear_id
		 ,p_operational_log_flag     =>	  p_operational_log_flag
		 ,p_checkin_status	=>	  p_checkin_status
		 ,p_supplier_warranty_exp_date =>   p_supplier_warranty_exp_date
		 ,p_equipment_gen_object_id  =>   p_equipment_gen_object_id
		 ,p_owning_department_id =>  p_owning_department_id
		 ,p_accounting_class_code => p_WIP_ACCOUNTING_CLASS_CODE
		 ,p_area_id		=> P_EAM_LOCATION_ID
		 ,X_OBJECT_ID 		=> l_instance_id
		 ,X_RETURN_STATUS 	=> l_X_RETURN_STATUS
		 ,X_MSG_COUNT 		=> l_X_MSG_COUNT
		 ,X_MSG_DATA 		=> l_X_MSG_DATA
	);

Line: 382

		ROLLBACK TO  Insert_Asset_Number_Pub;

Line: 389

		ROLLBACK TO  Insert_Asset_Number_Pub;

Line: 396

		ROLLBACK TO  Insert_Asset_Number_Pub;

Line: 410

END  Insert_Asset_Number;

Line: 414

PROCEDURE Update_Asset_Number
( 	p_api_version           	IN	NUMBER				,
  	p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE	,
	p_commit	    		IN  	VARCHAR2 := FND_API.G_FALSE	,
	p_validation_level		IN  	NUMBER	:=
						FND_API.G_VALID_LEVEL_FULL	,
	x_return_status		OUT NOCOPY	VARCHAR2		  	,
	x_msg_count		OUT NOCOPY	NUMBER				,
	x_msg_data		OUT NOCOPY	VARCHAR2			,
	--p_GEN_OBJECT_ID		IN  	NUMBER:=NULL,
	p_INVENTORY_ITEM_ID	IN 	NUMBER,
	p_SERIAL_NUMBER		IN	VARCHAR2,
	p_INSTANCE_NUMBER	IN 	VARCHAR2,
	P_INSTANCE_ID		IN 	NUMBER,
	--p_INITIALIZATION_DATE	IN	DATE:=NULL,
	p_CURRENT_STATUS	IN 	NUMBER:=3,
	p_DESCRIPTIVE_TEXT		IN	VARCHAR2:=NULL,
	p_CURRENT_ORGANIZATION_ID 	IN 	NUMBER,
	p_ATTRIBUTE_CATEGORY	IN	VARCHAR2:=NULL,
	p_ATTRIBUTE1		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE2		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE3		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE4		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE5		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE6		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE7		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE8		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE9		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE10		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE11		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE12		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE13		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE14		IN	VARCHAR2:=NULL,
	p_ATTRIBUTE15		IN	VARCHAR2:=NULL,
	P_ATTRIBUTE16                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE17                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE18                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE19                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE20                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE21                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE22                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE23                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE24                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE25                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE26                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE27                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE28                   VARCHAR2 DEFAULT NULL,
	P_ATTRIBUTE29                   VARCHAR2 DEFAULT NULL,
        P_ATTRIBUTE30                   VARCHAR2 DEFAULT NULL,
	--p_STATUS_ID		IN 	NUMBER:=1,
	--p_PREVIOUS_STATUS		IN 	NUMBER:=NULL,
	p_WIP_ACCOUNTING_CLASS_CODE	IN	VARCHAR2:=NULL,
	p_MAINTAINABLE_FLAG		IN	VARCHAR2:=NULL,
	p_OWNING_DEPARTMENT_ID		IN 	NUMBER,
	p_NETWORK_ASSET_FLAG		IN	VARCHAR2:=NULL,
	p_FA_ASSET_ID			IN 	NUMBER:=NULL,
	p_PN_LOCATION_ID		IN 	NUMBER:=NULL,
	p_EAM_LOCATION_ID		IN 	NUMBER:=NULL,
	p_ASSET_CRITICALITY_CODE	IN	VARCHAR2:=NULL,
	p_CATEGORY_ID			IN 	NUMBER:=NULL,
	p_PROD_ORGANIZATION_ID 		IN 	NUMBER:=NULL,
	p_EQUIPMENT_ITEM_ID		IN 	NUMBER:=NULL,
	p_EQP_SERIAL_NUMBER		IN	VARCHAR2:=NULL,
	p_EAM_LINEAR_ID			IN	NUMBER:=NULL
	,P_LOCATION_TYPE_CODE		IN	VARCHAR2:=NULL
	,P_LOCATION_ID			IN	NUMBER:=NULL
	,P_ACTIVE_END_DATE		IN 	DATE:=NULL
	,P_OPERATIONAL_LOG_FLAG	  	IN	VARCHAR2
	,P_CHECKIN_STATUS		IN 	NUMBER
	,P_SUPPLIER_WARRANTY_EXP_DATE	IN	DATE
	,P_EQUIPMENT_GEN_OBJECT_ID	IN	NUMBER
	,P_DISASSOCIATE_FA_FLAG		IN	VARCHAR2
)
IS
l_api_name			CONSTANT VARCHAR2(30)	:= 'Update_Asset_Number';

Line: 505

    SAVEPOINT	Update_Asset_Number_Pub;

Line: 524

		select eam_item_type into l_eam_item_type
		from mtl_system_items where inventory_item_id = p_inventory_item_id
		and organization_id = p_CURRENT_ORGANIZATION_ID;

Line: 533

	-- select meaning for capital asset
	select meaning into l_asset_meaning
	from mfg_lookups
	where lookup_type = 'MTL_EAM_ASSET_TYPE'
	and lookup_code=1;

Line: 539

	--select meaning for rebuild asset
	select meaning into l_rebuild_meaning
	from mfg_lookups
	where lookup_type = 'MTL_EAM_ASSET_TYPE'
  	and lookup_code=3;

Line: 546

		select last_vld_organization_id,inventory_item_id,serial_number
		into l_organization_id,l_inventory_item_id,l_serial_number
		from csi_item_instances
		where instance_id = p_instance_id;

Line: 551

		select last_vld_organization_id,inventory_item_id,serial_number
		into l_organization_id,l_inventory_item_id,l_serial_number
		from csi_item_instances
		where instance_number = p_instance_number;

Line: 558

		select instance_id,last_vld_organization_id
		into l_instance_id,l_organization_id
		from csi_item_instances
		where serial_number = p_serial_number
		and inventory_item_id = p_inventory_item_id;

Line: 630

   	   SELECT count(*) INTO l_count FROM csi_item_instances
	    WHERE linear_location_id = p_eam_linear_id
	      AND instance_id <> l_instance_id AND ROWNUM = 1;

Line: 644

			select current_status
			into l_old_current_status
			from mtl_serial_numbers
			where inventory_item_id = l_inventory_item_id
			and serial_number = l_serial_number
			and rownum <= 1;

Line: 663

	EAM_ASSET_NUMBER_PVT.update_asset
	(
		P_API_VERSION                => 1.0
		,P_INSTANCE_ID     	     => l_instance_id
		,P_INSTANCE_DESCRIPTION      => P_DESCRIPTIVE_TEXT
		,P_INVENTORY_ITEM_ID	     => p_inventory_item_id
		,P_SERIAL_NUMBER	     => p_serial_number
		,P_ORGANIZATION_ID	     => l_organization_id
		,P_CATEGORY_ID               => P_CATEGORY_ID
		,P_PN_LOCATION_ID            => P_PN_LOCATION_ID
		,P_FA_ASSET_ID               => P_FA_ASSET_ID
		,P_ASSET_CRITICALITY_CODE    => P_ASSET_CRITICALITY_CODE
		,P_MAINTAINABLE_FLAG         => P_MAINTAINABLE_FLAG
		,P_NETWORK_ASSET_FLAG        => P_NETWORK_ASSET_FLAG
		,P_ATTRIBUTE_CATEGORY        => P_ATTRIBUTE_CATEGORY
		,P_ATTRIBUTE1                =>    P_ATTRIBUTE1
		,P_ATTRIBUTE2                =>    P_ATTRIBUTE2
		,P_ATTRIBUTE3                =>    P_ATTRIBUTE3
		,P_ATTRIBUTE4                =>    P_ATTRIBUTE4
		,P_ATTRIBUTE5                =>    P_ATTRIBUTE5
		,P_ATTRIBUTE6                =>    P_ATTRIBUTE6
		,P_ATTRIBUTE7                =>    P_ATTRIBUTE7
		,P_ATTRIBUTE8                =>    P_ATTRIBUTE8
		,P_ATTRIBUTE9                =>    P_ATTRIBUTE9
		,P_ATTRIBUTE10               =>    P_ATTRIBUTE10
		,P_ATTRIBUTE11               =>    P_ATTRIBUTE11
		,P_ATTRIBUTE12               =>    P_ATTRIBUTE12
		,P_ATTRIBUTE13               =>    P_ATTRIBUTE13
		,P_ATTRIBUTE14               =>    P_ATTRIBUTE14
		,P_ATTRIBUTE15               =>    P_ATTRIBUTE15
		,P_ATTRIBUTE16               =>    P_ATTRIBUTE16
		,P_ATTRIBUTE17               =>    P_ATTRIBUTE17
		,P_ATTRIBUTE18               =>    P_ATTRIBUTE18
		,P_ATTRIBUTE19               =>    P_ATTRIBUTE19
		,P_ATTRIBUTE20               =>    P_ATTRIBUTE20
		,P_ATTRIBUTE21               =>    P_ATTRIBUTE21
		,P_ATTRIBUTE22               =>    P_ATTRIBUTE22
		,P_ATTRIBUTE23               =>    P_ATTRIBUTE23
		,P_ATTRIBUTE24               =>    P_ATTRIBUTE24
		,P_ATTRIBUTE25               =>    P_ATTRIBUTE25
		,P_ATTRIBUTE26              =>     P_ATTRIBUTE26
		,P_ATTRIBUTE27              =>     P_ATTRIBUTE27
		,P_ATTRIBUTE28              =>     P_ATTRIBUTE28
		,P_ATTRIBUTE29              =>     P_ATTRIBUTE29
		,P_ATTRIBUTE30              =>     P_ATTRIBUTE30
		,P_LAST_UPDATE_DATE         =>     SYSDATE
		,P_LAST_UPDATED_BY          =>     FND_GLOBAL.LOGIN_ID
		,P_LAST_UPDATE_LOGIN        =>     FND_GLOBAL.LOGIN_ID
		,P_FROM_PUBLIC_API	     =>  'Y'
		,P_INSTANCE_NUMBER	     =>	  P_INSTANCE_NUMBER
		,P_LOCATION_TYPE_CODE	     => P_LOCATION_TYPE_CODE
		,P_LOCATION_ID		     => P_LOCATION_ID
		,p_active_end_date	     => P_ACTIVE_END_DATE
		,p_linear_location_id	     => P_EAM_LINEAR_ID
		,p_operational_log_flag	     => P_OPERATIONAL_LOG_FLAG
		,p_checkin_status	     => P_CHECKIN_STATUS
		,p_supplier_warranty_exp_date  => P_SUPPLIER_WARRANTY_EXP_DATE
		,p_equipment_gen_object_id   	=> p_equipment_gen_object_id
		,p_owning_department_id	     => p_owning_department_id
		,p_accounting_class_code     => p_wip_accounting_class_code
		,p_area_id		     => p_eam_location_id
		,p_disassociate_fa_flag      => p_disassociate_fa_flag
		,X_RETURN_STATUS             => x_return_status
		,X_MSG_COUNT                 => x_msg_count
		,X_MSG_DATA                  => x_msg_data
  	);

Line: 743

		ROLLBACK TO Update_Asset_Number_Pub;

Line: 750

		ROLLBACK TO Update_Asset_Number_Pub;

Line: 757

		ROLLBACK TO Update_Asset_Number_Pub;

Line: 771

END Update_Asset_Number;

Line: 834

	select eam_item_type
	into l_eam_item_type
        from mtl_system_items
        where inventory_item_id = p_INVENTORY_ITEM_ID
  	and organization_id = p_CURRENT_ORGANIZATION_ID;

Line: 841

            SELECT cii.instance_id, nvl(cii.maintainable_flag, 'Y'), nvl(cii.network_asset_flag, 'N'), mp.maint_organization_id
	      INTO l_instance_id, l_old_maint_flag, l_old_network_asset_flag, l_org
	      FROM csi_item_instances cii, mtl_parameters mp
             WHERE cii.serial_number = p_serial_number
	       AND cii.inventory_item_id = p_inventory_item_id
	       AND cii.last_vld_organization_id = p_current_organization_id
	       AND cii.last_vld_organization_id = mp.organization_id;

Line: 868

              SELECT 1 INTO l_count
  	        FROM DUAL
               WHERE EXISTS
                     (SELECT wdj.wip_entity_id
                        FROM wip_discrete_jobs wdj
                       WHERE wdj.status_type not in (4, 5, 7, 12)
                         AND wdj.maintenance_object_id = l_instance_id
                         AND wdj.maintenance_object_type = 3
                         AND wdj.organization_id = l_org)
                  OR EXISTS
                     (SELECT wewr.asset_number
                        FROM wip_eam_work_requests wewr
                       WHERE wewr.work_request_status_id not in (5, 6)
                         AND wewr.organization_id = l_org
                         AND wewr.maintenance_object_id = l_instance_id
                         AND wewr.maintenance_object_type = 3);

Line: 993

	SELECT  count(*) into l_count
        FROM  MTL_ITEM_CATEGORIES
        WHERE category_id = p_category_id
        AND inventory_item_id = p_inventory_item_id
        AND organization_id =  p_current_organization_id
        AND category_set_id = l_category_set_id;

Line: 1013

		select count(*) into l_count
		from fnd_product_installations
		where application_id=140;

Line: 1023

          		SELECT  count(*) into l_count
          		FROM  FA_ADDITIONS_B
          		WHERE asset_id = p_fa_asset_id;

Line: 1042

                	select count(*) into l_count
                	from fnd_product_installations
                	where application_id=240;

Line: 1052

                	        SELECT  count(*) into l_count
                	        FROM pn_locations_all
                	        WHERE location_id= p_pn_location_id;

Line: 1098

				select current_organization_id, inventory_item_id, serial_number
				into l_prod_organization_id, l_prod_inventory_item_id, l_prod_serial_number
				from mtl_serial_numbers
				where gen_object_id = p_equipment_object_id;

Line: 1114

	 		select count(*) into l_count
			from mtl_parameters
			where organization_id=l_prod_organization_id
			and maint_organization_id=p_current_organization_id;

Line: 1128

             	SELECT count(*) INTO l_count
             	FROM   MTL_SYSTEM_ITEMS_B
             	WHERE  inventory_item_id = l_prod_inventory_item_id
             	AND    organization_id = l_prod_organization_id;

Line: 1140

             	SELECT equipment_type INTO l_prod_equipment_type
             	FROM   MTL_SYSTEM_ITEMS_B
             	WHERE  inventory_item_id = l_prod_inventory_item_id
             	AND    organization_id = l_prod_organization_id;

Line: 1162

             	select count(*) into l_count
             	from mtl_serial_numbers
             	where inventory_item_id = l_prod_inventory_item_id
             	and current_organization_id = l_prod_organization_id
             	and serial_number = l_prod_serial_number;