Dynamics GP – Manufacturing BOM – Export

Dynamics GP – Manufacturing BOM – Export

Users of manufacturing BOM wanted a easy way to have all the manufacturing BOM’s exported to excel where the cost from child part gets rolled up.  They also wanted to identify separately the cost components.  All the export should retain BOM Tree structure.

Before attempting to reinvent the wheel, it is worth while to check the SSRS BOM reports which is released with Dynamics GP SSRS package.

Implemented a series of views at the company database.  The later views are dependent on former views. Assumed the maximum level of children indents would be 10.

  1. Create Level 1 view
  2. Create Level 2 view
  3. Create Level 3 view
  4. Create Level 4 view
  5. Create Level 5 view
  6. Create Level 6 view
  7. Create Level 7 view
  8. Create Level 8 view
  9. Create Level 9 view
  10. Create Level 10 view
  11. Create Level Consolidation View
  12. Create Routing view
  13. Create BOM Material Export View
  14. Create BOM Machine Export View
  15. Create BOM Labor Export View.

The last three views can be  used in a SSRS Report to export cute BOM Reports.

Sample SSRS Reports

Continue reading

Dynamics GP BOM Export – Part 15 – Labor

Dynamics GP BOM Export  – Part 15 – Labor

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM_Labor]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM_Labour]
AS
SELECT TOP (100) PERCENT dbo.bom.item0                                     AS
ITEM,
dbo.bom.item10                                    AS
COMPONENT,
dbo.bom.[level],
dbo.bom.itemdesc,
dbo.bom.q0 * dbo.bom.q1 * dbo.bom.q2 * dbo.bom.q3 *
dbo.bom.q4 * dbo.bom.q5 *
dbo.bom.q6 * dbo.bom.q7 * dbo.bom.q8 * dbo.bom.q9 AS
Quantity,
Cast(dbo.bom.zero AS NUMERIC)                     AS
one,
Cast(dbo.bom.one AS NUMERIC)                      AS
two,
Cast(dbo.bom.two AS NUMERIC)                      AS
three,
Cast(dbo.bom.three AS NUMERIC)                    AS
four,
Cast(dbo.bom.four AS NUMERIC)                     AS
five,
Cast(dbo.bom.five AS NUMERIC)                     AS
six,
Cast(dbo.bom.six AS NUMERIC)                      AS
seven,
Cast(dbo.bom.seven AS NUMERIC)                    AS
eight,
Cast(dbo.bom.eight AS NUMERIC)                    AS
nine,
Cast(dbo.bom.nine AS NUMERIC)                     AS
ten,
dbo.bom_routing.routingname_i                     AS
ROUTINGNAME,
Isnull(dbo.rt010130.laborcode_i, )              AS
SETUP_CODE,
Isnull(dbo.rt010130.setuptime_i, 0)               AS
SETUP_TIME,
Isnull(LC010014_1.cost_i, 0)                      AS
SETUP_SHOP_RATE,
Isnull(LC010014_1.hourpiece1_i, 0)                AS
SETUP_HOUR_OR_PIECE,
Isnull(LC010014_1.fixamtorpct_i, 0)               AS
SETUP_FOH_AMT_OR_PCT,
Isnull(LC010014_1.fixed_overhead_amount, 0)       AS
SETUP_FOH_AMT,
Isnull(LC010014_1.fixohdpct_i, 0)                 AS
SETUP_FOH_PCT,
Isnull(LC010014_1.varamtorpct_i, 0)               AS
Setup_VOH_AMT_or_PCT,
Isnull(LC010014_1.variable_overhead_amount, 0)    AS
Setup_VOH_AMT,
Isnull(LC010014_1.varohdpct_i, 0)                 AS
Setup_VOH_PCT,
Isnull(dbo.rt010130.laborcode2_i, )             AS
LABOUR_CODE,
Isnull(dbo.rt010130.labortime_i, 0)               AS
LABOUR_TIME,
Isnull(dbo.lc010014.cost_i, 0)                    AS
LABOUR_RATE,
Isnull(dbo.lc010014.hourpiece1_i, 0)              AS
LABOR_HOUR_OR_PIECE_RATE,
Isnull(dbo.lc010014.fixamtorpct_i, 0)             AS
LABOR_FOH_AMT_OR_PCT,
Isnull(dbo.lc010014.fixed_overhead_amount, 0)     AS
LABOR_FOH_AMT,
Isnull(dbo.lc010014.fixohdpct_i, 0)               AS
LABOR_FOH_PCT,
Isnull(dbo.lc010014.varamtorpct_i, 0)             AS
LABOR_VOH_AMT_OR_PCT,
Isnull(dbo.lc010014.variable_overhead_amount, 0)  AS
LABOR_VOH_AMT,
Isnull(dbo.lc010014.varohdpct_i, 0)               AS
LABOR_VOH_PCT,
dbo.ivr10015.averageordqty_i                      AS
AVGLOTSIZE
FROM   dbo.rt010130
INNER JOIN dbo.bom_routing
ON dbo.rt010130.routingname_i = dbo.bom_routing.routingname_i
AND dbo.rt010130.itemnmbr = dbo.bom_routing.itemnmbr
LEFT OUTER JOIN dbo.lc010014
ON dbo.rt010130.laborcode2_i = dbo.lc010014.laborcode_i
LEFT OUTER JOIN dbo.lc010014 AS LC010014_1
ON dbo.rt010130.laborcode_i = LC010014_1.laborcode_i
RIGHT OUTER JOIN dbo.ivr10015
INNER JOIN dbo.bom
ON dbo.ivr10015.itemnmbr = dbo.bom.item10
ON dbo.bom_routing.itemnmbr = dbo.bom.item10
ORDER  BY item,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
ten  

