IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_SOP10100_INS_AA') begin
DROP proc dd_SOP10100_INS_AA
end
GO
CREATE proc dd_SOP10100_INS_AA
-- dd_SOP10100_INS_AA 'TTA0032309',3,1
@sopnumbe varchar(21),
@soptype int,
@force bit
AS
set transaction isolation level read uncommitted
set nocount on
if @force = 0 begin
if exists (select 1 from aag20000 where DOCNUMBR = @sopnumbe and DOCTYPE = @soptype) begin
select 'Existing Transactions' as Result
end
end
declare @aaSubLedgerHdrID int,
@custnmbr varchar(15),
@custname varchar(65),
@slprsnid varchar(15),
@JobTypeID int,
@SalespersonTypeID int,
@TypeTypeID int
declare @jobnumber as varchar(21)
select @custnmbr = custnmbr,
@custname = custname,
@jobnumber = ORIGNUMB,
@slprsnid = SLPRSNID
from sop10100 h
where h.SOPNUMBE = @sopnumbe and h.soptype = @soptype
--============================================================================================================
--delete existing transactions
--leave the AAG20001 line, if it exists
--============================================================================================================
if exists (select 1 from aag20000 where DOCNUMBR = @sopnumbe and DOCTYPE = @soptype) begin
select @aaSubLedgerHdrID = aaSubLedgerHdrID
from aag20000
where DOCNUMBR = @sopnumbe and DOCTYPE = @soptype
delete aag20001 where aaSubLedgerHdrID = @aaSubLedgerHdrID
delete aag20002 where aaSubLedgerHdrID = @aaSubLedgerHdrID
delete aag20003 where aaSubLedgerHdrID = @aaSubLedgerHdrID
end else begin
--get the next subledgerHdrID
select @aaSubLedgerHdrID = max(aaSubLedgerHdrID) + 1 from AAG20000
--create a new header line
insert into aag20000 (aaSubLedgerHdrID , SERIES, DOCTYPE , DOCNUMBR , Master_ID, PYRNTYPE)
values (@aaSubLedgerHdrID, 11 , @soptype, @sopnumbe, '' , 0 )
end
--============================================================================================================
--create lines in AAG20001
--we need one line for each distribution,
--and lines for the COGS account
--============================================================================================================
declare @InsertCount int
insert into aag20001 (aaSubLedgerHdrID, INTERID , ACTINDX , DISTTYPE , ACCTTYPE, aaBrowseType , DECPLACS, DEBITAMT , CRDTAMNT , ORDBTAMT , ORCRDAMT , aaCustID , aaCopyStatus, aaWinWasOpen, aaFutureDist, aaSubLedgerDistID , SEQNUMBR )
select @aaSubLedgerHdrID, DB_NAME(), dist.ACTINDX, dist.DISTTYPE, 1 , case when act.ACTINDX is null then 0 else 2 end, 2 , dist.DEBITAMT, dist.CRDTAMNT, dist.DEBITAMT, dist.CRDTAMNT, @custnmbr, 11 , 1 , 0 , ROW_NUMBER() over (order by sopnumbe), dist.SEQNUMBR
from sop10102 dist
left join aag00200 act on act.ACTINDX = dist.ACTINDX and isnull(act.aaAcctClassID,0) > 0
where dist.SOPNUMBE = @sopnumbe and dist.soptype = @soptype
select @InsertCount = @@ROWCOUNT
print convert(varchar(10),@InsertCount) + ' DIST lines inserted into aag20001'
insert into aag20001 (aaSubLedgerHdrID, INTERID , ACTINDX , DISTTYPE , ACCTTYPE, aaBrowseType , DECPLACS, DEBITAMT , CRDTAMNT , ORDBTAMT , ORCRDAMT , aaCustID , aaCopyStatus, aaWinWasOpen, aaFutureDist, aaSubLedgerDistID , SEQNUMBR )
select @aaSubLedgerHdrID, DB_NAME(), l.CSLSINDX , 14 , 0 , 2 , 0 , 0 , 0 , 0 , 0 , '' , 0 , 1 , 1 ,@InsertCount + ROW_NUMBER() over (order by sopnumbe),ROW_NUMBER() over (order by sopnumbe)
from sop10200 l
where l.SOPNUMBE = @sopnumbe and l.soptype = @soptype
group by l.CSLSINDX,l.SOPNUMBE
print convert(varchar(10),@@rowcount) + ' COGS lines inserted into aag20001'
-- dd_SOP10100_INS_AA 'TTA0032299',3,1
--============================================================================================================
--create lines in AAG20002
--one line for each line in AAG20001
--============================================================================================================
insert into aag20002 (aaSubLedgerHdrID, aaSubLedgerDistID, aaSubLedgerAssignID, DEBITAMT, CRDTAMNT, ORDBTAMT, ORCRDAMT, aaAssignedPercent)
select aaSubLedgerHdrID, aaSubLedgerDistID, 1 , DEBITAMT, CRDTAMNT, DEBITAMT, CRDTAMNT, case when aaCustID = '' then '0' else '10000' end
from aag20001
where aaSubLedgerHdrID = @aaSubLedgerHdrID
print convert(varchar(10),@@rowcount) + ' lines inserted into aag20002'
--==============================================================================================
--insert the job number
--==============================================================================================
declare @JobID int
select @JobTypeID = aaTrxDimID
from aag00400
where aaTrxDim = 'JOBCOSTING'
select @JobID = aaTrxDimCodeID
from aag00401 aa401
where aa401.aaTrxDimCode = @jobnumber and aatrxdimid = @JobTypeID
if @JobID is null begin
select @jobid = max(aaTrxDimCodeID) + 1
from aag00401 aa401
where aaTrxDimID = @JobTypeID
print 'job not found in AAG00401, inserting @jobid'
insert into aag00401( aaTrxDimID, aaTrxDimCodeID, aaTrxDimCode, aaTrxDimCodeDescr, aaTrxDimCodeDescr2)
select @JobTypeID, @JobID , @jobnumber , @custname , ''
end
insert into aag20003 (aaSubLedgerHdrID , aaSubLedgerDistID , aaSubLedgerAssignID, aaTrxDimID, aaTrxCodeID)
select @aaSubLedgerHdrID, aa2.aaSubLedgerDistID, 1 , @JobTypeID, @JobID
from aag20002 aa2
join aag20001 aa1 on aa1.aaSubLedgerHdrID = aa2.aaSubLedgerHdrID and aa1.aaSubLedgerDistID = aa2.aaSubLedgerDistID
--remove the accounts that are not eligible
join aag00200 aa200 on aa200.ACTINDX = aa1.ACTINDX and aa200.aaAcctClassID = 4
where aa2.aaSubLedgerHdrID = @aaSubLedgerHdrID
print convert(varchar(10),@@rowcount) + ' job lines inserted into aag20003 for ' + @jobnumber
--==============================================================================================
--salesperson
--==============================================================================================
declare @SalespersonID int
select @SalespersonTypeID = aaTrxDimID
from aag00400
where aaTrxDim = 'SALESPERSON ID'
-- dd_SOP10100_INS_AA 'TTA0032299',3,1
select @SalespersonID = aaTrxDimCodeID
from aag00401 aa401
where aa401.aaTrxDimCode = @slprsnid and aatrxdimid = @SalespersonTypeID
if @SalespersonID is null begin
select @SalespersonID = max(aaTrxDimCodeID) + 1
from aag00401 aa401
where aaTrxDimID = 3
print 'inserting @SalespersonID into aag00401'
insert into aag00401( aaTrxDimID , aaTrxDimCodeID, aaTrxDimCode, aaTrxDimCodeDescr, aaTrxDimCodeDescr2)
select @SalespersonTypeID ,@SalespersonID, @slprsnid , @slprsnid , ''
end
print 'inserting @SalespersonID into aag20003'
insert into aag20003 (aaSubLedgerHdrID , aaSubLedgerDistID , aaSubLedgerAssignID, aaTrxDimID , aaTrxCodeID)
select @aaSubLedgerHdrID, aa2.aaSubLedgerDistID, 1 , @SalespersonTypeID, @SalespersonID
from aag20002 aa2
join aag20001 aa1 on aa1.aaSubLedgerHdrID = aa2.aaSubLedgerHdrID and aa1.aaSubLedgerDistID = aa2.aaSubLedgerDistID
--remove the accounts that are not eligible
join aag00200 aa200 on aa200.ACTINDX = aa1.ACTINDX and aa200.aaAcctClassID = 4
where aa2.aaSubLedgerHdrID = @aaSubLedgerHdrID
--==============================================================================================
--insert the type
--==============================================================================================
declare @DefaultTypeID int
select @TypeTypeID = aaTrxDimID
from aag00400
where aaTrxDim = 'TYPE'
select @DefaultTypeID = aaTrxDimCodeID
from aag00401
where aaTrxDimCodeDescr in ('na') and aatrxdimid = 4
declare @FreightTypeID int
select @FreightTypeID = aaTrxDimCodeID
from aag00401 where aaTrxDimCodeDescr in ('freight') and aatrxdimid = 4
declare @CCFeeID int
select @CCFeeID = aaTrxDimCodeID
from aag00401 where aaTrxDimCodeDescr in ('cc fee') and aatrxdimid = 4
declare @type table (aaTrxDimCodeID int, actindx int )
insert into @type (aaTrxDimCodeID, actindx) select @FreightTypeID, ACTINDX from gl00105 where actnumst = '5250-000'
insert into @type (aaTrxDimCodeID, actindx) select @CCFeeID , ACTINDX from gl00105 where actnumst = '5510-200'
print 'inserting TYPE into aag20003'
insert into aag20003 (aaSubLedgerHdrID , aaSubLedgerDistID , aaSubLedgerAssignID, aaTrxDimID , aaTrxCodeID)
select @aaSubLedgerHdrID, aa2.aaSubLedgerDistID, 1 , @TypeTypeID, isnull(t.aaTrxDimCodeID,@defaultTypeID)
from aag20002 aa2
join aag20001 aa1 on aa1.aaSubLedgerHdrID = aa2.aaSubLedgerHdrID and aa1.aaSubLedgerDistID = aa2.aaSubLedgerDistID
--remove the accounts that are not eligible
join aag00200 aa200 on aa200.ACTINDX = aa1.ACTINDX and aa200.aaAcctClassID = 4
left join @type t on t.actindx = aa1.ACTINDX
where aa2.aaSubLedgerHdrID = @aaSubLedgerHdrID
/*
select * from aag20000 where DOCNUMBR = 'TTA0032299'
select * from sop10102 where sopnumbe = 'TTA0032299'
select * from aag20001 where aaSubLedgerHdrID = 227995
select * from aag20002 where aaSubLedgerHdrID = 227995
select * from aag20003 where aaSubLedgerHdrID = 227995
select * from aag00401
select * from sop10102 where sopnumbe = 'TTA0032299'
select * from sop10102 where sopnumbe = 'TTA0032300'
select * from sop10105 where sopnumbe = 'TTA0032300'
select * from gl00105 where actindx in (2,34,40)
-- dd_SOP10100_INS_AA 'TTA0032299',3,1
*/
GO
GRANT EXEC ON dd_SOP10100_INS_AA TO PUBLIC