xTuple.com xTupleU Blog & News Customer Support

Automatically fulfill an order

How do I ...?
#1

Hello,

I’m trying to import some Amazon order for which I have all the information required to have it fulfilled (customer info, item #, qty., tracking number,…)

Importing the Sales Order is easy but I’m struggling a little bit trying to auto distribute and ship the order automatically. The items are always shipped from the same Site and Location and the itemsite control method is regular. Since I might be trying to reinvent the wheel, can somebody point me to the correct function/s I should be using?

I was testing with createItemlocdistParent() and issueToShipping() but I’m not getting the correct results.

Thanks,
Javier

#2

This function(s) may help you

BEGIN
	FOR _r in select coitem_id, coitem_qtyord from  public.coitem where coitem_qtyshipped = 0
	loop
		perform public.issuetoshipping(_r.coitem_id, _r.coitem_qtyord);
	end loop;
	
END;

and then

BEGIN
	FOR _r in select ship_id from (select  cast(getOpenShipmentId('SO', shiphead_id, 4) as integer) ship_id from public.shiphead where not shiphead_shipped ) a
				where ship_id is not null
	loop
		perform public.shipshipment(_r.ship_id, current_timestamp);
	end loop;
END
#3

Hello,

Thank you for the reply! The problem is that the item is location controlled so there need to be a distribution from the specific location. The location is always the same one “X” that is also the default location, so I would need a function that would equal to the system “Default and Post”.

I would assume that having the coitem_id, coitem_qty and location_id there should be a way of doing this.

Using the functions createItemlocdistParent() and issueToShipping() I got some inventory history transactions and GL transactions but those remain unposted and digging a little bit I see problems with the itemlocdist… This was my initial approach:

        FOR _r IN (SELECT amazonfba_date AS orderdate, amazonfba_shipment_id AS shipment_id, SUM(amazonfba_qty::NUMERIC) as qty, 
    			          amazonfba_tracking AS tracking, amazonfba_sku, itemsite_id, coitem_id, cohead_id,
    			          sufficientInventoryToShipItem('SO', coitem_id) AS isqtyavail, 
    			          isControlledItemsite(itemsite_id) AS controlled,
    			          calcIssueToShippingLineBalance('SO', coitem_id) AS balance, 
    			          coitem_qty_invuomratio, itemsite_issuelocation_id AS defaultlocid
    			   FROM trdk.amazonfba LEFT OUTER JOIN cohead ON (amazonfba_cohead_number=cohead_number)
    			                       LEFT OUTER JOIN itemsite ON ((itemsite_item_id = getitemid(amazonfba_sku)) AND itemsite_warehous_id = getwarehousid('AMAZON','ALL')) 
    			                       LEFT OUTER JOIN coitem ON (coitem_cohead_id = cohead_id AND coitem_itemsite_id = itemsite_id)
    			   WHERE amazonfba_status = 'New'  
    	                 AND amazonfba_dirty = 'false' 
    			      --   AND amazonfba_shipment_id= 'DWd79t4zp'   --DELETE THIS LATER
    			   GROUP BY  amazonfba_date, amazonfba_sku, amazonfba_shipment_id, amazonfba_tracking, 
    			             itemsite_id, coitem_id, cohead_id, itemsite_issuelocation_id)
    LOOP
            
    		IF (_r.isqtyavail < 0) THEN
    		    
    			  UPDATE trdk.amazonfba
    			  SET amazonfba_error = amazonfba_error||'Not enough inventory available for item '||amazonfba_sku||'. ', 
    			      amazonfba_status = 'Error'
    			  WHERE amazonfba_shipment_id = _r.shipment_id;
    			  
    		ELSE	  
    		
    	          _itemlocSeries :=  NEXTVAL('itemloc_series_seq');
    	          _itemlocdist:= createItemlocdistParent(_r.itemsite_id, _r.qty, 'SO', _r.coitem_id, _itemlocSeries, NULL, _r.defaultlocid, 'SH');
                  PERFORM issueToShipping('SO', _r.coitem_id, _r.qty, _itemlocSeries, now(), NULL, FALSE, TRUE);
    END LOOP;

Not sure if there is a simpler way for doing this…

Thanks,
Javier

#4

I believe you’re looking for “distributeitemlocseries()” and “postItemLocSeries()”

Both functions take the itemlocSeries sequence number.

This will post those transactions for you.

#5

Hello,

I have checked the suggestion and those functions will work but I think before it’s necessary to update the column itemlocdist_source_id since it has a NULL value instead of the itemloc_id related to the itemsite_issuelocation_id. The reason for this is to allow distributeitemlocseries() inserting into invdetail table.

I think that could possibly be corrected on the function public._postinventorytrans() passing that value as pitemlocdistid to the function createItemlocdistParent().

Javier

#6

You are correct.

We update the “itemlocdist” table directly prior to calling distributeitemlocseries() and then postitemlocseries().

update itemlocdist set itemlocdist_source_type='L',itemlocdist_source_id='$A' where itemlocdist_series ='$B'

where $A is the location_id of the location you are sourcing the inventory from and $B is series # (which you receive back during your initial issuetoshipping() call).