Dynamics GP BOM Export – Part 14 – Machine

Dynamics GP BOM Export  – Part 14 – Machine

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM_Machine]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM_Machine]
AS
SELECT TOP (100) PERCENT dbo.bom.item0                                     AS
ITEM,
dbo.bom.item10                                    AS
COMPONENT,
dbo.bom.[level],
dbo.bom.itemdesc,
dbo.bom.q0 * dbo.bom.q1 * dbo.bom.q2 * dbo.bom.q3 *
dbo.bom.q4 * dbo.bom.q5 *
dbo.bom.q6 * dbo.bom.q7 * dbo.bom.q8 * dbo.bom.q9 AS
QTY,
Cast(dbo.bom.zero AS NUMERIC)                     AS
one,
Cast(dbo.bom.one AS NUMERIC)                      AS
two,
Cast(dbo.bom.two AS NUMERIC)                      AS
three,
Cast(dbo.bom.three AS NUMERIC)                    AS
four,
Cast(dbo.bom.four AS NUMERIC)                     AS
five,
Cast(dbo.bom.five AS NUMERIC)                     AS
six,
Cast(dbo.bom.six AS NUMERIC)                      AS
seven,
Cast(dbo.bom.seven AS NUMERIC)                    AS
eight,
Cast(dbo.bom.eight AS NUMERIC)                    AS
nine,
Cast(dbo.bom.nine AS NUMERIC)                     AS
ten,
dbo.bom_routing.routingname_i                     AS
ROUTINGNAME,
dbo.rt010130.machineid_i                          AS
MACHINE_ID,
dbo.rt010130.machinetime_i                        AS
MACHINE_TIME,
dbo.mm010032.cost_i                               AS
MACHINE_RATE,
dbo.mm010032.fixhrpc_i                            AS
MACHINE_FOH_HOUR_OR_PIECE,
dbo.mm010032.costperpiece_i                       AS
MACHINE_COST_PER_PIECE,
dbo.mm010032.fixamtorpct_i                        AS
MACHINE_FOH_AMOUNT_OR_PCT,
dbo.mm010032.fixed_overhead_amount                AS
MACHINE_FOH_AMOUNT,
dbo.mm010032.fixohdpct_i                          AS
MACHINE_FOH_PCT,
dbo.mm010032.varamtorpct_i                        AS
MACHINE_VOH_AMT_OR_PCT,
dbo.mm010032.variable_overhead_amount             AS
MACHINE_VOH_AMT,
dbo.mm010032.varohdpct_i                          AS
MACHINE_VOH_PCT,
dbo.mm010032.varhrpc_i                            AS
MACHINE_VOH_HOUR_OR_PC,
dbo.ivr10015.averageordqty_i,
dbo.rt010130.numberofmachines_i,
dbo.ivr10015.itemstatus_i,
dbo.ivr10015.bomtype_i,
dbo.ivr10015.makebuycode_i
FROM   dbo.ivr10015
INNER JOIN dbo.bom
ON dbo.ivr10015.itemnmbr = dbo.bom.item10
LEFT OUTER JOIN dbo.rt010130
LEFT OUTER JOIN dbo.mm010032
ON dbo.rt010130.machineid_i =
dbo.mm010032.machineid_i
RIGHT OUTER JOIN dbo.bom_routing
ON dbo.rt010130.routingname_i =
dbo.bom_routing.routingname_i
AND dbo.rt010130.itemnmbr =
dbo.bom_routing.itemnmbr
ON dbo.bom.item10 = dbo.bom_routing.itemnmbr
ORDER  BY item,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
ten  

