explanner_vfquotasplit.int

// Script generated by DI Visual Integrator

version "1";
object 'PARM' "Parms" {
	parms = {
		{
			name    = "LETTER",
			default = ""
		},
		{
			name    = "VND",
			default = "false",
			type    = "boolean"
		}
	}
};

object 'TSKL' "Main" {
	contents = {
		`Join Tables and Split on Time`
	}
};

object 'TASK' "Join Tables and Split on Time" {
	inputs    = {
		`VF Quota`,
		`Planner Goals`,
		`Planner Goals Master`,
		`Warehouse Master`,
		`Item Master`,
		`Planner VRC Groups`,
		`State List`,
		`VendorFilterCol Lookup`,
		`Vendor Time`
	},
	processes = {
		`squash vf quota`,
		`Filter Planner Goals Master`,
		`squash planner goals`,
		`Join Planner Goals Master`,
		`Filter Planner Goals`,
		`Join Planner Goals`,
		`Filter Out Planner Goals`,
		`VF Quota Calcs`,
		`Join Warehouse Master`,
		`Join Item Master`,
		`Calc Planner VRC Groups`,
		`Join Planner VRC Groups`,
		`State List Calcs`,
		`Join State List`,
		`Filter on State List`,
		`Do Calcs`,
		`Join Vendor Filter`,
		`Filter Vendor`,
		`Join Vendor Time`,
		`Vendor Time Filter Calc`,
		`Filter`,
		`trace-1-TRA`
	},
	output    = `Split`
};

object 'INPT' "VF Quota" {
	//+ coordinatex = `158`,
	//+ coordinatey = `8`,
	input_type      = `Filein`,
	filenames       = { `../data/current-goals.txt`, `../data/original-goals.txt` },
	file_type       = `column_headers`,
	filename_column = `Goal Type`,
	aliases         = {
		`Goal ID=Planner Quota Number`
	}
};

object 'INPT' "Planner Goals" {
	//+ coordinatex = `8`,
	//+ coordinatey = `83`,
	input_type      = `filein`,
	filenames       = { `../../global/data/original-goals.txt` },
	file_type       = `column_headers`,
	filename_column = `Filename`,
	aliases         = {
		`Goal ID=Planner Quota Number`
	},
	keep_columns    = {
		`Planner Quota Number`
	}
};

object 'INPT' "Planner Goals Master" {
	//+ coordinatex = `8`,
	//+ coordinatey = `158`,
	input_type      = `Filein`,
	filenames       = { `../../global/data/goal-master.txt` },
	file_type       = `column_headers`,
	filename_column = `Filename`,
	aliases         = {
		`Goal Type=Planner Vendor Flag`,
		`Goal ID=Planner Quota Number`
	},
	keep_columns    = {
		`Planner Quota Number`,
		`Planner Vendor Flag`
	}
};

object 'INPT' "Warehouse Master" {
	//+ coordinatex = `533`,
	//+ coordinatey = `83`,
	input_type   = `Filein`,
	filenames    = { `../data/gogwmpdd.csv` },
	dictfile     = `../programs/gogwmp.dic`,
	aliases      = {
		`Warehouse State Name=State Name`
	},
	keep_columns = {
		`Warehouse Number`,
		`Warehouse City`,
		`Warehouse State`,
		`State Name`,
		`City Abbrev`,
		`Old Warehouse Number`
	}
};

object 'INPT' "Item Master" {
	//+ coordinatex = `608`,
	//+ coordinatey = `83`,
	input_type   = `Filein`,
	filenames    = { `../data/gowimpdd.csv` },
	dictfile     = `../programs/gowimp.dic`,
	aliases      = {
		`Wine Gallons=Wine Gallons Conversion Factor`,
		`Vendor Report Code=Vendor Report Code Name`
	},
	keep_columns = {
		`Warehouse Number`,
		`Item Number`,
		`Pack Code`,
		`Vendor Number`,
		`Vendor Name`,
		`Vendor Report Code Number`,
		`Vendor Report Code Name`,
		`Vendor Fiscal Month`,
		`Wine Gallons Conversion Factor`
	}
};

