560 lines
32 KiB
PL/PgSQL
560 lines
32 KiB
PL/PgSQL
-- Script generated by Redgate Compare v1.45.1.2004
|
|
SET check_function_bodies = false;
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_stock_movements...';END$$;
|
|
CREATE VIEW public.vw_utility_stock_movements ("intStockId", "intCompanyId", "intLotId", "dblCostPrice", "strCurrency", "dtmEffectiveDate", "dtmBLDate", "intProductId", "intFromLocationId", "intToLocationId", origin, "strSourceType", "intShipmentId", "strMovementType", "intPurchaseLineId", "intSaleLineId", "dblQuantity", "strUnit", "dblQuantityKg", unit_price, "ysnPriceUpdated", "strState") AS SELECT sm.id AS "intStockId",
|
|
sm.company AS "intCompanyId",
|
|
COALESCE(sm.lot, 0) AS "intLotId",
|
|
COALESCE(sm.cost_price, (0)::numeric) AS "dblCostPrice",
|
|
cur.name AS "strCurrency",
|
|
sm.effective_date AS "dtmEffectiveDate",
|
|
sm.bldate AS "dtmBLDate",
|
|
sm.product AS "intProductId",
|
|
sm.from_location AS "intFromLocationId",
|
|
sm.to_location AS "intToLocationId",
|
|
sm.origin,
|
|
split_part((sm.origin)::text, ','::text, 1) AS "strSourceType",
|
|
COALESCE((split_part((sm.shipment)::text, ','::text, 2))::integer, 0) AS "intShipmentId",
|
|
CASE
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'purchase.line'::text) THEN 'Inbound'::text
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'sale.line'::text) THEN 'Outbound'::text
|
|
ELSE 'Unknown'::text
|
|
END AS "strMovementType",
|
|
CASE
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'purchase.line'::text) THEN (split_part((sm.origin)::text, ','::text, 2))::integer
|
|
ELSE 0
|
|
END AS "intPurchaseLineId",
|
|
CASE
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'sale.line'::text) THEN (split_part((sm.origin)::text, ','::text, 2))::integer
|
|
ELSE 0
|
|
END AS "intSaleLineId",
|
|
sm.quantity AS "dblQuantity",
|
|
uom.name AS "strUnit",
|
|
round(((sm.quantity * uom.factor))::numeric, public.get_rounding_position((uom.rounding)::numeric)) AS "dblQuantityKg",
|
|
sm.unit_price,
|
|
sm.unit_price_updated AS "ysnPriceUpdated",
|
|
sm.state AS "strState"
|
|
FROM ((public.stock_move sm
|
|
JOIN public.currency_currency cur ON ((sm.currency = cur.id)))
|
|
LEFT JOIN public.product_uom uom ON ((sm.unit = uom.id)));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_shipment_in...';END$$;
|
|
CREATE VIEW public.vw_utility_shipment_in ("intShipmentId", "dtmEffectiveDate", "strBookingNumber", "strShipmentNb", "strShipmentReference", "strBillOfLading", "dtmBillOfLadingDate", "dtmETADestination", "dtmOriginPlannedDate", "dtmPlannedDate", "strState", "strSupplier", "strWarehouse", "strWarehouseInput", "strStorage", "strFromLocation", "strToLocation", "strToLocationType", "strTransportType", "strVessel", "strVesselIMO", "strVesselYearOfBuilt", "strCarrier", "dtmETAPol", "dtmETD", "dtmETL", "dtmUnloadingDate", "dtmBookingDate", "strReceiveNumber", "dtmReceiveDate", "strCargoMode") AS SELECT 0 AS "intShipmentId",
|
|
NULL::date AS "dtmEffectiveDate",
|
|
''::character varying AS "strBookingNumber",
|
|
''::character varying AS "strShipmentNb",
|
|
''::character varying AS "strShipmentReference",
|
|
''::character varying AS "strBillOfLading",
|
|
NULL::date AS "dtmBillOfLadingDate",
|
|
NULL::date AS "dtmETADestination",
|
|
NULL::date AS "dtmOriginPlannedDate",
|
|
NULL::date AS "dtmPlannedDate",
|
|
''::character varying AS "strState",
|
|
''::character varying AS "strSupplier",
|
|
''::character varying AS "strWarehouse",
|
|
''::character varying AS "strWarehouseInput",
|
|
''::character varying AS "strStorage",
|
|
''::character varying AS "strFromLocation",
|
|
''::character varying AS "strToLocation",
|
|
''::character varying AS "strToLocationType",
|
|
''::character varying AS "strTransportType",
|
|
''::character varying AS "strVessel",
|
|
''::character varying AS "strVesselIMO",
|
|
''::character varying AS "strVesselYearOfBuilt",
|
|
''::character varying AS "strCarrier",
|
|
NULL::date AS "dtmETAPol",
|
|
NULL::date AS "dtmETD",
|
|
NULL::date AS "dtmETL",
|
|
NULL::date AS "dtmUnloadingDate",
|
|
NULL::date AS "dtmBookingDate",
|
|
''::character varying AS "strReceiveNumber",
|
|
NULL::date AS "dtmReceiveDate",
|
|
''::character varying AS "strCargoMode"
|
|
UNION ALL
|
|
SELECT ssin.id AS "intShipmentId",
|
|
ssin.effective_date AS "dtmEffectiveDate",
|
|
ssin.booking AS "strBookingNumber",
|
|
ssin.number AS "strShipmentNb",
|
|
ssin.reference AS "strShipmentReference",
|
|
ssin.bl_number AS "strBillOfLading",
|
|
ssin.bl_date AS "dtmBillOfLadingDate",
|
|
ssin.etad AS "dtmETADestination",
|
|
ssin.origin_planned_date AS "dtmOriginPlannedDate",
|
|
ssin.planned_date AS "dtmPlannedDate",
|
|
ssin.state AS "strState",
|
|
sup.name AS "strSupplier",
|
|
wh.name AS "strWarehouse",
|
|
inp.name AS "strWarehouseInput",
|
|
sto.name AS "strStorage",
|
|
src.name AS "strFromLocation",
|
|
dest.name AS "strToLocation",
|
|
dest.type AS "strToLocationType",
|
|
ssin.transport_type AS "strTransportType",
|
|
vessel.vessel_name AS "strVessel",
|
|
vessel.vessel_imo AS "strVesselIMO",
|
|
vessel.vessel_year AS "strVesselYearOfBuilt",
|
|
car.name AS "strCarrier",
|
|
ssin.eta AS "dtmETAPol",
|
|
ssin.etd AS "dtmETD",
|
|
ssin.etl AS "dtmETL",
|
|
ssin.unloaded AS "dtmUnloadingDate",
|
|
ssin.booking_date AS "dtmBookingDate",
|
|
ssin.receive_nb AS "strReceiveNumber",
|
|
ssin.receive_date AS "dtmReceiveDate",
|
|
ssin.cargo_mode AS "strCargoMode"
|
|
FROM ((((((((public.stock_shipment_in ssin
|
|
LEFT JOIN public.party_party sup ON ((ssin.supplier = sup.id)))
|
|
LEFT JOIN public.stock_location wh ON ((ssin.warehouse = wh.id)))
|
|
LEFT JOIN public.stock_location inp ON ((ssin.warehouse_input = inp.id)))
|
|
LEFT JOIN public.stock_location sto ON ((ssin.warehouse_storage = sto.id)))
|
|
LEFT JOIN public.stock_location src ON ((ssin.from_location = src.id)))
|
|
LEFT JOIN public.stock_location dest ON ((ssin.to_location = dest.id)))
|
|
LEFT JOIN public.trade_vessel vessel ON ((ssin.vessel = vessel.id)))
|
|
LEFT JOIN public.party_party car ON ((ssin.carrier_ = car.id)));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_product...';END$$;
|
|
CREATE VIEW public.vw_utility_product ("intProductId", "strProductCode", "strProductDescription", "ysnActive", "strTemplate", "strTemplateCode", "strDefaultUom", "dblDefaultUomFactor", "dblDefaultUomRate", "dblDefaultUomRounding", "strDefaultUomSymbol", "ysnAvailableOnPurchase", "strPurchaseUom", "dblPurchaseUomFactor", "dblPurchaseUomRate", "dblPurchaseUomRounding", "strPurchaseUomSymbol", "ysnAvailableOnSale", "strSaleUom", "dblSaleUomFactor", "dblSaleUomRate", "dblSaleUomRounding", "strSaleUomSymbol") AS SELECT 0 AS "intProductId",
|
|
''::character varying AS "strProductCode",
|
|
''::text AS "strProductDescription",
|
|
true AS "ysnActive",
|
|
''::character varying AS "strTemplate",
|
|
''::character varying AS "strTemplateCode",
|
|
''::character varying AS "strDefaultUom",
|
|
0 AS "dblDefaultUomFactor",
|
|
0 AS "dblDefaultUomRate",
|
|
0 AS "dblDefaultUomRounding",
|
|
''::character varying(10) AS "strDefaultUomSymbol",
|
|
true AS "ysnAvailableOnPurchase",
|
|
''::character varying AS "strPurchaseUom",
|
|
0 AS "dblPurchaseUomFactor",
|
|
0 AS "dblPurchaseUomRate",
|
|
0 AS "dblPurchaseUomRounding",
|
|
''::character varying(10) AS "strPurchaseUomSymbol",
|
|
true AS "ysnAvailableOnSale",
|
|
''::character varying AS "strSaleUom",
|
|
0 AS "dblSaleUomFactor",
|
|
0 AS "dblSaleUomRate",
|
|
0 AS "dblSaleUomRounding",
|
|
''::character varying(10) AS "strSaleUomSymbol"
|
|
UNION ALL
|
|
SELECT p.id AS "intProductId",
|
|
p.code AS "strProductCode",
|
|
COALESCE(p.description, ''::text) AS "strProductDescription",
|
|
p.active AS "ysnActive",
|
|
pt.name AS "strTemplate",
|
|
pt.type AS "strTemplateCode",
|
|
pu1.name AS "strDefaultUom",
|
|
pu1.factor AS "dblDefaultUomFactor",
|
|
pu1.rate AS "dblDefaultUomRate",
|
|
pu1.rounding AS "dblDefaultUomRounding",
|
|
pu1.symbol AS "strDefaultUomSymbol",
|
|
pt.purchasable AS "ysnAvailableOnPurchase",
|
|
pu2.name AS "strPurchaseUom",
|
|
pu2.factor AS "dblPurchaseUomFactor",
|
|
pu2.rate AS "dblPurchaseUomRate",
|
|
pu2.rounding AS "dblPurchaseUomRounding",
|
|
pu2.symbol AS "strPurchaseUomSymbol",
|
|
pt.salable AS "ysnAvailableOnSale",
|
|
pu3.name AS "strSaleUom",
|
|
pu3.factor AS "dblSaleUomFactor",
|
|
pu3.rate AS "dblSaleUomRate",
|
|
pu3.rounding AS "dblSaleUomRounding",
|
|
pu3.symbol AS "strSaleUomSymbol"
|
|
FROM ((((public.product_product p
|
|
JOIN public.product_template pt ON ((p.template = pt.id)))
|
|
JOIN public.product_uom pu1 ON ((pt.default_uom = pu1.id)))
|
|
JOIN public.product_uom pu2 ON ((pt.purchase_uom = pu2.id)))
|
|
JOIN public.product_uom pu3 ON ((pt.sale_uom = pu3.id)))
|
|
WHERE (1 = 1);
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_lot...';END$$;
|
|
CREATE VIEW public.vw_utility_lot ("intLotId", "strContainer", "strLotName", "strStatus", "strType", "strAvailability", "intPurchaseLineId", "intSaleLineId", "intProductId", "intLotStateId", "intUnitId", "intUnitLineId", "dblQuantity", "intInvoiceLineId", "intInvoiceLineProvId", "strPurchaseInvoiceState", "strSaleInvoiceState", "intShipmentInId", "intShipmentInternalId", "intShipmentOutId", "intSaleInvoiceLineId", "intSaleInvoiceLineProvId") AS SELECT id AS "intLotId",
|
|
COALESCE(lot_container, ''::character varying) AS "strContainer",
|
|
COALESCE(lot_name, ''::character varying) AS "strLotName",
|
|
COALESCE(lot_status, ''::character varying) AS "strStatus",
|
|
COALESCE(lot_type, ''::character varying) AS "strType",
|
|
COALESCE(lot_av, ''::character varying) AS "strAvailability",
|
|
line AS "intPurchaseLineId",
|
|
sale_line AS "intSaleLineId",
|
|
lot_product AS "intProductId",
|
|
lot_state AS "intLotStateId",
|
|
lot_unit AS "intUnitId",
|
|
lot_unit_line AS "intUnitLineId",
|
|
lot_qt AS "dblQuantity",
|
|
invoice_line AS "intInvoiceLineId",
|
|
invoice_line_prov AS "intInvoiceLineProvId",
|
|
COALESCE(lot_pur_inv_state, ''::character varying) AS "strPurchaseInvoiceState",
|
|
COALESCE(lot_sale_inv_state, ''::character varying) AS "strSaleInvoiceState",
|
|
lot_shipment_in AS "intShipmentInId",
|
|
lot_shipment_internal AS "intShipmentInternalId",
|
|
lot_shipment_out AS "intShipmentOutId",
|
|
sale_invoice_line AS "intSaleInvoiceLineId",
|
|
sale_invoice_line_prov AS "intSaleInvoiceLineProvId"
|
|
FROM public.lot_lot lot
|
|
WHERE (1 = 1);
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_location...';END$$;
|
|
CREATE VIEW public.vw_utility_location ("intLocationId", "strLocationName", "strLocationType", "ysnIsActive") AS SELECT 0 AS "intLocationId",
|
|
''::character varying AS "strLocationName",
|
|
''::character varying AS "strLocationType",
|
|
true AS "ysnIsActive"
|
|
UNION ALL
|
|
SELECT sl.id AS "intLocationId",
|
|
sl.name AS "strLocationName",
|
|
sl.type AS "strLocationType",
|
|
sl.active AS "ysnIsActive"
|
|
FROM public.stock_location sl;
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_received_lots...';END$$;
|
|
CREATE VIEW public.vw_utility_received_lots ("intCompanyId", "intPurchaseLineId", "intShipmentId", "intLotId", "dblQuantity", "strUnit", "dblQuantityKg") AS SELECT fm."intCompanyId",
|
|
fm."intPurchaseLineId",
|
|
fm."intShipmentId",
|
|
fm."intLotId",
|
|
fm."dblQuantity",
|
|
fm."strUnit",
|
|
fm."dblQuantityKg"
|
|
FROM ((public.vw_utility_stock_movements fm
|
|
JOIN public.vw_utility_shipment_in ds ON ((fm."intShipmentId" = ds."intShipmentId")))
|
|
JOIN public.vw_utility_location dl ON ((fm."intToLocationId" = dl."intLocationId")))
|
|
WHERE ((1 = 1) AND (fm."intLotId" > 0) AND ((ds."strToLocationType")::text = 'storage'::text) AND ((dl."strLocationType")::text = 'storage'::text) AND ((dl."strLocationName")::text = 'Transit'::text) AND ((fm."strState")::text = 'done'::text) AND (EXISTS ( SELECT fm2."intPurchaseLineId",
|
|
fm2."intShipmentId",
|
|
fm2."intLotId",
|
|
fm2."dblQuantity"
|
|
FROM ((public.vw_utility_stock_movements fm2
|
|
JOIN public.vw_utility_shipment_in ds2 ON ((fm2."intShipmentId" = ds2."intShipmentId")))
|
|
JOIN public.vw_utility_location dl2 ON ((fm2."intFromLocationId" = dl2."intLocationId")))
|
|
WHERE ((1 = 1) AND (fm2."intLotId" > 0) AND ((ds2."strToLocationType")::text = 'storage'::text) AND ((dl2."strLocationType")::text = 'storage'::text) AND ((dl2."strLocationName")::text = 'Transit'::text) AND ((fm2."strState")::text = 'done'::text) AND (fm2."intLotId" = fm."intLotId") AND (fm2."intPurchaseLineId" = fm."intPurchaseLineId") AND (fm2."intShipmentId" = fm."intShipmentId")))));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_invoices...';END$$;
|
|
CREATE VIEW public.vw_utility_invoices ("intInvoiceId", "intCompanyId", "strCompanyName", "strInvoiceNumber", "dtmInvoiceDate", "strPartyName", "strInvoiceType", "strPaymentTerm", "dtmAccountingDate", "intCurrencyId", "strCurrencyCode", "strInvoiceState", "intMoveId", "intAccountId", "strAccountName") AS SELECT ai.id AS "intInvoiceId",
|
|
ai.company AS "intCompanyId",
|
|
cparty.name AS "strCompanyName",
|
|
ai.number AS "strInvoiceNumber",
|
|
ai.invoice_date AS "dtmInvoiceDate",
|
|
p.name AS "strPartyName",
|
|
ai.type AS "strInvoiceType",
|
|
aipt.name AS "strPaymentTerm",
|
|
ai.accounting_date AS "dtmAccountingDate",
|
|
ai.currency AS "intCurrencyId",
|
|
cc.name AS "strCurrencyCode",
|
|
ai.state AS "strInvoiceState",
|
|
ai.move AS "intMoveId",
|
|
ai.account AS "intAccountId",
|
|
aa.name AS "strAccountName"
|
|
FROM ((((((public.account_invoice ai
|
|
JOIN public.currency_currency cc ON ((ai.currency = cc.id)))
|
|
JOIN public.party_party p ON ((ai.party = p.id)))
|
|
JOIN public.company_company comp ON ((ai.company = comp.id)))
|
|
JOIN public.party_party cparty ON ((comp.party = cparty.id)))
|
|
JOIN public.account_invoice_payment_term aipt ON ((ai.payment_term = aipt.id)))
|
|
LEFT JOIN public.account_account aa ON ((ai.account = aa.id)))
|
|
WHERE (1 = 1);
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_intransit_lots...';END$$;
|
|
CREATE VIEW public.vw_utility_intransit_lots ("intCompanyId", "intPurchaseLineId", "intShipmentId", "intLotId", "dblQuantity", "strUnit", "dblQuantityKg") AS SELECT fm."intCompanyId",
|
|
fm."intPurchaseLineId",
|
|
fm."intShipmentId",
|
|
fm."intLotId",
|
|
fm."dblQuantity",
|
|
fm."strUnit",
|
|
fm."dblQuantityKg"
|
|
FROM ((public.vw_utility_stock_movements fm
|
|
JOIN public.vw_utility_shipment_in ds ON ((fm."intShipmentId" = ds."intShipmentId")))
|
|
JOIN public.vw_utility_location dl ON ((fm."intToLocationId" = dl."intLocationId")))
|
|
WHERE ((1 = 1) AND (fm."intLotId" > 0) AND ((ds."strToLocationType")::text = 'storage'::text) AND ((dl."strLocationType")::text = 'storage'::text) AND ((dl."strLocationName")::text = 'Transit'::text) AND ((fm."strState")::text = 'done'::text) AND (NOT (EXISTS ( SELECT fm2."intPurchaseLineId",
|
|
fm2."intShipmentId",
|
|
fm2."intLotId",
|
|
fm2."dblQuantity"
|
|
FROM ((public.vw_utility_stock_movements fm2
|
|
JOIN public.vw_utility_shipment_in ds2 ON ((fm2."intShipmentId" = ds2."intShipmentId")))
|
|
JOIN public.vw_utility_location dl2 ON ((fm2."intFromLocationId" = dl2."intLocationId")))
|
|
WHERE ((1 = 1) AND (fm2."intLotId" > 0) AND ((ds2."strToLocationType")::text = 'storage'::text) AND ((dl2."strLocationType")::text = 'storage'::text) AND ((dl2."strLocationName")::text = 'Transit'::text) AND ((fm2."strState")::text = 'done'::text) AND (fm2."intLotId" = fm."intLotId") AND (fm2."intPurchaseLineId" = fm."intPurchaseLineId") AND (fm2."intShipmentId" = fm."intShipmentId"))))));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_instructed_lots...';END$$;
|
|
CREATE VIEW public.vw_utility_instructed_lots ("intLotId", "intShipmentId", "intPurchaseLineId", "dblQuantity", "strUnit", "dblQuantityKg") AS SELECT mvt."intLotId",
|
|
mvt."intShipmentId",
|
|
mvt."intPurchaseLineId",
|
|
mvt."dblQuantity",
|
|
mvt."strUnit",
|
|
mvt."dblQuantityKg"
|
|
FROM (public.vw_utility_stock_movements mvt
|
|
JOIN public.vw_utility_shipment_in spt ON ((mvt."intShipmentId" = spt."intShipmentId")))
|
|
WHERE ((1 = 1) AND (mvt."strMovementType" = 'Inbound'::text) AND ((spt."strState")::text = 'draft'::text));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_dropshipped_lots...';END$$;
|
|
CREATE VIEW public.vw_utility_dropshipped_lots ("intCompanyId", "intPurchaseLineId", "intShipmentId", "intLotId", "dblQuantity", "strUnit", "dblQuantityKg") AS SELECT fm."intCompanyId",
|
|
fm."intPurchaseLineId",
|
|
fm."intShipmentId",
|
|
fm."intLotId",
|
|
fm."dblQuantity",
|
|
fm."strUnit",
|
|
fm."dblQuantityKg"
|
|
FROM ((public.vw_utility_stock_movements fm
|
|
JOIN public.vw_utility_shipment_in ds ON ((fm."intShipmentId" = ds."intShipmentId")))
|
|
JOIN public.vw_utility_location dl ON ((fm."intToLocationId" = dl."intLocationId")))
|
|
WHERE ((1 = 1) AND (fm."intLotId" > 0) AND ((ds."strToLocationType")::text <> 'storage'::text) AND ((dl."strLocationType")::text <> 'storage'::text) AND ((fm."strState")::text = 'done'::text));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_purchase_physical_contract...';END$$;
|
|
CREATE VIEW public.vw_utility_purchase_physical_contract ("intPurchaseLineId", "dtmDeliveryDateFrom", "dtmDeliveryDateTo", "dblQuantity", "strPurchaseUom", "dblInTransitQuantity", "dblDropshippedQuantity", "dblReceivedQuantity", "dblPrice", "strPriceUnit", "strPriceCurrency", "dblUnitPrice", "dblTheoriticalQuantity", "dblLineAmount", "strCurrency", "dblLineBaseAmount", "strBaseCurrency", "dblQuantityInBaseUnit", "strBaseUnit", "dblFixedPrice", "dblFixedQuantity", "dblFixedAmount", "dblUnFixedPrice", "dblUnfixedQuantity", "dblUnfixedAmount", "dblInvoicedQuantity", "dblInvoicedAmount", "strInvoiceCurrency", "dblPhysicalQty", "dblOpenQuantity", "dblInInstructedQuantity") AS SELECT pl.id AS "intPurchaseLineId",
|
|
pl.from_del AS "dtmDeliveryDateFrom",
|
|
pl.to_del AS "dtmDeliveryDateTo",
|
|
pl.quantity AS "dblQuantity",
|
|
pu1.name AS "strPurchaseUom",
|
|
COALESCE(vult.dblintransitquantity, (0)::double precision) AS "dblInTransitQuantity",
|
|
COALESCE(dropship.dbldropshippedquantity, (0)::double precision) AS "dblDropshippedQuantity",
|
|
COALESCE(received.dblreceivedquantity, (0)::double precision) AS "dblReceivedQuantity",
|
|
pl.linked_price AS "dblPrice",
|
|
pu2.name AS "strPriceUnit",
|
|
'n/a'::text AS "strPriceCurrency",
|
|
pl.unit_price AS "dblUnitPrice",
|
|
pl.quantity_theorical AS "dblTheoriticalQuantity",
|
|
round(((pl.quantity * (pl.unit_price)::double precision))::numeric, public.get_rounding_position(mcr.rounding)) AS "dblLineAmount",
|
|
cur.name AS "strCurrency",
|
|
(0)::numeric AS "dblLineBaseAmount",
|
|
''::text AS "strBaseCurrency",
|
|
(0)::numeric AS "dblQuantityInBaseUnit",
|
|
''::text AS "strBaseUnit",
|
|
(0)::numeric AS "dblFixedPrice",
|
|
(0)::numeric AS "dblFixedQuantity",
|
|
(0)::numeric AS "dblFixedAmount",
|
|
(0)::numeric AS "dblUnFixedPrice",
|
|
(0)::numeric AS "dblUnfixedQuantity",
|
|
(0)::numeric AS "dblUnfixedAmount",
|
|
(0)::numeric AS "dblInvoicedQuantity",
|
|
(0)::numeric AS "dblInvoicedAmount",
|
|
''::text AS "strInvoiceCurrency",
|
|
COALESCE(phys.dblphysicalqty, (0)::double precision) AS "dblPhysicalQty",
|
|
((pl.quantity_theorical)::double precision - COALESCE(phys.dblphysicalqty, (0)::double precision)) AS "dblOpenQuantity",
|
|
COALESCE(inst.dblinstructedqty, (0)::double precision) AS "dblInInstructedQuantity"
|
|
FROM ((((((((((public.purchase_line pl
|
|
JOIN public.purchase_purchase pc ON ((pl.purchase = pc.id)))
|
|
JOIN public.product_uom pu1 ON ((pl.unit = pu1.id)))
|
|
LEFT JOIN public.product_uom pu2 ON ((pl.linked_unit = pu2.id)))
|
|
LEFT JOIN public.currency_linked cur ON ((pl.linked_currency = cur.id)))
|
|
LEFT JOIN public.currency_currency mcr ON ((cur.currency = mcr.id)))
|
|
LEFT JOIN ( SELECT transit."intPurchaseLineId",
|
|
sum(transit."dblQuantity") AS dblintransitquantity
|
|
FROM public.vw_utility_intransit_lots transit
|
|
GROUP BY transit."intPurchaseLineId") vult ON ((pl.id = vult."intPurchaseLineId")))
|
|
LEFT JOIN ( SELECT t1."intPurchaseLineId",
|
|
sum(t1."dblQuantity") AS dbldropshippedquantity
|
|
FROM public.vw_utility_dropshipped_lots t1
|
|
GROUP BY t1."intPurchaseLineId") dropship ON ((pl.id = dropship."intPurchaseLineId")))
|
|
LEFT JOIN ( SELECT t1."intPurchaseLineId",
|
|
sum(t1."dblQuantity") AS dblreceivedquantity
|
|
FROM public.vw_utility_received_lots t1
|
|
GROUP BY t1."intPurchaseLineId") received ON ((pl.id = received."intPurchaseLineId")))
|
|
LEFT JOIN ( SELECT t1."intPurchaseLineId",
|
|
sum(t1."dblQuantity") AS dblphysicalqty
|
|
FROM public.vw_utility_lot t1
|
|
WHERE ((t1."strType")::text = 'physic'::text)
|
|
GROUP BY t1."intPurchaseLineId") phys ON ((pl.id = phys."intPurchaseLineId")))
|
|
LEFT JOIN ( SELECT t1."intPurchaseLineId",
|
|
sum(t1."dblQuantity") AS dblinstructedqty
|
|
FROM public.vw_utility_instructed_lots t1
|
|
GROUP BY t1."intPurchaseLineId") inst ON ((pl.id = inst."intPurchaseLineId")))
|
|
WHERE (1 = 1);
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_dim_physical_sale_contract...';END$$;
|
|
CREATE VIEW public.vw_utility_dim_physical_sale_contract ("intSaleLineId", "strContractNb", "strCounterparty", "strReference", "dtmContractDate", "strContractStatus", "strPaymentTerm", "strCurrency", "strCertification", "strWeightBasis", "strBroker", "strCertif", "strAssociation", "strCrop", "dblTolMinPct", "dblTolMaxPct", "strIncoterm", "strLoadingPlace", "strDestinationPlace", "strProduct", "strDeliveryPeriod", "dtmDeliveryDateFrom", "dtmDeliveryDateTo") AS SELECT 0 AS "intSaleLineId",
|
|
''::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 sl.id AS "intSaleLineId",
|
|
sc.number AS "strContractNb",
|
|
pty.name AS "strCounterparty",
|
|
sc.reference AS "strReference",
|
|
sc.sale_date AS "dtmContractDate",
|
|
sc.state AS "strContractStatus",
|
|
COALESCE(pt.name, ''::character varying) AS "strPaymentTerm",
|
|
cur.name AS "strCurrency",
|
|
COALESCE(sc.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",
|
|
sc.tol_min AS "dblTolMinPct",
|
|
sc.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",
|
|
sl.from_del AS "dtmDeliveryDateFrom",
|
|
sl.to_del AS "dtmDeliveryDateTo"
|
|
FROM ((((((((((((((public.sale_line sl
|
|
JOIN public.sale_sale sc ON ((sl.sale = sc.id)))
|
|
JOIN public.party_party pty ON ((sc.party = pty.id)))
|
|
JOIN public.currency_currency cur ON ((sc.currency = cur.id)))
|
|
LEFT JOIN public.party_party brk ON ((sc.broker = brk.id)))
|
|
LEFT JOIN public.account_invoice_payment_term pt ON ((sc.payment_term = pt.id)))
|
|
LEFT JOIN public.purchase_crop crp ON ((sc.crop = crp.id)))
|
|
LEFT JOIN public.purchase_certification cer ON ((sc.certif = cer.id)))
|
|
LEFT JOIN public.purchase_association ass ON ((sc.association = ass.id)))
|
|
LEFT JOIN public.purchase_weight_basis wbs ON ((sc.wb = wbs.id)))
|
|
LEFT JOIN public.stock_location src ON ((sc.from_location = src.id)))
|
|
LEFT JOIN public.stock_location dst ON ((sc.to_location = dst.id)))
|
|
LEFT JOIN public.incoterm_incoterm inc ON ((sc.incoterm = inc.id)))
|
|
JOIN public.vw_utility_product pr ON ((sl.product = pr."intProductId")))
|
|
LEFT JOIN public.product_month pm ON ((sl.del_period = pm.id)))
|
|
WHERE ((1 = 1) AND ((sl.type)::text = 'line'::text));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_utility_dim_physical_purchase_contract...';END$$;
|
|
CREATE VIEW public.vw_utility_dim_physical_purchase_contract ("intPurchaseLineId", "strContractNb", "strCounterparty", "strReference", "dtmContractDate", "strContractStatus", "strPaymentTerm", "strCurrency", "strCertification", "strWeightBasis", "strBroker", "strCertif", "strAssociation", "strCrop", "dblTolMinPct", "dblTolMaxPct", "strIncoterm", "strLoadingPlace", "strDestinationPlace", "strProduct", "strDeliveryPeriod", "dtmDeliveryDateFrom", "dtmDeliveryDateTo") 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 ((((((((((((((public.purchase_line pl
|
|
JOIN public.purchase_purchase pc ON ((pl.purchase = pc.id)))
|
|
JOIN public.party_party pty ON ((pc.party = pty.id)))
|
|
JOIN public.currency_currency cur ON ((pc.currency = cur.id)))
|
|
LEFT JOIN public.party_party brk ON ((pc.broker = brk.id)))
|
|
LEFT JOIN public.account_invoice_payment_term pt ON ((pc.payment_term = pt.id)))
|
|
LEFT JOIN public.purchase_crop crp ON ((pc.crop = crp.id)))
|
|
LEFT JOIN public.purchase_certification cer ON ((pc.certif = cer.id)))
|
|
LEFT JOIN public.purchase_association ass ON ((pc.association = ass.id)))
|
|
LEFT JOIN public.purchase_weight_basis wbs ON ((pc.wb = wbs.id)))
|
|
LEFT JOIN public.stock_location src ON ((pc.from_location = src.id)))
|
|
LEFT JOIN public.stock_location dst ON ((pc.to_location = dst.id)))
|
|
LEFT JOIN public.incoterm_incoterm inc ON ((pc.incoterm = inc.id)))
|
|
JOIN public.vw_utility_product pr ON ((pl.product = pr."intProductId")))
|
|
LEFT JOIN public.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));
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_bi_fct_purchase_physical_contract...';END$$;
|
|
CREATE VIEW public.vw_bi_fct_purchase_physical_contract ("PurchaseLineId", "Delivery Date From", "Delivery DateTo", "Quantity", "In Transit Quantity", "Dropship Quantity", "Received Quantity", "Price", "Unit Price", "Theoretical Quantity", "Line Amount", "Physical Quantity", "Open Quantity", "Instructed Quantity") AS SELECT "intPurchaseLineId" AS "PurchaseLineId",
|
|
"dtmDeliveryDateFrom" AS "Delivery Date From",
|
|
"dtmDeliveryDateTo" AS "Delivery DateTo",
|
|
"dblQuantity" AS "Quantity",
|
|
"dblInTransitQuantity" AS "In Transit Quantity",
|
|
"dblDropshippedQuantity" AS "Dropship Quantity",
|
|
"dblReceivedQuantity" AS "Received Quantity",
|
|
"dblPrice" AS "Price",
|
|
"dblUnitPrice" AS "Unit Price",
|
|
"dblTheoriticalQuantity" AS "Theoretical Quantity",
|
|
"dblLineAmount" AS "Line Amount",
|
|
"dblPhysicalQty" AS "Physical Quantity",
|
|
"dblOpenQuantity" AS "Open Quantity",
|
|
"dblInInstructedQuantity" AS "Instructed Quantity"
|
|
FROM public.vw_utility_purchase_physical_contract pc;
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_bi_dim_purchase_physical_contract...';END$$;
|
|
CREATE VIEW public.vw_bi_dim_purchase_physical_contract ("intPurchaseLineId", "Contract Nb", "Supplier", "Contract Ref", "Contract Date", "Contract Status", "Payment Terms", "Currency", "Weight Basis", "Broker", "Certification", "Association", "Crop", "Tolerance Min %", "Tolerance Max %", "Inco Terms", "Loading Place", "Destination Place", "Product", "Delivery Period") AS SELECT "intPurchaseLineId",
|
|
"strContractNb" AS "Contract Nb",
|
|
"strCounterparty" AS "Supplier",
|
|
"strReference" AS "Contract Ref",
|
|
"dtmContractDate" AS "Contract Date",
|
|
"strContractStatus" 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"
|
|
FROM public.vw_utility_dim_physical_purchase_contract dpc;
|
|
|
|
|
|
DO LANGUAGE plpgsql $$BEGIN RAISE NOTICE 'Creating public.vw_bi_dim_product...';END$$;
|
|
CREATE VIEW public.vw_bi_dim_product ("intProductId", "Product Code", "Description", "Is Active", "Template", "Template Code", "Default Uom") AS SELECT "intProductId",
|
|
"strProductCode" AS "Product Code",
|
|
"strProductDescription" AS "Description",
|
|
"ysnActive" AS "Is Active",
|
|
"strTemplate" AS "Template",
|
|
"strTemplateCode" AS "Template Code",
|
|
"strDefaultUom" AS "Default Uom"
|
|
FROM public.vw_utility_product pr;
|
|
|
|
SET check_function_bodies = true;
|