Project

General

Profile

Download (6.01 KB)

script_upd_21032023.txt

Luc Tran Van, 03/21/2023 02:38 PM

 
1 
??
2 
IF @@ERROR <> 0 SET NOEXEC ON
3 
GO
4 
PRINT N'Altering [dbo].[rpt_MW_IN_PNK_Excel_BanViet]'
5 
GO
6 
ALTER PROCEDURE [dbo].[rpt_MW_IN_PNK_Excel_BanViet]
7 
@IN_ID	varchar(15) = NULL
8 
AS
9 
SELECT Row_number() over(order by A.IN_ID) AS STT,A.*, B.GROUP_CODE, B.GROUP_NAME,C.MATERIAL_ACCTNO, C.MATERIAL_ACCTNO_NHNN, C.MATERIAL_CODE, A. MATERIAL_NAME,D.UNIT_ID, D.UNIT_CODE, D.UNIT_NAME
10 
FROM MW_IN A
11 
LEFT JOIN MW_GROUP B ON A.GROUP_ID = B.GROUP_ID
12 
LEFT JOIN MW_MATERIAL C ON A.MATERIAL_ID = C.MATERIAL_ID
13 
LEFT JOIN CM_UNIT D ON C.UNIT_ID= D.UNIT_ID
14 
WHERE  IN_MASTER_ID= @IN_ID OR @IN_ID IS NULL
15
16 
SELECT TOP 1 N'TP. H? Ch? Minh, ng?y ' + RIGHT('0' + CAST(DATEPART(DD, A.CREATE_DT) AS VARCHAR(2)), 2)
17 
+ N' th?ng ' + RIGHT('0' + CAST(DATEPART(MM, A.CREATE_DT) AS VARCHAR(2)), 2)
18 
+ N' nm ' + RIGHT(YEAR(A.CREATE_DT), 4) AS CREATE_DT
19 
FROM MW_IN A
20 
WHERE  IN_MASTER_ID= @IN_ID OR @IN_ID IS NULL
21 
GO
22 
IF @@ERROR <> 0 SET NOEXEC ON
23 
GO
24 
PRINT N'Altering [dbo].[rpt_TR_REQ_PAY_Job_DT_ById]'
25 
GO
26
27 
ALTER   PROC [dbo].[rpt_TR_REQ_PAY_Job_DT_ById]
28 
@p_REQ_PAY_ID varchar(15) = NULL,
29 
@p_BRANCH_CREATE varchar(15) = NULL,
30 
@p_DEP_ID varchar(15) = NULL,
31 
@p_CHECKR_ID varchar(15) = NULL,
32 
@p_CHECKR_ID_KT varchar(15) = NULL,
33 
@p_APPROVE_DT_KT varchar(15) = NULL
34 
AS
35 
BEGIN
36 
	SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS STT, JF.REQ_CODE, A.FULLNAME, A.JOB_PLACE, FORMAT (A.FRMDATE, 'dd/MM/yyyy '), FORMAT (A.TODATE, 'dd/MM/yyyy '),
37 
	CAST(DATEDIFF(day,A.FRMDATE, A.TODATE) as decimal(18, 0)) AS NUMBER_DAY,
38 
	A.DAY_RATE, A.JOB_COST, A.COST_RESIDENCE, A.TYPE_TRANS, A.COST_AMT, A.COST_OTHER, A.COST_MOVE,
39 
	A.DAY_RATE + A.JOB_COST + A.COST_RESIDENCE + A.COST_AMT + A.COST_OTHER AS TOTAL_AMT
40 
	FROM TR_REQ_PAY_Job_DT A
41 
	LEFT JOIN TR_REQUEST_JOB_FORM JF ON A.REQ_ID = JF.REQ_ID
42 
	--LEFT JOIN TR_REQ_PAY_JOB J ON A.REQ_ID = J.REQ_ID
43 
	LEFT JOIN TR_REQ_PAYMENT TR ON A.REQ_PAY_ID = TR.REQ_PAY_ID
44 
	LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = @p_BRANCH_CREATE
45 
	LEFT JOIN TL_USER T ON T.TLNANME = A.FULLNAME
46 
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
47
48 
	SELECT TOP 1 N'T? ng?y ' + CAST(FORMAT (A.FRMDATE, 'dd/MM/yyyy ') as nvarchar) + ', ' AS FRMDATE
49 
	FROM TR_REQ_PAY_Job_DT A
50 
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
51 
	ORDER BY A.FRMDATE ASC
52
53 
	SELECT TOP 1 N'?n ng?y ' + CAST(FORMAT (A.TODATE, 'dd/MM/yyyy ') as nvarchar) AS TODATE
54 
	FROM TR_REQ_PAY_Job_DT A
55 
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
56 
	ORDER BY A.TODATE DESC
57
58 
	SELECT TOP 1 B.REQ_PAY_CODE, ISNULL(A.AMT_PAY,0) AS AMT_PAY
59 
	FROM TR_REQ_PAYMENT_DT A
60 
	LEFT JOIN TR_REQ_PAYMENT B ON A.PAY_ID = B.REQ_PAY_ID
61 
	WHERE A.PAY_ID =@p_REQ_PAY_ID
62
63 
	DECLARE @AMT_PAY DECIMAL(18,2)
64 
	SELECT TOP 1 @AMT_PAY = ISNULL(A.AMT_PAY,0)
65 
	FROM TR_REQ_PAYMENT_DT A
66 
	WHERE A.PAY_ID =@p_REQ_PAY_ID
67
68 
	SELECT @AMT_PAY - ISNULL(SUM(A.COST_AMT),0) AS 'TIENHOANUNGCHITHEM', ISNULL(SUM(A.JOB_COST),0) AS SUM_JOB_COST, ISNULL(SUM(A.COST_RESIDENCE),0) AS SUM_COST_RESIDENCE, ISNULL(SUM(A.COST_MOVE),0) AS SUM_COST_MOVE, ISNULL(SUM(A.COST_AMT),0) AS SUM_COST_AMT FROM TR_REQ_PAY_Job_DT A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
69 
END
70 
GO
71 
IF @@ERROR <> 0 SET NOEXEC ON
72