/* Begin_Procs taRMApplyPost */
if exists (select * from sysobjects where id = object_id('dbo.taRMApplyPost')and type = 'P')
begin
drop procedure dbo.taRMApplyPost
end
go
create procedure dbo.taRMApplyPost
/*
***********************************************************************************************************************************
* (c) 2004 Microsoft Business Solutions
***********************************************************************************************************************************
*
* PROCEDURE NAME: taRMApplyPost
*
* SANSCRIPT NAME: NA
*
* PARAMETERS:
*
* DESCRIPTION: taRMApplyPost Integration Stored Procedure
*
* TABLES:
* Table Name Access
* ========== ======
*
* PROCEDURES CALLED:
*
* DATABASE: Company
*
* RETURN VALUE:
* 0 = Successful
* non-0 = Not successful
*
* REVISION HISTORY:
*
* Date Who Comments
* ------------- -------- ----------------------------------------------------------------------------------------
*
*
*********************************************************************************************************************************
*
*********************************************************************************************************************************
*/
@I_vAPTODCNM char(21), /* Apply to Doc Number (Invoice) <Required> */
@I_vAPFRDCNM char(21), /* Apply from Doc Number (CRD) <Required> */
@I_vAPPTOAMT numeric(19,5), /* Amount to apply <Required> */
@I_vAPFRDCTY integer, /* Doc type of apply from doc <Required> */
@I_vAPTODCTY integer, /* Doc type of apply to doc <Required> */
@I_vDISTKNAM numeric(19,5), /* Discount Taken: null=use existing disc.; xx(dollar amnt), use the amount even if 0 */
@I_vWROFAMNT numeric(19,5), /* Writeoff Amount */
@I_vAPPLYDATE datetime, /* Apply Date <Optional> */
@I_vGLPOSTDT datetime, /* Apply Posting Date <Optional> */
@I_vUSRDEFND1 char(50), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND2 char(50), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND3 char(50), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND4 varchar(8000), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND5 varchar(8000), /* <Developer> User Defined field - developer use only */
@O_iErrorState int output, /* Return value: 0=No Errors, 1=Error Occurred */
@oErrString varchar(255) output /* Return Error Code List */
as
set nocount on
select @O_iErrorState = 0
/* Create Custom Business Logic */
if @I_vAPPLYDATE is null begin
select @I_vAPPLYDATE = convert(date,getdate())
end
declare @paprojnumber varchar(15)
declare @PACCAA numeric(19,5)
declare @PAFAA numeric(19,5)
select @paprojnumber = paprojnumber,
@PACCAA = case when bill.PATMbillamount > 0 then @I_vAPPTOAMT else 0 end,
@PAFAA = case when bill.PAFFbillamount > 0 then @I_vAPPTOAMT else 0 end
from pa23200 bill
where PADocnumber20 = @I_vAPTODCNM
if @paprojnumber is null begin
return
end
--============================================================================================================================
-- update the pa billing document
--============================================================================================================================
update pa23100 set
APPLDAMT = isnull(apy.apptoamt,0)
from pa23100 bill
left join (
select apy.APTODCNM, sum(apptoamt) as apptoamt
from rm20201 apy
group by apy.APTODCNM
) apy on apy.APTODCNM = bill.PADocnumber20
where PADocnumber20 = @I_vAPTODCNM
update pa23200 set
APPLDAMT = isnull(apy.apptoamt,0),
curtrxam = bill.PABillingAmount - isnull(apy.apptoamt,0)
from pa23200 bill
left join (
select apy.APTODCNM, sum(apptoamt) as apptoamt
from rm20201 apy
group by apy.APTODCNM
) apy on apy.APTODCNM = bill.PADocnumber20
where PADocnumber20 = @I_vAPTODCNM
--============================================================================================================================
-- insert the apply lines
--============================================================================================================================
declare @lnitmseq int
declare @PAbllngtype int
declare @PACOSTCATID varchar(15)
declare @PACOSTDOCNO varchar(17)
declare @paapproved_billing_amou numeric(19,5)
declare @curtrxam numeric(19,5)
declare @linetype varchar(10)
insert into pa23000(DOCNUMBR ,RMDTYPAL ,PADocnumber20,CUSTNMBR ,PABILLTRXT,PAPROJNUMBER ,PACCAA ,PAFAA ,PATTA,PAWA,PA_Tax_Applied_Amount,DOCDATE ,TRXDSCRN)
select @I_vAPFRDCNM,@I_vAPFRDCTY,@I_vAPTODCNM ,bill.custnmbr,1 ,bill.PAPROJNUMBER ,@PACCAA ,@PAFAA ,0 ,0 ,0 ,@I_vAPPLYDATE,''
from PA23200 bill
where bill.PADocnumber20 = @I_vAPTODCNM
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select bd.PAbllngtype, bd.PACOSTCATID, bd.PACOSTDOCNO, bd.LNITMSEQ, bd.PAApproved_Billing_Amou, bd.CURTRXAM as paccaa, 0 as pafaa, 'PACCAA' as linetype
from pa23203 bd --billing detail
where bd.PADocnumber20 = @I_vAPTODCNM
and bd.CURTRXAM > 0
union all
select 3, bd.PAFeeID, '', bd.LNITMSEQ, bd.PAFeeAmount, 0 as paccaa, bd.CURTRXAM as pafaa, 'PCFAA' as linetype
from pa23201 bd --billing detail
where bd.PADocnumber20 = @I_vAPTODCNM
and bd.CURTRXAM > 0
order by bd.LNITMSEQ
--select top 100 * from pa23201 where padocnumber20 = '100097L-L '
--select * from pa23010 where PADocnumber20 = '100097L-L '
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @PAbllngtype, @PACOSTCATID, @PACOSTDOCNO,@lnitmseq,@paapproved_billing_amou, @paccaa, @pafaa, @LINETYPE
if @@fetch_status <> 0 begin
break
end
if @linetype = 'paccaa' begin
select @PACCAA = case when @PACCAA > @I_vAPPTOAMT then @I_vAPPTOAMT else @PACCAA end
end
if @linetype = 'pafaa' begin
select @pafaa = case when @pafaa > @I_vAPPTOAMT then @I_vAPPTOAMT else @pafaa end
end
insert into pa23010(DOCNUMBR , PADocnumber20, PABILLTRXT, PAPROJNUMBER , PASource_Type, PArecordid ,PACOSTDOCNO , LNITMSEQ ,PABillingAmount , PAFAA , PACCAA ,PAWA,PATTA,PARetainer_Fee_Amount,PA_Tax_Applied_Amount,PA_Terms_Taken_Tax_Amt,PA_Write_Off_Tax_Amount,PA_Credit_Retainer_App)
select @I_vAPFRDCNM, @I_vAPTODCNM , 1 , bill.PAPROJNUMBER, @PAbllngtype , @PACOSTCATID,@PACOSTDOCNO, @lnitmseq ,@paapproved_billing_amou, @pafaa, @paccaa,0 ,0 ,0 ,0 ,0 ,0 ,0
from PA23200 bill
where bill.PADocnumber20 = @I_vAPTODCNM
update pa23203 set CURTRXAM = curtrxam - (@paccaa + @pafaa) where PADocnumber20 = @I_vAPTODCNM and LNITMSEQ = @lnitmseq
select @I_vAPPTOAMT = @I_vAPPTOAMT - (@paccaa + @pafaa)
if @I_vAPPTOAMT = 0 begin
break
end
END
CLOSE curName
DEALLOCATE curName
--============================================================================================================================
-- update the project
--============================================================================================================================
update pa01201 set PA_Actual_Receipts_Amoun = rct.appldamt
from pa01201 pj
join (
SELECT paprojnumber, SUM(APPLDAMT) as APPLDAMT FROM PA23200 group by PAPROJNUMBER
) rct on rct.PAPROJNUMBER = pj.PAPROJNUMBER
where pj.PAPROJNUMBER = @paprojnumber
return(@O_iErrorState)
go
grant execute on dbo.taRMApplyPost to DYNGRP
go
/* End_Procs taRMApplyPost */