Skip to Content
0

Help me to create report

Jan 08, 2017 at 09:47 AM

39

avatar image
Former Member

Dear Experts

I am trying to create a Report based on GRN and Serial Number

i had assigned my items in Serial Number in All transaction (IN & Out)

i need to know against a particular Vendor in a month how much items i purchase and out of that particular purchase how much i sold and how much is pending based on that Purchase

i try and can to know that serial number is must for the items , and i had created some items in serial numbers in a demo enviorment , but am not able to get the accurate date in the report

So i seek help from the experts to help me if any on has sql query format .

below attache is the Report format and the query which i had created is attached

Regards


Select Distinct OPDN.DocDate as 'Posting Date',OPDN.DocNum as 'Doc Number',OPDN.DocTotal as 'Doc Total',OPDN.U_PMOD as 'Pay Mode',PDN1.ItemCode,PDN1.Dscription,PDN1.Quantity AS 'Purchase Qty',COUNT(OSRQ.Quantity) [Net Out Qty],OSRQ.WhsCode  from OPDN
INNER join PDN1 on OPDN.DocEntry = PDN1.DocEntry 
LEFT join OSRQ on PDN1.ItemCode = OSRQ.ItemCode 
--LEFT OUTER join OSRQ on SRI1.ItemCode = OSRQ.ItemCode
where OPDN.CardCode = 'SP00000527' AND  OPDN.CANCELED = 'N' and OPDN.DocDate between '2017-01-01 00:00:00.000' and '2017-12-31 00:00:00.000' and OSRQ.WhsCode = 'JB-WH' AND OSRQ.Quantity = 0 


GROUP BY OPDN.DocDate ,OPDN.DocNum ,OPDN.DocTotal ,OPDN.U_PMOD ,PDN1.ItemCode,PDN1.Dscription,PDN1.Quantity ,OSRQ.WhsCode



report-format.png

report-format.png (12.9 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers