APPS.HR_BIS_ORG_PERF SQL Statements

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

Line: 109

    select opl.organization_id_group organization_id  -- S.Bhattal, 19/07/99
    ,      asg.assignment_id
    from   per_assignment_status_types ast
    ,      per_assignments_f           asg
    ,      hri_org_param_list          opl
    where  opl.org_param_id            = cp_org_param_id
    and    opl.organization_id_child   = asg.organization_id
    and    cp_eff_date between asg.effective_start_date and
		asg.effective_end_date
    and    asg.assignment_type = 'E'
    and    asg.assignment_status_type_id = ast.assignment_status_type_id
    and    ast.per_system_status = 'ACTIVE_ASSIGN';

Line: 125

    select org.organization_id
    from   hr_organization_units org
    ,      hri_org_param_list    opl
    where  opl.org_param_id          = cp_org_param_id
    and    opl.organization_id_child = org.organization_id
    group by org.organization_id;  -- S.Bhattal, 19/07/99

Line: 135

    select formula_id
    from   ff_formulas_f
    where  cp_business_group_id = business_group_id
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'BUDGET_'||p_budget_metric;

Line: 142

    select formula_id
    from   ff_formulas_f
    where  business_group_id   is null
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'TEMPLATE_'||p_budget_metric;

Line: 236

select 1
from per_all_assignments_f asg
where asg.assignment_id = cp_assignment_id
and   cp_effective_date between asg.effective_start_date and asg.effective_end_date;

Line: 284

    select asg.organization_id
    ,      asg.assignment_id
    ,      1 no_change
    ,      0 gain
    ,      0 loss
    from   per_assignment_status_types ast
    ,      per_assignments_f           asg
    ,      hr_all_organization_units   org
    ,      hri_org_param_list          opl
    where  opl.org_param_id = cp_org_param_id
    and    opl.organization_id_group = cp_org_id
    and    opl.organization_id_child = asg.organization_id
    and    org.organization_id = asg.organization_id
    and    cp_end_date between asg.effective_start_date and asg.effective_end_date
    and    asg.assignment_type = 'E'
    and    asg.assignment_status_type_id = ast.assignment_status_type_id
    and    ast.per_system_status = 'ACTIVE_ASSIGN'
    and    exists (
             select 1
             from   per_assignment_status_types ast2
             ,      per_assignments_f           asg2
             ,      hri_org_param_list          opl2
             where  opl2.org_param_id = cp_org_param_id
             and    opl2.organization_id_group = cp_org_id
             and    opl2.organization_id_child = asg2.organization_id
             and    asg2.assignment_id = asg.assignment_id
             and    asg2.assignment_status_type_id = ast2.assignment_status_type_id
             and    asg2.assignment_type = 'E'
             and    ast2.per_system_status = 'ACTIVE_ASSIGN'
             and    cp_start_date between asg2.effective_start_date and asg2.effective_end_date )
    UNION
    select asg.organization_id
    ,      asg.assignment_id
    ,      0 no_change
    ,      1 gain
    ,      0 loss
    from   per_assignment_status_types ast
    ,      per_assignments_f           asg
    ,      hr_all_organization_units   org
    ,      hri_org_param_list          opl
    where  opl.org_param_id = cp_org_param_id
    and    opl.organization_id_group = cp_org_id
    and    opl.organization_id_child = asg.organization_id
    and    org.organization_id = asg.organization_id
    and    asg.assignment_type = 'E'
    and    cp_end_date between asg.effective_start_date and asg.effective_end_date
    and    asg.assignment_status_type_id = ast.assignment_status_type_id
    and    ast.per_system_status = 'ACTIVE_ASSIGN'
    and    not exists (
             select 1
             from   per_assignment_status_types ast2
             ,      per_assignments_f           asg2
             ,      hri_org_param_list          opl2
             where  opl2.org_param_id = cp_org_param_id
             and    opl2.organization_id_group = cp_org_id
             and    opl2.organization_id_child = asg2.organization_id
             and    asg2.assignment_id=asg.assignment_id
             and    asg2.assignment_status_type_id = ast2.assignment_status_type_id
             and    asg2.assignment_type = 'E'
             and    ast2.per_system_status = 'ACTIVE_ASSIGN'
             and    cp_start_date between asg2.effective_start_date and asg2.effective_end_date)
    UNION
    select asg.organization_id
    ,      asg.assignment_id
    ,      0 no_change
    ,      0 gain
    ,      1 loss
    from   per_assignment_status_types ast
    ,      per_assignments_f           asg
    ,      hr_all_organization_units   org
    ,      hri_org_param_list          opl
    where  opl.org_param_id = cp_org_param_id
    and    opl.organization_id_group = cp_org_id
    and    opl.organization_id_child = asg.organization_id
    and    org.organization_id = asg.organization_id
    and    asg.assignment_type = 'E'
    and    cp_start_date between asg.effective_start_date and asg.effective_end_date
    and    asg.assignment_status_type_id = ast.assignment_status_type_id
    and    ast.per_system_status = 'ACTIVE_ASSIGN'
    and    not exists (
             select 1
             from   per_assignment_status_types ast2
             ,      per_assignments_f asg2
             ,      hri_org_param_list          opl2
             where  opl2.org_param_id = cp_org_param_id
             and    opl2.organization_id_group = cp_org_id
             and    opl2.organization_id_child = asg2.organization_id
             and    asg2.assignment_id=asg.assignment_id
             and    asg2.assignment_status_type_id = ast2.assignment_status_type_id
             and    asg2.assignment_type = 'E'
             and    ast2.per_system_status = 'ACTIVE_ASSIGN'
             and    cp_end_date between asg2.effective_start_date and asg2.effective_end_date);

