Skip to Content
0

Query for Lnaded Costs linked with AP invoices

Nov 22, 2016 at 11:21 PM

64

avatar image
Former Member

Hi All,

I have built this query for a client. They have been working with it for a long time, however, they are asking that the Landed Costs, Fixed costs be added to this. I have tried linking the OALC AlcName and also the IPF1 tables with no success. Can you link those tables to this query. I am stumped on how to do this. Any assistance would be appreciated.

SELECT
T0.[CardCode] AS 'Vendor Code',
T0.[CardName] AS 'Vendor Name',
T0.[DocDate],
T0.[DocNum] AS 'Invoice #',
T1.[BaseDocNum] AS 'Based on GRPO',
T2.[BaseDocNum] AS 'Based on PO',
sum(T1.[Quantity]) AS 'Total # Pieces',
T0.[Weight],
T0.[TotalExpns] AS 'Freight Cost',
T0.[DocTotal] AS 'Invoice Total'
FROM OPCH T0
INNER JOIN PCH1 T1 ON T0.DocNum = T1.DocEntry
INNER JOIN PDN1 T2 ON T1.BaseDocNum = T2.DocEntry
WHERE T0.[CardCode] = '[%0]' AND
(T0.[DocDate] >= [%1] AND T0.[DocDate] <= [%2])
GROUP BY
T0.[DocNum],
T0.[CardCode],
T0.[CardName],
T0.[DocDate],
T1.[BaseDocNum],
T2.[BaseDocNum],
T0.[Weight],
T0.[DocTotal],
T0.[TotalExpns]

Thank You,

Chris Sorensen

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers