Cacher is the code snippet organizer for pro developers

-- Check if Temp Table exists and Drop it
IF OBJECT_ID('tempdb..#ReferencedTables') IS NOT NULL
BEGIN
    DROP TABLE #ReferencedTables
END
-- Check if Temp Table exists and Drop it
IF OBJECT_ID('tempdb..#DocSeries') IS NOT NULL
BEGIN
    DROP TABLE #DocSeries
END

-- Create Temp Table with the Referenced Tables
SELECT
    *
INTO #ReferencedTables
FROM
    (Select
  Case
    When DOCTYPES.HECODE Is Not Null Then Concat(DOCTYPES.TBL, ':',
    DOCTYPES.HECODE) Else DOCTYPES.HECODE End As DOCTYPE_CODE,
  Case
    When HEDOCUMENTSCENARIOS.HECODE Is Not Null Then
    Concat('HEDOCUMENTSCENARIOS', ':', HEDOCUMENTSCENARIOS.HECODE)
    Else HEDOCUMENTSCENARIOS.HECODE End As SCENARIO_CODE,
  Case
    When HECOMPANYBRANCHES.HECODE Is Not Null Then Concat('HECOMPANYBRANCHES',
    ':', HECOMPANYBRANCHES.HECODE) Else HECOMPANYBRANCHES.HECODE
  End As BRANCH_CODE,
  Case
    When HECOMPWAREHOUSES.HECODE Is Not Null Then Concat('HECOMPWAREHOUSES',
    ':', HECOMPWAREHOUSES.HECODE) Else HECOMPWAREHOUSES.HECODE End As WH_CODE,
  Case
    When ACCBRANCH.HECODE Is Not Null Then Concat(ACCBRANCH.TBL, ':',
    ACCBRANCH.HECODE) Else ACCBRANCH.HECODE End As ACCBRANCH_CODE,
  Case
    When HEPRICELISTS.HECODE Is Not Null Then Concat('HEPRICELIST', ':',
    HEPRICELISTS.HECODE) Else HEPRICELISTS.HECODE End As PRICELIST_CODE,
  Case
    When COMMDOC.HECODE Is Not Null Then Concat(COMMDOC.TBL, ':',
    COMMDOC.HECODE) Else COMMDOC.HECODE End As COMMDOC_CODE,
  Case
    When NUMDOC.HECODE Is Not Null Then Concat(NUMDOC.TBL, ':', NUMDOC.HECODE)
    Else NUMDOC.HECODE End As NUMDOC_CODE,
  Case
    When CANCELDOC.HECODE Is Not Null Then Concat(CANCELDOC.TBL, ':',
    CANCELDOC.HECODE) Else CANCELDOC.HECODE End As CANCELDOC_CODE,
  Case
    When PAYDOC.HECODE Is Not Null Then Concat(PAYDOC.TBL, ':', PAYDOC.HECODE)
    Else PAYDOC.HECODE End As PAYDOC_CODE,
  Case
    When TRANSFORMDOC.HECODE Is Not Null Then Concat(TRANSFORMDOC.TBL,
    ':', TRANSFORMDOC.HECODE) Else TRANSFORMDOC.HECODE End As TRANSFORM_CODE,
  Case
    When REPLACEDOC.HECODE Is Not Null Then Concat(REPLACEDOC.TBL, ':',
    REPLACEDOC.HECODE) Else REPLACEDOC.HECODE End As REPLACEDOC_CODE,
  Case
    When COMPOSITIONDOC.HECODE Is Not Null Then Concat(COMPOSITIONDOC.TBL, ':',
    COMPOSITIONDOC.HECODE) Else COMPOSITIONDOC.HECODE End As COMPOSITION_CODE,
  HECOMPANIES.HETIN As COMPANY_TIN,
  HEDOCSERIES.HECODE As SERIES_CODE
From
  HECOMPANIES Right Join
  HEDOCSERIES
    On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID Left Join
  (Select
    HECOMMDOCTYPES.HEID,
    HECOMMDOCTYPES.HECODE,
    HECOMMDOCTYPES.HENAME,
    'HECOMMDOCTYPES' As TBL
  From
    HECOMMDOCTYPES Inner Join
    HECOMPANIES
      On HECOMMDOCTYPES.HECOMPID = HECOMPANIES.HEID
  Union All
  Select
    HEFINDOCTYPES.HEID,
    HEFINDOCTYPES.HECODE,
    HEFINDOCTYPES.HENAME,
    'HEFINDOCTYPES' As TBL
  From
    HECOMPANIES Inner Join
    HEFINDOCTYPES
      On HEFINDOCTYPES.HECOMPID = HECOMPANIES.HEID) DOCTYPES
    On HEDOCSERIES.HEDOCTYPEID = DOCTYPES.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) COMMDOC
    On HEDOCSERIES.HECOMMDCSRID = COMMDOC.HEID Left Join
  HEDOCUMENTSCENARIOS
    On HEDOCSERIES.HEDSCNID = HEDOCUMENTSCENARIOS.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) NUMDOC
    On HEDOCSERIES.HECOMMONNUMDCSRID = NUMDOC.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) CANCELDOC
    On HEDOCSERIES.HECANCELLINGDCSRID = CANCELDOC.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) PAYDOC
    On HEDOCSERIES.HERECPAYDCSRID = PAYDOC.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) TRANSFORMDOC
    On HEDOCSERIES.HETRANSFORMDSCRID = TRANSFORMDOC.HEID Left Join
  HECOMPANYBRANCHES
    On HEDOCSERIES.HECMBRID = HECOMPANYBRANCHES.HEID Left Join
  HECOMPWAREHOUSES
    On HEDOCSERIES.HEPROPCBWHID = HECOMPWAREHOUSES.HEID Left Join
  (Select
    HECOMPANYBRANCHES.HEID,
    HECOMPANYBRANCHES.HECODE,
    HECOMPANYBRANCHES.HENAME,
    'HECOMPANYBRANCHES' As TBL
  From
    HECOMPANYBRANCHES Inner Join
    HECOMPANIES
      On HECOMPANYBRANCHES.HECOMPID = HECOMPANIES.HEID) ACCBRANCH
    On HEDOCSERIES.HEACCBRANCH = ACCBRANCH.HEID Left Join
  HEPRICELISTS
    On HEDOCSERIES.HEPRLSID = HEPRICELISTS.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) REPLACEDOC
    On HEDOCSERIES.HEREPLACEDSCRID = REPLACEDOC.HEID Left Join
  (Select
    HEDOCSERIES.HEID,
    HEDOCSERIES.HECODE,
    HEDOCSERIES.HENAME,
    'HEDOCSERIES' As TBL
  From
    HEDOCSERIES Inner Join
    HECOMPANIES
      On HEDOCSERIES.HECOMPID = HECOMPANIES.HEID) COMPOSITIONDOC
    On HEDOCSERIES.HECOMPODSCRID = COMPOSITIONDOC.HEID) AS REF_TBL

