-- View: public.vw_utility_dim_physical_purchase_contract

-- DROP VIEW public.vw_utility_dim_physical_purchase_contract;

CREATE OR REPLACE VIEW public.vw_utility_dim_physical_purchase_contract AS
 SELECT 0 AS "intPurchaseLineId",
    ''::character varying AS "strContractNb",
    ''::character varying AS "strCounterparty",
    ''::character varying AS "strReference",
    NULL::date AS "dtmContractDate",
    ''::character varying AS "strContractStatus",
    ''::character varying AS "strPaymentTerm",
    ''::character varying AS "strCurrency",
    ''::character varying AS "strCertification",
    ''::character varying AS "strWeightBasis",
    ''::character varying AS "strBroker",
    ''::character varying AS "strCertif",
    ''::character varying AS "strAssociation",
    ''::character varying AS "strCrop",
    0 AS "dblTolMinPct",
    0 AS "dblTolMaxPct",
    ''::character varying AS "strIncoterm",
    ''::character varying AS "strLoadingPlace",
    ''::character varying AS "strDestinationPlace",
    ''::text AS "strProduct",
    ''::character varying AS "strDeliveryPeriod",
    NULL::date AS "dtmDeliveryDateFrom",
    NULL::date AS "dtmDeliveryDateTo"
UNION ALL
 SELECT pl.id AS "intPurchaseLineId",
    pc.number AS "strContractNb",
    pty.name AS "strCounterparty",
    pc.reference AS "strReference",
    pc.purchase_date AS "dtmContractDate",
        CASE
            WHEN pc.state::text = 'draft'::text THEN 'Draft'::text
            WHEN pc.state::text = 'confirmed'::text THEN 'Confirmed'::text
            WHEN pc.state::text = 'processing'::text THEN 'Processing'::text
            ELSE 'Validated'::text
        END AS "strContractStatus",
    COALESCE(pt.name, ''::character varying) AS "strPaymentTerm",
    cur.name AS "strCurrency",
    COALESCE(pc.certification, ''::character varying) AS "strCertification",
    COALESCE(wbs.name, ''::character varying) AS "strWeightBasis",
    COALESCE(brk.name, ''::character varying) AS "strBroker",
    COALESCE(cer.name, ''::character varying) AS "strCertif",
    COALESCE(ass.name, ''::character varying) AS "strAssociation",
    COALESCE(crp.name, ''::character varying) AS "strCrop",
    pc.tol_min AS "dblTolMinPct",
    pc.tol_max AS "dblTolMaxPct",
    COALESCE(inc.name, ''::character varying) AS "strIncoterm",
    COALESCE(src.name, ''::character varying) AS "strLoadingPlace",
    COALESCE(dst.name, ''::character varying) AS "strDestinationPlace",
    concat('[', pr."strProductCode", '] ', pr."strTemplate") AS "strProduct",
    COALESCE(pm.month_name) AS "strDeliveryPeriod",
    pl.from_del AS "dtmDeliveryDateFrom",
    pl.to_del AS "dtmDeliveryDateTo"
   FROM purchase_line pl
     JOIN purchase_purchase pc ON pl.purchase = pc.id
     JOIN party_party pty ON pc.party = pty.id
     JOIN currency_currency cur ON pc.currency = cur.id
     LEFT JOIN party_party brk ON pc.broker = brk.id
     LEFT JOIN account_invoice_payment_term pt ON pc.payment_term = pt.id
     LEFT JOIN purchase_crop crp ON pc.crop = crp.id
     LEFT JOIN purchase_certification cer ON pc.certif = cer.id
     LEFT JOIN purchase_association ass ON pc.association = ass.id
     LEFT JOIN purchase_weight_basis wbs ON pc.wb = wbs.id
     LEFT JOIN stock_location src ON pc.from_location = src.id
     LEFT JOIN stock_location dst ON pc.to_location = dst.id
     LEFT JOIN incoterm_incoterm inc ON pc.incoterm = inc.id
     JOIN vw_utility_product pr ON pl.product = pr."intProductId"
     LEFT JOIN product_month pm ON pl.del_period = pm.id
  WHERE 1 = 1 AND pl.type::text = 'line'::text AND pc.line_type::text = 'goods'::text;

ALTER TABLE public.vw_utility_dim_physical_purchase_contract
    OWNER TO postgres;