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.