nadim
Forum Replies Created
-
The issue only happens to BOM items.
Back when I got the sql from TS, I was too much in a hurry to wait as t the “Why?”. Maybe I should ask now.
-
Kristen,
Having you upgrade is the standard TS answer. In fact the upgrade will not resolve it. The sql below will give you a list of the PNs, templates and tasks that are causing the problems.
Two options:
1- Delete the PN from the BOM and add it again
2- Have your List Price update SQL ignore the list price of these BOM items.
Even if you clean up using Option 1, you’re not out of the woods. New PNs may end up having the same issue.
Nadim.
SELECT PNM.pnm_auto_key,
PNM.pn,
OPM.operation_id,
OPT.SEQUENCE
FROM operation_bom OPB,
operation_tasks OPT,
operation_master OPM,
parts_master PNM,
operation_master OPM_OPB,
model MDL
WHERE OPB.opt_auto_key = OPT.opt_auto_key
AND OPT.opm_auto_key = OPM.opm_auto_key
AND OPT.opm_auto_key <> OPB.opm_auto_key
AND OPB.pnm_auto_key = PNM.pnm_auto_key
AND OPB.opm_auto_key = OPM_OPB.opm_auto_key
AND MDL.mdl_auto_key (+) = OPM.mdl_auto_key
-
Most of them are functions that return cursors or nested tables. They are located in packages with names that start with RPT. So for example the inventory listing is RPT_IC_PKG.INVENTORY_LISTING.
-
Strange behavior.
In any case to reactivate an inactive user, you will have to create a new user with the same username. Quantum will then ask you whether to reactivate the user.
-
James,
It will not be a Forms Designer fix.
If the parts are already allocated to an SO on receiving then you can use the “Split SO Qty on Receiving functionality” in Purchasing Control.
If you want to print the label after receiving then you need to split the stockline (SL Transfer) , reserve the items and then print the label. This solution is kind of long winded.
Nadim.
-
James,
There is some confusion here. STD SM Label is used to print the shipping label (i.e.: Company address label) and not PN.
Your code shows “PN[‘STM_AUTO_KEY’]â€. The pipeline PN comes from either the PN Label or PN Barcode Label. Both are located in the Inventory Control folder.
Your code is correct and based on your example, it will return 3 customer PO numbers. But since you are not looping through the result then you will always see the 1st customer PO number.
I understand from your response to Ab that you do this from the SO. So you are inspecting the stockline I would suppose. If that is the case, the “current†record is not the SO but the actual stockline. This is why you will be returned with all orders unless you split the stockline.
If you company uses Shipping Management then your scenario will work by using the “STD SM Parts Tagâ€.
Nadim.
-
I don’t think you can as there really is no uniqueness when you have multiple qty for a single stockline.
If the parts are already allocated to an SO on receiving then you can use the “Split SO Qty on Receiving functionality” in Purchasing Control. This would resolve it.
-
Leonel,
I did something similar for your EEJC LBG site. They had a huge T&C which took a full A4 page if a small enough font size was used.
Basically you need to setup a summary band which will have Print On New Page = True. In that summary band you add a memo with all the text. The summary band will then always print as the last page. You also may need to add some code to stop the header and footer from printing on the last page.
This is hard coded but then the T&C don’t really change over the years.
Nadim.
-
Faye,
You don’t need to but it is necessary for the workaround of linking select the Ship Via Account.
Another workaround is to have the Ship Via Account in a Header Clause. One for each account and then the user selects.
Nadim.
-
Jesse,
Here you go for every two hours. With this it should be easy to figure how it works, build the second schedule and more.
FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=8,10,12,14,16;BYMINUTE=0;BYSECOND=0
I believe that you can omit BYMINUTE and BYSECOND when using top of the hour but I never got around to testing it. That’s for you to let us know. 🙂
Nadim.
-
What is the schedule you need as a custom schedule?
-
I suppose these must be non-serialised parts.
No you cannot combine them as this will defeat the purpose of Quantum’s auditing features. Just by receiving separately then you have different receive date/time and the SL can only have a single date/time.
-
Hi Sally,
This statement should give you the active manual with MFG based on the Manual ID. Is this what you are looking for?
Nadim.
SELECT MNL.manual_id,
MNL.description,
MFG.mfg_code,
MTP.type_code,
MNL.revision,
MNL.revision_date,
MLS.status_code
FROM manuals MNL,
manufacturer MFG,
manual_status MLS,
manual_type MTP
WHERE MNL.mls_auto_key = MLS.mls_auto_key (+)
AND MNL.mfg_auto_key = MFG.mfg_auto_key (+)
AND MNL.mtp_auto_key = MTP.mtp_auto_key (+)
AND MLS.isactive = ‘T’
AND MNL.manual_id = ‘525ADVD12’
-
Then Plan C would be the messy work around.
Issue the turned in unit.
Add the same PN to the BOM
Turn it in a second time as a Replace
-
salesforce.com can be accessed using a SOAP API. Check my presentation from this year’s QUE Group on how to exchange information between Quantum and UPS using a SOAP 1.1 API. All the code and steps you need are in there.
-
Try this by replacing the dates below with yours. It will return earliest date of when a security setting was applied to a user. But bear in mind that security was not always tracked in audit trail.
SELECT MIN(ADT.STAMPTIME) STAMPTIME, SYSUR.USER_NAME
FROM AUDIT_TRAIL ADT, SYS_USERS SYSUR
WHERE SYSUR.SYSUR_AUTO_KEY = ADT.SOURCE_AK
AND ADT.SOURCE_TABLE = ‘SYSUR’
AND ADT.SOURCE_FIELD = ‘CLS_AUTO_KEY’
AND TRUNC(ADT.STAMPTIME) BETWEEN TO_DATE(’01-SEP-2015′, ‘DD-MON-YYYY’) AND TO_DATE(’30-SEP-2015′, ‘DD-MON-YYYY’)
GROUP BY SYSUR.USER_NAME
ORDER BY 1,2
-
Rolando,
Once you reconcile the sub-WO, the unit is back in the BOM of the main WO. AT this stage, are you able to edit the BOM and change the activity to Replace? If yes then do so. You then Turn-In the scrap part and issue a serviceable part. If there is a need then it will show in PM.
Or another other option is to make a Main Component WO Exchange in the sub-WO. But I believe that in this scenario, the part will not show in Purchase Management. You will need to test.
Nadim.
-
Open any Quantum screen form in Edit mode (e.g.: Inventory Control/Edit). Then press Ctrl-F7 or Ctrl-F8. If it asks you for a keycode then you’re not licensed otherwise you are.
-
Ab,
You can do this with screen scripter if you have the module.
This will stop the user from creating a new receiver. But it will not stop this user from approving and finalizing a receiver that was created by someone else. So not perfect.
Nadim.
-
OK, did not see that part. Try this:
SELECT SPN.SALESPERSON_CODE,
NVL((SELECT SUM(SOH.TOTAL_PRICE) FROM SO_HEADER SOH, COMPANIES CMP
WHERE SOH.SPN_AUTO_KEY = SPN.SPN_AUTO_KEY AND EXTRACT(YEAR FROM SOH.ENTRY_DATE) = EXTRACT(YEAR FROM SYSDATE) AND CMP.CMP_AUTO_KEY = SOH.CMP_AUTO_KEY AND CMP.CLC_AUTO_KEY = 2),0) TOTAL_SO_ENTERED,
NVL((SELECT SUM(INH.TOTAL_PRICE) FROM INVC_HEADER INH, COMPANIES CMP
WHERE INH.SPN_AUTO_KEY = SPN.SPN_AUTO_KEY AND EXTRACT(YEAR FROM INH.POST_DATE) = EXTRACT(YEAR FROM SYSDATE) AND CMP.CMP_AUTO_KEY = INH.CMP_AUTO_KEY AND CMP.CLC_AUTO_KEY = 2), 0) TOTAL_INVOICED
FROM SALESPERSON SPN
I don’t have the data to test but it should work. The code may look overwhelming but the optimizer performance cost index is 3 and cardinality is 1 which is the minimum possible (i.e.: best performance) you can get when querying 3 tables.
-
Try this:
SELECT SPN.SALESPERSON_CODE,
NVL((SELECT SUM(TOTAL_PRICE) FROM SO_HEADER WHERE SPN_AUTO_KEY = SPN.SPN_AUTO_KEY AND EXTRACT(YEAR FROM ENTRY_DATE) = EXTRACT(YEAR FROM SYSDATE) ),0) TOTAL_SO_ENTERED,
NVL((SELECT SUM(TOTAL_PRICE) FROM INVC_HEADER WHERE SPN_AUTO_KEY = SPN.SPN_AUTO_KEY AND EXTRACT(YEAR FROM POST_DATE) = EXTRACT(YEAR FROM SYSDATE) ), 0) TOTAL_INVOICED
FROM SALESPERSON SPN
Nadim.
-
This will only work if Form.Dataset.State = 2 i.e.: is in Edit mode as opposed to Insert mode.
Try it.
-
Tony,
Do NOT hardcode the users in your code. What if a new user needs the access , then you will have to go and update the code.
What you need to do is find an unused checkbox in the user record and use it to mark the users who are allowed to change the group code. You can use Screen Designer to change the label. In my example, I will use the checkbox “Contractorâ€. So anyone marked as contractor can change the group code.
I prefer to use Enabled instead of ReadOnly. The reason being that Enabled will stop all interaction with the control and will grey it out. ReadOnly will allow the user to access the lookup field.
Try this:
procedure FormShow(Sender: TObject);
var
q: TOracleDataset;
x: string;
begin
if Form.Dataset.State = 2 then
begin
q := TOracleDataset.Create(nil);
q.Session := Form.OCSession;
q.Sql.Text:= ‘SELECT NVL(CONTRACTOR,”F”) CONTRACTOR ‘ +
‘FROM SYS_USERS ‘ +
‘WHERE USER_NAME = QC_SC_PKG.GET_USER_NAME’;
q.Open;
x:= q.FieldByName(‘CONTRACTOR’).AsString;
q.Close;
if x = ‘F’ then
Form.lkpGroupCode.Enabled := False;
end;
end;
-
Tyler,
The first error (http://imageshack.com/a/img921/8783/uxJTSa.jpg) is usually related to DEP. You need to turn on Date Execution Prevention for that machine and add rf.exe as an exclusion. Usually you would also have Quantum.exe and QCSYSMOD.exe in the exclusion list.
Maybe the second error is related.
Nadim.
-
Spoke to Nic Hanna about this. He is in agreement that it is a popular requirement. He has agreed to add an enhancement for this issue.
A condition code will be inked to each WO Header Workscope. When a WO is reconciled, the Workscope CD will be used. If there is no Workscope CD or no Workscope then the default CD will be used.
This will be in a later 10.8 release.
-
Some items are hard coded in the WQD table such Print Part, BG Name for the BG Line. Otherwise in my test, the Billing Method and the Billing Group columns came back empty when I looked at the quote after deleting the BG record.
Now you’re still seeing it but in Crystal. Then maybe it is the Billing Group Master (BGM) that you are seeing and not the Billing Group (BGS). Just a thought.
-
The main issue I observed are users who unintentionally (or rather automatically) reconcile for example a scrap or a check & test unit as OH because they forgot to change the default CD. What I did is to change the default CD to “XXâ€. If a user reconciles as “XX†then screen scripter will raise an error message that then forces the user to think and select the correct CD.
Over the years I have come across several ideas from several companies. The best two:
Link a Workscope (WO Work Type) to a default condition code
Add a CD step to the Release WO wizard
Some people suggested to link the template to a CD. But that is not feasible as some companies would have 1 PN = 1 Template containing all possible repairs.
-
André,
The WQD is linked to the BGS using the BGS_REF column when ITEM_TYPE = ‘Billing Group’.
If the entry from the BGS table was deleted then the info is gone. But you know that. So I suppose there is more to your question. What is it exactly you need?
Nadim.
-
There isn’t. The component will reconcile to whatever is listed as Main Component Out default in the WO Control.
-
Ab,
You’ll need to use the King of Events “Custom SQL and Template” event. The stuff that can be done with this event is tremendous (and fun for the nerds among us).
How to setup is a bit of a long explanation. Hopefully someone has some documentation.
Otherwise we can discuss at QUE Group.
Nadim.
-
Right! Back to the drawing board then.
-
If you prefer to load a file then this is the content of the csv file.
‘DVC_OLD’,’NUMBER’,”,’1′,”,’1′,”,”
‘DVC_NEW’,’NUMBER’,”,’1′,”,’2′,”,”
-
The DVC represent the auto keys therefore they are numbers.
So from the combo box for the Type column, select NUMBER. Don’t worry about the rest, it is for tab delimited. Then click the refresh, reorder and recreate buttons.
-
It’s a 30mn thing. Check the DIA procedure below.
You will need to create a DIA table with two columns.
DVC_OLD Number
DVC_NEW Number
Your csv file will have the old DVC_AUTO_KEY and the new (merge to) DVC_AUTO_KEY.
Test in the training database first.
create or replace PROCEDURE DVC_MERGE
IS
DVC_NEW number;
DVC_OLD number;
BEGIN
qc_trig_pkg.disable_triggers;
FOR R IN ( SELECT EXT.*
FROM EXT_DVC_MERGE EXT
) LOOP
DVC_NEW := R.DVC_NEW;
DVC_OLD := R.DVC_OLD;
update CQ_DETAIL set DVC_AUTO_KEY = DVC_NEW where DVC_AUTO_KEY = DVC_OLD;
update VQ_DETAIL set DVC_AUTO_KEY = DVC_NEW where DVC_AUTO_KEY = DVC_OLD;
delete from DELIVERY_CODES where DVC_AUTO_KEY = DVC_OLD;
END LOOP;
qc_trig_pkg.enable_triggers;
END ;
-
Katelyn,
You have a compilation error. Recreate the procedure without the forward slash.
The forward slash is an execute command for sqlplus. It is not needed for interactive sql.
You also do not need commit. The procedure will automatically commit.
Nadim.
-
Ian,
I would suggest the following:
Create an Exchange PO to the customer at zero value
Receive the PO and reserve the part to the WO
Receive the RO – You will get a message that part will be returned to stock due to existing reservation
Create a PO core return using the BER item as your core
Nadim.
-
Sally,
If you’re using SQL developer that came with Quantum then forget about. It won’t work withe the current java.
Once you download a current v4.1 release, then search for java JDK (not JRE) and download. When you open sql developer for the first time then navigate to the folder where the jdk folder is located and that’s it.
Also the Oracle site will have an version of sql developer that has the jdk included with the install.
Nadim.
-
Had the same issue raised by two customers. They will both be at the QUE Group conference. So I would suggest that this be raised at the round table discussion so it can make it to the QUE Group Custom Programming Request.
The workaround I used is to have the receiver enter the info in SDF fields which are available in the MSU. Then using an event that runs every 15 minutes, the data is moved from the SDF field to the the certification fields.
-
The QC_SCHED_CUST_PKG.SCRIPT_0003_EMAIL_CUSTOM_ALERT is to send custom error messages created in Quantum.
To create your own email alerts, you will need to use “Custom SQL Statement and Templateâ€. You will need to dirty your hands with XSLT and XML. But it is fun.
To send a csv based on SQL then you can use “Schedule Table/View Snapshotâ€. To view a list of custom views then execute:
select * from ALL_VIEWS where VIEW_NAME like ‘VIEW_%’
Some of the views are really cool while some others may just work slow or not work at all. You will need to figure out what information a view displays from the view name.
You can use the view in the same way you use a table in a select statement e.g.: select * from VIEW_PARTS_AVAIL where PN = ‘6688013-2′
Or if you’d rather keep it simple, you can create a Crystal Report and have the users extract their own Excel file from Crystal using Quantum’s integrated Crystal viewer. CC did a great job with this new Crystal viewer.
-
Terri,
In version 10.7.48, the SM Order PT can be sorted by Location, Item or PN.
Nadim.
-
Sorry forgot to write that it should be sorted in descending order. Crystal defaults to ascending.
-
Pam,
Each AP detail entry will have two lines, one for the actual AP and one for the payment (i.e.: a debit and a credit). This is why you have the duplication. Depending on the sorting of the AP Detail, Crystal will use the last detail value for a group as the group footer value. So sometimes the ckd linked line will appear first and sometimes it will appear last. It is the latter case that showed the check number in the group footer and that you need to force. Adding CHECK_NUMBER or TRAN_DATE as a sort value should fix it.
Nadim.
-
Abigail,
Do you have shading in your document? If so then you can try to tick “Hide Shading” in the Print Setup.
Or otherwise in the Global Settings change the Report Shade Color to white.
Long shot because I’m sure that’s the first thing TS did.
Nadim.
-
The SQL you posted above is correct albeit with typos. J
If you are using the grouping, then remove the distinct record flag.
You will need to group on APA_AUTO_KEY, AP_CONTROL_NUMBER or any other unique value.
The wizard creates the report. All the data will be in the detail with the Group Header band containing the value you grouped by as a Title. You can delete it.
Move the data that is unique (cheque number, company name, date, etc…) from the Detail band to the Group Header band (probably GH1).
Suppress the Detail and Group Footer 1 bands.
Should be good to go.
-
Pam,
The data looks duplicated but it is not. You are getting a line for every ap_detail.
If your select does not have any data from ap_detail then just add the keyword distinct after the select.
i.e: Select distinct a.vend_invc_date, …
Nadim.
-
Tony,
Assuming that the UOM will always be LB then:
procedure FormShow(Sender: TObject);
begin
if form.lkpUOMCode.Text <> ‘LB’ then
form.lkpUOMCode.Text := ‘LB’;
end;
procedure BtnOKClick(Sender: TObject);
begin
if (form.txtTrackingNumber.Text = ”) and (form.txtAirwayBill.Text <> ”) then
form.txtTrackingNumber.Text := form.txtAirwayBill.Text;
end;
begin
end.
You should consider using
in the future. It is pretty good and will allow you to add dimensions to all you shipping docs. Nadim.
-
Pam,
You probably have a wrong starting point in the joins.
There should be an inner join between AP_ACCOUNT and AP_DETAIL. Then a left join between AP_DETAIL AND CK_DETAIL with AP_DETAIL being on the left (i.e. starting point).
If you expect to have AP headers with no details and want to show these headers then you can also do a left join between AP_ACCOUNT and AP_DETAIL with AP_ACCOUNT being on the left. Otherwise stick to the inner join as it has less performance overhead.
Nadim.
-
Tony,
Try the script below.
The result expression should have the same datatype. For the same CASE you are returning a number in one case and a string in the other. The else ‘0′ should be else 0. Remove the single quotes to make it a number.
My changes are in red.
Nadim.
select glp.glp_auto_key, glp.description DESCRIPTION,
case glp.glf_auto_key
when 25 then ‘SPARES’
else ‘EXPEND’
end co,
SUBSTR(gla.account_number,1,4) Acct, gla.account_type account_type, gla.account_type_desc DESCRIPTION,
glv.std_tran_type, glv.ytd_tran_type,
case
when glv.std_tran_type = glv.ytd_tran_type then glv.ytd
else (glv.ytd *-1)
end balance,
case glp.glf_auto_key
when 25 then
case
when glv.std_tran_type = glv.ytd_tran_type then glv.ytd
else (glv.ytd *-1)
end
else 0
end SPARES,
case
when glp.glf_auto_key <> 25 then
case
when glv.std_tran_type = glv.ytd_tran_type then glv.ytd
else (glv.ytd *-1)
end
else 0
end EXPEND,
glv.std_tran_type, glv.ytd_tran_type
from gl_period glp, gl_account gla, view_gl_summary glv
where
glp.glp_auto_key = glv.glp_auto_key and
glv.gla_auto_key = gla.gla_auto_key and
glp.description = ‘Mar 2016’
-
Jason,
The only way I could find is to set the discount for the company to 100%.
Nadim.
-
You would need to be more specific. What UDFs? If they originate from the inventory module then I believe 4 of them will display out of the box on the quotation form.
UDF001 – UDF002 – UDF003 – UDF004A
You will need to tick the necessary checkbox in the Print Setting (Global/Print/Quote and then Edit). On the right side there is a “Include on Quote” group box. The last 4 checkboxes represent these UDF fields.
For the rest, you will need Forms Designer.