Query: Events and Booking

select
    hr.meaning                                              "Type",
    pe.date_start                                           "Date:Start",
    pe.date_end                                             "Date: End",
    pe.time_start                                           "Time:Start",
    pe.time_end                                             "Time:End",
    hotl.name                                               "Org Run By",
    hltl.location_code                                      "Location",
    hl.timezone_code                                        "TimeZone",
    pe.external_contact                                     "External Contact: Name",
    pe.contact_telephone_number                             "External contact:Telephone",
    pap.full_name                                           "Internal Contact:Name",
    nvl (pap.employee_number, pap.npw_number)               "Internal Contact:Number",
    (select full_name
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Bookings Name",
    (select employee_number
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Worker Number",
    (select applicant_number
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Applicant Number",
    --
    pe.event_id,
    pe.business_group_id,
    pe.assignment_id
    --
from
    per_events pe,
    per_bookings pb,
    per_all_people_f pap,
    hr_lookups hr,
    hr_locations_all_tl hltl,
    hr_locations_no_join hl,
    hr_all_organization_units ho,
    hr_all_organization_units_tl hotl
where  
    1=1
and ho.organization_id = hotl.organization_id(+)
and pe.organization_run_by_id = ho.organization_id(+)
and pe.event_or_interview = 'E'
and pe.type = hr.lookup_code
and pe.event_id=pb.event_id
and hr.lookup_type =decode (pe.emp_or_apl, 'E', 'EMP_EVENT_TYPE','A', 'APL_EVENT_TYPE')
and pe.location_id = hl.location_id(+)
and pe.internal_contact_person_id = pap.person_id(+)
and pe.date_start between nvl (pap.effective_start_date, pe.date_start)
and nvl (pap.effective_end_date, pe.date_start)
and decode (hotl.organization_id, null, '1', hotl.language) = decode (hotl.organization_id, null, '1', userenv ('LANG'))
and hl.location_id = hltl.location_id(+)
and decode (hltl.location_id, null, '1', hltl.language) =decode (hltl.location_id, null, '1', userenv ('LANG'))
--
and hr.meaning = 'Orientation Day'
order by
    1,2,3,4,5

Query: Organization Manager

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

Query: People Assignment Salary - History

select
    ppf.employee_number                                                     "Employee Number",
    --
    ppp.change_date                                                         "From" ,
    decode (ppp.date_to,hr_general.end_of_time, to_date (null),ppp.date_to) "To",
    nvl(ppp.proposed_salary_n,0)-(lag(ppp.proposed_salary_n)
        over (order by ppp.change_date))                                    "Change Amount",
    round(((nvl(ppp.proposed_salary_n,0)-(lag(ppp.proposed_salary_n)
        over (order by ppp.change_date)))/ppp.proposed_salary_n)*100,0)     "Change %",
    ppp.proposed_salary_n                                                   "Salary",
    (ppp.proposed_salary_n*12)                                              "Annualized Salary",
    pet.input_currency_code                                                 "Currency",
    ppb.name                                                                "Salary Basis",
    hr_general.decode_lookup ('PER_SAL_PROPOSAL_STATUS',ppp.approved)       "Status",
    --
    ppp.pay_proposal_id
from
    per_pay_proposals ppp,
    per_performance_reviews ppr,
    per_assignments_f2 paa,
    per_people_f ppf,
    per_pay_bases ppb,
    pay_input_values_f piv,
    pay_element_types_f pet,
    fnd_currencies_tl fct,
    fnd_user fu
where  
    1=1
and ppp.performance_review_id = ppr.performance_review_id(+)
and paa.assignment_id = ppp.assignment_id
and paa.person_id = ppf.person_id
and ppp.last_updated_by = fu.user_id(+)
and ppp.change_date between paa.effective_start_date and paa.effective_end_date
and paa.pay_basis_id = ppb.pay_basis_id(+)
and per_saladmin_utility.get_currency (ppp.assignment_id,ppp.change_date) = fct.name
and fct.language = userenv ('LANG')
and fct.currency_code = pet.input_currency_code
and ppb.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and ppp.assignment_id=12951
and ppp.change_date between piv.effective_start_date
and piv.effective_end_date
and ppp.change_date between pet.effective_start_date and pet.effective_end_date
--
and ppf.employee_number = '1309'
order by
    change_date desc;

Query: Salary Management

select
    ppf.employee_number                 "Employee Number",
    --
    ppf.full_name                         "Full Name",
    psf.assignment_number                 "Assignment Number",
    ppb.name                              "Salary basis",
    ppp.change_date                       "Approved Date",
    ppp.proposed_salary_n                 "Approved Salary",
    pro1.change_date                      "Change Date",
    pro1.proposed_salary_n                "Proposed Salary",
    proposal_reason                       "Proposal Reason",
    pppc.change_percentage                "COL %",
    pppc.change_amount_n                  "COL Amount",
    psf.employment_category               "Assignment Category",           -- lookup;EMP_CAT
    --
    ppf.person_id,
    ppb.pay_basis_id
from
     per_all_people_f ppf,
     per_assignments_f psf,
     per_pay_bases ppb,
      per_pay_proposals pro1,
     (select ppp.change_date,
                  ppp.proposed_salary_n,
                  ppp.approved,
                  ppp.assignment_id
             from per_pay_proposals ppp
            where approved = 'Y')  ppp,
    per_pay_proposal_components pppc        
where
    1=1
and ppf.person_id = psf.person_id
and psf.pay_basis_id = ppb.pay_basis_id
and psf.assignment_id = pro1.assignment_id(+)
and pro1.approved(+) = 'N'
and psf.assignment_id = ppp.assignment_id(+)
and (ppp.change_date is null
     or ppp.change_date = (select max (change_date)
                        from per_pay_proposals pro3
                        where ppp.assignment_id = pro3.assignment_id
                        and pro3.approved = 'Y'))
and pro1.pay_proposal_id = pppc.pay_proposal_id(+)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between psf.effective_start_date and psf.effective_end_date
--
and ppf.employee_number = '1309'
order by
    1,2;

Query: People Assignment Salary - Peer Avg

select     /*+ leading(sal.day) */
    percentile_cont (0.5)
    within group (order by sal.salary)         "Salary",
    sal.currency_code                          "Currency",
    round (
    avg (
    (sal.salary
    / case
         when sal.grade_mid_value = 0 then 1
         else sal.grade_mid_value
      end)
    * 100),
    2)                                          "COMPARATIO",
    trunc (
   avg (
   case
      when sal.salary <= sal.grade_min
      then
         0
      when sal.salary >= sal.grade_max
      then
         5
      when sal.salary <= (sal.grade_mid_value + sal.grade_min) / 2
      then
         1
      when sal.salary < sal.grade_mid_value
      then
         2
      when sal.salary >= (sal.grade_mid_value + sal.grade_max) / 2
      then
         4
      when sal.salary >= sal.grade_mid_value
      then
         3
    end),
    1)                                            "QUARTILE",                        -- lookup: ben_cwb_quartile
    round (
    avg (
    ( (sal.salary - sal.grade_min)
    / case
         when (sal.grade_max - sal.grade_min) = 0 then 1
         else (sal.grade_max - sal.grade_min)
      end)
    * 100),
    2)
range_position
from
    (select day.effective_date effective_start_date,
    add_months (day.effective_date, 12) - 1 effective_end_date,
    asg.business_group_id,
    asg.assignment_id,
    asg.job_id job_id,
    loc.country country,
    asg.pay_basis_id,
    asg.grade_id,
    pgr.rate_id,
    hri_bpl_abv.calc_abv (asg.assignment_id,
            asg.business_group_id,
            'HEAD',
            pro.change_date)  headcount,
                 case
                    when ppb.pay_basis = 'HOURLY'
                         or fnd_profile.value ('PER_ANNUAL_SALARY_ON_FTE') =
                               'N'
                    then
                       (ppb.pay_annualization_factor * pro.proposed_salary_n)
                    else
                       (ppb.pay_annualization_factor * pro.proposed_salary_n)
                       / case
                            when per_saladmin_utility.get_fte_factor (
                                    asg.assignment_id,
                                    day.effective_date) = 0
                            then
                               1
                            else
                               per_saladmin_utility.get_fte_factor (
                                  asg.assignment_id,
                                  day.effective_date)
                         end
                 end
                    salary,
                 pgr.currency_code grade_currency,
                 case
                    when     pet.input_currency_code is not null
                         and pgr.currency_code is not null
                         and pgr.currency_code != pet.input_currency_code
                    then
                         per_saladmin_utility.get_currency_rate (
                            pgr.currency_code,
                            pet.input_currency_code,
                            day.effective_date,
                            asg.business_group_id)
                       * pgr.minimum
                       * ppb.grade_annualization_factor
                    else
                       pgr.minimum * ppb.grade_annualization_factor
                 end
                    grade_min,
                 case
                    when     pet.input_currency_code is not null
                         and pgr.currency_code is not null
                         and pgr.currency_code != pet.input_currency_code
                    then
                         per_saladmin_utility.get_currency_rate (
                            pgr.currency_code,
                            pet.input_currency_code,
                            day.effective_date,
                            asg.business_group_id)
                       * pgr.maximum
                       * ppb.grade_annualization_factor
                    else
                       pgr.maximum * ppb.grade_annualization_factor
                 end
                    grade_max,
                 case
                    when     pet.input_currency_code is not null
                         and pgr.currency_code is not null
                         and pgr.currency_code != pet.input_currency_code
                    then
                         per_saladmin_utility.get_currency_rate (
                            pgr.currency_code,
                            pet.input_currency_code,
                            day.effective_date,
                            asg.business_group_id)
                       * pgr.mid_value
                       * ppb.grade_annualization_factor
                    else
                       pgr.mid_value * ppb.grade_annualization_factor
                 end
                    grade_mid_value,
                 pet.input_currency_code currency_code
            from per_all_assignments_f asg,
            per_all_people_f ppf,
                 per_pay_bases ppb,
                 per_pay_proposals pro,
                 pay_input_values_f piv,
                 pay_element_types_f pet,
                 hr_locations_all loc,
                 pay_grade_rules_f pgr,
                 (select trunc (sysdate) effective_date from dual
                  union all
                  select add_months (trunc (sysdate), -12) effective_date
                    from dual
                  union all
                  select add_months (trunc (sysdate), -24) effective_date
                    from dual
                  union all
                  select add_months (trunc (sysdate), -36) effective_date
                    from dual
                  union all
                  select add_months (trunc (sysdate), -48) effective_date
                    from dual) day
           where day.effective_date between asg.effective_start_date
                                        and asg.effective_end_date
                 and asg.job_id is not null
                 and asg.person_id = ppf.person_id
                 and asg.grade_id = pgr.grade_or_spinal_point_id
                 and ppb.rate_id = pgr.rate_id
                 and asg.assignment_id = pro.assignment_id
                 and pro.change_date =
                        (select max (change_date)
                           from per_pay_proposals pro2
                          where     pro2.assignment_id = pro.assignment_id
                                and pro2.change_date <= day.effective_date
                                and pro2.approved = 'Y')
                 and asg.pay_basis_id = ppb.pay_basis_id
                 and ppb.input_value_id = piv.input_value_id
                 and piv.element_type_id = pet.element_type_id
                 and loc.location_id = asg.location_id
                 and pro.change_date between piv.effective_start_date
                                         and piv.effective_end_date
                 and pro.change_date between pet.effective_start_date
                                         and pet.effective_end_date
                 and pro.change_date between pgr.effective_start_date
                                         and pgr.effective_end_date
                 and ppf.employee_number = '1309') sal
where
    1=1
and sysdate between sal.effective_start_date and sal.effective_end_date
group by
    sal.effective_start_date,
    sal.effective_end_date,
    sal.job_id,
    sal.country,
    sal.pay_basis_id,
    sal.currency_code;

Query to find out the responsibility,Menu based on Function

SELECT DISTINCT a.responsibility_name, c.user_menu_name
           FROM apps.fnd_responsibility_tl a,
                apps.fnd_responsibility b,
                apps.fnd_menus_tl c,
                apps.fnd_menus d,
                apps.fnd_application_tl e,
                apps.fnd_application f
          WHERE a.responsibility_id(+) = b.responsibility_id
            AND b.menu_id = c.menu_id
            AND b.menu_id = d.menu_id
            AND e.application_id = f.application_id
            AND f.application_id = b.application_id
            AND a.LANGUAGE = 'US'
            AND b.menu_id IN (
                   SELECT menu_id
                     FROM fnd_menu_entries_vl
                    WHERE function_id IN (
                             SELECT function_id
                               FROM applsys.fnd_form_functions_tl
                              WHERE user_function_name =
                                                       'Fujitsu Trip Sequence'))

Query to find concurrent execution file name

SELECT fe.executable_name
      ,fe.execution_file_name
      ,flu.meaning execution_method
      ,fcp.concurrent_program_name
      ,fcpt.user_concurrent_program_name
FROM fnd_executables fe
      ,fnd_concurrent_programs fcp
      ,fnd_concurrent_programs_tl fcpt
      ,apps.fnd_lookup_values flu