



--SELECT * FROM A

ALTER PROCEDURE  [Prod].[ - Princing_Channel_Model]
AS
BEGIN





    Drop table if exists #temp_Flows
   --CREATING A TEMPORAL TABLE WHERE WE APPLY THE GLOBLY METHODOLOGY OF REMOVING INTERNAL TRANSFERS

    Create Table #temp_Flows (
    [Fiscal year period] varchar(20),
    [Company Code] Varchar(5),
    [Account] varchar(20),
    [Generic Material] Varchar(8),
    [Issue] float,
    [Transfers In] float,
    [Transfers Out] float,
    [Return] float
    )


    Insert into #temp_Flows

    SELECT DISTINCT [Fiscal year period],[Company Code],[Account],[Generic Material],
    SUM(CASE WHEN [Metric] IN ('s (in)') THEN coalesce([Metric Value],0) END) OVER (PARTITION BY [Account],   [Company Code],[Generic Material],[Fiscal year period],[Metric]) AS [Issue]
    ,SUM(CASE WHEN [Metric] IN ( 'tDD (in)','tDE (in)','tED (in)','tEE (in)') THEN coalesce([Metric Value],0) END) OVER (PARTITION BY  [Account],[Company Code],[Generic Material] ,[Fiscal year period],[Metric]) AS [Transfers In]
    ,SUM(CASE WHEN [Metric] IN ('tDD (Out)','tDE (Out)','tED (Out)','tEE (Out)') THEN coalesce([Metric Value],0) END) OVER (PARTITION BY [Account],[Company Code],[Generic Material] ,[Fiscal year period],[Metric]) AS [Transfers Out]
    ,SUM(CASE WHEN [Metric] IN ( 'r (out)') THEN ([Metric Value]) END) OVER (PARTITION BY [Account],[Company Code],[Generic Material],[Fiscal year period],[Metric]) AS [Return]

    FROM
    (
    SELECT [Fiscal year period],[Company Code],[Contra Account]
                                    ,[Account],[Generic Material],[Metric],[Metric Value] 

    FROM [
].[Prod].[ - Global Field Flows (S_GL_KPI_PRSF_002) Full dataset (Client)_Table_SAP BW]
    -------------Applying the Global Methodology for Transfers
    WHERE [Account] != [Contra Account]
    AND [Metric] IN ( 'tDD (Out)','tDE (Out)','tEE (Out)','tED (Out)' ,'tDD (in)','tDE (in)','tEE (in)','tED (in)')


    -----Bringing in other data excluding the filtering of inta and contra movements/internal flows
    UNION ALL

    SELECT  [Fiscal year period],[Company Code],[Contra Account]
                                    ,[Account],[Generic Material],[Metric],[Metric Value] 

    FROM [].[Prod].[ - Global Field Flows (S_GL_KPI_PRSF_002) Full dataset (Client)_Table_SAP BW]
    WHERE [Metric] IN ('r (out)','s (in)','r (star) (in)','rDP (in)','rEP (in)','sPE (out)','sPD (out)'
                                                                                    ,'i (star) out (q+dDE (star)-out)','y (Based on P.Ord.)','x','z','q (in) - (s (in))'
                                                                                                    )

    ) Table_1

    WHERE [Company Code] IN ('AE10','AE20','SA10')






-----We instert the modelled date which excluded internal transfers into FlowsFact temp table Flows
Drop table if exists #temp_FlowsFact

Create Table #temp_FlowsFact (
[Fiscal year period] varchar(20),
[Calendar day] DATE,
[Financial year] Varchar (5),
[Period] Varchar(8),
[Account] varchar(20),
--[Generic Material] Varchar(8),
[Issue] float,
[Transfers In] float,
[Transfers Out] float,
[Return] float
)

INSERT INTO #temp_FlowsFact


