/****** Object: StoredProcedure [dbo].[dd_DeferredPayments_INS] Script Date: 6/20/2017 1:42:19 PM ******/
ALTER procedure [dbo].[dd_DeferredPayments_INS]
-- dd_DeferredPayments_INS 'GWANYWHERE-ANNUAL','9/1/2015','TEST','00-020752'
@PP_PROFILE_NAME varchar(50),
@START_DT datetime,
@BACHNUMB varchar(15),
@CUSTNMBR varchar(15)
AS
declare @SOPNUMBE varchar(21)
declare @xtndprce NUMERIC(19,5)
declare @lnitmseq int
declare @lnitmseq_mod int = 16384
declare @soptype int
declare @NUMOFPER int
declare @OFFINDX int
declare @ACTINDX int
declare @START_DT_MOD datetime
if @PP_PROFILE_NAME = '' begin
return
end
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select h.SOPNUMBE, l.xtndprce, h.soptype, l.lnitmseq
from SOP10100 h
join sop10200 l on l.sopnumbe = h.sopnumbe and l.soptype = h.soptype
--make sure that don't insert records twice
LEFT JOIN PP000100 D ON D.PP_DOCUMENT_NUMBER = h.SOPNUMBE AND h.SOPTYPE = D.PP_RECORD_TYPE AND D.PP_MODULE = 5
where h.CUSTNMBR = @CUSTNMBR and h.BACHNUMB = @BACHNUMB AND D.PP_DOCUMENT_NUMBER IS NULL
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @SOPNUMBE, @xtndprce, @soptype, @lnitmseq
if @@fetch_status <> 0 begin
break
end
set @START_DT_MOD = @START_DT
select @NUMOFPER = NUMOFPER,
@OFFINDX = OFFINDX,
@ACTINDX = ACTINDX
from PP000010
where PP_Profile_Name = @PP_PROFILE_NAME
insert into PP000042 (PP_Module,PP_Record_Type,PPOFFSEQ ,PP_Sequencer,PP_Document_Number,PP_Profile_Name)
values (5 ,@soptype ,16384 ,@lnitmseq ,@SOPNUMBE ,@PP_PROFILE_NAME)
declare @i int = 0
declare @total as numeric(19,5) = 0
declare @amount as numeric(19,5)
--this is the amount that we insert into the table
set @amount = round(@xtndprce/@NUMOFPER,2)
while @i < @NUMOFPER begin
insert into PP000101 (PP_Module,PP_Record_Type,PP_Document_Number,PP_Sequencer ,PPOFFSEQ,VCHRNMBR,CNTRLTYP,DSTSQNUM,DSCRIPTN,GLPOSTDT ,TRXAMNT ,JRNENTRY)
values (5 ,@soptype ,@SOPNUMBE ,16384 ,16384 ,'' ,0 ,0 ,'' ,@START_DT_MOD,@amount ,0)
set @i = @i + 1
-- add up the amount that we put into the table
set @total = @total + @amount
--get the start of the next month
set @START_DT_MOD = cast(month(@START_DT_MOD) as varchar(2)) + '/1/' + cast(year(@START_DT_MOD) as varchar(4))
set @START_DT_MOD = DATEADD(month,1,@START_DT_MOD)
set @lnitmseq_mod = @lnitmseq_mod + 16384
end
--handle rounding issues
if @total <> @xtndprce begin
declare @difference numeric(19,5)
set @difference = @xtndprce - @total
declare @maxrow int
select @maxrow = (select MAX(dex_row_id)
from PP000101
where PP_Document_Number = @SOPNUMBE)
update PP000101 set trxamnt = trxamnt + @difference
where dex_row_id = @maxrow
end
declare @IntegerValue int
select @IntegerValue = case when @soptype < 5 then -1 else 1 end
insert into PP000100 (PP_Module,PP_Record_Type,PP_Document_Number,PP_Sequencer,PPOFFSEQ,VCHRNMBR,CNTRLTYP,DSTSQNUM,STRTNGDT ,ENDINGDT ,OFFACCT ,ACTINDX ,PP_Deferred_BC_Index,PP_Deferrals_Transfer_In,PP_Recognition_Index,TRXAMNT ,REFRENCE ,USERID,POSTING,IntegerValue ,PP_Calculation_Method,PP_Miscellaneous_Periods,NOTEINDX ,JRNENTRY)
values (5 ,@soptype ,@SOPNUMBE ,16384 ,16384 ,'' ,0 ,0 ,@START_DT,DATEADD(MONTH,-1,@START_DT_MOD),@OFFINDX,@ACTINDX,0 ,0 ,0 ,@xtndprce,@PP_PROFILE_NAME,'' ,0 ,@IntegerValue,2 ,0 ,0 ,0)
INSERT into PP000011 (PP_Module, PP_Record_Type, PP_Document_Number, PPOFFSEQ, PP_Sequencer, DISTTYPE, PP_Profile_Name , OFFINDX , ACTINDX , PP_Deferred_BC_Index, PP_Deferrals_Transfer_In, PP_Recognition_Index, PP_Calculation_Method, STRTNGDT , ENDINGDT , NUMOFPER , NOTEINDX, PP_Miscellaneous_Periods)
values (5 , @soptype , @SOPNUMBE , 16384 , 16384 , 2 , @PP_PROFILE_NAME, @OFFINDX, @ACTINDX, 0 , 0 , 0 , 2 , @START_DT,DATEADD(MONTH,-1,@START_DT_MOD), @NUMOFPER, 0 , 0)
END
CLOSE curName
DEALLOCATE curName
GO