if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMRspformat]') AND type = 'P')
drop Procedure [dbo].[EMRspformat]
GO

create Procedure [dbo].[EMRspformat](@inputStr VARCHAR(8000)) as
  --inputStr   VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
Declare
  @in_strlen  BINARY_INTEGER,
  @in_array   DBMS_UTILITY.uncl_array,
  @out_query VARCHAR(4000),
  @final_query VARCHAR(4000),
  @sortOrder NUMERIC(20,0),
  @CCID NUMERIC(20,0),
  @ccIndex  NUMERIC(10),
  @ccLevel VARCHAR(5),
  @parentId NUMERIC(20,0),
  @firstCount  NUMERIC(10);
BEGIN
  SELECT  @CCID=ezEMRxID FROM EMRIDS where property_name = 'CC_ID'
  Set @sortOrder = 0
  Set @ccIndex = 1
  Set @ccLevel = '1'
  Set @parentId = 0
  Set @firstCount = 0
  Set @out_query = 'INSERT INTO EMRChiefComplaintLkup ('
  Set @out_query = @out_query + 'CC_ID, CC_PARENT_ID, CC_SORT_ORDER, CC_LEVEL , CC_NAME, CC_HAS_CHILD, CC_NARRATION, CC_TYPE,'
  Set @out_query = @out_query + 'CC_STATUS, USER_ID, CC_CODING_COUNT, SPECIALTY_ID '
  Set @out_query = @out_query + ') VALUES ('
  DBMS_UTILITY.comma_to_table (
      inputStr,
      in_strlen,
      in_array);

  While (@ccIndex<=@in_strlen) 
    @final_query = @out_query + Cast(@CCID as Varchar) + ', '
    if(@ccLevel != replace(Cast(@in_array(ccIndex)),'"',''))
      begin
         if(replace(Cast(@in_array(@ccIndex)),'"','') = '1') 
          begin
          Set @sortOrder = 0
          Set @firstCount = @firstCount+1
          Set @final_query = @final_query + Cast('0' as Varchar) + ', '
          Set @final_query = @final_query + Cast(@firstCount as Varchar) + ', '
          Set @parentId = @CCID
          end
         else
           begin
          Set @final_query = @final_query + Cast(@parentId as Varchar) + ', '
          Set @final_query = @final_query + Cast(@sortOrder as Varchar) + ', '
          Set @parentId = @CCID-1
          end
      end
    else
      begin
     if(replace(cast(@in_array(ccIndex)),'"','') = '1') 
       Set @sortOrder = 0
       Set @firstCount = @firstCount+1
       Set @final_query = @final_query + Cast('0' as Varchar) + ', '
       Set @final_query = @final_query + Cast(@firstCount as Varchar) + ', '
       Set @parentId = @CCID
     else 
        Set @final_query = @final_query + Cast(@parentId as Varchar) + ', '
        Set @final_query = @final_query + Cast(@sortOrder as Varchar) + ', '
      end
    Set @final_query = @final_query + replace(Cast(@in_array(@ccIndex)),'"','') + ', '
    Set @final_query = @final_query + ''''+ replace(replace((@in_array(@ccIndex+1)),'"',''),'''','''''')  + ''', '
    Set @final_query = @final_query + replace(Cast(@in_array(@ccIndex+2)),'"','')  + ', '
    --Set @final_query = @final_query + 'NULL, 0, 1, NULL, ' + to_char(sortOrder) + ', 0, 1003)'
    Set @ final_query = @final_query + 'NULL, 0, 1, NULL, 0, 1003)'
    Set @ccLevel = replace(Cast(@in_array(@ccIndex)),'"','')
    Set @sortOrder = @sortOrder + 1
    Set @ccid = @ccid + 1
    Set @ccIndex=@ccIndex+3
    PRINT @final_query
    PRINT '/'
    Set @final_query = ''
     PRINT 'update emrids set ezEMRxID = '+@ccid +' where property_name = ''CC_ID'''
END
GO