ProSKU Orders Status Intake

The purpose of this page is to give good understanding on how the new ProSKU order status intake via API functionality will work and what exactly it will be doing.

At the moment we are intaking order status updates (shipping updates) via FTP, however this is not efficient enough as we are not able to do partial shipping updates via this way. Another benefit of getting the orders statuses via api is that we can automate pre-shipment refunds.

We first need to introduce some new tables as we will need to be able to control some settings.

The first table we need will be called ProSKU Connector. In this table we will store the details we need for our connection to work.

The second table we need will be called ProSKU Sellers ****and this should be a dependant table of ProSKU Connector. We should be able to add records to this table by a button in the bottom right corner named “Insert ProSKU Seller”. The table structure is as follows :

If we have it checked, we raise the Orders > Update Shipping Pending = Yes If it is unchecked we leave Orders > Update Shipping Pending = No. This flag should be dependant from the above flag and only available if Orders is selected. If Order Shipment is selecetd, this should be greyed out and not available for select.</v1.1> |

Example wireframe :

The third table we will need is Account ProSKU Connector, a dependant table of Accounts

The order status updates intake will be done via the get order details call.

API Call : GET https://arcadeapi.prosku.com/orders/get_order_details API Docs : https://arcade.prosku.com/docs?json#get-order-details

The url we want to pick from ProSKU Connector > ProSKU URL

ProSKU provides responses in both xml and json formats, but we want to go with the json one. So we need to include a header of content-type = application/json with all our requests.

We should only pick orders that are with Orders > Order Status = Ready for Shipping AND Orders > Exported = Yes , and since we will have connection with the Seller table, we should only look for orders that have at least one seller which is in our selected list of sellers in ProSKU Sellers . If we have an order with 1 seller that is not in our ProSKU Sellers list we skip it. If we have order with 2 sellers that are not in our ProSKU Sellers list we skip it etc.

There is no bulk order option, so we need to call order by order and this will be done through a parameter called order_reference. The order reference will consist of Account ProSKU Connector > Order prefix+Orders > ID . Example order_reference : TST123321

We are also not able to filter by order_status in ProSKU but the only two statuses we should take into consideration are “Completed” and “Archived”. When a order is supposed to be updated, we will check the order status coming from prosku. If the status is not Completed or Archived, this means that there is no action to be taken on the order and it needs to be skipped, however we will update the Orders ProSKU > Order Status in Hemi. In the beginning this status will be empty. As soon as we receive the order with order status Completed or Archived from Prosku, we will update the order with the relevant information and update the status again in hemi. Once the status in hemi is set to completed or archived, that order will not be updated anymore.

There are two actions that need to happen based on the information provided in the responses for the orders - shipments and refunds (including partial for both) thus we need to create a logic for both cases (explained in the mapping section below).

Example calls :

//Example call response for a fully shipped order : 
{
    "message": "order found",
    "order": {
        "order_reference": "GH73454",
        "order_number": "855515",
        "marshalling_zone": "OB",
        "order_date": "08/09/2024",
        "order_type": "Sales",
        "order_status": "Completed",
        "order_created": "08/09/2024 11:33 BST",
        "created_by": "Api User",
        "order_total": "427.5",
        "currency": "GBP",
        "customer": {
            "delivery_name": "virtue ekhuemelo",
            "notes": null,
            "shipping": {
                "company_name": null,
                "carrier_code": "Parcelhub",
                "additional_carrier_info": "Tracked 48 HV POD",
                "service_code": "40000",
                "address_line_1": "53",
                "address_line_2": "ridgeacre lane quinton",
                "address_line_3": null,
                "address_line_4": null,
                "town": "birmingham",
                "post_code": "B32 1EL",
                "phone_number": null,
                "email": null,
                "delivery_date": "",
                "delivery_time": "",
                "country": {
                    "country_name": "United Kingdom",
                    "iso_2_code": "GB",
                    "iso_3_code": "GBR"
                }
            }
        },
        "custom_fields": {
            "mp_order_number": "#10433",
            "subtotal": "427.5000",
            "servicename": "Tracked 48 HV POD",
            "channel": "Shopify"
        },
        "lines": [
            {
                "product_code": "0734077110439",
                "quantity": 1,
                "price": "427.5",
                "quantity_picked": 1,
                "quantity_ready_to_pick": 0,
                "despatch_doc": "1413362"
            }
        ],
        "despatches": [
            {
                "status": "Confirmed",
                "reference": "GH73454",
                "delivery_note": "1413362",
                "delivery_date": "09/09/2024",
                "delivery_time": "09:49",
                "deliver_to": null,
                "deliver_from": "Warehow 1\nUnit 1-6 Park View\nWhaley thorns\nLangwith Mansfield\nNottinghamshire\nNG20 9DE\nGB",
                "carrier_data": {
                    "despatch_id": "1413362",
                    "carrier_name": "parcelhub",
                    "carrier_service_code": "40000",
                    "tracking_number": "QH031750083GB",
                    "integration_company_id": 7
                },
                "despatch_lines": [
                    {
                        "product_code": "0734077110439",
                        "description": "[SIZE 8] GOLD FOSSIL DRESS",
                        "original_quantity": 1,
                        "config": "Item",
                        "quantity_despatched": 1,
                        "quantity_to_follow": 0,
                        "batch_no": ""
                    }
                ]
            }
        ]
    }
}

