[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Function to insert an Org_payment_method.
NOTES
Called using...
--
--opm_id := ins_opm(to_date(start_date,'DD-MM-YYYY'),
-- to_date(end_date,'DD-MM-YYYY'),
-- bg_id,
-- exa_id,
-- currency,
-- payment_type,
-- name);
select bg.legislation_code
into legislation_code
from per_business_groups bg
where bg.business_group_id = p_business_group_id;
select pay_org_payment_methods_s.nextval
into method_id
from dual;
insert into pay_org_payment_methods_f
(ORG_PAYMENT_METHOD_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
EXTERNAL_ACCOUNT_ID,
CURRENCY_CODE,
PAYMENT_TYPE_ID,
ORG_PAYMENT_METHOD_NAME,
DEFINED_BALANCE_ID)
values
(method_id,
effective_start_date,
effective_end_date,
p_business_group_id,
external_account_id,
currency_code,
payment_type_id,
name,
defined_balance);
insert into pay_org_payment_methods_f_tl
(ORG_PAYMENT_METHOD_ID,
ORG_PAYMENT_METHOD_NAME,
LANGUAGE,
SOURCE_LANG)
values
(method_id,
name,
userenv('LANG'),
userenv('LANG'));
insert an external account
NOTES
Called as...
--
account_id := ins_exa(territory);
select pay_external_accounts_s.nextval
into account_id
from dual;
insert into pay_external_accounts(
external_account_id,
territory_code,
id_flex_num,
summary_flag,
enabled_flag)
values(
account_id,
territory_code,
1,
'N',
'Y');
Insert pay_method_usages
NOTES
Called as
--
pmu := ins_pmu(to_date(start,'DD-MM-YYYY'),
to_date(end,'DD-MM-YYYY'),
payroll_id,
opm_id);
select opmu.effective_start_date,
opmu.effective_end_date,
org_pay_method_usage_id
from pay_org_pay_method_usages_f opmu
where opmu.payroll_id = payroll
and opmu.org_payment_method_id = payment_method
and opmu.effective_start_date <= end_date
and opmu.effective_end_date >= start_date
order by opmu.effective_start_date
for update;
v_insert_record boolean := TRUE;
select org_pay_method_usage_id
into pmu_id
from pay_org_pay_method_usages_f
where payroll_id = payroll
and org_payment_method_id = payment_method
and effective_start_date < end_date
and effective_end_date > start_date;
select pay_org_pay_method_usages_s.nextval
into pmu_id
from sys.dual;
insert into pay_org_pay_method_usages_f
(org_pay_method_usage_id,
effective_start_date,
effective_end_date,
org_payment_method_id,
payroll_id)
values
(pmu_id,
start_date,
end_date,
payment_method,
payroll);
v_insert_record := FALSE;
update pay_org_pay_method_usages_f opmu
set opmu.effective_end_date = start_date - 1
where current of csr_payroll_opmu;
update pay_org_pay_method_usages_f opmu
set opmu.effective_start_date = end_date + 1
where current of csr_payroll_opmu;
delete from pay_org_pay_method_usages_f
where current of csr_payroll_opmu;
if v_insert_record then
--
select pay_org_pay_method_usages_s.nextval into pmu_id from sys.dual;
insert into pay_org_pay_method_usages_f
(org_pay_method_usage_id,
effective_start_date,
effective_end_date,
payroll_id,
org_payment_method_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values
(pmu_id,
start_date,
end_date,
payroll,
payment_method,
trunc(sysdate),
0,
0,
0,
trunc(sysdate));
insert personal payment method
NOTES
Call as...
--
ppm_id := ins_ppm(to_date(start,'DD-MM-YYYY'),
to_date(end,'DD-MM-YYYY'),
bg_id,
account,
assignment,
opm_id,
amount,
percentage,
priority);
select pay_personal_payment_methods_s.nextval
into ppm_id
from dual;
insert into pay_personal_payment_methods_f(
personal_payment_method_id,
effective_start_date,
effective_end_date,
business_group_id,
external_account_id,
assignment_id,
org_payment_method_id,
amount,
percentage,
priority)
values(
ppm_id,
start_date,
end_date,
business_group_id,
external_account,
assignment_id,
opm_id,
amount,
percentage,
priority);
insert exchaneg rates to and from two currencies
NOTES
--
*/
--
function ins_exr(start_date in date,
end_date in date,
base_currency in varchar2,
other_currency in varchar2,
rate_type in varchar2 default 'Payroll',
rate in number,
inverse_rate in number) return number is
exchange_rate number(16);
insert into gl_daily_rates_interface
(from_currency,
to_currency,
from_conversion_date,
to_conversion_date,
user_conversion_type,
conversion_rate,
inverse_conversion_rate,
mode_flag)
VALUES (
base_currency,
other_currency,
start_date ,
end_date,
rate_type,
rate,
inverse_rate,
'I');
insert payroll action
NOTES
Fill in other params as required, but do not default!
*/
function ins_ppa(action_type varchar2,
business_group number,
consolidation_set number,
payroll number,
pop_status varchar2,
action_status varchar2,
action_date date,
parameters varchar2) return number is
action_id number(16);
select pay_payroll_actions_s.nextval
into action_id
from dual;
insert into pay_payroll_actions(
payroll_action_id,
action_type,
business_group_id,
consolidation_set_id,
payroll_id,
action_population_status,
action_status,
effective_date,
legislative_parameters)
values(
action_id,
action_type,
business_group,
consolidation_set,
payroll,
pop_status,
action_status,
action_date,
parameters);
insert assignment_actions
NOTES
--
*/
--
function ins_paa(assignment number,
payroll_action number,
status varchar2 default 'U',
chunk number default null,
sequence number default null,
pre_payment number default null,
serial_no varchar2 default null) return number is
action_id number(16);
select pay_assignment_actions_s.nextval
into action_id
from dual;
insert into pay_assignment_actions(
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
pre_payment_id,
serial_number)
values(
action_id,
assignment,
payroll_action,
status,
chunk,
sequence,
pre_payment,
serial_no);
insert a payroll
NOTES
Note that pay_db_pay_setup.create_payroll should be used. This is just a
temporary measure
*/
FUNCTION ins_payroll(payroll_name varchar2,
number_of_years number,
period_type varchar2,
first_period_end_date date,
dflt_payment_method number,
pay_date_offset number default 0,
direct_deposit_date_offset number default 0,
pay_advice_date_offset number default 0,
cut_off_date_offset number default 0,
consolidation_set number,
negative_pay_allowed_flag varchar2 default 'N',
organization number default NULL,
midpoint_offset number default 0,
workload_shifting_level varchar2 default 'N',
effective_start_date date,
effective_end_date date,
business_group number)
RETURN number is
payroll_id number(16);
select pay_payrolls_s.nextval
into payroll_id
from dual;
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,
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)
values
(payroll_id,
effective_start_date,
effective_end_date,
dflt_payment_method,
business_group,
consolidation_set,
nvl(organization,business_group),
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);
select business_group_id
into business_group
from per_organization_units
where name = input_bg_name;
select c.user_column_id,
r.user_row_id,
t.user_table_id
into column_id,row_id,table_id
from pay_user_columns C
,pay_user_rows_f R
,pay_user_tables T
where t.user_table_name='EXCHANGE_RATE_TYPES'
and t.USER_ROW_TITLE='Processing Type'
and r.user_table_id=t.user_table_id
and c.user_table_id=t.user_table_id
and c.user_column_name='Conversion Rate Type'
and r.row_low_range_or_name='PAY';
insert into pay_user_column_instances_f (
user_column_instance_id,
effective_start_date,
effective_end_date,
user_row_id,
user_column_id,
business_group_id,
value)
values (pay_user_column_instances_s.nextval,
to_date('01-01-1990', 'DD-MM-YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
row_id,
column_id,
business_group,
'Payroll');
select dimension_name
into pay_dimension
from pay_balance_dimensions
where payments_flag = 'Y'
and legislation_code is NULL;
select payment_type_id
into cash_type
from pay_payment_types
where payment_type_name = 'Cash';
select payment_type_id
into check_type
from pay_payment_types
where payment_type_name = 'Check';
select payment_type_id
into nacs_type
from pay_payment_types
where payment_type_name = 'NACS';
select payment_type_id
into cheque_type
from pay_payment_types
where payment_type_name = 'Cheque';
select count(*)
into dummy
from gl_daily_rates
where conversion_type = 'Payroll'
and from_currency = 'NVS'
and to_currency = 'GBP';
insert into gl_daily_conversion_types
(CONVERSION_TYPE,
USER_CONVERSION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
values (
'Payroll',
'Payroll',
to_date('01-01-1990','DD-MM-YYYY'),
0);
select assignment_id
into assignment1
from per_all_assignments_f
where person_id = person;
select assignment_id
into assignment2
from per_all_assignments_f
where person_id = person;
select assignment_id
into assignment3
from per_all_assignments_f
where person_id = person;
select assignment_id
into assignment4
from per_all_assignments_f
where person_id = person;
select assignment_id
into assignment5
from per_all_assignments_f
where person_id = person;
select assignment_id
into assignment6
from per_all_assignments_f
where person_id = person;