Manex has over 400 dynamic tables, so it would be very difficult for us to continually update/maintain these.  So we will not be providing any table listings and relationship information in a standard format.   What we have done and can continue to do is have users let us know which specific table they are looking for and what they are trying to create so we can give some information related to that area

As requests arise, we will create a cursor that captures some data from the related files. If users have any knowledge of queries, they should be able to add or subtract information they might need or not need from the basic outline. 

In addition the below suggestion might help you find specific Table/fields you desire in your report:

Question:

What table(s) do I find the relationship between a Parent Assembly P/N and its Component P/Ns?  Put another way, how can I determine which Parent Assembly P/N(s) a Component is used on?  What is the linking data element?

How can I see which Parent is driving the need for an MRP required quantity such as the information displayed on top portion of the MRP Demands and AVL screen?

Suggested Resolution:

Once you find the uniq_key of your subject component, search the BOM_DET table uniq_key for that same field. The BOMPARENT field associated with the Uniq_key will be the Uniq_key for the parent part.

Finding the AVL is a different matter. First, you have to check the parent part in the inventor table to see if a customer number is associated with it. This is in the BOMCUSTNO field. If there is, then check the avl of the consigned component for that customer. If it isn’t associated with a customer, then check the internal AVL for the component.

*--enter your assy # and revision here
lcPart_no='910-1004                 '
lcRevision='A   '

*-- This gathers the consigned part numbers and AVL for the BOM

This SQL will just find all the items from inventory, which are not used as a BOM components. The SQL is not tested and might have typos.   

The EmailLog Table is available within the DBFS directory that users should be able to use to create a quick and simple Custom report in order to make their records of emails sent out of the ManEx system more viewable.  If users have Crystal Reports software, they should then be able to group/sort the email log as desired.  For example:  Group on the creportnm would group all Packing List related emails together.  Or, if they sorted per cuserinit you could see all emails sent out of the system per a particular user, etc. . . .

CLOSE DATABASES all
CLEAR

*pn = "134-0000450"

USE invtmfgr ORDER tag uniqmfgrhd
BROWSE LAST nowait

SELECT 0
USE invtmfsp ORDER tag uniqmfgrhd
BROWSE LAST nowait

SELECT 0
USE invtmfhd ORDER tag uniq_key
SET RELATION TO  uniqmfgrhd INTO invtmfgr
SET RELATION TO  uniqmfgrhd INTO invtmfsp additive

BROWSE LAST nowait

SELECT 0
USE invt_isu ORDER tag uniq_key
BROWSE LAST nowait

SELECT 0
USE invt_rec ORDER tag uniq_key
BROWSE LAST nowait

SELECT 0
USE inventor ORDER tag part_no
SET RELATION TO uniq_key INTO invtmfhd
SET RELATION TO uniq_key INTO invt_isu addi
SET RELATION TO uniq_key INTO invt_rec addi

*SEEK pn
BROWSE LAST nowait

** or....
SELECT inventor.part_class, inventor.Part_type, inventor.Part_no, inventor.Revision, ;
  inventor.custno, inventor.custpartno, inventor.descript, inventor.status, inventor.stdcost, ;
 Invtmfhd.partmfgr, Invtmfhd.Mfgr_pt_no, ;
 invtmfgr.whno, invtmfgr.qty_oh, invtmfgr.reserved, ;
 invtmfsp.supid, invtmfsp.suplpartno ;
 FROM inventor, invtmfhd,invtmfgr,invtmfsp;
 WHERE inventor.Uniq_key = invtmfhd.uniq_key;
  AND invtmfhd.uniqmfgrhd = invtmfgr.uniqmfgrhd;
  AND invtmfsp.uniqmfgrhd = invtmfhd.uniqmfgrhd;
 ORDER BY part_no,revision,partmfgr,whno;
 INTO CURSOR partslist
 
BROWSE LAST nowait

 The Manufacturer name and part number are contained in the INVTMFHD table. Records in this table are linked to the internal part number records in the INVENTOR table by the uniq_key.

 The Supplier information is kept in the INVTMFSP table, and is linked to the Manufacturer in the INVTMFHD table by the uniqmfgrhd field.

