|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_CASA_ByRefId]
|
|
3
|
@p_REF_ID VARCHAR(200) = NULL,
|
|
4
|
@p_BRANCH_ID VARCHAR(50) = NULL,
|
|
5
|
@RefNo VARCHAR(500) = NULL
|
|
6
|
AS
|
|
7
|
DECLARE @l_SUM_AMT DECIMAL,
|
|
8
|
@l_REF_NO VARCHAR(20),
|
|
9
|
@l_ASSET_ID VARCHAR(20);
|
|
10
|
DECLARE @l_TRN_TYPE VARCHAR(50) = '';
|
|
11
|
DECLARE @l_CASA_CR_ACCT VARCHAR(20) = '';
|
|
12
|
DECLARE @l_CASA_DR_ACCT VARCHAR(20) = '';
|
|
13
|
DECLARE @l_CASA_CR_ACCT_NAME NVARCHAR(500) = '';
|
|
14
|
DECLARE @l_CASA_DR_ACCT_NAME NVARCHAR(500) = '';
|
|
15
|
|
|
16
|
DECLARE @MW_ENTRI TABLE (ET_ID varchar(15), REF_NO varchar(20), TRN_ID varchar(15), REF_ID varchar(15), TRN_TYPE nvarchar(40), ENTRY_PAIR varchar(15), DO_BRN varchar(15), DRCR varchar(2), ACCT varchar(100), BRN_ID varchar(20), DEPT_ID varchar(15), AMT decimal(18, 2), CURRENCY varchar(20), EXC_RATE numeric(18, 2), EXP_TO_CORE varchar(1), TRN_DATE datetime, TRN_DESC nvarchar(1000), MAKER_ID varchar(15), CHECKER_ID varchar(15))
|
|
17
|
|
|
18
|
INSERT INTO @MW_ENTRI
|
|
19
|
SELECT * FROM PAY_ENTRIES_POST
|
|
20
|
WHERE TRN_ID = @p_REF_ID AND (DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' ) AND (REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
|
|
21
|
|
|
22
|
--SELECT * FROM @MW_ENTRI
|
|
23
|
DECLARE @ENTRY_PAIR VARCHAR(100)
|
|
24
|
SET @ENTRY_PAIR = (SELECT TOP 1 ENTRY_PAIR FROM @MW_ENTRI WHERE LEN(ACCT)>9)--------------*********
|
|
25
|
|
|
26
|
--SELECT @ENTRY_PAIR
|
|
27
|
SELECT MAX(KQ.TRN_ID) AS TRN_ID , MAX(KQ.PrintDate) AS PrintDate, SUM(KQ.SUM_AMT) AS SUM_AMT, MAX(KQ.SUM_stringMoney) AS SUM_stringMoney, MAX(KQ.CASA_CR_ACCT) AS CASA_CR_ACCT,
|
|
28
|
MAX(KQ.CASA_CR_ACCT_NAME) AS CASA_CR_ACCT_NAME,MAX(KQ.CASA_DR_ACCT) AS CASA_DR_ACCT,MAX(KQ.CASA_DR_ACCT_NAME) AS CASA_DR_ACCT_NAME, KQ.REF_NO, MAX(KQ.TRN_DESC) AS TRN_DESC
|
|
29
|
FROM
|
|
30
|
(
|
|
31
|
SELECT A.TRN_ID,
|
|
32
|
convert(varchar(10),DAY(A.TRN_DATE))+N'/'+convert(varchar(10),MONTH(A.TRN_DATE)) +N'/'+convert(varchar(10),YEAR(A.TRN_DATE)) AS PrintDate,
|
|
33
|
A.SUM_AMT,
|
|
34
|
A.SUM_stringMoney,
|
|
35
|
C.CR_ACCT AS CASA_CR_ACCT, C.CASA_CR_ACCT_NAME,
|
|
36
|
B.DR_ACCT AS CASA_DR_ACCT, B.CASA_DR_ACCT_NAME,
|
|
37
|
D.TRN_DESC,
|
|
38
|
D.REF_NO,
|
|
39
|
E.REF_NO_SUM
|
|
40
|
FROM
|
|
41
|
(
|
|
42
|
SELECT A.TRN_ID,
|
|
43
|
CONVERT(DATE,A.TRN_DATE,103) TRN_DATE,
|
|
44
|
SUM(A.AMT) AS SUM_AMT,
|
|
45
|
dbo.ReadMoney(SUM(A.AMT)) AS SUM_stringMoney
|
|
46
|
FROM @MW_ENTRI A
|
|
47
|
WHERE DRCR = 'D'
|
|
48
|
GROUP BY A.TRN_ID, CONVERT(DATE,A.TRN_DATE,103), A.REF_NO
|
|
49
|
) AS A
|
|
50
|
LEFT JOIN
|
|
51
|
(
|
|
52
|
SELECT TOP 100 A.TRN_ID, A.ACCT DR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) AS CASA_DR_ACCT_NAME
|
|
53
|
FROM @MW_ENTRI A
|
|
54
|
WHERE LEN(A.ACCT) > 9 AND A.DRCR = 'D' AND ENTRY_PAIR = @ENTRY_PAIR
|
|
55
|
) AS B ON B.TRN_ID = A.TRN_ID
|
|
56
|
LEFT JOIN
|
|
57
|
(
|
|
58
|
SELECT TOP 100 A.TRN_ID, A.ACCT CR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) CASA_CR_ACCT_NAME
|
|
59
|
FROM @MW_ENTRI A
|
|
60
|
WHERE LEN(A.ACCT) > 9 AND A.DRCR = 'C' AND ENTRY_PAIR = @ENTRY_PAIR
|
|
61
|
) AS C ON C.TRN_ID = A.TRN_ID
|
|
62
|
LEFT JOIN
|
|
63
|
(
|
|
64
|
SELECT TOP 100 A.TRN_DESC, A.TRN_ID, A.REF_NO
|
|
65
|
FROM @MW_ENTRI A
|
|
66
|
) AS D ON D.TRN_ID = A.TRN_ID
|
|
67
|
LEFT JOIN
|
|
68
|
(
|
|
69
|
SELECT STUFF((SELECT ', ' + C1.REF_NO
|
|
70
|
FROM @MW_ENTRI C1
|
|
71
|
WHERE C2.TRN_ID = C1.TRN_ID
|
|
72
|
GROUP BY C1.TRN_ID,C1.REF_NO
|
|
73
|
FOR XML PATH('')
|
|
74
|
),1,1,'') AS REF_NO_SUM, C2.TRN_ID
|
|
75
|
FROM @MW_ENTRI C2
|
|
76
|
GROUP BY C2.TRN_ID, C2.REF_NO
|
|
77
|
) AS E ON E.TRN_ID = A.TRN_ID
|
|
78
|
) AS KQ
|
|
79
|
GROUP BY KQ.REF_NO
|