quote:

---

Originally posted by hspatil31

Hi Ahmed,

Table structure means you want all table scripts which tables i am using in that procedure or you want my procedure ?

Thanks

---

quote:

---

Originally posted by hspatil31

Hi,

I am using following queary,

...
		WHILE (@RDTblVarIndex <= @RDTblVarRecordCnt)

		BEGIN
			SELECT @NP=NP, @NX=NX, @T_G=Th_G, @Norm_C_T_ID=Nor_C_Ty_ID FROM @RDTblVar WHERE ID = @RDTblVarIndex

			BEGIN TRANSACTION MAINTRANSACTION;
				----Initialize all variables
			...
			COMMIT TRANSACTION MAINTRANSACTION;

			SELECT @RDTblVarIndex = @RDTblVarIndex + 1 

		END --End of while for @RDTblVar

		--Clear the table
		DELETE FROM @RDTblVar
	
		
		--Find Name of the Cutomer & Rate deck
		SELECT @RDName=Name,@CustomerName=customer_name
		FROM tb_lcr_ra_d RD (nolock)
		INNER JOIN tb_lcr_r_d_det RDD (nolock) on RDD.rate_deck_id=RD.ID
		INNER JOIN tb_lcr_customer C (nolock) ON RD.customer_id=C.ID
		WHERE RDD.ID=@R_D_D_ID

END

Thnks,

---

