1 package body ben_profile_handler as
2 /* $Header: benprhnd.pkb 120.0.12020000.2 2012/11/15 11:38:58 amnaraya ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
10
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
11 Name
12 Profile Handler
13 Purpose
14 This package is used to handle setting of profile flags based on
15 data changes that have occurred on child component tables.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 07-OCT-1999 GPERRY 115.0 Created.
20 18-NOV-1999 GPERRY 115.1 Created.
21 05-MAY-2003 STEE 115.3 Change dynamic sql to use
22 bind variables. Bug 2939392.
23 04-Feb-2004 vvprabhu 115.4 Bug 3431740 Parameter p_oracle_schema added
24 to cursor cc_chk_table_exists in
25 event_handler,
26 the value is got by the
27 call to fnd_installation.get_app_info
28 15-Feb-2004 vvprabhu 115.8 Initialized l_application_short_name to BEN
29 15-Nov-2012 amnaraya 120.0.12020000.1 Bug 15876152: Changed all_tab_columns
30 reference to dba_tab_columns using synonyms
31
32 */
33 --------------------------------------------------------------------------------
34 g_package varchar2(80) := 'ben_profile_handler.';
35 --
36 procedure event_handler
37 (p_event in varchar2,
38 p_base_table in varchar2,
39 p_base_table_column in varchar2,
40 p_base_table_column_value in number,
41 p_base_table_reference_column in varchar2,
42 p_reference_table in varchar2,
43 p_reference_table_column in varchar2) is
44 --
45 l_proc varchar2(80) := g_package||'event_handler';
46 l_event varchar2(80) := upper(p_event);
47 l_dynamic_sql varchar2(32000);
48 l_rows number;
49 l_status varchar2(1);
50 l_industry varchar2(1);
51 l_application_short_name varchar2(30) := 'BEN';
52 l_oracle_schema varchar2(30);
53 l_return boolean;
54 --
55 --Bug 15876152: Changed the cursor select statement
56 cursor c_chk_table_exists(p_table in varchar2
57 ,p_column in varchar2
58 ,p_oracle_schema in varchar2
59 ) is
60 select 1
61 from all_tab_columns tab,
62 user_synonyms syn
63 where syn.synonym_name = p_table
64 and tab.owner = syn.table_owner
65 and tab.table_name = syn.table_name
66 and tab.column_name = p_column
67 and tab.owner = upper(p_oracle_schema);
68 --
69 begin
70 --
71 hr_utility.set_location('Entering '||l_proc,10);
72 --
73 -- Debugging parameters
74 --
75 --hr_utility.set_location('Event '||
76 -- p_event,10);
77 --hr_utility.set_location('Base Table '||
78 -- p_base_table,10);
79 --hr_utility.set_location('Base Table Column '||
80 -- p_base_table_column,10);
81 --hr_utility.set_location('Base Table Column Value '||
82 -- p_base_table_column_value,10);
83 --hr_utility.set_location('Base Table Reference Column '||
84 -- p_base_table_reference_column,10);
85 --hr_utility.set_location('Reference Table '||
86 -- p_reference_table,10);
87 --hr_utility.set_location('Reference Table Column '||
88 -- p_reference_table_column,10);
89 --
90 -- Parameter validation
91 --
92 hr_api.mandatory_arg_error(p_api_name => l_proc,
93 p_argument => 'event',
94 p_argument_value => p_event);
95 --
96 hr_api.mandatory_arg_error(p_api_name => l_proc,
97 p_argument => 'base_table',
98 p_argument_value => p_base_table);
99 --
100 hr_api.mandatory_arg_error(p_api_name => l_proc,
101 p_argument => 'base_table_column',
102 p_argument_value => p_base_table_column);
103 --
104 hr_api.mandatory_arg_error(p_api_name => l_proc,
105 p_argument => 'base_table_column_value',
106 p_argument_value => p_base_table_column_value);
107 --
108 hr_api.mandatory_arg_error(p_api_name => l_proc,
109 p_argument => 'base_table_reference_column',
110 p_argument_value => p_base_table_reference_column);
111 --
112 hr_api.mandatory_arg_error(p_api_name => l_proc,
113 p_argument => 'reference_table',
114 p_argument_value => p_reference_table);
115 --
116 hr_api.mandatory_arg_error(p_api_name => l_proc,
117 p_argument => 'reference_table_column',
118 p_argument_value => p_reference_table_column);
119 --
120 -- Check operation is valid
121 --
122 if l_event not in ('CREATE','DELETE','UPGRADE') then
123 --
124 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
125 fnd_message.raise_error;
126 --
127 end if;
128 --
129 -- Check for reference table existence.
130 --
131 -- Bug 3431740 Parameter l_oracle_schema added to cursor c_chk_table_exists, the value is got
132 -- by the following call
133 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
134 status => l_status,
135 industry => l_industry,
136 oracle_schema => l_oracle_schema);
137 --
138 open c_chk_table_exists(p_reference_table
139 ,p_reference_table_column,l_oracle_schema);
140 if c_chk_table_exists%notfound then
141 close c_chk_table_exists;
142 fnd_message.set_name('BEN','BEN_93388_NO_TAB_COL');
143 fnd_message.raise_error;
144 else
145 close c_chk_table_exists;
146 end if;
147 --
148 -- Check for base table existence.
149 --
150 open c_chk_table_exists(p_base_table
151 ,p_base_table_column,l_oracle_schema);
152 if c_chk_table_exists%notfound then
153 close c_chk_table_exists;
154 fnd_message.set_name('BEN','BEN_93388_NO_TAB_COL');
155 fnd_message.raise_error;
156 else
157 close c_chk_table_exists;
158 end if;
159
160 --
161 -- Rules
162 -- =====
163 -- The rules are as follows :
164 -- For create we update the flag if the query returns one row as thats the
165 -- one we have inserted. If the query returns more than one row then we don't
166 -- care we assume the update has happened already.
167 -- For delete we update the flag if the query returns zero rows as that means
168 -- the only existing row was the one we deleted. If the query returns more
169 -- than one row then we assume the update has already happened.
170 -- For upgrade we assume that the database could be in the incorrect state
171 -- thus we check and update whatever the result.
172 --
173 -- The SQL will look as follows :
174 -- select count(*)
175 -- from :reference_table
176 -- where :reference_table_column = :base_table_column_value
177 --
178 -- If this is succesful then depending on the mode the following will happen
179 --
180 -- update :base_table
181 -- set :base_table_column = :required_value
182 --
183 -- :required_value is set per the rules. If no rows exist then the flag is
184 -- set to N. if rows exist then the flag is set to Y.
185 --
186 l_dynamic_sql := 'select count(*)
187 from '||p_reference_table||' '||
188 'where '||p_reference_table_column||' = :1';
189 --
190 execute immediate l_dynamic_sql into l_rows using p_base_table_column_value;
191 --
192 -- Test for flag updates and if so update them
193 --
194 if l_event = 'CREATE' and
195 l_rows = 1 then
196 --
197 l_dynamic_sql := 'update '||p_base_table||' '||
198 'set '||p_base_table_reference_column||' = :1
199 where '||p_base_table_column||' = :2';
200 --
201 execute immediate l_dynamic_sql using 'Y',p_base_table_column_value;
202 --
203 elsif l_event = 'DELETE' and
204 l_rows = 0 then
205 --
206 l_dynamic_sql := 'update '||p_base_table||' '||
207 'set '||p_base_table_reference_column||' = :1
208 where '||p_base_table_column||' = :2';
209 --
210 execute immediate l_dynamic_sql using 'N', p_base_table_column_value;
211 --
212 elsif l_event = 'UPGRADE' and
213 l_rows >= 1 then
214 --
215 l_dynamic_sql := 'update '||p_base_table||' '||
216 'set '||p_base_table_reference_column||' = :1
217 where '||p_base_table_column||' = :2';
218 --
219 execute immediate l_dynamic_sql using 'Y', p_base_table_column_value;
220 --
221 elsif l_event = 'UPGRADE' and
222 l_rows = 0 then
223 --
224 l_dynamic_sql := 'update '||p_base_table||' '||
225 'set '||p_base_table_reference_column||' = :1
226 where '||p_base_table_column||' = :2';
227 --
228 execute immediate l_dynamic_sql using 'N', p_base_table_column_value;
229 --
230 end if;
231 --
232 hr_utility.set_location('Leaving '||l_proc,10);
233 --
234 end event_handler;
235 -----------------------------------------------------------------------
236 end ben_profile_handler;
[Home] [Help]