0

We use an module call iparcel for International Orders.

These orders get immediate tracking information and this updates in the correct tables needed for normal tracking use case, however, we use a middle-ware service called celigo to map order data to our ERP, Netsuite(NS).

Celigo uses sales_flat_order table to gather all records involved with order data , to import via an API call, and tracking is not a default field in this table.

I've managed to figure out how to correctly add the table based on a few fields in iParcels sql scripts, but I can not figure out what model to use to write to these tables.

Here you see where iParcel writes the tracking

 protected function _submitParcel($shipment, $order)
    {
        $api = Mage::helper('iparcel/api');
        $response = $api->submitParcel($shipment);

        // Find the name of the Service Level as defined in the Admin
        $serviceLevels = Mage::helper('iparcel')->getServiceLevels();
        $responseServiceLevelId = $response->ServiceLevels[0][0]->ServiceLevelID;
        $serviceLevelTitle = 'I-Parcel';
        if (array_key_exists($responseServiceLevelId, $serviceLevels)) {
            $serviceLevelTitle = $serviceLevels[$responseServiceLevelId];
        }

        // Add tracking number from submitParcel response
        Mage::getModel('sales/order_shipment_api')->addTrack(
            $shipment->getIncrementId(),
            $order->getShippingCarrier()->getCarrierCode(),
            $serviceLevelTitle,
            $response->CarrierTrackingNumber
        );
    }

What I am asking is, is there a way to directly update this field right after it writes tracking , so its there before celigo imports the order ?

  • A more efficient way is to modify Celigo to do a table join from sales_flat_shipment_track to sales_flat_order. – kiatng Sep 13 '16 at 1:48
0

If you can't change the code in celigo, then execute the following 3 mySQL statements in the database:

ALTER TABLE `sales_flat_order` ADD `track_number` text;  
UPDATE `sales_flat_order` o
INNER JOIN `sales_flat_shipment_track` t
ON o.entity_id = t.order_id
SET o.track_number = t.track_number;
CREATE TRIGGER track_number_insert AFTER INSERT ON `sales_flat_shipment_track` FOR EACH ROW
BEGIN
    UPDATE `sales_flat_order` o INNER JOIN `sales_flat_shipment_track` t ON o.entity_id = t.order_id SET o.track_number = t.track_number;
END$$

Note: the above is not tested.

| improve this answer | |
  • This works! Ive been banging my head for 4 days trying to figure this out. – Ryan Patt Sep 13 '16 at 17:38
  • could you clarify what the o and t reference to ? just so I know what this code fully means – Ryan Patt Sep 13 '16 at 17:50
  • Glad that it works for you. o refers to sales_flat_order, t refers to sales_flat_shipment_track. – kiatng Sep 14 '16 at 0:58
  • Also, what are the double dollar signs for ? I tried to look into but did not find anything that explained its use here – Ryan Patt Sep 14 '16 at 13:05
  • $$ is the delimiter, you can define other delimiter. See online doc – kiatng Sep 15 '16 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.