APPS.EAM_ITEM_ACTIVITIES_PUB SQL Statements

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

Line: 57

	INSERT into temp_isetup_api(msg,sr_no)
	VALUES (info,g_sr_no);

Line: 72

	SELECT count(*) INTO L_status
	FROM MTL_PARAMETERS
	WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
	AND NVL(EAM_ENABLED_FLAG, 'N') = 'Y';

Line: 99

			SELECT count(*) INTO l_count_rec
			FROM csi_item_instances cii
			WHERE cii.serial_number = p_asset_number
			AND cii.instance_id = p_maintenance_object_id
			AND cii.inventory_item_id = p_asset_group_id;

Line: 109

			SELECT count(*) INTO l_count_rec
			FROM mtl_system_items msi, mtl_parameters mp
			WHERE msi.inventory_item_id = p_asset_group_id
			AND msi.organization_id = mp.organization_id
			AND mp.maint_organization_id = p_organization_id;

Line: 147

        SELECT count(*) INTO l_count
	FROM   mfg_lookups
	WHERE  lookup_type = P_LOOKUP_TYPE
	  AND  lookup_code= P_LOOKUP_CODE;

Line: 171

        SELECT count(*) INTO l_count
        from wip_accounting_classes
        where class_code = P_WIP_ACNT_CLASS
        and class_type = 6
        and organization_id = P_ORGANIZATION_ID;

Line: 211

		select count(cii.instance_id) into l_count
		from csi_item_instances cii
		where cii.instance_id=p_object_id;

Line: 215

		select count(msi.inventory_item_id) into l_count
		from mtl_system_items msi, mtl_parameters mp
		where msi.inventory_item_id = p_object_id
		  and msi.organization_id = mp.organization_id
		  and mp.maint_organization_id = p_organization_id
		--and eam_item_type = p_eam_item_type
		;

Line: 246

	select count(msi.inventory_item_id) into l_count
	from mtl_system_items msi, mtl_parameters mp
	where msi.inventory_item_id=p_inventory_item_id
	and msi.organization_id = mp.organization_id
	and mp.maint_organization_id=p_organization_id
	and eam_item_type IN (1, 3);

Line: 284

        select count(cii.inventory_item_id) into l_count
        from csi_item_instances cii
	where cii.serial_number = p_serial_number;

Line: 367

	SELECT activity_association_id INTO x_act_assoc_id
	FROM mtl_eam_asset_activities
	WHERE maintenance_object_id = p_maintenance_object_id
	AND asset_activity_id = P_asset_activity_id
	AND maintenance_object_type = p_maintenance_object_type
	AND NVL(tmpl_flag, 'N') = NVL(p_tmpl_flag, 'N');

Line: 386

