SQL Query for full paid invoices and payed out credit notes

[Former Member]

Hi alltogether,

I have some problems with a sql query.

I want to export all closed (full paid) invoices and credit notes in a table with the paid date.

The problem is that I have several internal reconciliations for one invoice or credit note.

So with my current query I get all these dates in the result but only for invoices, not for credit notes.

I only need the last internal reconciliation date as payment date for all invoices and credit notes where the open amount is 0.

In SAP B1 the finance team has service invoices and credit notes imported out of an ERP system.

These documents are marked with an 'Y" in the coloumn "U_I_Imported" in the table "OINV" or "ORIN".

SELECT

OINV.DocTotal - OINV.PaidToDate as Offen, OINV.NumAtCard, OINV.U_I_Imported, ORCT.DocDate as 'Zahlungsdatum'

SELECT

OINV.DocTotal - OINV.PaidToDate as Offen, OINV.NumAtCard, OINV.U_I_Imported, ORCT.DocDate as 'Zahlungsdatum'

FROM

OJDT inner join

ORCT on OJDT.BaseRef = ORCT.DocNum inner join

RCT2 on ORCT.DocNum = RCT2.DocNum inner join

OINV on RCT2.BaseAbs = OINV.DocEntry

where

OINV.DocTotal - OINV.PaidToDate = 0 and  OINV.U_I_Imported = 'Y'

group by

OINV.NumatCard, OINV.DocTotal - OINV.PaidToDate, OINV.U_I_Imported, ORCT.DocDate

order by

ORCT.DocDate

I hope you can help me.

Kind regards,

Max

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT

OINV.DocTotal - OINV.PaidToDate as Offen, OINV.NumAtCard,  max(ORCT.DocDate) as 'Zahlungsdatum'

FROM

OJDT inner join

ORCT on OJDT.BaseRef = ORCT.DocNum inner join

RCT2 on ORCT.DocNum = RCT2.DocNum inner join

OINV on RCT2.BaseAbs = OINV.DocEntry

where

OINV.DocTotal - OINV.PaidToDate = 0

group by

OINV.NumatCard, OINV.DocTotal - OINV.PaidToDate

union all

SELECT

ORIN.DocTotal - ORIN.PaidToDate as Offen, ORIN.NumAtCard,  max(ORIN.DocDate) as 'Zahlungsdatum'

FROM

OJDT inner join

ORCT on OJDT.BaseRef = ORCT.DocNum inner join

RCT2 on ORCT.DocNum = RCT2.DocNum inner join

ORIN on RCT2.BaseAbs = ORIN.DocEntry

where

ORIN.DocTotal - ORIN.PaidToDate = 0

group by

ORIN.NumatCard, ORIN.DocTotal - ORIN.PaidToDate

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

The solution for my problem:

select

OINV.DocTotal - OINV.PaidToDate as 'Offen', OINV.CardCode as 'Kundennummer', OINV.NumAtCard as 'DIAMOD Rechnungsnummer', OINV.DocNum as 'Dokumentennummer',  max(ORCT.DocDate) as 'Zahlungsdatum', case when ORCT.DocCurr = (select MainCurncy from OADM) then RCT2.DcntSum else RCT2.DcntSumFC end as 'SkontoRechnungswährung',

    RCT2.DcntSum as 'SkontoFirmenwährung'

from

    JDT1 inner join

    OJDT on JDT1.TransId = OJDT.TransId inner join

    ORCT on OJDT.BaseRef = ORCT.DocNum inner join

    RCT2 on ORCT.DocNum = RCT2.DocNum inner join

    OINV on RCT2.BaseAbs = OINV.DocEntry

where

JDT1.TransType in ('24') and

OINV.U_I_Imported = 'Y' and

OINV.DocTotal - OINV.PaidToDate = 0

group by

OINV.NumAtCard, OINV.DocNum, OINV.CardCode, OINV.DocTotal - OINV.PaidToDate, ORCT.DocCurr, RCT2.DcntSum, RCT2.DcntSumFC

union all

select

OINV.DocTotal - OINV.PaidToDate as Offen, OINV.CardCode as 'Kundennummer', OINV.NumAtCard as 'DIAMOD Rechnungsnummer', OINV.DocNum as 'Dokumentennummer',  max(OITR.ReconDate) as 'Zahlungsdatum',

    0.0 as 'SkontoRechnungswährung',

    0.0 as 'SkontoFirmenwährung'

from

OITR inner join

ITR1 on OITR.ReconNum = ITR1.ReconNum inner join

OINV on ITR1.SrcObjAbs = OINV.DocEntry

where

OINV.DocTotal - OINV.PaidToDate = 0 and ITR1.SrcObjTyp = 13 and OINV.U_I_Imported = 'Y'

group by

OINV.NumatCard, OINV.DocNum, OINV.CardCode, OINV.DocTotal - OINV.PaidToDate

union all

select

ORIN.DocTotal - ORIN.PaidToDate as Offen, ORIN.CardCode as 'Kundennummer', ORIN.NumAtCard as 'DIAMOD Rechnungsnummer', ORIN.DocNum as 'Dokumentennummer',  max(OITR.ReconDate) as 'Zahlungsdatum',

    0.0 as 'SkontoRechnungswährung',

    0.0 as 'SkontoFirmenwährung'

from

OITR inner join

ITR1 on OITR.ReconNum = ITR1.ReconNum inner join

ORIN on ITR1.SrcObjAbs = ORIN.DocEntry

where

ORIN.DocTotal - ORIN.PaidToDate = 0 and ITR1.SrcObjTyp = 14 and ORIN.U_I_Imported = 'Y'

group by

ORIN.NumatCard, ORIN.DocNum, ORIN.CardCode, ORIN.DocTotal - ORIN.PaidToDate