[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p_org_id organization_id_parent
,0 lev
FROM dual
WHERE p_org_exists_in_hierarchy = 'Y'
UNION
SELECT organization_id_parent
,level lev
FROM per_org_structure_elements
WHERE org_structure_version_id = p_org_structure_version_id
START WITH organization_id_child = p_org_id
CONNECT BY PRIOR organization_id_parent = organization_id_child
AND org_structure_version_id = p_org_structure_version_id
ORDER BY lev;
SELECT p_org_id organization_id_parent
,0 lev
FROM dual
WHERE p_org_exists_in_hierarchy = 'Y'
UNION
SELECT organization_id_parent
,level lev
FROM per_org_structure_elements
WHERE org_structure_version_id = p_org_structure_version_id
START WITH organization_id_child = p_org_id
CONNECT BY PRIOR organization_id_parent = organization_id_child
AND org_structure_version_id = p_org_structure_version_id
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 = p_organization_id
AND inf.organization_id = org.business_group_id
AND inf.org_information_context = 'DE_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 = p_organization_structure_id
AND ses.session_id = USERENV('sessionid')
AND ses.effective_date BETWEEN sv.date_from AND NVL(sv.date_to, TO_DATE('31/12/4712','DD/MM/YYYY'))
ORDER BY sv.version_number DESC;
SELECT se.organization_id_child
FROM per_org_structure_elements se
WHERE se.org_structure_version_id = p_org_structure_version_id
AND (se.organization_id_parent = p_organization_id OR
se.organization_id_child = p_organization_id);
SELECT SUBSTR(org_information1, 1, 30) cc
,SUBSTR(org_information1, 1, 30) eb
,SUBSTR(org_information2, 1, 30) pb
,org_information_context ctx
FROM hr_organization_units d
,hr_organization_information e
WHERE d.organization_id = e.organization_id
AND d.organization_id = p_org_id
AND e.org_information_context IN ('DE_CHAMBER_CONTRIBUTION','DE_HR_ORG_INFO');
select HL.ORG_LINK_INFORMATION1 status,
HL.CHILD_ORGANIZATION_ID child_org_id,
HL.ORG_LINK_INFORMATION2 Class_Of_Risk,
HL.ORG_LINK_INFORMATION3 Membership_Number,
HU.NAME Name
from
HR_DE_ORGANIZATION_LINKS HL,
HR_ORGANIZATION_UNITS HU
where
Parent_Organization_id = c_org_id -- the internal org id
and Org_link_information_category = 'DE_LIABILITY_INSURANCE'
and HL.ORG_LINK_INFORMATION1 <> 'DE_INACTIVE'
and HL.CHILD_ORGANIZATION_ID = HU.ORGANIZATION_ID;
select HL.ORG_LINK_INFORMATION1 status,
Rpad(HL.CHILD_ORGANIZATION_ID, 15, ' ') child_org_id,
HL.ORG_LINK_INFORMATION2 Class_Of_Risk,
HL.ORG_LINK_INFORMATION3 Membership_Number,
rpad(HU.NAME, 240, ' ') Name
from
HR_DE_ORGANIZATION_LINKS HL,
HR_ORGANIZATION_UNITS HU
where
Parent_Organization_id = c_org_id -- the internal org id
and Org_link_information_category = 'DE_LIABILITY_INSURANCE'
and HL.CHILD_ORGANIZATION_ID = HU.ORGANIZATION_ID;
SELECT unique(organization_id) INTO l_org_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND primary_flag = 'Y'
AND p_incident_date BETWEEN effective_start_date and effective_end_date;
SELECT substr(segment2,1,5), substr(segment3,1,15) INTO l_exempt, l_liab_prov FROM Hr_soft_coding_keyflex
WHERE soft_coding_keyflex_id =
(SELECT unique(soft_coding_keyflex_id) FROM per_assignments_f WHERE assignment_id = p_assignment_id and p_incident_date BETWEEN effective_start_date AND effective_end_date);
SELECT org.name, NVL(orl.org_link_information3, 'NULL') INTO l_name, l_membership_no
FROM hr_de_organization_links orl, hr_organization_units org
WHERE orl.parent_organization_id = l_org_id
AND orl.child_organization_id = l_liab_prov
AND org.organization_id = l_liab_prov;
SELECT rpad(nvl(LOC.location_id, 999999), 15, ' ') location_id,
rpad(nvl(LOC.address_line_1, 'XXXXXX'), 240, ' ') address_line_1,
rpad(nvl(LOC.address_line_2, 'XXXXXX'), 240, ' ') address_line_2,
rpad(nvl(LOC.address_line_3, 'XXXXXX'), 240, ' ') address_line_3,
rpad(nvl(LOC.town_or_city, 'XXXXXX'), 30, ' ') town_or_city,
rpad(nvl(LOC.country, 'XXXXXX'), 60, ' ') country,
rpad(nvl(LOC.postal_code, 'XXXXXX'), 30, ' ') postal_code
-- INTO l_loc_id, l_add_1, l_add_2, l_add_3, l_town , l_country, l_post
-- INTO location_table
FROM hr_locations LOC WHERE LOC.location_id =
(select ORG.location_id
FROM hr_organization_units ORG WHERE ORG.organization_id = c_org_id);
SELECT unique(organization_id) INTO l_org_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND primary_flag = 'Y'
AND p_incident_date BETWEEN effective_start_date and effective_end_date;
SELECT nvl(l_cc,' ')
,nvl(l_e_bet,' ')
,nvl(l_p_bet,' ')
INTO l_cc, l_e_bet, l_p_bet
FROM dual;
SELECT Rpad(HL.CHILD_ORGANIZATION_ID, 15, ' ') child_org_id,
Rpad(HU.NAME, 240, ' ') Name
FROM
HR_DE_ORGANIZATION_LINKS HL,
HR_ORGANIZATION_UNITS HU
WHERE
Parent_Organization_id = c_org_id -- the internal org id
AND Org_link_type = 'DE_WRK_INC_SUP_OFF'
AND HL.CHILD_ORGANIZATION_ID = HU.ORGANIZATION_ID;
SELECT unique(organization_id) INTO l_org_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND primary_flag = 'Y'
AND p_incident_date BETWEEN effective_start_date and effective_end_date;