Monday, October 29, 2012

Concurrent Request Details

SELECT fc.request_id, application_name, responsibility_name,
       program_short_name, user_concurrent_program_name, fc.argument_text,
       requestor,
       (   FLOOR (  (  (fc.actual_completion_date - fc.actual_start_date)
                     * 24
                     * 60
                     * 60
                    )
                  / 3600
                 )
        || ' HOURS '
        || FLOOR (  (  (  (fc.actual_completion_date - fc.actual_start_date)
                        * 24
                        * 60
                        * 60
                       )
                     -   FLOOR (  (  (  fc.actual_completion_date
                             
        - fc.actual_start_date
                                     )
                                   * 24
                                   * 60
                                   * 60
                                  )
                                / 3600
                               )
                       * 3600
                    )
                  / 60
                 )
        || ' MINUTES '
        || ROUND ((  (  (fc.actual_completion_date - fc.actual_start_date)
                      * 24
                      * 60
                      * 60
                     )
                   -   FLOOR (  (  (  fc.actual_completion_date
                                    - fc.actual_start_date
                                   )
                                 * 24
                                 * 60
                                 * 60
                                )
                              / 3600
                             )
                     * 3600
                   - (  FLOOR (  (  (  (  fc.actual_completion_date
                                        - fc.actual_start_date
                                       )
                                     * 24
                                     * 60
                                     * 60
                                    )
                                  -   FLOOR (  (  (  fc.actual_completion_date
                                                   - fc.actual_start_date
                                                  )
                                                * 24
                                                * 60
                                                * 60
                                               )
                                             / 3600
                                            )
                                    * 3600
                                 )
                               / 60
                              )
                      * 60
                     )
                  )
                 )
        || ' SECS '
       ) RUN_TIME , TO_CHAR(fc.actual_start_date)run_date,
       user_print_style, output_file_type, outfile_name, logfile_name,
       fc.completion_text
  FROM fnd_conc_req_summary_v fcr,
       fnd_application_tl fa,
       fnd_responsibility_tl fr,
       fnd_concurrent_requests fc
 WHERE 1 = 1
   AND fcr.responsibility_application_id = fa.application_id
   AND fcr.responsibility_id = fr.responsibility_id  
   AND fc.request_id = fcr.request_id
   AND fc.request_id = 6948615

Create User & Copy Responsability from One User To Other

==========================================================
Create User 
===========================================================
 DECLARE
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER := fnd_global.user_id;
x_user_id NUMBER;
l_password VARCHAR2 (2000) := 'PASSWORD';
BEGIN
apps.hr_user_acct_internal.create_fnd_user (p_user_name => 'USER_NAME',
p_password => l_password,
p_employee_id => NULL,
p_user_id => x_user_id,
p_user_start_date => SYSDATE,
p_email_address => NULL,
p_description => NULL,
p_password_date => NULL
);
DBMS_OUTPUT.put_line (x_user_id);

IF x_user_id IS NOT NULL
THEN
UPDATE fnd_user
SET password_lifespan_days = 90
WHERE user_id = x_user_id;

COMMIT;
END IF;
END;


 ==========================================================

Copy Responsability from One User To Other

 =========================================================

DECLARE
  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'USER FROM'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
      -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
    AND furga.end_date IS NULL;
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => 'USER_TO',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
      --
    END;
  END LOOP;
  --
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
  --
  COMMIT;
END;