You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
92 lines
8.0 KiB
92 lines
8.0 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_PROCESS_TRAN_NIBANK] Script Date: 9/27/2019 1:30:14 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
--EXEC [PROC_PROCESS_TRAN_NIBANK] @FLAG = 'TRAN-TXN',@P_CONTROLNO='QIONPLPKKMP'
|
|
|
|
CREATE PROCEDURE [dbo].[PROC_PROCESS_TRAN_NIBANK]
|
|
(
|
|
@FLAG VARCHAR(50),
|
|
@P_CONTROLNO VARCHAR(20) = NULL,
|
|
@RATE MONEY = NULL,
|
|
@PARTNERID BIGINT = NULL
|
|
)
|
|
AS
|
|
|
|
SET NOCOUNT ON
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
|
|
----------------------------------------------------------------------------------
|
|
-- Procedure Start
|
|
----------------------------------------------------------------------------------
|
|
|
|
BEGIN
|
|
IF @FLAG = 'TRAN-TXN'
|
|
BEGIN
|
|
|
|
SELECT '0' AS ErrorCode,
|
|
'SUCCESS' AS Msg,
|
|
dbo.FNADecryptString(RT.controlNo) AS [TxnReference], --TxnReference
|
|
ISNULL(RT.purposeOfRemit,'Family maintenance') As [Purpose], --Purpose
|
|
(SELECT lastName1+ISNULL(' '+lastName2,'') FROM dbo.FNASplitName(TS.firstName)) AS [SenderLastName], --SenderLastName
|
|
(SELECT firstName+ISNULL(' '+middleName,'') FROM dbo.FNASplitName(TS.firstName)) AS [SenderFirstName], --SenderFirstName
|
|
TS.mobile AS [SenderTelNo], --SenderTelNo
|
|
KB.BankName AS [SenderBankName],
|
|
CM.walletAccountNo AS [SenderAccountNo],
|
|
CASE WHEN RI.localLastName1 IS NOT NULL THEN RI.localLastName1 ELSE TR.lastName1+ISNULL(' '+TR.lastName2,'') END AS [RecLastName], --RecLastName
|
|
CASE WHEN RI.localFirstName IS NOT NULL THEN RI.localFirstName ELSE TR.firstName END AS [RecFirstName], --RecLastName
|
|
--CASE WHEN RT.controlNo IN('QIOMJNMNRON') THEN N'НЯМЖАРГАЛ' ELSE TR.firstName END AS [RecFirstName], --RecFirstName
|
|
--TR.lastName1+ISNULL(' '+TR.lastName2,'') AS [RecLastName], --RecLastName
|
|
--TR.firstName+ISNULL(' '+TR.middleName,'') AS [RecFirstName], --RecFirstName
|
|
TR.mobile AS [RecTelNo],--RecTelNo
|
|
'' AS [RecForm],--RecForm
|
|
AM.agentCode AS [RecBankNo],--RecBankNo
|
|
RT.accountNo AS [RecAccount],--RecAccount
|
|
RT.payoutCurr AS [RecCurrency],--RecCurrency
|
|
ROUND(tAmt/(sCurrCostRate+ISNULL(sCurrHoMargin,0)) ,2) AS [FromAmount],--FromAmount
|
|
FORMAT(RT.createdDate,'yyyy-MM-dd HH:mm:ss') AS [SendingDate] --SendingDate
|
|
FROM remitTran(nolock) RT
|
|
INNER JOIN tranSenders(nolock) TS ON RT.id = TS.tranid
|
|
INNER JOIN tranReceivers(nolock) TR ON RT.id = TR.tranId
|
|
INNER JOIN receiverInformation(nolock) RI ON RI.receiverId= TR.customerId
|
|
INNER JOIN agentMaster(nolock) AM ON RT.pBank = AM.agentId
|
|
INNER JOIN customerMaster(nolock) CM ON CM.customerId = TS.customerId
|
|
INNER JOIN KoreanBankList(nolock) KB ON KB.rowId = CM.bankName
|
|
WHERE pCountry = 'MONGOLIA' AND payStatus = 'UNPAID' AND tranStatus='PAYMENT'
|
|
AND controlNo= @P_CONTROLNO AND RT.pSuperAgent = 590814
|
|
|
|
END
|
|
ELSE IF @FLAG = 'UPDATE-RATE'
|
|
BEGIN
|
|
DECLARE @FROMDATE DATE = GETDATE(),@TODATE DATE,@DAYNAME VARCHAR(20)
|
|
|
|
SET @DAYNAME = FORMAT(@FROMDATE, 'dddd')
|
|
|
|
SET @TODATE = CASE WHEN @DAYNAME = 'FRIDAY' THEN DATEADD(DAY,3,@FROMDATE)
|
|
WHEN @DAYNAME = 'SATURDAY' THEN DATEADD(DAY,2,@FROMDATE)
|
|
ELSE DATEADD(DAY,1,@FROMDATE)
|
|
END
|
|
|
|
IF EXISTS(SELECT 'A' FROM TBL_PARTNER_COST_RATE(NOLOCK) WHERE GETDATE() BETWEEN EffectiveFrom AND EffectiveTo AND ToCurr = 'MNT' AND ProviderId = @PARTNERID)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Todays Rate already applied.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
INSERT INTO TBL_PARTNER_COST_RATE(ProviderName,CurrCostRate,FromCurr,ToCurr,EffectiveFrom,EffectiveTo,Createdby,CreatedDate,ProviderId)
|
|
SELECT 'NIBANK',@RATE,'USD','MNT',@FROMDATE,CAST(@TODATE AS VARCHAR)+' 19:00:00','SYSTEM',GETDATE(),@PARTNERID
|
|
|
|
SELECT '0' ErrorCode,'New Rate Updated Successfully.' Msg, NULL
|
|
|
|
END
|
|
END
|
|
----------------------------------------------------------------------------------
|
|
-- Procedure End
|
|
----------------------------------------------------------------------------------
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
|
|
SET NOCOUNT OFF
|
|
|
|
GO
|