USE [SBCSS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[pr_lcr_tbv_CRD_N]
	@R_D_D_ID VARCHAR(36)
AS
BEGIN
SET IMPLICIT_TRANSACTIONS OFF

DECLARE @Inter				VARCHAR(36),
		@Norm_C_T_ID				VARCHAR(36),
		@R_D_F_ID			VARCHAR(36),
		@N_R_S_ID		VARCHAR(36),
		@NP							CHAR(3),
		@NX							CHAR(3),		
		@T_G				VARCHAR(10),
		@D_D					VARCHAR(10),
		@C_C					VARCHAR(10),
		@R							REAL,
		@R_R						REAL,
		@G_R 					REAL,
		@N_R 						REAL,
		@Mar 						REAL,
		@Ov 						REAL,		
		@Ov_E 					REAL,
		@Mar_E						REAL,
		@LCR_1 						REAL,
		@LCR_2 						REAL,
		@LCR_3 						REAL,
		@R_C 						INT,
		@R_D_C_R_S_ID	VARCHAR(36),
		@C_Com 				REAL,		
		@C_Com_1				REAL,
		@C_Com_2				REAL,		
		@C_Com_3				REAL,
		@S_1						REAL,
		@S_2_v					REAL,
		@S_2						REAL,
		@S_3						REAL,
		@R_D_C_R_ID		VARCHAR(36),
		@Pri						INT,
		@Pr_W_ID				VARCHAR(36),
		@W_s_ID					VARCHAR(36),
		@msgqudesc						VARCHAR(MAX),
		@RDName					VARCHAR(40),
		@CustomerName					VARCHAR(40),
		@intra					VARCHAR(36),
		@inter					VARCHAR(36),
		@C_Ty_ID					VARCHAR(36),
		@defaultintr			REAL,
		@defaultinter			REAL,
		@RDTblVarIndex			BIGINT,
		@RDTblVarRecordCnt		BIGINT,
		@LCCTblVarIndex	BIGINT,
		@LCCTblVarRecordCnt	BIGINT

		SET NOCOUNT ON 

		SET @Ov=0;
		SET @Mar=0;

		SET @msgqudesc=NULL;
		SET @RDName =NULL;
		SET @CustomerName =NULL;

		
		IF (@R_D_D_ID IS NULL) 
		BEGIN
		
		RETURN -1
		END			

		
		SELECT	@intra=ID
		FROM	tb_lcr_call_type (nolock)
		WHERE	upper(Name)='ABC';

		--Why do you have @Inter defined twice??  SQL variable names are not case sensitive...
		SELECT	@inter=ID
		FROM	tb_lcr_call_type (nolock)
		WHERE	upper(Name)='XYZ';

		--This is #2
		SELECT	@Inter=ID
		FROM	tb_lcr_call_type  (nolock)
		WHERE	LTRIM(RTRIM(UPPER([Name])))='LMN'

		
		SELECT	@defaultinter=default_rate
		FROM	tb_lcr_r_d_fin (nolock)
		WHERE	R_D_D_ID = @R_D_D_ID
		AND		C_Ty_Id=@inter;

		SELECT	@defaultintr=default_rate
		FROM	tb_lcr_r_d_fin (nolock)
		WHERE	R_D_D_ID = @R_D_D_ID
		AND		C_Ty_Id=@intra;
	
		IF (@defaultinter IS NULL)
		BEGIN
			SET @defaultinter=0.05;
		END
		IF (@defaultintr IS NULL)
		BEGIN
			SET @defaultinter=0.05;
		END
	
		PRINT 'DEFAULT AB=' + convert(varchar,@defaultinter) + ' XY=' + convert(varchar,@defaultintr)
			
		---------------------------------------
		--Find LCC for Domestic Traffic type
		--------------------------------------
		
		DECLARE @RDTblVar TABLE
		(
			 ID INT IDENTITY(1,1) PRIMARY KEY
			,NP CHAR(3)
			,NX CHAR(3)
			,Th_G CHAR(10)--Null is entered here, from below?
			,Nor_C_Ty_ID VARCHAR(36)
		)

		INSERT INTO @RDTblVar(NP, NX, Th_G, Nor_C_Ty_ID) 
		SELECT DISTINCT NP, NX, NULL AS Th_G, Nor_C_Ty_ID
			FROM 
				(SELECT NP, NX, Th_G, NRS.C_Ty_Id AS Nor_C_Ty_ID
					FROM tb_lcr_car C (nolock)
						INNER JOIN tb_lcr_r_sh RS (nolock) ON C.ID=RS.Carrier_ID
						INNER JOIN tb_lcr_wo_sh WS (nolock) ON R_Sh_ID=RS.ID
						INNER JOIN tb_lcr_nor_ra_sh NRS (nolock) ON NRS.Wo_She_ID=WS.ID 
						AND NRS.C_Ty_Id <> @Inter 
						AND NRS.Status=1 
					WHERE RS.ID IN 
						(SELECT R_Sh_ID 
							FROM tb_lcr_r_d_det RD (nolock)
								INNER JOIN tb_lcr_r_d_c_r_sh RDRSH (nolock) ON RDRSH.R_D_D_ID=RD.ID
							WHERE RD.ID=@R_D_D_ID)
				) As Result	
		ORDER BY NP, NX

		SELECT @RDTblVarIndex = 1

		SELECT @RDTblVarRecordCnt = COUNT(ID) FROM @RDTblVar

		WHILE (@RDTblVarIndex <= @RDTblVarRecordCnt)

		BEGIN
				SELECT @NP=NP, @NX=NX, @T_G=Th_G, @Norm_C_T_ID=Nor_C_Ty_ID FROM @RDTblVar WHERE ID = @RDTblVarIndex

				BEGIN TRANSACTION MAINTRANSACTION;
				----Initialize all variables
				SET @R_C=0;
				SET @R_R=NULL;
				SET @G_R=NULL;
				SET @N_R=NULL;
			
				SET @C_Com_1=NULL;
				SET @C_Com_2=NULL;
				SET @C_Com_3=NULL;
			
				SET @S_1=NULL;
				SET @S_2=NULL;
				SET @S_2_v=NULL;
				SET @S_3=NULL;

				SET @LCR_1=NULL;
				SET @LCR_2=NULL;
				SET @LCR_3=NULL;
			
				SET @Pr_W_ID=NULL;
				
				SET @R_D_C_R_ID=NULL;
			
				SET @Ov=0;
				SET @Ov_E=0;
				SET @Mar=0;
				SET @Mar_E=0;

				--Get finanacial ID, overhead & margin from tb_lcr_r_d_fin for Domestic
				SELECT Top 1 @R_D_F_ID=ID,@Ov=Overhead,@Mar=Margin 
				FROM tb_lcr_r_d_fin RDF (nolock) 
				WHERE R_D_D_ID=@R_D_D_ID and C_Ty_Id<>@Inter

				--Insert record in tb_lcr_Rate_Deck_Created_Rate
				--Do you have to use a procedure to insert a record???
				EXEC pr_lcr_SaveRD_C_R	1,
													NULL,
													@R_D_D_ID,
													@Norm_C_T_ID,
													@NP,
													@NX,
													@T_G,
													NULL,
													NULL,
													@R_R,
													@G_R,
													@N_R,
													@R_D_C_R_ID OUTPUT

				DECLARE @LCCTblVar TABLE
				(
					 ID INT IDENTITY(1,1) PRIMARY KEY
					,Nor_R_Sh_ID VARCHAR(36)
					,Wo_She_ID VARCHAR(36)
					,C_Comp REAL
					,Rate REAL
					,C_Ty_Id VARCHAR(36)
				)

				INSERT INTO @LCCTblVar(Nor_R_Sh_ID, Wo_She_ID, 
							C_Comp, Rate, C_Ty_Id) 
				SELECT  NRS.ID AS Nor_R_Sh_ID,
						WS.ID as Wo_She_ID,
						C_Comp, 
						NRS.Rate,
						NRS.C_Ty_Id 
					FROM tb_lcr_nor_ra_sh NRS (nolock)
						INNER JOIN tb_lcr_wo_sh WS (nolock) ON NRS.Wo_She_ID=WS.ID
						INNER JOIN tb_lcr_r_sh RS (nolock) ON RS.ID=WS.R_Sh_ID	
						INNER JOIN tb_lcr_car C (nolock) ON C.ID=RS.Carrier_ID
					WHERE 
						NP=@NP 
						AND NX=@NX 
						AND NRS.C_Ty_Id=@Norm_C_T_ID 
						AND NRS.Status=1
						AND RS.ID IN 
							(SELECT R_Sh_ID
								FROM tb_lcr_r_d_det RD (nolock) 
									INNER JOIN tb_lcr_r_d_c_r_sh RDRSH (nolock) 
									ON RDRSH.R_D_D_ID=RD.ID				
								WHERE RD.ID=@R_D_D_ID
							)
						AND NRS.Rate = (SELECT MAX(RATE)
											FROM tb_lcr_nor_ra_sh (nolock) 
											WHERE Wo_She_ID IN (WS.ID) --WS.ID ?
												AND NP=@NP 
												AND NX=@NX 
												AND C_Ty_Id=@Norm_C_T_ID 
												AND Status=1
										) 
						ORDER BY NRS.Rate, C.priority, NRS.Wo_She_ID, NRS.C_Ty_Id

				PRINT 'NP=' + @NP + ' NX=' + @NX + ' CT=' + @Norm_C_T_ID;

				SELECT @LCCTblVarIndex = 1

				SELECT @LCCTblVarRecordCnt = COUNT(ID) FROM @LCCTblVar

				--HOLY CRAP... There's 2!
				WHILE (@LCCTblVarIndex <= @LCCTblVarRecordCnt)

				BEGIN

					SELECT @N_R_S_ID=Nor_R_Sh_ID, @W_s_ID=@W_s_ID, 
						   @C_Com=C_Comp, @R=Rate, @C_Ty_ID=C_Ty_Id FROM @LCCTblVar WHERE ID = @LCCTblVarIndex

					-- If Rate is 0, replace it with default rate
					
					IF (@R <= 0)
					BEGIN
						IF (@C_Ty_ID = @inter)
						BEGIN
							SET @R = @defaultinter
						END
						IF (@C_Ty_ID = @intra)
						BEGIN
							SET @R = @defaultintr
						END
					END

					IF (@W_s_ID=@Pr_W_ID)
						BEGIN
							--Nothing to do
							PRINT 'Ignoring same worksheetid.....';
						END
					ELSE 
						BEGIN					
							--chk following condition for getting only 3 LCC						
							IF @R_C <= 3
							BEGIN
								SET @R_C=@R_C+1;
								--Insert data to table dbo.tb_lcr_rate_deck_least_cost_carrier							
								EXEC pr_lcr_SaveLCC 1,NULL,@R_D_C_R_ID,@N_R_S_ID,@R_C

								--Get call completions & rate for each carrier rate sheet
								IF @R_C=1
								BEGIN
									SET @C_Com_1=@C_Com;
									SET @LCR_1=@R;
								END
								IF @R_C=2
								BEGIN
									SET @C_Com_2=@C_Com;
									SET @LCR_2=@R;
								END
								IF @R_C=3
								BEGIN
									SET @C_Com_3=@C_Com;
									SET @LCR_3=@R;
								END	
							END
						END
					SET @Pr_W_ID=@W_s_ID;

					SELECT @LCCTblVarIndex = @LCCTblVarIndex + 1

				END --End of while for @LCCTblVar

				--Clear the table
				DELETE FROM @LCCTblVar

				--------------------------------------------------
				--Apply Exceptions
				--------------------------------------------------
				SELECT @Ov_E=ISNULL(Overhead,0),@Mar_E=ISNULL(Margin,0)
				FROM tb_lcr_rate_deck_exception (nolock) 
				WHERE Rate_Deck_Financials_ID IN 
						(SELECT ID
						  FROM tb_lcr_r_d_fin RDF (nolock) 
						  WHERE R_D_D_ID=@R_D_D_ID 
							AND C_Ty_Id<>@Inter)
				AND (@NP BETWEEN Start And [END]
					OR @LCR_1 BETWEEN CONVERT(REAL,START) AND CONVERT(REAL,[End]))
				
				IF(@Ov_E<>0)
				BEGIN 
					SET @Ov=@Ov_E;
				END
				IF(@Mar_E<>0)
				BEGIN	
					SET @Mar=@Mar_E;
				END

				--Calculate spread values from call completion
				SET @S_1=ISNULL(@C_Com_1,0);
				
				PRINT 'Row Count=' + convert(varchar(10),@R_C);
				
				--IF 3 LCC are present
				IF(@R_C=3)
				BEGIN
					SET @S_2_v=((100-@S_1) * ISNULL(@C_Com_2,0));
					SET @S_2=(@S_2_v/100);
					SET @S_3=((100-@S_1-@S_2));
				END
				ELSE IF(@R_C=2) -- IF 2 LCC are present
				BEGIN
					SET @S_2=(100-@S_1);
					SET @S_3=0; -- Only 2 least cost carriers are there so for third spread & all values should be 0 or null.
				END
				ELSE --IF only one LCC is there
				BEGIN	
					SET @S_1=100;				
					SET @S_2=0;
					SET @S_3=0;
				END

				--Calculate Raw rate
				--SET @R_R=(ISNULL(@LCR_1,0) * ISNULL(@S_1,0))+(ISNULL(@LCR_2,0) * ISNULL(@S_2,0))+(ISNULL(@LCR_3,0) * ISNULL(@S_3,0))

				SET @R_R=(ISNULL(@LCR_1,0) * ISNULL(@S_1,0)/100)+(ISNULL(@LCR_2,0) * ISNULL(@S_2,0)/100)+(ISNULL(@LCR_3,0) * ISNULL(@S_3,0)/100)

				--Calculate Gross Rate by applying overhead
				IF(@Ov >= 1) --IF overhead in percentage
				BEGIN
					SET @G_R=(@R_R * (1+(ISNULL(@Ov,0)/100)));
				END
				ELSE IF(@Ov < 1) --IF overhead is absolute value
				BEGIN
					SET @G_R=(@R_R + ISNULL(@Ov,0));
				END
				--Calculate NET Rate by applying Margin			
				IF(@Mar >= 1) --IF Margin in percentage
				BEGIN
					SET @N_R=(@G_R/(1-(ISNULL(@Mar,0)/100)));
				END
				ELSE IF(@Mar < 1) --IF Margin is absolute value
				BEGIN
					SET @N_R=(@G_R + ISNULL(@Mar,0));
				END
				
				--Update data to tb_lcr_rate_deck_created_rate from above inserted --Insert record in Rate_Deck_Created_Rate_ID
				EXEC pr_lcr_SaveRD_C_R 2,@R_D_C_R_ID,@R_D_D_ID,@Norm_C_T_ID,@NP,@NX,@T_G,NULL,NULL,@R_R,@G_R,@N_R,NULL

			COMMIT TRANSACTION MAINTRANSACTION;

			SELECT @RDTblVarIndex = @RDTblVarIndex + 1 

		END --End of while for @RDTblVar

		--Clear the table
		DELETE FROM @RDTblVar
	
		
		--Find Name of the Cutomer & Rate deck
		SELECT @RDName=Name,@CustomerName=customer_name
		FROM tb_lcr_ra_d RD (nolock)
		INNER JOIN tb_lcr_r_d_det RDD (nolock) on RDD.rate_deck_id=RD.ID
		INNER JOIN tb_lcr_customer C (nolock) ON RD.customer_id=C.ID
		WHERE RDD.ID=@R_D_D_ID

END