[Mike Carey]

I have seen the issue in x64 – would need to check the x86.

As soon as the update was applied none of the workstations with that new version could email out of Quantum.

It comes up with some sort of component licensing issue – exact message I will need to find and provide.

Rollback fixes issue.

Now waiting for CC to investigate further and advise.

I am hoping it is not Microsoft attempting to get us to move up a tier on 365 by removing a component and saying we need the more expensive version….

[Mike Carey]

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

[Mike Carey]

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

[Mike Carey]

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.

[Mike Carey]

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.

[Mike Carey]

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

[Mike Carey]

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…

[Mike Carey]

I’m not sure much is standard, it very often relies on customers coming up with the idea and paying for the programming … 🙂

The issue now with this feature, is it takes one or two days to add the feature to the module, then 3 times longer intergating that change into related modules, so it becomes very expensive to do.

[Mike Carey]

We asked for a CP quote for this feature to be added about a month ago.

Unfortunately it was quoted at approximately $20k.

Which puts it beyond out budget for the requirement.

So it is on the wish list … 🙂

[Mike Carey]

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

[Mike Carey]

Andrej- did you find a solution to your last question ?

[Mike Carey]

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.

[Mike Carey]

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.

[Mike Carey]

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.

[Mike Carey]

thanks for the help- Pietro, that works perfectly. I forgot all about styles with Crystal Reports !

[Mike Carey]

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.

[Mike Carey]

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).

[Mike Carey]

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

[Mike Carey]

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.

[Mike Carey]

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..

[Mike Carey]

Jake- what ideas have you considered ? Can you describe your current process?

[Mike Carey]

Jesse- what is the “fix” that you use there ? I was thinking of a daily export that section of the registry using the task scheduler. Then the user could import the backup back in to fix the problem..

Our issue seems to be only the Window bar being turned off, so turning it back on is not that big of deal, just annoying.

[Mike Carey]

If that is the free text on the Invoice header, I can’t see that being possible, as the address format is different.

I believe you can populate that free text address from the company site etc. but not the other way around.

From memory, those free text addresses throughout Quantum are from pre-Quantum days and required for compatability now.

We lock down all free text addresses in Quantum on header windows etc. and the user must select an existing company address/site etc., using the appropriate feature(s).

Is it just that you want to make sure the company sites etc. are updated when users create addresses for orders & invoices ?

If it is, then lock down free text entry and make them use company info…

But to answer you main question, I don’t believe it is possible due to address format differences, if I understood you question correctly …

[Mike Carey]

We see it happening every 2 or 3 weeks. We have about 12 users of Quantum. I have been attributing it to inadvertent keystroke combinations toggling them off, but maybe it is something else. I am going to figure out a way to trace and get notified when this happens, so that i can get notification and figure out what the user was doing. (or not doing)

[Mike Carey]

Jake- the Purchase_WO table is the link between WO BOM and PO_Detail. I use the following query to do just what you are looking for…

(select po_number || ‘ Due ‘ || To_Char(pod.next_delivery_date, ‘MM/DD/YY’)

from po_detail pod, po_header poh, purchase_wo pwo

where wob.wob_auto_key = pwo.wob_auto_key

and pwo.pod_auto_key = pod.pod_auto_key

and poh.poh_auto_key= pod.poh_auto_key

and pod.qty_rec < pod.qty_ordered) po_number_next_del

[Mike Carey]

i am not sure if this helps or not, but we installed Quantum on some tiny computer (Minix) that has very limited space on the C drive. We added an external F drive in the USB port and told Quantum to install there and it works fine no problem. The Oracle and Common Files still installed to the C drive.

[Mike Carey]

Hmmm, I believe we paid for custom programming on this years ago – adding security and to do exactly what you are seeing here.

The reason was to create workflows for staff, so that they see what work is assigned to to them and then work on that – without the distraction of other ship orders etc. that are not for them to work on.

An improvement maybe to have a module setting/user setting to allow them to see the items but not to be open or assign them from that screen.

