SQL Query to get customer account email addresses in one row in Oracle Apps R12 - Demonstrating XMLAGG Function

• Query for the customer account in Customer Screen

• Click on Customer Name to open that record
• We will write our query to get the below emails 

• Below is the query

SELECT

    xmlagg(

        xmlelement(

            e,                      --TAG NAME <E> Value </E>

            email_address || ', '   --TAG VALUE 

        )

    ).extract('//text()').getclobval() emails

FROM

    apps.hz_contact_points a

WHERE    1=1

    AND owner_table_name    = 'HZ_PARTIES'  --Table Name

    AND owner_table_id      = '12486218'    --party_id

    AND status              = 'A'           --active status

    ;