[Henrik Ekenberg]

Hi Abigail,

If you have Event Manager you can use the “Company Approval” event to set a warning (or restriction) on all customers where the approval date is exceeded.

You can specify what text should be in the warning, when it should be triggered, you can even make it change the approval code.

//Henrik

[Henrik Ekenberg]

Hi Katelyn,

I am not sure why you get this error, maybe the naming convention is wrong.

Try removing the “/” at the end of your script, and change “end;” to “end QCTLJAW.AWB;”

//Henrik

[Henrik Ekenberg]

Hi,

I am just being curious, but why would you receive a Non-Stock item? Why not receive it as a Stock Line and afterwards decide what to do with the item?

Thanks,

Henrik

[Henrik Ekenberg]

Hi Kristen,

I assume the 10k pn’s are already in Quantum.

On way to do this is by exporting the part numbers with pnm_auto_key to an Excel sheet.

You can use Interactive SQL to extract the list to .csv and afterwards import it into Excel.

REMEMBER to try this in your training database first to see if the results are as you intended!

Run this SQL to get the list:

SELECT PNM_AUTO_KEY, PN, DESCRIPTION, ALLOW_PRINT_CAAC FROM PARTS_MASTER

Save the list to a .csv-file and import it into Excel.

Make sure you have the PNM_AUTO_KEY in column A.

Edit the sheet so only the pn’s you want to update is present.

Insert this formula in another column:

=”UPDATE PARTS_MASTER SET ALLOW_PRINT_CAAC = ‘T’ WHERE PNM_AUTO_KEY = “&A2&”;”

Copy the formula all the way down.

You should now have a bunch of update statements all with the pnm_auto_key matching the key in column A and all ending with a ;.

In Interactive SQL write:

BEGIN

END;

Copy all the statements from the Excel sheet and insert it between BEGIN and END;

You now should have something like this:

BEGIN

UPDATE PARTS_MASTER SET ALLOW_PRINT_CAAC = ‘T’ WHERE PNM_AUTO_KEY = 1936228;

UPDATE PARTS_MASTER SET ALLOW_PRINT_CAAC = ‘T’ WHERE PNM_AUTO_KEY = 1936229;

END;

Your list will of course be longer.

Execute the SQL, it will take some time to complete so be patient.

When complete press the commit button to save the changes.

Best regards,

Henrik

[Henrik Ekenberg]

Jason,

I do it in a similar way, but not as elegant as Nadim’s method (thanks for sharing).

I use:

SELECT AMR_AUTO_KEY FROM AC_METER WHERE ACT_AUTO_KEY = XX

to find the meter I want to delete then:

DELETE FROM AC_METER WHERE AMR_AUTO_KEY = XX

to delete the meter record.

You can then delete the AC either by SQL:

DELETE FROM AIRCRAFT WHERE ACT_AUTO_KEY = XX

or via the GUI.

If any of the aircrafts you want to delete are used on a WO or WP it’s a bit more tricky.

You will then have to change the AC on every WO/WP with the wrong AC to the AC you’ve decided to keep.

I’ve be through this earlier because all users had the rights to add an aircraft to the list.

// Henrik

[Henrik Ekenberg]

Cory,

I wouldn’t recommend changing the registry. My point was if the exact key could be identified, you could back up that particular Registration Key, and then simply restore it when the menus disappeared.

The best way would be for CC to save the settings in QC on a per user basis.

Best regards,

Henrik

[Henrik Ekenberg]

Hi Jason,

Are you getting any error messages when trying to delete?

//Henrik E.

[Henrik Ekenberg]

Hello Cory,

We have the same problem here. We are running 10.7.38.

I addressed this problem during a Round Table Session at last year’s conference. And CC had not heard of this before.

Several of the other Round Table participants informed they had the same issue.

Apparently the settings are stored somewhere in the Windows Registry and not inside QC, which is why the settings are per machine instead of per user.

I assume the settings are stored in the HKEY_LOCAL_MACHINE somewhere, but I have not been able to track it.

I am not sure, but I suspect that Windows is doing something to these settings. The issue appears periodically, and only for some users.

As I heard it during the Round Table Session, CC would work on storing the settings in QC, but as of version 10.7.38 this has not been done.

If you have not already done so, I’ll encourage you, and anybody else who has this issue, to report it to CC, if they get a lot of reports on this issue, maybe they are going to speed up the solution.

Best Regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Peter,

What files are you exporting from?

I can export from a Crystal Report directly to an xlsx-file from within Quantum.

Best regards,

Henrik

[Henrik Ekenberg]

G’day Anne-Louise,

