eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_FR_PLAN_DFORM SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 20


l_tp_select varchar2(150);

Line: 52


select course_tl.version_name      member_name
      ,course.activity_version_id  member_id
      ,course_tl.description       member_description
      ,'ACTIVITY_VERSION'          member_level
from ota_activity_versions course,
     ota_activity_versions_tl course_tl
where course.activity_version_id = course_tl.activity_version_id(+)
and   course_tl.language(+) = userenv('LANG')
and exists
       (select null
        from ota_training_plan_members tpm
        where tpm.training_plan_id = c_training_plan_id
        and tpm.activity_version_id = course.activity_version_id
        and tpm.member_status_type_id <> 'CANCELLED')
union
select category_tl.name           member_name
      ,category.activity_id       member_id
      ,category_tl.description    member_description
      ,'ACTIVITY_DEFINITION' member_level
from ota_activity_definitions category,
     ota_activity_definitions_tl category_tl
where category.activity_id = category_tl.activity_id(+)
and     category_tl.language(+) = userenv('LANG')
and exists
       (select null
        from ota_training_plan_members tpm
        where tpm.training_plan_id = c_training_plan_id
        and tpm.activity_definition_id = category.activity_id
        and tpm.member_status_type_id <> 'CANCELLED');

Line: 87


select sum(pbv.value) delg_number
      ,ec.lookup_code emp_code
      ,ec.meaning emp_catg
from per_budgets pb,
     per_budget_versions pbr,
     per_budget_values pbv,
     per_budget_elements pbe,
     ota_training_plan_members tpm,
     hr_lookups ec
where pb.unit = 'FR_DELEGATES_PER_CATEGORY'
  and pb.budget_type_code = 'OTA_BUDGET'
  and pb.business_group_id = tpm.business_group_id
  and pb.budget_id = pbr.budget_id
  and pbr.budget_version_id = pbe.budget_version_id
  and pbv.budget_element_id = pbe.budget_element_id
  and pbv.business_group_id = tpm.business_group_id
  and tpm.business_group_id = c_business_group_id
  and pbe.training_plan_member_id = tpm.training_plan_member_id
  and pbe.event_id is null
  and pbe.business_group_id = tpm.business_group_id
  and tpm.training_plan_id = c_training_plan_id
  and decode(c_member_level,
             'ACTIVITY_VERSION', tpm.activity_version_id ,
             'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id
  and ec.lookup_type = 'FR_EMPLOYEE_CATEGORY'
  and ec.lookup_code = pbv.budget_information1
  group by ec.lookup_code,ec.meaning;

Line: 118


select distinct class.event_id  class_id
      ,class_tl.title           class_title
      ,fnd_date.date_to_canonical(class.course_start_date)  class_from
      ,fnd_date.date_to_canonical(class.course_end_date)    class_to
from ota_events class,
     ota_events_tl class_tl,
     ota_activity_versions course
where class.event_id is not null
  and class_tl.event_id = class.event_id
  and class_tl.language(+) = userenv('LANG')
  and class.activity_version_id = course.activity_version_id
  and decode(c_member_level,
             'ACTIVITY_VERSION',course.activity_version_id ,
             'ACTIVITY_DEFINITION',course.activity_id) = c_member_id;

Line: 139


select count(pbv.budget_information1) evt_delg_number
      ,ec.lookup_code evt_emp_code
      ,ec.meaning evt_emp_catg
from per_budgets pb,
     per_budget_versions pbr,
     per_budget_values pbv,
     per_budget_elements pbe,
     ota_training_plan_members tpm,
     hr_lookups ec
where pb.unit = 'FR_DELEGATES_PER_CATEGORY'
  and pb.budget_type_code = 'OTA_BUDGET'
  and pb.business_group_id = tpm.business_group_id
  and pb.budget_id = pbr.budget_id
  and pbr.budget_version_id = pbe.budget_version_id
  and pbv.budget_element_id = pbe.budget_element_id
  and pbv.business_group_id = tpm.business_group_id
  and tpm.business_group_id = c_business_group_id
  and pbe.training_plan_member_id = tpm.training_plan_member_id
  and pbe.event_id = c_event_id
  and pbe.business_group_id = tpm.business_group_id
  and tpm.training_plan_id = c_training_plan_id
  and decode(c_member_level,
             'ACTIVITY_VERSION', tpm.activity_version_id ,
             'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id
  and ec.lookup_type = 'FR_EMPLOYEE_CATEGORY'
  and ec.lookup_code = pbv.budget_information1
group by ec.lookup_code,ec.meaning;

Line: 180


l_tp_select :='Select tp.training_plan_id, tp.name,org_unit.name, fnd_date.date_to_canonical(period.start_date),fnd_date.date_to_canonical(period.end_date)';

Line: 225


l_sql := l_tp_select||l_tp_from||l_organization_from||l_tp_where||l_organization_where||l_where_tplan||l_tp_order;

Line: 369


  select budget_level
  from ota_tp_measurement_types
  where business_group_id = p_business_group_id
  and   tp_measurement_code = p_measurement_code;

Line: 425


select sum(pbv.value) value
from per_budgets pb,
     per_budget_versions pbr,
     per_budget_values pbv,
     per_budget_elements pbe,
     ota_training_plan_members tpm
where pb.unit = c_measure_code
  and pb.budget_type_code = 'OTA_BUDGET'
  and pb.business_group_id = tpm.business_group_id
  and pb.budget_id = pbr.budget_id
  and pbr.budget_version_id = pbe.budget_version_id
  and pbv.budget_element_id = pbe.budget_element_id
  and pbv.business_group_id = tpm.business_group_id
  and tpm.business_group_id = c_business_group_id
  and pbe.training_plan_member_id = tpm.training_plan_member_id
  and pbe.event_id is null
  and pbe.business_group_id = tpm.business_group_id
  and tpm.training_plan_id = c_training_plan_id
  and decode(c_member_level,
             'ACTIVITY_VERSION', tpm.activity_version_id ,
             'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id;

Line: 480


select sum(pbv.value)
from per_budgets pb,
     per_budget_versions pbr,
     per_budget_values pbv,
     per_budget_elements pbe,
     ota_training_plan_members tpm
where pb.unit = 'FR_DURATION_HOURS'
  and pb.budget_type_code = 'OTA_BUDGET'
  and pb.business_group_id = tpm.business_group_id
  and pb.budget_id = pbr.budget_id
  and pbr.budget_version_id = pbe.budget_version_id
  and pbv.budget_element_id = pbe.budget_element_id
  and pbv.business_group_id = tpm.business_group_id
  and tpm.business_group_id = c_business_group_id
  and pbe.event_id = c_event_id
  and pbe.training_plan_member_id = tpm.training_plan_member_id
  and pbe.business_group_id = tpm.business_group_id
  and tpm.training_plan_id = c_training_plan_id
  and decode(c_member_level,
             'ACTIVITY_VERSION', tpm.activity_version_id ,
             'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id;

Line: 531


  select meaning,tag
  FROM   fnd_lookup_values flv
  WHERE  lookup_type         = p_lookup_type
  AND    lookup_code         = p_lookup_code
  AND    language            = userenv('LANG')
  AND    view_application_id = p_view_app_id
  and    SECURITY_GROUP_ID   = decode(substr(userenv('CLIENT_INFO'),55,1),
                                 ' ', 0,
                                 NULL, 0,
                                 '0', 0,
                                 fnd_global.lookup_security_group(
                                     FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID));