AiDOPWarehouse/doc/plan/sql/tools/extract_full_from_dopdemorq.sql

SET NOCOUNT ON;
DECLARE @bills TABLE (bill_no NVARCHAR(64) PRIMARY KEY);
-- bill list injected by PowerShell

INSERT INTO @bills(bill_no) VALUES (N'__PLACEHOLDER__');

SELECT 'SO' AS kind,
       o.bill_no,
       CAST(o.Id AS NVARCHAR(32)) AS src_id,
       ISNULL(o.custom_no, N'') AS custom_no,
       ISNULL(o.custom_name, N'') AS custom_name,
       CONVERT(VARCHAR(23), o.[date], 121) AS order_date,
       ISNULL(o.urgent, 0) AS urgent
FROM crm_seorder o
WHERE o.bill_no IN (SELECT bill_no FROM @bills)
  AND ISNULL(o.IsDeleted, 0) = 0;

SELECT 'LINE' AS kind,
       e.bill_no,
       CAST(e.Id AS NVARCHAR(32)) AS src_entry,
       ISNULL(e.entry_seq, ROW_NUMBER() OVER (PARTITION BY e.bill_no ORDER BY e.Id)) AS entry_seq,
       ISNULL(e.item_number, N'') AS item_number,
       ISNULL(e.qty, 0) AS qty,
       CONVERT(VARCHAR(23), ISNULL(e.plan_date, e.[date]), 121) AS plan_date
FROM crm_seorderentry e
WHERE e.bill_no IN (SELECT bill_no FROM @bills)
  AND ISNULL(e.IsDeleted, 0) = 0;

SELECT 'WO' AS kind,
       e.bill_no,
       CAST(e.Id AS NVARCHAR(32)) AS src_entry,
       mo.morder_no,
       ISNULL(mo.product_code, e.item_number) AS product_code,
       ISNULL(mo.need_number, e.qty) AS need_number
FROM crm_seorderentry e
INNER JOIN mes_moentry me ON me.soentry_id = e.Id AND ISNULL(me.IsDeleted, 0) = 0
INNER JOIN mes_morder mo ON mo.Id = me.moentry_moid AND ISNULL(mo.IsDeleted, 0) = 0
WHERE e.bill_no IN (SELECT bill_no FROM @bills)
  AND ISNULL(e.IsDeleted, 0) = 0;