[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** selected run or prepayments.
**
** 11 JUN 2001 SHOSKATT Bug : 1817816
** get_tax_code function changed to retrieve Special
** Tax Code along with Tax Code. Also the function
** changed to check for date conditions
** 10 JAN 2002 SRRAJAGO 2177800 Included Action_type 'C' for Costing process
** 10 OCT 2002 PUCHIL 2595888 Changed the type of variable l_action_type
** from varchar2(1) to pay_payroll_actions.action_type%type
** 19 NOV 2002 SRRAJAGO 2636739 Removed the action_type 'C' only from cursor asg_latest_pay of the procedure get_asg_latest_pay
** 03 DEC 2002 SRRAJAGO 2689221 Included 'nocopy' option for the 'out' and 'in out' parameters of all the procedures.
** 17 NOV 2003 PUCHIL 3257888 Added language check to cursors c_get_work_address and c_get_home_address.
** 08 APR 2004 PUCHIL 3453503 Added logic to support Advanced Retropay.
** 04 JUN 2007 DDUVVURI 6083911 Removed the condition "legislation_code is null" in the cursor in procedure run_and_ytd_balances
*/
--
-- get_tax_code
--
function get_tax_code (p_run_assignment_action_id number) return varchar2 is
l_tax_code pay_run_result_values.result_value%type;
select rrv.result_value,rrv1.result_value
from pay_element_types_f et
, pay_input_values_f iv
, pay_run_result_values rrv
, pay_run_results rr
, pay_input_values_f iv1
, pay_run_result_values rrv1
, pay_payroll_actions ppa
, pay_assignment_actions paa
where et.element_name = 'PAYE Information'
and et.legislation_code = 'NZ'
and iv.element_type_id = et.element_type_id
and iv.name = 'Tax Code'
and rr.element_type_id = et.element_type_id
and rr.assignment_action_id = p_assignment_action_id
and rrv.run_result_id = rr.run_result_id
and rrv.input_value_id = iv.input_value_id
and iv1.element_type_id = et.element_type_id
and iv1.name = 'Special Tax Code'
and rrv1.run_result_id = rr.run_result_id
and rrv1.input_value_id = iv1.input_value_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = rr.assignment_action_id
and ppa.effective_date between et.effective_start_date and et.effective_end_date
and ppa.effective_date between iv.effective_start_date and iv.effective_end_date
and ppa.effective_date between iv1.effective_start_date and iv1.effective_end_date;
select substr(pad.address_line1,1,27),
substr(pad.address_line2,1,27),
substr(pad.address_line3,1,27),
substr(pad.town_or_city,1,27),
pad.postal_code,
substr(ftt.territory_short_name,1,27)
from per_addresses pad,
fnd_territories_tl ftt
where pad.country = ftt.territory_code
and ftt.language = USERENV('LANG') -- Bug 3257888
and pad.person_id = cp_person_id
and sysdate between nvl(pad.date_from, sysdate) and nvl(pad.date_to, sysdate);
select substr(hrl.address_line_1,1,27),
substr(hrl.address_line_2,1,27),
substr(hrl.address_line_3,1,27),
substr(hrl.town_or_city,1,27),
hrl.postal_code,
substr(ftt.territory_short_name,1,27)
from hr_locations hrl,
fnd_territories_tl ftt
where hrl.country = ftt.territory_code
and ftt.language = USERENV('LANG') -- Bug 3257888
and hrl.location_id = cp_location_id;
select eev.screen_entry_value
from pay_element_entry_values_f eev,
per_pay_bases ppb,
pay_element_entries_f pe
where ppb.pay_basis_id +0 = p_pay_basis_id
and pe.assignment_id = p_assignment_id
and eev.input_value_id = ppb.input_value_id
and eev.element_entry_id = pe.element_entry_id
and eev.input_value_id = ppb.input_value_id
and eev.element_entry_id = pe.element_entry_id
and p_effECtive_date between
eev.effective_start_date and eev.effective_end_date
and p_EFfective_date between
pe.effective_start_date and pe.effective_end_date;
select fcu.currency_code
from hr_organization_information hoi,
hr_organization_units hou,
fnd_currencies fcu
where hou.business_group_id = c_business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'Business Group Information'
and fcu.issuing_territory_code = hoi.org_information9;
select nvl(hr_nzbal.calc_asg_run(c_assignment_action_id,
balance_type_id,
c_effective_date,
c_assignment_id),0),
nvl(hr_nzbal.calc_asg_ytd(c_assignment_action_id,
balance_type_id,
c_effective_date,
c_assignment_id),0)
from pay_balance_types
where balance_name = c_balance_name
-- Modified the condition for bug 6083911
and legislation_code = 'NZ';
select
rppa.date_earned,
rpaa.payroll_action_id,
rpaa.assignment_action_id,
paa.assignment_action_id,
ptp.time_period_id,
ptp.period_name,
rppa.payroll_id,
nvl(rppa.pay_advice_date,ptp.pay_advice_date),
rppa.pay_advice_message
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions rpaa,
per_time_periods ptp,
pay_payroll_actions rppa
where paa.payroll_action_id = ppa.payroll_action_id
and rppa.payroll_action_id = rpaa.payroll_action_id
and rppa.time_period_id = ptp.time_period_id
and paa.assignment_action_id =
(select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
from pay_payroll_actions pa,
pay_assignment_actions aa
where pa.action_type in ('U','P')
and aa.action_status = 'C'
and pa.payroll_action_id = aa.payroll_action_id
and aa.assignment_id = p_assignment_id
and pa.effective_date <= p_session_date)
and ppa.action_type in ('P', 'U')
and rpaa.assignment_id = p_assignment_id
and rpaa.action_sequence =
(select max(aa.action_sequence)
from pay_assignment_actions aa,
pay_action_interlocks loc
where loc.locked_action_id = aa.assignment_action_id
and loc.locking_action_id = paa.assignment_action_id);
select pact.action_type , assact.assignment_id
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_assignment_action_id
and pact.payroll_action_id =
assact.payroll_action_id
;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_action_interlocks loc
where loc.locking_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locked_action_id
order by assact.action_sequence desc ;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_payroll_actions pact,
pay_action_interlocks loc
where loc.locked_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locking_action_id
and pact.payroll_action_id = assact.payroll_action_id
and pact.action_type in ('P','U','C') -- Bug No : 2177800
-- prepayments only
order by assact.action_sequence desc
;
select pact.payroll_id,
pact.payroll_action_id,
pact.date_earned,
pact.time_period_id,
ptp.period_name,
nvl(pact.pay_advice_date,ptp.pay_advice_date),
pay_advice_message
from pay_assignment_actions assact,
pay_payroll_actions pact,
per_time_periods ptp
where assact.assignment_action_id = p_run_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id
and pact.time_period_id = ptp.time_period_id ;
select LOCKED_ACTION_ID
from pay_action_interlocks
where LOCKING_ACTION_ID = p_assignment_action_id
order by locked_action_id asc;