November 7, 2006 at 10:49 am
Sorry for such a trivial question! I'm a hardware tech who has been appointed DBA. We're running SQL Server 2000. Our software vendor has e-mailed me a stored procedure that a staff member requested. I am to "install" it in the proper database. How is that done? The vendor is no help, and my pleas for training are falling on deaf ears. Thanks!!!
November 7, 2006 at 12:06 pm
Can you post the exact code he sent you?
November 7, 2006 at 1:46 pm
Here it is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AAA_CP_CustomDataExport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AAA_CP_CustomDataExport]
GO
--exec AAA_CP_CustomDataExport NULL, '01/01/06', '01/31/06'
CREATE PROCEDURE AAA_CP_CustomDataExport
@PlanID varchar(5) = NULL,
@BeginIncurredDate datetime = NULL,
@EndIncurredDate datetime = NULL
/************************************************************************/
/* Stored Procedure: AAA_CP_CustomDataExport */
/* */
/* Purpose: This procedure returns a custom data extract that can be */
/* copied from query analyzer into Excel for further analyis. */
/* */
/* Input Parameters: */
/* @PlanID */
/* @BeginIncurredDate */
/* @EndIncurredDate */
/* */
/* Output Parameters: None */
/* */
/* Return Value: None */
/* */
/* Usage: */
/* exec dbo.AAA_CP_CustomDataExport */
/* @PlanID = 'ARKP', */
/* @BeginIncurredDate = '01/01/2005', */
/* @EndIncurredDate = '01/31/2005' */
/* */
/* Called By: application */
/* */
/* Calls: None */
/* */
/* Data Modifications: None */
/* */
/* Updates: */
/* Date Author Purpose */
/* 07/06/2006 Chris P. Smith Initial Coding */
/* */
/************************************************************************/
AS
DECLARE
@IncurredDateRecordID integer,
@ProcessCompleteKey integer,
@ServiceLineTypeRecordID integer,
@PlanFinancialPartyRecordID integer,
@PrimaryEnrolleeTypeRecordID integer,
@DefaultStartDate datetime,
@DefaultEndDate datetime;
DECLARE @Plans TABLE (
PLPlanKey integer PRIMARY KEY CLUSTERED
);
DECLARE @ClaimsToView TABLE (
CPHeaderKey integer,
VersionNumber integer,
PBEntityKey integer,
IncurredDate datetime,
PRIMARY KEY CLUSTERED (CPHeaderKey)
);
DECLARE @Claims TABLE (
CPHeaderKey integer,
VersionNumber integer,
BenefitsAssignedFlag bit,
CPLineItemKey integer,
FamilyID varchar(16),
SequenceNumber integer,
Primary_FirstName varchar(25),
Primary_MiddleName varchar(25),
Primary_LastName varchar(50),
Patient_FirstName varchar(25),
Patient_MiddleName varchar(25),
Patient_LastName varchar(50),
Patient_Gender varchar(25),
Patient_Age integer,
Patient_DOB datetime,
Primary_MemberClass varchar(50),
Primary_City varchar(30),
Primary_State varchar(2),
Primary_Zip varchar(9),
Provider_TIN varchar(9),
Provider_Name varchar(50),
Provider_City varchar(30),
Provider_State varchar(2),
Provider_LastChangedBy varchar(50),
Profider_LastChanged datetime,
BilledAmount money,
CoverageType varchar(30),
PlanSelected varchar(20),
NumberOfUnits integer,
UnitsDescription varchar(50),
IncurredDate datetime,
StartOfService datetime,
EndOfService datetime,
PRIMARY KEY CLUSTERED (CPHeaderKey, CPLineItemKey)
);
DECLARE @ClaimDXCodes TABLE (
CPHeaderKey integer,
SequenceNumber integer,
DXCode varchar(48),
DXDescription varchar(100),
PRIMARY KEY CLUSTERED (CPHeaderKey, SequenceNumber)
);
DECLARE @ClaimServiceCodes TABLE (
CPHeaderKey integer,
CPLineItemKey integer,
ServiceCode varchar(48),
ServiceCodeDescription varchar(100),
BenefitCode varchar(5),
BenefitCodeDescription varchar(20),
PRIMARY KEY CLUSTERED (CPHeaderKey, CPLineItemKey)
);
DECLARE @ClaimFinancials TABLE (
CPHeaderKey integer,
CPLineItemKey integer,
ChargeAmt money,
CoveredAmt money,
NotAllowedMemberAmt money,
NotAllowedProviderAmt money,
PayAmt money,
DeductibleAmount money,
PRIMARY KEY CLUSTERED (CPHeaderKey, CPLineItemKey)
);
DECLARE @IncurredDates TABLE (
CPHeaderKey integer,
VersionNumber integer,
EventDate datetime,
PRIMARY KEY CLUSTERED (CPHeaderKey, VersionNumber)
);
SET NOCOUNT ON;
/*
** Variable initialization.
*/
SET @DefaultStartDate = '01/01/1753';
SET @DefaultEndDate = '01/01/3000';
SET @IncurredDateRecordID = dbo.GEN_RecordID(53, 2);
SET @ProcessCompleteKey = dbo.GEN_RecordID(51, 32768);
SET @ServiceLineTypeRecordID = dbo.GEN_RecordID(58, 1);
SET @PlanFinancialPartyRecordID = dbo.GEN_RecordID(54, 1);
SET @PrimaryEnrolleeTypeRecordID = dbo.GEN_RecordID(9, 1);
SET @BeginIncurredDate = CASE WHEN (@BeginIncurredDate IS NULL) OR (@BeginIncurredDate = '') THEN @DefaultStartDate ELSE @BeginIncurredDate END;
SET @EndIncurredDate = CASE WHEN (@EndIncurredDate IS NULL) OR (@EndIncurredDate = '') THEN @DefaultEndDate ELSE @EndIncurredDate END;
/*
** If we were passed a plan id then we'll limit our results by it, otherwise we'll specify that all
** the plans should be included.
*/
IF RTRIM(ISNULL(@PlanID, '')) = ''
INSERT INTO @Plans (
PLPlanKey
)
SELECT
PLPlanKey
FROM
dbo.PLPlanHeader;
ELSE
INSERT INTO @Plans (
PLPlanKey
)
SELECT
PLPlanKey
FROM
dbo.PLPlanHeader
WHERE
PlanID = @PlanID;
INSERT INTO @IncurredDates (CPHeaderKey, VersionNumber, EventDate)
SELECT
hd.CPHeaderKey, hd.VersionNumber, hd.EventDate
FROM
dbo.CPHeaderDate hd
WHERE
hd.EventRecordID_G51_53 = @IncurredDateRecordID
ANDhd.EventDate >= @BeginIncurredDate
ANDhd.EventDate <= @EndIncurredDate;
/*
** Get the listing of claims to be exported.
*/
INSERT INTO @ClaimsToView (
CPHeaderKey, PBEntityKey, VersionNumber, IncurredDate
)
SELECT
h.CPHeaderKey, h.PBEntityKey, h.VersionNumber, hd.EventDate
FROM
dbo.CPHeader h
INNER JOIN @IncurredDates hd ON
h.CPHeaderKey = hd.CPHeaderKey
ANDh.VersionNumber = hd.VersionNumber
WHERE
h.ComputedStatusRecordId_G51 = @ProcessCompleteKey
/*
** Get the base claim information.
*/
INSERT INTO @Claims(
CPHeaderKey, VersionNumber, BenefitsAssignedFlag, CPLineItemKey, FamilyID, SequenceNumber, Primary_FirstName, Primary_MiddleName,
Primary_LastName, Patient_FirstName, Patient_MiddleName, Patient_LastName, Patient_Gender, Patient_Age, Patient_DOB,
Primary_MemberClass, Primary_City, Primary_State, Primary_Zip, Provider_TIN, Provider_Name, Provider_City,
Provider_State, Provider_LastChangedBy, Profider_LastChanged, BilledAmount, CoverageType, PlanSelected,
NumberOfUnits, UnitsDescription, IncurredDate, StartOfService, EndOfService
)
SELECT
ctv.CPHeaderKey, ctv.VersionNumber, c.BenefitsAssignedFlag, li.CPLineItemKey, pam.FamilyID, pam.SequenceNumber, prie.FirstName, prie.MiddleName, prie.LastName + ISNULL(prig4.ShortDesc, '') AS LastName,
pate.FirstName, pate.MiddleName, pate.LastName + ISNULL(patg4.ShortDesc, '') AS LastName, path2.Description,
dbo.Age(pam.BirthDate, ctv.IncurredDate), pam.BirthDate, priecd.MbrClass, priea.City, priea.State, LEFT(priea.zip, 5) AS Zip,
prv.TaxPayerIdentificationNumber, prve.LastName AS Provider_Name,
prvea.City AS Provider_City, prvea.State AS Provider_State, prve.WhoLastChanged, prve.WhenLastChanged,
c.TotalAmountOfClaim, cth.CovType + ' - ' + cth.ShortDesc, ph.ShortDesc, li.Quantity AS Number_Of_Units, h41.Description AS Unit_Description,
ctv.IncurredDate, li.StartOfService, li.EndOfService
FROM
@ClaimsToView ctv
INNER JOIN dbo.CPClaim c ON
ctv.CPHeaderKey = c.CPHeaderKey
INNER JOIN dbo.PBEntityClaimsData ecd ON
c.PatientMBMemberKey = ecd.MBMemberKey
INNER JOIN dbo.PBMember pam ON
ecd.PBEntityKey = pam.PBEntityKey
INNER JOIN dbo.PBMember prim ON
pam.FamilyID = prim.FamilyID
ANDpam.CompanyPBEntityKey = prim.CompanyPBEntityKey
ANDprim.EnrolleeTypeKey = @PrimaryEnrolleeTypeRecordID
INNER JOIN dbo.PBEntity prie ON
prim.PBEntityKey = prie.PBEntityKey
LEFT OUTER JOIN dbo.GEN_LookupTables prig4 ON
prie.SuffixID = prig4.RecordID
INNER JOIN dbo.PBEntity pate ON
ecd.PBEntityKey = pate.PBEntityKey
LEFT OUTER JOIN dbo.GEN_LookupTables patg4 ON
pate.SuffixID = patg4.RecordID
LEFT OUTER JOIN dbo.GEN_HIPAA_LookupTables path2 ON
pam.SexKey = path2.RecordID
LEFT OUTER JOIN dbo.PBEntityClaimsData priecd ON
prie.PBEntityKey = priecd.PBEntityKey
ANDpriecd.PLPlanKey = ecd.PLPlanKey
LEFT OUTER JOIN dbo.PBEntityAddress priea ON
priea.PBEntityAddressKey = dbo.PBGetEntityAddressKey(prie.PBEntityKey, 1)
INNER JOIN dbo.CPProvider prv ON
c.ServiceProviderPBEntityKey = prv.PBEntityKey
INNER JOIN dbo.PBEntity prve ON
prv.PBEntityKey = prve.PBEntityKey
LEFT OUTER JOIN dbo.PBEntityAddress prvea ON
prvea.PBEntityAddressKey = dbo.PBGetEntityAddressKey(prv.PBEntityKey, 1)
INNER JOIN dbo.PLCovTypeHeader cth ON
c.PLCovTypeHdrKey = cth.PLCovTypeHdrKey
INNER JOIN dbo.PLPlanHeader ph ON
cth.PLPlanKey = ph.PLPlanKey
INNER JOIN @Plans ptv ON
ph.PLPlanKey = ptv.PLPlanKey
INNER JOIN dbo.CPLineItem li ON
ctv.CPHeaderKey = li.CPHeaderKey
ANDli.LineItemTypeRecordID_G58 = @ServiceLineTypeRecordID
INNER JOIN dbo.GEN_HIPAA_LookupTables h41 ON
li.QuantityUOM_RecordID_H41 = h41.RecordID;
/*
** Get summary financial information for the claims.
*/
INSERT INTO @ClaimFinancials (
CPHeaderKey, CPLineItemKey, PayAmt, CoveredAmt, ChargeAmt, NotAllowedProviderAmt, NotAllowedMemberAmt, DeductibleAmount
)
SELECT
a.CPHeaderKey, b.CPLineItemKey,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID & 0x0F00) < 0x0531) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS PayAmt,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND (d.ValueID < 0x0400) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS CoveredAmt,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID <= 0x0102)) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS ChargeAmt,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID) = 0x0202) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS NotAllowedProvider,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID) = 0x0201) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS NotAllowedMember
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID) = 0x0405) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
) -- AS DeductibleAmount
FROM
@ClaimsToView a
INNER JOIN CPLineItem b ON
b.CPHeaderKey = a.CPHeaderKey
ANDb.LineItemTypeRecordID_G58 = @ServiceLineTypeRecordID
INNER JOIN CPLineItemAmount c ON
c.CPLineItemKey = b.CPLineItemKey
INNER JOIN GEN_LookupTables d ON
d.RecordID = c.FinancialActivityRecordID_G55
INNER JOIN CPFinancialActivityParticipant e ON
e.FinancialActivityRecordID_G55 = d.RecordID
ANDe.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID
GROUP BY
a.CPHeaderKey, b.CPLineItemKey;
/*
** Get the diagnosis codes associated with the claims.
*/
INSERT INTO @ClaimDXCodes(
CPHeaderKey, SequenceNumber, DXCode, DXDescription
)
SELECT
cdc.CPHeaderKey, cdc.SequenceNumber, dcl.IndustryStandardCode, dcl.Description
FROM
@ClaimsToView c
INNER JOIN CPClaimDiagnosisCode cdc ON
c.CPHeaderKey = cdc.CPHeaderKey
INNER JOIN GEN_CP_LookupTables dcl ON
dcl.RecordID = cdc.DiagnosisCodeRecordID_C6
/*
** Get the service and benefit codes for the line items.
*/
INSERT INTO @ClaimServiceCodes (
CPHeaderKey, CPLineItemKey, ServiceCode, ServiceCodeDescription, BenefitCode, BenefitCodeDescription
)
SELECT
c.CPHeaderKey, c.CPLineItemKey, csc.IndustryStandardCode, csc.Description,
bch.BenCode, bch.ShortDesc
FROM
@Claims c
INNER JOIN CPLineItemClaimAdjudicationData cad ON
cad.CPLineItemKey = c.CPLineItemKey
INNER JOIN GEN_CP_LookupTables csc ON
csc.RecordID = cad.CurrentServiceCodeRecordID_C4
LEFT OUTER JOIN PLBenCodeHeader bch ON
bch.PLBenCodeHdrKey = cad.PLBenCodeHdrKey;
/*
** Turn NOCOUNT off so that the user will be able to clearly see how many rows came back.
*/
SET NOCOUNT OFF;
/*
** Return the final recordset.
*/
SELECT
RIGHT('00000000' + CONVERT(varchar, c.CPHeaderKey), 8) + '-' + RIGHT('00' + CONVERT(varchar, c.VersionNumber), 2) AS [ClaimNumber],
c.IncurredDate, c.FamilyID + '-' + RIGHT('00' + CONVERT(varchar, c.SequenceNumber), 2) AS [Patient_ID], c.Patient_DOB, c.Patient_Gender,
c.PlanSelected, c.Provider_TIN, c.Provider_Name, c.Provider_City, c.Provider_State,
ISNULL(DXCode_1, '') AS DXCode_1, ISNULL(DXDescription_1, '') AS DXDescription_1,
ISNULL(DXCode_2, '') AS DXCode_2, ISNULL(DXDescription_2, '') AS DXDescription_2,
ISNULL(DXCode_3, '') AS DXCode_3, ISNULL(DXDescription_3, '') AS DXDescription_3,
ISNULL(DXCode_4, '') AS DXCode_4, ISNULL(DXDescription_4, '') AS DXDescription_4,
ISNULL(DXCode_5, '') AS DXCode_5, ISNULL(DXDescription_5, '') AS DXDescription_5,
ISNULL(DXCode_6, '') AS DXCode_6, ISNULL(DXDescription_6, '') AS DXDescription_6,
ISNULL(DXCode_7, '') AS DXCode_7, ISNULL(DXDescription_7, '') AS DXDescription_7,
ISNULL(DXCode_8, '') AS DXCode_8, ISNULL(DXDescription_8, '') AS DXDescription_8,
c.StartOfService, c.EndOfService,
ISNULL(csc.ServiceCode, '') AS ServiceCode, ISNULL(csc.ServiceCodeDescription, '') AS ServiceCodeDescription,
ISNULL(csc.BenefitCode, '') AS BeneiftCode, ISNULL(csc.BenefitCodeDescription, '') AS BenefitCodeDescription,
c.NumberOfUnits, c.UnitsDescription,
ISNULL(cf.ChargeAmt, 0) AS ChargedAmount, ISNULL(cf.CoveredAmt, 0) AS CoveredAmount, ISNULL(cf.PayAmt, 0) AS PayAmount,
ISNULL(cf.NotAllowedProviderAmt, 0) AS NotAllowedProviderAmount,
ISNULL(cf.NotAllowedMemberAmt, 0) AS NotAllowedMemberAmount,
ISNULL(cf.DeductibleAmount, 0) AS DeductibleAmount,
CASE
WHEN c.BenefitsAssignedFlag = 1 THEN 'Yes'
ELSE 'No'
END AS [BenefitsAssignedIndicator]
FROM
@Claims c
LEFT OUTER JOIN @ClaimServiceCodes csc ON
c.CPHeaderKey = csc.CPHeaderKey
ANDc.CPLineItemKey = csc.CPLineItemKey
LEFT OUTER JOIN @ClaimFinancials cf ON
c.CPHeaderKey = cf.CPHeaderKey
ANDc.CPLineItemKey = cf.CPLineItemKey
LEFT OUTER JOIN (
SELECT
CPHeaderKey,
MAX(
CASE
WHEN SequenceNumber = 1 THEN DXCode
ELSE ''
END
) AS DXCode_1,
MAX(
CASE
WHEN SequenceNumber = 1 THEN DXDescription
ELSE ''
END
) AS DXDescription_1,
MAX(
CASE
WHEN SequenceNumber = 2 THEN DXCode
ELSE ''
END
) AS DXCode_2,
MAX(
CASE
WHEN SequenceNumber = 2 THEN DXDescription
ELSE ''
END
) AS DXDescription_2,
MAX(
CASE
WHEN SequenceNumber = 3 THEN DXCode
ELSE ''
END
) AS DXCode_3,
MAX(
CASE
WHEN SequenceNumber = 3 THEN DXDescription
ELSE ''
END
) AS DXDescription_3,
MAX(
CASE
WHEN SequenceNumber = 4 THEN DXCode
ELSE ''
END
) AS DXCode_4,
MAX(
CASE
WHEN SequenceNumber = 4 THEN DXDescription
ELSE ''
END
) AS DXDescription_4,
MAX(
CASE
WHEN SequenceNumber = 5 THEN DXCode
ELSE ''
END
) AS DXCode_5,
MAX(
CASE
WHEN SequenceNumber = 5 THEN DXDescription
ELSE ''
END
) AS DXDescription_5,
MAX(
CASE
WHEN SequenceNumber = 6 THEN DXCode
ELSE ''
END
) AS DXCode_6,
MAX(
CASE
WHEN SequenceNumber = 6 THEN DXDescription
ELSE ''
END
) AS DXDescription_6,
MAX(
CASE
WHEN SequenceNumber = 7 THEN DXCode
ELSE ''
END
) AS DXCode_7,
MAX(
CASE
WHEN SequenceNumber = 7 THEN DXDescription
ELSE ''
END
) AS DXDescription_7,
MAX(
CASE
WHEN SequenceNumber = 8 THEN DXCode
ELSE ''
END
) AS DXCode_8,
MAX(
CASE
WHEN SequenceNumber = 8 THEN DXDescription
ELSE ''
END
) AS DXDescription_8
FROM
@ClaimDXCodes
GROUP BY
CPHeaderKey
) dxc ON
c.CPHeaderKey = dxc.CPHeaderKey
ORDER BY
c.FamilyID, c.SequenceNumber, c.IncurredDate;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
November 7, 2006 at 1:47 pm
If it is regular stored procedure (a readable file):
1. Open your Query Analyzer;
2. Point to your desired databse;
3. Copy the code on the code window;
4. Run it.
If it is an extended stored procedure (DLL file):
1. Copy the file to ...\MS SQL Server\...\Bin folder;
2. Open your Query Analyzer;
3. Point to your desired database;
4. Run sp_addextendedproc to register (reference online help for its use).
November 7, 2006 at 1:48 pm
Your stored procedure is a regular one!
November 7, 2006 at 1:59 pm
Duh. I should have thought of that! Another question though. This is supposed to dump data I can use to create a report. Where is that data dump? Sorry to be such a pain. Like I say, I'm a hardware guy.
November 7, 2006 at 2:04 pm
I need to have more information to answer your last question.
November 7, 2006 at 2:06 pm
No problem. Thanks for the help! I'm making the vendor deal with it.
November 7, 2006 at 2:06 pm
Ya, that's a question that would be best answered by the vendor.
November 7, 2006 at 3:21 pm
Currently the output is intended to go to Query Analyzer.
/* Purpose: This procedure returns a custom data extract that can be */
/* copied from query analyzer into Excel for further analyis. */
It appears the vendor intends you to run this from QA and then copy the results into Excel.
It even shows how to run the script:
Usage: */
/* exec dbo.AAA_CP_CustomDataExport */
/* @PlanID = 'ARKP', */
/* @BeginIncurredDate = '01/01/2005', */
/* @EndIncurredDate = '01/31/2005' */
It does appear to be intended to have a final use in an application: * Called By: application */
That app should accept the parameters from the user and then return the desired results.
-SQLBill
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy