[Home] [Help]
PACKAGE BODY: APPS.PER_RI_LCW_REG_PKG
Source
1 PACKAGE BODY per_ri_lcw_reg_pkg AS
2 /* $Header: perrilcw.pkb 120.1 2010/05/27 18:23:01 sidsaxen noship $ */
3 PROCEDURE per_ri_lcw_register
4 (p_workbench_item_code In Varchar2
5 ,p_setup_task_code In Varchar2
6 ,p_setup_task_name In Varchar2
7 ,p_setup_task_seq In Number
11 ,p_legislation_code In Varchar2
8 ,p_setup_sub_task_code In Varchar2
9 ,p_setup_sub_task_name In Varchar2
10 ,p_setup_sub_task_action In Varchar2
12 ,p_sub_task_seq In Number
13 ,p_object_version_number In Out nocopy Number
14 ,p_msg Out nocopy Varchar2
15 ) Is
16 CURSOR csr_chk_task_exists IS
17 SELECT 1
18 FROM per_ri_setup_tasks
19 WHERE workbench_item_code = p_workbench_item_code
20 AND setup_task_code = p_setup_task_code;
21
22
23 CURSOR csr_get_max_task_seq IS
24 SELECT max(setup_task_sequence)
25 FROM per_ri_setup_tasks
26 WHERE workbench_item_code = p_workbench_item_code;
27
28 CURSOR csr_get_max_sub_task_seq IS
29 SELECT max(setup_sub_task_sequence)
30 FROM per_ri_setup_sub_tasks
31 WHERE setup_task_code = p_setup_task_code;
32
33
34 CURSOR csr_get_max_entry_seq (p_menu_id number) IS
35 SELECT max(entry_sequence)
36 FROM fnd_menu_entries
37 WHERE menu_id = p_menu_id;
38
39 --To be changed as soon as the issue with table's PK is resolved
40 Cursor chk_sub_task_exists IS
41 SELECT psst.setup_sub_task_sequence
42 FROM per_ri_setup_sub_tasks psst,fnd_territories_vl ter
43 WHERE psst.setup_sub_task_code = p_setup_sub_task_code;
44 -- AND psst.setup_task_code = 'LCW_' || p_workbench_item_code
45 -- AND psst.legislation_code= p_legislation_code;
46
47
48 l_max_task_seq NUMBER;
49 l_task_seq NUMBER;
50 l_max_sub_task_seq NUMBER;
51 l_sub_task_seq NUMBER;
52 l_max_entry_seq NUMBER;
53 l_main_menu_id NUMBER;
54 l_dummy NUMBER;
55 l_ovn NUMBER;
56 l_sub_ovn NUMBER;
57 l_leg_code VARCHAR(30);
58 l_row_id VARCHAR2(2000);
59 l_cus_task_start_seq NUMBER := 10000;
60 Begin
61
62
63 OPEN csr_chk_task_exists;
64 FETCH csr_chk_task_exists INTO l_dummy;
65 IF (csr_chk_task_exists%NOTFOUND) THEN
66 l_task_seq := l_cus_task_start_seq + p_setup_task_seq;
67 per_ri_setup_task_api.create_setup_task(
68 p_validate => FALSE
69 ,p_setup_task_code => p_setup_task_code
70 ,p_workbench_item_code => p_workbench_item_code
71 ,p_setup_task_name => p_setup_task_name
72 ,p_setup_task_description => p_setup_task_name
73 ,p_setup_task_sequence => l_task_seq
74 ,p_setup_task_status => 'NOT_STARTED'
75 ,p_setup_task_creation_date => sysdate
76 ,p_setup_task_last_mod_date => sysdate
77 ,p_setup_task_type => 'WIZARD'
78 ,p_setup_task_action => 'OA.jsp?OAFunc=PER_RI_LCW&setupTaskCode={!SetupTaskCode}&retainAM=Y&addBreadCrumb=Y'
79 ,p_effective_date => sysdate
80 ,p_object_version_number => l_ovn
81 );
82 create_lcw_oaf_function('S_' || substr(p_setup_task_code , 1 , 28) , p_setup_task_name);
83 END IF;
84 CLOSE csr_chk_task_exists;
85
86 /*OPEN chk_sub_task_exists;
87 FETCH chk_sub_task_exists into l_sub_task_seq;
88 if (chk_sub_task_exists%NOTFOUND) then
89 OPEN csr_get_max_sub_task_seq;
90 FETCH csr_get_max_sub_task_seq into l_max_sub_task_seq;
91 CLOSE csr_get_max_sub_task_seq;
92 l_max_sub_task_seq := nvl(l_max_sub_task_seq,0) + 1;
93 end if;
94 CLOSE chk_sub_task_exists;
95 */
96
97 OPEN chk_sub_task_exists;
98 FETCH chk_sub_task_exists into l_sub_task_seq;
99 if (chk_sub_task_exists%NOTFOUND) then
100 --Create Sub Task
101 l_sub_task_seq := l_cus_task_start_seq + p_sub_task_seq;
102 per_ri_setup_sub_task_api.CREATE_SETUP_SUB_TASK
103 ( P_SETUP_SUB_TASK_CODE =>p_setup_sub_task_code,
104 P_SETUP_SUB_TASK_NAME =>p_setup_sub_task_name,
105 P_SETUP_SUB_TASK_DESCRIPTION =>p_setup_sub_task_name,
106 P_SETUP_TASK_CODE =>p_setup_task_code,
107 P_SETUP_SUB_TASK_SEQUENCE =>l_sub_task_seq,
108 P_SETUP_SUB_TASK_STATUS =>'NOT_STARTED',
109 P_SETUP_SUB_TASK_TYPE => null,
110 P_SETUP_SUB_TASK_DP_LINK => null,
111 P_SETUP_SUB_TASK_ACTION => p_setup_sub_task_action,
112 P_SETUP_SUB_TASK_CREATION_DATE =>sysdate,
113 P_SETUP_SUB_TASK_LAST_MOD_DATE =>sysdate,
114 P_LEGISLATION_CODE => p_legislation_code,
115 P_LANGUAGE_CODE => 'US',
116 P_EFFECTIVE_DATE =>sysdate,
117 P_OBJECT_VERSION_NUMBER => p_object_version_number
118 );
119
120 p_msg := 'Success';
121 else
122 --Update Sub Task
123 if(p_object_version_number = 0)
124 then
125 p_msg := 'PER_RI_LCW_DUP_WIZ';
126 else
127 l_sub_task_seq := l_cus_task_start_seq + p_sub_task_seq;
128 per_ri_setup_sub_task_api.UPDATE_SETUP_SUB_TASK
129 (
130 P_SETUP_SUB_TASK_CODE =>p_setup_sub_task_code,
131 P_SETUP_SUB_TASK_NAME =>p_setup_sub_task_name,
132 P_SETUP_SUB_TASK_DESCRIPTION =>p_setup_sub_task_name,
133 P_SETUP_TASK_CODE =>p_setup_task_code,
134 P_SETUP_SUB_TASK_SEQUENCE =>l_sub_task_seq,
135 P_SETUP_SUB_TASK_STATUS =>'NOT_STARTED',
136 P_SETUP_SUB_TASK_TYPE =>null,
137 P_SETUP_SUB_TASK_DP_LINK =>null,
141 P_LANGUAGE_CODE =>'US',
138 P_SETUP_SUB_TASK_ACTION =>p_setup_sub_task_action,
139 P_SETUP_SUB_TASK_LAST_MOD_DATE =>sysdate,
140 P_LEGISLATION_CODE =>p_legislation_code,
142 P_EFFECTIVE_DATE =>sysdate,
143 P_OBJECT_VERSION_NUMBER =>p_object_version_number
144
145 );
146 p_msg := 'Success';
147
148 end if;
149
150 end if;
151 CLOSE chk_sub_task_exists;
152
153 /*per_ri_workbench_pkg.load_setup_sub_task_row
154 (p_setup_sub_task_code => p_setup_sub_task_code
155 ,p_setup_sub_task_name => p_setup_sub_task_name
156 ,p_setup_sub_task_description => p_setup_sub_task_name
157 ,p_setup_task_code => 'LCW_' || p_workbench_item_code
158 -- ,p_setup_sub_task_sequence => nvl(l_sub_task_seq,l_max_sub_task_seq)
159 ,p_setup_sub_task_sequence => p_sub_task_seq
160 ,p_setup_sub_task_status => 'NOT_STARTED'
161 ,p_setup_sub_task_type => null
162 ,p_setup_sub_task_dp_link => null
163 ,p_setup_sub_task_action => p_setup_sub_task_action
164 ,p_setup_sub_task_creation_date => sysdate
165 ,p_setup_sub_task_last_mod_date => sysdate
166 ,p_legislation_code => p_legislation_code
167 ,p_effective_date => sysdate
168 );*/
169
170 END per_ri_lcw_register;
171
172
173 PROCEDURE per_ri_lcw_delete (
174 p_workbench_item_code In Varchar2
175 ,p_setup_task_code In Varchar2
176 ,p_setup_sub_task_code In Varchar2
177 ,p_object_version_number In Number
178 )
179 Is
180
181 CURSOR csr_chk_sub_task_exists IS
182 SELECT 1
183 FROM per_ri_setup_sub_tasks
184 WHERE setup_task_code = p_setup_task_code;
185
186 CURSOR csr_task_object_version_num IS
187 SELECT object_version_number
188 FROM per_ri_setup_tasks
189 WHERE setup_task_code = p_setup_task_code;
190
191
192 l_dummy varchar2(20);
193 l_task_object_num Number;
194
195 Begin
196
197 --Delete the setup sub task with setup task LCW_<p_workbench_item_code>
198
199 per_ri_setup_sub_task_api.DELETE_SETUP_SUB_TASK(
200
201 P_SETUP_SUB_TASK_CODE =>p_setup_sub_task_code,
202 P_OBJECT_VERSION_NUMBER=>p_object_version_number
203 );
204
205
206 OPEN csr_chk_sub_task_exists;
207 FETCH csr_chk_sub_task_exists INTO l_dummy;
208 IF (csr_chk_sub_task_exists%NOTFOUND) THEN
209
210 OPEN csr_task_object_version_num;
211 FETCH csr_task_object_version_num INTO l_task_object_num;
212 CLOSE csr_task_object_version_num;
213
214 per_ri_setup_task_api.DELETE_SETUP_TASK(
215 P_SETUP_TASK_CODE => p_setup_task_code ,
216 P_OBJECT_VERSION_NUMBER => l_task_object_num
217 );
218
219 delete_lcw_oaf_function('S_' || substr(p_setup_task_code , 1 ,28 ));
220
221 END IF;
222
223 CLOSE csr_chk_sub_task_exists;
224
225
226 commit;
227
228 End per_ri_lcw_delete;
229
230 PROCEDURE create_lcw_oaf_function(
231 p_function_name IN Varchar2
232 ,p_user_function_name IN Varchar2
233 ) is
234 l_rowid VARCHAR2(200);
235 l_fun_id NUMBER;
236 l_application_id NUMBER;
237 l_web_host_name VARCHAR2(200);
238 l_web_agent_name VARCHAR2(200);
239 l_web_html_call VARCHAR2(200);
240 l_web_encrypt_parameters VARCHAR2(200);
241 l_web_secured VARCHAR2(200);
242 l_web_icon VARCHAR2(200);
243 l_object_id NUMBER;
244 l_region_application_id NUMBER;
245 l_region_code VARCHAR2(200);
246 l_form_id NUMBER;
247 l_parameters VARCHAR2(200);
248 l_type VARCHAR2(200);
249 l_maintenance_mode_support VARCHAR2(200);
250 l_context_dependence VARCHAR2(200);
251 l_jrad_ref_patch VARCHAR2(200);
252
253 begin
254 l_web_encrypt_parameters := 'N';
255 l_web_secured := 'N';
256 l_type := 'SUBFUNCTION';
257
258
259 SELECT fnd_form_functions_s.nextval
260 INTO l_fun_id
261 FROM dual;
262
263 fnd_form_functions_pkg.INSERT_ROW
264 (X_ROWID => l_rowid
265 ,X_FUNCTION_ID => l_fun_id
266 ,X_WEB_HOST_NAME => l_web_host_name
267 ,X_WEB_AGENT_NAME => l_web_agent_name
268 ,X_WEB_HTML_CALL => l_web_html_call
269 ,X_WEB_ENCRYPT_PARAMETERS => l_web_encrypt_parameters
270 ,X_WEB_SECURED => l_web_secured
271 ,X_WEB_ICON => l_web_icon
272 ,X_OBJECT_ID => l_object_id
273 ,X_REGION_APPLICATION_ID => l_region_application_id
274 ,X_REGION_CODE => l_region_code
275 ,X_FUNCTION_NAME => p_function_name
276 ,X_APPLICATION_ID => l_application_id
277 ,X_FORM_ID => l_form_id
278 ,X_PARAMETERS => l_parameters --can be changed later
279 ,X_TYPE => l_type
280 ,X_USER_FUNCTION_NAME => p_user_function_name --can be changed later
281 ,X_DESCRIPTION => p_user_function_name --can be changed later
282 ,X_CREATION_DATE => Sysdate
283 ,X_CREATED_BY => 120
284 ,X_LAST_UPDATE_DATE => sysdate
285 ,X_LAST_UPDATED_BY => 120
286 ,X_LAST_UPDATE_LOGIN => 0
287 ,X_MAINTENANCE_MODE_SUPPORT => l_maintenance_mode_support
288 ,X_CONTEXT_DEPENDENCE => l_context_dependence
289 ,X_JRAD_REF_PATH => l_jrad_ref_patch);
290 end create_lcw_oaf_function;
291
292 PROCEDURE delete_lcw_oaf_function(
293 p_function_name IN Varchar2
294 ) is
295 l_function_id NUMBER;
296 l_dummy NUMBER;
297 begin
298 select function_id into l_function_id
299 from fnd_form_functions where function_name = p_function_name;
300
301 FND_FORM_FUNCTIONS_PKG.DELETE_ROW(
302 X_FUNCTION_ID => l_function_id);
303 EXCEPTION
304 WHEN OTHERS THEN
305 l_dummy := 0;
306 end delete_lcw_oaf_function;
307
308
309 END per_ri_lcw_reg_pkg;