|
1
|
|
|
2
|
|
|
3
|
ALTER PROC [dbo].[TL_SYSROLE_LIMIT_Auto]
|
|
4
|
@p_BRANCH_ID VARCHAR(15) = NULL,
|
|
5
|
@p_LIMIT_TYPE VARCHAR(15) = NULL
|
|
6
|
AS
|
|
7
|
BEGIN TRANSACTION
|
|
8
|
IF(@p_LIMIT_TYPE='ADV_PAY')
|
|
9
|
BEGIN
|
|
10
|
DECLARE @BRANCH_ID VARCHAR(15), @BRANCH_NAMME NVARCHAR(200), @BRANCH_TYPE VARCHAR(15),@p_LIMIT_ID VARCHAR(15), @TOTAL_PGD DECIMAL(18,0), @DMMS_ID VARCHAR(15), @LIMIT_PGD DECIMAL(18,0),
|
|
11
|
@LIMIT_CN DECIMAL(18,0)
|
|
12
|
SET @LIMIT_PGD =10000000
|
|
13
|
SET @LIMIT_CN =40000000
|
|
14
|
DECLARE CUR_DV CURSOR
|
|
15
|
FOR SELECT A.BRANCH_ID, A.BRANCH_NAME, A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_TYPE IN ('CN','PGD') AND (A.BRANCH_ID =@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='')
|
|
16
|
OPEN CUR_DV
|
|
17
|
FETCH NEXT FROM CUR_DV INTO @BRANCH_ID,@BRANCH_NAMME,@BRANCH_TYPE
|
|
18
|
WHILE @@FETCH_STATUS = 0
|
|
19
|
BEGIN
|
|
20
|
--SET @TOTAL_PGD =(SELECT COUNT(*) FROM CM_BRANCH WHERE FATHER_ID =@BRANCH_ID)*10000000
|
|
21
|
SET @TOTAL_PGD =0
|
|
22
|
EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out
|
|
23
|
IF(@BRANCH_TYPE ='CN' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDDV'))
|
|
24
|
BEGIN
|
|
25
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'GDDV',@LIMIT_CN+@TOTAL_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_CN, NULL)
|
|
26
|
END
|
|
27
|
ELSE IF(@BRANCH_TYPE ='CN' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='PGD'))
|
|
28
|
BEGIN
|
|
29
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'PGD',@LIMIT_CN+@TOTAL_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_CN, NULL)
|
|
30
|
END
|
|
31
|
ELSE IF(@BRANCH_TYPE ='PGD' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='TPGD' AND @BRANCH_TYPE ='PGD'))
|
|
32
|
BEGIN
|
|
33
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'TPGD',@LIMIT_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_PGD, NULL)
|
|
34
|
END
|
|
35
|
ELSE IF(@BRANCH_TYPE ='PGD' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='PPGD' AND @BRANCH_TYPE ='PGD'))
|
|
36
|
BEGIN
|
|
37
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'PPGD',@LIMIT_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_PGD, NULL)
|
|
38
|
END
|
|
39
|
ELSE IF (@BRANCH_TYPE ='CN' AND EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDDV'))
|
|
40
|
BEGIN
|
|
41
|
UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =@LIMIT_CN +@TOTAL_PGD, LIMIT_PERCENT= @LIMIT_CN WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='GDDV'
|
|
42
|
END
|
|
43
|
-- KIỂM TRA XEM ĐÃ KHAI BÁO ĐƠN VỊ / CHI NHÁNH NÀY TRONG CM_DMMS HAY CHƯA
|
|
44
|
IF(NOT EXISTS (SELECT * FROM CM_DMMS WHERE BRANCH_ID =@BRANCH_ID))
|
|
45
|
BEGIN
|
|
46
|
EXEC SYS_CodeMasters_Gen 'CM_DMMS', @DMMS_ID out
|
|
47
|
INSERT INTO CM_DMMS (DMMS_ID,BRANCH_ID, DEP_ID,DVDM_ID) VALUES (@DMMS_ID,@BRANCH_ID,(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE BRANCH_ID =@BRANCH_ID),'')
|
|
48
|
END
|
|
49
|
FETCH NEXT FROM CUR_DV INTO @BRANCH_ID,@BRANCH_NAMME,@BRANCH_TYPE
|
|
50
|
END
|
|
51
|
CLOSE CUR_DV
|
|
52
|
DEALLOCATE CUR_DV
|
|
53
|
END
|
|
54
|
ELSE
|
|
55
|
BEGIN
|
|
56
|
-- INSERT HAN MUC CHO HOI SO
|
|
57
|
-- GIAM DOC KHOI
|
|
58
|
IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDK'))
|
|
59
|
BEGIN
|
|
60
|
EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out
|
|
61
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'GDK',100000000,N'HAN MUC GIAM DOC KHOI','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',50000000,NULL)
|
|
62
|
END
|
|
63
|
BEGIN
|
|
64
|
UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =100000000, LIMIT_PERCENT= 50000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='GDK'
|
|
65
|
END
|
|
66
|
-- PHO TONG GIAM DOC
|
|
67
|
IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='PTGD'))
|
|
68
|
BEGIN
|
|
69
|
EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out
|
|
70
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'PTGD',200000000,N'HAN MUC PHE DUYET PHO TONG GIAM DOC','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',100000000,NULL)
|
|
71
|
END
|
|
72
|
BEGIN
|
|
73
|
UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =200000000, LIMIT_PERCENT= 100000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='PTGD'
|
|
74
|
END
|
|
75
|
IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='TGD'))
|
|
76
|
BEGIN
|
|
77
|
EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out
|
|
78
|
INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'TGD',2000000000,N'HAN MUC PHE DUYET TONG GIAM DOC','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',500000000,NULL)
|
|
79
|
END
|
|
80
|
BEGIN
|
|
81
|
UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =2000000000, LIMIT_PERCENT= 500000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='TGD'
|
|
82
|
END
|
|
83
|
END
|
|
84
|
COMMIT TRANSACTION
|
|
85
|
SELECT '0' as Result, '' LIMIT_ID, (SELECT TOP 1 LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@p_BRANCH_ID AND LIMIT_TYPE ='ADV_PAY') AS LIMIT_VALUE,'' ErrorDesc
|
|
86
|
RETURN '0'
|
|
87
|
ABORT:
|
|
88
|
BEGIN
|
|
89
|
ROLLBACK TRANSACTION
|
|
90
|
SELECT '-1' as Result, '' LIMIT_ID, 0.0 AS LIMIT_VALUE, '' ErrorDesc
|
|
91
|
RETURN '-1'
|
|
92
|
END
|