mis-builder/mis_builder_demo/models/mis_committed_purchase.sql

115 lines
5.7 KiB
MySQL
Raw Permalink Normal View History

2021-03-23 19:36:01 +00:00
CREATE OR REPLACE VIEW mis_committed_purchase AS (
SELECT ROW_NUMBER() OVER() AS id, mis_committed_purchase.* FROM (
WITH currency_rate as (
SELECT
r.currency_id,
COALESCE(r.company_id, c.id) as company_id,
r.rate,
r.name AS date_start,
(SELECT name FROM res_currency_rate r2
WHERE r2.name > r.name AND
r2.currency_id = r.currency_id AND
(r2.company_id is null or r2.company_id = c.id)
ORDER BY r2.name ASC
LIMIT 1) AS date_end
FROM res_currency_rate r
JOIN res_company c ON (r.company_id is null or r.company_id = c.id)
)
/* UNINVOICED PURCHASES */
SELECT
'uninvoiced purchase' AS line_type,
pol.company_id AS company_id,
pol.name AS name,
po.date_planned::date as date,
pol.account_analytic_id as analytic_account_id,
pol.id AS res_id,
'purchase.order.line' AS res_model,
CASE
WHEN (cast(split_part(ip.value_reference, ',', 2) AS INTEGER) IS NOT NULL) THEN cast(split_part(ip.value_reference, ',', 2) AS INTEGER)
WHEN (cast(split_part(ipc.value_reference, ',', 2) AS INTEGER) IS NOT NULL) THEN cast(split_part(ipc.value_reference, ',', 2) AS INTEGER)
WHEN (cast(split_part(ipd.value_reference, ',', 2) AS INTEGER) IS NOT NULL) THEN cast(split_part(ipd.value_reference, ',', 2) AS INTEGER)
ELSE cast(NULL AS INTEGER)
END AS account_id,
CASE
WHEN (pol.price_unit / COALESCE(cur.rate, 1.0) * (pol.product_qty - pol.qty_invoiced))::decimal(16,2) >= 0.0 THEN (pol.price_unit / COALESCE(cur.rate, 1.0) * (pol.product_qty - pol.qty_invoiced))::decimal(16,2)
ELSE 0.0
END AS debit,
CASE
WHEN (pol.price_unit / COALESCE(cur.rate, 1.0) * (pol.product_qty - pol.qty_invoiced))::decimal(16,2) < 0 THEN (pol.price_unit / COALESCE(cur.rate, 1.0) * (pol.product_qty - pol.qty_invoiced))::decimal(16,2)
ELSE 0.0
END AS credit
FROM purchase_order_line pol
LEFT JOIN purchase_order po on po.id = pol.order_id
LEFT JOIN product_product pp ON pp.id = pol.product_id
LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
LEFT JOIN product_category pc ON pc.id = pt.categ_id
LEFT JOIN ir_property ip ON ip.name = 'property_account_expense_id' AND ip.type='many2one' AND ip.res_id ='product.template,' || pt.id
LEFT JOIN ir_property ipc ON ipc.name = 'property_account_expense_categ_id' AND ipc.type='many2one' AND ipc.res_id ='product.category,' || pc.id
LEFT JOIN ir_property ipd ON ipd.name = 'property_account_expense_categ_id' AND ipd.type='many2one' AND (ipd.res_id IS NULL OR ipd.res_id = '')
LEFT JOIN currency_rate cur on (cur.currency_id = po.currency_id and
cur.company_id = po.company_id and
cur.date_start <= coalesce(po.date_order, now()) and
(cur.date_end is null or cur.date_end > coalesce(po.date_order, now())))
WHERE pol.product_qty > pol.qty_invoiced AND po.state != 'cancel' AND po.state != 'draft'
UNION ALL
/* DRAFT INVOICES */
SELECT
'draft invoice' AS line_type,
ail.company_id AS company_id,
ail.name AS name,
ail.create_date::date as date,
ail.account_analytic_id as analytic_account_id,
ail.id AS res_id,
'account.invoice.line' AS res_model,
ail.account_id as account_id,
CASE
WHEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2) >= 0.0 THEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2)
ELSE 0.0
END AS debit,
CASE
WHEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2) < 0 THEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2)
ELSE 0.0
END AS credit
FROM account_invoice_line ail
LEFT JOIN account_invoice ai ON ai.id = ail.invoice_id
LEFT JOIN currency_rate cur on (cur.currency_id = ai.currency_id and
cur.company_id = ai.company_id and
cur.date_start <= coalesce(ai.date_invoice, now()) and
(cur.date_end is null or cur.date_end > coalesce(ai.date_invoice, now())))
WHERE ai.state = 'draft' AND ai.type IN ('in_invoice', 'out_refund')
UNION ALL
/* DRAFT INVOICES */
SELECT
'draft invoice' AS line_type,
ail.company_id AS company_id,
ail.name AS name,
ail.create_date::date as date,
ail.account_analytic_id as analytic_account_id,
ail.id AS res_id,
'account.invoice.line' AS res_model,
ail.account_id as account_id,
CASE
WHEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2) < 0 THEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2)
ELSE 0.0
END AS debit,
CASE
WHEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2) >= 0.0 THEN (ail.price_subtotal / COALESCE(cur.rate, 1.0))::decimal(16,2)
ELSE 0.0
END AS credit
FROM account_invoice_line ail
LEFT JOIN account_invoice ai ON ai.id = ail.invoice_id
LEFT JOIN currency_rate cur on (cur.currency_id = ai.currency_id and
cur.company_id = ai.company_id and
cur.date_start <= coalesce(ai.date_invoice, now()) and
(cur.date_end is null or cur.date_end > coalesce(ai.date_invoice, now())))
WHERE ai.state = 'draft' AND ai.type IN ('out_invoice', 'in_refund')
) AS mis_committed_purchase
)