2 Replies Latest reply on Jun 3, 2019 6:25 AM by Bharat -Oracle

    NoDATA: SELECT * FROM EAO_RA_CUSTOMER_TRX_V;

    LPNO

      I have this problem using SQL:

       

      APPS.RA_CUSTOMER_TRX is a synonym for AR.RA_CUSTOMER_TRX_ALL:

        CREATE OR REPLACE SYNONYM "APPS"."RA_CUSTOMER_TRX" FOR "AR"."RA_CUSTOMER_TRX_ALL";

       

      The OEBS-Standard view RA_CUSTOMER_TRX_PARTIAL_V is based on data source APPS.RA_CUSTOMER_TRX, anyhow it does not deliver any data.

      I pinned down the data lack to

      SELECT * FROM RA_CUSTOMER_TRX;
      

      wihich doesn't deliver any data as well. This puzzles me, because RA_CUSTOMER_TRX is just a synonym for AR.RA_CUSTOMER_TRX_ALL

       

       

      Anyhow

      SELECT count(*) FROM RA_CUSTOMER_TRX_ALL;
      

      sees about our 500'000 data rows.

       

      with

      SELECT fnd_profile.value ('org_id')  FROM dual;
      

      81

      where 81 is my ORGANIZATION_ID, which I set in the context of TOAD.

       

      What is going on here? I'm puzzled. Actually both SQL should select the same data.

       

      Nachricht geändert durch LPNO: added missing info about context of fnd_profile.value ('org_id'). Corrected some typos in the view names.

        • 1. Re: NoDATA: SELECT * FROM EAO_RA_CUSTOMER_TRX_V;
          John_K

          RA_CUSTOMER_TRX is a synonym for RA_CUSTOMER_TRX_ALL however the synonym has a VPD policy applied to it for MOAC.

           

          select * from dba_policies where object_name = 'RA_CUSTOMER_TRX'
          

           

          To select from VPD secured tables need to initialize your apps context using

           

          fnd_global.initialize() 
          

           

          with a valid responsibility for your user. That should let you access everything in your table. If you also need to query in a secured manner, you can call

           

          mo_global.init()
          

           

          which initializes the security policy.

          • 2. Re: NoDATA: SELECT * FROM EAO_RA_CUSTOMER_TRX_V;
            Bharat -Oracle

            Hi

             

            You need to set org context before fetching from ra_customer_trx

             

            exec MO_GLOBAL.SET_POLICY_CONTEXT('S', &org_id);

             

            Best Regards

            Bharat