Query For Closed Deliveries

[Former Member]

Hello,

We are running SAP B1 version 9.0

One problem we have run into is that when our shipper has made an error on a delivery and needs to remake it, he has closed it instead of cancelling it. This causes obvious problems, for example closing the delivery doesn't put the parts back into the sales order, which closes the sales order out early. There is also the potential for shipping actual parts without invoicing for them (since you can't make an invoice from a closed delivery). What query would identify the closed deliveries that don't have an invoice related to them, and then what could we do to fix them?

Thank You,

David

[Former Member]

Hi David,

You can use below query in case of any Delivery closed Manually.

But I believe this "Close" option should not be authorized to any normal user as it leads to big issues going forward.

SELECT T0.DocEntry, T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocManClsd] FROM ODLN T0 Where  T0.[DocManClsd] ='Y'

You can add more fields to above query.

Thanks,

Rahul

[frank_wang6]

delivery only creates a inventory transaction, from Inventory to COGS.

so u can revert its accounting effect by using Inventory Receipt with same cost as the delivery, and against the COGS account. After that, directly create A/R invoice. (You can not link it with sales order and delivery any more.) But the accounting result is correct.

SELECT * FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE TargetType <> '13'

AND T0.CANCELED = 'N'

AND T0.DocStatus = 'C'

Frank