[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 u.editions_enabled
into l_enabled
from fnd_oracle_userid f,
dba_users u
where f.read_only_flag='U'
and u.username=f.oracle_username;
select editions_enabled
into l_enabled
from dba_users
where username=p_username;
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 'table exists',t.owner
into l_dummy,
O_table_owner
from user_synonyms s,
dba_editioning_views t
where s.synonym_name = upper(X_table_name)
and t.owner = s.table_owner
and t.view_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 col.column_name, col.column_id
from user_synonyms syn, dba_tab_columns col
where syn.synonym_name = X_table_name
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name <> 'ORG_ID'
and col.data_type not in ('LONG', 'LONG RAW')
order by col.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 col.column_name, col.column_id
from user_synonyms syn, dba_tab_columns col
where syn.synonym_name = X_table_name
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name <>'ORG_ID'
and col.data_type not in ('LONG', 'LONG RAW')
order by col.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;
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';
' USING (SELECT '||column_list||' V.ORGANIZATION_ID '||
' FROM '||X_table_name||' A, '||in_line_view||
' where NVL(A.ORG_ID, -99) = nvl(:X_source_org_id, -99) '||
' AND NOT (ORG_ID = -3114 AND V.ORGANIZATION_ID IS NOT NULL) '||
' )src on '||
' (NVL(src.ORGANIZATION_ID,-99) = NVL(targ.ORG_ID, -99) ';
statement:= statement || ' ) WHEN NOT MATCHED THEN INSERT ( '||column_list||
' ORG_ID) VALUES(';
select nvl(min(multi_org_flag),'N')
into is_org
from fnd_product_groups
where product_group_id = 1;
update_book_id(X_org_id, X_table_name);
update_book_id(X_org_id, table_list(i));
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';
procedure update_book_id
(X_org_id in number,
X_table_name in varchar2)
is
book_id varchar2(20);
select count(*) into cnt
from dba_tab_columns
where table_name= X_table_name and
column_name='SET_OF_BOOKS_ID' and
nvl(owner, 'null') = nvl(owner, 'null');
select SET_OF_BOOKS_ID into book_id
from HR_OPERATING_UNITS where
ORGANIZATION_ID=X_org_id;
stmt:= 'update ' || X_table_name || ' set SET_OF_BOOKS_ID='||book_id||
' where ORG_ID='||X_org_id;
end update_book_id;