Package
----------------
Create table xxxx_po_headers
(
 po_id INTEGER
,vendor_id INTEGER
,suggested_vendor_id INTEGER
,po_description VARCHAR2(250)
,po_status    VARCHAR2(30)
,comments_from_approver VARCHAR2(2000)
,send_email_to VARCHAR2(300)
) ;







CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
  PROCEDURE is_po_valid
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  );

  PROCEDURE set_wf_approver_role
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  );

  PROCEDURE set_wf_status_to_validated
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  );

  PROCEDURE start_training_wf(p_po_id IN INTEGER);

END xxxx_po_wf_training_pkg;
/







CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

  FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
  BEGIN
    IF p_po_id > 0
    THEN
      RETURN TRUE;
    END IF;
    RETURN FALSE;
  END is_po_validated;

  PROCEDURE is_po_valid
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  ) IS
  BEGIN
    IF (funcmode != 'RUN')
    THEN
      RETURN;
    END IF;
    --Just a pseudo validation
    --call a function here, and either assign COMPLETE:Y or COMPLETE:N

    IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
                                                             ,itemkey  => itemkey
                                                             ,aname    => 'PO_ID'))
    THEN
      RESULT := 'COMPLETE:Y';
    ELSE
      RESULT := 'COMPLETE:N';
    END IF;
  END is_po_valid;

  PROCEDURE set_wf_approver_role
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  ) IS
    v_role_email xxxx_po_headers.send_email_to%TYPE;
    n_ctr        INTEGER := 0;
  BEGIN
    v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
                                                   ,itemkey  => itemkey
                                                   ,aname    => 'SEND_TO_EMAIL'));
    SELECT COUNT(*)
    INTO   n_ctr
    FROM   wf_local_roles
    WHERE  NAME = v_role_email;

    IF n_ctr = 0
    THEN
      wf_directory.createadhocrole(role_name               => v_role_email
                                  ,role_display_name       => v_role_email
                                  ,role_description        => v_role_email
                                  ,notification_preference => 'MAILHTML'
                                  ,email_address           => v_role_email
                                  ,status                  => 'ACTIVE'
                                  ,expiration_date         => NULL);
    END IF;
    wf_engine.setitemattrtext(itemtype => itemtype
                             ,itemkey  => itemkey
                             ,aname    => 'SEND_TO_ROLE'
                             ,avalue   => v_role_email);
    RESULT := 'COMPLETE:Y';

    RESULT := 'COMPLETE:Y';
  END set_wf_approver_role;

  PROCEDURE set_wf_status_to_validated
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  ) IS
  BEGIN
    UPDATE xxxx_po_headers
    SET    po_status = 'VALIDATED'
    WHERE  po_id = wf_engine.getitemattrnumber(itemtype, itemkey, 'PO_ID');
    RESULT := 'COMPLETE:Y';  
  END set_wf_status_to_validated;

  PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
    l_itemtype VARCHAR2(30) := 'XXXXPTR';
    l_itemkey  VARCHAR2(300) := 'TRAINING-' || p_po_id;
    CURSOR c_get IS
      SELECT *
      FROM   xxxx_po_headers
      WHERE  po_id = p_po_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;

    wf_engine.createprocess(l_itemtype, l_itemkey, 'MAIN_TRAINING_PROCESS');

    wf_engine.setitemuserkey(itemtype => l_itemtype
                            ,itemkey  => l_itemkey
                            ,userkey  => 'USERKEY: ' || l_itemkey);
    wf_engine.setitemowner(itemtype => l_itemtype
                          ,itemkey  => l_itemkey
                          ,owner    => 'SYSADMIN');

    wf_engine.setitemattrnumber(itemtype => l_itemtype
                               ,itemkey  => l_itemkey
                               ,aname    => 'PO_ID'
                               ,avalue   => p_po_id);

    wf_engine.setitemattrtext(itemtype => l_itemtype
                             ,itemkey  => l_itemkey
                             ,aname    => 'SEND_TO_EMAIL'
                             ,avalue   => p_get.send_email_to);
    wf_engine.setitemattrtext(itemtype => l_itemtype
                             ,itemkey  => l_itemkey
                             ,aname    => 'PO_DESCRIPTION'
                             ,avalue   => p_get.po_description);
    wf_engine.startprocess(l_itemtype, l_itemkey);

  END start_training_wf;

END xxxx_po_wf_training_pkg;
/







INSERT INTO xxxx_po_headers
  (po_id
  ,vendor_id
  ,suggested_vendor_id
  ,po_description
  ,po_status
  ,comments_from_approver
  ,send_email_to)
VALUES
  (1000 --po_id
  ,10000 --vendor_id
  ,NULL --suggested_vendor_id
  ,'This is PO Training Description' --po_description
  ,'INITIAL' --po_status
  ,NULL --comments_from_approver
  ,'ANILPASSI@GMAIL.com' --send_email_to
   );

declare
begin
  commit ;

end ;
/

 

declare
begin
  xxxx_po_wf_training_pkg.start_training_wf(p_po_id => 1000);
end ;
/