Date return tracking

A line contains a supplier, a depositor, a product, an effective date and the FIFO date of the last receipt.
The effective date sets the date from which this line is taken into consideration.

Operation of the effective date in the date return algorithm:
The purpose of this date is to be able to minimize the issue raised by several deliveries from a given supplier arriving in different trucks and on different dates (therefore on different receipts).
Let us consider the following case for a product and a given supplier: a supplier sends two trucks with products having a sell-by date set to 10/10/20XX in the first truck and products having a sell-by date set to 12/10/20XX in the second truck. The arrival of truck 1 is expected before truck 2.
Without effective dates being managed, if the stock of truck 2 arrives before the stock of truck 1 (because of transport difficulties), it may prove difficult to receive the stock of truck 1 because it will not comply with the date return. Its stock will be dated 10/10/xx.., which is earlier than the sell-by date of the last receipt dated 12/10/xx

To avoid these problems, the principle is to associate an effective date with the date of last receipt. This effective date is calculated by adding the number of days defined by setup to the day's date.

Let us consider truck 1 delivering stock with a sell-by date = 10/11/20xx
Let us consider truck 2 delivering stock with a sell-by date = 12/11/20xx
Let us consider truck 3 delivering stock with a sell-by date = 10/11/20xx
Let us consider truck 4 delivering stock with a sell-by date = 14/11/20xx
The current date is 01/11/xx (d) and no receipt occurred for the product for this supplier.

Example 1: Setup table of the date return defined with effective date = 0 (effective date = d date)

• Truck 1 is received on 01/11 (d)
• This does not generate any issue because there were no records in the date return tracking table.
• Record 1 is created with a date of last receipt on 10/11 and an effective date on 01/11 (d).
• Truck 2 is received on 01/11 (d)
• This does not generate any issue because the sell-by date of 12/11 > date of last receipt 10/11 (becoming effective on the d day).
• Record 2 is created with a date of last receipt on 12/11 and an effective date on 01/11 (d).
• Truck 3 is received on 02/11 (d)
• The line is on dispute because the sell-by date of 10/11 < date of last receipt 12/11 (being effective since 01/11).
• Truck 4 is received on 02/11.
• This does not generate any issue because the sell-by date of 14/10 > date of last receipt 12/11 (being effective since 01/11).
• Record 3 is created with a date of last receipt on 14/11 and an effective date on 02/11.

Example 2: Setup table of the date return defined with effective date = 0(effective date = d date)

• Truck 2 is received on 01/11 (d)
• This does not generate any issue because there were no records in the date return tracking table.
• Record 1 is created with a date of last receipt on 12/11 and an effective date on 01/11 (d).
• Truck 1 is received on 01/11 (d)
• The line is on dispute because the sell-by date of 10/11 < date of last receipt 12/11 (being effective since 01/11).
• Truck 3 is received on 02/11.
• The line is on dispute because the sell-by date of 10/11 < date of last receipt 12/11 (being effective since 01/11).
• Truck 4 is received on 02/11.
• This does not generate any issue because the sell-by date of 14/10 > date of last receipt 12/11 (being effective since 01/11).
• Record 2 is created with a date of last receipt on 14/11 and an effective date on 02/11.

Example 3: Setup table of the date return defined with effective date = 1 (effective date = d date+1)

• Truck 2 is received on 01/11 (d)
• This does not generate any issue because there were no records in the date return tracking table.
• A record is created with a date of last receipt on 12/11 and an effective date on 02/11 (d +1).
• Truck 1 is received on 01/11 (d)
• This does not generate any issue because there were no records in the date return tracking table. (record 1 only applies starting from 02/11).
• Truck 3 is received on 02/11.
• The line is on dispute because the sell-by date of 10/11 < date of last receipt 12/11 (being effective since 02/11).
• Truck 4 is received on 02/11.
• This does not generate any issue because the sell-by date of 14/10 > date of last receipt 12/11 (being effective since 02/11).
• Record 2 is created with a date of last receipt on 14/11 and an effective date on 03/11.

As the examples above illustrate, it is possible to have several records for the same group of three components (supplier, depositor, product) with different effective dates. The record considered during the check is always the one effective on the date of the day of the receipt (effective date earlier than or equal to the day's date d) and with the most recent effective date.