object 'INPT' "Planner VRC Groups" {
	//+ coordinatex = `608`,
	//+ coordinatey = `158`,
	input_type  = `Filein`,
	filenames   = { `../data/vrc-groups.txt` },
	file_type   = `column_headers`,
	aliases     = {
		`State=Warehouse State`,
		`Vendor Report Code Group=Vendor Report Group Desc`
	}
};

object 'INPT' "State List" {
	//+ coordinatex = `683`,
	//+ coordinatey = `233`,
	input_type  = `Filein`,
	file_type   = `column_headers`,
	filenames   = { `../execmdls/exbuildorder.txt` },
	aliases     = {
		`State Name=Buildorder Name`
	}
};

object 'INPT' "VendorFilterCol Lookup" {
	//+ coordinatex = `983`,
	//+ coordinatey = `83`,
	input_type  = `Filein`,
	filenames   = { `../execmdls/exvndlookup.txt` },
	delimiter   = `\t`,
	file_type   = `column_headers`
};

object 'INPT' "Vendor Time" {
	//+ coordinatex = `1133`,
	//+ coordinatey = `83`,
	input_type  = `Filein`,
	file_type   = `column_headers`,
	filenames   = { `../temp/vfquotatime.txt` }
};

object 'PROC' "Squash VF Quota" {
	//+ coordinatex = `233`,
	//+ coordinatey = `8`,
	process_type = `squash`,
	input        = `VF Quota`,
	dimensions   = {
		`YearMo`,
		`Item Number`,
		`Pack Code`,
		`Program`,
		`Planner Quota Number`,
		`Warehouse Number`,
		`Goal Type`
	},
	summary      = { `Units`, `Wine Gallons` }
};

object 'PROC' "Filter Planner Goals Master" {
	//+ notes = ` 1=Vendor, 2=Program`,
	//+ coordinatex = `83`,
	//+ coordinatey = `158`,
	process_type = `filter`,
	input        = `Planner goals Master`,
	action       = `keep`,
	filters      = {
		{
			column = `Planner Vendor Flag`,
			values = { `1` }
		}
	}
// 1=Vendor, 2=Program
};

object 'PROC' "squash Planner goals" {
	//+ coordinatex = `83`,
	//+ coordinatey = `83`,
	process_type = `squash`,
	input        = `planner goals`,
	dimensions   = { `Planner Quota Number` }
};

object 'PROC' "Join Planner Goals Master" {
	//+ coordinatex = `158`,
	//+ coordinatey = `83`,
	process_type = `Lookup`,
	inputs       = {
		`squash Planner Goals`,
		`Filter Planner Goals Master`
	},
	multijoins   = {
		{`Planner Quota Number`, `Planner Quota Number` }
	}
};

object 'PROC' "Filter Planner Goals" {
// another one to keep successful joins
	//+ notes = ` another one to keep successful joins\n`
		//+ ` 1=Vendor, 2=Program`,
	//+ coordinatex = `233`,
	//+ coordinatey = `83`,
	process_type = `filter`,
	input        = `Join Planner goals master`,
	action       = `keep`,
	filters      = {
		{
			column = `Planner Vendor Flag`,
			values = { `1` }
		}
	}
// 1=Vendor, 2=Program
};

object 'PROC' "Join Planner Goals" {
	//+ coordinatex = `308`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`Squash VF Quota`,
		`Filter Planner Goals`
	},
	multijoins   = {
		{`Planner Quota Number`, `Planner Quota Number` }
	}
};

object 'PROC' "Filter out Planner Goals" {
// another one to keep successful joins
	//+ notes = ` another one to keep successful joins\n`
		//+ ` 1=Vendor, 2=Program`,
	//+ coordinatex = `383`,
	//+ coordinatey = `8`,
	process_type = `filter`,
	input        = `Join Planner goals`,
	action       = `keep`,
	filters      = {
		{
			column = `Planner Vendor Flag`,
			values = { `1` }
		}
	}
// 1=Vendor, 2=Program
};

