in_summary_qty

CREATE OR REPLACE FUNCTION in_summary_qty(bigint, character varying, bigint, character varying, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE

    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pYearMonth          ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;
    pUserId             ALIAS FOR $6;

    vDocTypeAwal            bigint;
    vEmptyId                bigint;
    vNextYearMonth          character varying(6);
    vResult                 integer;
BEGIN

    vDocTypeAwal := -99;
    vEmptyId := -99;
    vResult = 0;

    SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;

    DELETE FROM tt_summary_monthly_qty WHERE session_id = pSessionId;

    DELETE FROM in_summary_monthly_invalid_qty
    WHERE tenant_id = pTenantId
    AND date_year_month = vNextYearMonth
    AND ou_id = pOuId;

    INSERT INTO tt_summary_monthly_qty
    (session_id, 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)
    SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_id, A.sub_ou_id, A.doc_type_id, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty
    FROM in_summary_monthly_qty A
    WHERE A.date_year_month = pYearMonth AND
        A.doc_type_id = vDocTypeAwal AND
        A.ou_id = pOuId AND
        A.tenant_id = pTenantId;

    INSERT INTO tt_summary_monthly_qty
    (session_id, 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)
    SELECT pSessionId, C.year_month_date, A.tenant_id, B.ou_bu_id, A.ou_id, A.doc_type_id, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.tenant_id = pTenantId;

    INSERT INTO tt_summary_monthly_qty
    (session_id, 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)
    SELECT pSessionId, vNextYearMonth, A.tenant_id, A.ou_id, A.sub_ou_id, vDocTypeAwal, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, SUM(A.qty)
    FROM tt_summary_monthly_qty A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_id, A.sub_ou_id, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id;

    INSERT INTO in_summary_monthly_invalid_qty(
        date_year_month, tenant_id, ou_id, sub_ou_id, warehouse_id,
        product_id, base_uom_id, qty,
        "version", create_datetime, create_user_id, update_datetime, update_user_id
    )
    SELECT A.date_year_month, A.tenant_id, A.ou_id, A.sub_ou_id, A.warehouse_id,
        A.product_id, A.base_uom_id, SUM(A.qty),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_summary_monthly_qty A
    WHERE A.session_id = pSessionId AND
        A.date_year_month = vNextYearMonth
    GROUP BY  A.date_year_month, A.tenant_id, A.ou_id, A.sub_ou_id, A.warehouse_id, A.product_id, A.base_uom_id
    HAVING SUM(A.qty) < 0;

    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 A.date_year_month, A.tenant_id, A.ou_id, A.sub_ou_id, A.doc_type_id, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_summary_monthly_qty A
    WHERE A.session_id = pSessionId AND
        A.date_year_month = vNextYearMonth;

    INSERT INTO in_summary_monthly_activity
    (date_year_month, tenant_id, ou_id, sub_ou_id, doc_type_id,
    warehouse_id, product_id, product_balance_id, product_status,
    activity_gl_id, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pYearMonth, A.tenant_id, D.ou_bu_id, D.ou_id, A.doc_type_id,
        A.warehouse_id, A.product_id, A.product_balance_id, A.product_status,
        B.activity_gl_id, A.base_uom_id, SUM(A.qty),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM in_log_product_balance_stock A, in_inventory B, dt_date C, m_ou_structure D
    WHERE A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.tenant_id = pTenantId AND
        A.tenant_id = B.tenant_id AND
        (A.ou_id = B.ou_from_id OR A.ou_id = B.ou_to_id ) AND
        A.doc_type_id = B.doc_type_id AND
        A.doc_no = B.doc_no AND
        A.doc_date = B.doc_date AND
        B.activity_gl_id <> vEmptyId AND
        A.ou_id = D.ou_id AND
        D.ou_bu_id = pOuId
    GROUP BY A.tenant_id, D.ou_bu_id, D.ou_id, A.doc_type_id, A.warehouse_id,
        A.product_id, A.product_balance_id, A.product_status, B.activity_gl_id, A.base_uom_id;


    DELETE FROM tt_summary_monthly_qty WHERE session_id = pSessionId;

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