[Home] [Help]
522: -- table for AOL type hierarchy i.e hierarchy type 'A'. But we added another
523: -- hierarchy type 'L' for looking up the ID value i.e use the corresponding id
524: -- value of the parent table at destination for a given column.
525: -- It populates the PL/SQL table with all columns details stored in a
526: -- hr_dm_hierarchies table for a given table and hierarchy type.
527: -- Input Parameters
528: -- p_hierarchy_type - 'A' - AOL type hierarchy
529: -- 'L' - lookup type hierarchy.
530: ----------------------------------------------------------------------------
542: lower(hir.parent_id_column_name) parent_id_column_name,
543: lower(tbl.table_name) parent_table_name,
544: lower(tbl.table_alias) parent_table_alias
545: from hr_dm_tables tbl,
546: hr_dm_hierarchies hir
547: where hir.hierarchy_type = p_hierarchy_type
548: and tbl.table_id = hir.parent_table_id
549: and hir.table_id = (
550: select table_id
814: l_pay_owner,
815: l_per_owner)
816: order by acc.position;
817:
818: -- get the logical primary key stored in hr_dm_hierarchies table
819: -- with type 'P'
820:
821: cursor csr_get_columns is
822: select lower(column_name) col_name
819: -- with type 'P'
820:
821: cursor csr_get_columns is
822: select lower(column_name) col_name
823: from hr_dm_hierarchies
824: where table_id = p_table_info.table_id
825: and hierarchy_type = 'P';
826:
827:
879: if l_count = 0 then
880: l_fatal_error_message := 'Could not find primary key for (' ||
881: 'p_table_name - ' || p_table_info.table_name || ').' ||
882: 'Define the logical' || ' primary key in ' ||
883: 'HR_DM_HIERARCHIES table.';
884: raise e_fatal_error;
885: end if;
886: end if;
887:
956: -- group id is not there in the table to be downloaded.
957:
958: cursor csr_get_columns is
959: select lower(column_name) col_name
960: from hr_dm_hierarchies
961: where table_id = p_table_info.table_id
962: and hierarchy_type = 'H';
963:
964: -- cursor to get the primary column name and data type
3055:
3056: cursor csr_get_table is
3057: select distinct parent_table_id
3058: from (select table_id,parent_table_id
3059: from hr_dm_hierarchies
3060: where hierarchy_type = 'PC')
3061: start with table_id = l_nonnull_table_id
3062: connect by prior parent_table_id = table_id;
3063:
3268: where table_name = 'HR_DM_FND_USERS_V2';
3269:
3270: -- do inserts, if no data already exists
3271:
3272: insert into hr_dm_hierarchies
3273: ( hierarchy_id
3274: ,hierarchy_type
3275: ,sql_order
3276: ,table_id
3277: ,column_name
3278: ,parent_table_id
3279: ,parent_column_name
3280: ,parent_id_column_name)
3281: select hr_dm_hierarchies_s.nextval
3282: ,'A'
3283: ,NULL
3284: ,l_table_id
3285: ,'CREATED_BY'
3287: ,'CREATED_NAME'
3288: ,'USER_NAME_ID'
3289: from dual
3290: where not exists (select 'x'
3291: from hr_dm_hierarchies hir
3292: where hir.hierarchy_type = 'A'
3293: and hir.table_id = l_table_id
3294: and nvl(hir.column_name,'X') = 'CREATED_BY'
3295: and nvl(hir.parent_table_id,-99) = l_created_table
3297: and nvl(hir.parent_id_column_name,'X') =
3298: 'USER_NAME_ID'
3299: );
3300:
3301: insert into hr_dm_hierarchies
3302: ( hierarchy_id
3303: ,hierarchy_type
3304: ,sql_order
3305: ,table_id
3306: ,column_name
3307: ,parent_table_id
3308: ,parent_column_name
3309: ,parent_id_column_name)
3310: select hr_dm_hierarchies_s.nextval
3311: ,'A'
3312: ,NULL
3313: ,l_table_id
3314: ,'LAST_UPDATED_BY'
3316: ,'UPDATED_NAME'
3317: ,'USER_NAME_ID'
3318: from dual
3319: where not exists (select 'x'
3320: from hr_dm_hierarchies hir
3321: where hir.hierarchy_type = 'A'
3322: and hir.table_id = l_table_id
3323: and nvl(hir.column_name,'X') = 'LAST_UPDATED_BY'
3324: and nvl(hir.parent_table_id,-99) = l_updated_table
3407: exit when csr_table%notfound;
3408:
3409: -- do inserts, if no data already exists
3410:
3411: insert into hr_dm_hierarchies
3412: ( hierarchy_id
3413: ,hierarchy_type
3414: ,sql_order
3415: ,table_id
3416: ,column_name
3417: ,parent_table_id
3418: ,parent_column_name
3419: ,parent_id_column_name)
3420: select hr_dm_hierarchies_s.nextval
3421: ,'N'
3422: ,NULL
3423: ,l_table_id
3424: ,'REQUEST_ID'
3426: ,NULL
3427: ,NULL
3428: from dual
3429: where not exists (select 'x'
3430: from hr_dm_hierarchies hir
3431: where hir.hierarchy_type = 'N'
3432: and hir.table_id = l_table_id
3433: and nvl(hir.column_name,'X') = 'REQUEST_ID'
3434: );
3432: and hir.table_id = l_table_id
3433: and nvl(hir.column_name,'X') = 'REQUEST_ID'
3434: );
3435:
3436: insert into hr_dm_hierarchies
3437: ( hierarchy_id
3438: ,hierarchy_type
3439: ,sql_order
3440: ,table_id
3441: ,column_name
3442: ,parent_table_id
3443: ,parent_column_name
3444: ,parent_id_column_name)
3445: select hr_dm_hierarchies_s.nextval
3446: ,'N'
3447: ,NULL
3448: ,l_table_id
3449: ,'PROGRAM_APPLICATION_ID'
3451: ,NULL
3452: ,NULL
3453: from dual
3454: where not exists (select 'x'
3455: from hr_dm_hierarchies hir
3456: where hir.hierarchy_type = 'N'
3457: and hir.table_id = l_table_id
3458: and nvl(hir.column_name,'X') =
3459: 'PROGRAM_APPLICATION_ID'