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