/****** Object:  StoredProcedure [dbo].[sp_CreateMappingForChangedTables]    Script Date: 25/08/2014 04:29:03 ******/
/**/
/****************************************************************************/
/***  This (DCA_Database_Patch.sql) will patch the DCA_Database.sql File  ***/
/***  The Filename does not contain a versionnumber anymore since DCA3.2  ***/
/***                                                                      ***/
/****************************************************************************/
/**/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateMappingForChangedTables]') AND type in (N'P', N'PC'))																																					 
DROP PROCEDURE [dbo].[sp_CreateMappingForChangedTables]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateMappingsForFineTunedChangedTables]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_CreateMappingsForFineTunedChangedTables]
GO

/* LND2-26093.sn */
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateDefaultMappings]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_CreateDefaultMappings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateDefaultMappingsForFineTunedTables]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_CreateDefaultMappingsForFineTunedTables]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateMappingForUnchangedTables]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_CreateMappingForUnchangedTables]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LegacyCreateDefaultMappings]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_LegacyCreateDefaultMappings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LegacyCreateMappingForChangedTables]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_LegacyCreateMappingForChangedTables]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LegacyCreateMappingForUnchangedTables]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_LegacyCreateMappingForUnchangedTables]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_bcddc000_1]') AND type in (N'P', N'PC'))																																		 
DROP PROCEDURE [dbo].[sp_update_bcddc000_1]
GO
/* LND2-26093.en */

/* LND2-19008.sn */
IF  EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_insert_update_bcmap140_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP procedure [dbo].[sp_insert_update_bcmap140_1]
GO

IF  EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GuessMapping]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP procedure [dbo].[sp_GuessMapping]
GO
/* LND2-19008.en */

/* LND2-26093.sn */
/********************************************************************************
* 	NAME:		sp_CreateDefaultMappings			      	*
*	DESCRIPTION:	This Strored procedure, generates the Default mappings	*
*			for the tables for which Comparison has already been  	*
*			done.						      	*
*	Inputs:		The Runn Number, the Migration Profile, the Start pack	*
*			module and Table to the target Pack, Mod and Table.   	*
*	Calls:		The Following procedures are called.		      	*
*			1. sp_insert_bcmap120_1					*
*			2. sp_insert_bcmap125_1					*
*			3. sp_insert_bcmap130_1					*
*			4. sp_insert_bcmap135_1					*
*			5. sp_CreateMappingsForFineTunedChangedTables		*
*			6. sp_update_bcddc000_1					*
*	UPDATION 								*
*		- Adding  a Parameter @I_UserName[varchar](12)-31/oct/2001	*
*		- Call to sp_insert_bcmap110_1() is added-31/oct/2001		*
********************************************************************************/
CREATE PROCEDURE [sp_CreateDefaultMappings]
	(

	@I_IntRunn 		[numeric](10,5),
	@I_IntMprf 		[int],
	@I_StrPackFrom 		[varchar](2),
	@I_StrModFrom 		[varchar](3),
	@I_StrTableFrom 	[varchar](3),
	@I_StrPackTo 		[varchar](2),
	@I_StrModTo		[varchar](3),
	@I_StrTableTo		[varchar](3),
	@I_UserName		[varchar](12),
	@I_DMGChangedTables	[int] = 1,
	@I_DMGUnChangedTables	[int] = 1,
	@I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
	)
	-- LND2-26093.c Parameter @I_IntRunn changed
AS
DECLARE @RecCntMAP120 		as int			--Record Count of BCMAP120
DECLARE @IntRetVal		as int			--Return Value
DECLARE	@IntCount		as int			--Counter
DECLARE @IntTableCount		as int			--Table Count
DECLARE @StrPackage 		as varchar(2)		--Package
DECLARE @StrModule 		as varchar(3)		--Module
DECLARE @StrTable 		as varchar(3)		--Table
DECLARE @StrCtype 		as varchar(1)		--Ctype -U/C/N/D
DECLARE @StrTargetAppl		as varchar(15)		--Target Application
DECLARE @StrSourceAppl		as varchar(15)		--Source Application
DECLARE @StrStdVersion		as varchar(4)		--Standard Version
DECLARE @StrStdRelease		as varchar(2)		--Standard Release
DECLARE @StrStdCustomer		as varchar(4)		--Standard Customer
DECLARE	@StrDevVersion		as varchar(4)		--Development Version
DECLARE	@StrDevRelease		as varchar(2)		--Development Release
DECLARE	@StrDevCustomer		as varchar(4)		--Development Customer
DECLARE @StrSCM			as varchar(1)		--SCM enabled for the Mig. Profile
DECLARE @StrText		as varchar(1024)	--Text
DECLARE @StrTableName		as varchar(8)		--To Store Table Name
DECLARE @CurrDateTime		as datetime		--Current Date and Time
DECLARE	@StrComment		as varchar(2000)	--Variable to hold Pretext
DECLARE @StrStatus		as varchar(1)		--Status
DECLARE @StrUnChanged		as varchar(1)		--Status - Unchanged
DECLARE @StrChanged		as varchar(1)		--Status - Changed
DECLARE @StrTemp		as varchar(1024)	--Temporary String
/* STEP 1. Get the Target Application Name & Mprf VRC details.*/
SELECT 	@StrTargetAppl 	= [dbo].[bcmap100].tapp,
	@StrSourceAppl	= [dbo].[bcmap100].sapp,
	@StrStdVersion	= [dbo].[bcmap100].vers,
	@StrStdRelease	= [dbo].[bcmap100].rele,
	@StrStdCustomer	= [dbo].[bcmap100].cust,
	@StrSCM		= [dbo].[bcmap100].scme
FROM 	[dbo].[bcmap100]
WHERE 	[dbo].[bcmap100].mprf = @I_IntMprf
/* STEP 2. Get the Development VRC from BCMAP 105 */
SELECT 	@StrDevVersion	= [dbo].[bcmap105].dver,
	@StrDevRelease	= [dbo].[bcmap105].drel,
	@StrDevCustomer	= [dbo].[bcmap105].dcus
FROM	[dbo].[bcmap105]
WHERE	[dbo].[bcmap105].mprf = @I_IntMprf
AND	[dbo].[bcmap105].vers = @StrStdVersion
AND	[dbo].[bcmap105].rele = @StrStdRelease
AND	[dbo].[bcmap105].cust = @StrStdCustomer

--If there is no Dev. VRC then set the Var to empty
SET @StrDevVersion = ISNULL(@StrDevVersion,'')
SET @StrDevRelease = ISNULL(@StrDevRelease,'')
SET @StrDevCustomer = ISNULL(@StrDevCustomer,'')
--IF Generation is for Changed Tables then Set the Flag.
IF (@I_DMGChangedTables = 1)
BEGIN
	SET @StrChanged ='C'
END
ELSE
BEGIN
	SET @StrChanged =''
END
--IF Generation is for UnChanged Tables then Set the Flag.
IF (@I_DMGUnChangedTables = 1 )
BEGIN
	SET @StrUnChanged ='U'
END
ELSE
BEGIN
	SET @StrUnChanged =''
END
SET @CurrDateTime = GETDATE()
EXEC Write_to_file '***********************************************************************************************************************', @I_StrLogFileName,1
SET @StrTemp=   'START DEFAULT MAPPING GENERATION '  + CHAR(9) + ' START TIME ' + CAST(@CurrDateTime as varchar)
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =  'Range FROM ' +	@I_StrPackFrom 	+ @I_StrModFrom + @I_StrTableFrom + '  TO   '  +
						@I_StrPackTo 	+ @I_StrModTo	+ @I_StrTableTo
EXEC Write_to_file @StrTemp, @I_StrLogFileName

SET @StrTemp =  'Migration Profile- '  +  LTRIM(STR(@I_IntMprf))
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =   'Run Number- '  +  LTRIM(STR(@I_IntRunn))
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =  'User Name- '  + @I_UserName
EXEC Write_to_file @StrTemp, @I_StrLogFileName
EXEC Write_to_file '***********************************************************************************************************************', @I_StrLogFileName
SET @IntTableCount = 0
/*STEP 3. Generate Default Mappings for Unchanged and  Changed  Tables*/
DECLARE DDC140_Cursor CURSOR FOR
SELECT 	[dbo].[bcddc140].cpac,
	[dbo].[bcddc140].cmod,
	[dbo].[bcddc140].flno,
	[dbo].[bcddc140].ctyp
FROM 	[dbo].[bcddc140]
WHERE	[dbo].[bcddc140].runn = @I_IntRunn
AND 	([dbo].[bcddc140].ctyp IN (@StrUnChanged, @StrChanged))
AND  	([dbo].[bcddc140].cpac  BETWEEN @I_StrPackFrom AND @I_StrPackTo )
AND	([dbo].[bcddc140].cmod  BETWEEN @I_StrModFrom  AND @I_StrModTo)
AND	([dbo].[bcddc140].flno  BETWEEN @I_StrTableFrom AND @I_StrTableTo )
OPEN DDC140_Cursor
FETCH NEXT FROM DDC140_Cursor
INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @StrComment = ''
	SET @StrStatus = ''


	/* If some mappings are present for the given Range (@RecCntMAP120 <> 0) then check
	   whether mapping present for the Curent Table. If no mappings then Generate */
	SELECT 	@StrComment= [dbo].[bcmap120].comt,
		@StrStatus = [dbo].[bcmap120].stat
	FROM   	[dbo].[bcmap120]
	WHERE  	[dbo].[bcmap120].mprf 	= 	@I_IntMprf
	AND	[dbo].[bcmap120].vers 	= 	@StrStdVersion
	AND	[dbo].[bcmap120].rele 	= 	@StrStdRelease
	AND	[dbo].[bcmap120].cust 	= 	@StrStdCustomer
	AND  	[dbo].[bcmap120].tcpac 	=	@StrPackage
	AND	[dbo].[bcmap120].tcmod 	=	@StrModule
	AND	[dbo].[bcmap120].tflno 	=	@StrTable

	SET @StrComment = ISNULL(@StrComment,'')
	SET @StrStatus = ISNULL(@StrStatus,'')


	IF (@StrStatus ='' OR @StrStatus ='N')
	BEGIN
		--check whether a rec. is there in DVRC.
		--If DVRC is present then Skip.
		SELECT 	@IntCount = Count(*)
		FROM   	[dbo].[bcmap120]
		WHERE  	[dbo].[bcmap120].mprf 	=	@I_IntMprf
		AND  	[dbo].[bcmap120].tcpac 	=	@StrPackage
		AND	[dbo].[bcmap120].tcmod 	=	@StrModule
		AND	[dbo].[bcmap120].tflno 	=	@StrTable
		AND	[dbo].[bcmap120].vers	=	@StrDevVersion
		AND	[dbo].[bcmap120].rele	=	@StrDevRelease
		AND	[dbo].[bcmap120].cust	=	@StrDevCustomer

		IF (@IntCount > 0)
		BEGIN
			--Write in Log file - Mapping Already Exist
			SET @StrTemp = CHAR(9) +'Mapping Exists for the ' +  	@StrPackage      +
										@StrModule       +
										@StrTable 	 +
					       ' In the Development VRC - ' +
										@StrDevVersion +
										@StrDevRelease +
										@StrDevCustomer
			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
			CONTINUE
		END

	END
	ELSE
	BEGIN
		IF ( @StrStatus <> 'N')
		BEGIN
			SET @StrTemp = CHAR(9)+ 'Mapping Exists for the ' +  	@StrPackage      +
										@StrModule       +
										@StrTable 	 +
					       ' in the Standard VRC - ' +
										@StrStdVersion 	 +
										@StrStdRelease    +
										@StrStdCustomer   +
					       ' with the status - '  +  @StrStatus

			EXEC Write_to_file @StrTemp, @I_StrLogFileName
			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
			CONTINUE
		END
	END


	BEGIN TRANSACTION
	-- STEP 4. Insert a New Record in BCMAP120/125,130/135

	/* Insert a Record in BAMAP120*/
	SET @StrTableName =  @StrPackage + @StrModule + @StrTable
	SET @StrText =  'migrate ' + @StrTableName
	EXEC @IntRetVal = sp_insert_bcmap120_1 	@I_IntMprf,		--Migration Profile
						@StrStdVersion,		--Version
						@StrStdRelease,		--Release
						@StrStdCustomer,	--Customer
						@StrPackage,		--Package
						@StrModule,		--Module
						@StrTable,		--Table
						@StrText,		--Precondition Text
						'',			--Check out User
						@I_UserName,		--Last Modified User
						@CurrDateTime,		--Date
						'G',			--Status
						'',			--Variables
						@StrComment,		--Comments
						@StrTableName		--Source Table
	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered: ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = CHAR(9) + 'Error -' + @StrTemp + ' -  while inserting  the record ' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP120. '
		EXEC Write_to_file @StrTemp, @I_StrLogFileName

		ROLLBACK TRANSACTION


		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

		CONTINUE
	END

	/* STEP 5. Insert 2 Records in BCMAP130
		1. For Select
		2. SelectDo */

	SET @StrText ='select  ' +  @StrTableName + '.* ' + CHAR(13)  + CHAR(10) + 'from  ' + @StrTableName    -- LND2-26093.c
	EXEC @IntRetVal = sp_insert_bcmap130_1  @I_IntMprf,		--Migration Profile
						@StrStdVersion,       	--Version
						@StrStdRelease,       	--Release
						@StrStdCustomer,      	--Customer
						@StrPackage,    	--Target Package
						@StrModule,     	--Target Module
						@StrTable,      	--Target Table
						@StrPackage,    	--Source Package
						@StrModule,     	--Source Module
						@StrTable,      	--Source Table
						'S',            	--Code for Text S/D/F/X
						@StrText        	--The Text -Query

	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered: ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                 ' CODE  - S ' +
	 		                 ' into BCMAP130. '

		EXEC Write_to_file @StrTemp, @I_StrLogFileName
		ROLLBACK TRANSACTION

		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

		CONTINUE
	END
	EXEC @IntRetVal = sp_insert_bcmap130_1 	@I_IntMprf,	--Migration Profile
						@StrStdVersion,	--Version
						@StrStdRelease,        --Release
						@StrStdCustomer,       --Customer
						@StrPackage,    --Target Package
						@StrModule,     --Target Module
						@StrTable,      --Target Table
						@StrPackage,    --Source Package
						@StrModule,     --Source Module
						@StrTable,      --Source Table
						'D',            --Code for Text S/D/F/X
						'dump 1'        --The Text -Query
	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered: ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                 ' CODE - D ' +
			                 ' into BCMAP130. '
		ROLLBACK TRANSACTION

		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

		CONTINUE
	END
	--STEP 6. If Migration is SCM enabled then insert records into 125 & 135

	IF @StrSCM = 'Y'
	BEGIN
		/* Inser a New Record in BCMAP110*/
		EXEC @IntRetVal = sp_insert_bcmap110_1 		@I_IntMprf,		--Migration Profile
								@StrStdVersion,     	--Version
								@StrStdRelease,     	--Release
								@StrStdCustomer,    	--Customer
								@StrPackage, 		--Package
								@StrModule,  		--Module
								@StrTable,   		--Table
								1,           		--Revision No.
								'Default Generation',   --Revision Info.
								'DEFAULT',		--Issue ID
								@CurrDateTime,		--Date & Time
								0,			--Patch NOT Exported(0)/Exported(1)
								@I_UserName		--User Name

		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP110. '

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END

		/* Insert a New Record in BCMAP125*/
		SET @StrText =  'migrate ' + @StrTableName
		EXEC	@IntRetVal = sp_insert_bcmap125_1 	@I_IntMprf,	        --Migration Profile
								@StrStdVersion,         --Version
								@StrStdRelease,         --Release
								@StrStdCustomer,        --Customer
								@StrPackage,    	--Package
								@StrModule,     	--Module
								@StrTable,      	--Table
								1,              	--Revision Number
								@StrText,       	--Precondition Text
								@I_UserName,    	--Last Modified User
								'C',            	--Status
								'',             	--Variables
								@StrTableName   	--Source Table

		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP125. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END

		/* Insert 2 Records in BCMAP135
			1. For Select
			2. SelectDo */
		SET @StrText =  'select  ' +  @StrTableName + '.* ' + CHAR(13)  + CHAR(10) + 'from  ' + @StrTableName    -- LND2-26093.c
		EXEC @IntRetVal = sp_insert_bcmap135_1 	@I_IntMprf,		--Migration Profile
							@StrStdVersion,        --Version
							@StrStdRelease,        --Release
							@StrStdCustomer,       --Customer
							@StrPackage,    	--Target Package
							@StrModule,     	--Target Module
							@StrTable,      	--Target Table
							@StrPackage,    	--Source Package
							@StrModule,     	--Source Module
							@StrTable,      	--Source Table
							'S',            	--Code for Text S/D/F/X
							1,              	-- REvision Code
							@StrText,		--The Text -Query
							@I_UserName,		--User Name
							'C'			--Change Code
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP135. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
		--STEP Insert Second Record
		EXEC  @IntRetVal = sp_insert_bcmap135_1 	@I_IntMprf,	       	--Migration Profile
								@StrStdVersion,        	--Version
								@StrStdRelease,        	--Release
								@StrStdCustomer,       	--Customer
								@StrPackage,    	--Target Package
								@StrModule,     	--Target Module
								@StrTable,      	--Target Table
								@StrPackage,    	--Source Package
								@StrModule,     	--Source Module
								@StrTable,      	--Source Table
								'D',            	--Code for Text S/D/F/X
								1,              	-- REvision Code
								'dump 1',       	--The Text -Query
								@I_UserName,    	--User Name
								'C'             	--Change Code
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP135. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
	END


	--STEP 7. If the Table is Unchanged then Call Unchangedtables Procedure

	IF @StrCtype = 'U'
	BEGIN

		EXEC @IntRetVal = sp_CreateMappingForUnchangedTables 	@StrTargetAppl, 	--Target Application
									@StrSourceAppl	,	--Source Application
									@StrStdVersion,		--Version
									@StrStdRelease,		--Release
									@StrStdCustomer,	--Customer
									@StrSCM,		--SCM
									@I_IntRunn, 		--Runn
									@I_IntMprf, 		--Migration Profile
									@StrPackage, 		--Package
									@StrModule, 		--Module
									@StrTable,		--Table
									@I_UserName,		--User Name
									@I_StrLogFileName	--LogFileName
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
	END

	--STEP 8. IF the Table is a changed Table then Call Changed tables Proc.

	IF @StrCtype = 'C'
	BEGIN
		EXEC @IntRetVal = sp_CreateMappingForChangedTables 	@StrTargetAppl, 	--Target Application
									@StrSourceAppl,         --Source Application
									@StrStdVersion,		--Version
									@StrStdRelease,         --Release
									@StrStdCustomer,        --Customer
									@StrSCM,                --SCM
									@I_IntRunn,             --Runn
									@I_IntMprf,             --Migration Profile
									@StrPackage,            --Package
									@StrModule,             --Module
									@StrTable,              --Table
									@I_UserName,            --User Name
									@I_StrLogFileName	--LogFile Name
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
	END
	COMMIT TRANSACTION  -- Commit the Transaction
	SET @IntTableCount = @IntTableCount + 1
	SET @StrTemp = CHAR(9) + 'Successfully completed Default Mapping for - ' + @StrTableName
	EXEC Write_to_file  @StrTemp, @I_StrLogFileName

	FETCH NEXT FROM DDC140_Cursor
	INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
END
CLOSE DDC140_Cursor
DEALLOCATE DDC140_Cursor
IF @IntTableCount = 0
BEGIN
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
	SET @StrTemp = CHAR(9) +  'Default Mapping Generation for Tables Not Done due to any of the following reasons. '
	EXEC Write_to_file @StrTemp,@I_StrLogFileName
	SET @StrTemp = CHAR(9) + CHAR(9) + '1. Mapping Already exists for the tables in the selcted Range.'
	EXEC Write_to_file @StrTemp, @I_StrLogFileName
	SET @StrTemp = CHAR(9) + CHAR(9) + '2. DD Comparison was not done for the Tables in the selected Range.'
	EXEC Write_to_file @StrTemp, @I_StrLogFileName
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
END
ELSE
BEGIN
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
	SET @StrTemp = CHAR(9) + 'Default Mapping Generation is successfully done for = ' + LTRIM(STR(@IntTableCount)) + ' table(s).' + char(9) + 'END TIME   ' + CAST(GETDATE() as varchar)
	EXEC Write_to_file @StrTemp,@I_StrLogFileName
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
END
RETURN @@ERROR



GO
/* LND2-26093.en */


/* LND2-26093.sn */
/********************************************************************************
*	NAME:		sp_CreateDefaultMappingsForFineTunedTables		*
*	DESCRIPTION:	This is the Stroed Procedure that generates the Default *
*			mapping for given range of table.Default Mappings are   *
*			generated only for those table for which mappings are 	*
*			not present in BCMAP120.				*
*	TABLES:		1. BCMAP100						*
*			2. BCMAP120						*
*			3. BCDDC141						*
*	CALLS:		The following are the list of the PROC's CALLED		*
*			1. sp_insert_bcmap120_1					*
*			2. sp_insert_bcmap125_1					*
*			3. sp_insert_bcmap130_1					*
*			4. sp_insert_bcmap135_1					*
*			5. sp_CreateMappingsForFineTunedChangedTables		*
*			6. sp_update_bcddc000_1					*
*	UPDATION:								*
*		- On 31/Oct/2001						*
*			- Add a Parameter @I_UserName[varchar](12)		*
*			- Call sp_insert_bcmap110_1()				*
*			- Replace all "root" by @I_UserName			*
********************************************************************************/
CREATE PROCEDURE [sp_CreateDefaultMappingsForFineTunedTables]
(
	@I_IntRunn 		[numeric](10,5),
	@I_IntMprf 		[int],
	@I_StrPackFrom 		[varchar](2),
	@I_StrModFrom 		[varchar](3),
	@I_StrTableFrom 	[varchar](3),
	@I_StrPackTo 		[varchar](2),
	@I_StrModTo		[varchar](3),
	@I_StrTableTo		[varchar](3),
	@I_UserName		[varchar](12),
	@I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
	)
	-- LND2-26093.c  Parameter @I_IntRunn changed
 AS
