Sumar Variables en un Query

[Former Member]

Buen dia tengo que realizar un Query para sacar un resumen de Ordenes de fabricación, por costo acumulado, ya tengo toda la información, lo que no logro sacar es el costo, como consulto las variables desde el query.?

este es el Query como hasta el momento lo tengo

SELECT T0."DocEntry",

CASE

    WHEN  T0."Series" = 90 THEN '15'

    WHEN  T0."Series" = 91 THEN 'S15'

END AS "Serie" ,

CAST(SUBSTRING(T0."DocNum",2) AS VARCHAR) AS "No.", 

CASE

    WHEN T0."Status" = 'P' THEN 'Planificada'

    WHEN T0."Status" = 'R' THEN 'Liberada'

    WHEN T0."Status" = 'L' THEN 'Cerrada'

    WHEN T0."Status" = 'C' THEN 'Cancelada'

END AS "Proceso",

T0."CardCode",

T3."CardName",

T0."ItemCode" AS "Modelo",

T2."ItemName" as "Descripcion"

FROM

OWOR T0  INNER JOIN WOR1 T1 ON T0."DocEntry" = T1."DocEntry"

                 INNER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"

                 INNER JOIN OCRD T3 ON T0."CardCode" = T3."CardCode"

WHERE

     (T0."Status" = [%0] or [%0] = '' )

     and T1."U_Serie_PT" <> ''

     and T0."U_u_Fecha" >=[%1]

     and  T0."U_u_Fecha" <=[%2]

ORDER BY T0."DocNum",T0."DocEntry",T0."Series",T0."Status",T0."CardCode",T0."ItemCode",T2."ItemName"

Gracias por la ayuda.

Saludos

[former_member227744]

Mario:

Acá te adjunto una query (crear vista asociada) que espero te sirva.

Saludos,

Manuel Díaz G.

-----------Create view-----

---Vista

CREATE View SI_OF_Val As

SELECT T0.DocEntry 'DocEntry',T8.name as 'Tipo Material',T9.Name as 'Subgrupo Material',T0.DocNum As 'Número de Orden de Fabricación',

(Case T0.Status When 'P' Then 'Planif.' When 'R' Then 'Liberado' When 'L' Then 'Cerrado' When 'C' Then 'Cancelado' End) Estado,

T0.ItemCode as 'Código Artículo',T1.ItemName As 'Nombre Artículo',

T0.PostDate as 'Fecha Creación',

(Case T0.OriginType When 'M' Then 'Manual' When 'R' Then 'MRP' When 'S' Then 'Pedido cliente' When 'U' Then 'Upgrade' End) Origen,

T0.PlannedQty as 'Cantidad Planificada',

T0.CmpltQty As 'Cantidad Terminada',

(T0.PlannedQty - T0.CmpltQty - T0.RjctQty) As 'Cantidad Pendiente',

T0.RjctQty As 'Cantidad Rechazada',

T1.InvntryUom as 'Unidad de Medida',

(T0.CmpltQty / T0.PlannedQty * 100) As 'por Ingreso',

'Costos Materia Prima' = (Case When (SELECT sum(round ((T5.OutQty -T5.InQty)* T5.CalcPrice, 0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'C') is Null Then 0 Else (SELECT sum(round ((T5.OutQty -T5.InQty)* T5.CalcPrice, 0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'C') End),

'Costos Indirectos de Fabricación' = (Case When (SELECT sum(round(T5.CompTotal, 0)) FROM WOR1 T5 WHERE T5.DocEntry = T0.DocNum and T5.IssueType = 'B') is Null Then 0 Else (SELECT sum(round(T5.CompTotal, 0)) FROM WOR1 T5 WHERE T5.DocEntry = T0.DocNum and T5.IssueType = 'B') End),

'Desviación' = (select sum(Round((T8.PlannedQty - T8.Issuedqty)* T1.AvgPrice,0)) FROM WOR1 T8  INNER JOIN OITM T1 on T1.ItemCode = T8.ItemCode where T0.DocEntry = T8.DocEntry),

'Valor Producto Terminado' = (Case When (SELECT sum(round( T5.InQty * T5.CalcPrice,0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'P') is Null Then 0 Else (SELECT sum(round( T5.InQty * T5.CalcPrice,0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'P') End),

'Valor en Proceso' = ((Case When (SELECT sum(round(T5.CompTotal, 0)) FROM WOR1 T5 WHERE T5.DocEntry = T0.DocNum and T5.IssueType = 'B') is Null Then 0 Else (SELECT sum(round(T5.CompTotal, 0)) FROM WOR1 T5 WHERE T5.DocEntry = T0.DocNum and T5.IssueType = 'B') End)

                   + (Case When (SELECT sum(round( (T5.OutQty -T5.InQty)* T5.CalcPrice, 0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'C') is Null Then 0 Else (SELECT sum(round( (T5.OutQty -T5.InQty)* T5.CalcPrice, 0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'C') End)

                   - (Case When (SELECT sum(round( T5.InQty * T5.CalcPrice,0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'P') is Null Then 0 Else (SELECT sum(round( T5.InQty * T5.CalcPrice,0)) FROM OINM T5 WHERE T5.ApplObj = 202 AND T5.AppObjAbs = T0.DocNum AND T5.AppObjType = 'P') End))

From OWOR T0

LEFT JOIN OCRD T3 On T0.CardCode = T3.CardCode

INNER JOIN OITM T1 On T0.ItemCode = T1.ItemCode

INNER JOIN OITB T4 On T1.ItmsGrpCod = T4.ItmsGrpCod

INNER JOIN OUSR T2 On T0.UserSign = T2.UserId

LEFT JOIN OUSR T6 ON T0.UserSign = T6.Internal_k

---Query a ejecutar ----

SELECT  T4.PrcName, T0.[Emisor Orden de Fabricación], T0.[Número de Orden de Fabricación], T0.[Estado], T0.[Código Artículo], T0.[Nombre Artículo], T0.[Fecha Creación], T0.[Cantidad Planificada], T0.[Cantidad Terminada], T0.[Cantidad Pendiente], T0.[Cantidad Rechazada], T0.[Unidad de Medida], T0.[por Ingreso], T0.[Costos Materia Prima], T0.[Costos Indirectos de Fabricación], T0.[Desviación]

From SI_OF_VAL T0

INNER JOIN OWOR T3 ON T0.[DocEntry]=T3.DocEntry

LEFT JOIN OPRC T4 ON T4.PrcCode = T0.[Centro de Costo]

WHERE T3.PostDate between [%3] and [%4]

----Query a ejecutar----

SELECT

T0.DocNum, T0.PostDate, T0.ItemCode, T3.ItemName, T0.PlannedQty, T0.CmpltQty, T1.LineNum, T1.ItemCode, T4.ItemName, T1.PlannedQty, T1.IssuedQty, T2.Price

FROM

OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry=T1.DocEntry

LEFT JOIN IGE1 T2 ON T2.BaseEntry = T0.DocEntry AND T2.ItemCode = T1.ItemCode AND T2.BaseLine = T1.LineNum

LEFT JOIN OITM T3 ON T3.ItemCode = T0.ItemCode

LEFT JOIN OITM T4 ON T4.ItemCode = T1.ItemCode

WHERE

T0.PostDate BETWEEN '[%0]' AND '[%1]'

ORDER

BY T0.DocNum, T1.LineNum

[felipe_loyolarodriguez]

El costo del componente real, es un campo calculado.

Te sugiero generar un Trace (sql profiler) para mirar la query que sap ejecuta al momento de llamar una OF.

Saludos

[Former Member]

y si multiplicas la cantidad realizada por el costo(basado en la emision de produccion), te debe de dar ese dato.

Saludos