r_outlet_top_selling_products_xlsx_no_group_by_periode_20170

/**
 * Modified by Adrian, Jul 28, 2017,
 * mengubah harga beli untuk menghitung profit menjadi dari gl_purch_gross_price
 * dan diambil per year-month
 */

CREATE OR REPLACE FUNCTION r_outlet_top_selling_products_xlsx_no_group_by_periode(character varying, bigint, bigint, character varying, character varying, bigint, 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;
    pOuId                   ALIAS FOR $3;
    pDateFrom               ALIAS FOR $4;
    pDateTo                 ALIAS FOR $5;
    pGroupProductOuId       ALIAS FOR $6;
    pLimit                  ALIAS FOR $7;
    pUserId                 ALIAS FOR $8;
    pWarehouseId            ALIAS FOR $9;
    pProductCodeName        ALIAS FOR $10;

    vEmptyValue             character varying(1);
    vFlgBu                  character varying(1);
    vFlgSubBu               character varying(1);
    vFlgBranch              character varying(1);
    vProductStatus          character varying(10);
    vDatetime               character varying(14);
    vGroupProductOuCode     character varying(50) := 'All';
    vGroupProductOuName     character varying(100) := 'All';
    vOuBuId                 bigint;
    vAllId                  bigint;
    vEmptyId                bigint;

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

BEGIN

    vEmptyValue := ' ';
    vProductStatus := 'GOOD';
    vAllId := -99;
    vEmptyId := -99;

    DELETE FROM tr_top_selling_products WHERE session_id = pSessionId;
    DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId;
    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;

    SELECT A.flg_bu, A.flg_sub_bu, flg_branch INTO vFlgBu, vFlgSubBu, vFlgBranch
    FROM t_ou_type A
    INNER JOIN t_ou B ON A.ou_type_id = B.ou_type_id
    WHERE B.ou_id = pOuId;

    SELECT ou_bu_id INTO vOuBuId
    FROM m_ou_structure
    WHERE ou_id = pOuId;

    /*
     * ambil data yang tidak pakai promo
     */
    INSERT INTO tr_pos_by_periode
    (session_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date,
    curr_code, line_no, ctgr_product_id, sub_ctgr_product_id, product_id,
    product_code, product_name, uom_id, uom_code, promo_code,
    qty, flg_tax_amount, tax_amount, nett_sell_price, nett_amount_item, gross_sell_price, discount_amount,
    group_product_ou_id, group_product_ou_code, group_product_ou_name,
    commision_ou_id, commision_type, commision_percentage, commision_curr_code, commision_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        B.curr_code, B.line_no, C.ctgr_product_id, C.sub_ctgr_product_id, COALESCE(G.core_product_id, B.product_id),
        C.product_code, C.product_Name, B.base_uom_id, D.uom_code, B.promo_code,
        B.qty, B.flg_tax_amount, B.tax_amount, B.nett_sell_price, B.nett_amount_item, B.gross_sell_price, B.discount_amount,
        B.group_product_ou_id, E.group_product_ou_code, E.group_product_ou_name,
        F.commision_ou_id, F.commision_type, F.commision_percentage, F.curr_code, F.commision_amount
    FROM i_trx_pos A
    INNER JOIN i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND A.tenant_id = B.tenant_id AND A.process_no = B.process_no
    INNER JOIN m_product C ON B.product_id = C.product_id
    INNER JOIN m_uom D ON B.base_uom_id = D.uom_id
    INNER JOIN m_group_product_ou E ON B.group_product_ou_id = E.group_product_ou_id
    INNER JOIN m_commision_ou F ON B.tenant_id = F.tenant_id AND B.group_product_ou_id = F.group_product_ou_id
    LEFT OUTER JOIN m_ext_product G ON B.product_id = G.product_id
    WHERE A.tenant_id = pTenantId AND
        --A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.doc_date BETWEEN F.date_from AND F.date_to AND
        F.flg_promo = 'N';

    UPDATE tr_pos_by_periode SET product_code = A.product_code, product_name = A.product_name
    FROM m_product A
    WHERE A.product_id = tr_pos_by_periode.product_id
    AND tr_pos_by_periode.session_id = pSessionId;

    UPDATE tr_pos_by_periode SET commision_amount = ROUND(nett_sell_price * commision_percentage / 100,0), commision_curr_code = curr_code
    WHERE commision_type = 'PCT' AND
        session_id = pSessionId;

    /*
     * transaksi POS yang di void, tidak akan dimunculkan
     * NK, 22 Mei 2013
     */
    DELETE FROM tr_pos_by_periode
    WHERE session_id = pSessionId AND
        EXISTS (SELECT 1 FROM i_trx_log_voided_pos A
                    WHERE tr_pos_by_periode.tenant_id = A.tenant_id AND
                    --tr_pos_by_periode.doc_type_id = A.doc_type_id AND
                    tr_pos_by_periode.doc_no = A.doc_no AND
                    tr_pos_by_periode.doc_date = A.doc_date AND
                    tr_pos_by_periode.ou_id = A.ou_id);

    IF pOuId <> vAllId THEN

        -- OU BU
        IF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'N' THEN

            DELETE FROM tr_pos_by_periode A
            WHERE A.session_id = pSessionId
                AND NOT EXISTS (
                    SELECT 1 FROM m_ou_structure B
                    WHERE A.ou_id = B.ou_id
                        AND B.ou_bu_id = pOuId
                );

        ELSEIF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'Y' THEN -- OU Branch

            DELETE FROM tr_pos_by_periode A
            WHERE A.session_id = pSessionId
                AND NOT EXISTS (
                    SELECT 1 FROM m_ou_structure B
                    WHERE A.ou_id = B.ou_id
                        AND B.ou_branch_id = pOuId
                );

        ELSE

            DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId AND ou_id <> pOuId;

        END IF;

    END IF;

    IF pGroupProductOuId <> vAllId THEN

        SELECT group_product_ou_code, group_product_ou_name INTO vGroupProductOuCode, vGroupProductOuName
        FROM m_group_product_ou
        WHERE group_product_ou_id = pGroupProductOuId;

        DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId AND group_product_ou_id <> pGroupProductOuId;

    END IF;


    SELECT SUBSTRING(MIN(doc_date), 1, 6), SUBSTRING(MAX(doc_date), 1, 6) FROM tr_pos_by_periode INTO vCurrentYearMonth, vEndYearMonth;

    --Modified by Adrian, Aug 1, 2017
    --harga beli untuk menghitung profit 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_pos_by_periode 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 SUBSTRING(A.doc_date, 1, 6) = vCurrentYearMonth
        GROUP BY B.ou_bu_id, A.doc_date, A.product_id;

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

        --PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, SUBSTRING(vDatetime, 1, 6), vOuBuId, vDatetime, pUserId);
        IF pOuId <> vAllId THEN
            PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, SUBSTRING(vDatetime, 1, 6), vOuBuId, vDatetime, pUserId);
        ELSE
            PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, SUBSTRING(vDatetime, 1, 6), B.ou_bu_id, vDatetime, pUserId)
            FROM t_ou A JOIN m_ou_structure B ON A.ou_id=B.ou_id where A.tenant_id=pTenantId;
        END IF;

        INSERT INTO tr_top_selling_products(
                session_id, tenant_id, ou_id,
                curr_code, group_product_ou_id, group_product_ou_code, group_product_ou_name,
                product_id, product_code, product_name,
                qty, uom_id, uom_code, uom_name,
                nett_amount, remaining_stock, profit
        )
        SELECT  pSessionId, pTenantId, pOuId,
                A.curr_code, -99, ' ', ' ',
                A.product_id, A.product_code, A.product_name,
                SUM(A.qty) AS qty, A.uom_id, A.uom_code, C.uom_name,
                SUM(A.nett_amount_item) AS nett_amount, 0 AS remaining_stock, SUM(A.nett_amount_item - (A.qty * E.gl_purch_gross_price)) AS profit
        FROM tr_pos_by_periode A
        INNER JOIN m_uom C ON A.uom_id = C.uom_id
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        INNER JOIN tt_out_latest_purchasing_price_by_date E ON E.session_id = A.session_id AND A.product_id = E.product_id AND A.doc_date = E.doc_date AND A.tenant_id = E.tenant_id AND D.ou_bu_id = E.ou_bu_id
        --LEFT OUTER JOIN in_product_balance_stock B ON A.tenant_id = B.tenant_id AND A.product_id = B.product_id AND B.warehouse_id = pWarehouseId AND B.product_status = vProductStatus
        WHERE A.session_id = pSessionId
        AND UPPER(A.product_code) LIKE '%' || UPPER(pProductCodeName) || '%' OR UPPER(A.product_name) LIKE '%' || UPPER(pProductCodeName) || '%'
        AND SUBSTRING(A.doc_date, 1, 6) = vCurrentYearMonth
        GROUP BY A.curr_code, A.product_id, A.product_code, A.product_name, A.uom_id, A.uom_code, C.uom_name
        ORDER BY qty DESC, nett_amount DESC;

        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;


    IF pOuId <> vAllId THEN

        IF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'N' THEN

            UPDATE tr_top_selling_products
            SET remaining_stock = A.qty
            FROM (
                SELECT B.product_id, SUM(B.qty) AS qty
                FROM in_product_balance_stock B
                INNER JOIN m_warehouse C ON B.warehouse_id = C.warehouse_id
                INNER JOIN m_warehouse_ou D ON C.warehouse_id = D.warehouse_id
                INNER JOIN t_ou E ON D.ou_id = E.ou_id
                INNER JOIN t_ou_type F ON E.ou_type_id = F.ou_type_id
                INNER JOIN m_ou_structure G ON G.ou_id = E.ou_id
                WHERE B.tenant_id = pTenantId
                    AND G.ou_bu_id = pOuId
                GROUP BY B.product_id
            ) A
            WHERE tr_top_selling_products.session_id = pSessionId
            AND tr_top_selling_products.product_id = A.product_id;

        ELSEIF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'Y' THEN

            UPDATE tr_top_selling_products
            SET remaining_stock = A.qty
            FROM (
                SELECT B.product_id, SUM(B.qty) AS qty
                FROM in_product_balance_stock B
                INNER JOIN m_warehouse C ON B.warehouse_id = C.warehouse_id
                INNER JOIN m_warehouse_ou D ON C.warehouse_id = D.warehouse_id
                INNER JOIN t_ou E ON D.ou_id = E.ou_id
                INNER JOIN t_ou_type F ON E.ou_type_id = F.ou_type_id
                INNER JOIN m_ou_structure G ON G.ou_id = E.ou_id
                WHERE B.tenant_id = pTenantId
                    AND G.ou_branch_id = pOuId
                GROUP BY B.product_id
            ) A
            WHERE tr_top_selling_products.session_id = pSessionId
            AND tr_top_selling_products.product_id = A.product_id;

        ELSE

            UPDATE tr_top_selling_products
            SET remaining_stock = A.qty
            FROM (
                SELECT B.product_id, SUM(B.qty) AS qty
                FROM in_product_balance_stock B
                WHERE B.tenant_id = pTenantId
                AND B.warehouse_id = pWarehouseId
                GROUP BY B.product_id
            ) A
            WHERE tr_top_selling_products.session_id = pSessionId
            AND tr_top_selling_products.product_id = A.product_id;

        END IF;
    ELSE
        UPDATE tr_top_selling_products
        SET remaining_stock = A.qty
        FROM (
            SELECT B.product_id, SUM(B.qty) AS qty
            FROM in_product_balance_stock B
            INNER JOIN m_warehouse C ON B.warehouse_id = C.warehouse_id
            INNER JOIN m_warehouse_ou D ON C.warehouse_id = D.warehouse_id
            INNER JOIN t_ou E ON D.ou_id = E.ou_id
            INNER JOIN t_ou_type F ON E.ou_type_id = F.ou_type_id
            WHERE B.tenant_id = pTenantId
            AND F.flg_bu = 'Y' AND F.flg_sub_bu = 'Y'
            GROUP BY B.product_id
        ) A
        WHERE tr_top_selling_products.session_id = pSessionId
        AND tr_top_selling_products.product_id = A.product_id;
    END IF;

    Open pRefHeader FOR
    SELECT fullName AS full_name, pDateFrom AS date_from, pDateTo AS date_to
    FROM t_user
    WHERE user_id = pUserId;

    RETURN NEXT pRefHeader;

    IF pLimit <> vAllId THEN
        Open pRefDetail FOR
        SELECT  A.curr_code AS curr_code, vGroupProductOuCode AS dept_code,
                vGroupProductOuName AS dept_name,
                A.product_code AS product_code, A.product_name AS product_name,
                SUM(A.qty) AS qty, A.uom_name AS uom_code,
                SUM(A.nett_amount) AS nett_amount, SUM(A.remaining_stock) AS remaining_stock,
                SUM(A.profit) AS profit
        FROM tr_top_selling_products A
        WHERE A.session_id = pSessionId
        GROUP BY A.curr_code,
                A.product_code, A.product_name, A.uom_name
        ORDER BY qty DESC, nett_amount DESC
        LIMIT pLimit;
    ELSE
        Open pRefDetail FOR
        SELECT  A.curr_code AS curr_code, vGroupProductOuCode AS dept_code,
                vGroupProductOuName AS dept_name,
                A.product_code AS product_code, A.product_name AS product_name,
                SUM(A.qty) AS qty, A.uom_name AS uom_code,
                SUM(A.nett_amount) AS nett_amount, SUM(A.remaining_stock) AS remaining_stock,
                SUM(A.profit) AS profit
        FROM tr_top_selling_products A
        WHERE A.session_id = pSessionId
        GROUP BY A.curr_code,
                A.product_code, A.product_name, A.uom_name
        ORDER BY qty DESC, nett_amount DESC;
    END IF;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId;
    DELETE FROM tr_top_selling_products WHERE session_id = pSessionId;
    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;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /