IBANK - PROJECT

??--ALTER  PROCEDURE [dbo].[usp_CalAppFTV] 

DECLARE @Applno NVARCHAR(20)				

	SET NOCOUNT ON;

	--declare @Applno NVARCHAR(20)	

	--set @Applno  ='AP00008-200917-00087'

	declare @TmpApplno nvarchar(50)

	declare @TmpTableName nvarchar(100)

	declare @SQLTxt01	varchar(max)

	declare @SQLTxt02	varchar(max)

	declare @SQLTxt03   varchar(max)

	declare @movmntID	uniqueidentifier 

	declare @marketvalues decimal(18,2)

	declare @smktValue decimal(18,2)

	declare @grpCollaLessAmt decimal(18,2)

	declare @valueCollateral  decimal(18,2)

	declare @valueAPP162 decimal(18,2)

	declare @AppState int

	declare @checkStatus int

	declare @Exposure  decimal(18,2)

	declare @pgmCde nvarchar(20)

	declare @checkauthTyp int

	set @checkStatus=0

	set @valueAPP162=0

	set @marketvalues=0

	set @smktValue =0

	set @valueCollateral=0

	set @movmntID = (select movmntID from cbmsapp010 where applno = @Applno and recsts='A')

	set @AppState=(select appstate from cbmsapp010 where applno = @Applno and recsts='A')

	set @Exposure =(select exposure from CBMSAPP010 where applno = @Applno and recsts='A')

	set @pgmCde =(select pgmCde from CBTBMKT040 where pgmNam like '%top up%' 

                     and pgmCde in (select  pgmCde from CBMSAPP010 where applNo=@Applno and recsts='A' ))

	print @pgmCde

	IF(@AppState>40)

	BEGIN

		select @checkStatus=COUNT(*)	from CBMSAPP162 where movmntID = @movmntID and recSts='A'  and procTyp = 0   --D!H3*H

		-- #22#0@!4 2
H2"#1#-#22

		select @valueAPP162=authValue	from CBMSAPP162 where movmntID = @movmntID and recSts='A'   and authSys=1 and apists=3 --H2"#1#-!2A%I'

		select @checkauthTyp = (SELECT authTyp FROM CBMSAPP162 WHERE movmntID = @movmntID and recSts='A')

	---- 
#5!5+%1
#0
1!2

'H2 1 #2"
2# 0@-2 #22#0@!4 2 +%1
#0
1 !2#'!
1 A%I' Update APP162

	--===================UPDATE BY Lannicha Kaensusadee 2020-08-21=================== 

			--#22#0@!4*   5H tab +%1
#0
1

			SELECT @valueCollateral	=	SUM(app.smktValue) 

			FROM(	SELECT ISNULL(PP150.smktValue,0) as smktValue  --5H4#I-!*4H%9
*4H%9
*#I2

					FROM CBMSAPP212 PP212 

					left join CBMSAPP150  PP150  ON PP212.collaDetailId =PP150.collaDetailId and PP150.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

					UNION

					SELECT ISNULL(PP151.smktValue,0)  as smktValue --5H4

					FROM CBMSAPP212 PP212 		

					left join Cbmsapp151  PP151  ON PP212.collaDetailId =PP151.collaDetailId and PP151.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

					UNION

					--updated by was

					SELECT ISNULL(PP154.smktValue,0)  as smktValue ---B

					FROM CBMSAPP212 PP212 		

					left join CBMSAPP154 PP154  ON PP212.collaDetailId =PP154.collaDetailId and PP154.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

					--end updated by was

			) AS app

			--#227I-2"* 5H tab +%1
#0
1

							--CBMSAPP150 5H4#I-!*4H%9
*#I2

			SELECT @marketvalues	=	SUM(AppMarketvalues.marketvalues) 

			FROM(	SELECT ISNULL(PP150.marketvalues,0) as marketvalues

					FROM CBMSAPP212 PP212 

					left join CBMSAPP150  PP150  ON PP212.collaDetailId =PP150.collaDetailId and PP150.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

					UNION  --CBMSAPP151 5H4

					SELECT ISNULL(PP151.marketvalues,0)  as marketvalues

					FROM CBMSAPP212 PP212 		

					left join Cbmsapp151  PP151  ON PP212.collaDetailId =PP151.collaDetailId and PP151.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

					UNION   --CBMSAPP154 -B

					SELECT ISNULL(PP154.mktValue,0)  as marketvalues

					FROM CBMSAPP212 PP212 		

					left join CBMSAPP154  PP154  ON PP212.collaDetailId =PP154.collaDetailId and PP154.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

					UNION   --CBMSAPP154 *4H%9
*#I2

					SELECT ISNULL(PP152.mktValue,0)  as marketvalues

					FROM CBMSAPP212 PP212 		

					left join CBMSAPP152  PP152  ON PP212.collaDetailId =PP152.collaDetailId and PP152.recSts='A'

					WHERE  PP212.movmntID=@movmntID

					and PP212.recSts='A'

			) AS AppMarketvalues

			IF(@valueCollateral>0) --#22#0@!4 2
+%1
#0
1!5H2!2

'H2 0 0-1@ 22##1#-#22

			BEGIN

					UPDATE  CBMSAPP162 SET authValue=@valueCollateral    --authValue 22##1#-#22

					WHERE  movmntID=@movmntID and recSts='A'

					SET @smktValue= @valueCollateral

			END

			IF(@valueAPP162>0)  -- 
#5#1#-#22!2A%I'

			BEGIN

								---@valueAPP162 22##1#-#22

							    ---@marketvalues #227I-2"

					--select 'case 1',@marketvalues,@valueAPP162

					SELECT  @grpCollaLessAmt=case when @marketvalues >  @valueAPP162   then @valueAPP162 else @marketvalues end 

					-- update #22#0@!4 I'"#225H22##1#-!2

					update PP150 set smktValue = @valueAPP162

					FROM CBMSAPP150 PP150 where collaDetailId in (select collaDetailId from CBMSAPP212 where movmntID = @movmntID)				

					update PP151 set smktValue = @valueAPP162

					FROM CBMSAPP151 PP151 where collaDetailId in (select collaDetailId from CBMSAPP212 where movmntID = @movmntID)

					update PP154 set smktValue = @valueAPP162

					FROM CBMSAPP154 PP154 where collaDetailId in (select collaDetailId from CBMSAPP212 where movmntID = @movmntID)

			END

			ELSE

			BEGIN  --
#5"1D!H#1#-#22

				   --select 'case 2' ,@marketvalues	,@smktValue

								---@smktValue #22#0@!4

								---@marketvalues #227I-2"

					SELECT  @grpCollaLessAmt=case when @marketvalues >  @smktValue   then @smktValue else @marketvalues end 

			END