Ajio reconciliation

Unicommerce login credentials:
url: https://auth.unicommerce.com/login
id: anushi.d@eshopbox.com
password: 1Unieshop

Queries used:

To update invoices and label URL details in DB, use the query below:

UPDATE shipments
SET label_url = 'https://storage.googleapis.com/unicommerce-files-prod/Ajio%20reco/30-01/AJIOBLR00201l_merged.pdf',
invoice_url = 'https://storage.googleapis.com/unicommerce-files-prod/Ajio%20reco/30-01/AJIOBLR00201i.pdf',
courierName = 'LCM_SK3P', trackingID = 'LCM000145359', invoiceNumber = 'MCS21186', invoiceDate = '2023-02-06 11:59:00'
WHERE id = 15142397;

To remove an order item from the shipment:

UPDATE order_items
SET STATUS = 'CANCELLED', shipment_id = 0
WHERE shipment_id = 14328330 AND orderItemID IN ('ESBEAB01149-27884411');

To change the status of shipment from canceled to created:

UPDATE order_items
SET STATUS= 'CREATED' WHERE
shipment_id = 14328330;

To get order items based on externalShipmentID:

SELECT * FROM order_items
LEFT JOIN shipments ON shipments.id = order_items.shipment_id
WHERE shipments.externalShipmentID IN ('ESBEAB00779-1908-5184')

Delete canceled status from shipment_status_logs table:

DELETE FROM shipment_status_logs
WHERE shipment_id = 14682221 AND `status` = 'cancelled';

Apps and Automations

Ajio reconciliation

Related content