Hey Scott!
I just recently accomplished this using a reporting tool called CorVu, which
generates SQL code. Here is the code that I used:
SELECT
e.po_vend,
f.vd_sort,
a.sch_nbr,
a.sch_line,
a.sch_rlse_id,
b.pod_part,
c.pt_desc1,
c.pt_part_type,
MAX( d.schd_cum_qty ),
b.pod_cum_qty[1],
SUM( d.schd_discr_qty )
FROM
cvlmfgpro.sch_mstr a,
cvlmfgpro.pod_det b,
cvlmfgpro.pt_mstr c,
cvlmfgpro.schd_det d,
cvlmfgpro.po_mstr e,
cvlmfgpro.vd_mstr f
WHERE
( a.sch_line = d.schd_line AND
a.sch_nbr = d.schd_nbr AND
a.sch_rlse_id = d.schd_rlse_id AND
a.sch_type = d.schd_type AND
b.pod_line = d.schd_line AND
b.pod_nbr = d.schd_nbr AND
c.pt_part = b.pod_part AND
e.po_nbr = d.schd_nbr AND
f.vd_addr = e.po_vend AND
a.sch_type = 4 AND
( d.schd_date >= &"Enter from date" AND
d.schd_date <= &"Enter to Date") AND
d.schd_fc_qual = "F"
)&@0,1
GROUP BY
a.sch_nbr,
a.sch_line
HAVING
a.sch_rlse_id = b.pod_curr_rlse_id[1] AND
e.po_vend = '&"Supplier Nbr"'
As you can see, the current release of the Supplier Schedule is linked to
the Purchase Order Detail by:
schd_nbr = pod_nbr AND
schd_line = pod_line AND
sch_rlse_id = pod_curr_rlse_id[1]
A few things to note:
schd_cum_qty (Total qty ordered over life of SS - do not sum)
pod_cum_qty[1] (Total qty received over life of SS)
SUM(schd_discr_qty) (Qty received for current release)
We can then calculate Qty Due, Over Receipts, etc.
Good Luck!
Steve Lippard Voice: (727)545-0400 x7797
Programmer/Systems Analyst Fax: (727)546-4732
Transitions Optical, Inc. Email: slippard@transitions.com
Pinellas Park, FL Web:
www.transitions.com
Peg Membership#: 1998121406
