Unable to Enter / View Value Set Values in Oracle EBS R12.2.7 or Not Able to Enter Values in Value Sets

In R12.2 value set security has enhanced. By default no body can enter or view values of Value Sets. To enable the user who can enter or view values in value sets need to perform the following steps.

1) Need to login as SYSADMIN
2) Select the "User Management" responsibility
3) Navigate to "User"
4) Find the particular user then click on update icon
5) Click on "Assign Role"
6) Select "Role" as search by and enter value as "Flexfield Value Set Security: All privileges"
or
Select "Code" as search by and enter value as"UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE"
7) Select the role from result
8) Enter a justification text.
9) Click on "Save" and "Apply"
10) Login as the user(applied for role assignment) and check the same

Clear the cache from functional administrator responsibility if required. 

QUERY TO FIND RESPONSIBILITIES ATTACHED TO A USER IN ORACLE R12

SELECT * FROM FND_USER WHERE user_name LIKE 'TEST';

SELECT *
  FROM FND_USER_RESP_GROUPS
 WHERE user_id=1180; --user_id is the link between the FND_USER and the FIND_USER_RESP_GROUPS

SELECT *
  FROM FND_RESPONSIBILITY_VL
 WHERE responsibility_id=50621; --responsibility_id is the link between the FND_RESPONSIBILITY_VL and FND_USER_RESP_GROUPS

-- The query which displays the user name and his responsibility_name will be

SELECT fu.user_name, ppx.employee_number, ppx.full_name,
       frv.responsibility_name,
        fat.APPLICATION_NAME
  FROM fnd_user fu,
       fnd_user_resp_groups furg,
       fnd_responsibility_vl frv,
       per_people_x ppx,
       fnd_application_tl fat
 WHERE fu.user_id = furg.user_id
   AND furg.responsibility_id = frv.responsibility_id
   AND fu.employee_id = ppx.person_id(+)
   and furg.RESPONSIBILITY_APPLICATION_ID=fat.APPLICATION_ID
   and fu.user_name like 'TEST'

How To Enable The Costing Tab On The Organizational Payment Method

How To Enable The Costing Tab On The Organizational Payment Method

Run the following query to Enable Costing Tab in Organizational Payment Method

Note: Provide Proper Legislation Code before running the Query

INSERT INTO pay_legislative_field_info (field_name,

                                        legislation_code,

                                        rule_mode,

                                        rule_type,

                                        validation_type,

                                        validation_name,

                                        target_location)

  VALUES   ('COSTING_TAB',

            'SA', -- Legislation Code

            'Y',

            'DISPLAY',

            'TAB_PAGE_PROPERTY',

            'DISPLAY',

            'PAYWSDPM')

COMMIT;

Employee Contact EIT API Script

Employee Contact EIT API Script

DECLARE
   o_object_version_number   NUMBER;
   o_person_extra_info_id    NUMBER;
   l_person_id               NUMBER;
   l_error                   VARCHAR2 (4000 BYTE);
   v_contact_extra_info_id   NUMBER;
   v_object_version_number   NUMBER;
   v_effective_start_date    DATE;
   v_effective_end_date      DATE;

   CURSOR c1
   IS
      SELECT *
        FROM xx_act_emp_con_passport
       WHERE status = 'Y' AND sno <= 50000;
