r_inventory_stock_summary

-- Function: r_inventory_stock_summary(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying)

DROP FUNCTION IF EXISTS r_inventory_stock_summary(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying);

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


    vEmptyId            bigint := -99;
    vEmptyString        character varying := '';
    vReleaseDoc         character varying := 'R';
    vStock              character varying := 'Y';
    vVoid               character varying := 'V';
    vCancel             character varying := 'C';
    vFinal              character varying := 'F';
    vFilterPartner      text := '';
    vRgConsignment      bigint := 113;
    vRg                 bigint := 111;
    vDo                 bigint := 311;
    vDoReceipt          bigint := 526;
    vReturnNote         bigint := 502;
    vClaimNote          bigint := 511;
    vClaimConsignment   bigint := 561;
    vAdjStockQty        bigint := 521;
    vAdjStockAmount     bigint := 522;
    vPosShop            bigint := 401;
    vPosShopInShop      bigint := 403;
    vVoidPosShop        bigint := 405;
    vVoidPosShopInShop  bigint := 406;
    vYes                character varying := 'Y';
BEGIN


    IF (pRoleId = -2) THEN

        SELECT partner_id INTO pPartnerId
        FROM m_partner_user WHERE user_id = pUserId;

        vFilterPartner := ' AND A.partner_id =  ' || pPartnerId;
    ELSE
       IF (pPartnerId <> vEmptyId) THEN
        vFilterPartner := ' AND A.partner_id = ' || pPartnerId;
       END IF;
    END IF;

    -- ambil semua produk yang sudah pernah ada di po
    EXECUTE 'INSERT INTO tt_inventory_stock_summary_consignment(
            session_id, product_id, supplier_product_code, product_code,
            product_name, qty_commitment, qty_receive, qty_return, qty_cancel,
            qty_add, qty_sell, qty_do_receipt, supplier_code, supplier_name, color, size,
            qty_nett_sell, qty_adjustment, qty_end_balance, style_product,
            qty_claim, qty_os_receive, qty_end_balance_supplier)
    SELECT $1, B.product_id, C.supplier_product_code, f_get_product_code(B.product_id),
            f_get_product_name(B.product_id), SUM(E.qty_int_po), 0, 0, SUM(E.qty_int_cancel),
            SUM(E.qty_int_add), 0, 0, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id), D.color, D.size,
            0, 0, 0, D.style_product,
            0, 0, 0
    FROM pu_po A
        INNER JOIN pu_po_item B ON A.po_id = B.po_id
        INNER JOIN m_product_consignment_supp_info C ON B.product_id = C.product_id AND A.partner_id = C.supplier_id
        INNER JOIN m_product_custom D ON C.product_id = D.product_id
        INNER JOIN pu_po_balance_item_consignment E ON E.po_item_id = B.po_item_id
    WHERE A.tenant_id = $2
        AND A.ou_id = $3
        AND D.flg_buy_konsinyasi = $4 '|| vFilterPartner ||
        ' GROUP BY B.product_id, C.supplier_product_code, A.partner_id, D.color, D.size, D.style_product' USING pSessionId, pTenantId, pOuId, vYes;


    -- ambil qty RG Consignment
    WITH data_qty_receive_cons AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id = vRgConsignment
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_receive = qty_receive + A.qty
    FROM data_qty_receive_cons A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;

    -- ambil Adj Stock
    WITH data_qty_adjustment AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id IN(vAdjStockQty, vAdjStockAmount)
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_adjustment = qty_adjustment + A.qty
    FROM data_qty_adjustment A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;

    -- ambil qty DO dan POS
    WITH data_qty_sell AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty * -1) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id IN(vDo, vPosShop, vPosShopInShop)
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_sell = qty_sell + A.qty
    FROM data_qty_sell A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;


    -- ambil qty posted
    WITH data_qty_posted AS (
        SELECT C.session_id AS session_id, A.qty_posted AS qty_posted, C.product_id
        FROM pu_po_balance_item_consignment_sold_manual A
        INNER JOIN sl_do_item B ON A.do_item_id = B.do_item_id
        INNER JOIN tt_inventory_stock_summary_consignment C ON B.product_id = C.product_id
        WHERE A.supplier_id = pPartnerId
    )

    UPDATE tt_inventory_stock_summary_consignment SET qty_sell = qty_sell + A.qty_posted
    FROM data_qty_posted A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;



    -- ambil qty Void POS
    WITH data_qty_void AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty * -1) AS qty
        FROM in_log_product_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id IN(vVoidPosShop, vVoidPosShopInShop)
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_sell = qty_sell + A.qty
    FROM data_qty_void A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;

    -- ambil qty DO Receipt
    WITH data_qty_do_receipt AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id = vDoReceipt
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_do_receipt = qty_do_receipt + A.qty
    FROM data_qty_do_receipt A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;

    -- ambil qty Return Note
    WITH data_qty_return AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id = vReturnNote
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_return = qty_return + A.qty
    FROM data_qty_return A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;


    -- ambil qty Claim Consignment
    WITH data_qty_claim AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty * -1) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id = vClaimConsignment
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_claim = qty_claim + A.qty
    FROM data_qty_claim A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;

    -- ambil qty Claim Note
    WITH data_qty_claim_note AS (
        SELECT B.session_id AS session_id, B.product_id, SUM(A.qty * -1) AS qty
        FROM in_log_product_consignment_balance_stock A
        INNER JOIN tt_inventory_stock_summary_consignment B ON A.product_id = B.product_id
        WHERE A.doc_type_id = vClaimNote
            AND B.session_id = pSessionId
        GROUP BY B.product_id, B.session_id

    )
    UPDATE tt_inventory_stock_summary_consignment SET qty_claim = qty_claim + A.qty
    FROM data_qty_claim_note A
    WHERE A.product_id = tt_inventory_stock_summary_consignment.product_id
        AND tt_inventory_stock_summary_consignment.session_id = A.session_id;

    -- update qty_nett_sell
    UPDATE tt_inventory_stock_summary_consignment SET qty_nett_sell = qty_sell - qty_return - qty_do_receipt
        WHERE tt_inventory_stock_summary_consignment.session_id = pSessionId;

    -- update qty_os_receive
    UPDATE tt_inventory_stock_summary_consignment SET qty_os_receive = qty_commitment + qty_add - qty_cancel - qty_receive
        WHERE tt_inventory_stock_summary_consignment.session_id = pSessionId;

    -- update qty_end_balance
    UPDATE tt_inventory_stock_summary_consignment SET qty_end_balance = f_get_product_balance_stock(pTenantId, tt_inventory_stock_summary_consignment.product_id, pOuId)
        WHERE tt_inventory_stock_summary_consignment.session_id = pSessionId;

    -- update qty_end_balance_supplier
    WITH data_end_balance_supp AS
        (
            SELECT pSessionId AS session_id, product_id, SUM(qty_receive - qty_claim - qty_nett_sell) AS qty_end_balance_supplier
            FROM tt_inventory_stock_summary_consignment
            GROUP BY product_id

        )
    UPDATE tt_inventory_stock_summary_consignment Z SET qty_end_balance_supplier = X.qty_end_balance_supplier
        FROM data_end_balance_supp X
        WHERE Z.session_id = X.session_id AND Z.product_id = X.product_id;

    Open pRefHeader FOR
    SELECT pPartnerId AS partner_id,
        CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_code(pPartnerId) END AS partner_code,
            CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pPartnerId) END AS partner_name,
        pDatetime AS print_datetime, pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name;
    RETURN NEXT pRefHeader;


    Open pRefDetail FOR
    SELECT  supplier_code, supplier_name, product_name, size AS psize, color, product_code, supplier_product_code,
            qty_commitment, qty_add, qty_cancel, qty_receive, qty_os_receive, qty_claim,
            qty_nett_sell, qty_end_balance_supplier AS qty_end_balance
    FROM tt_inventory_stock_summary_consignment
    WHERE session_id = pSessionId;
    RETURN NEXT pRefDetail ;

    DELETE FROM tt_inventory_stock_summary_consignment WHERE session_id = pSessionId;

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