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.
112 lines
4.7 KiB
112 lines
4.7 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_INCOME_EXPENCES_REPORT] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[PROC_INCOME_EXPENCES_REPORT]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_INCOME_EXPENCES_REPORT] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
--EXEC [PROC_INCOME_EXPENCES_REPORT] @flag='income-exp-rpt',@startDate='2018-08-06',@endDate='2018-08-06',@user='',@party=1036
|
|
CREATE PROC [dbo].[PROC_INCOME_EXPENCES_REPORT]
|
|
(
|
|
@flag VARCHAR(20)
|
|
,@user VARCHAR(50)
|
|
,@startDate VARCHAR(10)
|
|
,@endDate VARCHAR(20)
|
|
,@party VARCHAR(20) = NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN
|
|
IF @flag = 'income-exp-rpt'
|
|
BEGIN
|
|
DECLARE @TEMPEXPTABLE TABLE ([DATE] VARCHAR(10),PARTIES VARCHAR(100), SERVICE_CHARGE MONEY, COMM_EXPENCES MONEY, FX_MARGIN MONEY
|
|
,USD_AMT MONEY,TOTAL_INCOME MONEY, CASES INT,PER_TXN_INCOME MONEY,COUNTRY VARCHAR(50),ACCNUM VARCHAR(20),TXNTYPE CHAR(1))
|
|
|
|
INSERT INTO @TEMPEXPTABLE(PARTIES,SERVICE_CHARGE,COMM_EXPENCES,FX_MARGIN,[DATE],USD_AMT,CASES)
|
|
|
|
SELECT RT.PAGENT
|
|
, SUM(RT.SERVICECHARGE)
|
|
, COMMEXPENCES = SUM(CASE WHEN PAGENTCOMMCURRENCY = 'USD' THEN RT.PAGENTCOMM * (RT.SCURRCOSTRATE + RT.SCURRHOMARGIN)
|
|
WHEN ISNULL(PAGENTCOMMCURRENCY, 'KRW') = 'KRW' THEN RT.PAGENTCOMM
|
|
ELSE RT.PAGENTCOMM / RT.customerRate END)
|
|
, 0
|
|
, [DATE] = FORMAT(RT.APPROVEDDATE,'yyyyMMdd')
|
|
, SUM(tAmt/(sCurrCostRate+sCurrHoMargin))
|
|
, COUNT(1)
|
|
FROM REMITTRAN RT (NOLOCK)
|
|
WHERE RT.approvedDate BETWEEN @startDate AND @endDate+' 23:59:59'
|
|
AND RT.PAGENT = ISNULL(@party,RT.PAGENT)
|
|
GROUP BY RT.PAGENT,FORMAT(RT.APPROVEDDATE,'yyyyMMdd')
|
|
|
|
--SELECT * FROM @TEMPEXPTABLE T
|
|
--INNER JOIN FastMoneyPro_Account.dbo.ac_Master M(NOLOCK) ON m.Agent_id = t.Parties
|
|
--INNER JOIN (
|
|
-- SELECT SUM(tran_amt) tran_amt,acc_num FROM FastMoneyPro_Account.dbo.tran_master M(NOLOCK)
|
|
-- WHERE M.field2='FOREIGN GAIN' AND M.acct_type_code IS NULL
|
|
-- AND M.tran_date BETWEEN @startDate AND @endDate+' 23:59:59'
|
|
-- GROUP BY acc_num
|
|
--)A ON m.acct_num = A.acc_num
|
|
--RETURN
|
|
UPDATE T SET T.FX_MARGIN = X.tran_amt
|
|
FROM @TEMPEXPTABLE T
|
|
INNER JOIN(
|
|
SELECT SUM(CASE WHEN M.part_tran_type='DR' THEN tran_amt ELSE tran_amt*-1 END) tran_amt,A.AGENT_ID FROM FastMoneyPro_Account.dbo.tran_master M(NOLOCK)
|
|
INNER JOIN FastMoneyPro_Account.dbo.ac_Master A(NOLOCK) ON A.ACCT_NUM = M.ACC_NUM
|
|
WHERE M.field2 IN ('FOREIGN GAIN','FOREIGN GAIN FCY')
|
|
AND M.tran_date BETWEEN @startDate AND @endDate+' 23:59:59'
|
|
GROUP BY A.AGENT_ID
|
|
)X ON X.AGENT_ID = T.PARTIES
|
|
|
|
|
|
INSERT INTO @TEMPEXPTABLE(PARTIES,SERVICE_CHARGE,COMM_EXPENCES,FX_MARGIN,[DATE],USD_AMT,CASES)
|
|
SELECT RT.PAGENT
|
|
, SUM(RT.SERVICECHARGE) *-1
|
|
, COMMEXPENCES = SUM(CASE WHEN PAGENTCOMMCURRENCY = 'USD' THEN RT.PAGENTCOMM * (RT.SCURRCOSTRATE + RT.SCURRHOMARGIN)
|
|
WHEN ISNULL(PAGENTCOMMCURRENCY, 'KRW') = 'KRW' THEN RT.PAGENTCOMM
|
|
ELSE RT.PAGENTCOMM / RT.customerRate END) *-1
|
|
, 0
|
|
, [DATE] = FORMAT(RT.cancelApprovedDate,'yyyyMMdd')
|
|
, SUM(tAmt/(sCurrCostRate+sCurrHoMargin)) *-1
|
|
, COUNT(1) *-1
|
|
|
|
FROM REMITTRAN RT (NOLOCK)
|
|
WHERE RT.cancelApprovedDate BETWEEN @startDate AND @endDate+' 23:59:59'
|
|
AND RT.PAGENT = ISNULL(@party,RT.PAGENT)
|
|
GROUP BY RT.PAGENT,FORMAT(RT.cancelApprovedDate,'yyyyMMdd')
|
|
|
|
UPDATE @TEMPEXPTABLE SET PER_TXN_INCOME = (SERVICE_CHARGE-COMM_EXPENCES+FX_MARGIN)/CASES,TOTAL_INCOME = (SERVICE_CHARGE-COMM_EXPENCES+FX_MARGIN)
|
|
|
|
UPDATE T SET T.PARTIES = M.AGENTNAME FROM @TEMPEXPTABLE T
|
|
INNER JOIN AGENTMASTER M(NOLOCK) ON M.AGENTID = T.PARTIES
|
|
|
|
--SELECT * FROM @TEMPEXPTABLE
|
|
--RETURN
|
|
|
|
--SELECT [DATE],PARTIES,SERVICE_CHARGE,COMM_EXPENCES,FX_MARGIN,CASES,USD_AMT,TOTAL_INCOME,PER_TXN_INCOME
|
|
--FROM @TEMPEXPTABLE
|
|
|
|
select PARTIES,SUM(SERVICE_CHARGE) SERVICE_CHARGE,SUM(COMM_EXPENCES) COMM_EXPENCES,SUM(FX_MARGIN) FX_MARGIN,SUM(CASES) CASES,SUM(USD_AMT) USD_AMT
|
|
,SUM(TOTAL_INCOME) TOTAL_INCOME,SUM(TOTAL_INCOME)/SUM(CASES) PER_TXN_INCOME
|
|
FROM(
|
|
SELECT PARTIES PARTIES,SUM(SERVICE_CHARGE) SERVICE_CHARGE,SUM(COMM_EXPENCES) COMM_EXPENCES,(FX_MARGIN) FX_MARGIN,SUM(CASES) CASES,SUM(USD_AMT) USD_AMT
|
|
,TOTAL_INCOME = SUM(SERVICE_CHARGE-COMM_EXPENCES)+FX_MARGIN,0 PER_TXN_INCOME
|
|
FROM @TEMPEXPTABLE
|
|
group by PARTIES,FX_MARGIN
|
|
)X GROUP BY PARTIES
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
select 'Party' head, CASE WHEN @party IS NULL THEN 'All' ELSE (SELECT agentName FROM agentMaster (NOLOCK) WHERE agentId = @party) END UNION ALL
|
|
SELECT 'From Date' head, @startDate value UNION ALL
|
|
SELECT 'To Date' head, @endDate value
|
|
|
|
|
|
SELECT 'PARTNER INCOME EXPENCES REPORT' title
|
|
END
|
|
END
|
|
GO
|