[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select returns only versions for that
attribute and dimension label combo
Rob Flippo 29-Dec-2003 Fixed read_only_flag select from
FEM_DIM_ATTRIBUTES_B to
assignment_is_read_only;
such members get status update
'MISSING NAME';
attribute did not update _B status
Rob Flippo 06-Jan-2004 Fixed no_data_found problem for
retrieving attr assign value set
Rob Flippo 16-Mar-2004 Modified for Drop3 db. Implemented
Process Locks; Implemented other changes
join (+) to attr_select and member_Select
queries
Rob Flippo 31-Mar-2004 Implemented Error Reprocesing. Fixed
several bugs having to do with outputing
the correct status for failed records,
and preventing members from loading with
insufficient req attr assignments in the
_ATTR_T table.
Rob Flippo 06-May-2004 Implemented changes for Multi-processing.
Also added support for non-value set
attribute dims, like Financial Element,
Dataset, etc.
Rob Flippo 29-June-2004 Remove deprecated code for
BULK_FETCH_LIMIT profile option
Rob Flippo 16-Sep-2004 Bug#3835758 Incorrect logic on checking
the Value Set of Attribute assignments
Bug#3848996 Simple Dimension loads fail
Also fixed several issues with the
status messaging. Previously, error rows
were getting incorrect STATUS.
Also removed the error row count from the
log, since it is not possible for this
number to be accurate due to the fact that
the loader performs several bulk updates
on bad data in the interface tables.
Also fixed the logic on checking attributes
in FEM_DIM_ATTR_GRPS - this has been
changed so that the loader ignores any
entries into FEM_DIM_ATTR_GRPS for required
attributes assigned. Because the DHM is
supposed to prevent such occurrences - only
optional attributes are allowed to be
assigned to a specific group.
Rob Flippo 17-Sep-04 Bug#3843739 DIM LOAD REPROCESS FAILS IF
_TL_T STATUS=LOAD.
This is fixed by modifying the loader so
that when run in Error Reprocessing mode
it loads both 'LOAD' and error status
records.
Bug#3881433 ERROR MESSAGE REQUIRED TO
DEBUG MULTI LANG LOADS THAT FAIL.
The loader now updates the STATUS column
in _TL_T table to "LANGUAGE_NOT_INSTALLED"
for all records where the LANGUAGE value
is not designated as installed in
FND_LANGUAGES.
Rob Flippo 21-Sep-04 Bug#3900960 INSUFFICIENT ERROR MESSAGES
WHEN RUNNING FEM DIMENSION LOADER
- to fix this, add to_char wrapper
on date values inside of the to_date
Rob Flippo 27-Sep-04 Bug#3906366 ADD ABILITY TO
TRACK NUMBER OF ERROR ROWS IN THE MEMBER
LOADER
-- added accumulators for total rows
to load as well as counting all the
various error rows encountered
Bug#3909390 SIMPLE DIM LOADER ISSUE WHEN
SAME MEMBER_CODE IN 2 DIFFT TBLS
-- This was fixed by adding a where
condition on the "existing" mbr select stmt
on dimension_varchar_label when the load
is for simple dims.
Rob Flippo 28-Sep-04 Bug#3906218 NEED ABILITY TO UNDELETE
DIMENSIONS
-- added new procedure
build_enable_update_stmt; modified the
Base_Update procedure to call this new
update
Rob Flippo 01-OCT-04 Bug#3925620 CAL PERIOD LOAD FAILS WHEN
UPDATING CAL_PERIOD_END_DATE ATTRIBUTE IN
INTF TABLE
-- The loader now allows update on
assignment_is_read_only attributes, but
only as long as the update is identical
to the existing assignment.
Rob Flippo 11-OCT-04 Bug#3906182 Enable RCM dimensions for
loading
- added object_definition_id values
to the CASE statement in Main
Rob Flippo 13-OCT-04 Added SIC and Credit Status dims
Rob Flippo 27-OCT-04 Bug#3973837
FEM.C.DP3.4: DIMENSION LOADER
ERRORS ALL ATTRIBUTES IN ATTR_T TABLE
-- this change affected the
build_bad_attr_select_stmt procedure
Rob Flippo 01-NOV-04 Attributes for members with
invalid_dim_grps not getting updated -
modified new_members procedure
so that it continues even if bad drp
or value_set so that the attr records
get updated.
Rob Flippo 11-NOV-04 Bug#4002917 DIM LDR SHOULD PROHIBIT
OVERLAP CAL PERIODS
- modify the start/end date validations
for cal period so that overlaps are
prevented;
Bug#3654256 UPDATE DIMENSION NAMES IN DHM
SHOULD ALSO UPDATE LOADER OBJECT NAMES
-- the loader now requires a dim_id as a
parm and reads the associated object_Def_id
from the fem_Xdim_dimensions table
Rob Flippo 02-FEB-05 Bug#4066869 FEM.D MODIFY DIM MBR LOADER TO
READ DIM_ID AS PARM INSTEAD OF OBJECT_DEF_ID
Bug#4030717 FEM.D: MODIFY DIM MBR LOADER
OVERLAP DATE LOGIC TO ALLOW MP FOR CAL_PERIOD
Bug#3822561 FEM.D.1.DP1: MODIFY DIM MEMBER
LOADER TO SUPPORT ATTRIBUTES OF CAL PERIOD
-- added the CALPATTR columns to support
attributes of CAL_PERIOD dimension;
Rob Flippo 03-MAR-05 Modify base_update so that for dim grp
load we don't
get a unique dimgrp seq error unless the conflict is
caused by a dimension group other than the one
that is being loaded.
Rob Flippo 15-MAR-05 Bug#4226011
- new entity attributes require new col
USER_ASSIGN_ALLOWED_FLAG in FEM_DIM_ATTRIBUTES_B.
Modify all attribute queries to exclude attributes
where user_assign_allowed_flag = 'N'; Also add
update in Pre_Valid_attr to mark rows where the
attribute_varchar_label is
user_assign_allowed_flag='N';
Rob Flippo 22-MAR-05 Bug#4030730 add ability to update dimgrp
of a member
as long as member not in a sequence enforced hier;
build_remain_mbr_select
Rob Flippo 22-APR-05 Bug#4305050 Calendar_ID and Dimension_Group_ID
were being swapped on insert member in
Post_Cal_Periods procedure
sshanmug 28-APR-05 Support for Composite Dimension Loader
Added the following PROCEDURES
Pre_Process
Process_Rows
Get_Display_Codes
sshanmug 09-MAY-05 Modified according to Nico Review comments
Added a PROCEDURE Metadata_Initialize
Rflippo 26-MAY-05 Bug#4355484
Added raise_member_bus_event procedure
to be called
whenever a new member is created
Rob Flippo 02-JUN-05 Bug#4408918 Fix Overlap comparisons to not use
to_char and instead do direct date compare
using bind variables
Rob Flippo 14-JUN-05 Bug numbers:
4107370 - folder security
3920599 - log file changes
3928148 - prevent TL update for
read only members
3895203 - status update for level
specific attr
3923485 - remove date_format_mask parm
4429725 - array out of bounds error
4429443 - changes for splitting out
comp dim loader
Rob Flippo 30-JUN-05 Bug#4355484
CALL BUS EVENT WHEN CCC-ORGS
LOADED WITH DIM LOADER
As long as >0 new members created in a load
the loader calls the bus. event
Rob Flippo 11-AUG-05 Bug#4547868 performance issue -
fixed the Attr_assign_update so that the
member_id and value_set_id are retrieved
in the main attr query, thus allowing
the later query on does_attr_exist
to use those values directly, rather than join
with the base table.
Same applies to the actual
ATTR update statement - it no longer
joins with the base table.
Rob Flippo 07-OCT-05 Bug#4630742 10G issue -
Attr_assign_update fails
on does_attr_exist checks: Modified the fetch
so that both the "version" and
"non-version" queries
are identical for output variables;
Modified Cal period update statement in
build_tl_ro_mbr_upd_stmt for performance
issue encountered during regression
testing (no bug)
Rob Flippo 24-JAN-06 Bug#4927869 Change ICX_DATE_FORMAT to
FEM_INTF_ATTR_DATE_FORMAT_MASK;
new_members and tl_update procedures;
New_Members and Attr_Assign_Update: Added validation
that calp start_date must be <= calp end date
Rob Flippo 18-JUL-06 Bug 5024575 Updates for Many-to-many attributes
Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
Rob Flippo 12-AUG-06 Bug 5459028 fixed bind variable error for
:b_gv_apps_user_id
Naveen Kumar 07-Sep-06 Bug#4429427. Call to FEM_COMP_DIM_MEMBER_LOADER_PKG.Process_Rows
through fem_multi_proc_pkg.MASTER altered to comply with Bind Variable Push
architecture.
Rob Flippo 15-MAR-07 Bug#5900463 - TL rows for other languages
getting deleted when new members are being
loaded - modifications to build_calp_delete_stmt
to fix this
Rob Flippo 15-MAR-07 Bug#5905501 Need to update source_lang so that
translated rows get marked properly
G Cheng 15-FEM-08 6407625 (FP:6256819). Commented out two IF conditions
in get_dimension_info
*******************************************************************/
-------------------------------
-- Declare package variables --
-------------------------------
f_set_status BOOLEAN;
g_select_statement LONG;
procedure build_mbr_select_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_t_name_col IN VARCHAR2
,p_member_description_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_exists_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_select_stmt OUT NOCOPY VARCHAR2);
procedure build_bad_tl_select_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_select_stmt OUT NOCOPY VARCHAR2);
,x_update_stmt OUT NOCOPY VARCHAR2);
,x_update_stmt OUT NOCOPY VARCHAR2);
procedure build_enable_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_load_type IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2);
procedure build_tl_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_load_type IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_name_col IN VARCHAR2
,p_member_description_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2);
,x_select_stmt OUT NOCOPY VARCHAR2);
,x_attr_select_stmt OUT NOCOPY VARCHAR2);
,x_attr_select_stmt OUT NOCOPY VARCHAR2);
,x_attr_select_stmt OUT NOCOPY VARCHAR2);
procedure build_attr_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_outer_join_flag IN VARCHAR2
,p_specific_member_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_attr_select_stmt OUT NOCOPY VARCHAR2);
procedure build_attr_lvlspec_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_outer_join_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_attr_select_stmt OUT NOCOPY VARCHAR2);
procedure build_bad_attr_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_bad_attr_select_stmt OUT NOCOPY VARCHAR2);
,x_bad_member_select_stmt OUT NOCOPY VARCHAR2);
,x_bad_attr_vers_select_stmt OUT NOCOPY VARCHAR2);
procedure build_insert_member_stmt (p_table_handler_name IN VARCHAR2
,p_dimension_id IN NUMBER
,p_value_set_required_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_member_id_method_code VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR
,p_member_name_col IN VARCHAR2
,x_insert_member_stmt OUT NOCOPY VARCHAR2);
procedure build_insert_attr_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_insert_attr_stmt OUT NOCOPY VARCHAR2);
procedure build_status_update_stmt (p_source_table IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2);
procedure build_dep_status_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2);
procedure build_attrlab_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2);
,x_update_status_stmt OUT NOCOPY VARCHAR2);
procedure build_delete_stmt (p_source_table IN VARCHAR2
,x_delete_stmt OUT NOCOPY VARCHAR2);
procedure build_special_delete_stmt (p_source_table IN VARCHAR2
,x_delete_stmt OUT NOCOPY VARCHAR2);
procedure build_remain_mbr_select_stmt (p_load_type IN VARCHAR2
,p_dimension_id IN NUMBER
,p_dimension_varchar_label IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_remain_mbr_select_stmt OUT NOCOPY VARCHAR2);
procedure build_dimgrp_update_stmt (p_target_b_table IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2);
procedure build_attr_update_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2);
procedure build_src_sys_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,x_src_sys_select_stmt OUT NOCOPY VARCHAR2);
,x_select_stmt OUT NOCOPY VARCHAR2);
procedure build_calp_interim_insert_stmt (x_insert_calp_stmt OUT NOCOPY VARCHAR2
,x_insert_calp_attr_stmt OUT NOCOPY VARCHAR2);
procedure build_calp_status_update_stmt (p_operation_mode IN VARCHAR2
,p_source_table IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2);
procedure build_calp_delete_stmt (p_source_table IN VARCHAR2
,p_operation_mode IN VARCHAR2
,x_calp_delete_stmt OUT NOCOPY VARCHAR2);
SELECT NVL(SUM(rows_rejected),0), NVL(SUM(rows_loaded),0)
INTO x_rows_rejected, x_rows_loaded
FROM fem_mp_process_ctl_t
WHERE req_id = gv_request_id;
| created or updated
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Rob Flippo 25-MAY-05 Created
+===========================================================================*/
procedure raise_member_bus_event (p_dimension_varchar_label IN VARCHAR2) IS
v_param_list wf_parameter_list_t;
SELECT FEM_DHM_METADATA_OPS_KEY_S.nextval
INTO v_event_sequence
FROM dual;
SELECT attribute_id, attribute_varchar_label
FROM fem_dim_attributes_b
WHERE dimension_id = p_dimension_id;
SELECT count(*)
INTO v_default_version_count
FROM fem_dim_attr_versions_b
WHERE attribute_id = attr.attribute_id
AND default_version_flag='Y';
v_sql_stmt := 'SELECT count(*) FROM '||x_source_b_table||
' WHERE status '||x_exec_mode_clause||
v_dim_label_where_cond;
v_sql_stmt := 'SELECT count(*) FROM '||x_source_tl_table||
' WHERE status '||x_exec_mode_clause||
v_dim_label_where_cond;
v_sql_stmt := 'SELECT count(*) FROM '||x_source_attr_table||
' WHERE status '||x_exec_mode_clause||
v_dim_label_where_cond;
v_sql_stmt := 'SELECT count(*) FROM fem_dimension_grps_b_t '||
' WHERE dimension_varchar_label ='||
''''||p_dimension_varchar_label||''''||
' AND status '||x_exec_mode_clause;
v_sql_stmt := 'SELECT count(*) FROM fem_dimension_grps_tl_t '||
'WHERE dimension_varchar_label ='||
''''||p_dimension_varchar_label||''''||
' AND status '||x_exec_mode_clause;
SELECT display_name
INTO v_obj_def_display_name
FROM fem_object_definition_vl
WHERE object_definition_id = p_object_definition_id;
P_LAST_UPDATE_LOGIN => gv_login_id,
P_PROGRAM_ID => gv_pgm_id,
P_PROGRAM_LOGIN_ID => gv_login_id,
P_PROGRAM_APPLICATION_ID => gv_pgm_app_id,
P_EXEC_MODE_CODE => p_execution_mode,
P_DIMENSION_ID => null,
P_TABLE_NAME => null,
P_HIERARCHY_NAME => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
P_LAST_UPDATE_LOGIN => gv_login_id,
P_EXEC_MODE_CODE => p_execution_mode,
X_EXEC_STATE => v_exec_state,
X_PREV_REQUEST_ID => v_previous_request_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
P_LAST_UPDATE_LOGIN => gv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
| Updates the PL data model with rows rejected and rows loaded
| Registers the Dimension Load Status
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
|
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Rob Flippo 4-MAR-04 Created
| Rob Flippo 16-SEP-2004 Removing the error row count message
| Since it is impossible to get an accrurate count
| of error rows given that a number of status updates
| are bulk
|
+===========================================================================*/
PROCEDURE Eng_Master_Post_Proc (p_object_id IN NUMBER
,p_rows_rejected_accum IN NUMBER
,p_rows_to_load IN NUMBER)
IS
v_execution_status VARCHAR2(30);
FEM_MULTI_PROC_PKG.Delete_Data_Slices(
p_req_id => gv_request_id);
FEM_PL_PKG.Update_Obj_Exec_Errors(
P_API_VERSION => c_api_version,
P_COMMIT => c_true,
P_REQUEST_ID => gv_request_id,
P_OBJECT_ID => p_object_id,
P_ERRORS_REPORTED => p_rows_rejected_accum,
P_ERRORS_REPROCESSED => 0,
P_USER_ID => gv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
FEM_PL_PKG.Update_Obj_Exec_Status(
P_API_VERSION => c_api_version,
P_COMMIT => c_true,
P_REQUEST_ID => gv_request_id,
P_OBJECT_ID => p_object_id,
P_EXEC_STATUS_CODE => v_execution_status,
P_USER_ID => gv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
FEM_PL_PKG.Update_Request_Status(
P_API_VERSION => c_api_version,
P_COMMIT => c_true,
P_REQUEST_ID => gv_request_id,
P_EXEC_STATUS_CODE => v_execution_status,
P_USER_ID => gv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
| These groups are "bad" so we will update their status to MISSING_NAME
|
| This step also updates the records in the TL_T table where the LANGUAGE
| is not installed.
|
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| 1. Update Status of all "new" members missing TL_T record with 'MISSING_NAME'
| 2. (Simple Dim = 'N') Update Status of attribute assignments where the member
| doesn't exist in FEM and also is not in the join of _B_T/_TL_T with 'INVALID_MEMBER'
| 3. Special Status update for CAL PERIOD attribute assignment records
| with invalid CALENDAR_DISPLAY_CODE or DIMENSION_GROUP_DISPLAY_CODE.
| Status is 'INVALID_MEMBER'
| 4. (Simple Dim = 'N') Update status of all attribute assignment records where the
| VERSION_DISPLAY_CODE doesn't exist. Status is 'INVALID_VERSION'
|
| MODIFICATION HISTORY
| Rob Flippo 22-MAR-04 Created
| Rob Flippo 17-SEP-04 Modified to add update on the _TL_T tables where
| the LANGUAGE is not installed
|
+===========================================================================*/
PROCEDURE Pre_Validation (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN NUMBER
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_master_request_id IN NUMBER)
IS
-- Constants
c_proc_name VARCHAR2(30) := 'Pre_Validation';
x_bad_mbr_select_stmt VARCHAR2(4000);
x_update_mbr_status_stmt VARCHAR2(4000);
,x_bad_mbr_select_stmt);
x_bad_mbr_select_stmt := REPLACE(x_bad_mbr_select_stmt,'{{data_slice}}',p_data_slc);
x_bad_mbr_select_stmt := REPLACE(x_bad_mbr_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.bad_new_mbrs_select_stmt',x_bad_mbr_select_stmt);
build_status_update_stmt (p_source_b_table
,x_update_mbr_status_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.update_mbr_status_stmt '
,x_update_mbr_status_stmt);
OPEN cv_get_bad_mbr FOR x_bad_mbr_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_bad_mbr FOR x_bad_mbr_select_stmt;
EXECUTE IMMEDIATE x_update_mbr_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
t_rowid.DELETE;
t_b_status.DELETE;
| These assignments are "bad" so we will update their status to INVALID_VERSION
|
| Attribute assignment rows where the member doesn't exist in FEM
| and the member doesn't exist in the join of the _B_T and _TL_T tables.
| The member in this case is either a 'MISSING_NAME' member in the _B_T,
| or it is just a non-existent member. These attribute assignment rows are
| marked as 'INVALID_MEMBER'.
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| Rob Flippo 15-APR-04 Created
| Rob Flippo 15-MAR-05 Bug#4226011 added call to not_user_label procedure
| to update attr_t records where the attribute
| is user_assign_allowed_flag = 'N';
| Rob Flippo 09-JUN-05 Bug#3895203 modified to update level specific
| attributes
+===========================================================================*/
PROCEDURE Pre_Validation_Attr (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_master_request_id IN NUMBER)
IS
-- Constants
c_proc_name VARCHAR2(30) := 'Pre_Validation_Attr';
x_bad_attr_select_stmt VARCHAR2(4000);
x_bad_attr_vers_select_stmt VARCHAR2(4000);
x_update_attr_status_stmt VARCHAR2(4000);
x_attr_lvlspec_select_stmt VARCHAR2(4000);
build_attrlab_update_stmt (p_dimension_varchar_label
,p_source_attr_table
,p_shared_dimension_flag
,p_exec_mode_clause
,x_bad_attrlab_status_stmt);
build_status_update_stmt (p_source_attr_table
,x_update_attr_status_stmt);
build_bad_attr_select_stmt (p_dimension_varchar_label
,p_dimension_id
,p_source_b_table
,p_source_tl_table
,p_source_attr_table
,p_target_b_table
,p_member_t_dc_col
,p_member_dc_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_exec_mode_clause
,x_bad_attr_select_stmt);
x_bad_attr_select_stmt := REPLACE(x_bad_attr_select_stmt,'{{data_slice}}',p_data_slc);
x_bad_attr_select_stmt := REPLACE(x_bad_attr_select_stmt,'{{data_slice}}','1=1');
build_attr_lvlspec_select_stmt (p_dimension_varchar_label
,p_dimension_id
,p_source_b_table
,p_source_attr_table
,p_target_b_table
,p_member_t_dc_col
,p_member_dc_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_hier_dimension_flag
,'Y'
,p_exec_mode_clause
,x_attr_lvlspec_select_stmt);
x_attr_lvlspec_select_stmt := REPLACE(x_attr_lvlspec_select_stmt,'{{data_slice}}',p_data_slc);
x_attr_lvlspec_select_stmt := REPLACE(x_attr_lvlspec_select_stmt,'{{data_slice}}','1=1');
,x_bad_attr_vers_select_stmt);
x_bad_attr_vers_select_stmt := REPLACE(x_bad_attr_vers_select_stmt,'{{data_slice}}',p_data_slc);
x_bad_attr_vers_select_stmt := REPLACE(x_bad_attr_vers_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_vers_select_stmt',x_bad_attr_vers_select_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
,x_update_attr_status_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_select_stmt'
,x_bad_attr_select_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.attr_lvlspec_select_stmt'
,x_attr_lvlspec_select_stmt);
OPEN cv_get_bad_attr FOR x_bad_attr_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_bad_attr FOR x_bad_attr_select_stmt;
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
t_rowid.DELETE;
t_b_status.DELETE;
OPEN cv_get_bad_attr FOR x_attr_lvlspec_select_stmt USING v_slc_val, v_slc_val2;
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
t_rowid.DELETE;
t_b_status.DELETE;
x_special_calp_status_stmt := 'UPDATE fem_cal_periods_attr_t B'||
' SET status = ''INVALID_CALENDAR'''||
' WHERE NOT EXISTS (SELECT 0 FROM fem_calendars_b C2'||
' WHERE C2.calendar_display_code = B.calendar_display_code)'||
' AND {{data_slice}} '||
' AND B.STATUS'||p_exec_mode_clause;
x_special_calp_status_stmt := 'UPDATE fem_cal_periods_attr_t B'||
' SET status = ''INVALID_DIMENSION_GROUP'''||
' WHERE NOT EXISTS (SELECT 0 FROM fem_dimension_grps_b D2'||
' WHERE D2.dimension_group_display_code = B.dimension_group_display_code)'||
' AND {{data_slice}} '||
' AND EXISTS (SELECT 0 FROM FEM_CALENDARS_B C3'||
' WHERE C3.calendar_display_code = B.calendar_display_code)'||
' AND B.STATUS'||p_exec_mode_clause;
END IF; -- special CAL_PERIOD status update
(c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_vers_select_stmt',x_bad_attr_vers_select_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
,x_update_attr_status_stmt);
OPEN cv_get_bad_attr_vers FOR x_bad_attr_vers_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_bad_attr_vers FOR x_bad_attr_vers_select_stmt;
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
t_rowid.DELETE;
t_b_status.DELETE;
| to perform validations and eventually insert into the target dimension member tables
| In the case where the user provides multiple versions of the attribute
| assignments for the same member, only the "default" version is read for "required"
| attributes
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| New Members
|
| 1. Retrieve members from join of _B_T/_TL_T where the member does not
| already exist in FEM.
| 2. If Simple Dim = 'N', for each member, retrieve all "required" attribute
| assignment records from _ATTR_T where the VERSION_DISPLAY_CODE exists.
| 3. If number attribute assignments < number of required attributes, the member
| fails with status = 'MISSING_REQUIRED_ATTRIBUTE'. The _TL_T gets
| 'INVALID_MEMBER' and all of the _ATTR_T records get 'INVALID_MEMBER'.
| 4. If the number of attribute assignments = number of required attributes,
| then each assignment record is validated individually:
| For Dimension Groups - check the Dim Group Sequence. Failure =
| 'DIM_GROUP_SEQUENCE_NOT_UNIQUE'.
| For Dimension Groups (CAL_PERIOD only) - check Time Group Type Code.
| Failure = 'INVALID_TIME_GROUP_TYPE'.
| If a DIMENSION assignment, then assignment value must exist in the
| attribute dimension table. Status = 'INVALID_DIM_ASSIGNMENT' for failure
| If a DATE assignment, then assignment value must be a valid date.
| Status='INVALID_DATE' for failure
| If a NUMBER assignment, then assignment value must be a valid number.
| Status = 'INVALID_NUMBER' for failure.
| Special CAL_PERIOD validation on GL_PERIOD_NUM attribute - attribute
| assignment must match value in the CAL_PERIOD_NUMBER identifier column
| Status = 'INVALID_CAL_PERIOD_END_DATE' for failure
|
| The _B_T gets 'INVALID_REQUIRED_ATTRIBUTE' whenever one fails. The
| _TL_T gets 'INVALID_MEMBER'.
| 5. Insert success records into FEM. Only the _TL_T record for the user's
| current session is used to initially create the member - records for
| other installed languages default to that value for this stage.
| 6. Delete success records from _B_T/_TL_T/_ATTR_T.
|
| SPECIAL NOTE: The x_rows_loaded output variable is only populated if a
| new member is created. It does not represent the number of
| rows loaded. Rather, it is just serving as a flag to indicate
| that at least one new member was created.
| MODIFICATION HISTORY
| Rob Flippo 22-MAR-04 Created
| Rob Flippo 13-SEP-04 Bug#3835758 Validation on the attr_Assign_vs
| was modified to include dimension_id
| in the where condition
|
| Rob Flippo 14-SEP-04 Removed the build_dep_status_update procedure call
| since attribute records where the member is invalid
| is already handled in the attr_update section
| Rob Flippo 15-SEP-04 Bug#3835758 Added exception handler so no failure
| if VS didn't exist. Also moved the section
| that verifies the VS so that only called
| for DIMENSION attributes;
| The loader inserts from the final array into
| these new tables, then performs date overlap
| check before moving all good records into FEM
| Rob Flippo 30-JUN-05 Bug#4355484
| CALL BUS EVENT WHEN CCC-ORGS LOADED WITH DIM LOADER
| As long as >0 new members created in a load, the
| loader calls the bus. event
| Rob Flippo 10-MAR-06 Bug#5068022 - unique constraint error on duplicate
| names
| Rob Flippo 04-APR-06 Bug#5117594 Remove unique name check for Customer
| dimension
| Rob Flippo 28-APR-06 Bug 5174039 Added validation that calp start_date
| must be <= calp end date
| Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
+===========================================================================*/
PROCEDURE New_Members (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_date_format_mask IN VARCHAR2
,p_dimension_id IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_target_attr_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_table_handler_name IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_name_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_t_name_col IN VARCHAR2
,p_member_description_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_member_id_method_code IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_master_request_id IN NUMBER)
IS
-- Constants
c_proc_name VARCHAR2(30) := 'New_Members';
x_select_stmt VARCHAR2(4000);
x_attr_select_stmt VARCHAR2(4000);
x_remain_mbr_select_stmt VARCHAR2(4000);
x_insert_member_stmt VARCHAR2(4000);
x_insert_attr_stmt VARCHAR2(4000);
x_update_stmt VARCHAR2(4000);
x_attr_update_stmt VARCHAR2(4000);
x_update_tl_stmt VARCHAR2(4000);
x_update_dep_attr_status_stmt VARCHAR2(4000);
x_update_dep_tl_status_stmt VARCHAR2(4000);
x_update_attr_status_stmt VARCHAR2(4000);
x_update_mbr_status_stmt VARCHAR2(4000);
x_update_tl_status_stmt VARCHAR2(4000);
x_update_dimgrp_stmt VARCHAR2(4000);
x_delete_attr_stmt VARCHAR2(4000);
x_special_delete_attr_stmt VARCHAR2(4000);
x_delete_mbr_stmt VARCHAR2(4000);
x_delete_tl_stmt VARCHAR2(4000);
build_mbr_select_stmt (p_load_type
,p_dimension_varchar_label
,p_dimension_id
,p_target_b_table
,p_target_tl_table
,p_source_b_table
,p_source_tl_table
,p_member_dc_col
,p_member_t_dc_col
,p_member_t_name_col
,p_member_description_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_hier_dimension_flag
,'N'
,p_exec_mode_clause
,x_select_stmt);
x_select_stmt := REPLACE(x_select_stmt,'{{data_slice}}',p_data_slc);
x_select_stmt := REPLACE(x_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.member select_stmt'
,x_select_stmt);
build_insert_member_stmt (p_table_handler_name
,p_dimension_id
,p_value_set_required_flag
,p_hier_dimension_flag
,p_simple_dimension_flag
,p_member_id_method_code
,p_member_col
,p_member_dc_col
,p_member_name_col
,x_insert_member_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.insert member stmt'
,x_insert_member_stmt);
build_status_update_stmt (p_source_b_table
,x_update_mbr_status_stmt);
build_status_update_stmt (p_source_tl_table
,x_update_tl_status_stmt);
build_delete_stmt (p_source_b_table
,x_delete_mbr_stmt);
build_delete_stmt (p_source_tl_table
,x_delete_tl_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.dupname select stmt'
,x_dupname_count_stmt);
build_attr_select_stmt (p_dimension_varchar_label
,p_dimension_id
,p_source_b_table
,p_source_attr_table
,p_target_b_table
,p_member_t_dc_col
,p_member_dc_col
,p_member_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_hier_dimension_flag
,'Y'
,'Y'
,p_exec_mode_clause
,x_attr_select_stmt);
x_attr_select_stmt := REPLACE(x_attr_select_stmt,'{{data_slice}}',p_data_slc);
x_attr_select_stmt := REPLACE(x_attr_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.attribute select stmt'
,x_attr_select_stmt);
build_status_update_stmt (p_source_attr_table
,x_update_attr_status_stmt);
build_dep_status_update_stmt (p_dimension_varchar_label
,p_source_attr_table
,p_member_t_dc_col
,p_value_set_required_flag
,x_update_dep_attr_status_stmt); */
build_insert_attr_stmt (p_target_attr_table
,p_target_b_table
,p_member_col
,p_member_dc_col
,p_value_set_required_flag
,x_insert_attr_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.insert attr stmt'
,x_insert_attr_stmt);
build_delete_stmt (p_source_attr_table
,x_delete_attr_stmt);
build_calp_interim_insert_stmt(x_calp_interim_stmt
,x_calp_attr_interim_stmt);
OPEN cv_get_rows FOR x_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_rows FOR x_select_stmt;
SELECT A.number_assign_value
INTO t_periods_in_year(i)
FROM fem_time_grp_types_attr A
,fem_dim_attributes_b DA
,fem_dim_attr_versions_B V
,fem_dimension_grps_b DG
WHERE A.time_group_type_code = DG.time_group_type_code
AND DG.dimension_group_id = t_dimension_group_id(i)
AND A.attribute_id = DA.attribute_id
AND DA.dimension_id = 33
AND DA.attribute_varchar_label = 'PERIODS_IN_YEAR'
AND A.version_id = V.version_id
AND V.default_version_flag = 'Y'
AND V.aw_snapshot_flag = 'N'
AND V.attribute_id = DA.attribute_id;
SELECT count(*)
INTO t_seq_conflict_count(i)
FROM fem_dimension_grps_b
WHERE dimension_group_seq = t_dimension_group_seq(i)
AND dimension_id = p_dimension_id;
SELECT count(*)
INTO v_count
FROM fem_time_group_types_b
WHERE time_group_type_code = t_time_group_type_code(i);
SELECT count(*)
INTO v_req_attribute_count
FROM fem_dim_attributes_b
WHERE dimension_id = p_dimension_id
AND attribute_required_flag = 'Y'
AND nvl(user_assign_allowed_flag,'Y') NOT IN ('N');
AND (attribute_id NOT IN (SELECT attribute_id FROM fem_dim_attr_grps)
OR attribute_id IN (SELECT attribute_id
FROM fem_dim_attr_grps
WHERE dimension_group_id = t_dimension_group_id(i))); */
OPEN cv_get_attr_rows FOR x_attr_select_stmt
USING t_member_dc(i)
,t_value_set_dc(i);
OPEN cv_get_attr_rows FOR x_attr_select_stmt
USING t_member_dc(i);
x_adj_period_stmt := 'select count(*)'||
' from fem_cal_periods_attr_t A1'||
' where A1.calendar_display_code = '''||t_calendar_dc(i)||''''||
' and A1.dimension_group_display_code = '''||t_dimension_group_dc(i)||''''||
' and A1.cal_period_number = '''||t_cal_period_number(i)||''''||
' and to_char(A1.cal_period_end_date,'''||p_date_format_mask||''')'||
' = '''||to_char(t_cal_period_end_date(i),p_date_format_mask)||''''||
' and A1.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and A1.attribute_assign_value = ''Y''';
x_adj_period_stmt := 'select count(*)'||
' from fem_cal_periods_attr_t A1'||
' where A1.calendar_display_code = :b_cal_dc'||
' and A1.dimension_group_display_code = :b_dimgrp_dc'||
' and A1.cal_period_number = :b_calp_nbr'||
' and A1.cal_period_end_date'||
' = :b_calp_end_date'||
' and A1.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and A1.attribute_assign_value = ''Y''';
'select count(*)'||
' from fem_cal_periods_attr CS, fem_cal_periods_attr CE,'||
' fem_cal_periods_b C,'||
' fem_dim_attributes_b AE,'||
' fem_dim_attr_Versions_b VE,'||
' fem_cal_periods_attr CP,'||
' fem_dim_attributes_b AP,'||
' fem_dim_attr_versions_b VP'||
' where CS.cal_period_id = C.cal_period_id'||
' and C.cal_period_id = CP.cal_period_id'||
' and C.enabled_flag = ''Y'''||
' and CP.attribute_id = AP.attribute_id'||
' and CP.version_id = VP.version_id'||
' and CP.dim_attribute_varchar_member = ''N'''||
' and AP.dimension_id = 1'||
' and AP.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and VP.attribute_id = AP.attribute_id'||
' and VP.default_version_flag = ''Y'''||
' and VP.aw_snapshot_flag = ''N'''||
' and C.calendar_id = :b_cal_id'||
' and C.dimension_group_id = :b_dimgrp_id'||
' and CS.attribute_id = :b_attr_id'||
' and CS.version_id = :b_vers_id'||
' and CS.date_assign_value'||
' <= :b_new_end_date'||
' and CS.cal_period_id = CE.cal_period_id'||
' and CE.attribute_id = AE.attribute_id'||
' and AE.attribute_varchar_label = ''CAL_PERIOD_END_DATE'''||
' and CE.version_id = VE.version_id'||
' and VE.aw_snapshot_flag = ''N'''||
' and VE.default_version_flag = ''Y'''||
' and VE.attribute_id = AE.attribute_id'||
' and CE.date_assign_value'||
' >= :b_new_start_date';
'select count(*)'||
' from fem_cal_periods_attr CS, fem_cal_periods_attr CE,'||
' fem_cal_periods_b C,'||
' fem_dim_attributes_b AE,'||
' fem_dim_attr_Versions_b VE,'||
' fem_cal_periods_attr CP,'||
' fem_dim_attributes_b AP,'||
' fem_dim_attr_versions_b VP'||
' where CS.cal_period_id = C.cal_period_id'||
' and C.cal_period_id = CP.cal_period_id'||
' and C.enabled_flag = ''Y'''||
' and CP.attribute_id = AP.attribute_id'||
' and CP.version_id = VP.version_id'||
' and CP.dim_attribute_varchar_member = ''N'''||
' and AP.dimension_id = 1'||
' and AP.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and VP.attribute_id = AP.attribute_id'||
' and VP.default_version_flag = ''Y'''||
' and VP.aw_snapshot_flag = ''N'''||
' and C.calendar_id = '||t_calendar_id(i)||
' and C.dimension_group_id = '||t_dimension_group_id(i)||
' and CS.attribute_id = '||ta_attribute_id(j)||
' and CS.version_id = '||ta_version_id(j)||
' and CS.date_assign_value'||
' <= :b_new_end_date'||
' and CS.cal_period_id = CE.cal_period_id'||
' and CE.attribute_id = AE.attribute_id'||
' and AE.attribute_varchar_label = ''CAL_PERIOD_END_DATE'''||
' and CE.version_id = VE.version_id'||
' and VE.aw_snapshot_flag = ''N'''||
' and VE.default_version_flag = ''Y'''||
' and VE.attribute_id = AE.attribute_id'||
' and CE.date_assign_value'||
' >= :b_new_start_date';
'select count(*)'||
' from fem_cal_periods_attr CS,'||
' fem_cal_periods_b C,'||
' fem_cal_periods_attr CP,'||
' fem_dim_attributes_b AP,'||
' fem_dim_attr_versions_b VP'||
' where CS.cal_period_id = C.cal_period_id'||
' and C.cal_period_id = CP.cal_period_id'||
' and C.enabled_flag = ''Y'''||
' and CP.attribute_id = AP.attribute_id'||
' and CP.version_id = VP.version_id'||
' and CP.dim_attribute_varchar_member = ''N'''||
' and AP.dimension_id = 1'||
' and AP.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and VP.attribute_id = AP.attribute_id'||
' and VP.default_version_flag = ''Y'''||
' and VP.aw_snapshot_flag = ''N'''||
' and C.calendar_id = '||t_calendar_id(i)||
' and C.dimension_group_id = '||t_dimension_group_id(i)||
' and CS.attribute_id = '||ta_attribute_id(j)||
' and CS.version_id = '||ta_version_id(j)||
' and to_char(CS.date_assign_value,'''||p_date_format_mask||''')'||
' >= '''||to_char(ta_date_assign_value(j),p_date_format_mask)||''''||
' and to_char(CS.date_assign_value,'''||p_date_format_mask||''')'||
' <= '''||to_char(t_cal_period_end_date(i),p_date_format_mask)||'''';
SELECT value_set_id
INTO ta_attr_assign_vs_id(j)
FROM fem_value_sets_b
WHERE value_set_display_code = ta_attr_assign_vs_dc(j)
AND dimension_id = ta_attribute_dimension_id(j);
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING ta_status(i)
,ta_rowid(i)
,ta_status(i);
ta_rowid.DELETE;
ta_member_read_only_flag.DELETE;
ta_attribute_id.DELETE;
ta_attribute_varchar_label.DELETE;
ta_attribute_dimension_id.DELETE;
ta_attr_value_column_name.DELETE;
ta_attribute_data_type_code.DELETE;
ta_attribute_required_flag.DELETE;
ta_read_only_flag.DELETE;
ta_allow_mult_versions_flag.DELETE;
ta_allow_mult_assign_flag.DELETE;
ta_member_dc.DELETE;
ta_value_set_dc.DELETE;
ta_attribute_assign_value.DELETE;
ta_dim_attr_numeric_member.DELETE;
ta_dim_attr_varchar_member.DELETE;
ta_number_assign_value.DELETE;
ta_varchar_assign_value.DELETE;
ta_date_assign_value.DELETE;
ta_version_display_code.DELETE;
ta_version_id.DELETE;
ta_language.DELETE;
ta_attr_assign_vs_dc.DELETE;
ta_attr_assign_vs_id.DELETE;
ta_status.DELETE;
ta_use_interim_table_flag.DELETE;
ta_calpattr_cal_dc.DELETE;
ta_calpattr_dimgrp_dc.DELETE;
ta_calpattr_end_date.DELETE;
ta_calpattr_period_num.DELETE;
SELECT fem_dimension_grps_b_s.nextval
INTO tf_dimgrp_dimension_group_id(v_mbr_final_count)
FROM dual;
SELECT fem_time_dimension_group_key_s.nextval
INTO tf_time_dimension_group_key(v_mbr_final_count)
FROM dual;
(c_log_level_3,c_block||'.'||c_proc_name||'.Member_Insert',
null);
EXECUTE IMMEDIATE x_insert_member_stmt
USING tf_member_dc(i)
,tf_member_name(i)
,tf_member_desc(i)
,gv_apps_user_id
,gv_apps_user_id
,tf_time_dimension_group_key(i)
,tf_dimension_group_seq(i)
,tf_time_group_type_code(i)
,tf_dimgrp_dimension_group_id(i)
,tf_dimension_id(i);
(c_log_level_1,c_block||'.'||c_proc_name||'.update member status.p_value_set_req_flag'
,p_value_set_required_flag);
(c_log_level_1,c_block||'.'||c_proc_name||'.update member status.p_hier_dim_flag'
,p_hier_dimension_flag);
(c_log_level_1,c_block||'.'||c_proc_name||'.update member status.gv_apps_user_id'
,gv_apps_user_id);
(c_log_level_1,c_block||'.'||c_proc_name||'.update member status.in the forall'
,null);
EXECUTE IMMEDIATE x_insert_member_stmt
USING tf_value_set_id(i)
,tf_dimension_group_id(i)
,tf_member_dc(i)
,tf_member_name(i)
,tf_member_desc(i)
,gv_apps_user_id
,gv_apps_user_id;
EXECUTE IMMEDIATE x_insert_member_stmt
USING tf_value_set_id(i)
,tf_member_dc(i)
,tf_member_name(i)
,tf_member_desc(i)
,gv_apps_user_id
,gv_apps_user_id;
This statement is replaced with an insert into the
CAL_PERIOD INTERIM tables.
FORALL i IN 1..v_final_mbr_last_row
EXECUTE IMMEDIATE x_insert_member_stmt
USING tf_cal_period_end_date(i)
,tf_cal_period_number(i)
,tf_calendar_id(i)
,tf_dimension_group_id(i)
,tf_member_name(i)
,tf_member_desc(i);
EXECUTE IMMEDIATE x_insert_member_stmt
USING tf_dimension_group_id(i)
,tf_member_dc(i)
,tf_member_name(i)
,tf_member_desc(i)
,gv_apps_user_id
,gv_apps_user_id;
EXECUTE IMMEDIATE x_insert_member_stmt
USING tf_member_dc(i)
,tf_member_name(i)
,tf_member_desc(i)
,gv_apps_user_id
,gv_apps_user_id;
EXECUTE IMMEDIATE x_insert_attr_stmt
USING tfa_attribute_id(i)
,tfa_version_id(i)
,tfa_dim_attr_numeric_member(i)
,tfa_attr_assign_vs_id(i)
,tfa_dim_attr_varchar_member(i)
,tfa_number_assign_value(i)
,tfa_varchar_assign_value(i)
,tfa_date_assign_value(i)
,gv_apps_user_id
,gv_apps_user_id
,tfa_member_dc(i)
,tfa_value_set_dc(i)
,tfa_status(i);
EXECUTE IMMEDIATE x_insert_attr_stmt
USING tfa_attribute_id(i)
,tfa_version_id(i)
,tfa_dim_attr_numeric_member(i)
,tfa_attr_assign_vs_id(i)
,tfa_dim_attr_varchar_member(i)
,tfa_number_assign_value(i)
,tfa_varchar_assign_value(i)
,tfa_date_assign_value(i)
,gv_apps_user_id
,gv_apps_user_id
,tfa_member_dc(i)
,tfa_status(i);
EXECUTE IMMEDIATE x_update_mbr_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
(c_log_level_1,c_block||'.'||c_proc_name||'.update member status.v_mbr_last_row'
,v_mbr_last_row);
EXECUTE IMMEDIATE x_update_tl_status_stmt
USING t_tl_status(i)
,t_tl_rowid(i)
,t_tl_status(i);
(c_log_level_1,c_block||'.'||c_proc_name||'.update tl status.v_mbr_last_row'
,v_mbr_last_row);
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING tfa_status(i)
,tfa_rowid(i)
,tfa_status(i);
/* 9/14/2004 RCF Commenting this out because this update is already handled
in the attr_update step for bad_versions and bad_members
IF (p_value_set_required_flag = 'Y') THEN
FORALL i IN 1..v_mbr_last_row
EXECUTE IMMEDIATE x_update_dep_attr_status_stmt
USING t_member_dc(i)
,t_value_set_dc(i)
,t_b_status(i);
EXECUTE IMMEDIATE x_update_dep_attr_status_stmt
USING t_calendar_dc(i)
,t_dimension_group_dc(i)
,t_cal_period_end_date(i)
,t_cal_period_number(i)
,t_b_status(i);
EXECUTE IMMEDIATE x_delete_attr_stmt
USING tfa_rowid(i)
,tfa_status(i)
,'N'; -- use_interim_table_flag
tfa_rowid.DELETE;
tfa_attribute_id.DELETE;
tfa_attribute_varchar_label.DELETE;
tfa_member_dc.DELETE;
tfa_value_set_dc.DELETE;
tfa_dim_attr_numeric_member.DELETE;
tfa_dim_attr_varchar_member.DELETE;
tfa_number_assign_value.DELETE;
tfa_varchar_assign_value.DELETE;
tfa_date_assign_value.DELETE;
tfa_version_id.DELETE;
tfa_attr_assign_vs_id.DELETE;
tfa_status.DELETE;
EXECUTE IMMEDIATE x_delete_mbr_stmt
USING tf_rowid(i)
,tf_status(i)
,'N'; -- use_interim_table_flag
EXECUTE IMMEDIATE x_delete_tl_stmt
USING tf_tl_rowid(i)
,tf_status(i)
,'N'; -- use_interim_table_flag
tf_rowid.DELETE;
tf_tl_rowid.DELETE;
tf_member_dc.DELETE;
tf_calendar_id.DELETE;
tf_cal_period_end_date.DELETE;
tf_cal_period_number.DELETE;
tf_value_set_id.DELETE;
tf_dimension_group_id.DELETE;
tf_member_name.DELETE;
tf_member_desc.DELETE;
tf_language.DELETE;
tf_status.DELETE;
tf_dimension_group_seq.DELETE;
tf_time_group_type_code.DELETE;
tf_time_dimension_group_key.DELETE;
tf_calendar_dc.DELETE;
tf_dimension_group_dc.DELETE;
tf_cal_period_start_date.DELETE;
tf_adj_period_flag.DELETE;
t_rowid.DELETE;
t_tl_rowid.DELETE;
t_member_dc.DELETE;
t_calendar_dc.DELETE;
t_calendar_id.DELETE;
t_cal_period_end_date.DELETE;
t_cal_period_start_date.DELETE;
t_cal_period_number.DELETE;
t_value_set_dc.DELETE;
t_value_set_id.DELETE;
t_dimension_group_dc.DELETE;
t_dimension_group_id.DELETE;
t_b_status.DELETE;
t_member_name.DELETE;
t_member_desc.DELETE;
t_language.DELETE;
t_tl_status.DELETE;
t_dimension_group_seq.DELETE;
t_time_group_type_code.DELETE;
t_adj_period_flag.DELETE;
| TL_Update
|
| DESCRIPTION
| This step updates the translatable names and descriptions for existing
| Dimension members.
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
|
| 1. Other language rows in FEM are updated for the member if they exist.
| 2. Delete any TL rows that are successfully updated.
|
| Update Base (Dimension Group and Simple Dim = 'N' only)
| Note: This module only performs error checks. It does not perform any updates.
| Therefore, the Base status will remain unchanged, unless
| there is something invalid about one of the values in a base columns, in which
| case the Base Update module identifies the error and updates the status
| 1. Base columns for the member or Group are updated for any base records in
| the _B_T table. This update only occurs if the member exists in FEM.
| 2. Delete any _B_T records that are successful.
|
| MODIFICATION HISTORY
| Rob Flippo 8-MAR-04 Created
| Rob Flippo 10-MAR-06 Bug#5068022 add error checking to identify
| translatable names in the TL_T table that
| already exist in the target TL table for
| other members
| Rob Flippo 04-APR-06 Bug#5117594 Remove unique name check for Customer
| dimension
| Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
| Rob Flippo 15-MAR-07 Bug#5905501 Modify execute immediate on tl_update
| to pass in the source_lang = language
+===========================================================================*/
PROCEDURE TL_Update (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_name_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_t_name_col IN VARCHAR2
,p_member_description_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_master_request_id IN NUMBER)
IS
-- Constants
c_proc_name VARCHAR2(30) := 'TL_Update';
x_bad_tl_select_stmt VARCHAR2(4000);
x_select_stmt VARCHAR2(4000);
x_update_stmt VARCHAR2(4000);
x_update_tl_stmt VARCHAR2(4000);
x_update_tl_status_stmt VARCHAR2(4000);
x_delete_tl_stmt VARCHAR2(4000);
build_mbr_select_stmt (p_load_type
,p_dimension_varchar_label
,p_dimension_id
,p_target_b_table
,p_target_tl_table
,p_source_b_table
,p_source_tl_table
,p_member_dc_col
,p_member_t_dc_col
,p_member_t_name_col
,p_member_description_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_hier_dimension_flag
,'Y'
,p_exec_mode_clause
,x_select_stmt);
build_bad_tl_select_stmt (p_load_type
,p_dimension_varchar_label
,p_dimension_id
,p_target_b_table
,p_target_tl_table
,p_source_b_table
,p_source_tl_table
,p_member_dc_col
,p_member_t_dc_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_hier_dimension_flag
,p_exec_mode_clause
,x_bad_tl_select_stmt);
x_select_stmt := REPLACE(x_select_stmt,'{{data_slice}}',p_data_slc);
x_bad_tl_select_stmt := REPLACE(x_bad_tl_select_stmt,'{{data_slice}}',p_data_slc);
x_select_stmt := REPLACE(x_select_stmt,'{{data_slice}}','1=1');
x_bad_tl_select_stmt := REPLACE(x_bad_tl_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.member select stmt'
,x_select_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.bad_tl_select_stmt'
,x_bad_tl_select_stmt);
build_tl_update_stmt (p_dimension_varchar_label
,p_dimension_id
,p_load_type
,p_target_b_table
,p_target_tl_table
,p_member_col
,p_member_dc_col
,p_member_name_col
,p_member_description_col
,p_value_set_required_flag
,x_update_tl_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.tl table update stmt'
,x_update_tl_stmt);
build_delete_stmt (p_source_tl_table
,x_delete_tl_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.delete stmt'
,x_delete_tl_stmt);
build_status_update_stmt (p_source_tl_table
,x_update_tl_status_stmt);
,'TL_UPDATE'
,x_dupname_count_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.dupname select stmt'
,x_dupname_count_stmt);
OPEN cv_get_rows FOR x_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_rows FOR x_select_stmt;
EXECUTE IMMEDIATE x_update_tl_stmt
USING t_member_name(i)
,t_member_desc(i)
,t_language(i)
,gv_apps_user_id
,gv_login_id
,t_member_dc(i)
,t_value_set_dc(i)
,t_value_set_dc(i)
,t_language(i)
,t_tl_status(i);
EXECUTE IMMEDIATE x_update_tl_stmt
USING t_member_name(i)
,t_member_desc(i)
,t_language(i)
,gv_apps_user_id
,gv_login_id
,t_member_dc(i)
,t_language(i)
,t_tl_status(i);
EXECUTE IMMEDIATE x_delete_tl_stmt
USING t_tl_rowid(i)
,t_tl_status(i)
,'N';
EXECUTE IMMEDIATE x_update_tl_status_stmt
USING t_tl_status(i)
,t_tl_rowid(i)
,t_tl_status(i);
t_rowid.DELETE;
t_tl_rowid.DELETE;
t_member_dc.DELETE;
t_calendar_dc.DELETE;
t_calendar_id.DELETE;
t_cal_period_end_date.DELETE;
t_cal_period_number.DELETE;
t_value_set_dc.DELETE;
t_value_set_id.DELETE;
t_dimension_group_dc.DELETE;
t_dimension_group_id.DELETE;
t_b_status.DELETE;
t_member_name.DELETE;
t_member_desc.DELETE;
t_language.DELETE;
t_tl_status.DELETE;
t_dimension_group_seq.DELETE;
t_time_group_type_code.DELETE;
OPEN cv_get_rows FOR x_bad_tl_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_rows FOR x_bad_tl_select_stmt;
EXECUTE IMMEDIATE x_update_tl_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
t_rowid.DELETE;
t_tl_rowid.DELETE;
t_member_dc.DELETE;
t_calendar_dc.DELETE;
t_calendar_id.DELETE;
t_cal_period_end_date.DELETE;
t_cal_period_number.DELETE;
t_value_set_dc.DELETE;
t_value_set_id.DELETE;
t_dimension_group_dc.DELETE;
t_dimension_group_id.DELETE;
t_b_status.DELETE;
t_member_name.DELETE;
t_member_desc.DELETE;
t_language.DELETE;
t_tl_status.DELETE;
t_dimension_group_seq.DELETE;
t_time_group_type_code.DELETE;
END TL_Update;
| Base_Update
|
| DESCRIPTION
| These are all of the records in the _B_T table that are existing
| dimension members in FEM. For these we will update any
| "Base" table columns.
| Note: Inserts of new attribute assignments for existing members, as well
| as updates of existing attribute assignments, are handled in the Update_Attr module
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
| This procedure does not perform any updates of the FEM tables at the moment
| since the rules for updating the Dimension Group are unclear
|
| MODIFICATION HISTORY
| Rob Flippo 8-MAR-04 Created
| Rob Flippo 28-SEP-04 Bug#3906218 Ability undelete members
| Rob Flippo 03-MAR-05 Modify so that for dim grp load we don't get a
| unique dimgrp seq error unless the conflict is
| caused by a dimension group other than the one
| that is being loaded.
| Rob Flippo 16-MAR-05 Bug#4244082 Modify to update dimgrp as long as
| member does not participate in a sequence enforced
| hier
| -- note - if the dimgrp doesn't change, the loader
| won't mark the _b_t record as an error (for seq
| enf hier situation)
| Rob Flippo 22-MAR-05 Add p_dimension_id as parm for build_remain_mbr_select
+===========================================================================*/
PROCEDURE Base_Update (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_dimension_id IN NUMBER
,p_value_set_required_flag IN VARCHAR2
,p_hier_table_name IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_col IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_master_request_id IN NUMBER)
IS
-- Consants
c_proc_name VARCHAR2(30) := 'Base_Update';
x_remain_mbr_select_stmt VARCHAR2(4000);
x_update_stmt VARCHAR2(4000);
x_update_mbr_status_stmt VARCHAR2(4000);
x_update_dimgrp_stmt VARCHAR2(4000);
x_delete_mbr_stmt VARCHAR2(4000);
x_enabled_flag_update_stmt VARCHAR2(4000);
build_remain_mbr_select_stmt (p_load_type
,p_dimension_id
,p_dimension_varchar_label
,p_shared_dimension_flag
,p_value_set_required_flag
,p_hier_dimension_flag
,p_source_b_table
,p_target_b_table
,p_member_col
,p_member_dc_col
,p_member_t_dc_col
,p_exec_mode_clause
,x_remain_mbr_select_stmt);
x_remain_mbr_select_stmt := REPLACE(x_remain_mbr_select_stmt,'{{data_slice}}',p_data_slc);
x_remain_mbr_select_stmt := REPLACE(x_remain_mbr_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.member select stmt'
,x_remain_mbr_select_stmt);
build_status_update_stmt (p_source_b_table
,x_update_mbr_status_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.status update stmt'
,x_update_mbr_status_stmt);
build_delete_stmt (p_source_b_table
,x_delete_mbr_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.delete stmt'
,x_delete_mbr_stmt);
build_dimgrp_update_stmt (p_target_b_table
,p_value_set_required_flag
,p_member_dc_col
,x_update_dimgrp_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.update member base table stmt'
,x_update_dimgrp_stmt);
build_enable_update_stmt (p_dimension_varchar_label
,p_dimension_id
,p_load_type
,p_target_b_table
,p_member_col
,p_member_dc_col
,p_value_set_required_flag
,x_enabled_flag_update_stmt);
x_enabled_flag_update_stmt := REPLACE(x_enabled_flag_update_stmt,'{{data_slice}}',p_data_slc);
x_enabled_flag_update_stmt := REPLACE(x_enabled_flag_update_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.update member enabled_flag stmt'
,x_enabled_flag_update_stmt);
OPEN cv_get_remain_mbr FOR x_remain_mbr_select_stmt USING v_slc_val, v_slc_val2;
OPEN cv_get_remain_mbr FOR x_remain_mbr_select_stmt;
SELECT count(*)
INTO v_seq_conflict_count
FROM fem_dimension_grps_b
WHERE dimension_group_seq = t_dimension_group_seq(i)
AND dimension_id = p_dimension_id
AND dimension_group_id <> t_dimension_group_id(i);
SELECT count(*)
INTO v_count
FROM fem_time_group_types_b
WHERE time_group_type_code = t_time_group_type_code(i);
ELSE -- update the dimension_group
IF p_value_set_required_flag = 'Y' THEN
EXECUTE IMMEDIATE x_update_dimgrp_stmt
USING t_dimension_group_id(i)
,gv_apps_user_id
,gv_login_id
,t_member_dc(i)
,t_value_set_id(i)
,t_b_status(i);
EXECUTE IMMEDIATE x_update_dimgrp_stmt
USING t_dimension_group_id(i)
,gv_apps_user_id
,gv_login_id
,t_member_dc(i)
,t_b_status(i);
END IF; -- execute immediate x_update_dimgrp_stmt
EXECUTE IMMEDIATE x_update_mbr_status_stmt
USING t_b_status(i)
,t_rowid(i)
,t_b_status(i);
EXECUTE IMMEDIATE x_delete_mbr_stmt
USING t_rowid(i)
,t_b_status(i)
,'N';
EXECUTE IMMEDIATE x_enabled_flag_update_stmt
USING t_member_dc(i)
,t_value_set_dc(i)
,t_value_set_dc(i)
,t_b_status(i);
EXECUTE IMMEDIATE x_enabled_flag_update_stmt
USING t_member_dc(i)
,t_b_status(i);
t_rowid.DELETE;
t_member_id.DELETE;
t_member_dc.DELETE;
t_value_set_dc.DELETE;
t_value_set_id.DELETE;
t_dimension_group_dc.DELETE;
t_dimension_group_id.DELETE;
t_b_status.DELETE;
END Base_Update;
| Attr_Assign_Update
|
| DESCRIPTION
| This step updates attribute rows for existing members. It also inserts
| new attribute rows for existing members. It is not necessary for users
| to populate the _B_T table in order for this step to run - it just queries
| attribute assignments from the _ATTR_T table
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| Update Attribute Assign (Simple Dim = 'N' only)
| 1. Attribute assignment rows for members that exist in FEM can be updated
| where the assignment already exists. Also, assignment rows for
| additional versions can be loaded as long as the attribute supports multiple versions.
| If a DIMENSION assignment, then assignment value must exist in the
| attribute dimension table. Status = 'INVALID_DIM_ASSIGNMENT' for failure
| If a DATE assignment, then assignment value must be a valid date.
| Status='INVALID_DATE' for failure
| If a NUMBER assignment, then assignment value must be a valid number.
| Status = 'INVALID_NUMBER' for failure.
| If an assignment exists for the VERSION_DISPLAY_CODE and the attribute
| is Read Only, then record is rejected and
| Status = 'READ_ONLY_ATTRIBUTE'
| If an assignment does not exist for the VERSION_DISPLAY_CODE and
| and an assignment does exist for one or more versions and the attribute
| is a "Single Version Only", then record is rejected and
| Status = 'MULT_VERSION_NOT_ALLOWED'
|
| -If the attribute assignment already exists for the provided version, it will be
| updated in FEM with the new value as long as it is not a "read only"
| version and as long as the attribute is not a "multiple assignment" attribute.
|
| -If the assignment exists for the provided version and the attribute
| is a "multiple assignment" attribute, then a new attribute row will be
| inserted for that version.
|
| -If the attribute assignment exists for the member but for a different version,
| then a new assignment row will be created only if the attribute "allows
| multiple versions".
|
| -If the attribute assignment does not exist at all for the member (either for
| provided version or for any version), then a new assignment row will be
| inserted.
| 2. Delete any _ATTR_T rows that are successful.
|
| MODIFICATION HISTORY
| Rob Flippo 22-MAR-04 Created
| Rob Flippo 13-SEP-04 Bug#3835758 Validation on the attr_Assign_vs
| was modified to include dimension_id
| in the where condition
| Rob Flippo 15-SEP-04 Bug#3835758 Added exception handler so no failure
| if VS didn't exist. Also moved the section
| that verifies the VS so that only called
| for DIMENSION attributes;
| go ahead and do the update so that the
| interface row can be deleted and not
| show up as an error
| Logic for assignment_is_read_only is as follows:
| If assignment_is_read_only_flag = 'Y', and there is an existing
| assignment record for the same version (i.e., - using build_does_attr_exist
| with version_flag = 'Y'), then
| query the target ATTR table again for that member, attribute, version,
| and include the appropriate assignment columns to see if you get 1 row
| back. If you do, then go ahead an update, since it is identical
| If no row comes back, then error as normal for when user tries to update
| assignment_is_read_only attribute
|
| Rob Flippo 01-OCT-04 Added logic so that if member is read
| only, the attribute update is not
|
| Rob Flippo 22-NOV-04 Bug#4019066 Add validation on Accounting_Year
| and GL_PERIOD_NUM for CAL_PERIOD; ACCOUNTING_YEAR
| Select to get member_id and value_set_id
| Rob Flippo 07-OCT-05 Bug#4630742 10G issue - Attr_assign_update fails
| on does_attr_exist checks: Modified the fetch
| so that both the "version" and "non-version" queries
| are identical for output variables;
| Rob Flippo 18-JUL-06 Bug 5024575 Updates for Many-to-many attributes
+===========================================================================*/
PROCEDURE Attr_Assign_Update (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_dimension_varchar_label IN VARCHAR2
,p_date_format_mask IN VARCHAR2
,p_dimension_id IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_target_attr_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_master_request_id IN NUMBER )
IS
-- Constants
c_proc_name VARCHAR2(30) := 'Attr_Assign_Update';
x_select_stmt VARCHAR2(4000);
x_attr_select_stmt VARCHAR2(4000);
x_remain_mbr_select_stmt VARCHAR2(4000);
x_insert_member_stmt VARCHAR2(4000);
x_insert_attr_stmt VARCHAR2(4000);
x_update_stmt VARCHAR2(4000);
x_attr_update_stmt VARCHAR2(4000);
x_update_tl_stmt VARCHAR2(4000);
x_update_attr_status_stmt VARCHAR2(4000);
x_update_mbr_status_stmt VARCHAR2(4000);
x_update_tl_status_stmt VARCHAR2(4000);
x_update_dimgrp_stmt VARCHAR2(4000);
x_delete_attr_stmt VARCHAR2(4000);
x_special_delete_attr_stmt VARCHAR2(4000);
x_delete_mbr_stmt VARCHAR2(4000);
x_delete_tl_stmt VARCHAR2(4000);
x_identical_attr_select_stmt VARCHAR2(4000);
build_attr_select_stmt (p_dimension_varchar_label
,p_dimension_id
,p_source_b_table
,p_source_attr_table
,p_target_b_table
,p_member_t_dc_col
,p_member_dc_col
,p_member_col
,p_value_set_required_flag
,p_shared_dimension_flag
,p_hier_dimension_flag
,'N'
,'N'
,p_exec_mode_clause
,x_attr_select_stmt);
x_attr_select_stmt := REPLACE(x_attr_select_stmt,'{{data_slice}}',p_data_slc);
x_attr_select_stmt := REPLACE(x_attr_select_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.attr select stmt'
,x_attr_select_stmt);
,x_identical_attr_select_stmt);
build_attr_update_stmt (p_target_attr_table
,p_target_b_table
,p_member_dc_col
,p_member_col
,p_value_set_required_flag
,x_attr_update_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.attr update stmt'
,x_attr_update_stmt);
build_delete_stmt (p_source_attr_table
,x_delete_attr_stmt);
build_special_delete_stmt (p_source_attr_table
,x_special_delete_attr_stmt);
build_insert_attr_stmt (p_target_attr_table
,p_target_b_table
,p_member_col
,p_member_dc_col
,p_value_set_required_flag
,x_insert_attr_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.insert attr stmt'
,x_insert_attr_stmt);
build_status_update_stmt (p_source_attr_table
,x_update_attr_status_stmt);
build_calp_interim_insert_stmt(x_calp_interim_stmt
,x_calp_attr_interim_stmt);
OPEN cv_get_attr_rows FOR x_attr_select_stmt USING v_slc_val, v_slc_val2;
x_adj_period_stmt := 'select count(*)'||
' from fem_cal_periods_attr A1, fem_dim_attributes_b DA1'||
',fem_dim_attr_versions_b V1'||
',fem_cal_periods_attr A2, fem_dim_attributes_b DA2'||
',fem_dim_attr_Versions_b V2'||
' where to_char(A1.cal_period_id) = :b_mbr_dc'||
' and A1.attribute_id = DA1.attribute_id'||
' and DA1.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and DA1.dimension_id = 1'||
' and A1.dim_attribute_varchar_member = ''N'''||
' and A1.version_id = V1.version_id'||
' and V1.attribute_id = DA1.attribute_id'||
' and V1.aw_snapshot_flag = ''N'''||
' and V1.default_version_flag =''Y'''||
' and A1.cal_period_id = A2.cal_period_id'||
' and A2.date_assign_value '||
' <> :b_date_assign'||
' and A2.attribute_id = DA2.attribute_id'||
' and DA2.attribute_varchar_label = ''CAL_PERIOD_START_DATE'''||
' and DA2.dimension_id=1'||
' and A2.version_id = V2.version_id'||
' and V2.attribute_id = DA2.attribute_id'||
' and V2.aw_snapshot_flag = ''N'''||
' and V2.default_version_flag = ''Y''';
x_adj_period_stmt := 'select count(*)'||
' from fem_cal_periods_attr A1, fem_dim_attributes_b DA1'||
',fem_dim_attr_versions_b V1'||
',fem_cal_periods_attr A2, fem_dim_attributes_b DA2'||
',fem_dim_attr_Versions_b V2'||
' where to_char(A1.cal_period_id) = '''||ta_member_dc(j)||''''||
' and A1.attribute_id = DA1.attribute_id'||
' and DA1.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and DA1.dimension_id = 1'||
' and A1.dim_attribute_varchar_member = ''N'''||
' and A1.version_id = V1.version_id'||
' and V1.attribute_id = DA1.attribute_id'||
' and V1.aw_snapshot_flag = ''N'''||
' and V1.default_version_flag =''Y'''||
' and A1.cal_period_id = A2.cal_period_id'||
' and A2.date_assign_value '||
' <> to_date('''||to_char(ta_date_assign_value(j),p_date_format_mask)||''','''||p_date_format_mask||''')'||
' and A2.attribute_id = DA2.attribute_id'||
' and DA2.attribute_varchar_label = ''CAL_PERIOD_START_DATE'''||
' and DA2.dimension_id=1'||
' and A2.version_id = V2.version_id'||
' and V2.attribute_id = DA2.attribute_id'||
' and V2.aw_snapshot_flag = ''N'''||
' and V2.default_version_flag = ''Y''';
'select count(*)'||
' from fem_cal_periods_attr CS, fem_cal_periods_attr CE,'||
' fem_cal_periods_b C,'||
' fem_dimension_grps_b D,'||
' fem_dim_attributes_b AE,'||
' fem_dim_attr_Versions_b VE,'||
' fem_cal_periods_attr CP,'||
' fem_dim_attributes_b AP,'||
' fem_dim_attr_versions_b VP'||
' where CS.cal_period_id = C.cal_period_id'||
' and to_char(C.cal_period_id) <> :b_mbr_dc'||
' and C.cal_period_id = CP.cal_period_id'||
' and C.enabled_flag = ''Y'''||
' and CP.attribute_id = AP.attribute_id'||
' and CP.version_id = VP.version_id'||
' and CP.dim_attribute_varchar_member = ''N'''||
' and AP.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and AP.dimension_id = 1'||
' and AP.attribute_id = VP.attribute_id'||
' and VP.default_version_flag = ''Y'''||
' and VP.aw_snapshot_flag = ''N'''||
' and C.calendar_id = :b_cal_id'||
' and C.dimension_group_id = D.dimension_group_id'||
' and D.time_dimension_group_key = :b_dimgrp_key'||
' and D.dimension_id = 1'||
' and CS.attribute_id = :b_attr_id'||
' and CS.version_id = :b_vers_id'||
' and CE.date_assign_value'||
' >= :b_new_start_date'||
' and CS.cal_period_id = CE.cal_period_id'||
' and CE.attribute_id = AE.attribute_id'||
' and AE.attribute_varchar_label = ''CAL_PERIOD_END_DATE'''||
' and AE.dimension_id = 1'||
' and CE.version_id = VE.version_id'||
' and VE.aw_snapshot_flag = ''N'''||
' and VE.default_version_flag = ''Y'''||
' and VE.attribute_id = AE.attribute_id'||
' and CS.date_assign_value'||
' <= :b_new_end_date';
'select count(*)'||
' from fem_cal_periods_attr CS, fem_cal_periods_attr CE,'||
' fem_cal_periods_b C,'||
' fem_dimension_grps_b D,'||
' fem_dim_attributes_b AE,'||
' fem_dim_attr_Versions_b VE,'||
' fem_cal_periods_attr CP,'||
' fem_dim_attributes_b AP,'||
' fem_dim_attr_versions_b VP'||
' where CS.cal_period_id = C.cal_period_id'||
' and to_char(C.cal_period_id) <>'''||ta_member_dc(j)||''''||
' and C.cal_period_id = CP.cal_period_id'||
' and C.enabled_flag = ''Y'''||
' and CP.attribute_id = AP.attribute_id'||
' and CP.version_id = VP.version_id'||
' and CP.dim_attribute_varchar_member = ''N'''||
' and AP.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and AP.dimension_id = 1'||
' and AP.attribute_id = VP.attribute_id'||
' and VP.default_version_flag = ''Y'''||
' and VP.aw_snapshot_flag = ''N'''||
' and C.calendar_id = '||substr(ta_member_dc(j),23,5)||
' and C.dimension_group_id = D.dimension_group_id'||
' and D.time_dimension_group_key = '||substr(ta_member_dc(j),28,5)||
' and D.dimension_id = 1'||
' and CS.attribute_id = '||ta_attribute_id(j)||
' and CS.version_id = '||ta_version_id(j)||
' and CE.date_assign_value'||
' >= :b_new_start_date'||
' and CS.cal_period_id = CE.cal_period_id'||
' and CE.attribute_id = AE.attribute_id'||
' and AE.attribute_varchar_label = ''CAL_PERIOD_END_DATE'''||
' and AE.dimension_id = 1'||
' and CE.version_id = VE.version_id'||
' and VE.aw_snapshot_flag = ''N'''||
' and VE.default_version_flag = ''Y'''||
' and VE.attribute_id = AE.attribute_id'||
' and CS.date_assign_value'||
' <= :b_new_end_date';
'select count(*)'||
' from fem_cal_periods_attr CS'||
',fem_cal_periods_b C'||
',fem_dimension_grps_b D'||
',fem_cal_periods_attr CP'||
',fem_dim_attributes_b AP'||
',fem_dim_attr_versions_b VP'||
' where CS.cal_period_id = C.cal_period_id'||
' and to_char(C.cal_period_id) <>'''||ta_member_dc(j)||''''||
' and C.cal_period_id = CP.cal_period_id'||
' and C.enabled_flag = ''Y'''||
' and CP.attribute_id = AP.attribute_id'||
' and CP.version_id = VP.version_id'||
' and CP.dim_attribute_varchar_member = ''N'''||
' and AP.attribute_varchar_label = ''ADJ_PERIOD_FLAG'''||
' and AP.dimension_id = 1'||
' and AP.attribute_id = VP.attribute_id'||
' and VP.default_version_flag = ''Y'''||
' and VP.aw_snapshot_flag = ''N'''||
' and C.calendar_id = '||substr(ta_member_dc(j),23,5)||
' and C.dimension_group_id = D.dimension_group_id'||
' and D.time_dimension_group_key = '||substr(ta_member_dc(j),28,5)||
' and CS.attribute_id = '||ta_attribute_id(j)||
' and CS.version_id = '||ta_version_id(j)||
' and to_char(CS.date_assign_value,'''||p_date_format_mask||''')'||
' >= '''||to_char(ta_date_assign_value(j),p_date_format_mask)||''''||
' and to_char(CS.date_assign_value,''j'')'||
' <= '||to_char(substr(ta_member_dc(j),1,7));
SELECT value_set_id
INTO ta_attr_assign_vs_id(j)
FROM fem_value_sets_b
WHERE value_set_display_code = ta_attr_assign_vs_dc(j)
AND dimension_id = ta_attribute_dimension_id(j);
(c_log_level_1,c_block||'.'||c_proc_name||'.identical attr select stmt'
,x_identical_attr_select_stmt);
EXECUTE IMMEDIATE x_identical_attr_select_stmt
INTO ta_attr_identical_count(j)
USING ta_attribute_id(j)
,ta_version_id(j)
,ta_value_set_dc(j)
,ta_member_dc(j)
,ta_dim_attr_numeric_member(j)
,ta_dim_attr_numeric_member(j)
,ta_dim_attr_numeric_member(j)
,ta_attr_assign_vs_id(j)
,ta_attr_assign_vs_id(j)
,ta_attr_assign_vs_id(j)
,ta_dim_attr_varchar_member(j)
,ta_dim_attr_varchar_member(j)
,ta_dim_attr_varchar_member(j)
,ta_number_assign_value(j)
,ta_number_assign_value(j)
,ta_number_assign_value(j)
,ta_varchar_assign_value(j)
,ta_varchar_assign_value(j)
,ta_varchar_assign_value(j)
,ta_temp_date_assign_value(j)
,ta_temp_date_assign_value(j)
,ta_temp_date_assign_value(j);
EXECUTE IMMEDIATE x_identical_attr_select_stmt
INTO ta_attr_identical_count(j)
USING ta_attribute_id(j)
,ta_version_id(j)
,ta_member_dc(j)
,ta_dim_attr_numeric_member(j)
,ta_dim_attr_numeric_member(j)
,ta_dim_attr_numeric_member(j)
,ta_attr_assign_vs_id(j)
,ta_attr_assign_vs_id(j)
,ta_attr_assign_vs_id(j)
,ta_dim_attr_varchar_member(j)
,ta_dim_attr_varchar_member(j)
,ta_dim_attr_varchar_member(j)
,ta_number_assign_value(j)
,ta_number_assign_value(j)
,ta_number_assign_value(j)
,ta_varchar_assign_value(j)
,ta_varchar_assign_value(j)
,ta_varchar_assign_value(j)
,ta_temp_date_assign_value(j)
,ta_temp_date_assign_value(j)
,ta_temp_date_assign_value(j);
EXECUTE IMMEDIATE x_attr_update_stmt
USING ta_dim_attr_numeric_member(j)
,ta_attr_assign_vs_id(j)
,ta_dim_attr_varchar_member(j)
,ta_number_assign_value(j)
,ta_varchar_assign_value(j)
,ta_temp_date_assign_value(j)
,gv_apps_user_id
,gv_login_id
,ta_member_id(j)
,ta_value_set_id(j)
,ta_attribute_id(j)
,ta_version_id(j)
,ta_allow_mult_assign_flag(j)
,ta_attr_exists_count(j)
,ta_status(j);
EXECUTE IMMEDIATE x_attr_update_stmt
USING ta_dim_attr_numeric_member(j)
,ta_attr_assign_vs_id(j)
,ta_dim_attr_varchar_member(j)
,ta_number_assign_value(j)
,ta_varchar_assign_value(j)
,ta_temp_date_assign_value(j)
,gv_apps_user_id
,gv_login_id
,ta_member_dc(j)
,ta_attribute_id(j)
,ta_version_id(j)
,ta_allow_mult_assign_flag(j)
,ta_attr_exists_count(j)
,ta_status(j)
,ta_use_interim_table_flag(j);
EXECUTE IMMEDIATE x_attr_update_stmt
USING ta_dim_attr_numeric_member(j)
,ta_attr_assign_vs_id(j)
,ta_dim_attr_varchar_member(j)
,ta_number_assign_value(j)
,ta_varchar_assign_value(j)
,ta_temp_date_assign_value(j)
,gv_apps_user_id
,gv_login_id
,ta_member_id(j)
,ta_attribute_id(j)
,ta_version_id(j)
,ta_allow_mult_assign_flag(j)
,ta_attr_exists_count(j)
,ta_status(j)
,ta_use_interim_table_flag(j);
(c_log_level_1,c_block||'.'||c_proc_name||'.prior_to_special_delete'
,null);
EXECUTE IMMEDIATE x_special_delete_attr_stmt
USING ta_rowid(j)
,ta_allow_mult_assign_flag(j)
,ta_attr_exists_count(j)
,ta_status(j)
,ta_use_interim_table_flag(j);
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING ta_status(j)
,ta_rowid(j)
,ta_status(j);
(c_log_level_1,c_block||'.'||c_proc_name||'.after_status_update'
,null);
EXECUTE IMMEDIATE x_insert_attr_stmt
USING tfa_attribute_id(j)
,tfa_version_id(j)
,tfa_dim_attr_numeric_member(j)
,tfa_attr_assign_vs_id(j)
,tfa_dim_attr_varchar_member(j)
,tfa_number_assign_value(j)
,tfa_varchar_assign_value(j)
,tfa_date_assign_value(j)
,gv_apps_user_id
,gv_apps_user_id
,tfa_member_dc(j)
,tfa_value_set_dc(j)
,tfa_status(j);
EXECUTE IMMEDIATE x_insert_attr_stmt
USING tfa_attribute_id(j)
,tfa_version_id(j)
,tfa_dim_attr_numeric_member(j)
,tfa_attr_assign_vs_id(j)
,tfa_dim_attr_varchar_member(j)
,tfa_number_assign_value(j)
,tfa_varchar_assign_value(j)
,tfa_date_assign_value(j)
,gv_apps_user_id
,gv_apps_user_id
,tfa_member_dc(j)
,tfa_status(j);
EXECUTE IMMEDIATE x_delete_attr_stmt
USING tfa_rowid(j)
,tfa_status(j)
,ta_use_interim_table_flag(j);
tfa_rowid.DELETE;
tfa_attribute_id.DELETE;
tfa_member_dc.DELETE;
tfa_value_set_dc.DELETE;
tfa_dim_attr_numeric_member.DELETE;
tfa_dim_attr_varchar_member.DELETE;
tfa_number_assign_value.DELETE;
tfa_varchar_assign_value.DELETE;
tfa_date_assign_value.DELETE;
tfa_version_id.DELETE;
tfa_attr_assign_vs_id.DELETE;
tfa_status.DELETE;
ta_rowid.DELETE;
ta_member_read_only_flag.DELETE;
ta_attribute_id.DELETE;
ta_attribute_varchar_label.DELETE;
ta_attribute_dimension_id.DELETE;
ta_attr_value_column_name.DELETE;
ta_attribute_data_type_code.DELETE;
ta_attribute_required_flag.DELETE;
ta_read_only_flag.DELETE;
ta_allow_mult_assign_flag.DELETE;
ta_member_dc.DELETE;
ta_value_set_dc.DELETE;
ta_attribute_assign_value.DELETE;
ta_dim_attr_numeric_member.DELETE;
ta_dim_attr_varchar_member.DELETE;
ta_number_assign_value.DELETE;
ta_varchar_assign_value.DELETE;
ta_date_assign_value.DELETE;
ta_temp_date_assign_value.DELETE;
ta_version_display_code.DELETE;
ta_version_id.DELETE;
ta_language.DELETE;
ta_attr_assign_vs_dc.DELETE;
ta_attr_assign_vs_id.DELETE;
ta_status.DELETE;
ta_attr_exists_count.DELETE;
ta_multattr_exists_count.DELETE;
ta_attr_identical_count.DELETE;
ta_cal_period_end_date.DELETE;
ta_adj_period_flag.DELETE;
ta_use_interim_table_flag.DELETE;
ta_cal_period_number.DELETE;
ta_calendar_dc.DELETE;
ta_calendar_id.DELETE;
ta_protected_assign_flag.DELETE;
ta_dimension_group_dc.DELETE;
ta_dimension_group_id.DELETE;
ta_calpattr_cal_dc.DELETE;
ta_calpattr_dimgrp_dc.DELETE;
ta_calpattr_end_date.DELETE;
ta_calpattr_period_num.DELETE;
ta_member_id.DELETE;
ta_value_set_id.DELETE;
END Attr_Assign_Update;
| Src_Sys_select_stmt
|
| DESCRIPTION
| Identifies the Source System Codes for the load
| specifically, for the purposes of updating FEM_DIM_LOAD_STATUS
| after the load is complete
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Rob Flippo 8-MAR-04 Created
| Rob Flippo 31-JAN-05 Added where condition on dimension_varchar_label
| for dimensions that use the shared ATTR_T table
|
+===========================================================================*/
procedure build_src_sys_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,x_src_sys_select_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_label_where_cond VARCHAR2(1000);
x_src_sys_select_stmt := 'SELECT DISTINCT(attribute_assign_value)'||
' FROM '||p_source_attr_table||
' WHERE attribute_varchar_label = ''SOURCE_SYSTEM_CODE'''||
v_dim_label_where_cond;
END build_src_sys_select_stmt;
| Creates new and updates existing Dimension Groups from the
| interface tables
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
|
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
| The Dimension Group Loader has 5 main categories of records for loading. These are:
| 1) Bad New Groups
| 2) New Groups
| 3) Existing Groups for name/desc update
| 4) Remaining Groups for base table update
| 5) Groups that exist but for a different Dimension_ID
|
| The records that belong in each of the above categories are described
| in detail below in the Introduction to each section.
|
| For each step, the following sub-steps are involved:
| a) BULK Collect members for the category
| b) Validate (if necessary)
| c) Update _B_T or _TL_T table for invalid records
| d) Update/insert FEM with the new record (or new name/description)
| e) Delete records from _B_T or _TL_T tables where appropriate
|
|
| MODIFICATION HISTORY
| Rob Flippo 10-NOV-03 Created
|
+===========================================================================*/
/*===========================================================================+
| PROCEDURE
| get_dimension_info
|
| DESCRIPTION
| Validates the inputs and obtains object and column names
| for the dimension
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 21-OCT-03 Created
| Rob Flippo 12-JAN-05 Bug#4030756 Retieve interface table names
| from fem_xdim_dimensions metadata rather than
| rely on naming convention
| Rob Flippo 16-MAR-05 Bug#4244082 properly identify dimensions that have
| dimension_group_id on their tables
| Previously, the hier_dimension_flag designated
| if the dim had a HIER table or not. Now it
| designates that the dim uses dimension_groups.
| -- p_simple_dimension_flag now means no attributes
| (as opposed to no hierarchies and no attributes)
| so it will not necessarily match what is in the table
+===========================================================================*/
procedure get_dimension_info (p_dimension_varchar_label IN VARCHAR2
,x_dimension_id OUT NOCOPY NUMBER
,x_target_b_table OUT NOCOPY VARCHAR2
,x_target_tl_table OUT NOCOPY VARCHAR2
,x_target_attr_table OUT NOCOPY VARCHAR2
,x_source_b_table OUT NOCOPY VARCHAR2
,x_source_tl_table OUT NOCOPY VARCHAR2
,x_source_attr_table OUT NOCOPY VARCHAR2
,x_member_col OUT NOCOPY VARCHAR2
,x_member_dc_col OUT NOCOPY VARCHAR2
,x_member_t_dc_col OUT NOCOPY VARCHAR2
,x_member_name_col OUT NOCOPY VARCHAR2
,x_member_t_name_col OUT NOCOPY VARCHAR2
,x_member_description_col OUT NOCOPY VARCHAR2
,x_value_set_required_flag OUT NOCOPY VARCHAR2
,x_user_defined_flag OUT NOCOPY VARCHAR2
,x_simple_dimension_flag OUT NOCOPY VARCHAR2
,x_shared_dimension_flag OUT NOCOPY VARCHAR2
,x_hier_table_name OUT NOCOPY VARCHAR2
,x_hier_dimension_flag OUT NOCOPY VARCHAR2
,x_member_id_method_code OUT NOCOPY VARCHAR2
,x_table_handler_name OUT NOCOPY VARCHAR2
,x_composite_dimension_flag OUT NOCOPY VARCHAR2 --
,x_structure_id OUT NOCOPY NUMBER) --
IS
v_hierarchy_table_name VARCHAR2(30);
SELECT dimension_id
,member_b_table_name
,member_tl_table_name
,attribute_table_name
,intf_member_b_table_name
,intf_member_tl_table_name
,intf_attribute_table_name
,member_col
,member_display_code_col
,member_name_col
,member_description_col
,value_set_required_flag
,user_defined_flag
,hierarchy_table_name
,decode(group_use_code,'OPTIONAL','Y','REQUIRED','Y','N')
,hier_editor_managed_flag
,read_only_flag
,member_id_method_code
,SUBSTR(member_b_table_name,1,length(member_b_table_name)-2)||'_PKG'
,composite_dimension_flag --
,id_flex_num --
INTO x_dimension_id
,x_target_b_table
,x_target_tl_table
,x_target_attr_table
,x_source_b_table
,x_source_tl_table
,x_source_attr_table
,x_member_col
,x_member_dc_col
,x_member_name_col
,x_member_description_col
,x_value_set_required_flag
,x_user_defined_flag
,x_hier_table_name
,v_use_groups_flag
,v_hier_editor_managed_flag
,v_xdim_read_only_flag
,x_member_id_method_code
,x_table_handler_name
,x_composite_dimension_flag --
,x_structure_id
FROM fem_xdim_dimensions_vl
WHERE dimension_varchar_label = p_dimension_varchar_label;
| build_mbr_select_stmt
|
| DESCRIPTION
| Builds the dynamic SELECT statement for retrieving
| the member data from the _T interface tables
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 22-OCT-03 Created
| Rob Flippo 08-SEP-04 Added where condition exists = 'Y' query so that
| when querying from the TL_T table, only rows of
| installed languages are retrieved. This is for
| bug#3857097 where it was retrieving rows for all
| languages, trying to update, and when no update row
| found it would just delete the source row
|
| Rob Flippo 08-Sep-04 Bug#3835758 Modified the vs_where_cond so that it
| joins to fem_value_set_b where the dimension_id =
| the dimension being loaded
| Rob Flippo 16-FEB-05 Bug#4189544 DIMENSION GROUP LOADER ISSUE
| add dimension_id where condition when target
| table is fem_dimension_grps_b
| -- also fix so only load grps of the specific dim
| Rob Flippo 10-JUN-05 Bug#3928148 For TL update, only allow updates for
| members that are not read_only_flag = Y
+===========================================================================*/
procedure build_mbr_select_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_t_name_col IN VARCHAR2
,p_member_description_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_exists_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_select_stmt OUT NOCOPY VARCHAR2)
IS
-- Value Set Where conditions
v_vs_where_cond VARCHAR2(1000);
(c_log_level_2,c_block||'.'||'build_mbr_select_stmt','Begin Build member select statement');
x_select_stmt :=
'SELECT B.rowid'||
',T.rowid'||
','||v_member_code||
v_cal_period_cols||
v_vs_col||
v_dimension_grp_col||
', ''LOAD'' '||
',T.'||p_member_t_name_col||
',T.'||p_member_description_col||
',T.language'||
', ''LOAD'' '||
', to_date(''12/31/2499'',''MM/DD/YYYY'')'||
v_dimgrp_load_col||
' FROM '||p_source_b_table||' B,'||
p_source_tl_table||' T'||
v_dimension_grp_table||
v_vs_table||
v_calendar_table||
' WHERE B.status'||p_exec_mode_clause||
v_data_slice_pred||
' AND T.status'||p_exec_mode_clause||
v_member_dc_where_cond||
v_vs_t_where_cond||
v_dim_label_where_cond||
v_dim_label_join_cond||
' AND NOT EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||v_vs_subq_table||
' WHERE to_char(G.'||p_member_dc_col||') = '||v_member_code||
v_dim_id_where_cond||
v_vs_subq_where_cond||')'||
v_dimension_grp_where_cond||
v_calendar_where_cond||
v_vs_where_cond||
' AND T.language = userenv(''LANG'')';
x_select_stmt :=
'SELECT null'||
',B.rowid'||
','||v_member_code||
v_cal_period_cols||
v_vs_col||
',null,null'||
',null'||
',B.'||p_member_t_name_col||
',B.'||p_member_description_col||
',B.language'||
', ''LOAD'' '||
',null,null'||
' FROM '||p_source_tl_table||' B'||
v_dimension_grp_table||
v_vs_table||
v_calendar_table||
' WHERE B.status'||p_exec_mode_clause||
v_data_slice_pred||
v_dim_label_where_cond||
' AND EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||v_vs_subq_table||
' WHERE to_char(G.'||p_member_dc_col||') = '||v_member_code||
' AND G.read_only_flag = ''N'''||
v_dim_id_where_cond||
v_vs_subq_where_cond||')'||
' AND B.language in (SELECT language_code from FND_LANGUAGES '||
' WHERE installed_flag in (''I'',''B'')) '||
v_dimension_grp_where_cond||
v_calendar_where_cond||
v_vs_where_cond;
(c_log_level_2,c_block||'.'||'build_mbr_select_stmt','End');
END build_mbr_select_stmt;
| Builds the dynamic UPDATE statement for updating the TL_T
| records where the LANGUAGE is not installed
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 17-Sep-04 Created
+===========================================================================*/
procedure build_bad_lang_upd_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_tl_table IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_label_where_cond VARCHAR2(1000); -- where condition for when updating
x_update_stmt := 'UPDATE '||p_source_tl_table||' B'||
' SET B.status = ''LANGUAGE_NOT_INSTALLED'' '||
' WHERE B.language NOT IN '||
' (SELECT language_code from FND_LANGUAGES '||
' WHERE installed_flag in (''I'',''B'')) '||
v_dim_label_where_cond||
' AND B.status'||p_exec_mode_clause||
' AND {{data_slice}} ';
| Builds the dynamic UPDATE statement for updating the TL_T
| records where the member is read_only_flag='Y'
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 10-Jun-05 Created
| Rob Flippo 07-OCT-05 Modified Cal period update statement for performance
| issue encountered during regression testing (no bug)
+===========================================================================*/
procedure build_tl_ro_mbr_upd_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_tl_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_label_where_cond VARCHAR2(1000); -- where condition for when updating
x_update_stmt := 'UPDATE '||p_source_tl_table||' B'||
' SET B.status = ''READ_ONLY_MEMBER'' '||
' WHERE B.language IN '||
' (SELECT language_code from FND_LANGUAGES '||
' WHERE installed_flag in (''I'',''B'')) '||
v_dim_label_where_cond||
' AND B.status'||p_exec_mode_clause||
' AND EXISTS (SELECT 0 FROM '||p_target_b_table||' G'||
v_calp_from_cond||
' WHERE to_char(G.'||p_member_dc_col||') = '||
'LPAD(to_char(to_number(to_char(B.cal_period_end_date,''j''))),7,''0'')||'||
'LPAD(TO_CHAR(B.cal_period_number),15,''0'')||'||
'LPAD(to_char(C.calendar_id),5,''0'')||'||
'LPAD(to_char(D.time_dimension_group_key),5,''0'') '||
' AND G.read_only_flag = ''Y'''||
' AND G.dimension_group_id = D.dimension_group_id '||
' AND C.calendar_id = G.calendar_id '||
')'||
' AND {{data_slice}} ';
x_update_stmt := 'UPDATE '||p_source_tl_table||' B'||
' SET B.status = ''READ_ONLY_MEMBER'' '||
' WHERE B.language IN '||
' (SELECT language_code from FND_LANGUAGES '||
' WHERE installed_flag in (''I'',''B'')) '||
v_dim_label_where_cond||
' AND B.status'||p_exec_mode_clause||
' AND EXISTS (SELECT 0 FROM '||p_target_b_table||' G'||
v_vs_from_cond||
' WHERE G.'||p_member_dc_col||'=B.'||p_member_t_dc_col||
' AND G.read_only_flag = ''Y'''||
v_vs_where_cond||
')'||
' AND {{data_slice}} ';
| build_bad_tl_select_stmt
|
| DESCRIPTION
| Builds the dynamic SELECT statement for retrieving
| the TL records that are not for new members (i.e., no data
| in _B_T table) and are not for existing members (i.e., member
| doesn't exist in FEM)
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 04-May-04 Created
| Rob Flippo 07-Sep-04 Bug#3848996 - Simple Dim load failing because
| Invalid column name. The select was using the
| wrong variable name - it should use p_member_t_dc_col
| since that is the name of the member col on the
| interface table
|
| Rob Flippo 16-Sep-04 Added condition on the NOT EXISTS subquery for the B2
| table to check the dimension_Varchar_label when the
| load is for a Simple Dimension
| Rob Flippo 16-FEB-05 Bug#4189544 DIMENSION GROUP LOADER ISSUE
| add dimension_id where condition when target
| table is fem_dimension_grps_b
| -- also modified so only load grps of specific
| dimension
+===========================================================================*/
procedure build_bad_tl_select_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_select_stmt OUT NOCOPY VARCHAR2)
IS
-- Value Set Where conditions
v_vs_where_cond VARCHAR2(1000);
(c_log_level_2,c_block||'.'||'build_bad_tl_select_stmt','Begin');
x_select_stmt :=
'SELECT B.rowid'||
','||v_member_code||
v_cal_period_cols||
v_vs_col||
', ''INVALID_MEMBER'' '||
' FROM '||p_source_tl_table||' B'||
v_dimension_grp_table||
v_calendar_table||
' WHERE B.status'||p_exec_mode_clause||' '||
v_data_slice_pred||
v_dim_label_where_cond||
' AND NOT EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||v_vs_subq_table||
' WHERE to_char(G.'||p_member_dc_col||') = '||v_member_code||
v_dim_id_where_cond||
v_vs_subq_where_cond||')'||
' AND NOT EXISTS (SELECT 0 FROM '||
p_source_b_table||' B2'||
' WHERE to_char('||v_member_code_b_t||') = '||v_member_code||
v_dim_label_subqwhere_cond||
v_vs_subq_where_condB2||')'||
v_dimension_grp_where_cond||
v_calendar_where_cond;
(c_log_level_2,c_block||'.'||'build_bad_TL_select_stmt','End');
END build_bad_tl_select_stmt;
| BUILD_CALP_INTERM_INSERT_STMT
|
| DESCRIPTION
| Builds the update statement for the inserts into the
| FEM_CALP_INTERIM_T and FEM_CALP_ATTR_INTERIM_T
|
| SCOPE - PRIVATE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Rob Flippo 04-JAN-05 Created
|
+===========================================================================*/
procedure build_calp_interim_insert_stmt (x_insert_calp_stmt OUT NOCOPY VARCHAR2
,x_insert_calp_attr_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
x_insert_calp_stmt := 'INSERT INTO FEM_CALP_INTERIM_T'||
'(CAL_PERIOD_END_DATE'||
',CAL_PERIOD_NUMBER'||
',CALENDAR_DISPLAY_CODE'||
',DIMENSION_GROUP_DISPLAY_CODE'||
',CAL_PERIOD_START_DATE'||
',CAL_PERIOD_ID'||
',DIMENSION_GROUP_ID'||
',CALENDAR_ID'||
',CAL_PERIOD_NAME'||
',DESCRIPTION'||
',OVERLAP_FLAG'||
',ADJUSTMENT_PERIOD_FLAG)'||
' SELECT'||
':b_end_date'||
',:b_period_number'||
',:b_calendar_display_code'||
',:b_dimgrp_display_code'||
',:b_start_date'||
',:b_cal_period_id'||
',:b_dimgrp_id'||
',:b_calendar_id'||
',:b_cal_period_name'||
',:b_description'||
',''N'''||
',:b_adj_period_flag'||
' FROM dual'||
' WHERE :b_use_interim_table_flag = ''Y'''||
' AND :b_status = ''LOAD''';
x_insert_calp_attr_stmt := 'INSERT INTO fem_calp_attr_interim_t'||
'(CAL_PERIOD_ID'||
',ATTRIBUTE_ID'||
',VERSION_ID'||
',DIM_ATTRIBUTE_NUMERIC_MEMBER'||
',DIM_ATTRIBUTE_VALUE_SET_ID'||
',DIM_ATTRIBUTE_VARCHAR_MEMBER'||
',NUMBER_ASSIGN_VALUE'||
',VARCHAR_ASSIGN_VALUE'||
',DATE_ASSIGN_VALUE'||
',OVERLAP_FLAG)'||
' SELECT'||
':b_cal_period_id'||
',:b_attribute_id'||
',:b_version_id'||
',:b_dim_attr_numeric_member'||
',:b_dim_attr_value_set_id'||
',:b_dim_attr_varchar_member'||
',:b_number_assign_value'||
',:b_varchar_assign_value'||
',:b_date_assign_value'||
',''N'''||
' FROM dual'||
' WHERE :b_use_interim_table_flag = ''Y'''||
' AND :b_status = ''LOAD''';
END build_calp_interim_insert_stmt;
v_truncate_stmt := 'delete from fem_calp_interim_t';
v_truncate_stmt := 'delete from fem_calp_attr_interim_t';
SELECT calendar_id
INTO v_calendar_id
FROM fem_calendars_b
WHERE calendar_display_code = p_calendar_dc;
SELECT dimension_group_id
INTO v_dimension_group_id
FROM fem_dimension_grps_b
WHERE dimension_group_display_code = p_dimension_group_dc;
| were being swapped on insert member
| Rob Flippo 07-OCT-05 Bug#4628009 Fixed problem in Post_Cal_Periods where
| the Cal Period Name and Description table variables
| had a type of varchar2(30) instead of 150 and 255.
| Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
|
+===========================================================================*/
PROCEDURE Post_Cal_Periods (p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_partition_code IN NUMBER
,p_fetch_limit IN NUMBER
,p_operation_mode IN VARCHAR2
,p_master_request_id IN NUMBER)
IS
c_proc_name VARCHAR2(30) := 'Post_Cal_Periods';
x_insert_mbr_stmt VARCHAR2(4000);
x_insert_attr_stmt VARCHAR2(4000);
x_select_mbr_stmt VARCHAR2(4000);
x_update_attr_stmt VARCHAR2(4000);
x_delete_b_stmt VARCHAR2(4000);
x_delete_tl_stmt VARCHAR2(4000);
x_delete_attr_stmt VARCHAR2(4000);
x_select_mbr_stmt := 'SELECT '||
'B.cal_period_id'||
', B.calendar_id'||
', B.dimension_group_id'||
', B.cal_period_name'||
', B.description'||
', B.cal_period_start_date'||
', B.cal_period_end_date'||
', B.cal_period_number'||
', B.calendar_display_code'||
', B.dimension_group_display_code'||
' FROM fem_calp_interim_t B'||
' WHERE B.overlap_flag = ''N'''||
' AND {{data_slice}} ';
x_select_mbr_stmt := REPLACE(x_select_mbr_stmt,'{{data_slice}}',p_data_slc);
x_select_mbr_stmt := REPLACE(x_select_mbr_stmt,'{{data_slice}}','1=1');
(c_log_level_1,c_block||'.'||c_proc_name||'.select_mbr_stmt'
,x_select_mbr_stmt);
x_insert_mbr_stmt :=
'DECLARE v_row_id VARCHAR2(1000); v_err_code NUMBER; x_num_msg NUMBER;'||
'BEGIN FEM_CAL_PERIODS_PKG.INSERT_ROW '||
'(x_rowid => v_row_id '||
',x_cal_period_id => :b_cal_period_id'||
',x_dimension_group_id => :b_dimension_group_id '||
',x_calendar_id => :b_calendar_id '||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_cal_period_name => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
(c_log_level_1,c_block||'.'||c_proc_name||'.insert_mbr_stmt'
,x_insert_mbr_stmt);
x_insert_attr_stmt := 'INSERT INTO FEM_CAL_PERIODS_ATTR '||
'(CAL_PERIOD_ID'||
',ATTRIBUTE_ID'||
',VERSION_ID'||
',DIM_ATTRIBUTE_NUMERIC_MEMBER'||
',DIM_ATTRIBUTE_VALUE_SET_ID'||
',DIM_ATTRIBUTE_VARCHAR_MEMBER'||
',NUMBER_ASSIGN_VALUE'||
',VARCHAR_ASSIGN_VALUE'||
',DATE_ASSIGN_VALUE'||
',CREATION_DATE'||
',CREATED_BY'||
',LAST_UPDATED_BY'||
',LAST_UPDATE_DATE'||
',LAST_UPDATE_LOGIN'||
',OBJECT_VERSION_NUMBER'||
',AW_SNAPSHOT_FLAG) '||
'SELECT cal_period_id'||
',attribute_id'||
',version_id'||
',dim_attribute_numeric_member'||
',dim_attribute_value_set_id'||
',dim_attribute_varchar_member'||
',number_assign_value'||
',varchar_assign_value'||
',date_assign_value'||
', sysdate'||
',:b_apps_user_id'||
',:b_apps_user_id2'||
', sysdate'||
', null'||
', 1'||
', ''N'''||
' FROM fem_calp_attr_interim_t'||
' WHERE cal_period_id = :b_cal_period_id';
(c_log_level_1,c_block||'.'||c_proc_name||'.insert_attr_stmt'
,x_insert_attr_stmt);
build_calp_delete_stmt ('FEM_CAL_PERIODS_B_T'
,p_operation_mode
,x_delete_b_stmt);
build_calp_delete_stmt ('FEM_CAL_PERIODS_TL_T'
,p_operation_mode
,x_delete_tl_stmt);
build_calp_delete_stmt ('FEM_CAL_PERIODS_ATTR_T'
,p_operation_mode
,x_delete_attr_stmt);
x_update_attr_stmt := 'UPDATE fem_cal_periods_attr'||
' SET date_assign_value = :b_start_date'||
',creation_date = sysdate'||
',last_update_date = sysdate'||
',last_updated_by = :b_apps_user_id'||
' WHERE cal_period_id = :b_cal_period_id'||
' AND attribute_id = :b_attribute_id'||
' AND version_id = :b_version_id';
(c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_stmt'
,x_update_attr_stmt);
build_calp_delete_stmt ('FEM_CAL_PERIODS_ATTR_T'
,p_operation_mode
,x_delete_attr_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.delete_attr_stmt'
,x_delete_attr_stmt);
SELECT attribute_id
INTO v_attribute_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label = 'CAL_PERIOD_START_DATE';
SELECT version_id
INTO v_version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attribute_id
AND default_version_flag = 'Y';
OPEN cv_get_rows FOR x_select_mbr_stmt USING v_slc_val, v_slc_val2;
EXECUTE IMMEDIATE x_insert_mbr_stmt
USING t_cal_period_id(i)
,t_dimension_group_id(i)
,t_calendar_id(i)
,t_cal_period_name(i)
,t_description(i)
,gv_apps_user_id
,gv_apps_user_id;
EXECUTE IMMEDIATE x_insert_attr_stmt
USING gv_apps_user_id
,gv_apps_user_id
,t_cal_period_id(i);
EXECUTE IMMEDIATE x_delete_b_stmt
USING t_cal_period_number(i)
,t_cal_period_end_date(i)
,t_calendar_display_code(i)
,t_dimension_group_display_code(i);
EXECUTE IMMEDIATE x_delete_tl_stmt
USING t_cal_period_number(i)
,t_cal_period_end_date(i)
,t_calendar_display_code(i)
,t_dimension_group_display_code(i);
EXECUTE IMMEDIATE x_delete_attr_stmt
USING t_cal_period_number(i)
,t_cal_period_end_date(i)
,t_calendar_display_code(i)
,t_dimension_group_display_code(i);
EXECUTE IMMEDIATE x_delete_attr_stmt
USING t_cal_period_number(i)
,t_cal_period_end_date(i)
,t_calendar_display_code(i)
,t_dimension_group_display_code(i)
,t_cal_period_id(i);
ELSE -- we only update an existing assignment
-- since the START_DATE attribute is required and must
-- therefore always already exist
FORALL i IN 1..v_mbr_last_row
EXECUTE IMMEDIATE x_update_attr_stmt
USING t_start_date(i)
,gv_apps_user_id
,t_cal_period_id(i)
,v_attribute_id
,v_version_id;
EXECUTE IMMEDIATE x_delete_attr_stmt
USING t_cal_period_number(i)
,t_cal_period_end_date(i)
,t_calendar_display_code(i)
,t_dimension_group_display_code(i);
EXECUTE IMMEDIATE x_delete_attr_stmt
USING t_cal_period_number(i)
,t_cal_period_end_date(i)
,t_calendar_display_code(i)
,t_dimension_group_display_code(i)
,t_cal_period_id(i);
t_cal_period_id.DELETE;
t_calendar_id.DELETE;
t_dimension_group_id.DELETE;
t_cal_period_name.DELETE;
t_description.DELETE;
t_start_date.DELETE;
t_cal_period_end_date.DELETE;
t_cal_period_number.DELETE;
t_calendar_display_code.DELETE;
t_dimension_group_display_code.DELETE;
| BUILD_ENABLE_UPDATE_STMT
|
| DESCRIPTION
| Builds the dynamic UPDATE statement for updating
| the enabled flag for dimension members. This procedure
| allows users to "undelete" a member by populating the
| _B_T table with the member for a load. The loader will
| automatically undelete any existing members from the _B_T
| table.
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 27-SEP-04 Created
| Rob Flippo 03-MAR-05 Modified so that is updating a Dim Grp we use
| where condition on dimension_id so that dim grps
| with the same name for other dimensions don't get
| mixed into the update
| Rob Flippo 15-MAR-05 Modify value set update so that use value_set
| in the subquery (otherwise get too many rows if
| 2 members have same display_code)
+===========================================================================*/
procedure build_enable_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_load_type IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2)
IS
v_dimlabel_cond VARCHAR2(1000);
'build_enable_update_stmt','Begin Build Enabled flag update statement');
x_update_stmt :=
'UPDATE '||p_target_b_table||
' SET enabled_flag = ''Y'''||
',last_update_date = sysdate '||
',last_updated_by = '||gv_apps_user_id||
',last_update_login = '||gv_login_id||
' WHERE to_char('||p_member_col||') = :b_member_code'||
' AND :b_t_a_status = ''LOAD''';
x_update_stmt :=
'UPDATE '||p_target_b_table||
' SET enabled_flag = ''Y'''||
',last_update_date = sysdate '||
',last_updated_by = '||gv_apps_user_id||
',last_update_login = '||gv_login_id||
' WHERE '||p_member_col||' = (SELECT '||p_member_col||
' FROM '||p_target_b_table||' T'||
', fem_value_sets_b V'||
' WHERE to_char(T.'||p_member_dc_col||') = :b_member_display_code'||
' AND T.value_set_id = V.value_set_id'||
' AND V.value_set_display_code = :b_value_set_display_code)'||
' AND value_set_id = (SELECT value_set_id FROM FEM_VALUE_SETS_B'||
' WHERE value_set_display_code = :b_value_set_display_code)'||
' AND :b_t_a_status = ''LOAD''';
x_update_stmt :=
'UPDATE '||p_target_b_table||
' SET enabled_flag = ''Y'''||
',last_update_date = sysdate '||
',last_updated_by = '||gv_apps_user_id||
',last_update_login = '||gv_login_id||
' WHERE '||p_member_col||' = (SELECT '||p_member_col||
' FROM '||p_target_b_table||
' WHERE to_char('||p_member_dc_col||') = :b_member_display_code'||
v_dimlabel_cond||
')'||
' AND :b_t_a_status = ''LOAD''';
'build_enable_update_stmt','End');
END build_enable_update_stmt;
| Builds the dynamic SELECT statement for verifying if
| a translatable name already exists for another dimension
| member
|
| SCOPE - PRIVATE
|
| NOTES
|
| MODIFICATION HISTORY
| Rob Flippo 13-MAR-06 Created
|
+===========================================================================*/
procedure build_tl_dupname_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_load_type IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_name_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_calling_mode IN VARCHAR2
,x_select_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_id_where_cond VARCHAR2(1000);
'build_tl_dupname_stmt','Begin Build select statement for dupname check');
x_select_stmt :=
'SELECT count(*) FROM '||p_target_tl_table||
' WHERE '||p_member_name_col||' = :b_member_name'||
' AND dimension_group_id = :b_dimgrp_id'||
' AND calendar_id = :b_calendar_id';
x_select_stmt :=
'SELECT count(*) FROM '||
p_target_tl_table||' T, '||p_target_b_table||' B'||
' WHERE T.'||p_member_name_col||' = :b_member_name'||
' AND T.'||p_member_col||' = B.'||p_member_col||
' AND to_char(B.'||p_member_dc_col||') <> :b_member_dc'||
' AND T.language = :b_lang'||
' AND T.dimension_group_id = :b_dimgrp_id'||
' AND T.calendar_id = :b_calendar_id';
v_vs_where_cond := ' AND value_set_id = (SELECT value_set_id'||
' FROM fem_value_sets_b '||
' WHERE value_set_display_code = :vs_dc)';
x_select_stmt :=
'SELECT count(*) FROM '||p_target_tl_table||
' WHERE '||p_member_name_col||' = :b_member_name'||
v_vs_where_cond;
' AND B.value_set_id = (SELECT value_set_id'||
' FROM fem_value_sets_b '||
' WHERE value_set_display_code = :vs_dc)';
x_select_stmt :=
'SELECT count(*) FROM '||
p_target_tl_table||' T, '||p_target_b_table||' B'||
' WHERE T.'||p_member_name_col||' = :b_member_name'||
' AND T.'||p_member_col||' = B.'||p_member_col||
' AND to_char(B.'||p_member_dc_col||') <> :b_member_dc'||
' AND T.language = :b_lang'||
v_vs_where_cond||
v_dim_id_where_cond;
| BUILD_TL_UPDATE_STMT
|
| DESCRIPTION
| Builds the dynamic UPDATE statement for updating
| the translatable names/descriptions for dimension members
|
| SCOPE - PRIVATE
|
| NOTES
| This procedure does not use the table handlers for performing the update,
| since we may be updating multiple languages.
|
|
| MODIFICATION HISTORY
| Rob Flippo 22-OCT-03 Created
|
| Rob Flippo 16-FEB-05 Bug#4189544 DIMENSION GROUP LOADER ISSUE
| add dimension_id where condition when target
| table is fem_dimension_grps_b
| Rob Flippo 22-MAR-05 Fix problem with same display_code in multiple
| value sets (single row query returns multiple rows)
| Rob Flippo 10-MAR-06 Bug#5068022 modify update so that only
| records where status in the array = 'LOAD' get
| updated
| Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
| Rob Flippo 15-MAR-07 Bug#5905501 Need to update source_lang so that
| translated rows get marked properly
+===========================================================================*/
procedure build_tl_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_load_type IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_target_tl_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_name_col IN VARCHAR2
,p_member_description_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_id_where_cond VARCHAR2(1000);
'build_tl_update_stmt','Begin Build update statement for TL table');
x_update_stmt :=
'UPDATE '||p_target_tl_table||
' SET '||p_member_name_col||' = :b_member_name,'||
p_member_description_col||' = :b_member_desc'||
',source_lang = :b_source_lang'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE to_char('||p_member_col||') = :b_member_code'||
' AND language = :b_language'||
' AND :b_status = ''LOAD''';
x_update_stmt :=
'UPDATE '||p_target_tl_table||
' SET '||p_member_name_col||' = :b_member_name,'||
p_member_description_col||' = :b_member_desc'||
',source_lang = :b_source_lang'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE '||p_member_col||' = (SELECT T.'||p_member_col||
' FROM '||p_target_b_table||' T'||
',fem_value_sets_b V'||
' WHERE to_char(T.'||p_member_dc_col||') = :b_member_display_code'||
' AND T.value_set_id = V.value_set_id'||
' AND V.value_set_display_code = :b_value_set_display_code'||
')'||
' AND value_set_id = (SELECT value_set_id FROM FEM_VALUE_SETS_B'||
' WHERE value_set_display_code = :b_value_set_display_code)'||
' AND language = :b_language'||
' AND :b_status = ''LOAD''';
x_update_stmt :=
'UPDATE '||p_target_tl_table||
' SET '||p_member_name_col||' = :b_member_name,'||
p_member_description_col||' = :b_member_desc'||
',source_lang = :b_source_lang'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE '||p_member_col||' = (SELECT '||p_member_col||
' FROM '||p_target_b_table||
' WHERE to_char('||p_member_dc_col||') = :b_member_display_code'||
v_dim_id_where_cond||
')'||
' AND language = :b_language'||
' AND :b_status = ''LOAD''';
'build_tl_update_stmt','End');
END build_tl_update_stmt;
| BUILD_ATTR_LVLSPEC_SELECT_STMT
|
| DESCRIPTION
| Procedure for building the dynamic SELECT statement for
| retrieving attribute rows from the ATTR_T table
| where the attribute is level specific but the member
| does not belong to the level. This is so we can update
| the row with status = 'INVALID_LVL_SPEC_ATTR_LABEL'.
|
| SCOPE - PRIVATE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Rob Flippo 09-JUN-05 Created
|
+===========================================================================*/
procedure build_attr_lvlspec_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_outer_join_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_attr_select_stmt OUT NOCOPY VARCHAR2)
IS
-- Value Set where conditions
v_value_set_where_cond VARCHAR2(1000);
v_value_set_select_T1 VARCHAR2(1000);
v_value_set_select_T2 VARCHAR2(1000);
'build_attr_lvlspec_select_stmt',
'Begin Build select statement for level specific attribute assignments from source _ATTR_T table');
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,''INVALID_LVL_SPEC_ATTR_LABEL'' '||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.cal_period_number CAL_PERIOD_NUMBER'||
', C.calendar_id CALENDAR_ID'||
', C.calendar_display_code CALENDAR_DC'||
', D.dimension_group_id DIMENSION_GROUP_ID'||
', D.dimension_group_display_code DIMENSION_GROUP_DC'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.cal_period_end_date'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
', FEM_CALENDARS_VL C, FEM_DIMENSION_GRPS_B D'||
' WHERE B.calendar_display_code = C.calendar_display_code '||
' AND B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND B.dimension_group_display_code = D.dimension_group_display_code'||
' AND D.dimension_id = '||p_dimension_id||') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND ('||
' ((A1.attribute_required_flag =''N'') '||
' AND (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2)))'||
' AND (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG3'||
' WHERE AG3.dimension_group_id = T2.dimension_group_id)))';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,''INVALID_LVL_SPEC_ATTR_LABEL'' '||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.value_set_display_code'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
', fem_value_sets_b V2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND M2.value_set_id = V2.value_set_id '||
' AND V2.value_set_display_code = T2.value_set_display_code'||
' AND V2.dimension_id = '||p_dimension_id||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND ('||
' ((A1.attribute_required_flag =''N'') '||
' AND (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2)))'||
' AND (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG3'||
' WHERE AG3.dimension_group_id = M2.dimension_group_id)))';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,''INVALID_LVL_SPEC_ATTR_LABEL'' '||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND ('||
' ((A1.attribute_required_flag =''N'') '||
' AND (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2)))'||
' AND (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG3'||
' WHERE AG3.dimension_group_id = M2.dimension_group_id)))';
'build_attr_lvlspec_select_stmt',
'End');
END build_attr_lvlspec_select_stmt;
| BUILD_ATTR_SELECT_STMT
|
| DESCRIPTION
| Procedure for building the dynamic SELECT statement for
| retrieving attribute rows from the ATTR_T table
| along with the corresponding attribute metadata from
| FEM_DIM_ATTRIBUTES_B
|
| SCOPE - PRIVATE
|
| NOTES
| Procedure is split into 4 sections:
| For CAL Period - getting for new specific member
| getting only where member exists, but multiple members
| For Value Set Dim - getting for new specific member
| getting only where member exists but mult members
|
| When getting for new specific member:
| Only get default version of 'Req' attributes
|
| This means that when processing new members, only required attributes
| are read. All other attribute assignments get loaded only after
| the member is already created.
|
|
| MODIFICATION HISTORY
| Rob Flippo 23-OCT-03 Created
| Rob Flippo 13-SEP-04 Modified so that when looking for required attributes
| (in the specific member select), we don't care if
| any rows exist in FEM_DIM_ATTR_GRPS, because Req
| attributes not allowed to be assigned in that table
| However, when specific_member = 'N', we do need to
| check FEM_DIM_ATTR_GRPS.
|
| The logic for checking is as follows:
| If attribute_required_flag = 'N', then we
| check FEM_DIM_ATTR_GRPS to see if it has been
| assigned specifically to any groups. But if the
| attribute_required_flag='Y', then we don't check.
|
| Also - modified all of the queries to join with the
| fem_dim_attr_versions_b table and restrict on min(version_id)
| Previously, this was only occuring for specific_member='Y'.
| This needs to occur for every attribute query since
| the pre_validation_attr procedure already exists to mark
| rows with invalid version_display_code.
| Rob Flippo 01-OCT-04 Modified query to retieve member read_only_flag
| for the "specific_member=Y" queries (i.e., the queries used
| by the attr_assign_update procedure to update attr
| of existing members); For "specific_member=N", it returns
| users can't try to update snapshot versions using the
| loader
| Rob Flippo 04-JAN-05 For CAL_PERIOD, added a new column
| to the select for "use_interim_table_flag = Y". This
| info is only queried during the ATTR_ASSIGN_UPDATE
| to identify any CAL Period attributes that require
| use of the FEM_CALP_ATTR_INTERIM_T table prior to
| moving the assignment into FEM
|
| Also added selection of new columns for Spec Mbr =N
| for CAL_PERIOD so that we have enough information to
| insert into the INTERIM tables for overlap checking
|
| Bug#3822561 Added 4 new CALPATTR columns to all
| Select statements to support attributes of CAL_PERIOD
|
| Also - for non-value set dims, added where condition
| for dimension_varchar_label if the dim uses the shared
| attr table
| Rob Flippo 15-MAR-05 Bug#4226011 Modified to only retreive attributes where
| user_assign_allowed_flag not in ('N');
| for the attr_update phase
+===========================================================================*/
procedure build_attr_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_outer_join_flag IN VARCHAR2
,p_specific_member_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_attr_select_stmt OUT NOCOPY VARCHAR2)
IS
-- Value Set where conditions
v_value_set_where_cond VARCHAR2(1000);
v_value_set_select_T1 VARCHAR2(1000);
v_value_set_select_T2 VARCHAR2(1000);
'build_attr_select_stmt',
'Begin Build select statement for attributes from source _ATTR_T table');
v_value_set_select_T1 := ', T1.value_set_display_code';
v_value_set_select_T2 := ', T2.value_set_display_code';
v_value_set_select_T1 := ',null';
v_value_set_select_T2 := ',null';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,null'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
' ,T2.member_code'||
' ,null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null'||
' ,''LOAD'''||
',''Y'''||
', T2.CALPATTR_CAL_DISPLAY_CODE'||
', T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
', T2.CALPATTR_END_DATE'||
', T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', D.dimension_group_id'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
','||p_source_b_table||' S1'||
', fem_dimension_grps_b D'||
', FEM_CALENDARS_VL C'||
' WHERE B.calendar_display_code = C.calendar_display_code'||
' AND S1.calendar_display_code = B.calendar_display_code'||
' AND S1.dimension_group_display_code = B.dimension_group_display_code'||
' AND S1.cal_period_end_date = B.cal_period_end_date'||
' AND S1.cal_period_number = B.cal_period_number'||
' AND B.status'||p_exec_mode_clause||
' AND S1.dimension_group_display_code = D.dimension_group_display_code'||
' AND D.dimension_id = '||p_dimension_id||') T2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND T2.member_code '||v_outer_join||' = :b_member_display_code'||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.version_id IN (SELECT min(version_id) FROM fem_dim_attr_versions_b V3'||
' WHERE V3.default_version_flag = ''Y'''||
' AND V3.aw_snapshot_flag = ''N'''||
' AND V3.attribute_id = A1.attribute_id)'||
' AND A1.attribute_id = V1.attribute_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND A1.attribute_required_flag = ''Y''';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
' ,T2.member_code'||
' ,null'||
' ,T2.member_code'||
' ,null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null'||
' ,T2.cal_period_end_date'||
' ,''LOAD'' '||
',decode(A1.attribute_varchar_label,''CAL_PERIOD_START_DATE'',''Y'',''N'')'||
' ,T2.cal_period_number'||
' ,T2.calendar_dc'||
' ,T2.calendar_id'||
' ,T2.dimension_group_dc'||
' ,T2.dimension_group_id'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.cal_period_number CAL_PERIOD_NUMBER'||
', C.calendar_id CALENDAR_ID'||
', C.calendar_display_code CALENDAR_DC'||
', D.dimension_group_id DIMENSION_GROUP_ID'||
', D.dimension_group_display_code DIMENSION_GROUP_DC'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.cal_period_end_date'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
', FEM_CALENDARS_VL C, FEM_DIMENSION_GRPS_B D'||
' WHERE B.calendar_display_code = C.calendar_display_code '||
' AND B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND B.dimension_group_display_code = D.dimension_group_display_code'||
' AND D.dimension_id = '||p_dimension_id||') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND (((A1.attribute_required_flag = ''Y''))'||
' OR ((A1.attribute_required_flag =''N'') AND '||
' (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG1))'||
' OR (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2'||
' WHERE AG2.dimension_group_id = T2.dimension_group_id))))';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,null'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', T2.value_set_display_code'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null'||
' ,''LOAD'' '||
' ,''N'''||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,B.'||v_member_code||' MEMBER_CODE'||
', B.value_set_display_code'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
','||p_source_b_table||' S1'||
' WHERE S1.'||v_member_code||' = B.'||v_member_code||
' AND S1.value_set_display_code = B.value_set_display_code'||
' AND B.status'||p_exec_mode_clause||') T2 '||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND T2.member_code '||v_outer_join||' = :b_member_display_code'||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.version_id IN (SELECT min(version_id) FROM fem_dim_attr_versions_b V3'||
' WHERE V3.default_version_flag = ''Y'''||
' AND V3.aw_snapshot_flag = ''N'''||
' AND V3.attribute_id = A1.attribute_id)'||
' AND A1.attribute_id = V1.attribute_id'||
' AND T2.value_set_display_code'||v_outer_join||' = :b_value_set_display_code'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND A1.attribute_required_flag = ''Y''';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,null'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null'||
' ,''LOAD'' '||
' ,''N'''||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,B.'||v_member_code||' MEMBER_CODE'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
','||p_source_b_table||' S1'||
' WHERE S1.'||v_member_code||' = B.'||v_member_code||
v_dim_label_where_cond||
v_spcmbr_dim_label_where_cond||
' AND B.status'||p_exec_mode_clause||') T2 '||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND T2.member_code '||v_outer_join||' = :b_member_display_code'||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.version_id IN (SELECT min(version_id) FROM fem_dim_attr_versions_b V3'||
' WHERE V3.default_version_flag = ''Y'''||
' AND V3.aw_snapshot_flag = ''N'''||
' AND V3.attribute_id = A1.attribute_id)'||
' AND A1.attribute_id = V1.attribute_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND A1.attribute_required_flag = ''Y''';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', T2.value_set_display_code'||
', M2.'||p_member_col||
', M2.value_set_id'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null,null'||
' ,''LOAD'' '||
' ,''N'''||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.value_set_display_code'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
', fem_value_sets_b V2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND M2.value_set_id = V2.value_set_id '||
' AND V2.value_set_display_code = T2.value_set_display_code'||
' AND V2.dimension_id = '||p_dimension_id||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND (((A1.attribute_required_flag = ''Y''))'||
' OR ((A1.attribute_required_flag =''N'') AND '||
' (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG1))'||
' OR (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2'||
' WHERE AG2.dimension_group_id = M2.dimension_group_id))))';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', T2.value_set_display_code'||
', M2.'||p_member_col||
', M2.value_set_id'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null,null'||
' ,''LOAD'' '||
' ,''N'''||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.value_set_display_code'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
', fem_value_sets_b V2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND M2.value_set_id = V2.value_set_id '||
' AND V2.dimension_id = '||p_dimension_id||
' AND V2.value_set_display_code = T2.value_set_display_code';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', null'||
', M2.'||p_member_col||
', null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null,null'||
' ,''LOAD'' '||
' ,''N'''||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND (((A1.attribute_required_flag = ''Y''))'||
' OR ((A1.attribute_required_flag =''N'') AND '||
' (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG1))'||
' OR (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2'||
' WHERE AG2.dimension_group_id = M2.dimension_group_id))))';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', null'||
', null'||
', null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null,null'||
' ,''LOAD'' '||
' ,''N'''||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code'||
' AND (((A1.attribute_required_flag = ''Y''))'||
' OR ((A1.attribute_required_flag =''N'') AND '||
' (A1.attribute_id NOT IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG1))'||
' OR (A1.attribute_id IN (SELECT attribute_id'||
' FROM fem_dim_attr_grps AG2'||
' WHERE AG2.dimension_group_id = M2.dimension_group_id))))';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', null'||
',M2.'||p_member_col||
', null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null,null'||
' ,''LOAD'' '||
' ,''N'''||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code';
x_attr_select_stmt :=
'SELECT T2.base_rowid'||
' ,M2.read_only_flag'||
' ,A1.attribute_id'||
' ,A1.attribute_varchar_label'||
' ,A1.attribute_dimension_id'||
' ,A1.attribute_value_column_name'||
' ,A1.attribute_data_type_code'||
' ,A1.attribute_required_flag'||
' ,A1.assignment_is_read_only_flag'||
' ,A1.allow_multiple_versions_flag'||
' ,A1.allow_multiple_assignment_flag'||
',T2.member_code'||
', null'||
', null'||
', null'||
' ,T2.attribute_assign_value'||
' ,null, null, null, null, null'||
' ,T2.version_display_code, null'||
' ,T2.attr_assign_vs_display_code, null,null'||
' ,''LOAD'' '||
' ,''N'''||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,null'||
' ,T2.CALPATTR_CAL_DISPLAY_CODE'||
' ,T2.CALPATTR_DIMGRP_DISPLAY_CODE'||
' ,T2.CALPATTR_END_DATE'||
' ,T2.CALPATTR_PERIOD_NUM'||
' FROM fem_dim_attributes_b A1'||
',fem_dim_attr_versions_b V1'||
',fem_dimensions_b B1'||
',(SELECT B.rowid base_rowid'||
', B.attribute_varchar_label,'||v_member_code||' MEMBER_CODE'||
', B.version_display_code'||
', B.attribute_assign_value'||
', B.attr_assign_vs_display_code'||
', B.CALPATTR_CAL_DISPLAY_CODE'||
', B.CALPATTR_DIMGRP_DISPLAY_CODE'||
', B.CALPATTR_END_DATE'||
', B.CALPATTR_PERIOD_NUM'||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} '||
') T2'||
', '||p_target_b_table||' M2'||
' WHERE T2.attribute_varchar_label'||v_outer_join||' = A1.attribute_varchar_label '||
' AND B1.dimension_id = A1.dimension_id'||
' AND nvl(A1.user_assign_allowed_flag,''Y'') NOT IN (''N'')'||
' AND B1.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND T2.version_display_code = V1.version_display_code'||
' AND V1.attribute_id = A1.attribute_id'||
' AND V1.aw_snapshot_flag = ''N'''||
' AND M2.'||p_member_dc_col||' = T2.member_code';
'build_attr_select_stmt',
'End');
END build_attr_select_stmt;
| BUILD_BAD_ATTR_SELECT_STMT
|
| DESCRIPTION
| Procedure for building the dynamic SELECT statement for
| retrieving attribute rows from the ATTR_T table
| where the Member does not exist in FEM and does not exist
| in the _B_T interface table. Such attribute rows are bad
| because the member isn't value, and we want to update their
| STATUS since otherwise it would left as 'LOAD'.
|
| This procedure runs after the Attribute loader sections
| to ensure that we don't retrieve any ATTR_T records for members
| that would be created by the loader
|
| SCOPE - PRIVATE
|
| NOTES
| We are getting ATTR_T records where the member does not exist in
| either the "real" FEM dimension member table and does not exist in
| the join of the _B_T with the _TL_T. In the case of the join, any
| bad attr records that have a member in the join of _B_T/TL_T will be identified
| during the NEW_MEMBERS module.
|
| Procedure is split into 2 sections:
| For CAL PERIOD
| Only need to look for records where the
| CALENDAR_DISPLAY_CODE and DIMENSION_GROUP_DISPLAY_CODE are valid, since
| the New Members module performs validations on those columns already.
|
| For Value Set Dim
|
| MODIFICATION HISTORY
| Rob Flippo 26-MAR-04 Created
| Rob Flippo 14-SEP-04 Modify so that it excludes records
| where the version is actually valid.
| This allows the
| Bad version cursor to find the bad version rows
| and update
| them appropriately;
| Fixed the select stmt to select rows as follows:
| 1) the attribute is not required AND the member
| does not exist in the official _B table OR
| 2) the attribute is required AND the member does
| not exist in the official _B table AND the
| member does not exist in a join of the
| interface _B_T/_TL_T tables
|
| Rob Flippo 01-NOV-04 Modified so that members with invalid grp
| or bad value_set continue to be processed
| so that their attr records can get updated
| with an invalid_member status
| Rob Flippo 15-MAR-05 Bug#4226011 add user_assign_allowed_flag not in ('N')
| to all queries against the fem_dim_attributes_b table
+===========================================================================*/
procedure build_bad_attr_select_stmt (p_dimension_varchar_label IN VARCHAR2
,p_dimension_id IN NUMBER
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_bad_attr_select_stmt OUT NOCOPY VARCHAR2)
IS
-- Value Set where conditions
v_value_set_where_cond VARCHAR2(1000);
v_value_set_select_T1 VARCHAR2(1000);
v_value_set_select_T2 VARCHAR2(1000);
'build_bad_attr_select_stmt',
'Begin Build_bad_attr_select_stmt');
x_bad_attr_select_stmt :=
'SELECT B.rowid'||
',''INVALID_MEMBER'''||
' FROM '||p_source_attr_table||' B'||
', FEM_CALENDARS_VL C, FEM_DIMENSION_GRPS_B D'||
', FEM_DIM_ATTR_VERSIONS_B V'||
', FEM_DIM_ATTRIBUTES_B A'||
' WHERE B.calendar_display_code = C.calendar_display_code '||
' AND B.dimension_group_display_code = D.dimension_group_display_code'||
' AND B.STATUS'||p_exec_mode_clause||
' AND B.attribute_varchar_label = A.attribute_varchar_label'||
' AND A.dimension_id = '||p_dimension_id||
' AND B.version_display_code = V.version_display_code'||
' AND V.attribute_id = A.attribute_id'||
' AND LPAD(to_char(to_number(to_char(B.cal_period_end_date,''j''))),7,''0'')||'||
'LPAD(TO_CHAR(B.cal_period_number),15,''0'')||'||
'LPAD(to_char(C.calendar_id),5,''0'')||'||
'LPAD(to_char(D.time_dimension_group_key),5,''0'') '||
' NOT IN (SELECT cal_period_id FROM fem_cal_periods_b) '||
' AND ((A.attribute_required_flag = ''N'')'||
' OR (A.attribute_required_flag = ''Y'''||
' AND NOT EXISTS (SELECT 0 FROM fem_cal_periods_b_t B2'||
' ,fem_cal_periods_tl_t TL2'||
' WHERE B2.cal_period_end_date = B.cal_period_end_date'||
' AND B2.cal_period_number = B.cal_period_number'||
' AND B2.calendar_display_code = B.calendar_display_code'||
' AND B2.dimension_group_display_code = B.dimension_group_display_code'||
' AND B2.cal_period_end_date = TL2.cal_period_end_date'||
' AND B2.calendar_display_code = TL2.calendar_display_code'||
' AND B2.dimension_group_display_code = TL2.dimension_group_display_code'||
' AND B2.cal_period_number = TL2.cal_period_number'||
' AND B2.STATUS'||p_exec_mode_clause||
' AND TL2.STATUS'||p_exec_mode_clause||')))'||
' AND {{data_slice}} ';
/* RCF 9-14-2004 Removing this since we should always update in the _ATTR table
when the member doesn't exist in the _B table. That's because this
update occurs after the NEW_MEMBER procedure is complete
' AND B.cal_period_number NOT IN (SELECT B1.cal_period_number '||
' FROM fem_cal_periods_b_t B1, fem_cal_periods_tl_t TL '||
' WHERE B1.calendar_display_code = B.calendar_display_code '||
' AND B1.dimension_group_display_code = B.dimension_group_display_code '||
' AND B1.cal_period_end_date = B.cal_period_end_date'||
' AND B1.calendar_display_code = TL.calendar_display_code'||
' AND B1.dimension_group_display_code = TL.dimension_group_display_code'||
' AND B1.cal_period_end_date = TL.cal_period_end_date'||
' AND B1.cal_period_number = TL.cal_period_number) '||
*/
ELSIF p_value_set_required_flag = 'Y' THEN
x_bad_attr_select_stmt :=
'SELECT B.rowid'||
',''INVALID_MEMBER'''||
' FROM '||p_source_attr_table||' B'||
', FEM_DIM_ATTR_VERSIONS_B V'||
', FEM_DIM_ATTRIBUTES_B A'||
' WHERE B.STATUS'||p_exec_mode_clause||
' AND B.attribute_varchar_label = A.attribute_varchar_label'||
' AND A.dimension_id = '||p_dimension_id||
' AND nvl(A.user_assign_allowed_flag,''Y'') not in (''N'')'||
' AND B.version_display_code = V.version_display_code'||
' AND V.attribute_id = A.attribute_id'||
' AND NOT EXISTS (SELECT 0'||
' FROM '||p_target_b_table||' T'||
',fem_value_sets_b V'||
' WHERE T.value_set_id = V.value_set_id'||
' AND V.value_set_display_code = B.value_set_display_code'||
' AND T.'||p_member_dc_col||' = B.'||p_member_t_dc_col||')'||
' AND ((A.attribute_required_flag = ''N'')'||
' OR (A.attribute_required_flag = ''Y'''||
' AND NOT EXISTS (SELECT 0 FROM '||p_source_b_table||' B2'||
', '||p_source_tl_table||' TL2'||
' WHERE B2.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||
' AND B2.value_set_display_code = B.value_set_display_code'||
' AND B2.'||p_member_t_dc_col||' = TL2.'||p_member_t_dc_col||
' AND B2.value_set_display_code = TL2.value_set_display_code'||
' AND B2.STATUS'||p_exec_mode_clause||
' AND TL2.STATUS'||p_exec_mode_clause||')))'||
' AND {{data_slice}} ';
/* RCF 9-14-2004 Removing this since we should always update in the _ATTR table
when the member doesn't exist in the _B table. That's because this
update occurs after the NEW_MEMBER procedure is complete
' AND NOT EXISTS (SELECT 0'||
' FROM '||p_source_b_table||' B1'||
','||p_source_tl_table||' TL'||
' WHERE B1.value_set_display_code = B.value_set_display_code'||
' AND B1.value_set_display_code = TL.value_set_display_code'||
' AND B1.'||p_member_t_dc_col||' = TL.'||p_member_t_dc_col||
' AND B1.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||')'||
*/
ELSE
x_bad_attr_select_stmt :=
'SELECT B.rowid'||
',''INVALID_MEMBER'''||
' FROM '||p_source_attr_table||' B'||
',FEM_DIM_ATTR_VERSIONS_B V'||
',FEM_DIM_ATTRIBUTES_B A'||
' WHERE B.STATUS'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND B.attribute_varchar_label = A.attribute_varchar_label'||
' AND A.dimension_id = '||p_dimension_id||
' AND nvl(A.user_assign_allowed_flag,''Y'') not in (''N'')'||
' AND B.version_display_code = V.version_display_code'||
' AND V.attribute_id = A.attribute_id'||
' AND NOT EXISTS (SELECT 0'||
' FROM '||p_target_b_table||' T'||
' WHERE T.'||p_member_dc_col||' = B.'||p_member_t_dc_col||')'||
' AND ((A.attribute_required_flag = ''N'')'||
' OR (A.attribute_required_flag = ''Y'''||
' AND NOT EXISTS (SELECT 0 FROM '||p_source_b_table||' B2'||
', '||p_source_tl_table||' TL2'||
' WHERE B2.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||
' AND B2.'||p_member_t_dc_col||' = TL2.'||p_member_t_dc_col||
' AND B2.STATUS'||p_exec_mode_clause||
' AND TL2.STATUS'||p_exec_mode_clause||')))'||
' AND {{data_slice}} ';
/* RCF 9-14-2004 Removing this since we should always update in the _ATTR table
when the member doesn't exist in the _B table. That's because this
update occurs after the NEW_MEMBER procedure is complete
' AND NOT EXISTS (SELECT 0'||
' FROM '||p_source_b_table||' B1'||
','||p_source_tl_table||' TL'||
' WHERE B1.'||p_member_t_dc_col||' = TL.'||p_member_t_dc_col||
' AND B1.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||')'||
*/
END IF;
'build_bad_attr_select_stmt',
'End Build_bad_attr_select_stmt');
END build_bad_attr_select_stmt;
| Procedure for building the dynamic SELECT statement for
| identifying if a member participates in a sequence enforced hierarchy
|
| SCOPE - PRIVATE
|
| NOTES
|
| MODIFICATION HISTORY
| Rob Flippo 16-MAR-05 Created
|
+===========================================================================*/
procedure build_seq_enf_hiercount_stmt (p_value_set_required_flag IN VARCHAR2
,p_hier_table_name IN VARCHAR2
,x_select_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||'build_seq_enf_hiercount_stmt',
'Begin Build SQL statement to identify if member participates in sequence enf hier');
x_select_stmt := 'SELECT count(*)'||
' FROM '||p_hier_table_name||' H'||
',fem_object_definition_b D'||
',fem_hierarchies O'||
' WHERE H.hierarchy_obj_def_id = D.object_definition_id'||
' AND D.object_id = O.hierarchy_obj_id'||
' AND O.group_sequence_enforced_code '||
' IN (''SEQUENCE_ENFORCED'',''SEQUENCE_ENFORCED_SKIP_LEVEL'')'||
' AND to_char(child_id) = to_char(:b_member_id)'||
' AND H.child_value_set_id = :b_value_set_id';
x_select_stmt := 'SELECT count(*)'||
' FROM '||p_hier_table_name||' H'||
',fem_object_definition_b D'||
',fem_hierarchies O'||
' WHERE H.hierarchy_obj_def_id = D.object_definition_id'||
' AND D.object_id = O.hierarchy_obj_id'||
' AND O.group_sequence_enforced_code '||
' IN (''SEQUENCE_ENFORCED'',''SEQUENCE_ENFORCED_SKIP_LEVEL'')'||
' AND to_char(child_id) = to_char(:b_member_id)';
| Procedure for building the dynamic SELECT statement for
| identifying if an attribute row already exists in the _ATTR table
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 23-OCT-03 Created
| Rob Flippo 02-MAR-05 Bug#4170444 Modified to return value for the
| READ_ONLY_FLAG from the ATTR table
| Rob Flippo 11-AUG-05 Bug#4547868 performance issue on query
| - remove join to _B table
|
+===========================================================================*/
procedure build_does_attr_exist_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_version_flag IN VARCHAR2
,x_attr_select_stmt OUT NOCOPY VARCHAR2)
IS
v_value_set_where_cond VARCHAR2(1000);
x_attr_select_stmt :=
'SELECT count(*), max(A.read_only_flag)'||
' FROM '||p_target_attr_table||' A'||
' WHERE A.attribute_id = :b_attribute_id'||
v_version_where_cond||
' AND A.'||p_member_col||' = :b_member_id'||
v_value_set_where_cond;
| Procedure for building the dynamic SELECT statement for
| identifying if an attribute row already exists in the _ATTR table
| when the attribute is Multi-assignment
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 06-JUL-06 Created
|
+===========================================================================*/
procedure build_does_multattr_exist_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_attr_value_column_name IN VARCHAR2
,p_attr_assign_vs_id IN NUMBER
,x_attr_select_stmt OUT NOCOPY VARCHAR2)
IS
v_value_set_where_cond VARCHAR2(1000);
x_attr_select_stmt :=
'SELECT count(*)'||
' FROM '||p_target_attr_table||' A'||
' WHERE A.attribute_id = :b_attribute_id'||
v_version_where_cond||
' AND A.'||p_member_col||' = :b_member_id'||
v_value_set_where_cond||
v_assign_where_cond;
| Procedure for building the dynamic SELECT statement for
| identifying if the attribute assignment from the interface
| table is exactly the same as an existing attr assignment row
|
| SCOPE - PRIVATE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Rob Flippo 30-SEP-04 Created
|
+===========================================================================*/
procedure build_get_identical_assgn_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_date_format_mask IN VARCHAR2
,x_attr_select_stmt OUT NOCOPY VARCHAR2)
IS
v_value_set_where_cond VARCHAR2(1000);
x_attr_select_stmt :=
'SELECT count(*)'||
' FROM '||p_target_attr_table||' A'||
','||p_target_b_table||' B'||
v_value_set_table||
' WHERE A.attribute_id = :b_attribute_id'||
' AND A.version_id = :b_version_id'||
' AND A.'||p_member_col||' = B.'||p_member_col||
v_value_set_where_cond||
' AND B.'||p_member_dc_col||' = :b_member_dc'||
' AND ((:b_dim_attr_numeric_member IS NULL '||
' AND A.dim_attribute_numeric_member IS NULL)'||
' OR (:b_dim_attr_numeric_member IS NOT NULL '||
' AND A.dim_attribute_numeric_member = :b_dim_attr_numeric_member))'||
' AND ((:b_dim_attr_vs_id IS NULL '||
' AND A.dim_attribute_value_set_id IS NULL)'||
' OR (:b_dim_attr_vs_id IS NOT NULL '||
' AND A.dim_attribute_value_set_id = :b_dim_attr_vs_id))'||
' AND ((:b_dim_attr_varchar_member IS NULL '||
' AND A.dim_attribute_varchar_member IS NULL)'||
' OR (:b_dim_attr_varchar_member IS NOT NULL '||
' AND A.dim_attribute_varchar_member = :b_dim_attr_varchar_member))'||
' AND ((:b_number_assign_value IS NULL '||
' AND A.number_assign_value IS NULL)'||
' OR (:b_number_assign_value IS NOT NULL '||
' AND A.number_assign_value = :b_number_assign_value))'||
' AND ((:b_varchar_assign_value IS NULL '||
' AND A.varchar_assign_value IS NULL)'||
' OR (:b_varchar_assign_value IS NOT NULL '||
' AND A.varchar_assign_value = :b_varchar_assign_value))'||
' AND ((:b_date_assign_value IS NULL '||
' AND A.date_assign_value IS NULL) '||
' OR (:b_date_assign_value IS NOT NULL '||
' AND A.date_assign_value = :b_date_assign_value))';
SELECT B2.dimension_varchar_label
INTO v_attr_dimension_varchar_label
FROM fem_dimensions_b B1, fem_dim_attributes_b A1, fem_dimensions_b B2
WHERE B1.dimension_id = A1.dimension_id
AND A1.attribute_varchar_label = p_attribute_varchar_label
AND B1.dimension_varchar_label = p_dimension_varchar_label
AND B2.dimension_id = A1.attribute_dimension_id;
'SELECT '||p_member_col||
' FROM '||p_member_table||' B1 '||v_value_set_table||
' WHERE to_char('||p_member_dc_col||') = to_char(:b_member_dc)'||
v_value_set_where_cond;
| Rob Flippo 28-SEP-04 Bug#3906218 Ability undelete members
| Modified the select stmt so that it excludes
| members that already exist in the target table
|
+===========================================================================*/
procedure build_bad_new_mbrs_stmt (p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_source_tl_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_bad_member_select_stmt OUT NOCOPY VARCHAR2)
IS
v_member_code VARCHAR2(1000); -- used only in the CAL_PERIOD select
x_bad_member_select_stmt :=
'SELECT B.rowid'||
',''MISSING_NAME'''||
' FROM '||p_source_b_table||' B'||
', fem_calendars_b C, fem_dimension_grps_b D'||
' WHERE B.status'||p_exec_mode_clause||
' AND B.calendar_display_code = C.calendar_display_code'||
' AND B.dimension_group_display_code = D.dimension_group_display_code'||
' AND {{data_slice}} '||
' AND NOT EXISTS (SELECT 0 FROM '||
p_source_tl_table||' S1'||
' WHERE S1.CALENDAR_DISPLAY_CODE = B.CALENDAR_DISPLAY_CODE'||
' AND S1.DIMENSION_GROUP_DISPLAY_CODE = B.DIMENSION_GROUP_DISPLAY_CODE'||
' AND S1.CAL_PERIOD_END_DATE = B.CAL_PERIOD_END_DATE'||
' AND S1.CAL_PERIOD_NUMBER = B.CAL_PERIOD_NUMBER'||
' AND S1.STATUS'||p_exec_mode_clause||')'||
' AND NOT EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||
' WHERE to_char(G.cal_period_id) = '||v_member_code||')';
x_bad_member_select_stmt :=
'SELECT B.rowid'||
',''MISSING_NAME'''||
' FROM '||p_source_b_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND NOT EXISTS (SELECT 0 FROM '||
p_source_tl_table||' S1'||
' WHERE S1.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||
' AND S1.value_set_display_code = B.value_set_display_code'||
' AND S1.status'||p_exec_mode_clause||')'||
' AND NOT EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||
', fem_value_sets_b V'||
' WHERE to_char(G.'||p_member_dc_col||') = B.'||p_member_t_dc_col||
' AND G.value_set_id = V.value_set_id'||
' AND V.value_set_display_code = B.value_set_display_code)';
x_bad_member_select_stmt :=
'SELECT B.rowid'||
',''MISSING_NAME'''||
' FROM '||p_source_b_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND NOT EXISTS (SELECT 0 FROM '||
p_source_tl_table||' S1'||
' WHERE S1.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||
' AND S1.status'||p_exec_mode_clause||')'||
' AND NOT EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||
' WHERE to_char(G.'||p_member_dc_col||') = B.'||p_member_t_dc_col||')';
x_bad_member_select_stmt :=
'SELECT B.rowid'||
',''MISSING_NAME'''||
' FROM '||p_source_b_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND B.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND NOT EXISTS (SELECT 0 FROM '||
p_source_tl_table||' S1'||
' WHERE S1.'||p_member_t_dc_col||' = B.'||p_member_t_dc_col||
' AND S1.dimension_varchar_label = B.dimension_varchar_label'||
' AND S1.status'||p_exec_mode_clause||')'||
' AND NOT EXISTS (SELECT 0 FROM '||
p_target_b_table||' G'||
' WHERE to_char(G.'||p_member_dc_col||') = B.'||p_member_t_dc_col||')';
| to prevent users from trying update snapshot
| versions
| Rob Flippo 31-JAN-05 Added where condition on dimension_varchar_label
| when the dimension uses the shared ATTR_T table
| Rob Flippo 15-MAR-05 Bug#4226011 add where user_assign_allowed_flag
| not in 'N' to the select stmt
+===========================================================================*/
procedure build_bad_attr_vers_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_bad_attr_vers_select_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_label_where_cond VARCHAR2(1000);
x_bad_attr_vers_select_stmt :=
'SELECT B.rowid'||
',''INVALID_VERSION'''||
' FROM '||p_source_attr_table||' B'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND NOT EXISTS (SELECT 0 FROM fem_dim_attr_versions_b V'||
' ,fem_dim_attributes_b D'||
' WHERE V.version_display_code = B.version_display_code'||
' AND V.attribute_id = D.attribute_id'||
' AND V.aw_snapshot_flag = ''N'''||
' AND D.attribute_varchar_label = B.attribute_varchar_label)'||
' AND EXISTS (SELECT 0 FROM fem_dim_attributes_b A'||
' WHERE A.attribute_varchar_label = B.attribute_varchar_label'||
' AND nvl(A.user_assign_allowed_flag,''Y'') not in (''N''))'||
' AND {{data_slice}} ';
SELECT B.version_id
INTO x_version_id
FROM fem_dim_attr_versions_b B, fem_dim_attributes_b A, fem_dimensions_b D
WHERE B.version_display_code = p_version_display_code
AND B.attribute_id = A.attribute_id
AND A.attribute_varchar_label = p_attribute_varchar_label
AND D.dimension_varchar_label = p_dimension_varchar_label
AND D.dimension_id = A.dimension_id;
| BUILD_INSERT_MEMBER_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic statement that calls the
| INSERT_ROW procedure of the table handler
|
|
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 28-OCT-03 Created
| Rob Flippo 04-AUG-06 Bug5060746 Change literals to bind variables
| whereever possible
|
+===========================================================================*/
procedure build_insert_member_stmt (p_table_handler_name IN VARCHAR2
,p_dimension_id IN NUMBER
,p_value_set_required_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_simple_dimension_flag IN VARCHAR2
,p_member_id_method_code IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR
,p_member_name_col IN VARCHAR2
,x_insert_member_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_insert_member_stmt',
'Begin SQL statement to insert new dimension members');
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); v_err_code NUMBER; x_num_msg NUMBER;'||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id '||
',x_'||p_member_col||' => fem_dimension_util_pkg.generate_member_id('||
':b_end_date,:b_period_number,:b_calendar_id, :b_dimension_group_id,v_err_code,x_num_msg) '||
',x_dimension_group_id => :b_dimension_group_id '||
',x_calendar_id => :b_calendar_id '||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); '||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id '||
',x_'||p_member_dc_col||' => :b_member_dc '||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null '||
',x_time_dimension_group_key => :b_time_dimension_group_key '||
',x_dimension_group_seq => :b_dimension_group_seq'||
',x_time_group_type_code => :b_time_group_type_code'||
',x_dimension_group_id => :b_dimension_group_id'||
',x_dimension_id => :b_dimension_id ); END;';
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); v_err_code NUMBER; x_num_msg NUMBER; '||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id'||
',x_'||p_member_col||' =>'||
'fem_dimension_util_pkg.generate_member_id('||p_dimension_id||',v_err_code,x_num_msg)'||
',x_value_set_id => :b_value_set_id '||
',x_dimension_group_id =>:b_dimension_group_id '||
',x_'||p_member_dc_col||' => :b_member_dc'||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); v_err_code NUMBER; x_num_msg NUMBER; '||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id'||
',x_'||p_member_col||' =>'||
'fem_dimension_util_pkg.generate_member_id('||p_dimension_id||',v_err_code,x_num_msg)'||
',x_value_set_id => :b_value_set_id '||
',x_'||p_member_dc_col||' => :b_member_dc'||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); v_err_code NUMBER; x_num_msg NUMBER; '||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id'||
',x_'||p_member_col||' =>'||
'fem_dimension_util_pkg.generate_member_id('||p_dimension_id||',v_err_code,x_num_msg)'||
',x_dimension_group_id =>:b_dimension_group_id '||
',x_'||p_member_dc_col||' => :b_member_dc'||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); v_err_code NUMBER; x_num_msg NUMBER; '||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id'||
',x_'||p_member_col||' =>'||
'fem_dimension_util_pkg.generate_member_id('||p_dimension_id||',v_err_code,x_num_msg)'||
',x_'||p_member_dc_col||' => :b_member_dc'||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
x_insert_member_stmt :=
'DECLARE v_row_id VARCHAR2(1000); '||
'BEGIN '||p_table_handler_name||'.INSERT_ROW'||
'(x_rowid => v_row_id '||
',x_'||p_member_col||' => :b_member_dc '||
',x_enabled_flag => ''Y'' '||
',x_personal_flag => ''N'' '||
',x_read_only_flag => ''N'' '||
',x_object_version_number => '||c_object_version_number||
',x_'||p_member_name_col||' => :b_member_name'||
',x_description => :b_member_desc '||
',x_creation_date => sysdate '||
',x_created_by => :b_apps_user_id'||
',x_last_update_date => sysdate '||
',x_last_updated_by => :b_apps_user_id2'||
',x_last_update_login => null ); END;';
'build_insert_member_stmt',
'End');
END build_insert_member_stmt;
| BUILD_INSERT_ATTR_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic insert statement
| for inserting into the ATTR table
|
|
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 28-OCT-03 Created
| Rob Flippo 04-AUG-06 Bug5060746 Change literals to bind variables
| whereever possible
|
+===========================================================================*/
procedure build_insert_attr_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_insert_attr_stmt OUT NOCOPY VARCHAR2)
IS
v_value_set_parm VARCHAR2(100);
v_value_set_select VARCHAR2(100);
'build_insert_attr_stmt',
'Begin SQL statement to insert new attribute assignments');
v_value_set_select := ', V1.value_set_id';
v_value_set_select := '';
x_insert_attr_stmt := 'INSERT INTO '||p_target_attr_table||' ('||
'attribute_id'||
',version_id'||
','||p_member_col||
v_value_set_column||
', dim_attribute_numeric_member'||
', dim_attribute_value_set_id'||
', dim_attribute_varchar_member'||
', number_assign_value'||
', varchar_assign_value'||
', date_assign_value'||
', creation_date'||
', created_by'||
', last_updated_by'||
', last_update_date'||
', last_update_login'||
', object_version_number'||
', aw_snapshot_flag)'||
' SELECT'||
' :b_attribute_id '||
', :b_version_id '||
','||p_member_col||
v_value_set_select||
', :b_dim_attribute_numeric_member'||
', :b_attr_assign_vs_id'||
', :b_dim_attribute_varchar_member'||
', :b_number_assign_value'||
', :b_varchar_assign_value'||
', :b_date_assign_value'||
', sysdate'||
',:b_apps_user_id'||
',:b_apps_user_id2'||
', sysdate'||
', null'||
', 1'||
', ''N'''||
' FROM '||p_target_b_table||' B1 '||
v_value_set_table||
' WHERE B1.'||p_member_dc_col||' = :b_member_dc'||
v_value_set_where_cond||
' AND :b_status = ''LOAD''';
'build_insert_attr_stmt',
'End');
END build_insert_attr_stmt;
| BUILD_STATUS_UPDATE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic update statement
| for setting the status in the _T table for any records
| not loaded.
|
| SCOPE - PRIVATE
|
| NOTES
|
| MODIFICATION HISTORY
| Rob Flippo 04-NOV-03 Created
|
+===========================================================================*/
procedure build_status_update_stmt (p_source_table IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_status_update_stmt',
'Begin SQL statement to update the status in the source interface table');
x_update_status_stmt := 'UPDATE '||p_source_table||
' SET status = :b_t_a_status '||
' WHERE rowid = :b_rowid'||
' AND :b_t_a_status <> ''LOAD''';
'build_status_update_stmt',
'End');
END build_status_update_stmt;
| BUILD_CALP_STATUS_UPDATE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic update statement
| for setting the status in the various CAL_PERIOD
| _T table for any records identified in the Interim table
| as having an error
|
| SCOPE - PRIVATE
|
| NOTES
|
| MODIFICATION HISTORY
| Rob Flippo 05-JAN-05 Created
|
+===========================================================================*/
procedure build_calp_status_update_stmt (p_operation_mode IN VARCHAR2
,p_source_table IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_calp_status_update_stmt',
'Begin SQL statement to update the status in the interface table based on bad records from the CALP Interim table');
x_update_status_stmt := 'UPDATE '||p_source_table||
' SET status = :b_t_a_status '||
' WHERE cal_period_number = :b_cal_period_number'||
' AND cal_period_end_date = :b_cal_period_end_date'||
' AND calendar_display_code = :b_calendar_dc'||
' AND dimension_group_display_code = :b_dimension_group_dc'||
' AND :b_overlap_flag = ''Y'''||
' AND attribute_varchar_label = ''CAL_PERIOD_START_DATE''';
x_update_status_stmt := 'UPDATE '||p_source_table||
' SET status = :b_t_a_status '||
' WHERE cal_period_number = :b_cal_period_number'||
' AND cal_period_end_date = :b_cal_period_end_date'||
' AND calendar_display_code = :b_calendar_dc'||
' AND dimension_group_display_code = :b_dimension_group_dc'||
' AND :b_overlap_flag = ''Y''';
'build_calp_status_update_stmt',
'End');
END build_calp_status_update_stmt;
| BUILD_CALP_DELETE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic delete statement
| for setting CAL_PERIOD members from the INTERIM table
| that were successfully moved into FEM
|
| SCOPE - PRIVATE
|
| NOTES
|
| MODIFICATION HISTORY
| Rob Flippo 25-JAN-05 Created
| Rob Flippo 15-MAR-07 Bug#5900463 - TL rows for other languages
| getting deleted when new members are being
| loaded
|
+===========================================================================*/
procedure build_calp_delete_stmt (p_source_table IN VARCHAR2
,p_operation_mode IN VARCHAR2
,x_calp_delete_stmt OUT NOCOPY VARCHAR2)
IS
v_attr_where_cond VARCHAR2(4000);
'build_calp_delete_stmt',
'Begin SQL statement to delete rows from the CAL_PERIOD interface tables');
' (SELECT attribute_varchar_label'||
' FROM fem_dim_attributes_b'||
' WHERE dimension_id=1'||
' AND attribute_required_flag = ''Y'')';
' (SELECT attribute_varchar_label'||
' FROM fem_dim_attributes_b A, fem_calp_attr_interim_t I'||
' WHERE A.dimension_id=1'||
' AND A.attribute_id = I.attribute_id'||
' AND I.cal_period_id = :cal_period_id'||
' AND overlap_flag = ''N'')';
x_calp_delete_stmt := 'DELETE FROM '||p_source_table||
' WHERE cal_period_number = :b_cal_period_number'||
' AND cal_period_end_date = :b_cal_period_end_date'||
' AND calendar_display_code = :b_calendar_dc'||
' AND dimension_group_display_code = :b_dimension_group_dc'||
v_attr_where_cond||v_tl_where_cond;
'build_calp_delete_stmt',
'End');
END build_calp_delete_stmt;
| BUILD_DEP_STATUS_UPDATE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic update statement
| for setting the status in the depended _T tables (_ATTR and _TL_T)
| for records with the same member identifier as records
| that were identified in the _B_T table as having a bad Dimension Group,
| Bad Value Set, or Missing Name
|
|
| SCOPE - PRIVATE
|
| NOTES
| We only want to update dependent attribute records where the status='LOAD'
| And the member failed for some other reason. This is just so that all attr
| records for failed members have some sort of error status, thus enabling
| the records to be all reprocessed with an Error Reprocessing run
| MODIFICATION HISTORY
| Rob Flippo 29-MAR-04 Created
| Rob Flippo 17-SEP-04 This procedure is obsolete and is no longer called
|
+===========================================================================*/
procedure build_dep_status_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_table IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_dep_status_update_stmt',
'Begin');
x_update_status_stmt := 'UPDATE '||p_source_table||
' SET status = ''INVALID_MEMBER'' '||
' WHERE calendar_display_code = :b_calendar_display_code'||
' AND dimension_group_display_code = :b_dimension_group_display_code'||
' AND cal_period_end_date = :b_cal_period_end_date'||
' AND cal_period_number = :b_cal_period_number'||
' AND :b_t_a_status <> ''LOAD'''||
' AND status IN (''LOAD'')';
x_update_status_stmt := 'UPDATE '||p_source_table||
' SET status = ''INVALID_MEMBER'' '||
' WHERE '||p_member_t_dc_col||' = :b_member_dc'||
' AND value_set_display_code = :b_value_set_display_code'||
' AND :b_t_a_status <> ''LOAD'''||
' AND status IN (''LOAD'')';
'build_dep_status_update_stmt',
'End');
END build_dep_status_update_stmt;
| BUILD_ATTRLAB_UPDATE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic update statement
| for setting the status in the ATTR_T tables
| for records where the ATTRIBUTE_VARCHAR_LABEL does not exist;
| such records would not otherwise be updated
|
|
| SCOPE - PRIVATE
|
| NOTES
| MODIFICATION HISTORY
| Rob Flippo 14-SEP-04 Created
|
+===========================================================================*/
procedure build_attrlab_update_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_label_where_cond VARCHAR2(1000);
'build_attrlab_update_stmt',
'Begin');
x_update_status_stmt := 'UPDATE '||p_source_attr_table||' B'||
' SET status = ''INVALID_ATTRIBUTE_LABEL'' '||
' WHERE attribute_varchar_label NOT IN (SELECT '||
' attribute_varchar_label FROM fem_dim_attributes_b A'||
' ,fem_dimensions_b D '||
' WHERE D.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND D.dimension_id = A.dimension_id)'||
' AND B.status '||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} ';
'build_attrlab_update_stmt',
'End');
END build_attrlab_update_stmt;
| Procedure that constructs the dynamic update statement
| for setting the status in the ATTR_T tables
| for records where the ATTRIBUTE_VARCHAR_LABEL is
| user_assign_allowed_flag = 'N';
| such records would not otherwise be updated
|
|
| SCOPE - PRIVATE
|
| NOTES
| MODIFICATION HISTORY
| Rob Flippo 15-MAR-05 Created
|
+===========================================================================*/
procedure build_not_user_label_upd_stmt (p_dimension_varchar_label IN VARCHAR2
,p_source_attr_table IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_update_status_stmt OUT NOCOPY VARCHAR2)
IS
v_dim_label_where_cond VARCHAR2(1000);
'build_attrlab_update_stmt',
'Begin');
x_update_status_stmt := 'UPDATE '||p_source_attr_table||' B'||
' SET status = ''ATTR_LABEL_NOT_USER_ASSIGN'' '||
' WHERE attribute_varchar_label IN (SELECT '||
' attribute_varchar_label FROM fem_dim_attributes_b A'||
' ,fem_dimensions_b D '||
' WHERE D.dimension_varchar_label = '''||p_dimension_varchar_label||''''||
' AND nvl(A.user_assign_allowed_flag,''Y'') = ''N'''||
' AND D.dimension_id = A.dimension_id)'||
' AND B.status '||p_exec_mode_clause||
v_dim_label_where_cond||
' AND {{data_slice}} ';
'build_attrlab_update_stmt',
'End');
| BUILD_DELETE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic delete statement
| for removing loaded records from the _ATTR_T and _TL_T tables
|
|
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 04-NOV-03 Created
| Rob Flippo 05-JAN-05 Modified to include use_interim_table_flag
+===========================================================================*/
procedure build_delete_stmt (p_source_table IN VARCHAR2
,x_delete_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_delete_stmt',
'Begin');
x_delete_stmt := 'DELETE FROM '||p_source_table||
' WHERE rowid = :b_rowid'||
' AND :b_t_a_status = ''LOAD'''||
' AND :b_use_interim_table_flag = ''N''';
'build_delete_stmt',
'End');
END build_delete_stmt;
| BUILD_SPECIAL_DELETE_STMT
|
| DESCRIPTION
| Procedure that constructs the dynamic delete statement
| for removing records from the _ATTR_T that successfully updated
|
|
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 04-NOV-03 Created
|
+===========================================================================*/
procedure build_special_delete_stmt (p_source_table IN VARCHAR2
,x_delete_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_special_delete_stmt',
'Begin');
x_delete_stmt := 'DELETE FROM '||p_source_table||
' WHERE rowid = :b_rowid'||
' AND :b_allow_mult_assign_flag = ''N'''||
' AND :b_attr_exists_count > 0'||
' AND :b_status = ''LOAD'''||
' AND :b_use_interim_table_flag = ''N''';
'build_special_delete_stmt',
'End');
END build_special_delete_stmt;
| build_remain_mbr_select_stmt
|
| DESCRIPTION
| Builds the dynamic SELECT statement for retrieving
| the remaining members from the _B_T interface table.
| When this SELECT is run, only the only records remaining
| in the _B_T table with "LOAD" status are those for Value Set Required
| dimensions that already exist in FEM.
| Note that this SELECT stmt is not run for CAL_PERIOD and Simple dimensions
| since the Dimension Group for members of those dimensions either
| doesn't apply (Simple Dimensions) or can't be changed (CAL_PERIOD)
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 06-NOV-03 Created
| Rob Flippo 16-MAR-05 Bug34244082 Modify to return the member_id
| in the select so that we can use that to
| query the HIER table in Base_update
| Rob Flippo 22-MAR-05 Fix query so that dimension_id is part of where
| condition when joining to FEM_DIMENSION_GRPS_B
+===========================================================================*/
procedure build_remain_mbr_select_stmt (p_load_type IN VARCHAR2
,p_dimension_id IN NUMBER
,p_dimension_varchar_label IN VARCHAR2
,p_shared_dimension_flag IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_hier_dimension_flag IN VARCHAR2
,p_source_b_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_col IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_t_dc_col IN VARCHAR2
,p_exec_mode_clause IN VARCHAR2
,x_remain_mbr_select_stmt OUT NOCOPY VARCHAR2)
IS
-- Dimension Label where condition (for shared dimensions)
v_dim_label_where_cond VARCHAR2(1000);
'build_remain_mbr_select_stmt',
'Begin');
x_remain_mbr_select_stmt :=
'SELECT B.rowid'||
', to_char(TB.'||p_member_col||')'||
', B.'||p_member_t_dc_col||
', B.value_set_display_code'||
', V.value_set_id'||
', B.dimension_group_display_code'||
', D.dimension_group_id'||
', TB.dimension_group_id'||
',null'||
',null'||
', ''LOAD'' '||
' FROM '||p_source_b_table||' B'||
', FEM_DIMENSION_GRPS_B D'||
', FEM_VALUE_SETS_B V'||
', '||p_target_b_table||' TB'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND B.value_set_display_code = V.value_set_display_code'||
' AND B.dimension_group_display_code = D.dimension_group_display_code (+)'||
' AND D.dimension_id (+) = '||p_dimension_id||
' AND B.'||p_member_t_dc_col||' = TB.'||p_member_dc_col||
' AND TB.value_set_id = V.value_set_id';
' AND EXISTS (SELECT 0'||
' FROM '||p_target_b_table||
' WHERE '||p_member_dc_col||' = B.'||p_member_t_dc_col||
' AND value_set_id = (SELECT value_set_id FROM fem_value_sets_b'||
' WHERE value_set_display_code = B.value_set_display_code))';
x_remain_mbr_select_stmt :=
'SELECT B.rowid'||
', to_char(TB.'||p_member_col||')'||
', B.'||p_member_t_dc_col||
', B.value_set_display_code'||
', V.value_set_id'||
', null'||
', null'||
',null'||
',null'||
',null'||
', ''LOAD'' '||
' FROM '||p_source_b_table||' B'||
', FEM_VALUE_SETS_B V'||
', '||p_target_b_table||' TB'||
' WHERE B.status'||p_exec_mode_clause||
' AND {{data_slice}} '||
' AND B.value_set_display_code = V.value_set_display_code'||
' AND B.'||p_member_t_dc_col||' = TB.'||p_member_dc_col||
' AND TB.value_set_id = V.value_set_id';
' AND EXISTS (SELECT 0'||
' FROM '||p_target_b_table||
' WHERE '||p_member_dc_col||' = B.'||p_member_t_dc_col||
' AND value_set_id = (SELECT value_set_id FROM fem_value_sets_b'||
' WHERE value_set_display_code = B.value_set_display_code))';
x_remain_mbr_select_stmt :=
'SELECT B.rowid'||
',LPAD(to_char(to_number(to_char(B.cal_period_end_date,''j''))),7,''0'')||'||
'LPAD(TO_CHAR(B.cal_period_number),15,''0'')||'||
'LPAD(to_char(C.calendar_id),5,''0'')||'||
'LPAD(to_char(D.time_dimension_group_key),5,''0'') '||
',LPAD(to_char(to_number(to_char(B.cal_period_end_date,''j''))),7,''0'')||'||
'LPAD(TO_CHAR(B.cal_period_number),15,''0'')||'||
'LPAD(to_char(C.calendar_id),5,''0'')||'||
'LPAD(to_char(D.time_dimension_group_key),5,''0'') '||
', null'||
', null'||
', null'||
', null'||
',null'||
',null'||
',null'||
', ''LOAD'' '||
' FROM '||p_source_b_table||' B'||
', FEM_CALENDARS_B C'||
', FEM_DIMENSION_GRPS_B D'||
' WHERE B.calendar_display_code = C.calendar_display_code'||
' AND B.dimension_group_display_code = D.dimension_group_display_code'||
' AND EXISTS (SELECT 0'||
' FROM '||p_target_b_table||
' WHERE to_char('||p_member_dc_col||') = '||
'LPAD(to_char(to_number(to_char(B.cal_period_end_date,''j''))),7,''0'')||'||
'LPAD(TO_CHAR(B.cal_period_number),15,''0'')||'||
'LPAD(to_char(C.calendar_id),5,''0'')||'||
'LPAD(to_char(D.time_dimension_group_key),5,''0'')) '||
' AND B.status'||p_exec_mode_clause||
' AND {{data_slice}} ';
x_remain_mbr_select_stmt :=
'SELECT B.rowid'||
', to_char(TB.'||p_member_col||')'||
', B.'||p_member_t_dc_col||
', null'||
', null'||
', null'||
', null'||
',null'||
',null'||
',null'||
', ''LOAD'' '||
' FROM '||p_source_b_table||' B'||
', '||p_target_b_table||' TB'||
' WHERE B.status'||p_exec_mode_clause||
v_dim_label_where_cond||
' AND B.'||p_member_t_dc_col||' = TB.'||p_member_dc_col||
' AND {{data_slice}} ';
' WHERE EXISTS (SELECT 0'||
' FROM '||p_target_b_table||
' WHERE '||p_member_dc_col||' = '||
' B.'||p_member_t_dc_col||')'||
******************************************************/
ELSIF (p_load_type = 'DIMENSION_GROUP') THEN
x_remain_mbr_select_stmt :=
'SELECT B.rowid'||
', to_char(TB.'||p_member_col||')'||
', B.'||p_member_t_dc_col||
', null'||
', null'||
', null'||
', null'||
', null'||
',B.dimension_group_seq'||
',B.time_group_type_code'||
', ''LOAD'' '||
' FROM '||p_source_b_table||' B'||
', '||p_target_b_table||' TB'||
', fem_dimensions_b D'||
' WHERE B.status'||p_exec_mode_clause||
' AND B.dimension_varchar_label = D.dimension_varchar_label'||
' AND D.dimension_id = TB.dimension_id'||
' AND TB.dimension_group_display_code = B.dimension_group_display_code'||
' AND B.dimension_varchar_label = '''||p_dimension_varchar_label||'''';
' WHERE EXISTS (SELECT 0'||
' FROM '||p_target_b_table||
' WHERE '||p_member_dc_col||' = '||
' B.'||p_member_t_dc_col||')'||
*******************************************************/
END IF;
'build_remain_mbr_select_stmt',
'End');
END build_remain_mbr_select_stmt;
| BUILD_DIMGRP_UPDATE_STMT
|
| DESCRIPTION
| Builds the dynamic UPDATE statement for updating
| the DIMENSION_GROUP_ID column on the _B member table
|
| SCOPE - PRIVATE
|
| NOTES
| This procedure does not use the table handlers for performing the update
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 06-NOV-03 Created
| Rob Flippo 16-MAR-05 Bug#4244082 requires this procedure
| Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
+===========================================================================*/
procedure build_dimgrp_update_stmt (p_target_b_table IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_dimgrp_update_stmt',
'Begin');
x_update_stmt :=
'UPDATE '||p_target_b_table||
' SET dimension_group_id = :b_dimension_group_id'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE '||p_member_dc_col||' = :b_member_dc'||
' AND value_set_id = :b_value_set_id'||
' AND :b_status = ''LOAD''';
x_update_stmt :=
'UPDATE '||p_target_b_table||
' SET dimension_group_id = :b_dimension_group_id'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE '||p_member_dc_col||' = :b_member_dc'||
' AND :b_status = ''LOAD''';
'build_dimgrp_update_stmt',
'End');
END build_dimgrp_update_stmt;
| BUILD_ATTR_UPDATE_STMT
|
| DESCRIPTION
| Builds the dynamic UPDATE statement for updating
| existing ATTR rows using the interface source _ATTR_T rows
|
| SCOPE - PRIVATE
|
| NOTES
|
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 13-NOV-03 Created
| Rob Flippo 04-JAN-05 Added "use_interim_table_flag" condition
| so that the update only happens for rows that are
| not going to the FEM_CALP_ATTR_INTERIM_T table for
| additional processing
| Rob Flippo 11-AUG-05 Bug#4547868 performance issue - fix update
| so no subquery against base table
| Rob Flippo 04-AUG-06 Bug 5060746 Change literals to bind variables wherever possible
+===========================================================================*/
procedure build_attr_update_stmt (p_target_attr_table IN VARCHAR2
,p_target_b_table IN VARCHAR2
,p_member_dc_col IN VARCHAR2
,p_member_col IN VARCHAR2
,p_value_set_required_flag IN VARCHAR2
,x_update_stmt OUT NOCOPY VARCHAR2)
IS
BEGIN
FEM_ENGINES_PKG.TECH_MESSAGE
(c_log_level_2,c_block||'.'||
'build_attr_update_stmt',
'Begin');
x_update_stmt :=
'UPDATE '||p_target_attr_table||' A'||
' SET dim_attribute_numeric_member = :b_dim_attribute_numeric_member'||
' ,dim_attribute_value_set_id = :b_dim_attribute_value_set_id'||
' ,dim_attribute_varchar_member = :b_dim_attribute_varchar_member'||
' ,number_assign_value = :b_number_assign_value'||
' ,varchar_assign_value = :b_varchar_assign_value'||
' ,date_assign_value = :b_date_assign_value'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE A.'||p_member_col||' = :b_member_id'||
' AND A.value_set_id = :value_set_id'||
' AND A.attribute_id = :b_attribute_id'||
' AND A.version_id = :b_version_id'||
' AND :b_allow_mult_assign_flag = ''N'''||
' AND :b_attr_exists_count > 0'||
' AND :b_status = ''LOAD''';
'UPDATE '||p_target_attr_table||' A'||
' SET dim_attribute_numeric_member = :b_dim_attribute_numeric_member'||
' ,dim_attribute_value_set_id = :b_dim_attribute_value_set_id'||
' ,dim_attribute_varchar_member = :b_dim_attribute_varchar_member'||
' ,number_assign_value = :b_number_assign_value'||
' ,varchar_assign_value = :b_varchar_assign_value'||
' ,date_assign_value = :b_date_assign_value'||
',last_update_date = sysdate '||
',last_updated_by = '||gv_apps_user_id||
',last_update_login = '||gv_login_id||
' WHERE A.'||p_member_col||' = (SELECT '||p_member_col||
' FROM '||p_target_b_table||' B'||
', fem_value_sets_b V'||
' WHERE B.'||p_member_dc_col||' = :b_member_dc'||
' AND B.value_set_id = V.value_set_id'||
' AND V.value_set_display_code = :value_set_dc)'||
' AND A.attribute_id = :b_attribute_id'||
' AND A.version_id = :b_version_id'||
' AND :b_allow_mult_assign_flag = ''N'''||
' AND :b_attr_exists_count > 0'||
' AND :b_status = ''LOAD''';
x_update_stmt :=
'UPDATE '||p_target_attr_table||' A'||
' SET dim_attribute_numeric_member = :b_dim_attribute_numeric_member'||
' ,dim_attribute_value_set_id = :b_dim_attribute_value_set_id'||
' ,dim_attribute_varchar_member = :b_dim_attribute_varchar_member'||
' ,number_assign_value = :b_number_assign_value'||
' ,varchar_assign_value = :b_varchar_assign_value'||
' ,date_assign_value = :b_date_assign_value'||
',last_update_date = sysdate '||
',last_updated_by = :b_apps_user_id'||
',last_update_login = :b_login_id'||
' WHERE A.'||p_member_col||' = :b_member_id'||
' AND A.attribute_id = :b_attribute_id'||
' AND A.version_id = :b_version_id'||
' AND :b_allow_mult_assig_flag = ''N'''||
' AND :b_attr_exists_count > 0'||
' AND :b_status = ''LOAD'''||
' AND :b_use_interim_table_flag = ''N''';
'UPDATE '||p_target_attr_table||' A'||
' SET dim_attribute_numeric_member = :b_dim_attribute_numeric_member'||
' ,dim_attribute_value_set_id = :b_dim_attribute_value_set_id'||
' ,dim_attribute_varchar_member = :b_dim_attribute_varchar_member'||
' ,number_assign_value = :b_number_assign_value'||
' ,varchar_assign_value = :b_varchar_assign_value'||
' ,date_assign_value = :b_date_assign_value'||
',last_update_date = sysdate '||
',last_updated_by = '||gv_apps_user_id||
',last_update_login = '||gv_login_id||
' WHERE A.'||p_member_col||' = (SELECT '||p_member_col||
' FROM '||p_target_b_table||' B'||
' WHERE to_char(B.'||p_member_dc_col||') = :b_member_dc)'||
' AND A.attribute_id = :b_attribute_id'||
' AND A.version_id = :b_version_id'||
' AND :b_allow_mult_assig_flag = ''N'''||
' AND :b_attr_exists_count > 0'||
' AND :b_status = ''LOAD'''||
' AND :b_use_interim_table_flag = ''N''';
'build_attr_update_stmt',
'End');
END build_attr_update_stmt;
| Called after New_Members and ATTR_ASSIGN_UPDATE
| procedures are complete
| Identifies Date Overlap records for CAL_PERIOD loads
| This procedure does not employ MP - it is single threaded
| because all cal_periods to be loaded must be evaluated
| for each member to be loaded
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
|
| OUT:
|
|
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
| If operation_mode = 'NEW_MEMBERS', then Bulk Collect CAL_PERIOD_IDs
| to check from FEM_CALP_INTERIM_T
| Else operation_mode = 'ATTR_UPDATE', then Bulk Collect CAL_PERIOD_IDs
| to check from join of FEM_CALP_ATTR_INTERIM_T and FEM_CALP_INTERIM_T
| where the attribute = CAL_PERIOD_START_DATE
| Note that FEM_CALP_INTERIM_T is always populated, even for Attr Update,
| which is why we can use this table to perform the overlap checks
|
| Once the list of CAL_PERIOD_IDs to check is populated query the
| FEM_CALP_INTERIM_T table for each one to see if any date overlaps exist
| This table will be populated for all CAL_PERIODs in the load, whether
| for a New_Member phase or an ATTR_ASSIGN_UPDATE phase
|
| For NEW_MEMBER phase, if an overlap is found, update the OVERLAP_FLAG = 'Y'
| in FEM_CALP_INTERIM_T for that cal_period_id.
| -- also update the STATUS in the _B_T, _TL_T and ATTR_T tables for CAL_PERIOD
| for each member as follows:
| ATTR_T table: CALENDAR_PERIOD_START_DATE attr row = "OVERLAP_START_DATE_IN_LOAD"
| ATTR_T table: all other attr rows = "INVALID_MEMBER"
| TL_T table: "INVALID_MEMBER"
| B_T table: "INVALID_REQUIRED_ATTRIBUTE"
|
| For ATTR_UPDATE phase, if an overlap is found, update the OVERLAP_FLAG = 'Y'
| in FEM_CALP_ATTR_INTERIM_T for the "START_DATE" attribute rows
| -- also update the STATUS in the _ATTR_T table as follows:
| CALENDAR_PERIOD_START_DATE attr row = "OVERLAP_START_DATE_IN_LOAD"
|
| Note: We perform the STATUS updates on the interface tables in this procedure
| since we already have the bad rows in our array. Rather than require
| a requery of the Interim tables in another procedure, it's more performant just
| to perform the status update now, even though it is single threaded
|
| MODIFICATION HISTORY
| Rob Flippo 03-JAN-05 Created
|
+===========================================================================*/
procedure calp_date_overlap_check(x_rows_rejected OUT NOCOPY NUMBER
,p_operation_mode IN VARCHAR2
)
IS
t_cal_period_id number_type;
x_select_stmt VARCHAR2(4000);
x_update_stmt VARCHAR2(4000);
x_update_mbr_status_stmt VARCHAR2(4000);
x_update_tl_status_stmt VARCHAR2(4000);
x_update_attr_status_stmt VARCHAR2(4000);
x_update_attrlab_status_stmt VARCHAR2(4000);
build_calp_status_update_stmt ('ATTRIBUTE_LABEL'
,v_source_attr_table
,x_update_attrlab_status_stmt);
x_select_stmt := 'SELECT rowid'||
', cal_period_id'||
', calendar_id'||
', dimension_group_id'||
', cal_period_end_date'||
', cal_period_start_date'||
', overlap_flag'||
', cal_period_number'||
', calendar_display_code'||
', dimension_group_display_code'||
' FROM fem_calp_interim_t'||
' WHERE adjustment_period_flag = ''N''';
build_calp_status_update_stmt ('NO_LABEL'
,v_source_b_table
,x_update_mbr_status_stmt);
build_calp_status_update_stmt ('NO_LABEL'
,v_source_tl_table
,x_update_tl_status_stmt);
build_calp_status_update_stmt ('NO_LABEL'
,v_source_attr_table
,x_update_attr_status_stmt);
x_select_stmt := 'SELECT AI.rowid'||
', AI.cal_period_id'||
', I.calendar_id'||
', I.dimension_group_id'||
', I.cal_period_end_date'||
', I.cal_period_start_date'||
', I.overlap_flag'||
', I.cal_period_number'||
', I.calendar_display_code'||
', I.dimension_group_display_code'||
' FROM fem_calp_attr_interim_t AI'||
',fem_calp_interim_t I'||
',fem_dim_attributes_b A'||
' WHERE AI.cal_period_id = I.cal_period_id'||
' AND AI.attribute_id = A.attribute_id'||
' AND A.attribute_varchar_label = ''CAL_PERIOD_START_DATE'''||
' AND I.adjustment_period_flag = ''N''';
x_date_check_stmt := 'SELECT count(*) FROM fem_calp_interim_t'||
' WHERE cal_period_end_date >= :b_start_date'||
' AND cal_period_start_date <= :b_end_date'||
' AND calendar_id = :b_calendar_id'||
' AND dimension_group_id = :b_dimension_group_id'||
' AND adjustment_period_flag = ''N'''||
' AND cal_period_id <> :b_cal_period_id';
x_update_stmt := 'UPDATE '||v_interim_table_name||
' SET overlap_flag = ''Y'''||
' WHERE rowid = :b_rowid'||
' AND :b_overlap_flag = ''Y''';
(c_log_level_1,c_block||'.'||'x_select_stmt',x_select_stmt);
OPEN cv_get_rows FOR x_select_stmt;
(c_log_level_1,c_block||'.'||'calp_date_overlap_check','Update interim table');
EXECUTE IMMEDIATE x_update_stmt
USING t_rowid(i)
,t_overlap_flag(i);
(c_log_level_1,c_block||'.'||'calp_date_overlap_check','Update status of bad records');
EXECUTE IMMEDIATE x_update_mbr_status_stmt
USING 'INVALID_REQUIRED_ATTRIBUTE'
,t_cal_period_number(i)
,t_end_date(i)
,t_calendar_dc(i)
,t_dimension_group_dc(i)
,t_overlap_flag(i);
EXECUTE IMMEDIATE x_update_tl_status_stmt
USING 'INVALID_MEMBER'
,t_cal_period_number(i)
,t_end_date(i)
,t_calendar_dc(i)
,t_dimension_group_dc(i)
,t_overlap_flag(i);
EXECUTE IMMEDIATE x_update_attr_status_stmt
USING 'INVALID_MEMBER'
,t_cal_period_number(i)
,t_end_date(i)
,t_calendar_dc(i)
,t_dimension_group_dc(i)
,t_overlap_flag(i);
EXECUTE IMMEDIATE x_update_attrlab_status_stmt
USING 'OVERLAP_START_DATE_IN_LOAD'
,t_cal_period_number(i)
,t_end_date(i)
,t_calendar_dc(i)
,t_dimension_group_dc(i)
,t_overlap_flag(i);
t_rowid.DELETE;
t_cal_period_id.DELETE;
t_calendar_id.DELETE;
t_dimension_group_id.DELETE;
t_start_date.DELETE;
t_end_date.DELETE;
t_overlap_flag.DELETE;
t_cal_period_number.DELETE;
t_calendar_dc.DELETE;
t_dimension_group_dc.DELETE;
v_src_sys_select_stmt VARCHAR2(4000);
SELECT source_system_code
INTO v_source_system_code
FROM fem_source_systems_b
WHERE source_system_display_code = p_source_system_dc;
SELECT count(*)
INTO v_status_count
FROM fem_dim_load_status
WHERE dimension_id = p_dimension_id
AND source_system_code = v_source_system_code;
v_src_sys_select_stmt := 'SELECT count(*) FROM '||p_source_attr_table||
' WHERE attribute_varchar_label = ''SOURCE_SYSTEM_CODE'''||
' AND attribute_assign_value = '''||p_source_system_dc||''''||
' AND rownum=1';
EXECUTE IMMEDIATE v_src_sys_select_stmt
INTO v_attr_count;
INSERT INTO fem_dim_load_status (DIMENSION_ID
,SOURCE_SYSTEM_CODE
,LOAD_STATUS
,REPROCESS_ERRORS_FLAG
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
SELECT p_dimension_id
,v_source_system_code
,v_dim_load_status
,'N'
,sysdate
,gv_apps_user_id
,gv_apps_user_id
,sysdate
,gv_login_id
,1
FROM dual;
UPDATE fem_dim_load_status
SET load_status = v_dim_load_status,
last_updated_by = gv_apps_user_id,
last_update_date = sysdate,
last_update_login = gv_login_id
WHERE dimension_id = p_dimension_id
AND source_system_code = v_source_system_code;
t_component_dim_dc.DELETE;
t_display_code.DELETE;
SELECT dimension_active_flag
INTO l_dim_active_flag
FROM Fem_Xdim_Dimensions_VL
WHERE dimension_varchar_label = p_dimension_varchar_label;
t_metadata.DELETE;
SELECT x.dimension_id
,x.dimension_varchar_label
,x.member_col
,x.member_display_code_col
,x.member_b_table_name
,x.value_set_required_flag
FROM FEM_COLUMN_REQUIREMNT_VL c
,FEM_XDIM_DIMENSIONS_VL x
WHERE c.dimension_id = x.dimension_id
AND c.cost_obj_dim_component_flag = 'Y'
ORDER BY 1 )
LOOP
FOR i IN 1 .. t_metadata.COUNT LOOP
IF c_metadata_cost.member_display_code_col = t_component_dim_dc(i) THEN
t_metadata(i).dimension_id := c_metadata_cost.dimension_id;
' SELECT '||c_metadata_cost.member_col||
' FROM '||c_metadata_cost.member_b_table_name||
' WHERE enabled_flag = ''Y'''||
' AND '||c_metadata_cost.member_display_code_col||' = :b_dc_val';
g_select_statement :=
'SELECT rowid,'||
' GLOBAL_VS_COMBO_DISPLAY_CODE,'||
' financial_elem_display_code,'||
' ledger_display_code,'||
' product_display_code,'||
' CCTR_ORG_DISPLAY_CODE,'||
' customer_display_code,'||
' channel_display_code,'||
' project_display_code,'||
' user_dim1_display_code,'||
' user_dim2_display_code,'||
' user_dim3_display_code,'||
' user_dim4_display_code,'||
' user_dim5_display_code,'||
' user_dim6_display_code,'||
' user_dim7_display_code,'||
' user_dim8_display_code,'||
' user_dim9_display_code,'||
' user_dim10_display_code,'||
' status'||
' FROM FEM_COST_OBJECTS_T '||
' WHERE {{data_slice}} ';
SELECT x.dimension_id
,x.dimension_varchar_label
,x.member_col
,x.member_display_code_col
,x.member_b_table_name
,x.value_set_required_flag
FROM FEM_COLUMN_REQUIREMNT_VL c
,FEM_XDIM_DIMENSIONS_VL x
WHERE c.dimension_id = x.dimension_id
AND c.activity_dim_component_flag = 'Y'
ORDER BY 1 )
LOOP
FOR i IN 1 .. t_metadata.COUNT LOOP
IF c_metadata_activity.member_display_code_col
= t_component_dim_dc(i) THEN
t_metadata(i).dimension_id := c_metadata_activity.dimension_id;
' SELECT '||c_metadata_activity.member_col||
' FROM '||c_metadata_activity.member_b_table_name||
' WHERE enabled_flag = ''Y'''||
' AND '||c_metadata_activity.member_display_code_col||' = :b_dc_val';
g_select_statement :=
'SELECT rowid,'||
' GLOBAL_VS_COMBO_DISPLAY_CODE,'||
' TASK_DISPLAY_CODE,'||
' CCTR_ORG_DISPLAY_CODE,'||
' customer_display_code,'||
' channel_display_code,'||
' product_display_code,'||
' project_display_code,'||
' user_dim1_display_code,'||
' user_dim2_display_code,'||
' user_dim3_display_code,'||
' user_dim4_display_code,'||
' user_dim5_display_code,'||
' user_dim6_display_code,'||
' user_dim7_display_code,'||
' user_dim8_display_code,'||
' user_dim9_display_code,'||
' user_dim10_display_code,'||
' status'||
' FROM FEM_ACTIVITIES_T '||
' WHERE {{data_slice}} ';
| dimension members and inserts only the valid records into the member table
| of Composite Dimensions.The invalid records will be processed in
| 'Error Reproceesing' Mode.
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS :
|
| RETURNS : NONE
|
| NOTES
|
|
| MODIFICATION HISTORY
| Aturlapa 31-MAR-05 Created
| sshanmug 17-MAY-05 Incorporated comments from Nico
|
+===========================================================================*/
/* procedure Process_Rows (x_status OUT NOCOPY NUMBER
,x_message OUT NOCOPY VARCHAR2
,x_rows_processed OUT NOCOPY NUMBER
,x_rows_loaded OUT NOCOPY NUMBER
,x_rows_rejected OUT NOCOPY NUMBER
,p_eng_sql IN VARCHAR2
,p_data_slc IN VARCHAR2
,p_proc_num IN VARCHAR2
,p_slice_id IN VARCHAR2
,p_fetch_limit IN NUMBER
,p_load_type IN VARCHAR2
,p_dimension_varchar_label IN VARCHAR2
,p_execution_mode IN VARCHAR2
,p_structure_id IN NUMBER)
IS
c_proc_name CONSTANT varchar2(20) := 'Process_Rows';
v_select_stmt LONG;
v_update_stmt VARCHAR2(4000);
v_delete_stmt VARCHAR2(4000);
v_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
v_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
v_insert_cost_stmt CONSTANT LONG :=
'INSERT INTO FEM_COST_OBJECTS ('||
' COST_OBJECT_ID, '||
' COST_OBJECT_DISPLAY_CODE, '||
' SUMMARY_FLAG, '||
' START_DATE_ACTIVE, '||
' END_DATE_ACTIVE, '||
' COST_OBJECT_STRUCTURE_ID, '||
' LOCAL_VS_COMBO_ID, '||
' UOM_CODE, '||
' FINANCIAL_ELEM_ID, '||
' LEDGER_ID, '||
' PRODUCT_ID, '||
' COMPANY_COST_CENTER_ORG_ID, '||
' CUSTOMER_ID, '||
' CHANNEL_ID, '||
' PROJECT_ID, '||
' USER_DIM1_ID, '||
' USER_DIM2_ID, '||
' USER_DIM3_ID, '||
' USER_DIM4_ID, '||
' USER_DIM5_ID, '||
' USER_DIM6_ID, '||
' USER_DIM7_ID, '||
' USER_DIM8_ID, '||
' USER_DIM9_ID, '||
' USER_DIM10_ID, '||
' SEGMENT1, '||
' SEGMENT2, '||
' SEGMENT3, '||
' SEGMENT4, '||
' SEGMENT5, '||
' SEGMENT6, '||
' SEGMENT7, '||
' SEGMENT8, '||
' SEGMENT9, '||
' SEGMENT10, '||
' SEGMENT11, '||
' SEGMENT12, '||
' SEGMENT13, '||
' SEGMENT14, '||
' SEGMENT15, '||
' SEGMENT16, '||
' SEGMENT17, '||
' SEGMENT18, '||
' SEGMENT19, '||
' SEGMENT20, '||
' SEGMENT21, '||
' SEGMENT22, '||
' SEGMENT23, '||
' SEGMENT24, '||
' SEGMENT25, '||
' SEGMENT26, '||
' SEGMENT27, '||
' SEGMENT28, '||
' SEGMENT29, '||
' SEGMENT30, '||
' CREATION_DATE, '||
' CREATED_BY, '||
' LAST_UPDATED_BY, '||
' LAST_UPDATE_DATE, '||
' LAST_UPDATE_LOGIN, '||
' OBJECT_VERSION_NUMBER, '||
' ENABLED_FLAG, '||
' PERSONAL_FLAG, '||
' READ_ONLY_FLAG )'||
' SELECT fem_cost_objects_s.nextval,'||
' :b_COST_OBJECT_DISPLAY_CODE, '||
' :b_SUMMARY_FLAG, '||
' :b_START_DATE_ACTIVE, '||
' :b_END_DATE_ACTIVE, '||
' :b_COST_OBJECT_STRUCTURE_ID, '||
' :b_LOCAL_VS_COMBO_ID, '||
' :b_UOM_CODE, '||
' :b_FINANCIAL_ELEM_ID, '||
' :b_LEDGER_ID, '||
' :b_PRODUCT_ID, '||
' :b_COMPANY_COST_CENTER_ORG_ID, '||
' :b_CUSTOMER_ID, '||
' :b_CHANNEL_ID, '||
' :b_PROJECT_ID, '||
' :b_USER_DIM1_ID, '||
' :b_USER_DIM2_ID, '||
' :b_USER_DIM3_ID, '||
' :b_USER_DIM4_ID, '||
' :b_USER_DIM5_ID, '||
' :b_USER_DIM6_ID, '||
' :b_USER_DIM7_ID, '||
' :b_USER_DIM8_ID, '||
' :b_USER_DIM9_ID, '||
' :b_USER_DIM10_ID, '||
' :b_SEGMENT1, '||
' :b_SEGMENT2, '||
' :b_SEGMENT3, '||
' :b_SEGMENT4, '||
' :b_SEGMENT5, '||
' :b_SEGMENT6, '||
' :b_SEGMENT7, '||
' :b_SEGMENT8, '||
' :b_SEGMENT9, '||
' :b_SEGMENT10, '||
' :b_SEGMENT11, '||
' :b_SEGMENT12, '||
' :b_SEGMENT13, '||
' :b_SEGMENT14, '||
' :b_SEGMENT15, '||
' :b_SEGMENT16, '||
' :b_SEGMENT17, '||
' :b_SEGMENT18, '||
' :b_SEGMENT19, '||
' :b_SEGMENT20, '||
' :b_SEGMENT21, '||
' :b_SEGMENT22, '||
' :b_SEGMENT23, '||
' :b_SEGMENT24, '||
' :b_SEGMENT25, '||
' :b_SEGMENT26, '||
' :b_SEGMENT27, '||
' :b_SEGMENT28, '||
' :b_SEGMENT29, '||
' :b_SEGMENT30, '||
' :b_CREATION_DATE, '||
' :b_CREATED_BY, '||
' :b_LAST_UPDATED_BY, '||
' :b_LAST_UPDATE_DATE, '||
' :b_LAST_UPDATE_LOGIN, '||
' :b_OBJECT_VERSION_NUMBER, '||
' :b_ENABLED_FLAG, '||
' :b_PERSONAL_FLAG, '||
' :b_read_only_flag '||
' FROM dual'||
' WHERE :b_status = ''LOAD''';
v_insert_activity_stmt CONSTANT LONG :=
'INSERT INTO FEM_ACTIVITIES ('||
' ACTIVITY_ID, '||
' ACTIVITY_DISPLAY_CODE, '||
' SUMMARY_FLAG, '||
' START_DATE_ACTIVE, '||
' END_DATE_ACTIVE, '||
' ACTIVITY_STRUCTURE_ID, '||
' LOCAL_VS_COMBO_ID, '||
' TASK_ID, '||
' COMPANY_COST_CENTER_ORG_ID, '||
' CUSTOMER_ID, '||
' CHANNEL_ID, '||
' PRODUCT_ID, '||
' PROJECT_ID, '||
' USER_DIM1_ID, '||
' USER_DIM2_ID, '||
' USER_DIM3_ID, '||
' USER_DIM4_ID, '||
' USER_DIM5_ID, '||
' USER_DIM6_ID, '||
' USER_DIM7_ID, '||
' USER_DIM8_ID, '||
' USER_DIM9_ID, '||
' USER_DIM10_ID, '||
' SEGMENT1, '||
' SEGMENT2, '||
' SEGMENT3, '||
' SEGMENT4, '||
' SEGMENT5, '||
' SEGMENT6, '||
' SEGMENT7, '||
' SEGMENT8, '||
' SEGMENT9, '||
' SEGMENT10, '||
' SEGMENT11, '||
' SEGMENT12, '||
' SEGMENT13, '||
' SEGMENT14, '||
' SEGMENT15, '||
' SEGMENT16, '||
' SEGMENT17, '||
' SEGMENT18, '||
' SEGMENT19, '||
' SEGMENT20, '||
' SEGMENT21, '||
' SEGMENT22, '||
' SEGMENT23, '||
' SEGMENT24, '||
' SEGMENT25, '||
' SEGMENT26, '||
' SEGMENT27, '||
' SEGMENT28, '||
' SEGMENT29, '||
' SEGMENT30, '||
' CREATION_DATE, '||
' CREATED_BY, '||
' LAST_UPDATED_BY, '||
' LAST_UPDATE_DATE, '||
' LAST_UPDATE_LOGIN, '||
' OBJECT_VERSION_NUMBER, '||
' ENABLED_FLAG, '||
' PERSONAL_FLAG, '||
' READ_ONLY_FLAG )'||
' SELECT fem_activities_s.nextval,'||
' :b_ACTIVITY_DISPLAY_CODE, '||
' :b_SUMMARY_FLAG, '||
' :b_START_DATE_ACTIVE, '||
' :b_END_DATE_ACTIVE, '||
' :b_ACTIVITY_STRUCTURE_ID, '||
' :b_LOCAL_VS_COMBO_ID, '||
' :b_TASK_ID, '||
' :b_COMPANY_COST_CENTER_ORG_ID, '||
' :b_CUSTOMER_ID, '||
' :b_CHANNEL_ID, '||
' :b_PRODUCT_ID, '||
' :b_PROJECT_ID, '||
' :b_USER_DIM1_ID, '||
' :b_USER_DIM2_ID, '||
' :b_USER_DIM3_ID, '||
' :b_USER_DIM4_ID, '||
' :b_USER_DIM5_ID, '||
' :b_USER_DIM6_ID, '||
' :b_USER_DIM7_ID, '||
' :b_USER_DIM8_ID, '||
' :b_USER_DIM9_ID, '||
' :b_USER_DIM10_ID, '||
' :b_SEGMENT1, '||
' :b_SEGMENT2, '||
' :b_SEGMENT3, '||
' :b_SEGMENT4, '||
' :b_SEGMENT5, '||
' :b_SEGMENT6, '||
' :b_SEGMENT7, '||
' :b_SEGMENT8, '||
' :b_SEGMENT9, '||
' :b_SEGMENT10, '||
' :b_SEGMENT11, '||
' :b_SEGMENT12, '||
' :b_SEGMENT13, '||
' :b_SEGMENT14, '||
' :b_SEGMENT15, '||
' :b_SEGMENT16, '||
' :b_SEGMENT17, '||
' :b_SEGMENT18, '||
' :b_SEGMENT19, '||
' :b_SEGMENT20, '||
' :b_SEGMENT21, '||
' :b_SEGMENT22, '||
' :b_SEGMENT23, '||
' :b_SEGMENT24, '||
' :b_SEGMENT25, '||
' :b_SEGMENT26, '||
' :b_SEGMENT27, '||
' :b_SEGMENT28, '||
' :b_SEGMENT29, '||
' :b_SEGMENT30, '||
' :b_CREATION_DATE, '||
' :b_CREATED_BY, '||
' :b_LAST_UPDATED_BY, '||
' :b_LAST_UPDATE_DATE, '||
' :b_LAST_UPDATE_LOGIN, '||
' :b_OBJECT_VERSION_NUMBER, '||
' :b_ENABLED_FLAG, '||
' :b_PERSONAL_FLAG, '||
' :b_read_only_flag '||
' FROM dual'||
' WHERE :b_status = ''LOAD''';
v_update_cost_stmt CONSTANT VARCHAR2(4000) :=
'UPDATE FEM_COST_OBJECTS_T '||
' SET status = :b_status'||
' WHERE rowid = :b_rowid';
v_update_activity_stmt CONSTANT VARCHAR2(4000) :=
'UPDATE FEM_ACTIVITIES_T '||
' SET status = :b_status'||
' WHERE rowid = :b_rowid';
v_delete_cost_stmt CONSTANT VARCHAR2(4000) :=
'DELETE FROM FEM_COST_OBJECTS_T '||
' WHERE rowid = :b_rowid'||
' AND :b_status = ''LOAD''';
v_delete_activity_stmt CONSTANT VARCHAR2(4000) :=
'DELETE FROM FEM_ACTIVITIES_T '||
' WHERE rowid = :b_rowid'||
' AND :b_status = ''LOAD''';
v_select_stmt := REPLACE(p_eng_sql,'{{data_slice}}',v_data_slc);
v_update_stmt := v_update_cost_stmt;
v_delete_stmt := v_delete_cost_stmt;
v_update_stmt := v_update_activity_stmt;
v_delete_stmt := v_delete_activity_stmt;
,p_msg_text => 'v_update_stmt '||v_update_stmt||
'v_member_table_name'||v_member_table_name);
,p_msg_text => 'v_delete_stmt '||v_delete_stmt||
'v_member_table_name'||v_member_table_name);
' UPDATE '||v_member_table_name||' b'||
' SET status = ''LOAD'''||
' WHERE status <> ''LOAD'''||
' AND '||v_data_slc;
' SELECT a.rowid '||
' FROM '||v_member_table_name||' a'||
' WHERE not exists '||
' (SELECT 1 FROM fem_global_vs_combos_b b '||
' WHERE a.global_vs_combo_display_code= b.global_vs_combo_display_code)'||
' AND a.status = ''LOAD'''||
' AND '||v_data_slc;
EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
t_rowid.DELETE;
SELECT a.attribute_id
,v.version_id
,a.attribute_varchar_label
,a.dimension_id
FROM fem_dim_attributes_vl a
,fem_dim_attr_versions_vl v
WHERE a.attribute_id = v.attribute_id
AND v.default_version_flag = 'Y'
AND (
(a.attribute_varchar_label IN ('COST_OBJECT_UNIT_FLAG','DATA_TYPE_CODE')
AND a.dimension_id = 12) -- Financial Element
OR
(a.attribute_varchar_label IN ('GLOBAL_VS_COMBO')
AND a.dimension_id = 7) -- Ledger
)
AND v.default_version_flag = 'Y' )
LOOP
IF c_attr.dimension_id = 12 THEN -- Financial Element
IF c_attr.attribute_varchar_label = 'COST_OBJECT_UNIT_FLAG' THEN
l_count :=0 ;
' SELECT b.rowid'||
' FROM fem_cost_objects_t b'||
' ,fem_fin_elems_attr a'||
' ,fem_fin_elems_vl m'||
' WHERE a.financial_elem_id = m.financial_elem_id'||
' AND m.financial_elem_display_code = b.financial_elem_display_code'||
' AND a.attribute_id = '||c_attr.attribute_id||
' AND a.version_id = '||c_attr.version_id||
' AND a.dim_attribute_varchar_member <> ''Y'''||
' AND b.status = ''LOAD'''||
' AND '||v_data_slc;
EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
' SELECT b.rowid'||
' FROM fem_cost_objects_t b'||
' ,fem_fin_elems_attr a'||
' ,fem_fin_elems_vl m'||
' WHERE a.financial_elem_id = m.financial_elem_id'||
' AND m.financial_elem_display_code = b.financial_elem_display_code'||
' AND a.attribute_id = '||c_attr.attribute_id||
' AND a.version_id = '||c_attr.version_id||
' AND a.dim_attribute_varchar_member <> ''RATE'''||
' AND b.status = ''LOAD'''||
' AND '||v_data_slc;
EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
' SELECT b.rowid'||
' FROM fem_cost_objects_t b'||
' WHERE NOT EXISTS ('||
' SELECT 1'||
' FROM fem_ledgers_b l'||
' ,fem_ledgers_attr a'||
' ,fem_global_vs_combos_b g'||
' WHERE l.ledger_display_code = b.ledger_display_code'||
' AND a.ledger_id = l.ledger_id'||
' AND a.attribute_id = '||c_attr.attribute_id||
' AND a.version_id = '||c_attr.version_id||
' AND a.dim_attribute_numeric_member = g.global_vs_combo_id'||
' AND g.global_vs_combo_display_code = b.global_vs_combo_display_code'||
' )'||
' AND status = ''LOAD'''||
' AND '||v_data_slc;
EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
t_rowid.DELETE;
' SELECT b.rowid '||
' FROM '||v_member_table_name||' b'||
' WHERE '||t_metadata(i).member_display_code_col||' is null'||
' AND status = ''LOAD'''||
' AND '||v_data_slc;
' SELECT b.rowid '||
' FROM '||v_member_table_name||' b'||
' WHERE '||t_metadata(i).member_display_code_col||' is not null'||
' AND status = ''LOAD'''||
' AND '||v_data_slc;
EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
t_rowid.DELETE;
OPEN cv_get_rows FOR v_select_stmt;
SELECT g.dimension_id
,g.value_set_id
,g.global_vs_combo_id
FROM FEM_GLOBAL_VS_COMBO_DEFS g,
FEM_GLOBAL_VS_COMBOS_b m
WHERE g.global_vs_combo_id = m.global_vs_combo_id
AND g.dimension_id = t_metadata(i).dimension_id
AND m.global_vs_combo_display_code = t_global_vs_combo_dc(j)
ORDER BY 1)
LOOP
t_global_vs_combo_id(j) := c_value_set.global_vs_combo_id;
/* SELECT ledger_id
INTO t_ledger_id(j)
FROM fem_ledgers_vl
WHERE ledger_display_code = t_ledger_dc(j);
SELECT prod.dim_attribute_varchar_member AS uom_code
INTO t_uom_code(j)
FROM fem_products_attr prod,
fem_dim_attributes_b attr,
fem_dim_attr_versions_vl ver
WHERE prod.product_id = t_product_id(j)
AND prod.attribute_id = attr.attribute_id
AND prod.version_id = ver.version_id
AND attr.attribute_varchar_label = 'PRODUCT_UOM'
AND ver.attribute_id = attr.attribute_id
AND ver.default_version_flag = 'Y';
EXECUTE IMMEDIATE v_insert_cost_stmt
USING t_display_code(i),
'N',
sysdate,
sysdate,
p_structure_id,
t_global_vs_combo_id(i),
t_uom_code(i),
t_fin_elem_id(i),
t_ledger_id(i),
t_product_id(i),
t_cctr_org_id(i),
t_customer_id(i),
t_channel_id(i),
t_project_id(i),
t_user_dim1_id(i),
t_user_dim2_id(i),
t_user_dim3_id(i),
t_user_dim4_id(i),
t_user_dim5_id(i),
t_user_dim6_id(i),
t_user_dim7_id(i),
t_user_dim8_id(i),
t_user_dim9_id(i),
t_user_dim10_id(i),
t_fin_elem_dc(i),
t_ledger_dc(i),
t_product_dc(i),
t_cctr_org_dc(i),
t_customer_dc(i),
t_channel_dc(i),
t_project_dc(i),
t_user_dim1_dc(i),
t_user_dim2_dc(i),
t_user_dim3_dc(i),
t_user_dim4_dc(i),
t_user_dim5_dc(i),
t_user_dim6_dc(i),
t_user_dim7_dc(i),
t_user_dim8_dc(i),
t_user_dim9_dc(i),
t_user_dim10_dc(i),
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
sysdate,
v_CREATED_BY,
v_LAST_UPDATED_BY,
sysdate,
v_LAST_UPDATE_LOGIN,
1,
'Y',
'N',
'N',
t_status(i);
EXECUTE IMMEDIATE v_insert_activity_stmt
USING t_display_code(i),
'N',
sysdate,
sysdate,
p_structure_id,
t_global_vs_combo_id(i),
t_task_id(i),
t_cctr_org_id(i),
t_customer_id(i),
t_channel_id(i),
t_product_id(i),
t_project_id(i),
t_user_dim1_id(i),
t_user_dim2_id(i),
t_user_dim3_id(i),
t_user_dim4_id(i),
t_user_dim5_id(i),
t_user_dim6_id(i),
t_user_dim7_id(i),
t_user_dim8_id(i),
t_user_dim9_id(i),
t_user_dim10_id(i),
t_task_dc(i),
t_cctr_org_dc(i),
t_customer_dc(i),
t_channel_dc(i),
t_product_dc(i),
t_project_dc(i),
t_user_dim1_dc(i),
t_user_dim2_dc(i),
t_user_dim3_dc(i),
t_user_dim4_dc(i),
t_user_dim5_dc(i),
t_user_dim6_dc(i),
t_user_dim7_dc(i),
t_user_dim8_dc(i),
t_user_dim9_dc(i),
t_user_dim10_dc(i),
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
sysdate,
v_CREATED_BY,
v_LAST_UPDATED_BY,
sysdate,
v_LAST_UPDATE_LOGIN,
1,
'Y',
'N',
'N',
t_status(i);
EXECUTE IMMEDIATE v_update_stmt USING t_status(i),t_rowid(i);
EXECUTE IMMEDIATE v_delete_stmt USING t_rowid(i),t_status(i);
t_fin_elem_id.DELETE;
t_ledger_id.DELETE;
t_rowid.DELETE;
t_global_vs_combo_id.DELETE;
t_task_id.DELETE;
t_cctr_org_id.DELETE;
t_channel_id.DELETE;
t_customer_id.DELETE;
t_product_id.DELETE;
t_project_id.DELETE;
t_user_dim1_id.DELETE;
t_user_dim2_id.DELETE;
t_user_dim3_id.DELETE;
t_user_dim4_id.DELETE;
t_user_dim5_id.DELETE;
t_user_dim6_id.DELETE;
t_user_dim7_id.DELETE;
t_user_dim8_id.DELETE;
t_user_dim9_id.DELETE;
t_user_dim10_id.DELETE;
t_global_vs_combo_dc.DELETE;
t_task_dc.DELETE;
t_cctr_org_dc.DELETE;
t_channel_dc.DELETE;
t_customer_dc.DELETE;
t_product_dc.DELETE;
t_project_dc.DELETE;
t_fin_elem_dc.DELETE;
t_ledger_dc.DELETE;
t_user_dim1_dc.DELETE;
t_user_dim2_dc.DELETE;
t_user_dim3_dc.DELETE;
t_user_dim4_dc.DELETE;
t_user_dim5_dc.DELETE;
t_user_dim6_dc.DELETE;
t_user_dim7_dc.DELETE;
t_user_dim8_dc.DELETE;
t_user_dim9_dc.DELETE;
t_user_dim10_dc.DELETE;
t_display_code.DELETE;
t_status.DELETE;
| Pre-validation - identifies and updates bad records
| New Members - creates new dimension groups
| TL Update - updates the name/desc of groups
| Base Update - updates base properties of the groups
| 4) Load Dimension Members
| Pre-validation - identifies and updates bad records
| New Members - creates new dim members and their req. attr assignments
| TL Update - updates name/desc of members
| Base Update - updates base properties of members
| Pre-valid Attr - identifies bad optional attr records
| Attr Update - loads new optional attr assignments and updates existing
| assignments
| 5) Engine_Master_Post - post error counts and PL status
|
| SPECIAL NOTE: The x_rows_loaded output variable is only populated if a
| new member is created. It does not represent the number of
| rows loaded. Rather, it is just serving as a flag to indicate
| that at least one new member was created. The other procedures
| such as TL_UPDATE, BASE_UPDATE and ATTR_ASSIGN_UPDATE do not
| populate this output value.
|
|
|
| MODIFICATION HISTORY
| Rob Flippo 21-OCT-03 Created
| Rob Flippo 08-SEP-04 Added condition on the MP Master return status so
| that if no data slices found on Pre Validation, the
| loader continues rather than terminating
| sshanmug 28-APR-05 Added Logic for Composite_dimension_loader
| Rob Flippo 31-MAY-05 Bug#3923485 Removed p_date_format_mask input parm
| now using ICX: Date Format Mask profile option
| navekuma 26-APR-06 Bug#4736810. Added the call to get_mp_rows_rejected
| after the call to MP engine for composite dimensions.
+===========================================================================*/
PROCEDURE Load_Dimension (
x_rows_rejected_accum OUT NOCOPY NUMBER
,x_rows_to_load OUT NOCOPY NUMBER
,p_execution_mode IN VARCHAR2
,p_object_id IN NUMBER
,p_object_definition_id IN NUMBER
,p_dimension_varchar_label IN VARCHAR2
,p_master_request_id IN NUMBER
)
IS
-----------------------
-- Declare constants --
-----------------------
c_proc_name VARCHAR2(30) := 'load_dimension_members';
x_src_sys_select_stmt VARCHAR2(4000);
SELECT composite_dimension_flag,id_flex_num
INTO v_composite_dimension_flag,v_structure_id
FROM Fem_XDim_Dimensions_VL
WHERE dimension_varchar_label = p_dimension_varchar_label;
build_src_sys_select_stmt (p_dimension_varchar_label
,v_source_attr_table
,v_shared_dimension_flag
,x_src_sys_select_stmt);
(c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.src_sys_select_stmt',x_src_sys_select_stmt);
OPEN cv_get_src_sys FOR x_src_sys_select_stmt;
END IF; -- src system selection
(c_log_level_3,c_block||'.'||c_proc_name||'.Start Grp TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
TL_Update (P_ENG_SQL => null
,P_DATA_SLC => null
,P_PROC_NUM => null
,P_PARTITION_CODE => null
,P_FETCH_LIMIT => null
,P_LOAD_TYPE => v_load_type
,P_DIMENSION_VARCHAR_LABEL => p_dimension_varchar_label
,P_DIMENSION_ID => v_dimension_id
,P_TARGET_B_TABLE => c_dimgrp_b_table
,P_TARGET_TL_TABLE => c_dimgrp_tl_table
,P_SOURCE_B_TABLE => c_dimgrp_b_t_table
,P_SOURCE_TL_TABLE => c_dimgrp_tl_t_table
,P_MEMBER_COL => c_dimgrp_col
,P_MEMBER_DC_COL => c_dimgrp_dc_col
,P_MEMBER_NAME_COL => c_dimgrp_name_col
,P_MEMBER_T_DC_COL => c_dimgrp_dc_col
,P_MEMBER_T_NAME_COL => c_dimgrp_name_col
,P_MEMBER_DESCRIPTION_COL => c_dimgrp_desc_col
,P_VALUE_SET_REQUIRED_FLAG => 'N'
,P_SIMPLE_DIMENSION_FLAG => 'Y'
,P_SHARED_DIMENSION_FLAG => 'N'
,P_HIER_DIMENSION_FLAG => 'N'
,P_EXEC_MODE_CLAUSE => v_exec_mode_clause
,P_MASTER_REQUEST_ID => p_master_request_id);
(c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
(c_log_level_3,c_block||'.'||c_proc_name||'.Start Grp Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
Base_Update (P_ENG_SQL => null
,P_DATA_SLC => null
,P_PROC_NUM => null
,P_PARTITION_CODE => null
,P_FETCH_LIMIT => null
,P_LOAD_TYPE => v_load_type
,P_DIMENSION_VARCHAR_LABEL => p_dimension_varchar_label
,P_SIMPLE_DIMENSION_FLAG => 'N'
,P_SHARED_DIMENSION_FLAG => 'N'
,P_DIMENSION_ID => v_dimension_id
,P_VALUE_SET_REQUIRED_FLAG => 'N'
,P_HIER_TABLE_NAME => null
,P_HIER_DIMENSION_FLAG => 'N'
,P_SOURCE_B_TABLE => c_dimgrp_b_t_table
,P_TARGET_B_TABLE => c_dimgrp_b_table
,P_MEMBER_DC_COL => c_dimgrp_dc_col
,P_MEMBER_T_DC_COL => c_dimgrp_dc_col
,P_MEMBER_COL => c_dimgrp_col
,P_EXEC_MODE_CLAUSE => v_exec_mode_clause
,P_MASTER_REQUEST_ID => p_master_request_id);
(c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
(c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
,P_RUN_NAME => 'Base_Update'
,P_ENG_PRG => 'FEM_DIM_MEMBER_LOADER_PKG.Base_Update'
,P_CONDITION => null
,P_FAILED_REQ_ID => null
,P_ARG1 => v_load_type
,P_ARG2 => p_dimension_varchar_label
,P_ARG3 => v_simple_dimension_flag
,P_ARG4 => v_shared_dimension_flag
,P_ARG5 => v_dimension_id
,P_ARG6 => v_value_set_required_flag
,P_ARG7 => v_hier_table_name
,P_ARG8 => v_hier_dimension_flag
,P_ARG9 => v_source_b_table
,P_ARG10 => v_target_b_table
,P_ARG11 => v_member_dc_col
,P_ARG12 => v_member_t_dc_col
,P_ARG13 => v_member_col
,P_ARG14 => v_exec_mode_clause
,P_ARG15 => p_master_request_id
);
(c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
(c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
,P_RUN_NAME => 'TL_Update'
,P_ENG_PRG => 'FEM_DIM_MEMBER_LOADER_PKG.TL_Update'
,P_CONDITION => null
,P_FAILED_REQ_ID => null
,P_ARG1 => v_load_type
,P_ARG2 => p_dimension_varchar_label
,P_ARG3 => v_dimension_id
,P_ARG4 => v_target_b_table
,P_ARG5 => v_target_tl_table
,P_ARG6 => v_source_b_table
,P_ARG7 => v_source_tl_table
,P_ARG8 => v_member_col
,P_ARG9 => v_member_dc_col
,P_ARG10 => v_member_name_col
,P_ARG11 => v_member_t_dc_col
,P_ARG12 => v_member_t_name_col
,P_ARG13 => v_member_description_col
,P_ARG14 => v_value_set_required_flag
,P_ARG15 => v_simple_dimension_flag
,P_ARG16 => v_shared_dimension_flag
,P_ARG17 => v_hier_dimension_flag
,P_ARG18 => v_exec_mode_clause
,P_ARG19 => p_master_request_id
);
(c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
(c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr Attr_Assign_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
,P_RUN_NAME => 'Attr_Assign_Update'
,P_ENG_PRG => 'FEM_DIM_MEMBER_LOADER_PKG.Attr_Assign_Update'
,P_CONDITION => null
,P_FAILED_REQ_ID => null
-- ,P_REUSE_SLICES => 'R'
,P_ARG1 => p_dimension_varchar_label
,P_ARG2 => v_date_format_mask
,P_ARG3 => v_dimension_id
,P_ARG4 => v_target_b_table
,P_ARG5 => v_target_attr_table
,P_ARG6 => v_source_b_table
,P_ARG7 => v_source_attr_table
,P_ARG8 => v_member_col
,P_ARG9 => v_member_dc_col
,P_ARG10 => v_member_t_dc_col
,P_ARG11 => v_value_set_required_flag
,P_ARG12 => v_hier_dimension_flag
,P_ARG13 => v_simple_dimension_flag
,P_ARG14 => v_shared_dimension_flag
,P_ARG15 => v_exec_mode_clause
,P_ARG16 => p_master_request_id
);
,'ATTR_ASSIGN_UPDATE');
,P_ARG1 => 'ATTR_ASSIGN_UPDATE'
,P_ARG2 => p_master_request_id);
END IF; -- Simple Dimension Flag = 'N' (for Attr validation and Attr Update)
,P_ENG_SQL => g_select_statement
,P_TABLE_ALIAS => 'B'
,P_RUN_NAME => 'Process_Rows'
,P_ENG_PRG => 'FEM_COMP_DIM_MEMBER_LOADER_PKG.Process_Rows'
,P_CONDITION => null
,P_FAILED_REQ_ID => null
,P_ARG1 => p_dimension_varchar_label
,P_ARG2 => p_execution_mode
,P_ARG3 => v_structure_id
,P_ARG4 => p_master_request_id);
SELECT loader_object_def_id, dimension_varchar_label
INTO x_object_definition_id, x_dimension_varchar_label
FROM fem_xdim_dimensions_vl
WHERE dimension_id = p_dimension_id;
SELECT object_id
INTO v_object_id
FROM fem_object_definition_b
WHERE object_definition_id = v_object_definition_id
AND object_id IN (SELECT object_id FROM fem_object_catalog_b
WHERE object_type_code = 'DIM_MEMBER_LOADER');
SELECT count(*)
INTO v_sec_folder_count
FROM fem_user_folders
WHERE user_id=c_user_id
AND folder_id=1000;
SELECT X.hier_editor_managed_flag
,X.read_only_flag
,X.value_set_required_flag
,X.simple_dimension_flag
INTO v_hier_editor_managed_flag
,v_xdim_read_only_flag
,v_value_set_required_flag
,v_simple_dimension_flag
FROM fem_xdim_dimensions X, fem_dimensions_b B
WHERE B.dimension_varchar_label = v_dimension_varchar_label
AND B.dimension_id = X.dimension_id;
UPDATE fem_simple_dims_b_t
SET STATUS = 'INVALID_SIMPLE_DIM'
WHERE dimension_varchar_label = v_dimension_varchar_label;
UPDATE fem_simple_dims_tl_t
SET STATUS = 'INVALID_SIMPLE_DIM'
WHERE dimension_varchar_label = v_dimension_varchar_label;
SELECT folder_name
INTO v_sec_folder_name
FROM fem_folders_vl
WHERE folder_id=1000;
FEM_PL_PKG.Update_Obj_Exec_Status(
P_API_VERSION => c_api_version,
P_COMMIT => c_true,
P_REQUEST_ID => gv_request_id,
P_OBJECT_ID => v_object_id,
P_EXEC_STATUS_CODE => 'ERROR_RERUN',
P_USER_ID => gv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
FEM_PL_PKG.Update_Request_Status(
P_API_VERSION => c_api_version,
P_COMMIT => c_true,
P_REQUEST_ID => gv_request_id,
P_EXEC_STATUS_CODE => 'ERROR_RERUN',
P_USER_ID => gv_apps_user_id,
P_LAST_UPDATE_LOGIN => null,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);