Issue with Crystal Report

[Former Member]

Hi All,

I have a crystal report created for AR Invoice using query and not tables.

At times (random), the system does not pull the base document numbers (Delivery and Sales Order) in crystal report whereas if I run the command in SQL, I can see the data.

How can this issue be fixed?

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi Joseph,

Is it standalone AR invoice?

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

No, standalone alone documents would not have base documents!!

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi Joseph,

Yes i know that. What i am trying to say that, if standalone invoice means, may the above query will not pick base document from sales or delivery.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

No problem!

In my original post, I have mentioned that its a random issue and I can see the data when i run the query in SQL but data is not visible in crystal report.

Thanks,

Joseph

[Former Member]

Hi Joseph,

No it is not possible to share my emailid in SCN.

If You ready to share your screen then please share your ID and Passcode of TV & Ammy here.

[Former Member]

Hi Ambesh,

I dont have TV or AMMY, I can start a webex session.

My email id is mentioned in my profile, if you can give your email, I can share the webex link with you.

Thanks,

Joseph

[Former Member]

Hi Joseph,

You can share WbexLink here...

[Former Member]

Hi Joseph,

Could you please share your Screen throgh Ammy or TV...

[Former Member]

Hi Ambesh,

Can you share your email address?

Thanks,

Joseph

[Former Member]

Hi Joseph,

It is possible related to the query for this command. Could you post it here?

Thanks,

Gordon

[Former Member]

Hi Gordon,

Sure, the query is mentioned below:

SELECT OINV.DocEntry, concat(NNM1.BeginStr, RIGHT(OINV.DocNum, 6)) as 'INV Doc Num', OINV.DocDate, OINV.DocTime, OINV.CardCode, OINV.NumAtCard, OINV.CardName, OINV.NumAtCard, OINV.Address,

               OINV.Address2, OINV.DocTotal, INV1.ItemCode, INV1.Dscription, INV1.Quantity, OINV.U_SchemeName, OINV.U_DiscountName, OINV.U_DocNumber, OINV.U_LRNo, OINV.U_TransportName, INV1.U_FOC,

               INV1.PriceBefDi, INV1.Price, OINV.U_SubsidyAmt,

               INV1.Currency, INV1.Weight1, INV1.Rate, INV1.DiscPrcnt, OINV.RoundDif, OLCT.LstVatNo, OLCT.PanNo, OLCT.CstNo, OWHS.Street, owhs.streetno, OWHS.Block, OWHS.ZipCode, OWHS.City, OWHS.County,

               OWHS.Country, OWHS.State, OWHS.U_ContactName, OWHS.U_WhseTel,

               OWHS.Building, OLCT.PanNo, INV1.LineTotal, INV1.WhsCode, INV1.FreeTxt, INV1.LineVat, INV1.unitMsr, INV1.U_SDiscA, OINV.U_DiscountP, OINV.DiscPrcnt AS Expr1, OINV.DiscSum,

               (Select SUM(TaxRate) FROM INV4 WHERE DocEntry=OINV.DocEntry AND LineNum=INV1.LineNum AND INV4.StaType Between 1 and 4) AS Expr2, (Select SUM(BaseSum) FROM INV4 WHERE DocEntry=OINV.DocEntry) [BaseSum],

               (Select SUM(TaxSum) FROM INV4 WHERE DocEntry=OINV.DocEntry) [TaxSum],OCTG.PymntGroup,

               OINV.U_AmtInWords, OINV.U_TaxAmtInWords, OINV.U_TermsOfDel , OINV.U_GrossWeight,

               (Select Max(U_VAT) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='S' AND Address=OINV.ShipToCode) [U_VAT],

               (Select Max(U_TAN) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='S' AND Address=OINV.ShipToCode) [U_TAN],

               (Select Max(U_PAN) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='S' AND Address=OINV.ShipToCode) [U_PAN],

               (Select Max(U_CST) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='S' AND Address=OINV.ShipToCode) [U_CST],

               (Select Max(U_ContaPerso) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='S' AND Address=OINV.ShipToCode) [U_ContaPersoSHIP],

               (Select Max(U_ContaPerso) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='B' AND Address=OINV.ShipToCode) [U_ContaPersoBILL],

               (Select Max(U_ContaPerTel) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='S' AND Address=OINV.ShipToCode) [U_ContaPerTelSHIP],

               (Select Max(U_ContaPerTel) FROM CRD1 WHERE CardCode=OINV.CardCode AND AdresType='B' AND Address=OINV.ShipToCode) [U_ContaPerTelBILL],

               (Select Max(T2.PriceBefDi*T2.Quantity) FROM INV1 T2 WHERE T2.linenum=INV1.linenum AND T2.DocEntry=INV1.DocEntry AND  inv1.price <> '0.00') AS 'GrossPrice',

               OSLP.SlpName, OCPR.Name, OCPR.Cellolar, ItmsGrpNam, OINV.Footer, OINV.header,

              T1.BeginStr+' '+CONVERT(Varchar,RIGHT(ODLN.DocNum, 6)) [Delivery],

               (ODLN.DocDate) [Delivery Date],

               (SELECT BeginStr+' '+(CONVERT(Varchar,RIGHT(ORDR.DocNum, 6))) FROM ORDR left JOIN RDR1 ON RDR1.DocEntry=ORDR.DocEntry left JOIN NNM1 ON NNM1.Series=ORDR.Series

               WHERE RDR1.DocEntry=DLN1.BaseEntry AND RDR1.ObjType=DLN1.BaseType AND RDR1.LineNum=DLN1.BaseLine) [Order No],

               (SELECT ORDR.DocDate FROM ORDR left JOIN RDR1 ON RDR1.DocEntry=ORDR.DocEntry left JOIN NNM1 ON NNM1.Series=ORDR.Series

               WHERE RDR1.DocEntry=DLN1.BaseEntry AND RDR1.ObjType=DLN1.BaseType AND RDR1.LineNum=DLN1.BaseLine) [Order Date],

               OCST.Name [State1], OCRY.Name [Country1],

               OINV.U_BankName, OINV.U_Branch, OINV.U_AcctNo, OINV.U_IFSCode

               FROM  OINV left JOIN

               INV1 ON OINV.DocEntry = INV1.DocEntry left JOIN

               INV12 ON OINV.DocEntry = INV12.DocEntry left JOIN

               OWHS ON INV1.WhsCode = OWHS.WhsCode left JOIN

               OLCT ON INV1.LocCode = OLCT.Code left JOIN

               NNM1 ON OINV.Series = NNM1.Series left JOIN

               OCTG ON OINV.GroupNum=OCTG.GroupNum left JOIN

               OSLP ON OINV.SlpCode=OSLP.SlpCode left JOIN

               OCPR ON OINV.CntctCode=OCPR.CntctCode left JOIN

               OITM ON INV1.ItemCode=OITM.ItemCode left JOIN

               OCST ON OLCT.State=OCST.Code AND OLCT.Country=OCST.Country left JOIN

               OCRY ON OLCT.Country=OCRY.Code left JOIN

               OITB ON OITM.ItmsGrpCod=OITB.ItmsGrpCod LEFT OUTER JOIN

               DLN1 ON INV1.BaseEntry=DLN1.DocEntry AND INV1.BaseType=DLN1.ObjType AND INV1.BaseLine=DLN1.LineNum LEFT OUTER JOIN

               ODLN ON DLN1.DocEntry=ODLN.DocEntry LEFT OUTER JOIN

               NNM1 T1 ON ODLN.Series=T1.Series

               WHERE UPPER(ItmsGrpNam) NOT Like 'COUPON%'

Thanks,

Joseph