I want to build BAQ to extract BOM according to Horizontal
now it is possible to get the original BOM level 0 which needs further decay of the MtlPartNum lines marked as PullAsAsm or ViewAsAsm but is there a case where the RevisionNum of MtlPartNum is different from the RevisionNum of the PartNum? So how can I get the RevisionNum of MtlPartNum like in the ViewCosts method of Erp.Proxy.BO.EngWorkBenchImpl
DECLARE @TablePartMaxRev Table (Company nvarchar(4),PartNum nvarchar(50), RevisionNum nvarchar(20), DateMaxChangOn datetime)
insert into @TablePartMaxRev
select Company,PartNum, RevisionNum, MAX(ChangedOn) from PartRev where Company='01' and Approved=1
group by Company,PartNum, RevisionNum
order by PartNum
--12754
--select * from @TablePartMaxRev
--select * from @TablePartMaxRev where PartNum='EG0000000029'
--select MAX(DateMaxChangOn) from @TablePartMaxRev where PartNum='EG0000000029'
DECLARE @TablePartMax Table (Company nvarchar(4) ,PartNum nvarchar(50), DateMaxChangOn datetime)
insert into @TablePartMax
select Company, PartNum, max(DateMaxChangOn) from @TablePartMaxRev
group by Company, PartNum
--8051
DECLARE @TableMaxChangOnPartandRev Table (Company nvarchar(4), PartNum nvarchar(50), RevisionNum nvarchar(20),DateMaxChangOn datetime)
insert into @TableMaxChangOnPartandRev
select pr.* from @TablePartMax as p
inner join @TablePartMaxRev as pr on p.PartNum=pr.PartNum and p.DateMaxChangOn=pr.DateMaxChangOn
DECLARE @TableBomPartandRev Table (Company nvarchar(4),PartNum nvarchar(50), RevisionNum nvarchar(20),PartMtl nvarchar(50),PullAsAsm bit,DateMaxChangOn datetime)
insert into @TableBomPartandRev
select pmtl.Company, pmtl.PartNum, pmtl.RevisionNum, pmtl.MtlPartNum, pmtl.PullAsAsm,prm.DateMaxChangOn
from PartMtl as pmtl inner join @TableMaxChangOnPartandRev as prm on pmtl.Company=prm.Company
and prm.PartNum=pmtl.PartNum and pmtl.RevisionNum = prm.RevisionNum
select * from @TableBomPartandRev
where PullAsAsm=1 and PartMtl='TC0000000676'
---------level 1:
select pmtl.Company, pmtl.PartNum, pmtl.RevisionNum, bom.RevisionNum, pmtl.MtlPartNum, pmtl.ParentMtlSeq, MtlSeq,pmtl.PullAsAsm, bom.DateMaxChangOn
from PartMtl as pmtl inner join @TableBomPartandRev as bom on pmtl.Company=bom.Company and pmtl.PartNum= bom.PartMtl and pmtl.RevisionNum=bom.RevisionNum and
bom.PullAsAsm=1 and PartMtl in ('TC0000000676')
<PartRevCostsDetail>
<PartNum>EG0000000962</PartNum>
<RevisionNum>T3</RevisionNum>
<Quantity>1</Quantity>
<Approved>true</Approved>
<BOMType>Asm</BOMType>
<BOMLevel>1</BOMLevel>
<MtlRevision>M3</MtlRevision>
<MtlPartNum>.TC0000000676</MtlPartNum>
<MaterialCost>0.00000</MaterialCost>
<LaborCost>12.76275</LaborCost>
<BurdenCost>4.95416</BurdenCost>
<SubcontractCost>0.00000</SubcontractCost>
<MaterialBurdenCost>0.00000</MaterialBurdenCost>
<TotalCost>17.71691</TotalCost>
<MaterialUnitCost>0</MaterialUnitCost>
<LaborUnitCost>89.25000</LaborUnitCost>
<BurdenUnitCost>34.64450</BurdenUnitCost>
<SubcontractUnitCost>0</SubcontractUnitCost>
<MaterialBurdenUnitCost>0</MaterialBurdenUnitCost>
<TotalUnitCost>123.89450</TotalUnitCost>
<QtyPer>0.14300000</QtyPer>
<RequiredQty>0.14300000</RequiredQty>
<PartDescription>Nhựa ABS 12AB8-0085 Black trộn</PartDescription>
<EffectiveDate>2022-07-04T11:10:32.5106295+07:00</EffectiveDate>
<BomSequence>1</BomSequence>
<Company>01</Company>
<AltMethod/>
<SysRowID>fc0bbbc1-e485-42f0-8e65-af4abf4e92cf</SysRowID>
<RowMod>A</RowMod>
here partNum EG0000000962 uses RevisionNum as T3 but MtlPartNum TC0000000676 uses MtlRevision as M3? How can I get information from any table?
ViewCost.xml (20.8 KB)
Thank you very much have a nice day! 
