CREATE procedure usr_RS_DifferenceBetweenPLandGL_by_TransactionNumber_SupplierCode_InvoiceNumber
@CC nchar(2)=’01’
as
CREATE TABLE #TransactionNumbers(
[TransactionNumber] [nvarchar](9) NOT NULL,
[SupplierCode] [nvarchar](15) NOT NULL,
[InvoiceNumber] [nvarchar](25) NOT NULL,
[TableName] [nchar](8) NOT NULL,
[FinYear] [nchar](4) NOT NULL,
CONSTRAINT [SYFF02161] PRIMARY KEY CLUSTERED
(
[TransactionNumber] ASC,
[SupplierCode] ASC,
[InvoiceNumber] ASC,
[TableName] ASC,
[FinYear] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
declare @FinYear nchar(4)
declare @sql nvarchar(1500)
DECLARE CC_cursor CURSOR FOR
SELECT
[Year]
FROM [ScaCompanyYear] (nolock)
where CompanyCode=@CC
OPEN CC_cursor
FETCH NEXT FROM CC_cursor INTO
@FinYear
IF @@FETCH_STATUS <> 0
PRINT ‘ <<None>>’
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=’select distinct rtrim(GL06002), rtrim(GL06006), rtrim(GL06007), »GL06’+@CC+right(@FinYear,2)+»’, »’+@FinYear+»’ from GL06’+@CC+right(@FinYear,2)+’ (nolock) where char(GL06012) in(»0»,»5»,»6»)’
insert into #TransactionNumbers
exec(@sql)
set @sql=’select distinct rtrim(GL07002), rtrim(GL07012), rtrim(GL07013), »GL07’+@CC+right(@FinYear,2)+»’, »’+@FinYear+»’ from GL07’+@CC+right(@FinYear,2)+’ (nolock) where GL07021 in(»0»,»6»)’
insert into #TransactionNumbers
exec(@sql)
set @sql=’select distinct rtrim(PL04002), rtrim(PL04012), rtrim(PL04013), »PL04’+@CC+right(@FinYear,2)+»’, »’+@FinYear+»’ from PL04’+@CC+right(@FinYear,2)+’ (nolock)’
insert into #TransactionNumbers
exec(@sql)
set @sql=’select distinct rtrim(PL05002), rtrim(PL05012), rtrim(PL05013), »PL05’+@CC+right(@FinYear,2)+»’, »’+@FinYear+»’ from PL05’+@CC+right(@FinYear,2)+’ (nolock)’
insert into #TransactionNumbers
exec(@sql)
FETCH NEXT FROM CC_cursor INTO
@FinYear
END
CLOSE CC_cursor
DEALLOCATE CC_cursor
set @sql=’select
»Invoice» as RecordType,
PL03001 as SupplierCode,
PL03002 as InvoiceNumber,
PL03003 as TransactionNumber,
PL03005 as BE_Date,
PL03014 as InvoiceAmount,
SYCD009 as CurrencyCode,
PL03024 as Text
from PL03’+@CC+’00 (nolock)
join SYCD’+@CC+’00 (nolock)
on SYCD001=PL03015
left join #TransactionNumbers
on rtrim(PL03003)=[TransactionNumber] COLLATE DATABASE_DEFAULT
and rtrim(PL03001)=[SupplierCode] COLLATE DATABASE_DEFAULT
and rtrim(PL03002)=[InvoiceNumber] COLLATE DATABASE_DEFAULT
where [TransactionNumber] is null and PL03014<>0
union all
select
»Payment» as RecordType,
PL21001 as SupplierCode,
PL21002 as InvoiceNumber,
PL21005 as TransactionNumber,
PL21007 as BE_Date,
PL21009 as InvoiceAmount,
SYCD009 as CurrencyCode,
PL21020 as Text
from PL21’+@CC+’00 (nolock)
join SYCD’+@CC+’00 (nolock)
on SYCD001=PL21015
left join #TransactionNumbers
on rtrim(PL21005)=[TransactionNumber] COLLATE DATABASE_DEFAULT
and rtrim(PL21001)=[SupplierCode] COLLATE DATABASE_DEFAULT
and rtrim(PL21002)=[InvoiceNumber] COLLATE DATABASE_DEFAULT
where [TransactionNumber] is null
Order by SupplierCode, BE_Date, InvoiceNumber, RecordType’
exec(@sql)
drop table #TransactionNumbers |