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:
 |
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 );