SELECT * FROM
(
SELECT DISTINCT  FLOWS.[Fiscal year period],Calendar_Table.[Last Day of period],Calendar_Table.[Financial Year],Calendar_Table.[Period]
,FLOWS.[Account],ISSUE.Issues,TRANS_IN.[Transfers In],TRANS_OUT.[Transfers Out],RETURNS.[Return] 
 
 FROM  #temp_Flows AS FLOWS 

-----Bringing in Issue fact
LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Issue]) ,0) AS [Issues] FROM #temp_Flows AS Issues  
GROUP BY [Fiscal year period],[Account] ) AS ISSUE 
ON FLOWS.Account=ISSUE.Account AND FLOWS.[Fiscal year period] =ISSUE.[Fiscal year period] --AND FLOWS.[Generic Material]=ISSUE.[Generic Material]

-----Bringing in Transfers In fact

LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Transfers In]) ,0) AS [Transfers In] FROM #temp_Flows AS TRANS_IN   
GROUP BY [Fiscal year period],[Account] ) AS TRANS_IN 
ON FLOWS.Account=TRANS_IN.Account AND FLOWS.[Fiscal year period] =TRANS_IN.[Fiscal year period] --AND FLOWS.[Generic Material]=TRANS_IN.[Generic Material]

-----Bringing in Transfers out fact

LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Transfers Out]) ,0) AS [Transfers Out] FROM #temp_Flows AS TRANS_OUT   
GROUP BY [Fiscal year period],[Account] ) AS TRANS_OUT 
ON FLOWS.Account=TRANS_OUT.Account AND FLOWS.[Fiscal year period] =TRANS_OUT.[Fiscal year period] --AND FLOWS.[Generic Material]=TRANS_OUT.[Generic Material]

-----Bringing in return fact

LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Return]) ,0) AS [Return] FROM #temp_Flows AS RETURNS  
GROUP BY [Fiscal year period],[Account] ) AS RETURNS  
ON FLOWS.Account=RETURNS.Account AND FLOWS.[Fiscal year period] =RETURNS.[Fiscal year period] --AND FLOWS.[Generic Material]=RETURNS.[Generic Material]


----Bringing in last day of reporting Date for ease of reporting and calculations of rolling numbers


LEFT OUTER JOIN (
Select 
    CAST([Date] AS DATE) AS [Date]
    ,CONCAT('20',SUBSTRING([FY],3,2)) AS [Financial Year]
    ,[Period]
    ,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date]) AS [First Day of period]
    ,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC) AS [Last Day of period]
    ,DATEDIFF(DAY,(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date])),(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC))) +1 AS [Number of days]
From [].[Prod].[ - Calendar Days Data_View_Other] AS CDF
Where
    [FY] IS NOT NULL

)
AS Calendar_Table ON LEFT(FLOWS.[Fiscal year period],3) =CONCAT('0',REPLACE( Calendar_Table.[Period],'P','')) AND RIGHT(FLOWS.[Fiscal year period],4)= Calendar_Table.[Financial Year]



) FlowsFact ;



------------------------------ Creating temp table for Unpivoted  results as well as the calculation for FTRs
Create Table #temp_FieldFlowsFact (
[Account] varchar(20),
[Calendar day] DATE,
[Financial year] Varchar (5),
[Period] Varchar(8),
[Period type] Varchar(10),
--[Generic Material] Varchar(8),
[Metric] Varchar(50),
[Metric Value] float
)

INSERT INTO #temp_FieldFlowsFact



SELECT [Account],[Calendar day],[Financial year],[Period]
				,CASE 
				WHEN [Metric] like '%R12M%' THEN 'R12M'
				WHEN [Metric] like '%R6M%' THEN 'R6M'
				WHEN [Metric] like '%R3M%' THEN 'R3M'
				ELSE 'Periodic'
				END AS [Period type]
,[Metric],[Metric Value] 

FROM
(
----Calculating Rolling FTRs
SELECT  [Account], [Calendar day] ,[Financial year],[Period]


,[Issue]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Issues]

