[Home] [Help]
PACKAGE BODY: APPS.IEU_WORK_SOURCE_PVT
Source
1 PACKAGE BODY IEU_Work_Source_PVT AS
2 /* $Header: IEUWSAB.pls 120.0 2005/06/02 15:42:08 appldev noship $ */
3
4
5 --===================================================================
6 -- NAME
7 -- CREATE_action_map
8 --
9 -- PURPOSE
10 -- Private api to create action map
11 --
12 -- NOTES
13 -- 1. UWQ Admin will use this procedure to create action map
14 --
15 --
16 -- HISTORY
17 -- 8-may-2002 dolee Created
18 -- 14-Aug-2003 gpagadal updated
19
20 --===================================================================
21
22
23 PROCEDURE loadWorkSource(x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26 p_ws_id IN varchar2,
27 p_ws_type IN VARCHAR2,
28 p_ws_name IN VARCHAR2,
29 p_ws_code IN VARCHAR2,
30 p_ws_desc IN VARCHAR2,
31 p_ws_parent_id IN number,
32 p_ws_child_id IN number,
33 p_ws_dis_from IN VARCHAR2,
34 p_ws_dis_to IN VARCHAR2,
35 p_ws_dis_func IN VARCHAR2,
36 p_ws_object_code IN VARCHAR2,
37 p_ws_application_id IN varchar2,
38 p_ws_not_valid_flag IN VARCHAR2,
39 p_ws_dis_parent_flag IN VARCHAR2,
40 p_ws_profile_id IN varchar2,
41 p_ws_profile IN varchar2,
42 p_sqlValidation IN varchar2,
43 p_ws_task_rule_func IN varchar2,
44 r_mode IN VARCHAR2
45 ) AS
46
47 l_action_map_id NUMBER(15);
48 sql_stmt varchar2(2000);
49 l_count number:=0;
50 l_language ieu_uwqm_work_sources_tl.language%type;
51 x_rowid varchar2(20000);
52
53 l_ws_id number;
54 l_ws_assoc_prop_id number;
55 l_profile_id number;
56 l_applId NUMBER :=696;
57 l_yes VARCHAR2(1);
58 l_no VARCHAR2(1);
59 l_end VARCHAR2(200) := null;
60 l_temp_date VARCHAR2(15);
61 l_appl_short_name varchar2(15);
62 l_ws_not_valid_flag varchar2(1);
63 l_deactive_time Date;
64
65
66 BEGIN
67 l_yes := 'Y';
68 l_no := 'N';
69 l_ws_not_valid_flag := 'Y';
70 l_deactive_time := sysdate-1;
71 fnd_msg_pub.delete_msg();
72 x_return_status := fnd_api.g_ret_sts_success;
73 FND_MSG_PUB.initialize;
74 x_msg_data := '';
75 select to_char(sysdate, 'yyyy/mm/dd') into l_temp_date from dual;
76 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
77 -- for update description and dis function for not active case,
78 -- do not check ws object code
79 -- check ws object code only when deactive -> active
80 if (r_mode = 'update' ) then
81 EXECUTE IMMEDIATE 'select not_valid_flag from ieu_uwqm_work_sources_b '||
82 ' where ws_id =:1'
83 into l_ws_not_valid_flag
84 using p_ws_id;
85 end if;
86
87 if (r_mode = 'create' or (l_ws_not_valid_flag = 'Y' and p_ws_not_valid_flag = 'N')) then
88 validateObj(x_return_status, x_msg_count, x_msg_data, p_ws_name,p_ws_code, p_ws_parent_id, p_ws_child_id, r_mode);
89 end if;
90 IF (x_return_status = 'S') then
91 --x_msg_data := x_msg_data || 'validate is ok, dis function is '||p_ws_dis_func||', not_valid_flag is '||
92 -- p_ws_not_valid_flag||', p_ws_id is '|| p_ws_id||' user_id is '|| fnd_global.user_id ||
93 -- ' login id is '|| fnd_global.login_id || ', profile id is '||p_ws_profile_id||',profile is '||
94 -- p_ws_profile||' p_sqlValidation is '||p_sqlValidation;
95
96 IF (r_mode = 'update') then
97 IEU_UWQM_WORK_SOURCES_PKG.update_row(
98 p_ws_id,
99 p_ws_type,
100 p_ws_dis_to,
101 p_ws_dis_from,
102 p_ws_dis_func,
103 p_ws_not_valid_flag,
104 p_ws_object_code,
105 p_ws_name,
106 p_ws_desc,
107 p_ws_profile_id,
108 p_ws_application_id);
109
110 IF (p_ws_type = 'ASSOCIATION') then
111 EXECUTE IMMEDIATE 'select ws_association_prop_id from ieu_uwqm_ws_assct_props '||
112 ' where parent_ws_id = :1 and child_ws_id = :2 and ws_id = :3'
113 into l_ws_assoc_prop_id
114 USING p_ws_parent_id, p_ws_child_id, p_ws_id;
115
116 IEU_UWQM_WS_ASSCT_PROPS_PKG.update_row(
117 p_ws_association_prop_id =>l_ws_assoc_prop_id,
118 p_parent_ws_id => p_ws_parent_id,
119 p_child_ws_id =>p_ws_child_id ,
120 p_dist_st_based_on_parent_flag => p_ws_dis_parent_flag,
121 p_ws_id => p_ws_id,
122 p_tasks_rules_function => p_ws_task_rule_func);
123 END if;
124
125 -- when active or deactive, modified the end_active_date for profile_options
126 if (l_ws_not_valid_flag = 'N' and p_ws_not_valid_flag = 'Y') then
127 sql_stmt := 'update FND_PROFILE_OPTIONS ' ||
128 ' set end_date_active= :1 '||
129 ' , last_updated_by = :2 '||
130 ' , last_update_login = :3 '||
131 ', last_update_date = :4 '||
132 ' where profile_option_name = :5 ';
133 EXECUTE IMMEDIATE sql_stmt
134 USING l_deactive_time,fnd_global.user_id,fnd_global.login_id,sysdate, p_ws_profile_id;
135 ELSE IF (l_ws_not_valid_flag = 'Y' and p_ws_not_valid_flag = 'N') then
136 sql_stmt := 'update FND_PROFILE_OPTIONS ' ||
137 ' set end_date_active= :1 '||
138 ' , last_updated_by = :2 '||
139 ' , last_update_login = :3 '||
140 ', last_update_date = :4 '||
141 ' where profile_option_name = :5 ';
142 EXECUTE IMMEDIATE sql_stmt
143 USING l_end,fnd_global.user_id,fnd_global.login_id,sysdate, p_ws_profile_id;
144 END if;--active
145 end if;--deactive
146
147 else-- create case
148 EXECUTE IMMEDIATE 'select application_short_name from fnd_application where application_id = :1 '
149 into l_appl_short_name
150 using p_ws_application_id;
151
152 -- a. update/insert fnd_profile_options and fnd_profile_options_tl
153 FND_PROFILE_OPTIONS_PKG.LOAD_ROW (
154 x_profile_name => p_ws_profile_id ,
155 x_owner => fnd_global.user_id,
156 x_application_short_name => l_appl_short_name,
157 x_user_profile_option_name =>p_ws_profile,
158 x_description => null,
159 x_user_changeable_flag => l_no,
160 x_user_visible_flag => l_no,
161 x_read_allowed_flag => l_yes,
162 x_write_allowed_flag => l_yes,
163 x_site_enabled_flag => l_yes,
164 x_site_update_allowed_flag => l_yes,
165 x_app_enabled_flag => l_yes,
166 x_app_update_allowed_flag => l_yes,
167 x_resp_enabled_flag => l_yes,
168 x_resp_update_allowed_flag => l_yes,
169 x_user_enabled_flag => l_yes,
170 x_user_update_allowed_flag => l_yes,
171 x_start_date_active => l_temp_date,
172 x_end_date_active => null,
173 x_sql_validation => p_sqlValidation);
174
175 -- b. insert ieu_uwqm_work_sources_b and tl
176 select IEU_UWQM_WORK_SOURCES_B_S1.NEXTVAL into l_ws_id from sys.dual;
177 IEU_UWQM_WORK_SOURCES_PKG.load_row(
178 l_ws_id,
179 p_ws_type,
180 p_ws_dis_to,
181 p_ws_dis_from,
182 p_ws_dis_func,
183 p_ws_not_valid_flag,
184 p_ws_object_code,
185 p_ws_name,
186 p_ws_desc,
187 'ORACLE',
188 p_ws_code,
189 p_ws_profile_id,
190 p_ws_application_id,
191 'N');
192
193
194 -- c. if this is association type, insert ieu_uwqm_ws_assct_props
195 IF (p_ws_parent_id <> '0') then
196 select IEU_UWQM_WS_ASSCT_PROPS_S1.NEXTVAL into l_ws_assoc_prop_id from sys.dual;
197
198 IEU_UWQM_WS_ASSCT_PROPS_PKG.insert_row(
199 x_rowid => x_rowid,
200 p_ws_association_prop_id =>l_ws_assoc_prop_id,
201 p_parent_ws_id => p_ws_parent_id,
202 p_child_ws_id =>p_ws_child_id ,
203 p_dist_st_based_on_parent_flag => p_ws_dis_parent_flag,
204 p_ws_id => l_ws_id,
205 p_tasks_rules_function => p_ws_task_rule_func);
206
207
208 END IF ;-- props table
209
210 END if;
211 else
212 x_return_status := fnd_api.g_ret_sts_error;
213 x_msg_count := x_msg_count+1;
214 x_msg_data := x_msg_data||'IEU_PROV_WS_CREATE_ERROR$';
215 END if;
216 commit;
217 EXCEPTION
218 WHEN fnd_api.g_exc_error THEN
219 ROLLBACK;
220 x_return_status := fnd_api.g_ret_sts_error;
221 x_msg_data := x_msg_data || ' , fnd_api.g_exc_error.';
222
223
224 WHEN fnd_api.g_exc_unexpected_error THEN
225 ROLLBACK;
226 x_return_status := fnd_api.g_ret_sts_unexp_error;
227 x_msg_data := x_msg_data || ' , fnd_api.g_exc_unexpected_error.';
228
229 WHEN no_data_found THEN
230 ROLLBACK;
231 x_return_status := fnd_api.g_ret_sts_unexp_error;
232 x_msg_data := x_msg_data || ' , no_data_found.';
233
234
235 WHEN OTHERS THEN
236 ROLLBACK;
237 x_return_status := fnd_api.g_ret_sts_unexp_error;
238 x_msg_data := x_msg_data || ' , others.'|| sqlerrm;
239
240 END loadWorkSource;
241
242 PROCEDURE validateObj (x_return_status OUT NOCOPY VARCHAR2,
243 x_msg_count OUT NOCOPY NUMBER,
244 x_msg_data OUT NOCOPY VARCHAR2,
245 p_ws_name IN Varchar2,
246 p_ws_code IN varchar2,
247 p_ws_parent_id IN varchar2,
248 p_ws_child_id IN varchar2,
249 r_mode IN VARCHAR2
250 ) is
251 l_count NUMBER :=0;
252 l_not_valid_flag varchar2(1);
253
254
255 begin
256 l_not_valid_flag := 'N';
257 fnd_msg_pub.delete_msg();
258 x_return_status := fnd_api.g_ret_sts_success;
259 FND_MSG_PUB.initialize;
260 x_msg_data := '';
261 -- nothing need to check here since distribution function already be checked in javabean
262 -- check work source name and work source code
263 EXECUTE IMMEDIATE ' select count(*) from ieu_uwqm_work_sources_b where upper(ws_code) = upper(:1) and not_valid_flag=:2 '
264 INTO l_count USING p_ws_code, l_not_valid_flag ;
265 IF (l_count > 0) then
266 x_return_status := fnd_api.g_ret_sts_error;
267 x_msg_count := x_msg_count+1;
268 x_msg_data := 'IEU_PROV_WS_CODE_INVALID$';
269 END if;
270 IF (r_mode = 'create') then
271 EXECUTE IMMEDIATE ' select count(*) from ieu_uwqm_work_sources_b b, ieu_uwqm_work_sources_tl tl '||
272 ' where upper(tl.ws_name) = upper(:1) and b.not_valid_flag=:2 ' ||
273 ' and b.ws_id = tl.ws_id and tl.language = :3 '
274 INTO l_count USING p_ws_name, l_not_valid_flag, FND_GLOBAL.CURRENT_LANGUAGE ;
275 IF (l_count > 0) then
276 x_return_status := fnd_api.g_ret_sts_error;
277 x_msg_count := x_msg_count+1;
278 x_msg_data :=x_msg_data||'IEU_PROV_WS_NAME_INVALID$';
279 END if;
280 IF (p_ws_parent_id <> '0') then
281 EXECUTE IMMEDIATE ' select count(*) from ieu_uwqm_ws_assct_props where parent_ws_id = :1 and child_ws_id = :2 '
282 INTO l_count USING p_ws_parent_id, p_ws_child_id;
283 IF (l_count > 0) then
284 x_return_status := fnd_api.g_ret_sts_error;
285 x_msg_count := x_msg_count+1;
286 x_msg_data := x_msg_data||'IEU_PROV_WS_COMB_INVALID$';
287 END if;
288 END if;
289 END if;
290 EXCEPTION
291
292 WHEN fnd_api.g_exc_error THEN
293 ROLLBACK;
294 x_return_status := fnd_api.g_ret_sts_error;
295
296 WHEN fnd_api.g_exc_unexpected_error THEN
297 ROLLBACK;
298 x_return_status := fnd_api.g_ret_sts_unexp_error;
299
300 WHEN NO_DATA_FOUND THEN
301 null;
302
303 WHEN OTHERS THEN
304 ROLLBACK;
305 x_return_status := fnd_api.g_ret_sts_unexp_error;
306
307
308 commit;
309 END validateObj;
310
311
312
313 END ieu_work_source_pvt;