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