Skip to Content

Items Duplication Multi times in Report

Hi experts

i had created a QUERY for Invoice Print out, 
when i run the query , same item is duplicating  2 & more times 

how can i fix it

select OINV.DocEntry, OCRD.CntctPrsn AS 'Customer Contact Person',OCPR.Position AS 'Designation',OINV.CardName AS 'Customer Name',OCPR.Address as 'Address',CRD1.Street AS 'PO Box No', 
CRD1.City AS 'City',CRD1.Country as 'Country',OCRD.Fax as 'Fax',OCRD.Phone1,OCRD.Phone2,OCRD.Cellular as 'Mobile No',OCRD.E_Mail as 'Email',OCRD.IntrntSite AS 'WebSite',OINV.DocDate as 'Postng Date',
OINV.NumAtCard as 'Ref No' ,OINV.DocCur AS 'Currency',OINV.U_Creater AS 'Doc Creater/ From',INV1.LineTotal,


Case  OINV.DocType when ('S') THEN 'Service Based'
                   when ('I') Then 'Item Based'


else 'Invalid Doc Type' end AS 'Invalid Doc Type',


INV1.Dscription as 'Description',INV1.Quantity as 'Qty',INV1.U_srv_qty AS 'Service Qty',INV1.U_SerRate as'Service Unit Price',INV1.Price as 'Unit Price',OINV.DocNum as 'Doc Number', OINV.Header as 'T&C1',OINV.Footer AS'T&C2'
from OINV 
left join INV1 on OINV.DocEntry = INV1.DocEntry
LEFT JOIN OCRD ON OINV.CardCode = OCRD.CardCode
left join CRD1 on OCRD.DocEntry = OCRD.DocEntry
LEFT join OCPR on OCRD.CardCode = OCPR.CardCode


where DocNum = 100029


Regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Aug 30, 2017 at 11:06 AM

    Hi

    try this:

    change this

    left join CRD1 on OCRD.DocEntry = OCRD.DocEntry

    for this:

    inner join CRD1 on CRD1.cardcode = OCRD.cardcode

    And after this check your BP has only one contact person. If you have more than one, you have to add a new condition to filter. For example, you can select the contact person in the document.

    Kind regards

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded

    • Hi

      am having only single contact person at present for my BP

      late it may be Multi, but as of now am having only single contact persons

      in that BP also the same duplicate is happening

      Regards

  • Aug 30, 2017 at 11:09 AM

    Try Below Code:-

    
    select OINV.DocEntry, OCRD.CntctPrsn AS 'Customer Contact Person',OCPR.Position AS 'Designation',OINV.CardName AS 'Customer Name',OCPR.Address as 'Address',CRD1.Street AS 'PO Box No', 
    CRD1.City AS 'City',CRD1.Country as 'Country',OCRD.Fax as 'Fax',OCRD.Phone1,OCRD.Phone2,OCRD.Cellular as 'Mobile No',OCRD.E_Mail as 'Email',OCRD.IntrntSite AS 'WebSite',OINV.DocDate as 'Postng Date',
    OINV.NumAtCard as 'Ref No' ,OINV.DocCur AS 'Currency',
    OINV.U_Creater AS 'Doc Creater/ From',
    INV1.LineTotal,
    Case  OINV.DocType when ('S') THEN 'Service Based'
                       when ('I') Then 'Item Based'
    
    
    
    
    else 'Invalid Doc Type' end AS 'Invalid Doc Type',
    
    
    
    
    INV1.Dscription as 'Description',INV1.Quantity as 'Qty',
    INV1.U_srv_qty AS 'Service Qty',INV1.U_SerRate as'Service Unit Price',
    INV1.Price as 'Unit Price',OINV.DocNum as 'Doc Number', OINV.Header as 'T&C1',OINV.Footer AS'T&C2'
    from OINV 
    inner join INV1 on OINV.DocEntry = INV1.DocEntry
    inner JOIN OCRD ON OINV.CardCode = OCRD.CardCode
    Inner join CRD1 on OCRD.CardCode  = CRD1.CardCode and oinv.PayToCode = CRD1.Address
    LEFT join OCPR on  OINV.CntctCode = OCPR.CntctCode
    
    
    
    
    where  CRD1.AdresType ='B' and DocNum = 1718010022 
    
    Add comment
    10|10000 characters needed characters exceeded

  • Aug 30, 2017 at 04:47 PM

    Resultado de imagen para left join inner join

    Regards

    Add comment
    10|10000 characters needed characters exceeded

    • Hi P Vinod, I understand that default contact person & address are in the A/R Invoice, a example whit your code is:

      SELECT
      OINV.DocEntry, 
      OCPR.Name AS 'Customer Contact Person',
      OCPR.Position AS 'Designation',
      OINV.CardName AS 'Customer Name',
      OCPR.Address as 'Address',
      OINV.Address AS 'Full Address',
      OCRD.Fax as 'Fax',
      OCRD.Phone1,
      OCRD.Phone2,
      OCRD.Cellular as 'Mobile No',
      OCRD.E_Mail as 'Email',
      OCRD.IntrntSite AS 'WebSite',
      OINV.DocDate as 'Postng Date',
      OINV.NumAtCard as 'Ref No',
      OINV.DocCur AS 'Currency',
      OINV.U_Creater AS 'Doc Creater/ From',
      INV1.LineTotal,
      Case  OINV.DocType when ('S') THEN 'Service Based'
                         when ('I') Then 'Item Based'
      else 'Invalid Doc Type' end AS 'Invalid Doc Type',
      INV1.Dscription as 'Description',
      INV1.Quantity as 'Qty',
      INV1.U_srv_qty AS 'Service Qty',
      INV1.U_SerRate as'Service Unit Price',
      INV1.Price as 'Unit Price',
      OINV.DocNum as 'Doc Number', 
      OINV.Header as 'T&C1',
      OINV.Footer AS'T&C2'
      FROM OINV 
      LEFT join INV1 on OINV.DocEntry = INV1.DocEntry
      LEFT JOIN OCRD ON OINV.CardCode = OCRD.CardCode
      LEFT join OCPR on OINV.CntctCode = OCPR.CntctCode
      where DocNum = 100029