[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'PQP_GB_BEN_LER_ABSENCE_DELETE_EVENT_PERSON_CHANGES_RULE'
--||l_uniquestamp
,1,80);
Formula Name: PQP_GB_BEN_LER_ABSENCE_DELETE_EVENT_PERSON_CHANGES_RULE
Formula Type: Person Change Causes Life Event
Description : Sample formula to detect that an absence has been deleted.
DISCLAIMER :
In future releases of HRMS programs, Oracle Corporation may change or
upgrade this formula, and all other definitions for the predefined template
of which this formula is a part. We do not guarantee that the formula and the
predefined template will provide a ready-made solution to be used in your
environment. If the formula does not reflect your business rules, you are
responsible for writing a formula of your own to meet your particular
requirements. Any use of this formula and the predefined extract is subject to
the terms of the Oracle license agreement for the HRMS programs and
documentation.
**Change List
===========
Name Date Version Bug Text
============== =========== ======= ======= ===================================
rrazdan 29-JUL-2002 110.0 Created.
==============================================================================*/
/*
Set default values.
*/
DEFAULT FOR BEN_ABA_IN_DATE_START IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_DATE_START IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_DATE_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_DATE_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
/* Other inputs available are
DEFAULT FOR BEN_ABA_IN_PERSON_ID IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_PERSON_ID IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_SICKNESS_START_DATE IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_SICKNESS_START_DATE IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_SICKNESS_END_DATE IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_SICKNESS_END_DATE IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_ABSENCE_DAYS IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_ABSENCE_DAYS IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_ABSENCE_HOURS IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_ABSENCE_HOURS IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_DATE_NOTIFICATION IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_DATE_NOTIFICATION IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_START IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_START IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_TIME_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_TIME_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_END IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_START IS ''_DEFAULT_''
DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_START IS ''_DEFAULT_''
*/
/*
Declare Input values.
NOTE the naming convention followed
BEN_ABA_IN_ - New Values
BEN_ABA_IO_ - Old Values
*/
INPUTS ARE BEN_ABA_IN_DATE_START(TEXT)
,BEN_ABA_IO_DATE_START(TEXT)
,BEN_ABA_IN_DATE_END(TEXT)
,BEN_ABA_IO_DATE_END(TEXT)
,BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
,BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
/* Other inputs available are
,BEN_ABA_IN_PERSON_ID(TEXT)
,BEN_ABA_IO_PERSON_ID(TEXT)
,BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID(TEXT)
,BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID(TEXT)
,BEN_ABA_IN_SICKNESS_START_DATE(TEXT)
,BEN_ABA_IO_SICKNESS_START_DATE(TEXT)
,BEN_ABA_IN_SICKNESS_END_DATE(TEXT)
,BEN_ABA_IO_SICKNESS_END_DATE(TEXT)
,BEN_ABA_IN_ABSENCE_DAYS(TEXT)
,BEN_ABA_IO_ABSENCE_DAYS(TEXT)
,BEN_ABA_IN_ABSENCE_HOURS(TEXT)
,BEN_ABA_IO_ABSENCE_HOURS(TEXT)
,BEN_ABA_IN_DATE_NOTIFICATION(TEXT)
,BEN_ABA_IO_DATE_NOTIFICATION(TEXT)
,BEN_ABA_IN_DATE_PROJECTED_END(TEXT)
,BEN_ABA_IO_DATE_PROJECTED_END(TEXT)
,BEN_ABA_IN_DATE_PROJECTED_START(TEXT)
,BEN_ABA_IO_DATE_PROJECTED_START(TEXT)
,BEN_ABA_IN_TIME_END(TEXT)
,BEN_ABA_IO_TIME_END(TEXT)
,BEN_ABA_IN_TIME_PROJECTED_END(TEXT)
,BEN_ABA_IO_TIME_PROJECTED_END(TEXT)
,BEN_ABA_IN_TIME_PROJECTED_START(TEXT)
,BEN_ABA_IO_TIME_PROJECTED_START(TEXT)
*/
/*
Initialise values.
*/
l_default = ''_DEFAULT_'' /* TEXT */
l_yn = ''N'' /* TEXT */
/* 01. Determine the old and new values for ABSENCE_ATTENDANCE_TYPE_ID
NOTE Though these values are stored as dates or numbers they are made
available to the formula as TEXT inputs.
The dates are available in the default canoncial format of
"YYYY/MM/DD HH24:MI:SS".
Where required, use TO_DATE to convert text into a date.
The numbers are available in the default canonical format.
Where required, use TO_NUMBER to convert into text into a number.
*/
l_absence_type_id_old_value = BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID
l_absence_type_id_new_value = BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID
/* 02a. Check that the new value of absence type id has been defaulted.
NOTE When an absence is deleted, the absence_type_id new value will be
defaulted. To record an absence delete a seperate life event reason has
to be setup. To prevent a absence start change from being logged on a
delete, an additional check is introduced to ensure that new value is not
equal to the default.
*/
IF NOT l_absence_type_id_old_value = l_default
AND
l_absence_type_id_new_value = l_default
THEN
(
/* 02b. Set the return flag to "Y"es to allow this person change to cause an
"absence delete" life event.
NOTE the default for the flag is "N"o.
*/
l_yn = ''Y''
)
RETURN l_yn
';
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,l_effective_start_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count)
-- formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
;
NOTE When an absence is deleted, the absence_type_id new value will be
defaulted. To record an absence delete a seperate life event reason has
to be setup. To prevent a absence start change from being logged on a
delete, an additional check is introduced to ensure that new value is not
equal to the default.
*/
IF NOT l_date_end_old_value = l_date_end_new_value
AND
NOT l_absence_type_id_new_value = l_default
THEN
(
/* 02b. Set the return flag to "Y"es to allow this person change to cause an
"absence end" life event.
NOTE the default for the flag is "N"o.
*/
l_yn = ''Y''
)
RETURN l_yn
';
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,l_effective_start_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
NOTE When an absence is deleted, the absence_type_id new value will be
defaulted. To record an absence delete a seperate life event reason has
to be setup. To prevent a absence start change from being logged on a
delete, an additional check is introduced to ensure that new value is not
equal to the default.
*/
IF NOT l_date_start_old_value = l_date_start_new_value
AND
NOT l_absence_type_id_new_value = l_default
THEN
(
/* 02b. Set the return flag to "Y"es to allow this person change to cause an
"absence start" life event.
NOTE the default for the flag is "N"o.
*/
l_yn = ''Y''
)
/* End Absence Start Date Section
*/
l_debug_message = ''End Absence Start Date Section:''||l_yn
l_debug = DEBUG(l_debug_message)
/* Begin Absence Information 1 - Start Date Fraction Section
*/
/* 03a. Check that there is a difference between the old and new values of
ABS_INFORMATION1.
NOTE This is an example of how this formula may be extended to trigger the
absence start life event reason for other changes on absences.
First check that life event flag is set to "N", i.e. the previous absence
change hasn''t already caused a valid person change to cause the life event.
*/
IF l_yn = ''N'' THEN
(
l_abs_information1_old_value = BEN_ABA_IO_ABS_INFORMATION1
l_abs_information1_new_value = BEN_ABA_IN_ABS_INFORMATION1
IF NOT l_abs_information1_old_value = l_abs_information1_new_value
AND
NOT l_absence_type_id_new_value = l_default
THEN
(
l_yn = ''Y''
)
)
/* End Absence Information 1 - Start Date Fraction Section
*/
l_debug_message = ''End Absence Information 1 Section:''||l_yn
l_debug = DEBUG(l_debug_message)
/* Begin Absence Information 2 - End Date Fraction Section
*/
/* 03a. Check that there is a difference between the old and new values of
ABS_INFORMATION1.
NOTE This is an example of how this formula may be extended to trigger the
absence start life event reason for other changes on absences.
First check that life event flag is set to "N", i.e. the previous absence
change hasn''t already caused a valid person change to cause the life event.
*/
IF l_yn = ''N'' THEN
(
l_abs_information2_old_value = BEN_ABA_IO_ABS_INFORMATION2
l_abs_information2_new_value = BEN_ABA_IN_ABS_INFORMATION2
IF NOT l_abs_information2_old_value = l_abs_information2_new_value
AND
NOT l_absence_type_id_new_value = l_default
THEN
(
l_yn = ''Y''
)
)
/* End Absence Information 2 - End Date Fraction Section
*/
/* Begin Absence Days Section
*/
/* 03a. Check that there is a difference between the old and new values of
ABSENCE_DAYS.
NOTE This is an example of how this formula may be extended to trigger the
absence start life event reason for other changes on absences.
First check that life event flag is set to "N", i.e. the previous absence
change hasn''t already caused a valid person change to cause the life event.
*/
/*
IF l_yn = ''N'' THEN
(
l_absence_days_old_value = BEN_ABA_IO_ABSENCE_DAYS
l_absence_days_new_value = BEN_ABA_IN_ABSENCE_DAYS
IF NOT l_absence_days_old_value = l_absence_days_new_value
AND
NOT l_absence_type_id_new_value = l_default
THEN
(
l_yn = ''Y''
)
)
*/
/* End Absence Days Section
*/
RETURN l_yn
';
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,l_effective_start_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,p_effective_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
are selected for plan enrollment.
*/
IF NOT l_employee_hire_date = l_default_date THEN
(
/* 03. Calculate the length of service.
NOTE the sample OSP scheme uses "Months" as the unit of measure for
evaluating length of service based eligibility of OSP entitlements.
The month is rounded down to the nearest interger, by using the
FLOOR function.
*/
l_length_of_service
= FLOOR
(MONTHS_BETWEEN
(l_absence_start_date /* later date first */
,l_employee_hire_date
)
)
)
)
LENGTH_OF_SERVICE = l_length_of_service
RETURN LENGTH_OF_SERVICE
';
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,p_effective_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,p_effective_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,p_effective_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
are selected for plan enrollment.
*/
IF NOT l_employee_hire_date = l_default_date THEN
(
/* 03a. Determine the OMP Qualifying date.
NOTE As a first step determine the OMP Qualifying Date Type.
This can three possible values
"S" - For SMP Qualifying Date
"M" - For Maternity Start Date
"P" - For a given duration Prior to the Expected Week of Confinement
(EWC)
*/
l_omp_qualifying_date_type = l_default
l_truncated_yes_no = l_default
l_error_message = l_default
l_error_code =
PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
(''OMP Qualifying Date Type''
,l_omp_qualifying_date_type
,l_truncated_yes_no
,l_error_message
)
IF l_error_code = 0 THEN
(
/* 03b. Determine the OMP Qualifying date.
NOTE If the OMP Qualifying Date Type is ''S'' then it means that it
is the same as SMP Qualifying Date. This date is available on the
Maternity form as the field "Qualifying Week".
*/
l_omp_qualifying_date = l_default_date
IF l_omp_qualifying_date_type = ''S'' THEN
(
l_smp_qualifying_date_text =
BEN_GET_MATERNITY
(''Qualifying Week''
,l_error_code
,l_error_message
)
IF l_error_code = 0 THEN
(
l_omp_qualifying_date = TO_DATE(l_smp_qualifying_date_text,''YYYY/MM/DD HH24:MI:SS'')
)
)
/* 03b. Determine the OMP Qualifying date.
NOTE If the OMP Qualifying Date Type is ''M'' then it means that it
is the same as Maternity Start Date. Maternity Start Date is start
date of the maternity absence.
*/
IF l_omp_qualifying_date_type = ''M'' THEN
(
l_omp_qualifying_date = l_absence_start_date
)
/* 03b. Determine the OMP Qualifying date.
NOTE If the OMP Qualifying Date Type is ''P'' then it means that the
OMP Qualifying Date has to be derived as a given period prior to the
expected week of confinement (EWC).
The period is defined as extra information on the OMP Plan(as setup
in the OMP template form). The period is defined as a given
"Duration" of a certain "UOM", prior to the EWC.
EWC is available on the Maternity form.
*/
IF l_omp_qualifying_date_type = ''P'' THEN
(
/* 03c. Determine the "Prior to EWC Duration".
NOTE To access the OMP Plan rules and regulations as defined on the
OMP template form. Use PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION.
All values returned by this function are always TEXT datatype. If
the expected value is a NUMBER/DATE appropriate conversions might
be required before using the value.
*/
l_prior_to_ewc_duration_text = l_default_canonical_number
l_error_code =
PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
(''Prior to EWC Duration''
,l_prior_to_ewc_duration_text
,l_truncated_yes_no
,l_error_message
)
l_prior_to_ewc_duration = TO_NUMBER(l_prior_to_ewc_duration_text)
/* 03c. Determine the "Prior to EWC UOM".
NOTE To access the OMP Plan rules and regulations as defined on the
OMP template form. Use PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION.
All values returned by this function are always TEXT datatype. If
the expected value is a NUMBER/DATE appropriate conversions might
be required before using the value.
"Prior to EWC UOM" has three possible values.
i. DAYS
ii. WEEKS
iii. MONTHS
*/
l_prior_to_ewc_uom = l_default
l_error_code =
PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
(''Prior to EWC UOM''
,l_prior_to_ewc_uom
,l_truncated_yes_no
,l_error_message
)
/* 03c. Determine the EWC date.
NOTE This value is available on the Maternity form. To access
details as seen on the Maternity form use the PQP_GET_MATERNITY
function, passing the appropriate field name.
All values returned by the this function are always TEXT
datatype.If the expected value is a NUMBER/DATE appropriate
conversions might be required before using the value.
*/
l_ewc_text =
BEN_GET_MATERNITY
(''EWC''
,l_error_code
,l_error_message
)
IF l_error_code = 0 THEN
(
l_ewc = TO_DATE(l_ewc_text,''YYYY/MM/DD HH24:MI:SS'')
/* 03c. Calculate the OMP Qualifying Date as
EWC - (Duration) Days
or
EWC - (Duration)*7 Days (If UOM is Weeks)
or
EWC - (Duration) Months
*/
IF l_prior_to_ewc_uom = ''DAYS'' THEN
(
l_omp_qualifying_date =
ADD_DAYS
(l_ewc
,- l_prior_to_ewc_duration
)
)
IF l_prior_to_ewc_uom = ''WEEKS'' THEN
(
l_omp_qualifying_date =
ADD_DAYS
(l_ewc
,-l_prior_to_ewc_duration * 7
)
)
IF l_prior_to_ewc_uom = ''MONTHS'' THEN
(
l_omp_qualifying_date =
ADD_MONTHS
(l_ewc
,-l_prior_to_ewc_duration
)
)
) /* END IF NOT l_error_code = 0 BEN_GET_MATERNITY(EWC) */
) /* END IF l_omp_qualifying_date_type = ''P'' */
/* 04. Calculate the length of service as the period between the
employee hire date and the OMP qualifying date.
NOTE The length of service for the sample OMP scheme is measured
in weeks.The week is rounded down to the nearest interger, by
using the FLOOR function.
*/
IF NOT l_omp_qualifying_date = l_default_date THEN
(
l_length_of_service =
FLOOR(DAYS_BETWEEN(l_omp_qualifying_date,l_employee_hire_date)/7)
)
) /* END IF NOT l_error_code = 0 OMP Qualifying Date Type */
) /* END IF NOT l_employee_hire_date = l_default_date */
) /* END IF NOT l_absence_start_date = l_default_date THEN */
RETURN l_length_of_service
';
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,p_effective_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = l_formula_type;
INSERT INTO ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag)
VALUES(ff_formulas_s.NEXTVAL
,p_effective_date
,l_effective_end_date
,l_business_group_id
,l_legislation_code
,l_formula_type_id
,l_formula_name
,l_description
,NULL
,NULL
)
-- WHERE NOT EXISTS
-- (SELECT 1
-- FROM ff_formulas_f
-- WHERE formula_name = l_formula_name
-- AND business_group_id = l_business_group_id
-- )
RETURNING formula_id INTO p_formulas(l_formula_count);
UPDATE ff_formulas_f
SET formula_text = l_text
WHERE formula_id = p_formulas(l_formula_count);
PROCEDURE delete_ben_formulas
(p_business_group_id IN NUMBER
,p_effective_date IN DATE
,p_absence_pay_plan_category IN VARCHAR2
,p_base_name IN VARCHAR2
,p_error_code OUT NOCOPY NUMBER
,p_error_message OUT NOCOPY VARCHAR2
)
IS
l_proc_name VARCHAR2(61):= g_proc_name||'delete_ben_formulas';
pqp_utilities.delete_formula
(p_formula_id => l_formula.formula_id
,p_error_code => p_error_code
,p_error_message => p_error_message
);
pqp_utilities.delete_formula
(p_formula_id => l_formula.formula_id
,p_error_code => p_error_code
,p_error_message => p_error_message
);
pqp_utilities.delete_formula
(p_formula_id => l_formula.formula_id
,p_error_code => p_error_code
,p_error_message => p_error_message
);
END delete_ben_formulas;