[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_log_tab.DELETE;
DELETE FROM pqh_process_log
WHERE process_log_id IN
( SELECT process_log_id
FROM pqh_process_log
START WITH master_process_log_id IS NULL
AND txn_id = g_batch_id
AND module_cd = g_module_cd
CONNECT BY PRIOR process_log_id = master_process_log_id
);
insert start record in pqh_process_log with message_type_cd = 'START'
*/
-- Insert API call
pqh_process_log_api.create_process_log
(
p_validate => false
,p_process_log_id => l_process_log_id
,p_module_cd => g_module_cd
,p_txn_id => g_batch_id
,p_master_process_log_id => null
,p_message_text => 'Process Started'
,p_message_type_cd => 'START'
,p_batch_status => 'PENDING'
,p_batch_start_date => sysdate
,p_batch_end_date => null
,p_txn_table_route_id => null
,p_log_context => p_log_context
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_information6 => p_information6
,p_information7 => p_information7
,p_information8 => p_information8
,p_information9 => p_information9
,p_information10 => p_information10
,p_information11 => p_information11
,p_information12 => p_information12
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
,p_information21 => p_information21
,p_information22 => p_information22
,p_information23 => p_information23
,p_information24 => p_information24
,p_information25 => p_information25
,p_information26 => p_information26
,p_information27 => p_information27
,p_information28 => p_information28
,p_information29 => p_information29
,p_information30 => p_information30
,p_object_version_number => l_object_version_number
,p_effective_date => sysdate
);
we will also need this id to update in end_log
*/
g_master_process_log_id := l_process_log_id;
|| PROCEDURE : insert_log
||
------------------------------------------------------------------*/
PROCEDURE insert_log
(
p_message_type_cd IN pqh_process_log.message_type_cd%TYPE,
p_message_text IN pqh_process_log.message_text%TYPE,
p_information_category IN pqh_process_log.information_category%TYPE DEFAULT NULL,
p_information1 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information2 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information3 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information4 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information5 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information6 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information7 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information8 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information9 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information10 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information11 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information12 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information13 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information14 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information15 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information16 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information17 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information18 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information19 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information20 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information21 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information22 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information23 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information24 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information25 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information26 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information27 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information28 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information29 IN pqh_process_log.information1%TYPE DEFAULT NULL,
p_information30 IN pqh_process_log.information1%TYPE DEFAULT NULL
) IS
/*
Before inserting we check if row is already existing as the same node may have been
called more then once
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'insert_log';
SELECT 'Y', process_log_id
FROM pqh_process_log
WHERE txn_id = p_txn_id
AND master_process_log_id IS NOT NULL
START WITH master_process_log_id IS NULL
AND txn_id = g_batch_id
AND module_cd = g_module_cd
CONNECT BY prior process_log_id = master_process_log_id;
We will insert error for the current level in g_log_tab
*/
-- for first record in g_log_tab the master is g_master_process_log_id
l_master_process_log_id := g_master_process_log_id;
IF NVL(g_log_tab(i).insert_flag,'N') <> 'Y' THEN
*/
-- call insert API if l_row_exists <> 'Y
OPEN csr_row_exists(p_txn_id => g_log_tab(i).txn_id);
g_log_tab(i).insert_flag := 'Y';
hr_utility.set_location('Inserted log Process log id: '||l_process_log_id,25);
END insert_log;
g_log_tab.DELETE(p_level,l_max_level);
SELECT COUNT(*)
FROM pqh_process_log
WHERE message_type_cd = p_message_type_cd
START WITH master_process_log_id IS NULL AND txn_id = g_batch_id AND module_cd = g_module_cd
CONNECT BY PRIOR process_log_id = master_process_log_id;
update the 'start' record for this batch with message_type_cd = 'COMPLETE' and
update the batch_end_date with current date time
*/
UPDATE pqh_process_log
SET message_type_cd = 'COMPLETE',
message_text = fnd_message.get_string('PQH','PQH_PROCESS_COMPLETED'),
batch_status = l_status,
batch_end_date = sysdate
WHERE process_log_id = g_master_process_log_id;