Generated documentation

	Job Documentation
	Generated by Talend Open Studio

PROJECT NAME	Proyecto Ventas BI	GENERATION DATE	09-ene-2010 19:25:17
AUTHOR	respinosa@terramiticapark.com	T.O.S VERSION	4.0.0.M1_r33789

Summary

Project Description

Job Description

Job Preview Picture

Job Settings

Context List

Component List

Components Description

Project Description

Properties	Values
Name	Proyecto Ventas BI
Language	java
Description	Proyecto Ventas EnoBI

Job Description

Properties	Values
Name	DimProducto
Author	respinosa@terramiticapark.com
Version	0.1
Purpose	ETL Dimension Producto
Status	DEV
Description	Proceso para llenado de la dimensión Producto (tabla DWD_PRODUCTO) desde el sistema Sap
Creation	27-may-2009 7:20:14
Modification	09-ene-2010 19:14:18

Job Preview Picture

Job settings

Extra settings

Name	Value
COMP_DEFAULT_FILE_DIR	C:/TOS-Win32-r33789-V4.0.0M1/workspace
Multi thread execution	false
Implicit tContextLoad	false

Stats & Logs

Name	Value
Use statistics (tStatCatcher)	true
Use logs (tLogCatcher)	true
Use volumetrics (tFlowMeterCatcher)	true
On Console	false
On Files	true
File Path	"C:/temp/talend"
Log File Name	"logs_file.txt"
Meter file name	"meter_file.txt"
Stats File Name	"stats_file.txt"
On Databases	true
Property Type
Propiedad	REPOSITORY
Property:source	stage_area
Db Type	tMysqlOutput
Host	context.stage_area_Server
Port	context.stage_area_Port
Db Name	context.stage_area_Database
Additional parameters	context.stage_area_AdditionalParams
Schema	""
Usuario	context.stage_area_Login
Password	******
Base de Datos	""
Logs Table	"Logs"
Meter Table	"Meter"
Stats Table	"Stats"
Catch components statistics	true
Catch runtime errors	true
Catch user errors	true
Catch user warnings	true

Context List

Context : Default

Name	Prompt	Need Prompt?	Type	Value	Source
tm_int_Sid	tm_int_Sid?	false	id_String	MG1	tm_int
tm_int_Login	tm_int_Login?	false	id_String	system	tm_int
tm_int_Port	tm_int_Port?	false	id_String	1527	tm_int
tm_int_Schema	tm_int_Schema?	false	id_String	SAPR3	tm_int
tm_int_Password	tm_int_Password?	false	id_Password	******	tm_int
tm_int_Server	tm_int_Server?	false	id_String	tm_int	tm_int
stage_area_AdditionalParams	stage_area_AdditionalParams?	false	id_String	noDatetimeStringSync=true	stage_area
stage_area_Password	stage_area_Password?	false	id_Password	******	stage_area
stage_area_Port	stage_area_Port?	false	id_String	3306	stage_area
stage_area_Database	stage_area_Database?	false	id_String	stage_area	stage_area
stage_area_Server	stage_area_Server?	false	id_String	localhost	stage_area
stage_area_Login	stage_area_Login?	false	id_String	root	stage_area
enobi_AdditionalParams	enobi_AdditionalParams?	false	id_String	noDatetimeStringSync=true	enobi
enobi_Server	enobi_Server?	false	id_String	localhost	enobi
enobi_Login	enobi_Login?	false	id_String	root	enobi
enobi_Port	enobi_Port?	false	id_String	3306	enobi
enobi_Database	enobi_Database?	false	id_String	enobi	enobi
enobi_Password	enobi_Password?	false	id_Password	******	enobi

Component List

Component Name	Component Type
tFileOutputExcel_1	tFileOutputExcel
tFlowMeter_16	tFlowMeter
tFlowMeter_21	tFlowMeter
tFlowToIterate_1	tFlowToIterate
tLogCatcher_1	tLogCatcher
tLogRow_1	tLogRow
tMap_1	tMap
tMap_2	tMap
tMysqlInput_1	tMysqlInput
tMysqlInput_2	tMysqlInput
tMysqlOutput_1	tMysqlOutput
tOracleInput_1	tOracleInput
tOracleInput_3	tOracleInput
tOracleInput_4	tOracleInput
tOracleInput_5	tOracleInput
tOracleInput_6	tOracleInput
tOracleInput_7	tOracleInput
tOracleInput_9	tOracleInput
tPrejob_1	tPrejob
tReplace_1	tReplace
tSendMail_1	tSendMail
tSetGlobalVar_1	tSetGlobalVar
tWarn_1	tWarn
tWarn_2	tWarn

