Reporte de Factura de Proveedor vs Orden de Compra

[Former Member]

Buenos Días Consultores

Acudo a usted, se me solicito efectuar un reporte donde muestre lo siguiente :

OPOR	OPOR	OPOR	OPCH	OPCH	OPCH
T0.[DocNum]	T0.[NumAtCard]	T0.[DocDate]	T0.[DocNum]	T0.[NumAtCard]	T0.[DocDate]
Doc. de SAP	Referencia	Fecha	Fact. SAP	Referencia	Fecha

Necesito vincular todas las ordenes de compra que vinculen la factura de proveedor, investigando en el portal hay query que modifique pero solo me recupera cuando tiene documento de destino Entradas de Mercancías.

SELECT DISTINCT t2.cardcode as codigo, t2.cardname as Proveedor,t2.DocNum AS Orden_de_compra, t2.doctotal as Total_Orden, t2.DocDate as 'Fecha_orden', t4.DocNum AS No_Entrega, t4.doctotal as totalentrega, t4.DocDate AS Fecha_Entrega, t5.DocNum AS No_Fact, t5.doctotal as Total_fact, t5.DocDate as Fecha_Fact, t0.DocNum AS Nota_credito 

                                                               FROM ORPC t0 INNER JOIN 

                      RPC1 t1 ON t0.DocEntry = t1.DocEntry RIGHT OUTER JOIN 

                      OPOR t2 LEFT OUTER JOIN 

                      PDN1 t3 INNER JOIN 

                      OPDN t4 ON t3.DocEntry = t4.DocEntry INNER JOIN 

                      OPCH t5 INNER JOIN 

                      PCH1 t6 ON t5.DocEntry = t6.DocEntry ON t4.DocEntry = t6.BaseEntry ON t2.DocEntry = t3.BaseEntry ON 

                      t1.BaseEntry = t5.DocNum 

where 

t5.isins='N' and 

t2.DocDate>= [%0] and t2.DocDate<= [%1]

Agradezco de su apoyo para elaborar reporte.

Saludos.

Carlos Ortega.

[Former Member · Accepted solution]

Que tal Carlos, soy nuevo en este foro, espero este query sea lo que necesitas:

-- TABLA "RELACIÓN DE FACTURAS - ÓRDENES DE COMPRA

DECLARE @FACT AS TABLE(DocNum  INT, NumAtCard VARCHAR(100), DocDate DATE, BaseEntry INT, BaseType INT)

-- VISTA "FACTURAS - ÓRDENES DE COMPRA"

INSERT INTO @FACT

-- FACTURA - ÓRDEN DE COMPRA

SELECT DISTINCT T1.DocNum, T1.NumAtCard, T1.DocDate, T0.BaseEntry, T0.BaseType

FROM PCH1 T0

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

WHERE T0.BaseType = 22

UNION ALL

-- FACTURA - ENTRADA - ÓRDEN DE COMPRA

SELECT DISTINCT T1.DocNum, T1.NumAtCard, T1.DocDate, T2.BaseEntry, T2.BaseType

FROM PCH1 T0

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

INNER JOIN PDN1 T2 ON T0.BaseEntry = T2.DocEntry AND T0.BaseType = T2.ObjType

WHERE T2.BaseType = 22

-- VISTA PRINCIPAL

SELECT DISTINCT

-- DATOS DE ÓRDENES DE COMPRA

T0.DocNum [Doc SAP] ,T0.NumAtCard [Referencia] ,T0.DocDate [Fecha],

-- DATOS DE FACTURAS DE PROVEEDOR

T1.DocNum [Fact. SAP], T1.NumAtCard [Referencia], T1.DocDate [Fecha]

FROM OPOR T0

LEFT JOIN @FACT T1 ON T0.DocEntry = T1.BaseEntry AND T0.ObjType = T1.BaseType

WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1]

ORDER BY 1

Saludos,

[Former Member · Accepted solution]

Gracias Carlos

He corrido el query enviado me manda error.

Mi skype es ortega.carlos76 si podemos verlo de tal formar correrlo bien para compartirlo con los colegas del forum.

Saludos

[Former Member · Accepted solution]

Gracias por el dato, hace tiempo que no hago reportes para ejecutarlos desde el Query Manager , prueba de esta manera:

-- VISTA PRINCIPAL
SELECT DISTINCT
T0.DocNum [Doc SAP] ,T0.NumAtCard [Referencia] ,T0.DocDate [Fecha],
T1.DocNum [Fact. SAP], T1.NumAtCard [Referencia], T1.DocDate [Fecha]
FROM OPOR T0
LEFT JOIN
(
-- VISTA "FACTURA - ÓRDEN DE COMPRA
SELECT DISTINCT T1.DocNum, T1.NumAtCard, T1.DocDate, T0.BaseEntry, T0.BaseType
FROM PCH1 T0
INNER JOIN OPCH T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.BaseType = 22
UNION ALL
-- VISTA "FACTURA - ENTRADA - ÓRDEN DE COMPRA
SELECT DISTINCT T1.DocNum, T1.NumAtCard, T1.DocDate, T2.BaseEntry, T2.BaseType
FROM PCH1 T0
INNER JOIN OPCH T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN PDN1 T2 ON T0.BaseEntry = T2.DocEntry AND T0.BaseType = T2.ObjType
WHERE T2.BaseType = 22
) T1
ON T0.DocEntry = T1.BaseEntry AND T0.ObjType = T1.BaseType
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1]
ORDER BY 1

Espero sea de utilidad.

Saludos,

[Former Member · Accepted solution]

Gracias me funciono correctamente.

Saludos