versi excel

/*
 * nilai yang di pakai adalah sell price nya
 */

CREATE OR REPLACE FUNCTION r_sales_detail(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                          REFCURSOR := 'refHeader';
    pRefDetail                          REFCURSOR := 'refDetail';
    pSessionId                          ALIAS FOR $1;
    pTenantId                           ALIAS FOR $2;
    pOuId                               ALIAS FOR $3;
    pUserId                             ALIAS FOR $4;
    pRoleId                             ALIAS FOR $5;
    pDatetime                           ALIAS FOR $6;

    pCustomerCodeFrom                   ALIAS FOR $7;
    pCustomerCodeTo                     ALIAS FOR $8;
    pDateFrom                           ALIAS FOR $9;
    pDateTo                             ALIAS FOR $10;

    vCustomerFrom                       character varying(1024) := '';
    vCustomerTo                         character varying(1024) := '';
    vRawData                            character varying(10) := 'RAW-DATA';
    vSumData                            character varying(10) := 'SUM-DATA';
    vDocTypeSalesInvoice                bigint := 321;
    vDocTypeSalesInvoiceTemp            bigint := 361;
    vDocTypeReturnSalesInvoiceByBrand   bigint := 380;
    vDocTypeSoByBrand                   bigint := 398;
    vDocTypeDo                          bigint := 311;
    vDocTypeDoReceipt                   bigint := 526;
    --vDocTypeConvExcOut                    bigint := 395;
    vDocTypeRRS                         bigint := 381;--Request Return Sales
    vDocTypeReturnNote                  bigint := 502;

    vEmpty                              character varying := '';
    vReportName                         character varying := 'FormDeliveryOrderFromInvoiceEscp';
    vSudahCetak                         character varying := 'Already Printed';
    vBelumCetak                         character varying := 'Not Yet';
    vYes                                character varying := 'Y';
    vNo                                 character varying := 'N';
    vStatusDocVoid                      character varying := 'V';
    vEmptyPrice                 numeric := 0;
    vStatusDocReleased                  character varying := 'R';
BEGIN

    -- DELETE TEMP DATA --
    DELETE FROM tt_sl_invoice WHERE session_id = pSessionId;
    DELETE FROM tt_report_sales_journal WHERE session_id = pSessionId;
    DELETE FROM tt_sl_invoice_item WHERE session_id = pSessionId;

    SELECT partner_name INTO vCustomerFrom
    FROM m_partner
    WHERE tenant_id = pTenantId AND partner_code = pCustomerCodeFrom;

    SELECT partner_name INTO vCustomerTo
    FROM m_partner
    WHERE tenant_id = pTenantId AND partner_code = pCustomerCodeTo;

    -- HEADER --
    Open pRefHeader FOR
    SELECT pDatetime AS datetime, f_get_ou_name(pOuId) AS ou_name, f_get_username(pUserId) AS username,
        pDateFrom AS date_from, pDateTo AS date_to, pCustomerCodeFrom AS partner_code_from,
        vCustomerFrom AS partner_name_from, pCustomerCodeTo AS partner_code_to,
        vCustomerTo AS partner_name_to, f_get_role_name(pRoleId) AS rolename, pDatetime AS datetime,
        CASE WHEN (pCustomerCodeFrom !='ALL' AND pCustomerCodeTo !='ALL')
            THEN pCustomerCodeFrom||'/'||vCustomerFrom||' - '||pCustomerCodeTo||'/'||vCustomerTo
            ELSE 'ALL'
        END AS partner;
    RETURN NEXT pRefHeader;

    -- MASUKAN SL INVOICE BERDASARKAN FILTER YANG DI PILIH KE TABLE TEMP --
    IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.invoice_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.remark,
            A.partner_id, A.salesman_id, A.status_doc, A.workflow_status, A.version,
            A.ref_inv_temp_id, C.mou_id, C.promo_id, C.product_launching_id,
            f_get_city_by_partner(B.partner_id) AS city, f_get_province_by_partner(B.partner_id) AS province
        FROM fi_invoice_ar_balance D
            INNER JOIN sl_invoice A ON D.invoice_ar_id = A.invoice_id AND D.doc_type_id = A.doc_type_id
            INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
            INNER JOIN sl_so_info C ON B.so_id = C.so_id
        WHERE D.tenant_id = pTenantId
            AND D.ou_id = pOuId
            AND D.doc_type_id IN (vDocTypeSalesInvoice)
            AND A.doc_date BETWEEN pDateFrom AND pDateTo
            AND f_get_partner_code(A.partner_id) BETWEEN pCustomerCodeFrom AND pCustomerCodeTo;
    ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.invoice_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.remark,
            A.partner_id, A.salesman_id, A.status_doc, A.workflow_status, A.version,
            A.ref_inv_temp_id, C.mou_id, C.promo_id, C.product_launching_id,
            f_get_city_by_partner(B.partner_id) AS city, f_get_province_by_partner(B.partner_id) AS province
        FROM fi_invoice_ar_balance D
            INNER JOIN sl_invoice A ON D.invoice_ar_id = A.invoice_id AND D.doc_type_id = A.doc_type_id
            INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
            INNER JOIN sl_so_info C ON B.so_id = C.so_id
        WHERE D.tenant_id = pTenantId
            AND D.ou_id = pOuId
            AND D.doc_type_id IN (vDocTypeSalesInvoice)
            AND A.doc_date BETWEEN pDateFrom AND pDateTo;
    END IF;

    -- MASUKAN SL INVOICE TEMP YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
    -- Mod by WTC, 20161006, ambil SL Invoice Temp yang statusnya tidak void, dan perbaiki join tuk mengecek apakah sudah diubah menjadi sales invoice
    IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.invoice_temp_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.remark,
            A.partner_id, A.salesman_id, A.status_doc, A.workflow_status, A.version,
            -99, C.mou_id, C.promo_id, C.product_launching_id,
            f_get_city_by_partner(B.partner_id) AS city, f_get_province_by_partner(B.partner_id) AS province
        FROM fi_invoice_ar_balance D
            INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
            INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
            INNER JOIN sl_so_info C ON B.so_id = C.so_id
        WHERE D.tenant_id = pTenantId
            AND D.ou_id = pOuId
            AND D.doc_type_id = vDocTypeSalesInvoiceTemp
            AND A.doc_date BETWEEN pDateFrom AND pDateTo
            AND f_get_partner_code(A.partner_id) BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
            AND A.status_doc <> vStatusDocVoid;

    ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.invoice_temp_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.remark,
            A.partner_id, A.salesman_id, A.status_doc, A.workflow_status, A.version,
            -99, C.mou_id, C.promo_id, C.product_launching_id,
            f_get_city_by_partner(B.partner_id) AS city, f_get_province_by_partner(B.partner_id) AS province
        FROM fi_invoice_ar_balance D
            INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
            INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
            INNER JOIN sl_so_info C ON B.so_id = C.so_id
        WHERE D.tenant_id = pTenantId
            AND D.ou_id = pOuId
            AND D.doc_type_id = vDocTypeSalesInvoiceTemp
            AND A.doc_date BETWEEN pDateFrom AND pDateTo
            AND A.status_doc <> vStatusDocVoid;
    END IF;

    -- MASUKAN RETURN SALES INVOICE BY BRAND (RSIB)
    IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.invoice_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.remark,
            A.partner_id, A.salesman_id, A.status_doc, A.workflow_status, A.version,
            -99, COALESCE(C.mou_id, -99), COALESCE(C.promo_id, -99), COALESCE(C.product_launching_id, -99),
            f_get_city_by_partner(D.partner_id) AS city, f_get_province_by_partner(D.partner_id) AS province
        FROM fi_invoice_ar_balance E
            INNER JOIN sl_invoice A ON E.invoice_ar_id = A.invoice_id AND E.doc_type_id = A.doc_type_id
            INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = vDocTypeRRS
            LEFT OUTER JOIN sl_so B ON D.ref_id = B.so_id AND D.ref_doc_type_id = vDocTypeSoByBrand
            LEFT OUTER JOIN sl_so_info C ON B.so_id = C.so_id
        WHERE E.tenant_id = pTenantId
            AND E.ou_id = pOuId
            AND E.doc_type_id = vDocTypeReturnSalesInvoiceByBrand
            AND A.doc_date BETWEEN pDateFrom AND pDateTo
            AND f_get_partner_code(A.partner_id) BETWEEN pCustomerCodeFrom AND pCustomerCodeTo;
    ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.invoice_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.remark,
            A.partner_id, A.salesman_id, A.status_doc, A.workflow_status, A.version,
            -99, COALESCE(C.mou_id, -99), COALESCE(C.promo_id, -99), COALESCE(C.product_launching_id, -99),
            f_get_city_by_partner(D.partner_id) AS city, f_get_province_by_partner(D.partner_id) AS province
        FROM fi_invoice_ar_balance E
            INNER JOIN sl_invoice A ON E.invoice_ar_id = A.invoice_id AND E.doc_type_id = A.doc_type_id
            INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = vDocTypeRRS
            LEFT OUTER JOIN sl_so B ON D.ref_id = B.so_id AND D.ref_doc_type_id = vDocTypeSoByBrand
            LEFT OUTER JOIN sl_so_info C ON B.so_id = C.so_id
        WHERE E.tenant_id = pTenantId
            AND E.ou_id = pOuId
            AND E.doc_type_id = vDocTypeReturnSalesInvoiceByBrand
            AND A.doc_date BETWEEN pDateFrom AND pDateTo;
    END IF;

    -- MASUKAN RETURN NOTE yang belum dibuat RSIB(approved)
    IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.inventory_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date,
            A.ou_from_id, A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark,
            A.partner_id, -99 AS salesman_id, A.status_doc, A.workflow_status, A.version,
            -99, COALESCE(D.mou_id, -99), COALESCE(D.promo_id, -99), COALESCE(D.product_launching_id, -99),
            f_get_city_by_partner(A.partner_id) AS city, f_get_province_by_partner(A.partner_id) AS province
        FROM in_inventory A
            INNER JOIN sl_request_return_sales B ON A.ref_id = B.request_return_sales_id AND A.ref_doc_type_id = vDocTypeRRS
            LEFT OUTER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = vDocTypeSoByBrand
            LEFT OUTER JOIN sl_so_info D ON C.so_id = D.so_id
        WHERE A.tenant_id = pTenantId
            AND A.ou_from_id = pOuId
            AND A.doc_type_id = vDocTypeReturnNote
            AND A.doc_date BETWEEN pDateFrom AND pDateTo
            AND f_get_partner_code(A.partner_id) BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
            AND A.status_doc = vStatusDocReleased
            AND NOT EXISTS(SELECT 1 FROM sl_invoice X
                           WHERE X.doc_type_id = vDocTypeReturnSalesInvoiceByBrand
                                AND B.request_return_sales_id = X.ref_id
                                AND B.doc_type_id = X.ref_doc_type_id
                                AND X.status_doc = vStatusDocReleased);

    ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
        INSERT INTO tt_sl_invoice(
            session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, salesman_id, status_doc, workflow_status, version,
            ref_inv_temp_id, mou_id, promo_id, product_launching_id,
            city, province)
        SELECT pSessionId, A.inventory_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date,
            A.ou_from_id, A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark,
            A.partner_id, -99 AS salesman_id, A.status_doc, A.workflow_status, A.version,
            -99, COALESCE(D.mou_id, -99), COALESCE(D.promo_id, -99), COALESCE(D.product_launching_id, -99),
            f_get_city_by_partner(A.partner_id) AS city, f_get_province_by_partner(A.partner_id) AS province
        FROM in_inventory A
            INNER JOIN sl_request_return_sales B ON A.ref_id = B.request_return_sales_id AND A.ref_doc_type_id = vDocTypeRRS
            LEFT OUTER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = vDocTypeSoByBrand
            LEFT OUTER JOIN sl_so_info D ON C.so_id = D.so_id
        WHERE A.tenant_id = pTenantId
            AND A.ou_from_id = pOuId
            AND A.doc_type_id = vDocTypeReturnNote
            AND A.doc_date BETWEEN pDateFrom AND pDateTo
            AND A.status_doc = vStatusDocReleased
            AND NOT EXISTS(SELECT 1 FROM sl_invoice X
                           WHERE X.doc_type_id = vDocTypeReturnSalesInvoiceByBrand
                                AND B.request_return_sales_id = X.ref_id
                                AND B.doc_type_id = X.ref_doc_type_id
                                AND X.status_doc = vStatusDocReleased);
    END IF;

    --Insert item masing-masing invoice yang sudah diinsert (SI,TSI)
    INSERT INTO tt_sl_invoice_item (
        session_id, invoice_id, doc_type_id, product_balance_id, product_id,
        brand_id, sub_ctgr_product_id,
        serial_number, lot_number, product_expired_date,
        qty, uom_id, curr_code, gross_amount,
        tax_amount, discount_amount, nett_amount,
        so_item_id, remark)
    SELECT A.session_id, A.invoice_id, A.doc_type_id, E.product_balance_id, E.product_id,
        f_get_brand_by_product_id(E.product_id), f_get_sub_ctgr_product_by_product_id(E.product_id),
        F.serial_number, F.lot_number, F.product_expired_date,
        E.qty_dlv_int, E.base_uom_id, C.curr_code, C.gross_sell_price,
        C.tax_price, C.discount_amount, C.nett_sell_price,
        C.so_item_id, E.remark
    FROM tt_sl_invoice A
        INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
        INNER JOIN sl_so_item C ON B.so_id = C.so_id
        INNER JOIN sl_do_item D ON C.so_item_id = D.ref_id
        INNER JOIN sl_do_product E ON D.do_item_id = E.do_item_id
        INNER JOIN in_product_balance F ON E.product_balance_id = F.product_balance_id
    WHERE A.session_id = pSessionId
        AND A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeSalesInvoiceTemp);

    --insert item dari RSI By Brand
    INSERT INTO tt_sl_invoice_item (
        session_id, invoice_id, doc_type_id, product_balance_id, product_id,
        brand_id, sub_ctgr_product_id,
        serial_number, lot_number, product_expired_date,
        qty, uom_id, curr_code, gross_amount,
        tax_amount, discount_amount, nett_amount,
        so_item_id, remark)
    SELECT A.session_id, A.invoice_id, A.doc_type_id, C.product_balance_id, C.product_id,
        f_get_brand_by_product_id(C.product_id), f_get_sub_ctgr_product_by_product_id(C.product_id),
        COALESCE(D.serial_number, vEmpty), COALESCE(D.lot_number, vEmpty), COALESCE(D.product_expired_date, vEmpty),
        C.qty_realization * -1, C.base_uom_id, E.curr_code, E.gross_sell_price,
        E.tax_price, vEmptyPrice, E.nett_sell_price,
        C.inventory_item_id, C.remark
    FROM tt_sl_invoice A
    INNER JOIN sl_request_return_sales_brand_item E ON A.ref_id = E.request_return_sales_id
    INNER JOIN in_inventory B ON A.ref_id = B.ref_id AND A.ref_doc_type_id = B.ref_doc_type_id AND A.ref_doc_type_id = vDocTypeRRS
    INNER JOIN in_inventory_item C ON B.inventory_id = C.inventory_id AND E.brand_id = f_get_brand_by_product_id(C.product_id)
    LEFT OUTER JOIN in_product_balance D ON C.product_balance_id = D.product_balance_id
    WHERE A.session_id = pSessionId
        AND A.doc_type_id = vDocTypeReturnSalesInvoiceByBrand;

    --insert item dari Return Note
    INSERT INTO tt_sl_invoice_item (
        session_id, invoice_id, doc_type_id, product_balance_id, product_id,
        brand_id, sub_ctgr_product_id,
        serial_number, lot_number, product_expired_date,
        qty, uom_id, curr_code, gross_amount,
        tax_amount, discount_amount, nett_amount,
        so_item_id, remark)
    SELECT A.session_id, A.invoice_id, A.doc_type_id, B.product_balance_id, B.product_id,
        f_get_brand_by_product_id(B.product_id), f_get_sub_ctgr_product_by_product_id(B.product_id),
        COALESCE(D.serial_number, vEmpty), COALESCE(D.lot_number, vEmpty), COALESCE(D.product_expired_date, vEmpty),
        B.qty_realization * -1, B.base_uom_id, C.curr_code, C.gross_sell_price,
        C.tax_price, vEmptyPrice, C.nett_sell_price,
        B.inventory_item_id, B.remark
    FROM tt_sl_invoice A
    INNER JOIN in_inventory_item B ON A.invoice_id = B.inventory_id
    INNER JOIN sl_request_return_sales_brand_item C ON A.ref_id = C.request_return_sales_id AND f_get_brand_by_product_id(B.product_id) = C.brand_id
    LEFT OUTER JOIN in_product_balance D ON B.product_balance_id = D.product_balance_id
    WHERE A.session_id = pSessionId
        AND A.doc_type_id = vDocTypeReturnNote;

    -- Add by David, 20161006, ambil juga data item DO Receipt
    --insert item dari Do Receipt
    INSERT INTO tt_sl_invoice_item (
        session_id, invoice_id, doc_type_id, product_balance_id, product_id,
        brand_id, sub_ctgr_product_id,
        serial_number, lot_number, product_expired_date,
        qty, uom_id, curr_code, gross_amount,
        tax_amount, discount_amount, nett_amount,
        so_item_id, remark)
    SELECT A.session_id, A.invoice_id, A.doc_type_id, H.product_balance_id, H.product_id,
        f_get_brand_by_product_id(H.product_id), f_get_sub_ctgr_product_by_product_id(H.product_id),
        F.serial_number, F.lot_number, F.product_expired_date,
        H.qty_return * -1, H.uom_id, C.curr_code, C.gross_sell_price,
        C.tax_price, C.discount_amount, C.nett_sell_price,
        C.so_item_id, H.remark
    FROM tt_sl_invoice A
        INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
        INNER JOIN sl_so_item C ON B.so_id = C.so_id
        INNER JOIN sl_do_item D ON C.so_item_id = D.ref_id
        INNER JOIN in_do_receipt_item G ON G.ref_doc_type_id = vDocTypeDo AND G.ref_id = D.do_item_id
        INNER JOIN in_do_receipt I ON G.do_receipt_id = I.do_receipt_id AND I.status_doc = vStatusDocReleased
        INNER JOIN in_do_receipt_product H ON H.do_receipt_item_id = G.do_receipt_item_id
        INNER JOIN in_product_balance F ON F.product_balance_id = H.product_balance_id
    WHERE A.session_id = pSessionId
        AND A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeSalesInvoiceTemp);

    -- DETAIL --
    Open pRefDetail FOR

    WITH data_from_inv_and_temp_inv AS (
        -- Data dari Sales Invoice dan Temporary Sales Invoice
        SELECT A.invoice_id, A.doc_no AS invoice_no, A.doc_date AS invoice_date, f_get_partner_code(A.salesman_id) AS salesman_code,
            f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name, A.city, A.province,
            COALESCE(C.doc_no, vEmpty) AS mou_no, COALESCE(D.promo_code, vEmpty) AS promo_code,
            f_get_sub_ctgr_product_name(B.sub_ctgr_product_id) AS sub_ctgr_product_name, f_get_brand_code(B.brand_id) AS brand_code, f_get_brand_name(B.brand_id) AS brand_name,
            f_get_product_name(B.product_id) AS product_code, B.lot_number, B.product_expired_date,
            SUM(B.qty) AS qty, B.gross_amount * SUM(B.qty) AS gross_amount, B.discount_amount * SUM(B.qty) AS discount_amount, B.nett_amount * SUM(B.qty) AS nett_amount, B.tax_amount  * SUM(B.qty) AS tax_amount, A.remark, --COALESCE(B.remark, A.remark) AS remark,
            (CASE WHEN A.doc_type_id = vDocTypeSalesInvoiceTemp THEN vNo ELSE vYes END) AS flg_invoice,
            COALESCE(E.promo_code, vEmpty) AS product_launching_code,
            ((B.nett_amount+B.tax_amount) * SUM(B.qty)) AS total_amount, B.gross_amount AS harga_satuan,
            (CASE WHEN f_get_status_cetak_report(A.ref_id, A.ref_doc_type_id, vReportName) = 'Y' THEN vSudahCetak ELSE vBelumCetak END) AS status_cetak, B.serial_number
        FROM tt_sl_invoice A
            INNER JOIN tt_sl_invoice_item B ON A.session_id = B.session_id AND A.invoice_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
            LEFT OUTER JOIN sl_mou C ON A.mou_id = C.mou_id
            LEFT OUTER JOIN m_promo D ON A.promo_id = D.promo_id
            LEFT OUTER JOIN m_promo E ON A.product_launching_id = E.promo_id
        WHERE A.session_id = pSessionId
            AND A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeSalesInvoiceTemp)
        GROUP BY A.invoice_id, A.doc_no, A.doc_date, A.salesman_id, A.partner_id, A.city, A.province, C.doc_no, D.promo_code,
                 B.sub_ctgr_product_id, B.brand_id, B.product_id, B.lot_number, B.product_expired_date,
                 B.gross_amount, B.discount_amount, B.nett_amount, B.tax_amount, B.remark, A.remark, A.doc_type_id, E.promo_code, A.ref_id, A.ref_doc_type_id, B.serial_number

    ), data_from_rsib_and_rn AS (
        -- Data RSIB dan Return Note yg belum dibuat RSIB(approved)
        SELECT A.invoice_id, A.doc_no AS invoice_no, A.doc_date AS invoice_date, f_get_partner_code(A.salesman_id) AS salesman_code,
            f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name, A.city, A.province,
            COALESCE(C.doc_no, vEmpty) AS mou_no, COALESCE(D.promo_code, vEmpty) AS promo_code,
            f_get_sub_ctgr_product_name(B.sub_ctgr_product_id) AS sub_ctgr_product_name, f_get_brand_code(B.brand_id) AS brand_code, f_get_brand_name(B.brand_id) AS brand_name,
            f_get_product_name(B.product_id) AS product_code, B.lot_number, B.product_expired_date,
            SUM(B.qty) AS qty, B.gross_amount * SUM(B.qty) AS gross_amount, B.discount_amount * SUM(B.qty) AS discount_amount, B.nett_amount * SUM(B.qty) AS nett_amount, B.tax_amount * SUM(B.qty) AS tax_amount, A.remark, --COALESCE(B.remark, A.remark) AS remark,
            CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoiceByBrand THEN CAST(vYes AS text)
                 ELSE CAST(vNo AS text) END AS flg_invoice,
            COALESCE(E.promo_code, vEmpty) AS product_launching_code,
            ((B.nett_amount+B.tax_amount) * SUM(B.qty)) AS total_amount, B.gross_amount AS harga_satuan,
            CAST('-'  AS text) AS status_cetak, B.serial_number
        FROM tt_sl_invoice A
            INNER JOIN tt_sl_invoice_item B ON A.session_id = B.session_id AND A.invoice_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
            LEFT OUTER JOIN sl_mou C ON A.mou_id = C.mou_id
            LEFT OUTER JOIN m_promo D ON A.promo_id = D.promo_id
            LEFT OUTER JOIN m_promo E ON A.product_launching_id = E.promo_id
        WHERE A.session_id = pSessionId
            AND A.doc_type_id IN (vDocTypeReturnSalesInvoiceByBrand, vDocTypeReturnNote)
        GROUP BY A.invoice_id, A.doc_no, A.doc_date, A.salesman_id, A.partner_id, A.city, A.province,
                 C.doc_no, D.promo_code, B.sub_ctgr_product_id, B.brand_id, B.product_id, B.lot_number, B.product_expired_date,
                 B.gross_amount, B.discount_amount, B.nett_amount, B.tax_amount, B.remark, A.remark, E.promo_code, B.serial_number, A.doc_type_id
    )
        SELECT * FROM data_from_inv_and_temp_inv WHERE qty <> 0
        UNION ALL
        SELECT * FROM data_from_rsib_and_rn WHERE qty <> 0
        ORDER BY province, city, salesman_code, partner_code, invoice_no, product_code, lot_number;

    RETURN NEXT pRefDetail;

    -- DELETE TEMP DATA --
    DELETE FROM tt_sl_invoice WHERE session_id = pSessionId;
    DELETE FROM tt_report_sales_journal WHERE session_id = pSessionId;
    DELETE FROM tt_sl_invoice_item WHERE session_id = pSessionId;

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