[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(paa.action_sequence)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = p_process_type
AND paa.assignment_id = p_assignment_id
AND TRUNC(ppa.effective_date,'MM') = TRUNC(p_effective_date,'MM');
p_token_name.delete;
p_token_value.delete;
SELECT prt.run_type_name into l_run_type_name
FROM pay_run_types_f prt,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND prt.run_type_id = paa.run_type_id
AND paa.assignment_action_id = p_assignment_action_id
AND ppa.effective_date between prt.effective_start_date
and prt.effective_end_date;
SELECT person_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT assignment_id
FROM per_assignments_f
WHERE person_id = p_person_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT '1'
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_context = 'CLASS'
AND org_information1 = p_org_class
AND org_information2 = 'Y';
SELECT hsc.segment1
,hsc.segment2
,hsc.segment3
,hsc.segment4
,hsc.segment5
,hsc.segment6
,hsc.segment7
,hsc.segment8
,hsc.segment9
,hsc.segment10
,hsc.segment11
,hsc.segment12
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;
SELECT business_group_id
FROM per_assignments_f
where assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT element_link_id,
payroll_id,
link_to_all_payrolls_flag,
organization_id,
position_id,
job_id,
grade_id,
location_id,
pay_basis_id,
employment_category,
people_group_id
FROM pay_element_links_f
WHERE element_type_id = p_element_type_id
and business_group_id = p_bg_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT payroll_id,
organization_id,
position_id,
job_id,
grade_id,
location_id,
pay_basis_id,
employment_category
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT 1
FROM pay_assignment_link_usages_f
WHERE assignment_id = p_assignment_id
AND element_link_id = p_element_link_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE (legislation_code = 'IN' OR business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT link.element_link_id
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
WHERE assgn.assignment_id = p_assignment_id
AND ((link.payroll_id IS NOT NULL AND link.payroll_id = assgn.payroll_id) OR
(link.link_to_all_payrolls_flag = 'Y' AND assgn.payroll_id IS NOT NULL) OR
(link.payroll_id IS NULL AND link.link_to_all_payrolls_flag = 'N'))
AND (link.organization_id = assgn.organization_id OR link.organization_id IS NULL)
AND (link.position_id = assgn.position_id OR link.position_id IS NULL)
AND (link.job_id = assgn.job_id OR link.job_id IS NULL)
AND (link.grade_id = assgn.grade_id OR link.grade_id IS NULL)
AND (link.location_id = assgn.location_id OR link.location_id IS NULL)
AND (link.pay_basis_id = assgn.pay_basis_id OR link.pay_basis_id IS NULL)
AND (link.employment_category = assgn.employment_category OR link.employment_category IS NULL)
AND (link.people_group_id IS NULL OR
EXISTS ( SELECT 1 FROM pay_assignment_link_usages_f usage
WHERE usage.assignment_id = assgn.assignment_id
AND usage.element_link_id = link.element_link_id
AND p_effective_date BETWEEN usage.effective_start_date AND usage.effective_end_date
))
AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
AND link.business_group_id = assgn.business_group_id
AND link.element_type_id = types.element_type_id
AND types.element_name = p_element_name
AND p_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND p_effective_date BETWEEN link.effective_start_date
AND link.effective_end_date
AND p_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date;
SELECT val.screen_entry_value
FROM pay_element_entry_values_f val
,pay_input_values_f inputs
WHERE val.input_value_id = inputs.input_value_id
AND val.element_entry_id = p_element_entry_id
AND inputs.name = p_input_name
AND inputs.legislation_code = 'IN'
AND p_effective_date between val.effective_start_date AND val.effective_end_date
AND p_effective_date between inputs.effective_start_date AND inputs.effective_end_date;
SELECT val.screen_entry_value
FROM pay_element_entry_values_f val
,pay_input_values_f inputs
WHERE val.input_value_id = inputs.input_value_id
AND val.element_entry_id = p_element_entry_id
AND inputs.name = p_input_name
AND inputs.legislation_code = 'IN';
SELECT 'L'
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND EXISTS (SELECT 1
FROM pay_payroll_Actions ppa2
WHERE ppa2.effective_date >= ppa.effective_date
AND ppa2.action_type IN ('R','Q')
AND ppa2.action_status = 'C') ;
SELECT scl.segment1
FROM per_assignments_f asg,
hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT formula_id
INTO l_formula_id
FROM ff_formulas_f
WHERE legislation_code = 'IN'
AND formula_name = p_formula_name
AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
SELECT element_type_id
INTO l_element_id
FROM pay_element_types_f
WHERE element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT balance_type_id
INTO l_balance_id
FROM pay_balance_types
WHERE legislation_code = 'IN'
AND balance_name = p_balance_name;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND NAME = p_input_value;
SELECT template_id
INTO l_template_id
FROM pay_element_templates
WHERE template_name = p_template_name
AND legislation_code = 'IN';
SELECT pay_formula_result_rules_s.nextval
INTO v_form_res_rule_id
FROM sys.dual;
INSERT INTO pay_formula_result_rules_f
(formula_result_rule_id,
effective_start_date,
effective_end_date,
business_group_id,
status_processing_rule_id,
result_name,
result_rule_type,
severity_level,
input_value_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
element_type_id)
VALUES
(v_form_res_rule_id,
p_effective_date,
c_end_of_time,
p_business_group_id,
p_status_processing_rule_id,
upper(p_result_name),
p_result_rule_type,
p_severity_level,
l_input_value_id,
trunc(sysdate),
-1,
-1,
-1,
trunc(sysdate),
decode(p_result_rule_type,
'D',p_element_type_id,
'S',p_element_type_id,
'I',l_element_type_id,
'U',p_element_type_id,null));
SELECT rowid
,status_processing_rule_id
,effective_start_date
FROM pay_status_processing_rules_f psr
WHERE psr.element_type_id = p_element_type_id
AND p_effective_date BETWEEN psr.effective_start_date
AND psr.effective_end_date;
SELECT ROWID
,formula_result_rule_id
,effective_start_date
FROM pay_formula_result_rules_f
WHERE status_processing_rule_id = p_status_rule_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
pay_formula_result_rules_pkg.delete_row(k.rowid);
pay_status_rules_pkg.delete_row
( x_rowid => j.rowid
, p_session_date => j.effective_start_date
, p_delete_mode => hr_api.g_zap
, p_status_processing_rule_id => j.status_processing_rule_id
);
PROCEDURE delete_balance_feeds
(p_balance_name IN VARCHAR2
,p_element_name IN VARCHAR2
,p_input_value_name IN VARCHAR2
,p_effective_date IN DATE
)
IS
CURSOR csr_bf IS
SELECT balance_feed_id, object_version_number
FROM pay_balance_feeds_f
WHERE balance_type_id = get_balance_type_id (p_balance_name)
AND input_value_id = get_input_value_id (p_effective_date, p_element_name, p_input_value_name)
AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
l_procedure CONSTANT VARCHAR2(100):= g_package||'delete_balance_feeds';
pay_balance_feeds_api.delete_balance_feed
(
p_effective_date => p_effective_date
,p_datetrack_delete_mode => hr_api.g_delete
,p_balance_feed_id => l_bf_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_start
,p_effective_end_date => l_end
,p_exist_run_result_warning => l_warn
);
END delete_balance_feeds;
SELECT full_name
FROM per_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_organization_id;
SELECT pptl.payment_type_name
FROM pay_payment_types ppt
,pay_payment_types_tl pptl
WHERE ppt.payment_type_id = pptl.payment_type_id
AND ppt.territory_code = 'IN'
AND ppt.category <> 'MT'
AND pptl.language = USERENV('LANG')
AND ppt.payment_type_id = p_payment_type_id;
SELECT hr_general.decode_lookup('IN_BANK',org_information1)
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_CHALLAN_BANK'
AND org_information_id = p_org_information_id;
SELECT DECODE(p_segment_no,'1',add_information13
,'2',add_information14
,'3',hr_general.decode_lookup('IN_STATES',add_information15)
,hr_general.decode_lookup('YES_NO',add_information16)
)
FROM per_addresses
WHERE address_id = p_address_id
AND style = 'IN';
SELECT action_information30
FROM pay_action_information
WHERE action_information3 = p_year || p_quarter
AND action_context_type = 'PA'
AND action_information_category = DECODE(p_return_type,'O','IN_24Q_ORG','IN_24QC_ORG')
AND action_information30 IS NOT NULL
AND action_information1 = p_organization_id
AND action_context_id = p_action_context_id
ORDER BY action_information30 DESC;
SELECT processing_type
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id
AND business_group_id = p_business_group_id
AND p_earned_date BETWEEN effective_start_date AND effective_end_date;
SELECT 1
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
ORDER BY effective_end_date DESC;
SELECT DECODE(pec.classification_name
,'Monetary Perquisite',1
,'Non Monetary Perquisite',2
,-1
)
FROM pay_sub_classification_rules_f pscr
,pay_element_classifications pec
WHERE pscr.classification_id = pec.classification_id
AND pec.parent_classification_id =
(SELECT classification_id FROM pay_element_classifications
WHERE classification_name = 'Perquisites'
AND legislation_code = 'IN'
)
AND element_type_id = p_element_type_id
AND p_date_earned BETWEEN pscr.effective_start_date
AND pscr.effective_end_date;
SELECT pet.configuration_information2
FROM pay_element_types_f pee
,pay_element_templates pet
,pay_shadow_element_types pset
WHERE pee.element_type_id = p_element_type_id
AND pee.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pee.element_name = pset.element_name
AND pset.template_id = pet.template_id
AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date;
SELECT effective_end_date
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
SELECT hr_general.decode_lookup('CONTACT',RELATION.CONTACT_TYPE)
FROM per_contact_relationships relation,
per_all_people_f ppf,
per_all_assignments_f asg
WHERE relation.contact_person_id = p_contact_person_id
AND relation.person_id = ppf.person_id
AND asg.person_id = ppf.person_id
AND asg.assignment_id = p_asg_id
AND SYSDATE >= relation.date_start
AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT MAX(pos.date_start)
FROM per_periods_of_service pos
,per_people_f ppf
,per_assignments_f paf
WHERE pos.person_id = ppf.person_id
AND ppf.person_id = paf.person_id
AND pos.date_start between paf.effective_start_date and paf.effective_end_date
AND paf.assignment_id = p_assignment_id;
SELECT pos.name
FROM per_positions pos,
per_assignments_f asg
WHERE pos.position_id = asg.position_id
AND asg.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date;
select ppf.date_of_birth
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;
SELECT lookup_code
FROM hr_lookups hrl
WHERE HRL.lookup_type = 'IN_LTC_BLOCK'
AND p_effective_date BETWEEN TO_DATE(SUBSTR(HRL.MEANING,1,11),'DD-MM-YYYY')
AND TO_DATE(SUBSTR(HRL.MEANING,15,11),'DD-MM-YYYY');
SELECT lookup_code
FROM hr_lookups hrl
WHERE HRL.lookup_type = 'IN_LTC_BLOCK'
AND l_effective_date BETWEEN TO_DATE(SUBSTR(HRL.MEANING,1,11),'DD-MM-YYYY')
AND TO_DATE(SUBSTR(HRL.MEANING,15,11),'DD-MM-YYYY');