Att_(13)_BOM_Template_(CDRL_LR0B)_w_Macros_rev_3_1.10.2014_0221.xlsx

BOM Template CPN NHA QTY FIND# Ref Des CPN CAGE CPN MFR CPN Nomenclature CPN NIIN CPN FSC CPN COG Part Type Monitoring Type VPN VPN CAGE VPN Mfr. VPN Nomenclature VPN NIIN VPN FSC VPN COG DIST_CODE

24 Aug 2015 LR0B ATTACHMENT 13 BOM Template

RFP: N00019-16-R-3001

PID: N00019-15-P1-PMA-299-0097

Column Definitions

1.) The first row (after the header) in the "BOM Template" worksheet needs to have the Highest NHA P/N in the CPN column with the name of the platform it is going under in OMIS in the NHA column.

2.) In order for the EBOM to be loaded into OMIS the file name of the EBOM Template has to end with the following "BOM_Template" so any modifications to the EBOM file name needs to be done at the beginning with "BOM_Template" showing up at the end.

Part Type Idents

Monitoring Type - Load Rules

Check BOM Macro Help image1.png

Microsoft_Word_97_-_2003_Document1.doc Error Color Codes

A unique color is given for each error as shown in the color code table below:

Color

Macro Function/ Description

Hot Key

N/A

Trim Columns to remove excess pre-pended or appended unprintable characters

Ctrl + q

RED

Part number scan for matches to Next Higher Assembly

Ctrl + w

TAN

Check for existence of Next_Assy in Part_Num

Ctrl + e

YELLOW

Checks Cage Codes for 5 characters and replace leading O with zero

Ctrl + r

YELLOW

Check Vendor cages where Vendor Part_Num is present

Ctrl + s

Magenta

Validates that the Part Types match the types on Tab 3

Ctrl + t

N/A

Performs Text to Column operations on Columns A-M

Ctrl + y

Multiple

Run Check BOM to run all of the above checks

Ctrl + p image2.emf

Microsoft_Word_97_-_2003_Document2.doc Check BOM Macro Help

In the BOM template, a library of macros is included to perform some common checks automatically.

To use the macro follow these 2 steps:

Step 1: Paste your data into the BOM Template

Note: Do not change the order of the columns or worksheet tabs in the template.

(The macro relies on the template’s format for correct operation)

Step 2:

Run the Macro, select yes to continue and review errors found

· The macro can be run with a hotkey Ctrl + p

· The macro can also be activated through the Developers Ribbon:

· Developers ( Macro ( Select CheckBOM (or any other Macro) and click Run

CheckBOM will look for the following errors and highlight them for your review:

- Remove_Bad_Characters: Removes Invalid Characters from all Cells (+"`') Ctrl Shift + n

- RemoveOutOfRangeCharacters: Removes unprintable ASCII character Ctrl Shift + m

- Auto_Filter_Off: Turns off Auto Filter Ctrl Shift + z

- TrimColumns: Trim Columns to remove excess pre-pended or appended unprintable characters Ctrl Shift + q

- LeftJustify_VerticalCenter: Left Justifies and Vertically Centers all Cells Ctrl Shift + s

- ScanPartNum: Scan for instances of identical Part_number and Next_Assy Ctrl Shift + l

- Next_Higher_Exists: Check that Next_Assy is present in Part_Num Ctrl Shift + o

- CheckCageCodes: Checks Cage Code formatting and for blanks Ctrl Shift + r

- ValidatePartType: Validates that the Part Types match the types on Tab 3 (Part Type Indents) Ctrl Shift + k

- CheckColumn: Checks the columns for size and contents Ctrl Shift + j

- CpnOnlyOneCage: Checks for different CAGE codes for the same CPN Ctrl Shift + v

- CpnNhaCombinationDiffQty: Checks for different QTY for same CPN/NHA combination Ctrl Shift + w

Note: The macros above can be done individually and each macro has a unique hot key but CheckBom will perform all of these checks in the order outlined above.

These macros do not run with the CheckBom macro

- getCpnCageDescriptionFromAccess: Fills in Company name for CPN CAGE Ctrl Shift + u

- getVpnCageDescriptionFromAccess: Fills in Company name for VPN Cage and highlights potential non-manufacturers Ctrl Shift + t image3.emf