This code was created to show how the data connection between BOM_DET, Inventor, Invtmfhd, and AntiAvl tables.
*This code is not an "end product", it was not tested nor it is optimized and might take substantial time to run on the large scale data. The user may use this code for the reference. If they choose to use it as the "ready to use" code they should do so at their own risk.
*-- IA = Inventor (for assembly)
*-- IC = Inventor (for internal components)
*-- I2 = Inventor (for consigned components)
*--enter your assy # and revision here
lcPart_no='910-1004 '
lcRevision='A '
*-- This gathers the consigned part numbers and AVL for the BOM
SELECT Bom_det.Bomparent,Bom_det.uniq_key,Bom_det.qty,IA.BomCustno as AssemblyCust,;
IA.part_no as AssemblyNo,IA.Revision as AssemblyRev,Bom_det.item_no,;
IC.Part_sourc,IC.Part_no,IC.Revision ,I2.uniq_key as ConsgUniq, ;
Invtmfhd.PartMfgr,Invtmfhd.Mfgr_pt_no;
FROM Bom_det,Inventor IA, Inventor IC,Inventor I2 ,Invtmfhd;
WHERE Bom_det.Bomparent=IA.Uniq_key ;
AND Bom_Det.Uniq_key=IC.Uniq_key ;
AND Invtmfhd.Uniq_key=I2.uniq_key;
AND IA.Part_no= lcPart_no;
AND IA.Revision=lcrevision;
AND I2.Custno=IA.BomCustNo;
AND I2.Int_uniq=IC.Uniq_key;
AND I2.Part_sourc='CONSG';
AND BomParent+I2.uniq_key+Invtmfhd.PartMfgr+Invtmfhd.Mfgr_pt_no NOT IN (SELECT Bomparent+Uniq_key+PartMfgr+Mfgr_pt_no FROM AntiAvl) ;
UNION ;*-- This gathers internal part numbers and AVL for the BOM
SELECT Bom_det.Bomparent,Bom_det.uniq_key,Bom_det.qty,IA.BomCustno as AssemblyCust,;
IA.part_no as AssemblyNo,IA.Revision as AssemblyRev,Bom_det.item_no,;
IC.Part_sourc,IC.Part_no,IC.Revision ,SPACE(10) as ConsgUniq ,;
Invtmfhd.PartMfgr,Invtmfhd.Mfgr_pt_no;
FROM Bom_det,Inventor IA, Inventor IC ,Invtmfhd;
WHERE Bom_det.Bomparent=IA.Uniq_key ;
AND Bom_Det.Uniq_key=IC.Uniq_key ;
AND Invtmfhd.Uniq_key=IC.uniq_key;
AND IA.Part_no= lcPart_no;
AND IA.Revision=lcrevision;
AND Ic.Uniq_key NOT IN (SELECT Int_uniq FROM Inventor WHERE part_sourc='CONSG');
AND BomParent+IC.uniq_key+Invtmfhd.PartMfgr+Invtmfhd.Mfgr_pt_no NOT IN (SELECT Bomparent+Uniq_key+PartMfgr+Mfgr_pt_no FROM AntiAvl) ;
ORDER BY 7;
INTO CURSOR ZBom
|
