[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : This returns the select statement that is used to created the
range rows.
Arguments :
Notes :
*/
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
begin
--
-- hr_utility.trace('>>> NOW build sql string');
sqlstr := 'select distinct asg.person_id
from
per_assignments_f asg,
pay_payroll_actions pa1
where pa1.payroll_action_id = :payroll_action_id
and asg.payroll_id =
pay_core_utils.get_parameter(''PAYROLL_ID'',
pa1.legislative_parameters)
and pa1.effective_date < asg.effective_end_date
order by asg.person_id';
select distinct ppe.assignment_id,ppe.creation_date
from
per_all_assignments_f paf,
per_periods_of_service pos,
pay_payroll_actions ppa,
pay_process_events ppe
where ppa.payroll_action_id = cp_pactid
and paf.payroll_id = cp_payroll_id
and ppe.effective_date between paf.effective_start_date
and paf.effective_end_date
and pos.period_of_service_id = paf.period_of_service_id
and pos.person_id between cp_stperson and cp_endperson
and ppe.assignment_id = paf.assignment_id
and ppe.change_type in ('ASG', 'GRE', 'COST_CENTRE', 'PAYMENT',
'DATE_EARNED', 'DATE_PROCESSED');
select
pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_actid;
SELECT pay_assignment_actions_s.nextval
INTO l_lockingactid
FROM dual;
select nvl(min(ppe.effective_date), hr_api.g_eot)
effective_date
from pay_process_events ppe,
pay_assignment_actions paa
where paa.assignment_action_id = assact_id
and paa.assignment_id = ppe.assignment_id
and change_type = p_change_type;
select paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_assact_id
and paa.assignment_id = paa_arch.assignment_id
and paa.action_status = 'C'
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = 'C'
and ppa.effective_date >= p_effdate
and not exists (select ''
from pay_action_interlocks pai
where pai.locked_action_id = paa.assignment_action_id)
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_assact_id
and paa.assignment_id = paa_arch.assignment_id
and paa.action_status = 'C'
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('P', 'U')
and ppa.effective_date >= p_effdate
and not exists (select ''
from pay_action_interlocks pai
where pai.locked_action_id = paa.assignment_action_id)
order by paa.action_sequence desc;
select paa.assignment_action_id,
ppa.effective_date,
paa.assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_assact_id
and paa.assignment_id = paa_arch.assignment_id
and paa.action_status in ('C', 'S')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.source_action_id is null
and ppa.action_type in ('R', 'Q','V','B')
and ppa.effective_date >= p_effdate
and not exists (select ''
from pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2,
pay_action_interlocks pai2
where pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in ('P', 'U', 'C')
and pai2.locked_action_id = paa2.assignment_action_id
)
and not exists (
select null
from pay_action_classifications acl,
pay_payroll_actions pa2,
pay_assignment_actions ac2
where ac2.assignment_id = paa.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and pa2.action_type not in ('R', 'Q','V','B')
and ac2.action_sequence > paa.action_sequence
)
and not exists (
select null
from pay_action_classifications acl,
pay_payroll_actions pa2,
pay_assignment_actions ac2
where ac2.assignment_id = paa.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and pa2.action_type in ('R', 'Q','V','B')
and ac2.action_sequence > paa.action_sequence
and exists (select ''
from pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2,
pay_action_interlocks pai2
where pai.locked_action_id = ac2.assignment_action_id
and pai.locking_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in ('P', 'U', 'C')
and pai2.locked_action_id = paa2.assignment_action_id
)
)
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
where pai.locked_action_id = p_assact_id
and pai.locking_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and ppa.action_type in ('P','U', 'C')
order by paa.action_sequence desc;
select
paa.assignment_id,
pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters),
ppa.business_group_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = cp_actid;
select min(ppa.date_earned)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = cp_assact_id
and paa.assignment_id = paa_arch.assignment_id
and paa.action_status in ('C', 'S')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.source_action_id is null
and ppa.action_type in ('R', 'Q')
and not exists (
select 1
from pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2,
pay_action_interlocks pai2
where pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in ('P', 'U', 'C')
and pai2.locked_action_id = paa2.assignment_action_id
)
and not exists (
select 1
from pay_action_classifications acl,
pay_payroll_actions pa2,
pay_assignment_actions ac2
where ac2.assignment_id = paa.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and pa2.action_type not in ('R', 'Q')
and ac2.action_sequence > paa.action_sequence
);
SELECT DISTINCT
prr.source_id entry
, pet.recalc_event_group_id event_group
FROM pay_run_results prr
, pay_assignment_actions paa
, pay_payroll_actions ppa
, pay_assignment_actions paa_arch
, pay_element_types_f pet
WHERE paa_arch.assignment_action_id = cp_assact_id
and paa.assignment_id = paa_arch.assignment_id
AND prr.source_type = 'E'
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND prr.element_type_id = pet.element_type_id
AND cp_this_run_date between pet.effective_start_date and pet.effective_end_date
AND ppa.business_group_id = cp_bg
AND ppa.action_type in ('R', 'Q', 'B', 'V')
AND ppa.date_earned >= cp_de_min
UNION
SELECT DISTINCT
pee.element_entry_id entry
, pet.recalc_event_group_id event_group
FROM pay_element_entries_f pee
, pay_assignment_actions paa
, pay_element_links_f pel
, pay_element_types_f pet
WHERE paa.assignment_action_id = cp_assact_id
AND paa.assignment_id = pee.assignment_id
AND pee.element_link_id = pel.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pee.effective_end_date
between pel.effective_start_date and pel.effective_end_date
AND cp_this_run_date
between pet.effective_start_date and pet.effective_end_date
AND pee.effective_end_date >= cp_de_min;
select least(effective_date)
from pay_process_events
where creation_date > cp_creation_date_from
and change_type = cp_change_type
and status <> 'C'
and assignment_id = cp_ass_id;
select sysdate
into l_run_date
from dual;
select nvl(min(ppa.effective_date), hr_api.g_eot)
into l_aact_min_dedate_eff
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_assactid
and paa.assignment_id = paa_arch.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q')
and ppa.date_earned >= l_aact_min_dedate;
select
pay_core_utils.get_parameter('REMOVE_ACT',pa1.legislative_parameters),
pay_core_utils.get_parameter('PAYROLL_ID',pa1.legislative_parameters)
from pay_payroll_actions pa1
where pa1.payroll_action_id = cp_pactid;
p_bg_select in varchar2
)
is
--
-- cursor get_columns (p_tab_name in varchar2)
-- is
-- select substr(column_name, 1, 24) column_name,
-- column_name full_column_name,
-- data_type
-- from all_tab_columns
-- where table_name = p_tab_name
-- and owner = g_pay_schema
-- and column_name not in ('LAST_UPDATE_DATE',
-- 'LAST_UPDATED_BY',
-- 'LAST_UPDATE_LOGIN',
-- 'CREATED_BY',
-- 'CREATION_DATE',
-- 'OBJECT_VERSION_NUMBER')
-- and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
-- order by column_name;
select substr(tab.column_name, 1, 24) column_name,
tab.column_name full_column_name,
tab.data_type
from dba_tab_columns tab, user_synonyms syn
where syn.synonym_name = p_tab_name
and tab.table_name = syn.table_name
and tab.owner = syn.table_owner
and tab.owner = g_pay_schema
and tab.column_name not in ('LAST_UPDATE_DATE',
'LAST_UPDATED_BY',
'LAST_UPDATE_LOGIN',
'CREATED_BY',
'CREATION_DATE',
'OBJECT_VERSION_NUMBER')
and tab.data_type in ('NUMBER', 'VARCHAR2', 'DATE')
order by column_name;
'Continuous Calcuation trigger on update of '||p_table_name,
'N',
'N',
'U',
NULL
);
p_bg_select,
'N',
NULL
);
p_bg_select in varchar2 default null,
p_mode in varchar2 default 'PROCEDURE')
is
--
cursor dtexists is
select dated_table_id, object_version_number
from pay_dated_tables pdt
where table_name = p_table_name;
p_pkg_proc_name, p_bg_select);
Purpose : This procedure generates a default continuous calc update
procedure for the specified Table. The procedure is
generated into the log file.
Arguments :
Notes :
*/
procedure generate_cc_procedure(p_table_name in varchar2,
p_surr_key_name in varchar2,
p_eff_str_name in varchar2,
p_eff_end_name in varchar2,
p_owner in varchar2
)
is
--
-- cursor get_columns (p_tab_name in varchar2,
-- p_start in varchar2,
-- p_end in varchar2,
-- l_owner in varchar2)
-- is
-- select substr(column_name, 1, 24) column_name,
-- column_name full_column_name,
-- data_type
-- from all_tab_columns
-- where table_name = p_tab_name
-- and owner = l_owner
-- and column_name not in ('LAST_UPDATE_DATE',
-- 'LAST_UPDATED_BY',
-- 'LAST_UPDATE_LOGIN',
-- 'CREATED_BY',
-- 'CREATION_DATE',
-- 'OBJECT_VERSION_NUMBER')
-- and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
-- order by decode (column_name, p_start, 3,
-- p_end, 2,
-- 1),
-- column_name;
select substr(tab.column_name, 1, 24) column_name,
tab.column_name full_column_name,
tab.data_type
from dba_tab_columns tab, user_synonyms syn
where syn.synonym_name = p_tab_name
and tab.table_name = syn.table_name
and tab.owner = syn.table_owner
and tab.owner = l_owner
and tab.column_name not in ('LAST_UPDATE_DATE',
'LAST_UPDATED_BY',
'LAST_UPDATE_LOGIN',
'CREATED_BY',
'CREATION_DATE',
'OBJECT_VERSION_NUMBER')
and tab.data_type in ('NUMBER', 'VARCHAR2', 'DATE')
order by decode (column_name, p_start, 3,
p_end, 2,
1),
column_name;
pay_continuous_calc.event_update(p_business_group_id,
p_legislation_code,
'''||p_table_name||''',
'''||colrec.full_column_name||''',
p_old_'||colrec.column_name||',
p_new_'||colrec.column_name||',';
p_event_update_id => pay_continuous_calc.g_event_list.event_update_id(cnt),
p_business_group_id => p_business_group_id,
p_calculation_date => pay_continuous_calc.g_event_list.calc_date(cnt),
p_surrogate_key => p_new_'||lower(p_surr_key_name)||'
);
select ppa.payroll_id
from
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = cp_asg_act_id
and paa.payroll_action_id = ppa.payroll_action_id;
select assignment_id
from pay_assignment_actions paa
where paa.assignment_action_id = cp_asg_act_id;
select intloc.locking_action_id
from pay_assignment_actions assact,
pay_action_interlocks intloc,
pay_payroll_actions pact
where intloc.locked_action_id = p_assignment_action_id
and intloc.locking_action_id = assact.assignment_action_id
and assact.payroll_action_id = pact.payroll_action_id
and pact.action_type = 'D';
select paa.assignment_action_id
from
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and exists (select ''
from pay_process_events ppe
where ppe.assignment_id = paa.assignment_id
and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
and ppe.creation_date > cp_last_cc_run_date
and ppe.effective_date <= nvl(ppa.date_earned,ppa.effective_date)
)
and not exists (select ''
from pay_assignment_actions paa1, -- Prepay/Costing
pay_action_interlocks pai1,
pay_assignment_actions paa2,-- Payment/Trans GL
pay_action_interlocks pai2
where pai1.locked_action_id = paa.assignment_action_id
and pai1.locking_action_id = paa1.assignment_action_id
and pai2.locked_action_id = paa1.assignment_action_id
and pai2.locking_action_id = paa2.assignment_action_id);
select paa.assignment_action_id
from
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and not exists (select ''
from pay_assignment_actions paa1, -- Payment/Trans GL
pay_action_interlocks pai1
where pai1.locked_action_id = paa.assignment_action_id
and pai1.locking_action_id = paa1.assignment_action_id)
and (exists (select ''
from pay_process_events ppe
where ppe.assignment_id = paa.assignment_id
and ppe.effective_date < ppa.effective_date
and ppe.change_type in ('PAYMENT')
and ppe.creation_date > cp_last_cc_run_date
)
or
exists (select ''
from pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2
where pai.locking_action_id = paa.assignment_action_id
and pai.locked_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in ('R','Q')
and exists (select ''
from pay_process_events ppe
where ppe.assignment_id = paa2.assignment_id
and ppe.effective_date < ppa2.effective_date
and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
and ppe.creation_date > cp_last_cc_run_date
)
)
);
select paa.assignment_action_id
from
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and not exists (select ''
from pay_assignment_actions paa1, -- Payment/Trans GL
pay_action_interlocks pai1
where pai1.locked_action_id = paa.assignment_action_id
and pai1.locking_action_id = paa1.assignment_action_id)
and exists (select ''
from pay_process_events ppe
where ppe.assignment_id = paa.assignment_id
and ppe.effective_date < ppa.effective_date
and ppe.change_type in ('COST_CENTRE')
and ppe.creation_date > cp_last_cc_run_date
)
and exists (select ''
from pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2
where pai.locking_action_id = paa.assignment_action_id
and pai.locked_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in ('R','Q')
and exists (select ''
from pay_process_events ppe
where ppe.assignment_id = paa2.assignment_id
and ppe.effective_date < ppa2.effective_date
and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
and ppe.creation_date > cp_last_cc_run_date
)
);
select substr(tab.column_name, 1, 24) column_name,
tab.column_name full_column_name,
tab.data_type
from dba_tab_columns tab, user_synonyms syn
where syn.synonym_name = p_tab_name
and tab.table_name = syn.table_name
and tab.owner = syn.table_owner
and tab.owner = g_pay_schema
and tab.column_name not in ('LAST_UPDATE_DATE',
'LAST_UPDATED_BY',
'LAST_UPDATE_LOGIN',
'CREATED_BY',
'CREATION_DATE',
'OBJECT_VERSION_NUMBER')
and tab.data_type in ('NUMBER', 'VARCHAR2', 'DATE')
order by column_name;
''''''select statement'''''',
''N'',
NULL
);
the same list. Similarly for the after_update and after_delete
(...rku_pkg, ...rkd_pkg)
Prerequisites: The DYT must have been created in the old-skool manner,
using the generate_upd_trigger in mode PROCEDURE then mode TRIGGER DATA
The former creates code that should be edited and then placed in
pay_cc_dyt_code_pkg.
*/
PROCEDURE GENERATE_DYT_PKG_BEHAVIOUR(p_table_name in varchar2,
p_tab_rki_pkg in varchar2,
p_tab_rku_pkg in varchar2,
p_tab_rkd_pkg in varchar2 ) IS
--Include all control parameters, under the assumption that the finished
--rows will be manually edited, usually removing some of these
cursor csr_args (cp_rki varchar2, cp_rku varchar2, cp_rkd varchar2)
is
SELECT a.argument value_name, a.procedure$ proc_name
FROM SYS.ARGUMENT$ A,
USER_OBJECTS B
WHERE A.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME in (CP_RKI,CP_RKU,CP_RKD)
AND A.LEVEL# = 0
--AND a.argument not in ('P_VALIDATE',
-- 'P_EFFECTIVE_DATE',
-- 'P_DATETRACK_UPDATE_MODE',
-- 'P_DATETRACK_DELETE_MODE',
-- 'P_VALIDATION_START_DATE',
-- 'P_VALIDATION_END_DATE',
-- 'P_LANGUAGE_CODE')
ORDER BY a.procedure$;
select pdt.dated_table_id
into l_dated_table_id
from pay_dated_tables pdt
where pdt.table_name = p_table_name;
update pay_dated_tables pdt
set application_id = l_app_id,
dyn_trigger_type = 'P',
dyn_trigger_package_name = l_pkg_name,
dyn_trig_pkg_generated = 'N'
where pdt.table_name = p_table_name;
update pay_dated_tables pdt
set dyn_trigger_type = 'T',
dyn_trigger_package_name = null,
dyn_trig_pkg_generated = null
where pdt.table_name = p_table_name;
DELETE
FROM pay_trigger_parameters ptp
WHERE ptp.usage_id = (select dated_table_id
from pay_dated_tables pdt
where pdt.table_name = p_table_name)
AND ptp.usage_type in ('PI','PU','PD');
select min(effective_date) effective_date,
change_type
from pay_process_events
where assignment_id = p_asg_id
and creation_date between p_cca_date
and p_sysdate
and change_type in ('DATE_PROCESSED', 'DATE_EARNED')
group by change_type
order by change_type desc;
select pdt.table_name,ppe.surrogate_key
from pay_process_events ppe,
pay_event_updates peu,
pay_dated_tables pdt
where ppe.assignment_id is null
and ppe.creation_date between p_cca_date
and p_sysdate
AND ppe.change_type in ('DATE_PROCESSED', 'DATE_EARNED')
and peu.event_update_id = ppe.event_update_id
and peu.dated_table_id = pdt.dated_table_id
and pdt.table_name in ('PAY_GRADE_RULES_F','PQH_RATE_MATRIX_RATES_F','FF_GLOBALS_F','PAY_USER_COLUMN_INSTANCES_F'); /*Added for Bug 8302596 */
l_update_cc_date boolean;
l_update_cc_date := TRUE;
and l_update_cc_date = TRUE) loop
if (l_change_type = 'DATE_PROCESSED') then
select count(*)
into run_counts
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_curr
where paa_curr.assignment_action_id = p_assact_id
and paa.assignment_id = p_asg_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.payroll_action_id <> paa_curr.payroll_action_id
and paa.action_sequence < paa_curr.action_sequence
and ppa.action_type in ('R', 'Q')
and ppa.effective_date >= l_effective_date; /*Bug 13855961 */
l_update_cc_date := FALSE;
select count(*)
into run_counts
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_actions paa_curr
where paa_curr.assignment_action_id = p_assact_id
and paa.assignment_id = p_asg_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.payroll_action_id <> paa_curr.payroll_action_id
and paa.action_sequence < paa_curr.action_sequence
and ppa.action_type in ('R', 'Q')
and ppa.date_earned >= l_effective_date; /* Bug 13855961 */
l_update_cc_date := FALSE;
IF (l_update_cc_date= TRUE) then
open get_group_events(l_cca_date,
p_sysdate);
and l_update_cc_date = TRUE) loop
l_grp_event_valid := pay_interpreter_pkg.valid_group_event_for_asg(l_table_name,
p_asg_id,
l_surrogate_key);
l_update_cc_date := FALSE;
if (l_update_cc_date = TRUE) then
new_cc_date :=p_sysdate;