The following lines contain the word 'select', 'insert', 'update' or 'delete':
to delete KFF registration info for
composite dimensions
Rob Flippo 14-SEP-2006 Bug#5520316 Refresh engine should continue on
even if get exception to KFF delete; Also
SELECT request_id
FROM fnd_concurrent_requests R,
fnd_concurrent_programs P
WHERE parent_request_id = v_parent_request_id
AND R.concurrent_program_id = P.concurrent_program_id
AND P.concurrent_program_name = c_FNDLOAD
ORDER BY request_id;
SELECT R.file_name, ALDT.subdir file_directory_path, R.file_product_prefix,
R.lctfile_name, ALCT.subdir lctfile_directory_path, R.lctfile_product_prefix,
substr(ALDT.subdir,instr(ALDT.subdir,'/',-1,1)+1,(length(ALDT.subdir) - instr(ALDT.subdir,'/',-1,1))+1) FILE_LANG,
DECODE(substr(ALDT.subdir,instr(ALDT.subdir,'/',-1,1)+1,(length(ALDT.subdir) - instr(ALDT.subdir,'/',-1,1))+1),'US','1',substr(ALDT.subdir,instr(ALDT.subdir,'/',-1,1)+1,(length(ALDT.subdir) - instr(ALDT.subdir,'/',-1,1))+1)) ORDER_SEQ
FROM fem_rfsh_files R,
(SELECT distinct filename, subdir
FROM ad_files) ALDT,
(SELECT distinct filename, subdir
FROM ad_files
WHERE substr(subdir,1,5) = 'patch') ALCT
WHERE R.file_type = 'LDT'
AND UPPER(R.file_name) = UPPER(ALDT.filename)
AND UPPER(R.lctfile_name) = UPPER(ALCT.filename)
ORDER BY R.sub_phase, ORDER_SEQ, R.file_name;
SELECT distinct R.process_name, R.procedure_call, R.sub_phase
FROM fem_rfsh_procedures R, user_procedures U
WHERE R.package_name = U.object_name
AND R.procedure_name = U.procedure_name
ORDER BY sub_phase asc;
SELECT language_code
INTO v_base_lang
FROM fnd_languages
WHERE installed_flag = 'B';
SELECT userenv('LANG')
INTO v_base_lang
FROM dual;
Putting this logic to get the language in the SELECT
so we can sort by lang
-- identify the language_code for the ldt file
-- we will only run ldt files of the base language
v_subdir_length := length(ldtfile.file_directory_path);
| Rob Flippo 16-MAY-06 bug#5223789 Add call to delete KFF reg info for
| composite dimensions
| Rob Flippo 14-SEP-06 Bug#5520316 Continue on if get KFF delete failure;
SELECT DISTINCT A1.application_short_name app_short_name
,A1.application_id
FROM ( SELECT table_owner_application_id
FROM fem_rfsh_tables
UNION
SELECT table_owner_application_id
FROM fem_tables_b) R1,
fnd_application A1
WHERE R1.table_owner_application_id = A1.application_id;
SELECT table_name FROM
(SELECT R.table_name
FROM fem_rfsh_tables R
WHERE table_owner_application_id = p_app_id
AND EXISTS
(SELECT table_name FROM ALL_TABLES A2
WHERE A2.table_name = R.table_name
AND A2.owner = p_schema)
UNION
SELECT T.table_name
FROM fem_tables_b T
WHERE T.table_owner_application_id = p_app_id
AND T.table_name not in (select table_name
FROM fem_rfsh_tables R)) RT
WHERE EXISTS
(SELECT table_name FROM ALL_TABLES A1
WHERE A1.table_name = RT.table_name
AND A1.owner = p_schema);
e_composite_delete EXCEPTION;
fem_setup_pkg.delete_flexfield
(p_api_version => 1.0,p_init_msg_list => NULL
,p_commit => FND_API.G_TRUE
,p_encoded => NULL
,x_return_status=>v_return_status
,x_msg_count=>v_msg_count
,x_msg_data=>v_msg_data
,p_dimension_varchar_label => 'ACTIVITY');
raise e_composite_delete;
fem_setup_pkg.delete_flexfield
(p_api_version => 1.0,p_init_msg_list => NULL
,p_commit => FND_API.G_TRUE
,p_encoded => NULL
,x_return_status=>v_return_status
,x_msg_count=>v_msg_count
,x_msg_data=>v_msg_data
,p_dimension_varchar_label => 'COST_OBJECT');
raise e_composite_delete;
WHEN e_composite_delete THEN
FEM_ENGINES_PKG.USER_MESSAGE
(P_APP_NAME => c_fem
,P_MSG_NAME => 'FEM_RFSH_COMPOSITE_DELETE');
p_msg_text => 'Failed to delete composite dimension registration');
SELECT application_id
FROM fem_applications;
SELECT user_id
FROM fnd_user;
SELECT ledger_id
INTO v_ledger_id
FROM fem_ledgers_b
WHERE ledger_display_code = 'DEFAULT_LEDGER';
SELECT dataset_code
INTO v_dataset_cd
FROM fem_datasets_b
WHERE dataset_display_code = 'Default';
SELECT A.application_id, V.application_short_name, V.application_name
FROM fem_applications A, fnd_application_vl V
WHERE A.application_id = V.application_id;
SELECT count(*)
INTO v_table_count
FROM fem_rfsh_tables R, all_tables A
WHERE R.table_name = A.table_name
AND R.table_owner_application_id = v_app_id
AND A.owner = v_target_schema;
P_LAST_UPDATE_LOGIN => v_login_id,
P_PROGRAM_ID => v_pgm_id,
P_PROGRAM_LOGIN_ID => v_login_id,
P_PROGRAM_APPLICATION_ID => v_pgm_app_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_API_return_status);
P_LAST_UPDATE_LOGIN => v_login_id,
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 => v_login_id,
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 => v_request_id,
P_OBJECT_ID => p_object_id,
P_EXEC_STATUS_CODE => p_execution_status,
P_USER_ID => v_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 => v_request_id,
P_EXEC_STATUS_CODE => p_execution_status,
P_USER_ID => v_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);