158 lines
9.1 KiB
SQL
158 lines
9.1 KiB
SQL
CREATE OR REPLACE VIEW public.vw_cash_forecast AS
|
|
WITH contract_lines AS (
|
|
SELECT forex.id AS forex_id,
|
|
forex.number AS forex_number,
|
|
fcpc.id AS fcpc_id,
|
|
fcpc.amount AS forex_amount,
|
|
fcpc.amount AS forex_amount_for_all,
|
|
((fcpc.amount)::double precision * (((lh.quantity)::double precision * (unit.factor / unit_line.factor)) / pl.quantity)) AS line_amount,
|
|
(COALESCE(((pl.unit_price)::double precision * ((lh.quantity)::double precision * (unit.factor / unit_line.factor))), ((0)::numeric)::double precision) * ('-1'::integer)::double precision) AS qt_amount,
|
|
p.id AS contract_id,
|
|
p.number AS contract_number,
|
|
'purchase'::text AS contract_type,
|
|
COALESCE((pe.estimated_date + ptld.days), p.purchase_date) AS maturity_date,
|
|
pl.id AS contract_line_id,
|
|
pl.quantity,
|
|
pp.code AS product_code,
|
|
cc.code AS currency,
|
|
ll.lot_type,
|
|
ll.invoice_line,
|
|
ll.invoice_line_prov,
|
|
aml.reconciliation
|
|
FROM (((((((((((((((purchase_purchase p
|
|
LEFT JOIN forex_cover_physical_contract fcpc ON ((p.id = fcpc.contract)))
|
|
LEFT JOIN forex_forex forex ON ((fcpc.forex = forex.id)))
|
|
LEFT JOIN purchase_line pl ON ((pl.purchase = p.id)))
|
|
LEFT JOIN product_product pp ON ((pl.product = pp.id)))
|
|
LEFT JOIN currency_currency cc ON ((p.currency = cc.id)))
|
|
LEFT JOIN account_invoice_payment_term pt ON ((pt.id = p.payment_term)))
|
|
LEFT JOIN account_invoice_payment_term_line ptl ON ((ptl.payment = pt.id)))
|
|
LEFT JOIN account_invoice_payment_term_line_delta ptld ON ((ptld.line = ptl.id)))
|
|
LEFT JOIN pricing_estimated pe ON (((pe.purchase = p.id) AND ((pe.trigger)::text = (ptl.trigger_event)::text))))
|
|
LEFT JOIN lot_lot ll ON ((pl.id = ll.line)))
|
|
LEFT JOIN lot_qt_hist lh ON ((lh.lot = ll.id)))
|
|
LEFT JOIN lot_qt_type lt ON (((lh.quantity_type = lt.id) AND (lt.sequence = 1))))
|
|
LEFT JOIN product_uom unit ON ((unit.id = pl.unit)))
|
|
LEFT JOIN product_uom unit_line ON ((unit_line.id = ll.lot_unit_line)))
|
|
LEFT JOIN account_move_line aml ON (((split_part((aml.origin)::text, ','::text, 1) = 'invoice.line'::text) AND ((ll.invoice_line_prov = (split_part((aml.origin)::text, ','::text, 2))::integer) OR (ll.invoice_line = (split_part((aml.origin)::text, ','::text, 2))::integer)))))
|
|
WHERE (p.currency = 2)
|
|
UNION ALL
|
|
SELECT forex.id AS forex_id,
|
|
forex.number AS forex_number,
|
|
fcpc.id AS fcpc_id,
|
|
fcpc.amount AS forex_amount,
|
|
fcpc.amount AS forex_amount_for_all,
|
|
((fcpc.amount)::double precision * (((lh.quantity)::double precision * (unit.factor / unit_line.factor)) / sl.quantity)) AS line_amount,
|
|
((sl.unit_price)::double precision * ((lh.quantity)::double precision * (unit.factor / unit_line.factor))) AS qt_amount,
|
|
s.id AS contract_id,
|
|
s.number AS contract_number,
|
|
'sale'::text AS contract_type,
|
|
s.sale_date AS maturity_date,
|
|
sl.id AS contract_line_id,
|
|
sl.quantity,
|
|
pp.code AS product_code,
|
|
cc.code AS currency,
|
|
ll.lot_type,
|
|
ll.sale_invoice_line AS invoice_line,
|
|
ll.sale_invoice_line_prov AS invoice_line_prov,
|
|
aml.reconciliation
|
|
FROM (((((((((((sale_sale s
|
|
LEFT JOIN forex_cover_physical_sale fcpc ON ((s.id = fcpc.contract)))
|
|
LEFT JOIN forex_forex forex ON ((fcpc.forex = s.id)))
|
|
LEFT JOIN sale_line sl ON ((sl.sale = s.id)))
|
|
LEFT JOIN product_product pp ON ((sl.product = pp.id)))
|
|
LEFT JOIN currency_currency cc ON ((s.currency = cc.id)))
|
|
LEFT JOIN lot_lot ll ON ((sl.id = ll.sale_line)))
|
|
LEFT JOIN lot_qt_hist lh ON ((lh.lot = ll.id)))
|
|
LEFT JOIN lot_qt_type lt ON (((lh.quantity_type = lt.id) AND (lt.sequence = 1))))
|
|
LEFT JOIN product_uom unit ON ((unit.id = sl.unit)))
|
|
LEFT JOIN product_uom unit_line ON ((unit_line.id = ll.lot_unit_line)))
|
|
LEFT JOIN account_move_line aml ON (((split_part((aml.origin)::text, ','::text, 1) = 'invoice.line'::text) AND ((ll.sale_invoice_line_prov = (split_part((aml.origin)::text, ','::text, 2))::integer) OR (ll.sale_invoice_line = (split_part((aml.origin)::text, ','::text, 2))::integer)))))
|
|
WHERE (s.currency = 2)
|
|
), contracts_by_status AS (
|
|
SELECT contract_lines.forex_id,
|
|
contract_lines.forex_number,
|
|
contract_lines.fcpc_id,
|
|
contract_lines.line_amount,
|
|
contract_lines.forex_amount,
|
|
contract_lines.forex_amount_for_all,
|
|
contract_lines.qt_amount,
|
|
contract_lines.contract_id,
|
|
contract_lines.contract_number,
|
|
contract_lines.contract_type,
|
|
contract_lines.maturity_date,
|
|
contract_lines.contract_line_id,
|
|
contract_lines.quantity,
|
|
contract_lines.product_code,
|
|
contract_lines.currency,
|
|
contract_lines.lot_type,
|
|
contract_lines.invoice_line,
|
|
contract_lines.invoice_line_prov,
|
|
contract_lines.reconciliation,
|
|
CASE
|
|
WHEN ((contract_lines.lot_type)::text = 'virtual'::text) THEN 'open'::text
|
|
WHEN (((contract_lines.lot_type)::text = 'physic'::text) AND (contract_lines.invoice_line IS NULL) AND (contract_lines.invoice_line_prov IS NULL)) THEN 'shipped'::text
|
|
WHEN (((contract_lines.lot_type)::text = 'physic'::text) AND ((contract_lines.invoice_line IS NOT NULL) OR (contract_lines.invoice_line_prov IS NOT NULL)) AND ((contract_lines.reconciliation IS NULL) OR (contract_lines.reconciliation = 0))) THEN 'invoiced'::text
|
|
WHEN (((contract_lines.lot_type)::text = 'physic'::text) AND ((contract_lines.invoice_line IS NOT NULL) OR (contract_lines.invoice_line_prov IS NOT NULL)) AND (contract_lines.reconciliation > 0)) THEN 'paid'::text
|
|
ELSE 'total'::text
|
|
END AS qt_type,
|
|
CASE
|
|
WHEN ((contract_lines.lot_type)::text = 'virtual'::text) THEN 1
|
|
WHEN (((contract_lines.lot_type)::text = 'physic'::text) AND (contract_lines.invoice_line IS NULL) AND (contract_lines.invoice_line_prov IS NULL)) THEN 2
|
|
WHEN (((contract_lines.lot_type)::text = 'physic'::text) AND ((contract_lines.invoice_line IS NOT NULL) OR (contract_lines.invoice_line_prov IS NOT NULL)) AND ((contract_lines.reconciliation IS NULL) OR (contract_lines.reconciliation = 0))) THEN 3
|
|
WHEN (((contract_lines.lot_type)::text = 'physic'::text) AND ((contract_lines.invoice_line IS NOT NULL) OR (contract_lines.invoice_line_prov IS NOT NULL)) AND (contract_lines.reconciliation > 0)) THEN 4
|
|
ELSE 0
|
|
END AS qt_order
|
|
FROM contract_lines
|
|
), forex_summary AS (
|
|
SELECT forex.number AS forex_number,
|
|
'forex'::text AS line_type,
|
|
NULL::text AS contract_number,
|
|
NULL::text AS contract_type,
|
|
NULL::text AS contract_line,
|
|
NULL::text AS qt_type,
|
|
0 AS qt_order,
|
|
max(forex.rate) AS rate,
|
|
NULL::numeric AS amount_covered,
|
|
NULL::numeric AS forex_amount,
|
|
forex.buy_amount AS qt_amount,
|
|
forex.buy_amount AS total_amount_forex,
|
|
(forex.buy_amount - COALESCE(sum(fcpc.amount), (0)::numeric)) AS amount_remaining,
|
|
max((cc.code)::text) AS currency,
|
|
forex.value_date AS maturity_date
|
|
FROM ((forex_forex forex
|
|
LEFT JOIN forex_cover_physical_contract fcpc ON ((forex.id = fcpc.forex)))
|
|
LEFT JOIN currency_currency cc ON ((forex.buy_currency = cc.id)))
|
|
GROUP BY forex.id, forex.number, forex.buy_amount
|
|
)
|
|
SELECT forex_summary.line_type,
|
|
forex_summary.forex_number AS contract_ref,
|
|
forex_summary.contract_line,
|
|
forex_summary.qt_type,
|
|
forex_summary.qt_order,
|
|
forex_summary.rate,
|
|
forex_summary.forex_amount,
|
|
forex_summary.qt_amount,
|
|
forex_summary.total_amount_forex,
|
|
forex_summary.amount_remaining,
|
|
forex_summary.currency,
|
|
forex_summary.maturity_date,
|
|
to_char((forex_summary.maturity_date)::timestamp with time zone, 'MONYY'::text) AS maturity_month
|
|
FROM forex_summary
|
|
UNION ALL
|
|
SELECT cl.contract_type AS line_type,
|
|
cl.contract_number AS contract_ref,
|
|
((cl.product_code)::text || COALESCE(((' ('::text || cl.contract_line_id) || ')'::text), ''::text)) AS contract_line,
|
|
cl.qt_type,
|
|
cl.qt_order,
|
|
NULL::numeric AS rate,
|
|
cl.forex_amount,
|
|
cl.qt_amount,
|
|
NULL::numeric AS total_amount_forex,
|
|
NULL::numeric AS amount_remaining,
|
|
cl.currency,
|
|
cl.maturity_date,
|
|
to_char((cl.maturity_date)::timestamp with time zone, 'MONYY'::text) AS maturity_month
|
|
FROM contracts_by_status cl
|
|
ORDER BY 1, 2 DESC, 3, 6 DESC;;
|