47 lines
2.2 KiB
SQL
47 lines
2.2 KiB
SQL
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;;
|