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.
277 lines
9.4 KiB
277 lines
9.4 KiB
|
|
--EXEC PROC_THUNES_TXN_UPDATE @Flag='u',@User ='system',@GmeControlNo='80151368349',@WingCode='66420240',@ThunesCode='201020480'
|
|
--SELECT * FROM dbo.remitTran(NOLOCK) WHERE controlNo=dbo.FNAEncryptString('66420240')
|
|
--SELECT * FROM FastMoneyPro_Account.dbo.tran_master(NOLOCK) WHERE field1='66420240'
|
|
|
|
|
|
--EXEC APPROVE_ONHOLD_TXN @flag='s-by-controlNo', @ControlNo2='80115870376',@user='admin'
|
|
ALTER PROC APPROVE_ONHOLD_TXN
|
|
(
|
|
@Flag VARCHAR(100)
|
|
,@User VARCHAR(100)
|
|
,@ControlNo2 VARCHAR(100) = NULL
|
|
,@ControlNo VARCHAR(100) = NULL
|
|
,@ContNo VARCHAR(100) = NULL
|
|
,@tranId VARCHAR(100) = NULL
|
|
,@remarks VARCHAR(300) = NULL
|
|
,@searchBy VARCHAR(30) = NULL
|
|
)
|
|
AS
|
|
BEGIN TRY
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
DECLARE @ControlNo2Encrypted VARCHAR(20)
|
|
,@ControlNoEncrypted VARCHAR(20)
|
|
,@id BIGINT
|
|
,@sBranch INT;
|
|
|
|
SELECT @ControlNo2Encrypted = dbo.FNAEncryptString(@ControlNo2)
|
|
SELECT @ControlNoEncrypted = dbo.FNAEncryptString(@ControlNo)
|
|
|
|
|
|
IF @Flag = 's-by-controlNo'
|
|
BEGIN
|
|
|
|
IF @searchBy IS NOT NULL AND @searchBy='TranId'
|
|
BEGIN
|
|
SELECT @ControlNo2Encrypted=controlNo, @ControlNo2= dbo.FNADecryptString(controlNo) FROM dbo.remitTranTemp(NOLOCK) WHERE id = @tranId
|
|
IF ISNULL(@ControlNo2Encrypted,'') = ''
|
|
BEGIN
|
|
UPDATE remitTranTemp SET controlNo = DBO.FNAEncryptString(id) WHERE id = @tranId
|
|
SELECT @ControlNo2Encrypted = DBO.FNAEncryptString(@tranId),@ControlNo2 = @tranId
|
|
END
|
|
END
|
|
|
|
|
|
DECLARE @tranStatus VARCHAR(20) = NULL
|
|
SELECT @sBranch = agentId FROM applicationUsers WITH(NOLOCK) WHERE userName = @user
|
|
SELECT @tranStatus = tranStatus FROM remitTranTemp WITH(NOLOCK) WHERE controlNo = @ControlNo2Encrypted
|
|
IF (@tranStatus IS NOT NULL)
|
|
BEGIN
|
|
INSERT INTO tranViewHistory(
|
|
controlNumber
|
|
,tranViewType
|
|
,agentId
|
|
,createdBy
|
|
,createdDate
|
|
)
|
|
SELECT
|
|
@controlNoEncrypted
|
|
,'A'
|
|
,@sBranch
|
|
,@user
|
|
,GETDATE()
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
EXEC proc_errorHandler 1000, 'No Transaction Found for approve/rejection', @ControlNo2Encrypted
|
|
RETURN
|
|
END
|
|
|
|
IF (@tranStatus = 'CancelRequest')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has been requested for cancel', @ControlNo2Encrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Payment')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction already been approved and ready for payment', @ControlNo2Encrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Paid')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is already been paid', @ControlNo2Encrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Cancel')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is cancelled', @ControlNo2Encrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Lock')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is locked. Please Contact HO', @ControlNo2Encrypted
|
|
RETURN
|
|
END
|
|
EXEC proc_errorHandler 0, 'Transaction Found', @ControlNo2Encrypted
|
|
|
|
declare @table varchar(max), @sql VARCHAR(MAX);
|
|
|
|
SET @table = '
|
|
SELECT DISTINCT
|
|
ControlNo = dbo.fnaDecryptstring(trn.controlNo)
|
|
,trn.id
|
|
,branch = am.agentName
|
|
,country = trn.pCountry
|
|
,senderId = sen.customerId
|
|
,sender = sen.firstName + ISNULL( '' '' + sen.middleName, '''') + ISNULL( '' '' + sen.lastName1, '''') + ISNULL( '' '' + sen.lastName2, '''')
|
|
,receiverId = rec.customerId
|
|
,receiver = rec.firstName + ISNULL( '' '' + rec.middleName, '''') + ISNULL( '' '' + rec.lastName1, '''') + ISNULL( '' '' + rec.lastName2, '''')
|
|
,amt = CAST(trn.cAmt AS DECIMAL(18, 2))
|
|
,txnDate = CAST(trn.createdDate AS DATE)
|
|
,tranType = CASE WHEN sRouteId=''w'' THEN ''WALLET'' WHEN sRouteId=''a'' THEN ''AUTO-DEBIT'' ELSE ''-'' END
|
|
,txncreatedBy = trn.createdBy
|
|
,CASE WHEN trn.pAgent=1100 OR trn.pAgent = 1043 THEN 1 ELSE 0 END isThirdPartyTran
|
|
FROM remitTranTemp trn WITH(NOLOCK)
|
|
INNER JOIN agentMaster am WITH(NOLOCK) ON trn.sBranch = am.agentId
|
|
INNER JOIN tranSendersTemp sen WITH(NOLOCK) ON trn.id = sen.tranId
|
|
INNER JOIN tranReceiversTemp rec WITH(NOLOCK) ON trn.id = rec.tranId
|
|
WHERE trn.tranStatus IN (''Hold'',''Compliance Hold'',''OFAC Hold'',''OFAC/Compliance Hold'') AND
|
|
trn.payStatus = ''Unpaid'' AND
|
|
trn.approvedBy IS NULL
|
|
'
|
|
|
|
IF @ControlNo2 IS NOT NULL
|
|
SET @table = @table + ' AND trn.controlNo = '''+@ControlNo2Encrypted+''''
|
|
|
|
SET @sql = '
|
|
SELECT
|
|
* , STUFF((SELECT '''' + US.voucherNo +'' - ''+ CONVERT(VARCHAR(11),US.voucherDate,6) +'' - ''+ CAST(US.voucherAmt AS VARCHAR)+'' || ''
|
|
FROM bankCollectionVoucherDetail US
|
|
WHERE US.tempTranId = x.id
|
|
FOR XML PATH('''')), 1, 1, '''') [voucherDetail]
|
|
FROM (
|
|
' + @table + '
|
|
) x
|
|
WHERE 1 = 1 '
|
|
|
|
--PRINT @sql
|
|
EXEC (@sql)
|
|
RETURN
|
|
END
|
|
|
|
ELSE IF @Flag='u'
|
|
BEGIN
|
|
|
|
IF @ControlNo IS NULL
|
|
BEGIN
|
|
SELECT @ControlNoEncrypted = dbo.FNAEncryptString(@ControlNo2)
|
|
SET @ControlNo = @ControlNo2
|
|
END
|
|
|
|
BEGIN TRANSACTION
|
|
/*temp table check*/
|
|
|
|
IF EXISTS(SELECT 'X' from remitTrantemp(NOLOCK) where controlNo=@ControlNo2Encrypted AND tranStatus='Hold' AND payStatus='Unpaid')
|
|
BEGIN
|
|
IF EXISTS(SELECT 'X' FROM remitTran(nolock) where controlNo = @ControlNoEncrypted
|
|
--OR EXISTS(select 'x' from PinQueueList(nolock) where icn = @ControlNoEncrypted)
|
|
--OR EXISTS(SELECT 'X' FROM FastMoneyPro_Account.dbo.tran_master (nolock) where field1=@ControlNo
|
|
)
|
|
BEGIN
|
|
DECLARE @newControlNo VARCHAR(20);
|
|
SET @newControlNo = CONCAT(@controlNo,'A');
|
|
UPDATE remitTran SET controlNo=dbo.FNAEncryptString(@newControlNo) WHERE controlNo = @ControlNoEncrypted
|
|
UPDATE PinQueueList SET icn=dbo.FNAEncryptString(@newControlNo) WHERE icn = @ControlNoEncrypted
|
|
UPDATE FastMoneyPro_Account.dbo.tran_master SET field1= @newControlNo WHERE field1 = @controlNo AND tran_type='T'
|
|
|
|
IF EXISTS(SELECT TOP 1 'A' FROM FastMoneyPro_Account.dbo.tran_master_post(NOLOCK) WHERE field1 = @controlNo AND tran_type='T')
|
|
UPDATE FastMoneyPro_Account.dbo.tran_master_post SET field1= @newControlNo WHERE field1 = @controlNo AND tran_type='T'
|
|
END
|
|
|
|
UPDATE remitTrantemp SET
|
|
controlNo = @ControlNoEncrypted --wing code
|
|
,contNo = @ContNo--thunes code
|
|
,controlNo2 = @ControlNo2Encrypted
|
|
,tranStatus = 'Payment'
|
|
,payStatus = 'Post'
|
|
WHERE controlNo=@ControlNo2Encrypted AND id=@tranId
|
|
|
|
EXEC [proc_ApproveHoldedTXN] @flag='approve',@user=@user,@id = @tranId
|
|
|
|
UPDATE remitTran SET
|
|
postedBy=@User,
|
|
postedDate=GETDATE(),
|
|
postedDateLocal=GETDATE()
|
|
WHERE controlNo=@ControlNoEncrypted
|
|
END
|
|
/*main table */
|
|
--IF EXISTS(SELECT 'X' from remitTran(NOLOCK) where controlNo=@ControlNo2Encrypted AND tranStatus='Hold' AND payStatus='Unpaid')
|
|
--BEGIN
|
|
-- IF EXISTS (SELECT 'X' from FastMoneyPro_Account.dbo.tran_master (nolock) where field1=@ControlNo2)
|
|
-- BEGIN
|
|
-- UPDATE remitTran SET
|
|
-- controlNo = @ControlNoEncrypted --wing code
|
|
-- ,contNo = @ContNo --thunes code
|
|
-- ,controlNo2 = @ControlNo2Encrypted
|
|
-- ,tranStatus = 'Payment'
|
|
-- ,payStatus = 'Post'
|
|
-- WHERE controlNo = @ControlNo2Encrypted AND id = @tranId
|
|
|
|
-- UPDATE FastMoneyPro_Account.dbo.tran_master
|
|
-- SET field1 = @ControlNo
|
|
-- WHERE field1 = @ControlNo2
|
|
-- END
|
|
--END
|
|
COMMIT TRANSACTION
|
|
IF @@TRANCOUNT = 0
|
|
BEGIN
|
|
SELECT 0 errorCode,'Txn Update sucessfully' Msg, @id
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT 1 errorCode,'Failed to Update Transaction' Msg, @id
|
|
RETURN
|
|
END
|
|
END
|
|
|
|
ELSE IF @Flag='reject'
|
|
BEGIN
|
|
DECLARE @TxnType VARCHAR(2)= NULL
|
|
SELECT
|
|
@Id = id
|
|
,@TxnType = sRouteId
|
|
FROM remitTrantemp(NOLOCK) WHERE controlNo= @ControlNo2Encrypted
|
|
|
|
EXEC [proc_ApproveHoldedTXN] @flag='reject',@user=@User ,@id = @Id, @remarks=@remarks
|
|
RETURN
|
|
|
|
--IF @TxnType='a'
|
|
--BEGIN
|
|
-- SELECT '0' ErrorCode, 'You cannot reject the Autodebit transaction. First approve then use cancel menu to cancel the Transaction.' Msg, NULL Id
|
|
-- RETURN
|
|
--END
|
|
END
|
|
ELSE IF @Flag='onHoldRejectReceipt'
|
|
BEGIN
|
|
DECLARE @AccName NVARCHAR(100),@AccNo VARCHAR(30),@BankName NVARCHAR(100),@bankCode VARCHAR(5),@srouteId CHAR(1), @customerId BIGINT , @tAmt MONEY
|
|
|
|
SELECT @controlNoEncrypted = DBO.fnaDecryptstring(Controlno),@tranStatus= tranStatus,@srouteId = sRouteId FROM dbo.cancelTranHistory(NOLOCK) WHERE tranId = @tranId
|
|
|
|
IF ISNULL(@srouteId,'') = 'A'
|
|
BEGIN
|
|
SELECT @AccName = accountName,@AccNo = accountNum,@BankName = bankName ,@bankCode = bankCodeStd,@customerId = customerId
|
|
FROM DBO.[FNA_KFTC_CUST_DETAILBY_TXN](@tranId)
|
|
END
|
|
SELECT
|
|
controlNo = dbo.FNADecryptString(controlNo)
|
|
,'0' AS ERRORCODE
|
|
,postedBy = trn.sBranchName
|
|
,createdDate
|
|
,cancelDate = cancelApprovedDate
|
|
,sender = sen.firstName + ISNULL(' ' + sen.middleName, '') + ISNULL(' ' + sen.lastName1, '') + ISNULL(' ' + sen.lastName2, '')
|
|
,receiver = rec.firstName + ISNULL(' ' + rec.middleName, '') + ISNULL(' ' + rec.lastName1, '') + ISNULL(' ' + rec.lastName2, '')
|
|
,rContactNo = rec.mobile
|
|
,trn.collCurr
|
|
,trn.cAmt
|
|
,trn.serviceCharge
|
|
,trn.pAmt
|
|
,trn.cancelCharge
|
|
--,returnAmt = trn.cAmt - ISNULL(trn.cancelCharge,0)
|
|
,returnAmt = trn.cAmt
|
|
,@AccName AccName,@AccNo AccNo,@BankName BankName,@bankCode bankCode,@customerId customerId
|
|
FROM dbo.cancelTranHistory trn WITH(NOLOCK)
|
|
INNER JOIN dbo.cancelTranSendersHistory sen WITH(NOLOCK) ON trn.tranId = sen.tranId
|
|
INNER JOIN dbo.cancelTranReceiversHistory rec WITH(NOLOCK) ON trn.tranId = rec.tranId
|
|
WHERE trn.tranId = @tranId
|
|
END
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
SELECT 1 error_code, ERROR_MESSAGE() mes, NULL id
|
|
END CATCH
|
|
|
|
|
|
|