87 lines
4.6 KiB
SQL
87 lines
4.6 KiB
SQL
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",
|
|
''::character varying AS "strRegion",
|
|
''::character varying AS "strStrategyName",
|
|
''::character varying AS "strBookName",
|
|
''::character varying AS "strQuantityUom",
|
|
''::character varying AS "strIncotermCode",
|
|
NULL::date AS "dtmEstimatedBLDate"
|
|
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",
|
|
ad."strRegion",
|
|
strategy."strValueName" AS "strStrategyName",
|
|
book."strValueName" AS "strBookName",
|
|
pu1.name AS "strQuantityUom",
|
|
COALESCE(inc.code, ''::character varying) AS "strIncotermCode",
|
|
COALESCE(bl.estimated_date, pl.to_del) AS "dtmEstimatedBLDate"
|
|
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)))
|
|
JOIN product_uom pu1 ON ((pl.unit = pu1.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)))
|
|
LEFT JOIN vw_utility_last_counterparty_address ad ON ((pty.id = ad."intPartyId")))
|
|
LEFT JOIN vw_utility_strategy_dimension strategy ON (((pl.id = strategy."intTradeLineId") AND (strategy."strTradeCategory" = 'Physical'::text) AND (strategy."strTradeType" = 'Purchase'::text))))
|
|
LEFT JOIN vw_utility_book_dimension book ON (((pl.id = book."intTradeLineId") AND (book."strTradeCategory" = 'Physical'::text) AND (book."strTradeType" = 'Purchase'::text))))
|
|
LEFT JOIN pricing_estimated bl ON (((pl.id = bl.line) AND ((bl.trigger)::text = 'bldate'::text))))
|
|
WHERE ((1 = 1) AND ((pl.type)::text = 'line'::text) AND ((pc.line_type)::text = 'goods'::text));;
|