Tuesday, January 9

Query too Find Profile Option Values

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE 1=1
and po.USER_PROFILE_OPTION_NAME in( 'ICX: Limit time','ICX: Limit connect', 'ICX:Session Timeout')
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
-- AND decode(to_char(pov.level_id),
-- '10001', '',
-- '10002', app.application_short_name,
-- '10003', rsp.responsibility_key,
-- '10005', svr.node_name,
-- '10006', org.name,
-- '10004', usr.user_name,
-- '???') LIKE '%RCH'
ORDER BY "NAME", pov.level_id, "VALUE";

Script to create user accounts and assign the responsibilities from back end.

DECLARE

l_record NUMBER := 0;

l_user_id NUMBER := fnd_global.user_id;

x_user_id NUMBER := NULL;

l_password VARCHAR2 (30) := 'welcome1';

l_appl_name VARCHAR2 (20);

l_resp_name VARCHAR2 (200);

l_resp_key VARCHAR2 (200);

l_resp_desc VARCHAR2 (2000);

CURSOR cur_user_rec

SELECT *

FROM (SELECT DECODE (col,

1, "1",

2, "2",

3, "3",

4, "4",

5, "5",

6, "6",

7, "7" /* ,

8, "8",

9, "9",

10, "10",

11, "11",

12, "12",

13, "13",

14, "14" */

)

username

FROM (SELECT 'JBAVISETTI' AS "1" ,

'USERNAME2' AS "2", -- replace with required usernames

'USERNAME3' AS "3",

'USERNAME4' AS "4",

'USERNAME5' AS "5",

'USERNAME6' AS "6",

'USERNAME7' AS "7" /*,

'USERNAME8' AS "8",

'USERNAME9' AS "9",

'USERNAME10' AS "10" ,

'USERNAME11' AS "11",

'USERNAME12' AS "12",

'USERNAME13' AS "13",

'USERNAME14' AS "14" */

FROM DUAL),

(SELECT ROWNUM AS col

FROM all_objects

WHERE ROWNUM <= 20))

WHERE username IS NOT NULL;

CURSOR cur_resp_rec

SELECT *

FROM (SELECT DECODE (col,

1, "1",

2, "2",

3, "3",

4, "4",

5, "5",

6, "6",

7, "7",

8, "8",

9, "9",

10, "10",

11, "11",

12, "12",

13, "13",

14, "14" /*,

15, "15",

16, "16",

17, "17",

18, "18",

19, "19",

20, "20",

21, "21",

22, "22",

23, "23",

24, "24",

25, "25",

26, "26",

27, "27",

28, "28",

29, "29",

30, "30",

31, "31",

32, "32",

33, "33" */

)

respname

FROM (SELECT 'Application Developer' AS "1",

'Application Diagnostics' AS "2",

'Cash Management Superuser' AS "3",

'Cost Management - SLA' AS "4",

'Fixed Assets Manager' AS "5",

'Fixed Assets Administrator' AS "6",

'General Ledger Super User' AS "7",

'Payables Manager' AS "8",

'Receivables Inquiry' AS "9",

'Receivables Manager' AS "10",

'Tax Managers' AS "11",

'Workflow Administrator' AS "12",

'XML Publisher Administrator' AS "13",

'System Administrator' AS "14"

FROM DUAL),

(SELECT ROWNUM AS col

FROM all_objects

WHERE ROWNUM <= 100))

WHERE respname IS NOT NULL;

CURSOR cur_resp_detail_rec (

p_resp_name VARCHAR2)

SELECT frt.responsibility_name,

fa.application_short_name,

fr.RESPONSIBILITY_KEY,

frt.description,

frt.responsibility_id,

frt.application_id

FROM fnd_responsibility_tl frt,

fnd_responsibility fr,

fnd_application fa

WHERE frt.responsibility_id = fr.responsibility_id

AND frt.application_id = fa.application_id

AND UPPER (responsibility_name) = UPPER (p_resp_name);

