Oracle Virtual Private Database

-
What is VPD
VPD combines these two features, enabling you to enforce security policies to control access at the row level. This control is based on application or session attributes, which can be made available during execution.


CREATE OR REPLACE FUNCTION hide_sal_comm (
 v_schema IN VARCHAR2, 
 v_objname IN VARCHAR2)

RETURN VARCHAR2 AS
con VARCHAR2 (200);

BEGIN
-- con := 'LINE_TYPE_LOOKUP_CODE=''ITEM''';
con := 'INVOICE_ID=81552';
 RETURN (con);
END hide_sal_comm;


BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema     => 'apps', 
  object_name       => 'AP_INVOICE_LINES_ALL',
  policy_name       => 'hide_sal_policy', 
  policy_function   => 'hide_sal_comm',
  sec_relevant_cols => null);
END;


BEGIN
   -- to drop a existing policy
   DBMS_RLS.drop_POLICY (object_schema   => 'APPS',
                         object_name     => 'AP_INVOICE_LINES_ALL',
                         policy_name     => 'hide_sal_policy');
END;

If you select 

select *  from AP_INVOICE_LINES_ALL only you can see 'INVOICE_ID=81552'

Comments

Post a Comment

Popular posts from this blog

Oracle Shipping Network SQL Query

-
Useful Script for Oracle Shipping Network , I wrote custom function for GL Code , you may find below SELECT a.from_organization_code, a.from_organization_name,        a.to_organization_code, a.to_organization_name,  DECODE (a.intransit_type,                  2, 'Intransit',                  1, 'Direct'                 ) "Transfer Type",        (SELECT meaning           FROM apps.mfg_lookups          WHERE lookup_type = 'MTL_FOB_POINT'            AND lookup_code = a.fob_point) fob_point,        a.elemental_visibility_enabled, DECODE (a.routing_header_id,                  1, 'Standard',                  2, 'Insp...
Click for more

Oracle Ar Invoice Numbering Sequence alter to avoid missing in between by cache

-
1) Query up your invoice source , which user have reported Jump in number. Navigate>Setup>Invoice>Source 2) Execute the following script using your source as in step 1.   SELECT batch_source_id, org_id, name FROM ra_batch_sources_all WHERE name LIKE '<source>' ;   or use this query if you have access from database.   SELECT 'alter sequence AR.' ||seq.sequence_name|| ' nocache;' FROM ( SELECT name bsname, org_id org, 'RA_TRX_NUMBER_' ||BATCH_SOURCE_ID|| '_' ||org_id|| '_S' seqname FROM RA_batch_sources_all ) src, dba_sequences seq WHERE src.seqname=seq.sequence_name AND seq.cache_size; ORDER BY org /     3) Using the batch_source_id from the above query , execute the followingscript:   SELECT sequence_owner, sequence_name, cache_size FROM all_sequences WHERE sequence_name LIKE 'RA_TRX_NUMBER%1146%' ;     4) Execute the following:   ALTER sequence SEQ...
Click for more

Advance Supply Chaining Scripts - ASCP Full Pegging Concept Script

-
ASCP Pegging Script SELECT DISTINCT msc.organization_code m_organization_code,                       msc.category_name m_category_name,                       item_segments m_item_segments,                       msc.description m_description,                       order_type_text m_order_type_text,                       TO_CHAR (new_due_date) m_new_due_date,                       TO_CHAR (quantity_rate) m_quantity_rate,                                             --                TO_CHAR (msc.disposition_...
Click for more