Skip to main content

Celonis Product Documentation

c. Create Table: P2P_LFA1_ECOVADIS

This table depends on having a mapping between suppliers and risk. This is to mimic the usual customer requirement of integrating Risk as an important part of the app. If the customer has such a mapping you can leverage it and upload that excel. If not, you can create a dummy Excel sheet instead like this:

image31.png

Note

Even if the LIFNRs do not have any matches between the sheet and the customer's SAP that is OK. This is just so that the resulting table, after running the query, has the “risk” column which is necessary for the views to work afterwards.

Create a transformation “Create Table: P2P_LFA1_ECOVADIS” with the following query:

/*
First, create temp table without risk
*/
DROP TABLE IF EXISTS "P2P_LFA1_ECOVADIS_NO_RISK";
CREATE TABLE "P2P_LFA1_ECOVADIS_NO_RISK" AS (
SELECT
   LFA1.*
   ,ECO.integration_ids, ECO.ev_supplier_name, ECO.client_supplier_name, ECO.tax_number, ECO.siret_number, ECO.active, ECO.evid, ECO.city, ECO.state, ECO.country, ECO.address_1, ECO.address_2, ECO.website, ECO.isic_category, ECO.employee_range, ECO.size, ECO.turnover, ECO.risk_country, ECO.campaign_name, ECO.campaign_type, ECO.rfp_campaign_icon, ECO.current_stage, ECO.progress_status, ECO.sharing_status, ECO.request_outcome, ECO.current_stage_code, ECO.progress_status_code, ECO.sharing_status_code, ECO.request_outcome_code, ECO.source, ECO.launch_date, ECO.deadline, ECO.declined, ECO.last_comment, ECO.comment_date, ECO.buyer_action, ECO.specific_comment, ECO.published_date, ECO.status_last_update, ECO.global_score, ECO.env_score, ECO.lab_score, ECO.fbp_score, ECO.sup_score, ECO.global_trend, ECO.env_trend, ECO.lab_trend, ECO.fbp_trend, ECO.sup_trend, ECO.scorecard_link, ECO.expired, ECO.documents_number, ECO.scope_change, ECO.nb_flags, ECO.nb_client_filters, ECO.nb_integration_ids, ECO.nb_client_ca, ECO.nb_all_ca, ECO.nb_draft_ca, ECO.nb_requested_ca, ECO.nb_in_progress_ca, ECO.nb_rejected_ca, ECO.nb_completed_ca, ECO.nb_overdue_ca, ECO.nb_no_validation_ca, ECO.nb_not_validated_ca, ECO.nb_validated_ca, ECO.nb_closed_ca, ECO.nb_documents, ECO.vat_number, ECO.declined_date, ECO.buyer_last_contacted, ECO.last_modification, ECO.next_deadline, ECO.parent_company, ECO.supplier_contact_first_name, ECO.supplier_contact_last_name, ECO.supplier_contact_email, ECO.supplier_contact_phone, ECO.buyer_contact_first_name, ECO.buyer_contact_last_name, ECO.buyer_contact_email, ECO.expiration_date
FROM "P2P_LFA1" AS "LFA1"
LEFT JOIN "EV_SUPPLIER_RATINGS" AS "ECO" ON 1=1
   AND ECO.integration_ids = LFA1.LIFNR
);

/*
P2P_LFA1_ECOVADIS calculated from temp table + risk excel sheet
*/
DROP TABLE IF EXISTS "P2P_LFA1_ECOVADIS";
CREATE TABLE "P2P_LFA1_ECOVADIS" AS (
SELECT DISTINCT
   P2P_LFA1_ECOVADIS_NO_RISK.*
   ,excel_sheet.RISK as "risk"
   FROM "P2P_LFA1_ECOVADIS_NO_RISK"
LEFT JOIN "supplier_risk_xlsx_Sheet1" AS "excel_sheet" ON excel_sheet.LIFNR = P2P_LFA1_ECOVADIS_NO_RISK.LIFNR
);