title: How to debug a user defined function
body: <p>The user defined function below is causing duplicates and this causing a build to fail. What is the best approach to test each part of the function so I can pinpoint where the issue is? And assistance would be a huge benefit. </p>
  
  <p>I've never really had to debug a function before as I assume you would run the code as select first before inserting into a user defined function.</p>
  
  <pre><code>USE [HealthBI]
  GO
  
  /****** Object:  UserDefinedFunction [dbo].[udf_INS_CDSGetCommissionerCode]    Script Date: 03/10/2017 09:04:42 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  /*____________________________________________________________________________________
  **  Procedure Name  : udf_INS_CDSGetCommissionerCode
  **
  **  Description     : Returns the comissioner code calculated via standard rules.
  **  
  **  Parameters      : 
  **
  **  Returns         : Commissioner Code (Varchar50)
  **
  **  Created by      : Insource  
  **
  **  Changes         : Date      : Vers  : By        : Comments
  **                  _____________________________________________________________________________
  **                  30/01/2012  : 1.00  : PJS       : Initial Design 
  **                  20/06/2017  : 2.00  : JH        : UPDATE TO MEET COMMISSIONER DEFINITIONS 
  **_______________________________________________________________________________________________
  */
  CREATE function [dbo].[udf_INS_CDSGetCommissionerCode] (
   @CDS_TYPE as varchar(50), --'A&amp;E','OPS','APC'
   @HospitalProviderSpellNumber as  VARCHAR(50),
   @OverseasVisitorStatusClassification AS VARCHAR(50),
   @AdministrativeCategoryCode AS VARCHAR(50),
   @PostcodeatEventDate AS VARCHAR(50),
   @ISPBR AS VARCHAR(50), 
   @GPatEventDate AS VARCHAR(50),
   @OrganisationCodeCodeofProvider AS VARCHAR(50),
   @SourceofAdmissionHospitalProviderSpell AS VARCHAR(50),
   @ResponsibleCCG AS VARCHAR(50),
   @NHS_Service_Agreement_Line_Number AS VARCHAR(50), --USED AS A CHECK FOR CLOUMN B/C OF CROSS BORDER TABLE. PROBABLY REPLACE WITH A FLAG AT P4
   @Specialised_Level AS VARCHAR(50), --"0"/NULL not specialised, "1" Specialised, "2" Highly specialised     --SHOULD COME FROM                                                                                  -- load in appendix b for ref
   @IS_WITH_CROSS_BOARDER_AGREEMENT AS VARCHAR(50), --load in appendix H, add flag to ods psudo postcode table. IS_WITH_CROSS_BOARDER_AGREEMENT
   @POSTCODE_COUNTRY AS VARCHAR(50), --MANY CHECKS ARE MADE FOR THE COUNTRY OF THE POSTCODE, IT'S EASIER TO JUST HAVE THIS VALUE ON THE TABLE
   @GP_National_Grouping AS VARCHAR(50),
   @IS_Secondary_Dental_Care AS VARCHAR(50), --Codes can change, probably better to add a flag that can be updated in P4 (Also allows for CBRs for interim updates pending standard changes)
   @IS_Infertility_treatment AS VARCHAR(50), --Codes can change, probably better to add a flag that can be updated in P4 (Also allows for CBRs for interim updates pending standard changes)
   @GPStatusCode AS VARCHAR(50), --possibly a lookup instead
   @EpisodeNumber AS VARCHAR(50)
      )
  
  
      RETURNS varchar(50)
  
  AS 
  
      BEGIN
  
          /*_______________________________________________________________________
          **Declare Local variables
          **________________________________________________________________________
          */
          DECLARE @CommissionerCode   as varchar(50)
          SET     @CommissionerCode   = NULL
  
          /*_______________________________________________________________________
          **Set code not mapped to null for some variables
          **________________________________________________________________________
          */
          SET @OverseasVisitorStatusClassification = ISNULL(NULLIF(@OverseasVisitorStatusClassification,'CODE NOT MAPPED'),'')
          SET @SourceofAdmissionHospitalProviderSpell = ISNULL(NULLIF(@SourceofAdmissionHospitalProviderSpell,'CODE NOT MAPPED'),'')
          SET @AdministrativeCategoryCode = ISNULL(NULLIF(@AdministrativeCategoryCode,'CODE NOT MAPPED'),'')
          SET @GPatEventDate = ISNULL(NULLIF(@GPatEventDate,'CODE NOT MAPPED'),'')
          SET @PostcodeatEventDate = ISNULL(NULLIF(@PostcodeatEventDate,'CODE NOT MAPPED'),'')
          SET @ISPBR = ISNULL(NULLIF(@ISPBR,''),'Y')
          /*_______________________________________________________________________
          **Process the calculation
          **________________________________________________________________________
          */
  
  
  /*CHECK CDS TYPE
  */
  If @CDS_TYPE = 'A&amp;E' --Checks if the CDS type is A&amp;E
  GOTO Box_AE_1
  ELSE 
  GOTO Box_A
  
  
  Box_AE_1:
  /*Usual Place of Residence is in England A&amp;E-1 
   TRUE if on the A&amp;E Arrival Date: Postcode of Usual Address has a Country value of ‘E92000001’ in the ONS NHS Postcode Directory
  */
  
      IF @POSTCODE_COUNTRY = 'ENGLAND'
      GOTO Box_J
      ELSE
      Set @CommissionerCode = (select CCG_CODE from CDI_ODS_ALL_TRUST (NOLOCK) WHERE ORGANISATION_CODE = @OrganisationCodeCodeofProvider)
      GOTO Box_Final
  
  Box_A:
  /*Patient Liable for Charges (Box A)
  TRUE if any episode (in the PbR Spell) has: Overseas Visitor Status Classification = ‘4’ OR Administrative Category Code (On Admission) = ’02’ (Note episodes with this value are excluded from PbR Spells, so will not be found) CONSIDER ADDING: OR Organisation Code (Code of Commissioner) starts with “VPP”
  */ --WE DON'T HOLD OVERSEAS STATUS PER EPISODE.
          IF @OverseasVisitorStatusClassification = '4' or @AdministrativeCategoryCode = '02' 
          BEGIN
          set @CommissionerCode = 'VPP00'  --@GPatEventDate??
          GOTO Box_Final 
          END
          ELSE
          GOTO Box_B
  
  Box_B: 
  /*Ordinarily Resident in UK B 
  FALSE if any episode (in the PbR Spell) has: Overseas Visitor Status Classification IN (‘’1, ‘2’, ’3’, ‘4’) 
  
  FALSE if all episodes (in the PbR Spell) have: Overseas Visitor Status Classification NOT IN (‘’1, ‘2’, ’3’, ‘4’) AND in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): Postcode of Usual Address in form ‘BF1nAA’  
  
  TRUE if in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): Postcode of Usual Address has a Country value of ‘E92000001’ OR ‘ ‘N92000002’ OR ‘S92000003’ OR ‘W92000004’ in the ONS NHS Postcode Directory
   */ 
      IF @OverseasVisitorStatusClassification IN ('1','2','3','4') or @PostcodeatEventDate LIKE 'BF1%'--Could replace this with a flag IS_UK_RESIDENT calculated at P4, probably better to us this field as it's used for other steps.
      GOTO Box_B_1
      ELSE
      GOTO Box_XB_1
  
  
  Box_B_1:  
  /*Highly Specialised Care B-1 
  TRUE if: Derived PSS Code (PbR Spell level) is in the list of Highly Specialised Service Line Codes
   */
      IF @ISPBR = 'N'
          BEGIN
          IF (SELECT ISNULL(NULLIF(IS_SCG_ACTIVITY,''),'N') FROM CDI_APC_CONSULTANT_EPISODE (NOLOCK)  WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber) ='Y'
              BEGIN
              GOTO Box_B_2
              END
              ELSE
              GOTO Box_B_3
          END
          ELSE
          IF @Specialised_Level = '2' --posibly do this check on feed to this fn
          GOTO Box_B_2
          ELSE
          GOTO Box_B_3
  
  Box_B_2:  
  /*Patient Resident in EU State with Cross Border Right of Access B-2 
  TRUE if in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): Postcode of Usual Address is in the Cross Border Rights List
   */
      IF @IS_WITH_CROSS_BOARDER_AGREEMENT = 'Y'
      BEGIN
      SET @CommissionerCode = (select Footprint_Code from IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs (NOLOCK)  where IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs.Provider_Code = @OrganisationCodeCodeofProvider)--'Lookup Organisation Code (Code of Provider) in the list of Contracted Providers for Specialised Services.  Use the related footprint code. ' --OUTPUT: S4, appendix b
      GOTO BOX_Final
      END
      ELSE
      GOTO BOX_B_3
  
  Box_B_3: 
  /*Overseas Visitor Charging Exemption B-3 
  Unless guidance becomes clearer this will not be assessed in SUS+. Note: No incoming data fields will have been changed, so inferences can still be made from them.  CHECK GIBRALTER
  */ 
      --not defined in draft
      GOTO Box_B_4
  
  Box_B_4: 
  /*Usual Place of Residence is in England B-4 
  TRUE if in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): General Medical Practice Code (Patient Registration) (GP Code) NOT IN ‘V81999’, ’V81998’, ’V81997’ AND The National Grouping for the GP Code is NOT ‘W00’ AND The National Grouping for the GP Code is NOT blank AND  The Status Code for the GP Code is ‘A’ or ‘P’ on the CDS Assignment Date (see section 5 of the source document – e.g. Use the Discharge Date for APC spells)
  
  --THIS READS AS AN "OR" STATEMENT, AS IN, IF ANY OF THE CONDITIONS ARE MET. TBC
   */
      IF @GPatEventDate in ('V81999','V81998','V81997') or Left(@GP_National_Grouping,3) = 'W00' or @GPStatusCode in ('A','P') --COULD BE REPLACED BY A P4 CALCULATION
      BEGIN
      SET @CommissionerCode = (select CCG_CODE from CDI_ODS_ALL_TRUST (NOLOCK)  WHERE ORGANISATION_CODE = @OrganisationCodeCodeofProvider) --OUTPUT: S2 HOST
      GOTO Box_Final
      END
      ELSE
      SET @CommissionerCode = @ResponsibleCCG  --OUTPUT: S3 RESPONSIBLE
      GOTO Box_Final
  
  Box_XB_1:
  /*Live in Wales and Registered with an English GP XB-1 
  TRUE if: Live in Wales (Next box) AND Registered with an English GP (Next but one box)
   */
       IF @POSTCODE_COUNTRY = 'WALES' AND @GP_National_Grouping like 'E%' --UPDATE THESE ONCE ONS DATA IS UPDATED.
       BEGIN
       GOTO BOX_C_0
       END
       ELSE 
       GOTO BOX_XB_2
  
  Box_XB_2:
  /*Usual Place of Residence in NI, Scotland or Wales XB-2 
  TRUE if in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): Postcode of Usual Address has a Country value of ‘N92000002’ OR ‘S92000003’ OR ‘W92000004’ in the ONS NHS Postcode Directory
   */
       IF @POSTCODE_COUNTRY = 'SCOTLAND' OR @POSTCODE_COUNTRY = 'NI' OR @POSTCODE_COUNTRY = 'WALES'  --UPDATE THESE ONCE ONS DATA IS UPDATED.
       BEGIN
       GOTO BOX_C
       END
       ELSE 
       GOTO BOX_C_0
  
  
  Box_C:
  /*Is the activity specialised or highly specialised C and C-0 
  For APC SUS+ will derive Prescribed Specialised Service Codes (PSS) and National Programme of Care (NPOC) at episode, whole spell and PbR spell level. Appropriate PSS and NPOC codes will be added to each OP record. The PbR Spell level PSS code, or OP record level PSS code, will be used to determine whether the spell/record qualifies as specialised or highly specialised.  Appropriate uplift to spell prices will be carried out. TRUE if the derived PSS Code is in the list of specialised or highly specialised codes 
  
  Yet to be detailed: Additional derived commissioner codes where unbundled chemotherapy, unbundled radiotherapy, or neonatal critical care is found. Grateful for any help/advice.
   */
       IF @ISPBR = 'N'
          BEGIN
          IF (SELECT ISNULL(NULLIF(IS_SCG_ACTIVITY,''),'N') FROM CDI_APC_CONSULTANT_EPISODE (NOLOCK)  WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber) ='Y'
              BEGIN
              GOTO BOX_C_1
              END
              ELSE
              SET @CommissionerCode = (select CLINICAL_COMMISSIONING_GROUP_CODE from CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY  (NOLOCK) WHERE POSTCODE = @PostcodeatEventDate) 
              GOTO BOX_Final
          END
          ELSE
          IF @Specialised_Level &gt; 0 --UPDATE THESE ONCE ONS DATA IS UPDATED.
              BEGIN
              GOTO BOX_C_1
              END
              ELSE
              SET @CommissionerCode = (select CLINICAL_COMMISSIONING_GROUP_CODE from CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY  (NOLOCK) WHERE POSTCODE = @PostcodeatEventDate) 
              GOTO BOX_Final
  
  Box_C_0:
  /*Is the activity specialised or highly specialised C and C-0 
  For APC SUS+ will derive Prescribed Specialised Service Codes (PSS) and National Programme of Care (NPOC) at episode, whole spell and PbR spell level. Appropriate PSS and NPOC codes will be added to each OP record. The PbR Spell level PSS code, or OP record level PSS code, will be used to determine whether the spell/record qualifies as specialised or highly specialised.  Appropriate uplift to spell prices will be carried out. TRUE if the derived PSS Code is in the list of specialised or highly specialised codes 
  
  Yet to be detailed: Additional derived commissioner codes where unbundled chemotherapy, unbundled radiotherapy, or neonatal critical care is found. Grateful for any help/advice.
   */
        IF @ISPBR = 'N'
          BEGIN
          IF (SELECT ISNULL(NULLIF(IS_SCG_ACTIVITY,''),'N') FROM CDI_APC_CONSULTANT_EPISODE (NOLOCK)  WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber) ='Y'
              BEGIN
              GOTO BOX_C_2
              END
              ELSE 
              GOTO BOX_D
          END
          ELSE
          IF @Specialised_Level &gt; 0 --UPDATE THESE ONCE ONS DATA IS UPDATED.
              BEGIN
              GOTO BOX_C_2
              END
              ELSE
              GOTO BOX_D
  
  
  Box_C_1:
  /*Highly Specialised Service C-1 
  TRUE if in any episode in the PbR spell: NHS Service Agreement Line Number is in Column B or C of Cross Border Flows for Specialised Services Decision needed.  Do we rely on incoming data or use the derived PSS codes – see previous box.  The derived code is at spell level not episode level.
   */
       IF @ISPBR = 'N'
       BEGIN
          SET @NHS_Service_Agreement_Line_Number = (SELECT SCG_SERVICE_LINE FROM CDI_APC_CONSULTANT_EPISODE (NOLOCK)  WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber)
          IF @NHS_Service_Agreement_Line_Number IN (SELECT Service_Line FROM IHealthBIConfig_SpecialisedServicesCrossborderFlows (NOLOCK) ) --USED AS A CHECK FOR CLOUMN B/C OF CROSS BORDER TABLE. PROBABLY REPLACE WITH A FLAG AT P4
          BEGIN
              SET @CommissionerCode = (SELECT Footprint_Code 
                                       FROM IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs  (NOLOCK) 
                                       WHERE IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs.Provider_Code = @OrganisationCodeCodeofProvider)
              GOTO BOX_Final
          END
          ELSE 
          IF @POSTCODE_COUNTRY ='scotland' --CONFIRM THIS CHECK
              BEGIN
              SET @CommissionerCode = 'SD002' --OUTPUT: S5
              GOTO BOX_Final
              END
              ELSE 
                  IF @POSTCODE_COUNTRY = 'WALES'--CONFIRM THIS CHECK
                      BEGIN
                      SET @CommissionerCode = '7A5HC' --OUTPUT: S5
                      GOTO BOX_Final
                      END
       END
       ELSE
       IF @NHS_Service_Agreement_Line_Number IN (SELECT Service_Line FROM IHealthBIConfig_SpecialisedServicesCrossborderFlows (NOLOCK) ) --USED AS A CHECK FOR CLOUMN B/C OF CROSS BORDER TABLE. PROBABLY REPLACE WITH A FLAG AT P4
          BEGIN
          SET @CommissionerCode = (SELECT Footprint_Code 
                                   FROM IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs  (NOLOCK) 
                                   WHERE IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs.Provider_Code = @OrganisationCodeCodeofProvider)
          GOTO BOX_Final
          END
          ELSE 
          IF @POSTCODE_COUNTRY ='scotland' --CONFIRM THIS CHECK
              BEGIN
              SET @CommissionerCode = 'SD002' --OUTPUT: S5
              GOTO BOX_Final
              END
              ELSE 
                  IF @POSTCODE_COUNTRY = 'WALES'--CONFIRM THIS CHECK
                      BEGIN
                      SET @CommissionerCode = '7A5HC' --OUTPUT: S5
                      GOTO BOX_Final
                      END
  
  Box_C_2:
  /*Does NHSE have a contract with the specialised service provider C-2 
  TRUE if: Organisation Code (Code of Provider) is in a list of Contracted Providers for Specialised Services (Any episode because Organisation Code (Code of Provider) is the same on all episodes in a spell. 
   */
       IF @OrganisationCodeCodeofProvider IN (SELECT Provider_Code FROM IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs) --APPENDIX J.
       BEGIN
       SET @CommissionerCode = (SELECT Footprint_Code 
                                FROM IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs  (NOLOCK) 
                                WHERE IHealthBIConfig_SpecialistServicesProvidersSpecialisedCommissioningHubs.Provider_Code = @OrganisationCodeCodeofProvider)
       GOTO Box_Final
       END
       ELSE 
       SET @CommissionerCode = @ResponsibleCCG  --OUTPUT: S3
       GOTO Box_Final
  
  
  Box_D:
  /*Is the activity for secondary dental care? D 
  TRUE if on any episode in the PbR spell the: Activity Treatment Function Code in in the list ‘140’, ‘141’, ‘142’, ‘143’, ‘144’, ‘217’, OR ‘450’
   */
       IF @ISPBR = 'N'
       BEGIN
            IF (SELECT 'Y' FROM CDI_APC_CONSULTANT_EPISODE  (NOLOCK) WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber AND CDI_APC_CONSULTANT_EPISODE.TREATMENT_FUNCTION_CODE IN ('140', '141', '142', '143', '144', '217','450')) = 'Y'    
           BEGIN
              IF (ISNULL(@GPatEventDate,'') = '' OR @GPatEventDate in ('V81999','V81998','V81997'))
              BEGIN
                  IF ISNULL(@PostcodeatEventDate,'') = ''
                      BEGIN
                      SET @CommissionerCode = (select NHS_AREA_TEAM_CODE from CDI_ODS_ALL_TRUST  (NOLOCK) JOIN CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY  (NOLOCK) on CDI_ODS_ALL_TRUST.POST_CODE = CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY.POSTCODE WHERE ORGANISATION_CODE = @OrganisationCodeCodeofProvider)
                      GOTO BOX_Final
                      END
                  ELSE
                  SET @CommissionerCode = (select NHS_AREA_TEAM_CODE from CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY  (NOLOCK) WHERE CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY.POSTCODE = @PostcodeatEventDate)
                  GOTO BOX_Final
              END
              ELSE
              SET @CommissionerCode = (SELECT HA_CODE FROM CDI_ODS_GENERAL_MEDICAL_PRACTICE (NOLOCK)  WHERE ORGANISATION_CODE = @GPatEventDate)
              GOTO BOX_Final 
           END
           ELSE 
           GOTO BOX_E
       END
       ELSE
           IF @IS_Secondary_Dental_Care = 'Y'  
           BEGIN
              IF (ISNULL(@GPatEventDate,'') = '' OR @GPatEventDate in ('V81999','V81998','V81997'))
              BEGIN
                  IF ISNULL(@PostcodeatEventDate,'') = ''
                      BEGIN
                      SET @CommissionerCode = (select NHS_AREA_TEAM_CODE from CDI_ODS_ALL_TRUST (NOLOCK)  JOIN CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY (NOLOCK)  on CDI_ODS_ALL_TRUST.POST_CODE = CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY.POSTCODE WHERE ORGANISATION_CODE = @OrganisationCodeCodeofProvider)
                      GOTO BOX_Final
                      END
                  ELSE
                  SET @CommissionerCode = (select NHS_AREA_TEAM_CODE from CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY (NOLOCK)  WHERE CDI_ODS_ONS_NHS_POSTCODE_DIRECTORY.POSTCODE = @PostcodeatEventDate)
                  GOTO BOX_Final
              END
              ELSE
              SET @CommissionerCode = (SELECT HA_CODE FROM CDI_ODS_GENERAL_MEDICAL_PRACTICE (NOLOCK)  WHERE ORGANISATION_CODE = @GPatEventDate)
              GOTO BOX_Final 
           END
           ELSE 
           GOTO BOX_E
  
  
  Box_E:
  /*Is the patient part of the eligible health and justice population? E 
  For “APC” TRUE if on the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): Postcode of Usual Address is in the list of  Justice Addresses and Commissioners AND Source of Admission (Hospital Provider Spell) = ‘39’ 
  
  For “OP” TRUE if on the Attendance date: Postcode of Usual Address is in the list of  Justice Addresses and Commissioners
   */
       IF @CDS_TYPE = 'OPS'
       BEGIN 
          IF @PostcodeatEventDate in (select Justice_Facility_Postcode from IHealthBIConfig_JusticeFacilityCode (NOLOCK) )
          BEGIN
          SET @CommissionerCode = (select NHS_England_Regional_Geography_with_Commissioning_Responsibility_Code from IHealthBIConfig_JusticeFacilityCode  (NOLOCK) where Justice_Facility_Postcode = @PostcodeatEventDate) 
          GOTO BOX_FINAL
          END
          ELSE GOTO BOX_F
       END
       ELSE
       IF @PostcodeatEventDate in (select Justice_Facility_Postcode from IHealthBIConfig_JusticeFacilityCode)
       AND @SourceofAdmissionHospitalProviderSpell = '39'
       BEGIN
       SET @CommissionerCode = (select NHS_England_Regional_Geography_with_Commissioning_Responsibility_Code from IHealthBIConfig_JusticeFacilityCode (NOLOCK)  where Justice_Facility_Postcode = @PostcodeatEventDate) 
       GOTO BOX_FINAL
       END
       ELSE GOTO BOX_F
  
  Box_F:
  /*Is the activity part of the agreed list of Public Health Section 7a services? 
  Unless guidance becomes clearer this will not be assessed in SUS+.
   */
       --Is the activity part of the agreed list of Public Health Section 7a services? Unless guidance becomes clearer this will not be assessed in SUS+.
  
       GOTO BOX_H
  
  Box_H:
  /*Is it other Public Health activity? H  
  Unless guidance becomes clearer this will not be assessed in SUS+
   */
       --Is it other Public Health activity?  Unless guidance becomes clearer this will not be assessed in SUS+.
  
       GOTO BOX_I
  
  Box_I:
  /*Is the activity for Infertility treatment? I 
  TRUE if for any episode in the PbR Spell: Any ICD diagnosis code is in an infertility code list OR Any OPCS procedure code is in an infertility code list 
   */
       IF @ISPBR = 'N'
       BEGIN
          IF 'Y' = (SELECT DISTINCT [Y] FROM (
                      SELECT 'Y' [Y] FROM CDI_APC_CONSULTANT_EPISODE_DIAGNOSIS  (NOLOCK) 
                      INNER JOIN [IHealthBIConfig_InfertilityDiagnosisProcedureCodes] (NOLOCK)  ON DIAGNOSIS = Code
                      WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber
                      UNION
                      SELECT 'Y' [Y] FROM CDI_APC_CONSULTANT_EPISODE_PROCEDURE (NOLOCK) 
                      INNER JOIN [IHealthBIConfig_InfertilityDiagnosisProcedureCodes]  (NOLOCK) ON [PROCEDURE] = Code
                      WHERE HOSPITAL_PROVIDER_SPELL_NUMBER = @HospitalProviderSpellNumber AND EPISODE_NUMBER = @EpisodeNumber)X)
          BEGIN
          GOTO BOX_I_1
          END
          ELSE 
          GOTO BOX_J
       END
       ELSE
       IF @IS_Infertility_treatment = 'Y' 
       BEGIN
          GOTO BOX_I_1
       END
       ELSE 
       GOTO BOX_J
  
  
  /**********THESE LAST 2 STEPS BASICALLY CHECK IF THE RESPONSIBLE CCG IS 13Q THEN SET TO 13Q, OTHERWISE USE THE RESPONSIBLE CCG. WHICH IS THE SAME AS JUST USING THE CCG. LEFT THESE BOXES IN AS THIS IS BASED ON A DRAFT VERSION AND MAY CHANGE*****/
  Box_I_1:
  /*Does the activity meet NHSE eligibility for Armed Forces? I-1 
  TRUE if in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): Organisation Code (Code of Commissioner) = ‘13Q’ 
   */
       IF @ResponsibleCCG = '13Q' --or  @PostcodeatEventDate LIKE 'BF1%' --GUIDANCE SAYS THAT IF THEY ARE ARMED FORCES
       BEGIN
       SET @CommissionerCode = '13Q' --OUTPUT: S12
       GOTO BOX_Final
       END
       ELSE 
       GOTO BOX_J --BASICALLY THE SAME CHECK?
  
  
  
  Box_J:
  /*DMS GP Practice Code Check J 
  TRUE if in the first episode of the spell (whole spell) as at the Start Date (Hospital Provider Spell): General Medical Practice Code (Patient Registration) when checked against GP Practices in England and Wales has ‘13Q’ in the Commissioner column AND  The postcode column (in GP Practices in England and Wales) has a Country value of ‘E92000001’ when checked in the ONS NHS Postcode Directory 
   */
       IF @ResponsibleCCG = '13Q' AND @POSTCODE_COUNTRY ='ENGLAND'
       BEGIN
       SET @CommissionerCode = @ResponsibleCCG  --OUTPUT: S12
       GOTO BOX_Final
       END
       ELSE 
       SET @CommissionerCode = @ResponsibleCCG --OUTPUT: S3
       GOTO BOX_Final
  
  
  
  
          /*______________________________________________
          **return the result
          **______________________________________________*/
          Box_Final:
          RETURN @CommissionerCode
  
      END
  GO
  </code></pre>