Dynamics GP BOM Export – Part 13 – Material

Dynamics GP BOM Export  – Part 13 – Material

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM_Material]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM_Material]
AS
SELECT TOP (100) PERCENT dbo.bom.item0                                     AS
ITEM,
dbo.bom.item10                                    AS
COMPONENT,
dbo.bom.[level],
dbo.bom.itemdesc                                  AS
[Item Description],
CASE dbo.ivr10015.makebuycode_i
WHEN 1 THEN ‘MAKE’
WHEN 3 THEN ‘BUY’
WHEN 2 THEN ‘MAKEBUY’
ELSE ‘INVALID’
END                                               AS
MAKEBUYTYPE,
dbo.bom.q0 * dbo.bom.q1 * dbo.bom.q2 * dbo.bom.q3 *
dbo.bom.q4 * dbo.bom.q5 *
dbo.bom.q6 * dbo.bom.q7 * dbo.bom.q8 * dbo.bom.q9 AS
Quantity,
dbo.ct00003.cost_i                                AS
[Std Cost Per Unit],
dbo.ct00003.variable_overhead_amount              AS
[Variable OH Per Unit],
Cast(dbo.bom.zero AS NUMERIC)                     AS
one,
Cast(dbo.bom.one AS NUMERIC)                      AS
two,
Cast(dbo.bom.two AS NUMERIC)                      AS
three,
Cast(dbo.bom.three AS NUMERIC)                    AS
four,
Cast(dbo.bom.four AS NUMERIC)                     AS
five,
Cast(dbo.bom.five AS NUMERIC)                     AS
six,
Cast(dbo.bom.six AS NUMERIC)                      AS
seven,
Cast(dbo.bom.seven AS NUMERIC)                    AS
eight,
Cast(dbo.bom.eight AS NUMERIC)                    AS
nine,
Cast(dbo.bom.nine AS NUMERIC)                     AS
ten,
Isnull ((SELECT routingname_i
FROM   dbo.rt010001
WHERE  ( itemnmbr = dbo.bom.item10 )
AND ( rtprimary_i = 1 )), )     AS
ROUTINGNAME_I,
dbo.iv00101.uomschdl
FROM   dbo.iv00101
INNER JOIN dbo.bom
ON dbo.iv00101.itemnmbr = dbo.bom.item10
LEFT OUTER JOIN dbo.ivr10015
INNER JOIN dbo.ct00003
ON dbo.ivr10015.itemnmbr = dbo.ct00003.itemnmbr
ON dbo.bom.item10 = dbo.ct00003.itemnmbr
ORDER  BY item,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
ten  

Dynamics GP BOM Export – Part 12 – Routing

Dynamics GP BOM Export  – Part 12 – Routing

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM_Routing]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM_Routing]
AS

SELECT routingname_i,
itemnmbr,
rtprimary_i
FROM   dbo.rt010001
WHERE  ( rtprimary_i = 1 )

Dynamics GP BOM Export – Part 11

Dynamics GP BOM Export  – Part 11

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM]
AS

SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom0
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom1
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom2
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom3
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom4
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom5
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom6
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom7
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom8
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom9
UNION ALL
SELECT item0,
item1,
item2,
item3,
item4,
item5,
item6,
item7,
item8,
item9,
item10,
bomcat_i,
bomname_i,
[level],
effectiveindate_i,
bomtype_i,
stndcost,
backflushitem_i,
locncode,
position_number,
itemdesc,
quantity_i,
zero,
one,
two,
three,
four,
five,
six,
seven,
eight,
nine,
q0,
q1,
q2,
q3,
q4,
q5,
q6,
q7,
q8,
q9
FROM   dbo.bom10  

