SQLTeam.com | Weblogs | Forums

Need help with Stored Proc

sql2012

#1

Good Morning, I am new to SQL and i was given a stored proc, Now i need to add additional SQL to this stored proc however, it seems to be made up of tables and i am unsure how to proceed. the code i need to update

the SQL i need to add is here

SELECT     bh.billinginvoiceheaderpk, bh.chargeamount, b.billingchargecodepk, bh.invoicenumber, bh.contractfk, bh.remainingbalanceamount, bh.invoiceduedate, 
                      bh.contract_number
FROM         ch_billingchargecode AS b INNER JOIN
                 ch_billinginvoiceheader AS bh ON b.billingchargecodepk = bh.billingchargecodefk
WHERE     (b.billingchargecodeid = 'SREC SALE')

fairly easy but when i look at the stored proc so confused, If i add it at the end i am unable to still query my fields this is the stored proc any advise on how to add or modify would be appreciated thanks

/****** Object: StoredProcedure [dbo].[sp_GetARAgingCP] Script Date: 01/10/2017 11:33:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- exec sp_GetARAgingcp '6,7','N','12/31/2013'

-- select * from ch_artranstype

ALTER procedure [dbo].[sp_GetARAgingCP]

(
@corppk varchar(50),
@open_invoices_only varchar(50),
@asofdate datetime

)

as

--=================================================
create table #corppk_table (corppk int)

declare @input as varchar(1000) = @corppk
declare @current as varchar(50)
if LEN(@input)>0 and RIGHT(@input,1)<>','
set @input=@input+','

while LEN(@input)>1
begin
set @current=LEFT(@input,charindex(',',@input)-1)
insert into #corppk_table (corppk) select @current
--print 'Current:'+@current
set @input = SUBSTRING(@input,charindex(',',@input)+1,LEN(@input)-charindex(',',@input))
--print 'Input:'+@input
end
--select * from #corppk_table

declare @corpfk bigint

declare @checkdate datetime

set @checkdate = GETDATE()

create table #suspensebalances
(
corpfk bigint,
invoicenumber bigint,
nacustomerfk bigint,
-- contractfk bigint,
tosuspense [numeric](13, 2) ,
fromsuspense [numeric](13, 2) ,
suspensebalance [numeric](13, 2)
)

create table #tempnetbalances
(
corpfk bigint,
invoicenumber bigint,
recordtype varchar(20) ,
chargeamount [numeric](13, 2) ,
invoiceamount [numeric](13, 2) ,
taxamount [numeric](13, 2) ,
glpostdate datetime null ,
invoiceduedate datetime null ,
invoiceeffectivedate datetime null,
nacustomerfk bigint,
contractfk bigint,
billingchargecodefk bigint ,
lastpmtdate datetime null

  		)		

-- invoice

insert into #tempnetbalances (corpfk,invoicenumber,recordtype,chargeamount,invoiceamount,taxamount,
glpostdate,invoiceduedate,invoiceeffectivedate,nacustomerfk,contractfk,billingchargecodefk)

select

bih.corpfk,
bih.invoicenumber,
'invoice',
isnull(sum(bih.chargeamount),0) ,
isnull(sum(bih.remainingbalanceamount),0) ,
isnull(sum(bih.taxesamount),0) ,
bih.glpostdate,
bih.invoiceduedate,
bih.invoiceeffectivedate,
--ch.nacustomerfk ,
case when bih.onetimechargefk IS null then ch.nacustomerfk else otch.nacustomerfk end as nacustomerfk,
bih.contractfk,
MAX(bih.billingchargecodefk)

from ch_billinginvoiceheader bih
left outer join ch_contracts ch on ch.contractpk = bih.contractfk
left outer join ch_onetimecharges otc on otc.onetimechargepk = bih.onetimechargefk
left join ch_onetimechargesheader otch on otch.onetimechargeheaderpk = otc.onetimechargeheaderfk
join ch_corp cp on cp.corppk = bih.corpfk

where
cp.corppk in (select corppk from #corppk_table) and bih.glpostdate < = @asofdate
-- cp.corpid in (@corpid)
group by bih.corpfk,bih.invoicenumber,bih.glpostdate,bih.invoiceduedate,bih.invoiceeffectivedate,

case when bih.onetimechargefk IS null then ch.nacustomerfk else otch.nacustomerfk end ,
-- nacustomerfk,
bih.contractfk
order by bih.corpfk,bih.invoicenumber desc,bih.glpostdate

-- transactions -----------------------------------------

insert into #tempnetbalances (bih.corpfk,bih.invoicenumber,recordtype,chargeamount,
invoiceamount,taxamount,lastpmtdate,nacustomerfk,contractfk,billingchargecodefk,invoiceduedate,invoiceeffectivedate )

select

bih.corpfk,
abt.invoicenumber,
artt.artranstypeid ,
sum(isnull(-art.amount,0)),
case when artt.artranstypeid <> 'TO SUSPENSE' then sum(isnull(-art.amount,0))else 0 end ,
0 ,
abt.glpostdate,
abt.nacustomerfk,
ch.contractpk,
MAX(bih.billingchargecodefk),
bih.invoiceduedate,
bih.invoiceeffectivedate

from ch_artransdetail art
join ch_billinginvoiceheader bih on bih.billinginvoiceheaderpk = art.billinginvoiceheaderfk
join ch_arbatchdetail abt on abt.arbatchdetailpk = art.arbatchdetailfk
join ch_arbatchheader bh on bh.arbatchheaderpk = abt.arbatchheaderfk
join ch_artranstype artt on artt.artranstypepk = abt.artranstypefk
join ch_corp cp on cp.corppk = bih.corpfk
left join ch_contracts ch on ch.contractpk = bih.contractfk

where
cp.corppk in (select corppk from #corppk_table) and bh.glpostdate < = @asofdate
--cp.corpid in (@corpid)

and (abt.reversal is null or abt.reversal = 'N') and bh.arprocessstatusfk = 2 and artt.artranstypeid <> 'TO SUSPENSE'
group by bih.corpfk,
abt.invoicenumber,
artt.artranstypeid,
abt.nacustomerfk,
abt.glpostdate,
ch.contractpk,
bih.invoiceduedate,
bih.invoiceeffectivedate

--to suspense

insert into #tempnetbalances (bih.corpfk,bih.invoicenumber,recordtype,chargeamount,
invoiceamount,taxamount,lastpmtdate,nacustomerfk,contractfk,billingchargecodefk,invoiceduedate,invoiceeffectivedate )

select

bh.corpfk,
abt.invoicenumber,
artt.artranstypeid ,
sum(isnull(-art.amount,0)),
0,
-- case when artt.artranstypeid in ('TO SUSPENSE','PAY SUSPENSE') then sum(isnull(-art.amount,0))else 0 end ,
0 ,
abt.glpostdate,
abt.nacustomerfk,
null,
null,
null,
null
--bh.transdate,
--bh.transdate

from ch_artransdetail art
join ch_arbatchdetail abt on abt.arbatchdetailpk = art.arbatchdetailfk
join ch_arbatchheader bh on bh.arbatchheaderpk = abt.arbatchheaderfk
join ch_artranstype artt on artt.artranstypepk = abt.artranstypefk
join ch_corp cp on cp.corppk = bh.corpfk

where
cp.corppk in (select corppk from #corppk_table) and bh.glpostdate < = @asofdate
--cp.corpid in (@corpid)

and (abt.reversal is null or abt.reversal = 'N') and bh.arprocessstatusfk = 2 and artt.artranstypeid in ('TO SUSPENSE' ,'PAY SUSPENSE')
group by bh.corpfk,
abt.invoicenumber,
artt.artranstypeid,
abt.nacustomerfk,
abt.glpostdate,
bh.transdate

-- suspense table ----------------------------------------------------------------

insert into #suspensebalances (corpfk,invoicenumber,nacustomerfk,tosuspense,fromsuspense,suspensebalance )

select

bh.corpfk,
0,
abt.nacustomerfk,

case when artt.artranstypeid = 'TO SUSPENSE' then sum(isnull(art.amount,0))else 0 end ,
--case when artt.artranstypeid = 'FROM SUSPENSE' then sum(isnull(art.amount,0))else 0 end ,
0,
0

-- case (when artt.artranstypeid = 'TO SUSPENSE' then sum(isnull(art.amount,0))else 0 -

-- when artt.artranstypeid = 'FROM SUSPENSE' then sum(isnull(art.amount,0))else 0) end

from ch_artransdetail art
join ch_arbatchdetail abt on abt.arbatchdetailpk = art.arbatchdetailfk
join ch_arbatchheader bh on bh.arbatchheaderpk = abt.arbatchheaderfk
join ch_artranstype artt on artt.artranstypepk = abt.artranstypefk
join ch_corp cp on cp.corppk = bh.corpfk

where
cp.corppk in (select corppk from #corppk_table) and bh.glpostdate < = @asofdate
--cp.corpid in (@corpid)

and (abt.reversal is null or abt.reversal = 'N')
and bh.glpostdate is not null and artt.artranstypeid in ( 'TO SUSPENSE' )
group by bh.corpfk,abt.invoicenumber,abt.nacustomerfk,artt.artranstypeid

-- select cnam.commonname, tp.* from #suspensebalances tp
-- join ch_names_customer namc on namc.nacustomerpk = tp.nacustomerfk
--join ch_names cnam on cnam.napk = namc.nafk

---- select * from #tempnetbalances where recordtype = 'PAY SUSPENSE'

select

cp.corpid,
max(cnam.namecode)namecode,
max(cnam.companyname)companyname,
max(cnam.primaryphone)phone,
isnull(ch.contractid,'') contract_id,
isnull(max(bc.billingchargecodeid),'')invoicetype,

bih.invoicenumber,
--bih.recordtype,
MAX(bih.invoiceduedate)invoicedate,
MAX(bih.invoiceduedate)effectivedate,

isnull(SUM(case when recordtype = 'invoice' then bih.invoiceamount end),0) as invoiceamount,

isnull(SUM(case when recordtype = 'PAYMENT' then bih.invoiceamount end),0) as payments,

isnull(SUM(case when recordtype = 'credit' then bih.invoiceamount end),0) as credits,

isnull(SUM(case when recordtype = 'TO SUSPENSE' then bih.chargeamount end),0) as tosuspense,

--max(tmpsus.tosuspense)tosuspense,

isnull(SUM(case when recordtype = 'FROM SUSPENSE' then bih.invoiceamount end),0) as fromsuspense,

isnull(SUM(case when recordtype = 'PAY SUSPENSE' then bih.invoiceamount end),0) as paysuspense,

--isnull(SUM(case when recordtype not in ( 'invoice','Credits','PAYMENT''To suspense','PAY SUSPENSE') then bih.invoiceamount end),0) as other,

isnull(SUM(taxamount),0 )Taxes,
isnull(SUM(invoiceamount),0 )Rem_Due,

case when isnull(SUM(invoiceamount),0 ) = 0 then 0 else (DATEDIFF(day, bih.invoiceduedate ,@asofdate))end daysdelinquent,

isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) < 0 then isnull((invoiceamount),0 ) end),0)Future,
isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) >= 0 and DATEDIFF(day, bih.invoiceduedate,@asofdate)<= 30 then isnull((invoiceamount),0 ) end),0)[0-30],

isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) >= 31 and DATEDIFF(day, bih.invoiceduedate,@asofdate)<= 60 then isnull((invoiceamount),0 ) end),0)[31-60],

isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) >= 61 and DATEDIFF(day, bih.invoiceduedate,@asofdate)<= 90 then isnull((invoiceamount),0 ) end),0)[61-90],

isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) > 90 then isnull((invoiceamount),0 ) end),0)[90+],
MAX(lastpmtdate)lastpmtdate,
@asofdate asofdate,
conam.companyname as company,
fac.facilityname,
ct.contactname

from #tempnetbalances bih
join ch_corp cp on cp.corppk = bih.corpfk
left join ch_names_customer namc on namc.nacustomerpk = bih.nacustomerfk
left join ch_names cnam on cnam.napk = namc.nafk

left join ch_contracts ch on ch.contractpk = bih.contractfk

left join ch_names_company namco on namco.nacompanypk =ch.nacompanyfk
left join ch_names conam on conam.napk = namco.nafk
left join ch_facility_information fac on fac.contractfk = ch.contractpk
left join ch_names_contacts ct on ct.contactpk = cnam.contactfk

left join ch_billingchargecode bc on bc.billingchargecodepk = bih.billingchargecodefk
--left join #suspensebalances tmpsus on tmpsus.corpfk = bih.corpfk and tmpsus.invoicenumber = bih.invoicenumber
group by
cp.corpid,
cnam.companyname,
conam.companyname,
fac.facilityname,
ct.contactname,
bih.invoiceeffectivedate,
bih.invoiceduedate,
bih.invoicenumber,
ch.contractid,
(DATEDIFF(day, bih.invoiceduedate ,@asofdate))
--bih.recordtype

-- check to show 0 invoices or not

HAVING (

@open_invoices_only = 'Y'
AND
SUM(invoiceamount) <> 0)
OR
(
@open_invoices_only = 'N'
--AND
-- SUM(invoiceamount)<> 0
)

order by
cp.corpid,

cnam.companyname,
bih.invoiceeffectivedate desc,
bih.invoicenumber ,

bih.invoiceduedate desc
--ch.contractid,

--bih.recordtype

drop table #tempnetbalances

return 0


#2

Without knowing the business application it's pretty hard for an outsider to say where you should put your new code with any authority. Your question is really a business question, I think.

I read through the proc looking for something that might match but nothing obvious jumps out at me


#3

The question that needs to be asked is: How should your 'query' be incorporated into the results from this procedure?

What is your code supposed to add to the procedure?

This procedure is structured as:

Create Temp Tables...
Insert into temp tables
Final select from temp table and other tables

To incorporate your code you need to add your table or columns to the final query - or to one of the temp tables depending on what you are trying to accomplish.