Extraction part 1 - SP
create sp
@ parameters varchar(3)
@parameters float = 0,
@parameters varchar(3)= Null,
@parameter(tranid) char(12)= null output,
@parameter TinyInt =0,
@parameter
User_Defined_Datatype,
@parameter Char,
@parameter money output
with encryption
as --no begin here but usually
people tends to use
Declare
@variable varhar(1).
@variable Tinyint, -1 byte integer (range is 0-255)
@variable smallint,-2 byte integer (-32768 to 32768) --other remainting type of int are big int and int see separate blog for it
@variable int,
@variable float,
@variable Money,
@variable DateTime,
@variable User_Defined_DataType,
SET ANSI_WARNINGS OFF
SET XACT_ABORT ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET @VARIABLE ='EE' //SYSTEM ERROR 96
IF @VARIABLE <=0 BEGIN
SELECT @variable ='error
message',@variable='IA' --Invalid amount(format error)
return -101
end
select @variable= tablecolumnvalue ,
@variable = ISNULL(StartDayOp,'F') from blablaTable (NOlock)
set @variable = 0 /*false*/
If (@parameter<> @parameter)
select @variable = 1--true
IF (@parameter> 0) AND (@parameter<>
ISNULL(@BranchCodeTermCharge, @SourceBranchCode))
SELECT @IsABBS = 1 /* True */
CASE WHEN SUM(Amount) > 0 THEN (SELECT MAX(TranCode) FROM InMemTransPDX (NOLOCK)
WHERE TranId = @TranId
AND UserId = @UserId
AND BranchCode = I.BranchCode
AND MainCode = I.MainCode
AND Amount > 0)
IF ISNULL(@UserId,'') = '' BEGIN /* No
Host_Id(). Not Called from Pum*/
SELECT @UserId = CDefaultUser, @StationCode =
CDefaultStation FROM ConstantTable (NOLOCK)
END
IF NOT EXISTS(SELECT 1 FROM Master (NOLOCK)
WHERE MainCode = @SourceMainCode AND BranchCode = @SourceBranchCode)
BEGIN
SELECT @MsgStr = '(PDXBalnXfr) Source
MainCode Invalid : ' + @SourceMainCode + ' ' + @SourceBranchCode, @RetPumCode =
'AI'
RETURN -105
END
IF EXISTS(SELECT 1 FROM Master (NOLOCK) WHERE
MainCode = @SourceMainCode AND BranchCode = @SourceBranchCode
AND
ISNULL(IsBlocked,'') = 'C') BEGIN
SELECT @MsgStr = '(PDXBalnXfr) A/c is
Closed : ' + @SourceMainCode + ' ' + @SourceBranchCode, @RetPumCode =
'AI'
RETURN -106
END
same pattern selecting if, if not exit, if
exitsts, and then selecting, return 101 bla bla.. point to note is validating
at the begining is done .. balance not sufficient, account wrong and bla bla
bla ..
SELECT @AmountLCY = ROUND(@Amount *
R.SellRate, @MoneyRound), @LCY_Rate = R.SellRate
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 RIGHT(@Desc3,5) ='BIChg' and @CyCodeISO =
'356' BEGIN
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
END
IF @TermType = 'P' BEGIN /*not this banks
acquired terminal */
IF LTRIM(RTRIM(@AquirerCode)) <>
@BinNo BEGIN
SELECT @BankAquirerComm = 0
END
IF @MessageCode = '0220' BEGIN
SELECT @BankCommAmtLCY =
ROUND((@AmountLCY * @BankAquirerComm) / 100, 2)
SELECT @OtherCommAmtLCY =
ROUND((@AmountLCY * @OtherComm) / 100, 2)
END ELSE BEGIN
SELECT @BankIssuerComm = @BankIssuerComm
+ @BankAquirerComm
SELECT @BankCommAmtLCY =
ROUND((@AmountLCY * @BankIssuerComm) / 100, 2)
SELECT @OtherCommAmtLCY =
ROUND((@AmountLCY * @OtherComm) / 100, 2)
END
/*Make Tran Id*/
SELECT @ThisTime = CONVERT(CHAR(12), GETDATE(),
114)
SELECT @TranId = @StationCode +
SUBSTRING(@ThisTime, 1, 2) + SUBSTRING(@ThisTime, 4, 2) + SUBSTRING(@ThisTime,
7, 2) + SUBSTRING(@ThisTime, 10, 3)
BEGIN TRANSACTION BalnXfrWithComm
SELECT @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 (@StartDayOp = 'F') AND (@EndDayOp = 'F')
AND (@DayStarted = 'T') BEGIN
IF NOT EXISTS(SELECT 1 FROM LoginTable
(NOLOCK)
WHERE HostProcess =
HOST_ID()
AND SPID = @@SPID)
BEGIN
EXEC s_InsertLoginTable @UserId,
'ATM', @SourceBranchCode
SELECT @LoginInserted = 1
END
END
/* Convert into base currency */
SELECT @Amount = ROUND(@AmountLCY / @ConvertRate, @MoneyRound)
IF @IsReversal = 0 BEGIN /* NOT a reversal*/
/*
ChargeAmt is in base currency */
IF (@AvailBaln - (@Amount + ISNULL(@TermCharge, 0) + ISNULL(@CashMaintainCharge, 0) + ISNULL(@BankCommAmt, 0) + ISNULL(@OtherCommAmt, 0)) < 0) AND (@AcTypeType <> 'F') BEGIN
SELECT @MsgStr = '(PDXBalnXfr) Balance not sufficient : ' + @SourceMainCode, @RetPumCode = 'NF'
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION BalnXfrWithComm
RETURN -113
END END
IF @IsReversal <> 1 BEGIN /* not a
reversal */
INSERT INTO
InMemTransPDX
(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, -@TotAmount, -@TotAmountLCY, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2,
ISNULL(@Desc3, 'to ' + @MainCodeCr), @UserId, @UserId, @RateCode, @AcTypeType, @TraceNo)
END ELSE BEGIN
INSERT INTO
InMemTransPDX
(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, @TotAmount, @TotAmountLCY, @TranId, @CyCode, @UserId, @Today, @Today, @Desc1, @Desc2,
ISNULL(@Desc3, 'from ' + @MainCodeCr), @UserId, @UserId, @RateCode, @AcTypeType, @TraceNo)
END
IF @IsABBS = 1 BEGIN
SELECT BranchCode, SUM(LCYAmount) AS TotAmount INTO
#temp
FROM InMemTransPDX
WHERE BranchCode <> @SourceBranchCode
AND TranId = @TranId
AND LTRIM(RTRIM(UserId)) = LTRIM(RTRIM(@UserId))
GROUP BY
BranchCode
DELETE FROM #temp WHERE ISNULL(TotAmount,0) = 0
/***** check
whether IBT Accounts exists or not *********/
DECLARE EachBranch INSENSITIVE CURSOR FOR
SELECT BranchCode FROM
#temp
OPEN EachBranch
FETCH NEXT FROM EachBranch INTO @Branch
WHILE @@FETCH_STATUS <> -1 BEGIN
SELECT @MainCodeRM = A.MainCodeRM, @MainCodeTB = A.MainCodeTB, @RemoteAcType = A.RemoteAcType
FROM ABBSBranchTable A (NOLOCK), InMemTransPDX T (NOLOCK)
WHERE T.BranchCode = A.BranchCode
AND T.CyCode = A.CyCode
AND T.BranchCode = @Branch
AND T.TranId = @TranId
AND T.UserId = @UserId
IF (@MainCodeRM IS NULL) OR (@MainCodeTB IS NULL) OR (@RemoteAcType IS NULL) BEGIN
SELECT @MsgStr = 'ABBSBranchTable
does not have row of branch : ' + @Branch
CLOSE EachBranch
DEALLOCATE EachBranch
DROP TABLE #temp
BEGIN tRY
INSERT INTO
InMemTransPDX
(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 @SourceBranchCode, M.AcType, T.CyCode, A.MainCodeRM, M.Name, A.TranCodeDr, Amount, LCYAmount,Desc1, Desc2, Desc3, @Today, TranCyCode,
ApprovedBy, EnteredBy, CASE WHEN EnteredBy = '_Card' THEN '*' ELSE [Status] END, '*', 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), InMemTransPDX T (NOLOCK), 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 = @SourceBranchCode
AND M.AcType = A1.AcType
AND T.TranId = @TranId
AND T.UserId = @UserId
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, CASE WHEN EnteredBy='_Card' THEN '*' ELSE [Status] END, '*', RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, A1.AcTypeType, CityCode, ExtraChqNo, T.TranId, T.UserId,
'D', @SourceBranchCode, T.CustCode, T.SubCustCode
FROM ABBSBranchTable A (NOLOCK), InMemTransPDX T (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 A.RemoteAcType = A1.AcType
AND T.TranId = @TranId
AND T.UserId = @UserId
/******* Add
nostro entries for Cr Trans *****/
UNION ALL
/* This Branch
*/
SELECT @SourceBranchCode, M.AcType, T.CyCode, A.MainCodeRM, M.Name, A.TranCodeCr, Amount, LCYAmount, Desc1, Desc2, Desc3, @Today, TranCyCode,
ApprovedBy, EnteredBy, CASE WHEN EnteredBy='_Card' THEN '*' ELSE [Status] END, '*', 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), InMemTransPDX T (NOLOCK), 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 = @SourceBranchCode
AND M.AcType = A1.AcType
AND T.TranId = @TranId
AND T.UserId = @UserId
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, CASE WHEN EnteredBy='_Card' THEN '*' ELSE [Status] END, '*', RateCode, ReferenceNo, BankCode, ChequeNo,
TemporaryLimit, A1.AcTypeType, CityCode, ExtraChqNo, T.TranId, T.UserId, 'D' /* Dest ABBS Code */, @SourceBranchCode, /* Nostro Branch */
T.CustCode, T.SubCustCode
FROM ABBSBranchTable A (NOLOCK), InMemTransPDX T (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 A.RemoteAcType = A1.AcType
AND T.TranId = @TranId
AND T.UserId = @UserId
UPDATE InMemTransPDX SET NostroBranch = @SourceBranchCode, ABBSCode = 'D'
/* Destination */
WHERE BranchCode <> @SourceBranchCode
AND TranId = @TranId
AND UserId = @UserId
AND (ISNULL(NostroBranch, '') = '' OR ISNULL(ABBSCode, '') = '')
UPDATE InMemTransPDX SET NostroBranch = @Branch, ABBSCode = 'S' /* Source */
WHERE BranchCode =
@SourceBranchCode
AND TranId = @TranId
AND UserId = @UserId
AND (ISNULL(NostroBranch, '') = '' OR ISNULL(ABBSCode, '') = '')
DROP TABLE #temp
COMMIT TRANSACTION BalnXfrWithComm
SELECT @MsgStr = 'Transaction Successful'
RETURN 0
END TRY
BEGIN CATCH
SELECT @MsgStr = ERROR_MESSAGE()
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION BalnXfrWithComm
RETURN -135
END CATCH
Comments
Post a Comment