Problems in extracting old records in crystal report invoice template

[Former Member]

I have made the following SQL query. But for some reason, I am not able to see any invoices older than September 2011. I havent given any criteria or filters for the dates. I do know that there were loads of item invoices posted since 2009. Can anyone help me with this a.s.a.p.

My SQL Query:

SELECT DISTINCT "OINV"."DocNum", "OINV"."DocDate", "OINV"."CardCode", "INV1"."ItemCode", "INV1"."Dscription", "INV1"."Price", "INV1"."DiscPrcnt", "INV1"."Quantity", "INV1"."LineTotal", "INV1"."LineVat", "INV1"."ShipDate", "OINV"."DocTotal", "OINV"."VatSum", "INV1"."BaseRef", "OINV"."NumAtCard", "OINV"."Address", "OINV"."CardName", "OINV"."TotalExpns", "OINV"."Address2", "OINV"."DocEntry", "OINV"."GroupNum", "OINV"."ExtraDays", "OINV"."CntctCode", "OINV"."DocDueDate"
FROM   "Live"."dbo"."OINV" "OINV" INNER JOIN "Live"."dbo"."INV1" "INV1" ON ("OINV"."OwnerCode"="INV1"."OwnerCode") AND ("OINV"."DocEntry"="INV1"."DocEntry")
ORDER BY "OINV"."DocNum"

Thank you very much in advance for looking into this for me.

R

[former_member188911 · Accepted solution]

there is no WHERE condition which makes me think that either one or both tables don't have any records for years 2009/2010

have you checked the raw data? 

thanks

[Former Member · Accepted solution]

Hi Simone,

Thanks for ur reply.

Raw data as in transactions itself. Yes, I did see lots of invoice transactions in SAP since 2009. So, I was thinking that it should pull all item invoices since 2009. But my suspiscion is the closed periods. But again, I will have to check.

Thanks

Raahat

[Former Member · Accepted solution]

Hi Simone,

It looks like when we upgraded our SAP system to integrate with Accellos, we transferred all the stock items at the end of last year. The invoice records since the end of last year appears in this layout. But not the ones prior to it.. So I am not sure what might have caused this error.

Thanks

Raahat

[former_member188911 · Accepted solution]

so you are telling us that the problem was in the database, correct?

thanks

[Former Member · Accepted solution]

Yes, probably. As for some reason, this layout is working on invoices raised after 01st Dec 2011 after the upgrade. So, I have highlighted this issue to our SAP maintenance company. So, I am still awaiting for my response.

[Former Member · Accepted solution]

Hi Simone,

I have managed to resolve it as the OINV table was linked to INV1 with additional field links, which were quite unneccessary.. And I now tried linking OINV and INV1 with just two links  1  DocEntry 2. CardCode to BaseCard which has now resolved the issue.

Thanks

Raahat

[former_member188911 · Accepted solution]

thank you for sharing the solution!