[Home] [Help]
PACKAGE BODY: APPS.GL_LEDGERS_PKG
Source
1 PACKAGE BODY GL_LEDGERS_PKG AS
2 /* $Header: glistldb.pls 120.99.12010000.3 2008/11/27 06:43:34 phmullap ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'GL_LEDGERS_PKG';
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8 FUNCTION complete_config(
9 x_config_id NUMBER)
10 RETURN VARCHAR2 IS
11 CURSOR c_ledgers IS
12 SELECT DISTINCT ledger.chart_of_accounts_id -- glcd.object_id,
13 FROM gl_ledger_config_details glcd, gl_ledgers ledger
14 WHERE glcd.configuration_id = x_config_id
15 AND glcd.setup_step_code = 'NONE'
16 AND ledger.ledger_id = glcd.object_id;
17
18 CURSOR c_all_ledgers IS
19 SELECT ledger_id,ledger_category_code
20 FROM gl_ledgers
21 WHERE configuration_id = x_config_id;
22
23 CURSOR c_org_curr (p_ledger_id IN NUMBER) IS
24 SELECT haou.organization_id
25 , haou.date_from
26 ,haou.name
27 ,haou.type
28 ,haou.internal_external_flag
29 ,haou.location_id
30 ,hou.set_of_books_id
31 ,null usable_flag
32 ,hou.short_code
33 ,hou.default_legal_context_id
34 ,haou.object_version_number
35 FROM hr_operating_units hou,
36 hr_all_organization_units haou
37 WHERE set_of_books_id = p_ledger_id
38 AND haou.organization_id = hou.organization_id;
39
40
41 request_id NUMBER := NULL;
42 return_ids VARCHAR2(2000) := NULL;
43 x_access_set_id NUMBER(15);
44 v_security_segment_code VARCHAR2(1);
45 v_secured_seg_value_set_id NUMBER(15);
46 x_ledger_id NUMBER(15);
47 x_name VARCHAR2(30);
48 x_chart_of_accounts_id NUMBER(15, 0);
49 x_period_set_name VARCHAR2(15);
50 x_accounted_period_type VARCHAR2(15);
51 x_description VARCHAR2(240);
52 x_update_prim_ledger_warning BOOLEAN;
53 x_duplicate_org_warning BOOLEAN;
54 pri_ledger_id NUMBER(15) := 0;
55
56 BEGIN
57
58 FOR v_ledgers IN c_all_ledgers LOOP
59
60 IF v_ledgers.ledger_category_code = 'PRIMARY' THEN
61 pri_ledger_id := v_ledgers.ledger_id;
62 END IF;
63
64 SELECT ledger_id, NAME, chart_of_accounts_id, description,
65 period_set_name, accounted_period_type,
66 implicit_access_set_id
67 INTO x_ledger_id, x_name, x_chart_of_accounts_id, x_description,
68 x_period_set_name, x_accounted_period_type,
69 x_access_set_id
70 FROM gl_ledgers
71 WHERE ledger_id = v_ledgers.ledger_id;
72
73 IF (x_access_set_id IS NULL) THEN
74 v_security_segment_code := 'F';
75 v_secured_seg_value_set_id := NULL;
76
77 BEGIN
78 x_access_set_id :=
79 gl_access_sets_pkg.create_implicit_access_set
80 (x_name => x_name,
81 x_security_segment_code => v_security_segment_code,
82 x_chart_of_accounts_id => x_chart_of_accounts_id,
83 x_period_set_name => x_period_set_name,
84 x_accounted_period_type => x_accounted_period_type,
85 x_secured_seg_value_set_id => v_secured_seg_value_set_id,
86 x_default_ledger_id => x_ledger_id,
87 x_last_updated_by => fnd_global.user_id,
88 x_last_update_login => fnd_global.login_id,
89 x_creation_date => SYSDATE,
90 x_description => x_description);
91 EXCEPTION
92 WHEN OTHERS THEN
93 NULL;
94 END;
95
96 IF x_access_set_id IS NOT NULL THEN
97 UPDATE gl_ledgers
98 SET implicit_access_set_id = x_access_set_id
99 WHERE ledger_id = x_ledger_id;
100 END IF;
101 END IF;
102 END LOOP;
103
104 /*
105 * Update Operating unit's descriptive flexfiled org_information6 so
106 * that operating unit can be selected for entering transaction against it
107 */
108
109 for v_orgs in c_org_curr (pri_ledger_id)
110 loop
111 hr_organization_api.update_operating_unit
112 (
113 p_organization_id => v_orgs.organization_id
114 ,p_effective_date => v_orgs.date_from
115 ,p_usable_flag => v_orgs.usable_flag
116 ,p_object_version_number => v_orgs.object_version_number
117 ,p_update_prim_ledger_warning => x_update_prim_ledger_warning
118 ,p_duplicate_org_warning => x_duplicate_org_warning
119 );
120 end loop;
121
122
123 FOR v_ledgers IN c_ledgers LOOP
124 request_id :=
125 fnd_request.submit_request('SQLGL', 'GLSTFL', '', '', FALSE,
126 'VH', to_char(v_ledgers.chart_of_accounts_id),
127 'N', CHR(0), '', '', '', '', '', '',
128 '', '', '', '', '', '', '', '', '',
129 '', '', '', '', '', '', '', '', '',
130 '', '', '', '', '', '', '', '', '',
131 '', '', '', '', '', '', '', '', '',
132 '', '', '', '', '', '', '', '', '',
133 '', '', '', '', '', '', '', '', '',
134 '', '', '', '', '', '', '', '', '',
135 '', '', '', '', '', '', '', '', '',
136 '', '', '', '', '', '', '', '', '',
137 '', '', '', '', '', '', '', '', '');
138
139 IF (return_ids IS NULL) THEN
140 return_ids := TO_CHAR(request_id);
141 ELSE
142 return_ids := return_ids || ', ' || TO_CHAR(request_id);
143 END IF;
144 END LOOP;
145
146 RETURN(return_ids);
147 END;
148
149 -- *********************************************************************
150 -- This insert_row is used by the OA Framework Ledger page
151 PROCEDURE insert_row(
152 p_api_version IN NUMBER := 1.0,
153 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
154 p_commit IN VARCHAR2 := fnd_api.g_false,
155 p_validate_only IN VARCHAR2 := fnd_api.g_true,
156 p_record_version_number IN NUMBER := NULL,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 x_rowid IN OUT NOCOPY VARCHAR2,
161 x_ledger_id NUMBER,
162 x_name VARCHAR2,
163 x_short_name VARCHAR2,
164 x_chart_of_accounts_id NUMBER,
165 x_currency_code VARCHAR2,
166 x_period_set_name VARCHAR2,
167 x_accounted_period_type VARCHAR2,
168 x_first_ledger_period_name VARCHAR2,
169 x_ret_earn_code_combination_id NUMBER,
170 x_suspense_allowed_flag VARCHAR2,
171 x_suspense_ccid NUMBER,
172 x_allow_intercompany_post_flag VARCHAR2,
173 x_enable_avgbal_flag VARCHAR2,
174 x_enable_budgetary_control_f VARCHAR2,
175 x_require_budget_journals_flag VARCHAR2,
176 x_enable_je_approval_flag VARCHAR2,
177 x_enable_automatic_tax_flag VARCHAR2,
178 x_consolidation_ledger_flag VARCHAR2,
179 x_translate_eod_flag VARCHAR2,
180 x_translate_qatd_flag VARCHAR2,
181 x_translate_yatd_flag VARCHAR2,
182 x_automatically_created_flag VARCHAR2,
183 x_track_rounding_imbalance_f VARCHAR2,
184 --x_mrc_ledger_type_code VARCHAR2,
185 x_le_ledger_type_code VARCHAR2,
186 x_bal_seg_value_option_code VARCHAR2,
187 x_mgt_seg_value_option_code VARCHAR2,
188 x_last_update_date DATE,
189 x_last_updated_by NUMBER,
190 x_creation_date DATE,
191 x_created_by NUMBER,
192 x_last_update_login NUMBER,
193 x_description VARCHAR2,
194 x_future_enterable_periods_lmt NUMBER,
195 x_latest_opened_period_name VARCHAR2,
196 x_latest_encumbrance_year NUMBER,
197 x_cum_trans_ccid NUMBER,
198 x_res_encumb_ccid NUMBER,
199 x_net_income_ccid NUMBER,
200 x_balancing_segment VARCHAR2,
201 x_rounding_ccid NUMBER,
202 x_transaction_calendar_id NUMBER,
203 x_daily_translation_rate_type VARCHAR2,
204 x_period_average_rate_type VARCHAR2,
205 x_period_end_rate_type VARCHAR2,
206 x_context VARCHAR2,
207 x_attribute1 VARCHAR2,
208 x_attribute2 VARCHAR2,
209 x_attribute3 VARCHAR2,
210 x_attribute4 VARCHAR2,
211 x_attribute5 VARCHAR2,
212 x_attribute6 VARCHAR2,
213 x_attribute7 VARCHAR2,
214 x_attribute8 VARCHAR2,
215 x_attribute9 VARCHAR2,
216 x_attribute10 VARCHAR2,
217 x_attribute11 VARCHAR2,
218 x_attribute12 VARCHAR2,
219 x_attribute13 VARCHAR2,
220 x_attribute14 VARCHAR2,
221 x_attribute15 VARCHAR2,
222 x_set_manual_flag VARCHAR2,
223 --x_child_ledger_access_code VARCHAR2,
224 x_ledger_category_code VARCHAR2,
225 x_configuration_id NUMBER,
226 x_sla_accounting_method_code VARCHAR2,
227 x_sla_accounting_method_type VARCHAR2,
228 x_sla_description_language VARCHAR2,
229 x_sla_entered_cur_bal_sus_ccid NUMBER,
230 x_sla_bal_by_ledger_curr_flag VARCHAR2,
231 x_sla_ledger_cur_bal_sus_ccid NUMBER,
232 x_alc_ledger_type_code VARCHAR2,
233 x_criteria_set_id NUMBER,
234 x_enable_secondary_track_flag VARCHAR2 DEFAULT 'N',
235 x_enable_reval_ss_track_flag VARCHAR2 DEFAULT 'N',
236 x_enable_reconciliation_flag VARCHAR2 DEFAULT 'N',
237 x_sla_ledger_cash_basis_flag VARCHAR2 DEFAULT 'N',
238 x_create_je_flag VARCHAR2 DEFAULT 'Y',
239 x_commitment_budget_flag VARCHAR2 DEFAULT NULL) IS
240 CURSOR c IS
241 SELECT ROWID
242 FROM gl_ledgers
243 WHERE NAME = x_name;
244
245 l_api_version NUMBER := p_api_version;
246 l_api_name CONSTANT VARCHAR(30) := 'insert_row';
247 --x_access_set_id NUMBER(15);
248 --v_security_segment_code VARCHAR2(1);
249 --v_secured_seg_value_set_id NUMBER(15);
250 x_bal_seg_column_name VARCHAR2(25);
251 x_bal_seg_value_set_id NUMBER(10, 0);
252 x_mgt_seg_column_name VARCHAR2(25);
253 x_mgt_seg_value_set_id NUMBER(10, 0);
254 x_period_type VARCHAR2(15);
255 t_period_average_rate_type VARCHAR2(30);
256 t_period_end_rate_type VARCHAR2(30);
257 t_sla_description_language VARCHAR2(15);
258 t_criteria_set_id NUMBER(15, 0);
259 x_suspense_ccid_temp NUMBER;
260 v_CursorID INTEGER;
261 v_Dummy INTEGER;
262 v_CursorSQL VARCHAR2(300);
263 v_balancing_segment VARCHAR2(30);
264 temp NUMBER;
265 v_first_ledger_period_name VARCHAR2(15);
266 period_counter NUMBER := 1;
267 l_complete_flag VARCHAR2(1);
268 l_status_code VARCHAR2(30);
269 CURSOR first_ledger_period IS
270 select period_name
271 from gl_periods
272 where period_set_name = x_period_set_name
273 and period_type = x_accounted_period_type
274 order by period_year, period_num;
275 CURSOR get_config_status IS
276 select completion_status_code
277 from gl_ledger_configurations
278 where configuration_id = x_configuration_id;
279 BEGIN
280 IF p_commit = fnd_api.g_true THEN
281 SAVEPOINT complete_workorder;
282 END IF;
283
284 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
285 l_api_name, g_pkg_name) THEN
286 RAISE fnd_api.g_exc_unexpected_error;
287 END IF;
288
289 IF fnd_api.to_boolean(p_init_msg_list) THEN
290 fnd_msg_pub.initialize;
291 END IF;
292
293 x_return_status := fnd_api.g_ret_sts_success;
294
295 -- Default the first period of the ledger to the second period in the calendar.
296 OPEN first_ledger_period;
297 LOOP
298 FETCH first_ledger_period INTO v_first_ledger_period_name;
299 IF (period_counter = 1 AND v_first_ledger_period_name IS NOT NULL) THEN
300 EXIT;
301 ELSIF(v_first_ledger_period_name IS NULL) THEN
302 fnd_message.set_name('SQLGL', 'GL_LEDGER_ENABLE_STS_FLAG');
303 app_exception.raise_exception;
304 END IF;
305 period_counter := period_counter + 1;
306 END LOOP;
307 CLOSE first_ledger_period;
308
309 -- Validate secondary tracking flag - Ledger API checks.
310 IF (x_enable_secondary_track_flag = 'Y') THEN
311 IF (FND_FLEX_APIS.get_qualifier_segnum(101,'GL#',x_chart_of_accounts_id,'GL_SECONDARY_TRACKING',temp) = FALSE) THEN
312 fnd_message.set_name('SQLGL', 'GL_API_LEDGER_CHK_SECD_SEG');
313 app_exception.raise_exception;
314 END IF;
315 IF(x_enable_avgbal_flag = 'Y') THEN
316 fnd_message.set_name('SQLGL', 'GL_LEDGER_ENABLE_STS_FLAG');
317 app_exception.raise_exception;
318 END IF;
319 END IF;
320
321 -- Validate secondary tracking for revaluation flag - Ledger API checks
322 IF (x_enable_reval_ss_track_flag = 'Y') THEN
323 IF (FND_FLEX_APIS.get_qualifier_segnum(101,'GL#',x_chart_of_accounts_id,'GL_SECONDARY_TRACKING',temp) = FALSE) THEN
324 fnd_message.set_name('SQLGL', 'GL_API_LEDGER_CHK_SECD_SEG');
325 app_exception.raise_exception;
326 END IF;
327 END IF;
328
329 -- Create an implicit access set header for the corresponding new created
330 -- ledger and retrieve its ID.
331 -- The security segment code must be 'M' for Management Ledgers and
332 -- 'F' for Legal and Upgrade Ledgers.
333 -- The secured segment value set id should be X_Mgt_Seg_Value_Set_Id for
334 -- Management Ledgers and null for Legal and Upgrade Ledgers.
335
336 /* Now the Implicit access sets are ledger Implicit sets only modified
337 Srini Pala*/
338 --IF (X_Le_Ledger_Type_Code = 'M') THEN
339 /*
340 v_security_segment_code := 'F';
341 v_secured_seg_value_set_id := NULL; -- X_Mgt_Seg_Value_Set_Id;
342 x_access_set_id :=
343 gl_access_sets_pkg.create_implicit_access_set(
344 x_name => x_name,
345 x_security_segment_code => v_security_segment_code,
346 x_chart_of_accounts_id => x_chart_of_accounts_id,
347 x_period_set_name => x_period_set_name,
348 x_accounted_period_type => x_accounted_period_type,
349 x_secured_seg_value_set_id => v_secured_seg_value_set_id,
350 x_default_ledger_id => x_ledger_id,
351 x_last_updated_by => x_last_updated_by,
352 x_last_update_login => x_last_update_login,
353 x_creation_date => x_creation_date,
354 x_description => x_description); */
355
356 -- get the balance segment infor and mgt segment infor
357 gl_ledgers_pkg.get_bal_mgt_seg_info(x_bal_seg_column_name,
358 x_bal_seg_value_set_id,
359 x_mgt_seg_column_name,
360 x_mgt_seg_value_set_id,
361 x_chart_of_accounts_id);
362
363 IF x_net_income_ccid IS NOT NULL THEN
364 BEGIN
365 v_CursorSQL :=
366 'SELECT ' || x_bal_seg_column_name
367 || ' FROM gl_code_combinations WHERE chart_of_accounts_id = :1 '
368 || ' AND code_combination_id = :2 ';
369 EXECUTE IMMEDIATE v_CursorSQL INTO v_balancing_segment
370 USING x_chart_of_accounts_id, x_net_income_ccid;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 v_balancing_segment := x_balancing_segment;
375 END;
376 ELSE
377 v_balancing_segment := x_balancing_segment;
378 END IF;
379
380 ----- temporary validation starts
381 IF x_period_average_rate_type IS NOT NULL THEN
382 BEGIN
383 SELECT conversion_type
384 INTO t_period_average_rate_type
385 FROM gl_daily_conversion_types_v
386 WHERE conversion_type <> 'User'
387 AND conversion_type <> 'EMU FIXED'
388 AND conversion_type = x_period_average_rate_type;
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 --fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,
392 -- 'Invalid period_average_rate_type');
393 fnd_message.set_name('SQLGL', 'GL_ASF_LGR_NEED_PAVE_RATETYPE');
394 fnd_msg_pub.ADD;
395 x_return_status := fnd_api.g_ret_sts_unexp_error;
396 END;
397 END IF;
398
399 IF x_period_end_rate_type IS NOT NULL THEN
400 BEGIN
401 SELECT conversion_type
402 INTO t_period_end_rate_type
403 FROM gl_daily_conversion_types_v
404 WHERE conversion_type <> 'User'
405 AND conversion_type <> 'EMU FIXED'
406 AND conversion_type = x_period_end_rate_type;
407 EXCEPTION
408 WHEN NO_DATA_FOUND THEN
409 fnd_message.set_name('SQLGL', 'GL_ASF_LGR_NEED_PEND_RATETYPE');
410 fnd_msg_pub.ADD;
411 x_return_status := fnd_api.g_ret_sts_unexp_error;
412 END;
413 END IF;
414
415 /* IF x_sla_accounting_method_code IS NOT NULL THEN
416 BEGIN
417 SELECT language_code
418 INTO t_sla_description_language
419 FROM fnd_languages_vl
420 WHERE (installed_flag = 'I' OR installed_flag = 'B')
421 AND language_code = x_sla_description_language;
422 EXCEPTION
423 WHEN NO_DATA_FOUND THEN
424 fnd_message.set_name('SQLGL', 'GL_ASF_LGR_NEED_JE_DESC');
425 fnd_msg_pub.ADD;
426 x_return_status := fnd_api.g_ret_sts_unexp_error;
427 END;
428 END IF;*/
429
430 IF x_criteria_set_id IS NOT NULL THEN
431 BEGIN
432 SELECT criteria_set_id
433 INTO t_criteria_set_id
434 FROM gl_autorev_criteria_sets
435 WHERE criteria_set_id = x_criteria_set_id;
436 EXCEPTION
437 WHEN NO_DATA_FOUND THEN
438 fnd_message.set_name('SQLGL',
439 'GL_ASF_LGR_JE_REVERSAL_INVALID');
440 fnd_msg_pub.ADD;
441 x_return_status := fnd_api.g_ret_sts_unexp_error;
442 END;
443 END IF;
444
445 IF(x_ledger_category_code = 'ALC') THEN
446 OPEN get_config_status;
447 FETCH get_config_status INTO l_status_code;
448 IF(get_config_status%FOUND AND l_status_code = 'CONFIRMED') THEN
449 l_complete_flag := 'Y';
450 ELSE
451 l_complete_flag := 'N';
452 END IF;
453 ELSE
454 l_complete_flag := 'N';
455 END IF;
456
457 ----- temporary validation ends
458 INSERT INTO gl_ledgers
459 (ledger_id, NAME, short_name, chart_of_accounts_id,
460 currency_code, period_set_name,
461 accounted_period_type, first_ledger_period_name,
462 ret_earn_code_combination_id, suspense_allowed_flag,
463 allow_intercompany_post_flag,
464 enable_average_balances_flag,
465 enable_budgetary_control_flag,
466 require_budget_journals_flag, enable_je_approval_flag,
467 enable_automatic_tax_flag, consolidation_ledger_flag,
468 translate_eod_flag, translate_qatd_flag,
469 translate_yatd_flag, automatically_created_flag,
470 track_rounding_imbalance_flag,-- mrc_ledger_type_code,
471 object_type_code, le_ledger_type_code,
472 bal_seg_value_option_code, bal_seg_column_name,
473 bal_seg_value_set_id, mgt_seg_value_option_code,
474 mgt_seg_column_name, mgt_seg_value_set_id,
475 implicit_access_set_id, last_update_date, last_updated_by,
476 creation_date, created_by, last_update_login,
477 description, future_enterable_periods_limit,
478 ledger_attributes, latest_opened_period_name,
479 latest_encumbrance_year, cum_trans_code_combination_id,
480 res_encumb_code_combination_id,
481 net_income_code_combination_id,
482 rounding_code_combination_id, transaction_calendar_id,
483 daily_translation_rate_type, period_average_rate_type,
484 period_end_rate_type, CONTEXT, attribute1,
485 attribute2, attribute3, attribute4, attribute5,
486 attribute6, attribute7, attribute8, attribute9,
487 attribute10, attribute11, attribute12,
488 attribute13, attribute14, attribute15,
489 --child_ledger_access_code,
490 ledger_category_code, configuration_id,
491 sla_accounting_method_code,
492 sla_accounting_method_type, sla_description_language,
493 sla_entered_cur_bal_sus_ccid,
494 sla_bal_by_ledger_curr_flag,
495 sla_ledger_cur_bal_sus_ccid, alc_ledger_type_code,
496 criteria_set_id,enable_secondary_track_flag,
497 enable_reval_ss_track_flag, enable_reconciliation_flag,
498 sla_ledger_cash_basis_flag, create_je_flag, complete_flag,commitment_budget_flag)
499 VALUES (x_ledger_id, x_name, x_short_name, x_chart_of_accounts_id,
500 x_currency_code, x_period_set_name,
501 x_accounted_period_type, decode(x_ledger_category_code,'ALC',
502 x_first_ledger_period_name,v_first_ledger_period_name),
503 x_ret_earn_code_combination_id, x_suspense_allowed_flag,
504 x_allow_intercompany_post_flag,
505 x_enable_avgbal_flag,
506 x_enable_budgetary_control_f,
507 x_require_budget_journals_flag, x_enable_je_approval_flag,
508 x_enable_automatic_tax_flag, x_consolidation_ledger_flag,
509 x_translate_eod_flag, x_translate_qatd_flag,
510 x_translate_yatd_flag, x_automatically_created_flag,
511 x_track_rounding_imbalance_f, --x_mrc_ledger_type_code,
512 'L', x_le_ledger_type_code,
513 x_bal_seg_value_option_code, x_bal_seg_column_name,
514 x_bal_seg_value_set_id, x_mgt_seg_value_option_code,
515 x_mgt_seg_column_name, x_mgt_seg_value_set_id,
516 NULL, x_last_update_date, x_last_updated_by,
517 x_creation_date, x_created_by, x_last_update_login,
518 x_description, x_future_enterable_periods_lmt,
519 'L',
520 -- 'Y' || fnd_global.newline || 'Y' || fnd_global.newline || 'L',
521 x_latest_opened_period_name,
522 x_latest_encumbrance_year, x_cum_trans_ccid,
523 x_res_encumb_ccid,
524 x_net_income_ccid,
525 x_rounding_ccid, x_transaction_calendar_id,
526 x_daily_translation_rate_type, x_period_average_rate_type,
527 x_period_end_rate_type, x_context, x_attribute1,
528 x_attribute2, x_attribute3, x_attribute4, x_attribute5,
529 x_attribute6, x_attribute7, x_attribute8, x_attribute9,
530 x_attribute10, x_attribute11, x_attribute12,
531 x_attribute13, x_attribute14, x_attribute15,
532 --x_child_ledger_access_code,
533 x_ledger_category_code, x_configuration_id,
534 x_sla_accounting_method_code,
535 x_sla_accounting_method_type, x_sla_description_language,
536 x_sla_entered_cur_bal_sus_ccid,
537 x_sla_bal_by_ledger_curr_flag,
538 x_sla_ledger_cur_bal_sus_ccid, x_alc_ledger_type_code,
539 x_criteria_set_id, x_enable_secondary_track_flag,
540 x_enable_reval_ss_track_flag, x_enable_reconciliation_flag,
541 x_sla_ledger_cash_basis_flag, x_create_je_flag, l_complete_flag,x_commitment_budget_flag);
542
543 OPEN c;
544
545 FETCH c
546 INTO x_rowid;
547
548 IF (c%NOTFOUND) THEN
549 CLOSE c;
550
551 --RAISE NO_DATA_FOUND;
552 --The following new style is used for transferring error message back to OA FWK page
553 fnd_msg_pub.ADD;
554 x_return_status := fnd_api.g_ret_sts_unexp_error;
555 END IF;
556
557 CLOSE c;
558
559 -- Insert rows into gl_concurrency_control table for the
560 -- corresponding new created ledger.
561 -- Should be GL_CONC_CONTROL_PKG.insert_conc_ledger(
562 gl_conc_control_pkg.insert_conc_ledger(x_ledger_id, x_last_update_date,
563 x_last_updated_by,
564 x_creation_date, x_created_by,
565 x_last_update_login);
566
567 -- Insert rows into gl_period_statuses table for the
568 -- corresponding new created ledger.
569 BEGIN
570 gl_period_statuses_pkg.insert_led_ps(x_ledger_id, x_period_set_name,
571 x_accounted_period_type,
572 x_last_update_date,
573 x_last_updated_by,
574 x_last_update_login,
575 x_creation_date, x_created_by);
576 EXCEPTION
577 WHEN OTHERS THEN
578 fnd_msg_pub.ADD;
579 x_return_status := fnd_api.g_ret_sts_unexp_error;
580 IF sqlerrm is not null
581 then
582 fnd_message.set_name('SQLGL', 'DB-ERROR');
583 fnd_message.set_token ('Message ', sqlerrm);
584 fnd_msg_pub.add;
585 END IF;
586 END;
587
588 -- Insert rows into gl_autoreverse_options table for the
589 -- new ledger
590 -- gl_autoreverse_options_pkg.insert_ledger_reversal_cat(
591 -- x_ledger_id, x_created_by, x_last_updated_by, x_last_update_login);
592 -- Need comments this out as Srini requested for Sep 30 code freeze
593
594 -- Insert rows into gl_suspense_accounts table for the
595 -- corresponding new created ledger.
596 BEGIN
597 SELECT code_combination_id
598 INTO x_suspense_ccid_temp
599 FROM gl_code_combinations
600 WHERE code_combination_id = x_suspense_ccid;
601 EXCEPTION
602 WHEN OTHERS THEN
603 x_suspense_ccid_temp := NULL;
604 END;
605
606 IF ( (x_suspense_allowed_flag = 'Y')
607 AND (x_suspense_ccid_temp IS NOT NULL)) THEN
608 -- bug fix 2826511
609 gl_suspense_accounts_pkg.insert_ledger_suspense
610 (x_ledger_id,
611 x_suspense_ccid_temp,
612 x_last_update_date,
613 x_last_updated_by);
614 END IF;
615
616 -- Insert rows into gl_ledger_legal_entities table for the
617 -- corresponding new created ledger.
618
619 -- Removed LOGIC, SAGAR TAROON KAMDAR
620
621 -- Check whether journal_approval_flag is to be set to Y for
622 -- the Manual source.
623 IF (x_set_manual_flag = 'Y') THEN
624 gl_ledgers_pkg.enable_manual_je_approval;
625 END IF;
626
627 IF (x_enable_avgbal_flag = 'Y') THEN
628 gl_ledgers_pkg.update_gl_system_usages(x_consolidation_ledger_flag);
629 gl_ledgers_pkg.insert_gl_net_income_accounts(x_ledger_id,
630 v_balancing_segment,
631
632 --x_balancing_segment,
633 x_net_income_ccid,
634 x_creation_date,
635 x_created_by,
636 x_last_update_date,
637 x_last_updated_by,
638 x_last_update_login,
639 '', '', '', '');
640 END IF;
641
642 x_msg_count := fnd_msg_pub.count_msg;
643 x_msg_data := fnd_message.get;
644
645 IF x_msg_count > 0 THEN
646 x_return_status := fnd_api.g_ret_sts_unexp_error;
647 RAISE fnd_api.g_exc_error;
648 END IF;
649 EXCEPTION
650 WHEN fnd_api.g_exc_unexpected_error THEN
651 IF p_commit = fnd_api.g_true THEN
652 ROLLBACK TO complete_workorder;
653 END IF;
654
655 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
656 p_procedure_name => l_api_name);
657 x_return_status := fnd_api.g_ret_sts_unexp_error;
658 WHEN fnd_api.g_exc_error THEN
659 IF p_commit = fnd_api.g_true THEN
660 ROLLBACK TO complete_workorder;
661 END IF;
662
663 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
664 p_procedure_name => l_api_name);
665 x_return_status := fnd_api.g_ret_sts_unexp_error;
666 WHEN OTHERS THEN
667 IF p_commit = fnd_api.g_true THEN
668 ROLLBACK TO complete_workorder;
669 END IF;
670
671 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
672 p_procedure_name => l_api_name);
673 x_return_status := fnd_api.g_ret_sts_unexp_error;
674 END insert_row;
675
676 -- **********************************************************************
677 -- This update_row is used by the OA Framework Ledger page
678 PROCEDURE update_row(
679 p_api_version IN NUMBER := 1.0,
680 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
681 p_commit IN VARCHAR2 := fnd_api.g_false,
682 p_validate_only IN VARCHAR2 := fnd_api.g_true,
683 p_record_version_number IN NUMBER := NULL,
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2,
687 x_rowid VARCHAR2,
688 x_ledger_id NUMBER,
689 x_name VARCHAR2,
690 x_short_name VARCHAR2,
691 x_chart_of_accounts_id NUMBER,
692 x_currency_code VARCHAR2,
693 x_period_set_name VARCHAR2,
694 x_accounted_period_type VARCHAR2,
695 x_first_ledger_period_name VARCHAR2,
696 x_ret_earn_code_combination_id NUMBER,
697 x_suspense_allowed_flag VARCHAR2,
698 x_suspense_ccid NUMBER,
699 x_allow_intercompany_post_flag VARCHAR2,
700 x_enable_avgbal_flag VARCHAR2,
701 x_enable_budgetary_control_f VARCHAR2,
702 x_require_budget_journals_flag VARCHAR2,
703 x_enable_je_approval_flag VARCHAR2,
704 x_enable_automatic_tax_flag VARCHAR2,
705 x_consolidation_ledger_flag VARCHAR2,
706 x_translate_eod_flag VARCHAR2,
707 x_translate_qatd_flag VARCHAR2,
708 x_translate_yatd_flag VARCHAR2,
709 x_automatically_created_flag VARCHAR2,
710 x_track_rounding_imbalance_f VARCHAR2,
711 --x_mrc_ledger_type_code VARCHAR2,
712 x_le_ledger_type_code VARCHAR2,
713 x_bal_seg_value_option_code VARCHAR2,
714 x_mgt_seg_value_option_code VARCHAR2,
715 x_implicit_access_set_id NUMBER,
716 x_last_update_date DATE,
717 x_last_updated_by NUMBER,
718 x_last_update_login NUMBER,
719 x_description VARCHAR2,
720 x_future_enterable_periods_lmt NUMBER,
721 x_latest_opened_period_name VARCHAR2,
722 x_latest_encumbrance_year NUMBER,
723 x_cum_trans_ccid NUMBER,
724 x_res_encumb_ccid NUMBER,
725 x_net_income_ccid NUMBER,
726 x_balancing_segment VARCHAR2,
727 x_rounding_ccid NUMBER,
728 x_transaction_calendar_id NUMBER,
729 x_daily_translation_rate_type VARCHAR2,
730 x_period_average_rate_type VARCHAR2,
731 x_period_end_rate_type VARCHAR2,
732 x_context VARCHAR2,
733 x_attribute1 VARCHAR2,
734 x_attribute2 VARCHAR2,
735 x_attribute3 VARCHAR2,
736 x_attribute4 VARCHAR2,
737 x_attribute5 VARCHAR2,
738 x_attribute6 VARCHAR2,
739 x_attribute7 VARCHAR2,
740 x_attribute8 VARCHAR2,
741 x_attribute9 VARCHAR2,
742 x_attribute10 VARCHAR2,
743 x_attribute11 VARCHAR2,
744 x_attribute12 VARCHAR2,
745 x_attribute13 VARCHAR2,
746 x_attribute14 VARCHAR2,
747 x_attribute15 VARCHAR2,
748 x_set_manual_flag VARCHAR2,
749 --x_child_ledger_access_code VARCHAR2,
750 x_ledger_category_code VARCHAR2,
751 x_configuration_id NUMBER,
752 x_sla_accounting_method_code VARCHAR2,
753 x_sla_accounting_method_type VARCHAR2,
754 x_sla_description_language VARCHAR2,
755 x_sla_entered_cur_bal_sus_ccid NUMBER,
756 x_sla_bal_by_ledger_curr_flag VARCHAR2,
757 x_sla_ledger_cur_bal_sus_ccid NUMBER,
758 x_alc_ledger_type_code VARCHAR2,
759 x_criteria_set_id NUMBER,
760 x_enable_secondary_track_flag VARCHAR2 DEFAULT 'N',
761 x_enable_reval_ss_track_flag VARCHAR2 DEFAULT 'N',
762 x_enable_reconciliation_flag VARCHAR2 DEFAULT 'N',
763 x_sla_ledger_cash_basis_flag VARCHAR2 DEFAULT 'N',
764 x_create_je_flag VARCHAR2 DEFAULT 'Y',
765 x_commitment_budget_flag VARCHAR2 DEFAULT NULL) IS
766 l_api_version NUMBER := p_api_version;
767 l_api_name CONSTANT VARCHAR(30) := 'update_row';
768 x_access_set_id NUMBER(15);
769 v_security_segment_code VARCHAR2(1);
770 v_secured_seg_value_set_id NUMBER(15);
771 x_bal_seg_column_name VARCHAR2(25);
772 x_bal_seg_value_set_id NUMBER(10, 0);
773 x_mgt_seg_column_name VARCHAR2(25);
774 x_mgt_seg_value_set_id NUMBER(10, 0);
775 x_period_type VARCHAR2(15);
776 x_current_bsv_option_code VARCHAR2(30);
777 x_current_sla_actg_method_code VARCHAR2(30);
778 x_current_sla_actg_method_type VARCHAR2(30);
779 x_current_name VARCHAR2(30);
780 x_current_short_name VARCHAR2(20);
781 x_current_allow_intercom_flag VARCHAR2(1);
782 x_cum_trans_ccid_temp NUMBER;
783 x_res_encumb_ccid_temp NUMBER;
784 x_net_income_ccid_temp NUMBER;
785 x_rounding_ccid_temp NUMBER;
786 x_suspense_ccid_temp NUMBER;
787 p_sla_accounting_method_code VARCHAR2(30) := NULL;
788 p_sla_accounting_method_type VARCHAR2(1) := NULL;
789 p_sla_description_language VARCHAR2(15) := NULL;
790 p_sla_entered_cur_bal_sus_ccid NUMBER(15, 0) := NULL;
791 p_sla_sequencing_flag VARCHAR2(1) := NULL;
792 p_sla_bal_by_ledger_curr_flag VARCHAR2(1) := NULL;
793 p_sla_ledger_cur_bal_sus_ccid NUMBER(15, 0) := NULL;
794 x_acctg_environment_code VARCHAR2(30);
795 t_period_average_rate_type VARCHAR2(30);
796 t_period_end_rate_type VARCHAR2(30);
797 t_sla_description_language VARCHAR2(15);
798 t_criteria_set_id NUMBER(15, 0);
799 v_balancing_segment VARCHAR2(30);
800 v_CursorID INTEGER;
801 v_Dummy INTEGER;
802 v_CursorSQL VARCHAR2(300);
803 x_completion_status VARCHAR2(30);
804
805 CURSOR c_primary_ledgers IS
806 SELECT DISTINCT object_id
807 FROM gl_ledger_config_details
808 WHERE object_type_code = 'PRIMARY'
809 AND setup_step_code = 'NONE'
810 AND configuration_id IN(
811 SELECT configuration_id
812 FROM gl_ledger_config_details
813 WHERE object_type_code = 'SECONDARY'
814 AND setup_step_code = 'NONE'
815 AND object_id = x_ledger_id);
816
817 -- Added by LPOON on 11/20/03: Used to loop for each ALC to default GL
818 -- suspense CCID from the source
819 -- Modified by LPOON on 11/21/03: Join with GL_LEDGERS table to make
820 -- sure the ALC ledger which has been created already
821 CURSOR c_alc_ledgers IS
822 SELECT DISTINCT rs.target_ledger_id
823 FROM gl_ledger_relationships rs, gl_ledgers lg
824 WHERE rs.source_ledger_id = x_ledger_id
825 AND rs.application_id = 101
826 AND rs.target_ledger_category_code = 'ALC'
827 AND rs.relationship_type_code in ('JOURNAL', 'SUBLEDGER')
828 AND lg.ledger_id = rs.target_ledger_id;
829 CURSOR c IS
830 SELECT *
831 FROM gl_ledgers
832 WHERE ledger_id = x_ledger_id;
833
834 recinfo c%ROWTYPE;
835 l_ret_changed VARCHAR2(1) :='N';
836 l_suspense_changed VARCHAR2(1) :='N';
837 l_intercom_changed VARCHAR2(1) :='N';
838 l_cta_changed VARCHAR2(1) :='N';
839 l_reserv_encum_changed VARCHAR2(1) :='N';
840 l_autotax_changed VARCHAR2(1) :='N';
841 l_trans_eod_changed VARCHAR2(1) :='N';
842 l_trans_qatd_changed VARCHAR2(1) :='N';
843 l_trans_yatd_changed VARCHAR2(1) :='N';
844 l_period_avg_rt_changed VARCHAR2(1) :='N';
845 l_period_end_rt_changed VARCHAR2(1) :='N';
846 temp NUMBER;
847 BEGIN
848 IF p_commit = fnd_api.g_true THEN
849 SAVEPOINT complete_workorder;
850 END IF;
851
852 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
853 l_api_name, g_pkg_name) THEN
854 RAISE fnd_api.g_exc_unexpected_error;
855 END IF;
856
857 IF fnd_api.to_boolean(p_init_msg_list) THEN
858 fnd_msg_pub.initialize;
859 END IF;
860
861 x_return_status := fnd_api.g_ret_sts_success;
862
863 -- Ledger API checks
864 OPEN c;
865 FETCH c
866 INTO recinfo;
867
868 IF (c%NOTFOUND) THEN
869 CLOSE c;
870
871 RAISE NO_DATA_FOUND;
872 END IF;
873
874 CLOSE c;
875
876 IF (recinfo.ret_earn_code_combination_id <> x_ret_earn_code_combination_id) THEN
877 l_ret_changed := 'Y';
878 END IF;
879 IF (recinfo.suspense_allowed_flag <> x_suspense_allowed_flag) THEN
880 l_suspense_changed := 'Y';
881 END IF;
882 IF (recinfo.allow_intercompany_post_flag <> x_allow_intercompany_post_flag) THEN
883 l_intercom_changed := 'Y';
884 END IF;
885 IF (recinfo.cum_trans_code_combination_id <> x_cum_trans_ccid) THEN
886 l_cta_changed := 'Y';
887 END IF;
888 IF (recinfo.res_encumb_code_combination_id <> x_res_encumb_ccid) THEN
889 l_reserv_encum_changed := 'Y';
890 END IF;
891 IF (recinfo.enable_automatic_tax_flag <> x_enable_automatic_tax_flag) THEN
892 l_autotax_changed := 'Y';
893 END IF;
894 IF (recinfo.translate_eod_flag <> x_translate_eod_flag) THEN
895 l_trans_eod_changed := 'Y';
896 END IF;
897 IF (recinfo.translate_qatd_flag <> x_translate_qatd_flag) THEN
898 l_trans_qatd_changed := 'Y';
899 END IF;
900 IF (recinfo.translate_yatd_flag <> x_translate_yatd_flag) THEN
901 l_trans_yatd_changed := 'Y';
902 END IF;
903 IF (recinfo.period_average_rate_type <> x_period_average_rate_type) THEN
904 l_period_avg_rt_changed := 'Y';
905 END IF;
906 IF (recinfo.period_end_rate_type <> x_period_end_rate_type) THEN
907 l_period_end_rt_changed := 'Y';
908 END IF;
909
910 IF(x_ledger_category_code IN('PRIMARY','SECONDARY')
911 AND recinfo.ret_earn_code_combination_id = -1
912 AND recinfo.sla_accounting_method_code IS NOT NULL) THEN
913 IF(x_ledger_category_code = 'PRIMARY')THEN
914 xla_acct_setup_pub_pkg.setup_ledger_options
915 (x_ledger_id,
916 x_ledger_id);
917 ELSE
918 FOR v_primary_ledgers IN c_primary_ledgers LOOP
919 xla_acct_setup_pub_pkg.setup_ledger_options
920 (v_primary_ledgers.object_id,
921 x_ledger_id);
922 END LOOP;
923 END IF;
924 END IF;
925
926 -- Updating budgetary control flag from Y to N
927 IF ((recinfo.enable_budgetary_control_flag = 'Y') AND (x_enable_budgetary_control_f = 'N')) THEN
928 IF (GL_SUMMARY_TEMPLATES_PKG.is_funds_check_not_none(x_ledger_Id)) THEN
929 fnd_message.set_name('SQLGL', 'GL_DISABLE_BUDGETARY_CONTROL');
930 app_exception.raise_exception;
931 END IF;
932 IF (GL_BUD_ASSIGN_RANGE_PKG.is_funds_check_not_none(x_ledger_Id)) THEN
933 fnd_message.set_name('SQLGL', 'GL_DISABLE_BUDGETARY_CONTROL');
934 app_exception.raise_exception;
935 END IF;
936 END IF;
937
938 -- Updating budget journals flag from N to Y
939 IF ((recinfo.require_budget_journals_flag = 'N') AND (x_require_budget_journals_flag = 'Y')) THEN
940 IF (GL_BUDGETS_PKG.is_budget_journals_not_req(x_ledger_Id)) THEN
941 fnd_message.set_name('SQLGL', 'GL_FAIL_REQUIRE_BUDGET_JOURNAL');
942 app_exception.raise_exception;
943 END IF;
944 END IF;
945
946 -- Updating average balances translation options
947 IF ((recinfo.translate_eod_flag <> x_translate_eod_flag) OR
948 (recinfo.translate_qatd_flag <> x_translate_qatd_flag) OR
949 (recinfo.translate_yatd_flag <> x_translate_yatd_flag)) THEN
950 IF (GL_LEDGERS_PKG.Check_Avg_Translation(x_ledger_Id)) THEN
951 fnd_message.set_name('SQLGL', 'GL_LEDGER_TRANSLATION_FLAGS');
952 app_exception.raise_exception;
953 END IF;
954 END IF;
955
956 -- Updating MRC ledger type code from N to R or P
957 -- IF ((old_mrc_ledger_type_code <> 'N') AND (x_mrc_ledger_type_code <> old_mrc_ledger_type_code)) THEN
958 -- fnd_message.set_name('SQLGL', 'GL_API_LEDGER_MRC_SOB_NOT_UPDATE');
959 -- app_exception.raise_exception;
960 -- END IF;
961
962 IF (recinfo.enable_reval_ss_track_flag = 'N' AND x_enable_reval_ss_track_flag = 'Y') THEN
963 IF (FND_FLEX_APIS.get_qualifier_segnum(101,'GL#',x_chart_of_accounts_id,'GL_SECONDARY_TRACKING',temp) = FALSE) THEN
964 fnd_message.set_name('SQLGL', 'GL_API_LEDGER_CHK_SECD_SEG');
965 app_exception.raise_exception;
966 END IF;
967 END IF;
968 -- end of Ledger API checks.
969
970 ----- temporary validation starts
971 IF x_period_average_rate_type IS NOT NULL THEN
972 BEGIN
973 SELECT conversion_type
974 INTO t_period_average_rate_type
975 FROM gl_daily_conversion_types_v
976 WHERE conversion_type <> 'User'
977 AND conversion_type <> 'EMU FIXED'
978 AND conversion_type = x_period_average_rate_type;
979 EXCEPTION
980 WHEN NO_DATA_FOUND THEN
981 fnd_message.set_name('SQLGL', 'GL_ASF_LGR_NEED_PAVE_RATETYPE');
982 fnd_msg_pub.ADD;
983 x_return_status := fnd_api.g_ret_sts_unexp_error;
984 END;
985 END IF;
986
987 IF x_period_end_rate_type IS NOT NULL THEN
988 BEGIN
989 SELECT conversion_type
990 INTO t_period_end_rate_type
991 FROM gl_daily_conversion_types_v
992 WHERE conversion_type <> 'User'
993 AND conversion_type <> 'EMU FIXED'
994 AND conversion_type = x_period_end_rate_type;
995 EXCEPTION
996 WHEN NO_DATA_FOUND THEN
997 fnd_message.set_name('SQLGL', 'GL_ASF_LGR_NEED_PEND_RATETYPE');
998 fnd_msg_pub.ADD;
999 x_return_status := fnd_api.g_ret_sts_unexp_error;
1000 END;
1001 END IF;
1002
1003 IF x_sla_accounting_method_code IS NOT NULL THEN
1004 BEGIN
1005 SELECT language_code
1006 INTO t_sla_description_language
1007 FROM fnd_languages_vl
1008 WHERE (installed_flag = 'I' OR installed_flag = 'B')
1009 AND language_code = x_sla_description_language;
1010 EXCEPTION
1011 WHEN NO_DATA_FOUND THEN
1012 fnd_message.set_name('SQLGL', 'GL_ASF_LGR_NEED_JE_DESC');
1013 fnd_msg_pub.ADD;
1014 x_return_status := fnd_api.g_ret_sts_unexp_error;
1015 END;
1016 END IF;
1017
1018 IF x_criteria_set_id IS NOT NULL THEN
1019 BEGIN
1020 SELECT criteria_set_id
1021 INTO t_criteria_set_id
1022 FROM gl_autorev_criteria_sets
1023 WHERE criteria_set_id = x_criteria_set_id;
1024 EXCEPTION
1025 WHEN NO_DATA_FOUND THEN
1026 fnd_message.set_name('SQLGL',
1027 'GL_ASF_LGR_JE_REVERSAL_INVALID');
1028 fnd_msg_pub.ADD;
1029 x_return_status := fnd_api.g_ret_sts_unexp_error;
1030 END;
1031 END IF;
1032
1033 ----- temporary validation ends
1034 SELECT NAME, short_name
1035 INTO x_current_name, x_current_short_name
1036 FROM gl_ledgers
1037 WHERE ledger_id = x_ledger_id;
1038
1039 IF (x_current_name <> x_name) THEN
1040 -- Added where clauses to exclude ALC balance level relationships
1041 UPDATE gl_ledger_relationships
1042 SET target_ledger_name = x_name
1043 WHERE target_ledger_id = x_ledger_id
1044 AND ( target_ledger_category_code <> 'ALC'
1045 OR ( target_ledger_category_code = 'ALC'
1046 AND relationship_type_code <> 'BALANCE'));
1047 END IF;
1048
1049 IF (x_current_short_name <> x_short_name) THEN
1050 -- Added where clauses to exclude ALC balance level relationships
1051 UPDATE gl_ledger_relationships
1052 SET target_ledger_short_name = x_short_name
1053 WHERE target_ledger_id = x_ledger_id
1054 AND ( target_ledger_category_code <> 'ALC'
1055 OR ( target_ledger_category_code = 'ALC'
1056 AND relationship_type_code <> 'BALANCE'));
1057 END IF;
1058
1059 -- the following logic will be moved to a API package for table GL_LEDGER_CONFIG_details
1060 IF ( (x_ledger_category_code = 'PRIMARY')
1061 OR (x_ledger_category_code = 'SECONDARY')) THEN
1062 IF (x_current_name <> x_name) THEN
1063 UPDATE gl_ledger_config_details
1064 SET object_name = x_name
1065 WHERE configuration_id = x_configuration_id
1066 AND object_id = x_ledger_id
1067 AND object_type_code = x_ledger_category_code;
1068 END IF;
1069
1070 SELECT bal_seg_value_option_code, sla_accounting_method_code,
1071 sla_accounting_method_type, allow_intercompany_post_flag
1072 INTO x_current_bsv_option_code, x_current_sla_actg_method_code,
1073 x_current_sla_actg_method_type, x_current_allow_intercom_flag
1074 FROM gl_ledgers
1075 WHERE ledger_id = x_ledger_id;
1076
1077 /* IF (x_current_bsv_option_code <> x_bal_seg_value_option_code) THEN
1078 INSERT INTO gl_ledger_config_details
1079 (configuration_id, object_type_code,
1080 object_id, object_name, setup_step_code,
1081 next_action_code, status_code, created_by,
1082 last_update_login, last_update_date,
1083 last_updated_by, creation_date)
1084 VALUES (x_configuration_id, x_ledger_category_code,
1085 x_ledger_id, x_name, 'BSV_ASSIGNMENTS',
1086 'ASSIGN_BSV', 'NOT_STARTED', x_last_update_login,
1087 x_last_update_login, x_last_update_date,
1088 x_last_updated_by, x_last_update_date);
1089 UPDATE gl_ledgers
1090 SET bal_seg_value_option_code = x_bal_seg_value_option_code
1091 WHERE ledger_id IN(
1092 SELECT DISTINCT target_ledger_id
1093 FROM gl_ledger_relationships
1094 WHERE source_ledger_id = x_ledger_id
1095 AND target_ledger_category_code = 'ALC');
1096 END IF;*/
1097
1098 IF ( ( (x_current_sla_actg_method_code IS NULL)
1099 AND (x_sla_accounting_method_code IS NOT NULL))
1100 OR ( (x_current_sla_actg_method_type IS NULL)
1101 AND (x_sla_accounting_method_type IS NOT NULL))) THEN
1102 INSERT INTO gl_ledger_config_details
1103 (configuration_id, object_type_code,
1104 object_id, object_name, setup_step_code,
1105 next_action_code, status_code,
1106 created_by, last_update_login,
1107 last_update_date, last_updated_by,
1108 creation_date)
1109 VALUES (x_configuration_id, x_ledger_category_code,
1110 x_ledger_id, x_name, 'SLAM_SETUP',
1111 'REVIEW_DEFAULTS', 'CONFIRMED',
1112 x_last_update_login, x_last_update_login,
1113 x_last_update_date, x_last_updated_by,
1114 x_last_update_date);
1115
1116 p_sla_accounting_method_code := x_sla_accounting_method_code;
1117 p_sla_accounting_method_type := x_sla_accounting_method_type;
1118 p_sla_description_language := x_sla_description_language;
1119 p_sla_entered_cur_bal_sus_ccid := x_sla_entered_cur_bal_sus_ccid;
1120 p_sla_bal_by_ledger_curr_flag := x_sla_bal_by_ledger_curr_flag;
1121 p_sla_ledger_cur_bal_sus_ccid := x_sla_ledger_cur_bal_sus_ccid;
1122 --bug 3248289, calling xla api
1123 /* move to after update ledger
1124 IF (x_ledger_category_code = 'PRIMARY') THEN
1125 xla_acct_setup_pub_pkg.setup_ledger_options(x_ledger_id,
1126 x_ledger_id);
1127 ELSIF(x_ledger_category_code = 'SECONDARY') THEN
1128 FOR v_primary_ledgers IN c_primary_ledgers LOOP
1129 xla_acct_setup_pub_pkg.setup_ledger_options
1130 (v_primary_ledgers.object_id,
1131 x_ledger_id);
1132 END LOOP;
1133 END IF;
1134 */
1135 ELSIF( ( (x_current_sla_actg_method_code IS NOT NULL)
1136 AND (x_sla_accounting_method_code IS NULL))
1137 OR ( (x_current_sla_actg_method_type IS NOT NULL)
1138 AND (x_sla_accounting_method_type IS NULL))) THEN
1139 DELETE FROM gl_ledger_config_details
1140 WHERE configuration_id = x_configuration_id
1141 AND object_type_code = x_ledger_category_code
1142 AND object_id = x_ledger_id
1143 AND object_name = x_name
1144 AND setup_step_code = 'SLAM_SETUP';
1145
1146 p_sla_sequencing_flag := 'N'; -- remove this flag to null
1147 ELSIF( (x_current_sla_actg_method_code <>
1148 x_sla_accounting_method_code)
1149 OR (x_current_sla_actg_method_type <>
1150 x_sla_accounting_method_type)) THEN
1151 UPDATE gl_ledger_config_details
1152 SET next_action_code = 'REVIEW_DEFAULTS',
1153 status_code = 'CONFIRMED',
1154 last_update_login = x_last_update_login,
1155 last_update_date = x_last_update_date,
1156 last_updated_by = x_last_updated_by
1157 WHERE configuration_id = x_configuration_id
1158 AND object_type_code = x_ledger_category_code
1159 AND object_id = x_ledger_id
1160 AND object_name = x_name
1161 AND setup_step_code = 'SLAM_SETUP';
1162
1163 p_sla_accounting_method_code := x_sla_accounting_method_code;
1164 p_sla_accounting_method_type := x_sla_accounting_method_type;
1165 p_sla_description_language := x_sla_description_language;
1166 p_sla_entered_cur_bal_sus_ccid := x_sla_entered_cur_bal_sus_ccid;
1167 p_sla_bal_by_ledger_curr_flag := x_sla_bal_by_ledger_curr_flag;
1168 p_sla_ledger_cur_bal_sus_ccid := x_sla_ledger_cur_bal_sus_ccid;
1169 ELSE
1170 p_sla_accounting_method_code := x_sla_accounting_method_code;
1171 p_sla_accounting_method_type := x_sla_accounting_method_type;
1172 p_sla_description_language := x_sla_description_language;
1173 p_sla_entered_cur_bal_sus_ccid := x_sla_entered_cur_bal_sus_ccid;
1174 p_sla_bal_by_ledger_curr_flag := x_sla_bal_by_ledger_curr_flag;
1175 p_sla_ledger_cur_bal_sus_ccid := x_sla_ledger_cur_bal_sus_ccid;
1176 END IF;
1177
1178 -- Move this to a single update SQL for all attributes
1179 -- required to be sychronized for ALC ledgers
1180 /* UPDATE gl_ledgers
1181 SET future_enterable_periods_limit =
1182 x_future_enterable_periods_lmt
1183 WHERE ledger_id IN(
1184 SELECT DISTINCT target_ledger_id
1185 FROM gl_ledger_relationships
1186 WHERE source_ledger_id = x_ledger_id
1187 --AND target_ledger_id <> source_ledger_id
1188 AND target_ledger_category_code = 'ALC'); */
1189 IF ( (x_current_allow_intercom_flag = 'N')
1190 AND (x_allow_intercompany_post_flag = 'Y')) THEN
1191 --SELECT acctg_environment_code
1192 -- INTO x_acctg_environment_code
1193 -- FROM gl_ledger_configurations
1194 -- WHERE configuration_id = x_configuration_id;
1195 -- bug fix 3175231 insert the CE row in config details
1196 --IF ( (x_acctg_environment_code = 'EXCLUSIVE')
1197 -- OR ( (x_acctg_environment_code = 'SHARED')
1198 -- AND (x_bal_seg_value_option_code = 'I') ) ) THEN
1199 INSERT INTO gl_ledger_config_details
1200 (configuration_id, object_type_code,
1201 object_id, object_name, setup_step_code,
1202 next_action_code, status_code, created_by,
1203 last_update_login, last_update_date,
1204 last_updated_by, creation_date)
1205 VALUES (x_configuration_id, x_ledger_category_code,
1206 x_ledger_id, x_name, 'INTRA_BAL',
1207 'DEFINE_RULES', 'NOT_STARTED', x_last_update_login,
1208 x_last_update_login, x_last_update_date,
1209 x_last_updated_by, x_last_update_date);
1210 --END IF;
1211 ELSIF( (x_current_allow_intercom_flag = 'Y')
1212 AND (x_allow_intercompany_post_flag = 'N')) THEN
1213 DELETE gl_ledger_config_details
1214 WHERE configuration_id = x_configuration_id
1215 AND object_id = x_ledger_id
1216 AND setup_step_code = 'INTRA_BAL';
1217 END IF;
1218 ELSE
1219 p_sla_accounting_method_code := x_sla_accounting_method_code;
1220 p_sla_accounting_method_type := x_sla_accounting_method_type;
1221
1222 IF (x_sla_accounting_method_code IS NULL) THEN
1223 p_sla_description_language := NULL;
1224 p_sla_entered_cur_bal_sus_ccid := NULL;
1225 p_sla_bal_by_ledger_curr_flag := NULL;
1226 p_sla_ledger_cur_bal_sus_ccid := NULL;
1227 ELSE
1228 p_sla_description_language := x_sla_description_language;
1229 p_sla_entered_cur_bal_sus_ccid := x_sla_entered_cur_bal_sus_ccid;
1230 p_sla_bal_by_ledger_curr_flag := x_sla_bal_by_ledger_curr_flag;
1231 p_sla_ledger_cur_bal_sus_ccid := x_sla_ledger_cur_bal_sus_ccid;
1232 END IF;
1233 END IF;
1234
1235 -- The following code is for bug 2826511, there are OA FWK issues with Key Flexfield
1236 -- After the OA FWK fully support Key Flexfield (i.e., generate CCID correctly)
1237 -- We will remove the following code
1238 -- Start the temporary Code
1239 BEGIN
1240 SELECT code_combination_id
1241 INTO x_cum_trans_ccid_temp
1242 FROM gl_code_combinations
1243 WHERE code_combination_id = x_cum_trans_ccid;
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 x_cum_trans_ccid_temp := NULL;
1247 END;
1248
1249 BEGIN
1250 SELECT code_combination_id
1251 INTO x_res_encumb_ccid_temp
1252 FROM gl_code_combinations
1253 WHERE code_combination_id = x_res_encumb_ccid;
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 x_res_encumb_ccid_temp := NULL;
1257 END;
1258
1259 BEGIN
1260 SELECT code_combination_id
1261 INTO x_net_income_ccid_temp
1262 FROM gl_code_combinations
1263 WHERE code_combination_id = x_net_income_ccid;
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 x_net_income_ccid_temp := NULL;
1267 END;
1268
1269 BEGIN
1270 SELECT code_combination_id
1271 INTO x_rounding_ccid_temp
1272 FROM gl_code_combinations
1273 WHERE code_combination_id = x_rounding_ccid;
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 x_rounding_ccid_temp := NULL;
1277 END;
1278
1279 --- End the temporary Code
1280
1281 --- There are some columns are not updateable as business rule.
1282 --- Enhancement: I try to comments them out in following update to avoid
1283 --- potential mistake.
1284 UPDATE gl_ledgers
1285 SET --ledger_id = x_ledger_id,
1286 NAME = x_name,
1287 short_name = x_short_name,
1288 --chart_of_accounts_id = x_chart_of_accounts_id,
1289 --currency_code = x_currency_code,
1290 --period_set_name = x_period_set_name,
1291 --accounted_period_type = x_period_type,
1292 first_ledger_period_name = x_first_ledger_period_name,
1293 ret_earn_code_combination_id = x_ret_earn_code_combination_id,
1294 suspense_allowed_flag = x_suspense_allowed_flag,
1295 allow_intercompany_post_flag = x_allow_intercompany_post_flag,
1296 enable_average_balances_flag = x_enable_avgbal_flag,
1297 enable_budgetary_control_flag = x_enable_budgetary_control_f,
1298 require_budget_journals_flag = x_require_budget_journals_flag,
1299 enable_je_approval_flag = x_enable_je_approval_flag,
1300 enable_automatic_tax_flag = x_enable_automatic_tax_flag,
1301 consolidation_ledger_flag = x_consolidation_ledger_flag,
1302 translate_eod_flag = x_translate_eod_flag,
1303 translate_qatd_flag = x_translate_qatd_flag,
1304 translate_yatd_flag = x_translate_yatd_flag,
1305 automatically_created_flag = x_automatically_created_flag,
1306 track_rounding_imbalance_flag = x_track_rounding_imbalance_f,
1307 -- mrc_ledger_type_code = x_mrc_ledger_type_code,
1308 le_ledger_type_code = x_le_ledger_type_code,
1309 bal_seg_value_option_code = x_bal_seg_value_option_code,
1310 --bal_seg_column_name = x_bal_seg_column_name,
1311 --bal_seg_value_set_id = x_bal_seg_value_set_id,
1312 mgt_seg_value_option_code = x_mgt_seg_value_option_code,
1313 --mgt_seg_column_name = x_mgt_seg_column_name,
1314 --mgt_seg_value_set_id = x_mgt_seg_value_set_id,
1315 last_update_date = x_last_update_date,
1316 last_updated_by = x_last_updated_by,
1317 last_update_login = x_last_update_login,
1318 description = x_description,
1319 future_enterable_periods_limit = x_future_enterable_periods_lmt,
1320 latest_opened_period_name = x_latest_opened_period_name,
1321 --latest_encumbrance_year = x_latest_encumbrance_year,
1322 cum_trans_code_combination_id = x_cum_trans_ccid_temp,
1323 res_encumb_code_combination_id = x_res_encumb_ccid_temp,
1324 net_income_code_combination_id = x_net_income_ccid_temp,
1325 rounding_code_combination_id = x_rounding_ccid_temp,
1326 transaction_calendar_id = x_transaction_calendar_id,
1327 daily_translation_rate_type = x_daily_translation_rate_type,
1328 period_average_rate_type = x_period_average_rate_type,
1329 period_end_rate_type = x_period_end_rate_type,
1330 CONTEXT = x_context,
1331 attribute1 = x_attribute1,
1332 attribute2 = x_attribute2,
1333 attribute3 = x_attribute3,
1334 attribute4 = x_attribute4,
1335 attribute5 = x_attribute5,
1336 attribute6 = x_attribute6,
1337 attribute7 = x_attribute7,
1338 attribute8 = x_attribute8,
1339 attribute9 = x_attribute9,
1340 attribute10 = x_attribute10,
1341 attribute11 = x_attribute11,
1342 attribute12 = x_attribute12,
1343 attribute13 = x_attribute13,
1344 attribute14 = x_attribute14,
1345 attribute15 = x_attribute15,
1346 --child_ledger_access_code = x_child_ledger_access_code,
1347 ledger_category_code = x_ledger_category_code,
1348 configuration_id = x_configuration_id,
1349 --association_level_code = x_association_level_code,
1350 sla_accounting_method_code = p_sla_accounting_method_code,
1351 sla_accounting_method_type = p_sla_accounting_method_type,
1352 sla_description_language = p_sla_description_language,
1353 sla_entered_cur_bal_sus_ccid = p_sla_entered_cur_bal_sus_ccid,
1354 sla_bal_by_ledger_curr_flag = p_sla_bal_by_ledger_curr_flag,
1355 sla_ledger_cur_bal_sus_ccid = p_sla_ledger_cur_bal_sus_ccid,
1356 sla_sequencing_flag =
1357 DECODE(p_sla_sequencing_flag,
1358 'N', NULL,
1359 sla_sequencing_flag),
1360 alc_ledger_type_code = x_alc_ledger_type_code,
1361 criteria_set_id = x_criteria_set_id,
1362 enable_secondary_track_flag = x_enable_secondary_track_flag,
1363 enable_reval_ss_track_flag = x_enable_reval_ss_track_flag,
1364 enable_reconciliation_flag = x_enable_reconciliation_flag,
1365 sla_ledger_cash_basis_flag = x_sla_ledger_cash_basis_flag,
1366 create_je_flag = x_create_je_flag
,
1367 commitment_budget_flag = x_commitment_budget_flag
1368 WHERE ledger_id = x_ledger_id;
1369
1370 -- Bug fix 3265048: Move this check before updating ALC ledgers
1371 IF (SQL%NOTFOUND) THEN
1372 --RAISE NO_DATA_FOUND;
1373 fnd_msg_pub.ADD;
1374 x_return_status := fnd_api.g_ret_sts_unexp_error;
1375 END IF;
1376
1377 IF ( ( (x_current_sla_actg_method_code IS NULL)
1378 AND (x_sla_accounting_method_code IS NOT NULL))
1379 OR ( (x_current_sla_actg_method_type IS NULL)
1380 AND (x_sla_accounting_method_type IS NOT NULL))) THEN
1381 --bug 3248289, calling xla api
1382 IF (x_ledger_category_code = 'PRIMARY') THEN
1383 xla_acct_setup_pub_pkg.setup_ledger_options(x_ledger_id,
1384 x_ledger_id);
1385 ELSIF(x_ledger_category_code = 'SECONDARY') THEN
1386 FOR v_primary_ledgers IN c_primary_ledgers LOOP
1387 xla_acct_setup_pub_pkg.setup_ledger_options
1388 (v_primary_ledgers.object_id,
1389 x_ledger_id);
1390 END LOOP;
1391 END IF;
1392 END IF;
1393
1394 BEGIN
1395 SELECT completion_status_code
1396 INTO x_completion_status
1397 FROM gl_ledger_configurations
1398 WHERE configuration_id = x_configuration_id;
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 x_completion_status := NULL;
1402 END;
1403
1404 -- Propagate the setup changes to its ALC ledgers if it is a ALC source
1405 IF (x_alc_ledger_type_code = 'SOURCE') THEN
1406 UPDATE gl_ledgers alclg
1407 SET (future_enterable_periods_limit, suspense_allowed_flag,
1408 allow_intercompany_post_flag, bal_seg_value_option_code,
1409 mgt_seg_value_option_code, sla_accounting_method_code,
1410 sla_accounting_method_type, sla_description_language,
1411 sla_bal_by_ledger_curr_flag, sla_sequencing_flag,
1412 sla_entered_cur_bal_sus_ccid, sla_ledger_cur_bal_sus_ccid,
1413 last_update_date, last_updated_by, last_update_login,
1414 first_ledger_period_name, ret_earn_code_combination_id,
1415 track_rounding_imbalance_flag, enable_average_balances_flag,
1416 cum_trans_code_combination_id, res_encumb_code_combination_id,
1417 net_income_code_combination_id, rounding_code_combination_id,
1418 enable_automatic_tax_flag, consolidation_ledger_flag,
1419 translate_eod_flag, translate_qatd_flag, translate_yatd_flag,
1420 transaction_calendar_id, daily_translation_rate_type,
1421 criteria_set_id, period_average_rate_type,
1422 period_end_rate_type, enable_secondary_track_flag,
1423 enable_reval_ss_track_flag, enable_reconciliation_flag,
1424 sla_ledger_cash_basis_flag, context, attribute1,
1425 attribute2, attribute3, attribute4, attribute5,
1426 attribute6, attribute7, attribute8, attribute9,
1427 attribute10, attribute11, attribute12, attribute13,
1428 attribute14, attribute15) =
1429 (SELECT srclg.future_enterable_periods_limit,
1430 decode(l_suspense_changed, 'Y',
1431 srclg.suspense_allowed_flag,
1432 alclg.suspense_allowed_flag),
1433 DECODE(l_intercom_changed, 'Y',
1434 srclg.allow_intercompany_post_flag,
1435 alclg.allow_intercompany_post_flag),
1436 srclg.bal_seg_value_option_code,
1437 srclg.mgt_seg_value_option_code,
1438 srclg.sla_accounting_method_code,
1439 srclg.sla_accounting_method_type,
1440 srclg.sla_description_language,
1441 srclg.sla_bal_by_ledger_curr_flag,
1442 srclg.sla_sequencing_flag,
1443
1444 -- SLA sus CCIDs of ALC must be same as its source's
1445 srclg.sla_entered_cur_bal_sus_ccid,
1446 srclg.sla_ledger_cur_bal_sus_ccid,
1447 srclg.last_update_date, srclg.last_updated_by,
1448 srclg.last_update_login,
1449 decode(srclg.complete_flag, 'Y',
1450 alclg.first_ledger_period_name,
1451 srclg.first_ledger_period_name),
1452 decode(l_ret_changed, 'Y',
1453 srclg.ret_earn_code_combination_id,
1454 alclg.ret_earn_code_combination_id),
1455 decode(srclg.track_rounding_imbalance_flag,'Y',
1456 'Y', alclg.track_rounding_imbalance_flag),
1457 decode(srclg.complete_flag, 'Y',
1458 alclg.enable_average_balances_flag,
1459 srclg.enable_average_balances_flag),
1460 decode(l_cta_changed, 'Y',
1461 srclg.cum_trans_code_combination_id,
1462 alclg.cum_trans_code_combination_id),
1463 decode(l_reserv_encum_changed, 'Y',
1464 srclg.res_encumb_code_combination_id,
1465 alclg.res_encumb_code_combination_id),
1466 decode(srclg.complete_flag, 'Y',
1467 alclg.net_income_code_combination_id,
1468 srclg.net_income_code_combination_id),
1469 decode(alclg.rounding_code_combination_id, null,
1470 srclg.rounding_code_combination_id, -1,
1471 srclg.rounding_code_combination_id,
1472 alclg.rounding_code_combination_id),
1473 decode(l_autotax_changed, 'Y',
1474 srclg.enable_automatic_tax_flag,
1475 alclg.enable_automatic_tax_flag),
1476 decode(srclg.complete_flag, 'Y',
1477 alclg.consolidation_ledger_flag,
1478 srclg.consolidation_ledger_flag),
1479 decode(l_trans_eod_changed, 'Y',
1480 srclg.translate_eod_flag,
1481 alclg.translate_eod_flag),
1482 decode(l_trans_qatd_changed, 'Y',
1483 srclg.translate_qatd_flag,
1484 alclg.translate_qatd_flag),
1485 decode(l_trans_yatd_changed, 'Y',
1486 srclg.translate_yatd_flag,
1487 alclg.translate_yatd_flag),
1488 decode(srclg.complete_flag, 'Y',
1489 alclg.transaction_calendar_id,
1490 srclg.transaction_calendar_id),
1491 decode(srclg.complete_flag, 'Y',
1492 alclg.daily_translation_rate_type,
1493 srclg.daily_translation_rate_type),
1494 srclg.criteria_set_id,
1495 decode(l_period_avg_rt_changed, 'Y',
1496 srclg.period_average_rate_type,
1497 alclg.period_average_rate_type),
1498 decode(l_period_end_rt_changed, 'Y',
1499 srclg.period_end_rate_type,
1500 alclg.period_end_rate_type),
1501 decode(srclg.complete_flag, 'Y',
1502 alclg.enable_secondary_track_flag,
1503 srclg.enable_secondary_track_flag),
1504 decode(srclg.complete_flag, 'Y',
1505 alclg.enable_reval_ss_track_flag,
1506 srclg.enable_reval_ss_track_flag),
1507 srclg.enable_reconciliation_flag,
1508 srclg.sla_ledger_cash_basis_flag,
1509 srclg.context,
1510 srclg.attribute1,
1511 srclg.attribute2,
1512 srclg.attribute3,
1513 srclg.attribute4,
1514 srclg.attribute5,
1515 srclg.attribute6,
1516 srclg.attribute7,
1517 srclg.attribute8,
1518 srclg.attribute9,
1519 srclg.attribute10,
1520 srclg.attribute11,
1521 srclg.attribute12,
1522 srclg.attribute13,
1523 srclg.attribute14,
1524 srclg.attribute15
1525 FROM gl_ledgers srclg
1526 WHERE ledger_id = x_ledger_id)
1527 WHERE ledger_id IN(
1528 SELECT target_ledger_id
1529 FROM gl_ledger_relationships
1530 WHERE source_ledger_id = x_ledger_id
1531 AND target_ledger_category_code = 'ALC'
1532 AND relationship_type_code IN('SUBLEDGER', 'JOURNAL'));
1533 END IF; -- IF (x_alc_ledger_type_coe = 'SOURCE')
1534
1535 -- Update the implicit access set name for the ledger
1536 IF (x_implicit_access_set_id IS NOT NULL) THEN
1537 gl_access_sets_pkg.update_implicit_access_set
1538 (x_access_set_id => x_implicit_access_set_id,
1539 x_name => x_name,
1540 x_last_update_date => x_last_update_date,
1541 x_last_updated_by => x_last_updated_by,
1542 x_last_update_login => x_last_update_login);
1543 END IF;
1544
1545 IF(x_ledger_category_code = 'PRIMARY') THEN
1546 update gl_ledger_configurations
1547 set name = x_name
1548 where configuration_id =
1549 (select configuration_id
1550 from gl_ledger_config_details
1551 where object_id = x_ledger_id
1552 and object_type_code = 'PRIMARY'
1553 and setup_step_code = 'NONE');
1554 END IF;
1555
1556 -- Check whether journal_approval_flag is to be set to Y for
1557 -- the Manual source.
1558 IF (x_set_manual_flag = 'Y') THEN
1559 gl_ledgers_pkg.enable_manual_je_approval;
1560 END IF;
1561
1562 -- Update gl_system_usage and insert the net income account
1563 IF (recinfo.enable_average_balances_flag = 'N' AND x_enable_avgbal_flag = 'Y') THEN
1564 -- get the balance segment infor and mgt segment infor
1565 gl_ledgers_pkg.get_bal_mgt_seg_info(x_bal_seg_column_name,
1566 x_bal_seg_value_set_id,
1567 x_mgt_seg_column_name,
1568 x_mgt_seg_value_set_id,
1569 x_chart_of_accounts_id);
1570
1571 IF x_net_income_ccid IS NOT NULL THEN
1572 BEGIN
1573 v_CursorSQL :=
1574 'SELECT ' || x_bal_seg_column_name
1575 || ' FROM gl_code_combinations WHERE chart_of_accounts_id = :1 '
1576 || ' AND code_combination_id = :2 ';
1577 EXECUTE IMMEDIATE v_CursorSQL INTO v_balancing_segment
1578 USING x_chart_of_accounts_id, x_net_income_ccid;
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 v_balancing_segment := x_balancing_segment;
1582 END;
1583 ELSE
1584 v_balancing_segment := x_balancing_segment;
1585 END IF;
1586
1587 gl_ledgers_pkg.update_gl_system_usages(x_consolidation_ledger_flag);
1588 gl_ledgers_pkg.insert_gl_net_income_accounts(x_ledger_id,
1589 v_balancing_segment,
1590 --x_balancing_segment,
1591 x_net_income_ccid,
1592 x_last_update_date,
1593 x_last_updated_by,
1594 x_last_update_date,
1595 x_last_updated_by,
1596 x_last_update_login,
1597 '', '', '', '');
1598 END IF;
1599
1600
1601 -- Added by LPOON on 11/20/03: only insert suspense CCID when the suspense
1602 -- flag is turned on in order to avoid inserting invalid suspense CCID
1603 BEGIN
1604 SELECT code_combination_id
1605 INTO x_suspense_ccid_temp
1606 FROM gl_code_combinations
1607 WHERE code_combination_id = x_suspense_ccid;
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 x_suspense_ccid_temp := NULL;
1611 END;
1612
1613 -- IF ( (x_suspense_allowed_flag = 'Y')
1614 -- AND (x_suspense_ccid_temp IS NOT NULL)) THEN
1615 -- Update suspense account for this ledger
1616 -- bug fix 2826511
1617 gl_ledgers_pkg.led_update_other_tables(x_ledger_id,
1618 x_last_update_date,
1619 x_last_updated_by,
1620 x_suspense_ccid_temp);
1621
1622 -- Check each ALC ledger of this ledger and if it doesn't have existing
1623 -- suspense account, default it same as source's
1624 FOR v_alc IN c_alc_ledgers LOOP
1625 -- IF ( NOT gl_suspense_accounts_pkg.is_ledger_suspense_exist(v_alc.ledger_id)
1626 -- AND x_suspense_ccid_temp IS NOT NULL) THEN
1627 gl_ledgers_pkg.led_update_other_tables(v_alc.target_ledger_id,
1628 x_last_update_date,
1629 x_last_updated_by,
1630 x_suspense_ccid_temp);
1631 -- gl_suspense_accounts_pkg.insert_ledger_suspense
1632 -- (v_alc.target_ledger_id,
1633 -- x_suspense_ccid_temp,
1634 -- x_last_update_date,
1635 -- x_last_updated_by);
1636 -- END IF; -- IF (NOT gl_suspense_accounts_pkg.is_ledger_suspense...
1637 END LOOP; -- FOR v_alc IN c_alc_ledgers
1638 -- END IF;
1639
1640 x_msg_count := fnd_msg_pub.count_msg;
1641
1642 IF x_msg_count > 0 THEN
1643 x_return_status := fnd_api.g_ret_sts_unexp_error;
1644 RAISE fnd_api.g_exc_error;
1645 END IF;
1646 EXCEPTION
1647 WHEN fnd_api.g_exc_unexpected_error THEN
1648 IF p_commit = fnd_api.g_true THEN
1649 ROLLBACK TO complete_workorder;
1650 END IF;
1651
1652 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
1653 p_procedure_name => l_api_name);
1654 x_return_status := fnd_api.g_ret_sts_unexp_error;
1655 WHEN fnd_api.g_exc_error THEN
1656 IF p_commit = fnd_api.g_true THEN
1657 ROLLBACK TO complete_workorder;
1658 END IF;
1659
1660 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
1661 p_procedure_name => l_api_name);
1662 x_return_status := fnd_api.g_ret_sts_unexp_error;
1663 WHEN OTHERS THEN
1664 IF p_commit = fnd_api.g_true THEN
1665 ROLLBACK TO complete_workorder;
1666 END IF;
1667
1668 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
1669 p_procedure_name => l_api_name);
1670 x_return_status := fnd_api.g_ret_sts_unexp_error;
1671 END update_row;
1672
1673 -- **********************************************************************
1674 PROCEDURE check_unique_name(
1675 x_rowid VARCHAR2,
1676 x_name VARCHAR2) IS
1677 CURSOR c_dup IS
1678 SELECT 'Duplicate'
1679 FROM gl_ledgers l
1680 WHERE l.NAME = x_name AND(x_rowid IS NULL OR l.ROWID <> x_rowid);
1681
1682 CURSOR c_dup2 IS
1683 SELECT 'Duplicate'
1684 FROM gl_access_sets a
1685 WHERE a.NAME = x_name AND a.automatically_created_flag <> 'Y';
1686
1687 dummy VARCHAR2(100);
1688 BEGIN
1689 OPEN c_dup;
1690
1691 FETCH c_dup
1692 INTO dummy;
1693
1694 IF c_dup%FOUND THEN
1695 CLOSE c_dup;
1696
1697 fnd_message.set_name('SQLGL', 'GL_LEDGER_DUPLICATE_LEDGER');
1698 app_exception.raise_exception;
1699 END IF;
1700
1701 CLOSE c_dup;
1702
1703 OPEN c_dup2;
1704
1705 FETCH c_dup2
1706 INTO dummy;
1707
1708 IF c_dup2%FOUND THEN
1709 CLOSE c_dup2;
1710
1711 fnd_message.set_name('SQLGL', 'GL_LEDGER_DUPLICATE_LEDGER');
1712 app_exception.raise_exception;
1713 END IF;
1714
1715 CLOSE c_dup2;
1716 EXCEPTION
1717 WHEN app_exceptions.application_exception THEN
1718 RAISE;
1719 WHEN OTHERS THEN
1720 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1721 fnd_message.set_token('PROCEDURE',
1722 'GL_LEDGERS_PKG.check_unique_name');
1723 RAISE;
1724 END check_unique_name;
1725
1726 -- **********************************************************************
1727 PROCEDURE check_unique_short_name(
1728 x_rowid VARCHAR2,
1729 x_short_name VARCHAR2) IS
1730 CURSOR c_dup IS
1731 SELECT 'Duplicate'
1732 FROM gl_ledgers l
1733 WHERE l.short_name = x_short_name
1734 AND (x_rowid IS NULL OR l.ROWID <> x_rowid);
1735
1736 dummy VARCHAR2(100);
1737 BEGIN
1738 OPEN c_dup;
1739
1740 FETCH c_dup
1741 INTO dummy;
1742
1743 IF c_dup%FOUND THEN
1744 CLOSE c_dup;
1745
1746 fnd_message.set_name('SQLGL', 'GL_LEDGER_DUPLICATE_SHORT_NAME');
1747 app_exception.raise_exception;
1748 END IF;
1749
1750 CLOSE c_dup;
1751 EXCEPTION
1752 WHEN app_exceptions.application_exception THEN
1753 RAISE;
1754 WHEN OTHERS THEN
1755 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1756 fnd_message.set_token('PROCEDURE',
1757 'GL_LEDGERS_PKG.check_unique_short_name');
1758 RAISE;
1759 END check_unique_short_name;
1760
1761 -- **********************************************************************
1762 FUNCTION get_unique_id
1763 RETURN NUMBER IS
1764 CURSOR c_getid IS
1765 SELECT gl_ledgers_s.NEXTVAL
1766 FROM DUAL;
1767
1768 ID NUMBER;
1769 BEGIN
1770 OPEN c_getid;
1771
1772 FETCH c_getid
1773 INTO ID;
1774
1775 IF c_getid%FOUND THEN
1776 CLOSE c_getid;
1777
1778 RETURN(ID);
1779 ELSE
1780 CLOSE c_getid;
1781
1782 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
1783 fnd_message.set_token('SEQUENCE', 'GL_LEDGERS_S');
1784 app_exception.raise_exception;
1785 END IF;
1786 EXCEPTION
1787 WHEN app_exceptions.application_exception THEN
1788 RAISE;
1789 WHEN OTHERS THEN
1790 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1791 fnd_message.set_token('PROCEDURE', 'GL_LEDGERS_PKG.get_unique_id');
1792 RAISE;
1793 END get_unique_id;
1794
1795 -- **********************************************************************
1796 FUNCTION is_coa_frozen(
1797 x_chart_of_accounts_id NUMBER)
1798 RETURN BOOLEAN IS
1799 CURSOR c_is_coa_frozen IS
1800 SELECT 'X'
1801 FROM fnd_id_flex_structures s
1802 WHERE s.application_id = 101
1803 AND s.id_flex_code = 'GL#'
1804 AND s.id_flex_num = x_chart_of_accounts_id
1805 AND s.freeze_flex_definition_flag = 'Y';
1806
1807 dummy VARCHAR2(1);
1808 BEGIN
1809 OPEN c_is_coa_frozen;
1810
1811 FETCH c_is_coa_frozen
1812 INTO dummy;
1813
1814 IF c_is_coa_frozen%FOUND THEN
1815 CLOSE c_is_coa_frozen;
1816
1817 RETURN TRUE;
1818 END IF;
1819
1820 CLOSE c_is_coa_frozen;
1821
1822 RETURN FALSE;
1823 EXCEPTION
1824 WHEN app_exceptions.application_exception THEN
1825 RAISE;
1826 WHEN OTHERS THEN
1827 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1828 fnd_message.set_token('PROCEDURE', 'GL_LEDGERS_PKG.is_coa_frozen');
1829 RAISE;
1830 END is_coa_frozen;
1831
1832 -- **********************************************************************
1833 PROCEDURE get_bal_mgt_seg_info(
1834 x_bal_seg_column_name OUT NOCOPY VARCHAR2,
1835 x_bal_seg_value_set_id OUT NOCOPY NUMBER,
1836 x_mgt_seg_column_name OUT NOCOPY VARCHAR2,
1837 x_mgt_seg_value_set_id OUT NOCOPY NUMBER,
1838 x_chart_of_accounts_id NUMBER) IS
1839 CURSOR c_get_bal_seg_column_name IS
1840 SELECT s.application_column_name, s.flex_value_set_id
1841 FROM fnd_id_flex_segments s, fnd_segment_attribute_values v
1842 WHERE s.application_id = v.application_id
1843 AND s.id_flex_code = v.id_flex_code
1844 AND s.id_flex_num = v.id_flex_num
1845 AND s.application_column_name = v.application_column_name
1846 AND v.application_id = 101
1847 AND v.id_flex_code = 'GL#'
1848 AND v.id_flex_num = x_chart_of_accounts_id
1849 AND v.segment_attribute_type = 'GL_BALANCING'
1850 AND v.attribute_value = 'Y';
1851
1852 CURSOR c_get_mgt_seg_column_name IS
1853 SELECT s.application_column_name, s.flex_value_set_id
1854 FROM fnd_id_flex_segments s, fnd_segment_attribute_values v
1855 WHERE s.application_id = v.application_id
1856 AND s.id_flex_code = v.id_flex_code
1857 AND s.id_flex_num = v.id_flex_num
1858 AND s.application_column_name = v.application_column_name
1859 AND v.application_id = 101
1860 AND v.id_flex_code = 'GL#'
1861 AND v.id_flex_num = x_chart_of_accounts_id
1862 AND v.segment_attribute_type = 'GL_MANAGEMENT'
1863 AND v.attribute_value = 'Y';
1864 BEGIN
1865 OPEN c_get_bal_seg_column_name;
1866
1867 FETCH c_get_bal_seg_column_name
1868 INTO x_bal_seg_column_name, x_bal_seg_value_set_id;
1869
1870 IF c_get_bal_seg_column_name%FOUND THEN
1871 CLOSE c_get_bal_seg_column_name;
1872 ELSE
1873 CLOSE c_get_bal_seg_column_name;
1874
1875 x_bal_seg_column_name := NULL;
1876 x_bal_seg_value_set_id := NULL;
1877 fnd_message.set_name('SQLGL', 'GL_LEDGER_ERR_GETTING_BAL_SEG');
1878 app_exception.raise_exception;
1879 END IF;
1880
1881 OPEN c_get_mgt_seg_column_name;
1882
1883 FETCH c_get_mgt_seg_column_name
1884 INTO x_mgt_seg_column_name, x_mgt_seg_value_set_id;
1885
1886 IF c_get_mgt_seg_column_name%FOUND THEN
1887 CLOSE c_get_mgt_seg_column_name;
1888 ELSE
1889 CLOSE c_get_mgt_seg_column_name;
1890
1891 x_mgt_seg_column_name := NULL;
1892 x_mgt_seg_value_set_id := NULL;
1893 fnd_message.set_name('SQLGL', 'GL_LEDGER_ERR_GETTING_MGT_SEG');
1894 -- Now the management segment value is an optional segment
1895 -- commneted by Srini Pala.
1896 --app_exception.raise_exception;
1897 END IF;
1898 EXCEPTION
1899 WHEN app_exceptions.application_exception THEN
1900 RAISE;
1901 WHEN OTHERS THEN
1902 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1903 fnd_message.set_token('PROCEDURE',
1904 'GL_LEDGERS_PKG.get_bal_mgt_seg_info');
1905 RAISE;
1906 END get_bal_mgt_seg_info;
1907
1908 -- **********************************************************************
1909 -- This Insert_Row is used by the Ledger Form; Will be removed after the
1910 -- form is removed.
1911 PROCEDURE insert_row(
1912 x_rowid IN OUT NOCOPY VARCHAR2,
1913 x_ledger_id NUMBER,
1914 x_name VARCHAR2,
1915 x_short_name VARCHAR2,
1916 x_chart_of_accounts_id NUMBER,
1917 x_chart_of_accounts_name VARCHAR2,
1918 x_currency_code VARCHAR2,
1919 x_period_set_name VARCHAR2,
1920 x_user_period_type VARCHAR2,
1921 x_accounted_period_type VARCHAR2,
1922 x_first_ledger_period_name VARCHAR2,
1923 x_ret_earn_code_combination_id NUMBER,
1924 x_suspense_allowed_flag VARCHAR2,
1925 x_suspense_ccid NUMBER,
1926 x_allow_intercompany_post_flag VARCHAR2,
1927 x_enable_avgbal_flag VARCHAR2,
1928 x_enable_budgetary_control_f VARCHAR2,
1929 x_require_budget_journals_flag VARCHAR2,
1930 x_enable_je_approval_flag VARCHAR2,
1931 x_enable_automatic_tax_flag VARCHAR2,
1932 x_consolidation_ledger_flag VARCHAR2,
1933 x_translate_eod_flag VARCHAR2,
1934 x_translate_qatd_flag VARCHAR2,
1935 x_translate_yatd_flag VARCHAR2,
1936 x_automatically_created_flag VARCHAR2,
1937 x_track_rounding_imbalance_f VARCHAR2,
1938 x_alc_ledger_type_code VARCHAR2,
1939 x_le_ledger_type_code VARCHAR2,
1940 x_bal_seg_value_option_code VARCHAR2,
1941 x_bal_seg_column_name VARCHAR2,
1942 x_bal_seg_value_set_id NUMBER,
1943 x_mgt_seg_value_option_code VARCHAR2,
1944 x_mgt_seg_column_name VARCHAR2,
1945 x_mgt_seg_value_set_id NUMBER,
1946 x_last_update_date DATE,
1947 x_last_updated_by NUMBER,
1948 x_creation_date DATE,
1949 x_created_by NUMBER,
1950 x_last_update_login NUMBER,
1951 x_description VARCHAR2,
1952 x_future_enterable_periods_lmt NUMBER,
1953 x_latest_opened_period_name VARCHAR2,
1954 x_latest_encumbrance_year NUMBER,
1955 x_cum_trans_ccid NUMBER,
1956 x_res_encumb_ccid NUMBER,
1957 x_net_income_ccid NUMBER,
1958 x_balancing_segment VARCHAR2,
1959 x_rounding_ccid NUMBER,
1960 x_transaction_calendar_id NUMBER,
1961 x_transaction_calendar_name VARCHAR2,
1962 x_daily_translation_rate_type VARCHAR2,
1963 x_daily_user_translation_type VARCHAR2,
1964 x_period_average_rate_type VARCHAR2,
1965 x_period_avg_user_rate_type VARCHAR2,
1966 x_period_end_rate_type VARCHAR2,
1967 x_period_end_user_rate_type VARCHAR2,
1968 x_context VARCHAR2,
1969 x_attribute1 VARCHAR2,
1970 x_attribute2 VARCHAR2,
1971 x_attribute3 VARCHAR2,
1972 x_attribute4 VARCHAR2,
1973 x_attribute5 VARCHAR2,
1974 x_attribute6 VARCHAR2,
1975 x_attribute7 VARCHAR2,
1976 x_attribute8 VARCHAR2,
1977 x_attribute9 VARCHAR2,
1978 x_attribute10 VARCHAR2,
1979 x_attribute11 VARCHAR2,
1980 x_attribute12 VARCHAR2,
1981 x_attribute13 VARCHAR2,
1982 x_attribute14 VARCHAR2,
1983 x_attribute15 VARCHAR2,
1984 x_set_manual_flag VARCHAR2) IS
1985 CURSOR c IS
1986 SELECT ROWID
1987 FROM gl_ledgers
1988 WHERE NAME = x_name;
1989
1990 x_access_set_id NUMBER(15);
1991 v_security_segment_code VARCHAR2(1);
1992 v_secured_seg_value_set_id NUMBER(15);
1993 BEGIN
1994 -- Create an implicit access set header for the corresponding new created
1995 -- ledger and retrieve its ID.
1996 -- The security segment code must be 'M' for Management Ledgers and
1997 -- 'F' for Legal and Upgrade Ledgers.
1998 -- The secured segment value set id should be X_Mgt_Seg_Value_Set_Id for
1999 -- Management Ledgers and null for Legal and Upgrade Ledgers.
2000
2001 /* Now the Implicit access sets are ledger Implicit sets only modified
2002 Srini Pala*/
2003 --IF (X_Le_Ledger_Type_Code = 'M') THEN
2004 v_security_segment_code := 'F';
2005 v_secured_seg_value_set_id := NULL; -- X_Mgt_Seg_Value_Set_Id;
2006 x_access_set_id :=
2007 gl_access_sets_pkg.create_implicit_access_set
2008 (x_name => x_name,
2009 x_security_segment_code => v_security_segment_code,
2010 x_chart_of_accounts_id => x_chart_of_accounts_id,
2011 x_period_set_name => x_period_set_name,
2012 x_accounted_period_type => x_accounted_period_type,
2013 x_secured_seg_value_set_id => v_secured_seg_value_set_id,
2014 x_default_ledger_id => x_ledger_id,
2015 x_last_updated_by => x_last_updated_by,
2016 x_last_update_login => x_last_update_login,
2017 x_creation_date => x_creation_date,
2018 x_description => x_description);
2019
2020 INSERT INTO gl_ledgers
2021 (ledger_id, NAME, short_name,
2022 chart_of_accounts_id, currency_code,
2023 period_set_name, accounted_period_type,
2024 first_ledger_period_name,
2025 ret_earn_code_combination_id, suspense_allowed_flag,
2026 allow_intercompany_post_flag,
2027 enable_average_balances_flag,
2028 enable_budgetary_control_flag,
2029 require_budget_journals_flag,
2030 enable_je_approval_flag, enable_automatic_tax_flag,
2031 consolidation_ledger_flag, translate_eod_flag,
2032 translate_qatd_flag, translate_yatd_flag,
2033 automatically_created_flag,
2034 track_rounding_imbalance_flag, alc_ledger_type_code,
2035 ledger_category_code, object_type_code,
2036 le_ledger_type_code, bal_seg_value_option_code,
2037 bal_seg_column_name, bal_seg_value_set_id,
2038 mgt_seg_value_option_code, mgt_seg_column_name,
2039 mgt_seg_value_set_id, implicit_access_set_id,
2040 last_update_date, last_updated_by, creation_date,
2041 created_by, last_update_login, description,
2042 future_enterable_periods_limit, ledger_attributes,
2043 latest_opened_period_name, latest_encumbrance_year,
2044 cum_trans_code_combination_id,
2045 res_encumb_code_combination_id,
2046 net_income_code_combination_id,
2047 rounding_code_combination_id, transaction_calendar_id,
2048 daily_translation_rate_type,
2049 period_average_rate_type, period_end_rate_type,
2050 CONTEXT, attribute1, attribute2, attribute3,
2051 attribute4, attribute5, attribute6, attribute7,
2052 attribute8, attribute9, attribute10, attribute11,
2053 attribute12, attribute13, attribute14, attribute15)
2054 VALUES (x_ledger_id, x_name, x_short_name,
2055 x_chart_of_accounts_id, x_currency_code,
2056 x_period_set_name, x_accounted_period_type,
2057 x_first_ledger_period_name,
2058 x_ret_earn_code_combination_id, x_suspense_allowed_flag,
2059 x_allow_intercompany_post_flag,
2060 x_enable_avgbal_flag,
2061 x_enable_budgetary_control_f,
2062 x_require_budget_journals_flag,
2063 x_enable_je_approval_flag, x_enable_automatic_tax_flag,
2064 x_consolidation_ledger_flag, x_translate_eod_flag,
2065 x_translate_qatd_flag, x_translate_yatd_flag,
2066 x_automatically_created_flag,
2067 x_track_rounding_imbalance_f, x_alc_ledger_type_code,
2068 'NONE', 'L',
2069 x_le_ledger_type_code, x_bal_seg_value_option_code,
2070 x_bal_seg_column_name, x_bal_seg_value_set_id,
2071 x_mgt_seg_value_option_code, x_mgt_seg_column_name,
2072 x_mgt_seg_value_set_id, x_access_set_id,
2073 x_last_update_date, x_last_updated_by, x_creation_date,
2074 x_created_by, x_last_update_login, x_description,
2075 x_future_enterable_periods_lmt, 'L',
2076 x_latest_opened_period_name, x_latest_encumbrance_year,
2077 x_cum_trans_ccid,
2078 x_res_encumb_ccid,
2079 x_net_income_ccid,
2080 x_rounding_ccid, x_transaction_calendar_id,
2081 x_daily_translation_rate_type,
2082 x_period_average_rate_type, x_period_end_rate_type,
2083 x_context, x_attribute1, x_attribute2, x_attribute3,
2084 x_attribute4, x_attribute5, x_attribute6, x_attribute7,
2085 x_attribute8, x_attribute9, x_attribute10, x_attribute11,
2086 x_attribute12, x_attribute13, x_attribute14, x_attribute15);
2087
2088 OPEN c;
2089
2090 FETCH c
2091 INTO x_rowid;
2092
2093 IF (c%NOTFOUND) THEN
2094 CLOSE c;
2095
2096 RAISE NO_DATA_FOUND;
2097 END IF;
2098
2099 CLOSE c;
2100
2101 -- Insert rows into gl_concurrency_control table for the
2102 -- corresponding new created ledger.
2103 -- Should be GL_CONC_CONTROL_PKG.insert_conc_ledger(
2104 gl_conc_control_pkg.insert_conc_ledger(x_ledger_id, x_last_update_date,
2105 x_last_updated_by,
2106 x_creation_date, x_created_by,
2107 x_last_update_login);
2108 -- Insert rows into gl_period_statuses table for the
2109 -- corresponding new created ledger.
2110 gl_period_statuses_pkg.insert_led_ps(x_ledger_id, x_period_set_name,
2111 x_accounted_period_type,
2112 x_last_update_date,
2113 x_last_updated_by,
2114 x_last_update_login,
2115 x_creation_date, x_created_by);
2116
2117 -- Insert rows into gl_autoreverse_options table for the
2118 -- new ledger
2119 -- gl_autoreverse_options_pkg.insert_ledger_reversal_cat(
2120 -- x_ledger_id, x_created_by, x_last_updated_by, x_last_update_login);
2121
2122 -- Insert rows into gl_suspense_accounts table for the
2123 -- corresponding new created ledger.
2124 IF (x_suspense_ccid IS NOT NULL) THEN
2125 gl_suspense_accounts_pkg.insert_ledger_suspense(x_ledger_id,
2126 x_suspense_ccid,
2127 x_last_update_date,
2128 x_last_updated_by);
2129 END IF;
2130
2131 -- Check whether journal_approval_flag is to be set to Y for
2132 -- the Manual source.
2133 IF (x_set_manual_flag = 'Y') THEN
2134 gl_ledgers_pkg.enable_manual_je_approval;
2135 END IF;
2136
2137 IF (x_enable_avgbal_flag = 'Y') THEN
2138 gl_ledgers_pkg.update_gl_system_usages(x_consolidation_ledger_flag);
2139 gl_ledgers_pkg.insert_gl_net_income_accounts(x_ledger_id,
2140 x_balancing_segment,
2141 x_net_income_ccid,
2142 x_creation_date,
2143 x_created_by,
2144 x_last_update_date,
2145 x_last_updated_by,
2146 x_last_update_login,
2147 '', '', '', '');
2148 END IF;
2149 END insert_row;
2150
2151 -- **********************************************************************
2152 PROCEDURE lock_row(
2153 x_rowid VARCHAR2,
2154 x_ledger_id NUMBER,
2155 x_name VARCHAR2,
2156 x_short_name VARCHAR2,
2157 x_chart_of_accounts_id NUMBER,
2158 x_chart_of_accounts_name VARCHAR2,
2159 x_currency_code VARCHAR2,
2160 x_period_set_name VARCHAR2,
2161 x_user_period_type VARCHAR2,
2162 x_accounted_period_type VARCHAR2,
2163 x_first_ledger_period_name VARCHAR2,
2164 x_ret_earn_code_combination_id NUMBER,
2165 x_suspense_allowed_flag VARCHAR2,
2166 x_suspense_ccid NUMBER,
2167 x_allow_intercompany_post_flag VARCHAR2,
2168 x_enable_avgbal_flag VARCHAR2,
2169 x_enable_budgetary_control_f VARCHAR2,
2170 x_require_budget_journals_flag VARCHAR2,
2171 x_enable_je_approval_flag VARCHAR2,
2172 x_enable_automatic_tax_flag VARCHAR2,
2173 x_consolidation_ledger_flag VARCHAR2,
2174 x_translate_eod_flag VARCHAR2,
2175 x_translate_qatd_flag VARCHAR2,
2176 x_translate_yatd_flag VARCHAR2,
2177 x_automatically_created_flag VARCHAR2,
2178 x_track_rounding_imbalance_f VARCHAR2,
2179 x_alc_ledger_type_code VARCHAR2,
2180 x_le_ledger_type_code VARCHAR2,
2181 x_bal_seg_value_option_code VARCHAR2,
2182 x_bal_seg_column_name VARCHAR2,
2183 x_bal_seg_value_set_id NUMBER,
2184 x_mgt_seg_value_option_code VARCHAR2,
2185 x_mgt_seg_column_name VARCHAR2,
2186 x_mgt_seg_value_set_id NUMBER,
2187 x_description VARCHAR2,
2188 x_future_enterable_periods_lmt NUMBER,
2189 x_latest_opened_period_name VARCHAR2,
2190 x_latest_encumbrance_year NUMBER,
2191 x_cum_trans_ccid NUMBER,
2192 x_res_encumb_ccid NUMBER,
2193 x_net_income_ccid NUMBER,
2194 x_balancing_segment VARCHAR2,
2195 x_rounding_ccid NUMBER,
2196 x_transaction_calendar_id NUMBER,
2197 x_transaction_calendar_name VARCHAR2,
2198 x_daily_translation_rate_type VARCHAR2,
2199 x_daily_user_translation_type VARCHAR2,
2200 x_period_average_rate_type VARCHAR2,
2201 x_period_avg_user_rate_type VARCHAR2,
2202 x_period_end_rate_type VARCHAR2,
2203 x_period_end_user_rate_type VARCHAR2,
2204 x_context VARCHAR2,
2205 x_attribute1 VARCHAR2,
2206 x_attribute2 VARCHAR2,
2207 x_attribute3 VARCHAR2,
2208 x_attribute4 VARCHAR2,
2209 x_attribute5 VARCHAR2,
2210 x_attribute6 VARCHAR2,
2211 x_attribute7 VARCHAR2,
2212 x_attribute8 VARCHAR2,
2213 x_attribute9 VARCHAR2,
2214 x_attribute10 VARCHAR2,
2215 x_attribute11 VARCHAR2,
2216 x_attribute12 VARCHAR2,
2217 x_attribute13 VARCHAR2,
2218 x_attribute14 VARCHAR2,
2219 x_attribute15 VARCHAR2) IS
2220 CURSOR c IS
2221 SELECT *
2222 FROM gl_ledgers
2223 WHERE ROWID = x_rowid
2224 FOR UPDATE OF NAME NOWAIT;
2225
2226 recinfo c%ROWTYPE;
2227 BEGIN
2228 OPEN c;
2229
2230 FETCH c
2231 INTO recinfo;
2232
2233 IF (c%NOTFOUND) THEN
2234 CLOSE c;
2235
2236 RAISE NO_DATA_FOUND;
2237 END IF;
2238
2239 CLOSE c;
2240
2241 IF ( ( (recinfo.ledger_id = x_ledger_id)
2242 OR ((recinfo.ledger_id IS NULL) AND(x_ledger_id IS NULL)))
2243 AND ( (recinfo.NAME = x_name)
2244 OR ((recinfo.NAME IS NULL) AND(x_name IS NULL)))
2245 AND ( (recinfo.short_name = x_short_name)
2246 OR ((recinfo.short_name IS NULL) AND(x_short_name IS NULL)))
2247 AND ( (recinfo.chart_of_accounts_id = x_chart_of_accounts_id)
2248 OR ( (recinfo.chart_of_accounts_id IS NULL)
2249 AND (x_chart_of_accounts_id IS NULL)))
2250 AND ( (recinfo.currency_code = x_currency_code)
2251 OR ( (recinfo.currency_code IS NULL)
2252 AND (x_currency_code IS NULL)))
2253 AND ( (recinfo.period_set_name = x_period_set_name)
2254 OR ( (recinfo.period_set_name IS NULL)
2255 AND (x_period_set_name IS NULL)))
2256 AND ( (recinfo.accounted_period_type = x_accounted_period_type)
2257 OR ( (recinfo.accounted_period_type IS NULL)
2258 AND (x_accounted_period_type IS NULL)))
2259 AND ( (recinfo.first_ledger_period_name =
2260 x_first_ledger_period_name)
2261 OR ( (recinfo.first_ledger_period_name IS NULL)
2262 AND (x_first_ledger_period_name IS NULL)))
2263 AND ( (recinfo.ret_earn_code_combination_id =
2264 x_ret_earn_code_combination_id)
2265 OR ( (recinfo.ret_earn_code_combination_id IS NULL)
2266 AND (x_ret_earn_code_combination_id IS NULL)))
2267 AND ( (recinfo.suspense_allowed_flag = x_suspense_allowed_flag)
2268 OR ( (recinfo.suspense_allowed_flag IS NULL)
2269 AND (x_suspense_allowed_flag IS NULL)))
2270 AND ( (recinfo.allow_intercompany_post_flag =
2271 x_allow_intercompany_post_flag)
2272 OR ( (recinfo.allow_intercompany_post_flag IS NULL)
2273 AND (x_allow_intercompany_post_flag IS NULL)))
2274 AND ( (recinfo.enable_average_balances_flag = x_enable_avgbal_flag)
2275 OR ( (recinfo.enable_average_balances_flag IS NULL)
2276 AND (x_enable_avgbal_flag IS NULL)))
2277 AND ( (recinfo.enable_budgetary_control_flag =
2278 x_enable_budgetary_control_f)
2279 OR ( (recinfo.enable_budgetary_control_flag IS NULL)
2280 AND (x_enable_budgetary_control_f IS NULL)))
2281 AND ( (recinfo.require_budget_journals_flag =
2282 x_require_budget_journals_flag)
2283 OR ( (recinfo.require_budget_journals_flag IS NULL)
2284 AND (x_require_budget_journals_flag IS NULL)))
2285 AND ( (recinfo.enable_je_approval_flag = x_enable_je_approval_flag)
2286 OR ( (recinfo.enable_je_approval_flag IS NULL)
2287 AND (x_enable_je_approval_flag IS NULL)))
2288 AND ( (recinfo.enable_automatic_tax_flag =
2289 x_enable_automatic_tax_flag)
2290 OR ( (recinfo.enable_automatic_tax_flag IS NULL)
2291 AND (x_enable_automatic_tax_flag IS NULL)))
2292 AND ( (recinfo.consolidation_ledger_flag =
2293 x_consolidation_ledger_flag)
2294 OR ( (recinfo.consolidation_ledger_flag IS NULL)
2295 AND (x_consolidation_ledger_flag IS NULL)))
2296 AND ( (recinfo.translate_eod_flag = x_translate_eod_flag)
2297 OR ( (recinfo.translate_eod_flag IS NULL)
2298 AND (x_translate_eod_flag IS NULL)))
2299 AND ( (recinfo.translate_qatd_flag = x_translate_qatd_flag)
2300 OR ( (recinfo.translate_qatd_flag IS NULL)
2301 AND (x_translate_qatd_flag IS NULL)))
2302 AND ( (recinfo.translate_yatd_flag = x_translate_yatd_flag)
2303 OR ( (recinfo.translate_yatd_flag IS NULL)
2304 AND (x_translate_yatd_flag IS NULL)))
2305 AND ( (recinfo.automatically_created_flag =
2306 x_automatically_created_flag)
2307 OR ( (recinfo.automatically_created_flag IS NULL)
2308 AND (x_automatically_created_flag IS NULL)))
2309 AND ( (recinfo.track_rounding_imbalance_flag =
2310 x_track_rounding_imbalance_f)
2311 OR ( (recinfo.track_rounding_imbalance_flag IS NULL)
2312 AND (x_track_rounding_imbalance_f IS NULL)))
2313 AND ( (recinfo.alc_ledger_type_code = x_alc_ledger_type_code)
2314 OR ( (recinfo.alc_ledger_type_code IS NULL)
2315 AND (x_alc_ledger_type_code IS NULL)))
2316 AND ( (recinfo.le_ledger_type_code = x_le_ledger_type_code)
2317 OR ( (recinfo.le_ledger_type_code IS NULL)
2318 AND (x_le_ledger_type_code IS NULL)))
2319 AND ( (recinfo.bal_seg_value_option_code =
2320 x_bal_seg_value_option_code)
2321 OR ( (recinfo.bal_seg_value_option_code IS NULL)
2322 AND (x_bal_seg_value_option_code IS NULL)))
2323 AND ( (recinfo.bal_seg_column_name = x_bal_seg_column_name)
2324 OR ( (recinfo.bal_seg_column_name IS NULL)
2325 AND (x_bal_seg_column_name IS NULL)))
2326 AND ( (recinfo.bal_seg_value_set_id = x_bal_seg_value_set_id)
2327 OR ( (recinfo.bal_seg_value_set_id IS NULL)
2328 AND (x_bal_seg_value_set_id IS NULL)))
2329 AND ( (recinfo.mgt_seg_value_option_code =
2330 x_mgt_seg_value_option_code)
2331 OR ( (recinfo.mgt_seg_value_option_code IS NULL)
2332 AND (x_mgt_seg_value_option_code IS NULL)))
2333 AND ( (recinfo.mgt_seg_column_name = x_mgt_seg_column_name)
2334 OR ( (recinfo.mgt_seg_column_name IS NULL)
2335 AND (x_mgt_seg_column_name IS NULL)))
2336 AND ( (recinfo.mgt_seg_value_set_id = x_mgt_seg_value_set_id)
2337 OR ( (recinfo.mgt_seg_value_set_id IS NULL)
2338 AND (x_mgt_seg_value_set_id IS NULL)))
2339 AND ( (recinfo.description = x_description)
2340 OR ((recinfo.description IS NULL) AND(x_description IS NULL)))
2341 AND ( (recinfo.future_enterable_periods_limit =
2342 x_future_enterable_periods_lmt)
2343 OR ( (recinfo.future_enterable_periods_limit IS NULL)
2344 AND (x_future_enterable_periods_lmt IS NULL)))
2345 AND ( (recinfo.latest_opened_period_name =
2346 x_latest_opened_period_name)
2347 OR ( (recinfo.latest_opened_period_name IS NULL)
2348 AND (x_latest_opened_period_name IS NULL)))
2349 AND ( (recinfo.latest_encumbrance_year = x_latest_encumbrance_year)
2350 OR ( (recinfo.latest_encumbrance_year IS NULL)
2351 AND (x_latest_encumbrance_year IS NULL)))
2352 AND ( (recinfo.cum_trans_code_combination_id = x_cum_trans_ccid)
2353 OR ( (recinfo.cum_trans_code_combination_id IS NULL)
2354 AND (x_cum_trans_ccid IS NULL)))
2355 AND ( (recinfo.res_encumb_code_combination_id = x_res_encumb_ccid)
2356 OR ( (recinfo.res_encumb_code_combination_id IS NULL)
2357 AND (x_res_encumb_ccid IS NULL)))
2358 AND ( (recinfo.net_income_code_combination_id = x_net_income_ccid)
2359 OR ( (recinfo.net_income_code_combination_id IS NULL)
2360 AND (x_net_income_ccid IS NULL)))
2361 AND ( (recinfo.rounding_code_combination_id = x_rounding_ccid)
2362 OR ( (recinfo.rounding_code_combination_id IS NULL)
2363 AND (x_rounding_ccid IS NULL)))
2364 AND ( (recinfo.transaction_calendar_id = x_transaction_calendar_id)
2365 OR ( (recinfo.transaction_calendar_id IS NULL)
2366 AND (x_transaction_calendar_id IS NULL)))
2367 AND ( (recinfo.daily_translation_rate_type =
2368 x_daily_translation_rate_type)
2369 OR ( (recinfo.daily_translation_rate_type IS NULL)
2370 AND (x_daily_translation_rate_type IS NULL)))
2371 AND ( (recinfo.period_average_rate_type =
2372 x_period_average_rate_type)
2373 OR ( (recinfo.period_average_rate_type IS NULL)
2374 AND (x_period_average_rate_type IS NULL)))
2375 AND ( (recinfo.period_end_rate_type = x_period_end_rate_type)
2376 OR ( (recinfo.period_end_rate_type IS NULL)
2377 AND (x_period_end_rate_type IS NULL)))
2378 AND ( (recinfo.CONTEXT = x_context)
2379 OR ((recinfo.CONTEXT IS NULL) AND(x_context IS NULL)))
2380 AND ( (recinfo.attribute1 = x_attribute1)
2381 OR ((recinfo.attribute1 IS NULL) AND(x_attribute1 IS NULL)))
2382 AND ( (recinfo.attribute2 = x_attribute2)
2383 OR ((recinfo.attribute2 IS NULL) AND(x_attribute2 IS NULL)))
2384 AND ( (recinfo.attribute3 = x_attribute3)
2385 OR ((recinfo.attribute3 IS NULL) AND(x_attribute3 IS NULL)))
2386 AND ( (recinfo.attribute4 = x_attribute4)
2387 OR ((recinfo.attribute4 IS NULL) AND(x_attribute4 IS NULL)))
2388 AND ( (recinfo.attribute5 = x_attribute5)
2389 OR ((recinfo.attribute5 IS NULL) AND(x_attribute5 IS NULL)))
2390 AND ( (recinfo.attribute6 = x_attribute6)
2391 OR ((recinfo.attribute6 IS NULL) AND(x_attribute6 IS NULL)))
2392 AND ( (recinfo.attribute7 = x_attribute7)
2393 OR ((recinfo.attribute7 IS NULL) AND(x_attribute7 IS NULL)))
2394 AND ( (recinfo.attribute8 = x_attribute8)
2395 OR ((recinfo.attribute8 IS NULL) AND(x_attribute8 IS NULL)))
2396 AND ( (recinfo.attribute9 = x_attribute9)
2397 OR ((recinfo.attribute9 IS NULL) AND(x_attribute9 IS NULL)))
2398 AND ( (recinfo.attribute10 = x_attribute10)
2399 OR ((recinfo.attribute10 IS NULL) AND(x_attribute10 IS NULL)))
2400 AND ( (recinfo.attribute11 = x_attribute11)
2401 OR ((recinfo.attribute11 IS NULL) AND(x_attribute11 IS NULL)))
2402 AND ( (recinfo.attribute12 = x_attribute12)
2403 OR ((recinfo.attribute12 IS NULL) AND(x_attribute12 IS NULL)))
2404 AND ( (recinfo.attribute13 = x_attribute13)
2405 OR ((recinfo.attribute13 IS NULL) AND(x_attribute13 IS NULL)))
2406 AND ( (recinfo.attribute14 = x_attribute14)
2407 OR ((recinfo.attribute14 IS NULL) AND(x_attribute14 IS NULL)))
2408 AND ( (recinfo.attribute15 = x_attribute15)
2409 OR ((recinfo.attribute15 IS NULL) AND(x_attribute15 IS NULL)))) THEN
2410 RETURN;
2411 ELSE
2412 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2413 app_exception.raise_exception;
2414 END IF;
2415 END lock_row;
2416
2417 -- **********************************************************************
2418 -- This Update_Row is used by the Ledger Form; Will be removed after the
2419 -- form is removed.
2420 PROCEDURE update_row(
2421 x_rowid VARCHAR2,
2422 x_ledger_id NUMBER,
2423 x_name VARCHAR2,
2424 x_short_name VARCHAR2,
2425 x_chart_of_accounts_id NUMBER,
2426 x_chart_of_accounts_name VARCHAR2,
2427 x_currency_code VARCHAR2,
2428 x_period_set_name VARCHAR2,
2429 x_user_period_type VARCHAR2,
2430 x_accounted_period_type VARCHAR2,
2431 x_first_ledger_period_name VARCHAR2,
2432 x_ret_earn_code_combination_id NUMBER,
2433 x_suspense_allowed_flag VARCHAR2,
2434 x_suspense_ccid NUMBER,
2435 x_allow_intercompany_post_flag VARCHAR2,
2436 x_enable_avgbal_flag VARCHAR2,
2437 x_enable_budgetary_control_f VARCHAR2,
2438 x_require_budget_journals_flag VARCHAR2,
2439 x_enable_je_approval_flag VARCHAR2,
2440 x_enable_automatic_tax_flag VARCHAR2,
2441 x_consolidation_ledger_flag VARCHAR2,
2442 x_translate_eod_flag VARCHAR2,
2443 x_translate_qatd_flag VARCHAR2,
2444 x_translate_yatd_flag VARCHAR2,
2445 x_automatically_created_flag VARCHAR2,
2446 x_track_rounding_imbalance_f VARCHAR2,
2447 x_alc_ledger_type_code VARCHAR2,
2448 x_le_ledger_type_code VARCHAR2,
2449 x_bal_seg_value_option_code VARCHAR2,
2450 x_bal_seg_column_name VARCHAR2,
2451 x_bal_seg_value_set_id NUMBER,
2452 x_mgt_seg_value_option_code VARCHAR2,
2453 x_mgt_seg_column_name VARCHAR2,
2454 x_mgt_seg_value_set_id NUMBER,
2455 x_implicit_access_set_id NUMBER,
2456 x_last_update_date DATE,
2457 x_last_updated_by NUMBER,
2458 x_last_update_login NUMBER,
2459 x_description VARCHAR2,
2460 x_future_enterable_periods_lmt NUMBER,
2461 x_latest_opened_period_name VARCHAR2,
2462 x_latest_encumbrance_year NUMBER,
2463 x_cum_trans_ccid NUMBER,
2464 x_res_encumb_ccid NUMBER,
2465 x_net_income_ccid NUMBER,
2466 x_balancing_segment VARCHAR2,
2467 x_rounding_ccid NUMBER,
2468 x_transaction_calendar_id NUMBER,
2469 x_transaction_calendar_name VARCHAR2,
2470 x_daily_translation_rate_type VARCHAR2,
2471 x_daily_user_translation_type VARCHAR2,
2472 x_period_average_rate_type VARCHAR2,
2473 x_period_avg_user_rate_type VARCHAR2,
2474 x_period_end_rate_type VARCHAR2,
2475 x_period_end_user_rate_type VARCHAR2,
2476 x_context VARCHAR2,
2477 x_attribute1 VARCHAR2,
2478 x_attribute2 VARCHAR2,
2479 x_attribute3 VARCHAR2,
2480 x_attribute4 VARCHAR2,
2481 x_attribute5 VARCHAR2,
2482 x_attribute6 VARCHAR2,
2483 x_attribute7 VARCHAR2,
2484 x_attribute8 VARCHAR2,
2485 x_attribute9 VARCHAR2,
2486 x_attribute10 VARCHAR2,
2487 x_attribute11 VARCHAR2,
2488 x_attribute12 VARCHAR2,
2489 x_attribute13 VARCHAR2,
2490 x_attribute14 VARCHAR2,
2491 x_attribute15 VARCHAR2,
2492 x_set_manual_flag VARCHAR2) IS
2493 BEGIN
2494 UPDATE gl_ledgers
2495 SET ledger_id = x_ledger_id,
2496 NAME = x_name,
2497 short_name = x_short_name,
2498 chart_of_accounts_id = x_chart_of_accounts_id,
2499 currency_code = x_currency_code,
2500 period_set_name = x_period_set_name,
2501 accounted_period_type = x_accounted_period_type,
2502 first_ledger_period_name = x_first_ledger_period_name,
2503 ret_earn_code_combination_id = x_ret_earn_code_combination_id,
2504 suspense_allowed_flag = x_suspense_allowed_flag,
2505 allow_intercompany_post_flag = x_allow_intercompany_post_flag,
2506 enable_average_balances_flag = x_enable_avgbal_flag,
2507 enable_budgetary_control_flag = x_enable_budgetary_control_f,
2508 require_budget_journals_flag = x_require_budget_journals_flag,
2509 enable_je_approval_flag = x_enable_je_approval_flag,
2510 enable_automatic_tax_flag = x_enable_automatic_tax_flag,
2511 consolidation_ledger_flag = x_consolidation_ledger_flag,
2512 translate_eod_flag = x_translate_eod_flag,
2513 translate_qatd_flag = x_translate_qatd_flag,
2514 translate_yatd_flag = x_translate_yatd_flag,
2515 automatically_created_flag = x_automatically_created_flag,
2516 track_rounding_imbalance_flag = x_track_rounding_imbalance_f,
2517 alc_ledger_type_code = x_alc_ledger_type_code,
2518 le_ledger_type_code = x_le_ledger_type_code,
2519 bal_seg_value_option_code = x_bal_seg_value_option_code,
2520 bal_seg_column_name = x_bal_seg_column_name,
2521 bal_seg_value_set_id = x_bal_seg_value_set_id,
2522 mgt_seg_value_option_code = x_mgt_seg_value_option_code,
2523 mgt_seg_column_name = x_mgt_seg_column_name,
2524 mgt_seg_value_set_id = x_mgt_seg_value_set_id,
2525 last_update_date = x_last_update_date,
2526 last_updated_by = x_last_updated_by,
2527 last_update_login = x_last_update_login,
2528 description = x_description,
2529 future_enterable_periods_limit = x_future_enterable_periods_lmt,
2530 latest_opened_period_name = x_latest_opened_period_name,
2531 latest_encumbrance_year = x_latest_encumbrance_year,
2532 cum_trans_code_combination_id = x_cum_trans_ccid,
2533 res_encumb_code_combination_id = x_res_encumb_ccid,
2534 net_income_code_combination_id = x_net_income_ccid,
2535 rounding_code_combination_id = x_rounding_ccid,
2536 transaction_calendar_id = x_transaction_calendar_id,
2537 daily_translation_rate_type = x_daily_translation_rate_type,
2538 period_average_rate_type = x_period_average_rate_type,
2539 period_end_rate_type = x_period_end_rate_type,
2540 CONTEXT = x_context,
2541 attribute1 = x_attribute1,
2542 attribute2 = x_attribute2,
2543 attribute3 = x_attribute3,
2544 attribute4 = x_attribute4,
2545 attribute5 = x_attribute5,
2546 attribute6 = x_attribute6,
2547 attribute7 = x_attribute7,
2548 attribute8 = x_attribute8,
2549 attribute9 = x_attribute9,
2550 attribute10 = x_attribute10,
2551 attribute11 = x_attribute11,
2552 attribute12 = x_attribute12,
2553 attribute13 = x_attribute13,
2554 attribute14 = x_attribute14,
2555 attribute15 = x_attribute15
2556 WHERE ROWID = x_rowid;
2557
2558 IF (SQL%NOTFOUND) THEN
2559 RAISE NO_DATA_FOUND;
2560 END IF;
2561
2562 -- Update the implicit access set name for the ledger
2563 gl_access_sets_pkg.update_implicit_access_set
2564 (x_access_set_id => x_implicit_access_set_id,
2565 x_name => x_name,
2566 x_last_update_date => x_last_update_date,
2567 x_last_updated_by => x_last_updated_by,
2568 x_last_update_login => x_last_update_login);
2569
2570 -- Check whether journal_approval_flag is to be set to Y for
2571 -- the Manual source.
2572 IF (x_set_manual_flag = 'Y') THEN
2573 gl_ledgers_pkg.enable_manual_je_approval;
2574 END IF;
2575
2576 gl_ledgers_pkg.led_update_other_tables(x_ledger_id, x_last_update_date,
2577 x_last_updated_by,
2578 x_suspense_ccid);
2579 END update_row;
2580
2581 -- **********************************************************************
2582 PROCEDURE select_row(
2583 recinfo IN OUT NOCOPY gl_ledgers%ROWTYPE) IS
2584 BEGIN
2585 SELECT *
2586 INTO recinfo
2587 FROM gl_ledgers
2588 WHERE ledger_id = recinfo.ledger_id;
2589 END select_row;
2590
2591 -- **********************************************************************
2592 PROCEDURE select_columns(
2593 x_ledger_id NUMBER,
2594 x_name IN OUT NOCOPY VARCHAR2) IS
2595 recinfo gl_ledgers%ROWTYPE;
2596 BEGIN
2597 recinfo.ledger_id := x_ledger_id;
2598 select_row(recinfo);
2599 x_name := recinfo.NAME;
2600 END select_columns;
2601
2602 -- **********************************************************************
2603 PROCEDURE update_gl_system_usages(
2604 cons_lgr_flag VARCHAR2) IS
2605 BEGIN
2606 UPDATE gl_system_usages
2607 SET average_balances_flag = 'Y',
2608 consolidation_ledger_flag =
2609 DECODE(cons_lgr_flag,
2610 'Y', 'Y',
2611 consolidation_ledger_flag)
2612 WHERE EXISTS(
2613 SELECT '1'
2614 FROM gl_system_usages
2615 WHERE average_balances_flag = 'N'
2616 OR ( (cons_lgr_flag = 'Y')
2617 AND consolidation_ledger_flag = 'N'));
2618 END update_gl_system_usages;
2619
2620 -- **********************************************************************
2621 PROCEDURE insert_gl_net_income_accounts(
2622 x_ledger_id NUMBER,
2623 x_balancing_segment VARCHAR2,
2624 x_net_income_ccid NUMBER,
2625 x_creation_date DATE,
2626 x_created_by NUMBER,
2627 x_last_update_date DATE,
2628 x_last_updated_by NUMBER,
2629 x_last_update_login NUMBER,
2630 x_request_id NUMBER,
2631 x_program_application_id NUMBER,
2632 x_program_id NUMBER,
2633 x_program_update_date DATE) IS
2634 BEGIN
2635 INSERT INTO gl_net_income_accounts
2636 (ledger_id, bal_seg_value, code_combination_id,
2637 creation_date, created_by, last_update_date,
2638 last_updated_by, last_update_login, request_id,
2639 program_application_id, program_id,
2640 program_update_date)
2641 VALUES (x_ledger_id, x_balancing_segment, x_net_income_ccid,
2642 x_creation_date, x_created_by, x_last_update_date,
2643 x_last_updated_by, x_last_update_login, x_request_id,
2644 x_program_application_id, x_program_id,
2645 x_program_update_date);
2646 END insert_gl_net_income_accounts;
2647
2648 -- **********************************************************************
2649 PROCEDURE led_update_other_tables(
2650 x_ledger_id NUMBER,
2651 x_last_update_date DATE,
2652 x_last_updated_by NUMBER,
2653 x_suspense_ccid NUMBER) IS
2654 FOUND BOOLEAN;
2655 BEGIN
2656 -- Update the existing rows in gl_suspense_accounts table for
2657 -- the corresponding ledger. If they are not exist create
2658 -- the new default suspense accounts into gl_suspense_accounts
2659 -- for the corresponding ledger.
2660 FOUND := gl_suspense_accounts_pkg.is_ledger_suspense_exist(x_ledger_id);
2661
2662 IF (FOUND) THEN
2663 -- Update rows in gl_suspense_accounts table for the
2664 -- corresponding ledger.
2665 -- This statement is executed no matter X_suspense_CCID
2666 -- is NULL or not, since the function checks for NULL and
2667 -- delete the appropriate lines when CCID is NULL.
2668 gl_suspense_accounts_pkg.update_ledger_suspense(x_ledger_id,
2669 x_suspense_ccid,
2670 x_last_update_date,
2671 x_last_updated_by);
2672 ELSE
2673 -- Create rows in gl_suspense_accounts table for the
2674 -- corresponding new created ledger.
2675 IF (x_suspense_ccid IS NOT NULL) THEN
2676 gl_suspense_accounts_pkg.insert_ledger_suspense
2677 (x_ledger_id,
2678 x_suspense_ccid,
2679 x_last_update_date,
2680 x_last_updated_by);
2681 END IF;
2682 END IF;
2683 END led_update_other_tables;
2684
2685 -- **********************************************************************
2686 FUNCTION check_avg_translation(
2687 x_ledger_id NUMBER)
2688 RETURN BOOLEAN IS
2689 CURSOR check_avg_translation IS
2690 SELECT 'avg translated'
2691 FROM DUAL
2692 WHERE EXISTS(
2693 SELECT 'X'
2694 FROM gl_translation_tracking
2695 WHERE ledger_id = x_ledger_id
2696 AND average_translation_flag = 'Y'
2697 AND earliest_ever_period_name <>
2698 earliest_never_period_name);
2699
2700 dummy VARCHAR2(100);
2701 BEGIN
2702 OPEN check_avg_translation;
2703
2704 FETCH check_avg_translation
2705 INTO dummy;
2706
2707 IF check_avg_translation%FOUND THEN
2708 CLOSE check_avg_translation;
2709
2710 RETURN(TRUE);
2711 ELSE
2712 CLOSE check_avg_translation;
2713
2714 RETURN(FALSE);
2715 END IF;
2716 END check_avg_translation;
2717
2718 -- **********************************************************************
2719 PROCEDURE enable_manual_je_approval IS
2720 BEGIN
2721 -- Set the journal_approval_flag column for Manual source.
2722 UPDATE gl_je_sources
2723 SET journal_approval_flag = 'Y'
2724 WHERE je_source_name = 'Manual';
2725 END enable_manual_je_approval;
2726
2727 -- **********************************************************************
2728 PROCEDURE insert_set(
2729 x_rowid IN OUT NOCOPY VARCHAR2,
2730 x_access_set_id IN OUT NOCOPY NUMBER,
2731 x_ledger_id NUMBER,
2732 x_name VARCHAR2,
2733 x_short_name VARCHAR2,
2734 x_chart_of_accounts_id NUMBER,
2735 x_period_set_name VARCHAR2,
2736 x_accounted_period_type VARCHAR2,
2737 x_default_ledger_id NUMBER,
2738 x_last_update_date DATE,
2739 x_last_updated_by NUMBER,
2740 x_creation_date DATE,
2741 x_created_by NUMBER,
2742 x_last_update_login NUMBER,
2743 x_description VARCHAR2,
2744 x_context VARCHAR2,
2745 x_attribute1 VARCHAR2,
2746 x_attribute2 VARCHAR2,
2747 x_attribute3 VARCHAR2,
2748 x_attribute4 VARCHAR2,
2749 x_attribute5 VARCHAR2,
2750 x_attribute6 VARCHAR2,
2751 x_attribute7 VARCHAR2,
2752 x_attribute8 VARCHAR2,
2753 x_attribute9 VARCHAR2,
2754 x_attribute10 VARCHAR2,
2755 x_attribute11 VARCHAR2,
2756 x_attribute12 VARCHAR2,
2757 x_attribute13 VARCHAR2,
2758 x_attribute14 VARCHAR2,
2759 x_attribute15 VARCHAR2) IS
2760 l_bal_seg_column_name VARCHAR2(25);
2761 l_mgt_seg_column_name VARCHAR2(25);
2762 l_bal_seg_value_set_id NUMBER(10);
2763 l_mgt_seg_value_set_id NUMBER(10);
2764 l_chart_of_accounts_id NUMBER(15);
2765
2766 CURSOR c IS
2767 SELECT ROWID
2768 FROM gl_ledgers
2769 WHERE NAME = x_name;
2770 BEGIN
2771 l_chart_of_accounts_id := x_chart_of_accounts_id;
2772 gl_ledgers_pkg.get_bal_mgt_seg_info(l_bal_seg_column_name,
2773 l_bal_seg_value_set_id,
2774 l_mgt_seg_column_name,
2775 l_mgt_seg_value_set_id,
2776 l_chart_of_accounts_id);
2777 -- Create an implicit access set header
2778 x_access_set_id :=
2779 gl_access_sets_pkg.create_implicit_access_set
2780 (x_name => x_name, x_security_segment_code => 'F',
2781 x_chart_of_accounts_id => x_chart_of_accounts_id,
2782 x_period_set_name => x_period_set_name,
2783 x_accounted_period_type => x_accounted_period_type,
2784 x_default_ledger_id => x_default_ledger_id,
2785 x_secured_seg_value_set_id => NULL,
2786 x_last_updated_by => x_last_updated_by,
2787 x_last_update_login => x_last_update_login,
2788 x_creation_date => x_creation_date,
2789 x_description => x_description);
2790
2791 INSERT INTO gl_ledgers
2792 (ledger_id, NAME, short_name,
2793 chart_of_accounts_id, currency_code, period_set_name,
2794 accounted_period_type, first_ledger_period_name,
2795 ret_earn_code_combination_id, suspense_allowed_flag,
2796 allow_intercompany_post_flag,
2797 track_rounding_imbalance_flag,
2798 enable_average_balances_flag,
2799 enable_budgetary_control_flag,
2800 require_budget_journals_flag, enable_je_approval_flag,
2801 enable_automatic_tax_flag, consolidation_ledger_flag,
2802 translate_eod_flag, translate_qatd_flag,
2803 translate_yatd_flag, automatically_created_flag,
2804 alc_ledger_type_code, ledger_category_code,
2805 object_type_code, le_ledger_type_code,
2806 bal_seg_value_option_code, bal_seg_column_name,
2807 mgt_seg_value_option_code, mgt_seg_column_name,
2808 bal_seg_value_set_id, mgt_seg_value_set_id,
2809 implicit_access_set_id, future_enterable_periods_limit,
2810 ledger_attributes, enable_reconciliation_flag,
2811 last_update_date, last_updated_by,
2812 creation_date, created_by, last_update_login,
2813 description, CONTEXT, attribute1, attribute2,
2814 attribute3, attribute4, attribute5, attribute6,
2815 attribute7, attribute8, attribute9, attribute10,
2816 attribute11, attribute12, attribute13,
2817 attribute14, attribute15, create_je_flag)
2818 VALUES (x_ledger_id, x_name, x_short_name,
2819 x_chart_of_accounts_id, 'X', -- currency_code
2820 x_period_set_name,
2821 x_accounted_period_type, 'X', -- first_ledger_period_name
2822 -1, -- ret_earn_code_combination_id
2823 'N', -- suspense_allowed_flag
2824 'N', -- allow_intercompany_post_flag
2825 'N', -- track_rounding_imbalance_flag
2826 'N', -- enable_average_balances_flag
2827 'N', -- enable_budgetary_control_flag
2828 'N', -- require_budget_journals_flag
2829 'N', -- enable_je_approval_flag
2830 'N', -- enable_automatic_tax_flag
2831 'N', -- consolidation_ledger_flag
2832 'N', -- translate_eod_flag
2833 'N', -- translate_qatd_flag
2834 'N', -- translate_yatd_flag
2835 'N', -- automatically_created_flag
2836 'NONE', -- alc_ledger_type_code
2837 'NONE', -- ledger_category_code
2838 'S', -- object_type_code
2839 'U', -- le_ledger_type_code
2840 'I', -- bal_seg_value_option_code
2841 l_bal_seg_column_name,
2842 'I', -- mgt_seg_value_option_code
2843 l_mgt_seg_column_name,
2844 l_bal_seg_value_set_id, l_mgt_seg_value_set_id,
2845 x_access_set_id, 0, -- future_enterable_periods_limit
2846 'S', 'N', -- ledger_attributes, enable_reconciliation_flag
2847 x_last_update_date, x_last_updated_by,
2848 x_creation_date, x_created_by, x_last_update_login,
2849 x_description, x_context, x_attribute1, x_attribute2,
2850 x_attribute3, x_attribute4, x_attribute5, x_attribute6,
2851 x_attribute7, x_attribute8, x_attribute9, x_attribute10,
2852 x_attribute11, x_attribute12, x_attribute13,
2853 x_attribute14, x_attribute15,
2854 'N'); -- create_je_flag
2855
2856 OPEN c;
2857
2858 FETCH c
2859 INTO x_rowid;
2860
2861 IF (c%NOTFOUND) THEN
2862 CLOSE c;
2863
2864 RAISE NO_DATA_FOUND;
2865 END IF;
2866
2867 CLOSE c;
2868 END insert_set;
2869
2870 -- **********************************************************************
2871 PROCEDURE lock_set(
2872 x_rowid VARCHAR2,
2873 x_ledger_id NUMBER,
2874 x_name VARCHAR2,
2875 x_short_name VARCHAR2,
2876 x_chart_of_accounts_id NUMBER,
2877 x_period_set_name VARCHAR2,
2878 x_accounted_period_type VARCHAR2,
2879 x_description VARCHAR2,
2880 x_context VARCHAR2,
2881 x_attribute1 VARCHAR2,
2882 x_attribute2 VARCHAR2,
2883 x_attribute3 VARCHAR2,
2884 x_attribute4 VARCHAR2,
2885 x_attribute5 VARCHAR2,
2886 x_attribute6 VARCHAR2,
2887 x_attribute7 VARCHAR2,
2888 x_attribute8 VARCHAR2,
2889 x_attribute9 VARCHAR2,
2890 x_attribute10 VARCHAR2,
2891 x_attribute11 VARCHAR2,
2892 x_attribute12 VARCHAR2,
2893 x_attribute13 VARCHAR2,
2894 x_attribute14 VARCHAR2,
2895 x_attribute15 VARCHAR2) IS
2896 CURSOR c IS
2897 SELECT *
2898 FROM gl_ledgers
2899 WHERE ROWID = x_rowid
2900 FOR UPDATE OF NAME NOWAIT;
2901
2902 recinfo c%ROWTYPE;
2903 BEGIN
2904 OPEN c;
2905
2906 FETCH c
2907 INTO recinfo;
2908
2909 IF (c%NOTFOUND) THEN
2910 CLOSE c;
2911
2912 RAISE NO_DATA_FOUND;
2913 END IF;
2914
2915 CLOSE c;
2916
2917 IF ( ( (recinfo.ledger_id = x_ledger_id)
2918 OR ((recinfo.ledger_id IS NULL) AND(x_ledger_id IS NULL)))
2919 AND ( (recinfo.NAME = x_name)
2920 OR ((recinfo.NAME IS NULL) AND(x_name IS NULL)))
2921 AND ( (recinfo.short_name = x_short_name)
2922 OR ((recinfo.short_name IS NULL) AND(x_short_name IS NULL)))
2923 AND ( (recinfo.chart_of_accounts_id = x_chart_of_accounts_id)
2924 OR ( (recinfo.chart_of_accounts_id IS NULL)
2925 AND (x_chart_of_accounts_id IS NULL)))
2926 AND ( (recinfo.period_set_name = x_period_set_name)
2927 OR ( (recinfo.period_set_name IS NULL)
2928 AND (x_period_set_name IS NULL)))
2929 AND ( (recinfo.accounted_period_type = x_accounted_period_type)
2930 OR ( (recinfo.accounted_period_type IS NULL)
2931 AND (x_accounted_period_type IS NULL)))
2932 AND ( (recinfo.description = x_description)
2933 OR ((recinfo.description IS NULL) AND(x_description IS NULL)))
2934 AND ( (recinfo.CONTEXT = x_context)
2935 OR ((recinfo.CONTEXT IS NULL) AND(x_context IS NULL)))
2936 AND ( (recinfo.attribute1 = x_attribute1)
2937 OR ((recinfo.attribute1 IS NULL) AND(x_attribute1 IS NULL)))
2938 AND ( (recinfo.attribute2 = x_attribute2)
2939 OR ((recinfo.attribute2 IS NULL) AND(x_attribute2 IS NULL)))
2940 AND ( (recinfo.attribute3 = x_attribute3)
2941 OR ((recinfo.attribute3 IS NULL) AND(x_attribute3 IS NULL)))
2942 AND ( (recinfo.attribute4 = x_attribute4)
2943 OR ((recinfo.attribute4 IS NULL) AND(x_attribute4 IS NULL)))
2944 AND ( (recinfo.attribute5 = x_attribute5)
2945 OR ((recinfo.attribute5 IS NULL) AND(x_attribute5 IS NULL)))
2946 AND ( (recinfo.attribute6 = x_attribute6)
2947 OR ((recinfo.attribute6 IS NULL) AND(x_attribute6 IS NULL)))
2948 AND ( (recinfo.attribute7 = x_attribute7)
2949 OR ((recinfo.attribute7 IS NULL) AND(x_attribute7 IS NULL)))
2950 AND ( (recinfo.attribute8 = x_attribute8)
2951 OR ((recinfo.attribute8 IS NULL) AND(x_attribute8 IS NULL)))
2952 AND ( (recinfo.attribute9 = x_attribute9)
2953 OR ((recinfo.attribute9 IS NULL) AND(x_attribute9 IS NULL)))
2954 AND ( (recinfo.attribute10 = x_attribute10)
2955 OR ((recinfo.attribute10 IS NULL) AND(x_attribute10 IS NULL)))
2956 AND ( (recinfo.attribute11 = x_attribute11)
2957 OR ((recinfo.attribute11 IS NULL) AND(x_attribute11 IS NULL)))
2958 AND ( (recinfo.attribute12 = x_attribute12)
2959 OR ((recinfo.attribute12 IS NULL) AND(x_attribute12 IS NULL)))
2960 AND ( (recinfo.attribute13 = x_attribute13)
2961 OR ((recinfo.attribute13 IS NULL) AND(x_attribute13 IS NULL)))
2962 AND ( (recinfo.attribute14 = x_attribute14)
2963 OR ((recinfo.attribute14 IS NULL) AND(x_attribute14 IS NULL)))
2964 AND ( (recinfo.attribute15 = x_attribute15)
2965 OR ((recinfo.attribute15 IS NULL) AND(x_attribute15 IS NULL)))) THEN
2966 RETURN;
2967 ELSE
2968 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2969 app_exception.raise_exception;
2970 END IF;
2971 END lock_set;
2972
2973 -- **********************************************************************
2974 PROCEDURE update_set(
2975 x_rowid VARCHAR2,
2976 x_access_set_id NUMBER,
2977 x_ledger_id NUMBER,
2978 x_name VARCHAR2,
2979 x_short_name VARCHAR2,
2980 x_chart_of_accounts_id NUMBER,
2981 x_period_set_name VARCHAR2,
2982 x_accounted_period_type VARCHAR2,
2983 x_default_ledger_id NUMBER,
2984 x_last_update_date DATE,
2985 x_last_updated_by NUMBER,
2986 x_last_update_login NUMBER,
2987 x_description VARCHAR2,
2988 x_context VARCHAR2,
2989 x_attribute1 VARCHAR2,
2990 x_attribute2 VARCHAR2,
2991 x_attribute3 VARCHAR2,
2992 x_attribute4 VARCHAR2,
2993 x_attribute5 VARCHAR2,
2994 x_attribute6 VARCHAR2,
2995 x_attribute7 VARCHAR2,
2996 x_attribute8 VARCHAR2,
2997 x_attribute9 VARCHAR2,
2998 x_attribute10 VARCHAR2,
2999 x_attribute11 VARCHAR2,
3000 x_attribute12 VARCHAR2,
3001 x_attribute13 VARCHAR2,
3002 x_attribute14 VARCHAR2,
3003 x_attribute15 VARCHAR2) IS
3004 BEGIN
3005 UPDATE gl_ledgers
3006 SET ledger_id = x_ledger_id,
3007 NAME = x_name,
3008 short_name = x_short_name,
3009 chart_of_accounts_id = x_chart_of_accounts_id,
3010 period_set_name = x_period_set_name,
3011 accounted_period_type = x_accounted_period_type,
3012 last_update_date = x_last_update_date,
3013 last_updated_by = x_last_updated_by,
3014 last_update_login = x_last_update_login,
3015 description = x_description,
3016 CONTEXT = x_context,
3017 attribute1 = x_attribute1,
3018 attribute2 = x_attribute2,
3019 attribute3 = x_attribute3,
3020 attribute4 = x_attribute4,
3021 attribute5 = x_attribute5,
3022 attribute6 = x_attribute6,
3023 attribute7 = x_attribute7,
3024 attribute8 = x_attribute8,
3025 attribute9 = x_attribute9,
3026 attribute10 = x_attribute10,
3027 attribute11 = x_attribute11,
3028 attribute12 = x_attribute12,
3029 attribute13 = x_attribute13,
3030 attribute14 = x_attribute14,
3031 attribute15 = x_attribute15
3032 WHERE ROWID = x_rowid;
3033
3034 IF (SQL%NOTFOUND) THEN
3035 RAISE NO_DATA_FOUND;
3036 END IF;
3037
3038 -- Update the implicit access set name for the ledger
3039 gl_access_sets_pkg.update_implicit_access_set
3040 (x_access_set_id => x_access_set_id,
3041 x_name => x_name,
3042 x_last_update_date => x_last_update_date,
3043 x_last_updated_by => x_last_updated_by,
3044 x_last_update_login => x_last_update_login);
3045
3046 -- Only when updating ledger sets could the default ledger be changed.
3047 UPDATE gl_access_sets
3048 SET default_ledger_id = x_default_ledger_id
3049 WHERE access_set_id = x_access_set_id;
3050 END update_set;
3051
3052 -- **********************************************************************
3053 FUNCTION maintain_def_ledger_assign(
3054 x_ledger_set_id NUMBER,
3055 x_default_ledger_id NUMBER) RETURN BOOLEAN IS
3056
3057 CURSOR c_default_ledger_assign IS
3058 SELECT 1
3059 FROM GL_LEDGER_SET_NORM_ASSIGN
3060 WHERE ledger_set_id = x_ledger_set_id
3061 AND ledger_id = x_default_ledger_id
3062 AND (status_code <> 'D' OR status_code IS NULL)
3063 AND rownum < 2;
3064
3065 dumnum NUMBER;
3066 rowid VARCHAR2(30);
3067
3068 updated_by NUMBER;
3069 update_login NUMBER;
3070 update_date DATE;
3071 BEGIN
3072 OPEN c_default_ledger_assign;
3073 FETCH c_default_ledger_assign INTO dumnum;
3074 IF c_default_ledger_assign%FOUND THEN
3075 CLOSE c_default_ledger_assign;
3076 RETURN FALSE;
3077 END IF;
3078 CLOSE c_default_ledger_assign;
3079
3080 -- Insert default ledger assignment
3081 SELECT last_updated_by, last_update_login, last_update_date
3082 INTO updated_by, update_login, update_date
3083 FROM GL_LEDGERS
3084 WHERE ledger_id = x_ledger_set_id;
3085
3086 GL_LEDGER_SET_NORM_ASSIGN_PKG.Insert_Row(
3087 rowid,
3088 x_ledger_set_id,
3089 x_default_ledger_id,
3090 'L', -- object_type_code
3091 update_date, -- last_update_date
3092 updated_by, -- last_updated_by
3093 update_date, -- creation_date
3094 updated_by, -- created_by
3095 update_login, -- last_update_login
3096 NULL, -- start_date
3097 NULL, -- end_date
3098 NULL, -- context
3099 NULL, -- attribute1
3100 NULL, -- attribute2
3101 NULL, -- attribute3
3102 NULL, -- attribute4
3103 NULL, -- attribute5
3104 NULL, -- attribute6
3105 NULL, -- attribute7
3106 NULL, -- attribute8
3107 NULL, -- attribute9
3108 NULL, -- attribute10
3109 NULL, -- attribute11
3110 NULL, -- attribute12
3111 NULL, -- attribute13
3112 NULL, -- attribute14
3113 NULL, -- attribute15
3114 NULL); -- request_id
3115
3116 RETURN TRUE;
3117 END maintain_def_ledger_assign;
3118
3119 -- *********************************************************************
3120 PROCEDURE Get_CCID(Y_Chart_Of_Accounts_Id NUMBER,
3121 Y_Concat_Segments VARCHAR2,
3122 Y_Account_Code VARCHAR2,
3123 Y_Average_Balances_Flag VARCHAR2,
3124 Y_User_Id NUMBER,
3125 Y_Resp_Id NUMBER,
3126 Y_Resp_Appl_Id NUMBER,
3127 Y_CCID OUT NOCOPY NUMBER) IS
3128
3129 return_value BOOLEAN;
3130 rule VARCHAR2(1000);
3131 get_column VARCHAR2(30);
3132 where_clause VARCHAR2(30);
3133 message_name VARCHAR2(30);
3134
3135 BEGIN
3136 get_column := NULL;
3137 where_clause := 'SUMMARY_FLAG<>''Y''';
3138 message_name :=
3139 gl_public_sector.get_message_name('GL_RETAINED_EARNINGS_TITLE','SQLGL',NULL);
3140
3141
3142 IF (Y_Account_Code = 'RET_EARN') THEN
3143 rule := '\nSUMMARY_FLAG\nI\n' ||
3144 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3145 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
3146 '\nI\n' ||
3147 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY\0' ||
3148 'GL_ACCOUNT\nGL_ACCOUNT_TYPE' ||
3149 '\nE\n' ||
3150 'APPL=SQLGL;NAME=' || message_name || '\nE\0' ||
3151 'GL_ACCOUNT\nGL_ACCOUNT_TYPE' ||
3152 '\nE\n' ||
3153 'APPL=SQLGL;NAME=' || message_name || '\nR';
3154
3155 ELSIF (Y_Account_Code in('SUSPENSE','SLA_ENT_SUS','SLA_LDG_SUS')) THEN
3156 rule := '\nSUMMARY_FLAG\nI\n' ||
3157 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3158 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
3159 '\nI\n' ||
3160 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY';
3161
3162 ELSIF (Y_Account_Code = 'ROUNDING') THEN
3163 rule := '\nSUMMARY_FLAG\nI\n' ||
3164 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3165 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
3166 '\nI\n' ||
3167 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY';
3168
3169 ELSIF (Y_Account_Code = 'RES_ENCUMB') THEN
3170 rule := '\nSUMMARY_FLAG\nI\n' ||
3171 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3172 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
3173 '\nI\n' ||
3174 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY';
3175
3176 ELSIF (Y_Account_Code = 'CUM_TRANS') THEN
3177 IF (Y_Average_Balances_Flag = 'Y') THEN
3178 rule := '\nSUMMARY_FLAG\nI\n' ||
3179 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3180 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
3181 '\nI\n' ||
3182 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY\0' ||
3183 'GL_ACCOUNT\nGL_ACCOUNT_TYPE' ||
3184 '\nE\n' ||
3185 'APPL=SQLGL;NAME=GL_SOB_TRANSLATION_ACCOUNT\nE\0' ||
3186 'GL_ACCOUNT\nGL_ACCOUNT_TYPE' ||
3187 '\nE\n' ||
3188 'APPL=SQLGL;NAME=GL_SOB_TRANSLATION_ACCOUNT\nR';
3189 ELSE
3190 rule := '\nSUMMARY_FLAG\nI\n' ||
3191 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3192 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
3193 '\nI\n' ||
3194 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY';
3195 END IF;
3196
3197 ELSIF (Y_Account_Code = 'NET_INCOME') THEN
3198 rule := '\nSUMMARY_FLAG\nI\n' ||
3199 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
3200 'GL_ACCOUNT\nGL_ACCOUNT_TYPE' ||
3201 '\nE\n' ||
3202 'APPL=SQLGL;NAME=GL_SOB_REVENUE_EXPENSE\nE\0' ||
3203 'GL_ACCOUNT\nGL_ACCOUNT_TYPE' ||
3204 '\nE\n' ||
3205 'APPL=SQLGL;NAME=GL_SOB_REVENUE_EXPENSE\nR';
3206 get_column := 'DETAIL_POSTING_ALLOWED_FLAG';
3207
3208 END IF;
3209
3210 return_value := fnd_flex_keyval.validate_segs('CREATE_COMBINATION','SQLGL',
3211 'GL#', Y_Chart_Of_Accounts_Id, Y_Concat_Segments,
3212 'V', sysdate, 'ALL', NULL, rule, where_clause,
3213 get_column, FALSE, FALSE,
3214 Y_Resp_Appl_Id, Y_Resp_Id, Y_User_Id, NULL, NULL, NULL);
3215
3216 IF (return_value) THEN
3217 IF (Y_Account_Code = 'NET_INCOME') THEN
3218 IF (fnd_flex_keyval.column_value(1) <> 'N') THEN
3219 fnd_message.set_name('SQLGL', 'GL_NET_INCOME_COMBINATION');
3220 app_exception.raise_exception;
3221 ELSIF (fnd_flex_keyval.qualifier_value('DETAIL_POSTING_ALLOWED', 'D') <> 'N') THEN
3222 fnd_message.set_name('SQLGL', 'GL_NET_INCOME_SEGMENTS');
3223 app_exception.raise_exception;
3224 END IF;
3225 END IF;
3226 Y_CCID := fnd_flex_keyval.combination_id;
3227 ELSE
3228 Y_CCID := 0;
3229 END IF;
3230
3231 EXCEPTION
3232 WHEN OTHERS THEN
3233 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
3234 fnd_message.set_token('PROCEDURE', 'GL_LEDGERS_PKG.Get_CCID');
3235 RAISE;
3236 END Get_CCID;
3237
3238 -- *********************************************************************
3239 PROCEDURE insert_set(
3240 x_ledger_id NUMBER,
3241 x_name VARCHAR2,
3242 x_short_name VARCHAR2,
3243 x_chart_of_accounts_id NUMBER,
3244 x_period_set_name VARCHAR2,
3245 x_accounted_period_type VARCHAR2,
3246 x_default_ledger_id NUMBER,
3247 x_date DATE,
3248 x_user_id NUMBER,
3249 x_login_id NUMBER,
3250 x_description VARCHAR2,
3251 x_context VARCHAR2,
3252 x_attribute1 VARCHAR2,
3253 x_attribute2 VARCHAR2,
3254 x_attribute3 VARCHAR2,
3255 x_attribute4 VARCHAR2,
3256 x_attribute5 VARCHAR2,
3257 x_attribute6 VARCHAR2,
3258 x_attribute7 VARCHAR2,
3259 x_attribute8 VARCHAR2,
3260 x_attribute9 VARCHAR2,
3261 x_attribute10 VARCHAR2,
3262 x_attribute11 VARCHAR2,
3263 x_attribute12 VARCHAR2,
3264 x_attribute13 VARCHAR2,
3265 x_attribute14 VARCHAR2,
3266 x_attribute15 VARCHAR2) IS
3267 l_bal_seg_column_name VARCHAR2(25);
3268 l_mgt_seg_column_name VARCHAR2(25);
3269 l_bal_seg_value_set_id NUMBER(10);
3270 l_mgt_seg_value_set_id NUMBER(10);
3271 l_chart_of_accounts_id NUMBER(15);
3272 l_access_set_id NUMBER(15);
3273 BEGIN
3274 l_chart_of_accounts_id := x_chart_of_accounts_id;
3275 gl_ledgers_pkg.get_bal_mgt_seg_info(l_bal_seg_column_name,
3276 l_bal_seg_value_set_id,
3277 l_mgt_seg_column_name,
3278 l_mgt_seg_value_set_id,
3279 l_chart_of_accounts_id);
3280
3281 -- Create an implicit access set header
3282 l_access_set_id := GL_ACCESS_SETS_PKG.create_implicit_access_set
3283 (x_name => x_name,
3284 x_security_segment_code => 'F',
3285 x_chart_of_accounts_id => x_chart_of_accounts_id,
3286 x_period_set_name => x_period_set_name,
3287 x_accounted_period_type => x_accounted_period_type,
3288 x_default_ledger_id => x_default_ledger_id,
3289 x_secured_seg_value_set_id => NULL,
3290 x_last_updated_by => x_user_id,
3291 x_last_update_login => x_login_id,
3292 x_creation_date => x_date,
3293 x_description => x_description);
3294
3295 INSERT INTO GL_LEDGERS
3296 (ledger_id, name, short_name, chart_of_accounts_id, currency_code,
3297 period_set_name, accounted_period_type, first_ledger_period_name,
3298 ret_earn_code_combination_id, suspense_allowed_flag,
3299 allow_intercompany_post_flag, track_rounding_imbalance_flag,
3300 enable_average_balances_flag, enable_budgetary_control_flag,
3301 require_budget_journals_flag, enable_je_approval_flag,
3302 enable_automatic_tax_flag, consolidation_ledger_flag,
3303 translate_eod_flag, translate_qatd_flag,
3304 translate_yatd_flag, automatically_created_flag,
3305 alc_ledger_type_code, ledger_category_code,
3306 object_type_code, le_ledger_type_code,
3307 bal_seg_value_option_code, bal_seg_column_name,
3308 mgt_seg_value_option_code, mgt_seg_column_name,
3309 bal_seg_value_set_id, mgt_seg_value_set_id,
3310 implicit_access_set_id, future_enterable_periods_limit,
3311 ledger_attributes, enable_reconciliation_flag,
3312 last_update_date, last_updated_by, last_update_login,
3313 creation_date, created_by, description,
3314 context, attribute1, attribute2, attribute3,
3315 attribute4, attribute5, attribute6, attribute7,
3316 attribute8, attribute9, attribute10, attribute11,
3317 attribute12, attribute13, attribute14, attribute15,
3318 create_je_flag)
3319 VALUES
3320 (x_ledger_id, x_name, x_short_name, x_chart_of_accounts_id, 'X',
3321 x_period_set_name, x_accounted_period_type, 'X',
3322 -1, 'N', -- ret_earn_code_combination_id, suspense_allowed_flag
3323 'N','N', -- allow_intercompany_post_flag, track_rounding_imbalance_flag
3324 'N','N', -- enable_average_balances_flag, enable_budgetary_control_flag
3325 'N','N', -- require_budget_journals_flag, enable_je_approval_flag
3326 'N','N', -- enable_automatic_tax_flag, consolidation_ledger_flag
3327 'N','N', -- translate_eod_flag, translate_qatd_flag
3328 'N','N', -- translate_yatd_flag, automatically_created_flag
3329 'NONE','NONE', -- alc_ledger_type_code, ledger_category_code
3330 'S','U', -- object_type_code, le_ledger_type_code
3331 'I', -- bal_seg_value_option_code
3332 l_bal_seg_column_name,
3333 'I', -- mgt_seg_value_option_code
3334 l_mgt_seg_column_name,
3335 l_bal_seg_value_set_id, l_mgt_seg_value_set_id,
3336 l_access_set_id, 0, -- future_enterable_periods_limit
3337 'S', 'N', -- ledger_attributes, enable_reconciliation_flag
3338 x_date, x_user_id, x_login_id,
3339 x_date, x_user_id, x_description,
3340 x_context, x_attribute1, x_attribute2, x_attribute3,
3341 x_attribute4, x_attribute5, x_attribute6, x_attribute7,
3342 x_attribute8, x_attribute9, x_attribute10, x_attribute11,
3343 x_attribute12, x_attribute13, x_attribute14, x_attribute15,
3344 'N'); -- enable_reconciliation_flag, create_je_flag
3345 END insert_set;
3346
3347 -- *********************************************************************
3348 PROCEDURE update_set(
3349 x_ledger_id NUMBER,
3350 x_name VARCHAR2,
3351 x_short_name VARCHAR2,
3352 x_chart_of_accounts_id NUMBER,
3353 x_period_set_name VARCHAR2,
3354 x_accounted_period_type VARCHAR2,
3355 x_default_ledger_id NUMBER,
3356 x_date DATE,
3357 x_user_id NUMBER,
3358 x_login_id NUMBER,
3359 x_description VARCHAR2,
3360 x_context VARCHAR2,
3361 x_attribute1 VARCHAR2,
3362 x_attribute2 VARCHAR2,
3363 x_attribute3 VARCHAR2,
3364 x_attribute4 VARCHAR2,
3365 x_attribute5 VARCHAR2,
3366 x_attribute6 VARCHAR2,
3367 x_attribute7 VARCHAR2,
3368 x_attribute8 VARCHAR2,
3369 x_attribute9 VARCHAR2,
3370 x_attribute10 VARCHAR2,
3371 x_attribute11 VARCHAR2,
3372 x_attribute12 VARCHAR2,
3373 x_attribute13 VARCHAR2,
3374 x_attribute14 VARCHAR2,
3375 x_attribute15 VARCHAR2) IS
3376 l_access_set_id NUMBER(15);
3377 BEGIN
3378 UPDATE GL_LEDGERS
3379 SET name = x_name,
3380 short_name = x_short_name,
3381 chart_of_accounts_id = x_chart_of_accounts_id,
3382 period_set_name = x_period_set_name,
3383 accounted_period_type = x_accounted_period_type,
3384 last_update_date = x_date,
3385 last_updated_by = x_user_id,
3386 last_update_login = x_login_id,
3387 description = x_description,
3388 context = x_context,
3389 attribute1 = x_attribute1,
3390 attribute2 = x_attribute2,
3391 attribute3 = x_attribute3,
3392 attribute4 = x_attribute4,
3393 attribute5 = x_attribute5,
3394 attribute6 = x_attribute6,
3395 attribute7 = x_attribute7,
3396 attribute8 = x_attribute8,
3397 attribute9 = x_attribute9,
3398 attribute10 = x_attribute10,
3399 attribute11 = x_attribute11,
3400 attribute12 = x_attribute12,
3401 attribute13 = x_attribute13,
3402 attribute14 = x_attribute14,
3403 attribute15 = x_attribute15
3404 WHERE ledger_id = x_ledger_id
3405 RETURNING implicit_access_set_id INTO l_access_set_id;
3406
3407 -- Make sure we have the implicit access set id
3408 IF (SQL%NOTFOUND) THEN
3409 RAISE NO_DATA_FOUND;
3410 END IF;
3411
3412 -- Update the implicit access set name to the same as the ledger set
3413 GL_ACCESS_SETS_PKG.update_implicit_access_set
3414 (x_access_set_id => l_access_set_id,
3415 x_name => x_name,
3416 x_last_update_date => x_date,
3417 x_last_updated_by => x_user_id,
3418 x_last_update_login => x_login_id);
3419
3420 -- Only when updating ledger sets could the default ledger be changed
3421 UPDATE GL_ACCESS_SETS
3422 SET default_ledger_id = x_default_ledger_id
3423 WHERE access_set_id = l_access_set_id;
3424 END update_set;
3425
3426 -- ********************************************************************
3427 PROCEDURE remove_lgr_bsv_for_le(x_le_id NUMBER) IS
3428 BEGIN
3429 delete from gl_ledger_norm_seg_vals
3430 where legal_entity_id = x_le_id
3431 and segment_type_code = 'B'
3432 and segment_value_type_code = 'S';
3433 END remove_lgr_bsv_for_le;
3434
3435 -- *********************************************************************
3436 PROCEDURE process_le_bsv_assign(x_le_id NUMBER,
3437 x_value_set_id NUMBER,
3438 x_bsv_value VARCHAR2,
3439 x_operation VARCHAR2,
3440 x_start_date DATE DEFAULT null,
3441 x_end_date DATE DEFAULT null) IS
3442 l_rowid VARCHAR2(30);
3443 l_ledger_id NUMBER;
3444 l_config_id NUMBER;
3445 l_record_id NUMBER;
3446 l_completion_status VARCHAR2(30);
3447 l_has_le_bsv VARCHAR2(30);
3448 CURSOR get_ledger_id IS
3449 select l.ledger_id, cd2.configuration_id
3450 from gl_ledgers l, gl_le_value_sets lv,
3451 gl_ledger_config_details cd1, gl_ledger_config_details cd2
3452 where lv.legal_entity_id = x_le_id
3453 and lv.flex_value_set_id = x_value_set_id
3454 and cd1.object_id = lv.legal_entity_id
3455 and cd1.object_type_code = 'LEGAL_ENTITY'
3456 and cd1.setup_step_code = 'NONE'
3457 and cd2.configuration_id = cd1.configuration_id
3458 and cd2.object_type_code in ('PRIMARY', 'SECONDARY')
3459 and cd2.setup_step_code = 'NONE'
3460 and l.bal_seg_value_set_id = lv.flex_value_set_id
3461 and l.ledger_id = cd2.object_id;
3462 CURSOR get_record_id(c_ledger_id NUMBER) IS
3463 select rowid
3464 from gl_ledger_norm_seg_vals
3465 where ledger_id = c_ledger_id
3466 and segment_type_code = 'B'
3467 and segment_value = x_bsv_value
3468 and segment_value_type_code = 'S'
3469 FOR UPDATE NOWAIT;
3470 CURSOR get_le_BSV (c_ledger_id NUMBER)IS
3471 SELECT 'has_le_bsv'
3472 FROM GL_LEDGER_NORM_SEG_VALS
3473 WHERE ledger_id = c_ledger_id
3474 AND legal_entity_id IS NOT NULL
3475 AND segment_type_code = 'B'
3476 AND segment_value_type_code = 'S'
3477 AND rownum<2;
3478 CURSOR get_complete_status IS
3479 select completion_status_code
3480 from gl_ledger_configurations
3481 where configuration_id =
3482 (select configuration_id
3483 from gl_ledger_config_details
3484 where object_id = x_le_id
3485 and object_type_code = 'LEGAL_ENTITY');
3486 BEGIN
3487 OPEN get_complete_status;
3488 FETCH get_complete_status INTO l_completion_status;
3489
3490 OPEN get_ledger_id;
3491 LOOP
3492 FETCH get_ledger_id INTO l_ledger_id, l_config_id;
3493 EXIT WHEN get_ledger_id%NOTFOUND;
3494 OPEN get_record_id(l_ledger_id);
3495 FETCH get_record_id INTO l_rowid;
3496 IF(get_record_id%NOTFOUND AND x_operation = 'I') THEN
3497 GL_LEDGER_NORM_SEG_VALS_PKG.Insert_Row(
3498 X_Rowid => l_rowid,
3499 X_Ledger_Id => l_ledger_id,
3500 X_Segment_Type_Code => 'B',
3501 X_Segment_Value => x_bsv_value,
3502 X_Segment_Value_Type_Code => 'S',
3503 X_Record_Id => GL_LEDGER_NORM_SEG_VALS_PKG.Get_Record_Id,
3504 X_Last_Update_Date => sysdate,
3505 X_Last_Updated_By => fnd_global.user_id,
3506 X_Creation_Date => sysdate,
3507 X_Created_By => fnd_global.user_id,
3508 X_Last_Update_Login => fnd_global.login_id,
3509 X_Start_Date => x_start_date,
3510 X_End_Date => x_end_date,
3511 X_Context => null,
3512 X_Attribute1 => null,
3513 X_Attribute2 => null,
3514 X_Attribute3 => null,
3515 X_Attribute4 => null,
3516 X_Attribute5 => null,
3517 X_Attribute6 => null,
3518 X_Attribute7 => null,
3519 X_Attribute8 => null,
3520 X_Attribute9 => null,
3521 X_Attribute10 => null,
3522 X_Attribute11 => null,
3523 X_Attribute12 => null,
3524 X_Attribute13 => null,
3525 X_Attribute14 => null,
3526 X_Attribute15 => null,
3527 X_Request_Id => null);
3528 update gl_ledger_norm_seg_vals
3529 set legal_entity_id = x_le_id,
3530 last_update_date = sysdate,
3531 last_updated_by = fnd_global.user_id,
3532 last_update_login = fnd_global.login_id
3533 where ledger_id = l_ledger_id
3534 and segment_type_code = 'B'
3535 and segment_value = x_bsv_value
3536 and segment_value_type_code = 'S';
3537 insert into gl_ledger_config_details
3538 (configuration_id,
3539 object_type_code,
3540 object_id,
3541 object_name,
3542 setup_step_code,
3543 next_action_code,
3544 status_code,
3545 created_by,
3546 last_update_login,
3547 last_update_date,
3548 last_updated_by,
3549 creation_date)
3550 select
3551 configuration_id,
3552 object_type_code,
3553 object_id,
3554 object_name,
3555 'INTER_ASSG',
3556 'ASSIGN_ACCTS',
3557 'NOT_STARTED',
3558 fnd_global.user_id,
3559 fnd_global.login_id,
3560 sysdate,
3561 fnd_global.user_id,
3562 sysdate
3563 from gl_ledger_config_details
3564 where object_id = l_ledger_id
3565 and object_type_code <> 'LEGAL_ENTITY'
3566 and setup_step_code = 'NONE'
3567 and configuration_id = l_config_id
3568 and NOT EXISTS(select 1
3569 from gl_ledger_config_details
3570 where object_id = l_ledger_id
3571 and object_type_code <> 'LEGAL_ENTITY'
3572 and setup_step_code = 'INTER_ASSG');
3573 update gl_ledgers
3574 set bal_seg_value_option_code = 'I'
3575 where ledger_id = l_ledger_id
3576 and bal_seg_value_option_code = 'A';
3577 update gl_ledgers
3578 set bal_seg_value_option_code = 'I'
3579 where ledger_id in
3580 (select target_ledger_id
3581 from gl_ledger_relationships
3582 where source_ledger_id = l_ledger_id
3583 and target_ledger_category_code = 'ALC'
3584 and relationship_type_code in ('JOURNAL','SUBLEDGER') )
3585 and bal_seg_value_option_code = 'A';
3586 ELSIF(x_operation = 'D') THEN
3587 IF(l_completion_status <> 'CONFIRMED') THEN
3588 delete from gl_ledger_norm_seg_vals
3589 where rowid = l_rowid;
3590 OPEN get_le_BSV(l_ledger_id);
3591 FETCH get_le_BSV
3592 INTO l_has_le_bsv;
3593
3594 IF (get_le_BSV%NOTFOUND) THEN
3595 delete gl_ledger_norm_seg_vals
3596 where ledger_id = l_ledger_id
3597 and segment_type_code = 'B'
3598 and segment_value_type_code = 'S';
3599
3600 update gl_ledgers
3601 set bal_seg_value_option_code = 'A'
3602 where ledger_id = l_ledger_id
3603 and bal_seg_value_option_code = 'I';
3604
3605 update gl_ledgers
3606 set bal_seg_value_option_code = 'A'
3607 where ledger_id in
3608 (select target_ledger_id
3609 from gl_ledger_relationships
3610 where source_ledger_id = l_ledger_id
3611 and target_ledger_category_code = 'ALC'
3612 and relationship_type_code in ('JOURNAL','SUBLEDGER') )
3613 and bal_seg_value_option_code = 'I';
3614 END IF;
3615 CLOSE get_le_BSV;
3616 ELSE
3617 GL_LEDGER_NORM_SEG_VALS_PKG.Delete_Row(
3618 X_Rowid => l_rowid);
3619 END IF;
3620 ELSIF(x_operation = 'U')THEN
3621 IF(l_completion_status <> 'CONFIRMED') THEN
3622 update gl_ledger_norm_seg_vals
3623 set start_date = x_start_date,
3624 end_date = x_end_date,
3625 last_update_date = sysdate,
3626 last_updated_by = fnd_global.user_id,
3627 last_update_login = fnd_global.login_id
3628 where rowid = l_rowid;
3629 ELSE
3630 update gl_ledger_norm_seg_vals
3631 set start_date = x_start_date,
3632 end_date = x_end_date,
3633 status_code = decode(status_code, 'I', 'I', 'U'),
3634 last_update_date = sysdate,
3635 last_updated_by = fnd_global.user_id,
3636 last_update_login = fnd_global.login_id
3637 where rowid = l_rowid;
3638 END IF;
3639 END IF;
3640 CLOSE get_record_id;
3641 END LOOP;
3642 CLOSE get_ledger_id;
3643 END process_le_bsv_assign;
3644
3645 -- *********************************************************************
3646 FUNCTION get_bsv_desc(x_object_type VARCHAR2,
3647 x_object_id NUMBER,
3648 x_bal_seg_value VARCHAR2)
3649 RETURN VARCHAR2 IS
3650 l_table_name VARCHAR2(30);
3651 l_value_col_name VARCHAR2(30);
3652 l_meaning_col_name VARCHAR2(30);
3653 l_enabled_col_name VARCHAR2(30);
3654 l_start_date_col_name VARCHAR2(30);
3655 l_end_date_col_name VARCHAR2(30);
3656 l_where_clause VARCHAR2(5000);
3657 sql_stmt VARCHAR2(10000);
3658 l_bal_value_desc VARCHAR2(240);
3659 l_flex_value_set_id NUMBER(30);
3660 CURSOR tabval_value_set(c_flex_value_set_id NUMBER) IS
3661 select application_table_name, value_column_name,
3662 nvl(meaning_column_name, 'null'), enabled_column_name,
3663 start_date_column_name, end_date_column_name,
3664 additional_where_clause
3665 from fnd_flex_validation_tables
3666 where flex_value_set_id = c_flex_value_set_id;
3667 CURSOR ledger_bsv_value_set(c_ledger_id NUMBER) IS
3668 select BAL_SEG_VALUE_SET_ID
3669 from gl_ledgers
3670 where ledger_id = c_ledger_id;
3671 BEGIN
3672 IF(x_object_type = 'LGR') THEN
3673 OPEN ledger_bsv_value_set(x_object_id);
3674 FETCH ledger_bsv_value_set INTO l_flex_value_set_id;
3675 CLOSE ledger_bsv_value_set;
3676 ELSIF(x_object_type = 'LE') THEN
3677 l_flex_value_set_id := x_object_id;
3678 END IF;
3679
3680 OPEN tabval_value_set(l_flex_value_set_id);
3681 FETCH tabval_value_set INTO l_table_name, l_value_col_name,
3682 l_meaning_col_name, l_enabled_col_name,
3683 l_start_date_col_name, l_end_date_col_name,
3684 l_where_clause;
3685 IF(tabval_value_set%NOTFOUND)THEN
3686 return null;
3687 END IF;
3688 IF(lower(substr(l_where_clause, 1, 5)) = 'where')THEN
3689 l_where_clause := substr(l_where_clause, 6);
3690 END IF;
3691 sql_stmt := null;
3692 sql_stmt := 'select '||l_meaning_col_name||' from '||l_table_name||
3693 ' where '||l_value_col_name||
3694 ' = :1 and '||l_enabled_col_name||' =''Y'''
3695 ||' and nvl('||l_start_date_col_name||',sysdate)<=sysdate'
3696 ||' and nvl('||l_end_date_col_name||',sysdate)>=sysdate';
3697 IF(l_where_clause IS NOT null) THEN
3698 sql_stmt := sql_stmt||' and '||substrb(l_where_clause,instrb(l_where_clause, ' ')+1);
3699 END IF;
3700 EXECUTE IMMEDIATE sql_stmt INTO l_bal_value_desc USING x_bal_seg_value;
3701 RETURN l_bal_value_desc;
3702 END get_bsv_desc;
3703
3704 -- *********************************************************************
3705 PROCEDURE check_calendar_gap (
3706 x_period_set_name IN VARCHAR2,
3707 x_period_type IN VARCHAR2,
3708 x_gap_flag OUT NOCOPY VARCHAR2,
3709 x_start_date OUT NOCOPY DATE,
3710 x_end_date OUT NOCOPY DATE) IS
3711 not_assigned CONSTANT VARCHAR2(15) := 'NOT ASSIGNED';
3712
3713 gap_date DATE;
3714 start_date DATE;
3715 end_date DATE;
3716 beginning DATE;
3717 ending DATE;
3718
3719 CURSOR period_set IS
3720 SELECT min(start_date) begins, max(end_date) ends
3721 FROM gl_periods
3722 WHERE period_set_name = x_period_set_name
3723 AND period_type = x_period_type;
3724
3725 CURSOR gap_exists IS
3726 SELECT accounting_date
3727 FROM gl_date_period_map
3728 WHERE period_name = not_assigned
3729 AND period_set_name = x_period_set_name
3730 AND period_type = x_period_type
3731 AND accounting_date BETWEEN beginning AND ending;
3732
3733 CURSOR gap_start IS
3734 SELECT max(accounting_date)
3735 FROM gl_date_period_map
3736 WHERE period_name <> not_assigned
3737 AND period_set_name = x_period_set_name
3738 AND period_type = x_period_type
3739 AND accounting_date < gap_date;
3740
3741 CURSOR gap_end IS
3742 SELECT min(accounting_date)
3743 FROM gl_date_period_map
3744 WHERE period_name <> not_assigned
3745 AND period_set_name = x_period_set_name
3746 AND period_type = x_period_type
3747 AND accounting_date > gap_date;
3748
3749 BEGIN
3750 -- Open the gap_exists cursor and see if we get anything
3751 OPEN period_set;
3752 FETCH period_set INTO beginning, ending;
3753 CLOSE period_set;
3754 OPEN gap_exists;
3755 FETCH gap_exists INTO gap_date;
3756 IF gap_exists%NOTFOUND THEN
3757 CLOSE gap_exists;
3758 x_gap_flag := 'N';
3759 ELSE
3760 CLOSE gap_exists;
3761 x_gap_flag := 'Y';
3762 -- Get the spanning dates
3763 OPEN gap_start;
3764 FETCH gap_start INTO start_date;
3765 CLOSE gap_start;
3766 OPEN gap_end;
3767 FETCH gap_end INTO end_date;
3768 CLOSE gap_end;
3769 x_start_date := nvl(start_date, beginning);
3770 x_end_date := nvl(end_date, ending);
3771 -- Tell the user
3772 END IF;
3773 EXCEPTION
3774 WHEN OTHERS THEN
3775 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
3776 fnd_message.set_token('PROCEDURE',
3777 'gl_period_statuses_pkg.check_for_gap');
3778 RAISE;
3779 END check_calendar_gap;
3780
3781 -- *********************************************************************
3782
3783 PROCEDURE check_duplicate_ledger (
3784 x_object_name IN VARCHAR2,
3785 x_object_id IN NUMBER,
3786 x_dupl_flag OUT NOCOPY VARCHAR2) IS
3787 CURSOR get_duplicate IS
3788 select 'Duplicate'
3789 from GL_LEDGERS LEDGERS
3790 where LEDGERS.NAME = x_object_name
3791 and LEDGERS.LEDGER_ID <> x_object_id
3792 UNION
3793 select 'Duplicate'
3794 from GL_ACCESS_SETS ACCESS_SETS
3795 where ACCESS_SETS.NAME = x_object_name
3796 and ACCESS_SETS.AUTOMATICALLY_CREATED_FLAG <> 'Y'
3797 UNION
3798 select 'Duplicate'
3799 from GL_LEDGER_RELATIONSHIPS
3800 where target_ledger_category_code = 'ALC'
3801 and relationship_type_code = 'BALANCE'
3802 and target_ledger_name = x_object_name;
3803 test VARCHAR2(20);
3804 BEGIN
3805 OPEN get_duplicate;
3806 FETCH get_duplicate INTO test;
3807 IF(get_duplicate%NOTFOUND) THEN
3808 x_dupl_flag := 'N';
3809 ELSE
3810 x_dupl_flag := 'Y';
3811 END IF;
3812 END check_duplicate_ledger;
3813
3814 PROCEDURE check_dupl_ldg_shortname(
3815 x_ledger_short_name IN VARCHAR2,
3816 x_ledger_id IN NUMBER,
3817 x_dupl_flag OUT NOCOPY VARCHAR2) IS
3818 CURSOR get_duplicate IS
3819 select 'Duplicate'
3820 from GL_LEDGERS GL_LEDGERS
3821 where GL_LEDGERS.SHORT_NAME = x_ledger_short_name
3822 and GL_LEDGERS.LEDGER_ID <> x_ledger_id
3823 UNION
3824 select 'Duplicate'
3825 from GL_LEDGER_RELATIONSHIPS
3826 where target_ledger_category_code = 'ALC'
3827 and relationship_type_code = 'BALANCE'
3828 and target_ledger_short_name = x_ledger_short_name;
3829 test VARCHAR2(20);
3830 BEGIN
3831 OPEN get_duplicate;
3832 FETCH get_duplicate INTO test;
3833 IF(get_duplicate%NOTFOUND) THEN
3834 x_dupl_flag := 'N';
3835 ELSE
3836 x_dupl_flag := 'Y';
3837 END IF;
3838 END check_dupl_ldg_shortname;
3839
3840 PROCEDURE check_dupl_tgr_name (
3841 x_target_ledger_name IN VARCHAR2,
3842 x_relationship_id IN NUMBER,
3843 x_ledger_id IN NUMBER,
3844 x_dupl_flag OUT NOCOPY VARCHAR2) IS
3845 CURSOR get_duplicate IS
3846 SELECT 'Duplicate'
3847 FROM gl_ledger_relationships
3848 WHERE application_id = 101
3849 AND target_ledger_name = x_target_ledger_name
3850 AND relationship_type_code <> 'NONE'
3851 AND relationship_id <> x_relationship_id
3852 AND target_ledger_id <> x_ledger_id
3853 UNION
3854 SELECT name
3855 FROM GL_LEDGERS
3856 WHERE name = x_target_ledger_name
3857 AND ledger_id <> NVL(x_ledger_id,-1)
3858 UNION
3859 SELECT 'Duplicate'
3860 FROM GL_ACCESS_SETS a
3861 WHERE a.name = x_target_ledger_name
3862 AND a.automatically_created_flag <> 'Y';
3863 test VARCHAR2(20);
3864 BEGIN
3865 OPEN get_duplicate;
3866 FETCH get_duplicate INTO test;
3867 IF(get_duplicate%NOTFOUND) THEN
3868 x_dupl_flag := 'N';
3869 ELSE
3870 x_dupl_flag := 'Y';
3871 END IF;
3872 END check_dupl_tgr_name;
3873
3874 PROCEDURE check_dupl_tgr_shortname(
3875 x_ledger_short_name IN VARCHAR2,
3876 x_relationship_id IN NUMBER,
3877 x_ledger_id IN NUMBER,
3878 x_dupl_flag OUT NOCOPY VARCHAR2) IS
3879 CURSOR get_duplicate IS
3880 SELECT 'Duplicate'
3881 FROM gl_ledger_relationships
3882 WHERE application_id = 101
3883 AND target_ledger_short_name = x_ledger_short_name
3884 AND relationship_type_code <> 'NONE'
3885 AND relationship_id <> x_relationship_id
3886 AND target_ledger_id <> x_ledger_id
3887 UNION
3888 SELECT short_name
3889 FROM gl_ledgers
3890 WHERE short_name = x_ledger_short_name
3891 AND ledger_id <> NVL(x_ledger_id,-1);
3892 test VARCHAR2(20);
3893 BEGIN
3894 OPEN get_duplicate;
3895 FETCH get_duplicate INTO test;
3896 IF(get_duplicate%NOTFOUND) THEN
3897 x_dupl_flag := 'N';
3898 ELSE
3899 x_dupl_flag := 'Y';
3900 END IF;
3901 END check_dupl_tgr_shortname;
3902
3903 PROCEDURE set_status_complete(x_object_type VARCHAR2,
3904 x_object_id IN NUMBER) IS
3905 BEGIN
3906 IF(x_object_type = 'CONFIGURATION') THEN
3907 update gl_ledger_configurations
3908 set completion_status_code = 'CONFIRMED'
3909 where configuration_id = x_object_id;
3910
3911 update gl_ledgers
3912 set complete_flag = 'Y'
3913 where configuration_id = x_object_id
3914 and ledger_category_code in ('PRIMARY', 'SECONDARY', 'ALC');
3915 ELSIF(x_object_type = 'LEDGER') THEN
3916 update gl_ledgers
3917 set complete_flag = 'Y'
3918 where ledger_id = x_object_id;
3919 END IF;
3920 END set_status_complete;
3921
3922 PROCEDURE check_translation_performed(
3923 x_ledger_id NUMBER,
3924 x_run_flag OUT NOCOPY VARCHAR2) IS
3925 CURSOR check_translation IS
3926 SELECT 'translated'
3927 FROM DUAL
3928 WHERE EXISTS(
3929 SELECT 'X'
3930 FROM gl_translation_tracking
3931 WHERE ledger_id = x_ledger_id
3932 AND actual_flag = 'A'
3933 AND ((earliest_ever_period_year*10000)+earliest_ever_period_num) <
3934 ((earliest_never_period_year*10000)+earliest_never_period_num));
3935 dummy VARCHAR2(100);
3936 BEGIN
3937 OPEN check_translation;
3938
3939 FETCH check_translation
3940 INTO dummy;
3941
3942 IF check_translation%FOUND THEN
3943 CLOSE check_translation;
3944 x_run_flag :='Y';
3945 ELSE
3946 CLOSE check_translation;
3947 x_run_flag :='N';
3948 END IF;
3949 END check_translation_performed;
3950
3951 PROCEDURE check_calendar_35max_days(x_ledger_id IN NUMBER,
3952 x_35day_flag OUT NOCOPY VARCHAR2) IS
3953 CURSOR check_calendar IS
3954 SELECT max(pr.end_date - pr.start_date)+1
3955 FROM GL_PERIODS pr, GL_PERIOD_TYPES pty
3956 WHERE pr.period_type = pty.period_type
3957 AND ((pr.period_set_name, pr.period_type)
3958 IN (SELECT period_set_name, accounted_period_type
3959 FROM gl_ledgers where ledger_id = x_ledger_id))
3960 GROUP BY pr.period_set_name, pty.user_period_type, pty.period_type
3961 HAVING (max(pr.end_date - pr.start_date)+1) >=35;
3962
3963 dummy VARCHAR2(100);
3964 BEGIN
3965 OPEN check_calendar;
3966
3967 FETCH check_calendar
3968 INTO dummy;
3969
3970 IF check_calendar%FOUND THEN
3971 CLOSE check_calendar;
3972 x_35day_flag :='Y';
3973 ELSE
3974 CLOSE check_calendar;
3975 x_35day_flag :='N';
3976 END IF;
3977 END check_calendar_35max_days;
3978
3979 PROCEDURE check_desc_flex_setup(x_desc_flex_name IN VARCHAR2,
3980 x_setup_flag OUT NOCOPY VARCHAR2) IS
3981 BEGIN
3982 IF (FND_FLEX_APIS.is_descr_setup(101,x_desc_flex_name)= FALSE) THEN
3983 x_setup_flag := 'N';
3984 ELSE
3985 x_setup_flag := 'Y';
3986 END IF;
3987 END check_desc_flex_setup;
3988
3989 PROCEDURE remove_ou_setup(x_config_id IN NUMBER) IS
3990 CURSOR le_assigned IS
3991 SELECT object_id
3992 FROM gl_ledger_config_details
3993 WHERE configuration_id = x_config_id
3994 AND object_type_code = 'LEGAL_ENTITY';
3995 test NUMBER;
3996 BEGIN
3997 OPEN le_assigned;
3998 FETCH le_assigned INTO test;
3999 IF(le_assigned%NOTFOUND) THEN
4000 delete from gl_ledger_config_details
4001 where configuration_id = x_config_id
4002 and object_type_code = 'PRIMARY'
4003 and setup_step_code = 'OU_SETUP';
4004 END IF;
4005 END remove_ou_setup;
4006
4007 -- *********************************************************************
4008 FUNCTION get_short_name ( x_primary_ledger_short_name VARCHAR2,
4009 x_suffix_length number)
4010 RETURN VARCHAR2 IS
4011
4012 l_short_name VARCHAR2(100) := NULL;
4013 l_prefix_length NUMBER := 20 - x_suffix_length;
4014
4015 BEGIN
4016
4017 IF ((lengthb(x_primary_ledger_short_name) + x_suffix_length) >20) THEN
4018
4019 FOR i IN REVERSE 1..l_prefix_length
4020 LOOP
4021 l_short_name := substr(x_primary_ledger_short_name,0,i);
4022
4023 IF (lengthb(l_short_name) <= l_prefix_length) THEN
4024
4025 return l_short_name;
4026
4027 END IF;
4028
4029 END LOOP;
4030
4031 ELSE
4032 return x_primary_ledger_short_name;
4033 END IF;
4034
4035 END get_short_name ;
4036 -- ***********************************************************************
4037 END gl_ledgers_pkg;