[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(row_id IN OUT NOCOPY VARCHAR2,
data_type_id NUMBER,
data_type_code VARCHAR2,
enforce_balancing_flag VARCHAR2,
apply_elim_rules_flag VARCHAR2,
apply_cons_rules_flag VARCHAR2,
source_dataset_code NUMBER,
data_type_name VARCHAR2,
description VARCHAR2,
creation_date DATE,
created_by NUMBER,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
object_version_number NUMBER) IS
CURSOR datatypes_row IS
SELECT rowid
FROM GCS_DATA_TYPE_CODES_B cb
WHERE cb.data_type_id = insert_row.data_type_id;
INSERT INTO GCS_DATA_TYPE_CODES_B
(data_type_id,
data_type_code,
enforce_balancing_flag,
apply_elim_rules_flag,
apply_cons_rules_flag,
source_dataset_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number)
SELECT insert_row.data_type_id,
insert_row.data_type_code,
insert_row.enforce_balancing_flag,
insert_row.apply_elim_rules_flag,
insert_row.apply_cons_rules_flag,
insert_row.source_dataset_code,
insert_row.creation_date,
insert_row.created_by,
insert_row.last_update_date,
insert_row.last_updated_by,
insert_row.last_update_login,
insert_row.object_version_number
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM GCS_DATA_TYPE_CODES_b cb
WHERE cb.data_type_id = insert_row.data_type_id);
INSERT INTO GCS_DATA_TYPE_CODES_tl
(data_type_id,
language,
source_lang,
data_type_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
-- Bugfix 5353211 : Qualify API variables with the API name, so that the values passed to the API are utilized
SELECT insert_row.data_type_id,
L.LANGUAGE_CODE,
userenv('LANG'),
insert_row.data_type_name,
insert_row.description,
insert_row.last_update_date,
insert_row.last_updated_by,
insert_row.last_update_login,
insert_row.creation_date,
insert_row.created_by
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
(SELECT 1
FROM GCS_DATA_TYPE_CODES_tl ctl
WHERE ctl.data_type_id = insert_row.data_type_id
AND ctl.LANGUAGE = L.LANGUAGE_CODE);
END Insert_Row;
PROCEDURE Update_Row(row_id IN OUT NOCOPY VARCHAR2,
data_type_id NUMBER,
data_type_code VARCHAR2,
enforce_balancing_flag VARCHAR2,
apply_elim_rules_flag VARCHAR2,
apply_cons_rules_flag VARCHAR2,
source_dataset_code NUMBER,
data_type_name VARCHAR2,
description VARCHAR2,
creation_date DATE,
created_by NUMBER,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
object_version_number NUMBER) IS
BEGIN
UPDATE GCS_DATA_TYPE_CODES_b cb
SET data_type_id = update_row.data_type_id,
data_type_code = update_row.data_type_code,
enforce_balancing_flag = update_row.enforce_balancing_flag,
apply_elim_rules_flag = update_row.apply_elim_rules_flag,
apply_cons_rules_flag = update_row.apply_cons_rules_flag,
source_dataset_code = update_row.source_dataset_code,
last_update_date = update_row.last_update_date,
last_updated_by = update_row.last_updated_by,
last_update_login = update_row.last_update_login,
object_version_number = update_row.object_version_number
WHERE cb.data_type_id = update_row.data_type_id;
INSERT INTO GCS_DATA_TYPE_CODES_tl
(data_type_id,
language,
source_lang,
data_type_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
-- Bugfix 5353211 : Qualify API variables with the API name, so that the values passed to the API are utilized
SELECT update_row.data_type_id,
L.LANGUAGE_CODE,
userenv('LANG'),
update_row.data_type_name,
update_row.description,
update_row.last_update_date,
update_row.last_updated_by,
update_row.last_update_login,
update_row.creation_date,
update_row.created_by
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
(SELECT 1
FROM GCS_DATA_TYPE_CODES_tl ctl
WHERE ctl.data_type_id = update_row.data_type_id
AND ctl.LANGUAGE = L.LANGUAGE_CODE);
UPDATE GCS_DATA_TYPE_CODES_tl ctl
SET data_type_name = update_row.data_type_name,
description = update_row.description,
last_update_date = update_row.last_update_date,
last_updated_by = update_row.last_updated_by,
last_update_login = update_row.last_update_login
WHERE ctl.data_type_id = update_row.data_type_id
AND ctl.language = userenv('LANG');
END Update_Row;
last_update_date VARCHAR2,
custom_mode VARCHAR2,
data_type_code VARCHAR2,
enforce_balancing_flag VARCHAR2,
apply_elim_rules_flag VARCHAR2,
apply_cons_rules_flag VARCHAR2,
source_dataset_display_code VARCHAR2,
object_version_number NUMBER,
data_type_name VARCHAR2,
description VARCHAR2) IS
row_id VARCHAR2(64);
f_ludate DATE; -- datatype update date in file
db_ludate DATE; -- datatype update date in db
f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT dataset_code
INTO source_dataset_code
FROM fem_datasets_b fdb
WHERE dataset_display_code = load_row.source_dataset_display_code ;
SELECT cb.last_updated_by, cb.last_update_date
INTO db_luby, db_ludate
FROM GCS_DATA_TYPE_CODES_B cb
WHERE cb.data_type_id = load_row.data_type_id;
update_row(row_id => row_id,
data_type_id => load_row.data_type_id,
data_type_code => load_row.data_type_code,
enforce_balancing_flag => load_row.enforce_balancing_flag,
apply_elim_rules_flag => load_row.apply_elim_rules_flag,
apply_cons_rules_flag => load_row.apply_cons_rules_flag,
source_dataset_code => source_dataset_code,
data_type_name => load_row.data_type_name,
description => load_row.description,
creation_date => f_ludate,
created_by => f_luby,
last_update_date => f_ludate,
last_updated_by => f_luby,
last_update_login => 0,
object_version_number => load_row.object_version_number);
insert_row(row_id => row_id,
data_type_id => load_row.data_type_id,
data_type_code => load_row.data_type_code,
enforce_balancing_flag => load_row.enforce_balancing_flag,
apply_elim_rules_flag => load_row.apply_elim_rules_flag,
apply_cons_rules_flag => load_row.apply_cons_rules_flag,
source_dataset_code => source_dataset_code,
data_type_name => load_row.data_type_name,
description => load_row.description,
creation_date => f_ludate,
created_by => f_luby,
last_update_date => f_ludate,
last_updated_by => f_luby,
last_update_login => 0,
object_version_number => load_row.object_version_number);
last_update_date VARCHAR2,
custom_mode VARCHAR2,
data_type_name VARCHAR2,
description VARCHAR2) IS
f_luby NUMBER; -- category owner in file
f_ludate DATE; -- category update date in file
db_ludate DATE; -- category update date in db
f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT ctl.last_updated_by, ctl.last_update_date
INTO db_luby, db_ludate
FROM GCS_DATA_TYPE_CODES_TL ctl
WHERE ctl.data_type_id = translate_row.data_type_id
AND ctl.language = userenv('LANG');
UPDATE gcs_data_type_codes_tl ctl
SET data_type_name = translate_row.data_type_name,
description = translate_row.description,
source_lang = userenv('LANG'),
last_update_date = f_ludate,
last_updated_by = f_luby,
last_update_login = 0
WHERE ctl.data_type_id = translate_row.data_type_id
AND userenv('LANG') IN (ctl.language, ctl.source_lang);
INSERT /*+ append parallel(tt) */
INTO GCS_DATA_TYPE_CODES_TL tt
(DATA_TYPE_ID,
DATA_TYPE_NAME,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG)
SELECT /*+ parallel(v) parallel(t) use_nl(t) */
v.*
FROM (SELECT /*+ no_merge ordered parellel(b) */
B.DATA_TYPE_ID,
B.DATA_TYPE_NAME,
B.DESCRIPTION,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM GCS_DATA_TYPE_CODES_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND B.LANGUAGE = userenv('LANG')) v,
GCS_DATA_TYPE_CODES_TL t
WHERE T.DATA_TYPE_ID(+) = v.data_type_id
AND T.LANGUAGE(+) = v.language_code
AND T.DATA_TYPE_ID IS NULL;
SELECT data_type_id,
data_type_code,
enforce_balancing_flag,
apply_elim_rules_flag,
apply_cons_rules_flag,
source_dataset_code BULK COLLECT
INTO g_datatype_info
FROM gcs_data_type_codes_b;