APPS.AHL_MC_MASTERCONFIG_PVT SQL Statements

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

Line: 77

		SELECT 	'x'
		FROM 	ahl_mc_headers_b
		WHERE 	upper(name) = upper(p_mc_name) AND
			mc_id <> p_mc_id;

Line: 209

	-- 6.	Select next value from the AHL_MC_HEADERS_B_S sequence
	SELECT ahl_mc_headers_b_s.nextval INTO p_x_mc_header_rec.mc_header_id FROM dual;

Line: 232

	-- 8.	Call AHL_MC_HEADERS_PKG.INSERT_ROW with relevant attribute values
	AHL_MC_HEADERS_PKG.INSERT_ROW
	(
		X_ROWID			=> l_row_id,	-- passed as dummy, cannot pass null
		X_MC_HEADER_ID		=> p_x_mc_header_rec.mc_header_id,
		X_NAME			=> p_x_mc_header_rec.name,
		X_MC_ID			=> p_x_mc_header_rec.mc_id,
		X_VERSION_NUMBER	=> p_x_mc_header_rec.version_number,
		X_REVISION		=> p_x_mc_header_rec.revision,
		X_MODEL_CODE            => p_x_mc_header_rec.model_code, -- SATHAPLI::Enigma code changes, 26-Aug-2008
		X_CONFIG_STATUS_CODE	=> p_x_mc_header_rec.config_status_code,
		X_OBJECT_VERSION_NUMBER	=> p_x_mc_header_rec.object_version_number,
		X_SECURITY_GROUP_ID	=> p_x_mc_header_rec.security_group_id,
		X_ATTRIBUTE_CATEGORY	=> p_x_mc_header_rec.attribute_category,
		X_ATTRIBUTE1		=> p_x_mc_header_rec.attribute1,
		X_ATTRIBUTE2		=> p_x_mc_header_rec.attribute2,
		X_ATTRIBUTE3		=> p_x_mc_header_rec.attribute3,
		X_ATTRIBUTE4		=> p_x_mc_header_rec.attribute4,
		X_ATTRIBUTE5		=> p_x_mc_header_rec.attribute5,
		X_ATTRIBUTE6		=> p_x_mc_header_rec.attribute6,
		X_ATTRIBUTE7		=> p_x_mc_header_rec.attribute7,
		X_ATTRIBUTE8		=> p_x_mc_header_rec.attribute8,
		X_ATTRIBUTE9		=> p_x_mc_header_rec.attribute9,
		X_ATTRIBUTE10		=> p_x_mc_header_rec.attribute10,
		X_ATTRIBUTE11		=> p_x_mc_header_rec.attribute11,
		X_ATTRIBUTE12		=> p_x_mc_header_rec.attribute12,
		X_ATTRIBUTE13		=> p_x_mc_header_rec.attribute13,
		X_ATTRIBUTE14		=> p_x_mc_header_rec.attribute14,
		X_ATTRIBUTE15		=> p_x_mc_header_rec.attribute15,
		X_DESCRIPTION		=> p_x_mc_header_rec.description,
		X_CREATION_DATE		=> G_SYSDATE,
		X_CREATED_BY		=> G_USER_ID,
		X_LAST_UPDATE_DATE	=> G_SYSDATE,
		X_LAST_UPDATED_BY	=> G_USER_ID,
  		X_LAST_UPDATE_LOGIN	=> G_LOGIN_ID
	);

Line: 279

	-- 9.	Select next value from the AHL_MC_RELATIONSHIPS_S sequence
	SELECT ahl_mc_relationships_s.nextval INTO p_x_node_rec.relationship_id FROM dual;

Line: 294

		SELECT ahl_mc_rel_pos_key_s.nextval INTO p_x_node_rec.position_key FROM dual;

