|
1
|
ALTER PROC dbo.MW_OUT_Byid @p_OUT_ID VARCHAR(15) = NULL
|
|
2
|
AS
|
|
3
|
DECLARE @TABLE TABLE(MASTERBAL_ID VARCHAR(15), SL_TONG INT)
|
|
4
|
|
|
5
|
INSERT INTO @TABLE SELECT MAST_BAL_ID, SUM(QTY) FROM MW_OUT_DT WHERE OUT_ID = @p_OUT_ID GROUP BY MAST_BAL_ID
|
|
6
|
|
|
7
|
SELECT A.OUT_DT_ID, A.OUT_ID,A.MAST_BAL_ID,A.CUST_NAME,A.QTY,A.PRICE,A.TOTAL_AMT,A.NOTES,
|
|
8
|
A.QTY_OLD QTY_OLD,A.TO_BRN_ID,A.TO_DEPT_ID,A.EVENT_NAME,A.IS_BCT,A.COST_ACC,
|
|
9
|
B.BRANCH_CODE AS FR_BR_CODE,
|
|
10
|
B.BRANCH_NAME AS FR_BRNAME,
|
|
11
|
BR.BRANCH_CODE AS TO_BRCODE,
|
|
12
|
BR.BRANCH_NAME AS TO_BRNAME,
|
|
13
|
C.DEP_CODE AS FR_DEPCODE,
|
|
14
|
C.DEP_NAME FR_DEPNAME,
|
|
15
|
CR.DEP_CODE AS TO_DEPCODE,
|
|
16
|
CR.DEP_NAME AS TO_DEPNAME,
|
|
17
|
CR.BRANCH_ID AS TO_DEPT_BRN_ID,
|
|
18
|
MB.MATERIAL_ID,
|
|
19
|
MB.PRICE_ID,
|
|
20
|
MI.MATERIAL_NAME, MP.PRICE_CODE,MB.TOTAL_AMT AS CUR_AMT_MASTBAL,
|
|
21
|
CASE WHEN CW.WARE_CODE NOT IN ('05KM','06QT','07TT','08NV') THEN 0 ELSE ISNULL(A.VAT,MI.VAT) END AS VAT
|
|
22
|
,ISNULL(A.PRICE_VAT,0) AS PRICE_VAT, A.PRICE*A.QTY AS PRICE_NO_VAT,
|
|
23
|
D.UNIT_ID, D.UNIT_CODE, D.UNIT_NAME, A.RECEIVE_NOTES,
|
|
24
|
BR.BRANCH_CODE + ' - ' + BR.BRANCH_NAME AS TO_BRFULLNAME, CR.DEP_CODE + ' - ' + CR.DEP_NAME AS TO_DEPFULLNAME,
|
|
25
|
CB1.BRANCH_CODE + '-' + CB1.BRANCH_NAME AS UNIT_RECEIVE_NAME, CB2.BRANCH_CODE + '-' + CB2.BRANCH_NAME AS UNIT_CHARGE_NAME, cd.DEP_ID AS DEP_RECEIVE, cd.DEP_NAME AS DEP_RECEIVE_NAME, cd1.DEP_ID AS DEP_CHARGE, cd1.DEP_NAME AS DEP_CHARGE_NAME,
|
|
26
|
CB1.BRANCH_ID AS UNIT_RECEIVE, CB2.BRANCH_ID AS UNIT_CHARGE, D.UNIT_ID AS UNIT_PAY,D.UNIT_NAME AS UNIT_PAY_NAME, A.CUSTOMER_NAME, A.CUSTOMER_ID, A.CUSTOMER_LOCATION, A.QTY_REAL_OLD
|
|
27
|
,MB.QTY_REAL AS SL_TON_TT
|
|
28
|
,MB.QTY_BALANCE AS QTY_BALANCE, A.QTY_RECEIVE, A.QTY_DAMAGED, A.QTY_LOSS
|
|
29
|
FROM MW_OUT_DT A
|
|
30
|
LEFT JOIN @TABLE TT ON TT.MASTERBAL_ID = A.MAST_BAL_ID
|
|
31
|
LEFT JOIN dbo.MW_OUT O ON A.OUT_DT_ID = O.OUT_ID
|
|
32
|
LEFT JOIN CM_WARE CW ON CW.WARE_ID = O.WARE_ID
|
|
33
|
LEFT JOIN CM_BRANCH B ON O.BRN_ID = B.BRANCH_ID
|
|
34
|
LEFT JOIN CM_BRANCH BR ON A.TO_BRN_ID = BR.BRANCH_ID
|
|
35
|
LEFT JOIN CM_DEPARTMENT C ON O.DEPT_ID = C.DEP_ID
|
|
36
|
LEFT JOIN CM_DEPARTMENT CR ON A.TO_DEPT_ID = CR.DEP_ID
|
|
37
|
LEFT JOIN MW_MAST_BAL MB ON A.MAST_BAL_ID = MB.MAST_BAL_ID
|
|
38
|
LEFT JOIN MW_MATERIAL MT ON MB.MATERIAL_ID = MT.MATERIAL_ID
|
|
39
|
LEFT JOIN CM_UNIT D ON MT.UNIT_ID= D.UNIT_ID
|
|
40
|
LEFT JOIN MW_MAST_PRICE MP ON MB.PRICE_ID = MP.PRICE_ID
|
|
41
|
LEFT JOIN MW_IN MI ON MP.MATERIAL_ID = MI.IN_ID
|
|
42
|
LEFT JOIN MW_EXPORT_VIEW EP ON A.MAST_BAL_ID= EP.MAST_BAL_ID
|
|
43
|
LEFT JOIN CM_BRANCH CB1 ON CB1.BRANCH_ID = A.UNIT_RECEIVE
|
|
44
|
LEFT JOIN CM_BRANCH CB2 ON CB2.BRANCH_ID = A.UNIT_CHARGE
|
|
45
|
LEFT JOIN CM_DEPARTMENT cd ON cd.DEP_ID = A.DEP_RECEIVE
|
|
46
|
LEFT JOIN CM_DEPARTMENT cd1 ON cd1.DEP_ID = A.DEP_CHARGE
|
|
47
|
WHERE A.OUT_ID = @p_OUT_ID OR @p_OUT_ID IS NULL OR @p_OUT_ID = '';
|