WITH disp AS (
    SELECT
    CASE

        WHEN
            ( COUNT ( * ) OVER ( PARTITION BY disp_249."cDLCode" ) != COUNT ( * ) OVER ( PARTITION BY disp_253."cDLCode" ) ) THEN
                '已补单但条目数不一致' ELSE NULL
            END "条目数状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND Inventory_249."cInvCCode" NOT LIKE'0304%'
    AND ( disp_249."cBatch" != COALESCE ( disp_253."cBatch", '' ) ) THEN
    '已补单但批次不一致' ELSE NULL
    END "批次状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND ( disp_249."iSum" :: FLOAT != COALESCE ( disp_253."iSum" :: FLOAT, 0 ) ) THEN
    '已补单但原币含税合计不一致' ELSE NULL
    END "原币含税合计状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND ( disp_249."iTaxUnitPrice" :: FLOAT != COALESCE ( disp_253."iTaxUnitPrice" :: FLOAT, 0 ) ) THEN
    '已补单但原币含税单价不一致' ELSE NULL
    END "原币含税单价状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND ( disp_249."cCusCode" ) != COALESCE ( disp_253."cCusCode", '' ) THEN
    '已补单但客户名称不一致' ELSE NULL
    END "客户名称状态",
CASE

    WHEN disp_253."AutoID" IS NULL THEN
    '缺发货单' ELSE NULL
    END "发货单状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND ( disp_249."iQuantity" != COALESCE ( disp_253."iQuantity", '' ) ) THEN
    '已补单但数量不一致' ELSE NULL
    END "数量状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND disp_249."dverifydate" != '2021-11-07'
    AND ( disp_249."dverifydate" != COALESCE ( disp_253."dverifydate", '' ) ) THEN
    '已补单但审核日期不一致' ELSE NULL
    END "审核日期状态",
CASE

    WHEN disp_253."AutoID" IS NOT NULL
    AND disp_253."dDate" != '2021-11-07'
    AND ( disp_249."dDate" != COALESCE ( disp_253."dDate", '' ) ) THEN
    '已补单但单据日期不一致' ELSE NULL
    END "单据日期状态",
    disp_249."dDate" "006单据日期",
    disp_253."dDate" "009单据日期",
    disp_249.dverifydate "006dverifydate",
    disp_253.dverifydate "009dverifydate",
    disp_249."AutoID" "006发货单标识",
    so_249."iSOsID" "006销售单标识",
    disp_253."AutoID" "009发货单标识",
    so_253."iSOsID" "009销售单标识",
    disp_249."cBatch" "006发货单批次",
    disp_253."cBatch" "009发货单批次",
    disp_249."iQuantity" "006发货数量",
    disp_253."iQuantity" "009发货数量",
    disp_253."dverifydate" "009审核日期",
    disp_249."cCusName" "006客户名称",
    disp_253."cCusName" "009客户名称",
    disp_249."iSum" "006原币含税合计",
    disp_253."iSum" "009原币含税合计",
    disp_249."iTaxUnitPrice" "006原币含税单价",
    disp_253."iTaxUnitPrice" "009原币含税单价",
    sales."cUser_Name" cUser_Name,
    Inventory_249."cInvCCode",
    COUNT ( * ) OVER ( PARTITION BY disp_249."cDLCode" ) "006发货条数",
    COUNT ( * ) OVER ( PARTITION BY disp_253."cDLCode" ) "009发货条数",
    disp_249.*
FROM
    fdw_u8db.disp_249 disp_249
    LEFT JOIN fdw_u8db.so_249 so_249 ON so_249."iSOsID" = disp_249."iSOsID"
    LEFT JOIN fdw_u8db."Inventory_249" Inventory_249 ON so_249."cInvCode" = Inventory_249."cInvCode"
    LEFT JOIN fdw_u8db."UA_User" sales ON sales."cUser_Id" = so_249."cMainPersonCode"
    LEFT JOIN fdw_u8db.so_253 ON (
        so_249."cSOCode" = so_253."cSOCode"
        AND so_249."cInvCode" = so_253."cInvCode"
        AND COALESCE ( so_249."cFree1", '' ) = COALESCE ( so_253."cFree1", '' )
        AND so_249."iQuantity" :: FLOAT = so_253."iQuantity" :: FLOAT
    )
    LEFT JOIN fdw_u8db.disp_253 ON ( so_253."iSOsID" = disp_253."iSOsID" AND disp_249."cDLCode" = disp_253."cDLCode" )
ORDER BY
    disp_249."cDLCode"
    )
