r_analysis_sales_nett_profit_with_monthly_average_ctgr_produ

/**
 * Adrian, Aug 18, 2017
 * Report Analysis Sales Gross Profit with Monthly Average By Category Product
 * Dibuat berdasarkan Report Analysis Sales Gross Profit,
 * tetapi harga beli diambil dari in_summary_monthly_cogs,
 * harga jual yang diambil adalah harga nett,
 * akhir rekap adalah bulan yang dipilih,
 * semua transaksi diambil dari sl_so_balance_invoice,
 * hanya bisa memilih periode tanpa tanggal awal dan akhir,
 * dan di-group berdasarkan category product
 */

/**
 * Adrian, Sep 12, 2017
 * nett_amount_item untuk partner Magento dikurangi pajak
 */

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

    vEmptyValue             character varying(1);
    vAwalTahun              character varying(6);
    vAkhirRekap             character varying(6);
    vAllId                  bigint;
    vEmptyId                bigint;
    vRoundingMode           character varying(5);
    vValutaBuku             character varying(5);

    vFilterCtgrProduct      character varying;

    vTotalCurrentProfitPercentage   numeric;
    vTotalBeforeProfitPercentage    numeric;

    vPartnerIdMagento       bigint;
    vFlagYes                character varying(1);
    vZero                   integer;
    vRoundingModeUp         character varying(2);

