Hi Guys, I'm trying to reduce runtime(optimize)of the stored procedure which is mentioned below. I found the problem is with cursor using in stored procedure. Some one suggest me to use function or while loop instead of cursor. Can any one try to replace cursor with something to reduce run time. Please help me guys. Thank you.

CREATE PROCEDURE "kvarma"."FDSSP_UMC023_PHARMACY_SELECT_ORIGINAL"
(
    @p_RUN_FROM_DATE     CHAR(10),
    @p_RUN_TO_DATE       CHAR(10),
    @p_GRGR_ID           VARCHAR(100) --Version 1.11 change
)
AS                          
BEGIN
/**********************************************************************
**                       Declare local variables                     **
**********************************************************************/
DECLARE
        @l_UMUM_REF_ID                  CHAR(9)            ,
        @l_LOC_UMUM_REF_ID              CHAR(9)            ,
        @l_RunDate                      DATETIME           ,
        @l_RetCode                      INT                ,
        @l_RowCount                     INT                ,
        @d_Firstday                     DATE               ,
        @d_Lastday                      DATE               ,
        @d_RUN_FROM_DATE                DATE               ,
        @d_RUN_TO_DATE                  DATE               ,
        @l_NTNB_ID                      DATETIME           ,
        @l_COMMENTS                     CHAR(141)          ,
        @l_DRUG_REQ                     CHAR(141)          ,
        @l_LOC_NTNB_ID                  DATETIME           ,
        @l_CON_COMMENTS                 VARCHAR(500)       ,
        @l_CON_DRG_REQ                  VARCHAR(500)       ,
        @l_NTTX_SEQ_NO                  int                ,
        @msg                            VARCHAR(100)       ,
        @l_GRGR_ID                      VARCHAR(100)    --Version 1.11 change

/**********************************************************************
** Getting the First & Last day of the month.                        **
**********************************************************************/

if @p_RUN_FROM_DATE <>'00/00/0000'
   BEGIN
    SELECT @p_RUN_FROM_DATE = SUBSTRING(@p_RUN_FROM_DATE,7,4)+'-'+SUBSTRING(@p_RUN_FROM_DATE,1,2)+'-'+SUBSTRING(@p_RUN_FROM_DATE,4,2)
    SELECT @d_RUN_FROM_DATE=CONVERT(DATE,@p_RUN_FROM_DATE)
    SELECT @d_Firstday = @d_RUN_FROM_DATE
   END

if @p_RUN_TO_DATE <>'00/00/0000'
   BEGIN
    SELECT @p_RUN_TO_DATE = SUBSTRING(@p_RUN_TO_DATE,7,4)+'-'+SUBSTRING(@p_RUN_TO_DATE,1,2)+'-'+SUBSTRING(@p_RUN_TO_DATE,4,2)
    SELECT @d_RUN_TO_DATE=CONVERT(DATE,@p_RUN_TO_DATE)
    SELECT @d_Lastday = @d_RUN_TO_DATE
   END

IF ISNULL(LTRIM(@p_RUN_FROM_DATE),'00/00/0000' ) = '00/00/0000'
   AND ISNULL(LTRIM(@p_RUN_TO_DATE),'00/00/0000' ) = '00/00/0000'

BEGIN                       -- If none of the dates are given
                            -- period is the month of the cofg table date

EXEC  @l_RetCode      = kvarma.FDSSP_GET_CURRENT_DATE @l_RunDate OUTPUT
      IF (@l_RetCode != 0)
        BEGIN
            SELECT @msg = 'exec FDSSP_GET_CURRENT_DATE failed!'
            PRINT @msg
            RETURN
        END            
    SELECT @d_Firstday = @l_RunDate
    SELECT  @d_Lastday = @l_RunDate
END

/**********************************************************************
**                    Create temporary tables                        **
**********************************************************************/