In your report, right-click your date field and select “Format field”. Here you can format the date in numerous ways. You can either choose one of the predefined styles or you can customize the field to suit your needs.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hello Anne-Louise,

The page settings are taken from your default printer (at least in my system). Try and check out your print settings on the printer you have set as default.

What you could do is to make a new blank report, change the page settings and save the report.

Now you have a template you can use whenever you want to create a new report, just remember to save it with a different name so you do not overwrite the template.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Joyce,

You gain access via “Maintain User Security Profiles” – Quantum ControlInventory ManagementRecap

I wonder why they didn’t call it “Analysis”.

Best regards,

Henrik

[Henrik Ekenberg]

Hi Abigail,

I am pretty sure that the xsl-files are written in html.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Bob,

You could do the extract using Interactive SQL.

The following SQL is taken from a Crystal Report I made a while back. It shows various information on Stock Lines with qty on hand.

——

SELECT STOCK.STM_AUTO_KEY,STOCK.PN, STOCK.DESCRIPTION, STOCK.QTY_OH, STOCK.SERIAL_NUMBER, PART_CONDITION_CODES.CONDITION_CODE, LOCATION.LOCATION_CODE, STOCK.UNIT_COST, STOCK.STOCK_LINE, STOCK.REC_DATE, STOCK.QTY_RESERVED, STOCK.RECEIVER_NUMBER

FROM (QCTL.STOCK STOCK INNER JOIN QCTL.PART_CONDITION_CODES PART_CONDITION_CODES ON STOCK.PCC_AUTO_KEY=PART_CONDITION_CODES.PCC_AUTO_KEY) INNER JOIN QCTL.LOCATION LOCATION ON STOCK.LOC_AUTO_KEY=LOCATION.LOC_AUTO_KEY

WHERE STOCK.QTY_OH > 0

ORDER BY STOCK.PN

——

You can export to results to a .csv file and then import it into Excel.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Ok, so all you need to do is adding the STOCK table to your report and make a link (Left Outer Join) from WO_RELEASE/STM_AUTO_KEY to STOCK/STM_AUTO_KEY. Putting in the CONTROL_NUMBER from the STOCK table should accomplish what you need.

//Henrik

[Henrik Ekenberg]

Hello Katelyn

It depends on what number the document you use uses as Form Tracking Number.

I my case, as a European MRO, we use the EASA Form 1 which utilize the field “RELEASE_NUMBER” from the table “WO_RELEASE”.

I can see that some of the earlier 8130-3 documents in QC uses the CTRL_NUMBER from the Stock Line, but the latest ones pulls from the WO_RELEASE table as well.

So you need to add the RELEASE_NUMBER field from WO_RELEASE to you report.

I am not sure about the CAAC, as we do not use it, but the document that I have in my QC (STD Work Order Form CAAC) use the CTRL_NUMBER from the Stock Line.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hello Terri,

There is a setting in User – System Setup – Sales Order called “Allow override core due days”. According to the tool tip text it should somehow work in conjunction with the SM Ship Date, but the Help File states different, so I am not sure.

Try it out (in your test invironment).

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Pam,

Thanks for the feed back.

Working with the Forms Designer can be really tricky sometimes.

I am glad you got it to Work.

Best regards,

Henrik

[Henrik Ekenberg]

Hi Jeff,

Do you have an Oracle admin client installation on the computer you are trying to connect from?

//Henrik E.

[Henrik Ekenberg]

Tony,

Sorry, I missed it was the Invoice Date you needed.

I failed to find that particular table too, but I think I found a workaround.

If you add the AP_ACCOUNT table to the subreport and link CHECK_REGISTER_AP.AP_CONTROL_NUMBER to AP_ACCOUNT.AP_CONTROL_NUMBER you can add the VEND_INVC_DATE field.

Best regards,

Henrik Ekenberg

P.S. I sent you my “STD Check Register” report, so you can see what I did.

[Henrik Ekenberg]

Hi Tony,

I suppose that “CCD01_new” is showing as the Service when connecting Crystal Reports to your Oracle Server? Mine does, and what I need to do is to change “CCD01_new” to “MAXQPROD” to be able to edit the report.

My “STD Check Register” report already show the Vendor Invoice, but only if SHOW_DETAIL is set to True.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Rosa,

Of course you can.

I will email it right away.

Best Regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Terri,

Clipboard History might be useful to you. I use it my self every day.

http://www.outertech.com/en/clipboard-history

//Henrik Ekenberg

[Henrik Ekenberg]

Hi Kristen,

We have had the same problem, and mitigated it by building in alerts into our document just as Poul mention.

If the condition of the released component does not meet the criteria set up in the document, a message box appear telling the user that we are not allowed to issue a form for the selected condition. Furthermore a big “VOID” in red is printed on the form as well.

Right now I am looking for a way to restrict access to the documents that we are not supposed to use. When we create a certificate a list of 7 documents are presented to the user. I would like to be able to remove irrelevant documents from that list by adding some level of security to the documents, just like with the Crystal Reports.

I addressed this issue at the System Administration Round Table during this year’s conference, and from what I heard, it shouldn’t be all that difficult to implement, but let’s see if Component Control choses to do so.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Lydia,

You need to be logged in as sysdba in order to make a system-wide change.

Also, if you use themes, you might have make the change in those too via the Screen Scriptor module.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Terri,

As Anonymous states, connecting to Quantum through a VPN connection will work, but I would strongly advise against it.

When you are on a VPN connection ALL data are transferred over the internet, which as previously noted is extremely slow (compared to a Gigabit LAN).

Another and perhaps a more important issue is the risk of data corruption if the internet connection is unstable or lost.

Using a Terminal Server or Remote Desktop Services eliminates that risk because you basically only transfer what you see on the screen via the internet, and the link between the terminal and the Quantum server still exist if the internet connection is lost.

Using a Terminal Server or RDS is the way to remotely connect to Quantum.

Best regards,

Henrik

[Henrik Ekenberg]

Hi Tony,

Ryan said that it should be doable to add security to the forms, just like on the Crystal Reports.

//Henrik

[Henrik Ekenberg]

Hi Sally

The idea is good – unfortunately the System Defined forms cannot be deleted. It works on “my own” forms, but those are not the problem in this case.

Thanks,

Henrik

[Henrik Ekenberg]

Tony,

In SQL Developer, expand Packages find QC_SCHED_PKG and expand that too.

Expand QC_SCHED_PKG Body and double click PRINT_OPEN_INVOICES.

You should now see the procedure in the pane to the right.

I am actually not sure if you can change the procedure, but maybe you can copy the code and use it to create your own?

Hope this helps.

Regards,

Henrik

[Henrik Ekenberg]

Henrik Ekenberg wrote:

“You are indeed able to set individual permissions directly on a user, I would however advise against it.

I have done this in the past but I experienced that for some reason the users permissions sometimes defaults back to the templates assigned to them. Resulting in “angry” users asking why I revoked their permissions.”

Hello again,

Just a little update.

I found out why the permissions set directly on users are reset.

If you click the buttom “Refresh Users” all users that have the selected template applied will get their permissions reset to match only with the permissions granted by the templates applied to the user.

//Henrik

[Henrik Ekenberg]

You’re welcome, happy to help.

//Henrik

[Henrik Ekenberg]

Hi Paul,

You are almost there, you just need to add one more level. You already got the LOC_AUTO_KEY you need to do the lookup. So you basically just take your code (which gives you the LOC_AUTO_KEY) and use that as the value to look for in the LOCATION table.

The code below will do just that.

q.Sql.text := ‘select LOCATION_CODE from LOCATION ‘ +

‘where LOC_AUTO_KEY = (SELECT LOC_AUTO_KEY FROM STOCK WHERE STM_AUTO_KEY = ‘ + STOCK_RESERVATIONS[‘STM_AUTO_KEY’] +’)’;

You will then need to specify that the data you want is from the LOCATION_CODE field by changing you Text := code to this:

Text := q.FieldByName[‘LOCATION_CODE’].AsString;

That should do the trick. If you want a different field from the LOCATION table just change LOCATION_CODE to whatever is applicable.

Happy coding!

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Tony,

I cannot locate that report in my system. Is it custom made? Or how do you print it?

Best regards,

Henrik

[Henrik Ekenberg]

Tony,

A stored procedure in conjunction with an event in EM can take care of your problem. Basically a stored procedure is just a SQL-script that is stored for easy execution.

You can create the stored procedure in different ways. One way is doing it directly in “Interactive SQL” in QC. Another way, which is my preferred method, is using Oracle’s SQL Developer.

I prefer this because of the ability to debug your procedure when you compile it. SQL Developer will prompt you with errors if the syntax is incorrect.

The following procedure will change the lead days to NULL if it is 30 days.

******

create or replace PROCEDURE MA_MASTER_PARTS_LEAD_DAYS_0 AS

BEGIN

/* Sets LEAD_DAYS from ’30’ to NULL. Master Parts with LEAD_DAYS different from “30” will not be affected. */

