Hi Everyone, my name is Michael Kaiser and I worked for IFS Germany from 1999 to 2001. On the very first day - in these days the fair “Systems” in munich was still very important - my swedish colleage gave me call from munich and told me: “Michael, we do not have anybody around who could show CRM (formerly known as IFS Sales & Marketing) and BI (in this times cognos series 7). Now you got your two new babies!!!” Zoooooom, from this day on, I was the specialist for these two modules and a very happy father. Within some months I digged deeper and deeper especially into BI because it was more fascinating than every other area I came across the 10 years before that. In 2001 I left IFS and did some CRM and some cognos BI projects mainly on IFS basis. We created STAS Control and were very focused on IFS customers. Within 2002 private and business turmoils happened. I got divorced, changed my flat and changed from cognos to Microsoft. Don’t ask me what was the bigger deal! From 2002 to th
Hi Marcelle, you are very kind! Do you believe in past lives? I didn’t believe in pastlives my last past live already! :-) So who knows, perhaps I lived more than 5 lives already? Do you know about the Debug Console? Shift+Ctrl+D. I work with this tool every day and “listen” to the commands the GUI is sending to the ORACLE database. Even when you are not familiar with databases, views and tables just think about the knowledge you gain, when you know what happens when you search for a customer, an order or you start a task with a rigth mouse button (RMB) task. You start to get a bigger view about the system and the way things correspond. At least it helps to ask better questions. I’m looking forward to hear about your questions and promise: There will be a lot of first “I don’t have any idea but…. very interesting question!” All the best, happy new year and if you want, just tell me more about you. regards Michael
Hi Binura, thanks for your answer and you are right: :-) I now about the strict way creating a QR! To change it, you must replace the view(s). I don’t have any problem with that. No quiet simply: Somebody shows me a ORACLE SQL script (could be a QR script but could be something else) What I’m asking for, is an elegant way to “know” from the given view which is the correspondig GUI mask. e.g: (rechercherd by debug console) CUST_ORDER_INV_HEAD_UIV_ALL → CustomerInvoice (single mask) CUSTOMER_ORDER_INV_JOIN → CustomerInvoiceLines (Overview mask) To go this way is very simple but boring and a lot of work to do. To get a list of all mask with the underlying structures “fetched” by the application is just days and days of stupid work. So because I’m thinking about a “compendium” for BI experts where you just enter the VIEW-Name and the documentation gives you information in which masks this view is used therefore I’m asking for “the other way around” :-) best regards Michael
Hi Nick, brilliant! Yes, this is the “missing link”. But you know, there is a saying in german: “Der Appetit kommt beim Essen!” ( Appetite comes with eating ) Now I know the connection between View and “Frame”. Is there a connection between navigation tree and Frame / GUI visible in ORACLE as well? What I’m planning to do: A compendium with all the navigation tree parts down to the last level with single data mask like customer or inventorypart or multi data “masks” like customerinvoicelines. Then imagine a list of known Quickreports organised within this compendium! I can imagine this must be some tool for all colleages creating QRs. But nevertheless: great answer and fun trying out. Thanks again!!! All the best Michael
Hi Callum, you’re a genius!!! Thanks for that brilliant script. It is exactly I was looking for. If you need information about BI, QR just let me know. Have a nice weekend. Michael
Hi Kristian,very tricky question! But a good one!First I like to mention that most of your requests are from totally different “datasources”(ORACLE Tables and Views). Some of them, you can combine (i.g. make Joins in ORACLE or MS SQL DWH Databases), some of them are not “joinable”.At least to my knowledge.All your data has something in common. A LotBachNo.I think your approach would be better if you split it into scenarios.So one scenario is:What kind of material/products/etc. are we buying? What part of these PurchaseParst are with or without a lotbatchno?This is your purchase part.From this on, you can go to the inventory part scenario and ask:Give InventoryPart with some or one specific lotbatchno: Where are these parts?By the way: we learned the hard way that using the data in INVENTORY_PART_UNIT_COST(_SUM) is not recommandable. You will find that mask under Warehouse Management - Financial Control - Inventory Part Unit Cost. On first glance it looks very “attractive” but it is not
Hi everyone,we came across the same solution and I just show the relevant joins.We prefix every IFS object in the DWH with extr_IFS to have a quick separation to let’s say extr_NAV_ or other datasource from the “old” ERP System. (also extr_xls extr_txt and so on)The transformed tables start with sql_xxSELECT ….,COIL.CATALOG_NO ,HIST.LOT_BATCH_NO--into tmp_sql_ifs_fact_invoice_linesFROM [extr_IFS_CUSTOMER_ORDER_INV_JOIN] as COILLEFT OUTER JOIN extr_IFS_CUST_DELIVERY_INV_REF as CDIRON COIL.INVOICE_ID = CDIR.INVOICE_IDAND COIL.ITEM_ID = CDIR.ITEM_IDand other Left outer joins…...…..LEFT OUTER JOIN sql_ifs_hlp_ITH_OESHIP as HISTON COIL.CONTRACT = HIST.CONTRACTAND COIL.ORDER_NO = HIST.SOURCE_REF1AND CO.LINE_NO = HIST.SOURCE_REF2AND COIL.CATALOG_NO = HIST.PART_NOAND CDIR.DELIV_NO = HIST.SOURCE_REF5
Hi Kristian,sounds good!I imagine, as long as you don’t need detailled cost information with the lot/batch, the lot/batch master should be sufficient.In this structure you should be able to ask top down: Manufactured lot/batch includes which parts?or button up: Where would or is my lot/batch be part of?Can you provide me with the initial questions and business cases?I’m in the BI business now since 1999 and “converting” more and more to a consultant helping people asking the right questions and align their goals e.g.CEO goals e.g. for purchase department,goals for the department leader (purchase),goals for purchaser (backlog lists of open POLs, etc. I have to do today )The last question is: What kind of report shows “are we on track or not?”But if I get your first post right the report is not for internal use but official sites like goverment, etc.Dangery goods, etc.My last customer has contracts with his customers which includes: If you change one part of the recipe you must inform yo
Hi Kristian,the german customer just finished the first phase of his BI project and we estimated roundabout 60 to 90 days of work for the next 6 months. We started with purchase and - so we hope - will proceed with sales and finance. I’m quite shure we will come across your issue as well. So let us stay in contact.The moment we have a script, a QR or something else, I will come back to you. You wrote:How much of the purchased lot/batch is still in our possession? And subsequently: Where is it?Who wants to know and why? :-) Is it “Finance”, the “inventory guys”, production, or is it sales with the very indirect question:Given the rawmaterial “on board” what are we able to produce the next months and when could I sell it?We changed our approach more and more:The last years we just created reports exactly they already looked like (based on the old ERP).These days we start with “top level” questions and even ask some provocative questions like:What will hapen, if you do not know? Will you
Hi Thomas,thank you very much. A very misleading description indead.I’ll talk to my customer.cuMichael
Hi,I would like to add some specific information about that issue.You know that “germans” are a little bit cracy about numbers and details.In this case our customer want to see all the discounts (from RMB View starting from CustomerOrderLine = COL) in the report, so I had to join at least 3 different kinds of discounts to the COL.To see the payment discount on a COL detail granulation, we not only joined with the payment_term_tab but also the payment_term_disc_tab (group by to use the maximum of possible discounts).So the idea is: If there is payment discount (Skonto), the customer will mostly use the max discount possible. For techs only :-)LEFT OUTER JOIN extr_IFS_PAYMENT_TERM_TAB AS PTON CS.COMPANY = PT.COMPANYAND CO.PAY_TERM_ID = PT.PAY_TERM_IDLEFT OUTER JOIN(SELECT COMPANY+'_'+ PAY_TERM_ID as PayTermKey,MAX(DISC_PERCENT) as DiscPercent FROM extr_IFS_PAYMENT_TERM_DISC_TAB group by COMPANY+'_'+ PAY_TERM_ID ) as PTDISo we can use the discount on a COL
Hi Callum,long time now, since I first used your script and it is still of great use.Now I came along an issue I cannot understand.Example:When you look for “Purchasing Analysis” you get the following window: When using your script I get:This looks quite (!) the same but not exactly!When I put in “Analysis - Purchase” into the IFS navigator it will not find a window.On the other hand if I search for the Text (and translation) every thing looks fine.The PROG_TEXT and the german translation will do fine when I search them in the navigator line.What I don’t understand:Why is the presentation objects (pres_object and pres_object_security) giving me a “Analysis - Purchasing” when the navigator is listening to “Purchasing Analysis”?Is there a “translation” in between?All the best to you and thank you for sharing. Michael
Hi folks,I stumbled over some more interesting issues last weekend.Looked into FND_NAVIGATOR and found the a classical parent-child-connection and all the information I need to “tag” a screenshot for - say - “Sales Quotation Lines”So with the script above I get the following information:Not only the underlying View but also the objects below the view.Now I can combine the presentation object (what the user is familiar with), the navigation “path” and the technical information (view + underlying tables).Because I have a metadata-analysis-tool which fetch all metadata from all (table) objects and store them in an excel-datadictionary, the documentation is quite enhanced and “round”.This datadictionary is the “interface” between me and the customer.All field “taged” as relevant are copied into the DWH and transformed to a Star-Schema and (if wanted) to a microsoft AS cube. (since last winter I work with an algorithm to decide which fields to select and excel macros doing the colouring (g
Hi Jens,that did the job! Brilliant!I tried to join the Active Directory Data but the displayname there and the internaldisplay from ifs do not match all the time.ü, ö, ä , missing commas and other stuff :-(Thank you!! all the best Michael
Hi Cindy,and what you perhaps want to measure:How many cases are answered within the first 60 minutes? 120 minutes? etc.We would like to build a quick model on this data scenario.Would be the first of his kind. :-) All the best Michael
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.Part of excel based DataDictonary “Purchase”.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.“Navigation” within the DD.Good luck with your work. RegardsMichael
Hello Maja,I’m not shure but guess the following.Light Cleanup job should run every 10 min = second job. It normally takes around 3-4 seconds.MV refresh background job every 60 min = first job.How long does it take? The moment your second job tries to read data from the MW, ORACLE need a “finished” MV.If the creation is still in process this could be a dead lock situation on database level.I suggest you talk to an ORACLE admin.If not possible try this workaround.Do not schedule the second job every 10 Minutes but every 15 Minutes and start 5 minutes before the first job is scheduled. So at least it got 9 Minutes to finish.By the way: what kind of data you are using for what kind of purpose?(“improve performance to display large amounts of data by using Materialized View (MV):”)All the bestMichaelPS:If you dig deeper into IFS you will find uge amounts of APIs creating data “on the flight”.For keeping an IFS mask filled with customer data, customer order, shop order, etc. absolutely fi
Hi Parsa,just want to check, if I’m right about the following.You start with a BO (related to one (!) customer or lead, but this must be a record in the customer area in IFS)You have different “sales channels” you call “opportunity channels”.Now you add another CustomerNo.A second and so on.Hmmm, this looks like you just want to “break” with the rule “one customer, one BO, one or more CustomerOrders, one or more CustomerInvoices”.But I can easily follow your way of thoughts:We do have a new product (line of products) and want to communicate that to as many customers as possible.Normally you should create one BO for one customer and this would be 1:1 as Elin mentioned.Just another idea?Could it be possible to “clone” one BO into a list of BOs with a given list of customers?You will stay in the BOL - OQL - COL- CIL line of data but you will have “semi - automated” creation of BOs.Good luck.Michael
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 bestMichael
Hi fdieud,very quick & dirty 😀 BUSINESS_OPPORTUNITY_LINE_TABBUSINESS_OPPORTUNITY_TABBUSINESS_OPPORTUN_HISTORY_TABBUSINESS_REP_GROUP_TABBUSINESS_REP_GROUP_MEMBER_TABBUS_OBJ_REPRESENTATIVE_TABCOMPANY_PERSON_TABCOMPANY_TABCOMPANY_SITE_TABCRM_CUST_INFO_TABCUST_HIERARCHY_STRUCT_TABCUST_INVOICE_ITEM_DISCOUNT_TABCUST_ORD_CUSTOMER_ADDRESS_TABCUST_ORD_CUSTOMER_CFTCUST_ORD_CUSTOMER_TABCUSTOM_ENUMERATIONSCUSTOM_FIELD_ENUM_VALUESCUSTOMER_DELIVERY_TAX_INFO_TABCUSTOMER_INFO_ADDRESS_TABCUSTOMER_INFO_ADDRESS_TYPE_TABCUSTOMER_INFO_TABCUSTOMER_ORDER_CHARGE_TABCUSTOMER_ORDER_HISTORY_TABCUSTOMER_ORDER_LINE_TABCUSTOMER_ORDER_TABDEDUCTION_GROUP_TABDEDUCTION_RULE_TABHANDLING_UNIT_TABIDENTITY_PART_INFO_TABIDENTITY_INVOICE_INFO_TABIDENTITY_PAY_INFO_TABINVENTORY_PART_TABINVENTORY_PRODUCT_FAMILY_TABINVOICE_DISC_TABINVOICE_ITEM_TABINVOICE_PARTY_TYPE_GROUP_TABINVOICE_SERIES_TABINVOICE_TABINVOICE_TYPE_TABISO_COUNTRY_TABISO_CURRENCY_TABMPCCOM_SHIP_VIA_TABORDER_COMMISSION_TABORDER_COORDINATOR_TAGORDER_DELIVERY_T
Hello everybody,just did a quick look by myself and also confirm.On my customers ORACLE DB the table is empty (at the moment).Quite astonishing: most of all the tables end with _TAB. This one with _TMP.I’m very interested about the code which fills the table temporarily!We had similar issues in other data structure and we would love to learn how IFS is “ticking”.All the bestMichael
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
Add on:Because in this case we have no ShipmentLines the whole process of fetching detailed costs fails and the contribution margin is way to high because of missing costs. Excel-Pivot-Reporting for Contribution Margin (CM)
Hi Link,I think, that makes totally sense.This value is an average of all items available at the moment.I checked it:As you can see, the values in the DataWareHouse match perfectly with the inventory value (for one piece) DWH Check for IP Costs More details:Inventory_Part_Unit_Cost_API.Get_Inventory_Value_By_Method(CONTRACT,PART_NO,CONFIGURATION_ID,LOT_BATCH_NO,SERIAL_NO)This API fetch the value:Get_Inventory_Value_By_MethodIt comes down to: INVENTORY_PART_UNIT_COST_TAB Cost buckets result in Field COST I can remember that we tried to get cost data from this source.But the problem is: The moment the Lotbatch is finished, the inventory value - for this lot - will disapear. That was the reason we switched to ITH.Thanks Link for helping me get things sorted out.CUMichael
Hi Mathew,what are you using the IAL for?cuMichaek
Already have an account? Login
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
