|
1
|
ALTER PROC dbo.ASS_ADDNEW_VB_BBBG
|
|
2
|
@ADDNEW_ID VARCHAR(15) = NULL
|
|
3
|
AS
|
|
4
|
BEGIN
|
|
5
|
DECLARE @BRANCH_ID VARCHAR(50) = (SELECT AA.BRANCH_ID FROM ASS_ADDNEW AA WHERE AA.ADDNEW_ID = @ADDNEW_ID)
|
|
6
|
DECLARE @REQ_ID VARCHAR(50) = (SELECT AA.REQ_ID FROM ASS_ADDNEW AA WHERE AA.ADDNEW_ID = @ADDNEW_ID)
|
|
7
|
|
|
8
|
--TABLE0
|
|
9
|
IF((@REQ_ID IS NULL OR @REQ_ID = '') AND (@BRANCH_ID IS NULL OR @BRANCH_ID = ''))
|
|
10
|
BEGIN
|
|
11
|
SELECT '' AS INFO_LIQ, '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME ,
|
|
12
|
'' AS BRANCH_NAME
|
|
13
|
END
|
|
14
|
ELSE
|
|
15
|
BEGIN
|
|
16
|
SELECT DISTINCT
|
|
17
|
al.FORWARD_CONTENT AS INFO_LIQ, tu.TLFullName, tu.ADDRESS, tu.PHONE, cel.POS_NAME AS POS_NAME ,
|
|
18
|
CASE WHEN cb.BRANCH_TYPE = 'HS' THEN cb.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE cb.BRANCH_NAME END AS BRANCH_NAME
|
|
19
|
FROM ASS_ADDNEW al
|
|
20
|
LEFT JOIN TL_USER tu ON al.MAKER_ID = tu.TLNANME
|
|
21
|
LEFT JOIN CM_EMPLOYEE_LOG cel ON al.MAKER_ID = cel.USER_DOMAIN
|
|
22
|
LEFT JOIN CM_BRANCH cb ON al.BRANCH_CREATE = cb.BRANCH_ID
|
|
23
|
LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = al.DEPT_CREATE
|
|
24
|
WHERE al.ADDNEW_ID = @ADDNEW_ID
|
|
25
|
|
|
26
|
END
|
|
27
|
|
|
28
|
--TABLE1
|
|
29
|
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, ald.ADDNEW_ID, am.ASSET_CODE, am.ASSET_NAME, cu.UNIT_NAME,
|
|
30
|
'1' AS SO_LUONG,ams.STATUS_NAME AS ASS_STATUS_DESC, am.ASSET_SERIAL_NO, prd.REQ_CODE AS TO_TRINH, ald.NOTES, am.ASSET_DESC AS ASS_DESC,
|
|
31
|
ISNULL(am.REQ_CODE,'') + ISNULL(CHAR(10) + CHAR(10) + am.PL,'') AS REQ_CODE
|
|
32
|
FROM ASS_ADDNEW ald
|
|
33
|
LEFT JOIN ASS_TRANSACTIONS AT ON ald.ADDNEW_ID= AT.TRN_ID
|
|
34
|
LEFT JOIN ASS_MASTER am ON AT .ASSET_ID = am.ASSET_ID
|
|
35
|
LEFT JOIN ASS_STATUS ams ON am.ASS_STATUS = ams.STATUS_ID
|
|
36
|
LEFT JOIN ASS_GROUP ag ON am.GROUP_ID = ag.GROUP_ID
|
|
37
|
LEFT JOIN PL_REQUEST_DOC prd ON am.REQ_ID = prd.REQ_ID
|
|
38
|
LEFT JOIN CM_UNIT cu ON cu.UNIT_ID = ag.UNIT
|
|
39
|
--WHERE ald.ADDNEW_ID = 'ASA000000021002'
|
|
40
|
WHERE ald.ADDNEW_ID = @ADDNEW_ID
|
|
41
|
ORDER BY STT ASC
|
|
42
|
|
|
43
|
--table2
|
|
44
|
--SELECT DISTINCT al.LIQ_ID, tu.TLFullName FROM TL_USER tu
|
|
45
|
--RIGHT JOIN ASS_LIQUIDATION al ON al.MAKER_ID = tu.TLNANME
|
|
46
|
--WHERE al.LIQ_ID = @LIQ_ID
|
|
47
|
DECLARE @DT DATETIME = (SELECT APPROVE_DT FROM ASS_ADDNEW AA WHERE AA.ADDNEW_ID = @ADDNEW_ID)
|
|
48
|
DECLARE @BB_DT NVARCHAR(500)
|
|
49
|
SET @BB_DT = N'Ngày ' + CAST(DAY(@DT) AS VARCHAR(500))
|
|
50
|
+ N' tháng ' + CAST(MONTH(@DT) AS VARCHAR(500))
|
|
51
|
+ N' năm ' + CAST(YEAR(@DT) AS VARCHAR(500))
|
|
52
|
+ N' tại '
|
|
53
|
+ (SELECT B.BRANCH_NAME FROM dbo.ASS_ADDNEW A JOIN dbo.CM_BRANCH B ON A.BRANCH_CREATE = B.BRANCH_ID WHERE A.ADDNEW_ID = @ADDNEW_ID)
|
|
54
|
|
|
55
|
|
|
56
|
SELECT CASE WHEN cb.BRANCH_TYPE = 'HS' THEN N'Hội sở' + ' - ' + CD.DEP_NAME ELSE cb.BRANCH_NAME END AS BRANCH_NAME,
|
|
57
|
CASE WHEN cb.BRANCH_TYPE = 'HS' THEN @BB_DT + ' - ' + CD.DEP_NAME ELSE @BB_DT END AS BBBG_DT,
|
|
58
|
AL.NOTES AS INFO_LIQ, AL.ADDNEW_ID
|
|
59
|
FROM ASS_ADDNEW AL
|
|
60
|
LEFT JOIN CM_BRANCH CB ON AL.BRANCH_CREATE = CB.BRANCH_ID
|
|
61
|
LEFT JOIN CM_DEPARTMENT CD ON AL.DEPT_CREATE = CD.DEP_ID
|
|
62
|
WHERE AL.ADDNEW_ID = @ADDNEW_ID
|
|
63
|
|
|
64
|
--TABLE3
|
|
65
|
IF((@REQ_ID IS NULL OR @REQ_ID = '') AND (@BRANCH_ID IS NULL OR @BRANCH_ID = ''))
|
|
66
|
BEGIN
|
|
67
|
SELECT '' AS INFO_LIQ, '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME ,
|
|
68
|
'' AS BRANCH_NAME
|
|
69
|
END
|
|
70
|
ELSE
|
|
71
|
BEGIN
|
|
72
|
SELECT DISTINCT
|
|
73
|
al.FORWARD_CONTENT AS INFO_LIQ, tu.TLFullName, tu.ADDRESS, tu.PHONE, ISNULL(cel.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
|
|
74
|
((SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = @ADDNEW_ID AND PP.PROCESS_ID = 'SEND' ORDER BY PP.APPROVE_DT DESC))
|
|
75
|
, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME ,
|
|
76
|
CASE WHEN cb.BRANCH_TYPE = 'HS' THEN cb.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE cb.BRANCH_NAME END AS BRANCH_NAME
|
|
77
|
FROM ASS_ADDNEW al
|
|
78
|
LEFT JOIN TL_USER tu ON al.MAKER_ID = tu.TLNANME
|
|
79
|
LEFT JOIN CM_EMPLOYEE_LOG cel ON al.MAKER_ID = cel.USER_DOMAIN
|
|
80
|
LEFT JOIN CM_BRANCH cb ON al.BRANCH_CREATE = cb.BRANCH_ID
|
|
81
|
LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = al.DEPT_CREATE
|
|
82
|
WHERE al.ADDNEW_ID = @ADDNEW_ID
|
|
83
|
|
|
84
|
END
|
|
85
|
|
|
86
|
END
|