Line: 380

    select org.organization_id
    from   hr_organization_units org
    ,      hri_org_param_list    opl
    where  opl.org_param_id          = cp_org_param_id
    and    opl.organization_id_child = org.organization_id
    group by org.organization_id;  -- S.Bhattal, 19/07/99

Line: 390

    select formula_id
    from   ff_formulas_f
    where  cp_business_group_id = business_group_id
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'BUDGET_'||p_budget_metric;

Line: 398

    select formula_id
    from   ff_formulas_f
    where business_group_id   is null
    and     trunc(sysdate) between effective_start_date and effective_end_date
    and     formula_name = 'TEMPLATE_'||p_budget_metric;

Line: 575

    select asg.organization_id
    ,      asg.assignment_id
    from   per_assignment_status_types ast
    ,      per_assignments_f asg
    where  cp_report_date between asg.effective_start_date and asg.effective_end_date
    and    asg.assignment_type = 'E'
    and    ast.assignment_status_type_id = asg.assignment_status_type_id
    and    ast.per_system_status = 'ACTIVE_ASSIGN'
    and    asg.organization_id in (
             select be.organization_id
             from   per_budget_values      bval
             ,      per_budget_elements    be
           	 ,      per_budget_versions    bver
           	 ,      per_time_periods       tp
           	 ,      per_budgets_v          bud
             where  bud.budget_id	= cp_budget_id
             and	  bud.budget_id = bver.budget_id
             and    sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
             and	  be.budget_version_id = bver.budget_version_id
             and	  be.budget_element_id = bval.budget_element_id
             and	  tp.time_period_id	= bval.time_period_id
             and	  cp_report_date between tp.start_date and tp.end_date );

Line: 602

    select distinct be.organization_id
    from   per_budget_values    bval
    ,      per_budget_elements  be
    ,      per_budget_versions  bver
    ,      per_time_periods     tp
    ,      per_budgets_v        bud
    where  bud.budget_id = cp_budget_id
    and	   bud.budget_id = bver.budget_id
    and    sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
    and	   be.budget_version_id	= bver.budget_version_id
    and	   be.budget_element_id	= bval.budget_element_id
    and	   tp.time_period_id = bval.time_period_id
    and	   cp_report_date between tp.start_date and tp.end_date
    -- bug 2324688
    and    be.organization_id is not null;

Line: 622

    select sum(bval.value) budget_value
    ,      be.organization_id
    from   per_budget_values    bval
    ,      per_budget_elements  be
    ,      per_budget_versions  bver
    ,      per_time_periods	    tp
    ,      per_budgets_v		    bud
    where  bud.budget_id = cp_budget_id
    and	   bud.budget_id = bver.budget_id
    and    sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
    and	   be.budget_version_id	= bver.budget_version_id
    and	   be.budget_element_id	= bval.budget_element_id
    and	   tp.time_period_id = bval.time_period_id
    and	   be.organization_id	is not null
    and	   cp_report_date between tp.start_date and tp.end_date
    group by be.organization_id;

