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