[Worthington Aviation]

Ken, that is odd. Sometimes certain characters in a PN will cause weird results. I have not seen this happen for a long time, but I am curious – what was the PN ?

[Worthington Aviation]

The best scenario is to wait until all are closed, but that probably wont happen..

The only thing I can say is to make sure you do not include your WIP for inventory costs in your beginning balances for your inventory account.

It will take quite a bit of reconciliation, and be prepared to do some jornel entries as the first set of jobs complete.

[Worthington Aviation]

You can also take this and save it as a dqy file which is MS Querry. This will allow you to just double click it, then it will open in MS Excel as a live connection.
Take this content, copy and paste it into a text document, then save it as a .dqy file
You will see the first couple of lines are the DB connection. if you ID and password are different, you will need to change it. You will also need the oracle client installed on that machine you run it on.
XLODBC
1
DRIVER={Microsoft ODBC for Oracle};UID=crystal;PWD=report;SERVER=MAXQPROD;
select stk.stm_auto_key “STOCK AUTO KEY”,stk.pnm_auto_key “PN AUTO KEY”,pmn.pn “PN”, pmn.description “DESCRIPTION”,pmn.sdf_pnm_002 “SEGMENT”,pmn.sdf_pnm_001 “PRODUCT LINE”,pmn.qty_oh “TOT PN QTY OH”,pmn.custom_value “PN ASSIGNED CUSTOMS VALUE”,pmn.qty_oh*pmn.custom_value “PN TOT VALUE ON HAND”,stk.qty_oh “STK QTY OH”,stk.qty_reserved “STK RES QTY”, pmn.custom_value*stk.qty_oh “STK EXT VALUE ON HAND”,stk.qty_oh*stk.unit_cost “STK EXT CST ON HAND”,stk.liquidate_option “LIQUIDATE”,stk.tag_date “TAG DATE”,stk.serial_number “SERIAL NUMBER”,(case when stk.stm_lot>0 then ‘LOT’ else (case when stk.original_po_number <> ” then ‘PURCHASE’ else (case when stk.pod_auto_key >0 then ‘PURCHASE’ else (case when stk.rec_date < to_date('2004/01/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')then 'PENTAGON' else 'UNKNOWN' end) end) end) end) "ORG SOURCE",(select (case when stk.ind_auto_key>0 and stk.qty_credit>0 then ‘RMA’ else(case when rch.order_type1 = ‘SO’ then ‘CORE’ else (case when rch.order_type1 = ‘PO’ then ‘PURCHASE’ else (case when rch.order_type1 = ‘MI’ then ‘CPP’ else (case when rch.order_type1= ‘RO’ then ‘REPAIR’ else (case when rch.order_type1= ‘SM’ then ‘TRANSFER’ else ‘PENTAGON’ end) end) end) end) end) end) from rc_header rch, rc_detail rcd where rcd.rcd_auto_key=+ stk.rcd_auto_key and rcd.rch_auto_key=+ rch.rch_auto_key) “STK LAST SOURCE”,(select condition_code from part_condition_codes pcc where stk.pcc_auto_key=pcc.pcc_auto_key) “STK CONDITION”,(select location_code from location loc where stk.loc_auto_key=loc.loc_auto_key) “STK LOCATION”,(select warehouse_code from warehouse wh where stk.whs_auto_key=wh.whs_auto_key) “STK WAREHOUSE”,(select group_code from pn_groups png where pmn.png_auto_key=png.png_auto_key) “PN GROUP”,(select consignment_code from consignment_codes csg where stk.cnc_auto_key=csg.cnc_auto_key and csg.consignment_flag=’T’) “STK CSNG CODE”,(case when stk.stm_lot > 0 then (select wo.si_number from wo_operation wo, stock_reservations str where stk.stm_lot=str.stm_auto_key and str.woo_auto_key=wo.woo_auto_key) else (select wo.si_number from wo_operation wo, stock_reservations str where stk.stm_auto_key=str.stm_auto_key and str.woo_auto_key=wo.woo_auto_key)end) “LOT NUMBER”,(case when stk.rec_date < stk.order_rec_date then stk.order_rec_date else stk.rec_date end) "LAST RECEIVE DATE",(select max(case when stk1.rec_date < stk1.order_rec_date then stk1.order_rec_date else stk1.rec_date end) from stock stk1 where stk.pnm_auto_key=stk1.pnm_auto_key and stk1.qty_oh>0) “PN NEWEST REC DATE”,(select min(case when stk1.rec_date < stk1.order_rec_date then stk1.order_rec_date else stk1.rec_date end) from stock stk1 where stk.pnm_auto_key=stk1.pnm_auto_key and stk1.qty_oh>0) “PN OLDEST REC DATE”,(select sum(ind.qty_ship) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTY SOLD ALL TIME”,(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTY SOLD EX SINCE 01/07”,(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTY SOLD OUTRT SINCE 01/07”,(select sum(ind.qty_ship*ind.unit_cost) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT CST SOLD EX SINCE 01/07”,(select sum(ind.qty_ship*ind.unit_price) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PRC SOLD EX SINCE 01/07”,(select sum(ind.qty_ship*ind.unit_cost) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT CST SOLD OUTRT SINCE 01/07”,(select sum(ind.qty_ship*ind.unit_price) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PRC SOLD OUTRT SINCE 01/07”,(select max(ind.invoice_date) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key) “LST SOLD”,(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-366)) “1YR EX DMD”,(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-732)) “2YR EX DMD”,(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-366)) “1YR OUTRT DMD”,(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-732)) “2YR OUTRT DMD”,(select sum(ind.qty_ship) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-366)) “1YR TOT DMD”,(select sum(ind.qty_ship) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-732)) “2YR TOT DMD”,(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QUOTE QTY ALL TIME”,(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QUOTE QTY SINCE 01/07”,(select sum(qte.qty_quoted*qte.unit_cost) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTE COST SINCE 01/07”,(select sum(qte.qty_quoted*qte.unit_price) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTE PRICE SINCE 01/07”,(select avg(qte.qty_quoted*qte.unit_cost) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG QTE COST SINCE 01/07”,(select avg(qte.qty_quoted*qte.unit_price) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG QTE PRICE SINCE 01/07”,(select max(qte.entry_date) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key) “LST QUOTED”,(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date > (CURRENT_TIMESTAMP-366)) “1YR QTES”,(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date > (CURRENT_TIMESTAMP-732)) “2YR QTES”,(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT RO QTY ALL TIME”,(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT RO QTY SINCE 01/07”,(select sum(ro.qty_repaired*(ro.labor_cost+ro.misc_cost+ro.parts_cost)) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT RO COST SINCE 01/07”,(select avg(ro.qty_repaired*(ro.labor_cost+ro.misc_cost+ro.parts_cost)) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG RO COST SINCE 01/07”,(select max(ro.LAST_DELIVERY_DATE) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key) “LST REPAIRED”,(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-366)) “1YR RO”,(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-732)) “2YR RO”, (select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PO QTY ALL TIME”,(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PO QTY SINCE 01/07”,(select sum(po.qty_rec*po.unit_cost) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PO COST SINCE 01/07”,(select avg(po.qty_rec*po.unit_cost) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG PO COST SINCE 01/07”,(select max(po.LAST_DELIVERY_DATE) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key) “LST PURCHASED”,(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-366)) “1YR PO”,(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-732)) “2YR PO” from parts_master pmn, stock stk where stk.pnm_auto_key=pmn.pnm_auto_key and stk.qty_oh>0

[Worthington Aviation]

We do something similar here. What we use is querries to pull the data into excel, then we can filter from there.
Here is the querry that we use. You can run this in interactive SQL in quantum. It is designed around the Oracle DB.
select
stk.stm_auto_key “STOCK AUTO KEY”,
stk.pnm_auto_key “PN AUTO KEY”,
pmn.pn “PN”,
pmn.description “DESCRIPTION”,
pmn.sdf_pnm_002 “SEGMENT”,
pmn.sdf_pnm_001 “PRODUCT LINE”,
pmn.qty_oh “TOT PN QTY OH”,
pmn.custom_value “PN ASSIGNED CUSTOMS VALUE”,
pmn.qty_oh*pmn.custom_value “PN TOT VALUE ON HAND”,
stk.qty_oh “STK QTY OH”,
stk.qty_reserved “STK RES QTY”,
–//pmn.custom_value*stk.qty_oh “STK EXT VALUE ON HAND”,
(case when (select pcc.cond_level from part_condition_codes pcc where stk.pcc_auto_key=pcc.pcc_auto_key)=1 then (pmn.custom_value)*stk.qty_oh else
(case when (select pcc.cond_level from part_condition_codes pcc where stk.pcc_auto_key=pcc.pcc_auto_key)=2 then (pmn.custom_value)*stk.qty_oh else
(case when (select pcc.cond_level from part_condition_codes pcc where stk.pcc_auto_key=pcc.pcc_auto_key)=3 then (pmn.custom_value*.45)*stk.qty_oh else
0 end)end)end) “EXT CALC VALUE”,
(select pcc.condition_code from part_condition_codes pcc where stk.pcc_auto_key=pcc.pcc_auto_key) “STK CONDITION”,
(select pcc.cond_level from part_condition_codes pcc where stk.pcc_auto_key=pcc.pcc_auto_key) “STK COND LEVEL”,
stk.qty_oh*stk.unit_cost “STK EXT CST ON HAND”,
stk.liquidate_option “LIQUIDATE”,
stk.tag_date “TAG DATE”,
stk.serial_number “SERIAL NUMBER”,
(case when stk.stm_lot>0 then ‘LOT’ else (case when stk.original_po_number <> ” then ‘PURCHASE’ else (case when stk.pod_auto_key >0 then ‘PURCHASE’ else (case when stk.rec_date < to_date('2004/01/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')then 'PENTAGON' else 'UNKNOWN' end) end) end) end) "ORG SOURCE", (select (case when stk.ind_auto_key>0 and stk.qty_credit>0 then ‘RMA’ else(case when rch.order_type1 = ‘SO’ then ‘CORE’ else (case when rch.order_type1 = ‘PO’ then ‘PURCHASE’ else (case when rch.order_type1 = ‘MI’ then ‘CPP’ else (case when rch.order_type1= ‘RO’ then ‘REPAIR’ else (case when rch.order_type1= ‘SM’ then ‘TRANSFER’ else ‘PENTAGON’ end) end) end) end) end) end)
from rc_header rch, rc_detail rcd where rcd.rcd_auto_key=+ stk.rcd_auto_key and rcd.rch_auto_key=+ rch.rch_auto_key) “STK LAST SOURCE”,
(select location_code from location loc where stk.loc_auto_key=loc.loc_auto_key) “STK LOCATION”,
(select warehouse_code from warehouse wh where stk.whs_auto_key=wh.whs_auto_key) “STK WAREHOUSE”,
(select group_code from pn_groups png where pmn.png_auto_key=png.png_auto_key) “PN GROUP”,
(select consignment_code from consignment_codes csg where stk.cnc_auto_key=csg.cnc_auto_key and csg.consignment_flag=’T’) “STK CSNG CODE”,
(case when stk.stm_lot > 0 then (select wo.si_number from wo_operation wo, stock_reservations str where stk.stm_lot=str.stm_auto_key and str.woo_auto_key=wo.woo_auto_key)
else (select wo.si_number from wo_operation wo, stock_reservations str where stk.stm_auto_key=str.stm_auto_key and str.woo_auto_key=wo.woo_auto_key)end) “LOT NUMBER”,
(case when stk.rec_date < stk.order_rec_date then stk.order_rec_date else stk.rec_date end) "LAST RECEIVE DATE", (select max(case when stk1.rec_date < stk1.order_rec_date then stk1.order_rec_date else stk1.rec_date end) from stock stk1 where stk.pnm_auto_key=stk1.pnm_auto_key and stk1.qty_oh>0) “PN NEWEST REC DATE”,
(select min(case when stk1.rec_date < stk1.order_rec_date then stk1.order_rec_date else stk1.rec_date end) from stock stk1 where stk.pnm_auto_key=stk1.pnm_auto_key and stk1.qty_oh>0) “PN OLDEST REC DATE”,
(select sum(ind.qty_ship) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTY SOLD ALL TIME”,
(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTY SOLD EX SINCE 01/07”,
(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTY SOLD OUTRT SINCE 01/07”,
(select sum(ind.qty_ship*ind.unit_cost) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT CST SOLD EX SINCE 01/07”,
(select sum(ind.qty_ship*ind.unit_price) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PRC SOLD EX SINCE 01/07”,
(select sum(ind.qty_ship*ind.unit_cost) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT CST SOLD OUTRT SINCE 01/07”,
(select sum(ind.qty_ship*ind.unit_price) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PRC SOLD OUTRT SINCE 01/07”,
(select max(ind.invoice_date) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key) “LST SOLD”,
(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-366)) “1YR EX DMD”,
(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’E’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-732)) “2YR EX DMD”,
(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-366)) “1YR OUTRT DMD”,
(select sum(ind.qty_ship) from invc_detail ind where ind.route_code=’S’ and ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-732)) “2YR OUTRT DMD”,
(select sum(ind.qty_ship) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-366)) “1YR TOT DMD”,
(select sum(ind.qty_ship) from invc_detail ind where ind.pnm_auto_key=pmn.pnm_auto_key and ind.invoice_date > (CURRENT_TIMESTAMP-732)) “2YR TOT DMD”,
(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QUOTE QTY ALL TIME”,
(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QUOTE QTY SINCE 01/07”,
(select sum(qte.qty_quoted*qte.unit_cost) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTE COST SINCE 01/07”,
(select sum(qte.qty_quoted*qte.unit_price) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT QTE PRICE SINCE 01/07”,
(select avg(qte.qty_quoted*qte.unit_cost) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG QTE COST SINCE 01/07”,
(select avg(qte.qty_quoted*qte.unit_price) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG QTE PRICE SINCE 01/07”,
(select max(qte.entry_date) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key) “LST QUOTED”,
(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date > (CURRENT_TIMESTAMP-366)) “1YR QTES”,
(select sum(qte.qty_quoted) from cq_detail qte where qte.pnm_auto_key=pmn.pnm_auto_key and qte.entry_date > (CURRENT_TIMESTAMP-732)) “2YR QTES”,
(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT RO QTY ALL TIME”,
(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT RO QTY SINCE 01/07”,
(select sum(ro.qty_repaired*(ro.labor_cost+ro.misc_cost+ro.parts_cost)) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT RO COST SINCE 01/07”,
(select avg(ro.qty_repaired*(ro.labor_cost+ro.misc_cost+ro.parts_cost)) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG RO COST SINCE 01/07”,
(select max(ro.LAST_DELIVERY_DATE) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key) “LST REPAIRED”,
(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-366)) “1YR RO”,
(select sum(ro.qty_repaired) from ro_detail ro where ro.pnm_auto_key=pmn.pnm_auto_key and ro.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-732)) “2YR RO”,
(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘1899/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PO QTY ALL TIME”,
(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PO QTY SINCE 01/07”,
(select sum(po.qty_rec*po.unit_cost) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “TOT PO COST SINCE 01/07”,
(select avg(po.qty_rec*po.unit_cost) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE >= to_date(‘2007/01/01:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’)) “AVG PO COST SINCE 01/07”,
(select max(po.LAST_DELIVERY_DATE) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key) “LST PURCHASED”,
(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-366)) “1YR PO”,
(select sum(po.qty_rec) from po_detail po where po.pnm_auto_key=pmn.pnm_auto_key and po.LAST_DELIVERY_DATE > (CURRENT_TIMESTAMP-732)) “2YR PO”
from parts_master pmn, stock stk where
stk.pnm_auto_key=pmn.pnm_auto_key
and stk.qty_oh>0

[Worthington Aviation]

Matt, sometimes the tables keep the filter of the data that was first typed in, so when you enter a new value, it “appears” to not have been entered. Have them do a find with nothing in the find field and see if that pulls up the value.

[Worthington Aviation]

Jackie, I suggest you look into using the Stock Issue function for this type of activity.

It was designed specifically for this purpose, and would probably be much, much easier.

[Worthington Aviation]

After reading your post again, we could be on the same page. So, the answer to your question may be YES if you are talking about the auto number logs, and using an “R” as the auto number prefix.

[Worthington Aviation]

Ken, not exactly. I am suggesting you create a second number log for sales orders.

Then, when you do a rental order, you would select the new number log.

See help chapter on System Setup, Auto-Numbering

Or the training video on the Component Control website.

[Worthington Aviation]

Since there is a one to many relationship there, your best bet is to create a sub report that shows all linked details.

The reason you do not see the link is because there is an intermediate table.

You should have a main report that links to the sub report. The link should be SOD_auto_key

and link to the sod_auto_key in the purchase_sales table

In the sub report, the purchase sales table pod_auto_key should link to the po_detail pod_auto_key and the po_detail poh_auto_key should link to the po_header poh_auto_key

All inter module order linking works pretty much the same way. If it was a straight link from the SO_Detail to the PO_detail then it would be very difficult to manage a one to many relationship in the program

[Worthington Aviation]

Wow I didn’t even know MAS 90 was still around.

I believe there are a few users out there using great planes (check with Pilatus?) and CC may even have a bridge for it.

But, I have to ask the question if you are changing, why not just use QC Accounting?

We’ve used it for 10 years, and our company is run and audited like a public company. We manage pretty well.

[Worthington Aviation]

Betsy,

Yes, this is because the remote session you are connecting to is the same user.

When they start up their remote desktop connection, what is the network user account they are using to log into the remote session?

If they are different, then it is because you are using a shared installation of Quantum. So, when the images are scanned, a single cache is shared between the two session.

To solve this..

1. Make sure each workstation/user is using a unique log in

2. Make sure you have a QC installation for each user account. Component Control can help you set that up.

My guess is your issue is number 2

[Worthington Aviation]

Ken – you could create a unique number log for your rental transactions. Then, when creating the order, simply select the number log that is applicable.

This assumes you do not put rentals on the same orders as regular sales.

You can also use the SO Category – but this doesn’t show on the core reports.

[Worthington Aviation]

Ken, without the rental lease module, the best way to do it then is to just sell them as exchanges and/or loaners.

Then track the rental units using the standard core reports.

I suggest you go through a couple of test transactions in your test database to see which method will work best.

The difference between an exchange and a loaner is that an exchange allows for any part to be returned as the core, whereas using the loan option forces the receipt of the same unit back against the order.

[Worthington Aviation]

from the rental lease drop down menu, select reports.

Here you can run the lease journal report which allows you to run the open lease transactions.

You can choose to show only open leases.

For the stock rentals, run the renewal report, periodic billing or recurring billing reports.

Since rentals and leases typically have a recurring payment, the function of tracking those payments also provides insight to the return of the rental unit.

If you are looking for different ways to do this, look at my old presentation called Creative Exchanges. http://quegroup.camp7.org/Resources/Documents/que_group_presents_2010_creativeexchanges.zip

You also have the option of creating your own report in crystal reports and pull this information in.

[Worthington Aviation]

Tony, I think this is a registry setting. If not, it would be (most likely) in the table called quantum I think.

[Worthington Aviation]

There are reports in the rental lease module that are meant to handle those transactions.

They are not considered cores, but open rentals.

[Worthington Aviation]

We use the HP Scanjet with document feeder. Works great. http://h20000.www2.hp.com/bizsupport/TechSupport/DriverDownload.jsp?prodNameId=3723537&locale=en_us&taskId=135&prodSeriesId=3723469&prodTypeId=15179

[Worthington Aviation]

I sent mine over to you.

[Worthington Aviation]

Billy you are absolutely correct! I was thinking stock lines, not part numbers.

[Worthington Aviation]

Test confirmed

[Worthington Aviation]

Jackie – I sent you an expired parts crystal report via email This should work good as a basis for your report.

[Worthington Aviation]

Cory, we modified out barcode part labels a while back to include the sys_user code of the person who entered the part. Here is the snippet to pull this into a label object in the forms designer var q : TOracleDataset; Cost: Double; begin q := TOracleDataset.Create(nil); q.SetSession; q.SQL.Text := ‘Select EMPLOYEE_CODE ‘ + ‘from sys_users sur, stock stm where stm.sysur_AUTO_KEY= sur.sysur_AUTO_KEY and stm.STM_AUTO_KEY =’+ PN[‘STM_AUTO_KEY’]; q.Open; Text:=’ENT BY: ‘+q.FieldByName[’employee_code’].AsString; q.Free; end; If you want to do just an SQL statement in interactive SQL use this Select EMPLOYEE_CODE from sys_users sur, stock stm where stm.sysur_AUTO_KEY= sur.sysur_AUTO_KEY and stm.STM_AUTO_KEY = PUT YOUR STM_AUTO_KEY here To get your STM auto key, open the stock line and press CTRL+F10

[Worthington Aviation]

Bill, yes I do believe this is a limitation to the event manager.

The event manager is an extremely powerful tool, but can fall short on a few things like this.

A cheap solution for this to the VisualCut tool from Millet Software. His tool handles a huge variety of tasks like this, and quite a bit more.

[Worthington Aviation]

From the RO_Detail table link the sod_auto_key to the SO_Detail table with a left outer join. From SO_Detail to SO_Header link soh_auto_key with a left outer join. This will get you the SO number when present. The key is the outer join. For WO, do the following From the RO_Detail table link the wob_auto_key to the wo_bom table with a left outer join. From wo_bom to wo_operation link woo_auto_key with a left outer join. If you want to keep the report clean, you can use this little trick. 1. Take the si_number field and the so_number field and place them on the report in the exact same place, so that they are on top of each other. 2. Create a formula that says if woo_auto_key>0 then ToText(‘WO #”) else if soh_auto_key>0 then ToText(“SO #”) else ToText(“”) Take this formula and place it next to the order number fields. The result should be that when you order is linked to a work order, the WO number will appear and your forumula will say WO # Likewise for the SO number.

[Worthington Aviation]

Jackie, I do not believe there is such a tool. I recommend getting the following books “Learning SQL” Oreilly “SQL Cookbook” Oreilly “Oracle RL/SQL Programming” Oreilly “Head First Data Analysis” Oreilly I have found these books to be pretty useful.

[Worthington Aviation]

Do you mean a software tool, or a person with the skill set?

[Worthington Aviation]

Jackie, we can certainly help with that.

But, you need to offer a great deal more information.

Are you doing this in crystal? Are you doing this in Forms Designer?

What is your current table structure if you are doing this in Crystal?

Is the RO actively linked to the SO or WO ? Or, are you trying to pull historical data after the fact?

Is this information being printed before or after the RO is received?

[Worthington Aviation]

Nothing like this exists that I am aware.

You could accomplish something similar (not as smooth) using a tablet and a wireless printer. You can get wireless barcode printers that are designed to be worn on the hip.

So, using a table to access QC, you could push the label out to the wireless unit right on on your hip.

That is the only short term suggestion I have.

[Worthington Aviation]

Did you post the invoice already? If you did, it may not print. don’t know for sure. We use the shipping module to avoid these types of issues.

[Worthington Aviation]

No there is not. Unless you do it through SQL

[Worthington Aviation]

Sandy – This is good feedback. Although we don’t use the MO, it is similar in function to a WO.
This definitely needs to be reported to CC as it is an issue.
The only suggestion I have is to adjust the cost of the stock lines that were turned in, then move the proper amount of money back to the stock lines that remain.
However, since this is MO – those stock lines probably do not exist, so you may need to apply them to some sort of BOM item or something. Perhaps create a PN called adjustments, create fake stock lines, move the money to those (applying substantial notes to clarify what is happening) then issue that back to the MO.

[Worthington Aviation]

I don’t have EM yet – but this message looks to me like it could not run the report because the report couldn’t connect to the DB

Just reaching for something I guess..

[Worthington Aviation]

Bill – the “ready” flag is a calculated flag based on qty reserved.

If Qty_Reserved >0 then the flag is thrown

[Worthington Aviation]

Sean, welcome to the group! I hope you get valuable use of the forums and other content here. We look forward to learning from you as well.

[Worthington Aviation]

Tom,

QC Does not have that capability. It could be done, however, through crystal reports. You would likely have to purchase and install a 2D barcode plugin for crystal, but I am willing to be you could make it work.

[Worthington Aviation]

Tony – I have never looked into this, but if you find more out please post to the forum. I would be interested to learn more.

[Worthington Aviation]

Ken,

It sounds to me like you are speaking of your balance sheet and income statement. These are standard accounting financial documents that are included in accounting.

[Worthington Aviation]

Scott – licenses are required for the document imaging to work.

Once licensed, the requirements are primarily around storage space. A small application is run on the machine that holds the images. The ports will be specified on the imaging server, then in QC you would setup the imaging path in your system settings.

[Worthington Aviation]

Good morning. You should use the training records functions found off of the system setup, maintain system tables, users.

THis is where you input the profile of the user, and at the bottom of this you can find buttons for training records.

Hope that helps.

[Worthington Aviation]

Ken, I undertand. However, again this is a computer operating system setting, not Quantum. It is pretty standard.

[Worthington Aviation]

This is not controlled by the Quantum Software, but rather your regional settings on your local computer.

If you are on a network, these settings may be controlled by your domain server.

[Worthington Aviation]

Hi Tim, looking forward to seeing you at the conference. Worthington Aviation is ASA certified, and ISO certified.

We also have annual audits by independent firms which are held to the same standards as a public company.

[Worthington Aviation]

Ken,

The only way to do that is through an SQL statement where you would delete them based on some sort of constant.

These samples would do it for the entire RO.

If you wanted to restrict it to a specific line item, just at at the end

” and rod.item_number=’item number here’ “

The select statement for that would be something like this..

select str.* from stock_reservations str where str.RPP_AUTO_KEY in (select rp2.rpp_auto_key from

ro_piece_parts rp2, ro_detail rod, ro_header rh where

rp2.rod_auto_key=rod.rod_auto_key and

rod.roh_auto_key=rh.roh_auto_key and

rh.ro_number=’R27043′)

The delete statement would be something like this..

delete str.* from stock_reservations str where str.RPP_AUTO_KEY in (select rp2.rpp_auto_key from

ro_piece_parts rp2, ro_detail rod, ro_header rh where

rp2.rod_auto_key=rod.rod_auto_key and

rod.roh_auto_key=rh.roh_auto_key and

rh.ro_number=’R27043′)

Replace the RO number with your RO number.

ALWAYS TEST IN A TEST DB FIRST!

[Worthington Aviation]

Ken, you would need the forms designer module to be able to modify those documents.

If you have it, or once you get it, we can help you include the fields you need.

Your other option to build your own forms outside of QC using Crystal reports, and a tool like Datalink Viewer to run those reports.

[Worthington Aviation]

Do you have the Screen Scriptor module, or the forms designer module?

Is your question relative to showing this on the header of the screen in the module, or in your printed document?

[Worthington Aviation]

Dave – you could go with the department code option. Not quite sure how to answer your question without understanding the business model and structure.

[Worthington Aviation]

There is no BOM import functionality that I am aware of. You could develop an import script with the DIA tools, but by the time you finished that you can do it manually.

[Worthington Aviation]

Ken, I think you are stuck doing this one manually. I cannot think of any SQL statements that might help.

[Worthington Aviation]

Bob – we used the Forms Designer module to pull that information in.

Our customs invoice also will pull in the customs value if the shipping order is zero.

I can send you the customs invoice, and a regular invoice document if you have the forms designer module.

Paul

[Worthington Aviation]

I do not recommend using the GEO code.

I believe it goes against best practice to create records like this to accommodate a situation that is not part of standard practice. Eventually those unit will be sold, and then what? You have bogus GEO Codes that you have to live with for the rest of the time you use Quantum. Plus, this would impact your minimum qty functions for inventory planning, your warehouse reporting, your ongoing stock reservations when you do need to sell it, etc. etc.

The hold flag was built into the lot specifically for this purpose. If you open the lot, you see at the bottom there is a hold/unhold button that sets the flag without even inspecting/editing the stock line. This would allow you to easily remove them from hold to sell them one by one.

Plus – in the newest release there is now a “Reason For Hold” that is available so you can actually set a reason such as Hold, Quarantine, Scrapped, Deleted, PI Count

So, I recommend you do that SQL statement and put them on hold. It is pretty straight forward..

update stock set hold_line=’T’ where stm_lot = [your lot stm auto key here] and

qty_oh>0

For the new release

update stock set hold_line=’T’, reason_for_hold=[insert the hold reason text here based on available values] where stm_lot = [your lot stm auto key here] and

qty_oh>0