Tuesday, 7 July 2015

How to find Legal Entity Address in Oracle Apps

SELECT    DECODE (hla.address_line_1, NULL, NULL, hla.address_line_1)
       || DECODE (hla.address_line_2, NULL, NULL, ', ' || hla.address_line_2)
       || DECODE (hla.address_line_3, NULL, NULL, ', ' || hla.address_line_3)
       || DECODE (hla.loc_information15,
                  NULL, NULL,
                  ', ' || hla.loc_information15
                 )
       || DECODE (hla.loc_information16,
                  NULL, NULL,
                  ', ' || hla.loc_information16
                 )
       || DECODE (ftv.territory_short_name,
                  NULL, NULL,
                  ', ' || ftv.territory_short_name
                 )
       || DECODE (hla.postal_code, NULL, NULL, ' - ' || hla.postal_code)  LE_Address
  FROM xle_entity_profiles etbp,
       xle_registrations reg,
       hr_locations_all hla,
       fnd_territories_vl ftv
 WHERE etbp.legal_entity_id = reg.source_id
   AND etbp.legal_entity_id = (SELECT   DISTINCT DEFAULT_LEGAL_CONTEXT_ID
                               FROM   HR_OPERATING_UNITS
                              WHERE   NAME = 'XX_OPERATING_UNIT')
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND identifying_flag = 'Y'
   AND hla.location_id = reg.location_id
   AND ftv.territory_code = hla.country;

No comments:

Post a Comment