I have a customer’s web site running osCommerce with just over 2,200 products in a MySQL database. They will be receiving a nightly XML file from their vendor with inventory count updates. I need to take that XML data and update the MySQL products database to update the correct inventory counts daily.
The vendor has provided a very small Java servlet that will receive the XML, however I still need to get that XML data into the MySQL database.
This is all running on a shared-host Linux server.
I have attached the java servlet, sample data, and the email from the vendor explaining how they “push” the data. See below.
From: xxxx xxxx
Subject: RE: Documentation
I’m attaching the sample xml files we use for fulfillment partners at xxxx. The model we use is a fill or kill model where you would send us an order and if we can fulfill the order with stock then we accept the order and if we have to back order the items then we do not fill the order. In either case we send you the [url removed, login to view] document showing SUCCESS or FAILURE of the order submission. I think I talked to you about real time inventory request on the phone, but I was mistaken about that, we would push the inventory to you on a nightly basis for all products that you do business with for xxxx. I have also attached an example servlet program that could be used to listen for data and write it out to a file to be processed on your end when we send you items 1,3,4,5 below. This was just to give you an idea of the environment we work in and how we handle things on our end. The breakdown of the transactions is as follows.
1 InventoryAdvice.xml. This is sent once per day as a FULL inventory feed to you. Durring the day you will also receive this same document as PARTIAL feeds to you as inventory level on certain products at xxxx fluctuate in availability. This could be used to update your local database with availability levels for your site.
2 Order.xml. This would be sent by your system over http to xxxx. Typically to a URL like [url removed, login to view]
3 OrderFulfillmentResponse.xml. This document is sent back to you showing whether or not xxxx could fulfill the order on there system. At the header level you will receive a SUCCESS or FAILURE response with a description as to the failure and at the line level will receive SUCCESS or FAILURE and descriptions for each line individually. With the fill or kill model if there is a single line item FAILURE the header will report a FAILURE and the entire order will be rejected.
4 AdvanceShipmentNotification.xml. This document is sent out to you when the order is shipped from xxxx. It contains the quantity shipped for each line item.
5 InvoiceNotification.xml. This is a consolidated invoice sent to you once per day for all orders that have been placed by you at xxxx and have shipped and invoiced that day. It will contain detail of the individual line items that invoiced for that day along with the product, price extension etc.
Not all documents have to be integrated. For example you might choose to only have the [url removed, login to view] pushed to you daily and then you handle placing the order and getting invoiced manually instead of electronically. Or you might choose to only do steps 1-4 and have xxxx invoice you via a standard paper invoice in the mail. All of that is really up to you and xxxx as to how much of the integration between your site and xxxx is handled electronically.
Let me know if you have any questions.
Final clarification on this project:
1) Inventory quantity updates only from the XML file. Not the entire product database. Will receive XML updates as often as every 15 minutes from vendor pushing XML to the site.
2) The vendor provides an Excel spreadsheet on a monthly basis. Can you write a module in the admin area to upload this spreadsheet, compare and update against products already in the database? Attaching sample spreadsheet.