Mike
Forum Replies Created
-
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.
-
Hi Nate- this topic was just answered in the Screen Script forum.. Here is the post. Basically, it is linked via the Purchase_WO table.. Hope this helps !!
-
The Purchase_wo table shows you how much is linked- look at the first 4 columns in that table and you will see the linkage to wo bom and po detail along with the quantity ordered and received. link this table to the wo_bom, and wo_bom to wo_operation to get the si_number. Both of the SQL examples above show that linkage.
-
Jami- can you elaborate more on what you are looking for on this ? is it a report of clock in and outs over a time period ?
-
Mahmoud- the Purchase_WO tables connects the PO to a WO BOM item . To get the SI number for that BOM item, you need to link the WO_BOM table to the wo_operation table. THis SQL does that but removes your “other WO”, which i am not sure what you are expecting (see below)
Select PD.ENTRY_DATE, PH.PO_NUMBER, PH.VENDOR_NAME, P.PN, P.DESCRIPTION, PD.QTY_ORDERED, PD.QTY_REC, PD.LAST_DELIVERY_DATE, PD.UNIT_COST, SU.EMPLOYEE_CODE,
PD.NOTES, PD.PNM_AUTO_KEY, WO.SI_NUMBER “WO#”
FROM PARTS_MASTER P, PO_DETAIL PD, PO_HEADER PH, WO_OPERATION WO, SYS_USERS SU, PURCHASE_WO PW, WO_BOM WOB
WHERE PD.POH_AUTO_KEY = PH.POH_AUTO_KEY
AND PD.PNM_AUTO_KEY = P.PNM_AUTO_KEY
AND PD.SYSUR_AUTO_KEY = SU.SYSUR_AUTO_KEY
AND PD.POD_AUTO_KEY = PW.POD_AUTO_KEY (+)
and pw.wob_auto_key = wob.wob_auto_key
and wo.woo_auto_key = wob.woo_auto_key
Order by ENTRY_DATE asc
But I am not sure what you mean in your first posts above, to show the linked and unlinked SI number for a PO. What are you expecting to see for the unlinked SI number ?
-
Ian- that is to bad the shipper isn’t actually created, i wonder if that is a bug ? We don’t have that module, so i can’t help with that at all. But if that works out, then after doing the shipping, you can just issue all the parts that were reserved and then you can close the WO.
Have you looked at using piece parts to make this work ? each part would be received as a piece part, and then returned to the customer at the end of the job.
-
Vicky- are you looking to have the manual page(s) print with the travelers or to have them lookup-able via part number ?
-
For an external work order, you could add the items to BOM as Replace. Do the turn in to create a stock line. Then process the disposition and reserve the stock line that was turned in. Then you can do the Parts Mgmt shipping to send it to customer.
We don’t do this, but I tested on training system and it looks like it would work. We currently use a manual, outside of Quantum, process to do this for our “Tear down and tag” work orders, but I am looking at ways to do inside of Quantum and have the physical tag printed by Quantum instead of Excel and Word merge. We don’t use the shipping module, so it is a little easier.
-
@Nadim- yes- brilliant investigation and explanation. I have worked on some forms designer reports that use the hide display of detail lines that are truly hard to figure out. It works, but very hard to debug and understand sometimes.
@Craig- good question as to why your forms dated older- worth checking into your upgrade processes for sure.
-
I am very interested in this as well. I have developed a proof of concept how this can work, but have not had time yet to work it to completion.
-
Craig- we are on an older version than you (10.9.38) and my version of STD Work Order Traveler A4 is dated Nov 13, 2013. Not sure why yours is older and also has a “~” at the end of the name. I would look into why that is the case.
Also- your screen shots show printing to PDF- does it print the extra page when printing to a printer ? (wondering if print to PDF page sizes are not the same as the document).
-
Craig- not able to reproduce this with the STD A4 traveler and using your task selection on 10.9.38.
Can you send a screen shot of the print parameters window? You are saying that this is happening with the STD A4 form or your own customized form ?
-
Craig.. If you want to email me your rtm file, I can take a look at it for you.
-
Dana- this code is entered on the OnGetText procedure in the Calc section of the Forms Designer for the varSerialNumber field. This form has multiple sub reports, and the stock items are in the SubReport Stock area. You select these on the bottom of the window.
-
Dana- you can do this with Forms Designer. Set the serial number variable with this code:
begin
if STOCK[‘SERIAL_NUMBER’]<>” then
Text := STOCK[‘SERIAL_NUMBER’]
else
Text:=’N/A’;
end;
-
Hi John- yes, this is what i have in mind. It is my “roll your own” event manager.
-
Thank you Nadim and Thiago for your help- we don’t have Event Mgr, but I can look for errors in the that table other ways automatically and notify me via email.
I will convert my command SQL into a package that has an exception handler to write to this table and see how that works… Maybe the exception handler could return an error flag in the cursor that the Crystal could look for and display on the report, so that the user knows what happened.
-
that table only contains errors from being out of licenses and some dataversion unique key errors.. maybe because we are on 10.9.38 ?
-
i don’t see a SYS_USER_ERROR table- is it in a different schema ?
-
Nadim makes an excellent point about using views. Also, view can change with upgrades / release changes, which could impact your report. I like to take the code from a view or package and extract the components from it that I need to make the query for my report. This way, I am protected from changes by CC, and also have a better performing query.
-
Craig- check the quantity on the WO Header ? You can receive up to that quantity.
-
Lee- did Nadim’s answer on this post (https://quegroup.org/f_crystal/9024099#9024809)answer your question ?
-
Krista- i wrote 2 reports do this. See attached. the v3 one looks at payments made in a date range in the past, and the v2 one is the average of all payments in between today and that date range in the past.
-
Thank you for the update on this John. It indeed is a big project to make this change. I would also like to see the inconsistencies between module windows, how menu items are different, ordered differently and different names for same function (Find, Stock Issue for example) cleaned up as well.
-
We don’t have this in production yet, but this is the SQL that our test report is using in Crystal Reports (ExpDays is a user input parameter at runtime):
SELECT stm.PN, stm.DESCRIPTION, (select loc.location_code from qctl.location loc where stm.LOC_AUTO_KEY = loc.loc_auto_key) as “Location”,
stm.QTY_OH, stm.QTY_AVAILABLE, stm.REC_DATE, stm.EXP_DATE, stm.STOCK_LINE, stm.CTRL_NUMBER, stm.CTRL_ID,
stm.RECEIVER_NUMBER , to_char(sysdate, ‘MM/dd/YYYY’) as DateFrom, to_char((sysdate +42), ‘MM/dd/YYYY’) as DateTo
FROM stock stm
WHERE stm.EXP_DATE IS NOT NULL
and stm.QTY_AVAILABLE != 0
and stm.EXP_DATE <= sysdate +{?ExpDays} ORDER BY stm.exp_date
-
Yes, great info Nadim, thank you. Qualifying the tables with QCTL would mean that you could not run that report on the TRAIN scheme without changing the SQL, right ?
I wonder why CC doesn’t run this function to rebuild the synonyms when they upgrade.
I ran that command (sqlplus crystal/report@maxqprod) from command prompt to start SQLPlus and then ran the statement exec adm_pkg.bind_to_quantum_schema(‘QCTL’); to rebuild the synonyms.
So i always use QCTL when i am working in Crystal Reports (or TRAIN if accessing the training database where i can manipulate test data). Is there an advantage to using CRYSTAL instead? Is there a similar schema to access for the TRAIN schema ?
-
John- i don’t know why that error appears.. i copied your code into CR and it ran fine for me. double check the spelling on each table in the FROM on your select.
Also- you have
roh.rst_auto_key = ‘2’ and
rst.status_type = ‘Closed’
You don’t need both- the status_type=’Closed” is the better way than the rst_auto_key, as this can change and then your report won’t work. In the example I posted above, the pst_auto_key was commented out with —
-
Hi John- just to further improve this- instead of hard-coding the PST auto_key for the closed status (this can possibly change or new types of closed status codes can be created), I would suggest changing the where clause like this: (note that i posted my example with PST_auto_key=7, instead of 1 from your example)
SELECT max(psh.entry_datetime), poh.po_number, poh.poh_udf_001, pst.pst_auto_key, pst.status_code
FROM po_header poh, po_status pst, po_status_history psh
WHERE
poh.poh_auto_key(+) = psh.poh_auto_key and
poh.pst_auto_key = pst.pst_auto_key and
—poh.pst_auto_key = 7
pst.status_type = ‘Closed’
group by poh.po_number, poh.poh_udf_001, pst.pst_auto_key, pst.status_code
order by po_number
In our case, we added an additional status of Cancelled, which is also a closed status. Plus we deleted and add status, so that our current Closed status has a pst_auto_key = 7
-
If you want to reproduce the data that the stock search / browse shows, that query is in FUNCTION STOCK_BROWSE, in the QC_IC_PKG. You can grab the SQL out of the dynamic SQL, edit and adjust it to replace the parameter variables with your own values. Let me know if you need help doing that.
-
John- you were correct to think about using MAX. This is a grouping function, so you need a group by in the select, like this.
SELECT max(psh.entry_datetime), poh.po_number, poh.poh_udf_001, pst.pst_auto_key, pst.status_code
FROM po_header poh, po_status pst, po_status_history psh
WHERE
poh.poh_auto_key(+) = psh.poh_auto_key and
poh.pst_auto_key = pst.pst_auto_key and
poh.pst_auto_key = 7
group by poh.po_number, poh.poh_udf_001, pst.pst_auto_key, pst.status_code
order by po_number
Hope this helps !
-
Pietro- i wanted to print the signature of a user selected from a drop down list. I can get the sysur info for currently logged in user, but sometimes we wanted to print this for someone else who is not in the office for example.
This is a crystal report Sales Order C of C that i created and it wanted to use the CRI_auto_key to query the Crystal reports security to see who is allowed to run the report and only allow those signatures to be applied. The thinking was that anybody on the list could print it with another signature from the list.
But we decided that printing the signature on a C of C was not correct, only the name can be filled in. This relates to my other forum topic of how to get the CRI_auto_key from within the report.
-
Thank you Nadim- that works. Having the report name hard coded to match the name in Quantum isn’t ideal, but this will work.
-
Nadim- yes i meant CRI not RPI auto_key…
-
Thank you Nadim.. this worked and i was able to insert the signature image for the user who runs the report.
Do you know how to get the rpi_auto_key for the current report inside of Crystal Reports ?
-
Pietro. Thank you, I have the query to grab the user info but don’t know how to reference the current cri_auto_key for the report being run.
-
Yes, that is a good idea. I was hoping to grab them from the database, like in Forms Designer forms…
-
Pietro – on 10.9.38, there are no color schemes that are actually dark mode. There are still large areas of bright white. Maybe in new versions there are better options ?
-
Hi Rob- take a look at the “Shift Relative to” when you right click the item in the design window- this tells how to position and “push down” sections as other sections grow. I would look at all the memos, regions etc and see what the shift relative to settings are.
-
If you have the PN list in the excel, you could query the parts_master for all PN matching those and having the max pnm_auto_key (the most recently added). That should give you the pnm_auto_keys to archive, after you do a select and review them, and maybe also select and review any stock lines for that list of pnm_auto_key.
The “where PN in(xxx,xxx,xxx,xxx)” and “where pnm_auto_key in(xxx,xxx,xxx)” is your friend here !
-
That’s correct- but that only will work for WO that are currently open and Main component is reserved (received and NOT on repair order). The view will work for completed WO, open WO and open WO with Main Component out on repair.
-
select * from view_spb_wo_maincomponent
where woo_auto_key=379
will return lots of info, including serial_number, from a WO using it’s woo_auto_key. If you look at the code behind the view, you will learn how it works and how the tables are connected.
-
From the Help file search for “unallowable”: This is in the Global, GL Utilities menu
Toggle Unallowable
Toggles the current value of the “Unallowable” true/false flag. The “Unallowable” flag was implemented as a part of custom programming. When the “Unallowable” flag is true, an error will occur when creating a quote or billing for the work order. Access to this option is controlled with security rights.
-
Srini- your query is from the audit_trail table, which is where Quantum stores changes made to fields. Which fields are audited and stored here are completely up the developers at CC. They can add and remove fields from here with different versions of the software. I suspect that you upgraded from a earlier release of Quantum on Jan 13 of 2017. The version you upgraded FROM was auditing changes for more fields than your current version of Quantum is doing. Your current version only audits the mkt_price and unit_price field changes.
What Nadim is referring to with the “Activate Stock Line in Accounting Audit Trail” setting is that having this turned on, will allow you do the search “stock audit” function. This is one of the searches on the drop down arrow next to the Parts Management icon. Having the option checked tells Quantum to populate the data used in this search. You should be able to run that search with no parameters and see all stock changes that impacted the GL.
Hope this helps !