Skip to main content

Celonis Product Documentation

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" != ''
);