Oracle EBS: 擷取Oracle EBS R12中Legal Entity, Business group資料

 擷取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