76 lines
3.3 KiB
SQL
76 lines
3.3 KiB
SQL
CREATE OR REPLACE VIEW public.vw_bi_os_migration_sale_sale AS
|
|
SELECT (mm.source_id)::character varying AS source_id,
|
|
(mm.object_type)::character varying AS object_type,
|
|
(mm.recon_key)::character varying AS recon_key,
|
|
(mm.tryton_id)::integer AS tryton_id,
|
|
(mm.write_date)::timestamp without time zone AS migration_date,
|
|
(mm.tryton_model)::character varying[] AS tryton_model,
|
|
p.number AS tradon_number,
|
|
p.reference AS tradon_reference,
|
|
p.our_reference AS tradon_our_reference,
|
|
p.sale_date AS tradon_sale_date,
|
|
pa.name AS tradon_counterparty,
|
|
c.name AS tradon_currency,
|
|
p.state AS tradon_state,
|
|
p.invoice_method AS tradon_invoice_method,
|
|
i.code AS tradon_incoterm,
|
|
i.version AS tradon_incoterm_version,
|
|
fromloc.name AS tradon_from_location,
|
|
toloc.name AS tradon_to_location,
|
|
pwb.name AS tradon_weight_basis,
|
|
p.tol_max AS tradon_tol_max_pct,
|
|
p.tol_min AS tradon_tol_min_pct,
|
|
p.tol_max_qt AS tradon_tol_max_qty,
|
|
p.tol_min_qt AS tradon_tol_min_qty,
|
|
op.name AS tradon_operator,
|
|
tr.name AS tradon_trader
|
|
FROM (((((((((os_migration_mapping mm
|
|
JOIN sale_sale p ON ((p.id = mm.tryton_id)))
|
|
LEFT JOIN party_party pa ON ((pa.id = p.party)))
|
|
LEFT JOIN currency_currency c ON ((c.id = p.currency)))
|
|
LEFT JOIN incoterm_incoterm i ON ((p.incoterm = i.id)))
|
|
LEFT JOIN stock_location fromloc ON ((p.from_location = fromloc.id)))
|
|
LEFT JOIN stock_location toloc ON ((p.to_location = toloc.id)))
|
|
LEFT JOIN purchase_weight_basis pwb ON ((p.wb = pwb.id)))
|
|
LEFT JOIN party_party op ON ((p.operator = op.id)))
|
|
LEFT JOIN party_party tr ON ((p.trader = tr.id)))
|
|
WHERE (mm.tryton_model = ARRAY['sale.sale'::character varying])
|
|
UNION ALL
|
|
SELECT ''::character varying AS source_id,
|
|
''::character varying AS object_type,
|
|
NULL::character varying AS recon_key,
|
|
NULL::integer AS tryton_id,
|
|
NULL::timestamp without time zone AS migration_date,
|
|
NULL::character varying[] AS tryton_model,
|
|
p.number AS tradon_number,
|
|
p.reference AS tradon_reference,
|
|
p.our_reference AS tradon_our_reference,
|
|
p.sale_date AS tradon_sale_date,
|
|
pa.name AS tradon_counterparty,
|
|
c.name AS tradon_currency,
|
|
p.state AS tradon_state,
|
|
p.invoice_method AS tradon_invoice_method,
|
|
i.code AS tradon_incoterm,
|
|
i.version AS tradon_incoterm_version,
|
|
fromloc.name AS tradon_from_location,
|
|
toloc.name AS tradon_to_location,
|
|
pwb.name AS tradon_weight_basis,
|
|
p.tol_max AS tradon_tol_max_pct,
|
|
p.tol_min AS tradon_tol_min_pct,
|
|
p.tol_max_qt AS tradon_tol_max_qty,
|
|
p.tol_min_qt AS tradon_tol_min_qty,
|
|
op.name AS tradon_operator,
|
|
tr.name AS tradon_trader
|
|
FROM ((((((((sale_sale p
|
|
LEFT JOIN party_party pa ON ((pa.id = p.party)))
|
|
LEFT JOIN currency_currency c ON ((c.id = p.currency)))
|
|
LEFT JOIN incoterm_incoterm i ON ((p.incoterm = i.id)))
|
|
LEFT JOIN stock_location fromloc ON ((p.from_location = fromloc.id)))
|
|
LEFT JOIN stock_location toloc ON ((p.to_location = toloc.id)))
|
|
LEFT JOIN purchase_weight_basis pwb ON ((p.wb = pwb.id)))
|
|
LEFT JOIN party_party op ON ((p.operator = op.id)))
|
|
LEFT JOIN party_party tr ON ((p.trader = tr.id)))
|
|
WHERE (NOT (EXISTS ( SELECT 1
|
|
FROM os_migration_mapping mm
|
|
WHERE ((mm.tryton_id = p.id) AND (mm.tryton_model = ARRAY['sale.sale'::character varying])))));;
|