1 PACKAGE BODY pn_profile_upgrade AS
2 /* $Header: PNXPROFB.pls 120.4 2006/04/03 10:13:28 appldev noship $ */
3
4 TYPE def_set_of_books_type IS TABLE OF hr_operating_units%ROWTYPE
5 INDEX BY BINARY_INTEGER;
6
7 TYPE profile_value_rec_type IS RECORD
8 (profile_option_name fnd_profile_options.profile_option_name%TYPE,
9 profile_option_value fnd_profile_option_values.profile_option_value%TYPE,
10 level_id fnd_profile_option_values.level_id%TYPE,
11 level_value fnd_profile_option_values.level_value%TYPE);
12
13 TYPE profile_value_type IS TABLE OF profile_value_rec_type
14 INDEX BY BINARY_INTEGER;
15
16 def_set_of_books_tbl def_set_of_books_type;
17 profile_value_tbl profile_value_type;
18
19 g_default_org_id pn_system_setup_options.org_id%TYPE;
20 g_profileid4orgid fnd_profile_options.profile_option_id%TYPE;
21
22
23 /*-----------------------------------------------------------------------------
24 | FUNCTION : get_value
25 | PURPOSE : gets profile value for a given parameter
26 | NOTE : o assumes internal tables are populated through init_lookup_vars
27 | o will return default value if no match found
28 | o optimization note:
29 | - the search algorithm assumes the tables are structured in
30 | a specific order
31 | HISTORY : 26-MAR-02 ftanudja created
32 | 28-JAN-04 atuppad Added code to handle 2 profile options:
33 | GL_TRANSFER_MODE and SUBMIT_JOURNAL_IMPORT
34 \----------------------------------------------------------------------------*/
35
36 FUNCTION get_value(p_resp_id NUMBER,
37 p_parameter VARCHAR2)
38 RETURN VARCHAR2
39 IS
40 l_result VARCHAR2(30);
41 BEGIN
42
43 FOR i IN 0 .. (profile_value_tbl.count - 1) LOOP
44 IF ((profile_value_tbl(i).profile_option_name = p_parameter) AND
45 (profile_value_tbl(i).level_value = p_resp_id)AND
46 (profile_value_tbl(i).level_id = 10003))
47 OR
48 ((profile_value_tbl(i).level_id <> 10003) AND
49 (profile_value_tbl(i).profile_option_name = p_parameter))
50 THEN
51 l_result := profile_value_tbl(i).profile_option_value;
52 EXIT;
53 END IF;
54 END LOOP;
55
56 -- take care of default value;
57
58 IF l_result IS NOT NULL OR p_parameter IN ('PN_GL_TRANSFER_MODE','PN_SUBMIT_JOURNAL_IMPORT') THEN
59 RETURN l_result;
60 ELSIF p_parameter NOT IN ('PN_SET_OF_BOOKS_ID','PN_CURRENCY_CONV_RATE_TYPE',
61 'PN_ACCOUNTING_OPTION','PN_SPASGN_CHNGDT_OPTN') THEN
62 RETURN 'N';
63 ELSIF p_parameter IN ('PN_ACCOUNTING_OPTION','PN_SPASGN_CHNGDT_OPTN') THEN
64 RETURN 'Y';
65 ELSE
66 RETURN '';
67 END IF;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 null;
72 END;
73
74
75 /*-----------------------------------------\
76 | FUNCTION : get_def_set_of_books_id
77 | PURPOSE : gets default set of books id
78 | NOTE : assumes internal tables are initialized through init_lookup_vars
79 | HISTORY : 26-MAR-2002 ftanudja created
80 \------------------------------------------*/
81
82 FUNCTION get_def_set_of_books_id(p_org_id NUMBER) RETURN NUMBER
83 IS
84 l_result NUMBER;
85 BEGIN
86
87 FOR i IN 0 .. (def_set_of_books_tbl.count - 1) LOOP
88 IF (def_set_of_books_tbl(i).organization_id = p_org_id) THEN
89 l_result := def_set_of_books_tbl(i).set_of_books_id;
90 EXIT;
91 END IF;
92 END LOOP;
93
94 RETURN l_result;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 RAISE;
99 END;
100
101 /*-----------------------------------------------------------------------------
102 | PROCEDURE : init_lookup_vars
103 | PURPOSE : initializes data into plsql tables
104 | NOTES : o assumes 10001 = level_id for site
105 | o assumes 10002 = level_id for application
106 | o cursor get_def_org_id is ordered by level_id
107 | oo the fetch loop picks up lowest level => if find higher level_id, replace
108 | o cursor profile_option_cur is ordered by profile_option_name then level_id
109 | oo will go through the list starting from the highest level first (i.e resp level)
110 | oo if not found, default at next level (app level then site level)
111 | HISTORY : 26-MAR-2002 ftanudja o created
112 | : 11-SEP-2002 ftanudja o added handle for PN_MULTIPLE_LEASE_FOR_LOCATION
113 | o changed TO_NUMBER() to TRIM() and put TO_CHAR()
114 | in get_def_set_of_books_cur
115 | : 07-AUG-2003 ftanudja o added constraint for application id. 3084731.
116 | : 07-OCT-2003 ftanudja o removed 'DISTINCT' and added alias ref to tbl.
117 | : 03-FEB-2005 ftanudja o add 'PN_MULT_TNC_FOR_SAME_LEASE'. 4150676
118 \----------------------------------------------------------------------------*/
119
120 PROCEDURE init_lookup_vars
121 IS
122
123 CURSOR profile_option_cur IS
124 SELECT o.profile_option_name,
125 v.profile_option_value,
126 v.level_id,
127 v.level_value
128 FROM fnd_profile_options o,
129 fnd_profile_option_values v
130 WHERE o.profile_option_id = v.profile_option_id
131 AND o.application_id = v.application_id
132 AND v.level_id <> 10004
133 AND o.profile_option_name IN ('PN_ACCOUNTING_OPTION',
134 'PN_SET_OF_BOOKS_ID',
135 'PN_AUTOMATIC_COMPANY_NUMBER',
136 'PN_AUTOMATIC_INDEX_RENT_NUMBERING',
137 'PN_AUTOMATIC_LEASE_NUMBER',
138 'PN_AUTOMATIC_SPACE_DISTRIBUTION',
139 'PN_AUTO_VAR_RENT_NUM',
140 'PN_CURRENCY_CONV_RATE_TYPE',
141 'PN_SPASGN_CHNGDT_OPTN',
142 'PN_MULTIPLE_LEASE_FOR_LOCATION',
143 'PN_MULT_TNC_FOR_SAME_LEASE')
144 ORDER BY 1, 3 DESC;
145
146 CURSOR def_set_of_books_cur IS
147 SELECT hr.organization_id org_id,
148 hr.set_of_books_id set_of_books_id
149 FROM hr_operating_units hr,
150 fnd_profile_options o,
151 fnd_profile_option_values v
152 WHERE v.profile_option_id = o.profile_option_id
153 AND o.profile_option_name = 'ORG_ID'
154 AND v.level_id <> 10004
155 AND TRIM(v.profile_option_value) = TO_CHAR(hr.organization_id)
156 GROUP BY hr.organization_id, hr.set_of_books_id
157 ORDER BY 1;
158
159 CURSOR get_def_org_id IS
160 SELECT TO_NUMBER(v.profile_option_value) org_id, v.level_id
161 FROM fnd_profile_option_values v, fnd_profile_options o
162 WHERE v.profile_option_id = o.profile_option_id
163 AND o.profile_option_name = 'ORG_ID'
164 AND v.level_id IN (10002,10001)
165 ORDER BY 2;
166
167 CURSOR get_profileid4orgid IS
168 SELECT profile_option_id
169 FROM fnd_profile_options
170 WHERE profile_option_name = 'ORG_ID';
171
172 l_counter NUMBER := 0;
173 l_info_text VARCHAR2(200);
174 BEGIN
175
176 l_info_text := 'creating default set of books plsql table';
177
178 def_set_of_books_tbl.delete;
179 FOR def_rec IN def_set_of_books_cur LOOP
180 def_set_of_books_tbl(l_counter).organization_id := def_rec.org_id;
181 def_set_of_books_tbl(l_counter).set_of_books_id := def_rec.set_of_books_id;
182 l_counter := l_counter + 1;
183 END LOOP;
184
185 l_counter := 0;
186 l_info_text := 'creating list of profile options plsql table';
187
188 profile_value_tbl.delete;
189 FOR prof_rec IN profile_option_cur LOOP
190 profile_value_tbl(l_counter).profile_option_name := prof_rec.profile_option_name;
191 profile_value_tbl(l_counter).profile_option_value := prof_rec.profile_option_value;
192 profile_value_tbl(l_counter).level_id := prof_rec.level_id;
193 profile_value_tbl(l_counter).level_value := prof_rec.level_value;
194 l_counter := l_counter + 1;
195 END LOOP;
196
197 l_info_text := 'populating default_org_id variable';
198 OPEN get_def_org_id;
199 LOOP
200 FETCH get_def_org_id INTO g_default_org_id, l_counter;
201 EXIT WHEN get_def_org_id%NOTFOUND;
202 END LOOP;
203 CLOSE get_def_org_id;
204
205 l_info_text := 'populating profile_id for org_id variable';
206 OPEN get_profileid4orgid;
207 LOOP
208 FETCH get_profileid4orgid INTO g_profileid4orgid;
209 EXIT WHEN get_profileid4orgid%NOTFOUND;
210 END LOOP;
211 CLOSE get_profileid4orgid;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 raise_application_error(-20001,'Error while ' || l_info_text || to_char(sqlcode));
216 app_exception.raise_exception;
217 END;
218
219
220 /*-----------------------------------------------------------------------------
221 | PROCEDURE : populate_profile_tbl
222 | PURPOSE : inserts data into the pn_system_setup_options table
223 | NOTE : o assumes -1 is not a valid level_value
224 | o optimization note:
225 | oo the search algorithm assumes the tables are structured in a
226 | specific order
227 | oo for entries with the same org_id, the one with the most user
228 | is placed on top of list
229 | HISTORY :
230 | 26-MAR-02 ftanudja created.
231 | 11-SEP-02 ftanudja added handle for PN_MULTIPLE_LEASE_FOR_LOCATION.
232 | 30-JUN-03 ftanudja during INSERT, added new required columns from recovery
233 | module. And populated them with default values
234 | (since these are new profiles)
235 | 22-JUL-04 atuppad Optimized the main cursor for performance bug#3779117.
236 | Also, issued a mass insert on the table.
237 | 26-JUL-04 atuppad Added the default value of default_user_view_code col.
238 | 30-AUG-04 ftanudja add default value of 'extend_indexrent_term_flag'
239 | Reference bug #3756208.
240 | 28-OCT-04 atuppad o Added code for 4 columns of Retro.
241 | 30-DEC-04 kkhegde o Added calc_annualized_basis_code column.
242 | 15-DEC-05 kkhegde o Added recalc_ir_on_acc_chg_flag column default 'Y'
243 | 23-MAR-06 Hareesha o Bug 5106419 Modified to handle case when
244 | mandatory columns of pn_system_setup_options are NULL.
245 | 24-MAR-06 Hareesha o Bug 5106419 Needed to update the default value of
246 | ACCOUNTING_OPTION
247 | 03-APR-06 Kiran o Bug 5135571 changed OrgId, RespId, Count to tables
248 | INDEX BY BINARY_INTEGER
249 \----------------------------------------------------------------------------*/
250
251 PROCEDURE populate_profile_tbl
252 IS
253
254 CURSOR active_pn_resp_cur IS
255 SELECT NVL(v.profile_option_value, g_default_org_id)
256 org_id,
257 r.responsibility_id resp_id,
258 COUNT(u.user_id) num_users
259 FROM fnd_user u,
260 wf_user_roles wur,
261 fnd_responsibility r,
262 fnd_profile_option_values v
263 WHERE r.application_id = 240
264 AND r.responsibility_id = wur.role_orig_system_id (+)
265 AND wur.role_orig_system (+) = 'FND_RESP'
266 AND not wur.role_name (+) like 'FND_RESP|%|ANY'
267 AND u.user_name(+) = wur.user_name
268 AND r.start_date <= SYSDATE
269 AND NVL(r.end_date, SYSDATE) >= SYSDATE
270 AND v.profile_option_id = g_profileid4orgid
271 AND v.level_value = r.responsibility_id
272 AND v.profile_option_value NOT IN (SELECT org_id
273 FROM pn_system_setup_options)
274 AND v.level_value_application_id = 240
275 AND v.level_id = 10003
276 GROUP BY r.responsibility_id, v.profile_option_value
277 UNION
278 SELECT TO_CHAR(g_default_org_id) org_id,
279 0 resp_id,
280 0 num_users
281 FROM dual
282 WHERE NOT EXISTS (SELECT NULL
283 FROM fnd_profile_option_values v
287 AND v.level_value_application_id = 240
284 WHERE v.profile_option_id = g_profileid4orgid
285 AND v.profile_option_value = g_default_org_id
286 AND v.level_id = 10003
288 AND EXISTS (SELECT null
289 FROM pn_system_setup_options
290 WHERE org_id = g_default_org_id))
291 ORDER BY 1,3 DESC;
292
293 -- Get multiorg flag
294 CURSOR multi_org_cur IS
295 SELECT nvl(multi_org_flag, 'N') multi_org
296 FROM fnd_product_groups;
297
298 l_prev NUMBER := -1;
299 l_multi_org VARCHAR2(1) := 'N';
300 l_exists VARCHAR2(1);
301 l_info_text VARCHAR2(200);
302
303 TYPE OrgId IS TABLE OF pn_system_setup_options.org_id%TYPE INDEX BY BINARY_INTEGER;
304 TYPE RespId IS TABLE OF fnd_responsibility.responsibility_id%TYPE INDEX BY BINARY_INTEGER;
305 TYPE Count IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
306
307 orgids OrgId;
308 respids RespId;
309 counts Count;
310
311 l_set_of_books_id NUMBER(15);
312
313 BEGIN
314
315 l_info_text := 'Selecting multi org flag';
316 FOR multi_org_rec IN multi_org_cur LOOP
317 l_multi_org := multi_org_rec.multi_org;
318 END LOOP;
319
320
321 IF (UPPER(l_multi_org) = 'Y') THEN
322
323 OPEN active_pn_resp_cur;
324 LOOP
325 orgids.DELETE;
326 respids.DELETE;
327 counts.DELETE;
328
329 FETCH active_pn_resp_cur BULK COLLECT INTO orgids, respids, counts LIMIT 1000;
330 EXIT WHEN active_pn_resp_cur%NOTFOUND;
331 FOR i IN 1..orgids.COUNT LOOP
332
333 l_set_of_books_id := nvl(TO_NUMBER(get_value(respids(i), 'PN_SET_OF_BOOKS_ID')),
334 get_def_set_of_books_id(orgids(i)));
335
336 IF l_set_of_books_id IS NOT NULL THEN
337
338 INSERT INTO pn_system_setup_options
339 (profile_id,
340 org_id,
341 accounting_option,
342 set_of_books_id,
343 default_currency_conv_type,
344 space_assign_sysdate_optn,
345 multiple_tenancy_lease,
346 auto_comp_num_gen,
347 auto_lease_num_gen,
348 auto_index_num_gen,
349 auto_space_distribution,
350 auto_var_rent_num_gen,
351 auto_rec_agr_num_flag,
352 auto_rec_exp_num_flag,
353 auto_rec_arcl_num_flag,
354 auto_rec_expcl_num_flag,
355 cons_rec_agrterms_flag,
359 sysdate_for_adj_flag,
356 default_locn_area_flag,
357 default_user_view_code,
358 extend_indexrent_term_flag,
360 sysdate_as_trx_date_flag,
361 renorm_adj_acc_all_draft_flag,
362 consolidate_adj_items_flag,
363 calc_annualized_basis_code,
364 allow_tenancy_overlap_flag,
365 recalc_ir_on_acc_chg_flag,
366 created_by,
367 last_update_login,
368 last_updated_by,
369 creation_date,
370 last_update_date)
371 VALUES(pn_system_setup_options_s.nextval,
372 orgids(i),
373 NVL(get_value(respids(i), 'PN_ACCOUNTING_OPTION'),'Y'),
374 l_set_of_books_id,
375 get_value(respids(i), 'PN_CURRENCY_CONV_RATE_TYPE'),
376 NVL(get_value(respids(i), 'PN_SPASGN_CHNGDT_OPTN'),'Y'),
377 NVL(get_value(respids(i), 'PN_MULTIPLE_LEASE_FOR_LOCATION'),'N'),
378 NVL(get_value(respids(i), 'PN_AUTOMATIC_COMPANY_NUMBER'),'Y'),
379 NVL(get_value(respids(i), 'PN_AUTOMATIC_LEASE_NUMBER'),'Y'),
380 NVL(get_value(respids(i), 'PN_AUTOMATIC_INDEX_RENT_NUMBERING'),'N'),
381 NVL(get_value(respids(i), 'PN_SPACE_DISTRIBUTION'),'N'),
382 NVL(get_value(respids(i), 'PN_AUTO_VAR_RENT_NUMBER'),'N'),
383 'N',
384 'N',
385 'N',
386 'N',
387 'N',
388 'N',
389 'TENANT',
390 'Y',
391 'Y',
392 'N',
393 'Y',
394 'N',
395 'PERIOD',
396 NVL(get_value(respids(i), 'PN_MULT_TNC_FOR_SAME_LEASE'),'N'),
397 'Y',
398 NVL(fnd_global.user_id, -1),
399 NVL(fnd_global.user_id, -1),
400 NVL(fnd_global.user_id, -1),
401 SYSDATE,
402 SYSDATE);
403
404 END IF;
405
406 END LOOP;
407
408 END LOOP;
409
410 CLOSE active_pn_resp_cur;
411
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 l_info_text := SQLERRM;
417 raise_application_error(-20001,'Error while ' || l_info_text || to_char(sqlcode));
418 app_exception.raise_exception;
419 END;
420
421
422 /*-----------------------------------------------------------------------------
423 | PROCEDURE : init_lookup_vars_migr
424 | PURPOSE : initializes data into plsql tables
425 | DESCRIPTION : Initializes the table with the profile values for the 2 new
426 | profiles that need to be migrated. The 2 profiles are:
427 | 1. GL_TRANSFER_MODE 2. SUBMIT_JOURNAL_IMPORT
428 | This proc is similar to init_lookup_vars procedure.
429 | NOTES : Please refer to notes in procedure init_lookup_vars.
430 | HISTORY : 28-JAN-2004 atuppad o created
431 -----------------------------------------------------------------------------*/
432
433 PROCEDURE init_lookup_vars_migr
434 IS
435
436 CURSOR profile_option_cur IS
437 SELECT o.profile_option_name,
438 v.profile_option_value,
439 v.level_id,
440 v.level_value
441 FROM fnd_profile_options o,
442 fnd_profile_option_values v
443 WHERE o.profile_option_id = v.profile_option_id
444 AND o.application_id = v.application_id
445 AND v.level_id <> 10004
446 AND o.profile_option_name IN ('PN_GL_TRANSFER_MODE','PN_SUBMIT_JOURNAL_IMPORT')
447 ORDER BY 1, 3 DESC;
448
449 CURSOR get_profileid4orgid IS
450 SELECT profile_option_id
451 FROM fnd_profile_options
452 WHERE profile_option_name = 'ORG_ID';
453
454 CURSOR get_def_org_id IS
455 SELECT TO_NUMBER(v.profile_option_value) org_id, v.level_id
456 FROM fnd_profile_option_values v, fnd_profile_options o
457 WHERE v.profile_option_id = o.profile_option_id
458 AND o.profile_option_name = 'ORG_ID'
459 AND v.level_id IN (10002,10001)
460 ORDER BY 2;
461
462 l_counter NUMBER := 0;
463 l_info_text VARCHAR2(200);
464 BEGIN
465
466 l_counter := 0;
467 l_info_text := 'creating list of profile options plsql table';
468
469 profile_value_tbl.delete;
470 FOR prof_rec IN profile_option_cur LOOP
471 profile_value_tbl(l_counter).profile_option_name := prof_rec.profile_option_name;
472 profile_value_tbl(l_counter).profile_option_value := prof_rec.profile_option_value;
473 profile_value_tbl(l_counter).level_id := prof_rec.level_id;
474 profile_value_tbl(l_counter).level_value := prof_rec.level_value;
475 l_counter := l_counter + 1;
476 END LOOP;
477
478 l_info_text := 'populating default_org_id variable';
479 OPEN get_def_org_id;
480 LOOP
481 FETCH get_def_org_id INTO g_default_org_id, l_counter;
482 EXIT WHEN get_def_org_id%NOTFOUND;
483 END LOOP;
484 CLOSE get_def_org_id;
485
486 l_info_text := 'populating profile_id for org_id variable';
487 OPEN get_profileid4orgid;
488 LOOP
492 CLOSE get_profileid4orgid;
489 FETCH get_profileid4orgid INTO g_profileid4orgid;
490 EXIT WHEN get_profileid4orgid%NOTFOUND;
491 END LOOP;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 raise_application_error(-20001,'Error while ' || l_info_text || to_char(sqlcode));
497 app_exception.raise_exception;
498
499 END init_lookup_vars_migr;
500
501
502 /*-----------------------------------------------------------------------------
503 | PROCEDURE : update_profile_tbl
504 | PURPOSE : updates data into the pn_system_setup_options table for migration
505 | DESCRIPTION: This procedure will update the pn_system_setup_option table for
506 | migration of GL_TRANSFER_MODE and SUBMIT_JOURNAL_IMPORT
507 | NOTE : Please refer to populate_profile_tbl procedure.
508 | HISTORY :
509 | 28-JAN-04 atuppad o created.
510 | 22-JUL-04 atuppad o Optimized the main cursor for performance bug#3779117.
511 | Also, issued a mass update on the table.
512 \----------------------------------------------------------------------------*/
513
514 PROCEDURE update_profile_tbl
515 IS
516
517 CURSOR active_pn_resp_cur IS
518 SELECT NVL(v.profile_option_value, g_default_org_id)
519 org_id,
520 r.responsibility_id resp_id,
521 COUNT(u.user_id) num_users
522 FROM fnd_user u,
523 wf_user_roles wur,
524 fnd_responsibility r,
525 fnd_profile_option_values v
526 WHERE r.application_id = 240
527 AND r.responsibility_id = wur.role_orig_system_id (+)
528 AND wur.role_orig_system (+) = 'FND_RESP'
529 AND not wur.role_name (+) like 'FND_RESP|%|ANY'
530 AND u.user_name(+) = wur.user_name
531 AND r.start_date <= SYSDATE
532 AND NVL(r.end_date, SYSDATE) >= SYSDATE
533 AND v.profile_option_id = g_profileid4orgid
534 AND v.level_value = r.responsibility_id
535 AND v.profile_option_value IN (SELECT org_id
536 FROM pn_system_setup_options)
537 AND v.level_value_application_id = 240
538 AND v.level_id = 10003
539 GROUP BY r.responsibility_id, v.profile_option_value
540 UNION
541 SELECT TO_CHAR(g_default_org_id) org_id,
542 0 resp_id,
543 0 num_users
544 FROM dual
545 WHERE NOT EXISTS (SELECT NULL
546 FROM fnd_profile_option_values v
547 WHERE v.profile_option_id = g_profileid4orgid
548 AND v.profile_option_value = g_default_org_id
549 AND v.level_id = 10003
550 AND v.level_value_application_id = 240
551 AND EXISTS (SELECT null
552 FROM pn_system_setup_options
553 WHERE org_id = g_default_org_id))
554 ORDER BY 1,3 DESC;
555
556 -- Get multiorg flag
557 CURSOR multi_org_cur IS
558 SELECT nvl(multi_org_flag, 'N') multi_org
559 FROM fnd_product_groups;
560
561 l_prev NUMBER := -1;
562 l_multi_org VARCHAR2(1) := 'N';
563 l_exists VARCHAR2(1);
564 l_info_text VARCHAR2(200);
565 TYPE OrgId IS TABLE OF pn_system_setup_options.org_id%TYPE;
566 TYPE RespId IS TABLE OF fnd_responsibility.responsibility_id%TYPE;
567 TYPE Count IS TABLE OF NUMBER;
568
569 orgids OrgId;
570 respids RespId;
571 counts Count;
572
573 BEGIN
574
575 l_info_text := 'Selecting multi org flag';
576 FOR multi_org_rec IN multi_org_cur LOOP
577 l_multi_org := multi_org_rec.multi_org;
578 END LOOP;
579
580 IF (UPPER(l_multi_org) = 'Y') THEN
581 OPEN active_pn_resp_cur;
582 LOOP
583 FETCH active_pn_resp_cur BULK COLLECT INTO orgids, respids, counts LIMIT 1000;
584
585 FORALL i IN 1..orgids.COUNT
586 UPDATE PN_SYSTEM_SETUP_OPTIONS
587 SET gl_transfer_mode = get_value(respids(i),'PN_GL_TRANSFER_MODE'),
588 submit_journal_import_flag = get_value(respids(i),'PN_SUBMIT_JOURNAL_IMPORT'),
589 last_update_login = NVL(fnd_global.user_id, -1),
590 last_updated_by = NVL(fnd_global.user_id, -1),
591 last_update_date = SYSDATE
592 WHERE org_id = orgids(i);
593 EXIT WHEN active_pn_resp_cur%NOTFOUND;
594
595 END LOOP;
596 CLOSE active_pn_resp_cur;
597 END IF;
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 raise_application_error(-20001,'Error while ' || l_info_text || to_char(sqlcode));
602 app_exception.raise_exception;
603
604 END update_profile_tbl;
605
606 END pn_profile_upgrade;
[Home] [Help]