[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
get_term_info. This will select 'Y' for
all pay periods upto actual termination
date.
27-MAY-2004 rsethupa 115.35 3487250 Changed cursor c_currency_code to
fetch by hou.organization_id instead
of hou.business_group_id
28-MAR-2004 ahanda 115.34 Changed check_emp_personal_payment
to check if archiver is locking
prepay or run action.
07-JAN-2004 kaverma 115.33 3350023 Modified cursor c_hourly_salary to remove
MERGE JOIN CARTESIAN
22-DEC-2003 ahanda 115.32 3331020 Changed cursor c_check_for_reversal.
06-Nov-2003 pganguly 115.31 Changed the procedure get_term_info
so that it caches the legislation
_code, legislation_rule.
18-Sep-2003 sdahiya 115.30 2976050 Modified the
check_emp_personal_payment procedure
so that it calls
get_payment_status_code
instead of get_payment_status.
02-Sep-2003 meshah 115.29 3124483 using actual_termination_date instead
of final_prcess_date.
02-Sep-2003 meshah 115.28 3124483 the cursor get_person_info in
get_doc_eit function has been changed.
Now joining to per_periods_of_service
to find out if the employee is
terminated.
19-JUL-2003 ahanda 115.27 Added function format_to_date.
23-May-03 ekim 115.26 2897743 Added c_get_lookup_for_paid.
30-APR-03 asasthan 115.25 2925411 Added to_char in c_check_number
cursor
07-Feb-03 ekim 115.24 2716253 Performance fix on c_regular_salary.
23-JAN-2002 ahanda 115.23 2764088 Changed cursor get_bg_eit in
function get_doc_eit for performance.
15-NOV-2002 ahanda 115.22 Modified function get_jurisdiction_name
Changed c_get_state to return
state_abbrev.
14-NOV-2002 tclewis 115.21 Modified the order of parameters
on the get_check_number function
now pass pp_ass_act , pre_pay_id.
21-OCT-2002 tclewis 115.19 changed get_check_no, to return a
deposit advice number. Either,
pre-payment assignment action id
for Master payment or Run AAID for
the sep payment AAID.
09-OCT-2002 ahanda 115.18 2474524 Changed check_emp_personal_payment
15-AUG-2002 ahanda 115.17 Changed get_proposed_emp_salary for
performance.
18-JUL-2002 ahanda 115.16 Changed the get_jurisdiction_name
function to return NULL is not a US
jurisdiction.
16-JUN-2002 sodhingr 115.15 Added a new function get_term_info
to check
the terminated employee based
on the legislation_field_info
13-MAY-2002 pganguly 115.13 2363857 Added a new function
get_legislation_code.
01-MAY-2002 ahanda 115.12 2352332 Changed get_check_number to check
for Void.
23-MAR-2002 ahanda 115.11 Fixed compilation errors
22-MAR-2002 ekim 115.10 Removed trace_on.
21-MAR-2002 ekim 115.9 Changed get_doc_eit function.
15-FEB-2002 ahanda 115.7 2229092 Changed get_check_number to check for
External Manual Payments.
24_JAN-2002 dgarg 115.6 Added get_jurisdiction_name
function.
05-OCT-2001 ekim 115.5 Added get_doc_eit function.
21-SEP-2001 ekim 115.4 Added get_format_value function.
17-SEP-2001 assathan 115.3 Added get_check_number for payslip
09-FEB-2001 ahanda 115.2 Changed the procedure
check_emp_personal_payment for
performance.
14-DEC-2000 ahanda 115.1 1343941/ Changed the procedure
1494453 check_emp_personal_payment to go of pre
payments instead of personal payment
methods. This will also fix issue of
Payslip not printing Zero net.
10-FEB-2000 ahanda 115.0 Changed proposed_salary to
proposed_salary_n for function
get_proposed_emp_salary.
****************************************************************************
01-FEB-2000 ahanda 110.3 Changed function to get School Dst
Name from city if it is not there
in county dsts table.
01-FEB-2000 ahanda 110.2 Added function to get School Dst Name.
24-DEC-1999 ahanda 110.1 1117470 Changed get_proposed_emp_salary to get
1116604 proposed salary effective on period end
date. Changed the check_for_paid cursor
to check for if checkwriter has been
locked for of Void Pymt and Run in
case of Reversal.
01-JUL-1999 ahanda 110.0 Created.
****************************************************************************/
AS
gv_package VARCHAR2(100);
select payslip_view_date
from per_time_periods ptp
where time_period_id = cp_time_period_id;
select ppp.pre_payment_id
from pay_payment_types ppt,
pay_org_payment_methods_f popm,
pay_pre_payments ppp
where ppp.assignment_action_id = cp_assignment_action_id
and popm.org_payment_method_id = ppp.org_payment_method_id
and popm.defined_balance_id is not null
and ppt.payment_type_id = popm.payment_type_id
and ppt.category = cp_payment_category
and ppt.territory_code = cp_legislation_code;
select 1
from pay_action_interlocks pai_pre
where pai_pre.locking_action_id = cp_assignment_action_id
and exists (
select 1
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai_run
/* Get the run assignment action id locked by pre-payment */
where pai_run.locked_action_id = pai_pre.locked_action_id
/* Check if the Run is being locked by Reversal */
and pai_run.locking_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and ppa.action_type = 'V');
select paa.assignment_action_id
from pay_action_interlocks paci,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paci.locking_action_id = cp_assignment_action_id
and paa.assignment_action_id = paci.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P', 'U');
select paa_pre.assignment_action_id
from pay_action_interlocks pai_run,
pay_action_interlocks pai_pre,
pay_assignment_actions paa_pre,
pay_payroll_actions ppa_pre
where pai_run.locking_action_id = cp_assignment_action_id
and pai_pre.locked_action_id = pai_run.locked_action_id
and paa_pre.assignment_Action_id = pai_pre.locking_action_id
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and ppa_pre.action_type in ('P', 'U');
select nvl(max(ppa.date_earned), max(ppa.effective_date))
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pai
where ppa.payroll_action_id = paa.payroll_action_id
and pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = cp_assignment_action_id
and ppa.action_type in ('R', 'Q', 'B', 'V');
select ptp.time_period_id
from per_time_periods ptp
where cp_date_earned between ptp.start_date
and ptp.end_Date
and ptp.payroll_id = cp_payroll_id;
select 1
from dual
where not exists
(select 1
from pay_pre_payments ppp
where ppp.assignment_action_id = cp_prepayment_action_id
);
select payslip_view_date
from per_time_periods ptp
where time_period_id = cp_time_period_id;
select ppp.pre_payment_id
from pay_payment_types ppt,
pay_org_payment_methods_f popm,
pay_pre_payments ppp
where ppp.assignment_action_id = cp_assignment_action_id
and popm.org_payment_method_id = ppp.org_payment_method_id
and popm.defined_balance_id is not null
and ppt.payment_type_id = popm.payment_type_id;
select 1
from pay_action_interlocks pai_pre
where pai_pre.locking_action_id = cp_assignment_action_id
and exists (
select 1
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai_run
where pai_run.locked_action_id = pai_pre.locked_action_id
and pai_run.locking_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and ppa.action_type = 'V');
select 1
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai_run
where pai_run.locked_action_id = cp_assignment_action_id
and pai_run.locking_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and ppa.action_type = 'V';
select paa.assignment_action_id
from pay_action_interlocks paci,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paci.locking_action_id = cp_assignment_action_id
and paa.assignment_action_id = paci.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P', 'U');
select paa_pre.assignment_action_id
from pay_action_interlocks pai_run,
pay_action_interlocks pai_pre,
pay_assignment_actions paa_pre,
pay_payroll_actions ppa_pre
where pai_run.locking_action_id = cp_assignment_action_id
and pai_pre.locked_action_id = pai_run.locked_action_id
and paa_pre.assignment_Action_id = pai_pre.locking_action_id
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and ppa_pre.action_type in ('P', 'U');
select nvl(max(ppa.date_earned), max(ppa.effective_date))
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pai
where ppa.payroll_action_id = paa.payroll_action_id
and pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = cp_assignment_action_id
and ppa.action_type in ('R', 'Q', 'B', 'V');
select ptp.time_period_id
from per_time_periods ptp
where cp_date_earned between ptp.start_date
and ptp.end_Date
and ptp.payroll_id = cp_payroll_id;
select to_number(substr(SERIAL_NUMBER,3)) sprt_pymnt_actn_id,
substr(SERIAL_NUMBER,2,1) sprt_pymnt_flag
from pay_assignment_actions where assignment_action_id=p_assignment_action;
select locked_action_id from
pay_action_interlocks pai ,pay_assignment_actions paa
where pai.locking_action_id = p_action_id
and pai.locked_action_id not in (
select to_number(substr(SERIAL_NUMBER,3)) from pay_assignment_actions
where payroll_action_id in (select distinct payroll_action_id
from pay_assignment_actions where assignment_action_id = xfr_action_id)
and substr(SERIAL_NUMBER,2,1) = 'Y') and
pai.locked_action_id=paa.assignment_action_id
and source_action_id is not null;
select nvl(max(ppa.date_earned), max(ppa.effective_date))
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pai
where ppa.payroll_action_id = paa.payroll_action_id
and pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = cp_assignment_action_id
and ppa.action_type in ('R', 'Q', 'B', 'V');
select ptp.time_period_id
from per_time_periods ptp
where cp_date_earned between ptp.start_date
and ptp.end_Date
and ptp.payroll_id = cp_payroll_id;
select ppp.proposed_salary_n
from per_pay_proposals ppp
where ppp.assignment_id = cp_assignment_id
and ppp.change_date =
(select max(change_date)
from per_pay_proposals ppp1
where ppp1.assignment_id = cp_assignment_id
and ppp1.approved = 'Y'
and ppp1.change_date <= cp_period_end_date);
select piv.element_type_id, piv.input_value_id
from pay_input_values_f piv,
per_pay_bases ppb
where ppb.pay_basis_id = cp_pay_basis_id
and ppb.input_value_id = piv.input_value_id
and cp_period_to_date between piv.effective_start_date
and piv.effective_end_date;
select prrv.result_value
from pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_payroll_actions ppa
where prr.element_type_id = piv.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.source_type = 'E'
and piv.input_value_id = prrv.input_value_id
and piv.input_value_id = cp_input_value_id
and ppa.effective_date between piv.effective_start_date
and piv.effective_end_date
and paa.assignment_action_id = prr.assignment_action_id
and paa.assignment_id = cp_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_period_to_date;
select prrv.result_value
from pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_payroll_actions ppa
where prr.element_type_id = piv.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.source_type = 'E'
and piv.input_value_id = prrv.input_value_id
and piv.element_type_id = cp_element_type_id
and piv.name = cp_input_value_name
and ppa.effective_date between piv.effective_start_date --Bug 3350023
and piv.effective_end_date
and paa.assignment_action_id = prr.assignment_action_id
and paa.assignment_id = cp_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_period_to_date;
select ppb.pay_annualization_factor
from per_pay_bases ppb
where ppb.pay_basis_id = cp_pay_basis_id;
select ptpt.number_per_fiscal_year
from per_time_period_types ptpt
where ptpt.period_type = cp_period_type;
select paf.normal_hours,
decode(paf.frequency,'Y', 1,
'M', 12,
'W', 52,
'D', 365, 1)
from per_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_period_end_date between paf.effective_start_date
and paf.effective_end_date;
select pos.working_hours,
decode(pos.frequency, 'Y', 1,
'M', 12,
'W', 52,
'D', 365, 1)
from per_positions pos,
per_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_period_end_date between paf.effective_start_date
and paf.effective_end_date
and paf.position_id = pos.position_id;
select pou.working_hours,
decode(pou.frequency, 'Y', 1,
'M', 12,
'W', 52,
'D', 365, 1)
from per_organization_units pou,
per_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_period_end_date between paf.effective_start_date
and paf.effective_end_date
and paf.organization_id = pou.organization_id;
select pbg.working_hours,
decode(pbg.frequency, 'Y', 1,
'M', 12,
'W', 52,
'D', 365, 1)
from per_business_groups pbg,
per_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_period_end_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id;
select initcap(pcisd.school_dst_name)
from pay_us_city_school_dsts pcisd
where pcisd.state_code = substr(cp_jurisdiction_code,1,2)
and pcisd.school_dst_code = substr(cp_jurisdiction_code,4);
select initcap(pcosd.school_dst_name)
from pay_us_county_school_dsts pcosd
where pcosd.state_code = substr(cp_jurisdiction_code,1,2)
and pcosd.school_dst_code = substr(cp_jurisdiction_code,4);
select decode(ppa_pymt.action_type,
'M', to_char(NVL(ppp.source_action_id,cp_pre_payment_action)),
paa_pymt.serial_number)
from pay_pre_payments ppp,
pay_assignment_actions paa_pymt,
pay_payroll_actions ppa_pymt,
pay_action_interlocks pai
where pai.locked_action_id = cp_pre_payment_action
and paa_pymt.assignment_action_id = pai.locking_action_id
and ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
and ppa_pymt.action_type in ('M','H', 'E')
and paa_pymt.pre_payment_id = cp_pre_payment_id
and ppp.pre_payment_id = paa_pymt.pre_payment_id
and not exists (
select 1
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai_void
/* Assignment Action of Payment Type - NACHA/Check */
where pai_void.locked_action_id = paa_pymt.assignment_action_id --Void
/* Check if the locking is that of Void Pymt */
and pai_void.locking_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and ppa.action_status = 'C'
and ppa.action_type = 'D');
select hoi.org_information10
from hr_organization_units hou,
hr_organization_information hoi
where hou.organization_id = p_business_group_id /* Bug 3487250 */
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'Business Group Information';
select pei_information2, pei_information3
from per_people_extra_info
where information_type = 'HR_SELF_SERVICE_PER_PREFERENCE'
and person_id = l_person_id
and pei_information1 = upper(p_doc_type);
select lei_information2, lei_information3
from hr_location_extra_info
where information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
and location_id = l_location_id
and lei_information1 = upper(p_doc_type);
select org_information2,org_information3
from hr_organization_information
where org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
and org_information1 = upper(p_doc_type)
and organization_id = l_organization_id;
select org_information2, org_information3
from hr_organization_information hoi
where hoi.organization_id = l_business_group_id
and hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
and hoi.org_information1 = upper(p_doc_type) ;
select paf.business_group_id, paf.organization_id,
paf.location_id, paf.person_id
from per_assignments_f paf, per_periods_of_service pps
where paf.assignment_id = l_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and pps.period_of_service_id = paf.period_of_service_id
and pps.actual_termination_date is null;
select state_abbrev
from pay_us_states
where state_code = cp_state_code;
select county_name
from pay_us_counties
where state_code = cp_state_code
and county_code = cp_county_code;
select city_name
from pay_us_city_names
where state_code = cp_state_code
and county_code = cp_county_code
and city_code = cp_city_code
and primary_flag = 'Y';
select
org_information9
from
hr_organization_information
where
org_information_context = 'Business Group Information'
and organization_id = p_business_group_id;
select rule_mode
from pay_legislative_field_info plf
WHERE validation_name = 'ITEM_PROPERTY'
and rule_type = 'PAYSLIP_STOP_TERM_EMP'
and field_name = 'CHOOSE_PAYSLIP'
and legislation_code = p_legislation_code;
select actual_termination_date, pai.action_information16
from per_periods_of_service pps,
pay_action_information pai
where pps.person_id = p_person_id
and pai.action_context_id = p_action_context_id
and pai.action_information_category = 'EMPLOYEE DETAILS'
/* and fnd_date.canonical_to_date(pai.action_information11) = pps.date_start;*/
select 'Y'
from per_periods_of_service pps
where person_id = p_person_id
and decode(actual_termination_date,NULL,date_start,p_effective_start_date)
between date_start
and nvl(actual_termination_date,p_effective_end_date) ;
select 'N' from per_time_periods ptp
where ptp.time_period_id = p_time_period_id
and ( p_actual_termination_date between ptp.start_date
and ptp.end_date
or
p_actual_termination_date < ptp.start_date);
SELECT hr_general_utilities.Get_lookup_Meaning(p_lookup_type,p_lookup_code)
FROM DUAL;
select hoi.org_information9
from hr_organization_information hoi
where hoi.organization_id = p_business_grp_id
and hoi.org_information_context = 'Business Group Information';
select '1'
from pay_payment_types ppt
,pay_org_payment_methods_f popm
where popm.org_payment_method_id = p_org_pay_meth_id
and popm.payment_type_id = ppt.payment_type_id
and ppt.territory_code = p_legislation_code
and ppt.category = 'CH';