|
1
|
ALTER FUNCTION dbo.FN_GET_BRANCH_LEADER
|
|
2
|
( @BRANCH_ID VARCHAR(15), @DEPT_ID VARCHAR(50) )
|
|
3
|
|
|
4
|
RETURNS VARCHAR(MAX)
|
|
5
|
|
|
6
|
AS
|
|
7
|
|
|
8
|
BEGIN
|
|
9
|
DECLARE @BRANCH_TYPE VARCHAR(15), @TLNAME VARCHAR(100)
|
|
10
|
DECLARE @LST_USER_RECIVE TABLE (TLNAME VARCHAR(10))
|
|
11
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_ID)
|
|
12
|
IF(@BRANCH_TYPE = 'PGD' )
|
|
13
|
BEGIN
|
|
14
|
SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER
|
|
15
|
WHERE 1=1
|
|
16
|
AND TLSUBBRID = @BRANCH_ID
|
|
17
|
AND RoleName IN ('TPGD','PPGD')
|
|
18
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
|
|
19
|
END
|
|
20
|
ELSE IF(@BRANCH_TYPE = 'CN' )
|
|
21
|
BEGIN
|
|
22
|
SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER
|
|
23
|
WHERE 1=1
|
|
24
|
AND TLSUBBRID = @BRANCH_ID
|
|
25
|
AND RoleName IN ('GDDV','PGD')
|
|
26
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
|
|
27
|
END
|
|
28
|
ELSE IF(@BRANCH_TYPE = 'HS' )
|
|
29
|
BEGIN
|
|
30
|
SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER
|
|
31
|
WHERE 1=1
|
|
32
|
AND TLSUBBRID = @BRANCH_ID
|
|
33
|
AND SECUR_CODE = @DEPT_ID
|
|
34
|
AND RoleName IN ('GDDV','PP','TP')
|
|
35
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
|
|
36
|
END
|
|
37
|
|
|
38
|
RETURN @TLNAME
|
|
39
|
END;
|
|
40
|
GO
|
|
41
|
|
|
42
|
ALTER PROC dbo.rpt_ASS_USE_MULTI_CONFIRM_Excel
|
|
43
|
@USER_MASTER_ID VARCHAR(15) = null,
|
|
44
|
@BRANCH_ID VARCHAR(15) = null,
|
|
45
|
@FROMDATE VARCHAR(15) = null,
|
|
46
|
@TODATE VARCHAR(15) = null,
|
|
47
|
@IS_CONFIRM VARCHAR(1) = null
|
|
48
|
as
|
|
49
|
BEGIN
|
|
50
|
SELECT
|
|
51
|
B.ASSET_CODE, B.ASSET_NAME, B.AMORT_MONTH, B.AMORTIZED_MONTH, B.AMORT_AMT, B.AMORTIZED_AMT, AMORT_AMT - B.AMORTIZED_AMT AS GT_CL, B.NOTES,
|
|
52
|
A.USER_MASTER_ID,CB.BRANCH_CODE,CB.BRANCH_NAME,1 AS SL,B.BUY_PRICE,'' AS UNIT,B.ASSET_SERIAL_NO AS SERIAL_NUMBER,
|
|
53
|
dbo.FN_GET_BRANCH_LEADER(C.BRANCH_ID, c.DEPT_ID) AS TLFullName,D.DEP_CODE,D.DEP_NAME,E.REQ_CODE
|
|
54
|
FROM ASS_USE_MULTI_MASTER A
|
|
55
|
INNER JOIN ASS_USE_MULTI_DT C ON C.USER_MASTER_ID = A.USER_MASTER_ID
|
|
56
|
INNER JOIN ASS_MASTER B ON B.ASSET_ID = C.ASSET_ID
|
|
57
|
LEFT JOIN CM_BRANCH CB ON CB.BRANCH_ID = C.BRANCH_ID
|
|
58
|
LEFT JOIN CM_DEPARTMENT D ON A.DEP_ID = D.DEP_ID
|
|
59
|
LEFT JOIN TR_REQUEST_SHOP_DOC E ON C.REQ_ID = E.REQ_ID
|
|
60
|
WHERE (A.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')
|
|
61
|
AND A.AUTH_STATUS = 'A'
|
|
62
|
AND (C.BRANCH_ID = @BRANCH_ID OR @BRANCH_ID IS NULL OR @BRANCH_ID = '')
|
|
63
|
AND ((@IS_CONFIRM = 'Y' AND A.USER_MASTER_ID IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER))
|
|
64
|
OR (@IS_CONFIRM = 'N' AND A.USER_MASTER_ID NOT IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)
|
|
65
|
OR (@IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)))
|
|
66
|
AND (CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')
|
|
67
|
AND (CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')
|
|
68
|
END
|