DECLARE @RecCntMAP120 	as int			--Record Count in BCMAP120 Table
DECLARE @IntCount	as int			--Int Record Count
DECLARE @IntRetVal 	as int			--Return Value
DECLARE @IntTableCount	as int			--Table Count
DECLARE @StrTrgPackage 	as varchar(2)		--Target  Package
DECLARE @StrTrgModule 	as varchar(3)		--Target  Module
DECLARE @StrTrgTable 	as varchar(3)		--Target  Table
DECLARE @StrTableName	as varchar(8)		--To Store Table Name
DECLARE @StrSrcPackage 	as varchar(2)		--Source  Package
DECLARE @StrSrcModule 	as varchar(3)          	--Source  Module
DECLARE @StrSrcTable 	as varchar(3)          	--Source  Table
DECLARE @StrTargetAppl	as varchar(15)		--Target Application
DECLARE @StrSourceAppl	as varchar(15)		--Source Application
DECLARE @StrStdVersion	as varchar(4)		--Standard Version
DECLARE @StrStdRelease	as varchar(2)		--Standard Release
DECLARE @StrStdCustomer	as varchar(4)		--Standard Customer
DECLARE	@StrDevVersion	as varchar(4)		--Development Version
DECLARE	@StrDevRelease	as varchar(2)		--Development Release
DECLARE	@StrDevCustomer	as varchar(4)		--Development Customer
DECLARE @StrSCM		as varchar(1)		--Is SCM enabled?
DECLARE @StrText	as varchar(1024)	--To hold the Text value
DECLARE @CurrDateTime	as DateTime		--Current Date & time
DECLARE @StrTemp	as varchar(1024)	--Temp String to write data into he Log file
DECLARE	@StrComment	as varchar(2000)	--Variable to hold Pretext
DECLARE @StrStatus	as varchar(1)		--Status
/* STEP 1: Get the Target Application Name & mprf VRC details.*/
SELECT 	@StrTargetAppl 	= [dbo].[bcmap100].tapp,
	@StrSourceAppl	= [dbo].[bcmap100].sapp,
	@StrStdVersion	= [dbo].[bcmap100].vers,
	@StrStdRelease	= [dbo].[bcmap100].rele,
	@StrStdCustomer	= [dbo].[bcmap100].cust,
	@StrSCM		= [dbo].[bcmap100].scme
FROM 	[dbo].[bcmap100]
WHERE 	[dbo].[bcmap100].mprf 	= @I_IntMprf
/* STEP 2. Get the Development VRC from BCMAP 105 */
SELECT 	@StrDevVersion	= [dbo].[bcmap105].dver,
	@StrDevRelease	= [dbo].[bcmap105].drel,
	@StrDevCustomer	= [dbo].[bcmap105].dcus
FROM	[dbo].[bcmap105]
WHERE	[dbo].[bcmap105].mprf = @I_IntMprf
AND	[dbo].[bcmap105].vers = @StrStdVersion
AND	[dbo].[bcmap105].rele = @StrStdRelease
AND	[dbo].[bcmap105].cust = @StrStdCustomer

--If there is no Dev. VRC then set the Var to empty
SET @StrDevVersion = ISNULL(@StrDevVersion,'')
SET @StrDevRelease = ISNULL(@StrDevRelease,'')
SET @StrDevCustomer = ISNULL(@StrDevCustomer,'')
SET @CurrDateTime = GETDATE()
EXEC Write_to_file '***********************************************************************************************************************', @I_StrLogFileName
SET @StrTemp=  'START  Default Mapping Generation for FINE TUNED TABLES ' + CHAR(9) + + ' START TIME ' + CAST(@CurrDateTime as varchar)
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =  'Range FROM ' +	@I_StrPackFrom 	+ @I_StrModFrom + @I_StrTableFrom + '  TO   '  +
						@I_StrPackTo 	+ @I_StrModTo	+ @I_StrTableTo
EXEC Write_to_file @StrTemp, @I_StrLogFileName

SET @StrTemp = 'Migration Profile- '  +  LTRIM(STR(@I_IntMprf))
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =   'Run Number- '  +  LTRIM(STR(@I_IntRunn))
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =  'User Name- '  + @I_UserName
EXEC Write_to_file @StrTemp, @I_StrLogFileName
EXEC Write_to_file '***********************************************************************************************************************', @I_StrLogFileName
SET @IntTableCount = 0
/*Generate Default Mappings for Unchanged and  Changed  Tables*/
/* STEP 3: Select all the records from BCDDC141 (Fine Tuned Table) which are within the Range*/
DECLARE DDC141_Cursor CURSOR FOR
SELECT 	[dbo].[bcddc141].spac,
	[dbo].[bcddc141].smod,
	[dbo].[bcddc141].slno,
	[dbo].[bcddc141].tpac,
	[dbo].[bcddc141].tmod,
	[dbo].[bcddc141].tlno
FROM 	[dbo].[bcddc141]
WHERE	[dbo].[bcddc141].runn = @I_IntRunn
AND 	([dbo].[bcddc141].tpac  BETWEEN   @I_StrPackFrom AND  @I_StrPackTo )
AND	([dbo].[bcddc141].tmod  BETWEEN   @I_StrModFrom  AND  @I_StrModTo  )
AND	([dbo].[bcddc141].tlno  BETWEEN	  @I_StrTableFrom AND @I_StrTableTo)
OPEN DDC141_Cursor
FETCH NEXT FROM DDC141_Cursor
INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @StrComment = ''
	SET @StrStatus = ''

	/* If some mappings are present for the given Range (@RecCntMAP120 <> 0) then check
	   whether mapping present for the Curent Table. If no mappings then Generate */
	SELECT 	@StrComment= [dbo].[bcmap120].comt,
		@StrStatus = [dbo].[bcmap120].stat
	FROM   	[dbo].[bcmap120]
	WHERE  	[dbo].[bcmap120].mprf 	= 	@I_IntMprf
	AND	[dbo].[bcmap120].vers 	= 	@StrStdVersion
	AND	[dbo].[bcmap120].rele 	= 	@StrStdRelease
	AND	[dbo].[bcmap120].cust 	= 	@StrStdCustomer
	AND  	[dbo].[bcmap120].tcpac 	=	@StrTrgPackage
	AND	[dbo].[bcmap120].tcmod 	=	@StrTrgModule
	AND	[dbo].[bcmap120].tflno 	=	@StrTrgTable

	SET @StrComment = ISNULL(@StrComment,'')
	SET @StrStatus = ISNULL(@StrStatus,'')


	IF (@StrStatus ='' OR @StrStatus ='N')
	BEGIN
		--check whether a rec. is there in DVRC.
		--If DVRC is present then Skip.
		SELECT 	@IntCount = Count(*)
		FROM   	[dbo].[bcmap120]
		WHERE  	[dbo].[bcmap120].mprf 	=	@I_IntMprf
		AND  	[dbo].[bcmap120].tcpac 	=	@StrTrgPackage
		AND	[dbo].[bcmap120].tcmod 	=	@StrTrgModule
		AND	[dbo].[bcmap120].tflno 	=	@StrTrgTable
		AND	[dbo].[bcmap120].vers	=	@StrDevVersion
		AND	[dbo].[bcmap120].rele	=	@StrDevRelease
		AND	[dbo].[bcmap120].cust	=	@StrDevCustomer

		IF (@IntCount > 0)
		BEGIN
			--Write in Log file - Mapping Already Exist
			SET @StrTemp = char(9) +'Mapping Exists for the ' +  	@StrTrgPackage      +
										@StrTrgModule       +
										@StrTrgTable 	 +
					       ' In the Development VRC - ' +
										@StrDevVersion 	 +
										@StrDevRelease   +
										@StrDevCustomer
			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
			CONTINUE
		END

	END
	ELSE
	BEGIN
		IF ( @StrStatus <> 'N')
		BEGIN
			SET @StrTemp = char(9)+ 'Mapping Exists for the ' +  	@StrTrgPackage   +
										@StrTrgModule    +
										@StrTrgTable 	 +
					       ' in the Standard VRC - ' +
										@StrStdVersion 	 +
										@StrStdRelease    +
										@StrStdCustomer   +
					       ' with the status - '  +  @StrStatus

			EXEC Write_to_file @StrTemp, @I_StrLogFileName
			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
			CONTINUE
		END
	END

	--Start Updating the Records
	BEGIN TRANSACTION 	--Start the Transaction for this Table
	SET @StrTableName = @StrSrcPackage + @StrSrcModule + @StrSrcTable
	-- STEP 5: Insert a New Record in BCMAP120 And 130
	/* Insert the "Migrate <TableName> Record in BCMAP120*/

	SET @StrText =  'migrate ' + @StrTableName



	EXEC	@IntRetVal =  sp_insert_bcmap120_1 	@I_IntMprf,		--Migration Profile  				'',			--Variables						'',			--Variables
							@StrStdVersion,         --Version            				@StrComment,		--Comments 						@StrComment,		--Comments
							@StrStdRelease,         --Release            				@StrTableName		--Source Table						@StrTableName		--Source Table
							@StrStdCustomer,        --Customer
							@StrTrgPackage,         --Package
							@StrTrgModule,          --Module
							@StrTrgTable,           --Table
							@StrText,               --Precondition Text
							'',                     --Check out User
							@I_UserName,            --Last Modified User
							@CurrDateTime,          --Date
							'G',                    --Status
							'',			--Variables
							@StrComment,		--Comments
							@StrTableName		--Source Table
	IF @IntRetVal <> 0
	BEGIN
		-- ERROR Encountered ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = char(9) + 'Error -' + @StrTemp + ' -  while inserting  the record ' +
					 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrTrgPackage	+
			                                @StrTrgModule	+
			                                @StrTrgTable	+  ' ' +
			                ' into BCMAP120. '
		EXEC Write_to_file @StrTemp, @I_StrLogFileName

		ROLLBACK TRANSACTION
		FETCH NEXT FROM DDC141_Cursor
		INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
		CONTINUE
	END
	/* Insert 2 Records in BCMAP130
		1. For Select  - Select * from <TableName>
		2. SelectDo    - Dump 1*/

	SET @StrText =  'select  '  + @StrSrcPackage + @StrSrcModule + @StrSrcTable + '.*  from ' + @StrSrcPackage + @StrSrcModule + @StrSrcTable
	EXEC @IntRetVal = sp_insert_bcmap130_1 		@I_IntMprf,		--Migration Profile
							@StrStdVersion,         --Version
							@StrStdRelease,         --Release
							@StrStdCustomer,        --Customer
							@StrTrgPackage,         --Target Package
							@StrTrgModule,          --Target Module
							@StrTrgTable,           --Target Table
							@StrSrcPackage,         --Source Package
							@StrSrcModule,          --Source Module
							@StrSrcTable,           --Source Table
							'S',                    --Code for Text S/D/F/X
							@StrText                --The Text -Query

	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = char(9) + ' Error -' + @StrTemp + ' -  while inserting  the record ' +
					 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrTrgPackage	+
			                                @StrTrgModule	+
			                                @StrTrgTable	+  ' ' +
			                 ' CODE - S ' +
			                 ' into BCMAP130. '
		EXEC Write_to_file @StrTemp, @I_StrLogFileName

		ROLLBACK TRANSACTION
		FETCH NEXT FROM DDC141_Cursor
		INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
		CONTINUE
	END
	EXEC @IntRetVal = sp_insert_bcmap130_1 		@I_IntMprf,		--Migration Profile
							@StrStdVersion,         --Version
							@StrStdRelease,         --Release
							@StrStdCustomer,        --Customer
						  	@StrTrgPackage,         --Target Package
						  	@StrTrgModule,          --Target Module
						  	@StrTrgTable,           --Target Table
						  	@StrSrcPackage,         --Source Package
						  	@StrSrcModule,          --Source Module
						  	@StrSrcTable,           --Source Table
						  	'D',                    --Code for Text S/D/F/X
						  	'dump 1'		--The Text -Query
	IF @IntRetVal <> 0
	BEGIN
		--ERROR enocuntered ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = char(9) + ' Error -'   + @StrTemp + ' -  while inserting  the record ' +
					 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrTrgPackage	+
			                                @StrTrgModule	+
			                                @StrTrgTable	+  ' ' +
			                 ' CODE - D ' +
			                 ' into BCMAP130. '
		EXEC Write_to_file @StrTemp, @I_StrLogFileName

		ROLLBACK TRANSACTION
		FETCH NEXT FROM DDC141_Cursor
		INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
		CONTINUE
	END
	--If  Migration Profile is SCM enabled then Fill 125 and 135 also.
	IF @StrSCM = 'Y'
	BEGIN
		/* Inser a New Record in BCMAP110*/
		EXEC @IntRetVal = sp_insert_bcmap110_1 		@I_IntMprf,			--Migration Profile
							  	@StrStdVersion,                 --Version
							  	@StrStdRelease,                 --Release
							  	@StrStdCustomer,                --Customer
								@StrTrgPackage,                 --Package
								@StrTrgModule,                  --Module
								@StrTrgTable,                   --Table
							  	1,                              --Revision No.
							  	' Default Generation ' ,        --Revision Info.
								'DEFAULT',                      --Issue ID
								@CurrDateTime,                  --Date & Time
								0,                              --Patch NOT Exported(0)/Exported(1)
								@I_UserName                     --User Name

		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = char(9) + ' Error -'   + @StrTemp + ' -  while inserting  the record ' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @StrStdVersion	+  '-' +
				                                @StrStdRelease	+  '-' +
				                                @StrStdCustomer	+
						 ' TABLE - '  +	@StrTrgPackage	+
				                                @StrTrgModule	+
				                                @StrTrgTable	+  ' ' +
				                 ' into BCMAP110. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable

			CONTINUE
		END



		/* Insert a New Record in BCMAP125*/
		SET @StrText =  'Migrate ' + @StrSrcPackage + @StrSrcModule + @StrSrcTable
		EXEC	@IntRetVal = sp_insert_bcmap125_1 	@I_IntMprf,		--Migration Profile
								@StrStdVersion,         --Version
								@StrStdRelease,         --Release
								@StrStdCustomer,        --Customer
								@StrTrgPackage,         --Package
								@StrTrgModule,          --Module
								@StrTrgTable,           --Table
								1,                      --Revision Number
								@StrText,               --Precondition Text
								@I_UserName,            --Last Modified User
								'C',                    --Status
								'',                     --Variables
								@StrTableName           --Source Table

		IF @IntRetVal <> 0
		BEGIN
			--ERROR encountered ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = char(9) + ' Error -'   + @StrTemp + ' -  while inserting  the record ' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @StrStdVersion	+  '-' +
				                                @StrStdRelease	+  '-' +
				                                @StrStdCustomer	+
						 ' TABLE - '  +	@StrTrgPackage	+
				                                @StrTrgModule	+
				                                @StrTrgTable	+  ' ' +
				                 ' into BCMAP125. '
			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION
			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
			CONTINUE
		END
		/* Insert 2 Records in BCMAP135
			1. For Select
			2. SelectDo */
		/* Insert  'Select * from  <TableName> in BCMAP135 */

		SET @StrText =  'select  '  + @StrSrcPackage + @StrSrcModule + @StrSrcTable + '.*  from ' + @StrSrcPackage + @StrSrcModule + @StrSrcTable    -- LND2-26093.c
		EXEC @IntRetVal = sp_insert_bcmap135_1 		@I_IntMprf,		--Migration Profile
								@StrStdVersion,         --Version
								@StrStdRelease,         --Release
								@StrStdCustomer,        --Customer
								@StrTrgPackage,         --Target Package
								@StrTrgModule,          --Target Module
								@StrTrgTable,           --Target Table
								@StrSrcPackage,         --Source Package
								@StrSrcModule,          --Source Module
								@StrSrcTable,           --Source Table
								'S',                    --Code for Text S/D/F/X
								1,                      --Revision Code
								@StrText,               --The Text -Query
								@I_UserName,            --User Name
								'C'			--Change Code
		IF @IntRetVal <> 0
		BEGIN
			--ERROR encountered ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = char(9) + ' Error -' + @StrTemp + ' -  while inserting  the record ' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @StrStdVersion	+  '-' +
				                                @StrStdRelease	+  '-' +
				                                @StrStdCustomer	+
						 ' TABLE - '  +	@StrTrgPackage	+
				                                @StrTrgModule	+
				                                @StrTrgTable	+  ' ' +
				                 ' CODE - S ' +
				                 ' into BCMAP135. '
			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION
			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
			CONTINUE
		END
		/* Insert 'dump 1'  text into BCMAP135 */
		EXEC @IntRetVal = sp_insert_bcmap135_1 		@I_IntMprf,		--Migration Profile
								@StrStdVersion,         --Version
								@StrStdRelease,         --Release
								@StrStdCustomer,        --Customer
								@StrTrgPackage,         --Target Package
								@StrTrgModule,          --Target Module
								@StrTrgTable,           --Target Table
								@StrSrcPackage,         --Source Package
								@StrSrcModule,          --Source Module
								@StrSrcTable,           --Source Table
								'D',                    --Code for Text S/D/F/X
								1,                      -- Revision Code
								'dump 1	',              --The Text -Query
								@I_UserName,            --User Name
								'C'                     --Change Code
		IF @IntRetVal <> 0
		BEGIN
			--ERROR encountered ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = char(9) + ' Error -'   + @StrTemp + ' -  while inserting  the record ' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @StrStdVersion	+  '-' +
				                                @StrStdRelease	+  '-' +
				                                @StrStdCustomer	+
						 ' TABLE - '  +	@StrTrgPackage	+
				                                @StrTrgModule	+
				                                @StrTrgTable	+  ' ' +
				                 ' CODE - D ' +
				                 ' into BCMAP135. '
			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION
			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
			CONTINUE
		END
	END
	EXEC @IntRetVal = sp_CreateMappingsForFineTunedChangedTables 	@StrTargetAppl,
									@StrSourceAppl,
									@StrStdVersion,
									@StrStdRelease,
									@StrStdCustomer,
									@StrSCM,
									@I_IntRunn,
									@I_IntMprf,
									@StrSrcPackage,
									@StrSrcModule,
									@StrSrcTable,
									@StrTrgPackage,
									@StrTrgModule,
									@StrTrgTable,
									@I_UserName,
									@I_StrLogFileName
	IF @IntRetVal <> 0
	BEGIN
		--ERROR encountered ROLLBACK and Continue with the Next Table


		ROLLBACK TRANSACTION
		FETCH NEXT FROM DDC141_Cursor
		INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
		CONTINUE
	END
	COMMIT TRANSACTION
	SET @IntTableCount = @IntTableCount + 1
	SET @StrTemp = CHAR(9) + 'Successfully completed Default Mapping generation for - ' + @StrTableName
	EXEC Write_to_file  @StrTemp, @I_StrLogFileName
	FETCH NEXT FROM DDC141_Cursor
	INTO   @StrSrcPackage, @StrSrcModule, @StrSrcTable,@StrTrgPackage, @StrTrgModule, @StrTrgTable
END
CLOSE DDC141_Cursor
DEALLOCATE DDC141_Cursor
IF @IntTableCount = 0
BEGIN
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
	SET @StrTemp = CHAR(9) +  'Default Mapping Generation for FINE TUNED Tables Not Done due to any of the following reasons. '
	EXEC Write_to_file @StrTemp,@I_StrLogFileName
	SET @StrTemp = CHAR(9) + CHAR(9) + '1. Mapping Already exists for the tables in the selcted Range.'
	EXEC Write_to_file @StrTemp, @I_StrLogFileName
	SET @StrTemp = CHAR(9) + CHAR(9) + '2. FINE TUNE was not done for the Tables in the selected Range.'
	EXEC Write_to_file @StrTemp, @I_StrLogFileName
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
END
ELSE
BEGIN
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
	SET @StrTemp = 'Default Mapping Generation (FINE TUNED Tables) is successfully done for = ' + LTRIM(STR(@IntTableCount)) + ' table(s).' + char(9) + 'END TIME   ' + CAST(GETDATE() as varchar)
	EXEC Write_to_file @StrTemp,@I_StrLogFileName
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
END
RETURN @@ERROR



GO
/* LND2-26093.en */


