[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
start_captiring_Updates procedure in psp_general package will be made
-- use_ld_enc varchar2(1) := FND_PROFILE.VALUE('PSP_ENC_ENABLE_QKUPD');
use_ld_enc varchar2(1) :=PSP_GENERAL.start_capturing_updates(bg_id);
PROCEDURE element_entries_inserts
(p_assignment_id IN NUMBER,
p_element_link_id IN NUMBER,
p_effective_date IN DATE)
IS
CURSOR element_cur IS
SELECT pel.element_type_id
FROM pay_element_links_f pel
WHERE pel.element_link_id = p_element_link_id
AND EXISTS (SELECT pee.element_type_id
FROM psp_enc_elements pee
WHERE pee.element_type_id = pel.element_type_id
AND pee.business_group_id = bg_id)
AND ROWNUM = 1;
SELECT PAYROLL_ID
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
--AND SYSDATE BETWEEN effective_start_date AND effective_end_date; Commented For Bug 3451760
SELECT 'Y'
FROM psp_enc_summary_lines
WHERE payroll_id = p_payroll_id
AND assignment_id = p_assignment_id
AND status_code IN ('A', 'N')
AND ROWNUM = 1;
INSERT INTO psp_enc_changed_assignments
(assignment_id, payroll_id, change_type, processed_flag)
VALUES (p_assignment_id, l_payroll_id, 'ET', NULL);
END element_entries_inserts;
PROCEDURE element_entries_updates
(p_assignment_id_o IN NUMBER,
p_element_link_id_o IN NUMBER,
p_effective_date IN DATE)
IS
l_element_type_id NUMBER DEFAULT NULL;
SELECT pel.element_type_id
FROM pay_element_links_f pel
WHERE pel.element_link_id = p_element_link_id_o
AND EXISTS (SELECT pee.element_type_id
FROM psp_enc_elements pee
WHERE pee.element_type_id = pel.element_type_id
AND pee.business_group_id = bg_id )
AND ROWNUM = 1;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id_o
--AND SYSDATE BETWEEN effective_start_date AND effective_end_date; Commented for Bug 3451760
SELECT 'Y'
FROM psp_enc_summary_lines
WHERE payroll_id = p_payroll_id
AND assignment_id = p_assignment_id_o
AND status_code IN ('A', 'N')
AND ROWNUM = 1;
INSERT INTO psp_enc_changed_assignments
(assignment_id, payroll_id, change_type, processed_flag)
VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL);
END element_Entries_updates;
PROCEDURE element_entries_deletes
(p_assignment_id_o IN NUMBER,
p_element_link_id_o IN NUMBER,
p_effective_date IN DATE)
IS
l_element_type_id NUMBER DEFAULT NULL;
SELECT pel.element_type_id
FROM pay_element_links_f pel
WHERE pel.element_link_id = p_element_link_id_o
AND EXISTS (SELECT pee.element_type_id
FROM psp_enc_elements pee
WHERE pee.element_type_id = pel.element_type_id
AND pee.business_group_id = bg_id )
AND ROWNUM = 1;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id_o
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM psp_enc_summary_lines
WHERE payroll_id = p_payroll_id
AND assignment_id = p_assignment_id_o
AND status_code IN ('A', 'N')
AND ROWNUM = 1;
/* INSERT INTO psp_enc_changed_assignments
(assignment_id, payroll_id, change_type, processed_flag)
VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL); */
INSERT INTO psp_enc_changed_assignments
(assignment_id, payroll_id, change_type, processed_flag)
VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL);
END element_entries_deletes;
Function Name :assignment_updates
Purpose :Dynamic Trigger Implementation, this function is called from After Row trigger
Update dynamic trigger PSP_ASG_CHANGES_ARU.
Date Of Creation:23-07-2003
Bug:3075435
*******************************************************************************/
PROCEDURE assignment_updates
(p_old_payroll_id IN NUMBER,
p_new_payroll_id IN NUMBER,
p_old_organization_id IN NUMBER,
p_new_organization_id IN NUMBER,
p_old_asg_status_type_id IN NUMBER,
p_new_asg_status_type_id IN NUMBER,
p_new_assignment_id IN NUMBER,
p_new_period_of_service_id IN NUMBER,
p_new_effective_end_date IN DATE,
p_new_primary_flag IN VARCHAR2,
p_new_person_id IN NUMBER,
p_old_grade_id IN NUMBER, -- for bug 4719330
p_new_grade_id IN NUMBER) -- for bug 4719330 )
IS
p_actual_date DATE;
select 'Y'
from psp_enc_payrolls
where payroll_id = l_payroll_id ;
SELECT 'Y'
FROM psp_enc_summary_lines
WHERE payroll_id = p_payroll_id
AND assignment_id = p_new_assignment_id
AND status_code IN ('A', 'N')
AND ROWNUM = 1;
hr_utility.trace('LD1 Entering assignment_updates PROC');
/* introduced the code to check only chages for those payroll selected in Encumbrance payroll
form are stored */
IF ( p_new_payroll_id is not null ) THEN
OPEN check_enc_run_csr(p_new_payroll_id);
assignment_status,theb do not insert. If old and new payroll values are different or if the
organization or assignment_status is different insert a record. Any update of a date tracked
record in per_all_assignments_f results in both in an INSERT as well as UPDATE operation */
/* Commented the following code and Break the If condition into if elsif conditions
IF (NVL(p_old_payroll_id,0) <> NVL(p_new_payroll_id,0) OR
p_old_organization_id <> p_new_organization_id OR
p_old_asg_status_type_id <> p_new_asg_status_type_id )
THEN
End of commenting for Bug 3466753 */
IF (NVL(p_old_payroll_id,0) <> NVL(p_new_payroll_id,0)) THEN
IF (check_new_enc_run_flag = 'Y') THEN
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
select count(*)
into l_count
from per_assignment_status_types
where p_new_asg_status_type_id = assignment_status_type_id
and per_system_status = 'TERM_ASSIGN';
select count(*)
into l_count1
from psp_enc_summary_lines
where status_code = 'A'
and person_id = p_new_person_id
and award_id is not null
and effective_date > p_new_effective_end_date; --- added date check for 3413373
select count(*)
into l_count2
from psp_enc_summary_lines
where status_code = 'N'
and person_id = p_new_person_id
and award_id is not null
and effective_date > p_new_effective_end_date; --bug 5977888
hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
/* commented the following code as the insert did nit have a check
whether payroll id is null for Bug 3432995 */
-- INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
-- VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
SELECT actual_termination_date into p_actual_date
FROM PER_PERIODS_OF_SERVICE
WHERE person_id =p_new_person_id --- replaced for 3184075 to resolve ORA-4091
/* (SELECT paf.person_id
FROM per_assignments_f paf
WHERE paf.assignment_id = p_new_assignment_id) -- Introduced for bug fix 3263333 */
AND period_of_service_id = p_new_period_of_service_id ----replaced p_new_asg_status_type_id and also replaced <> with =
AND p_new_effective_end_date = actual_termination_date;
select count(*)
into l_count1
from psp_enc_summary_lines
where status_code = 'A'
and person_id = p_new_person_id
and award_id is not null
and effective_date > p_new_effective_end_date; --- added date check for 3413373
select count(*)
into l_count2
from psp_enc_summary_lines
where status_code = 'N'
and person_id = p_new_person_id
and award_id is not null
and effective_date > p_new_effective_end_date; --bug 5977888
hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
/* INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL); */
INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag,chk_asg_end_date_flag)
VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL,'Y');
INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag,chk_asg_end_date_flag)
VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL,'Y');
End assignment_updates;
Function Name :assignment_deletes
Purpose :Dynamic Trigger Implementation, this function is called from After Row
Delete dynamic trigger PSP_ASG_CHANGES_ARD.
Date Of Creation:23-07-2003
BUg: 3075435
*******************************************************************************/
PROCEDURE assignment_deletes
(p_new_assignment_id IN NUMBER,
p_old_assignment_id IN NUMBER,
p_old_payroll_id IN NUMBER,
p_old_effective_start_date IN DATE,
p_old_person_id IN NUMBER)
IS
-- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
SELECT 'Y'
FROM psp_enc_summary_lines
WHERE payroll_id = p_payroll_id
AND assignment_id = p_old_assignment_id
AND status_code IN ('A', 'N')
AND ROWNUM = 1;
SELECT count(CURRENT_EMPLOYEE_FLAG)
FROM per_all_people_f where person_id = p_old_person_id
AND current_employee_flag = 'Y'
and effective_start_date = (select max(effective_start_date)
FROM per_all_people_f
where effective_start_date < p_old_effective_start_date
and person_id = p_old_person_id);
/* Insert the old value of assignment_id and payroll_id. Multiple records would be inserted here
if multiple date tracked records existed before. */
hr_utility.trace('LD10 Entering assignment_deletes PROC');
select count(*)
into l_count1
from psp_enc_summary_lines
where status_code = 'A'
and person_id = p_old_person_id
and award_id is not null
and effective_date > p_old_effective_start_date;
hr_utility.set_message(8403,'PSP_ENC_LIQ_BEFORE_DELETE');
select count(*)
into l_count2
from psp_enc_summary_lines
where status_code = 'N'
AND person_id = p_old_person_id
and award_id is not null
and effective_date > p_old_effective_start_date;
hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES(p_old_assignment_id,p_old_payroll_id,'AS',NULL);
END assignment_deletes;
Procedure Asig_grade_point_update
(p_assignment_id IN NUMBER,
p_new_effective_start_date IN DATE,
p_new_effective_end_date IN DATE ,
p_old_effective_end_date IN DATE)
IS
Cursor get_asg_payroll IS
select payroll_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_end_date >= p_new_effective_start_date
and effective_start_date <= p_old_effective_end_date ;
SELECT 'Y'
FROM psp_enc_summary_lines
WHERE payroll_id = p_payroll_id
AND assignment_id = p_assignment_id
AND status_code IN ('A', 'N')
AND ROWNUM = 1;
INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
VALUES (p_assignment_id,pay_rec.payroll_id,'AS',NULL);
END Asig_grade_point_update ;