Mike
Forum Replies Created
-
Good to know, thank you Pietro. How is the current 10.11 release in terms of stability / bugs ?
-
What are companies doing with PowerBi and other visual reporting tools ? Can you post screen shots and or code from queries to share with others ?
-
Todd- you should be able to configure your barcode scanner to remove leading zeros. Quantum generic BC doesn’t do this and won’t find the employee id (as you discovered). I would google how to remove leading zeroes for your specific barcode scanner to see how this is done.
Otherwise, you could update all of the ID’s in Quantum to have the leading zeroes to match the badges. But not sure if that would cause other problems or not.
-
Cyndy- this should work for you if you add “Work Order Main Component” or “Bill of Material” (which ever you need) to the part warning settings for that part. Look in the help file here to see how to set this:
Home > Chapter 4 – Inventory Management > Master Parts Global Options
Hope this helps !
-
Another idea is to use Windows’ workspaces/virtual desktop and put each Quantum in it’s own desktop that you can name appropriately.
-
Take a look at the properties for the shortcut that you use to start Quantum. I believe that you can change the icon in there. You can create a different color icon and point your other Quantum to it with the shortcut properties… it may get overridden by Quantum when it decides which color icon to use, based on prod vs test.
-
Nate- take a look at the QueSources in the members area, in the Forms Designer section. There is an entry there for querying the attributes.
Home/ Members-only /QUEsources/ Forms Designer
-
That’s great to hear about version 12. Can ‘t wait to hear how it goes you and others.
Can you elaborate or share any of the changes with report builder ?
-
Jake- Try scanning the working and then the non-working bar code into something like Excel and see what the difference is. We don’t have the mobile app, so i can’t test to see how it works.
When you right click on the bar code in the center of the forms designer and select “configure”, what do you have selected ? Look at everything in forms designer on your bar code and the one that works, to find the difference.
If you want, you can email your form to me and i can take a look at it (File, Save to File).
-
Jake- which app are you using and what field are you scanning into ? try typing the values for the bar code and see what it says- this will make sure that what you are scanning is that the bar code app is looking for. Maybe it is looking for a STM auto key for the stock line ? or the PNM auto key for the part number ? If you are using the genericbc.exe app, are you certain that it is pointing to the database that you are expecting ? check it’s INI file setting.
-
Jake- Take a look at any of the STD forms that have bar code labels. In the DetailsBeforePrint section, you will find the bar code variables being set. Here is the code from one of them, this should help you out.
if PN[‘CTRL_NUMBER’] > 99999 then
begin
if Parameters[‘LEAD_SPACE’] then
BarCodeCrtlNumID.Data := FloatToStr(PN[‘CTRL_NUMBER’]) +
LPad(FloatToStr(PN[‘CTRL_ID’]), 6)
else
BarCodeCrtlNumID.Data := FloatToStr(PN[‘CTRL_NUMBER’]) +
StrPadLeft(FloatToStr(PN[‘CTRL_ID’]), ‘0’, 6);
end
else begin
if Parameters[‘LEAD_SPACE’] then
BarCodeCrtlNumID.Data := LPad(FloatToStr(PN[‘CTRL_NUMBER’]), 6) +
LPad(FloatToStr(PN[‘CTRL_ID’]), 6)
else
BarCodeCrtlNumID.Data := StrPadLeft(FloatToStr(PN[‘CTRL_NUMBER’]), ‘0’, 6) +
StrPadLeft(FloatToStr(PN[‘CTRL_ID’]), ‘0’, 6);
end;
-
This happens in a few other places as well (you MUST set destination to printer, not preview) when printing attached documents and images… great tip !
-
TO add to this, I don’t like how pop up browses and windows block the UI, so that you have to close them in order to look at or operate on the previous windows / tabs.
-
Billy- here is how you can do the code so that it will show the SI_NUMBER for BOM or main component repair order detail lines.
var q: TOracleDataset;
begin
if (RO_DETAIL[‘WOB_AUTO_KEY’]<>0) then
begin
q := TOracleDataset.Create(nil);
q.SetSession;
q.SQL.Text:=’select SI_NUMBER from WO_OPERATION WOO, WO_BOM WOB where WOB.WOB_AUTO_KEY=’+RO_DETAIL[‘WOB_AUTO_KEY’] and WOO.WOO_AUTO_KEY = WOB.WOO_AUTO_KEY;
q.Open;
Text := q.FieldByName[‘SI_NUMBER’].AsString;
q.Free;
end
else
if (RO_DETAIL[‘WOO_AUTO_KEY’]<>0) then
begin
q := TOracleDataset.Create(nil);
q.SetSession;
q.SQL.Text:=’select SI_NUMBER from WO_OPERATION where WOO_AUTO_KEY=’+RO_DETAIL[‘WOO_AUTO_KEY’];
q.Open;
Text := q.FieldByName[‘SI_NUMBER’].AsString;
q.Free;
end
else
Text := ”;
end;
-
Can you post the code that you used to fetch and populate the WO number? I am guessing that the woo_auto_key is only populated in the data stream for main component repairs. For BOM repairs, you need use the wob_auto_key and tie that back to the woo_auto_key with the wo_bom table, then you can get the si_number from wo_operation using the woo_auto_key matched in wo_bom.
-
Craig- if you use templates for user security, you can just update the templates and then “refresh users” on each template.
Otherwise, you can indeed update this with SQL. The tables are user_fields and field_list. Set a field restriction for one user and look at these tables to see how they work. Then build SQL statements to do the rest. As always, test and check carefully in your test system first !
-
Vanessa- this isn’t exactly a search, but you can report on inventory items and filter by a lot of the UDF fields. On the Inventory Control drop down, select forms & reports, then stock listings, then master listing. If your UDF field is on the report settings window, then you can filter by them.
Your other option along this line is to create a Crystal Report that would filter by UDF fields.
-
Re-opening this post to ask a related question. If I create a tab on an existing window that does not have any tabs defined (like Purchase Request Header), it looks like I need to move all the existing fields into another newly created tab ? How would I do this ? If I just add a new tab, it overlays the fields in the window.
Would I need to add a tab for original fields and then cycle through all the fields and change the parent to the new tab ?
-
We are on 10.9.38 and i don’t see any column in the company_sites table for history or such… It also lets me delete a site from the company record that is used in a PO, which doesn’t seem right…
-
Nikki- this is fantastic- thank you ! Great resource to have.
Are there other Quick Guides available someplace ?
-
Nate- there are a few ways to do this, but the best is to “let Oracle do the work” in the query. Change your sql line to this and adjust the YYYY-MM-DD to your preferences, like MM/DD/YYYY for example)
q.Sql.text :=’select TO_CHAR(CLOSE_DATE, ‘YYYY-MM-DD’) from WO_OPERATION where SI_NUMBER = ”’ + WO_BILLING_HEADER[‘SI_NUMBER’]+””;
-
In Forms Designer, you can do something like this in the MemoShipToOnPrint (substitute the company code and the name of your UDA variable. This is only the first part of the code, the rest remains unchanged. I have not tested this, but it does compile cleanly.
var
lsLine: String;
qc : TOracleDataset;
begin
memoShipTo.Lines.Clear;
if (INVC_HEADER[‘COMPANY_CODE’] = ‘XXX1’) then
begin
qc := TOracleDataset.Create(nil);
qc.SetSession;
qc.Sql.text :=’select ATTRIBUTE_VALUE from uda_checked where auto_key = ‘ + INVC_HEADER[‘COMPANY_CODE’] +
‘ and uda_auto_key in (select UDA_AUTO_KEY from user_defined_attributes ‘ +
‘ where UDA_CODE = ”your-attribute-name” and auto_key_prefix = ”CMP”)’;
qc.Open;
lsLine := qc.FieldByName[‘ATTRIBUTE_VALUE’].AsString;
qc.Free;
end
else
lsLine :=INVC_HEADER[‘SHIP_NAME’];
if lsLine <> ” then
memoShipTo.Lines.Add(lsLine);
lsLine := INVC_HEADER[‘SHIP_ADDRESS1’];
-
Not a very elegant solution, but you can create a User Defined Attribute (these are different from UDF) on the Companies module to hold the name (max length of these is 250 char). Then in your forms that need to print the customer name, check to see if the UDA field is present and if so, use it for customer name instead of the customer_name field in the Companies table.
All of the UDF fields in Companies are less than 50 char, so you would need to use a UDA instead.
-
John- there are about 450 views in Quantum (depending on your version). You can view them using either SQL Developer (or most any other tool used to access the Oracle database) or the OCRunner.exe program in the server folder where Quantum lives.
You can also run this query from Quantum in Interactive SQL:
select view_name, text from SYS.ALL_VIEWS where owner =’QCTL’
-
Hi John- i updated my reply above to show the code for the view (VIEW_SPB_WO_MAINCOMPONENT). I originally posted the code for another Quantum PL/SQL that returns main component information, but it was not for the actual view. The view is the simplest way to view the main component info. It is a complex query because the main component can be on a repair order, or reconciled / returned to customer.
Hope this is a little more clear !
-
john..here is how i grab the work order main component. i use the view provided. i also included the code for the view,so you can see how it works and the relationships….
select * from view_spb_wo_maincomponent where woo_auto_key=379
The view is:
SELECT STR.WOO_AUTO_KEY, STR.stm_auto_key, STR.qty_reserved QTY,
STM.stock_line, STM.pn PART_NUMBER, STM.serial_number, STM.ctrl_number, STM.PNM_AUTO_KEY, STM.DESCRIPTION,
STM.gla_auto_key, STM.unit_cost, STM.ovhl_cost, STM.cmp_auto_key, STM.owner, STM.customer_owned,
0 QTY_RETURN,
STM.cnc_auto_key, STM.loc_auto_key ,STM.stc_auto_key, STM.whs_auto_key, STR.str_auto_key,
NVL(STM.TURNIN_REC, ‘T’) TURNIN_REC,
whs.warehouse_code, loc.location_code
FROM STOCK_RESERVATIONS STR , STOCK STM, warehouse whs, location loc
WHERE STM.stm_auto_key = STR.stm_auto_key
and STR.qty_reserved > 0
AND NOT EXISTS (SELECT ‘X’ FROM WO_STM_COMPLETE WHERE WOO_AUTO_KEY = STR.woo_auto_key
AND STM_AUTO_KEY = STR.stm_auto_key )
AND stm.whs_auto_key = whs.whs_auto_key(+)
AND stm.loc_auto_key = loc.loc_auto_key(+)
UNION ALL
SELECT WSC.woo_auto_key, WSC.stm_auto_key, WSC.qty_COMPLETE QTY,
STM.stock_line, STM.pn PART_NUMBER, STM.serial_number, STM.ctrl_number, STM.PNM_AUTO_KEY, STM.DESCRIPTION,
STM.gla_auto_key, STM.unit_cost, STM.ovhl_cost, STM.cmp_auto_key, STM.owner, STM.customer_owned,
0 QTY_RETURN,
STM.cnc_auto_key, STM.loc_auto_key ,STM.stc_auto_key, STM.whs_auto_key, TO_NUMBER(NULL) str_auto_key,
NVL(STM.TURNIN_REC, ‘T’) TURNIN_REC,
whs.warehouse_code, loc.location_code
FROM WO_STM_COMPLETE WSC, STOCK STM, warehouse whs, location loc
WHERE STM.stm_auto_key = WSC.stm_auto_key
AND stm.whs_auto_key = whs.whs_auto_key(+)
AND stm.loc_auto_key = loc.loc_auto_key(+)
UNION ALL
SELECT ROD.woo_auto_key, STR.stm_auto_key, STR.qty_reserved QTY,
STM.stock_line, STM.pn PART_NUMBER, STM.serial_number, STM.ctrl_number, STM.PNM_AUTO_KEY, STM.DESCRIPTION,
STM.gla_auto_key, STM.unit_cost, STM.ovhl_cost, STM.cmp_auto_key, STM.owner, STM.customer_owned,
0 QTY_RETURN,
STM.cnc_auto_key, STM.loc_auto_key ,STM.stc_auto_key, STM.whs_auto_key, STR.str_auto_key,
NVL(STM.TURNIN_REC, ‘T’) TURNIN_REC,
whs.warehouse_code, loc.location_code
FROM RO_DETAIL ROD, STOCK_RESERVATIONS STR , STOCK STM, warehouse whs, location loc
WHERE STM.stm_auto_key = STR.stm_auto_key
AND STR.rod_auto_key = ROD.rod_auto_key
and STR.qty_reserved > 0
AND stm.whs_auto_key = whs.whs_auto_key(+)
AND stm.loc_auto_key = loc.loc_auto_key(+)
union all — KI T1868
select woo.woo_auto_key, null stm_auto_key, null qty,
null stock_line, null part_number, act.serial serial_number, null ctrl_number, null pnm_auto_key, null description,
null gla_auto_key, null unit_cost, null ovhl_cost, null cmp_auto_key, null owner, null customer_owned,
0 qty_return,
null cnc_auto_key, null loc_auto_key, null stc_auto_key, null whs_auto_key, null str_auto_key,
null turnin_rec,
NULL warehouse_code, NULL location_code
from aircraft act, wo_operation woo
where act.act_auto_key = woo.act_auto_key
and WOO.wo_type = ‘Work Package’
-
Craig
I don’t have Work Package, so i cannot fully test this, but ran the SQL against a Work Order with tools and it retrieves what i expected. It looks for all reserved tools that match the task auto key (WOT).
For each of the 4 calc routines, you can use this code, with changes for field names on the memo and the sql field referenced. Because there can be multiple tools per task, you need to loop through the result set.
Hopefully this will solve your issue !!
var
q : TOracleDataset;
begin
MemToolCalDate.lines.clear;
q := TOracleDataset.Create(nil);
q.SetSession;
q.Sql.text :=’Select STM.PN,STM.DESCRIPTION,STM.SERIAL_NUMBER,STM.INSPECT_DUE_DATE from STOCK STM, STOCK_RESERVATIONS STR, WO_TASK_TOOLS WTT where ‘+
‘STM.STM_AUTO_KEY = STR.STM_AUTO_KEY and ‘+
‘STR.WTT_AUTO_KEY = WTT.WTT_AUTO_KEY and ‘+
‘WTT.WOT_AUTO_KEY = ‘ + WO_TASK[‘WOT_AUTO_KEY’];
q.Open;
while not q.eof do
begin
MemToolCalDate.lines.add(q.FieldByName[‘INSPECT_DUE_DATE’].AsString);
q.next;
end;
q.Free;
end;
If you want this report to me more efficient (and run faster), instead of running these queries for each field, you can combine them into the detail_before_print (and remove the 4 individual onprint routines) like this.
var
q : TOracleDataset;
begin
MemToolCalDate.lines.clear;
MemToolDesc.lines.clear;
MemToolPN.lines.clear;
MemToolSN.lines.clear;
q := TOracleDataset.Create(nil);
q.SetSession;
q.Sql.text :=’Select STM.PN,STM.DESCRIPTION,STM.SERIAL_NUMBER,STM.INSPECT_DUE_DATE from STOCK STM, STOCK_RESERVATIONS STR, WO_TASK_TOOLS WTT where ‘+
‘STM.STM_AUTO_KEY = STR.STM_AUTO_KEY and ‘+
‘STR.WTT_AUTO_KEY = WTT.WTT_AUTO_KEY and ‘+
‘WTT.WOT_AUTO_KEY = ‘ + WO_TASK[‘WOT_AUTO_KEY’];
q.Open;
while not q.eof do
begin
MemToolCalDate.lines.add(q.FieldByName[‘INSPECT_DUE_DATE’].AsString);
MemToolDesc.lines.add(q.FieldByName[‘DESCRIPTION’].AsString);
MemToolPN.lines.add(q.FieldByName[‘PN’].AsString);
MemToolSN.lines.add(q.FieldByName[‘SERIAL_NUMBER’].AsString);
q.next;
end;
q.Free;
end;
-
David- have you looked into using User Defined Attributes for this ? You can set these up as drop downs with predefined values.
-
Lisa
If you open most any form (invoice, PO, Sales Order, etc), you can see how they insert the logo. In the STD Purchase Order, it is the imgletterhead1. Look at the code iin the Calc tab for imgletterhead1 and you will see how they select it. In the design tab, you can set the size and placement of the image.
The 2 best ways to learn about Forms Designer is to review the many posts here in the forum and also look at the existing forms to see how they are done. You can learn many techniques and tricks from this !
-
Craig- sure no problem, email it to me…
-
Craig- can you post the entire form (.rtm file, using File, Save to File menu command) on here so we can look at it and help you out ?
-
Terri- would this be the AR_STATEMENT field in the Rolodex table ?
-
McAllister – could you elaborate on the Quantum settings needed to support GSuite ?
-
Nadim- thank you ! I never knew about pivot clause, so i did some research and then combined pivot and decode to get exactly what I wanted- a single line for each active employee and their skills. (your query returned a line for each employee -skill combo)
here is what i came up with, using our skill codes:
SELECT uname,
(SELECT wok_df.description FROM sys_users sysur_df, wo_skills wok_df WHERE sysur_ak = sysur_df.sysur_auto_key and wok_df.wok_auto_key = sysur_df.wok_auto_key) defaultskill,
Decode(Balance,1,’X’) Balance,
Decode(Clean,1,’X’) Clean,
Decode(Engineering,1,’X’) Engineering,
Decode(General,1,’X’) General,
Decode(Grind,1,’X’) Grind,
Decode(HandFinish,1,’X’) HandFinish,
Decode(Inspection,1,’X’) Inspection,
Decode(Janitor,1,’X’) Janitor,
Decode(Machinist,1,’X’) Machinist,
Decode(Mechanic,1,’X’) Mechanic,
Decode(MechanicAPU,1,’X’) MechanicAPU,
Decode(NDI,1,’X’) NDI,
Decode(Paint,1,’X’) Paint,
Decode(Plan,1,’X’) Plan,
Decode(Quote,1,’X’) Quote,
Decode(Technician,1,’X’) Technician,
Decode(Test,1,’X’) Test,
Decode(TestAccy,1,’X’) TestAccy,
Decode(Weld,1,’X’) Weld
FROM
(
SELECT sysur.user_name uname, wok.description, sysur.sysur_auto_key sysur_ak
FROM sys_users sysur, wo_empl_skills wes, wo_skills wok
WHERE wes.sysur_auto_key = sysur.sysur_auto_key
AND sysur.archived = ‘F’
AND wok.wok_auto_key = wes.wok_auto_key
)
PIVOT
(
COUNT(description) FOR description IN
(
‘Balance’ Balance,
‘Clean’ Clean,
‘Engineering’ Engineering,
‘General’ General,
‘Grind’ Grind,
‘Hand Finish’ HandFinish,
‘Inspection’ Inspection,
‘Janitor / Trainee’ Janitor,
‘Machinist’ Machinist,
‘Mechanic’ Mechanic,
‘Mechanic APU’ MechanicAPU,
‘NDI’ NDI,
‘Paint’ Paint,
‘Plan’ Plan,
‘Quote’ Quote,
‘Technician’ Technician,
‘Test’ Test,
‘Test Accy’ TestAccy,
‘Weld’ Weld
)
)
ORDER BY uname
-
Grab the query from your code and run it directly in SQL developer or interactive SQL and see what you get.
Also display your SQL variable on the form along with the auto key being referenced, just to make sure it is as expected
-
Craig- this code is in the Tools subreport and so the datastream for it is all of the tool items for a task. The subreport will loop and print each tool connected to a task. Therefore, you need to match on the wtt_auto_key, not the wot_auto_key, as the wot_auto_key will give ALL tools for the task, not the one being processed for each item in the subreport.
Change your
‘WTT.WOT_AUTO_KEY = ‘+ WO_TASK[‘WOT_AUTO_KEY’];
to
‘WTT.WTT_AUTO_KEY = ‘+ WO_TASK_TOOLS[‘WTT_AUTO_KEY’];
as Nadim pointed out and it should work for you. Note that you don’t need the looping logic after the SQL, as there will only be a single row returned.
Here is how I think it should look: (modify the select for matching wtt_auto_key, change loop to just check for not EOF, remove q.next via comment). I tested this on our Traveler and it displays the correct SN for each tool.
MemToolSN.lines.clear;
q := TOracleDataset.Create(nil);
q.SetSession;
q.Sql.text :=’Select STM.PN,STM.DESCRIPTION,STM.SERIAL_NUMBER,STM.INSPECT_DUE_DATE from STOCK STM, STOCK_RESERVATIONS STR, WO_TASK_TOOLS WTT where ‘+
‘STM.STM_AUTO_KEY = STR.STM_AUTO_KEY and ‘+
‘STR.WTT_AUTO_KEY = WTT.WTT_AUTO_KEY and ‘+
‘WTT.WTT_AUTO_KEY = ‘+ WO_TASK_TOOLS[‘WTT_AUTO_KEY’];
q.Open;
if (q.FieldByName[‘PN’].AsString <> ”) and (q.FieldByName[‘DESCRIPTION’].AsString <> Null)
Then
if not q.eof then begin
MemToolSN.lines.add(q.FieldByName[‘SERIAL_NUMBER’].AsString);
{MemoTools.lines.add(q.FieldByName[‘DESCRIPTION’].AsString);
MemoTools.lines.add(q.FieldByName[‘SERIAL_NUMBER’].AsString);
MemoTools.lines.add(q.FieldByName[‘INSPECT_DUE_DATE’].AsString);}
{ q.next; }
MemToolSN.visible := True;
end
else
MemToolSN.visible := False;
q.Free;
You mention that your code works in another report- check to see what sub report and datastream is where the code is being used. It is probably at a task type, not a task tool type, if that makes sense.
-
Can you post the entire code now ? Note that with the new query against the WTT, there is no “looping through result” needed, as there is only 1 tool per WTT entry.
-
Craig
Did you change the left side of the statement to
‘WTT.WTT_AUTO_KEY
instead of
‘WTT.WOT_AUTO_KEY
I missed that when i tested this and it works for me after using Nadim’s code
-
Jake- we don’t have that module, so i cannot look deeper into this. But is there a browse window in Quantum that shows the levels and hierarchy ? If so, then you should be able to find the package within the MO module that creates the query to build that browse. I can show you how to do that if there is a browse exists showing the template levels…
-
Thanks Andy- i saw that thread, but it doesn’t contain the actual code. He mentioned in several threads that he posted it elsewhere.. maybe it was deleted, as a search (using the procedure names) didn’t locate it
-
Andrej- did you find a solution to your last question ?
-
Jim- pretty sure you can only do 1 per update statement. You would need to generate a update for each line in your Excel and run them. That is pretty easy to add a column and put a formula in it that generates the update SQL based on the ro column and the tracking number column.
You could also do this with a DIA script that would read your excel csv file and do the updates all at once.
-
Jim- is the tracking number for the entire RO or the individual items?
If the tracking is for the entire RO, You can update ro_header set tracking_number = (your data) where ro_number = ‘(your ro number)’
If tracking is for detail items and there are multiple items per RO, you need to be able to identify which PN or item to update.
-
Jake – do you have templates linked to master routers or just multiple templates that have BOM items to report on ? Can you describe a little how you are using this? I created a Crystal Report that shows templates and BOM based on the model selected in a dynamic parameter list, then you can choose all or individual templates for that model and have them printed to review them.
-
thanks for the help- Pietro, that works perfectly. I forgot all about styles with Crystal Reports !
-
Lisa- We include remittance instructions as a footer note in the invoice print parameters and have it print on the bottom of all invoices, just above the signature area. You could also hard code them right on the invoice with Forms Designer.
-
Craig- here is how you can do this with not too much modification to the report.
Set the Detail section to not visible (uncheck the “visible” box).
Create an OnPrint event for the detail section and move some of the code from the WIP and Overhead OnGetText routines here, as shown in the attached screen shot. These are the subtotal and grand total calculations. You can remove the code from the OnGetText routines or leave it there, since it won’t be called (due to Detail section being not visible).
-
Craig- you are correct– i missed that when i tried this out.. looking at how to do this without making big changes to the report
-
Hi Craig- for the STD WO WIP Detail Report, you could just not display the detail section of the report by un-checking the “visible” check box on the detail section.
The GL WIP Log is based off the VIEW_SPB_WO_WIP_LOG and the function spb_wo_wip_log, so you should be able to recreate it pretty easily in Crystal.
-
Jesse- what about some VBA code in Outlook itself ? it could look for attachments of a certain name convention and add some text to the body of the email..