Components Description

Component: tFileOutputExcel

	UNIQUE NAME	tFileOutputExcel_1	INPUT(S)	tMap_1, tOracleInput_3, tOracleInput_5, tOracleInput_4, tOracleInput_6, tOracleInput_7, tOracleInput_9
	LABEL	REGISTROS_ERRONEOS	OUTPUT(S)	none

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use Output Stream	false
File Name	"C:/temp/talend/rechazados.xls"
Sheet name	"Sheet1"
Include header	false
Añadir al fichero existente	true
Añadir a la hoja existente	true
Is absolute Y pos.	false
Font
Define all columns auto size	false
Define column auto size	[{IS_AUTO_SIZE=false, SCHEMA_COLUMN=material_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=material_desc}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=familia_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=familia_desc}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=denom_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=variet_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=formato_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=um_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=litros_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=linprod_id}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=linprod_desc}, {IS_AUTO_SIZE=false, SCHEMA_COLUMN=target_id}]
Create directory if not exists	true
Advanced separator(for number)	false
Codificación	"ISO-8859-15"
Show Information	false
Comentario	En el mapeo se verifica que la conversión a litros no devuelva un valor NULL, en cuyo caso el registro se rechaza y es enviado a un fichero Excel para su revisión.

Schema for tFileOutputExcel_1 :

Column	Key	Type	Length	Precision	Nullable
material_id	true	long	7		false
material_desc	false	String	40		false
familia_id	false	short	6		false
familia_desc	false	String	20		false
denom_id	false	String	30		false
variet_id	false	String	45		false
formato_id	false	String	18		false
um_id	false	String	10		false
litros_id	false	Float	10	2	true
linprod_id	false	short	6		false
linprod_desc	false	String	20		false
target_id	false	String	50		false

Original Function Parameters:

Component: tFlowMeter

	UNIQUE NAME	tFlowMeter_16	INPUT(S)	tMap_1
	LABEL	CUENTA_LEIDOS_SAP	OUTPUT(S)	tLogRow_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use input connection name as label	true
Mode	Relative
Connections List	Rechazados
Thresholds	[]
Show Information	false
Comentario	Incluimos en el flujo un control tFlowMeter para contar el numero de registros recuperados desde Sap (el valor se guarda en la tabla METER).

Schema for tFlowMeter_16 :

Column	Key	Type	Length	Precision	Nullable
material_id	true	long	7		false
material_desc	false	String	40		false
familia_id	false	short	6		false
familia_desc	false	String	20		false
denom_id	false	String	30		false
variet_id	false	String	45		false
formato_id	false	String	18		false
um_id	false	String	10		false
litros_id	false	Float	10	2	true
linprod_id	false	short	6		false
linprod_desc	false	String	20		false
target_id	false	String	50		false

Original Function Parameters:

Component: tFlowMeter

	UNIQUE NAME	tFlowMeter_21	INPUT(S)	tMap_2, tMysqlInput_2
	LABEL	CUENTA_MODIFICADOS	OUTPUT(S)	tMysqlOutput_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use input connection name as label	true
Mode	/ouative
Connections List	Rechazados
Thresholds	[]
Show Information	false
Comentario	Incluimos en el flujo un control tFlowMeter para contar el numero de registros recuperados desde Sap que incluyen alta o modificacion y que generaran un proceso de insert o update en la base de datos del DWH (el valor se guarda en la tabla METER).

Schema for tFlowMeter_21 :

Column	Key	Type	Length	Precision	Nullable
material_id	true	long	7		false
material_desc	false	String	40		false
familia_id	false	short	6		false
familia_desc	false	String	20		false
denom_id	false	String	30		false
variet_id	false	String	45		false
formato_id	false	String	18		false
um_id	false	String	10		false
litros_id	false	float	10	2	false
linprod_id	false	short	6		false
linprod_desc	false	String	20		false
target_id	false	String	50		false

Original Function Parameters:

Component: tFlowToIterate

	UNIQUE NAME	tFlowToIterate_1	INPUT(S)	tLogCatcher_1
	LABEL	__UNIQUE_NAME__	OUTPUT(S)	tSendMail_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use the default (key, value) in global variables.	true
Show Information	false
Comentario

Schema for tFlowToIterate_1 :

Column	Key	Type	Length	Precision	Nullable	Comment

Original Function Parameters:

Component: tLogCatcher

	UNIQUE NAME	tLogCatcher_1	INPUT(S)	tSetGlobalVar_1
	LABEL	CONTROL_ERRORES	OUTPUT(S)	tFlowToIterate_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Catch Java Exception	true
