Tim Hernandez
Forum Replies Created
-
I have several reports that drill down by using the crystal report sub report function. You have to play around with the alignment and maybe the padding of the cell in reference to the row details but it should work and expand.
Don’t have the sub report run it’s query unless it is drilled down into. Also, my sub reports are pretty light. So the command used is only pulling what is needed. I hope this helps. If there is a better way I would like to know myself.
-Tim
-
You can just remove the last portion of the query
“and
woo.si_number is not null”
and it should give you everything but I added that last part for you to verify the BOM that is to be allocated to WO’s.
-
Oooooo Purchase_WO table you seek!
LOL, sorry it is Friday and I am in a good mood.
Here is a starter snippet for your group:
Select
woo.si_number, poh.po_number, pnm.pn, pnm.description, pod.qty_ordered, pod.qty_rec, pod.entry_date
From
wo_operation woo, purchase_wo pwo, po_header poh, po_detail pod, parts_master pnm, wo_bom wob, wo_task wot
Where
pnm.pnm_auto_key = pod.pnm_auto_key and
poh.poh_auto_key = pod.poh_auto_key and
pwo.pod_auto_key (+)= pod.pod_auto_key and
wob.wob_auto_key (+)= pwo.wob_auto_key and
wot.wot_auto_key (+)= wob.wot_auto_key and
woo.woo_auto_key (+)= wot.woo_auto_key and
woo.si_number is not null
Order by pod.entry_date desc
Hope this can get you started. Feel free to expand on this and that goes for everyone else. Please post your results on this thread.
-
Rolando:
What are you looking for that isn’t provided in Anthony’s question?
-
Are you using a Custom SQL event for the email blasts?
-
SELECT STA.TIME_STAMP, STA.EMPLOYEE_CODE, STA.TRAN_TYPE, STA.NEW_STM_AUTO_KEY, STA.NEW_PN, STA.NEW_DESCRIPTION, STA.OLD_CONDITION_CODE, STA.OLD_LOCATION_CODE, STA.NEW_WAREHOUSE_CODE, STA.OLD_REC_DATE, STA.OLD_STOCK_LINE, STA.NEW_CTRL_NUMBER, STA.OLD_RECEIVER_NUMBER, STA.OLD_SERIAL_NUMBER, STA.OLD_UNIT_COST, STA.NEW_UNIT_COST, STA.RC_NUMBER, STA.PO_NUMBER, STA.RO_NUMBER, STA.SO_NUMBER, STA.INVC_NUMBER, STA.AMOUNT
/* Remove this whole line and add your desired fields below
,pod.vendor_price, pod.unit_cost –Purchase Order Details
,sod.unit_price, sod.unit_cost –Sales Order Details
,rod.labor_cost, rod.qty_repaired –Repair Order Details
*/ Remove this whole line as well.
FROM STOCK_AUDIT STA, PO_DETAIL POD, RO_DETAIL ROD, SO_DETAIL SOD
WHERE
pod.pod_auto_key (+)= sta.pod_auto_key
and rod.rod_auto_key (+)= sta.rod_auto_key
and sod.sod_auto_key (+)= sta.sod_auto_key
and sta.time_stamp >= to_date(’01-NOV-2012′,’DD-MON-YYYY’)
-
Jim:
Like Paul said, It would be helpful if you posted your query so the community could help you with your joins.
All the best,
TH
-
You can, just make sure you don’t bogg down the query. Needs to be specific. My rule of thumb is if I use the GUI then I stick to it. When you get more comfortable with the Command SQL you won’t care for the GUI.
Good luck and bring some reports to the conference workshops. Gonna be a great experience.
-
André, is right. Just add another parts_master table and rename it BOMParts_Master or whatever you want. Left outer join the pnm_auto_key on the wo_bom.pnm_auto_key and you are in business.
If you don’t need the wo.pn then just remove the wo.pnm_auto_key link from the parts_master and left outer join the parts-master.pnm_auto_key to the wo_bom.pnm_auto_key.
Best,
Tim
-
Send me the report I will covert it to sql. Thanks everyone!!!!!!
-
Why don’t we share the command on this thread?
Thank you everyone for getting involved. QUEGroup Army in full effect.
-
Just copy it to a command Brian. It is really easy.
Thank you André and Nadim for all your input.
-
Hi Pietro,
I am sure many other would like this. could you please post the SQL on this thread?
Thanks in advance,
Tim
-
Nadim: Great recommendation with a view.
Bill: Try the statement below. Not as clean as Nadim recommends but if you are in a tight spot right now and don’t have access to the views with your crystal user then this may be able to help.
select woo.si_number, stm.pn, stm.description, stm.serial_number, roh.ro_number
from stock_reservations str, stock stm, ro_detail rod, ro_header roh, wo_operation woo
where woo.woo_auto_key = str.woo_auto_key
and stm.stm_auto_key = str.stm_auto_key
and rod.rod_auto_key (+)= str.rod_auto_key
and roh.roh_auto_key (+)= rod.roh_auto_key
and stm.historical_flag = ‘F’
union ALL
select woo.si_number, stm.pn, stm.description, stm.serial_number, roh.ro_number
from stock_reservations str, stock stm, ro_detail rod, ro_header roh, wo_operation woo
where woo.woo_auto_key = rod.woo_auto_key
and stm.stm_auto_key = str.stm_auto_key
and rod.rod_auto_key (+)= str.rod_auto_key
and roh.roh_auto_key (+)= rod.roh_auto_key
and stm.historical_flag = ‘F’
Best,
Tim
-
LOL, Jesse. =)
-
Abigail,
Your opn.serial_number is listed twice. I assume you meant to use cpn.serial_number. I did remove all the RO tables because we utilize a similar report just with WO’s. Hope this gives some insight. Please see below:
SELECT SOH.SO_NUMBER,
SOD.ENTRY_DATE,
OPN.PN,
OPN.SERIAL_NUMBER,
EXC.CORE_VALUE,
CPN.PN COREPN,
CPN.SERIAL_NUMBER CORESN,
EXC.CORE_RET_DATE,
decode(OPN.PN, CPN.PN, 1, 0) as TEST, –Same PN Test
CMP.COMPANY_NAME,
WOO.SI_NUMBER
FROM SO_HEADER SOH,
SO_DETAIL SOD,
EXCHANGE EXC,
STOCK OPN,
STOCK CPN,
COMPANIES CMP,
WO_OPERATION WOO
WHERE SOH.SOH_AUTO_KEY = SOD.SOH_AUTO_KEY
AND EXC.SOD_AUTO_KEY = SOD.SOD_AUTO_KEY
AND OPN.STM_AUTO_KEY = EXC.ORIG_STM
AND CPN.STM_AUTO_KEY = EXC.STM_AUTO_KEY
AND SOH.CMP_AUTO_KEY = CMP.CMP_AUTO_KEY
AND WOO.WOO_AUTO_KEY = EXC.WOO_AUTO_KEY
AND SOD.ENTRY_DATE BETWEEN ‘1-JAN-16’ AND SYSDATE
Terri I also hope this helps. Shoot me an email if you like and we can catch up. My email is thernandez@psilabs.com and I am always available.
Best of luck all,
TH
-
woo.woo_auto_key = wqd.woo_ref
wqd.wqh_auto_key = wqh.wqh_auto_key
I am pretty sure you must go through WQD to WQH.