Line: 593

	-- 1h.	Call AHL_MC_HEADERS_PKG.UPDATE_ROW with relevant attribute values
	AHL_MC_HEADERS_PKG.UPDATE_ROW
	(
		X_MC_HEADER_ID		=> p_x_mc_header_rec.mc_header_id,
		X_NAME			=> p_x_mc_header_rec.name,
		X_MC_ID			=> p_x_mc_header_rec.mc_id,
		X_VERSION_NUMBER	=> p_x_mc_header_rec.version_number,
		X_REVISION		=> p_x_mc_header_rec.revision,
		X_MODEL_CODE            => p_x_mc_header_rec.model_code, -- SATHAPLI::Enigma code changes, 26-Aug-2008
		X_CONFIG_STATUS_CODE	=> p_x_mc_header_rec.config_status_code,
		X_OBJECT_VERSION_NUMBER	=> p_x_mc_header_rec.object_version_number,
		X_SECURITY_GROUP_ID	=> p_x_mc_header_rec.security_group_id,
		X_ATTRIBUTE_CATEGORY	=> p_x_mc_header_rec.attribute_category,
		X_ATTRIBUTE1		=> p_x_mc_header_rec.attribute1,
		X_ATTRIBUTE2		=> p_x_mc_header_rec.attribute2,
		X_ATTRIBUTE3		=> p_x_mc_header_rec.attribute3,
		X_ATTRIBUTE4		=> p_x_mc_header_rec.attribute4,
		X_ATTRIBUTE5		=> p_x_mc_header_rec.attribute5,
		X_ATTRIBUTE6		=> p_x_mc_header_rec.attribute6,
		X_ATTRIBUTE7		=> p_x_mc_header_rec.attribute7,
		X_ATTRIBUTE8		=> p_x_mc_header_rec.attribute8,
		X_ATTRIBUTE9		=> p_x_mc_header_rec.attribute9,
		X_ATTRIBUTE10		=> p_x_mc_header_rec.attribute10,
		X_ATTRIBUTE11		=> p_x_mc_header_rec.attribute11,
		X_ATTRIBUTE12		=> p_x_mc_header_rec.attribute12,
		X_ATTRIBUTE13		=> p_x_mc_header_rec.attribute13,
		X_ATTRIBUTE14		=> p_x_mc_header_rec.attribute14,
		X_ATTRIBUTE15		=> p_x_mc_header_rec.attribute15,
		X_DESCRIPTION		=> p_x_mc_header_rec.description,
		X_LAST_UPDATE_DATE	=> G_SYSDATE,
		X_LAST_UPDATED_BY	=> G_USER_ID,
  		X_LAST_UPDATE_LOGIN	=> G_LOGIN_ID
  	);

Line: 633

			'Header ['||p_x_mc_header_rec.mc_header_id||'] updated'
		);

Line: 735

	p_x_node_rec.operation_flag := G_DML_UPDATE;

Line: 828

PROCEDURE Delete_Master_Config
(
	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_mc_header_id     	IN 		NUMBER,
	p_object_ver_num        IN 		NUMBER
)
IS
	-- Define cursor check_mc_not_subconfig to verify that MC is not associated as a subconfiguration
	-- ##TAMAL## -- Need to check for only non-expired subconfiguration associations, but also need to delete
	-- such associations from the table so that the draft MC can be deleted, else will throw foreign key
	-- validation error...
	-- ##TAMAL## -- Need some mechanism to check whether a subconfiguration association is logically expired,
	-- since any node up to the root node could be expired...
	CURSOR check_mc_not_subconfig
	(
		p_mc_header_id in number
	)
	IS
		SELECT 'x'
		FROM ahl_mc_config_relations
		WHERE mc_header_id = p_mc_header_id;

Line: 862

		SELECT 	relationship_id, object_version_number
		FROM 	ahl_mc_relationships
		WHERE 	mc_header_id = p_mc_header_id AND
			parent_relationship_id IS NULL;

Line: 874

		SELECT 	'x'
		FROM 	ahl_unit_config_headers
		WHERE 	master_config_id = p_mc_header_id AND
			trunc(nvl(active_end_date, G_SYSDATE + 1)) > G_TRUNC_DATE;