object 'PROC' "VF Quota Calcs" {
	//+ coordinatex = `458`,
	//+ coordinatey = `8`,
	process_type = `calc`,
	input        = `Filter out Planner Goals`,
	calcs        = {
		{
			name   = `Goal Type`,
			value  = `if(regexp(Goal Type,"current"),"current",if(regexp(Goal Type,"original`
				`"),"original",""))`,
			update = `true`
		},
		{
			name   = `YearMo`,
			value  = `replace(YearMo,"-","")`,
			update = `true`
		},
		{
			name   = `Warehouse Number`,
			value  = `lpad(Warehouse Number,7,"0")`,
			update = `true`
		},
		{
			name   = `Item Number`,
			value  = `lpad(Item Number,5,"0")`,
			update = `true`
		},
		{
			name   = `Pack Code`,
			value  = `lpad(Pack Code,2,"0")`,
			update = `true`
		}
	}
};

object 'PROC' "Join Warehouse Master" {
	//+ coordinatex = `608`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`trace-1-TRA`,
		`Warehouse Master`
	},
	multijoins   = {
		{`Warehouse Number`, `Warehouse Number` }
	}
};

object 'PROC' "Join Item Master" {
	//+ coordinatex = `683`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`Join Warehouse Master`,
		`Item Master`
	},
	multijoins   = {
		{`Warehouse Number`, `Warehouse Number` },
		{`Item Number`, `Item Number` },
		{`Pack Code`, `Pack Code` }
	}
};

object 'PROC' "Calc Planner VRC Groups" {
	//+ coordinatex = `683`,
	//+ coordinatey = `158`,
	process_type = `calc`,
	input        = `Planner VRC Groups`,
	calcs        = {
		{
			name   = `Vendor Number`,
			value  = `lpad(Vendor Number,7,"0")`,
			update = `true`
		},
		{
			name   = `Vendor Report Code Number`,
			value  = `lpad(Vendor Report Code Number,2,"0")`,
			update = `true`
		},
		{
			name  = `Vendor Report Group Number`,
			value = `""`
		}
	}
};

object 'PROC' "Join Planner VRC Groups" {
	//+ coordinatex = `758`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`Join Item Master`,
		`Calc Planner VRC Groups`
	},
	multijoins   = {
		{`Warehouse State`, `Warehouse State` },
		{`Vendor Number`, `Vendor Number` },
		{`Vendor Report Code Number`, `Vendor Report Code Number` }
	}
};

object 'PROC' "State List Calcs" {
	//+ coordinatex = `758`,
	//+ coordinatey = `233`,
	process_type = `calc`,
	input        = `State List`,
	calcs        = {
		{
			name  = `State List Filter`,
			value = `"Keep"`
		}
	}
};

object 'PROC' "Join State List" {
	//+ coordinatex = `833`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`Join Planner VRC Groups`,
		`State List Calcs`
	},
	multijoins   = {
	#if VND
		{`Vendor Number`, `State` },
	#endif
	#if !(VND)
		{`Warehouse State`, `State` }
	#endif
	}
};

object 'PROC' "Filter on State List" {
	//+ coordinatex = `908`,
	//+ coordinatey = `8`,
	process_type = `filter`,
	input        = `Join State List`,
	action       = `keep`,
	filters      = {
		{
			column = `State List Filter`,
			values = { `Keep` }
		}
	}
};

object 'PROC' "Do Calcs" {
	//+ coordinatex = `983`,
	//+ coordinatey = `8`,
	process_type = `calc`,
	input        = `Filter on State List`,
	calcs        = {
	#if VND
		{
			column   = `Model Path Calc`,
			calc_str = `concat("../temp/",Buildorder Name,"-vfquota_planner=",Vendor Number,".txt")`
		},
	#endif
	#if !(VND)
		{
			name  = `Model Path Calc`,
			value = `concat("../temp/",City Abbrev,"-vfquota_planner=",Warehouse State,".txt")`
		},
	#endif
		{
			name  = `Model`,
			value = `Model Path Calc`
		},
		{
			name  = `Goal Cases`,
			value = `Units*if(Goal Type="current",1,0)`
		},
		{
			name  = `Original Goal Cases`,
			value = `Units*if(Goal Type="original",1,0)`
		},
		{
			name  = `Goal 9L`,
			value = `Units*Wine Gallons Conversion Factor*3.7853/9*if(Goal Type="current",1,0)`
		},
		{
			name  = `Original Goal 9L`,
			value = `Units*Wine Gallons Conversion Factor*3.7843/9*if(Goal Type="original",1,0)`
		},
		{
			name  = `Goal CE`,
			value = `Units*Wine Gallons Conversion Factor/2.25*if(Goal Type="current",1,0)`
		},
		{
			name  = `Original Goal CE`,
			value = `Units*Wine Gallons Conversion Factor/2.25*if(Goal Type="original",1,0)`
		}
	}
};

