list of tables and schema of IFS ERP database

Hi ,

you can see all tables with this query.

select * from All_all_tables

Hi @Mustapha_Deublin,

All the database objects related to IFS Applications are deployed as the Application Owner user(usually ‘IFSAPP’). So you can use the following queries to fetch the tables and views.

• SELECT * FROM dba_views WHERE owner='IFSAPP'; 
• SELECT * FROM dba_tables WHERE owner='IFSAPP'; 

Objects related to Information Access Layer are deployed under IFSINFO user, so you can change the owner in above queries to retrieve those. 

Hope this helps!

List of tables and views can be viewed by querying DBA_VIEWS and DBA_TABLES.

SELECT * FROM DBA_VIEWS WHERE owner IN (‘IFSAPP’,’IFSINFO’);

SELECT * FROM DBA_TABLES WHERE owner IN (‘IFSAPP’,’IFSINFO’);

The key constraints and the cascade relationship can be viewed by querying CONSTRAINT tables.

SELECT constraint.TABLE_NAME,

       constraint.R_CONSTRAINT_NAME,

       contraint_column.TABLE_NAME,

       contraint_column.COLUMN_NAME

  FROM USER_CONSTRAINTS constraint,

       USER_CONS_COLUMNS contraint_column

 WHERE constraint.R_CONSTRAINT_NAME = contraint_column.CONSTRAINT_NAME

   AND constraint.constraint_type = 'R'

 ORDER BY constraint.TABLE_NAME,

          constraint.R_CONSTRAINT_NAME,

          contraint_column.TABLE_NAME,

          contraint_column.COLUMN_NAME;

There is no foreign keys in IFS. Relationship is based on entries from reference_sys_tab which are comming from view columns comments. One exception is ROWKEY colum when You define custom objects.

Hi Mustapha,

wellcome to my world! :-)
The list of available tables and views are easily fetched with the sql commands mentioned above.
I would like to recommend to start your own “data dictionary” (DD) with analyzed objects and the use of them in IFS masks and processes.
We did a lot of Powerpoint documentation work but know we just add the IFS Screenshots in our own DDs.

We did a lot of “field work” and connected hundreds of tables/views within a datawarehouse on MS SQL Server. Some connections are very simple, some highly sophisticated.
Just give me a call (teams) or a mail. We would be glad to share our knowledge.

Good luck with your work.

Regards
Michael

Hello, Michael, can you share your excel workbook with me so that I can list the tables to be used

Thanks

Bon jour fdieud,

which part of IFS you are expecially interested in?

Sales, purchase, finance, manufacturing, quality management, HR, logistics?

We can give you a list of tables from each “module” to start with.
Just mention the “module-name”.

Sharing the excel-workbook isn’t that easy because of customer related data in it. 😊

But it should be very easy to create a datadictionary (e.g. sales or purchase) for your company within hours.

All the best

Michael

I Think i need Sales for check project's invoces currency

Hi fdieud,
very quick & dirty 😀

BUSINESS_OPPORTUNITY_LINE_TAB
BUSINESS_OPPORTUNITY_TAB
BUSINESS_OPPORTUN_HISTORY_TAB
BUSINESS_REP_GROUP_TAB
BUSINESS_REP_GROUP_MEMBER_TAB
BUS_OBJ_REPRESENTATIVE_TAB
COMPANY_PERSON_TAB
COMPANY_TAB
COMPANY_SITE_TAB
CRM_CUST_INFO_TAB
CUST_HIERARCHY_STRUCT_TAB
CUST_INVOICE_ITEM_DISCOUNT_TAB
CUST_ORD_CUSTOMER_ADDRESS_TAB
CUST_ORD_CUSTOMER_CFT
CUST_ORD_CUSTOMER_TAB
CUSTOM_ENUMERATIONS
CUSTOM_FIELD_ENUM_VALUES
CUSTOMER_DELIVERY_TAX_INFO_TAB
CUSTOMER_INFO_ADDRESS_TAB
CUSTOMER_INFO_ADDRESS_TYPE_TAB
CUSTOMER_INFO_TAB
CUSTOMER_ORDER_CHARGE_TAB
CUSTOMER_ORDER_HISTORY_TAB
CUSTOMER_ORDER_LINE_TAB
CUSTOMER_ORDER_TAB
DEDUCTION_GROUP_TAB
DEDUCTION_RULE_TAB
HANDLING_UNIT_TAB
IDENTITY_PART_INFO_TAB
IDENTITY_INVOICE_INFO_TAB
IDENTITY_PAY_INFO_TAB
INVENTORY_PART_TAB
INVENTORY_PRODUCT_FAMILY_TAB
INVOICE_DISC_TAB
INVOICE_ITEM_TAB
INVOICE_PARTY_TYPE_GROUP_TAB
INVOICE_SERIES_TAB
INVOICE_TAB
INVOICE_TYPE_TAB
ISO_COUNTRY_TAB
ISO_CURRENCY_TAB
MPCCOM_SHIP_VIA_TAB
ORDER_COMMISSION_TAB
ORDER_COORDINATOR_TAG
ORDER_DELIVERY_TERM_TAB
ORDER_QUOTATION_LINE_TAB
ORDER_QUOTATION_TAB
PART_CATALOG_CFT
PART_CATALOG_TAB
PART_HANDLING_UNIT_TAB
PAYMENT_TERM_TAB
PERSON_COMPANY_ACCESS_TAB
PERSON_INFO_TAB
PERS_TAB
REBATE_TRANSACTION_TAB
RETURN_MATERIAL_REASON_TAB
SALES_CHARGE_TYPE_TAB
SALES_GROUP_TAB
SALES_MARKET_TAB
SALES_PART_TAB
SALES_PRICE_GROUP_TAB
SALES_REGION_TAB
SHIPMENT_FREIGHT_CHARGE_TAB
SHIPMENT_LINE_TAB
SHIPMENT_TAB

