API to Create Organization Manager

API to Create Organization Manager

/* Formatted on 2017/10/02 12:34 (Formatter Plus v4.8.8) */
DECLARE
   v_org_information_id      NUMBER;
   v_object_version_number   NUMBER;
   v_warning                 BOOLEAN;
   l_start_date              DATE;
   l_org_id                  NUMBER;
   l_person_id               NUMBER;
   l_error                   VARCHAR2 (2500);

   CURSOR cur_ee
   IS
      SELECT *
        FROM xxkq_org_manager_api_stg
       WHERE status IS NULL AND sno <= 1;
BEGIN
   FOR c1_ee_rec IN cur_ee
   LOOP
      BEGIN
         SELECT papf.person_id, paaf.effective_start_date
           INTO l_person_id, l_start_date
           FROM per_all_people_f papf, per_all_assignments_f paaf
          WHERE 1 = 1
            AND papf.person_id = paaf.person_id
            AND papf.employee_number = c1_ee_rec.org_owner_staff_no
            AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                    AND papf.effective_end_date
            AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                    AND paaf.effective_end_date;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := 'Invalid Emp Details' || SQLERRM;
      END;

      BEGIN
         SELECT organization_id
           INTO l_org_id
           FROM hr_all_organization_units
          WHERE NAME = TRIM (c1_ee_rec.NAME);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := 'Invalid Org Details' || SQLERRM;
      END;

      IF l_error IS NULL
      THEN
         BEGIN
            hr_organization_api.create_org_manager
                     (p_validate                   => FALSE,
                      p_effective_date             => l_start_date,
                      p_organization_id            => l_org_id,
                      p_org_info_type_code         => 'Organization Name Alias',
                      p_org_information2           => l_person_id,
                      p_org_information3           => TO_CHAR
                                                         (l_start_date,
                                                          'YYYY/MM/DD HH24:MI:SS'
                                                         ),
                      p_org_information4           => '4712/12/31 00:00:00',
                      p_org_information_id         => v_org_information_id,
                      p_object_version_number      => v_object_version_number,
                      p_warning                    => v_warning
                     );

            UPDATE xxkq_org_manager_api_stg
               SET status = 'S'
             WHERE sno = c1_ee_rec.sno;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM;

               UPDATE xxkq_org_manager_api_stg
                  SET status = 'E',
                      error_msg = l_error
                WHERE sno = c1_ee_rec.sno;
         END;
      ELSE
         UPDATE xxkq_org_manager_api_stg
            SET status = 'E',
                error_msg = l_error
          WHERE sno = c1_ee_rec.sno;
      END IF;
   END LOOP;

   COMMIT;
END;

API to Create HR Organization and Cost Allocation Segment

API to Create HR Organization and Cost Allocation Segment

/* Formatted on 2017/10/01 21:41 (Formatter Plus v4.8.8) */
DECLARE
   p_effective_date          DATE;
   p_object_version_number   NUMBER;
   p_duplicate_org_warning   BOOLEAN;
   v_err                     VARCHAR2 (1000);
   l_org_id                  NUMBER;
   lb_record_status          BOOLEAN;
   lv_error_message          VARCHAR2 (2400);

   CURSOR c1
   IS
      SELECT *
        FROM xxkq_org_cost_stg
       WHERE 1 = 1 AND status IS NULL AND sno <= 400;
BEGIN
   FOR i IN c1
   LOOP
      lb_record_status := TRUE;
      lv_error_message := '';

      BEGIN
         SELECT org.organization_id, org.date_from, org.object_version_number
           INTO l_org_id, p_effective_date, p_object_version_number
           FROM hr_all_organization_units org
          WHERE 1 = 1 AND NAME = TRIM (i.ORGANIZATION);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_org_id := NULL;
      END;

      IF l_org_id IS NULL
      THEN
         lb_record_status := FALSE;
         lv_error_message := 'Unable to find the Organization Details';
      END IF;

      IF lb_record_status = TRUE
      THEN
         BEGIN
            hr_organization_api.update_organization
                         (p_validate                   => FALSE,
                          p_effective_date             => p_effective_date,
                          p_organization_id            => l_org_id,
                          p_segment1                   => TO_CHAR (i.company),
                          p_segment2                   => TO_CHAR (i.LOCATION),
                          p_segment3                   => TO_CHAR
                                                                (i.cost_centre),
                          p_object_version_number      => p_object_version_number,
                          p_duplicate_org_warning      => p_duplicate_org_warning
                         );

            UPDATE xxkq_org_cost_stg
               SET status = 'S',
                   error_msg = ''
             WHERE sno = i.sno;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_message := SUBSTR (SQLERRM, 1, 239);
               ROLLBACK;

               UPDATE xxkq_org_cost_stg
                  SET status = 'E',
                      error_msg = lv_error_message
                WHERE sno = i.sno;
         END;
      ELSE
         UPDATE xxkq_org_cost_stg
            SET status = 'E',
                error_msg = lv_error_message
          WHERE sno = i.sno;

         COMMIT;
      END IF;
   END LOOP;

   COMMIT;

