ALR_PO_HEADERS_ALL_UAR

create or replace trigger ALR_PO_HEADERS_ALL_UAR
  after update on APPS.PO_HEADERS_ALL
  for each row

declare
 -- pragma autonomous_transaction;   /*设置为自治事务*/

  l_request_id NUMBER;
  l2_request_id number;
  REQID        number;
  retval       BOOLEAN;
  Errbuf      Varchar2(512);
  v_date      date;
  req_status  varchar(10);

BEGIN
if :new.authorization_status in ('IN PROCESS','PRE-APPROVED')  and
     (:old.authorization_status is null or :old.authorization_status in ('REJECTED','INCOMPLETE','REQUIRES REAPPROVAL'))then
    --start for po单价预警mail.
    RETVAL := FND_REQUEST.SET_MODE(DB_TRIGGER => TRUE);
   /* RETVAL := FND_REQUEST.SET_OPTIONS(IMPLICIT => 'ERROR');*/
    REQID  := FND_REQUEST.SUBMIT_REQUEST('PO',
                                         'XXPOPRICE',
                                         NULL, --your Description
                                         null,
                                         FALSE,
                                         :NEW.po_header_id,
                                         /*:new.po_line_id,*/
                                         Chr(0),'','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','',''
                                         /*fnd_global.local_chr(0)*/);

    SELECT FCR.STATUS_CODE
      INTO req_status
      FROM FND_CONCURRENT_REQUESTS FCR
     where FCR.REQUEST_ID = REQID;

     if req_status = 'E' then

    Xxbyd_Sendmail.Test_Sendmail(p_Txt      => to_char(:NEW.po_header_id),
                               p_Sub      => REQID||'PO Sendmail Concurrent error',
                               p_Is_Html  => 'Y',
                               p_Sendor   => 'nb.erpsystem@byd.com',
                               p_Receiver => 'wu.xianling@byd.com',
                               p_Server    => 'smtp.byd.com',
                               p_Port      => 25,
                               p_Need_Smtp => 1,
                               p_User      => 'nb.erpsystem',
                               p_Pass      => 'nbbyd2008');

    end if;
    --end for po单价预警mail.

    --start for po单价为0的mail.
    RETVAL := FND_REQUEST.SET_MODE(DB_TRIGGER => TRUE);
    l2_request_id  := FND_REQUEST.SUBMIT_REQUEST('PO',
                                         'XXMAIL0009',
                                         NULL, --your Description
                                         null,
                                         FALSE,
                                         :NEW.po_header_id,
                                         /*:new.po_line_id,*/
                                         Chr(0),'','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','','',
                                          '','','','','','','','','',''
                                         /*fnd_global.local_chr(0)*/);
  SELECT FCR.STATUS_CODE
      INTO req_status
      FROM FND_CONCURRENT_REQUESTS FCR
     where FCR.REQUEST_ID = l2_request_id;

     if req_status = 'E' then

    Xxbyd_Sendmail.Test_Sendmail(p_Txt      => to_char(:NEW.po_header_id),
                               p_Sub      => l2_request_id||'PO Sendmail Concurrent error',
                               p_Is_Html  => 'Y',
                               p_Sendor   => 'nb.erpsystem@byd.com',
                               p_Receiver => 'wu.xianling@byd.com',
                               p_Server    => 'smtp.byd.com',
                               p_Port      => 25,
                               p_Need_Smtp => 1,
                               p_User      => 'nb.erpsystem',
                               p_Pass      => 'nbbyd2008');

    end if;
    --end for po单价为0的mail.

    --start for po单价预警时更改note栏位.
  v_date := sysdate + 3/(24*60);  /*设置延迟3分钟执行Concurrent*/

  retval       := fnd_request.set_mode(db_trigger => TRUE); /*触发器里提交Request注意加上*/
  --retval       := fnd_request.set_options(implicit => 'ERROR');    \*如果加上此句,表示只有当Concurrent执行失败时，才会在Concurrent Requests form中显示出来*\
  l_request_id := fnd_request.submit_request('PO',
                                             'xx_po_price_upnote',
                                             NULL, --your Description
                                             TO_CHAR(v_date,
                                                     'YYYY/MM/DD HH24:MI:SS'),
                                             FALSE,
                                             :NEW.po_header_id,
                                             Chr(0),'','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','',
                                              '','','','','','','','','','');


  IF l_request_id > 0 then
    COMMIT;
  end if;


    SELECT FCR.STATUS_CODE
      INTO req_status
      FROM FND_CONCURRENT_REQUESTS FCR
     where FCR.REQUEST_ID = l_request_id;


     if req_status = 'E' then

    Xxbyd_Sendmail.Test_Sendmail(p_Txt      => to_char(:NEW.po_header_id),
                               p_Sub      => l_request_id||'PO update note Concurrent error',
                               p_Is_Html  => 'Y',
                               p_Sendor   => 'nb.erpsystem@byd.com',
                               p_Receiver => 'wu.xianling@byd.com',
                               p_Server    => 'smtp.byd.com',
                               p_Port      => 25,
                               p_Need_Smtp => 1,
                               p_User      => 'nb.erpsystem',
                               p_Pass      => 'nbbyd2008');

    end if;
     --end for po单价预警时更改note栏位.
 end if;

EXCEPTION
  WHEN OTHERS THEN
    NULL;
END ALR_PO_HEADERS_ALL_IAR;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11759632/viewspace-722298/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11759632/viewspace-722298/