PROCEDURE INSERT_ITEM_ACTIVITIES
(
        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_ASSET_ACTIVITY_ID		IN	NUMBER	,
	/*P_INVENTORY_ITEM_ID		IN	NUMBER	,*/
	P_INVENTORY_ITEM_ID		IN	NUMBER	default null,
	P_ORGANIZATION_ID		IN	NUMBER	,
	P_OWNINGDEPARTMENT_ID		IN	NUMBER	default null,
	P_MAINTENANCE_OBJECT_ID		IN	NUMBER default null,
	P_CREATION_ORGANIZATION_ID	IN	NUMBER 	default null,
	P_START_DATE_ACTIVE		IN	DATE default null	,
	P_END_DATE_ACTIVE		IN	DATE default null	,
	P_PRIORITY_CODE			IN	VARCHAR2 default null	,
	P_ACTIVITY_CAUSE_CODE		IN	VARCHAR2 default null,
	P_ACTIVITY_TYPE_CODE		IN	VARCHAR2 default null	,
	P_SHUTDOWN_TYPE_CODE		IN	VARCHAR2 default null	,
	P_MAINTENANCE_OBJECT_TYPE	IN	NUMBER default null	,
	P_TMPL_FLAG			IN	VARCHAR2 default null	,
	P_CLASS_CODE			IN	VARCHAR2 default null,
	P_ACTIVITY_SOURCE_CODE		IN	VARCHAR2 default null,
	P_SERIAL_NUMBER			IN	VARCHAR2 default null	,
	P_ATTRIBUTE_CATEGORY		IN	VARCHAR2 default null	,
	P_ATTRIBUTE1			IN	VARCHAR2 default null	,
	P_ATTRIBUTE2			IN	VARCHAR2 default null	,
	P_ATTRIBUTE3			IN	VARCHAR2 default null	,
	P_ATTRIBUTE4			IN	VARCHAR2 default null	,
	P_ATTRIBUTE5			IN	VARCHAR2 default null	,
	P_ATTRIBUTE6			IN	VARCHAR2 default null	,
	P_ATTRIBUTE7			IN	VARCHAR2 default null	,
	P_ATTRIBUTE8			IN	VARCHAR2 default null	,
	P_ATTRIBUTE9			IN	VARCHAR2 default null	,
	P_ATTRIBUTE10			IN	VARCHAR2 default null	,
	P_ATTRIBUTE11			IN	VARCHAR2 default null	,
	P_ATTRIBUTE12			IN	VARCHAR2 default null	,
	P_ATTRIBUTE13			IN	VARCHAR2 default null	,
	P_ATTRIBUTE14			IN	VARCHAR2 default null	,
	P_ATTRIBUTE15			IN	VARCHAR2 default null	,
	P_TAGGING_REQUIRED_FLAG		IN	VARCHAR2 default null	,
	P_LAST_SERVICE_START_DATE	IN	DATE default null	,
	P_LAST_SERVICE_END_DATE		IN	DATE default null	,
	P_PREV_SERVICE_START_DATE	IN	DATE default null	,
	P_PREV_SERVICE_END_DATE		IN	DATE default null	,
	P_LAST_SCHEDULED_START_DATE	IN	DATE default null	,
        P_LAST_SCHEDULED_END_DATE	IN	DATE default null	,
	P_PREV_SCHEDULED_START_DATE	IN	DATE default null	,
        P_PREV_SCHEDULED_END_DATE	IN	DATE default null	,
	P_WIP_ENTITY_ID                 IN      NUMBER default null     ,
	P_SOURCE_TMPL_ID		IN	NUMBER default null	,
	p_pm_last_service_tbl           IN      EAM_PM_LAST_SERVICE_PUB.pm_last_service_tbl

)
IS
	l_api_name			CONSTANT VARCHAR2(30)	:= 'APIname';

Line: 473

    SAVEPOINT	INSERT_ITEM_ACTIVITIES;

Line: 534

	   select eam_item_type into l_item_type
	   from mtl_system_items
	   where inventory_item_id = p_asset_activity_id
	   and organization_id = nvl(p_organization_id, organization_id)
	   AND rownum = 1;

Line: 718

		SELECT msi.eam_item_type , msi.serial_number_control_code
		INTO l_item_type , l_ser_num_ctrl_cd
		FROM mtl_system_items_b msi, mtl_parameters mp
		WHERE msi.organization_id = mp.organization_id
		  AND mp.maint_organization_id = nvl(l_org_id, mp.maint_organization_id)
		  AND msi.inventory_item_id = l_asset_group_id
		  AND rownum = 1;

Line: 760

	   select count(*) into l_item_id
	   from mtl_eam_asset_activities
	   where activity_association_id = p_source_tmpl_id
	   and asset_activity_id = p_asset_activity_id
	   and inventory_item_id = l_asset_group_id
	   and tmpl_flag = 'Y';

Line: 775

	  select MTL_EAM_ASSET_ACTIVITIES_S.NEXTVAL into l_actv_assoc_id from dual;

