http://support.microsoft.com/kb/2714780
https://community.dynamics.com/gp/f/32/p/99479/278940.aspx
These following views and sp are experimental. It has worked in my specific situation to restore the missing HITB records. It will identify all missing records, reconstruct what is required, then resequence them based on the current sequence numbering in the HITB. Execute the BI_HITB_RECRUN to populate the SEE30303 with the correctly sequenced missing records.
/****** Object: View [dbo].[BI_HITB_Reconcile] Script Date: 09/19/2014 15:28:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HITB_Reconcile]
AS
SELECT DISTINCT
TOP (100) PERCENT dbo.SOP30200.USER2ENT, dbo.IV00101.ITMCLSCD, dbo.SOP30200.VOIDSTTS, dbo.SEE30303.ITEMNMBR AS HITBItemNo, dbo.SOP30300.ITEMNMBR,
dbo.SOP30200.DOCDATE, dbo.SOP30200.GLPOSTDT, dbo.SOP30300.SOPNUMBE, CASE sop30200.SOPTYPE WHEN 3 THEN 6 WHEN 4 THEN 5 END AS doctype, dbo.SOP30300.LOCNCODE,
'' AS Rctnum, '' AS RctSeqNum, 1 AS pchsrcty, 1 AS qtytype, dbo.SOP30300.UOFM,
dbo.SOP30300.QTYBSUOM * CASE WHEN sop30200.soptype = 3 THEN QUANTITY * - 1 ELSE quantity END AS TrxQtyinbs,
CASE WHEN sop30200.soptype = 3 THEN QUANTITY * - 1 ELSE quantity END AS TrxQty, 0 AS varqty, dbo.SOP30300.UNITCOST,
CASE WHEN sop30200.soptype = 3 THEN sop30300.extdcost * - 1 ELSE sop30300.extdcost END AS ExtCost, dbo.SOP30300.DECPLQTY, dbo.SOP30300.DECPLCUR, 0 AS overrec, 0 AS overrel,
0 AS overreldate, '' AS Rctnum1, 'SOP' AS Hstmodul, '' AS ortrxsrc, dbo.SOP30300.LNITMSEQ AS Lnseqnmbr, dbo.SOP30300.LNITMSEQ AS lnseq, dbo.SOP30300.CMPNTSEQ, '' AS scrfr, '' AS vend,
'' AS po, dbo.SOP30200.REFRENCE, 3 AS Vctn, dbo.SOP30300.INVINDX, dbo.SOP30300.CSLSINDX, gl.JRNENTRY, gl.TRXSORCE,
CASE WHEN sop30200.soptype = 4 THEN XTNDPRCE ELSE 0 END AS Debitamt, CASE WHEN sop30200.soptype = 3 THEN XTNDPRCE ELSE 0 END AS CreditAmt, gl.TRXDATE AS Date1, 0 AS Time1,
dbo.SOP30300.DEX_ROW_ID
FROM dbo.SOP30300 INNER JOIN
dbo.SOP30200 ON dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE AND dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE INNER JOIN
dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
(SELECT JRNENTRY, TRXDATE, ORDOCNUM, TRXSORCE
FROM dbo.GL30000
GROUP BY JRNENTRY, TRXDATE, ORDOCNUM, TRXSORCE) AS gl ON dbo.SOP30300.SOPNUMBE = gl.ORDOCNUM LEFT OUTER JOIN
dbo.IV10002 ON dbo.SOP30300.ITEMNMBR = dbo.IV10002.ITEMNMBR AND dbo.SOP30300.SOPNUMBE = dbo.IV10002.IVDOCNBR FULL OUTER JOIN
dbo.SEE30303 ON dbo.SOP30300.UOFM = dbo.SEE30303.UOFM AND dbo.SOP30300.SOPNUMBE = dbo.SEE30303.DOCNUMBR AND dbo.SOP30300.ITEMNMBR = dbo.SEE30303.ITEMNMBR
WHERE (CASE WHEN sop30200.soptype = 4 THEN QUANTITY * - 1 ELSE quantity END <> 0) AND (dbo.SEE30303.ITEMNMBR IS NULL) AND (dbo.SOP30300.SOPTYPE IN (3, 4)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (NOT (dbo.SOP30300.ITEMNMBR IN ('*misc')))
ORDER BY dbo.SOP30200.DOCDATE DESC
GO
/****** Object: View [dbo].[BI_HITB_Reconcile2] Script Date: 09/19/2014 15:28:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HITB_Reconcile2]
AS
SELECT ITEMNMBR, DOCDATE, GLPOSTDT, SOPNUMBE, doctype, LOCNCODE, Rctnum, RctSeqNum, pchsrcty, qtytype, UOFM, TrxQtyinbs, TrxQty, varqty, UNITCOST, ExtCost, DECPLQTY, DECPLCUR,
overrec, overrel, overreldate, Rctnum1, Hstmodul, ortrxsrc, Lnseqnmbr, lnseq, CMPNTSEQ, scrfr, vend, po, REFRENCE, Vctn, INVINDX, CSLSINDX, JRNENTRY, TRXSORCE, Debitamt, CreditAmt,
Date1, Time1, DEX_ROW_ID AS dexrow
FROM dbo.BI_HITB_Reconcile
GO
/****** Object: View [dbo].[BI_HITB_Reconcile3] Script Date: 09/19/2014 15:29:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HITB_Reconcile3]
AS
SELECT dbo.bi_hitb_rec.ITEMNMBR, seqnm.Next_SEQNUMBR AS SeqNo, dbo.bi_hitb_rec.DOCDATE, dbo.bi_hitb_rec.GLPOSTDT, dbo.bi_hitb_rec.SOPNUMBE, dbo.bi_hitb_rec.doctype,
dbo.bi_hitb_rec.LOCNCODE, dbo.bi_hitb_rec.Rctnum, dbo.bi_hitb_rec.RctSeqNum, dbo.bi_hitb_rec.pchsrcty, dbo.bi_hitb_rec.qtytype, dbo.bi_hitb_rec.UOFM, dbo.bi_hitb_rec.TrxQtyinbs,
dbo.bi_hitb_rec.TrxQty, dbo.bi_hitb_rec.varqty, dbo.bi_hitb_rec.UNITCOST, dbo.bi_hitb_rec.ExtCost, dbo.bi_hitb_rec.DECPLQTY, dbo.bi_hitb_rec.DECPLCUR, dbo.bi_hitb_rec.overrec,
dbo.bi_hitb_rec.overrel, dbo.bi_hitb_rec.overreldate, dbo.bi_hitb_rec.Rctnum1, dbo.bi_hitb_rec.Hstmodul, dbo.bi_hitb_rec.ortrxsrc, dbo.bi_hitb_rec.Lnseqnmbr, dbo.bi_hitb_rec.lnseq,
dbo.bi_hitb_rec.CMPNTSEQ, dbo.bi_hitb_rec.scrfr, dbo.bi_hitb_rec.vend, dbo.bi_hitb_rec.po, dbo.bi_hitb_rec.REFRENCE, dbo.bi_hitb_rec.Vctn, dbo.bi_hitb_rec.INVINDX, dbo.bi_hitb_rec.CSLSINDX,
dbo.bi_hitb_rec.JRNENTRY, dbo.bi_hitb_rec.TRXSORCE, dbo.bi_hitb_rec.Debitamt, dbo.bi_hitb_rec.CreditAmt, dbo.bi_hitb_rec.Date1, dbo.bi_hitb_rec.Time1, dbo.bi_hitb_rec.dexrow
FROM dbo.bi_hitb_rec INNER JOIN
(SELECT ITEMNMBR, MAX(SEQNUMBR) + 1 AS Next_SEQNUMBR
FROM dbo.SEE30303 AS SEE30303_1
GROUP BY ITEMNMBR) AS seqnm ON dbo.bi_hitb_rec.ITEMNMBR = seqnm.ITEMNMBR
GO
/****** Object: StoredProcedure [dbo].[BI_HITB_RecRun] Script Date: 09/19/2014 15:39:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BI_HITB_RecRun]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @dex as int
drop table bi_hitb_rec
select * into bi_hitb_rec from bi_hitb_reconcile2
while (SELECT COUNT(*) AS Expr1
FROM BI_HITB_Rec) >=1
begin
select @dex=MAX(dexrow) from bi_hitb_rec
insert into see30303 select
[ITEMNMBR]
,[SeqNo]
,[DOCDATE]
,[GLPOSTDT]
,[SOPNUMBE]
,[doctype]
,[LOCNCODE]
,[Rctnum]
,[RctSeqNum]
,[pchsrcty]
,[qtytype]
,[UOFM]
,[TrxQtyinbs]
,[TrxQty]
,[varqty]
,[UNITCOST]
,[ExtCost]
,[DECPLQTY]
,[DECPLCUR]
,[overrec]
,[overrel]
,[overreldate]
,[Rctnum1]
,[Hstmodul]
,[ortrxsrc]
,[Lnseqnmbr]
,[lnseq]
,[CMPNTSEQ]
,[scrfr]
,[vend]
,[po]
,[REFRENCE]
,[Vctn]
,[INVINDX]
,[CSLSINDX]
,[JRNENTRY]
,[TRXSORCE]
,[Debitamt]
,[CreditAmt]
,[Date1]
,[Time1]
from bi_hitb_reconcile3 where dexrow = @dex
delete from bi_hitb_rec where dexrow = @dex
print @dex
end
END
GO
No comments:
Post a Comment