Extended query on Costed BOM issue with linking with SUBORD_PART_ID

Hi,

I'm new to running queries. Infor Support pointed me to this place, as they do not support any questions regarding the Report Builder extended queries. I'm running 9.0.8 and just learned about using extended queries in the reports. The powerpoint presentation that Support gave me seems like it's out of date. The instructions tell me to put the extended query in the Report Overrides menu, and still has a button for it. But what I've discovered is that you need to place a function called SQLQUERY in the actual QRP itself with the SELECT statement into a Variable. I hope that is the right way to do it? I would have thought that you would merge the table with the query, but it seems as if it will query this on every line/record that it pulls, so I'd imagine this will be very costly if the query was complicated and the records were large? It's almost like a subreport in Crystal.

So what I seem to have an issue with is linking the PART table with the VMCSTBOM.QRP report. So let's say for this report I wanted the QTY_ON_HAND column, I would do this in the SQLQUERY function:

'SELECT QTY_ON_HAND FROM PART WHERE ID = :SUBORD_PART_ID INTO :QOH'

The problem is that SUBORD_PART_ID seems to have either spaces or tabs in front of it, so it will not be a perfect match. So a part that is 'PART-123' will look like ' PART-123' as the SUBORD_PART_ID. I tried to do a LTRIM(:SUBORD_PART_ID) but that fails.

Does anyone have any suggestions?

Thank you!!

Find more posts tagged with

VISUAL - Enterprise General Discussions

Accepted answers

All comments

charles-hooper

I suggest that you read my six-part article series "How To: Customizing Visual Manufacturing Standard Reports", in particular articles one through three. You must be a member of the VMIUG-TEC Google Group to view those articles. If you are not yet a member of the VMIUG-TEC Google Group, I believe that Google will allow you to request membership when accessing the below link - please provide enough information when joining so that the group administrators are able to confirm you are a Visual user (for example, briefly state your problem). The article series is accessible from the following link: groups.google.com/.../vmiug-tec

You need to set up a report format override using a copy of the standard report that is put into a different folder - never directly modify the standard reports in their original folder location. To create a report format override, select Report Format File Overrides... from the Maintain menu in Visual's Application Global Maintenance. Once the report format override is saved, you may click the Add Extended Query button and add the extended query.

In a quick test, the above extended query seemed to work - you must also place the new Input Variable into the detail line of the report for it to show up. Keep in mind that the bill of material report will show the part IDs for the leg header cards, when the engineering masters have multiple legs - those leg part IDs do not cause inventory transactions, so it is quite likely that the QTY_ON_HAND will be 0 for those part IDs. If you think that spaces or tab characters are being added to the SUBORD_PART_ID Input Item, you may confirm that is happening by creating the below formula in the report, and then adding that formula in one of the report's detail block lines (it should then be easy to visually count the number of characters and compare that count with the output of the formula).

The extended query will execute for each detail data row in the report (to be clear, not once per line in the report's Detail Block section), so you need to make certain that any added extended query executes efficiently.

Charles Hooper
Senior Software Engineer
Meadville Forging Company

unknown

Thanks for the reply!

Yes the example you chose above is the VMBOMRPT.QRP. That's the standard BOM report, and I've got that to work there as well. They put the spaces (indents) by using a formula in the Report Builder. So the SUBORD_PART_ID is a valid ID.

The one that I have an issue with is the Costed BOM report. This is VMCSTBOM.QRP. For some reason the SUBORD_PART_ID gets a prefix of spaces (or some kind of indents) right from Visual.

charles-hooper

Sorry, I missed that you were trying to work with the Costed Bill of Material report. I am able to duplicate the problem you are seeing with the Costed Bill of Material report. Visual will not permit you to place one of the report's Input Items into an Oracle Database or SQL Server function to strip the leading spaces, as you have already found. To work around the problem, you must flip the entry around and only apply database functions to the database table columns, which will adversely affect the efficiency of the extended query execution (the index on the PART table's ID column cannot be used by the database engine).

If your part IDs are mostly unique, where one part ID is NOT likely to be found inside another part ID, you may set up the extended query as shown below for an Oracle Database back end:
SELECT QTY_ON_HAND FROM PART WHERE :SUBORD_PART_ID LIKE '%' || ID

For a SQL Server back end, you would set up the extended query as shown below:
SELECT QTY_ON_HAND FROM PART WHERE :SUBORD_PART_ID LIKE '%' + ID