Dynamics GP BOM Export – Part 10

Dynamics GP BOM Export  – Part 10

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM10]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM10]
AS
SELECT TOP (100) PERCENT dbo.bom9.item0,
dbo.bom9.item1,
dbo.bom9.item2,
dbo.bom9.item3,
dbo.bom9.item4,
dbo.bom9.item5,
dbo.bom9.item6,
dbo.bom9.item7,
dbo.bom9.item8,
dbo.bom9.item9,
dbo.bm010115.cpn_i AS ITEM10,
dbo.bm010115.bomcat_i,
dbo.bm010115.bomname_i,
10                 AS [LEVEL],
dbo.bm010115.effectiveindate_i,
dbo.bm010115.bomtype_i,
dbo.iv00101.stndcost,
dbo.bm010115.backflushitem_i,
dbo.bm010115.locncode,
dbo.bm010115.position_number,
dbo.iv00101.itemdesc,
dbo.bm010115.quantity_i,
dbo.bm010115.uofm,
dbo.bom9.zero,
dbo.bom9.one,
dbo.bom9.two,
dbo.bom9.three,
dbo.bom9.four,
dbo.bom9.five,
dbo.bom9.six,
dbo.bom9.seven,
dbo.bom9.eight,
dbo.bom9.nine,
dbo.bom9.q0,
dbo.bom9.q1,
dbo.bom9.q2,
dbo.bom9.q3,
dbo.bom9.q4,
dbo.bom9.q5,
dbo.bom9.q6,
dbo.bom9.q7,
dbo.bom9.q8,
dbo.bom9.q9
FROM   dbo.bom9
INNER JOIN dbo.bm010115
ON dbo.bom9.bomname_i = dbo.bm010115.bomname_i
AND dbo.bom9.bomcat_i = dbo.bm010115.bomcat_i
AND dbo.bom9.item9 = dbo.bm010115.ppn_i
INNER JOIN dbo.iv00101
ON dbo.bm010115.cpn_i = dbo.iv00101.itemnmbr
ORDER  BY dbo.bm010115.position_number  

Dynamics GP BOM Export – Part 9

Dynamics GP BOM Export  – Part 9

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM9]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM9]
AS
SELECT TOP (100) PERCENT dbo.bom8.item0,
dbo.bom8.item1,
dbo.bom8.item2,
dbo.bom8.item3,
dbo.bom8.item4,
dbo.bom8.item5,
dbo.bom8.item6,
dbo.bom8.item7,
dbo.bom8.item8,
dbo.bm010115.cpn_i           AS ITEM9,
dbo.bm010115.cpn_i           AS ITEM10,
dbo.bm010115.bomcat_i,
dbo.bm010115.bomname_i,
9                            AS [LEVEL],
dbo.bm010115.effectiveindate_i,
dbo.bm010115.bomtype_i,
dbo.iv00101.stndcost,
dbo.bm010115.backflushitem_i,
dbo.bm010115.locncode,
dbo.bm010115.position_number,
dbo.iv00101.itemdesc,
dbo.bm010115.quantity_i,
dbo.bm010115.uofm,
dbo.bom8.zero,
dbo.bom8.one,
dbo.bom8.two,
dbo.bom8.three,
dbo.bom8.four,
dbo.bom8.five,
dbo.bom8.six,
dbo.bom8.seven,
dbo.bom8.eight,
dbo.bm010115.position_number AS nine,
dbo.bom8.q0,
dbo.bom8.q1,
dbo.bom8.q2,
dbo.bom8.q3,
dbo.bom8.q4,
dbo.bom8.q5,
dbo.bom8.q6,
dbo.bom8.q7,
dbo.bom8.q8,
dbo.bm010115.quantity_i      AS Q9
FROM   dbo.bom8
INNER JOIN dbo.bm010115
ON dbo.bom8.bomname_i = dbo.bm010115.bomname_i
AND dbo.bom8.bomcat_i = dbo.bm010115.bomcat_i
AND dbo.bom8.item8 = dbo.bm010115.ppn_i
INNER JOIN dbo.iv00101
ON dbo.bm010115.cpn_i = dbo.iv00101.itemnmbr
ORDER  BY dbo.bm010115.position_number  

Dynamics GP BOM Export – Part 8