CREATE TABLE #FTM_UMC023_REC
(
    GROUP_NAME             VARCHAR(39)        NULL         , --Version 1.11 change
    UMSV_RECD_DT              DATETIME        NULL         ,
    INI_AUTH_NURSE            CHAR(82)        NULL         ,
    REVIEW_REQUEST            CHAR(82)        NULL         ,
    UMUM_REF_ID               CHAR(9)         NULL         ,
    MCTR_DESC                 CHAR(30)        NULL         ,
    UMVT_SEQ_NO               SMALLINT        NULL         ,
    UMSV_SEQ_NO               SMALLINT        NULL         ,
    MD_NAME                   CHAR(55)        NULL         ,
    PRCF_MCTR_SPEC            CHAR(4)         NULL         ,
    PRAC_SPECIALITY           CHAR(30)        NULL         ,
    AREA_CODE                 CHAR(3)         NULL         ,
    COUNTY                    CHAR(20)        NULL         ,
    INDICATION                CHAR(127)       NULL         ,
    UM_COST_STS               CHAR(35)        DEFAULT SPACE(35) ,
    UM_COST_TYPE              CHAR(35)        DEFAULT SPACE(35)         ,
    NTNB_ID                   DATETIME        NULL         ,
    DRUG_REQ                  VARCHAR(500)    NULL         ,
    COMMENTS                  VARCHAR(500)    NULL         ,
    SUBJECT_TYPE              VARCHAR(500)    NULL         ,
    NOTE_TYPE                 VARCHAR(500)    NULL         ,
    PROJECTED_COST            MONEY           DEFAULT 0.0  ,
    ACTUAL_COST               MONEY           DEFAULT 0.0  ,
    MEME_LAST_NAME            CHAR(35)        NULL         ,
    MEME_FIRST_NAME           CHAR(15)        NULL         ,
    MEMBER_NAME               VARCHAR(52)     NULL         ,
    MEME_SFX                  CHAR(3)         NULL         ,
    MEME_SEX                  CHAR(1)         NULL         ,
    MEMBER_ID                 VARCHAR(12)     NULL         ,
    MEME_BIRTH_DT             DATETIME        NULL         ,
    PATIENT_AGE               INT             NULL         ,
    UMSV_PRPR_ID_REQ          CHAR(12)        NULL         ,
    MEME_CK                   INT             NULL         ,
    GRGR_CK                   INT             NULL         ,
    SBSB_CK                   INT             NULL         ,
    UMSV_FROM_DT              DATETIME        NULL         ,
    UMSV_TO_DT                DATETIME        NULL         ,
    UMVT_STS                  CHAR(30)        NULL         ,
    UMVT_STS_REAS             CHAR(30)        NULL         ,
    UMSV_MCTR_LDNY            CHAR(4)         NULL         ,
    FLAG                      INT             DEFAULT 0    ,
    UMCL_SEQ_NO               INT             DEFAULT 0    ,
    UMUM_PDPD_ID              CHAR(12)        NULL         ,
    UMUM_PDPD_ID1             CHAR(12)        NULL         ,
    EXPD_STND                 VARCHAR(500)    NULL         ,
    CSPI_ID                   CHAR(8)         NULL         , -- Version 2.1    
    UMSV_MCTR_CALL            CHAR(4)         NULL         ,
    NTNB_UPD_DTM              DATETIME        NULL         ,
    UMSV_TYPE                 CHAR(1)         NULL         ,
    TIMELY_MCTR_DESC          CHAR(255)       NULL         ,
    UMSV_MICRO_ID             CHAR(18)        NULL         ,
    UMSV_MCTR_PVIO            CHAR(4)         NULL,
    ATXR_SOURCE_ID           DATETIME        NULL      ,
     IDCD_TYPE                  VARCHAR(4)      NULL,
    IDCD_DESC                    CHAR(228)        NULL    
)

CREATE TABLE #FTM_UMC023_NOTES
(
    UMUM_REF_ID               CHAR(9)         NULL         ,
    NTNB_ID                   DATETIME        NULL         ,
    NTNB_SUMMARY              CHAR(70)        NULL         ,
    NTTX_TEXT                 CHAR(70)        NULL         ,
    NTNB_MCTR_SUBJ            CHAR(4)         NULL         ,
    DRUG_REQ                  CHAR(500)       NULL         ,
    COMMENTS                  CHAR(500)       NULL         ,
    NTTX_SEQ_NO               INT             NULL
)

CREATE TABLE #FTM_UMC023_CONT
(
    UMUM_REF_ID               CHAR(9)         NULL         ,
    NTNB_ID                   DATETIME        NULL         ,
    DRUG_REQ                  VARCHAR(500)    NULL         ,
    COMMENTS                  VARCHAR(500)    NULL         ,
    NTTX_SEQ_NO               INT             NULL
)

CREATE TABLE #FTM_UMC023_UMCL_SEQNO
(
    UMUM_REF_ID               CHAR(9)         NULL         ,
    UMCL_SEQ_NO               INT             DEFAULT 0
)
--Version 1.11 change begins
/*********************************************************************
**               Create temporary tables                            **
*********************************************************************/
CREATE TABLE
    #FTM_RPT_PARAM
    (
    PARAM_NAME     VARCHAR(7)   ,
    PARAM_VALUE    VARCHAR(8)
    )

IF  ISNULL(LTRIM(RTRIM(@p_GRGR_ID)),'') <> ''
BEGIN
    SELECT
        @l_GRGR_ID = LTRIM(RTRIM(@p_GRGR_ID))
END

EXEC @l_RetCode  = kvarma.FDSSP_TEMP_TABLE_INSERT 'GRGR_ID',@l_GRGR_ID

IF (@l_RetCode != 0)
        BEGIN
            SELECT @msg = 'EXEC FDSSP_TEMP_TABLE_INSERT FAILED!'
                PRINT @msg
            RETURN
      END

--Version 1.11 change ends

/**********************************************************************
 **                     Populating the temporary table for           **
 **                     #FTM_UMC023_REC                              **
**********************************************************************/

