Skip to main content

Celonis Product Documentation

d. Update P2P_LFA1_ECOVADIS with Parent Mappings

Create a transformation “Update P2P_LFA1_ECOVADIS with Parent Mappings” with the following query:

Note

This code depends on previously running the skill 4.Update Parent Supplier once, so here we will only create the transformation and save it for running later.

ALTER TABLE P2P_LFA1_ECOVADIS ADD COLUMN parentSupplierInCelonis varchar(80);
ALTER TABLE P2P_LFA1_ECOVADIS ADD COLUMN parentUpdatedDateInCelonis date;
ALTER TABLE P2P_LFA1_ECOVADIS ADD COLUMN parentUpdatedByInCelonis varchar(80);

 --taking only most recent rows for each supplier
DROP TABLE IF EXISTS PARENT_SUPPLIERS_TABLE;
CREATE TABLE "PARENT_SUPPLIERS_TABLE" AS
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY "MANDT", "LIFNR"
    ORDER BY "PARENT_RECORDS"."_CELONIS_CHANGE_DATE" DESC) as seqnum
    FROM "PARENT_RECORDS";
DELETE FROM "PARENT_SUPPLIERS_TABLE" WHERE seqnum > 1; 

UPDATE P2P_LFA1_ECOVADIS LFA1 SET parentSupplierInCelonis = 
"PARENT_SUPPLIERS_TABLE"."NEW_PARENT_SUPPLIER"
FROM "PARENT_SUPPLIERS_TABLE"
WHERE (LFA1.MANDT = "PARENT_SUPPLIERS_TABLE".MANDT
AND LFA1.LIFNR = "PARENT_SUPPLIERS_TABLE".LIFNR); 

UPDATE P2P_LFA1_ECOVADIS LFA1 SET parentUpdatedDateInCelonis = 
"PARENT_SUPPLIERS_TABLE"."_CELONIS_CHANGE_DATE"
FROM "PARENT_SUPPLIERS_TABLE"
WHERE (LFA1.MANDT = "PARENT_SUPPLIERS_TABLE".MANDT
AND LFA1.LIFNR = "PARENT_SUPPLIERS_TABLE".LIFNR); 

UPDATE P2P_LFA1_ECOVADIS LFA1 SET parentUpdatedByInCelonis = 
"PARENT_SUPPLIERS_TABLE"."USERNAME"
FROM "PARENT_SUPPLIERS_TABLE"
WHERE (LFA1.MANDT = "PARENT_SUPPLIERS_TABLE".MANDT
AND LFA1.LIFNR = "PARENT_SUPPLIERS_TABLE".LIFNR);

Action: Having created all of the transformations, you should schedule these to run once a week as a Delta load in the Scheduling section. First, the Weekly EcoVadis extraction data job and second the EcoVadis Transformation data job.

image33.png