Project code based Sales Order and Purchase Order - Item wise Report

[Former Member]

Dear all,

Scenario:

We are doing business based on Projects. So, for every Sales , Project code is created and Sales order is booked with Project code and subsequently Purchase Order is booked with Project code. (Note:Some cases, few items might not be purchased since stock is available.)

We want report like

SO Project code

SO Posting date

SO Sales Employee

SO Customer Name

SO Item code

SO Quantity

SO Unit price

SO Row Total

PO Project code

PO Posting date

PO Sales Employee

PO Customer Name

PO Item code

PO Quantity

PO Unit price

PO Row Total

*SO line item and PO line item for same project should be parallel , that is important and challenge (certain cases, item in SO might not be in PO, for such cases Blank field should appear for PO Item code.)

Thanks in advance,

Regards,

Dwarak.

[Former Member]

Dear Dwarak,

You can try:

SELECT 'Sales Order' 'Type', T0.Project,T0.DocDate,T2.SLPName,T1.CardName,T0.ItemCode,T0.Quantity,T0.Price,T0.Linetotal

FROM RDR1 T0

JOIN ORDR T1 ON T1.DocEntry = T0.DocEntry

JOIN OSLP T2 ON T2.SLPCode = T1.SLPCode

UNION

SELECT 'Purchase Order' 'Type', T0.Project,T0.DocDate,T2.SLPName,T1.CardName,T0.ItemCode,T0.Quantity,T0.Price,T0.Linetotal

FROM POR1 T0

JOIN OPOR T1 ON T1.DocEntry = T0.DocEntry

JOIN OSLP T2 ON T2.SLPCode = T1.SLPCode

ORDER BY T0.Project,T0.ItemCode

Thanks,

Gordon

[Former Member]

Dear Gordon,

I want it at same list. Item code wise

i tried this query,

SELECT T0.[PrjCode], T1.[Project], T0.[PrjName], T1.[DocNum], T1.[DocDate], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T2.[Price], T2.[LineTotal], T3.[Project], T4.[ItemCode], T4.[Dscription], T4.[Quantity], T4.[Price], T4.[LineTotal] FROM OPRJ T0 INNER JOIN ORDR T1 ON T0.PrjCode = T1.Project INNER JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OPOR T3 ON T0.PrjCode = T3.Project LEFT OUTER JOIN POR1 T4 ON T3.DocEntry = T4.DocEntry AND T2.ITEMCODE = T4.ITEMCODE INNER JOIN OITM T5 ON T2.ItemCode = T5.ItemCode WHERE T0.[PrjCode] =[%0] AND

T5.[ItmsGrpCod] in ('108', '110') GROUP BY T0.[PrjCode], T1.[Project], T0.[PrjName], T1.[DocNum], T1.[DocDate], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T2.[Price], T2.[LineTotal], T3.[Project], T4.[ItemCode], T4.[Dscription], T4.[Quantity], T4.[Price], T4.[LineTotal]

Is this right?

Thanks in advance,

Dwarak

[Former Member]

Hi Dwarak.......

Your Report is right but a small change you have to make Left join RDR1 with POR1........

Try this.......

SELECT T0.PrjCode, T1.Project, T0.PrjName, T1.DocNum, T1.DocDate, T1.CardName, T2.ItemCode, T2.Dscription, T2.Quantity, T2.Price, T2.LineTotal, T3.Project, T4.ItemCode, T4.Dscription, T4.Quantity, T4.Price, T4.LineTotal FROM OPRJ T0 INNER JOIN ORDR T1 ON T0.PrjCode = T1.Project INNER JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry LEFT JOIN OPOR T3 ON T0.PrjCode = T3.Project LEFT OUTER JOIN POR1 T4 ON T3.DocEntry = T4.DocEntry AND T2.ITEMCODE = T4.ITEMCODE INNER JOIN OITM T5 ON T2.ItemCode = T5.ItemCode WHERE T0.PrjCode ='[%0]' AND
T5.ItmsGrpCod in ('108', '110') GROUP BY T0.PrjCode, T1.Project, T0.PrjName, T1.DocNum, T1.DocDate, T1.CardName, T2.ItemCode, T2.Dscription, T2.Quantity, T2.Price, T2.LineTotal, T3.Project, T4.ItemCode, T4.Dscription, T4.Quantity, T4.Price, T4.LineTotal

Regards,

Rahul

[Former Member]

Unless your PO has direct link to SO, this query can not run without problem.

[Former Member]

HI Dwarak.

you can get Project Code field in row level in SO and PO. First create project in project master and choose project at the time of SO creation and choose same project at the time of PO creation if any item not related to the project so you can leave field blank.

You can fin this field in form setting.

Thanks & Regard

Khan Imran