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

-- DROP FUNCTION sl_so_mlm_remove_item(bigint, character varying, bigint, bigint, bigint);

CREATE OR REPLACE FUNCTION sl_so_mlm_remove_item(bigint, character varying, bigint, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pTenantId               alias for $1;
    pDateTime               alias for $2;
    pUserId                 alias for $3;
    pSoMlmItemId            alias for $4;
    pSoMlmId                alias for $5;

    vProductId              bigint;
    vProductCatalogId           bigint;
    vDecimalRounding            integer;
    vRoundingMode           character varying;
    vFlagTaxAmount          character varying;

    vSubTotalPriceAfterDisc numeric;
    vYearMonth              character varying(6);
    vPartnerId              bigint;

    pRefHeader              REFCURSOR := 'refHeader';

BEGIN
/**
 * 1. cari Sub Total Price After Disc untuk di gunakan sebagai pengurang usage_amount
 * 2. Update table reserved, dikurangin dengan seluruh qty_so dari item tersebut
 * 3. Delete dari sl_so_mlm_item
 * 4. Update commission amount di sl_so_mlm, kurangi dengan komisi dari item tersebut
 * 5. Kurangi nilai usage_amount di table sl_partner_credit_limit
 *
 */

        vDecimalRounding := 0;
        vRoundingMode := 'RD';
        vFlagTaxAmount := 'Y';

        vSubTotalPriceAfterDisc := 0;
        vYearMonth := ' ';
        vPartnerId := -99;


     -- 1. cari Sub Total Price After Disc untuk di gunakan sebagai pengurang usage_amount
        SELECT (qty_so * price_after_disc) INTO vSubTotalPriceAfterDisc
            FROM sl_so_mlm_item WHERE so_mlm_item_id = pSoMlmItemId;

        SELECT product_catalog_id INTO vProductCatalogId FROM sl_so_mlm_item WHERE so_mlm_item_id = pSoMlmItemId;

        SELECT f_get_product_by_product_catalog_id(vProductCatalogId) INTO vProductId;

     -- 2. update ke in_product_balance_reserved_stock
        DELETE FROM in_product_balance_stock_reserved Z WHERE EXISTS(
                 SELECT 1
                 FROM sl_so_mlm A
                 WHERE A.so_mlm_id = pSoMlmId
                 AND Z.ref_id =A.so_mlm_id
                 AND Z.ref_doc_type_id = A.doc_type_id
        );

     -- 3. Delete dari sl_so_mlm_item
        DELETE FROM sl_so_mlm_item WHERE so_mlm_item_id = pSoMlmItemId;

     -- 4. Update commission amount di sl_so_mlm, kurangi dengan komisi dari item tersebut
       UPDATE sl_so_mlm SET commission_amount = f_get_commission_amount_by_so_mlm_id(pTenantId, pSoMlmId, vDecimalRounding, vRoundingMode)
       WHERE so_mlm_id = pSoMlmId;


      -- 5. Penambahan untuk update ke table sl_partner_credit limit_usage --
        -- Cari year_month dari doc_date
        SELECT LEFT(doc_date, 6) INTO vYearMonth
            FROM sl_so_mlm WHERE so_mlm_id = pSoMlmId;

        -- Cari partner_id yang pesan SO MLM
        SELECT partner_id INTO vPartnerId
            FROM sl_so_mlm WHERE so_mlm_id = pSoMlmId;

        -- [update 22 desember 2015 by hary] partner credit limit usage dihitung pada saat approve

--      -- Update sl_partner_usage_amount + vSubTotalPriceAfterDisc
--          UPDATE sl_partner_credit_limit_usage
--          SET    usage_amount = usage_amount - vSubTotalPriceAfterDisc,
--                 version = version + 1,
--                 update_datetime = pDateTime,
--                 update_user_id = pUserId
--          WHERE  tenant_id = pTenantId
--          AND    partner_id = vPartnerId
--          AND    year_month = vYearMonth;
--
--
--      -- Insert sl_log_partner_usage_amount
--      INSERT INTO sl_log_partner_credit_limit_usage(
--              tenant_id, partner_id, doc_type_id,
--              doc_id, doc_no, doc_date, amount, version,
--              create_datetime, create_user_id, update_datetime, update_user_id)
--      SELECT  pTenantId, A.partner_id, A.doc_type_id,
--              A.so_mlm_id, A.doc_no, A.doc_date, 0 - vSubTotalPriceAfterDisc, 0,
--              pDateTime, pUserId, pDateTime, pUserId
--      FROM    sl_so_mlm A
--      WHERE   A.so_mlm_id = pSoMlmId;

       Open pRefHeader FOR
       SELECT pSoMlmItemId;
       RETURN NEXT pRefHeader;

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