BEGIN

FOR rec_user_cur IN cur_user_rec

LOOP

SELECT COUNT (user_id)

INTO l_record

FROM fnd_user

WHERE user_name = UPPER (rec_user_cur.username);

IF l_record = 0

THEN

hr_user_acct_internal.create_fnd_user (

p_user_name => rec_user_cur.username,

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 => 'Apps Associates Consultant',

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;

ELSE

SELECT user_id

INTO x_user_id

FROM fnd_user

WHERE user_name = UPPER (rec_user_cur.username);

DBMS_OUTPUT.put_line (

'User '

|| rec_user_cur.username

|| ' is exists with user id '

|| x_user_id);

END IF;

FOR rec_resp_cur IN cur_resp_rec

LOOP

FOR rec_resp_detail_cur

IN cur_resp_detail_rec (rec_resp_cur.respname)

LOOP

SELECT COUNT (RESPONSIBILITY_ID)

INTO l_record

FROM FND_USER_RESP_GROUPS_ALL

WHERE USER_ID = x_user_id

AND RESPONSIBILITY_ID =

rec_resp_detail_cur.responsibility_id

AND RESPONSIBILITY_APPLICATION_ID =

rec_resp_detail_cur.application_id;

IF l_record = 0

THEN

BEGIN

fnd_user_pkg.addresp (

username => UPPER (rec_user_cur.username),

resp_app => rec_resp_detail_cur.application_short_name,

resp_key => rec_resp_detail_cur.RESPONSIBILITY_KEY,

security_group => 'STANDARD',

description => rec_resp_detail_cur.description,

start_date => SYSDATE,

end_date => NULL);

COMMIT;

DBMS_OUTPUT.put_line (

rec_resp_detail_cur.responsibility_name

|| ' Responsibility Added Successfully for the user '

|| rec_user_cur.username);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

rec_resp_detail_cur.responsibility_name

|| ' Responsibility is not added for the user '

|| rec_user_cur.username

|| ' due to'

|| SQLCODE

|| SUBSTR (SQLERRM, 1, 100));

END;

ELSE

DBMS_OUTPUT.put_line (

rec_resp_detail_cur.responsibility_name

|| ' Responsibility already assigned to the user '

|| rec_user_cur.username);

END IF;

END LOOP;

COMMIT;

END;

Script To Remove End Date From Responsibilities Assigned to a user

DECLARE

p_user_name VARCHAR2(50) := 'RSRIVASTAV';

p_resp_name VARCHAR2(50) := 'System Administrator';

v_user_id NUMBER(10) := 0;

v_responsibility_id NUMBER(10) := 0;

v_application_id NUMBER(10) := 0;

BEGIN

SELECT

user_id

INTO

v_user_id

FROM

fnd_user

WHERE

upper(user_name) = upper(p_user_name);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('User not found');

RAISE;

WHEN OTHERS THEN

dbms_output.put_line('Error finding User.');

RAISE;

END;

BEGIN

SELECT

application_id,

responsibility_id

INTO

v_application_id,v_responsibility_id

FROM

fnd_responsibility_vl

WHERE

upper(responsibility_name) = upper(p_resp_name);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('Responsibility not found.');

RAISE;

WHEN too_many_rows THEN

dbms_output.put_line('More than one responsibility found with this name.');

RAISE;

WHEN OTHERS THEN

dbms_output.put_line('Error finding responsibility.');

RAISE;

END;

BEGIN

dbms_output.put_line('Initializing The Application');

fnd_global.apps_initialize(user_id => v_user_id,resp_id => v_responsibility_id,resp_appl_id => v_application_id);

