/*
 * Fitra 23 Feb 2017
 * */

CREATE OR REPLACE FUNCTION upload_adjustment_stock_qty(bigint)
  RETURNS INTEGER AS
$BODY$

DECLARE
    pUlHeaderId             ALIAS FOR $1;

    vTenantId                       bigint;
    vCount                          bigint;
    vCountNoSerial                  bigint;
    vCountSerial                    bigint;
    vCountItem                      bigint;
    vStatusOk                       CHARACTER varying;
    vStatusFail                     CHARACTER varying;
    vStatusSkip                     CHARACTER varying;
    vStatusX                        CHARACTER varying;
    vEmpty                          CHARACTER varying;
    vEmptySerialNumber              CHARACTER varying;
    vValidate                       bigint;
    vCurrentDateTime                CHARACTER varying;
    vYes                            CHARACTER varying;
    vNullInt                        bigint;
    vAutonumGeneratedId             bigint;
    vAdjustmentStockQtyDocTypeId    bigint;
    vAdjustmentStockQtyId           bigint;
    vSchemeAdjustmentStockQty       CHARACTER varying;
    vUserId                         bigint;

    vDocNo                  CHARACTER varying;
    vDocDate                CHARACTER varying;
    vWarehouseId            bigint;
    vActivityGlId           bigint;
    vOuRcId                 bigint;
    vSeqmentId              bigint;
    vGroupProductId         bigInt;