BEGIN
   FOR c1_rec IN c1
   LOOP
      v_object_version_number := NULL;
      v_contact_extra_info_id := NULL;
      l_person_id := NULL;
      l_error := NULL;

      IF c1_rec.contact_relationship_id IS NOT NULL
      THEN
         BEGIN
            hr_contact_extra_info_api.create_contact_extra_info
               (p_validate                      => FALSE,
                p_effective_date                => c1_rec.contact_start_date,
                p_contact_relationship_id       => c1_rec.contact_relationship_id,
                p_information_type              => 'XX_HR_CONTACT_PASSPORT',
                p_cei_information_category      => 'XX_HR_CONTACT_PASSPORT',
                p_cei_information1              => c1_rec.passport_number,
                p_cei_information2              => c1_rec.passport_issue_location,
                p_cei_information3              => TO_CHAR(TO_DATE(c1_rec.passport_issue_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD'),
                p_cei_information4              => TO_CHAR(TO_DATE(c1_rec.passport_expire_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD'),
                p_cei_information5              => TRIM (c1_rec.contact_name),
                p_contact_extra_info_id         => v_contact_extra_info_id,
                p_object_version_number         => v_object_version_number,
                p_effective_start_date          => v_effective_start_date,
                p_effective_end_date            => v_effective_end_date
               );

            UPDATE xx_act_emp_con_passport
               SET status = 'S'
             WHERE sno = c1_rec.sno;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := l_error || SQLERRM;

               UPDATE xx_act_emp_con_passport
                  SET status = 'E',
                      remarks = l_error
                WHERE sno = c1_rec.sno;
         END;
      END IF;
   END LOOP;

   COMMIT;
END;

EBS: Learning Management Query

EBS: Learning Management Query
To get the Oracle Learning Management details.


  SELECT   oe.title event_name,
           TO_CHAR (oe.course_start_date, 'DD-Mon-RRRR') stdate,
           TO_CHAR (oe.course_end_date, 'DD-Mon-RRRR') enddate,
           oe.course_start_time stime,
           oe.course_end_time etime,
           papf.email_address emp_mail,
           papf.full_name emp_name,
           papf1.email_address sup_mail,
           papf1.full_name sup_name,
           oe.evt_information2 class_location
    FROM   ota_events oe,
           ota_delegate_bookings odb,
           per_all_people_f papf,
           per_all_assignments_f paaf,
           per_all_people_f papf1
   WHERE       1 = 1
           AND odb.event_id(+) = oe.event_id
           AND papf.person_id = odb.delegate_person_id
           AND odb.internal_booking_flag = 'Y'
           AND paaf.primary_flag = 'Y'
           AND papf.current_employee_flag = 'Y'
           AND paaf.person_id = papf.person_id
           AND papf1.person_id = paaf.supervisor_id
           AND papf1.current_employee_flag = 'Y'
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND  paaf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
                                   AND  papf1.effective_end_date
ORDER BY   1;

EBS: Employee Performance Query

EBS: Employee Performance Query
To get the Employee Performance Band.



  SELECT   DISTINCT papf.employee_number,
                    papf.full_name,
                    ppr.RATING_MEANING,
                    ppr.review_date,
                    hrv.name
    FROM   PER_ALL_PEOPLE_F papf,
           PER_all_ASSIGNMENTS_F paaf,
           PER_PERFORMANCE_REVIEWS_V ppr,
           PER_PERIODS_OF_SERVICE_V pps,
           HR_ORGANIZATION_UNITS_V hrv
   WHERE       1 = 1
           AND papf.person_id = ppr.person_id
           AND PPS.PERSON_ID = papf.person_id
           AND Paaf.PERSON_ID = papf.person_id
           AND HRV.ORGANIZATION_ID = paaf.ORGANIZATION_ID
           AND PPS.ACTUAL_TERMINATION_DATE IS NULL
           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
           AND TO_CHAR (ppr.review_date, 'YYYY') = '2014'
--and  to_number(ppr.RATING_MEANING) between 0 and  0.8    -
ORDER BY   papf.employee_number

Organization Manager Script

Organization Manager Script

select

    org_tl.name                                                         "Organization",

    ppf.full_name                                                       "Manager",

    fnd_date.canonical_to_date (org_info2.org_information3)             "Start Date",

    fnd_date.canonical_to_date (org_info2.org_information4)             "End Date",

    --

    org.organization_id,

    org.business_group_id,

    ppf.person_id

from

    hr_all_organization_units org,

    hr_all_organization_units_tl org_tl,

    hr_organization_information org_info2,

    per_all_people_f ppf

where

    1=1

and org_info2.organization_id = org.organization_id

and org_info2.org_information_context = 'Organization Name Alias'

and org_info2.org_information2 = to_char (ppf.person_id(+))

and org.organization_id = org_tl.organization_id

and org_tl.language = userenv ('LANG')

and exists (select null

    from hr_org_info_types_by_class oitbc, hr_organization_information org_info

    where org_info.organization_id = org.organization_id

    and org_info.org_information_context = 'CLASS'

    and org_info.org_information2 = 'Y'

    and oitbc.org_classification = org_info.org_information1

    and oitbc.org_information_type = 'Organization Name Alias')

and (decode(hr_security.view_all,

        'Y', 'TRUE',

        hr_security.show_record('HR_ALL_ORGANIZATION_UNITS',org.organization_id)) = 'TRUE'

    and decode (hr_general.get_xbg_profile,

            'Y', org.business_group_id,

            hr_general.get_business_group_id) = org.business_group_id

    or decode (hr_security.view_all,

            'Y', 'TRUE',

            hr_security.show_record ('PER_ALL_PEOPLE_F',

                               ppf.person_id,

                               ppf.person_type_id,

                               ppf.employee_number,

                               ppf.applicant_number)) = 'TRUE'

        and decode(hr_general.get_xbg_profile,

                'Y', ppf.business_group_id,

                hr_general.get_business_group_id) = ppf.business_group_id)

--

--and org_tl.name = 'Sales'

order by

    1,2