Items Duplication Multi times in Report

[pvinod_kumar]

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

[Former Member]

Regards

[pvinod_kumar]

Sir

can you provide me the Query

Regards

[Former Member]

Sure P Vinod, but can you show me the repeated lines? Because your situation are these:

- Your BP has 2 or more contact person.

- Your BP has 2 or more adress.

What do you do in this situation? show you the first, anything or the last?

Regards.

[pvinod_kumar]

sir

if i have more then one contact person or more than one address, i need the default contact person & address to be appear in layout

Regrds

[Former Member]

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

[anand_singh]

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 

[pvinod_kumar]

Hi

when i run your query , am getting the result only for 2 document, for rest The data output, its blank

Regards

[agustin_marcoscividanes]

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

[pvinod_kumar]

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