Custom Event On Releasing Purchase Orders

​@seadtaydin just providing information that a quotation is connected to a purchase order line (Request No column), not the purchase order header. So you can have a mix of lines on a purchase order that originated from, or did not originate from, a quotation.

This is an interesting requirement.  I have not come across it before.  ​@matt.watters above has provided some good feedback.

Additionally, I think you’d need to consider some of the following scenarios, although perhaps you already have:

• Is the amount fixed to 20,000 or will it be variable?  If it’s variable, what controls the variability (i.e. Supplier, Part Number, etc.)? 
• What happens if a user makes a change after the PO is released? 
  • For example, they receive a quotation for 10 pieces, but they change it to 100 pieces or what if they change the price afterwards (created at 5,000, updated to 30,000), do these situations impact you?
• Is the assumption that the user must create the PO from a Supplier RFQ?  If no, what if there is a legitimate quotation created, but it was not connected to the PO, what is the desired result for this outcome? 
• What would occur after the error is presented?  Does the user have to cancel the PO and create a new one from a supplier quotation? 

@astfarazt @matt.watters — First of all, thank you both for your responses.

@astfarazt, here’s the information you asked for:

• The amount is fixed at 20,000 in our local currency.

• I’ve implemented several event actions to prevent users from changing quantity or price when a purchase order is created from a quotation.

• Our company policy states that direct purchase orders (i.e., created without a quotation — from requisitions) must not exceed a net total of 20,000.

• Yes, users are expected to cancel the PO and go through a request-for-quotation process if the amount is above this limit.

I’ve written an event action to enforce this rule, but it doesn’t seem to work as expected. I attempted to print the OLD and NEW ROWSTATE values to debug the state transition, but it looks like I'm unable to retrieve those values reliably.

DECLARE
  v_net_total       NUMBER;
  v_has_requisition NUMBER;
BEGIN
  -- Calculate net total
  SELECT SUM(NVL(buy_qty_due, 0) * NVL(buy_unit_price, 0) *
             (1 - NVL(discount, 0) / 100))
    INTO v_net_total
    FROM PURCHASE_ORDER_LINE_PART
   WHERE order_no = '&:NEW.ORDER_NO';

  -- Check if theres rfq's connected
  SELECT COUNT(*)
    INTO v_has_requisition
    FROM PURCHASE_ORDER_LINE_PART
   WHERE order_no = '&:NEW.ORDER_NO'
     AND requisition_no IS NOT NULL;

  -- Main error
  IF '&:OLD.ROWSTATE' = 'Planned' AND '&:NEW.ROWSTATE' = 'Released' THEN
    IF v_has_requisition = 0 THEN
      IF v_net_total > 20000 THEN
        ERROR_SYS.System_General('Direkt siparişlerde tutar TMKF limitini geçemez!');
      END IF;
    END IF;
  END IF;
END;

Okay now I figured some things out. And changed the code like below:

DECLARE
  v_net_total       NUMBER;
  v_has_requisition NUMBER;
BEGIN
  -- Calculate net total
  SELECT SUM(NVL(buy_qty_due, 0) * NVL(buy_unit_price, 0) *
             (1 - NVL(discount, 0) / 100))
    INTO v_net_total
    FROM PURCHASE_ORDER_LINE_PART
   WHERE order_no = '&NEW:ORDER_NO';

  -- Check if theres rfq's connected
  SELECT COUNT(*)
    INTO v_has_requisition
    FROM PURCHASE_ORDER_LINE_PART
   WHERE order_no = '&NEW:ORDER_NO'
     AND requisition_no IS NOT NULL;

  -- Main error
  IF '&OLD:ROWSTATE' = 'Planned' AND '&NEW:ROWSTATE' = 'Released' THEN
    IF v_has_requisition = 0 THEN
      IF v_net_total > 20000 THEN
        ERROR_SYS.System_General('Direkt siparişlerde tutar TMKF limitini geçemez!');
      END IF;
    END IF;
  END IF;
END;

But,  the requisition_no field is used both when the purchase order is created from a purchase requisition and when it’s created from an RFQ.
So, checking whether requisition_no is null or not doesn’t clearly tell me if the order was created directly (from a requisition) or via an RFQ.

Is there a reliable way to distinguish if the PO was created from an RFQ vs a direct requisition?

Hi ​@seadtaydin 

Would it be an idea where you define a PO Authorization rule for purchases above 20,000? Before authorizing, the authorizer can check if there are no relevant quotations in place and approve / reject where necessary. 

In the PO, you may add a custom field to reflect the quotation information. Then the approve can see it and approve / reject. 

Can do the same for PO Change orders as well. 

Thanks,
Asela