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 ;
/

Triiger

CREATE OR REPLACE TRIGGER XX.TT_OE_CUSTOM_ALERT
AFTER INSERT OR UPDATE ON APPS.OE_ORDER_HEADERS_ALL
FOR EACH ROW
WHEN (NEW.ATTRIBUTE16 IS NOT NULL AND NEW.FLOW_STATUS_CODE = 'BOOKED')
DECLARE

V TT_CUSTOM_ALERTS%ROWTYPE;



BEGIN



SELECT *

INTO   V

FROM  TT_CUSTOM_ALERTS

WHERE  ORDER_NUMBER = :NEW.ORDER_NUMBER

AND      ORDER_TYPE_ID = :NEW.ORDER_TYPE_ID;



IF :NEW.ATTRIBUTE16 != :OLD.ATTRIBUTE16 THEN



UPDATE TT_CUSTOM_ALERTS

SET      NO_TIMES_UDT = 3

WHERE  ORDER_NUMBER = :NEW.ORDER_NUMBER

AND      ORDER_TYPE_ID = :NEW.ORDER_TYPE_ID;



END IF;



EXCEPTION WHEN NO_DATA_FOUND THEN

INSERT INTO ITW_CUSTOM_ALERTS (ORDER_NUMBER,LIKELY_DISPATCH_DATE,NO_TIMES,ORDER_TYPE_ID,NO_TIMES_UDT)
                              VALUES(:NEW.ORDER_NUMBER,
                                              :NEW.ATTRIBUTE16,
                                                  1,
                                                  :NEW.ORDER_TYPE_ID,1);
END;

TRIGGER

CREATE OR REPLACE TRIGGER APPS.INV_MISC_ISSUE_BOM_COMPS
AFTER INSERT ON APPS.MTL_MATERIAL_TRANSACTIONS
FOR EACH ROW
DECLARE
ln_request_id NUMBER;
lv_count      NUMBER;
ln_user_id NUMBER;
ln_responsibility_id NUMBER;
ln_responsibility_app_id NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ln_user_id:=fnd_profile.value('USER_ID');
ln_responsibility_id:=fnd_profile.value('RESP_ID');
ln_responsibility_app_id :=fnd_profile.value('RESP_APPL_ID');
fnd_global.apps_initialize(ln_user_id,ln_responsibility_id,ln_responsibility_app_id);
--IF :new.transaction_type_id = 262 OR :new.transaction_type_id=42 THEN ----modified on 08-feb-2013 according to harish
IF (:new.transaction_type_id = 262 OR :new.transaction_type_id=42) and (:new.reason_id=4) THEN
--  raise_application_error(-20010,'Misc Receipt');
--262  -Miscellaneous Receipt(RG Update)
--42    --Miscellaneous Receipt
   BEGIN
     select count(jil.item_id)
     into   lv_count
     from   apps.ja_in_osp_items_hdr jih
     ,     apps.ja_in_osp_items_dtl jil
     WHERE  1=1
     AND    jih.osp_item_id     =    jil.osp_item_id
     AND    jih.organization_id=:new.organization_id
     AND    jih.osp_item_id=:new.inventory_item_id;
   END;
IF lv_count > 0 THEN
  --raise_application_error(-20010,'No Component is Defined for this FG Item');
ln_request_id:=  fnd_request.Submit_request(  'INV'
                                             ,'STX_BOM_MISC_ISSUE'
                                             ,''  -- NEW CHANGE'
                                             ,''
                                             ,FALSE
                                             ,:new.organization_id
                                             ,:new.inventory_item_id
                                             ,:new.transaction_date
                                             ,:new.transaction_quantity
                                             ,:new.subinventory_code
                                             ,:new.transaction_type_id
                                             ,:new.distribution_account_id
                                             ,:new.created_by
                                             ,:new.transaction_reference
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0)
                                            ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0),CHR(0));


 END IF; --  Checking the count

 --commit;

    END IF; --Checking the transaction_type_id
COMMIT;
END;