dbms_output.put_line('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

fnd_user_resp_groups_api.update_assignment(user_id => v_user_id,responsibility_id => v_responsibility_id,responsibility_application_id

=> v_application_id,security_group_id => 0,start_date => SYSDATE,end_date => SYSDATE + 10,description => NULL);

dbms_output.put_line('The End Date has been removed from responsibility');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error calling the API');

RAISE;

END;

Friday, November 21

Performance Rating/Review Load

Performance Rating Load

From Application : Performance rating can be entered from application as in below

· Select Appropriate Business Group Responsibility: ex: HR Operations US

· Navigation : HR Operations US > People > Enter and Maintain > Query for Employee > Assignments > Others > Performance

· Enter, 'Effective Date' and 'Performance Rating', 'Next Date'

· Save

Tables effected : PER_PERFORMANCE_REVIEWS

Loading through Program or backend: Performance rating can also be entered from backend or by developing a script and registering as Program for bulk Load

· Api : HR_PERF_REVIEW_API.CREATE_PERF_REVIEW

· Required data:

Created_by,

person_id ( can be derived if Employee number provided)

full_name (just to validate or to confirm the exact employee provided in csv or for any typo)

performance_rating

Effective_date

next_review_date

Business_Group

Single Insert : using the below script done a single insert and the data get inserted into application and could able to see it ( refer to the below screenshot)

The below script is just a sample single insert script followed by a a package /procedure with required validations with commit and Roll back options

DECLARE

-- Start of Variable declarations, Initialize Variables with appropriate values to test the script

-- Input Variables

V_VALIDATE BOOLEAN;

-- Output Variables

V_PERFORMANCE_REVIEW_ID NUMBER;

-- Input Variables

V_PERSON_ID NUMBER := 21227;

V_EVENT_ID NUMBER;

V_REVIEW_DATE DATE := '01-NOV-2014' ;

V_PERFORMANCE_RATING VARCHAR2(2000) := 'XX_4';

V_NEXT_PERF_REVIEW_DATE DATE := '01-NOV-2015';

-- Output Variables

V_OBJECT_VERSION_NUMBER NUMBER;

V_NEXT_REVIEW_DATE_WARNING BOOLEAN;

begin

fnd_global.apps_initialize(23248,52441,800 );

-- Calling API HR_PERF_REVIEW_API.CREATE_PERF_REVIEW

HR_PERF_REVIEW_API.CREATE_PERF_REVIEW (

P_VALIDATE => V_VALIDATE ,

P_PERFORMANCE_REVIEW_ID => V_PERFORMANCE_REVIEW_ID ,

P_PERSON_ID => V_PERSON_ID ,

P_EVENT_ID => V_EVENT_ID ,

P_REVIEW_DATE => V_REVIEW_DATE ,

P_PERFORMANCE_RATING => V_PERFORMANCE_RATING ,

P_NEXT_PERF_REVIEW_DATE => V_NEXT_PERF_REVIEW_DATE

--,P_ATTRIBUTE_CATEGORY => V_ATTRIBUTE_CATEGORY

,P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER ,

P_NEXT_REVIEW_DATE_WARNING => V_NEXT_REVIEW_DATE_WARNING

);

dbms_output.put_line('V_PERFORMANCE_REVIEW_ID : ' || V_PERFORMANCE_REVIEW_ID);

dbms_output.put_line('V_OBJECT_VERSION_NUMBER : ' || V_OBJECT_VERSION_NUMBER);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('error : ' || sqlerrm);

END;

CREATE or replace package Apps.XX_HR_PERFORMANCE_PKG AS
/*
*************************************************************************
** XX Performance Review Load by Apps Associates
*************************************************************************
FILE_NAME         : XX_HR_PERFORMANCE_PKG.pks
File Type         : PACKAGE SPECIFICATION
PURPOSE           : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.
Created   by       : Jithendra
Creation Date       : 17-Sep-2014
Updated by       :
Updation Date       :
VERSION           : 1.0
Comments           :
***************************************************************************/

PROCEDURE XX_PERF_REVIEW_LOAD_PROC(
                           X_ERRBUF              OUT VARCHAR2,
                           X_RETCODE             OUT NUMBER,
                           V_PATH_LOCATION in varchar2,
                           v_file_name varchar2,
                           V_REVIEW_DATE in varchar2,
                           V_NEXT_PERF_REVIEW_DATE in varchar2
                            );
end XX_HR_PERFORMANCE_PKG;

/

CREATE or replace package body Apps.XX_HR_PERFORMANCE_PKG AS
/*
*************************************************************************
** XX Performance Review Load by Apps Associates
*************************************************************************
FILE_NAME         : XX_HR_PERFORMANCE_PKG.pkb
File Type         : PACKAGE body
PURPOSE           : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.
Created   by       : Jithendra
Creation Date       : 17-Sep-2014
Updated by       :
Updation Date       :
VERSION           : 1.0
Comments           :
***************************************************************************/

PROCEDURE XX_PERF_REVIEW_LOAD_PROC (
                           X_ERRBUF              OUT VARCHAR2,
                           X_RETCODE             OUT NUMBER,
                           V_PATH_LOCATION in varchar2,
                           v_file_name varchar2,
                           V_REVIEW_DATE in varchar2,
                           V_NEXT_PERF_REVIEW_DATE in varchar2
                            )
as
    V_FILE_TYPE UTL_FILE.FILE_TYPE;
    V_LINE varchar2 (1000);
    V_EMPLOYEE_NUMBER number;
    V_BUSINESS_GROUP   varchar2(200);
    V_LAST_NAME     varchar2(200);
    V_FIRST_NAME   varchar2(200);
    V_PERF_RATING_NAME varchar2(30);
    V_PERF_RATING_CODE VARCHAR2(30);
    V_REC_COUNT number := 0;
    V_VALIDATE                      BOOLEAN;
    V_PERSON_ID                     number ;
    V_PERFORMANCE_REVIEW_ID         NUMBER;
    V_OBJECT_VERSION_NUMBER         NUMBER;
    V_NEXT_REVIEW_DATE_WARNING      BOOLEAN;
   L_NEXT_REVIEW_DATE date;
   V_TOT_count Number :=0;
   v_SUC_count Number :=0;
   V_FAIL_Count Number :=0;
BEGIN

fnd_file.put_line(fnd_file.output,'File Name:' ||V_PATH_LOCATION||'/'||v_file_name);
fnd_file.put_line(fnd_file.output,'Review Date:' ||V_REVIEW_DATE);
fnd_file.put_line(fnd_file.output,'Next Review Date:' ||V_NEXT_PERF_REVIEW_DATE);

V_FILE_TYPE := UTL_FILE.FOPEN (v_path_location, v_file_name, 'R');

if V_NEXT_PERF_REVIEW_DATE = null
then
L_NEXT_REVIEW_DATE := null;
else
L_NEXT_REVIEW_DATE := TO_DATE(V_NEXT_PERF_REVIEW_DATE,'dd-mon-yyyy');
end if;

IF UTL_FILE.IS_OPEN(V_FILE_TYPE) THEN
    LOOP
--   fnd_file.put_line(fnd_file.output,'start/repeat :- '||V_REC_COUNT);
       BEGIN

           begin
               UTL_FILE.GET_LINE(V_FILE_TYPE, V_LINE);
               V_TOT_count := V_TOT_count +1;
               Exception when no_data_found THEN
                   fnd_file.put_line(fnd_file.log,'No More Reccords ' ||V_REC_COUNT);
                   UTL_FILE.FCLOSE(V_FILE_TYPE);
                   EXIT;
           END;
           if V_REC_COUNT = 0 then
               fnd_file.put_line(fnd_file.log,'skipiping the first line to read values '||V_LINE);
               V_REC_COUNT := V_REC_COUNT+1;

           else

               V_REC_COUNT := V_REC_COUNT+1;

           --   fnd_file.put_line(fnd_file.output,'Rec :- '||V_LINE);

               v_employee_number := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
               --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_employee_number : '|| V_EMPLOYEE_NUMBER);

               v_business_group := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
               --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_business_group : '||V_BUSINESS_GROUP);

               v_last_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
               --fnd_file.put_line(fnd_file.output,'v_last_name : '||v_last_name);

               v_first_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
               --fnd_file.put_line(fnd_file.output,'v_first_name : '||v_first_name);

               --V_PERF_RATING_NAME := nvl(trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5)),trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4)));
               V_PERF_RATING_NAME := trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5));


               --fnd_file.put_line(fnd_file.output,'V_PERF_RATING_NAME : '||V_PERF_RATING_NAME);

               select papf.PERSON_ID , DECODE(V_PERF_RATING_NAME ,'1 - Lowest' , 'XX_1',
                                              '2' , 'XX_2',
                                               '3', 'XX_3',
                                              '4' , 'XX_4'
                                               ,'5 - Highest' ,'XX_5')
               into V_PERSON_ID, V_PERF_RATING_CODE
               from PER_ALL_PEOPLE_F papf, per_business_groups pbg
               where EMPLOYEE_NUMBER = V_EMPLOYEE_NUMBER
               and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
               AND papf.business_group_id=pbg.business_group_id
               and pbg.name=v_business_group;

               --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_person_id :'||V_PERSON_ID||', V_PERF_RATING_CODE :'||V_PERF_RATING_CODE);

               HR_PERF_REVIEW_API.CREATE_PERF_REVIEW( P_VALIDATE                     => V_VALIDATE
                                                      ,P_PERFORMANCE_REVIEW_ID        => V_PERFORMANCE_REVIEW_ID
                                                      ,P_PERSON_ID                    => V_PERSON_ID
                                                      ,P_EVENT_ID                     => null
                                                      ,P_REVIEW_DATE                  => TO_DATE(V_REVIEW_DATE,'dd-mon-yyyy')
                                                      ,P_PERFORMANCE_RATING           => V_PERF_RATING_CODE
                                                      ,P_NEXT_PERF_REVIEW_DATE        => L_NEXT_REVIEW_DATE
                                                      ,P_OBJECT_VERSION_NUMBER        => V_OBJECT_VERSION_NUMBER
                                                      ,P_NEXT_REVIEW_DATE_WARNING     => V_NEXT_REVIEW_DATE_WARNING
                                                      );



           --   FND_FILE.PUT_LINE(FND_FILE.log,V_LINE);
               --rollback;
               v_SUC_count := v_SUC_count +1;
               --COMMIT;
           end if;
      EXCEPTION when OTHERS then
        --fnd_file.put_line(fnd_file.log,'V_PERF_RATING_NAME'||V_PERF_RATING_NAME);
       --fnd_file.put_line(fnd_file.log,V_LINE);
        fnd_file.put_line(fnd_file.log,'Failed - '||V_EMPLOYEE_NUMBER||' with Error :'||sqlerrm);
        --EXIT;
       --continue;
       V_FAIL_Count := V_FAIL_Count +1;
      end ;

    END LOOP;
