-- Draft p1790621868706_fb5f7cf3 - SuperValu invoice 907260825592619
-- 1 of 3: products. The draft has 4 lines but 3 distinct UPCs (rotini is on it twice).
-- Values come from the draft; required flags copy the existing rows (dept 1, tax 0, foodstamp 1).
-- cost is left at 0, as on file today; script 3 (batch) carries the invoiced cost.
USE posbdat;
GO

SET XACT_ABORT ON;
BEGIN TRAN;

INSERT INTO dbo.Products
    (upc, cert_code, description, cost, normal_price, pricemethod, department, vendor, pack, size,
     tax, foodstamp, scale, dsd, active, advertised, deleted, attributes, modified, Created, CreatedBy)
SELECT v.upc, v.cert_code, v.description, 0, v.normal_price, 0, 1, 1, v.pack, v.size,
       0, 1, 0, 0, 1, 0, 0, 0, GETDATE(), GETDATE(), 'INVOICE'
FROM (VALUES
    ('0002680000550', '645479',  'AMER/BEAUTY ROTINI',        1.39, 1,   '16'),
    ('0088810925309', '3491452', 'HST GLAZED HNY BUN',        2.49, 184, NULL),
    ('0019269900079', '4178665', 'SMYNG BLDK ROSE BG BWL 3.', 3.29, 824, '0.65')
) AS v(upc, cert_code, description, normal_price, pack, size)
WHERE NOT EXISTS (SELECT 1 FROM dbo.Products p WHERE p.upc = v.upc);

SELECT @@ROWCOUNT AS products_inserted;
COMMIT;
GO