1 USE [Test]
2 GO
3 /****** Object: Trigger [dbo].[DBpoitem_mstIup] Script Date: 2017/6/1 9:35:41 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8
9 ALTER TRIGGER [dbo].[DBpoitem_mstIup]
10 ON [dbo].[poitem_mst]
11 FOR UPDATE
12 AS
13 DECLARE @Site SiteType
14 , @InsertFlag TINYINT
15 , @ActionExpression NVARCHAR(60)
16 SELECT
17 @InsertFlag = CASE
18 WHEN EXISTS ( SELECT 1 FROM deleted ) THEN 0
19 ELSE 1
20 END
21
22 DECLARE @UserName LongListType
23 SET @UserName = dbo.UserNameSp()
24
25 DECLARE
26 @Severity INT
27 , @Infobar InfobarType
28 , @BypassPoitemTrg ListYesNoType
29 , @ESBSupplierBalanceFlag ListYesNoType
30
31 SET @Severity = 0
32
33 DECLARE
34 @poitemRowPointer RowPointerType
35 , @PoNum PoNumType
36 , @PoVendNum VendNumType
37 , @PoLine PoLineType
38 , @PoRelease PoReleaseType
39 , @oldPoitemStat PoitemStatType
40 , @newPoitemStat PoitemStatType
41 , @oldDueDate DateType
42 , @newDueDate DateType
43 , @oldPromiseDate DateType
44 , @newPromiseDate DateType
45 , @oldReleaseDate DateType
46 , @newReleaseDate DateType
47 , @oldItem ItemType
48 , @newItem ItemType
49 , @oldVendItem VendItemType
50 , @newVendItem VendItemType
51 , @oldDescription DescriptionType
52 , @newDescription DescriptionType
53 , @oldItemType PoLineItemTypeType
54 , @newItemType PoLineItemTypeType
55 , @oldCostType FaCostTypeType
56 , @newCostType FaCostTypeType
57 , @oldUM UMType
58 , @newUM UMType
59 , @QtyReceived QtyUnitNoNegType
60 , @oldQtyOrdered QtyUnitNoNegType
61 ,@newQtyOrdered QtyUnitNoNegType
62 , @oldQtyOrderedConv QtyUnitNoNegType
63 , @newQtyOrderedConv QtyUnitNoNegType
64 , @oldPlanCostConv CostPrcType
65 , @newPlanCostConv CostPrcType
66 , @oldItemCostConv CostPrcType
67 , @newItemCostConv CostPrcType
68 , @oldUnitMatCostConv CostPrcType
69 , @newUnitMatCostConv CostPrcType
70 , @oldUnitFreightCostConv CostPrcType
71 , @newUnitFreightCostConv CostPrcType
72 , @oldUnitDutyCostConv CostPrcType
73 , @newUnitDutyCostConv CostPrcType
74 , @oldUnitBrokerageCostConv CostPrcType
75 , @newUnitBrokerageCostConv CostPrcType
76 , @oldUnitInsuranceCostConv CostPrcType
77 , @newUnitInsuranceCostConv CostPrcType
78 , @oldUnitLocFrtCostConv CostPrcType
79 , @newUnitLocFrtCostConv CostPrcType
80 , @oldLcOverride ListYesNoType
81 , @newLcOverride ListYesNoType
82 , @oldExpedited ListYesNoType
83 , @newExpedited ListYesNoType
84 , @oldWhse WhseType
85 , @newWhse WhseType
86 , @oldRevision RevisionType
87 , @newRevision RevisionType
88 , @ItemRevision RevisionType
89 , @oldDrawingNbr DrawingNbrType
90 , @newDrawingNbr DrawingNbrType
91 , @oldRefType RefTypeIJKOTType
92 , @newRefType RefTypeIJKOTType
93 , @oldRefNum CoJobProjTrnNumType
94 , @newRefNum CoJobProjTrnNumType
95 , @oldRefLineSuf CoLineSuffixProjTaskTrnLineType
96 , @newRefLineSuf CoLineSuffixProjTaskTrnLineType
97 , @oldRefRelease CoReleaseOperNumType
98 , @newRefRelease CoReleaseOperNumType
99 , @oldNonInvAcct AcctType
100 , @newNonInvAcct AcctType
101 , @oldNonInvAcctUnit1 UnitCode1Type
102 , @newNonInvAcctUnit1 UnitCode1Type
103 , @oldNonInvAcctUnit2 UnitCode2Type
104 , @newNonInvAcctUnit2 UnitCode2Type
105 , @oldNonInvAcctUnit3 UnitCode3Type
106 , @newNonInvAcctUnit3 UnitCode3Type
107 , @oldNonInvAcctUnit4 UnitCode4Type
108 , @newNonInvAcctUnit4 UnitCode4Type
109 , @oldTaxCode1 TaxCodeType
110 , @newTaxCode1 TaxCodeType
111 , @oldTaxCode2 TaxCodeType
112 , @newTaxCode2 TaxCodeType
113 , @oldTransNat TransNatType
114 , @newTransNat TransNatType
115 , @oldTransNat2 TransNat2Type
116 , @newTransNat2 TransNat2Type
117 , @oldDelterm DeltermType
118 , @newDelterm DeltermType
119 , @oldProcessInd ProcessIndType
120 , @newProcessInd ProcessIndType
121 , @oldEcCode EcCodeType
122 , @newEcCode EcCodeType
123 , @oldCommCode CommodityCodeType
124 , @newCommCode CommodityCodeType
125 , @oldOrigin EcCodeType
126 , @newOrigin EcCodeType
127 , @oldUnitWeight UnitWeightType
128 , @newUnitWeight UnitWeightType
129 , @oldSupplQtyConvFactor UMConvFactorType
130 , @newSupplQtyConvFactor UMConvFactorType
131 , @oldExportValue AmountType
132 , @newExportValue AmountType
133 , @oldShipAddr DropShipTypeType
134 , @newShipAddr DropShipTypeType
135 , @oldDropShipNo DropShipNoType
136 , @newDropShipNo DropShipNoType
137 , @oldDropSeq DropSeqType
138 , @newDropSeq DropSeqType
139 , @ContainsOnlyTaxFreeMatls ListYesNoType
140 , @TaxFreeMatl ListYesNoType
141 , @OldQtyReceived QtyUnitNoNegType
142 , @oldManufacturerId ManufacturerIdType
143 , @newManufacturerId ManufacturerIdType
144 , @oldManufacturerItem ManufacturerItemType
145 , @newManufacturerItem ManufacturerItemType
146 , @OldPreassignLots ListYesNoType
147 , @NewPreassignLots ListYesNoType
148 , @OldPreassignSerials ListYesNoType
149 , @NewPreassignSerials ListYesNoType
150 , @ProjStat ProjStatusType
151 , @ProjtaskStat ProjStatusType
152 , @AutoReceiveDemandingSitePO ListYesNoType
153 , @newFaNum FaNumType
154 , @oldFaNum FaNumType
155 , @PoNumLinkFaNum PoNumType
156 , @PoLineLinkFaNum PoLineType
157
158
159 DECLARE poitem_mstIupCrs CURSOR LOCAL STATIC READ_ONLY
160 FOR SELECT
161 ii.RowPointer
162 , ii.po_num
163 , ISNULL(ii.po_line, 0)
164 , ISNULL(ii.po_release, 0)
165 , dd.stat
166 , ii.stat
167 , dd.due_date
168 , ii.due_date
169 , dd.expedited
170 , ii.expedited
171 , dd.promise_date
172 , ii.promise_date
173 , dd.release_date
174 , ii.release_date
175 , dd.item
176 , ii.item
177 , dd.vend_item
178 , ii.vend_item
179 , dd.description
180 , ii.description
181 , dd.item_type
182 , ii.item_type
183 , dd.cost_type
184 , ii.cost_type
185 , dd.u_m
186 , ii.u_m
187 ,dd.qty_ordered
188 ,ISNULL(ii.qty_ordered,0)
189 , dd.qty_ordered_conv
190 , ISNULL(ii.qty_ordered_conv, 0)
191 , dd.plan_cost_conv
192 , ii.plan_cost_conv
193 , dd.item_cost_conv
194 , ii.item_cost_conv
195 , dd.unit_mat_cost_conv
196 , ISNULL(ii.unit_mat_cost_conv, 0)
197 , dd.unit_freight_cost_conv
198 , ISNULL(ii.unit_freight_cost_conv, 0)
199 , dd.unit_duty_cost_conv
200 , ISNULL(ii.unit_duty_cost_conv, 0)
201 , dd.unit_brokerage_cost_conv
202 , ISNULL(ii.unit_brokerage_cost_conv, 0)
203 , dd.unit_insurance_cost_conv
204 , ISNULL(ii.unit_insurance_cost_conv, 0)
205 , dd.unit_loc_frt_cost_conv
206 , ISNULL(ii.unit_loc_frt_cost_conv, 0)
207 , dd.lc_override
208 , ii.lc_override
209 , dd.whse
210 , ii.whse
211 , dd.revision
212 , ii.revision
213 , item.revision
214 , dd.drawing_nbr
215 , ii.drawing_nbr
216 , dd.ref_type
217 , ii.ref_type
218 , dd.ref_num
219 , ii.ref_num
220 , dd.ref_line_suf
221 , ISNULL(ii.ref_line_suf, 0)
222 , dd.ref_release
223 , ISNULL(ii.ref_release, 0)
224 , dd.non_inv_acct
225 , ii.non_inv_acct
226 , dd.non_inv_acct_unit1
227 , ii.non_inv_acct_unit1
228 , dd.non_inv_acct_unit2
229 , ii.non_inv_acct_unit2
230 , dd.non_inv_acct_unit3
231 , ii.non_inv_acct_unit3
232 , dd.non_inv_acct_unit4
233 , ii.non_inv_acct_unit4
234 , dd.tax_code1
235 , ii.tax_code1
236 , dd.tax_code2
237 , ii.tax_code2
238 , dd.trans_nat
239 , ii.trans_nat
240 , dd.trans_nat_2
241 , ii.trans_nat_2
242 , dd.delterm
243 , ii.delterm
244 , dd.process_ind
245 , ii.process_ind
246 , dd.ec_code
247 , ii.ec_code
248 , dd.comm_code
249 , ii.comm_code
250 , dd.origin
251 , ii.origin
252 , dd.unit_weight
253 , ii.unit_weight
254 , dd.suppl_qty_conv_factor
255 , ii.suppl_qty_conv_factor
256 , dd.export_value
257 , ii.export_value
258 , dd.ship_addr
259 , ii.ship_addr
260 , dd.drop_ship_no
261 , ii.drop_ship_no
262 , dd.drop_seq
263 , ii.drop_seq
264 , dd.qty_received
265 , ii.qty_received
266 , dd.manufacturer_id
267 , ii.manufacturer_id
268 , dd.manufacturer_item
269 , ii.manufacturer_item
270 , dd.preassign_lots
271 , ii.preassign_lots
272 , dd.preassign_serials
273 , ii.preassign_serials
274 , po.auto_receive_demanding_site_po
275 , ii.fa_num
276 , dd.fa_num
277 FROM inserted ii
278 LEFT OUTER JOIN deleted AS dd ON
279 dd.RowPointer = ii.RowPointer
280 left outer join po WITH (HOLDLOCK) on
281 po.po_num = ii.po_num
282 left outer join vendor on
283 vendor.vend_num = po.vend_num
284 left outer join currency with (readuncommitted) on
285 currency.curr_code = vendor.curr_code
286 left outer join item on
287 item.item = ii.item
288 LEFT OUTER JOIN non_inventory_item ON
289 non_inventory_item.item = ii.item
290
291 OPEN poitem_mstIupCrs
292 WHILE @Severity = 0
293 BEGIN -- cursor loop
294 FETCH poitem_mstIupCrs INTO
295 @poitemRowPointer
296 , @PoNum
297 , @PoLine
298 , @PoRelease
299 , @oldPoitemStat
300 , @newPoitemStat
301 , @oldDueDate
302 , @newDueDate
303 , @oldExpedited
304 , @newExpedited
305 , @oldPromiseDate
306 , @newPromiseDate
307 , @oldReleaseDate
308 , @newReleaseDate
309 , @oldItem
310 , @newItem
311 , @oldVendItem
312 , @newVendItem
313 , @oldDescription
314 , @newDescription
315 , @oldItemType
316 , @newItemType
317 , @oldCostType
318 , @newCostType
319 , @oldUM
320 , @newUM
321 ,@oldQtyOrdered
322 ,@newQtyOrdered
323 , @oldQtyOrderedConv
324 , @newQtyOrderedConv
325 , @oldPlanCostConv
326 , @newPlanCostConv
327 , @oldItemCostConv
328 , @newItemCostConv
329 , @oldUnitMatCostConv
330 , @newUnitMatCostConv
331 , @oldUnitFreightCostConv
332 , @newUnitFreightCostConv
333 , @oldUnitDutyCostConv
334 , @newUnitDutyCostConv
335 , @oldUnitBrokerageCostConv
336 , @newUnitBrokerageCostConv
337 , @oldUnitInsuranceCostConv
338 , @newUnitInsuranceCostConv
339 , @oldUnitLocFrtCostConv
340 , @newUnitLocFrtCostConv
341 , @oldLcOverride
342 , @newLcOverride
343 , @oldWhse
344 , @newWhse
345 , @oldRevision
346 , @newRevision
347 , @ItemRevision
348 , @oldDrawingNbr
349 , @newDrawingNbr
350 , @oldRefType
351 , @newRefType
352 , @oldRefNum
353 , @newRefNum
354 , @oldRefLineSuf
355 , @newRefLineSuf
356 , @oldRefRelease
357 , @newRefRelease
358 , @oldNonInvAcct
359 , @newNonInvAcct
360 , @oldNonInvAcctUnit1
361 , @newNonInvAcctUnit1
362 , @oldNonInvAcctUnit2
363 , @newNonInvAcctUnit2
364 , @oldNonInvAcctUnit3
365 , @newNonInvAcctUnit3
366 , @oldNonInvAcctUnit4
367 , @newNonInvAcctUnit4
368 , @oldTaxCode1
369 , @newTaxCode1
370 , @oldTaxCode2
371 , @newTaxCode2
372 , @oldTransNat
373 , @newTransNat
374 , @oldTransNat2
375 , @newTransNat2
376 , @oldDelterm
377 , @newDelterm
378 , @oldProcessInd
379 , @newProcessInd
380 , @oldEcCode
381 , @newEcCode
382 , @oldCommCode
383 , @newCommCode
384 , @oldOrigin
385 , @newOrigin
386 , @oldUnitWeight
387 , @newUnitWeight
388 , @oldSupplQtyConvFactor
389 , @newSupplQtyConvFactor
390 , @oldExportValue
391 , @newExportValue
392 , @oldShipAddr
393 , @newShipAddr
394 , @oldDropShipNo
395 , @newDropShipNo
396 , @oldDropSeq
397 , @newDropSeq
398 , @OldQtyReceived
399 , @QtyReceived
400 , @oldManufacturerId
401 , @newManufacturerId
402 , @oldManufacturerItem
403 , @newManufacturerItem
404 , @OldPreassignLots
405 , @NewPreassignLots
406 , @OldPreassignSerials
407 , @NewPreassignSerials
408 , @AutoReceiveDemandingSitePO
409 , @newFaNum
410 , @oldFaNum
411
412 IF @@FETCH_STATUS = -1
413 BREAK
414
415 IF UPDATE(Stat) AND @newPoitemStat=N'O'
416 BEGIN
417 EXEC dbo.DBPoItemToInvLotSp @Stat = @newPoitemStat, -- PoitemStatType
418 @PoNum = @PoNum, -- PoNumType
419 @PoLine = @PoLine, -- PoLineType
420 @PoRelease = @PoRelease, -- PoReleaseType
421 @Item = @newItem, -- ItemType
422 @Qty_ordered = @newQtyOrdered-- QtyUnitNoNegType
423 END
424 END -- End of cursor loop
425
426 CLOSE poitem_mstIupCrs
427 DEALLOCATE poitem_mstIupCrs
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有