on 04-23-2010 5:57 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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] )
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
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
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.