SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[BI_seepmHATBWrapper_mc] @I_dAgingDate datetime = NULL, @I_cStartVendorID char(15) = NULL, @I_cEndVendorID char(15) = NULL, @I_cStartVendorName char(65) = NULL, @I_cEndVendorName char(65) = NULL, @I_cStartClassID char(15) = NULL, @I_cEndClassID char(15) = NULL, @I_cStartUserDefined char(15) = NULL, @I_cEndUserDefined char(15) = NULL, @I_cStartPaymentPriority char(3) = NULL, @I_cEndPaymentPriority char(3) = NULL, @I_cStartDocumentNumber char(21) = NULL, @I_cEndDocumentNumber char(21) = NULL, @I_tUsingDocumentDate tinyint = NULL, @I_dStartDate datetime = NULL, @I_dEndDate datetime = NULL, @I_tExcludeNoActivity tinyint = NULL, @I_tExcludeMultiCurrency tinyint = NULL, @I_tExcludeZeroBalanceVendors tinyint = NULL, @I_tExcludeFullyPaidTrxs tinyint = NULL, @I_tExcludeCreditBalance tinyint = NULL, @I_tExcludeUnpostedAppldCrDocs tinyint = NULL as declare @X int, @VENDORID char (15), @CNTRLNUM char (21), @DOCNUMBR char (21), @DOCAMNT numeric (19,5) , @MIN_DEX_ROW_ID int set @X = 0 CREATE TABLE #PMATBVEN( [VENDORID] [char](15) NOT NULL, [VENDNAME] [char](65) NOT NULL, [VNDCLSID] [char](11) NOT NULL, [USERDEF1] [char](21) NOT NULL, [PYMNTPRI] [char](3) NOT NULL, [KEYSOURC] [char](41) NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#PMATBVEN] ON #PMATBVEN ( [VENDORID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#PMATBVEN] ON #PMATBVEN ( [VENDNAME] ASC, [VENDORID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK3#PMATBVEN] ON #PMATBVEN ( [VNDCLSID] ASC, [VENDORID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK4#PMATBVEN] ON #PMATBVEN ( [USERDEF1] ASC, [VENDORID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK5#PMATBVEN] ON #PMATBVEN ( [PYMNTPRI] ASC, [VENDORID] ASC ) CREATE TABLE #PMATBAPP( [APTVCHNM] [char](21) NOT NULL, [APTODCTY] [smallint] NOT NULL, [VCHRNMBR] [char](21) NOT NULL, [DOCTYPE] [smallint] NOT NULL, [APAMAGPR_1] [numeric](19, 5) NOT NULL, [APAMAGPR_2] [numeric](19, 5) NOT NULL, [APAMAGPR_3] [numeric](19, 5) NOT NULL, [APAMAGPR_4] [numeric](19, 5) NOT NULL, [APAMAGPR_5] [numeric](19, 5) NOT NULL, [APAMAGPR_6] [numeric](19, 5) NOT NULL, [APAMAGPR_7] [numeric](19, 5) NOT NULL, [APPLDAMT] [numeric](19, 5) NOT NULL, [POSTED] [tinyint] NOT NULL, [CURNCYID] [char](15) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORAPPAMT] [numeric](19, 5) NOT NULL, [OAGPRAMT_1] [numeric](19, 5) NOT NULL, [OAGPRAMT_2] [numeric](19, 5) NOT NULL, [OAGPRAMT_3] [numeric](19, 5) NOT NULL, [OAGPRAMT_4] [numeric](19, 5) NOT NULL, [OAGPRAMT_5] [numeric](19, 5) NOT NULL, [OAGPRAMT_6] [numeric](19, 5) NOT NULL, [OAGPRAMT_7] [numeric](19, 5) NOT NULL, [RLGANLOS] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#PMATBAPP] ON #PMATBAPP ( [APTVCHNM] ASC, [APTODCTY] ASC, [VCHRNMBR] ASC, [DOCTYPE] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#PMATBAPP] ON #PMATBAPP ( [VCHRNMBR] ASC, [DOCTYPE] ASC, [APTVCHNM] ASC, [APTODCTY] ASC ) CREATE TABLE #PMATBDOC( [VENDORID] [char](15) NOT NULL, [CNTRLNUM] [char](21) NOT NULL, [CNTRLTYP] [smallint] NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [DOCTYPE] [smallint] NOT NULL, [DOCAMNT] [numeric](19, 5) NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [DOCDATE] [datetime] NOT NULL, [DUEDATE] [datetime] NOT NULL, [DISCDATE] [datetime] NOT NULL, [TRXSORCE] [char](13) NOT NULL, [CURTRXAM] [numeric](19, 5) NOT NULL, [EAMAGPER_1] [numeric](19, 5) NOT NULL, [EAMAGPER_2] [numeric](19, 5) NOT NULL, [EAMAGPER_3] [numeric](19, 5) NOT NULL, [EAMAGPER_4] [numeric](19, 5) NOT NULL, [EAMAGPER_5] [numeric](19, 5) NOT NULL, [EAMAGPER_6] [numeric](19, 5) NOT NULL, [EAMAGPER_7] [numeric](19, 5) NOT NULL, [DISAMTAV] [numeric](19, 5) NOT NULL, [PERIODID] [smallint] NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [KEYSOURC] [char](41) NOT NULL, [DINVPDOF] [datetime] NOT NULL, [PSTGDATE] [datetime] NOT NULL, [CURNCYID] [char](15) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORDOCAMT] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORCTRXAM] [numeric](19, 5) NOT NULL, [OAGPRAMT_1] [numeric](19, 5) NOT NULL, [OAGPRAMT_2] [numeric](19, 5) NOT NULL, [OAGPRAMT_3] [numeric](19, 5) NOT NULL, [OAGPRAMT_4] [numeric](19, 5) NOT NULL, [OAGPRAMT_5] [numeric](19, 5) NOT NULL, [OAGPRAMT_6] [numeric](19, 5) NOT NULL, [OAGPRAMT_7] [numeric](19, 5) NOT NULL, [ODISAMTAV] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#PMATBDOC] ON #PMATBDOC ( [VENDORID] ASC, [CURNCYID] ASC, [CNTRLNUM] ASC, [CNTRLTYP] ASC, [KEYSOURC] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#PMATBDOC] ON #PMATBDOC ( [CURNCYID] ASC, [DEX_ROW_ID] ASC ) exec pmHistoricalAgedTrialBalance '#PMATBVEN', '#PMATBDOC', '#PMATBAPP', '', @I_dAgingDate, @I_cStartVendorID, @I_cEndVendorID, @I_cStartVendorName, @I_cEndVendorName, @I_cStartClassID, @I_cEndClassID, @I_cStartUserDefined, @I_cEndUserDefined, @I_cStartPaymentPriority, @I_cEndPaymentPriority, @I_cStartDocumentNumber, @I_cEndDocumentNumber, @I_tUsingDocumentDate, @I_dStartDate, @I_dEndDate, @I_tExcludeNoActivity, @I_tExcludeMultiCurrency, @I_tExcludeZeroBalanceVendors, @I_tExcludeFullyPaidTrxs, @I_tExcludeCreditBalance, @I_tExcludeUnpostedAppldCrDocs, '', 0, 0, 1, 0, 2, 0
Drop Table BIT_PMHATB_MC
Select HATB.* Into BIT_PMHATB_MC FROM ( select isnull([V].[VENDORID],'') as [VENDORID], isnull([VENDNAME],'') as [VENDNAME], isnull([VNDCLSID],'') as [VNDCLSID], isnull([USERDEF1],'') as [USERDEF1], isnull([PYMNTPRI],'') as [PYMNTPRI], isnull([V].[KEYSOURC],'') as [VEN_KEYSOURC], isnull([APTVCHNM],'') as [APTVCHNM], isnull([APTODCTY],0) as [APTODCTY], isnull([VCHRNMBR],'') as [VCHRNMBR], isnull([A].[DOCTYPE],0) as APP_DOCTYPE, isnull([APAMAGPR_1],0) as [APAMAGPR_1], isnull([APAMAGPR_2],0) as [APAMAGPR_2], isnull([APAMAGPR_3],0) as [APAMAGPR_3], isnull([APAMAGPR_4],0) as [APAMAGPR_4], isnull([APAMAGPR_5],0) as [APAMAGPR_5], isnull([APAMAGPR_6],0) as [APAMAGPR_6], isnull([APAMAGPR_7],0) as [APAMAGPR_7], isnull([APPLDAMT],0) as [APPLDAMT], isnull([POSTED],0) as [POSTED], isnull([ORAPPAMT],0) as [ORAPPAMT], isnull([A].[OAGPRAMT_1],0) as [APP_OAGPRAMT_1], isnull([A].[OAGPRAMT_2],0) as [APP_OAGPRAMT_2], isnull([A].[OAGPRAMT_3],0) as [APP_OAGPRAMT_3], isnull([A].[OAGPRAMT_4],0) as [APP_OAGPRAMT_4], isnull([A].[OAGPRAMT_5],0) as [APP_OAGPRAMT_5], isnull([A].[OAGPRAMT_6],0) as [APP_OAGPRAMT_6], isnull([A].[OAGPRAMT_7],0) as [APP_OAGPRAMT_7], isnull([CNTRLNUM],'') as [CNTRLNUM], isnull([CNTRLTYP],0) as [CNTRLTYP], isnull([DOCNUMBR],'') as [DOCNUMBR], isnull([D].[DOCTYPE],0) as DOC_DOCTYPE, isnull([DOCAMNT],0) as [DOCAMNT], isnull([DISTKNAM],0) as [DISTKNAM], isnull([DOCDATE],'1900-01-01') as [DOCDATE], isnull([DUEDATE],'1900-01-01') as [DUEDATE], isnull([DISCDATE],'1900-01-01') as [DISCDATE], isnull([TRXSORCE],'') as [TRXSORCE], isnull([CURTRXAM],0) as [CURTRXAM], isnull([EAMAGPER_1],0) as [EAMAGPER_1], isnull([EAMAGPER_2],0) as [EAMAGPER_2], isnull([EAMAGPER_3],0) as [EAMAGPER_3], isnull([EAMAGPER_4],0) as [EAMAGPER_4], isnull([EAMAGPER_5],0) as [EAMAGPER_5], isnull([EAMAGPER_6],0) as [EAMAGPER_6], isnull([EAMAGPER_7],0) as [EAMAGPER_7], isnull([DISAMTAV],0) as [DISAMTAV], isnull([PERIODID],0) as [PERIODID], isnull([WROFAMNT],0) as [WROFAMNT], isnull([D].[KEYSOURC],'') as [DOC_KEYSOURC], isnull([DINVPDOF],'1900-01-01') as [DINVPDOF], isnull([PSTGDATE],'1900-01-01') as [PSTGDATE], isnull([ORDOCAMT],0) as [ORDOCAMT], isnull([ORDISTKN],0) as [ORDISTKN], isnull([ORCTRXAM],0) as [ORCTRXAM], isnull([D].[OAGPRAMT_1],0) as [DOC_OAGPRAMT_1], isnull([D].[OAGPRAMT_2],0) as [DOC_OAGPRAMT_2], isnull([D].[OAGPRAMT_3],0) as [DOC_OAGPRAMT_3], isnull([D].[OAGPRAMT_4],0) as [DOC_OAGPRAMT_4], isnull([D].[OAGPRAMT_5],0) as [DOC_OAGPRAMT_5], isnull([D].[OAGPRAMT_6],0) as [DOC_OAGPRAMT_6], isnull([D].[OAGPRAMT_7],0) as [DOC_OAGPRAMT_7], isnull([ODISAMTAV],0) as [ODISAMTAV], isnull([ORWROFAM],0) as [ORWROFAM], 1 as DEX_ROW_ID from #PMATBVEN V left join #PMATBDOC D on V.VENDORID = D.VENDORID left join #PMATBAPP A on D.CNTRLNUM = A.APTVCHNM and D.DOCTYPE = A.APTODCTY ) as HATB update BIT_PMHATB_MC set DEX_ROW_ID = @X, @X = @X + 1 declare AMOUNT cursor for select VENDORID, CNTRLNUM, DOCNUMBR, DOCAMNT,Min(DEX_ROW_ID)as MIN_DEX_ROW_ID from BIT_PMHATB_MC group by VENDORID, CNTRLNUM,DOCNUMBR,DOCAMNT having count(*)>1 OPEN AMOUNT FETCH NEXT FROM AMOUNT INTO @VENDORID, @CNTRLNUM, @DOCNUMBR, @DOCAMNT, @MIN_DEX_ROW_ID WHILE @@FETCH_STATUS = 0 BEGIN update BIT_PMHATB_MC set CURTRXAM =0.00000, EAMAGPER_1=0.00000, EAMAGPER_2=0.00000, EAMAGPER_3=0.00000, EAMAGPER_4=0.00000, EAMAGPER_5=0.00000, EAMAGPER_6=0.00000, EAMAGPER_7=0.00000 where DEX_ROW_ID > @MIN_DEX_ROW_ID and VENDORID = @VENDORID and CNTRLNUM = @CNTRLNUM and DOCNUMBR = @DOCNUMBR and DOCAMNT = @DOCAMNT FETCH NEXT FROM AMOUNT INTO @VENDORID, @CNTRLNUM, @DOCNUMBR, @DOCAMNT,@MIN_DEX_ROW_ID end CLOSE AMOUNT DEALLOCATE AMOUNT
Select * from BI_PMHATB4_MC
GO
/****** Object: Table [dbo].[BIT_PMHATB_MC] Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_PMHATB_MC](
[VENDORID] [char](15) NOT NULL,
[VENDNAME] [char](65) NOT NULL,
[VNDCLSID] [char](11) NOT NULL,
[USERDEF1] [char](21) NOT NULL,
[PYMNTPRI] [char](3) NOT NULL,
[VEN_KEYSOURC] [char](41) NOT NULL,
[APTVCHNM] [char](21) NOT NULL,
[APTODCTY] [smallint] NOT NULL,
[VCHRNMBR] [char](21) NOT NULL,
[APP_DOCTYPE] [smallint] NOT NULL,
[APAMAGPR_1] [numeric](19, 5) NOT NULL,
[APAMAGPR_2] [numeric](19, 5) NOT NULL,
[APAMAGPR_3] [numeric](19, 5) NOT NULL,
[APAMAGPR_4] [numeric](19, 5) NOT NULL,
[APAMAGPR_5] [numeric](19, 5) NOT NULL,
[APAMAGPR_6] [numeric](19, 5) NOT NULL,
[APAMAGPR_7] [numeric](19, 5) NOT NULL,
[APPLDAMT] [numeric](19, 5) NOT NULL,
[POSTED] [tinyint] NOT NULL,
[ORAPPAMT] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_1] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_2] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_3] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_4] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_5] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_6] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_7] [numeric](19, 5) NOT NULL,
[CNTRLNUM] [char](21) NOT NULL,
[CNTRLTYP] [smallint] NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOC_DOCTYPE] [smallint] NOT NULL,
[DOCAMNT] [numeric](19, 5) NOT NULL,
[DISTKNAM] [numeric](19, 5) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[DUEDATE] [datetime] NOT NULL,
[DISCDATE] [datetime] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[EAMAGPER_1] [numeric](19, 5) NOT NULL,
[EAMAGPER_2] [numeric](19, 5) NOT NULL,
[EAMAGPER_3] [numeric](19, 5) NOT NULL,
[EAMAGPER_4] [numeric](19, 5) NOT NULL,
[EAMAGPER_5] [numeric](19, 5) NOT NULL,
[EAMAGPER_6] [numeric](19, 5) NOT NULL,
[EAMAGPER_7] [numeric](19, 5) NOT NULL,
[DISAMTAV] [numeric](19, 5) NOT NULL,
[PERIODID] [smallint] NOT NULL,
[WROFAMNT] [numeric](19, 5) NOT NULL,
[DOC_KEYSOURC] [char](41) NOT NULL,
[DINVPDOF] [datetime] NOT NULL,
[PSTGDATE] [datetime] NOT NULL,
[ORDOCAMT] [numeric](19, 5) NOT NULL,
[ORDISTKN] [numeric](19, 5) NOT NULL,
[ORCTRXAM] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_1] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_2] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_3] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_4] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_5] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_6] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_7] [numeric](19, 5) NOT NULL,
[ODISAMTAV] [numeric](19, 5) NOT NULL,
[ORWROFAM] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[BI_PMHATB2_MC] Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PMHATB2_MC]
AS
SELECT dbo.BIT_PMHATB_MC.VENDORID, dbo.BIT_PMHATB_MC.VENDNAME, dbo.BIT_PMHATB_MC.VNDCLSID, dbo.BIT_PMHATB_MC.USERDEF1, dbo.BIT_PMHATB_MC.PYMNTPRI,
dbo.BIT_PMHATB_MC.VEN_KEYSOURC, dbo.BIT_PMHATB_MC.APTVCHNM, dbo.BIT_PMHATB_MC.APTODCTY, dbo.BIT_PMHATB_MC.VCHRNMBR, dbo.BIT_PMHATB_MC.APP_DOCTYPE,
dbo.BIT_PMHATB_MC.APAMAGPR_1, dbo.BIT_PMHATB_MC.APAMAGPR_2, dbo.BIT_PMHATB_MC.APAMAGPR_3, dbo.BIT_PMHATB_MC.APAMAGPR_4, dbo.BIT_PMHATB_MC.APAMAGPR_5,
dbo.BIT_PMHATB_MC.APAMAGPR_6, dbo.BIT_PMHATB_MC.APAMAGPR_7, dbo.BIT_PMHATB_MC.APPLDAMT, dbo.BIT_PMHATB_MC.POSTED, dbo.BIT_PMHATB_MC.ORAPPAMT,
dbo.BIT_PMHATB_MC.APP_OAGPRAMT_1, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_2, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_3, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_4,
dbo.BIT_PMHATB_MC.APP_OAGPRAMT_5, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_6, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_7, dbo.BIT_PMHATB_MC.CNTRLNUM, dbo.BIT_PMHATB_MC.CNTRLTYP,
dbo.BIT_PMHATB_MC.DOCNUMBR, dbo.BIT_PMHATB_MC.DOC_DOCTYPE, dbo.BIT_PMHATB_MC.DISTKNAM, dbo.BIT_PMHATB_MC.DOCDATE, dbo.BIT_PMHATB_MC.DUEDATE,
dbo.BIT_PMHATB_MC.DISCDATE, dbo.BIT_PMHATB_MC.TRXSORCE, dbo.BIT_PMHATB_MC.CURTRXAM, dbo.BIT_PMHATB_MC.EAMAGPER_1, dbo.BIT_PMHATB_MC.EAMAGPER_2,
dbo.BIT_PMHATB_MC.EAMAGPER_3, dbo.BIT_PMHATB_MC.EAMAGPER_4, dbo.BIT_PMHATB_MC.EAMAGPER_5, dbo.BIT_PMHATB_MC.EAMAGPER_6, dbo.BIT_PMHATB_MC.EAMAGPER_7,
dbo.BIT_PMHATB_MC.DISAMTAV, dbo.BIT_PMHATB_MC.PERIODID, dbo.BIT_PMHATB_MC.WROFAMNT, dbo.BIT_PMHATB_MC.DOC_KEYSOURC, dbo.BIT_PMHATB_MC.DINVPDOF,
dbo.BIT_PMHATB_MC.PSTGDATE, dbo.BIT_PMHATB_MC.ORDOCAMT, dbo.BIT_PMHATB_MC.ORDISTKN, dbo.BIT_PMHATB_MC.ORCTRXAM, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_1,
dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_2, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_3, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_4, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_5,
dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_6, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_7, dbo.BIT_PMHATB_MC.ODISAMTAV, dbo.BIT_PMHATB_MC.ORWROFAM, dbo.BIT_PMHATB_MC.DEX_ROW_ID,
dbo.BIT_PMHATB_MC.DOCAMNT, ISNULL(Exch.ExchRate, 0) AS CalcRate, P.PORDNMBR, CASE WHEN Currency = '' THEN ExchCurr ELSE Currency END AS Currency, Exch.ExchCurr, Exch.ExchRate,
Exch.ExchOrDocamt
FROM dbo.BIT_PMHATB_MC LEFT OUTER JOIN
(SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCNUMBR, CURNCYID AS Currency, PORDNMBR
FROM dbo.PM20000
UNION
SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCNUMBR, CURNCYID, PORDNMBR
FROM dbo.PM30200) AS P ON dbo.BIT_PMHATB_MC.DOC_DOCTYPE = P.DOCTYPE AND dbo.BIT_PMHATB_MC.CNTRLNUM = P.VCHRNMBR AND
dbo.BIT_PMHATB_MC.VENDORID = P.VENDORID LEFT OUTER JOIN
(SELECT DCSTATUS, DOCTYPE, VCHRNMBR, PMNTNMBR, DOCDATE, VENDORID, CURNCYID AS ExchCurr, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE AS ExchRate, EXCHDATE, TIME1, RTCLCMTD,
ORCTRXAM, OPURAMT, ORFRTAMT, OMISCAMT, ORTAXAMT, ORCASAMT, ORCHKAMT, ORCHKTTL, ORCCDAMT, ORAPPAMT, ORDISTKN, ORDATKN, ORDDLRAT, ORTDISAM, ORWROFAM,
OBKPURAMT, ORBKTFRT, ORBKTMSC, UNGANLOS, RMMCERRS, OCHGAMT, ORDOCAMT AS ExchOrDocamt, ODISAMTAV, ORGAPDISCTKN, OTOTPAY, OR1099AM, DENXRATE, MCTRXSTT,
OrigBackoutTradeDisc, DEX_ROW_ID
FROM dbo.MC020103) AS Exch ON dbo.BIT_PMHATB_MC.DOC_DOCTYPE = Exch.DOCTYPE AND dbo.BIT_PMHATB_MC.CNTRLNUM = Exch.VCHRNMBR AND
dbo.BIT_PMHATB_MC.VENDORID = Exch.VENDORID
GO
/****** Object: View [dbo].[BI_PMHATB4_MC] Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PMHATB4_MC]
AS
SELECT VENDORID, VENDNAME, VNDCLSID, USERDEF1, PYMNTPRI, VEN_KEYSOURC, APTVCHNM, APTODCTY, VCHRNMBR, APP_DOCTYPE, APAMAGPR_1, APAMAGPR_2, APAMAGPR_3, APAMAGPR_4, APAMAGPR_5,
APAMAGPR_6, APAMAGPR_7, APPLDAMT, POSTED, ORAPPAMT, APP_OAGPRAMT_1, APP_OAGPRAMT_2, APP_OAGPRAMT_3, APP_OAGPRAMT_4, APP_OAGPRAMT_5, APP_OAGPRAMT_6, APP_OAGPRAMT_7,
CNTRLNUM, CNTRLTYP, DOCNUMBR, DOC_DOCTYPE, DISTKNAM, DOCDATE, DUEDATE, DISCDATE, TRXSORCE, CURTRXAM, EAMAGPER_1, EAMAGPER_2, EAMAGPER_3, EAMAGPER_4, EAMAGPER_5,
EAMAGPER_6, EAMAGPER_7, DISAMTAV, PERIODID, WROFAMNT, DOC_KEYSOURC, DINVPDOF, PSTGDATE, ORDOCAMT, ORDISTKN, ORCTRXAM, DOC_OAGPRAMT_1, DOC_OAGPRAMT_2, DOC_OAGPRAMT_3,
DOC_OAGPRAMT_4, DOC_OAGPRAMT_5, DOC_OAGPRAMT_6, DOC_OAGPRAMT_7, ODISAMTAV, ORWROFAM, DEX_ROW_ID, DOCAMNT, CalcRate, ROUND(DOCAMNT / CalcRate, 2) AS CalcOrdDocamt,
ROUND(EAMAGPER_1 / CalcRate, 2) AS CEAMAGPER_1, ROUND(EAMAGPER_2 / CalcRate, 2) AS CEAMAGPER_2, ROUND(EAMAGPER_3 / CalcRate, 2) AS CEAMAGPER_3, ROUND(EAMAGPER_4 / CalcRate, 2)
AS CEAMAGPER_4, ROUND(EAMAGPER_5 / CalcRate, 2) AS CEAMAGPER_5, ROUND(EAMAGPER_6 / CalcRate, 2) AS CEAMAGPER_6, ROUND(EAMAGPER_7 / CalcRate, 2) AS CEAMAGPER_7, PORDNMBR,
Currency
FROM dbo.BI_PMHATB2_MC
WHERE (Currency <> 'TTD')
GO
No comments:
Post a Comment