0 Replies Latest reply on Apr 17, 2019 12:27 PM by 3975581

    I want to check the user name against a value and nested query

    3975581

      In the below code what i am trying to do is limit results by either the wkf.email_address of the requester or the wkf.email_address of the buyer

       

      However i get BUYER_EMAIL not a a valid identifier.

       

      fairly new to sql so i am sure this is incorrect syntax.

       

      It works on the requester email only ie

       

      AND    WKF.EMAIL_ADDRESS = :xdo_user_name

      instead of

      AND    WKF.EMAIL_ADDRESS = :xdo_user_name OR BUYER_EMAIL  = :xdo_user_name

       

      One is the manager of the other so I need to show either that requestors PO only or all that relate to the Buyer multiple requestors.

       

      SELECT DISTINCT

        APA.INVOICE_NUM

      , APA.INVOICE_AMOUNT

      , TO_CHAR(APA.INVOICE_DATE,'DD/MM/YYYY') INVOICE_DATE

      , SUP.VENDOR_NAME

      , SSV.VENDOR_SITE_CODE

      --

      , POH.DOCUMENT_NUM   PO_NUMBER

      , POH.COMMENTS

      , WKF.EMAIL_ADDRESS as REQUESTOR_EMAIL

      , WKF.FULL_NAME as REQUESTOR_NAME

      ,(Select

                WKF.EMAIL_ADDRESS

           From PER_WORKFORCE_X           WKF

               ,    PO_HEADERS_INTERFACE      POH

           WHERE POH.PO_HEADER_ID = PLO.PO_HEADER_ID

          AND POH.AGENT_NAME = WKF.FULL_NAME) as BUYER_EMAIL

      --

      FROM

        AP_INVOICE_LINES_ALL      AIL

      , AP_INVOICES_ALL           APA

      , POZ_SUPPLIERS_V           SUP

      , POZ_SUPPLIER_SITES_V      SSV

      --

      , PO_HEADERS_INTERFACE      POH

      , PO_LINES_ALL              PLO

      , PO_LINE_LOCATIONS_ALL     PLL

      , PO_DISTRIBUTIONS_ALL        PDA

      --

      , AP_HOLDS_ALL              HOLDS

      , PER_WORKFORCE_X           WKF

       

      WHERE

             APA.INVOICE_ID       = AIL.INVOICE_ID

      AND    SUP.VENDOR_ID        = SSV.VENDOR_ID

      AND    APA.VENDOR_SITE_ID   = SSV.VENDOR_SITE_ID

       

      AND    POH.PO_HEADER_ID     = PLO.PO_HEADER_ID

      AND    PLO.PO_LINE_ID       = PLL.PO_LINE_ID

      AND    PLL.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID

       

       

      AND    PDA.DELIVER_TO_PERSON_ID = WKF.PERSON_ID

      --

      AND    APA.INVOICE_ID       = HOLDS.INVOICE_ID

      AND    PLL.LINE_LOCATION_ID = HOLDS.LINE_LOCATION_ID(+)

      AND    PDA.DELIVER_TO_PERSON_ID = WKF.PERSON_ID

      --

      AND    PDA.PO_DISTRIBUTION_ID = AIL.PO_DISTRIBUTION_ID

       

      AND    POH.DOCUMENT_NUM     NOT LIKE ('CP%')

      AND    SSV.VENDOR_SITE_CODE LIKE('%B')

      AND    WKF.EMAIL_ADDRESS = :xdo_user_name OR BUYER_EMAIL  = :xdo_user_name

       

      ORDER BY  WKF.FULL_NAME

      ,         POH.DOCUMENT_NUM

      ,         APA.INVOICE_NUM