Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <cfquery datasource="myDSN" name="getParts">
- select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, sum(w.allocqty) as allocated, sum(w.onhandqty) as onhand
- from part p with(nolock), [otherServer].internal.dbo.report_tracker t with(nolock),
- partwhse w with(nolock)
- where p.company = 'PC68300'
- and w.company = p.company
- and w.partnum = p.partnum
- and p.partnum = t.partnum
- group by p.partnum, cast(p.partdescription as varchar(100))
- </cfquery>
- <cfquery datasource="myDSN" name="getjobs">
- select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, count(j.jobnum) as jobcount, sum(requiredqty) as qtyNeeded, sum(issuedqty) as qtysent
- from part p with(nolock), [otherServer].internal.dbo.report_tracker t with(nolock),
- jobmtl j with(nolock)
- where p.company = 'PC68300'
- and j.company = p.company
- and j.partnum = p.partnum
- and p.partnum = t.partnum
- group by p.partnum, cast(p.partdescription as varchar(100))
- </cfquery>
- <cfquery datasource="myDSN" name="getOrders">
- select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, count(d.ordernum) as ordercount, sum(d.orderqty) as ordered, sum(s.ourinventoryshipqty + ourjobshipqty) as shipqty
- from part p with(nolock), [otherServer].internal.dbo.report_tracker t with(nolock),
- orderdtl d with(nolock) left join shipdtl s with(nolock) on (
- d.company = s.company and d.partnum = s.partnum and d.ordernum = s.ordernum )
- where p.company = 'PC68300'
- and d.company = p.company
- and d.partnum = p.partnum
- and p.partnum = t.partnum
- group by p.partnum, cast(p.partdescription as varchar(100))
- </cfquery>
- <cfquery datasource="myDSN" name="getIncoming">
- select partnum, sum(quantity) as recqty
- from partdtl with (nolock)
- where PartDtl.Company = 'PC68300'
- and PartDtl.Type = 'Mtl'
- and PartDtl.Plant = 'MfgSys'
- and PartDtl.RequirementFlag = 0
- and jobnum not like 'MRP%'
- and PartDtl.partnum in (select partnum from [otherServer].internal.dbo.report_tracker t with(nolock))
- group by partnum
- </cfquery>
Advertisement
Add Comment
Please, Sign In to add comment