end if;
--rollback;
commit;
V_TOT_count:=V_TOT_count-1;
        fnd_file.put_line(fnd_file.output,'Total No of records in file : '||V_TOT_count);
       fnd_file.put_line(fnd_file.output,'Failed records count: '||V_FAIL_Count);
       fnd_file.put_line(fnd_file.output,'Successful records count: '||V_Suc_Count);

EXCEPTION when OTHERS then
        FND_FILE.PUT_LINE(FND_FILE.log,'Other errors :'||SQLERRM);
        UTL_FILE.FCLOSE(V_FILE_TYPE);
END XX_PERF_REVIEW_LOAD_PROC;
end XX_HR_PERFORMANCE_PKG;

Oracle HRMS API – Update Employee Assignment

/*
To Update: Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Gre, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id

API -- hr_assignment_api.update_emp_asg

To Update: Grade, Location, Job, Payroll, Organization, Employee Category, People Group

API -- hr_assignment_api.update_emp_asg_criteria
*/

--Example --

DECLARE
   -- Local Variables
   -- -----------------------
   LC_DT_UD_MODE          VARCHAR2(100)     := NULL;
   LN_ASSIGNMENT_ID       NUMBER            := 9375;
   LN_SUPERVISOR_ID       NUMBER            := NULL;
   LN_OBJECT_NUMBER       NUMBER            := 1;
   ln_people_group_id NUMBER               := 1;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                               BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;

   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id      HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   LN_COMMENT_ID                  PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning         BOOLEAN;

