[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete_error EXCEPTION;
l_insert_error EXCEPTION;
SELECT 1
INTO dummy
FROM fem_tab_columns_b ftc
WHERE ftc.column_name NOT IN
(SELECT column_name
FROM dba_tab_columns dtc
WHERE dtc.owner = l_owner
AND dtc.table_name=p_table_name)
AND ftc.table_name=p_table_name
AND ROWNUM = 1;
DELETE FROM fem_tab_columns_b
WHERE column_name NOT IN ( SELECT column_name
FROM dba_tab_columns
WHERE table_name = p_table_name
AND owner = l_owner )
AND table_name = p_table_name
RETURNING column_name BULK COLLECT INTO l_column_tab;
DELETE FROM fem_tab_columns_tl
WHERE table_name = p_table_name
AND column_name = l_column_tab(i);
DELETE FROM fem_tab_column_prop
WHERE table_name =p_table_name
AND column_name = l_column_tab(i)
RETURNING column_property_code BULK COLLECT INTO l_col_prop_code_tab ;
DELETE FROM fem_tab_column_prop
WHERE table_name = p_table_name
AND column_property_code = 'PROCESSING_KEY';
UPDATE fem_tables_b
SET proc_key_index_name = NULL
WHERE table_name = p_table_name;
raise_proc_key_update_event(p_table_name,
l_pk_msg_count,
l_pk_msg_data,
l_pk_return_status);
RAISE l_delete_error;
INSERT INTO fem_tab_columns_vl
(table_name,
column_name,
display_name,
description,
fem_data_type_code,
dimension_id,
uom_column_name,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number
)
SELECT
p_table_name,
dtc.column_name,
NVL(display_name, dtc.column_name) display_name,
NVL(description,dtc.column_name) description,
nvl(fcr.fem_data_type_code,dtc.data_type) as cpm_datatype,
DECODE(fcr.restricted_flag,'N',TO_NUMBER(NULL),fcr.dimension_id) dimension_id,
DECODE(fcr.restricted_flag,'N',TO_NUMBER(NULL),fcr.uom_column_name) uom_column_name,
DECODE(dtc.nullable,'N','Y','Y','N') enabled_flag,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
1
FROM dba_tab_columns dtc,
fem_column_requiremnt_vl fcr
WHERE dtc.table_name = p_table_name
AND dtc.owner = l_owner
AND dtc.column_name = fcr.column_name
AND NOT EXISTS ( SELECT 1
FROM fem_tab_columns_b
WHERE table_name = p_table_name
AND column_name = fcr.column_name );
INSERT INTO fem_tab_columns_vl
(table_name,
column_name,
display_name,
description,
fem_data_type_code,
dimension_id,
uom_column_name,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number
)
SELECT
p_table_name,
dump.column_name,
nvl(display_name,dump.column_name) display_name,
nvl(description,dump.column_name) description,
nvl(ftc.fem_data_type_code,dump.data_type) as cpm_datatype,
ftc.dimension_id,
ftc.uom_column_name,
DECODE(dump.nullable,'N','Y','Y','N') enabled_flag,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
1
FROM fem_tab_columns_vl ftc,
(
SELECT dtc.column_name, dtc.table_name, dtc.nullable, (SELECT table_name tname
FROM fem_tab_columns_b
WHERE column_name = dtc.column_name AND rownum = 1) tname,
data_type
FROM dba_tab_columns dtc
WHERE dtc.table_name = p_table_name
AND dtc.owner = l_owner
AND NOT EXISTS ( SELECT 1
FROM fem_tab_columns_b fcr
WHERE fcr.column_name = dtc.column_name
AND fcr.table_name = p_table_name )) dump
WHERE ftc.column_name = dump.column_name
AND ftc.table_name = dump.tname;
INSERT INTO fem_tab_columns_vl
(table_name,
column_name,
display_name,
description,
fem_data_type_code,
dimension_id,
uom_column_name,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number
)
SELECT
table_name,
column_name,
column_name,
column_name,
data_type,
TO_NUMBER(NULL),
NULL,
DECODE(atc.nullable,'N','Y','Y','N') enabled_flag,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
1
FROM dba_tab_columns atc
WHERE table_name = p_table_name
AND atc.owner = l_owner
AND NOT EXISTS ( SELECT 1
FROM fem_tab_columns_b ftc
WHERE column_name = atc.column_name
AND table_name = p_table_name );
RAISE l_insert_error;
WHEN l_delete_error THEN
x_return_status := c_error;
,p_msg_text => 'synchronize: Trying to delete from fem_tab_columns - ' || p_table_name);
WHEN l_insert_error THEN
x_return_status := c_error;
,p_msg_text => 'synchronize: Trying to insert for' || p_table_name);
SELECT di_view_name
INTO l_di_view_name
FROM fem_tables_vl
WHERE table_name = p_table_name;
DELETE FROM fem_tab_columns_vl
WHERE table_name = p_table_name;
DELETE FROM fem_tables_vl
WHERE table_name = p_table_name;
DELETE FROM fem_tab_column_prop
WHERE table_name = p_table_name;
DELETE FROM fem_table_class_assignmt
WHERE table_name = p_table_name;
DELETE FROM FEM_SVIEW_COLUMNS WHERE view_name = l_di_view_name;
DELETE FROM fem_table_class_assignmt ftca
WHERE EXISTS ( SELECT table_classification_code
FROM fem_tab_class_errors_gt ftce
WHERE ftce.table_classification_code = ftca.table_classification_code
AND table_name = p_table_name
AND ROWNUM = 1 )
AND table_name = p_table_name;
INSERT INTO fem_tab_columns_gt
(table_name,
column_name,
display_name,
description,
data_type,
data_length,
data_precision,
cpm_datatype,
dimension_id,
dimension_name,
uom_column_name,
uom_col_display_name,
selected,
disable_flag,
cpm_switcher,
dim_switcher,
uom_switcher,
enabled_flag,
restricted_flag,
update_flag ,
object_version_number
)
SELECT
dtc.table_name,
dtc.column_name,
NVL(display_name, dtc.column_name) display_name,
NVL(description,dtc.column_name) description,
dtc.data_type,
dtc.data_length,
dtc.data_precision,
nvl(fcr.fem_data_type_code,dtc.data_type) as cpm_datatype,
fcr.dimension_id,
(SELECT fd.dimension_name FROM fem_dimensions_tl fd
WHERE fd.dimension_id = fcr.dimension_id
AND fd.language = USERENV('LANG')
AND rownum = 1) as Dimension_name,
fcr.uom_column_name,
DECODE(uom_column_name,NULL, NULL, (SELECT display_name
FROM fem_tab_columns_tl
WHERE column_name = uom_column_name
AND language = USERENV('LANG')
AND rownum = 1)) as uom_col_display_name,
'Y' selected,
DECODE(dtc.nullable,'N','Y','Y','N') disable_flag,
DECODE(fcr.restricted_flag,'Y','CpmDisabled','CpmDataType') cpm_switcher,
DECODE(fcr.restricted_flag, 'Y',
DECODE(DECODE(fcr.restricted_flag,'N',NULL,fcr.dimension_id),NULL,
DECODE(fcr.fem_data_type_code,'DIMENSION','ronlyDimswitch','disableDimLov' ),
'ronlyDimswitch'),
DECODE(DECODE(fcr.restricted_flag,'N',NULL,fcr.dimension_id),NULL,
DECODE(fcr.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
'enableDimLov')) dim_switcher,
DECODE(fcr.restricted_flag, 'Y',
DECODE(uom_column_name,NULL,
DECODE(fcr.fem_data_type_code,'TERM', 'ronlyUomswitch',
'STATISTIC', 'ronlyUomswitch',
'FREQ', 'ronlyUomswitch', 'disableUomLov' ),
'ronlyUomswitch'),
DECODE(uom_column_name,NULL,
DECODE(fcr.fem_data_type_code,'TERM', 'enableUomLov',
'STATISTIC', 'enableUomLov',
'FREQ', 'enableUomLov', 'disableUomLov' ),
'enableUomLov')) uom_Switcher,
'Y' enabled_flag,
NVL(restricted_flag,'N') restricted_flag,
'N' update_flag,
0 object_version_number
FROM dba_tab_columns dtc,
fem_column_requiremnt_vl fcr
WHERE dtc.table_name = p_table_name
AND dtc.owner = p_owner
AND dtc.column_name = fcr.column_name;
INSERT INTO fem_tab_columns_gt
(table_name,
column_name,
display_name,
description,
data_type,
data_length,
data_precision,
cpm_datatype,
dimension_id,
dimension_name,
uom_column_name,
uom_col_display_name,
selected,
disable_flag,
cpm_switcher,
dim_switcher,
uom_switcher,
enabled_flag,
restricted_flag,
update_flag,
object_version_number
)
SELECT
dump.table_name,
dump.column_name,
nvl(display_name,dump.column_name) display_name,
nvl(description,dump.column_name) description,
dump.data_type,
dump.data_length,
dump.data_precision,
nvl(ftc.fem_data_type_code,dump.data_type) as cpm_datatype,
ftc.dimension_id,
DECODE(ftc.dimension_id, NULL, NULL, (SELECT fd.dimension_name
FROM fem_dimensions_tl fd
WHERE fd.dimension_id = ftc.dimension_id
AND fd.language = USERENV('LANG')
AND rownum = 1)) as Dimension_name,
ftc.uom_column_name,
DECODE(uom_column_name,NULL, NULL, (SELECT display_name
FROM fem_tab_columns_tl
WHERE column_name = ftc.uom_column_name
AND language = USERENV('LANG')
AND rownum = 1)) as uom_col_display_name,
'Y' selected,
DECODE(dump.nullable,'N','Y','Y','N') disable_flag,
'CpmDataType' cpm_switcher,
DECODE(ftc.dimension_id,NULL,
DECODE(ftc.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
'enableDimLov') dim_switcher,
DECODE(ftc.uom_column_name,NULL,
DECODE(ftc.fem_data_type_code,'TERM', 'enableUomLov',
'STATISTIC', 'enableUomLov',
'FREQ', 'enableUomLov', 'disableUomLov' ),
'enableUomLov') uom_Switcher,
'Y' enabled_flag,
'N' restricted_flag,
'N' update_flag,
0 object_version_number
FROM fem_tab_columns_vl ftc,
(
SELECT dtc.column_name, dtc.table_name, (SELECT table_name tname
FROM fem_tab_columns_b
WHERE column_name = dtc.column_name AND rownum = 1) tname,
data_type, nullable, data_length, data_precision
FROM dba_tab_columns dtc
WHERE dtc.table_name = p_table_name
AND dtc.owner = p_owner
AND NOT EXISTS ( SELECT 1
FROM fem_tab_columns_gt fcr
WHERE fcr.column_name = dtc.column_name )) dump
WHERE ftc.column_name = dump.column_name
AND ftc.table_name = dump.tname;
INSERT INTO fem_tab_columns_gt
(table_name,
column_name,
display_name,
description,
data_type,
data_length,
data_precision,
cpm_datatype,
dimension_id,
dimension_name,
uom_column_name,
uom_col_display_name,
selected,
disable_flag,
cpm_switcher,
dim_switcher,
uom_switcher,
enabled_flag,
restricted_flag,
update_flag,
object_version_number
)
SELECT
table_name,
column_name,
column_name,
column_name,
data_type,
data_length,
data_precision,
data_type,
TO_NUMBER(NULL),
NULL,
NULL,
NULL,
'Y',
DECODE(nullable,'N','Y','Y','N'),
'CpmDataType',
'disableDimLov',
'disableUomLov',
'Y',
'N',
'N' update_flag,
0 object_version_number
FROM dba_tab_columns atc
WHERE table_name = p_table_name
AND atc.owner = p_owner
AND NOT EXISTS ( SELECT 1
FROM fem_tab_columns_gt ftc
WHERE column_name = atc.column_name );
ELSIF p_mode = 'UPDATE' THEN
BEGIN
IF l_owner is NULL THEN
fem_database_util_pkg.get_table_owner
(x_return_status => l_schema_return_status,
x_msg_count => l_schema_msg_count,
x_msg_data => l_schema_msg_data,
p_syn_name => p_table_name,
x_tab_name => l_schema_tab_name,
x_tab_owner => l_owner
);
,p_msg_text => 'Update mode: Populating columns');
INSERT INTO fem_tab_columns_gt
(table_name,
column_name,
display_name,
description,
data_type,
data_length,
data_precision,
cpm_datatype,
dimension_id,
dimension_name,
uom_column_name,
uom_col_display_name,
selected,
disable_flag,
cpm_switcher,
dim_switcher,
uom_switcher,
enabled_flag,
restricted_flag,
update_flag,
object_version_number
)
SELECT
dtc.table_name,
dtc.column_name,
dtc.display_name,
dtc.description,
dt.data_type,
dt.data_length,
dt.data_precision,
dtc.fem_data_type_code,
dtc.dimension_id,
DECODE(dtc.dimension_id, NULL, NULL, (SELECT fd.dimension_name
FROM fem_dimensions_tl fd
WHERE fd.dimension_id = dtc.dimension_id
AND fd.language = USERENV('LANG')
AND rownum = 1)),
dtc.uom_column_name,
DECODE(dtc.uom_column_name,NULL, NULL, (SELECT display_name
FROM fem_tab_columns_tl
WHERE column_name = dtc.uom_column_name
AND language = USERENV('LANG')
AND rownum = 1)),
dtc.enabled_flag selected,
DECODE(dt.nullable,'N','Y','Y','N') disable_flag,
DECODE(fcr.restricted_flag,'Y','CpmDisabled','CpmDataType') cpm_switcher,
DECODE(fcr.restricted_flag, 'Y',
DECODE(dtc.dimension_id,NULL,
DECODE(dtc.fem_data_type_code,'DIMENSION','ronlyDimswitch','disableDimLov' ),
'ronlyDimswitch'),
DECODE(dtc.dimension_id,NULL,
DECODE(dtc.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
'enableDimLov')) dim_switcher,
DECODE(fcr.restricted_flag, 'Y',
DECODE(dtc.uom_column_name,NULL,
DECODE(dtc.fem_data_type_code,'TERM', 'ronlyUomswitch',
'STATISTIC', 'ronlyUomswitch',
'FREQ', 'ronlyUomswitch', 'disableUomLov' ),
'ronlyUomswitch'),
DECODE(dtc.uom_column_name,NULL,
DECODE(dtc.fem_data_type_code,'TERM', 'enableUomLov',
'STATISTIC', 'enableUomLov',
'FREQ', 'enableUomLov', 'disableUomLov' ),
'enableUomLov')) uom_Switcher,
dtc.enabled_flag,
NVL(restricted_flag,'N') restricted_flag,
'Y' update_flag,
dtc.object_version_number
FROM fem_tab_columns_vl dtc,
fem_column_requiremnt_vl fcr,
dba_tab_columns dt
WHERE dtc.table_name = p_table_name
AND dtc.table_name = dt.table_name
AND dt.column_name = dtc.column_name
AND dtc.column_name = fcr.column_name
AND dt.owner = l_owner;
INSERT INTO fem_tab_columns_gt
(table_name,
column_name,
display_name,
description,
data_type,
data_length,
data_precision,
cpm_datatype,
dimension_id,
dimension_name,
uom_column_name,
uom_col_display_name,
selected,
disable_flag,
cpm_switcher,
dim_switcher,
uom_switcher,
enabled_flag,
restricted_flag,
update_flag,
object_version_number
)
SELECT
dtc.table_name,
dtc.column_name,
dtc.display_name,
dtc.description,
dt.data_type,
dt.data_length,
dt.data_precision,
dtc.fem_data_type_code,
dtc.dimension_id,
DECODE(dtc.dimension_id, NULL, NULL, (SELECT fd.dimension_name
FROM fem_dimensions_tl fd
WHERE fd.dimension_id = dtc.dimension_id
AND fd.language = USERENV('LANG')
AND rownum = 1)),
dtc.uom_column_name,
DECODE(dtc.uom_column_name,NULL, NULL, (SELECT display_name
FROM fem_tab_columns_tl
WHERE column_name = dtc.uom_column_name
AND language = USERENV('LANG')
AND rownum = 1)),
dtc.enabled_flag selected,
DECODE(dt.nullable,'N','Y','Y','N') disable_flag,
'CpmDataType' cpm_switcher,
DECODE(dtc.dimension_id,NULL,
DECODE(dtc.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
'enableDimLov') dim_switcher,
DECODE(dtc.uom_column_name,NULL,
DECODE(dtc.fem_data_type_code,'TERM', 'enableUomLov',
'STATISTIC', 'enableUomLov',
'FREQ', 'enableUomLov', 'disableUomLov' ),
'enableUomLov') uom_Switcher,
dtc.enabled_flag,
'N' restricted_flag,
'Y' update_flag,
dtc.object_version_number
FROM fem_tab_columns_vl dtc,
dba_tab_columns dt
WHERE dt.table_name = p_table_name
AND dt.table_name = dtc.table_name
AND dt.column_name = dtc.column_name
AND dt.owner = l_owner
AND NOT EXISTS ( SELECT 1
FROM fem_tab_columns_gt
WHERE column_name = dtc.column_name );
SELECT COUNT(*)
INTO l_count
FROM fem_tab_columns_b ftc,
fem_tab_columns_gt ftcg
WHERE ftc.table_name = p_table_name
AND ftcg.table_name = ftc.table_name
AND ftc.object_version_number <> ftcg.object_version_number
AND ftc.column_name = ftcg.column_name;
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt
WHERE display_name IS NULL;
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt
WHERE description IS NULL;
SELECT a.column_name,a.display_name
BULK COLLECT INTO l_column_tab, l_display_tab
FROM fem_tab_columns_gt a,fem_tab_columns_gt b
WHERE UPPER(a.display_name)=UPPER(b.display_name)
AND a.column_name <> b.column_name;
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt
WHERE cpm_datatype = 'DIMENSION'
AND dimension_id IS NULL
AND SUBSTR(COLUMN_NAME,1,8) <> 'USER_DIM';
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt
WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
AND uom_column_name IS NULL;
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt
WHERE cpm_datatype NOT IN ('TERM','FREQ','STATISTIC')
AND uom_column_name IS NOT NULL;
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt a
WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
AND uom_column_name IS NOT NULL
AND NOT EXISTS ( SELECT column_name
FROM fem_tab_columns_gt b
WHERE a.uom_column_name = b.column_name );
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt a
WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
AND uom_column_name IS NOT NULL
AND NOT EXISTS ( SELECT column_name
FROM fem_tab_columns_gt b
WHERE a.uom_column_name = b.column_name
AND b.cpm_datatype = 'DIMENSION' );
SELECT column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt a
WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
AND uom_column_name IS NOT NULL
AND uom_column_name in (SELECT uom_column_name
FROM fem_tab_columns_gt b
GROUP BY uom_column_name
HAVING COUNT(uom_column_name)>1);
IF p_mode = 'UPDATE' THEN
SELECT proc_key_index_name
INTO l_index_name
FROM fem_tables_b ftb
WHERE table_name = p_table_name;
SELECT ftcb.column_name
BULK COLLECT INTO l_column_tab
FROM fem_tab_columns_gt ftcb,
fem_tab_column_prop ftcp
WHERE ftcb.table_name = p_table_name
AND ftcb.table_name = ftcp.table_name
AND ftcp.column_property_code = 'PROCESSING_KEY'
AND ftcp.column_name = ftcb.column_name
AND ftcb.enabled_flag = 'N';
,p_msg_text => 'Before insert into _B');
SELECT COUNT(*)
INTO l_count
FROM fem_tab_columns_b ftc,
fem_tab_columns_gt ftcg
WHERE ftc.table_name = p_table_name
AND ftcg.table_name = ftc.table_name
AND ftc.object_version_number <> ftcg.object_version_number
AND ftc.column_name = ftcg.column_name;
WHEN MATCHED THEN UPDATE
SET
ftc.enabled_flag = ftcg.enabled_flag,
ftc.fem_data_type_code = NVL(ftcg.cpm_datatype,'UNDEFINED'),
ftc.dimension_id = ftcg.dimension_id,
ftc.uom_column_name = ftcg.uom_column_name,
ftc.last_updated_by = l_user_id,
ftc.last_update_date = SYSDATE,
ftc.last_update_login = l_login_id,
ftc.object_version_number = NVL(ftc.object_version_number,0) + 1
WHEN NOT MATCHED THEN
INSERT
(
enabled_flag,
interface_column_name,
table_name,
column_name,
fem_data_type_code,
dimension_id,
uom_column_name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number
)
VALUES( ftcg.enabled_flag,
NULL,
ftcg.table_name,
ftcg.column_name,
NVL(ftcg.cpm_datatype,'UNDEFINED'),
ftcg.dimension_id,
ftcg.uom_column_name,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
1
);
,p_msg_text => 'Before insert into _TL');
USING (SELECT tcgt.*,fndl.language_code
FROM fem_tab_columns_gt tcgt,
fnd_languages fndl
WHERE fndl.installed_flag IN ('I','B')) ftcg
ON ( ftc.column_name = ftcg.column_name
AND ftc.table_name = ftcg.table_name
AND ftc.language = ftcg.language_code
AND ftcg.table_name = p_table_name )
WHEN MATCHED THEN UPDATE
SET
ftc.display_name = DECODE(USERENV('LANG'),
ftc.language,ftcg.display_name,
ftc.source_lang,ftcg.display_name,
ftc.display_name),
ftc.description = DECODE(USERENV('LANG'),
ftc.language,ftcg.description,
ftc.source_lang,ftcg.description,
ftc.description),
ftc.last_updated_by = l_user_id,
ftc.last_update_date = SYSDATE,
ftc.last_update_login = l_login_id,
ftc.source_lang = DECODE(USERENV('LANG'),
ftc.language,ftcg.language_code,
ftc.source_lang)
WHEN NOT MATCHED THEN
INSERT
(
language,
table_name,
column_name,
source_lang,
display_name,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES(
ftcg.language_code,
ftcg.table_name,
ftcg.column_name,
USERENV('LANG'),
ftcg.display_name,
ftcg.description,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
);
DELETE FROM fem_tab_columns_gt;
SELECT enabled_flag INTO l_valid_flag
FROM fem_tables_b
WHERE table_name=p_table_name;
SELECT enabled_flag INTO l_valid_flag
FROM fem_tab_columns_b
WHERE table_name=p_table_name
AND column_name = p_column_name;
SELECT DECODE(count(*),0,'N','Y') INTO l_valid_flag
FROM fem_table_class_assignmt
WHERE table_classification_code = p_table_class_code
AND table_name = p_table_name
AND enabled_flag='Y';
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type=c_table_class_lookup_type
AND language=userenv('LANG') ;
SELECT DECODE(count(*),0,'N','Y') INTO l_valid_flag
FROM fem_table_class_assignmt
WHERE table_classification_code IN (SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type=p_table_class_lookup_type
AND language=userenv('LANG'))
AND table_name = p_table_name
AND enabled_flag='Y';
SELECT application_short_name
INTO l_app_short_name
FROM fnd_application
WHERE application_id = p_app_id;
PROCEDURE raise_proc_key_update_event(p_table_name IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_event_name VARCHAR2(240) := 'oracle.apps.fem.admin.prockey.updated';
l_api_name CONSTANT VARCHAR2(30) := 'raise_proc_key_update_event';
l_parameter_list.delete;
SELECT dbms_metadata.get_ddl('VIEW',p_view_name)
INTO l_str
FROM dual;
DELETE fem_tab_info_gt;
DELETE fem_tab_indx_info_gt;
INSERT INTO fem_tab_info_gt
(table_name, owner, db_link)
SELECT table_name,
table_owner,
NULL
FROM user_synonyms
WHERE synonym_name = g_tab_list_tab(k)
UNION
SELECT g_tab_list_tab(k),
g_owner_list_tab(k),
NULL
FROM dual;
INSERT INTO fem_tab_indx_info_gt(table_name,index_name,column_name,column_position)
SELECT aic.table_name,
aic.index_name,
aic.column_name,
aic.column_position
FROM all_ind_columns aic,
all_indexes ai,
all_updatable_columns uuc,
fem_tab_info_gt ftig
WHERE ai.index_name = aic.index_name
AND ai.table_name = aic.table_name
AND ai.uniqueness = 'UNIQUE'
AND ai.index_type = 'NORMAL'
AND ftig.table_name = aic.table_name
AND aic.table_name = uuc.table_name
AND uuc.table_name = ai.table_name
AND uuc.column_name = aic.column_name
AND ftig.owner = aic.index_owner
AND aic.index_owner = uuc.owner
AND uuc.owner = ai.owner
AND uuc.updatable = 'YES'
ORDER BY index_name, column_position;
SELECT decode(object_type,'TABLE','FEM_TABLE','VIEW','FEM_VIEW','SYNONYM','FEM_TABLE')
INTO l_obj_type
FROM all_objects
WHERE owner=l_apps
AND OBJECT_NAME = p_object_name;
SELECT count(*)
INTO i
FROM fem_table_class_assignmt
WHERE table_name = p_object_name
AND TABLE_CLASSIFICATION_CODE = 'DI_READ_ONLY';
SELECT di_view_name INTO x_di_view_name FROM fem_tables_b
WHERE table_name = p_table_name
AND EXISTS (SELECT 1 FROM user_objects WHERE object_name = di_view_name
AND status = 'VALID');
select_list LONG;
UPDATE fem_tables_b set di_view_name = NULL where table_name=p_tab_name;
SELECT table_owner
INTO l_owner
FROM user_synonyms
WHERE synonym_name = p_tab_name;
SELECT ftcv.column_name,
NVL(ftcv.dimension_id, -1),
nullable,
display_name
BULK COLLECT INTO gs_col_name_tab,
gs_dim_id_tab,
gs_null_flag_tab,
gs_disp_name_tab
FROM fem_tab_columns_vl ftcv,
dba_tab_columns dtc
WHERE ftcv.table_name = dtc.table_name
AND ftcv.column_name = dtc.column_name
AND dtc.owner = l_owner
AND ftcv.enabled_flag='Y'
AND (EXISTS
(
SELECT column_name
FROM fem_tab_column_prop
WHERE column_property_code='PROCESSING_KEY'
AND table_name = ftcv.table_name
AND column_name = ftcv.column_name)
OR
(dtc.nullable='N' and ftcv.fem_data_type_code = 'DIMENSION')
OR
(ftcv.fem_data_type_code = 'BALANCE'))
AND dtc.table_name = p_tab_name
ORDER BY NVL(ftcv.dimension_id, -1) asc;
DELETE FROM FEM_SVIEW_COLUMNS WHERE view_name=l_view_name;
select_list := select_list || ',' || l_tab_alias || '.' ||gs_col_name_tab(i)||' '|| l_tmp_string_id;
insert into fem_sview_columns (view_name,tbl_column_name,dimension_id,disp_code_column, disp_name_column) values(l_view_name,l_tmp_string_id,null,l_tmp_string_id,l_tmp_string_id);
IF gs_dim_id_tab(i) <> -1 OR (gs_col_name_tab(i) NOT IN ('LAST_UPDATED_BY_OBJECT_ID',
'LAST_UPDATED_BY_REQUEST_ID',
'CREATED_BY_OBJECT_ID',
'CREATED_BY_REQUEST_ID')
AND gs_dim_id_tab(i) > 0 )
THEN
FOR metadata_rec IN (SELECT member_display_code_col, member_name_col,
member_vl_object_name, member_col, value_set_required_flag
FROM fem_xdim_dimensions
WHERE dimension_id = gs_dim_id_tab(i))
LOOP
j := j + 1;
select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_display_code_col ||' '||l_tmp_string_dc;
select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_name_col ||' '||l_tmp_string_dn ;
UPDATE fem_sview_columns SET dimension_id = gs_dim_id_tab(i), disp_code_column = l_tmp_string_dc, disp_name_column = l_tmp_string_dn
WHERE view_name = l_view_name
AND tbl_column_name = gs_col_name_tab(i);
select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_display_code_col || ' ' || SUBSTR(gs_col_name_tab(i),1,27) || '_DC';
select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_name_col || ' ' || SUBSTR(gs_col_name_tab(i),1,27) || '_DN';
insert into fem_sview_columns (view_name,tbl_column_name, dimension_id, disp_code_column, disp_name_column)
values(l_view_name,gs_col_name_tab(i),gs_dim_id_tab(i),
SUBSTR(gs_col_name_tab(i),1,27) || '_DC',SUBSTR(gs_col_name_tab(i),1,27) || '_DN');
select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_display_code_col;
select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_name_col ;
insert into fem_sview_columns (view_name,tbl_column_name, dimension_id, disp_code_column, disp_name_column)
values(l_view_name,gs_col_name_tab(i),gs_dim_id_tab(i),
metadata_rec.member_display_code_col,metadata_rec.member_name_col);
IF select_list is NOT NULL THEN
select_list := ' SELECT ' || RTRIM(LTRIM(select_list,','),',');
p_msg_text => 'Preparing Select Clause::'||select_list
);
,p_msg_text => 'Select, From and Where clauses are prepared'
);
IF select_list is NOT NULL THEN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || l_view_name||' AS '|| select_list || from_clause || where_clause;
UPDATE fem_tables_b set di_view_name = l_view_name where table_name=p_tab_name;
SELECT table_name
FROM fem_tables_vl ftc
WHERE enabled_flag='Y'
AND di_view_name is null
AND EXISTS(
select 1
FROM user_synonyms
where synonym_name = ftc.table_name);