Catch tDie	true
Catch tWarn	false
Show Information	false
Comentario	Disparo el componente tLogCatcher, solo para los mensajes de error (die) y para las excepciones Java. Este componente se quedara escuchando durante la ejecución de todos los pasos del job, y en el caso de que haya error, se activara y producira el envio de un email de notificación.

Schema for tLogCatcher_1 :

Column	Key	Type	Length	Nullable
moment	false	java.util.Date		true
pid	false	String	20	true
root_pid	false	String	20	true
father_pid	false	String	20	true
project	false	String	50	true
job	false	String	255	true
context	false	String	50	true
priority	false	Integer	3	true
type	false	String	255	true
origin	false	String	255	true
message	false	String	255	true
code	false	Integer	3	true

Original Function Parameters:

Component: tLogRow

	UNIQUE NAME	tLogRow_1	INPUT(S)	tFlowMeter_16
	LABEL	__UNIQUE_NAME__	OUTPUT(S)	tMap_2

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Basic	true
Table (print values in cells of a table)	false
Vertical (each row is a key/value list)	false
Field Separator	"\|"
Print header	false
Print component unique name in front of each output row	false
Print schema column name in front of each value	false
Use fixed length for values	false
Show Information	false
Comentario	Log de visualización del flujo de datos para debug y verificaciones.

Schema for tLogRow_1 :

Column	Key	Type	Length	Precision	Nullable
material_id	true	long	7		false
material_desc	false	String	40		false
familia_id	false	short	6		false
familia_desc	false	String	20		false
denom_id	false	String	30		false
variet_id	false	String	45		false
formato_id	false	String	18		false
um_id	false	String	10		false
litros_id	false	float	10	2	false
linprod_id	false	short	6		false
linprod_desc	false	String	20		false
target_id	false	String	50		false

Original Function Parameters:

Component: tMysqlInput

	UNIQUE NAME	tMysqlInput_1	INPUT(S)	tPrejob_1
	LABEL	ULTIMA_FECHA_EJECUCION	OUTPUT(S)	tSetGlobalVar_1, tWarn_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	true
Use an existing connection	true
Component List	connectionStatsLogs
Table Name	"logs"
Guess Schema	""
Query	"select ifnull(max(logs.moment),MAKEDATE(2009,1)) as ultima_ejec from logs where logs.project = '" + ((String)projectName) + "' and logs.job = '" + ((String)jobName) + "' and message = 'FIN' and code = 0"
Codificación	"ISO-8859-15"
Enable stream	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=ultima_ejec, TRIM=false}]
Show Information	false
Comentario	Recuperamos de la tabla logs, la ultima fecha de ejecución CORRECTA del job. Esta fecha sera el punto de partida para recuperar los materiales dados de alta o modificaciones desde dicho momento. En el caso de que no se haya ejecutado nunca el proceso, generamos una fecha de referencia para la primera carga de datos (CARGA INICIAL).

Schema for tMysqlInput_1 :

Column	Key	Type	Length	Precision	Nullable	Comment
ultima_ejec	false	java.util.Date			true

Original Function Parameters:

Component: tMysqlInput

	UNIQUE NAME	tMysqlInput_2	INPUT(S)	none
	LABEL	LEER_DWD_PRODUCTO	OUTPUT(S)	tMap_2

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Host	context.enobi_Server
Port	context.enobi_Port
Base de Datos	context.enobi_Database
Username	context.enobi_Login
Password	context.enobi_Password
Table Name	"dwd_producto"
Guess Schema	""
Query	"SELECT dwd_producto.material_id, dwd_producto.material_desc, dwd_producto.familia_id, dwd_producto.familia_desc, dwd_producto.denom_id, dwd_producto.variet_id, dwd_producto.formato_id, dwd_producto.um_id, dwd_producto.litros_id, dwd_producto.linprod_id, dwd_producto.linprod_desc, dwd_producto.target_id FROM dwd_producto"
Additional JDBC Parameters	context.enobi_AdditionalParams
Codificación	"ISO-8859-15"
Enable stream	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=material_id, TRIM=false}, {SCHEMA_COLUMN=material_desc, TRIM=false}, {SCHEMA_COLUMN=familia_id, TRIM=false}, {SCHEMA_COLUMN=familia_desc, TRIM=false}, {SCHEMA_COLUMN=denom_id, TRIM=false}, {SCHEMA_COLUMN=variet_id, TRIM=false}, {SCHEMA_COLUMN=formato_id, TRIM=false}, {SCHEMA_COLUMN=um_id, TRIM=false}, {SCHEMA_COLUMN=litros_id, TRIM=false}, {SCHEMA_COLUMN=linprod_id, TRIM=false}, {SCHEMA_COLUMN=linprod_desc, TRIM=false}, {SCHEMA_COLUMN=target_id, TRIM=false}]
Show Information	false
Comentario	Recuperamos los registros existentes en la tabla DWD_PRODUCTO para comprobar si hay cambios con los registros recuperados desde el ERP y en caso afirmativo, pasar los registros al proceso de actualización.

