[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(paa.employee_category,'X')
FROM per_all_assignments_f paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_id = ppa.payroll_id
AND ppa.effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT hoi.org_information7
FROM hr_organization_information hoi,
hr_soft_coding_keyflex hsk,
per_all_assignments_f paa
WHERE hoi.org_information_context = 'PER_IN_PF_DF'
AND hoi.organization_id = hsk.segment2
AND hsk.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND paa.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name ='ESI Eligible Salary'
AND pbd.database_item_suffix='_ASG_PTD' /* Bugfix 3844554 */
AND pbt.legislation_code='IN'
AND pdb.balance_type_id =pbt.balance_type_id
AND pdb.balance_dimension_id =pbd.balance_dimension_id;
SELECT GREATEST( MIN(ppa.date_earned),l_start)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf
WHERE paf.assignment_id =p_assignment_id
AND paf.assignment_id=paa.assignment_id
AND paa.payroll_action_id =ppa.payroll_action_id;
SELECT paa.assignment_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND last_day(ppa.date_earned) = l_virtual_date
AND paa.source_action_id IS NULL
AND ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')-- Added as a part of bug fix 4774108
AND ppa.action_type in ('Q','R','I','B')
ORDER BY paa.action_sequence DESC ;
select pdf.dis_information1
from per_disabilities_f pdf,
per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and paa.person_id = pdf.person_id
and l_esi_cont_date between paa.effective_start_date and paa.effective_end_date
and l_esi_cont_date between pdf.effective_start_date and pdf.effective_end_date
order by nvl(pdf.dis_information1,'N') desc;
SELECT GREATEST( MIN(ppa.date_earned),l_start)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf
WHERE paf.assignment_id =p_assignment_id
AND paf.assignment_id=paa.assignment_id
AND paa.payroll_action_id =ppa.payroll_action_id;
SELECT pap.accrual_plan_id
FROM pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE pee.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pap.accrual_plan_element_type_id = pet.element_type_id
AND pap.accrual_category = p_plan_category ;
SELECT decode(to_char(TPERIOD.end_date,'MM'),'04',1,'05',2,'06',3,
'07',4,'08',5,'09',6,
'10',7,'11',8,'12',9,
'01',10,'02',11,'03',12)
FROM per_time_periods TPERIOD,
per_time_period_types TPTYPE
WHERE TPERIOD.payroll_id = p_payroll_id
AND TPTYPE.period_type = TPERIOD.period_type
AND p_term_date between TPERIOD.start_date and TPERIOD.end_date;
SELECT count (*)
FROM pay_element_entry_values_f pev,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_input_values_f piv
WHERE pet.element_name like 'Deduction under Section 80DD'
AND pet.legislation_code = 'IN'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Disability Percentage'
AND piv.input_value_id = pev.input_value_id
AND pev.screen_entry_value = '80100'
AND pev.element_entry_id = pee.element_entry_id
AND pee.assignment_id = p_assignment_id
AND pee.element_type_id = pet.element_type_id
AND pee.entry_type = 'E'
AND p_date_earned BETWEEN pev.effective_start_date AND pev.effective_end_date
AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT DISTINCT(DECODE(pcr.contact_type,'JP_FT','Father','F','Father','S','Spouse')) relation_type
, ppf.sex sex
, ppf.marital_status marital_status
FROM per_people_f ppf
, per_assignments_f paf
, per_contact_relationships pcr
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = ppf.person_id
AND pcr.person_id = ppf.person_id
AND pcr.contact_type IN ('JP_FT','F',DECODE(ppf.marital_status,'M','S'))
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date AND ppf.effective_end_date
AND p_effective_date BETWEEN pcr.date_start AND NVL(pcr.date_end,TO_DATE('4712/12/31','YYYY/MM/DD'))
ORDER BY DECODE(pcr.contact_type,'JP_FT','Father','F','Father','S','Spouse');
PROCEDURE check_gre_update
(p_effective_date IN DATE
,p_dt_mode IN VARCHAR2
,p_assignment_id IN NUMBER
,p_gre_org IN VARCHAR2
,p_pf_org IN VARCHAR2
,p_esi_org IN VARCHAR2
,p_gre IN NUMBER
,p_pf IN NUMBER
,p_esi IN NUMBER
,p_message OUT NOCOPY VARCHAR2
,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
)
IS
-- The cursor to obtain the maximum payroll run date for an assignment in a BG.
CURSOR c_max_pay_date
IS
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND paa.source_action_id IS NULL
AND ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND paa.assignment_id = p_assignment_id
ORDER BY ppa.date_earned DESC;
SELECT ADD_MONTHS(LAST_DAY(p_effective_date),-1)+1
,LAST_DAY(p_effective_date)
FROM dual;
SELECT COUNT(DISTINCT scl.segment1)
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND ( asg.effective_start_date BETWEEN p_start_date AND p_end_date
OR
asg.effective_end_date BETWEEN p_start_date AND p_end_date
);
SELECT organization_id
FROM hr_organization_units
WHERE NAME = p_org_name
AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
SELECT asg.effective_start_date
FROM per_all_assignments_f asg
WHERE p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_id = p_assignment_id;
SELECT 1
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND scl.segment1 = p_latest_org_id
AND ( asg.effective_start_date BETWEEN p_start_date AND p_end_date
OR
asg.effective_end_date BETWEEN p_start_date AND p_end_date
);
l_procedure := g_package ||'check_gre_update';
END check_gre_update;
PROCEDURE check_pf_update
(p_effective_date IN DATE
,p_dt_mode IN VARCHAR2
,p_assignment_id IN NUMBER
,p_gre_org IN VARCHAR2
,p_pf_org IN VARCHAR2
,p_esi_org IN VARCHAR2
,p_message OUT NOCOPY VARCHAR2
,p_gre IN NUMBER
,p_pf IN NUMBER
,p_esi IN NUMBER
)
IS
l_procedure VARCHAR2(100);
l_procedure := g_package ||'check_pf_update';
check_gre_update
(p_effective_date => p_effective_date
,p_dt_mode => p_dt_mode
,p_assignment_id => p_assignment_id
,p_gre_org => p_gre_org
,p_pf_org => p_pf_org
,p_esi_org => p_esi_org
,p_gre => p_gre
,p_pf => p_pf
,p_esi => p_esi
,p_message => l_message_name
,p_token_name => p_token_name
,p_token_value => p_token_value
);
END check_pf_update;
PROCEDURE check_esi_update
(p_effective_date IN DATE
,p_dt_mode IN VARCHAR2
,p_assignment_id IN NUMBER
,p_esi_org IN VARCHAR2
,p_message OUT NOCOPY VARCHAR2
)
IS
BEGIN
NULL;
END check_esi_update;
Select object_version_number
From pay_element_entries_f
Where element_type_id = p_element_type_id
And assignment_id = p_assignment_id
And p_date Between effective_start_date and effective_end_date;
Select input_value_id
From pay_input_values_f
Where element_type_id = p_element_type_id
And p_date Between effective_start_date AND effective_end_date
And name=p_input_value;
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => 'UPDATE'
,p_effective_date => p_date+1
,p_business_group_id => p_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_ovn
,p_input_value_id4 => l_input_val_id
,p_entry_value4 => ' '
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warning
);
SELECT location_id
FROM hr_all_organization_units
WHERE organization_id = p_organization_id;
SELECT hl.loc_information16
FROM hr_all_organization_units hou
,hr_locations hl
WHERE hou.organization_id = p_organization_id
AND hou.location_id = hl.location_id
AND hl.style = 'IN';
SELECT hsc.segment2
,hsc.segment3
,hsc.segment4
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
WHERE paf.assignment_id = p_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND paf.business_group_id = p_business_group_id
AND p_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;