Oracle EBS - How to add responsibility to user using PLSQL Script

 --Add responsibility

DECLARE

    v_user_name    VARCHAR2(30):= '&USER_NAME';

    lc_resp_appl_short_name   VARCHAR2(100)    := 'SYSADMIN';

    lc_responsibility_key          VARCHAR2(100)    := 'SYSTEM_ADMINISTRATOR';

    lc_security_group_key        VARCHAR2(100)    := 'STANDARD';

    ld_resp_start_date                DATE                        := TO_DATE(sysdate);

    ld_resp_end_date                 DATE                        := NULL;

 

BEGIN

     fnd_user_pkg.addresp

     (   username           => v_user_name,

        resp_app             => lc_resp_appl_short_name,

        resp_key             => lc_responsibility_key,

        security_group  => lc_security_group_key,

        description         => NULL,

        start_date           => ld_resp_start_date,

        end_date            => ld_resp_end_date

    );

 

COMMIT;

 

EXCEPTION

            WHEN OTHERS THEN

                        ROLLBACK;

                        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Oracle EBS - How to reset oracle application password using PLSQL script

 --Reset Password

DECLARE

  v_user_name    VARCHAR2(30):= '&USER_NAME';

  v_new_password VARCHAR2(30):= 'welcome';

  v_status       BOOLEAN;

BEGIN

  update fnd_user set user_guid = null where user_name = v_user_name;

 

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name,

                                              newpassword => v_new_password

                                            );

  IF v_status =TRUE THEN

    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

Oracle EBS - Order Management Sales Order Cancelled Line Return Reason SQL Query

Requirement: We need to fetch cancelled sale order line return reason query.

Navigation: Order Management Responsibility > Sales Order > Action > Additional Information > Quantity Hitrory.

SQL Query:

 (select LISTAGG( FLV.MEANING, ',') WITHIN GROUP (ORDER BY  FLV.MEANING)

        from 

        OE_REASONS OER,

        fnd_lookup_values_vl FLV

        where 

        FLV.LOOKUP_TYPE=OER.REASON_TYPE

        AND FLV.LOOKUP_CODE=OER.REASON_CODE

        AND OER.HEADER_ID=OOHA.HEADER_ID  --Need to pass order header Id

        AND OER.ENTITY_ID=OOLA.LINE_ID        -- Need to pass order line Id

) return_reason_code