Schema for row12 :

Column	Key	Type	Length	Precision	Nullable	Comment
material_id	true	long	10		false	Codigo Material (PK): clave
material_desc	false	String	40		false	Descripcion Material
familia_id	false	short	5		false	Identificador Familia
familia_desc	false	String	20		false	Descripcion Familia
denom_id	false	String	30		false	Desc. denominacion origen
variet_id	false	String	45		false	Descripcion varietales
formato_id	false	String	18		false	Desc. formato venta
um_id	false	String	10		false	Identificador de la unidad de medida de venta
litros_id	false	float	10	2	false	Equivalencia en litros
linprod_id	false	short	5		false	Identificador de la linea de producto
linprod_desc	false	String	20		false	Descripcion de la linea de producto
target_id	false	String	50		false	Descripcion de Target del producto

Original Function Parameters:

Component: tMysqlOutput

	UNIQUE NAME	tMysqlOutput_1	INPUT(S)	tFlowMeter_21
	LABEL	__UNIQUE_NAME__	OUTPUT(S)	tWarn_2

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Host	context.enobi_Server
Port	context.enobi_Port
Base de Datos	context.enobi_Database
Username	context.enobi_Login
Password	context.enobi_Password
Table	"dwd_producto"
Action on table	NONE
Action on data	INSERT_OR_UPDATE
Schema
Die on error	false
Additional JDBC Parameters	context.enobi_AdditionalParams
Codificación	"ISO-8859-15"
Commit every	10000
Columnas adicionales	[]
Use field options	false
Use Hint Options	false
Enable debug mode	false
Show Information	false
Comentario	Inserción en Mysql, en la tabla del DWH correspondiente a la dimensión Producto (DWD_PRODUCTO).

Schema for tMysqlOutput_1 :

Column	Key	Type	Length	Precision	Nullable
material_id	true	long	7		false
material_desc	false	String	40		false
familia_id	false	short	6		false
familia_desc	false	String	20		false
denom_id	false	String	30		false
variet_id	false	String	45		false
formato_id	false	String	18		false
um_id	false	String	10		false
litros_id	false	float	10	2	false
linprod_id	false	short	6		false
linprod_desc	false	String	20		false
target_id	false	String	50		false

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_1	INPUT(S)	none
	LABEL	LECT_MAESTRO_PRODUCTO	OUTPUT(S)	tReplace_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	true
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"MARA"
Guess Schema	""
Query	"SELECT MARA.matnr, MARA.matkl, MARA.LABOR, MARA.FERTH, MARA.NORMT, MARA.meins, MARA.extwg, MARA.WRKST FROM SAPR3.MARA where mtart = 'TICV' and laeda between '" + TalendDate.formatDate("yyyyMMdd",((Date)globalMap.get("ultima_ejec"))) + "' and '" + TalendDate.formatDate("yyyyMMdd",TalendDate.getCurrentDate()) + "' or ERSDA between '" + TalendDate.formatDate("yyyyMMdd",((Date)globalMap.get("ultima_ejec"))) + "' and '" + TalendDate.formatDate("yyyyMMdd",TalendDate.getCurrentDate()) + "'";
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=matnr, TRIM=false}, {SCHEMA_COLUMN=matkl, TRIM=false}, {SCHEMA_COLUMN=labor, TRIM=false}, {SCHEMA_COLUMN=ferth, TRIM=false}, {SCHEMA_COLUMN=normt, TRIM=false}, {SCHEMA_COLUMN=meins, TRIM=false}, {SCHEMA_COLUMN=extwg, TRIM=false}, {SCHEMA_COLUMN=wrkst, TRIM=false}]
Show Information	false
Comentario	Lectura Oracle desde Sap de la tabla MARA, que es el maestro de materiales. Se buscan los registros creados o modificados desde la fecha de la ultima ejecución hasta el momento actual.

Schema for tOracleInput_1 :

Column	Key	Type	Length	Nullable	Comment
matnr	true	String		false	Material
matkl	false	String		true	Familia
labor	false	String		true	Denominacion Origen
ferth	false	String		true	Varietal
normt	false	String		true	Formato Venta
meins	false	String	3	true	Unidad Medida
extwg	false	String		true	Linea Producto
wrkst	false	String		true	Target

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_3	INPUT(S)	none
	LABEL	LECT_DESC_PRODUCTO	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"MAKT"
