Supplier Liabilities SQL Query with currency calcu...

former_member983685 · ‎2022 Apr 28

I have a quick question, I have developed a SQL report for AP, however I we have 24 businesses which all have different currencies, it is east to report in the BP currency but many times we need to report in a different currency. Has anyone else had this issue before and if so could you point me in a quick direction.

Here is my code: I need to link this to a parameter to recalculate. Two quires here are running, but the second query will only run over one database not all like the first one.

Any idea's 🙂

IF OBJECT_ID(N'tempdb.dbo.#FX') IS NOT NULL

DROP TABLE #FX

IF OBJECT_ID(N'tempdb.dbo.#AP') IS NOT NULL

DROP TABLE #AP

DECLARE @DATABASENAME VARCHAR(25)

DECLARE @intcount INT

SET @intcount = 1

DECLARE @DATE AS DATE = GETDATE()

DECLARE @COMPANY AS VARCHAR(50) = 'KSS_LTD'

CREATE TABLE #FX (

Current_Database VARCHAR(50)

,RateDate DATE

,Currency VARCHAR(3)

,Rate NUMERIC(19, 6)

)

CREATE CLUSTERED INDEX ix_tempCIcOMPANY ON #FX (Current_Database)

WHILE (@intcount <= 24)

BEGIN

IF @intcount = 1

BEGIN

SET @DATABASENAME = 'KSS_INC'

USE [KSS_INC]

END

IF @intcount = 2

BEGIN

SET @DATABASENAME = 'KSS_LTD'

USE [KSS_LTD]

END

IF @intcount = 3

BEGIN

SET @DATABASENAME = 'MPSI_US'

USE [MPSI_US]

END

IF @intcount = 4

BEGIN

SET @DATABASENAME = 'KENTGRPLTD'

USE [KENTGRPLTD]

END

IF @intcount = 5

BEGIN

SET @DATABASENAME = 'KSS_INDIA'

USE [KSS_INDIA]

END

IF @intcount = 6

BEGIN

SET @DATABASENAME = 'KSS_Canada'

USE [KSS_Canada]

END

IF @intcount = 7

BEGIN

SET @DATABASENAME = 'KSS_AUS'

USE [KSS_AUS]

END

IF @intcount = 8

BEGIN

SET @DATABASENAME = 'MPS_INDIA'

USE [MPS_INDIA]

END

IF @intcount = 9

BEGIN

SET @DATABASENAME = 'MPSI_SAF'

USE [MPSI_SAF]

END

IF @intcount = 10

BEGIN

SET @DATABASENAME = 'MPSI_CAN'

USE [MPSI_CAN]

END

IF @intcount = 11

BEGIN

SET @DATABASENAME = 'MPSI_US'

USE [MPSI_US]

END

IF @intcount = 12

BEGIN

SET @DATABASENAME = 'MPSI_JAP'

USE [MPSI_JAP]

END

IF @intcount = 13

BEGIN

SET @DATABASENAME = 'MPSI_UK'

USE [MPSI_UK]

END

IF @intcount = 14

BEGIN

SET @DATABASENAME = 'MPSI_CHI'

USE [MPSI_CHI]

END

IF @intcount = 15

BEGIN

SET @DATABASENAME = 'KAL_LTD'

USE [KAL_LTD]

END

IF @intcount = 16

BEGIN

SET @DATABASENAME = 'TRADE_AREA_SYSTEMS'

USE [TRADE_AREA_SYSTEMS]

END

IF @intcount = 17

BEGIN

SET @DATABASENAME = 'INTALYTICS'

USE [INTALYTICS]

END

IF @intcount = 18

BEGIN

SET @DATABASENAME = 'KSSL_LTD'

USE [KSSL_LTD]

END

IF @intcount = 19

BEGIN

SET @DATABASENAME = 'MPSI'

USE [MPSI]

END

IF @intcount = 20

BEGIN

SET @DATABASENAME = 'MPSI_INT'

USE [MPSI_INT]

END

IF @intcount = 21

BEGIN

SET @DATABASENAME = 'MPSI_KOR'

USE [MPSI_KOR]

END

IF @intcount = 22

BEGIN

SET @DATABASENAME = 'NEW_SIS_SA'

USE [NEW_SIS_SA]

END

IF @intcount = 23

BEGIN

SET @DATABASENAME = 'MPSICHI_INC'

USE [MPSICHI_INC]

END

IF @intcount >= 24 -- to stop loop after last database

BEGIN

BREAK

END

INSERT INTO #FX

SELECT DB_Name() AS [Database]

,t0.RateDate

,T0.Currency

