NF12 Back-dating Costing
Before this improvement, the costing information (e.g. ‘Product Costs’ window > ‘Select Product’ tab > ‘Cost Details’ tab) is recorded by system date. Back-date transaction will raise issues if there are secondary/indirect costing-relevant transactions after the back-date transaction. Costing data of the secondary/indirect costing-relevant transactions must be recalculated and updated manually. Some documents may need to be reposted manually.
This improvement records costing information by ‘Account Date’. It will re-post all secondary/indirect costing-relevant documents after a back-date document is posted. Indirectly recalculate and update the costing data of the secondary/indirect costing-relevant transactions after the back-date transaction.
JIRA Ticket: https://idempiere.atlassian.net/browse/IDEMPIERE-6203
Database Changes
- DateAcct column is added to M_CostDetail and M_CostHistory table to record the account date of the transaction.
- IsBackDate column is added to M_CostDetail and M_CostHistory table. IsBackDate=Y when it is a back-date transaction.
- BackDateProcessedOn column is added to M_CostDetail and M_CostHistory table to record the timestamp the back-date transaction was processed.
- Ref_CostDetail_ID column is added to M_CostDetail table to record the referenced cost detail record.
- DateAcct, IsBackDate and BackDateProcessedOn columns are added to M_CostMovement_v database view.
- Set DateAcct=TRUNC(Created) and IsBackDate=N for all existing M_CostDetail and M_CostHistory records.
- T_Fact_Acct_History table is created to backup the old posting record (i.e. Fact_Acct)
- BackDateDay column is added to C_AcctSchema table to configure the number of days allowed to post a back-date document based on system date.
Back-Date Control Check
The ‘Back-Date Days’ on ‘Accounting Schema’ window > ‘Accounting Schema’ tab is defaulted to 0. Configure the number of days allowed to post a back-date document based on system date. 
The back-date control check is enabled when the ‘Back-Date Days’ is not 0. The System will only process a back-date document if the document is costing-relevant and within the allowed ‘Back-Date Days’. The allowed ‘Back-Date Days’ is calculated based on the account date/movement date of the document and the system date. The System will throw a ‘Back-Date transaction not allowed’ error if the back-date document failed the back-date control check.
Example: Accounting Schema > Back-Date Days = 30 Current system date = 01/12/2025 Date Invoiced (Account Date) = 12/01/2024
After the back-date control check, the System will perform period closed check for all the secondary/indirect costing-relevant documents after the back-date document. The System will throw a 'Period Closed' error if the document failed the period closed check.
The System will perform stock coverage check for the back-date document:
- A reversal of receipt should not be possible if there is insufficient stock coverage, for example, due to shipments which have been made. A reversal of shipments is required to 'replenish' stock, prior to a reversal of receipt.
- A shipment should not be possible unless sufficient on-hand quantity as of or before the shipment's movement date.
Accounting Fact History (i.e. T_Fact_Acct_History)
When re-posting a document, the old posting records (i.e. Fact_Acct) for the document will be inserted into the T_Fact_Acct_History before deleting from the Fact_Acct.
A House Keeping record (i.e. Delete T_Fact_Acct_History records preserving last week) is created at the System level to clean up the T_Fact_Acct_History records older than 7 days.
Post a Document
The System will retrieve the costing information (i.e. Current Cost Price, Current Quantity, Accumulated Amt and Accumulated Qty) from ‘Product Costs’ window > ‘Select Product’ tab > ‘Product Costs’ tab > ‘Cost Movement’ tab. If no record is found, the System will retrieve the information from ‘Product Costs’ window > ‘Select Product’ tab > ‘Product Costs’ tab.
When creating a ‘Cost Detail’ (i.e. ‘Product Costs’ window > ‘Select Product’ tab > ‘Cost Details’ tab) for a transaction, the account date/movement date of the transaction is recorded in the ‘Cost Detail’ record > ‘Account Date’ field. The ‘Cost Detail’ record is set to ‘Back-Date’ (i.e. IsBackDate=Y) when the ‘Account Date’ is before the maximum ‘Account Date’ of the cost details by product or the current system date.
For reversal transaction, the cost detail of the original transaction is recorded in the 'Cost Detail' record > 'Referenced Cost Detail' field. The ‘Cost Detail’ record is set to ‘Back-Date’ (i.e. IsBackDate=Y) when there is a 'Referenced Cost Detail'.
Example 1: MR1: Account date = 01/12/2025 Current system date = 01/12/2025 Maximum account date = NULL Back-Date = No - not before 01/12/2025 SH: Account date = 01/15/2025 (Post-Date) Current system date = 01/12/2025 Maximum account date = 01/12/2025 Back-Date = No - not before 01/12/2025 MR2: Account date = 01/12/2025 Current system date = 01/12/2025 Maximum account date = 01/15/2025 Back-Date = Yes - before 01/15/2025
Example 2: MR1: Account date = 01/12/2025 Current system date = 01/12/2025 Maximum account date = NULL Back-Date = No - not before 01/12/2025 MR2 : Account date = 01/12/2025, Reference cost detail = MR1 (Reverse-Correct) Current system date = 01/12/2025 Maximum account date = 01/12/2025 Back-Date = Yes - there is a referenced cost detail
Back-date Process
When a document is successfully posted, the System will run the back-date process.
Step 1: Retrieve the ‘Cost Detail’ records of the posted document where IsBackDate=Y. Skip the process if there is no record found.
Step 2: Perform back-date control check based on the ‘Account Date’ of the ‘Cost Detail’ (from Step 1) and system date. Skip to process the back-date cost detail record if it is not within the allowed ‘Back-Date Days’.
Step 3: Retrieve all the ‘Cost Detail’ records after the back-date ‘Cost Detail’ records (from Step 1).
- The 'Cost Detail' records are ordered by:
- Account Date
- Referenced Cost Detail ID / Cost Detail ID
- If the 'Account Date' of the 'Referenced Cost Detail' = 'Account Date' of the 'Cost Detail'
- Reference Cost Detail ID
- Else
- Cost Detail ID
- If the 'Account Date' of the 'Referenced Cost Detail' = 'Account Date' of the 'Cost Detail'
- Cost Detail ID
Step 4: Retrieve the list of secondary/indirect documents to be reposted based on the ‘Cost Detail’ records (from Step 3).
Step 5: Re-post all the secondary/indirect documents (from Step 4) after the back-date document.
Step 6: Update the ‘Back-Date Processed On’ of the back-date ‘Cost Detail’ records (from Step 1 & 3) at the end of the process
Case Scenario
Current System Date = 01/12/2025
Product = TestProduct01
Step 1: Create Material Receipt:
- Account Date = 01/01/2025
- Quantity = 10
- Price = $5.00
| Organization | Account | Accounted Debit | Accounted Credit | Product | Business Partner | Project | Campaign | Account Date | Period | Posting Type |
| HQ | 14120_Product asset | 50.00 | 0.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/01/2025 | Jan-25 | Actual | ||
| HQ | 21190_Not invoiced receipts | 0.00 | 50.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/01/2025 | Jan-25 | Actual | ||
| 50.00 | 50.00 |
Step 2: Create Purchase Invoice:
- Account Date = 01/01/2025
- Quantity = 10
- Price = $5.00
| Organization | Account | Accounted Debit | Accounted Credit | Product | Business Partner | Project | Campaign | Account Date | Period | Posting Type |
| HQ | 51400_Inventory Clearing | 50.00 | 0.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/01/2025 | Jan-25 | Actual | ||
| HQ | 21100_Accounts Payable Trade | 0.00 | 50.00 | Seed Farm Inc. | 01/01/2025 | Jan-25 | Actual | |||
| 50.00 | 50.00 |
Step 3: Create Landed Cost (to Material Receipt created in Step 1):
- Account Date = 01/05/2025
- Quantity = 1
- Price = $10.00
| Organization | Account | Accounted Debit | Accounted Credit | Product | Business Partner | Project | Campaign | Account Date | Period | Posting Type |
| HQ | 14120_Product asset | 10.00 | 0.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/05/2025 | Jan-25 | Actual | ||
| HQ | 21100_Accounts Payable Trade | 0.00 | 10.00 | Seed Farm Inc. | 01/05/2025 | Jan-25 | Actual | |||
| 10.00 | 10.00 |
Step 4: Create Shipment (Customer):
- Account Date = 01/12/2025
- Quantity = 6
- Price = $6.00
- Product CoGs = $36.00
| Organization | Account | Accounted Debit | Accounted Credit | Product | Business Partner | Project | Campaign | Account Date | Period | Posting Type |
| HQ | 51100_Product CoGs | 36.00 | 0.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/12/2025 | Jan-25 | Actual | ||
| HQ | 14120_Product asset | 0.00 | 36.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/12/2025 | Jan-25 | Actual | ||
| 36.00 | 36.00 |
Cost Details (for Step 1 - 4):
- Current Cost Price = $6.00 (as of 01/12/2025)
- Current Quantity = 4
- Accumulated Amt = $60.00
- Accumulated Qty = 10
| Product | Price | Quantity | Amount | Account Date | Back-Date | Purchase Order Line | Invoice Line | Receipt Line | Sales Transaction | Referenced Cost Detail | Processed | Back-Date Processed On | Current Cost Price | Current Quantity | Accumulated Amt | Accumulated Qty |
| TestProduct01_TestProduct01 | 5.00 | 10 | 50.00 | 01/01/2025 | Yes | Purchase Order of Material Receipt (created in Step 1) | No | Yes | 01/12/2025 | 5.00 | 10 | 50.00 | 10 | |||
| TestProduct01_TestProduct01 | 5.00 | 10 | 50.00 | 01/01/2025 | Yes | Purchase Invoice (created in Step 2) | No | Yes | 01/12/2025 | 5.00 | 10 | 50.00 | 10 | |||
| TestProduct01_TestProduct01 | 1.00 | 10 | 10.00 | 01/05/2025 | Yes | Landed Cost (created in Step 3) | No | Yes | 01/12/2025 | 6.00 | 10 | 60.00 | 10 | |||
| TestProduct01_TestProduct01 | 6.00 | -6 | -36.00 | 01/12/2025 | No | Shipment | Yes | Yes | 6.00 | 4 | 60.00 | 10 |
Step 5: Reverse-correct Landed Cost (created in Step 3)
- Account Date = 01/05/2025
| Organization | Account | Accounted Debit | Accounted Credit | Product | Business Partner | Project | Campaign | Account Date | Period | Posting Type |
| HQ | 14120_Product asset | 0.00 | 10.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/05/2025 | Jan-25 | Actual | ||
| HQ | 21100_Accounts Payable Trade | 10.00 | 0.00 | Seed Farm Inc. | 01/05/2025 | Jan-25 | Actual | |||
| 10.00 | 10.00 |
Shipment (Customer) after reversal of Landed Cost (in Step 5)
- Product CoGs = $30.00
| Organization | Account | Accounted Debit | Accounted Credit | Product | Business Partner | Project | Campaign | Account Date | Period | Posting Type |
| HQ | 51100_Product CoGs | 30.00 | 0.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/12/2025 | Jan-25 | Actual | ||
| HQ | 14120_Product asset | 0.00 | 30.00 | TestProduct01_TestProduct01 | Seed Farm Inc. | 01/12/2025 | Jan-25 | Actual | ||
| 30.00 | 30.00 |
Cost Details (for Step 1 - 5) after reversal of Landed Cost (in Step 5):
- Current Cost Price = $5.00 (as of 01/12/2025)
- Current Quantity = 4
- Accumulated Amt = $50.00
- Accumulated Qty = 10
| Product | Price | Quantity | Amount | Account Date | Back-Date | Purchase Order Line | Invoice Line | Receipt Line | Sales Transaction | Referenced Cost Detail | Processed | Back-Date Processed On | Current Cost Price | Current Quantity | Accumulated Amt | Accumulated Qty |
| TestProduct01_TestProduct01 | 5.00 | 10 | 50.00 | 01/01/2025 | Yes | Purchase Order of Material Receipt (created in Step 1) | No | Yes | 01/12/2025 | 5.00 | 10 | 50.00 | 10 | |||
| TestProduct01_TestProduct01 | 5.00 | 10 | 50.00 | 01/01/2025 | Yes | Purchase Invoice (created in Step 2) | No | Yes | 01/12/2025 | 5.00 | 10 | 50.00 | 10 | |||
| TestProduct01_TestProduct01 | 1.00 | 10 | 10.00 | 01/05/2025 | Yes | Landed Cost (created in Step 3) | No | Yes | 01/12/2025 | 6.00 | 10 | 60.00 | 10 | |||
| TestProduct01_TestProduct01 | 5.00 | -6 | -30.00 | 01/12/2025 | No | Shipment (created in Step 4) | Yes | Yes | 5.00 | 4 | 50.00 | 10 | ||||
| TestProduct01_TestProduct01 | -1.00 | 10 | -10.00 | 01/05/2025 | Yes | Reversal Landed Cost (created in Step 5) | No | Cost Detail record for Landed Cost (created in Step 3) | Yes | 01/12/2025 | 5.00 | 10 | 50.00 | 10 |
