168: SELECT org.organization_id legal_employer_id
169: ,org.name
170: ,org.location_id
171: ,hoi1.org_information1
172: FROM hr_all_organization_units org
173: ,hr_organization_information hoi1
174: WHERE org.organization_id = p_legal_employer_id
175: AND hoi1.organization_id (+) = org.organization_id
176: AND hoi1.org_information_context (+) = 'NO_LEGAL_EMPLOYER_DETAILS';
184: hou_lu.NAME local_unit_name,
185: hoi_lu.org_information1,
186: hoi_lu.org_information3,
187: hou_lu.location_id
188: FROM hr_all_organization_units hou_le,
189: hr_organization_information hoi_le,
190: hr_all_organization_units hou_lu,
191: hr_organization_information hoi_lu
192: WHERE hoi_le.organization_id = hou_le.organization_id
186: hoi_lu.org_information3,
187: hou_lu.location_id
188: FROM hr_all_organization_units hou_le,
189: hr_organization_information hoi_le,
190: hr_all_organization_units hou_lu,
191: hr_organization_information hoi_lu
192: WHERE hoi_le.organization_id = hou_le.organization_id
193: AND hou_le.organization_id = csr_v_legal_employer_id
194: AND hoi_le.org_information_context = 'NO_LOCAL_UNITS'
200: SELECT org.organization_id
201: ,org.name
202: ,org.location_id
203: ,hoi1.org_information1 sp_org_number
204: FROM hr_all_organization_units org
205: ,hr_organization_information hoi1
206: WHERE org.organization_id = p_sp_org_id
207: AND hoi1.organization_id (+) = org.organization_id
208: AND hoi1.org_information_context (+) = 'NO_STATEMENT_PROVIDER_DETAILS';
539: SELECT hoi_le.org_information1 local_unit_id,
540: hou_lu.NAME local_unit_name,
541: hoi_lu.org_information1,
542: hou_lu.location_id
543: FROM hr_all_organization_units hou_le,
544: hr_organization_information hoi_le,
545: hr_all_organization_units hou_lu,
546: hr_organization_information hoi_lu
547: WHERE hoi_le.organization_id = hou_le.organization_id
541: hoi_lu.org_information1,
542: hou_lu.location_id
543: FROM hr_all_organization_units hou_le,
544: hr_organization_information hoi_le,
545: hr_all_organization_units hou_lu,
546: hr_organization_information hoi_lu
547: WHERE hoi_le.organization_id = hou_le.organization_id
548: AND hou_le.organization_id = g_legal_employer_id
549: AND hoi_le.org_information_context = 'NO_LOCAL_UNITS'