[Home] [Help]
PACKAGE BODY: APPS.AP_MO_CACHE_UTILS
Source
4 --
1 PACKAGE BODY ap_mo_cache_utils AS
2 /* $Header: apmocshb.pls 120.10.12010000.4 2009/01/09 15:04:20 kpasikan ship $ */
3
5 -- This procedure retrieves operating unit attributes from the
6 -- database and stores them into the specified data structure.
7 --
8 PROCEDURE retrieve_globals(p_globals OUT NOCOPY GlobalsTable )
9 IS
10 current_calling_sequence VARCHAR2(2000);
11 debug_info VARCHAR2(100);
12 i PLS_INTEGER;
13 l_default_org_id NUMBER;
14 l_default_ou_name VARCHAR2(240);
15 l_ou_count NUMBER;
16 BEGIN
17 -- Update the calling sequence
18 --
19 current_calling_sequence :=
20 'AP_MO_CACHE_UTILS.Retrieves_Globals';
21
22
23
24 --
25 -- This statement fetches operating unit attributes from the
26 -- database and stores them into nested tables using BULK
27 -- COLLECT
28 --
29 SELECT asp.org_id,
30 gl.chart_of_accounts_id,
31 gl.set_of_books_id,
32 gl.name,
33 gl.short_name,
34 DECODE(gl.enable_budgetary_control_flag,'Y','Y','N'),
35 fnd.currency_code,
36 asp.base_currency_code,
37 DECODE (asp.multi_currency_flag, 'N', 'N', 'Y'),
38 asp.payment_currency_code,
39 DECODE (asp.confirm_date_as_inv_num_flag, 'Y', 'Y', 'N'),
40 asp.accts_pay_code_combination_id,
41 asp.allow_flex_override_flag,
42 asp.allow_final_match_flag,
43 asp.allow_dist_match_flag,
44 asp.gl_date_from_receipt_flag,
45 asp.income_tax_region_flag,
46 asp.income_tax_region,
47 asp.auto_create_freight_flag,
48 asp.freight_code_combination_id,
49 asp.disc_is_inv_less_tax_flag,
50 asp.discount_distribution_method,
51 asp.inv_doc_category_override,
52 nvl(asp.approvals_option,'BATCH'),
53 asp.combined_filing_flag,
54 nvl(asp.allow_awt_flag,'N'),
55 asp.create_awt_dists_type,
56 asp.allow_awt_override,
57 asp.awt_include_tax_amt,
58 nvl(asp.awt_include_discount_amt, 'N'),
59 asp.allow_paid_invoice_adjust,
60 asp.add_days_settlement_date,
61 asp.prepayment_terms_id,
62 apt.name,
63 asp.prepay_code_combination_id,
64 asp.future_dated_pmt_acct_source,
65 asp.calc_user_xrate,
66 asp.make_rate_mandatory_flag,
67 asp.default_exchange_rate_type,
68 asp.post_dated_payments_flag,
69 asp.update_pay_site_flag,
70 asp.online_print_flag,
71 asp.replace_check_flag,
72 asp.auto_calculate_interest_flag,
73 asp.interest_tolerance_amount,
74 asp.interest_accts_pay_ccid,
75 nvl(asp.use_bank_charge_flag, 'N'), /* bug 5007989 */
76 nvl(asp.allow_supplier_bank_override, 'N'),
77 nvl(asp.pay_doc_category_override, 'N'),
78 asp.days_between_check_cycles,
79 asp.approval_workflow_flag,
80 asp.allow_force_approval_flag,
81 asp.validate_before_approval_flag,
82 asp.global_attribute_category,
83 asp.global_attribute1,
84 asp.global_attribute2,
85 asp.global_attribute3,
86 asp.global_attribute4,
87 asp.global_attribute5,
88 asp.global_attribute6,
89 asp.global_attribute7,
90 asp.global_attribute8,
91 asp.global_attribute9,
92 asp.global_attribute10,
93 asp.global_attribute11,
94 asp.global_attribute12,
95 asp.global_attribute13,
96 asp.global_attribute14,
97 asp.global_attribute15,
98 asp.global_attribute16,
99 asp.global_attribute17,
100 asp.global_attribute18,
101 asp.global_attribute19,
102 asp.global_attribute20,
103 fsp.purch_encumbrance_flag,
107 aba.bank_account_name,
104 fsp.inventory_organization_id,
105 fsp.vat_country_code,
106 asp.ce_bank_acct_use_id,
108 aba.zero_amount_allowed,
109 aba.max_outlay,
110 aba.max_check_amount,
111 aba.min_check_amount,
112 aba.currency_code,
113 aba.multi_currency_allowed_flag,
114 gdct.user_conversion_type,
115 asp.auto_calculate_interest_flag,
116 asp.approval_timing, --Bug4299234
117 fsp.misc_charge_ccid, --bug4936051
118 --Third Party Payments
119 asp.allow_inv_third_party_ovrd,
120 asp.allow_pymt_third_party_ovrd
121 BULK COLLECT
122 INTO p_globals.org_id_t,
123 p_globals.chart_of_accounts_id_t,
124 p_globals.set_of_books_id_t,
125 p_globals.set_of_books_name_t,
126 p_globals.set_of_books_short_name_t,
127 p_globals.enable_budget_control_flag_t,
128 p_globals.currency_code_t,
129 p_globals.sp_base_currency_code_t,
130 p_globals.sp_multi_currency_flag_t,
131 p_globals.sp_payment_currency_code_t,
132 p_globals.sp_confirm_date_inv_num_flag_t,
133 p_globals.sp_accts_pay_cc_id_t,
134 p_globals.sp_allow_flex_override_flag_t,
135 p_globals.sp_allow_final_match_flag_t,
136 p_globals.sp_allow_dist_match_flag_t,
137 p_globals.sp_gl_date_from_receipt_flag_t,
138 p_globals.sp_income_tax_region_flag_t,
139 p_globals.sp_income_tax_region_t,
140 p_globals.sp_auto_create_freight_flag_t,
141 p_globals.sp_default_freight_cc_id_t,
142 p_globals.sp_disc_is_inv_less_tax_flag_t,
143 p_globals.sp_discount_dist_method_t,
144 p_globals.sp_inv_doc_category_override_t,
145 p_globals.sp_approvals_option_t,
146 p_globals.sp_combined_filing_flag_t,
147 p_globals.sp_allow_awt_flag_t,
148 p_globals.sp_create_awt_dists_type_t,
149 p_globals.sp_allow_awt_override_t,
150 p_globals.sp_awt_include_tax_amt_t,
151 p_globals.sp_awt_include_discount_amt_t,
152 p_globals.sp_allow_paid_invoice_adjust_t,
153 p_globals.sp_add_days_settlement_date_t,
154 p_globals.sp_prepayment_terms_id_t,
155 p_globals.sp_ap_prepayment_term_name_t,
156 p_globals.sp_prepay_cc_id_t,
157 p_globals.sp_future_dated_pmt_acct_s_t,
158 p_globals.sp_calc_user_xrate_t,
159 p_globals.sp_make_rate_mandatory_flag_t,
160 p_globals.sp_def_exchange_rate_type_t,
161 p_globals.sp_post_dated_payments_flag_t,
162 p_globals.sp_update_pay_site_flag_t,
163 p_globals.sp_online_print_flag_t,
164 p_globals.sp_replace_check_flag_t,
165 p_globals.sp_auto_calc_interest_flag_t,
166 p_globals.sp_interest_tolerance_amount_t,
167 p_globals.sp_interest_accts_pay_ccid_t,
168 p_globals.sp_use_bank_charge_flag_t, /* bug 5007989 */
169 p_globals.sp_allow_supp_bank_override_t,
170 p_globals.sp_pay_doc_category_override_t,
171 p_globals.sp_days_between_check_cycles_t,
172 p_globals.sp_approval_workflow_flag_t,
173 p_globals.sp_allow_force_approval_flag_t,
174 p_globals.sp_validate_before_approval_t,
175 p_globals.sp_global_attribute_category_t,
176 p_globals.sp_global_attribute1_t,
177 p_globals.sp_global_attribute2_t,
178 p_globals.sp_global_attribute3_t,
179 p_globals.sp_global_attribute4_t,
180 p_globals.sp_global_attribute5_t,
181 p_globals.sp_global_attribute6_t,
182 p_globals.sp_global_attribute7_t,
186 p_globals.sp_global_attribute11_t,
183 p_globals.sp_global_attribute8_t,
184 p_globals.sp_global_attribute9_t,
185 p_globals.sp_global_attribute10_t,
187 p_globals.sp_global_attribute12_t,
188 p_globals.sp_global_attribute13_t,
189 p_globals.sp_global_attribute14_t,
190 p_globals.sp_global_attribute15_t,
191 p_globals.sp_global_attribute16_t,
192 p_globals.sp_global_attribute17_t,
193 p_globals.sp_global_attribute18_t,
194 p_globals.sp_global_attribute19_t,
195 p_globals.sp_global_attribute20_t,
196 p_globals.fsp_purch_encumbrance_flag_t,
197 p_globals.fsp_inventory_org_id_t,
198 p_globals.fsp_vat_country_code_t,
199 p_globals.sp_aba_bank_account_id_t,
200 p_globals.sp_aba_bank_account_name_t,
201 p_globals.sp_aba_zero_amounts_allowed_t,
202 p_globals.sp_aba_max_outlay_t,
203 p_globals.sp_aba_max_check_amount_t,
204 p_globals.sp_aba_min_check_amount_t,
205 p_globals.sp_aba_currency_code_t,
206 p_globals.sp_aba_multi_currency_flag_t,
207 p_globals.sp_gdct_user_conversion_type_t,
208 p_globals.sp_allow_interest_invoices_t,
209 p_globals.sp_approval_timing_t, --Bug4299234
210 p_globals.fsp_misc_charge_ccid_t, --Bug4936051
211 --Third Party Payments
212 p_globals.sp_allow_inv_thrd_prty_ovrd_t,
213 p_globals.sp_allow_pymt_thrd_prty_ovrd_t
214 FROM gl_sets_of_books gl,
215 fnd_currencies fnd,
216 ap_system_parameters asp,
217 ce_bank_accounts aba,
218 ce_bank_acct_uses_all cbau,
219 gl_daily_conversion_types gdct,
220 financials_system_parameters fsp,
221 ap_terms apt
222 WHERE gl.set_of_books_id = asp.set_of_books_id
223 AND gl.currency_Code = fnd.currency_code
224 AND asp.org_id = fsp.org_id
225 AND asp.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
226 AND cbau.bank_account_id = aba.bank_account_id (+)
227 AND asp.default_exchange_rate_type = gdct.conversion_type(+)
228 AND sysdate < nvl(aba.end_date(+),sysdate) --7673935, reverting 6870310
229 -- AND asp.terms_id = apt.term_id(+);
230 -- Fix for bug 2416598 commented above line and wrote below one
231 AND asp.prepayment_terms_id = apt.term_id(+);
232
233
234 IF p_globals.org_id_t.COUNT > 0 THEN
235 FOR i IN 1..p_globals.org_id_t.LAST LOOP
236
237 IF gl_mc_inquiry_pkg.mrc_enabled(200,
238 p_globals.set_of_books_id_t(i),
239 p_globals.org_id_t(i)) THEN
240
241 p_globals.mrc_enabled_t(i) := 'Y';
242
243 ELSE
244
245 p_globals.mrc_enabled_t(i) := 'N';
246
247 END IF;
248
249 END LOOP;
250
251 END IF;
252
253
254 EXCEPTION
255 --
256 -- You should raise exception here instead of returning NULL if
257 -- caching is critical to your application. For example,
258 -- the system options setup may be incomplete or not done,
259 -- in which case, the user should close the transaction form,
260 -- complete the required setup first.
261 --
262 WHEN no_data_found THEN
263 Mo_Utils.Get_Default_Ou(p_default_org_id => l_default_org_id
264 ,p_default_ou_name => l_default_ou_name
265 ,p_ou_count => l_ou_count);
266 If (l_ou_count = 0) Then
267 FND_MESSAGE.set_name('SQLAP', 'MO_ORG_ACCESS_NO_DATA_FOUND');
268 APP_EXCEPTION.raise_exception;
269 Else
270 FND_MESSAGE.set_name('SQLAP', 'AP_OPTIONS_NOT_YET_DEFINED');
271 APP_EXCEPTION.raise_exception;
272 End If;
273 END retrieve_globals;
274 END ap_mo_cache_utils;