Line: 880

		SELECT	'x'
		FROM	ahl_mc_relationships mcr, ahl_unit_config_headers uch
		WHERE	mcr.mc_header_id = p_mc_header_id AND
			mcr.parent_relationship_id IS NULL AND
			uch.master_config_id = mcr.relationship_id AND
			trunc(nvl(uch.active_end_date, G_SYSDATE + 1)) > G_TRUNC_DATE;

Line: 889

	l_api_name	CONSTANT	VARCHAR2(30)	:= 'Delete_Master_Config';

Line: 903

	SAVEPOINT Delete_Master_Config_SP;

Line: 982

		-- 6.	If l_config_status_code = 'DRAFT' or 'APPROVAL_REJECTED' [Delete MC]

		-- 6a.	Call AHL_MC_RULE_PVT.Delete_Rules_For_MC to delete all the rules associated with this MC
		AHL_MC_RULE_PVT.Delete_Rules_For_MC
		(
			p_api_version 		=> 1.0,
			p_init_msg_list 	=> FND_API.G_FALSE,
			p_commit 		=> FND_API.G_FALSE,
			p_validation_level 	=> FND_API.G_VALID_LEVEL_FULL,
			x_return_status 	=> l_return_status,
			x_msg_count 		=> l_msg_count,
			x_msg_data 		=> l_msg_data,
			p_mc_header_id 		=> p_mc_header_id
		);

Line: 1009

				'Deleted rules for MC'
			);

Line: 1013

		-- 6b.	Call AHL_MC_PATH_POSITION_PVT.Delete_Positions_For_MC to delete all the position path records for this MC
		AHL_MC_PATH_POSITION_PVT.Delete_Positions_For_MC
		(
			p_api_version 		=> 1.0,
			p_init_msg_list 	=> FND_API.G_FALSE,
			p_commit 		=> FND_API.G_FALSE,
			p_validation_level 	=> FND_API.G_VALID_LEVEL_FULL,
			x_return_status 	=> l_return_status,
			x_msg_count 		=> l_msg_count,
			x_msg_data 		=> l_msg_data,
			p_mc_header_id 		=> p_mc_header_id
		);

Line: 1038

				'Deleted position paths for MC'
			);

Line: 1047

		-- 6c.	Call AHL_MC_NODE_PVT.Delete_Node to delete the MC tree starting from the topnode
		AHL_MC_NODE_PVT.Delete_Node
		(
			p_api_version 		=> 1.0,
			p_init_msg_list 	=> FND_API.G_FALSE,
			p_commit 		=> FND_API.G_FALSE,
			p_validation_level 	=> FND_API.G_VALID_LEVEL_FULL,
			x_return_status		=> l_return_status,
			x_msg_count 		=> l_msg_count,
			x_msg_data 		=> l_msg_data,
			p_node_id 		=> l_topnode_rel_id,
			p_object_ver_num 	=> l_topnode_object_ver_num
		);

Line: 1073

				'Deleted nodes for MC'
			);

Line: 1077

		-- 6d.	Call AHL_MC_HEADERS_PKG.DELETE_ROW to delete the MC
		AHL_MC_HEADERS_PKG.DELETE_ROW (p_mc_header_id);

Line: 1086

				'Deleted Header'
			);

Line: 1125

		-- 7b.	Update the config_status_code = 'CLOSED' and object_version_number for the MC with MC_HEADER_ID = p_ mc_header_id
		UPDATE 	ahl_mc_headers_b
		SET 	object_version_number = p_object_ver_num + 1,
			config_status_code = 'CLOSED',
			last_update_date = G_SYSDATE,
			last_updated_by = G_USER_ID,
			last_update_login = G_LOGIN_ID
		WHERE 	mc_header_id = p_mc_header_id;

Line: 1139

		-- 7c.	Update the active_end_date of the topnode of the MC with MC_HEADER_ID = p_mc_header_id
		UPDATE 	ahl_mc_relationships
		SET 	active_end_date = G_TRUNC_DATE,
			object_version_number = l_topnode_object_ver_num + 1,
			last_update_date = G_SYSDATE,
			last_updated_by = G_USER_ID,
			last_update_login = G_LOGIN_ID
		WHERE 	relationship_id = l_topnode_rel_id;

