Capas Materiales OF

[armandomuozsule]

Buena tarde expertos , solicito los buenos oficios de alguien que me pueda ayudar con un query que amarre las capas de los materiales en una orden de fabricacion.

De antemano muchas gracias

saludos cordiales

[Former Member · Accepted solution]

Buen Dia Estimado Armando,
te comparto el Query que amarra las capas de materiales:

SELECT

    OB.DocNum,

    CONVERT(VARCHAR(50),OB.DueDate,103) Fecha,

    OB.ItemCode,

    A.ItemName,

    OB.PlannedQty,

    B.Code Code1,

    B.ItemName Item1,

    (B.Quantity * OB.PlannedQty) ToPlanned1,

    C.Code Code2,

    C.ItemName Item2,

    ((B.Quantity * OB.PlannedQty) * C.Quantity) ToPlanned2,

    D.Code Code3,

    D.ItemName Item3,

    (((B.Quantity * OB.PlannedQty) * C.Quantity) * D.Quantity) ToPlanned3,

    E.Code Code3,

    E.ItemName Item3,

    ((((B.Quantity * OB.PlannedQty) * C.Quantity) * D.Quantity) * E.Quantity) ToPlanned4,

    F.Code Code3,

    F.ItemName Item3,

    (((((B.Quantity * OB.PlannedQty) * C.Quantity) * D.Quantity) * E.Quantity) * F.Quantity) ToPlanned5

FROM (

    SELECT *

    FROM OWOR T0

    WHERE T0.DocNum = '' --Aqui va el Numero de Orden que necesitas

    ) AS OB

    INNER JOIN OITM A ON A.ItemCode = OB.itemcode

    INNER JOIN (

            SELECT T0.[Code] item, T1.[ItemName] name, T2.[Code],

            T3.[ItemName], T2.[Quantity], T2.[Warehouse]

            FROM OITT T0

            INNER JOIN OITM T1 ON T0.Code = T1.ItemCode

            INNER JOIN ITT1 T2 ON T0.Code = T2.Father

            INNER JOIN OITM T3 ON T2.Code = T3.ItemCode

            WHERE T2.[Code] NOT IN ('GIF','MO')

    ) B ON B.item = OB.itemcode

    LEFT JOIN (

            SELECT T0.[Code] item, T1.[ItemName] name, T2.[Code],

            T3.[ItemName], T2.[Quantity], T2.[Warehouse]

            FROM OITT T0

            INNER JOIN OITM T1 ON T0.Code = T1.ItemCode

            INNER JOIN ITT1 T2 ON T0.Code = T2.Father

            INNER JOIN OITM T3 ON T2.Code = T3.ItemCode

            WHERE T2.[Code] NOT IN ('GIF','MO')

    ) C ON B.Code = C.item

    LEFT JOIN (

            SELECT T0.[Code] item, T1.[ItemName] name, T2.[Code],

            T3.[ItemName], T2.[Quantity], T2.[Warehouse]

            FROM OITT T0

            INNER JOIN OITM T1 ON T0.Code = T1.ItemCode

            INNER JOIN ITT1 T2 ON T0.Code = T2.Father

            INNER JOIN OITM T3 ON T2.Code = T3.ItemCode

            WHERE T2.[Code] NOT IN ('GIF','MO')

    ) D ON C.Code = D.Item

    LEFT JOIN (

            SELECT T0.[Code] item, T1.[ItemName] name, T2.[Code],

            T3.[ItemName], T2.[Quantity], T2.[Warehouse]

            FROM OITT T0

            INNER JOIN OITM T1 ON T0.Code = T1.ItemCode

            INNER JOIN ITT1 T2 ON T0.Code = T2.Father

            INNER JOIN OITM T3 ON T2.Code = T3.ItemCode

            WHERE T2.[Code] NOT IN ('GIF','MO')

    ) E ON D.Code = E.Item

    LEFT JOIN (

            SELECT T0.[Code] item, T1.[ItemName] name, T2.[Code],

            T3.[ItemName], T2.[Quantity], T2.[Warehouse]

            FROM OITT T0

            INNER JOIN OITM T1 ON T0.Code = T1.ItemCode

            INNER JOIN ITT1 T2 ON T0.Code = T2.Father

            INNER JOIN OITM T3 ON T2.Code = T3.ItemCode

            WHERE T2.[Code] NOT IN ('GIF','MO')

    ) F ON E.Code = F.Item

    WHERE B.Code IS NOT NULL

    GROUP BY

	OB.DocNum,
	OB.DueDate,
	OB.ItemCode,
	A.ItemName,
	OB.PlannedQty,
	B.Code,
	B.ItemName,
	C.Code,
	C.ItemName,
	D.Code,
	D.ItemName,
	E.Code,
	E.ItemName,
	F.Code,
	F.ItemName,
	B.Quantity,
	C.Quantity,
	D.Quantity,
	E.Quantity,
	F.Quantity

---------------------------------------------------------------------------------------

Saludos!!

[armandomuozsule · Accepted solution]

Gracias Mr Query!

Saludos Cordiales