END;

Query to get the Menu,Submenu and function details For Oracle Menus

Query to get the Menu,Submenu and function details For Oracle Menus

/* Formatted on 2017/10/13 16:26 (Formatter Plus v4.8.8) */
SELECT   SECOND.application_id "App ID", SECOND.application_name "App Name",
         SECOND.responsibility_id "Resp ID",
         SECOND.responsibility_name "Responsibility",
         SECOND.menu_id "Menu ID", SECOND.user_menu_name "Main Menu Name",
         SECOND.entry_sequence "Seq", SECOND.prompt "Prompt",
         SECOND.function_id "Function ID",
         SECOND.user_function_name "Function",
         SECOND.func_descrip "Function Descrip",
         SECOND.sub_menu_id "SubMenu ID", SECOND.sub_menu_name "SubMenu Name",
         SECOND.sub_seq "Sub Seq", SECOND.sub_prompt "SubPrompt",
         SECOND.sub_func_id "SubFunction ID", SECOND.sub_func "SubFunction",
         SECOND.sub_func_descrip "SubFunction Descrip",
         SECOND.sub_sub_menu_id "Sub-SubMenu ID",
         SECOND.grant_flag "Grant Flag", SECOND.resp_end_date "Resp End Date",
         DECODE (exc.rule_type,
                 'F', (SELECT 'Ex F: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.function_id = exc.action_id)
                ) excluded_function,
         DECODE (exc.rule_type,
                 'F', (SELECT 'Ex SF: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.sub_func_id = exc.action_id)
                ) excluded_sub_function,
         DECODE (exc.rule_type,
                 'M', (SELECT 'Ex M: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.menu_id = exc.action_id)
                ) excluded_menu,
         DECODE (exc.rule_type,
                 'M', (SELECT 'Ex SM: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.sub_menu_id = exc.action_id)
                ) excluded_sub_menu,
         DECODE (exc.rule_type,
                 'M', (SELECT 'Ex SSM: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.sub_sub_menu_id = exc.action_id)
                ) excluded_sub_sub_menu
    FROM (SELECT   FIRST.application_id, FIRST.application_name,
                   FIRST.responsibility_id, FIRST.responsibility_name,
                   FIRST.end_date AS resp_end_date, FIRST.menu_id,
                   FIRST.user_menu_name, FIRST.entry_sequence, FIRST.prompt,
                   FIRST.function_id, ffft.user_function_name,
                   ffft.description AS func_descrip, FIRST.sub_menu_id,
                   fmv2.user_menu_name AS sub_menu_name,
                   fme2.entry_sequence AS sub_seq, fmet2.prompt AS sub_prompt,
                   fme2.function_id AS sub_func_id,
                   ffft2.user_function_name AS sub_func,
                   ffft2.description AS sub_func_descrip,
                   fme2.sub_menu_id AS sub_sub_menu_id, FIRST.grant_flag
              FROM (SELECT   fat.application_id, fat.application_name,
                             fr.responsibility_id, frt.responsibility_name,
                             fr.end_date, fr.menu_id, fmv.user_menu_name,
                             fme.entry_sequence, fmet.prompt, fme.sub_menu_id,
                             fme.function_id, fme.grant_flag
                        FROM apps.fnd_application_tl fat,
                             apps.fnd_responsibility fr,
                             apps.fnd_menus_vl fmv,
                             apps.fnd_responsibility_tl frt,
                             apps.fnd_menu_entries fme,
                             apps.fnd_menu_entries_tl fmet
                       --joins and constant selection
                    WHERE    fat.application_id = fr.application_id(+)
                         AND fr.menu_id = fmv.menu_id(+)
                         AND fr.responsibility_id = frt.responsibility_id(+)
                         AND fr.menu_id = fme.menu_id(+)
                         AND fme.menu_id = fmet.menu_id(+)
                         AND fme.entry_sequence = fmet.entry_sequence(+)
                         AND fmet.LANGUAGE = 'US'
                         AND frt.responsibility_name = 'Receivables Manager'
--                     AND fat.application_id = &appid
                    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST
                         ---for application, responsibility and main menu info
                                                                         ,
                   apps.fnd_menus_vl fmv2                  ---for submenu info
                                         ,
                   apps.fnd_menu_entries fme2,
                   apps.fnd_menu_entries_tl fmet2,
                   apps.fnd_form_functions_tl ffft        ---for function info
                                                  ,
                   apps.fnd_form_functions_tl ffft2    ---for subfunction info
             --left outer joins keep original records and add any sub menu and function info
          WHERE    FIRST.function_id = ffft.function_id(+)
               AND FIRST.sub_menu_id = fmv2.menu_id(+)
               AND FIRST.sub_menu_id = fme2.menu_id(+)
               AND fme2.menu_id = fmet2.menu_id(+)
               AND fme2.entry_sequence = fmet2.entry_sequence(+)
               AND fme2.function_id = ffft2.function_id(+)
          ORDER BY 1,
                   2,
                   3,
                   4,
                   5,
                   6,
                   7,
                   8,
                   9,
                   10,
                   11,
                   12,
                   13,
                   14,
                   15,
                   16,
                   17,
                   18,
                   19,
                   20,
                   21) SECOND           -- adds any sub menu and function info
         LEFT OUTER JOIN
         apps.fnd_resp_functions exc                         ---for exclusions
         ON (    SECOND.application_id = exc.application_id
             AND SECOND.responsibility_id = exc.responsibility_id
             AND (   SECOND.function_id = exc.action_id
                  OR SECOND.sub_func_id = exc.action_id
                  OR SECOND.menu_id = exc.action_id
                  OR SECOND.sub_menu_id = exc.action_id
                  OR SECOND.sub_sub_menu_id = exc.action_id
                 )
            )
ORDER BY 1,
         2,
         3,
         4,
         5,
         6,
         7,
         8,
         9,
         10,
         11,
         12,
         13,
         14,
         15,
         16,
         17,
         18,
         19,
         20,
         21;

How to Create a Custom Form to Restrict Special Information Types (SIT)

How to Create a Custom Form to Restrict Special Information Types (SIT)

To restrict the number of Special Information Types (SIT) available to an end user.

Often SIT's can hold confidential and sensitive information, therefore this activity of restricting the types forms part of security design.


DETAILS

NAVIGATE Security > CustomForm

Form to be customized = Enter Person Special Information

In Customized version Block, Enter
Name = MYFORM
Standard Title = My SIT
Query Title = My SIT

SAVE

In restrictions block
Type = Restrict by Special Information Type Value = SIT 1
Type = Restrict by Special Information Type Value = SIT 2

SAVE

(This restricts to 2 out of the total number SIT set up)

As Systems Administrator

NAVIGATE Application > Functions
In 'Description', Enter
Function = MYFORM
User Function Name = MYFORM
Type = FORM
Description = CUSTOMIZED SIT FORM
Form = Enter Person Special Information
Application = Oracle Human Resources (defaulted from Form name above)
Parameters = HR_CUSTOMIZATION="MYFORM" (The bit in the double quotes MUST be
the same as entered the 'Name' field of block in Customform)


SAVE

NAVIGATE Application > menu

Query up menu e.g. PER_UK_HRMS_FAST

On row for Special Information replace displayed function with MYFORM (selected from Pick List)

SAVE

How to find Profile Options Attached in various levels

How to find Profile Options Attached in various levels

select 

b.user_profile_option_name "Long Name"

, a.profile_option_name "Short Name"

, decode(to_char(c.level_id),'10001','Site'

,'10002','Application'

,'10003','Responsibility'

,'10004','User'

,'Unknown') "Level"

, decode(to_char(c.level_id),'10001','Site'

,'10002',nvl(h.application_short_name,to_char(c.level_value))

,'10003',nvl(g.responsibility_name,to_char(c.level_value))

,'10004',nvl(e.user_name,to_char(c.level_value))

,'Unknown') "Level Value"

, c.PROFILE_OPTION_VALUE "Profile Value"

, c.profile_option_id "Profile ID"

, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"

, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"

from 

apps.fnd_profile_options a

, apps.FND_PROFILE_OPTIONS_VL b

, apps.FND_PROFILE_OPTION_VALUES c

, apps.FND_USER d

, apps.FND_USER e

, apps.FND_RESPONSIBILITY_VL g

, apps.FND_APPLICATION h

where 1=1

--a.application_id = nvl(401, a.application_id)

--and a.profile_option_name = nvl('INV', a.profile_option_name)

and b.user_profile_option_name like 'HR%Sec%' -- 'AFLOG_ENABLED'

and a.profile_option_name = b.profile_option_name

and a.profile_option_id = c.profile_option_id

and a.application_id = c.application_id

and c.last_updated_by = d.user_id (+)

and c.level_value = e.user_id (+)

and c.level_value = g.responsibility_id (+)

and c.level_value = h.application_id (+)

--and c.PROFILE_OPTION_VALUE=14063

order by 

b.user_profile_option_name, c.level_id, 

decode(to_char(c.level_id),'10001','Site'

,'10002',nvl(h.application_short_name,to_char(c.level_value))

,'10003',nvl(g.responsibility_name,to_char(c.level_value))

,'10004',nvl(e.user_name,to_char(c.level_value))

,'Unknown');

Absence Action History Details Script

Absence Approval Action History Script:


SELECT hats.information1, hats.information2, hats.information6,
       hats.information8, psa.user_name, psa.action
  FROM hr_api_transactions hat,
       hr_api_transaction_steps hats,
       apps.wf_items wi,
       pqh_ss_approval_history psa
 WHERE hat.selected_person_id = 501
   AND hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
   AND hat.transaction_id = hats.transaction_id
   AND wi.item_key = hat.item_key
   AND wi.item_key = psa.transaction_item_key
   AND wi.item_type = 'HRSSA'
   AND end_date IS NOT NULL



Absence Pending History Sctipt:


SELECT wfn.notification_id, wi.item_key,
       TO_DATE (hats.information1, 'YYYY-MM-DD'), hats.information2,
       hats.information6, hats.information8, wfn.recipient_role, wfn.status
  FROM hr_api_transactions hat,
       hr_api_transaction_steps hats,
       apps.wf_items wi,
       apps.wf_notifications wfn
 WHERE hat.selected_person_id = 501
   AND hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
   AND hat.transaction_id = hats.transaction_id
   AND wi.item_key = hat.item_key
--and wi.item_key=psa.TRANSACTION_ITEM_KEY
   AND wi.item_type = 'HRSSA'
   AND wi.end_date IS NULL
   AND wi.item_key = wfn.item_key
   AND wfn.notification_id IN (SELECT MAX (notification_id)
                                 FROM apps.wf_notifications
                                WHERE item_key = wi.item_key)
   AND TO_DATE (hats.information1, 'YYYY-MM-DD') >= '01-JAN-2017'

Element Input Validation Fast Formula Type

Element Input Validation Fast Formula Type

Element Input Validation

This can be used when we got to validate / restrict the element input value (Date/Number). There various types through which we can enable restriction. That are,

        a) Fast Formula (Date/Money),
        b) Lookup (Character),
        c) Value Set,
        d) Default Value,
        e) Minimum & Maximum

In this article we are going to see how to restrict/validate entry values in element input value.

Scenario 1

Restrict the input value for a element called "One time Bonus" not higher than Rs: 15000 /-
Steps:

1) Write fast formula to validate / restrict desired entry value (Date/ Money) in element input value,
(Navigation: HRMS: -> Total Compensation  ->  Basic  ->  Write Formula)