,T0.Rate

FROM ORTT AS t0

LEFT JOIN ocrn AS t1 ON t0.Currency = t1.CurrCode

WHERE RateDate = @Date

----and DB_NAME() = @company

UNION ALL

SELECT TOP 1 DB_Name() AS [Database]

,t0.RateDate

,Currency = CASE

WHEN db_name() = 'KSS_LTD'

THEN 'GBP'

WHEN db_name() = 'KSSGRP_CON'

THEN 'CAD'

WHEN DB_NAME() = 'KSS_INC'

THEN 'USD'

WHEN DB_Name() = 'MPSI_US'

THEN 'USD'

END

,Rate = 1

FROM ORTT AS t0 ---- forighn rates

LEFT JOIN ocrn AS t1 ON t0.Currency = t1.CurrCode

WHERE RateDate = @Date

----and DB_NAME() = @company

SET @intcount = @intcount + 1

END

CREATE TABLE #AP (

Current_Database VARCHAR(50)

,Customer_Number VARCHAR(50)

,Customer_Name VARCHAR(150)

,Sales_Employee_Name_BP VARCHAR(50)

,TransType NUMERIC(18, 0)

,[Type] VARCHAR(50) --

,Document_Number VARCHAR(50)

,Posting_Date DATE --

,Due_Date DATE --

,BP_Reference VARCHAR(150)

,Balance_Due_Currency VARCHAR(5)

,Payment_Terms_BP VARCHAR(50)

----, DaysDiff numeric(18,0)

,Balance NUMERIC(18, 2)

,Future NUMERIC(18, 2)

,[Current] NUMERIC(18, 2)

,[30_60_Days] NUMERIC(18, 2)

,[61_90_Days] NUMERIC(18, 2)

,[91-120_Days] NUMERIC(18, 2)

,[121+_Days] NUMERIC(18, 2)

)

CREATE CLUSTERED INDEX ix_tempCIcOMPANY ON #ap (customer_number)

use [kss_ltd]

WHILE (@intcount <= 25)

BEGIN

IF @intcount = 1

BEGIN

SET @DATABASENAME = 'KSS_INC'

USE [KSS_INC]

END

IF @intcount = 2

BEGIN

SET @DATABASENAME = 'KSS_LTD'

USE [KSS_LTD]

END

IF @intcount = 3

BEGIN

SET @DATABASENAME = 'MPSI_US'

USE [MPSI_US]

END

IF @intcount = 4

BEGIN

SET @DATABASENAME = 'KENTGRPLTD'

USE [KENTGRPLTD]

END

IF @intcount = 5

BEGIN

SET @DATABASENAME = 'KSS_INDIA'

USE [KSS_INDIA]

END

IF @intcount = 6

BEGIN

SET @DATABASENAME = 'KSS_Canada'

USE [KSS_Canada]

END

IF @intcount = 7

BEGIN

SET @DATABASENAME = 'KSS_AUS'

USE [KSS_AUS]

END

IF @intcount = 8

BEGIN

SET @DATABASENAME = 'MPS_INDIA'

USE [MPS_INDIA]

END

IF @intcount = 9

BEGIN

SET @DATABASENAME = 'MPSI_SAF'

USE [MPSI_SAF]

END

IF @intcount = 10

BEGIN

SET @DATABASENAME = 'MPSI_CAN'

USE [MPSI_CAN]

END

IF @intcount = 11

BEGIN

SET @DATABASENAME = 'MPSI_US'

USE [MPSI_US]

END

IF @intcount = 12

BEGIN

SET @DATABASENAME = 'MPSI_JAP'

USE [MPSI_JAP]

END

IF @intcount = 13

BEGIN

SET @DATABASENAME = 'MPSI_UK'

USE [MPSI_UK]

END

IF @intcount = 14

BEGIN

SET @DATABASENAME = 'MPSI_CHI'

USE [MPSI_CHI]

END

IF @intcount = 15

BEGIN

SET @DATABASENAME = 'KAL_LTD'

USE [KAL_LTD]

END

IF @intcount = 16

BEGIN

SET @DATABASENAME = 'TRADE_AREA_SYSTEMS'

USE [TRADE_AREA_SYSTEMS]

END

IF @intcount = 17

BEGIN

SET @DATABASENAME = 'INTALYTICS'

USE [INTALYTICS]

END

IF @intcount = 18

BEGIN

SET @DATABASENAME = 'KSSL_LTD'

USE [KSSL_LTD]

END

IF @intcount = 19

BEGIN

SET @DATABASENAME = 'MPSI'

USE [MPSI]

END

