Untitled

mail@pastecode.io avatar
unknown
plain_text
18 days ago
1.2 kB
1
Indexable
Never
SELECT
    pmp.product_id AS id,
    pmp.product_id,
    sub.vendor_id,
    pmp.brand,
    pmp.item_code,
    pmp.product,
    MAX(pmp.onhand) AS onhand,
    MAX(pmp.incoming) AS incoming,
    MAX(pmp.outgoing) AS outgoing,
    pmp.action,
    MAX(pjs.status_apo) AS status_apo,
    MAX(pjs.note) AS note,
    MAX(po.user_id) AS purchase_representative_id
FROM v_procurement_monitoring_by_product pmp
LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id
LEFT JOIN (
    SELECT
        vso.product_id,
        rp.id AS vendor_id
    FROM v_sales_outstanding vso
    LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id
    LEFT JOIN res_partner rp ON rp.id = sol.vendor_id
) AS sub ON sub.product_id = pmp.product_id
LEFT JOIN (
    SELECT
        pol.product_id,
        po.user_id,
		MAX(po.create_date) AS max_create_date
    FROM purchase_order po
	LEFT JOIN purchase_order_line pol on pol.order_id = po.id
	GROUP BY
	pol.product_id,
	po.user_id
) AS po ON po.product_id = pmp.product_id
WHERE pmp.action = 'kurang'
    AND sub.vendor_id <> 1
GROUP BY
    pmp.product_id,
    pmp.brand,
    pmp.item_code,
    pmp.product,
    pmp.action,
    sub.vendor_id;
Leave a Comment