Guess Schema	""
Query	"select matnr,maktx from sapr3.makt where spras = 'S' and matnr = " + ((String)globalMap.get("var.matnr"))
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=matnr, TRIM=false}, {SCHEMA_COLUMN=maktx, TRIM=false}]
Show Information	false
Comentario

Schema for tOracleInput_3 :

Column	Key	Type	Length	Precision	Nullable	Comment
matnr	true	String			false
maktx	false	String			false

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_4	INPUT(S)	none
	LABEL	LECT_DESC_LINPROD	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"TWEWT"
Guess Schema	""
Query	"select EXTWG, EWBEZ from SAPR3.TWEWT where spras ='S' UNION select 'MERC9999','VINOS MESA' from sapr3.twewt where spras = 'S'"
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=extwg, TRIM=false}, {SCHEMA_COLUMN=ewbez, TRIM=false}]
Show Information	false
Comentario

Schema for tOracleInput_4 :

Column	Key	Type	Length	Precision	Nullable	Comment
extwg	true	String			true
ewbez	false	String			true

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_5	INPUT(S)	none
	LABEL	LECT_DESC_FAMILIA	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"T023T"
Guess Schema	""
Query	"select MATKL,WGBEZ from sapr3.T023T where spras = 'S'"
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=matkl, TRIM=false}, {SCHEMA_COLUMN=wgbez, TRIM=false}]
Show Information	false
Comentario

Schema for tOracleInput_5 :

Column	Key	Type	Length	Precision	Nullable	Comment
matkl	true	String			true
wgbez	false	String			true

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_6	INPUT(S)	none
	LABEL	LECT_DESC_DENOM_ORIGEN	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"T024X"
Guess Schema	""
Query	"select LABOR, LBTXT from SAPR3.T024X where spras ='S' UNION select '9999','SIN DENOMINACION ORIGEN' from sapr3.T024X where spras = 'S'"
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=labor, TRIM=false}, {SCHEMA_COLUMN=lbtxt, TRIM=false}]
Show Information	false
Comentario

Schema for tOracleInput_6 :

Column	Key	Type	Length	Precision	Nullable	Comment
labor	true	String			true
lbtxt	false	String			true

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_7	INPUT(S)	none
	LABEL	LECT_CONVERSION_LITROS	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"MARM"
Guess Schema	""
Query	"select matnr,umren from sapr3.marm where meinh = 'ML'"
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=matnr, TRIM=false}, {SCHEMA_COLUMN=umren, TRIM=false}]
Show Information	false
Comentario

Schema for tOracleInput_7 :

Column	Key	Type	Length	Precision	Nullable	Comment
matnr	true	String			true
umren	false	Float	10	2	true

Original Function Parameters:

Component: tOracleInput

	UNIQUE NAME	tOracleInput_9	INPUT(S)	none
	LABEL	LECT_UMEDIDA_ESP	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Use an existing connection	false
Connection Type	ORACLE_SID
DB Version	ojdbc14-9i.jar
Host	context.tm_int_Server
Port	context.tm_int_Port
Base de Datos	context.tm_int_Sid
Oracle schema	context.tm_int_Schema
Username	context.tm_int_Login
Password	context.tm_int_Password
Table Name	"T006A"
Guess Schema	""
Query	"select MSEHI,MSEH3 from sapr3.T006A where spras = 'S'"
Codificación	"ISO-8859-15"
Use cursor	false
Trim all the String/Char columns	false
Trim column	[{SCHEMA_COLUMN=msehi, TRIM=false}, {SCHEMA_COLUMN=mseh3, TRIM=false}]
Show Information	false
Comentario

Schema for tOracleInput_9 :

Column	Key	Type	Length	Precision	Nullable	Comment
msehi	true	String	3		false
mseh3	false	String	3		false

Original Function Parameters:

Component: tPrejob

	UNIQUE NAME	tPrejob_1	INPUT(S)	none
	LABEL	__UNIQUE_NAME__	OUTPUT(S)	tMysqlInput_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Show Information	false
Comentario	Prejob para la orquestación del proceso. Tiene los siguientes pasos: -Recuperación de la ultima fecha de ejecución correcta del proceso. -Generación de mensaje de log de inicio del proceso. -Lanzamiento del componente tLogCatcher que quedara escuchando los mensajes de log generados, y en el caso de que se genere un error, mandara un email de notificación.

Original Function Parameters:

