Procurement Vulnerability Analysis for SAP SpotApp

create or replace view V_DIM_VENDOR_BY_MATERIAL_GROUP(
	MATKL,
	NAME1,
	MANDT,
	TELF1,
	LANDX
) as
SELECT
  "ta_1"."MATKL" ,
  "ta_2"."NAME1" ,
  "ta_1"."MANDT" ,
  "ta_2"."TELF1" ,
  "ta_3"."LANDX"
FROM “<DATABASE NAME>“.”<SCHEMA NAME>“."EKPO" "MTA_0"
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."MARA" "ta_1"
    ON (
      "MTA_0"."MATNR" = "ta_1"."MATNR"
      AND "MTA_0"."MANDT" = "ta_1"."MANDT"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKKO" "MTA_1"
    ON (
      "MTA_0"."MANDT" = "MTA_1"."MANDT"
      AND "MTA_0"."EBELN" = "MTA_1"."EBELN"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKKO" "MTA_2"
    ON (
      "MTA_0"."MANDT" = "MTA_2"."MANDT"
      AND "MTA_0"."EBELN" = "MTA_2"."EBELN"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."LFA1" "ta_2"
    ON (
      "MTA_1"."MANDT" = "ta_2"."MANDT"
      AND "MTA_1"."LIFNR" = "ta_2"."LIFNR"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."LFA1" "MTA_3"
    ON (
      "MTA_2"."MANDT" = "MTA_3"."MANDT"
      AND "MTA_2"."LIFNR" = "MTA_3"."LIFNR"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."T005T" "ta_3"
    ON (
      "MTA_3"."LAND1" = "ta_3"."LAND1"
      AND "ta_3"."SPRAS" = 'E'
    )
GROUP BY
  "ta_1"."MATKL",
  "ta_2"."NAME1",
  "ta_1"."MANDT",
  "ta_2"."TELF1",
  "ta_3"."LANDX";
create or replace view V_DIM_MAKT(
	MANDT,
	MATNR,
	SPRAS,
	MAKTX
) as
Select
 	cast (MANDT as VARCHAR(3)),
	cast (MATNR as VARCHAR(40)),
	cast (SPRAS as VARCHAR(1)),
	cast (MAKTX as VARCHAR(40))
 From “<DATABASE NAME>“.”<SCHEMA NAME>“."MAKT" Where SPRAS = 'E';
create or replace view V_DIM_MATERIAL_SOURCING_TYPE(
	MATNR,
	MANDT,
	VENDOR_SOURCE_TYPE,
	COUNTRY_SOURCE_TYPE,
	REGION_SOURCE_TYPE
) as
SELECT
  "ta_1"."MATNR" ,
  "ta_1"."MANDT" ,
  CASE
    WHEN count(DISTINCT "ta_2"."NAME1") > 1 THEN 'multi-source'
    ELSE 'single-source'
  END "VENDOR_SOURCE_TYPE",
  CASE
    WHEN count(DISTINCT "ta_2"."LAND1") > 1 THEN 'multi-country'
    ELSE 'single-country'
  END "COUNTRY_SOURCE_TYPE",
  CASE
    WHEN count(DISTINCT "ta_2"."REGIO") > 1 THEN 'multi-region'
    ELSE 'single-region'
  END "REGION_SOURCE_TYPE"
FROM “<DATABASE NAME>“.”<SCHEMA NAME>“."EKPO" "MTA_0"
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."MARA" "ta_1"
    ON (
      "MTA_0"."MATNR" = "ta_1"."MATNR"
      AND "MTA_0"."MANDT" = "ta_1"."MANDT"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKET" "ta_3"
    ON (
      "MTA_0"."MANDT" = "ta_3"."MANDT"
      AND "MTA_0"."EBELN" = "ta_3"."EBELN"
      AND "MTA_0"."EBELP" = "ta_3"."EBELP"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."EKKO" "MTA_1"
    ON (
      "MTA_0"."MANDT" = "MTA_1"."MANDT"
      AND "MTA_0"."EBELN" = "MTA_1"."EBELN"
    )
  JOIN “<DATABASE NAME>“.”<SCHEMA NAME>“."LFA1" "ta_2"
    ON (
      "MTA_1"."MANDT" = "ta_2"."MANDT"
      AND "MTA_1"."LIFNR" = "ta_2"."LIFNR"
    )
WHERE (
  (
    TO_DATE("ta_3"."SLFDT",'yyyymmdd') >= dateadd(month, -18, current_date)
    AND TO_DATE("ta_3"."SLFDT",'yyyymmdd') < current_date
  )
)
GROUP BY
  "ta_1"."MATNR",
  "ta_1"."MANDT";
create or replace view V_DIM_T005T(
	MANDT,
	SPRAS,
	LAND1,
	LANDX
) as
Select
 	cast (MANDT as VARCHAR(3)),
	cast (SPRAS as VARCHAR(1)),
	cast (LAND1 as VARCHAR(3)),
	cast (LANDX as VARCHAR(15))
 From “<DATABASE NAME>“.”<SCHEMA NAME>“."T005T" Where SPRAS = 'E';
create or replace view V_DIM_TCURR(
	BEDAT,
	MANDT,
	TCURR,
	KURST,
	FCURR,
	UKURS
) as
SELECT
CAST((99999999 - CAST(MaxDate AS integer)) AS varchar) "BEDAT",
"MANDT",
Lower("TCURR") as "TCURR",
"KURST",
Lower("FCURR") as "FCURR" ,
"UKURS"
FROM(
    SELECT
        "ta_1"."GDATU",
        "ta_1"."MANDT" "MANDT",
        "ta_1"."TCURR" "TCURR",
        "ta_1"."KURST" "KURST",
        "ta_1"."FCURR" "FCURR",
        "ta_1"."UKURS",
        min("ta_1"."GDATU") over ( partition by "ta_1"."MANDT", "ta_1"."TCURR" , "ta_1"."KURST" , "ta_1"."FCURR" ,"ta_1"."UKURS") as MaxDate
    FROM “<DATABASE NAME>“.”<SCHEMA NAME>“."TCURR" "ta_1") WHERE "GDATU"=MaxDate and "KURST" ='M' and "TCURR" ='USD';