Open pRefDetail FOR
        EXECUTE
        'WITH sl_inv_sub_cat AS (
            SELECT a.invoice_id, a.doc_type_id,
                f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
                f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
            FROM tt_sl_invoice a
                INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $1
                INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
                INNER JOIN m_product d ON c.product_id = d.product_id
            WHERE a.doc_type_id = $2
                AND a.session_id = $3
            GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
        ), sls_inv_temp_sub_cat AS (
            SELECT a.invoice_id, a.doc_type_id,
                f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
                f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
            FROM tt_sl_invoice a
                INNER JOIN sl_invoice_temp_item b ON a.invoice_id = b.invoice_temp_id AND b.ref_doc_type_id = $1
                INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
                INNER JOIN m_product d ON c.product_id = d.product_id
            WHERE a.doc_type_id = $5
                AND a.session_id = $3
            GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
        ), sls_order_sub_cat AS (
            SELECT a.invoice_id, a.doc_type_id,
                f_get_sub_ctgr_product_name(c.sub_ctgr_product_id) AS sub_ctgr_product_name,
                f_get_sub_ctgr_product_code(c.sub_ctgr_product_id) AS sub_ctgr_product_code
            FROM tt_sl_invoice a
                INNER JOIN sl_so_item b ON a.invoice_id = b.so_id
                INNER JOIN m_product c ON b.product_id = c.product_id
            WHERE a.doc_type_id = $12
                AND a.session_id = $3
            GROUP BY a.invoice_id, a.doc_type_id, c.sub_ctgr_product_id
        ), return_sls_sub_cat AS (
            SELECT a.invoice_id, a.doc_type_id,
                f_get_sub_ctgr_product_name(e.sub_ctgr_product_id) AS sub_ctgr_product_name,
                f_get_sub_ctgr_product_code(e.sub_ctgr_product_id) AS sub_ctgr_product_code
            FROM tt_sl_invoice a
                INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $15
                INNER JOIN in_inventory c ON b.ref_id = c.inventory_id AND b.ref_doc_type_id = c.doc_type_id
                INNER JOIN in_inventory_item d ON c.inventory_id = d.inventory_id
                INNER JOIN m_product e ON d.product_id = e.product_id
            WHERE a.doc_type_id = $14
                AND a.session_id = $3
            GROUP BY a.invoice_id, a.doc_type_id, e.sub_ctgr_product_id
        ), category_invoice AS (
            SELECT a.invoice_id, a.doc_type_id,
                string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
                string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
            FROM sl_inv_sub_cat a
            GROUP BY a.invoice_id, a.doc_type_id
            UNION
            SELECT a.invoice_id, a.doc_type_id,
                string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
                string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
            FROM sls_inv_temp_sub_cat a
            GROUP BY a.invoice_id, a.doc_type_id
            UNION
            SELECT a.invoice_id, a.doc_type_id,
                string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
                string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
            FROM sls_order_sub_cat a
            GROUP BY a.invoice_id, a.doc_type_id
            UNION
            SELECT a.invoice_id, a.doc_type_id,
                string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
                string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
            FROM return_sls_sub_cat a
            GROUP BY a.invoice_id, a.doc_type_id
            UNION
            SELECT a.invoice_id, a.doc_type_id,
                ''-'' AS sub_ctgr_product_name,
                ''-'' AS sub_ctgr_product_code
            FROM tt_sl_invoice a
            WHERE a.doc_type_id IN ($6, $7 )
            AND a.session_id = $3
            GROUP BY a.invoice_id, a.doc_type_id
        )
        SELECT f_get_partner_code(a.partner_id) AS partner_code, f_get_partner_name(a.partner_id) AS partner_name,
            f_get_city_by_partner(a.partner_id) AS city, a.doc_no AS invoice_no, c.sub_ctgr_product_name, COALESCE(f.doc_no, ''-'') AS mou_no,
            a.doc_date AS invoice_date, a.due_date, a.total_amount, d.add_amount AS ongkir, d.retur_amount AS retur,
            d.payment_amount, a.tax_amount AS pot_bayar, (a.total_amount+a.tax_amount)-d.payment_amount AS sisa, a.invoice_status, d.no_resi,
            d.ref_doc_no, a.so_date, COALESCE(g.promo_code, ''-'') AS promo, COALESCE(h.promo_code, ''-'') AS launching
        FROM tt_sl_invoice a
            INNER JOIN category_invoice c ON a.invoice_id = c.invoice_id AND a.doc_type_id = c.doc_type_id '||vFilterSubCtgr||'
            INNER JOIN tt_report_sales_journal d ON a.invoice_id = d.sales_invoice_id AND a.doc_type_id = d.doc_type_id AND d.session_id = $3
            LEFT OUTER JOIN sl_mou f ON a.mou_id = f.mou_id
            LEFT OUTER JOIN m_promo g ON a.promo_id = g.promo_id
            LEFT OUTER JOIN m_promo h ON a.product_launching_id = h.promo_id
        WHERE a.session_id = $3
            AND ((a.total_amount+a.tax_amount)-d.payment_amount) = $13
        ORDER BY f_get_partner_name(a.partner_id), a.doc_date, a.invoice_status'
        USING vDocTypeDo,vDocTypeSalesInvoice,pSessionId,vDocTypeConvExcOut,vDocTypeSalesInvoiceTemp,vDocTypeDNAR,vDocTypeCNAR,pTenantId,pUserId,pRoleId,vOne,vDocTypeSoByBrand,vNolAmount,vDocTypeRSIB,vDocTypeRN;
        RETURN NEXT pRefDetail;