dtulonen
Forum Replies Created
-
Removing all of the parameters it was left with this:
SELECT S.*,
GREATEST((S.QTY_BK_ORD-S.QTY_AVAILABLE-S.QTY_RO), 0) NEED_TO_BUY,
(S.UNIT_PRICE-S.UNIT_COST)*S.QTY_ORDERED PROFIT,
(S.QTY_ORDERED*S.UNIT_PRICE) ORDERED_AMT,
(S.QTY_ORDERED-S.QTY_INVOICED)*S.UNIT_PRICE OPEN_AMT,
(S.QTY_RESERVED*S.UNIT_PRICE) BILLABLE_AMT
FROM (
select
SOH.SO_NUMBER, SOH.ENTRY_DATE H_ENTRY_DATE, SOH.COMPANY_REF_NUMBER, SOH.DUE_DATE, SOH.CUR_AUTO_KEY,
CMP.COMPANY_NAME, CMP.COMPANY_CODE, CMP.SITE_CODE, CUR.CURRENCY_CODE,
SYSUR.EMPLOYEE_CODE, CLC.CLASS_CODE,
SOD.ROUTE_CODE, SOD.UNIT_PRICE, SOD.UNIT_COST, SOD.EST_COST, SOD.SOD_AUTO_KEY,
SOD.QTY_RESERVED, SOD.QTY_ORDERED, SOD.QTY_SHIP, SOD.QTY_INVOICED,
(SOD.QTY_ORDERED-SOD.QTY_SHIP-SOD.QTY_INVOICED) QTY_BK_ORD,
SOD.ITEM_NUMBER, SOD.DELIVERY_DATE, SOD.NEXT_SHIP_DATE, NVL(SOD.NEXT_SHIP_DATE,SOD.DELIVERY_DATE) SHIP_DATE,
PNM.PN, PNM.DESCRIPTION, PCC.CONDITION_CODE,
NVL((SELECT ‘T’ FROM DUAL WHERE EXISTS (SELECT ‘X’ FROM QCTL.STOCK_RESERVATIONS STR WHERE STR.SOD_AUTO_KEY = SOD.SOD_AUTO_KEY)),’F’) RES_EXISTS,
(select sum(QTY_AVAILABLE) from QCTL.stock stM
where SOD.PNM_AUTO_KEY=stM.PNM_AUTO_KEY and SOD.PCC_AUTO_KEY=stM.PCC_AUTO_KEY
and upper(stM.historical_flag)=’F’) QTY_AVAILABLE,
(select SUM(QTY_ORDERED – QTY_REC) from QCTL.PO_DETAIL pod
where pod.PNM_AUTO_KEY = SOD.PNM_AUTO_KEY
and pod.PCC_AUTO_KEY = SOD.PCC_AUTO_KEY) TOTAL_QTY_ON_ORDER ,
SCC.so_category_code ,
NVL((select sum(QTY_RESERVED) from QCTL.RO_DETAIL where SOD_AUTO_KEY=sod.SOD_AUTO_KEY),0) QTY_RO,
SPN_SOH.SALESPERSON_CODE SOH_SALESPERSON ,
SPN_SOD.SALESPERSON_CODE SOD_SALESPERSON, SYSCM.COMPANY_NAME ACCT_COMPANY
from QCTL.SO_DETAIL SOD, QCTL.SO_HEADER SOH, QCTL.COMPANIES CMP, QCTL.SYS_USERS SYSUR, QCTL.CURRENCY CUR,
QCTL.SO_CATEGORY_CODES SCC, QCTL.CLASS_CODES CLC, QCTL.PARTS_MASTER PNM, QCTL.PART_CONDITION_CODES PCC,
QCTL.SALESPERSON SPN_SOH , QCTL.SALESPERSON SPN_SOD, QCTL.SYS_COMPANIES SYSCM
where SOH.SOH_AUTO_KEY = SOD.SOH_AUTO_KEY
AND PNM.pnm_auto_key (+) = SOD.pnm_auto_key
AND PCC.pcc_auto_key (+) = SOD.pcc_auto_key
AND CMP.CMP_AUTO_KEY (+) = SOH.CMP_AUTO_KEY
AND SYSUR.SYSUR_AUTO_KEY (+) = SOH.SYSUR_AUTO_KEY
AND SCC.SCC_AUTO_KEY (+) = SOD.SCC_AUTO_KEY
AND CLC.CLC_AUTO_KEY (+) = CMP.CLC_AUTO_KEY
AND CUR.CUR_AUTO_KEY (+) = SOH.CUR_AUTO_KEY
AND SPN_SOH.SPN_AUTO_KEY (+) = SOH.SPN_AUTO_KEY
AND SPN_SOD.SPN_AUTO_KEY (+) = SOD.SPN_AUTO_KEY
AND SYSCM.SYSCM_AUTO_KEY(+) = SOH.SYSCM_AUTO_KEY
AND CMP.CMP_AUTO_KEY >= 0
AND SOH.OPEN_FLAG = ‘T’
ORDER BY SOH.SO_NUMBER, SOD.ITEM_NUMBER)s
-
Hello Jason,
This query is awesome, on 10.8.22.0 I had to mod one of the serial number pulls:
(SELECT MAX(V.SERIAL_NUMBER) FROM view_sps_wo_serial_nums V WHERE V.WOO_AUTO_KEY=WOO.WOO_AUTO_KEY ) UNIT_SERIAL_NUMBER
To:
FROM view_sps_woo_serial_number V
Other then that it works great, question about the query though is there a reason you reversed the “Profit” to show negative instead of positive and vise versa? Personally I’ll probably change it to the other way around but I’m wondering if there is a purpose to it?
-
I’m unsure if this will help you solve the exact issue, hopefully it gets you closer to the result you are looking for, but in our system getting this to post results in excel required me to remove the conditions placed on this sub query:
“LEFT OUTER JOIN (SELECT WOT.WOT_AUTO_KEY AS “WOT_AUTO_KEY”, WOT.WOO_AUTO_KEY AS “WOO_AUTO_KEY”, WOT.WOS_AUTO_KEY AS “WOS_AUTO_KEY”, WOS.DESCRIPTION AS “WOS_DESCRIPTION”,
WOT.LAST_STATUS_CHANGE AS “WOT_LAST_STATUS_CHANGE”
FROM QCTL.WO_TASK WOT
LEFT OUTER JOIN QCTL.WO_OPERATION WOO ON WOO.WOO_AUTO_KEY = WOT.WOO_AUTO_KEY
LEFT OUTER JOIN QCTL.WO_TASK_MASTER WTM ON WTM.WTM_AUTO_KEY = WOT.WTM_AUTO_KEY
LEFT OUTER JOIN QCTL.WO_STATUS WOS ON WOS.WOS_AUTO_KEY = WOT.WOS_AUTO_KEY
LEFT OUTER JOIN QCTL.WO_WORK_TYPE WWT ON WWT.WWT_AUTO_KEY = WOO.WWT_AUTO_KEY
WHERE WOO.OPEN_FLAG = ‘T’) WOS_INFO ON WOS_INFO.WOO_AUTO_KEY = WOO.WOO_AUTO_KEY”
Removed: “AND WWT.WWT_AUTO_KEY = 3 AND WTM.WTM_AUTO_KEY = 226”
You may have to do the same and then use the built in excel filters to ignore unwanted results.
Best wishes
Dmitri T
-
Another way to look at this in oracle that I have found, every field except something with SUM() etcetera has to be included in the group by clause.
Your query could have been fixed like so as well.
Select mfg.mfg_code, loc.location_code
from parts_master pnm join manufacturer mfg on pnm.mfg_auto_key = mfg.mfg_auto_key
join location loc on pnm.loc_auto_key = loc.loc_auto_key
group by mfg.mfg_code, loc.location_code
As for your second question, I believe you are looking for a switch case maybe?
Select
CASE mfg.mfg_code
WHEN ‘_’ THEN ‘Some Text’
ELSE mfg.mfg_code
END as “mfg_code”,
loc.location_code
from
parts_master pnm
join
manufacturer mfg on
pnm.mfg_auto_key = mfg.mfg_auto_key
join
location loc on
pnm.loc_auto_key = loc.loc_auto_key
group by
mfg.mfg_code, loc.location_code
-
@David Hope
I ended up using your base to do what Larry was after (brilliant idea by the way), however it didn’t quite work with copy paste to get the minimum stockline. In our system for wo_stm_complete to have the data the work order has to be “completed” though we didn’t find out the exact trigger to get the data there. What I ended up using was the Stock reservations table.
Code below with the fix for our system:
VAR
q : TOracleDataset;
begin
text:=”;
q := TOracleDataset.Create(nil);
q.SetSession;
q.Sql.text := ‘WITH ‘+
‘MINID AS (select min(stock.stock_line) as min from stock where stock.stm_auto_key in (Select str.STM_AUTO_KEY from STOCK_RESERVATIONS str where str.WOO_AUTO_KEY = ‘ + Parameters[‘WOO_AUTO_KEY’] + ‘)), ‘ +
‘SLID AS (select stock_line as sl from stock where stm_auto_key = ‘ + Parameters[‘STM_AUTO_KEY’] + ‘), ‘ +
‘REVID AS (select release_version as rev FROM wo_release where release_number like ‘ + ”” + Parameters[‘RELEASE_NUMBER’]+ ”” + ‘)’ +
‘SELECT slid.sl – minid.min + 1 as formid, revid.rev FROM minid, slid, revid’;
q.Open;
IF q.FieldByName[‘rev’].AsString = ‘0’ THEN
Text := WO_OPERATION[‘SI_NUMBER’] + ‘ – ‘ + q.FieldByName[‘formid’].AsString
ELSE
Text := WO_OPERATION[‘SI_NUMBER’] + ‘ – ‘ + q.FieldByName[‘formid’].AsString + ‘ – R’ + q.FieldByName[‘rev’].AsString;
q.Free;
end;
-
Quick question regarding this, are you searching closed WO’s or open ones?
When they are closed I personally link using WO_STM_COMPLETE, when they are open I link them via STOCK_RESERVATIONS.
-
Thank you for taking the time to explain Nadim,
I’ll try that method the next time I have to alter the table data (something I rarely do) most of my mods are to the packages themselves, changing the data the views show in the main program.
Example being changing the “Search RO Header” and having the “Code” field show any linked WO numbers, and “Site” to show the companies those WO’s are attached to in the main RO Header view.
One thing I can say about my previous mentioned method though, I have multiple backups using it in case of error, one of such, using the schema prefix allows you to use the training schema as an extra backup because you can always pull the data back from the training into the live if something bad truly happens. Keep in mind i understand this is a moot point if using your method interacts with the native triggers happily.
-
Nadim,
First I would like to say thank you for the explanation, most of the mods that I would personally do are one off mods, so I wasn’t thinking in re-usability though I do record all mods in details with notes in notepad files should I have to reverse them.
In my environment to save licences I connect in with sys as sysdba (doesnt use a license while developing) so having the schema is a necessity, using find and replace I switch from Train.Table to QCTL.Table for testing etc.
For Triggers in my personal experience certain tables it is honestly the best option to disable triggers, an example being the master parts table, if you executed a direct update statement on it, it will wipe out all pricing information in your system because the way the triggers interact on update. Because I have done the above by accident I probably spend more time researching triggers then anything else before performing an update.
-
It may help you to look at the view:
Select * from QCTL.View_Balance_Export
If you’re using SQL Developer you can view the SQL behind the view to see how its linking everything together.
-
I have a random question about this and I’m hoping one of the 2 people recommending SQL could answer it.
What is the reason to do this via procedure instead of a strait update statement through SQL? isn’t it making the task far more complicated then it should be?
The reason I’m asking is because faced with this question personally, I would tell the person that before all else that the safest way to do this is to disable all triggers on the table while no one is using the system and then use something along the lines (not real SQL)
Update qctl.stock stm set stm.loc_auto_key = (select loc.loc_auto_key from qctl.locations loc where loc.location_code = ‘Fill me in’) where stm.whs_auto_key = (select whs.whs_auto_key from qctl.warehouse whs where whs.warehouse_code = ‘Fill me in’)
Then to reactivate the triggers after the alteration is done.
-
Jae Lupo I’ve been playing with this lately and had the slow load times even when just interacting with group codes using the natural oracle connector in Power BI (not odbc).
In order to lower the connection use I switched it to Direct Query, it honestly looks to load much slower at first so pick a table with 20-30 records. Then you go into the options from the file menu and click on the direct query section and lower the max connections to 1 (keep in mind from my experimenting it always used 2 licenses).
After that go to Edit Queries, then Advanced Editor and replace the SQL even if its “select * from wo_operation” that got my query return speed back into the milliseconds instead of minutes.
-
On 10.8 you can make 1 connection without using a license (I haven’t tested multiples).
Go to the oracle website, and download the “Standalone” SQL Developer with the included Java. Set up your new connection using the SID, with sys as a sysdba. The connection manager will not see the connection while you work on data pulls.
Another way I found to quickly pull data was to make small php scripts with update timers, grab everything store it in an array and disconnect, then send it to a My SQL instance from the array.
-
Mike, I just got word from a friend who is a SAP – Consultant, they keep launching new SP’s to fix this ongoing issue, however, it seems that every new “Major” release by Microsoft breaks it again.
This break has happened 6 times consecutively.
From what he explained to me, the only work around that hasn’t broken is printing to file, and then printing from adobe.
This is just a warning that you may need to disable all windows updates after Component Control patches the viewer.
-
Not sure if this will help you Mike but if remember correctly about those reports, they are all associated with a “custom” paper within the reports.
Export the report to your desktop, open it, right click on design page, and press page setup, unclick the check box that associates it with a paper size, and leave it disassociated with a printer.
Then add it in as a new crystal report, selecting the appropriate “Settings Group” so that you can select it from editing the print options for the report
I remember having to follow this process for a couple of reports and it may or may not work, I’m honestly unsure.
-
Not having this issue at all in: 10.8.22.0 that I can see, would someone be willing to send me a faulty report so I could check if there is a difference between how I write my reports and the reports having issues?
-
Not sure if this is what people had in mind, but this query checks ahead to see what will expire in the next 42 days. (based on the expiry date)
select
st.PN,
st.DESCRIPTION,
(select loc.location_code from qctl.location loc where st.LOC_AUTO_KEY = loc.loc_auto_key) as “Location”,
st.QTY_OH,
st.QTY_AVAILABLE,
st.REC_DATE,
st.EXP_DATE,
st.STOCK_LINE,
st.CTRL_NUMBER,
st.CTRL_ID,
st.RECEIVER_NUMBER ,
to_char(sysdate, ‘MM/dd/YYYY’) as DateFrom,
to_char((sysdate +42), ‘MM/dd/YYYY’) as DateTo
from
qctl.stock st
where
st.EXP_DATE IS NOT NULL and
st.QTY_AVAILABLE != 0 and
st.EXP_DATE <= sysdate +42 order by st.exp_date
-
Thank you for elaborating Mike, my response was typed in haste and assumed a lot of known knowledge, I do however agree with everything you have stated.
To expand on two of the things slightly that you may personally wish to look into, you can use something other then the auto key in some cases, though it requires knowing the pipeline that the data comes from and what fields are passed from the program to the pipeline. The second thing is the report location settings, if you place it in the specific folder for what you would like, you can actually select the report from the print drop down and no longer need to use print custom.
Best Wishes
-
For this you can do multiple things, I will suggest 2 of them that I personally find the simplest to accomplish.
Add a WHERE clause to your SQL statement:
Select * from Table WHERE Order_Number = ‘123123’
Then within crystal reports change that to a parameter that it prompts you for on refresh. (This would be used on an external crystal form)
The other method would be in the Report menu through the Selection Formula, where again you could create a parameter to allow you to select what to filter on.
Best Wishes
Dmitri T.
-
I’m unsure if this will help you track down the information you need but it will assist you in locating the SQL currently used.
Load the report (in this case crystal on our system)
Export the report to your desktop
Open in crystal reports and go to the database menu and press set data source location; on the screen you will see ‘SPR_INVC_DETAIL_MAIN’ expand that to find the package location.
In this case its located in RPT_CRYSTAL_PKG under the function SPR_INVC_DETAIL_MAIN; go to that package open the body and use the find function to locate the function in question.
That will tell you where it pulls its data from:
PROCEDURE SPR_INVC_DETAIL_MAIN(P_CURSOR IN OUT QC_UTL_PKG.CURSOR_TYPE)
IS
BEGIN
P_CURSOR := RPT_IN_PKG.INVC_DETAIL_MAIN;
END;
The P_CURSOR is the line you are looking for telling you to go look at package RPT_IN_PKG in that package look for function INVC_DETAIL_MAIN
That will tell you the current SQL used by the system to produce the report.