Social Icons

Featured Posts

Monday, April 27, 2020

Users and responsibilities in Oracle EBS

SELECT DISTINCT fu.user_name,
                TO_CHAR (fu.creation_date, 'DD-MON-YYYY') creation_date,
                papf.GLOBAL_NAME employee_name, papf.employee_number,
                papf.email_address,
                houa.name department,
                houa.NAME, houa.TYPE, frt.responsibility_name,
                fat.application_name,
                papf1.GLOBAL_NAME manager_name,
                papf1.employee_number manager_number,
                papf1.email_address manager_email_address,               
                --frt.DESCRIPTION responsibility_Description,
                TO_CHAR (furgd.start_date, 'DD-MON-YYYY') start_date,
                TO_CHAR (furgd.end_date, 'DD-MON-YYYY') end_date
           FROM apps.fnd_user fu,
                fnd_user_resp_groups_direct furgd,
                apps.fnd_responsibility_tl frt,
                per_all_people_f papf,
                per_all_assignments_f paaf,
                hr_all_organization_units houa,
                per_all_people_f papf1,
                 fnd_application_tl fat
          WHERE fu.user_id = furgd.user_id
            AND furgd.responsibility_id = frt.responsibility_id
            AND fu.employee_id = papf.person_id
            AND papf.person_id = paaf.person_id
            AND paaf.supervisor_id = papf1.person_id
            --and frt.application_id=101
            AND papf.current_employee_flag = 'Y'
            AND papf.business_group_id = 81
            AND papf1.current_employee_flag = 'Y'
            and paaf.primary_flag='Y'
            AND papf1.business_group_id = 81
             and frt.APPLICATION_ID=fat.APPLICATION_ID
            and fat.language='US'
            AND paaf.organization_id = houa.organization_id
            AND SYSDATE BETWEEN papf.effective_start_date
                            AND papf.effective_end_date
            AND SYSDATE BETWEEN papf1.effective_start_date
                            AND papf1.effective_end_date
            AND SYSDATE BETWEEN paaf.effective_start_date
                            AND paaf.effective_end_date
            --AND furgd.end_date IS NULL
            AND fu.end_date IS NULL
            AND furgd.end_date IS NULL
            --and papf.employee_number='1051'
                     /*   and papf.PERSON_ID in (Select Distinct person_id from apps.XXCBO_STAFF_WITH_DEPARTMENT
            where  department in ('Information Technology Department'))*/
            AND frt.LANGUAGE = 'US'
       ORDER BY 4

Sunday, April 26, 2020

Training profile history query in oracle learning management

/* Formatted on 12/4/2018 9:29:10 AM (QP5 v5.294) */
  SELECT DISTINCT
         employee_number                       "Staff Number",
         LTRIM (
               hr_general.decode_lookup ('TITLE', papf.title)
            || ' '
            || papf.first_name
            || ' '
            || papf.middle_names
            || ' '
            || papf.last_name)
            "Staff Name",
         HR_GENERAL.DECODE_GRADE (PAAF.GRADE_ID)"Grade"-- ,hr_general.DECODE_ORGANIZATION(paaf.organization_id) Department
         ,
         haou.attribute1                       "Department",
         oe.title                              "Course Title",
         oe.evt_information1                   "Vendor",
         oe.course_start_date                  "Course Start Date",
         oe.course_end_date                    "Course End Date",
         oe.evt_information2                   "Venue",
         oe.evt_information3                   "Institute University",
         oe.evt_information4                   "Training type",
         oe.evt_information5                   "remarks",
         odb.event_id,
         ODB.BOOKING_ID
    FROM ota_delegate_bookings   odb,
         ota_events              oe,
         ota_booking_status_types obst,
         per_all_people_f        papf,
         per_all_assignments_f   paaf,
         hr_all_organization_units haou
   WHERE     1 = 1
         AND paaf.organization_id = haou.organization_id
         AND odb.event_id = oe.event_id
         AND odb.booking_status_type_id = obst.booking_status_type_id
         AND odb.delegate_person_id = papf.person_id
         AND papf.person_id = paaf.person_id
         AND TO_DATE (oe.course_end_date) BETWEEN papf.effective_start_date
                                              AND papf.effective_end_date
         AND TO_DATE (oe.course_end_date) BETWEEN paaf.effective_start_date
                                              AND paaf.effective_end_date
         AND paaf.primary_flag = 'Y'
         --and papf.person_id =nvl( :p_employee_number,papf.person_id)
        and papf.employee_number='0987'
         --AND TO_CHAR(oe.course_start_date,'YYYY')=to_char(to_date(:P_DATE),'YYYY')
         /*and to_date(oe.course_start_date)>=to_date(:P_STARTDATE)
         and to_date(oe.course_end_date)<=to_date(:P_DATE) */
         --AND haou.attribute1 = NVL (:P_DEPARMENT, haou.attribute1)
ORDER BY 1, oe.course_start_date
">