UPDATE PARTS_MASTER SET LEAD_DAYS = NULL WHERE LEAD_DAYS = ’30’;

/* Commits the changes */

COMMIT;

END MA_MASTER_PARTS_LEAD_DAYS_0;

******

All you need to do to create the stored procedure is executing the part between the asterisks in Interactive SQL.

Then you create a new event in EM, based on the “Execute Stored Procedure”-event.

Select when you want it to run, enter a descriptive name for the event.

Enter the procedure name, you will have to type it or copy & paste, as the drop down is empty.

If you enter an email address in the next field, an email will be sent every time the event is executed.

Well… that it really.

Best regards,

Henrik

[Henrik Ekenberg]

Cameron Price wrote:

Hey Henrik, sorry I could be a bit wrong, Our last upgrade was a long time ago, and I remember being caught – basically its their upgrade program that runs after the oracle import etc. From what I remember it drops everything (storedprocs/packages/functions etc), and then recreates only the ones they are expecting for quantum. I’m pretty sure when we first started with quantum oracle we put our stored procs int the qctl schema, but got caught out, so we moved to a separate one. My memory is very hazy now though so thanks for pulling me up, I probably should be 100% sure on something before posting :/

Hi Cameron,

I stand corrected on this one…

I have now played around with stored procedures a little bit, and even though I haven’t experienced any of my procedures disappeared after an update, I was wrong about the procedures being created automatically by the Event Manager.

I mistakenly assumed that the content of the stored procedure was defined in the event, but it is not. The event just calls a pre-stored procedure.

So it was me who probably should be 100% sure on something before posting… sorry about that 🙂

I would say it is a good idea to have your own stored procedures documented somewhere so they can easily be restored if needed.

Best regards,

Henrik

[Henrik Ekenberg]

It is correct that there is no direct link between the WO and the quote, but it is actually possible to pull whatever information you need from the PARTS_MASTER table.

In the WO_QUOTE_DETAIL there are links to both the WO_OPERATION (WOO_REF)and WO_BOM (WOB_REF), and from WO_BOM you can get the PNM_AUTO_KEY and thus pull the data from the PARTS_MASTER table.

Try executing this SQL in Interactive SQL and replace ‘1004’ with the WO Quote# you wish to lookup. Remember to enclose Quote# in single quotation marks.

SELECT WQH.WQH_NUMBER AS QUOTE#, WQD.WQD_AUTO_KEY, WOO.SI_NUMBER AS WO#, WQD.WOB_REF, PNM.PN, PNM.DESCRIPTION, WOB.PNM_AUTO_KEY

FROM WO_QUOTE_DETAIL WQD

INNER JOIN WO_BOM WOB ON WQD.WOB_REF = WOB.WOB_AUTO_KEY

INNER JOIN PARTS_MASTER PNM ON WOB.PNM_AUTO_KEY = PNM.PNM_AUTO_KEY

INNER JOIN WO_QUOTE_HEADER WQH ON WQD.WQH_AUTO_KEY = WQH.WQH_AUTO_KEY

INNER JOIN WO_OPERATION WOO ON WOB.WOO_AUTO_KEY = WOO.WOO_AUTO_KEY

WHERE WQH.RECORD_TYPE =’Q’ AND WQH.WQH_NUMBER = ‘1004’

In Crystal Reports it is fairly simple to pull the data you need. In Forms Designer I am sure it can be done too, but that is probably a little trickier.

Best regards,

Henrik

[Henrik Ekenberg]

A trial version of Report Builder can be downloaded here:

https://www.digital-metaphors.com/download/

There are some limits in the trial version, but I believe that the code can be viewed without problems.

You can also download some documentation for Report Builder at that location.

Best regards,

Henrik

[Henrik Ekenberg]

Hi Lydia,

I think it is a good idea to restrict access to that table. At DAO pretty much all the code tables, with a few exceptions, are restricted so only two people can “mess” with them.

The security for Delivery Terms is controlled by the settings in:

“Quantum ControlCode TablesDelivery Terms”

Best regards,

Henrik

[Henrik Ekenberg]

Hello Leo,

You can use an SQL-statment to accomplish this.

I am not SQL-savvy so there is probably a more “elegant” way to do this.

You need to store the company’s US$ AR credit limit in a designated field (I used CV_UDF_19, located on the Additional Info 2-tab in Company Management).

You can then use this field to calculate the MAX_CREDIT.

You need to find out the CUR_AUTO_KEY for US$ to use in the following statement, for the sake of this example let’s assume its “2”:

UPDATE COMPANIES SET MAX_CREDIT = (CV_UDF_019/0.75) WHERE CUR_AUTO_KEY = 2

