[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ss.effective_date
into v_session_date
from fnd_sessions ss
where ss.session_id = userenv('sessionid');
select hl.lookup_code
into v_post_term_rule
from hr_lookups hl
where hl.lookup_type = 'TERMINATION_RULE'
and (upper(hl.meaning) = upper(p_post_termination_rule)
or
hl.lookup_code =
decode(p_post_termination_rule, 'Actual Termination','A',
'Final Close', 'F',
'Last Standard Process','L',
p_post_termination_rule));
select business_group_id,
currency_code,
legislation_code
into v_business_group_id,
v_currency_code,
v_legislation_code
from per_business_groups
where name = p_business_group_name;
select ec.classification_id,
nvl(p_processing_priority, ec.default_priority),
ec.non_payments_flag
into v_classification_id,
v_processing_priority,
v_non_payments_flag
from pay_element_classifications ec
where upper(ec.classification_name) = upper(p_classification_name)
and ec.parent_classification_id is NULL
and ((ec.legislation_code = v_legislation_code)
or (ec.legislation_code is null
and not exists (select ''
from pay_element_classifications ec2
where upper(ec2.classification_name) = upper(p_classification_name)
and ec2.parent_classification_id is NULL
and ec2.legislation_code = v_legislation_code)
));
pay_element_types_pkg.insert_row(
p_rowid =>v_rowid,
P_ELEMENT_TYPE_ID =>v_element_type_id,
P_EFFECTIVE_START_DATE =>v_effective_start_date,
P_EFFECTIVE_END_DATE =>v_effective_end_date,
P_BUSINESS_GROUP_ID =>v_business_group_id,
P_LEGISLATION_CODE =>p_legislation_code,
P_FORMULA_ID =>p_formula_id,
P_INPUT_CURRENCY_CODE =>v_input_currency_code,
P_OUTPUT_CURRENCY_CODE =>v_output_currency_code,
P_CLASSIFICATION_ID =>v_classification_id,
P_BENEFIT_CLASSIFICATION_ID =>NULL,
P_ADDITIONAL_ENTRY_ALLOWED =>p_add_entry_allowed_flag,
P_ADJUSTMENT_ONLY_FLAG =>p_adjustment_only_flag,
P_CLOSED_FOR_ENTRY_FLAG =>p_closed_for_entry_flag,
P_ELEMENT_NAME =>p_element_name,
P_BASE_ELEMENT_NAME =>p_element_name,
P_INDIRECT_ONLY_FLAG =>p_indirect_only_flag,
P_MULTIPLE_ENTRIES_ALLOWED =>p_mult_entries_allowed,
P_MULTIPLY_VALUE_FLAG =>p_multiply_value_flag,
P_POST_TERMINATION_RULE =>v_post_term_rule,
P_PROCESS_IN_RUN_FLAG =>p_process_in_run_flag,
P_PROCESSING_PRIORITY =>v_processing_priority,
P_PROCESSING_TYPE =>p_processing_type,
P_STANDARD_LINK_FLAG =>p_standard_link_flag,
P_COMMENT_ID =>NULL,
P_DESCRIPTION =>p_description,
P_LEGISLATION_SUBGROUP =>p_legislation_subgroup,
P_QUALIFYING_AGE =>p_qual_age,
P_QUALIFYING_LENGTH_OF_SERVICE =>p_qual_length_of_service,
P_QUALIFYING_UNITS =>p_qual_units,
P_REPORTING_NAME =>p_reporting_name,
P_ATTRIBUTE_CATEGORY =>NULL,
P_ATTRIBUTE1 =>NULL,
P_ATTRIBUTE2 =>NULL,
P_ATTRIBUTE3 =>NULL,
P_ATTRIBUTE4 =>NULL,
P_ATTRIBUTE5 =>NULL,
P_ATTRIBUTE6 =>NULL,
P_ATTRIBUTE7 =>NULL,
P_ATTRIBUTE8 =>NULL,
P_ATTRIBUTE9 =>NULL,
P_ATTRIBUTE10 =>NULL,
P_ATTRIBUTE11 =>NULL,
P_ATTRIBUTE12 =>NULL,
P_ATTRIBUTE13 =>NULL,
P_ATTRIBUTE14 =>NULL,
P_ATTRIBUTE15 =>NULL,
P_ATTRIBUTE16 =>NULL,
P_ATTRIBUTE17 =>NULL,
P_ATTRIBUTE18 =>NULL,
P_ATTRIBUTE19 =>NULL,
P_ATTRIBUTE20 =>NULL,
P_ELEMENT_INFORMATION_CATEGORY =>NULL,
P_ELEMENT_INFORMATION1 =>NULL,
P_ELEMENT_INFORMATION2 =>NULL,
P_ELEMENT_INFORMATION3 =>NULL,
P_ELEMENT_INFORMATION4 =>NULL,
P_ELEMENT_INFORMATION5 =>NULL,
P_ELEMENT_INFORMATION6 =>NULL,
P_ELEMENT_INFORMATION7 =>NULL,
P_ELEMENT_INFORMATION8 =>NULL,
P_ELEMENT_INFORMATION9 =>NULL,
P_ELEMENT_INFORMATION10 =>NULL,
P_ELEMENT_INFORMATION11 =>NULL,
P_ELEMENT_INFORMATION12 =>NULL,
P_ELEMENT_INFORMATION13 =>NULL,
P_ELEMENT_INFORMATION14 =>NULL,
P_ELEMENT_INFORMATION15 =>NULL,
P_ELEMENT_INFORMATION16 =>NULL,
P_ELEMENT_INFORMATION17 =>NULL,
P_ELEMENT_INFORMATION18 =>NULL,
P_ELEMENT_INFORMATION19 =>NULL,
P_ELEMENT_INFORMATION20 =>NULL,
P_NON_PAYMENTS_FLAG =>NULL,
P_DEFAULT_BENEFIT_UOM =>NULL,
P_CONTRIBUTIONS_USED =>NULL,
P_THIRD_PARTY_PAY_ONLY_FLAG =>p_third_party_pay_only,
P_RETRO_SUMM_ELE_ID =>p_retro_summ_ele_id,
P_ITERATIVE_FLAG =>p_iterative_flag,
P_ITERATIVE_FORMULA_ID =>p_iterative_formula_id,
P_ITERATIVE_PRIORITY =>p_iterative_priority,
P_PROCESS_MODE =>p_process_mode,
P_GROSSUP_FLAG =>p_grossup_flag,
P_ADVANCE_INDICATOR =>p_advance_indicator,
P_ADVANCE_PAYABLE =>p_advance_payable,
P_ADVANCE_DEDUCTION =>p_advance_deduction,
P_PROCESS_ADVANCE_ENTRY =>p_process_advance_entry,
P_PRORATION_GROUP_ID =>p_proration_group_id,
P_PRORATION_FORMULA_ID =>p_proration_formula_id,
P_RECALC_EVENT_GROUP_ID =>p_recalc_event_group_id
);
insert into hr_application_ownerships
(KEY_NAME,
PRODUCT_NAME,
KEY_VALUE)
values
('ELEMENT_TYPE_ID',
'PER',
v_element_type_id);
select l.language_code
from fnd_languages l
where l.installed_flag in ('I', 'B');
select pay_input_values_s.nextval
into v_input_value_id
from dual;
select l.language_code
into v_base_language
from fnd_languages l
where l.installed_flag = 'B';
select bg.business_group_id
into v_business_group_id
from per_business_groups bg
where bg.name = p_business_group_name;
select lookup_code
into v_warning_or_error
from hr_lookups
where lookup_type = 'WARNING_ERROR'
and upper(meaning) = upper(p_warning_or_error);
select lookup_code
into v_warning_or_error
from hr_lookups
where lookup_type = 'WARNING_ERROR'
and lookup_code = p_warn_or_error_code;
select lookup_code
into v_uom
from hr_lookups
where lookup_type = 'UNITS'
and upper(meaning) = upper(p_uom);
select lookup_code
into v_uom
from hr_lookups
where lookup_code = p_uom_code
and lookup_type = 'UNITS';
select ss.effective_date
into v_session_date
from fnd_sessions ss
where ss.session_id = userenv('sessionid');
select element_name
into v_ele_name
from pay_element_types_f
where element_name = p_element_name
and (business_group_id = v_business_group_id OR
(business_group_id is NULL and legislation_code = p_legislation_code))
and v_session_date between effective_start_date and
effective_end_date; /* new bug 1576000 */
select element_name
into v_ele_name
from pay_element_types_f
where element_name = p_element_name
and legislation_code = p_legislation_code
and v_session_date between effective_start_date and
effective_end_date; /* new bug 1576000 */
select min(et.effective_start_date),
max(et.effective_end_date),
et.legislation_subgroup,
et.element_type_id
into v_element_start_date,
v_element_end_date,
v_legislation_subgroup,
v_element_type_id
from pay_element_types_f et
where upper(et.element_name) = upper(p_element_name)
and (et.business_group_id + 0 = v_business_group_id
or (et.business_group_id is null
and et.legislation_code = v_legislation_code)
or (et.business_group_id is null and et.legislation_code is null))
group by et.legislation_subgroup, et.element_type_id;
insert into pay_input_values_f
(INPUT_VALUE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ELEMENT_TYPE_ID,
LOOKUP_TYPE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_ID,
DISPLAY_SEQUENCE,
GENERATE_DB_ITEMS_FLAG,
HOT_DEFAULT_FLAG,
MANDATORY_FLAG,
NAME,
UOM,
DEFAULT_VALUE,
LEGISLATION_SUBGROUP,
MAX_VALUE,
MIN_VALUE,
WARNING_OR_ERROR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
(v_input_value_id,
v_effective_start_date,
v_effective_end_date,
v_element_type_id,
p_lookup_type,
v_business_group_id,
v_legislation_code,
p_formula_id,
p_display_sequence,
p_generate_db_item_flag,
p_hot_default_flag,
p_mandatory_flag,
p_name,
v_uom,
p_default_value,
v_legislation_subgroup,
p_max_value,
p_min_value,
v_warning_or_error,
v_todays_date,
-1,
-1,
-1,
v_todays_date);
insert into pay_input_values_f_tl
(INPUT_VALUE_ID,
NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
(v_input_value_id,
p_name,
lang_rec.language_code,
v_base_language,
v_todays_date,
-1,
-1,
-1,
v_todays_date);
select pay_payrolls_s.nextval
into v_payroll_id
from sys.dual;
select ss.effective_date
into v_session_date
from fnd_sessions ss
where ss.session_id = userenv('sessionid');
select bg.business_group_id,
bg.legislation_code,
bg.currency_code
into v_business_group_id,
v_legislation_code,
v_currency_code
from per_business_groups bg
where name = p_business_group_name;
select org.organization_id
into v_organization_id
from per_organization_units org
where upper(org.name) = upper(p_organization_name)
and org.business_group_id + 0 = v_business_group_id;
select cs.consolidation_set_id
into v_consolidation_set_id
from pay_consolidation_sets cs
where upper(cs.consolidation_set_name) = upper(p_consolidation_set_name)
and cs.business_group_id + 0 = v_business_group_id;
select opm.org_payment_method_id
into v_dflt_payment_method_id
from pay_payment_types ppt,
pay_org_payment_methods_f opm
where upper(opm.org_payment_method_name) = upper(p_dflt_payment_method)
and opm.business_group_id + 0 = v_business_group_id
and opm.payment_type_id = ppt.payment_type_id
and ppt.allow_as_default = 'Y'
and v_session_date between opm.effective_start_date
and opm.effective_end_date;
insert into pay_payrolls_f
(PAYROLL_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
DEFAULT_PAYMENT_METHOD_ID,
BUSINESS_GROUP_ID,
CONSOLIDATION_SET_ID,
ORGANIZATION_ID,
COST_ALLOCATION_KEYFLEX_ID,
GL_SET_OF_BOOKS_ID,
SOFT_CODING_KEYFLEX_ID,
PERIOD_TYPE,
CUT_OFF_DATE_OFFSET,
DIRECT_DEPOSIT_DATE_OFFSET,
FIRST_PERIOD_END_DATE,
MIDPOINT_OFFSET,
NEGATIVE_PAY_ALLOWED_FLAG,
NUMBER_OF_YEARS,
PAY_ADVICE_DATE_OFFSET,
PAY_DATE_OFFSET,
PAYROLL_NAME,
WORKLOAD_SHIFTING_LEVEL,
COMMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
(v_payroll_id,
v_effective_start_date,
v_effective_end_date,
v_dflt_payment_method_id,
v_business_group_id,
v_consolidation_set_id,
v_organization_id,
p_cost_all_keyflex_id,
p_gl_set_of_books_id,
p_soft_coding_keyflex_id,
p_period_type,
p_cut_off_date_offset,
p_direct_deposit_date_offset,
p_first_period_end_date,
p_midpoint_offset,
p_negative_pay_allowed_flag,
p_number_of_years,
p_pay_advice_date_offset,
p_pay_date_offset,
p_payroll_name,
p_workload_shifting_level,
NULL, -- Ignore Comments.
v_todays_date,
-1,
-1,
-1,
v_todays_date);
select pay_consolidation_sets_s.nextval,
business_group_id
into v_consolidation_set_id,
v_business_group_id
from per_business_groups
where name = p_business_group_name;
insert into pay_consolidation_sets
(CONSOLIDATION_SET_ID,
BUSINESS_GROUP_ID,
CONSOLIDATION_SET_NAME,
COMMENTS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
(v_consolidation_set_id,
v_business_group_id,
p_consolidation_set_name,
NULL, -- Ignore Comments
v_todays_date,
-1,
-1,
-1,
v_todays_date);
insert into hr_owner_definitions
(PRODUCT_SHORT_NAME,
SESSION_ID)
select
p_app_short_name,
userenv('sessionid')
from sys.dual
where not exists (select 1
from hr_owner_definitions od
where od.product_short_name = p_app_short_name
and session_id = userenv('sessionid'));
delete from fnd_sessions where session_id = userenv('sessionid');
insert into fnd_sessions
(SESSION_ID,
EFFECTIVE_DATE)
values
(userenv('sessionid'),
p_session_date);
select pay_element_links_s.nextval,
bg.business_group_id,
bg.legislation_code
into v_element_link_id,
v_business_group_id,
v_legislation_code
from per_business_groups bg
where name = p_business_group_name;
select ss.effective_date
into v_session_date
from fnd_sessions ss
where ss.session_id = userenv('sessionid');
select max(et.effective_end_date),
et.element_type_id,
et.standard_link_flag,
et.multiply_value_flag,
et.qualifying_age,
et.qualifying_length_of_service,
et.qualifying_units
into v_element_end_date,
v_element_type_id,
v_el_standard_link_flag,
v_el_multiply_value_flag,
v_el_qual_age,
v_el_qual_lngth_of_service,
v_el_qual_units
from pay_element_types_f et
where upper(et.element_name) = upper(p_element_name)
and (et.business_group_id + 0 = v_business_group_id
or (et.business_group_id is null
and et.legislation_code = v_legislation_code)
or (et.business_group_id is null and et.legislation_code is null))
group by et.element_type_id, et.standard_link_flag, et.multiply_value_flag,
et.qualifying_age, et.qualifying_length_of_service,
et.qualifying_units;
select jb.job_id
into v_job_id
from per_jobs_vl jb
where upper(jb.name) = upper(p_job_name)
and jb.business_group_id + 0 = v_business_group_id
and v_effective_start_date between jb.date_from
and nvl(jb.date_to,v_end_of_time);
select po.position_id
into v_position_id
from per_positions po
where upper(po.name) = upper(p_position_name)
and po.business_group_id + 0 = v_business_group_id
and v_effective_start_date between po.date_effective
and nvl(po.date_end,v_end_of_time);
select gr.grade_id
into v_grade_id
from per_grades_vl gr
where upper(gr.name) = upper(p_grade_name)
and gr.business_group_id + 0 = v_business_group_id
and v_effective_start_date between gr.date_from
and nvl(gr.date_to,v_end_of_time);
select pg.people_group_id
into v_people_group_id
from pay_people_groups pg
where upper(pg.group_name) = upper(p_people_group_name)
and v_effective_start_date
between nvl(pg.start_date_active, v_start_of_time)
and nvl(pg.end_date_active,v_end_of_time);
select org.organization_id
into v_organization_id
from per_organization_units org
where upper(org.name) = upper(p_organization_name)
and org.business_group_id + 0 = v_business_group_id
and v_effective_start_date between org.date_from
and nvl(org.date_to,v_end_of_time);
select pa.payroll_id,
max(pa.effective_end_date)
into v_payroll_id,
v_payroll_end_date
from pay_all_payrolls_f pa
where upper(pa.payroll_name) = upper(p_payroll_name)
and pa.business_group_id + 0 = v_business_group_id
and pa.effective_start_date <= v_effective_end_date
and pa.effective_end_date >= v_effective_start_date
group by payroll_id;
insert into pay_element_links_f
(ELEMENT_LINK_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PAYROLL_ID,
JOB_ID,
POSITION_ID,
PEOPLE_GROUP_ID,
COST_ALLOCATION_KEYFLEX_ID,
ORGANIZATION_ID,
ELEMENT_TYPE_ID,
LOCATION_ID,
GRADE_ID,
BALANCING_KEYFLEX_ID,
BUSINESS_GROUP_ID,
ELEMENT_SET_ID,
COSTABLE_TYPE,
LINK_TO_ALL_PAYROLLS_FLAG,
MULTIPLY_VALUE_FLAG,
STANDARD_LINK_FLAG,
TRANSFER_TO_GL_FLAG,
COMMENT_ID,
QUALIFYING_AGE,
QUALIFYING_LENGTH_OF_SERVICE,
QUALIFYING_UNITS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
(v_element_link_id,
v_effective_start_date,
least(v_payroll_end_date, v_element_end_date, v_effective_end_date),
v_payroll_id,
v_job_id,
v_position_id,
v_people_group_id,
p_cost_all_keyflex_id,
v_organization_id,
v_element_type_id,
p_location_id,
v_grade_id,
p_balancing_keyflex_id,
v_business_group_id,
NULL, -- Do not worry about distribution set
p_costable_type,
p_link_to_all_pyrlls_fl,
nvl(p_multiply_value_flag,v_el_multiply_value_flag),
nvl(p_standard_link_flag,v_el_standard_link_flag),
p_transfer_to_gl_flag,
NULL, -- Do not worry about comments
nvl(p_qual_age,v_el_qual_age),
nvl(p_qual_lngth_of_service,v_el_qual_lngth_of_service),
nvl(p_qual_units,v_el_qual_units),
v_todays_date,
-1,
-1,
-1,
v_todays_date);
hr_input_values.create_link_input_value('INSERT_LINK',
v_element_link_id,
NULL,
NULL,
p_costable_type,
v_effective_start_date,
least(v_payroll_end_date,
v_element_end_date,
v_effective_end_date),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
v_pay_value_name,
v_element_type_id);
PROCEDURE insert_primary_balance_feed(p_balance_type_id number
,p_primary_bal_iv_id number
,p_primary_bal_ele_id number
,p_business_group_id number
,p_legislation_code varchar2
,p_effective_date date
,p_mode varchar2)
IS
--
cursor feed_exists(p_bal_id number
,p_iv_id number
,p_bg_id number
,p_leg varchar2
,p_eff_date date)
is
select null
from pay_balance_feeds_f pbf
where pbf.balance_type_id = p_bal_id
and pbf.input_value_id = p_iv_id
and nvl(pbf.business_group_id, -1) = nvl(p_bg_id, -1)
and nvl(pbf.legislation_code, 'NULL') = nvl(p_leg, 'NULL')
and p_eff_date between pbf.effective_start_date
and pbf.effective_end_date;
END Insert_primary_balance_feed;
select balance_category_id
, business_group_id
from pay_balance_categories_f cat
where category_name = p_cat_name
and nvl(legislation_code,'NULL') = nvl(p_cat_leg, 'NULL')
and p_eff_date between cat.effective_start_date
and cat.effective_end_date;
select legislation_code
from per_business_groups
where name = p_bg_name;
select balance_type_id
, base_balance_type_id
from pay_balance_types
where balance_name = p_base_bal_name
and nvl(business_group_id, nvl(p_ctl_bg, -1)) = nvl(p_ctl_bg, -1)
and nvl(legislation_code, nvl(p_ctl_leg, ' ')) = nvl(p_ctl_leg, ' ');
select piv.input_value_id
, pet.element_type_id
from pay_input_values_f piv
, pay_element_types_f pet
, pay_input_values_f_tl pivtl
, pay_element_types_f_tl pettl
where piv.input_value_id = pivtl.input_value_id
and pivtl.language = userenv('LANG')
and pivtl.name = p_prim_iv
and pet.element_type_id = pettl.element_type_id
and pettl.language = userenv('LANG')
and pettl.element_name = p_prim_ele
and piv.element_type_id = pet.element_type_id
and p_eff_date between piv.effective_start_date
and piv.effective_end_date
and p_eff_date between pet.effective_start_date
and pet.effective_end_date
and piv.uom = p_bal_uom
and nvl(pet.business_group_id, nvl(p_ctl_bg, -1)) = nvl(p_ctl_bg, -1)
and nvl(pet.legislation_code, nvl(p_ctl_leg, ' ')) = nvl(p_ctl_leg, ' ');
select business_group_id,
currency_code,
legislation_code
into v_business_group_id,
v_currency_code,
v_legislation_code
from per_business_groups
where name = p_business_group_name;
select lookup_code,decode(lookup_code,'M',v_currency_code,NULL)
into v_uom,v_money
from hr_lookups
where lookup_type = 'UNITS'
and upper(meaning) = upper(p_uom);
pay_balance_types_pkg.insert_row(
X_ROWID =>v_rowid,
X_BALANCE_TYPE_ID =>v_balance_type_id,
X_BUSINESS_GROUP_ID =>v_business_group_id,
X_LEGISLATION_CODE =>p_legislation_code,
X_CURRENCY_CODE =>v_money,
X_ASSIGNMENT_REMUNERATION_FLAG =>p_ass_remuneration_flag,
X_BALANCE_NAME =>p_balance_name,
X_BASE_BALANCE_NAME =>p_balance_name,
X_BALANCE_UOM =>v_uom,
X_COMMENTS =>NULL,
X_LEGISLATION_SUBGROUP =>p_legislation_subgroup,
X_REPORTING_NAME =>p_reporting_name,
X_ATTRIBUTE_CATEGORY =>NULL,
X_ATTRIBUTE1 =>NULL,
X_ATTRIBUTE2 =>NULL,
X_ATTRIBUTE3 =>NULL,
X_ATTRIBUTE4 =>NULL,
X_ATTRIBUTE5 =>NULL,
X_ATTRIBUTE6 =>NULL,
X_ATTRIBUTE7 =>NULL,
X_ATTRIBUTE8 =>NULL,
X_ATTRIBUTE9 =>NULL,
X_ATTRIBUTE10 =>NULL,
X_ATTRIBUTE11 =>NULL,
X_ATTRIBUTE12 =>NULL,
X_ATTRIBUTE13 =>NULL,
X_ATTRIBUTE14 =>NULL,
X_ATTRIBUTE15 =>NULL,
X_ATTRIBUTE16 =>NULL,
X_ATTRIBUTE17 =>NULL,
X_ATTRIBUTE18 =>NULL,
X_ATTRIBUTE19 =>NULL,
X_ATTRIBUTE20 =>NULL,
x_balance_category_id =>l_bal_cat_id,
x_base_balance_type_id =>l_bt_id,
x_input_value_id =>l_prim_iv);
insert_primary_balance_feed
(p_balance_type_id => v_balance_type_id
,p_primary_bal_iv_id => l_prim_iv
,p_primary_bal_ele_id => l_prim_ele
,p_business_group_id => v_business_group_id
,p_legislation_code => v_legislation_code
,p_effective_date => p_effective_date
,p_mode => l_mode
);
select ss.effective_date
into v_session_date
from fnd_sessions ss
where ss.session_id = userenv('sessionid');
select pay_balance_classifications_s.nextval
into v_balance_classification_id
from sys.dual;
select bg.business_group_id,
bg.legislation_code
into v_business_group_id,
v_legislation_code
from per_business_groups bg
where bg.name = p_business_group_name;
select fnd_number.canonical_to_number(lookup_code)
into v_scale
from hr_lookups
where lookup_type = 'ADD_SUBTRACT'
and upper(meaning) = upper(p_scale);
select bt.balance_type_id,
bt.legislation_subgroup
into v_balance_type_id,
v_legislation_subgroup
from pay_balance_types bt
where upper(bt.balance_name) = upper(p_balance_name)
and nvl(bt.business_group_id,nvl(v_business_group_id,-1))
= nvl(v_business_group_id,-1)
and nvl(bt.legislation_code,nvl(p_legislation_code,'-1'))
= nvl(p_legislation_code,'-1');
select cl.classification_id
,decode(cl.parent_classification_id, null,'N','Y')
into v_classification_id, v_secondary_class
from pay_element_classifications cl
where upper(cl.classification_name) = upper(p_balance_classification)
-- and cl.parent_classification_id is NULL
and cl.legislation_code = v_legislation_code;
insert into pay_balance_classifications
(BALANCE_CLASSIFICATION_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
BALANCE_TYPE_ID,
CLASSIFICATION_ID,
SCALE,
LEGISLATION_SUBGROUP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values(
v_balance_classification_id,
v_business_group_id,
p_legislation_code,
v_balance_type_id,
v_classification_id,
v_scale,
v_legislation_subgroup,
v_todays_date,
-1,
-1,
-1,
v_todays_date);
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select balance_category_id
from pay_balance_types
where balance_name = p_bal_type
and nvl(legislation_code,'NULL') = nvl(p_leg_code, 'NULL')
and nvl(business_group_id, -1) = nvl(p_bg_id, -1);
select business_group_id,
legislation_code
into v_business_group_id,
v_legislation_code
from per_business_groups
where name = p_business_group_name;
select bd.balance_dimension_id
into v_balance_dimension_id
from pay_balance_dimensions bd
where upper(bd.dimension_name) = upper(p_balance_dimension)
and bd.legislation_code = v_legislation_code;
select pay_defined_balances_s.nextval
, bt.business_group_id
, bt.legislation_code
, bt.balance_type_id
, bt.legislation_subgroup
, bt.balance_category_id
into v_def_bal_nextval
, v_bt_business_group_id
, v_bt_legislation_code
, v_bt_balance_type_id
, v_bt_legislation_subgroup
, v_bt_balance_category_id
from pay_balance_types bt
where upper(bt.balance_name) = upper(p_balance_name)
and nvl(bt.business_group_id,nvl(v_business_group_id,-1))
= nvl(v_business_group_id,-1)
and nvl(bt.legislation_code,nvl(v_legislation_code,'-1'))
= nvl(v_legislation_code,'-1');
insert into pay_defined_balances
(DEFINED_BALANCE_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
BALANCE_TYPE_ID,
BALANCE_DIMENSION_ID,
FORCE_LATEST_BALANCE_FLAG,
LEGISLATION_SUBGROUP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
save_run_balance)
values
(v_def_bal_nextval
,v_dfb_business_group_id
,v_dfb_legislation_code
,v_bt_balance_type_id
,v_balance_dimension_id
,p_frce_ltst_balance_flag
,v_dfb_legislation_subgroup
,v_todays_date
,-1
,-1
,-1
,v_todays_date
,l_run_bal_flag
);
select defined_balance_id
into l_defined_balance_id
from pay_defined_balances
where balance_type_id = v_bt_balance_type_id
and balance_dimension_id = v_balance_dimension_id
and nvl(business_group_id,nvl(v_dfb_business_group_id,-1))
= nvl(v_dfb_business_group_id,-1)
and nvl(legislation_code,nvl(v_dfb_legislation_code,'-1'))
= nvl(v_dfb_legislation_code,'-1');
pay_defined_balances_pkg.insert_default_attrib_wrapper
(p_balance_dimension_id => v_balance_dimension_id
,p_balance_category_id => v_bt_balance_category_id
,p_def_bal_bg_id => v_dfb_business_group_id
,p_def_bal_leg_code => v_dfb_legislation_code
,p_defined_balance_id => l_defined_balance_id
,p_effective_date => l_eff_date
);
insert_customize_restriction
DESCRIPTION
Creates a new customize restriction type.
NOTES
This function returns the customized_restriction_id of the row it has
created and inserted into pay_customized_restrictions.
*/
--
FUNCTION insert_customize_restriction
( p_business_group_id number default NULL,
p_name varchar2,
p_form_name varchar2,
p_query_form_title varchar2,
p_standard_form_title varchar2,
p_enabled_flag varchar2 default 'N',
p_legislation_subgroup varchar2 default NULL,
p_legislation_code varchar2 default NULL
) return number is
-- Constants
v_todays_date constant date := trunc(sysdate);
hr_utility.set_location('insert_customize_restriction',1);
select 'Y'
into v_name_already_exists
from pay_customized_restrictions pcr
where pcr.form_name = p_form_name;
hr_utility.set_location('insert_customize_restriction',2);
hr_utility.set_location('insert_customize_restriction',3);
select f.application_id
into v_application_id
from fnd_form f
where f.form_name = p_form_name
and f.application_id between 800 and 899
and exists
(select 1
from pay_restriction_parameters prp
where prp.form_name = f.form_name
and prp.application_id = f.application_id);
hr_utility.set_location('insert_customize_restriction',5);
PER_CUSTOMIZED_RESTR_PKG.INSERT_ROW (
X_ROWID => v_rowid,
X_CUSTOMIZED_RESTRICTION_ID => v_customized_restriction_id,
X_BUSINESS_GROUP_ID => p_business_group_id ,
X_LEGISLATION_CODE => p_legislation_code,
X_APPLICATION_ID => v_application_id ,
X_FORM_NAME => p_form_name,
X_ENABLED_FLAG => p_enabled_flag,
X_NAME => p_name,
X_COMMENTS => null,
X_LEGISLATION_SUBGROUP => p_legislation_subgroup,
X_QUERY_FORM_TITLE => p_query_form_title,
X_STANDARD_FORM_TITLE => p_standard_form_title,
X_CREATION_DATE => v_todays_date,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => v_todays_date,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => -1
);
hr_utility.set_location('insert_customize_restriction',6);
end insert_customize_restriction;
insert_restriction_values
DESCRIPTION
This procedure adds a new restriction value for the specified customization
restriction.
NOTES
*/
PROCEDURE insert_restriction_values
( p_customized_restriction_id number,
p_restriction_code varchar2,
p_value varchar2
) IS
--
-- Constants
--
v_todays_date constant date := trunc(sysdate);
insert into PAY_RESTRICTION_VALUES
(CUSTOMIZED_RESTRICTION_ID
,RESTRICTION_CODE
,VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE)
values
(p_customized_restriction_id ,p_restriction_code
,p_value
,v_todays_date
,-1
,-1
,-1
,v_todays_date);
end insert_restriction_values;
select rule_mode
from pay_legislation_rules
where rule_type = p_rule_type
and legislation_code = p_legislation_code;
select cu.currency_code
into l_currency_code
from fnd_currencies cu
where cu.issuing_territory_code = p_legislation_code
and cu.enabled_flag = 'Y';