,[Transfers In] 
,SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers In]

,[Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers Out]

,[Return] 
,SUM([Return]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Return]


,ROUND(ISNULL(([Transfers Out]+[Return])/NULLIF(([Issue]+[Transfers In]),0),0)*100,2) AS [FTR%]  --Calculating FTR%






,ROUND(ISNULL((   SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)  
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
)

/NULLIF(( 
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)

),0),0)*100,2) AS [R12M FTR%]  --Calculating R12M FTR%



,ROUND(ISNULL((   SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)  
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
)

/NULLIF(( 
SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 5 PRECEDING)

),0),0)*100,2) AS [R6M FTR%]  --Calculating R6M FTR%



,ROUND(ISNULL((   SUM([Transfers Out]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)  
+
SUM([Return]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)
)

/NULLIF(( 
SUM([Issue]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account]  ORDER BY [Calendar day] ROWS 2 PRECEDING)

),0),0)*100,2) AS [R3M FTR%]  ---Calculating R3M FTR%


FROM #temp_FlowsFact) AS FACT

--WHERE [Account]='SA11'
UNPIVOT  
(              [Metric Value] FOR [Metric] IN ( 
 [Issue],[R12M Issues],[R6M Issues],[R3M Issues],[Transfers In],[R12M Transfers In]
,[R6M Transfers In],[R3M Transfers In],[Transfers Out],[R12M Transfers Out],[R6M Transfers Out]
,[R3M Transfers Out],[Return],[R12M Return],[R6M Return],[R3M Return],[FTR%],[R12M FTR%],[R6M FTR%],[R3M FTR%]
)
)AS unpvt
--WHERE [Account] ='AE25' AND [Metric] like '%FTR%'
ORDER BY [Calendar day]




--Creating pricing Buckets

Create Table #temp_FieldFlowsFact_with_Channels (
[Account] varchar(20),
[Financial year] Varchar (5),
[Period] Varchar(8),
[Period type] Varchar(10),
[Pricing channel] Varchar(20),
[Pricing Category] Varchar(20),


)

INSERT INTO  #temp_FieldFlowsFact_with_Channels

SELECT [Account],[Financial year],[Period],

CASE 
WHEN [Metric] like '%R12M%' THEN 'R12M'
WHEN [Metric] like '%R6M%' THEN 'R6M'
WHEN [Metric] like '%R3M%' THEN 'R3M'
ELSE 'Periodic'
END AS [Period type]


,CASE 

WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0)  BETWEEN 98 AND 100 THEN '98 - 100'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0)   BETWEEN 90 AND 97 THEN '90 - 97'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0)   BETWEEN 70 AND 89 THEN '70 - 89'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0)   BETWEEN 51 AND 69 THEN '51 - 69'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0)   BETWEEN 0 AND 50 THEN 'Below 50'
WHEN [Metric] LIKE '%FTR%' AND  ROUND([Metric Value],0)  >100  THEN 'Above 100'
ELSE 'Uncategorised'

END AS [Pricing channel]

,CASE 

WHEN ROUND([Metric Value],0)   BETWEEN 98 AND 100 THEN 'E'
WHEN ROUND([Metric Value],0)   BETWEEN 90 AND 97 THEN 'D'
WHEN ROUND([Metric Value],0)   BETWEEN 70 AND 89 THEN 'C'
WHEN ROUND([Metric Value],0)   BETWEEN 51 AND 69 THEN 'B'
WHEN ROUND([Metric Value],0)   BETWEEN 0 AND 50 THEN 'A'
WHEN ROUND([Metric Value],0)  >100  THEN 'F'
ELSE 'Uncategorised'

END AS [Pricing Category]


FROM  #temp_FieldFlowsFact 


WHERE [Metric] like '%FTR%%'  
ORDER BY [Calendar day]




----------Final Table

--Note that The pricing channel is based on the R6M Rolling FTR

