Workflow Not Started/Notification Not Sent For Invoice Holds

Module: Oracle Payables

Symptoms: AP Workflow not started for invoice price variance hold. The invoice approval history is empty and the notification is not sent out to the approver/Buyer

Note: The symptoms may happen to other hold types when the same setup.

Cause: "Manual Release Allowed" options in the Hold Setup for price variance hold was not Enabled.

Reason: Holds resolution workflow is only initiated during invoice validation with both conditions: 
      1) The Initiate Workflow option is selected for hold type; 
      2) ‘Manual Release Allowed’ option is checked.

Solution: 
      Payables Responsibility > Setup : Invoice >Hold and Release Names
      Query for the Hold: example: Price
      Enable "Manual Release Allowed" options

Challenges: 
      The price hold (or other holds) is not supposed to be manually released. By selecting ‘Manual Release Allowed’,  manual release is open.

      Luckily we have the rule that for all those holds supposed to send out notifications by initiating workflow, manual release is not allowed.

Workaround: 
       A form personalization is needed to turn off release button and manual release option in action window. 
      1) Make ‘Release button’ disabled.
      Condition:
          Trigger event: WHEN-NEW-RECORD-INSTANCE
          Trigger Object: AP_HOLDS
          Condition: XX_5011_AP_HOLD_INV_NTF_PKG.XX_Manual_Release_Not_Allowed(:AP_HOLDS.HOLD_NAME) = 'Y' 
      Actions:
          Type: Property
          Object Type: Item
          Target Object: AP_HOLDS_CONTROL.ACTION_BUTTON
          Property Name: ENABLED
          Value: FALSE

      2) Make manual release option disable in action window. 
      Condition:
          Trigger event: WHEN-NEW-RECORD-INSTANCE
          Trigger Object: AP_HOLDS
          Condition: XX_5011_AP_HOLD_INV_NTF_PKG.XX_Manual_Release_Not_Allowed(:AP_HOLDS.HOLD_NAME) = 'Y'
      Actions: 1
          Type: Property
          Object Type: Item
          Target Object: AP_HOLDS.RELEASE_NAME 
          Property Name: ENTERABLE (APPLICATIONS COVER)
          Value: FALSE
      Actions: 2
          Type: Property
          Object Type: Item
          Target Object: AP_HOLDS.RELEASE_REASON 
          Property Name: ENTERABLE (APPLICATIONS COVER)
          Value: FALSE

The package to be called:

XX_5011_AP_HOLD_INV_NTF_PKG.XX_Manual_Release_Not_Allowed(:AP_HOLDS.HOLD_NAME)

Logic: Return ‘Y’ if ‘Initiate Workflow’ option is checked.

  Function XX_Manual_Release_Not_Allowed(l_HOLD_LOOKUP_CODE in varchar2)
    RETURN varchar2 IS
    --  PRAGMA AUTONOMOUS_TRANSACTION;
    l_initiate_workflow_flag ap.ap_hold_codes.initiate_workflow_flag%type;
    --   l_user_releaseable_flag ap.ap_hold_codes.user_releaseable_flag%type;
    l_hold_id ap.ap_holds_all.hold_id%type;
    --  l_hold_lookup_code ap.ap_hold_codes.hold_lookup_code%type; 

  BEGIN
   select nvl(ahc.initiate_workflow_flag, 'N')
      INTO l_initiate_workflow_flag
      from ap.ap_hold_codes ahc, ap_lookup_codes alc
     where ahc.hold_lookup_code = alc.lookup_code
       and alc.lookup_type = 'HOLD CODE'
       and nvl(alc.displayed_field, 'X') = nvl(l_HOLD_LOOKUP_CODE, 'X');
    RETURN l_initiate_workflow_flag;

  EXCEPTION
    WHEN no_data_found THEN
      RETURN 'N';
    WHEN OTHERS THEN
      -- ROLLBACK;
      FND_FILE.PUT_LINE(FND_FILE.LOG,
                        'XX_5011_Manual_Release_Not_Allowed' || ' ' ||
                        SQLERRM || 'hold_lookup_code:' ||
                        l_hold_lookup_code);
      RAISE_APPLICATION_ERROR(-20001,
                              'XX_5011_Manual_Release_Not_Allowed' || ' ' ||
                              SQLERRM || 'hold_lookup_code:' ||
                              l_hold_lookup_code);
  END XX_Manual_Release_Not_Allowed;