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