[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
07-DEC-2012 abellur 115.67 15949522 Updated add_custom_xml procedure,
modified the cursor get_third_party_check_info
set the category to 'US THIRD PARTY CHECKS'.
05-DEC-2012 schowl 115.66 15952728 Added a condition in procedure
get_custom_xml_routine to check profile option
value to disable/enable custom xml code.
01-Jun-2012 vvijayku 115.65 13969852 Modified the Lookup Codes for which the
custom xml code name will be retrieved.
17-Apr-2012 vvijayku 115.64 13969852 Added a new procedure get_custom_xml_routine
which will fetch the custom xml code name
defined by the user as the description for
the respective lookup_codes under the
lookup type PAY_CUSTOM_XML_CODE
27-Mar-2012 abellur 115.63 13902377 Updated add_custom_xml to populate
transit code required for Third Party
23-Feb-2012 abellur 115.61 13029999 Updated add_custom_xml to add XML
for third party cheques. Also added
the cursor get_third_party_check_info.
30-DEC-2011 pkoduri 115.58 13529461 Added the cursor get_employer_name_phone
to fetch employer name and corresponding
phone number for PDF payslip.
22-06-2011 nkjaladi 115.57 12549430 Modified the cursor get_depoadvice_deatils
in procedure add_custom_xml to use
Personal Payment method id
(action_information2) instead of
Organization payment method
id(action_information1) for value in
XML tag PAYMETHOD_ID.
29-07-2010 lbodired 115.56 9541448 Added two new xml tags for the deposit
advice details in the API add_custom_xml
19-Mar-2010 sjawid 115.53 9488426 Reverted back the changes made for Third party
payments of bug 9382065 as this issue is now
handling in pay_xml_extract_pkg.
03-Mar-2010 sjawid 115.51 9439388 Added cursor get_net_pay_dstr_details at add
_custom_xml procedure to get
employee net pay distribution details for US PDF
payslip.
24-Feb-2010 sjawid 115.50 9382065 Added cursor get_tp_check_num at add
_custom_xml procedure to get
Third party check number for US PDF
payslip.
16-Feb-2010 sjawid 115.48 9382065 Modified add_custom_xml procedure for
US pdf payslip enhancement.
17-Apr-2009 sudedas 115.47 8414024 Added IN OUT parameter to function
work_schedule_total_hours
16-Mar-2009 sudedas 115.46 7660565 get_payslip_sort_order2 modified,
Added ORGANIZATION_NAME.
115.45 7583387 Added NOCOPY hint for OUT variable.
21-Jan-2009 sudedas 115.44 7583387 Changed Function DAxml_range_cursor
to Procedure.
15-Jan-2009 sudedas 115.43 7583387 Added 3 functions for DA(XML) -
get_payslip_sort_order1
get_payslip_sort_order2
get_payslip_sort_order3
Added payslip_range_cursor.
28-Aug-2008 sudedas 115.42 7269477 Modified cursor get_depoadvice_deatils
and get_check_depoad_details.
Added effective_date Join Condition.
5-Sep-2007 sausingh 115.41 6392875 Modified the cursor get_check_num_for_depad
31-JUL-2007 sausingh 115.40 5635335 Added cursor to capture check details if
deposit advice is run after check-writer for an
assignment.
05-JUL-2007 sausingh 115.39 5635335 Added cursors
get_check_depoad_details
get_preassact_id
get_depoadvice_deatils
To get Employer's account detail and
deposit advice number in the XML
26-JUN-2007 sausingh 115.38 5635335 Added Tag to get the Check
Amount in check writer.
26-Jun-2007 sudedas 115.36 5635335 Modified add_custom_xml to print
Check Number and Amount. Added
procedure get_token_names.
24-May-2007 sausingh 115.33 5635335 Added procedure add_custom_xml
and some functions to display
Net Pay Amount in Words in Archive
Check Writer/Deposit Advice.
13-MAR-2007 kvsankar 115.33 FLSA For some scenarios, the function
get_time_def_for_entry and
get_time_def_for_entry_func was
associating custom Time Definitions
with seeded 'Regular Salary' and
'Regular Wages'.
Also the caching logic was
modified so that it does not return
seeded Time Definition(if FLSA Time
Definitionis is specified) for other
elements if 'Regular Salary' or
'Regular Wages' happens to be the
first element to be processed.
18-FEB-2007 kvsankar 115.32 5876883 Modified element_template_pre_process
5696187 and element_template_post_process
for the new template names
'US FLSA '
and
'US '
20-OCT-2006 asasthan 115.31 5610376 Regular Salary and Regular
Wages should not be associated
with FLSA Time Definitions as
these elements should not be
allocated.Code has been modified
to ensure that these seeded
elements do not inherit the time def
set at Payroll. Modified caching
so that seeded elements do not
blindly inherit time def set
by reduce regular element.
18-APR-2006 saikrish 115.30 5161974 Creating Commission balance feeds.
13-APR-2006 ahanda 115.29 Added a formula result rule to the
seeded Hours by Rate element
TEMPLATE_EARNING -> Pay Value
20-SEP-2005 rdhingra 115.28 FLSA2 Priority for Reduce Regular has to
be set to 1526. Updating
element_template_upd_user_stru
15-SEP-2005 rdhingra 115.27 FLSA2 Changed reporting name of FLSA Adjust
FROM: Retro
TO: Adjustment
15-SEP-2005 rdhingra 115.26 FLSA2 Added an AND clause in
CURSOR: get_payroll_time_definition_id
FUNCTION: get_time_def_for_entry_func
to take time_definition id as of
payroll_period end date
02-SEP-2005 asasthan 115.25 FLSA2 Attached Proration Event to
FLSA Period Adjustment Element
31-AUG-2005 rdhingra 115.24 FLSA2 Changes for FLSA Phase 2
Premium Adjutment
11-AUG-2005 kvsankar 115.23 FLSA2 Created a new function
get_time_def_for_entry_func
which is called by the procedure
get_time_def_for_entry
10-AUG-2005 rdhingra 115.22 FLSA2 Exclusion rule added for Overtime
and Premium categories.
4542621 element_information_category updated
from US_IMPUTED_EARNINGS
to US_IMPUTED EARNINGS
08-AUG-2005 rdhingra 115.21 FLSA2 Added default retro component
for "Entry Changes"
02-AUG-2005 rdhingra 115.20 FLSA2 Added retro group "Entry Changes"
to all FLSA Calc elements in Post
Process
27-JUL-2005 rdhingra 115.19 FLSA2 Modified element_template_pre_process
to remove the exclusion rules for
HXR when calculation_rule = 'US Earnings'
Added details pertaining to Augments
Added procedures delete_pre_process
and delete_post_process
09-JUN-2005 kvsankar 115.18 4420211 Modified the
element_template_post_process
to set the Mandatory Flag for
'Deduction Processing' to 'N'
for 'Non-payroll Payments'
24-MAY-2005 kvsankar 115.17 FLSA Modified the
element_template_upd_user_stru
to set the Processing priority
depending on whether Reduce
Regular checkbox is checked
or not.
23-MAY-2005 asasthn 115.15 FLSA Modified defaulting of JOB CODE
23-MAY-2005 rdhingra 115.14 FLSA Modified get_time_def_for_entry
Problem in cursor call.
21-MAY-2005 rdhingra 115.13 FLSA Added code to get the default
time_definition_id in procedure
get_time_def_for_entry
05-MAY-2005 kvsankar 115.12 FLSA Modified the
element_template_post_process to set
the Time Definition Type of
Base element to 'G' if the element
has FLSA Earnings checked
05-MAY-2005 kvsankar 115.11 FLSA is created using US FLSA template
04-MAY-2005 ahanda 115.10 FLSA Modified get_time_def_for_entry
29-APR-2005 kvsankar 115.9 FLSA Modified the
element_template_post_process to set
the Time Definition Type of only the
Base element to 'G' if the element
is created using US FLSA template
29-APR-2005 rdhingra 115.8 FLSA Added Procedure call for
get_time_def_for_entry
28-APR-2005 sodhingr 115.7 Added the function work_schedule
_total_hours used by new work
schedule functionality
27-APR-2005 kvsankar 115.6 FLSA Modified the Element Template PRE
Process to not to create Special
Inputs element, if 'FLSA Hours' or
'Overtime Base' checkboxes are
checked. This check is only for
'US FLSA' template.
27-APR-2005 kvsankar 115.5 FLSA Modified the Element Template PRE,
UPDATE and the POST Process for
incluing the new template created
for FLSA
17-APR-2005 rdhingra 115.3 Changed for Global Element
Template Migration. Added defi-
nitions for user exit calls
Pre-Process, upd_user_stru and
Post-Process made from Global
Element Template. Also added
definition of get_obj_id
function.
23-AUG-2004 kvsankar 115.2 3840248 Modified the IF condition to
correctly set END IF
12-MAY-2004 sdahiya 115.1 Modified phase to plb
25-APR-2004 sdahiya 115.0 3622290 Created.
****************************************************************************/
/****************************************************************************
Name : GET_DEFAULT_JUSRIDICTION
Description : This function returns the default jurisdiction code which is
used for involuntary deduction elements if the end user does
not specify jurisdiction input value.
*****************************************************************************/
PROCEDURE get_default_jurisdiction(p_asg_act_id number,
p_ee_id number,
p_jurisdiction in out nocopy varchar2) IS
-- Cursor to get classification of elements.
cursor csr_ele_classification is
select classification_name
from pay_element_classifications pec,
pay_element_types_f pet,
pay_element_entries_f pee
where pec.classification_id = pet.classification_id
and pet.element_type_id = pee.element_type_id
and pee.element_entry_id = p_ee_id;
select assign.work_at_home
from per_all_assignments_f assign,
pay_assignment_actions paa
where paa.assignment_id = assign.assignment_id
and paa.assignment_action_id = p_asg_act_id
and assign.effective_start_date = (select max(paf.effective_start_date)
from per_all_assignments_f paf
where paf.assignment_id = assign.assignment_id);
select nvl(addr.add_information17,addr.region_2),
nvl(addr.add_information19,addr.region_1),
nvl(addr.add_information18,addr.town_or_city),
nvl(addr.add_information20,addr.postal_code)
from per_addresses addr,
per_all_assignments_f assign,
pay_assignment_actions paa
where paa.assignment_id = assign.assignment_id
and paa.assignment_action_id = p_asg_act_id
and addr.person_id = assign.person_id
and addr.primary_flag = 'Y'
and assign.effective_start_date
between nvl(addr.date_from, assign.effective_start_date)
and nvl(addr.date_to,assign.effective_start_date)
and assign.effective_start_date = (select max(paf.effective_start_date)
from per_all_assignments_f paf
where paf.assignment_id = assign.assignment_id);
select nvl(hrloc.loc_information17,hrloc.region_2),
nvl(hrloc.loc_information19,hrloc.region_1),
nvl(hrloc.loc_information18,hrloc.town_or_city),
nvl(hrloc.loc_information20,hrloc.postal_code)
from hr_locations hrloc,
hr_soft_coding_keyflex hrsckf,
per_all_assignments_f assign,
pay_assignment_actions paa
where paa.assignment_id = assign.assignment_id
and paa.assignment_action_id = p_asg_act_id
and assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
and nvl(hrsckf.segment18,assign.location_id) = hrloc.location_id
and assign.effective_start_date = (select max(paf.effective_start_date)
from per_all_assignments_f paf
where paf.assignment_id = assign.assignment_id);
SELECT element_type_id, object_version_number
FROM pay_shadow_element_types
WHERE element_name = p_ele_name AND template_id = p_template_id;
SELECT template_id
FROM pay_element_templates
WHERE template_name = p_template_name
AND legislation_code = p_legislation_code;
pay_shadow_element_api.update_shadow_element
(p_validate => FALSE
,p_effective_date => lrec.effective_date
,p_element_type_id => l_element_type_id
,p_element_name => lrec.element_name
,p_skip_formula => lrec.preference_information12
,p_element_information_category => lrec.preference_information7
-- p_ele_category
,p_element_information1 => NVL(lrec.configuration_info_category,
hr_api.g_varchar2)
--p_ele_ot_base
,p_element_information8 => NVL(lrec.configuration_information7,
hr_api.g_varchar2)
--p_flsa_hours
,p_element_information11 => NVL(lrec.configuration_information6,
hr_api.g_varchar2)
--p_reduce_regular
,p_element_information13 => NVL(lv_reduce_regular,
hr_api.g_varchar2)
--p_special_input_flag
,p_element_information14 => NVL(lrec.preference_information14,
hr_api.g_varchar2)
--p_stop_reach_rule
,p_element_information15 => NVL(lrec.preference_information15,
hr_api.g_varchar2)
,p_relative_processing_priority => ln_base_process_priority
,p_object_version_number => l_ovn
);
pay_shadow_element_api.update_shadow_element
(p_validate => FALSE
,p_reporting_name => lrec.reporting_name || ' SF'
,p_classification_name => lrec.element_classification
,p_effective_date => lrec.effective_date
,p_element_type_id => l_element_type_id
,p_description => 'Special Features element for '
|| lrec.element_name
--l_sf_rel_priority
,p_relative_processing_priority => ln_sf_process_priority
,p_element_information_category => lrec.preference_information7
--p_ele_category
,p_element_information1 => NVL(lrec.configuration_info_category,
hr_api.g_varchar2)
--p_ele_ot_base
,p_element_information8 => NVL(lrec.configuration_information7,
hr_api.g_varchar2)
,p_object_version_number => l_ovn
);
pay_shadow_element_api.update_shadow_element
(p_validate => FALSE
,p_reporting_name => lrec.reporting_name || ' SI'
,p_classification_name => lrec.element_classification
,p_effective_date => lrec.effective_date
,p_element_type_id => l_element_type_id
,p_description => 'Special Inputs element for '
|| lrec.element_name
--l_si_rel_priority
,p_relative_processing_priority => ln_si_process_priority
,p_element_information_category => lrec.preference_information7
--p_ele_category
,p_element_information1 => NVL(lrec.configuration_info_category,
hr_api.g_varchar2)
--p_ele_ot_base
,p_element_information8 => NVL(lrec.configuration_information7,
hr_api.g_varchar2)
,p_object_version_number => l_ovn
);
pay_shadow_element_api.update_shadow_element
(p_validate => FALSE
,p_effective_date => lrec.effective_date
,p_element_type_id => l_element_type_id
,p_element_name => lv_flsa_calc_name
,p_reporting_name => lrec.reporting_name || ' FC'
,p_classification_name => 'Information'
,p_description => 'FLSA Calc element for '
|| lrec.element_name
,p_skip_formula => lrec.preference_information12
,p_element_information_category => 'US_INFORMATION'
-- p_ele_category
,p_element_information1 => NULL
--p_ele_ot_base
,p_element_information8 => NVL(lrec.configuration_information7,
hr_api.g_varchar2)
,p_relative_processing_priority => ln_fc_process_priority
,p_object_version_number => l_ovn
);
pay_shadow_element_api.update_shadow_element
(p_validate => FALSE
,p_effective_date => lrec.effective_date
,p_element_type_id => l_element_type_id
,p_element_name => lv_prem_adjust_name
,p_reporting_name => lrec.reporting_name || ' Adjustment'
,p_classification_name => lrec.element_classification
,p_description => 'FLSA Period Adjust element for '
|| lrec.element_name
,p_skip_formula => lrec.preference_information12
,p_element_information_category => lrec.preference_information7
-- p_ele_category
,p_element_information1 => NVL(lrec.configuration_info_category,
hr_api.g_varchar2)
--p_ele_ot_base
,p_element_information8 => NVL(lrec.configuration_information7,
hr_api.g_varchar2)
,p_post_termination_rule => NVL(lrec.termination_rule,
hr_api.g_varchar2)
,p_relative_processing_priority => ln_fpa_process_priority
,p_object_version_number => l_ovn
);
SELECT business_group_id, legislation_code
FROM per_business_groups
WHERE NAME = cp_business_group_name;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name =
'Assignment within Government Reporting Entity Run'
AND legislation_code = 'US';
SELECT element_type_id
FROM pay_element_types_f
WHERE UPPER (element_name) = UPPER (p_element_name)
AND legislation_code = 'US';
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND UPPER (NAME) = UPPER (p_input_val_name);
SELECT status_processing_rule_id
FROM pay_status_processing_rules_f
WHERE element_type_id = p_element_type_id
AND business_group_id = p_bg_id;
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = p_formula_name
AND legislation_code = p_legislation_code;
SELECT FF.formula_name
FROM pay_status_processing_rules_f PSP,
ff_formulas_f FF
WHERE PSP.element_type_id = l_element_type_id
AND PSP.processing_rule = l_processing_rule
AND FF.formula_id = PSP.formula_id
AND PSP.business_group_id = l_business_group_id
AND FF.business_group_id = l_business_group_id;
SELECT event_group_id
FROM pay_event_groups
WHERE event_group_name = l_proration_group_name
AND ((business_group_id IS NULL and legislation_code IS NULL) OR
(business_group_id IS NULL and legislation_code = l_legislation_code) OR
(business_group_id = l_bg_id and legislation_code IS NULL)
);
SELECT retro_component_id
FROM pay_retro_components
WHERE component_name = l_comp_name
AND retro_type = l_retro_type
AND legislation_code = l_legislation_code;/*For seeded retro component, US
UPDATE pay_element_types_f
SET element_name = lrec.element_name,
element_information10 = l_pri_bal_id,
element_information12 = l_hours_bal_id
WHERE element_type_id = pay_us_earn_templ_wrapper.g_ele_type_id
AND business_group_id = ln_business_group_id;
UPDATE pay_element_types_f
SET element_information10 = l_fc_bal_id
WHERE element_type_id = l_fc_ele_type_id
AND business_group_id = ln_business_group_id;
UPDATE pay_element_types_f
SET element_information10 = l_fpa_bal_id,
element_information12 = l_fpa_hrs_bal_id,
proration_group_id = ln_proration_group_id
WHERE element_type_id = l_fpa_ele_type_id
AND business_group_id = ln_business_group_id;
UPDATE pay_input_values_f
SET formula_id = t_form_id (i),
warning_or_error = t_we_flag (i),
DEFAULT_VALUE = t_def_val (i)
WHERE input_value_id = t_ipv_id (i);
UPDATE pay_input_values_f
SET mandatory_flag = 'N'
WHERE input_value_id = t_ipv_id (i);
UPDATE pay_element_types_f
SET proration_group_id = ln_proration_group_id
WHERE business_group_id = ln_business_group_id
AND element_type_id = l_fc_ele_type_id;
UPDATE pay_element_types_f
SET recalc_event_group_id = ln_proration_group_id
WHERE business_group_id = ln_business_group_id
AND element_type_id = l_fc_ele_type_id;
UPDATE pay_element_types_f
SET time_definition_type = 'G'
WHERE business_group_id = ln_business_group_id
AND element_type_id in ( pay_us_earn_templ_wrapper.g_ele_type_id
,l_fpa_ele_type_id);
UPDATE pay_element_types_f
SET time_definition_type = 'G'
WHERE business_group_id = ln_business_group_id
AND element_type_id = pay_us_earn_templ_wrapper.g_ele_type_id;
UPDATE pay_element_types_f
SET time_definition_type = 'G'
WHERE business_group_id = ln_business_group_id
AND element_type_id = l_fc_ele_type_id;
/* PROCEDURE delete_pre_process */
/****************************************************************************/
PROCEDURE delete_pre_process(p_element_template_id IN NUMBER) IS
i NUMBER;
SELECT element_name
, business_group_id
, legislation_code
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id;
SELECT status_processing_rule_id
, effective_start_date
, object_version_number
FROM pay_status_processing_rules_f
WHERE element_type_id = p_element_type_id
AND ((business_group_id = p_business_group_id AND
legislation_code IS NULL) OR
(business_group_id IS NULL AND
legislation_code = p_legislation_code)
);
SELECT formula_result_rule_id
, effective_start_date
, object_version_number
FROM pay_formula_result_rules_f
WHERE status_processing_rule_id = p_status_processing_rule_id
AND ((business_group_id = p_business_group_id AND
legislation_code IS NULL) OR
(business_group_id IS NULL AND
legislation_code = p_legislation_code)
);
hr_utility.TRACE ('Entering pay_us_rules.delete_pre_process');
pay_formula_result_rule_api.DELETE_FORMULA_RESULT_RULE
(p_validate => FALSE
,p_effective_date => l_frr_eff_start_date
,p_datetrack_delete_mode => 'ZAP'
,p_formula_result_rule_id => l_frr_id
,p_object_version_number => l_frr_obj_ver_num
,p_effective_start_date => l_eff_start_date
,p_effective_end_date => l_eff_end_date
);
pay_status_processing_rule_api.delete_status_process_rule
(p_validate => FALSE
,p_effective_date => l_spr_eff_start_date
,p_datetrack_mode => 'ZAP'
,p_status_processing_rule_id => l_spr_id
,p_object_version_number => l_spr_obj_ver_num
,p_effective_start_date => l_eff_start_date
,p_effective_end_date => l_eff_end_date
);
hr_utility.TRACE ('Error in pay_us_rules.delete_pre_process');
hr_utility.TRACE ('Leaving pay_us_rules.delete_pre_process');
END delete_pre_process;
/* PROCEDURE delete_post_process */
/****************************************************************************/
PROCEDURE delete_post_process(p_element_template_id IN NUMBER) IS
BEGIN
hr_utility.TRACE ('Entering pay_us_rules.delete_post_process');
hr_utility.TRACE ('Leaving pay_us_rules.delete_post_process');
END delete_post_process;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_object_name
AND business_group_id = p_business_group_id;
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.NAME = p_object_name
AND piv.element_type_id = p_object_id
AND piv.business_group_id = p_business_group_id;
SELECT balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_object_name
AND NVL (pbt.business_group_id, p_business_group_id) =
p_business_group_id
AND NVL (pbt.legislation_code, p_legislation_code) = p_legislation_code;
SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = 'US'
and rule_type = 'S';
SELECT target.SEGMENT4
FROM /* route for SCL keyflex - assignment level */
hr_soft_coding_keyflex target,
per_all_assignments_f ASSIGN
WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
AND target.id_flex_num = p_id_flex_num;
select put.user_table_name
into c_ws_tab_name
from hr_organization_information hoi
,pay_user_tables put
where hoi.organization_id = p_bg_id
and hoi.org_information_context ='Work Schedule'
and hoi.org_information1 = put.user_table_id ;
SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
INTO v_fnd_sess_row
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
select 'Y'
into l_exists
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_NAME = p_ws_name
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'US') = 'US'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name;
select PUC.USER_COLUMN_NAME
into v_ws_name
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_ID = p_ws_name
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'US') = 'US'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name;
SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
4,'WED',5,'THU',6,'FRI',7,'SAT')
INTO v_curr_day
FROM DUAL;
SELECT NVL(ppa.date_earned,ppa.effective_date)
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE paa.assignment_action_id = cp_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT element_name
FROM pay_element_types_f pet,
pay_element_entries_f pee
WHERE pee.element_entry_id = cp_element_entry
AND pee.element_type_id = pet.element_type_id
AND ((pet.legislation_code = 'US' and pet.business_group_id is null)
or (pet.legislation_code is null and pet.business_group_id = cp_bus_grp))
AND cp_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND cp_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT time_definition_id
FROM pay_time_definitions
WHERE definition_name = p_time_def_name
AND legislation_code = p_legislation_code
AND business_group_id IS NULL;
* or insert data into any database tables. No calls to hr_utility.trace *
* should be made. *
/****************************************************************************/
FUNCTION get_time_def_for_entry_func(
p_element_entry_id NUMBER,
p_assignment_id NUMBER,
p_assignment_action_id NUMBER,
p_business_group_id NUMBER,
p_time_def_date DATE
) RETURN NUMBER IS
/* Check if employee is flsa eligible */
CURSOR get_jobs_us_flsa_code(cp_date_earned DATE
,cp_assignment_id NUMBER) IS
SELECT nvl(perj.JOB_INFORMATION3, 'EX')
FROM per_jobs perj,
per_jobs_tl perjtl,
per_all_assignments_f paa
WHERE cp_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.assignment_id = cp_assignment_id
AND paa.job_id = perj.job_id
AND paa.job_id = perjtl.job_id
AND userenv('LANG') = perjtl.language;
SELECT peev.screen_entry_value
FROM pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_entry_values_f peev
WHERE pet.element_name = cp_ele_name
AND pet.business_group_id is NULL
AND peef.element_type_id = pet.element_type_id
AND pet.element_type_id = piv.element_type_id
AND pet.legislation_code = 'US'
AND piv.business_group_id is NULL
AND piv.legislation_code = 'US'
AND piv.NAME = cp_inp_val_name
AND peev.element_entry_id = peef.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND peef.assignment_id = cp_assignment_id
AND cp_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND cp_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND cp_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND cp_date BETWEEN peev.effective_start_date
AND peev.effective_end_date;
SELECT person_id, payroll_id
FROM per_all_assignments_f
WHERE assignment_id = cp_assignment_id
AND cp_date BETWEEN effective_start_date AND effective_end_date;
SELECT time_definition_id
FROM pay_time_definitions
WHERE creator_id = cp_otl_recurring_period AND creator_type = 'OTL_W';
SELECT pap.prl_information10
FROM pay_all_payrolls_f pap
WHERE pap.payroll_id = cp_payroll_id
AND cp_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT time_definition_id
FROM pay_time_definitions
WHERE definition_name = p_time_def_name
AND legislation_code = p_legislation_code
AND business_group_id IS NULL;
SELECT element_name
FROM pay_element_types_f pet,
pay_element_entries_f pee
WHERE pee.element_entry_id = cp_element_entry
AND pee.element_type_id = pet.element_type_id
AND ((pet.legislation_code = 'US' and pet.business_group_id is null)
or (pet.legislation_code is null and pet.business_group_id = cp_bus_grp))
AND cp_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND cp_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT net_pay
FROM PAY_AC_EMP_SUM_ACTION_INFO_V
WHERE action_context_id = cp_assignment_action_id
AND action_information_category = 'AC SUMMARY CURRENT';
SELECT net_pay
FROM PAY_AC_EMP_SUM_ACTION_INFO_V
WHERE action_context_id = cp_assignment_action_id
AND ACTION_INFORMATION_CATEGORY = 'AC SUMMARY YTD';
SELECT pai.action_information16, ppt.CATEGORY, pai.action_information5,
pai.action_information6, pai.action_information7,
pai.action_information8, pai.action_information9,
pai.action_information10, paa.serial_number
FROM pay_action_information pai,
pay_org_payment_methods_f popmf,
pay_payment_types ppt,
pay_assignment_actions paa
WHERE pai.action_context_id = arch_assact_id
AND pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND paa.assignment_action_id = chk_assact_id
AND popmf.org_payment_method_id = pai.action_information1
AND popmf.payment_type_id = ppt.payment_type_id
AND paa.pre_payment_id = pai.action_information15
AND pai.effective_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date;
SELECT locked_action_id
FROM pay_action_interlocks
WHERE locking_action_id = arch_assact_id;
SELECT pai.action_information5,
DECODE (pai.action_information6,
'C', 'Checking Account',
'Savings Account'
),
pai.action_information7, pai.action_information8,
pai.action_information9, pai.action_information10,
pai.action_information17, pai.action_information16,
pai.action_information2, --pai.action_information1 #12549430
ppt.CATEGORY --For bug#9541448
FROM pay_action_information pai,
pay_org_payment_methods_f popmf,
pay_payment_types ppt
WHERE pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND pai.action_context_id = arch_assact_id
AND pai.action_information1 = popmf.org_payment_method_id
AND popmf.payment_type_id = ppt.payment_type_id
AND ppt.CATEGORY = 'MT'
AND pai.effective_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date ;
SELECT paa.serial_number, pain.action_information16 ,
pain.action_information9 ,
DECODE (pain.action_information6,
'C', 'Checking Account',
'Savings Account'
),
pain.action_information7
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai1,
pay_action_information pain
WHERE pai.locking_action_id = cp_assignment_action_id
AND pai.locked_action_id = pai1.locked_action_id
AND pai.locking_action_id <> pai1.locking_action_id
AND pai1.locking_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'H'
AND pain.action_information15 = paa.pre_payment_id
AND pain.action_context_id = pai.locking_action_id
AND pain.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION' ;
select ppa.business_group_id,
pai.tax_unit_id,
pai.action_information2,
pai.effective_date,
ppa.payroll_action_id
from
pay_action_information pai,
pay_payroll_actions ppa
where pai.action_context_id=cp_assignment_action_id
AND ppa.payroll_action_id= (select payroll_action_id
from pay_assignment_actions
where assignment_action_id = cp_assignment_action_id)
and pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMPLOYEE DETAILS' ;
SELECT
DISTINCT
action_information5,
action_information6,
action_information7,
action_information8,
-- action_information9,
action_information10,
action_information12,
action_information13
FROM
pay_action_information pai,
pay_payroll_actions ppa
WHERE action_context_type = 'PA'
AND action_context_id=ppa.payroll_action_id
AND action_information_category = 'ADDRESS DETAILS'
AND action_information14 = 'Employer Address'
AND action_context_id=cp_payroll_action_id
AND pai.action_information1 = cp_organization_id;
SELECT check_deposit_number,
segment5,
segment2,
segment3,
value from
pay_emp_net_dist_action_info_v
WHERE action_context_id=cp_assignment_action_id;
SELECT
org.NAME,loc.telephone_number_1
FROM
hr_all_organization_units org,
hr_locations_all loc
WHERE
org.location_id=loc.location_id
AND org.organization_id = cp_organization_id;
SELECT paa.serial_number, pai.action_information3 Amount,
ltrim (initcap (rtrim (ppf.title))|| ' '|| rtrim (ppf.first_name)|| ' '|| rtrim (ppf.last_name)) employee_name,
national_identifier Employee_SSN,
pai.action_information20 Print_SSN_Flag,
pea.segment4 ER_Transit_code,
pea.segment3 ER_Account_Number
FROM
pay_assignment_actions paa,
pay_action_information pai,
per_all_assignments_f paf,
per_all_people_f ppf,
pay_external_accounts pea,
pay_org_payment_methods_f popm
WHERE paa.assignment_action_id = cp_chk_assactid
AND pai.action_context_id = cp_assactid
AND paa.pre_payment_id = pai.action_information2
AND paf.assignment_id = pai.assignment_id
AND ppf.person_id = paf.person_id
AND pai.effective_date between paf.effective_start_date and paf.effective_end_date
AND pai.effective_date between ppf.effective_start_date and ppf.effective_end_date
AND popm.org_payment_method_id = pai.action_information5
AND pai.effective_date between popm.effective_start_date and popm. effective_end_date
AND pea.external_account_id = popm.external_account_id
AND pai.action_information_category = 'US THIRD PARTY CHECKS'; -- added for 15949522
select initcap(lower(
l_word_amount||' '||
decode(trunc(p_amount),
1,l_unit_singular,
l_unit_plural)||' And '||
lpad(to_char(trunc((p_amount-trunc(p_amount))*l_unit_ratio)),
ceil(l_log),'0')||' '||
decode(trunc((p_amount-trunc(p_amount))*l_unit_ratio),
1,l_sub_unit_singular,
l_sub_unit_plural)
))
into l_currency_word
from dual;
SELECT hl.description
FROM hr_lookups hl
WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
AND hl.lookup_code = 'US_DEPOSIT_ADVICE_XML'
AND hl.enabled_flag = 'Y';
SELECT hl.description
FROM hr_lookups hl
WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
AND hl.lookup_code = 'US_ARCHIVE_CHECK_XML'
AND hl.enabled_flag = 'Y';
SELECT hl.description
FROM hr_lookups hl
WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
AND hl.lookup_code = 'US_THIRD_PARTY_CHECK_XML'
AND hl.enabled_flag = 'Y';