/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2004 Jorg Janke, ComPiere, Inc.
 * Copyright (C)      2004 Marco LOMBARDO.
 * All Rights Reserved.
 *************************************************************************/
-- Remove Region from a Country.
-- Problems could araise if you already create Location that use the Country and the Region created.
-- You can avoid this with the following command (the example is for Italy):
--
--   update C_Location
--     set C_Region_ID = null,
--         C_City_ID = null
--     where C_Country_ID = (select C_Country_ID from C_Country where Name = 'Italy')
--         and C_Region_ID is not null;
--
-- But I don't want to code this in the script because some information will be lost.
-- You can run it manually if you want.

drop table NN_Cap;

-- Here you have some setup. See LoadCap.sql.

define NN_Country=Italy
define NN_Client=System
set serveroutput on

-- You would not need to modify below this line for do it in Italy. ------------------------

declare
  v_ClientName		nvarchar2(60) := '&NN_Client';
  v_CountryName		nvarchar2(60) := '&NN_Country';
  v_AD_Client_ID	number(10);
  v_C_Country_ID	number(10);
begin
  -- Get info.
  select AD_Client_ID into v_AD_Client_ID
    from AD_Client
    where Name = v_ClientName;
  select C_Country_ID into v_C_Country_ID
    from C_Country
    where Name = v_CountryName;
  -- Delete City.
  dbms_output.put('Delete City... ');
  delete from C_City
    where C_Country_ID = v_C_Country_ID
    and AD_Client_ID = v_AD_Client_ID;
  dbms_output.put_line(SQL%RowCount||' records deleted.');
  commit;
  -- Delete Region.
  dbms_output.put('Delete Region... ');
  delete from C_Region
    where C_Country_ID = v_C_Country_ID
    and AD_Client_ID = v_AD_Client_ID;
  dbms_output.put_line(SQL%RowCount||' records deleted.');
  commit;
  -- Update Country.
  dbms_output.put('Update Country Italy... ');
  -- We want Italy as default.
  update C_Country
    set HasRegion = 'N',
	RegionName = null,
	DisplaySequence = '@C@, @P@',
	IsDefault = 'N',
	updated = sysdate,
	updatedby = 0
  where C_Country_ID = v_C_Country_ID;
  dbms_output.put_line(SQL%RowCount||' records updated.');
  dbms_output.put('Update Country USA... ');
  update C_Country
    set IsDefault = 'Y',
	updated = sysdate,
	updatedby = 0
  where Name = 'United States';		-- Reset to the default.
  dbms_output.put_line(SQL%RowCount||' records updated.');
  commit;
exception
  when OTHERS then
  dbms_output.put_line('error!!!');
  rollback;
  dbms_output.put_line('Last part of transaction rolled back.');
end;
/

-- Marco LOMBARDO, Lucca, Italy, 2004-03-04.
-- marco.lombardo@enneenne.com