Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Saturday, October 19, 2013

HITB Essentials Series– HITB Reset Tool and SEE Tables

I have been recently searching the internet for details regarding the HITB Inventory Reset tool regarding specific information on the SEE tables; (SEE99997), (SEE99998) and (SEE99999) but in vain !  Therefore, I am taking this opportunity to include essential details on the HITB Inventory Reset Tool and the role of these tables throughout the process as part of the HITB Essentials Series.

HITB Inventory Reset Tool uses three primary tables throughout the process to manage the process and come up with the desired result of an Inventory Modules that is clean of errors, reconciled internally, free of integrity errors and tied to GL. In order to do so, each of the above three tables has a specific functionality that is highly essential.

First of all, it is worth to mention the basic four stored procedures that are used with the Inventory HITB Reset Tool, which are;

  • ivHITBBalanceQTYONHND
  • ivHitbRunDataIntegrityChecks
  • ivHitbDataInsert
  • ivHITBSiteSegment

 

SEE Tables:

1- SEE99999 | Data Integrity Checks

In order for the Reset tool to fill out the HITB table and provide the HITB report, inventory must be cleaned of any associated errors. In order to write in precise records in SEE30303, and tie the Inventory Module to General Ledger Correctly. Which is all comes under “Data Integrity Checks” function.

In this essence, this table; which might be the primary SEE table in HITB Reset Process, is responsible of the Data Integrity Checks Function. It uses the four procedures above in order to prepare and insert all the corrupted instances in Inventory and as related to GL.

Integrity Checks can be divided into two main levels (Inventory and GL), within each level, there are several errors that are recorded to be well.

Error Message Level Values:

  • Inventory = 2
  • GL = 1

 

Error Message Level

Error Message Description

Error Message Number

Comment

2

Receipt QTY's do not match IV QTY's

101

Quantity for specific Transaction Location in IV00102 (RCRDTYPE = 2)

2

Receipt QTY's do not match IV QTY's

102

Quantity for ALL Transaction Location in IV00102 (RCRDTYPE = 1)

2

Average Item receipt QTY on hand does not equal IV QTY on hand

103

2

Receipt layer QTY is 0, but not marked as sold

104

Incorrect RCPTSOLD field in IV10200.

QTYRECVD = QTYSOLD

and RCPTSOLD = 0

2

Receipt layer QTY is not 0, but is marked as sold

105

QTYRECVD <> (QTYSOLD + QTYRESERVED)

and RCPTSOLD =

and PCHSRCTY <> 4

2

Layer is not an override and QTY sold is greater than QTY received

106

QTYSOLD > QTYRECVD

and PCHSRCTY <> 4

2

Invalid purchase receipt type

107

PCHSRCTY not in (0,1,2,3,4,5,6,7,8)

2

Serial/Lot tracked item's receipt layer adjusted unit cost does not equal unit cost

108

b.VCTNMTHD in (1,2)

and b.ITMTRKOP <> 1

and a.RCPTSOLD = 0

and a.ADJUNITCOST <> a.UNITCOST

2

Item Type not "Sales/Invoice or Discontinued

111

ITEMTYPE not in (1,2)

2

Open layer exists in purchase receipts table where valuation method not stamped

112

QTYRECVD <> QTYSOLD

and VCTNMTHD = 0

and PCHSRCTY <> 4

2

Item is on open PO Return

113

Item is on an open po number in POP10300

WHERE POPTYPE in (4,5,6,7)

1

Account not found

109

ACTINDX = 0

1

Account type Profit and Loss

110

Account PSTNGTYP = 1 in GL00100

 

SEE99999 Fields:

  • ITEMNMBR: Item Number
  • LOCATNID: Location ID
  • ERMSGLVL: Error Message Level
  • ERRDESCR: Error Message Description
  • ERMSGNBR: Error Message Number
  • USERID: User ID
  • DATE1: Execution Date
  • TIME1: Time Stamp
  • DEX_ROW_ID: Dex Row ID

 

2- SEE99998 | HITB Staging Table

HITB reset tool will record the Inventory balance brought forward in this table as a staging area before being populated to SEE30303. The balances are primarily retrieved from Purchase Receipt Layer (IV10200) .

Therefore, all records will have a fixed value of "BBF" (Balance Brought Forward) within the HSMODUL (Host Module) field.

 

SEE99998 Fields:

  • ITEMNMBR
  • LOCNCODE
  • DOCDATE
  • GLPOSTDT
  • DOCTYPE
  • DOCNUMBR
  • RCTSEQNM
  • HSTMODUL
  • VCTNMTHD
  • QTYTYPE
  • PCHSRCTY
  • RCPTNMBR
  • VENDORID
  • TRXREFERENCE
  • IsOverrideReceipt
  • DECPLQTY
  • DECPLCUR
  • IVIVINDX
  • LOCIVIVINDX
  • TRXQTY
  • TRXQTYInBase
  • VARIANCEQTY
  • UOFM
  • UNITCOST
  • EXTDCOST
  • CRDTAMNT
  • DEBITAMT
  • DEX_ROW_ID

SEE99997 | HITB Reset Tool Steps

This table records the steps through which the HITB Reset Tool accomplishes. There are primarily six steps as shown below;

Helping Now:

One of the quality checks that are performed in order to ensure that the HITB Reset tool has been implemented correctly is to find all steps recorded in this table (as log details).

SEE99997 Fields

  • SEQNUMBR: Sequence Number
  • Step: Step
  • STRTDATE: Start Date
  • STRTTIME: Start Time
  • Status: Status
  • BACHNUMB: Bach Number
  • ReconStat: Reconciliation Status
  • ERRSTATE: Error Status
  • DEX_ROW_ID:Dex Row ID

Step    Step Description

10      Run Reconcile

20      Data Integrity Checks

30      Populate HITB Staging Table

40      Create GL Clearing Transactions

50      Create GL Transactions and HITB Records

60      Finalize IV Reset

 

Best Regards,

Mahmoud M. AlSaadi

10 comments:

  1. Hi Mahmood,
    thank you very much for the post. Can you please tell me how to solve this "Account not found" problem.I have many items with this warning.
    i am unable to populate table in HITB. on which table is this ACTCINDX=0 ?

    ReplyDelete
  2. Hi Mahmood,
    thank you very much for the post. Can you please tell me how to solve this "Account not found" problem.I have many items with this warning.
    i am unable to populate table in HITB. on which table is this ACTCINDX=0 ? -

    ReplyDelete
    Replies
    1. Would you please provide further explanation ?

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  3. hi mahmood,
    actually this is my issue:
    https://community.dynamics.com/gp/f/32/p/186201/467326#467326

    ReplyDelete
  4. I did check the account for items in cards->inventory--> items-->item account mainenance....
    -- Both inventory and inventory offset account exists for the items.
    :(

    ReplyDelete
    Replies
    1. I believe there is a specific transaction which is missing the correct inventory account. As an alternative, go to the posting setup window and provide a general inventory account to be used by the Reset utility temporarily.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  5. Dear MOHAMMAD,
    please help me with this error, i have follow the steps and correct the errors but while Populate HITB Staging Table i have got this error.
    [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column 'VARIANCEQTY', table 'TLTST.dbo.SEE99998'; column does not allow nulls. INSERT fails.*/

    ReplyDelete
  6. hi Mohammad
    problem solved and finalized the process but i could not fined the report in reports>>inventory>>activity
    what could be the reason?

    ReplyDelete
    Replies
    1. It seems you have not completed the process successfully :)

      Best Regards,
      Mahmoud M. AlSaadi

      Delete