[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT RPAD (SUBSTR (MESSAGE_TEXT,
INSTR (MESSAGE_TEXT, '')
+ 7,
INSTR (MESSAGE_TEXT, '')
- INSTR (MESSAGE_TEXT, '')
- 7
),
30
)
|| ' : '
|| SUBSTR (MESSAGE_TEXT,
INSTR (MESSAGE_TEXT, '', 1, 2)
+ 7,
INSTR (MESSAGE_TEXT, '', 1, 2)
- INSTR (MESSAGE_TEXT, '', 1, 2)
- 7
) text
FROM pqh_process_log
WHERE txn_id = p_copy_entity_txn_id
ORDER BY process_log_id;
select cpe.information1 formula_id,
rpad(substr(cpe.information112,1,30),30) formula_name,
cpe.information112 full_formula_name,
ff_typ.formula_type_name formula_type_name,
cpe.information2 effective_start_date,
cpe.information3 effective_end_date
from ben_copy_entity_results cpe,
pqh_copy_entity_txns cet,
ff_formula_types ff_typ
where cet.copy_entity_txn_id = p_copy_entity_txn_id
and cet.copy_entity_txn_id = cpe.copy_entity_txn_id
and ff_typ.formula_type_id = cpe.information160
and cet.status = 'COMPLETE'
and cpe.table_alias = 'FFF'
and cpe.number_of_copies = 1
and cpe.dml_operation in ('INSERT','UPDATE')
and (cpe.datetrack_mode IN ('INSERT','CORRECTION')
or cpe.datetrack_mode like 'UPDATE%')
order by 1,2;
l_delete_failed VARCHAR2(30);
SELECT cet.object_version_number,
cet.src_effective_date,
cea.row_type_cd,
cea.information3 target_typ_cd,
cea.information4 reuse_object_flag,
cea.information1 prefix_suffix_text
FROM pqh_copy_entity_txns cet, pqh_copy_entity_attribs cea
WHERE cet.copy_entity_txn_id = cea.copy_entity_txn_id
AND cet.copy_entity_txn_id = p_copy_entity_txn_id;
ben_plan_design_wizard_api.update_result_rows(p_copy_entity_txn_id);
savepoint DELETE_REQUEST;
ben_plan_design_delete_api.call_delete_apis
( p_process_validate => p_validate
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_delete_failed => l_delete_failed
);
UPDATE ben_copy_entity_results cer
set number_of_copies = 0
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and l_txn_effective_date between nvl(information2,l_txn_effective_date) and nvl(information3,l_txn_effective_date)
and cer.dml_operation = 'DELETE';
rollback to DELETE_REQUEST;
l_delete_failed :='Y';
if(l_delete_failed ='Y') then
l_delete_failed:='N';
ben_plan_design_delete_api.call_delete_apis
( p_process_validate => p_validate
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_delete_failed => l_delete_failed
);
pqh_copy_entity_txns_api.update_copy_entity_txn (p_copy_entity_txn_id => p_copy_entity_txn_id,
p_datetrack_mode => hr_api.g_correction,
p_status => 'COMPLETE', /* To enable View Log Icon */
p_start_with => l_start_with,
p_object_version_number => l_cet_object_version_number,
p_effective_date => TRUNC (l_effective_date)
);
SAVEPOINT update_cet;
pqh_copy_entity_txns_api.update_copy_entity_txn (p_copy_entity_txn_id => p_copy_entity_txn_id,
p_datetrack_mode => hr_api.g_correction,
p_status => 'ERROR', /* To disable View Log Icon */
p_start_with => l_start_with, /* Bug 4415568 Enable Continue*/
p_object_version_number => l_cet_object_version_number,
p_effective_date => TRUNC (l_effective_date)
);
ROLLBACK TO update_cet;