BOLT
select
jb.IndustryCommodityCode as IndustryCommodeityCode,
jb.ShortMaterialDescription as MaterialDescription,
jrb.BoltQuantity as Quantity,
jrb.RoundedLength*1000 as Length,
jrb.Diameter*1000/25.4 as Diameter,
jdps.SpecName,
jn1.ItemName,
'set' as Unit
from JRteBolt jrb
left join XImpliedMatingParts ximp on jrb.oid=ximp.oidOrigin
left join JBolt jb on ximp.oidDestination=jb.oid
Join XOwnsImpliedItems xoii on xoii.OidDestination = jrb.Oid
join XOwnsParts xop on xop.OidDestination = xoii.OidOrigin
Join JRtePipeRun jrpr on jrpr.Oid = xop.OidOrigin
Join XSystemHierarchy xsh on xsh.OidDestination = jrpr.Oid
Join JPipelineSystem jps on jps.oid = xsh.OidOrigin
Join JNamedItem jn1 on jn1.oid = jps.oid
Join XPathRunUsesSpec xprus on xprus.OidOrigin = jrpr.oid
Join JDPipeSpec jdps on jdps.oid = xprus.OidDestination
WHERE jrb.oid IN (SELECT oid FROM @RPTtemptable)
GASKET
select
jg.IndustryCommodityCode AS CommodityCode,
jg.ShortMaterialDescription as ShortMaterialDescription,
jrpr.NPD as NPD,
jrpr.NPDUnitType AS UnitType,
jdps.SpecName,
jn1.ItemName,
1 as Item,
'pcs' as Unit
from JRteGasket jrg
Join JRteConnectionPart jrcp on jrcp.oid = jrg.oid
Join XImpliedMatingParts ximp on ximp.oidOrigin = jrcp.oid
Join JGasket jg on jg.oid = ximp.oidDestination
Join XOwnsImpliedItems xoii on xoii.OidDestination = jrg.Oid
join XOwnsParts xop on xop.OidDestination = xoii.OidOrigin
Join JRtePipeRun jrpr on jrpr.Oid = xop.OidOrigin
Join XSystemHierarchy xsh on xsh.OidDestination = jrpr.Oid
Join JPipelineSystem jps on jps.oid = xsh.OidOrigin
Join JNamedItem jn1 on jn1.oid = jps.oid
Join XPathRunUsesSpec xprus on xprus.OidOrigin = jrpr.oid
Join JDPipeSpec jdps on jdps.oid = xprus.OidDestination
WHERE jrg.oid IN (SELECT oid FROM @RPTtemptable)
PIPE
select
jrsp.oid,
jrsp.Length,
clst.ShortStringValue as Schedule,
jgmc.ContractorCommodityCode as CommodityCode,
jgmc.ShortMaterialDescription as MaterialsDescription,
jrpr.NPD as NPD,
'meter' as unit,
1 as item,
jdps.SpecName as Specification,
jni.itemname as linename
from JRteStockPartOccur jrsp
join XmadeFrom xmf on xmf.oidOrigin = jrsp.oid
join JDPipeComponent jdpc on jdpc.oid = xmf.OidDestination
join XPartOccToMaterialControlData xpom on xpom.OidOrigin = jrsp.oid
join JGenericMaterialControlData jgmc on jgmc.oid = xpom.OidDestination
join YPipelineToPipingParts xop1 on xop1.oidOrigin = jrsp.oid
join XOwnsParts xop on xop.oidDestination = jrsp.oid
join JRtePipeRun jrpr on jrpr.oid = xop.oidOrigin
join JNamedItem jni on jni.oid = xop1.oidDestination
join XPathRunUsesSpec xprus on xprus.oidOrigin = jrpr.oid
join JDPipeSpec jdps on jdps.oid = xprus.oidDestination
join CL_ScheduleThickness clst on clst.valueid = jdpc.FirstSizeSchedule
WHERE jrsp.oid IN (SELECT oid FROM @RPTtemptable)
FITTING
Select
jrppf.oid as jrppfoid,
jpo.oid as jpooid,
jrppf.Tag as Tag,
jgmc.ContractorCommodityCode as CommodityCode,
jgmc.ShortMaterialDescription as MaterialDescription,
jpc.PrimarySize as PrimarySize,
jpc.PriSizeNPDUnits as PriNPDUnits,
jpc.SecondarySize as SecondarySize,
jpc.SecSizeNPDUnits as SecNPDUnits,
jdps.SpecName as SpecName,
jni.ItemName as ItemName,
1 as item,
'Piecs' as Unit
from JRtePipePathFeat jrppf
join XPathGeneratedParts xpgp on xpgp.OidOrigin = jrppf.oid
join JPartOcc jpo on jpo.oid = xpgp.OidDestination
join XPartOccToMaterialControlData xpom on xpom.OidOrigin = jpo.oid
join JGenericMaterialControlData jgmc on jgmc.oid = xpom.OidDestination
join XmadeFrom xf on xf.OidOrigin = jpo.oid
join JDPipeComponent jpc on jpc.oid = xf.OidDestination
join XPathSpecification xps on xps.oidDestination = jrppf.oid
join JRtePathRun jrpr on jrpr.oid = xps.oidOrigin
join XPathRunUsesSpec xprus on xprus.oidOrigin = jrpr.oid
join JDPipeSpec jdps on jdps.oid = xprus.oidDestination
join YPipelineToPipingParts yptpp on yptpp.oidOrigin = jpo.oid
join JNamedItem jni on jni.oid = yptpp.oidDestination
WHERE jpo.oid IN (SELECT oid FROM @RPTtemptable)
WELD
select
'Pipe Weld' as WeldType,
x8.Npd as Npd,
x8.NpdUnitType,
x8.Npd * 3.1415926 as Length,
//jrspo.Length as PipeLength,
jnRun.ItemName,
jdps.SpecName
from
JRteWeld x1
join JrteConnectionItemOwner x2 on x2.oid = x1.oid
join XOwnsImpliedItems x3 on x3.oidDestination = x2.oid
join JRtePathGenPart x4 on x4.oid = x3.oidOrigin
//join JRteStockPartOccur jrspo on jrspo.oid = x3.oidOrigin
join XPathGeneratedParts x5 on x5.oidDestination = x4.oid
join JRtePathFeature x6 on x6.oid = x5.oidOrigin
join XPathSpecification x7 on x7.oidDestination = x6.oid
join JRtePipeRun x8 on x8.oid = x7.oidOrigin
join JNamedItem jnRun on jnRun.oid = x8.oid
join XPathRunUsesSpec xprus on xprus.oidOrigin = x8.oid
join JDPipeSpec jdps on jdps.oid = xprus.oidDestination
WHERE x1.oid IN (SELECT oid FROM @RPTtemptable)
GROUP BY ???
------------------------------------------------------------------------------------------------
1:Ctrl+Shift+R
2:C:\Win32app\SmartPlant\3D\Core\Tools\Administrator\Bin\MetaDataBrowser
CommonRoute Business Services\PipelineToPipingParts.
3:select * from SP3DTrain_CDB.dbo.REFDATCommodityMatlCtrlData
4:select * from SP3DTrain_CDB.dbo.REFDATPipePartSpec
5:select * from SP3DTrain_CDB.dbo.REFDATPipeSpec
6:http://www.caxperts.com/content/view/62/38/lang,en/
