APPS.EAM_SETNAME_PUB SQL Statements

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

Line: 14

This procedure inserts a record in the eam_pm_set_names table
--      Parameters      :
--      IN              :       P_API_VERSION	IN NUMBER	REQUIRED
--                              P_INIT_MSG_LIST IN VARCHAR2	OPTIONAL
--                                      DEFAULT = FND_API.G_FALSE
--                              P_COMMIT	IN VARCHAR2	OPTIONAL
--                                      DEFAULT = FND_API.G_FALSE
--                              P_VALIDATION_LEVEL IN NUMBER	OPTIONAL
--                                      DEFAULT = FND_API.G_VALID_LEVEL_FULL
--				p_set_name              IN    varchar2 ,
--				p_description	      IN    varchar2 default null,
--				p_end_date	      IN    date 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_end_date_val_req      IN    BOOLEAN  default true ,
--
--      OUT             :       x_return_status    OUT NOCOPY    VARCHAR2(1)
--                              x_msg_count        OUT NOCOPY    NUMBER
--                              x_msg_data         OUT NOCOPY    VARCHAR2 (2000)
--				x_new_set_name_id	OUT	NOCOPY	NUMBER
--      Version :       Current version: 1.0
--                      Initial version: 1.0
--
--      NOTE: p_end_date_validate flag will be false in case of migration, meaning no end date validation required for
--		migration. If the flag is true, only in that case the Validate_FutureEndDate function will be called.
*/

PROCEDURE Insert_PMSetName
(
	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_set_name              IN    varchar2 ,
	p_description	      IN    varchar2 default null,
	p_end_date	      IN    date 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_organization_id       IN    number default null,
	p_local_flag	        IN    VARCHAR2 default 'N' ,
	x_new_set_name_id	OUT	NOCOPY	NUMBER ,
	--p_end_date_val_req      IN    BOOLEAN  default true
	p_end_date_val_req      IN    varchar2  default 'true'
)
IS
l_api_name			CONSTANT VARCHAR2(30)	:='insert set name';

Line: 99

	SAVEPOINT Insert_PMSetName_PUB;

Line: 183

	SELECT EAM_PM_SET_NAMES_S.NEXTVAL INTO L_SET_NAME_ID FROM DUAL;

Line: 187

	INSERT INTO eam_pm_set_names
	(
		set_name_id           ,
		set_name              ,
		description           ,
		end_date              ,
		last_update_date      ,
		last_updated_by       ,
		creation_date         ,
		created_by            ,
		last_update_login     ,
		attribute_category    ,
		attribute1            ,
		attribute2            ,
		attribute3            ,
		attribute4            ,
		attribute5            ,
		attribute6            ,
		attribute7            ,
		attribute8            ,
		attribute9            ,
		attribute10           ,
		attribute11           ,
		attribute12           ,
		attribute13           ,
		attribute14           ,
		attribute15	      ,
		owning_organization_id,
		local_flag
	)
	VALUES
	(
		l_set_name_id		,
		p_set_name		 ,
		p_description	    	,
		p_end_date		,
		SYSDATE			,
		FND_GLOBAL.USER_ID	,
	        SYSDATE			,
		FND_GLOBAL.LOGIN_ID	,
		FND_GLOBAL.USER_ID	,
		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_organization_id   ,
		p_local_flag
	);

Line: 261

		ROLLBACK TO Insert_PMSetName_PUB;

Line: 270

		ROLLBACK TO Insert_PMSetName_PUB;

Line: 279

		ROLLBACK TO Insert_PMSetName_PUB;

Line: 296

END Insert_PMSetName;

Line: 300

This procedure updates a record in the eam_pm_set_names table
--      Parameters      :
--      IN              :       P_API_VERSION	IN NUMBER	REQUIRED
--                              P_INIT_MSG_LIST IN VARCHAR2	OPTIONAL
--                                      DEFAULT = FND_API.G_FALSE
--                              P_COMMIT	IN VARCHAR2	OPTIONAL
--                                      DEFAULT = FND_API.G_FALSE
--                              P_VALIDATION_LEVEL IN NUMBER	OPTIONAL
--                                      DEFAULT = FND_API.G_VALID_LEVEL_FULL
--
--
--      OUT             :       x_return_status    OUT NOCOPY    VARCHAR2(1)
--                              x_msg_count        OUT NOCOPY    NUMBER
--                              x_msg_data         OUT NOCOPY    VARCHAR2 (2000)
--      Version :       Current version: 1.0
--                      Initial version: 1.0
--
--      NOTE: p_end_date_validate flag will be false in case of migration, meaning no end date validation required for
--		migration. If the flag is true, only in that case the Validate_FutureEndDate function will be called.
*/

PROCEDURE Update_PMSetName
(
	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_set_name_id          IN    NUMBER   ,
	p_set_name             IN    VARCHAR2 ,
	p_description	     IN    VARCHAR2 default null,
	p_end_date	     IN    DATE 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_organization_id       IN    number default null,
	p_local_flag	        IN    VARCHAR2 default 'N',
	p_end_date_val_req      IN    varchar2  default 'true'
)
IS

l_api_name			CONSTANT VARCHAR2(30)	:='update set name';

Line: 372

	SAVEPOINT Update_PMSetName_PUB;

Line: 398

		select set_name_id into l_set_name_id
		from eam_pm_set_names
		where set_name = p_set_name;

Line: 459

 	       SELECT end_date, local_flag
 	         INTO l_end_date, l_local_flag
 	         FROM eam_pm_set_names
 	        WHERE set_name_id = l_set_name_id;

Line: 502

				select 'Y' into l_exists from dual
				where exists
				(
					select eps.*
					from eam_pm_schedulings eps, csi_item_instances cii, mtl_parameters mp
					where eps.set_name_id = l_set_name_id
					and eps.maintenance_object_id = cii.instance_id
					and cii.last_vld_organization_id = mp.organization_id
					and mp.maint_organization_id <> p_organization_id
				);

Line: 530

	UPDATE
		eam_pm_set_names
	SET
		set_name         =  		p_set_name          		,
		description       = 		p_description	    		,
		end_date           =		p_end_date	    		,

		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			,

		owning_organization_id =	p_organization_id		,
		local_flag	=		p_local_flag
	WHERE
		set_name_id = l_set_name_id;

Line: 575

		ROLLBACK TO Update_PMSetName_PUB;

Line: 584

		ROLLBACK TO Update_PMSetName_PUB;

Line: 593

		ROLLBACK TO Update_PMSetName_PUB;

Line: 609

END Update_PMSetName;

Line: 611

/* validate set name exists at the time of update */
FUNCTION Validate_SetName
	(p_set_name_id NUMBER, p_set_name varchar2)
	return boolean
IS
	l_status number;

Line: 619

	SELECT
		count (*) into l_status
	FROM
		eam_pm_set_names
	WHERE
		set_name_id = p_set_name_id
		and set_name=p_set_name;

Line: 637

/* Validate set name is unique at the time of insert */
FUNCTION Validate_SetNameUnique
	(p_set_name VARCHAR2)
	return boolean
IS
	l_status number;

Line: 645

	SELECT
		count(*) into l_status
	FROM
		eam_pm_set_names
	WHERE
		set_name = p_set_name ;

Line: 673

	SELECT
		'PASS' into l_status
	FROM
		dual
	WHERE
		p_end_date > sysdate;

Line: 713

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