The unit price, lead time, minimum order quantity, order multiple, MRP Order Policy Code are all in the INVENTOR table.  We do not have a field for currency.

from inventor;

INNER JOIN invtmfhd ON inventor.uniq_key = invtmfhd.uniq_key;

inner JOIN invtmfgr ON invtmfhd.uniqmfgrhd = invtmfgr.uniqmfgrhd;

inner JOIN warehous ON invtmfgr.uniqwh = warehous.uniqwh;

left OUTER JOIN invtser ON invtmfgr.w_key = invtser.id_value;

WHERE inventor.status = 'Active';

AND invtmfhd.is_deleted <> .t.;

and invtmfgr.is_deleted <> .t.;

AND qty_oh <> 0.00;

AND serialyes = .t.

Here is the sequel statement that generates the information in the PO History button from the inventory page 2 screen.  The uniq_key is the field identifying the part you are looking for.  In this sequel, records are gathered up to ten records. You can change the sequel if you would like less records.

Response:

ManEx is available upon request to provide simple queries.  But when it involves MRP demands, that is anything but simple.  The method in which the ManEx system compiles the MRP information is very complex with 1000’s of lines of codes that the system goest through to provide the correct demand for the parts within the system.  Every file used by Inventory, Purchasing, Work Orders, Kitting, Sales orders and Forecasts are used in the MRP Generation. All information from each file is gathered and incorporated in the tables beginning with “MPS” and “MRP”. The data in these tables are further managed by logic rules that are very complex relationships.

ManEx does not have the resources to in providing the users with the Possible 1000’s plus line of SQL statements that would ben needed to get the end results of MRP, but if the nature of the information sought after is explained in further detail, there is the possiblity that more specific table/releationships can be provided.   

You may find partial answers in the tables mrpsch2 and mpract. 

*seepos
clear
close all
set safety off
public m.pono
m.pono = space(10)

@ 12,12 SAY "ENTER Purchase Order :" GET m.pono
READ
m.pono = padl(alltrim(m.pono),15,"0")
? m.pono
* CLEAR
 
SET STEP ON

use pomain order tag ponum in 0
use poitems order tag ponum in 0

sele pomain
set relation to ponum  into poitems

seek m.pono

brow last nowait

use poitschd order tag uniqlnno in 0
use porecdtl order tag uniqlnno in 0
use porecloc order tag uniqdetno in 0
use poreclot in 0
use podock order tag uniqlnno in 0
use porecmrb order tag DMR in 0
use invtmfgr order tag uniqmfgrhd in 0
USE INVENTOR ORDER TAG UNIQ_KEY IN 0
use invtlot order tag w_keylot in 0
use invt_rec order tag uniqmfgrhd IN 0

sele poitems
set relation to uniqlnno into poitschd
set relation to uniqlnno into porecdtl additive
set relation to uniqlnno into podock additive
SET RELATION TO UNIQ_KEY INTO INVENTOR ADDITIVE
set relation to uniqmfgrhd into invtmfgr addi
set relation to uniqmfgrhd into invt_rec addi
sele invtmfgr
set relation to w_key into invtlot
sele poitschd
set relation to uniqdetno into porecloc
sele porecdtl
set relation to transno into porecmrb

sele poitschd
brow last NOWAIT
sele porecdtl
brow last NOWAIT
sele porecloc
brow last NOWAIT
sele podock
brow last NOWAIT
sele poitems
brow last NOWAIT
sele porecmrb
brow last NOWAIT
SELE INVENTOR
BROW LAST NOWAIT
SELE INVtmfgr
BROW LAST NOWAIT
SELE invtlot
brow last nowait

