|
1
|
|
|
2
|
/*
|
|
3
|
[ASS_TRANSFER_MULTI_MASTER_ById] 'TFS000000000017'
|
|
4
|
*/
|
|
5
|
ALTER PROCEDURE dbo.ASS_TRANSFER_MULTI_MASTER_ById
|
|
6
|
@TRANS_MULTI_MASTER_ID varchar(15)
|
|
7
|
AS
|
|
8
|
SELECT
|
|
9
|
A.ASSET_ID, A.[TYPE_ID],C.[TYPE_CODE], C.[TYPE_NAME], A.GROUP_ID,B.GROUP_CODE, B.GROUP_NAME, A.ASSET_CODE, A.ASSET_NAME, A.ASSET_SERIAL_NO, A.ASSET_DESC,
|
|
10
|
A.SUP_ID, A.BUY_PRICE, A.AMORT_AMT, A.ASS_TYPE, TR.BRANCH_ID,BR_R.BRANCH_CODE,BR_R.BRANCH_NAME,H.DEP_ID, H.DEP_CODE,
|
|
11
|
H.DEP_NAME, A.EMP_ID,EM_R.EMP_CODE,EM_R.EMP_NAME,
|
|
12
|
A.BUY_DATE, A.USE_DATE, A.SPECIAL_ASS, A.AMORT_MONTH, A.AMORT_RATE, A.AMORT_START_DATE, A.AMORT_END_DATE,
|
|
13
|
A.FIRST_AMORT_AMT,
|
|
14
|
A.AMORTIZED_MONTH,
|
|
15
|
(A.AMORT_MONTH - A.AMORTIZED_MONTH) AS REMAIN_MONTH,
|
|
16
|
A.AMORTIZED_AMT,
|
|
17
|
ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), A.AMORT_AMT) AS REMAIN_AMORTIZED_AMT, --So tien khau hao con lai
|
|
18
|
A.PO_ID, A.WAREHOUSE_ID, A.LOCATION, A.REF_ASSET_ID,
|
|
19
|
A.REF_AMORTIZED_AMT,
|
|
20
|
A.WARRANTY_MONTHS,
|
|
21
|
A.AMORT_STATUS,
|
|
22
|
D.STATUS_NAME AMORT_STATUS_NAME,
|
|
23
|
A.ASS_STATUS,
|
|
24
|
E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san
|
|
25
|
A.ASS_STATUS_DESC,
|
|
26
|
A.RECORD_STATUS,
|
|
27
|
A.AUTH_STATUS,
|
|
28
|
ZZ.AUTH_STATUS_NAME,
|
|
29
|
A.MAKER_ID,
|
|
30
|
A.CREATE_DT,
|
|
31
|
A.CHECKER_ID,
|
|
32
|
A.APPROVE_DT,
|
|
33
|
TR.[DESCRIPTION], TR.BRANCH_CREATE,TR.LOCATION,TR.[DESCRIPTION],TR.TRANSFER_MULTI_ID,
|
|
34
|
ISNULL(TR.REMAIN_VALUE,A.AMORT_AMT-A.AMORTIZED_AMT) ASREMAIN_AMT,
|
|
35
|
--B.CREATE_DT AS CREATE_DT_ASS,
|
|
36
|
A.CREATE_DT AS CREATE_DT_ASS, -- HUNDGDV FIX NGAY NHAP TAI SAN
|
|
37
|
MK.BRANCH_NAME AS BRANCH_CREATE_NAME,MK.TLSUBBRID,A.BUY_DATE, TR.REMAIN_VALUE,
|
|
38
|
BR_R.FATHER_ID, TR.EMP_ID AS EMP_CONTRACT_ID,
|
|
39
|
|
|
40
|
-- hungdv fix hien don vi su dung
|
|
41
|
-- A.BRANCH_ID as BRANCH_ID_OLD,
|
|
42
|
-- B_A.BRANCH_NAME AS BRANCH_NAME_OLD,
|
|
43
|
-- A.DEPT_ID AS DEPT_ID_OLD,
|
|
44
|
-- D_A.DEP_NAME AS DEPT_NAME_OLD,
|
|
45
|
-- A.EMP_ID AS EMP_ID_OLD,
|
|
46
|
-- E_A.EMP_NAME AS EMP_NAME_OLD,
|
|
47
|
TR.BRANCH_ID_OLD,
|
|
48
|
B_A.BRANCH_NAME AS BRANCH_NAME_OLD,
|
|
49
|
TR.DEPT_ID_OLD,
|
|
50
|
D_A.DEP_NAME AS DEPT_NAME_OLD,
|
|
51
|
TR.EMP_ID_OLD,
|
|
52
|
E_A.EMP_NAME AS EMP_NAME_OLD,
|
|
53
|
(CASE WHEN EXISTS(SELECT 1 FROM dbo.ASS_ENTRIES_POST WHERE TRN_ID=@TRANS_MULTI_MASTER_ID AND REF_NO IS NULL) THEN 1
|
|
54
|
ELSE 0
|
|
55
|
END) AS IS_REF_NO
|
|
56
|
|
|
57
|
|
|
58
|
FROM [dbo].[ASS_TRANSFER_MULTI_DT] TR
|
|
59
|
LEFT JOIN ASS_MASTER A ON A.ASSET_ID = TR.ASSET_ID
|
|
60
|
LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID
|
|
61
|
LEFT JOIN ASS_TYPE C ON A.[TYPE_ID] = C.[TYPE_ID]
|
|
62
|
LEFT JOIN ASS_AMORT_STATUS D ON A.AMORT_STATUS = D.STATUS_CODE
|
|
63
|
LEFT JOIN ASS_STATUS E ON A.ASS_STATUS = E.STATUS_ID
|
|
64
|
LEFT JOIN CM_AUTH_STATUS ZZ ON ZZ.AUTH_STATUS = TR.AUTH_STATUS
|
|
65
|
LEFT JOIN CM_BRANCH BR_R ON BR_R.BRANCH_ID=TR.BRANCH_ID
|
|
66
|
LEFT JOIN CM_EMPLOYEE EM_R ON EM_R.EMP_ID=TR.EMP_ID
|
|
67
|
LEFT JOIN CM_DEPARTMENT H ON H.DEP_ID = TR.DEPT_ID
|
|
68
|
LEFT JOIN TL_USER MK ON MK.TLNANME = TR.MAKER_ID
|
|
69
|
-- HUNGDV FIX HIEN THI DON VI SU DUNG
|
|
70
|
-- LEFT JOIN CM_BRANCH B_A ON A.BRANCH_ID = B_A.BRANCH_ID
|
|
71
|
-- LEFT JOIN CM_DEPARTMENT D_A ON A.DEPT_ID = D_A.DEP_ID
|
|
72
|
-- LEFT JOIN CM_EMPLOYEE E_A ON A.EMP_ID = E_A.EMP_ID
|
|
73
|
|
|
74
|
LEFT JOIN CM_BRANCH B_A ON TR.BRANCH_ID_OLD = B_A.BRANCH_ID
|
|
75
|
LEFT JOIN CM_DEPARTMENT D_A ON TR.DEPT_ID_OLD = D_A.DEP_ID
|
|
76
|
LEFT JOIN CM_EMPLOYEE E_A ON TR.EMP_ID_OLD = E_A.EMP_ID
|
|
77
|
|
|
78
|
WHERE 1=1
|
|
79
|
AND TR.TRANS_MULTI_MASTER_ID= @TRANS_MULTI_MASTER_ID
|