[Mahmoud Sabobeh]

The Tasks are in sequence order, basically if you look at the screenshot I attached, you will see “Last Status Change” have few blanks and skips to sequence 140, what I want is to show only 140 and the next task after that 150, and not display all the previous one.

[Mahmoud Sabobeh]

I haven’t had a chance to put full focus on it, but for some reason when I look at WO_TASK it tracks mostly Last_Status_Change and Last_Close_Date but does not give me “Last Scanned or Sysur_scanned_by

Here is what I’ve done so far

SELECT “WO_OPERATION”.”SI_NUMBER”, “PARTS_MASTER”.”PN”, “PARTS_MASTER”.”DESCRIPTION”, “WO_OPERATION”.”ENTRY_DATE”, “PN_GROUPS”.”GROUP_CODE”, “WO_OPERATION”.”OPEN_FLAG”, “WO_STATUS”.”STATUS_TYPE”, “STOCK”.”SERIAL_NUMBER”, “APPLICATION_CODES”.”APPLICATION_CODE”, “VIEW_SPS_WO_OPERATION”.”PARENT_WO”, “VIEW_DASH_WO_BASE”.”WORK_TYPE”, “LOCATION”.”LOCATION_CODE”, “WAREHOUSE”.”WAREHOUSE_CODE”, “WO_TASK”.”LAST_STATUS_CHANGE”, “WO_TASK”.”LAST_SCANNED”, “WO_TASK”.”SYSUR_SCANNED_BY”, “WO_TASK”.”LAST_CLOSE_DATE”, “WO_TASK”.”SEQUENCE”

