[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pkh.text,
pkb.text
from user_source pkh,
user_source pkb
where pkh.name = upper(p_package_name)
and pkh.type = 'PACKAGE'
and pkh.line = 2
and pkb.name = upper(p_package_name)
and pkb.type = 'PACKAGE BODY'
and pkb.line = 2;
select ORACLE_USERNAME
from fnd_oracle_userid
where ORACLE_ID = 900;
select lower(data_type) data_type
from all_tab_columns
where table_name = upper(p_table_name)
and column_name = upper(p_column_name)
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner);
select tbl.table_id
,lower(tbl.table_name) table_name
,tbl.datetrack
,decode (tbl.surrogate_pk_column_name,NULL,'N',
'Y') surrogate_primary_key
,lower(tbl.surrogate_pk_column_name) surrogate_pk_column_name
,lower(tbl.table_alias) table_alias
,lower(tbl.short_name) short_name
,lower(who_link_alias) who_link_alias
,derive_sql_download_full
,derive_sql_download_add
,derive_sql_calc_ranges
,derive_sql_delete_source
,derive_sql_source_tables
,upper(tbl.global_data) global_data
,sequence_name
from hr_dm_tables tbl
where tbl.table_id = p_table_id;
p_table_info.derive_sql_delete_source :=
csr_get_table_rec.derive_sql_delete_source;
select lower(hir.column_name) column_name,
hir.parent_table_id parent_table_id,
lower(hir.parent_column_name) parent_column_name,
lower(hir.parent_id_column_name) parent_id_column_name,
lower(tbl.table_name) parent_table_name,
lower(tbl.table_alias) parent_table_alias
from hr_dm_tables tbl,
hr_dm_hierarchies hir
where hir.hierarchy_type = p_hierarchy_type
and tbl.table_id = hir.parent_table_id
and hir.table_id = (
select table_id
from hr_dm_tables
where table_name = (
select nvl(upload_table_name, table_name)
from hr_dm_tables
where table_id = p_table_info.table_id));
select ORACLE_USERNAME
from fnd_oracle_userid
where ORACLE_ID = 900;
select distinct lower(column_name) col_name,
lower(data_type) data_type
from all_tab_columns
where table_name = upper(p_table_info.table_name)
and column_name <> 'BATCH_ID'
and data_type <> 'SDO_GEOMETRY'
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner);
if csr_get_columns_rec.col_name = 'last_update_date' then
p_missing_who_info := 'N';
select distinct lower(atc.column_name) col_name,
lower(atc.data_type) data_type,
acc.position
from all_tab_columns atc,
all_cons_columns acc,
all_constraints ac
where ac.table_name = upper(p_table_info.table_name)
and ac.constraint_type = 'P'
and ac.CONSTRAINT_NAME = acc.constraint_name
and ac.owner = acc.owner
and atc.table_name = acc.table_name
and atc.column_name = acc.column_name
and atc.owner = acc.owner
and ac.owner in
(l_apps_owner,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by acc.position;
select lower(column_name) col_name
from hr_dm_hierarchies
where table_id = p_table_info.table_id
and hierarchy_type = 'P';
select lower(column_name) col_name
from hr_dm_hierarchies
where table_id = p_table_info.table_id
and hierarchy_type = 'H';
select distinct lower(atc.column_name) col_name,
lower(atc.data_type) data_type,
acc.position
from all_tab_columns atc,
all_cons_columns acc,
all_constraints ac
where ac.table_name = upper(p_table_info.table_name)
and ac.constraint_type = 'P'
and ac.CONSTRAINT_NAME = acc.constraint_name
and ac.owner = acc.owner
and atc.table_name = acc.table_name
and atc.column_name = acc.column_name
and atc.owner = acc.owner
and ac.owner in
(l_apps_owner,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by acc.position;
select ORACLE_USERNAME
from fnd_oracle_userid
where ORACLE_ID = 900;
select data_type,
nullable
from all_tab_columns
where table_name = upper(p_table_name)
and column_name = upper(p_column_name)
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner);
insert into hr_dm_resolve_pks
( resolve_pk_id,
source_database_instance,
table_name,
source_id,
destination_id
)
select
hr_dm_resolve_pks_s.nextval,
hr_dm_upload.g_data_migrator_source_db,
p_table_name,
p_source_id,
p_destination_id
from dual
where not exists (select null
from hr_dm_resolve_pks
where source_database_instance =
hr_dm_upload.g_data_migrator_source_db
and table_name = p_table_name
and source_id = p_source_id);
update hr_dm_resolve_pks
set destination_id = p_destination_id
where source_database_instance =
hr_dm_upload.g_data_migrator_source_db
and table_name = p_table_name
and source_id = p_source_id;
hr_dm_utility.message('INFO','HR_DM_LIBARARY - insert row into ' ||
'hr_dm_resolve_pks table ',15);
l_data_type hr_dm_dt_deletes.data_type%type;
select destination_id
from hr_dm_resolve_pks
where table_name = upper(p_table_name)
and source_id = p_source_id
and source_database_instance = hr_dm_upload.g_data_migrator_source_db;
procedure ins_dt_delete
( p_id in number default null,
p_table_name in varchar2,
p_ins_type in varchar2 ,
p_pk_column_1 in varchar2 default null,
p_pk_column_2 in varchar2 default null,
p_pk_column_3 in varchar2 default null,
p_pk_column_4 in varchar2 default null
) is
begin
hr_dm_utility.message('ROUT','entry:hr_dm_library.ins_dt_delete ', 5);
insert into hr_dm_dt_deletes ( dt_delete_id
,table_name
,data_type
,id_value
,pk_column_1
,pk_column_2
,pk_column_3
,pk_column_4
)
values ( hr_dm_dt_deletes_s.nextval
,p_table_name
,p_ins_type
,p_id
,p_pk_column_1
,p_pk_column_2
,p_pk_column_3
,p_pk_column_4);
hr_dm_utility.message('INFO','HR_DM_LIBARARY - insert row into ' ||
'hr_dm_dt_deletes table ',15);
hr_dm_utility.message('ROUT','exit:hr_dm_library.ins_dt_delete',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_library.ins_dt_delete',
'(none)','R');
end ins_dt_delete;
procedure chk_row_in_dt_delete
( p_id in number,
p_table_name in varchar2,
p_ins_type out nocopy varchar2,
p_row_exists out nocopy varchar2
) is
l_data_type hr_dm_dt_deletes.data_type%type;
cursor csr_find_dt_deletes is
select data_type
from hr_dm_dt_deletes
where id_value = p_id
and table_name = p_table_name
order by data_type;
hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete', 5);
open csr_find_dt_deletes;
fetch csr_find_dt_deletes into l_data_type ;
if csr_find_dt_deletes%found then
p_row_exists := 'Y';
close csr_find_dt_deletes;
close csr_find_dt_deletes;
'in hr_dm_dt_deletes table ',15);
hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete',
'(none)','R');
end chk_row_in_dt_delete;
procedure chk_row_in_dt_delete_1_pkcol
( p_pk_column_1 in number,
p_table_name in varchar2,
p_ins_type out nocopy varchar2,
p_row_exists out nocopy varchar2
) is
l_data_type hr_dm_dt_deletes.data_type%type;
cursor csr_find_dt_deletes is
select data_type
from hr_dm_dt_deletes
where pk_column_1 = p_pk_column_1
and table_name = p_table_name
order by data_type;
hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_1_pkcol', 5);
open csr_find_dt_deletes;
fetch csr_find_dt_deletes into l_data_type ;
if csr_find_dt_deletes%found then
p_row_exists := 'Y';
close csr_find_dt_deletes;
close csr_find_dt_deletes;
'in hr_dm_dt_deletes table ',15);
hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_1_pkcol',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_1_pkcol',
'(none)','R');
end chk_row_in_dt_delete_1_pkcol;
procedure chk_row_in_dt_delete_2_pkcol
( p_pk_column_1 in number,
p_pk_column_2 in number,
p_table_name in varchar2,
p_ins_type out nocopy varchar2,
p_row_exists out nocopy varchar2
) is
l_data_type hr_dm_dt_deletes.data_type%type;
cursor csr_find_dt_deletes is
select data_type
from hr_dm_dt_deletes
where pk_column_1 = p_pk_column_1
and pk_column_2 = p_pk_column_2
and table_name = p_table_name
order by data_type;
hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_2_pkcol', 5);
open csr_find_dt_deletes;
fetch csr_find_dt_deletes into l_data_type ;
if csr_find_dt_deletes%found then
p_row_exists := 'Y';
close csr_find_dt_deletes;
close csr_find_dt_deletes;
'in hr_dm_dt_deletes table ',15);
hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_2_pkcol',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_2_pkcol',
'(none)','R');
end chk_row_in_dt_delete_2_pkcol;
procedure chk_row_in_dt_delete_3_pkcol
( p_pk_column_1 in number,
p_pk_column_2 in number,
p_pk_column_3 in number,
p_table_name in varchar2,
p_ins_type out nocopy varchar2,
p_row_exists out nocopy varchar2
) is
l_data_type hr_dm_dt_deletes.data_type%type;
cursor csr_find_dt_deletes is
select data_type
from hr_dm_dt_deletes
where pk_column_1 = p_pk_column_1
and pk_column_2 = p_pk_column_2
and pk_column_3 = p_pk_column_3
and table_name = p_table_name
order by data_type;
hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_3_pkcol', 5);
open csr_find_dt_deletes;
fetch csr_find_dt_deletes into l_data_type ;
if csr_find_dt_deletes%found then
p_row_exists := 'Y';
close csr_find_dt_deletes;
close csr_find_dt_deletes;
'in hr_dm_dt_deletes table ',15);
hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_3_pkcol',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_3_pkcol',
'(none)','R');
end chk_row_in_dt_delete_3_pkcol;
procedure chk_row_in_dt_delete_4_pkcol
( p_pk_column_1 in number,
p_pk_column_2 in number,
p_pk_column_3 in number,
p_pk_column_4 in number,
p_table_name in varchar2,
p_ins_type out nocopy varchar2,
p_row_exists out nocopy varchar2
) is
l_data_type hr_dm_dt_deletes.data_type%type;
cursor csr_find_dt_deletes is
select data_type
from hr_dm_dt_deletes
where pk_column_1 = p_pk_column_1
and pk_column_2 = p_pk_column_2
and pk_column_3 = p_pk_column_3
and pk_column_4 = p_pk_column_4
and table_name = p_table_name
order by data_type;
hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_4_pkcol', 5);
open csr_find_dt_deletes;
fetch csr_find_dt_deletes into l_data_type ;
if csr_find_dt_deletes%found then
p_row_exists := 'Y';
close csr_find_dt_deletes;
close csr_find_dt_deletes;
'in hr_dm_dt_deletes table ',15);
hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_4_pkcol',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_4_pkcol',
'(none)','R');
end chk_row_in_dt_delete_4_pkcol;
select status
from user_objects
where upper(object_name) = upper(p_object_name)
and upper(object_type) = upper(p_object_type);
'in hr_dm_dt_deletes table ',15);
l_cursor_select_from varchar2(32767);
l_cursor_select_where varchar2(32767);
select ORACLE_USERNAME
from fnd_oracle_userid
where ORACLE_ID = 900;
select column_name
from all_tab_columns
where table_name = upper(p_table_info.upload_table_name)
and column_name not in ('BUSINESS_GROUP_ID','BATCH_ID')
and data_type <> 'SDO_GEOMETRY'
and not (table_name = 'FF_FORMULAS_F' and column_name = 'FORMULA_TEXT')
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by column_id;
select column_name
from all_tab_columns
where table_name = upper(p_table_info.upload_table_name)
and data_type <> 'SDO_GEOMETRY'
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by column_id;
select table_id
from hr_dm_tables
where table_name = upper(p_table_info.upload_table_name);
select distinct parent_table_id
from (select table_id,parent_table_id
from hr_dm_hierarchies
where hierarchy_type = 'PC')
start with table_id = l_nonnull_table_id
connect by prior parent_table_id = table_id;
l_cursor_select_from := p_table_info.upload_table_name ||
' ' || p_table_info.alias;
l_cursor_select_from := l_cursor_select_from || ' ' || ',' ||
l_parent_table_info.table_name ||' ' || l_parent_table_info.alias || l_cr;
l_cursor_select_where := 'where ' ||p_table_info.alias || '.' ||
'business_group_id is null';
p_where_clause => l_cursor_select_where);
'as select ' || l_cr ||
l_columns2 ||
'from ' || l_cursor_select_from || l_cr ||
l_cursor_select_where;
select distinct dm.table_name,
dm.table_id
from hr_dm_tables dm,
all_tab_columns tc
where dm.table_name not like 'FND%'
and dm.table_name like 'HR_DMV%'
and dm.table_name = tc.table_name
and tc.column_name = 'CREATED_BY'
and tc.owner in
(l_apps_owner,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner);
l_updated_table varchar2(30);
select table_id
into l_created_table
from hr_dm_tables
where table_name = 'HR_DM_FND_USERS_V1';
select table_id
into l_updated_table
from hr_dm_tables
where table_name = 'HR_DM_FND_USERS_V2';
insert into hr_dm_hierarchies
( hierarchy_id
,hierarchy_type
,sql_order
,table_id
,column_name
,parent_table_id
,parent_column_name
,parent_id_column_name)
select hr_dm_hierarchies_s.nextval
,'A'
,NULL
,l_table_id
,'CREATED_BY'
,l_created_table
,'CREATED_NAME'
,'USER_NAME_ID'
from dual
where not exists (select 'x'
from hr_dm_hierarchies hir
where hir.hierarchy_type = 'A'
and hir.table_id = l_table_id
and nvl(hir.column_name,'X') = 'CREATED_BY'
and nvl(hir.parent_table_id,-99) = l_created_table
and nvl(hir.parent_column_name,'X') = 'CREATED_NAME'
and nvl(hir.parent_id_column_name,'X') =
'USER_NAME_ID'
);
insert into hr_dm_hierarchies
( hierarchy_id
,hierarchy_type
,sql_order
,table_id
,column_name
,parent_table_id
,parent_column_name
,parent_id_column_name)
select hr_dm_hierarchies_s.nextval
,'A'
,NULL
,l_table_id
,'LAST_UPDATED_BY'
,l_updated_table
,'UPDATED_NAME'
,'USER_NAME_ID'
from dual
where not exists (select 'x'
from hr_dm_hierarchies hir
where hir.hierarchy_type = 'A'
and hir.table_id = l_table_id
and nvl(hir.column_name,'X') = 'LAST_UPDATED_BY'
and nvl(hir.parent_table_id,-99) = l_updated_table
and nvl(hir.parent_column_name,'X') = 'UPDATED_NAME'
and nvl(hir.parent_id_column_name,'X') =
'USER_NAME_ID'
);
select distinct dm.table_name,
dm.table_id
from hr_dm_tables dm,
all_tab_columns tc1,
all_tab_columns tc2
where dm.table_name not like 'FND%'
and dm.table_name like 'HR_DMV%'
and dm.table_name = tc1.table_name
and dm.table_name = tc2.table_name
and tc1.column_name = 'REQUEST_ID'
and tc2.column_name = 'PROGRAM_APPLICATION_ID'
and tc2.owner = tc1.owner
and tc1.owner in
(l_apps_owner,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner);
insert into hr_dm_hierarchies
( hierarchy_id
,hierarchy_type
,sql_order
,table_id
,column_name
,parent_table_id
,parent_column_name
,parent_id_column_name)
select hr_dm_hierarchies_s.nextval
,'N'
,NULL
,l_table_id
,'REQUEST_ID'
,NULL
,NULL
,NULL
from dual
where not exists (select 'x'
from hr_dm_hierarchies hir
where hir.hierarchy_type = 'N'
and hir.table_id = l_table_id
and nvl(hir.column_name,'X') = 'REQUEST_ID'
);
insert into hr_dm_hierarchies
( hierarchy_id
,hierarchy_type
,sql_order
,table_id
,column_name
,parent_table_id
,parent_column_name
,parent_id_column_name)
select hr_dm_hierarchies_s.nextval
,'N'
,NULL
,l_table_id
,'PROGRAM_APPLICATION_ID'
,NULL
,NULL
,NULL
from dual
where not exists (select 'x'
from hr_dm_hierarchies hir
where hir.hierarchy_type = 'N'
and hir.table_id = l_table_id
and nvl(hir.column_name,'X') =
'PROGRAM_APPLICATION_ID'
);
select ORACLE_USERNAME
from fnd_oracle_userid
where ORACLE_ID = 900;
select dmt.table_name,
dmt.upload_table_name
from hr_dm_tables dmt,
hr_dm_phase_items pi,
hr_dm_phases p
where p.phase_name = 'G'
and pi.phase_id = p.phase_id
and pi.table_name = dmt.table_name
and pi.status <> 'C'
and dmt.table_name like 'HR_DMV%'
and p.migration_id = p_migration_id;
select column_name
from all_tab_columns
where table_name = l_table_name
and column_name not in ('BUSINESS_GROUP_ID','BATCH_ID')
and data_type <> 'SDO_GEOMETRY'
and not (table_name = 'FF_FORMULAS_F' and column_name = 'FORMULA_TEXT')
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by column_id;
select column_name
from all_tab_columns
where table_name = l_table_name
and data_type <> 'SDO_GEOMETRY'
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by column_id;
select column_name
from all_tab_columns
where table_name = l_table_name
and column_name <> 'BATCH_ID'
and owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner)
order by column_id;
l_view_name || ' as select ' || l_cr ||
l_columns || 'from ' || l_table_name;