1.1) Type Formula Name,
1.2) Select Type as "Element Input Validation",
1.3) Enter description for the fast formula.

2) Enter the following formula

Inputs are entry_value (text)
If to_num (entry_value) > 15000
Then
(Formula_Status = 'e'
Formula_Message = 'Value Entered is above the limit')
Else
(Formula_Status = 's'
Formula_Message = 'Fine')
Return Formula_Status, Formula_Message

3) Define an element,
(Navigation: HRMS  -> Total Compensation -> Basic Element Description)

4) Enter input values for the element

4.1) Select "Money" in units,
4.2) Select the formula from the  LOV in the formula field.

5) Testing: try entering values within & above 15000 in element entries
(Navigation: People -> Enter & Maintain -> "Assignment"  ->  Entries  –  Entry Values)


Scenario 2

Restrict Entry date not prior to system date (Can be Today & Tomorrow Onwards)

1) Wirte fast formula to validate element input date,
(Navigation: HRMS:-> Total Compensation -> Basic -> Write Formula)

         1.1) Type Formula Name,
         1.2) Select Type as "Element Input Validation",
         1.3) Enter description for the fast formula.

2) Enter the following formula;

       Default for session_date IS '1900/01/01 00:00:00' (date)
        Inputs are entry_value(text)
        If(to_date(entry_value,'YYYY/MM/DD HH24:MI:SS') < session_date) then
       (formula_status ='e'
        formula_message ='Enter future date')
       else
      (formula_status='s')
      Return formula_status,formula_message

3) Define an element,
      (Navigation: HRMS -> Total Compensation -> Basic Element Description)

4) Enter input values for the element
        4.1) Select "Money" in units,
        4.2) Select the formula from the LOV in the formula field.

5) Testing: try entering date less than, equal to & greater than the session date.
(Navigation: People -> Enter & Maintain -> "Assignment" -> Entries – Entry Values)


Some Fast Formula Tables:   SQL Query to select tables:-

Select * from all_objects where object_type=’table’ and object_name like ‘ff%';
ff_functions
ff_function_parameters
ff_formulas_f
ff_formula_types
ff_database_items
ff_globals_ff