Line: 776

          INSERT INTO mtl_eam_asset_activities
          (
		ACTIVITY_ASSOCIATION_ID ,
		ASSET_ACTIVITY_ID	,
		MAINTENANCE_OBJECT_ID	,
		START_DATE_ACTIVE	,
		END_DATE_ACTIVE	,
		PRIORITY_CODE	,
		MAINTENANCE_OBJECT_TYPE	,
		TMPL_FLAG	,
		ATTRIBUTE_CATEGORY	,
		ATTRIBUTE1	,
		ATTRIBUTE2	,
		ATTRIBUTE3	,
		ATTRIBUTE4	,
		ATTRIBUTE5	,
		ATTRIBUTE6	,
		ATTRIBUTE7	,
		ATTRIBUTE8	,
		ATTRIBUTE9	,
		ATTRIBUTE10	,
		ATTRIBUTE11	,
		ATTRIBUTE12	,
		ATTRIBUTE13	,
		ATTRIBUTE14	,
		ATTRIBUTE15	,
		LAST_SERVICE_START_DATE	,
		LAST_SERVICE_END_DATE	,
		PREV_SERVICE_START_DATE	,
		PREV_SERVICE_END_DATE	,
		LAST_SCHEDULED_START_DATE	,
		LAST_SCHEDULED_END_DATE	,
		PREV_SCHEDULED_START_DATE	,
		PREV_SCHEDULED_END_DATE	,
		WIP_ENTITY_ID,
		SOURCE_TMPL_ID		,
		CREATED_BY           ,
		CREATION_DATE       ,
		LAST_UPDATE_LOGIN  ,
		LAST_UPDATE_DATE  ,
		LAST_UPDATED_BY
	  )
	  VALUES
	  (
		l_actv_assoc_id,
		P_ASSET_ACTIVITY_ID	,
		l_maintenance_object_id,
		P_START_DATE_ACTIVE	,
		P_END_DATE_ACTIVE	,
		P_PRIORITY_CODE	,
		/*P_MAINTENANCE_OBJECT_TYPE	,*/
		l_maintenance_object_type,
		nvl(P_TMPL_FLAG,'N')	,
		P_ATTRIBUTE_CATEGORY	,
		P_ATTRIBUTE1	,
		P_ATTRIBUTE2	,
		P_ATTRIBUTE3	,
		P_ATTRIBUTE4	,
		P_ATTRIBUTE5	,
		P_ATTRIBUTE6	,
		P_ATTRIBUTE7	,
		P_ATTRIBUTE8	,
		P_ATTRIBUTE9	,
		P_ATTRIBUTE10	,
		P_ATTRIBUTE11	,
		P_ATTRIBUTE12	,
		P_ATTRIBUTE13	,
		P_ATTRIBUTE14	,
		P_ATTRIBUTE15	,
		P_LAST_SERVICE_START_DATE	,
		P_LAST_SERVICE_END_DATE	,
		P_PREV_SERVICE_START_DATE	,
		P_PREV_SERVICE_END_DATE	,
		P_LAST_SCHEDULED_START_DATE	,
		P_LAST_SCHEDULED_END_DATE	,
		P_PREV_SCHEDULED_START_DATE	,
		P_PREV_SCHEDULED_END_DATE	,
		P_WIP_ENTITY_ID,
		P_SOURCE_TMPL_ID	,
		fnd_global.user_id,
		sysdate,
		fnd_global.login_id,
		sysdate    ,
		fnd_global.user_id
	  );

Line: 871

         eam_org_maint_defaults_pvt.update_insert_row
  	 (
	      p_api_version           => 1.0
             ,p_commit                => p_commit
	     ,p_object_type           => l_object_type
	     ,p_object_id             => l_actv_assoc_id
	     ,p_organization_id       => p_organization_Id
	     ,p_owning_department_id  => p_owningdepartment_id
	     ,p_accounting_class_code => p_class_code
	     ,p_activity_cause_code   => p_activity_cause_code
	     ,p_activity_type_code    => p_activity_type_code
	     ,p_activity_source_code  => p_activity_source_code
	     ,p_shutdown_type_code    => p_shutdown_type_code
	     ,p_tagging_required_flag => p_tagging_required_flag
	     ,x_return_status         => x_return_status
	     ,x_msg_count             => x_msg_count
	     ,x_msg_data              => x_msg_data
	 );

Line: 933

		ROLLBACK TO INSERT_ITEM_ACTIVITIES;

Line: 940

		ROLLBACK TO INSERT_ITEM_ACTIVITIES;

Line: 947

		ROLLBACK TO INSERT_ITEM_ACTIVITIES;

Line: 961

END INSERT_ITEM_ACTIVITIES;

Line: 964