You could suggest this to CC and it could go into a wish list…

We could add to our CP, it does cause us the occasional problem, but not sure I can justify the approx $2000 for the amendment …. yet.

[Mike Carey]

Brandon- we don’t use the shipping module, but i assume that you want to WO customer PO on each line item on the shipping document ? If so, the SM Detail wob_auto_key can be linked to the WO_BOM wob_auto_key, and then WO_BOM woo_auto_key links to the WO_OPERATION woo_auto_key, where you can get the customer PO.

The query looks like this:

select company_ref_number

from wo_operation woo, wo_bom wob

where wob.woo_auto_key = woo.woo_auto_key

and wob.wob_auto_key = (wob auto key from SM Detail)

[Mike Carey]

Hi Brandon… here is the SQL that is used to build the Report Order form..

For the Header:

select ROH.*, CMP.COMPANY_CODE, CMP.COMPANY_NAME, CMP.ADDRESS1, CMP.ADDRESS2,

CMP.ADDRESS3, CMP.CITY, CMP.STATE, CMP.ZIP_CODE, CMP.COUNTRY,

(select PRINT_STATEMENT from CURRENCY CUR where CUR.CUR_AUTO_KEY=ROH.CUR_AUTO_KEY) PRINT_STATEMENT,

(select DESCRIPTION from SHIP_VIA_CODES SVC where SVC.SVC_AUTO_KEY=ROH.SVC_AUTO_KEY) SHIP_VIA_CODE,

(select TERM_CODE from TERM_CODES TMC where TMC.TMC_AUTO_KEY=ROH.TMC_AUTO_KEY) TERM_CODE,

(select COMPANY_NAME from SYS_COMPANIES SYSCM where SYSCM.SYSCM_AUTO_KEY=ROH.SYSCM_AUTO_KEY) SYS_COMPANY_NAME,

(select RESALE_NUMBER from SYS_COMPANIES SYSCM where SYSCM.SYSCM_AUTO_KEY=ROH.SYSCM_AUTO_KEY) RESALE_NUMBER,

(select ACCOUNT_NUMBER from SHIP_VIA_ACCOUNTS SVA where SVA.SVA_AUTO_KEY = ROH.SVA_AUTO_KEY) ACCOUNT_NUMBER

from RO_HEADER ROH, COMPANIES CMP

where ROH.CMP_AUTO_KEY=CMP.CMP_AUTO_KEY

and ROH.ROH_AUTO_KEY = (insert ROH auto key here)

For the Detail:

select ROD.*, PNM.PN, PNM.DESCRIPTION,

(select CONDITION_CODE from PART_CONDITION_CODES PCC where PCC.PCC_AUTO_KEY=ROD.PCC_AUTO_KEY) CONDITION_CODE,

(select UOM_CODE from UOM_CODES UOM where UOM.UOM_AUTO_KEY = PNM.UOM_AUTO_KEY) UOM_CODE,

(select SO_NUMBER from SO_HEADER SOH, SO_DETAIL SOD where SOH.SOH_AUTO_KEY=SOD.SOH_AUTO_KEY and SOD.SOD_AUTO_KEY=ROD.SOD_AUTO_KEY) SO_NUMBER,

(select CORE_SERIAL_NUMBER from EXCHANGE E where E.ROD_AUTO_KEY = ROD.ROD_AUTO_KEY) CORE_SERIAL_NUMBER,

woo.si_number mo_number

from RO_DETAIL ROD, PARTS_MASTER PNM, WO_TASK WOT, WO_OPERATION WOO

where ROD.PNM_AUTO_KEY=PNM.PNM_AUTO_KEY

and ROD.ROH_AUTO_KEY = (insert ROH auto key here)

AND wot.wot_auto_key (+)= rod.wot_auto_key

and woo.woo_auto_key (+)= wot.woo_auto_key

order by ROD.ITEM_NUMBER;

[Mike Carey]

Brandon- for efficiency and performance sake, you should just select the field(s) that you need instead of all fields in the table. In this case, the Work Type table is small and only has a few fields, but with other tables, the performance impact on this report and other users will be noticeable. If you use “select *…” all the time on all your reports, over time you will put extra load on the server and network that is not needed.

So in this case, use “select description from…” instead of “select * from… If you use “select *…”

[Mike Carey]

What is your server system memory size? What is the SGA/PGA size in Oracle set to? Is it Oracle 11g?

If you are running out of Oracle cache, Quantum performance will drop sharply in certain areas – can be very hit and miss, which is what I would expect with a cache issue.

As a guess you might want at least 100GB server memory with an SGA set to 40GB. I think we run at least 120GB with 52GB SGA set.

SGA/PGA sizes have been overlooked by Component Control in the past from what I have seen.

There are various different methods of setting memory, but the 2 installs done by CC for us were set manually – so increasing memory in server itself made no difference because these settings were overlooked/missed.

Over the years we have changed this a few times for similar issues and the SGA/GGA changes fixed odd performance issues immediately.

We are now on Oracle 12c and I don’t see the issues on there (so far !)

It might not be your issue, but worth mentioning for others too 🙂

[Mike Carey]

Tim- the parameters are dynamic and different for every link.

Pietro- i need to have hyperlinks within a Crystal Report to an exe file- i don’t see how that article helps with this.

[Mike Carey]

Jim- take a look at the invc_detail table and you will it contains the sod_auto_key. So link the SO Detail sod_auto_key to the invc detail sod_auto_key and then the invc detail to the invc header to the get invoice number.

[Mike Carey]

Here is the piece of the code for the TAX_TOTAL field from the GET_WO_BILLING_HEADER function which builds that data pipeline for this report. It is pulling the tax total from the WO Quote Detail record. So maybe something is keeping it from being calculated there ?

NVL((SELECT SUM(FOREIGN_TAX_AMOUNT) FROM WO_QUOTE_DETAIL

WHERE WQH_AUTO_KEY = WQH.WQH_AUTO_KEY),0) TAX_TOTAL

I also found this in the help file in the Shop Control, Quote/Billing Option 2, Print Quote section. Maybe you need to use this document instead ?

Document

Should be STD WO Billing Group Quote/Invoice Document or STD WO/WP Quote/Bill (Taxes). If applying taxes to the billing, you will want to use the latter as this will print the tax on the invoice.

[Mike Carey]

Hi Lisa- this will pull the expiry date and image key for the image itself. Change the Vendor Approval to what ever you call your vendor approval code and the source_pk is the company cmp_auto_key

select doc_expiry_date, image_code, image_key

from image_list iml , image_codes imc

where source_table = ‘COMPANIES’

and imc.imc_auto_key = iml.imc_auto_key

and imc.image_code = ‘Vendor Approval’

and source_pk = 455

[Mike Carey]

If somebody has an old version of Quantum that includes this calculation in the report, they can post save and post that report here so that we can see the calculations in the code.

[Mike Carey]

Take a look at syntax in the last message i sent- make those 2 changes and you will be all set !

[Mike Carey]

It would be helpful if you post the actual error message.. but I think this will clear it up for you. Change the code to start like this: (you had var q:= TOracleDataset) and left off the begin statement

var q : TOracleDataset;

begin

q.SetSession;

[Mike Carey]

Jake- I see a couple of problems there. FIrst, on your SQL statetment, you need a space at the end of each line where you are continuing the SQL on the next line. Otherwise, the text is jammed together and you get SQL syntax error when you run the form. This does not show up as a compile error.

Second, it looks like you are mixing OnPrint events with OnGetText events.

if you are adding to a memo field, then you are using a OnPrint event, and the syntax to add a single line to a memo field is

memoLongDescr.Lines.Text := (some data value)

if you are adding multiple lines to the memo field, you do it like MemoManuals.Lines.Add(some data value);

Since this is a SO number, there isn’t a need for a memo field, use a plain label field. You can do this in a OnGetText event and use the Text := (some data value) syntax to add the data.

Either way, the entire routine needs to start with

var q : TOracleDataset;

begin

Hope this helps !

[Mike Carey]

We have 10.9.38. I can see the tax items in the data streams for Wo, Parts, labor, Charges, Repairs, etc, but i do not see any calculations that use them in the report. What version did you use when you saw the calculations being performed ?

[Mike Carey]

Craig- not sure if this helps but i was rooting around and found this in a Receiver Reconciliation report package ( FUNCTION RR_REPORT) as part of the RR detail data being generated.

pod.QTY_REC_UC + NVL((SELECT SUM(p.QTY_REC_UC) FROM PO_DETAIL P, stock s

WHERE p.ROUTE_CODE = ‘V’ AND p.ORIG_POD = POD.POD_AUTO_KEY and s.stm_auto_key =p.stm_returned

and ( (s.rejected_line=’T’ and s.historical_flag=’T’and s.split=’T’) or (s.rejected_line=’F’ and s.historical_flag=’T’and s.split=’T’) or s.split=’F’ ) ),0)

— and (s.rejected_line<>‘T’ or (rejected_line=’T’ and historical_flag=’T’) ) ),0)

– nvl((select SUM(QTY_REC_UC) from stock where POD_AUTO_KEY = POD.POD_AUTO_KEY and rejected_line=’T’ and historical_flag=’F’),0)

) QTY_UNRETURNED_FINAL ,

Later on in the procedure, it is used again

V_QTY := R_POD.QTY_UNRETURNED_FINAL – least (R_POD.QTY_UNRETURNED_FINAL,R_POD.QTY_RECONCILED)

[Mike Carey]

Jim- this SQL returns the warehouse code in our system. there are 2 fields in the warehouse table for each code, perhaps you want to see the DESCRIPTION column instead of the WAREHOUSE_CODE column? Or maybe you have a more complicated physical location configuration than we do (we have 1 physical location with a few warehouses in it. )

[Mike Carey]

Hey Jim- post your SQL and we can figure it out..

[Mike Carey]

Jim- by warehouse location, are you referring to the stock line reserved to the detail lines of a SO ?

[Mike Carey]

Jiim- run this SQL in the interactive SQL in Quantum (or SQL Developer or OCRunner) and you will see that it does return all detail lines for a RO. Your vba in your Excel is probably not looping through all the records being returned ? post your vba here and i can take a look.

[Mike Carey]

Jim- here is the SQL to do what you are looking for; i put my changes in lower case. the pnm_modify is a pnm_auto_key which must be matched to a parts_master using a 2nd reference to that table.

SELECT RO_HEADER.RO_NUMBER, RO_HEADER.OPEN_FLAG, PARTS_MASTER.PN, parts_master2.pn as pn_modify

FROM QCTL.PARTS_MASTER PARTS_MASTER, QCTL.RO_DETAIL RO_DETAIL, QCTL.RO_HEADER RO_HEADER, QCTL.parts_master parts_master2

WHERE PARTS_MASTER.PNM_AUTO_KEY = RO_DETAIL.PNM_AUTO_KEY

AND RO_HEADER.ROH_AUTO_KEY = RO_DETAIL.ROH_AUTO_KEY

AND ((RO_HEADER.OPEN_FLAG=’T’))

and parts_master2.pnm_auto_key = ro_detail.pnm_modify

[Mike Carey]

Jim- can you post your query in here so I can take a look at it ?

[Mike Carey]

Hi Jim- in the RO_Detail table, pnm_auto_key and pnm_modify point to the part and modify part in the parts_master table.

[Mike Carey]

Possibly login to create your views as Crystal – you will then be limited to schema Crystal.

You are missing out without crystal reports though, $495 to be able to get so much more reporting productivity and options …

[Mike Carey]

We have not lost any custom views we created, on upgrades.

It would be worth checking with the person assigned to do your upgrade though.