Service for Canceling ItemIssuance

[Bilgin Ibryam]

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
 

[Iain Fogg]

Bilgin,

Not sure if this will help, but I had to hack an SQL function to unissue
an item (haven't got the bandwidth to implement in OFBiz right now).
This "backdoor" approach is obviously not the right thing, but maybe the
query will short-circuit some of the analysis needed to work out which
entities 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');

[David E Jones]

Stepping back a little (to hopefully make it easier to see what needs  
to be done here), from a business perspective does it mean and what  
needs to happen when you cancel an ItemIssuance?

When an item is issued from inventory (usually to a shipment), it  
represents a stock-out and inventory is committed to the shipment or  
whatever it is issued to.

What is the scenario where this would be reversed?

The main reason I'm asking is because chances are there is more that  
needs to be considered and tracked with this sort of operation...

-David


On May 14, 2008, at 4:33 AM, 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
>
>

[Bilgin Ibryam]

>What is the scenario where this would be reversed?

If the customer change mind after the item is issued from inventory but
the shipment still not packed(invoice not created) then there is no way
to remove the item from shipment and from the future invoice that will
be created. The only solution is to create an order return and invoice
for it.
I'd like to cancel the issuance if the shipment is still not packed, and
not to invoice that item.

Are the steps mentioned in my previous email fine for this scenario?

Thanks for your reply
Bilgin

[Iain Fogg]

David,

2 scenarios...

1) Receiving a purchase order and the receiver mistakenly receives the
wrong product...need to unissue an item (happens surprisingly often)
2) Issued an item to fulfil a sales order (not necessarily packed the
shipment and invoiced yet) - customer cancels the order, so we need to
unissue the item and give it back to inventory (this happens pretty
often for us)

Note scenario (2) probably doesn't happen that often if you have a web
store, but with a real store customers have more scope and time for
changing their mind :-)

Cheers, Iain

David E Jones wrote:

>
> Stepping back a little (to hopefully make it easier to see what needs
> to be done here), from a business perspective does it mean and what
> needs to happen when you cancel an ItemIssuance?
>
> When an item is issued from inventory (usually to a shipment), it
> represents a stock-out and inventory is committed to the shipment or
> whatever it is issued to.
>
> What is the scenario where this would be reversed?
>
> The main reason I'm asking is because chances are there is more that
> needs to be considered and tracked with this sort of operation...
>
> -David
>
>
> On May 14, 2008, at 4:33 AM, 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
>  

[Bilgin Ibryam]

Thanks Iain, I will check it!

[Bilgin Ibryam]

Iain described two scenarios:

1. Cancel issuance during receaiving purchase order.
2. Cance issuance done for sales shipment.

I mainly work on the second senario and found one more step to be done: Reserve Inventory for the order item. So far the steps looks like this:

1. Add the cancelQuantity to ItemIssuance.cancelQuantity field.
2. Create a InventoryItemDetail.
3. ReserveInventory for the cancelledQuantity.
4. Call balanceInventoryItems.
5. Create a seca for adjusting the accounting transaction.

[Jacopo Cappellato-3]

Hi Bilgin,

please see my comments inline:

On May 15, 2008, at 6:48 PM, Bilgin Ibryam wrote:

>
> Iain described two scenarios:
>
> 1. Cancel issuance during receaiving purchase order.
> 2. Cance issuance done for sales shipment.
>
> I mainly work on the second senario and found one more step to be  
> done:
> Reserve Inventory for the order item. So far the steps looks like  
> this:
>
> 1. Add the cancelQuantity to ItemIssuance.cancelQuantity field.
> 2. Create a InventoryItemDetail.
> 3. ReserveInventory for the cancelledQuantity.
> 4. Call balanceInventoryItems.

or maybe the newer service: "reassignInventoryReservations"

>
> 5. Create a seca for adjusting the accounting transaction.
>

Yes, there should be a service that can be used to revert an existing  
accounting transaction: so we may retrieve the accounting transaction  
id and call that service to revert it (and this new service can be  
placed with the other postAcct* services in the accounting component).

In general it sounds good to me,

Jacopo

PS: also I agree that we should prevent the user to modify the  
shipment items of a packed (invoiced) shipment.

>
> --
> View this message in context: http://www.nabble.com/Service-for-Canceling-ItemIssuance-tp17228500p17257630.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.
>

[Bilgin Ibryam]

Jacopo,

it's not possible to retrieve the accounting transaction id, because in the AcctgTrans entity keeps only the shipmentId. And one shipment may have many ItemIssuances associated...

I think it's not necessary to revert the whole transaction. Creating reversed transaction only for the canceled  quantity would be enough.

The seca which does accounting transaction is "createAcctgTransForCanceledSalesShipmentIssuance" in OFBIZ-1798 What do you think?