[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
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 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;