5 Replies Latest reply: Mar 4, 2012 2:53 AM by Gordon Du RSS

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

Dwarakanath P
Currently Being Moderated

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.

  • Re: Project code based  Sales Order and Purchase Order - Item wise Report
    Mohammad imran
    Currently Being Moderated

    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

  • Re: Project code based  Sales Order and Purchase Order - Item wise Report
    Gordon Du
    Currently Being Moderated

    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

    • Re: Project code based  Sales Order and Purchase Order - Item wise Report
      Dwarakanath P
      Currently Being Moderated

      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

      • Re: Project code based  Sales Order and Purchase Order - Item wise Report
        Rahul Moundekar
        Currently Being Moderated

        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

      • Re: Project code based  Sales Order and Purchase Order - Item wise Report
        Gordon Du
        Currently Being Moderated

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

Actions