Component: tReplace

	UNIQUE NAME	tReplace_1	INPUT(S)	tOracleInput_1
	LABEL	AJUSTE_CAMPOS	OUTPUT(S)	tMap_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Simple mode ( search with regexp pattern or glob expression)	true
Search/Replace	[{USE_GLOB=false, CASE_SENSITIVE=false, INPUT_COLUMN=extwg, REPLACE_STRING="MERC9999", COMMENT=Ajuste Linea Producto, WHOLE_WORD=true, SEARCH_PATTERN=" "}, {USE_GLOB=false, CASE_SENSITIVE=false, INPUT_COLUMN=labor, REPLACE_STRING="9999", COMMENT=Ajuste Denominac. Origen, WHOLE_WORD=true, SEARCH_PATTERN=" "}, {USE_GLOB=false, CASE_SENSITIVE=false, INPUT_COLUMN=wrkst, REPLACE_STRING="GRAN CONSUMO", COMMENT=Ajuste Target, WHOLE_WORD=true, SEARCH_PATTERN=" "}, {USE_GLOB=false, CASE_SENSITIVE=false, INPUT_COLUMN=ferth, REPLACE_STRING="MEZCLA", COMMENT=Ajuste Varietal, WHOLE_WORD=true, SEARCH_PATTERN=" "}, {USE_GLOB=false, CASE_SENSITIVE=false, INPUT_COLUMN=normt, REPLACE_STRING="BOTELLA", COMMENT=Ajuste Formato Venta, WHOLE_WORD=true, SEARCH_PATTERN=" "}]
Advanced mode ( search with regexp pattern )	false
Show Information	false
Comentario	Reemplazamos algunos campos en el caso de que venga vacios con valores genericos que agrupan los registros sin valor.

Schema for tReplace_1 :

Column	Key	Type	Length	Nullable
matnr	true	String		false
matkl	false	String		true
labor	false	String		true
ferth	false	String		true
normt	false	String		true
meins	false	String	3	true
extwg	false	String		true
wrkst	false	String		true

Original Function Parameters:

Component: tSendMail

	UNIQUE NAME	tSendMail_1	INPUT(S)	tFlowToIterate_1
	LABEL	ENVIO_EMAIL_NOTIF	OUTPUT(S)	none

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
To	"respinosamilla@yahoo.com"
From	"respinosamilla@gmail.com"
Show sender's name?	false
Cc	"respinosamilla@gmail.com"
Bcc
Subject	"Error en ejecución del Job " + jobName
Message	"Cancelado el trabajo " + jobName + "\nen el paso " + row12.origin + "\n con el mensaje de error " + row12.message
Die if the attachment file doesn't exist.	true
Attachments	[]
Other headers	[]
SMTP host	"smtp.gmail.com"
SMTP port	465
SSL Support	true
STARTTLS Support	false
Importance	High
Need authentication?	true
Username	"respinosamilla@gmail.com"
Password	"atitelavoyadeciryo"
Die on error	true
MIME subtype from the 'text' MIME type	plain
charset used for encoding message	"ISO-8859-15"
Show Information	false
Comentario	En el caso de error o excepción Java, enviamos un email informativo, indicado que el job se ha parado, en que paso y con que mensaje de error.

Schema for tSendMail_1 :

Column	Key	Type	Length	Nullable
moment	false	java.util.Date		true
pid	false	String	20	true
root_pid	false	String	20	true
father_pid	false	String	20	true
project	false	String	50	true
job	false	String	255	true
context	false	String	50	true
priority	false	Integer	3	true
type	false	String	255	true
origin	false	String	255	true
message	false	String	255	true
code	false	Integer	3	true

Original Function Parameters:

Component: tSetGlobalVar

	UNIQUE NAME	tSetGlobalVar_1	INPUT(S)	tMysqlInput_1
	LABEL	SET_VARIABLE_FECHA	OUTPUT(S)	tLogCatcher_1

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Variables	[{VALUE=row4.ultima_ejec, KEY="ultima_ejec"}]
Show Information	false
Comentario	La fecha recuperada la almaceno en la variable global ultima_ejec para luego poder utilizarla en los diferentes pasos del job.

Schema for tSetGlobalVar_1 :

Column	Key	Type	Length	Precision	Nullable	Comment
ultima_ejec	false	java.util.Date			true

Original Function Parameters:

Component: tWarn

	UNIQUE NAME	tWarn_1	INPUT(S)	tMysqlInput_1
	LABEL	MENSAJE_LOG_INICIO	OUTPUT(S)	none

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Warn message	"INICIO"
Codigo	0
Priority	3
Show Information	false
Comentario	Utilizando el componente tWarn, genero un mensaje en el log correspondiente al INICIO del trabajo (generara un registro en la tabla LOGS).

Schema for tWarn_1 :

Column	Key	Type	Length	Precision	Nullable	Comment

Original Function Parameters:

Component: tWarn

	UNIQUE NAME	tWarn_2	INPUT(S)	tMysqlOutput_1
	LABEL	MENSAJE_LOG_FIN	OUTPUT(S)	none

Component Parameters:

Properties	Values
Activate	true
tStatCatcher Statistics	false
Warn message	"FIN"
Codigo	0
Priority	3
Show Information	false
Comentario	Utilizando el componente tWarn, genero un mensaje en el log correspondiente al FIN correcto del trabajo (generara un registro en la tabla LOGS).

Schema for tWarn_2 :

Column	Key	Type	Length	Precision	Nullable	Comment

Original Function Parameters:

Component: tMap

	UNIQUE NAME	tMap_1	INPUT(S)	tMap_1, tOracleInput_3, tOracleInput_5, tOracleInput_4, tOracleInput_6, tOracleInput_7, tOracleInput_9
	LABEL	MAPEO_PRODUCTO	OUTPUT(S)	tFlowMeter_16, tFileOutputExcel_1

Component Parameters:

Properties	Values
tStatCatcher Statistics	true
Mapping links display as:	AUTO
Temp data directory path:
Max buffer size (nb of rows):	2000000
Show Information	false
Comentario	A partir del registro recuperado de la tabla MARA, realizamos un mapeo completando el resto de campos de la dimension con tablas de LOOKUP. Recuperamos la descripción del producto, descripción de la familia, descripción de la linea de producto, descripción de la denominación de origen y la conversión a litros según las unidades de medida. En el mapeo se verifica que la conversión a litros no devuelva un valor NULL, en cuyo caso el registro se rechaza y es enviado a un fichero Excel para su revisión.

Mapper table for tMap_1 ( input ):

Mapper table Properties( row2 ):

Properties	Values
Name	row2
Matching-mode	UNIQUE_MATCH
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row2 ):

Name	Type	isNullable
matnr	String	false
matkl	String	true
labor	String	true
ferth	String	true
normt	String	true
meins	String	true
extwg	String	true
wrkst	String	true

Constraint Table Entries( row2 ):

Name	Type	Expression	isNullable

Mapper table Properties( row6 ):

Properties	Values
Name	row6
Matching-mode	UNIQUE_MATCH
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row6 ):

Name	Type	Expression	isNullable
matnr	String	row2.matnr	false
maktx	String		false

Constraint Table Entries( row6 ):

Name	Type	Expression	isNullable

Mapper table Properties( row5 ):

Properties	Values
Name	row5
Matching-mode	UNIQUE_MATCH
isMinimized	true
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row5 ):

Name	Type	Expression	isNullable
matkl	String	row2.matkl	true
wgbez	String		true

Constraint Table Entries( row5 ):

Name	Type	Expression	isNullable

Mapper table Properties( row7 ):

Properties	Values
Name	row7
Matching-mode	UNIQUE_MATCH
isMinimized	true
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row7 ):

Name	Type	Expression	isNullable
extwg	String	row2.extwg	true
ewbez	String		true

Constraint Table Entries( row7 ):

Name	Type	Expression	isNullable

Mapper table Properties( row8 ):

Properties	Values
Name	row8
Matching-mode	UNIQUE_MATCH
isMinimized	true
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row8 ):

Name	Type	Expression	isNullable
labor	String	row2.labor	true
lbtxt	String		true

Constraint Table Entries( row8 ):

Name	Type	Expression	isNullable

Mapper table Properties( row9 ):

Properties	Values
Name	row9
Matching-mode	UNIQUE_MATCH
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row9 ):

Name	Type	Expression	isNullable
matnr	String	row2.matnr	true
umren	Float		true

Constraint Table Entries( row9 ):

Name	Type	Expression	isNullable

Mapper table Properties( row16 ):

Properties	Values
Name	row16
Matching-mode	UNIQUE_MATCH
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row16 ):

Name	Type	Expression	isNullable
msehi	String	row2.meins	false
mseh3	String		false

Constraint Table Entries( row16 ):

Name	Type	Expression	isNullable

Mapper table for tMap_1 ( output ):

Mapper table Properties( row3 ):

Properties	Values
Name	row3
Matching-mode
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row3 ):