PROCEDURE update_item_activities
(
        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_ACTIVITY_ASSOCIATION_ID	IN	NUMBER	,
	P_ASSET_ACTIVITY_ID		IN	NUMBER	,
	P_INVENTORY_ITEM_ID		IN	NUMBER	default null,
	P_ORGANIZATION_ID		IN	NUMBER	,
	P_OWNINGDEPARTMENT_ID		IN	NUMBER	default null,
	P_MAINTENANCE_OBJECT_ID		IN	NUMBER default null,
	P_CREATION_ORGANIZATION_ID	IN	NUMBER default null,
	P_START_DATE_ACTIVE		IN	DATE default null	,
	P_END_DATE_ACTIVE		IN	DATE default null	,
	P_PRIORITY_CODE			IN	VARCHAR2 default null	,
	P_ACTIVITY_CAUSE_CODE		IN	VARCHAR2 default null,
	P_ACTIVITY_TYPE_CODE		IN	VARCHAR2 default null	,
	P_SHUTDOWN_TYPE_CODE		IN	VARCHAR2 default null	,
	P_MAINTENANCE_OBJECT_TYPE	IN	NUMBER default null	,
	P_TMPL_FLAG			IN	VARCHAR2 default null	,
	P_CLASS_CODE			IN	VARCHAR2 default null,
	P_ACTIVITY_SOURCE_CODE		IN	VARCHAR2 default null,
	P_SERIAL_NUMBER			IN	VARCHAR2 default null	,
	P_ATTRIBUTE_CATEGORY		IN	VARCHAR2 default null	,
	P_ATTRIBUTE1			IN	VARCHAR2 default null	,
	P_ATTRIBUTE2			IN	VARCHAR2 default null	,
	P_ATTRIBUTE3			IN	VARCHAR2 default null	,
	P_ATTRIBUTE4			IN	VARCHAR2 default null	,
	P_ATTRIBUTE5			IN	VARCHAR2 default null	,
	P_ATTRIBUTE6			IN	VARCHAR2 default null	,
	P_ATTRIBUTE7			IN	VARCHAR2 default null	,
	P_ATTRIBUTE8			IN	VARCHAR2 default null	,
	P_ATTRIBUTE9			IN	VARCHAR2 default null	,
	P_ATTRIBUTE10			IN	VARCHAR2 default null	,
	P_ATTRIBUTE11			IN	VARCHAR2 default null	,
	P_ATTRIBUTE12			IN	VARCHAR2 default null	,
	P_ATTRIBUTE13			IN	VARCHAR2 default null	,
	P_ATTRIBUTE14			IN	VARCHAR2 default null	,
	P_ATTRIBUTE15			IN	VARCHAR2 default null	,
	P_TAGGING_REQUIRED_FLAG		IN	VARCHAR2 default null	,
	P_LAST_SERVICE_START_DATE	IN	DATE default null	,
	P_LAST_SERVICE_END_DATE		IN	DATE default null	,
	P_PREV_SERVICE_START_DATE	IN	DATE default null	,
	P_PREV_SERVICE_END_DATE		IN	DATE default null	,
	P_LAST_SCHEDULED_START_DATE	IN	DATE default null	,
	P_LAST_SCHEDULED_END_DATE		IN	DATE default null	,
	P_PREV_SCHEDULED_START_DATE	IN	DATE default null	,
	P_PREV_SCHEDULED_END_DATE		IN	DATE default null	,
	P_WIP_ENTITY_ID                 IN      NUMBER default null,
	P_SOURCE_TMPL_ID		IN	NUMBER default null	,

	p_pm_last_service_tbl           IN      EAM_PM_LAST_SERVICE_PUB.pm_last_service_tbl
)
IS
	l_api_name			CONSTANT VARCHAR2(30)	:= 'APIname';

Line: 1051

    SAVEPOINT UPDATE_ITEM_ACTIVITIES;

Line: 1114

	   select count(*) into l_cnt
	   from mtl_eam_asset_activities
	   where asset_activity_id = p_asset_activity_id
	   and activity_association_id = p_activity_association_id;

Line: 1297

		SELECT msi.eam_item_type , msi.serial_number_control_code
		INTO l_item_type , l_ser_num_ctrl_cd
		FROM mtl_system_items_b msi, mtl_parameters mp
		WHERE msi.organization_id = mp.organization_id
		  AND mp.maint_organization_id = nvl(l_org_id, mp.maint_organization_id)
		  AND msi.inventory_item_id = l_asset_group_id
		  AND rownum = 1;