-- Which Company and Which DOC Series
WHERE
    REF_TBL.COMPANY_TIN = '094180111' AND
    REF_TBL.SERIES_CODE LIKE 'RWH%'

-- Create a Temp Table with all the DocSeries Required by the DocSeries we want to import
SELECT
    *
INTO #DocSeries
FROM (SELECT
        COMMDOC_CODE AS r
    FROM #ReferencedTables
    WHERE COMMDOC_CODE IS NOT NULL
    UNION
    SELECT
        NUMDOC_CODE AS r
    FROM #ReferencedTables
    WHERE NUMDOC_CODE IS NOT NULL
    UNION
    SELECT
        CANCELDOC_CODE AS r
    FROM #ReferencedTables
    WHERE CANCELDOC_CODE IS NOT NULL
    UNION
    SELECT
        PAYDOC_CODE AS r
    FROM #ReferencedTables
    WHERE PAYDOC_CODE IS NOT NULL
    UNION
    SELECT
        REPLACEDOC_CODE AS r
    FROM #ReferencedTables
    WHERE REPLACEDOC_CODE IS NOT NULL
    UNION
    SELECT
        COMPOSITION_CODE AS r
    FROM #ReferencedTables
    WHERE COMPOSITION_CODE IS NOT NULL) AS DOC_SERIES

SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM #DocSeries

-- Select Only the DocTypes required
SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM (
    SELECT DISTINCT
        DOCTYPE_CODE AS r
    FROM #ReferencedTables
    WHERE DOCTYPE_CODE IS NOT NULL) AS DOCTYPE

-- Select Only the Scenarios required
SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM (
    SELECT DISTINCT
        SCENARIO_CODE AS r
    FROM #ReferencedTables
    WHERE SCENARIO_CODE IS NOT NULL) AS SCENARIO

-- Select Only the Branches required
SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM (
    SELECT DISTINCT
        BRANCH_CODE AS r
    FROM #ReferencedTables
    WHERE BRANCH_CODE IS NOT NULL) AS BRANCH

-- Select Only the WareHouse required
SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM (
    SELECT DISTINCT
        WH_CODE AS r
    FROM #ReferencedTables
    WHERE WH_CODE IS NOT NULL) AS WH

-- Select Only the AccountingBranch required
SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM (
    SELECT DISTINCT
        ACCBRANCH_CODE AS r
    FROM #ReferencedTables
    WHERE ACCBRANCH_CODE IS NOT NULL) AS ACCBRANCH

-- Select Only the PriceList required
SELECT
    LEFT(r, CHARINDEX(':',r)-1) AS TBL,
    RIGHT(r, LEN(r)-CHARINDEX(':',r)) AS HECODE
FROM (
    SELECT DISTINCT
        PRICELIST_CODE AS r
    FROM #ReferencedTables
    WHERE PRICELIST_CODE IS NOT NULL) AS PRICELIST

-- Drop Temp Tables
DROP TABLE #ReferencedTables
DROP TABLE #DocSeries