b. Create Table: EV_SUPPLIER_RATINGS
Create a transformation “Create Table: EV_SUPPLIER_RATINGS” using one of the following queries:
I. With MLW script
Action: Once the suppliers and suppliers$integration_ids views are created, add them to the P2P DM as floating tables (not joined).
Then, in the MLW script, if the System/Region is not part of the data (LFA1), then remove the related PQL Column in the Data Pull part of the script.
Then run the MLW script.
It allows the script to match the suppliers from EcoVadis with the SAP ECC suppliers.
The script will output one table (named lfa1_with_recommended_ev) containing the recommended EcoVadis supplier for each SAP LIFNR supplier.
/*
Outputs: mapping between LIFNR id - Ecovadis id
Joins: LFA1 x RECOMMENDED x Ecovadis (to get the mapping between ids)
*/
DROP TABLE IF EXISTS "RECOMMENDED_INTEGRATION_IDS";
CREATE TABLE RECOMMENDED_INTEGRATION_IDS AS (
SELECT distinct("T1"."LIFNR") as integration_ids
, "T1"."lfa1_name1" as lfa1_name1
, T1."evid" as suppliers_evid
, "T1"."recommended_ev_supplier" as ecovadis_recommended
FROM (
SELECT
"P2P_LFA1"."LIFNR"
, "P2P_LFA1"."NAME1" as lfa1_name1
, "lfa1_with_recommended_ev"."id"
, "lfa1_with_recommended_ev"."name1"
, "lfa1_with_recommended_ev"."recommended_ev_supplier"
, "suppliers"."evid"
, "suppliers"."ev_supplier_name"
FROM "suppliers"
JOIN "lfa1_with_recommended_ev" ON 1=1
AND "suppliers"."ev_supplier_name" = "lfa1_with_recommended_ev"."recommended_ev_supplier"
JOIN "P2P_LFA1" ON 1=1
AND "P2P_LFA1"."NAME1" = "lfa1_with_recommended_ev"."name1"
) AS T1
);
/*Creates EV_SUPPLIER_RATINGS table from:
RECOMMENDED_INTEGRATION_IDS table from ML script
+
the integration_ids table from EcoVadis.
*/
DROP TABLE IF EXISTS EV_SUPPLIER_RATINGS;
CREATE TABLE EV_SUPPLIER_RATINGS AS (
SELECT
integrations1."integration_ids"
, suppliers1.*
,ADD_MONTHS(suppliers1."published_date", 12) as expiration_date
FROM "RECOMMENDED_INTEGRATION_IDS" as integrations1
LEFT JOIN "suppliers" as suppliers1 ON 1=1
AND integrations1."suppliers_evid" = suppliers1."evid"
UNION
SELECT
LPAD(integrations2.integration_ids,10,'0') AS integration_ids
,suppliers2.*
,ADD_MONTHS(suppliers2."published_date", 12) as expiration_date
FROM "suppliers$integration_ids" as integrations2
LEFT JOIN "suppliers" as suppliers2 ON 1=1
AND integrations2."suppliers_evid" = suppliers2."evid"
AND integrations2."integration_ids" != ''
);ii. Without MLW script
Create a transformation "Create Table: EV_SUPPLIER_RATINGS" with the following query:
/*
Creates EV_SUPPLIER_RATINGS table from the integration_ids table from EcoVadis.
*/
DROP TABLE IF EXISTS EV_SUPPLIER_RATINGS;
CREATE TABLE EV_SUPPLIER_RATINGS AS (
SELECT
LPAD(integrations2.integration_ids,10,'0') AS integration_ids
,suppliers2.*
,ADD_MONTHS(suppliers2."published_date", 12) as expiration_date
FROM "suppliers$integration_ids" as integrations2
LEFT JOIN "suppliers" as suppliers2 ON 1=1
AND integrations2."suppliers_evid" = suppliers2."evid"
AND integrations2."integration_ids" != ''
);I. With MLW script
Action: Once the suppliers and suppliers$integration_ids views are created, add them to the P2P DM as floating tables (not joined).
Then, in the MLW script, if the System/Region is not part of the data (LFA1), then remove the related PQL Column in the Data Pull part of the script.
Then run the MLW script.
It allows the script to match the suppliers from EcoVadis with the SAP ECC suppliers.
The script will output one table (named lfa1_with_recommended_ev) containing the recommended EcoVadis supplier for each SAP LIFNR supplier.
/*
Outputs: mapping between LIFNR id - Ecovadis id
Joins: LFA1 x RECOMMENDED x Ecovadis (to get the mapping between ids)
*/
DROP TABLE IF EXISTS "RECOMMENDED_INTEGRATION_IDS";
CREATE TABLE RECOMMENDED_INTEGRATION_IDS AS (
SELECT distinct("T1"."LIFNR") as integration_ids
, "T1"."lfa1_name1" as lfa1_name1
, T1."evid" as suppliers_evid
, "T1"."recommended_ev_supplier" as ecovadis_recommended
FROM (
SELECT
"P2P_LFA1"."LIFNR"
, "P2P_LFA1"."NAME1" as lfa1_name1
, "lfa1_with_recommended_ev"."id"
, "lfa1_with_recommended_ev"."name1"
, "lfa1_with_recommended_ev"."recommended_ev_supplier"
, "suppliers"."evid"
, "suppliers"."ev_supplier_name"
FROM "suppliers"
JOIN "lfa1_with_recommended_ev" ON 1=1
AND "suppliers"."ev_supplier_name" = "lfa1_with_recommended_ev"."recommended_ev_supplier"
JOIN "P2P_LFA1" ON 1=1
AND "P2P_LFA1"."NAME1" = "lfa1_with_recommended_ev"."name1"
) AS T1
);
/*Creates EV_SUPPLIER_RATINGS table from:
RECOMMENDED_INTEGRATION_IDS table from ML script
+
the integration_ids table from EcoVadis.
*/
DROP TABLE IF EXISTS EV_SUPPLIER_RATINGS;
CREATE TABLE EV_SUPPLIER_RATINGS AS (
SELECT
integrations1."integration_ids"
, suppliers1.*
,ADD_MONTHS(suppliers1."published_date", 12) as expiration_date
FROM "RECOMMENDED_INTEGRATION_IDS" as integrations1
LEFT JOIN "suppliers" as suppliers1 ON 1=1
AND integrations1."suppliers_evid" = suppliers1."evid"
UNION
SELECT
LPAD(integrations2.integration_ids,10,'0') AS integration_ids
,suppliers2.*
,ADD_MONTHS(suppliers2."published_date", 12) as expiration_date
FROM "suppliers$integration_ids" as integrations2
LEFT JOIN "suppliers" as suppliers2 ON 1=1
AND integrations2."suppliers_evid" = suppliers2."evid"
AND integrations2."integration_ids" != ''
);ii. Without MLW script
Create a transformation "Create Table: EV_SUPPLIER_RATINGS" with the following query:
/*
Creates EV_SUPPLIER_RATINGS table from the integration_ids table from EcoVadis.
*/
DROP TABLE IF EXISTS EV_SUPPLIER_RATINGS;
CREATE TABLE EV_SUPPLIER_RATINGS AS (
SELECT
LPAD(integrations2.integration_ids,10,'0') AS integration_ids
,suppliers2.*
,ADD_MONTHS(suppliers2."published_date", 12) as expiration_date
FROM "suppliers$integration_ids" as integrations2
LEFT JOIN "suppliers" as suppliers2 ON 1=1
AND integrations2."suppliers_evid" = suppliers2."evid"
AND integrations2."integration_ids" != ''
);