INSERT INTO #FTM_UMC023_REC
 (
    GROUP_NAME                                             ,  --Version 1.11 change
    UMUM_REF_ID                                            ,
    UMSV_RECD_DT                                           ,
    INDICATION                                             ,
    UMSV_PRPR_ID_REQ                                       ,
    UMVT_SEQ_NO                                            ,
    UMSV_SEQ_NO                                            ,
    NTNB_ID                                                ,
    GRGR_CK                                                ,
    SBSB_CK                                                ,
    MEME_CK                                                ,
    UMSV_FROM_DT                                           ,
    UMSV_TO_DT                                             ,
    INI_AUTH_NURSE                                         ,
    UMVT_STS                                               ,
    UMSV_MCTR_LDNY                                         ,
    UMUM_PDPD_ID                                           ,
    UMUM_PDPD_ID1                                          ,
    EXPD_STND                                              ,
    CSPI_ID                                                 ,-- Version 2.1
    UMSV_MCTR_CALL                                         ,
    UMSV_TYPE                                               ,
    UMSV_MICRO_ID                                          ,
    UMSV_MCTR_PVIO,
    ATXR_SOURCE_ID
)
SELECT
    GRGR.GRGR_ID                                     + '-' + --Version 1.11 change
    MCTR.MCTR_DESC                                         ,--Version 1.11 change
    UMUM.UMUM_REF_ID                                       ,
    UMSV.UMSV_RECD_DT                                      ,
    UMSV.UMSV_IDCD_ID_PRI                                  ,
    UMSV.UMSV_PRPR_ID_REQ                                  ,
    UMSV.UMVT_SEQ_NO                                       ,
    UMSV.UMSV_SEQ_NO                                       ,
    UMUM.NTNB_ID                                           ,
    UMUM.GRGR_CK                                           ,
    UMUM.SBSB_CK                                           ,
    UMUM.MEME_CK                                           ,
    UMSV.UMSV_FROM_DT                                      ,
    UMSV.UMSV_TO_DT                                        ,
    UMUM.UMUM_CREATE_USID                                  ,
    CASE UMSV.UMVT_STS
         WHEN 'CL' THEN 'Void'
         WHEN 'CO' THEN 'Approved'
         WHEN 'DS' THEN 'Disallowed'
         WHEN 'IN' THEN 'Pended'
         WHEN 'LG' THEN 'Pended'
         WHEN 'PD' THEN 'Pended'
         WHEN 'UP' THEN 'Pended'
         ELSE ''
    END ,
    UMSV.UMSV_MCTR_LDNY,
    SUBSTRING(UMUM.CSPI_ID, 1, 3) as LOB                   ,
  CASE 
        WHEN SUBSTRING(UMUM.PDPD_ID, 3, 1)='P' and 
            (SUBSTRING(UMUM.CSPI_ID, 1, 1)='M' 
                or SUBSTRING(UMUM.CSPI_ID, 1, 3)='D')THEN 'MA-PD'
        WHEN SUBSTRING(UMUM.PDPD_ID, 3, 1)='N' and 
           (SUBSTRING(UMUM.PDPD_ID, 1, 1)='D' 
              or SUBSTRING(UMUM.CSPI_ID, 1, 1)='M') THEN 'MA'
        WHEN SUBSTRING(UMUM.PDPD_ID, 3, 1)='A' and 
            (SUBSTRING(UMUM.CSPI_ID, 1, 1)='M' 
                or SUBSTRING(UMUM.PDPD_ID, 1, 1)='D')THEN 'MA-PD Dual'
          ELSE ' '
     END AS MEDICARE,
    Space(500) AS EXPD                         ,           
    UMUM.CSPI_ID                               ,                  -- Version 2.1
    UMSV.UMSV_MCTR_CALL                        ,
    UMSV.UMSV_TYPE                             ,
    UMSV.UMSV_MICRO_ID                         ,
    UMSV.UMSV_MCTR_PVIO,
    UMSV.ATXR_SOURCE_ID

FROM
    ODS_FACETS.CMC_UMUM_UTIL_MGT            UMUM
INNER JOIN
    ODS_FACETS.CMC_UMSV_SERVICES            UMSV
ON
    UMUM.MEME_CK       = UMSV.MEME_CK
AND
    UMUM.UMUM_REF_ID   = UMSV.UMUM_REF_ID

--Version 1.11 change begins
INNER JOIN
     ODS_FACETS.CMC_GRGR_GROUP  GRGR
ON
    UMUM.GRGR_CK = GRGR.GRGR_CK
AND
    UPPER(LTRIM(RTRIM(GRGR.GRGR_ID))) IN (
                                         SELECT
                                             PARAM_VALUE
                                         FROM
                                             #FTM_RPT_PARAM
                                         WHERE
                                             PARAM_NAME = 'GRGR_ID'
                                         )
INNER JOIN ODS_FACETS.CMC_MCTR_CD_TRANS MCTR
   ON MCTR.MCTR_ENTITY = 'GRGR' 
  AND MCTR.MCTR_TYPE = 'TYPE' 
  AND MCTR.MCTR_VALUE = GRGR.GRGR_MCTR_TYPE
--Version 1.11 change ends
WHERE
    UMSV.SESE_ID in (SELECT TEXT_VAL
                       FROM ODS_FACETS.fid_FDS_CNFG_CONFIG
                      WHERE ENTITY = 'UMC023_PHARM_OVR')  --Pharmacy Override cases
AND
    UMSV.UMSV_RECD_DT BETWEEN @d_Firstday AND @d_Lastday

