Mike
Forum Replies Created
-
That is very helpful to understand what is going on here. What i need to know, however, is what checkbox the user checked, as i need to validate that against another field. Grabbing the database value won’t help with that.
-
Thanks John- that explains why code for new event handlers that i put in the script is being ignored.
Can you tell me how to get the value of a group radio button? In a Purchase Req, I want to validate the purchase type against the account department in the header. But the itemindex for cbType always comes back as 0 no matter which radio button is selected.
procedure BtnOKClick(Sender: TObject);
begin
showmessage(IntToStr(Form.cbType.ItemIndex));
showmessage(Form.cbType.Items[Form.cbType.ItemIndex]);
end;
-
Andrew- have you been able to add tabs to an existing form or change it’s size to allow adding more elements to it ? Would love to see an example of this code.
-
Paul- thank you a ton for this great information ! I will report back on how this all went.
-
Is the “ordinary user” assigned to the theme that the script uses in the user_themes table?
-
Thank you Paul… a glimmer of hope ! I am willing to dig into this- any tips or hints to get me started ?
-
That’s too bad they don’t have that functionality to link from a report to that item in Quantum. It would be very useful to our users.
Maybe it is coming, given how the new Global Search adds this type of functionality (opens an item directly in it’s module based on a search).
-
Ian- are you adding 72 stock lines of the same PN from stock or are there 72 different PN? Or are these coming from a Work Order or Sales Order ?
-
Here is an Excel file with VBA to connect and read from Quantum. It doesn’t do anything particularly useful, it was just a test I created last summer using some videos I found on Youtube.
It brings in a list of Work Order numbers and woo_auto_key where the woo_auto_key < the "Last WOO" value in cell E4. The VBA code shows how to connect, do an sql query and then populate the data into the Excel sheet.
-
Nadim.. This makes sense what you suggested. How do you invoke a stored procedure like this? Can you do it from the Quantum Control program on an open RO?
Ideally, I would like to invoke the stored procedure from the Work Order on it’s main component. This is where we do most of the repetitive outside repairs.
-
Abigail- Is Screen Scriptor available as a module from CC? I thought they stopped selling that one.
-
Abigail- I pretty much have it worked out how to do the database updates, just not sure how to best invoke the procedure to do this.
-
Hi Faye. This is how we did our signatures.
I had people sign their name in black ink on the attached form several times and had them indicate which one to use. The form has a faint black line across it that they sign on, so that signature goes below the line. I then scanned and / or photographed the paper to get a digital image. The image was cropped to the box around the image and then sized to 468 x 45 pixels and edited /save to be black and white BMP image. All of this was to get consistently sized and placed signatures within the 468 x 45 pixel area.
To add the images to Quantum, click User menu, system setup, global settings. Then click the Document Images tab, then click the Add button to add anew image. Navigate to where the image is located on your computer and select it. Type in the persons name in the Description for image box, then click Ok. Click to select the image in the list and click the Users button. On the bottom of the browsing users by signature image window, click add button and choose who can use this signature. (If you don’t do this, anybody can use the signature. ). Close this window. You can add more signatures or click Ok when finished.
In the various forms, you can select the newly added signature(s) to print.
-
We handled this a little differently. I created 2 UDF fields for Repair Orders- Shipping To Vendor, and Shipping From Vendor. These have options “Our Vehicle”, “Vendor Vehicle”, “Use Ship Via” and “Ship on our Account”.
On the Repair Order, these fields both print with their values.
We mostly ship both directions on our account, so the “Ship on our Account” option causes the report to go to OUR company record and get the shipping account number that matches the shipping method on the RO. This then is printed in the field for account number. So the RO header only specifies the ship method, not the account.
To use the vendor account, we pick “Use Ship Via” and select the vendors account number for the ship method on the RO header.
Hope this helps !
-
To elaborate on Dmitri post- this will print the item being viewed: It assumes that you are using a COMMAND to enter the SQL.
You create a parameter in Crystal reports that is the auto_key of the table you selecting from. So if pulling from Sales Orders, SOH_AUTO_KEY is the name of the parameter. It must match the actual auto_key name to work properly. Use this parameter on the WHERE clause to get just the one sales order.
When you add the report to Quantum, add it in the Sales Order folder, give the report a name, and let the Settings Group default to RPT_CUSTOM_CRYSTAL.
Then open the sales order to print, and select Global, Print, Custom Crystal, then choose your report. This will pass the auto_key of the Sales Order that you are viewing into the Crystal Report and print just that item.
-
Hi Duane- i am seeing something similar as well. What is the query that you are using ? There is one thing in the function that is done before the section that I posted, where they add 1 to the “TO” date range. The query uses < on the TO date range, instead of <=, probably because the receive date field is a timestamp, not just a date. I adjusted that in my query, but still get very different results than the report. Maybe there is additional logic in the report generator?
-
Duane- that report comes from the “RECEIVING_LOG_HEADER” function in RPT.IC_PKG. If you can read the code, you can come up with the SQL to generate that report. The SQL is in there and the dynamic part of the sorting and date ranges makes it a little tricky to read. But it shouldn’t be very hard to reproduce this in Crystal or some other reporting tool. Let me know if I can help further with this..
Here is the code of the function:
FUNCTION RECEIVING_LOG_HEADER RETURN QC_UTL_PKG.CURSOR_TYPE
IS
V_GN VARCHAR2(30) := ‘RPT_IC_RECEIVINGLOG’;
V_FROM DATE;
V_TO DATE;
V_SORT NUMBER;
V_SQL VARCHAR2(32000);
V_ORDER VARCHAR2(100);
V_CURSOR QC_UTL_PKG.CURSOR_TYPE ;
BEGIN
V_FROM := RPT_UTL_PKG.date_val(V_GN,’FROM’);
V_TO := RPT_UTL_PKG.date_val(V_GN,’TO’) + 1;
V_SQL :=
‘SELECT STM.STM_AUTO_KEY, STM.CTRL_NUMBER, STM.CTRL_ID, STM.ORIGINAL_PO_NUMBER, STM.REC_DATE, ‘||
‘ STM.RECEIVER_NUMBER, STM.SERIAL_NUMBER, STM.QTY_REC, STM.UNIT_COST, STM.ROD_AUTO_KEY, ‘||
‘ STM.PN, STM.DESCRIPTION, STM.ORDER_REC_DATE, STM.IC_UDF_005, ‘||
‘ PNM.serialized, ROD.qty_repaired, CMP.company_code, PCC.condition_code, ‘||
‘ SYSUR.employee_code, LOC.location_code, pod.unit_cost po_unit_cost ‘||
‘FROM STOCK STM, PARTS_MASTER PNM, RO_DETAIL ROD, ‘||
‘ PO_DETAIL POD, PO_HEADER POH, COMPANIES CMP, ‘||
‘ PART_CONDITION_CODES PCC, SYS_USERS SYSUR, LOCATION LOC ‘||
‘WHERE PNM.pnm_auto_key (+) = STM.pnm_auto_key ‘||
‘AND ROD.rod_auto_key (+) = STM.rod_auto_key ‘||
‘AND POD.pod_auto_key (+) = STM.pod_auto_key ‘||
‘AND POH.poh_auto_key (+) = POD.poh_auto_key ‘||
‘AND CMP.cmp_auto_key (+) = POH.cmp_auto_key ‘||
‘AND PCC.pcc_auto_key (+) = STM.pcc_auto_key ‘||
‘AND SYSUR.sysur_auto_key(+) = STM.sysur_auto_key ‘||
‘AND LOC.loc_auto_key (+) = STM.loc_auto_key ‘||
‘and nvl(STM.REMARKS,” ”)<>”Void Receiving” and nvl(STM.REMARKS,” ”)<>”Void RO Receiving” ‘; /*TS-3/30/2010-#24041*/
V_SORT := RPT_UTL_PKG.num_val(V_GN,’SORT_ORDER’);
IF V_SORT = 0 THEN V_ORDER := ‘STM.REC_DATE’;
ELSIF V_SORT = 1 THEN V_ORDER := ‘STM.CTRL_NUMBER,STM.CTRL_ID’;
ELSIF V_SORT = 2 THEN V_ORDER := ‘STM.RECEIVER_NUMBER’;
ELSIF V_SORT = 3 THEN V_ORDER := ‘STM.SERIAL_NUMBER’;
ELSIF V_SORT = 4 THEN V_ORDER := ‘STM.PN_UPPER’;
ELSIF V_SORT = 5 THEN V_ORDER := ‘STM.ORIGINAL_PO_NUMBER’;
END IF;
IF V_ORDER IS NOT NULL THEN V_ORDER := ‘ ORDER BY ‘||V_ORDER; END IF;
IF V_FROM IS NOT NULL AND V_TO IS NOT NULL THEN
OPEN V_CURSOR FOR
V_SQL||’ AND (STM.rec_date BETWEEN :P1 AND :P2 OR STM.order_rec_date BETWEEN :P3 AND :P4)’||V_ORDER
USING V_FROM, V_TO, V_FROM, V_TO;
ELSIF V_FROM IS NOT NULL THEN
OPEN V_CURSOR FOR
V_SQL||’ AND (STM.rec_date >= :P1 OR STM.order_rec_date >= :P2)’||V_ORDER
USING V_FROM, V_FROM;
ELSIF V_TO IS NOT NULL THEN
OPEN V_CURSOR FOR
V_SQL||’ AND (STM.rec_date < :P1 OR STM.order_rec_date < :P2)'||V_ORDER USING V_TO, V_TO; else open V_CURSOR for V_SQL||V_ORDER; END IF; RETURN V_CURSOR; END;
-
There was another behavior that led me to find the footer setting problem. There was an empty Summary section in the report that printed nothing. When I turned that section off, then the blank page stopped printing at the end of the report, but the footer section stopped printing. Looking deeper into the Footer section properties and found the 2 check box flag to print on first page and last page. The last page box was not checked. A single page PO is both the first and last page.
Hope that helps
-
Tony- could you email me a copy of the report and I can look at it ? my email is in my profile here.
-
Kristen- i wrote a Crystal Report that shows all certs in the system, filter by date, cert type. Would that help you ?
-
Yes it is strange. The printer/scanner is USB attached as well, and the phone was Android, which always prompts for connection type (charge or data transfer) when USB connecting to a computer. No idea why this happens, but she noticed that every time it happened, the phone was plugged in and never happened when phone was unplugged.
-
We had this happening as well. Turns out that it happened only when the user had their cell phone connected to the computer via USB for charging. It hasn’t happened since they stopped using the computer to charge the cell phone. Hope this helps you.
-
Looks like you might need a space in the quoted strings just before from and also the where clause
From this: q.SQL.Text := ‘select list_price’+’from parts_master pnm’+ ‘where
To this: q.SQL.Text := ‘select list_price’+’ from parts_master pnm’+ ‘ where
Actually, after looking at this more, not sure why you need to break up the SQL into the multiple strings. Can it just be like this:
q.SQL.Text := ‘select list_price from parts_master pnm where pnm.pnm_auto_key=’+form.dataset.fieldbyname(‘PNM_AUTO_KEY’).AsString;
-
Ian- is this a tear down, tag and return to customer situation? We have not done one of these yet in Quantum, but I think it would go like this:
Tear down the part from WO
Issue the stock line back to the WO (add to BOM as consumable)
Our procedure to tear down part(s) from a WO and put into inventory is:
1. Add part to the Bill of Materials
a. Click the Bill of Materials tab.
b. Click Add and type in the part number to be removed.
c. Set the disposition to “Turn Inâ€.
d. DO NOT PROCESS the turn in now.
2. Turn the Part In
a. Click on Parts Mgt. / Receiving / Turn-InRemove
b. On the Items to Receive window, add any costs to the item in the Unit Cost field
c. Add the warehouse information and anything else you want on the new stock line here.
d. Click Receive to process and add the part to inventory.
-
Hi Jake- not sure if this works for MO , but it works for Work Order.
select woo.si_number, wtl.start_time, user_name
from wo_task_labor wtl, wo_task wot, wo_operation woo, sys_users sur
where wot.wot_auto_key = wtl.wot_auto_key
and woo.woo_auto_key = wot.woo_auto_key
and wtl.stop_time is null
and wtl.sysur_auto_key = sur.sysur_auto_key
-
You can query the audit_trail table and filter by user_name, source_table (woo, CMP, SOD, RRH, INH, POD, POH, etc) and date range as needed. But note that not everything is captured in the audit_trail, so it will not tell you EVERYTHING that someone did in the system.
select * from audit_trail at, sys_users su
where source_table in ( ‘WOO’, ‘POD’, ‘POH’)
and at.sysur_auto_key = su.sysur_auto_key
and su.user_name = ‘xxxx’
AND TRUNC(AT.STAMPTIME) BETWEEN TO_DATE(’01-SEP-2015′, ‘DD-MON-YYYY’) AND TO_DATE(’30-SEP-2015′, ‘DD-MON-YYYY’)
-
Kristin
Would the WO Labor Report give you what you need? (can filter by specific employee and date range).
-
James- our sys_log table is empty. Maybe this is used with Active Directory logins ? (We don’t do this)
-
John- which WO form are you referring to that needs customizing ?
-
I would also check the GL default accounts/distributions settings to see if you are missing an account for one of the items that your receiver is trying to process.
-
Since you want the stock room to pick the alternate parts, and not the technician, you only need 1 BOM / Template. Set up the alternate parts in IC, turn on WO Auto Reserve Alts flag in IC ettings. I believe the Pick Ticket reports will already show alternates. Auto Reserve will pick alternates if the main PN is not available once you turn on the flag above.
-
Dan
If you are recreating this in Forms Designer, I have used the “parameters” to add additional filters and flags to reports. This creates a pop up window where user can specify the parameter values.
In the Report Tree window on Forms Designer window, you will see Parameters just below the top level. Right click and add a new one. Give it a name and then set the parameters in the section below. Don’t forget to set the Enable property to activate it. You can play around with different settings to see how they work.
If you are going the Crystal Reports route and need the data, the procedures used to create the data for this are not that hard to find. They are functions within the QC_IN_PKG function, which you can browse with OCRunner or SQL Developer. Once you locate the proper functions, you can copy the SQL used and adjust the variables used in SQL procedures to be the Crystal Reports parameters and you should be good to go.
To make sure you have the proper function, make sure that the data fields in the Forms Designer pipeline match exactly to the fields in the SQL select in the package.
I found the functions RPT_INVC_DETAIL and RPT_INVC_HEADER which fill the header and detail data pipelines. The others are in the same package.
-
David is correct. Your error message happens on line 68 of the trigger, which is caused by stock line qty >1.
Here are lines 66-68 of the trigger
IF ((:NEW.SERIALIZED <> :OLD.SERIALIZED) AND (:NEW.SERIALIZED = ‘T’)) THEN
OPEN C1 FOR SELECT ‘X’ FROM STOCK WHERE PNM_AUTO_KEY = :NEW.PNM_AUTO_KEY AND QTY_OH > 1;
QC_UTL_PKG.RAISE_WHEN_EXISTS(C1,QC_E_PKG.E_PNM_SERIAL_CONST);
You will need to split those stock lines before running your routine. You can use this to locate the stock lines that need to be split.
select pm.PN, st.stock_line
from PARTS_MASTER pm
inner join stock st on pm.pnm_auto_key = st.PNM_AUTO_KEY
where serialized = ‘F’
and st.REC_DATE is null
and pm.PTC_AUTO_KEY = 3
and st.RCD_AUTO_KEY is null
and st.qty_oh > 1
-
Could you simplify this into a single SQL statement that you run with interactive SQL ?
UPDATE PARTS_MASTER SET SERIALIZED=’T’ WHERE PNM_AUTO_KEY in (
select pm.PNM_AUTO_KEY
from PARTS_MASTER pm
inner join stock st on pm.pnm_auto_key = st.PNM_AUTO_KEY
where serialized = ‘F’
and st.REC_DATE is null
and pm.PTC_AUTO_KEY = 3
and st.RCD_AUTO_KEY is null)
-
Rob- what is the trigger error message that you are receiving ?
-
Rob- I don’t have the shipping module, so not sure how the tables connect. But what if you use RESERVATIONS[‘STM_AUTO_KEY’] instead of the STOCK[‘STM_AUTO_KEY’] and remove the left outer join above that ?
-
We had similar problem, from a different cause. We billed and invoiced the wrong amount. CC suggested we bill again and do a manual credit memo on the invoice by entering a negative amount line item. This worked from the accounting side, but not from the Work Order side. (The Work Order profit browse shows both billing amounts).
What we did was to create a negative billing amount to cancel the wrong billing, apply the negative invoice created to the wrong invoice, then bill again the proper amount.
In your case, you would use the negative billing invoice as the credit memo for your customer. Apply this negative invoice against the initial invoice to remove the AR.
Hope this helps someone !
-
Abigail- Looks like that feature was added in 10.8.6 / 10/9.1
-
I just noticed that in version 9 they added a Print Custom Crystal to the Global menu for Stock Issues. More incentive to upgrade from v7.
Inventory/Stock Issue: New “Print Custom Crystal†option has been added to the Stock Issue Global menu. [Enhancement] [Tracker: T7672] (10.9.1)
In the meantime, I will have the user’s go to the kit’s PN in Inventory Control and print a custom crystal that will let them choose the SI number and then print the Kit Material List for each stock line created in that SI.
-
Hello Andre
What about when there is no Print Custom Crystal option on the menu? I am working with a Stock Issue and building kits
-
One last thing on kits-
When we issue parts to a WO, we use the bar code app to scan the part’s bar code and issue to the WO. If the part scanned doesn’t match what was reserved, it won’t issue to the WO.
How do you do the same process when building a kit? I only see the ability to print a pick ticket, hope that the parts person pulls the correct part and then tell Quantum to build the kit using the reserved parts. Am I missing something ?
-
I too would be very interested in learning more about how these work and what they can do for us to track work orders around the shop.
-
Abigail- what if you create a inventory part or a sales order for each employee and use stock lines for each cert / training type ? You could attach the images for certs, training, etc and modify an existing report (labels, in this case) to print the employee info and attached images.
I know this doesn’t address the original question, but thought it might help your case.
-
Thank you Paul– This gives me what I need to build the Crystal Report for a kit list. I created a 2nd query to “union all” to this that picks up the issued parts as well.
-
Thank you Paul !
Very helpful…
Would you happen to know the query to recreate the Kit Material List report? I want to create a Crystal Report to show the stock line details (SL #, ctrl number, ctrl ID, PO #) for all items in the kit and the existing report doesn’t have the PO # trace. I would print this as a custom crystal report from the part master and have the user select which stock line(s) to print the kit material list.
-
Diane- the “User_Role” column will have an equal sign, =, at the end if they are a admin. I use the following SQL to display an asterisk next to the name if they are an admin.
select user_name, case substr(user_role, -1) when ‘=’ then ‘*’ else ” end as admin from sys_users
-
Jesse- thank you.. The link didn’t work, but it gave me incentive to dig around more on the SAP site to find the updates I needed. (That site is probably the most user UNfriendly site ever built.)
-
Hi Andre- i was referring to the software used to create the reports, not the viewer inside of Quantum.
-
Never mind- found the answer. StrToIntDef converts a string to Integer if it is a valid number, or to a default value if not. Similar function for Float is StrToFloatDef.
https://www.freepascal.org/docs-html/rtl/sysutils/strtointdef.html
-
Hi Duane.. See if this is what you what you are looking for. It pulls invoice data for sales orders and work orders.
select ‘Sale’ inv_type, soh.so_number so_wo_number, condition_code, ind.*
from invc_detail ind, so_detail sod, part_condition_codes pcc, so_header soh
where sod.sod_auto_key = ind.sod_auto_key
and pcc.pcc_auto_key = sod.pcc_auto_key
and sod.sod_auto_key = ind.sod_auto_key
and soh.soh_auto_key = sod.soh_auto_key
and ind.route_code = ‘S’
union all
select ‘WO’ inv_type, woo.si_number so_wo_number, condition_code, ind.*
from invc_detail ind, wo_quote_detail wqd, wo_operation woo, part_condition_codes pcc, wo_stm_complete wsc, stock stm
where pcc.pcc_auto_key = stm.pcc_auto_key
and wqd.wqd_auto_key = ind.wqd_auto_key
and ind.route_code = ‘W’
and wqd.woo_ref = woo.woo_auto_key
and wsc.woo_auto_key = woo.woo_auto_key
and stm.stm_auto_key = wsc.stm_auto_key
order by inv_type, so_wo_number