Function Penmabahan Saldo Stock & Master Harga Jual

/**
 * Samuel, Nov 2, 2021
 */

CREATE OR REPLACE FUNCTION f_get_balance_stock_and_sell_price_of_list_m_product_rm(bigint, bigint, numeric, numeric, varchar,
varchar, varchar)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pUserId             ALIAS FOR $2;
    pQtyStokAwal        ALIAS FOR $3;
    pGrossSellPrice     ALIAS FOR $4;
    pFlgIncludeTax      ALIAS FOR $5;
    pFlgPpn             ALIAS FOR $6;
    pStartYearMonth     ALIAS FOR $7;

    vYes                character varying(1);
    vNo                 character varying(1);
    vSpace              character varying;
    vProductStatus      character varying;
    vPriceLevel         character varying;


BEGIN

    vYes := 'Y';
    vNo := 'N';
    vSpace := ' ';
    vProductStatus := 'GOOD';
    vPriceLevel := 'END USER';

    --> Keperluan Penambahan Saldo Stock Barang
    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 A.tenant_id, A.product_id, vSpace, vSpace, vSpace, vSpace, 0, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1
    FROM m_product A
    WHERE NOT EXISTS (SELECT 1 FROM in_product_balance B
                WHERE A.product_id = B.product_id
                AND A.tenant_id = B.tenant_id
                AND B.serial_number = vSpace
                AND B.lot_number = vSpace
    );

    INSERT INTO in_product_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty, version,
    create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, D.warehouse_id, A.product_id, B.product_balance_id, vProductStatus, A.base_uom_id, pQtyStokAwal, 0,
    to_char(NOW(), 'YYYYMMDDHH24MIss'), -1, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1
    FROM m_product A
    INNER JOIN in_product_balance B ON A.product_id = B.product_id
    INNER JOIN i_outlet D ON A.tenant_id = D.tenant_id
    WHERE NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
                WHERE A.tenant_id = C.tenant_id
                AND C.warehouse_id = D.warehouse_id
                AND A.product_id = C.product_id
                AND B.product_balance_id = C.product_balance_id
                AND C.product_status = vProductStatus
    );

    INSERT INTO in_summary_monthly_qty
    (date_year_month, tenant_id, ou_id, sub_ou_id, doc_type_id, warehouse_id, product_id, product_balance_id, product_status,
    base_uom_id, qty, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pStartYearMonth, A.tenant_id, D.ou_id, D.ou_id, -99, D.warehouse_id, A.product_id, B.product_balance_id, vProductStatus,
    A.base_uom_id, pQtyStokAwal, 0, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1
    FROM m_product A
    INNER JOIN in_product_balance B ON A.product_id = B.product_id
    INNER JOIN i_outlet D ON A.tenant_id = D.tenant_id
    WHERE NOT EXISTS (SELECT 1 FROM in_summary_monthly_qty C
                WHERE A.tenant_id = C.tenant_id
                AND C.date_year_month = pStartYearMonth
                AND D.ou_id = C.ou_id
                AND D.ou_id = C.sub_ou_id
                AND C.doc_type_id = -99
                AND D.warehouse_id = C.warehouse_id
                AND A.product_id = C.product_id
                AND B.product_balance_id = C.product_balance_id
                AND C.product_status = vProductStatus
    );

    --> Keperluan Penambahan Data Master Harga Jual Barang
    --> Kondisi Harga Jual Sudah termasuk PPn
    IF (pFlgIncludeTax = vYes AND pFlgPpn = vYes) THEN

        INSERT INTO m_sell_price_product_for_so
        (tenant_id, ou_id, product_id, date_from, date_to, curr_code, gross_sell_price, flg_tax_amount, tax_id,
        tax_amount,
        sell_price,
        min_sell_price, price_level, create_datetime, create_user_id, update_datetime, update_user_id,
        version, active, active_datetime, non_active_datetime)
        SELECT A.tenant_id, B.ou_id, A.product_id, CONCAT(pStartYearMonth,'01'), '30001231', 'IDR', pGrossSellPrice, vYes, D.tax_id,
        CAST(pGrossSellPrice AS numeric) - CEIL(CAST(pGrossSellPrice AS numeric) * 100 / (100 + D.percentage)),
        CEIL(CAST(pGrossSellPrice AS numeric) * 100 / (100 + D.percentage)),
        -1, vPriceLevel, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1,
        0, vYes, to_char(NOW(), 'YYYYMMDDHH24MIss'), ''
        FROM m_product A
        INNER JOIN i_outlet B ON A.tenant_id = B.tenant_id
        INNER JOIN m_tax D ON A.tenant_id = D.tenant_id AND tax_code = 'PPn'
        WHERE NOT EXISTS (SELECT 1 FROM m_sell_price_product_for_so C
                    WHERE A.tenant_id = C.tenant_id
                    AND B.ou_id = C.ou_id
                    AND A.product_id = C.product_id
                    AND C.date_from = CONCAT(pStartYearMonth,'01')
                    AND C.price_level = vPriceLevel
        );

    --> Kondisi Harga Jual belum termasuk PPn & ditambah PPn
    ELSIF (pFlgIncludeTax = vNo AND pFlgPpn = vYes) THEN

        INSERT INTO m_sell_price_product_for_so
        (tenant_id, ou_id, product_id, date_from, date_to, curr_code, gross_sell_price, flg_tax_amount, tax_id,
        tax_amount,
        sell_price, min_sell_price, price_level, create_datetime, create_user_id, update_datetime, update_user_id,
        version, active, active_datetime, non_active_datetime)
        SELECT A.tenant_id, B.ou_id, A.product_id, CONCAT(pStartYearMonth,'01'), '30001231', 'IDR', pGrossSellPrice, vNo, D.tax_id,
        COALESCE(CAST(pGrossSellPrice AS numeric) * D.percentage / 100, 0),
        pGrossSellPrice, -1, vPriceLevel, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1,
        0, vYes, to_char(NOW(), 'YYYYMMDDHH24MIss'), ''
        FROM m_product A
        INNER JOIN i_outlet B ON A.tenant_id = B.tenant_id
        INNER JOIN m_tax D ON A.tenant_id = D.tenant_id AND tax_code = 'PPn'
        WHERE NOT EXISTS (SELECT 1 FROM m_sell_price_product_for_so C
                    WHERE A.tenant_id = C.tenant_id
                    AND B.ou_id = C.ou_id
                    AND A.product_id = C.product_id
                    AND C.date_from = CONCAT(pStartYearMonth,'01')
                    AND C.price_level = vPriceLevel
        );

    --> Kondisi Harga Jual belum termasuk PPn
    ELSE

        INSERT INTO m_sell_price_product_for_so
        (tenant_id, ou_id, product_id, date_from, date_to, curr_code, gross_sell_price, flg_tax_amount, tax_id, tax_amount,
        sell_price, min_sell_price, price_level, create_datetime, create_user_id, update_datetime, update_user_id,
        version, active, active_datetime, non_active_datetime)
        SELECT A.tenant_id, B.ou_id, A.product_id, CONCAT(pStartYearMonth,'01'), '30001231', 'IDR', pGrossSellPrice, vNo, D.tax_id, 0,
        pGrossSellPrice, -1, vPriceLevel, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1, to_char(NOW(), 'YYYYMMDDHH24MIss'), -1,
        0, vYes, to_char(NOW(), 'YYYYMMDDHH24MIss'), ''
        FROM m_product A
        INNER JOIN i_outlet B ON A.tenant_id = B.tenant_id
        INNER JOIN m_tax D ON A.tenant_id = D.tenant_id AND tax_code = 'PPn'
        WHERE NOT EXISTS (SELECT 1 FROM m_sell_price_product_for_so C
                    WHERE A.tenant_id = C.tenant_id
                    AND B.ou_id = C.ou_id
                    AND A.product_id = C.product_id
                    AND C.date_from = CONCAT(pStartYearMonth,'01')
                    AND C.price_level = vPriceLevel
        );

    END IF;


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