AiDOPWarehouse/doc/db/mdp/aidopb_b2_receipt_nbr_seed.sql

-- AIDOPB UAT B2: PurOrdRct (rc) + NbrMaster (WOI/WOD) minimal seed
-- tenant_id = 824585161322565; Domain = 2410 (AIDOPB ItemMaster)
-- Idempotent by business keys Receiver / Nbr + tenant_id
-- RecID uses high fixed ints (tables are INT PK; PurOrdRct* not AUTO_INCREMENT)

SET @tid := 824585161322565;
SET @domain := '2410';
SET @item := '1A0044F';
SET @supp := '3100';
SET @loc := '8001';
SET @um := 'PC';

-- ========== S4/S5 purchase receipt rc ==========
INSERT INTO PurOrdRctMaster (
  RecID, Domain, Receiver, RctType, RctDate, OrdNbr, PerformDate, EffTime,
  Supp, OrdType, Ufld1, Remark, CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Print, IsChanged, TaxIn, IsExport, IsPlan, IsBackwash,
  BusinessID, tenant_id
)
SELECT
  900101, @domain, 'B-RC-001', 'rc', CURDATE(), 'B-PO-SEED-001', CURDATE(), CURDATE(),
  @supp, 'PO', 'B-RC-001', 'AIDOPB UAT B2 seed rc', 'UAT', 'UAT', NOW(), NOW(),
  1, 1, 0, 0, 0, 0, 0, 0,
  0, @tid
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctMaster WHERE tenant_id=@tid AND Receiver='B-RC-001' AND RctType='rc'
);

INSERT INTO PurOrdRctDetail (
  RecID, Domain, Receiver, OrdNbr, OrdLine, RctType, Line,
  QtyReceived, RctQty, QtyReturn, QtyOrded, ItemNum, LotSerial, UM, UMConversion,
  Dimension1, Dimension2, POCost, BlanketLine, ProjectLine,
  RctDate, PerformDate, EffTime, Supp, Location, Potype, RctNbr, RctLine,
  PurOrdRctRecID, CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, IsChanged, TaxIn, IsClosed, IsRounding, BusinessID, QcQty, tenant_id
)
SELECT
  900101, @domain, 'B-RC-001', 'B-PO-SEED-001', 1, 'rc', 1,
  10, 10, 0, 10, @item, 'LOT-B-RC-001', @um, 1,
  '', '', 0, 0, 0,
  CURDATE(), CURDATE(), CURDATE(), @supp, @loc, 'po', 'B-RC-001', 10,
  900101, 'UAT', 'UAT', NOW(), NOW(),
  1, 1, 0, 0, 0, 0, 0, 0, @tid
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctDetail WHERE tenant_id=@tid AND RecID=900101
);

-- ========== S5 production inbound WOI ==========
INSERT INTO NbrMaster (
  RecID, Domain, Type, Nbr, Date, EffDate, WorkOrd, QtyOrd, QtyRec,
  Department, Remark, Name, Status, CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Print, IsChanged, IsGP12Checked, IsPlan, IsReturn, BusinessID, Op, tenant_id
)
SELECT
  900201, @domain, 'WOI', 'B-WOI-001', CURDATE(), CURDATE(), 'B-WO-SEED-001', 10, 10,
  '101', 'AIDOPB UAT B2 seed WOI', 'UAT', 'C', 'UAT', 'UAT', NOW(), NOW(),
  1, 1, 0, 0, 0, 0, 0, 0, 0, @tid
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM NbrMaster WHERE tenant_id=@tid AND Type='WOI' AND Nbr='B-WOI-001'
);

INSERT INTO NbrDetail (
  RecID, Domain, Type, Nbr, Line, ItemNum, ItemName, LocationFrom, LocationTo,
  QtyFrom, QtyTo, QtyOrd, QtyRec, UM, LotSerial, WorkOrd, NbrRecID,
  CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Print, IsChanged, IsGP12Demand, IsGP12Checked, BusinessID, SeqID, OrdLine, Material, tenant_id
)
SELECT
  900201, @domain, 'WOI', 'B-WOI-001', 1, @item, 'AIDOPB seed item', @loc, @loc,
  0, 10, 10, 10, @um, 'LOT-B-WOI-001', 'B-WO-SEED-001', 900201,
  'UAT', 'UAT', NOW(), NOW(),
  1, 1, 0, 0, 0, 0, 0, 0, 0, 0, @tid
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM NbrDetail WHERE tenant_id=@tid AND RecID=900201
);

-- ========== S5 production return WOD ==========
INSERT INTO NbrMaster (
  RecID, Domain, Type, Nbr, Date, EffDate, WorkOrd, QtyOrd, QtyRec,
  Department, Remark, Name, Status, CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Print, IsChanged, IsGP12Checked, IsPlan, IsReturn, BusinessID, Op, tenant_id
)
SELECT
  900202, @domain, 'WOD', 'B-WOD-001', CURDATE(), CURDATE(), 'B-WO-SEED-001', 2, 2,
  '101', 'AIDOPB UAT B2 seed WOD', 'UAT', 'C', 'UAT', 'UAT', NOW(), NOW(),
  1, 1, 0, 0, 0, 0, 0, 0, 0, @tid
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM NbrMaster WHERE tenant_id=@tid AND Type='WOD' AND Nbr='B-WOD-001'
);

INSERT INTO NbrDetail (
  RecID, Domain, Type, Nbr, Line, ItemNum, ItemName, LocationFrom, LocationTo,
  QtyFrom, QtyTo, QtyOrd, QtyRec, UM, LotSerial, WorkOrd, NbrRecID,
  CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Print, IsChanged, IsGP12Demand, IsGP12Checked, BusinessID, SeqID, OrdLine, Material, tenant_id
)
SELECT
  900202, @domain, 'WOD', 'B-WOD-001', 1, @item, 'AIDOPB seed item', @loc, @loc,
  2, 0, 2, 2, @um, 'LOT-B-WOD-001', 'B-WO-SEED-001', 900202,
  'UAT', 'UAT', NOW(), NOW(),
  1, 1, 0, 0, 0, 0, 0, 0, 0, 0, @tid
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM NbrDetail WHERE tenant_id=@tid AND RecID=900202
);