/**********************************************************************
 **                Updating the temporary table                      **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    MD_NAME        = PRPR.PRPR_NAME                        ,
    AREA_CODE      = SUBSTRING(PRAD.PRAD_PHONE,1,3)        ,
    COUNTY         = PRAD.PRAD_COUNTY                      ,
    PRCF_MCTR_SPEC = PRPR.PRCF_MCTR_SPEC                   ,
    FLAG           = 1
FROM
    #FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_PRPR_PROV                PRPR
ON
    FTMP.UMSV_PRPR_ID_REQ    = PRPR.PRPR_ID
INNER JOIN
    ODS_FACETS.CMC_PRAD_ADDRESS             PRAD
ON
    PRPR.PRAD_ID = PRAD.PRAD_ID
AND
    PRAD.PRAD_TYPE = 'PRI'
AND
    FTMP.UMSV_RECD_DT BETWEEN PRAD_EFF_DT AND PRAD_TERM_DT

/**********************************************************************
 **              Deletion from #FTM_UMC023_REC                       **
**********************************************************************/

DELETE FROM #FTM_UMC023_REC
WHERE FLAG = 0

/**********************************************************************
 **                Updating the temporary table                      **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    MEME_LAST_NAME  = ISNULL(RTRIM(LTRIM(MEME.MEME_LAST_NAME)),'' )   ,
    MEME_FIRST_NAME = ISNULL(RTRIM(LTRIM(MEME.MEME_FIRST_NAME)),'' )  ,
    MEME_SFX        = CASE
                           WHEN MEME.MEME_SFX IS NULL THEN ''
                           ELSE '-'+ RIGHT('0'+LTRIM(RTRIM(CONVERT(CHAR(2),
                                       MEME.MEME_SFX))),2)
                      END                                             ,
    MEME_SEX        = MEME.MEME_SEX                                   ,
    MEME_BIRTH_DT   = MEME.MEME_BIRTH_DT
FROM
    #FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_MEME_MEMBER              MEME
ON
    FTMP.GRGR_CK = MEME.GRGR_CK
AND
    FTMP.MEME_CK = MEME.MEME_CK

UPDATE #FTM_UMC023_REC
SET
    MEME_LAST_NAME  = RTRIM(LTRIM(MEME_LAST_NAME)) ,
    MEME_FIRST_NAME  = RTRIM(LTRIM(MEME_FIRST_NAME))
FROM
    #FTM_UMC023_REC

/**********************************************************************
 **                  Member Name and Patient Age                     **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    MEMBER_NAME = CASE
                    WHEN MEME_LAST_NAME <>'' AND MEME_FIRST_NAME <> ''
                    THEN RTRIM(MEME_LAST_NAME) + ', ' + RTRIM(MEME_FIRST_NAME)
                    WHEN MEME_LAST_NAME ='' AND MEME_FIRST_NAME <> ''
                    THEN MEME_FIRST_NAME
                    WHEN MEME_LAST_NAME <>'' AND MEME_FIRST_NAME = ''
                    THEN MEME_LAST_NAME
                    ELSE ''
                  END                                      ,
    PATIENT_AGE = CASE
                        WHEN DATEPART(MM,@d_Lastday) <
                                  DATEPART(MM,MEME_BIRTH_DT )
                        THEN (DATEDIFF (YY,
                                   MEME_BIRTH_DT,@d_Lastday ) -1 )
                        WHEN DATEPART(MM,@d_Lastday)=
                                 DATEPART(MM,MEME_BIRTH_DT)
                        AND  DATEPART(dd,@d_Lastday)  <
                                 DATEPART(dd,MEME_BIRTH_DT)
                        THEN (DATEDIFF (YY,
                                   MEME_BIRTH_DT,@d_Lastday) -1 )
                        ELSE DATEDIFF (YY,
                                   MEME_BIRTH_DT,@d_Lastday)
                        END

UPDATE #FTM_UMC023_REC  -- Set the Age = 0 when the Age is < 0
SET
    PATIENT_AGE = 0
WHERE
    PATIENT_AGE < 0

/**********************************************************************
 **                          Member ID                               **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    MEMBER_ID  = LTRIM(RTRIM(SBSB.SBSB_ID)) + FTMP.MEME_SFX
FROM
    #FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_SBSB_SUBSC               SBSB
ON
    FTMP.GRGR_CK = SBSB.GRGR_CK
AND
    FTMP.SBSB_CK = SBSB.SBSB_CK

/**********************************************************************
**                note and subject types                 **
**********************************************************************/
-----------------------------------------------------------------------

UPDATE #FTM_UMC023_REC
SET
    NOTE_TYPE = 'Medication',
    SUBJECT_TYPE = MCTR_SPEC.MCTR_DESC
FROM

#FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_NTNB_NOTE_BASE           NTNB
ON
    FTMP.NTNB_ID = NTNB.NTNB_ID
AND NTNB.NTNB_CAT = 'RX'

INNER JOIN
    ODS_FACETS.CMC_MCTR_CD_TRANS  MCTR_SPEC
ON
    MCTR_SPEC.MCTR_VALUE = NTNB.NTNB_MCTR_SUBJ
