on 09-11-2015 6:18 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
y si multiplicas la cantidad realizada por el costo(basado en la emision de produccion), te debe de dar ese dato.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.