object 'PROC' "Join Vendor Filter" {
	//+ coordinatex = `1058`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`Do Calcs`,
		`VendorFilterCol Lookup`
	},
	multijoins   = {
		{`Vendor Number`, `Vndr` }
	}
};

object 'PROC' "Filter Vendor" {
	//+ coordinatex = `1133`,
	//+ coordinatey = `8`,
	input        = `Join Vendor Filter`,
	process_type = `filter`,
	action       = `keep`,
	filters      = {
		{
			column = `VndrFltrCol`,
			values = { `keep` }
		}
	}
};

object 'PROC' "Join Vendor Time" {
	//+ coordinatex = `1208`,
	//+ coordinatey = `8`,
	process_type = `Lookup`,
	inputs       = {
		`Filter Vendor`,
		`Vendor Time`
	},
	multijoins   = {
		{`YearMo`, `YearMo` },
		{`Vendor Fiscal Month`, `Vendor Fiscal Month` },
		{`City Abbrev`, `City Abbrev` }
	}
};

object 'PROC' "Vendor Time Filter Calc" {
	//+ coordinatex = `1283`,
	//+ coordinatey = `8`,
	process_type = `calc`,
	input        = `Join Vendor Time`,
	calcs        = {
		{
			name  = `Vendor Time Filter Col`,
			value = `Vendor TY+Vendor NY`
		}
	}
};

object 'PROC' "Filter" {
	//+ coordinatex = `1358`,
	//+ coordinatey = `8`,
	input        = `Vendor Time Filter Calc`,
	process_type = `filter`,
	action       = `keep`,
	filters      = {
		{
			column = `Vendor Time Filter Col`,
			values = { `1` }
		}
	}
};

object 'PROC' "trace-1-TRA" {
	//+ coordinatex = `533`,
	//+ coordinatey = `8`,
	process_type = `trace`,
	format       = `column_headers`,
	input        = `VF Quota Calcs`,
	qual_str     = `Planner Quota Number="209"`,
	limit        = 1
};

object 'OUTP' "Split" {
	//+ coordinatex = `1433`,
	//+ coordinatey = `8`,
	output_type     = `split`,
	input           = `Filter`,
	dictfile1       = `../temp/vfquota.dic`,
	filename_column = `Model`,
	reportfile      = `../temp/vfquota.rep`,
	reportfile_type = `column_headers`,
	columns         = {
		`YearMo`,
		`Vendor YTD`,
		`Vendor TY`,
		`Vendor NY`,
		`Mo1`,
		`Mo2`,
		`Mo3`,
		`Mo4`,
		`Mo5`,
		`Mo6`,
		`Mo7`,
		`Mo8`,
		`Mo9`,
		`Mo10`,
		`Mo11`,
		`Mo12`,
		`Vendor Fiscal Month`,
		`Fiscal Month Count`,
		`Warehouse State`,
		`Warehouse City`,
		`Warehouse Number`,
		`Old Warehouse Number`,
		`Vendor Name`,
		`Vendor Number`,
		`Vendor Report Code Number`,
		`Vendor Report Code Name`,
		`Vendor Report Group Number`,
		`Vendor Report Group Desc`,
		`Original Goal Cases`,
		`Original Goal 9L`,
		`Original Goal CE`,
		`Goal Cases`,
		`Goal 9L`,
		`Goal CE`
	}
};

//+audits = {
//+`<2014-06-06>, <Bill Lee>: Modified script`,
//+}