AND
    MCTR_SPEC.MCTR_ENTITY    = 'NTNB'          
AND
    MCTR_SPEC.MCTR_TYPE      = 'SUBJ'
-----------------------------------------------------------------------
/**********************************************************************
**                Getting the Provider's Speciality                  **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    PRAC_SPECIALITY = MCTR_SPEC.MCTR_DESC
FROM
    ODS_FACETS.CMC_MCTR_CD_TRANS  MCTR_SPEC
INNER JOIN
    #FTM_UMC023_REC                                        FTMP
ON
    MCTR_SPEC.MCTR_VALUE = FTMP.PRCF_MCTR_SPEC
WHERE
    MCTR_SPEC.MCTR_ENTITY    = 'PRAC'          
AND
    MCTR_SPEC.MCTR_TYPE      = 'SPEC'

/**********************************************************************
**                Getting the Primary Diagnosis Code                 **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    IDCD_TYPE = IDCD.IDCD_TYPE,
    IDCD_DESC = IDCD.IDCD_DESC,
    INDICATION = CASE
                    WHEN ISNULL(LTRIM(RTRIM(IDCD.IDCD_DESC)),'') = ''
                    THEN FTMP.INDICATION
                    ELSE FTMP.INDICATION + '-'
                               + LTRIM(RTRIM(IDCD.IDCD_DESC))
                 END
FROM
    ODS_FACETS.CMC_IDCD_DIAG_CD             IDCD
INNER JOIN
    #FTM_UMC023_REC                                        FTMP
ON
    IDCD.IDCD_ID = FTMP.INDICATION
AND 
    IDCD.IDCD_TYPE IN ('I', 'T')

/**********************************************************************
** Insert max UMCL seq # corresponding to the auth in the temp table **
**********************************************************************/
INSERT INTO #FTM_UMC023_UMCL_SEQNO
(
    UMUM_REF_ID   ,
    UMCL_SEQ_NO
)
SELECT
    FTMP.UMUM_REF_ID ,
    MAX(UMCL.UMCL_SEQ_NO)
FROM
    #FTM_UMC023_REC    FTMP
INNER JOIN
    ODS_FACETS.CMC_UMCL_COST_LOG            UMCL
ON
    FTMP.UMUM_REF_ID = UMCL.UMUM_REF_ID
GROUP BY
    FTMP.UMUM_REF_ID

/**********************************************************************
**  Update the max UMCL seq no in the main temp table                **
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET
    UMCL_SEQ_NO = UMCL.UMCL_SEQ_NO
FROM
    #FTM_UMC023_REC  FTMP
INNER JOIN
    #FTM_UMC023_UMCL_SEQNO UMCL
ON
    FTMP.UMUM_REF_ID = UMCL.UMUM_REF_ID

/**********************************************************************
**  Drop the temporary table which stored sequence #                 **
**********************************************************************/
DROP TABLE #FTM_UMC023_UMCL_SEQNO

/**********************************************************************
 **                 Getting the UM Cost Status                       **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    UM_COST_STS     = ISNULL(LTRIM(RTRIM(MCTR_UMCL.MCTR_DESC)),''),
    UM_COST_TYPE   = UMCL.UMCL_MCTR_COST                   ,
    PROJECTED_COST = UMCL.UMCL_PROJ_COST                   ,
    ACTUAL_COST    = UMCL.UMCL_ACT_COST
FROM
    #FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_UMCL_COST_LOG            UMCL
ON
    FTMP.UMUM_REF_ID = UMCL.UMUM_REF_ID
AND
    FTMP.UMCL_SEQ_NO = UMCL.UMCL_SEQ_NO
LEFT OUTER JOIN
    ODS_FACETS.CMC_MCTR_CD_TRANS            MCTR_UMCL
ON
    UMCL.UMCL_MCTR_CSTS   = MCTR_UMCL.MCTR_VALUE
AND
    MCTR_UMCL.MCTR_ENTITY = 'UMCL'
AND
    MCTR_UMCL.MCTR_TYPE   = 'CSTS'

/**********************************************************************
**                      Getting the UM Cost Type                     **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    UM_COST_TYPE = ISNULL(LTRIM(RTRIM(UMCL_COST.MCTR_DESC)),'')
FROM
    ODS_FACETS.CMC_MCTR_CD_TRANS            UMCL_COST
INNER JOIN
    #FTM_UMC023_REC                                        FTMP
ON
    UMCL_COST.MCTR_VALUE = FTMP.UM_COST_TYPE
AND
    UMCL_COST.MCTR_ENTITY = 'UMCL'
AND
    UMCL_COST.MCTR_TYPE   = 'COST'

/**********************************************************************
            Getting the UMVT STATUS CODE REASON DESCRIPTION          **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    UMVT_STS_REAS = LTRIM(RTRIM(MCTR.MCTR_DESC))
FROM
    #FTM_UMC023_REC                               FTMP
INNER JOIN
    ODS_FACETS.CMC_UMVT_STATUS     UMVT
ON
    FTMP.UMUM_REF_ID = UMVT.UMUM_REF_ID
AND
    FTMP.UMSV_SEQ_NO = UMVT.UMSV_SEQ_NO
AND
    FTMP.UMVT_SEQ_NO = UMVT.UMVT_SEQ_NO
INNER JOIN
    ODS_FACETS.CMC_MCTR_CD_TRANS   MCTR
ON
    UMVT.UMVT_MCTR_REAS = MCTR.MCTR_VALUE
AND
    MCTR_ENTITY = 'UMVT'
AND
    MCTR_TYPE   = 'REAS'

/**********************************************************************
            Getting the REASON DESCRIPTION FOR DISALLOWED CASES      **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    UMVT_STS_REAS = LTRIM(RTRIM(MCTR.MCTR_DESC))
FROM
    #FTM_UMC023_REC                               FTMP
INNER JOIN
    ODS_FACETS.CMC_MCTR_CD_TRANS   MCTR
ON
    FTMP.UMSV_MCTR_LDNY = MCTR.MCTR_VALUE
AND
    MCTR.MCTR_ENTITY = 'UMSV'
AND
    MCTR.MCTR_TYPE   = 'LDNY'
AND
    FTMP.UMVT_STS    = 'Disallowed'

/**********************************************************************
**             Getting the Reviewer User ID and Description          **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
    REVIEW_REQUEST = UMVT.USUS_ID
FROM
    #FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_UMVT_STATUS              UMVT
ON
    FTMP.UMUM_REF_ID = UMVT.UMUM_REF_ID
AND
    FTMP.UMSV_SEQ_NO = UMVT.UMSV_SEQ_NO
AND
    FTMP.UMVT_SEQ_NO = UMVT.UMVT_SEQ_NO

/**********************************************************************
 **                     Populating the temporary table for           **
 **                     #FTM_UMC023_NOTES                            **
**********************************************************************/

