cancel
Showing results for 
Search instead for 
Did you mean: 

Query

Former Member
0 Kudos

Hi All,

I am trying to get a report generated through query which should give the following details;

Document number, Customer name, item, quantity, price, item cost, cogs value, sale value

the purpose of this query is to get the figures which would match with the GL and Trial balance. Also would it be possible to include the JE's (manual) in the same report.

The reports that I have created is showing differences and also have created doifferent reports to get the desired result. please assist if these queries could be combined.

COGS VALUE:

SELECT T0.[DocNum], T0.[DocDate],T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[StockPrice], T1.[StockPrice]*T1.[Quantity] as 'COGS ENTRY', T1.[Linetotal] as 'Sale VAlue' FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <= [%1]

this query does not consider the discounts and returns or credit memo's or the JE's. How can the desired data be achieved in one report? Please assist.

Thanks,

Joseph

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

HI

You are pulling data only from OINV table, which is Invoice Table. Rather pull the data from OINM table, which stores all the transactions of the items

Former Member
0 Kudos

Hi Sridharan,

Could you please assist with start for the query.

Thanks,

Joseph

Former Member
0 Kudos

Hi Sridharan,

Could you please assist with start for the query.

I created the following but this does not help.

SELECT T0.[TransNum], T0.[CreatedBy], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[Ref1], T0.[Ref2], T0.[Comments], T0.[JrnlMemo], T0.[ItemCode], T0.[Dscription], T0.[OutQty], T0.[Price], T0.[Rate], T0.[CalcPrice], T0.[StockAct], T0.[CostAct], T0.[CogsVal] FROM OINM T0 WHERE T0.[TransType] = 13 AND T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]

Thanks,

Joseph

Former Member
0 Kudos

Start with this


SELECT T0.[TransNum], T0.[TransType], T0.[DocDate], T0.[CardCode], T0.[CardName], 
Case  when T0.[TransType] = 15 then 'Delivery'
        when T0.[TransType] = 13 then 'AR Invoice'
        when T0.[TransType] = 16 then 'Sales Returns'
        when T0.[TransType] = 14 then 'A/R Credit Note'  
END, 
T0.[ItemCode], T0.[Price] as 'Unit Price After Discount', T0.[INQty] as 'Qty Recived',T0.[OutQty] as 'Qty Trans', T0.[CogsVal] FROM OINM T0 where T0.[TransType] in (15,13,14,16) and  (T0.[DocDate]  >= [%0] AND T0.[DocDate]  <= [%1] )

Former Member
0 Kudos

Hi Sri,

I have made 5 query reports ;

1. Cogs and Local sale value based on invoice

2. Discount

3. Manual JE for Cogs

4. Manual JE for Sale

5. Cogs and Sale based on credit memo

After exporting to excel, doin calculations manually for the time being. Will work on the report provided by you too..

Thanks,

Joseph