SELECT  z.po_item_sold_manual_id, z.qty_so, z.qty_posted, (z.qty_so - z.qty_posted) AS qty_available, b.po_item_id,
    b.line_no, b.product_id, d.product_code, d.product_name, d.ctgr_product_id, e.ctgr_product_code, e.ctgr_product_name,
    d.sub_ctgr_product_id, f.sub_ctgr_product_code, f.sub_ctgr_product_name, d.brand_id, g.brand_code,
    g.brand_name, a.qty_po + a.qty_add AS qtyPo, (a.qty_po - a.qty_rcv - a.qty_cancel + a.qty_add) AS outstandingQtyPo, a.po_uom_id,
    h.uom_code AS poUomCode, h.uom_name AS poUomName, a.base_uom_id, i.uom_code AS baseUomCode, i.uom_name AS baseUomName,
    f.flg_serial_number, sl_get_flg_work_order_by_so_item(b.ref_id) AS flg_work_order , c.doc_no, c.doc_date
FROM pu_po_balance_item_consignment_sold_manual z
INNER JOIN pu_po_balance_item a ON z.tenant_id = a.tenant_id AND z.po_item_id = a.po_item_id
INNER JOIN pu_po_item b ON a.tenant_id = b.tenant_id AND a.po_item_id = b.po_item_id
INNER JOIN pu_po c ON b.tenant_id = c.tenant_id AND b.po_id = c.po_id
INNER JOIN m_product d ON d.tenant_id = b.tenant_id AND d.product_id = b.product_id
INNER JOIN m_ctgr_product e ON d.tenant_id = e.tenant_id AND d.ctgr_product_id = e.ctgr_product_id
INNER JOIN m_sub_ctgr_product f ON d.tenant_id = f.tenant_id AND d.sub_ctgr_product_id = f.sub_ctgr_product_id
INNER JOIN m_brand g ON d.tenant_id = g.tenant_id AND d.brand_id = g.brand_id
INNER JOIN m_uom h ON a.tenant_id = h.tenant_id AND a.po_uom_id = h.uom_id
INNER JOIN m_uom i ON a.tenant_id = i.tenant_id AND a.base_uom_id = i.uom_id
INNER JOIN t_user_role j ON j.user_id = 20 AND j.role_id = 29
INNER JOIN m_policy_product k ON k.sub_ctgr_product_id = d.sub_ctgr_product_id AND j.user_role_id = k.user_role_id
WHERE z.tenant_id = 10
    AND z.po_item_id = 24420
    AND z.qty_posted < z.qty_so
ORDER BY z.po_item_sold_manual_id