if you store the AP credit limit in CV_UDF_020 you can use this to update both AR and AP credit limits:

UPDATE COMPANIES SET MAX_CREDIT = (CV_UDF_019/0.75), CREDIT_LIMIT = (CV_UDF_020/0.75) WHERE CUR_AUTO_KEY = 2

As I said, there is most likely a more beautiful way to do it, maybe getting it to pull the exchange rate automatically from the CURRENCY table.

Be aware that the user defined fields are not audited, as far as I know. So there is no telling if someone makes changes to them.

I might be an idea to restrict possibility to edit these fields.

Best regards,

Henrik

[Henrik Ekenberg]

Tony Lameri wrote:

Hi Henrik, I wouldn’t mind getting a copy of that

thanks

tony

Hi Tony,

No problem, I’ll email it to you.

Henrik.

[Henrik Ekenberg]

Hi Wesley,

I have designed a report that almost does that.

The report shows all parts, labor, and charges that have been issued to a specific WO/WP, grouped by the type (parts, labor, charges).

You cannot select a specific date, but it is sorted by the date, so running down the list to find what you are looking for is pretty straight forward.

If you are interested, I can send you a copy.

Best regards,

Henrik

[Henrik Ekenberg]

Your message is hereby disregarded.

[Henrik Ekenberg]

The code seems correct but…

I think in order to do what you want, you’ll need to select the option to print “1 cert for each stock line” in the printing options. That way the main report is run once per stock line processing one stock line at a time. The other way the report runs onces processing all the stock lines in one go. The variable only contain information from one stock line so if you have more than one item on the cert, it is the information from the last stock line that is showed, because the variable is overwritten when it processes the next stock line.

I hope this makes sense.

[Henrik Ekenberg]

That’s odd, it works here. Tried with a shipping order with 4 items on it. Entered data in the Parts Cert field on three of the Stock Lines, and it showed correctly on those three and left the fourth blank as it should.

This may be a stupid question, but you added your field to the subreport right?

Regards,

Henrik

[Henrik Ekenberg]

Hi Anthony.. Happy Monday! 🙂

I am not sure how Report Designer handles “is null”, but I know that it sees [Null] as ”, so…

If you change your code to:

begin

If STOCK_RESERVATIONS[‘PART_CERT_NUMBER’] = ” then

Text := ”

else

Text := ‘FORM NUMBER:’ + STOCK_RESERVATIONS[‘PART_CERT_NUMBER’];

end;

It should output the contents of PART_CERT_NUMBER from the Stock Line in question if not Null.

Regards,

Henrik

Edit: BTW in regards to the TaggedBy and udf10, they are probably defined as variables in the “Detail” section in your Report Objects Tree.

[Henrik Ekenberg]

Hello Bennett,

This is really an awesome tool!

Thank you for sharing.

Regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Nick,

I am on 10.4.5 and when changing to Email in the drop down and clicking print, it only takes 2-3 seconds for the Outlook message to pop up.

Maybe it is the process of creating the pdf-file that is causing the delay?

Best regards,

Henrik

[Henrik Ekenberg]

Hi Billy,

I agree, there ought to be a way to protect the Company Name field from accidentally being edited. Maybe a warning that the company name was changed when OK’ing out of the screen. The the user would at least be aware of it then.

If you use the Event Manager, you can however set up an event alerting you (or whomever applicable) that a company name was changed.

The way I have done it is I set up an “Audit Trail Alert”-event

Unfortunately I cannot attach screen shots, but you can see a picture of my settings here: COMPANY Audit Trail Alert-screen shot

I have choosen to get one list per day with all changes from that day, but you could schedule it to run every half our or what ever you prefer.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

Hi Tony,

As far as I can see, they parameters of that event is set in a procedure called PRINT_OPEN_INVOICES in the package QC_SCHED_PKG

In that procedure it is stated that only invoices with at least one item and POST_STATUS=1 is included in the report.

My guess is that changing the procedure to include POST_STATUS=2 would do the trick.

However, since messing with a stored procedure could cause a lot of trouble, I strongly advise against editing it.

Maybe it is possible to create a new procedure and call it with the “Execute Stored Procedue” event instead.

You could try to talk to Stephen Sullivan from CC, he might have a suggestion on what to do.

Best regards,

Henrik Ekenberg

[Henrik Ekenberg]

+1 for the standard Oracle Backup Scripts. I consider implementing compression of the backup files, as described in Paul’s 2013 presentation (Emergency Backup Server on a shoestring budget). That significantly reduces backup file sizes.

//Henrik