If you DO have cases where one part ID might be found inside another part ID, then you would need to use a much longer SQL statement for the extended query. For example, the following Oracle Database SQL statement will work for zero to eight leading spaces (the extended queries must be typed without any embedded Enter keys):
SELECT QTY_ON_HAND FROM PART WHERE :SUBORD_PART_ID = ID OR :SUBORD_PART_ID = LPAD(N' ', 1) || ID OR :SUBORD_PART_ID = LPAD(N' ', 2) || ID OR :SUBORD_PART_ID = LPAD(N' ', 3) || ID OR :SUBORD_PART_ID = LPAD(N' ', 4) || ID OR :SUBORD_PART_ID = LPAD(N' ', 5) || ID OR :SUBORD_PART_ID = LPAD(N' ', 6) || ID OR :SUBORD_PART_ID = LPAD(N' ', 7) || ID OR :SUBORD_PART_ID = LPAD(N' ', 8) || ID

The SQL Server equivalent of the above is shown below:
SELECT QTY_ON_HAND FROM PART WHERE :SUBORD_PART_ID = ID OR :SUBORD_PART_ID = REPLICATE(N' ', 1) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 2) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 3) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 4) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 5) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 6) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 7) + ID OR :SUBORD_PART_ID = REPLICATE(N' ', 8) + ID

Charles Hooper
Senior Software Engineer
Meadville Forging Company

unknown

keatl: FYI the VMIUG-TEC group that Charles mentioned is VERY active. I strongly suggest joining it. A lot of very knowledgeable people exchange questions and solutions there. I believe Charles is the admin and can help you with getting into the group. https://groups.google.com/g/vmiug-tec There is also the VMIUGLIST group, which is not quite as active and not quite as technicallly oriented: https://groups.google.com/g/vmiuglist

One thing as Charles pointed out: the usual way to do Extended Queries is from App Global. This creates both the SQL Query, along with the INTO statements, and the Input Variables as well. Just be careful to not have the report open in Report Builder when you do the Extended Query, because if you save the file after it was open during an extended query session, it will write was was in the QRP file to the extended query, and you'll lose your work.

I think the spaces you are seeing are hard-wired in the QRP report to produce the indentations of the levels.

Here's my version of the BOM report. It's a little bit like Synergy's BOMXploder app, which shows part shortages at various levels. You can't of course drill to Material Planning or the WO like BOMXploder can.

Here's my query to bring in the extra part material planning info:

SELECT PS.QTY_ON_HAND , PS.QTY_ON_ORDER , PS.QTY_IN_DEMAND , PS.QTY_COMMITTED , PS.QTY_AVAILABLE_MRP , PS.PLANNING_LEADTIME , R.CALC_QTY , W.DESIRED_QTY , W.TYPE FROM PART_SITE PS , REQUIREMENT R , WORK_ORDER W , OPERATION O WHERE PS.PART_ID = :SUBORD_PART_ID AND O.WORKORDER_TYPE = W.TYPE AND O.WORKORDER_BASE_ID = W.BASE_ID AND O.WORKORDER_LOT_ID = W.LOT_ID AND O.WORKORDER_SPLIT_ID = W.SPLIT_ID AND O.WORKORDER_SUB_ID = W.SUB_ID AND R.WORKORDER_TYPE = W.TYPE AND R.WORKORDER_BASE_ID = W.BASE_ID AND R.WORKORDER_LOT_ID = W.LOT_ID AND R.WORKORDER_SPLIT_ID = W.SPLIT_ID AND R.WORKORDER_SUB_ID = W.SUB_ID AND R.OPERATION_SEQ_NO = O.SEQUENCE_NO AND R.SITE_ID=W.SITE_ID AND O.SITE_ID=W.SITE_ID AND W.SITE_ID = :SITE_ID AND W.BASE_ID = :PARENT_PART_ID AND R.PART_ID = :SUBORD_PART_ID AND R.QTY_PER = :QTY_PER AND PS.SITE_ID = W.SITE_ID

Also, Charles is right about always making copies of QRP's in a separate folder that you will use for customized reports. That is because the standard QRP's get overwritten when you upgrade Visual. Also in case you corrupt a report, which I have done. My favorite way to do this is make folder (keep the name SHORT, QRP's are only allowed to have a filepath something like 70 characters long!), and copy ALL the QRP's into that folder, then rename them en masse with a -C at the end, using the Windows Command Prompt or Power Shell. Then no matter what file I want to customize, I know it's already there and ready to be used for an override.

unknown

The problem is that the Costed BOM does not list the LOT_ID, SPLIT_ID, SUB_ID, SEQ_NO, nor PIECE_NO. I don't know why that was done. But I'm guessing that because the BOM structure is not in the report, the space on the SUBORD_PART_ID was the only way to get an indented BOM. They are doing some whizbang querying in the background of this report! To be honest, I think I'd just bring the costs into the BOM Report and call it good. The Costed BOM is, of course a costing utility. But the report itself obviously isn't designed for easily bringing in part planning info like the BOM report is.

unknown

keatl--one last thing on this thread. Extended Queries have a limit to how long they can be. If you have a complex query with lots of tables and joins, or if you have a lot of fields to bring in, you can get around that by using SSMS to develop a View, then reference the View in the Extended Query.

Copyright © 2025 Infor. All rights reserved.