Dynamics GP BOM Export  – Part 8
USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM8]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM8]
AS
SELECT TOP (100) PERCENT dbo.bom7.item0,
dbo.bom7.item1,
dbo.bom7.item2,
dbo.bom7.item3,
dbo.bom7.item4,
dbo.bom7.item5,
dbo.bom7.item6,
dbo.bom7.item7,
dbo.bm010115.cpn_i           AS ITEM8,
dbo.bm010115.cpn_i           AS ITEM9,
dbo.bm010115.cpn_i           AS ITEM10,
dbo.bm010115.bomcat_i,
dbo.bm010115.bomname_i,
8                            AS [LEVEL],
dbo.bm010115.effectiveindate_i,
dbo.bm010115.bomtype_i,
dbo.iv00101.stndcost,
dbo.bm010115.backflushitem_i,
dbo.bm010115.locncode,
dbo.bm010115.position_number,
dbo.iv00101.itemdesc,
dbo.bm010115.quantity_i,
dbo.bm010115.uofm,
dbo.bom7.zero,
dbo.bom7.one,
dbo.bom7.two,
dbo.bom7.three,
dbo.bom7.four,
dbo.bom7.five,
dbo.bom7.six,
dbo.bom7.seven,
dbo.bm010115.position_number AS eight,
dbo.bom7.nine,
dbo.bom7.q0,
dbo.bom7.q1,
dbo.bom7.q2,
dbo.bom7.q3,
dbo.bom7.q4,
dbo.bom7.q5,
dbo.bom7.q6,
dbo.bom7.q7,
dbo.bm010115.quantity_i      AS Q8,
dbo.bom7.q9
FROM   dbo.bom7
INNER JOIN dbo.bm010115
ON dbo.bom7.bomname_i = dbo.bm010115.bomname_i
AND dbo.bom7.bomcat_i = dbo.bm010115.bomcat_i
AND dbo.bom7.item7 = dbo.bm010115.ppn_i
INNER JOIN dbo.iv00101
ON dbo.bm010115.cpn_i = dbo.iv00101.itemnmbr
ORDER  BY dbo.bm010115.position_number  

 

 

Dynamics GP BOM Export – Part 7

Dynamics GP BOM Export  – Part 7

USE [COMPANYDB]
go
/****** Object:  View [dbo].[BOM7]    Script Date: 09/04/2013 19:37:00 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BOM7]
AS
SELECT TOP (100) PERCENT dbo.bom6.item0,
dbo.bom6.item1,
dbo.bom6.item2,
dbo.bom6.item3,
dbo.bom6.item4,
dbo.bom6.item5,
dbo.bom6.item6,
dbo.bm010115.cpn_i           AS ITEM7,
dbo.bm010115.cpn_i           AS ITEM8,
dbo.bm010115.cpn_i           AS ITEM9,
dbo.bm010115.cpn_i           AS ITEM10,
dbo.bm010115.bomcat_i,
dbo.bm010115.bomname_i,
7                            AS [LEVEL],
dbo.bm010115.effectiveindate_i,
dbo.bm010115.bomtype_i,
dbo.iv00101.stndcost,
dbo.bm010115.backflushitem_i,
dbo.bm010115.locncode,
dbo.bm010115.position_number,
dbo.iv00101.itemdesc,
dbo.bm010115.quantity_i,
dbo.bm010115.uofm,
dbo.bom6.zero,
dbo.bom6.one,
dbo.bom6.two,
dbo.bom6.three,
dbo.bom6.four,
dbo.bom6.five,
dbo.bom6.six,
dbo.bm010115.position_number AS seven,
dbo.bom6.eight,
dbo.bom6.nine,
dbo.bom6.q0,
dbo.bom6.q1,
dbo.bom6.q2,
dbo.bom6.q3,
dbo.bom6.q4,
dbo.bom6.q5,
dbo.bom6.q6,
dbo.bm010115.quantity_i      AS Q7,
dbo.bom6.q8,
dbo.bom6.q9
FROM   dbo.bom6
INNER JOIN dbo.bm010115
ON dbo.bom6.bomname_i = dbo.bm010115.bomname_i
AND dbo.bom6.bomcat_i = dbo.bm010115.bomcat_i
AND dbo.bom6.item6 = dbo.bm010115.ppn_i
INNER JOIN dbo.iv00101
ON dbo.bm010115.cpn_i = dbo.iv00101.itemnmbr
ORDER  BY dbo.bm010115.position_number