Query with the date that a sales order was cancelled

[Former Member]

My Daily Bookings report is picking up the daily sales and daily cancelled sales orders.  We have an issue when a sales order is created in month one, but cancelled in month 2.  The bookings report in month one should show the sale, but in month 2 show the cancellation (with negative numbers)

I am having difficulty querying the date that a sales order was cancelled using ADOC and ADO1 etc tables.  I am trying to select:

Select

Sale Order number,

Status,

Posting Date,

Date cancelled

From

  ORDR

  AD01

Where

Posting Date between '[%1]' and '[%2]'

OR

Date cancelled between '[%1]' and '[%2]'

If the cancellation date is with the same period as the posting date, then displaying only the cancellation date is fine (or both if need be)

thanks,

Tony

[Lakshmipathi]

The bookings report in month one should show the sale,

Ideally, once billing happens only, we consider it as a sale.

G. Lakshmipathi

[former_member212181]

Hi Tony,

I think there are some practical difficulties in getting this report.

Because SAP B1 doesn't not maintain Cancellation date (date in which user manually cancelled SO ) in ORDR Table.

This Cancellation information is getting updated in ADOC table, but not immediately on cancellation event. Its updating in ADOC table in the next any update happening in respective Sales order such as remarks update, Customer reference No update.

You can achieve this in two steps

Step 1 :

Create a CancelDate UDF in Header level table, and update this field with manual Cancellation date through Transaction Notification.

create Query based on Sales Order Document date and Sales Order Cancellation date(UDF)

Query may look like this

Replace T0.CancelDate with new UDF field

Declare @FDate DateTime, @TDate DateTime

Select @FDate = min(Z1.RefDate) from OJDT Z1 where Z1.RefDate >='[%1]'

Select @TDate = max(Z2.RefDate) from OJDT Z2 where Z2.RefDate <='[%2]'

Set @FDate = '[%1]'

Set @TDate = '[%2]'

Select T0.DocEntry, T0.DocNum, T0.DocStatus, T0.DocDate, T0.CancelDate

From ORDR T0

Where (T0.DocDate >= @FDate or T0.CancelDate >= @FDate)

    and (T0.DocDate <= @TDate or T0.CancelDate <= @TDate)

Thanks

Unnikrishnan

[Former Member]

Hi,

SELECT T0.[DocNum] as 'Sales Order No', T0.[DocStatus], T0.[DocDate], T0.CancelDate FROM ORDR T0 WHERE T0.[CANCELED] = 'Y'

Regards

SP Samy

[frank_wang6]

HI, in ORDR table, there is a field called CancelDate.

You should use that.

Frank