Line: 1150

		-- MC associated with one of the nodes, the latter cannot be deleted ever since it is associated as a
		-- subconfig, since potentially the earlier MC can be reopened

		IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
		THEN
			fnd_log.string
			(
				fnd_log.level_statement,
				'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
				'Header ['||p_mc_header_id||'] and topnode ['||l_topnode_rel_id||'] closed'
			);

Line: 1164

		FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DELETE_STS_INV');

Line: 1211

		Rollback to Delete_Master_Config_SP;

Line: 1221

		Rollback to Delete_Master_Config_SP;

Line: 1231

		Rollback to Delete_Master_Config_SP;

Line: 1237

				p_procedure_name 	=> 'Delete_Master_Config',
				p_error_text     	=> SUBSTR(SQLERRM,1,240)
			);

Line: 1248

END Delete_Master_Config;

Line: 1574

	SELECT	MC_HEADER_ID,
		NAME,
		MC_ID,
		VERSION_NUMBER,
		REVISION,
		MODEL_CODE, -- SATHAPLI::Enigma code changes, 26-Aug-2008
		CONFIG_STATUS_CODE,
		SECURITY_GROUP_ID,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		DESCRIPTION
	FROM 	AHL_MC_HEADERS_VL
	WHERE 	MC_HEADER_ID = p_mc_header_id;

Line: 1608

	SELECT	RELATIONSHIP_ID,
		POSITION_KEY,
		ITEM_GROUP_ID,
		GROUP_NAME,
		POSITION_REF_CODE,
		POSITION_REF_MEANING,
		POSITION_NECESSITY_CODE,
		POSITION_NECESSITY_MEANING,
		-- Priyan : Bug # 5639027
		ATA_CODE,
		ATA_MEANING,
		-- End Priyan : Bug # 5639027
		UOM_CODE,
		QUANTITY,
		DISPLAY_ORDER,
		ACTIVE_START_DATE,
		ACTIVE_END_DATE,
		SECURITY_GROUP_ID,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15
	FROM 	AHL_MC_RELATIONSHIPS_V
	WHERE 	MC_HEADER_ID = p_mc_header_id AND
	      	PARENT_RELATIONSHIP_ID IS NULL;

Line: 1653

		SELECT 	'x'
		FROM 	ahl_mc_headers_b
		WHERE 	mc_id = p_mc_id AND
			nvl(version_number, 0) > nvl(p_version_number, 0);

Line: 2084

		SELECT 	mch.config_status_code,
			mcr.relationship_id,
			mcr.object_version_number,
			mcr.item_group_id
		FROM 	ahl_mc_headers_v mch,
			ahl_mc_relationships mcr
		WHERE 	mch.mc_header_id = p_mc_header_id AND
			mch.mc_header_id = mcr.mc_header_id AND
			mcr.parent_relationship_id IS NULL;

Line: 2100

		SELECT 	relationship_id, object_version_number
		FROM 	ahl_mc_relationships
		WHERE 	mc_header_id = p_mc_header_id AND
			parent_relationship_id IS NULL;

Line: 2111

		SELECT 	status_code,object_version_number
		FROM 	ahl_item_groups_b igp
		WHERE 	item_group_id = p_item_group_id;

Line: 2121

		SELECT config_status_code
		FROM ahl_mc_headers_b
		WHERE mc_header_id = p_mc_header_id;

Line: 2235

  			UPDATE ahl_item_groups_b
    		SET  status_code ='COMPLETE',
        		object_version_number = object_version_number +1
   			WHERE item_group_id = l_item_group_id;

Line: 2245

	UPDATE 	ahl_mc_headers_b
	SET 	config_status_code = l_config_status_code,
	    	object_version_number = p_object_ver_num + 1,
		last_update_date = G_SYSDATE,
		last_updated_by = G_USER_ID,
		last_update_login = G_LOGIN_ID
	WHERE 	mc_header_id = p_mc_header_id;