INSERT INTO #FTM_UMC023_NOTES
(
  UMUM_REF_ID                                              ,
  NTNB_ID                                                  ,
  NTNB_SUMMARY                                             ,
  NTTX_TEXT                                                ,
  NTNB_MCTR_SUBJ                                           ,
  COMMENTS                                                 ,
  NTTX_SEQ_NO
)
SELECT
  FTMP.UMUM_REF_ID                                         ,
  NTNB.NTNB_ID                                             ,
  NTNB.NTNB_SUMMARY                                        ,
  NTTX.NTTX_TEXT                                           ,
  NTNB.NTNB_MCTR_SUBJ                                      ,
  CASE
       WHEN ISNULL(LTRIM(RTRIM(NTTX.NTTX_TEXT)),'') = ''
       THEN LTRIM(RTRIM(NTNB.NTNB_SUMMARY))
       ELSE LTRIM(RTRIM(NTNB.NTNB_SUMMARY)) + '-'
                 + LTRIM(RTRIM(NTTX.NTTX_TEXT))
  END                                                       ,
  NTTX.NTTX_SEQ_NO
FROM
    #FTM_UMC023_REC                                        FTMP
INNER JOIN
    ODS_FACETS.CMC_NTNB_NOTE_BASE           NTNB
ON
    FTMP.NTNB_ID = NTNB.NTNB_ID
INNER JOIN
    ODS_FACETS.CMC_NTTX_NOTE_TEXT           NTTX
ON
    NTNB.NTNB_ID        = NTTX.NTNB_ID
AND
    NTNB.NTNB_INPUT_DTM = NTTX.NTNB_INPUT_DTM
WHERE
    NTNB.NTNB_CAT       = 'RX'
AND
    NTNB.NTNB_MCTR_SUBJ IN ('NS44','NS96','NS97','NU78','NU79','NU80','NU81','NU82','NU83','NU84','NU85','NU86','NU87','NU88','NU89','NU90','NU91','NU92','NU93')

/**********************************************************************
 **             Getting the Comments and Drug Requested              **
**********************************************************************/

DECLARE COMMENTS_CURSOR CURSOR
FOR
SELECT
    FTMP.UMUM_REF_ID                                       ,
    FTMP.NTNB_ID                                           ,
    FTMP.NTTX_TEXT                                         ,
    FTMP.DRUG_REQ                                          ,
    FTMP.NTTX_SEQ_NO
FROM
    #FTM_UMC023_NOTES                                     FTMP
ORDER BY
 FTMP.UMUM_REF_ID                                          ,
 FTMP.NTTX_SEQ_NO      DESC

OPEN COMMENTS_CURSOR

FETCH COMMENTS_CURSOR INTO
    @l_UMUM_REF_ID                                         ,
    @l_NTNB_ID                                             ,
    @l_COMMENTS                                            ,
    @l_DRUG_REQ                                            ,
    @l_NTTX_SEQ_NO
SELECT
    @l_LOC_NTNB_ID = @l_NTNB_ID                             ,
    @l_CON_COMMENTS= ''                                     ,
    @l_CON_DRG_REQ = ''                                     ,
    @l_LOC_UMUM_REF_ID = @l_UMUM_REF_ID

WHILE (@@sqlstatus <> 2)
BEGIN

IF ( @l_UMUM_REF_ID != @l_LOC_UMUM_REF_ID)
BEGIN

