What data table is feeding this inquiry screen?

[kphu]

HI,

I'm not sure if its called a report or what but I would like to know what table and fields within SBT is feeding the 36 Month sales/usage Inquiry screen. To get to the screen you open inventory control then "Maintain" -> Inventory items -> Inventory Master. From there you select an item. Any item then hit Inquiry button and select "36-Month Sales/Usage" from the Inquiry Type dropbox.

From there is displays the Usage History for "Item # you selected" and shows the previous Usage or Sale for the item.
I'd like to know what table\fields is populating the Qty Use column.

Any help would be much appreciated!

Thanks,

Ken

 

[kryton151]

Ken,

If you didn't figure this one out by now you should be able to find that data in the ICHIST?? table where the ?? is the company number.

-Adam

 

[mandaragit]

Hi ken,

I agree w/ Adam, ichist will helps. But if you want to see details of all your item movement, try to consider the stockcard datafile (icytrn?? and ictran??). These files have all the information you need...

 

[mlefkowitz]

If your version of SBT/ACCPAC/Sage Pro is 7.4 or earlier, it is indeed ICHISTnn.DBF where "nn" is the company number. If it is 7.5 or newer, it is ICISUM. Sage moved the history data from ICHIST to ICISUM.

The structure of these tables is quite different. The ICISUM is structured much more reliably than the ICHIST table.

If you have ICHIST and the numbers are incorrect, consider buying my MAKEHISTORY for SBT/ACCPAC/Sage Pro. I wrote it because these numbers are unreliable. It recalculates inventory (and customer and vendor) history totals from original documents. The numbers in these tables are only reliable if users close periods on time each period which, in my experience, rarely happens. The newer tables (ICISUM, ARCSUM and APVSUM) do not rely on human beings to remain accurate.

-Matthew Lefkowitz
Lefkowitz Systems, Inc.

http://www.lefkowitzsys.com

 

[kphu]

Hi Everyone,

Thanks for the info. I kind of let this task slip for other tasks that were more important.

Appreciate the info never the less.

Thank You,

Ken

 

[SBTBILL]

It might not be a DBF. After either 6.5 or 7.0 don't remember which it was possible to use either DBF's or SQL depending on what you selected. The table names were the same however.

ictran - current line item file
icytrn - historic line item file

artran/sotran - current line item sales
armast/somast - current order header file
arytrn/soytrn - history
arymst/soymst - history

 

[mandaragit]

It ProSeries 5.0i, the dbase file of 36month sales/usage is ichist??.dbf

Example:
If the accounting current period of sbt is June 2012, then the query will look like this...

Pds Past end date Sales %Prior qtysls % prior Period ASP
(column1) (column2) (column3) (column4) (column5) (column6) (column7) ===>only my naming column
12 5.31.11
11 6.30.11
.
.
3 3.31.12 250,998.00 181
2 4.30.12 199,464.00 79 144 79 1385.16
1 5.31.12 182,069.00 91 132 91 1379.31

Total (12-1) = total sales of column3 =total sales in column5 =total_amt/total_qty
Avg of Pds (12-1) = Total/12 (amt) =total /12 (qty)

where:
column source of data/fields in ichist??.dbf
column1 = the 12month period
column2 = end date sales of the month starting from last closed sbt date
column3 = ichist??.sls (express in sls1,sls2,sls3...slsnth)
column4 = [ichist??.sls1 / ichist??.sls2] * 100 or in given sample will be [182,069/199,464]*100 = 91

column5 = ichist??.sqty (express in sqty1,sqty2,sqty3...sqtynth)
column6 = [ichist??.sqty1 / ichist??.sqty2] * 100 or in given sample will be [132/144]*100 = 91
column7 = ichist??.sls1/ichist??.sqty1

 

[SBTBILL]

Basically IChist is created as totals from the base tables. There are similar tables in several SBT aps.

I would agree that the screen being discussed is unreliable. If there are any mods it is almost always ignored as well.

 

[mandaragit]

On my second post, it only answer what's ken is asking about in a 36months sales/usage inquiry... But on my first post, I am not recommending it. Ichist??.dbf is a summary data and there are a lot of issues questioning the integrity of that figure. By normal SBT recording, this is true. But if incomplete process of recording(during saving of entries) is noted due to system hang-up, technical network problem, power failure or even programmers/user's intervention of someone in the IT/MIS department in SBT databases and others beyond SBT control, that makes this file unreliable... The provision to SBT system to Recalculate balances might help but still I am not recommending it as the primary source of information and even me and the rest of the guys are not using that table. Instead, I am recommending and mentioned in my first post to use the stockcard datafile, ictran??.dbf and icytrn??.dbf. This table handles everything happening in the inventory such as sales, detailed AR adjustment, purchases, stock transfer, inventory issuance and others. The fieldname "applid" will categorized the source of transaction and there are also a provision that record the selling price, if transaction originated in AR module, and the cost of merchandise inventory.