Line: 2253

	-- 5.	Update the topnode of the MC
	UPDATE 	ahl_mc_relationships
	SET	active_end_date = null,
		object_version_number = l_topnode_object_ver_num + 1,
		last_update_date = G_SYSDATE,
		last_updated_by = G_USER_ID,
		last_update_login = G_LOGIN_ID
	WHERE 	relationship_id = l_topnode_rel_id;

Line: 2486

		-- 54b.i. Update the status and object_version_number of the MC header record
		UPDATE 	ahl_mc_headers_b
		SET	config_status_code = 'APPROVAL_PENDING',
			object_version_number = p_object_ver_num + 1,
			last_update_date = G_SYSDATE,
			last_updated_by = G_USER_ID,
			last_update_login = G_LOGIN_ID
		WHERE	mc_header_id = p_mc_header_id AND
			object_version_number = p_object_ver_num;

Line: 2533

		UPDATE 	ahl_mc_headers_b
		SET	config_status_code = 'COMPLETE',
			object_version_number = p_object_ver_num + 1,
			last_update_date = G_SYSDATE,
			last_updated_by = G_USER_ID,
			last_update_login = G_LOGIN_ID
		WHERE	mc_header_id = p_mc_header_id AND
			object_version_number = p_object_ver_num;

Line: 2631

		SELECT 	object_version_number
		FROM 	ahl_mc_headers_b
		WHERE 	mc_header_id = p_mc_header_id;

Line: 2686

		SELECT 	'x'
		FROM 	ahl_mc_headers_b
		WHERE 	upper(name) = upper(p_mc_name);

Line: 2690

	CURSOR check_mc_name_noupdate
	(
		p_mc_header_id in number
	)
	IS
		SELECT 	name
		FROM 	ahl_mc_headers_b
		WHERE 	mc_header_id = p_mc_header_id;

Line: 2738

			-- Implies MC is being updated, check name is not updated
			OPEN check_mc_name_noupdate(p_x_mc_header_rec.mc_header_id);

Line: 2740

			FETCH check_mc_name_noupdate INTO l_dummy_name;

Line: 2743

				FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RNAME_NOUPDATE');

Line: 2755

			CLOSE check_mc_name_noupdate;

Line: 2775

		SELECT 	'x'
		FROM 	ahl_mc_headers_b
		WHERE 	upper(revision) = upper(p_mc_revision) AND
			mc_id = p_mc_id and
			mc_header_id <> p_mc_header_id;

Line: 2786

		SELECT revision
		FROM ahl_mc_headers_b
		WHERE mc_header_id = p_mc_header_id;

Line: 2837

					SELECT TO_NUMBER(SUBSTR(p_x_mc_header_rec.revision,i,1)) INTO l_temp_num FROM DUAL;

Line: 2872

		SELECT 	config_status_code
		FROM 	ahl_mc_headers_v
		WHERE	mc_header_id = p_mc_header_id;

Line: 2919

		SELECT config_status_code
		FROM ahl_mc_headers_b
		WHERE mc_header_id = p_mc_header_id;

Line: 2931

	-- 5.	If (record is found and l_status = 'APPROVAL_REJECTED'), then Update config_status_code = 'DRAFT'
	IF (get_mc_header_status%FOUND)
	THEN
		IF (l_status = 'APPROVAL_REJECTED')
		THEN
			UPDATE ahl_mc_headers_b
			SET config_status_code = 'DRAFT'
			WHERE mc_header_id = p_mc_header_id;

Line: 2958

		SELECT relationship_id, position_ref_meaning, position_necessity_code
		FROM ahl_mc_relationships_v
		WHERE mc_header_id = p_mc_header_id;

Line: 2968

		SELECT 	mch.name, mch.config_status_code
		FROM 	ahl_mc_config_relations mccr, ahl_mc_headers_b mch
		WHERE 	mccr.relationship_id = p_relationship_id AND
	      		mccr.mc_header_id = mch.mc_header_id;

Line: 2981

		SELECT 	ig.item_group_id, ig.name, ig.type_code, ig.status_code
		FROM 	ahl_mc_relationships mcr, ahl_item_groups_b ig
		WHERE 	mcr.relationship_id = p_relationship_id AND
	      		ig.item_group_id = mcr.item_group_id;