3 Replies Latest reply on Sep 3, 2015 11:03 AM by 2953192

    getting error for event alert

    2953192

      created an event alert for schedule_ship_date NULL also modified the trigger. but while testing functionally, when I make schedule_ship_date null, it's showing the error.

      error:

       

      modified trigger package:

       

      SET DEFINE OFF;

      create or replace TRIGGER APPS.ALR_OE_ORDER_LIN_660_71211_UAR AFTER

      UPDATE ON APPS.OE_ORDER_LINES_ALL FOR EACH row DECLARE MAILID VARCHAR2(255):=NULL;

      REQID                                                         NUMBER;

      RETVAL                                                        BOOLEAN;

      ORGID                                                         VARCHAR2(255);

      MORGID                                                        NUMBER;

      l_hold_count                                                  NUMBER;

      l_flow_status_code                                            VARCHAR2(30);

      l_security_profile_id fnd_profile_option_values.profile_option_value%TYPE;

      l_org_id fnd_profile_option_values.profile_option_value%TYPE;

      default_org_id fnd_profile_option_values.profile_option_value%TYPE;

      BEGIN

        SELECT NVL(mo_global.get_current_org_id, 0) INTO MORGID FROM dual;

        IF (MORGID = 0) THEN

          fnd_profile.get('XLA_MO_SECURITY_PROFILE_LEVEL', l_security_profile_id);

          IF (l_security_profile_id IS NULL) THEN

            fnd_profile.get('ORG_ID', l_org_id);

            ORGID := l_org_id;

          ELSE

            fnd_profile.get('DEFAULT_ORG_ID', default_org_id);

            ORGID := default_org_id;

          END IF;

        ELSE

          ORGID := TO_CHAR(MORGID);

          BEGIN

            SELECT COUNT(1)

            INTO l_hold_count

            FROM oe_order_holds_all ooha

            WHERE ooha.released_flag  = 'N'

            AND ooha.hold_release_id IS NULL

            AND ooha.header_id        = :old.header_id

            AND ooha.line_id          = :old.line_id;

          EXCEPTION

              WHEN OTHERS THEN

                   l_hold_count := 0;

          END;

        

          BEGIN

            SELECT oola.flow_status_code

              INTO l_flow_status_code

              FROM oe_order_lines_all oola

             WHERE oola.line_id = :old.line_id;

          EXCEPTION

              WHEN OTHERS THEN

                   l_flow_status_code := 'AWAITING_SHIPPING';

          END;

        END IF;

        IF (USER||ORGID NOT IN ('APPS101')) OR (:old.schedule_ship_date IS NULL) OR (nvl(:old.schedule_ship_date,'X') = nvl(:new.schedule_ship_date,'X')) OR l_hold_count > 0 OR l_flow_status_code <> 'AWAITING_SHIPPING'  THEN

          RETURN;

        END IF;

        fnd_profile.get('EMAIL_ADDRESS',MAILID);

        IF MAILID                                          IS NULL THEN

          IF alr_profile.value('DEFAULT_USER_MAIL_ACCOUNT')!='O' THEN

            fnd_profile.get('USERNAME',MAILID);

          ELSE

            fnd_profile.get('SIGNONAUDIT:LOGIN_NAME',MAILID);

          END IF;

          IF MAILID IS NULL THEN

            MAILID  :='MAILID';

          END IF;

        END IF;

        RETVAL               :=FND_REQUEST.SET_MODE(DB_TRIGGER => TRUE);

        RETVAL               :=FND_REQUEST.SET_OPTIONS(IMPLICIT => 'ERROR');

        IF fnd_global.resp_id = -1 THEN

          RETVAL             := fnd_request.set_options(datagroup=>'Standard');

        END IF;

        REQID  :=FND_REQUEST.SUBMIT_REQUEST('ALR','ALECTC','OE_ORDER_LINES_ALL',NULL,FALSE,USER,'OE_ORDER_LINES_ALL',rowidtochar(:new.rowid),'U',mailid,ORGID);

        IF REQID=0 THEN

          raise_application_error(-20160, FND_MESSAGE.GET);

        END IF;

      END;

       

       

      please help with possible solutions.