DBA Data[Home] [Help]

APPS.HR_NL_LAW_SAMEN_REPORT SQL Statements

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

Line: 195

select max(posv.org_structure_version_id) org_structure_version_id
from per_org_structure_versions posv
where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and     to_number(to_char(nvl(posv.date_from,sysdate),'YYYY')) <= P_REPORT_YEAR
and    	to_number(to_char(nvl(posv.date_to,sysdate),'YYYY')) >= P_REPORT_YEAR
order by   posv.org_structure_version_id;
Line: 208

select    distinct ose.organization_id_child organization_id
from      per_org_structure_elements ose, per_org_structure_versions_v posv
where     ose.org_structure_version_id     = posv.ORG_STRUCTURE_VERSION_ID
and       ose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and       posv.ORG_STRUCTURE_VERSION_ID =  P_ORG_STRUCTURE_VERSION_ID
and       posv.ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID
and       ose.organization_id_parent         = P_TOP_ORGANIZATION_ID

UNION
select    P_TOP_ORGANIZATION_ID organization_id
from      dual
order by  organization_id;
Line: 226

select 	distinct ose.organization_id_child organization_id
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: 252

SELECT distinct POU.ORGANIZATION_ID organization_id
FROM PER_ORGANIZATION_UNITS POU
WHERE POU.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND POU.ORGANIZATION_ID = HR_NL_ORG_INFO.Check_Org_In_Region(POU.ORGANIZATION_ID,P_REGION)
AND TO_NUMBER(TO_CHAR(POU.DATE_FROM,'YYYY'))<= P_REPORT_YEAR
AND TO_NUMBER(TO_CHAR(NVL(POU.DATE_TO,SYSDATE),'YYYY')) >= P_REPORT_YEAR;
Line: 406

select     distinct paf.assignment_id assignment_id
              ,paf.person_id
from       per_all_assignments_f paf

where      paf.organization_id     =     p_organization_id_child
and        paf.business_group_id   =     P_BUSINESS_GROUP_ID
and        paf.assignment_type     =       'E'
and        paf.primary_flag        =       'Y'
and        to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
and to_number(to_char(nvl(paf.effective_end_date,sysdate),'YYYY')) >= P_REPORT_YEAR;
Line: 423

select   distinct hr_nl_calc_target_group.get_target_group(p_person_id,
                   to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY'))
                  allochth
       ,ppf1.per_information7 objection
from     per_people_f ppf1
where      ppf1.business_group_id   =      P_BUSINESS_GROUP_ID
and        to_number(to_char(nvl(ppf1.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
and to_number(to_char(nvl(ppf1.effective_end_date,sysdate),'YYYY')) >= P_REPORT_YEAR
and ppf1.person_id  = P_PERSON_ID
and     to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf1.effective_start_date and ppf1.effective_end_date;
Line: 443

select  distinct paf.assignment_id assignment_id
            ,paf.effective_start_date start_date
	   ,paf.employment_category per_type
	   ,hr_nl_calc_target_group.get_target_group(ppf.person_id,
           to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY')) allochth
	   ,ppf.per_information7 objection

from 	per_all_assignments_f paf
      , per_people_f ppf

where 	paf.organization_id 	= 	p_organization_id_child
and	paf.business_group_id	=	P_BUSINESS_GROUP_ID
and	paf.assignment_type	= 	'E'
and	paf.primary_flag	=	'Y'
and	to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
and nvl(paf.effective_end_date,sysdate) >= to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY')
and paf.person_id  = ppf.person_id
and ppf.BUSINESS_GROUP_ID = paf.business_group_id
and to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
Line: 470

select  distinct  pos.leaving_reason leaving_reason
       ,paf.job_id job
	   ,paf.person_id

from    per_periods_of_service pos
	  , per_assignments_f 	 paf

where   to_number(to_char(nvl(pos.date_start,sysdate),'YYYY'))                  <= P_REPORT_YEAR
and     to_number(to_char(nvl(pos.actual_termination_date,sysdate+50000),'YYYY')) = P_REPORT_YEAR
and     pos.period_of_service_id        = paf.period_of_service_id
and	   paf.business_group_id	= P_BUSINESS_GROUP_ID
and	   paf.assignment_type	= 'E'
and	   paf.primary_flag		= 'Y'
and    paf.organization_id  	= P_ORGANIZATION_ID_CHILD;
Line: 598

select  count(1)
into    l_last_acht_total
from 	per_all_assignments_f paf
      , per_people_f ppf

where 	paf.organization_id 	= 	P_ORGANIZATION_ID
and	    paf.business_group_id	=	P_BUSINESS_GROUP_ID
and	    paf.assignment_type	= 	'E'
and	    paf.primary_flag	=	'Y'
and	    to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR - 1
and     nvl(paf.effective_end_date,sysdate) >= to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY')
and     paf.person_id  = ppf.person_id
and     ppf.BUSINESS_GROUP_ID = paf.business_group_id
and     hr_nl_calc_target_group.get_target_group(ppf.person_id,
        to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY')) = 'Y'
and     to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
Line: 687

select hr_nl_calc_target_group.get_target_group(ppf.person_id,
       to_date('31/12/'||to_char(P_REPORT_YEAR),'DD/MM/YYYY')) allochth
into l_acht
from per_people_f ppf
where ppf.person_id = term_rec.person_id
and     to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;