[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 hou.organization_id
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
AND hoi.org_information1 = p_tan_number;
SELECT hoi.org_information1
FROM hr_organization_units hou
,hr_organization_information hoi
,per_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND asg.business_group_id = hou.business_group_id
AND asg.assignment_id = p_assignment_id
AND TO_NUMBER(scl.segment1) = hoi.organization_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN hou.date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT effective_start_date
,assignment_id
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
SELECT NVL(ppa.date_earned,ppa.effective_date)
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pai
WHERE pai.locking_action_id = p_assignment_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_Id
AND ppa.action_type IN ('Q','R','A')
AND ppa.action_status = 'C'
ORDER BY TO_NUMBER(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id) DESC ;
SELECT effective_start_date
,assignment_id
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
SELECT assignment_action_id
FROM pay_payroll_actions pre_ppa
,pay_assignment_actions pre_paa
WHERE pre_ppa.payroll_action_id = pre_paa.payroll_action_id
AND pre_paa.assignment_id = p_assignment_id
AND pre_ppa.action_type IN ('P','U')
AND pre_ppa.action_status = 'C'
AND (pre_ppa.date_earned = p_effective_date
OR
pre_ppa.effective_date = p_effective_date
);
SELECT assignment_action_id
FROM pay_payroll_actions pre_ppa
,pay_assignment_actions pre_paa
WHERE pre_ppa.payroll_action_id = pre_paa.payroll_action_id
AND pre_paa.assignment_id = p_assignment_id
AND pre_ppa.action_type IN ('P','U')
AND pre_ppa.action_status = 'C'
AND ((pre_ppa.date_earned <= p_ee_payment_date AND pre_ppa.date_earned >= p_effective_date)
OR
(pre_ppa.effective_date <= p_ee_payment_date AND pre_ppa.effective_date >= p_effective_date)
)
ORDER BY assignment_action_id desc;
SELECT assignment_action_id
FROM pay_payroll_actions pre_ppa
,pay_assignment_actions pre_paa
WHERE pre_ppa.payroll_action_id = pre_paa.payroll_action_id
AND pre_paa.assignment_id = p_assignment_id
AND pre_ppa.action_type IN ('P','U')
AND pre_ppa.action_status = 'C'
AND (pre_ppa.date_earned >= p_ee_payment_date
OR
pre_ppa.effective_date >= p_ee_payment_date
)
ORDER BY assignment_action_id asc;
SELECT NVL(date_earned,effective_date)
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pai
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.action_type IN ('Q','R','A')
AND ppa.action_status = 'C'
AND paa.source_action_id IS NOT NULL
AND pai.locking_action_id = p_assignment_action_id
AND pai.locked_action_id = paa.assignment_action_id
ORDER BY TO_NUMBER(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id) DESC ;
SELECT ppa.action_type
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND ppa.action_status = 'C';
SELECT pai.locked_action_id assignment_action_id
,paa.assignment_id
FROM pay_action_interlocks pai
,pay_assignment_actions paa
WHERE pai.locking_action_id = p_assignment_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.action_status = 'C'
AND paa.source_action_id IS NULL
ORDER BY pai.locked_action_id DESC;
SELECT assignment_action_id child_actions
FROM pay_assignment_actions
WHERE assignment_id = p_assignment_id
AND action_status = 'C'
AND source_action_id = p_master_asg_act_id;
SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
FROM dual;
,p_last_updated_date IN DATE DEFAULT NULL
,p_element_entry_id IN NUMBER DEFAULT NULL
,p_tan_number IN VARCHAR2 DEFAULT NULL
,p_purge_record IN VARCHAR2 DEFAULT NULL
,p_assignment_id IN NUMBER
)
IS
--If element entry id id not null, then use it to determine the element details
CURSOR c_element_details(p_effective_date DATE,
p_business_group_id NUMBER
)
IS
SELECT element_type_id
,element_link_id
,asg.assignment_id
,entry.object_version_number
FROM pay_element_entries_f entry
,per_assignments_f asg
WHERE asg.business_group_id = p_business_group_id
AND asg.assignment_id = entry.assignment_id
AND entry.element_entry_id = p_element_entry_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date;
SELECT types.element_type_id
,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.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
,p_pre_payment_date
,types.element_type_id
)
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 = 'Income Tax Challan Information'
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 inputs.name name
, inputs.input_value_id id
FROM pay_element_types_f types
, pay_input_values_f inputs
WHERE types.element_type_id = p_element_type_id
AND inputs.element_type_id = types.element_type_id
AND inputs.legislation_code = 'IN'
AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
ORDER BY inputs.display_sequence;
SELECT effective_start_date,object_version_number
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id
ORDER BY object_version_number DESC;
SELECT 1
FROM per_people_f pep
,per_assignments_f asg
,pay_element_entries_f entry
WHERE entry.element_entry_id = p_element_entry_id
AND asg.assignment_id = entry.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND pep.employee_number = p_employee_number
AND asg.business_group_id = p_busines_group_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND p_effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date;
pay_in_utils.trace('p_last_updated_date ',p_last_updated_date );
pay_element_entry_api.delete_element_entry
(p_validate => FALSE
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => l_pre_payment_date
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_warnings
);
pay_element_entry_api.delete_element_entry
(p_validate => FALSE
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => l_pre_payment_date
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_warnings
);
UPDATE pay_element_entry_values_f
SET screen_entry_value = fnd_date.date_to_canonical(sysdate)
WHERE input_value_id = l_input_values_rec(9).input_value_id
AND element_entry_id = l_element_entry_id;
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => hr_api.g_correction
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_values_rec(1).input_value_id
,p_input_value_id2 => l_input_values_rec(2).input_value_id
,p_input_value_id3 => l_input_values_rec(3).input_value_id
,p_input_value_id4 => l_input_values_rec(4).input_value_id
,p_input_value_id5 => l_input_values_rec(5).input_value_id
,p_input_value_id6 => l_input_values_rec(6).input_value_id
,p_input_value_id7 => l_input_values_rec(7).input_value_id
,p_input_value_id8 => l_input_values_rec(8).input_value_id
,p_input_value_id9 => l_input_values_rec(9).input_value_id
,p_entry_value1 => p_voucher_number
,p_entry_value2 => p_pre_payment_date
,p_entry_value3 => p_taxable_income
,p_entry_value4 => p_income_tax_deducted
,p_entry_value5 => p_surcharge_deducted
,p_entry_value6 => p_education_cess_deducted
,p_entry_value7 => p_amount_deposited
,p_entry_value8 => NVL(p_correction_flag,'N')
,p_entry_value9 => sysdate
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warnings
);
UPDATE pay_element_entry_values_f
SET screen_entry_value = fnd_date.date_to_canonical(sysdate)
WHERE input_value_id = l_input_values_rec(9).input_value_id
AND element_entry_id = p_element_entry_id;