FROM “QCTL”.”VIEW_DASH_WO_BASE” “VIEW_DASH_WO_BASE” LEFT OUTER JOIN ((((((((((“QCTL”.”WO_OPERATION” “WO_OPERATION” LEFT OUTER JOIN “QCTL”.”STOCK_RESERVATIONS” “STOCK_RESERVATIONS” ON “WO_OPERATION”.”WOO_AUTO_KEY”=”STOCK_RESERVATIONS”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”PARTS_MASTER” “PARTS_MASTER” ON “WO_OPERATION”.”PNM_AUTO_KEY”=”PARTS_MASTER”.”PNM_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_STATUS” “WO_STATUS” ON “WO_OPERATION”.”WOS_AUTO_KEY”=”WO_STATUS”.”WOS_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”VIEW_SPS_WO_OPERATION” “VIEW_SPS_WO_OPERATION” ON “WO_OPERATION”.”WOO_AUTO_KEY”=”VIEW_SPS_WO_OPERATION”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_TASK” “WO_TASK” ON “WO_OPERATION”.”WOO_AUTO_KEY”=”WO_TASK”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”STOCK” “STOCK” ON “STOCK_RESERVATIONS”.”STM_AUTO_KEY”=”STOCK”.”STM_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”LOCATION” “LOCATION” ON “STOCK”.”LOC_AUTO_KEY”=”LOCATION”.”LOC_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WAREHOUSE” “WAREHOUSE” ON “STOCK”.”WHS_AUTO_KEY”=”WAREHOUSE”.”WHS_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”PN_GROUPS” “PN_GROUPS” ON “PARTS_MASTER”.”PNG_AUTO_KEY”=”PN_GROUPS”.”PNG_AUTO_KEY”) INNER JOIN “QCTL”.”APPLICATION_CODES” “APPLICATION_CODES” ON “PARTS_MASTER”.”APC_AUTO_KEY”=”APPLICATION_CODES”.”APC_AUTO_KEY”) ON “VIEW_DASH_WO_BASE”.”WOO_AUTO_KEY”=”VIEW_SPS_WO_OPERATION”.”WOO_AUTO_KEY”

WHERE “WO_OPERATION”.”OPEN_FLAG”=’T’ AND “WAREHOUSE”.”WAREHOUSE_CODE”=’GATE 2′

ORDER BY “WO_OPERATION”.”SI_NUMBER” DESC

[Mahmoud Sabobeh]

Here is another update to the code, so now it shows me last Task and Last Labor, but I need to know how to be able to display next task that needs to be worked on / and previous task of the last task was worked on, and not worry about everything else that was worked on

SELECT “WO_OPERATION”.”SI_NUMBER”, “PARTS_MASTER”.”PN”, “PARTS_MASTER”.”DESCRIPTION”, “WO_OPERATION”.”ENTRY_DATE”, “PN_GROUPS”.”GROUP_CODE”, “WO_OPERATION”.”OPEN_FLAG”, “WO_STATUS”.”STATUS_TYPE”, “STOCK”.”SERIAL_NUMBER”, “APPLICATION_CODES”.”APPLICATION_CODE”, “VIEW_SPS_WO_OPERATION”.”PARENT_WO”, “VIEW_DASH_WO_BASE”.”WORK_TYPE”, “LOCATION”.”LOCATION_CODE”, “WAREHOUSE”.”WAREHOUSE_CODE”, “WO_TASK”.”LAST_STATUS_CHANGE”, “WO_TASK”.”LAST_CLOSE_DATE”, “WO_TASK”.”SEQUENCE”, “WO_TASK_LABOR”.”CLOSED_UPDATE”, “WO_TASK_LABOR”.”STOP_TIME”, “WO_TASK_LABOR”.”REWORK_FLAG”

FROM ((((((((((“QCTL”.”WO_OPERATION” “WO_OPERATION” LEFT OUTER JOIN “QCTL”.”STOCK_RESERVATIONS” “STOCK_RESERVATIONS” ON “WO_OPERATION”.”WOO_AUTO_KEY”=”STOCK_RESERVATIONS”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”PARTS_MASTER” “PARTS_MASTER” ON “WO_OPERATION”.”PNM_AUTO_KEY”=”PARTS_MASTER”.”PNM_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_STATUS” “WO_STATUS” ON “WO_OPERATION”.”WOS_AUTO_KEY”=”WO_STATUS”.”WOS_AUTO_KEY”) LEFT OUTER JOIN (“QCTL”.”VIEW_DASH_WO_BASE” “VIEW_DASH_WO_BASE” LEFT OUTER JOIN “QCTL”.”VIEW_SPS_WO_OPERATION” “VIEW_SPS_WO_OPERATION” ON “VIEW_DASH_WO_BASE”.”WOO_AUTO_KEY”=”VIEW_SPS_WO_OPERATION”.”WOO_AUTO_KEY”) ON “WO_OPERATION”.”WOO_AUTO_KEY”=”VIEW_SPS_WO_OPERATION”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_TASK” “WO_TASK” ON “WO_OPERATION”.”WOO_AUTO_KEY”=”WO_TASK”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_TASK_LABOR” “WO_TASK_LABOR” ON “WO_TASK”.”WOT_AUTO_KEY”=”WO_TASK_LABOR”.”WOT_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”PN_GROUPS” “PN_GROUPS” ON “PARTS_MASTER”.”PNG_AUTO_KEY”=”PN_GROUPS”.”PNG_AUTO_KEY”) INNER JOIN “QCTL”.”APPLICATION_CODES” “APPLICATION_CODES” ON “PARTS_MASTER”.”APC_AUTO_KEY”=”APPLICATION_CODES”.”APC_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”STOCK” “STOCK” ON “STOCK_RESERVATIONS”.”STM_AUTO_KEY”=”STOCK”.”STM_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”LOCATION” “LOCATION” ON “STOCK”.”LOC_AUTO_KEY”=”LOCATION”.”LOC_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WAREHOUSE” “WAREHOUSE” ON “STOCK”.”WHS_AUTO_KEY”=”WAREHOUSE”.”WHS_AUTO_KEY”

WHERE “WO_OPERATION”.”OPEN_FLAG”=’T’ AND “WAREHOUSE”.”WAREHOUSE_CODE”=’GATE 3′

ORDER BY “WO_OPERATION”.”SI_NUMBER” DESC

[Mahmoud Sabobeh]

We’ve had something similar to this, it was a bug in version 12.5.x and was fixed 12.5.5 and later, currently running 12.5.6

[Mahmoud Sabobeh]

Warren Coykendall wrote:

Based on this website:

https://www.devart.com/odbc/oracle/docs/microsoft_sql_server_manager_s.htm

You need to add a new Linked Server,

Provider string: OraOLED.Oracle

DataSource string:

(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort)))(CONNECT_DATA=(SID=urOracleSID)(SERVER=DEDICATED)));UserId=urUsername;Password=urPassword

I’ve tried this, I got the following error… beside there are no such choice “Microsoft OLE DB Driver for Oracle” there is few of them but the only thing that really relates to Oracle is this “Microsoft OLE DB Provider for ODBC Drivers”

OLE DB provider “MSDASQL” for linked server “QUANTUM” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)

[Mahmoud Sabobeh]

John Boyce wrote:

Mahmoud,

Which program are you referencing? The OraKill program should work from any workstation and be visible, the service program does not have a user interface.

John

I’m referring to the auto IDLE kick program that you uploaded back in 2017, “Tool to kill inactive sessions”

I extracted the file to local C: drive on the Server with the Oracle Database, I created a service for it using parameters, when I try to start the service I get the famous Oracle error 1053

[Mahmoud Sabobeh]

Henrik Ekenberg wrote:

With a little delay, the files are now posted in the QUEsources section http://quegroup.org/QUES_SysAdmin

//Henrik E.

I hate to prompt an old thread, but I downloaded this app and tried to run it, it just opens and disappears, am I missing something here?

[Mahmoud Sabobeh]

Nadim Ghazzaoui wrote:

The way you write it is the standard ANSI SQL join syntax.

The way some of us write it is the lazy Oracle join syntax. In Oracle, you can use both syntaxes but you cannot use the Oracle syntax with other vendors.

Don’t poison your habits and keep writing joins the way you currently do. It is the proper way. Plus it gives you access to more join types such as FULL OUTER join.

Nadim,

I ended up using this script which works perfect… however only issue I run into as you mentioned earlier “Multiple WO” shows QTY_ORDERED for ex. 7 instead of breaking down the part number for each WO of what QTY was really ordered, so I can see multiple entries of same part with same qty ordered for different WO’s which is confusing.

Here is the code I used

SELECT

“PO_HEADER”.”ENTRY_DATE”,

“PO_HEADER”.”PO_NUMBER”,

“PO_HEADER”.”VENDOR_NAME”,

“PARTS_MASTER”.”PN”,

“PARTS_MASTER”.”DESCRIPTION”,

“PO_DETAIL”.”QTY_ORDERED”,

“PURCHASE_WO”.”QTY_ORDERED” “QTY_WO”,

“PO_DETAIL”.”QTY_REC”,

“PO_DETAIL”.”LAST_DELIVERY_DATE”,

“PO_DETAIL”.”UNIT_COST”,

“SYS_USERS”.”USER_NAME”,

“PO_DETAIL”.”NOTES”,

“WO_OPERATION”.”SI_NUMBER”,

“PO_HEADER”.”COMMIT_SHIP_DATE”,

“WO_TASK”.”SEQUENCE”

FROM

((((((“QCTL”.”PO_HEADER” “PO_HEADER” INNER JOIN “QCTL”.”PO_DETAIL” “PO_DETAIL” ON “PO_HEADER”.”POH_AUTO_KEY”=”PO_DETAIL”.”POH_AUTO_KEY”) INNER JOIN “QCTL”.”SYS_USERS” “SYS_USERS” ON “PO_HEADER”.”SYSUR_AUTO_KEY”=”SYS_USERS”.”SYSUR_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”PURCHASE_WO” “PURCHASE_WO” ON “PO_DETAIL”.”POD_AUTO_KEY”=”PURCHASE_WO”.”POD_AUTO_KEY”) INNER JOIN “QCTL”.”PARTS_MASTER” “PARTS_MASTER” ON “PO_DETAIL”.”PNM_AUTO_KEY”=”PARTS_MASTER”.”PNM_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_BOM” “WO_BOM” ON “PURCHASE_WO”.”WOB_AUTO_KEY”=”WO_BOM”.”WOB_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_OPERATION” “WO_OPERATION” ON “WO_BOM”.”WOO_AUTO_KEY”=”WO_OPERATION”.”WOO_AUTO_KEY”) LEFT OUTER JOIN “QCTL”.”WO_TASK” “WO_TASK” ON “WO_BOM”.”WOT_AUTO_KEY”=”WO_TASK”.”WOT_AUTO_KEY”

WHERE “PO_HEADER”.”ENTRY_DATE”>=TO_DATE (’01-01-2020 00:00:00′, ‘DD-MM-YYYY HH24:MI:SS’)

[Mahmoud Sabobeh]

Hello,

I’m working on a similar report here that consist of “Root_SI_NUMBER. PARENT_SI_NUMBER, SI_NUMBER, PO_Number, Entry_Date, Issued_date, PN, Description, Location_code, Warehouse_code

I wouldn’t mind getting a copy if possible as well, I may use some of that code

[Mahmoud Sabobeh]

Pietro Race wrote:

I think you are mixing apples and oranges. Do you want repair orders or work orders. They are separate beasts.

I’m trying to show all outside RO’s (all Open RO’s) with clauses

[Mahmoud Sabobeh]

Mike Carey wrote:

Mahmoud- how did you resolve this ? I was going to suggest you rename the csv to a text file and then import it with the Excel wizard- and specify text as the column type for the PN column…

Mike,

If you don’t mind, do you have a script handy for this WO BOM? currently WO BOM gives me the following

Order Number, Order Type, TI Type, Date, Qty Issued, Tail Number, Part Number, Serial Number, Stock Line, Warehouse, Location, Stock Cat, Task Seq, (Unit Cost from Stock Line) is what is missing and I need to add.

What I’m trying to do here is to add “Unit_cost” from Stockline and run extract year to date on Tran_date.

[Mahmoud Sabobeh]

Mike Carey wrote:

Mahmoud- how did you resolve this ? I was going to suggest you rename the csv to a text file and then import it with the Excel wizard- and specify text as the column type for the PN column…

Mike,

That is exactly what I did, however I really would love a script that display the result of WO BOM and Parts Master

for the WO BOM I want it only to show ytd but I’m not sure what the script name in OCRUNNER, there are few BOM’s but the only thing that shows BOM_INQUIRY is under packages.

AND Extract (Year from POH.entry_date) =Extract(Year from SYSDATE)

[Mahmoud Sabobeh]

Never mind, I found an alternate way to fix this issue!

Thanks though!

[Mahmoud Sabobeh]

Warren Coykendall wrote:

In Excel, you can click on Data->Text To Columns, click Next, then uncheck deliminters then next and finish, that will bring back the missing 0’s.

When I do that, I only see :Fixed Width instead of Delimited, there is no way to uncheck it without choosing Fixed Width.

For example if one of the Parts number is

0626262 it will display it as 626262 and omit the 0.

[Mahmoud Sabobeh]

Basically, I’m trying to mimic the “search stock” feature with certain fields only, and run a 3rd party software that can read scripts like SQL Developer and produce excel reports of these stock fields that runs every 1 hour (like event manager).

[Mahmoud Sabobeh]

I got the script to work… however I’m running into an issue here… when we search stock through quantum, it display WO_NUMBER field, when I do CTRL + F10 it will show WO_NUMBER not SI_NUMBER…

So I did this script which shows SI_NUMBER but comes back with no result in the whole stock for SI_NUMBER which makes me believe I must of missed something along the way…

SELECT

PNM.PN,

STM.STOCK_LINE,

STM.QTY_OH,

STM.UNIT_PRICE,

STM.UNIT_COST,

STM.REC_DATE,

STM.EXP_DATE,

STM.SERIAL_NUMBER,

POH.PO_NUMBER,

STM.REMARKS,

STM.OVHL_COST,

STM.QTY_AVAILABLE,

STM.QTY_RESERVED,

WHS.WAREHOUSE_CODE,

LOC.LOCATION_CODE,

PCC.CONDITION_CODE,

CNC.CONSIGNMENT_CODE,

V.SI_NUMBER,

tim.csn

CYCLE_SINCE_NEW,

tim.cso

CYCLE_SINCE_OVHL,

PNM.DESCRIPTION,

CMP.COMPANY_NAME,

POH.COMPANY_REF_NUMBER

PO_HEADER_REF_NUMBER,

ROH.RO_NUMBER,

DECODE (PCC.ILS_COND, ‘AR’, 0, STM.QTY_AVAILABLE)

QTY_AVAIL_NO_AR,

(SELECT RCH.RC_NUMBER

FROM RC_DETAIL RCD, RC_HEADER RCH

WHERE RCD.RCD_AUTO_KEY(+) = STM.RCD_AUTO_KEY

AND RCH.RCH_AUTO_KEY(+) = RCD.RCH_AUTO_KEY)

DAYS_SINCE_REC

FROM

PARTS_MASTER PNM,

MANUFACTURER MFG,

STOCK STM,

PART_CONDITION_CODES PCC,

LOCATION LOC,

CONSIGNMENT_CODES CNC,

SYS_COMPANIES SYSCM,

CERT_SOURCE CTS,

WAREHOUSE WHS,

GEO_CODES GEO,

VIEW_LOT_FOR_STM_LOT V,

PO_DETAIL POD,

PO_HEADER POH,

RO_DETAIL ROD,

RO_HEADER ROH,

COMPANIES CMP,

STOCK_CATEGORY_CODES STC,

TABLE (

CAST (QC_STOCK_PKG.GET_TIMELIFE (STM.STM_AUTO_KEY) AS T_TIMELIFE_LIST)) TIM,

APPLICATION_CODES APC,

END_ITEMS EIT,

TAG_TYPE TTP,

WO_OPERATION WOO,

USER_DEFINED_LOOKUPS UDL

WHERE STM.PNM_AUTO_KEY = PNM.PNM_AUTO_KEY

AND APC.APC_AUTO_KEY(+) = PNM.APC_AUTO_KEY

AND STM.QTY_OH > 0

AND STM.HOLD_LINE = ‘F’

AND MFG.MFG_AUTO_KEY(+) = PNM.MFG_AUTO_KEY

AND PCC.PCC_AUTO_KEY(+) = STM.PCC_AUTO_KEY

AND LOC.LOC_AUTO_KEY(+) = STM.LOC_AUTO_KEY

AND CNC.CNC_AUTO_KEY(+) = STM.CNC_AUTO_KEY

AND SYSCM.SYSCM_AUTO_KEY(+) = STM.SYSCM_AUTO_KEY

AND CTS.CTS_AUTO_KEY(+) = STM.CTS_AUTO_KEY

AND WHS.WHS_AUTO_KEY(+) = STM.WHS_AUTO_KEY

AND GEO.GEO_AUTO_KEY(+) = WHS.GEO_AUTO_KEY

AND V.STM_AUTO_KEY(+) = STM.STM_LOT

AND CMP.CMP_AUTO_KEY(+) = STM.CMP_AUTO_KEY

AND POD.POD_AUTO_KEY(+) = STM.POD_AUTO_KEY

AND POH.POH_AUTO_KEY(+) = POD.POH_AUTO_KEY

AND ROD.ROD_AUTO_KEY(+) = STM.ROD_AUTO_KEY

AND ROH.ROH_AUTO_KEY(+) = ROD.ROH_AUTO_KEY

AND STC.STC_AUTO_KEY(+) = STM.STC_AUTO_KEY

AND EIT.EIT_AUTO_KEY(+) = STM.EIT_AUTO_KEY

AND TTP.TTP_AUTO_KEY(+) = STM.TTP_AUTO_KEY

AND WOO.WOO_AUTO_KEY(+) = V.WOO_AUTO_KEY

AND UDL.UDL_AUTO_KEY(+) = WOO.LOT_UDL_001 — MZ: T6332

AND QC_SC_PKG.passes_user_visibility_geo (geo.geo_auto_key) = ‘T’

AND QC_IC_PKG2.IS_STOCK_GEO_USER (STM.STM_AUTO_KEY,

QC_SC_PKG.GET_SYSUR) =’T’

Order by SI_Number desc

[Mahmoud Sabobeh]

Nadim Ghazzaoui wrote:

I think what he means is from the start of the current year until today.

If that is the case then you can try:

where extract( year from invoice_date) = extract(year from sysdate)

So if you run the report every day then it will give you all invoices from the current year the report is run.

That worked!!! thank you so much

[Mahmoud Sabobeh]

Nadim Ghazzaoui wrote:

Mahmoud,

You can do almost anything with Event Manager and Crystal. It can generate a report on the first day of every month for the previous month. For example for an invoice, you can have a where clause similar to this:

WHERE invoice_date BETWEEN last_day(add_months(trunc(SYSDATE), -2)) +1

AND

last_day(add_months(trunc(SYSDATE), -1))

Nadim,

How can I make it YTD? to constantly update the report YTD

[Mahmoud Sabobeh]

Mike Carey wrote:

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.

Good day Mike

I’ve been working on this, and I changed a lot of things but my SQL Developer isn’t being super specific to where I have coding error exactly.

I need to pull out the following I omitted some of them out because I’m not sure yet which table to pull this from:

Select

STK PN,

STK Stock_line,

STK qty_oh,

STK unit_price,

STK unit_cost,

STK rec_date,

STK exp_date,

STK serial_number,

POD PO_number,

STK remarks,

STK OVHL_COST,

STK QTY_AVAILABLE,

STK QTY_RESERVED,

–warehouse_code,

–location_code,

–condition_code,

–consignment_code,

–CYCLE_SINCE_NEW,

–CYCLE_SINCE_OVHL

STK Description,

–Company_name

POH PO_HEADER_REF_NUMBER,

ROH RO_Number

–Days_since_recd,

–WO_number

From

Stock STK,

WO_Operation WOO,

–RO_Detail ROD,

PO_DETAIL POD,

Parts_master PNM,

PO_HEADER POH

Where

STK.WOO_AUTO_KEY = WOO.WOO_AUTO_KEY

AND STK.PNM_AUTO_KEY = PNM.PNM_AUTO_KEY

AND STK.POD_AUTO_KEY = POD.POD_AUTO_KEY

AND –STK.ROH_AUTO_KEY = ROD.ROH_AUTO_KEY

AND STK.POH_AUTO_KEY = POH.POH_AUTO_KEY

Order by 1 asc

[Mahmoud Sabobeh]

Mike Carey wrote:

Mahmoud- the query that builds this browse is in the attached file. You will need to be able to decipher the PL/SQL and convert it to use as pure SQL for your report.

Thanks Mike,

I’ve loaded that script into SQL Developer, there is a lot of lines that needs to be removed / adjusted, but this gives me a good idea about tables being used/linked in here just not 100% sure of check boxes matching to my posted screen shot.

[Mahmoud Sabobeh]

When I went into Crystal Report, I filled out the sections based on the settings I want in the previous screenshot, then the report loaded exactly how I wanted it, I clicked on Save Style and saved it, when I run the report again none of the settings is applied even when I set Style to that style I created, I’m wondering what can be done?

[Mahmoud Sabobeh]

James Jewell wrote:

Nadim –

Thanks for the insight on this code.

Also a great way to handle the “leap year” (Feb 29th) issue when comparing Year-Over-Year results.

last_day(add_months(trunc(SYSDATE), -1))

-Jim

Nadim,

Thank you for your quick response, I’ve went to Crystal Reports and looked up one of the reports I want to schedule in the event manager (Receiver Reconciliation) Goods Received not invoiced, and when I double click on it, it pops a huge menu to select from open date etc per screen shot below, but when I browse to Form and Reports in the Receiver Reconciliation and click on the report I want, I don’t get half of that menu due to the loaded style.

[Mahmoud Sabobeh]

Mike Carey wrote:

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)

Mike,

I’m not sure how that can be done in Event Manager, when I extract reports Manually I can do that without any issue, but with event manager there is not much options given.

[Mahmoud Sabobeh]

*Quick Update*

Jason helped me with utilizing the Event Manager and creating Crystal Report for my PO Detail, it works flawlessly! however I’m having a Quantum issue that I’m trying to resolve here… when I try to save the Crystal Report to Shared folder, it always save the file with no format, the other issue I have is, if I changed the file name and choose format, it will loop in event manager and keep on creating the file infinite amount of times till I run a script to change the status to Success…

And finally the last issue is… When I run it as an email (with EXCEL) it sends the file as (.xls) format and breaks the report into 2 sheets instead of all in 1… (which event manager) doesn’t allow me to choose .xlsx format.

Anyone had this issue or know a way around this?

[Mahmoud Sabobeh]

Thanks Mikey, I’ve noticed the (+) just a few moment before you wrote this post, but changing pnm to stm helped a lot!

[Mahmoud Sabobeh]

Nadim Ghazzaoui wrote:

Mahmoud,

Never create a delete statement without a WHERE clause (to a few limited exceptions). Remember that a single stockline can be reserved to several WOs and/or SOs. In this case the DELETE statement would delete all reservation and not just the the intended single WO reservation.

In any case, sql delete may not work due to the way the trigger was written. Better to use pl/sql.

Run this in Interactive sql. Replace the WO number below in yellow by your WO number.

DECLARE

v_wo_number wo_operation.si_number%TYPE := ‘W1702874’;

BEGIN

FOR r IN (SELECT str.str_auto_key

FROM wo_operation woo,

wo_bom wob,

stock_reservations str

WHERE woo.woo_auto_key = wob.woo_auto_key

AND wob.wob_auto_key = str.wob_auto_key

AND str.qty_reserved > 0

AND woo.si_number = v_wo_number

AND wob.activity = ‘Consumable’) LOOP

DELETE FROM stock_reservations

WHERE str_auto_key = r.str_auto_key;

END LOOP;

END;

Please, please, please, test with several reservations in your training database first.

Nadim,

You hit the nail on its head! this works flawlessly I can’t thank you enough for this! I really appreciate it!

[Mahmoud Sabobeh]

Mike Carey wrote:

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.

Mike,

Thanks for the quick reply, can this be done automatically by WO number for all reserved parts?

I know this script works 100% just too much time consuming.

delete from stock_reservations where STM_AUTO_KEY = xxxxx

[Mahmoud Sabobeh]

Marty Luksts wrote:

Mahmoud, I have my master part window configured with a segment that is “Stock Summary with alternates”. This shows most of the information you mention and you can inspect the stock lines for further history. Would that help?

Marty,

I have it setup the same way, but I want to export an excel report for all the parts in parts master that have a quantity greater than 0.

Even with 0 is fine.

[Mahmoud Sabobeh]

Jason Cordoba wrote:

It could be that you didn’t have an alias in the SQL like _H.

If you can connect me to your computer I am happy to help.

Thanks

Jason Cordoba

Jason,

Thanks! that would be great thing to do, please let me know how to get this done, my email is msabobeh@lgt.aero

[Mahmoud Sabobeh]

Thanks guys for the help! both scripts worked, I’m just trying to do a small tweaks here,

How can I setup the date to show anything entered 30 days ago or anything exist with 30 days?

I’ve had a similar script I created in the past for a different use (parts shelf life) where I used

and s.exp_date < to_date ('09/05/2020','MM/DD/YYYY') The downside to this script above is the date needs to be updated manually, so if I want to use event manager i would have to keep on editing the date, is there a way to automate the date to + or – 30 days of current date script is being ran on (thru event manager or manually)?

[Mahmoud Sabobeh]

Thanks for all the help guys!

What I’m having trouble with though is trying to show the qty that is linked to a WO from the PO. If you go to a PO and click on MISC and select List WO’s you will get a view that shows to what WO the parts maybe linked to and what Qty.

I’ve been trying to figure out what table this Qty Linked field is coming from, but i cant seem to find it.

Thanks

[Mahmoud Sabobeh]

Nadim Ghazzaoui wrote:

Are you using PURCHASE_WO table? You will need outer join between POD and PWO in order to see all matching and non-matching records in POD.

If you are using Oracle notations then:

pod.pod_auto_key = pwo.pod_auto_key (+)

Otherwise ANSI notation:

FROM po_detail pod

LEFT OUTER JOIN purchase_wo pwo

ON pod.pod_auto_key = pwo.pod_auto_key

Nadim,

I’ve tried what you told me, but I still ran into an error.

Here is the script I’m trying to do below, notice PD.SI_NUMBER is the newest addition based on your recommendation.

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#”, PD.SI_NUMBER “OTHER WO”

FROM PARTS_MASTER P, PO_DETAIL PD, PO_HEADER PH, WO_OPERATION WO, SYS_USERS SU, PURCHASE_WO PW

WHERE PD.POH_AUTO_KEY = PH.POH_AUTO_KEY

AND PD.PNM_AUTO_KEY = P.PNM_AUTO_KEY

AND PD.PNM_AUTO_KEY = WO.PNM_AUTO_KEY

AND PD.SYSUR_AUTO_KEY = SU.SYSUR_AUTO_KEY

AND PD.POD_AUTO_KEY = PW.POD_AUTO_KEY (+)

Order by ENTRY_DATE asc

[Mahmoud Sabobeh]

Hi,

I have the STM_AUTO_KEY for all the parts, however I cannot find a script that would work without giving me a trigger error.

[Mahmoud Sabobeh]

I replied to your question below

[Mahmoud Sabobeh]

I did post about it just now, they are in a sequence (screenshot provided)

[Mahmoud Sabobeh]

I have not started the report, but I’m just curious how to look at current task (in sequence) that is being worked on and what will be the next task, from which table to pull that (and how to show next task)

[Mahmoud Sabobeh]

Tim and Mike,

Thanks to both of you, I’ve had some Left Outter done, I missed few of them (using old version of Crystal Report) I went back and changed the link between WOO and STR and then linked STR to ROD and that did the job.