[Home] [Help]
PACKAGE BODY: APPS.PN_MO_CACHE_UTILS
Source
1 PACKAGE BODY pn_mo_cache_utils AS
2 /* $Header: PNMOCSHB.pls 120.7 2007/09/11 16:10:29 rthumma ship $ */
3
4 /** Added these two functions to remove dependency on MO packages
5 * until shared services changes is effective
6 */
7 --------------------------------------------------------------------------------
8 -- NAME : get_multi_org_flag
9 -- DESCRIPTION : This function determines whether this is a Multi-Org
10 -- instance or not. Returns 'Y' or 'N'.
11 -- PURPOSE :
12 -- INVOKED FROM : retrieve_globals,retrieve_org_id_specific and get_profile_value
13 -- ARGUMENTS : NONE
14 -- RETURN : Returns N if Multi-Org not enabled else returns 'Y'
15 -- REFERENCE : PN_COMMON.debug()
16 -- HISTORY :
17 --
18 -- 30-OCT-02 ftanudja o Created
19 -- 31-MAR-05 piagrawa o Modified the function to make a call to
20 -- is_multi_org_enabled of mo_globals if MOAC is
21 -- enabled.
22 --------------------------------------------------------------------------------
23 FUNCTION get_multi_org_flag RETURN VARCHAR2
24 IS
25 l_result VARCHAR2(1) := 'N';
26
27 CURSOR flag IS
28 SELECT multi_org_flag
29 FROM fnd_product_groups;
30
31 BEGIN
32 IF pn_mo_cache_utils.is_MOAC_enabled THEN
33 l_result := mo_global.is_multi_org_enabled;
34 ELSE
35 FOR cur IN flag LOOP
36 l_result := cur.multi_org_flag;
37 END LOOP;
38 END IF;
39 RETURN l_result;
40 END get_multi_org_flag;
41
42 --------------------------------------------------------------------------------
43 -- NAME : check_access
44 -- DESCRIPTION : Checks if an operating unit exists in the list of Operating
45 -- Units that are allowed access to, for a responsibility.
46 -- PURPOSE :
47 -- INVOKED FROM : retrieve_globals
48 -- ARGUMENTS : NONE
49 -- RETURN : Returns N if Multi-Org not enabled else returns 'Y'
50 -- REFERENCE : PN_COMMON.debug()
51 -- HISTORY :
52 --
53 -- 30-OCT-02 ftanudja o Created
54 -- 31-MAR-05 piagrawa o Modified the function to make a call to
55 -- check_access of mo_globals if MOAC is enabled
56 --------------------------------------------------------------------------------
57 FUNCTION check_access(p_org_id NUMBER) RETURN VARCHAR2
58 IS
59 l_result VARCHAR2(1) := 'N';
60 BEGIN
61 IF pn_mo_cache_utils.is_MOAC_enabled THEN
62 l_result := mo_global.check_access(p_org_id);
63 ELSE
64 IF TO_NUMBER(fnd_profile.value('ORG_ID')) = p_org_id THEN
65 l_result := 'Y';
66 ELSE
67 l_result := 'N';
68 END IF;
69 END IF;
70 RETURN l_result;
71 END check_access;
72
73 --------------------------------------------------------------------------------
74 -- NAME : check_valid_org
75 -- DESCRIPTION : Checks if an operating unit is valid for a responsibility.
76 -- PURPOSE : Wrapper on mo_global.check_valid_org - calls
77 -- mo_global.check_valid_org if MOAC is enabled.
78 -- INVOKED FROM : form libraries.
79 -- ARGUMENTS : NONE
80 -- RETURN : Returns 'Y' OR 'N'
81 -- REFERENCE : PN_COMMON.debug()
82 -- HISTORY :
83 --
84 -- 03-AUG-05 Kiran o Created
85 --------------------------------------------------------------------------------
86 FUNCTION check_valid_org(p_org_id NUMBER) RETURN VARCHAR2 IS
87 l_result VARCHAR2(1);
88 BEGIN
89 l_result := 'N';
90 IF pn_mo_cache_utils.is_MOAC_enabled THEN
91 l_result := mo_global.check_valid_org(p_org_id);
92 ELSE
93 IF TO_NUMBER(fnd_profile.value('ORG_ID')) = p_org_id THEN
94 l_result := 'Y';
95 ELSE
96 l_result := 'N';
97 END IF;
98 END IF;
99 RETURN l_result;
100 END check_valid_org;
101
102 -------------------------------------------------------------------------------
103 -- PROCEDURE : is_MOAC_enabled
104 -- DESCRIPTION : Works as a On/Off switch for MOAC Changes.
105 -- Returns boolean value of TRUE if functionality
106 -- is switched ON else returns FALSE.
107 -- PURPOSE :
108 -- INVOKED FROM : get_multi_org_flag, check_access and PNCOMMON.pld
109 -- ARGUMENTS : NONE
110 -- RETURN : Returns FALSE if Multi-Org not enabled else returns TRUE
111 -- REFERENCE : PN_COMMON.debug()
112 -- HISTORY :
113 --
114 -- 29-MAR-05 piagrawa o Created.
115 -------------------------------------------------------------------------------
116 FUNCTION is_MOAC_enabled RETURN BOOLEAN IS
117
118 BEGIN
119
120 RETURN g_MOAC_enabled;
121
122 END is_MOAC_enabled;
123
124 -------------------------------------------------------------------------------
125 -- PROCEDURE : is_MOAC_enabled_char
126 -- DESCRIPTION : Works as a On/Off switch for MOAC Changes
127 -- Returns character value of 'Y' if functionality
128 -- is switched ON else returns 'N'. It is created to be used in
129 -- OA framewrork VOs.
130 -- PURPOSE :
131 -- INVOKED FROM :
132 -- ARGUMENTS : NONE
133 -- RETURN : Returns 'N' if Multi-Org not enabled else returns 'Y'
134 -- REFERENCE : PN_COMMON.debug()
135 -- HISTORY :
136 --
137 -- 29-MAR-05 piagrawa o Created.
138 -------------------------------------------------------------------------------
139 FUNCTION is_MOAC_enabled_char RETURN VARCHAR2 IS
140 BEGIN
141
142 IF g_MOAC_enabled THEN
143 RETURN 'Y';
144 ELSE
145 RETURN 'N';
146 END IF;
147
148 END is_MOAC_enabled_char;
149
150
151 -------------------------------------------------------------------------------
152 -- PROCEDURE : retrieve_globals
153 -- DESCRIPTION : This procedure retrieves operating unit attributes from the
154 -- database and stores them into the specified data structure.
155 -- PURPOSE :
156 -- INVOKED FROM : populate in PNCOMMON.pld
157 -- ARGUMENTS : IN NONE
158 -- OUT p_globals
159 -- RETURNS : NONE
160 -- REFERENCE : PN_COMMON.debug()
161 -- HISTORY :
162 --
163 -- 29-MAR-05 piagrawa o Modified to include legacy_cut_off_date in the
164 -- select statement.
165 -- 15-SEP-05 pikhar o Modified to include recalc_ir_on_acc_chg_flag in
166 -- the SELECT statement.
167 -- 29-NOV-05 Kiran o Fixed the cartesian join in the the query
168 -- 06-APR-06 hkulkarn o Modified to include smallest_term_amount in
169 -- the SELECT statement.
170 -- 11-SEP-07 rthumma o Modified to include incl_terms_by_default_flag
171 -- in the SELECT statement.
172 -------------------------------------------------------------------------------
173 PROCEDURE retrieve_globals(p_globals OUT NOCOPY GlobalsTable )
174 IS
175 BEGIN
176 IF pn_mo_cache_utils.get_multi_org_flag = 'Y' THEN
177 SELECT pn.org_id,
178 gl.name,
179 gl.chart_of_accounts_id,
180 hr.name,
181 gl.currency_code,
182 pn.set_of_books_id,
183 pn.accounting_option,
184 pn.default_currency_conv_type,
185 pn.space_assign_sysdate_optn,
186 pn.multiple_tenancy_lease,
187 pn.auto_space_distribution,
188 pn.auto_comp_num_gen,
189 pn.auto_lease_num_gen,
190 pn.auto_index_num_gen,
191 pn.auto_var_rent_num_gen,
192 pn.auto_rec_agr_num_flag,
193 pn.auto_rec_exp_num_flag,
194 pn.auto_rec_arcl_num_flag,
195 pn.auto_rec_expcl_num_flag,
196 pn.cons_rec_agrterms_flag,
197 pn.location_code_separator,
198 pn.default_locn_area_flag,
199 pn.grouping_rule_id,
200 pn.gl_transfer_mode,
201 pn.submit_journal_import_flag,
202 pn.legacy_data_cutoff_date,
203 pn.default_user_view_code,
204 pn.extend_indexrent_term_flag,
205 pn.sysdate_for_adj_flag,
206 pn.sysdate_as_trx_date_flag,
207 pn.renorm_adj_acc_all_draft_flag,
208 pn.consolidate_adj_items_flag,
209 pn.calc_annualized_basis_code,
210 pn.allow_tenancy_overlap_flag,
211 pn.recalc_ir_on_acc_chg_flag,
212 pn.smallest_term_amount,
213 pn.incl_terms_by_default_flag
214 BULK COLLECT
215 INTO p_globals.org_id_t,
216 p_globals.set_of_books_name_t,
217 p_globals.chart_of_accounts_id_t,
218 p_globals.legal_entity_name_t,
219 p_globals.functional_currency_code_t,
220 p_globals.set_of_books_id_t,
221 p_globals.accounting_option_t,
222 p_globals.default_currency_conv_type_t,
223 p_globals.space_assign_sysdate_optn_t,
224 p_globals.multiple_tenancy_lease_t,
225 p_globals.auto_space_distribution_t,
226 p_globals.auto_comp_num_gen_t,
227 p_globals.auto_lease_num_gen_t,
228 p_globals.auto_index_num_gen_t,
229 p_globals.auto_var_rent_num_gen_t,
230 p_globals.auto_rec_agr_num_flag_t,
231 p_globals.auto_rec_exp_num_flag_t,
232 p_globals.auto_rec_arcl_num_flag_t,
233 p_globals.auto_rec_expcl_num_flag_t,
234 p_globals.cons_rec_agrterms_flag_t,
235 p_globals.location_code_separator_t,
236 p_globals.default_locn_area_flag_t,
237 p_globals.grouping_rule_id_t,
238 p_globals.gl_transfer_mode_t,
239 p_globals.submit_journal_import_flag_t,
240 p_globals.legacy_data_cutoff_date_t,
241 p_globals.default_user_view_code_t,
242 p_globals.extend_indexrent_term_flag_t,
243 p_globals.sysdate_for_adj_flag_t,
244 p_globals.sysdate_as_trx_date_flag_t,
245 p_globals.renorm_acc_all_draft_flag_t,
246 p_globals.consolidate_adj_items_flag_t,
247 p_globals.calc_annualized_basis_code_t,
248 p_globals.allow_tenancy_overlap_flag_t,
249 p_globals.recalc_ir_on_acc_chg_flag_t,
250 p_globals.smallest_term_amount_t,
251 p_globals.incl_terms_by_default_flag_t
252 FROM hr_legal_entities hr,
253 gl_sets_of_books gl,
254 pn_system_setup_options pn
255 WHERE pn_mo_cache_utils.check_access(pn.org_id) = 'Y'
256 AND gl.set_of_books_id = pn.set_of_books_id
257 AND hr.organization_id (+) = pn.org_id;
258
259 ELSE -- non multi org case
260
261 SELECT -3115,
262 gl.name,
263 gl.chart_of_accounts_id,
264 NULL,
265 gl.currency_code,
266 pn.set_of_books_id,
267 pn.accounting_option,
268 pn.default_currency_conv_type,
269 pn.space_assign_sysdate_optn,
270 pn.multiple_tenancy_lease,
271 pn.auto_space_distribution,
272 pn.auto_comp_num_gen,
273 pn.auto_lease_num_gen,
274 pn.auto_index_num_gen,
275 pn.auto_var_rent_num_gen,
276 pn.auto_rec_agr_num_flag,
277 pn.auto_rec_exp_num_flag,
278 pn.auto_rec_arcl_num_flag,
279 pn.auto_rec_expcl_num_flag,
280 pn.cons_rec_agrterms_flag,
281 pn.location_code_separator,
282 pn.default_locn_area_flag,
283 pn.grouping_rule_id,
284 pn.gl_transfer_mode,
285 pn.submit_journal_import_flag,
286 pn.legacy_data_cutoff_date,
287 pn.default_user_view_code,
288 pn.extend_indexrent_term_flag,
289 pn.sysdate_for_adj_flag,
290 pn.sysdate_as_trx_date_flag,
291 pn.renorm_adj_acc_all_draft_flag,
292 pn.consolidate_adj_items_flag,
293 pn.calc_annualized_basis_code,
294 pn.allow_tenancy_overlap_flag,
295 pn.recalc_ir_on_acc_chg_flag,
296 pn.smallest_term_amount,
297 pn.incl_terms_by_default_flag
298 BULK COLLECT
299 INTO p_globals.org_id_t,
300 p_globals.set_of_books_name_t,
301 p_globals.chart_of_accounts_id_t,
302 p_globals.legal_entity_name_t,
303 p_globals.functional_currency_code_t,
304 p_globals.set_of_books_id_t,
305 p_globals.accounting_option_t,
306 p_globals.default_currency_conv_type_t,
307 p_globals.space_assign_sysdate_optn_t,
308 p_globals.multiple_tenancy_lease_t,
309 p_globals.auto_space_distribution_t,
310 p_globals.auto_comp_num_gen_t,
311 p_globals.auto_lease_num_gen_t,
312 p_globals.auto_index_num_gen_t,
313 p_globals.auto_var_rent_num_gen_t,
314 p_globals.auto_rec_agr_num_flag_t,
315 p_globals.auto_rec_exp_num_flag_t,
316 p_globals.auto_rec_arcl_num_flag_t,
317 p_globals.auto_rec_expcl_num_flag_t,
318 p_globals.cons_rec_agrterms_flag_t,
319 p_globals.location_code_separator_t,
320 p_globals.default_locn_area_flag_t,
321 p_globals.grouping_rule_id_t,
322 p_globals.gl_transfer_mode_t,
323 p_globals.submit_journal_import_flag_t,
324 p_globals.legacy_data_cutoff_date_t,
325 p_globals.default_user_view_code_t,
326 p_globals.extend_indexrent_term_flag_t,
327 p_globals.sysdate_for_adj_flag_t,
328 p_globals.sysdate_as_trx_date_flag_t,
329 p_globals.renorm_acc_all_draft_flag_t,
330 p_globals.consolidate_adj_items_flag_t,
331 p_globals.calc_annualized_basis_code_t,
332 p_globals.allow_tenancy_overlap_flag_t,
333 p_globals.recalc_ir_on_acc_chg_flag_t,
334 p_globals.smallest_term_amount_t, --#@#Bug4291907
335 p_globals.incl_terms_by_default_flag_t
336 FROM gl_sets_of_books gl,
337 pn_system_setup_options pn
338 WHERE gl.set_of_books_id = pn.set_of_books_id;
339
340 END IF;
341 EXCEPTION
342 WHEN OTHERS THEN
343 IF pn_mo_cache_utils.get_multi_org_flag = 'Y' THEN
344 raise_application_error(-20001,'Error: no data found in multi org table' || to_char(sqlcode));
345 app_exception.raise_exception;
346 ELSE -- non multi org
347 raise_application_error(-20001,'Error: no data found in profile_setup table'|| to_char(sqlcode));
348 app_exception.raise_exception;
349 END IF;
350
351 END retrieve_globals;
352
353 -------------------------------------------------------------------------------
354 -- PROCEDURE : retrieve_org_id_specific
355 -- PURPOSE : returns record that contains profile information regarding
356 -- a specific OU
357 -- NOTE o This procedure is an addendum (not defined in the template)
358 -- o For non multi org cases
359 -- oo assumed that only 1 row exists in the
360 -- pn_system_setup_options table
361 --
362 -- INVOKED FROM :
363 -- ARGUMENTS : IN p_org_id
364 -- OUT p_table
365 -- RETURNS : NONE
366 -- REFERENCE : PN_COMMON.debug()
367 -- HISTORY:
368 -- 11-APR-02 ftanudja o created
369 -- 12-SEP-02 ftanudja o incorporated new profile options
370 -- 30-SEP-02 ftanudja o commented out code until shared services effective
371 -- 30-JUN-03 ftanudja o added new columns from recovery module.
372 -- 19-JAN-04 atuppad o added new cols grouping_rule_id,
373 -- gl_transfer_mode + submit_journal_import_flag
374 -- 01-JUL-04 atuppad o added new column for default user view
375 -- 24-AUG-04 ftanudja o added extend_indexrent_term_flag. 3756208.
376 -- 28-OCT-04 atuppad o Added code for 5 columns of Retro.
377 -- 02-FEB-05 ftanudja o added colm allow_tenancy_overlap_flag. 4150676
378 -- 15-SEP-05 pikhar o added recalc_ir_on_acc_chg_flag to SELECT
379 -- statement and FOR loop of retrieve_org_id_specific
380 -- 29-NOV-05 Kiran o Fixed the cartesian join in the the query
381 -- 06-APR-06 hkulkarn o Modified to include smallest_term_amount in
382 -- the SELECT statement.
383 -- 11-SEP-07 rthumma o Modified to include incl_terms_by_default_flag
384 -- in the SELECT statement.
385 -------------------------------------------------------------------------------
386 PROCEDURE retrieve_org_id_specific(p_org_id IN NUMBER,
387 p_table OUT NOCOPY GlobalsRecord)
388 IS
389 l_dummy_org_id NUMBER := -666;
390 l_count PLS_INTEGER;
391 l_name VARCHAR2(200);
392
393 CURSOR profile_cur(p_id IN NUMBER) IS
394 SELECT gl.name set_of_books_name,
395 gl.chart_of_accounts_id chart_of_accounts_id,
396 hr.name legal_entity_name,
397 gl.currency_code functional_currency_code,
398 pn.set_of_books_id set_of_books_id,
399 pn.accounting_option accounting_option,
400 pn.default_currency_conv_type default_currency_conv_type,
401 pn.space_assign_sysdate_optn space_assign_sysdate_optn,
402 pn.multiple_tenancy_lease multiple_tenancy_lease,
403 pn.auto_comp_num_gen auto_comp_num_gen,
404 pn.auto_index_num_gen auto_index_num_gen,
405 pn.auto_lease_num_gen auto_lease_num_gen,
406 pn.auto_space_distribution auto_space_distribution,
407 pn.auto_var_rent_num_gen auto_var_rent_num_gen,
408 pn.auto_rec_agr_num_flag auto_rec_agr_num_flag,
409 pn.auto_rec_exp_num_flag auto_rec_exp_num_flag,
410 pn.auto_rec_arcl_num_flag auto_rec_arcl_num_flag,
411 pn.auto_rec_expcl_num_flag auto_rec_expcl_num_flag,
412 pn.cons_rec_agrterms_flag cons_rec_agrterms_flag,
413 pn.location_code_separator location_code_separator,
414 pn.default_locn_area_flag default_locn_area_flag,
415 pn.grouping_rule_id grouping_rule_id,
416 pn.gl_transfer_mode gl_transfer_mode,
417 pn.submit_journal_import_flag submit_journal_import_flag,
418 pn.legacy_data_cutoff_date legacy_data_cutoff_date,
419 pn.default_user_view_code default_user_view_code,
420 pn.extend_indexrent_term_flag extend_indexrent_term_flag,
421 pn.sysdate_for_adj_flag sysdate_for_adj_flag,
422 pn.sysdate_as_trx_date_flag sysdate_as_trx_date_flag,
423 pn.renorm_adj_acc_all_draft_flag renorm_adj_acc_all_draft_flag,
424 pn.consolidate_adj_items_flag consolidate_adj_items_flag,
425 pn.calc_annualized_basis_code calc_annualized_basis_code,
426 pn.allow_tenancy_overlap_flag allow_tenancy_overlap_flag,
427 pn.recalc_ir_on_acc_chg_flag recalc_ir_on_acc_chg_flag,
428 pn.smallest_term_amount smallest_term_amount,
429 pn.incl_terms_by_default_flag incl_terms_by_default_flag
430 FROM hr_legal_entities hr,
431 gl_sets_of_books gl,
432 pn_system_setup_options pn
433 WHERE mo_global.check_access(pn.org_id) = 'Y'
434 AND gl.set_of_books_id = pn.set_of_books_id
435 AND hr.organization_id (+) = pn.org_id
436 AND nvl(pn.org_id,l_dummy_org_id) = p_id;
437
438 BEGIN
439
440 IF pn_mo_cache_utils.get_multi_org_flag = 'Y' THEN
441 IF p_org_id IS NULL THEN
442 mo_utils.get_default_ou(l_dummy_org_id, l_name, l_count);
443 ELSE
444 l_dummy_org_id := p_org_id;
445 END IF;
446 END IF;
447
448 FOR profile_rec IN profile_cur(l_dummy_org_id) LOOP
449 p_table.set_of_books_id := profile_rec.set_of_books_id;
450 p_table.chart_of_accounts_id := profile_rec.chart_of_accounts_id;
451 p_table.legal_entity_name := profile_rec.legal_entity_name;
452 p_table.functional_currency_code := profile_rec.functional_currency_code;
453 p_table.accounting_option := profile_rec.accounting_option;
454 p_table.default_currency_conv_type := profile_rec.default_currency_conv_type;
455 p_table.space_assign_sysdate_optn := profile_rec.space_assign_sysdate_optn;
456 p_table.multiple_tenancy_lease := profile_rec.multiple_tenancy_lease;
457 p_table.auto_comp_num_gen := profile_rec.auto_comp_num_gen;
458 p_table.auto_index_num_gen := profile_rec.auto_index_num_gen;
459 p_table.auto_lease_num_gen := profile_rec.auto_lease_num_gen;
460 p_table.auto_space_distribution := profile_rec.auto_space_distribution;
461 p_table.auto_var_rent_num_gen := profile_rec.auto_var_rent_num_gen;
462 p_table.auto_rec_agr_num_flag := profile_rec.auto_rec_agr_num_flag;
463 p_table.auto_rec_exp_num_flag := profile_rec.auto_rec_exp_num_flag;
464 p_table.auto_rec_arcl_num_flag := profile_rec.auto_rec_arcl_num_flag;
465 p_table.auto_rec_expcl_num_flag := profile_rec.auto_rec_expcl_num_flag;
466 p_table.cons_rec_agrterms_flag := profile_rec.cons_rec_agrterms_flag;
467 p_table.location_code_separator := profile_rec.location_code_separator;
468 p_table.default_locn_area_flag := profile_rec.default_locn_area_flag;
469 p_table.grouping_rule_id := profile_rec.grouping_rule_id;
470 p_table.gl_transfer_mode := profile_rec.gl_transfer_mode;
471 p_table.submit_journal_import_flag := profile_rec.submit_journal_import_flag;
472 p_table.legacy_data_cutoff_date := profile_rec.legacy_data_cutoff_date;
473 p_table.default_user_view_code := profile_rec.default_user_view_code;
474 p_table.extend_indexrent_term_flag := profile_rec.extend_indexrent_term_flag;
475 p_table.sysdate_for_adj_flag := profile_rec.sysdate_for_adj_flag;
476 p_table.sysdate_as_trx_date_flag := profile_rec.sysdate_as_trx_date_flag;
477 p_table.renorm_adj_acc_all_draft_flag := profile_rec.renorm_adj_acc_all_draft_flag;
478 p_table.consolidate_adj_items_flag := profile_rec.consolidate_adj_items_flag;
479 p_table.calc_annualized_basis_code := profile_rec.calc_annualized_basis_code;
480 p_table.calc_annualized_basis_code := profile_rec.calc_annualized_basis_code;
481 p_table.recalc_ir_on_acc_chg_flag := profile_rec.recalc_ir_on_acc_chg_flag;
482 p_table.smallest_term_amount := profile_rec.smallest_term_amount; --#@#Bug4291907
483 p_table.incl_terms_by_default_flag := profile_rec.incl_terms_by_default_flag;
484 END LOOP;
485
486 -- set of books is a 'not null' column : check if value populated to see if everything OK
487
488 IF p_table.set_of_books_id IS NULL THEN
489 raise NO_DATA_FOUND;
490 END IF;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 IF get_multi_org_flag = 'Y' THEN
495 raise_application_error(-20001,'Error: no data found in multi org table' || to_char(sqlcode));
496 app_exception.raise_exception;
497 ELSE -- non multi org
498 raise_application_error(-20001,'Error: no data found in profile_setup table' || to_char(sqlcode));
499 app_exception.raise_exception;
500 END IF;
501 END retrieve_org_id_specific;
502
503 -------------------------------------------------------------------------------
504 -- FUNCTION : get_profile_value
505 -- PURPOSE : wrapper function for fnd_profile.value() due to MO
506 -- NOTE : temporarily assume either 1 or 0 entry for
507 -- pn_system_setup_options table (until MO is activated)
508 -- INVOKED FROM : get_profile_value in PNCOMMON.pld and *.pls files.
509 -- ARGUMENTS : IN p_profile_name, p_org_id
510 -- OUT NONE
511 -- RETURNS : the profile value for a profile name for an operating unit
512 -- REFERENCE : PN_COMMON.debug()
513 -- HISTORY :
514 -- 12-SEP-02 ftanudja o created
515 -- 30-JUN-03 ftanudja o added new profiles from recovery module.
516 -- 08-AUG-03 Ashish o Bug#3087785 Change the profile name
517 -- PN_AUTO_SPACE_DISTRIBUTION to
518 -- PN_AUTOMATIC_SPACE_DISTRIBUTION
519 -- 09-DEC-03 ftanudja o added default_locn_area_flag. 3257508.
520 -- 19-JAN-04 atuppad o added 3 new columns: grouping_rule_id,
521 -- gl_transfer_mode + submit_journal_import_flag.
522 -- 01-JUL-04 atuppad o added new column for default user view
523 -- 24-AUG-04 ftanudja o added extend_indexrent_term_flag. 3756208.
524 -- 27-OCT-04 stripath o Fixed for BUG# 3961117, added legacy_data_cutoff_date
525 -- in cursor get_profile for profile name PN_CUTOFF_DATE.
526 -- 28-OCT-04 atuppad o Added code for 5 columns of Retro.
527 -- 02-FEB-05 ftanudja o added colm allow_tenancy_overlap_flag. 4150676
528 -- 15-sep-05 pikhar o added recalc_ir_on_acc_chg_flag to SELECT
529 -- statement and IF condition of get_profile_value
530 -- 29-NOV-05 Kiran o Fixed the cartesian join in the the query
531 -- 06-APR-06 hkulkarn o Modified to include smallest_term_amount in
532 -- the SELECT statement.
533 -- 11-SEP-07 rthumma o Modified to include incl_terms_by_default_flag
534 -- in the SELECT statement and IF condition of
535 -- get_profile_value.
536 -------------------------------------------------------------------------------
537
538 FUNCTION get_profile_value(p_profile_name IN VARCHAR2, p_org_id IN NUMBER) RETURN VARCHAR2 IS
539
540 l_answer VARCHAR2(100):= NULL;
541
542 CURSOR get_profile IS
543 SELECT set_of_books_id,
544 accounting_option,
545 default_currency_conv_type,
546 space_assign_sysdate_optn,
547 multiple_tenancy_lease,
548 auto_comp_num_gen,
549 auto_index_num_gen,
550 auto_lease_num_gen,
551 auto_var_rent_num_gen,
552 auto_space_distribution,
553 auto_rec_agr_num_flag,
554 auto_rec_exp_num_flag,
555 auto_rec_arcl_num_flag,
556 auto_rec_expcl_num_flag,
557 cons_rec_agrterms_flag,
558 location_code_separator,
559 default_locn_area_flag,
560 grouping_rule_id,
561 gl_transfer_mode,
562 submit_journal_import_flag,
563 default_user_view_code,
564 extend_indexrent_term_flag,
565 TO_CHAR(legacy_data_cutoff_date, 'MM/DD/YYYY') legacy_cutoff_date,
566 sysdate_for_adj_flag,
567 sysdate_as_trx_date_flag,
568 renorm_adj_acc_all_draft_flag,
569 consolidate_adj_items_flag,
570 calc_annualized_basis_code,
571 allow_tenancy_overlap_flag,
572 recalc_ir_on_acc_chg_flag,
573 smallest_term_amount,
574 incl_terms_by_default_flag
575 FROM pn_system_setup_options
576 WHERE org_id = nvl(p_org_id, fnd_profile.value('ORG_ID'));
577
578 BEGIN
579
580 IF pn_mo_cache_utils.get_multi_org_flag = 'Y' THEN
581
582 /* assume single-org */
583
584 FOR answer_cur IN get_profile LOOP
585
586 IF UPPER(p_profile_name) = 'PN_SET_OF_BOOKS_ID' THEN
587 l_answer := answer_cur.set_of_books_id;
588 ELSIF UPPER(p_profile_name) = 'PN_ACCOUNTING_OPTION' THEN
589 l_answer := answer_cur.accounting_option;
590 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_COMPANY_NUMBER' THEN
591 l_answer := answer_cur.auto_comp_num_gen;
592 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_INDEX_RENT_NUMBERING' THEN
593 l_answer := answer_cur.auto_index_num_gen;
594 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_LEASE_NUMBER' THEN
595 l_answer := answer_cur.auto_lease_num_gen;
596 ELSIF UPPER(p_profile_name) = 'PN_AUTO_VAR_RENT_NUM' THEN
597 l_answer := answer_cur.auto_var_rent_num_gen;
598 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_SPACE_DISTRIBUTION' THEN
599 l_answer := answer_cur.auto_space_distribution;
600 ELSIF UPPER(p_profile_name) = 'PN_CURRENCY_CONV_RATE_TYPE' THEN
601 l_answer := answer_cur.default_currency_conv_type;
602 ELSIF UPPER(p_profile_name) = 'PN_SPASGN_CHNGDT_OPTN' THEN
603 l_answer := answer_cur.space_assign_sysdate_optn;
604 ELSIF UPPER(p_profile_name) = 'PN_MULTIPLE_LEASE_FOR_LOCATION' THEN
605 l_answer := answer_cur.multiple_tenancy_lease;
606 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_REC_EXPENSE_NUM' THEN
607 l_answer := answer_cur.auto_rec_exp_num_flag;
608 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_AREA_CLS_NUM' THEN
609 l_answer := answer_cur.auto_rec_arcl_num_flag;
610 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_EXPENSE_CLASS_NUMBER' THEN
611 l_answer := answer_cur.auto_rec_expcl_num_flag;
612 ELSIF UPPER(p_profile_name) = 'PN_AUTOMATIC_REC_AGR_NUM' THEN
613 l_answer := answer_cur.auto_rec_agr_num_flag;
614 ELSIF UPPER(p_profile_name) = 'PN_REC_CONSOLIDATE_TERMS' THEN
615 l_answer := answer_cur.cons_rec_agrterms_flag;
616 ELSIF UPPER(p_profile_name) = 'PN_LOCATION_CODE_SEPARATOR' THEN
617 l_answer := answer_cur.location_code_separator;
618 ELSIF UPPER(p_profile_name) = 'PN_DEFAULT_LOCTN_AREA' THEN
619 l_answer := answer_cur.default_locn_area_flag;
620 ELSIF UPPER(p_profile_name) = 'PN_GROUPING_RULE_ID' THEN
621 l_answer := answer_cur.grouping_rule_id;
622 ELSIF UPPER(p_profile_name) = 'PN_GL_TRANSFER_MODE' THEN
623 l_answer := answer_cur.gl_transfer_mode;
624 ELSIF UPPER(p_profile_name) = 'PN_SUBMIT_JOURNAL_IMPORT' THEN
625 l_answer := answer_cur.submit_journal_import_flag;
626 ELSIF UPPER(p_profile_name) = 'PN_DEFAULT_USER_VIEW' THEN
627 l_answer := answer_cur.default_user_view_code;
628 ELSIF UPPER(p_profile_name) = 'PN_EXTEND_INDEXRENT_TERM' THEN
629 l_answer := answer_cur.extend_indexrent_term_flag;
630 ELSIF UPPER(p_profile_name) = 'PN_CUTOFF_DATE' THEN
631 l_answer := answer_cur.legacy_cutoff_date;
632 ELSIF UPPER(p_profile_name) = 'PN_USE_SYSDATE_FOR_ADJ' THEN
633 l_answer := answer_cur.sysdate_for_adj_flag;
634 ELSIF UPPER(p_profile_name) = 'PN_USE_SYSDATE_AS_TRX_DATE' THEN
635 l_answer := answer_cur.sysdate_as_trx_date_flag;
636 ELSIF UPPER(p_profile_name) = 'PN_RENORM_ACC_ALL_DRAFT_SCH' THEN
637 l_answer := answer_cur.renorm_adj_acc_all_draft_flag;
638 ELSIF UPPER(p_profile_name) = 'PN_CONSOLIDATE_ADJ_ITEMS' THEN
639 l_answer := answer_cur.consolidate_adj_items_flag;
640 ELSIF UPPER(p_profile_name) = 'PN_CALC_ANNUALIZED_BASIS' THEN
641 l_answer := answer_cur.calc_annualized_basis_code;
642 ELSIF UPPER(p_profile_name) = 'PN_MULT_TNC_SAME_LEASE' THEN
643 l_answer := answer_cur.allow_tenancy_overlap_flag;
644 ELSIF UPPER(p_profile_name) = 'RECALC_IR_ON_ACC_CHG_FLAG' THEN
645 l_answer := answer_cur.recalc_ir_on_acc_chg_flag;
646 ELSIF UPPER(p_profile_name) = 'SMALLEST_TERM_AMOUNT' THEN --#@#Bug4291907
647 l_answer := answer_cur.smallest_term_amount; --#@#Bug4291907
648 ELSIF UPPER(p_profile_name) = 'INCL_TERMS_BY_DEFAULT_FLAG' THEN
649 l_answer := answer_cur.incl_terms_by_default_flag;
650 ELSE
651 l_answer := fnd_profile.value(p_profile_name);
652 /* handle for non OU level profiles */
653 END IF;
654
655 END LOOP;
656
657 RETURN l_answer;
658
659 ELSE
660 RETURN fnd_profile.value(p_profile_name);
661 END IF;
662
663 END get_profile_value;
664
665 -------------------------------------------------------------------------------
666 -- FUNCTION : mo_global_init
667 -- PURPOSE : wrapper function for mo_global.init. Does nothing in 11i.
668 -- calls mo_global.init in R12
669 -- INVOKED FROM : PNCOMMON.pld
670 -- ARGUMENTS : IN p_appl_short_name = 'PN'
671 -- HISTORY
672 -- 06-AUG-05 Kiran o Created
673 -------------------------------------------------------------------------------
674 PROCEDURE mo_global_init(p_appl_short_name IN VARCHAR2) IS
675
676 BEGIN
677
678 IF is_MOAC_enabled AND p_appl_short_name IS NOT NULL THEN
679 mo_global.init(p_appl_short_name);
680 END IF;
681
682 EXCEPTION
683 WHEN others THEN
684 RAISE;
685 END mo_global_init;
686
687 -------------------------------------------------------------------------------
688 -- FUNCTION : fnd_req_set_org_id
689 -- PURPOSE : wrapper function for fnd_request.set_org_id Does nothing
690 -- in 11i Calls fnd_request.set_org_id in R12
691 -- INVOKED FROM : PNTLEASE.pld,PNTTERMS.pld
692 -- ARGUMENTS : IN p_org_id
693 -- HISTORY
694 -- 12-SEP-05 SatyaDeep o Created
695 -------------------------------------------------------------------------------
696 PROCEDURE fnd_req_set_org_id(p_org_id IN NUMBER) IS
697
698 BEGIN
699
700 IF is_MOAC_enabled AND p_org_id IS NOT NULL THEN
701 fnd_request.set_org_id(p_org_id);
702 END IF;
703
704 EXCEPTION
705 WHEN others THEN
706 RAISE;
707 END fnd_req_set_org_id;
708
709 --------------------------------------------------------------------------------
710 -- NAME : get_current_org_id
711 -- DESCRIPTION : Returns the current org id
712 -- PURPOSE :
713 -- INVOKED FROM :
714 -- ARGUMENTS : NONE
715 -- RETURN : Returns current org id
716 -- REFERENCE : PN_COMMON.debug()
717 -- HISTORY :
718 --
719 -- 10-nov-05 piagrawa o Created
720 --------------------------------------------------------------------------------
721 FUNCTION get_current_org_id RETURN NUMBER
722 IS
723 BEGIN
724 RETURN mo_global.get_current_org_id;
725 EXCEPTION
726 WHEN others THEN
727 RAISE;
728 END get_current_org_id;
729
730 END pn_mo_cache_utils;