Code:
ALTER proc [dbo].[App_ParentAccount] (@Parent char(6) ,@date DateTime , @FridayEstimate DateTime, @PaymentsReceived Bit, @End dateTime) as
DECLARE @Debtor char(6)
Declare @Name char(50)
Declare @FriEst numeric(8,2)
if @Parent = '0' or @Parent = '' Set @Debtor = NULL
else Set @Debtor = @Parent
set @FriEst = dbo.app_WeekEndEstimateRelief (@debtor,@FridayEstimate)
Declare @Table Table(
TabID int,
Company char(50),
ABN char(20),
Address char(50),
Suburb char(50),
PostCode char(20),
Phone char(15),
ParentCode char(15),
DelName char(50),
DelAddress1 char(50),
DelAddress2 char(50),
DelSuburb char(50),
DelPostCode char(20),
DateRaised DateTime,
TranDetails char(50),
TranNo integer,
Gross numeric(10,2),
Signage integer ,
Type char(20),
Amount Numeric(10,2),
CCMSPaymentType int,
CCMSPaymentRef char(15) ,
EziStatus char(5),
EziStart DateTime,
ezimax Numeric(6,2),
ProviderName char(160),
ProviderABN char(11),
ProviderID char(10),
ServiceID char(10),
FridayEstimate numeric(8,2),
CCSPay numeric(10,2),
RollbackAmount numeric (6,2),
CCMSWeekEnding DateTime
)
insert into @Table
select
0,
RTrim(sysinfo.Company_Name) ,
RTrim(sysinfo.abn),
RTrim(sysinfo.Address1) ,
RTrim(sysinfo.Suburb) ,
RTrim(sysinfo.PostCode) ,
sysinfo.Phone ,
ccparent.Parent_code ,
( Rtrim(ccParent.Guard_firstName) + ' ' + (Rtrim(ccParent.Guard_SurName)) ) ,
dbo.String_UpperLowerCase(ccParent.Guard_Address),
dbo.String_UpperLowerCase(RTrim(ccParent.Guard_Suburb)),
dbo.String_UpperLowerCase(ccparent.Guard_PostCode),
arMaster.Del_Post_Code,
(@Date-7),
'Open Balance',
0,
dbo.App_GetStatementBalance(arMaster.Code , @Date) ,
CASE
when dbo.App_GetStatementBalance(arMaster.Code , @Date) > 0 then 1
ELSE -1
end ,
'OPEN',
dbo.App_GetStatementBalance(arMaster.Code , @Date) ,
0 ,
' ',
ccparent.EziStatus,
ccparent.EziStart,
ccparent.ezimax,
ccsprovider.LegalName,
ccsprovider.abn,
ccsprovider.providerid,
ccsservice.serviceid,
@FriEst,
0,
0,
@date
from arMaster
join sysinfo on arMaster.recnum <> -1
join ccParent on ccParent.Parent_Code = @Parent
join ccsprovider on ccsprovider.[default]=sysinfo.recnum
join ccsservice on ccsprovider.providerId=ccsservice.providerid
where Code = isNULL(@Debtor,arMaster.Code)
-- set @Name = (select delname from @Table)
insert into @Table
select
artrans.recnum,
RTrim(sysinfo.Company_name) ,
RTrim(sysinfo.abn),
RTrim(sysinfo.Address1) ,
RTrim(sysinfo.Suburb) ,
RTrim(sysinfo.PostCode) ,
sysinfo.Phone,
ccParent.Parent_Code,
( Rtrim(ccParent.Guard_firstName) + ' ' + (Rtrim(ccParent.Guard_SurName)) ) ,
dbo.String_UpperLowerCase(ccParent.Guard_Address),
dbo.String_UpperLowerCase(RTrim(ccParent.Guard_Suburb)),
dbo.String_UpperLowerCase(ccparent.Guard_PostCode) ,
arTrans.Del_Post_Code,
Date_raised,
details,
tran_no,
Gross,
dbo.app_Sign(arTrans.Type) as 'Sign',
dbo.App_GetTransType(artrans.Type) as 'Type',
State_Amount * dbo.app_Sign(artrans.Type),
CCMSPayment ,
CCMSPaymentRef ,
ccparent.EziStatus,
ccparent.EziStart,
ccparent.ezimax,
ccsprovider.LegalName,
ccsprovider.abn,
ccsprovider.providerid,
ccsservice.serviceid,
@FriEst,
dbo.App_CCSPaymentOffset(artrans.DEBTOR,(artrans.DATE_RAISED-4)),
artrans.RollbackAmount,
artrans.ccmsweekending
From arTrans
join sysinfo on arTrans.Debtor <> sysinfo.recnum
join arMaster on arTrans.Debtor = arMaster.code -- central_debtor
join ccParent on ccParent.Parent_Code = @Parent
join ccsprovider on ccsprovider.[default]=sysinfo.recnum
join ccsservice on ccsprovider.providerId=ccsservice.providerid
where debtor = isNULL(@Debtor,arTrans.debtor)
and (arTrans.Date_Raised >= @Date and artrans.date_raised <= @End)
-- Select * from @Table order by delName, tabId, DateRaised
select
Company ,
ABN ,
Address ,
Suburb ,
PostCode ,
Phone ,
ParentCode ,
DelName ,
DelAddress1 ,
DelAddress2 ,
DelSuburb ,
DelPostCode ,
DateRaised ,
TranDetails ,
TranNo ,
Gross ,
Signage ,
Type ,
Amount ,
CCMSPaymentType ,
CCMSPaymentRef ,
EziStatus,
EziStart,
ezimax,
ProviderName,
ProviderABN,
ProviderID,
ServiceID,
FridayEstimate,
CCSPay,
RollbackAmount,
ccmsweekending
from @Table order by delName, DateRaised,tabId