AiDOPWarehouse/doc/migrations/2026-05-10_s3_generation_validation_seed.sql

-- S3 generation validation seed data for aidopdev.
-- Source samples are selected from old SQL Server dopdemorq:
--   delivery schedule candidate: PurOrd 4500010230 / Line 20
--   process outsource candidate: WorkOrd M500028878 / Op 60 / Supplier VEN00060
--
-- This script is intentionally small and idempotent. It only inserts fields
-- required by the current S3 generation services plus target NOT NULL fields
-- that have no database default.

START TRANSACTION;

SET @seed_tenant_id := 797403760988229;
SET @seed_now := NOW();

-- 1) Delivery schedule generation candidate.
INSERT INTO PurOrdMaster (
  Confirming, CreditTermsInt, Disc, ExchRate, EstVal, ExchRate1, ExchRate2,
  FixedPrice, FixedRate, Frt, PartialOK, AmtPrepaid, PrintPO, PST, Recurr,
  `Release`, Revision, Scheduled, ServiceCharge, SpecialCharge, Taxable,
  Tax1, Tax2, Tax3, TransportDays, IsActive, IsConfirm, Potype, IsChanged,
  TaxIn, Amt, IsPriceChanged,
  Buyer, Domain, PurOrd, OrdDate, ReqBy, Status, Supp, CreateUser, CreateTime,
  UpdateUser, UpdateTime, Department, Curr, tenant_id
)
SELECT
  0, 0, 0, 1, 0, 1, 1,
  0, 0, 0, 1, 0, 0, 0, 0,
  0, 0, 0, 0, 0, 1,
  0, 0, 0, 0, 1, 1, 'PT', 0,
  1, 0, 0,
  '110', '8010', '4500010230', '2025-05-10', 'PO', '', '10001573', 's3-seed', @seed_now,
  's3-seed', @seed_now, '110', 'CNY', @seed_tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdMaster WHERE PurOrd = '4500010230'
);

SELECT @seed_po_recid := RecID
FROM PurOrdMaster
WHERE PurOrd = '4500010230'
LIMIT 1;

INSERT INTO PurOrdDetail (
  QtyBO, RctCost, CreditTermsInt, UpdateCurrentCost, CumReceived1, CumReceived2,
  CumReceived3, CumReceived4, Disc, FixedPrice, InspectReq, SingleLot, SupplyPer,
  PurOrd, PST, PackingSlipQty, PayUMConv, PurCost, RctQty, QtyOrded, QtyReceived,
  QtyReturned, Active, QtyReleased, RctUMConversion, Scheduled, ScheduledChanged,
  SchedMRPReq, SafetyDays, SafetyHours, StdCost, Taxable, TaxIn, MaxTaxableAmt,
  TransportHours, UMConversion, VAT, IsActive, IsConfirm, Potype, IsChanged,
  TaxRate, IsRounding, ReceiptQty, BarCodeQty, IsClosed, QtyReturnedRefund, CumQtyBO,
  Domain, DueDate, Line, ItemNum, UM, Status, PurOrdRecID, CreateUser, CreateTime,
  UpdateUser, UpdateTime, tenant_id
)
SELECT
  0, 0, 0, 0, 0, 0,
  0, 0, 0, 0, 0, 0, 0,
  '4500010230', 0, 0, 1, 0, 2, 3, 0,
  0, 1, 0, 1, 0, 0,
  0, 0, 0, 0, 1, 1, 0,
  0, 1, 0, 1, 1, 'PT', 0,
  0, 0, 0, 0, 0, 0, 0,
  '8010', '2025-05-20', 20, '112AB6752', 'PC', '', @seed_po_recid, 's3-seed', @seed_now,
  's3-seed', @seed_now, @seed_tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdDetail WHERE PurOrd = '4500010230' AND Line = 20
);

-- 2) Process outsource generation candidate.
INSERT INTO WorkOrdMaster (
  IsActive, IsConfirm, Priority,
  Domain, WorkOrd, ItemNum, QtyOrded, Status, Department, Batch, CreateUser, CreateTime,
  UpdateUser, UpdateTime, tenant_id
)
SELECT
  1, 1, 64,
  '8010', 'M500028878', '91CC0177', 886, 'R', '', 'BATCH-S3-SEED', 's3-seed', @seed_now,
  's3-seed', @seed_now, @seed_tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM WorkOrdMaster WHERE WorkOrd = 'M500028878'
);

INSERT INTO WorkOrdRouting (
  Domain, WorkOrd, Op, ItemNum, ProcessOut, ProcessOutDay, WorkCtr, Status,
  QtyOrded, QtyComplete, CreateUser, CreateTime, UpdateUser, UpdateTime, IsActive,
  IsConfirm, tenant_id
)
SELECT
  '8010', 'M500028878', 60, '91CC0177', 1, 0, 'WC000011', 'r',
  886, 870, 's3-seed', @seed_now, 's3-seed', @seed_now, 1,
  1, @seed_tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM WorkOrdRouting WHERE WorkOrd = 'M500028878' AND Op = 60 AND ItemNum = '91CC0177'
);

-- aidopdev already has RoutingOpDetail(RoutingCode='91CC0177', Op=60, SupplierCode='VEN00060').

COMMIT;

-- Validation queries after API execution:
-- SELECT * FROM NbrDayInfo WHERE NbrType IN ('M8','pw') ORDER BY Today DESC;
-- SELECT * FROM srm_polist_ds WHERE ponumber='4500010230' AND poline=20;
-- SELECT * FROM PurOrdMaster WHERE WorkOrd='M500028878' AND Potype='PW';