[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Calling update_shadow_element.
12-DEC-2004 vpandya 115.2 Removed legislation rule check.
And calling pre and post process
package without checking leg rule.
22-DEC-2004 vpandya 115.3 Added SYSTEM to Type Object in
declaration section.
05-JAN-2005 vpandya 115.4 Calling procedure
ELEMENT_TEMPLATE_UPD_USER_STRU
That is being called after
User Structure generation (Shadow
Schema ) and before creating Core
Schema. Also calling
fnd_request.submit_request to
compile formula.
27-JAN-2005 vmehta 115.5 Removed reference to SYSTEM schema
from objects.
18-FEB-2005 ssattini 115.6 Added code to create
Sub-classification
and Frequency Rules records.
07-MAR-2005 ssattini 115.7 Added code to initialise
Frequency Rule PL/SQL table
used in Summary Page of Element
Template Wizard.
14-Apr-2005 vpandya 115.8,9 4300619 Setting default processing
priority only when
p_rec.processing_priority
is null or it not in its range.
28-APR-2005 pganguly 115.10 Added the delete_element
procedure.
04-MAY-2005 pganguly 115.11 Changed the procedure in
delete_ element.
01-AUG-2005 rdhingra 115.12 4518218 Removing code from the call of
Cursor Name: get_proc_priority
As decided, if the user is allowed
to give his own priority then
thats what should be carried
forward.
31-AUG-2005 vpandya 115.13 4585922 Added a cursor get_shd_formula_id
and compiling formulas.
14-Sep-2005 vpandya 115.14 Changed code for exception so that
it raises error if inlined pkg
returns any error.
15-Nov-2006 vpandya 115.15 5609242 Added a function
get_base_classification to get
classification name in base (US)
language if it entered in
translated/pseudo language. Also
used for sub classifications.
12-Dec-2011 vvijayku 115.16 13362720 Added new code to update the element
information category for an element
being created through an Element Design
wizard.
12-Dec-2011 vvijayku 115.17 13362720 Corrected the code added.
21-Dec-2011 vvijayku 115.18 13362720 Added new code to automatically update
the Primary Balance id and the Hourly
balance id in the columns Element_Information10
and Element_Information12 respectively.
05-Mar-2012 vvijayku 120.4.12010000.4
13772336 Adding new code to insert the Arrears and
Accruals Balance Id in Element_InformationXX
when a Deduction type element is created for
International Payroll product.
================================================================================
*/
--
g_package varchar2(50);
SELECT pec.classification_id
,pec.classification_name
,pect.language
FROM pay_element_classifications_tl pect
,pay_element_classifications pec
WHERE pect.classification_name = cp_classification_name
AND pec.classification_id = pect.classification_id
AND ( pec.legislation_code = cp_legislation_code OR
pec.business_group_id = cp_business_group_id )
ORDER BY pec.classification_id DESC;
SELECT template_id
FROM pay_element_templates
WHERE legislation_code = cp_legislation_code
AND template_name = cp_template_name;
SELECT business_group_id
,legislation_code
,currency_code
FROM per_business_groups
WHERE name = cp_business_group_name;
SELECT element_type_id
,object_version_number
,payroll_formula_id
FROM pay_shadow_element_types
WHERE template_id = cp_template_id
AND element_name = cp_element_name;
SELECT default_priority
,default_high_priority
,default_low_priority
FROM pay_element_classifications
WHERE legislation_code = cp_legislation_code
AND classification_name = cp_classification_name;
SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = cp_legislation_code
AND rule_type = 'SEP_CHEQUE_IV';
SELECT payroll_formula_id
FROM pay_shadow_element_types
WHERE template_id = cp_template_id
AND payroll_formula_id IS NOT NULL;
SELECT ff.formula_name
,ft.formula_type_name
FROM pay_shadow_formulas psf
,ff_formulas_f ff
,ff_formula_types ft
WHERE psf.formula_id = cp_formula_id
AND psf.formula_name = ff.formula_name
AND ff.formula_type_id = ft.formula_type_id;
SELECT core_object_id
,shadow_object_id
FROM pay_template_core_objects
WHERE template_id = cp_template_id
AND core_object_type = cp_object_type;
SELECT payroll_id
FROM pay_payrolls_f
WHERE business_group_id + 0 = cp_bg_id
AND payroll_name = cp_payroll_name
AND cp_eff_date between effective_start_date and effective_end_date;
SELECT pet.element_type_id,
upper(pec.classification_name)
FROM pay_element_types_f pet,
pay_element_classifications pec
WHERE pet.element_name = cp_element_name
AND pet.business_group_id = cp_business_grp_id
AND pec.classification_id = pet.classification_id;
SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
FROM pay_balance_types pbt,
pay_element_types_f pet
WHERE pet.element_type_id = cp_element_id
AND pbt.balance_name = pet.element_name
AND pbt.business_group_id = pet.business_group_id;
SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
FROM pay_balance_types pbt,
pay_element_types_f pet
WHERE pet.element_type_id = cp_element_id
AND pbt.balance_name = pet.element_name||' Hours'
AND pbt.business_group_id = pet.business_group_id;
SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
FROM pay_balance_types pbt,
pay_element_types_f pet
WHERE pet.element_type_id = cp_element_id
AND pbt.balance_name = pet.element_name||' Arrears'
AND pbt.business_group_id = pet.business_group_id;
SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
FROM pay_balance_types pbt,
pay_element_types_f pet
WHERE pet.element_type_id = cp_element_id
AND pbt.balance_name = pet.element_name||' Accrued'
AND pbt.business_group_id = pet.business_group_id;
SELECT COUNT(*)
INTO ln_iv_exists
FROM pay_shadow_element_types pset
,pay_shadow_input_values psiv
WHERE pset.template_id = l_source_template_id
AND psiv.element_type_id = pset.element_type_id
AND psiv.name = lv_rule_mode;
pay_shadow_element_api.update_shadow_element
(p_validate => p_validate
,p_effective_date => l_rec.effective_date
,p_element_type_id => ln_shd_ele_type_id
,p_reporting_name => l_rec.reporting_name
,p_description => l_rec.element_description
,p_classification_name => l_rec.element_classification
,p_post_termination_rule => l_rec.termination_rule
,p_standard_link_flag => l_rec.standard_link
,p_processing_type => l_rec.processing_type
,p_element_information_category => lv_information_category
,p_once_each_period_flag => l_rec.proc_once_pay_period
,p_process_mode => l_rec.process_mode
,p_input_currency_code => l_rec.input_currency_code
,p_output_currency_code => lv_currency_code
,p_multiple_entries_allowed_fla => l_rec.multiple_entries_allowed
,p_object_version_number => l_ele_obj_ver_number);
UPDATE pay_element_types_f set ELEMENT_INFORMATION10 = lv_primary_bal_id , ELEMENT_INFORMATION12 = lv_hourly_bal_id, ELEMENT_INFORMATION11 = lv_arrears_bal_id, ELEMENT_INFORMATION13 = lv_accruals_bal_id
WHERE element_type_id = ln_element_type_id
AND element_information_category = upper(l_rec.legislation_code)||'_'||lv_classification_name;
(p_context => 'ON-UPDATE',
p_eletype_id => ln_core_object_id,
p_payroll_id => ln_payroll_id,
p_period_type => lr_freq_rule_rec.period_type,
p_bg_id => ln_business_group_id,
p_period_1 => lr_freq_rule_rec.period_1,
p_period_2 => lr_freq_rule_rec.period_2,
p_period_3 => lr_freq_rule_rec.period_3,
p_period_4 => lr_freq_rule_rec.period_4,
p_period_5 => lr_freq_rule_rec.period_5,
p_period_6 => lv_period_6,
p_eff_date => l_rec.effective_date,
p_rule_date_code => lr_freq_rule_rec.date_option,
p_leg_code => null);
PROCEDURE delete_element
( p_validate IN BOOLEAN,
p_template_id IN NUMBER
) IS
l_proc VARCHAR2(240);
SELECT
pbg.legislation_code
FROM
per_business_groups pbg,
pay_element_templates pet
WHERE
pet.template_id = p_template_id AND
pet.business_group_id = pbg.business_group_id;
l_proc := 'delete_element';
'_RULES.DELETE_PRE_PROCESS(:c); END;'
pay_element_template_api.delete_user_structure
(p_validate => p_validate,
p_drop_formula_packages => TRUE,
p_template_id => p_template_id);
'_RULES.DELETE_POST_PROCESS(:c); END;'
END delete_element;