-- Out Variables for Update Employee Assgment Criteria
-- -------------------------------------------------------------------------------
ln_special_ceiling_step_id           PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
lc_group_name                         VARCHAR2(30);
ld_effective_start_date               PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
LD_EFFECTIVE_END_DATE                 PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
lb_org_now_no_manager_warning           BOOLEAN;
lb_other_manager_warning                BOOLEAN;
lb_spp_delete_warning                   BOOLEAN;
lc_entries_changed_warning              VARCHAR2(30);
lb_tax_district_changed_warn            BOOLEAN;

BEGIN
   -- Find Date Track Mode
   -- --------------------------------
   dt_api.find_dt_upd_modes
   (    p_effective_date                  => TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value            => ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert
     );

   IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
   THEN
       -- UPDATE_OVERRIDE
       -- ---------------------------------
       lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;

IF ( lb_correction = TRUE )
THEN
      -- CORRECTION
      -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
END IF;

IF ( lb_update = TRUE )
THEN
      -- UPDATE
      -- --------------
      lc_dt_ud_mode := 'UPDATE';
   END IF;

-- Update Employee Assignment
-- ---------------------------------------------
hr_assignment_api.update_emp_asg
( -- Input data elements
-- ------------------------------
p_effective_date                              => TO_DATE('12-JUN-2011'),
p_datetrack_update_mode         => lc_dt_ud_mode,
p_assignment_id                            => ln_assignment_id,
p_supervisor_id                              => NULL,
p_change_reason                           => NULL,
p_manager_flag                              => 'N',
p_bargaining_unit_code              => NULL,
p_labour_union_member_flag   => NULL,
p_segment1                                       => 204,
p_segment3                                       => 'N',
p_normal_hours                              => 10,
p_frequency                                       => 'W',
-- Output data elements
-- -------------------------------
p_object_version_number             => ln_object_number,
p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
p_concatenated_segments             => lc_concatenated_segments,
p_comment_id                                   => ln_comment_id,
p_effective_start_date                      => ld_effective_start_date,
p_effective_end_date                        => ld_effective_end_date,
p_no_managers_warning               => lb_no_managers_warning,
p_other_manager_warning            => lb_other_manager_warning
);

