before after

--BEFORE

-- Return data header
    OPEN pRefHeader FOR
    SELECT A.doc_id, A.doc_no, A.doc_date, A.so_no || ' ' || f_get_create_datetime_so_print_do(A.so_id) AS so_no, A.po_no, A.ou_id, A.partner_ship_to_id, A.cust_ship_to_code,
        A.cust_ship_to_name, A.partner_ship_address_id, A.cust_addr_1, A.cust_addr_2, A.cust_addr_3, A.cust_phone,
        A.cust_fax, A.cust_city, B.partner_bill_to_id, f_get_partner_code(B.partner_bill_to_id) AS cust_bill_to_code,
        f_get_partner_name(B.partner_bill_to_id) AS cust_bill_to_name, C.partner_address_id, C.address1 AS cust_bill_addr_1,
        C.address2 AS cust_bill_addr_2, C.address3 AS cust_bill_addr_3, C.phone1 AS cust_bill_phone,
        C.fax1 AS cust_bill_fax, C.city AS cust_bill_city, B.ext_doc_no AS so_ext_doc_no,
        A.cust_npwp_name, A.cust_npwp_no, A.cust_npwp_date, A.remark1, A.remark2,
        A.username, A.rolename, A.datetime, A.date_of_datetime, A.time_of_datetime, ROUND(A.total_harga_akhir::Numeric)::Character Varying AS total_harga_akhir,
        SUBSTRING (RPAD(f_get_last_remark_approved_so(A.so_id), 175, ' '), 1, 35) AS remark_approved_so_1,
		SUBSTRING (RPAD(f_get_last_remark_approved_so(A.so_id), 175, ' '), 36, 35) AS remark_approved_so_2,
		SUBSTRING (RPAD(f_get_last_remark_approved_so(A.so_id), 175, ' '), 71, 35) AS remark_approved_so_3,
		SUBSTRING (RPAD(f_get_last_remark_approved_so(A.so_id), 175, ' '), 106, 35) AS remark_approved_so_4,
		SUBSTRING (RPAD(f_get_last_remark_approved_so(A.so_id), 175, ' '), 141, 35) AS remark_approved_so_5,
		CASE WHEN A.coin > vEmptyAmount THEN 'Coin : '||ROUND(A.coin, 2)
			 ELSE vEmptyString END AS coin
    FROM tt_form_do_invoice_header A
    INNER JOIN sl_so B ON A.so_id = B.so_id
	LEFT OUTER JOIN m_partner_address C ON B.partner_bill_to_id = C.partner_id AND C.flg_official = 'Y'
    WHERE A.session_id = pSessionId;

-- AFTER

-- Get last remark from approval so
    SELECT RPAD(regexp_replace(f_get_last_remark_approved_so(A.so_id), '\r|\n', ' ', 'g'), 175, ' ') INTO vApprovalRemarkSo
    FROM tt_form_do_invoice_header A
    WHERE A.session_id = pSessionId;

    -- Return data header
    OPEN pRefHeader FOR
    SELECT A.doc_id, A.doc_no, A.doc_date, A.so_no || ' ' || f_get_create_datetime_so_print_do(A.so_id) AS so_no, A.po_no, A.ou_id, A.partner_ship_to_id, A.cust_ship_to_code,
        A.cust_ship_to_name, A.partner_ship_address_id, A.cust_addr_1, A.cust_addr_2, A.cust_addr_3, A.cust_phone,
        A.cust_fax, A.cust_city, B.partner_bill_to_id, f_get_partner_code(B.partner_bill_to_id) AS cust_bill_to_code,
        f_get_partner_name(B.partner_bill_to_id) AS cust_bill_to_name, C.partner_address_id, C.address1 AS cust_bill_addr_1,
        C.address2 AS cust_bill_addr_2, C.address3 AS cust_bill_addr_3, C.phone1 AS cust_bill_phone,
        C.fax1 AS cust_bill_fax, C.city AS cust_bill_city, B.ext_doc_no AS so_ext_doc_no,
        A.cust_npwp_name, A.cust_npwp_no, A.cust_npwp_date, A.remark1, A.remark2,
        A.username, A.rolename, A.datetime, A.date_of_datetime, A.time_of_datetime, ROUND(A.total_harga_akhir::Numeric)::Character Varying AS total_harga_akhir,
        SUBSTRING (vApprovalRemarkSo, 1, 35) AS remark_approved_so_1,
		SUBSTRING (vApprovalRemarkSo, 36, 35) AS remark_approved_so_2,
		SUBSTRING (vApprovalRemarkSo, 71, 35) AS remark_approved_so_3,
		SUBSTRING (vApprovalRemarkSo, 106, 35) AS remark_approved_so_4,
		SUBSTRING (vApprovalRemarkSo, 141, 35) AS remark_approved_so_5,
		CASE WHEN A.coin > vEmptyAmount THEN 'Coin : '||ROUND(A.coin, 2)
			 ELSE vEmptyString END AS coin
    FROM tt_form_do_invoice_header A
    INNER JOIN sl_so B ON A.so_id = B.so_id
	LEFT OUTER JOIN m_partner_address C ON B.partner_bill_to_id = C.partner_id AND C.flg_official = 'Y'
    WHERE A.session_id = pSessionId;