BEGIN
    vStatusOk := 'OK';
    vStatusFail := 'FAIL';
    vStatusSkip := 'SKIP';
    vStatusX := 'X'; -- untuk yang fail karena validasi difungsi ini, akan ditandai X dulu untuk membedakan dengan yang validasi dari Spring batch untuk domain data
    vEmpty := '';
    vEmptySerialNumber := ' ';
    vNullInt=-99;
    vYes :='Y';
    vAdjustmentStockQtyDocTypeId := 521;
    vSchemeAdjustmentStockQty='EA20';

    SELECT tenant_id INTO vTenantId
    from ul_header
    WHERE ul_header_id=pUlHeaderId;

    SELECT user_id INTO vUserId
    from ul_header
    WHERE ul_header_id=pUlHeaderId;

   SELECT f_datetime((extract(epoch from NOW())*1000)::bigint) INTO vCurrentDateTime;

   IF isnumeric(f_get_ul_header_value(pUlHeaderId,'adjustmentStockQtyId')) THEN
        vAdjustmentStockQtyId:=f_get_ul_header_value(pUlHeaderId,'adjustmentStockQtyId')::bigint;
   END IF;

   SELECT doc_date INTO vDocDate
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   SELECT doc_no INTO vDocNo
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   SELECT warehouse_from_id INTO vWarehouseId
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   SELECT activity_gl_id INTO vActivityGlId
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   SELECT ou_rc_id INTO vOuRcId
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   SELECT segment_id INTO vSeqmentId
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   SELECT group_product_id INTO vGroupProductId
   FROM in_inventory
   WHERE inventory_id=vAdjustmentStockQtyId;

   IF isnumeric(f_get_ul_header_value(pUlHeaderId,'groupProductId')) THEN
        vGroupProductId:=f_get_ul_header_value(pUlHeaderId,'groupProductId')::bigint;
   END IF;

   --hapus dokumen jika terjadi kesalahan csv
   IF EXISTS(SELECT 1 FROM ul_skip_detail WHERE ul_header_id = pUlHeaderId) THEN
        --update STATUS autonum menjadi unused
        UPDATE autonum_generated SET flg_unused=vYes, update_datetime = vCurrentDateTime, update_user_id = vUserId
        WHERE tenant_id = vTenantId AND
              year_month_date = vDocDate AND
              value_auto_num = vDocNo AND
              scheme = vSchemeAdjustmentStockQty;

        SELECT autonum_generated_id INTO vAutonumGeneratedId
        FROM autonum_generated A
        WHERE tenant_id = vTenantId AND
              year_month_date = vDocDate AND
              value_auto_num = vDocNo AND
              scheme = vSchemeAdjustmentStockQty;

        --delete autonum_ref_mapping
        DELETE FROM autonum_ref_mapping
        WHERE autonum_generated_id =vAutonumGeneratedId;


        --delete approval
        DELETE FROM awe_worklist
        WHERE scheme = vSchemeAdjustmentStockQty AND
        tenant_id = vTenantId AND
        doc_id = vAdjustmentStockQtyId AND
        doc_no = vDocNo;

        DELETE FROM awe_currdoc_status
        WHERE scheme = vSchemeAdjustmentStockQty AND
        tenant_id = vTenantId AND
        doc_id = vAdjustmentStockQtyId AND
        doc_no = vDocNo;

        DELETE FROM in_inventory
        WHERE inventory_id=vAdjustmentStockQtyId;

        RETURN vNullInt;
        --RAISE EXCEPTION 'ADA ITEM YANG DI SKIP';
    END IF;
   --update ul_adjustment_stock_qty product serial number yg EMPTY menjadi spasi
   --UPDATE ul_adjustment_stock_qty Z SET serial_number=vEmptySerialNumber
   --WHERE Z.ul_header_id = pUlHeaderId AND serial_number=vEmpty;

   --validasi group product
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusSkip , MESSAGE = Z.message||'product skipped due not match group product,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND C.ul_header_id=Z.ul_header_id
   AND NOT EXISTS (SELECT (1) FROM m_product B
                              INNER JOIN m_ctgr_product D ON D.ctgr_product_id = B.ctgr_product_id
                              INNER JOIN m_group_product E ON E.group_product_id = D.group_product_id
   WHERE Z.product_code=B.product_code AND C.tenant_id=B.tenant_id AND E.group_product_id=vGroupProductId);

   --validasi adjustment qty = 0
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusSkip , MESSAGE = Z.message||'product skipped due adjustment qty = 0 ,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND (Z.balance_qty_system::numeric-Z.balance_qty_current::numeric)=0;

   --validasi product CODE harus ada
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'product code not exists,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND NOT EXISTS (SELECT (1) FROM m_product B
   WHERE Z.product_code=B.product_code AND C.tenant_id=B.tenant_id);

   --validasi jika ada serial number
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'product should not have serial number,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND Z.serial_number<>vEmpty
   AND Z.serial_number<>vEmptySerialNumber
   AND NOT EXISTS (SELECT (1) FROM m_product B
                   JOIN m_sub_ctgr_product C ON C.sub_ctgr_product_id=B.sub_ctgr_product_id AND C.flg_serial_number=vYes
                   WHERE Z.product_code=B.product_code AND C.tenant_id=B.tenant_id);

   --validasi YEAR made
   UPDATE ul_adjustment_stock_qty Z  SET STATUS= vStatusX , MESSAGE = Z.message||'year made not valid format (must numeric),'
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND Z.serial_number<>vEmpty
   AND Z.serial_number<>vEmptySerialNumber
   AND NOT isnumeric(Z.product_year_made);

    --validasi expired DATE
   UPDATE ul_adjustment_stock_qty Z  SET STATUS= vStatusX , MESSAGE = Z.message||'expired date not valid format or not numeric,'
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND Z.serial_number<>vEmpty
   AND Z.serial_number<>vEmptySerialNumber
   AND NOT(is_date(Z.product_expired_date) AND isnumeric(Z.product_expired_date));

   --validasi product STATUS
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'product status not exists,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND NOT EXISTS (SELECT (1) FROM m_product_status B
   WHERE Z.product_status=B.product_status_code AND C.tenant_id=B.tenant_id);

   --validasi balance qty system harus numeric
   UPDATE ul_adjustment_stock_qty Z  SET STATUS= vStatusX , MESSAGE = Z.message||'balance qty system must numeric,'
   FROM
   ul_adjustment_stock_qty A
   WHERE A.ul_header_id = pUlHeaderId
   AND A.status <> vStatusFail
   AND A.status <> vStatusSkip
   AND NOT isnumeric(A.balance_qty_system)
   AND Z.ul_adjustment_stock_qty_id = A.ul_adjustment_stock_qty_id;

   --validasi balance qty system harus >=0
   UPDATE ul_adjustment_stock_qty Z  SET STATUS= vStatusX , MESSAGE = Z.message||'balance qty system must equal or greater than 0,'
   FROM
   ul_adjustment_stock_qty A
   WHERE A.ul_header_id = pUlHeaderId
   AND A.status <> vStatusFail
   AND A.status <> vStatusSkip
   AND NOT is_greater_equal_with(A.balance_qty_system,0)
   AND Z.ul_adjustment_stock_qty_id = A.ul_adjustment_stock_qty_id;

   --validasi balance qty current harus numeric
   UPDATE ul_adjustment_stock_qty Z  SET STATUS= vStatusX , MESSAGE = Z.message||'balance qty current must numeric,'
   FROM
   ul_adjustment_stock_qty A
   WHERE A.ul_header_id = pUlHeaderId
   AND A.status <> vStatusFail
   AND A.status <> vStatusSkip
   AND NOT isnumeric(A.balance_qty_current)
   AND Z.ul_adjustment_stock_qty_id = A.ul_adjustment_stock_qty_id;

   --validasi balance qty current harus >=0
   UPDATE ul_adjustment_stock_qty Z  SET STATUS= vStatusX , MESSAGE = Z.message||'balance qty current must equal or greater than 0,'
   FROM
   ul_adjustment_stock_qty A
   WHERE A.ul_header_id = pUlHeaderId
   AND A.status <> vStatusFail
   AND A.status <> vStatusSkip
   AND NOT is_greater_equal_with(A.balance_qty_current,0)
   AND Z.ul_adjustment_stock_qty_id = A.ul_adjustment_stock_qty_id;

   --validasi jika adj qty < 0 dan stock baru , tidak serial number
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'new stock can not less then 0,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND Z.balance_qty_current::numeric-Z.balance_qty_system::numeric < 0
   AND (Z.serial_number=vEmpty OR Z.serial_number=vEmptySerialNumber)
   AND NOT EXISTS (SELECT (1)
                   FROM m_product D
                   INNER JOIN in_product_balance E ON E.product_id = D.product_id AND E.serial_number = vEmptySerialNumber
                   INNER JOIN in_product_balance_stock F ON F.product_balance_id=E.product_balance_id AND F.product_status=Z.product_status AND F.warehouse_id=vWarehouseId
                   WHERE D.product_code=Z.product_code AND
                         D.tenant_id=C.tenant_id);

   --validasi jika adj qty < 0 dan stock baru, serial number
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'new stock can not less then 0,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND Z.balance_qty_current::numeric-Z.balance_qty_system::numeric < 0
   AND Z.serial_number<>vEmpty
   AND Z.serial_number<>vEmptySerialNumber
   AND NOT EXISTS (SELECT (1)
                   FROM m_product D
                   INNER JOIN in_product_balance E ON E.product_id = D.product_id AND E.serial_number = Z.serial_number
                   INNER JOIN in_product_balance_stock F ON F.product_balance_id=E.product_balance_id AND F.product_status=Z.product_status AND F.warehouse_id=vWarehouseId
                   WHERE D.product_code=Z.product_code AND
                         D.tenant_id=C.tenant_id);

   --validasi jika adj qty < 0 maka qty stock >= adj qty, tidak serial number
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'insufficient product balance stock,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND (Z.serial_number=vEmpty OR Z.serial_number=vEmptySerialNumber)
   AND EXISTS (SELECT (1)
                   FROM m_product D
                   INNER JOIN in_product_balance E ON E.product_id = D.product_id AND E.serial_number = vEmptySerialNumber
                   INNER JOIN in_product_balance_stock F ON F.product_balance_id=E.product_balance_id AND F.product_status=Z.product_status AND F.warehouse_id=vWarehouseId
                   WHERE D.product_code=Z.product_code AND
                         D.tenant_id=C.tenant_id AND
                         F.qty+(Z.balance_qty_current::numeric-Z.balance_qty_system::numeric)<0);

   --validasi jika adj qty < 0 maka qty stock >= adj qty, serial number
   UPDATE ul_adjustment_stock_qty Z SET STATUS= vStatusX , MESSAGE = Z.message||'insufficient product balance stock,'
   FROM ul_header C
   WHERE Z.ul_header_id = pUlHeaderId
   AND Z.status <> vStatusFail
   AND Z.status <> vStatusSkip
   AND C.ul_header_id=Z.ul_header_id
   AND Z.serial_number<>vEmpty
   AND Z.serial_number<>vEmptySerialNumber
   AND EXISTS (SELECT (1)
                   FROM m_product D
                   INNER JOIN in_product_balance E ON E.product_id = D.product_id AND E.serial_number = Z.serial_number
                   INNER JOIN in_product_balance_stock F ON F.product_balance_id=E.product_balance_id AND F.product_status=Z.product_status AND F.warehouse_id=vWarehouseId
                   WHERE D.product_code=Z.product_code AND
                         D.tenant_id=C.tenant_id AND
                         F.qty+(Z.balance_qty_current::numeric-Z.balance_qty_system::numeric)<0);

   --validasi item yang sama
   SELECT SUM(f_validate_duplicate_item_adjustment_stock_qty(A.ul_adjustment_stock_qty_id)) INTO vValidate
   FROM (SELECT * from ul_adjustment_stock_qty B
   WHERE B.ul_header_id=pUlHeaderId AND
         b.status <> vStatusSkip AND
         B.status <> vStatusFail
         ORDER BY B.ul_adjustment_stock_qty_id ASC) A;

   -- UPDATE semua yang vStatusX jadi xStatusFail
   UPDATE ul_adjustment_stock_qty SET STATUS = vStatusFail
   WHERE STATUS = vStatusX
   AND ul_header_id = pUlHeaderId;

   -- UPDATE semua yang vEmpty jadi vStatusOk
   UPDATE ul_adjustment_stock_qty SET STATUS = vStatusOk
   WHERE STATUS = vEmpty
   AND ul_header_id = pUlHeaderId;

   SELECT COUNT(ul_adjustment_stock_qty_id) INTO vCount
   FROM ul_adjustment_stock_qty
   WHERE
     ul_header_id = pUlHeaderId
     AND STATUS = vStatusFail;

   IF (vCount=0) THEN
       SELECT COUNT(ul_adjustment_stock_qty_id) INTO vCountNoSerial
       FROM ul_adjustment_stock_qty
       WHERE
         ul_header_id = pUlHeaderId
         AND STATUS = vStatusOk
         AND serial_number=vEmpty;

       --insert item yg tdk ada serialnya
       INSERT INTO in_inventory_item(
                tenant_id, inventory_id, line_no, product_id,
                product_balance_id, product_status, serial_number, product_expired_date,
                product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
                curr_code, amount, base_uom_id, qty_request, qty_realization,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id)
        SELECT B.tenant_id, vAdjustmentStockQtyId, row_number() OVER() AS row_number, C.product_id,
               D.product_balance_id, A.product_status, vEmptySerialNumber, D.product_expired_date,
               D.product_year_made, D.lot_number,vNullInt,vNullInt,vNullInt,
               vNullInt,0,C.base_uom_id,(A.balance_qty_current::numeric-A.balance_qty_system::numeric),(A.balance_qty_current::numeric-A.balance_qty_system::numeric),
               vEmpty,0,vCurrentDateTime,B.user_id,vCurrentDateTime,
               B.user_id
        FROM ul_adjustment_stock_qty A
        INNER JOIN ul_header B ON A.ul_header_id=B.ul_header_id
        INNER JOIN m_product C ON C.product_code=A.product_code AND C.tenant_id=B.tenant_id
        INNER JOIN in_product_balance D ON D.tenant_id=B.tenant_id AND D.product_id=C.product_id AND D.serial_number=vEmptySerialNumber
        --INNER JOIN in_product_balance_stock E ON E.product_balance_id=D.product_balance_id AND E.product_status=A.product_status AND E.warehouse_id=vWarehouseId
        WHERE A.ul_header_id=pUlHeaderId AND
              A.status=vStatusOk AND
              (A.serial_number=vEmpty OR A.serial_number=vEmptySerialNumber);

       --insert item yg ada serial numbernya
       SELECT COUNT(inventory_item_id) INTO vCountItem
       FROM in_inventory_item
       WHERE inventory_id=vAdjustmentStockQtyId;

       INSERT INTO in_inventory_item(
                tenant_id, inventory_id, line_no, product_id,
                product_balance_id, product_status, serial_number, product_expired_date,
                product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
                curr_code, amount, base_uom_id, qty_request, qty_realization,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id)
        SELECT B.tenant_id, vAdjustmentStockQtyId, vCountItem+row_number() OVER() AS row_number, C.product_id,
               D.product_balance_id, A.product_status, A.serial_number, D.product_expired_date,
               D.product_year_made, D.lot_number,vNullInt,vNullInt,vNullInt,
               vNullInt,0,C.base_uom_id, (A.balance_qty_current::numeric-A.balance_qty_system::numeric), (A.balance_qty_current::numeric-A.balance_qty_system::numeric),
               vEmpty,0,vCurrentDateTime,B.user_id,vCurrentDateTime,
               B.user_id
        FROM ul_adjustment_stock_qty A
        INNER JOIN ul_header B ON A.ul_header_id=B.ul_header_id
        INNER JOIN m_product C ON C.product_code=A.product_code AND C.tenant_id=B.tenant_id
        INNER JOIN in_product_balance D ON D.tenant_id=B.tenant_id AND D.product_id=C.product_id AND D.serial_number=A.serial_number
        --INNER JOIN in_product_balance_stock E ON E.product_balance_id=D.product_balance_id AND E.product_status=A.product_status AND E.warehouse_id=vWarehouseId
        WHERE A.ul_header_id=pUlHeaderId AND
              A.status=vStatusOk AND
              A.serial_number<>vEmpty AND
              A.serial_number<>vEmptySerialNumber;

       --insert item yg tdk ada serialnya dan belum ada balancenya
       SELECT COUNT(inventory_item_id) INTO vCountItem
       FROM in_inventory_item
       WHERE inventory_id=vAdjustmentStockQtyId;

       INSERT INTO in_inventory_item(
                tenant_id, inventory_id, line_no, product_id,
                product_balance_id, product_status, serial_number, product_expired_date,
                product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
                curr_code, amount, base_uom_id, qty_request, qty_realization,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id)
        SELECT B.tenant_id, vAdjustmentStockQtyId, vCountItem+row_number() OVER() AS row_number, C.product_id,
               vNullInt, A.product_status, vEmptySerialNumber, vEmptySerialNumber,
               vEmptySerialNumber, vEmptySerialNumber,vNullInt,vNullInt,vNullInt,
               vNullInt,0,C.base_uom_id,(A.balance_qty_current::numeric-A.balance_qty_system::numeric),(A.balance_qty_current::numeric-A.balance_qty_system::numeric),
               vEmpty,0,vCurrentDateTime,B.user_id,vCurrentDateTime,
               B.user_id
        FROM ul_adjustment_stock_qty A
        INNER JOIN ul_header B ON A.ul_header_id=B.ul_header_id
        INNER JOIN m_product C ON C.product_code=A.product_code AND C.tenant_id=B.tenant_id
        WHERE A.ul_header_id=pUlHeaderId AND
              A.status=vStatusOk AND
              (A.serial_number=vEmpty OR A.serial_number=vEmptySerialNumber) AND
              NOT EXISTS (SELECT 1 FROM in_product_balance D
                    WHERE B.tenant_id = D.tenant_id AND
                          C.product_id = D.product_id AND
                          vEmptySerialNumber = D.serial_number);

       --insert item yg ada serial numbernya dan belum ada balancenya
       SELECT COUNT(inventory_item_id) INTO vCountItem
       FROM in_inventory_item
       WHERE inventory_id=vAdjustmentStockQtyId;

       INSERT INTO in_inventory_item(
                tenant_id, inventory_id, line_no, product_id,
                product_balance_id, product_status, serial_number, product_expired_date,
                product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
                curr_code, amount, base_uom_id, qty_request, qty_realization,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id)
        SELECT B.tenant_id, vAdjustmentStockQtyId, vCountItem+row_number() OVER() AS row_number, C.product_id,
               vNullInt, A.product_status, A.serial_number, A.product_expired_date,
               A.product_year_made, vEmptySerialNumber,vNullInt,vNullInt,vNullInt,
               vNullInt,0,C.base_uom_id, (A.balance_qty_current::numeric-A.balance_qty_system::numeric), (A.balance_qty_current::numeric-A.balance_qty_system::numeric),
               vEmpty,0,vCurrentDateTime,B.user_id,vCurrentDateTime,
               B.user_id
        FROM ul_adjustment_stock_qty A
        INNER JOIN ul_header B ON A.ul_header_id=B.ul_header_id
        INNER JOIN m_product C ON C.product_code=A.product_code AND C.tenant_id=B.tenant_id
        WHERE A.ul_header_id=pUlHeaderId AND
              A.status=vStatusOk AND
              (A.balance_qty_current::numeric-A.balance_qty_system::numeric) <> 0 AND
              A.serial_number <> vEmpty AND A.serial_number <> vEmptySerialNumber AND
              NOT EXISTS (SELECT 1 FROM in_product_balance D
                    WHERE B.tenant_id = D.tenant_id AND
                          C.product_id = D.product_id AND
                          A.serial_number = D.serial_number);

       --update product balance stock yg tidak ada serial numbernya jika adj qty < 0
       UPDATE in_product_balance_stock
       SET qty = qty+(F.balance_qty_current::numeric-F.balance_qty_system::numeric),
           version = version+1,
           update_datetime = vCurrentDateTime,
           update_user_id = F.user_id
       FROM (SELECT B.tenant_id, C.product_id,A.product_status, A.balance_qty_current,A.balance_qty_system,D.product_balance_id, B.user_id
             FROM ul_adjustment_stock_qty A
             INNER JOIN ul_header B ON A.ul_header_id=B.ul_header_id
             INNER JOIN m_product C ON C.product_code=A.product_code AND C.tenant_id=B.tenant_id
             INNER JOIN in_product_balance D ON D.tenant_id=B.tenant_id AND D.product_id=C.product_id AND D.serial_number=vEmptySerialNumber
             WHERE A.ul_header_id=pUlHeaderId AND
                   A.status = vStatusOk AND
                   (A.serial_number = vEmpty OR A.serial_number = vEmptySerialNumber) AND
                   (A.balance_qty_current::numeric-A.balance_qty_system::numeric) < 0 AND
                   EXISTS (SELECT (1)
                           FROM in_product_balance_stock E
                           WHERE E.tenant_id=B.tenant_id AND
                                 E.warehouse_id=vWarehouseId AND
                                 E.product_id=C.product_id AND
                                 E.product_balance_id=D.product_balance_id AND
                                 E.product_status=A.product_status)) F
       WHERE in_product_balance_stock.tenant_id=F.tenant_id AND
             in_product_balance_stock.warehouse_id=vWarehouseId AND
             in_product_balance_stock.product_id=F.product_id AND
             in_product_balance_stock.product_balance_id=F.product_balance_id AND
             in_product_balance_stock.product_status=F.product_status;

    --update product balance stock yg ada serial numbernya jika adj qty < 0
       UPDATE in_product_balance_stock
       SET qty = qty+(F.balance_qty_current::numeric-F.balance_qty_system::numeric),
           version = version+1,
           update_datetime = vCurrentDateTime,
           update_user_id = F.user_id
       FROM (SELECT B.tenant_id, C.product_id,A.product_status, A.balance_qty_current,A.balance_qty_system,D.product_balance_id, B.user_id
             FROM ul_adjustment_stock_qty A
             INNER JOIN ul_header B ON A.ul_header_id=B.ul_header_id
             INNER JOIN m_product C ON C.product_code=A.product_code AND C.tenant_id=B.tenant_id
             INNER JOIN in_product_balance D ON D.tenant_id=B.tenant_id AND D.product_id=C.product_id AND D.serial_number=A.serial_number
             WHERE A.ul_header_id=pUlHeaderId AND
                   A.status = vStatusOk AND
                   A.serial_number <> vEmpty AND
                   A.serial_number <> vEmptySerialNumber AND
                   (A.balance_qty_current::numeric-A.balance_qty_system::numeric) < 0 AND
                   EXISTS (SELECT (1)
                           FROM in_product_balance_stock E
                           WHERE E.tenant_id=B.tenant_id AND
                                 E.warehouse_id=vWarehouseId AND
                                 E.product_id=C.product_id AND
                                 E.product_balance_id=d.product_balance_id AND
                                 E.product_status=A.product_status)) F
       WHERE in_product_balance_stock.tenant_id=F.tenant_id AND
             in_product_balance_stock.warehouse_id=vWarehouseId AND
             in_product_balance_stock.product_id=F.product_id AND
             in_product_balance_stock.product_balance_id=F.product_balance_id AND
             in_product_balance_stock.product_status=F.product_status;
    ELSE
        --update STATUS autonum menjadi unused
        UPDATE autonum_generated SET flg_unused=vYes, update_datetime = vCurrentDateTime, update_user_id = vUserId
        WHERE tenant_id = vTenantId AND
              year_month_date = vDocDate AND
              value_auto_num = vDocNo AND
              scheme = vSchemeAdjustmentStockQty;

        SELECT autonum_generated_id INTO vAutonumGeneratedId
        FROM autonum_generated A
        WHERE tenant_id = vTenantId AND
              year_month_date = vDocDate AND
              value_auto_num = vDocNo AND
              scheme = vSchemeAdjustmentStockQty;

        --delete autonum_ref_mapping
        DELETE FROM autonum_ref_mapping
        WHERE autonum_generated_id =vAutonumGeneratedId;


        --delete approval
        DELETE FROM awe_worklist
        WHERE scheme = vSchemeAdjustmentStockQty AND
        tenant_id = vTenantId AND
        doc_id = vAdjustmentStockQtyId AND
        doc_no = vDocNo;

        DELETE FROM awe_currdoc_status
        WHERE scheme = vSchemeAdjustmentStockQty AND
        tenant_id = vTenantId AND
        doc_id = vAdjustmentStockQtyId AND
        doc_no = vDocNo;

        DELETE FROM in_inventory
        WHERE inventory_id=vAdjustmentStockQtyId;

    END IF;

   RETURN vCount;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;