IF @intcount = 20

BEGIN

SET @DATABASENAME = 'MPSI_INT'

USE [MPSI_INT]

END

IF @intcount = 21

BEGIN

SET @DATABASENAME = 'MPSI_KOR'

USE [MPSI_KOR]

END

IF @intcount = 22

BEGIN

SET @DATABASENAME = 'NEW_SIS_SA'

USE [NEW_SIS_SA]

END

IF @intcount = 23

BEGIN

SET @DATABASENAME = 'MPSICHI_INC'

USE [MPSICHI_INC]

END

IF @intcount = 24

BEGIN

SET @DATABASENAME = 'ZTEST_ESITE_ANALYTICS'

END

IF @intcount >= 25 -- to stop loop after last database

BEGIN

BREAK

END

INSERT INTO #AP

SELECT DB_Name() AS [Database]

,T1."CardCode" AS customer_number

,T1."CardName" AS Customer_Name

,t7.SlpName AS Sales_Employee_Name_BP

,t0.TransType

,CASE

WHEN T0.TransType = 18

THEN 'A/P Invoice'

WHEN T0.TransType = '46'

THEN 'Outgoing_Payments'

WHEN t0.TransType = 30

THEN 'Journal Entry'

WHEN t0.TransType = 24

THEN 'Incoming Payment'

END

,T0.Ref1

,T0.RefDate AS Posting_Date

,T0.DueDate AS Due_Date

,t0.Ref2

,T1.Currency AS Balance_Due_Currency

,T6.PymntGroup

--

,(T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)) 'Balance'

,(

CASE

WHEN datediff(D, T0."DueDate", @Date) <= 0

THEN T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)

ELSE 0

END

) 'Future'

,(

CASE

WHEN datediff(D, T0."DueDate", @Date) > 0

AND datediff(D, T0."DueDate", @Date) <= 30

THEN T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)

ELSE 0

END

) 'Current'

,(

CASE

WHEN datediff(D, T0."DueDate", @Date) > 30

AND datediff(D, T0."DueDate", @Date) <= 60

THEN T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)

ELSE 0

END

) '31-60 Days'

,(

CASE

WHEN datediff(D, T0."DueDate", @Date) > 60

AND datediff(D, T0."DueDate", @Date) <= 90

THEN T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)

ELSE 0

END

) '61-90 Days'

,(

CASE

WHEN datediff(D, T0."DueDate", @Date) > 90

AND datediff(D, T0."DueDate", @Date) <= 120

THEN T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)

ELSE 0

END

) '91-120 Days'

,(

CASE

WHEN datediff(D, T0."DueDate", @Date) > 120

THEN T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)

ELSE 0

END

) '121+ Days'

FROM JDT1 T0

INNER JOIN OCRD T1 ON T0."ShortName" = T1."CardCode"

INNER JOIN OJDT T2 ON T0."TransId" = T2."TransId"

LEFT JOIN (

SELECT T4."ShortName"

,T4."TransId"

,SUM(T4."ReconSum" * CASE

WHEN T4."IsCredit" = 'D'

THEN 1

ELSE - 1

END) AS "ReconSum"

FROM OITR T3

INNER JOIN ITR1 T4 ON T3."ReconNum" = T4."ReconNum"

WHERE T3."ReconDate" <= @Date

GROUP BY T4."ShortName"

,T4."TransId"

) T5 ON T0."TransId" = T5."TransId"

AND T0."ShortName" = T5."ShortName"

LEFT JOIN octg AS t6 ON t6.GroupNum = t1.GroupNum /*sales BP number*/

LEFT JOIN OSLP AS t7 ON t7.SlpCode = t1.SlpCode

WHERE T0."RefDate" <= @Date

AND T1."CardType" = 'S'

AND (T0."Debit" - T0."Credit" - COALESCE(T5."ReconSum", 0)) <> 0

AND Balance <> 0 /*to take filter out all other transactions that have been at zero at time of date*/

ORDER BY T1."CardCode"

,T0."DueDate"

,T0."Ref1"

SET @intcount = @intcount + 1

END

SELECT DISTINCT *

FROM #AP

--WHERE Current_Database IN (@Company)

SELECT *

FROM #FX

AlexGourdet · ‎2022 Apr 28

Thank you for visiting SAP Community to get answers to your questions.

Since you're asking a question here for the first time, I'd like to recommend you with the following steps so you can get the most out of your community membership:

Please see: https://community.sap.com/resources/questions-and-answers as this resource page provides tips for preparing questions that draw responses from our members
Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html. as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,
-Alex

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Supplier Liabilities SQL Query with currency calculation