-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Raditya Perdhevi
-- Create date: 2020-11-23
-- Description:	Splitting journal directly from db
-- =============================================
CREATE PROCEDURE SUP_SPLIT_DIRECT 
	-- Add the parameters for the stored procedure here
	@batch_no int, 
	@tran_no_start int = 0,
	@tran_no_end int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @batch_no_to int
begin transaction 

exec GET_NEXT_SEQUENCE_NUMBER 5,4,'MAIN',1,1,@batch_no_to output

insert into batch(batch_no, batch_type, descript, period, fiscal_yr, dr_amt, cr_amt, head_amt, ACNT_CODE, void, closed, system, dep_code, created_date)
select @batch_no_to,batch_type, descript, period, fiscal_yr, dr_amt, cr_amt, head_amt, ACNT_CODE, void, closed, system, dep_code, getdate() created_date  from batch
where batch_no = @batch_no and batch_type = 4

/*
select * from BATCH where batch_no = @batch_no and batch_type = 4
union all 
select * from BATCH where batch_no = @batch_no_to and batch_type = 4


select sum(dbt) dbt, sum(crd) crd, sum(head_amt) head_amt
from (
select (case dr_cr when 1 then tran_amt else 0 end) dbt, 
(case dr_cr when 2 then tran_amt else 0 end) crd,
tran_amt head_amt
from bch_head where batch_no = @batch_no and batch_type =4
union all 
select case dr_cr when 1 then amount else 0 end dbt,
case dr_cr when 2 then amount else 0 end crd,
amount head_amt
from bch_tran where batch_no = @batch_no and batch_type =4
) a

*/

update bch_head set batch_no = @batch_no_to where batch_no = @batch_no and batch_type = 4 and tran_no >= @tran_no_start and tran_no <= @tran_no_end

update BCH_TRAN set batch_no = @batch_no_to where batch_no = @batch_no and batch_type = 4 and tran_no >= @tran_no_start and tran_no <= @tran_no_end

update batch set dr_amt = s.dbt, cr_amt = s.crd from (
select sum(dbt) dbt, sum(crd) crd, sum(head_amt) head_amt
from (
select (case dr_cr when 1 then tran_amt else 0 end) dbt, 
(case dr_cr when 2 then tran_amt else 0 end) crd,
tran_amt head_amt
from bch_head where batch_no = @batch_no_to and batch_type =4
union all 
select case dr_cr when 1 then amount else 0 end dbt,
case dr_cr when 2 then amount else 0 end crd,
amount head_amt
from bch_tran where batch_no = @batch_no_to and batch_type =4
) a
) s
 where batch.batch_no = @batch_no_to and batch_type = 4

 update batch set dr_amt = s.dbt, cr_amt = s.crd from (
select sum(dbt) dbt, sum(crd) crd, sum(head_amt) head_amt
from (
select (case dr_cr when 1 then tran_amt else 0 end) dbt, 
(case dr_cr when 2 then tran_amt else 0 end) crd,
tran_amt head_amt
from bch_head where batch_no = @batch_no and batch_type =4
union all 
select case dr_cr when 1 then amount else 0 end dbt,
case dr_cr when 2 then amount else 0 end crd,
amount head_amt
from bch_tran where batch_no = @batch_no and batch_type =4
) a
) s
 where batch.batch_no = @batch_no and batch_type = 4
 /*
select sum(dbt) dbt, sum(crd) crd, sum(head_amt) head_amt
from (
select (case dr_cr when 1 then tran_amt else 0 end) dbt, 
(case dr_cr when 2 then tran_amt else 0 end) crd,
tran_amt head_amt
from bch_head where batch_no = @batch_no_to and batch_type =4
union all 
select case dr_cr when 1 then amount else 0 end dbt,
case dr_cr when 2 then amount else 0 end crd,
amount head_amt
from bch_tran where batch_no = @batch_no_to and batch_type =4
) a


select * from batch where batch_no = @batch_no_to
*/
declare @srcCount int 
select @srcCount  = count(*) from BCH_HEAD where batch_no = @batch_no and batch_type = 4
if @srcCount = 0 
begin
	delete from batch where batch_no = @batch_no and batch_type = 4
end
select @srcCount  = count(*) from BCH_HEAD where batch_no = @batch_no_to and batch_type = 4
if @srcCount = 0 
begin
	delete from batch where batch_no = @batch_no_to and batch_type = 4
end

commit
print @batch_no_to
--rollback
END
GO
