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.
79 lines
5.2 KiB
79 lines
5.2 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[Inbound_proc_payOfacCompliance] Script Date: 9/27/2019 1:30:13 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROCEDURE [dbo].[Inbound_proc_payOfacCompliance](
|
|
@flag VARCHAR(20)
|
|
,@user VARCHAR(50) = NULL
|
|
,@id INT = NULL
|
|
,@remarks VARCHAR(MAX) = NULL
|
|
,@controlNo VARCHAR(50) = NULL
|
|
,@sortBy VARCHAR(50) = NULL
|
|
,@sortOrder VARCHAR(5) = NULL
|
|
,@pageSize INT = NULL
|
|
,@pageNumber INT = NULL
|
|
)
|
|
AS
|
|
|
|
IF @flag = 's_summary'
|
|
BEGIN
|
|
SELECT
|
|
[Head] = B.tranStatus,
|
|
[Count] = CASE WHEN COUNT('x') > 0 THEN
|
|
CASE WHEN B.tranStatus = 'OFAC HOLD' THEN '<a href="PayTranOfacList.aspx">'+CAST(COUNT('x') AS VARCHAR) +'</a>'
|
|
WHEN B.tranStatus = 'COMPLIANCE HOLD' THEN '<a href="PayTranCompliance.aspx">'+CAST(COUNT('x') AS VARCHAR) +'</a>'
|
|
ELSE '<a href="List.aspx">'+CAST(COUNT('x') AS VARCHAR) +'</a>' END
|
|
|
|
ELSE '-' END
|
|
FROM dbo.INBOUND_REMIT_TRAN b WITH(NOLOCK)
|
|
WHERE (B.tranStatus = 'COMPLIANCE/OFAC HOLD' OR B.tranStatus = 'COMPLIANCE HOLD' OR B.tranStatus = 'OFAC HOLD')
|
|
GROUP BY B.tranStatus
|
|
|
|
END
|
|
|
|
IF @Flag='s'
|
|
BEGIN
|
|
EXEC proc_errorHandler 0, 'Transaction varification successfully.', @Id
|
|
SELECT TOP 1
|
|
rowId =trn.id
|
|
,controlNo =dbo.FNADecryptString(trn.controlNo)
|
|
,sendingAgent = trn.sAgentName
|
|
,senderName =sen.firstName + ISNULL( ' ' + sen.middleName, '') + ISNULL( ' ' + sen.lastName, '')
|
|
,recName =rec.firstName + ISNULL( ' ' + rec.middleName, '') + ISNULL( ' ' + rec.lastName, '')
|
|
,providerName ='Inbound API'
|
|
,type = trn.tranStatus
|
|
,payoutagent = trn.pAgentName
|
|
,payoytAmount = trn.pAmt
|
|
,trn.createdDate
|
|
,trn.createdBy
|
|
|
|
FROM dbo.INBOUND_REMIT_TRAN trn WITH(NOLOCK)
|
|
INNER JOIN dbo.INBOUND_TRAN_SENDERS sen WITH(NOLOCK) ON trn.id = sen.tranId
|
|
INNER JOIN dbo.INBOUND_TRAN_RECEIVERS rec WITH(NOLOCK) ON trn.id = rec.tranId
|
|
INNER JOIN agentMaster am WITH(NOLOCK) ON am.agentId = trn.sAgent
|
|
WHERE trn.id = @Id
|
|
|
|
-- ## Transaction Log Details
|
|
DECLARE @controlNoEncrypted VARCHAR(50)
|
|
SELECT @controlNoEncrypted = controlNo
|
|
FROM dbo.INBOUND_REMIT_TRAN rt WITH(NOLOCK) WHERE id = @Id
|
|
SELECT
|
|
id
|
|
,message
|
|
,trn.createdBy
|
|
,trn.createdDate
|
|
FROM dbo.INBOUND_TROUBLE_TICKET trn WITH(NOLOCK)
|
|
LEFT JOIN applicationUsers au WITH(NOLOCK) ON trn.createdBy = au.userName
|
|
WHERE trn.tranId = @Id OR trn.controlNo = @controlNoEncrypted
|
|
ORDER BY trn.id DESC
|
|
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
GO
|