[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
value returned from the supplied select statement.
Arguments :
Notes :
*/
function get_business_group (p_statement varchar2) return number
is
sql_cur number;
value returned from the supplied select statement.
Arguments :
Notes :
*/
function get_business_group_withbind (p_statement varchar2, p_bindvar number) return number
is
-- bug 13075414 overload to pass bind variable so reduce hard parsing
business_group_id number;
value returned from the supplied select statement.
This should be used only from the dynamic triggers and only
when its required to use the cached business group.
(Do not use unless you are very sure)
Arguments :
Notes :
*/
function get_dyt_business_group (p_statement varchar2) return number
is
sql_cur number;
value returned from the supplied select statement.
This should be used only from the dynamic triggers and only
when its required to use the cached business group.
(Do not use unless you are very sure)
Arguments :
Notes :
*/
function get_dyt_business_group (p_statement varchar2, p_bindvar number) return number
is
sql_cur number;
select legislation_code
into g_legislation_code
from per_business_groups_perf
where business_group_id = p_bg_id;
select paa.assignment_id,
ppa.business_group_id,
pbg.legislation_code
into l_asg_id,
l_bus_grp_id,
l_leg_code
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_business_groups_perf pbg
where paa.assignment_action_id = p_asg_act_id
and ppa.business_group_id = pbg.business_group_id
and paa.payroll_action_id = ppa.payroll_action_id;
select ptp.start_date
into l_start_date
from per_time_periods ptp,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date;
select nvl(proration_group_id, -1), nvl(time_definition_type, 'N')
into l_proration_group_id, l_time_definition_type
from pay_element_types_f
where element_type_id = p_element_type_id
and p_date_earned between effective_start_date
and effective_end_date;
select ppa.date_earned,
ptp.end_date,
ptp.start_date,
pet.proration_group_id
into l_date_earned,
l_prd_end_date,
l_prd_start_date,
l_prorate_grp_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp,
pay_element_types_f pet
where paa.assignment_action_id = p_asg_act_id
and paa.payroll_action_id = ppa.payroll_action_id
and pet.element_type_id = p_et_id
and ppa.date_earned between pet.effective_start_date
and pet.effective_end_date
and ppa.date_earned between ptp.start_date
and ptp.end_date
and ptp.payroll_id = ppa.payroll_id;
/* Remember we only deal with updates */
if (p_arr_cnt = 1) then
p_start_date := to_char(l_prd_start_date, 'YYYY/MM/DD HH24:MI:SS');
select pay_run_results_s.nextval
into rr_id
from sys.dual;
select pay_assignment_actions_s.nextval
into aa_id
from sys.dual;
select pay_run_balances_s.nextval
into rb_id
from sys.dual;
insert into pay_message_lines
(
LINE_SEQUENCE,
PAYROLL_ID,
MESSAGE_LEVEL,
SOURCE_ID,
SOURCE_TYPE,
LINE_TEXT
)
values
(
pay_message_lines_s.nextval,
null,
l_sev_level,
p_pactid,
'P',
substr(l_msg_text,0,240)
);
select INTLK.locking_action_id
into l_action_id
from pay_action_interlocks INTLK,
pay_assignment_actions paa,
pay_payroll_actions ppa
where INTLK.locked_action_id = p_action_id
and INTLK.locking_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('P', 'U')
and paa.source_action_id is null;
select paa.assignment_action_id,
nvl(prt.run_method, 'N'),
paa.start_date
from pay_assignment_actions paa,
pay_run_types_f prt
where paa.source_action_id = p_action
and paa.run_type_id = prt.run_type_id (+);
select nvl(nvl(prt_aa.run_method, prt_pa.run_method), 'N')
into l_run_method
from pay_run_types_f prt_aa,
pay_run_types_f prt_pa,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_calling_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.run_type_id = prt_aa.run_type_id (+)
and ppa.effective_date between nvl(prt_aa.effective_start_date, ppa.effective_date)
and nvl(prt_aa.effective_end_date, ppa.effective_date)
and ppa.run_type_id = prt_pa.run_type_id (+)
and ppa.effective_date between nvl(prt_pa.effective_start_date, ppa.effective_date)
and nvl(prt_pa.effective_end_date, ppa.effective_date);
select parameter_value
into p_para_value
from pay_action_parameters
where parameter_name = p_para_name
or parameter_name=REPLACE(p_para_name,' ','_')
or parameter_name=REPLACE(p_para_name,'_',' ');
select parameter_value
into p_para_value
from pay_report_format_parameters prfp,
pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and ppa.report_type = prfm.report_type
and ppa.report_qualifier = prfm.report_qualifier
and ppa.report_category = prfm.report_category
and prfm.report_format_mapping_id = prfp.report_format_mapping_id
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date
and ( parameter_name = p_para_name
or parameter_name=REPLACE(p_para_name,' ','_')
or parameter_name=REPLACE(p_para_name,'_',' ')
);
select rule_mode
into p_legrul_value
from pay_legislation_rules
where rule_type = p_legrul_name
and legislation_code = p_legislation;
elsif p_legrul_name = 'RETRO_DELETE' then
p_legrul_value := 'N';
select fc.context_name
,plc.input_value_name
,decode(fc.context_name
,'JURISDICTION_CODE' ,'JURISDICTION_IV'
,'SOURCE_ID' ,'SOURCE_IV'
,'SOURCE_TEXT' ,'SOURCE_TEXT_IV'
,'SOURCE_TEXT2' ,'SOURCE_TEXT2_IV'
,'SOURCE_NUMBER' ,'SOURCE_NUMBER_IV'
,null
) rule_type
from pay_legislation_contexts plc,
ff_contexts fc
where plc.legislation_code(+) = p_legislation
and plc.context_id (+) = fc.context_id
and fc.context_name in
('JURISDICTION_CODE'
,'SOURCE_ID'
,'SOURCE_TEXT'
,'SOURCE_TEXT2'
,'SOURCE_NUMBER'
,'SOURCE_NUMBER2'
,'ORGANIZATION_ID');
select fc.context_name,
pbg.legislation_code
from ff_contexts fc,
per_business_groups_perf pbg
where pbg.business_group_id = p_bus_grp
and fc.context_name in ('JURISDICTION_CODE',
'SOURCE_ID',
'SOURCE_TEXT',
'SOURCE_NUMBER',
'SOURCE_TEXT2',
'SOURCE_NUMBER2',
'ORGANIZATION_ID');
p_context_list.delete;
select distinct piv.input_value_id
into l_ivid
from pay_input_values_f piv,
pay_element_entry_values_f peev
where peev.element_entry_id = p_ee_id
and peev.input_value_id = piv.input_value_id
and piv.name = p_context_name;
select count(*)
into l_exists
from pay_element_entry_values_f peev
where peev.element_entry_id = p_ee_id
and peev.input_value_id = l_ivid
and peev.screen_entry_value = p_context_value;
select prr.source_id
from pay_run_results prr,
pay_element_types_f pet
where prr.assignment_action_id = p_aa_id
and prr.source_type = 'E'
and prr.element_type_id = pet.element_type_id
and nvl(pet.process_mode, 'N') in ('P', 'S')
and prr.entry_type not in ('A', 'R')
and p_eff_date between pet.effective_start_date
and pet.effective_end_date
order by decode (prr.status,
'P', 1,
'B', 2,
3),
decode (prr.entry_type,
'S', 1,
2);
select nvl(prt.run_method, 'N'),
ppa.effective_date
into l_run_meth,
l_eff_date
from pay_run_types_f prt,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_asg_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
and ppa.effective_date
between nvl(prt.effective_start_date, ppa.effective_date)
and nvl(prt.effective_end_date, ppa.effective_date);
select run_type_id,
source_action_id,
to_char(start_date, 'YYYY/MM/DD HH24:MI:SS'),
to_char(end_date, 'YYYY/MM/DD HH24:MI:SS')
into l_run_type_id,
l_src_id,
l_start_date,
l_end_date
from pay_assignment_actions
where assignment_action_id = p_asg_action_id;
g_sql_cursors.delete;
g_sql_cursors.delete(l_cnt);
select pud.upgrade_definition_id,
pud.legislation_code,
pud.upgrade_level,
pud.failure_point,
pud.legislatively_enabled
into l_upgrade_definition_id,
l_legislation_code,
l_upgrade_level,
l_failure_point,
l_legislatively_enabled
from pay_upgrade_definitions pud
where pud.short_name = p_short_name;
select pbg.legislation_code
into l_bg_leg_code
from per_business_groups_perf pbg
where pbg.business_group_id = p_bus_grp_id;
select 1
into l_dummy
from pay_upgrade_legislations pul
where pul.upgrade_definition_id = l_upgrade_definition_id
and pul.legislation_code = l_bg_leg_code;
select pus.status
into l_upgrade_status
from pay_upgrade_status pus
where pus.upgrade_definition_id = l_upgrade_definition_id
and pus.business_group_id = p_bus_grp_id;
select pus.status
into l_upgrade_status
from pay_upgrade_status pus
where pus.upgrade_definition_id = l_upgrade_definition_id
and pus.legislation_code = l_bg_leg_code;
select pus.status
into l_upgrade_status
from pay_upgrade_status pus
where pus.upgrade_definition_id = l_upgrade_definition_id
and pus.legislation_code is null
and pus.business_group_id is null;
select ppf.payroll_name
into l_payroll_name
from pay_payroll_actions ppa,
pay_payrolls_f ppf
where ppa.payroll_action_id=p_pactid
and nvl(ppa.payroll_id,-9999)=ppf.payroll_id
and ppa.effective_date between ppf.effective_start_date and effective_end_date;
select pbg.legislation_code
into g_leg_code
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_business_groups_perf pbg
where paa.assignment_action_id = p_asg_act_id
and ppa.business_group_id = pbg.business_group_id
and paa.payroll_action_id = ppa.payroll_action_id;
SELECT nvl(INCLUSION_FLAG, 'Y')
FROM PAY_ELEMENT_CLASS_USAGES_F pecu,
PAY_ELEMENT_TYPES_F pet,
PAY_ELEMENT_CLASSIFICATIONS pec
WHERE pet.element_type_id = p_element_type_id
AND pet.classification_id = pecu.classification_id
AND pec.classification_id = pet.classification_id
-- Only checking for primary classifications here
-- Will also need to check for sub classifications exclusions
AND pec.PARENT_CLASSIFICATION_ID is null
AND pecu.run_type_id = p_run_type_id
AND (pecu.business_group_id = p_business_group_id
OR (pecu.business_group_id is null
AND pecu.legislation_code = p_legislation)
OR (pecu.business_group_id is null and pecu.legislation_code is null))
AND (pec.business_group_id = p_business_group_id
OR (pec.business_group_id is null
AND pec.legislation_code = p_legislation)
OR (pec.business_group_id is null and pec.legislation_code is null))
AND (pet.business_group_id = p_business_group_id
OR (pet.business_group_id is null
AND pet.legislation_code = p_legislation)
OR (pet.business_group_id is null and pet.legislation_code is null))
AND p_effective_date between pet.effective_start_date
and pet.effective_end_date
AND p_effective_date between pecu.effective_start_date
and pecu.effective_end_date;
SELECT nvl(INCLUSION_FLAG, 'Y')
FROM PAY_ELEMENT_CLASS_USAGES_F pecu
WHERE pecu.run_type_id = p_run_type_id
AND (pecu.business_group_id = p_business_group_id
OR (pecu.business_group_id is null
AND pecu.legislation_code = p_legislation)
OR (pecu.business_group_id is null and pecu.legislation_code is null))
AND p_effective_date between pecu.effective_start_date
and pecu.effective_end_date;
SELECT INCLUSION_FLAG
FROM pay_element_type_usages_f
WHERE element_type_id = p_element_type_id
AND run_type_id = p_run_type_id
AND nvl(usage_type, 'I') = 'I'
AND (business_group_id = p_business_group_id
OR (business_group_id is null
AND legislation_code = p_legislation)
OR (business_group_id is null and legislation_code is null))
AND p_effective_date between effective_start_date
and effective_end_date;
PROCEDURE update_prj_flag ( p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE ,
p_flag IN VARCHAR2,
p_status OUT NOCOPY varchar2
) IS
lv_proc VARCHAR2(30) := 'update_prj_flag';
SELECT DISTINCT input_value_id
FROM pay_input_values_f
WHERE element_type_id=v_element_type_id
AND NAME = 'Pay Value';
UPDATE pay_costs SET transfered_to_prj = p_flag
WHERE input_value_id = v_input_value_id
AND assignment_action_id = p_assignment_action_id;