擷取Oracle EBS R12中Legal Entity, Business group資料
Query to fetch Business group Details - 1
SELECT business_group_id,
organization_id,
NAME,
date_from,
date_to,
internal_address_line,
location_id,
comments,
default_start_time,
default_end_time,
working_hours,
frequency,
short_name,
method_of_generation_emp_num,
method_of_generation_apl_num,
grade_structure,
people_group_structure,
job_structure,
cost_allocation_structure,
position_structure,
legislation_code,
currency_code,
security_group_id,
enabled_flag,
competence_structure,
method_of_generation_cwk_num
FROM per_business_groups;
Query to fetch Business group Details - 2
SELECT o.organization_id,
o.organization_id,
otl.NAME,
o.date_from,
o.date_to,
o.internal_address_line,
o.location_id,
o.comments
FROM hr_all_organization_units o,
hr_all_organization_units_tl otl,
hr_organization_information o2,
hr_organization_information o3,
hr_organization_information o4
WHERE o.organization_id = otl.organization_id
AND o.organization_id = o2.organization_id(+)
AND o.organization_id = o3.organization_id
AND o.organization_id = o4.organization_id
AND o3.org_information_context = 'Business Group Information'
AND o2.org_information_context(+) = 'Work Day Information'
AND o4.org_information_context = 'CLASS'
AND o4.org_information1 = 'HR_BG'
AND o4.org_information2 = 'Y';
Query to fetch Legal Entity Details
SELECT xle_firstpty.NAME,
xle_firstpty.activity_code,
xle_firstpty.sub_activity_code,
xle_firstpty.registration_number,
xle_firstpty.effective_from,
xle_firstpty.location_id,
xle_firstpty.address_line_1,
xle_firstpty.address_line_2,
xle_firstpty.address_line_3,
xle_firstpty.town_or_city,
xle_firstpty.region_1,
xle_firstpty.region_2,
xle_firstpty.region_3,
xle_firstpty.postal_code,
xle_firstpty.country,
xle_firstpty.address_style,
xle_cont.contact_name,
xle_cont.contact_legal_id,
xle_cont.title,
xle_cont.job_title,
xle_cont.role
FROM xle_firstparty_information_v xle_firstpty,
xle_legal_contacts_v xle_cont
WHERE 1 = 1
AND XLE_FIRSTPTY.LEGAL_ENTITY_ID = C_REP_ENTITY_ID
AND xle_firstpty.legal_entity_id = xle_cont.entity_id(+);
Query to fetch legal entity details along with OU details
SELECT DISTINCT hrl.country,
hroutl_bg.NAME bg,
hroutl_bg.organization_id,
lep.legal_entity_id,
lep.NAME legal_entity,
hroutl_ou.NAME ou_name,
hroutl_ou.organization_id org_id,
hrl.location_id,
hrl.location_code,
glev.flex_segment_value
FROM xle_entity_profiles lep,
xle_registrations reg,
hr_locations_all hrl,
hz_parties hzp,
fnd_territories_vl ter,
hr_operating_units hro,
hr_all_organization_units_tl hroutl_bg,
hr_all_organization_units_tl hroutl_ou,
hr_organization_units gloperatingunitseo,
gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag = 'Y'
AND lep.party_id = hzp.party_id
AND lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND hrl.location_id = reg.location_id
AND reg.identifying_flag = 'Y'
AND ter.territory_code = hrl.country
AND lep.legal_entity_id = hro.default_legal_context_id
AND gloperatingunitseo.organization_id = hro.organization_id
AND hroutl_bg.organization_id = hro.business_group_id
AND hroutl_ou.organization_id = hro.organization_id
AND glev.legal_entity_id = lep.legal_entity_id;
Ref:
1.Query to fetch Legal Entity, Business group details in Oracle Apps R12 - per_business_groups,xle_firstparty_information_v
http://www.shareoracleapps.com/2014/08/query-to-fetch-legal-entity-business-groups-ou-r12-oracle-apps.html