With ken's query, the somast??.dbf, sotran??.dbf and others from SO module has nothing to do with this. Basically because, module from SO is a temporary transaction and will be counted only as long as releases to Receivable. Although, this is one source of Receivable, (for us this is the main source of our receivable), but still waited to ship or post to mark it official transaction and record it in the sales book.

SBTBILL is direct to the point... But he always have maintain a very nice judgment in every post that he made about issues of SBT system...

FarEast...

 

[mlefkowitz]

There is no way to correct ICHIST with standard features in SBT Pro 5.0. The "Recalculate Application Balances" feature in System Manager does not touch ICHIST (or ARCHST or APVHST, the analogous history tables for customers and vendors, respectively).

The periodic totals in these three tables are set whenever the user decides to close the period for IC, AR and AP, respectively. If the user forgets to close one of these periods, the numbers are crap.

The periods are stored in the first record in these tables in julian dates. It's crazy. Breaks all of the rules of data normalization. Sage finally corrected this nonsense in Pro 7.5 when they moved to the ICISUM, ARISUM and APISUM tables which have normal data structures.

The only way to recalculate these numbers in SBT Pro 5.0, outside of a bunch of complicated manual queries, is my Make History Pro. I wrote this program because the history numbers in SBT for these three tables are unreliable.

http://www.lefkowitzsys.com/products/enhancements/lefkowitz-acccounting-modifications-sage-pro/

Matthew Lefkowitz
Lefkowitz Systems, Inc.
415/657-9900
mlefkowitz@sprintmail.com

http://www.lefkowitzsys.com

 

[mandaragit]

Excellent remarks mlefkowitz... Even in my own, I have a lot of customization to enhance SBT reports. Basically, I am not using the standard report of SBT to show company's assets and liabilities... But I maintain the SBT financial statement reports such as Trial balance, Profit & loss, Balance Sheet and General ledger report as well. The rest of reports such as various customized format of sales report, collection reports, statement of accounts, inventory reports, purchases and our payables are all extracted in our external reports. But before hand, I have various created audit trail program that helps a lot to maintain the maximum confident level of our accountant that the reports are intact and correct. Audit trail on various SBT standard modules and the one that applied the so-called Generally Accepted Accounting Principle (GAAP) to maximized the efficiency of accounting reports.

Pro Series 5.0i, is one of the best accounting system during the time of its original release. And even now our SBT is still very good. The technology of its purging idea makes SBT maintain the remarkable performance with respect to speed, processing transactions in a network-based environment working on a 14yrs old sbt databases as of June 2012. With the help of my department, limitation of SBT is not a hindrance to make accounting reports compliance to the existing taxation system in my country.

FarEast...

 

[mlefkowitz]

There are a lot of Pro 5.0 systems still running well out in the wild. I support a dozen of them or so. Most have heavy customizations which make them difficult to upgrade or replace. All run very fast, not because of the archiving feature in SBT, but because computers are so much faster today than they were in the late 1990s when SBT Pro 5.0 was released.

Why did you write your own balance sheet and income statement reports? The standard ones in SBT seem perfectly fine to me.

If you need custom financial reports, consider F9 from Infor. It's an Excel add-on that reads GL data from SBT directly. You can make custom financial reports that refresh automatically with current data (no manual export/import of data). It was (and is) the default financial reporter for those whose SBT/ACCPAC/Sage Pro financial reporting requirements are greater than what comes out of the box.

Matthew Lefkowitz
Lefkowitz Systems, Inc.
415/657-9900
mlefkowitz@sprintmail.com

http://www.lefkowitzsys.com

 

[mandaragit]

The SBT Financial Statement report is so far very good. That's why until now, I maintain those reports. There are other reports that management wanted too, to help them analyzed their marketing strategy at their own convenient report format. Customizations are everything in the SBT modules except the financial reports. For accounting department, standard SBT FS is enough and informative...

FarEast...

 

[mlefkowitz]

Yes, the report writer for GL and Payroll is quite different than the report writer used in the other modules. SBT was trying to move the logic from code to data but, in my view, the result is worse than the old-style reports. The reports are impenetrable. If you like them as they are, great. If you want something different, you're out of luck. Go with F9.

Matthew Lefkowitz
Lefkowitz Systems, Inc.
415/657-9900
mlefkowitz@sprintmail.com

http://www.lefkowitzsys.com