Create Table #temp_FinalTable (
[Account] varchar(20),
[Account Name] varchar(100),
[Reporting Parent] Varchar (100),
[CRM Parent] VARCHAR (100),
[Financial year] Varchar(8),
[Period] Varchar(8),
[Period type] Varchar(10),
[Metric] Varchar (50),
[Metric Value] float,
[Pricing channel] Varchar(20),
[Pricing Category] Varchar(20),
[Weighted Avg Rate] Nvarchar(20)

)

INSERT INTO  #temp_FinalTable

SELECT DISTINCT  FlowsFact.[Account],Customer.[Account Name],Customer.[Reporting Parent],Customer.[CRM Parent], FlowsFact.[Financial year], FlowsFact.[Period],FlowsFact.[Period type],FlowsFact.[Metric], 
FlowsFact.[Metric Value],CHANNEL.[Pricing channel],CHANNEL.[Pricing Category],

CASE 


WHEN CHANNEL.[Pricing Category]='E' AND FlowsFact.[Metric]='R12M FTR%' THEN '-'
WHEN CHANNEL.[Pricing Category]='D' AND FlowsFact.[Metric]='R12M FTR%' THEN '1.87'
WHEN CHANNEL.[Pricing Category]='C' AND FlowsFact.[Metric]='R12M FTR%' THEN '5.13'
WHEN CHANNEL.[Pricing Category]='B' AND FlowsFact.[Metric]='R12M FTR%' THEN '10.4'
WHEN CHANNEL.[Pricing Category]='A' AND FlowsFact.[Metric]='R12M FTR%' THEN '21.79'
ELSE '' ------This may change depending on furture reporting requiremnts
END AS [Weighted Avg Rate]
FROM   #temp_FieldFlowsFact AS FlowsFact

INNER JOIN 

(SELECT * FROM #temp_FieldFlowsFact_with_Channels ) AS CHANNEL ON CHANNEL.[Account]=FlowsFact.[Account] 
AND CHANNEL.[Financial year]=FlowsFact.[Financial year] AND CHANNEL.[Period]=FlowsFact.[Period] AND FlowsFact.[Period type]=CHANNEL.[Period type]

INNER JOIN (
SELECT [Account],[CRM Parent],[Reporting Parent],[Account Name]
FROM [Stag].[IMETA - Customer Master Data (ZZ_GL_COBJ_AMR)_Table_SAP BW]  
WHERE [Company Code] IN ('AE10','AE20','SA10')  
) AS Customer ON   FlowsFact.Account=Customer.Account
WHERE  FlowsFact.[Financial year]='2024'  ---Remove filters for complete datas et


-------------Writing Data into the new Prod Table


CREATE TABLE [Prod].[IMETA_Pricing_Channel_Model_SAP_BW]
(
[Account] varchar(20),
[Account Name] varchar(100),
[CRM Parent] Varchar(100),
[Reporting Parent] Varchar (100),
[Financial year] Varchar(8),
[Period] Varchar(8),
[Period type] Varchar(10),
[Metric] Varchar (50),
[Metric Value] float,
[Pricing channel] Varchar(20),
[Pricing Category] Varchar(20),
[Weighted Avg Rate] Nvarchar(20)
)


INSERT INTO [Prod].[IMETA_Pricing_Channel_Model_SAP_BW]
SELECT [Account]
      ,[Account Name]
	  ,[CRM Parent]
      ,[Reporting Parent]
      ,[Financial year]
      ,[Period]
      ,[Period type]
      ,[Metric]
      ,[Metric Value]
      ,[Pricing channel]
      ,[Pricing Category] 
	  ,[Weighted Avg Rate] 
FROM #temp_FinalTable 



--SELECT * FROM [Prod].[IMETA_Pricing_Channel_Model_SAP_BW] WHERE  [Period]='P12'

-------------------------------------------------------------------------------------------------------
END




