Supplier Liabilities SQL Query with currency calculation

former_member983685 — Thu, 28 Apr 2022 20:33:53 GMT

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

topic Re: Supplier Liabilities SQL Query with currency calculation in Application Development and Automation Discussions

Supplier Liabilities SQL Query with currency calculation

Re: Supplier Liabilities SQL Query with currency calculation