U8数据库同步至WMS数据库语句

1. 静态数据

1.1. 部门档案(Department)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH DepartmentSync AS(
  SELECT
    cDepCode DEPARTMENT_CODE,
    cDepName DEPARTMENT_NAME,
    cDepFullName DEPARTMENT_DESC,
    'Y' DATA_VALID,
    CONVERT(nvarchar, dp.dModifyDate, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    Department dp
  WHERE
    CONVERT(nvarchar, isnull(dp.dModifyDate, getdate()), 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  DepartmentSync
ORDER BY
  dModifyTime desc

1.2. 用户资料(UserAccount)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
WITH UserAccountSync AS(
  SELECT
    u.cUser_Id USER_ACCOUNT,
    u.cUser_Name USER_NAME,
    u.cPassword USER_PASSWORD,
    u.cUserEmail E_MAIL,
    d.cDepCode DEPARTMENTID,
    h.cPsnMobilePhone MOBILE,
    'Y' DATA_VALID,
    CONVERT(
      nvarchar,
      CASE
        WHEN u.dPasswordDate IS NULL THEN u.dCrDate
        ELSE u.dPasswordDate
      END,
      121
    ) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    UA_User_Ex u
    LEFT JOIN hr_hi_person h ON h.cPsn_Num = u.cUser_Id
    LEFT JOIN Department d ON d.cDepCode = h.cDept_num
  WHERE
    CONVERT(
      nvarchar,
      isnull(
        CASE
          WHEN u.dPasswordDate IS NULL THEN u.dCrDate
          ELSE u.dPasswordDate
        END,
        getdate()
      ),
      121
    ) > CONVERT(nvarchar, '', 121)
)
SELECT
  *
FROM
  UserAccountSync
ORDER BY
  dModifyTime desc

1.3. 客户档案(Customer)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
WITH CustomerSync AS(
  SELECT
    cCuscode CUSTOMER_CODE,
    cCusname CUSTOMER_NAME,
    cCusabbname SHORT_NAME,
    cc.cCCCode CCUSTOMER_CODE,
    cc.cCCName CCUSTOMER_NAME,
    cu.cCusAddress CUSTOMER_ADDRESS,
    cu.cCusPhone CUSTOMER_TEL,
    CONVERT(nvarchar, cu.dCusDevDate, 121) CUSTOMER_DEVDATE,
    'N/A' SALES_CODE,
    0 RMA_CENTERID,
    'Y' DATA_VALID,
    cu.cModifyPerson MODIFY_USERID,
    CONVERT(
      nvarchar,
      (
        CASE
          WHEN cu.dModifyDate IS NOT NULL THEN cu.dModifyDate
          ELSE cu.dCusCreateDatetime
        END
      ),
      121
    ) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    customer cu
    INNER JOIN customerclass cc ON cu.cCCCode = cc.cCCCode
  WHERE
    CONVERT(
      nvarchar,
      isnull(
        CASE
          WHEN cu.dModifyDate IS NULL THEN cu.dCusCreateDatetime
          ELSE cu.dModifyDate
        END,
        getdate()
      ),
      121
    ) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  CustomerSync
ORDER BY
  dModifyTime desc

1.4. 供应商档案(Vendor)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
WITH VendorSync AS(
  SELECT
    vc.cVCCode VENDOR_CCODE,
    vc.cVCName VENDOR_CNAME,
    v.cVenCode VENDOR_CODE,
    v.cVenName VENDOR_NAME,
    v.cVenAbbname SHORT_NAME,
    v.cVenAddress VENDOR_ADDRESS,
    v.cCreatePerson CREATE_USERID,
    'Y' DATA_VALID,
    CONVERT(
      nvarchar,
      (
        CASE
          WHEN v.dModifyDate IS NULL THEN v.dVenCreateDatetime
          ELSE v.dModifyDate
        END
      ),
      121
    ) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    Vendor v
    INNER JOIN VendorClass vc ON v.cVCCode = vc.cVCCode
  WHERE
    CONVERT(
      nvarchar,
      isnull(
        CASE
          WHEN v.dModifyDate IS NULL THEN v.dVenCreateDatetime
          ELSE v.dModifyDate
        END,
        getdate()
      ),
      121
    ) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  VendorSync
ORDER BY
  dModifyTime desc

1.5. 仓库信息(Warehouse)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
WITH WarehouseSync AS(
  SELECT
    w.cWhCode WAREHOUSE_NO,
    w.cWhName WAREHOUSE_NAME,
    w.cDepCode DEPARTMENT_NO,
    w.cWhAddress WAREHOUSE_ADDRESS,
    (
      CASE
        WHEN w.bWhPos = 1 THEN 'Y'
        ELSE 'N'
      END
    ) POSITION_FLAG,
    w.cWhPerson WAREHOUSE_PERSON,
    w.cWhPhone WAREHOUSE_TEL,
    'Y' DATA_VALID,
    CONVERT(nvarchar, w.dModifyDate, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    Warehouse w
  WHERE
    CONVERT(nvarchar, isnull(w.dModifyDate, getdate()), 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  WarehouseSync
ORDER BY
  dModifyTime desc

1.6. 货位信息(Location)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH LocationSync AS(
  SELECT
    p.cPosCode LOCATION_NO,
    p.cPosName LOCATION_DESC,
    p.cWhCode WAREHOUSE_CODE,
    p.cMemo LOCATION_REMARK,
    'Y' DATA_VALID,
    CONVERT(bit, 0) IsSync
  FROM
    POSITION p
  WHERE
    bPosEnd = 1
    AND CONVERT(nvarchar, p.cPosCode, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  LocationSync
ORDER BY
  LOCATION_NO desc

1.7. 物料档案(Partnumber)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
WITH PartnumberSync AS(
  SELECT
    DISTINCT i.cInvCode PART_NUMBER,
    i.cInvName PART_DESC1,
    i.cInvStd PART_DESC2,
    com.cComUnitName PART_UNIT,
    i.cInvCCode PART_TYPECODE,
    ic.cInvCName PART_TYPE,
    cVenCode VENDOR_CODE,
    cPosition LOCATION_NO,
    (
      CASE
        WHEN i.bPropertyCheck = 1 THEN 'Y'
        ELSE 'N'
      END
    ) QC_FLAG,
    --是否质检
    (
      CASE
        WHEN i.bInvQuality = 1 THEN 'Y'
        ELSE 'N'
      END
    ) QUALITY_FLAG,
    --是否保质期管理 
    (
      CASE
        WHEN i.bInvBatch = 1 THEN 'Y'
        ELSE 'N'
      END
    ) BATCH_FLAG,
    --是否批次管理 
    'Y' DATA_VALID,
    CONVERT(nvarchar, i.dModifyDate, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    inventory i
    INNER JOIN inventoryclass ic ON i.cInvCCode = ic.cInvCCode
    INNER JOIN ComputationUnit com ON com.cComunitCode = i.cComUnitCode
  WHERE
    CONVERT(nvarchar, isnull(i.dModifyDate, getdate()), 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  PartnumberSync
ORDER BY
  dModifyTime desc

2. 动态数据

2.1. 入库作业

2.1.1 收料

采购（委外）到货单（ArrivalVouch）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
WITH ArrivalVouchSync AS(
  SELECT
    CAST(ph.cCode AS nvarchar) + '|' + RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(phs.ivouchrowno) > 0 THEN phs.ivouchrowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) RTID,
    ph.cCode RTNO,
    RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(phs.ivouchrowno) > 0 THEN phs.ivouchrowno
            ELSE 0
          END
        ) AS VARCHAR
      ),
      3
    ) ITEMNO,
    v.cVenCode VENDORID,
    'N/A' VENDOR_SITE,
    'N/A' VENDOR_DELIVERYNO,
    phs.cInvCode PARTID,
    'N/A' VERSION,
    'Y' PROPERTYCHECK,
    CAST(
      isnull(phs.fValidQuantity, 0) + isnull(phs.fDegradeQuantity, 0) AS DECIMAL(10, 4)
    ) RT_QTY,
    ph.cBusType RT_TYPE,
    'N/A' BRAND,
    'N/A' ORIGIN,
    'Initial' RT_STATUS,
    ph.cMaker RT_USERID,
    ph.cpocode PO_NO,
    CONVERT(nvarchar, ph.dDate, 121) PLAN_RTDATE,
    getdate() ACTURAL_RTDATE,
    (
      CASE
        WHEN phs.bexigency = 1 THEN 'Y'
        ELSE 'N'
      END
    ) URGENT_STATUS,
    (
      CASE
        WHEN phs.bGsp = 1 THEN 'Y'
        ELSE 'N'
      END
    ) QC_FLAG,
    ctransordercode ASN_NO,
    cgeneralordercode ASN_ITEMNO,
    'N/A' WORK_ORDER,
    phs.cBatch ERPLOT_NO,
    'N/A' IQC_LOTNO,
    0 MINPACK_LOTSIZE,
    phs.cGspState QC_RESULT,
    phs.cWhCode WAREHOUSEID,
    'N/A' WAIVE_NO,
    CONVERT(nvarchar, q.DVERIFYTIME, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    PU_ArrivalVouchs phs
    INNER JOIN PU_ArrivalVouch ph ON ph.id = phs.id
    AND phs.cCloser IS NULL
    INNER JOIN QMCHECKVOUCHER q ON q.SOURCEID = ph.id
    AND q.SOURCEAUTOID = phs.Autoid
    INNER JOIN vendor v ON v.cVenCode = ph.cVenCode
  WHERE
    ph.cverifier IS NOT NULL
    AND q.DVERIFYTIME IS NOT NULL
    AND ph.cCloser IS NULL
    AND (
      ph.cBusType = '普通采购'
      OR ph.cBusType = '委外加工'
    )
    AND ph.bNegative = 0
    AND phs.bGsp = 1
    AND CONVERT(nvarchar, q.DVERIFYTIME, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  ArrivalVouchSync
ORDER BY
  dModifyTime desc

采购（委外）到货单（免检）（ArrivalvouchInspection）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
WITH ArrivalvouchInspectionSync AS(
  SELECT
    CAST(ph.cCode AS nvarchar) + '|' + RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(phs.ivouchrowno) > 0 THEN phs.ivouchrowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) RTID,
    ph.cCode RTNO,
    RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(phs.ivouchrowno) > 0 THEN phs.ivouchrowno
            ELSE 0
          END
        ) AS VARCHAR
      ),
      3
    ) ITEMNO,
    v.cVenCode VENDORID,
    'N/A' VENDOR_SITE,
    'N/A' VENDOR_DELIVERYNO,
    phs.cInvCode PARTID,
    'N/A' VERSION,
    'N' PROPERTYCHECK,
    CAST(phs.iQuantity AS DECIMAL(10, 4)) RT_QTY,
    ph.cBusType RT_TYPE,
    'N/A' BRAND,
    'N/A' ORIGIN,
    'Initial' RT_STATUS,
    ph.cMaker RT_USERID,
    ph.cpocode PO_NO,
    CONVERT(nvarchar, ph.dDate, 121) PLAN_RTDATE,
    getdate() ACTURAL_RTDATE,
    (
      CASE
        WHEN phs.bexigency = 1 THEN 'Y'
        ELSE 'N'
      END
    ) URGENT_STATUS,
    (
      CASE
        WHEN phs.bGsp = 1 THEN 'Y'
        ELSE 'N'
      END
    ) QC_FLAG,
    ctransordercode ASN_NO,
    cgeneralordercode ASN_ITEMNO,
    'N/A' WORK_ORDER,
    phs.cBatch ERPLOT_NO,
    'N/A' IQC_LOTNO,
    0 MINPACK_LOTSIZE,
    phs.cGspState QC_RESULT,
    phs.cWhCode WAREHOUSEID,
    'N/A' WAIVE_NO,
    CONVERT(nvarchar, ph.caudittime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    PU_ArrivalVouchs phs
    INNER JOIN PU_ArrivalVouch ph ON ph.id = phs.id
    AND phs.cCloser IS NULL
    INNER JOIN vendor v ON v.cVenCode = ph.cVenCode
  WHERE
    ph.cverifier IS NOT NULL
    AND ph.caudittime IS NOT NULL
    AND ph.cCloser IS NULL
    AND (
      ph.cBusType = '普通采购'
      OR ph.cBusType = '委外加工'
    )
    AND ph.bNegative = 0
    AND phs.bGsp = 0
    AND CONVERT(nvarchar, ph.caudittime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  ArrivalvouchInspectionSync
ORDER BY
  dModifyTime desc

2.1.2 产成品入库

生产订单(FgstockinManufacture)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH FgstockinManufactureSync AS(
  SELECT
    o.MoCode FORM_NO,
    'Initial' FORM_STATUS,
    '' REMARK,
    '生产订单' FORM_TYPE,
    '' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    NULL PROCESS_STARTTIME,
    NULL PROCESS_ENDTIME,
    'I04' WRH_FORMCODE,
    NULL CREATE_TYPE,
    NULL SOURCE_NO,
    NULL SITEID,
    o.CreateUser REQUEST_USERID,
    o.CreateDate CREATE_DATE,
    mot.RelsUser MODIFY_USERID,
    CONVERT(nvarchar, mot.RelsTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    mom_order o
    INNER JOIN mom_orderdetail mot ON mot.moid = o.moid
  WHERE
    mot.Status = 3
    AND mot.RelsTime IS NOT NULL
    AND mot.RelsUser IS NOT NULL
    AND CONVERT(nvarchar, mot.RelsTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinManufactureSync
ORDER BY
  dModifyTime DESC

生产订单明细(FgstockinManufactureDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH FgstockinManufactureDetailSync AS(
  SELECT
    o.MoCode + '|' + RIGHT('000' + CAST(od.SortSeq AS nvarchar), 3) FORM_RECID,
    o.MoCode FORM_NO,
    RIGHT('000' + CAST(od.SortSeq AS nvarchar), 3) ITEMNO,
    '生产订单' FORM_TYPE,
    '' WORK_ORDER,
    od.InvCode PARTID,
    'N/A' VERSION,
    od.WhCode WAREHOUSEID,
    od.MoLotCode ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(od.Qty AS DECIMAL(10, 4)) RETURN_QTY,
    0 ACTUAL_QTY,
    od.remark REMARK,
    '' ITEMNO_FLAG,
    0 STOCKIN_QTY,
    'N/A' RTNPACKAGE_ID,
    od.QcFlag QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    NULL WAIVE_NO,
    NULL WRH_ORG,
    NULL SITEID,
    NULL DID,
    od.RelsUser MODIFY_USERID,
    CONVERT(nvarchar, od.RelsTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    mom_orderdetail od
    INNER JOIN mom_order o ON o.MOID = od.MOID
  WHERE
    od.RelsTime IS NOT NULL
    AND od.RelsUser IS NOT NULL
    AND CONVERT(nvarchar, od.RelsTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinManufactureDetailSync
ORDER BY
  dModifyTime DESC

委外订单(FgstockinOutsourcing)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH FgstockinOutsourcingSync AS(
  SELECT
    mo.cCode FORM_NO,
    'Initial' FORM_STATUS,
    mo.cMemo REMARK,
    '委外订单' FORM_TYPE,
    '' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    NULL PROCESS_STARTTIME,
    NULL PROCESS_ENDTIME,
    'I01' WRH_FORMCODE,
    NULL CREATE_TYPE,
    NULL SOURCE_NO,
    NULL SITEID,
    mo.cMaker REQUEST_USERID,
    mo.dCreateTime CREATE_DATE,
    mo.cVerifier MODIFY_USERID,
    CONVERT(nvarchar, mo.dVerifyTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    OM_MOMain mo
  WHERE
    mo.cVerifier IS NOT NULL
    AND mo.dVerifyTime IS NOT NULL
    AND CONVERT(nvarchar, mo.dVerifyTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinOutsourcingSync
ORDER BY
  dModifyTime DESC

委外订单明细(FgstockinOutsourcingDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH FgstockinOutsourcingDetailSync AS(
  SELECT
    CAST(od.MOID AS nvarchar) + '|' + RIGHT('000' + CAST(od.iVouchRowNo AS nvarchar), 3) FORM_RECID,
    o.MOID FORM_NO,
    RIGHT('000' + CAST(od.iVouchRowNo AS nvarchar), 3) ITEMNO,
    '委外订单' FORM_TYPE,
    NULL WORK_ORDER,
    od.cInvCode PARTID,
    NULL VERSION,
    NULL WAREHOUSEID,
    NULL ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(od.iQuantity AS DECIMAL(10, 4)) RETURN_QTY,
    0 ACTUAL_QTY,
    od.cbMemo REMARK,
    '' ITEMNO_FLAG,
    0 STOCKIN_QTY,
    NULL RTNPACKAGE_ID,
    od.bGsp QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    NULL WAIVE_NO,
    NULL WRH_ORG,
    NULL SITEID,
    NULL DID,
    o.cVerifier MODIFY_USERID,
    CONVERT(nvarchar, o.dVerifyTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    OM_MODetails od
    INNER JOIN OM_MOMain o ON o.MOID = od.MOID
  WHERE
    o.dVerifyTime IS NOT NULL
    AND o.cVerifier IS NOT NULL
    AND CONVERT(nvarchar, o.dVerifyTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinOutsourcingDetailSync
ORDER BY
  dModifyTime DESC

工单退料单(FgstockinReturnMaterial)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
WITH FgstockinReturnMaterialSync AS(
  SELECT
    o.cCode FORM_NO,
    'Initial' FORM_STATUS,
    o.cMemo REMARK,
    '工单退料单' FORM_TYPE,
    '' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    NULL PROCESS_STARTTIME,
    NULL PROCESS_ENDTIME,
    'I03' WRH_FORMCODE,
    NULL CREATE_TYPE,
    NULL SOURCE_NO,
    NULL SITEID,
    o.cHandler REQUEST_USERID,
    o.dnmaketime CREATE_DATE,
    (
      CASE
        WHEN o.cHandler IS NULL THEN o.cMaker
        ELSE o.cHandler
      END
    ) MODIFY_USERID,
    CONVERT(
      nvarchar,
      CASE
        WHEN o.dnverifytime IS NULL THEN o.dnmaketime
        ELSE o.dnverifytime
      END,
      121
    ) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    rdrecords11 od
    INNER JOIN rdrecord11 o ON od.ID = o.ID
  WHERE
    od.iQuantity < 0
    AND CONVERT(
      nvarchar,
      CASE
        WHEN o.dnverifytime IS NULL THEN o.dnmaketime
        ELSE o.dnverifytime
      END,
      121
    ) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinReturnMaterialSync
ORDER BY
  dModifyTime DESC

工单退料单明细(FgstockinReturnMaterialDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
WITH FgstockinReturnMaterialDetailSync AS(
  SELECT
    CAST(o.cCode AS nvarchar) + '|' + CAST(od.AutoID AS nvarchar) FORM_RECID,
    o.cCode FORM_NO,
    CAST(od.AutoID AS nvarchar) ITEMNO,
    '工单退料单' FORM_TYPE,
    '' WORK_ORDER,
    od.cInvCode PARTID,
    'N/A' VERSION,
    o.cWhCode WAREHOUSEID,
    od.cMoLotCode ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(ABS(od.iQuantity) AS DECIMAL(10, 4)) RETURN_QTY,
    0 ACTUAL_QTY,
    od.cbMemo REMARK,
    '' ITEMNO_FLAG,
    0 STOCKIN_QTY,
    'N/A' RTNPACKAGE_ID,
    '' QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    NULL WAIVE_NO,
    NULL WRH_ORG,
    NULL SITEID,
    NULL DID,
    (
      CASE
        WHEN o.cHandler IS NULL THEN o.cMaker
        ELSE o.cHandler
      END
    ) MODIFY_USERID,
    CONVERT(
      nvarchar,
      CASE
        WHEN o.dnverifytime IS NULL THEN o.dnmaketime
        ELSE o.dnverifytime
      END,
      121
    ) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    rdrecords11 od
    INNER JOIN rdrecord11 o ON od.ID = o.ID
  WHERE
    od.iQuantity < 0
    AND CONVERT(
      nvarchar,
      CASE
        WHEN o.dnverifytime IS NULL THEN o.dnmaketime
        ELSE o.dnverifytime
      END,
      121
    ) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinReturnMaterialDetailSync
ORDER BY
  dModifyTime DESC

其他入库单(FgstockinOther)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH FgstockinOtherSync AS(
  SELECT
    o.cCode FORM_NO,
    'Initial' FORM_STATUS,
    o.cMemo REMARK,
    '其他入库单' FORM_TYPE,
    '' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    NULL PROCESS_STARTTIME,
    NULL PROCESS_ENDTIME,
    'I01' WRH_FORMCODE,
    NULL CREATE_TYPE,
    NULL SOURCE_NO,
    NULL SITEID,
    o.cMaker REQUEST_USERID,
    o.dnmaketime CREATE_DATE,
    o.cHandler MODIFY_USERID,
    CONVERT(nvarchar, o.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    RdRecord08 o
  WHERE
    o.dnverifytime IS NOT NULL
    AND o.cHandler IS NOT NULL
    AND CONVERT(nvarchar, o.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinOtherSync
ORDER BY
  dModifyTime DESC

其他入库单明细(FgstockinOtherDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH FgstockinOtherDetailSync AS(
  SELECT
    o.cCode + '|' + CAST(od.AutoID AS nvarchar) FORM_RECID,
    o.cCode FORM_NO,
    CAST(od.AutoID AS nvarchar) ITEMNO,
    '其他入库单' FORM_TYPE,
    '' WORK_ORDER,
    od.cInvCode PARTID,
    'N/A' VERSION,
    o.cWhCode WAREHOUSEID,
    '' ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(od.iQuantity AS DECIMAL(10, 4)) RETURN_QTY,
    0 ACTUAL_QTY,
    od.cbMemo REMARK,
    '' ITEMNO_FLAG,
    0 STOCKIN_QTY,
    'N/A' RTNPACKAGE_ID,
    NULL QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    NULL WAIVE_NO,
    NULL WRH_ORG,
    NULL SITEID,
    NULL DID,
    o.cMaker REQUEST_USERID,
    o.dnmaketime CREATE_DATE,
    o.cHandler MODIFY_USERID,
    CONVERT(nvarchar, o.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    rdrecords08 od
    INNER JOIN RdRecord08 o ON od.ID = o.ID
  WHERE
    o.dnverifytime IS NOT NULL
    AND o.cHandler IS NOT NULL
    AND CONVERT(nvarchar, o.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  FgstockinOtherDetailSync
ORDER BY
  dModifyTime DESC

2.2.出库作业

2.2.1 材料出库

生产订单(MaterialstockoutManufacture)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
WITH MaterialstockoutManufactureSync AS(
  SELECT
    DISTINCT o.MoCode FORM_NO,
    od.RelsUser REQUEST_USERID,
    CONVERT(nvarchar, o.CreateDate, 120) CREATE_DATE,
    CONVERT(nvarchar, od.RelsDate, 120) REQUEST_DATE,
    'Initial' FORM_STATUS,
    od.remark REMARK,
    '生产订单' FORM_TYPE,
    'N/A' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    CAST(NULL AS datetime) PROCESS_STARTTIME,
    CAST(NULL AS datetime) PROCESS_ENDTIME,
    'N' PICKBYLOT_SPLIT_FLAG,
    'N' UNOPEN_FIRST_FLAG,
    'N' CHECK_BRAND,
    'ERP' CREATE_TYPE,
    'N' PICKBYAREA_FLAG,
    'N' PICKBYLOCATION,
    'N' MERGE_FLAG,
    'N' LIFO_FLAG,
    'N' RESERVE_FIRST_FLAG,
    'N' PRIORITYLOCATION,
    'N/A' SOURCE_NO,
    NULL SITEID,
    od.RelsUser MODIFY_USERID,
    CONVERT(nvarchar, od.RelsTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    mom_order o
    INNER JOIN mom_orderdetail od ON o.MOID = od.MOID
  WHERE
    od.RelsTime IS NOT NULL
    AND od.RelsUser IS NOT NULL
    AND CONVERT(nvarchar, od.RelsTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutManufactureSync
ORDER BY
  dModifyTime DESC

生产订单子件(MaterialstockoutManufactureDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
WITH MaterialstockoutManufactureDetailSync AS(
  SELECT
    CAST(o.MoCode AS nvarchar) + '|' + CAST(om.AllocateId AS nvarchar) FORM_RECID,
    CAST(o.MoCode AS nvarchar) FORM_NO,
    CAST(om.AllocateId AS nvarchar) ITEMNO,
    '生产订单' FORM_TYPE,
    '' WORK_ORDER,
    om.InvCode PARTID,
    'N/A' VERSION,
    om.WhCode WAREHOUSEID,
    om.LotNo ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(om.Qty AS DECIMAL(10, 4)) REQUEST_QTY,
    CAST(0 AS DECIMAL(10, 4)) ACTUAL_QTY,
    om.remark REMARK,
    '' ITEMNO_FLAG,
    (
      CASE
        WHEN om.QcFlag = 1 THEN 'Y'
        ELSE 'N'
      END
    ) QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    od.RelsUser MODIFY_USERID,
    CONVERT(nvarchar, od.RelsTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    mom_moallocate om
    INNER JOIN mom_orderdetail od ON om.MoDId = od.MoDId
    INNER JOIN mom_order o ON od.MoId = o.MoId
  WHERE
    om.WIPType = 3 --只有领料的物料才同步
    AND od.RelsTime IS NOT NULL
    AND od.RelsUser IS NOT NULL
    AND CONVERT(nvarchar, od.RelsTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutManufactureDetailSync
ORDER BY
  dModifyTime DESC

委外订单(MaterialstockoutOutsourcing)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH MaterialstockoutOutsourcingSync AS(
  SELECT
    DISTINCT o.cCode FORM_NO,
    o.cVerifier REQUEST_USERID,
    CONVERT(nvarchar, o.dDate, 120) CREATE_DATE,
    CONVERT(nvarchar, o.dVerifyDate, 120) REQUEST_DATE,
    'Initial' FORM_STATUS,
    o.cMemo REMARK,
    '委外订单' FORM_TYPE,
    'N/A' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    CAST(NULL AS datetime) PROCESS_STARTTIME,
    CAST(NULL AS datetime) PROCESS_ENDTIME,
    'N' PICKBYLOT_SPLIT_FLAG,
    'N' UNOPEN_FIRST_FLAG,
    'N' CHECK_BRAND,
    'ERP' CREATE_TYPE,
    'N' PICKBYAREA_FLAG,
    'N' PICKBYLOCATION,
    'N' MERGE_FLAG,
    'N' LIFO_FLAG,
    'N' RESERVE_FIRST_FLAG,
    'N' PRIORITYLOCATION,
    'N/A' SOURCE_NO,
    NULL SITEID,
    o.cVerifier MODIFY_USERID,
    CONVERT(nvarchar, o.dVerifyTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    OM_MOMain o
  WHERE
    o.dVerifyTime IS NOT NULL
    AND o.cVerifier IS NOT NULL
    AND CONVERT(nvarchar, o.dVerifyTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutOutsourcingSync
ORDER BY
  dModifyTime DESC

委外订单子件(MaterialstockoutOutsourcingDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
WITH MaterialstockoutOutsourcingDetailSync AS(
  SELECT
    CAST(o.cCode AS nvarchar) + '|' + RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(ods.irowno) > 0 THEN ods.irowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) FORM_RECID,
    CAST(o.cCode AS nvarchar) FORM_NO,
    RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(ods.irowno) > 0 THEN ods.irowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) ITEMNO,
    '委外订单' FORM_TYPE,
    '' WORK_ORDER,
    ods.cInvCode PARTID,
    'N/A' VERSION,
    ods.cWhCode WAREHOUSEID,
    ods.cBatch ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(ods.iQuantity AS DECIMAL(10, 4)) REQUEST_QTY,
    CAST(0 AS DECIMAL(10, 4)) ACTUAL_QTY,
    ods.cbMemo REMARK,
    '' ITEMNO_FLAG,
    'N' QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    o.cVerifier MODIFY_USERID,
    CONVERT(nvarchar, o.dVerifyTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    OM_MOMaterials ods
    INNER JOIN OM_MODetails od ON od.MODetailsID = ods.MoDetailsID
    INNER JOIN OM_MOMain o ON o.MOID = od.MOID
  WHERE
    o.dVerifyTime IS NOT NULL
    AND o.cVerifier IS NOT NULL
    AND CONVERT(nvarchar, o.dVerifyTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutOutsourcingDetailSync
ORDER BY
  dModifyTime DESC

领料申请单(MaterialstockoutMaterial)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH MaterialstockoutMaterialSync AS(
  SELECT
    DISTINCT o.cCode FORM_NO,
    o.cHandler REQUEST_USERID,
    CONVERT(nvarchar, o.dDate, 120) CREATE_DATE,
    CONVERT(nvarchar, o.dVeriDate, 120) REQUEST_DATE,
    'Initial' FORM_STATUS,
    o.cMemo REMARK,
    '领料申请单' FORM_TYPE,
    'N/A' FORM_ATTR,
    NULL MOVE_NO,
    NULL PROCESS_USERID,
    CAST(NULL AS datetime) PROCESS_STARTTIME,
    CAST(NULL AS datetime) PROCESS_ENDTIME,
    'N' PICKBYLOT_SPLIT_FLAG,
    'N' UNOPEN_FIRST_FLAG,
    'N' CHECK_BRAND,
    'ERP' CREATE_TYPE,
    'N' PICKBYAREA_FLAG,
    'N' PICKBYLOCATION,
    'N' MERGE_FLAG,
    'N' LIFO_FLAG,
    'N' RESERVE_FIRST_FLAG,
    'N' PRIORITYLOCATION,
    'N/A' SOURCE_NO,
    NULL SITEID,
    o.cHandler MODIFY_USERID,
    CONVERT(nvarchar, o.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    MaterialAppVouch o
  WHERE
    o.dnverifytime IS NOT NULL
    AND o.cHandler IS NOT NULL
    AND CONVERT(nvarchar, o.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutMaterialSync
ORDER BY
  dModifyTime DESC

领料申请单子件(MaterialstockoutMaterialDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
WITH MaterialstockoutMaterialDetailSync AS(
  SELECT
    CAST(o.cCode AS nvarchar) + '|' + RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(od.irowno) > 0 THEN od.irowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) FORM_RECID,
    CAST(o.cCode AS nvarchar) FORM_NO,
    RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(od.irowno) > 0 THEN od.irowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) ITEMNO,
    '领料申请单' FORM_TYPE,
    '' WORK_ORDER,
    od.cInvCode PARTID,
    'N/A' VERSION,
    od.cWhCode WAREHOUSEID,
    '' ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(od.iQuantity AS DECIMAL(10, 4)) REQUEST_QTY,
    CAST(0 AS DECIMAL(10, 4)) ACTUAL_QTY,
    od.cbMemo REMARK,
    'N' ITEMNO_FLAG,
    'N' QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    (
      CASE
        WHEN o.cModifyPerson IS NULL THEN o.cMaker
        ELSE o.cModifyPerson
      END
    ) MODIFY_USERID,
    CONVERT(nvarchar, o.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    MaterialAppVouchs od
    INNER JOIN MaterialAppVouch o ON o.id = od.ID
  WHERE
    --od.WIPType = 3 AND  --只有领料的物料才同步
    o.dnverifytime IS NOT NULL
    AND o.cHandler IS NOT NULL
    AND CONVERT(nvarchar, o.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutMaterialDetailSync
ORDER BY
  dModifyTime DESC

补料申请单(MaterialstockoutReplenishapply)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH MaterialstockoutReplenishapplySync AS(
  SELECT
    DISTINCT o.DocCode FORM_NO,
    o.RelsUser REQUEST_USERID,
    CONVERT(nvarchar, o.CreateDate, 120) CREATE_DATE,
    CONVERT(nvarchar, o.DocDate, 120) REQUEST_DATE,
    'Initial' FORM_STATUS,
    NULL REMARK,
    '补料申请单' FORM_TYPE,
    'N/A' FORM_ATTR,
    NULL MOVE_NO,
    o.RelsUser PROCESS_USERID,
    CAST(NULL AS datetime) PROCESS_STARTTIME,
    CAST(NULL AS datetime) PROCESS_ENDTIME,
    'N' PICKBYLOT_SPLIT_FLAG,
    'N' UNOPEN_FIRST_FLAG,
    'N' CHECK_BRAND,
    'ERP' CREATE_TYPE,
    'N' PICKBYAREA_FLAG,
    'N' PICKBYLOCATION,
    'N' MERGE_FLAG,
    'N' LIFO_FLAG,
    'N' RESERVE_FIRST_FLAG,
    'N' PRIORITYLOCATION,
    'N/A' SOURCE_NO,
    NULL SITEID,
    o.ModifyUser MODIFY_USERID,
    CONVERT(nvarchar, o.RelsTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    mom_replenishapply o
  WHERE
    o.RelsTime IS NOT NULL
    AND o.RelsUser IS NOT NULL
    AND CONVERT(nvarchar, o.RelsTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutReplenishapplySync
ORDER BY
  dModifyTime DESC

补料申请单子件(MaterialstockoutReplenishapplyDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
WITH MaterialstockoutReplenishapplyDetailSync AS(
  SELECT
    CAST(o.DocCode AS nvarchar) + '|' + RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(od.SortSeq) > 0 THEN od.SortSeq
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) FORM_RECID,
    CAST(o.DocCode AS nvarchar) FORM_NO,
    RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(od.SortSeq) > 0 THEN od.SortSeq
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) ITEMNO,
    '补料申请单' FORM_TYPE,
    '' WORK_ORDER,
    ods.InvCode PARTID,
    'N/A' VERSION,
    ods.WhCode WAREHOUSEID,
    ods.LotNo ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(od.ApplyQty AS DECIMAL(10, 4)) REQUEST_QTY,
    CAST(0 AS DECIMAL(10, 4)) ACTUAL_QTY,
    ods.Remark REMARK,
    '' ITEMNO_FLAG,
    'N' QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    o.RelsUser MODIFY_USERID,
    CONVERT(nvarchar, o.RelsTime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    mom_moallocate ods
    INNER JOIN mom_replenishapplydtl od ON od.MoAllocateId = ods.AllocateId
    INNER JOIN mom_replenishapply o ON o.ApplyId = od.ApplyId
  WHERE
    ods.WIPType = 3
    AND --只有领料的物料才同步
    o.RelsTime IS NOT NULL
    AND o.RelsUser IS NOT NULL
    AND CONVERT(nvarchar, o.RelsTime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutReplenishapplyDetailSync
ORDER BY
  dModifyTime DESC

其他出库单(MaterialstockoutOther)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH MaterialstockoutOtherSync AS(
  SELECT
    DISTINCT o.cCode FORM_NO,
    o.cHandler REQUEST_USERID,
    CONVERT(nvarchar, o.dnmaketime, 120) CREATE_DATE,
    CONVERT(nvarchar, o.dDate, 120) REQUEST_DATE,
    'Initial' FORM_STATUS,
    NULL REMARK,
    '其他出库单' FORM_TYPE,
    'N/A' FORM_ATTR,
    NULL MOVE_NO,
    o.cHandler PROCESS_USERID,
    CAST(NULL AS datetime) PROCESS_STARTTIME,
    CAST(NULL AS datetime) PROCESS_ENDTIME,
    'N' PICKBYLOT_SPLIT_FLAG,
    'N' UNOPEN_FIRST_FLAG,
    'N' CHECK_BRAND,
    'ERP' CREATE_TYPE,
    'N' PICKBYAREA_FLAG,
    'N' PICKBYLOCATION,
    'N' MERGE_FLAG,
    'N' LIFO_FLAG,
    'N' RESERVE_FIRST_FLAG,
    'N' PRIORITYLOCATION,
    'N/A' SOURCE_NO,
    NULL SITEID,
    o.cHandler MODIFY_USERID,
    CONVERT(nvarchar, o.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    RdRecord09 o
  WHERE
    o.dnverifytime IS NOT NULL
    AND o.cHandler IS NOT NULL
    AND CONVERT(nvarchar, o.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutOtherSync
ORDER BY
  dModifyTime DESC

其他出库单子件(MaterialstockoutOtherDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
WITH MaterialstockoutOtherDetailSync AS(
  SELECT
    CAST(o.cCode AS nvarchar) + '|' + CAST(od.AutoID AS nvarchar) FORM_RECID,
    CAST(o.cCode AS nvarchar) FORM_NO,
    CAST(od.AutoID AS nvarchar) ITEMNO,
    '其他出库单' FORM_TYPE,
    '' WORK_ORDER,
    od.cInvCode PARTID,
    'N/A' VERSION,
    o.cWhCode WAREHOUSEID,
    NULL ERP_LOTNO,
    NULL ERP_PROCESS,
    CAST(od.iQuantity AS DECIMAL(10, 4)) REQUEST_QTY,
    CAST(od.iFQuantity AS DECIMAL(10, 4)) ACTUAL_QTY,
    od.cbMemo REMARK,
    '' ITEMNO_FLAG,
    'N' QC_FLAG,
    NULL QC_RESULT,
    NULL IQC_LOTNO,
    o.cHandler MODIFY_USERID,
    CONVERT(nvarchar, o.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    Rdrecords09 od
    INNER JOIN RdRecord09 o ON od.ID = o.ID
  WHERE
    --od.WIPType = 3 AND  --只有领料的物料才同步
    o.dnverifytime IS NOT NULL
    AND o.cHandler IS NOT NULL
    AND CONVERT(nvarchar, o.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  MaterialstockoutOtherDetailSync
ORDER BY
  dModifyTime DESC

2.2.2 发货备料

销售发货退货单(Shipping)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WITH ShippingSync AS(
  SELECT
    DISTINCT CAST(d.cDLCode AS nvarchar) SHIPPING_NO,
    d.cCusCode CUSTOMERID,
    d.cPersonCode SALER_USERID,
    CONVERT(nvarchar, d.dDate, 111) CREATE_DATE,
    d.dverifydate SCHEDULE_DATE,
    'Initial' SO_STATUS,
    d.cMemo REMARK,
    d.cBusType SO_TYPE,
    d.cmodifier MODIFY_USERID,
    NULL CREATE_TYPE,
    NULL TRANSPORT_TYPE,
    'N' PICKBYLOT_SPLIT_FLAG,
    'N' CHECK_BRAND,
    'N' RESERVE_FIRST_FLAG,
    'N' PICKBYAREA_FLAG,
    'N' PICKBYLOCATION,
    'N' MERGE_FLAG,
    'N' LIFO_FLAG,
    'N' PRIORITYLOCATION,
    'N' UNOPEN_FIRST_FLAG,
    'N' SOURCE_NO,
    NULL SITEID,
    CONVERT(nvarchar, d.dverifysystime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    DispatchList d
  WHERE
    d.dverifysystime IS NOT NULL
    AND CONVERT(nvarchar, dverifysystime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  ShippingSync
ORDER BY
  dModifyTime desc

销售发货退货单明细(ShippingDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
WITH ShippingDetailSync AS(
  SELECT
    DISTINCT CAST(d.cDLCode AS nvarchar) + '|' + RIGHT('000' + CAST(ds.irowno AS nvarchar), 3) SHIPPING_RECID,
    CAST(d.cDLCode AS nvarchar) SHIPPING_NO,
    RIGHT('000' + CAST(ds.irowno AS nvarchar), 3) ITEMNO,
    ds.cInvCode PARTID,
    ds.cBatch VERSION,
    cBatch ERP_LOTNO,
    ds.cWhCode WAREHOUSEID,
    CAST(ds.iQuantity AS DECIMAL(10, 4)) SOQTY,
    CAST(fOutQuantity AS DECIMAL(10, 4)) SHIP_QTY,
    iMassDate WARRANTY_MONTH,
    NULL CUSTOMER_SITE,
    d.cShipAddress SHIP_ADDRESS,
    NULL PO_NO,
    NULL PO_ITEMNO,
    ds.cMemo REMARK,
    d.cmodifier MODIFY_USERID,
    NULL SHIP_ADDRESS2,
    NULL CREATE_TYPE,
    NULL ITEMNO_FLAG,
    NULL SCHEDULE_DATE,
    NULL SITEID,
    NULL DID,
    CONVERT(nvarchar, d.dverifysystime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    DispatchLists ds
    INNER JOIN DispatchList d ON ds.DLID = d.DLID
  WHERE
    d.dverifysystime IS NOT NULL
    AND CONVERT(nvarchar, dverifysystime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  ShippingDetailSync
ORDER BY
  dModifyTime desc

2.3. 调拨作业

调拨申请单(Transfer)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
WITH TransferSync AS(
  SELECT
    DISTINCT v.cTVCode FORM_NO,
    '调拨申请单' FORM_TYPE,
    CONVERT(nvarchar, v.dnmaketime, 121) CREATE_DATE,
    'Initial' FORM_STATUS,
    'N/A' FORM_ATTR,
    v.cTVMemo REMARK,
    v.cOWhCode FROM_WRHID,
    v.cIWhCode TARGET_WRHID,
    v.cMaker REQUEST_USERID,
    v.cVerifyPerson PROCESS_USERID,
    v.cModifyPerson MODIFY_USERID,
    CONVERT(nvarchar, v.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    ST_AppTransVouch v
  WHERE
    v.dnverifytime IS NOT NULL
    AND v.cVerifyPerson IS NOT NULL
    AND CONVERT(nvarchar, v.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  TransferSync
ORDER BY
  dModifyTime DESC

调拨申请单明细(TransferDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
WITH TransferDetailSync AS(
  SELECT
    CAST(v.cTVCode AS nvarchar) + '|' + RIGHT('000' + CAST(vs.irowno AS nvarchar), 3) FORM_RECID,
    v.cTVCode FORM_NO,
    RIGHT('000' + CAST(vs.irowno AS nvarchar), 3) ITEMNO,
    '调拨申请单' FORM_TYPE,
    'Initial' FORM_STATUS,
    'N/A' FORM_ATTR,
    v.cTVMemo REMARK,
    vs.cInvCode PARTID,
    'N/A' VERSION,
    CAST(iTVQuantity AS DECIMAL(10, 4)) MOVE_QTY,
    v.cOWhCode FROM_WRHID,
    'N/A' FROM_LOCID,
    v.cIWhCode TARGET_WRHID,
    'N/A' TARGET_LOCID,
    CAST(iTVQuantity AS DECIMAL(10, 4)) FROM_QTY,
    CAST(iTvSumQuantity AS DECIMAL(10, 4)) TARGET_QTY,
    v.cMaker FROM_USERID,
    CONVERT(nvarchar, v.dnmaketime, 121) FROM_DATETIME,
    v.cMaker REQUEST_USERID,
    CONVERT(nvarchar, v.dnmaketime, 121) CREATE_DATE,
    v.cVerifyPerson PROCESS_USERID,
    v.cModifyPerson MODIFY_USERID,
    CONVERT(nvarchar, v.dnverifytime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    ST_AppTransVouchs vs
    INNER JOIN ST_AppTransVouch v ON vs.ID = v.ID
  WHERE
    v.cVerifyPerson IS NOT NULL
    AND v.dnverifytime IS NOT NULL
    AND CONVERT(nvarchar, v.dnverifytime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  TransferDetailSync
ORDER BY
  dModifyTime DESC

2.4. 退货作业

2.4.1 仓库退货

采购（委外）退货单(ReturnVendor)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH ReturnVendorSync AS(
  SELECT
    ph.cCode FORM_NO,
    ph.cMaker REQUEST_USERID,
    CONVERT(nvarchar, ph.cMakeTime, 121) CREATE_DATE,
    'Initial' FORM_STATUS,
    ph.cMemo REMARK,
    (
      CASE
        WHEN ph.cBusType = '普通采购' THEN '采购退货单'
        ELSE '委外退货单'
      END
    ) FORM_TYPE,
    NULL FORM_ATTR,
    NULL [ PROCESS_USERID ],
    NULL [ PROCESS_STARTTIME ],
    NULL [ PROCESS_ENDTIME ],
    (
      CASE
        WHEN ph.cchanger IS NULL THEN ph.cMaker
        ELSE ph.cchanger
      END
    ) MODIFY_USERID,
    v.cVenCode VENDORID,
    CONVERT(nvarchar, ph.caudittime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    PU_ArrivalVouch ph
    INNER JOIN vendor v ON v.cVenCode = ph.cVenCode
  WHERE
    ph.cverifier IS NOT NULL
    AND ph.caudittime IS NOT NULL
    AND ph.cCloser IS NULL
    AND (
      ph.cBusType = '普通采购'
      OR ph.cBusType = '委外加工'
    )
    AND ph.bNegative = 1
    AND CONVERT(nvarchar, ph.caudittime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  ReturnVendorSync
ORDER BY
  dModifyTime desc

采购（委外）退货单明细(ReturnVendorDetail)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
WITH ReturnVendorDetailSync AS(
  SELECT
    CAST(ph.cCode AS nvarchar) + '|' + RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(phs.ivouchrowno) > 0 THEN phs.ivouchrowno
            ELSE 0
          END
        ) AS nvarchar
      ),
      3
    ) FORM_RECID,
    ph.cCode FORM_NO,
    RIGHT(
      '000' + CAST(
        (
          CASE
            WHEN len(phs.ivouchrowno) > 0 THEN phs.ivouchrowno
            ELSE 0
          END
        ) AS VARCHAR
      ),
      3
    ) ITEMNO,
    phs.cInvCode PARTID,
    'N/A' VERSION,
    CAST(phs.iQuantity AS DECIMAL(10, 4)) RETURN_QTY,
    0 ACTUAL_QTY,
    'Initial' PICK_STATUS,
    phs.cWhCode WAREHOUSEID,
    'N/A' ITEMNO_FLAG,
    (
      CASE
        WHEN ph.cchanger IS NULL THEN ph.cMaker
        ELSE ph.cchanger
      END
    ) MODIFY_USERID,
    CONVERT(nvarchar, ph.caudittime, 121) dModifyTime,
    CONVERT(bit, 0) IsSync
  FROM
    PU_ArrivalVouchs phs
    INNER JOIN PU_ArrivalVouch ph ON ph.id = phs.id
    AND phs.cCloser IS NULL
  WHERE
    ph.cverifier IS NOT NULL
    AND ph.caudittime IS NOT NULL
    AND ph.cCloser IS NULL
    AND (
      ph.cBusType = '普通采购'
      OR ph.cBusType = '委外加工'
    )
    AND ph.bNegative = 1
    AND phs.bGsp = 0
    AND CONVERT(nvarchar, ph.caudittime, 121) > CONVERT(nvarchar, '${LastDatetime_}', 121)
)
SELECT
  *
FROM
  ReturnVendorDetailSync
ORDER BY
  dModifyTime desc