[Home] [Help]
PACKAGE BODY: APPS.GCS_ENTITIES_PKG
Source
1 PACKAGE BODY GCS_ENTITIES_PKG AS
2 /* $Header: gcsenttb.pls 120.10 2007/03/22 12:59:33 vkosuri ship $ */
3 --
4 -- Package
5 -- gcs_entities_pkg
6 -- Purpose
7 -- Package procedures for Consolidation Hierarchies
8 -- History
9 -- 06-MAR-04 M Ward Created
10 --
11
12 --
13 -- Private Global Variables
14 --
15
16 -- The API name
17 g_api CONSTANT VARCHAR2(40) := 'gcs.plsql.GCS_ENTITIES_PKG';
18
19 -- Action types for writing module information to the log file. Used for
20 -- the procedure log_file_module_write.
21 g_module_enter CONSTANT VARCHAR2(2) := '>>';
22 g_module_success CONSTANT VARCHAR2(2) := '<<';
23 g_module_failure CONSTANT VARCHAR2(2) := '<x';
24
25 -- A newline character. Included for convenience when writing long strings.
26 g_nl CONSTANT VARCHAR2(1) := '
27 ';
28
29 G_CHAN_FLG VARCHAR2(1);
30 G_CCTR_FLG VARCHAR2(1);
31 G_CUST_FLG VARCHAR2(1);
32 G_GEOG_FLG VARCHAR2(1);
33 G_LN_ITEM_FLG VARCHAR2(1);
34 G_NAT_ACCT_FLG VARCHAR2(1);
35 G_PROD_FLG VARCHAR2(1);
36 G_PROJ_FLG VARCHAR2(1);
37 G_USER1_FLG VARCHAR2(1);
38 G_USER2_FLG VARCHAR2(1);
39 G_USER3_FLG VARCHAR2(1);
40 G_USER4_FLG VARCHAR2(1);
41 G_USER5_FLG VARCHAR2(1);
42 G_USER6_FLG VARCHAR2(1);
43 G_USER7_FLG VARCHAR2(1);
44 G_USER8_FLG VARCHAR2(1);
45 G_USER9_FLG VARCHAR2(1);
46 G_USER10_FLG VARCHAR2(1);
47
48 --
49 -- Private Procedures and Functions for Multiple Parents
50 --
51
52 --
53 -- Procedure
54 -- Module_Log_Write
55 -- Purpose
56 -- Write the procedure or function entered or exited, and the time that
57 -- this happened. Write it to the log repository.
58 -- Arguments
59 -- p_module Name of the module
60 -- p_action_type Entered, Exited Successfully, or Exited with Failure
61 -- Example
62 -- GCS_ENTITIES_PKG.Module_Log_Write
63 -- Notes
64 --
65 PROCEDURE Module_Log_Write(p_module VARCHAR2, p_action_type VARCHAR2) IS
66 BEGIN
67 -- Only print if the log level is set at the appropriate level
68 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
69 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
70 g_api || '.' || p_module,
71 p_action_type || ' ' || p_module || '() ' ||
72 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
73 END IF;
74 END Module_Log_Write;
75
76 --
77 -- Procedure
78 -- Write_To_Log
79 -- Purpose
80 -- Write the text given to the log in 3500 character increments
81 -- this happened. Write it to the log repository.
82 -- Arguments
83 -- p_module Name of the module
84 -- p_level Logging level
85 -- p_text Text to write
86 -- Example
87 -- GCS_ENTITIES_PKG.Write_To_Log
88 -- Notes
89 --
90 PROCEDURE Write_To_Log(p_module VARCHAR2,
91 p_level NUMBER,
92 p_text VARCHAR2) IS
93 api_module_concat VARCHAR2(200);
94 text_with_date VARCHAR2(32767);
95 text_with_date_len NUMBER;
96 curr_index NUMBER;
97 BEGIN
98 -- Only print if the log level is set at the appropriate level
99 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
100 api_module_concat := g_api || '.' || p_module;
101 text_with_date := to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') || g_nl ||
102 p_text;
103 text_with_date_len := length(text_with_date);
104 curr_index := 1;
105 WHILE curr_index <= text_with_date_len LOOP
106 fnd_log.string(p_level,
107 api_module_concat,
108 substr(text_with_date, curr_index, 3500));
109 curr_index := curr_index + 3500;
110 END LOOP;
111 END IF;
112 END Write_To_Log;
113
114 --
115 -- Public Procedures and Functions for Multiple Parents
116 --
117
118 --
119 -- Procedure
120 -- Add_To_Summary_Table
121 -- Purpose
122 -- Inserts rows into the gcs_entity_cctr_orgs table.
123 -- Arguments
124 -- p_entity_id Entity for which the logic must be performed
125 -- Example
126 -- GCS_ENTITIES_PKG.Add_To_Summary_Table(...);
127 -- Notes
128 --
129 PROCEDURE Add_To_Summary_Table(p_entity_id NUMBER) IS
130 v_module VARCHAR2(30);
131 BEGIN
132 v_module := 'Add_To_Summary_Table';
133 module_log_write(v_module, g_module_enter);
134
135 --Added by Santosh - 5235164
136 DELETE gcs_entity_cctr_orgs WHERE entity_id = p_entity_id;
137
138
139 INSERT INTO gcs_entity_cctr_orgs
140 (entity_id,
141 company_cost_center_org_id,
142 object_version_number,
143 creation_date,
144 created_by,
145 last_update_date,
146 last_updated_by,
147 last_update_login)
148 SELECT p_entity_id,
149 coa.company_cost_center_org_id,
150 1,
151 sysdate,
152 eo.created_by,
153 sysdate,
154 eo.last_updated_by,
155 eo.last_update_login
156 FROM gcs_entity_organizations eo,
157 fem_cctr_orgs_attr coa,
158 fem_dim_attributes_b fdab,
159 fem_dim_attr_versions_b fdavb
160 WHERE eo.entity_id = p_entity_id
161 AND coa.dim_attribute_numeric_member =
162 eo.company_cost_center_org_id
163 AND coa.attribute_id = fdab.attribute_id
164 AND coa.version_id = fdavb.version_id
165 AND fdab.attribute_varchar_label = 'COMPANY'
166 AND fdavb.attribute_id = fdab.attribute_id
167 AND fdavb.default_version_flag = 'Y';
168
169 module_log_write(v_module, g_module_success);
170 EXCEPTION
171 WHEN OTHERS THEN
172 write_to_log(v_module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
173 module_log_write(v_module, g_module_failure);
174 RAISE;
175 END Add_To_Summary_Table;
176
177 --
178 -- Procedure
179 -- Get_Next_Token
180 -- Purpose
181 -- Gets the next token from a clob
182 -- Arguments
183 -- p_info_clob The clob from which to retrieve the next token
184 -- p_current_loc The current starting location of the clob
185 -- x_info_buffer The buffer into which the clob should be written
186 -- Example
187 -- GCS_ENTITIES_PKG.Get_Next_Token(...);
188 -- Notes
189 --
190 PROCEDURE Get_Next_Token(p_info_clob CLOB,
191 p_current_loc IN OUT NOCOPY INTEGER,
192 x_info_buffer OUT NOCOPY VARCHAR2) IS
193 next_delim INTEGER;
194 read_amount INTEGER;
195 BEGIN
196 next_delim := DBMS_LOB.INSTR(p_info_clob, g_nl, p_current_loc, 1);
197
198 read_amount := next_delim - p_current_loc;
199
200 IF read_amount > 0 THEN
201 DBMS_LOB.READ(p_info_clob, read_amount, p_current_loc, x_info_buffer);
202 ELSE
203 x_info_buffer := '';
204 END IF;
205
206 p_current_loc := next_delim + 1;
207 END Get_Next_Token;
208
209 --
210 -- Entitiy Loader private procedures
211 --
212
213 --
214 -- Function
215 -- Create_Ext_Ledger
216 -- Purpose
217 -- Create an external ledger based on the parameters given.
218 -- Arguments
219 -- p_src_sys_code Source system code
220 -- p_vs_combo_id Global value set combination id
221 -- p_cal_hier_id Calendar hierarchy id
222 -- p_ccy_code Default currency code
223 -- p_ledger_name Name of the new ledger
224 -- p_ledger_desc Description of the new ledger
225 -- Return value
226 -- The new ledger_id
227 -- Example
228 -- GCS_ENTITIES_PKG.Create_Ext_Ledger(...)
229 -- Notes
230 --
231 FUNCTION Create_Ext_Ledger(p_src_sys_code NUMBER,
232 p_vs_combo_id NUMBER,
233 p_cal_hier_id NUMBER,
234 p_ccy_code VARCHAR2,
235 p_ledger_name VARCHAR2,
236 p_ledger_desc VARCHAR2) RETURN NUMBER IS
237 new_ledger_id NUMBER;
238
239 return_status VARCHAR2(100);
240 msg_count NUMBER;
241 msg_data VARCHAR2(2000);
242
243 source_system_disp_code VARCHAR2(200);
244 BEGIN
245 SELECT gl_sets_of_books_s.nextval INTO new_ledger_id FROM dual;
246
247 FEM_DIMENSION_UTIL_PKG.REGISTER_LEDGER(X_RETURN_STATUS => return_status,
248 X_MSG_COUNT => msg_count,
249 X_MSG_DATA => msg_data,
250 P_LEDGER_ID => new_ledger_id,
251 P_DISPLAY_CODE => p_ledger_name,
252 P_LEDGER_NAME => p_ledger_name,
253 P_FUNC_CURR_CD => p_ccy_code,
254 P_SOURCE_CD => p_src_sys_code,
255 P_CAL_PER_HID => p_cal_hier_id,
256 P_GLOBAL_VS_ID => p_vs_combo_id,
257 P_EPB_DEF_LG_FLG => 'N',
258 P_ENT_CURR_FLG => 'Y',
259 P_AVG_BAL_FLG => 'Y',
260 P_CHAN_FLG => G_CHAN_FLG,
261 P_CCTR_FLG => G_CCTR_FLG,
262 P_CUST_FLG => G_CUST_FLG,
263 P_GEOG_FLG => G_GEOG_FLG,
264 P_LN_ITEM_FLG => G_LN_ITEM_FLG,
265 P_NAT_ACCT_FLG => G_NAT_ACCT_FLG,
266 P_PROD_FLG => G_PROD_FLG,
267 P_PROJ_FLG => G_PROJ_FLG,
268 P_ENTITY_FLG => 'Y',
269 P_USER1_FLG => G_USER1_FLG,
270 P_USER2_FLG => G_USER2_FLG,
271 P_USER3_FLG => G_USER3_FLG,
272 P_USER4_FLG => G_USER4_FLG,
273 P_USER5_FLG => G_USER5_FLG,
274 P_USER6_FLG => G_USER6_FLG,
275 P_USER7_FLG => G_USER7_FLG,
276 P_USER8_FLG => G_USER8_FLG,
277 P_USER9_FLG => G_USER9_FLG,
278 P_USER10_FLG => G_USER10_FLG,
279 P_VER_NAME => 'Default',
280 P_VER_DISP_CD => 'Default',
281 P_LEDGER_DESC => p_ledger_desc);
282
283 return new_ledger_id;
284 END Create_Ext_Ledger;
285
286 --
287 -- Entitiy Loader procedure
288 --
289 PROCEDURE Load_Entities(x_errbuf OUT NOCOPY VARCHAR2,
290 x_retcode OUT NOCOPY VARCHAR2,
291 p_file_id NUMBER) IS
292 l_local_clob CLOB;
293
294 info_buffer VARCHAR2(1000);
295 current_loc INTEGER;
296 clob_length INTEGER;
297 read_mode INTEGER; -- 1 for EXT, 2 for OGL, 3 for CONS
298
299 new_entity_id NUMBER;
300 entity_name VARCHAR2(500);
301 entity_desc VARCHAR2(500);
302 entity_nComps NUMBER;
303 entity_comp_id NUMBER;
304 entity_base_org_id NUMBER;
305 entity_contact VARCHAR2(500);
306 entity_logo VARCHAR2(500);
307 entity_src_sys NUMBER;
308 entity_vs_combo_id NUMBER;
309 entity_cal_hier_id NUMBER;
310 entity_def_ccy VARCHAR2(30);
311 entity_trs_obj_id NUMBER;
312 entity_vrs_obj_id NUMBER;
313 entity_sec_by_role VARCHAR2(10);
314 entity_nRoles NUMBER;
315 entity_role_name VARCHAR2(500);
316 entity_ledger_id NUMBER;
317 entity_bal_rule_id NUMBER;
318 entity_elim_name VARCHAR2(500);
319 entity_cont_id NUMBER;
320
321 entity_type_code VARCHAR2(30);
322
323 elim_entity_id NUMBER;
324
325 base_org_attr_id NUMBER;
326 base_org_v_id NUMBER;
327 contact_attr_id NUMBER;
328 contact_v_id NUMBER;
329 logo_attr_id NUMBER;
330 logo_v_id NUMBER;
331 src_sys_attr_id NUMBER;
332 src_sys_v_id NUMBER;
333 ledger_attr_id NUMBER;
334 ledger_v_id NUMBER;
335 trs_attr_id NUMBER;
336 trs_v_id NUMBER;
337 vrs_attr_id NUMBER;
338 vrs_v_id NUMBER;
339 secure_attr_id NUMBER;
340 secure_v_id NUMBER;
341 bal_rule_attr_id NUMBER;
342 bal_rule_v_id NUMBER;
343 elim_attr_id NUMBER;
344 elim_v_id NUMBER;
345 cont_attr_id NUMBER;
346 cont_v_id NUMBER;
347 entity_type_attr_id NUMBER;
348 entity_type_v_id NUMBER;
349 recon_leaf_attr_id NUMBER;
350 recon_leaf_v_id NUMBER;
351
352 user_id NUMBER;
353 login_id NUMBER;
354
355 v_module VARCHAR2(30);
356 l_request_id NUMBER(15);
357 BEGIN
358 v_module := 'Load_Entities';
359 module_log_write(v_module, g_module_enter);
360 current_loc := 1;
361 read_mode := 0;
362
363 SELECT xml_data
364 INTO l_local_clob
365 FROM gcs_xml_files xf
366 WHERE xf.xml_file_id = p_file_id
367 AND xf.xml_file_type = 'ENTITY_LOADER'
368 AND xf.language = 'US';
369
370 G_CHAN_FLG := gcs_utility_pkg.get_dimension_required('CHANNEL_ID');
371 G_CCTR_FLG := gcs_utility_pkg.get_dimension_required('COMPANY_COST_CENTER_ORG_ID');
372 G_CUST_FLG := gcs_utility_pkg.get_dimension_required('CUSTOMER_ID');
373 G_GEOG_FLG := gcs_utility_pkg.get_dimension_required('GEOGRAPHY_ID');
374 G_LN_ITEM_FLG := gcs_utility_pkg.get_dimension_required('LINE_ITEM_ID');
375 G_NAT_ACCT_FLG := gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID');
376 G_PROD_FLG := gcs_utility_pkg.get_dimension_required('PRODUCT_ID');
377 G_PROJ_FLG := gcs_utility_pkg.get_dimension_required('PROJECT_ID');
378 G_USER1_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM1_ID');
379 G_USER2_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM2_ID');
380 G_USER3_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM3_ID');
381 G_USER4_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM4_ID');
382 G_USER5_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM5_ID');
383 G_USER6_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM6_ID');
384 G_USER7_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM7_ID');
385 G_USER8_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM8_ID');
386 G_USER9_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM9_ID');
387 G_USER10_FLG := gcs_utility_pkg.get_dimension_required('USER_DIM10_ID');
388
389 base_org_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-BASE_ORGANIZATION')
390 .attribute_id;
391 base_org_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-BASE_ORGANIZATION')
392 .version_id;
393 contact_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT')
394 .attribute_id;
395 contact_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT')
396 .version_id;
397 logo_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-IMAGE_NAME')
398 .attribute_id;
399 logo_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-IMAGE_NAME')
400 .version_id;
401 src_sys_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-SOURCE_SYSTEM_CODE')
402 .attribute_id;
403 src_sys_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-SOURCE_SYSTEM_CODE')
404 .version_id;
405 ledger_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
406 .attribute_id;
407 ledger_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
408 .version_id;
409 trs_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-TRANSFORM_RULE_SET_ID')
410 .attribute_id;
411 trs_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-TRANSFORM_RULE_SET_ID')
412 .version_id;
413 vrs_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-VALIDATION_RULE_SET_ID')
414 .attribute_id;
415 vrs_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-VALIDATION_RULE_SET_ID')
416 .version_id;
417 secure_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-SECURITY_ENABLED_FLAG')
418 .attribute_id;
419 secure_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-SECURITY_ENABLED_FLAG')
420 .version_id;
421 bal_rule_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-BALANCES_RULE_ID')
422 .attribute_id;
423 bal_rule_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-BALANCES_RULE_ID')
424 .version_id;
425 elim_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY')
426 .attribute_id;
427 elim_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY')
428 .version_id;
429 cont_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY')
430 .attribute_id;
431 cont_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY')
432 .version_id;
433 entity_type_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
434 .attribute_id;
435 entity_type_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
436 .version_id;
437 recon_leaf_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-RECON_LEAF_NODE_FLAG')
438 .attribute_id;
439 recon_leaf_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-RECON_LEAF_NODE_FLAG')
440 .version_id;
441
442 user_id := FND_GLOBAL.user_id;
443 login_id := FND_GLOBAL.login_id;
444
445 clob_length := DBMS_LOB.GETLENGTH(l_local_clob);
446
447 WHILE (current_loc < clob_length) LOOP
448 get_next_token(l_local_clob, current_loc, info_buffer);
449
450 -- Switch reading modes if applicable
451 IF info_buffer = 'EXT' THEN
452 read_mode := 1;
453 ELSIF info_buffer = 'OGL' THEN
454 read_mode := 2;
455 ELSIF info_buffer = 'CONS' THEN
456 read_mode := 3;
457 ELSE
458 -- Now we get to the meat of the loading logic
459
460 new_entity_id := to_number(info_buffer);
461 get_next_token(l_local_clob, current_loc, entity_name);
462 get_next_token(l_local_clob, current_loc, entity_desc);
463
464 -- Insert the base entity row first
465 INSERT INTO fem_entities_vl
466 (entity_id,
467 entity_display_code,
468 entity_name,
469 description,
470 value_set_id,
471 enabled_flag,
472 read_only_flag,
473 personal_flag,
474 object_version_number,
475 creation_date,
476 created_by,
477 last_update_date,
478 last_updated_by,
479 last_update_login)
480 VALUES
481 (new_entity_id,
482 to_char(new_entity_id),
483 entity_name,
484 entity_desc,
485 18,
486 'Y',
487 'N',
488 'N',
489 1,
490 sysdate,
491 user_id,
492 sysdate,
493 user_id,
494 login_id);
495
496 -- operating entity
497 IF read_mode IN (1, 2) THEN
498 get_next_token(l_local_clob, current_loc, info_buffer);
499 entity_nComps := to_number(info_buffer);
500 FOR counter IN 1 .. entity_nComps LOOP
501 get_next_token(l_local_clob, current_loc, info_buffer);
502 entity_comp_id := to_number(info_buffer);
503
504 -- Insert a single company assignment row
505 INSERT INTO gcs_entity_organizations
506 (entity_id,
507 company_cost_center_org_id,
508 object_version_number,
509 creation_date,
510 created_by,
511 last_update_date,
512 last_updated_by,
513 last_update_login)
514 VALUES
515 (new_entity_id,
516 entity_comp_id,
517 1,
518 sysdate,
519 user_id,
520 sysdate,
521 user_id,
522 login_id);
523 END LOOP;
524
525 -- Also populate the flattened table
526 add_to_summary_table(p_entity_id => new_entity_id);
527
528 -- Get and insert the base org information
529 get_next_token(l_local_clob, current_loc, info_buffer);
530 entity_base_org_id := to_number(info_buffer);
531
532 INSERT INTO fem_entities_attr
533 (attribute_id,
534 version_id,
535 entity_id,
536 value_set_id,
537 aw_snapshot_flag,
538 dim_attribute_numeric_member,
539 object_version_number,
540 creation_date,
541 created_by,
542 last_update_date,
543 last_updated_by,
544 last_update_login)
545 VALUES
546 (base_org_attr_id,
547 base_org_v_id,
548 new_entity_id,
549 18,
550 'N',
551 entity_base_org_id,
552 1,
553 sysdate,
554 user_id,
555 sysdate,
556 user_id,
557 login_id);
558
559 entity_type_code := 'O';
560 ELSE
561 entity_type_code := 'C';
562 END IF;
563
564 -- Insert the entity type information
565 INSERT INTO fem_entities_attr
566 (attribute_id,
567 version_id,
568 entity_id,
569 value_set_id,
570 aw_snapshot_flag,
571 dim_attribute_varchar_member,
572 object_version_number,
573 creation_date,
574 created_by,
575 last_update_date,
576 last_updated_by,
577 last_update_login)
578 VALUES
579 (entity_type_attr_id,
580 entity_type_v_id,
581 new_entity_id,
582 18,
583 'N',
584 entity_type_code,
585 1,
586 sysdate,
587 user_id,
588 sysdate,
589 user_id,
590 login_id);
591
592 -- Get and insert the contact information
593 get_next_token(l_local_clob, current_loc, entity_contact);
594
595 INSERT INTO fem_entities_attr
596 (attribute_id,
597 version_id,
598 entity_id,
599 value_set_id,
600 aw_snapshot_flag,
601 varchar_assign_value,
602 object_version_number,
603 creation_date,
604 created_by,
605 last_update_date,
606 last_updated_by,
607 last_update_login)
608 VALUES
609 (contact_attr_id,
610 contact_v_id,
611 new_entity_id,
612 18,
613 'N',
614 entity_contact,
615 1,
616 sysdate,
617 user_id,
618 sysdate,
619 user_id,
620 login_id);
621
622 -- Get and insert the logo information if provided
623 get_next_token(l_local_clob, current_loc, entity_logo);
624
625 IF trim(entity_logo) IS NOT NULL THEN
626 INSERT INTO fem_entities_attr
627 (attribute_id,
628 version_id,
629 entity_id,
630 value_set_id,
631 aw_snapshot_flag,
632 varchar_assign_value,
633 object_version_number,
634 creation_date,
635 created_by,
636 last_update_date,
637 last_updated_by,
638 last_update_login)
639 VALUES
640 (logo_attr_id,
641 logo_v_id,
642 new_entity_id,
643 18,
644 'N',
645 entity_logo,
646 1,
647 sysdate,
648 user_id,
649 sysdate,
650 user_id,
651 login_id);
652 END IF;
653
654 IF read_mode = 1 THEN
655 -- external operating entity
656 get_next_token(l_local_clob, current_loc, info_buffer);
657 entity_src_sys := to_number(info_buffer);
658
659 get_next_token(l_local_clob, current_loc, info_buffer);
660 entity_vs_combo_id := to_number(info_buffer);
661
662 get_next_token(l_local_clob, current_loc, info_buffer);
663 entity_cal_hier_id := to_number(info_buffer);
664
665 get_next_token(l_local_clob, current_loc, entity_def_ccy);
666
667 entity_ledger_id := create_ext_ledger(entity_src_sys,
668 entity_vs_combo_id,
669 entity_cal_hier_id,
670 entity_def_ccy,
671 entity_name,
672 entity_desc);
673
674 -- Now create a ledger based on the info, and get back the ledger_id
675 INSERT INTO fem_entities_attr
676 (attribute_id,
677 version_id,
678 entity_id,
679 value_set_id,
680 aw_snapshot_flag,
681 dim_attribute_numeric_member,
682 object_version_number,
683 creation_date,
684 created_by,
685 last_update_date,
686 last_updated_by,
687 last_update_login)
688 VALUES
689 (ledger_attr_id,
690 ledger_v_id,
691 new_entity_id,
692 18,
693 'N',
694 entity_ledger_id,
695 1,
696 sysdate,
697 user_id,
698 sysdate,
699 user_id,
700 login_id);
701
702 -- Get the Transform Rule Set and set the attribute if applicable
703 get_next_token(l_local_clob, current_loc, info_buffer);
704 entity_trs_obj_id := to_number(info_buffer);
705
706 IF entity_trs_obj_id IS NOT NULL THEN
707 INSERT INTO fem_entities_attr
708 (attribute_id,
709 version_id,
710 entity_id,
711 value_set_id,
712 aw_snapshot_flag,
713 dim_attribute_numeric_member,
714 object_version_number,
715 creation_date,
716 created_by,
717 last_update_date,
718 last_updated_by,
719 last_update_login)
720 VALUES
721 (trs_attr_id,
722 trs_v_id,
723 new_entity_id,
724 18,
725 'N',
726 entity_trs_obj_id,
727 1,
728 sysdate,
729 user_id,
730 sysdate,
731 user_id,
732 login_id);
733 END IF;
734
735 -- Get the Validation Rule Set and set the attribute if applicable
736 get_next_token(l_local_clob, current_loc, info_buffer);
737 entity_vrs_obj_id := to_number(info_buffer);
738
739 IF entity_vrs_obj_id IS NOT NULL THEN
740 INSERT INTO fem_entities_attr
741 (attribute_id,
742 version_id,
743 entity_id,
744 value_set_id,
745 aw_snapshot_flag,
746 dim_attribute_numeric_member,
747 object_version_number,
748 creation_date,
749 created_by,
750 last_update_date,
751 last_updated_by,
752 last_update_login)
753 VALUES
754 (vrs_attr_id,
755 vrs_v_id,
756 new_entity_id,
757 18,
758 'N',
759 entity_vrs_obj_id,
760 1,
761 sysdate,
762 user_id,
763 sysdate,
764 user_id,
765 login_id);
766 END IF;
767
768 ELSIF read_mode = 2 THEN
769 -- ogl operating entity
770 entity_src_sys := 10;
771
772 -- Get the Ledger and set the attribute
773 get_next_token(l_local_clob, current_loc, info_buffer);
774 entity_ledger_id := to_number(info_buffer);
775
776 INSERT INTO fem_entities_attr
777 (attribute_id,
778 version_id,
779 entity_id,
780 value_set_id,
781 aw_snapshot_flag,
782 dim_attribute_numeric_member,
783 object_version_number,
784 creation_date,
785 created_by,
786 last_update_date,
787 last_updated_by,
788 last_update_login)
789 VALUES
790 (ledger_attr_id,
791 ledger_v_id,
792 new_entity_id,
793 18,
794 'N',
795 entity_ledger_id,
796 1,
797 sysdate,
798 user_id,
799 sysdate,
800 user_id,
801 login_id);
802
803 -- Get the Balances Rule and set the attribute
804 get_next_token(l_local_clob, current_loc, info_buffer);
805 entity_bal_rule_id := to_number(info_buffer);
806
807 INSERT INTO fem_entities_attr
808 (attribute_id,
809 version_id,
810 entity_id,
811 value_set_id,
812 aw_snapshot_flag,
813 dim_attribute_numeric_member,
814 object_version_number,
815 creation_date,
816 created_by,
817 last_update_date,
818 last_updated_by,
819 last_update_login)
820 VALUES
821 (bal_rule_attr_id,
822 bal_rule_v_id,
823 new_entity_id,
824 18,
825 'N',
826 entity_bal_rule_id,
827 1,
828 sysdate,
829 user_id,
830 sysdate,
831 user_id,
832 login_id);
833
834 ELSIF read_mode = 3 THEN
835 -- consolidation entity
836 entity_src_sys := 70;
837
838 -- Get the Elimination Entity name and create all necessary rows
839 get_next_token(l_local_clob, current_loc, entity_elim_name);
840
841 SELECT FND_FLEX_VALUES_S.nextval INTO elim_entity_id FROM dual;
842
843 INSERT INTO fem_entities_attr
844 (attribute_id,
845 version_id,
846 entity_id,
847 value_set_id,
848 aw_snapshot_flag,
849 dim_attribute_numeric_member,
850 object_version_number,
851 creation_date,
852 created_by,
853 last_update_date,
854 last_updated_by,
855 last_update_login)
856 VALUES
857 (elim_attr_id,
858 elim_v_id,
859 new_entity_id,
860 18,
861 'N',
862 elim_entity_id,
863 1,
864 sysdate,
865 user_id,
866 sysdate,
867 user_id,
868 login_id);
869
870 INSERT INTO fem_entities_vl
871 (entity_id,
872 entity_display_code,
873 entity_name,
874 description,
875 value_set_id,
876 enabled_flag,
877 read_only_flag,
878 personal_flag,
879 object_version_number,
880 creation_date,
881 created_by,
882 last_update_date,
883 last_updated_by,
884 last_update_login)
885 VALUES
886 (elim_entity_id,
887 to_char(elim_entity_id),
888 entity_elim_name,
889 entity_elim_name,
890 18,
891 'Y',
892 'N',
893 'N',
894 1,
895 sysdate,
896 user_id,
897 sysdate,
898 user_id,
899 login_id);
900
901 INSERT INTO fem_entities_attr
902 (attribute_id,
903 version_id,
904 entity_id,
905 value_set_id,
906 aw_snapshot_flag,
907 dim_attribute_varchar_member,
908 object_version_number,
909 creation_date,
910 created_by,
911 last_update_date,
912 last_updated_by,
913 last_update_login)
914 VALUES
915 (entity_type_attr_id,
916 entity_type_v_id,
917 elim_entity_id,
918 18,
919 'N',
920 'E',
921 1,
922 sysdate,
923 user_id,
924 sysdate,
925 user_id,
926 login_id);
927
928 INSERT INTO fem_entities_attr
929 (attribute_id,
930 version_id,
931 entity_id,
932 value_set_id,
933 aw_snapshot_flag,
934 dim_attribute_numeric_member,
935 object_version_number,
936 creation_date,
937 created_by,
938 last_update_date,
939 last_updated_by,
940 last_update_login)
941 VALUES
942 (src_sys_attr_id,
943 src_sys_v_id,
944 elim_entity_id,
945 18,
946 'N',
947 70,
948 1,
949 sysdate,
950 user_id,
951 sysdate,
952 user_id,
953 login_id);
954
955 INSERT INTO fem_entities_attr
956 (attribute_id,
957 version_id,
958 entity_id,
959 value_set_id,
960 aw_snapshot_flag,
961 varchar_assign_value,
962 object_version_number,
963 creation_date,
964 created_by,
965 last_update_date,
966 last_updated_by,
967 last_update_login)
968 VALUES
969 (contact_attr_id,
970 contact_v_id,
971 elim_entity_id,
972 18,
973 'N',
974 entity_contact,
975 1,
976 sysdate,
977 user_id,
978 sysdate,
979 user_id,
980 login_id);
981
982 IF trim(entity_logo) IS NOT NULL THEN
983 INSERT INTO fem_entities_attr
984 (attribute_id,
985 version_id,
986 entity_id,
987 value_set_id,
988 aw_snapshot_flag,
989 varchar_assign_value,
990 object_version_number,
991 creation_date,
992 created_by,
993 last_update_date,
994 last_updated_by,
995 last_update_login)
996 VALUES
997 (logo_attr_id,
998 logo_v_id,
999 elim_entity_id,
1000 18,
1001 'N',
1002 entity_logo,
1003 1,
1004 sysdate,
1005 user_id,
1006 sysdate,
1007 user_id,
1008 login_id);
1009 END IF;
1010
1011 INSERT INTO fem_entities_attr
1012 (attribute_id,
1013 version_id,
1014 entity_id,
1015 value_set_id,
1016 aw_snapshot_flag,
1017 dim_attribute_varchar_member,
1018 object_version_number,
1019 creation_date,
1020 created_by,
1021 last_update_date,
1022 last_updated_by,
1023 last_update_login)
1024 VALUES
1025 (recon_leaf_attr_id,
1026 recon_leaf_v_id,
1027 elim_entity_id,
1028 18,
1029 'N',
1030 'N',
1031 1,
1032 sysdate,
1033 user_id,
1034 sysdate,
1035 user_id,
1036 login_id);
1037
1038 -- Get the Controlling Entity and set the attribute if applicable
1039 get_next_token(l_local_clob, current_loc, info_buffer);
1040 entity_cont_id := to_number(info_buffer);
1041
1042 IF entity_cont_id IS NOT NULL THEN
1043 INSERT INTO fem_entities_attr
1044 (attribute_id,
1045 version_id,
1046 entity_id,
1047 value_set_id,
1048 aw_snapshot_flag,
1049 dim_attribute_numeric_member,
1050 object_version_number,
1051 creation_date,
1052 created_by,
1053 last_update_date,
1054 last_updated_by,
1055 last_update_login)
1056 VALUES
1057 (cont_attr_id,
1058 cont_v_id,
1059 new_entity_id,
1060 18,
1061 'N',
1062 entity_cont_id,
1063 1,
1064 sysdate,
1065 user_id,
1066 sysdate,
1067 user_id,
1068 login_id);
1069 END IF;
1070
1071 END IF;
1072
1073 -- Insert the source system information
1074 INSERT INTO fem_entities_attr
1075 (attribute_id,
1076 version_id,
1077 entity_id,
1078 value_set_id,
1079 aw_snapshot_flag,
1080 dim_attribute_numeric_member,
1081 object_version_number,
1082 creation_date,
1083 created_by,
1084 last_update_date,
1085 last_updated_by,
1086 last_update_login)
1087 VALUES
1088 (src_sys_attr_id,
1089 src_sys_v_id,
1090 new_entity_id,
1091 18,
1092 'N',
1093 entity_src_sys,
1094 1,
1095 sysdate,
1096 user_id,
1097 sysdate,
1098 user_id,
1099 login_id);
1100
1101 -- Get and insert the security information
1102 get_next_token(l_local_clob, current_loc, entity_sec_by_role);
1103
1104 INSERT INTO fem_entities_attr
1105 (attribute_id,
1106 version_id,
1107 entity_id,
1108 value_set_id,
1109 aw_snapshot_flag,
1110 dim_attribute_varchar_member,
1111 object_version_number,
1112 creation_date,
1113 created_by,
1114 last_update_date,
1115 last_updated_by,
1116 last_update_login)
1117 VALUES
1118 (secure_attr_id,
1119 secure_v_id,
1120 new_entity_id,
1121 18,
1122 'N',
1123 entity_sec_by_role,
1124 1,
1125 sysdate,
1126 user_id,
1127 sysdate,
1128 user_id,
1129 login_id);
1130
1131 IF read_mode = 3 THEN
1132 -- consolidation entity
1133 INSERT INTO fem_entities_attr
1134 (attribute_id,
1135 version_id,
1136 entity_id,
1137 value_set_id,
1138 aw_snapshot_flag,
1139 dim_attribute_varchar_member,
1140 object_version_number,
1141 creation_date,
1142 created_by,
1143 last_update_date,
1144 last_updated_by,
1145 last_update_login)
1146 VALUES
1147 (secure_attr_id,
1148 secure_v_id,
1149 elim_entity_id,
1150 18,
1151 'N',
1152 entity_sec_by_role,
1153 1,
1154 sysdate,
1155 user_id,
1156 sysdate,
1157 user_id,
1158 login_id);
1159 END IF;
1160
1161 IF entity_sec_by_role = 'Y' THEN
1162 get_next_token(l_local_clob, current_loc, info_buffer);
1163 entity_nRoles := to_number(info_buffer);
1164 FOR counter IN 1 .. entity_nRoles LOOP
1165 get_next_token(l_local_clob, current_loc, entity_role_name);
1166
1167 -- Insert a single role assignment here
1168 INSERT INTO gcs_role_entity_relns
1169 (role_name,
1170 orig_system,
1171 orig_system_id,
1172 partition_id,
1173 entity_id,
1174 object_version_number,
1175 creation_date,
1176 created_by,
1177 last_update_date,
1178 last_updated_by,
1179 last_update_login)
1180 SELECT wlr.name,
1181 wlr.orig_system,
1182 wlr.orig_system_id,
1183 wlr.partition_id,
1184 load_entities.new_entity_id,
1185 1,
1186 sysdate,
1187 user_id,
1188 sysdate,
1189 user_id,
1190 login_id
1191 FROM wf_local_roles wlr
1192 WHERE wlr.name = entity_role_name;
1193
1194 IF read_mode = 3 THEN
1195 -- consolidation entity
1196 INSERT INTO gcs_role_entity_relns
1197 (role_name,
1198 orig_system,
1199 orig_system_id,
1200 partition_id,
1201 entity_id,
1202 object_version_number,
1203 creation_date,
1204 created_by,
1205 last_update_date,
1206 last_updated_by,
1207 last_update_login)
1208 SELECT wlr.name,
1209 wlr.orig_system,
1210 wlr.orig_system_id,
1211 wlr.partition_id,
1212 load_entities.elim_entity_id,
1213 1,
1214 sysdate,
1215 user_id,
1216 sysdate,
1217 user_id,
1218 login_id
1219 FROM wf_local_roles wlr
1220 WHERE wlr.name = entity_role_name;
1221 END IF;
1222 END LOOP;
1223 END IF;
1224
1225 -- Insert the recon_leaf_node_flag information
1226 INSERT INTO fem_entities_attr
1227 (attribute_id,
1228 version_id,
1229 entity_id,
1230 value_set_id,
1231 aw_snapshot_flag,
1232 dim_attribute_varchar_member,
1233 object_version_number,
1234 creation_date,
1235 created_by,
1236 last_update_date,
1237 last_updated_by,
1238 last_update_login)
1239 VALUES
1240 (recon_leaf_attr_id,
1241 recon_leaf_v_id,
1242 new_entity_id,
1243 18,
1244 'N',
1245 'N',
1246 1,
1247 sysdate,
1248 user_id,
1249 sysdate,
1250 user_id,
1251 login_id);
1252
1253 /***** Logic to insert the ACTUAL/ADB rows into GCS_ENTITIES_ATTR
1254 for all operating entities********/
1255 --Start - Code inserted by Santosh Matam Dated 16-jan-2006
1256 IF read_mode IN (1, 2) THEN
1257 --Insert one row for Actuals
1258 INSERT INTO gcs_entities_attr
1259 (entity_id,
1260 data_type_code,
1261 ledger_id,
1262 source_system_code,
1263 balances_rule_id,
1264 transform_rule_set_id,
1265 validation_rule_set_id,
1266 last_update_date,
1267 last_updated_by,
1268 creation_date,
1269 created_by,
1270 last_update_login,
1271 object_version_number,
1272 -- Bug fix : 5843592
1273 effective_start_date)
1274 (SELECT feb.entity_id,
1275 'ACTUAL',
1276 fea_ledger.dim_attribute_numeric_member ledger_id,
1277 fea_src.dim_attribute_numeric_member source_system_code,
1278 fea_bal_rule.dim_attribute_numeric_member balances_rule_id,
1279 --Bugfix 5087900
1280 trs.rule_set_id transform_rule_set_id,
1281 vrs.rule_set_id validation_rule_set_id,
1282 sysdate,
1283 user_id,
1284 sysdate,
1285 user_id,
1286 login_id,
1287 1,
1288 -- Bug fix : 5843592
1289 to_date('01-01-1900','dd-MM-yyyy')
1290 FROM fem_entities_b feb,
1291 fem_entities_attr fea_src,
1292 fem_entities_attr fea_ledger,
1293 fem_entities_attr fea_bal_rule,
1294 fem_entities_attr fea_trs,
1295 fem_entities_attr fea_vrs,
1296 gcs_lex_map_rule_sets trs,
1297 gcs_lex_map_rule_sets vrs
1298 WHERE fea_ledger.entity_id = feb.entity_id
1299 AND fea_ledger.attribute_id = ledger_attr_id
1300 AND fea_ledger.version_id = ledger_v_id
1301 AND fea_bal_rule.entity_id(+) = feb.entity_id
1302 AND fea_bal_rule.attribute_id(+) = bal_rule_attr_id
1303 AND fea_bal_rule.version_id(+) = bal_rule_v_id
1304 AND fea_trs.entity_id(+) = feb.entity_id
1305 AND fea_trs.attribute_id(+) = trs_attr_id
1306 AND fea_trs.version_id(+) = trs_v_id
1307 AND fea_vrs.entity_id(+) = feb.entity_id
1308 AND fea_vrs.attribute_id(+) = vrs_attr_id
1309 AND fea_vrs.version_id(+) = vrs_v_id
1310 AND fea_src.entity_id = feb.entity_id
1311 AND fea_src.attribute_id = src_sys_attr_id
1312 AND fea_src.version_id = src_sys_v_id
1313 AND trs.associated_object_id(+) = fea_trs.dim_attribute_numeric_member
1314 AND vrs.associated_object_id(+) = fea_vrs.dim_attribute_numeric_member
1315 AND feb.entity_id = new_entity_id);
1316
1317 --Insert one row for ADB
1318 INSERT INTO gcs_entities_attr
1319 (entity_id,
1320 data_type_code,
1321 ledger_id,
1322 source_system_code,
1323 balances_rule_id,
1324 transform_rule_set_id,
1325 validation_rule_set_id,
1326 last_update_date,
1327 last_updated_by,
1328 creation_date,
1329 created_by,
1330 last_update_login,
1331 object_version_number,
1332 -- Bug fix : 5843592
1333 effective_start_date)
1334 (SELECT feb.entity_id,
1335 'ADB',
1336 fea_ledger.dim_attribute_numeric_member ledger_id,
1337 fea_src.dim_attribute_numeric_member source_system_code,
1338 fea_bal_rule.dim_attribute_numeric_member balances_rule_id,
1339 --Bugfix 5087900
1340 trs.rule_set_id transform_rule_set_id,
1341 vrs.rule_set_id validation_rule_set_id,
1342 sysdate,
1343 user_id,
1344 sysdate,
1345 user_id,
1346 login_id,
1347 1,
1348 -- Bug fix : 5843592
1349 to_date('01-01-1900','dd-MM-yyyy')
1350 FROM fem_entities_b feb,
1351 fem_entities_attr fea_src,
1352 fem_entities_attr fea_ledger,
1353 fem_entities_attr fea_bal_rule,
1354 fem_entities_attr fea_trs,
1355 fem_entities_attr fea_vrs,
1356 gcs_lex_map_rule_sets trs,
1357 gcs_lex_map_rule_sets vrs
1358 WHERE fea_ledger.entity_id = feb.entity_id
1359 AND fea_ledger.attribute_id = ledger_attr_id
1360 AND fea_ledger.version_id = ledger_v_id
1361 AND fea_bal_rule.entity_id(+) = feb.entity_id
1362 AND fea_bal_rule.attribute_id(+) = bal_rule_attr_id
1363 AND fea_bal_rule.version_id(+) = bal_rule_v_id
1364 AND fea_trs.entity_id(+) = feb.entity_id
1365 AND fea_trs.attribute_id(+) = trs_attr_id
1366 AND fea_trs.version_id(+) = trs_v_id
1367 AND fea_vrs.entity_id(+) = feb.entity_id
1368 AND fea_vrs.attribute_id(+) = vrs_attr_id
1369 AND fea_vrs.version_id(+) = vrs_v_id
1370 AND fea_src.entity_id = feb.entity_id
1371 AND fea_src.attribute_id = src_sys_attr_id
1372 AND fea_src.version_id = src_sys_v_id
1373 AND trs.associated_object_id(+) = fea_trs.dim_attribute_numeric_member
1374 AND vrs.associated_object_id(+) = fea_vrs.dim_attribute_numeric_member
1375 AND feb.entity_id = new_entity_id);
1376
1377 END IF;
1378 --End - Code inserted by Santosh Matam Dated 16-jan-2006
1379 END IF;
1380
1381 END LOOP;
1382
1383 COMMIT;
1384
1385 -- Refresh Entity Value Set for FCH-ICM Integration
1386 l_request_id := fnd_request.submit_request(application => 'GCS',
1387 program => 'FCH_ICM_ENTITY_VS_MAINTAIN',
1388 sub_request => FALSE);
1389
1390 module_log_write(v_module, g_module_success);
1391
1392 END Load_Entities;
1393
1394 --
1395 -- GCS_ENTITY_CCTR_ORGS update
1396 --
1397
1398 PROCEDURE Update_Entity_Orgs(x_errbuf OUT NOCOPY VARCHAR2,
1399 x_retcode OUT NOCOPY VARCHAR2) IS
1400 v_module VARCHAR2(30);
1401 BEGIN
1402 v_module := 'Update_Entity_Orgs';
1403 module_log_write(v_module, g_module_enter);
1404
1405 --Added by Santosh - 5235164
1406 DELETE gcs_entity_cctr_orgs;
1407
1408 INSERT INTO gcs_entity_cctr_orgs
1409 (entity_id,
1410 company_cost_center_org_id,
1411 object_version_number,
1412 creation_date,
1413 created_by,
1414 last_update_date,
1415 last_updated_by,
1416 last_update_login)
1417 SELECT eo.entity_id,
1418 coa.company_cost_center_org_id,
1419 1,
1420 sysdate,
1421 eo.created_by,
1422 sysdate,
1423 eo.last_updated_by,
1424 eo.last_update_login
1425 FROM gcs_entity_organizations eo,
1426 fem_cctr_orgs_attr coa,
1427 fem_dim_attributes_b fdab,
1428 fem_dim_attr_versions_b fdavb
1429 WHERE coa.dim_attribute_numeric_member =
1430 eo.company_cost_center_org_id
1431 AND coa.attribute_id = fdab.attribute_id
1432 AND coa.version_id = fdavb.version_id
1433 AND fdab.attribute_varchar_label = 'COMPANY'
1434 AND fdavb.attribute_id = fdab.attribute_id
1435 AND fdavb.default_version_flag = 'Y';
1436
1437 module_log_write(v_module, g_module_success);
1438 END Update_Entity_Orgs;
1439
1440 END GCS_ENTITIES_PKG;