CREATE OR REPLACE FUNCTION r_print_form_do_invoice(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';

    pSessionId              ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pUserId                 ALIAS FOR $3;
    pRoleId                 ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;
    pDocId                      ALIAS FOR $6;
    pFlgDoc                 ALIAS FOR $7;
    pFlgShowPpn                 ALIAS FOR $8;

    vInvoiceId              BIGINT;
    --vFlgShowPpn           CHARACTER VARYING := 'Y';
    pFlgPkp                 CHARACTER VARYING := 'N';
    vStatusReleased         CHARACTER VARYING := 'R';
    vStatusDO               CHARACTER VARYING(1);
    vInvoiceTempId          BIGINT := -99;
BEGIN

    -- Delete table temporary
    DELETE FROM tt_form_do_invoice_header WHERE session_id = pSessionId;
    DELETE FROM tt_form_do_invoice_item WHERE session_id = pSessionId;

    IF (pFlgDoc = 'INVOICE') THEN
        -- Merupakan dokumen Sales Invoice

        -- ambil flg pkp
        SELECT A.flg_pkp INTO pFlgPkp
        FROM t_ou_legal A
            INNER JOIN sl_invoice B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id
        WHERE B.invoice_id = pDocId;

        -- cari flg show ppn berdasarkan SO
        /*
        SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
        FROM sl_invoice A
            INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
        WHERE A.invoice_id = pDocId;
        */

        -- Menjalankan function r_print_delivery_order_from_invoice
        IF(pFlgShowPpn='Y') THEN
            PERFORM r_print_delivery_order_from_invoice(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pDocId);
        ELSE
            PERFORM r_print_delivery_order_from_invoice_show_ppn_no(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pDocId);
        END IF;
    ELSIF (pFlgDoc = 'INVOICE_TEMP') THEN
        -- Merupakan dokumen Temporary Sales Invoice

        -- Cek dulu apakah Temp Sales Invoice sudah dibuat Sales Invoice (invoice asli) atau belum
        IF EXISTS (SELECT 1 FROM sl_invoice WHERE ref_inv_temp_id = pDocId) THEN
            SELECT invoice_id INTO vInvoiceId FROM sl_invoice WHERE ref_inv_temp_id = pDocId;

            -- ambil flg pkp
            SELECT A.flg_pkp INTO pFlgPkp
            FROM t_ou_legal A
                INNER JOIN sl_invoice B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id
            WHERE B.invoice_id = vInvoiceId;

            -- cari flg show ppn berdasarkan SO
            /*
            SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
            FROM sl_invoice A
                INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
            WHERE A.invoice_id = vInvoiceId;
            */

            -- Menjalankan function r_print_delivery_order_from_invoice
            IF(pFlgShowPpn='Y') THEN
                PERFORM r_print_delivery_order_from_invoice(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, vInvoiceId);
            ELSE
                PERFORM r_print_delivery_order_from_invoice_show_ppn_no(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, vInvoiceId);
            END IF;
        ELSE

            -- ambil flg pkp
            SELECT A.flg_pkp INTO pFlgPkp
            FROM t_ou_legal A
                INNER JOIN sl_invoice_temp B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id
            WHERE B.invoice_temp_id = pDocId;

            -- cari flg show ppn berdasarkan SO
            /*
            SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
            FROM sl_invoice_temp A
                INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
            WHERE A.invoice_temp_id = pDocId;
            */

            -- Menjalankan function r_print_delivery_order_from_invoice_temp
            IF(pFlgShowPpn='Y') THEN
                PERFORM r_print_delivery_order_from_invoice_temp(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pDocId);
            ELSE
                PERFORM r_print_delivery_order_from_invoice_temp_show_ppn_no(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pDocId);
            END IF;
        END IF;

    ELSIF (pFlgDoc = 'DO') THEN
        -- Merupakan dokumen delivery order
        -- Cek Status doc DO
        SELECT status_doc INTO vStatusDO FROM sl_do WHERE tenant_id = pTenantId AND do_id = pDocId;

        -- GET invoice_temp_id from DO
        SELECT B.invoice_temp_id INTO vInvoiceTempId
        FROM sl_do A
        INNER JOIN sl_invoice_temp B ON A.ref_id = B.ref_id AND A.ref_doc_type_id = B.ref_doc_type_id
        WHERE A.tenant_id = pTenantId AND
              A.do_id = pDocId AND
              B.status_doc = vStatusReleased;

        IF (vStatusDO = vStatusReleased and vInvoiceTempId != -99) THEN
        -- UNTUK DO yg status_doc = R -> acuan datanya dari dokumen sales invoice temp

            -- Cek dulu apakah Temp Sales Invoice sudah dibuat Sales Invoice (invoice asli) atau belum
            IF EXISTS (SELECT 1 FROM sl_invoice WHERE ref_inv_temp_id = vInvoiceTempId) THEN
                SELECT invoice_id INTO vInvoiceId FROM sl_invoice WHERE ref_inv_temp_id = vInvoiceTempId;

                -- ambil flg pkp
                SELECT A.flg_pkp INTO pFlgPkp
                FROM t_ou_legal A
                    INNER JOIN sl_invoice B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id
                WHERE B.invoice_id = vInvoiceId;

                -- cari flg show ppn berdasarkan SO
                /*
                SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
                FROM sl_invoice A
                    INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
                WHERE A.invoice_id = vInvoiceId;
                */

                -- Menjalankan function r_print_delivery_order_from_invoice
                IF(pFlgShowPpn='Y') THEN
                    PERFORM r_print_delivery_order_from_invoice(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, vInvoiceId);
                ELSE
                    PERFORM r_print_delivery_order_from_invoice_show_ppn_no(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, vInvoiceId);
                END IF;
            ELSE

                -- ambil flg pkp
                SELECT A.flg_pkp INTO pFlgPkp
                FROM t_ou_legal A
                    INNER JOIN sl_invoice_temp B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id
                WHERE B.invoice_temp_id = vInvoiceTempId;

                -- cari flg show ppn berdasarkan SO
                /*
                SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
                FROM sl_invoice_temp A
                    INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
                WHERE A.invoice_temp_id = vInvoiceTempId;
                */

                -- Menjalankan function r_print_delivery_order_from_invoice_temp
                IF(pFlgShowPpn='Y') THEN
                    PERFORM r_print_delivery_order_from_invoice_temp(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, vInvoiceTempId);
                ELSE
                    PERFORM r_print_delivery_order_from_invoice_temp_show_ppn_no(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, vInvoiceTempId);
                END IF;
            END IF;
        ELSE
        -- UNTUK DO yg status_doc = I/D -> acuan datanya dari dokumen DO

            -- ambil flg pkp
            SELECT A.flg_pkp INTO pFlgPkp
            FROM t_ou_legal A
                INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id
            WHERE B.do_id = pDocId;

            -- cari flg show ppn berdasarkan SO
            /*
            SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
            FROM sl_do A
                INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
            WHERE A.do_id = pDocId;
            */

            -- Menjalankan function r_print_form_delivery_order_escp
            IF(pFlgShowPpn='Y') THEN
                PERFORM r_print_form_delivery_order_escp(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pDocId);
            ELSE
                PERFORM r_print_form_delivery_order_escp_show_ppn_no(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pDocId);
            END IF;

        END IF;


    END IF;

    -- 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, regexp_replace(A.remark1, '\r|\n', ' ', 'g') AS remark1,
        regexp_replace(A.remark2, '\r|\n', ' ', 'g') AS 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
    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;

    RETURN NEXT pRefHeader;

    -- Return data item
    OPEN pRefDetail FOR
    SELECT product_id, product_code, product_name, brand_id, brand_code, brand_name,
        qty, uom_id, uom_code, uom_name, ROUND(gross_sell_price::Numeric)::Character Varying AS gross_sell_price, ROUND(amount::Numeric)::Character Varying AS amount, sub_total_discount, tax_amount, nett_amount, SIZE, color, ordial
    FROM tt_form_do_invoice_item
    WHERE session_id = pSessionId
    ORDER BY ordial, brand_code, product_name;

    RETURN NEXT pRefDetail;

    -- Delete table temporary
    DELETE FROM tt_form_do_invoice_header WHERE session_id = pSessionId;
    DELETE FROM tt_form_do_invoice_item WHERE session_id = pSessionId;


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /