54 lines
3.1 KiB
MySQL
54 lines
3.1 KiB
MySQL
|
-- Draft p1790621868706_fb5f7cf3 - SuperValu invoice 907260825592619
|
||
|
|
-- 3 of 3: ProductBatchesHeader + ProductBatches, the batch BatchService would write.
|
||
|
|
-- Only rotini qualifies: the other lines have no invoiced cost, and for a repeated UPC
|
||
|
|
-- the first row with a cost wins (20.53 / pack 1 = 20.53 unit cost vs 0 on file).
|
||
|
|
-- Detail rows copy every column Products and ProductBatches share, then set cost/pack/audit.
|
||
|
|
USE posbdat;
|
||
|
|
GO
|
||
|
|
|
||
|
|
SET XACT_ABORT ON;
|
||
|
|
BEGIN TRAN;
|
||
|
|
|
||
|
|
DECLARE @b varchar(25) = 'INV_907260825592619';
|
||
|
|
|
||
|
|
IF EXISTS (SELECT 1 FROM dbo.ProductBatchesHeader WITH (UPDLOCK, HOLDLOCK) WHERE BatchNo = @b)
|
||
|
|
BEGIN
|
||
|
|
ROLLBACK;
|
||
|
|
RAISERROR('Batch INV_907260825592619 already exists', 16, 1);
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
|
||
|
|
INSERT INTO dbo.ProductBatchesHeader
|
||
|
|
(BatchNo, Description, StartDate, EndDate, StartTime, EndTime, Priority, Type, WhoApplied, WhoCreated, Created)
|
||
|
|
VALUES
|
||
|
|
(@b, 'Invoice 907260825592619 vendor 1 cost change', GETDATE(), GETDATE(),
|
||
|
|
'1900-01-01 00:00:00.000', '1900-01-01 23:59:00.000', 1, 'PB', 888, 888, GETDATE());
|
||
|
|
|
||
|
|
INSERT INTO dbo.ProductBatches
|
||
|
|
(BatchNo, cost, pack, Created, CreatedBy, modified, cost_modified,
|
||
|
|
active, advertised, attributes, cert_code, deleted, Department, description, discount, dsd,
|
||
|
|
effectiveschedule, end_date, EndTime, foodstamp, FSACategory, groupprice, groupprice2, groupprice3,
|
||
|
|
groupprice4, groupprice5, longdescription, MaxDiscount, mixmatchcode, normal_price, picture_name,
|
||
|
|
Points, pricemethod, QualifiedFlatAmount, QualifiedPercent, quantity, quantity2, quantity3, quantity4,
|
||
|
|
quantity5, scale, seconddescription, Section, size, special_price, specialcost, specialgroupprice,
|
||
|
|
Specialgroupprice2, Specialgroupprice3, Specialgroupprice4, Specialgroupprice5, specialpricemethod,
|
||
|
|
specialquantity, Specialquantity2, Specialquantity3, Specialquantity4, Specialquantity5, start_date,
|
||
|
|
StartTime, tareweight, target_margin, tax, TOB, unitofmeasure, upc, upc_link, validage, Vendor,
|
||
|
|
whomodified, wicable)
|
||
|
|
SELECT @b, v.cost, v.pack, GETDATE(), 'INVOICE', GETDATE(), GETDATE(),
|
||
|
|
p.active, p.advertised, p.attributes, p.cert_code, p.deleted, p.department, p.description, p.discount, p.dsd,
|
||
|
|
p.effectiveschedule, p.end_date, p.EndTime, p.foodstamp, p.FSACategory, p.groupprice, p.groupprice2, p.groupprice3,
|
||
|
|
p.groupprice4, p.groupprice5, p.longdescription, p.maxdiscount, p.mixmatchcode, p.normal_price, p.picture_name,
|
||
|
|
p.Points, p.pricemethod, p.QualifiedFlatAmount, p.QualifiedPercent, p.quantity, p.quantity2, p.quantity3, p.quantity4,
|
||
|
|
p.quantity5, p.scale, p.seconddescription, p.section, p.size, p.special_price, p.specialcost, p.specialgroupprice,
|
||
|
|
p.Specialgroupprice2, p.Specialgroupprice3, p.Specialgroupprice4, p.Specialgroupprice5, p.specialpricemethod,
|
||
|
|
p.specialquantity, p.Specialquantity2, p.Specialquantity3, p.Specialquantity4, p.Specialquantity5, p.start_date,
|
||
|
|
p.StartTime, p.tareweight, p.target_margin, p.tax, p.TOB, p.unitofmeasure, p.upc, p.upc_link, p.validage, p.vendor,
|
||
|
|
p.whomodified, p.wicable
|
||
|
|
FROM (VALUES ('0002680000550', 20.53, 1)) AS v(upc, cost, pack)
|
||
|
|
JOIN dbo.Products p ON p.upc = v.upc;
|
||
|
|
|
||
|
|
SELECT @@ROWCOUNT AS batch_rows;
|
||
|
|
COMMIT;
|
||
|
|
GO
|