35 lines
1.8 KiB
SQL
35 lines
1.8 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_lot AS
|
|
SELECT lot.id AS "intLotId",
|
|
COALESCE(lot.lot_container, ''::character varying) AS "strContainer",
|
|
COALESCE(lot.lot_name, ''::character varying) AS "strLotName",
|
|
COALESCE(lot.lot_status, ''::character varying) AS "strStatus",
|
|
COALESCE(lot.lot_type, ''::character varying) AS "strType",
|
|
COALESCE(lot.lot_av, ''::character varying) AS "strAvailability",
|
|
lot.line AS "intPurchaseLineId",
|
|
lot.sale_line AS "intSaleLineId",
|
|
lot.lot_product AS "intProductId",
|
|
lot.lot_state AS "intLotStateId",
|
|
lot.lot_unit AS "intUnitId",
|
|
lot.lot_unit_line AS "intUnitLineId",
|
|
lot.lot_qt AS "dblQuantity",
|
|
lot.invoice_line AS "intInvoiceLineId",
|
|
lot.invoice_line_prov AS "intInvoiceLineProvId",
|
|
COALESCE(lot.lot_pur_inv_state, ''::character varying) AS "strPurchaseInvoiceState",
|
|
COALESCE(lot.lot_sale_inv_state, ''::character varying) AS "strSaleInvoiceState",
|
|
lot.lot_shipment_in AS "intShipmentInId",
|
|
lot.lot_shipment_internal AS "intShipmentInternalId",
|
|
lot.lot_shipment_out AS "intShipmentOutId",
|
|
lot.sale_invoice_line AS "intSaleInvoiceLineId",
|
|
lot.sale_invoice_line_prov AS "intSaleInvoiceLineProvId",
|
|
qth.quantity AS "dblNetWeight",
|
|
qth.gross_quantity AS "dblGrossWeight",
|
|
pu.name AS "strUnitName",
|
|
pu_line.name AS "strUnitLineName",
|
|
convert_weight(qth.quantity, (pu_line.name)::text, 'Kilogram'::text) AS "dblNetWeightKg",
|
|
convert_weight(qth.gross_quantity, (pu_line.name)::text, 'Kilogram'::text) AS "dblGrossWeightKg"
|
|
FROM (((lot_lot lot
|
|
LEFT JOIN lot_qt_hist qth ON (((lot.id = qth.lot) AND (lot.lot_state = qth.quantity_type))))
|
|
LEFT JOIN product_uom pu ON ((lot.lot_unit = pu.id)))
|
|
LEFT JOIN product_uom pu_line ON ((lot.lot_unit_line = pu_line.id)))
|
|
WHERE (1 = 1);;
|