Ship Sets on Internal and EDI orders

In some cases customers require their shipments to be complete rather than shipped piecemeal. In other cases it is efficient to organize picking work around picking and packing the complete delivery, rather than each line separately.

To address those needs you can use ship sets on Oracle OM lines. All lines belonging to the same ship set will not be released for picking until every single line in the set has full quantity available. In most organizations the largest shipments are replenishments to remote branches, but out of the box there is no way to add a ship set to a manually created internal requisition line or one released from the ASCP plan. This means that the largest shipments cannot be easily consolidated for picking and packing unless someone queries the internal order and adds the ship sets manually after they are created. This is mightily inconvenient if you ask me.

To remedy this problem one might be tempted to simply add a trigger on the OE_LINES_IFACE_ALL to add the ship set. Unfortunately this is is not that simple as single internal order my contain multiple ship sets. An internal order may ship from and to multiple locations and we don't want to pick and pack together items that will have to be dispatched to different destinations. Even within a single destinations we may have requirements to ship on different days and have to have different ship set for each day. There may be additional criteria such as shipping priority or shipping method that drives separate deliveries from a single order.

We can't evaluate all the lines on an order for those conditions before they are all completely inserted making a potential trigger on OE_LINES_IFACE_ALL useless.

Ideally we would put a trigger on OE_HEADERS_IFACE_ALL but the header is inserted before the lines...

Fortunately the Order Import concurrent job updates the OE_HEADERS_IFACE_ALL with the request_id, before even looking at the lines. This is an ideal time point to evaluate all the lines for ship set application, as at this point we know the lines are all complete and their number or data will not have any more inputs.

Here is sample code of an After Update trigger on OE_HEADERS_IFACE_ALL.  In addition to checking the dates and shipping warehouse, it checks if the customer (internal for IR/ISO or external for EDI) has ship sets enforced on the customer or site level. This allows putting tailored ship sets to some customers and none to others, depending on the setup preferences.

CREATE OR REPLACE TRIGGER XX_SHIP_SET_IFACE
AFTER UPDATE
   ON OE_HEADERS_IFACE_ALL
  FOR EACH ROW
DECLARE

-- This trigger is intended to create different ship sets per Request Date and warehouse for each order imported thru the interface
-- The update is in AFTER Update trigger since every time an order is procesed the OE_HEADERS_IFACE_ALL gets updated with the processing concurrent request ID.
-- At this point the lines are all completly in the OE_LINES_IFACE_ALL tabel and can be groupped into ship sets

cursor c is
  SELECT ol.ORIG_SYS_DOCUMENT_REF, ol.REQUEST_DATE, ol.SHIP_FROM_ORG_ID  -- internal order ship set grouping rules
    FROM OE_LINES_IFACE_ALL ol, po_requisition_lines_all prl
   WHERE ol.ORIG_SYS_DOCUMENT_REF =:new.ORIG_SYS_DOCUMENT_REF
   and order_source_id=10 -- internal orders
   and ol.ORIG_SYS_LINE_REF=to_char(prl.REQUISITION_LINE_ID)
 GROUP BY ol.ORIG_SYS_DOCUMENT_REF,
         ol.SHIP_SET_NAME,
         ol.SHIP_FROM_ORG_ID,
         ol.REQUEST_DATE,
         prl.attribute1
UNION all
  SELECT ol.ORIG_SYS_DOCUMENT_REF, ol.REQUEST_DATE, ol.SHIP_FROM_ORG_ID -- EDI orders
    FROM OE_LINES_IFACE_ALL ol
   WHERE ol.ORIG_SYS_DOCUMENT_REF =:new.ORIG_SYS_DOCUMENT_REF
   and order_source_id!=10
GROUP BY ol.ORIG_SYS_DOCUMENT_REF,
         ol.SHIP_SET_NAME,
         ol.SHIP_FROM_ORG_ID,
         ol.REQUEST_DATE
Order by REQUEST_DATE;

cursor ship_set is -- check if the ship sets are enforeced on site or customer level
select 0 from  dual where exists
(select 0 from
hz_cust_site_uses_all
where site_use_id =:new.ship_to_org_id
and SHIP_SETS_INCLUDE_LINES_FLAG='Y'
union all
select 0 from
 hz_cust_accounts_all
where CUST_ACCOUNT_ID =:new.sold_to_org_id
and SHIP_SETS_INCLUDE_LINES_FLAG='Y');


       
i number;

BEGIN
 i:=0;

 for z in ship_set loop --only for sites with enabled ship sets

 for r in c loop -- loop thru each combination of request date and warehouse within each order header

 i:=i+1;

 update OE_LINES_IFACE_ALL
 set SHIP_SET_NAME=to_char(i)
 where ORIG_SYS_DOCUMENT_REF=r.ORIG_SYS_DOCUMENT_REF
 and nvl(REQUEST_DATE, trunc(sysdate))=nvl(r.REQUEST_DATE, trunc(sysdate))
 and nvl(SHIP_FROM_ORG_ID,0)= nvl(r.SHIP_FROM_ORG_ID,0);


 end loop;

 end loop;


EXCEPTION
   WHEN OTHERS
   then null; -- if ship sets cannot be added the order is still good to go

END;
/