Function Report Penjualan

CREATE OR REPLACE FUNCTION rpt.r_report_sales(character varying, bigint, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';

    pSessionId                  ALIAS FOR $1;
    pRecordOwnerId              ALIAS FOR $2;
    pSalesman                   ALIAS FOR $3;
    pDateFrom                   ALIAS FOR $4;
    pDateTo                     ALIAS FOR $5;
    pCustomer                   ALIAS FOR $6;

    vEmpty                      CHARACTER VARYING := '';
    vNo                         CHARACTER VARYING := 'N';
    vYes                        CHARACTER VARYING := 'Y';
    vFilterSalesman             character varying := '';
    vFilterCustomer             character varying := '';

    vPosDocTypeId               BIGINT := 390;
    vVoidPosDocTypeId           BIGINT := 391;
    vEmptyId                    BIGINT := -99;

BEGIN

    DELETE FROM tt_summary_trx_pos WHERE session_id = pSessionId;

    IF pSalesman <> vEmpty THEN
        vFilterSalesman := ' AND UPPER(Z.salesman_name) LIKE UPPER(''%'|| pSalesman ||'%'')';
    END IF;

    IF pCustomer <> vEmpty THEN
        vFilterCustomer := ' AND ( UPPER(Z.customer_code) LIKE UPPER(''%'|| pCustomer ||'%'') OR UPPER(Z.customer_name) LIKE UPPER(''%'|| pCustomer ||'%'') )';
    END IF;

    Open pRefHeader FOR
        SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pDateFrom AS date_from, pDateTo AS date_to;
    RETURN NEXT pRefHeader;

    /**
     * Ambil data penjualan yang type dokumen nya
     * 1. POS,Koreksi POS (390)
     */
    INSERT INTO tt_summary_trx_pos (
        session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
        customer_id, salesman_id, product_id, total_trx_qty
    )
    SELECT pSessionId, C.pos_id, C.doc_type_id, A.record_owner_id, A.ref_doc_no, A.ref_doc_date, substring(A.ref_doc_date, 1, 6),
        C.customer_id, C.salesman_id, A.product_id, A.qty
    FROM in_log_product_balance_stock A
    INNER JOIN trx_pos C ON A.ref_id = C.pos_id
        AND A.ref_doc_type_id = C.doc_type_id
    WHERE A.record_owner_id = pRecordOwnerId
        AND A.ref_doc_type_id IN (vPosDocTypeId)
        AND A.ref_doc_date BETWEEN pDateFrom AND pDateTo;

    /**
     * Ambil Summary produk yg jumlah trx qty <> 0
     */
    INSERT INTO tt_rekap_summary_trx_pos_item (
        session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
        customer_id, salesman_id, product_id, total_trx_qty
    )
    SELECT session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
        customer_id, salesman_id, product_id, ABS(SUM(total_trx_qty)) AS total_qty
    FROM tt_summary_trx_pos
    WHERE session_id = pSessionId
    GROUP BY session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
        customer_id, salesman_id, product_id
    HAVING SUM(total_trx_qty) <> 0;

    /**
     * Hapus Dokumen POS yang dilakukan void
     */
    DELETE FROM tt_rekap_summary_trx_pos_item Z WHERE EXISTS (SELECT 1 FROM trx_pos A WHERE A.doc_type_id = vVoidPosDocTypeId AND A.ref_id = Z.doc_id);

    OPEN pRefDetail FOR
        EXECUTE '
            WITH TEMP AS (
                SELECT A.doc_no, A.doc_date, f_get_customer_code(A.customer_id) AS customer_code, f_get_customer_name(A.customer_id) AS customer_name,
                f_get_salesman_name(A.salesman_id) AS salesman_name, SUM((B.unit_sell_price-B.item_discount_amount)*A.total_trx_qty) AS total_trx_amount,
                COALESCE(C.due_date, $2) AS due_date, A.record_owner_id, COALESCE(SUM((D.amount_total/D.qty_total)*A.total_trx_qty), 0) AS hpp_avg
                FROM tt_rekap_summary_trx_pos_item A
                INNER JOIN trx_pos_item B ON A.doc_id = B.pos_id
                    AND A.product_id = B.product_id
                LEFT JOIN fi_invoice_ar C ON A.doc_id = C.ref_id
                        AND A.doc_type_id = C.ref_doc_type_id
                LEFT JOIN in_summary_cogs D ON  A.record_owner_id = D.record_owner_id
                    AND A.product_id = D.product_id
                    AND A.year_month = D.year_month
                WHERE session_id = $1
                GROUP BY A.doc_no, A.doc_date, C.due_date, A.customer_id, A.salesman_id, A.record_owner_id
            )
            SELECT *, total_trx_amount-hpp_avg AS total_laba_rugi
            FROM TEMP Z
            WHERE true
            '|| vFilterSalesman || vFilterCustomer ||''
        USING pSessionId, vEmpty;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_summary_trx_pos WHERE session_id = pSessionId;
    DELETE FROM tt_rekap_summary_trx_pos_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /