[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select tct.transaction_category_id
, cet.copy_entity_txn_id
, tct.short_name
, cet.display_name name
, cet.datetrack_mode dt_mode
, tct.master_table_route_id
, to_char(nvl(cet.src_effective_date,sysdate),'RRRR/MM/DD HH24:MI:SS') effective_date
, cet.context -- application_id
, cec.context gbl_context
from pqh_copy_entity_txns cet
, pqh_transaction_categories_vl tct
, pqh_copy_entity_contexts cec
where tct.transaction_category_id = cet.transaction_category_id
and cec.transaction_short_name = tct.short_name
and cec.application_short_name is null
and cec.legislation_code is null
and cec.responsibility_key is null
and cet.copy_entity_txn_id = v_copy_entity_txn_id
for update of copy_entity_txn_id;
select trt.from_clause
, trt.where_clause
, trt.table_alias
from pqh_table_route trt
where trt.table_route_id = v_table_route_id;
select trt.from_clause
, trt.table_route_id
, trt.where_clause
, trt.table_alias
from pqh_table_route trt
, pqh_copy_entity_prefs cep
where cep.copy_entity_txn_id = v_copy_entity_txn_id
and cep.table_route_id = trt.table_route_id
and cep.select_flag = 'Y' ;
select cer.* , src.information1 src_information1
from pqh_copy_entity_results cer ,
pqh_copy_entity_results src
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and cer.src_copy_entity_result_id = src.copy_entity_result_id
and src.number_of_copies <> 0
and cer.number_of_copies = 1
and cer.result_type_cd = 'TARGET'
order by cer.src_copy_entity_result_id; -- to group targets by source.
select upper(att.column_name) column_name
, upper(att.column_type) column_type
, upper(sat.ddf_column_name) ddf_column_name
from pqh_attributes att
, pqh_special_attributes sat
, pqh_txn_category_attributes tca
, pqh_copy_entity_txns cet
where att.attribute_id = tca.attribute_id
and att.master_table_route_id = v_table_route_id
and tca.txn_category_attribute_id = sat.txn_category_attribute_id
and cet.transaction_category_id = tca.transaction_category_id
and sat.attribute_type_cd = v_attribute_type
and cet.copy_entity_txn_id = v_copy_entity_txn_id
and sat.ddf_column_name is not null
and att.enable_flag = 'Y'
and tca.select_flag = 'Y'
and sat.enable_flag = 'Y'
and sat.context = pqh_generic.g_gbl_context; --application_id
select pqh_generic.get_alias(upper(att.column_name)) column_name
, upper(att.column_type) column_type
, upper(sat1.ddf_column_name) ddf_column_name
, sat1.context context
, sat.context context_s
from pqh_attributes att
, pqh_special_attributes sat
, pqh_special_attributes sat1
, pqh_txn_category_attributes tca
, pqh_copy_entity_txns cet
where att.attribute_id = tca.attribute_id
and att.master_table_route_id = v_table_route_id
and tca.txn_category_attribute_id = sat.txn_category_attribute_id
and cet.transaction_category_id = tca.transaction_category_id
and sat.attribute_type_cd = 'CHANGEABLE'
and sat1.attribute_type_cd in ('DISPLAY','SEGMENT')
and sat.txn_category_attribute_id = sat1.txn_category_attribute_id
and att.enable_flag = 'Y'
and tca.select_flag = 'Y'
and sat.enable_flag = 'Y'
and sat1.enable_flag = 'Y'
and cet.copy_entity_txn_id = v_copy_entity_txn_id
and sat.context = sat1.context -- application_id
and sat.context = pqh_generic.g_gbl_context
and sat1.ddf_column_name is not null ;
select status
from pqh_copy_entity_results
where result_type_cd = 'TARGET'
and copy_entity_txn_id = p_copy_entity_txn_id
and status = v_status
and number_of_copies <> 0
and rownum < 2 ;
select table_route_id
from pqh_table_route
where table_alias like v_alias ;
select ddf_column_name
from pqh_special_attributes s
,pqh_txn_category_attributes c
,pqh_attributes a
where a.attribute_id = c.attribute_id
and c.txn_category_attribute_id = s.txn_category_attribute_id
and a.enable_flag = 'Y'
and c.select_flag = 'Y'
and s.enable_flag = 'Y'
and s.context = pqh_generic.g_gbl_context
and s.attribute_type_cd in ('SELECT', 'PARAMETER','DISPLAY')
and a.column_name like 'DATETRACK%MODE%'
and s.ddf_column_name is not null
and rownum < 2;
select cec.application_short_name, cec.legislation_code, cec.responsibility_key
from pqh_copy_entity_contexts cec
where cec.context = p_context
and cec.transaction_short_name = p_transaction_short_name;
select cec.context, cec.application_short_name, cec.legislation_code, cec.responsibility_key,
cef.function_type_cd, cef.pre_copy_function_name, cef.copy_function_name, cef.post_copy_function_name
from pqh_copy_entity_contexts cec, pqh_copy_entity_functions cef
where cec.context = cef.context
and cef.table_route_id = p_table_route_id
and cec.transaction_short_name = p_transaction_short_name;
execute immediate 'update pqh_copy_entity_results set '||i.ddf_column_name||' = '''||rec1.dt_mode
||''' where copy_entity_txn_id = '||to_char(p_copy_entity_txn_id)
||' and result_type_cd = ''TARGET'''
||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
update pqh_copy_entity_results
set status = 'COMPLETED'
where copy_entity_txn_id = rec1.copy_entity_txn_id
and result_type_cd = 'SOURCE'
and number_of_copies <> 0
and copy_entity_result_id not in (select src_copy_entity_result_id from pqh_copy_entity_results
where copy_entity_txn_id = rec1.copy_entity_txn_id
and result_type_cd = 'TARGET'
and number_of_copies <> 1 ) ;
update pqh_copy_entity_results
set status = 'TGT_P'
where copy_entity_txn_id = rec1.copy_entity_txn_id
and result_type_cd = 'TARGET'
and number_of_copies = 0
and status = 'TGT_ERR' ;
update pqh_copy_entity_txns
set status = nvl(l_status, 'COMPLETED')
where copy_entity_txn_id = rec1.copy_entity_txn_id;
update pqh_copy_entity_results
set status = 'COMPLETED'
where copy_entity_result_id = rec2.copy_entity_result_id ;
, p_reset_flag => l_reset_flag -- delete before populate flag
, p_source_flag => 'Y' ) ; -- specify source/target PLtable
, p_reset_flag => l_reset_flag -- delete before populate flag
, p_source_flag => 'N' ) ; -- specify source/target PLtable
, p_reset_flag => 'N' -- delete before populate flag
, p_source_flag => 'N' ) ; -- specify source/target PLtable
if (pqh_generic.g_gbl_context ='Global Position Update'
and l_dt_mode ='UPDATE_CHANGE_INSERT') then
upd_where :='Y';
update pqh_copy_entity_results
set number_of_copies = '0'
where copy_entity_result_id = rec2.copy_entity_result_id
and status in ('COMPLETED','DPT_ERR') ;
select pa.column_name
, pa.column_type
, pa.width
, get_alias(pa.column_name) param
, decode(upper(pa.column_type)
, 'D' ,'L_'||get_alias(pa.column_name)||' DATE'
, 'V' ,'L_'||get_alias(pa.column_name)||' VARCHAR2'||'('||pa.width||')'
, 'C' ,'L_'||get_alias(pa.column_name)||' CHAR' ||'('||pa.width||')'
, 'N' ,'L_'||get_alias(pa.column_name)||' NUMBER' ||decode(pa.width,'0','','('||pa.width||')')
, 'L' ,'L_'||get_alias(pa.column_name)||' LONG'
, 'B' ,'L_'||get_alias(pa.column_name)||' BOOLEAN'
, 'L_'||get_alias(pa.column_name)||' '||pa.column_type||'('||pa.width||')') var_def
from pqh_attributes pa
, pqh_txn_category_attributes tca
, pqh_copy_entity_txns cet
, pqh_special_attributes sat
where pa.master_table_route_id = p_table_route_id
and pa.attribute_id = tca.attribute_id
and cet.copy_entity_txn_id = p_copy_entity_txn_id
and cet.transaction_category_id = tca.transaction_category_id
and sat.txn_category_attribute_id = tca.txn_category_attribute_id
and sat.attribute_type_cd = v_attrib_type --SELECT/PARAMETER/PRIMARY_KEY
and pa.enable_flag = 'Y'
and tca.select_flag = 'Y'
and sat.enable_flag = 'Y'
and sat.context = pqh_generic.g_gbl_context ;
for e_rec in c_attrib('SELECT') loop
if c_attrib%rowcount = 1 then
l_column := e_rec.column_name ; -- columns for the cursor
l_string := assign_part(e_rec.param, 'SELECT') ; -- assignment string
l_string := l_string || assign_part(e_rec.param, 'SELECT') ; -- assignment string
l_pk_val := 'if pqh_generic.g_level = 1 then PQH_GENERIC.g_target_pk_table.delete;'
||'cursor c1 is select effective_date from fnd_sessions where session_id = userenv(''sessionid''); '
||'insert into fnd_sessions(session_id, effective_date)'
||' values (userenv(''sessionid''), nvl(l_effective_date, trunc(sysdate)) ); '
||'update fnd_sessions set effective_date=l_effective_date where session_id=userenv(''sessionid''); '
||'cursor c1 is select effective_date from fnd_sessions where session_id = userenv(''sessionid''); '
||'insert into fnd_sessions(session_id, effective_date)'
||' values (userenv(''sessionid''), nvl(l_effective_date, trunc(sysdate)) ); '
||'update fnd_sessions set effective_date=l_effective_date where session_id=userenv(''sessionid''); '
|| ' cursor c_at is select '
|| nvl(l_column,'')
|| ' from '
|| p_from_clause
|| ' where '
|| l_where
|| '; '
|| ' cursor c_at is select '
|| nvl(l_column,'')
|| ' from '
|| p_from_clause
|| ' where '
|| l_where
|| '; '
pqh_refresh_data.g_refresh_tab.delete;
PQH_GENERIC.g_target_pk_table.delete;
PQH_GENERIC.g_target_pk_table.delete;
|| 'select cer.'||p_ddf_column_name ||' value '
|| 'from pqh_copy_entity_results cer '
|| 'where cer.copy_entity_result_id = '||p_copy_entity_result_id ||' '
|| 'and cer.copy_entity_txn_id = '||p_copy_entity_txn_id ||'; '
select copy_entity_txn_id,
src_copy_entity_result_id
from pqh_copy_entity_results
where copy_entity_result_id = l_copy_entity_result_id ;
select copy_entity_txn_id,
copy_entity_result_id
from pqh_copy_entity_results
where copy_entity_result_id = l_src_id
and status in ( 'COMPLETED', 'DPT_ERR')
and status <> l_status ;
pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR',
p_message_text => pqh_generic.v_err );
update pqh_copy_entity_results
set status = 'DPT_ERR',
number_of_copies = 0
where copy_entity_result_id = l_copy_entity_result_id;
pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR',
p_message_text => pqh_generic.v_err );
update pqh_copy_entity_results
set status = 'TGT_ERR'
where copy_entity_result_id = l_copy_entity_result_id;
update pqh_copy_entity_results
set status = l_status
where copy_entity_result_id = i.copy_entity_result_id;
update pqh_copy_entity_txns
set status = l_status
where copy_entity_txn_id = i.copy_entity_txn_id
and status <> 'TGT_ERR';