SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[StockMove_rp1069_ID]
-- StockMove_rp1069_InsertData
    @syear varchar(10),
    @fp1 varchar(20),       -- from item
    @fp2 varchar(20),       -- to item
    @fp3 varchar(20),       -- from category
    @fp4 varchar(20),       -- to category
    @fp5 varchar(20),       -- from sub category
    @fp6 varchar(20),       -- to sub category
    @fp7 varchar(20),       -- from item group
    @fp8 varchar(20),       -- to item group
    @fp9 varchar(20),       -- from sub item group
    @fp10 varchar(20)       -- to sub item group

as
declare @sdate nvarchar(11)
declare @edate nvarchar(11)

declare @itemno varchar(30)
declare @desp varchar(100)
declare @category varchar(50)
declare @group varchar(50)
declare @subcategory varchar(50)
declare @subgroup varchar(50)
declare @qty int
declare @month varchar(10)
declare @year varchar(10)

set @sdate = '01/jan/' + @syear
set @edate = '31/dec/' + @syear

SET NOCOUNT ON

if object_id('GTEC1069') is not null
truncate table GTEC1069;

-->> part1: get do record
declare do_cursor cursor for
select b.itemno, c.desp1, isnull(c.category,'')category, isnull(c.igroup,'')igroup, isnull(c.subcategory,'')subcategory,
isnull(c.subgroup,'')subgroup, qty, month(dodate)do_mth, year(dodate)do_year
from dohdr a
inner join dodtl b on a.dono=b.dono
inner join item c on c.itemno=b.itemno
where dodate between @sdate and @edate
and b.itemno between @fp1 and @fp2
and isnull(c.category,'') between @fp3 and @fp4
and isnull(c.igroup,'') between @fp7 and @fp8
and isnull(c.subcategory,'') between @fp5 and @fp6
and isnull(c.subgroup,'') between @fp9 and @fp10

open do_cursor
fetch next from do_cursor into
@itemno, @desp, @category, @group, @subcategory, @subgroup, @qty, @month, @year

-- loop through the retrieve data and insert into report table
while @@FETCH_STATUS = 0
begin
    insert into GTEC1069 (itemno, desp1, category, igroup, subcategory, subgroup, qty, do_mth, do_year)
    values (@itemno, @desp, @category, @group, @subcategory, @subgroup, @qty, @month, @year)

    fetch next from do_cursor into
    @itemno, @desp, @category, @group, @subcategory, @subgroup, @qty, @month, @year
end

close do_cursor
deallocate do_cursor

-->> part2: get consign note record
declare consignnote_cursor cursor for
select b.itemno, c.desp1, isnull(c.category,'')category, isnull(c.igroup,'')igroup, isnull(c.subcategory,'')subcategory,
isnull(c.subgroup,'')subgroup, qty, month(consignnotedate)do_mth, year(consignnotedate)do_year
from consignnotehdr a
inner join consignnotedtl b on a.consignnoteno=b.consignnoteno
inner join item c on c.itemno=b.itemno
where consignnotedate between @sdate and @edate
and b.itemno between @fp1 and @fp2
and isnull(c.category,'') between @fp3 and @fp4
and isnull(c.igroup,'') between @fp7 and @fp8
and isnull(c.subcategory,'') between @fp5 and @fp6
and isnull(c.subgroup,'') between @fp9 and @fp10

open consignnote_cursor
fetch next from consignnote_cursor into
@itemno, @desp, @category, @group, @subcategory, @subgroup, @qty, @month, @year

-- loop through the retrieve data and insert into report table
while @@FETCH_STATUS = 0
begin
    insert into GTEC1069 (itemno, desp1, category, igroup, subcategory, subgroup, qty, do_mth, do_year)
    values (@itemno, @desp, @category, @group, @subcategory, @subgroup, @qty, @month, @year)

    fetch next from consignnote_cursor into
    @itemno, @desp, @category, @group, @subcategory, @subgroup, @qty, @month, @year
end

close consignnote_cursor
deallocate consignnote_cursor

SET NOCOUNT OFF
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO