Re: Service for Canceling ItemIssuance

iain Wed, 14 May 2008 06:07:16 -0700

Bilgin,

Not sure if this will help, but I had to hack an SQL function to unissuean item (haven't got the bandwidth to implement in OFBiz right now).This "backdoor" approach is obviously not the right thing, but maybe thequery will short-circuit some of the analysis needed to work out whichentities need to be modified.


I hope it can help.

Cheers, Iain

Bilgin Ibryam wrote:

Hi all,

I'd like to add a service for canceling item issuance.
I found in the list that this feature is asked before some time
http://www.nabble.com/Cancel-shipment-to11953006.html#a11953006 and also
it is needed for OFBIZ-1766 - canceling shipments.

I'm not familiar with all the inventory and accounting code and I would
appreciate comments/advices/help.

Do you think that this is a right approach for implementing it:

Add a new cancelQuantity field to ItemIssuance entity for storing the
canceled quantity.
Create a CancelItemIssuance service which works the following way:

1. Add the cancelQuantity quantity to ItemIssuance.cancelQuantity field.
2. Create a InventoryItemDetail entry with quantityOnHandDiff and
availableToPromiseDiff equal to cancelQuantity.
3. Call or create a seca for balanceInventoryItems service to update
inventory reservations.
4. Create a seca for adjusting the accounting transaction.

Thanks,
Bilgin

------------------------------------------------------------------------



No virus found in this incoming message.

Checked by AVG.Version: 8.0.100 / Virus Database: 269.23.16/1431 - Release Date: 5/13/2008 7:55 PM

CREATE OR REPLACE FUNCTION unissue_item(varchar, varchar) RETURNS varchar AS $$
DECLARE
  ship_id ALIAS FOR $1;
  ship_item_seq_id ALIAS FOR $2;
  my_item_issuance_id VARCHAR;
  my_order_id VARCHAR;
  my_order_item_seq_id VARCHAR;
  my_ship_group_seq_id VARCHAR;
  my_order_type_id VARCHAR;
  my_inventory_item_id VARCHAR;
  my_receipt_id VARCHAR;
  my_quantity_accepted REAL;
  my_quantity REAL;
  my_acctg_trans_id VARCHAR;
  _now TIMESTAMP;
BEGIN
  SELECT item_issuance_id, order_id, order_item_seq_id, ship_group_seq_id, 
inventory_item_id, quantity
  INTO my_item_issuance_id, my_order_id, my_order_item_seq_id, 
my_ship_group_seq_id, my_inventory_item_id, my_quantity
  FROM item_issuance
  WHERE shipment_id = ship_id
    AND shipment_item_seq_id = ship_item_seq_id;

  IF my_item_issuance_id IS NULL THEN
    RETURN NULL;
  END IF;

  RAISE NOTICE 'item_issuance_id = %, inventory_item_id = %, quantity = %',
    my_item_issuance_id, my_inventory_item_id, my_quantity;

  SELECT order_type_id INTO my_order_type_id
  FROM order_header
  WHERE order_id = my_order_id;

  RAISE NOTICE 'order_id = %, order_item_seq_id = %, order_type_id = %', 
my_order_id, my_order_item_seq_id, my_order_type_id;

  --SELECT quantity INTO my_quantity
  --FROM shipment_item
  --WHERE shipment_id = ship_id
  --  AND shipment_item_seq_id = ship_item_seq_id;
 
  --SELECT receipt_id, inventory_item_id, quantity_accepted
  --INTO my_receipt_id, my_inventory_item_id, my_quantity_accepted
  SELECT receipt_id,  quantity_accepted
  INTO my_receipt_id, my_quantity_accepted
  FROM shipment_receipt
  WHERE shipment_id = ship_id
    AND order_id = my_order_id
    AND order_item_seq_id = my_order_item_seq_id;

  IF my_receipt_id IS NOT NULL THEN
    SELECT acctg_trans_id INTO my_acctg_trans_id
    FROM acctg_trans
    WHERE receipt_id = my_receipt_id;

    DELETE FROM inventory_item_detail
    WHERE receipt_id = my_receipt_id;

    UPDATE inventory_item
    SET quantity_on_hand_total = quantity_on_hand_total - my_quantity_accepted,
        available_to_promise_total = available_to_promise_total - 
my_quantity_accepted
    WHERE inventory_item_id = my_inventory_item_id;

    DELETE FROM acctg_trans_entry
    WHERE acctg_trans_id = my_acctg_trans_id;

    DELETE FROM acctg_trans
    WHERE acctg_trans_id = my_acctg_trans_id;

    DELETE FROM shipment_receipt
    WHERE receipt_id = my_receipt_id;
  END IF;

  _now := now();

  IF my_order_type_id = 'SALES_ORDER' THEN
    INSERT INTO order_item_ship_grp_inv_res(
      order_id, ship_group_seq_id, order_item_seq_id,
      inventory_item_id, reserve_order_enum_id,
      quantity, quantity_not_available,
      reserved_datetime, created_datetime, promised_datetime,
      last_updated_stamp, last_updated_tx_stamp, created_stamp, created_tx_stamp
    )
    VALUES (
      my_order_id, my_ship_group_seq_id, my_order_item_seq_id,
      my_inventory_item_id, 'INVRO_FIFO_REC',
      my_quantity, 0,
      _now, _now, _now,
      _now, _now, _now, _now
    );
  END IF;

  DELETE FROM inventory_item_detail
  WHERE item_issuance_id = my_item_issuance_id;

  DELETE FROM item_issuance_role
  WHERE item_issuance_id = my_item_issuance_id;

  DELETE FROM item_issuance
  WHERE item_issuance_id = my_item_issuance_id;

  DELETE FROM shipment_package_content
  WHERE shipment_id = ship_id
    AND shipment_item_seq_id = ship_item_seq_id;

  DELETE FROM shipment_item
  WHERE shipment_id = ship_id
    AND shipment_item_seq_id = ship_item_seq_id;

  RETURN my_item_issuance_id;
END;
$$ LANGUAGE plpgsql;

SELECT unissue_item('13787', '00001');

Re: Service for Canceling ItemIssuance

Reply via email to