/*************************************************************************
 * 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.
 *************************************************************************/
-- Load the CAP (Italian ZIP Code) into Compiere.

-- First create the table to import data: this can vary with respect
-- to your Country and your external file to load. One record of my external file is below.
-- 55100 TOS LU 0583 Lucca

create table NN_Cap(
  CAP		char(5),
  Regione	char(3),
  Provincia	char(2),
  AreaCode	char(4),
  Name		varchar2(60)
);

-- Do the import with SQL*Loader.
host sqlldr compiere250d/compiere250d control=zip.ctl log=zip.log bad=zip.bad discard=zip.dis direct=Y

-- Here you have some setup. Both parameters are case sensitive.
-- NN_Country is the Country you want to modify.
-- NN_Client is the Client you want to work with: you can choose a particular Client or choose System.
--           In this case loaded records are available for every Client present in your installation.

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);
  v_RegionName		nvarchar2(60) := 'Provincia';
  v_Count		number := 0;
  v_C_Region_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;
  -- Update Country.
  dbms_output.put('Update Country... ');
  -- We want Italy as default.
  Update C_Country
    set IsDefault = 'N'
    where IsDefault = 'Y';
  update C_Country
    set HasRegion = 'Y',
	RegionName = v_RegionName,
	DisplaySequence = '@C@, @R@ @P@',
	IsDefault = 'Y',
	updated = sysdate,
	updatedby = 0
  where C_Country_ID = v_C_Country_ID;
  dbms_output.put_line(SQL%RowCount||' records updated.');
  commit;
  -- Insert Region: in Italy the main city of a Region (Provincia) has a ZIP code (CAP) ending with 100.
  -- This is why I/we can take information about Region on a ZIP Code list... well... more or less.
  dbms_output.put('Insert Region... ');
  insert into C_Region(C_REGION_ID, AD_CLIENT_ID, AD_ORG_ID,
			ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
			NAME, DESCRIPTION, C_COUNTRY_ID, ISDEFAULT)
    select AD_Sequence_NextNo('C_Region'), v_AD_Client_ID, 0,
			'Y', sysdate, 0, sysdate, 0,
			Provincia, Name||', '||Regione||'.', v_C_Country_ID, 'N'
    from NN_Cap
    where CAP like '__100' or Name in ('Biella', 'Crotone', 'Gorizia', 'Isernia', 'Lecco', 'Lodi', 'Pordenone', 'Oristano',
					'Rimini', 'Repubblica di San Marino', 'Verbania', 'Vibo Valentia')
	or (Name = 'Prato' and Provincia = 'PO');
  dbms_output.put_line(SQL%RowCount||' records inserted.');
  commit;
  -- Insert all the records in City.
  dbms_output.put('Insert City... ');
  v_Count := 0;
  for r_r in (select * from C_Region where C_Country_ID = v_C_Country_ID) loop
    insert into C_City(C_CITY_ID, AD_CLIENT_ID, AD_ORG_ID,
			ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
			NAME, POSTAL, AREACODE, C_COUNTRY_ID, C_Region_ID)
      select AD_Sequence_NextNo('C_City'), v_AD_Client_ID, 0,
			'Y', sysdate, 0, sysdate, 0,
			Name, CAP, AreaCode, v_C_Country_ID, r_r.C_Region_ID
      from NN_Cap
      where Provincia = r_r.Name;
    v_Count := v_Count + SQL%RowCount;
  end loop;
  dbms_output.put_line(v_Count||' records inserted.');	-- This should be equal to the number of rows into NN_Cap if nothing is missing.
  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