CREATE OR REPLACE VIEW public.vw_bi_dim_sale_physical_contract AS
 SELECT "intSaleLineId",
    "strContractNb" AS "Contract Nb",
    "strCounterparty" AS "Customer",
    "strReference" AS "Contract Ref",
    "dtmContractDate" AS "Contract Date",
        CASE
            WHEN (("strContractStatus")::text = 'Draft'::text) THEN ((("strContractStatus")::text || ' 🧾'::text))::character varying
            WHEN (("strContractStatus")::text = 'Confirmed'::text) THEN ((("strContractStatus")::text || ' ✅'::text))::character varying
            WHEN (("strContractStatus")::text = 'Validated'::text) THEN ((("strContractStatus")::text || ' ✔️'::text))::character varying
            WHEN (("strContractStatus")::text = 'Processing'::text) THEN ((("strContractStatus")::text || ' ⏳'::text))::character varying
            ELSE "strContractStatus"
        END AS "Contract Status",
    "strPaymentTerm" AS "Payment Terms",
    "strCurrency" AS "Currency",
    "strWeightBasis" AS "Weight Basis",
    "strBroker" AS "Broker",
    "strCertif" AS "Certification",
    "strAssociation" AS "Association",
    "strCrop" AS "Crop",
    "dblTolMinPct" AS "Tolerance Min %",
    "dblTolMaxPct" AS "Tolerance Max %",
    "strIncoterm" AS "Inco Terms",
    "strLoadingPlace" AS "Loading Place",
    "strDestinationPlace" AS "Destination Place",
    "strProduct" AS "Product",
    "strDeliveryPeriod" AS "Delivery Period",
    "strQuantityUom" AS "Quantity UOM",
    "dtmDeliveryDateFrom" AS "Delivery Date From",
    "dtmDeliveryDateTo" AS "Delivery Date To",
        CASE
            WHEN ("dtmDeliveryDateTo" < CURRENT_DATE) THEN 'Overdue'::text
            WHEN (("dtmDeliveryDateFrom" <= CURRENT_DATE) AND ("dtmDeliveryDateTo" >= CURRENT_DATE)) THEN 'Current'::text
            WHEN ("dtmDeliveryDateFrom" > CURRENT_DATE) THEN 'Future'::text
            ELSE 'Unknown'::text
        END AS "Delivery Status",
    "strRegion" AS "Bassin",
    "strBookName" AS "Book",
    "strStrategyName" AS "Strategy",
        CASE
            WHEN (("strBookName")::text ~~ '%Projection%'::text) THEN 'Yes'::text
            ELSE 'No'::text
        END AS "Is Projection",
    "strIncotermCode" AS "Incoterm Code",
    "dtmEstimatedBLDate" AS "Estimated BL Date"
   FROM vw_utility_dim_physical_sale_contract dpc;;