T+19.0 采购订单选请购单搜索单据的后台执行sql

smilelight

SELECT TOP 10000 [Inventory].code AS Inventory_Code, [Inventory].name AS Inventory_Name, [Inventory].priuserdefnvc4 AS Inventory_priuserdefnvc4, [Unit].name AS Unit_Name, [PurchaseRequisitionDetailDTO].quantity AS Quantity
        , [PurchaseRequisitionDetailDTO].cumExecuteQuantity AS cumExecuteQuantity
        , CASE
                WHEN [PurchaseRequisitionDetailDTO].quantity < isnull([PurchaseRequisitionDetailDTO].cumExecuteQuantity, 0) THEN 0
                ELSE [PurchaseRequisitionDetailDTO].quantity - isnull([PurchaseRequisitionDetailDTO].cumExecuteQuantity, 0)
        END AS CurrentArrivalQuantity, [PurchaseRequisitionDetailDTO].requireDate AS RequireDate, [PurchaseRequisitionDetailDTO].id AS ID, [PurchaseRequisitionDetailDTO].idPurchaseRequisitionDTO AS IdpurchaseRequisitionDTO, [PurchaseRequisitionDetailDTO].code AS code
        , [PurchaseRequisition].code AS PurchaseRequisition_code, [PurchaseRequisition].id AS PurchaseRequisition_id, [Unit].id AS Unit_id, [Inventory].id AS Inventory_id
FROM [Pu_PurchaseRequisition_b] [PurchaseRequisitionDetailDTO]
        LEFT JOIN [Pu_PurchaseRequisition] [PurchaseRequisition] ON [PurchaseRequisitionDetailDTO].idPurchaseRequisitionDTO = [PurchaseRequisition].id
        LEFT JOIN [V_AA_Partner] [PurchaseRequisition.SuggPartner] ON [PurchaseRequisition].idsuggpartner = [PurchaseRequisition.SuggPartner].id
        LEFT JOIN [AA_Unit] [Unit] ON [PurchaseRequisitionDetailDTO].idunit = [Unit].id
        LEFT JOIN [V_AA_Inventory] [Inventory] ON [PurchaseRequisitionDetailDTO].idinventory = [Inventory].id
WHERE 1 = 1
        AND (   --是否劳务费用                      --请购单明细DTO数量不为空且等于0
                ([Inventory].IsLaborCost = 1 AND isnull(PurchaseRequisitionDetailDTO.quantity, 0) = 0)
                OR    --是否单计量                 --数量绝对值是否大于等于累计执行数量
                ([Unit].IsSingleUnit = 1 AND ABS(PurchaseRequisitionDetailDTO.Quantity) > ABS(isnull(PurchaseRequisitionDetailDTO.cumExecuteQuantity, 0)))
                OR
                ([Unit].IsSingleUnit = 0 --不为单计量
                        AND [Unit].ChangeType1 = N'24'  --变动类型 是否为24
                        AND (ABS(PurchaseRequisitionDetailDTO.Quantity) > ABS(isnull(PurchaseRequisitionDetailDTO.cumExecuteQuantity, 0))
                                AND ABS(PurchaseRequisitionDetailDTO.Quantity2) > ABS(isnull(PurchaseRequisitionDetailDTO.cumExecuteQuantity2, 0)))) --数量1、2绝对值是否大于等于累计执行数量1、2
                OR ([Unit].IsSingleUnit = 0 --不为单计量
                        AND [Unit].ChangeType1 = N'152' --变动类型 是否为152
                        AND (ABS(PurchaseRequisitionDetailDTO.Quantity) > ABS(isnull(PurchaseRequisitionDetailDTO.cumExecuteQuantity, 0))  
                                OR ABS(PurchaseRequisitionDetailDTO.Quantity2) > ABS(isnull(PurchaseRequisitionDetailDTO.cumExecuteQuantity2, 0)))) --数量1、2绝对值是否大于等于累计执行数量1、2
                )
        AND PurchaseRequisitionDetailDTO.IsPraRequire = 0
        AND PurchaseRequisitionDetailDTO.IsClose = 0
        AND [PurchaseRequisition].VoucherState = N'189'
        AND ([PurchaseRequisition.SuggPartner].Disabled = 0
                OR [PurchaseRequisition].idSuggPartner IS NULL)
        AND [Inventory].Disabled = 0
        AND (
                SELECT COUNT(dbo.XT_QueryPrice_b.id)
                FROM dbo.XT_QueryPrice_b
                WHERE dbo.XT_QueryPrice_b.sourceVoucherDetailId = PurchaseRequisitionDetailDTO.id
                        AND dbo.XT_QueryPrice_b.idsourcevouchertype = N'101'
        ) = 0
        AND 1 = 1
        AND ([Inventory].IsLaborCost = N'0'
                OR [Inventory].IsLaborCost IS NULL)
        AND ([PurchaseRequisition].Code >= N'PQ-2024-12-0352'
                AND [PurchaseRequisition].Code <= N'PQ-2024-12-0352')
        AND [PurchaseRequisition].IdmarketingOrgan = N'1'
ORDER BY [PurchaseRequisition].Code, PurchaseRequisitionDetailDTO.Code

luckgem

smilelight 发表于 2024-12-18 13:57
是的，反正为了排查客户为什么会查不出来，就跟踪了一下，发现是存货建档案的时候，计量单位出错了，换算 ...

掌握了后台跟踪技巧和数据字典，大多数问题都会迎刃而解

luckgem

这篇幅不小啊，这是要给大家演示后台逻辑吗？

smilelight

luckgem 发表于 2024-12-17 13:43
这篇幅不小啊，这是要给大家演示后台逻辑吗？

是的，反正为了排查客户为什么会查不出来，就跟踪了一下，发现是存货建档案的时候，计量单位出错了，换算率是空的，但是又是多计量，辅数量为空，导致选单时候，明细行不满足任何一个条件。顺便发出来方便大家搜索