ssp_inputs

ssp_inputs

-- 🏥🏥🏥
-- Consumer Pricing Service 
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ ps_schema }}.pricing_service_inputs_ssp_lines_{{ ps_month }} 
-- Subversion: {{ ps_month }}

--------------------------------
-- Purpose:
-- This table pulls in all relevant SSP lines for the pricing service.
-- It includes HCPCS lines from outpatient SSPs and MS-DRG lines from inpatient SSPs.
--------------------------------

CREATE TABLE IF NOT EXISTS {{ ps_schema }}.pricing_service_inputs_ssp_lines_{{ ps_month }} AS 

--------------------------------
-- OUTPATIENT:
--------------------------------

SELECT 
    distinct 
    c.ssp_id, 
    n.ssp_name, 
    c.sub_package_id,
    s.line_code,
    'HCPCS' as line_code_type,
    CASE
        WHEN s.base_code = s.line_code THEN 1
        WHEN s.average_units <= 1 THEN 1
        ELSE s.average_units
    END as average_units,
    CASE
        WHEN s.fee_type = 'Professional Fee' THEN 'professional'
        ELSE 'facility'
    END AS fee_type
FROM {{ tables.SSP_SUB_PACKAGES.value }} c
LEFT JOIN {{ tables.SUB_PACKAGE_CONTENTS.value }} s
    ON s.sub_package_id = c.sub_package_id
LEFT JOIN {{ tables.SSPS_HISTORY.value }} n
    ON c.ssp_id = n.ssp_id
WHERE 
s.fee_type IN ('Professional Fee', 'Facility Fee', 'Optional Fee')
AND item_type = 'package_line'
AND c.version = {{ ssp_version }}
AND s.version = {{ ssp_version }}
AND n.version = {{ ssp_version }}
AND c.ssp_id IN ('{{ ssps | join("', '") }}')
AND c.ssp_id NOT IN ('OB001', 'OB002')
-- Exclude ED codes
AND s.line_code NOT IN (
    '99281', '99282', '99283', '99284', '99285', 
    '99288',
    'G0380', 'G0381', 'G0382', 'G0383', 'G0384',
    'Z7502'
)

UNION ALL

--------------------------------
-- INPATIENT: ADD MS-DRGs
--------------------------------
SELECT 
    distinct 
    c.ssp_id,
    s.ssp_name,
    c.sub_package_ids,
    c.line_code,
    'MS-DRG' as line_code_type,
    NULL as average_units,
    CASE
        WHEN c.fee_type = 'Professional Fee' THEN 'professional'
        ELSE 'facility'
    END AS fee_type
FROM {{ tables.SSP_CONTENTS_HISTORY.value }} c
LEFT JOIN {{ tables.SSPS_HISTORY.value }} s
    ON s.ssp_id = c.ssp_id
WHERE (
    c.fee_type IN ('Facility Fee', 'Optional Fee', 'Base Code', 'Professional Fee')
)
AND c.code_type = 'MS-DRG'
AND c.ssp_id IN ('OB001', 'OB002')
AND c.version = {{ ssp_version }}
AND s.version = {{ ssp_version }}
-- Exclude ED codes
AND c.line_code NOT IN (
    '99281', '99282', '99283', '99284', '99285', 
    '99288',
    'G0380', 'G0381', 'G0382', 'G0383', 'G0384',
    'Z7502'
)