[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_id
from hr_operating_units
, fnd_product_groups
where product_group_id = 1
and multi_org_flag = 'Y'
union
select to_number(null)
from dual
where not exists (
select null
from fnd_product_groups
where multi_org_flag = 'Y')
order by 1;
select 'table exists',t.owner
into l_dummy,
O_table_owner
from user_synonyms s,
dba_tables t
where s.synonym_name = upper(X_table_name)
and t.owner = s.table_owner
and t.table_name = s.table_name
and rownum = 1;
select num_rows
into l_num_rows
from dba_tables
where table_name = x_table_name
and owner= l_table_owner;
select distinct column_name, column_id
from all_tab_columns tc
, user_synonyms us
where us.synonym_name = X_table_name
and tc.table_name = us.synonym_name
and tc.owner = us.table_owner
and tc.column_name <> 'ORG_ID'
and tc.data_type not in ('LONG', 'LONG RAW')
order by column_id;
select c.column_name, pkc.primary_key_sequence
from fnd_columns c
, fnd_primary_key_columns pkc
, fnd_primary_keys pk
, fnd_tables t
where t.application_id >= 0
and t.table_name = X_table_name
and pk.application_id = t.application_id
and pk.table_id = t.table_id
and pk.primary_key_type = 'D'
and pkc.application_id = pk.application_id
and pkc.table_id = pk.table_id
and pkc.primary_key_id = pk.primary_key_id
and c.application_id = pkc.application_id
and c.table_id = pkc.table_id
and c.column_id = pkc.column_id
and c.column_name <> 'ORG_ID'
order by pkc.primary_key_sequence;
' SELECT NULL FROM ' || X_table_name ||
' WHERE NVL(ORG_ID, -99) = NVL(:X_target_org_id, -99)';
statement := 'INSERT INTO ' || X_table_name || ' (' ||
column_list || 'ORG_ID) SELECT ' ||
column_list || ':X_target_org_id FROM ' || X_table_name || ' A' ||
where_clause;
select distinct column_name, column_id
from all_tab_columns tc
, user_synonyms us
where us.synonym_name = X_table_name
and tc.table_name = us.synonym_name
and tc.owner = us.table_owner
and tc.column_name <> 'ORG_ID'
and tc.data_type not in ('LONG', 'LONG RAW')
order by column_id;
select c.column_name, pkc.primary_key_sequence
from fnd_columns c
, fnd_primary_key_columns pkc
, fnd_primary_keys pk
, fnd_tables t
where t.application_id >= 0
and t.table_name = X_table_name
and pk.application_id = t.application_id
and pk.table_id = t.table_id
and pk.primary_key_type = 'D'
and pkc.application_id = pk.application_id
and pkc.table_id = pk.table_id
and pkc.primary_key_id = pk.primary_key_id
and c.application_id = pkc.application_id
and c.table_id = pkc.table_id
and c.column_id = pkc.column_id
and c.column_name <> 'ORG_ID'
order by pkc.primary_key_sequence;
' NOT IN (SELECT /*+ hash_aj parallel(b) */ ' || c_pk.column_name ||
', NVL(ORG_ID, -99) FROM ' || X_table_name || ' B)';
in_line_view := ' (SELECT /*+ no_merge */ ORGANIZATION_ID FROM' ||
' fnd_product_groups, hr_operating_units' ||
' WHERE product_group_id = 1' ||
' AND multi_org_flag = ''Y'' UNION' ||
' SELECT TO_NUMBER(NULL) FROM DUAL WHERE NOT EXISTS' ||
' (SELECT NULL FROM fnd_product_groups WHERE' ||
' multi_org_flag = ''Y'')) V';
statement := 'INSERT INTO ' || X_table_name || ' (' ||
column_list || 'ORG_ID)' ||
' SELECT /*+ ordered no_expand parallel(a) */ ' ||
column_list || ' V.ORGANIZATION_ID FROM ' || X_table_name ||
' A,' || in_line_view || where_clause;
select nvl(min(multi_org_flag),'N')
into is_org
from fnd_product_groups
where product_group_id = 1;
table_list.DELETE;
owner_list.DELETE;
view_list.DELETE;
appl_list.DELETE;
seed_data.DELETE;
conv_method.DELETE;
add_list('AP_INV_SELECTION_CRITERIA_ALL' , 'AP_INVOICE_SELECTION_CRITERIA' , 'SQLAP' ,'N','N');
add_list('AP_SELECTED_INVOICES_ALL' , 'AP_SELECTED_INVOICES' , 'SQLAP' ,'N','N');
add_list('AP_SELECTED_INVOICE_CHECKS_ALL' , 'AP_SELECTED_INVOICE_CHECKS' , 'SQLAP' ,'N','N');
add_list('JL_BR_AR_SELECT_ACCOUNTS_ALL' , 'JL_BR_AR_SELECT_ACCOUNTS' , 'JL' ,'N','N');
add_list('JL_BR_AR_SELECT_CONTROLS_ALL' , 'JL_BR_AR_SELECT_CONTROLS' , 'JL' ,'N','N');
add_list('PA_MASS_UPDATE_BATCHES_ALL' , 'PA_MASS_UPDATE_BATCHES' , 'PA' ,'N','N');
select count(*)
into l_num_org
from hr_operating_units
, fnd_product_groups
where product_group_id = 1
and multi_org_flag = 'Y';
select c.column_name, pkc.primary_key_sequence
from fnd_columns c
, fnd_primary_key_columns pkc
, fnd_primary_keys pk
, fnd_tables t
where t.application_id >= 0
and t.table_name = p_table
and pk.application_id = t.application_id
and pk.table_id = t.table_id
and pk.primary_key_type = 'D'
and pkc.application_id = pk.application_id
and pkc.table_id = pk.table_id
and pkc.primary_key_id = pk.primary_key_id
and c.application_id = pkc.application_id
and c.table_id = pkc.table_id
and c.column_id = pkc.column_id
and c.column_name <> 'ORG_ID'
order by pkc.primary_key_sequence;
statement := ' select '||''' '||''''||'||'||
nvl(pk_list, ''''||'no primary key'||'''')
||' pk_value '||
' from '||X_table||' a'||
' where org_id in (-3113, -3114)'||
' and decode(org_id, -3114, 1, '||X_num_org||') <> ( '||
' select count(*)'||
' from '||X_table||' b'||
' where '||nvl(where_clause, '1=1 ')||
' and nvl(b.org_id, -99) not in (-3113, -3114)'||
') order by 1';