· 8 years ago · Feb 28, 2018, 09:34 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 mol_id AS MolId,
78 mc_name_full AS Contractor,
79 moh_field_4 AS FVNumber,
80 moh_field_3 AS AdditionalNumber,
81 mar_bar_code AS EAN,
82 mar_name AS ArticleName,
83 CAST(mol_qty_completed AS INT) AS VerifiedAmount,
84 CAST(mol_qty_order AS INT) AS AdviceAmount,
85 moh_field_8 AS Warehouse,
86 mc_GID AS OrganisationalUnit,
87 moh_mdn_id AS DocumentType,
88 CAST(
89 (
90 SELECT
91 SUM(mprtl_qty)
92 FROM mom_Protocols_Lines
93 JOIN mom_Protocols_Heads ON mprth_id = mprtl_mprth_id
94 JOIN mom_Doc_Lines ON mdl_id = mprtl_mdl_id
95 WHERE mdl_mol_id = mol_id
96 AND mprth_mdn_id IN('BDSPZ', 'BRSPZ', 'USSPZ')
97 ) AS INT) AS LacksAmount,
98 CASE
99 WHEN dbo.fn_cms_Is_Article_Has_Assigned_Zone(mol_id) = 0
100 THEN 'Zablokowana'
101 WHEN mol_qty_completed = 0
102 AND mol_qty_completed = 0
103 AND mol_status < 20
104 THEN 'Niezweryfikowana'
105 WHEN mol_status = 25
106 THEN 'W trakcie weryfikacji'
107 WHEN mol_status = 35
108 THEN 'Zweryfikowana częściowo'
109 WHEN mol_status = 40
110 THEN 'Zweryfikowna (nośnik otwarty)'
111 END AS Status,
112 CASE
113 WHEN 1 = 0
114 THEN 4
115 WHEN mol_qty_completed = 0
116 AND mol_qty_completed = 0
117 AND mol_status < 20
118 THEN 0
119 WHEN mol_status = 25
120 THEN 1
121 WHEN mol_status = 35
122 THEN 2
123 WHEN mol_status = 40
124 THEN 3
125 END AS StatusValue,
126 CASE
127 WHEN mar_serials_tracking > 0
128 THEN 1
129 ELSE 0
130 END AS RequireSN,
131 moh_date_created AS PZDate,
132 moh_name AS PZNumber,
133 mar_index AS ArticleIndex,
134 moh_field_6 AS CreatedBy,
135 ret.krvh_bill AS Waybill,
136 ret.krvh_mbat_barcode AS MbatBarcode
137 FROM mom_Orders_Heads WITH (NOLOCK)
138 LEFT JOIN mom_Users_Assignments WITH (NOLOCK) ON mua_object_id = moh_id
139 JOIN mom_Orders_Lines WITH (NOLOCK) ON mol_moh_id = moh_id
140 JOIN mom_Contractors WITH (NOLOCK) ON mc_id = moh_mc_id
141 JOIN mom_Articles WITH (NOLOCK) ON mar_id = mol_mar_id
142 LEFT JOIN(SELECT
143 krvh_mol_id,
144 krvh_bill,
145 krvh_mbat_barcode
146 FROM Komputronik_Returns_Verification_Helper WITH (NOLOCK)
147 WHERE krvh_state_is_to_clarify = 1) AS ret ON ret.krvh_mol_id = mol_id
148 WHERE moh_mdn_id = 'PW_PWP'
149 AND moh_field_8 = '3211'
150 AND ((@existsAssingments = 1
151 AND mua_mus_id = @mus_id)
152 OR (@existsAssingments = 0
153 AND mua_mus_id IS NULL))
154 AND moh_status < 45
155 AND (@code = ''
156 OR @code != ''
157 AND (mar_bar_code = @code
158 OR EXISTS
159 (
160 SELECT
161 1
162 FROM mom_Articles_Packages WITH (NOLOCK)
163 WHERE marp_mar_id = mar_id
164 AND marp_bar_code = @code
165 )
166 OR EXISTS
167 (
168 SELECT
169 1
170 FROM mom_Doc_Lines_Serials WITH (NOLOCK)
171 JOIN mom_Doc_Lines WITH (NOLOCK) ON mdls_mdl_id = mdl_id
172 JOIN mom_Doc_Heads WITH (NOLOCK) ON mdh_id = mdl_mdh_id
173 WHERE mdls_number = @code
174 AND mdl_mar_id = mar_id
175 AND mdh_mdn_id IN('WZ')
176 )))
177 AND (mol_status < 40
178 OR mol_status = 40
179 AND EXISTS
180 (
181 SELECT
182 1
183 FROM mom_Doc_Lines WITH (NOLOCK)
184 JOIN mom_Doc_Lines_Info WITH (NOLOCK) ON mdli_mdl_id = mdl_id
185 JOIN mom_Batch_Articles_Trays WITH (NOLOCK) ON mdli_mbat_id = mbat_id
186 WHERE mdl_mol_id = mol_id
187 AND mbat_status = 1
188 AND NOT EXISTS
189 (
190 SELECT
191 1
192 FROM mom_Batch_Articles WITH (NOLOCK)
193 JOIN Komputronik_Returns_Verification_Helper WITH (NOLOCK) ON krvh_mba_id = mba_id
194 WHERE mba_mbat_id = mbat_id
195 AND (krvh_state_is_sale = 1
196 OR krvh_state_is_controlling = 1)
197 )
198 ))
199 RETURN;
200 END;