[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'N'
into v_validation_check
from sys.dual
where exists (select 1
from pay_element_types_f
where upper(p_element_name) = upper(element_name)
and (p_element_type_id <> element_type_id
or p_element_type_id is null)
and (p_business_group_id = business_group_id + 0
or (business_group_id is null
and (p_legislation_code = legislation_code))));
Checks that the attributes of element type are allowed to be updated.
NOTES
Does not test for attributes which cannot be updated.
These are element_name and classification id.
*/
--
PROCEDURE chk_upd_element_type(p_update_mode in varchar2,
p_val_start_date in date,
p_val_end_date in date,
p_element_type_id in number,
p_business_group_id in number,
p_old_name in varchar2,
p_name in varchar2,
p_old_process_in_run_flag in varchar2,
p_process_in_run_flag in varchar2,
p_old_input_currency in varchar2,
p_input_currency in varchar2,
p_old_output_currency in varchar2,
p_output_currency in varchar2,
p_old_standard_link_flag in varchar2,
p_standard_link_flag in varchar2,
p_old_adjustment_only_flag in varchar2,
p_adjustment_only_flag in varchar2,
p_old_indirect_only_flag in varchar2,
p_indirect_only_flag in varchar2,
p_old_scndry_ent_allwd_flag in varchar2,
p_scndry_ent_allwd_flag in varchar2,
p_old_post_termination_rule in varchar2,
p_post_termination_rule in varchar2,
p_old_processing_priority in number,
p_processing_priority in number) is
--
v_validation_check varchar2(1) := 'Y';
l_no_process_update varchar2(1) := 'N';
select 'N'
into v_validation_check
from sys.dual
where exists (select 1
from pay_element_links_f el
where el.element_type_id = p_element_type_id
and el.effective_start_date <= p_val_end_date
and el.effective_end_date >= p_val_start_date);
hr_utility.set_message(801,'PAY_6147_ELEMENT_LINK_UPDATE');
if (p_update_mode <> 'CORRECTION') then
hr_utility.set_message(801,'PAY_6460_ELEMENT_NO_PROC_CORR');
select 'Y'
into l_no_process_update
from sys.dual
where p_val_start_date =
(select min(effective_start_date)
from pay_element_types_f
where element_type_id = p_element_type_id)
and p_val_end_date =
(select max(effective_end_date)
from pay_element_types_f
where element_type_id = p_element_type_id);
if (l_no_process_update = 'N') then
hr_utility.set_message(801,'PAY_6460_ELEMENT_NO_PROC_CORR');
if (p_update_mode <> 'CORRECTION') then
hr_utility.set_message(801,'PAY_6727_ELEMENT_NO_UPD_NAME');
select 'Y'
into l_no_process_update
from sys.dual
where p_val_start_date =
(select min(effective_start_date)
from pay_element_types_f
where element_type_id = p_element_type_id)
and p_val_end_date =
(select max(effective_end_date)
from pay_element_types_f
where element_type_id = p_element_type_id);
if (l_no_process_update = 'N') then
hr_utility.set_message(801,'PAY_6727_ELEMENT_NO_UPD_NAME');
select 'N'
into v_validation_check
from sys.dual
where exists
(select 1
from pay_formula_result_rules_f frr,
pay_input_values_f iv
where p_element_type_id = iv.element_type_id
and iv.input_value_id = frr.input_value_id
and frr.effective_start_date <= p_val_end_date
and frr.effective_end_date >= p_val_start_date);
select 'N'
into v_validation_check
from sys.dual
where exists
(select 1
from pay_run_results rr,
pay_assignment_actions aa,
pay_payroll_actions pa
where p_element_type_id = rr.element_type_id
and aa.assignment_action_id = rr.assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date between
p_val_start_date and p_val_end_date);
can change. This is on update and on next change delete.
*/
--
FUNCTION element_priority_ok(p_element_type_id number,
p_processing_priority number,
p_val_start_date date,
p_val_end_date date)
return boolean is
--
v_validation_check varchar2(1) := 'Y';
select 'N'
into v_validation_check
from sys.dual
where exists (select 1
from pay_status_processing_rules_f spr,
pay_formula_result_rules_f fr,
pay_input_values_f iv,
pay_element_types_f et
where spr.element_type_id = p_element_type_id
and fr.result_rule_type = 'I'
and spr.status_processing_rule_id =
fr.status_processing_rule_id
and fr.input_value_id = iv.input_value_id
and iv.element_type_id = et.element_type_id
and et.processing_priority <= p_processing_priority
and spr.effective_start_date <= p_val_end_date
and spr.effective_end_date >= p_val_start_date
and fr.effective_start_date <= p_val_end_date
and fr.effective_end_date >= p_val_start_date);
select 'N'
into v_validation_check
from sys.dual
where exists(select 1
from pay_status_processing_rules_f spr,
pay_formula_result_rules_f fr,
pay_input_values_f iv,
pay_element_types_f et
where fr.input_value_id = iv.input_value_id
and fr.result_rule_type = 'I'
and iv.element_type_id = p_element_type_id
and fr.status_processing_rule_id =
spr.status_processing_rule_id
and spr.element_type_id = et.element_type_id
and et.processing_priority >= p_processing_priority
and fr.effective_end_date >= p_val_start_date
and fr.effective_start_date <= p_val_end_date
and spr.effective_start_date <= p_val_end_date
and spr.effective_end_date >= p_val_start_date);
Checks that the element can be deleted. This is either complete delete or
Date effective delete.
NOTES
This procedure disallows delete for any element with element links.
*/
--
PROCEDURE chk_del_element_type(p_mode in varchar2,
p_element_type_id in number,
p_processing_priority in number,
p_session_date in date,
p_val_start_date in date,
p_val_end_date in date) is
--
l_processing_priority number;
select input_value_id
from pay_input_values_f
where p_element_type_id = element_type_id
And effective_end_date >= p_val_start_date
and effective_start_date <= p_val_end_date;
select 'N'
into v_validation_check
from sys.dual
where exists (select 1
from pay_element_links_f el
where el.element_type_id = p_element_type_id
and el.effective_end_date >= p_val_start_date
and el.effective_start_date <= p_val_end_date);
select 'Y'
into v_run_results_exist
from sys.dual
where exists
(select 1
from pay_run_results rr,
pay_assignment_actions aa,
pay_payroll_actions pa
where p_element_type_id = rr.element_type_id
and aa.assignment_action_id = rr.assignment_action_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.effective_date between
p_val_start_date and p_val_end_date);
select 'Y'
into v_element_rules_exist
from sys.dual
where exists
(select 1
from pay_element_type_rules
where element_type_id = p_element_type_id);
if p_mode = 'DELETE_NEXT_CHANGE' and
hr_elements.element_priority_ok(p_element_type_id,
p_processing_priority,
p_val_start_date,
p_val_end_date) = FALSE then
--
hr_utility.set_message(801,'PAY_6914_ELEMENT_PRI_NCD');
inserts a pay value for an element type and a balance feed for the pay value.
This procedure calls balances.ins_balance_feed.
NOTES
*/
--
PROCEDURE ins_input_value(p_element_type_id in number,
p_legislation_code in varchar2,
p_business_group_id in number,
p_classification_id in number,
p_val_start_date in date,
p_val_end_date in date,
p_startup_mode in varchar2) is
v_input_value_id number(15);
select pay_input_values_s.nextval
into v_input_value_id
from sys.dual;
insert into pay_input_values_f
(input_value_id,
effective_start_date,
effective_end_date,
element_type_id,
display_sequence,
generate_db_items_flag,
hot_default_flag,
mandatory_flag,
name,
uom,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
business_group_id,
legislation_code,
legislation_subgroup)
select
v_input_value_id,
p_val_start_date,
p_val_end_date,
et.element_type_id,
1,
'Y',
'N',
'N',
'Pay Value',
'M',
et.last_update_date,
et.last_updated_by,
et.last_update_login,
et.created_by,
et.creation_date,
et.business_group_id,
et.legislation_code,
et.legislation_subgroup
from pay_element_types_f et
where et.element_type_id = p_element_type_id
and et.effective_start_date = p_val_start_date;
insert into PAY_INPUT_VALUES_F_TL (
INPUT_VALUE_ID,
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
v_input_value_id,
l_pay_value_name,
sysdate,
c_user_id,
c_user_id,
c_login_id,
sysdate,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PAY_INPUT_VALUES_F_TL T
where T.INPUT_VALUE_ID = v_input_value_id
and T.LANGUAGE = L.LANGUAGE_CODE);
select classification_id
from pay_element_classifications
where parent_classification_id = p_classification_id
and nvl(business_group_id, nvl(p_business_group_id, 0)) = nvl(p_business_group_id, 0)
and nvl(legislation_code, nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ')
and create_by_default_flag = 'Y'
for update;
select 'X'
from pay_sub_classification_rules_f
where effective_start_date = p_start_date
and effective_end_date = p_end_date
and element_type_id = p_element_type_id
and classification_id = p_classification
and nvl(business_group_id, nvl(p_business_group_id, 0)) = nvl(p_business_group_id, 0)
and nvl(legislation_code, nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ');
select pay_sub_classification_rules_s.nextval
into l_sub_classification_rule_id
from dual;
insert into pay_sub_classification_rules_f
(SUB_CLASSIFICATION_RULE_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,ELEMENT_TYPE_ID
,CLASSIFICATION_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE)
values
(l_sub_classification_rule_id
,p_val_start_date
,p_val_end_date
,p_element_type_id
,subcr_rec.classification_id
,p_business_group_id
,l_legislation_code);
Based on the process in run flag this will call the insert input value
and the insert status processing rules procedures.
*/
--
PROCEDURE ins_3p_element_type(p_element_type_id in number,
p_process_in_run_flag in varchar2,
p_legislation_code in varchar2,
p_business_group_id in number,
p_classification_id in number,
p_non_payments_flag in varchar,
p_val_start_date in date,
p_val_end_date in date,
p_startup_mode in varchar2) is
--
begin
g_debug := hr_utility.debug_enabled;
This procedure deletes any formula result rules in existence for the element.
It is only called from del_status_processing_rules.
*/
--
PROCEDURE del_formula_result_rules(
p_status_processing_rule_id in number,
p_delete_mode in varchar2,
p_val_session_date in date,
p_val_start_date in date,
p_val_end_date in date,
p_startup_mode in varchar2) is
--
begin
g_debug := hr_utility.debug_enabled;
if p_delete_mode = 'ZAP' then
--
if p_startup_mode <> 'USER' then
--
delete from hr_application_ownerships ao
where key_name = 'FORMULA_RESULT_RULE_ID'
and exists
(select 1
from pay_formula_result_rules_f frr
where frr.status_processing_rule_id =
p_status_processing_rule_id
and ao.key_value = to_char(frr.formula_result_rule_id));
delete from pay_formula_result_rules_f
where status_processing_rule_id = p_status_processing_rule_id;
elsif p_delete_mode = 'DELETE' then
--
if g_debug then
hr_utility.set_location('hr_elements.del_formula_result_rules', 2);
delete from pay_formula_result_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and effective_start_date > p_val_session_date;
update pay_formula_result_rules_f
set effective_end_date = p_val_session_date
where status_processing_rule_id = p_status_processing_rule_id
and p_val_session_date between
effective_start_date and effective_end_date;
This procedure deletes any status processing rules for this element and
calls a function to delete any formula result rules.
NOTES
Element types cannot be subject to a future change delete. They can be subject
to a next change delete but, in the case of status processing rules, this
does not cause the records to 'open up' if we are on the final record. A
warning will appear in the form telling the users that this is the case.
*/
PROCEDURE del_status_processing_rules(
p_element_type_id in number,
p_delete_mode in varchar2,
p_val_session_date in date,
p_val_start_date in date,
p_val_end_date in date,
p_startup_mode in varchar2) is
--
-- Cursor select all valid sprs for the element and locks these rows
--
CURSOR get_sprs (p_element_type_id number,
p_val_start_date date,
p_val_end_date date) is
select status_processing_rule_id,
effective_start_date,
effective_end_date
from pay_status_processing_rules_f
where p_element_type_id = element_type_id
and effective_start_date <= p_val_end_date
and effective_end_date >= p_val_start_date
for update;
p_delete_mode,
p_val_session_date,
spr_rec.effective_start_date,
spr_rec.effective_end_date,
p_startup_mode);
if p_delete_mode = 'ZAP' then
--
if p_startup_mode <> 'USER' then
--
delete from hr_application_ownerships ao
where ao.key_name = 'STATUS_PROCESSING_RULE_ID'
and exists
(select 1
from pay_status_processing_rules_f spr
where spr.element_type_id = p_element_type_id
and ao.key_value =
to_char(spr.status_processing_rule_id));
delete from pay_status_processing_rules_f
where element_type_id = p_element_type_id;
elsif p_delete_mode = 'DELETE' then
--
if g_debug then
hr_utility.set_location('hr_elements.del_status_processing_rules', 2);
delete from pay_status_processing_rules_f
where element_type_id = p_element_type_id
and effective_start_date > p_val_session_date;
update pay_status_processing_rules_f
set effective_end_date = p_val_session_date
where element_type_id = p_element_type_id
and p_val_session_date between
effective_start_date and effective_end_date;
This procedure deletes any existing sub_classification_rules and any
related balance feeds.
NOTES
Element types cannot be subject to a future change delete. They can, however,
be subject to a next change delete and this is handled in the code. This
procedure relies on the hr_input_values.del_3p_input_values being called
in the same commit unit as this will tidy up the balance feeds that may have
been created by the sub_classification rules.
*/
--
PROCEDURE del_sub_classification_rules(
p_element_type_id in number,
p_delete_mode in varchar2,
p_val_session_date in date,
p_val_start_date in date,
p_val_end_date in date,
p_startup_mode in varchar2) is
--
v_end_of_time date;
if p_delete_mode = 'ZAP' then
--
if p_startup_mode <> 'USER' then
--
delete from hr_application_ownerships ao
where ao.key_name = 'SUB_CLASSIFICATION_RULE_ID'
and exists
(select 1
from pay_sub_classification_rules_f scr
where scr.element_type_id = p_element_type_id
and ao.key_value =
to_char(scr.sub_classification_rule_id));
delete from pay_sub_classification_rules_f
where element_type_id = p_element_type_id;
elsif p_delete_mode = 'DELETE' then
--
--
if g_debug then
hr_utility.set_location('hr_elements.del_sub_classification_rules', 2);
delete from pay_sub_classification_rules_f
where element_type_id = p_element_type_id
and effective_start_date > p_val_session_date;
update pay_sub_classification_rules_f
set effective_end_date = p_val_session_date
where element_type_id = p_element_type_id
and p_val_session_date between
effective_start_date and effective_end_date;
This procedure does third party processing necessary on update. Currenctly
this only consists of deleting and recreating the database items
*/
PROCEDURE upd_3p_element_type(p_element_type_id in number,
p_val_start_date in date,
p_old_name in varchar2,
p_name in varchar2) is
--
begin
--
if p_old_name <> p_name then
--
hrdyndbi.delete_element_type_dict(p_element_type_id);
This procedure does the necessary cascade deletes when an element type is
deleted. This affects the following tables: Input values, status processing
rules and formula result rules.
NOTES
Element types cannot be subject to a future change delete. They can, however,
be subject to a next change delete and this is handled in the code.
*/
PROCEDURE del_3p_element_type(p_element_type_id in number,
p_delete_mode in varchar2,
p_val_session_date in date,
p_val_start_date in date,
p_val_end_date in date,
p_startup_mode in varchar2) is
--
v_end_of_time date;
select iv.input_value_id input_value_id,
iv.generate_db_items_flag db_items_flag
from pay_input_values_f iv
where p_element_type_id = iv.element_type_id
and iv.effective_start_date <= p_val_end_date
and iv.effective_end_date >= p_val_start_date
for update;
hr_input_values.del_3p_input_values(p_delete_mode,
iv_rec.input_value_id,
iv_rec.db_items_flag,
p_val_end_date,
p_val_session_date,
p_startup_mode);
if p_delete_mode = 'ZAP' then
--
if p_startup_mode <> 'USER' then
--
delete from hr_application_ownerships ao
where ao.key_name = 'INPUT_VALUE_ID'
and exists
(select 1
from pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and ao.key_value = to_char(iv.input_value_id));
delete from pay_input_values_f
where element_type_id = p_element_type_id;
elsif p_delete_mode = 'DELETE' then
--
--
if g_debug then
hr_utility.set_location('hr_elements.del_3p_element_type', 3);
delete from pay_input_values_f
where element_type_id = p_element_type_id
and effective_start_date > p_val_session_date;
update pay_input_values_f
set effective_end_date = p_val_session_date
where element_type_id = p_element_type_id
and p_val_session_date between
effective_start_date and effective_end_date;
elsif p_delete_mode = 'DELETE_NEXT_CHANGE' then
--
--
if g_debug then
hr_utility.set_location('hr_elements.del_3p_element_type', 4);
select 'Y'
into l_on_final_record
from pay_element_types_f et1
where p_element_type_id = et1.element_type_id
and p_val_session_date between
et1.effective_start_date and et1.effective_end_date
and et1.effective_end_date =
(select max(et2.effective_end_date)
from pay_element_types_f et2
where p_element_type_id = et2.element_type_id);
update pay_input_values_f iv1
set iv1.effective_end_date = v_end_of_time
where (iv1.input_value_id, iv1.effective_end_date) =
(select iv2.input_value_id, max(iv2.effective_end_date)
from pay_input_values_f iv2
where iv2.element_type_id = p_element_type_id
group by iv2.input_value_id);
p_delete_mode,
p_val_session_date,
p_val_start_date,
p_val_end_date,
p_startup_mode);
p_delete_mode,
p_val_session_date,
p_val_start_date,
p_val_end_date,
p_startup_mode);
if p_delete_mode = 'ZAP' then
--
-- We need to clear down the database items
--
hrdyndbi.delete_element_type_dict(p_element_type_id);
This procedure will insert product ownerships for any startup or generic
record
*/
PROCEDURE ins_ownerships(p_key_name varchar2,
p_key_value number,
p_element_type_id number) is
--
l_session_id number;
insert into hr_application_ownerships
(key_name,
key_value,
product_name)
select p_key_name,
p_key_value,
ao.product_name
from hr_application_ownerships ao
where ao.key_name = 'ELEMENT_TYPE_ID'
and ao.key_value = p_element_type_id;
SELECT COUNT(0)
INTO v_freq_rule_exists
FROM pay_ele_payroll_freq_rules EPF
WHERE element_type_id = p_ele_type_id
AND payroll_id = p_payroll_id
AND business_group_id + 0 = p_bg_id;
SELECT NVL(rule_date_code,'E')
INTO v_rule_date_code
FROM pay_ele_payroll_freq_rules
WHERE element_type_id = p_ele_type_id
AND payroll_id = p_payroll_id;
SELECT end_date, start_date
INTO v_period_end_date,
v_period_start_date
FROM per_time_periods
WHERE p_passed_date BETWEEN start_date AND end_date
AND payroll_id = p_payroll_id;
SELECT TPT.number_per_fiscal_year
INTO v_number_per_fy
FROM per_time_period_types TPT,
pay_payrolls_f PRL
WHERE TPT.period_type = PRL.period_type
AND PRL.business_group_id + 0 = p_bg_id
AND p_passed_date BETWEEN prl.effective_start_date and prl.effective_end_date
AND PRL.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_end_date,'YEAR')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_start_date,'YEAR')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_end_date,'YEAR')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(p_passed_date,'YEAR')
AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_end_date,'YEAR')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_start_date,'YEAR')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(p_passed_date,'YEAR')
AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_end_date, 'MONTH')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_start_date, 'MONTH')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_end_date, 'MONTH')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(p_passed_date, 'MONTH')
AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_end_date, 'MONTH')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(v_period_start_date, 'MONTH')
AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
BETWEEN TRUNC(p_passed_date, 'MONTH')
AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
AND PTP.payroll_id = p_payroll_id;
SELECT 'N'
INTO p_skip_element
FROM pay_ele_payroll_freq_rules EPF,
pay_freq_rule_periods FRP
WHERE FRP.period_no_in_reset_period = v_run_number
AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
AND EPF.business_group_id + 0 = p_bg_id
AND EPF.payroll_id = p_payroll_id
AND EPF.element_type_id = p_ele_type_id;
select pap.parameter_value
from pay_action_parameters pap
where pap.parameter_name = 'FREQ_RULE_WHOLE_PERIOD';
select pte.regular_payment_date
from per_time_periods pte
where pte.payroll_id = p_payroll_id
and p_date_earned between
pte.start_date and pte.end_date;