[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.person_id,a.effective_date,noa.order_of_processing,
a.pa_request_id,a.first_noa_code,a.object_version_number,
a.employee_last_name, a.employee_first_name,a.employee_national_identifier
FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
WHERE effective_date <= sysdate
AND pa_notification_id IS NULL
AND approval_date IS NOT NULL
AND a.pa_request_id = b.pa_request_id
AND noa.code = a.first_noa_code
AND c_poi =
(SELECT POEI_INFORMATION3 FROM per_position_extra_info
WHERE information_type = 'GHR_US_POS_GRP1'
AND position_id = NVL(a.to_position_id,a.from_position_id))
AND action_taken = 'FUTURE_ACTION'
AND EXISTS
(SELECT 1
FROM per_people_f per
WHERE per.person_id = a.person_id
AND a.effective_date BETWEEN
per.effective_start_date AND per.effective_end_date )
AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
FROM ghr_pa_routing_history
WHERE pa_request_id = a.pa_request_id)
ORDER BY person_id,effective_date,order_of_processing;
SELECT COUNT(*) fut_cnt
FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
WHERE effective_date <= sysdate
AND pa_notification_id IS NULL
AND approval_date IS NOT NULL
AND a.pa_request_id = b.pa_request_id
AND noa.code = a.first_noa_code
AND c_poi =
(SELECT POEI_INFORMATION3 FROM per_position_extra_info
WHERE information_type = 'GHR_US_POS_GRP1'
AND position_id = NVL(a.to_position_id,a.from_position_id))
AND action_taken = 'FUTURE_ACTION'
AND EXISTS
(SELECT 1
FROM per_people_f per
WHERE per.person_id = a.person_id
AND a.effective_date BETWEEN
per.effective_start_date AND per.effective_end_date )
AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
FROM ghr_pa_routing_history
WHERE pa_request_id = a.pa_request_id);
SELECT a.person_id,a.effective_date,noa.order_of_processing,
a.pa_request_id,a.first_noa_code,a.object_version_number,
a.employee_last_name, a.employee_first_name,a.employee_national_identifier
FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
WHERE effective_date <= sysdate
AND pa_notification_id IS NULL
AND approval_date IS NOT NULL
AND a.pa_request_id = b.pa_request_id
AND noa.code = a.first_noa_code
AND action_taken = 'FUTURE_ACTION'
AND EXISTS
(SELECT 1
FROM per_people_f per
WHERE per.person_id = a.person_id
AND a.effective_date BETWEEN
per.effective_start_date AND per.effective_end_date )
AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
FROM ghr_pa_routing_history
WHERE pa_request_id = a.pa_request_id)
ORDER BY person_id,effective_date,order_of_processing;
SELECT COUNT(*) fut_cnt
FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
WHERE effective_date <= sysdate
AND pa_notification_id IS NULL
AND approval_date IS NOT NULL
AND a.pa_request_id = b.pa_request_id
AND noa.code = a.first_noa_code
AND action_taken = 'FUTURE_ACTION'
AND EXISTS
(SELECT 1
FROM per_people_f per
WHERE per.person_id = a.person_id
AND a.effective_date BETWEEN
per.effective_start_date AND per.effective_end_date )
AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
FROM ghr_pa_routing_history
WHERE pa_request_id = a.pa_request_id);
SELECT max(completion_status) max_status
FROM GHR_MTS_TEMP
WHERE session_id = c_session_id;
g_request_ids.DELETE;
-- Loop through the future actions and insert them into the appropriate batch.
-- If the batch size exceeds the limit and if the record belongs to different
-- person, insert the following records into the next batch.
FOR l_c_futr_actions IN c_futr_actions LOOP
l_result := NULL;
INSERT INTO GHR_MTS_TEMP(session_id, batch_no, pa_request_id, action_type)
VALUES(l_session_id,l_batch_no,l_c_futr_actions.pa_request_id, 'FUTURE');
INSERT INTO GHR_MTS_TEMP(session_id, batch_no, pa_request_id, action_type)
VALUES(l_session_id,l_batch_no,l_c_futr_actions.pa_request_id, 'FUTURE');
-- Delete the temporary table data.
DELETE FROM GHR_MTS_TEMP
WHERE session_id = l_session_id;
DELETE FROM GHR_MTS_TEMP
WHERE session_id = l_session_id;
SELECT pa_request_id, batch_no
FROM GHR_MTS_TEMP
WHERE session_id = c_session_id
AND batch_no = c_batch_no;
SELECT *
FROM ghr_pa_requests
WHERE pa_request_id = c_pa_request_id;
select userenv('sessionid') sesid from dual;
SELECT par.object_version_number
FROM ghr_pa_requests par
WHERE par.pa_request_id = p_pa_request_id; -- 3769917
select ppf.business_group_id
from per_people_f ppf
where ppf.person_id = p_person_id
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
UPDATE fnd_sessions SET SESSION_ID = l_sid
WHERE SESSION_ID = l_sid;
INSERT INTO fnd_sessions
(SESSION_ID,EFFECTIVE_DATE)
VALUES
(l_sid,sysdate);
'Cause: The Personnel Action attempted to update the employee''s salary with a ' || l_new_line ||
'decreased amount of Basic Pay. ' || l_new_line ||
'Action: Please review the personnel action to verify the Grade and Step, Pay Table amounts,' || l_new_line ||
'and Pay Rate Determinant code for this employee.' ; -- Bug 3320086 Changed error message.
ghr_sf52_post_update.get_notification_details
(p_pa_request_id => l_sf52_rec.pa_request_id,
p_effective_date => l_sf52_rec.effective_date,
p_from_position_id => l_sf52_rec.from_position_id,
p_to_position_id => l_sf52_rec.to_position_id,
p_agency_code => l_sf52_rec.agency_code,
p_from_agency_code => l_sf52_rec.from_agency_code,
p_from_agency_desc => l_sf52_rec.from_agency_desc,
p_from_office_symbol => l_sf52_rec.from_office_symbol,
p_personnel_office_id => l_sf52_rec.personnel_office_id,
p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
p_to_office_symbol => l_sf52_rec.to_office_symbol
);
'Action: RPA related information has changed. Retrieve the RPA from the groupbox to review the refreshed information, make necessary changes, and update HR',1,2000);
l_error_message := 'Action: RPA related information has changed. Retrieve the RPA from the groupbox to review the refreshed information, make necessary changes, and update HR';--Bug#5634990
UPDATE GHR_MTS_TEMP
SET completion_status = p_retcode
WHERE session_id = p_session_id
AND batch_no = p_batch_no;
insert into ghr_process_log
(process_log_id
,program_name
,log_text
,message_name
,log_date
)
values
(ghr_process_log_s.nextval
,p_program_name
,p_log_text
,p_message_name
,p_log_date
);
SELECT 'X'
FROM ghr_pa_requests a, ghr_pa_routing_history b
WHERE a.effective_date between (c_effective_date - 2) and (c_effective_date + 1)
AND a.person_id = c_person_id
AND (substr(a.first_noa_code,1,1) = '5' OR a.first_noa_code IN ('760','762','765'))
AND pa_notification_id IS NULL
AND approval_date IS NOT NULL
AND a.pa_request_id = b.pa_request_id
AND action_taken = 'FUTURE_ACTION'
AND EXISTS
(SELECT 1
FROM per_people_f per
WHERE per.person_id = a.person_id
AND a.effective_date BETWEEN
per.effective_start_date AND per.effective_end_date )
AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
FROM ghr_pa_routing_history
WHERE pa_request_id = a.pa_request_id);
SELECT 'Y'
FROM ghr_pa_requests
WHERE effective_date between (c_effective_date - 14) and (c_effective_date + 1)
AND person_id = c_person_id
AND (substr(first_noa_code,1,1) = '5' OR first_noa_code IN ('760','762','765'))
AND pa_notification_id IS NOT NULL
AND (NVL(first_noa_cancel_or_correct,'C') <> 'CANCEL' OR NVL(second_noa_cancel_or_correct,'C') <> 'CANCEL');