REM
REM     Created from Entity NX_VERSIONS by SCHENG on 07-FEB-95
REM
REM PROMPT 
REM PROMPT Upgrade Table NX_VERSIONS
ALTER TABLE nx_versions add ( string_status NUMBER(38));

REM
REM Update string count for base modules to speed up query
REM
PROMPT 
PROMPT Updating String Count for Base Modules
Update NX_Versions v1
Set  String_Status  = (
       Select count(*)
       From nx_nodes
       Where  Node_Type = 'S'
       And    Tree_id   = v1.Tree_Id )
Where  (v1.Tree_id, v1.Language_code) in
       (select v2.tree_id, v2.language_code 
        from   nx_versions v2, nx_projects p
        where  v2.project_name = p.project_name
        and    v2.language_code = p.language_code
        and    p.origin = 'B')
And     String_status is null
;

REM
REM Update string status for translation modules to speed up query
REM
PROMPT 
PROMPT Updating String Status for Translation Modules
Update NX_Versions v1
Set  String_Status = (
       Select nvl(round((sum(decode(status,'TDR',1,'ONT',1,'RDN',1,0)) /
              count(*))*100,0),0)
       From nx_text_strings t, nx_nodes n
       Where t.node_id       = n.node_id
       And   t.language_code = v1.Language_Code
       And   n.tree_id       = v1.Tree_Id
       And   n.node_type     = 'S')
Where  (v1.Tree_id, v1.Language_code) in
       (select v2.tree_id, v2.language_code 
        from   nx_versions v2, nx_projects p
        where  v2.project_name = p.project_name
        and    v2.language_code = p.language_code
        and    p.origin = 'T')
And     String_status is null
;

