-------------------------------------------------------------------------
--All Manufacturing G/L Entries
---------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_GL_Trx] AS SELECT gl.trx_status, gl.trxdate AS trx_date, gl.jrnentry AS journal_entry, Rtrim(gm.actnumst) AS account_number, Rtrim(ga.actdescr) AS account_description, gl.debitamt AS debit_amount, gl.crdtamnt AS credit_amount, gl.refrence AS reference, gl.sourcdoc AS source_document, gl.ortrxsrc AS originating_trx_source, gl.ormstrid AS originating_master_id, gl.ormstrnm AS originating_master_name, gl.ordocnum AS originating_doc_number, gl.curncyid AS currency_id, gl.lastuser AS last_user, gl.uswhpstd AS user_who_posted, CASE WHEN sourcdoc = 'mfgadj' THEN jrnentry + 1 ELSE jrnentry END AS rctjrn FROM ( SELECT actindx, trxdate, sourcdoc, jrnentry, ortrxsrc, refrence, ordocnum, ormstrid, ormstrnm, debitamt, crdtamnt, curncyid, 'Open' AS trx_status, lastuser, uswhpstd FROM dbo.gl20000 WHERE ( sourcdoc NOT IN ('BBF', 'P/L')) AND ( voided = 0) UNION ALL SELECT actindx, trxdate, sourcdoc, jrnentry, ortrxsrc, refrence, ordocnum, ormstrid, ormstrnm, debitamt, crdtamnt, curncyid, 'History' AS trx_status, lastuser, uswhpstd FROM dbo.gl30000 WHERE ( sourcdoc NOT IN ('BBF', 'P/L')) AND ( voided = 0) UNION ALL SELECT gd.actindx, gh.trxdate, gh.sourcdoc, gh.jrnentry, gh.ortrxsrc, gh.refrence, gd.ordocnum, gd.ormstrid, gd.ormstrnm, gd.debitamt, gd.crdtamnt, gh.curncyid, 'Work' AS trx_status, gh.lastuser, gh.uswhpstd FROM dbo.gl10000 AS gh INNER JOIN dbo.gl10001 AS gd ON gh.jrnentry = gd.jrnentry WHERE ( gh.voided = 0)) AS gl INNER JOIN dbo.gl00105 AS gm ON gl.actindx = gm.actindx INNER JOIN dbo.gl00100 AS ga ON gl.actindx = ga.actindx
go
----------------------------------------------------------------------------------
--All Manufacturing Receipt Journals
------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Jrn_Rct_Index]
AS
SELECT dbo.bi_gl_trx.journal_entry,
dbo.bi_gl_trx.originating_doc_number,
derivedtbl_1.moprctnm
FROM dbo.bi_gl_trx
INNER JOIN (SELECT DISTINCT ivdocnbr,
moprctnm
FROM dbo.ivic0101 AS IVIC0101_1) AS derivedtbl_1
ON dbo.bi_gl_trx.originating_doc_number = derivedtbl_1.ivdocnbr
WHERE ( dbo.bi_gl_trx.source_document = 'ivadj' )
GROUP BY dbo.bi_gl_trx.journal_entry,
dbo.bi_gl_trx.originating_doc_number,
derivedtbl_1.moprctnm
go
-----------------------------------------------------------------------------
--All Manufacturing Inventory Transactions
-----------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Mfg_InvTrx]
AS
SELECT dbo.IV30200.SRCRFRNCNMBR, dbo.IV30200.TRXSORCE, dbo.IV30200.BCHSOURC, dbo.IV30200.BACHNUMB, dbo.IV30200.DOCDATE, dbo.IV30200.GLPOSTDT, dbo.IV30300.ITEMNMBR, dbo.IV00101.ITEMDESC,
dbo.IV30300.UOFM, dbo.IV30300.TRXQTY, dbo.IV30300.UNITCOST, dbo.IV30300.EXTDCOST, dbo.IV30300.TRXLOCTN, dbo.GL00105.ACTNUMST AS IVActNum, dbo.GL00100.ACTDESCR AS IVActDesc,
GL00105_1.ACTNUMST AS IVOfstNum, GL00100_1.ACTDESCR AS IVOfstDesc, LEFT(dbo.IV00101.ITEMNMBR, 1) AS ItemType
FROM dbo.GL00100 AS GL00100_1 INNER JOIN
dbo.GL00105 AS GL00105_1 INNER JOIN
dbo.IV00101 INNER JOIN
dbo.IV30200 INNER JOIN
dbo.IV30300 ON dbo.IV30200.TRXSORCE = dbo.IV30300.TRXSORCE AND dbo.IV30200.IVDOCTYP = dbo.IV30300.DOCTYPE AND dbo.IV30200.DOCNUMBR = dbo.IV30300.DOCNUMBR ON
dbo.IV00101.ITEMNMBR = dbo.IV30300.ITEMNMBR ON GL00105_1.ACTINDX = dbo.IV30300.IVIVINDX ON GL00100_1.ACTINDX = GL00105_1.ACTINDX INNER JOIN
dbo.GL00105 ON dbo.IV30300.IVIVOFIX = dbo.GL00105.ACTINDX INNER JOIN
dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX
WHERE (dbo.IV30200.SRCRFRNCNMBR > '')
GO
-----------------------------------------------------------------------------
--All Manufacturing BOMS and Components
-----------------------------------------------------------------------------
/****** Object: View [dbo].[BI_Mfg_BOM] Script Date: 7/31/2017 2:37:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM]
AS
SELECT TOP (100) PERCENT dbo.IV00101.ITEMNMBR AS BomNumber, dbo.IV00101.ITEMDESC AS BOMName, IV00101_1.ITEMNMBR AS CmpNumber, IV00101_1.ITEMDESC AS CmpName,
dbo.BM010115.QUANTITY_I AS CmpQty, dbo.BM010115.UOFM AS CmpUofM, dbo.BM010415.LOCNCODE, dbo.IV40700.STATE, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + dbo.IV00101.ITEMDESC AS BomLookup,
dbo.IV00101.ITMCLSCD, dbo.IV40400.ITMCLSDC, dbo.IVR10015.ITEMSTATUS_I, dbo.IVR10015.MAKEBUYCODE_I, dbo.IVR10015.ITEMFULFILLMETHOD,
CASE makebuycode_i WHEN 1 THEN 'Make' WHEN 2 THEN 'Buy' WHEN 3 THEN 'Make/Buy' END AS MakeBuy,
CASE itemfulfillmethod WHEN 1 THEN 'Make to Stock' WHEN 2 THEN 'Make to Order-Manual' WHEN 3 THEN 'Make to Order-Silent' END AS FulfillMethod, dbo.MN010000.NOTETEXT_I AS BomNote,
IV00101_1.CURRCOST AS CmpCurrCost, dbo.BM010415.BOMTYPE_I, CASE itemstatus_i WHEN 1 THEN 'Active' ELSE 'Inactive' END AS ItemEngStatus,
CASE WHEN iv00101.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMStatus, dbo.BM010415.EFFECTIVEDATE_I AS BOMInDate, CASE WHEN BM010415.EFFECTIVEDATE_I < getdate()
THEN 'Active' WHEN BM010415.EFFECTIVEDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS BOMDateStatus, CASE WHEN iv00101_1.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMCmpStatus,
dbo.BM010115.EFFECTIVEINDATE_I AS CmpInDate, dbo.BM010115.EFFECTIVEOUTDATE_I AS CmpOutDate, CASE WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I >= getdate()
THEN 'Active' WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS CmpStatus
FROM dbo.MN010000 RIGHT OUTER JOIN
dbo.IVR10015 ON dbo.MN010000.NOTEINDX = dbo.IVR10015.MFGNOTEINDEX_I RIGHT OUTER JOIN
dbo.BM010415 INNER JOIN
dbo.BM010115 ON dbo.BM010415.BOMCAT_I = dbo.BM010115.BOMCAT_I AND dbo.BM010415.ITEMNMBR = dbo.BM010115.PPN_I INNER JOIN
dbo.IV00101 ON dbo.BM010415.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
dbo.IV00101 AS IV00101_1 ON dbo.BM010115.CPN_I = IV00101_1.ITEMNMBR INNER JOIN
dbo.IV40700 ON dbo.BM010415.LOCNCODE = dbo.IV40700.LOCNCODE INNER JOIN
dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD ON dbo.IVR10015.ITEMNMBR = dbo.BM010415.ITEMNMBR
WHERE (dbo.BM010115.EFFECTIVEOUTDATE_I > GETDATE()) OR
(dbo.BM010115.EFFECTIVEOUTDATE_I = 0)
GO
/*----------------------------------------------------------------------------------------
All Manufacturing Receipts and Components
---------------------------------------------------------------------------------------*/
CREATE VIEW [dbo].[BI_Mfg_Rcts_Cmp]
AS
SELECT TOP (100) PERCENT dbo.bi_mfgbom.bomnumber,
dbo.bi_mfgbom.bomname,
dbo.mop1110.moprctnm,
dbo.mop1110.itemnmbr,
dbo.iv00101.itemdesc,
Isnull(dbo.bi_mfgbom.cmpuofm, dbo.bi_mfg_invtrx.uofm)
AS CmpUofM,
Isnull(dbo.bi_mfgbom.cmpqty, 0)
AS BomQty,
CASE
WHEN cmpuofm = 'DOL' THEN Isnull(cmpqty, 0)
ELSE Isnull(bi_mfg_invtrx.unitcost, currcost) *
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END
AS BomCost,
Isnull(dbo.mop1100.qtyrecvd * dbo.bi_mfgbom.cmpqty, 0
)
AS ExtBomQty,
CASE
WHEN cmpuofm = 'DOL' THEN Isnull(cmpqty, 0)
ELSE Isnull(bi_mfg_invtrx.unitcost, currcost) *
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END * Isnull(dbo.mop1100.qtyrecvd, 0)
AS ExtBomCost,
dbo.mop1110.qtytouse
AS ExtActQty,
CASE
WHEN qtytouse = 0 THEN 0
ELSE Round(item_costs_array_i_1 / mop1100.qtyrecvd,
2
)
END
AS ActUnitCost,
dbo.mop1110.item_costs_array_i_1
AS ExtActCost,
dbo.mop1110.item_costs_array_i_10,
CASE
WHEN dbo.bi_mfgbom.cmpqty IS NULL THEN qtytouse
ELSE dbo.mop1110.qtytouse - dbo.mop1100.qtyrecvd *
cmpqty
END
AS VarQty,
CASE
WHEN dbo.bi_mfgbom.cmpqty IS NULL THEN ( CASE
WHEN cmpuofm = 'DOL' THEN Isnull(cmpqty, 0)
ELSE dbo.mop1110.item_costs_array_i_1
END )
ELSE dbo.mop1110.item_costs_array_i_1 -
( CASE
WHEN
cmpuofm = 'DOL' THEN
Isnull(cmpqty, 0)
ELSE
Isnull(bi_mfg_invtrx.unitcost, currcost
)
*
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END *
Isnull(dbo.mop1100.qtyrecvd, 0) )
END
AS VarCost,
( CASE
WHEN qtytouse = 0 THEN 0
ELSE item_costs_array_i_1 / mop1100.qtyrecvd
END ) - ( CASE
WHEN cmpuofm = 'DOL' THEN
Isnull(cmpqty, 0)
ELSE Isnull(bi_mfg_invtrx.unitcost,
currcost) *
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END )
AS VarUnitCost,
dbo.bi_mfg_invtrx.unitcost,
dbo.mop1100.qtyrecvd,
dbo.mop1100.daterecd,
dbo.mop1100.postdate
FROM dbo.mop1110
INNER JOIN dbo.iv00101
ON dbo.mop1110.itemnmbr = dbo.iv00101.itemnmbr
LEFT OUTER JOIN dbo.bi_mfg_invtrx
ON dbo.mop1110.moprctnm = dbo.bi_mfg_invtrx.moprctnm
AND dbo.mop1110.itemnmbr = dbo.bi_mfg_invtrx.itemnmbr
LEFT OUTER JOIN dbo.mop1100
ON dbo.mop1110.moprctnm = dbo.mop1100.moprctnm
LEFT OUTER JOIN dbo.wo010032
ON dbo.mop1100.manufactureorder_i =
dbo.wo010032.manufactureorder_i
LEFT OUTER JOIN dbo.bi_mfgbom
ON dbo.wo010032.itemnmbr = dbo.bi_mfgbom.bomnumber
AND dbo.mop1110.itemnmbr = dbo.bi_mfgbom.cmpnumber
ORDER BY dbo.mop1110.moprctnm,
dbo.mop1110.itemnmbr
go
No comments:
Post a Comment