-- Find Date Track Mode for Second API
-- ------------------------------------------------------
dt_api.find_dt_upd_modes
( p_effective_date                   => TO_DATE('12-JUN-2011'),
     p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
     p_base_key_column           => 'ASSIGNMENT_ID',
     p_base_key_value               => ln_assignment_id,
     -- Output data elements
     -- -------------------------------
     p_correction                           => lb_correction,
     p_update                                 => lb_update,
     p_update_override               => lb_update_override,
     p_update_change_insert    => lb_update_change_insert
);

IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
THEN
    -- UPDATE_OVERRIDE
    -- --------------------------------
    lc_dt_ud_mode := 'UPDATE_OVERRIDE';
END IF;

   IF ( lb_correction = TRUE )
   THEN
     -- CORRECTION
     -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
END IF;

   IF ( lb_update = TRUE )
   THEN
     -- UPDATE
     -- --------------
     lc_dt_ud_mode := 'UPDATE';
   END IF;

-- Update Employee Assgment Criteria
-- -----------------------------------------------------
hr_assignment_api.update_emp_asg_criteria
( -- Input data elements
-- ------------------------------
p_effective_date                                   => TO_DATE('12-JUN-2011'),
p_datetrack_update_mode               => lc_dt_ud_mode,
p_assignment_id                                 => ln_assignment_id,
p_location_id                                        => 204,
p_grade_id                                             => 29,
p_job_id                                                  => 16,
p_payroll_id                                          => 52,
p_organization_id                               => 239,
p_employment_category                    => 'FR',
-- Output data elements
-- -------------------------------
p_people_group_id                              => ln_people_group_id,
p_object_version_number                   => ln_object_number,
p_special_ceiling_step_id                  => ln_special_ceiling_step_id,
p_group_name                                        => lc_group_name,
p_effective_start_date                           => ld_effective_start_date,
p_effective_end_date                             => ld_effective_end_date,
p_org_now_no_manager_warning => lb_org_now_no_manager_warning,
p_other_manager_warning                 => lb_other_manager_warning,
p_spp_delete_warning                         => lb_spp_delete_warning,
p_entries_changed_warning              => lc_entries_changed_warning,
p_tax_district_changed_warning     => lb_tax_district_changed_warn
);

