DECLARE
CURSOR get_lookup_details
IS
SELECT ltype.application_id,
ltype.customization_level,
ltype.creation_date,
ltype.created_by,
ltype.last_update_date,
ltype.last_updated_by,
ltype.last_update_login,
tl.lookup_type,
tl.security_group_id,
tl.view_application_id,
tl.description,
tl.meaning
FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
WHERE ltype.lookup_type = 'XX POSITIONS'
AND ltype.lookup_type = tl.lookup_type
AND language = 'US';
CURSOR get_value
IS
SELECT DISTINCT
hl.lookup_code, pos.position_name_arabic, position_name_english
FROM xxhr_position_t pos, hr_lookups hl
WHERE hl.meaning = pos.position_name_english
AND hl.lookup_type = 'XX POSITIONS'
-- AND lookup_code= '321'
AND lkp_process_flag_ar = 'N';
l_err_msg VARCHAR2 (1000) := NULL;
l_db_nls_language VARCHAR2 (500) := NULL;
BEGIN
FOR i IN get_lookup_details
LOOP
FOR j IN get_value
LOOP
-- Set NLS_LANG to Arabic to insert Arabic Values
fnd_global.set_nls (
p_nls_language => 'ARABIC',
p_nls_date_format => NULL,
p_nls_date_language => NULL,
p_nls_numeric_characters => NULL,
p_nls_sort => NULL,
p_nls_territory => NULL,
p_db_nls_language => l_db_nls_language,
p_db_nls_date_format => l_db_nls_language,
p_db_nls_date_language => l_db_nls_language,
p_db_nls_numeric_characters => l_db_nls_language,
p_db_nls_sort => l_db_nls_language,
p_db_nls_territory => l_db_nls_language,
p_db_nls_charset => l_db_nls_language);
l_err_msg := NULL;
dbms_output.put_line('l_db_nls_language: ' || l_db_nls_language);
BEGIN
fnd_lookup_values_pkg.translate_row (
x_lookup_type => i.lookup_type,
x_security_group_id => i.security_group_id,
x_view_application_id => i.view_application_id,
x_owner => 49431, --> User ID
x_meaning => j.position_name_arabic,
x_description => j.position_name_arabic,
x_lookup_code => j.lookup_code);
UPDATE xxhr_position_t --> Stagging Table to Log Status
SET lkp_process_flag_ar = 'Y', lkp_err_msg = NULL
WHERE UPPER (position_name_english) =
UPPER (j.position_name_english);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
UPDATE xxhr_position_t
SET lkp_process_flag_ar = 'N', lkp_err_msg = l_err_msg
WHERE UPPER (position_name_english) =
UPPER (j.position_name_english);
COMMIT;
END;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
ليست هناك تعليقات:
إرسال تعليق