SELECT Custname, Qainsp.Wono, Part_no, Revision, LotSize, InspQty, FailQty, PassQty, Yield, Dept_name, Date, Qaseqmain AS QALink ;
 FROM Qainsp, Woentry, Inventor, Customer, Depts ;
 WHERE Inventor.Uniq_key = Woentry.Uniq_key ;
 AND Woentry.Custno = Customer.Custno ;
 AND Qainsp.Wono = Woentry.Wono ;
 AND Qainsp.Dept_id = Depts.Dept_id ;
 AND Date >= dStart AND Date <= dEnd ;
 AND Qainsp.Dept_id IN (SELECT Dept_id FROM ZD) ;
 AND Woentry.Custno IN (SELECT Custno FROM ZCu) ;
 AND Qainsp.Wono IN (SELECT Wono FROM ZW) ;
 ORDER BY Date, Custname, Part_no ;
 INTO CURSOR ZYieldDt

  Qadefloc.LocQty, Qadefloc.Location, Qadef.DefDate, Depts.Dept_name, Depts.Number, Qadef.Wono ;
 FROM Customer, Inventor, Qadef, Support, Qadefloc, Woentry, Depts ;
 WHERE Customer.Custno = Woentry.Custno ;
 AND Woentry.Uniq_key = Inventor.Uniq_key ;
 AND Woentry.Wono = Qadef.Wono ;
 AND Qadef.Locseqno = Qadefloc.Locseqno ;
 AND Qadefloc.Def_code = LEFT(Support.Text2,10) ;
 AND Support.Fieldname = "DEF_CODE  " ;
 AND (DefDate >= (dStart) AND DefDate <= (dEnd)) ;
 AND Woentry.Custno IN (SELECT Custno FROM ZC) ;
 AND Qadefloc.ChgDept_id = Depts.Dept_id ;
 AND Qadefloc.ChgDept_id IN (SELECT Dept_id FROM ZD) ;
 ORDER BY CustName, Depts.Number, Part_no, Revision, Def_code ;
 INTO CURSOR Zqaloc

SELECT woentry.custno, woentry.wono, woentry.openclos, woentry.due_date, woentry.bldqty, woentry.balance, ;

       woentry.reledate,;

      inventor.parT_no, inventor.revision, inventor.descript, ;

       woentry.sono,due_dts.due_dts as SOdueDt,due_dts.commit_dts as SO_commdt,due_dts.qty as SOqty;

      FROM woentry,inventor,due_dts;

      WHERE openclos <> "Closed" and openclos <> "Cancel";

            and inventor.uniq_key = woentry.uniq_key;

            AND due_dts.uniqueln = woentry.uniqueln;

      ORDER BY woentry.wono;

      INTO CURSOR wostuff

BROWSE LAST nowait

** work center locations

SELECT wostuff.wono,dept_id, curr_qty;

      FROM wostuff, dept_qty;

      WHERE wostuff.wono = dept_qty.wono;

      ORDER BY wostuff.wono,dept_id;

      INTO CURSOR whereitis

BROWSE LAST nowait

** wo shortages

SELECT distinct wostuff.wono,kamain.dept_id,kamain.shortqty,;

      inventor.part_no, inventor.revision, kamain.uniq_key;

      FROM wostuff, kamain, kadetail, inventor;

      WHERE wostuff.wono = kamain.wono;

            AND kamain.kaseqnum = kadetail.kaseqnum;

            AND kamain.uniq_key = inventor.uniq_key;

            AND kamain.shortqty > 0;

      ORDER BY wostuff.wono,inventor.part_no;

      INTO CURSOR whatitis

BROWSE LAST nowait

SELECT WHATITIS.WONO, WHATITIS.PART_NO, WHATITIS.SHORTQTY, poitems.ponum, POMAIN.POSTATUS,  ;

      poitems.itemno,poitschd.balance, ;

      poitschd.schd_date, SUPINFO.SUPNAME ;

      from poitems, pomain, POITSCHD, SUPINFO, WHATITIS;

      where POITEMS.PONUM = POMAIN.PONUM;

            AND POITSCHD.UNIQLNNO = POITEMS.UNIQLNNO;

            AND POMAIN.postatus <> "CANCEL" ;

            AND POMAIN.postatus <> "CLOSED" ;

            AND POITSCHD.BALANCE > 0;

            AND SUPINFO.UNIQSUPNO = POMAIN.UNIQSUPNO;

            AND POITEMS.UNIQ_KEY = WHATITIS.UNIQ_KEY;

      ORDER BY WHATITIS.WONO, WHATITIS.PART_NO,POITEMS.PONUM,POITEMS.ITEMNO,POITSCHD.SCHD_DATE;

      INTO CURSOR WHENISIT

BROWSE LAST NOWAIT