APPS.HR_HEAD_COUNT_SUMMARY SQL Statements

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

Line: 123

select 	distinct pose.ORGANIZATION_ID_CHILD organization_id
from    per_org_structure_elements pose
where   exists (select 1
from    per_org_structure_versions posv
where   posv.org_structure_version_id 	= pose.org_structure_version_id
and     posv.organization_structure_id 	= P_ORGANIZATION_STRUCTURE_ID
and     (posv.date_from <= P_REPORT_DATE_FROM
or     	nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'))
				>= P_REPORT_DATE_TO ))
and     pose.organization_id_parent 	= P_TOP_ORGANIZATION_ID
union
select  P_TOP_ORGANIZATION_ID
from    sys.dual
where   P_INCLUDE_TOP_ORG = 'Y';

Line: 143

select 	 posv.org_structure_version_id
	,posv.date_from date_from
	,nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')) date_to
from 	per_org_structure_versions posv
where 	posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and 	(P_REPORT_DATE_FROM between posv.date_from and
	nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'))
	or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO)
order by   posv.org_structure_version_id
	  ,posv.date_from
	  ,posv.date_to;

Line: 159

select  ose.org_structure_element_id,
	ose.organization_id_child organization_id1
from    per_org_structure_elements ose
where   ose.org_structure_version_id +0    = P_ORG_STRUCTURE_VERSION_ID
and     ose.organization_id_parent         = P_TOP_ORGANIZATION_ID
order by ose.organization_id_child;

Line: 171

select 	ose.organization_id_child organization_id2
from   	per_org_structure_elements ose
where  	ose.org_structure_version_id +0  	= P_ORG_STRUCTURE_VERSION_ID
and	P_ROLL_UP				= 'Y'
connect by prior ose.organization_id_child 	= ose.organization_id_parent
and    	ose.org_structure_version_id  		= P_ORG_STRUCTURE_VERSION_ID
start with ose.organization_id_parent 		= P_ORGANIZATION_ID
and    	ose.org_structure_version_id  		= P_ORG_STRUCTURE_VERSION_ID
UNION
select 	P_ORGANIZATION_ID organization_id
from	dual
where	P_ROLL_UP                          = 'Y'
UNION
select 	P_ORGANIZATION_ID organization_id
from	dual
where	P_ROLL_UP                          = 'N';

Line: 194

select 	paf.assignment_id
       ,paf.job_id
       ,paf.effective_start_date
       ,paf.assignment_type
from 	per_all_assignments_f 		paf,
	per_assignment_status_types 	past
where 	paf.organization_id 	= 	P_ORGANIZATION_ID_CHILD
   and     P_EFFECTIVE_DATE  between paf.effective_start_date
                                 and paf.effective_end_date
   and     paf.assignment_status_type_id = past.assignment_status_type_id
   and     (
           (paf.assignment_type = 'E'
           and (
              (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
           OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
           OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
                                                                       'SUSP_ASSIGN'))
               )
           )
           OR
           (paf.assignment_type =  'C'
           and (
              (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
           OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
           OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
                                                                       'SUSP_CWK_ASG'))
              )
           )
           );

Line: 232

select 	paf.assignment_id
       ,paf.job_id
       ,paf.effective_start_date
       ,paf.assignment_type
from 	per_all_assignments_f 		paf,
	per_assignment_status_types 	past
where 	paf.organization_id 	= 	p_organization_id_child
and     paf.effective_start_date     <= P_DATE_FROM
and     paf.assignment_status_type_id = past.assignment_status_type_id
and     P_DATE_TO <= (select max(paf1.effective_end_date)
                      from   per_all_assignments_f          paf1
                            ,per_assignment_status_types    past1
                      where  paf.assignment_id              = paf1.assignment_id
                      and    paf1.assignment_status_type_id = past1.assignment_status_type_id
                      and    past.per_system_status         = past1.per_system_status)
and     (
        (paf.assignment_type = 'E'
         and (
             (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
          OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
          OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
                                                                      'SUSP_ASSIGN'))
             )
           )
           OR
          (paf.assignment_type =  'C'
           and (
              (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
           OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
           OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
                                                                       'SUSP_CWK_ASG'))
               )
           )
           );

Line: 275

select  paf.job_id
from     per_periods_of_service pos
	,per_all_assignments_f 	 paf
where   pos.date_start                  <= P_DATE_FROM
and     pos.actual_termination_date is not null
and     pos.actual_termination_date between P_DATE_FROM and P_DATE_TO
and     pos.period_of_service_id        = paf.period_of_service_id
and	paf.assignment_type		= 'E'
and	paf.primary_flag		= 'Y'
and     paf.organization_id  		= P_ORGANIZATION_ID_CHILD
and     paf.effective_start_date in
				(select max(paf1.effective_start_date)
				from 	per_all_assignments_f paf1
				where 	paf1.assignment_id = paf.assignment_id
				and 	paf1.effective_end_date
						between P_DATE_FROM
						and  P_DATE_TO)
UNION
select paf.job_id
from   per_periods_of_placement       pop
      ,per_all_assignments_f  paf
where  pop.date_start          <= P_DATE_FROM
and    pop.actual_termination_date is not null
and    pop.actual_termination_date between P_DATE_FROM
                                          and P_DATE_TO
and    pop.date_start = paf.period_of_placement_date_start
and    paf.effective_end_date   = pop.actual_termination_date
and    paf.assignment_type      = 'C'
and    paf.primary_flag		= 'Y'
and    paf.organization_id      = P_ORGANIZATION_ID_CHILD
and    paf.effective_start_date in
                                (select max(paf1.effective_start_date)
                                from    per_all_assignments_f paf1
                                where   paf1.assignment_id = paf.assignment_id
                                and     paf1.effective_end_date
                                                between P_DATE_FROM
                                                and  P_DATE_TO);

Line: 316

select formula_id
from   ff_formulas_f
where  p_business_group_id = business_group_id+0
and    trunc(sysdate) between effective_start_date and effective_end_date
and    formula_name = 'BUDGET_'||p_budget
and    formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');

Line: 325

select formula_id
from   ff_formulas_f
where  business_group_id+0 is null
and    trunc(sysdate) between effective_start_date and effective_end_date
and    formula_name = 'TEMPLATE_'||p_budget
and    formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');

Line: 423

select  min(posv.org_structure_version_id)
into	l_min_org_structure_version_id
from    per_org_structure_versions posv
where   posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and 	P_REPORT_DATE_FROM between posv.date_from
	and nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'));

Line: 432

select  greatest(posv.date_from,P_REPORT_DATE_FROM),
	least(nvl(posv.date_to,P_REPORT_DATE_TO),P_REPORT_DATE_TO)
into	l_min_date_from,l_min_date_to
from    per_org_structure_versions posv
where   posv.org_structure_version_id = l_min_org_structure_version_id;

Line: 441

select  max(posv.org_structure_version_id)
into    l_max_org_structure_version_id
from    per_org_structure_versions posv
where   posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and 	P_REPORT_DATE_TO between posv.date_from
	and nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'));

Line: 450

select  least(nvl(posv.date_to,P_REPORT_DATE_TO),P_REPORT_DATE_TO),
	greatest(posv.date_from,P_REPORT_DATE_FROM)
into	l_max_date_to,l_max_date_from
from    per_org_structure_versions posv
where   posv.org_structure_version_id = l_max_org_structure_version_id;