The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT assignment_id
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
SELECT business_group_id
FROM per_all_assignments_f paf
,pay_element_entries_f peef
WHERE peef.element_entry_id = p_element_entry_id
AND peef.assignment_id = paf.assignment_id;
PROCEDURE asg_check_update(p_assignment_id NUMBER,
p_assignment_type VARCHAR2,
p_effective_start_date DATE,
p_effective_end_date DATE,
p_asg_status_type_id NUMBER,
p_employment_category VARCHAR2,
p_soft_coding_keyflex_id NUMBER,
p_primary_flag VARCHAR) AS
CURSOR csr_asg_status IS
SELECT assignment_extra_info_id
,object_version_number
,aei_information2 last_reported_date
,aei_information3 event
,nvl(aei_information4,'X') value
,aei_information6 action_type
,aei_information7 first_change_date
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'ES_SS_REP'
AND aei_information5 <> 'Y';
SELECT sck.segment5
FROM hr_soft_coding_keyflex sck
WHERE sck.soft_coding_keyflex_id = p_soft_coding_keyflex_id;
SELECT per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = p_asg_status_type_id;
UPDATE per_assignment_extra_info
SET aei_information5 = 'Y'
,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
WHERE assignment_id = p_assignment_id
AND aei_information3 = 'AS'
AND aei_information_category = 'ES_SS_REP'
AND object_version_number = csr_extra_info.object_version_number;
UPDATE per_assignment_extra_info
SET aei_information5 = 'Y'
,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date-1)
,aei_information4 = p_asg_status_type_id
WHERE assignment_id = p_assignment_id
AND aei_information3 = 'TS'
AND aei_information_category = 'ES_SS_REP'
AND object_version_number = csr_extra_info.object_version_number;
UPDATE per_assignment_extra_info
SET aei_information5 = 'Y'
,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
WHERE assignment_id = p_assignment_id
AND aei_information3 = 'EC'
AND aei_information_category = 'ES_SS_REP'
AND object_version_number = csr_extra_info.object_version_number;
UPDATE per_assignment_extra_info
SET aei_information5 = 'Y'
,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
WHERE assignment_id = p_assignment_id
AND aei_information3 = 'CG'
AND aei_information_category = 'ES_SS_REP'
AND object_version_number = csr_extra_info.object_version_number;
END asg_check_update;
PROCEDURE asg_check_insert( p_assignment_id NUMBER,
p_assignment_type VARCHAR2,
p_effective_start_date DATE,
p_effective_end_date DATE,
p_asg_status_type_id NUMBER,
p_employment_category VARCHAR2,
p_soft_coding_keyflex_id NUMBER,
p_primary_flag VARCHAR) IS
CURSOR csr_check_asg IS
SELECT assignment_id
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id;
/* Call the update procedure to update the report type */
asg_check_update(p_assignment_id,
p_assignment_type,
p_effective_start_date,
p_effective_end_date,
p_asg_status_type_id,
p_employment_category,
p_soft_coding_keyflex_id,
p_primary_flag);
/* Insert the records directly into the table because the api used to insert the values uses savepoint*/
/* savepoint cannot be used in triggers */
INSERT INTO per_assignment_extra_info
(assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
object_version_number
)
VALUES
(per_assignment_extra_info_s.nextval,
p_assignment_id,
'ES_SS_REP',
'ES_SS_REP',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
'AS',
p_asg_status_type_id,
'Y',
'I',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
1
);
/* Inserting the Termination Status as One */
INSERT INTO per_assignment_extra_info
(assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
object_version_number
)
VALUES
(per_assignment_extra_info_s.nextval,
p_assignment_id,
'ES_SS_REP',
'ES_SS_REP',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
'TS',
p_asg_status_type_id,
'N',
'U',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
1
);
/* INSERT INTO per_assignment_extra_info
(assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
object_version_number
)
VALUES
(per_assignment_extra_info_s.nextval,
p_assignment_id,
'ES_SS_REP',
'ES_SS_REP',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
'EC',
p_employment_category,
'Y',
'I',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
1
);
INSERT INTO per_assignment_extra_info
(assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
object_version_number
)
VALUES
(per_assignment_extra_info_s.nextval,
p_assignment_id,
'ES_SS_REP',
'ES_SS_REP',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
'CG',
NULL,
'Y',
'I',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
1
);
END asg_check_insert;
PROCEDURE element_check_insert(p_element_entry_id NUMBER,
p_effective_start_date DATE,
p_effective_end_date DATE,
p_epigraph_code VARCHAR2,
p_input_value_id NUMBER) AS
CURSOR csr_chk_element_eit(p_assignment_id NUMBER) IS
SELECT assignment_extra_info_id
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'ES_SS_REP'
AND aei_information3 IN ('EP','EC');
SELECT input_value_id
FROM pay_input_values_f piv
WHERE piv.name = p_name
AND legislation_code = 'ES';
SELECT paf.primary_flag, paf.assignment_type
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_effective_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
element_check_update(p_element_entry_id,
p_effective_start_date,
p_effective_end_date,
p_epigraph_code,
p_input_value_id);
/*inserting for EPIGRAPH CODE EP*/
INSERT INTO per_assignment_extra_info
(assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
object_version_number
)
VALUES
(per_assignment_extra_info_s.nextval,
l_assg_id,
'ES_SS_REP',
'ES_SS_REP',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
'EP',
NULL,
'Y',
'I',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
1
);
/*Inserting for contract key change*/
INSERT INTO per_assignment_extra_info
(assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
object_version_number
)
VALUES
(per_assignment_extra_info_s.nextval,
l_assg_id,
'ES_SS_REP',
'ES_SS_REP',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
'EC',
NULL,
'Y',
'I',
fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
1
);
END element_check_insert;
PROCEDURE element_check_update(p_element_entry_id NUMBER,
p_effective_start_date DATE,
p_effective_end_date DATE,
p_epigraph_code VARCHAR2,
p_input_value_id NUMBER) AS
CURSOR csr_get_eit_value(p_assignment_id NUMBER
,p_event_type VARCHAR2) IS
SELECT assignment_extra_info_id
,object_version_number
,aei_information2 last_reported_date
,nvl(aei_information4,'X') value
,aei_information6 action_type
,aei_information7 last_changed_date
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'ES_SS_REP'
AND aei_information3 = p_event_type
AND aei_information5 <> 'Y';
SELECT input_value_id
FROM pay_input_values_f piv
WHERE piv.name = p_name
AND legislation_code = 'ES';
SELECT paf.primary_flag, paf.assignment_type
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_effective_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
UPDATE per_assignment_extra_info
SET aei_information5 = 'Y'
,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
WHERE assignment_id = l_assignment_id
AND aei_information_category = 'ES_SS_REP'
AND aei_information3 = 'EP'
AND object_version_number = csr_extra_info.object_version_number;
UPDATE per_assignment_extra_info
SET aei_information5 = 'Y'
,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
WHERE assignment_id = l_assignment_id
AND aei_information_category = 'ES_SS_REP'
AND aei_information3 = 'EC'
AND object_version_number = csr_extra_info.object_version_number;
END element_check_update;