r_outlet_detail_analysis_gross_profit_20170801

/**
 * Modified by Adrian, Aug 1, 2017
 * monthly_price_amount diambil per year-month
 */

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

    vEmptyValue             character varying(1);
    vAllId                  bigint;
    vEmptyId                bigint;
    vParentOuId             bigint;
    vCount                  bigint;
    vRoundingMode           character varying(5);
    vYearMonth              character varying(6);
    vDatetime               character varying(14);

    vCurrentYearMonth       character varying(6);
    vEndYearMonth           character varying(6);

BEGIN

    vEmptyValue := '';
    vAllId := -99;
    vEmptyId := -99;
    vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;

    SELECT TO_CHAR(now(), 'YYYYMM') INTO vYearMonth;

    SELECT TO_CHAR(now(), 'YYYYMMDDHH24MISS') INTO vDatetime;

    DELETE FROM tr_commision_gross_profit WHERE session_id = pSessionId;
    DELETE FROM tr_detail_gross_profit WHERE session_id = pSessionId;
    DELETE FROM tt_input_data WHERE session_id = pSessionId;
    DELETE FROM tt_output_data WHERE session_id = pSessionId;
    DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
    DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;

    /*
     * ambil semua trx pos di main business unit yang tidak di void
     * jika transaksi itu ada item assembly ambil berdasarkan core product id nya
     * ambil juga harga beli terakhir dari item itu sesuai dengan tahun bulan
     */
    INSERT INTO tr_commision_gross_profit(
           session_id, tenant_id, year_month_date, ou_id, ou_code, ou_name, ou_parent_id,
           doc_type_id, doc_no, doc_date, line_no,
           product_id, curr_code, qty, nett_sell_price, nett_amount_item,
           commision_ou_id, commision_type, commision_percentage, commision_curr_code, commision_amount,
           monthly_price_curr_code, monthly_price_amount
    )
    SELECT pSessionId, A.tenant_id, SUBSTRING(A.doc_date, 1, 6), A.ou_id, B.ou_code, B.ou_name, B.ou_parent_id,
           A.doc_type_id, A.doc_no, A.doc_date, C.line_no,
           COALESCE(G.core_product_id, C.product_id), C.curr_code, C.qty, C.nett_sell_price, C.nett_amount_item,
           D.commision_ou_id, D.commision_type, D.commision_percentage, D.curr_code, D.commision_amount,
           '', 0
    FROM i_trx_pos A
    INNER JOIN t_ou B ON A.ou_id = B.ou_id
    INNER JOIN i_trx_pos_item C ON A.tenant_id = C.tenant_id AND A.trx_pos_id = C.trx_pos_id AND A.process_no = C.process_no
    INNER JOIN m_commision_ou D ON C.group_product_ou_id = D.group_product_ou_id
    LEFT OUTER JOIN m_ext_product G ON C.product_id = G.product_id
    WHERE A.tenant_id = pTenantId
    AND A.doc_date BETWEEN pDateFrom AND pDateTo
    AND A.ou_id = pOuId
    AND B.ou_parent_id = vParentOuId
    AND NOT EXISTS (
        SELECT 1 FROM i_trx_log_voided_pos F
        WHERE A.tenant_id = F.tenant_id
        AND A.doc_no = F.doc_no
        AND A.doc_date = F.doc_date
        AND A.ou_id = F.ou_id
    );

    /* v329 data yang diambil dari pu_monthly_price_product diubah
     * menjadi ambil dari table
     * tt_out_latest_purchasing_price_by_date menggunakan
     * pu_get_latest_purchasing_price_by_date yang sebelumnya datanya dimasukkan ke
     * tt_in_latest_purchasing_price_by_date */

    ANALYZE tr_commision_gross_profit;

    SELECT MIN(year_month_date), MAX(year_month_date) FROM tr_commision_gross_profit INTO vCurrentYearMonth, vEndYearMonth;

    --Modified by Adrian, Aug 1, 2017
    --monthly_price_amount di-update per year-month
    WHILE vCurrentYearMonth <= vEndYearMonth LOOP

        DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
        DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;

        INSERT INTO tt_in_latest_purchasing_price_by_date(session_id, tenant_id, ou_bu_id, doc_date, product_id)
            SELECT pSessionId, pTenantId, B.ou_bu_id, A.doc_date, A.product_id
        FROM tr_commision_gross_profit A
            INNER JOIN m_ou_structure B ON A.ou_id = B.ou_id
        WHERE A.tenant_id = pTenantId
            AND A.session_id = pSessionId
            AND A.year_month_date = vCurrentYearMonth
        GROUP BY B.ou_bu_id, A.doc_date, A.product_id;

        PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, vYearMonth, vParentOuId, vDatetime, pUserId);

        /*sebelum v329
        UPDATE tr_commision_gross_profit
        SET monthly_price_curr_code = COALESCE(A.curr_code, tr_commision_gross_profit.curr_code), monthly_price_amount = COALESCE(f_get_gross_price_from_nett_amount(A.amount, A.qty, A.flg_tax_amount, A.tax_percentage, vRoundingMode, f_get_digit_decimal_doc_curr(-99, A.curr_code)), 0)
        FROM pu_monthly_price_product A
        WHERE tr_commision_gross_profit.session_id = pSessionId
        AND tr_commision_gross_profit.product_id = A.product_id
        AND tr_commision_gross_profit.year_month_date = A.year_month_date
        AND tr_commision_gross_profit.ou_parent_id = A.ou_id;
        */

        ANALYZE tt_out_latest_purchasing_price_by_date;

        UPDATE tr_commision_gross_profit B
        --SET monthly_price_curr_code = A.curr_code, monthly_price_amount = ROUND(A.amount / A.qty)
        SET monthly_price_curr_code = COALESCE(A.gl_purch_curr_code, B.curr_code), monthly_price_amount = COALESCE(A.gl_purch_gross_price, 0)
        FROM tt_out_latest_purchasing_price_by_date A
        INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_bu_id
        WHERE A.session_id = B.session_id
        AND B.product_id = A.product_id
        AND B.tenant_id = A.tenant_id
        AND B.doc_date = A.doc_date
        AND B.year_month_date = vCurrentYearMonth
        AND C.ou_id = B.ou_id;

        SELECT TO_CHAR(TO_DATE(vCurrentYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vCurrentYearMonth;

        DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
        DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;

    END LOOP;

    ANALYZE tr_commision_gross_profit;

    /*
     * update nilai komisi jika menggunakan tipe komisi persentase
     */
    UPDATE tr_commision_gross_profit SET commision_amount = ROUND(nett_sell_price * commision_percentage / 100,0), commision_curr_code = curr_code
    WHERE commision_type = 'PCT'
    AND session_id = pSessionId;

    /*sebelum v329
     * update nilai harga beli terakhir menggunakan valuta penjualan

    UPDATE tr_commision_gross_profit SET monthly_price_amount = monthly_price_amount * f_commercial_rate(tenant_id, doc_date, monthly_price_curr_code, curr_code), monthly_price_curr_code = curr_code
    WHERE monthly_price_curr_code <> curr_code
    AND session_id = pSessionId AND monthly_price_curr_code <> '';
    */
    /*
     * pindahkan data tahun bulan yang diminta (sudah direkap per outlet)
     */

    ANALYZE tr_commision_gross_profit;

    INSERT INTO tr_detail_gross_profit (
        session_id, tenant_id, ou_id, product_id, curr_code,
        sales_amount, commision_amount, monthly_price_amount, gross_profit,
        qty
    )
    SELECT pSessionId, tenant_id, ou_id, product_id, curr_code,
           SUM(nett_amount_item) AS sales_amount, SUM(commision_amount * qty) AS commision_amount, SUM(monthly_price_amount * qty) AS monthly_price_amount, 0,
           SUM(qty)
    FROM tr_commision_gross_profit
    WHERE session_id = pSessionId
    GROUP BY tenant_id, ou_id, product_id, curr_code;

    /*
     * update data gross profit
     */
    ANALYZE tr_detail_gross_profit;

    UPDATE tr_detail_gross_profit SET gross_profit = sales_amount - monthly_price_amount
    WHERE session_id = pSessionId;

    /*
     * return header
     */
    Open pRefHeader FOR
    SELECT A.fullname AS full_name, B.ou_name AS ou_name, pDateFrom AS date_from, pDateTo AS date_to, vDatetime AS datetime
    FROM t_user A
    INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
    WHERE user_id = pUserId
    AND ou_id = pOuId;

    RETURN NEXT pRefHeader;

    INSERT INTO tt_input_data
    (session_id, id_data, data_group_1,
    data_group_2, data_group_3, data_group_4, data_group_5, data_measure)
    SELECT pSessionId, 'SALDO', A.curr_code,
        A.qty::text, B.product_id::text, A.sales_amount::text, A.gross_profit::text, A.gross_profit::numeric
    FROM tr_detail_gross_profit A
    INNER JOIN m_product B ON A.product_id = B.product_id
    WHERE A.session_id = pSessionId;

    PERFORM f_agregate_percentage(pSessionId, 'SALDO', ';', 3, 1, 2);

    IF TRIM(pProductCodeName) <> vEmptyValue THEN
        Open pRefDetail FOR
        SELECT A.data_group_1 AS curr_code,
        A.data_group_2::bigint AS qty,
        f_get_product_code(A.data_group_3::bigint) AS product_code,
        f_get_product_name(A.data_group_3::bigint) AS product_name,
        A.data_group_4::numeric AS nilai_penjualan,
        A.data_group_5::numeric AS margin,
        A.data_group_6::numeric AS qty,
        f_get_ctgr_product_code(B.ctgr_product_id) AS ctgr_product_code,
        f_get_ctgr_product_name(B.ctgr_product_id) AS ctgr_product_name,
        f_get_sub_ctgr_product_code(B.sub_ctgr_product_id) AS sub_ctgr_product_code,
        f_get_sub_ctgr_product_name(B.sub_ctgr_product_id) AS sub_ctgr_product_name,
        f_get_uom_name(B.base_uom_id) AS uom_name,
        round(A.percentage_measure, 2) AS percent
        FROM tt_output_data A
        INNER JOIN m_product B ON A.data_group_3::bigint = B.product_id
        WHERE A.session_id = pSessionId
        AND UPPER(A.data_group_2) LIKE '%' || UPPER(pProductCodeName) || '%' OR UPPER(A.data_group_3) LIKE '%' || UPPER(pProductCodeName) || '%'
        ORDER BY curr_code ASC, percent DESC, nilai_penjualan DESC, margin DESC, product_code ASC;
    ELSE
        Open pRefDetail FOR
        SELECT A.data_group_1 AS curr_code, A.data_group_2::bigint AS qty, f_get_product_code(A.data_group_3::bigint) AS product_code, f_get_product_name(A.data_group_3::bigint) AS product_name,
               A.data_group_4::numeric AS nilai_penjualan, A.data_group_5::numeric AS margin, round(A.percentage_measure, 2) AS percent,
               f_get_ctgr_product_code(B.ctgr_product_id) AS ctgr_product_code,
               f_get_ctgr_product_name(B.ctgr_product_id) AS ctgr_product_name,
               f_get_sub_ctgr_product_code(B.sub_ctgr_product_id) AS sub_ctgr_product_code,
               f_get_sub_ctgr_product_name(B.sub_ctgr_product_id) AS sub_ctgr_product_name,
               f_get_uom_name(B.base_uom_id) AS uom_name
        FROM tt_output_data A
        INNER JOIN m_product B ON A.data_group_3::bigint = B.product_id
        WHERE A.session_id = pSessionId
        ORDER BY curr_code ASC, percent DESC, nilai_penjualan DESC, margin DESC, product_code ASC;
    END IF;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_commision_gross_profit WHERE session_id = pSessionId;
    DELETE FROM tr_detail_gross_profit WHERE session_id = pSessionId;
    DELETE FROM tt_input_data WHERE session_id = pSessionId;
    DELETE FROM tt_output_data WHERE session_id = pSessionId;
    DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
    DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;


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