Search This Blog

Wednesday, July 13, 2011

To get the legal entity

SELECT xep.legal_entity_id, NAME, hl.address_line_1, hl.town_or_city,
       hl.postal_code, hl.country
  FROM apps.xle_entity_profiles xep,
       apps.xle_registrations xr,
       hr_locations hl,
       hz_geographies b,
       hz_parties hp,
       ra_customer_trx_all rct
 WHERE xep.geography_id = b.geography_id
   AND xr.location_id = hl.location_id
   AND xr.source_id = xep.legal_entity_id
   AND xr.identifying_flag = 'Y'
   AND xr.source_table = 'XLE_ENTITY_PROFILES'
   AND SYSDATE BETWEEN xr.effective_from AND NVL (xr.effective_to, SYSDATE)
   AND xep.transacting_entity_flag = 'Y'
   AND hp.party_id = xep.party_id
   AND xep.legal_entity_id = rct.legal_entity_id
   AND customer_trx_id = :customer_trx_id



SELECT ou.organization_id org_id, mp.organization_id inv_org_id,
          le.ledger_id, legal_entity_id, le.legal_entity_name,
          ou.NAME ORGANIZATION, mp.organization_code inv_org
     FROM financials_system_params_all fspa,
          mtl_parameters mp,
          hr_operating_units ou,
          xle_le_ou_ledger_v le
    WHERE fspa.inventory_organization_id = mp.organization_id
      AND fspa.org_id = ou.organization_id
      AND ou.organization_id = le.operating_unit_id
      AND ou.NAME LIKE 'GEC%'

------------
Base table: XLE_ENTITY_PROFILES
Posted by at
Labels: ,

2 comments:

  1. PrashantNovember 28, 2013 at 4:12 AM

    Thanks a lot. One small comment here. In my project I find xr.effective_from NULL. I dont know if that is right.
    So, I had to NVL it.

    ReplyDelete
  2. UnknownSeptember 9, 2015 at 5:40 AM

    Hi Mallareddy,
    Good Evening!!

    I am developing a HRMS Report and it is giving more than one Legal Entity for each Employee. Could you please very the code and tell me where I am gone wrong.


    SELECT DISTINCT le.legal_entity_id,le.LEGAL_ENTITY_NAME ,--xep.legal_entity_id,xep.name legal_entity,
    haou.NAME organisation, haou.type,a.ass_attribute3 department,
    fu.user_name, fu.description full_name,
    app.application_name ,
    tl.responsibility_name,
    TO_CHAR (furgd.start_date,'DD-MON-YYYY') Responsibility_Start_Date,
    TO_CHAR (furgd.end_date, 'DD-MON-YYYY') Responsibility_End_Date
    FROM apps.per_all_people_f p,
    apps.per_all_assignments_f a,
    apps.pay_people_groups ppg,
    apps.hr_all_positions_f hap,
    apps.hr_operating_units hou,
    apps.hr_all_organization_units haou,
    --xle_entity_profiles xep,
    xle_le_ou_ledger_v le,
    fnd_user fu,
    fnd_user_resp_groups_direct furgd,
    fnd_responsibility_tl tl,
    fnd_application_tl app
    WHERE p.person_id = a.person_id
    AND SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
    AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
    AND a.primary_flag = 'Y'
    AND a.people_group_id = ppg.people_group_id
    AND hap.position_id(+) = a.position_id
    AND hou.business_group_id = p.business_group_id
    AND hou.business_group_id = a.business_group_id
    -- AND hou.default_legal_context_id =xep.legal_entity_id
    AND hou.organization_id = le.operating_unit_id
    AND haou.organization_id = p.business_group_id
    AND fu.employee_id = p.person_id
    --AND fu.employee_id(+) = p.person_id
    and fu.user_id = furgd.user_id
    --and fu.user_id = furgd.user_id(+)
    AND furgd.responsibility_id = tl.responsibility_id
    AND tl.application_id = app.application_id
    AND fu.user_name like '%LAMBERTB%'
    ORDER BY le.LEGAL_ENTITY_NAME,haou.NAME,fu.user_name,tl.responsibility_name

    Thanks,
    Manjunath

    ReplyDelete

Subscribe to: Post Comments (Atom)