Line: 643

    select formula_id
    from   ff_formulas_f
    where  cp_business_group_id = business_group_id
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'BUDGET_'||cp_budget_metric;

Line: 652

    select formula_id
    from   ff_formulas_f
    where  business_group_id   is null
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'TEMPLATE_'||cp_budget_metric;

Line: 673

  select  unit
  into    l_budget_metric
  from    per_budgets
  where   budget_id = p_budget_id;

Line: 755

    select asg.organization_id
    ,      asg.assignment_id
    from   per_assignment_status_types ast
    ,      per_assignments_f asg
    where  cp_report_date between asg.effective_start_date and asg.effective_end_date
    and    asg.assignment_type = 'E'
    and    ast.assignment_status_type_id = asg.assignment_status_type_id
    and    ast.per_system_status = 'ACTIVE_ASSIGN'
    and    asg.organization_id in (
select  distinct  bdet.organization_id
from      pqh_budgets bud
        , pqh_budget_versions bver
        , pqh_budget_details  bdet
        , pqh_budget_periods  bper
        , per_shared_types pst1
        , per_time_periods ptp
where bud.budget_id = cp_budget_id
and   bud.budget_id  = bver.budget_id
and   sysdate between bver.date_from and nvl(bver.date_to, sysdate +1)
and   bver.budget_version_id  = bdet.budget_version_id
and   bdet.budget_detail_id      = bper.budget_detail_id
and   bper.start_time_period_id  = ptp.time_period_id
and   bdet.organization_id   is not null
and   cp_report_date between ptp.start_date and ptp.end_date);

Line: 784

select  distinct  bdet.organization_id
from      pqh_budgets bud
        , pqh_budget_versions bver
        , pqh_budget_details  bdet
        , pqh_budget_periods  bper
        , per_shared_types pst1
        , per_time_periods ptp
where bud.budget_id = cp_budget_id
and   bud.budget_id  = bver.budget_id
and   sysdate between bver.date_from and nvl(bver.date_to, sysdate +1)
and   bver.budget_version_id  = bdet.budget_version_id
and   bdet.budget_detail_id      = bper.budget_detail_id
and   bper.start_time_period_id  = ptp.time_period_id
and   bdet.organization_id   is not null
and   cp_report_date between ptp.start_date and ptp.end_date;

Line: 804

select    SUM(bper.budget_unit1_value)           budget_value1
           , SUM(bper.budget_unit2_value)           budget_value2
           , SUM(bper.budget_unit3_value)           budget_value3
        , bdet.organization_id
from      pqh_budgets bud
        , pqh_budget_versions bver
        , pqh_budget_details  bdet
        , pqh_budget_periods  bper
        , per_shared_types pst1
        , per_shared_types pst2
        , per_shared_types pst3
        , per_time_periods ptp
where bud.budget_id = cp_budget_id
and   bud.budget_unit1_id = pst1.shared_type_id
and   bud.budget_unit2_id = pst2.shared_type_id (+)
and   bud.budget_unit3_id = pst3.shared_type_id (+)
and   bud.budget_id  = bver.budget_id
and   sysdate between bver.date_from and nvl(bver.date_to, sysdate+1)
and   bver.budget_version_id  = bdet.budget_version_id
and   bdet.budget_detail_id      = bper.budget_detail_id
and   bper.start_time_period_id  = ptp.time_period_id
and   bdet.organization_id   is not null
and   cp_report_date between ptp.start_date and ptp.end_date
group by  bdet.organization_id;

Line: 834

    select formula_id
    from   ff_formulas_f
    where  cp_business_group_id = business_group_id
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'BUDGET_'||cp_budget_metric;

Line: 843

    select formula_id
    from   ff_formulas_f
    where  business_group_id   is null
    and    trunc(sysdate) between effective_start_date and effective_end_date
    and    formula_name = 'TEMPLATE_'||cp_budget_metric;