[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p_org_id organization_id_parent
,0 lev
FROM dual
UNION
SELECT organization_id_parent
,level
FROM
(SELECT organization_id_parent
,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = latest_named_hierarchy_vers(p_org_id)
)
START WITH organization_id_child = p_org_id
CONNECT BY PRIOR organization_id_parent = organization_id_child
ORDER BY lev;
SELECT TO_NUMBER(inf.org_information1) organization_structure_id
FROM hr_organization_information inf
,hr_all_organization_units org
WHERE org.organization_id = vp_organization_id
AND inf.organization_id = org.business_group_id
AND inf.org_information_context = 'NL_BG_INFO'
AND inf.org_information1 IS NOT NULL;
SELECT sv.org_structure_version_id, sv.version_number
FROM per_org_structure_versions sv
,fnd_sessions ses
WHERE sv.organization_structure_id = named_hierarchy(vp_organization_id)
AND ses.session_id = USERENV('sessionid')
AND ses.effective_date BETWEEN sv.date_from
AND NVL(sv.date_to, Hr_general.End_Of_time)
ORDER BY sv.version_number DESC;
SELECT se.organization_id_child
FROM per_org_structure_elements se
WHERE se.org_structure_version_id =
latest_named_hierarchy_vers(vp_organization_id)
AND (se.organization_id_parent = vp_organization_id OR
se.organization_id_child = vp_organization_id);
select
substr(org_information1, 1, 30),
substr(org_information2, 1, 30)
from
hr_organization_units d,
hr_organization_information e
where
d.organization_id = e.organization_id and
d.organization_id = l_org_id and
e.org_information_context in ('NL_ORG_INFORMATION');
SELECT Organization_id,
org_information1 Region
FROM Hr_Organization_information
WHERE Organization_ID = vp_Organization_ID
AND Org_Information_Context='NL_ORG_INFORMATION';
SELECT vp_Organization_ID Organization_id,
0 lev
FROM dual
UNION
SELECT organization_id_parent
,level lev
FROM
(
SELECT organization_id_parent
,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id =
hr_nl_org_info.latest_named_hierarchy_vers(vp_Organization_ID)
)
START WITH organization_id_child= vp_Organization_ID
CONNECT BY PRIOR organization_id_parent= organization_id_child
ORDER BY lev;
select pae.aei_information8 provider,
decode(pae.aei_information3,'AMI',0,1) si_class_order
from per_assignment_extra_info pae
,fnd_sessions s
where assignment_id = l_assgn_id
and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
pae.AEI_INFORMATION3 = DECODE(l_si_type,'WEWA','AMI','WEWE','AMI','WAOD','AMI','WAOB','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
and s.effective_date between
fnd_date.canonical_to_date(pae.aei_information1)
and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
s.session_id=userenv('sessionid')
order by si_class_order desc;
select
e.org_information_id,
e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
decode(e.org_information3,'AMI',0,1) si_class_order
from
hr_organization_information e
,fnd_sessions s
where
e.organization_id=l_org_id and
e.org_information_context = 'NL_SIP' and
(e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
'PRIVATE_HEALTH','ZFW',l_si_type) or
e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
e.org_information4 = NVL(l_uwv_id,e.org_information4)
and s.effective_date between
fnd_date.canonical_to_date(e.org_INFORMATION1)
and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
s.session_id=userenv('sessionid')
order by p_flag desc,si_class_order desc;
select pae.aei_information8 provider,
decode(pae.aei_information3,'AMI',0,1) si_class_order
from per_assignment_extra_info pae
,fnd_sessions s
where assignment_id = l_assgn_id
and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
pae.aei_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI',
'WAOB','AMI','WAOD','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','ZVWL','AMI','ZVWS','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
and s.effective_date between
fnd_date.canonical_to_date(pae.aei_information1)
and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
s.session_id=userenv('sessionid')
order by si_class_order desc;
select
e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
decode(e.org_information3,'AMI',0,1) si_class_order
from
hr_organization_information e
,fnd_sessions s
where
e.organization_id=l_org_id and
e.org_information_context = 'NL_SIP' and
(e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
'PRIVATE_HEALTH','ZFW',l_si_type) or
e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','ZVWL','AMI','ZVWS','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
s.effective_date between
fnd_date.canonical_to_date(e.org_INFORMATION1)
and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
s.session_id=userenv('sessionid')
order by p_flag desc,si_class_order desc;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information4 provider
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context = 'NL_SIP' and
e.org_information4 =l_uwv_org_id ;
select pae.assignment_extra_info_id,
decode(pae.aei_information3,'AMI',0,1) si_class_order
from per_assignment_extra_info pae
,fnd_sessions s
where assignment_id = l_assgn_id
and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
pae.AEI_INFORMATION3 = DECODE(l_si_type,'WEWA','AMI','WEWE','AMI','WAOD','AMI','WAOB','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
and s.effective_date between
fnd_date.canonical_to_date(pae.aei_information1)
and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date)
and session_id = userenv('sessionid')
order by si_class_order desc;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information5 Avg_days_Per_Month
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information5 IS NOT NULL;
select
e.org_information_id,
e.org_information4 tax_information
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information3 IS NOT NULL
and e.org_information4 IS NOT NULL;
SELECT tax_org_id,lev from hr_organization_information e,(
SELECT l_org_id tax_org_id,0 lev from dual
UNION
SELECT distinct organization_id_parent
,level
FROM (
SELECT distinct organization_id_parent, organization_id_child
FROM per_org_structure_elements pose
where pose.org_structure_version_id = l_org_struct_version_id)
START WITH organization_id_child = l_org_id
CONNECT BY PRIOR organization_id_parent = organization_id_child)
where
e.organization_id=tax_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information3 IS NOT NULL
and e.org_information4 IS NOT NULL
ORDER BY lev;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information7 working_hours
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information7 IS NOT NULL;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information8 Part_Time_Percentage_Method
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information8 IS NOT NULL;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information9 Lunar_5_Week_Month_Wage_Method
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information9 IS NOT NULL;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date
and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information10 Proration_Tax_Table
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information10 IS NOT NULL;
select pae.aei_information8 provider,
decode(pae.aei_information3,'AMI',0,1) si_class_order
from per_assignment_extra_info pae
,fnd_sessions s
where assignment_id = l_assgn_id
and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
pae.aei_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI',
'WAOB','AMI','WAOD','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
and s.effective_date between
fnd_date.canonical_to_date(pae.aei_information1)
and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
s.session_id=userenv('sessionid')
order by si_class_order desc;
select
e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
decode(e.org_information3,'AMI',0,1) si_class_order
from
hr_organization_information e
,fnd_sessions s
where
e.organization_id=l_org_id and
e.org_information_context = 'NL_SIP' and
(e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
'PRIVATE_HEALTH','ZFW',l_si_type) or
e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
s.effective_date between
fnd_date.canonical_to_date(e.org_INFORMATION1)
and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
s.session_id=userenv('sessionid')
order by p_flag desc,si_class_order desc;
select pose.organization_id_child employer
from per_org_structure_elements pose,hr_organization_information e
where 1=1 --pose.org_structure_version_id = P_Org_Struct_Version_Id --16275108
and e.organization_id=pose.organization_id_child
and ((e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information3 IS NOT NULL
and e.org_information4 IS NOT NULL)
or (e.org_information_context= 'NL_LE_TAX_DETAILS'
and e.org_information1 IS NOT NULL
and e.org_information2 IS NOT NULL))
start with pose.organization_id_parent = p_top_org_id
connect by prior pose.organization_id_child = pose.organization_id_parent
and pose.org_structure_version_id = p_Org_Struct_Version_Id
union
select to_number(p_top_org_id) employer from dual;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information11 Parental_Leave_Perc
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information11 IS NOT NULL;
select
e.org_information_id,
e.org_information15 cbs_reporting_frequency
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information15 IS NOT NULL;
select
e.org_information_id,
e.org_information16 cbs_cust_number
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information16 IS NOT NULL;
select
e.org_information_id,
e.org_information18 cbs_company_unit
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information18 IS NOT NULL;
select
e.org_information_id,
e.org_information17 cbs_public_sector_org
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information17 IS NOT NULL;
select
e.org_information_id,
e.org_information19 full_sick_wage_paid
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information19 IS NOT NULL;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information20 IZA_Weekly_Hours
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information20 IS NOT NULL;
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
select
e.org_information_id,
e.org_information12 IZA_Monthly_Hours
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_ORG_INFORMATION'
and e.org_information12 IS NOT NULL;
select
e.org_information_id,
e.org_information1 iza_information
from
hr_organization_information e
where
e.organization_id=l_org_id and
e.org_information_context= 'NL_IZA_REPO_INFO'
and e.org_information1 IS NOT NULL
and e.org_information2 IS NOT NULL;
SELECT iza_org_id,lev from hr_organization_information e,(
SELECT l_org_id iza_org_id,0 lev from dual
UNION
SELECT distinct organization_id_parent
,level
FROM per_org_structure_elements pose
where pose.org_structure_version_id = l_org_struct_version_id
START WITH organization_id_child = l_org_id
CONNECT BY PRIOR organization_id_parent = organization_id_child)
where
e.organization_id=iza_org_id and
e.org_information_context= 'NL_IZA_REPO_INFO'
and e.org_information1 IS NOT NULL
and e.org_information2 IS NOT NULL
ORDER BY lev;
select
1
from
hr_organization_information e
where e.organization_id=l_org_id
and e.org_information_context = 'NL_AMI'
and e.org_information3=l_si_class
and l_date_earned between fnd_date.canonical_to_date(e.org_INFORMATION1)
and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),hr_general.END_OF_TIME);
select paa.organization_id
from per_all_assignments_f paa,fnd_sessions ses
where paa.assignment_id = p_assignment_id and
ses.effective_date between paa.effective_start_date and paa.effective_end_date and
session_id = userenv('sessionid');
SELECT paa.organization_id
FROM per_all_assignments_f paa
,fnd_sessions ses
WHERE paa.assignment_id = p_assignment_id
AND ses.effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND session_id = userenv ('sessionid');
SELECT hoi.org_information6 lhd_pro_override
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_org_id
AND hoi.org_information_context = 'NL_ORG_INFORMATION'
AND hoi.org_information6 IS NOT NULL;
SELECT paa.organization_id
FROM per_all_assignments_f paa
,fnd_sessions ses
WHERE paa.assignment_id = p_assignment_id
AND ses.effective_date
BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND session_id = userenv ('sessionid');
SELECT hoi.org_information7 benefi_rule
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_org_id
AND hoi.org_information_context = 'NL_ORG_INFORMATION'
AND hoi.org_information7 IS NOT NULL;