ALTER PROCEDURE stp
GetInvoices
@CaseStatusClosed BIGINT, --this will always be 133
@ThisRun
dt VARCHAR(50), --this will always be equalled to getdate()
@Classnm VARCHAR(50),--this will always be 'st. louis'
@FeeGroup
Client BIGINT,--this will always be 168
@FeeGroupProcessServer BIGINT,--this will always be 169
@ParentType
Company BIGINT,--this will always be 11
@ParentTypePerson BIGINT,--this will always be 12
@ServiceLine
ProcessServing BIGINT,--this will always be 1
@ServiceLine_Investigative BIGINT--this will always be 2
AS
DECLARE @Personvv BIGINT
DECLARE @Companyvv BIGINT
SELECT @Personvv = (SELECT ValidValueid FROM tblValidValue WHERE ValidValuenm = 'Person')
SELECT @Companyvv = (SELECT ValidValueid FROM tblValidValue WHERE ValidValuenm = 'Company')
-- Commented out because it should not be needed, if problems occur, uncomment the next line first
-- EXEC stpRollupInvoiceFees @CaseStatusClosed, @FeeGroupClient, @FeeGroupProcessServer
SELECT
,
Plaintiff_desc + ' Vs. ' + Defendant_desc as CaseCaption,
CASE ServiceLine_vv
WHEN @ServiceLine_Process_Serving THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE LTRIM(RTRIM(First_nm)) + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 'No Process Server' END)
ELSE ''
END AS ProcessServer,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN 'BILL'
ELSE 'INVOICE'
END AS TrnsType,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN 'Accounts Payable'
ELSE 'Accounts Receivable'
END AS TrnsAccnt,
FeeDescriptionVV.ValidValue_desc as SPLAccnt,
FeeDescriptionVV.ValidValue_nm as SPLMemo,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE RTRIM(First_nm) + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 'No Process Server' END)
ELSE (SELECT CASE Client_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE First_nm + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = Client_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = Client_pk) ELSE '' END)
END AS FeeGroup,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE RTRIM(First_nm) + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 'No Process Server' END)
ELSE (SELECT CASE Client_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE First_nm + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = Client_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = Client_pk) ELSE '' END)
END AS InvoiceToName,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT QuickBooks_flg FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT QuickBooks_flg FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 0 END)
ELSE (SELECT CASE Client_vv WHEN @ParentType_Person THEN (SELECT QuickBooks_flg FROM tbl_Person WHERE Person_id = Client_pk) WHEN @ParentType_Company THEN (SELECT QuickBooks_flg FROM tbl_Company WHERE Company_id = Client_pk) ELSE 0 END)
END AS FeeGroup_PSClientBoth,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN convert(decimal(10,2),tbl_job.ProcessServer_amt)
ELSE convert(decimal(10,2),tbl_job.Fee_amt)
END AS Amount,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN (SELECT Address_1 FROM tbl_Address WHERE Parent_vv = ProcessServer_vv and Parent_pk = ProcessServer_pk AND PrimaryAddress_flg = 1)
ELSE (SELECT Address_1 FROM tbl_Address WHERE Parent_vv = Client_vv and Parent_pk = Client_pk AND PrimaryAddress_flg = 1)
END AS Address1,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN (SELECT Address_2 FROM tbl_Address WHERE Parent_vv = ProcessServer_vv and Parent_pk = ProcessServer_pk AND PrimaryAddress_flg = 1)
ELSE (SELECT Address_2 FROM tbl_Address WHERE Parent_vv = Client_vv and Parent_pk = Client_pk AND PrimaryAddress_flg = 1)
END AS Address2,
CASE FeeGroup_vv
WHEN @FeeGroup_ProcessServer THEN (SELECT City_nm + ', ' + State_cd + ' ' + Zip_cd FROM tbl_Address WHERE Parent_vv = ProcessServer_vv and Parent_pk = ProcessServer_pk AND PrimaryAddress_flg = 1)
ELSE (SELECT City_nm + ', ' + State_cd + ' ' + Zip_cd FROM tbl_Address WHERE Parent_vv = Client_vv and Parent_pk = Client_pk AND PrimaryAddress_flg = 1)
END AS Address3,
FeeDue_amtFeeDueqty as TotalFee,
@ThisRundt as Rundt,
FeeGroupVV.ValidValuenm as FeeGroupnm,
FeeDescriptionVV.ValidValuenm as FeeDescription,
ServiceLineVV.ValidValuenm as ServiceLine,
FeeGroupVV.ValidValueid as FeeGroupid,
FeeDescriptionVV.ValidValueid as FeeDescriptionid,
ServiceLineVV.ValidValueid as ServiceLineid,
JobNavigationVV.ValidValueid as JobNavigationid,
case tbljobgroup.filenum
when '' then tbljobgroup.casenum
else tbljobgroup.filenum
end as TrnsMemo,
CASE tblPerson.Lastnm
WHEN 'Unassigned' THEN ''
ELSE tblPerson.Firstnm + ' ' + tblPerson.Lastnm
END AS Contact,
(SELECT MAX(Invoicedt) FROM tblJob) AS LastRundt,
dbo.ufgetReversedPersonCompanyName(Recipientvv, Recipientpk) Recipient,
classnm as ClassNm
FROM
tblValidValue FeeGroupVV,
tblValidValue FeeDescriptionVV,
tblValidValue ServiceLineVV,
tblValidValue JobNavigationVV,
tblServiceType,
tblJobGroup,
tblPerson,
tblJob
LEFT OUTER JOIN tblFeeDue ON tblFeeDue.Jobid = tblJob.Jobid
WHERE
tblJob.Invoicedt IS NULL AND
tblJob.CaseStatusvv = @CaseStatusClosed AND
tblJob.QuickBooksflg = 1 AND
tblFeeDue.FeeGroupvv = FeeGroupVV.ValidValueid AND
tblFeeDue.FeeDescriptionvv = FeeDescriptionVV.ValidValueid AND
tblJob.ServiceTypeid = tblServiceType.ServiceTypeid AND
tblServiceType.ServiceLinevv = ServiceLineVV.ValidValueid AND
tblJobGroup.CLientContactid = tblPerson.Personid AND
tblJob.JobGroupid = tblJobGroup.JobGroupid AND
tblJob.JobNavigationvv = JobNavigationVV.ValidValueid
ORDER BY
tblFeeDue.FeeGroupid,
tblJob.Job_id
No one has replied yet! Why not be the first?
Sign in or Join us (it's free).