Name	Type	Expression	isNullable
material_id	long	Long.valueOf(StringHandling.RIGHT(row2.matnr,7))	false
material_desc	String	StringHandling.UPCASE(row6.maktx)	false
familia_id	short	Short.valueOf(row2.matkl)	false
familia_desc	String	StringHandling.UPCASE(row5.wgbez)	false
denom_id	String	row8.lbtxt	false
variet_id	String	row2.ferth	false
formato_id	String	row2.normt	false
um_id	String	row16.mseh3	false
litros_id	Float	row9.umren / 1000	true
linprod_id	short	Short.valueOf(StringHandling.RIGHT(row2.extwg,4))	false
linprod_desc	String	StringHandling.UPCASE(row7.ewbez)	false
target_id	String	row2.wrkst	false

Constraint Table Entries( row3 ):

Name	Type	Expression	isNullable

Mapper table Properties( Rechazados ):

Properties	Values
Name	Rechazados
Matching-mode
isMinimized	false
isReject	true
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( Rechazados ):

Name	Type	Expression	isNullable
material_id	long	Long.valueOf(StringHandling.RIGHT(row2.matnr,7))	false
material_desc	String	StringHandling.UPCASE(row6.maktx)	false
familia_id	short	Short.valueOf(row2.matkl)	false
familia_desc	String	StringHandling.UPCASE(row5.wgbez)	false
denom_id	String	row8.lbtxt	false
variet_id	String	row2.ferth	false
formato_id	String	row2.normt	false
um_id	String	row16.mseh3	false
litros_id	Float	Float.valueOf("0.750")	true
linprod_id	short	Short.valueOf(StringHandling.RIGHT(row2.extwg,4))	false
linprod_desc	String	StringHandling.UPCASE(row7.ewbez)	false
target_id	String	row2.wrkst	false

Constraint Table Entries( Rechazados ):

Name	Type	Expression	isNullable

Mapper table for tMap_1 ( var ):

Mapper table Properties( Var ):

Properties	Values
Name	Var
Matching-mode
isMinimized	true
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( Var ):

Name	Type	Expression	isNullable

Constraint Table Entries( Var ):

Name	Type	Expression	isNullable

Component: tMap

	UNIQUE NAME	tMap_2	INPUT(S)	tMap_2, tMysqlInput_2
	LABEL	VERIF_MODIFICACIONES	OUTPUT(S)	tFlowMeter_21

Component Parameters:

Properties	Values
tStatCatcher Statistics	false
Mapping links display as:	AUTO
Temp data directory path:
Max buffer size (nb of rows):	2000000
Show Information	false
Comentario

Mapper table for tMap_2 ( input ):

Mapper table Properties( row11 ):

Properties	Values
Name	row11
Matching-mode	UNIQUE_MATCH
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row11 ):

Name	Type	isNullable
material_id	long	false
material_desc	String	false
familia_id	short	false
familia_desc	String	false
denom_id	String	false
variet_id	String	false
formato_id	String	false
um_id	String	false
litros_id	float	false
linprod_id	short	false
linprod_desc	String	false
target_id	String	false

Constraint Table Entries( row11 ):

Name	Type	Expression	isNullable

Mapper table Properties( row15 ):

Properties	Values
Name	row15
Matching-mode	UNIQUE_MATCH
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( row15 ):

Name	Type	Expression	isNullable
material_id	long	row11.material_id	false
material_desc	String		false
familia_id	short		false
familia_desc	String		false
denom_id	String		false
variet_id	String		false
formato_id	String		false
um_id	String		false
litros_id	float		false
linprod_id	short		false
linprod_desc	String		false
target_id	String		false

Constraint Table Entries( row15 ):

Name	Type	Expression	isNullable

Mapper table for tMap_2 ( output ):

Mapper table Properties( modificados ):

Properties	Values
Name	modificados
Matching-mode
isMinimized	false
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( modificados ):

Name	Type	Expression	isNullable
material_id	long	row11.material_id	false
material_desc	String	row11.material_desc	false
familia_id	short	row11.familia_id	false
familia_desc	String	row11.familia_desc	false
denom_id	String	row11.denom_id	false
variet_id	String	row11.variet_id	false
formato_id	String	row11.formato_id	false
um_id	String	row11.um_id	false
litros_id	float	row11.litros_id	false
linprod_id	short	row11.linprod_id	false
linprod_desc	String	row11.linprod_desc	false
target_id	String	row11.target_id	false

Constraint Table Entries( modificados ):

Name	Type	Expression	isNullable

Mapper table for tMap_2 ( var ):

Mapper table Properties( Var ):

Properties	Values
Name	Var
Matching-mode
isMinimized	true
isReject	false
isRejectInnerJoin	false
isInnerJoin	false

Metadata Table Entries( Var ):

Name	Type	Expression	isNullable

Constraint Table Entries( Var ):

Name	Type	Expression	isNullable