[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The following database items are created/ deleted using procedures in
this file:
balances
element types
input values
grade rates
descriptive flexs
key flexs
absence types
NOTES
User Defined Data:
-----------------
User defined data may either be owned by a ---------------------
business group OR by a legislation. Since | User Defined Data |
a business group belongs to a legislation, ---------------------
the legislation code for data that has a Y Y
business group id may always be derived. | |
\ | | /
\ | | /
The effect on the entity horizon is that \------------/
either the legislation code OR the | |
business group id may be specified. The ------------ |
columns on the table ff_user_entities should | Business | |
not BOTH be populated. | group | |
------------ |
The allowed combinations for these 2 columns Y |
are shown in the table below. This is now the | |
same model for Payroll and Formula. | |
| |
---------------
| Legislation |
---------------
--
--------------------------------------------------------------------
| Legislation Business |
| Code Group id |
| |
| Generic Startup Data : null null |
| |
| Legislation Specific : not null null |
| Startup Data |
| |
| User Defined Data : null not null |
| |
--------------------------------------------------------------------
--
(Previously Formula used the same model, EXCEPT that User Defined Data had
both columns as not null).
--
--
Creating DB Items:
-----------------
The main procedures that create/ delete database items are named xxx_dict.
These procedures share some general purpose routines to simplify the
process of creating DB items. These procedures are called:
--
insert_user_entity
insert_parameter_value
insert_database_item
--
Since they are declared in the package header, other routines that may
be written in the future to generate DB items may also reference them.
Each of the database creation procedures follow a similar layout:
--
The procedure (xxx_dict) is called with the relevent parameters (for
example the procedure to generate Element DB items is passed an element
type id and an effective date). The procedure may then retrieve further
information from the database. Then the entity is created with a call
to: 'insert_user_entity'. This defines what route the database items will
be attached to. If the route uses any where clause fillers (written as
(Ux in the route) then the procedure 'insert_parameter_value' is called
next. (If the route does not use any where clause fillers, this call is
omitted). Finally the procedure calls 'insert_database_item' for every
datbase item that is to be attached to the entity (and hence the route).
When several database items are to be created each with a different route,
then the main procedure (xxx_dict) calls the 3 routines in order, ie:
--
procedure xxx_dict (..) -- create some DB items
begin
-- get some information from the database
insert_user_entity (..);
insert_parameter_value (..); -- if the route uses where clause fillers
insert_database_item (..);
insert_database_item (..);
insert_database_item (..);
insert_user_entity (..);
insert_parameter_value (..);
insert_database_item (..);
insert_database_item (..);
insert_database_item (..);
ckesanap 115.148 25-Apr-2008 Bug 6955080. In delete_keyflex_dict, chaged the
where condition clause for deleting the
existing user_entity from ff_user_entities.
ckesanap 115.147 07-Aug-2007 Bug 5930272. Added fnd_message.retrieve() in
insert_user_entity_main() to clear the message
in the FND message queue as the error raised
in checkformat() is being handled.
ckesanap 115.146 18-Jul-2007 Bug 6215173. Passed null for l_legislation_code
parameter to create_key_flex() in
create_keyflex_dict() procedure.
divicker 115.145 22-MAY-2007 Merge exists for check_for_(tl)_dbi_clash
divicker 115.144 16-MAY-2007 Optimizer hints added for procedures
check_for_(tl)_dbi_clash
arashid 115.143 24-NOV-2006 Make dbi2ueid error message give out
more information. Also, catch
exceptions from update_xxx calls
in pay_process_dyndbi_changes and
log the errors.
Set the g_security_group_id for the
NAME_TRANSLATIONS lookup in
pay_process_dyndbi_changes, otherwise
the meaning is not translated.
Only delete PAY_DYNDBI_CHANGES row if
no errors occurred.
arashid 115.142 10-NOV-2006 Bug5464970 fix for changed
pay_dbitl_update_errors_pkg.fetch_all_rows
interface. Also, use dbms_sql.varchar_table
in process_leg_translations.
arashid 115.141 22-SEP-2006 Bug 5556728: in the MLS dbi case,
'Pay Value' was passed into
replace_code_name so the replacement was
not taking place.
divicker 115.140 01-JUN-2006 11511 branch merge
arashid 115.139 11-MAY-2006 Add code for running legislation-specific
database item translation from
process_pay_dyndbi_changes.
arashid 115.138 09-MAY-2006 Don't disable ff_user_entities delete
triggers as thay are required to delete
ff_database_items_tl rows (other child
rows are cascade deleted).
arashid 115.137 05-MAY-2006 Use substr to restrict names to 80
characters in routines that generate base
dbi names - this is to match the main
dynamic dbi name generation code. The
changes are in:
GEN_ET_BASE_DBI_NAME
GEN_DB_BASE_DBI_NAME
GEN_EIV_BASE_DBI_NAME
arashid 115.136 04-MAY-2006 Fixed infinitely looping code for
translating DATE_PAID and non-DATE_PAID
ELEMENT TYPE / INPUT VALUE database items.
Put debug into PROCESS_PAY_DYNDBI_CHANGES,
and tidied up the update error logging
code therein.
arashid 115.135 27-APR-2006 The following changes were made:
- process date-paid and non-date-paid
database items when updating _TL names.
- process_pay_dyndbi_changes code now calls
FF_DATABASE_ITEMS_PKG.UPDATE_SEEDED_TL_ROWS
and writes messages to the concurrent
processing log file.
arashid 115.134 31-MAR-2006 Changed process_pay_dyndbi_changes to not
raise assertions when entities are missing.
Instead, the condition is traced and the
PAY_DYNDBI_CHANGES row deleted.
arashid 115.133 27-JAN-2006 Changed process_pay_dyndbi_changes to a
concurrent processing interface with VARCHAR2
parameters.
arashid 115.132 11-NOV-2005 Fixed bug in new_defined_balance whereby
p_defined_balance_id was being passed
as p_balance_type_id in GEN_DB_TL_DBI_NAME.
arashid 115.131 01-NOV-2005 Made process_pay_dyndbi_changes multi-threaded.
arashid 115.130 31-OCT-2005 Changed PROCESS_PAY_DYNDBI_CHANGES to COMMIT
every 50 rows.
arashid 115.129 19-OCT-2005 Added support for fully translated dynamic
database items.
1. Added internal insert_database_item
interface with extra parameters to
help with fully translated database
items.
2. Tightened up validation for 'SEED'
condition to check against business
groups with the same legislation code.
3. Extracted code into functions for reuse:
check_for_dbi_clash
replace_code_name
uom_requires_dbis
4. Changed insert_database_item to handle
full MLS and pseudo-MLS as separate
cases. For full MLS, only a single
base DBI in ff_database_items is
generated.
5. Changed code for generating element,
input, and defined balance DBIs to
handle full MLS where it is supported.
6. Added update_xxx calls for MLS updates
to FF_DATABASE_ITEMS_TL.
Updates to FP.K and earlier should be
branched on 115.28.
alogue 115.128 07-OCT-2005 Performance fix to sel_ffci in
create_alternative_dbis. Bug 4658377.
nbristow 115.126 01-AUG-2005 Added support for OTL balances.
divicker 115.125 27-JUL-2005 add sleep for all workers bar first when
altering trigger states to prevent ora 4061
divicker 115.124 27-JUN-2005 add delimiting quotes to a text type
route parameter value 4431372
divicker 115.123 23-JUN-2005 hint update
divicker 115.122 22-JUN-2005 Add ordered hints for 2 stmts that speed up
fresh installs of hrglobal
divicker 115.121 09-JUN-2005 4363933 Add sleep to prevent too many
executions of worker complete loop
arashid 115.120 25-MAY-2005 4387272
Fixed FF_COMPILED_INFO_F delete code so
that all the affected FF_COMPILED_INFO_F
rows are deleted. The changes are made
for the partial delete cases:
delete_compiled_formula
delete_compiled_formula_priv
legislative rebuild_ele_input_bal
divicker 115.119 17-MAY-2005 Pre delete formula usages compiled info
by legislation or all if core
divicker 115.118 13-MAY-2005 Add commit points per 100 rows
Trace the worker for each main loop
divicker 115.117 12-MAY-2005 Regress back to fail and quit mode
divicker 115.116 11-MAY-2005 Fix duplicate RB user entity creation
divicker 115.115 05-MAY-2005 Add name delete for RB ue as well
divicker 115.114 21-APR-2005 add hint to del_comp_form_priv cursor
divicker 115.113 21-APR-2005 make internal calls to del_comp_form
call a more performant del_comp_form_priv
version instead when we can guarantee internal
calls will have a non-null creator id so we
can exploit the index by removing the nvl
divicker 115.112 20-APR-2005 fix issue with RB only def bal creation
divicker 115.111 31-MAR-2005 refresh def bal cursor addition and
and get_alt_route addition for 4273939
divicker 115.110 29-MAR-2005 fix to delete_compiled_formula when passed
leg_code is null even though formulae
may not be null 4262658
divicker 115.109 10-MAR-2005 fix an incorrect trace stmt
divicker 115.108 10-MAR-2005 dont eenable triggers in exception blk of
reib in order to avoid red herring mutation
erros in other drivers. instead do it in
hrrbdeib caller. Do it at beginning so both
are initially enabled if calling reib
manually
divicker 115.107 24-FEB-2005 comment change
divicker 115.106 23-FEB-2005 trace each main ID being processed
allows for much faster tracing
format is:
RDB:B,RB:leg_code.bg.defbalid,baldimid,baltype,srunbal
RELE:element_type_id.date
RELI:input_value_id.date
To rerun hrrbdeib for just one piece of data call the following
call disable trigger procs
set boolean hrdyndbi.g_triggers_altered true
for RDB: hrdyndbi.refresh_defined_balances(defbalid, 1);
divicker 115.96 21-JAN-2005 Be more selective on the formulae to
uncompile so FFXBPC can run faster
divicker 115.95 21-JAN-2005 Remove sho err
divicker 115.94 19-JAN-2005 Performance improvements
divicker 115.93 18-NOV-2004 Multithread
alogue 115.92 28-JUL-2004 Ensure quoted dbitems front last change
do not exceed 80 characters. Bug 3797888.
alogue 115.88 06-JUL-2004 Quote dbitem names that would have failed
with illegal characters. Bug 3723715.
divicker 115.87 24-JUN-2004 More mods to debug for insert route param
divicker 115.86 12-MAY-2004 Fix for 8i
divicker 115.84 06-MAY-2004 More debug info for input value
divicker 115.83 Add a load of additional debug info.
PYUPIP apps/apps HRDYNDBI
divicker 115.81 09-DEC-2003 No multi-thread
Merge changes in 115.79
Merge changes in 115.80
divicker 115.76 24-SEP-2003 Same as 115.74 (back out mthread routines)
alogue 115.74 31-JUL-2003 Bug 3073514. Replace hyphens with underscores
from flex segment names before creating
their dbitems. Change to dflex_c2.
alogue 115.73 28-MAY-2003 Bug 2977644. Fix delete_compiled_formula
to correctlt delete formula.
alogue 115.72 02-MAY-2003 Bug 2936561. Remove full stops from input
value names before ue and dbi creation.
GUI should have avoided full stops being in
input value names, but transpires accrual
code creates elements and input values
without running checkformat check. This is
really a workaround to that issue.
alogue 115.71 02-MAY-2003 Bug 2936750. Remove full stops from flex
segment names before creating their dbitems.
Change to dflex_c2;
Balance user entities get deleted.
scchakra 115.65 20-FEB-2003 Bug 2813405. Removed code to raise error
PAY_34166_DF_DBI_MULTI_OCCUR from procedures
create_dev_desc_flex_dict and
get_legislation_code.
scchakra 115.64 11-FEB-2003 Bug 2637573. Modified
create_dev_desc_flex_dict to create DB items
for routes defined in table
pay_route_to_descr_flexs. Created a new
function get_legislation_code.
divicker 115.63 03-FEB-2003 bugfix 2782128
divicker 115.62 31-JAN-2003 Ensure BG items related to legcode are also picked
up for processing in legislative striping mode
alogue 115.61 18-DEC-2002 NOCOPY changes. Bug 2692195.
Support of Competencies dbitem creation in
create_keyflex_dict.
alogue 115.60 15-NOV-2002 Changed balance dbitem definition strategy so
now only use RULE hint when LOW_VOLUME
pay_action_parameter is set to Y. Thus default
behaviour is now that balances won't have this hint.
RThirlby 115.59 15-OCT-2002 Changed new_defined_balance to default the
save_run_balance flag is it is not alreaady
set, and if a default is available.
nbristow 115.58 03-OCT-2002 Changed new_defined_balance to allow calls to
get_value.
mkandasa 115.57 03-OCT-2002 Removed the error which is raised if p_record_inserted
returns false in insert_user_entity.
mkandasa 115.56 01-OCT-2002 Substr'd user entity name to 80 chars.Raised
Error in case of duplication of user entity name.
Bug no 2073022.
divicker 115.55 24-SEP-2002 H_ instead of H_DEC
divicker 115.54 20-SEP-2002 Slight fix to change in 115.53
divicker 115.53 19-SEP-2002 Change UOM H_ support to use H_DEC%. Tidy up of string
alogue 115.51 11-SEP-2002 Remove full stops from potential dbitems. Bug 2557062.
alogue 115.50 22-AUG-2002 Remove brackets from potential dbitems. Bug 2377726.
divicker 115.49 29-JUL-2002 Leg code striping for formula
mreid 115.48 18-JUL-2002 Modify creation of rpv for Entry DDF
alogue 115.47 16-JUL-2002 Handle user entities owned by old
values of SCL legislation rule
in create_scl_flex_dict.
divicker 115.46 26-JUN-2002 Added call in create_desc_flex_main to
insert rpv for ENTRY_DESC_FLEX_ROUTE
divicker 115.45 17-JUN-2002 Added back H_% UOM support
Added route parameter for ENTRY_DESC_FLEX
route
mreid 115.44 11-JUN-2002 Added support for Entry DDF
divicker 115.43 22-MAY-2002 Back out 115.41 change until July MP
rthirlby 115.42 13-MAY-2002 Corrected delete of SRB user entities to
delete of RB user entities.
divicker 115.41 01-MAY-2002 Added H_% UOM to generate fffunc.cn calls
rthirlby 115.40 01-MAR-2002 Added procedure create_alternative_dbis, to
create user entites for run balances. NB, no
dbi is created, to avoid the possibility of
people using the wrong dbi in formulas.
Added delete of SRB balances to rebuild_ele_
input_bal procedure.
alogue 08-MAR-2001 Fix insert_database_item when passed in
user_entity_id.
alogue 12-DEC-2000 Suport of creation of dbitems in
create_dev_desc_flex_dict for Extra Location Info
DDF, Extra Position Info DDF and Extra Person Info
DDF.
alogue 23-NOV-2000 Always RULE hint for balace dbitems. Bug 1513266.
divicker 115.35 30-OCT-2000 trigger enabled check for new_defined_balance
procedure added.
divicker 115.34 30-OCT-2000 Exception handler for reenabling triggers.
Check on derived codename value and whether triggers
are disabled or not, keeping original logic if
insert_database_item, insert_user_entity not called
via rebuild_ele_input_bal.
jarthurt 115.33 28-OCT-2000 Remove enabling and disabling of triggers in
rebuild_ele_input_bal. These are being transfered
to hrrbdeib.sql.
divicker 04-OCT-2000 Perf. enhancements to rebuild_ele_input_bal
alogue 07-AUG-2000 Fix to bug fix 1271588.
alogue 28-JUN-2000 Support of BALANCE_DBITEM_TYPE for flexible
balance dbitem definition text approach.
Includes Rule Hint.
alogue 20-APR-2000 Fix issue in descriptive flex dflex_c1 cursor
to handle titles that have been passed that
contain full-stops. Bug 1271588.
alogue 16-MAR-2000 Fix issue in insert_database_item to handle
NAME_TRANSLATIONS lokkup meanings that
contain apostrophes. Bug 1210117.
tbattoo 24-FEB-2000 Bug 1207273, if a user entity alredy exists when
you insert the db item use the id for the
existing entity and not the currval in the seq
alogue 22-FEB-2000 Translated Pay Value Database Item issue.
Bug 1110849.
alogue 14-FEB-2000 Utf8 support.
alogue 28-JAN-2000 Performance fix to create_element_type.
alogue 10-NOV-1999 Fix issue in descriptive flex dflex_c1 cursor
to handle titles that have been passed that
contain apostrophes. Bug 1061472.
jmoyano 03-NOV-1999 generator for Payroll DDF added to procedure
create_dev_desc_flex_dict.
alogue 02-NOV-1999 Fix issue in delete_flexfield_dict to handle
titles that have been passed that contain
apostrophes. Bug 1058676.
alogue 05-OCT-1999 Fix issue in create_input_value to handle
scenario where some user entities already exist,
but others don't. Bug 1018544.
alogue 23-SEP-1999 Support application_ids for SSP (804),
OAD (805), HXT (808), Federal HR (8301).
alogue 22-SEP-1999 Change to reflect desc flex titles now being
stripped of apostrophes prior to being passed
in.
kkawol 14-SEP-1999 Changed create_input_value, now checking date
UOM is set to 'D' instead of 'D_%'.
alogue 04-AUG-1999 Canoncial Number fix for absence dbitems.
alogue 01-JUL-1999 Fix to exc_acc and scl dbitem creations so
that now process several legislations using
the same passed in flexfield.
alogue 09-JUN-1999 Now handles descriptive flexfields titles
that contain apostrophes ie Add'l Org Unit Details.
Bug 874129.
alogue 24-MAY-1999 Support for Cananda in creation of Org
dev flex dbis.
alogue 27-APR-1999 Change in create_input_value support
of canonical numbers.
alogue 26-APR-1999 Fixes in create_input_value to support
canonical dates and canonical numbers.
alogue 09-APR-1999 Change in new_defined_balance to support
canonical numbers in balances database items.
cborrett 04-DEC-1998 Added generation of context sensitive
dbitems in procedure CREATE_FLEXFIELD_DICT().
Replaced hardcoded routes in CREATE_FLEXFIELD_DICT()
with cursor against new table pay_route_to_descr_flexs.
alogue 08-JAN-1999 Change in create_desc_flex to create
CURRENCY_CODE dbitem for Org Pay Method
descriptive flex.
arundell 06-JAN-1999 Changes in insert_database_item to support
MLS.
alogue 02-DEC-1998 Removed application_id check on hr_lookups
within insert_database_item.
alogue 05-OCT-1998 Fix insert_user_entity check of whether entity
exists to also check it is in current bus grp
or legislation.
alogue 09-MAR-1998 Creation of overloading of insert_user_entity
so 'old' style call is supported ie called
without p_record_inserted out parameter
added by 13-JAN-1998 change.
mfender 10-FEB-1998 Bug 610203 - removed count(*) from
insert_database_item.
amills 110.11 23-JAN-1998 Bug 523343. Changed insert_database_item procedure.
Added cursor get_codename which takes the
parameter item name and splits into constituent
items where necessary for matching onto hr_lookups,
so that translation of each part can be effective.
After retrieving the translatable 'meaning',
the constituent parts are then concatenated back
to form a fully translated db item.
amyers 13-JAN-1998 Amended procedure insert_user_entitiy to:
i. only insert data if it doesn't exist,
ii. return a value in a new parameter indicating
whether the insert has happened to determine
the creation of underlying parameter values
and database items.
This change comes from bug 602851, where in an
R11 upgrade database items and entities were not
created and formulae would then not compile, so
in driver hr11gn.drv we need to run procedure
refresh_grade_spine_rates to ensure this doesn't
happen.
New version is 110.10.
amills 24-DEC-1997 Added rtrim to same select to remove full stop,
a temporary workaround for bug 603256 to
ensure no reserved words or characters are in desc
flex creation.
amills 09-DEC-1997 changed l_title to select from fnd_descriptive_
flexs_vl rather than take a hardcoded value
in create_org_pay_flex_dict
alogue 28-OCT-1997 legislation_code used in delete from ff_user_entities
in delete_keyflex_dict. Fix to bug 513364.
mreid 24-SEP-1997 Changed table_names for release 11 security.
dsaxby 15-SEP-1997 Changed substr to substrb to avoid problems with
generating NLS database items.
alogue 13-AUG-1997 Business_group_id passed to delete_keyflex_dict
to fix bug 513364.
nbristow 25-JUL-1997 Changed all references of fn_descriptive_flexs
to fnd_descriptive_flexs_vl.
mwcallag 26-APR-1995 Entity name passed to delete_keyflex_dict to
fix bug 278064.
rfine 24-NOV-1994 Suppressed index on business_group_id
mwcallag 13-OCT-1994 Route PAYROLL_ACTION_FLEXFIELD_ROUTE deleted.
rfine 05-OCT-1994 Changed call to renamed package: was us_contr_dbi,
now pay_us_contr_dbi.
mwcallag 28-JUL-1994 Optional commit points added to procedure
rebuild_ele_input_bal.
mwcallag 20-JUL-1994 It has been decided to convert the formula model
to the payroll model for User Defined Data,
thereby being consistent for Payroll and Formula.
This means that for User Defined Data only the
business group id should be populated on
ff_user_entities, not legislation code as well.
(previously both columns were populated). Refer
to the Notes above for more information. The
change dated 15-JUL-1994 is undone.
mwcallag 15-JUL-1994 procedure 'new_defined_balance' altered to
populate leg_code in ff_user_entity if the
business group id is null. (ie. payroll to
Formula startup data interface). <- temp. change.
mwcallag 13-JUN-1994 G916 Procedure 'rebuild_ele_input_bal' added.
mwcallag 07-JUN-1994 G890 Entity name for DF Element Type corrected.
mwcallag 06-JUN-1994 G867 The user entity id is no longer appended to
the user entity name when the entity is created.
This eases the startup delivery for DB items.
mwcallag 25-MAY-1994 G795 The new where clause filler of element type
id was missing from the input value route
'INPUT_VALUE_ENTRY_LEVEL' for multiple
entries allowed input values.
mwcallag 29-APR-1994 Element type id context added for the route:
INPUT_VALUE_ENTRY_LEVEL to improve performance.
mwcallag 28-FEB-1994 Database names changed from '%ASS_%' to '%ASG_%'.
mwcallag 20-JAN-1994 Legislation code passed to delete_keyflex_dict,
procedure delete_compiled_formula added (G516).
mwcallag 11-JAN-1994 The title of the Element DF changed from 'Element
Developer DF' to 'Further Element Information'.
mwcallag 09-DEC-1993 G334 For element or input values DB items, if the
legislation code is null on the base table it
is derived from per_business_groups.
mwcallag 08-DEC-1993 G323 Context name is now used in the entity name
(together with the title) for all descriptive
flex DB items.
mwcallag 07-DEC-1993 G291 Change to Legal Company DB items.
mwcallag 30-NOV-1993 G259 procedure insert_parameter_value corrected to
properly handle multiple where clause fillers.
mwcallag 29-NOV-1993 G221 Improved handling for long database item
names.
mwcallag 23-NOV-1993 G161 Simplified the calls to generate DB items for
external use. Element DDF DB item now gets the
legislation code from per_business_groups if the
legislation code is null and business_group_id
is present on the element type table. (The
legislation code concatenated with the element
classification is used as the context code in the
AOL descriptive flex tables).
mwcallag 03-NOV-1993 Assignment Developer Descriptive flex DB items
added.
mwcallag 02-NOV-1993 ********************************
* DIVERGENCE FROM FROZEN CODE *
********************************
Input Value DB item creation now tests the
multiple entries allowed flag, rather than the
recurring flag. Developer descriptive flex
DB items for elements and jobs added.
Benefit classification DB items added.
mwcallag 26-OCT-1993 Sum function in definition text for non-recurring
input values moved to outer parenthesis to stop
sql retrival error.
mwcallag 28-SEP-1993 pay_name_translations reference replaced with
hr_lookups in insert_database_item procedure,
also passed parameter of legislation code removed
since it is no longer used.
abraae 09-SEP-1993 strip blanks from DB Item defn text to fit into
ff_database_items.definition_text (char(240))
mwcallag 08-SEP-1993 Input value definition text modified to include a
decode on the UOM to avoid problem in formula when
several DB items of different user definable
data types are retrieved in 1 formula cursor.
mwcallag 01-SEP-1993 Procedure for converting element DB items from the
context of date earned to date paid added. Enable
checks added to cursors in SCL and descriptive
flex routines.
mwcallag 23-AUG-1993 More DB items for Descriptive flexfields added,
plus Organization payment methods, external
accounts and legal company SCL DB items.
mwcallag 03-AUG-1993 Developer Descriptive flexfield and SCL flexfield
procedures added.
mwcallag 27-JUL-1993 Passed parameter name to Key flexfield DB items
now use the short names of GRP, GRD, POS JOB.
mwcallag 20-JUL-1993 Not found flags set to yes, this stops quickpaint
error on an assignment with minimal information.
Dummy group function added to recurring input
values to stop formula error (see input value
code below for more information).
mwcallag 18-JUN-1993 Descriptive and key flex deletion routines now
delete compilied DB items from ff_fdi_usages_f
mwcallag 14-JUN-1993 Application id removed from both
delete_flexfield_dict and create_flexfield_dict
mwcallag 03-JUN-1993 Create descriptive and key flexfield routines
delete old flexfields before creation attempted.
mwcallag 26-MAY-1993 Creator types changed to reflect database change.
mwcallag 24-MAY-1993 'rate_type' DB item removed from grade rate
creation. (Bug 160305 rejected for rel. 10).
mwcallag 07-MAY-1993 Spine DB creation added to grade procedure.
DB creation procedure for key flexfield.
mwcallag 30-APR-1993 Grade rates extended, descriptive flexs and
absence types added.
mwcallag 26-APR-1993 Procedures for input values, element types
and grade rate database items added.
Abraae 06-APR-1993 Created.
*/
--
-- Translations Data Structures
--
type r_dbi_prefix is record
(language varchar2(30)
,found boolean
,prefix varchar2(240)
);
SELECT DFC.descriptive_flexfield_name c_flex_name,
replace (replace (replace (ltrim(rtrim(upper(DF.title))),
' ','_'),'''',''),'.','_') c_title,
DFC.created_by c_created_by,
DFC.last_update_login c_last_login
FROM fnd_descriptive_flexs_vl DF,
fnd_descr_flex_contexts DFC
WHERE DF.application_table_name = p_table_name
AND replace (ltrim (rtrim(DF.title)), '''','') = replace(p_title,'''','')
AND DF.application_id IN (800, 801, 804, 805, 808, 8301)
AND DF.descriptive_flexfield_name = DFC.descriptive_flexfield_name
AND DFC.enabled_flag = 'Y'
AND DFC.global_flag = p_global_flag
AND DFC.application_id IN (800, 801, 804, 805, 808, 8301)
AND DFC.descriptive_flex_context_code = p_context;
SELECT DFCU.application_column_name c_def_text,
replace(replace(replace (ltrim(rtrim(upper(DFCU.end_user_column_name))),
' ','_'),'.',''),'-','_') c_db_name
FROM fnd_descr_flex_column_usages DFCU
WHERE DFCU.descriptive_flexfield_name = p_descr_flex
AND DFCU.application_id IN (800, 801, 804, 805, 808, 8301)
AND DFCU.descriptive_flex_context_code = p_context
AND DFCU.enabled_flag = 'Y';
PROCEDURE insert_mthread_pps (p_stage number,
p_worker_id number,
p_leg_code varchar2 default 'ZZ')
IS
PRAGMA AUTONOMOUS_TRANSACTION;
insert into PAY_PATCH_STATUS(id,
patch_number,
patch_name,
process_type,
applied_date)
values (
pay_patch_status_s.nextval,
to_char(p_worker_id),
'HRRBDEIB INTERNAL PROC S' || to_char(p_stage),
p_leg_code,
sysdate);
END insert_mthread_pps;
PROCEDURE insert_mthread_pps_err (p_worker_id number,
p_leg_code varchar2 default 'ZZ')
IS
PRAGMA AUTONOMOUS_TRANSACTION;
insert into PAY_PATCH_STATUS(id,
patch_number,
patch_name,
process_type,
applied_date)
values (
pay_patch_status_s.nextval,
to_char(p_worker_id),
'HRRBDEIB INTERNAL PROC ERR' || to_char(p_worker_id),
p_leg_code,
sysdate);
END insert_mthread_pps_err;
procedure update_tl_dbi_name
(p_user_name in varchar2
,p_user_entity_id in number
,p_leg_code in varchar2
,p_bg_id in number
,p_startup_mode in varchar2
,p_language in varchar2
,p_tl_user_name in varchar2
);
procedure update_et_tl_dbi_names
(p_leg_code in varchar2
,p_bg_id in number
,p_startup_mode in varchar2
,p_user_name in varchar2
,p_user_entity_id in number
,p_prefixes in t_dbi_prefixes
,p_suffix in varchar2
,p_date_p in varchar2
);
procedure delete_compiled_formula_priv
(
p_creator_id in number,
p_creator_type in varchar2,
p_user_entity_name in varchar2,
p_leg_code in varchar2
);
select ff_user_entities_s.nextval
into p_user_entity_id
from sys.dual;
insert into ff_user_entities
(user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description)
select
p_user_entity_id,
p_business_group_id,
p_legislation_code,
p_route_id,
p_notfound_flag,
p_db_item_name,
p_defined_balance_id,
p_creator_type,
p_description
from dual
where not exists (
select null
from ff_user_entities a
where a.user_entity_name = p_db_item_name
and
( p_startup_mode = 'MASTER'
or
( p_startup_mode = 'SEED'
and
( a.legislation_code = p_legislation_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_legislation_code =
(
select b.legislation_code
from per_business_groups_perf b
where b.business_group_id = a.business_group_id
)
)
)
or
( p_startup_mode = 'NON-SEED'
and
( a.business_group_id = p_business_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_legislation_code)
)
)
));
select ff_user_entities_s.nextval
into p_user_entity_id
from sys.dual;
insert into ff_user_entities
(user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description)
values (
p_user_entity_id,
p_business_group_id,
p_legislation_code,
p_route_id,
p_notfound_flag,
p_db_item_name,
p_defined_balance_id,
p_creator_type,
p_description
);
insert into ff_database_items
(user_name
,user_entity_id
,data_type
,definition_text
,null_allowed_flag
,description
)
values
(p_db_item_name
,p_user_entity_id
,p_datatype
,p_definition
,p_null_allowed
,p_description
);
select pdr.route_id,
pdr.route_type,
pdr.run_dimension_id,
pdr.priority
from pay_dimension_routes pdr
where pdr.balance_dimension_id = p_bal_dim
and not exists (select null
from ff_user_entities u
where u.creator_id = p_defined_balance_id
and u.creator_type = 'RB'
and u.route_id = pdr.route_id)
order by pdr.balance_dimension_id, pdr.priority;
select /*+ ORDERED
INDEX(a FF_USER_ENTITIES_N50)
INDEX(fdbi FF_DATABASE_ITEMS_FK1)
INDEX(fdi FF_USER_ENTITIES_N50)
USE_NL(a fdbi fdi) */
formula_id
from
ff_user_entities a,
ff_database_items fdbi,
ff_fdi_usages_f fdi
where fdi.usage = 'D'
and fdi.item_name = fdbi.user_name
and fdbi.user_entity_id = a.user_entity_id
and a.user_entity_name = p_dbi_item_name
and
( p_startup_mode = 'MASTER'
or
( p_startup_mode = 'SEED'
and
( a.legislation_code = p_legislation_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_legislation_code =
(
select b.legislation_code
from per_business_groups_perf b
where b.business_group_id = a.business_group_id
)
)
)
or
( p_startup_mode = 'NON-SEED'
and
( a.business_group_id = p_business_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_legislation_code)
)
)
);
delete ff_fdi_usages_f where formula_id = r_sel_ffci.formula_id;
delete ff_compiled_info_f where formula_id = r_sel_ffci.formula_id;
delete ff_user_entities a
where a.user_entity_name = l_db_item_name
and
( p_startup_mode = 'MASTER'
or
( p_startup_mode = 'SEED'
and
( a.legislation_code = p_legislation_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_legislation_code =
(
select b.legislation_code
from per_business_groups_perf b
where b.business_group_id = a.business_group_id
)
)
)
or
( p_startup_mode = 'NON-SEED'
and
( a.business_group_id = p_business_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_legislation_code)
)
)
);
insert into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value)
select RP.route_parameter_id,
usr_ent_id,
to_char(p_balance_type_id)
from ff_route_parameters RP
where RP.route_id = rrrec.route_id
and RP.sequence_no = 1;
insert into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value)
select RP.route_parameter_id,
usr_ent_id,
to_char(rrrec.run_dimension_id)
from ff_route_parameters RP
where RP.route_id = rrrec.route_id
and RP.sequence_no = 2;
* trigger on insert of defined balance, or from the refresh routine.
*/
procedure new_defined_balance (p_defined_balance_id in number,
p_balance_dimension_id in number,
p_balance_type_id in number,
p_business_group_id in number,
p_legislation_code in varchar2) is
--
cursor c_language is
select language_code
from fnd_languages
where installed_flag in ('I','B');
select count(*)
from pay_defined_balances
where defined_balance_id = p_def_bal
and save_run_balance is null;
select balance_category_id
from pay_balance_types
where balance_type_id = p_bal_type;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select bg.legislation_code
into l_legislation_code
from per_business_groups_perf bg
where bg.business_group_id = p_business_group_id
;
update pay_defined_balances
set save_run_balance = l_run_bal_flag
where defined_balance_id = p_defined_balance_id;
hr_utility.trace('update pay_def_bal.save_run_balance:def_bal_id:' || to_char(p_defined_balance_id) ||
'to save_run_balance:' || nvl(l_run_bal_flag, 'NULL') || ':');
end if; -- flag is null, so dont both to update
select BALDIM.route_id,
nvl(database_item_function, 'N'),
upper(replace(BALTYPE.balance_name || BALDIM.database_item_suffix,
' ','_'))
into l_route_id,
l_dbi_function,
l_db_item_name
from pay_balance_dimensions BALDIM,
pay_balance_types BALTYPE
where BALDIM.balance_dimension_id = p_balance_dimension_id
and BALTYPE.balance_type_id = p_balance_type_id;
insert into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value)
select RP.route_parameter_id,
usr_ent_id,
to_char(route_param_value)
from ff_route_parameters RP
where RP.route_id = l_route_id
and RP.sequence_no = 1;
select parameter_value
into g_low_volume
from pay_action_parameters
where parameter_name = 'LOW_VOLUME';
update_tl_dbi_name
(p_user_name => l_db_item_name
,p_user_entity_id => usr_ent_id
,p_leg_code => p_legislation_code
,p_bg_id => p_business_group_id
,p_startup_mode => startup_mode
,p_language => l.language_code
,p_tl_user_name => l_tl_name
);
cursor c1 is select defined_balance_id,
balance_dimension_id,
balance_type_id,
business_group_id,
legislation_code,
save_run_balance
from pay_defined_balances b
where not exists (
select null from ff_user_entities u
where b.defined_balance_id = u.creator_id
and u.creator_type = 'B')
and mod(defined_balance_id, p_maxworkers) = p_worker_id
order by b.defined_balance_id;
cursor c2 is select defined_balance_id,
balance_dimension_id,
balance_type_id,
business_group_id,
legislation_code,
save_run_balance
from pay_defined_balances b
where /* def bal ue simply doesn't exist but has an associated pdr */
((
not exists (
select null from ff_user_entities u
where b.defined_balance_id = u.creator_id
and u.creator_type = 'RB')
and exists
(select null
from pay_dimension_routes pdr
where pdr.balance_dimension_id = b.balance_dimension_id)
)
OR /* def bal ue does exists but has a missing ue pdr */
(
exists (
select pdr.balance_dimension_id
from pay_dimension_routes pdr
where pdr.balance_dimension_id = b.balance_dimension_id
and not exists (select null
from ff_user_entities ue
where ue.creator_id = b.defined_balance_id
and ue.route_id = pdr.route_id
and ue.creator_type = 'RB'))
))
and mod(defined_balance_id, p_maxworkers) = p_worker_id
order by b.defined_balance_id;
select upper(replace(BALTYPE.balance_name ||
BALDIM.database_item_suffix,
' ','_'))
into l_db_item_name
from pay_balance_dimensions BALDIM,
pay_balance_types BALTYPE
where BALDIM.balance_dimension_id = c2rec.balance_dimension_id
and BALTYPE.balance_type_id = c2rec.balance_type_id;
delete ff_user_entities
where creator_id = c2rec.defined_balance_id
and creator_type = 'RB';
cursor c1 is select defined_balance_id,
balance_dimension_id,
balance_type_id,
business_group_id,
legislation_code,
save_run_balance
from pay_defined_balances a
where not exists (
select null from ff_user_entities u
where a.defined_balance_id = u.creator_id
and u.creator_type = 'B')
and mod(defined_balance_id, p_maxworkers) = p_worker_id
and (a.legislation_code = p_leg_code
or exists (select null
from per_business_groups_perf b
where a.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code))
order by a.defined_balance_id;
cursor c2 is select defined_balance_id,
balance_dimension_id,
balance_type_id,
business_group_id,
legislation_code,
save_run_balance
from pay_defined_balances a
where /* def bal ue simply doesn't exist but has an associated pdr */
((
not exists (
select null from ff_user_entities u
where a.defined_balance_id = u.creator_id
and u.creator_type = 'RB')
and exists
(select null
from pay_dimension_routes pdr
where pdr.balance_dimension_id = a.balance_dimension_id)
)
OR /* def bal ue does exists but has a missing ue pdr */
(
exists (
select pdr.balance_dimension_id
from pay_dimension_routes pdr
where pdr.balance_dimension_id = a.balance_dimension_id
and not exists (select null
from ff_user_entities ue
where ue.creator_id = a.defined_balance_id
and ue.route_id = pdr.route_id
and ue.creator_type = 'RB'))
))
and mod(defined_balance_id, p_maxworkers) = p_worker_id
and (a.legislation_code = p_leg_code
or exists (select null
from per_business_groups_perf b
where a.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code))
order by a.defined_balance_id;
select upper(replace(BALTYPE.balance_name ||
BALDIM.database_item_suffix,
' ','_'))
into l_db_item_name
from pay_balance_dimensions BALDIM,
pay_balance_types BALTYPE
where BALDIM.balance_dimension_id = c2rec.balance_dimension_id
and BALTYPE.balance_type_id = c2rec.balance_type_id;
delete ff_user_entities
where creator_id = c2rec.defined_balance_id
and creator_type = 'RB';
* This routine deletes and creates database items based on a given
* defined balance in the system.
*/
procedure recreate_defined_balance(p_defined_balance_id in number,
p_balance_dimension_id in number,
p_balance_type_id in number,
p_business_group_id in number,
p_legislation_code in varchar2)
is
begin
--
-- delete_compiled_formula_priv(p_defined_balance_id, 'B', '%', p_legislation_code);
delete from ff_user_entities
where creator_id = p_defined_balance_id
and creator_type = 'B';
delete from ff_user_entities
where creator_id = p_defined_balance_id
and creator_type = 'RB';
insert_parameter_value - insert the entity value into the route parameter
table
DESCRIPTION
This routine is called if the routes contains where clause fillers (Ux).
Get the route parameter id from the ff_route_parameters table and insert
the actual entity value into the ff_route_parameter_values table. For
example, when an element type is created, the value inserted into the
ff_route_parameter_values table is the element type id.
The parameters passed are:
p_value - the actual where clause filler value
p_sequence_no - the number of the where clause filler, eg. 1 for U1.
*/
procedure insert_parameter_value
(
p_value in varchar2,
p_sequence_no in number
) is
l_route_parameter_id number;
l_last_login ff_user_entities.last_update_login%type;
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
select RPARAM.route_parameter_id,
ENTITY.created_by,
ENTITY.last_update_login,
ENTITY.user_entity_name
into l_route_parameter_id,
l_created_by,
l_last_login,
l_user_entity_name
from ff_user_entities ENTITY
, ff_route_parameters RPARAM
where ENTITY.user_entity_id = l_user_entity_id
and RPARAM.route_id = ENTITY.route_id
and RPARAM.sequence_no = p_sequence_no;
hr_utility.trace('insert param val');
select ENTITY.user_entity_name,
ENTITY.route_id,
ENTITY.BUSINESS_GROUP_ID,
ENTITY. LEGISLATION_CODE
into l_user_entity_name,
l_route_id,
l_ent_bg_id,
l_ent_lc
from ff_user_entities ENTITY
where ENTITY.user_entity_id = l_user_entity_id;
select route_name,
to_char(CREATION_DATE, 'DD-MM-YYYY'),
to_char(LAST_UPDATE_DATE, 'DD-MM-YYYY'),
LAST_UPDATED_BY
into l_route_name,
l_route_cd,
l_route_lud,
l_route_lub
from ff_routes
where route_id = l_route_id;
insert into ff_route_parameter_values (
user_entity_id,
route_parameter_id,
value,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
--
values (l_user_entity_id,
l_route_parameter_id,
p_value,
sysdate,
l_created_by,
l_last_login,
l_created_by,
sysdate);
END insert_parameter_value;
insert_database_item - load the database item.
--
DESCRIPTION
Internal interface for insert_database_item.
This is the external insert_database_item but with extra parameters
extra parameters so that only a single base database item is
created.
If P_FULL_MLS is true then this call just creates a single base
database item whose name is returned in P_BASE_DBI_NAME. If
P_FULL_MLS is false then this call performs the old pseudo-MLS
generation of multiple database items in FF_DATABASE_ITEMS, and
P_BASE_DBI_NAME is ignored.
*/
procedure insert_database_item
(
p_entity_name in varchar2,
p_item_name in varchar2,
p_data_type in varchar2,
p_definition_text in varchar2,
p_null_allowed_flag in varchar2,
p_description in varchar2,
p_user_entity_id in number,
p_full_mls in boolean,
p_base_dbi_name out nocopy varchar2
) is
l_item_name ff_database_items.user_name%type;
l_last_login ff_user_entities.last_update_login%type;
select language_code
from fnd_languages
where installed_flag in ('I','B');
SELECT 'Y'
FROM ff_database_items
WHERE user_name = c_user_name
AND user_entity_id = c_user_entity_id;
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
select created_by,
last_update_login,
business_group_id,
legislation_code
into l_created_by,
l_last_login,
bg_id,
leg_code
from ff_user_entities
where user_entity_id = l_user_entity_id;
insert into ff_database_items
(user_name
,user_entity_id
,data_type
,definition_text
,null_allowed_flag
,description
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(l_user_name
,l_user_entity_id
,p_data_type
,p_definition_text
,p_null_allowed_flag
,p_description
,sysdate
,l_created_by
,l_last_login
,l_created_by
,sysdate
);
insert into ff_database_items
(user_name
,user_entity_id
,data_type
,definition_text
,null_allowed_flag
,description
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(l_user_name
,l_user_entity_id
,p_data_type
,p_definition_text
,p_null_allowed_flag
,p_description
,sysdate
,l_created_by
,l_last_login
,l_created_by
,sysdate
);
END insert_database_item;
insert_database_item - load the database item.
--
DESCRIPTION
Insert a new row into the database_items table. The actual database
name is a concatenation of the supplied entity name and the database
name as:
--
_
--
The parameter is checked to see if its name translation is
to be used. This is now done for each installed language (the c_language
cursor loop), to maintain the current Japanese functionality of using
the name translations lookup to translation JP-specific elements into
Japanese. This functionality leads to pseudo-translated multilingual
DEH DBIs, and currently does nothing with the translation table. For
this multilanguage translation to
work, we can not access the data through the single language views,
either HR_LOOKUPS or FND_LOOKUPS, but instead have to access the base table
directly, filtering by the required language.
The parameters passed are:
p_entity_name - The first half of the database name
p_item_name - The second half of the database name
p_data_type - Data type T = text, N = number, D = date.
p_definition_text - The text after the 'select' statment and before
the 'from' that is used to retrieve the data.
p_null_allowed_flag - Y or N, can the database item be null.
p_description - The description of the database item
*/
procedure insert_database_item
(
p_entity_name in varchar2,
p_item_name in varchar2,
p_data_type in varchar2,
p_definition_text in varchar2,
p_null_allowed_flag in varchar2,
p_description in varchar2,
p_user_entity_id in number
) is
l_base_dbi_name varchar2(2000);
insert_database_item
(
p_entity_name => p_entity_name,
p_item_name => p_item_name,
p_data_type => p_data_type,
p_definition_text => p_definition_text,
p_null_allowed_flag => p_null_allowed_flag,
p_description => p_description,
p_user_entity_id => p_user_entity_id,
p_full_mls => false,
p_base_dbi_name => l_base_dbi_name
);
end insert_database_item;
insert_user_entity - load the element type information into user
entity and route parameter value tables
--
DESCRIPTION
This is the first general purpose routine that should be called by the
main procedures (xxx_dict). It defines an entity, that the other
procedures ('insert_parameter_value' and 'insert_database_item')
reference.
NB insert_user_entity_main is overloaded by 2 insert_user_entity
procedures: one that is passed the out parameter p_record_inserted
and one that uses its own local p_record_inserted paramter (whose
values is subsequently ignored).
The parameters passed are:
p_route_name - the route name to be used, this must already be
seeded in the table ff_routes.
p_user_entity_name - The name of the user entity.
p_entity_description- The description of the entity.
p_not_found_flag - Y/ N, Y= the database item may not be found in
some cases, N= it will ALWAYS return a value.
p_creator_type - Indicates which type of DB items are to be
created, eg. E = element type.
p_creator_id - Further defines who created the DB items, for
example, element type id.
p_business_group_id - If this is null, the item may be seen by all
business groups.
p_legislation_code - The legisaltion code, null = generic.
p_created_by - Used in the AOL columns of the database.
p_last_login - Used in the AOL columns of the database.
p_record_inserted - Boolean indicating whether insertion happened
*/
--
-- insert_user_entity called with p_record_inserted
--
procedure insert_user_entity
(
p_route_name in varchar2,
p_user_entity_name in varchar2,
p_entity_description in varchar2,
p_not_found_flag in varchar2,
p_creator_type in varchar2,
p_creator_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_created_by in number,
p_last_login in number,
p_record_inserted out nocopy boolean
) is
l_user_entity_name ff_user_entities.user_entity_name%type;
insert_user_entity_main( p_route_name,
l_user_entity_name,
p_entity_description,
p_not_found_flag,
p_creator_type,
p_creator_id,
p_business_group_id,
p_legislation_code,
p_created_by,
p_last_login,
p_record_inserted);
END insert_user_entity;
procedure insert_user_entity
(
p_route_name in varchar2,
p_user_entity_name in varchar2,
p_entity_description in varchar2,
p_not_found_flag in varchar2,
p_creator_type in varchar2,
p_creator_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_created_by in number,
p_last_login in number
) is
l_record_inserted boolean;
insert_user_entity_main( p_route_name,
p_user_entity_name,
p_entity_description,
p_not_found_flag,
p_creator_type,
p_creator_id,
p_business_group_id,
p_legislation_code,
p_created_by,
p_last_login,
l_record_inserted);
END insert_user_entity;
procedure insert_user_entity_main
(
p_route_name in varchar2,
p_user_entity_name in varchar2,
p_entity_description in varchar2,
p_not_found_flag in varchar2,
p_creator_type in varchar2,
p_creator_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_created_by in number,
p_last_login in number,
p_record_inserted out nocopy boolean
) is
l_route_id number;
SELECT ff_user_entities_s.nextval
INTO l_user_entities_seq
FROM dual;
select count(*)
into l_dummy_fetch_var
from ff_user_entities
where user_entity_name = l_user_entity_name
and nvl (legislation_code, ' ') = nvl (p_legislation_code, ' ')
and nvl (business_group_id, -1) = nvl (p_business_group_id, -1);
SELECT route_id
INTO l_route_id
FROM ff_routes
WHERE route_name = p_route_name;
hr_utility.trace('insert_user_entity_main : missing route : ' ||
p_route_name);
insert into ff_user_entities (
user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
select l_user_entities_seq,
p_business_group_id,
p_legislation_code,
l_route_id,
p_not_found_flag,
l_user_entity_name,
p_creator_id,
p_creator_type,
p_entity_description,
sysdate,
p_created_by,
p_last_login,
p_created_by,
sysdate
from dual
where not exists (
select null
from ff_user_entities a
where a.user_entity_name = l_user_entity_name
and
( startup_mode = 'MASTER'
or
( startup_mode = 'SEED'
and
( a.legislation_code = p_legislation_code
or
(a.legislation_code is null and a.business_group_id is null)
or
p_legislation_code =
(
select b.legislation_code
from per_business_groups_perf b
where b.business_group_id = a.business_group_id
)
)
)
or
( startup_mode = 'NON-SEED'
and
( a.business_group_id = p_business_group_id
or
(a.legislation_code is null and a.business_group_id is null)
or
(a.business_group_id is null and a.legislation_code = p_legislation_code)
)
)
));
insert into ff_user_entities (
user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values (l_user_entities_seq,
p_business_group_id,
p_legislation_code,
l_route_id,
p_not_found_flag,
l_user_entity_name,
p_creator_id,
p_creator_type,
p_entity_description,
sysdate,
p_created_by,
p_last_login,
p_created_by,
sysdate);
p_record_inserted := TRUE;
hr_utility.trace('insert ff_user_entities: ue: ' || l_user_entity_name);
hr_utility.trace('insert ff_user_entities: route_name: ' || p_route_name);
p_record_inserted := FALSE;
END insert_user_entity_main;
delete_compiled_formula - Delete any compiled formula references
based on a user entity.
-- this version is for when we can't guarantee that the creator id passed
-- in is not null and as such will use a less efficient index. It also
-- ensures that we don't destabailise outside APIs calling into this proc
-- with a potentially null creator_id
-- For internal calls from this package where we know the creator id is
-- not null we will call into a _priv version of this routine.
DESCRIPTION
This routine is called before certain database items are deleted to
remove all compiled references to them (eg. Descriptive flexs). Since
the user creates Descriptive flex database items from a concurrent
program which could be run several times, it is necessary to delete them
before the re-creation.
*/
procedure delete_compiled_formula
(
p_creator_id in number,
p_creator_type in varchar2,
p_user_entity_name in varchar2,
p_leg_code in varchar2
) is
--
cursor get_formula_ids
is
select /* INDEX(fdi FF_FDI_USAGES_F_N50)*/
distinct fdi.formula_id,
fdi.effective_start_date
from
ff_user_entities ent,
ff_database_items dbi,
ff_fdi_usages_f fdi
where fdi.item_name = dbi.user_name
and fdi.usage = 'D'
and ent.user_entity_id = dbi.user_entity_id
and ent.creator_type = p_creator_type
and ent.user_entity_name like p_user_entity_name
and (nvl (ent.legislation_code, ' ') = nvl (p_leg_code, ' ')
or exists (select null from per_business_groups_perf b
where ent.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code))
and nvl(ent.creator_id, -1) = nvl(nvl(p_creator_id, ent.creator_id), -1);
select count(*)
into num
from ff_formulas_f f
where form.formula_id = f.formula_id
and form.effective_start_date = f.effective_start_date
and (nvl (f.legislation_code, ' ') = nvl (p_leg_code, nvl (f.legislation_code, ' '))
or exists (select null from per_business_groups_perf b
where f.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code));
delete from ff_fdi_usages_f fdi
where fdi.formula_id = form.formula_id
and form.effective_start_date = fdi.effective_start_date;
delete from ff_compiled_info_f fci
where fci.formula_id = form.formula_id
and form.effective_start_date = fci.effective_start_date;
END delete_compiled_formula;
delete_compiled_formula_priv - Delete any compiled formula references
based on a user entity. Performant version
--
DESCRIPTION
This routine is called before certain database items are deleted to
remove all compiled references to them (eg. Descriptive flexs). Since
the user creates Descriptive flex database items from a concurrent
program which could be run several times, it is necessary to delete them
before the re-creation.
Private version for performance. See note on del_comp_form for more info
*/
procedure delete_compiled_formula_priv
(
p_creator_id in number,
p_creator_type in varchar2,
p_user_entity_name in varchar2,
p_leg_code in varchar2
) is
--
l_formula_ids dbms_sql.number_table;
delete ff_fdi_usages_f fdi
where FDI.usage = 'D'
and exists (select null
from ff_formulas_f f
where fdi.formula_id = f.formula_id
and (nvl (f.legislation_code, ' ') = nvl (p_leg_code, nvl (f.legislation_code, ' '))
or exists (select null from per_business_groups_perf b
where f.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code)))
and exists (select null from
ff_database_items dbi
where fdi.item_name = dbi.user_name
and exists (select /*+ INDEX(ent FF_USER_ENTITIES_N51)*/ null from
ff_user_entities ent
where ent.user_entity_id = dbi.user_entity_id
and ent.creator_id = p_creator_id
and ent.creator_type = p_creator_type
and ent.user_entity_name like p_user_entity_name
and (nvl (ent.legislation_code, ' ') = nvl (p_leg_code, ' ')
or exists (select null from per_business_groups_perf b
where ent.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code))
))
returning fdi.formula_id, fdi.effective_start_date
bulk collect into l_formula_ids, l_start_dates
;
END delete_compiled_formula_priv;
Bulk delete FF_COMPILED_INFO_F using FORMULA_ID and
EFFECTIVE_START_DATE from FF_FDI_USAGES_F.
*/
procedure ff_compiled_info_del
(p_formula_ids dbms_sql.number_table
,p_start_dates dbms_sql.date_table
) is
l_iterations number;
delete
from ff_compiled_info_f
where formula_id = p_formula_ids(j)
and effective_start_date = p_start_dates(j)
;
delete_element_type_dict - delete an element type from the data
dictionary
--
DESCRIPTION
*/
procedure delete_element_type_dict
(
p_element_type_id in number
) is
--
BEGIN
-- delete_compiled_formula_priv(p_element_type_id, 'E', '%', null);
DELETE FROM ff_user_entities
WHERE creator_id = p_element_type_id
AND creator_type = 'E';
END delete_element_type_dict;
route parameter values, and then inserting each database items for that
particular route.
The database items created use the element type name. This routine
generates the following database items:
--
_REPORTING_NAME
_PRIMARY_CLASSIFICATION
_INPUT_CURRENCY_CODE
_OUTPUT_CURRENCY_CODE
_PROCESSING_PRIORITY
_CLOSED_FOR_ENTRY
_CLOSED_FOR_ENTRY_CODE
_END_DATE
_QUALIFYING_LENGTH_OF_SERVICE
_QUALIFYING_UNITS
_QUALIFYING_UNITS_CODE
_QUALIFYING_AGE
_STANDARD_LINK
_STANDARD_LINK_CODE
_COSTABLE_TYPE
_COSTABLE_TYPE_CODE
_COUNT
*/
procedure create_element_type
(
p_element_type_id in number,
p_effective_date in date,
p_date_p in varchar2
) is
cursor get_title is
select title from fnd_descriptive_flexs_vl
where descriptive_flexfield_name = 'Element Developer DF'
and application_id = 801;
select language_code
from fnd_languages
where installed_flag in ('I','B');
l_record_inserted boolean;
select replace (ltrim (rtrim (upper (ETYPE.element_name))), ' ', '_'),
ETYPE.business_group_id,
ltrim(rtrim(ETYPE.legislation_code)),
ETYPE.benefit_classification_id,
upper (CLASS.classification_name),
ETYPE.created_by,
ETYPE.last_update_login
into l_element_name,
l_business_group_id,
l_legislation_code,
l_benefit_class_id,
l_class_name,
l_created_by,
l_last_login
from pay_element_types_f ETYPE
, pay_element_classifications CLASS
where ETYPE.element_type_id = p_element_type_id
and p_effective_date between ETYPE.effective_start_date
and ETYPE.effective_end_date
and CLASS.classification_id = ETYPE.classification_id;
select bg.legislation_code
into l_legislation_code1
from per_business_groups_perf bg
where bg.business_group_id = l_business_group_id
;
insert_user_entity (l_route1_name,
l_element_name || '_E1' || p_date_p,
'entity for '|| l_route1_name,
'Y', -- not found allowed flag
'E',
p_element_type_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_element_type_id, 1);
insert_database_item (l_element_name,
'REPORTING_NAME' || p_date_p,
'T', -- data type
'ETYPE.reporting_name',
'Y', -- null allowed
'reporting name for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'REPORTING_NAME'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'CLASSIFICATION' || p_date_p,
'T', -- data type
'ECLASS.classification_name',
'N', -- null allowed
'primary classification name for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'CLASSIFICATION'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'INPUT_CURRENCY_CODE' || p_date_p,
'T', -- data type
'ETYPE.input_currency_code',
'Y', -- null allowed
'input currency code for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'INPUT_CURRENCY_CODE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'OUTPUT_CURRENCY_CODE' || p_date_p,
'T', -- data type
'ETYPE.output_currency_code',
'Y', -- null allowed
'output currency code for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'OUTPUT_CURRENCY_CODE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'PROCESSING_PRIORITY' || p_date_p,
'N', -- data type
'ETYPE.processing_priority',
'N', -- null allowed
'processing priority for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'PROCESSING_PRIORITY'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'CLOSED_FOR_ENTRY' || p_date_p,
'T', -- data type
'CELOOK.meaning',
'N', -- null allowed
'closed for entry flag meaning from the lookup table for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'CLOSED_FOR_ENTRY'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'CLOSED_FOR_ENTRY_CODE' || p_date_p,
'T', -- data type
'ETYPE.closed_for_entry_flag',
'N', -- null allowed
'closed for entry flag - Y or N, for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'CLOSED_FOR_ENTRY_CODE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'END_DATE' || p_date_p,
'D', -- data type
'ETYPE.effective_end_date',
'N', -- null allowed
'effective end date for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'END_DATE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'BEN_CLASS' || p_date_p,
'T', -- data type
'BCLASS.benefit_classification_name',
'N', -- null allowed
'The element''s benefit classification',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'BEN_CLASS'
,p_date_p => p_date_p
);
insert_user_entity (l_route2_name,
l_element_name || '_E2' || p_date_p,
'entity for '|| l_route2_name,
'Y', -- not found allowed flag
'E',
p_element_type_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_element_type_id, 1);
insert_database_item
(l_element_name,
'LENGTH_OF_SERVICE' || p_date_p,
'N', -- data type
'nvl (ELINK.qualifying_length_of_service, ETYPE.qualifying_length_of_service)',
'Y', -- null allowed
'qualifying length of service for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'LENGTH_OF_SERVICE'
,p_date_p => p_date_p
);
insert_database_item
(l_element_name,
'QUALIFYING_UNITS' || p_date_p,
'T', -- data type
'nvl(hr_general.decode_lookup(''QUALIFYING_UNITS'',ELINK.QUALIFYING_UNITS),hr_general.decode_lookup(''QUALIFYING_UNITS'',ETYPE.QUALIFYING_UNITS))',
'Y', -- null allowed
'qualifying units from lookup table for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'QUALIFYING_UNITS'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'QUALIFYING_UNITS_CODE' || p_date_p,
'T', -- data type
'nvl(ELINK.qualifying_units, ETYPE.qualifying_units)',
'Y', -- null allowed
'qualifying units from database for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'QUALIFYING_UNITS_CODE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'QUALIFYING_AGE' || p_date_p,
'N', -- data type
'nvl (ELINK.qualifying_age, ETYPE.qualifying_age)',
'Y', -- null allowed
'qualifying age for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'QUALIFYING_AGE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'STANDARD_LINK' || p_date_p,
'T', -- data type
'nvl(hr_general.decode_lookup(''YES_NO'',ELINK.STANDARD_LINK_FLAG),hr_general.decode_lookup(''YES_NO'',ETYPE.STANDARD_LINK_FLAG))',
'N', -- null allowed
'standard link meaning from lookup table Yes = standard, No = discretionary',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'STANDARD_LINK'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'STANDARD_LINK_CODE' || p_date_p,
'T', -- data type
'nvl (ELINK.standard_link_flag, ETYPE.standard_link_flag)',
'N', -- null allowed
'standard link value held on the database Y = standard, N = discretionary',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'STANDARD_LINK_CODE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'COSTABLE_TYPE' || p_date_p,
'T', -- data type
'CTLOOK.meaning',
'N', -- null allowed
'costable type meaning from lookup table for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'COSTABLE_TYPE'
,p_date_p => p_date_p
);
insert_database_item (l_element_name,
'COSTABLE_TYPE_CODE' || p_date_p,
'T', -- data type
'ELINK.costable_type',
'N', -- null allowed
'costable type value held on the database for element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'COSTABLE_TYPE_CODE'
,p_date_p => p_date_p
);
insert_user_entity (l_route3_name,
l_element_name || '_E3' || p_date_p,
'entity for '|| l_route3_name,
'Y', -- not found allowed flag
'E',
p_element_type_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_element_type_id, 1);
insert_database_item (l_element_name,
'COUNT' || p_date_p,
'N', -- data type
'count(0)',
'Y', -- null allowed
'count of element types for given assignment and element type',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'COUNT'
,p_date_p => p_date_p
);
select nvl (ltrim(rtrim(ETYPE.legislation_code)),
ltrim(rtrim(BUSGP.legislation_code)))
into l_leg_code_class
from pay_element_types_f ETYPE
, per_business_groups_perf BUSGP
where ETYPE.element_type_id = p_element_type_id
and p_effective_date between ETYPE.effective_start_date
and ETYPE.effective_end_date
and BUSGP.business_group_id (+) = ETYPE.business_group_id;
insert_user_entity (l_route1_name,
l_element_name || '_DF_E3' || p_date_p,
'Element DDF entity for '|| l_route1_name,
'Y',
'E',
p_element_type_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted
THEN
insert_parameter_value (p_element_type_id, 1);
IF l_record_inserted
THEN
insert_database_item (l_element_name,
c2rec.c_db_name || p_date_p,
'T', -- data type
'ETYPE.' || c2rec.c_def_text,
'Y', -- null allowed
'Element Descriptive flex DB item for ' || l_element_name);
deletes all the old DB items for the given element type id, as these DB
items would have been set up to use the context of date earned. It then
calls the element type creation procedure : create_element_type, with the
parameters to create date paid DB items. This includes deleting and
re-creating input value database items for the given element type.
*/
procedure create_element_type_dp_dict
(
p_element_type_id in number
) is
--
-- cursor c1 : select input values to be deleted:
--
cursor c1 is
select input_value_id
from pay_input_values_f
where element_type_id = p_element_type_id;
select input_value_id,
max(effective_end_date) c_date
from pay_input_values_f
where generate_db_items_flag = 'Y'
and element_type_id = p_element_type_id
group by input_value_id;
delete_element_type_dict (p_element_type_id);
select element_type_id,
max(effective_end_date)
into l_element_type,
l_date
from pay_element_types_f
where element_type_id = p_element_type_id
group by element_type_id;
delete_input_value_dict (c1rec.input_value_id);
delete_input_value_dict - delete an input value from the data
dictionary
--
DESCRIPTION
*/
procedure delete_input_value_dict
(
p_input_value_id in number
) is
--
BEGIN
-- delete_compiled_formula_priv(p_input_value_id, 'I', '%', null);
DELETE FROM ff_user_entities
WHERE creator_id = p_input_value_id
AND creator_type = 'I';
END delete_input_value_dict;
route parameter values, and then inserting each database items for that
particular route.
The database items created use the input value name. This routine
generates the following database items:
--
_UNIT_OF_MEASURE
_UNIT_OF_MEASURE_CODE
_DEFAULT
_MINIMUM
_MAXIMUM
--
The following database items are created if multiple entries are NOT
allowed:
--
_ENTRY_VALUE
_USER_ENTERED_CODE
_START_DATE
_END_DATE
--
The following database item is created if the multiple entries are
allowed:
--
_ENTRY_VALUE (sum of all non recurring values)
*/
procedure create_input_value
(
p_input_value_id in number,
p_effective_date in date,
p_date_p in varchar2
) is
l_route1_name varchar2(50) := 'INPUT_VALUE_FROM_INPUT_VALUE_TABLE' ||p_date_p;
l_record_inserted boolean;
select language_code
from fnd_languages
where installed_flag in ('I','B');
select name,
element_type_id,
to_char(effective_start_date, 'DD-MM-YYYY') esd,
to_char(effective_end_date, 'DD-MM-YYYY') eed,
legislation_code,
business_group_id
from pay_input_values_f
where input_value_id = p_iv_id;
select element_name,
to_char(effective_start_date, 'DD-MM-YYYY') esd,
to_char(effective_end_date, 'DD-MM-YYYY') eed,
legislation_code,
business_group_id
from pay_element_types_f
where element_type_id = p_el_id;
PROCEDURE local_insert_database_item (
p_item_name in varchar2,
p_data_type in varchar2,
p_definition_text in varchar2,
p_null_allowed_flag in varchar2,
p_description in varchar2,
p_user_entity_id in number,
p_full_mls in boolean,
p_base_dbi_name out nocopy varchar2)
IS
BEGIN
--
-- NB attention spaces ' ' of l_input_name are not replaced with underscore '_'.
--
-- call to insert_database_item to handle Pay_Value issue : Bug 1110849
-- where if the input value found is Pay Value get dbitem created
-- to contain the translated value for Pay Value by passing the input value
-- name within the second parameter which goes through translation
-- lookup in insert_database_item.
--
if l_input_name = 'PAY VALUE' and not p_full_mls then
insert_database_item (l_element_name,
l_input_name || '_' || p_item_name || p_date_p,
p_data_type,
p_definition_text,
p_null_allowed_flag,
p_description,
p_user_entity_id,
p_full_mls,
p_base_dbi_name);
insert_database_item (l_entity_name,
p_item_name || p_date_p,
p_data_type,
p_definition_text,
p_null_allowed_flag,
p_description,
p_user_entity_id,
p_full_mls,
p_base_dbi_name);
END local_insert_database_item;
select ltrim (rtrim (upper (INPUTV.name))),
INPUTV.uom,
INPUTV.business_group_id,
ltrim(rtrim(INPUTV.legislation_code)),
INPUTV.created_by,
INPUTV.last_update_login,
ET.element_type_id,
replace (ltrim (rtrim (upper (ET.element_name))), ' ', '_'),
ET.multiple_entries_allowed_flag
into l_input_name,
l_uom,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_element_type_id,
l_element_name,
l_multiple_entries
from pay_input_values_f INPUTV
, pay_element_types_f ET
where INPUTV.input_value_id = p_input_value_id
and p_effective_date between INPUTV.effective_start_date
and INPUTV.effective_end_date
and ET.element_type_id = INPUTV.element_type_id
and p_effective_date between ET.effective_start_date
and ET.effective_end_date;
hr_utility.trace('create_input_value select');
select distinct element_type_id
into l_iv_eltypeid
from pay_input_values_f
where input_value_id = p_input_value_id
and rownum = 1;
select bg.legislation_code
into l_legislation_code1
from per_business_groups_perf bg
where bg.business_group_id = l_business_group_id
;
insert_user_entity (l_route1_name,
l_entity_name || '_I1' || p_date_p,
'entity for '|| l_route1_name,
'Y', -- not found allowed flag
'I',
p_input_value_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_input_value_id, 1);
local_insert_database_item (
'UNIT_OF_MEASURE',
'T', -- data type
'UMLOOK.meaning',
'N', -- null allowed
'unit of measure from lookup table for input value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'UNIT_OF_MEASURE'
,p_date_p => p_date_p
);
local_insert_database_item (
'UNIT_OF_MEASURE_CODE',
'T', -- data type
'INPUTV.uom',
'N', -- null allowed
'unit of measure held on the database for input value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'UNIT_OF_MEASURE_CODE'
,p_date_p => p_date_p
);
insert_user_entity (l_route2_name,
l_entity_name || '_I2' || p_date_p,
'entity for '|| l_route2_name,
'Y', -- not found allowed flag
'I',
p_input_value_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_input_value_id, 1);
local_insert_database_item (
'DEFAULT',
l_data_type, -- data type
l_start_string||'nvl(LIV.default_value,INPUTV.default_value)'|| l_end_string,
'Y', -- null allowed
'default value for input value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'DEFAULT'
,p_date_p => p_date_p
);
local_insert_database_item (
'MIN',
l_data_type, -- data type
l_start_string || 'nvl(LIV.min_value,INPUTV.min_value)' || l_end_string,
'Y', -- null allowed
'minimum value for input value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'MIN'
,p_date_p => p_date_p
);
local_insert_database_item (
'MAX',
l_data_type, -- data type
l_start_string || 'nvl(LIV.max_value,INPUTV.max_value)' || l_end_string,
'Y', -- null allowed
'maximum value for input value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'MAX'
,p_date_p => p_date_p
);
insert_user_entity (l_entry_route,
l_entity_name || '_I3' || p_date_p,
'non multiple entry entity for '|| l_entry_route,
'Y', -- not found allowed flag
'I',
p_input_value_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_input_value_id, 1);
insert_parameter_value (l_element_type_id, 2);
local_insert_database_item (
'ENTRY_VALUE',
l_data_type, -- data type
'min (' || l_start_string ||
'decode(INPUTV.hot_default_flag,''Y'',nvl(EEV.screen_entry_value,
nvl(LIV.default_value,INPUTV.default_value)),''N'',EEV.screen_entry_value)'
|| l_end_string || ')',
'Y', -- null allowed
'the recurring value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'ENTRY_VALUE'
,p_date_p => p_date_p
);
local_insert_database_item (
'USER_ENTERED_CODE',
'T', -- data type
'min(decode(EEV.screen_entry_value,null,''N'',''Y''))',
'Y', -- null allowed
'indicates if a value exists at the element entry level',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'USER_ENTERED_CODE'
,p_date_p => p_date_p
);
local_insert_database_item (
'START_DATE',
'D', -- data type
'min(EE.effective_start_date)',
'Y', -- null allowed
'start date of element',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'START_DATE'
,p_date_p => p_date_p
);
local_insert_database_item (
'END_DATE',
'D', -- data type
'min(EE.effective_end_date)',
'Y', -- null allowed
'end date of element',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'END_DATE'
,p_date_p => p_date_p
);
insert_user_entity (l_override_entry_route,
l_entity_name || '_I4' || p_date_p,
'non multiple entry entity for '|| l_override_entry_route,
'Y', -- not found allowed flag
'I',
p_input_value_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_input_value_id, 1);
insert_parameter_value (l_element_type_id, 2);
local_insert_database_item (
'OVERRIDE_ENTRY_VALUE',
l_data_type, -- data type
'min (' || l_start_string ||
'decode(INPUTV.hot_default_flag,''Y'',nvl(EEV.screen_entry_value,
nvl(LIV.default_value,INPUTV.default_value)),''N'',EEV.screen_entry_value)'
|| l_end_string || ')',
'Y', -- null allowed
'the recurring value',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'OVERRIDE_ENTRY_VALUE'
,p_date_p => p_date_p
);
local_insert_database_item (
'OVERRIDE_USER_ENTERED_CODE',
'T', -- data type
'min(decode(EEV.screen_entry_value,null,''N'',''Y''))',
'Y', -- null allowed
'indicates if a value exists at the element entry level',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'OVERRIDE_USER_ENTERED_CODE'
,p_date_p => p_date_p
);
local_insert_database_item (
'OVERRIDE_START_DATE',
'D', -- data type
'min(EE.effective_start_date)',
'Y', -- null allowed
'start date of element',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'OVERRIDE_START_DATE'
,p_date_p => p_date_p
);
local_insert_database_item (
'OVERRIDE_END_DATE',
'D', -- data type
'min(EE.effective_end_date)',
'Y', -- null allowed
'end date of element',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'OVERRIDE_END_DATE'
,p_date_p => p_date_p
);
insert_user_entity (l_entry_route,
l_entity_name || '_I3' || p_date_p,
'multiple entry entity for '|| l_entry_route,
'Y',
'I',
p_input_value_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_input_value_id, 1);
insert_parameter_value (l_element_type_id, 2);
local_insert_database_item (
'ENTRY_VALUE',
l_data_type, -- data type
'sum(' || l_start_string || 'decode(INPUTV.hot_default_flag,
''Y'',nvl(EEV.screen_entry_value,
nvl(LIV.default_value,INPUTV.default_value)),
''N'',EEV.screen_entry_value)' || l_end_string || ')',
'Y', -- null allowed
'the summed multiple entry element values',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'ENTRY_VALUE'
,p_date_p => p_date_p
);
insert_user_entity (l_override_entry_route,
l_entity_name || '_I4' || p_date_p,
'multiple entry entity for '|| l_override_entry_route,
'Y',
'I',
p_input_value_id,
l_business_group_id,
l_legislation_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- Fetch the user_entity_id.
--
select ff_user_entities_s.currval
into l_user_entity_id
from dual;
insert_parameter_value (p_input_value_id, 1);
insert_parameter_value (l_element_type_id, 2);
local_insert_database_item (
'OVERRIDE_ENTRY_VALUE',
l_data_type, -- data type
'sum(' || l_start_string || 'decode(INPUTV.hot_default_flag,
''Y'',nvl(EEV.screen_entry_value,
nvl(LIV.default_value,INPUTV.default_value)),
''N'',EEV.screen_entry_value)' || l_end_string || ')',
'Y', -- null allowed
'the summed multiple entry element values',
l_user_entity_id,
l_full_mls,
l_base_dbi_name);
update_et_tl_dbi_names
(p_leg_code => l_legislation_code
,p_bg_id => l_business_group_id
,p_startup_mode => l_startup_mode
,p_user_name => l_base_dbi_name
,p_user_entity_id => l_user_entity_id
,p_prefixes => l_dbi_prefixes
,p_suffix => 'OVERRIDE_ENTRY_VALUE'
,p_date_p => p_date_p
);
select nvl (ltrim(rtrim(INPUTV.legislation_code)),
ltrim(rtrim(BUSGP.legislation_code)))
into l_leg_code_ben
from pay_input_values_f INPUTV
, per_business_groups_perf BUSGP
where INPUTV.input_value_id = p_input_value_id
and p_effective_date between INPUTV.effective_start_date
and INPUTV.effective_end_date
and BUSGP.business_group_id (+) = INPUTV.business_group_id;
cursor c1 is select element_type_id,
max(effective_end_date)
from pay_element_types_f e
where not exists (
select null
from ff_user_entities u
where u.creator_id = e.element_type_id
and u.creator_type = 'E')
and mod(element_type_id, p_maxworkers) = p_worker_id
group by element_type_id
order by element_type_id;
cursor c2 is select input_value_id,
max(effective_end_date)
from pay_input_values_f i
where generate_db_items_flag = 'Y'
and not exists (
select null
from ff_user_entities u
where u.creator_id = i.input_value_id
and u.creator_type = 'I')
and mod(input_value_id, p_maxworkers) = p_worker_id
group by input_value_id
order by input_value_id;
cursor c1 is select element_type_id,
max(effective_end_date)
from pay_element_types_f a
where not exists (
select null
from ff_user_entities u
where u.creator_id = a.element_type_id
and u.creator_type = 'E')
and mod(element_type_id, p_maxworkers) = p_worker_id
and (a.legislation_code = p_leg_code
or exists (select null
from per_business_groups_perf b
where a.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code))
group by element_type_id
order by element_type_id;
cursor c2 is select input_value_id,
max(effective_end_date)
from pay_input_values_f a
where generate_db_items_flag = 'Y'
and not exists (
select null
from ff_user_entities u
where u.creator_id = a.input_value_id
and u.creator_type = 'I')
and mod(input_value_id, p_maxworkers) = p_worker_id
and (a.legislation_code = p_leg_code
or exists (select null
from per_business_groups_perf b
where a.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code))
group by input_value_id
order by input_value_id;
delete_element_types - delete all DB itms for element type and input
values
--
DESCRIPTION
This routine deletes all database items based on element types
in the system. The routine assumes that no such database items currently
exist.
*/
procedure delete_element_types(p_worker_id in number default 0,
p_maxworkers in number default 1) is
begin
--
delete ff_user_entities u
where u.creator_type = 'I'
and exists (
select null
from pay_input_values_f a
where u.creator_id = a.input_value_id);
delete ff_user_entities u
where u.creator_type = 'E'
and exists (
select null
from pay_element_types_f x
where u.creator_id = x.element_type_id);
delete_element_types - delete all DB itms for element type and input
values
--
DESCRIPTION
This routine deletes all database items based on element types
in the system. The routine assumes that no such database items currently
exist.
*/
procedure delete_element_types(p_leg_code in varchar2,
p_worker_id in number default 0,
p_maxworkers in number default 1) is
begin
--
-- delete the input values
--
delete ff_user_entities u
where u.creator_type = 'I'
and exists (
select null
from pay_input_values_f a
where u.creator_id = a.input_value_id
and (a.legislation_code = p_leg_code
or exists
(select null
from per_business_groups_perf b
where a.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code)));
delete ff_user_entities u
where u.creator_type = 'E'
and exists (
select null
from pay_element_types_f x
where u.creator_id = x.element_type_id
and (x.legislation_code = p_leg_code
or exists
(select null
from per_business_groups_perf y
where x.business_group_id = y.business_group_id
and nvl(y.legislation_code, p_leg_code) = p_leg_code)));
end delete_element_types;
rebuild_ele_input_bal - Delete and then re-create Db items for elements,
input values and balances.
--
DESCRIPTION
This routine deletes all database items for element, input values and
balances in the database. It then re-creates them.
This routine would typically be called after a startup delivery.
If the parameter 'p_commit' is set to 'Y' then the routine will commit
after the procedure calls to delete/re-create the DB items. This helps
the installation process when there is many DB items, or if the rollback
segment space is limited.
*/
procedure rebuild_ele_input_bal
(
p_commit in varchar2 default 'N',
p_worker_id in number default 0,
p_maxworkers in number default 1
)
is
workers_complete number;
select count(*)
into g_debug_cnt
from pay_patch_status
where patch_name = 'HRGLOBAL_DEBUG2';
insert_mthread_pps(1, p_worker_id, 'ZZ');
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S1'
and process_type = 'ZZ';
insert_mthread_pps(2, p_worker_id, 'ZZ');
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S2'
and process_type = 'ZZ';
insert_mthread_pps(3, p_worker_id, 'ZZ');
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S3'
and process_type = 'ZZ';
insert_mthread_pps(4, p_worker_id, 'ZZ');
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S4'
and process_type = 'ZZ';
insert_mthread_pps_err(p_worker_id, 'ZZ');
rebuild_ele_input_bal - Delete and then re-create Db items for elements,
input values and balances.
--
DESCRIPTION
This routine deletes all database items for element, input values and
balances in the database. It then re-creates them.
This routine would typically be called after a startup delivery.
If the parameter 'p_commit' is set to 'Y' then the routine will commit
after the procedure calls to delete/re-create the DB items. This helps
the installation process when there is many DB items, or if the rollback
segment space is limited.
*/
procedure rebuild_ele_input_bal
(
p_commit in varchar2 default 'N',
p_leg_code in varchar2,
p_worker_id in number default 0,
p_maxworkers in number default 1
)
is
workers_complete number;
select fdi.rowid
, fdi.formula_id
, fdi.effective_start_date
from ff_fdi_usages_f fdi
where exists
(select null
from ff_database_items dbi
where fdi.item_name = dbi.user_name
and exists
(select null
from ff_user_entities ent
where ent.user_entity_id = dbi.user_entity_id
and ent.creator_type in ('B','RB','E','I')
-- MERGE B RB E I main cursors
--
-- B
and (
not exists (
select null
from pay_defined_balances b
where b.defined_balance_id = ent.creator_id
and ent.creator_type = 'B'
)
OR
-- RB
(not exists (
select null
from pay_defined_balances b
where b.defined_balance_id = ent.creator_id
and ent.creator_type = 'RB'
and exists
(select null
from pay_dimension_routes pdr
where pdr.balance_dimension_id = b.balance_dimension_id)
)
OR
(exists (
select pdr.balance_dimension_id
from pay_dimension_routes pdr,
pay_defined_balances b
where pdr.balance_dimension_id = b.balance_dimension_id
and not exists (select null
from ff_user_entities ue
where ue.creator_id = b.defined_balance_id
and ue.route_id = pdr.route_id
and ue.creator_type = 'RB'))
)
) -- end RB
OR
-- E
(not exists (
select null
from pay_element_types_f et
where ent.creator_id = et.element_type_id
and ent.creator_type = 'E'))
OR
-- I
(not exists (
select null
from pay_input_values_f i
where ent.creator_id = i.input_value_id
and i.generate_db_items_flag = 'Y'
and ent.creator_type = 'I'))
) -- end B RB E I
and
(nvl (ent.legislation_code, ' ') = nvl (p_leg_code, ' ')
or exists
(select null
from per_business_groups_perf b
where ent.business_group_id = b.business_group_id
and nvl(b.legislation_code, p_leg_code) = p_leg_code
)
)
)
)
;
select count(*)
into g_debug_cnt
from pay_patch_status
where patch_name = 'HRGLOBAL_DEBUG2';
hr_utility.trace('entering delete fcomp info: LC: ' || p_leg_code);
delete
from ff_fdi_usages_f fdi
where fdi.rowid = l_rowids(i)
;
delete
from ff_compiled_info_f fci
where fci.formula_id = l_formula_ids(i)
and fci.effective_start_date = l_start_dates(i)
;
hr_utility.trace('leaving delete fcomp info: LC: ' || p_leg_code);
insert_mthread_pps(1, p_worker_id, p_leg_code);
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S1'
and process_type = p_leg_code;
insert_mthread_pps(2, p_worker_id, p_leg_code);
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S2'
and process_type = p_leg_code;
insert_mthread_pps(3, p_worker_id, p_leg_code);
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S3'
and process_type = p_leg_code;
insert_mthread_pps(4, p_worker_id, p_leg_code);
select count(*)
into worker_err
from pay_patch_status
where patch_name like 'HRRBDEIB INTERNAL PROC ERR%';
select count(*)
into workers_complete
from pay_patch_status
where patch_name = 'HRRBDEIB INTERNAL PROC S4'
and process_type = p_leg_code;
insert_mthread_pps_err(p_worker_id, p_leg_code);
delete_grade_spine_dict - delete the grade rate items from the data
dictionary
--
DESCRIPTION
*/
procedure delete_grade_spine_dict
(
p_rate_id in number
) is
--
BEGIN
DELETE FROM ff_user_entities
WHERE creator_id = p_rate_id
AND creator_type = 'G';
end delete_grade_spine_dict;
route parameter values, and then inserting each database items for that
particular route.
The database items created use the rate name. This routine
generates the following database items for grades:
--
GRADE__VALUE
GRADE__MINIMUM
GRADE__MAXIMUM
--
For spines the following database item is generated:
--
SPINE__VALUE
*/
procedure create_grade_spine_dict
(
p_rate_id in number
) is
l_grade_route_name varchar2(50) := 'GRADE_RATE_ROUTE';
l_record_inserted boolean;
select rate_type,
replace (ltrim (rtrim (upper (name))), ' ', '_'),
business_group_id,
created_by,
last_update_login
into l_rate_type,
l_name,
l_business_group_id,
l_created_by,
l_last_login
from pay_rates
where rate_id = p_rate_id;
insert_user_entity (l_grade_route_name,
'GRADE_' || l_name,
'entity for '|| l_grade_route_name,
'Y', -- not found allowed flag
'G',
p_rate_id,
l_business_group_id,
null, -- null legislation code
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- insert the rate id for the where clause filler
--
insert_parameter_value (p_rate_id, 1);
insert_database_item (l_name,
'VALUE',
'T', -- data type
'GRULE.value',
'Y', -- null allowed
'value for grade rates');
insert_database_item (l_name,
'MINIMUM',
'T', -- data type
'GRULE.minimum',
'Y', -- null allowed
'minimum value for grade rates');
insert_database_item (l_name,
'MAXIMUM',
'T', -- data type
'GRULE.maximum',
'Y', -- null allowed
'maximum value for grade rates');
insert_user_entity (l_spine_route_name,
'SPINE_' || l_name,
'entity for '|| l_spine_route_name,
'Y', -- not found allowed flag
'G',
p_rate_id,
l_business_group_id,
null, -- null legislation code
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- insert the rate id for the where clause filler
--
insert_parameter_value (p_rate_id, 1);
insert_database_item (l_name,
'VALUE',
'T', -- data type
'target.value',
'Y', -- null allowed
'value for spine rates');
cursor c1 is select rate_id
from pay_rates r
where not exists (select null from ff_user_entities u
where u.creator_id = r.rate_id
and u.creator_type = 'G');
delete_grade_spine_rates - delete DB items for grade and spine rates
--
DESCRIPTION
This routine deletes all database items based on grade or spine rates
in the system. The routine assumes that no such database items currently
exist.
*/
procedure delete_grade_spine_rates is
cursor c1 is select rate_id
from pay_rates;
delete_grade_spine_dict (c1rec.rate_id);
end delete_grade_spine_rates;
delete_flexfield_dict - delete a descriptive flexfield in the
data dictionary
--
DESCRIPTION
This procedure is the main entry point for deleting descriptive
flexfield database items. To delete all the descriptive flexfield
database items for a given legislation code, pass the title parameter
as '%'. To delete an individual descriptive flexfield pass the title
of the flexfield.
NOTES
Since the legislation code for a descriptive flexfield could be null
a nvl function is required as part of the SQL statement.
*/
procedure delete_flexfield_dict
(
p_title in varchar2,
p_context in varchar2,
p_leg_code in varchar2
) is
l_entity_name ff_user_entities.user_entity_name%type;
(p_context = '%') then -- delete all descriptive flexfield DB items
--
-- first delete any complied formula references
--
delete_compiled_formula (null,
'DF',
'%',
p_leg_code);
delete from ff_user_entities
where creator_type = 'DF'
and nvl (legislation_code, ' ') = nvl (p_leg_code, ' ');
else -- delete selected DB items
--
-- assemble the entity name:
--
l_entity_name := replace (replace (ltrim(rtrim(upper(p_title))),' ','_'),'''','')
||'_'|| replace (ltrim(rtrim(upper(p_context))),' ','_');
delete_compiled_formula (null,
'DF',
l_entity_name,
p_leg_code);
delete from ff_user_entities
where creator_type = 'DF'
and user_entity_name like l_entity_name
and nvl (legislation_code, ' ') = nvl (p_leg_code, ' ');
end delete_flexfield_dict;
the database items, the outer one to select the context
and the inner one to select the column names.
*/
--
-- This create_desc_flex() does not accept business_group_id for context
-- sensitive db_item generation.
--
procedure create_desc_flex
(
p_title in varchar2,
p_table_name in varchar2,
p_route_name in varchar2,
p_entity_name in varchar2,
p_context in varchar2,
p_global_flag in varchar2,
p_param_value in varchar2,
p_leg_code in varchar2
) is
BEGIN
create_desc_flex_main( p_title,
p_table_name,
p_route_name,
p_entity_name,
p_context,
p_global_flag,
p_param_value,
p_leg_code,
p_business_group_id => NULL );
l_record_inserted boolean;
delete_flexfield_dict (p_title,
p_context,
p_leg_code);
insert_user_entity (p_route_name,
l_entity_name || '_DF',
'entity for '|| p_route_name,
'Y',
'DF',
null, -- null creator id
p_business_group_id,
p_leg_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- if we are creating certain Developer DF DB items then we
-- need to insert the type context into the route parameter
-- value table.
--
if (p_route_name = 'DEVELOPER_ORG_DESC_FLEX_ROUTE') OR
(p_route_name = 'LEGAL_CO_DESC_FLEX_ROUTE') OR
(p_route_name = 'DEVELOPER_ASS_DESC_FLEX_ROUTE') OR
(p_route_name = 'DEVELOPER_LOC_DESC_FLEX_ROUTE') OR
(p_route_name = 'DEVELOPER_POS_DESC_FLEX_ROUTE') OR
(p_route_name = 'DEVELOPER_PER_DESC_FLEX_ROUTE') then
--
-- note: fast formula requires the quotes for a text
-- string to be in the parameter value table, as opposed
-- to in the route.
--
l_param_value := '''' ||
replace (ltrim(rtrim(p_param_value)),' ','_') || '''';
insert_parameter_value (l_param_value, 1);
insert_parameter_value (l_param_value, 1);
insert_parameter_value (p_param_value, 1);
insert_database_item (p_entity_name,
'CURRENCY_CODE',
'T', -- data type
'target.currency_code',
'N', -- null allowed
'database item for : ' ||
p_entity_name);
insert_parameter_value (p_param_value, 1);
select user_entity_id
into l_user_entity_id
from ff_user_entities
where user_entity_name=l_entity_name || '_DF'
and nvl(legislation_code,'X')=nvl(p_leg_code,'X')
and nvl(business_group_id,-1)=nvl(p_business_group_id,-1);
insert_database_item (p_entity_name,
c2rec.c_db_name,
'T', -- data type
'target.' || c2rec.c_def_text,
'Y', -- null allowed
'database item for : ' || p_entity_name,
l_user_entity_id);
select legislation_code,
navigation_method
into l_legislation_code,
l_navigation_method
from hr_org_information_types
where org_information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from per_assignment_info_types
where information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from hr_location_info_types
where information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from per_position_info_types
where information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from per_people_info_types
where information_type = p_context;
select df.application_table_name,
rtd.route_name,
rtd.user_key
from fnd_descriptive_flexs_vl df,
pay_route_to_descr_flexs rtd
where df.application_id = rtd.application_id
and df.descriptive_flexfield_name = rtd.descriptive_flexfield_name
and replace (ltrim (rtrim(df.title)), '''','') = p_title;
select dfc.descriptive_flex_context_code bus_grp_id
from fnd_descr_flex_contexts dfc,
fnd_descriptive_flexs_vl df
where dfc.application_id = df.application_id
and dfc.descriptive_flexfield_name = df.descriptive_flexfield_name
and df.application_table_name = p_table_name
and replace (ltrim (rtrim(df.title)), '''','') = p_title
and df.default_context_field_name = 'BUSINESS_GROUP_ID'
and dfc.enabled_flag = 'Y'
and dfc.global_flag = 'N';
select df.application_table_name,
rtd.route_name,
rtd.user_key
from fnd_descriptive_flexs_vl df,
pay_route_to_descr_flexs rtd
where df.application_id = rtd.application_id
and df.descriptive_flexfield_name = rtd.descriptive_flexfield_name
and replace (ltrim (rtrim(df.title)), '''','') = p_title
and rtd.descriptive_flex_context_code = p_context;
select data_type
from ff_route_parameters rpm, ff_routes rt
where rt.route_name = p_route_name
and rpm.route_id = rt.route_id;
select descriptive_flexfield_name
into l_flexfield_name
from fnd_descriptive_flexs_vl
where title = p_title
and application_id between 800 and 801;
select legislation_code,
navigation_method
into l_legislation_code,
l_navigation_method
from hr_org_information_types
where org_information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from per_assignment_info_types
where information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from hr_location_info_types
where information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from per_position_info_types
where information_type = p_context;
select legislation_code,
multiple_occurences_flag
into l_legislation_code,
l_multi_occur_flag
from per_people_info_types
where information_type = p_context;
Added rtrim to title select below, due to Reserved word problem, ie
the title, in creating descriptive flex cannot contain full stops,
eg 'Further Payment Info.' - this is a temporary measure.
*/
procedure create_org_pay_flex_dict
(
p_payment_id in number
) is
cursor get_title is
select rtrim(title,'.') from fnd_descriptive_flexs_vl
where descriptive_flexfield_name = 'Paymeth Developer DF';
select payment_type_name
into l_context
from pay_payment_types
where payment_type_id = p_payment_id;
l_record_inserted boolean;
select replace (ltrim (rtrim (upper (name))), ' ', '_'),
business_group_id,
created_by,
last_update_login
into l_absence_name,
l_business_group_id,
l_created_by,
l_last_login
from per_absence_attendance_types
where absence_attendance_type_id = p_absence_type_id;
insert_user_entity (l_route_name,
l_absence_name,
'entity for '|| l_route_name,
'Y', -- not found allowed flag
'A',
p_absence_type_id,
l_business_group_id,
null, -- null legislation code
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- insert the absence type id for the where clause filler
--
insert_parameter_value (p_absence_type_id, 1);
insert_database_item (l_absence_name,
'CUM_BALANCE',
'N', -- data type
'sum (fnd_number.canonical_to_number(target.screen_entry_value))',
'Y', -- null allowed
'cumulative balance of an absence type for a given assignment');
delete_absence_dict - delete the absence DB items from the data
dictionary
--
DESCRIPTION
*/
procedure delete_absence_dict
(
p_absence_type_id in number
) is
--
BEGIN
DELETE FROM ff_user_entities
WHERE creator_id = p_absence_type_id
AND creator_type = 'A';
end delete_absence_dict;
cursor c1 is select absence_attendance_type_id type_id
from per_absence_attendance_types;
delete_absence_types - delete DB items for absence types
--
DESCRIPTION
This routine deletes all database items based on absence types
in the system. The routine assumes that no such database items currently
exist.
*/
procedure delete_absence_types is
cursor c1 is select absence_attendance_type_id type_id
from per_absence_attendance_types;
delete_absence_dict (c1rec.type_id);
end delete_absence_types;
delete_keyflex_dict - delete a key flexfield in the data dictionary
--
DESCRIPTION
This procedure is the main entry point for deleting key
flexfield database items. The parameters passed in are the id flex num
and the name of the key flexfield.
--
NOTES
*/
procedure delete_keyflex_dict
(
p_creator_id in number,
p_entity_name in varchar2,
p_leg_code in varchar2,
p_business_group_id in number
) is
l_entity_name ff_user_entities.user_entity_name%type;
delete_compiled_formula (p_creator_id,
'KF',
l_entity_name,
p_leg_code);
delete from ff_user_entities
where creator_type = 'KF'
and creator_id = p_creator_id
and user_entity_name like l_entity_name
and ( nvl (legislation_code, ' ') = nvl (p_leg_code, ' ') -- 6955080
OR nvl (business_group_id, -1) = nvl (p_business_group_id, -1));
end delete_keyflex_dict;
SELECT application_column_name c_def_text,
replace (ltrim(rtrim(upper(segment_name))),' ','_') c_db_name
FROM fnd_id_flex_segments
WHERE application_id = p_applic_id
AND id_flex_num = p_id_flex_num
AND id_flex_code = p_id_flex_code;
l_record_inserted boolean;
insert_user_entity (p_route_name,
p_entity_name || '_KEY_FLEX_ENTITY',
'route for key flexfield : '|| p_route_name,
'Y',
'KF',
p_id_flex_num,
p_business_group,
p_leg_code,
l_created_by,
l_last_login,
l_record_inserted);
insert_database_item (p_entity_name,
c1rec.c_db_name,
'T', -- data type
p_table_name || '.' || c1rec.c_def_text,
'Y', -- null allowed
'database item for : ' || p_entity_name);
select grade_structure,
people_group_structure,
job_structure,
position_structure,
competence_structure,
legislation_code
into l_grade_flex_num,
l_group_flex_num,
l_job_flex_num,
l_position_flex_num,
l_competence_flex_num,
l_legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
delete_keyflex_dict (l_grade_flex_num,
'GRADE_KF',
l_legislation_code,
p_business_group_id);
delete_keyflex_dict (l_job_flex_num,
'JOB_KF',
l_legislation_code,
p_business_group_id);
delete_keyflex_dict (l_position_flex_num,
'POS_KF',
l_legislation_code,
p_business_group_id);
delete_keyflex_dict (l_group_flex_num,
'GROUP_KF',
l_legislation_code,
p_business_group_id);
delete_keyflex_dict (l_competence_flex_num,
'COMP_KF',
l_legislation_code,
p_business_group_id);
select legislation_code
from pay_legislation_rules
where rule_type = 'E'
and rule_mode = to_char (p_id_flex_num);
delete_keyflex_dict (p_id_flex_num,
'ORG_' || l_legislation_code,
l_legislation_code,
null);
delete_keyflex_dict (p_id_flex_num,
'PER_' || l_legislation_code,
l_legislation_code,
null);
select legislation_code
from pay_legislation_rules
where rule_type = 'S'
and rule_mode = to_char (p_id_flex_num);
select SEG.application_column_name c_def_text,
replace (ltrim(rtrim(upper(SEG.segment_name))),' ','_') c_db_name,
SEG.created_by c_created_by,
SEG.last_update_login c_last_login
from fnd_id_flex_segments SEG
, fnd_segment_attribute_values VALUE
where SEG.application_id = 800
and SEG.id_flex_code = 'SCL'
and SEG.id_flex_num = p_id_flex_num
and SEG.enabled_flag = 'Y'
and VALUE.application_column_name = SEG.application_column_name
and VALUE.id_flex_code = 'SCL'
and VALUE.id_flex_num = p_id_flex_num
and VALUE.segment_attribute_type = p_attribute_type
and VALUE.attribute_value = 'Y';
l_record_inserted boolean;
insert_user_entity (p_route_name,
p_entity_name,
'route for SCL level : '|| p_attribute_type,
'Y',
'KF',
p_id_flex_num,
null, -- null business group id
p_leg_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- insert the id flex num for the where clause filler
--
insert_parameter_value (p_id_flex_num, 1);
insert_database_item (p_entity_name,
c1rec.c_db_name,
'T', -- data type
'target.' || c1rec.c_def_text,
'Y', -- null allowed
'database item for : ' || p_entity_name);
delete_keyflex_dict (p_id_flex_num,
'SCL',
l_legislation_code,
null);
delete from ff_user_entities
where creator_type = 'KF'
and creator_id <> p_id_flex_num
and user_entity_name like 'SCL%'
and nvl (legislation_code, ' ') = nvl (l_legislation_code, ' ')
and business_group_id is null;
delete pay_patch_status
where patch_name like 'HRRBDEIB%';
delete from ff_user_entities
where creator_type in ('B', 'RB');
hrdyndbi.delete_element_types(0,1);
delete pay_patch_status
where patch_name like 'HRRBDEIB%';
select null
into l_exists
from dual
where exists
(
select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items a,
ff_user_entities b
where a.user_name = p_user_name
and a.user_entity_id = b.user_entity_id
and
(
p_startup_mode = 'MASTER'
or
(
p_startup_mode = 'SEED'
and
(
b.legislation_code = p_leg_code
or
(
b.legislation_code is null and b.business_group_id is null
)
or
p_leg_code =
(
select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
(
p_startup_mode = 'NON-SEED'
and
(
b.business_group_id = p_bg_id
or
(
b.legislation_code is null and b.business_group_id is null
)
or
(
b.business_group_id is null and b.legislation_code = p_leg_code
)
)
)
)
)
or exists
(
select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items_tl a,
ff_user_entities b
where a.translated_user_name = p_user_name
and (a.user_name <> p_user_name or a.user_entity_id <> p_ue_id)
and a.user_entity_id = b.user_entity_id
and
(
p_startup_mode = 'MASTER'
or
(
p_startup_mode = 'SEED'
and
(
b.legislation_code = p_leg_code
or
(
b.legislation_code is null and b.business_group_id is null
)
or
p_leg_code =
(
select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
(
p_startup_mode = 'NON-SEED'
and
(
b.business_group_id = p_bg_id
or
(
b.legislation_code is null and b.business_group_id is null
)
or
(
b.business_group_id is null and b.legislation_code = p_leg_code
)
)
)
)
)
or exists
(
select null
from ff_contexts
where context_name = p_user_name
);
select lookup_code,
replace(replace(replace(replace(replace(ltrim(rtrim(upper(meaning))),' ','_'),'''','_'),'(',''),')',''),'.','') meaning2
from fnd_lookup_values
where instr(c_lookup_code,lookup_code) > 0
and lookup_type = 'NAME_TRANSLATIONS'
and language = c_language
and view_application_id = 3
and security_group_id = g_security_group_id
order by length(lookup_code) desc;
select b.balance_name || d.database_item_suffix
, db.legislation_code
, db.business_group_id
, db.balance_type_id
, db.balance_dimension_id
from pay_defined_balances db
, pay_balance_types b
, pay_balance_dimensions d
where db.defined_balance_id = p_defined_balance_id
and b.balance_type_id = db.balance_type_id
and d.balance_dimension_id = db.balance_dimension_id
;
select et.element_name
, et.legislation_code
, et.business_group_id
from pay_element_types_f et
where et.element_type_id = p_element_type_id
;
select et.element_name || '_' || iv.name
, et.legislation_code
, et.business_group_id
from pay_element_types_f et
, pay_input_values_f iv
where iv.input_value_id = p_input_value_id
and et.element_type_id = iv.element_type_id
;
select b.balance_name
from pay_balance_types_tl b
where b.balance_type_id = p_balance_type_id
and b.language = p_language
;
select bd.database_item_suffix
from pay_balance_dimensions_tl bd
where bd.balance_dimension_id = p_balance_dimension_id
and bd.language = p_language
;
select ettl.element_name
from pay_element_types_f_tl ettl
where ettl.element_type_id = p_element_type_id
and ettl.language = p_language
;
select iv.name
from pay_input_values_f iv
where iv.input_value_id = p_input_value_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
;
select ivtl.name
, ettl.element_name
from pay_input_values_f iv
, pay_element_types_f_tl ettl
, pay_input_values_f_tl ivtl
where iv.input_value_id = p_input_value_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
and ettl.element_type_id = iv.element_type_id
and ettl.language = p_language
and ivtl.input_value_id = iv.input_value_id
and ivtl.language = p_language
;
select u.user_entity_id
from ff_database_items d
, ff_user_entities u
where d.user_name = p_user_name
and u.user_entity_id = d.user_entity_id
and u.creator_id = p_creator_id
and u.creator_type = p_creator_type
;
select null
into l_exists
from dual
where exists
(
select /*+ INDEX(a FF_DATABASE_ITEMS_PK)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items a,
ff_user_entities b
where a.user_name = p_tl_user_name
and (p_user_name <> p_tl_user_name or a.user_entity_id <> p_user_entity_id)
and a.user_entity_id = b.user_entity_id
and
(
p_startup_mode = 'MASTER'
or
(
p_startup_mode = 'SEED'
and
(
b.legislation_code = p_leg_code
or
(
b.legislation_code is null and b.business_group_id is null
)
or
p_leg_code =
(
select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
(
p_startup_mode = 'NON-SEED'
and
(
b.business_group_id = p_bg_id
or
(
b.legislation_code is null and b.business_group_id is null
)
or
(
b.business_group_id is null and b.legislation_code = p_leg_code
)
)
)
)
)
or exists
(
select /*+ ORDERED INDEX(a FF_DATABASE_ITEMS_TL_N2)
INDEX(b FF_USER_ENTITIES_PK) */ null
from ff_database_items_tl a,
ff_user_entities b
where a.translated_user_name = p_tl_user_name
and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
and a.user_entity_id = b.user_entity_id
and
(
p_startup_mode = 'MASTER'
or
(
p_startup_mode = 'SEED'
and
(
b.legislation_code = p_leg_code
or
(
b.legislation_code is null and b.business_group_id is null
)
or
p_leg_code =
(
select c.legislation_code
from per_business_groups_perf c
where c.business_group_id = b.business_group_id
)
)
)
or
(
p_startup_mode = 'NON-SEED'
and
(
b.business_group_id = p_bg_id
or
(
b.legislation_code is null and b.business_group_id is null
)
or
(
b.business_group_id is null and b.legislation_code = p_leg_code
)
)
)
)
)
or exists
(
select null
from ff_contexts
where context_name = p_tl_user_name
);
select count(*)
into l_count
from ff_user_entities ue
where ue.creator_id = p_creator_id
and ue.creator_type = p_creator_type
and ue.user_entity_name like '%_DP'
;
select count(*)
into l_count
from ff_user_entities ue
where ue.creator_id = p_creator_id
and ue.creator_type = p_creator_type
and ue.user_entity_name not like '%_DP'
;
procedure update_tl_dbi_name
(p_user_name in varchar2
,p_user_entity_id in number
,p_leg_code in varchar2
,p_bg_id in number
,p_startup_mode in varchar2
,p_language in varchar2
,p_tl_user_name in varchar2
) is
l_clash boolean;
update ff_database_items_tl dbitl
set dbitl.translated_user_name = l_tl_user_name
, dbitl.source_lang = p_language
where dbitl.language = p_language
and dbitl.user_name = p_user_name
and dbitl.user_entity_id = p_user_entity_id
;
assert(SQL%rowcount > 0, 'update_tl_dbi_name:1',
p_user_name || ':' || p_user_entity_id);
ff_database_items_pkg.update_seeded_tl_rows
(x_user_name => p_user_name
,x_user_entity_id => p_user_entity_id
,x_language => p_language
,x_translated_user_name => l_tl_user_name
,x_description => null
,x_got_error => l_got_error
);
ff_database_items_pkg.update_tl_row
(x_user_name => p_user_name
,x_user_entity_id => p_user_entity_id
,x_language => p_language
,x_source_lang => p_language
,x_translated_user_name => p_tl_user_name
,x_description => null
);
end update_tl_dbi_name;
procedure update_et_tl_dbi_names
(p_leg_code in varchar2
,p_bg_id in number
,p_startup_mode in varchar2
,p_user_name in varchar2
,p_user_entity_id in number
,p_prefixes in t_dbi_prefixes
,p_suffix in varchar2
,p_date_p in varchar2
) is
--
l_tl_name ff_database_items_tl.translated_user_name%type;
update_tl_dbi_name
(p_user_name => p_user_name
,p_user_entity_id => p_user_entity_id
,p_leg_code => p_leg_code
,p_bg_id => p_bg_id
,p_startup_mode => p_startup_mode
,p_language => p_prefixes(i).language
,p_tl_user_name => l_tl_name
);
end update_et_tl_dbi_names;
procedure update_defined_balance
(p_defined_balance_id in number
,p_languages in dbms_sql.varchar2s
) is
l_leg_code varchar2(30);
update_tl_dbi_name
(p_user_name => l_base_name
,p_user_entity_id => l_ueid
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_startup_mode => l_startup_mode
,p_language => p_languages(i)
,p_tl_user_name => l_tl_name
);
end update_defined_balance;
procedure update_element_type
(p_element_type_id in number
,p_effective_date in date
,p_languages in dbms_sql.varchar2s
,p_dbi_prefixes in t_dbi_prefixes
) is
l_date_p varchar2(16);
select et.benefit_classification_id
, et.legislation_code
, et.business_group_id
from pay_element_types_f et
where et.element_type_id = p_element_type_id
and p_effective_date between
et.effective_start_date and et.effective_end_date
;
procedure update_dbis
(p_element_type_id in number
,p_leg_code in out nocopy varchar2
,p_bg_id in out nocopy number
,p_prefix in out nocopy varchar2
,p_dbi_prefixes in t_dbi_prefixes
,p_date_p in varchar2
,p_startup_mode in varchar2
,p_languages in dbms_sql.varchar2s
,p_user_entity_id in out nocopy number
,p_suffix in varchar2
) is
l_base_name ff_database_items.user_name%type;
update_et_tl_dbi_names
(p_leg_code => p_leg_code
,p_bg_id => p_bg_id
,p_startup_mode => p_startup_mode
,p_user_name => l_base_name
,p_user_entity_id => p_user_entity_id
,p_prefixes => p_dbi_prefixes
,p_suffix => p_suffix
,p_date_p => p_date_p
);
end update_dbis;
assert(l_found, 'update_element_type:1', p_element_type_id);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'REPORTING_NAME'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'CLASSIFICATION'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'INPUT_CURRENCY_CODE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'OUTPUT_CURRENCY_CODE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'PROCESSING_PRIORITY'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'CLOSED_FOR_ENTRY'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'CLOSED_FOR_ENTRY_CODE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'END_DATE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'BEN_CLASS'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'LENGTH_OF_SERVICE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'QUALIFYING_UNITS'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'QUALIFYING_UNITS_CODE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'QUALIFYING_AGE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'STANDARD_LINK'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'STANDARD_LINK_CODE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'COSTABLE_TYPE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'COSTABLE_TYPE_CODE'
);
update_dbis
(p_element_type_id => p_element_type_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'COUNT'
);
end update_element_type;
procedure update_element_type
(p_element_type_id in number
,p_effective_date in date
,p_languages in dbms_sql.varchar2s
) is
l_prefixes t_dbi_prefixes;
update_element_type
(p_element_type_id => p_element_type_id
,p_effective_date => p_effective_date
,p_languages => p_languages
,p_dbi_prefixes => l_prefixes
);
end update_element_type;
procedure update_input_value
(p_input_value_id in number
,p_effective_date in date
,p_languages in dbms_sql.varchar2s
,p_dbi_prefixes in t_dbi_prefixes
) is
--
-- Cursor to get input value information.
--
cursor csr_input_value_info
(p_input_value_id in number
,p_effective_date in date
) is
select et.multiple_entries_allowed_flag
, iv.legislation_code
, iv.business_group_id
, iv.uom
, iv.generate_db_items_flag
from pay_input_values_f iv
, pay_element_types_f et
where iv.input_value_id = p_input_value_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
and et.element_type_id = iv.element_type_id
and p_effective_date between
et.effective_start_date and et.effective_end_date
;
procedure update_dbis
(p_input_value_id in number
,p_leg_code in out nocopy varchar2
,p_bg_id in out nocopy number
,p_prefix in out nocopy varchar2
,p_dbi_prefixes in t_dbi_prefixes
,p_date_p in varchar2
,p_startup_mode in varchar2
,p_languages in dbms_sql.varchar2s
,p_user_entity_id in out nocopy number
,p_suffix in varchar2
) is
l_base_name ff_database_items.user_name%type;
update_et_tl_dbi_names
(p_leg_code => p_leg_code
,p_bg_id => p_bg_id
,p_startup_mode => p_startup_mode
,p_user_name => l_base_name
,p_user_entity_id => p_user_entity_id
,p_prefixes => p_dbi_prefixes
,p_suffix => p_suffix
,p_date_p => p_date_p
);
end update_dbis;
assert(l_found, 'update_input_value:1', p_input_value_id);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'UNIT_OF_MEASURE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'UNIT_OF_MEASURE_CODE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'DEFAULT'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'MIN'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'MAX'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'ENTRY_VALUE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'USER_ENTERED_CODE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'START_DATE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'END_DATE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'OVERRIDE_ENTRY_VALUE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'OVERRIDE_USER_ENTERED_CODE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'OVERRIDE_START_DATE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'OVERRIDE_END_DATE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'ENTRY_VALUE'
);
update_dbis
(p_input_value_id => p_input_value_id
,p_leg_code => l_leg_code
,p_bg_id => l_bg_id
,p_prefix => l_prefix
,p_dbi_prefixes => p_dbi_prefixes
,p_date_p => l_date_p
,p_startup_mode => l_startup_mode
,p_languages => p_languages
,p_user_entity_id => l_ueid
,p_suffix => 'OVERRIDE_ENTRY_VALUE'
);
end update_input_value;
procedure update_input_value
(p_input_value_id in number
,p_effective_date in date
,p_languages in dbms_sql.varchar2s
) is
l_prefixes t_dbi_prefixes;
update_input_value
(p_input_value_id => p_input_value_id
,p_effective_date => p_effective_date
,p_languages => p_languages
,p_dbi_prefixes => l_prefixes
);
end update_input_value;
select plr.legislation_code
from pay_legislation_rules plr
where rule_type = 'FF_TRANSLATE_DATABASE_ITEMS'
and rule_mode = 'Y'
;
select null
from user_objects uo
where uo.object_name = p_package_name
and uo.object_type = 'PACKAGE BODY'
and uo.status ='VALID'
;
select pdc.id
, pdc.language
from pay_dyndbi_changes pdc
where pdc.type = p_type
order by 1, 2
;
select et.effective_end_date
from pay_element_types_f et
where et.element_type_id = p_element_type_id
order by 1 desc
;
select iv.effective_end_date
from pay_input_values_f iv
where iv.input_value_id = p_input_value_id
order by 1 desc
;
select to_char(null)
from pay_defined_balances db
where db.defined_balance_id = p_defined_balance_id
;
l_ids.delete;
l_languages.delete;
l_id_langs.delete;
update_element_type
(p_element_type_id => l_id
,p_effective_date => l_eff_date
,p_languages => l_id_langs
);
pay_dyndbi_changes_pkg.delete_rows
(p_id => l_id
,p_type => pay_dyndbi_changes_pkg.c_element_type
);
l_ids.delete;
l_languages.delete;
l_id_langs.delete;
update_input_value
(p_input_value_id => l_id
,p_effective_date => l_eff_date
,p_languages => l_id_langs
);
pay_dyndbi_changes_pkg.delete_rows
(p_id => l_id
,p_type => pay_dyndbi_changes_pkg.c_input_value
);
l_ids.delete;
l_languages.delete;
l_id_langs.delete;
update_defined_balance
(p_defined_balance_id => l_id
,p_languages => l_id_langs
);
pay_dyndbi_changes_pkg.delete_rows
(p_id => l_id
,p_type => pay_dyndbi_changes_pkg.c_defined_balance
);
pay_dbitl_update_errors_pkg.fetch_all_rows
(p_rowids => l_rowids
,p_messages => l_messages
);
pay_dbitl_update_errors_pkg.delete_rows
(p_rowids => l_rowids
);