|
1
|
|
|
2
|
ALTER PROC [dbo].[rpt_MW_OUT_Byid] @p_OUT_ID VARCHAR(15) = NULL
|
|
3
|
--AS
|
|
4
|
--SELECT A.*,
|
|
5
|
-- B.BRANCH_CODE AS FR_BR_CODE,
|
|
6
|
-- B.BRANCH_NAME AS FR_BRNAME,
|
|
7
|
-- BR.BRANCH_CODE AS TO_BRCODE,
|
|
8
|
-- BR.BRANCH_NAME AS TO_BRNAME,
|
|
9
|
-- C.DEP_CODE AS FR_DEPCODE,
|
|
10
|
-- C.DEP_NAME FR_DEPNAME,
|
|
11
|
-- CR.DEP_CODE AS TO_DEPCODE, CR.DEP_NAME AS TO_DEPNAME,
|
|
12
|
-- MB.MATERIAL_ID,
|
|
13
|
-- MB.PRICE_ID,
|
|
14
|
-- MB.QTY_BALANCE,
|
|
15
|
-- MI.MATERIAL_NAME
|
|
16
|
--FROM MW_OUT_DT A
|
|
17
|
-- LEFT JOIN dbo.MW_OUT O ON A.OUT_DT_ID = O.OUT_ID
|
|
18
|
-- LEFT JOIN CM_BRANCH B ON O.BRN_ID = B.BRANCH_ID
|
|
19
|
-- LEFT JOIN CM_BRANCH BR ON A.TO_BRN_ID = BR.BRANCH_ID
|
|
20
|
-- LEFT JOIN CM_DEPARTMENT C ON O.DEPT_ID = C.DEP_ID
|
|
21
|
-- LEFT JOIN CM_DEPARTMENT CR ON A.TO_DEPT_ID = CR.DEP_ID
|
|
22
|
-- LEFT JOIN MW_MAST_BAL MB ON A.MAST_BAL_ID = MB.MAST_BAL_ID
|
|
23
|
-- LEFT JOIN MW_MAST_PRICE MP ON MB.PRICE_ID = MB.PRICE_ID
|
|
24
|
-- LEFT JOIN MW_IN MI ON MI.IN_ID= MP.MATERIAL_ID
|
|
25
|
AS
|
|
26
|
DECLARE @TABLE TABLE(MASTERBAL_ID VARCHAR(15), SL_TONG INT)
|
|
27
|
|
|
28
|
INSERT INTO @TABLE SELECT MAST_BAL_ID, SUM(QTY) FROM MW_OUT_DT WHERE OUT_ID = @p_OUT_ID GROUP BY MAST_BAL_ID
|
|
29
|
|
|
30
|
SELECT A.OUT_DT_ID, A.OUT_ID,A.MAST_BAL_ID,A.CUST_NAME,A.QTY,A.PRICE,A.TOTAL_AMT,A.COST_ACC,
|
|
31
|
A.QTY_OLD QTY_OLD,A.TO_BRN_ID,A.TO_DEPT_ID,A.EVENT_NAME,A.IS_BCT,A.COST_ACC,
|
|
32
|
B.BRANCH_CODE AS FR_BR_CODE,
|
|
33
|
B.BRANCH_NAME AS FR_BRNAME,
|
|
34
|
BR.BRANCH_CODE AS TO_BRCODE,
|
|
35
|
BR.BRANCH_NAME AS TO_BRNAME,
|
|
36
|
C.DEP_CODE AS FR_DEPCODE,
|
|
37
|
C.DEP_NAME FR_DEPNAME,
|
|
38
|
CR.DEP_CODE AS TO_DEPCODE, CR.DEP_NAME AS TO_DEPNAME,
|
|
39
|
MB.MATERIAL_ID,
|
|
40
|
MB.PRICE_ID,
|
|
41
|
--EP.SL_TON QTY_BALANCE ,EP.SL_TON + TT.SL_TONG SL_TON_TT,
|
|
42
|
ISNULL(EP.SL_TON,0) + TT.SL_TONG QTY_BALANCE ,ISNULL(EP.SL_TON,0) + TT.SL_TONG SL_TON_TT,
|
|
43
|
--EP.SL_TON + TT.SL_TONG QTY_BALANCE ,EP.SL_TON SL_TON_TT,
|
|
44
|
MI.MATERIAL_NAME, MP.PRICE_CODE,MB.TOTAL_AMT AS CUR_AMT_MASTBAL
|
|
45
|
FROM MW_OUT_DT A
|
|
46
|
LEFT JOIN @TABLE TT ON TT.MASTERBAL_ID = A.MAST_BAL_ID
|
|
47
|
LEFT JOIN dbo.MW_OUT O ON A.OUT_DT_ID = O.OUT_ID
|
|
48
|
LEFT JOIN CM_BRANCH B ON O.BRN_ID = B.BRANCH_ID
|
|
49
|
LEFT JOIN CM_BRANCH BR ON A.TO_BRN_ID = BR.BRANCH_ID
|
|
50
|
LEFT JOIN CM_DEPARTMENT C ON O.DEPT_ID = C.DEP_ID
|
|
51
|
LEFT JOIN CM_DEPARTMENT CR ON A.TO_DEPT_ID = CR.DEP_ID
|
|
52
|
LEFT JOIN MW_MAST_BAL MB ON A.MAST_BAL_ID = MB.MAST_BAL_ID
|
|
53
|
LEFT JOIN MW_MATERIAL MT ON MB.MATERIAL_ID = MT.MATERIAL_ID
|
|
54
|
LEFT JOIN MW_MAST_PRICE MP ON MB.PRICE_ID = MP.PRICE_ID
|
|
55
|
LEFT JOIN MW_IN MI ON MP.MATERIAL_ID = MI.IN_ID
|
|
56
|
LEFT JOIN MW_EXPORT_VIEW EP ON A.MAST_BAL_ID= EP.MAST_BAL_ID
|
|
57
|
WHERE A.OUT_ID = @p_OUT_ID OR @p_OUT_ID IS NULL OR @p_OUT_ID = '';
|
|
58
|
|