1 PACKAGE BODY Bom_alt_desigs_policy AS
2 /* $Header: BOMSPOLB.pls 120.0 2005/05/25 06:08:48 appldev noship $*/
3 --
4 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BOMSPOLB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- Package Bom_alt_desigs_policy
14 -- This is the package used to set the fine-grained security
15 -- policy for bom_alternate_designators table
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 24-SEP-2003 Deepak Jebar Initial Creation
22 -- 31-MAR-2004 Deepu Corrected cursor to include policy_owner
23 -- 03-SEP-2004 Hari Gelli Modified the get_alt_predicate function to handle the structures of
24 -- 'Packaging Hierarchy'. Structures of this type can be viewed from the product
25 -- 'Advanced Product Catalog'
26 -- 23-DEC-2004 Hari Gelli Modifed the ADD/DROP policy functions to pass object schema name as BOM
27 --
28 PROCEDURE add_policy IS
29 p_policy_owner FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
30 l_status VARCHAR2(1);
31 l_industry VARCHAR2(1);
32 l_Prod_Schema VARCHAR2(30);
33
34 cursor c1 is
35 select 1
36 from all_policies
37 where object_owner = p_policy_owner
38 and object_name = 'BOM_ALTERNATE_DESIGNATORS'
39 and policy_name = 'STRUCT_TYPE_ALTS';
40
41 CURSOR prod_policy IS
42 SELECT 1 FROM All_Policies
43 WHERE Object_Owner = l_Prod_Schema
44 AND Object_Name = 'BOM_ALTERNATE_DESIGNATORS'
45 AND Policy_Name = 'STRUCT_TYPE_ALTS';
46
47 BEGIN
48 SELECT oracle_username
49 into p_policy_owner
50 from fnd_oracle_userid
51 where read_only_flag = 'U';
52
53 -- Checking whether policy is already defined under universal schema. (APPS)
54 FOR crec IN C1 LOOP
55 RETURN;
56 END LOOP;
57
58 IF NOT FND_INSTALLATION.GET_APP_INFO('BOM', l_status, l_industry, l_Prod_Schema) THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- Checking whether policy is already defined under product schema.
63 FOR pprec IN prod_policy LOOP
64 RETURN;
65 END LOOP;
66
67 DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA => l_Prod_Schema, -- We will add the policy to the BOM schema
68 OBJECT_NAME => 'BOM_ALTERNATE_DESIGNATORS',
69 POLICY_NAME => 'STRUCT_TYPE_ALTS',
70 --FUNCTION_SCHEMA => 'APPS',
71 POLICY_FUNCTION => 'Bom_alt_desigs_policy.get_alt_predicate',
72 STATEMENT_TYPES => 'SELECT');
73 EXCEPTION
74 WHEN OTHERS THEN
75 NULL;
76 END add_policy;
77
78 PROCEDURE drop_policy IS
79 l_status VARCHAR2(1);
80 l_industry VARCHAR2(1);
81 l_Prod_Schema VARCHAR2(30);
82 BEGIN
83
84 IF NOT FND_INSTALLATION.GET_APP_INFO('BOM', l_status, l_industry, l_Prod_Schema) THEN
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 BEGIN
89 DBMS_RLS.DROP_POLICY(--OBJECT_SCHEMA => l_Prod_Schema, -- We will drop the policy from the universal schema
90 OBJECT_NAME => 'BOM_ALTERNATE_DESIGNATORS',
91 POLICY_NAME => 'STRUCT_TYPE_ALTS');
92 EXCEPTION
93 WHEN OTHERS THEN
94 NULL;
95 END;
96 DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA => l_Prod_Schema, -- We will drop the policy from the BOM schema
97 OBJECT_NAME => 'BOM_ALTERNATE_DESIGNATORS',
98 POLICY_NAME => 'STRUCT_TYPE_ALTS');
99 EXCEPTION
100 WHEN OTHERS THEN
101 NULL;
102 END drop_policy;
103
104 FUNCTION get_alt_predicate( p_namespace in varchar2
105 , p_object in varchar2)
106 RETURN VARCHAR2 IS
107 l_return VARCHAR2(100);
108 l_current_appl_id varchar2(10);
109 l_pkghier_type_id VARCHAR2(100);
110 BEGIN
111 select sys_context('Struct_Type_Ctx', 'appl_id')
112 into l_current_appl_id
113 from dual;
114 if (l_current_appl_id = to_char(G_EAM_APPLICATION)) then
115 l_return := 'structure_type_id = SYS_CONTEXT(''Struct_Type_Ctx'',''struct_type_id'')';
116 elsif (l_current_appl_id <> to_char(G_EGO_APPLICATION)) then
117 l_pkghier_type_id := SYS_CONTEXT('Struct_Type_Ctx','pkg_struct_type_id');
118 if(l_pkghier_type_id IS NULL) THEN
119 l_return := '1 = 1';
120 else
121 l_return := 'structure_type_id <> ' || l_pkghier_type_id;
122 end if;
123 else
124 l_return := '1 = 1';
125 end if;
126 RETURN (l_return);
127 EXCEPTION
128 WHEN OTHERS THEN
129 l_return := '1 = 1';
130 RETURN (l_return);
131 END get_alt_predicate;
132
133 END Bom_alt_desigs_policy;
[Home] [Help]