Update PO Inquiry

WITH ctedata AS (
         SELECT DISTINCT co_1.ad_client_id,
            co_1.c_order_id,
            co_1.c_doctype_id,
            co_1.documentno,
            co_1.docstatus,
            co_1.dateordered,
            co_1.datepromised,
            co_1.user1_id,
            co_1.ad_org_id,
            co_1.ad_orgtrx_id,
            co_1.salesrep_id,
            co_1.c_bpartner_id,
            co_1.c_bpartner_location_id,
            co_1.c_bpartnersr_id,
            co_1.paymentrule,
            co_1.c_paymentterm_id,
            co_1.priorityrule,
            co_1.description,
            co_1.note,
            co_1.c_currency_id,
            co_1.grandtotal,
            mr.documentno AS mrno,
            mr.m_requisition_id,
            mr.c_doctype_id AS pr_c_doctype_id,
            cp.c_prroute_id,
            mr.budgetuse,
                CASE
                    WHEN mr.c_doctype_id = 1000050::numeric THEN 1000000::numeric
                    ELSE mr.c_prroute_id
                END AS department,
            mp.mp_requisition_id,
            mp.documentno AS mpno,
            ce.c_elementvalue_id,
            ce.value AS akunno,
            ce.name AS akunname,
            mpc.value AS glclass,
            po_line.line,
            po_line.m_product_id,
            prod.value AS item_no,
            prod.sku,
            prod.name AS item_name,
            po_line.description AS item_description,
            po_line.qtyentered,
            po_line.qtydelivered,
            COALESCE(po_line.qtyentered, 0::numeric) - COALESCE(po_line.qtydelivered, 0::numeric) AS os_delivery,
            po_line.qtyinvoiced,
            po_line.c_uom_id,
            po_line.priceactual,
            po_line.priceentered,
            po_line.discount,
            po_line.linenetamt,
            po_line.c_tax_id,
            prod.lco_withholdingcategory_id,
            prod.producttype,
            po_line.c_orderline_id,
            co_1.created,
            co_1.createdby,
            co_1.updated,
            co_1.updatedby
           FROM c_order co_1
             JOIN c_orderline po_line ON co_1.c_order_id = po_line.c_order_id
             LEFT JOIN m_product prod ON prod.m_product_id = po_line.m_product_id
             LEFT JOIN m_product_category mpc ON mpc.m_product_category_id = prod.m_product_category_id
             LEFT JOIN m_product_acct mpa ON mpa.m_product_id = prod.m_product_id
             LEFT JOIN c_validcombination cv ON cv.c_validcombination_id = mpa.p_expense_acct
             LEFT JOIN c_elementvalue ce ON ce.c_elementvalue_id = cv.account_id
             LEFT JOIN m_requisitionline mrl ON mrl.m_requisitionline_id = po_line.m_requisitionline_id
             LEFT JOIN m_requisition mr ON mr.m_requisition_id = mrl.m_requisition_id
             LEFT JOIN c_prroute cp ON cp.c_prroute_id = mr.c_prroute_id
             LEFT JOIN mp_requisitionline mpr ON mpr.mp_requisitionline_id = mrl.mp_requisitionline_id
             LEFT JOIN mp_requisition mp ON mp.mp_requisition_id = mpr.mp_requisition_id
          WHERE co_1.docstatus <> 'VO'::bpchar 
        ), ctemaxreceipt AS (
         SELECT col.c_orderline_id,
            max(mi_1.m_inout_id) AS m_inout_id,
            (array_agg(mi_1.documentno ORDER BY mi_1.m_inout_id DESC))[1] AS receiptno
           FROM m_inout mi_1
             JOIN m_inoutline mil ON mil.m_inout_id = mi_1.m_inout_id
             JOIN c_orderline col ON col.c_orderline_id = mil.c_orderline_id
          WHERE mi_1.docstatus <> 'VO'::bpchar
          GROUP BY col.c_orderline_id
        ), ctemaxcm AS (
         SELECT col.c_orderline_id,
            max(mi_1.m_cargomanifest_id) AS m_cargomanifest_id,
            (array_agg(mi_1.documentno ORDER BY mi_1.m_cargomanifest_id DESC))[1] AS cmno
           FROM m_cargomanifest mi_1
             JOIN m_cargomanifest_receipt mil ON mil.m_cargomanifest_id = mi_1.m_cargomanifest_id
             JOIN ctemaxreceipt col ON col.m_inout_id = mil.m_inout_id
          WHERE mi_1.docstatus <> 'VO'::bpchar
          GROUP BY col.c_orderline_id
        ) 
 SELECT co.ad_client_id,
    co.c_doctype_id,
    co.documentno,
    co.docstatus,
    co.dateordered,
    co.datepromised,
    co.user1_id,
    co.ad_org_id,
    co.ad_orgtrx_id,
    co.salesrep_id,
    co.c_bpartner_id,
    co.c_bpartner_location_id,
    co.c_bpartnersr_id,
    co.paymentrule,
    co.c_paymentterm_id,
    co.priorityrule,
    co.description,
    co.note,
    co.c_currency_id,
    co.grandtotal,
    co.mrno,
    co.m_requisition_id,
    co.pr_c_doctype_id,
    co.c_prroute_id,
    co.budgetuse,
    co.department,
    co.mp_requisition_id,
    co.mpno,
    co.c_elementvalue_id,
    co.akunno,
    co.akunname,
    co.glclass,
    co.line,
    co.m_product_id,
    co.item_no,
    co.sku,
    co.item_name,
    co.item_description,
    co.qtyentered,
    co.qtydelivered,
    co.qtyinvoiced,
    co.os_delivery,
    co.c_uom_id,
    co.priceactual,
    co.discount,
    co.linenetamt,
    co.c_tax_id,
    co.lco_withholdingcategory_id,
    co.producttype,
    co.c_orderline_id,
    cmr.m_inout_id,
    cmr.receiptno,
    mi.docstatus AS receiptdocstatus,
    mi.c_doctype_id AS receiptdoctype,
    co.created,
    co.createdby,
    co.updated,
    co.updatedby,
    cmc.m_cargomanifest_id,
    cmc.cmno,
    mc.docstatus AS cmdocstatus,
        CASE
            WHEN mi.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]) THEN mi.updated
            ELSE NULL::timestamp without time zone
        END AS receipt_date,
        CASE
            WHEN mi.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]) THEN mi.updatedby
            ELSE NULL::numeric(10,0)
        END AS receiptby,
    co.c_order_id,
    co.priceentered,
    mil.qtyentered as qtygr
   FROM ctedata co
     LEFT JOIN ctemaxreceipt cmr ON cmr.c_orderline_id = co.c_orderline_id
     LEFT JOIN m_inout mi ON mi.m_inout_id = cmr.m_inout_id
     left join m_inoutline mil on mil.c_orderline_id = co.c_orderline_id
     LEFT JOIN ctemaxcm cmc ON cmc.c_orderline_id = co.c_orderline_id
     LEFT JOIN m_cargomanifest mc ON mc.m_cargomanifest_id = cmc.m_cargomanifest_id;