[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
full_name select
mreid 115.13 28-JUN-2001 Bugfix 1855543 - rewrote
balance adjustment name select.
exjones 115.14 14-AUG-2001 Allow the g_server_validate thing
to switch off the v_name fetch
for performance in PAYWSACT
jtomkins 115.15 30-OCT-2001 Added function latest_balance_exists
for performance support of
pay_balances_v (1509490)
kkawol 115.16 02-NOV-2001 v_name procedure altered for purge.
Added get_purge_phase.
dsaxby 115.17 28-JAN-2002 Added dbdrv commands.
jbarker 115.18 06-SEP-2002 Added support for BEE status type in
v_name procedure.
alogue 115.20 06-JAN-2003 Performance fix to get_balance_adjustment
in v_name function. Bug 2653089.
mreid 115.21 24-FEB-2003 Bug 2802446 - corrected possible
invalid number in US archive
retrieval (added Hint)
SuSivasu 115.22 04-APR-2003 Fixed the issue in Bug 2802446, where by
using pay_core_utils.get_parameter to extract GRE info.
JBarker 115.23 11-JUN-2003 Added decode_cheque_type function
alogue 115.24 24-FEB-2003 Bug 3166075 - fix v_name procedure for
archiver.
tvankayl 115.25 29-DEC-2003 Bug 3261430 - v_name procedure
modified to return process names
for all archiver processes.
alogue 115.26 24-JUN-2004 Further Performance fixes to get_person_name
and get_balance_adjustment in v_name
function. Bug 3720619.
adkumar 115.27 30-JUL-2004 Bug No. 3665606. Batch Balance Adjustment process
should display -
becuase the process may have multiple assignment
actions.
tvankayl 115.28 29-AUG-2005 Bug 4584489. Support for Action Type 'CP'.
SuSivasu 115.29 21-OCT-2005 Added support for SERVER_VALIDATION in
get_char_bindvar.
alogue 115.30 04-JAN-2006 Performance Repository fix to get_archiver
cursor.
alogue 115.31 28-MAR-2007 Support for single latest balance table in
latest_balance_exists. Bug 5956216.
alogue 115.32 26-JUL-2007 Bug 6130796 - check within v_name procedure
pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE')
is same as action_type passed in.
mshingan 115.33 21-AUG-2007 Bug 6353676 - Translated element set name is used.
Cursor get_element_set_name is using pay_element_sets_tl
instead of pay_element_sets.
mshingan 115.34 21-AUG-2007 Bug 6353676 - changed declaration of variable l_eltset.
mshingan 115.35 22-AUG-2007 Bug 6353676 - Translated element set name functionality
is available only in r12 and not in 11i.Hence added
new cursor for R12.
ckesanap 115.36 08-Jul-2008 Bug 5892723 - Modified the v_name() procedure for
action_type 'V'. Assignment set is passed as Name for
batch reversal process.
*/
--
--
-- GLOBAL VARIABLES
--
g_business_group_id number ;
procedure update_row(p_rowid in varchar2,
p_action_status in varchar2 ) is
begin
--
update PAY_PAYROLL_ACTIONS
set ACTION_STATUS = p_action_status
where ROWID = p_rowid;
end update_row;
procedure delete_row(p_rowid in varchar2) is
--
begin
--
delete from PAY_PAYROLL_ACTIONS
where ROWID = p_rowid;
end delete_row;
cursor C is select *
from PAY_PAYROLL_ACTIONS
where rowid = p_rowid
for update of PAYROLL_ACTION_ID NOWAIT ;
select 1
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id
and action_status in ('E','M','U');
select status.meaning
from fnd_concurrent_requests r,
fnd_lookups status
where r.request_id = p_request_id
and r.status_code = status.lookup_code
and r.phase_code = 'C'
and status.lookup_type = 'CP_STATUS_CODE' ;
select 1
from pay_message_lines
where source_id = p_payroll_action_id
and source_type = 'P' ;
select consolidation_set_name
from pay_consolidation_sets
where consolidation_set_id = p_consolidation_set_id ;
select els.element_set_name
from pay_element_sets els
where els.element_set_id = p_element_set_id ;
select pes_tl.element_set_name
from pay_element_sets_tl pes_tl
where pes_tl.element_set_id = p_element_set_id
and pes_tl.language = USERENV('LANG');
select ast.assignment_set_name
from hr_assignment_sets ast
where ast.assignment_set_id = p_assignment_set_id;
select hr_general.decode_lookup('PURGE_PHASE', to_char(ppa.purge_phase))
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select /*+ INDEX
(aac PAY_ASSIGNMENT_ACTIONS_N50,
peo PER_PEOPLE_F_PK,
asg PER_ASSIGNMENTS_F_PK)
USE_NL(aac, peo, asg) */
peo.full_name
,pac.element_type_id --bug no. 3665606
,pac.legislative_parameters --bug no. 3665606
from pay_assignment_actions aac,
pay_payroll_actions pac,
per_all_people_f peo,
per_all_assignments_f asg
where pac.payroll_action_id = p_payroll_action_id
and aac.payroll_action_id = pac.payroll_action_id
and asg.assignment_id = aac.assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and peo.person_id = asg.person_id
and p_effective_date between peo.effective_start_date
and peo.effective_end_date ;
select /*+ ORDERED
INDEX(rrs PAY_RUN_RESULTS_N50)
USE_NL(rrs)*/
tl.element_name
from pay_payroll_actions pac,
pay_assignment_actions aac,
pay_run_results rrs,
pay_element_types_f ety,
pay_element_types_f_tl tl
where pac.payroll_action_id = p_payroll_action_id
and aac.payroll_action_id = pac.payroll_action_id
and aac.assignment_action_id = rrs.assignment_action_id
and rrs.element_type_id = ety.element_type_id
and ety.element_type_id = tl.element_type_id
and tl.language = USERENV('LANG')
and p_effective_date between ety.effective_start_date
and ety.effective_end_date;
select /*+ INDEX (pac PAY_PAYROLL_ACTIONS_PK) */
pus.state_name||'-'||pac.report_type||decode(hou.name,
NULL, NULL, '-'||hou.name)
from pay_us_states pus,
hr_organization_units hou,
pay_payroll_actions pac,
per_business_groups_perf bg
where pac.payroll_action_id = p_payroll_action_id
and pac.report_qualifier = pus.state_abbrev
and pac.report_category is not null
and bg.business_group_id = pac.business_group_id
and bg.legislation_code in ('US', 'CA')
and hou.organization_id(+) = pay_core_utils.get_parameter('TRANSFER_GRE',pac.legislative_parameters)
--
-- decode(instr(pac.legislative_parameters,
-- 'TRANSFER_GRE'),
-- 0, -1,
-- substr(pac.legislative_parameters,
-- instr(pac.legislative_parameters,
-- 'TRANSFER_GRE') + 13)
-- )
union
select /*+ INDEX (pac PAY_PAYROLL_ACTIONS_PK) */
'Federal-'||pac.report_type||decode(hou.name,
NULL, NULL, '-'||hou.name)
from hr_all_organization_units hou,
pay_payroll_actions pac
where pac.payroll_action_id = p_payroll_action_id
and pac.report_category is not null
and pac.report_qualifier = 'FED'
and hou.organization_id(+) = pay_core_utils.get_parameter('TRANSFER_GRE',pac.legislative_parameters);
select pud.name
from pay_upgrade_definitions_vl pud,
pay_payroll_actions pac
where pac.payroll_action_id = p_payroll_action_id
and pud.short_name = pay_core_utils.get_parameter('UPG_DEF_NAME',pac.legislative_parameters);
select pac.report_type
from pay_payroll_actions pac
where pac.payroll_action_id = p_payroll_action_id;
select rfmtl.display_name
from pay_payroll_actions pac,
pay_report_format_mappings_f rfm,
pay_report_format_mappings_tl rfmtl
where pac.payroll_action_id = p_payroll_action_id
and pac.report_type = rfm.report_type
and pac.report_qualifier = rfm.report_qualifier
and pac.report_category = rfm.report_category
and p_effective_date between rfm.effective_start_date and rfm.effective_end_date
and rfm.report_format_mapping_id = rfmtl.report_format_mapping_id
and rfmtl.language = USERENV('LANG');
select fnd_date.date_to_canonical(pacc.effective_date)||'-'||to_char(pacv.start_cheque_number)
||'-'||to_char(pacv.end_cheque_number)
from pay_payroll_actions pacc,
pay_payroll_actions pacv
where pacv.payroll_action_id = p_payroll_action_id
and pacc.payroll_action_id = pacv.target_payroll_action_id ;
select pbh.batch_name
from pay_batch_headers pbh,
pay_payroll_actions ppa
where ppa.batch_id = pbh.batch_id
and ppa.payroll_action_id = p_payroll_action_id;
select 'Y'
from pay_assignment_latest_balances
where assignment_action_id = p_assignment_action_id
and defined_balance_id = p_defined_balance_id;
select 'Y'
from pay_person_latest_balances
where assignment_action_id = p_assignment_action_id
and defined_balance_id = p_defined_balance_id;
select 'Y'
from pay_latest_balances
where assignment_action_id = p_assignment_action_id
and defined_balance_id = p_defined_balance_id;
select pli.validation_name
from pay_legislative_field_info pli,
per_business_groups pbg
where pli.legislation_code = pbg.legislation_code
and pbg.business_group_id = p_bus_grp_id
and pli.rule_type = 'H'
and pli.field_name = 'CHEQUE_CHECK';