CREATE OR REPLACE FUNCTION f_init_adj_stock_qty_preparation_for_promo_bundling(bigint, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId       alias for $1;
    pSessionId      alias for $2;
    pSoMlmId        alias for $3;

    vItemTypeChild      character varying := 'C';
    vWhsCodeDisplay     character varying := 'DISPLAY';
    vWhsLocCodeDef      character varying := 'DEF';
    vSpaceValue     character varying := ' ';

    vNewSessionId       character varying;
    vWhsIdDisplay       bigint;

BEGIN
    vNewSessionId := pSessionId||'BUNDLING';
    DELETE FROM tt_prepare_adj_stock_item WHERE session_id = vNewSessionId;

    -- Warehouse ID
    SELECT warehouse_id INTO vWhsIdDisplay
    FROM m_warehouse WHERE warehouse_code = vWhsCodeDisplay AND tenant_id = pTenantId;

    -- INSERT item-item yang ada di dalam SO ke dalam Table temp.
    -- pada kondisi ini, masih ada kemungkinan barang yang dibeli, belum pernah dilakukan penerimaan barang sehingga tidak memiliki product_balance_id
    INSERT INTO tt_prepare_adj_stock_item (
      session_id, tenant_id, doc_id, doc_type_id, doc_no, doc_date, doc_item_id,
      from_product_id, from_warehouse_id, from_whs_location_code, from_product_balance_id, from_product_status,
      to_product_id, to_warehouse_id, to_whs_location_code, to_product_balance_id, to_product_status, qty,
      create_datetime, create_user_id, update_datetime, update_user_id, version
    )
    SELECT vNewSessionId, pTenantId, C.so_mlm_id, C.doc_type_id, C.doc_no, C.doc_date, B.so_mlm_item_id,
      D.sub_original_product_id, vWhsIdDisplay, vSpaceValue, COALESCE(E.product_balance_id, -99), 'GOOD',
      D.sub_product_id, vWhsIdDisplay, vSpaceValue, COALESCE(F.product_balance_id, -99), 'GOOD', SUM(B.qty_int),
      C.create_datetime, C.create_user_id, C.update_datetime, C.update_user_id, 0
    FROM tt_process_so_with_product_bundling_01 A
    INNER JOIN sl_so_mlm_item B ON A.so_mlm_item_id = B.so_mlm_item_id
    INNER JOIN sl_so_mlm C ON B.so_mlm_id = C.so_mlm_id
    INNER JOIN m_product_bundling_mapping D
        ON A.product_catalog_id = D.sub_product_catalog_id
        AND A.parent_product_catalog_id = D.product_catalog_bundling_id
    LEFT JOIN in_product_balance E ON D.sub_original_product_id = E.product_id AND E.serial_number = vSpaceValue
    LEFT JOIN in_product_balance F ON D.sub_product_id = F.product_id AND F.serial_number = vSpaceValue
    WHERE A.session_id = pSessionId
    AND A.item_type = vItemTypeChild
    AND C.so_mlm_id = pSoMlmId
    GROUP BY C.so_mlm_id, C.doc_type_id, C.doc_no, C.doc_date, B.so_mlm_item_id,
      D.sub_original_product_id, E.product_balance_id,
      D.sub_product_id, F.product_balance_id, B.qty_int,
      C.create_datetime, C.create_user_id, C.update_datetime, C.update_user_id;

    -- INSERT ke table in_product_balance untuk product-product yang belum penah dilakukan penerimaan
    -- INSERT untuk product FROM (ORIGINAL)
    INSERT INTO in_product_balance(
        tenant_id, product_id, serial_number, lot_number,
        product_expired_date, product_year_made,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT pTenantId, A.from_product_id, vSpaceValue, vSpaceValue,
        vSpaceValue, vSpaceValue,
        0, A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id
          FROM tt_prepare_adj_stock_item A
        WHERE from_product_balance_id = -99
        AND session_id = vNewSessionId
        AND NOT EXISTS (
        SELECT 1 FROM in_product_balance B
        WHERE A.from_product_id = B.product_id
        AND B.serial_number = ' '
    )
        GROUP BY A.from_product_id, A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id;

    -- INSERT untuk product TO (DUMMY)
        INSERT INTO in_product_balance(
        tenant_id, product_id, serial_number, lot_number,
        product_expired_date, product_year_made,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT pTenantId, A.to_product_id, vSpaceValue, vSpaceValue,
        vSpaceValue, vSpaceValue,
        0, A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id
          FROM tt_prepare_adj_stock_item A
        WHERE from_product_balance_id = -99
        AND session_id = vNewSessionId
        AND NOT EXISTS (
        SELECT 1 FROM in_product_balance B
        WHERE A.to_product_id = B.product_id
        AND B.serial_number = ' '
    )
        GROUP BY A.to_product_id, A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id;

    -- DELETE data di Table tt_prepare_adj_stock_item, dengan asumsi masih ada product_balance_id yang -99
        DELETE FROM tt_prepare_adj_stock_item WHERE session_id = vNewSessionId;

    -- INSERT kembali ke table tt_prepare_adj_stock_item dengan data product_balance_id yang sudah lengkap
    INSERT INTO tt_prepare_adj_stock_item (
      session_id, tenant_id, doc_id, doc_type_id, doc_no, doc_date, doc_item_id,
      from_product_id, from_warehouse_id, from_whs_location_code, from_product_balance_id, from_product_status,
      to_product_id, to_warehouse_id, to_whs_location_code, to_product_balance_id, to_product_status, qty,
      create_datetime, create_user_id, update_datetime, update_user_id, version
    )
    SELECT vNewSessionId, pTenantId, C.so_mlm_id, C.doc_type_id, C.doc_no, C.doc_date, B.so_mlm_item_id,
      D.sub_original_product_id, vWhsIdDisplay, vSpaceValue, COALESCE(E.product_balance_id, -99), 'GOOD',
      D.sub_product_id, vWhsIdDisplay, vSpaceValue, COALESCE(F.product_balance_id, -99), 'GOOD', SUM(B.qty_int),
      C.create_datetime, C.create_user_id, C.update_datetime, C.update_user_id, 0
    FROM tt_process_so_with_product_bundling_01 A
    INNER JOIN sl_so_mlm_item B ON A.so_mlm_item_id = B.so_mlm_item_id
    INNER JOIN sl_so_mlm C ON B.so_mlm_id = C.so_mlm_id
    INNER JOIN m_product_bundling_mapping D
        ON A.product_catalog_id = D.sub_product_catalog_id
        AND A.parent_product_catalog_id = D.product_catalog_bundling_id
    LEFT JOIN in_product_balance E ON D.sub_original_product_id = E.product_id AND E.serial_number = vSpaceValue
    LEFT JOIN in_product_balance F ON D.sub_product_id = F.product_id AND F.serial_number = vSpaceValue
    WHERE A.session_id = pSessionId
    AND A.item_type = vItemTypeChild
    AND C.so_mlm_id = pSoMlmId
    GROUP BY C.so_mlm_id, C.doc_type_id, C.doc_no, C.doc_date, B.so_mlm_item_id,
      D.sub_original_product_id, E.product_balance_id,
      D.sub_product_id, F.product_balance_id, B.qty_int,
      C.create_datetime, C.create_user_id, C.update_datetime, C.update_user_id;

    -------------------------------------------------------------------------------------------------------
    -- UPDATE field whs_location_code Pada Tabel stock_opname_planning_adjustment
    -- A. Update Kode Produk yang memiliki kode lokasi produk
    -------------------------------------------------------------------------------------------------------

    -- Update whs_location_code untuk FROM dan TO
    WITH whs_location_code_list AS (
          SELECT A.product_id AS product_id, MAX(C.whs_location_code) AS whs_location_code
          FROM m_product A
          INNER JOIN m_whs_location_product B ON A.product_id = B.product_id
          INNER JOIN m_whs_location C ON B.whs_location_id = C.whs_location_id AND warehouse_id = vWhsIdDisplay
          GROUP BY A.product_id
    )
    UPDATE tt_prepare_adj_stock_item A
      SET
      from_whs_location_code = B.whs_location_code,
      to_whs_location_code = B.whs_location_code
    FROM whs_location_code_list B
    WHERE A.from_product_id = B.product_id
    AND A.session_id = vNewSessionId
    AND A.doc_id = pSoMlmId;


    -------------------------------------------------------------------------------------------------------
    -- B. Update Kode Produk yang tidak memiliki kode lokasi produk dengan DEF
    -------------------------------------------------------------------------------------------------------
    -- from_whs_location_code
    UPDATE tt_prepare_adj_stock_item
    SET from_whs_location_code = vWhsLocCodeDef
    WHERE from_whs_location_code = vSpaceValue AND session_id = vNewSessionId AND doc_id = pSoMlmId;

    -- to_whs_location_code
    UPDATE tt_prepare_adj_stock_item
    SET to_whs_location_code = vWhsLocCodeDef
    WHERE to_whs_location_code = vSpaceValue AND session_id = vNewSessionId AND doc_id = pSoMlmId;

END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;