BEGIN

    vEmptyValue := '';
    vAllId := -99;
    vEmptyId := -99;
    vAwalTahun := SUBSTRING(pYearMonthDate, 1, 4) || '01';
    vValutaBuku := 'IDR';
    vFilterCtgrProduct := ' ';

    vPartnerIdMagento := 33586;
    vFlagYes := 'Y';
    vZero := 0;
    vRoundingModeUp := 'RU';

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

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vValutaBuku;

    vAkhirRekap := pYearMonthDate;

    IF pCtgrProductId <> vAllId THEN
        vFilterCtgrProduct = ' AND F.ctgr_product_id = ' || pCtgrProductId || ' ';
    END IF;

    DELETE FROM tr_sales_gross_profit_by_ctgr_product WHERE session_id = pSessionId;
    DELETE FROM tr_current_sales_gross_profit_by_ctgr_product WHERE session_id = pSessionId;
    DELETE FROM tr_rekap_sales_gross_profit_by_ctgr_product WHERE session_id = pSessionId;
    DELETE FROM tr_sales_gross_profit_by_ctgr_product_for_output WHERE session_id = pSessionId;

    /*
     * ambil semua so balance invoice di main business unit
     * jika transaksi itu ada item assembly ambil berdasarkan core product id nya
     * ambil juga harga beli terakhir dari item itu sesuai dengan tahun bulan
     */

    --DELIVERY ORDER dan DO RECEIPT
    EXECUTE '
        INSERT INTO tr_sales_gross_profit_by_ctgr_product(
               session_id, tenant_id, ou_id, year_month_date, partner_id, partner_code, partner_name,
               ctgr_product_id, ctgr_product_code, ctgr_product_name,
               doc_type_id, doc_no, doc_date,
               product_id, curr_code, qty, nett_sell_price,
               nett_amount_item,
               monthly_price_curr_code, monthly_price_amount
        )
        SELECT $1, A.tenant_id, E.ou_bu_id, SUBSTRING(A.ref_doc_date, 1, 6), $6, $7, $7,
               F.ctgr_product_id, f_get_ctgr_product_code(F.ctgr_product_id), f_get_ctgr_product_name(F.ctgr_product_id),
               A.ref_doc_type_id, A.ref_doc_no, A.ref_doc_date,
               B.product_id, A.curr_code, A.qty_dlv_so, (A.price_so),
               SUM(
                    CASE WHEN A.partner_id = $8 THEN
                        COALESCE (
                            f_get_amount_before_tax(A.item_amount, $9, H.tax_percentage, $11, $10),
                            A.item_amount
                        )
                    ELSE
                        A.item_amount
                    END
                ),
               A.curr_code, 0
        FROM sl_so_balance_invoice A
        INNER JOIN sl_do_item B ON A.ref_item_id = B.do_item_id
        INNER JOIN sl_do C ON A.ref_doc_type_id = C.doc_type_id AND C.do_id = A.ref_id AND C.do_id = B.do_id
        INNER JOIN sl_so_item D ON B.ref_id = D.so_item_id
        INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
        INNER JOIN m_product F ON B.product_id = F.product_id
        LEFT JOIN sl_invoice G ON G.invoice_id = A.invoice_id AND A.partner_id = $8
        LEFT JOIN sl_invoice_tax H ON H.invoice_id = G.invoice_id
        WHERE A.tenant_id = $2
        AND SUBSTRING(A.ref_doc_date, 1, 6) BETWEEN $3 AND $4
        AND E.ou_bu_id = $5 '
        || vFilterCtgrProduct ||
        ' GROUP BY A.tenant_id, E.ou_bu_id, A.ref_doc_date, F.ctgr_product_id, A.ref_doc_type_id, A.ref_doc_no, B.product_id,
        A.curr_code, A.qty_dlv_so, A.price_so, D.tax_price '
    USING pSessionId, pTenantId, vAwalTahun, pYearMonthDate, pOuId, vEmptyId, vEmptyValue, vPartnerIdMagento, vFlagYes, vRoundingModeUp, vZero;

    ANALYZE tr_sales_gross_profit_by_ctgr_product;

    --RETURN NOTE
    EXECUTE '
        INSERT INTO tr_sales_gross_profit_by_ctgr_product(
               session_id, tenant_id, ou_id, year_month_date, partner_id, partner_code, partner_name,
               ctgr_product_id, ctgr_product_code, ctgr_product_name,
               doc_type_id, doc_no, doc_date,
               product_id, curr_code, qty, nett_sell_price,
               nett_amount_item,
               monthly_price_curr_code, monthly_price_amount
        )
        SELECT $1, A.tenant_id, E.ou_bu_id, SUBSTRING(A.ref_doc_date, 1, 6), $6, $7, $7,
               F.ctgr_product_id, f_get_ctgr_product_code(F.ctgr_product_id), f_get_ctgr_product_name(F.ctgr_product_id),
               A.ref_doc_type_id, A.ref_doc_no, A.ref_doc_date,
               B.product_id, A.curr_code, A.qty_dlv_so * -1, (A.price_so),
               SUM(A.item_amount) * -1,
               A.curr_code, 0
        FROM sl_so_balance_invoice A
        INNER JOIN in_inventory_item B ON A.ref_item_id = B.ref_item_id
        INNER JOIN in_inventory C ON A.ref_doc_type_id = C.doc_type_id AND C.inventory_id = A.ref_id AND C.inventory_id = B.inventory_id
        INNER JOIN in_balance_do_item G ON B.ref_item_id = G.do_item_id
        INNER JOIN sl_so_item D ON G.so_item_id = D.so_item_id
        INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
        INNER JOIN m_product F ON B.product_id = F.product_id
        WHERE A.tenant_id = $2
        AND SUBSTRING(A.ref_doc_date, 1, 6) BETWEEN $3 AND $4
        AND E.ou_bu_id = $5 '
        || vFilterCtgrProduct ||
        ' GROUP BY A.tenant_id, E.ou_bu_id, A.ref_doc_date, F.ctgr_product_id, A.ref_doc_type_id, A.ref_doc_no, B.product_id,
        A.curr_code, A.qty_dlv_so, A.price_so, D.tax_price '
    USING pSessionId, pTenantId, vAwalTahun, pYearMonthDate, pOuId, vEmptyId, vEmptyValue;

    ANALYZE tr_sales_gross_profit_by_ctgr_product;

    --Update harga beli dari in_summary_monthly_cogs
    UPDATE tr_sales_gross_profit_by_ctgr_product B
    SET monthly_price_curr_code = COALESCE(A.curr_code, B.curr_code),
        monthly_price_amount = (CASE WHEN COALESCE(qty_total, 0) = 0 OR COALESCE(amount_total, 0) = 0 THEN
            0
        ELSE
            COALESCE(A.amount_total/A.qty_total, 0)
        END)
    FROM in_summary_monthly_cogs A
    INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_id
    WHERE B.tenant_id = A.tenant_id
        AND B.product_id = A.product_id
        AND B.year_month_date = A.date_year_month
        AND C.ou_id = B.ou_id;

    ANALYZE tr_sales_gross_profit_by_ctgr_product;

    /*
     * update nilai harga beli terakhir menggunakan valuta penjualan (gunakan kurs komersial)

    UPDATE tr_sales_gross_profit SET monthly_price_amount = monthly_price_amount * f_commercial_rate(tenant_id, doc_date, monthly_price_curr_code, vValutaBuku), monthly_price_curr_code = vValutaBuku
    WHERE monthly_price_curr_code <> vValutaBuku
    AND session_id = pSessionId AND monthly_price_curr_code <> '';   */

    UPDATE tr_sales_gross_profit_by_ctgr_product SET nett_amount_item = nett_amount_item * f_commercial_rate(tenant_id, doc_date, curr_code, vValutaBuku), curr_code = vValutaBuku
    WHERE curr_code <> vValutaBuku
    AND session_id = pSessionId AND curr_code <> '';

    /*
     * pindahkan data tahun bulan yang diminta (sudah direkap per partner)
     */
    INSERT INTO tr_current_sales_gross_profit_by_ctgr_product (
        session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
        ctgr_product_id, ctgr_product_code, ctgr_product_name,
        curr_code, current_sales_amount, current_monthly_price_amount, current_gross_profit
    )
    SELECT pSessionId, tenant_id, ou_id, vEmptyId, vEmptyValue, vEmptyValue,
        ctgr_product_id, ctgr_product_code, ctgr_product_name,
        curr_code, SUM(nett_amount_item) AS current_sales_amount, SUM(monthly_price_amount * qty) AS current_monthly_price_amount, 0
    FROM tr_sales_gross_profit_by_ctgr_product
    WHERE session_id = pSessionId
    AND year_month_date = pYearMonthDate
    --AND doc_date BETWEEN pTglAwal AND pTglAkhir
    GROUP BY tenant_id, ou_id, ctgr_product_id, ctgr_product_code, ctgr_product_name, curr_code;

    ANALYZE tr_current_sales_gross_profit_by_ctgr_product;

    /*
     * pindahkan data sebelum tahun bulan yang diminta (sudah direkap per partner)
     */
    INSERT INTO tr_rekap_sales_gross_profit_by_ctgr_product (
        session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
        ctgr_product_id, ctgr_product_code, ctgr_product_name,
        curr_code, rekap_sales_amount, rekap_monthly_price_amount, rekap_gross_profit
    )
    SELECT pSessionId, tenant_id, ou_id, vEmptyId, vEmptyValue, vEmptyValue,
        ctgr_product_id, ctgr_product_code, ctgr_product_name,
        curr_code, SUM(nett_amount_item) AS rekap_sales_amount, SUM(monthly_price_amount * qty) AS rekap_monthly_price_amount, 0
    FROM tr_sales_gross_profit_by_ctgr_product
    WHERE session_id = pSessionId
    --AND year_month_date <> pYearMonthDate
    GROUP BY tenant_id, ou_id, ctgr_product_id, ctgr_product_code, ctgr_product_name, curr_code;

    ANALYZE tr_current_sales_gross_profit_by_ctgr_product;

    /*
     * update data gross profit untuk bulan yang diminta
     */
    UPDATE tr_current_sales_gross_profit_by_ctgr_product SET current_gross_profit = current_sales_amount - current_monthly_price_amount
    WHERE session_id = pSessionId;

    ANALYZE tr_rekap_sales_gross_profit_by_ctgr_product;

    /*
     * update data gross profit untuk sebelum bulan yang diminta
     */
    UPDATE tr_rekap_sales_gross_profit_by_ctgr_product SET rekap_gross_profit = rekap_sales_amount - rekap_monthly_price_amount
    WHERE session_id = pSessionId;

    ANALYZE tr_current_sales_gross_profit_by_ctgr_product;

    INSERT INTO tr_sales_gross_profit_by_ctgr_product_for_output
        (session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
         ctgr_product_id, ctgr_product_code, ctgr_product_name,
         curr_code,
         current_sales_amount, current_gross_profit,
         rekap_sales_amount, rekap_gross_profit)
    SELECT pSessionId, A.tenant_id, ou_id, vEmptyId, vEmptyValue, vEmptyValue,
           A.ctgr_product_id, A.ctgr_product_code, A.ctgr_product_name,
           A.curr_code,
           A.current_sales_amount, A.current_gross_profit,
           0, 0
    FROM tr_current_sales_gross_profit_by_ctgr_product A
    WHERE A.session_id = pSessionId;

    ANALYZE tr_rekap_sales_gross_profit_by_ctgr_product;

    UPDATE tr_sales_gross_profit_by_ctgr_product_for_output
    SET rekap_sales_amount = A.rekap_sales_amount, rekap_gross_profit = A.rekap_gross_profit
    FROM tr_rekap_sales_gross_profit_by_ctgr_product A
    WHERE A.session_id = pSessionId
        AND tr_sales_gross_profit_by_ctgr_product_for_output.partner_id = A.partner_id
        AND tr_sales_gross_profit_by_ctgr_product_for_output.ctgr_product_id = A.ctgr_product_id;

    ANALYZE tr_rekap_sales_gross_profit_by_ctgr_product;

    INSERT INTO tr_sales_gross_profit_by_ctgr_product_for_output
        (session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
         ctgr_product_id, ctgr_product_code, ctgr_product_name,
         curr_code,
         current_sales_amount, current_gross_profit,
         rekap_sales_amount, rekap_gross_profit)
    SELECT pSessionId, A.tenant_id, A.ou_id, vEmptyId, vEmptyValue, vEmptyValue,
           A.ctgr_product_id, A.ctgr_product_code, A.ctgr_product_name,
           A.curr_code,
           0, 0,
           A.rekap_sales_amount, A.rekap_gross_profit
    FROM tr_rekap_sales_gross_profit_by_ctgr_product A
    WHERE A.session_id = pSessionId
    AND ROW(A.partner_id, A.ctgr_product_id) NOT IN ( SELECT B.partner_id, B.ctgr_product_id FROM tr_sales_gross_profit_by_ctgr_product_for_output B WHERE B.session_id = pSessionId );

    ANALYZE tr_sales_gross_profit_by_ctgr_product_for_output;

    SELECT CASE
                WHEN SUM(A.current_sales_amount) = 0 THEN 0
                ELSE COALESCE (SUM(A.current_gross_profit)/SUM(A.current_sales_amount), 0)
           END,
           CASE
                WHEN SUM(A.rekap_sales_amount) = 0 THEN 0
                ELSE COALESCE (SUM(A.rekap_gross_profit)/SUM(A.rekap_sales_amount), 0)
           END
    FROM tr_sales_gross_profit_by_ctgr_product_for_output A
    INTO vTotalCurrentProfitPercentage, vTotalBeforeProfitPercentage;

    /*
     * return header
     */
    Open pRefHeader FOR
    SELECT A.fullname AS username, B.ou_name AS ou_name, pYearMonthDate AS current_month_year, vAwalTahun AS start_month_year, vAkhirRekap AS end_month_year,
    vValutaBuku AS valuta_buku, pDatetime AS datetime,
    vTotalCurrentProfitPercentage AS total_current_profit_percentage, vTotalBeforeProfitPercentage AS total_before_profit_percentage
    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;

    Open pRefDetail FOR
    SELECT A.ctgr_product_id AS ctgr_product_id, A.ctgr_product_code AS ctgr_product_code, A.ctgr_product_name AS ctgr_product_name,
           A.curr_code AS curr_code,
           A.current_sales_amount AS current_sales, A.current_gross_profit AS current_gross_profit,
           CASE
                WHEN A.current_sales_amount = 0 THEN 0
                ELSE COALESCE (A.current_gross_profit/A.current_sales_amount, 0)
           END AS current_profit_percentage,
           A.rekap_sales_amount AS before_sales, A.rekap_gross_profit AS before_gross_profit,
           CASE
                WHEN A.rekap_sales_amount = 0 THEN 0
                ELSE COALESCE (A.rekap_gross_profit/A.rekap_sales_amount, 0)
           END AS before_profit_percentage
    FROM tr_sales_gross_profit_by_ctgr_product_for_output A
    WHERE A.session_id = pSessionId
    ORDER BY curr_code, ctgr_product_name;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_sales_gross_profit_by_ctgr_product WHERE session_id = pSessionId;
    DELETE FROM tr_current_sales_gross_profit_by_ctgr_product WHERE session_id = pSessionId;
    DELETE FROM tr_rekap_sales_gross_profit_by_ctgr_product WHERE session_id = pSessionId;
    DELETE FROM tr_sales_gross_profit_by_ctgr_product_for_output WHERE session_id = pSessionId;

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