// Example call response for a fully refund order :
{
    "message": "order found",
    "order": {
        "order_reference": "GH74330",
        "order_number": "856931",
        "marshalling_zone": "OB",
        "order_date": "12/09/2024",
        "order_type": "Sales",
        "order_status": "Completed",
        "order_created": "12/09/2024 05:12 BST",
        "created_by": "Api User",
        "order_total": "531.34",
        "currency": "GBP",
        "customer": {
            "delivery_name": "Harlie Brindak",
            "notes": null,
            "shipping": {
                "company_name": null,
                "carrier_code": "DHL OWN COURIER",
                "additional_carrier_info": "DHL OWN COURIER",
                "service_code": "DHL OWN COURIER",
                "address_line_1": "184 Java Street",
                "address_line_2": "3L",
                "address_line_3": null,
                "address_line_4": null,
                "town": "Brooklyn",
                "post_code": "11222",
                "phone_number": null,
                "email": null,
                "delivery_date": "",
                "delivery_time": "",
                "country": {
                    "country_name": "United States",
                    "iso_2_code": "US",
                    "iso_3_code": "USA"
                }
            }
        },
        "custom_fields": {
            "supplier": "",
            "width": "",
            "item_weight": "",
            "total": "",
            "total_weight": "",
            "length": "",
            "height": "",
            "waitrose_barcode": "",
            "very_url": "",
            "mp_order_number": "#10477-PREORDER",
            "subtotal": "505.2700",
            "servicename": "DHL OWN COURIER",
            "channel": "Shopify-PREORDER"
        },
        "lines": [
            {
                "product_code": "0748322464871",
                "quantity": 1,
                "price": "505.27",
                "quantity_picked": 0,
                "quantity_ready_to_pick": 0
            }
        ]
    }
}

//Example response for a part shipped order : 
{
    "message": "order found",
    "order": {
        "order_reference": "STBC71846",
        "order_number": "852799",
        "marshalling_zone": "OB",
        "order_date": "01/09/2024",
        "order_type": "Sales",
        "order_status": "Archived",
        "order_created": "01/09/2024 21:33 BST",
        "created_by": "Api User",
        "order_total": "96.0",
        "currency": "GBP",
        "customer": {
            "delivery_name": "Bethany Eaton",
            "notes": null,
            "shipping": {
                "company_name": null,
                "carrier_code": "Parcelhub",
                "additional_carrier_info": "Hermes Parcelhub DF Packet POD DF",
                "service_code": "5010",
                "address_line_1": "Third floor flat 99 Lansdowne Place",
                "address_line_2": "Hove Brighton and Hove",
                "address_line_3": null,
                "address_line_4": null,
                "town": "Hove",
                "post_code": "BN3 1FN",
                "phone_number": null,
                "email": null,
                "delivery_date": "",
                "delivery_time": "",
                "country": {
                    "country_name": "United Kingdom",
                    "iso_2_code": "GB",
                    "iso_3_code": "GBR"
                }
            }
        },
        "custom_fields": {
            "mp_order_number": "3475",
            "total_weight": "0",
            "subtotal": "96.0000",
            "servicename": "Hermes Parcelhub DF Packet POD DF",
            "channel": "Shopify"
        },
        "lines": [
            {
                "product_code": "5056747452638",
                "quantity": 1,
                "price": "16.0",
                "quantity_picked": 1,
                "quantity_ready_to_pick": 0,
                "despatch_doc": "1411980"
            },
            {
                "product_code": "5056747452584",
                "quantity": 1,
                "price": "18.0",
                "quantity_picked": 1,
                "quantity_ready_to_pick": 0,
                "despatch_doc": "1411980"
            },
            {
                "product_code": "5056747435563",
                "quantity": 1,
                "price": "18.0",
                "quantity_picked": 1,
                "quantity_ready_to_pick": 0,
                "despatch_doc": "1411980"
            },
            {
                "product_code": "5056747435501",
                "quantity": 1,
                "price": "20.0",
                "quantity_picked": 0,
                "quantity_ready_to_pick": 0
            },
            {
                "product_code": "5056747452423",
                "quantity": 1,
                "price": "24.0",
                "quantity_picked": 1,
                "quantity_ready_to_pick": 0,
                "despatch_doc": "1411980"
            }
        ],
        "despatches": [
            {
                "status": "Archived",
                "reference": "STBC71846",
                "delivery_note": "1411980",
                "delivery_date": "05/09/2024",
                "delivery_time": null,
                "deliver_to": null,
                "deliver_from": "Warehow 1\nUnit 1-6 Park View\nWhaley thorns\nLangwith Mansfield\nNottinghamshire\nNG20 9DE\nGB",
                "carrier_data": {
                    "despatch_id": "1411980",
                    "carrier_name": "parcelhub",
                    "carrier_service_code": "5010",
                    "tracking_number": "H0461A0000180311|883C00012993A028",
                    "integration_company_id": 7
                },
                "despatch_lines": [
                    {
                        "product_code": "5056747452423",
                        "description": "[Size:L/XL] COBALT JASMIN SEQUIN CROCHET TROUSERS",
                        "original_quantity": 1,
                        "config": "Item",
                        "quantity_despatched": 1,
                        "quantity_to_follow": 0,
                        "batch_no": ""
                    },
                    {
                        "product_code": "5056747435563",
                        "description": "[Size:12] BLUE GLITTER METALLIC TIE SIDE BRIEF",
                        "original_quantity": 1,
                        "config": "Item",
                        "quantity_despatched": 1,
                        "quantity_to_follow": 0,
                        "batch_no": ""
                    },
                    {
                        "product_code": "5056747452584",
                        "description": "[Size:14] CREAM RUCHED UNDERWIRE BIKINI TOP",
                        "original_quantity": 1,
                        "config": "Item",
                        "quantity_despatched": 1,
                        "quantity_to_follow": 0,
                        "batch_no": ""
                    },
                    {
                        "product_code": "5056747452638",
                        "description": "[Size:14] CREAM RUCHED SIDE BIKINI BRIEF",
                        "original_quantity": 1,
                        "config": "Item",
                        "quantity_despatched": 1,
                        "quantity_to_follow": 0,
                        "batch_no": ""
                    }
                ]
            }
        ]
    }
}

These different responses illustrate the variations between different order scenarios.

Response mapping :

OR Orders > Shipping Carrier | Where we store the info will depend on the ProSKU Connector > Store shipment info on order level

If we are storing in Order Shipment we should create a record in Order Shipment table and prepare it for sending to the marketplace as per the abstraction - Order Shipment Functional Details | | | | | carrier_service_code | N/A | | | | | | tracking_number | <v1.1>Order Shipment > Tracking Number OR Orders > Shipping Track Number | Where we store the info will depend on the ProSKU Connector > Store shipment info on order level </v1.1> | | | | | integration_company_id | N/A | | | | | despatch_lines | | | | | | | | product_code | | This will be our Product > EAN . We want to use it in order to identify which products were shipped and add them in Order Shipment Rows when creating the order shipment record. | | | | | description | N/A | | | | | | original_quantity | N/A | | | | | | config | N/A | | | | | | quantity_despatched | Order Shipment Row > Quantity | | | | | | quantity_to_follow | N/A | | | | | | batch_no | N/A | |

If everything goes well, the response will be a success message order found and status will be 200 . After successfully reading the response we should either have created a Order Shipment table record prepared for sending to the marketplace or have refund records created also prepared for sending to the marketplace OR we could have both (when we have had partial ship). As we process only Completed or Archived orders we will essentially be actioning them in hemi once, as this will either be full shipment, full cancellation or part ship part cancel BUT for the full order quantity.

Example error response :

{
    "error": "Order with given order_reference not found"
}

In case of error, the response will be status 500 with the corresponding error message which we want to store in Order Error with type = Connector for the respective order.