|
1
|
ALTER PROCEDURE dbo.PL_REQUEST_COSTCENTER_ByID
|
|
2
|
@P_REQ_ID varchar(15),
|
|
3
|
@p_TYPE VARCHAR(15),
|
|
4
|
@p_TLNAME VARCHAR(20)
|
|
5
|
AS
|
|
6
|
|
|
7
|
DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT
|
|
8
|
|
|
9
|
|
|
10
|
SET @IS_ALL=0
|
|
11
|
|
|
12
|
DECLARE
|
|
13
|
@COST_ID TABLE (
|
|
14
|
COST_ID VARCHAR(15)
|
|
15
|
)
|
|
16
|
|
|
17
|
DECLARE @DVDM_ID TABLE (
|
|
18
|
DVDM_ID VARCHAR(15)
|
|
19
|
)
|
|
20
|
|
|
21
|
SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
|
|
22
|
|
|
23
|
SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
|
|
24
|
SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
|
|
25
|
IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
|
|
26
|
SET @IS_ALL=1
|
|
27
|
|
|
28
|
|
|
29
|
INSERT INTO @COST_ID
|
|
30
|
SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID
|
|
31
|
|
|
32
|
INSERT INTO @DVDM_ID
|
|
33
|
SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID
|
|
34
|
|
|
35
|
|
|
36
|
IF(@p_TYPE='DVKD')
|
|
37
|
BEGIN
|
|
38
|
SELECT A.*,CC.DVDM_NAME AS COST_NAME,
|
|
39
|
CC.DVDM_CODE AS COST_CODE,
|
|
40
|
CASE
|
|
41
|
WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận'
|
|
42
|
ELSE N'Chưa xác nhận'
|
|
43
|
END AS STATUS_NAME,
|
|
44
|
--------BAODNQ 22/6/2022 : LẤY THÊM DEP_ID----------
|
|
45
|
STUFF((select ', ' + cd.DEP_ID
|
|
46
|
FROM CM_DEPARTMENT cd
|
|
47
|
WHERE cd.FATHER_ID = PCD.DEP_ID OR cd.DEP_ID = PCD.DEP_ID
|
|
48
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''
|
|
49
|
) AS DEP_ID
|
|
50
|
FROM dbo.PL_REQUEST_COSTCENTER A
|
|
51
|
LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
|
|
52
|
LEFT JOIN PL_COSTCENTER PC ON PC.DVDM_ID = CC.DVDM_ID
|
|
53
|
LEFT JOIN PL_COSTCENTER_DT PCD ON PC.COST_ID = PCD.COST_ID
|
|
54
|
WHERE A.REQ_ID=@P_REQ_ID
|
|
55
|
END
|
|
56
|
ELSE IF(@p_TYPE='PDTT')
|
|
57
|
BEGIN
|
|
58
|
SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE, CASE WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
|
|
59
|
FROM dbo.PL_REQUEST_COSTCENTER A
|
|
60
|
LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
|
|
61
|
WHERE A.REQ_ID=@P_REQ_ID
|
|
62
|
--AND (
|
|
63
|
--( A.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @ROLE ='TGD' OR @IS_ALL=1 OR @ROLE='HDQT'))
|
|
64
|
|
|
65
|
END
|
|
66
|
ELSE IF(@p_TYPE='XLTT' OR @p_TYPE='TFJOB')
|
|
67
|
BEGIN
|
|
68
|
SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE, CASE WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
|
|
69
|
FROM dbo.PL_REQUEST_COSTCENTER A
|
|
70
|
LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
|
|
71
|
WHERE A.REQ_ID=@P_REQ_ID
|
|
72
|
-- AND (
|
|
73
|
-- ( A.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
|
|
74
|
--OR @ROLE ='TGD' OR @IS_ALL=1 OR @ROLE='HDQT' )
|
|
75
|
|
|
76
|
END
|
|
77
|
GO
|