[Home] [Help]
PACKAGE BODY: APPS.AR_MO_CACHE_UTILS
Source
1 PACKAGE BODY ar_mo_cache_utils AS
2 /*$Header: ARMOCSHB.pls 120.17 2006/03/16 00:55:42 jypandey noship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 -- Bug 3251839 - tm_installed and tm_default_setup functions provide a
6 -- wrapper to the TM boolean functions that can be called in sql
7
8 FUNCTION tm_installed
9 RETURN VARCHAR2 IS
10 BEGIN
11 IF PG_DEBUG in ('Y', 'C') THEN
12 arp_util.debug('ar_mo_cache_utils.tm_installed()');
13 END IF;
14
15 IF ozf_claim_install.check_installed THEN
16 RETURN 'Y';
17 ELSE
18 RETURN 'N';
19 END IF;
20 EXCEPTION
21 WHEN OTHERS THEN
22 IF PG_DEBUG in ('Y', 'C') THEN
23 arp_util.debug('EXCEPTION: problem calling ozf_claim_install.check_installed - ar_mo_cache_utils.tm_installed');
24 END IF;
25 APP_EXCEPTION.raise_exception;
26 END;
27
28 FUNCTION tm_default_setup
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF PG_DEBUG in ('Y', 'C') THEN
32 arp_util.debug('ar_mo_cache_utils.tm_default_setup()');
33 END IF;
34
35 IF ozf_claim_install.check_default_setup THEN
36 RETURN 'Y';
37 ELSE
38 RETURN 'N';
39 END IF;
40 EXCEPTION
41 WHEN OTHERS THEN
42 IF PG_DEBUG in ('Y', 'C') THEN
43 arp_util.debug('EXCEPTION: problem calling ozf_claim_install.check_default_setup - ar_mo_cache_utils.tm_default_setup');
44 END IF;
45 APP_EXCEPTION.raise_exception;
46 END;
47
48 PROCEDURE retrieve_globals( p_globals OUT NOCOPY Globalstable,
49 p_org_id IN NUMBER DEFAULT NULL)
50 IS
51 l_exp_flag varchar2(1);
52 l_sob_test varchar2(1);
53 l_zx_test varchar2(1);
54
55 /* Bug 5051539 */
56 cursor c_exception(c_org_id IN NUMBER) is
57 SELECT *
58 FROM ar_system_parameters sp
59 WHERE sp.org_id = nvl(c_org_id , sp.org_id);
60
61 BEGIN
62 IF PG_DEBUG in ('Y', 'C') THEN
63 arp_util.debug('ar_mo_cache_utils.retrieve_globals()+');
64 END IF;
65
66 /* ---------------------------------------------------------------
67 This statement fetches operating unit attributes from the
68 database and stores them into nested tables using BULK COLLECT
69 --------------------------------------------------------------- */
70 IF mo_utils.get_multi_org_flag = 'Y' THEN
71 /* --------------------------------
72 For Multi org case
73 --------------------------------- */
74 /* Bug 3836832 - dummy salesrep is retrieved directly from jtf_rs_salesreps
75 instead of ra_salesreps view. Lookup retrieval from ar_lookups
76 replaced by calls to get_lookup function */
77
78 /* Bug 4188835 - replaced numerous sp columns with some
79 from zx_product_options */
80
81 /* 4923225 - made zx_product_options an outer join */
82
83 /* 5051539 - Added org_id filter to only retrieve for org_id if passed */
84
85 select
86 sp.org_id,
87 sob.chart_of_accounts_id,
88 sob.set_of_books_id,
89 sob.name,
90 fc.currency_code,
91 sp.accounting_method,
92 sp.accrue_interest,
93 sp.unearned_discount,
94 sp.partial_discount_flag,
95 sp.print_remit_to,
96 sp.default_cb_due_date,
97 sp.auto_site_numbering,
98 sp.cash_basis_set_of_books_id,
99 sp.code_combination_id_gain,
100 sp.autocash_hierarchy_id,
101 sp.run_gl_journal_import_flag,
102 sp.cer_split_amount,
103 sp.cer_dso_days,
104 sp.posting_days_per_cycle,
105 sp.address_validation,
106 sp.calc_discount_on_lines_flag,
107 sp.change_printed_invoice_flag,
108 sp.code_combination_id_loss,
109 sp.create_reciprocal_flag,
110 sp.default_country,
111 sp.default_territory,
112 sp.generate_customer_number,
113 sp.invoice_deletion_flag,
114 sp.location_structure_id,
115 sp.site_required_flag,
116 zxpo.tax_allow_compound_flag,
117 sp.tax_header_level_flag, -- ?
118 zxpo.allow_tax_rounding_ovrd_flag, --tax_rounding_allow_override,
119 sp.tax_invoice_print,
120 zxpo.tax_method_code,
121 zxpo.tax_use_customer_exempt_flag,
122 sp.tax_use_cust_exc_rate_flag, -- obsolete
123 zxpo.tax_use_loc_exc_rate_flag,
124 zxpo.tax_use_product_exempt_flag,
125 sp.tax_use_prod_exc_rate_flag, -- obsolete
126 sp.tax_use_site_exc_rate_flag, -- obsolete
127 sp.ai_log_file_message_level,
128 sp.ai_max_memory_in_bytes,
129 sp.ai_acct_flex_key_left_prompt,
130 sp.ai_mtl_items_key_left_prompt,
131 sp.ai_territory_key_left_prompt,
132 sp.ai_purge_interface_tables_flag,
133 sp.ai_activate_sql_trace_flag,
134 sp.default_grouping_rule_id,
135 sp.salesrep_required_flag,
136 sp.auto_rec_invoices_per_commit,
137 sp.auto_rec_receipts_per_commit,
138 sp.pay_unrelated_invoices_flag,
139 sp.print_home_country_flag,
140 sp.location_tax_account,
141 sp.from_postal_code,
142 sp.to_postal_code,
143 sp.tax_registration_number, -- zx_registrations
144 sp.populate_gl_segments_flag,
145 sp.unallocated_revenue_ccid,
146 sob.period_set_name,
147 fc.precision,
148 fc.EXTENDED_PRECISION,
149 fc.MINIMUM_ACCOUNTABLE_UNIT,
150 rs.name,
151 arpt_sql_func_util.get_lookup_meaning( 'YES/NO', 'Y'),
152 arpt_sql_func_util.get_lookup_meaning( 'YES/NO', 'N'),
153 arpt_sql_func_util.get_lookup_meaning( 'TAX_CONTROL_FLAG', 'S'),
154 zxpo.inclusive_tax_used_flag,
155 sp.tax_enforce_account_flag, -- zx_evnt_cls_options
156 sp.ta_installed_flag,
157 sp.bills_receivable_enabled_flag,
158 sp.attribute_category,
159 sp.attribute1,
160 sp.attribute2,
161 sp.attribute3,
162 sp.attribute4,
163 sp.attribute5,
164 sp.attribute6,
165 sp.attribute7,
166 sp.attribute8,
167 sp.attribute9,
168 sp.attribute10,
169 sp.attribute11,
170 sp.attribute12,
171 sp.attribute13,
172 sp.attribute14,
173 sp.attribute15,
174 sp.created_by,
175 sp.creation_date,
176 sp.last_updated_by,
177 sp.last_update_date,
178 sp.last_update_login,
179 zxpo.tax_classification_code, --tax_code,
180 sp.tax_currency_code, --?
181 zxpo.tax_minimum_accountable_unit,
182 zxpo.tax_precision,
183 zxpo.tax_rounding_rule,
184 sp.tax_use_account_exc_rate_flag, -- obsol. use def_option_hier 1-7
185 sp.tax_use_system_exc_rate_flag,
186 sp.tax_hier_site_exc_rate,
187 sp.tax_hier_cust_exc_rate,
188 sp.tax_hier_prod_exc_rate,
189 sp.tax_hier_account_exc_rate,
190 sp.tax_hier_system_exc_rate, -- obsol. use def_option_hier 1-7
191 sp.tax_database_view_set, -- obsolete
192 sp.global_attribute1,
193 sp.global_attribute2,
194 sp.global_attribute3,
195 sp.global_attribute4,
196 sp.global_attribute5,
197 sp.global_attribute6,
198 sp.global_attribute7,
199 sp.global_attribute8,
200 sp.global_attribute9,
201 sp.global_attribute10,
202 sp.global_attribute11,
203 sp.global_attribute12,
204 sp.global_attribute13,
205 sp.global_attribute14,
206 sp.global_attribute15,
207 sp.global_attribute16,
208 sp.global_attribute17,
209 sp.global_attribute18,
210 sp.global_attribute19,
211 sp.global_attribute20,
212 sp.global_attribute_category,
213 sp.rule_set_id,
214 sp.code_combination_id_round,
215 sp.trx_header_level_rounding,
216 sp.trx_header_round_ccid,
217 sp.finchrg_receivables_trx_id,
218 sp.sales_tax_geocode,
219 sp.rev_transfer_clear_ccid,
220 sp.sales_credit_pct_limit,
221 sp.max_wrtoff_amount,
222 sp.irec_cc_receipt_method_id,
223 sp.show_billing_number_flag,
224 sp.cross_currency_rate_type,
225 sp.document_seq_gen_level,
226 'Y', --sp.calc_tax_on_credit_memo_flag,
227 sp.IREC_BA_RECEIPT_METHOD_ID,
228 tm_installed,
229 tm_default_setup,
230 sp.payment_threshold,
231 sp.standard_refund,
232 sp.credit_classification1,
233 sp.credit_classification2,
234 sp.credit_classification3,
235 sp.unmtch_claim_creation_flag,
236 sp.matched_claim_creation_flag,
237 sp.matched_claim_excl_cm_flag,
238 sp.min_wrtoff_amount,
239 sp.min_refund_amount
240 BULK COLLECT
241 INTO
242 p_globals.org_id_t,
243 p_globals.chart_of_accounts_id_t,
244 p_globals.set_of_books_id_t,
245 p_globals.set_of_books_name_t,
246 p_globals.currency_code_t,
247 p_globals.accounting_method_t,
248 p_globals.accrue_interest_t,
249 p_globals.unearned_discount_t,
250 p_globals.partial_discount_flag_t,
251 p_globals.print_remit_to_t,
252 p_globals.default_cb_due_date_t,
253 p_globals.auto_site_numbering_t,
254 p_globals.cash_basis_set_of_books_id_t,
255 p_globals.code_combination_id_gain_t,
256 p_globals.autocash_hierarchy_id_t,
257 p_globals.run_gl_journal_import_flag_t,
258 p_globals.cer_split_amount_t,
259 p_globals.cer_dso_days_t,
260 p_globals.posting_days_per_cycle_t,
261 p_globals.address_validation_t,
262 p_globals.calc_discount_on_lines_flag_t,
263 p_globals.change_printed_invoice_flag_t,
264 p_globals.code_combination_id_loss_t,
265 p_globals.create_reciprocal_flag_t,
266 p_globals.default_country_t,
267 p_globals.default_territory_t,
268 p_globals.generate_customer_number_t,
269 p_globals.invoice_deletion_flag_t,
270 p_globals.location_structure_id_t,
271 p_globals.site_required_flag_t,
272 p_globals.tax_allow_compound_flag_t,
273 p_globals.tax_header_level_flag_t,
274 p_globals.tax_rounding_allow_override_t,
275 p_globals.tax_invoice_print_t,
276 p_globals.tax_method_t,
277 p_globals.tax_use_customer_exempt_flag_t,
278 p_globals.tax_use_cust_exc_rate_flag_t,
279 p_globals.tax_use_loc_exc_rate_flag_t,
280 p_globals.tax_use_product_exempt_flag_t,
281 p_globals.tax_use_prod_exc_rate_flag_t,
282 p_globals.tax_use_site_exc_rate_flag_t,
283 p_globals.ai_log_file_message_level_t,
284 p_globals.ai_max_memory_in_bytes_t,
285 p_globals.ai_acct_flex_key_left_prompt_t,
286 p_globals.ai_mtl_items_key_left_prompt_t,
287 p_globals.ai_territory_key_left_prompt_t,
288 p_globals.ai_purge_int_tables_flag_t,
289 p_globals.ai_activate_sql_trace_flag_t,
290 p_globals.default_grouping_rule_id_t,
291 p_globals.salesrep_required_flag_t,
292 p_globals.auto_rec_invoices_per_commit_t,
293 p_globals.auto_rec_receipts_per_commit_t,
294 p_globals.pay_unrelated_invoices_flag_t,
295 p_globals.print_home_country_flag_t,
296 p_globals.location_tax_account_t,
297 p_globals.from_postal_code_t,
298 p_globals.to_postal_code_t,
299 p_globals.tax_registration_number_t,
300 p_globals.populate_gl_segments_flag_t,
301 p_globals.unallocated_revenue_ccid_t,
302 p_globals.period_set_name_t,
303 p_globals.base_precision_t,
304 p_globals.base_extended_precision_t,
305 p_globals.base_min_accountable_unit_t,
306 p_globals.salescredit_name_t,
307 p_globals.yes_meaning_t,
308 p_globals.no_meaning_t,
309 p_globals.tax_exempt_flag_meaning_t,
310 p_globals.inclusive_tax_used_t,
311 p_globals.tax_enforce_account_flag_t,
312 p_globals.ta_installed_flag_t,
313 p_globals.br_enabled_flag_t,
314 p_globals.attribute_category_t,
315 p_globals.attribute1_t,
316 p_globals.attribute2_t,
317 p_globals.attribute3_t,
318 p_globals.attribute4_t,
319 p_globals.attribute5_t,
320 p_globals.attribute6_t,
321 p_globals.attribute7_t,
322 p_globals.attribute8_t,
323 p_globals.attribute9_t,
324 p_globals.attribute10_t,
325 p_globals.attribute11_t,
326 p_globals.attribute12_t,
327 p_globals.attribute13_t,
328 p_globals.attribute14_t,
329 p_globals.attribute15_t,
330 p_globals.created_by_t,
331 p_globals.creation_date_t,
332 p_globals.last_updated_by_t,
336 p_globals.tax_currency_code_t,
333 p_globals.last_update_date_t,
334 p_globals.last_update_login_t,
335 p_globals.tax_code_t,
337 p_globals.tax_minimum_accountable_unit_t,
338 p_globals.tax_precision_t,
339 p_globals.tax_rounding_rule_t,
340 p_globals.tax_use_acc_exc_rate_flag_t,
341 p_globals.tax_use_system_exc_rate_flag_t,
342 p_globals.tax_hier_site_exc_rate_t,
343 p_globals.tax_hier_cust_exc_rate_t,
344 p_globals.tax_hier_prod_exc_rate_t,
345 p_globals.tax_hier_account_exc_rate_t,
346 p_globals.tax_hier_system_exc_rate_t,
347 p_globals.tax_database_view_set_t,
348 p_globals.global_attribute1_t,
349 p_globals.global_attribute2_t,
350 p_globals.global_attribute3_t,
351 p_globals.global_attribute4_t,
352 p_globals.global_attribute5_t,
353 p_globals.global_attribute6_t,
354 p_globals.global_attribute7_t,
355 p_globals.global_attribute8_t,
356 p_globals.global_attribute9_t,
357 p_globals.global_attribute10_t,
358 p_globals.global_attribute11_t,
359 p_globals.global_attribute12_t,
360 p_globals.global_attribute13_t,
361 p_globals.global_attribute14_t,
362 p_globals.global_attribute15_t,
363 p_globals.global_attribute16_t,
364 p_globals.global_attribute17_t,
365 p_globals.global_attribute18_t,
366 p_globals.global_attribute19_t,
367 p_globals.global_attribute20_t,
368 p_globals.global_attribute_category_t,
369 p_globals.rule_set_id_t,
370 p_globals.code_combination_id_round_t,
371 p_globals.trx_header_level_rounding_t,
372 p_globals.trx_header_round_ccid_t,
373 p_globals.finchrg_receivables_trx_id_t,
374 p_globals.sales_tax_geocode_t,
375 p_globals.rev_transfer_clear_ccid_t,
376 p_globals.sales_credit_pct_limit_t,
377 p_globals.max_wrtoff_amount_t,
378 p_globals.irec_cc_receipt_method_id_t,
379 p_globals.show_billing_number_flag_t,
380 p_globals.cross_currency_rate_type_t,
381 p_globals.document_seq_gen_level_t,
382 p_globals.calc_tax_on_credit_memo_flag_t,
383 p_globals.irec_ba_receipt_method_id_t,
384 p_globals.tm_installed_flag_t,
385 p_globals.tm_default_setup_flag_t,
386 p_globals.payment_threshold_t,
387 p_globals.standard_refund_t,
388 p_globals.credit_classification1_t,
389 p_globals.credit_classification2_t,
390 p_globals.credit_classification3_t,
391 p_globals.unmtch_claim_creation_flag_t,
392 p_globals.matched_claim_creation_flag_t,
393 p_globals.matched_claim_excl_cm_flag_t,
394 p_globals.min_wrtoff_amount_t,
395 p_globals.min_refund_amount_t
396 from
397 gl_sets_of_books sob,
398 fnd_currencies fc,
399 jtf_rs_salesreps rs,
400 ar_system_parameters sp,
401 zx_product_options zxpo
402 where sp.set_of_books_id = sob.set_of_books_id
403 and sob.currency_code = fc.currency_code
404 and nvl(sp.org_id,-99) = nvl(rs.org_id,-99)
405 and sp.org_id = nvl(p_org_id, sp.org_id)/* Bug 5051539 */
406 and rs.salesrep_id = -3
407 and zxpo.application_id (+) = 222
408 and zxpo.org_id (+) = sp.org_id;
409 else
410 /* --------------------------------
411 For non multi org case
412 --------------------------------- */
413 select
414 -3115,
415 sob.chart_of_accounts_id,
416 sob.set_of_books_id,
417 sob.name,
418 fc.currency_code,
419 sp.accounting_method,
420 sp.accrue_interest,
421 sp.unearned_discount,
422 sp.partial_discount_flag,
423 sp.print_remit_to,
424 sp.default_cb_due_date,
425 sp.auto_site_numbering,
426 sp.cash_basis_set_of_books_id,
427 sp.code_combination_id_gain,
428 sp.autocash_hierarchy_id,
429 sp.run_gl_journal_import_flag,
430 sp.cer_split_amount,
431 sp.cer_dso_days,
432 sp.posting_days_per_cycle,
433 sp.address_validation,
434 sp.calc_discount_on_lines_flag,
435 sp.change_printed_invoice_flag,
436 sp.code_combination_id_loss,
437 sp.create_reciprocal_flag,
438 sp.default_country,
439 sp.default_territory,
440 sp.generate_customer_number,
441 sp.invoice_deletion_flag,
442 sp.location_structure_id,
443 sp.site_required_flag,
444 zxpo.tax_allow_compound_flag,
445 sp.tax_header_level_flag, --zx_party_tax_profile, zx_evnt_cls_options
446 zxpo.allow_tax_rounding_ovrd_flag, --tax_rounding_allow_override,
447 sp.tax_invoice_print,
448 zxpo.tax_method_code,
449 zxpo.tax_use_customer_exempt_flag,
450 sp.tax_use_cust_exc_rate_flag, -- obsolete
451 zxpo.tax_use_loc_exc_rate_flag,
452 zxpo.tax_use_product_exempt_flag,
453 sp.tax_use_prod_exc_rate_flag, -- obsolete
457 sp.ai_acct_flex_key_left_prompt,
454 sp.tax_use_site_exc_rate_flag, -- obsolete
455 sp.ai_log_file_message_level,
456 sp.ai_max_memory_in_bytes,
458 sp.ai_mtl_items_key_left_prompt,
459 sp.ai_territory_key_left_prompt,
460 sp.ai_purge_interface_tables_flag,
461 sp.ai_activate_sql_trace_flag,
462 sp.default_grouping_rule_id,
463 sp.salesrep_required_flag,
464 sp.auto_rec_invoices_per_commit,
465 sp.auto_rec_receipts_per_commit,
466 sp.pay_unrelated_invoices_flag,
467 sp.print_home_country_flag,
468 sp.location_tax_account,
469 sp.from_postal_code,
470 sp.to_postal_code,
471 sp.tax_registration_number, --zx_registrations
472 sp.populate_gl_segments_flag,
473 sp.unallocated_revenue_ccid,
474 sob.period_set_name,
475 fc.precision,
476 fc.EXTENDED_PRECISION,
477 fc.MINIMUM_ACCOUNTABLE_UNIT,
478 rs.name,
479 arpt_sql_func_util.get_lookup_meaning( 'YES/NO', 'Y'),
480 arpt_sql_func_util.get_lookup_meaning( 'YES/NO', 'N'),
481 arpt_sql_func_util.get_lookup_meaning( 'TAX_CONTROL_FLAG', 'S'),
482 zxpo.inclusive_tax_used_flag,
483 sp.tax_enforce_account_flag, --zx_evnt_cls_options
484 sp.ta_installed_flag,
485 sp.bills_receivable_enabled_flag,
486 sp.attribute_category,
487 sp.attribute1,
488 sp.attribute2,
489 sp.attribute3,
490 sp.attribute4,
491 sp.attribute5,
492 sp.attribute6,
493 sp.attribute7,
494 sp.attribute8,
495 sp.attribute9,
496 sp.attribute10,
497 sp.attribute11,
498 sp.attribute12,
499 sp.attribute13,
500 sp.attribute14,
501 sp.attribute15,
502 sp.created_by,
503 sp.creation_date,
504 sp.last_updated_by,
505 sp.last_update_date,
506 sp.last_update_login,
507 zxpo.tax_classification_code, --tax_code,
508 sp.tax_currency_code, --?
509 zxpo.tax_minimum_accountable_unit,
510 zxpo.tax_precision,
511 zxpo.tax_rounding_rule,
512 sp.tax_use_account_exc_rate_flag, -- obsol. use def_option_hier 1-7
513 sp.tax_use_system_exc_rate_flag,
514 sp.tax_hier_site_exc_rate,
515 sp.tax_hier_cust_exc_rate,
516 sp.tax_hier_prod_exc_rate,
517 sp.tax_hier_account_exc_rate,
518 sp.tax_hier_system_exc_rate, -- obsol. use def_option_hier 1-7
519 sp.tax_database_view_set, -- obsolete
520 sp.global_attribute1,
521 sp.global_attribute2,
522 sp.global_attribute3,
523 sp.global_attribute4,
524 sp.global_attribute5,
525 sp.global_attribute6,
526 sp.global_attribute7,
527 sp.global_attribute8,
528 sp.global_attribute9,
529 sp.global_attribute10,
530 sp.global_attribute11,
531 sp.global_attribute12,
532 sp.global_attribute13,
533 sp.global_attribute14,
534 sp.global_attribute15,
535 sp.global_attribute16,
536 sp.global_attribute17,
537 sp.global_attribute18,
538 sp.global_attribute19,
539 sp.global_attribute20,
540 sp.global_attribute_category,
541 sp.rule_set_id,
542 sp.code_combination_id_round,
543 sp.trx_header_level_rounding,
544 sp.trx_header_round_ccid,
545 sp.finchrg_receivables_trx_id,
546 sp.sales_tax_geocode,
547 sp.rev_transfer_clear_ccid,
548 sp.sales_credit_pct_limit,
549 sp.max_wrtoff_amount,
550 sp.irec_cc_receipt_method_id,
551 sp.show_billing_number_flag,
552 sp.cross_currency_rate_type,
553 sp.document_seq_gen_level,
554 'Y', --sp.calc_tax_on_credit_memo_flag,
555 sp.IREC_BA_RECEIPT_METHOD_ID,
556 tm_installed,
557 tm_default_setup,
558 sp.payment_threshold,
559 sp.standard_refund,
560 sp.credit_classification1,
561 sp.credit_classification2,
562 sp.credit_classification3,
563 sp.unmtch_claim_creation_flag,
564 sp.matched_claim_creation_flag,
565 sp.matched_claim_excl_cm_flag,
566 sp.min_wrtoff_amount,
567 sp.min_refund_amount
568 BULK COLLECT
569 INTO
570 p_globals.org_id_t,
571 p_globals.chart_of_accounts_id_t,
572 p_globals.set_of_books_id_t,
573 p_globals.set_of_books_name_t,
574 p_globals.currency_code_t,
575 p_globals.accounting_method_t,
576 p_globals.accrue_interest_t,
577 p_globals.unearned_discount_t,
578 p_globals.partial_discount_flag_t,
579 p_globals.print_remit_to_t,
580 p_globals.default_cb_due_date_t,
581 p_globals.auto_site_numbering_t,
582 p_globals.cash_basis_set_of_books_id_t,
586 p_globals.cer_split_amount_t,
583 p_globals.code_combination_id_gain_t,
584 p_globals.autocash_hierarchy_id_t,
585 p_globals.run_gl_journal_import_flag_t,
587 p_globals.cer_dso_days_t,
588 p_globals.posting_days_per_cycle_t,
589 p_globals.address_validation_t,
590 p_globals.calc_discount_on_lines_flag_t,
591 p_globals.change_printed_invoice_flag_t,
592 p_globals.code_combination_id_loss_t,
593 p_globals.create_reciprocal_flag_t,
594 p_globals.default_country_t,
595 p_globals.default_territory_t,
596 p_globals.generate_customer_number_t,
597 p_globals.invoice_deletion_flag_t,
598 p_globals.location_structure_id_t,
599 p_globals.site_required_flag_t,
600 p_globals.tax_allow_compound_flag_t,
601 p_globals.tax_header_level_flag_t,
602 p_globals.tax_rounding_allow_override_t,
603 p_globals.tax_invoice_print_t,
604 p_globals.tax_method_t,
605 p_globals.tax_use_customer_exempt_flag_t,
606 p_globals.tax_use_cust_exc_rate_flag_t,
607 p_globals.tax_use_loc_exc_rate_flag_t,
608 p_globals.tax_use_product_exempt_flag_t,
609 p_globals.tax_use_prod_exc_rate_flag_t,
610 p_globals.tax_use_site_exc_rate_flag_t,
611 p_globals.ai_log_file_message_level_t,
612 p_globals.ai_max_memory_in_bytes_t,
613 p_globals.ai_acct_flex_key_left_prompt_t,
614 p_globals.ai_mtl_items_key_left_prompt_t,
615 p_globals.ai_territory_key_left_prompt_t,
616 p_globals.ai_purge_int_tables_flag_t,
617 p_globals.ai_activate_sql_trace_flag_t,
618 p_globals.default_grouping_rule_id_t,
619 p_globals.salesrep_required_flag_t,
620 p_globals.auto_rec_invoices_per_commit_t,
621 p_globals.auto_rec_receipts_per_commit_t,
622 p_globals.pay_unrelated_invoices_flag_t,
623 p_globals.print_home_country_flag_t,
624 p_globals.location_tax_account_t,
625 p_globals.from_postal_code_t,
626 p_globals.to_postal_code_t,
627 p_globals.tax_registration_number_t,
628 p_globals.populate_gl_segments_flag_t,
629 p_globals.unallocated_revenue_ccid_t,
630 p_globals.period_set_name_t,
631 p_globals.base_precision_t,
632 p_globals.base_extended_precision_t,
633 p_globals.base_min_accountable_unit_t,
634 p_globals.salescredit_name_t,
635 p_globals.yes_meaning_t,
636 p_globals.no_meaning_t,
637 p_globals.tax_exempt_flag_meaning_t,
638 p_globals.inclusive_tax_used_t,
639 p_globals.tax_enforce_account_flag_t,
640 p_globals.ta_installed_flag_t,
641 p_globals.br_enabled_flag_t,
642 p_globals.attribute_category_t,
643 p_globals.attribute1_t,
644 p_globals.attribute2_t,
645 p_globals.attribute3_t,
646 p_globals.attribute4_t,
647 p_globals.attribute5_t,
648 p_globals.attribute6_t,
649 p_globals.attribute7_t,
650 p_globals.attribute8_t,
651 p_globals.attribute9_t,
652 p_globals.attribute10_t,
653 p_globals.attribute11_t,
654 p_globals.attribute12_t,
655 p_globals.attribute13_t,
656 p_globals.attribute14_t,
657 p_globals.attribute15_t,
658 p_globals.created_by_t,
659 p_globals.creation_date_t,
660 p_globals.last_updated_by_t,
661 p_globals.last_update_date_t,
662 p_globals.last_update_login_t,
663 p_globals.tax_code_t,
664 p_globals.tax_currency_code_t,
665 p_globals.tax_minimum_accountable_unit_t,
666 p_globals.tax_precision_t,
667 p_globals.tax_rounding_rule_t,
668 p_globals.tax_use_acc_exc_rate_flag_t,
669 p_globals.tax_use_system_exc_rate_flag_t,
670 p_globals.tax_hier_site_exc_rate_t,
671 p_globals.tax_hier_cust_exc_rate_t,
672 p_globals.tax_hier_prod_exc_rate_t,
673 p_globals.tax_hier_account_exc_rate_t,
674 p_globals.tax_hier_system_exc_rate_t,
675 p_globals.tax_database_view_set_t,
676 p_globals.global_attribute1_t,
677 p_globals.global_attribute2_t,
681 p_globals.global_attribute6_t,
678 p_globals.global_attribute3_t,
679 p_globals.global_attribute4_t,
680 p_globals.global_attribute5_t,
682 p_globals.global_attribute7_t,
683 p_globals.global_attribute8_t,
684 p_globals.global_attribute9_t,
685 p_globals.global_attribute10_t,
686 p_globals.global_attribute11_t,
687 p_globals.global_attribute12_t,
688 p_globals.global_attribute13_t,
689 p_globals.global_attribute14_t,
690 p_globals.global_attribute15_t,
691 p_globals.global_attribute16_t,
692 p_globals.global_attribute17_t,
693 p_globals.global_attribute18_t,
694 p_globals.global_attribute19_t,
695 p_globals.global_attribute20_t,
696 p_globals.global_attribute_category_t,
697 p_globals.rule_set_id_t,
698 p_globals.code_combination_id_round_t,
699 p_globals.trx_header_level_rounding_t,
700 p_globals.trx_header_round_ccid_t,
701 p_globals.finchrg_receivables_trx_id_t,
702 p_globals.sales_tax_geocode_t,
703 p_globals.rev_transfer_clear_ccid_t,
704 p_globals.sales_credit_pct_limit_t,
705 p_globals.max_wrtoff_amount_t,
706 p_globals.irec_cc_receipt_method_id_t,
707 p_globals.show_billing_number_flag_t,
708 p_globals.cross_currency_rate_type_t,
709 p_globals.document_seq_gen_level_t,
710 p_globals.calc_tax_on_credit_memo_flag_t,
711 p_globals.irec_ba_receipt_method_id_t,
712 p_globals.tm_installed_flag_t,
713 p_globals.tm_default_setup_flag_t,
714 p_globals.payment_threshold_t,
715 p_globals.standard_refund_t,
716 p_globals.credit_classification1_t,
717 p_globals.credit_classification2_t,
718 p_globals.credit_classification3_t,
719 p_globals.unmtch_claim_creation_flag_t,
720 p_globals.matched_claim_creation_flag_t,
721 p_globals.matched_claim_excl_cm_flag_t,
722 p_globals.min_wrtoff_amount_t,
723 p_globals.min_refund_amount_t
724 from
725 gl_sets_of_books sob,
726 fnd_currencies fc,
727 jtf_rs_salesreps rs,
728 ar_system_parameters sp,
729 zx_product_options zxpo
730 where sp.set_of_books_id = sob.set_of_books_id
731 and sob.currency_code = fc.currency_code
732 and nvl(sp.org_id,-99) = nvl(rs.org_id,-99)
733 and sp.org_id = nvl(p_org_id, sp.org_id) /* Bug 5051539 */
734 and rs.salesrep_id = -3
735 and zxpo.application_id (+) = 222
736 and zxpo.org_id (+) = sp.org_id;
737 end if;
738
739 IF PG_DEBUG in ('Y', 'C') THEN
740 arp_util.debug('ar_mo_cache_utils.retrieve_globals()-');
741 END IF;
742
743 EXCEPTION
744 when no_data_found then
745
746 /* ---------------------------------------------
747 Check row exists in ar_system_parameter
748 ---------------------------------------------- */
749 l_exp_flag := 'N';
750
751 FOR REC in c_exception(p_org_id) LOOP
752
753 l_exp_flag := 'Y';
754
755 /* ---------------------------------------------
756 Check row exists in gl sets of books
757 ---------------------------------------------- */
758 begin
759 SELECT 'x' into l_sob_test
760 from ar_system_parameters,
761 gl_sets_of_books sob
762 where sob.set_of_books_id = REC.set_of_books_id
763 and org_id = REC.org_id;
764 exception when no_data_found then
765 IF PG_DEBUG in ('Y', 'C') THEN
766 arp_util.debug('EXCEPTION: NO_DATA_FOUND IN SET OF BOOKS - ar_mo_cache_utils.retrieve_globals' );
767 END IF;
768 FND_MESSAGE.set_name('AR','AR_NO_ROW_IN_GL_SET_OF_BOOKS');
769 APP_EXCEPTION.raise_exception;
770 RAISE;
771 end;
772 /* ---------------------------------------------
773 Check row exists in fnd_currencies
774 ---------------------------------------------- */
775 begin
776 SELECT 'x' into l_sob_test
777 FROM ar_system_parameters sp, gl_sets_of_books sob, fnd_currencies c
778 WHERE sob.set_of_books_id = sp.set_of_books_id
779 and sp.org_id = REC.org_id
780 and sob.currency_code = c.currency_code;
781
782 exception when no_data_found then
783 IF PG_DEBUG in ('Y', 'C') THEN
784 arp_util.debug('EXCEPTION: NO_DATA_FOUND IN CURRENCIES - ar_mo_cache_utils.retrieve_globals' );
785 END IF;
786 FND_MESSAGE.set_name('AR','AR_NO_ROW_IN_FND_CURRENCIES');
787 APP_EXCEPTION.raise_exception;
788 RAISE; --end of WHEN NO DATA FOUND
789 end;
790
791 /* ---------------------------------------------------
792 Check if row exists in zx_product_options
793 --------------------------------------------------*/
797
794 /* No longer require to check ZX_PRODUCT_OPTIONS in R12 */
795
796 END LOOP;
798 IF l_exp_flag ='N' THEN
799 IF PG_DEBUG in ('Y', 'C') THEN
800 arp_util.debug('retrieve_globals: ' || 'EXCEPTION: NO_DATA_FOUND IN SYSTEM PARAMETERS
801 - ar_mo_cache_utils.retrieve_globals' );
802 END IF;
803 FND_MESSAGE.set_name('AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
804 APP_EXCEPTION.raise_exception;
805 RAISE;
806 END IF;
807
808
809 END retrieve_globals;
810
811 PROCEDURE set_org_context_in_api(p_org_id IN OUT NOCOPY NUMBER,
812 p_return_status OUT NOCOPY VARCHAR2)
813 AS
814 l_curr_org_id number;
815 l_status VARCHAR2(1);
816 l_default_org_id number;
817 BEGIN
818 p_return_status := FND_API.G_RET_STS_SUCCESS;
819
820 l_default_org_id := MO_UTILS.Get_Default_Org_ID;
821 l_curr_org_id := mo_global.get_current_org_id;
822
823 IF (p_org_id is null or
824 p_org_id = FND_API.G_MISS_NUM) THEN
825 If l_curr_org_id is not null then
826 p_org_id := l_curr_org_id;
827 else
828 p_org_id := l_default_org_id;
829 end if;
830 END IF;
831
832 l_status := MO_GLOBAL.check_valid_org(p_org_id);
833
834 IF l_Status = 'N' THEN
835 p_return_status := FND_API.G_RET_STS_ERROR;
836 ELSE
837 mo_global.set_policy_context('S',p_org_id);
838 /*-------------------------------------------------+
839 | Initialize SOB/org dependent variables |
840 +-------------------------------------------------*/
841 arp_global.init_global(p_org_id);
842 arp_standard.init_standard(p_org_id);
843 END IF;
844
845 EXCEPTION
846 WHEN others THEN Raise;
847
848 END set_org_context_in_api;
849
850 END ar_mo_cache_utils;