azertyuiop456

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>

	<!-- = = = = = = = = = = = = = = = = = = = -->
	<!-- = Load external properties					= -->
	<!-- = = = = = = = = = = = = = = = = = = = -->
	<properties><include href="etl.properties"/></properties>

	<!-- = = = = = = = = = = = = = = = = = = = -->
	<!-- = Connections											 = -->
	<!-- = = = = = = = = = = = = = = = = = = = -->
	<connection id='log' driver='${driver_log}'/>
	<connection id='src' driver='${driver_src}' url='${url_src}' user='${user_src}' password='${pass_src}' classpath='lib/ojdbc6.jar'/>
	<connection id='ods' driver='${driver_ods}' url='${url_ods}' user='${user_ods}' password='${pass_ods}' classpath='lib/ojdbc6.jar'/>
	<connection id='dwh' driver='${driver_dwh}' url='${url_dwh}' user='${user_dwh}' password='${pass_dwh}' classpath='lib/ojdbc6.jar'/>

	<!-- = = = = = = = = = = = = = = = = = = = -->
	<!-- Job definition											= -->
	<!-- = = = = = = = = = = = = = = = = = = = -->
	<script connection-id='ods'>
		<onerror codes="955" retry='true'>
			DROP TABLE ODS_Person;
		</onerror>
		CREATE TABLE ODS_Person(
			BusinessEntityID			int,
			PersonType						char(2),
			NameStyle							varchar2(64),
			Title									varchar2(8),
			FirstName							varchar2(64),
			MiddleName						varchar2(64),
			LastName							varchar2(64),
			Suffix								varchar2(10),
			EmailPromotion				int,
			rowguid								varchar2(64),
			ModifiedDate					timestamp,
			ActionFlag			char(1)
		);
	</script>
	<query connection-id="src">
		SELECT
			BusinessEntityID,
			PersonType,
			NameStyle,
			Title,
			FirstName,
			MiddleName,
			LastName,
			Suffix,
			EmailPromotion,
			rowguid,
			ModifiedDate
		FROM SRC_Person;
		<script connection-id="ods">
			INSERT INTO ODS_Person
			VALUES (
				?1,
				?2,
				?3,
				?4,
				?5,
				?6,
				?7,
				?8,
				?9,
				?10,
				?11,
				'-'
			);
		</script>
	</query>
	<query connection-id='ods'>
		SELECT count(1) AS result FROM ODS_Person;
		<script connection-id='log'>
			[ODS_Person] Loaded: ${result}
		</script>
	</query>

	<!-- = = = = = = = = = = = = = = = = = = = -->
	<!-- Job definition											= -->
	<!-- = = = = = = = = = = = = = = = = = = = -->
	<script connection-id='dwh'>
		<onerror codes="955">
		</onerror>
		CREATE TABLE DWH_Person(
			BusinessEntityID			int,
			PersonType						char(2),
			NameStyle							varchar2(64),
			Title									varchar2(8),
			FirstName							varchar2(64),
			MiddleName						varchar2(64),
			LastName							varchar2(64),
			Suffix								varchar2(10),
			EmailPromotion				int,
			rowguid								varchar2(64),
			ModifiedDate					timestamp,
			SCD_Version			int,
			SCD_Flag			int,
			SCD_DateDebut			date,
			SCD_DateFin			date
		);
	</script>
	<script connection-id="ods">
		UPDATE ODS_Person SET ActionFlag='I';
	</script>
	<query connection-id="dwh">
		SELECT
                        BusinessEntityID,
                        PersonType,
                        NameStyle,
                        Title,
                        FirstName,
                        MiddleName,
                        LastName,
                        Suffix,
                        EmailPromotion,
                        rowguid,
                        ModifiedDate
		FROM DWH_Person;
		<script connection-id="ods">
			UPDATE ODS_Person
			SET ActionFlag='-'
			WHERE BusinessEntityID=?1;
		</script>
		<script connection-id="ods">
			UPDATE ODS_Person
			SET ActionFlag='U'
			WHERE BusinessEntityID=?1 AND
			(
				PersonType != ?2
				OR NameStyle!=?3
				OR Title!=?4
				OR FirstName!=?5
				OR MiddleName!=?6
				OR LastName!=?7
				OR Suffix!=?8
				OR EmailPromotion!=?9
				OR rowguid!=?10
				OR ModifiedDate!=?11
			)
		</script>
	</query>

	<!-- = = = = = = = = = = = = = = = = = = = -->
	<!-- Job definition											= -->
	<!-- = = = = = = = = = = = = = = = = = = = -->
        <query connection-id="ods">
                SELECT
                        BusinessEntityID,
                        PersonType,
                        NameStyle,
                        Title,
                        FirstName,
                        MiddleName,
                        LastName,
                        Suffix,
                        EmailPromotion,
                        rowguid,
                        ModifiedDate
                FROM ODS_Person
		WHERE ActionFlag='I';
                <script connection-id="dwh">
                        INSERT INTO DWH_Person
                        VALUES (
                                ?1,
                                ?2,
                                ?3,
                                ?4,
                                ?5,
                                ?6,
                                ?7,
                                ?8,
                                ?9,
                                ?10,
                                ?11,
                                1,
				1,
				TO_DATE('0001-01-01','YYYY-MM-DD'),
				TO_DATE('9999-12-31','YYYY-MM-DD')
                        );
                </script>
        </query>
	<query connection-id='ods'>
		SELECT count(1) AS result FROM ODS_Person WHERE ActionFlag='I';
		<script connection-id='log'>
			[DWH_Person] Inserted: ${result}
		</script>
	</query>


	<query connection-id='ods'>
		SELECT count(1) AS result FROM ODS_Person WHERE ActionFlag='U';
		<script connection-id='log'>
			[DWH_Person] Updated: ${result}
		</script>
	</query>
	<!--
	<query connection-id='ods'>
		SELECT count(1) AS result FROM ODS_Person WHERE ActionFlag='D';
		<script connection-id='log'>
			[DWH_Person] Deleted: ${result}
		</script>
	</query>
	-->
	<query connection-id='dwh'>
		SELECT count(1) AS result FROM DWH_Person;
		<script connection-id='log'>
			[DWH_Person] Result: ${result}
		</script>
	</query>
</etl>