/* Copyright (c) 2017 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice. */
/*
   tblTop /* structure: Use and Allocation of Items */
	TopItemLineNum
	TopItemName
	TopItemRev
	TopItemQty
	TopItemCommon /* (several fields re Use and Allocation) */
	TopItemRevKey /* (= [ItemName] & ":" & [ItemRev] ) */
	TopItemIDKey /* (= TopItemName ) */

   tblBOM /* structure: (Structured BOM tree of makeup of Items) */
	BOMLineNum
	ItemName
	ItemRev
	ItemRevKey /* (= [ItemName] & ":" & [ItemRev] ) */
	SubName
	SubRev
	SubRevKey /* (= [SubName] & ":" & [ItemRev] ) */
	SubQty

   tblDates /* structure: Dates for build and assembly of items */
	datesLineNum
	ItemName
	ItemRev
	ItemRevKey /* (= [ItemName] & ":" & [ItemRev] ) */
	TaskName
	TaskDate
*/

/* Query qL0 */
SELECT
	tblTop.TopItemLineNum AS TopItemLineNum,
	tblTop.TopItemName AS TopItemName,
	tblTop.TopItemRev AS TopItemRev,
	tblTop.TopItemQty AS TopItemQty,
	tblTop.TopItemRevKey AS TopItemRevKey,
	tblTop.TopItemIDKey AS TopItemIDKey,
	BOM0.SubName AS ItemName,
	BOM0.SubRev AS SubRev,
	BOM0.SubQty AS ItemRevQty,
	BOM0.SubRevKey AS ItemRevKey,
	'L0' AS Level
FROM tblTop
LEFT OUTER JOIN tblBOM AS BOM0
	ON tblTop.TopItemRevKey = BOM0.ItemRevKey;


/* Query qL1*/
SELECT
	qL0.TopItemLineNum AS TopItemLineNum,
	qL0.TopItemName AS TopItemName,
	qL0.TopItemRev AS TopItemRev,
	qL0.TopItemQty AS TopItemQty,
	qL0.TopItemRevKey AS TopItemRevKey,
	qL0.TopItemIDKey AS TopItemIDKey,
	BOM1.SubName AS ItemName,
	BOM1.SubRev AS SubRev,
	BOM1.SubQty * qL0.ItemRevQty AS ItemRevQty,
	BOM1.SubRevKey AS ItemRevKey,
	'L1' AS Level
FROM qL0
LEFT OUTER JOIN tblBOM AS BOM1
	ON qL0.ItemRevKey = BOM1.ItemRevKey;


/* Query qL2* (and repeat pattern incrementing qL1, qL2, BOM2... up to qLfinal) */
SELECT
	qL1.TopItemLineNum AS TopItemLineNum,
	qL1.TopItemName AS TopItemName,
	qL1.TopItemRev AS TopItemRev,
	qL1.TopItemQty AS TopItemQty,
	qL1.TopItemRevKey AS TopItemRevKey,
	qL1.TopItemIDKey AS TopItemIDKey,
	BOM2.SubName AS ItemName,
	BOM2.SubRev AS SubRev,
	BOM2.SubQty * qL1.ItemRevQty AS ItemRevQty,
	BOM2.SubRevKey AS ItemRevKey,
	'L2' AS Level
FROM qL1
LEFT OUTER JOIN tblBOM AS BOM2
	ON qL1.ItemRevKey = BOM2.ItemRevKey;


/* Query qDetailL1 (example of one such query per qLn: n=0 to final) */
SELECT
	qL1.*, tblTop.TopItemCommon, tblBOM.*, tblItemRevData.*, tblItemData.*, tblDates.*
FROM qL1
LEFT OUTER JOIN tblTop AS Top
	ON qL1.TopItemLineNum=Top.TopItemLineNum
LEFT OUTER JOIN tblItemRevData
	ON qL1.ItemRevKey=tblItemRevData.ItemRevKey
LEFT OUTER JOIN tblItemData
	ON qL1.ItemIDKey=tblItemData.ItemIDKey
LEFT OUTER JOIN tblDates
	ON qL1.ItemRevKey=tblDates.ItemRevKey;


/* Final Union Query */
SELECT qDetailL0.* FROM qDetailL0
UNION ALL
SELECT qDetailL1.* FROM qDetailL1
UNION ALL
...
UNION ALL
SELECT qDetailLfinal.* FROM qDetailLfinal;