· 8 years ago · Feb 28, 2018, 09:36 AM
1
2IF OBJECT_ID('[dbo].[fn_cms_Return_Verification_Get_Verification_Positions]') IS NOT NULL
3 BEGIN
4 DROP FUNCTION
5 [dbo].[fn_cms_Return_Verification_Get_Verification_Positions];
6 END;
7GO
8CREATE FUNCTION [dbo].[fn_cms_Return_Verification_Get_Verification_Positions]
9(@user_id INT,
10 @code VARCHAR(100)
11)
12RETURNS @rtnTable TABLE
13(MolId UNIQUEIDENTIFIER,
14 Contractor VARCHAR(100),
15 FVNumber VARCHAR(100),
16 AdditionalNumber VARCHAR(100),
17 EAN VARCHAR(100),
18 ArticleName VARCHAR(256),
19 VerifiedAmount INT,
20 AdviceAmount INT,
21 Warehouse VARCHAR(100),
22 OrganisationalUnit VARCHAR(100),
23 DocumentType VARCHAR(100),
24 LacksAmount INT,
25 Status VARCHAR(50),
26 StatusValue INT,
27 RequireSN INT,
28 PZDate DATETIME,
29 PZNumber VARCHAR(50),
30 ArticleIndex VARCHAR(50),
31 CreatedBy VARCHAR(50),
32 Waybill VARCHAR(50),
33 MbatBarcode VARCHAR(50)
34)
35AS
36 BEGIN
37 DECLARE @mus_id UNIQUEIDENTIFIER= (SELECT
38 mus_id
39 FROM mom_users
40 WHERE mus_cms_mus_id = @user_id);
41 DECLARE @existsAssingments BIT= 0;
42 IF EXISTS(SELECT
43 1
44 FROM mom_Users_Assignments WITH (NOLOCK)
45 JOIN mom_Orders_Heads WITH (NOLOCK) ON moh_id = mua_object_id
46 JOIN mom_Orders_Lines WITH (NOLOCK) ON mol_moh_id = moh_id
47 WHERE(mol_status < 40
48 OR (mol_status = 40
49 AND EXISTS
50 (
51 SELECT
52 1
53 FROM mom_Doc_Lines WITH (NOLOCK)
54 JOIN mom_Doc_Lines_Info WITH (NOLOCK) ON mdli_mdl_id = mdl_id
55 JOIN mom_Batch_Articles_Trays WITH (NOLOCK) ON mdli_mbat_id = mbat_id
56 WHERE mdl_mol_id = mol_id
57 AND mbat_status = 1
58 AND NOT EXISTS
59 (
60 SELECT
61 1
62 FROM mom_Batch_Articles WITH (NOLOCK)
63 JOIN Komputronik_Returns_Verification_Helper WITH (NOLOCK) ON krvh_mba_id = mba_id
64 WHERE mba_mbat_id = mbat_id
65 AND (krvh_state_is_sale = 1
66 OR krvh_state_is_controlling = 1)
67 )
68 )))
69 AND moh_mdn_id = 'PW_PWP'
70 AND moh_field_8 = '3211'
71 AND mua_mus_id = @mus_id
72 AND mua_status < 15)
73 BEGIN
74 SET @existsAssingments = 1;
75 END;
76 INSERT INTO @rtnTable
77 SELECT
78 mol_id AS MolId,
79 mc_name_full AS Contractor,
80 moh_field_4 AS FVNumber,
81 moh_field_3 AS AdditionalNumber,
82 mar_bar_code AS EAN,
83 mar_name AS ArticleName,
84 CAST(mol_qty_completed AS INT) AS VerifiedAmount,
85 CAST(mol_qty_order AS INT) AS AdviceAmount,
86 moh_field_8 AS Warehouse,
87 mc_GID AS OrganisationalUnit,
88 moh_mdn_id AS DocumentType,
89 CAST(mprlh.qty AS INT) AS LacksAmount,
90 CASE
91 WHEN hasZone.hasZone = 0
92 THEN 'Zablokowana'
93 WHEN mol_qty_completed = 0
94 AND mol_qty_completed = 0
95 AND mol_status < 20
96 THEN 'Niezweryfikowana'
97 WHEN mol_status = 25
98 THEN 'W trakcie weryfikacji'
99 WHEN mol_status = 35
100 THEN 'Zweryfikowana częściowo'
101 WHEN mol_status = 40
102 THEN 'Zweryfikowna (nośnik otwarty)'
103 END AS Status,
104 CASE
105 WHEN hasZone.hasZone = 0
106 THEN 4
107 WHEN mol_qty_completed = 0
108 AND mol_qty_completed = 0
109 AND mol_status < 20
110 THEN 0
111 WHEN mol_status = 25
112 THEN 1
113 WHEN mol_status = 35
114 THEN 2
115 WHEN mol_status = 40
116 THEN 3
117 END AS StatusValue,
118 CASE
119 WHEN mar_serials_tracking > 0
120 THEN 1
121 ELSE 0
122 END AS RequireSN,
123 moh_date_created AS PZDate,
124 moh_name AS PZNumber,
125 mar_index AS ArticleIndex,
126 moh_field_6 AS CreatedBy,
127 ret.krvh_bill AS Waybill,
128 ret.krvh_mbat_barcode AS MbatBarcode
129 FROM mom_Orders_Heads WITH (NOLOCK)
130 LEFT JOIN mom_Users_Assignments WITH (NOLOCK) ON mua_object_id = moh_id
131 JOIN mom_Orders_Lines WITH (NOLOCK) ON mol_moh_id = moh_id
132 JOIN mom_Contractors WITH (NOLOCK) ON mc_id = moh_mc_id
133 JOIN mom_Articles WITH (NOLOCK) ON mar_id = mol_mar_id
134 CROSS APPLY(SELECT
135 dbo.fn_cms_Is_Article_Has_Assigned_Zone(mol_id) AS hasZone) hasZone
136 LEFT JOIN(SELECT
137 marp_mar_id
138 FROM mom_Articles_Packages WITH (NOLOCK)
139 WHERE marp_bar_code = @code) marp ON marp.marp_mar_id = mar_id
140 LEFT JOIN(SELECT
141 mdl_mar_id
142 FROM mom_Doc_Lines_Serials WITH (NOLOCK)
143 JOIN mom_Doc_Lines WITH (NOLOCK) ON mdl_id = mdls_mdl_id
144 WHERE mdls_number = @code) mdls ON mdls.mdl_mar_id = mar_id
145 LEFT JOIN(SELECT
146 TRY_CAST(mprtl_field_1 AS UNIQUEIDENTIFIER) AS mprtl_mol_id,
147 SUM(mprtl_qty) AS qty
148 FROM mom_Protocols_Lines
149 JOIN mom_Protocols_Heads ON mprth_id = mprtl_mprth_id
150 WHERE mprth_mdn_id IN('BDSPZ', 'BRSPZ', 'USSPZ')
151 GROUP BY
152 TRY_CAST(mprtl_field_1 AS UNIQUEIDENTIFIER)) mprlh ON mprlh.mprtl_mol_id = mol_id
153 LEFT JOIN(SELECT
154 mdl_mol_id
155 FROM mom_Doc_Lines
156 JOIN mom_Doc_Lines_Info ON mdli_mdl_id = mdl_id
157 JOIN mom_Batch_Articles_Trays ON mbat_id = mdli_mbat_id
158 WHERE mbat_status = 1
159 AND NOT EXISTS
160 (
161 SELECT
162 1
163 FROM mom_Batch_Articles WITH (NOLOCK)
164 JOIN Komputronik_Returns_Verification_Helper WITH (NOLOCK) ON krvh_mba_id = mba_id
165 WHERE mba_mbat_id = mbat_id
166 AND (krvh_state_is_sale = 1
167 OR krvh_state_is_controlling = 1)
168 )) mbat ON mbat.mdl_mol_id = mol_id
169 LEFT JOIN(SELECT
170 krvh_mol_id,
171 krvh_bill,
172 krvh_mbat_barcode
173 FROM Komputronik_Returns_Verification_Helper WITH (NOLOCK)
174 WHERE krvh_state_is_to_clarify = 1) AS ret ON ret.krvh_mol_id = mol_id
175 WHERE moh_mdn_id = 'PW_PWP'
176 AND moh_field_8 = '3211'
177 AND ((@existsAssingments = 1
178 AND mua_mus_id = @mus_id)
179 OR (@existsAssingments = 0
180 AND mua_mus_id IS NULL))
181 AND moh_status <= 40
182 AND (@code = ''
183 OR @code != ''
184 AND (mar_bar_code = @code
185 OR marp.marp_mar_id IS NOT NULL
186 OR mdls.mdl_mar_id IS NOT NULL))
187 AND (mol_status < 40
188 OR (mol_status = 40
189 AND mbat.mdl_mol_id IS NOT NULL))
190 ORDER BY
191 StatusValue;
192 RETURN;
193 END;