If you want to do some “research” by yourself:
1. Start with SystemInfo in the IFS Mask.
2. mark one field in the mask. Look for Viewname on the left side of SystemInfo.
3. Be aware: mark field in header will result in other View-Names than mark field in Lines.
4. Do a check in ORACLE Tool. Look for View-Name.
5. Analyze ORACLE View Definition. (You can do this with different tools)
6. Note the underlying tables, the view is using.
7. Note the API-functions.
    Some of them are easily “rebuild” by joins others are not that easy.
8. Especially check the Join-criteria in the IFS View.

But, if you look especially for the Currency - field. This is simple: INVOICE_TAB.CURRENCY
Try select distinct currency from invoice_tab
 

I’m not shure because my customer doesn’t use project invoices.

I recommend to “select distinct rowtype from invoice_tab”
If there is a rowtype “ProjectInvoice” just use this as a filter.

We learned that a lot (Or all of them? I’m not quite shure at the moment!)
of invoices (purchase invoices as well) are stored in the INVOICE_TAB and are just filtered for the specific mask.

Hope this helps.

All the best

Michael

Hi i’m not an administrator so i havent accès to Oracle Tools

Can you tel me why that requiest responde bad index column

Im using a Excel sync in VBA language

 "SELECT * FROM APP_FOR_PAYMENT WHERE PROJECT_ID = '" & id_project

Hi fdieud,

can you use the SQL within IFS?
When you tell me, you use Excel for creating SQL syntax, than there must be some “link” to ORACLE.

I just checked APP_FOR% within Views and Tables in the IFS database.
Couldn’t find any object.
Please try SELECT * FROM APP_FOR_PAYMENT first.
My guess:
You need a closing ‘ !!!

So add & “’” : this is one ‘ marked by two “ as a text field.

I would try:

 "SELECT * FROM APP_FOR_PAYMENT WHERE PROJECT_ID = '" & id_project & “ ‘ “

Without the blanks I just added that you can see the single ‘.
There is no blank between your first ‘

  = '"

and the “!

All the best.

Michael

Hello, Michael,

can you please share your excel workbook with me so that I can list the tables to be used.

I am for now mostly interested in Sales & Purchase.

Kind regards,

Arjen

Hi Arjen,

I would like to share the datadictinary with you but there are some customer related data in it.
So it is not possible to just copy & paste it.

The effort of stripping or masking personal data is much higher than creating a new datadictionary
from the scratch on base of your IFS system.

But I can share the list of tables we used in the purchase area.
 

Kind regards

Michael

TabellenName
ACCOUNTING_GROUP_TAB
ASSET_CLASS_TAB
COMPANY_TAB
COMPANY_SITE_TAB
CORPORATE_FORM_TAB
DELIVERY_CONTROL_CODE_TAB
EXTERNAL_SERVICE_TYPE_TAB
IDENTITY_INVOICE_INFO_TAB
INVENTORY_PART_CHAR_TAB
INVENTORY_PART_TAB
INVENTORY_PRODUCT_CODE_TAB
INVENTORY_PRODUCT_FAMILY_TAB
INVOICE_ITEM_TAB
INVOICE_MESSAGE_DEFAULTS_TAB
INVOICE_PART_TYPE_GROUP_TAB
INVOICE_PROPERTY_CODE
INVOICE_TAB
ISO_COUNTRY_TAB
ORDER_DELIVERY_TERM_TAB
ORDER_PROC_TYPE_EVENT_TAB
ENG_PART_MAIN_GROUP_TAB
PART_CATALOG_TAB
PARTY_TYPE_ID_PROPERTY_TAB
PAYMENT_TERM_TAB
PERSON_INFO_TAB
PRE_ACCOUNTING_TAB
PUR_ORD_LINE_EXT_TAB
PURCH_ADDITIONAL_COST_TYPE_TAB
PURCH_MILESTONE_TEMPL_TAB
PURCHASE_BUYER_TAB
PURCHASE_CHARGE_GROUP_TAB
PURCHASE_CHARGE_TYPE_TAB
PURCHASE_CODE_TAB
PURCHASE_DOCK_CODE_TAB
PURCHASE_ORDER_CHARGE_TAB
PURCHASE_ORDER_INVOICE_TAB
PURCHASE_ORDER_LINE_TAB
PURCHASE_ORDER_TAB
PURCHASE_PART_GROUP_TAB
PURCHASE_PART_TAB
PURCHASE_REQ_LINE_PART
PURCHASE_REQ_LINE_TAB
PURCHASE_REQUISITION_TAB
PURCHASE_REQUISITIONER_TAB
PURCHASE_SUB_DOCK_CODE_TAB
RECEIPT_INFO_TAB
RECEIPT_INV_LOCATION_TAB
RECEIPT_RETURN_TAB
RECEIPT_SCRAP_TAB
SALES_PART_TAB
SUPPLIER_TAB
SUPPLIER_ADDRESS_TAB
SUPPLIER_ASSORTMENT_TAB
SUPPLIER_BLANKET_TYPE_TAB
SUPPLIER_BR_TAX_INFO_TAB
SUPPLIER_DELIVERY_TAX_CODE_TAB
SUPPLIER_DOCUMENT_TAX_INFO_TAB
SUPPLIER_GROUP_TAB
SUPPLIER_INFO_ADDRESS_TAB
SUPPLIER_INFO_ADDRESS_TYPE_TAB
SUPPLIER_INFO_CONTACT_TAB
SUPPLIER_INFO_MSG_SETUP_TAB
SUPPLIER_INFO_OUR_ID_TAB
SUPPLIER_INFO_TAB