r_supplier_sales (Baru)

CREATE OR REPLACE FUNCTION r_supplier_sales(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, 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;
    pOuId               ALIAS FOR $6;
    pStartDate          ALIAS FOR $7;
    pEndDate            ALIAS FOR $8;
    pPartnerId          ALIAS FOR $9;
    pDocType            ALIAS FOR $10;

    vEmptyId            bigint := -99;
    vEmptyString        character varying := '';
    vEmptyNumeric       numeric := 0;
    vReleaseDoc         character varying := 'R';
    vStock              character varying := 'Y';
    vFilterPartner      text := '';
    vFilterPartnerForSupplier text := '';
    vDocTypeDo          bigint := 311;
    vDocTypeDoReceipt   bigint := 526;
    vDocTypeReturnNote  bigint := 502;
    vDocTypePosShop     bigint := 401;
    vDocTypePosShopInShop       bigint := 403;
    vDocTypeVoidPosShop     bigint := 405;
    vDocTypeVoidPosShopInShop       bigint := 406;
    vReceiveGoodsManualDocTypeId bigint:=114;
    vYes                character varying := 'Y';
    vNo                 character varying := 'N';
    vDoc                character varying := '';
    vPOS                character varying := 'POS';
    vFilterDocType      text := '';
    vSubTotal       numeric := 0;
    vDPP            numeric := 0;
    vPPN            numeric := 0;

BEGIN

    DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;

    IF (pRoleId = -2) THEN

        SELECT partner_id INTO pPartnerId
        FROM m_partner_user WHERE user_id = pUserId;

        vFilterPartner := ' AND C.supplier_id =  ' || pPartnerId;
        vFilterPartnerForSupplier := ' AND D.supplier_id =  ' || pPartnerId;
    ELSE
       IF (pPartnerId <> vEmptyId) THEN
        vFilterPartner := ' AND C.supplier_id = ' || pPartnerId;
        vFilterPartnerForSupplier := ' AND D.supplier_id =  ' || pPartnerId;
       END IF;
    END IF;

    IF (pDocType = vPOS) THEN
            vDoc := 'POS';

            /*
             * insert data into table tr_report_sales_supplier for POS Shop & POS Shop In Shop from in_log_product_balance_stock
             */
            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
            INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
                AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
            WHERE A.tenant_id = $2
                AND A.doc_date BETWEEN $5 AND $6
                AND A.ou_id = $3
                AND B.flg_buy_konsinyasi = $7
                AND A.doc_type_id IN ($8, $9) ' ||
                vFilterPartner || '
                AND F.from_manual = $11
            GROUP BY C.supplier_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypePosShop, vDocTypePosShopInShop, vEmptyId, vNo;

            /*
             * insert data into table tr_report_sales_supplier for Void POS Shop & Void POS Shop In Shop from in_log_product_balance_stock
             */
            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   SUBSTRING(A.update_datetime, 1, 8) AS doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
            INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
                AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
            WHERE A.tenant_id = $2
                AND SUBSTRING(A.update_datetime, 1, 8) BETWEEN $5 AND $6
                AND A.ou_id = $3
                AND B.flg_buy_konsinyasi = $7
                AND A.doc_type_id IN ($8, $9) ' ||
                vFilterPartner || '
                AND F.from_manual = $11
            GROUP BY C.supplier_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUBSTRING(A.update_datetime, 1, 8)
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop, vEmptyId, vNo;


        ELSE
            vDoc := 'ALL';

            /*
             * insert data into table tr_report_sales_supplier for DO from in_log_product_balance_stock
             */
            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN sl_do D ON A.ref_id = D.do_id
            INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
            WHERE A.tenant_id = $2
                AND A.doc_date BETWEEN $5 AND $6
                AND A.ou_id = $3
                AND B.flg_buy_konsinyasi = $7
                AND A.doc_type_id = $8 ' ||
                vFilterPartner || '
                AND F.from_manual = $9
            GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vNo, vDocTypeDo;

            -- insert data into table tr_report_sales_supplier for GR Manual Consignment
            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, D.supplier_id,A.so_item_id, f_get_doc_desc($10),
                    B.doc_no,
                    B.doc_date, $4,
                    C.product_id, f_get_product_code(C.product_id), D.supplier_product_code,
                    f_get_product_name(C.product_id), E.style_product, E.color, E.size,
                    A.normal_price_correction, A.sold_price_used,
                    A.discount, A.margin_supp_correction, A.sold_price_after_margin, SUM(C.qty_rcv_po)
            FROM pu_po_balance_item_consignment_sold A
            INNER JOIN pu_receive_goods B ON A.receive_goods_id = B.receive_goods_id
            INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id AND A.receive_goods_item_id = C.receive_goods_item_id
            INNER JOIN m_product_consignment_supp_info D ON D.product_id = C.product_id
            INNER JOIN m_product_custom E ON E.product_id = C.product_id
            WHERE B.tenant_id = $2
                AND B.doc_date BETWEEN $5 AND $6
                AND B.ou_id = $3
                AND E.flg_buy_konsinyasi = $7
                AND B.doc_type_id = $8 ' ||
                vFilterPartner || '
                AND A.from_manual = $7
            GROUP BY D.supplier_id, A.so_item_id, B.doc_type_id, B.doc_no, B.doc_date, C.product_id,
                     D.supplier_product_code, E.style_product, E.color, E.size,
            A.normal_price_correction, A.sold_price_used, A.discount, A.margin_supp_correction, A.sold_price_after_margin
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vReceiveGoodsManualDocTypeId, vYes, vDocTypeDo;

            /*
             * insert data into table tr_report_sales_supplier for DO from in_log_product_consignment_balance_stock
             */

            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
            FROM in_log_product_consignment_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN sl_do D ON A.ref_id = D.do_id
            INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
            WHERE A.tenant_id = $2
                AND A.doc_date BETWEEN $5 AND $6
                AND A.ou_id = $3
                AND B.flg_buy_konsinyasi = $7
                AND A.doc_type_id = $8 ' ||
                vFilterPartner || '
                AND F.from_manual = $9
            GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vNo;

            /*
             * insert data into table tr_report_sales_supplier for POS Shop & POS Shop In Shop from in_log_product_balance_stock
             */
            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
            INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
                AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
            WHERE A.tenant_id = $2
                AND A.doc_date BETWEEN $5 AND $6
                AND A.ou_id = $3
                AND B.flg_buy_konsinyasi = $7
                AND A.doc_type_id IN ($8, $9) ' ||
                vFilterPartner || '
                AND F.from_manual = $11
            GROUP BY C.supplier_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypePosShop, vDocTypePosShopInShop, vEmptyId, vNo;

                /*
             * insert data into table tr_report_sales_supplier for Void POS Shop & Void POS Shop In Shop from in_log_product_balance_stock
             */
            EXECUTE '
            INSERT INTO tr_report_sales_supplier(
                    session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                    doc_date, payment_method, product_id, product_code, supplier_product_code,
                    product_name, product_style, color, psize, normal_price, sold_price,
                    discount, margin_supp, sold_price_after_margin, qty_so)
            SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   SUBSTRING(A.update_datetime, 1, 8) AS doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
            INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
                AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
            WHERE A.tenant_id = $2
                AND SUBSTRING(A.update_datetime, 1, 8) BETWEEN $5 AND $6
                AND A.ou_id = $3
                AND B.flg_buy_konsinyasi = $7
                AND A.doc_type_id IN ($8, $9) ' ||
                vFilterPartner || '
                AND F.from_manual = $11
            GROUP BY C.supplier_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUBSTRING(A.update_datetime, 1, 8)
            ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop, vEmptyId, vNo;
            /*
     * insert data into table tr_report_sales_supplier for Return Note from in_log_product_balance_stock
     */
    EXECUTE '
    INSERT INTO tr_report_sales_supplier(
            session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
            doc_date, payment_method, product_id, product_code, supplier_product_code,
            product_name, product_style, color, psize, normal_price, sold_price,
            discount, margin_supp, sold_price_after_margin, qty_so)
    SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
           A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
           f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
           F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
    FROM in_log_product_balance_stock A
    INNER JOIN m_product_custom B ON A.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
    INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
    INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
    INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
    INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = $2
        AND A.doc_date BETWEEN $5 AND $6
        AND A.ou_id = $3
        AND B.flg_buy_konsinyasi = $7
        AND A.doc_type_id IN ($9) ' ||
        vFilterPartner || '
        AND F.from_manual = $10
    GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
    F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
    ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;


    /*
     * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_balance_stock
     */
    EXECUTE '
    INSERT INTO tr_report_sales_supplier(
            session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
            doc_date, payment_method, product_id, product_code, supplier_product_code,
            product_name, product_style, color, psize, normal_price, sold_price,
            discount, margin_supp, sold_price_after_margin, qty_so)
    SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
           A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
           f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
           F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
    FROM in_log_product_balance_stock A
    INNER JOIN m_product_custom B ON A.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
    INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
    INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
    INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
    INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = $2
        AND A.doc_date BETWEEN $5 AND $6
        AND A.ou_id = $3
        AND B.flg_buy_konsinyasi = $7
        AND A.doc_type_id IN ($8) ' ||
        vFilterPartner || '
        AND F.from_manual = $10
    GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
    F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
    ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;


    /*
     * insert data into table tr_report_sales_supplier for Return Note from in_log_product_consignment_balance_stock
     */
    EXECUTE '
    INSERT INTO tr_report_sales_supplier(
            session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
            doc_date, payment_method, product_id, product_code, supplier_product_code,
            product_name, product_style, color, psize, normal_price, sold_price,
            discount, margin_supp, sold_price_after_margin, qty_so)
    SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
           A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
           f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
           F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
    FROM in_log_product_consignment_balance_stock A
    INNER JOIN m_product_custom B ON A.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
    INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
    INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
    INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
    INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = $2
        AND A.doc_date BETWEEN $5 AND $6
        AND A.ou_id = $3
        AND B.flg_buy_konsinyasi = $7
        AND A.doc_type_id IN ($9) ' ||
        vFilterPartner || '
        AND F.from_manual = $10
    GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
    F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
    ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;

    /*
     * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_consignment_balance_stock
     */
    EXECUTE '
    INSERT INTO tr_report_sales_supplier(
            session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
            doc_date, payment_method, product_id, product_code, supplier_product_code,
            product_name, product_style, color, psize, normal_price, sold_price,
            discount, margin_supp, sold_price_after_margin, qty_so)
    SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
           A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
           f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
           F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
    FROM in_log_product_consignment_balance_stock A
    INNER JOIN m_product_custom B ON A.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
    INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
    INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
    INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
    INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = $2
        AND A.doc_date BETWEEN $5 AND $6
        AND A.ou_id = $3
        AND B.flg_buy_konsinyasi = $7
        AND A.doc_type_id IN ($8) ' ||
        vFilterPartner || '
        AND F.from_manual = $10
    GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
    F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
    ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;

    END IF;

    SELECT SUM(sub_total) INTO vSubTotal FROM (
    SELECT (sold_price_after_margin * SUM(qty_so)) AS sub_total
        FROM tr_report_sales_supplier
        WHERE session_id = pSessionId
            AND tenant_id = pTenantId
        GROUP BY transaction_type, doc_no, doc_date, payment_method, product_code,
           supplier_product_code, product_name, product_style, color, psize, normal_price,
           sold_price, discount, margin_supp, sold_price_after_margin, partner_id ) Z;

    vDPP = vSubTotal / 1.1;

    vPPN =  vSubTotal - vDPP;

    Open pRefHeader FOR
    SELECT pPartnerId AS partner_id, CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pPartnerId) END AS partner_name, pStartDate AS start_date,
        pEndDate AS end_date, pDatetime AS print_datetime, pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name, vDoc AS filter_doc_type,
        ROUND(COALESCE(vDPP, 0.0), 0) AS dpp, ROUND(COALESCE(vPPN, 0.0), 0) AS ppn;
    RETURN NEXT pRefHeader;


    Open pRefDetail FOR
        SELECT A.transaction_type, A.doc_no, A.doc_date, A.payment_method, A.product_code,
           A.supplier_product_code, A.product_name, A.product_style, A.color, A.psize, A.normal_price,
           A.sold_price, A.discount, A.margin_supp, A.sold_price_after_margin, SUM(A.qty_so) AS qty_so,
           f_get_partner_name(A.partner_id) AS supplier_name, f_get_partner_code(A.partner_id) AS supplier_code ,'N' AS flg_pkp,
           (A.sold_price_after_margin * SUM(A.qty_so)) AS sub_total , ROUND(COALESCE(A.sold_price_after_margin * SUM(A.qty_so) / (1.1),0.0),0) AS dpp,
           ROUND(COALESCE(A.sold_price_after_margin * SUM(A.qty_so) - A.sold_price_after_margin * SUM(A.qty_so) / (1.1),0.0),0) AS ppn
        FROM tr_report_sales_supplier A
        INNER JOIN m_partner_npwp B ON A.partner_id = B.partner_id
        WHERE A.session_id = pSessionId
            AND A.tenant_id = pTenantId
        GROUP BY A.transaction_type, A.doc_no, A.doc_date, A.payment_method, A.product_code,
           A.supplier_product_code, A.product_name, A.product_style, A.color, A.psize, A.normal_price,
           A.sold_price, A.discount, A.margin_supp, A.sold_price_after_margin, A.partner_id,B.flg_pkp
        ORDER BY supplier_name, doc_date, doc_no;
    RETURN NEXT pRefDetail ;

    DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /