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.
494 lines
29 KiB
494 lines
29 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_DAILYPAID_AND_SENDING] Script Date: 4/4/2024 9:52:08 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROC [dbo].[PROC_DAILYPAID_AND_SENDING] @flag VARCHAR(20)
|
|
,@user VARCHAR(30) = NULL
|
|
,@startDate VARCHAR(10) = NULL
|
|
,@endDate VARCHAR(10) = NULL
|
|
,@sAgentId BIGINT = NULL
|
|
,@payoutPartnerId BIGINT = NULL
|
|
,@payoutPartnerName VARCHAR(100) = NULL
|
|
,@sortBy VARCHAR(50) = NULL
|
|
,@sortOrder VARCHAR(5) = NULL
|
|
,@pageSize INT = NULL
|
|
,@pageNumber INT = NULL
|
|
,@trantype VARCHAR(5) = NULL
|
|
,@verificationType VARCHAR(50) = NULL
|
|
AS
|
|
--------------------------------------
|
|
--July 20 @JME-562 -> add customerid column
|
|
-- #509 - Add transactionType column
|
|
-- #718 - @flag = 'send'
|
|
-- #1254 - @flag = 'send' , add source of deposit
|
|
-- #1440 - Payment type , @flag = 'send'
|
|
--------------------------------------
|
|
BEGIN TRY
|
|
DECLARE @select_field_list VARCHAR(MAX)
|
|
,@extra_field_list VARCHAR(MAX)
|
|
,@table VARCHAR(MAX)
|
|
,@sql_filter VARCHAR(MAX)
|
|
,@sAgentName VARCHAR(150)
|
|
,@pAgentName VARCHAR(150)
|
|
,@endDateNew VARCHAR(20)
|
|
,@usertype VARCHAR(20)
|
|
|
|
SELECT @sAgentName = agentName
|
|
FROM dbo.agentMaster
|
|
WHERE agentid = @sAgentId
|
|
|
|
SELECT @pAgentName = agentName
|
|
FROM dbo.agentMaster
|
|
WHERE agentid = @payoutPartnerId
|
|
|
|
SET @endDateNew = @endDate + ' 23:59:59'
|
|
|
|
IF @flag = 'PAID'
|
|
BEGIN
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY rt.id
|
|
) SN
|
|
,rt.id [Receipt No]
|
|
,convert(VARCHAR, rt.createdDate, 121) [Creation Date]
|
|
,convert(VARCHAR, rt.paidDate, 121) [Payment Date]
|
|
,rt.createdBy [User Name]
|
|
,rt.pSuperAgentName [Payment Office]
|
|
,rt.sCountry [Country Of Origin]
|
|
,rt.receiverName [Beneficiary]
|
|
,tr.mobile [Receiver Mobile]
|
|
,rt.tAmt [Amount To Send]
|
|
,rt.collCurr [Coll Currency]
|
|
,rt.pAmt [Amount to Receive]
|
|
,rt.payoutCurr [Pay Currency]
|
|
,rt.pCountry [Receiver country]
|
|
,CASE rt.paymentMethod
|
|
WHEN 'BANK DEPOSIT'
|
|
THEN 'BT'
|
|
WHEN 'CASH PAYMENT'
|
|
THEN 'CPU'
|
|
END [Payment Type]
|
|
,dbo.FNADecryptString(rt.controlNo) [Control Number]
|
|
INTO #PAID_RPT
|
|
FROM remittran rt(NOLOCK)
|
|
INNER JOIN dbo.tranReceivers tr(NOLOCK) ON tr.tranId = rt.id
|
|
WHERE pSuperAgent = ISNULL(@payoutPartnerId, pSuperAgent)
|
|
AND sAgent = ISNULL(@sAgentId, sAgent)
|
|
AND paidDate BETWEEN @startDate
|
|
AND @endDateNew
|
|
AND (
|
|
PAYSTATUS = 'PAID'
|
|
OR transtatus = 'Paid'
|
|
)
|
|
AND tranType = ISNULL(@trantype,tranType)
|
|
ORDER BY rt.createdDate
|
|
|
|
SELECT *
|
|
FROM #PAID_RPT
|
|
|
|
UNION ALL
|
|
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY rt.id
|
|
) SN
|
|
,rt.id [Receipt No]
|
|
,convert(VARCHAR, rt.createdDate, 121) [Creation Date]
|
|
,[Payment Date] = 'Previously paid but cancelled'
|
|
,rt.createdBy [User Name]
|
|
,rt.pSuperAgentName [Payment Office]
|
|
,rt.sCountry [Country Of Origin]
|
|
,rt.receiverName [Beneficiary]
|
|
,tr.mobile [Receiver Mobile]
|
|
,rt.tAmt * - 1 [Amount To Send]
|
|
,rt.collCurr [Coll Currency]
|
|
,rt.pAmt [Amount to Receive]
|
|
,rt.payoutCurr [Pay Currency]
|
|
,rt.pCountry [Receiver country]
|
|
,CASE rt.paymentMethod
|
|
WHEN 'BANK DEPOSIT'
|
|
THEN 'BT'
|
|
WHEN 'CASH PAYMENT'
|
|
THEN 'CPU'
|
|
END [Payment Type]
|
|
,dbo.FNADecryptString(rt.controlNo) [Control Number]
|
|
FROM remittran rt(NOLOCK)
|
|
INNER JOIN dbo.tranReceivers tr(NOLOCK) ON tr.tranId = rt.id
|
|
WHERE pSuperAgent = ISNULL(@payoutPartnerId, pSuperAgent)
|
|
AND sAgent = ISNULL(@sAgentId, sAgent)
|
|
AND incrRpt = 'c'
|
|
AND CANCELAPPROVEDDATE BETWEEN @startDate
|
|
AND @endDateNew
|
|
AND tranType =ISNULL(@trantype,tranType)
|
|
|
|
UNION ALL
|
|
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY rt.id
|
|
) SN
|
|
,rt.id [Receipt No]
|
|
,convert(VARCHAR, '2023-01-06 9:45:35 AM', 121) [Creation Date]
|
|
,[Payment Date] = 'Previously paid but cancelled'
|
|
,rt.createdBy [User Name]
|
|
,rt.pSuperAgentName [Payment Office]
|
|
,rt.sCountry [Country Of Origin]
|
|
,rt.receiverName [Beneficiary]
|
|
,tr.mobile [Receiver Mobile]
|
|
,rt.tAmt * - 1 [Amount To Send]
|
|
,rt.collCurr [Coll Currency]
|
|
,rt.pAmt [Amount to Receive]
|
|
,rt.payoutCurr [Pay Currency]
|
|
,rt.pCountry [Receiver country]
|
|
,CASE rt.paymentMethod
|
|
WHEN 'BANK DEPOSIT'
|
|
THEN 'BT'
|
|
WHEN 'CASH PAYMENT'
|
|
THEN 'CPU'
|
|
END [Payment Type]
|
|
,dbo.FNADecryptString(rt.controlNo) [Control Number]
|
|
FROM remittran rt(NOLOCK)
|
|
INNER JOIN dbo.tranReceivers tr(NOLOCK) ON tr.tranId = rt.id
|
|
WHERE controlNo= dbo.encryptDb('33TF113141256')
|
|
AND CASE WHEN '2023-01-06' BETWEEN @startDate AND @endDateNew THEN 1 ELSE 0 END= 1
|
|
--AND incrRpt = 'c'
|
|
|
|
PRINT @startDate;
|
|
PRINT @endDateNew;
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'Paying Agent' head
|
|
,ISNULL(@pAgentName, 'All') value
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'From Date' head
|
|
,@startDate value
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@endDate value
|
|
|
|
SELECT 'Daily Paid Report Agent' title
|
|
END
|
|
|
|
IF @flag = 'SEND'
|
|
BEGIN
|
|
DECLARE @sql1 VARCHAR(max)
|
|
,@sql2 VARCHAR(max)
|
|
,@SQL VARCHAR(MAX)
|
|
|
|
SELECT @usertype = usertype
|
|
FROM applicationusers
|
|
WHERE username = @user
|
|
|
|
SET @sql1 = '
|
|
SELECT
|
|
[IME_NO] = ''"''+ CASE WHEN ''' + ISNULL(@usertype, '') +
|
|
'''=''A'' THEN ''<a href="../SearchTxnReport/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>''
|
|
ELSE ''<a href="/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>'' END
|
|
,[Serial No] = RT.id
|
|
,[TRUST_PAYMENT_ID] = RT.transactionreference
|
|
,[TXN_CHANNEL] = CASE WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''M'') THEN ''Mobile''
|
|
WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''O'') THEN ''Web Online''
|
|
WHEN RT.tranType = ''I'' THEN ''CR Panel''
|
|
ELSE RT.tranType END
|
|
,[DATE_SEND] =CONVERT(VARCHAR,RT.createdDate,111)
|
|
,[DATE_PAID] = CONVERT(VARCHAR,RT.paidDate,111)
|
|
--,[MEMBERSHIPID] = ISNULL(TS.membershipId, CM.MEMBERSHIPID)
|
|
,[MEMBERSHIPID] = ''<a href="/Remit/Administration/CustomerSetup/CustomerDetails.aspx?customerId=''
|
|
+ CAST(CM.CUSTOMERID AS VARCHAR) + ''">''
|
|
+ ISNULL(TS.membershipId, CM.MEMBERSHIPID) +
|
|
''</a>''
|
|
-- ''</a>''
|
|
,[PAYOUT_PARTNER] = RT.pAgentName
|
|
,[SENDER_NAME] = senderName
|
|
,[SENDER_MOBILE] = ''"''+ TS.MOBILE
|
|
,[RECEIVER_NAME]= receiverName
|
|
,[EX_RATE] = customerRate
|
|
,[PAYMENT_TYPE] = paymentMethod
|
|
,[DEPOSIT_TYPE] = RT.depositType
|
|
,[COLL_AMT] = cAmt
|
|
,[COLL_CURR] = collCurr
|
|
,[REWARD_P] = RT.rewardPoints
|
|
,[RATE_MARGIN] = RT.customerPremium
|
|
,[TRANSFER_AMT] = tAmt
|
|
,[SEND_CURR] = collCurr
|
|
,[SC_FEE] = serviceCharge
|
|
,[CHARGE_CURR] = collCurr
|
|
,[RECEIVED_AMT] = pAmt
|
|
,[RECEIVED_CURR]= payoutCurr
|
|
,[APPROVED_BY] = rt.approvedBy
|
|
,TRANSTATUS = transtatus
|
|
,PAYSTATUS = CASE
|
|
WHEN RT.PAYSTATUS = ''Paid'' THEN ''PAID''
|
|
WHEN RT.PAYSTATUS = ''Post'' THEN ''POST''
|
|
WHEN RT.PAYSTATUS = ''Cancel'' THEN ''CANCEL''
|
|
ELSE RT.PAYSTATUS
|
|
END
|
|
,[CREATED_DATE] = rt.createddate
|
|
,[VERFIY_DATE] = rt.verifiedDate
|
|
,[VERFIY_BY] = rt.verifiedBy
|
|
,RT.sBranch
|
|
,[COST_RATE] =pDateCostRate
|
|
,[EMAIL] = ISNULL(CM.email, CM.username)
|
|
FROM REMITTRANTEMP RT(NOLOCK)
|
|
LEFT JOIN TRANSENDERSTEMP TS (NOLOCK) ON TS.TRANID = RT.ID
|
|
LEFT JOIN CUSTOMERMASTER CM (NOLOCK) ON CM.CUSTOMERID = TS.CUSTOMERID
|
|
WHERE 1=1'
|
|
SET @sql2 = '
|
|
SELECT
|
|
[IME_NO] = ''"''+ CASE WHEN ''' + ISNULL(@usertype, '') +
|
|
''' = ''A'' THEN ''<a href="../SearchTxnReport/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>''
|
|
ELSE ''<a href="/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>'' END
|
|
,[Serial No] = RT.holdtranid
|
|
,[TRUST_PAYMENT_ID] = RT.transactionreference
|
|
,[TXN_CHANNEL] = CASE WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''M'') THEN ''Mobile''
|
|
WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''O'') THEN ''Web Online''
|
|
WHEN RT.tranType = ''I'' THEN ''CR Panel''
|
|
ELSE RT.tranType END
|
|
,[DATE_SEND] =CONVERT(VARCHAR,RT.approvedDate,111)
|
|
,[DATE_PAID] = CONVERT(VARCHAR,RT.paidDate,111)
|
|
--,[MEMBERSHIPID] = ISNULL(TS.membershipId, CM.MEMBERSHIPID)
|
|
,[MEMBERSHIPID] = ''<a href="/Remit/Administration/CustomerSetup/CustomerDetails.aspx?customerId='' +CAST(CM.CUSTOMERID AS VARCHAR) + ''">'' +ISNULL(TS.membershipId, CM.MEMBERSHIPID) + ''</a>''
|
|
-- +CAST(CM.CUSTOMERID AS VARCHAR) +
|
|
-- ''>''
|
|
-- + ISNULL(TS.membershipId, CM.MEMBERSHIPID) +
|
|
-- ''</a>''
|
|
,[PAYOUT_PARTNER] = RT.pAgentName
|
|
,[SENDER_NAME] = senderName
|
|
,[SENDER_MOBILE] = ''"''+ TS.MOBILE
|
|
,[RECEIVER_NAME]= receiverName
|
|
,[EX_RATE] = customerRate
|
|
,[PAYMENT_TYPE] = paymentMethod
|
|
,[DEPOSIT_TYPE] = RT.depositType
|
|
,[COLL_AMT] = cAmt
|
|
,[COLL_CURR] = collCurr
|
|
,[REWARD_P] = RT.rewardPoints
|
|
,[RATE_MARGIN] = RT.customerPremium
|
|
,[TRANSFER_AMT] = tAmt
|
|
,[SEND_CURR] = collCurr
|
|
,[SC_FEE] = serviceCharge
|
|
,[CHARGE_CURR] = collCurr
|
|
,[RECEIVED_AMT] = pAmt
|
|
,[RECEIVED_CURR]= payoutCurr
|
|
,[APPROVED_BY] = rt.approvedBy
|
|
,TRANSTATUS = transtatus
|
|
,PAYSTATUS = CASE
|
|
WHEN RT.PAYSTATUS = ''Paid'' THEN ''PAID''
|
|
WHEN RT.PAYSTATUS = ''Post'' THEN ''POST''
|
|
WHEN RT.PAYSTATUS = ''Cancel'' THEN ''CANCEL''
|
|
ELSE RT.PAYSTATUS
|
|
END
|
|
,[CREATED_DATE] = rt.createddate
|
|
,[VERFIY_DATE] = rt.verifiedDate
|
|
,[VERFIY_BY] = rt.verifiedBy
|
|
,RT.sBranch
|
|
,[COST_RATE] =pDateCostRate
|
|
,[EMAIL] = ISNULL(CM.email, CM.username)
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
LEFT JOIN TRANSENDERS TS (NOLOCK) ON TS.TRANID = RT.ID
|
|
LEFT JOIN CUSTOMERMASTER CM (NOLOCK) ON CM.CUSTOMERID = TS.CUSTOMERID
|
|
where 1=1 '
|
|
|
|
IF isnull(@startDate, '') <> ''
|
|
AND isnull(@endDate, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and rt.createddate BETWEEN ''' + @startDate + ''' AND ''' + @endDate + '''+'' 23:59:59'''
|
|
SET @sql2 = @sql2 + ' and rt.createddate BETWEEN ''' + @startDate + ''' AND ''' + @endDate + '''+'' 23:59:59'''
|
|
END
|
|
|
|
IF ISNULL(@verificationType, '') <> ''
|
|
BEGIN
|
|
IF @verificationType = 'verified'
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' AND rt.verifiedDate IS NOT NULL AND rt.verifiedBy IS NOT NULL'
|
|
SET @sql2 = @sql2 + ' AND rt.verifiedDate IS NOT NULL AND rt.verifiedBy IS NOT NULL'
|
|
END
|
|
ELSE IF @verificationType = 'unverified'
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' AND rt.verifiedDate IS NULL AND rt.verifiedBy IS NULL'
|
|
SET @sql2 = @sql2 + ' AND rt.verifiedDate IS NULL AND rt.verifiedBy IS NULL'
|
|
END
|
|
END
|
|
|
|
SET @SQL = 'select
|
|
[SN] = ROW_NUMBER() OVER (ORDER BY xyz.[CREATED_DATE] DESC)
|
|
,[IME_NO]
|
|
,[Serial No]
|
|
,[TRUST_PAYMENT_ID]
|
|
,[TXN_CHANNEL]
|
|
,[DATE_SEND]
|
|
--,[DATE_PAID]
|
|
,[MEMBERSHIPID]
|
|
,[PAYOUT_PARTNER]
|
|
,[SENDER_NAME]
|
|
,[SENDER_MOBILE]
|
|
,[RECEIVER_NAME]
|
|
,[PAYMENT_TYPE]
|
|
,[DEPOSIT_TYPE]
|
|
,[COLL_AMT]
|
|
,[COLL_CURR]
|
|
,[SC_FEE]
|
|
,[REWARD_P]
|
|
,[TRANSFER_AMT]
|
|
,[EX_RATE]
|
|
,[RATE_MARGIN]
|
|
--,[COST_RATE]
|
|
,[RECEIVED_AMT]
|
|
,[RECEIVED_CURR]
|
|
,[VERFIY_DATE]
|
|
,[VERFIY_BY]
|
|
,[APPROVED_BY]
|
|
,TRANSTATUS
|
|
,PAYSTATUS
|
|
,[EMAIL]
|
|
|
|
from (
|
|
' + @sql1 + ' union all ' + @sql2 + '
|
|
|
|
) xyz
|
|
'
|
|
SET @SQL = @SQL + 'order by xyz.[CREATED_DATE] desc'
|
|
|
|
PRINT (@SQL)
|
|
|
|
EXEC (@SQL)
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'Verification Type' head
|
|
,@verificationType value
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'From Date' head
|
|
,@startDate value
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@endDate value
|
|
|
|
SELECT 'Verified/ Unverified txn report' title
|
|
END
|
|
|
|
|
|
IF @flag = 'NoWorkPermit'
|
|
BEGIN
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY RT.id
|
|
) SN
|
|
,RT.id [Transaction No]
|
|
,dbo.FNADecryptString(controlNo) [Control Number]
|
|
,collMode [Deposit Type]
|
|
,'Sent' [Tran Status]
|
|
,CASE paymentMethod
|
|
WHEN 'BANK DEPOSIT'
|
|
THEN 'BT'
|
|
WHEN 'CASH PAYMENT'
|
|
THEN 'CPU'
|
|
END [Payment Type]
|
|
,createdDate [Date]
|
|
,pSuperAgentName [Corresponding]
|
|
,senderName [Sender]
|
|
,createdBy [Cashier]
|
|
,tAmt [Money Send]
|
|
,collCurr [Currency]
|
|
,serviceCharge [Commision]
|
|
,cAmt [Total Amount]
|
|
,pAmt [Money Received]
|
|
,payoutCurr [PayCCY]
|
|
,pCurrCostRate [Settlement Rate]
|
|
,customerRate [CustRate]
|
|
,payoutCurr [Currency Type]
|
|
,pCountry [Receiver Country]
|
|
FROM remittran RT(NOLOCK)
|
|
INNER JOIN TRANSENDERS TS(NOLOCK) ON TS.TRANID = RT.ID
|
|
WHERE pSuperAgent = ISNULL(@payoutPartnerId, pSuperAgent)
|
|
AND sAgent = ISNULL(@sAgentId, sAgent)
|
|
AND createdDate BETWEEN @startDate
|
|
AND @endDateNew
|
|
AND TS.VISASTATUS IN (
|
|
'11387'
|
|
,'11418'
|
|
,'11419'
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY RT.id
|
|
) SN
|
|
,RT.id [Transaction No]
|
|
,dbo.FNADecryptString(controlNo) [Control Number]
|
|
,collMode [Deposit Type]
|
|
,'Cancel' [Tran Status]
|
|
,CASE paymentMethod
|
|
WHEN 'BANK DEPOSIT'
|
|
THEN 'BT'
|
|
WHEN 'CASH PAYMENT'
|
|
THEN 'CPU'
|
|
END [Payment Type]
|
|
,createdDate [Date]
|
|
,pSuperAgentName [Corresponding]
|
|
,senderName [Sender]
|
|
,createdBy [Cashier]
|
|
,- 1 * tAmt [Money Send]
|
|
,collCurr [Currency]
|
|
,- 1 * serviceCharge [Commision]
|
|
,- 1 * cAmt [Total Amount]
|
|
,- 1 * pAmt [Money Received]
|
|
,payoutCurr [PayCCY]
|
|
,pCurrCostRate [Settlement Rate]
|
|
,customerRate [CustRate]
|
|
,payoutCurr [Currency Type]
|
|
,pCountry [Receiver Country]
|
|
FROM remittran RT(NOLOCK)
|
|
INNER JOIN TRANSENDERS TS(NOLOCK) ON TS.TRANID = RT.ID
|
|
WHERE pSuperAgent = ISNULL(@payoutPartnerId, pSuperAgent)
|
|
AND sAgent = ISNULL(@sAgentId, sAgent)
|
|
AND cancelApprovedDate BETWEEN @startDate
|
|
AND @endDateNew
|
|
AND tranStatus IN ('Cancel')
|
|
AND TS.VISASTATUS IN (
|
|
'11387'
|
|
,'11418'
|
|
,'11419'
|
|
)
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'Sending Agent' head
|
|
,@sAgentName value
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'From Date' head
|
|
,@startDate value
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@endDate value
|
|
|
|
SELECT 'Transaction Report (No Work Permit)' title
|
|
END
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
|
|
SELECT 1 ERRORCODE
|
|
,ERROR_MESSAGE() MSG
|
|
,NULL ID
|
|
END CATCH
|