45 lines
2.0 KiB
SQL
45 lines
2.0 KiB
SQL
-- View: public.vw_bi_dim_sale_physical_contract
|
|
|
|
-- DROP VIEW public.vw_bi_dim_sale_physical_contract;
|
|
|
|
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"
|
|
FROM vw_utility_dim_physical_sale_contract dpc;
|
|
|
|
ALTER TABLE public.vw_bi_dim_sale_physical_contract
|
|
OWNER TO postgres;
|