COMMIT;

EXCEPTION
         WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
END;
/

Tuesday, September 16

Scheduling the Discoverer reports

Scheduling the Discoverer reports

Scheduling the Discoverer reports can be done only through Discoverer Plus /Desktop and the scheduled reports can be shared to users so that they can see the results from Application.

As the end users may not have access to Discoverer plus / Desktop, Tech team or DBA's have to schedule the reports and to share to the respective users who are looking for the scheduled results.

Prerequisites to schedule the discoverer reports:

user should have Scheduling access to schedule the reports from Discoverer plus/Discoverer Desktop

Steps to Verify and Enable the required Privileges :

Step1: Login to Discoverer Administrator as sysadmin

Step2: Choose the responsibility "System Administrator"

Step3: Open an Existing Business Area "IDCO UK Contract and Receivables"

Step4 : Go to Tools Menu --> Privileges

Step5: Click on Select button on Privileges tab and search for the username "SYSADMIN" and click on "Go"

Step6: "Schedule Workbooks" Option on "Desktop and Plus Privilege" should be checked. if this was not checked, please check the check box and click on Apply.

Step7: Now navigate to tab "Scheduled workbooks" on same window.

Step8:

1. Select the 3rd Radio button "Schedule Only if predicted time Exceeds (HH:MM:SS)"

2. Set any time (ex: 5 mins i.e, 00:05:00)

3. uncheck the "Limit number of scheduled workbooks" and time

4. the other options can be choose as per requirement.

Click Apply and OK.

Schedule the Discoverer Reports :

Step1: Login to discoverer plus as syadmin ( make sure to chose the connect to as "Oracle Applications") and select the responsibility "System Administrator"

Step2 : Navigate to Tools menu --> Manage Schedules

Step3: Click on Schedule and select the workbook. (here I am selecting the workbook ' IDCO UK 7 Buckets Aging Report ')

Step4:

1. Enter the name for scheduled work book, (this should be unique, if you have entered any name that was being used by any other user, it will ask you to rename.)

2. Select the work sheets to be run on the work book and click on Next

Step5: Enter the Parameters and click on Next.

Step 6: Schedule the report as per your requirement and Finish. (for demo, I am choosing to run the report immediately and repeat for every minute and selecting the option 'yes, Keep all results' and delete results after 7 days)

Step7: Click on share and choose all the users to access this scheduled workbook and click ok.

The scheduled reports can be unscheduled or deleted from the same window.

View the results from Application :

Step1: Login to application as end user and open any discoverer report as they do daily.

Step2: Instead of running the report select the link "workbook" on the top and choose scheduled workbooks from the lov and click on GO .

Here user can see all the scheduled reports that are shared to him.

in this example I have scheduled only one report to run for every minute , we can schedule n number of reports and also one report with multiple parameters.