Process
Navigate to CommerceIQ -> Profit Recovery Overview
Download the data from the journey map
Filter the data to open items
Paste the applicable values into Amazons open statement template
Upload to Amazon using their standard process
Gathering the data from CommerceIQ
In CIQ Navigate to Profit Recovery Overview and update to the desired time frame
At the top of the Profit Recovery section, click the forward arrow and select the Excel format from the pop-up to download data from the journey map for the selected time range.
In the downloaded spreadsheet, filter for the invoices that still have an open amount. This will be the list of unpaid shortage deductions for the Open Statement.
Before copying the Invoice Number to the Amazon Open Statement Template, the ending “R” needs to be removed. The data is correct however it’s pulled from the Invoice section of VC where Amazon includes the “R” to the Invoice Number
To Remove the last “R” from the invoice number:
In a blank column use the following function
=LEFT(B2,LEN(B2)-1) where B2 = the cell where the Invoice Number is located, this will most likely be the B Column, copy the formula to the rest of the invoice numbers on the spreadsheet
The result is all the Invoice Numbers will end in SC and not SCR, for example 1234SCRSCRSCR will become 1234SCRSCRSC, the deduction invoice number always ends in the SC
Fill out Amazon's Open Statement template
Start by downloading the Standard statement of open items template from Amazon Vendor Central.
Amazon requires the list of unpaid shortage deductions in their Open Statement Template. The data from the downloaded CIQ spreadsheet can be copied and pasted into the Amazon Template with the following translations:
Key: Template Name = CIQ Name
Document Number = Root Invoice
Document Date = Invoice Date
Document Due Date = Due Date
Document Amount = Invoice Amount (this is the original invoice amount)
Item Balance = Open Amount (this is the last iteration of the shortage churn that is unpaid)
Amazon Document = Invoice Number
Amazon Document Description - write in “Shortage Claim” and copy to all rows with data
Amazon's Open Statement file:
CommerceIQ's download, filtered to applicable collumns:
Copy and paste the shortage credit deduction data into the Amazon template and Save the template. Completed document shown below.
Submit in Amazon Vendor Central
Send the completed Open Statement Template to Amazon via a contact us case in Vendor Central.
Open a case: Contact us > Payments > Statement of Open Items Submission
Amazon will review the listing and respond back with a reconciliation:
Amazon will identify shortage invoices that will be paid back, meaning they found inventory to support the reversal of the claim
Amazon will also identify invoices which will not be paid back as the inventory and research do not support reversing the shortage claim deduction, this becomes the list of unpaid shortage claims deductions for a settlement agreement
Submit the list of unpaid shortage claim deductions (from the reconciliation) back to Amazon via the contact us case, open a new case if needed and reference the Open Statement Case Number. Instruct Amazon to forward the list of unpaid shortage deductions to their financial operations (fin ops) team for a settlement review.
Amazon will respond back with a settlement amount and you can proceed from here.