REM +=======================================================================+
REM |    Copyright (c) 1992 Oracle Corporation, Redwood Shores, CA, USA     |
REM |                         All rights reserved.                          |
REM +=======================================================================+
REM | FILENAME
REM |   WFNLADD.sql
REM | USAGE
REM |   sqlplus <appsuser/pwd> @WFNLADD
REM | NOTES
REM |   Add missing translation rows for WF _TL tables.
REM +=======================================================================+
REM $Header: WFNLADD.sql 26.6 2005/02/02 22:48:42 yohuang ship $


WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

declare

  chr_newline varchar2(1) := '
';

  -- Loop through all installed languages starting with Base
  cursor langcurs is
    select NLS_LANGUAGE
    from WF_LANGUAGES
    where INSTALLED_FLAG = 'Y'
    order by decode(CODE, 'US', 1, 2);

  curlang varchar2(30);
  curpkg varchar2(30);
begin

  for lang in langcurs loop
    curlang := lang.nls_language;

    -- Set new language to default from
    -- Bug 3072370
    -- Remove the use of costly dynamic sql to alter session lang
    -- use dbms_session.set_nls()
    /** 
    curs := dbms_sql.open_cursor;
    sqlbuf := 'ALTER SESSION SET NLS_LANGUAGE = '''||lang.nls_language||'''';
    dbms_sql.parse(curs, sqlbuf, dbms_sql.v7);
    rows := dbms_sql.execute(curs);
    dbms_sql.close_cursor(curs);
    **/

    dbms_session.set_nls('NLS_LANGUAGE',''''||curlang||'''');

    -- Default all tables from current language
    curpkg := 'WF_ITEM_TYPES_PKG';
    wf_item_types_pkg.add_language;
    commit;

    curpkg := 'WF_ITEM_ATTRIBUTES_PKG';
    wf_item_attributes_pkg.add_language;
    commit;

    curpkg := 'WF_LOOKUP_TYPES_PKG';
    wf_lookup_types_pkg.add_language;
    commit;

    curpkg := 'WF_LOOKUPS_PKG';
    wf_lookups_pkg.add_language;
    commit;

    curpkg := 'WF_ACTIVITIES_PKG';
    wf_activities_pkg.add_language;
    commit;

    curpkg := 'WF_ACTIVITY_ATTRIBUTES_PKG';
    wf_activity_attributes_pkg.add_language;
    commit;

    curpkg := 'WF_MESSAGES_PKG';
    wf_messages_pkg.add_language;
    commit;

    curpkg := 'WF_MESSAGE_ATTRIBUTES_PKG';
    wf_message_attributes_pkg.add_language;
    commit;

    curpkg := 'WF_RESOURCES_PKG';
    wf_resources_pkg.add_language;
    commit;

    curpkg := 'WF_EVENTS_PKG';
    wf_events_pkg.add_language;
    commit;
    
    curpkg := 'FND_SVC_COMP_TYPES_PKG';
    fnd_svc_comp_types_pkg.add_language;
    commit;

    curpkg := 'FND_SVC_COMP_PARAMS_PKG';
    fnd_svc_comp_params_pkg.add_language;
    commit; 

  end loop;

exception 
when others then
  raise_application_error(-20001,
           'LANGUAGE='||curlang||chr_newline||
           'PACKAGE='||curpkg||chr_newline||
           'SQLERRM='||sqlerrm);
end;
/

commit;
exit;