INSERT INTO #FTM_UMC023_CONT
  (
    UMUM_REF_ID                                            ,
    NTNB_ID                                                ,
    COMMENTS                                               ,
    DRUG_REQ
 )
   SELECT
    @l_LOC_UMUM_REF_ID                                     ,
    @l_LOC_NTNB_ID                                         ,
    @l_CON_COMMENTS                                        ,
    @l_CON_DRG_REQ

SELECT
    @l_LOC_NTNB_ID = @l_NTNB_ID                            ,
    @l_CON_COMMENTS=''                                     ,
    @l_CON_DRG_REQ = ''                                    ,
    @l_LOC_UMUM_REF_ID = @l_UMUM_REF_ID

SELECT
    @l_CON_COMMENTS =
                           ISNULL(LTRIM(RTRIM( @l_COMMENTS)),'')

SELECT
    @l_CON_DRG_REQ  =
                       CASE
                         WHEN  @l_DRUG_REQ IS  NULL
                         THEN  @l_CON_DRG_REQ
                         ELSE ISNULL(LTRIM(RTRIM(@l_CON_DRG_REQ )),'')
                          + ISNULL(LTRIM(RTRIM( @l_DRUG_REQ)),'') + ';'
                       END
END

ELSE
BEGIN

IF (@l_LOC_UMUM_REF_ID = @l_UMUM_REF_ID)
BEGIN
SELECT @l_CON_COMMENTS =
                       CASE
                         WHEN  @l_COMMENTS IS NULL
                         THEN  @l_CON_COMMENTS
                         ELSE ISNULL(LTRIM(RTRIM( @l_COMMENTS)),'')
                              + ' '
                              + ISNULL(LTRIM(RTRIM(@l_CON_COMMENTS)),'')
                       END

SELECT @l_CON_DRG_REQ  =
                       CASE
                         WHEN  @l_DRUG_REQ IS  NULL
                         THEN @l_CON_DRG_REQ
                         ELSE ISNULL(LTRIM(RTRIM(@l_CON_DRG_REQ )),'')
                          + ISNULL(LTRIM(RTRIM( @l_DRUG_REQ)),'') + ';'
                       END
END

END
FETCH COMMENTS_CURSOR INTO
    @l_UMUM_REF_ID                                         ,
    @l_NTNB_ID                                             ,
    @l_COMMENTS                                            ,
    @l_DRUG_REQ                                            ,
    @l_NTTX_SEQ_NO
END

INSERT INTO #FTM_UMC023_CONT
  (
    UMUM_REF_ID                                            ,
    NTNB_ID                                                ,
    COMMENTS                                               ,
    DRUG_REQ
 )
SELECT
    @l_UMUM_REF_ID                                         ,
    @l_LOC_NTNB_ID                                         ,
    @l_CON_COMMENTS                                        ,
    @l_CON_DRG_REQ

CLOSE COMMENTS_CURSOR

DEALLOCATE CURSOR COMMENTS_CURSOR

/**********************************************************************
**             Getting the Comments and Drug Requested               **
**********************************************************************/

UPDATE #FTM_UMC023_REC
SET
   COMMENTS = CONT.COMMENTS
FROM
    #FTM_UMC023_CONT                                       CONT
INNER JOIN
    #FTM_UMC023_REC                                        FTMP
ON
      CONT.UMUM_REF_ID = FTMP.UMUM_REF_ID

UPDATE #FTM_UMC023_REC
SET
     DRUG_REQ = CONT.NTNB_SUMMARY
FROM
    #FTM_UMC023_NOTES                                      CONT
INNER JOIN
    #FTM_UMC023_REC                                        FTMP
ON
      CONT.UMUM_REF_ID = FTMP.UMUM_REF_ID

/**********************************************************************
** Update Notes Only for the cases where NTNB_CAT = "GN"            **
**  AND NTNB_MCTR_SUBJ IN ("NT78","NT79","NT82", "NT83")            **                   
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET EXPD_STND = NTNB_MCTR_SUBJ 
FROM
    #FTM_UMC023_REC                                FTMP
INNER JOIN
    ODS_FACETS.CMC_NTNB_NOTE_BASE           NTNB
ON
    FTMP.NTNB_ID = NTNB.NTNB_ID
INNER JOIN
    ODS_FACETS.CMC_NTTX_NOTE_TEXT           NTTX
ON
    NTNB.NTNB_ID        = NTTX.NTNB_ID
AND
    NTNB.NTNB_INPUT_DTM = NTTX.NTNB_INPUT_DTM
WHERE
    NTNB.NTNB_CAT       = 'GN'
AND
    NTNB.NTNB_MCTR_SUBJ IN ('NT78','NT79', 'NT82', 'NT83')

/*********************************************************************
 UPDATE NTNB_UPD_DTM 
**********************************************************************/
UPDATE #FTM_UMC023_REC
SET NTNB_UPD_DTM = NTNB.NTNB_UPD_DTM 
FROM
    #FTM_UMC023_REC                                FTMP
INNER JOIN
    ODS_FACETS.CMC_NTNB_NOTE_BASE           NTNB
ON
    FTMP.NTNB_ID = NTNB.NTNB_ID
