[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT person_id ,
business_group_id ,
MAX(NVL(max_contr_allowed, 0)),
SUM(NVL(amt_contr, 0)) ,
SUM(NVL(includable_comp, 0))
FROM PAY_US_CONTRIBUTION_HISTORY
WHERE TO_NUMBER(TO_CHAR(date_from, 'YYYY')) = p_year
AND TO_NUMBER(TO_CHAR(date_to , 'YYYY')) = p_year
AND tax_unit_id = p_gre_id
GROUP BY person_id ,
business_group_id
HAVING (MAX(NVL(max_contr_allowed, 0)) < SUM(NVL(amt_contr, 0)) OR
MAX(NVL(max_contr_allowed, 0)) = 0 OR
SUM(NVL(includable_comp, 0)) = 0) ;
SELECT person_id ,
business_group_id ,
MAX(NVL(max_contr_allowed, 0)),
SUM(NVL(amt_contr, 0)) ,
SUM(NVL(includable_comp, 0))
FROM PAY_US_CONTRIBUTION_HISTORY
WHERE TO_NUMBER(TO_CHAR(date_from, 'YYYY')) = p_year
AND TO_NUMBER(TO_CHAR(date_to , 'YYYY')) = p_year
AND person_id = p_person_id
GROUP BY person_id ,
business_group_id
HAVING (MAX(NVL(max_contr_allowed, 0)) < SUM(NVL(amt_contr, 0)) OR
MAX(NVL(max_contr_allowed, 0)) = 0 OR
SUM(NVL(includable_comp, 0)) = 0) ;
SELECT person_id ,
business_group_id ,
MAX(NVL(max_contr_allowed, 0)),
SUM(NVL(amt_contr, 0)) ,
SUM(NVL(includable_comp, 0))
FROM PAY_US_CONTRIBUTION_HISTORY
WHERE TO_NUMBER(TO_CHAR(date_from, 'YYYY')) = p_year
AND TO_NUMBER(TO_CHAR(date_to , 'YYYY')) = p_year
AND person_id = p_person_id
AND tax_unit_id = p_gre_id
GROUP BY person_id ,
business_group_id
HAVING (MAX(NVL(max_contr_allowed, 0)) < SUM(NVL(amt_contr, 0)) OR
MAX(NVL(max_contr_allowed, 0)) = 0 OR
SUM(NVL(includable_comp, 0)) = 0) ;
SELECT full_name
INTO l_full_name
FROM per_people_f ppf,
per_person_types ppt
WHERE ppf.person_id = l_person_id
AND ppf.effective_start_date =
(SELECT MAX(a.effective_start_date)
FROM per_people_f a,
per_person_types b
WHERE TO_NUMBER(TO_CHAR(a.effective_start_date,'YYYY'))
<= p_year
AND TO_NUMBER(TO_CHAR(a.effective_end_date,'YYYY'))
>= p_year
AND a.person_id = ppf.person_id
AND a.person_type_id = b.person_type_id
AND a.business_group_id = l_business_group_id
AND b.system_person_type = 'EMP' )
AND ppf.business_group_id = l_business_group_id
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type = 'EMP' ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured while inserting in' ||
' PAY_US_CONTRIBUTION_HISTORY table for Person id = ' ||
TO_CHAR(l_person_id) || ' GRE = ' || TO_CHAR(l_tax_unit_id)
|| SUBSTR(SQLERRM, 1, 128) || ' ' || TO_CHAR(SQLCODE) ||
' in ' || l_proc);
hr_utility.set_location('Error occured while inserting in ' ||
' PAY_US_CONTRIBUTION_HISTORY table for Person id = ' ||
TO_CHAR(l_person_id) || ' GRE = ' || TO_CHAR(l_tax_unit_id)
|| SUBSTR(SQLERRM, 1, 128) || ' ' || TO_CHAR(SQLCODE) ||
' in ' || l_proc, 15);
pay_contribution_history_api.update_contribution_history
(
p_validate => false ,
p_contr_history_id => l_contr_history_id ,
p_amt_contr => l_amt_contr ,
p_max_contr_allowed => l_max_contr_allowed ,
p_includable_comp => l_includable_comp ,
p_object_version_number => l_object_version_number
);
SELECT /*+ USE_NL (pbd) */
pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_balance_types pbt ,
pay_defined_balances pdb ,
pay_balance_dimensions pbd
WHERE pbt.balance_name = p_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = p_dimension_name
AND pdb.business_group_id = p_business_group_id;
SELECT /*+ USE_NL (pbd) */
pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_balance_types pbt ,
pay_defined_balances pdb ,
pay_balance_dimensions pbd
WHERE pbt.balance_name = p_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = p_dimension_name;
SELECT contr_history_id ,
object_version_number
INTO p_contr_history_id,
p_ovn_number
FROM PAY_US_CONTRIBUTION_HISTORY
WHERE CONTR_TYPE = g_contr_type
AND TO_NUMBER(TO_CHAR(DATE_FROM, 'YYYY')) = p_year
AND TO_NUMBER(TO_CHAR(DATE_TO, 'YYYY')) = p_year
AND tax_unit_id = p_tax_unit_id
AND person_id = p_person_id
AND business_group_id = p_business_group_id;
SELECT /*+ INDEX (pet pay_element_types_f_pk)
INDEX (pel pay_element_links_f_n7)
INDEX (ppt per_person_types_pk)
INDEX (hsck HR_SOFT_CODING_KEYFLEX_PK)
USE_NL(hsck) */
DISTINCT paa.person_id ,
TO_NUMBER(hsck.segment1) ,
pap.full_name ,
paa.assignment_id ,
pee.element_link_id ,
pet.element_name ,
paa.business_group_id ,
pet.element_information1 ,
TO_CHAR(MAX(paa.effective_end_date), 'DD/MM/YYYY')
FROM per_assignments_f paa,
per_all_people_f pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
per_person_types ppt,
hr_soft_coding_keyflex hsck
WHERE paa.assignment_Type = 'E'
AND pap.person_id = paa.person_id
AND pap.person_type_id = ppt.person_Type_id
AND ppt.system_person_type = 'EMP'
AND pee.assignment_id = paa.assignment_id
AND pee.element_link_id = pel.element_link_id
AND pet.element_information_Category = 'US_PRE-TAX DEDUCTIONS'
AND pet.element_information1 = g_contr_type
AND pet.element_type_id = pel.element_type_id
AND (TO_NUMBER(TO_CHAR(pap.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pap.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(paa.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(paa.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pee.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pee.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pel.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pel.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pet.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pet.effective_end_date,'YYYY')) >= p_year )
AND pet.element_name NOT LIKE '%Special%'
AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND TO_NUMBER(hsck.segment1) = p_gre_id
AND pap.effective_start_date =
(SELECT MAX(effective_start_date)
FROM per_all_people_f a,
per_person_types b
WHERE a.person_type_id = b.person_type_id
AND a.person_id = pap.person_id
AND a.business_group_id = pap.business_group_id
AND (TO_NUMBER(TO_CHAR(pap.effective_start_date,'YYYY'))
<= p_year
AND TO_NUMBER(TO_CHAR(pap.effective_end_date,'YYYY'))
>= p_year )
AND b.system_person_type = 'EMP')
GROUP BY paa.person_id ,
TO_NUMBER(hsck.segment1) ,
pap.full_name ,
paa.assignment_id ,
pee.element_link_id ,
pet.element_name ,
paa.business_group_id ,
pet.element_information1
ORDER BY paa.person_id ,
paa.assignment_id ,
UPPER(pet.element_name) ;
SELECT DISTINCT paa.person_id ,
TO_NUMBER(hsck.segment1) ,
pap.full_name ,
paa.assignment_id ,
pee.element_link_id ,
pet.element_name ,
paa.business_group_id ,
pet.element_information1 ,
TO_CHAR(MAX(paa.effective_end_date), 'DD/MM/YYYY')
FROM per_assignments_f paa,
per_all_people_f pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
per_person_types ppt,
hr_soft_coding_keyflex hsck
WHERE paa.assignment_Type = 'E'
AND pap.person_id = paa.person_id
AND pap.person_type_id = ppt.person_Type_id
AND ppt.system_person_type = 'EMP'
AND pee.assignment_id = paa.assignment_id
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND pet.element_information_Category = 'US_PRE-TAX DEDUCTIONS'
AND pet.element_information1 = g_contr_Type
AND (TO_NUMBER(TO_CHAR(pap.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pap.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(paa.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(paa.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pee.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pee.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pel.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pel.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pet.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pet.effective_end_date,'YYYY')) >= p_year )
AND pet.element_name NOT LIKE '%Special%'
AND pap.person_id = p_person_id
AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND pap.effective_start_date =
(SELECT MAX(effective_start_date)
FROM per_all_people_f a,
per_person_types b
WHERE a.person_type_id = b.person_type_id
AND a.person_id = pap.person_id
AND a.business_group_id = pap.business_group_id
AND (TO_NUMBER(TO_CHAR(pap.effective_start_date,'YYYY'))
<= p_year
AND TO_NUMBER(TO_CHAR(pap.effective_end_date,'YYYY'))
>= p_year )
AND b.system_person_type = 'EMP')
GROUP BY paa.person_id ,
TO_NUMBER(hsck.segment1) ,
pap.full_name ,
paa.assignment_id ,
pee.element_link_id ,
pet.element_name ,
paa.business_group_id ,
pet.element_information1
ORDER BY TO_NUMBER(hsck.segment1),
paa.person_id ,
paa.assignment_id ,
UPPER(pet.element_name) ;
SELECT DISTINCT paa.person_id ,
TO_NUMBER(hsck.segment1) ,
pap.full_name ,
paa.assignment_id ,
pee.element_link_id ,
pet.element_name ,
paa.business_group_id ,
pet.element_information1 ,
TO_CHAR(MAX(paa.effective_end_date), 'DD/MM/YYYY')
FROM per_assignments_f paa,
per_all_people_f pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
per_person_types ppt,
hr_soft_coding_keyflex hsck
WHERE paa.assignment_Type = 'E'
AND pap.person_id = paa.person_id
AND pap.person_type_id = ppt.person_Type_id
AND ppt.system_person_type = 'EMP'
AND pee.assignment_id = paa.assignment_id
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND pet.element_information_Category = 'US_PRE-TAX DEDUCTIONS'
AND pet.element_information1 = g_contr_Type
AND (TO_NUMBER(TO_CHAR(pap.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pap.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(paa.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(paa.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pee.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pee.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pel.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pel.effective_end_date,'YYYY')) >= p_year )
AND (TO_NUMBER(TO_CHAR(pet.effective_start_date,'YYYY')) <= p_year AND
TO_NUMBER(TO_CHAR(pet.effective_end_date,'YYYY')) >= p_year )
AND pet.element_name NOT LIKE '%Special%'
AND pap.person_id = p_person_id
AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND TO_NUMBER(hsck.segment1) = p_gre_id
AND pap.effective_start_date =
(SELECT MAX(effective_start_date)
FROM per_all_people_f a,
per_person_types b
WHERE a.person_type_id = b.person_type_id
AND a.person_id = pap.person_id
AND a.business_group_id = pap.business_group_id
AND (TO_NUMBER(TO_CHAR(pap.effective_start_date,'YYYY'))
<= p_year
AND TO_NUMBER(TO_CHAR(pap.effective_end_date,'YYYY'))
>= p_year )
AND b.system_person_type = 'EMP')
GROUP BY paa.person_id ,
TO_NUMBER(hsck.segment1) ,
pap.full_name ,
paa.assignment_id ,
pee.element_link_id ,
pet.element_name ,
paa.business_group_id ,
pet.element_information1
ORDER BY TO_NUMBER(hsck.segment1),
paa.person_id ,
paa.assignment_id ,
UPPER(pet.element_name) ;
' not selected as this person has multiple elements of same type');
' not selected as this person has multiple elements of same type', 55);
hr_utility.set_location('Inserting into PAY_US_CONTRIBUTION_HISTORY', 85);