[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT edw_mapping_seq.NEXTVAL
INTO g_request_id
FROM DUAL;
l_stmt := 'select ''DIMENSION'' from EDW_DIMENSIONS_MD_V@'
|| g_target_link
|| ' where dim_name =:d '
|| ' union '
|| ' select ''FACT'' from EDW_FACTS_MD_V@'
|| g_target_link
|| ' where fact_name=:f ';
p_rows_inserted IN NUMBER
) is
Begin
wrapup (p_sucessful,p_rows_inserted,null,null,null);
p_rows_inserted IN NUMBER,
p_exception_msg IN VARCHAR2
) is
Begin
wrapup (p_sucessful,p_rows_inserted,p_exception_msg,null,null);
p_rows_inserted IN NUMBER,
p_period_start IN DATE,
p_period_end IN DATE
) is
Begin
wrapup (p_sucessful,p_rows_inserted,null,p_period_start,p_period_end);
p_rows_inserted IN NUMBER,
p_exception_msg IN VARCHAR2,
p_period_start IN DATE,
p_period_end IN DATE
)
IS
l_rows_inserted INTEGER := 0;
1. Direct insert into remote staging tables
(row count in local staging tables equal zero)
2. Data transportation from local staging tables to remote
staging tables using EDW Generic transportation model
Running PL/SQL table based push_to_target
*/
-- update function input parameter
BEGIN
l_rows_inserted := push_to_target;
'Rows Inserted into Interface Tables: '
|| p_rows_inserted,FND_LOG.LEVEL_STATEMENT
);
IF l_rows_inserted = -1
THEN
l_sucessful := FALSE;
ELSIF ( l_rows_inserted = 0 ) AND
(g_source_same_as_target = FALSE)
AND (p_rows_inserted > 0)
THEN
l_rows_inserted := p_rows_inserted;
( l_rows_inserted = 0 ) AND
(g_source_same_as_target = TRUE)
THEN
l_rows_inserted := p_rows_inserted;
p_no_of_records=> l_rows_inserted,
p_period_start=> p_period_start,
p_period_end=> p_period_end
);
'Inserted error message into the edw_push_detail_log',FND_LOG.LEVEL_ERROR
);
SELECT edw_language_code
INTO l_lang
FROM edw_local_system_parameters;
SELECT
meaning
INTO
l_meaning
FROM fnd_lookup_values_vl@edw_apps_to_wh
WHERE lookup_code= p_lookup_code
AND lookup_type= 'EDW_LEVEL_LOOKUP';
'SELECT
meaning
FROM fnd_lookup_values_vl@'
|| g_target_link
|| '
WHERE upper(lookup_type)= upper(:s1)
AND upper(lookup_code)= upper(:s2) ';
'SELECT
meaning
FROM fnd_lookup_values_vl@'
|| g_target_link
|| '
WHERE upper(lookup_type)= upper(:s1)
AND upper(lookup_code)= upper(:s2) ';
SELECT instance_code
INTO g_instance_code
FROM edw_local_instance;
l_stmt := 'select sysdate
from dual@'
|| g_target_link;
'Error inserting into local log table '
|| SQLERRM,FND_LOG.LEVEL_ERROR
);
'Insert into edw_push_detail_log a row for this collection run'
);
l_stmt := 'select relation_id from edw_relations_md_v@'
|| g_target_link
|| ' where relation_name=:s';
/* Insert into the Detail */
l_stmt :=
'INSERT INTO EDW_Push_Detail_Log@'
|| g_target_link
|| '(
INSTANCE_CODE,
PUSH_STATUS,
PUSH_START_DATE,
PUSH_END_DATE,
WH_PUSH_START_DATE,
WH_PUSH_END_DATE,
NO_OF_PUSHED_RECORDS,
PUSH_EXCEPTION_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATE_BY,
LAST_UPDATE_LOGIN,
PERIOD_START,
PERIOD_END, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE,
PUSH_CONCURRENT_ID)
VALUES( :x_instance, :x_status, :x_start, :x_end, :x_whstart, :x_whend,
:x_no_pushed, :x_message, :x_createdby, :x_creationdate, :x_lastupddate,
:x_lastupdby, :x_lastupdlogin, :x_period_start, :x_period_end,
:x_objname, :x_objid, :x_objtype, :x_concid)';
'Error inserting into local log table '
|| SQLERRM,FND_LOG.LEVEL_ERROR
);
l_stmt := 'select sysdate
from dual@'
|| g_target_link;
SELECT edw_language_code
INTO l_lang_code
FROM edw_local_system_parameters;
SELECT nls_language
INTO l_nls_language
FROM fnd_languages
WHERE language_code = l_lang_code;
'SELECT enabled_flag
FROM edw_source_instances_vl@'
|| g_target_link
|| '
WHERE instance_code= ( SELECT instance_code
FROM edw_local_instance)';
SELECT instance_code
INTO l_instance1
FROM edw_local_instance;
l_stmt := 'SELECT instance_code
FROM edw_local_instance@'
|| g_target_link;
OPEN cv FOR 'select count(1) from '
|| l_stgtbl_name
|| ' where COLLECTION_STATUS = ''LOCAL READY'' ';
'UPDATE '
|| l_stgtbl_name
|| ' SET COLLECTION_STATUS = ''READY''
WHERE COLLECTION_STATUS = ''LOCAL READY'' AND ROWNUM <= '
|| l_num;
<>
FOR i IN 1 .. CEIL (l_count / l_num)
LOOP
EXECUTE IMMEDIATE l_stmt;
END LOOP update_loop;
stmt := 'SELECT relation_name from edw_relations_md_v@'
|| g_target_link
|| ' where relation_long_name = :longname';
' select nvl(period_end, to_date(''01/01/1950'',''MM/DD/YYYY'')) '
|| ' from edw_push_detail_log@'
|| g_target_link
|| ' where object_name= :s and push_status=''SUCCESS'' and '
|| ' instance_code=(select instance_code from edw_local_instance)'
|| ' and last_update_date= ( select max(last_update_date)
from edw_push_detail_log@'
|| g_target_link
|| ' where object_name=:s and push_status=''SUCCESS'' and '
|| ' instance_code=(select instance_code from edw_local_instance))';
SELECT param.VALUE
FROM v$parameter param
WHERE param.NAME = 'global_names';
SELECT GLOBAL_NAME val
FROM GLOBAL_NAME;
SELECT 1
FROM dba_tables
WHERE table_name = p_tbl_name AND owner = g_bis_schema;
'SELECT stg.relation_name FROM edw_levels_md_v@'
|| g_target_link
|| ' ltc, edw_relationmapping_md_v@'
|| g_target_link
|| ' map, edw_relations_md_v@'
|| g_target_link
|| ' stg WHERE ltc.dim_name = :a'
|| ' AND map.targetdataentity = ltc.level_table_id'
|| ' AND map.sourcedataentity = stg.relation_id'
|| ' AND ltc.level_table_name = ltc.level_name||''_LTC''';
l_smt := 'SELECT stg.relation_name FROM edw_relations_md_v@'
|| g_target_link
|| ' stg, edw_relationmapping_md_v@'
|| g_target_link
|| ' map, edw_facts_md_v@'
|| g_target_link
|| ' fact WHERE fact.fact_name = :a'
|| ' AND map.targetdataentity = fact.fact_id'
|| ' AND stg.relation_id = map.sourcedataentity';
tablist.delete;
'Update of table'
|| l_stgtbl_name
|| ' failed',FND_LOG.LEVEL_ERROR
);
OPEN cv FOR 'select default_tablespace from dba_users where username=:u'
USING g_bis_schema;
OPEN cv FOR 'select initial_extent from dba_tablespaces where tablespace_name= :t'
USING g_op_tablespace;
OPEN cv FOR 'select count(1) from '
|| l_stgtbl_name
|| ' where collection_status in (''READY'',''LOCAL READY'')';
'SELECT column_name FROM all_tab_columns WHERE table_name =:t AND column_name not in ( ''COLLECTION_STATUS'',''REQUEST_ID'')'
|| ' AND owner = :o';
OPEN cv FOR 'select initial_extent, next_extent, partitioned from dba_tables where table_name= :t and owner =:o'
USING l_stgtbl_name, l_stg_owner;
|| ' as select /*+PARALLEL('
|| l_stgtbl_name
|| ', '
|| g_parallel
|| ') */ rowid row_id, 0 status from '
|| l_stgtbl_name
|| ' where collection_status in (''READY'', ''LOCAL READY'') ';
<>
WHILE l_pushcount <= CEIL (row_count / g_push_size)
LOOP
set_transaction_rbs (g_rbs);
'update '
|| g_bis_schema
|| '.'
|| l_tmptbl_name
|| ' set status =1 where status = 0 and rownum <='
|| g_push_size;
|| ' as select /*+ORDERED PARALLEL('
|| l_stgtbl_name
|| ', '
|| g_parallel
|| ') PARALLEL('
|| g_bis_schema
|| '.'
|| l_tmptbl_name
|| ', '
|| g_parallel
|| ') '
|| '*/ ''READY'' COLLECTION_STATUS, '
|| g_request_id
|| ' REQUEST_ID, '
|| l_scollist
|| ' from '
|| g_bis_schema
|| '.'
|| l_tmptbl_name
|| ' t, '
|| l_stgtbl_name
|| ' s where t.row_id = s.rowid and t.status = 1 ';
l_stmt := 'insert /*+APPEND PARALLEL('
|| l_stgtbl_name
|| '@'
|| g_target_link
|| ', '
|| g_parallel
|| ') '
|| '*/ into '
|| l_stgtbl_name
|| '@'
|| g_target_link
|| ' (COLLECTION_STATUS, REQUEST_ID, '
|| l_collist
|| ') '
|| ' select /*+PARALLEL('
|| g_bis_schema
|| '.'
|| l_optbl_name
|| ', '
|| g_parallel
|| ')*/'
|| ' COLLECTION_STATUS, REQUEST_ID, '
|| l_collist
|| ' FROM '
|| g_bis_schema
|| '.'
|| l_optbl_name;
l_stmt := 'update '
|| g_bis_schema
|| '.'
|| l_tmptbl_name
|| ' set status =2 where status = 1';
END LOOP bach_insert_loop;
'Direct insert into Remote Staging Tables. '
);
'insert into '
|| l_stgtbl_name
|| '@'
|| g_target_link
|| ' (COLLECTION_STATUS, REQUEST_ID, '
|| l_collist
|| ') /*+APPEND PARALLEL('
|| l_stgtbl_name
|| '@'
|| g_target_link
|| ', '
|| g_parallel
|| ')*/ '
|| ' select /*+PARALLEL('
|| l_stgtbl_name
|| ', '
|| g_parallel
|| ')*/ ''READY'','
|| g_request_id
|| ','
|| l_scollist
|| ' FROM '
|| l_stgtbl_name
|| ' s '
|| ' where s.COLLECTION_STATUS in (''READY'',''LOCAL READY'')';
|| ' rows are updated',FND_LOG.LEVEL_PROCEDURE);
select table_owner from user_synonyms where synonym_name = :s'
USING syn_name;
'select 1 from FND_COMMON_LOOKUPS where lookup_type=:a and lookup_code=:b';