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.
391 lines
34 KiB
391 lines
34 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_REFERRAL_REPORT] Script Date: 4/3/2024 12:23:25 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
--EXEC PROC_REFERRAL_REPORT @FLAG = 'SS', @FROM_DATE = '2019-12-01', @TO_DATE = '2020-12-31'
|
|
|
|
ALTER PROC [dbo].[PROC_REFERRAL_REPORT]
|
|
(
|
|
@FLAG VARCHAR(50) = NULL
|
|
,@user VARCHAR(20) = NULL
|
|
,@FROM_DATE VARCHAR(25) = NULL
|
|
,@TO_DATE VARCHAR(25) = NULL
|
|
,@REFERRAL_CODE INT = NULL
|
|
,@controlNo varchar(50) = NULL
|
|
,@tranNo bigint = null
|
|
)
|
|
AS
|
|
;
|
|
--#298 Trim white space of Control number and TranId
|
|
--#479 how only collection mode cash @FLAG = 'SUMMARY' & 'staffvisitearnings'
|
|
-- #700 - Count MisMactch Referral Report , @Flag = 'SS'
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN
|
|
IF @FLAG = 'S'
|
|
BEGIN
|
|
SELECT R.REFERRAL_NAME , PARTNER_ID, COMMISSION_PCNT, SUM(PAID_COMMISSION) PAID_COMMISSION, FX_PCNT, SUM(PAID_FX) PAID_FX, FLAT_RATE
|
|
, SUM(PAID_FLAT) PAID_FLAT, PAID_NEW_CUSTOMER_RATE, SUM(PAID_NEW_CUSTOMER) PAID_NEW_CUSTOMER, COUNT(1) [NO_OF_TXN], CONVERT(CHAR(10), T.TXN_DATE,126) [DATE]
|
|
FROM REFERRAL_INCENTIVE_TRANSACTION_WISE T(NOLOCK)
|
|
INNER JOIN REFERRAL_AGENT_WISE R(NOLOCK) ON R.ROW_ID = T.REFERRAL_ID
|
|
WHERE T.TXN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
AND R.ROW_ID = ISNULL(@REFERRAL_CODE, R.ROW_ID)
|
|
AND R.IS_ACTIVE = 1
|
|
GROUP BY PARTNER_ID, COMMISSION_PCNT, FX_PCNT, FLAT_RATE, PAID_NEW_CUSTOMER_RATE, R.REFERRAL_NAME , CONVERT(char(10), T.TXN_DATE,126)
|
|
ORDER BY [DATE], R.REFERRAL_NAME
|
|
END
|
|
IF @FLAG = 'SUMMARY'
|
|
BEGIN
|
|
SELECT R.REFERRAL_NAME, ISNULL(RT.SENDERNAME,cm.fullName) SENDERNAME, PARTNER_ID, COMMISSION_PCNT, PAID_COMMISSION, FX_PCNT, PAID_FX, FLAT_RATE,ISNULL(RT.serviceCharge,0) serviceCharge,ISNULL(T.TAX_AMOUNT,0) TAX_AMOUNT
|
|
, CONTROLNO = '<a href="javascript:void(0)" onclick="OpenInNewWindow(''/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId='+CAST(RT.ID AS VARCHAR)+''')">'+dbo.FNADecryptString(controlNo) +'</a>'
|
|
, PAID_FLAT, PAID_NEW_CUSTOMER_RATE, PAID_NEW_CUSTOMER, ISNULL(CONVERT(CHAR(10), T.TXN_DATE,126), CONVERT( CHAR(10), cm.createdDate, 126)) [DATE]
|
|
, ISNULL(RT.CAMT, 0) CAMT
|
|
, T.REFERRAL_TYPE
|
|
INTO #STAFF_VISIT_REPORT_SUMMARY
|
|
FROM REFERRAL_INCENTIVE_TRANSACTION_WISE T(NOLOCK)
|
|
INNER JOIN REFERRAL_AGENT_WISE R(NOLOCK) ON R.REFERRAL_CODE = T.REFERRAL_ID
|
|
LEFT JOIN customerMaster cm(NOLOCK) ON cm.customerId = T.CUSTOMER_ID
|
|
LEFT JOIN REMITTRAN RT(NOLOCK) ON RT.ID = T.TRAN_ID AND T.TXN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
-- AND RT.COLLMODE = 'CASH COLLECT'
|
|
WHERE
|
|
R.ROW_ID = ISNULL(@REFERRAL_CODE, R.ROW_ID)
|
|
AND R.IS_ACTIVE = 1
|
|
|
|
UPDATE #STAFF_VISIT_REPORT_SUMMARY SET PARTNER_ID = 393880 WHERE PARTNER_ID = 394397
|
|
|
|
SELECT * FROM #STAFF_VISIT_REPORT_SUMMARY
|
|
ORDER BY [DATE], REFERRAL_NAME
|
|
END
|
|
IF @FLAG = 'staffVisit'
|
|
BEGIN
|
|
SELECT R.REFERRAL_NAME, RT.SENDERNAME , PARTNER_ID, COMMISSION_PCNT, PAID_COMMISSION, FX_PCNT, PAID_FX, FLAT_RATE,RT.serviceCharge,ISNULL(T.TAX_AMOUNT,0) TAX_AMOUNT
|
|
, CONTROLNO = '<a href="javascript:void(0)" onclick="OpenInNewWindow(''/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId='+CAST(RT.ID AS VARCHAR)+''')">'+dbo.FNADecryptString(controlNo) +'</a>'
|
|
, PAID_FLAT, PAID_NEW_CUSTOMER_RATE, PAID_NEW_CUSTOMER, CONVERT(CHAR(10), T.TXN_DATE,126) [DATE]
|
|
FROM REFERRAL_INCENTIVE_TRANSACTION_WISE T(NOLOCK)
|
|
INNER JOIN REFERRAL_AGENT_WISE R(NOLOCK) ON R.ROW_ID = T.REFERRAL_ID
|
|
INNER JOIN REMITTRAN RT(NOLOCK) ON RT.ID = T.TRAN_ID
|
|
WHERE T.TXN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
AND R.ROW_ID = ISNULL(@REFERRAL_CODE, R.ROW_ID)
|
|
AND STAFF_VISIT = 1
|
|
AND R.IS_ACTIVE = 1
|
|
ORDER BY [DATE], R.REFERRAL_NAME
|
|
END
|
|
IF @FLAG = 'SS'
|
|
BEGIN
|
|
CREATE TABLE #AGENT_COMM(REFERRAL_NAME VARCHAR(200), [NO_OF_TXN] VARCHAR(200), SERVICE_CHARGE MONEY, PAID_COMMISSION MONEY, PAID_FX MONEY
|
|
, PAID_FLAT MONEY, PAID_NEW_CUSTOMER MONEY, TAX_DEDUCTION MONEY, [TOTAL] MONEY, ROW_ID INT, [TYPE] CHAR(1))
|
|
|
|
INSERT INTO #AGENT_COMM(REFERRAL_NAME, ROW_ID, [TYPE])
|
|
SELECT REFERRAL_NAME, ROW_ID, 'R'
|
|
FROM REFERRAL_AGENT_WISE R(NOLOCK)
|
|
WHERE AGENT_ID = 0
|
|
AND R.IS_ACTIVE = 1
|
|
AND ROW_ID = ISNULL(@REFERRAL_CODE, R.ROW_ID)
|
|
|
|
UNION ALL
|
|
|
|
SELECT DISTINCT A.ACCT_NAME, A.ACCT_ID, 'A'
|
|
FROM FASTMONEYPRO_ACCOUNT.DBO.AC_MASTER A(NOLOCK)
|
|
WHERE A.ACCT_RPT_CODE = 'ACP'
|
|
|
|
|
|
UPDATE AC SET AC.[NO_OF_TXN] = Y.[NO_OF_TXN], AC.SERVICE_CHARGE = Y.SERVICE_CHARGE, AC.PAID_COMMISSION = Y.PAID_COMMISSION, AC.PAID_FX = Y.PAID_FX
|
|
, AC.PAID_FLAT = Y.PAID_FLAT, AC.PAID_NEW_CUSTOMER = Y.PAID_NEW_CUSTOMER, AC.TAX_DEDUCTION = Y.TAX_DEDUCTION, AC.[TOTAL] = Y.[TOTAL]
|
|
FROM(SELECT REFERRAL_NAME,--+'('+CAST(SUM(CASH_TXN - CASH_CANCEL) AS VARCHAR) + ')'
|
|
'<a href="/Remit/Transaction/Reports/ReferralReport/ReferralReportComm.aspx?fromDate='+@FROM_DATE+'&toDate='+@TO_DATE+'&referralCode='+ISNULL(CAST(ROW_ID AS VARCHAR), '')+'">'
|
|
+CAST(SUM(SEND_TXN - CANCEL_TXN) AS VARCHAR)+
|
|
'</a> (SEND: '+ CAST(SUM(SEND_TXN) AS VARCHAR)+' CANCEL: '+CAST(SUM(CANCEL_TXN) AS VARCHAR)+' ) CASH TXN: '+CAST(SUM(CASH_TXN - CASH_CANCEL) AS VARCHAR) [NO_OF_TXN]
|
|
,SUM(SERVICE_CHARGE) SERVICE_CHARGE
|
|
,SUM(PAID_COMMISSION) PAID_COMMISSION, SUM(PAID_FX) PAID_FX
|
|
,SUM(PAID_FLAT) PAID_FLAT, SUM(PAID_NEW_CUSTOMER) PAID_NEW_CUSTOMER
|
|
,SUM(TAX_AMOUNT) TAX_DEDUCTION
|
|
,SUM([TOTAL]) [TOTAL]
|
|
,ROW_ID
|
|
,[TYPE]
|
|
FROM (
|
|
SELECT R.REFERRAL_NAME
|
|
,R.ROW_ID
|
|
, SERVICE_CHARGE = SUM(CASE WHEN IS_CANCEL = 1 THEN -1 * RR.SERVICECHARGE ELSE RR.SERVICECHARGE END)
|
|
, SEND_TXN = SUM(CASE WHEN IS_CANCEL = 0 THEN 1 ELSE 0 END )
|
|
--, SEND_TXN = SUM(CASE WHEN IS_CANCEL = 0 AND RR.COLLMODE = 'CASH COLLECT' THEN 1 ELSE 0 END)
|
|
, CANCEL_TXN = SUM(CASE WHEN IS_CANCEL = 1 THEN 1 ELSE 0 END)
|
|
, CASH_TXN = SUM(CASE WHEN RR.COLLMODE = 'CASH COLLECT' THEN 1 ELSE 0 END)
|
|
, CASH_CANCEL = SUM(CASE WHEN IS_CANCEL = 1 AND RR.COLLMODE = 'CASH COLLECT' THEN 1 ELSE 0 END)
|
|
--, CONVERT(CHAR(10), T.TXN_DATE,126) [DATE]
|
|
, SUM(PAID_COMMISSION) PAID_COMMISSION, SUM(PAID_FX) PAID_FX
|
|
, SUM(PAID_FLAT) PAID_FLAT, SUM(PAID_NEW_CUSTOMER) PAID_NEW_CUSTOMER
|
|
, SUM(TAX_AMOUNT) TAX_AMOUNT
|
|
, SUM(PAID_COMMISSION)+SUM(PAID_FX)+SUM(PAID_FLAT)+SUM(PAID_NEW_CUSTOMER) [TOTAL]
|
|
, [TYPE] = 'R'
|
|
FROM REFERRAL_INCENTIVE_TRANSACTION_WISE T(NOLOCK)
|
|
INNER JOIN REMITTRAN RR(NOLOCK) ON RR.ID = T.TRAN_ID
|
|
INNER JOIN REFERRAL_AGENT_WISE R(NOLOCK) ON R.ROW_ID = T.REFERRAL_ID
|
|
WHERE T.TXN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
AND R.ROW_ID = ISNULL(@REFERRAL_CODE, R.ROW_ID)
|
|
AND AGENT_ID = 0
|
|
AND R.IS_ACTIVE = 1
|
|
GROUP BY R.REFERRAL_NAME, R.ROW_ID, T.IS_CANCEL--, CONVERT(char(10), T.TXN_DATE,126)
|
|
|
|
UNION ALL
|
|
|
|
SELECT REFERRAL_NAME = A.ACCT_NAME
|
|
,ROW_ID = A.ACCT_ID
|
|
,SERVICE_CHARGE = 0
|
|
,SEND_TXN = SUM(CASE WHEN PART_TRAN_TYPE = 'CR' THEN 1 ELSE 0 END)
|
|
,CANCEL_TXN = SUM(CASE WHEN PART_TRAN_TYPE = 'DR' THEN 1 ELSE 0 END)
|
|
,CASH_TXN = 0
|
|
, CASH_CANCEL = 0
|
|
,PAID_COMMISSION = 0
|
|
,PAID_FX = 0, PAID_FLAT = 0, PAID_NEW_CUSTOMER = 0
|
|
,TAX_AMOUNT = 0
|
|
,TOTAL = SUM(CASE WHEN PART_TRAN_TYPE = 'CR' THEN TRAN_AMT ELSE -1 * TRAN_AMT END)
|
|
, [TYPE] = 'A'
|
|
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 A.ACCT_RPT_CODE = 'ACP'
|
|
AND FIELD2 = 'REMITTANCE VOUCHER'
|
|
AND M.TRAN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
GROUP BY A.ACCT_NAME, A.ACCT_ID
|
|
)X
|
|
GROUP BY REFERRAL_NAME, ROW_ID, [TYPE]
|
|
--ORDER BY REFERRAL_NAME--, [DATE]
|
|
)Y
|
|
INNER JOIN #AGENT_COMM AC ON AC.ROW_ID = Y.ROW_ID AND AC.[TYPE] = Y.[TYPE]
|
|
|
|
|
|
CREATE TABLE #ACCOUNT([TYPE] CHAR(1), ACC_NUM VARCHAR(30), ROW_ID INT)
|
|
|
|
INSERT INTO #ACCOUNT
|
|
SELECT A.[TYPE], AC.acct_num, A.ROW_ID
|
|
FROM #AGENT_COMM A
|
|
INNER JOIN FastMoneyPro_Account.DBO.ac_master AC(NOLOCK) ON AC.agent_id = A.ROW_ID AND AC.acct_rpt_code = 'RAC'
|
|
WHERE A.[TYPE] = 'R'
|
|
|
|
UNION ALL
|
|
|
|
SELECT A.[TYPE], AC.acct_num, A.ROW_ID
|
|
FROM #AGENT_COMM A
|
|
INNER JOIN FastMoneyPro_Account.DBO.ac_master AC(NOLOCK) ON AC.ACCT_ID = A.ROW_ID
|
|
WHERE A.[TYPE] = 'A'
|
|
|
|
SELECT ROW_ID, [TYPE], RUNNING_BAL = ISNULL(SUM(case when part_tran_type='dr' then tran_amt*-1 else tran_amt end), 0)
|
|
INTO #AGENT_RUNNING_BAL
|
|
FROM #ACCOUNT A
|
|
INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER M(NOLOCK) ON M.ACC_NUM = A.ACC_NUM
|
|
WHERE M.TRAN_DATE <= @TO_DATE + ' 23:59:59'
|
|
GROUP BY ROW_ID, [TYPE]
|
|
|
|
SELECT ROW_ID, [TYPE], COMM_PAID = SUM(M.TRAN_AMT)
|
|
INTO #AGENT_COMM_PAID
|
|
FROM #ACCOUNT A
|
|
INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER M(NOLOCK) ON M.ACC_NUM = A.ACC_NUM
|
|
WHERE M.TRAN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
AND ISNULL(M.field2, '') <> 'Remittance Voucher'
|
|
AND M.part_tran_type = 'DR'
|
|
GROUP BY ROW_ID, [TYPE]
|
|
|
|
SELECT REFERRAL_NAME
|
|
,[NO_OF_TXN]
|
|
, SERVICE_CHARGE
|
|
, PAID_COMMISSION, PAID_FX
|
|
, PAID_FLAT, PAID_NEW_CUSTOMER
|
|
, TAX_DEDUCTION
|
|
, [TOTAL]
|
|
, B.RUNNING_BAL
|
|
, ISNULL(M.COMM_PAID, 0) PAID_COMM
|
|
FROM #AGENT_COMM A
|
|
INNER JOIN #AGENT_RUNNING_BAL B ON B.ROW_ID = A.ROW_ID AND B.[TYPE] = A.[TYPE]
|
|
LEFT JOIN #AGENT_COMM_PAID M ON M.ROW_ID = A.ROW_ID AND M.[TYPE] = A.[TYPE]
|
|
ORDER BY REFERRAL_NAME
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
SELECT 'From Date' head, @FROM_DATE UNION ALL
|
|
SELECT 'To Date' head, @TO_DATE VALUE UNION ALL
|
|
SELECT 'Referral Name' head, ISNULL((SELECT REFERRAL_NAME FROM REFERRAL_AGENT_WISE REF (NOLOCK) WHERE ROW_ID = @REFERRAL_CODE AND REF.IS_ACTIVE = 1), 'All') VALUE
|
|
|
|
SELECT 'Summary Referral Report' title
|
|
END
|
|
IF @FLAG = 'staffvisitearnings'
|
|
BEGIN
|
|
SELECT REFERRAL_NAME,--+'('+CAST(SUM(CASH_TXN - CASH_CANCEL) AS VARCHAR) + ')'
|
|
'<a href="/Remit/Transaction/Reports/ReferralReport/ReferralReportComm.aspx?fromDate='+@FROM_DATE+'&toDate='+@TO_DATE+'&referralCode='+ISNULL(CAST(ROW_ID AS VARCHAR), '')+'">'
|
|
+CAST(SUM(SEND_TXN - CANCEL_TXN) AS VARCHAR)+
|
|
'</a> (SEND: '+ CAST(SUM(SEND_TXN) AS VARCHAR)+' CANCEL: '+CAST(SUM(CANCEL_TXN) AS VARCHAR)+' ) CASH TXN: '+CAST(SUM(CASH_TXN - CASH_CANCEL) AS VARCHAR) [NO_OF_TXN]
|
|
,SUM(SERVICE_CHARGE) SERVICE_CHARGE
|
|
,SUM(PAID_COMMISSION) PAID_COMMISSION, SUM(PAID_FX) PAID_FX
|
|
,SUM(PAID_FLAT) PAID_FLAT, SUM(PAID_NEW_CUSTOMER) PAID_NEW_CUSTOMER
|
|
,SUM(TAX_AMOUNT) TAX_DEDUCTION
|
|
,SUM([TOTAL]) [TOTAL]
|
|
,ROW_ID
|
|
,[TYPE]
|
|
INTO #AGENT_COMM_NEW
|
|
FROM (
|
|
SELECT R.REFERRAL_NAME
|
|
,R.ROW_ID
|
|
, SERVICE_CHARGE = SUM(CASE WHEN IS_CANCEL = 1 THEN -1 * RR.SERVICECHARGE ELSE RR.SERVICECHARGE END)
|
|
, SEND_TXN = SUM(CASE WHEN IS_CANCEL = 0 THEN 1 ELSE 0 END)
|
|
, CANCEL_TXN = SUM(CASE WHEN IS_CANCEL = 1 THEN 1 ELSE 0 END)
|
|
, CASH_TXN = SUM(CASE WHEN RR.COLLMODE = 'CASH COLLECT' THEN 1 ELSE 0 END)
|
|
, CASH_CANCEL = SUM(CASE WHEN IS_CANCEL = 1 AND RR.COLLMODE = 'CASH COLLECT' THEN 1 ELSE 0 END)
|
|
--, CONVERT(CHAR(10), T.TXN_DATE,126) [DATE]
|
|
, SUM(PAID_COMMISSION) PAID_COMMISSION, SUM(PAID_FX) PAID_FX
|
|
, SUM(PAID_FLAT) PAID_FLAT, SUM(PAID_NEW_CUSTOMER) PAID_NEW_CUSTOMER
|
|
, SUM(TAX_AMOUNT) TAX_AMOUNT
|
|
, SUM(PAID_COMMISSION)+SUM(PAID_FX)+SUM(PAID_FLAT)+SUM(PAID_NEW_CUSTOMER) [TOTAL]
|
|
, [TYPE] = 'R'
|
|
FROM REFERRAL_INCENTIVE_TRANSACTION_WISE T(NOLOCK)
|
|
INNER JOIN REMITTRAN RR(NOLOCK) ON RR.ID = T.TRAN_ID
|
|
INNER JOIN REFERRAL_AGENT_WISE R(NOLOCK) ON R.ROW_ID = T.REFERRAL_ID
|
|
WHERE T.TXN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
--AND RR.COLLMODE = 'CASH COLLECT'
|
|
AND R.ROW_ID = ISNULL(@REFERRAL_CODE, R.ROW_ID)
|
|
AND AGENT_ID = 0
|
|
AND STAFF_VISIT = 1
|
|
AND R.IS_ACTIVE = 1
|
|
GROUP BY R.REFERRAL_NAME, R.ROW_ID, T.IS_CANCEL--, CONVERT(char(10), T.TXN_DATE,126)
|
|
|
|
--UNION ALL
|
|
|
|
--SELECT REFERRAL_NAME = A.ACCT_NAME
|
|
-- ,ROW_ID = A.ACCT_ID
|
|
-- ,SERVICE_CHARGE = 0
|
|
-- ,SEND_TXN = SUM(CASE WHEN PART_TRAN_TYPE = 'CR' THEN 1 ELSE 0 END)
|
|
-- ,CANCEL_TXN = SUM(CASE WHEN PART_TRAN_TYPE = 'DR' THEN 1 ELSE 0 END)
|
|
-- , CASH_TXN = 0
|
|
-- , CASH_CANCEL = 0
|
|
-- ,PAID_COMMISSION = 0
|
|
-- ,PAID_FX = 0, PAID_FLAT = 0, PAID_NEW_CUSTOMER = 0
|
|
-- ,TAX_AMOUNT = 0
|
|
-- ,TOTAL = SUM(CASE WHEN PART_TRAN_TYPE = 'CR' THEN TRAN_AMT ELSE -1 * TRAN_AMT END)
|
|
-- , [TYPE] = 'A'
|
|
--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 A.ACCT_RPT_CODE = 'ACP'
|
|
--AND FIELD2 = 'REMITTANCE VOUCHER'
|
|
--AND M.TRAN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
--GROUP BY A.ACCT_NAME, A.ACCT_ID
|
|
)X
|
|
GROUP BY REFERRAL_NAME, ROW_ID, [TYPE]
|
|
ORDER BY REFERRAL_NAME--, [DATE]
|
|
|
|
|
|
CREATE TABLE #ACCOUNT_NEW([TYPE] CHAR(1), ACC_NUM VARCHAR(30), ROW_ID INT)
|
|
|
|
INSERT INTO #ACCOUNT_NEW
|
|
SELECT A.[TYPE], AC.acct_num, A.ROW_ID
|
|
FROM #AGENT_COMM_NEW A
|
|
INNER JOIN FastMoneyPro_Account.DBO.ac_master AC(NOLOCK) ON AC.agent_id = A.ROW_ID AND AC.acct_rpt_code = 'RAC'
|
|
WHERE A.[TYPE] = 'R'
|
|
|
|
UNION ALL
|
|
|
|
SELECT A.[TYPE], AC.acct_num, A.ROW_ID
|
|
FROM #AGENT_COMM_NEW A
|
|
INNER JOIN FastMoneyPro_Account.DBO.ac_master AC(NOLOCK) ON AC.ACCT_ID = A.ROW_ID
|
|
WHERE A.[TYPE] = 'A'
|
|
|
|
SELECT ROW_ID, [TYPE], RUNNING_BAL = ISNULL(SUM(case when part_tran_type='dr' then tran_amt*-1 else tran_amt end), 0)
|
|
INTO #AGENT_RUNNING_BAL_NEW
|
|
FROM #ACCOUNT_NEW A
|
|
INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER M(NOLOCK) ON M.ACC_NUM = A.ACC_NUM
|
|
WHERE M.TRAN_DATE <= @TO_DATE + ' 23:59:59'
|
|
GROUP BY ROW_ID, [TYPE]
|
|
|
|
SELECT ROW_ID, [TYPE], COMM_PAID = SUM(M.TRAN_AMT)
|
|
INTO #AGENT_COMM_PAID_NEW
|
|
FROM #ACCOUNT_NEW A
|
|
INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER M(NOLOCK) ON M.ACC_NUM = A.ACC_NUM
|
|
WHERE M.TRAN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
AND ISNULL(M.field2, '') <> 'Remittance Voucher'
|
|
AND M.part_tran_type = 'DR'
|
|
GROUP BY ROW_ID, [TYPE]
|
|
|
|
SELECT REFERRAL_NAME
|
|
,[NO_OF_TXN]
|
|
, SERVICE_CHARGE
|
|
, PAID_COMMISSION, PAID_FX
|
|
, PAID_FLAT, PAID_NEW_CUSTOMER
|
|
, TAX_DEDUCTION
|
|
, [TOTAL]
|
|
, B.RUNNING_BAL
|
|
, ISNULL(M.COMM_PAID, 0) PAID_COMM
|
|
FROM #AGENT_COMM_NEW A
|
|
INNER JOIN #AGENT_RUNNING_BAL_NEW B ON B.ROW_ID = A.ROW_ID AND B.[TYPE] = A.[TYPE]
|
|
LEFT JOIN #AGENT_COMM_PAID_NEW M ON M.ROW_ID = A.ROW_ID AND M.[TYPE] = A.[TYPE]
|
|
ORDER BY REFERRAL_NAME
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
SELECT 'From Date' head, @FROM_DATE UNION ALL
|
|
SELECT 'To Date' head, @TO_DATE VALUE UNION ALL
|
|
SELECT 'Referral Name' head, ISNULL((SELECT REFERRAL_NAME FROM REFERRAL_AGENT_WISE REF (NOLOCK) WHERE ROW_ID = @REFERRAL_CODE AND STAFF_VISIT = 1 AND REF.IS_ACTIVE = 1), 'All') VALUE
|
|
|
|
SELECT 'Staff Visit-Earnings' title
|
|
END
|
|
IF @FLAG = 'referralName'
|
|
BEGIN
|
|
SELECT ROW_ID valueId, REFERRAL_NAME detailTitle FROM REFERRAL_AGENT_WISE REF (NOLOCK)
|
|
WHERE REF.IS_ACTIVE = 1
|
|
END
|
|
IF @FLAG = 'referralName-staffVisit'
|
|
BEGIN
|
|
SELECT ROW_ID valueId, REFERRAL_NAME detailTitle FROM REFERRAL_AGENT_WISE REF (NOLOCK)
|
|
where staff_visit = 1
|
|
AND REF.IS_ACTIVE = 1
|
|
END
|
|
IF @FLAG = 'referralNameWithReferralCode'
|
|
BEGIN
|
|
SELECT REFERRAL_CODE valueId, REFERRAL_NAME detailTitle FROM REFERRAL_AGENT_WISE REF (NOLOCK)
|
|
WHERE REF.IS_ACTIVE = 1
|
|
END
|
|
IF @FLAG = 'activeReferralNameWithReferralCode'
|
|
BEGIN
|
|
SELECT REFERRAL_CODE valueId, REFERRAL_NAME detailTitle FROM REFERRAL_AGENT_WISE REF (NOLOCK)
|
|
where staff_visit = 1
|
|
AND REF.IS_ACTIVE = 1
|
|
END
|
|
IF @FLAG = 'referralNameWithAccountNo'
|
|
BEGIN
|
|
SELECT acct_num valueId,REFERRAL_NAME detailTitle from fastmoneypro_account.dbo.ac_master (NOLOCK) ACC
|
|
INNER JOIN REFERRAL_AGENT_WISE (NOLOCK) REF ON REF.REFERRAL_CODE = ACC.ACCT_NAME
|
|
WHERE gl_code = 0 and acct_rpt_code = 'RA'
|
|
AND REF.IS_ACTIVE = 1
|
|
END
|
|
IF @FLAG = 'checkReferral'
|
|
BEGIN
|
|
select ra.REFERRAL_NAME,vrt.collMode [Deposit Type],vrt.cAmt [Collect Amount],vrt.createdDate [Created Date] from vwRemitTran vrt (nolock)
|
|
INNER JOIN REFERRAL_AGENT_WISE ra (nolock) on ra.REFERRAL_CODE = vrt.promotionCode
|
|
where vrt.controlNo = dbo.FNAEncryptString(ISNULL(LTRIM(RTRIM(@controlNo)),''))
|
|
AND RA.IS_ACTIVE = 1
|
|
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
SELECT 'Control No' head, @controlNo value
|
|
|
|
SELECT 'Check Referral Report' title
|
|
|
|
|
|
|
|
END
|
|
END
|
|
--REFERRAL_INCENTIVE_TRANSACTION_WISE_TMP
|
|
--exec PROC_REFERRAL_REPORT @flag = 's', @FROM_DATE = '2019-02-25', @TO_DATE = '2019-02-25'
|
|
|
|
|
|
--SELECT DISTINCT A.ACCT_RPT_CODE
|
|
--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 A.ACCT_RPT_CODE = 'ACP'
|
|
--AND FIELD2 = 'REMITTANCE VOUCHER'
|
|
----AND M.TRAN_DATE BETWEEN @FROM_DATE AND @TO_DATE + ' 23:59:59'
|
|
--GROUP BY A.ACCT_NAME, A.ACCT_ID
|
|
|
|
|
|
|
|
|
|
|