Line: 1343

        UPDATE mtl_eam_asset_activities
        SET
		START_DATE_ACTIVE	 =	P_START_DATE_ACTIVE	,
		END_DATE_ACTIVE	  	 =	P_END_DATE_ACTIVE	,
		PRIORITY_CODE	 	 =	P_PRIORITY_CODE	,
		ATTRIBUTE_CATEGORY	 =	P_ATTRIBUTE_CATEGORY	,
		ATTRIBUTE1	 	 =	P_ATTRIBUTE1	,
		ATTRIBUTE2	  	 =	P_ATTRIBUTE2	,
		ATTRIBUTE3	  	 =	P_ATTRIBUTE3	,
		ATTRIBUTE4	 	 =	P_ATTRIBUTE4	,
		ATTRIBUTE5	 	 =	P_ATTRIBUTE5	,
		ATTRIBUTE6	 	 =	P_ATTRIBUTE6	,
		ATTRIBUTE7	 	 =	P_ATTRIBUTE7	,
		ATTRIBUTE8	 	 =	P_ATTRIBUTE8	,
		ATTRIBUTE9	 	 =	P_ATTRIBUTE9	,
		ATTRIBUTE10	 	 =	P_ATTRIBUTE10	,
		ATTRIBUTE11	 	 =	P_ATTRIBUTE11	,
		ATTRIBUTE12	 	 =	P_ATTRIBUTE12	,
		ATTRIBUTE13	 	 =	P_ATTRIBUTE13	,
		ATTRIBUTE14	 	 =	P_ATTRIBUTE14	,
		ATTRIBUTE15	 	 =	P_ATTRIBUTE15	,
		LAST_SERVICE_START_DATE	 =	P_LAST_SERVICE_START_DATE	,
		LAST_SERVICE_END_DATE	 =	P_LAST_SERVICE_END_DATE	,
		PREV_SERVICE_START_DATE	 =	P_PREV_SERVICE_START_DATE	,
		PREV_SERVICE_END_DATE	 =	P_PREV_SERVICE_END_DATE	,
		LAST_SCHEDULED_START_DATE	 =	P_LAST_SCHEDULED_START_DATE	,
		LAST_SCHEDULED_END_DATE	 =	P_LAST_SCHEDULED_END_DATE	,
		PREV_SCHEDULED_START_DATE	 =	P_PREV_SCHEDULED_START_DATE	,
		PREV_SCHEDULED_END_DATE	 =	P_PREV_SCHEDULED_END_DATE	,
		WIP_ENTITY_ID            =      P_WIP_ENTITY_ID,
		LAST_UPDATE_LOGIN	 =	fnd_global.login_id	,
		LAST_UPDATE_DATE	 =	sysdate	,
		LAST_UPDATED_BY		 =	fnd_global.user_id

	WHERE ACTIVITY_ASSOCIATION_ID = P_ACTIVITY_ASSOCIATION_ID;

Line: 1387

	  eam_org_maint_defaults_pvt.update_insert_row
	    (
	      p_api_version           => 1.0
             ,p_commit                => p_commit
	     ,p_object_type           => l_object_type
	     ,p_object_id             => p_activity_association_id
	     ,p_organization_id       => p_organization_Id
	     ,p_owning_department_id  => p_owningdepartment_id
	     ,p_accounting_class_code => p_class_code
	     ,p_activity_cause_code   => p_activity_cause_code
	     ,p_activity_type_code    => p_activity_type_code
	     ,p_activity_source_code  => p_activity_source_code
	     ,p_shutdown_type_code    => p_shutdown_type_code
	     ,p_tagging_required_flag => p_tagging_required_flag
	     ,x_return_status         => x_return_status
	     ,x_msg_count             => x_msg_count
	     ,x_msg_data              => x_msg_data
	    );

Line: 1451

		ROLLBACK TO UPDATE_ITEM_ACTIVITIES;

Line: 1458

		ROLLBACK TO UPDATE_ITEM_ACTIVITIES;

Line: 1465

		ROLLBACK TO UPDATE_ITEM_ACTIVITIES;

Line: 1479

END update_item_activities;