it
Forum Replies Created
-
We do it as a period cost.
When looking at the day to day, it just isn’t reasonable for us to allocate the man power needed to update every transaction. Especially when you could be touching one sales order 3 times … outbound shipment on SO, outbound shipment on RO and inbound shipment on RO
.. for example.
-
Glad it worked!
-
Bill, since many modules use the wo_operation table (ie lots, stock issues, etc) please confirm you are trying to pul lin the work order number for a work order whose main component was sent out on repair, or for a bom item that was sent out on repair?
-
Bill, in looking at this in more detail I guess I am confused what you are trying to do.
You are doing a look up first by the loc_auto_key then next by the description and finally by the seq.
Each lookup could return different records, yet you update each based on the loc_auto_key .
If you are trying to update locations that match the loc auto key with data from your import file, then all you should have to do is your initial lookup by the auto key, then one update for all three values.
Perhaps some more like this?
PROCEDURE JAS_LOCATION_UPDATES (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;
V_START_LINE NUMBER := DIA_RL_PKG.get_start_line(p_imp) ;
I NUMBER := 0 ;
V_PWD VARCHAR2(100) := NULL;
SYSUR NUMBER;
LOCv NUMBER;
BEGIN
OPEN C FOR SELECT PWD, SYSUR_AUTO_KEY FROM SYS_USERS WHERE USER_NAME = ‘SYSDBA’ ;
FETCH C INTO V_PWD , SYSUR ; CLOSE C;
C := qc_sc_pkg.validate_password(‘SYSDBA’,V_PWD);
CLOSE C ;
C := qc_sc_pkg.validate_password(‘SYSDBA’,V_PWD);
CLOSE C ;
FOR R IN ( SELECT EXT.* FROM EXT_JAS_LOCATION_UPDATES EXT ) LOOP
I := I +1 ;
IF (I >= V_START_LINE) THEN
LOCv:=NULL;
OPEN C FOR SELECT LOC.LOC_AUTO_KEY FROM LOCATION LOC WHERE LOC.LOC_AUTO_KEY=R.LOC_AUTO_KEY;
FETCH C INTO LOCv;
IF C%NOTFOUND THEN
–// DO NOTHING, CLOSE AND GO TO NEXT RECORD
CLOSE C;
ELSE
UPDATE LOCATION LOC SET LOC.DESCRIPTION=R.DESCRIPTION, LOC.SEQUENCE= R.SEQUENCE WHERE LOC_AUTO_KEY = LOCv;
CLOSE C;
END IF;
END IF;
END LOOP;
END ;
-
Welcome James. Many users here use Work Order module. Can you post your specific question please?
-
This is a sample output of the freight matching. This gets written back to the header notes of the matched order. As you can see, there many entries for a single shipment
FRT MATCH DATE: 4/15/2015 8:27:30 AM
OUT RO Tracking #: ZA380R60350243143
Freight Charge: 9
Specified Account #: A380R6
Specified Method: UPS GROUND
Shipped Account #: A380R6
Shipped Method: Shipping Charge Correction Additional Handling
Segment Charged For Fees: 1. BUSINESS JET
FRT MATCH DATE: 4/15/2015 8:27:30 AM
OUT RO Tracking #: ZA380R60350243143
Freight Charge: 0
Specified Account #: A380R6
Specified Method: UPS GROUND
Shipped Account #: A380R6
Shipped Method: Shipping Charge Correction Ground
Segment Charged For Fees: 1. BUSINESS JET
FRT MATCH DATE: 4/15/2015 8:27:18 AM
OUT RO Tracking #: ZA380R60350243143
Freight Charge: 9.25
Specified Account #: A380R6
Specified Method: UPS GROUND
Shipped Account #: A380R6
Shipped Method: Ground Commercial
Segment Charged For Fees: 1. BUSINESS JET
FRT MATCH DATE: 4/15/2015 8:27:18 AM
OUT RO Tracking #: ZA380R60350243143
Freight Charge: 0
Specified Account #: A380R6
Specified Method: UPS GROUND
Shipped Account #: A380R6
Shipped Method: Ground Commercial
Segment Charged For Fees: 1. BUSINESS JET
FRT MATCH DATE: 4/15/2015 8:26:57 AM
OUT RO Tracking #: ZA380R60350243143
Freight Charge: 0.48
Specified Account #: A380R6
Specified Method: UPS GROUND
Shipped Account #: A380R6
Shipped Method: Fuel Surcharge
Segment Charged For Fees: 1. BUSINESS JET
-
Applying freight to the stock lines has always been a huge challenge. Not because of the technology but because of the delay in getting the freight bills then matching them back up to the stock.
What we do is force the recording of the tracking number upon receipt. Of course, our tracking number is recorded at time of shipment as well.
We use BirdDog to screen our freight bills. So, our invoices from FedEx and UPS are sent to them, they find discrepancies and do a charge back for us.
We then receive the bills with corrections for payment in a excel file. I wrote a visual basic program that takes those bills and matches them back up to the data in QC. IE: PO, RO, SO, Stock, RMI, etc. It also shows us what we charged the customer, finds the business unit, employee and sales person as well as what our ship method and account spefied were and then outputs all of this to a new spreadsheet. This allows us to see if shipping instructions were followed, and who might use our account when they shouldn’t.
The program then writes the findings back to QC in the header notes and hidden fields for later reference and reporting. This is the basis for our monthly vendor performance report cards that auto generated to our vendors.
Based on the matches it finds, we then write the amounts to the GL for COGS or and Expense account.
So, in short we don’t try to write the costs back to the stock line. This is a futile effort anyway because the bills come to you so much later and I have found that charges for a single shipment could actually be slip across bills. So, we just write the costs to the appropriate GL buckets after the fact.
-
you can thank me in person at the 2015 QUE Group Conference 🙂
-
The tables are usually pretty well identified in the Data Tree View.
They do not bring in all possible tables or fields, but they do give you the ability to access many other tables and fields through the auto key values by using SQL . So, even if you don’t see a table in the data tree, look for the auto key field for the table you want to access. Case and point, the WO_TASK_MASTER table is not in the pipeline of the main report for the std wo invoice document. But, it can be accessed because under the WO_TASK table, the wtm_auto_key is available.
As you get more familiar with the linking and table names, it will get easier, but knowing the tables available in a pipe line is not the answer you are ultimately seeking.
If you want to pull in a task description, you can do so with this query.. you can run it in interactive sql
select
wo.si_number,
tsk.description,
tsk.long_descr,
wt.squawk_desc
from
wo_task_master tsk,
wo_task wt,
wo_operation wo
where
wt.wtm_auto_key=tsk.wtm_auto_key and
wt.woo_auto_key=wo.woo_auto_key and
wo.si_number=’WO NUM HERE’
If you were pulling this data into a report, you would place a label on the report, and in the “On Get Text” event your code would probably look something like this..
var
q : TOracleDataset;
begin
q := TOracleDataset.Create(nil);
q.SetSession;
q.Sql.Text := ‘select tsk.description from ‘ +
‘ wo_task_master tsk where ‘ +
‘ tsk.wtm_auto_key= ‘+WO_TASK[‘WTM_AUTO_KEY’];
q.Open;
Text := q.FieldByName[‘DESCRIPTION’].AsString;
q.Free;
end;
-
I have posted two queries that show you the tables associated with any field value.
If you have a field name, and you want to know what tables are associated to this, you can run these queries.
All tables are joined using the auto_key fields in the table structures.
I have included them again
select utc.table_name, utc.column_name, subStr(utc.data_type,1,10) data_type, utc.data_length, (select min(pk.table_name) from user_cons_columns ucc, user_constraints uc, user_constraints pk where ucc.constraint_name = uc.constraint_name and pk.constraint_name = uc.r_constraint_name and uc.table_name = utc.table_name and ucc.column_name = utc.column_name) parent_table
from user_tab_columns utc
where column_name = ‘DPT_AUTO_KEY’
order by column_id
.. and the following..
select ucc.table_name, substr(ucc.column_name,1,20) column_name, uc.delete_rule,
ucc.constraint_name fk_name, uc.r_constraint_name pk_name,
substr(pk.column_name,1,20) pk_column, ic.index_name
from user_cons_columns ucc, user_constraints uc, user_cons_columns pk,
user_ind_columns ic
where uc.constraint_name = ucc.constraint_name
and uc.constraint_type = ‘R’
and pk.constraint_name = uc.r_constraint_name
and ic.table_name (+)= ucc.table_name
and ic.column_name (+)= ucc.column_name
and pk.column_name = ‘DPT_AUTO_KEY’
-
There are also a couple examples in the members resources area.
-
You can pull data from any table where the auto key is available in the data pipeline. You do this with SQL statements. There is many examples in the forums you can can probably find with the search.
-
Sent
-
I think you have to set your pass settings to 2 for the page count to be tabulated first.
-
If you right click on the footer object in the design screen, there is an option that says print on first page and print on last page.
take the check box off of the print on first page and see if that does the trick.
If not, I don’t know if you can hide the whole footer. You could try and put your footer content inside of a region, then show or hide the region. It is essentially the same thing but I think you have more control of the regions.
On your region On Print even it might be something like
Region1.Visible := Report.AbsolutePageNo=Report.AbsolutePageCount
-
Can you put an if statement in to trap for empty values?
Ignor the syntax please..
If isNull(LOC) = True then
MsgBox(‘Location Auto Key is Empty on Row & ROW CNTR &”)
Else if isNull(DES) = True then
et. etc.
I am sure my pascal, visual basic and NS basic code is all getting mixed up, but you get the idea I think..
I assume you have your field names setup with the correct names, type, size, offset, etc?
-
Teresa, there is no easy way to convert a sales order to a kit.
However, you can just use the COPY SO option from the SO Header browse
-
You can also use this if you want to specify the lot number as your filter
update
stock stk set
stk.loc_auto_key= XXX
where
stk.stm_lot in
(
select stk2.stm_lot
from
stock stk2,
stock_reservations str,
wo_operation wo
where
stk2.stm_lot=str.stm_auto_key and
str.woo_auto_key=wo.woo_auto_key and
wo.si_number=XXX
)
and stk.qty_oh>0
and stk.loc_auto_key = XXX
-
If you are using the factory document, I would say it is a bug. If you are using a custom form, try it in the factory document.
-
Go to User -> System Setup -> Global Setting -> Misc Tab -> Click Maintain Default Clauses
From this option you can add clauses for all orders.
-
Also, if SO.DUE_DATE is a DateTime format, you might need to convert it to a date only format. You can find that I the code toolbox.
-
I would probably do some thing like this
Procedure YourLabelNameOnPrint;
begin
YourLabelName.Visible := False;
if SO_HEADER[‘DUE_DATE’] < CurrentDate then YourLabelName.Visible := True; end; You have this in the footer, so it should work. I am not sure what your vtDate is or where you assign the value. So, I assumed you wanted to use the current date. If you use the Form Designer’s Code Toolbox, and look at the Language Tab, you will see under the DateTime Function that CurrentDate will give you the current system date.
-
You can use the file export and import options for the form as a while or the data pipeline. The output is in XML format I believe.
-
Please post what code you have.
-
You may also find my previous lectures on inventory shortages, physical inventory and barcoding useful
-
Tony, At Worthington, we have gone through a couple different cycle count approaches. One method we used was to count every bin that had been touched the previous business day. In addition, we would throw in random bins throughout the year.
To determine what bins to count, we used a couple different reports I developed. Since we are no longer using those reports, I can provide you a copy.
Our auditors didn’t care for that approach at the time so we switched to a full wall to wall audit throughout the course the of year. As our facility and inventory grew, we switched to a 33% approach at our main facility and continue to do a wall to wall at our 5 other storage facilities.
The 33% approach is done by taking all locations in the facility and randomly selecting 33% of the unaudited locations each year (excluding previously audited locations) over the course of 3 years. The selection process also includes a variety of random locations, and is separated by consigned, non consigned and lot materials.
To accomplish this, I wrote a somewhat sophisticated visual basic program in excel that builds all of the candidate locations to choose from based on user input. The duplicates are removed, then copied into a separate spreadsheet. The separate spreadsheet then tracks locations audited in the physical inventory module in Quantum and compares them against the selected list to give you a progress report, estimated time to completion based on current pace and how many locations must be audited per day to finish by year end.
Hope that gives you some ideas.
-
I believe the only forms that “require” versions by the FAA is the 8130.
Any other forms that have or require version numbers would likely be your own internal quality program. If you are ASA or ISO certified, then those form control numbers are managed by the DPRs (department of primary responsibility) for the forms. QC forms can be edited if you have the forms designer, but to put control numbers on these (outside of the 8130) is a bit over kill in my opinion.
We are ASA and ISO certified and have two repair stations. We do not put control numbers on all of our Quantum documents.
-
The reason for this stance is because of the path to fix the issues. More often times than not a patch is not made because it is too difficult or too time consuming.
They are certainly not Microsoft. They have gotten better as they used to not offer patches at all. Still, I understand where you are coming from.
-
to update your stock line qty you probably need to set the closed_update field to True.
This is a hidden gem in many of the core tables that bypasses the triggers.
update stock stk set
stk.qty_oh=0, stk.qty_available=0,
stk.qty_reserved=0,
stk.closed_updated=’T’ were stk.whs_auto_key=??
and
stk.qty_oh>0
-
I do not recal if updating the quantity directly with SQL will invoke the adjustment to the GL. I suggest you try it on one stock line first. If it does, you can always disable the tu_stock trigger (I think that is the one) through SQL developer. Be sure no one is in the system when you disable the trigger. Then enable it after you are done.
-
Hi Bill, not sure what the purpose of the variable is, but if I was just trying to display text, I would add a label and in the OnGetText event I would put this code
begin
if ALTERNATE_STOCK[‘ALT_TYPE_CODE’] = ‘XWZ’ then
Begin
Text :=’Approved cuz I say so ‘ + ALTERNATE_STOCK[‘PN’];
varAlternate.Text:=’317 approved alternate for ‘ + ALTERNATE_STOCK[‘PN’];
End
Else
Text := ”;
end;
‘Of course, exclude the variable assignment unless your variable is global and you are looking to pass that string to another portion of the report.
-
Is this in relation to implementation where you are trying to import your historical invoices from another system before Go Live?
-
Dump question but have you checked the fetch size on ALL of your users browsers? Make sure it is set to something of 250 or less.
-
I would imagine this could be done with some SQL, but I don’t know the linking structure that would tie the billing groups to PNs. I would assume it is through the templates.
-
Tony, I’ve been slowly migrating towards direct inserts from excel vs. imports. I create an excel template that has embedded macros. Then, in this example, the user click the import button and would be prompted for the CQ number (for example). Once that is known, the macro rolls through each line and inserts it as detail into quote.
once you get the first macro done, apply the method to virtually any data element is not a far stretch. I am currently creating one for our expense reports. All payables will have to do is click a button and specify the GL batch. The macro will insert the detail using the data from excel.
So, something to think about.
-
I believe you can achieve your goal by just marked the stock line you are selling on exchange as precosted. leaving the cost at zero. Do this before you reserve the item.
I think that will override the lot margin calculations. Then, the core comes back into the lot.
Next, If you are billing the customer the repair costs, make sure the repair is launched from the SO and you should be able to use the Vendor Quote Wizard to redirect the repair charges back to the customer.
See help Chapter 6 – Sales Orders > Costing Repairs in Exchange Processing
-
Have you looked at OCDia to see any existing import templates exist? I know there are tools for the work order like this, so it cant be that big of a stretch to do it for the MFG module.
-
Bill can you post the code you have, and identify which portion of the pick ticket you are putting in (ie main, sub, etc.) ?
-
A full demand planning functionality was removed some time ago from the system due to third party option being readily available.
The best option is to create reports with Crystal to solve this problem. At Worthington, we have a variety of usage analysis tools we use to assign tiers to the part, determine levels for each of our warehouse and identify IMU (intensely managed units) parts.
I do not believe there is any plan to expand these functions beyond the part by part analysis available in the system today.
-
Dori, this is a pretty hard issue to tackle. I know many have tried creating reports to handle this, but haven’t heard of anyone doing it yet.
The way we solve this problem is we run a crystal report automatically at midnight on the first day of every month. The report just dumps the entire stock into an excel file. So, it gives us a snapshop of inventory levels every month.
For what we need, it is a much easier and more accurate approach than trying to reconstruct the inventory levels at a given point in time.
-
Im curious now. Please post the findings.
-
here is something else to try. Insert a bogus stock line, then with SQL, update the control number to where you want it to be. Add a new stock line and see if it increments to the next value.
My thoughts on this are I wonder if they are actually doing a max value lookup instead of using a sequence (I doubt it – bad programming)
-
The only thing it could be is a different sequence. Look at the sequences in SQL Developer and find the one with the same last number as your control number.
you can download it here if it isn’t already on your server.
-
Did the last value of your sequence change?
The control ID will increment for each item within the batch. Different from the Control Number
-
This would be a useful thing to have.
-
Control numbers are incremented for each receiving event in the system (imports included as well as some stock splits and transfers) and control IDs are incremented for each item within that receiving event. The two values put together are a unique identifier for that particular stock line. The two put together also represent the barcode printed on the stock labels. Altering number logs (Oracle Sequences) is a dangerous thing. But it you must..
All sequence numbers are held in Oracle sequence tables and are incremented by calling “NEXTVAL” from “DUAL”
The stock line control sequence is called G_STM_CTRL_NUMBER and has field names of CREATED, LAST_DDL_TIME, SQUENCE_OWNER, SEQUENCE_NAME,MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG,CACHE_SIZE,LAST_NUMBER
To increment a number log the easiest way is to just open on that sequence in Oracle SQL Developer tool, click the last number field and input the new number.
Or, you can do it the “proper” way, according Oracle tutorials
ALTER SEQUENCE G_STM_CTRL_NUMBER
INCREMENT BY 1;
Now, the next value to be served by the sequence will be the last value +1
Do this at your own risk.
-
Great input. Thanks for the tip!
-
It sounds like you have the LIFO/FIFO functionality enabled. You can change this, or turn it off through your user->system setup -> Inventory-> Inventory Settings
Look in the top right corner under the “Auto Reserve” block. Here you will see a setting for “Stock Method” where it lets you choose Manual, LIFO (last in first out) or FIFO (first in first out)
If you set this to manual, all stock reservations will always be manual.
-
Tony, yes those would be sub reports. If it were me, I would just create an SQL query and make the consignment code a prompt. So, when the data is refreshed in Excel, the user is prompted for the consignment code, and the report runs.
I would probably not do this in crystal, but if you want to schedule it, crystal is fine too.
-
The SQL syntax that is used with Quantum is Oracle SQL
I think you will find that the majority of your scripts can be written with a dozen or so core sql statements that are slightly modified for each purpose. My recommendation is that you start creating a library of scripts, code and sql queries for reference.
The language that QC runs on is called Delphi. The are numerous books out there to help you learn the basics of Delphi (which I believe is a Pascal based language).
If you have the forms designer, it is a great tool to use to learn the very basics of retrieving data, initiating pop ups, or basic functions like that just to get you started.
Another great source to get you started is the OCDIA tool, which is the Oracle Data Import Tool. This uses the same language as the Event Manager. It is the opposite of the forms designer in that it imports data. So, you can look through the various scripts in this tool to get a feel for parsing data, updating records, inserting records, etc.
If you are fairly technical, and you are familiar with Crystal and the table structure in QC, you should be able create scripts pretty well by learning from the examples that have been created in event manager, oracle DIA tool, forms designer and the books.