Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Dilip Kuchipudi
    Policy Predicate ErrorAnswered
    Topic posted October 25, 2019 by Dilip KuchipudiRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Procurement reporting, Reports, Sample Reports 
    74 Views, 12 Comments
    Title:
    Policy Predicate Error
    Summary:
    When querying some tables and views we are getting Policy Predicate Error
    Content:

    Selecting data from per_person_names_f or hz_parties is giving a policy predicate error and so the data model is erring out.  Any suggestions please provide how to overcome this.

    Version:
    19C

    Best Comment

    Senthilrajan Vaithianathan

    Hi Dilip,

    I am able to query all these tables.

    The Error file you have attached is for "Datamodel SQL Explain Plan Report", let me know what exactly you are performing.

    Trying to query the data or you running a Explain plan to see the query performance? In case if you are trying to query the data, check if you are following the steps i have attached...

    Click 'OK' instead of 'Generate Explain Plan'

    Comment

     

    • nathan morgan (at Client)

      I think this is to do with the data security in the database because the tables are configured with a policy that will add a where clause to your query at run time to select from a security table so that you only see the rows that your are authorized to see as set up in the system configuration as a mapping from your user name to a list of things that you have. There is something wrong with the sql where clause it has added so it is throwing the error. Review your user in the security console.   

    • nathan morgan (at Client)

      FYI The policies are in data dictionary view ALL_POLICIES and the predicates in play are in V$VPD_POLICY 

      For more informations see
      Oracle® Database Database Security Guide 12c Release 2 (12.2) E85682-02 December 2017
      10 Using Oracle Virtual Private Database to Control Data Access
      https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/using-oracle-vpd-to-control-data-access.html#GUID-06022729-9210-4895-BF04-6177713C65A7

    • Dilip Kuchipudi

       Hello Nathan

      There is no where clause in my query.  I was just querying select * from poz_suppliers_v.

    • Senthilrajan Vaithianathan

      Hi Dilip,

      I am able to query all these tables.

      The Error file you have attached is for "Datamodel SQL Explain Plan Report", let me know what exactly you are performing.

      Trying to query the data or you running a Explain plan to see the query performance? In case if you are trying to query the data, check if you are following the steps i have attached...

      Click 'OK' instead of 'Generate Explain Plan'

      • Dilip Kuchipudi

        Hello Senthilrajan

        Thank you so much for your suggestions.  I was doing the Generate Explain Plan on the single view select and also on my joins as well and getting the Policy Predicate Error.  On further researching I found that I was joining the wrong attribute as well.  I was joining okc_k_headers_all_b.supplier_id=poz_suppliers_v.vendor_id.. On Trial and Error by my friend found that it should be okc_k_headers_all_b.party_id=poz_suppliers_v.vendor_id.  Still get the predicate error but getting the data and so moving on with it.  

        I wish there is somewhere documentation available about how the attributes are linked together.  You would think Supplier_id is Vendor_id.  If you know of any documentation which will clarify this I would really appreciate it.

        Thanks to you and Nathan for your suggestions.

        Dilip

    • nathan morgan (at Client)

      Dilip, if the table/view you select from has a row level security policy configured upon it then the where clause predicate is added by oracle automatically. Nathan

      • Dilip Kuchipudi

        Thank you so much Nathan.  I think it is the issue with Generate Explain Plan when selecting without any where clause.

        Appreciate your suggestions.

         

        Dilip

    • nathan morgan (at Client)

      Grand. FYI

      hz_parties has 5 policies on it calling functions in package hz_pii_security 

      -- test case -- expect rows but got none
      select all count(*) over (partition by null) as n, t.* 
      from hz_parties t

      -- is it a table or view or synonym?
      select all t.object_type, t.owner
      from all_objects t
      where (1=1)
      and (t.name = upper('hz_parties'))

      -- if a view what does it select from?
      select all count(*) over (partition by null) as n, t.referenced_owner, t.referenced_name, t.referenced_type
      from all_dependencies t
      where (1=1)
      and (t.name = upper('hz_parties'))
      order by null
      , t.referenced_owner, t.referenced_name, t.referenced_type

      -- what are the package functions from any policies on these objects?
      select all count(*) over (partition by null) as n, t.policy_name, t.object_name, t.package, t.function 
      from all_policies t 
      where (1=1)
      and (t.object_name like upper('%hz_parties%')) 
      order by null, t.policy_name, t.object_name

      PARTY_ADDRESS_PII_VIEW_ACCESS
      PARTY_CONSUMER_DATA_ACCESS
      PARTY_EMAIL_PII_VIEW_ACCESS
      PARTY_PHONE_PII_VIEW_ACCESS
      PERSON_PII_VIEW_ACCESS

      -- lets read the package specification
      select all t.text as s from all_source t where (t.name = upper('HZ_PII_SECURITY'))

      -- lets test the predicate you get from each of these 
      select all HZ_PII_SECURITY.PARTY_ADDRESS_PII_VIEW_ACCESS('FUSION','HZ_PARTIES') as s from dual t
      select all HZ_PII_SECURITY.PARTY_CONSUMER_DATA_ACCESS('FUSION','HZ_PARTIES') as s from dual t
      select all HZ_PII_SECURITY.PARTY_EMAIL_PII_VIEW_ACCESS('FUSION','HZ_PARTIES') as s from dual t
      select all HZ_PII_SECURITY.PARTY_PHONE_PII_VIEW_ACCESS('FUSION','HZ_PARTIES') as s from dual t
      select all HZ_PII_SECURITY.PARTY_PERSON_PII_VIEW_ACCESS('FUSION','HZ_PARTIES') as s from dual t

      • Dilip Kuchipudi

        Hello Nathan

        I don't have access to select from all_policies etc.

        If you know of any documentation how the attributes are interlinked between tables please let me know.  I was joining okc_k_headers_all_b.supplier_id=poz_suppliers_v.vendor_id.  Still get the predicate error but moving on as I am getting the data now.

        Thanks

        Dilip

    • nathan morgan (at Client)

      the data model is here

      Procurement Cloud
      Tables and Views for Oracle Procurement
      19C
      Part Number: F18778-0
      https://docs.oracle.com/en/cloud/saas/procurement/19c/oedmp/index.html

      view poz_suppliers_v
      https://docs.oracle.com/en/cloud/saas/procurement/19c/oedmp/supplier-model.html#pozsuppliersv-4542

      reads table poz_suppliers
      https://docs.oracle.com/en/cloud/saas/procurement/19c/oedmp/supplier-model.html#pozsuppliers-19580

      which has primary key
      VENDOR_ID

       

      • Dilip Kuchipudi

        Hello Nathan

        I have viewed this document but doesn't show how the attributes are linked to other tables as foreign keys.  For eg: OKC_K_HEADERS_ALL_B.SUPPLIER_ID=POZ_SUPPLIERS_V.PARTY_ID.  I did not find this relationship anywhere.

        Any idea if we have the 'About Record' information that we used to have in the Oracle Forms version of applicatioins.  This was a good way of determining the table names and column names.

        Thank you so much

        Dilip

        • nathan morgan (at Client)

          Yes you are correct. There is no "direct" relationship between entity supplier and entity contract header.  As you can see by reading the data model view poz_supplier_v is table HZ_PARTIES inner join to POZ_SUPPLIERS and left outer join to TCA_ADDITIONAL_NAMES.  The primary key on hz_parties is PARTY_ID. The foreign keys are listed in the documents after the column descriptions at the bottom. So you can see that OKC_K_HEADERS_ALL_B has a foreign key to hz_parties on primary_ent_party_id. This is the Primary Party that is entitled to services covered in the contract. So if you want to get contract header with details about the parent supplier of the primary party then you can do that by joining on PARTY_ID. Reference https://docs.oracle.com/en/cloud/saas/sales-and-b2b-service/19d/oedms/contracts.html#okckheadersallb-16907