Mike
Forum Replies Created
-
Craig- this was a forum post from 2012 that may help. Things may have changed a lot since then, so use caution with the SQL update.
https://quegroup.org/f_system_admin/836797?rid=836797%23#836797#836797
-
Writing your own script isn’t too difficult.. If you learn some PL/SQL and read some existing scripts that are provided in Quantum, you can get the hang of it. I have written a few and find them quite useful
-
I would run the query without including the sys_companies join and column (in case it is a security issue with that setup), if you have not already. And use the View User Security Profile (not the Manage user security profile) to compare all the settings between the user having the issue and one that is not having the issue. I like to save that to excel and create a column that puts an X in a column if the two user columns are not the same. Tthen filter on the X in new column spot the differences.
Is there a field blocked in the security profile for the user / template that is included in the query ? Maybe try stripping down the query to show just 1 field from the sql of the query.
We have an issue from the recent 12.1 upgrade where 2 views pull data from the test schema instead of the live schema. CC has no idea why, so perhaps as you said there is a permissions issue on the view or tables involved.
-
Hi Henrik … If you remove the sys companies column and join, does he see data? What if he runs the query in isql on his quantum login?
-
Tino- this may be a long shot, but we had this happen intermittently as well when emailing quotes from Quantum. Some of our quotes require attachments with specs and details about a part to be built. It sometimes took several minutes to locate the documents on the server and attach all of them. Or sometimes they got interrupted by a phone call or visitor.
There is a time-out that occurs between Quantum and Outlook, so if you take too long to send the quote, you will get an error.
-
Is the Office / Outlook 32 bit or 64 ? 64 bit doesn’t play well with Quantum.
-
Jae- have you tried using 32 bit Office ? or if using 64 bit, then using the rweasymapi32/rweasymapi64 to unregister and register the mapi dll ?
-
yes, the extra clock out and in are what seem to be the main problem.. Can you create a admin WO for non-pay that they can clock in and out of ? You would need to modify the extracts or create your own for payroll purposes. THis is what i am looking do here – thoughts on that ?
-
Kristen- what i did was to modify the 8130 form to be our CofC form. I set the document name to the new CofC form, and created / use a CofC number log as well. The only downside to this is that some could create a cert for an actual 8130 for that part. But you could add attribute to parts that specify CofC only and check for that in forms designer when printing..
i will email you our CofC. Note- we are on V12 now, so the forms designer has been converted to new format and may not work on older version of Quantum
Let me know if you need any help with this.
-
It looks like the feedback here is that the module is not good and doesn’t work in the real world. What is everybody using instead ? Home grown like in Paul Stewart’s QUE Conference presentations or some other product ?
-
Nate- This would be a great demo / session for QUE round table/online conference ! I know I would love to see this
-
is there a limit on the length of the data that Excel can receive ? Maybe the result is being truncated .. I assume that if you change some other value (like a due date) for a MO in Quantum that you see that value changing in Excel query as well ?
-
I noticed that our server gets slower when it hasn’t been rebooted in a month. I try to restart it every 3 weeks.
-
Mahmoud- change your join on the clause table to a left outer join. That will give you RO with and without clauses.
Note that RO can be tied to a WO in 2 days- as WO main component (rod.woo_auto_key is not null, points to WO), and as BOM (rod.wob_auto_key points to wo_bom which points to WO with it’s woo_auto_key). you need to make all of these connections as left outer joins too.
RO can also be done straight from inventory, so rod. woo_auto_key and rod.wob_auto_key will both be null.
-
Warren- i thought about that that idea as well. Note that you still have to maintain the drop down user defined field with the work order managers as they change, but that is probably easier.
We are currently updating all of our open work orders to reflect manager personnel changes. It would be nice if there was a function within Quantum to do this.
-
Josh- you could run an SQL via Oracle schedule job or event manager if you have it. It could look for Work Orders without a manager and set the manager sysur_auto_key based on logic in the SQL. You would have to maintain the SQL for new managers, companies, etc. You could have an SQL to change the manager sysur_auto_key when users leave or get hired.
-
Pietro- no it does not. I even used the old version of report from pre-upgrade to v12 and it doesn’t work either. I also tried adding the report using the generic RPT_CUSTOM_CRYSTAL settings / data pipeline and it pulls from train scheme.
WIP Valuation report does the same thing i discovered. GL Income report pulls from correct schema.
-
Pietro- yes- here it is…
-
Thank you Nadim- this is the STD version of the report- no changes have been made at all. The report runs correctly within Crystal Reports desktop. This happens on several workstations and multiple logins.
-
Warren- when I add a stock line using the process that you describe, there is a place on the receiving window to add the cost of the new stock line. If you put a cost here, a GL batch is created with entries for that amount. If you leave the cost defaulting to 0.00, a GL batch is created, but there are no entries in it, as nothing impacts the GL for a 0.00 value stock line.
The way that i view the GL batches is in the GL drop down menu, Print/Post Batches. Locate the batch with Stock Adjustments in the Description and inspect the batch number.
I am not aware of a setting that impacts how the add new stock line process works. It has always used the receiving process to create the stock lines for us.
-
Warren .. You can inspect the gl batch entry that is created and verify that the gli accounts are what you want.. we have modified the gl accounts a couple of times. Have your accounting people not post batches until you check it out..
-
We have Screen Editor- but i can’t remember how to set up security to determine who can use it. I can’t find anything in help files.
-
Anthony- i probably copied the wrong text to the answer above. Try this- copy all the columns in the select clause and replace the entire group by with them. Then remove all the alias in the group by and also the sum column. That should do it.
-
I think it you add this to the group by, it will work. Group by needs all fields in the select except the grouping function item. I copied and pasted your select stmt items to the group by and removed the column alias’
(inv.qty_ship*inv.unit_cost),
((inv.qty_ship*inv.unit_price)-(inv.qty_ship*inv.unit_cost)),
(inv.qty_ship*inv.unit_price)
-
Imgletterhead1 is placed inside the Region1 region. I am not sure where you are seeing placed inside memo fields or other fields. The Region1 is the top of the header area and sometimes the fields there overlap, but each field can only be placed once in the report….
-
Jeff- you can open the copy of the STD report you made, and then on the file menu, there is a load from file option- if you open the file i sent, it will load and replace the current form. Then you can tweak it as needed.
To get to the qc.requestrow[2] , click on the Calc tab on the top. What i do to make it easier to find code for specific items is to right click in the “modules view” area (top left of the screen) and select “modules”. This shows only the form items that have code behind them. (you will need to do the same and select “events” to show all items if you want add code to an item). Now click on Event Handlers in the module view area and you will see on the right all the items that have code. Click on the “imgletterheadOnPrint” to view that items code. Now you can modify that code.
-
Jeff- we did the same thing here (see attached zip file of the rtm file). I modified the default remittance report to use a header similar to all the other reports. It pulls the image from the Quantum report images table. You will need to modify the parameter on the qc.RequestRow[2]; to get the autokey of your logo in the report_images table.
The advantage of doing it this way, instead of imbedding an image, is that if you change your logo down the road (which we are doing right now), you won’t have to update your forms designer reports. This assumes that you do a replace of the image in the Global Settings/Document Images, so the autokey stays the same, instead of adding a new logo.
-
Mahmoud- how did you resolve this ? I was going to suggest you rename the csv to a text file and then import it with the Excel wizard- and specify text as the column type for the PN column…
-
Craig.. Any filtering you can do I the query will greatly improve the report performance. Bringing extra data from database into The client’s crystal report to be filtered is much slower.
-
Great solution Nadim. We don’t yet use Barcode scanning for labor, so we enter time cards the next (or so) manually. This means the query would need to look at the actual labor date in wtl.start_time instead of wtl.entry_date.
-
Kyle- Basically, Forms Designer is a stripped down version of some software, where some functionality has been removed (like modifying data pipelines). To add new fields not in the existing pipeline, you need to make your own SQL queries and place the result into fields you add to the report.
The way that I learned Forms Designer was to read the forums here as you did, plus digging around on the internet. Report Builder is the software incorporated in Quantum to be “Forms Designer”. The part where you add your own code is called “RAP”. Here are 2 pdfs that go into these in depth.
http://www.digital-metaphors.com/download/pdf/LearnReportBuilder.pdf
http://www.digital-metaphors.com/download/pdf/LearnRAP.pdf
You can look at existing code in the provided forms for ideas on all kinds of techniques. Note that some of these use older methods to grab data.
Hope this helps you get started !
-
great news, Nadim, thank you. Does this support come in 12.1 or 12.2 ?
-
It looks like you are trying to pull WOO info from the VIEW_LOT_FOR_STM_LOT, which only returns for lots. if your work orders are not for lots, then it will not return any work order info.
Could you describe exactly what you are trying to get with your SQL- are you trying to reproduce the browse of all stock ?
-
Brandon- you can look at the release notes for each version. The items are flagged as bug fixes or enhancements. The upgrade page has a link to the current list of upgrades.
https://www.componentcontrol.com/support/upgrades/quantum-upgrades
-
The most efficient way to do this might be (though it won’t work if you run this on Jan 1 or 2 and want last year- but if you are using Event Mgr, just run it on 12/31)
WHERE invoice_date >= TRUNC (sysdate, ‘YEAR’)
-
Craig- setting up an aggregate function in the where gets messy when you have columns with functions and name identifiers, try this SQL…
SELECT STM.PN, STM.DESCRIPTION, STM.SERIAL_NUMBER, DECODE (STI.TI_TYPE, ‘I’, STI.QTY, 0) QTY_ISSUED,
DECODE (STI.TI_TYPE, ‘T’, STI.QTY, 0) QTY_TURN, STM.CTRL_ID, STM.CTRL_NUMBER
FROM STOCK_TI STI, STOCK STM
WHERE STI.WOB_AUTO_KEY = 30680 and STM.STM_AUTO_KEY = STI.STM_AUTO_KEY
group by pn, description, serial_number, DECODE (STI.TI_TYPE, ‘I’, STI.QTY, 0), DECODE (STI.TI_TYPE, ‘T’, STI.QTY, 0), ctrl_id, ctrl_number
having sum(DECODE (STI.TI_TYPE, ‘I’, STI.QTY, 0)) > 0
-
Mahmoud- yes, i wish SQL developer would tell you which line there it finds the error. Here is what I found on your SQL..
missing the . between table identifier and field name in many of the select columns
the third AND statement in the where has comment mark in wrong place
there are some invalid column names , which you will be able to see once the above are corrected.
-
Can you post your most current query?
-
Mahmoud- the query that builds this browse is in the attached file. You will need to be able to decipher the PL/SQL and convert it to use as pure SQL for your report.
-
Hey Craig… Add a where clause to sum the qty issued >1. That should take care of that situation.
-
Vicky- depending on how you do your WO quoting, the pnm_ref field int the quote detail should be the pnm_auto_key for the parts master. You can then see all the stock lines for the pnm_auto_key (with some filtering to see actual available stock lines not reserved, etc)
-
What version is that ? I don’t see that option on 10.9.38
-
I am thinking you are out of luck on this one- you can’t control the data feed or sort orders (except via the report parameters and bom sequence is not one of them in my version).
Your might be able to work around this by not using the data pipeline data for the BOM data, but put your own fields and SQL (with the sort the way you want it) in the title area. This is kind of like stuffing a sub report or two into the header or footer of a Crystal Report.
-
Craig- your code looks fine (i tested it too, the SQL returns multiple values for a BOM item). The problem is probably with your memo fields- they are set to a height of a single row. You need to add the “stretch” parameter to each memo field (right click and select Stretch in the design tab. That should take care of it !
-
Craig- the inspection/tear down report that Nadim mentions is what we print after the work order has completed. We have heavily modified it to meet our needs. The data pipeline for the report does not have any stock info, only the BOM details. But you can link the issued stock to each BOM line item with this query (insert your WOB_AUTO_KEY or use the WOB_AUTO_KEY from the WO_BOM datastream).
select STM.STOCK_LINE, STM.SERIAL_NUMBER,
DECODE (STI.TI_TYPE, ‘I’, STI.QTY, 0) QTY_ISSUED,
DECODE (STI.TI_TYPE, ‘T’, STI.QTY, 0) QTY_TURN,
STM.CTRL_ID, STM.CTRL_NUMBER
from STOCK_TI STI, STOCK STM
where STI.WOB_AUTO_KEY = **WOB_AUTO_KEY**
and STM.STM_AUTO_KEY = STI.STM_AUTO_KEY
-
Kristen- we do something similar to your process. We create an Excel spreadsheet of the items pulled from the engine. This acts as the manifest of items, along with serial numbers, notes, comments, etc. This is then used to do a Word mail merge to create labels to affix to tags for each part. I am working on using a Quantum template to define the BOM with inspect activity. BOM item notes, S/N etc can be entered on the BOM items, then a report run for manifest and tags.
-
Mahmoud- i don’t know event manager at all, but maybe you can do all this within the report. You can code the where clause in the select (if you are using cmd) with xxdate >= (sysdate -7) to get the last days. Or if using Crystal reports record select, xxdate >= dateadd(“d”,-7,CurrentDate)
-
If you want all stock and the Work Order number for any parts that are main components, your query just needs a slight modification. Add the (+) to the right side of the first where clause, so that you will also match stock lines that are not a WO main component,
STK.PNM_AUTO_KEY = WOO.PNM_AUTO_KEY(+)
However- i think this query will incorrectly show a SI number for all stock lines where that PN is a main component, even if that stock line is not on that work order. I don’t think this is what you want. You should be matching the stock to Work Order main component using the view VIEW_SPB_WO_MAINCOMPONENT, matching the stm_auto_key.
-
Mahmoud- i am pretty sure that you need to delete the row(s) in the stock_reservations table for each wob_auto_key that matches the work order and is consumable.
-
You can use this where clause to filter last 30 days dynamically
purchase_date >= trunc(sysdate-30)
trunc removes the timestamp from the current system date.