AND NTNB.NTNB_CAT = 'PA'
AND NTNB.NTNB_MCTR_SUBJ = 'NS89' 
/********************************************************************
 UPDATE Timely/Untimely
*********************************************************************/
UPDATE #FTM_UMC023_REC
SET
   TIMELY_MCTR_DESC  = LTRIM(RTRIM(MCTR.MCTR_DESC))
FROM
    #FTM_UMC023_REC                               FTMP
INNER JOIN
    ODS_FACETS.CMC_MCTR_CD_TRANS   MCTR
ON  FTMP.UMSV_MCTR_PVIO =  MCTR.MCTR_VALUE 
AND MCTR.MCTR_ENTITY = 'UMSV'
AND MCTR.MCTR_TYPE = 'PVIO'

/**********************************************************************
**                      Final Result Set                             **
**********************************************************************/

SELECT
    GROUP_NAME                                             , --Version 1.11 change
    CSPI_ID                                                , -- Version 2.1
    UMSV_RECD_DT                                           ,
    INI_AUTH_NURSE                                         ,
    REVIEW_REQUEST                                         ,
    UMUM_REF_ID                                            ,
    MEMBER_NAME                                            ,
    MEMBER_ID                                              ,
    PATIENT_AGE                                            ,
    MD_NAME                                                ,
    PRAC_SPECIALITY                                        ,
    MEME_SEX                                               ,
    AREA_CODE                                              ,
    COUNTY                                                 ,
    INDICATION                                             ,
    UM_COST_STS                                            ,
    UM_COST_TYPE                                           ,
    PROJECTED_COST                                         ,
    ACTUAL_COST                                            ,
    DRUG_REQ                                               ,
    COMMENTS                                               ,
    SUBJECT_TYPE                        ,
    NOTE_TYPE                           ,
    UMSV_FROM_DT                                           ,
    UMSV_TO_DT                                             ,
    UMVT_STS                                               ,
    UMVT_STS_REAS                                          ,
    UMUM_PDPD_ID                                           ,
    UMUM_PDPD_ID1                                          ,                                                               
    EXPD_STND                                              ,
    CASE  WHEN ISNULL(LTRIM(RTRIM(UMSV_MCTR_CALL)),'') = ''
          THEN 'BLANK'
          ELSE LTRIM(RTRIM(UMSV_MCTR_CALL))  
    END 'Category of Med'                                  ,
    NTNB_UPD_DTM                                           ,
    UMSV_TYPE                                              ,
    TIMELY_MCTR_DESC                                       ,
    UMSV_MICRO_ID           ,
    'Style_Id' = ATXR.ATSY_ID           ,
    'Document_ID' =ATLD_ID  ,
    IDCD_TYPE ,
    IDCD_DESC

FROM
    #FTM_UMC023_REC         FTM

LEFT OUTER JOIN
    ODS_FACETS.CER_ATXR_ATTACH_U            ATXR
    ON ATXR.ATXR_SOURCE_ID  = FTM.ATXR_SOURCE_ID
    AND ATTB_ID =  'UMSV'

LEFT OUTER JOIN
    ODS_FACETS.CER_ATLT_LETTER_D        ATLT    
    ON  ATXR.ATSY_ID = ATLT.ATSY_ID
    AND ATXR.ATXR_DEST_ID = ATLT.ATXR_DEST_ID

-----------   
ORDER BY
    GROUP_NAME                                             , --Version 1.11 change
    UMSV_RECD_DT DESC                                      ,
    UMUM_REF_ID

RETURN 0

END

asked 24 Mar '14, 14:40

pandu's gravatar image

pandu
1112
accept rate: 0%

edited 24 Mar '14, 15:05

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270

How do you know it is the cursor that is taking the time?

Have you run the stored procedure profiler to see where the time is going?

Have you looked at the graphical plan with statistics for any of the queries that you know are taking up a lot of time?

What version of SQL Anywhere are you using? Please tell me it is SQL Anywhere, not Sybase ASE or Microsoft SQL Server... which is what it looks like because the code's written in Transact SQL rather than Watcom SQL.

(24 Mar '14, 15:18) Breck Carter

Thanks you for the reply

1) Yes I know it is the cursor which is taking time because I have run the SP to see where it is taking time and for cursor it is taking 3 hrs to run the store procedure.

2) no I didnt look at the grapical plan, this is the first time I am using rapid SQL(sybase).

3) I am using sybase IQ rapid sql

Please let me know if you have any question that I have to answer. Once again thanks for your reply.

(24 Mar '14, 18:13) pandu
Replies hidden
1

If "sybase IQ rapid sql" means you are using Embarcadero's Rapid SQL product on a Sybase IQ database, then this is the wrong forum. This forum is dedicated to Sybase SQL Anywhere which is a different product from Sybase IQ when it comes to performance (the SQL language is very close but the underlying query engines are different).

(24 Mar '14, 20:55) Breck Carter

"I have run the SP to see where it is taking time" - exactly HOW did you do that? Does Rapid SQL have a profiler for IQ?

(24 Mar '14, 21:02) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×20

question asked: 24 Mar '14, 14:40

question was seen: 865 times

last updated: 25 Mar '14, 04:26