Buenas el siguiente código me permite exportar en Columnas las órdenes de compra, con su respectivas entrada de mercancía y Factura de proveedores.
OPOR-OPDN-OPCH
Pero cuando una Orden de compra pasa directamente a factura, sin pasar por una entrada de mercancía, no me muestra el numero de factura cuando exporto de crystal a excel.
Esto a que se debe. Los datos de la factura solo aparecen cuando existe una entrada.
Gracias de antemano ante cualquier consejo, o respuesta.
SELECT
T2.DocNum AS ‘Purch Ord’,
T2.Cardname AS ‘Nombre’,
T2.Comments AS ‘Comentario’,
T2.Doctotal AS 'TotalOPOR ‘,
T2.DocDate AS ’ Ord Date’,
T2.CardCode AS ‘Vend Num’,
T1.ItemCode AS ‘Product’,
T1.LineStatus AS ‘Row Stat’,
T2.Canceled AS ‘Cancelled?’,
T1.LineNum+1 AS ‘Line’,
T1.Quantity AS ‘Quantity’,
T1.OpenQty AS ‘Open Qty’,
T4.DocNum AS ‘Goods Rcpt’,
T4.DocDueDate AS ‘Rcpt Date’,
T3.LineStatus AS ‘Row Stat’,
T4.Canceled AS ‘Cancelled?’,
T3.LineNum + 1 AS ‘Line’,
T3.Quantity AS ‘Quantity’,
T6.DocNum AS ‘Return’,
T5.LineNum +1 AS ‘Line’,
T5.LineStatus AS ‘Row Stat’,
T6.Canceled AS ‘Cancelled?’,
T5.Quantity AS ‘Quantity’,
T8.DocNum AS ‘AP Invoice’,
T8.NumAtCard AS ‘Ref Dany’,
T7.LineNum + 1 AS ‘Line’,
T7.Quantity AS ‘Quantity’,
T10.DocNum AS ‘AP Cred Memo’,
T9.LineNum + 1 AS ‘Line’,
T9.Quantity AS ‘Quantity’
FROM POR1 T1
LEFT OUTER JOIN OPOR T2
ON T1.DocEntry = T2.DocEntry
LEFT OUTER JOIN PDN1 T3
ON T2.DocEntry = T3.BaseEntry
AND T1.LineNum = T3.BaseLine
AND T1.ItemCode = T3.ItemCode
LEFT OUTER JOIN OPDN T4
ON T3.DocEntry = T4.DocEntry
LEFT OUTER JOIN RPD1 T5
ON T4.DocEntry = T5.BaseEntry
AND T3.LineNum = T5.BaseLine
AND T3.ItemCode = T5.ItemCode
LEFT OUTER JOIN ORPD T6
ON T5.DocEntry = T6.DocEntry
LEFT OUTER JOIN PCH1 T7
ON T4.DocEntry = T7.BaseEntry
AND T3.LineNum = T7.BaseLine
AND T3.ItemCode = T7.ItemCode
LEFT OUTER JOIN OPCH T8
ON T7.DocEntry = T8.DocEntry
LEFT OUTER JOIN RPC1 T9
ON T8.DocEntry = T9.BaseEntry
AND T7.LineNum = T9.BaseLine
AND T7.ItemCode = T9.ItemCode
LEFT OUTER JOIN ORPC T10
ON T9.DocEntry = T10.DocEntry
ORDER BY
T2.DocNum,
T1.LineNum,
T4.DocNum,
T3.LineNum,
T6.DocNum,
T5.LineNum,
T8.DocNum,
T7.LineNum,
T10.DocNum,
T9.LineNum