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;
    vPengurangHutangDocTypeId   bigint := 251;
    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, record_owner_id, doc_no, doc_date, year_month,
        customer_id, salesman_id, due_date, product_id, total_trx_amount
    )
    SELECT pSessionId, 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, D.due_date, A.product_id, B.item_amount_after_discount
    FROM in_log_product_balance_stock A
    INNER JOIN trx_pos_item B ON A.ref_id = B.pos_id
    INNER JOIN trx_pos C ON A.ref_id = C.pos_id
        AND A.ref_doc_type_id = C.doc_type_id
    INNER JOIN fi_invoice_ar D ON C.pos_id = D.ref_id
        AND C.doc_type_id = D.ref_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
        AND A.qty <> 0
    GROUP BY A.record_owner_id, A.ref_doc_no, A.ref_doc_date,
        C.customer_id, C.salesman_id, D.due_date, A.product_id, B.item_amount_after_discount;

    /**
     * Ambil data penjualan yang type dokumen nya
     * 2. Void POS (391)
     */
    WITH TEMP AS (
        SELECT A.ref_doc_no, A.ref_doc_date, C.customer_id, C.salesman_id, E.product_id, E.item_amount_after_discount AS total_trx_amount,
            C.doc_type_id AS void_doc_type_id, C.pos_id AS void_pos_id, A.record_owner_id
        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
        INNER JOIN trx_pos D ON C.ref_doc_type_id = D.doc_type_id
            AND C.ref_id = D.pos_id
        INNER JOIN trx_pos_item E ON D.pos_id = E.pos_id
        WHERE A.record_owner_id = pRecordOwnerId
            AND A.ref_doc_type_id IN (vVoidPosDocTypeId)
            AND A.ref_doc_date BETWEEN pDateFrom AND pDateTo
            AND A.qty <> 0
        GROUP BY A.ref_doc_no, A.ref_doc_date, C.customer_id, C.salesman_id, E.product_id, E.item_amount_after_discount,
            C.doc_type_id, C.pos_id, A.record_owner_id
    )
    INSERT INTO tt_summary_trx_pos (
        session_id, record_owner_id, doc_no, doc_date, year_month,
        customer_id, salesman_id, due_date, product_id, total_trx_amount
    )
    SELECT pSessionId, A.record_owner_id, A.ref_doc_no, A.ref_doc_date, substring(A.ref_doc_date, 1, 6),
        A.customer_id, A.salesman_id, B.due_date, A.product_id, A.total_trx_amount
    FROM TEMP A
    INNER JOIN fi_invoice_ar B ON A.void_doc_type_id = B.ref_doc_type_id
        AND A.void_pos_id = B.ref_id;

    OPEN pRefDetail FOR
        EXECUTE '
        SELECT *, total_trx_amount-hpp_avg AS total_laba_rugi
        FROM (
            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,
                    A.due_date, COALESCE(SUM(A.total_trx_amount), 0) AS total_trx_amount,
                    CASE WHEN SUM(B.qty_total) = 0
                    THEN
                        0
                    ELSE
                        COALESCE(SUM(B.amount_total)/SUM(B.qty_total), 0)
                    END AS hpp_avg
            FROM tt_summary_trx_pos A
            LEFT JOIN in_summary_cogs B ON A.record_owner_id = B.record_owner_id
                AND A.year_month = B.year_month
                AND A.product_id = B.product_id
            WHERE A.session_id = $1
            GROUP BY A.doc_no, A.doc_date, A.customer_id, A.salesman_id, A.due_date
        ) Z
        WHERE true
        ' || vFilterSalesman || vFilterCustomer ||''
        USING pSessionId;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_summary_trx_pos WHERE session_id = pSessionId;

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