nadim
Forum Replies Created
-
Do you have Screen Designer? If yes then it may be possible to scrip it.
-
You can find GetData as well as other goodies in the code toolbox under the Language tab as in the attached screenshot.
It is basically a CC built function.
-
You’re absolutely right Mike. John will get the same error if he runs the report from inside TRAIN.
Crystal has read-only access (select). Therefore it is less risky to share the password. Whereas QCTL’s elevated privileges can wreak havoc to the Quantum db structure. So you do not want to share that password.
CRYSTAL has “SELECT ANY TABLE” privilege. So you can access TRAIN from there although you will need to prefix the schema name.
-
I really, but really, suck in accounting but I am assuming that all ARD for the same ARA would have the same invoice number.
If this is false then stop reading. Otherwise I would suggest to have a list containing every ARA and its INH. Then instead of linking ARD to INH, you would link ARA to INH.
This is a long shot as it is all abstract and Martian (but fun) to me:
WITH ardp AS (
SELECT DISTINCT
ard.ara_auto_key,
inh.inh_auto_key
FROM
ar_detail ard,
invc_header inh
WHERE
inh.inh_auto_key = ard.inh_auto_key
)
SELECT
ara.ara_auto_key,
ara.gla_auto_key,
ara.cmp_auto_key,
ara.entry_date,
ara.ar_control_number,
ard.amount,
cmp.cmp_auto_key,
inh.inh_auto_key,
inh.invc_number,
inh.dpt_auto_key,
dpt.dept_name
FROM
ar_account ara,
companies cmp,
ardp,
ar_detail ard,
invc_header inh,
department dpt
WHERE
ard.ara_auto_key (+) = ara.ara_auto_key
AND cmp.cmp_auto_key (+) = ara.cmp_auto_key
AND ardp.ara_auto_key (+) = ara.ara_auto_key
AND inh.inh_auto_key (+) = ardp.inh_auto_key
AND dpt.dpt_auto_key (+) = inh.dpt_auto_key
AND ara.balance <> 0
AND dpt.dept_name = ‘Avmax Spares’
AND ara.cmp_auto_key = 17496
AND ara.ar_control_number = ‘ICS32502’
AND ara.prepay_flag = ‘F’
ORDER BY
ara_auto_key
-
Try this in Crystal:
SELECT
MAX(rsh.entry_datetime),
roh.ro_number,
roh.reco,
rst.rst_auto_key,
rst.status_code
FROM
qctl.ro_status rst,
qctl.ro_header roh,
qctl.ro_status_history rsh
WHERE
roh.roh_auto_key (+) = rsh.roh_auto_key
AND roh.rst_auto_key = rst.rst_auto_key
AND roh.rst_auto_key = ‘2’
AND rst.status_type = ‘Closed’
GROUP BY
roh.ro_number,
roh.reco,
rst.rst_auto_key,
rst.status_code
ORDER BY
ro_number
It should work. This is because the user ID CRYSTAL is telling Oracle that the tables it is querying are owned by user ID QCTL. If you’re new to this, user (or schema) QCTL owns all the production tables in Quantum.
A synonym is in fact an Oracle shortcut to avoid us prefixing a user ID to each table name when logging in as CRYSTAL.
When Quantum is originally installed, a synonym is created for every table owned by QCTL. But as you upgrade Quantum over the years, new tables are created. These new tables do not have synonyms because they did not exist when Quantum was first installed and the synonyms were created.
Ask Tech Support to refresh the CRYSTAL schema. Let me know if they don’t understand what you mean and I’ll escalate it for you.
-
John,
I assume you may be using the CRYSTAL Oracle user (schema) to run your report from Crystal Reports. In this case you will need to refresh the synonyms as both the RO status tables in your sql may have been created after your original installation of Quantum.
You will need to log into the CRYSTAL schema using “sqlplus crystal/report@maxqprod”
Then enter the following statement and press enter:
exec adm_pkg.bind_to_quantum_schema(‘QCTL’);
I am assuming that QCTL is your production schema.
Nadim.
-
Craig,
The QA_REQUIRED is a CHAR datatype and not BOOLEAN. The values are: F for None, T for One and S for Two.
You can do something to the code below
var
sMecReq : string;
begin
sMecReq := GetData(‘WO_TASK’, ‘WOT_AUTO_KEY’, ‘MEC_REQ’, WO_TASK[‘WOT_AUTO_KEY’]);
if (sMecReq = ‘T’) and
((WO_TASK[‘QA_REQUIRED’] = ‘T’) or (WO_TASK[‘QA_REQUIRED’] = ‘S’)) then
regMyRegion.Visible := True
else
regMyRegion.Visible := False;
end;
Nadim.
-
Indeed. This will happen if “Auto-Open task if WO is Open” is turned on in your WO Settings.
-
Cyndy,
The status in question is the Task Status and not the WO Status. You can access from the Status tab for each task. You can add/edit/delete from there.
As to why the status is open, I suppose you have “Auto-Open task if WO is Open” setting turned on. This is located in WO Control.
Nadim.
-
You mean CRI_AUTO_KEY?
-
A workaround would be to query the report name within your query.
So if your report is called “My Report” then have something a simple as:
select CRI_AUTO_KEY from CR_ITEM where NAME = ‘My Report’;
If you are using Command for your code then you can have a separate query that is not linked to the main query.
-
Try:
SELECT
*
FROM
TABLE ( CAST(rpt_gl_pkg.get_income_statement(
p_syscm => 1,
p_glp => NULL,
p_date_from => ’20-AUG-2015′,
p_date_to => ’20-AUG-2015′,
p_segment_nnumber => -1,
p_seg => NULL,
p_group_by => 0
) AS t_income_statement_list) )
This will work in Interactive SQL although you may need to remove the argument names in Crystal as I don’t know if Crystal supports named notation (e.g. “p_xxx =>”).
Use Ctrl-F10 in the Income Statement report to know what each value can be. For example radio button Group By will be 0 for Account Type and 1 for Account Group. This is represented by the p_group_by argument.
-
Are you using Departments and Divisions and if yes, do they represent the warehouses? In later 10.10 versions, the columns Department and Division have been added to the SM grid but they don’t show by default. You will need to restore default setting for the grid. This can be an indicator and you can also color code each row based on department and/or division by using the 10.11 Color Management functionality.
-
In Crystal you should be able to query an object table type the same way as a database table. So you can use something like:
“where fiscal_start >= {?START_DATE} and fiscal_end <= {?END_DATE}"
-
Query it like any other field. From the Report Explorer, drag and drop the field into the report and that’s it.
Note that image BLOB support was introduced sometime after Crystal 8 or XI. It will not work in earlier versions of Crystal.
-
Yep, I thought so.
Two other tips since you guys are starting on templates.
One other thing that may help is that you can link a set of template to several models. So you may have a set of templates for a 412 and another set for a PT6T-3 and a third set for a PT6T-9. When you have a 412 in a WP you want to be able to access the templates for both the PT6T-3 and PT6T-9. In this case, in both the PT6T templates, you click on Additional Model and you add the 412.
In case you have a non-airworthiness job card that is common for all work packs such as cleaning or defuelling then you can create a model called “All Airframes”. You would then create a template linked to model “All Airframes” which will contain these cards and then make “All Airframes” an Additional Model for all your airframes.
-
Craig,
Two procedures attached: 1- SP_UPDATE_WOS_FROM_WQD which is the main procedure and 2- SP_UPDATE_WOT_STATUS which is a supporting procedure.
The 2nd procedure will actually change the job card status based on the parameters passed by the first procedure. So it is the first procedure that needs to be setup in Event Manager.
I have created them for 5 years ago so can’t remember all the details but a quick read would say:
If WQD is changed to Approved then
If Job Card status is Q SENT then change the job card status to LAUNCH
If Job Card status is A/W APPROVAL then change the job card status to WIP
Else if WQD is changed to Deny then
Remove all BOM reservation
Change Qty Needed to zero
Change Job Card status to Q DENIED.
There are other validations that happen such as making sure that a denied JC does not have any issued material or that the statuses exist. Any error will be written to the table SYY_ERROR_LOG. There is another alert email that is sent out daily with SYS_ERROR_LOG entries.
The job card statuses are hard coded but you can replace them with yours.
You can chat with Aybek at Flying Group and Jens at Atlas Air Service about these procedures. I have set up the procedures at their facility. They are both QUE members.
Nadim.
-
Yes but indirectly. You need to create a WO/WP Quote first.
In the quote, click on the PN line and then on the Vendor button. This is on a line by line basis.
If you want to link several PNs to a single vendor RFQ then you need to click Global/Select Items for One Vendor.
-
Carli,
There are no limits whatsoever. What do you mean by “can’t seem to add anymore”? Is there an error message or maybe they were added by you can’t see them (low fetch size)?
Nadim.
-
Carli,
There are a couple of settings you need to apply which should help you.
Set the Base Condition Code
In the Condition Code table, change the Base Condition for the Unserviceable condition code to “AR – As Removed”. This should be done for all condition codes you deem as being unserviceable.
In the Shop Control browses, look for a column labeled “Part Qty Ava (no AR)”. This column will show all available parts excluding unserviceable parts.
Prevent AR parts from being reserved
If you never reserve unserviceable parts for WO/WP then there is a setting that will prevent users from accidentally reserving unserviceable parts. In WO Control BOM tab: Make sure that “Prevent AR parts from being issued to BOM” is checked. This will stop any part that has a base condition code of “As Removed” from being reserved to a WO/WP. You can still reserve in the rest of Quantum such as RO.
Update the form to show the correct Qty
For the form, you need to update the requisition for Qty Available using this code:
var
q : TOracleDataset;
begin
q := TOracleDataset.Create(nil);
q.SetSession;
q.sql.text := ‘select QC_IC_PKG.GET_QTY_AVAIL_NO_AR(‘ + WO_BOM[‘PNM_AUTO_KEY’] + ‘) qty from dual’;
q.Open;
Text := q.FieldByName[‘QTY’].AsString;
q.Close;
q.Free;
end;
-
Quite simple. The BOM activity must be Replace or Turn In.
Turn-in the core.
Click on Parts Mgt/Shipping/Return to Customer
A Shipping Order header will popup. Click OK.
A grid with all available turned-in parts will popup. Select the part(s). Click Done.
The Shipping Order containing the selected parts will open.
From here, it is standard Shipping Manager processes.
Notes:
Doing a Turn-In for a Repair and then changing the activity to Replace or Turn-In will not allow the user to ship the unit to the customer. I believe that’s an oversight. I have seen instances where a customer gives the OK to the planner for a Repair but then due to pricing or long TAT, the customer decides instead replace the unit.
If the user escapes the popup in step 4, an empty Shipping order will still be created. From the empty Shipping Order, the user can add units from the linked WO/WP. When clicking add the user will be asked whether to select the main component. By clicking No, the popup from step 4 above will display. The idea here is that the planner can create an empty Shipping Order. Once the mechanic returns the cores to stores, the storesperson can add the units to the Shipping Order without needing to access the WP/WO.
-
It is a pity for the Shipping Manager. There is a very clean process which allows a user to add turned-in parts to a shipping order. This will in turn provide you with all the necessary shipping/packing documentation and give you access to reporting on these shipments.
In the BOM click on Parts Mgt/Shipping/Return to Customer.
Otherwise the most common way to do this, when not using this functionality, is to create a zero priced SO.
In any case I can provide you with a demo for this if you need one.
-
Two questions:
Are you returning a main component or are you returning customer supplied material?
Are you licensed for the Shipping Manager module?
-
Actually the way you wrote AUDIT_TRAIL leads me to believe that you are actually referring to the database table rather than the Quantum functionality.
There is a view called STOCK_AUDIT. If the view does not have the data you require then this means that stock audit is not activated.
In Inventory Module Settings you need to check “Activate Stock Line in Accounting Audit Trail”.
Screenshot attached.
-
My experience is better to use simple statuses that are task related as opposed to task type related. So something like Open, Close, Cancel for operational tasks. Quoting, A/W Approval or Rejected for commercial tasks. You would use reports and dashboards to give you a view of the current phase of the WO. Usually, the task that is open is the current task. Or the latest open task in case several tasks are open. With the Automatic Task Statusing turned on then as soon as a task is closed the next one will open automatically. That is unless you also want to calculate queue time which is the time between a task closure and the start of the next task.
The advantage is that users don’t need to have a list of tasks to chose from and therefore easier training and less probability of mistakes. You have a fewer barcodes to deal with if using barcoding. But most important, your reporting is enhanced as it becomes more dynamic. As your capability grows and add new Tasks, you do not need to add new statuses and therefore you don’t need to modify existing reports and hardcode new statuses.
In case you are using a WIP Board, reporting against the WO Task Status table would give you TAT per task, per delay or per gate (milestone). The gate would be the duration of 1 or several grouped tasks.
-
Did removing the user from the Theme fix it?
-
This is available in Stock Audit.
The Audit Trail is more about the unit history, e.g.: Unit received on PO, Unit went out on RO, unit received on RO with differrent PN or SN. Therefore the Audit Trail will span different stocklines for a single Series Number.
Stock Audit is about changes to a single stockline.
-
For the MRO shops using Global/Certificate in Shop Control and Aircraft Services: The new imaging API will break it. This probably means that Print to Archive in all modules also does not work although I did not test.
If you are using CAMP Connect, worker.exe will no longer attach the Task Cards to the Job Card.
For those using Mobile Tech, MT will not work with the new API.No login possible.
CCI will be installing two separate APIs on my server to test. The new one for new Imaging and the old one for Mobile Tech. This should fix all the issues above. I will test using customer processes.
-
Thiago,
In a later 10.10 version, I could not import tables in ocDIA. And when I could, it would sometimes duplicate columns from other tables or create the table without columns. I thought it was related to the client installation. But apparently not according to your post.
Having said that, I was still able to add a new table. Not anymore it seems.
Nadim.
-
This will happen when there is an update with SQL or when an updated columns is not individually managed by the stock audit trigger.
-
Jake,
The form icon means that Screen Editor was used. Just right click on your form and select “Show Hidden”. Once the field is visible then right click again and uncheck the “Hide” menu item.
Nadim.
-
In the OnPrint event:
label.font.italic := True;
-
Dimitri,
If updating parts master from Interactive SQL then the pricing will not disappear. This is because the Quantum session records the username and passes it to the trigger.
If you need to to update anything from SQL developer then you need to log into to Quantum programmatically.
To do this just wrap the below code around the DML code:
DECLARE
c SYS_REFCURSOR;
v_pwd VARCHAR2(20);
v_sysur NUMBER;
BEGIN
OPEN c FOR
SELECT pwd,
sysur_auto_key
FROM sys_users
WHERE user_name = ‘SYSDBA’;
FETCH c INTO v_pwd, v_sysur;
CLOSE c;
–Need to validate password TWICE
c := qc_sc_pkg.Validate_password(‘SYSDBA’, v_pwd);
CLOSE c;
c := qc_sc_pkg.Validate_password(‘SYSDBA’, v_pwd);
CLOSE c;
–ENTER CODE BELOW
–USE “SYSUR_AUTO_KEY = V_SYSUR” when required
UPDATE parts_master
SET ic_udf_001 = ‘123’
WHERE mfg_auto_key = 123;
END;
This will work from any Quantum or DIA schema without the need of a schema prefix. If using SYSTEM (you should not) then you need to prefix the schema name and add a synonym for qc_sc_pkg.
-
From 12.1, there is a new API which uses the Facebook developed GraphQL (https://graphql.org/). This is a true web service based API as opposed to the current procedure based API. It uses a single endpoint for all API requests (unlike RESTFul API).
Advantages: https://www.howtographql.com/basics/1-graphql-is-the-better-rest/
-
Creating a procedure has the advantage of making the code reusable in the future although the procedure in this thread does not have any IN parameters. To reuse, the user will have to overwrite the procedure. In this case, the effort involved is the same as copying and pasting an UPDATE statement as Dimitri mentioned.
Below is the quick and dirty statement based on what Dimitri suggested. I have removed the schema prefix as it is not necessary if executed from Interactive SQL. The schema prefix would have raised an error if executed (tested) from the training database (which you should always do first).
However DO NOT disable the triggers. They are there to provide some automation and to ensure data integrity. Yes, they are a pain sometimes. In this case, they most probably will not be (unless you have stock imbalance or missing SN from serialized parts). Plus the triggers will ensure that your change is captured by the stock audit.
UPDATE stock stm
SET stm.loc_auto_key = (SELECT loc.loc_auto_key
FROM location loc
WHERE loc.location_code = ‘NEW LOCATION’)
WHERE stm.loc_auto_key = (SELECT lo.loc_auto_key
FROM location lo
WHERE lo.location_code = ‘OLD LOCATION’)
AND stm.pnm_auto_key IN (SELECT pnm.pnm_auto_key
FROM parts_master pnm
WHERE pnm.mfg_auto_key =
(SELECT mfg.mfg_auto_key
FROM manufacturer mfg
WHERE
mfg.mfg_code = ‘MANUFACTURER’))
AND stm.historical_flag = ‘F’
-
Should be Work Launch date but only when applied from template.
-
Brandon,
Attached is the one from CC. You will need to add and/or remove processes as applicable for your company.It is from 2017 and does not have the newer modules.
Nadim.
-
It all depends on whether you are using the Receiving/Inspection module which, as its name suggests, is used to inspect the receipts, some of which may end up in Quarantine.
In any case, whether you are using Quick Receiving or the RI module, you still need to return the item to the vendor should the vendor not rectify the finding. So receiving is still required.
Use the RI module with clever disposition codes. The status could be something like Hold. The disposition code can be something like DOA (dead on arrival), MPW (missing paperwork) and so on. This will also allow you to report on receiving findings, categorise the rejections, calculate time to resolution and report on vendor reliability.
Event better if you have Contact Manager with it as you will able to track the whole exchange with the vendor.
-
You will need to do some reading and testing. So have a fresh copy of your production database available in training.
The billing group will allow you to automate (to a certain degree) the quoting process. Use the help file to learn about billing groups. It is under Chapter 13 Shop Control. This applies for both quoting and billing.
You will need to group cards by billing groups if your WO has several pricing schemes. For Labor you will probably use Skill Hours x Labor Rate.
Add each group of tasks that need approval to a separate quote (Chapter 25 – Work Packages – Quoting/Billing). You may need a workflow. For example each job card that is out on approval may have an open status “A/W Approval†with block activity turned on. Once a card is approved, you can then change the status to “Approvedâ€. This can be automated with event manager although not out of the box.
Once the group of job cards are completed, you can convert the quote to a billing. Make sure that the checkbox “Partial Billing†is ticked. The default behavior for Partial Billing can be set from WO Control. Also in WO Control, there are several option in the Quotes/Billing tab that will affect the quote/billing. Have a read through them. You can refer to Chapter 13 – Shop Control Setup.
I know that Canada has its own way of calculating sales tax based on provincial and national tax. I understand that Quantum supports it although I don’t have a clue how it works. Hopefully you won’t need tax or someone else could help.
-
MT takes the images as png. png images don’t resize in document imaging.
Why? I suppose that’s my next question to CC.
-
You’re in the right place then. We are all at your service. 🙂
What are the milestones for payment? Do you use billing groups? Are you using accounting? Most importantly what are your internal processes for this kind of approach.
One important question: You know how to use the WP billing? Otherwise it may be near impossible to train you in the basics here. But we can help in the process.
-
Starting from the later 10.10 versions, you can set both the companies and the sites as Historical.
-
Keep smiling Nikki and thanks for all your quality documentation.
-
In a normal install, you should have a D:RecreateSchemaCOPY_LIVE_TO_TRAIN_DP.bat
You will need to execute this batch file. That’s all there is to it.
If the program gets stuck at ‘Waiting for users to disconnect…’ then this means someone or something (barcode) is still connected to the TRAIN schema.
-
Patricia Manea wrote:
Hi do you know how to do this
May be. I can’t say before seeing the specs. But Oracle SQL has some pretty advanced analytical functions that should cover most scenarios. It can be used in the Crystal SQL parameter to generate the graphs. Then it is a matter of event manager emailing a pdf.
-
Is the data entry a work around for your reporting? To put it in context, I have companies that need to show on the job training on their employee logbooks so as the employees are able to comply with some type minima to renew their certification.
The request was similar to what you required. They also needed to add 30 minutes Inspection skill to any user that released a component i.e. signed the EASA Form 1 or the 8130.
Adding fake labor entries was not tolerated as they had to comply with Sarbanes-Oxley. Labor entries create GL entries.
In their case this was resolved by reporting. The SQL would automatically add the required extra hours to the employee logbook.
Otherwise, as Dave suggested, an event manager procedure would resolve it.
-
You have two choices, either the PIVOT clause or the DECODE function.
With the PIVOT clause you need to have an aggregate function such as COUNT. In this case your cells will show ones and zeros.
In your case, the DECODE function over a skill listing subquery works better. It will display “X” whenever the user has a skill.
SELECT
–One DECODE function per skill
user_name,
Decode(description, ‘INSPECTOR’, ‘X’) inspector,
Decode(description, ‘TECHNICIAN’, ‘X’) technician,
Decode(description, ‘PAINTING’, ‘X’) painting
FROM (
–Get the default skills from SYS_USERS
SELECT sysur.user_name,
wok.description
FROM sys_users sysur,
wo_skills wok
WHERE wok.wok_auto_key = sysur.wok_auto_key
UNION ALL
–Get the user skills from WO_EMPL_SKILLS
SELECT sysur.user_name,
wok.description
FROM sys_users sysur,
wo_empl_skills wes,
wo_skills wok
WHERE wes.sysur_auto_key = sysur.sysur_auto_key
AND wok.wok_auto_key = wes.wok_auto_key)
ORDER BY user_name
-
Craig,
This code will only work on Tools that are checked-out (i.e.: reserved). As your original code used the Sock Reservations table then one would assume that this was your requirement.
Once a tool is checked back in, the stock reservation record is deleted as opposed to changing the qty_reserved down to 0.
If you print this task sheet after the task is completed then you would need to use the WO_TOOL_HISTORY table.
Let us know your requirement and whether you need further help.
Nadim.
-
Are the tools still checked-out against the task?
-
Patricia,
Is there a reason why the math in Excel is not done in SQL or in Crystal?
Once all is in Crystal then Event Manager can send you the completed graphs with the report.
Nadim.