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)
IF (_r.isqtyavail < 0) THEN
SET amazonfba_error = amazonfba_error||'Not enough inventory available for item '||amazonfba_sku||'. ',
amazonfba_status = 'Error'
WHERE amazonfba_shipment_id = _r.shipment_id;
_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);
Not sure if there is a simpler way for doing this…