BI Publisher – Get Opportunity Info With account and contact name and phone

        
            SELECT
 
            o.NAME as Opty_Name,
             
            o.OPTY_ID,
             
            o.CUST_PARTY_ID,
             
            o.PR_CON_PARTY_ID,
             
            (select PARTY_NAME FROM HZ_PARTIES WHERE PARTY_ID = o.CUST_PARTY_ID) as AccountName,
             
            (select PARTY_NAME FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as ContactName,
             
            (select EMAIL_ADDRESS FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as Email,
             
            (select PRIMARY_PHONE_COUNTRY_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as cc,
             
            (select PRIMARY_PHONE_AREA_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as ar,
             
            (select PRIMARY_PHONE_NUMBER FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as phone,
             
            (select PRIMARY_PHONE_EXTENSION FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as ext,
             
            (select CITY FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as CITY,
             
            r.INVENTORY_ITEM_ID,
             
            (select DESCRIPTION FROM QSC_PRODUCTS_TL WHERE INVENTORY_ITEM_ID = r.INVENTORY_ITEM_ID AND LANGUAGE = 'US' AND rownum = 1) as Product,
             
            r.QTY,
             
            r.UNIT_PRICE,
             
            CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT('(',(select PRIMARY_PHONE_COUNTRY_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID)),')'),' ('),(select PRIMARY_PHONE_AREA_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID)), ') '), (select PRIMARY_PHONE_NUMBER FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID)),' '), (select PRIMARY_PHONE_EXTENSION FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID))
             
            as FormatedPhoneNumber,
             
            CURRENT_DATE
             
            FROM
             
            MOO_OPTY o INNER JOIN
             
            MOO_REVN  r ON (o.OPTY_ID = r.OPTY_ID)
             
            WHERE
             
            o.OPTY_ID = :opty_id
Leave a Reply Cancel reply
