sl_get_sales_order_list_for_inquiry_so_rabat

CREATE OR REPLACE FUNCTION sl_get_sales_order_list_for_inquiry_so_rabat(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING,
    BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT,
    CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING,
    CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING,
    CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE

    pRefDetail                      REFCURSOR := 'refDetail';

    pSessionId                      ALIAS FOR $1;
    pTenantId                       ALIAS FOR $2;
    pUserId                         ALIAS FOR $3;
    pRoleId                         ALIAS FOR $4;
    pDatetime                       ALIAS FOR $5;

    pOuId                           ALIAS FOR $6;
    pSoDateFrom                     ALIAS FOR $7;
    pSoDateTo                       ALIAS FOR $8;
    pSoNo                           ALIAS FOR $9;
    pSalesmanCodeOrName             ALIAS FOR $10;
    pCustomerCodeOrName             ALIAS FOR $11;
    pWarehouseId                    ALIAS FOR $12;
    pProductCodeOrName              ALIAS FOR $13;
    pRgtoNo                         ALIAS FOR $14;
    pDoDateFrom                     ALIAS FOR $15;
    pDoDateTo                       ALIAS FOR $16;
    pDoNo                           ALIAS FOR $17;
    pDkbNo                          ALIAS FOR $18;
    pResiNo                         ALIAS FOR $19;
    pSalesInvoiceNo                 ALIAS FOR $20;
    pDorNo                          ALIAS FOR $21;
    pStatusSo                       ALIAS FOR $22;
    pStatusCetakSo                  ALIAS FOR $23;
    pStatusRgto                     ALIAS FOR $24;
    pStatusDo                       ALIAS FOR $25;
    pStatusDkb                      ALIAS FOR $26;
    pStatusInvoice                  ALIAS FOR $27;
    pStatusDor                      ALIAS FOR $28;

    vYes                            character varying(1) := 'Y';
    vNo                             character varying(1) := 'N';
    vEmptyString                    character varying(1) := '';
    vWorkflowApproved               character varying(8) := 'APPROVED';
    vReportCodeSo                   character varying(10) := 'SalesOrder';
    vSchemeSo                       character varying(4) := 'FA01';
    vStatusRelease                  character varying(1) := 'R';
    vAll                            character varying(3) := 'ALL';
    vDataTypeRaw1                   character varying(4) := 'RAW1';
    vDataTypeRaw2                   character varying(4) := 'RAW2';
    vDataTypeRaw3                   character varying(4) := 'RAW3';
    vDataTypeGood                   character varying(4) := 'GOOD';
    vDocTypeSo                      bigint := 301;
    vDocTypeDo                      bigint := 311;
    vDocTypeRgto                    bigint := 538;
    vEmptyId                        bigint := -99;

    vFilterOu                       text := '';
    vFilterSoNo                     text := '';
    vFilterDoNo                     text := '';
    vFilterRgtoNo                   text := '';
    vFilterSiNo                     text := '';
    vFilterDkbNo                    text := '';
    vFilterDorNo                    text := '';
    vFilterCetakSo                  text := '';
    vFilterResiNo                   text := '';
    vFilterSalesman                 text := '';
    vFilterCustomer                 text := '';
    vFilterProduct                  text := '';
    vFilterWarehouse                text := '';
    vQuery                          text := '';
    vFilterSoDate                   text := '';

BEGIN

    -- RESET TEMP TABLE
    DELETE FROM tt_so_for_inquiry_so WHERE session_id = pSessionId;
    DELETE FROM tt_inquiry_so_rabat WHERE session_id = pSessionId;

    /**
     * Create filter for OU
     */
    IF (pOuId <> vEmptyId) THEN
        vFilterOu := ' AND A.ou_id = '||pOuId||' ';
    END IF;

    /**
     * Create filter for Salesman
     */
    IF (pSalesmanCodeOrName <> vEmptyString) THEN
        vFilterSalesman := ' AND (UPPER(A.salesman_name) LIKE UPPER(''%'||pSalesmanCodeOrName||'%'')
                                    OR
                                    UPPER(A.salesman_code) LIKE UPPER(''%'||pSalesmanCodeOrName||'%'')) ';
    END IF;

    /**
     * Create filter for Customer
     */
    IF (pCustomerCodeOrName <> vEmptyString) THEN
        vFilterCustomer := ' AND (UPPER(A.customer_name) LIKE UPPER(''%'||pCustomerCodeOrName||'%'')
                                    OR
                                    UPPER(A.customer_code) LIKE UPPER(''%'||pCustomerCodeOrName||'%'')) ';
    END IF;

    /**
     * Create filter for Sales order
     */
    IF (pSoDateFrom <> vEmptyString AND pSoDateTo<>vEmptyString) THEN
        vFilterSoDate := ' AND A.doc_date BETWEEN '''||pSoDateFrom||''' AND '''||pSoDateTo||''' ';
    END IF;

    IF (pStatusCetakSo = vYes) THEN
        vFilterCetakSo := ' AND f_get_status_cetak_report(A.so_id, '||vDocTypeSo||', '''||vReportCodeSo||''') = '''||vYes||''' ';
    ELSIF (pStatusCetakSo = vNo) THEN
        vFilterCetakSo := ' AND f_get_status_cetak_report(A.so_id, '||vDocTypeSo||', '''||vReportCodeSo||''') = '''||vNo||''' ';
    END IF;

    /**
     * Create filter for warehouse
     */
    IF (pWarehouseId <> vEmptyId) THEN
        vFilterWarehouse := ' AND B.warehouse_id = '||pWarehouseId||' ';
    END IF;

    /**
     * Create filter for RGTO
     */
    IF (pRgtoNo <> vEmptyString) THEN
        vFilterRgtoNo := ' AND UPPER(C.doc_no) LIKE UPPER(''%'||pRgtoNo||'%'') ';
    END IF;

    IF (pStatusRgto = vYes) THEN
        vFilterRgtoNo := vFilterRgtoNo||' AND C.inventory_id IS NOT NULL ';
    ELSIF (pStatusRgto = vNo) THEN
        vFilterRgtoNo := vFilterRgtoNo||' AND C.inventory_id IS NULL ';
    END IF;

    /**
     * Create filter for Sales invoice
     */
    IF (pSalesInvoiceNo <> vEmptyString) THEN
        vFilterSiNo := ' AND UPPER(D.doc_no) LIKE UPPER(''%'||pSalesInvoiceNo||'%'') ';
    END IF;

    IF (pStatusInvoice = vYes) THEN
        vFilterSiNo := vFilterSiNo||' AND D.invoice_id IS NOT NULL ';
    ELSIF (pStatusInvoice = vNo) THEN
        vFilterSiNo := vFilterSiNo||' AND D.invoice_id IS NULL ';
    END IF;

    /**
     * Create filter for DO
     */
    IF (pDoNo <> vEmptyString) THEN
        vFilterDoNo := ' AND UPPER(E.doc_no) LIKE UPPER(''%'||pDoNo||'%'') ';
    END IF;

    IF (pStatusDo = vYes) THEN
        vFilterDoNo := vFilterDoNo||' AND E.do_id IS NOT NULL ';
    ELSIF (pStatusDo = vNo) THEN
        vFilterDoNo := vFilterDoNo||' AND E.do_id IS NULL ';
    END IF;

    /**
     * Create filter for DOR
     */
    IF (pDorNo <> vEmptyString) THEN
        vFilterDorNo := ' AND UPPER(F.doc_no) LIKE UPPER(''%'||pDorNo||'%'') ';
    END IF;

    IF (pStatusDor = vYes) THEN
        vFilterDorNo := vFilterDorNo||' AND F.do_receipt_id IS NOT NULL ';
    ELSIF (pStatusDor = vNo) THEN
        vFilterDorNo := vFilterDorNo||' AND F.do_receipt_id IS NULL ';
    END IF;

    /**
     * Create filter for DKB
     */
    IF (pDkbNo <> vEmptyString) THEN
        vFilterDkbNo := ' AND UPPER(C.doc_no) LIKE UPPER(''%'||pDkbNo||'%'') ';
    END IF;

    IF (pStatusDkb = vYes) THEN
        vFilterDkbNo := vFilterDkbNo||' AND C.dkb_id IS NOT NULL ';
    ELSIF (pStatusDkb = vNo) THEN
        vFilterDkbNo := vFilterDkbNo||' AND C.dkb_id IS NULL ';
    END IF;

    -- INIT DATA SO
    EXECUTE '
    WITH raw_so AS (
        SELECT A.so_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date,
            A.ou_id, A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, B.warehouse_id, A.remark,
            A.partner_id, A.salesman_id, A.curr_code, A.regular_discount_percentage,
            A.status_doc, A.workflow_status
        FROM sl_so A
        INNER JOIN sl_so_additional_for_dlg B ON A.so_id = B.so_id
        WHERE A.tenant_id = $2 '
        || vFilterOu
        || vFilterSoDate
        || vFilterCetakSo
        || vFilterWarehouse ||'
    )
    INSERT INTO tt_so_for_inquiry_so(
            session_id, so_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, warehouse_id, remark,
            partner_id, salesman_id, curr_code, regular_discount_percentage,
            status_doc, workflow_status)
    SELECT $1, A.so_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date,
            A.ou_id, A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.warehouse_id, A.remark,
            A.partner_id, A.salesman_id, A.curr_code, A.regular_discount_percentage,
            A.status_doc, A.workflow_status
    FROM raw_so A
    WHERE f_authorize_user_role_policy_ou($2, $3, $4, A.ou_id) = 1
    AND f_authorize_user_role_policy_warehouse($2, $3, $4, A.warehouse_id) = 1
    ' USING pSessionId, pTenantId, pUserId, pRoleId;

    /**
     * MELENGKAPI INFORMASI :
     * - customer code, name
     * - salesman code, name
     * - so amount
     * - so release date
     * - next role
     * - partner address id
     * - city
     */
    EXECUTE '
    WITH exisiting_data_so AS (
        SELECT A.tenant_id, A.so_id, A.doc_no AS so_no, A.doc_date AS so_date,
                A.ext_doc_no, f_get_partner_code(A.salesman_id) AS salesman_code, f_get_partner_name(A.salesman_id) AS salesman_name,
                A.partner_id, f_get_partner_code(A.partner_id) AS customer_code, f_get_partner_name(A.partner_id) AS customer_name,
                ROUND (SUM(B.nett_item_amount + B.tax_amount) - (SUM(B.nett_item_amount + B.tax_amount) * A.regular_discount_percentage / 100.0 )) AS total_amount_so,
                A.status_doc AS status_so
        FROM tt_so_for_inquiry_so A
        INNER JOIN sl_so_item B ON A.so_id = B.so_id
        WHERE A.session_id = $1
        GROUP BY A.tenant_id, A.so_id, A.doc_no, A.doc_date, A.ext_doc_no, A.salesman_id, A.partner_id, A.status_doc, A.regular_discount_percentage
    ), data_so_with_other_information AS (
        SELECT A.so_id, A.so_no, A.so_date,
                A.ext_doc_no, A.salesman_code, A.salesman_name,
                A.customer_code, A.customer_name, A.total_amount_so, A.status_so,
                CASE WHEN B.current_state = $5
                    THEN to_char(to_timestamp(B.update_datetime, ''YYYYMMDDHH24MISS''), ''YYYYMMDD'')
                    ELSE $6
                END AS so_release_date, f_get_role_name(B.current_role_id) AS next_role,
                C.partner_address_id, C.city
        FROM exisiting_data_so A
        INNER JOIN awe_currdoc_status B ON A.tenant_id = B.tenant_id AND B.scheme = $3 AND B.doc_id = A.so_id
        INNER JOIN m_partner_address C ON A.partner_id = C.partner_id AND C.flg_official = $4 '
        || vFilterCustomer
        || vFilterSalesman ||
        '
    )
    INSERT INTO tt_inquiry_so_rabat(
            session_id, type_data, so_id, so_no, so_date, so_release_date,
            customer_doc_no, salesman_code, salesman_name, customer_code,
            customer_name, total_amount_so, status_so, next_role,
            partner_address_official_id, city)
    SELECT $1 AS session_id, $2 AS type_data, A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.ext_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.partner_address_id, A.city
    FROM data_so_with_other_information A
    ' USING pSessionId, vDataTypeRaw1, vSchemeSo, vYes, vWorkflowApproved, vEmptyString;

    /**
     * MELENGKAPI INFORMASI :
     * - rgto id
     * - rgto no
     * - rgto date
     * - invoice id
     * - invoice no
     * - invoice date
     * - do id
     * - do no
     * - do date
     * - dor id
     * - dor no
     * - dor date
     */
    EXECUTE '
    WITH exisiting_data_so AS(
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.partner_address_official_id, A.city
        FROM tt_inquiry_so_rabat A
        WHERE A.session_id = $1
        AND A.type_data = $2
    ), data_so_with_other_information AS (
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.partner_address_official_id, A.city,
            COALESCE(C.inventory_id, $3) AS rgto_id,
            COALESCE(C.doc_no, $4) AS rgto_no,
            COALESCE(C.doc_date, $4) AS rgto_date,
            COALESCE(D.invoice_id, $3) AS invoice_id,
            COALESCE(D.doc_no, $4) AS invoice_no,
            COALESCE(D.doc_date, $4) AS invoice_date,
            COALESCE(E.do_id, $3) AS do_id,
            COALESCE(E.doc_no, $4) AS do_no,
            COALESCE(E.doc_date, $4) AS do_date,
            sl_get_total_amount_do_for_inquiry_so(E.do_id) AS do_amount,
            COALESCE(F.do_receipt_id, $3) AS dor_id,
            COALESCE(F.doc_no, $4) AS dor_no,
            COALESCE(F.doc_date, $4) AS dor_date
        FROM exisiting_data_so A
        LEFT JOIN in_inventory_so B ON A.so_id = B.so_id
        LEFT JOIN in_inventory C ON B.inventory_id = C.inventory_id AND C.ref_doc_type_id = $6 AND C.doc_type_id = $9 AND C.status_doc = $7
        LEFT JOIN sl_invoice D ON A.so_id = D.ref_id AND D.ref_doc_type_id = $6 AND D.status_doc = $7
        LEFT JOIN sl_do E ON A.so_id = E.ref_id AND E.ref_doc_type_id = $6 AND E.status_doc = $7
        LEFT JOIN in_do_receipt F ON E.do_id = F.ref_id AND F.ref_doc_type_id = $8 AND F.status_doc = $7
        WHERE ((E.doc_date BETWEEN $10 AND $11) OR E.doc_date IS NULL ) '
        || vFilterRgtoNo
        || vFilterSiNo
        || vFilterDoNo
        || vFilterDorNo ||'
    )
    INSERT INTO tt_inquiry_so_rabat(
            session_id, type_data, so_id, so_no, so_date, so_release_date,
            customer_doc_no, salesman_code, salesman_name, customer_code,
            customer_name, total_amount_so, status_so, next_role,
            rgto_id, rgto_no, rgto_date,
            do_id, do_no, do_date, do_amount,
            dor_id, dor_no, dor_date,
            invoice_id, invoice_no, invoice_date,
            partner_address_official_id, city)
    SELECT $1 AS session_id, $5 AS type_data, A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.partner_address_official_id, A.city
    FROM data_so_with_other_information A
    ' USING pSessionId, vDataTypeRaw1, vEmptyId, vEmptyString, vDataTypeRaw2, vDocTypeSo, vStatusRelease, vDocTypeDo, vDocTypeRgto, pDoDateFrom, pDoDateTo;

    DELETE FROM tt_inquiry_so_rabat WHERE type_data = vDataTypeRaw1;

    /**
     * MELENGKAPI INFORMASI :
     * - dkb id
     * - dkb no
     * - dkb date
     * - resi id
     * - resi no
     * - resi date
     * - resi amount
     */
    EXECUTE '
    WITH exisiting_data_so AS(
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.partner_address_official_id, A.city
        FROM tt_inquiry_so_rabat A
        WHERE A.session_id = $1
        AND A.type_data = $2
    ), data_so_with_dkb_information AS (
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.partner_address_official_id, A.city,
            COALESCE(C.dkb_id, $3) AS dkb_id_alias,
            COALESCE(C.doc_no, $4) AS dkb_no,
            COALESCE(C.doc_date, $4) AS dkb_date,
            COALESCE(C.expedition_id, $3) AS expedition_id_alias
        FROM exisiting_data_so A
        LEFT JOIN sl_dkb_item B ON A.do_id = B.ref_id AND B.ref_doc_type_id = $8
        LEFT JOIN sl_dkb C ON B.dkb_id = C.dkb_id AND C.status_doc = $7
        WHERE true '
        || vFilterDkbNo ||'
        GROUP BY A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.partner_address_official_id, A.city, dkb_id_alias, dkb_no, dkb_date, expedition_id_alias
    ), data_so_with_expedition_information AS (
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.partner_address_official_id, A.city,
            A.dkb_id_alias AS dkb_id, A.dkb_no, A.dkb_date,
            COALESCE(B.expedition_id, $3) AS expedition_id,
            COALESCE(B.expedition_code, $4) AS expedition_code,
            COALESCE(B.expedition_name, $4) AS expedition_name
        FROM data_so_with_dkb_information A
        LEFT JOIN m_expedition B ON A.expedition_id_alias = B.expedition_id
    )
    INSERT INTO tt_inquiry_so_rabat(
            session_id, type_data, so_id, so_no, so_date, so_release_date,
            customer_doc_no, salesman_code, salesman_name, customer_code,
            customer_name, total_amount_so, status_so, next_role,
            rgto_id, rgto_no, rgto_date,
            do_id, do_no, do_date, do_amount,
            dor_id, dor_no, dor_date,
            invoice_id, invoice_no, invoice_date,
            dkb_id, dkb_no, dkb_date,
            partner_address_official_id, city,
            expedition_id, expedition_code, expedition_name)
    SELECT $1 AS session_id, $5 AS type_data, A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.dkb_id, A.dkb_no, A.dkb_date,
            A.partner_address_official_id, A.city,
            A.expedition_id, A.expedition_code, A.expedition_name
    FROM data_so_with_expedition_information A
    ' USING pSessionId, vDataTypeRaw2, vEmptyId, vEmptyString, vDataTypeRaw3, vDocTypeSo, vStatusRelease, vDocTypeDo;

    DELETE FROM tt_inquiry_so_rabat WHERE type_data = vDataTypeRaw2;

    /**
     * MELENGKAPI INFORMASI :
     * - dkb id
     * - dkb no
     * - dkb date
     * - resi id
     * - resi no
     * - resi date
     * - resi amount
     */
    EXECUTE '
    WITH exisiting_data_so AS(
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.dkb_id, A.dkb_no, A.dkb_date,
            A.partner_address_official_id, A.city,
            A.expedition_id, A.expedition_code, A.expedition_name
        FROM tt_inquiry_so_rabat A
        WHERE A.session_id = $1
        AND A.type_data = $2
    ), data_so_with_resi_information AS (
        SELECT A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.dkb_id, A.dkb_no, A.dkb_date,
            A.partner_address_official_id, A.city,
            A.expedition_id, A.expedition_code, A.expedition_name,
            COALESCE(C.resi_id, $3) AS resi_id_alias,
            COALESCE(C.receipt_no, $4) AS resi_no,
            COALESCE(C.receipt_date, $4) AS resi_date,
            COALESCE(C.cost_amount, 0) AS resi_amount
        FROM exisiting_data_so A
        LEFT JOIN sl_resi_item B ON A.do_id = B.ref_id AND B.ref_doc_type_id = $8
        LEFT JOIN sl_resi C ON B.resi_id = C.resi_id
        WHERE true '
        || vFilterResiNo ||'
        GROUP BY A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.dkb_id, A.dkb_no, A.dkb_date,
            A.partner_address_official_id, A.city,
            A.expedition_id, A.expedition_code, A.expedition_name,
            resi_id_alias, resi_no, resi_date, resi_amount
    )
    INSERT INTO tt_inquiry_so_rabat(
            session_id, type_data, so_id, so_no, so_date, so_release_date,
            customer_doc_no, salesman_code, salesman_name, customer_code,
            customer_name, total_amount_so, status_so, next_role,
            rgto_id, rgto_no, rgto_date,
            do_id, do_no, do_date, do_amount,
            dor_id, dor_no, dor_date,
            resi_id, resi_no, resi_date, resi_amount,
            invoice_id, invoice_no, invoice_date,
            dkb_id, dkb_no, dkb_date,
            partner_address_official_id, city,
            expedition_id, expedition_code, expedition_name)
    SELECT $1 AS session_id, $5 AS type_data, A.so_id, A.so_no, A.so_date, A.so_release_date,
            A.customer_doc_no, A.salesman_code, A.salesman_name, A.customer_code,
            A.customer_name, A.total_amount_so, A.status_so, A.next_role,
            A.rgto_id, A.rgto_no, A.rgto_date,
            A.do_id, A.do_no, A.do_date, A.do_amount,
            A.dor_id, A.dor_no, A.dor_date,
            A.resi_id_alias, A.resi_no, A.resi_date, A.resi_amount,
            A.invoice_id, A.invoice_no, A.invoice_date,
            A.dkb_id, A.dkb_no, A.dkb_date,
            A.partner_address_official_id, A.city,
            A.expedition_id, A.expedition_code, A.expedition_name
    FROM data_so_with_resi_information A
    ' USING pSessionId, vDataTypeRaw3, vEmptyId, vEmptyString, vDataTypeGood, vDocTypeSo, vStatusRelease, vDocTypeDo;

    DELETE FROM tt_inquiry_so_rabat WHERE type_data = vDataTypeRaw3;

    Open pRefDetail FOR
        SELECT so_no, so_date, so_release_date AS release_so_date,
            do_no, do_date, do_amount, dkb_no, dkb_date, resi_no AS receipt_no,
            resi_date AS receipt_date, resi_amount AS nilai_resi, city, expedition_name
          FROM tt_inquiry_so_rabat
          WHERE session_id = pSessionId
          AND type_data = vDataTypeGood
          ORDER BY so_no;
    RETURN NEXT pRefDetail;

    -- RESET TEMP TABLE
    DELETE FROM tt_so_for_inquiry_so WHERE session_id = pSessionId;
    DELETE FROM tt_inquiry_so_rabat WHERE session_id = pSessionId;

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