Extraction part P2T(Xtransactions Fbaln r) - SP
CREATE PROCEDURE
@OriginatingBranch VARCHAR(3) ,
@SourceBranchCode VARCHAR(3) , /* Branch of the customer a/c */
@SourceMainCode u_MainCodeLen , /* Source (Debited if amount is +ve) */
@DestBranchCode VARCHAR(3) , /* Branch of the merchant */
@DestMainCode u_MainCodeLen , /* Destination (Credited is amount is +ve) */
@CyCodeISO VARCHAR(3) ,
@Amount MONEY , /* Amount always postive */
@TermType VARCHAR(1) , /* FROM A=ATM, I=EBank, etc */
@TranCodeDr VARCHAR(3) ,
@TranCodeCr VARCHAR(3) ,
@UserId VARCHAR(6) ,
@Station VARCHAR(3) ,
@BrCodeComm1 VARCHAR(3) ,/*Added on 15 Aug 2013 */
@BrCodeComm2 VARCHAR(3) , /*Added on 15 Aug 2013 */
@BrCodeComm3 VARCHAR(3) , /*Added on 15 Aug 2013 */
@BrCodeComm4 VARCHAR(3) ,
@BrCodeComm5 VARCHAR(3) ,
@CyCode1 VARCHAR(3) ,
@CyCode2 VARCHAR(3) ,
@CyCode3 VARCHAR(3) ,
@CyCode4 VARCHAR(3) ,
@CyCode5 VARCHAR(3) ,
@CommAmt1 MONEY ,
@CommAmt2 MONEY ,
@CommAmt3 MONEY ,
@CommAmt4 MONEY ,
@CommAmt5 MONEY ,
@CommAcc1 u_MainCodeLen ,
@CommAcc2 u_MainCodeLen ,
@CommAcc3 u_MainCodeLen ,
@CommAcc4 u_MainCodeLen ,
@CommAcc5 u_MainCodeLen ,
@CommTranCodeDr VARCHAR(3) ,
@CommTranCodeCr VARCHAR(3) ,
@PAN VARCHAR(19) = NULL ,
@Desc1 VARCHAR(35) = NULL ,
@Desc2 VARCHAR(20) = NULL ,
@Desc3 VARCHAR(20) = NULL ,
@IsReversal TINYINT = 0 , /* 1 is True 0 is False */
@SIICode VARCHAR(4) , /* OPOS, OATM, VISA, MCCS, MCDS */
@RetrievalRef VARCHAR(12) = NULL ,
@MessageCode VARCHAR(4) = NULL ,
@TranId CHAR(9) ,
@RetPumCode VARCHAR(2) OUTPUT ,
@MsgStr VARCHAR(255) = NULL OUTPUT
AS
DECLARE @Result INT ,
@tMainCode u_MainCodeLen ,
@BranchCode VARCHAR(3) ,
@ThisBranch VARCHAR(3) , /* Running branch */
@NostroBranch VARCHAR(3) ,
@tNostroBranch VARCHAR(3) ,
@ISOCodeNo VARCHAR(3) ,
@tTranCode VARCHAR(3) ,
@AcType VARCHAR(2) ,
@CyCode VARCHAR(2) ,
@AcTypeType VARCHAR(1) ,
@IsBlocked VARCHAR(1) ,
@IsDormant VARCHAR(1) ,
@DestAc u_MainCodeLen ,
@SegerageChgAc u_MainCodeLen ,
@TotChgAmtLCY MONEY ,
@HighLcyAmt MONEY ,
@FCY_Amt MONEY ,
@LCY_Amt MONEY ,
@AmountLCY MONEY ,
@SegerageChgAmt MONEY ,
@SegerageChgAmtLCY MONEY ,
@CommAmt MONEY ,
@CommAmtLCY MONEY ,
@CommAmtLCY1 MONEY ,
@CommAmtLCY2 MONEY ,
@CommAmtLCY3 MONEY ,
@CommAmtLCY4 MONEY ,
@CommAmtLCY5 MONEY ,
@LCY_Rate FLOAT ,
@Reval_A FLOAT ,
@Reval_B FLOAT ,
@ConvertRate FLOAT ,
@Today DATETIME ,
@LCYCode VARCHAR(2) ,
@CStartCrCode VARCHAR(3) ,
@RateCode VARCHAR(1) ,
@MoveType VARCHAR(1) ,
@Name VARCHAR(25) ,
@MoneyRound SMALLINT ,
@ServerName VARCHAR(50) ,
@DatabaseName VARCHAR(50) ,
@IsOnLine VARCHAR(1) ,
@ABBSCode VARCHAR(1) ,
@tABBSCode VARCHAR(1) ,
@LoginInserted TINYINT , /* 1 is True */
@IsABBS TINYINT , /* 1 is True */
@TempStr VARCHAR(50) ,
@sProc VARCHAR(200) ,
@AvailBaln MONEY ,
@GoodBaln MONEY ,
@TermHeldAmt MONEY ,
@TermCharge MONEY ,
@ForceWarning VARCHAR(255) ,
@WarnMesg VARCHAR(255) ,
@StackError INT ,
@WarnError INT ,
@ChgCount INT ,
@ShowTranSet VARCHAR(1) ,
@CanStack VARCHAR(1) ,
@CanPost VARCHAR(1) ,
@CheckNum VARBINARY(1000),
@EndDayOp VARCHAR(1),
@DayStarted VARCHAR(1),
@MainCode u_MainCodeLen,
@TranCode VARCHAR(3),
@BankCode VARChAR(3),
@ChequeNo u_ChequeNoLen,
@LCYAmount MONEY,
@ExtraChqNo VARCHAR(6),
@ApprovedBy VARCHAR(6),
@EnteredBy VARCHAR(6),
@TranCyCode VARCHAR(2),
@CustCode VARCHAR(7),
@SubCustCode VARCHAR(7),
@Notice VARCHAR(1),
@ValueDate DATETIME,
@CyCodeSource CHAR(3),
@CyCodeDest CHAR(3),
@SeqNo INT,
@TotRecord INT,
@RecordCount INT,
@CrTotBS MONEY,
@DrTotBS MONEY,
@CrTotOB MONEY,
@DrTotOB MONEY
DECLARE @InMemTrans TABLE (
[MainCode] [u_MainCodeLen] NOT NULL,
[Name] [varchar](35) NULL,
[TranCode] [char](3) NULL,
[ErrorCode] [smallint] NULL,
[Amount] [money] NULL,
[LCYAmount] [money] NULL,
[Desc1] [varchar](35) NULL,
[Desc2] [varchar](20) NULL,
[Desc3] [varchar](20) NULL,
[ValueDate] [datetime] NULL,
[TranCyCode] [char](2) NULL,
[BranchCode] [char](3) NULL,
[AcType] [char](2) NULL,
[CyCode] [char](2) NULL,
[ApprovedBy] [char](6) NULL,
[EnteredBy] [char](6) NULL,
[Status] [char](1) NULL,
[ABBSCode] [char](1) NULL,
[Notice] [char](1) NULL,
[RateCode] [char](1) NULL,
[ReferenceNo] [char](12) NULL,
[BankCode] [char](3) NULL,
[ChequeNo] [dbo].[u_ChequeNoLen] NULL,
[TemporaryLimit] [money] NULL,
[AcTypeType] [varchar](1) NULL,
[NostroBranch] [varchar](3) NULL,
[CityCode] [varchar](3) NULL,
[ExtraChqNo] [varchar](6) NULL,
[TranId] [varchar](9) NULL,
[UserId] [varchar](6) NULL,
[TranDate] [datetime] NULL,
[HostProcess] [varchar](20) NULL,
[InterBank] [char](1) NULL,
[ReconType] [varchar](2) NULL,
[NostroCode] [varchar](3) NULL,
[CustCode] [varchar](7) NULL,
[SubCustCode] [varchar](7) NULL,
[SeqNo] [int] IDENTITY(1,1) NOT NULL)
SET XACT_ABORT ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SELECT @RetPumCode = 'EE', /*System error 96*/ @Result = 0, @ThisBranch = @SourceBranchCode,
@CyCodeSource = '', @CyCodeDest = ''
SELECT @Today = CurrentDate, @EndDayOp = ISNULL(EndDayOp, 'F'), @DayStarted = ISNULL(DayStarted, 'F')
FROM ControlTable (NOLOCK)
IF ( @ThisBranch = ISNULL(@SourceBranchCode, @ThisBranch) )
AND ( @ThisBranch = ISNULL(@DestBranchCode, @ThisBranch) )
SELECT @IsABBS = 0 /* False */
ELSE
SELECT @IsABBS = 1 /* True */
IF @IsABBS = 0 BEGIN
IF @CommAmt1 > 0 BEGIN
IF @ThisBranch <> ISNULL(@BrCodeComm1, @ThisBranch)
SET @IsABBS = 1 /* True */
IF @CommAmt2 > 0 AND @ThisBranch <> ISNULL(@BrCodeComm2, @ThisBranch)
SET @IsABBS = 1 /* True */
IF @CommAmt3 > 0 AND @ThisBranch <> ISNULL(@BrCodeComm3, @ThisBranch)
SET @IsABBS = 1 /* True */
IF @CommAmt4 > 0 AND @ThisBranch <> ISNULL(@BrCodeComm4, @ThisBranch)
SET @IsABBS = 1 /* True */
IF @CommAmt5 > 0 AND @ThisBranch <> ISNULL(@BrCodeComm5, @ThisBranch)
SET @IsABBS = 1 /* True */
END
END
IF ISNULL(@UserId, '') = ''
SELECT @UserId = CDefaultUser, @Station = CDefaultStation FROM ConstantTable (NOLOCK)
SELECT @LCYCode = CLCYCode, @RateCode = CRateCode, @MoneyRound = MoneyRound, @CStartCrCode = CStartCrCode
FROM ConstantTable (NOLOCK)
IF NOT EXISTS ( SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @SourceBranchCode AND MainCode = @SourceMainCode ) BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) Source MainCode Invalid : ' + ISNULL(@SourceMainCode, 'NULL') + ' '
+ ISNULL(@SourceBranchCode, 'NULL'), @RetPumCode = 'AI'
RETURN -101
END
SELECT @IsBlocked = ISNULL(IsBlocked, ''), @IsDormant = ISNULL(IsDormant, '')
FROM Master (NOLOCK)
WHERE BranchCode = @SourceBranchCode
AND MainCode = @SourceMainCode
IF @IsBlocked = 'C' BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) A/c is Closed : ' + @SourceMainCode + ' ' + @SourceBranchCode, @RetPumCode = 'AC'
RETURN -8
END
IF @IsBlocked IN ( 'T', 'B' ) BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) A/c is blocked : ' + @SourceMainCode + ' ' + @SourceBranchCode, @RetPumCode = 'AB'
RETURN -81
END
IF @IsReversal = 0 BEGIN /* NOT a reversal */
IF @IsBlocked = '+' BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) A/c is Dr Restricted : ' + @SourceMainCode + ' ' + @SourceBranchCode, @RetPumCode = 'PV'
RETURN -8
END
END
IF @IsBlocked = 'L' BEGIN /* Linked A/c */
SELECT @DestAc = Destination FROM LinkTable (NOLOCK) WHERE SourceBranch = @SourceBranchCode AND Source = @SourceMainCode
IF @DestAc = NULL BEGIN
SELECT @MsgStr = 'A/c ' + @SourceMainCode + ' is linked, but Linked A/c is missing'
RETURN -8
END ELSE BEGIN
SELECT @MsgStr = 'A/c ' + @SourceMainCode + ' is linked to ' + @DestAc
RETURN -8
END
END
IF @IsDormant = 'T' BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) A/c is Dormant : ' + @SourceMainCode + ' ' + @SourceBranchCode, @RetPumCode = 'PV'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @DestBranchCode AND MainCode = @DestMainCode AND ISNULL(IsBlocked, '') = 'C') BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) A/c is Closed : ' + @DestMainCode + ' ' + @DestBranchCode, @RetPumCode = 'AC'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @DestBranchCode AND MainCode = @DestMainCode AND ISNULL(IsBlocked, '') IN ('T', 'B')) BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) A/c is blocked : ' + + @DestMainCode + ' '+ @DestBranchCode, @RetPumCode = 'AB'
RETURN -81
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @DestBranchCode AND MainCode = @DestMainCode AND MoveType ='6') BEGIN
SELECT @MsgStr = '(PDXHPBalnXfr) Destination A/C is LoanType : ' + + @DestMainCode + ' '+ @DestBranchCode, @RetPumCode = 'PV'
RETURN -8
END
SET @AmountLCY = 0
IF @Amount > 0 BEGIN
/* Convert into LCY amount */
SELECT @AmountLCY = ROUND(@Amount * R.BuyRate, @MoneyRound), @LCY_Rate = R.BuyRate
FROM CurrRateTable R (NOLOCK), CurrencyTable C (NOLOCK)
WHERE R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = C.CyCode
AND C.ISOCodeNo = @CyCodeISO
IF ISNULL(@AmountLCY, 0) <= 0 BEGIN
SELECT @MsgStr = '(PDXBalnXfr) Rate is invalid : ' + ISNULL(@CyCodeISO, 'NULL'), @RetPumCode = 'IA'
RETURN -11 /* 0 amt can not be transferred */
END
END
/* process @CommAmt1, CommAmt2, @CommAmt3, @CommAmt4 and @CommAmt5 */
SELECT @CommAmtLCY = 0, @CommAmtLCY1 = 0, @CommAmtLCY2 = 0, @CommAmtLCY3 = 0, @CommAmtLCY4 = 0, @CommAmtLCY5 = 0, @CommAmt = 0
IF @CommAmt1 > 0 BEGIN
IF @CyCode1 IN ('524', 'NPR') BEGIN
SET @CommAmtLCY1 = @CommAmt1
END ELSE BEGIN
SELECT @CommAmtLCY1 = ISNULL(ROUND(@CommAmt1 * R.BuyRate, @MoneyRound), 0)
FROM CurrRateTable R (NOLOCK), CurrencyTable C (NOLOCK)
WHERE R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = C.CyCode
AND (CASE WHEN ISNUMERIC(@CyCode1) = 1 THEN C.ISOCodeNo ELSE C.CyDesc END) = @CyCode1
END
SET @CommAmtLCY = @CommAmtLCY1
IF @CommAmt2 > 0 BEGIN
IF @CyCode2 IN ('524', 'NPR') BEGIN
SET @CommAmtLCY2 = @CommAmt2
END ELSE BEGIN
SELECT @CommAmtLCY2 = ISNULL(ROUND(@CommAmt2 * R.BuyRate, @MoneyRound), 0)
FROM CurrRateTable R (NOLOCK), CurrencyTable C (NOLOCK)
WHERE R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = C.CyCode
AND (CASE WHEN ISNUMERIC(@CyCode2) = 1 THEN C.ISOCodeNo ELSE C.CyDesc END) = @CyCode2
END
SET @CommAmtLCY = @CommAmtLCY + @CommAmtLCY2
END
IF @CommAmt3 > 0 BEGIN
IF @CyCode3 IN ('524', 'NPR') BEGIN
SET @CommAmtLCY3 = @CommAmt3
END ELSE BEGIN
SELECT @CommAmtLCY3 = ISNULL(ROUND(@CommAmt3 * R.BuyRate, @MoneyRound), 0)
FROM CurrRateTable R (NOLOCK), CurrencyTable C (NOLOCK)
WHERE R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = C.CyCode
AND (CASE WHEN ISNUMERIC(@CyCode3) = 1 THEN C.ISOCodeNo ELSE C.CyDesc END) = @CyCode3
END
SET @CommAmtLCY = @CommAmtLCY + @CommAmtLCY3
END
IF @CommAmt4 > 0 BEGIN
IF @CyCode4 IN ('524', 'NPR') BEGIN
SET @CommAmtLCY4 = @CommAmt4
END ELSE BEGIN
SELECT @CommAmtLCY4 = ISNULL(ROUND(@CommAmt4 * R.BuyRate, @MoneyRound), 0)
FROM CurrRateTable R (NOLOCK), CurrencyTable C (NOLOCK)
WHERE R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = C.CyCode
AND (CASE WHEN ISNUMERIC(@CyCode4) = 1 THEN C.ISOCodeNo ELSE C.CyDesc END) = @CyCode4
END
SET @CommAmtLCY = @CommAmtLCY + @CommAmtLCY4
END
IF @CommAmt5 > 0 BEGIN
IF @CyCode5 IN ('524', 'NPR') BEGIN
SET @CommAmtLCY5 = @CommAmt5
END ELSE BEGIN
SELECT @CommAmtLCY5 = ISNULL(ROUND(@CommAmt5 * R.BuyRate, @MoneyRound), 0)
FROM CurrRateTable R (NOLOCK), CurrencyTable C (NOLOCK)
WHERE R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = C.CyCode
AND (CASE WHEN ISNUMERIC(@CyCode5) = 1 THEN C.ISOCodeNo ELSE C.CyDesc END) = @CyCode5
END
SET @CommAmtLCY = @CommAmtLCY + @CommAmtLCY5
END
SELECT @CommAmt = ROUND(@CommAmtLCY / @LCY_Rate, 2)
END -- IF @CommAmt1 > 0 BEGIN
SET @LoginInserted = 0
/* do not insert if end operation or day not started because in this condition
trans are written to Offline trans table (TransDailyOff) which fires no trigger */
IF (@EndDayOp = 'F') AND (@DayStarted = 'T') BEGIN
--IF NOT EXISTS (SELECT 1 FROM LoginTable (NOLOCK) WHERE HostProcess = HOST_ID() AND SPID = @@SPID) BEGIN
-- INSERT INTO LoginTable ( HostProcess, UserId, Today, LoginDate, StationCode, Branch, TranId, SPID )
-- VALUES ( HOST_ID(), @UserId, @Today, GETDATE(), @Station, @ThisBranch, @TranId, @@SPID )
-- SELECT @LoginInserted = 1
-- END
IF NOT EXISTS (SELECT 1 FROM LoginTable (NOLOCK) WHERE HostProcess = HOST_ID()) BEGIN
BEGIN TRY
INSERT INTO LoginTable ( HostProcess, UserId, Today, LoginDate, StationCode, Branch, TranId, SPID )
VALUES ( HOST_ID(), @UserId, @Today, GETDATE(), @Station, @SourceBranchCode, @TranId, @@SPID )
SET @LoginInserted = 1
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627 BEGIN -- IF NOT Violation of PRIMARY KEY return error else do not insert
SET @MsgStr = LEFT(ERROR_MESSAGE(), 255)
RETURN -444
END
END CATCH
END
END
/***** Transact on Source (User or Customer) ****************************************/
SET @AcType = NULL
IF @MessageCode = '0220' OR @MessageCode = '0221' BEGIN
/* if SIP then do not minus (IncludeMinBaln * MinBalnReqd) from GoodBaln */
SELECT @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = ISNULL(A.AcTypeType, 'C'),
@AvailBaln = CASE WHEN A.AcTypeType = 'F'
AND A.AcTypeSubType <> 'C' THEN GoodBaln
WHEN A.LimitType = '0'
OR DATEDIFF(Day, M.LimitExpiryDate, @Today) >= 0
THEN M.GoodBaln - M.HeldAmt
WHEN M.LimitType = '1' /* DR limit */
THEN M.Limit + M.GoodBaln - M.HeldAmt
ELSE /* '2' : CR limit */ M.Limit - M.GoodBaln - M.HeldAmt
END, @ConvertRate = R.BuyRate,
@GoodBaln = M.GoodBaln,
@CheckNum=M.CheckNum
FROM Master M ( NOLOCK ) ,
AcTypeTable A ( NOLOCK ) ,
ParaTable P ( NOLOCK ) ,
CurrRateTable R ( NOLOCK )
WHERE M.AcType = A.AcType
AND M.BranchCode = P.BranchCode
AND M.AcType = P.AcType
AND M.CyCode = P.CyCode
AND R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = M.CyCode
AND M.BranchCode = @SourceBranchCode
AND M.MainCode = @SourceMainCode
END ELSE BEGIN
SELECT @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = ISNULL(A.AcTypeType, 'C'),
@AvailBaln = dbo.f_GetAvailBaln(@SourceBranchCode, @SourceMainCode),
@ConvertRate = R.BuyRate, @GoodBaln = M.GoodBaln,
@CheckNum=M.CheckNum
FROM Master M ( NOLOCK ) ,
AcTypeTable A ( NOLOCK ) ,
ParaTable P ( NOLOCK ) ,
CurrRateTable R ( NOLOCK )
WHERE M.AcType = A.AcType
AND M.BranchCode = P.BranchCode
AND M.AcType = P.AcType
AND M.CyCode = P.CyCode
AND R.RateCode = @RateCode
AND R.CyCode1 = @LCYCode
AND R.CyCode2 = M.CyCode
AND M.BranchCode = @SourceBranchCode
AND M.MainCode = @SourceMainCode
END
IF @AcType IS NULL BEGIN
SELECT @MsgStr = 'Wrong source account :' + @SourceMainCode + '(' + @SourceBranchCode + ')'
SET @RetPumCode = 'AI'
RETURN -505
END
IF ISNULL(@AcTypeType, '') = 'C' AND ISNULL(@GoodBaln, 0) <> 0 BEGIN
IF dbo.f_CheckNum(CONVERT(BIGINT, FLOOR(ABS(@GoodBaln))) + dbo.f_GetAddNum(@SourceMainCode), 1) <>
CONVERT(INT, CHAR(DECRYPTBYPASSPHRASE(@SourceMainCode, @CheckNum))) BEGIN
SELECT @MsgStr = 'Balance seems to be wrong '
RETURN -506
END
END
SELECT @CyCodeSource = ISOCodeNo FROM CurrencyTable (NOLOCK) WHERE CyCode = @CyCode
IF NOT EXISTS (SELECT 1 FROM TranCodeTable (NOLOCK) WHERE TranCode = @TranCodeDr) BEGIN
SELECT @MsgStr = '(PDXBalnXfr) Tran Code Dr invalid : ' + ISNULL(@TranCodeDr, 'NULL'), @RetPumCode = 'EE'
RETURN -1
END
IF NOT EXISTS (SELECT 1 FROM TranCodeTable (NOLOCK) WHERE TranCode = @TranCodeCr) BEGIN
SELECT @MsgStr = '(PDXBalnXfr) Tran Code Cr invalid : ' + ISNULL(@TranCodeCr, 'NULL'), @RetPumCode = 'EE'
RETURN -1
END
SELECT @FCY_Amt = @Amount + @CommAmt, @LCY_Amt = @AmountLCY + @CommAmtLCY
IF @IsReversal <> 1 BEGIN /* not a reversal */
IF (@AvailBaln - (@Amount + @CommAmt) < 0) AND (@AcTypeType <> 'F') BEGIN
SELECT @MsgStr = '(PDXBalnXfr) Balance not sufficient : ' + @SourceMainCode, @RetPumCode = 'DR'
RETURN -200
END
EXECUTE @Result = s_Tran1Check @ThisBranch, @SourceMainCode, @UserId, @FCY_Amt, @LCY_Amt,
@TranCodeDr, NULL, 'T', 'T', 0, 'F', @WarnError OUTPUT, @StackError OUTPUT, @ForceWarning OUTPUT,
@WarnMesg OUTPUT, @MsgStr OUTPUT
IF @Result <> 0 BEGIN
RETURN @Result
END
INSERT INTO @InMemTrans(
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount, TranId,
TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2, Desc3, ApprovedBy,
EnteredBy, RateCode, AcTypeType, ReferenceNo )
VALUES (
@TranCodeDr, @SourceMainCode, @SourceBranchCode, @AcType, @CyCode, -@FCY_Amt, -@LCY_Amt,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2,
ISNULL(@Desc3, 'to ' + @DestMainCode), @UserId, @UserId, @RateCode, @AcTypeType,
@RetrievalRef)
END ELSE BEGIN
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount, TranId,
TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2, Desc3, ApprovedBy,
EnteredBy, RateCode, AcTypeType, ReferenceNo )
VALUES (
@TranCodeCr, @SourceMainCode, @SourceBranchCode, @AcType, @CyCode, @FCY_Amt, @LCY_Amt,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2,
ISNULL(@Desc3, 'from ' + @DestMainCode), @UserId, @UserId, @RateCode, @AcTypeType,
@RetrievalRef )
END
/************************** Credit Commissions ******************************/
IF @CommAmt1 > 0 BEGIN
IF NOT EXISTS (SELECT 1 FROM TranCodeTable (NOLOCK) WHERE TranCode = @CommTranCodeDr) BEGIN
SELECT @MsgStr = 'Comm Tran Code Dr invalid : ' + ISNULL(@CommTranCodeDr, 'NULL'), @RetPumCode = 'EE'
RETURN -1
END
IF NOT EXISTS (SELECT 1 FROM TranCodeTable (NOLOCK) WHERE TranCode = @CommTranCodeCr) BEGIN
SELECT @MsgStr = 'Comm Tran Code Cr invalid : ' + ISNULL(@CommTranCodeCr, 'NULL'), @RetPumCode = 'EE'
RETURN -1
END
SET @AcType = NULL
IF NOT EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm1 AND MainCode = @CommAcc1) BEGIN
SELECT @MsgStr = 'Comm A/c invalid : ' + ISNULL(@CommAcc1, 'NULL'), @RetPumCode='AI'
RETURN -101
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm1 AND MainCode = @CommAcc1 AND ISNULL(IsBlocked, '') = 'C') BEGIN
SELECT @MsgStr = 'A/c is closed : ' + @CommAcc1, @RetPumCode='AC'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm1 AND MainCode = @CommAcc1 AND ISNULL(IsBlocked, '') IN ('T', 'B')) BEGIN
SELECT @MsgStr = 'A/c is blocked : ' + @CommAcc1, @RetPumCode='AB'
RETURN -81
END
SELECT @BranchCode = M.BranchCode, @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = A.AcTypeType
FROM Master M (NOLOCK), AcTypeTable A (NOLOCK)
WHERE M.AcType = A.AcType
AND M.BranchCode = @BrCodeComm1
AND M.MainCode = @CommAcc1
IF (@AcType IS NULL) OR (@AcTypeType = 'O') BEGIN
SELECT @MsgStr = 'Wrong commision account:' + @CommAcc1 + ' (' + @BrCodeComm1 + ')', @RetPumCode = 'AI'
RETURN -505
END
IF @CyCode = @LCYCode BEGIN
SELECT @LCY_Rate = 1
END ELSE BEGIN
SELECT @Reval_A = Reval_A, @Reval_B = Reval_B
FROM ControlTable (NOLOCK)
SELECT @LCY_Rate = ROUND(@Reval_A * BuyRate, @MoneyRound) + ROUND(@Reval_B * SellRate, @MoneyRound)
FROM CurrRateTable (NOLOCK)
WHERE RateCode = @RateCode
AND CyCode1 = @LCYCode
AND CyCode2 = @CyCode
END
SELECT @CommAmt1 = ROUND(@CommAmtLCY1 / @LCY_Rate, 2)
SELECT @tABBSCode = NULL, @tNostroBranch = NULL
IF @IsABBS = 1 BEGIN
IF @BrCodeComm1 <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @BrCodeComm1
IF @DestBranchCode <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @DestBranchCode
END
IF @IsReversal <> 1 BEGIN /* not a reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeCr, @CommAcc1, @BrCodeComm1, @AcType, @CyCode, @CommAmt1, @CommAmtLCY1,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3, @UserId,
@UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END ELSE BEGIN /* reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeDr, @CommAcc1, @BrCodeComm1, @AcType, @CyCode, -@CommAmt1,
-@CommAmtLCY1, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3,
@UserId, @UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END
IF @CommAmt2 > 0 BEGIN
SET @AcType = NULL
IF NOT EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm2 AND MainCode = @CommAcc2) BEGIN
SELECT @MsgStr = 'Comm A/c invalid : ' + ISNULL(@CommAcc2, 'NULL'), @RetPumCode='AI'
RETURN -101
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm2 AND MainCode = @CommAcc2 AND ISNULL(IsBlocked, '') = 'C') BEGIN
SELECT @MsgStr = 'A/c is closed : ' + @CommAcc2, @RetPumCode='AC'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm2 AND MainCode = @CommAcc2 AND ISNULL(IsBlocked, '') IN ('T', 'B')) BEGIN
SELECT @MsgStr = 'A/c is blocked : ' + @CommAcc2, @RetPumCode='AB'
RETURN -81
END
SELECT @BranchCode = M.BranchCode, @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = A.AcTypeType
FROM Master M (NOLOCK), AcTypeTable A (NOLOCK)
WHERE M.AcType = A.AcType
AND M.BranchCode = @BrCodeComm2
AND M.MainCode = @CommAcc2
IF (@AcType IS NULL) OR (@AcTypeType = 'O') BEGIN
SELECT @MsgStr = 'Wrong commision account:' + @CommAcc2 + ' (' + @BrCodeComm2 + ')', @RetPumCode = 'AI'
RETURN -505
END
IF @CyCode = @LCYCode BEGIN
SELECT @LCY_Rate = 1
END ELSE BEGIN
SELECT @Reval_A = Reval_A, @Reval_B = Reval_B
FROM ControlTable (NOLOCK)
SELECT @LCY_Rate = ROUND(@Reval_A * BuyRate, @MoneyRound) + ROUND(@Reval_B * SellRate, @MoneyRound)
FROM CurrRateTable (NOLOCK)
WHERE RateCode = @RateCode
AND CyCode1 = @LCYCode
AND CyCode2 = @CyCode
END
SELECT @CommAmt2 = ROUND(@CommAmtLCY2 / @LCY_Rate, 2)
SELECT @tABBSCode = NULL, @tNostroBranch = NULL
IF @IsABBS = 1 BEGIN
IF @BrCodeComm2 <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @BrCodeComm2
IF @DestBranchCode <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @DestBranchCode
END
IF @IsReversal <> 1 BEGIN /* not a reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeCr, @CommAcc2, @BrCodeComm2, @AcType, @CyCode, @CommAmt2, @CommAmtLCY2,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3, @UserId,
@UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END ELSE BEGIN /* reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeDr, @CommAcc2, @BrCodeComm2, @AcType, @CyCode, -@CommAmt2,
-@CommAmtLCY2, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3,
@UserId, @UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END
END -- IF @CommAmt2 > 0 BEGIN
IF @CommAmt3 > 0 BEGIN
SET @AcType = NULL
IF NOT EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm3 AND MainCode = @CommAcc3) BEGIN
SELECT @MsgStr = 'Comm A/c invalid : ' + ISNULL(@CommAcc3, 'NULL'), @RetPumCode='AI'
RETURN -101
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm3 AND MainCode = @CommAcc3 AND ISNULL(IsBlocked, '') = 'C') BEGIN
SELECT @MsgStr = 'A/c is closed : ' + @CommAcc3, @RetPumCode='AC'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm3 AND MainCode = @CommAcc3 AND ISNULL(IsBlocked, '') IN ('T', 'B')) BEGIN
SELECT @MsgStr = 'A/c is blocked : ' + @CommAcc3, @RetPumCode='AB'
RETURN -81
END
SELECT @BranchCode = M.BranchCode, @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = A.AcTypeType
FROM Master M (NOLOCK), AcTypeTable A (NOLOCK)
WHERE M.AcType = A.AcType
AND M.BranchCode = @BrCodeComm3
AND M.MainCode = @CommAcc3
IF (@AcType IS NULL) OR (@AcTypeType = 'O') BEGIN
SELECT @MsgStr = 'Wrong commision account:' + @CommAcc3 + ' (' + @BrCodeComm3 + ')', @RetPumCode = 'AI'
RETURN -505
END
IF @CyCode = @LCYCode BEGIN
SELECT @LCY_Rate = 1
END ELSE BEGIN
SELECT @Reval_A = Reval_A, @Reval_B = Reval_B
FROM ControlTable (NOLOCK)
SELECT @LCY_Rate = ROUND(@Reval_A * BuyRate, @MoneyRound) + ROUND(@Reval_B * SellRate, @MoneyRound)
FROM CurrRateTable (NOLOCK)
WHERE RateCode = @RateCode
AND CyCode1 = @LCYCode
AND CyCode2 = @CyCode
END
SELECT @CommAmt3 = ROUND(@CommAmtLCY3 / @LCY_Rate, 2)
SELECT @tABBSCode = NULL, @tNostroBranch = NULL
IF @IsABBS = 1 BEGIN
IF @BrCodeComm3 <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @BrCodeComm3
IF @DestBranchCode <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @DestBranchCode
END
IF @IsReversal <> 1 BEGIN /* not a reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeCr, @CommAcc3, @BrCodeComm3, @AcType, @CyCode, @CommAmt3, @CommAmtLCY3,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3, @UserId,
@UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END ELSE BEGIN /* reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeDr, @CommAcc3, @BrCodeComm3, @AcType, @CyCode, -@CommAmt3,
-@CommAmtLCY3, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3,
@UserId, @UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END
END -- IF @CommAmt3 > 0 BEGIN
IF @CommAmt4 > 0 BEGIN
SET @AcType = NULL
IF NOT EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm4 AND MainCode = @CommAcc4) BEGIN
SELECT @MsgStr = 'Comm A/c invalid : ' + ISNULL(@CommAcc4, 'NULL'), @RetPumCode='AI'
RETURN -101
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm4 AND MainCode = @CommAcc4 AND ISNULL(IsBlocked, '') = 'C') BEGIN
SELECT @MsgStr = 'A/c is closed : ' + @CommAcc4, @RetPumCode='AC'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm4 AND MainCode = @CommAcc4 AND ISNULL(IsBlocked, '') IN ('T', 'B')) BEGIN
SELECT @MsgStr = 'A/c is blocked : ' + @CommAcc4, @RetPumCode='AB'
RETURN -81
END
SELECT @BranchCode = M.BranchCode, @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = A.AcTypeType
FROM Master M (NOLOCK), AcTypeTable A (NOLOCK)
WHERE M.AcType = A.AcType
AND M.BranchCode = @BrCodeComm4
AND M.MainCode = @CommAcc4
IF (@AcType IS NULL) OR (@AcTypeType = 'O') BEGIN
SELECT @MsgStr = 'Wrong commision account:' + @CommAcc4 + ' (' + @BrCodeComm4 + ')', @RetPumCode = 'AI'
RETURN -505
END
IF @CyCode = @LCYCode BEGIN
SELECT @LCY_Rate = 1
END ELSE BEGIN
SELECT @Reval_A = Reval_A, @Reval_B = Reval_B
FROM ControlTable (NOLOCK)
SELECT @LCY_Rate = ROUND(@Reval_A * BuyRate, @MoneyRound) + ROUND(@Reval_B * SellRate, @MoneyRound)
FROM CurrRateTable (NOLOCK)
WHERE RateCode = @RateCode
AND CyCode1 = @LCYCode
AND CyCode2 = @CyCode
END
SELECT @CommAmt4 = ROUND(@CommAmtLCY4 / @LCY_Rate, 2)
SELECT @tABBSCode = NULL, @tNostroBranch = NULL
IF @IsABBS = 1 BEGIN
IF @BrCodeComm4 <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @BrCodeComm4
IF @DestBranchCode <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @DestBranchCode
END
IF @IsReversal <> 1 BEGIN /* not a reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeCr, @CommAcc4, @BrCodeComm4, @AcType, @CyCode, @CommAmt4, @CommAmtLCY4,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3, @UserId,
@UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END ELSE BEGIN /* reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeDr, @CommAcc4, @BrCodeComm4, @AcType, @CyCode, -@CommAmt4,
-@CommAmtLCY4, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3,
@UserId, @UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END
END -- IF @CommAmt4 > 0 BEGIN
IF @CommAmt5 > 0 BEGIN
SET @AcType = NULL
IF NOT EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm5 AND MainCode = @CommAcc5) BEGIN
SELECT @MsgStr = 'Comm A/c invalid : ' + ISNULL(@CommAcc5, 'NULL'), @RetPumCode='AI'
RETURN -101
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm5 AND MainCode = @CommAcc5 AND ISNULL(IsBlocked, '') = 'C') BEGIN
SELECT @MsgStr = 'A/c is closed : ' + @CommAcc5, @RetPumCode='AC'
RETURN -8
END
IF EXISTS (SELECT 1 FROM Master (NOLOCK) WHERE BranchCode = @BrCodeComm5 AND MainCode = @CommAcc5 AND ISNULL(IsBlocked, '') IN ('T', 'B')) BEGIN
SELECT @MsgStr = 'A/c is blocked : ' + @CommAcc5, @RetPumCode='AB'
RETURN -81
END
SELECT @BranchCode = M.BranchCode, @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = A.AcTypeType
FROM Master M (NOLOCK), AcTypeTable A (NOLOCK)
WHERE M.AcType = A.AcType
AND M.BranchCode = @BrCodeComm5
AND M.MainCode = @CommAcc5
IF (@AcType IS NULL) OR (@AcTypeType = 'O') BEGIN
SELECT @MsgStr = 'Wrong commision account:' + @CommAcc5 + ' (' + @BrCodeComm5 + ')', @RetPumCode = 'AI'
RETURN -505
END
IF @CyCode = @LCYCode BEGIN
SELECT @LCY_Rate = 1
END ELSE BEGIN
SELECT @Reval_A = Reval_A, @Reval_B = Reval_B
FROM ControlTable (NOLOCK)
SELECT @LCY_Rate = ROUND(@Reval_A * BuyRate, @MoneyRound) + ROUND(@Reval_B * SellRate, @MoneyRound)
FROM CurrRateTable (NOLOCK)
WHERE RateCode = @RateCode
AND CyCode1 = @LCYCode
AND CyCode2 = @CyCode
END
SELECT @CommAmt5 = ROUND(@CommAmtLCY5 / @LCY_Rate, 2)
SELECT @tABBSCode = NULL, @tNostroBranch = NULL
IF @IsABBS = 1 BEGIN
IF @BrCodeComm4 <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @BrCodeComm4
IF @DestBranchCode <> @ThisBranch
SELECT @tABBSCode = 'S', @tNostroBranch = @DestBranchCode
END
IF @IsReversal <> 1 BEGIN /* not a reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeCr, @CommAcc5, @BrCodeComm5, @AcType, @CyCode, @CommAmt5, @CommAmtLCY5,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3, @UserId,
@UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END ELSE BEGIN /* reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@CommTranCodeDr, @CommAcc5, @BrCodeComm5, @AcType, @CyCode, -@CommAmt5,
-@CommAmtLCY5, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2, @Desc3,
@UserId, @UserId, @RateCode, @AcTypeType, @tNostroBranch, @RetrievalRef )
END
END -- IF @CommAmt5 > 0 BEGIN
END -- IF @CommAmt1 > 0 BEGIN
/***** End Commission **********************************/
IF @Amount > 0 BEGIN
/******************** Transact for Destination A/c ***************************/
SELECT @AcType = NULL
SELECT @AcType = M.AcType, @CyCode = M.CyCode, @AcTypeType = A.AcTypeType
FROM Master M (NOLOCK), AcTypeTable A (NOLOCK)
WHERE M.AcType = A.AcType
AND M.BranchCode = @DestBranchCode
AND M.MainCode = @DestMainCode
IF @AcType IS NULL BEGIN
SELECT @MsgStr = 'Wrong destination account:' + @DestMainCode + '(' + @DestBranchCode + ')', @RetPumCode = 'AI'
RETURN -505
END
SELECT @CyCodeDest = ISOCodeNo FROM CurrencyTable (NOLOCK) WHERE CyCode = @CyCode
/* check for source and destination currency code */
IF (@CyCodeSource = '524') AND (@CyCodeSource <> @CyCodeDest) BEGIN
SELECT @MsgStr = 'Cannot Xfr from LCY to foreign currency !', @RetPumCode = 'AI'
RETURN -666
END ELSE BEGIN
IF (@CyCodeSource <> @CyCodeDest) AND (@CyCodeDest <> '524') BEGIN
SELECT @MsgStr = 'Cannot Xfr from one FCY to another FCY !', @RetPumCode = 'AI'
RETURN -777
END
END
IF @CyCode = @LCYCode BEGIN
SELECT @LCY_Rate = 1
END ELSE BEGIN
SELECT @Reval_A = Reval_A, @Reval_B = Reval_B
FROM ControlTable (NOLOCK)
-- SELECT @LCY_Rate = ROUND(@Reval_A * BuyRate, @MoneyRound) + ROUND(@Reval_B * SellRate, @MoneyRound)
SELECT @LCY_Rate = BuyRate
FROM CurrRateTable (NOLOCK)
WHERE RateCode = @RateCode
AND CyCode1 = @LCYCode
AND CyCode2 = @CyCode
END
SELECT @LCY_Amt = @AmountLCY
SELECT @FCY_Amt = ROUND(@LCY_Amt / @LCY_Rate, 2)
SELECT @tABBSCode = NULL, @tNostroBranch = NULL
IF @IsABBS = 1 BEGIN
IF @SourceBranchCode <> @DestBranchCode
SELECT @tABBSCode = 'S', @tNostroBranch = @DestBranchCode
ELSE
SELECT @tABBSCode = 'S', @tNostroBranch = @SourceBranchCode
END
IF @IsReversal <> 1 BEGIN /* not a reversal */
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@TranCodeCr, @DestMainCode, @DestBranchCode, @AcType, @CyCode, @FCY_Amt, @LCY_Amt,
@TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2,
ISNULL(@Desc3, 'from ' + @SourceMainCode), @UserId, @UserId, @RateCode,
@AcTypeType, @tNostroBranch, @RetrievalRef )
END ELSE BEGIN
INSERT INTO @InMemTrans (
TranCode, MainCode, BranchCode, AcType, CyCode, Amount, LCYAmount,
TranId, TranCyCode, UserId, TranDate, ValueDate, Desc1, Desc2,
Desc3, ApprovedBy, EnteredBy, RateCode, AcTypeType, NostroBranch,
ReferenceNo )
VALUES (
@TranCodeDr, @DestMainCode, @DestBranchCode, @AcType, @CyCode, -@FCY_Amt,
-@LCY_Amt, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2,
ISNULL(@Desc3, 'to ' + @SourceMainCode), @UserId, @UserId, @RateCode, @AcTypeType,
@tNostroBranch, @RetrievalRef )
END
END --IF @Amount > 0 BEGIN
IF @IsABBS = 1 BEGIN
/***************** if ABBS Transaction add IBT Trans. *********************/
DECLARE
@Branch VARCHAR(3),
@OfBranch VARCHAR(3),
@MainCodeRM u_MainCodeLen,
@MainCodeTB u_MainCodeLen,
@RemoteAcType VARCHAR(2)
DECLARE @temp TABLE (
[SeqNo] [int] IDENTITY(1,1) NOT NULL,
[BranchCode] [varchar](3) NULL,
[TotAmount] [money] NULL)
INSERT INTO @temp (BranchCode, TotAmount)
SELECT BranchCode, SUM(LCYAmount)
FROM @InMemTrans
WHERE BranchCode <> @ThisBranch
GROUP BY BranchCode
DELETE FROM @temp WHERE ISNULL(TotAmount,0) = 0
SELECT @RecordCount = 1, @TotRecord = MAX(SeqNo) FROM @temp
WHILE @RecordCount <= @TotRecord BEGIN
SELECT @Branch = BranchCode FROM @temp WHERE SeqNo = @RecordCount
SELECT @MainCodeRM = A.MainCodeRM, @MainCodeTB = A.MainCodeTB, @RemoteAcType = A.RemoteAcType
FROM ABBSBranchTable A (NOLOCK), @InMemTrans T
WHERE T.BranchCode = A.BranchCode
AND T.CyCode = A.CyCode
AND T.BranchCode = @Branch
IF (@MainCodeRM IS NULL) OR (@MainCodeTB IS NULL) OR (@RemoteAcType IS NULL) BEGIN
SELECT @MsgStr = 'ABBSBrTable of branch : ' + ISNULL(@ThisBranch, 'NULL') +
' does not have row of branch : ' + ISNULL(@Branch, 'NULL')
RETURN -1
END
SET @RecordCount += 1
END -- WHILE @RecordCount < @TotRecord BEGIN
/******* Add notro entries for Dr Trans *****/
/* This Branch */
INSERT INTO @InMemTrans (
BranchCode, AcType, CyCode, MainCode, Name, TranCode, Amount, LCYAmount,
Desc1, Desc2, Desc3, ValueDate, TranCyCode,
ApprovedBy, EnteredBy, Status, Notice,
RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, AcTypeType, CityCode, ExtraChqNo, TranId, UserId,
ABBSCode, NostroBranch, CustCode, SubCustCode)
SELECT @ThisBranch, M.AcType, T.CyCode, A.MainCodeRM, M.Name, A.TranCodeDr, Amount, LCYAmount,
Desc1, Desc2, Desc3, @Today, TranCyCode,
ApprovedBy, EnteredBy, Status, '*',
RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, A1.AcTypeType, CityCode, ExtraChqNo, T.TranId, T.UserId,
'S' /* Source ABBS Code */, A.BranchCode, /* Nostro Branch */
T.CustCode, T.SubCustCode
FROM ABBSBranchTable A (NOLOCK), @InMemTrans T, Master M (NOLOCK), @temp X, AcTypeTable A1 (NOLOCK)
WHERE T.BranchCode = X.BranchCode
AND T.BranchCode = A.BranchCode
AND T.CyCode = A.CyCode
AND T.LCYAmount < 0
AND M.MainCode = A.MainCodeRM
AND M.BranchCode = @ThisBranch
AND M.AcType = A1.AcType
UNION ALL
/* Remote Branch */
SELECT T.BranchCode, A.RemoteAcType, T.CyCode, A.MainCodeTB, A.RemoteAcName, A.TranCodeCr, -Amount, -LCYAmount,
Desc1, Desc2, Desc3, @Today, TranCyCode,
ApprovedBy, EnteredBy, Status, '*',
RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, A1.AcTypeType, CityCode, ExtraChqNo, T.TranId, T.UserId,
'D', @ThisBranch, T.CustCode, T.SubCustCode
FROM ABBSBranchTable A (NOLOCK), @InMemTrans T, @temp X, AcTypeTable A1 (NOLOCK)
WHERE T.BranchCode = X.BranchCode
AND T.BranchCode = A.BranchCode
AND T.CyCode = A.CyCode
AND T.LCYAmount < 0
AND A.RemoteAcType = A1.AcType
/******* Add notro entries for Cr Trans *****/
UNION ALL
/* This Branch */
SELECT @ThisBranch, M.AcType, T.CyCode, A.MainCodeRM, M.Name, A.TranCodeCr, Amount, LCYAmount,
Desc1, Desc2, Desc3, @Today, TranCyCode,
ApprovedBy, EnteredBy, Status, '*',
RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, A1.AcTypeType, CityCode, ExtraChqNo, T.TranId, T.UserId,
'S' /* Source ABBS Code */, A.BranchCode, /* Nostro Branch */
T.CustCode, T.SubCustCode
FROM ABBSBranchTable A (NOLOCK), @InMemTrans T, Master M (NOLOCK), @temp X, AcTypeTable A1 (NOLOCK)
WHERE T.BranchCode = X.BranchCode
AND T.BranchCode = A.BranchCode
AND T.CyCode = A.CyCode
AND T.LCYAmount > 0
AND ISNULL(T.ABBSCode, '') = ''
AND M.MainCode = A.MainCodeRM
AND M.BranchCode = @ThisBranch
AND M.AcType = A1.AcType
UNION ALL
/* Add notro entries for Dr Trans : Remote Branch */
SELECT T.BranchCode, A.RemoteAcType, T.CyCode, A.MainCodeTB, A.RemoteAcName, A.TranCodeDr, -Amount, -LCYAmount,
Desc1, Desc2, Desc3, @Today, TranCyCode,
ApprovedBy, EnteredBy, Status, '*',
RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, A1.AcTypeType, CityCode, ExtraChqNo, T.TranId, T.UserId,
'D' /* Dest ABBS Code */, @ThisBranch, /* Nostro Branch */
T.CustCode, T.SubCustCode
FROM ABBSBranchTable A (NOLOCK), @InMemTrans T, @temp X, AcTypeTable A1 (NOLOCK)
WHERE T.BranchCode = X.BranchCode
AND T.BranchCode = A.BranchCode
AND T.CyCode = A.CyCode
AND T.LCYAmount > 0
AND ISNULL(T.ABBSCode, '') = ''
AND A.RemoteAcType = A1.AcType
SELECT @CrTotBS = 0, @DrTotBS = 0, @CrTotOB = 0, @DrTotOB = 0
SELECT @CrTotBS = SUM(CASE WHEN LCYAmount > 0 AND AcTypeType <> 'O' THEN LCYAmount ELSE 0 END),
@DrTotBS = SUM(CASE WHEN LCYAmount < 0 AND AcTypeType <> 'O' THEN LCYAmount ELSE 0 END),
@CrTotOB = SUM(CASE WHEN LCYAmount > 0 AND AcTypeType = 'O' THEN LCYAmount ELSE 0 END),
@DrTotOB = SUM(CASE WHEN LCYAmount < 0 AND AcTypeType = 'O' THEN LCYAmount ELSE 0 END)
FROM @InMemTrans
WHERE BranchCode <> @ThisBranch
IF ISNULL(@CrTotBS, 0) <> -ISNULL(@DrTotBS, 0) OR ISNULL(@CrTotOB, 0) <> -ISNULL(@DrTotOB, 0) BEGIN
SELECT @MsgStr = 'Total Dr/Cr do not match in remote branch'
RETURN -1
END
UPDATE @InMemTrans SET NostroBranch = @ThisBranch, ABBSCode = 'D' /* Destination */
WHERE BranchCode <> @ThisBranch
AND (ISNULL(NostroBranch, '') = '' OR ISNULL(ABBSCode, '') = '')
UPDATE @InMemTrans SET NostroBranch = @Branch, ABBSCode = 'S' /* Source */
WHERE BranchCode = @ThisBranch
AND (ISNULL(NostroBranch, '') = '' OR ISNULL(ABBSCode, '') = '')
END -- IF @IsABBS = 1 BEGIN
SELECT @CrTotBS = 0, @DrTotBS = 0, @CrTotOB = 0, @DrTotOB = 0
SELECT @CrTotBS = SUM(CASE WHEN LCYAmount > 0 AND AcTypeType <> 'O' THEN LCYAmount ELSE 0 END),
@DrTotBS = SUM(CASE WHEN LCYAmount < 0 AND AcTypeType <> 'O' THEN LCYAmount ELSE 0 END),
@CrTotOB = SUM(CASE WHEN LCYAmount > 0 AND AcTypeType = 'O' THEN LCYAmount ELSE 0 END),
@DrTotOB = SUM(CASE WHEN LCYAmount < 0 AND AcTypeType = 'O' THEN LCYAmount ELSE 0 END)
FROM @InMemTrans
WHERE BranchCode = @ThisBranch
IF ISNULL(@CrTotBS, 0) <> -ISNULL(@DrTotBS, 0) OR ISNULL(@CrTotOB, 0) <> -ISNULL(@DrTotOB, 0) BEGIN
SELECT @MsgStr = 'Total Dr/Cr do not match'
RETURN -1
END
DECLARE @EachTran TABLE (
[RecNo] [int] IDENTITY(1,1) NOT NULL,
[SeqNo] [int] NULL)
INSERT INTO @EachTran (SeqNo)
SELECT SeqNo FROM @InMemTrans ORDER BY SeqNo
SELECT @RecordCount = 1, @TotRecord = MAX(RecNo) FROM @EachTran
BEGIN TRY
BEGIN TRANSACTION BalnXfrWithComm
WHILE @RecordCount <= @TotRecord BEGIN
SELECT @SeqNo = SeqNo FROM @EachTran WHERE RecNo = @RecordCount
SELECT @BranchCode = BranchCode, @AcType = AcType, @CyCode = CyCode, @MainCode = MainCode,
@TranCode = TranCode, @AcTypeType = AcTypeType, @Desc1 = Desc1, @Desc2 = Desc2,
@Desc3 = Desc3, @ABBSCode = ABBSCode, @NostroBranch = NostroBranch, @Notice = Notice,
@RateCode = RateCode, @BankCode = BankCode, @ChequeNo = ChequeNo, @Amount = Amount,
@LCYAmount = LCYAmount, @ExtraChqNo = ExtraChqNo, @TranCyCode = TranCyCode, @EnteredBy = EnteredBy,
@ApprovedBy = ISNULL(ApprovedBy, EnteredBy), @CustCode = CustCode, @SubCustCode = SubCustCode
FROM @InMemTrans
WHERE SeqNo = @SeqNo
IF (@EndDayOp = 'T') OR (@DayStarted = 'F') BEGIN
INSERT INTO TransDailyOff
(BranchCode, AcType, CyCode, MainCode, TranCode, AcTypeType,
Desc1, Desc2, Desc3, ABBSCode, NostroBranch,
Notice, RateCode, ReferenceNo, BankCode, ChequeNo,
Amount, LCYAmount, ExtraChqNo, TranCyCode, EnteredBy, ApprovedBy, TranId,
TranDate, ValueDate, CustCode, SubCustCode)
SELECT @BranchCode, @AcType, @CyCode, @MainCode, @TranCode, @AcTypeType,
@Desc1, @Desc2, @Desc3, @ABBSCode, @NostroBranch,
@Notice, @RateCode, @RetrievalRef, @BankCode, @ChequeNo,
@Amount, @LCYAmount, @ExtraChqNo, @TranCyCode, @EnteredBy, @ApprovedBy, @TranId,
@Today, @Today, @CustCode, @SubCustCode
END ELSE BEGIN
INSERT INTO TransDaily
(BranchCode, AcType, CyCode, MainCode, TranCode, AcTypeType,
Desc1, Desc2, Desc3, ABBSCode, NostroBranch,
Notice, RateCode, ReferenceNo, BankCode, ChequeNo,
Amount, LCYAmount, ExtraChqNo, TranCyCode, EnteredBy, ApprovedBy, TranId,
TranDate, ValueDate, CustCode, SubCustCode)
SELECT @BranchCode, @AcType, @CyCode, @MainCode, @TranCode, @AcTypeType,
@Desc1, @Desc2, @Desc3, @ABBSCode, @NostroBranch,
@Notice, @RateCode, @RetrievalRef, @BankCode, @ChequeNo,
@Amount, @LCYAmount, @ExtraChqNo, @TranCyCode, @EnteredBy, @ApprovedBy, @TranId,
@Today, @Today, @CustCode, @SubCustCode
END
SET @RecordCount += 1
END -- WHILE @RecordCount < @TotRecord BEGIN
IF EXISTS(SELECT 1 FROM ConstantTable (NOLOCK)
WHERE ISNULL(CCywiseGL, 'F') = 'T') BEGIN
EXEC s_MakeCyWiseTrans @TranId
END
SELECT @MsgStr = 'Transaction Successful'
IF @LoginInserted = 1 BEGIN /* Login Table was inserted from here. Delete it */
DELETE FROM LoginTable WITH (READPAST) WHERE HostProcess = HOST_ID() AND SPID = @@SPID
END
COMMIT TRANSACTION BalnXfrWithComm
RETURN @Result
END TRY
BEGIN CATCH
--CLOSE EachTran
--DEALLOCATE EachTran
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION BalnXfrWithComm
IF @LoginInserted = 1 BEGIN /* Login Table was inserted from here. Delete it */
DELETE FROM LoginTable WITH (READPAST)
WHERE HostProcess = HOST_ID() AND SPID = @@SPID
END
SET @MsgStr = ERROR_MESSAGE()
RETURN -998
END CATCH
Comments
Post a Comment