SELECT
    disp."cInvCode",
    COALESCE (
        MAX ( CASE WHEN pos_sum."cInvCode" IS NULL THEN '缺生产' ELSE NULL END ) OVER ( PARTITION BY disp."cDLCode" ),
        MAX ( CASE WHEN disp."009审核日期" IS NULL AND pos_sum."iquantity" :: FLOAT < disp."006发货数量" :: FLOAT THEN '库存数量不足,不能发货' ELSE NULL END ) OVER ( PARTITION BY disp."cDLCode" ),
        MAX ( disp."发货单状态" ) OVER ( PARTITION BY disp."cDLCode" ),
        disp."条目数状态",
        MAX ( disp."原币含税合计状态" ) OVER ( PARTITION BY disp."cDLCode" ),
        MAX ( disp."原币含税单价状态" ) OVER ( PARTITION BY disp."cDLCode" ),
        MAX ( disp."单据日期状态" ) OVER ( PARTITION BY disp."cDLCode" ),
        disp."客户名称状态",
        MAX ( disp."批次状态" ) OVER ( PARTITION BY disp."cDLCode" ),
        MAX ( disp."数量状态" ) OVER ( PARTITION BY disp."cDLCode" ),
    CASE

            WHEN disp."009审核日期" IS NULL THEN
            '客服已做单, 数据无误 ,缺审核' ELSE NULL
        END,
        MAX ( disp."审核日期状态" ) OVER ( PARTITION BY disp."cDLCode" ),
        '正常'
    ) 状态,
    disp."006单据日期",
    disp."009单据日期",
    disp."单据日期状态",
    disp."006dverifydate",
    disp."009dverifydate",
    disp."审核日期状态",
    disp."006发货条数",
    disp."009发货条数",
    disp."条目数状态",
    disp."006原币含税合计",
    disp."009原币含税合计",
    disp."原币含税合计状态",
    disp."006原币含税单价",
    disp."009原币含税单价",
    disp."原币含税单价状态",
    disp."006发货数量",
    disp."009发货数量",
    disp."数量状态",
    disp."006发货单批次",
    disp."009发货单批次",
    disp."批次状态",
    disp."006客户名称",
    disp."009客户名称",
    disp."客户名称状态",
CASE

        WHEN pos_sum."cInvCode" IS NULL THEN
        '缺生产' ELSE NULL
    END "生产状态",
CASE

    WHEN pos_sum."iquantity" :: FLOAT < disp."009发货数量" :: FLOAT THEN
    '库存数量不足,不能发货' ELSE NULL
    END "发货状态",
CASE

    WHEN disp."009审核日期" IS NULL THEN
    '缺审核' ELSE NULL
    END "审核状态",
    pos_sum."iquantity" :: FLOAT 现有库存量,
    disp."DLID" ID,
    disp."cDLCode" 发货退货单号,
    disp."cInvCode" "货号",
    disp."cCusName" 客户名称,
    disp."iQuantity" 数量,
    disp."iTaxUnitPrice" 原币含税单价,
    disp."iSum"原币价税合计,
    disp."cBatch" 批号,
    disp."cuser_name" SO业务人员,
    disp."cMaker" 制单人,
    disp."iBatch" 批次,
    disp."cPosition" 货位,
    disp."cVouchType" 单据类型编码,
    disp."cBusType" 业务类型,
    disp."dDate" 单据日期 ,
    disp."cRdCode" 收发类别编码 ,
    disp."cDepCode" 部门编码,
    disp."cPersonCode" 业务员编码,
    disp."cSoCode" 销售订单号,
    disp."cSBVCode" 销售发票号,
    disp."cCusCode" 客户编码 ,
    disp."ccusperson" 客户联系人,
    disp."cShipAddress" 发往地址,
    disp."cexch_name" 币种名称,
    disp."disp_m_iTaxRate" 表头税率,
    disp."disp_m_cMemo" 表头备注,
    disp."dcreatesystime" 制单时间,
    disp."cVerifier" 审核人,
    disp."dverifydate" 审核日期,
    disp."cmodifier" 修改人,
    disp."dmoddate" 修改日期,
    disp."cAccounter" 记账人 ,
    disp."cinvoicecompany" 开票单位编码,
    disp."AutoID" 发货退货单子表标识,
    disp."cWhCode" 仓库编码 ,
    disp."cInvCode" 存货编码,
    disp."cInvName" 存货名称 ,
    disp."disp_d_iTaxRate" 表体税率,
    disp."cFree1" 规格,
    disp."disp_d_cMemo" 表体备注,
    disp."iSOsID" 销售订单子表标识 ,
    disp."iDLsID" "发货退货单子表标识2",
    disp."cSoCode" 表体销售订单号 ,
    disp."cordercode" 订单号,
    disp."cDefine29" 跨表备注,
    disp."cSCloser" 关闭人,
    disp."cbaccounter" 表体记账人,
    disp."cdemandid" 需求跟踪 ID
FROM
    disp
    LEFT JOIN (
    SELECT
        InvPositionSum_253."cInvCode",
    CASE

            WHEN Inventory_249."cInvCCode" LIKE'0304%' THEN
            '' ELSE InvPositionSum_253."cBatch"
        END "cBatch",
    SUM ( InvPositionSum_253."iQuantity" :: FLOAT ) iQuantity
FROM
    fdw_u8db."InvPositionSum_253" InvPositionSum_253
    LEFT JOIN fdw_u8db."Inventory_249" Inventory_249 ON InvPositionSum_253."cInvCode" = Inventory_249."cInvCode"
GROUP BY
    ( InvPositionSum_253."cInvCode" ),
CASE

        WHEN Inventory_249."cInvCCode" LIKE'0304%' THEN
        '' ELSE InvPositionSum_253."cBatch"
    END
    ) pos_sum ON disp."cInvCode" = pos_sum."cInvCode"
AND
CASE

        WHEN disp."cInvCCode" LIKE'0304%' THEN
        '' ELSE disp."cBatch"
    END = pos_sum."cBatch"
ORDER BY
    disp."cDLCode"