MOBILE_API/DB/SPs/EXCHNAGE_PROC_SAVEBRANCH_SELLV2.sql

USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[EXCHNAGE_PROC_SAVEBRANCH_SELLV2]    Script Date: 7/4/2019 11:35:48 AM ******/
DROP PROCEDURE [dbo].[EXCHNAGE_PROC_SAVEBRANCH_SELLV2]
GO
/****** Object:  StoredProcedure [dbo].[EXCHNAGE_PROC_SAVEBRANCH_SELLV2]    Script Date: 7/4/2019 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procEDURE [dbo].[EXCHNAGE_PROC_SAVEBRANCH_SELLV2]
	@flag			CHAR(1),
	@sessionId		VARCHAR(50),
	@operation		CHAR(1)				= NULL ,
	@remarks		VARCHAR(500)		= NULL,
	@customer		VARCHAR(200)		= NULL,
	@idType			INT					= NULL,
	@idNumber		VARCHAR(50)			= NULL,
	@country		INT					= NULL,
	@issuedate		DATETIME			= NULL,
	@expdate		DATETIME			= NULL,
	@address		VARCHAR(150)		= NULL,
	@occupation		INT					= NULL,
	@EMPNAME		VARCHAR(100)		= NULL,
	@moneysource	INT					= NULL,
	@purpose		INT					= NULL,
	@benefName		VARCHAR(100)		= NULL,
	@beneidtype		INT					= NULL,
	@beneidnum		VARCHAR(50)			= NULL,
	@contact		varchar(20)			= NULL,
	@Nationality	varchar(50)			= NULL,
	@issuefrom		varchar(50)			= NULL,
	@user			VARCHAR(50),
	@branchID		VARCHAR(30),
	@tran_date		DATETIME			= null,
	@ofacchk		CHAR(1)				= NULL,
	@isverify		CHAR(1)				= NULL,
	@party			INT					= NULL,
	@cntdob			VARCHAR(20)			= NULL,
	
	@filterCust		INT					= NULL,
	@type			CHAR(1)				= NULL,
	@CustacNum		VARCHAR(30)			= NULL,
	@isOfac			CHAR(1)	,
	@OFACREMARKS	VARCHAR(MAX)		= NULL,
	@isNewCustomer  CHAR(1)				= NULL,
	@businessType   VARCHAR(100)		= NULL,
	@town			VARCHAR(100)		= NULL,
	@custState		VARCHAR(100)		= NULL,
	@postalCode		VARCHAR(50)			= NULL
	
	,@oName			VARCHAR(100)		= NULL
	,@oAddress		VARCHAR(100)		= NULL
	,@oTown			VARCHAR(100)		= NULL
	,@oState		VARCHAR(100)		= NULL
	,@oPostalCode   VARCHAR(50)			= NULL
	,@oCountry	    VARCHAR(50)			= NULL
	,@oNationality  VARCHAR(50)			= NULL
	,@oidType		VARCHAR(50)			= NULL
	,@oidNo			VARCHAR(50)			= NULL
	,@oContact		VARCHAR(50)			= NULL
	,@oDob			VARCHAR(20)			= NULL	
	,@oOccupation   VARCHAR(100)		= NULL
	,@oBussType		VARCHAR(100)		= NULL
	,@ofacId		VARCHAR(100)		= NULL
	,@saveOFACTxn	CHAR(1)				= NULL
	,@strReason		VARCHAR(100)		= NULL
	,@strRemark		VARCHAR(500)		= NULL
	,@strMark		CHAR(1)				= NULL

AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
DECLARE @currencyName VARCHAR(20) = 'KRW'
IF @flag = 'i'
BEGIN
DECLARE @LOG INT
DECLARE @AVAILABLEBAL MONEY
DECLARE @ACCTNUM VARCHAR(50)
DECLARE @totalDR NUMERIC(20,2)
DECLARE @totalCR NUMERIC(20,2)
DECLARE @Part_Id INT
DECLARE @totalRows INT
DECLARE @TotalAmt NUMERIC(20,2)
DECLARE @trntype VARCHAR(2)
DECLARE @ref_num VARCHAR(20)
declare @mas varchar(500)
DECLARE @total_lcy MONEY
DECLARE @currLimitId INT,@rateCode INT


	SELECT @tran_date = CAST(GETDATE() AS DATE)
	IF NOT EXISTS(SELECT 'A' FROM FastMoneyPro_Account.dbo.TEMP_TRANSACTION(NOLOCK) WHERE sessionId = @sessionId AND tran_type = 'S' AND BRANCH= @branchID)
	BEGIN
		SELECT 1 code,'No transaction found for operation..' AS remarks
		RETURN;
	END

	IF @Nationality = 'Select Country'
		SET @Nationality = NULL
	IF @businessType = 'Select Business Type'
		SET @businessType = NULL
	
	IF @oCountry = 'Select Country'
		SET @oCountry = NULL
	IF @oNationality = 'Select Country'
		SET @oNationality = NULL
	IF @oidType ='Select ID Type'
		SET @oidType =NULL
	IF @oOccupation ='Select Occupation'
		SET @oOccupation= NULL
	IF @oBussType ='Select Business Type'
		SET @oBussType = NULL

IF @type IS NULL
	SET @type ='N'

select @CustacNum = a.acct_num
	from EXCHANGE_CUSTOMERSETUP  c(NOLOCK)
	INNER join FastMoneyPro_Account.dbo.ac_master(nolock) a ON c.cid = a.acct_type_code and a.acct_rpt_code ='MoneyEx'
	where c.cid = @filterCust

	IF @type  = 'C'
	BEGIN
		IF @filterCust IS NULL
		BEGIN
			SELECT 1 as code,'Registered customer not found,can not buy on credit' AS remarks
			RETURN;
		END
		IF @CustacNum IS NULL
		BEGIN
			SELECT 1 as code,'Customer Ac not created,can not buy on credit' AS remarks
			RETURN;
		END
	END

IF @filterCust IS NULL
SET @filterCust = 0	
IF @branchID IS NULL 
BEGIN
	SELECT 1 as code,'Something wend wrong,refresh and try again!' AS remarks
	RETURN;
END

IF EXISTS(SELECT 'A' FROM staticDataValue WITH(NOLOCK) WHERE typeID = @idType ) --AND ref_code='IC' )
BEGIN
	IF @idNumber IS NULL
	BEGIN
		SELECT 1 as code,'Id number is missing' AS remarks
		RETURN;
	END
	--IF (SELECT dbo.EXCHANGE_FNAGetDobFromNRICNo(@idNumber)) IS NULL
	--BEGIN	
	--	SELECT 1 as code,'Enter corect Id Number' AS remarks
	--	RETURN;
	--END
	--SET @cntdob =  (SELECT dbo.EXCHANGE_FNAGetDobFromNRICNo(@idNumber))
END

IF LTRIM(ISNULL(@customer,'')) <> ''
BEGIN
	IF @party IS NULL
	BEGIN
		SELECT 1 as code,'Customer Type is missing.' AS remarks
		RETURN;
	END
	IF @Nationality IS NULL
	BEGIN
		SELECT 1 as code,'Native Country is missing.' AS remarks
		RETURN;
	END
	IF @idType IS NULL or @idNumber IS NULL
	BEGIN
		SELECT 1 as code,'Id type or Number is missing.' AS remarks
		RETURN;
	END
	IF @address IS NULL
	BEGIN
		SELECT 1 as code,'Address is missing.' AS remarks
		RETURN;
	END
	IF @cntdob IS NULL
	BEGIN
		SELECT 1 as code,'DOB/DOI is missing.' AS remarks
		RETURN;
	END
END

IF @isNewCustomer = 'Y'
BEGIN
	IF EXISTS(SELECT TOP 1 'A' FROM EXCHANGE_CUSTOMERSETUP(NOLOCK) WHERE chkId = REPLACE(REPLACE(REPLACE(@IDNUMBER,' ',''),'-',''),'/','') AND idType = @idType )
	BEGIN
		SELECT 1 as code,'ID Number is already added.' AS remarks
		RETURN;
	END
	ELSE IF EXISTS(SELECT TOP 1 'A' FROM EXCHANGE_CUSTOMERSETUP(NOLOCK) WHERE cName = @customer AND dob = @cntdob)
	BEGIN
		SELECT 1 as code,'Customer is already added.' AS remarks
		RETURN;
	END
END

SET @strMark='N' --Direct reject concept for now 
IF @strMark ='Y'
BEGIN
	IF @strReason IS NULL
	BEGIN
		SELECT 1 as code,'STR Reason is missing..' AS remarks
		RETURN;
	END
	IF @strRemark IS NULL
	BEGIN
		SELECT 1 as code,'STR Remark is missing..' AS remarks
		RETURN;
	END
END
	----SELECT @isOfac = name FROM VwOFACList WHERE REPLACE(name,',','') = REPLACE(@customer,',','')

DECLARE @custInfoCheck MONEY, @EDD MONEY,@txnApproveAmt MONEY,@perDayTxn MONEY,@perMonthTxn MONEY,@perYearTxn MONEY


SELECT @custInfoCheck = ISNULL(chkcddOn,0) * rate ,@EDD = ISNULL(chkEddOn,0) * rate ,@txnApproveAmt = ISNULL(perTxnLimit,0) * rate
	,@perDayTxn = ISNULL(perDayLimit,0) * rate,@perMonthTxn = ISNULL(perMonthLimit,0) * rate,@perYearTxn = ISNULL(perYearLimit,0) * rate
	FROM FastMoneyPro_Account.dbo.EXCHANGE_TRANSACTIONPOLICY (NOLOCK)   ---- REMOVE COLUMN FROM TABLE 

--#####################################################
-- CHECKING FOR TOTAL WON 10000 AND 20000 VALIDATION   OR @issuedate IS NULL OR @expdate IS NULL
SELECT @total_lcy = ISNULL(SUM(lcyamt),0) FROM FastMoneyPro_Account.dbo.TEMP_TRANSACTION(NOLOCK) WHERE sessionId = @sessionId AND tran_type='S' AND BRANCH = @branchID

----## Customer type Individual and Customer Type Others limit setup for branch and agent in equivalent WON (Buy and Sell) -  Limit exceeds deny TXN
	DECLARE @partyName VARCHAR(100)
	SELECT @partyName = ISNULL(ref_code,'') FROM FastMoneyPro_Account.dbo.staticdatadetail(NOLOCK) WHERE id = @party
	
	----## FOR CUSTOMER TYPE WISE
	IF EXISTS(SELECT 'A' FROM EXCHANGE_CUSTOMERLIMITDETAIL WITH(NOLOCK) WHERE limitId = @currLimitId AND customerType = @party 
		AND currency = @currencyName AND ISNULL(isActive,'N') ='Y' AND ISNULL(sellLimit,0) >0 AND @total_lcy >= ISNULL(sellLimit,0))
	BEGIN
		SELECT 1 AS code, 'Deny Transaction,Sell limit exceeds for customer type: '+@partyName AS remarks
		RETURN
	END
	----## FOR ALL CUSTOMER TYPE WISE
	ELSE IF EXISTS(SELECT 'A' FROM EXCHANGE_CUSTOMERLIMITDETAIL WITH(NOLOCK) WHERE limitId = @currLimitId  
		AND currency = @currencyName AND ISNULL(isActive,'N') ='Y' AND customerType=0 AND ISNULL(sellLimit,0) >0 AND @total_lcy >= ISNULL(sellLimit,0))
	BEGIN
		SELECT 1 AS code, 'Deny Transaction,Sell limit exceeds for customer type: '+@partyName AS remarks
		RETURN
	END
	
	--## CURRENCY WISE CUSTOMER LIMIT CHECKING 
	ELSE IF EXISTS(SELECT 'A' FROM EXCHANGE_CUSTOMERLIMITDETAIL L WITH(NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION T ON L.currency = T.acct_num
		AND sessionId=@sessionId AND tran_type='S' AND BRANCH = @branchID
		WHERE limitId = @currLimitId AND customerType = @party 
		AND ISNULL(isActive,'N') ='Y' AND ISNULL(sellLimit,0) >0 GROUP BY T.acct_num,sellLimit HAVING SUM(T.fcyamt) >=ISNULL(sellLimit,0))
	BEGIN
		SET @remarks = 'Deny Transaction,Sell limit exceeds for currency : '
		
		SELECT @remarks = @remarks+ T.acct_num+',' FROM EXCHANGE_CUSTOMERLIMITDETAIL L WITH(NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION T ON L.currency = T.acct_num
		AND sessionId=@sessionId AND tran_type='S' AND BRANCH = @branchID
		WHERE limitId = @currLimitId AND customerType = @party 
		AND ISNULL(isActive,'N') ='Y' AND ISNULL(sellLimit,0) >0 GROUP BY T.acct_num,sellLimit HAVING SUM(T.fcyamt) >=ISNULL(sellLimit,0)
		
		SELECT 1 AS code, ISNULL(@remarks,'Deny Transaction,Sell limit exceeds for currency') AS remarks
		
		RETURN
	END
	ELSE IF EXISTS(SELECT 'A' FROM EXCHANGE_CUSTOMERLIMITDETAIL L WITH(NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION T ON L.currency = T.acct_num
		AND sessionId=@sessionId AND tran_type='S' AND BRANCH = @branchID AND customerType=0
		WHERE limitId = @currLimitId AND ISNULL(isActive,'N') ='Y' AND ISNULL(sellLimit,0) >0 
		GROUP BY T.acct_num,sellLimit HAVING SUM(T.fcyamt) >=ISNULL(sellLimit,0))
	BEGIN
		SET @remarks = 'Deny Transaction,Sell limit exceeds for currency : '
		
		SELECT @remarks = @remarks+ T.acct_num+',' FROM EXCHANGE_CUSTOMERLIMITDETAIL L WITH(NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION T(NOLOCK) ON L.currency = T.acct_num
		AND sessionId=@sessionId AND tran_type='S' AND BRANCH = @branchID AND customerType=0
		WHERE limitId = @currLimitId AND ISNULL(isActive,'N') ='Y' AND ISNULL(sellLimit,0) >0 
		GROUP BY T.acct_num,sellLimit HAVING SUM(T.fcyamt) >=ISNULL(sellLimit,0)
		
		SELECT 1 AS code, ISNULL(@remarks,'Deny Transaction,Sell limit exceeds for currency') AS remarks
		
		RETURN
	END
	--## CURRENCY WISE LIMIT CHECKING
	ELSE IF EXISTS(SELECT 'A' FROM EXCHANGE_CURRENCYRATE_SETUP R WITH(NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION T(NOLOCK) ON R.baseCurrency = T.acct_num
		AND sessionId = @sessionId AND tran_type='S' AND BRANCH = @branchID
		WHERE ISNULL(R.Active,'N') ='Y' AND ISNULL(sellLimit,0) >0 
		GROUP BY T.acct_num,sellLimit HAVING SUM(T.fcyamt) >=ISNULL(sellLimit,0))
	BEGIN
		
		SET @remarks = 'Deny Transaction,Sell limit exceeds for currency : '
		
		SELECT @remarks = @remarks + T.acct_num +',' FROM EXCHANGE_CURRENCYRATE_SETUP R WITH(NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION T(NOLOCK) ON R.baseCurrency = T.acct_num
		AND sessionId = @sessionId AND tran_type='S' AND BRANCH = @branchID
		WHERE ISNULL(R.Active,'N') ='Y' AND ISNULL(sellLimit,0) >0 
		GROUP BY T.acct_num,sellLimit HAVING SUM(T.fcyamt) >=ISNULL(sellLimit,0)
		
		SELECT 1 AS code, ISNULL(@remarks,'Deny Transaction,Sell limit exceeds for currency') AS remarks
		RETURN
	END


IF (SELECT SUM(total_amt)+ @total_lcy FROM EXCHANGE_TRANSACTION_INFO(NOLOCK)
		 WHERE entered_date BETWEEN CAST(GETDATE() AS DATE) AND GETDATE() AND id_number = @idNumber) >=ISNULL(@custInfoCheck,0)
BEGIN
	IF(@customer IS NULL OR @address IS NULL OR @idType IS NULL OR @idNumber IS NULL OR @Nationality IS NULL )
	BEGIN
		SELECT 1 as code,'All customer due diligence not filled for amt: '+ CAST(ISNULL(@custInfoCheck,0) AS VARCHAR) as remarks
		RETURN;
	END
	IF(SELECT SUM(total_amt)+ @total_lcy FROM EXCHANGE_TRANSACTION_INFO(NOLOCK) WHERE entered_date BETWEEN CAST(GETDATE() AS DATE) AND GETDATE()
		AND id_number = @idNumber)>=ISNULL(@EDD,0)
	BEGIN
		IF(@occupation IS NULL OR @EMPNAME IS NULL OR @moneysource IS NULL OR @purpose IS NULL )
		BEGIN
			SELECT 11 as code,'All Enhance due diligence not filled for amt: '+CAST(ISNULL(@EDD,0) AS VARCHAR) as remarks
			RETURN;
		END
	END
	
END

IF(SELECT SUM(total_amt)+ @total_lcy FROM EXCHANGE_TRANSACTION_INFO(NOLOCK) WHERE entered_date BETWEEN CAST(GETDATE() AS DATE) AND GETDATE()
		AND id_number = @idNumber)> =ISNULL(@perDayTxn,0)
BEGIN
	SELECT 11 as code,'Per day transaction limit exceeded for amt: '+CAST(ISNULL(@perDayTxn,0) AS VARCHAR) as remarks
	RETURN;
END
IF(SELECT SUM(total_amt)+ @total_lcy FROM EXCHANGE_TRANSACTION_INFO(NOLOCK) WHERE entered_date BETWEEN CAST(GETDATE() AS DATE) AND GETDATE()
		AND id_number = @idNumber)> =ISNULL(@perMonthTxn,0)
BEGIN
	SELECT 11 as code,'Per month transaction limit exceeded for amt: '+CAST(ISNULL(@perMonthTxn,0) AS VARCHAR) as remarks
	RETURN;
END
IF(SELECT SUM(total_amt)+ @total_lcy FROM EXCHANGE_TRANSACTION_INFO(NOLOCK) WHERE entered_date BETWEEN CAST(GETDATE() AS DATE) AND GETDATE()
		AND id_number = @idNumber)> =ISNULL(@perYearTxn,0)
BEGIN
	SELECT 11 as code,'Per year transaction limit exceeded for amt: '+CAST(ISNULL(@perYearTxn,0) AS VARCHAR) as remarks
	RETURN;
END
	
	--IF  @isOfac = 'Y' OR (@total_lcy >= @txnApproveAmt AND @txnApproveAmt >0) OR (@CHKGtTOindBranch = 'Y')
	--BEGIN
			
	--		IF @isOfac = 'Y'
	--			SET @OFACREMARKS = ISNULL(@OFACREMARKS,'')
	--		ELSE IF @CHKGtTOindBranch = 'Y'
	--			SET @OFACREMARKS = 'Transaction has been sent successfully and is waiting for approval <br/> '+'Customer transacting from more than 2 branches for amt: '+CAST(ISNULL(@GtTOindBranchAmt,0) AS VARCHAR)
			
			
	--	BEGIN TRANSACTION	
	--		INSERT INTO EXCHANGE_HOLDTRANSACTION_INFO (
	--			branch_id,customer_name,id_type,id_number,country,id_issuedate,id_expdate,tran_type,total_amt,remarks,
	--			entered_by,entered_date,tran_date,contact,Nationality,issuedFrom,party,isverify,operation,tranStatus,custId,sessionId)
	--		SELECT	
	--			@branchID,@customer,@idType,@idNumber,@country,@issuedate,@expdate,'S',ISNULL(@total_lcy,0),@remarks,
	--			@user,GETDATE(),@tran_date,@contact,@Nationality,@issuefrom,@party,@isverify,@type
	--			,CASE WHEN  @isOfac = 'Y' THEN 'OFAC Hold' ELSE 'Hold' END,@filterCust,@sessionId
			
	--		SET @LOG = @@IDENTITY
			
	--		INSERT INTO EXCHANGE_CUSTOMER (
	--			ct_name,ct_address,ct_card_type,ct_card_no,country,issuedate,expdate,occupation,employer_name,moneysource,purpose,sessionId,
	--			benefName,beneidtype,beneidnum,contact ,Nationality,issuedFrom,isOfac 
	--			,ofacremarks,branch_id,CID,custAcNum
	--			,businessType,town,custState,postalCode,tran_type,party,ofacId,ct_dob,modifyBy,modifyDate)
	--	SELECT @customer,@address,@idType,@idNumber,@country,@issuedate,@expdate,@occupation,@EMPNAME,@moneysource,@purpose,@LOG,
	--			@benefName,@beneidtype,@beneidnum,@contact,@Nationality,@issuefrom,CASE WHEN  @isOfac = 'Y' THEN 'Y' ELSE 'A' END
	--			,@OFACREMARKS,@branchID,@filterCust,@CustacNum
	--			,@businessType,@town,@custState,@postalCode,@operation,@party,@ofacId,@cntdob,@user,GETDATE()
			
			
	--		INSERT INTO EXCHANGE_OFACTRANSACTION (tran_id,sessionId,currency,fcyamt,sysrate,apprate,lcyamt,tran_date,tran_type,Branchid,entryby )	
	--		SELECT 0,@LOG,acct_num,fcyamt,sysrate,apprate,lcyamt,tran_date,tran_type,@branchID,@user 
	--		FROM FastMoneyPro_Account.dbo.TEMP_TRANSACTION(NOLOCK) WHERE tran_type = 'S' AND sessionId = @sessionId AND BRANCH = @branchID
			
	--		IF @oName IS NOT NULL
	--		BEGIN
	--			INSERT INTO EXCHANGE_TRANSACTIONONBEHALF 
	--				(tranId,custId,oName,oAddress,oTown,oState,oPostalCode,oCountry,oNationality,oidType,oidNo,oContact,oDob,sessionId
	--				,oOccupation,oBussType)
	--			SELECT 0,@filterCust,@oName,@oAddress,@oTown,@oState,@oPostalCode,@oCountry,@oNationality,@oidType,@oidNo,@oContact,@oDob,@LOG
	--				,@oOccupation,@oBussType
	--		END
			
	--		INSERT INTO EXCHANGE_CUSTOMERDOCUMENT(cId,fileName,fileDescription,fileType,createdBy,createdDate,approvedBy,approvedDate,branchId,tranId)
	--		SELECT @filterCust,docName,docDesc,docType,createdBy,createdDate,case when @party = '726' then createdBy END
	--			,case when @party = '726' then createdDate END,branchId ,@LOG
	--		FROM EXCHANGE_TEMPCUSTOMERDOCUMENT(NOLOCK) WHERE sessionId = @sessionId AND branchId = @branchID AND docFor ='C'
			
	--		INSERT INTO EXCHANGE_TRANSACTIONDOCUMENT(sessionId,fileName,fileDescription,fileType,createdBy,createdDate,branchId)
	--		SELECT @LOG,docName,docDesc,docType,createdBy,createdDate,branchId 
	--		FROM EXCHANGE_TEMPCUSTOMERDOCUMENT WHERE sessionId = @sessionId AND branchId = @branchID AND docFor ='T'

	--		IF @strMark ='Y'
	--		BEGIN
	--			INSERT INTO EXCHANGE_SUSPICIOUSTRANSACTION(holdId,reason,remarks,createdBy,createdDate)
	--			SELECT @LOG,@strReason,@strRemark,@user,GETDATE()
	--		END

	--		DELETE FROM EXCHANGE_TEMPCUSTOMERDOCUMENT WHERE sessionId = @sessionId AND branchId = @branchID
			
	--		DELETE FROM FastMoneyPro_Account.dbo.TEMP_TRANSACTION WHERE tran_type = 'S' AND sessionId = @sessionId AND BRANCH = @branchID
			
	--	COMMIT TRANSACTION	
			
	--	IF @isOfac = 'Y'
	--	BEGIN
	--			SET @OFACREMARKS = 'Transaction is in OFAC and is waiting for approval <br/>' + ISNULL(@OFACREMARKS,'')
	--			SELECT 2 as code,ISNULL(@OFACREMARKS,'Transaction has been sent successfully and is waiting for approval') AS remarks
	--		RETURN
	--	END
			
	--	SELECT 3 as code,ISNULL(@OFACREMARKS,'Transaction has been sent successfully and is waiting for approval') AS remarks
	--	RETURN;
	--END

IF EXISTS(SELECT T.ACCT_NUM [CURRENCY] ,fcyamt[FCYAMT],usd_amt*-1 [AVAILABLE]
FROM FastMoneyPro_Account.dbo.ac_master a WITH (NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION t WITH (NOLOCK)
ON A.ac_currency=T.acct_num AND A.acct_rpt_code='MoneyEx' AND T.sessionId=@sessionId AND A.agent_id=@branchID
AND fcyamt> (usd_amt*-1) AND tran_type='S')
BEGIN
SELECT @mas = COALESCE(@mas + ' <br>', '') +'Currency:'+ T.ACCT_NUM +'-FCY Amt:'+ CAST(fcyamt AS VARCHAR)+'-Balance:'+CAST(usd_amt*-1 AS VARCHAR)
FROM FastMoneyPro_Account.dbo.ac_master a WITH (NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION t WITH (NOLOCK)
ON A.ac_currency=T.acct_num AND A.acct_rpt_code='MoneyEx' AND T.sessionId=@sessionId AND A.agent_id=@branchID
AND fcyamt> (usd_amt*-1) AND tran_type='S'

SET @mas= 'Insuficient Balance for '+@mas
SELECT 1 as code,@mas AS remarks
END

--######################################################
SELECT A.acct_num, T.ACCT_NUM CURRENCY ,fcyamt ,sysrate,apprate,ROUND(fcyamt*apprate,2) lcyamt,
(ISNULL(clr_bal_amt,0)*-1 )clr_bal_amt, ISNULL(USD_AMT,0)*-1 usd_amt,
---ROUND(fcyamt * (ISNULL(clr_bal_amt,0)/ISNULL(USD_AMT,0)),2) lcysale
ROUND(fcyamt * (cast(ISNULL(clr_bal_amt,0) as float)/cast(ISNULL(USD_AMT,0) as float) ),2) lcysale
into #tempsale
FROM FastMoneyPro_Account.dbo.ac_master a WITH (NOLOCK) INNER JOIN FastMoneyPro_Account.dbo.TEMP_TRANSACTION t WITH (NOLOCK)
ON A.ac_currency=T.acct_num
AND A.acct_rpt_code='MoneyEx'
AND T.sessionId=@sessionId
AND A.agent_id=@branchID
AND tran_type='S'
--SELECT * FROM #tempsale

ALTER TABLE #tempsale ADD FX MONEY
UPDATE #tempsale SET FX= LCYAMT-LCYSALE

DECLARE @WONAC VARCHAR(50)

SELECT @WONAC=ACCT_NUM FROM FastMoneyPro_Account.dbo.ac_master(NOLOCK) WHERE ac_currency=@currencyName AND acct_rpt_code='MoneyEx' AND agent_id=@branchID


CREATE TABLE #tempsumTrn (Part_Id INT IDENTITY,acct_num VARCHAR(20),
TotalAmt DECIMAL(18,4), part_tran_type VARCHAR(2),fcy_amt FLOAT
,fcy_rate FLOAT,sys_rate FLOAT,currency VARCHAR(50),RUNNINGBALANCE MONEY,FCY_RUNNINGBALANCE MONEY)

IF @type = 'N'
BEGIN
	INSERT INTO #tempsumTrn(acct_num,TotalAmt,part_tran_type,fcy_amt,fcy_rate,sys_rate,currency,RUNNINGBALANCE,FCY_RUNNINGBALANCE)
	SELECT @WONAC acct_num,SUM(ROUND(ISNULL(lcyamt,0),2)) [lcyamt], 'DR' [tran_type]
	,0 [fcyamt] , 0 APPRATE, 0 SYSRATE,@currencyName [currency],0 RUNNINGBALANCE, 0 FCY_RUNNINGBALANCE
	FROM #tempsale t WITH (NOLOCK)

	UNION ALL

	SELECT T.acct_num,SUM(ROUND(ISNULL(lcyamt,0),2)) [lcyamt], 'CR' [tran_type]
	,SUM(ISNULL(fcyamt,0))[fcyamt]
	, APPRATE, SYSRATE, [currency], T.CLR_BAL_AMT RUNNINGBALANCE, T.usd_amt FCY_RUNNINGBALANCE
	FROM #tempsale t WITH (NOLOCK)
	GROUP BY T.acct_num,fcyamt,APPRATE,[currency],sysrate,T.CLR_BAL_AMT,T.usd_amt

END

IF @type = 'C'
BEGIN
	INSERT INTO #tempsumTrn(acct_num,TotalAmt,part_tran_type,fcy_amt,fcy_rate,sys_rate,currency,RUNNINGBALANCE,FCY_RUNNINGBALANCE)
	SELECT @CustacNum acct_num,SUM(ROUND(ISNULL(lcyamt,0),2)) [lcyamt], 'DR' [tran_type]
	,0 [fcyamt] , 0 APPRATE, 0 SYSRATE,@currencyName [currency],0 RUNNINGBALANCE, 0 FCY_RUNNINGBALANCE
	FROM #tempsale t WITH (NOLOCK)

	UNION ALL

	SELECT T.acct_num,SUM(ROUND(ISNULL(lcyamt,0),2)) [lcyamt], 'CR' [tran_type]
	,SUM(ISNULL(fcyamt,0))[fcyamt]
	, APPRATE, SYSRATE, [currency], T.CLR_BAL_AMT RUNNINGBALANCE, T.usd_amt FCY_RUNNINGBALANCE
	FROM #tempsale t WITH (NOLOCK)
	GROUP BY fcyamt,APPRATE,[currency],sysrate,T.CLR_BAL_AMT,T.usd_amt,T.acct_num
END


SELECT @totalDR=isnull(sum(TotalAmt),0) FROM #tempsumTrn WITH (NOLOCK)
WHERE part_tran_type='DR' GROUP BY part_tran_type

SELECT @totalCR=isnull(sum(TotalAmt),0) FROM #tempsumTrn WITH (NOLOCK)
WHERE part_tran_type='CR' GROUP BY part_tran_type


--SELECT @totalDR,@totalCR
--RETURN

		IF EXISTS(SELECT 'A' FROM #tempsumTrn WHERE acct_num IS NULL)
		BEGIN
			SELECT 1 as code,'Some account is missing..' AS remarks
			RETURN;
		END

IF ISNULL(@totalDR,0)<>ISNULL(@totalCR,0)
BEGIN
SELECT 1 as code,'DR and CR amount NOT Equal' AS remarks
RETURN;
END
-----SELECT @isOfac=name FROM VwOFACList WHERE name LIKE '%'+REPLACE(@customer,',','')+'%'
-------------#########################################
declare @BILLNO VARCHAR(30)
BEGIN TRANSACTION
	
	IF NOT EXISTS(select 'a' from agentMaster(NOLOCK) where agentid=@branchId and extCode is null)
	BEGIN
		update agentMaster set extCode='1' where agentid=@branchId and extCode is null
	END
	SELECT @BILLNO = dbo.EXCHANGE_FNAGetBillNo(@branchID,'S')
	
	INSERT INTO EXCHANGE_TRANSACTION_INFO (
		branch_id,customer_name,id_type,id_number,country,id_issuedate,id_expdate,tran_type,total_amt,remarks,entered_by,entered_date,
		tran_date,contact,Nationality,issuedFrom,party,isverify,operation,billNo
				)
	SELECT	
		@branchID,@customer,@idType,@idNumber,@country,@issuedate,@expdate,'S',ISNULL(@total_lcy,0),@remarks,@user,GETDATE()
		,@tran_date,@contact,@Nationality,@issuefrom,@party,@isverify,@type,@BILLNO
		
	SET @LOG = @@IDENTITY
	
	UPDATE agentMaster SET extCode = cast(extCode as int)+1 WHERE agentId = @branchID
	
	IF @customer IS NOT NULL
	BEGIN
		INSERT INTO Exchange_Customer (ct_name,ct_address,ct_card_type,ct_card_no,country,issuedate,expdate,occupation,employer_name,moneysource,
				purpose,tran_id,benefName,beneidtype,beneidnum,contact ,Nationality,issuedFrom,isOfac,remarks,ct_dob,CID,custAcNum,
				businessType,town,custState,postalCode,tran_type,ofacId,party,ofacStatus,modifyBy,modifyDate
		)
		SELECT @customer,@address,@idType,@idNumber,@country,@issuedate,@expdate,@occupation,@EMPNAME,@moneysource,
				@purpose,@LOG,@benefName,@beneidtype,@beneidnum,@contact,@Nationality,@issuefrom,CASE WHEN @isOfac IS NOT NULL THEN 'Y' ELSE NULL END,
				@remarks,@cntdob,@filterCust,@CustacNum,
				@businessType,@town,@custState,@postalCode,'S',@ofacId,@party,@saveOFACTxn,@user,GETDATE()
	END	
	IF @saveOFACTxn IS NOT NULL
	BEGIN
		INSERT INTO EXCHANGE_OFACTRANSACTION (tran_id,sessionId,currency,fcyamt,sysrate,apprate,lcyamt,tran_date,tran_type,Branchid,entryby )	
		SELECT @LOG,@sessionId,acct_num,fcyamt,sysrate,apprate,lcyamt,tran_date,tran_type,@branchID,@user 
		FROM FastMoneyPro_Account.dbo.TEMP_TRANSACTION WHERE tran_type = 'S' AND sessionId = @sessionId AND BRANCH = @branchID	
	END
	
	IF @isNewCustomer = 'Y' AND @customer IS NOT NULL
	BEGIN
		INSERT INTO EXCHANGE_CUSTOMERSETUP(cName,cAddress,gender,country,idType,idNumber,dob,postalCode,contact,email,cType,occupation,branchId,createdDate,createdBy,town,custState,nativCountry,approvedBy,approvedDate,businessType,chkId)
		SELECT ct_name,ct_address,NULL,country,ct_card_type,ct_card_no,CAST(ct_dob AS DATE),@postalCode,contact,NULL,@party,occupation,@branchID
				,GETDATE(),@user,town,custState,Nationality,@USER ,GETDATE()
				,businessType,REPLACE(REPLACE(REPLACE(ct_card_no,' ',''),'-',''),'/','')
		FROM Exchange_Customer WITH(NOLOCK)
		WHERE tran_id = @LOG
		
		SET @filterCust = @@IDENTITY
		
		--UPDATE Exchange_Customer SET CID = @filterCust WHERE tran_id = @LOG
		
	END
	IF @isNewCustomer = 'N'
	BEGIN
		UPDATE  CS SET
			CS.cName		= C.ct_name
			,CS.cAddress	= C.ct_address
			,CS.gender		= NULL
			,CS.country		= C.country
			,CS.idType		= C.ct_card_type
			,CS.idNumber	= C.ct_card_no
			,CS.chkId		= REPLACE(REPLACE(REPLACE(C.ct_card_no,' ',''),'-',''),'/','')
			,CS.dob			= CASE WHEN C.ct_dob IS NOT NULL THEN CAST(C.ct_dob AS DATE) ELSE CS.dob END
			,CS.postalCode	= @postalCode
			,CS.contact		= C.contact
			,CS.email		= NULL
			,CS.cType		= @party
			,CS.occupation	= C.occupation
			,CS.branchId	= @branchID
			,CS.town		= C.town
			,CS.custState	= C.custState
			,CS.nativCountry = C.Nationality
			,CS.businessType= C.businessType
			,CS.modifyBy	= @user
			,CS.modifyDate	= GETDATE()
		FROM EXCHANGE_CUSTOMERSETUP CS(NOLOCK)
		INNER JOIN Exchange_Customer C(NOLOCK) ON c.CID = CS.cid
		WHERE CS.cid = @filterCust AND C.tran_id = @LOG
		
	END
	
	--IF @oName IS NOT NULL
	--BEGIN
	--	INSERT INTO EXCHANGE_TRANSACTIONONBEHALF 
	--				(tranId,custId,oName,oAddress,oTown,oState,oPostalCode,oCountry,oNationality,oidType,oidNo,oContact,oDob,sessionId
	--				,oOccupation,oBussType)
	--	SELECT @LOG,@filterCust,@oName,@oAddress,@oTown,@oState,@oPostalCode,@oCountry,@oNationality,@oidType,@oidNo,@oContact,@oDob,NULL
	--				,@oOccupation,@oBussType
	--END
	
	INSERT INTO EXCHANGE_CUSTOMERDOCUMENT(cId,fileName,fileDescription,fileType,createdBy,createdDate,approvedBy,approvedDate,branchId,tranId)
	SELECT @filterCust,docName,docDesc,docType,createdBy,createdDate,case when @party = '276' then createdBy END
		,case when @party = '276' then createdDate END,branchId ,@LOG
	FROM EXCHANGE_TEMPCUSTOMERDOCUMENT(NOLOCK) WHERE sessionId = @sessionId AND branchId = @branchID AND docFor ='C'
	
	INSERT INTO EXCHANGE_TRANSACTIONDOCUMENT(tranId,fileName,fileDescription,fileType,createdBy,createdDate,branchId)
	SELECT @LOG,docName,docDesc,docType,createdBy,createdDate,branchId 
	FROM EXCHANGE_TEMPCUSTOMERDOCUMENT(NOLOCK) WHERE sessionId = @sessionId AND branchId = @branchID AND docFor ='T'
	
	IF @strMark ='Y'
	BEGIN
		INSERT INTO EXCHANGE_SUSPICIOUSTRANSACTION(billNo,reason,remarks,createdBy,createdDate)
		SELECT @BILLNO,@strReason,@strRemark,@user,GETDATE()
	END
	
	DELETE FROM EXCHANGE_TEMPCUSTOMERDOCUMENT WHERE sessionId = @sessionId AND branchId = @branchID
	
----------------------------------------
IF ISNULL(@saveOFACTxn,'S') = 'S'
BEGIN
SELECT @Part_Id=max(Part_Id) FROM #tempsumTrn

---- Start loop count
--SET @totalRows=1
--WHILE @Part_Id >= @totalRows
--BEGIN

-- -- row wise trn VALUES
-- SELECT @ACCTNUM=acct_num,@TotalAmt=TotalAmt,@trntype=part_tran_type
-- FROM #tempsumTrn WHERE Part_Id=@totalRows

-- EXEC ProcDrCrUpdateFinal @trntype ,@ACCTNUM, @TotalAmt,0

--SET @totalRows=@totalRows+1
--END

-- Create Bill Number
SELECT @ref_num= isnull(fcy_sales,1) FROM FastMoneyPro_Account.dbo.BillSetting(NOLOCK) where company_id = 1

UPDATE FastMoneyPro_Account.dbo.BillSetting SET fcy_sales=isnull(fcy_sales,1)+1 WHERE company_id=1

update FastMoneyPro_Account.dbo.ac_master set
CLR_BAL_AMT=case
when part_tran_type='dr' then isnull(CLR_BAL_AMT,0) - isnull(TotalAmt,0)
else isnull(CLR_BAL_AMT,0) - isnull(-TotalAmt,0)
end
,usd_amt=case
when part_tran_type='dr' then isnull(usd_amt,0) - isnull(fcy_amt,0)
else isnull(usd_amt,0) - isnull(-fcy_amt,0)
end
FROM FastMoneyPro_Account.dbo.ac_master A WITH (NOLOCK)
INNER JOIN #tempsumTrn T ON T.acct_num =A.acct_num


--SELECT * FROM FastMoneyPro_Account.dbo.tran_master

INSERT INTO FastMoneyPro_Account.dbo.tran_master (RunningBalance,tran_date,usd_amt,usd_rate,sys_rate ,entry_user_id
,acc_num,gl_sub_head_code ,part_tran_type ,ref_num
,tran_amt,tran_type,created_date,billno,fcy_Curr,company_id,part_tran_srl_num,field2)
SELECT FCY_RUNNINGBALANCE,@tran_date,fcy_amt,fcy_rate,sys_rate,@user,acct_num,FastMoneyPro_Account.dbo.FunGetGLCode(acct_num),part_tran_type,@ref_num,TotalAmt,
'S',GETDATE(),@LOG,currency,1,
ROW_NUMBER() OVER(ORDER BY part_tran_type desc),'MoneyEx'
FROM (
SELECT RUNNINGBALANCE,FCY_RUNNINGBALANCE,currency,fcy_amt,fcy_rate,sys_rate, acct_num,TotalAmt,part_tran_type FROM #tempsumTrn
)a

INSERT INTO FastMoneyPro_Account.dbo.tran_masterDetail(ref_num,tran_particular,tran_type,tranDate,company_id)
VALUES (@ref_num, ISNULL(@remarks,'SELL'), 'S',@tran_date,1)


	UPDATE T SET T.DENOS  = D.DENOS
	FROM FastMoneyPro_Account.dbo.tran_master T 
	LEFT JOIN FastMoneyPro_Account.dbo.EXCHANGE_tempDeno D ON T.fcy_curr = D.curr AND T.usd_amt = D.cAmt
	WHERE D.sessionId = @SESSIONID and D.tranType = 'S' and D.branchId = @branchID AND T.billno = @LOG
	
	DELETE FROM FastMoneyPro_Account.dbo.EXCHANGE_TEMPDENO WHERE sessionId = @SESSIONID and tranType = 'S' and branchId = @branchID

--SELECT '<b><font color="red">Sell Success voucher No:<a target=''_blank'' href=''userreport_result.asp?trn_date='+CONVERT(VARCHAR,GETDATE(),101)+'&type=trannumber&tran_num='+ cast(@ref_num AS VARCHAR) +'&vouchertype=S'' > '+ cast(@ref_num AS VARCHAR) +' </a></font></b>' +
--'<b><font color="red">Receipt No:<a target=''_blank'' href=''receipt_result.asp?tran_type=s&trn_id='+CONVERT(VARCHAR,@LOG)+''' title="Receipt" rel="gb_page_center[300,430]"> '+ CAST(@LOG AS VARCHAR) +' </a></font></b>' AS remarks
-----------------------------------------
SELECT 0 as code,CAST(@LOG AS VARCHAR) AS   remarks

END
IF ISNULL(@saveOFACTxn,'N') = 'R'
BEGIN
	SELECT 1 as code,'OFAC Transaction Rejected successfully!' AS remarks
END

DELETE FROM FastMoneyPro_Account.dbo.TEMP_TRANSACTION WHERE sessionId = @sessionId AND tran_type = 'S' AND BRANCH = @branchID

IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END

END TRY
BEGIN CATCH
     IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION
     SELECT  1 as code, ERROR_MESSAGE() remarks
END CATCH
GO