SQL Stored Procedure - Dynamics GP Historical Aged RM Trial Balance for Tableau

--Run all scripts to create all objects

-- exec BI_HARTB_RMFinal_Load '2017-04-19'

/****** Object:  StoredProcedure [dbo].[BI_HARTB_RMFinal_Load]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BI_HARTB_RMFinal_Load] (@AgeDate as datetime)

AS
BEGIN

truncate table BI_HARTB_RMFinal_Temp
truncate table BI_HARTB_RMFinal_Temp3

insert into BI_HARTB_RMFinal_Temp
SELECT @AgeDate, [CUSTNMBR]
      ,[CPRCSTNM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[SLPRSNID]
      ,[VOIDSTTS]
      ,[VOIDDATE]
      ,[APFRDCNM]
      ,[APFRDCDT]
      ,[ApplyFromGLPostDate]
      ,[APFRMAPLYAMT]
      ,[ActualApplyToAmount]
      ,[ApplyFromPostDate]
      ,[LastDate]
      ,[LastStatus]
      ,[LastAmt]
      ,[LastApplyToDoc]
      ,[DocType],wrofamnt
  FROM [TWO].[dbo].[BI_HARTB_RMFinal] where docdate <= @agedate

  --Load all Invoices and applications
insert into BI_HARTB_RMFinal_Temp3
Select [AgeDate]
      ,[CUSTNMBR]
      ,[CPRCSTNM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[SLPRSNID]
      ,[VOIDSTTS]
      ,[VOIDDATE]
      ,[APFRDCNM]
      ,[APFRDCDT]
      ,[ApplyFromGLPostDate]
      ,[APFRMAPLYAMT]
      ,[ActualApplyToAmount]
      ,[ApplyFromPostDate]
      ,[LastDate]
      ,[LastStatus]
      ,[LastAmt]
      ,[LastApplyToDoc]
      ,[DocType]
      ,[DocDateDiff]
      ,[AgedLastDate]
      ,[AgedLastStatus]
      ,[AgedLastAmt]
 ,[isfullypaid]
  FROM [TWO].[dbo].[BI_HARTB_RMFinal_Temp2A]

  --Add partial unapplied credits
  insert into [BI_HARTB_RMFinal_Temp3]
  select [AgeDate]
      ,[CUSTNMBR]
      ,[CPRCSTNM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[SLPRSNID]
      ,[VOIDSTTS]
      ,[VOIDDATE]
      ,[APFRDCNM]
      ,[APFRDCDT]
      ,[ApplyFromGLPostDate]
      ,[APFRMAPLYAMT]
      ,[ActualApplyToAmount]
      ,[ApplyFromPostDate]
      ,[LastDate]
      ,[LastStatus]
      ,[LastAmt]
      ,[LastApplyToDoc]
      ,[DocType]
      ,[DocDateDiff]
      ,[AgedLastDate]
      ,[AgedLastStatus]
      ,[AgedLastAmt]
 ,isfullypaid
  FROM [TWO].[dbo].[BI_HARTB_RMFinal_Temp2b]

  select * from BI_HARTB_RMFinal_temp3
END

GO
/****** Object:  Table [dbo].[BI_HARTB_RMFinal_Temp]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_HARTB_RMFinal_Temp](
[AgeDate] [datetime] NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[CPRCSTNM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[VOIDSTTS] [smallint] NOT NULL,
[VOIDDATE] [datetime] NOT NULL,
[APFRDCNM] [char](21) NULL,
[APFRDCDT] [datetime] NULL,
[ApplyFromGLPostDate] [datetime] NULL,
[APFRMAPLYAMT] [numeric](19, 5) NULL,
[ActualApplyToAmount] [numeric](19, 5) NULL,
[ApplyFromPostDate] [datetime] NULL,
[LastDate] [datetime] NULL,
[LastStatus] [varchar](9) NOT NULL,
[LastAmt] [numeric](21, 5) NULL,
[LastApplyToDoc] [char](21) NULL,
[DocType] [varchar](14) NULL,
[wrofamnt] [numeric](19, 5) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BI_HARTB_RMFinal_Temp3]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_HARTB_RMFinal_Temp3](
[AgeDate] [datetime] NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[CPRCSTNM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[VOIDSTTS] [smallint] NOT NULL,
[VOIDDATE] [datetime] NOT NULL,
[APFRDCNM] [char](21) NULL,
[APFRDCDT] [datetime] NULL,
[ApplyFromGLPostDate] [datetime] NULL,
[APFRMAPLYAMT] [numeric](19, 5) NULL,
[ActualApplyToAmount] [numeric](19, 5) NULL,
[ApplyFromPostDate] [datetime] NULL,
[LastDate] [datetime] NULL,
[LastStatus] [varchar](9) NOT NULL,
[LastAmt] [numeric](21, 5) NULL,
[LastApplyToDoc] [char](21) NULL,
[DocType] [varchar](14) NULL,
[DocDateDiff] [int] NULL,
[AgedLastDate] [datetime] NULL,
[AgedLastStatus] [varchar](9) NOT NULL,
[AgedLastAmt] [numeric](21, 5) NULL,
[IsfullyPaid] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[BI_HARTB_RMApply]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMApply]
AS
SELECT        CUSTNMBR, CPRCSTNM, TRXSORCE, DATE1, TIME1, GLPOSTDT, POSTED, TAXDTLID, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, DISTKNAM, DISAVTKN,
                         WROFAMNT, ORAPTOAM, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, APFRDCNM, APFRDCTY, APFRDCDT, ApplyFromGLPostDate, FROMCURR,
                         CASE WHEN APFRMAPLYAMT = 0 THEN ActualWriteOffAmount ELSE APFRMAPLYAMT - actualwriteoffamount END AS APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT,
                         CASE WHEN ActualApplyToAmount = 0 THEN ActualWriteOffAmount ELSE actualapplytoamount - ActualWriteOffAmount END AS ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken,
                         ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT, OAPYTORNDDISC,
                         GSTDSAMT, PPSAMDED, RLGANLOS, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv, Settled_Loss_DebitDiscAv,
                         Revaluation_Status
FROM            (SELECT        CUSTNMBR, CPRCSTNM, TRXSORCE, DATE1, TIME1, GLPOSTDT, POSTED, TAXDTLID, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, DISTKNAM,
                                                    DISAVTKN, WROFAMNT, ORAPTOAM, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, APFRDCNM, APFRDCTY, APFRDCDT,
                                                    ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT, ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken,
                                                    ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT,
                                                    OAPYTORNDDISC, GSTDSAMT, PPSAMDED, RLGANLOS, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv,
                                                    Settled_Loss_DebitDiscAv, Revaluation_Status
                          FROM            dbo.RM20201
                          UNION
                          SELECT        CUSTNMBR, CPRCSTNM, TRXSORCE, DATE1, TIME1, GLPOSTDT, POSTED, TAXDTLID, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, DISTKNAM,
                                                   DISAVTKN, WROFAMNT, ORAPTOAM, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, APFRDCNM, APFRDCTY, APFRDCDT,
                                                   ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT, ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken,
                                                   ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT,
                                                   OAPYTORNDDISC, GSTDSAMT, PPSAMDED, RLGANLOS, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv,
                                                   Settled_Loss_DebitDiscAv, Revaluation_Status
                          FROM            dbo.RM30201) AS derivedtbl_1

GO
/****** Object:  View [dbo].[BI_HARTB_RMTrx]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMTrx]
AS
SELECT        CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, TRXSORCE, RMDTYPAL, CSHRCTYP, DUEDATE, DOCDATE, POSTDATE, GLPOSTDT, ORTRXAMT, CURTRXAM, SLSAMNT,
                         TAXAMNT, COSTAMNT, FRTAMNT, MISCAMNT, CASHAMNT, DISTKNAM, TRXDSCRN, SLPRSNID, SLSTERCD, TAXSCHID, CURNCYID, PYMTRMID, SHIPMTHD, NOTEINDX, ADRSCODE, VOIDSTTS, VOIDDATE
FROM            (SELECT        VOIDSTTS, VOIDDATE, CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, TRXSORCE, RMDTYPAL, CSHRCTYP, DUEDATE, DOCDATE, POSTDATE, GLPOSTDT,
                                                    ORTRXAMT, CURTRXAM, SLSAMNT, TAXAMNT, COSTAMNT, FRTAMNT, MISCAMNT, CASHAMNT, DISTKNAM, TRXDSCRN, SLPRSNID, SLSTERCD, TAXSCHID, CURNCYID, PYMTRMID, SHIPMTHD,
                                                    NOTEINDX, ADRSCODE
                          FROM            dbo.RM20101
                          UNION
                          SELECT        VOIDSTTS, VOIDDATE, CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, TRXSORCE, RMDTYPAL, CSHRCTYP, DUEDATE, DOCDATE, POSTDATE, GLPOSTDT,
                                                   ORTRXAMT, CURTRXAM, SLSAMNT, TAXAMNT, COSTAMNT, FRTAMNT, MISCAMNT, CASHAMNT, DISTKNAM, TRXDSCRN, SLPRSNID, SLSTERCD, TAXSCHID, CURNCYID, PYMTRMID, SHIPMTHD,
                                                   NOTEINDX, ADRSCODE
                          FROM            dbo.RM30101) AS derivedtbl_1

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal]
AS
SELECT DISTINCT
                         dbo.BI_HARTB_RMTrx.CUSTNMBR, dbo.BI_HARTB_RMTrx.CPRCSTNM, dbo.BI_HARTB_RMTrx.DOCNUMBR, dbo.BI_HARTB_RMTrx.RMDTYPAL, dbo.BI_HARTB_RMTrx.DOCDATE,
                         dbo.BI_HARTB_RMTrx.GLPOSTDT, CASE WHEN ORTRXAMT = 0 THEN isnull(actualwriteoffamount, 0) ELSE ortrxamt END AS ORTRXAMT, dbo.BI_HARTB_RMTrx.CURTRXAM, dbo.BI_HARTB_RMTrx.SLPRSNID,
                         dbo.BI_HARTB_RMTrx.VOIDSTTS, dbo.BI_HARTB_RMTrx.VOIDDATE, dbo.BI_HARTB_RMApply.APFRDCNM, dbo.BI_HARTB_RMApply.APFRDCDT, dbo.BI_HARTB_RMApply.ApplyFromGLPostDate,
                         dbo.BI_HARTB_RMApply.APFRMAPLYAMT, dbo.BI_HARTB_RMApply.ActualApplyToAmount, dbo.BI_HARTB_RMApply.DATE1 AS ApplyFromPostDate,
                         CASE WHEN voidstts = 1 THEN voiddate ELSE CASE WHEN applyfromglpostdate IS NOT NULL THEN applyfromglpostdate ELSE docdate END END AS LastDate,
                         CASE WHEN voidstts = 1 THEN 'Voided' ELSE CASE WHEN applyfromglpostdate IS NOT NULL THEN 'ApplyDate' ELSE 'DocDate' END END AS LastStatus, CASE WHEN actualapplytoamount IS NOT NULL
                         THEN actualapplytoamount * - 1 ELSE CASE WHEN rmdtypal IN (2, 7, 8, 9) THEN ortrxamt * - 1 ELSE ortrxamt END END AS LastAmt, CASE WHEN APTODCNM IS NULL
                         THEN docnumbr ELSE aptodcnm END AS LastApplyToDoc,
                         CASE rmdtypal WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Scheduled Pmt' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo'
                          WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS DocType, dbo.BI_HARTB_RMApply.WROFAMNT
FROM            dbo.BI_HARTB_RMApply RIGHT OUTER JOIN
                         dbo.BI_HARTB_RMTrx ON dbo.BI_HARTB_RMApply.CUSTNMBR = dbo.BI_HARTB_RMTrx.CUSTNMBR AND dbo.BI_HARTB_RMApply.APFRDCNM = dbo.BI_HARTB_RMTrx.DOCNUMBR

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp1]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp1]
AS
SELECT        AgeDate, CUSTNMBR, CPRCSTNM, DOCNUMBR, RMDTYPAL, DOCDATE, GLPOSTDT, ORTRXAMT, CURTRXAM, SLPRSNID, VOIDSTTS, VOIDDATE, APFRDCNM, APFRDCDT, ApplyFromGLPostDate,
                         APFRMAPLYAMT, ActualApplyToAmount, ApplyFromPostDate, LastDate, LastStatus, LastAmt, LastApplyToDoc, DocType, CAST(AgeDate - DOCDATE AS int) AS DocDateDiff, CASE WHEN voidstts = 1 AND
                         agedate < voiddate THEN CASE WHEN agedate > applyfromglpostdate THEN applyfromglpostdate ELSE docdate END ELSE lastdate END AS AgedLastDate, CASE WHEN voidstts = 1 AND
                         agedate < voiddate THEN CASE WHEN agedate > applyfromglpostdate THEN 'ApplyDate' ELSE 'DocDate' END ELSE laststatus END AS AgedLastStatus, CASE WHEN agedate < (CASE WHEN voidstts = 1 AND
                         agedate < voiddate THEN CASE WHEN agedate > applyfromglpostdate THEN applyfromglpostdate ELSE docdate END ELSE lastdate END) THEN 0 ELSE lastamt END AS AgedLastAmt
FROM            dbo.BI_HARTB_RMFinal_Temp

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp2]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp2]
AS
SELECT        AgeDate, CUSTNMBR, CPRCSTNM, DOCNUMBR, RMDTYPAL, DOCDATE, GLPOSTDT, ORTRXAMT, CURTRXAM, SLPRSNID, VOIDSTTS, VOIDDATE, APFRDCNM, APFRDCDT, ApplyFromGLPostDate,
                         APFRMAPLYAMT, ActualApplyToAmount, ApplyFromPostDate, LastDate, LastStatus, LastAmt, LastApplyToDoc, DocType, DocDateDiff, AgedLastDate, AgedLastStatus, AgedLastAmt
FROM            dbo.BI_HARTB_RMFinal_Temp1
WHERE        (AgedLastStatus <> 'voided') AND (DocDateDiff >= 0)

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_ApplyCBal]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_ApplyCBal]
AS
SELECT        DOCNUMBR, OSBal, AppliedAmt, CURTRXAM, ORTRXAMT
FROM            (SELECT        DOCNUMBR, SUM(ORTRXAMT + AppliedAmt) AS OSBal, AppliedAmt, CURTRXAM, ORTRXAMT
                          FROM            (SELECT        DOCNUMBR, ORTRXAMT, SUM(AgedLastAmt) AS AppliedAmt, CURTRXAM
                                                    FROM            dbo.BI_HARTB_RMFinal_Temp2 AS BI_HARTB_RMFinal_Temp2_1
                                                    GROUP BY DOCNUMBR, ORTRXAMT, CURTRXAM
                                                    HAVING         (SUM(AgedLastAmt) <= 0)) AS derivedtbl_1_1
                          GROUP BY DOCNUMBR, AppliedAmt, CURTRXAM, ORTRXAMT) AS derivedtbl_2
WHERE        (OSBal > 0)

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp2B]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp2B]
AS
SELECT        dbo.BI_HARTB_RMFinal_Temp2.AgeDate, dbo.BI_HARTB_RMFinal_Temp2.CUSTNMBR, dbo.BI_HARTB_RMFinal_Temp2.CPRCSTNM, dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR,
                         dbo.BI_HARTB_RMFinal_Temp2.RMDTYPAL, dbo.BI_HARTB_RMFinal_Temp2.DOCDATE, dbo.BI_HARTB_RMFinal_Temp2.GLPOSTDT, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal AS ORTRXAMT,
                         dbo.BI_HARTB_RMFinal_Temp2.CURTRXAM, dbo.BI_HARTB_RMFinal_Temp2.SLPRSNID, dbo.BI_HARTB_RMFinal_Temp2.VOIDSTTS, dbo.BI_HARTB_RMFinal_Temp2.VOIDDATE,
                         dbo.BI_HARTB_RMFinal_Temp2.APFRDCNM, dbo.BI_HARTB_RMFinal_Temp2.APFRDCDT, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromGLPostDate,
                         dbo.BI_HARTB_RMFinal_ApplyCBal.AppliedAmt AS APFRMAPLYAMT, dbo.BI_HARTB_RMFinal_ApplyCBal.AppliedAmt AS ActualApplyToAmount, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromPostDate,
                         dbo.BI_HARTB_RMFinal_Temp2.LastDate, dbo.BI_HARTB_RMFinal_Temp2.LastStatus, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal * - 1 AS LastAmt,
                         dbo.BI_HARTB_RMFinal_ApplyCBal.DOCNUMBR AS LastApplyToDoc, dbo.BI_HARTB_RMFinal_Temp2.DocType, dbo.BI_HARTB_RMFinal_Temp2.DocDateDiff, dbo.BI_HARTB_RMFinal_Temp2.AgedLastDate,
                         dbo.BI_HARTB_RMFinal_Temp2.AgedLastStatus, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal * - 1 AS AgedLastAmt, 0 AS IsFullyPaid
FROM            dbo.BI_HARTB_RMFinal_Temp2 INNER JOIN
                         dbo.BI_HARTB_RMFinal_ApplyCBal ON dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR = dbo.BI_HARTB_RMFinal_ApplyCBal.DOCNUMBR
GROUP BY dbo.BI_HARTB_RMFinal_Temp2.AgeDate, dbo.BI_HARTB_RMFinal_Temp2.CUSTNMBR, dbo.BI_HARTB_RMFinal_Temp2.CPRCSTNM, dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR,
                         dbo.BI_HARTB_RMFinal_Temp2.RMDTYPAL, dbo.BI_HARTB_RMFinal_Temp2.DOCDATE, dbo.BI_HARTB_RMFinal_Temp2.GLPOSTDT, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal,
                         dbo.BI_HARTB_RMFinal_Temp2.CURTRXAM, dbo.BI_HARTB_RMFinal_Temp2.SLPRSNID, dbo.BI_HARTB_RMFinal_Temp2.VOIDSTTS, dbo.BI_HARTB_RMFinal_Temp2.VOIDDATE,
                         dbo.BI_HARTB_RMFinal_Temp2.APFRDCNM, dbo.BI_HARTB_RMFinal_Temp2.APFRDCDT, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromGLPostDate, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromPostDate,
                         dbo.BI_HARTB_RMFinal_Temp2.LastDate, dbo.BI_HARTB_RMFinal_Temp2.LastStatus, dbo.BI_HARTB_RMFinal_Temp2.DocType, dbo.BI_HARTB_RMFinal_Temp2.DocDateDiff,
                         dbo.BI_HARTB_RMFinal_Temp2.AgedLastDate, dbo.BI_HARTB_RMFinal_Temp2.AgedLastStatus, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal * - 1, dbo.BI_HARTB_RMFinal_ApplyCBal.AppliedAmt,
                         dbo.BI_HARTB_RMFinal_ApplyCBal.DOCNUMBR

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_FullyPaid]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_FullyPaid]
AS
SELECT        LastApplyToDoc, SUM(AgedLastAmt) AS AgedLastAmt, 1 AS IsFullyPaid
FROM            (SELECT        LastApplyToDoc, SUM(AgedLastAmt) AS AgedLastAmt, ORTRXAMT
                          FROM            dbo.BI_HARTB_RMFinal_Temp2 AS BI_HARTB_RMFinal_Temp2_1
                          GROUP BY LastApplyToDoc, ORTRXAMT) AS derivedtbl_1
GROUP BY LastApplyToDoc
HAVING        (SUM(AgedLastAmt) = 0)

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp2A]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp2A]
AS
SELECT        dbo.BI_HARTB_RMFinal_Temp2.AgeDate, dbo.BI_HARTB_RMFinal_Temp2.CUSTNMBR, dbo.BI_HARTB_RMFinal_Temp2.CPRCSTNM, dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR,
                         dbo.BI_HARTB_RMFinal_Temp2.RMDTYPAL, dbo.BI_HARTB_RMFinal_Temp2.DOCDATE, dbo.BI_HARTB_RMFinal_Temp2.GLPOSTDT, dbo.BI_HARTB_RMFinal_Temp2.ORTRXAMT,
                         dbo.BI_HARTB_RMFinal_Temp2.CURTRXAM, dbo.BI_HARTB_RMFinal_Temp2.SLPRSNID, dbo.BI_HARTB_RMFinal_Temp2.VOIDSTTS, dbo.BI_HARTB_RMFinal_Temp2.VOIDDATE,
                         dbo.BI_HARTB_RMFinal_Temp2.APFRDCNM, dbo.BI_HARTB_RMFinal_Temp2.APFRDCDT, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromGLPostDate, dbo.BI_HARTB_RMFinal_Temp2.APFRMAPLYAMT,
                         dbo.BI_HARTB_RMFinal_Temp2.ActualApplyToAmount, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromPostDate, dbo.BI_HARTB_RMFinal_Temp2.LastDate, dbo.BI_HARTB_RMFinal_Temp2.LastStatus,
                         dbo.BI_HARTB_RMFinal_Temp2.LastAmt, dbo.BI_HARTB_RMFinal_Temp2.LastApplyToDoc, dbo.BI_HARTB_RMFinal_Temp2.DocType, dbo.BI_HARTB_RMFinal_Temp2.DocDateDiff,
                         dbo.BI_HARTB_RMFinal_Temp2.AgedLastDate, dbo.BI_HARTB_RMFinal_Temp2.AgedLastStatus, dbo.BI_HARTB_RMFinal_Temp2.AgedLastAmt, ISNULL(dbo.BI_HARTB_RMFinal_FullyPaid.IsFullyPaid, 0)
                         AS IsFullyPaid
FROM            dbo.BI_HARTB_RMFinal_Temp2 LEFT OUTER JOIN
                         dbo.BI_HARTB_RMFinal_FullyPaid ON dbo.BI_HARTB_RMFinal_Temp2.LastApplyToDoc = dbo.BI_HARTB_RMFinal_FullyPaid.LastApplyToDoc

GO