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
2.8 KiB
79 lines
2.8 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_ExcludeCancelTXN] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[proc_ExcludeCancelTXN]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_ExcludeCancelTXN] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE procEDURE [dbo].[proc_ExcludeCancelTXN]
|
|
@FLAG VARCHAR(20),
|
|
@FROMDATE VARCHAR(20) = NULL,
|
|
@TODATE VARCHAR(30) = NULL,
|
|
@pageSize VARCHAR(50) = NULL,
|
|
@pageNumber VARCHAR(50) = NULL,
|
|
@USER VARCHAR(50) = NULL,
|
|
@pCountry VARCHAR(20) = NULL,
|
|
@sAgent VARCHAR(50) = NULL,
|
|
@sBranch VARCHAR(20) = NULL
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET ANSI_NULLS ON;
|
|
|
|
SET @pCountry='South Korea'
|
|
|
|
IF @FLAG = 'settlement'
|
|
BEGIN
|
|
IF OBJECT_ID(N'tempdb..#SETTLEMENT') IS NOT NULL
|
|
DROP TABLE #SETTLEMENT
|
|
|
|
SELECT
|
|
sCurrCostRate,sCurrHoMargin
|
|
,serviceCharge = CASE WHEN PAGENT = 221227 THEN 0 ELSE serviceCharge END
|
|
,sAgentComm,sCountry,sAgent,sBranch,pCountry
|
|
,tAmt,cAmt,pAmt,tranStatus
|
|
,approvedDate = CAST(approvedDate AS DATE)
|
|
INTO #SETTLEMENT
|
|
FROM remitTran WITH (NOLOCK)
|
|
WHERE 1=1
|
|
AND
|
|
(
|
|
(SAGENT=ISNULL(@sAgent, SAGENT) AND APPROVEDDATE BETWEEN @fromDate AND @toDate + ' 23:59:59' AND sCountry = isnull(@pCountry,sCountry) AND sBranch = isnull(@sBranch,sBranch))
|
|
OR (SAGENT=ISNULL(@sAgent, SAGENT) AND CANCELAPPROVEDDATE BETWEEN @fromDate AND @toDate + ' 23:59:59' AND sCountry = isnull(@pCountry,sCountry) AND sBranch = isnull(@sBranch,sBranch))
|
|
)
|
|
|
|
DELETE FROM #SETTLEMENT WHERE tranStatus ='CANCEL'
|
|
|
|
SELECT --[S No] = ROW_NUMBER() OVER (ORDER BY convert(varchar,approvedDate,101))
|
|
[Date] = CONVERT(varchar,approvedDate,101)
|
|
,[CountryName] = pCountry
|
|
,[Total Trans] = COUNT(1)
|
|
,[IN USD_Total Collection] = SUM(cAmt/(sCurrCostRate+ISNULL(sCurrHoMargin,0)))
|
|
,[IN USD_Total Charge] = SUM(serviceCharge/sCurrCostRate)
|
|
,[IN USD_Principal Amount] = SUM(tAmt/(sCurrCostRate+ISNULL(sCurrHoMargin,0)))
|
|
,[IN USD_Agent <br /> Commission] = SUM(sAgentComm/(sCurrCostRate+ISNULL(sCurrHoMargin,0)))
|
|
,[IN USD_Settlement Amount] = SUM((cAmt - ISNULL(sAgentComm,0))/(sCurrCostRate+ISNULL(sCurrHoMargin,0)))
|
|
--,[IN Paying_Principal Amount] = SUM(pAmt)
|
|
FROM #SETTLEMENT
|
|
--WHERE 1=1 AND SAGENT = ISNULL(@sAgent, SAGENT)
|
|
--AND APPROVEDDATE BETWEEN @fromDate AND @toDate + ' 23:59:59'
|
|
GROUP BY CONVERT(varchar,approvedDate,101),pCountry
|
|
ORDER BY [Date]
|
|
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
SELECT 'From Date' head,@fromDate VALUE
|
|
UNION ALL
|
|
SELECT 'To Date' head,@toDate VALUE
|
|
|
|
SELECT 'TRANSACTION EXCLUDING CANCELLATION' title
|
|
RETURN
|
|
|
|
|
|
END
|
|
|
|
GO
|