Odoo Missing Inbound Cases Automation – Technical Documentation

Objective

The goal of this module is to reconcile Odoo Daily Sales Reports (DSR) with Odoo Inventory Ledger to identify missing or short-received inventory for customer codes 1475 and 1584. This process will help track shipments against received inventory and highlight discrepancies.

Existing Reports & Data Sources

1. Odoo Daily Sales Report (DSR)

• Data Source: Exported from Odoo
• Relevant Fields:
  • Customer Code (1475, 1584)
  • Invoice Number
  • Quantity Shipped
  • Unique Code (Item Code + Invoice Number)

2. Odoo Inventory Ledger Detail View

• Navigation: Reports → Fulfillment → Inventory Tab → Inventory Ledger
• Relevant Fields:
  • Receipt Events
  • Quantity Received
  • Item Code
  • Invoice Number

3. Existing Reports in Odoo (FBA Fulfillment Reports)

• FBA Fulfillment Removal Order Detail
• FBA Fulfillment Removal Shipment Order Detail
• These reports contain outbound fulfillment data, which can help cross-check if items were removed but not received.

Automation Workflow

Step 1: Data Extraction & Import

1. Import Odoo DSR
   • Filter invoices for customer codes 1475 & 1584.
   • Extract Invoice Number, Unique Code, Quantity Shipped.
   • Store in a custom Odoo model (inventory.shipment).
2. Fetch Inventory Ledger Receipts from Odoo
   • Filter for receipt events.
   • Extract Item Code, Invoice Number, Quantity Received.
   • Store in a custom Odoo model (inventory.receipts).

Step 2: Matching Process (Reconciliation Logic)

1. Match shipments against receipts using Unique Code (Item Code + Invoice Number).
2. Compare Quantity Shipped vs. Quantity Received:
   • If Quantity Received < Quantity Shipped, mark it as short-received.
   • If Quantity Received = 0, mark it as not received.
3. Store the results in inventory.reconciliation with status:
   • “Matched” → If received fully
   • “Short Received” → If partial receipt
   • “Not Received” → If no receipt found

Step 3: Utilizing FBA Fulfillment Reports

• If an item is marked as Not Received, cross-check against the FBA Fulfillment Removal Reports:
  • If the removal order exists but the receipt is missing, trigger an Amazon Case Review Alert.
  • If no removal order exists, flag it for internal investigation.

Step 4: Automations & Alerts

1. Daily Scheduled Job
   • Auto-fetch Odoo DSR and Odoo Inventory Ledger.
   • Run the reconciliation logic.
   • Update Odoo with results.
2. Email Alerts & Reports
   • If discrepancies exist, send a report to the team.
   • Generate an exportable reconciliation report for Amazon case submission.

Deliverables for Developer

1. Custom Odoo Models:
   • inventory.shipment: Stores Odoo DSR shipments.
   • inventory.receipts: Stores Odoo Inventory Ledger receipts.
   • inventory.reconciliation: Stores matched/unmatched cases.
2. Automated Scripts:
   • Scheduled jobs for daily reconciliation.
   • Matching algorithm between shipments and receipts.
   • Alerts & notifications for missing inbound cases.
3. User Interface (Odoo Views):
   • A dashboard displaying reconciliation status.
   • Export option for missing cases.

Next Steps

• Implement the data import pipeline.
• Develop the reconciliation logic.
• Test with historical data.
• Deploy & automate reporting.

Overview:

We are building an automation system to reconcile Odoo Daily Sales Reports (DSR) with the Inventory Ledger in Odoo. This will help us track and identify missing or short-received inventory for customers 1475 and 1584. If there are discrepancies, we need to trigger alerts or reports for further investigation.

Key Components:

1. Odoo Daily Sales Report (DSR): This report contains data on items shipped, including customer codes, invoice numbers, and quantities.
2. Odoo Inventory Ledger: This report contains data on what items have been received and how many were received, linked to invoice numbers.
3. FBA Fulfillment Reports: These help cross-check if there are shipments that were not received but should have been, triggering a review.

Step-by-Step Breakdown:

1. Data Extraction & Import:

• Import Odoo DSR:
  • Filter the DSR data for customer codes 1475 & 1584.
  • Extract the following fields from the DSR:
    • Invoice Number
    • Unique Code (combination of item code and invoice number)
    • Quantity Shipped
  • Store this information in a custom Odoo model called inventory.shipment.
• Fetch Inventory Ledger from Odoo:
  • Filter out data related to receipt events.
  • Extract the following fields:
    • Item Code
    • Invoice Number
    • Quantity Received
  • Store this data in another custom Odoo model called inventory.receipts.

2. Reconciliation Logic (Matching Process):

• Match Shipments and Receipts:
  • Compare each entry in inventory.shipment with inventory.receipts by matching the Unique Code (Item Code + Invoice Number).
  • If the Quantity Shipped from DSR is greater than the Quantity Received from the inventory ledger, flag it as Short Received.
  • If the Quantity Received is 0, flag it as Not Received.
• Store the reconciliation result in a model called inventory.reconciliation with the following possible statuses:
  • Matched: If the quantity received matches the quantity shipped.
  • Short Received: If some but not all of the quantity was received.
  • Not Received: If no quantity was received for that shipment.

3. Cross-check with FBA Fulfillment Reports:

• If an item is marked as Not Received:
  • Check the FBA Fulfillment Removal Reports to see if the item was removed but not received. If so, trigger an Amazon Case Review Alert.
  • If no removal order exists for the item, flag it for internal investigation.

4. Automation & Alerts:

• Set up a daily scheduled job that:
  • Automatically imports the Odoo DSR and Inventory Ledger.
  • Runs the reconciliation logic.
  • Updates Odoo with the reconciliation results.
• If discrepancies are found (e.g., missing or short-received items), generate:
  • Email Alerts to the team.
  • An exportable reconciliation report for Amazon case submission.

Deliverables for Developer:

1. Custom Odoo Models:
   • inventory.shipment – stores Sage DSR shipments.
   • inventory.receipts – stores Odoo Inventory Ledger receipts.
   • inventory.reconciliation – stores the results of the reconciliation process.
2. Automated Scripts:
   • Scheduled jobs for daily reconciliation.
   • A matching algorithm that compares shipments and receipts.
   • Alerts and notifications for missing inbound cases.
3. User Interface (Odoo Views):
   • Create a dashboard that displays the reconciliation status.
   • Include an option to export reports for missing or short-received items