/* LND2-26093.sn */
/********************************************************************************
*	NAME:		sp_CreateMappingForUnchangedTables			*
*	DESCRPTION:	This Stored procedure generates the default mappings    *
*			for Unchaged Tables.					*
*	CALLS:		1. sp_insert_bcmap140_1()				*
*			2. sp_insert_bcmap145_1()				*
*	CALLED BY:	sp_CreateDefaultMappings()				*
*	UPDATION								*
*			-Add a Parameter @I_UserName [varchar](12)-31/Oct/01    *
*			-Replace "root" by @I_UserName	-31/Oct/01 		*
********************************************************************************/
CREATE PROCEDURE [sp_CreateMappingForUnchangedTables]
(
	  @I_StrTargetappl 	[varchar](15),
	  @I_StrSourceappl 	[varchar](15),
	  @I_StrVer  		[varchar](4),
	  @I_StrRel  		[varchar](2),
	  @I_StrCust  		[varchar](4),
	  @I_StrSCM  		[varchar](1),
	  @I_IntRunn  		[numeric](10,5),
	  @I_IntMprf   		[int],
	  @I_StrPackage  	[varchar](2),
	  @I_StrModule   	[varchar](3),
	  @I_StrTable   	[varchar](3),
	  @I_UserName		[varchar](12),
   	  @I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
	   )
	-- LND2-26093.c  Parameter @I_IntRunn changed
 AS
	 DECLARE @StrField   		as varchar(15)
	 DECLARE @StrTempField  	as varchar(15)
	 DECLARE @StrText   		as varchar(1024)
	 DECLARE @StrTrgDomain 		as varchar(14)
	 DECLARE @StrTrgDType 		as varchar(1)
	 DECLARE @IntTrgDLen  		as int
	 DECLARE @IntSrcDepth  		as int
	 DECLARE @IntTrgDepth  		as int
	 DECLARE @IntLoopCount 		as int
	 DECLARE @IntRetVal 		as int
	 DECLARE @StrTemp		as varchar(1024)
	 DECLARE @StrTrgInitialValue	as nvarchar(60)

	 SET @IntRetVal = 0

	 /*Get all the Unchanged Tables Records from BCDDC150*/
	 DECLARE DDC150_Cursor CURSOR FOR

	 SELECT  	[dbo].[bcddc150].fdnm,
	   		[dbo].[bcddc150].tddo,
	   		[dbo].[bcddc150].sdep,
	   		[dbo].[bcddc150].tdep,
	   		[dbo].[bcddc150].tini
	 FROM   	[dbo].[bcddc150]
	 WHERE 		[dbo].[bcddc150].runn  	= @I_IntRunn
	 AND   		[dbo].[bcddc150].ctyp 	= 'U'
	 AND    	[dbo].[bcddc150].cpac  	= @I_StrPackage
	 AND  		[dbo].[bcddc150].cmod  	= @I_StrModule
	 AND  		[dbo].[bcddc150].flno  	= @I_StrTable
	 ORDER BY  	[dbo].[bcddc150].fdnm

	 OPEN 	DDC150_Cursor
	 FETCH	NEXT FROM DDC150_Cursor
	 INTO   @StrField, @StrTrgDomain, @IntSrcDepth, @IntTrgDepth, @StrTrgInitialValue

	 WHILE 	@@FETCH_STATUS = 0
	 BEGIN
		  SET @StrTempField = @StrField
 		  SET @StrTrgInitialValue = ISNULL(@StrTrgInitialValue,'0')
		  /*Get the TARGET Domain Information */
		  SELECT 	@StrTrgDType  = [dbo].[bcddi220].type,
		    		@IntTrgDLen = [dbo].[bcddi220].leng
		  FROM  	[dbo].[bcddi220]
		  WHERE 	[dbo].[bcddi220].appl = @I_StrTargetappl
		  AND  		(([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrTrgDomain)


		  /*Check for the Depth of the Field*/
		  WHILE @IntTrgDepth >= 0
		  BEGIN

		  	SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrField

			IF  @IntTrgDepth > @IntSrcDepth
			BEGIN
				IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
				BEGIN
					IF @StrTrgInitialValue = '0'
					BEGIN
						SET @StrTrgInitialValue = 'empty.string(1;' + LTRIM(STR(@IntTrgDLen)) + ')'
					END
				END
		        	SET @StrText = CASE @StrTrgDType
						        WHEN 'B' THEN @StrTrgInitialValue
						        WHEN 'I' THEN @StrTrgInitialValue
						        WHEN 'L' THEN @StrTrgInitialValue
						        WHEN 'D' THEN @StrTrgInitialValue
						        WHEN 'F' THEN @StrTrgInitialValue
						        WHEN 'S' THEN @StrTrgInitialValue
						        WHEN 'E' THEN @StrTrgInitialValue
						        WHEN 'T' THEN 'utc(0,0,2)'
						        WHEN 'U' THEN @StrTrgInitialValue
						        WHEN 'M' THEN @StrTrgInitialValue
						        WHEN 'K' THEN 'utc(0,0)'
						        ELSE '0'
 		 		          	  END
			     SET @StrField = @StrTempField + '(' + LTRIM(STR(@IntTrgDepth)) + ')'
		        END

			ELSE
			BEGIN

			     IF @IntTrgDepth >= 1
			     BEGIN
				SET @StrField = @StrTempField + '(' +  LTRIM(STR(@IntTrgDepth)) + ')'

				--check whether the Target is a string. If so add "1,1" to the value
				IF @StrTrgDType = 'S' or @StrTrgDType = 'M'
				BEGIN
					SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrTempField + '(1,' +  LTRIM(STR(@IntTrgDepth)) + ')'
				END
				ELSE
				BEGIN
					SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrField
				END
			     END

			     SET @StrText= @StrText
			     SET @StrText = CASE  @StrTrgDType
					        WHEN  'E' THEN  @StrText	--Enum Data Type
					        WHEN  'B' THEN  @StrText 	--Byte Data Type
					        WHEN  'K' THEN  'utc.utc(' + @StrText +')'   --UTC Data Type
						WHEN  'M' THEN  @StrText 	-- Multi - byte
-- Cycle3 .o					WHEN  'T' THEN  'utc(' + @StrText + '0,2)' -- Date
						WHEN  'T' THEN  'utc(' + @StrText + ',0,2)' -- Date --Cycle3 .n
					        ELSE  @StrText
					       END
			     END

			     EXEC @IntRetVal = sp_insert_bcmap140_1
					      @I_IntMprf,  	-- MPRF
					      @I_StrVer,  	--version
					      @I_StrRel,  	--Release
					      @I_StrCust,  	--Customer
					      @I_StrPackage,  	--Package
					      @I_StrModule,  	--Module
					      @I_StrTable ,  	--Table
					      1,   		--Dump Number
					      @StrField,  	--Field Name
					      @StrText,  	--Field  Value
					      0,   		--Type (Hard coded)
					      'N'   		--Not Approved

			     IF @IntRetVal <> 0
			     BEGIN
				SELECT @StrTemp = [master].[dbo].[sysmessages].description
				FROM   [master].[dbo].[sysmessages]
				WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

				SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @I_StrVer	+  '-' +
				                                @I_StrRel	+  '-' +
				                                @I_StrCust	+
						 ' TABLE - '  +	@I_StrPackage	+
				                                @I_StrModule	+
				                                @I_StrTable	+  ' ' +
				                 ' FIELD - '  + @StrField  	+
				                 ' into BCMAP140. '

				EXEC Write_to_file @StrTemp, @I_StrLogFileName
				RETURN @IntRetVal
			     END

			     -- If the Migration Profile is SCM enabled then Insert a rec. in BCMAP145.
			     IF @I_StrSCM = 'Y'
			     BEGIN
				    EXEC @IntRetVal = sp_insert_bcmap145_1
						       @I_IntMprf,  	--Mprf
						       @I_StrVer,  	--version
						       @I_StrRel,  	--Release
						       @I_StrCust,  	--Customer
						       @I_StrPackage,  	--Package
						       @I_StrModule,  	--Module
						       @I_StrTable ,  	--Table
						       1,   		--Dump No.
						       @StrField,  	--Field Name
						       1,   		--Revision Number
						       @StrText,  	--Field  Value
						       0,   		--Hard coded
						       @I_UserName,  	--Login Id
						      'N',  		--Not Approved
						      'C'		--Changed
				    IF @IntRetVal <> 0
				    BEGIN
					SELECT @StrTemp = [master].[dbo].[sysmessages].description
					FROM   [master].[dbo].[sysmessages]
					WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

					SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
							 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
						         ' VRC  - '   + @I_StrVer	+  '-' +
					                                @I_StrRel	+  '-' +
					                                @I_StrCust	+
							 ' TABLE - '  +	@I_StrPackage	+
					                                @I_StrModule	+
					                                @I_StrTable	+  ' ' +
					                 ' FIELD - '  + @StrField  	+
					                 ' into BCMAP145. '

					EXEC Write_to_file @StrTemp, @I_StrLogFileName
					RETURN @IntRetVal
				    END
			    END

			    SET @IntTrgDepth = @IntTrgDepth - 1
			    IF @IntTrgDepth = 0 BREAK    -- This is to Avoid one more looping
		  END

		  FETCH NEXT FROM DDC150_Cursor
		  INTO   @StrField, @StrTrgDomain, @IntSrcDepth, @IntTrgDepth, @StrTrgInitialValue
	 END

	 CLOSE DDC150_Cursor

	 DEALLOCATE DDC150_Cursor

	 RETURN @IntRetVal



GO
/* LND2-26093.en */

/* LND2-26093.sn */
/********************************************************************************
* 	NAME:		sp_LegacyCreateDefaultMappings			      	*
*	DESCRIPTION:	This Strored procedure, generates the Default mappings	*
*			for the tables for which Comparison has already been  	*
*			done.						      	*
*	Inputs:		The Runn Number, the Migration Profile, the Start pack	*
*			module and Table to the target Pack, Mod and Table.   	*
*	Calls:		The Following procedures are called.		      	*
*			1. sp_insert_bcmap120_1					*
*			2. sp_insert_bcmap125_1					*
*			3. sp_insert_bcmap130_1					*
*			4. sp_insert_bcmap135_1					*
*			5. sp_CreateMappingsForFineTunedChangedTables		*
*			6. sp_update_bcddc000_1					*
*	UPDATION 								*
*		- Adding  a Parameter @I_UserName[varchar](12)-31/oct/2001	*
*		- Call to sp_insert_bcmap110_1() is added-31/oct/2001		*
********************************************************************************/
CREATE PROCEDURE [sp_LegacyCreateDefaultMappings]
	(

	@I_IntRunn 		[numeric](10,5),
	@I_IntMprf 		[int],
	@I_StrPackFrom 		[varchar](2),
	@I_StrModFrom 		[varchar](3),
	@I_StrTableFrom 	[varchar](60),
	@I_StrPackTo 		[varchar](2),
	@I_StrModTo		[varchar](3),
	@I_StrTableTo		[varchar](60),
	@I_UserName		[varchar](12),
	@I_DMGChangedTables	[int] = 1,
	@I_DMGUnChangedTables	[int] = 1,
	@I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
	)
	-- LND2-26093.c  Parameter @I_IntRunn changed
AS
DECLARE @RecCntMAP120 		as int			--Record Count of BCMAP120
DECLARE @IntRetVal		as int			--Return Value
DECLARE	@IntCount		as int			--Counter
DECLARE @IntTableCount		as int			--Table Count
DECLARE @StrPackage 		as varchar(2)		--Package
DECLARE @StrModule 		as varchar(3)		--Module
DECLARE @StrTable 		as varchar(60)		--Table
DECLARE @StrCtype 		as varchar(1)		--Ctype -U/C/N/D
DECLARE @StrTargetAppl		as varchar(15)		--Target Application
DECLARE @StrSourceAppl		as varchar(15)		--Source Application
DECLARE @StrStdVersion		as varchar(4)		--Standard Version
DECLARE @StrStdRelease		as varchar(2)		--Standard Release
DECLARE @StrStdCustomer		as varchar(4)		--Standard Customer
DECLARE	@StrDevVersion		as varchar(4)		--Development Version
DECLARE	@StrDevRelease		as varchar(2)		--Development Release
DECLARE	@StrDevCustomer		as varchar(4)		--Development Customer
DECLARE @StrSCM			as varchar(1)		--SCM enabled for the Mig. Profile
DECLARE @StrText		as varchar(1024)	--Text
DECLARE @StrTableName		as varchar(60)		--To Store Table Name
DECLARE @CurrDateTime		as datetime		--Current Date and Time
DECLARE	@StrComment		as varchar(2000)	--Variable to hold Pretext
DECLARE @StrStatus		as varchar(1)		--Status
DECLARE @StrUnChanged		as varchar(1)		--Status - Unchanged
DECLARE @StrChanged		as varchar(1)		--Status - Changed
DECLARE @StrTemp		as varchar(1024)	--Temporary String
/* STEP 1. Get the Target Application Name & Mprf VRC details.*/
SELECT 	@StrTargetAppl 	= [dbo].[bcmap100].tapp,
	@StrSourceAppl	= [dbo].[bcmap100].sapp,
	@StrStdVersion	= [dbo].[bcmap100].vers,
	@StrStdRelease	= [dbo].[bcmap100].rele,
	@StrStdCustomer	= [dbo].[bcmap100].cust,
	@StrSCM		= [dbo].[bcmap100].scme
FROM 	[dbo].[bcmap100]
WHERE 	[dbo].[bcmap100].mprf = @I_IntMprf
/* STEP 2. Get the Development VRC from BCMAP 105 */
SELECT 	@StrDevVersion	= [dbo].[bcmap105].dver,
	@StrDevRelease	= [dbo].[bcmap105].drel,
	@StrDevCustomer	= [dbo].[bcmap105].dcus
FROM	[dbo].[bcmap105]
WHERE	[dbo].[bcmap105].mprf = @I_IntMprf
AND	[dbo].[bcmap105].vers = @StrStdVersion
AND	[dbo].[bcmap105].rele = @StrStdRelease
AND	[dbo].[bcmap105].cust = @StrStdCustomer

--If there is no Dev. VRC then set the Var to empty
SET @StrDevVersion = ISNULL(@StrDevVersion,'')
SET @StrDevRelease = ISNULL(@StrDevRelease,'')
SET @StrDevCustomer = ISNULL(@StrDevCustomer,'')
--IF Generation is for Changed Tables then Set the Flag.
IF (@I_DMGChangedTables = 1)
BEGIN
	SET @StrChanged ='C'
END
ELSE
BEGIN
	SET @StrChanged =''
END
--IF Generation is for UnChanged Tables then Set the Flag.
IF (@I_DMGUnChangedTables = 1 )
BEGIN
	SET @StrUnChanged ='U'
END
ELSE
BEGIN
	SET @StrUnChanged =''
END
SET @CurrDateTime = GETDATE()
EXEC Write_to_file '***********************************************************************************************************************', @I_StrLogFileName,1
SET @StrTemp=   'START DEFAULT MAPPING GENERATION '  + CHAR(9) + ' START TIME ' + CAST(@CurrDateTime as varchar)
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =  'Range FROM ' +	@I_StrPackFrom 	+ @I_StrModFrom + @I_StrTableFrom + '  TO   '  +
						@I_StrPackTo 	+ @I_StrModTo	+ @I_StrTableTo
EXEC Write_to_file @StrTemp, @I_StrLogFileName

SET @StrTemp =  'Migration Profile- '  +  LTRIM(STR(@I_IntMprf))
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =   'Run Number- '  +  LTRIM(STR(@I_IntRunn))
EXEC Write_to_file @StrTemp, @I_StrLogFileName
SET @StrTemp =  'User Name- '  + @I_UserName
EXEC Write_to_file @StrTemp, @I_StrLogFileName
EXEC Write_to_file '***********************************************************************************************************************', @I_StrLogFileName
SET @IntTableCount = 0
/*STEP 3. Generate Default Mappings for Unchanged and  Changed  Tables*/
DECLARE DDC140_Cursor CURSOR FOR
SELECT 	[dbo].[bcldc140].cpac,
	[dbo].[bcldc140].cmod,
	[dbo].[bcldc140].flno,
	[dbo].[bcldc140].ctyp
FROM 	[dbo].[bcldc140]
WHERE	[dbo].[bcldc140].runn = @I_IntRunn
AND 	([dbo].[bcldc140].ctyp IN (@StrUnChanged, @StrChanged))
AND  	([dbo].[bcldc140].cpac  BETWEEN @I_StrPackFrom AND @I_StrPackTo )
AND	([dbo].[bcldc140].cmod  BETWEEN @I_StrModFrom  AND @I_StrModTo)
AND	([dbo].[bcldc140].flno  BETWEEN @I_StrTableFrom AND @I_StrTableTo )
OPEN DDC140_Cursor
FETCH NEXT FROM DDC140_Cursor
INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @StrComment = ''
	SET @StrStatus = ''


	/* If some mappings are present for the given Range (@RecCntMAP120 <> 0) then check
	   whether mapping present for the Curent Table. If no mappings then Generate */
	SELECT 	@StrComment= [dbo].[bcmap120].comt,
		@StrStatus = [dbo].[bcmap120].stat
	FROM   	[dbo].[bcmap120]
	WHERE  	[dbo].[bcmap120].mprf 	= 	@I_IntMprf
	AND	[dbo].[bcmap120].vers 	= 	@StrStdVersion
	AND	[dbo].[bcmap120].rele 	= 	@StrStdRelease
	AND	[dbo].[bcmap120].cust 	= 	@StrStdCustomer
	AND  	[dbo].[bcmap120].tcpac 	=	@StrPackage
	AND	[dbo].[bcmap120].tcmod 	=	@StrModule
	AND	[dbo].[bcmap120].tflno 	=	@StrTable

	SET @StrComment = ISNULL(@StrComment,'')
	SET @StrStatus = ISNULL(@StrStatus,'')


	IF (@StrStatus ='' OR @StrStatus ='N')
	BEGIN
		--check whether a rec. is there in DVRC.
		--If DVRC is present then Skip.
		SELECT 	@IntCount = Count(*)
		FROM   	[dbo].[bcmap120]
		WHERE  	[dbo].[bcmap120].mprf 	=	@I_IntMprf
		AND  	[dbo].[bcmap120].tcpac 	=	@StrPackage
		AND	[dbo].[bcmap120].tcmod 	=	@StrModule
		AND	[dbo].[bcmap120].tflno 	=	@StrTable
		AND	[dbo].[bcmap120].vers	=	@StrDevVersion
		AND	[dbo].[bcmap120].rele	=	@StrDevRelease
		AND	[dbo].[bcmap120].cust	=	@StrDevCustomer

		IF (@IntCount > 0)
		BEGIN
			--Write in Log file - Mapping Already Exist
			SET @StrTemp = CHAR(9) +'Mapping Exists for the ' +  	@StrPackage      +
										@StrModule       +
										@StrTable 	 +
					       ' In the Development VRC - ' +
										@StrDevVersion +
										@StrDevRelease +
										@StrDevCustomer
			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
			CONTINUE
		END

	END
	ELSE
	BEGIN
		IF ( @StrStatus <> 'N')
		BEGIN
			SET @StrTemp = CHAR(9)+ 'Mapping Exists for the ' +  	@StrPackage      +

										@StrModule       +
										@StrTable 	 +
					       ' in the Standard VRC - ' +
										@StrStdVersion 	 +
										@StrStdRelease    +
										@StrStdCustomer   +
					       ' with the status - '  +  @StrStatus

			EXEC Write_to_file @StrTemp, @I_StrLogFileName
			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
			CONTINUE
		END
	END


	BEGIN TRANSACTION
	-- STEP 4. Insert a New Record in BCMAP120/125,130/135

	/* Insert a Record in BAMAP120*/
	SET @StrTableName =  @StrTable
	SET @StrText =  'migrate ' + @StrTableName
	EXEC @IntRetVal = sp_insert_bcmap120_1 	@I_IntMprf,		--Migration Profile
						@StrStdVersion,		--Version
						@StrStdRelease,		--Release
						@StrStdCustomer,	--Customer
						@StrPackage,		--Package
						@StrModule,		--Module
						@StrTable,		--Table
						@StrText,		--Precondition Text
						'',			--Check out User
						@I_UserName,		--Last Modified User
						@CurrDateTime,		--Date
						'G',			--Status
						'',			--Variables
						@StrComment,		--Comments
						@StrTableName		--Source Table
	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered: ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = CHAR(9) + 'Error -' + @StrTemp + ' -  while inserting  the record ' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP120. '
		EXEC Write_to_file @StrTemp, @I_StrLogFileName

		ROLLBACK TRANSACTION


		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

		CONTINUE
	END

	/* STEP 5. Insert 2 Records in BCMAP130
		1. For Select
		2. SelectDo */

	SET @StrText ='select  ' +  @StrTableName + '.* ' + CHAR(13)  + CHAR(10) + 'from  ' + @StrTableName    -- LND2-26093.c
	EXEC @IntRetVal = sp_insert_bcmap130_1  @I_IntMprf,		--Migration Profile
						@StrStdVersion,       	--Version
						@StrStdRelease,       	--Release
						@StrStdCustomer,      	--Customer
						@StrPackage,    	--Target Package
						@StrModule,     	--Target Module
						@StrTable,      	--Target Table
						@StrPackage,    	--Source Package
						@StrModule,     	--Source Module
						@StrTable,      	--Source Table
						'S',            	--Code for Text S/D/F/X
						@StrText        	--The Text -Query

	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered: ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                 ' CODE  - S ' +
	 		                 ' into BCMAP130. '

		EXEC Write_to_file @StrTemp, @I_StrLogFileName
		ROLLBACK TRANSACTION

		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

		CONTINUE
	END
	EXEC @IntRetVal = sp_insert_bcmap130_1 	@I_IntMprf,	--Migration Profile
						@StrStdVersion,	--Version
						@StrStdRelease,        --Release
						@StrStdCustomer,       --Customer
						@StrPackage,    --Target Package
						@StrModule,     --Target Module
						@StrTable,      --Target Table
						@StrPackage,    --Source Package
						@StrModule,     --Source Module
						@StrTable,      --Source Table
						'D',            --Code for Text S/D/F/X
						'dump 1'        --The Text -Query
	IF @IntRetVal <> 0
	BEGIN
		--ERROR Encountered: ROLLBACK and Continue with the Next Table
		SELECT @StrTemp = [master].[dbo].[sysmessages].description
		FROM   [master].[dbo].[sysmessages]
		WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

		SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                 ' CODE - D ' +
			                 ' into BCMAP130. '
		ROLLBACK TRANSACTION

		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

		CONTINUE
	END
	--STEP 6. If Migration is SCM enabled then insert records into 125 & 135

	IF @StrSCM = 'Y'
	BEGIN
		/* Inser a New Record in BCMAP110*/
		EXEC @IntRetVal = sp_insert_bcmap110_1 		@I_IntMprf,		--Migration Profile
								@StrStdVersion,     	--Version
								@StrStdRelease,     	--Release
								@StrStdCustomer,    	--Customer
								@StrPackage, 		--Package
								@StrModule,  		--Module
								@StrTable,   		--Table
								1,           		--Revision No.
								'Default Generation',   --Revision Info.
								'DEFAULT',		--Issue ID
								@CurrDateTime,		--Date & Time
								0,			--Patch NOT Exported(0)/Exported(1)
								@I_UserName		--User Name

		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP110. '

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END

		/* Insert a New Record in BCMAP125*/
		SET @StrText =  'migrate ' + @StrTableName
		EXEC	@IntRetVal = sp_insert_bcmap125_1 	@I_IntMprf,	        --Migration Profile
								@StrStdVersion,         --Version
								@StrStdRelease,         --Release
								@StrStdCustomer,        --Customer
								@StrPackage,    	--Package
								@StrModule,     	--Module
								@StrTable,      	--Table
								1,              	--Revision Number
								@StrText,       	--Precondition Text
								@I_UserName,    	--Last Modified User
								'C',            	--Status
								'',             	--Variables
								@StrTableName   	--Source Table

		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP125. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END

		/* Insert 2 Records in BCMAP135
			1. For Select
			2. SelectDo */
		SET @StrText =  'select  ' +  @StrTableName + '.* ' + CHAR(13)  + CHAR(10) + 'from  ' + @StrTableName    -- LND2-26093.c
		EXEC @IntRetVal = sp_insert_bcmap135_1 	@I_IntMprf,		--Migration Profile
							@StrStdVersion,        --Version
							@StrStdRelease,        --Release
							@StrStdCustomer,       --Customer
							@StrPackage,    	--Target Package
							@StrModule,     	--Target Module
							@StrTable,      	--Target Table
							@StrPackage,    	--Source Package
							@StrModule,     	--Source Module
							@StrTable,      	--Source Table
							'S',            	--Code for Text S/D/F/X
							1,              	-- REvision Code
							@StrText,		--The Text -Query
							@I_UserName,		--User Name
							'C'			--Change Code
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP135. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
		--STEP Insert Second Record
		EXEC  @IntRetVal = sp_insert_bcmap135_1 	@I_IntMprf,	       	--Migration Profile
								@StrStdVersion,        	--Version
								@StrStdRelease,        	--Release
								@StrStdCustomer,       	--Customer
								@StrPackage,    	--Target Package
								@StrModule,     	--Target Module
								@StrTable,      	--Target Table
								@StrPackage,    	--Source Package
								@StrModule,     	--Source Module
								@StrTable,      	--Source Table
								'D',            	--Code for Text S/D/F/X
								1,              	-- REvision Code
								'dump 1',       	--The Text -Query
								@I_UserName,    	--User Name
								'C'             	--Change Code
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			SELECT @StrTemp = [master].[dbo].[sysmessages].description
			FROM   [master].[dbo].[sysmessages]
			WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

			SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
					 ' MPRF - '   + LTRIM(STR(@I_IntMprf))	+  ' ' +
				         ' VRC  - '   + @StrStdVersion	+  '-' +
			                                @StrStdRelease	+  '-' +
			                                @StrStdCustomer	+
					 ' TABLE - '  +	@StrPackage	+
			                                @StrModule	+
			                                @StrTable	+  ' ' +
			                ' into BCMAP135. '

			EXEC Write_to_file @StrTemp, @I_StrLogFileName

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
	END


	--STEP 7. If the Table is Unchanged then Call Unchangedtables Procedure

	IF @StrCtype = 'U'
	BEGIN

		EXEC @IntRetVal = sp_LegacyCreateMappingForUnchangedTables 	@StrTargetAppl, 	--Target Application
									@StrSourceAppl	,	--Source Application
									@StrStdVersion,		--Version
									@StrStdRelease,		--Release
									@StrStdCustomer,	--Customer
									@StrSCM,		--SCM
									@I_IntRunn, 		--Runn
									@I_IntMprf, 		--Migration Profile
									@StrPackage, 		--Package
									@StrModule, 		--Module
									@StrTable,		--Table
									@I_UserName,		--User Name
									@I_StrLogFileName	--LogFileName
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table
			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
	END

	--STEP 8. IF the Table is a changed Table then Call Changed tables Proc.

	IF @StrCtype = 'C'
	BEGIN
		EXEC @IntRetVal = sp_LegacyCreateMappingForChangedTables 	@StrTargetAppl, 	--Target Application
									@StrSourceAppl,         --Source Application
									@StrStdVersion,		--Version
									@StrStdRelease,         --Release
									@StrStdCustomer,        --Customer
									@StrSCM,                --SCM
									@I_IntRunn,             --Runn
									@I_IntMprf,             --Migration Profile
									@StrPackage,            --Package
									@StrModule,             --Module
									@StrTable,              --Table
									@I_UserName,            --User Name
									@I_StrLogFileName	--LogFile Name
		IF @IntRetVal <> 0
		BEGIN
			--ERROR Encountered: ROLLBACK and Continue with the Next Table

			ROLLBACK TRANSACTION

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable,@StrCtype

			CONTINUE
		END
	END
	COMMIT TRANSACTION  -- Commit the Transaction
	SET @IntTableCount = @IntTableCount + 1
	SET @StrTemp = CHAR(9) + 'Successfully completed Default Mapping for - ' + @StrTableName
	EXEC Write_to_file  @StrTemp, @I_StrLogFileName

	FETCH NEXT FROM DDC140_Cursor
	INTO   @StrPackage, @StrModule, @StrTable,@StrCtype
END
CLOSE DDC140_Cursor
DEALLOCATE DDC140_Cursor
IF @IntTableCount = 0
BEGIN
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
	SET @StrTemp = CHAR(9) +  'Default Mapping Generation for Tables Not Done due to any of the following reasons. '
	EXEC Write_to_file @StrTemp,@I_StrLogFileName
	SET @StrTemp = CHAR(9) + CHAR(9) + '1. Mapping Already exists for the tables in the selcted Range.'
	EXEC Write_to_file @StrTemp, @I_StrLogFileName
	SET @StrTemp = CHAR(9) + CHAR(9) + '2. DD Comparison was not done for the Tables in the selected Range.'
	EXEC Write_to_file @StrTemp, @I_StrLogFileName
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
END
ELSE
BEGIN
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
	SET @StrTemp = CHAR(9) + 'Default Mapping Generation is successfully done for = ' + LTRIM(STR(@IntTableCount)) + ' table(s).' + char(9) + 'END TIME   ' + CAST(GETDATE() as varchar)
	EXEC Write_to_file @StrTemp,@I_StrLogFileName
	EXEC Write_to_file '----------------------------------------------------------------------------------------------------------------------',@I_StrLogFileName
END
RETURN @@ERROR
GO

/* LND2-26093.en */

/* LND2-26093.sn */
 CREATE PROCEDURE [sp_LegacyCreateMappingForChangedTables]
 (
	  @I_StrTargetappl 	[varchar](15),
	  @I_StrSourceappl 	[varchar](15),
	  @I_StrVer  		[varchar](4),
	  @I_StrRel  		[varchar](2),
	  @I_StrCust  		[varchar](4),
	  @I_StrSCM  		[varchar](1),
	  @I_IntRunn  		[numeric](10,5),
	  @I_IntMprf   		[int],
	  @I_StrPackage  	[varchar](2),
	  @I_StrModule   	[varchar](3),
	  @I_StrTable  		[varchar](60),
	  @I_UserName		[varchar](12),
   	  @I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
 )
	-- LND2-26093.c Parameter @I_IntRunn changed
 AS

	 DECLARE @StrField    		as varchar(60)		--Field Name.
	 DECLARE @StrTempField  	as varchar(60)		--Temporary Var. to hold.
	 DECLARE @StrText    		as varchar(1024)	--Text to hold the Generated rule.
	 DECLARE @StrTempTableName  	as varchar(60)		--Temp Var. to hold Table Name.
	 DECLARE @StrTempTableName1 	as varchar(60)		--Temp Var. to hold Table Name.
	 DECLARE @StrFType   		as varchar(1)		--Var. to hold the Field Type.
	 DECLARE @IntRetVal		as int			--Var. to hold the Return Value.
	 DECLARE @StrTemp   		as varchar(1024)	--Temporary Variable
	 DECLARE @StrVarName  		as varchar(100)	 	--Variable to hold the Type 2 variable name.


	 /*STEP 1. Get all the Unchanged/Changed and New Fields Records from BCLDC150*/

	 DECLARE LDC150_Cursor CURSOR FOR

	 SELECT 	[dbo].[bcldc150].flno,
			[dbo].[bcldc150].fdnm,
			[dbo].[bcldc150].ttyp
	 FROM   	[dbo].[bcldc150]
	 WHERE 		[dbo].[bcldc150].runn  = @I_IntRunn AND [dbo].[bcldc150].ctyp <> 'R'  AND [dbo].[bcldc150].ctyp <> 'N'
	 AND    	[dbo].[bcldc150].cpac  = @I_StrPackage
	 AND  		[dbo].[bcldc150].cmod  = @I_StrModule
	 AND  		[dbo].[bcldc150].flno  = @I_StrTable
	 ORDER BY  	[dbo].[bcldc150].fdnm,
	   		[dbo].[bcldc150].ctyp

	 OPEN LDC150_Cursor
	 FETCH NEXT FROM LDC150_Cursor
	 INTO    @StrTempTableName1,@StrField,@StrFType

	 WHILE 	  @@FETCH_STATUS = 0
	 BEGIN


			         Set @StrText = CASE  @StrFType
					        WHEN 'T'  THEN  'to_date(' + @StrTempTableName1 + '.' + @StrField + ')'
					        ELSE @StrTempTableName1 + '.' + @StrField
				           	END



		   --Insert the Field values into BCMAP140

			   EXEC @IntRetVal = sp_insert_bcmap140_1 	@I_IntMprf,		--Migration Profile
					   				@I_StrVer,		--Version
					   				@I_StrRel,		--Release
					   				@I_StrCust,		--Customer
				   					@I_StrPackage,		--Package
				   					@I_StrModule,		--Module
				        			 	@I_StrTable,		--Table
							        	1,			--Dump Number
						        		@StrField,		--Field Name
						        		@StrText,		--Field Value - Text
							        	0,			--Type of the Value Type 0/1
							        	'N'			-- N- Not Approved  Rule not Approved

			   IF (@IntRetVal <> 0)
			   BEGIN
				-- Error So BREAK
				SELECT @StrTemp = [master].[dbo].[sysmessages].description
				FROM   [master].[dbo].[sysmessages]
				WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

				SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @I_StrVer	+  '-' +
				                                @I_StrRel	+  '-' +
				                                @I_StrCust	+
						 ' TABLE - '  +	@I_StrPackage	+
				                                @I_StrModule	+
				                                @I_StrTable	+  ' ' +
				                 ' FIELD - '  + @StrField  	+
				                 ' into BCMAP140. '

				EXEC Write_to_file @StrTemp, @I_StrLogFileName
			   	BREAK
			   END

			   --If the Profile is SCM enabled then  Insert another Record in BCMAP145
			   IF @I_StrSCM = 'Y'
			   BEGIN
			    	EXEC @IntRetVal =sp_insert_bcmap145_1 	@I_IntMprf,		--Migration Profile
				    					@I_StrVer,              --Version
				    					@I_StrRel,              --Release
				    					@I_StrCust,             --Customer
			    						@I_StrPackage,          --Package
			    						@I_StrModule,           --Module
						            		@I_StrTable,            --Table
						            		1,                      --Dump Number
				            				@StrField,              --Field Name
			            					1,                      --Revision Info
			            					@StrText,               --Field Value - Text
					            			0,		        --Tupe of the Value Type 0/1
					            			@I_UserName,            --User Name
			            					'N' ,			--N- Not Approved  Rule not Approved
									'C'			--Status of the Component C- New/Changed
				IF @IntRetVal <> 0
				BEGIN
					-- ERROR So Break
					SELECT @StrTemp = [master].[dbo].[sysmessages].description
					FROM   [master].[dbo].[sysmessages]
					WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

					SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
							 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
						         ' VRC  - '   + @I_StrVer	+  '-' +
					                                @I_StrRel	+  '-' +
					                                @I_StrCust	+
							 ' TABLE - '  +	@I_StrPackage	+
					                                @I_StrModule	+
					                                @I_StrTable	+  ' ' +
					                 ' FIELD - '  + @StrField  	+
					                 ' into BCMAP145. '

					EXEC Write_to_file @StrTemp, @I_StrLogFileName
					BREAK
				END
			   END


		IF @IntRetVal <> 0
		BEGIN
			 CLOSE LDC150_Cursor
			 DEALLOCATE LDC150_Cursor
 			 RETURN @IntRetVal
		END

		FETCH NEXT FROM LDC150_Cursor
		INTO    @StrTempTableName1,@StrField,@StrFType

	 END   -- WHILE  @@FETCH_STATUS = 0

	 CLOSE LDC150_Cursor
	 DEALLOCATE LDC150_Cursor
	 RETURN @IntRetVal
GO
/* LND2-26093.en */

/* LND2-26093.sn */
/********************************************************************************
*	NAME:		sp_LegacyCreateMappingForUnchangedTables			*
*	DESCRPTION:	This Stored procedure generates the default mappings    *
*			for Unchaged Tables.					*
*	CALLS:		1. sp_insert_bcmap140_1()				*
*			2. sp_insert_bcmap145_1()				*
*	CALLED BY:	sp_LegacyCreateDefaultMappings()				*
********************************************************************************/
CREATE PROCEDURE [sp_LegacyCreateMappingForUnchangedTables]
(
	  @I_StrTargetappl 	[varchar](15),
	  @I_StrSourceappl 	[varchar](15),
	  @I_StrVer  		[varchar](4),
	  @I_StrRel  		[varchar](2),
	  @I_StrCust  		[varchar](4),
	  @I_StrSCM  		[varchar](1),
	  @I_IntRunn  		[numeric](10,5),
	  @I_IntMprf   		[int],
	  @I_StrPackage  	[varchar](2),
	  @I_StrModule   	[varchar](3),
	  @I_StrTable   	[varchar](60),
	  @I_UserName		[varchar](12),
   	  @I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
	   )
	-- LND2-26093.c Parameter @I_IntRunn changed
 AS
	 DECLARE @StrField   		as varchar(60)
	 DECLARE @StrTempField  	as varchar(60)
	DECLARE @StrTempTable  	as varchar(60)
	 DECLARE @StrText   		as varchar(1024)
	 DECLARE @StrTrgFType 		as varchar(1)
	 DECLARE @IntRetVal 		as int
	 DECLARE @StrTemp		as varchar(1024)

	 SET @IntRetVal = 0

	 /*Get all the Unchanged Tables Records from BCLDC150*/
	 DECLARE LDC150_Cursor CURSOR FOR

	 SELECT  	[dbo].[bcldc150].flno , [dbo].[bcldc150].fdnm,[dbo].[bcldc150].ttyp
	 FROM   	[dbo].[bcldc150]
	 WHERE 		[dbo].[bcldc150].runn  	= @I_IntRunn
	 AND   		[dbo].[bcldc150].ctyp 	= 'U'
	 AND    	[dbo].[bcldc150].cpac  	= @I_StrPackage
	 AND  		[dbo].[bcldc150].cmod  	= @I_StrModule
	 AND  		[dbo].[bcldc150].flno  	= @I_StrTable
	 ORDER BY  	[dbo].[bcldc150].fdnm

	 OPEN 	LDC150_Cursor
	 FETCH	NEXT FROM LDC150_Cursor
	 INTO   @StrTempTable,@StrField,@StrTrgFType

	 WHILE 	@@FETCH_STATUS = 0
	 BEGIN
		  SET @StrTempField = @StrField

		        	SET @StrText = CASE @StrTrgFType
					        WHEN 'T' THEN 'to_date(' + @StrTempTable + '.' + @StrField + ')'
					        ELSE @StrTempTable + '.' + @StrField
 	 		          	  	END

			     EXEC @IntRetVal = sp_insert_bcmap140_1
					      @I_IntMprf,  	-- MPRF
					      @I_StrVer,  	--version
					      @I_StrRel,  	--Release
					      @I_StrCust,  	--Customer
					      @I_StrPackage,  	--Package
					      @I_StrModule,  	--Module
					      @I_StrTable ,  	--Table
					      1,   		--Dump Number
					      @StrField,  	--Field Name
					      @StrText,  	--Field  Value
					      0,   		--Type (Hard coded)
					      'N'   		--Not Approved

			     IF @IntRetVal <> 0
			     BEGIN
				SELECT @StrTemp = [master].[dbo].[sysmessages].description
				FROM   [master].[dbo].[sysmessages]
				WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

				SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @I_StrVer	+  '-' +
				                                @I_StrRel	+  '-' +
				                                @I_StrCust	+
						 ' TABLE - '  +	@I_StrPackage	+
				                                @I_StrModule	+
				                                @I_StrTable	+  ' ' +
				                 ' FIELD - '  + @StrField  	+
				                 ' into BCMAP140. '

				EXEC Write_to_file @StrTemp, @I_StrLogFileName
				RETURN @IntRetVal
			     END

			     -- If the Migration Profile is SCM enabled then Insert a rec. in BCMAP145.
			     IF @I_StrSCM = 'Y'
			     BEGIN
				    EXEC @IntRetVal = sp_insert_bcmap145_1
						       @I_IntMprf,  	--Mprf
						       @I_StrVer,  	--version
						       @I_StrRel,  	--Release
						       @I_StrCust,  	--Customer
						       @I_StrPackage,  	--Package
						       @I_StrModule,  	--Module
						       @I_StrTable ,  	--Table
						       1,   		--Dump No.
						       @StrField,  	--Field Name
						       1,   		--Revision Number
						       @StrText,  	--Field  Value
						       0,   		--Hard coded
						       @I_UserName,  	--Login Id
						      'N',  		--Not Approved
						      'C'		--Changed
				    IF @IntRetVal <> 0
				    BEGIN
					SELECT @StrTemp = [master].[dbo].[sysmessages].description
					FROM   [master].[dbo].[sysmessages]
					WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

					SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
							 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
						         ' VRC  - '   + @I_StrVer	+  '-' +
					                                @I_StrRel	+  '-' +
					                                @I_StrCust	+
							 ' TABLE - '  +	@I_StrPackage	+
					                                @I_StrModule	+
					                                @I_StrTable	+  ' ' +
					                 ' FIELD - '  + @StrField  	+
					                 ' into BCMAP145. '

					EXEC Write_to_file @StrTemp, @I_StrLogFileName
					RETURN @IntRetVal
				    END


		  END

		  FETCH NEXT FROM LDC150_Cursor
		  INTO   @StrTempTable,@StrField,@StrTrgFType
	 END

	 CLOSE LDC150_Cursor

	 DEALLOCATE LDC150_Cursor

	 RETURN @IntRetVal
GO
/* LND2-26093.en */

/* LND2-26093.sn */
/* **********************************************************************************************
*	This stored procedure is  used to update the Log table bcddc000	       *
* **********************************************************************************************/
CREATE PROCEDURE [sp_update_bcddc000_1]
(
	@I_IntRunn 	[numeric](10,5),
	@I_IntMprf 	[int],
	@I_StrPackFrom 	[nvarchar](2),
	@I_StrModFrom 	[nvarchar](3),
	@I_StrTableFrom [nvarchar](3),
	@I_StrPackTo 	[nvarchar](2),
	@I_StrModTo	[nvarchar](3),
	@I_StrTableTo	[nvarchar](3),
	@I_IntType	int
)
	-- LND2-26093.c Parameter @I_IntRunn changed
AS
	DECLARE @StrPackage 	as nvarchar(2)
	DECLARE @StrModule 	as nvarchar(3)
	DECLARE @StrTable 	as nvarchar(3)

	DECLARE BCMAP120_Cursor CURSOR FOR
	SELECT  [dbo].[bcmap120].tcpac,
		[dbo].[bcmap120].tcmod,
		[dbo].[bcmap120].tflno
	FROM	[dbo].[bcmap120]
	WHERE 	[dbo].[bcmap120].mprf = @I_IntMprf
	AND  	[dbo].[bcmap120].tcpac  +  [dbo].[bcmap120].tcmod + [dbo].[bcmap120].tflno
	BETWEEN	(@I_StrPackFrom +  @I_StrModFrom  + @I_StrTableFrom)
	AND  	(@I_StrPackTo + @I_StrModTo +@I_StrTableTo)
	OPEN BCMAP120_Cursor
	FETCH NEXT FROM BCMAP120_Cursor
	INTO   @StrPackage, @StrModule, @StrTable
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC sp_insert_bcddc000_1 @I_IntRunn,@StrPackage, @StrModule, @StrTable,'','','S'

		FETCH NEXT FROM BCMAP120_Cursor
		INTO   @StrPackage, @StrModule, @StrTable
	END
	CLOSE BCMAP120_Cursor
	DEALLOCATE BCMAP120_Cursor
	IF @I_IntType = 1  -- call from Default Mapping Gen Proc
	BEGIN
		DECLARE DDC140_Cursor CURSOR FOR
		SELECT 	[dbo].[bcddc140].cpac,
			[dbo].[bcddc140].cmod,
			[dbo].[bcddc140].flno
		FROM 	[dbo].[bcddc140]
		WHERE	NOT EXISTS(	SELECT  *
					FROM	[dbo].[bcmap120]
					WHERE 	[dbo].[bcmap120].mprf = @I_IntMprf
					AND  	[dbo].[bcmap120].tcpac  +  [dbo].[bcmap120].tcmod + [dbo].[bcmap120].tflno
					BETWEEN	(@I_StrPackFrom +  @I_StrModFrom  + @I_StrTableFrom)
					AND  	(@I_StrPackTo + @I_StrModTo +@I_StrTableTo) )
		AND		[dbo].[bcddc140].runn = @I_IntRunn
		AND		([dbo].[bcddc140].ctyp = 'U'  	OR  	[dbo].[bcddc140].ctyp = 'C')
		AND  		([dbo].[bcddc140].cpac  +  [dbo].[bcddc140].cmod + [dbo].[bcddc140].flno
		BETWEEN 	(@I_StrPackFrom +  @I_StrModFrom  + @I_StrTableFrom)
		AND 		(@I_StrPackTo + @I_StrModTo +@I_StrTableTo) )

		OPEN DDC140_Cursor

		FETCH NEXT FROM DDC140_Cursor
		INTO   @StrPackage, @StrModule, @StrTable


		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC sp_insert_bcddc000_1 @I_IntRunn,@StrPackage, @StrModule, @StrTable,'','','F'

			FETCH NEXT FROM DDC140_Cursor
			INTO   @StrPackage, @StrModule, @StrTable

		END

		CLOSE DDC140_Cursor

		DEALLOCATE DDC140_Cursor
	END
	ELSE	--Called from Fine Tune Table
	BEGIN
		DECLARE DDC141_Cursor CURSOR FOR

		SELECT 	[dbo].[bcddc141].spac,
			[dbo].[bcddc141].smod,
			[dbo].[bcddc141].slno
		FROM 	[dbo].[bcddc141]
		WHERE	NOT EXISTS(	SELECT  	*
					FROM		[dbo].[bcmap120]
					WHERE 		[dbo].[bcmap120].mprf = @I_IntMprf
					AND  		[dbo].[bcmap120].tcpac  +  [dbo].[bcmap120].tcmod + [dbo].[bcmap120].tflno
					BETWEEN		(@I_StrPackFrom +  @I_StrModFrom  + @I_StrTableFrom)
					AND  		(@I_StrPackTo + @I_StrModTo +@I_StrTableTo) )
		AND	[dbo].[bcddc141].runn = @I_IntRunn
		AND 	[dbo].[bcddc141].spac +  [dbo].[bcddc141].smod +[dbo].[bcddc141].slno
		BETWEEN	(@I_StrPackFrom +  @I_StrModFrom  + @I_StrTableFrom)  AND  (@I_StrPackTo + @I_StrModTo +@I_StrTableTo)

		OPEN DDC141_Cursor

		FETCH NEXT FROM DDC141_Cursor
		INTO   @StrPackage, @StrModule, @StrTable

		WHILE @@FETCH_STATUS = 0
		BEGIN

			EXEC sp_insert_bcddc000_1 @I_IntRunn,@StrPackage, @StrModule, @StrTable,'','','F'

			FETCH NEXT FROM DDC141_Cursor
			INTO   @StrPackage, @StrModule, @StrTable
		END

		CLOSE DDC141_Cursor
		DEALLOCATE DDC141_Cursor
	END



GO
/* LND2-26093.en */

/* LND2-19008.sn */
CREATE PROCEDURE [dbo].[sp_GuessMapping]
(
	  @I_StrPackage  	[varchar](2),
	  @I_StrModule   	[varchar](3),
	  @I_StrTable   	[varchar](3),
   	  @StrTrgDType		[varchar](1),	--Target Domain Type
	  @IntTrgDLen		[int],			--Target Domain Length 
	  @IntSrcDepth		[int],			--Field Depth	 - Source
	  @IntTrgDepth		[int],			--Field Depth	 - Target
	  @StrCType			[varchar](1),	--Var. to hold the Field Type.
	  @StrSrcDType  	[varchar](1),	--Source Domain Type
	  @IntSrcDLen   	[int],			--Source Domain Length
	  @IntSrcDDigb  	[int],			--Source Domain Digit before Decimal.
	  @IntTrgDDigb  	[int],       	--Target Domain Digit before Decimal.
	  @IntSrcDDiga  	[int], 			--Source Domain Digit After Decimal.
	  @IntTrgDDiga  	[int],       	--Target Domain Digit After Decimal.
	  @StrTrgMan   		[varchar](1),	--Target Domain - Mandatory or not?
	  @StrSrcMan   		[varchar](1),	--Source Domain - Mandatory or not?
	  @StrField   		[varchar](15),
	  @StrText			[varchar](1024) OUTPUT
)
 AS
 	 DECLARE @StrVarName			as varchar(100)
	 DECLARE @StrTempField  		as varchar(15)
	 DECLARE @IntRetVal 			as int
	 DECLARE @StrTemp				as varchar(1024)
	 DECLARE @StrTrgInitialValue	as nvarchar(60)
	 DECLARE @IntFieldDumpType		as varchar(1)

	 SET @IntRetVal = 0


/* 
   SYNCHRONIZE WITH [sp_CreateMappingsForFineTunedChangedTables] START
   -------------------------------------------------------------------
*/

			   /* The Depth of the Field is > 0. So all these Field are assigned Default Values*/
			   IF  @IntTrgDepth > @IntSrcDepth
			   BEGIN
-- Cycle3 .so			IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
--				BEGIN
--					IF @StrTrgInitialValue = '0'
--					BEGIN
--						SET @StrTrgInitialValue = 'String.Set$("",' + LTRIM(STR(@IntTrgDLen)) + ')'
--					END
--				END
--
--		        	SET @StrText = CASE @StrTrgDType
--						        WHEN 'B' THEN @StrTrgInitialValue
--						        WHEN 'I' THEN @StrTrgInitialValue
--						        WHEN 'L' THEN @StrTrgInitialValue
--						        WHEN 'D' THEN @StrTrgInitialValue
--						        WHEN 'F' THEN @StrTrgInitialValue
--						        WHEN 'S' THEN @StrTrgInitialValue
--						        WHEN 'E' THEN @StrTrgInitialValue
--						        WHEN 'T' THEN 'utc(0,0,2)'
--						        WHEN 'U' THEN @StrTrgInitialValue
--						        WHEN 'M' THEN @StrTrgInitialValue
--						        WHEN 'K' THEN 'utc(0,0)'
--						        ELSE '0'
-- Cycle3 .eo		 		          	  END
				SET @StrText = 'default()' -- Cycle3 .n
			     	SET @IntFieldDumpType = 0
				SET @StrField = @StrTempField + '(' +  LTRIM(STR(@IntTrgDepth)) + ')'
			   END

			   ELSE
			   BEGIN

				    IF @IntTrgDepth >= 1
				    BEGIN
					SET @StrField = @StrTempField + '(' + LTRIM(STR(@IntTrgDepth)) + ')'
					--check whether the Target is a string. If so add "1,1" to the value
					IF @StrTrgDType = 'S' or @StrTrgDType = 'M'
					BEGIN
						SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrTempField + '(1,' + LTRIM(STR(@IntTrgDepth)) + ')'
					END
					ELSE
					BEGIN
						SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrField
					END
				    END
				    /* The Field is a New Field. So Map Default Values for it.*/
				    IF @StrCType = 'N'
				    BEGIN
-- Cycle3 .so					IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
--						BEGIN
--							IF @StrTrgInitialValue = '0'
--							BEGIN
--								SET @StrTrgInitialValue = 'String.Set$("",' + LTRIM(STR(@IntTrgDLen)) + ')'
--							END
--						END
--					       	SET @StrText = CASE @StrTrgDType
--								WHEN 'B' THEN @StrTrgInitialValue
--							        WHEN 'I' THEN @StrTrgInitialValue
--							        WHEN 'L' THEN @StrTrgInitialValue
--							        WHEN 'D' THEN @StrTrgInitialValue
--							        WHEN 'F' THEN @StrTrgInitialValue
--							        WHEN 'S' THEN @StrTrgInitialValue
--							        WHEN 'E' THEN @StrTrgInitialValue
--							        WHEN 'T' THEN 'utc(0,0,2)'
--							        WHEN 'U' THEN @StrTrgInitialValue
--							        WHEN 'M' THEN @StrTrgInitialValue
--							        WHEN 'K' THEN 'utc(0,0)'
--							        ELSE '0'
-- Cycle3 .eo					END
						SET @StrText = 'default()' -- Cycle3 .n
					       	SET @IntFieldDumpType=0
				     END

				     /* The Field is Changed or Unchanged one*/
				     IF @StrCType = 'U' OR @StrCType = 'C'
				     BEGIN

					       SET @StrText = @I_StrPackage + @I_StrModule + @I_StrTable +  '.'  + @StrField

					       /* 1. Target is ENUM  Dataype  */
					       IF @StrTrgDType  = 'E'
					       BEGIN
						         SET @StrText = CASE  @StrSrcDType
								            WHEN 'E'  THEN  @StrText --Enum Data Type
								            WHEN 'I'  THEN  @StrText --INT Data Type
								            WHEN 'L'  THEN  @StrText --INT Data Type
-- Cycle3 .o								    ELSE @StrTrgInitialValue  --Initial Value
									    ELSE 'default()'  --Initial Value -- Cycle3 .n
								        END
					       END

						/* LND-9338.so */
					   /* 2. Target is STRING DataType
					   IF @StrTrgDType = 'S'
					   BEGIN*/
							 /* Check for the  ALignment */
						 /* Done in XML generation */

							 /* check for the Case */
							 /* done is XML Generation */
					   /*END*/
					    /* LND-9338.eo */

						/* LND-9338.sn */
						/* 2. Target is STRING DataType */
						IF @StrTrgDType = 'S' 
						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'S' THEN	
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN		/* Source smaller or equal than Target */
											@StrText
									   ELSE										/* String does not Fit -> Action */
											''
									   END
									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */ 
										''
									END
						END
						/* LND-9338.en */
						
												/* LND-9325.sn */
						/* 2. Target is STRING DataType */
						IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'	
						BEGIN
							IF @IntTrgDepth >= 1
							BEGIN
								SET @StrText = @I_StrPackage + @I_StrModule + @I_StrTable  +  '.' + @StrTempField + '(1,' + LTRIM(STR(@IntTrgDepth)) + ')'
							END
						END
												/* LND-9325.en */


		


					       /* 3. Target is UTC Datatype */
					       IF @StrTrgDType = 'K'
					       BEGIN
						         Set @StrText = CASE  	@StrSrcDType
								        WHEN 'T'  THEN  'utc(' + @StrText +',0)'    --Date Data Type
								        WHEN 'K'  THEN  'utc$(' + @StrText +')'     --UTC Data Type
-- Cycle3 .o								ELSE 'utc(0,0)'
									ELSE 'empty()' -- Empty value -- Cycle3 .n
							           	END
					       END

					       /* 4. Target is BYTE Datatype */
					       IF @StrTrgDType = 'B'
					       BEGIN
						         --SET @StrText = 'to.byte(' + @StrText + ')'
						         SET @StrText = @StrText
					       END

						/* LND-9338.sn */
						/* 5 + 6. Target is FLOAT or DOUBLE Datatype */
						IF  @StrTrgDType = 'F' OR @StrTrgDType ='D' OR
							@StrTrgDType = 'I' OR @StrTrgDType ='L'

						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'D' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'F' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'I' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'L' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END

									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */

										''
									END
						END
						/* LND-9338.en */

						/* LND-9338.so
					       /* 5. Target is INTEGER / LONG Datatype */
					       IF @StrTrgDType = 'I' OR  @StrTrgDType ='L'
			                      	       BEGIN
						               SET @StrText = 	CASE @StrSrcDType
						             		    	WHEN 'I' THEN @StrText
					                           		WHEN 'L' THEN @StrText
-- Cycle3 .o						                        ELSE '0' + '|Source field is of diff. Data types'
										ELSE 'empty()' -- Cycle3 .n
			             				             	END

					       END

		                   	      /* 6. Target is FLOAT /DOUBLE Datatype */
		                   	      IF @StrTrgDType = 'F' OR @StrTrgDType ='D'
		                   	      BEGIN
		                    	       	SET @StrText = CASE @StrSrcDType
						             	     	WHEN 'D' THEN @StrText
		        	                			  	WHEN 'F' THEN @StrText
-- Cycle3 .o		                	         	  		ELSE '0' + '|Source field is of diff. Data types'
										ELSE 'empty()' -- Cycle3 .n
			        	                	  	END

		                   	       END
		                  		 LND-9338.eo */

					       /* 7. Target is DATE Datatype */
					       IF @StrTrgDType = 'T'
					       BEGIN
					       		SET @StrText =   CASE @StrSrcDType
-- Cycle3 .o					                	        WHEN 'T'  THEN 'utc('+ @StrText + '0,2)' --Source is Date
										WHEN 'T'  THEN 'utc('+ @StrText + ',0,2)' --Source is Date -- Cycle3 .n
                        							WHEN 'K'  THEN 'date.utc(' + @StrText + ')' --source is UTC
-- Cycle3 .o			                          			ELSE 'utc(0,0,2)'
										ELSE 'empty()' -- Cycle3 .n
					                         	 END
					       END

						/* LND-9338.sn */
						/* 8. Target is MULTI BYTE DataType */
						IF @StrTrgDType = 'M' 
						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'S' THEN
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN	/* Source smaller or equal than Target */
											@StrText
									   ELSE		/* String does not Fit -> Action */
											''
									   END
								   WHEN 'M' THEN
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN	/* Source smaller or equal than Target */
											@StrText
									   ELSE		/* String does not Fit -> Action */
											''
									   END


									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */

										''
									END
						END
						/* LND-9338.en */
						
						/* LND-9338.so
					       /* 8. Target is MULTI BYTE DataType */

					       /*IF @StrTrgDType = 'M'
					       BEGIN*/
					       		/* Handled in XML Generation */
					       /*END*/
					      LND-9338.eo */


			                       /* Check for TARGET Mandatory and SOURCE NOT Mandatory */
			                       IF @StrTrgMan = 'M' and @StrSrcMan ='N'
					       BEGIN

			      				SET @StrVarName = 'var.' + @I_StrPackage + @I_StrModule + @I_StrTable +  '.'  + @StrField

				                	SET @StrTemp = CASE @StrTrgDType
								            WHEN 'B' THEN '0'
								            WHEN 'I' THEN '0'
								            WHEN 'L' THEN '0'
								            WHEN 'D' THEN '0'
								            WHEN 'F' THEN '0'
								            WHEN 'S' THEN  'bcrun.convdll.string("",' + LTRIM(STR(@IntTrgDLen)) + ')'    -- LND2-9769.c
								            WHEN 'E' THEN  '0'
								            WHEN 'T' THEN  'utc(0,0,2)'
								            WHEN 'U' THEN  '0'
								            WHEN 'M' THEN  'bcrun.convdll.stringmb("",' + LTRIM(STR(@IntTrgDLen)) + ')'  -- LND2-9769.c
								            WHEN 'K' THEN  'utc(0,0)'
									    ELSE '0'
								       END

					           	/* WRITE THE TEXT FOR THE FIELD OF TYPE 2
							 if isspace(Field) then
						            	Var.FieldName = Default Value
					              	endif */

				                	SET @StrText = '|* The Target field is a Mandatory Field but the Source is' + char(13) + char(10) +
				            			       '|* Not a Mandatory Field. So  Default Value is Filled here.' + char(13) + char(10) +
				            			       char(9) + 'if isspace(' +  @StrText + ') then' + char(13)  + char(10) +
					           		       char(9) + char(9) + @StrVarName + '=' + @StrTemp + char(13)  + char(10) +
					           		       char(9) + 'endif'

					         	SET @IntFieldDumpType = 1
					        END
					        ELSE
					        BEGIN
						         SET @StrVarName = ''
						         SET @StrTemp = ''
						         SET @IntFieldDumpType=0
					        END
		                     END  --IF @StrCType = 'U' OR @StrCType = 'C'  END

			   END  -- ELSE of @IntTrgDepth > 0 END

/*
   SYNCHRONIZE WITH [sp_CreateMappingsForFineTunedChangedTables] END
   -----------------------------------------------------------------
*/

GO

/* LND2-19008.en */

CREATE PROCEDURE [dbo].[sp_insert_update_bcmap140_1]
(	 @mprf_1 	[int],
	 @vers_2 	[varchar](4),
	 @rele_3 	[varchar](2),
	 @cust_4 	[varchar](4),
	 @tcpac_5 	[varchar](2),
	 @tcmod_6 	[varchar](3),
	 @tflno_7 	[varchar](60),
	 @dnum_8 	[int],
	 @fdnm_9 	[varchar](60),
	 @mrul_10 	[text],
	 @type_11 	[int],
	 @stat_12 	[varchar](1)
)
AS
BEGIN
	IF EXISTS (SELECT 1 FROM [dbo].[bcmap140]
		WHERE   [mprf]   = @mprf_1  
			and [vers]  = @vers_2  
			and [rele]  = @rele_3  
			and [cust]  = @cust_4  
			and [tcpac] = @tcpac_5 
			and [tcmod] = @tcmod_6 
			and [tflno] = @tflno_7 
			and [dnum]  = @dnum_8  
			and [fdnm]  = @fdnm_9
			and [type]  = @type_11 
			and [stat]  = @stat_12 
	)
	BEGIN
		UPDATE [dbo].[bcmap140] set  [mrul] = @mrul_10 
		WHERE  [mprf]   = @mprf_1  
			and [vers]  = @vers_2  
			and [rele]  = @rele_3  
			and [cust]  = @cust_4  
			and [tcpac] = @tcpac_5 
			and [tcmod] = @tcmod_6 
			and [tflno] = @tflno_7 
			and [dnum]  = @dnum_8  
			and [fdnm]  = @fdnm_9
			and [type]  = @type_11 
			and [stat]  = @stat_12 
	END
ELSE
	BEGIN
		INSERT INTO [dbo].[bcmap140]
			 ( [mprf],
			 [vers],
			 [rele],
			 [cust],
			 [tcpac],
			 [tcmod],
			 [tflno],
			 [dnum],
			 [fdnm],
			 [mrul],
			 [type],
			 [stat])
			 
		VALUES
			( @mprf_1,
			 @vers_2,
			 @rele_3,
			 @cust_4,
			 @tcpac_5,
			 @tcmod_6,
			 @tflno_7,
			 @dnum_8,
			 @fdnm_9,
			 @mrul_10,
			 @type_11,
			 @stat_12)
	END

END

RETURN @@ERROR

GO
/* LND2-19008.en */

/* LND-9330.sn */
IF  EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_EmptyRulesReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP procedure [dbo].[sp_EmptyRulesReport]
GO

CREATE PROCEDURE [dbo].[sp_EmptyRulesReport]
(
		@I_IntMprf 		[int]=0,			-- From part of Profile Range
		@I_IntMprfTo 		[int]=999,			-- To part of Profile Range
		@I_StrPac		[varchar](65)='' ,	-- Table  Name of From range
		@I_StrPacTo		[varchar](65)='zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' ,	-- Table  Name of To range
		@I_StrUsr		[varchar](12)='',	-- User  Name of From range
		@I_StrUsrTo		[varchar](65)='zzzzzzzzzzzz',	-- User  Name of To range
		@I_IntFlag  		[int],		   -- if 0,find list of tables for which rules are not maintained         -- if 1, Find empty rules for fields of tables for which rules are maintained
		@I_IntUpdateMappingFlag [int]=0  -- if 0 view : 0: insert/update mapping record   --LND2-19008.c

)

 AS
DECLARE @strTargetApp		as varchar(30)			--Target Application Name
DECLARE @intMPRF			as int				-- MPRF
DECLARE @intPPRF			as int				-- PPRF
DECLARE @strNoRuleTable		as varchar(65)			--Tables not maintained with Rules
DECLARE @strNoRuleTable_ButM	as varchar(65)			--Tables not maintained with Rules but status as Maintned in bcmap120

DECLARE @strMaintTable		as varchar(65)			--Tables with maintained Rules
DECLARE @intDump			as int				-- Dumps
DECLARE @strTemp			as varchar(2000)		--Storing temporary string
DECLARE @intCNTPPRF			as int				--Storing No of Total profiles
DECLARE @intCNTTab			as int				--Storing No of Total tables not having rules
DECLARE @intCNTTab_M		as int				--Storing No of Total tables not having rules but has got M status
DECLARE @intCNTMaintTab		as int				--Storing No of Total tables having rules
DECLARE @strMissedField		as varchar(60)			--Storing Empty Field Name
DECLARE @intCNTMissedField		as int				--Storing No of Total Fields in a table which missed rule
DECLARE @intCNTDumps		as int				--Storing No of Dumps maintained per table
DECLARE @PrintFlag			as varchar(10)
DECLARE @TargetType			as int 				--Stores 'baan=1' or 'nonbaan=0' value
DECLARE @DateModified		as datetime			--Stores date modified
DECLARE @UserModified		as varchar(12) 			--Stores user name who modified the rule
DECLARE @IntRetVal		    as int				--Return Value
DECLARE @I_StrVer			as varchar(4)
DECLARE @I_StrRel			as varchar(2)
DECLARE @I_StrCust			as varchar(4)
DECLARE @I_StrSrcPackage 	as varchar(2)
DECLARE @I_StrSrcModule 	as varchar(3)
DECLARE @I_StrSrcTable 		as varchar(3)
DECLARE @I_StrTrgPackage 	as varchar(2)
DECLARE @I_StrTrgModule 	as varchar(3)
DECLARE @I_StrTrgTable 		as varchar(3)
DECLARE @StrText			as varchar(2000)
DECLARE @IntFieldDumpType	as varchar(1)
DECLARE	@StrDevVersion		as varchar(4)		--Development Version
DECLARE	@StrDevRelease		as varchar(2)		--Development Release
DECLARE	@StrDevCustomer		as varchar(4)		--Development Customer

/* Get Target Application Name for MPRF(@I_IntMprf) 	*/


SET NOCOUNT OFF

--LND2-19008.so
--IF(@I_IntUpdateMappingFlag = 1)      -- 1: update mode 0: View mode
--BEGIN
--	/* Get the Development VRC from BCMAP 105 */
--	SELECT 	@StrDevVersion	= [dbo].[bcmap105].dver,
--		@StrDevRelease	= [dbo].[bcmap105].drel,
--		@StrDevCustomer	= [dbo].[bcmap105].dcus
--	FROM	[dbo].[bcmap105]
--	WHERE	[dbo].[bcmap105].mprf = @I_IntMprf ;
--END
--LND2-19008.eo

--LND2-19008.sn
IF(@I_IntUpdateMappingFlag = 1)
BEGIN
	SELECT 	@StrDevVersion	= [dbo].[bcmap100].vers,
		@StrDevRelease	= [dbo].[bcmap100].rele,
		@StrDevCustomer	= [dbo].[bcmap100].cust
	FROM	[dbo].[bcmap100]
	WHERE	[dbo].[bcmap100].mprf = @I_IntMprf ;
END
--LND2-19008.en

IF(@I_IntFlag  =1 )
BEGIN
	Create table ##TempResult (tbln varchar(8000));
END

IF(@I_IntFlag  =0 )
BEGIN
	Create table ##TempNoRules(tbln varchar(8000));
END

/* LND-9330.sn */
/* Try to remove older Cursors if still remaining from previous actions. */
/* If not cleaned the resulting search list will be empty */
BEGIN TRY 
	CLOSE BCMAP101_CURSOR
	DEALLOCATE BCMAP101_CURSOR
END TRY
BEGIN CATCH
	/* No Action */
END CATCH
/* LND-9330.en */

DECLARE BCMAP101_CURSOR   CURSOR STATIC FOR
	--select distinct mprf from bcmap101 where (pprf =@I_IntMprf)	SCA2.2
	select distinct pprf from bcmap101 where mprf between @I_IntMprf and @I_IntMprfTo


OPEN BCMAP101_CURSOR;

FETCH NEXT FROM BCMAP101_CURSOR INTO @intPPRF

SET @intCNTPPRF = @@CURSOR_ROWS

WHILE @intCNTPPRF > 0
	BEGIN



		SELECT @strTargetApp=[dbo].[bcmap100].tapp
		FROM [dbo].[bcmap100]
		WHERE [dbo].[bcmap100].mprf =@intPPRF


		/* Check for application type @TargetType */
		SELECT @TargetType=[dbo].[bcddi100].extn
		FROM [dbo].[bcddi100]
		WHERE [dbo].[bcddi100].appl = @strTargetApp

		/* Finding EMpty fields mappings starts here  */
	IF(@I_IntFlag=1)
	BEGIN



			DECLARE MaintainedTable_CURSOR CURSOR STATIC FOR
			select DISTINCT( tcpac+tcmod+tflno ) from bcmap140 where mprf=@intPPRF and (tcpac+tcmod+tflno between  @I_StrPac and @I_StrPacTo ) and  tcpac+tcmod+tflno in (select tcpac+tcmod+tflno from bcmap120 where mprf = @intPPRF   and lusr between @I_StrUsr and @I_StrUsrTo and stat<>'X' ) order by tcpac+tcmod+tflno;    /* LND-9330.c */

			OPEN MaintainedTable_CURSOR;

			FETCH NEXT FROM MaintainedTable_CURSOR INTO @strMaintTable

			SET @intCNTMaintTab	= @@CURSOR_ROWS

			--IF(@intCNTMaintTab > 0 )
			--BEGIN
				--SET @strTemp = 'Profile : ' + STR(@intPPRF)
				--Insert into ##TempResult values (@strTemp)

				--SET @strTemp='Target Application : ' + @strTargetApp
				--Insert into ##TempResult values (@strTemp)


			--END

			WHILE @intCNTMaintTab > 0
			BEGIN

				/* DECLARE Cursor to find out no of dumps maintained for particular table */
				DECLARE Dumps_CURSOR CURSOR STATIC FOR
				select DISTINCT dnum from bcmap140  where mprf=  @intPPRF and tcpac+tcmod+tflno= @strMaintTable;
				OPEN Dumps_CURSOR ;
				FETCH NEXT FROM Dumps_CURSOR into @intDump
				SET @intCNTDumps = @@CURSOR_ROWS

				SET @PrintFlag='True';
				WHILE @intCNTDumps > 0
				BEGIN


					/* Declare cursor to find out Missed fields in dumps per table */
					/* First find out whether selected table contains any dept  field*/

					IF(@TargetType=1)
						BEGIN
							DECLARE EmptyFields_CURSOR CURSOR STATIC FOR
							select DISTINCT bcddi150.fdnm from bcddi150 where bcddi150.appl = @strTargetApp
							and bcddi150.comb='N'
							and bcddi150.dept = 0
							and bcddi150.cpac+bcddi150.cmod+bcddi150.flno = @strMaintTable
							and bcddi150.fdnm not in (select bcmap140.fdnm from bcmap140 where mprf= @intPPRF and dnum = @intDump and tcpac+tcmod+tflno= @strMaintTable ) ;
						END

					ELSE

						BEGIN
							DECLARE EmptyFields_CURSOR CURSOR STATIC FOR
							select DISTINCT bclgy150.flna from bclgy150 where bclgy150.appl = @strTargetApp
							and bclgy150.cpac+bclgy150.cmod+bclgy150.flno = @strMaintTable
							and bclgy150.flna not in (select bcmap140.fdnm from bcmap140 where mprf= @intPPRF and dnum = @intDump and tcpac+tcmod+tflno= @strMaintTable ) ;

						END

					OPEN EmptyFields_CURSOR ;

					FETCH NEXT FROM EmptyFields_CURSOR into @strMissedField	 ;
					SET @intCNTMissedField = @@CURSOR_ROWS;



						IF (@intCNTMissedField <> 0 )
						BEGIN
							IF (@PrintFlag = 'True')
							BEGIN
								SET @strTemp ='['+'Profile: ' + STR(@intPPRF) +']'+  '    ' +  '[' + @strMaintTable +']'
								Insert into ##TempResult values (@strTemp)
							END

							SET @strTemp = 'Dump= ' + LTRIM(STR(@intDump))
							Insert into ##TempResult values (@strTemp)

							SET @strTemp ='Empty Fields :'
							Insert into ##TempResult values (@strTemp)


						END

					WHILE @intCNTMissedField > 0
					BEGIN

						IF(@strMissedField IS NOT NULL)
						BEGIN
							Insert into ##TempResult values (@strMissedField)
						END
						SET @intCNTMissedField = @intCNTMissedField - 1

						/* LND2-19008.sn */
						IF (@I_IntUpdateMappingFlag = 1)
						BEGIN
							SET @I_StrVer = @StrDevVersion ;
							SET @I_StrRel = @StrDevRelease ;
							SET @I_StrCust = @StrDevCustomer ;
							SET @I_StrSrcPackage = SUBSTRING(@strMaintTable, 1, 2) ;
							SET @I_StrSrcModule = SUBSTRING(@strMaintTable, 3, 3) ;
							SET @I_StrSrcTable = SUBSTRING(@strMaintTable, 6, 3) ;
							SET @I_StrTrgPackage = SUBSTRING(@strMaintTable, 1, 2) ;
							SET @I_StrTrgModule = SUBSTRING(@strMaintTable, 3, 3) ;
							SET @I_StrTrgTable = SUBSTRING(@strMaintTable, 6, 3) ;
							SET @StrText = 'default()' ;
							SET @IntFieldDumpType = 0 ;
					
							DECLARE  @I_StrTargetappl 	as varchar(15)
							DECLARE  @I_StrSourceappl 	as varchar(15)

							/* Get Source an dTarget Application */
							SELECT    @I_StrSourceappl = [dbo].[bcmap100].sapp,
									  @I_StrTargetappl = [dbo].[bcmap100].tapp
							FROM      [dbo].[bcmap100]
							WHERE     [dbo].[bcmap100].mprf = @I_IntMprf


							--VARIABLES TO  HOLD THE DOMAIN ATTRIBUTES
							DECLARE @IntSrcDepth   	as int				--Field Depth	 - Source
							DECLARE @StrSrcDType  		as varchar(1)		--Source Domain Type
							DECLARE @IntSrcDLen   		as int			--Source Domain Length
							DECLARE @StrSrcDAlign  	as varchar(1)		--Source Domain	Align.
							DECLARE @StrSrcDCase	  	as varchar(1)		--Source Domain	Case.
							DECLARE @IntSrcDDigb  		as int			--Source Domain Digit before Decimal.
							DECLARE @IntSrcDDiga  		as int 			--Source Domain Digit After Decimal.
							DECLARE @StrSrcMan   		as varchar(1)		--Source Domain - Mandatory or not?
							DECLARE @StrSrcInitialValue	as nvarchar(60)		--Initial Value of the Target Field

							DECLARE @IntTrgDepth   	as int				--Field Depth	 - Target
							DECLARE @StrTrgDType  		as varchar(1)		--Target Domain Type
							DECLARE @IntTrgDLen   		as int       		--Target Domain Length
							DECLARE @StrTrgDAlign  	as varchar(1)		--Target Domain	Align.
							DECLARE @StrTrgDCase	  	as varchar(1)		--Target Domain	Case.
							DECLARE @IntTrgDDigb  		as int       		--Target Domain Digit before Decimal.
							DECLARE @IntTrgDDiga  		as int       		--Target Domain Digit After Decimal.
							DECLARE @StrTrgMan   		as varchar(1)		--Target Domain - Mandatory or not?
							DECLARE @StrTrgInitialValue	as nvarchar(60)		--Initial Value of the Target Field
							DECLARE @StrSrcDomain  	as varchar(14)		--Var. to Hold Source Domain.
							DECLARE @StrTrgDomain  	as varchar(14)		--Var. to Hold Target Domain.


							SET @StrSrcDType 	=''
							SET @IntSrcDLen		=0
							SET @IntSrcDDigb	=0
							SET @IntSrcDDiga    =0
							SET @StrTrgDType 	=''
							SET @IntTrgDLen		=0
							SET @IntTrgDDigb	=0
							SET @IntTrgDDiga	=0


							/* Get StrSrcDomain */
							SELECT @StrSrcDomain=t.cdom, @IntSrcDepth=t.dept
							FROM bcddi150 t 
							LEFT JOIN bcddi220 d ON t.cpac = d.cpac AND t.cdom = d.cpac+d.cdom
							WHERE t.cpac=@I_StrTrgPackage AND t.cmod=@I_StrTrgModule AND t.flno=@I_StrTrgTable AND t.appl=d.appl AND t.fdnm=@strMissedField AND t.appl=@I_StrSourceappl


							/* Get StrTrgDomain */
							SELECT @StrTrgDomain=t.cdom, @IntTrgDepth=t.dept
							FROM bcddi150 t 
							LEFT JOIN bcddi220 d ON t.cpac = d.cpac AND t.cdom = d.cpac+d.cdom
							WHERE t.cpac=@I_StrTrgPackage AND t.cmod=@I_StrTrgModule AND t.flno=@I_StrTrgTable AND t.appl=d.appl AND t.fdnm=@strMissedField AND t.appl=@I_StrTargetappl


							/*Get the Source Domain Information */
							SELECT    @StrSrcDType  	= [dbo].[bcddi220].type,
								@IntSrcDLen 	= [dbo].[bcddi220].leng,
								@StrSrcDAlign 	= [dbo].[bcddi220].adju,
								@IntSrcDDigb 	= [dbo].[bcddi220].digb,
								@IntSrcDDiga 	= [dbo].[bcddi220].diga,
								@StrSrcDCase	= [dbo].[bcddi220].conv
							FROM      [dbo].[bcddi220]
							WHERE     [dbo].[bcddi220].appl = @I_StrSourceappl
							AND  	    (([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrSrcDomain)

							/*Get the TARGET Domain Information */
							SELECT    @StrTrgDType  	= [dbo].[bcddi220].type,
								@IntTrgDLen 	= [dbo].[bcddi220].leng,
								@StrTrgDAlign 	= [dbo].[bcddi220].adju,
								@IntTrgDDigb 	= [dbo].[bcddi220].digb,
								@IntTrgDDiga 	= [dbo].[bcddi220].diga,
								@StrTrgDCase 	= [dbo].[bcddi220].conv
							FROM      [dbo].[bcddi220]
							WHERE     [dbo].[bcddi220].appl = @I_StrTargetappl
							AND       (([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrTrgDomain)



							DECLARE @Type   as varchar(1)
							DECLARE @Diga  	as int 
							DECLARE @Digb  	as int 
							DECLARE @Leng	as int

							BEGIN
							SELECT @Type=d.type, @Diga=d.diga, @Digb=d.digb, @Leng=d.leng
								FROM bcddi150 t 
								LEFT JOIN bcddi220 d ON t.cpac = d.cpac AND t.cdom = d.cpac+d.cdom
								WHERE t.cpac=@I_StrTrgPackage AND t.cmod=@I_StrTrgModule AND t.flno=@I_StrTrgTable AND t.appl=d.appl AND t.appl=@strTargetApp AND t.fdnm=@strMissedField
							END



							EXEC @IntRetVal = sp_GuessMapping @I_StrTrgPackage, --  @I_StrPackage  	
												@I_StrTrgModule,         --  @I_StrModule   	
												@I_StrTrgTable,          --  @I_StrTable   	
												@StrTrgDType,   		 --Target Domain Type
												@IntTrgDLen, 			 --Target Domain Length 
												@IntSrcDepth, --0, 	 	 --Field Depth	 - Source
												@IntTrgDepth, --0,  	 --Field Depth	 - Target
												'U',                	 --Var. to hold the Field Type.
												@StrSrcDType,  --@Type,  --Source Domain Type
												@IntSrcDLen, -- @Leng,   --Source Domain Length
												@IntSrcDDigb, --@Digb,   --Source Domain Digit before Decimal.
												@IntTrgDDigb,   --@Digb, --Target Domain Digit before Decimal.
												@IntSrcDDiga,   --@Diga, --Source Domain Digit After Decimal.
												@IntTrgDDiga, --@Diga,   --Target Domain Digit After Decimal.
												'N',                	 --Target Domain - Mandatory or not?
												'N',                	 --Source Domain - Mandatory or not?
												@strMissedField,
												@StrText OUTPUT


							--SET @StrText = @StrText + '_DEBUG_'

							EXEC @IntRetVal = sp_insert_update_bcmap140_1 	@I_IntMprf,   --Migration Profile
												@I_StrVer,                      --Version
												@I_StrRel,                      --Release
												@I_StrCust,                     --Customer
												@I_StrTrgPackage,               --Package
												@I_StrTrgModule,                --Module
												@I_StrTrgTable,                 --Table
												@intDump,                       --Dump Number
												@strMissedField,                --Field Name
												@StrText,                       --Field Value - Text
												@IntFieldDumpType,              --Type of the Value Type 0/1
												'A';                            -- N- Not Approved  Rule not Approved
					
							IF @IntRetVal <> 0
							BEGIN
								SET @strTemp = 'ERROR: Could not insert Record in bcmap140 ' + @strMaintTable + ' Dump=' + LTRIM(STR(@intDump)) + ' ' + @strMissedField + ' mapping:default()' +'^' ;
								--EXEC Write_to_file @strTemp ,@I_StrLogFileName ;
							END
							ELSE
							BEGIN
								SET @strTemp = 'Record inserted in bcmap140 ' + @strMaintTable + ' Dump=' + LTRIM(STR(@intDump)) + ' ' + @strMissedField  + ' mapping:default()' +'^' ;
							--EXEC Write_to_file @strTemp ,@I_StrLogFileName ;
							END
						END
						/* LND2-19008.en */

						FETCH NEXT FROM EmptyFields_CURSOR into @strMissedField	 ;

						CONTINUE
					END

					CLOSE EmptyFields_CURSOR
					DEALLOCATE EmptyFields_CURSOR

				IF(@TargetType=1)
				BEGIN

					/* [dbo].sp_EmptydepthFields  is called here which will find missed fields which has got depth in bcddi150*/

					EXEC [dbo].sp_EmptydepthFields @strTargetApp=@strTargetApp,@strMaintTable=@strMaintTable,@intPPRF =@intPPRF,@intDump=@intDump

				END


				/*SET @PrintFlag = 'False'; */
				FETCH NEXT FROM Dumps_CURSOR into @intDump
				SET @intCNTDumps = @intCNTDumps -1

				CONTINUE
				END
				CLOSE Dumps_CURSOR;
				DEALLOCATE Dumps_CURSOR;

				FETCH NEXT FROM MaintainedTable_CURSOR into @strMaintTable
				SET @intCNTMaintTab	= @intCNTMaintTab - 1
				CONTINUE

			END

			CLOSE MaintainedTable_CURSOR;
			DEALLOCATE MaintainedTable_CURSOR;

			FETCH NEXT FROM BCMAP101_CURSOR into @intPPRF
			SET @intCNTPPRF = @intCNTPPRF - 1
			Insert into ##TempResult values (' ')
		END
/* Finding EMpty fields mappings ends here  */


/* Finding No rules starts here  */
	ELSE
		BEGIN
			/*  Below cursor finds table which has got M status but actullay no rules prsent i.e. user might have just checked out and in*/
			DECLARE NoRuleButMaintTabl_CURSOR CURSOR STATIC FOR
			select bcmap120.tcpac+bcmap120.tcmod+bcmap120.tflno,ldat,lusr
			from bcmap120 where ( prtx LIKE '' OR prtx IS NULL) and mprf = @intPPRF and stat='M' and
			lusr between @I_StrUsr and @I_StrUsrTo 

			IF(@TargetType=0)
				BEGIN
			/*  Below cursor finds table which has got No Rules in case of Non Baan as target */
					DECLARE   NoRuleTables_CURSOR   CURSOR STATIC FOR
					select cpac+cmod+flno from bclgy140 where bclgy140.appl=@strTargetApp
					and cpac+cmod+flno between @I_StrPac and @I_StrPacTo
					and cpac+cmod+flno not in (select bcmap120.tcpac+bcmap120.tcmod+bcmap120.tflno from bcmap120 where mprf=@intPPRF)
					order by cpac+cmod+flno


				END

			ELSE

				BEGIN
			/*  Below cursor finds table which has got No Rules in case of Baan as target */
					DECLARE NoRuleTables_CURSOR   CURSOR STATIC FOR
					select cpac+cmod+flno from bcddi140 where bcddi140.appl=@strTargetApp
					and cpac+cmod+flno between @I_StrPac and @I_StrPacTo
					and cpac+cmod+flno not in (select bcmap120.tcpac+bcmap120.tcmod+bcmap120.tflno from bcmap120 where mprf=@intPPRF)
					order by cpac+cmod+flno

				END

			/*  Retrieve and insert both cursor data into ##TempNoRules  table */

				OPEN NoRuleTables_CURSOR;

				FETCH NEXT FROM NoRuleTables_CURSOR INTO @strNoRuleTable;
				SET @intCNTTab = @@CURSOR_ROWS

				--IF(@intCNTTab>0)
				--BEGIN
					--SET @strTemp = 'Profile : ' + STR(@intPPRF)
					--Insert into ##TempNoRules values (@strTemp + '^' + '' + '^' + '' +'^' + '')
					--Insert into ##TempNoRules values ('---------------' +'^' + '---------------' + '^' + '---------------' + '^'  + '---------------' + '^' )
				--END


				WHILE @intCNTTab > 0
				BEGIN
					Insert into ##TempNoRules values (STR(@intPPRF) + '^' + @strNoRuleTable + '^' + '-' + '^' + '-' +'^' + 'No-Rules')
					FETCH NEXT FROM NoRuleTables_CURSOR INTO @strNoRuleTable;
					SET @intCNTTab = @intCNTTab - 1
					CONTINUE

				END

				--Insert into ##TempNoRules values ('--------------------------')

				CLOSE NoRuleTables_CURSOR;
				DEALLOCATE NoRuleTables_CURSOR;

				/* Now retrieve records from No rules but maintaied cursor  */
				OPEN NoRuleButMaintTabl_CURSOR;


				FETCH NEXT FROM NoRuleButMaintTabl_CURSOR INTO @strNoRuleTable_ButM,@DateModified,@UserModified;
				SET @intCNTTab_M = @@CURSOR_ROWS

				--IF( @intCNTTab_M > 0)
				--BEGIN
					--SET @strTemp = 'Profile : ' + STR(@intPPRF)
					--Insert into ##TempNoRules values (@strTemp + '^' + '' + '^' + '' +'^' + '')
					--Insert into ##TempNoRules values ('---------------' +'^' + '---------------' + '^' + '---------------' + '^'  + '---------------' + '^' )
				--END

				WHILE @intCNTTab_M > 0
				BEGIN
					DECLARE @tempSTR as varchar(8000)
					SET @tempSTR = STR(@intPPRF) +  '^' + @strNoRuleTable_ButM + '^' + CONVERT(varchar,@DateModified,100) + '^' +  @UserModified +'^' + 'Maintained'
					Insert into ##TempNoRules values (@tempSTR)

					FETCH NEXT FROM NoRuleButMaintTabl_CURSOR INTO @strNoRuleTable_ButM,@DateModified,@UserModified;
						SET @intCNTTab_M = @intCNTTab_M - 1
					CONTINUE

				END ;

			--	Insert into ##TempNoRules values ('---------------' +'^' + '---------------' + '^' + '---------------' + '^'  + '---------------' + '^' )

				CLOSE NoRuleButMaintTabl_CURSOR;
				DEALLOCATE NoRuleButMaintTabl_CURSOR;
				Insert into ##TempNoRules values (''+ '^' + '' +'^' + '' + '^' + '' + '^'  + '' + '^' )
				FETCH NEXT FROM BCMAP101_CURSOR into @intPPRF
				SET @intCNTPPRF = @intCNTPPRF - 1



		END
		/* Finding No rules ENds  here  */




	CONTINUE


CLOSE BCMAP101_CURSOR
DEALLOCATE BCMAP101_CURSOR

END

GO
/* LND-9330.en */

/********************************************************************************
*	NAME:		sp_CreateMappingForChangedTables			*
*	DESCRIPTION:	This stored procedure creates default mappings for all 	*
*			changed tables.						*
*	INPUTS:	The Source Application 						*
*			The Target Application					*
*			The Version Release and Customer			*
*			The Migration Profile					*
*			Whether the Migration profile is SCM enabled?		*
*			DDC Comparison Run number				*
*			The <Package><Module><Table>				*
*	CALLS:		1. sp_insert_bcmap140_1					*
*			2. sp_insert_bcmap145_1					*
*	RETURNS:	ON SUCCESS - -0						*
*			ON ERROR    Value <> 0					*
*	CREATED BY:	S. Anand Kumar						*
* Updated - On 30 /Oct/2001							*
*	-Adding User Name as the Parameter.	@I_UserName			*
* Updated - On2- Nov - 2002							*
*	-Added the Depth field check so that if the Target and the source	*
*	-fields are depth fields, they are mapped accordingly.			*
*	-Removed applying funcions like Left.Align, Right.Align,ToUpper$,	*
*	-ToLower$ functions							*
********************************************************************************/
CREATE PROCEDURE [sp_CreateMappingForChangedTables]
 (
	  @I_StrTargetappl 	[varchar](15),
	  @I_StrSourceappl 	[varchar](15),
	  @I_StrVer  		[varchar](4),
	  @I_StrRel  		[varchar](2),
	  @I_StrCust  		[varchar](4),
	  @I_StrSCM  		[varchar](1),
	  @I_IntRunn  		[numeric](10,5),
	  @I_IntMprf   		[int],
	  @I_StrPackage  	[varchar](2),
	  @I_StrModule   	[varchar](3),
	  @I_StrTable  		[varchar](3),
	  @I_UserName		[varchar](12),
   	  @I_StrLogFileName	[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
 )
	-- LND2-26093.c Parameter @I_IntRunn changed
 AS

	 DECLARE @StrField    		as varchar(20)		--Field Name.
	 DECLARE @StrTempField  	as varchar(20)		--Temporary Var. to hold.
	 DECLARE @StrText    		as varchar(1024)	--Text to hold the Generated rule.
	 DECLARE @StrTempTableName  	as varchar(8)		--Temp Var. to hold Table Name.
	 DECLARE @StrTempTableName1 	as varchar(8)		--Temp Var. to hold Table Name.
	 DECLARE @StrSrcDomain  	as varchar(14)		--Var. to Hold Source Domain.
	 DECLARE @StrTrgDomain  	as varchar(14)		--Var. to Hold Target Domain.
	 DECLARE @StrCType   		as varchar(1)		--Var. to hold the Field Type.
	 DECLARE @IntFieldDumpType  	as int			--Var. to hold the Field dump type. (1/2)
	 DECLARE @IntTrgDepth   	as int			--Field Depth	 - Target
	 DECLARE @IntSrcDepth   	as int			--Field Depth	 - source
	 DECLARE @IntRetVal		as int			--Var. to hold the Return Value.
	 DECLARE @StrTemp   		as varchar(1024)	--Temporary Variable
	 DECLARE @StrVarName  		as varchar(100)	 	--Variable to hold the Type 2 variable name.

	 --VARIABLES TO  HOLD THE DOMAIN ATTRIBUTES
	 DECLARE @StrSrcDType  		as varchar(1)		--Source Domain Type
	 DECLARE @IntSrcDLen   		as int			--Source Domain Length
	 DECLARE @StrSrcDAlign  	as varchar(1)		--Source Domain	Align.
	 DECLARE @StrSrcDCase	  	as varchar(1)		--Source Domain	Case.
	 DECLARE @IntSrcDDigb  		as int			--Source Domain Digit before Decimal.
	 DECLARE @IntSrcDDiga  		as int 			--Source Domain Digit After Decimal.
	 DECLARE @StrSrcMan   		as varchar(1)		--Source Domain - Mandatory or not?
	 DECLARE @StrSrcInitialValue	as nvarchar(60)		--Initial Value of the Target Field


	 DECLARE @StrTrgDType  		as varchar(1)		--Target Domain Type
	 DECLARE @IntTrgDLen   		as int       		--Target Domain Length
	 DECLARE @StrTrgDAlign  	as varchar(1)		--Target Domain	Align.
	 DECLARE @StrTrgDCase	  	as varchar(1)		--Target Domain	Case.
	 DECLARE @IntTrgDDigb  		as int       		--Target Domain Digit before Decimal.
	 DECLARE @IntTrgDDiga  		as int       		--Target Domain Digit After Decimal.
	 DECLARE @StrTrgMan   		as varchar(1)		--Target Domain - Mandatory or not?
	 DECLARE @StrTrgInitialValue	as nvarchar(60)		--Initial Value of the Target Field

	 /*STEP 1. Get all the Unchanged/Changed and New Fields Records from BCDDC150*/

	 DECLARE DDC150_Cursor CURSOR FOR

	 SELECT 	[dbo].[bcddc150].fdnm,
			[dbo].[bcddc150].sddo,
			[dbo].[bcddc150].tddo,
			[dbo].[bcddc150].sdep,
			[dbo].[bcddc150].tdep,
			[dbo].[bcddc150].ctyp,
			[dbo].[bcddc150].sman,
			[dbo].[bcddc150].tman,
  			[dbo].[bcddc150].sini,
  			[dbo].[bcddc150].tini
	 FROM   	[dbo].[bcddc150]
	 WHERE 		[dbo].[bcddc150].runn  = @I_IntRunn AND [dbo].[bcddc150].ctyp <> 'R'
	 AND    	[dbo].[bcddc150].cpac  = @I_StrPackage
	 AND  		[dbo].[bcddc150].cmod  = @I_StrModule
	 AND  		[dbo].[bcddc150].flno  = @I_StrTable
	 AND 		[dbo].[bcddc150].tcom  = 'N'
	 ORDER BY  	[dbo].[bcddc150].fdnm,
	   		[dbo].[bcddc150].ctyp

	 OPEN DDC150_Cursor
	 SET @IntTrgDepth	  = 0
	 SET @IntSrcDepth	  = 0
	 FETCH NEXT FROM DDC150_Cursor
	 INTO    @StrField, @StrSrcDomain, @StrTrgDomain,@IntSrcDepth, @IntTrgDepth,
		 @StrCType,@StrSrcMan, @StrTrgMan,@StrSrcInitialValue, @StrTrgInitialValue

	 WHILE 	  @@FETCH_STATUS = 0
	 BEGIN
		  SET @StrSrcDType  	=''
		  SET @IntSrcDLen 	=0
		  SET @StrSrcDAlign 	=''
		  SET @IntSrcDDigb 	=0
		  SET @IntSrcDDiga      =0
		  SET @StrSrcDCase	=''

		  SET @StrTrgDType  	=''
		  SET @IntTrgDLen 	=0
		  SET @StrTrgDAlign 	=''
		  SET @IntTrgDDigb 	=0
		  SET @IntTrgDDiga 	=0
		  SET @StrTrgDCase	=''

		  SET @StrTempField = @StrField

		  /*Get the Source Domain Information */
	  	  SELECT    @StrSrcDType  	= [dbo].[bcddi220].type,
			    @IntSrcDLen 	= [dbo].[bcddi220].leng,
			    @StrSrcDAlign 	= [dbo].[bcddi220].adju,
			    @IntSrcDDigb 	= [dbo].[bcddi220].digb,
			    @IntSrcDDiga 	= [dbo].[bcddi220].diga,
			    @StrSrcDCase	= [dbo].[bcddi220].conv
		  FROM      [dbo].[bcddi220]
		  WHERE     [dbo].[bcddi220].appl = @I_StrSourceappl
		  AND  	    (([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrSrcDomain)

		  /*Get the TARGET Domain Information */
		  SELECT    @StrTrgDType  	= [dbo].[bcddi220].type,
			    @IntTrgDLen 	= [dbo].[bcddi220].leng,
			    @StrTrgDAlign 	= [dbo].[bcddi220].adju,
			    @IntTrgDDigb 	= [dbo].[bcddi220].digb,
			    @IntTrgDDiga 	= [dbo].[bcddi220].diga,
			    @StrTrgDCase 	= [dbo].[bcddi220].conv
		  FROM      [dbo].[bcddi220]
		  WHERE     [dbo].[bcddi220].appl = @I_StrTargetappl
		  AND       (([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrTrgDomain)

		  WHILE @IntTrgDepth >= 0
		  BEGIN
			SET @StrSrcDType 	= ISNULL(@StrSrcDType,'0')
			SET @IntSrcDLen 	= ISNULL(@IntSrcDLen,0)
			SET @StrSrcDAlign 	= ISNULL(@StrSrcDAlign,'')
			SET @StrSrcDCase 	= ISNULL(@StrSrcDCase,'')
			SET @IntSrcDDigb 	= ISNULL(@IntSrcDDigb,0)
			SET @IntSrcDDiga	= ISNULL(@IntSrcDDiga,0)
			SET @StrSrcInitialValue = ISNULL(@StrSrcInitialValue,'0')
			SET @StrTrgDType 	= ISNULL(@StrTrgDType,'0')
			SET @IntTrgDLen 	= ISNULL(@IntTrgDLen,0)
			SET @StrTrgDAlign 	= ISNULL(@StrTrgDAlign,'')
			SET @StrTrgDCase 	= ISNULL(@StrTrgDCase,'')
			SET @IntTrgDDigb 	= ISNULL(@IntTrgDDigb,0)
			SET @IntTrgDDiga 	= ISNULL(@IntTrgDDiga,0)
			SET @StrTrgInitialValue = ISNULL(@StrTrgInitialValue,'0')

/* 
   SYNCHRONIZE WITH [sp_CreateMappingsForFineTunedChangedTables] START
   -------------------------------------------------------------------
*/

			   /* The Depth of the Field is > 0. So all these Field are assigned Default Values*/
			   IF  @IntTrgDepth > @IntSrcDepth
			   BEGIN
-- Cycle3 .so			IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
--				BEGIN
--					IF @StrTrgInitialValue = '0'
--					BEGIN
--						SET @StrTrgInitialValue = 'String.Set$("",' + LTRIM(STR(@IntTrgDLen)) + ')'
--					END
--				END
--
--		        	SET @StrText = CASE @StrTrgDType
--						        WHEN 'B' THEN @StrTrgInitialValue
--						        WHEN 'I' THEN @StrTrgInitialValue
--						        WHEN 'L' THEN @StrTrgInitialValue
--						        WHEN 'D' THEN @StrTrgInitialValue
--						        WHEN 'F' THEN @StrTrgInitialValue
--						        WHEN 'S' THEN @StrTrgInitialValue
--						        WHEN 'E' THEN @StrTrgInitialValue
--						        WHEN 'T' THEN 'utc(0,0,2)'
--						        WHEN 'U' THEN @StrTrgInitialValue
--						        WHEN 'M' THEN @StrTrgInitialValue
--						        WHEN 'K' THEN 'utc(0,0)'
--						        ELSE '0'
-- Cycle3 .eo		 		          	  END
				SET @StrText = 'default()' -- Cycle3 .n
			     	SET @IntFieldDumpType = 0
				SET @StrField = @StrTempField + '(' +  LTRIM(STR(@IntTrgDepth)) + ')'
			   END

			   ELSE
			   BEGIN

				     IF @IntTrgDepth >= 1
				     BEGIN
					SET @StrField = @StrTempField + '(' + LTRIM(STR(@IntTrgDepth)) + ')'
					--check whether the Target is a string. If so add "1,1" to the value
					IF @StrTrgDType = 'S' or @StrTrgDType = 'M'
					BEGIN
						SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrTempField + '(1,' + LTRIM(STR(@IntTrgDepth)) + ')'
					END
					ELSE
					BEGIN
						SET @StrText= @I_StrPackage + @I_StrModule + @I_StrTable  +  '.'  + @StrField
					END
				     END
				     /* The Field is a New Field. So Map Default Values for it.*/
				     IF @StrCType = 'N'
				     BEGIN
-- Cycle3 .so					IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
--						BEGIN
--							IF @StrTrgInitialValue = '0'
--							BEGIN
--								SET @StrTrgInitialValue = 'String.Set$("",' + LTRIM(STR(@IntTrgDLen)) + ')'
--							END
--						END
--					       	SET @StrText = CASE @StrTrgDType
--								WHEN 'B' THEN @StrTrgInitialValue
--							        WHEN 'I' THEN @StrTrgInitialValue
--							        WHEN 'L' THEN @StrTrgInitialValue
--							        WHEN 'D' THEN @StrTrgInitialValue
--							        WHEN 'F' THEN @StrTrgInitialValue
--							        WHEN 'S' THEN @StrTrgInitialValue
--							        WHEN 'E' THEN @StrTrgInitialValue
--							        WHEN 'T' THEN 'utc(0,0,2)'
--							        WHEN 'U' THEN @StrTrgInitialValue
--							        WHEN 'M' THEN @StrTrgInitialValue
--							        WHEN 'K' THEN 'utc(0,0)'
--							        ELSE '0'
-- Cycle3 .eo					END
						SET @StrText = 'default()' -- Cycle3 .n
					       	SET @IntFieldDumpType=0
				     END

				     /* The Field is Changed or Unchanged one*/
				     IF @StrCType = 'U' OR @StrCType = 'C'
				     BEGIN

					       SET @StrText = @I_StrPackage + @I_StrModule + @I_StrTable +  '.'  + @StrField

					       /* 1. Target is ENUM  Dataype  */
					       IF @StrTrgDType  = 'E'
					       BEGIN
						         SET @StrText = CASE  @StrSrcDType
								            WHEN 'E'  THEN  @StrText --Enum Data Type
								            WHEN 'I'  THEN  @StrText --INT Data Type
								            WHEN 'L'  THEN  @StrText --INT Data Type
-- Cycle3 .o								    ELSE @StrTrgInitialValue  --Initial Value
									    ELSE 'default()'  --Initial Value -- Cycle3 .n
								        END
					       END

						/* LND-9338.so */
					   /* 2. Target is STRING DataType
					   IF @StrTrgDType = 'S'
					   BEGIN*/
							 /* Check for the  ALignment */
						 /* Done in XML generation */

							 /* check for the Case */
							 /* done is XML Generation */
					   /*END*/
					    /* LND-9338.eo */

						/* LND-9338.sn */
						/* 2. Target is STRING DataType */
						IF @StrTrgDType = 'S' 
						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'S' THEN	
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN		/* Source smaller or equal than Target */
											@StrText
									   ELSE										/* String does not Fit -> Action */
											''
									   END
									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */ 
										''
									END
						END
						/* LND-9338.en */
						
												/* LND-9325.sn */
						/* 2. Target is STRING DataType */
						IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'	
						BEGIN
							IF @IntTrgDepth >= 1
							BEGIN
								SET @StrText = @I_StrPackage + @I_StrModule + @I_StrTable  +  '.' + @StrTempField + '(1,' + LTRIM(STR(@IntTrgDepth)) + ')'
							END
						END
												/* LND-9325.en */


		


					       /* 3. Target is UTC Datatype */
					       IF @StrTrgDType = 'K'
					       BEGIN
						         Set @StrText = CASE  	@StrSrcDType
								        WHEN 'T'  THEN  'utc(' + @StrText +',0)'    --Date Data Type
								        WHEN 'K'  THEN  'utc$(' + @StrText +')'     --UTC Data Type
-- Cycle3 .o								ELSE 'utc(0,0)'
									ELSE 'empty()' -- Empty value -- Cycle3 .n
							           	END
					       END

					       /* 4. Target is BYTE Datatype */
					       IF @StrTrgDType = 'B'
					       BEGIN
						         --SET @StrText = 'to.byte(' + @StrText + ')'
						         SET @StrText = @StrText
					       END

						/* LND-9338.sn */
						/* 5 + 6. Target is FLOAT or DOUBLE Datatype */
						IF  @StrTrgDType = 'F' OR @StrTrgDType ='D' OR
							@StrTrgDType = 'I' OR @StrTrgDType ='L'

						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'D' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'F' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'I' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'B' THEN    -- LND2-26093.sn
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END    -- LND2-26093.en
								   WHEN 'L' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END

									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */

										''
									END
						END
						/* LND-9338.en */

						/* LND-9338.so
					       /* 5. Target is INTEGER / LONG Datatype */
					       IF @StrTrgDType = 'I' OR  @StrTrgDType ='L'
			                      	       BEGIN
						               SET @StrText = 	CASE @StrSrcDType
						             		    	WHEN 'I' THEN @StrText
					                           		WHEN 'L' THEN @StrText
					                           		WHEN 'B' THEN @StrText  -- LND2-26093.n
-- Cycle3 .o						                        ELSE '0' + '|Source field is of diff. Data types'
										ELSE 'empty()' -- Cycle3 .n
			             				             	END

					       END

		                   	      /* 6. Target is FLOAT /DOUBLE Datatype */
		                   	      IF @StrTrgDType = 'F' OR @StrTrgDType ='D'
		                   	      BEGIN
		                    	       	SET @StrText = CASE @StrSrcDType
						             	     	WHEN 'D' THEN @StrText
		        	                			  	WHEN 'F' THEN @StrText
-- Cycle3 .o		                	         	  		ELSE '0' + '|Source field is of diff. Data types'
										ELSE 'empty()' -- Cycle3 .n
			        	                	  	END

		                   	       END
		                  		 LND-9338.eo */

					       /* 7. Target is DATE Datatype */
					       IF @StrTrgDType = 'T'
					       BEGIN
					       		SET @StrText =   CASE @StrSrcDType
-- Cycle3 .o					                	        WHEN 'T'  THEN 'utc('+ @StrText + '0,2)' --Source is Date
										WHEN 'T'  THEN 'utc('+ @StrText + ',0,2)' --Source is Date -- Cycle3 .n
                        							WHEN 'K'  THEN 'date.utc(' + @StrText + ')' --source is UTC
-- Cycle3 .o			                          			ELSE 'utc(0,0,2)'
										ELSE 'empty()' -- Cycle3 .n
					                         	 END
					       END

						/* LND-9338.sn */
						/* 8. Target is MULTI BYTE DataType */
						IF @StrTrgDType = 'M' 
						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'S' THEN
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN	/* Source smaller or equal than Target */
											@StrText
									   ELSE		/* String does not Fit -> Action */
											''
									   END
								   WHEN 'M' THEN
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN	/* Source smaller or equal than Target */
											@StrText
									   ELSE		/* String does not Fit -> Action */
											''
									   END


									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */

										''
									END
						END
						/* LND-9338.en */
						
						/* LND-9338.so
					       /* 8. Target is MULTI BYTE DataType */

					       /*IF @StrTrgDType = 'M'
					       BEGIN*/
					       		/* Handled in XML Generation */
					       /*END*/
					      LND-9338.eo */


			                       /* Check for TARGET Mandatory and SOURCE NOT Mandatory */
			                       IF @StrTrgMan = 'M' and @StrSrcMan ='N'
					       BEGIN

			      				SET @StrVarName = 'var.' + @I_StrPackage + @I_StrModule + @I_StrTable +  '.'  + @StrField

				                	SET @StrTemp = CASE @StrTrgDType
								            WHEN 'B' THEN '0'
								            WHEN 'I' THEN '0'
								            WHEN 'L' THEN '0'
								            WHEN 'D' THEN '0'
								            WHEN 'F' THEN '0'
								            WHEN 'S' THEN  'bcrun.convdll.string("",' + LTRIM(STR(@IntTrgDLen)) + ')'    -- LND2-9769.c
								            WHEN 'E' THEN  '0'
								            WHEN 'T' THEN  'utc(0,0,2)'
								            WHEN 'U' THEN  '0'
								            WHEN 'M' THEN  'bcrun.convdll.stringmb("",' + LTRIM(STR(@IntTrgDLen)) + ')'  -- LND2-9769.c
								            WHEN 'K' THEN  'utc(0,0)'
									    ELSE '0'
								       END

					           	/* WRITE THE TEXT FOR THE FIELD OF TYPE 2
							 if isspace(Field) then
						            	Var.FieldName = Default Value
					              	endif */

				                	SET @StrText = '|* The Target field is a Mandatory Field but the Source is' + char(13) + char(10) +
				            			       '|* Not a Mandatory Field. So  Default Value is Filled here.' + char(13) + char(10) +
				            			       char(9) + 'if isspace(' +  @StrText + ') then' + char(13)  + char(10) +
					           		       char(9) + char(9) + @StrVarName + '=' + @StrTemp + char(13)  + char(10) +
					           		       char(9) + 'endif'

					         	SET @IntFieldDumpType = 1
					        END
					        ELSE
					        BEGIN
						         SET @StrVarName = ''
						         SET @StrTemp = ''
						         SET @IntFieldDumpType=0
					        END
		                     END  --IF @StrCType = 'U' OR @StrCType = 'C'  END

			   END  -- ELSE of @IntTrgDepth > 0 END

/*
   SYNCHRONIZE WITH [sp_CreateMappingsForFineTunedChangedTables] END
   -----------------------------------------------------------------
*/


			   --Insert the Field values into BCMAP140

			   EXEC @IntRetVal = sp_insert_bcmap140_1 	@I_IntMprf,		--Migration Profile
					   				@I_StrVer,		--Version
					   				@I_StrRel,		--Release
					   				@I_StrCust,		--Customer
				   					@I_StrPackage,		--Package
				   					@I_StrModule,		--Module
				        			 	@I_StrTable,		--Table
							        	1,			--Dump Number
						        		@StrField,		--Field Name
						        		@StrText,		--Field Value - Text
							        	@IntFieldDumpType,	--Type of the Value Type 0/1
							        	'N'			-- N- Not Approved  Rule not Approved

			   IF (@IntRetVal <> 0)
			   BEGIN
				-- Error So BREAK
				SELECT @StrTemp = [master].[dbo].[sysmessages].description
				FROM   [master].[dbo].[sysmessages]
				WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

				SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
						 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
					         ' VRC  - '   + @I_StrVer	+  '-' +
				                                @I_StrRel	+  '-' +
				                                @I_StrCust	+
						 ' TABLE - '  +	@I_StrPackage	+
				                                @I_StrModule	+
				                                @I_StrTable	+  ' ' +
				                 ' FIELD - '  + @StrField  	+
				                 ' into BCMAP140. '

				EXEC Write_to_file @StrTemp, @I_StrLogFileName
			   	BREAK
			   END

			   --If the Profile is SCM enabled then  Insert another Record in BCMAP145
			   IF @I_StrSCM = 'Y'
			   BEGIN
			    	EXEC @IntRetVal =sp_insert_bcmap145_1 	@I_IntMprf,		--Migration Profile
				    					@I_StrVer,              --Version
				    					@I_StrRel,              --Release
				    					@I_StrCust,             --Customer
			    						@I_StrPackage,          --Package
			    						@I_StrModule,           --Module
						            		@I_StrTable,            --Table
						            		1,                      --Dump Number
				            				@StrField,              --Field Name
			            					1,                      --Revision Info
			            					@StrText,               --Field Value - Text
					            			@IntFieldDumpType,      --Tupe of the Value Type 0/1
					            			@I_UserName,            --User Name
			            					'N' ,			--N- Not Approved  Rule not Approved
									'C'			--Status of the Component C- New/Changed
				IF @IntRetVal <> 0
				BEGIN
					-- ERROR So Break
					SELECT @StrTemp = [master].[dbo].[sysmessages].description
					FROM   [master].[dbo].[sysmessages]
					WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

					SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
							 ' MPRF - '   + STR(@I_IntMprf)	+  ' ' +
						         ' VRC  - '   + @I_StrVer	+  '-' +
					                                @I_StrRel	+  '-' +
					                                @I_StrCust	+
							 ' TABLE - '  +	@I_StrPackage	+
					                                @I_StrModule	+
					                                @I_StrTable	+  ' ' +
					                 ' FIELD - '  + @StrField  	+
					                 ' into BCMAP145. '

					EXEC Write_to_file @StrTemp, @I_StrLogFileName
					BREAK
				END
			   END

			   SET @IntTrgDepth = @IntTrgDepth - 1
			   IF @IntTrgDepth = 0 BREAK      /*  This is to Avoid one more looping in case the  *
			   				*  Depth of the Field is more than 0.		  */

	  	END  --WHILE @IntTrgDepth > 0 then

		IF @IntRetVal <> 0
		BEGIN
			 CLOSE DDC150_Cursor
			 DEALLOCATE DDC150_Cursor
 			 RETURN @IntRetVal
		END
		 SET @IntTrgDepth	  = 0
		 SET @IntSrcDepth	  = 0

		FETCH NEXT FROM DDC150_Cursor
		INTO    @StrField, @StrSrcDomain, @StrTrgDomain,@IntSrcDepth, @IntTrgDepth, @StrCType,
			@StrSrcMan, @StrTrgMan,@StrSrcInitialValue, @StrTrgInitialValue

	 END   -- WHILE  @@FETCH_STATUS = 0

	 CLOSE DDC150_Cursor
	 DEALLOCATE DDC150_Cursor
	 RETURN @IntRetVal
GO




/****** Object:  Stored Procedure dbo.sp_CreateMappingsForFineTunedChangedTables    Script Date: 10/6/2003 2:36:55 PM ******/
/****** Object:  Stored Procedure dbo.sp_CreateMappingsForFineTunedChangedTables    Script Date: 8/29/2003 10:56:09 AM ******/
/********************************************************************************
*	NAME:		sp_CreateMappingsForFineTunedChangedTables		*
*	DESCRPTION:	This Stored procedure generates default mappings for 	*
*			all fine tuned CHANGED tables.				*
*	INPUTS:		The Source Application 					*
*			The Target Application					*
*			The Version Release and Customer			*
*			The Migration Profile					*
*			Whether the Migration profile is SCM enabled?		*
*			DDC Comparison Run number				*
*			The Source <Package><Module><Table>			*
*			The Target <Package><Module><Table>			*
*	CALLS:		1.sp_insert_bcmap140_1()				*
*			2.sp_insert_bcmap145_1()				*
*	CALLED BY:	sp_CreateDefaultMappings()				*
*	RETURNS:	ON SUCCESS - -0						*
*			ON ERROR    Value <> 0					*
*	CREATED BY:	S. Anand Kumar						*
*	UPDATION:								*
*		- 31/Oct/2001							*
*			- Parameter @I_UserName	[varchar](12) Added		*
*			- Replace "root" by @I_UserName				*
*		-2/Nov/2001							*
*			- Check for the Depth field				*
********************************************************************************/
CREATE PROCEDURE [sp_CreateMappingsForFineTunedChangedTables]
	(
	 @I_StrTargetappl		[varchar](15),
	 @I_StrSourceappl		[varchar](15),
	 @I_StrVer			[varchar](4),
	 @I_StrRel			[varchar](2),
	 @I_StrCust			[varchar](4),
	 @I_StrSCM			[varchar](1),
	 @I_IntRunn			[numeric](10,5),
	 @I_IntMprf 			[int],
	 @I_StrSrcPackage 		[varchar](2),
	 @I_StrSrcModule 		[varchar](3),
	 @I_StrSrcTable 		[varchar](3),
	 @I_StrTrgPackage 		[varchar](2),
	 @I_StrTrgModule 		[varchar](3),
	 @I_StrTrgTable 		[varchar](3),
	 @I_UserName			[varchar](12),
   	 @I_StrLogFileName		[varchar](1024) = "C:\Temp\DefaultMapGenLog.txt"
	 )
	-- LND2-26093.c  Parameter @I_IntRunn changed
 AS
	DECLARE @StrField 		as varchar(15)
	DECLARE @StrTempField		as varchar(15)
	DECLARE @StrText 		as varchar(1024)
	DECLARE @StrTempTableName 	as varchar(8)
	DECLARE @StrTempTableName1	as varchar(8)
	DECLARE @StrSrcDomain		as varchar(14)
	DECLARE @StrTrgDomain		as varchar(14)
	DECLARE @Strctype		as varchar(1)
	DECLARE @IntFieldDumpType	as int
	DECLARE @IntRetVal		as int

	--VARIABLES TO  HOLD THE DOMAIN ATTRIBUTES
	DECLARE @StrSrcDType		as varchar(1)
	DECLARE @IntSrcDLen		as int
	DECLARE @StrSrcDAlign		as varchar(1)
	DECLARE @StrSrcDCase		as varchar(1)
	DECLARE @IntSrcDDigb		as int
	DECLARE @IntSrcDDiga		as int
	DECLARE @IntSrcDepth		as int
	DECLARE @IntTrgDepth		as int
	DECLARE @StrSrcMan		as varchar(1)

	DECLARE @StrTrgDType		as varchar(1)
	DECLARE @IntTrgDLen		as int
	DECLARE @StrTrgDAlign		as varchar(1)
	DECLARE @StrTrgDCase		as varchar(1)
	DECLARE @IntTrgDDigb		as int
	DECLARE @IntTrgDDiga		as int
	DECLARE @StrTrgMan		as varchar(1)
	DECLARE @StrTemp		as varchar(1024)
	DECLARE @StrVarName		as varchar(100)
	DECLARE @StrTrgInitialValue	as nvarchar(60)


	/*Get all the Unchanged Tables Records from BCDDC150*/
	DECLARE DDC150_Cursor CURSOR FOR
	SELECT		[dbo].[bcddc150].fdnm,
			[dbo].[bcddc150].sddo,
			[dbo].[bcddc150].tddo,
			[dbo].[bcddc150].sdep,
			[dbo].[bcddc150].tdep,
			[dbo].[bcddc150].ctyp,
			[dbo].[bcddc150].sman,
			[dbo].[bcddc150].tman,
			[dbo].[bcddc150].tini
	FROM 		[dbo].[bcddc150]
	WHERE		[dbo].[bcddc150].runn  = @I_IntRunn
	AND 		[dbo].[bcddc150].ctyp <> 'R'
	AND 		[dbo].[bcddc150].tcom =  'N'
	AND  		[dbo].[bcddc150].cpac  = @I_StrSrcPackage
	AND		[dbo].[bcddc150].cmod  = @I_StrSrcModule
	AND		[dbo].[bcddc150].flno  = @I_StrSrcTable
	AND  		[dbo].[bcddc150].tpac  = @I_StrTrgPackage
	AND		[dbo].[bcddc150].tmod  = @I_StrTrgModule
	AND		[dbo].[bcddc150].tlno  = @I_StrTrgTable
	ORDER BY	[dbo].[bcddc150].fdnm,
			[dbo].[bcddc150].ctyp

	OPEN DDC150_Cursor

	set @IntTrgDepth = 0
	set @IntSrcDepth = 0

	FETCH NEXT FROM DDC150_Cursor
	INTO   	@StrField, @StrSrcDomain, @StrTrgDomain, @IntSrcDepth,@IntTrgDepth,@Strctype,@StrSrcMan, @StrTrgMan,@StrTrgInitialValue

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @StrSrcDType 	=''
	        SET @IntSrcDLen		=0
		SET @StrSrcDAlign	=''
		SET @IntSrcDDigb	=0
		SET @IntSrcDDiga        =0
		SET @StrSrcDCase	=''

		SET @StrTrgDType 	=''
		SET @IntTrgDLen		=0
		SET @StrTrgDAlign	=''
		SET @StrTrgDCase	=''
		SET @IntTrgDDigb	=0
		SET @IntTrgDDiga	=0

		SET @StrTempField = @StrField	-- Store the field Name in a Temporary Variable

		/*Get the Source Domain Information */
		SELECT	@StrSrcDType 	= [dbo].[bcddi220].type,
			@IntSrcDLen	= [dbo].[bcddi220].leng,
			@StrSrcDAlign	= [dbo].[bcddi220].adju,
			@IntSrcDDigb	= [dbo].[bcddi220].digb,
			@IntSrcDDiga	= [dbo].[bcddi220].diga,
			@StrSrcDCase	= [dbo].[bcddi220].conv
		FROM 	[dbo].[bcddi220]
		WHERE	[dbo].[bcddi220].appl = @I_StrSourceappl
		AND	(([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrSrcDomain)

		/*Get the TARGET Domain Information */
		SELECT	@StrTrgDType 	= [dbo].[bcddi220].type,
			@IntTrgDLen	= [dbo].[bcddi220].leng,
			@StrTrgDAlign	= [dbo].[bcddi220].adju,
			@IntTrgDDigb	= [dbo].[bcddi220].digb,
			@IntTrgDDiga	= [dbo].[bcddi220].diga,
			@StrTrgDCase	= [dbo].[bcddi220].conv
		FROM	[dbo].[bcddi220]
		WHERE	[dbo].[bcddi220].appl = @I_StrTargetappl
		AND	(([dbo].[bcddi220].cpac + [dbo].[bcddi220].cdom) = @StrTrgDomain)


		WHILE @IntTrgDepth >= 0
		BEGIN

			SET @StrSrcDType 	= ISNULL(@StrSrcDType,'0')
			SET @IntSrcDLen 	= ISNULL(@IntSrcDLen,0)
			SET @StrSrcDAlign 	= ISNULL(@StrSrcDAlign,'')
			SET @StrSrcDCase 	= ISNULL(@StrSrcDAlign,'')
			SET @IntSrcDDigb 	= ISNULL(@IntSrcDDigb,0)
			SET @IntSrcDDiga	= ISNULL(@IntSrcDDiga,0)
			SET @StrTrgDType 	= ISNULL(@StrTrgDType,'0')
			SET @IntTrgDLen 	= ISNULL(@IntTrgDLen,0)
			SET @StrTrgDAlign 	= ISNULL(@StrTrgDAlign,'')
			SET @StrTrgDCase 	= ISNULL(@StrTrgDAlign,'')
			SET @IntTrgDDigb 	= ISNULL(@IntTrgDDigb,0)
			SET @IntTrgDDiga 	= ISNULL(@IntTrgDDiga,0)
			SET @StrTrgInitialValue	= ISNULL(@StrTrgInitialValue,'0')


/* 
   SYNCHRONIZE WITH [sp_CreateMappingForChangedTables]           START
   -------------------------------------------------------------------
*/
			/* The Depth of the Field is > 0. So for all these Field Map Default Values*/
			IF @IntTrgDepth > @IntSrcDepth
				BEGIN
-- Cycle3 .so				IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
--				BEGIN
--					IF @StrTrgInitialValue = '0'
--					BEGIN
--						SET @StrTrgInitialValue = 'String.Set$("",' + LTRIM(STR(@IntTrgDLen)) + ')'
--					END
--				END
--
--		        	SET @StrText = CASE @StrTrgDType
--						        WHEN 'B' THEN @StrTrgInitialValue
--						        WHEN 'I' THEN @StrTrgInitialValue
--						        WHEN 'L' THEN @StrTrgInitialValue
--						        WHEN 'D' THEN @StrTrgInitialValue
--						        WHEN 'F' THEN @StrTrgInitialValue
--						        WHEN 'S' THEN @StrTrgInitialValue
--						        WHEN 'E' THEN @StrTrgInitialValue
--						        WHEN 'T' THEN 'utc(0,0,2)'
--						        WHEN 'U' THEN @StrTrgInitialValue
--						        WHEN 'M' THEN @StrTrgInitialValue
--						        WHEN 'K' THEN 'utc(0,0)'
--						        ELSE '0'
-- Cycle3 .eo		 		          	  END
				SET @StrText = 'default()' -- Cycle3 .n
			     	SET @IntFieldDumpType = 0
				SET @StrField = @StrTempField + '(' +  LTRIM(STR(@IntTrgDepth)) + ')'
			   END

			   ELSE
			   BEGIN

				     IF @IntTrgDepth >= 1
				     BEGIN
					SET @StrField = @StrTempField + '(' + LTRIM(STR(@IntTrgDepth)) + ')'
					--check whether the Target is a string. If so add "1,1" to the value
					IF @StrTrgDType = 'S' or @StrTrgDType = 'M'
					BEGIN
						SET @StrText= @I_StrTrgPackage + @I_StrTrgModule + @I_StrTrgTable  +  '.'  + @StrTempField + '(1,' + LTRIM(STR(@IntTrgDepth)) + ')'
					END
					ELSE
					BEGIN
						SET @StrText= @I_StrTrgPackage + @I_StrTrgModule + @I_StrTrgTable  +  '.'  + @StrField
					END
				     END
				     /* The Field is a New Field. So Map Default Values for it.*/
				     IF @StrCType = 'N'
				     BEGIN
-- Cycle3 .so					IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'
--						BEGIN
--							IF @StrTrgInitialValue = '0'
--							BEGIN
--								SET @StrTrgInitialValue = 'String.Set$("",' + LTRIM(STR(@IntTrgDLen)) + ')'
--							END
--						END
--					       	SET @StrText = CASE @StrTrgDType
--								WHEN 'B' THEN @StrTrgInitialValue
--							        WHEN 'I' THEN @StrTrgInitialValue
--							        WHEN 'L' THEN @StrTrgInitialValue
--							        WHEN 'D' THEN @StrTrgInitialValue
--							        WHEN 'F' THEN @StrTrgInitialValue
--							        WHEN 'S' THEN @StrTrgInitialValue
--							        WHEN 'E' THEN @StrTrgInitialValue
--							        WHEN 'T' THEN 'utc(0,0,2)'
--							        WHEN 'U' THEN @StrTrgInitialValue
--							        WHEN 'M' THEN @StrTrgInitialValue
--							        WHEN 'K' THEN 'utc(0,0)'
--							        ELSE '0'
-- Cycle3 .eo					END
						SET @StrText = 'default()' -- Cycle3 .n
					       	SET @IntFieldDumpType=0
				     END

				     /* The Field is Changed or Unchanged one*/
				     IF @StrCType = 'U' OR @StrCType = 'C'
				     BEGIN

					       SET @StrText = @I_StrTrgPackage + @I_StrTrgModule + @I_StrTrgTable +  '.'  + @StrField

					       /* 1. Target is ENUM  Dataype  */
					       IF @StrTrgDType  = 'E'
					       BEGIN
						         SET @StrText = CASE  @StrSrcDType
								            WHEN 'E'  THEN  @StrText --Enum Data Type
								            WHEN 'I'  THEN  @StrText --INT Data Type
								            WHEN 'L'  THEN  @StrText --INT Data Type
-- Cycle3 .o								    ELSE @StrTrgInitialValue  --Initial Value
									    ELSE 'default()'  --Initial Value -- Cycle3 .n
								        END
					       END

						/* LND-9338.so */
					   /* 2. Target is STRING DataType
					   IF @StrTrgDType = 'S'
					   BEGIN*/
							 /* Check for the  ALignment */
						 /* Done in XML generation */

							 /* check for the Case */
							 /* done is XML Generation */
					   /*END*/
					    /* LND-9338.eo */

						/* LND-9338.sn */
						/* 2. Target is STRING DataType */
						IF @StrTrgDType = 'S' 
						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'S' THEN	
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN		/* Source smaller or equal than Target */
											@StrText
									   ELSE										/* String does not Fit -> Action */
											''
									   END
									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */ 
										''
									END
						END
						/* LND-9338.en */
						
												/* LND-9325.sn */
						/* 2. Target is STRING DataType */
						IF @StrTrgDType = 'S' OR @StrTrgDType = 'M'	
						BEGIN
							IF @IntTrgDepth >= 1
							BEGIN
								SET @StrText = @I_StrTrgPackage + @I_StrTrgModule + @I_StrTrgTable  +  '.' + @StrTempField + '(1,' + LTRIM(STR(@IntTrgDepth)) + ')'
							END
						END
												/* LND-9325.en */


		


					       /* 3. Target is UTC Datatype */
					       IF @StrTrgDType = 'K'
					       BEGIN
						         Set @StrText = CASE  	@StrSrcDType
								        WHEN 'T'  THEN  'utc(' + @StrText +',0)'    --Date Data Type
								        WHEN 'K'  THEN  'utc$(' + @StrText +')'     --UTC Data Type
-- Cycle3 .o								ELSE 'utc(0,0)'
									ELSE 'empty()' -- Empty value -- Cycle3 .n
							           	END
					       END

					       /* 4. Target is BYTE Datatype */
					       IF @StrTrgDType = 'B'
					       BEGIN
						         --SET @StrText = 'to.byte(' + @StrText + ')'
						         SET @StrText = @StrText
					       END

						/* LND-9338.sn */
						/* 5 + 6. Target is FLOAT or DOUBLE Datatype */
						IF  @StrTrgDType = 'F' OR @StrTrgDType ='D' OR
							@StrTrgDType = 'I' OR @StrTrgDType ='L'

						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'D' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'F' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'I' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END
								   WHEN 'L' THEN
									   CASE
									   WHEN @IntSrcDDigb <= @IntTrgDDigb THEN
											CASE
											WHEN @IntSrcDDiga <= @IntTrgDDiga THEN
												@StrText
											ELSE
												''
											END
									   ELSE		/* Double does not Fit */
											''
									   END

									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */

										''
									END
						END
						/* LND-9338.en */

						/* LND-9338.so
					       /* 5. Target is INTEGER / LONG Datatype */
					       IF @StrTrgDType = 'I' OR  @StrTrgDType ='L'
			                      	       BEGIN
						               SET @StrText = 	CASE @StrSrcDType
						             		    	WHEN 'I' THEN @StrText
					                           		WHEN 'L' THEN @StrText
					                           		WHEN 'B' THEN @StrText   -- LND2-26093.n
-- Cycle3 .o						                        ELSE '0' + '|Source field is of diff. Data types'
										ELSE 'empty()' -- Cycle3 .n
			             				             	END

					       END

		                   	      /* 6. Target is FLOAT /DOUBLE Datatype */
		                   	      IF @StrTrgDType = 'F' OR @StrTrgDType ='D'
		                   	      BEGIN
		                    	       	SET @StrText = CASE @StrSrcDType
						             	     	WHEN 'D' THEN @StrText
		        	                			  	WHEN 'F' THEN @StrText
-- Cycle3 .o		                	         	  		ELSE '0' + '|Source field is of diff. Data types'
										ELSE 'empty()' -- Cycle3 .n
			        	                	  	END

		                   	       END
		                  		 LND-9338.eo */

					       /* 7. Target is DATE Datatype */
					       IF @StrTrgDType = 'T'
					       BEGIN
					       		SET @StrText =   CASE @StrSrcDType
-- Cycle3 .o					                	        WHEN 'T'  THEN 'utc('+ @StrText + '0,2)' --Source is Date
										WHEN 'T'  THEN 'utc('+ @StrText + ',0,2)' --Source is Date -- Cycle3 .n
                        							WHEN 'K'  THEN 'date.utc(' + @StrText + ')' --source is UTC
-- Cycle3 .o			                          			ELSE 'utc(0,0,2)'
										ELSE 'empty()' -- Cycle3 .n
					                         	 END
					       END

						/* LND-9338.sn */
						/* 8. Target is MULTI BYTE DataType */
						IF @StrTrgDType = 'M' 
						BEGIN
							/* Check for the  ALignment done in XML generation 
							   Check for the Case Sensitive done is XML Generation */

							SET @StrText = CASE @StrSrcDType
								   WHEN 'S' THEN
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN	/* Source smaller or equal than Target */
											@StrText
									   ELSE		/* String does not Fit -> Action */
											''
									   END
								   WHEN 'M' THEN
									   CASE
									   WHEN @IntSrcDLen <= @IntTrgDLen THEN	/* Source smaller or equal than Target */
											@StrText
									   ELSE		/* String does not Fit -> Action */
											''
									   END


									ELSE	/* DD Change Action required make field rule empty
											   for detection with SP Empty Mapping rule present */

										''
									END
						END
						/* LND-9338.en */
						
						/* LND-9338.so
					       /* 8. Target is MULTI BYTE DataType */

					       /*IF @StrTrgDType = 'M'
					       BEGIN*/
					       		/* Handled in XML Generation */
					       /*END*/
					      /* LND-9338.eo */


			                       /* Check for TARGET Mandatory and SOURCE NOT Mandatory */
			                       IF @StrTrgMan = 'M' and @StrSrcMan ='N'
					       BEGIN

			      				SET @StrVarName = 'var.' + @I_StrTrgPackage + @I_StrTrgModule + @I_StrTrgTable +  '.'  + @StrField

				                	SET @StrTemp = CASE @StrTrgDType
								            WHEN 'B' THEN '0'
								            WHEN 'I' THEN '0'
								            WHEN 'L' THEN '0'
								            WHEN 'D' THEN '0'
								            WHEN 'F' THEN '0'
								            WHEN 'S' THEN  'bcrun.convdll.string("",' + LTRIM(STR(@IntTrgDLen)) + ')'    -- LND2-9769.c
								            WHEN 'E' THEN  '0'
								            WHEN 'T' THEN  'utc(0,0,2)'
								            WHEN 'U' THEN  '0'
								            WHEN 'M' THEN  'bcrun.convdll.stringmb("",' + LTRIM(STR(@IntTrgDLen)) + ')'  -- LND2-9769.c
								            WHEN 'K' THEN  'utc(0,0)'
									    ELSE '0'
								       END

					           	/* WRITE THE TEXT FOR THE FIELD OF TYPE 2
							 if isspace(Field) then
						            	Var.FieldName = Default Value
					              	endif */

				                	SET @StrText = '|* The Target field is a Mandatory Field but the Source is' + char(13) + char(10) +
				            			       '|* Not a Mandatory Field. So  Default Value is Filled here.' + char(13) + char(10) +
				            			       char(9) + 'if isspace(' +  @StrText + ') then' + char(13)  + char(10) +
					           		       char(9) + char(9) + @StrVarName + '=' + @StrTemp + char(13)  + char(10) +
					           		       char(9) + 'endif'

					         	SET @IntFieldDumpType = 1
					        END
					        ELSE
					        BEGIN
						         SET @StrVarName = ''
						         SET @StrTemp = ''
						         SET @IntFieldDumpType=0
					        END
		                     END  --IF @StrCType = 'U' OR @StrCType = 'C'  END

			   END  -- ELSE of @IntTrgDepth > 0 END
/* 
   SYNCHRONIZE WITH [sp_CreateMappingForChangedTables]             END
   -------------------------------------------------------------------
*/

			   --Insert the Field values into BCMAP140

			EXEC	@IntRetVal = sp_insert_bcmap140_1 	@I_IntMprf,             --Migration Profile
									@I_StrVer,              --Version
									@I_StrRel,              --Release
									@I_StrCust,             --Customer
									@I_StrTrgPackage,       --Package
									@I_StrTrgModule,        --Module
								  	@I_StrTrgTable,         --Table
								  	1,                      --Dump Number
								  	@StrField,              --Field Name
								  	@StrText,               --Field Value - Text
								  	@IntFieldDumpType,      --Type of the Value Type 0/1
								  	'N'                     -- N- Not Approved  Rule not Approved
			IF  @IntRetVal <> 0
			BEGIN
				-- Error So BREAK
				SELECT @StrTemp = [master].[dbo].[sysmessages].description
				FROM   [master].[dbo].[sysmessages]
				WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

				SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
						 ' MPRF - '   + STR(@I_IntMprf)		+  ' ' +
					         ' VRC  - '   + @I_StrVer		+  '-' +
				                                @I_StrRel		+  '-' +
				                                @I_StrCust		+
						 ' TABLE - '  +	@I_StrTrgPackage	+
				                                @I_StrTrgModule		+
				                                @I_StrTrgTable		+  ' ' +
				                 ' FIELD - '  + @StrField  		+
				                 ' into BCMAP140. '

				EXEC Write_to_file @StrTemp, @I_StrLogFileName

				CLOSE DDC150_Cursor
				DEALLOCATE DDC150_Cursor

				RETURN @IntRetVal
			END

			IF @I_StrSCM = 'Y'
			BEGIN
				EXEC	@IntRetVal = sp_insert_bcmap145_1 	@I_IntMprf,            --Migration Profile
										@I_StrVer,             --Version
										@I_StrRel,             --Release
										@I_StrCust,            --Customer
										@I_StrTrgPackage,      --Package
										@I_StrTrgModule,       --Module
									     	@I_StrTrgTable,        --Table
									     	1,                     --Dump Number
									     	@StrField,             --Field Name
									     	1,                     --Revision Info
									     	@StrText,              --Field Value - Text
									     	@IntFieldDumpType,     --Tupe of the Value Type 0/1
									     	@I_UserName,           --User Name
									     	'N',                   --N- Not Approved  Rule not Approved
										'C'		       --Status of the Component C- New/Changed


				IF  @IntRetVal <> 0
				BEGIN
					SELECT @StrTemp = [master].[dbo].[sysmessages].description
					FROM   [master].[dbo].[sysmessages]
					WHERE  [master].[dbo].[sysmessages].error = @IntRetVal

					SET @StrTemp = ' Error -' + @StrTemp + ' -  while inserting  the record -' +
							 ' MPRF - '   + STR(@I_IntMprf)		+  ' ' +
						         ' VRC  - '   + @I_StrVer		+  '-' +
					                                @I_StrRel		+  '-' +
					                                @I_StrCust		+
							 ' TABLE - '  +	@I_StrTrgPackage	+
					                                @I_StrTrgModule		+
					                                @I_StrTrgTable		+  ' ' +
					                 ' FIELD - '  + @StrField  		+
					                 ' into BCMAP145. '

					EXEC Write_to_file @StrTemp, @I_StrLogFileName

					CLOSE DDC150_Cursor
					DEALLOCATE DDC150_Cursor

					RETURN @IntRetVal
				END
			END

			SET @IntTrgDepth = @IntTrgDepth - 1
			IF @IntTrgDepth = 0 BREAK  		-- This is to Avoid one more looping when @IntTrgDepth > 0
		END 	--WHILE END


		FETCH NEXT FROM DDC150_Cursor
		INTO   	@StrField, @StrSrcDomain, @StrTrgDomain, @IntSrcDepth,@IntTrgDepth,@Strctype,@StrSrcMan, @StrTrgMan,@StrTrgInitialValue
	END -- WHILE  @@FETCH_STATUS = 0

	CLOSE DDC150_Cursor
	DEALLOCATE DDC150_Cursor

	RETURN @IntRetVal



GO

/* LND-9769.sn */
IF NOT EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bcmap102' AND column_name = 'rwvr' )
BEGIN
  ALTER TABLE bcmap102 ADD [rwvr] [bit] NULL
END

IF NOT EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bcmap102' AND column_name = 'rwvn')
BEGIN
  ALTER TABLE bcmap102 ADD [rwvn] [int] NULL
END
GO
/* LND-9769.en */

/* LND2-16081.sn */
IF NOT EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bcusr100' AND column_name = 'admr')
BEGIN
  ALTER TABLE bcusr100 ADD [admr] [bit] NULL
END
/* LND2-16081.sn */

/* DCA3.4_013.sn */
IF NOT EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bcddi140' AND column_name = 'dsca')
BEGIN
  ALTER TABLE bcddi140 ADD [dsca] [varchar] (70) COLLATE Latin1_General_BIN NULL
END
ELSE
BEGIN
  ALTER TABLE bcddi140 ALTER COLUMN [dsca] varchar(70)
END
/* DCA3.4_013.en */

/* DCA3.4_013.sn */
IF NOT EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bcddi150' AND column_name = 'dsca')
BEGIN
  ALTER TABLE bcddi150 ADD [dsca] [varchar] (150) COLLATE Latin1_General_BIN NULL
END
/* DCA3.4_013.en */

/* LND2-27370.sn */
IF NOT EXISTS (select * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bcusr120' AND column_name = 'usrn')
BEGIN
  CREATE TABLE [dbo].[bcusr120] (
	[usrn] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[logi] [datetime] NULL ,
	[logo] [datetime] NULL ,
	CONSTRAINT [PK_bcusr120] PRIMARY KEY (usrn)
)
END
/* LND2-27370.en */


GO
