32 lines
1.6 KiB
SQL
32 lines
1.6 KiB
SQL
CREATE OR REPLACE VIEW public.vw_bi_os_migration_instructed_shipments AS
|
|
SELECT il."intLotId" AS "intPurchaseLotId",
|
|
il."intLotId" AS "intSaleLotId",
|
|
il."intShipmentId",
|
|
il."intPurchaseLineId",
|
|
il."intSaleLineId",
|
|
il."dblQuantity",
|
|
il."strUnit",
|
|
pl.source_id AS purchase_source_id,
|
|
sl.source_id AS sale_source_id,
|
|
'Physical'::text AS "strLotType"
|
|
FROM ((vw_utility_instructed_lots il
|
|
LEFT JOIN vw_bi_os_migration_mapping pl ON (((il."intPurchaseLineId" = pl.tryton_id) AND (pl.tryton_model = '{purchase.line}'::character varying[]))))
|
|
LEFT JOIN vw_bi_os_migration_mapping sl ON (((il."intSaleLineId" = sl.tryton_id) AND (sl.tryton_model = '{sale.line}'::character varying[]))))
|
|
UNION ALL
|
|
SELECT ml."intPurchaseLotId",
|
|
ml."intSaleLotId",
|
|
ml."intLotShipmentIn" AS "intShipmentId",
|
|
pl."intPurchaseLineId",
|
|
sl."intSaleLineId",
|
|
ml."dblQuantity",
|
|
ml."strUnit",
|
|
ospl.source_id AS purchase_source_id,
|
|
ossl.source_id AS sale_source_id,
|
|
'Virtual'::text AS "strLotType"
|
|
FROM ((((vw_utility_matched_lots ml
|
|
JOIN vw_utility_lot pl ON ((ml."intPurchaseLotId" = pl."intLotId")))
|
|
JOIN vw_utility_lot sl ON ((ml."intSaleLotId" = sl."intLotId")))
|
|
LEFT JOIN vw_bi_os_migration_mapping ospl ON (((pl."intPurchaseLineId" = ospl.tryton_id) AND (ospl.tryton_model = '{purchase.line}'::character varying[]))))
|
|
LEFT JOIN vw_bi_os_migration_mapping ossl ON (((sl."intSaleLineId" = ossl.tryton_id) AND (ossl.tryton_model = '{sale.line}'::character varying[]))))
|
|
WHERE ((1 = 1) AND (ml."dblQuantity" > (0)::numeric) AND (ml."intLotShipmentIn" > 0));;
|