DECLARE
  s CLOB := '
-- MAX DATE IS {{max_dt}}
{{#hub}}
SELECT  ptz.{{hub_code}}_id
      , ptz.point_dt AS start_dt
      , COALESCE (MAX (ptz.point_dt) OVER (PARTITION BY ptz.{{hub_code}}_id ORDER BY ptz.point_dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - 1, DATE ''{{max_dt}}'') AS end_dt
      {{#sat}}{{#atr}}, {{sat_code}}.{{atr_name}}{{/atr}}{{/sat}}
FROM  ( {{#sat}}
        {{^first_element}}
        UNION ALL
        {{/first_element}}
        SELECT  {{hub_code}}_id, start_dt AS point_dt
        FROM    {{hub_code}}_{{sat_code}}
        UNION   ALL
        SELECT  {{hub_code}}_id, end_dt + 1 AS point_dt
        FROM    {{hub_code}}_{{sat_code}}
        WHERE   ent_dt <> DATE ''{{max_dt}}''
        {{/sat}}
      ) ptz
      {{#sat}}
      LEFT JOIN {{hub_code}}_{{sat_code}} {{sat_code}} ON {{sat_code}}.{{hub_code}}_id = ptz.{{hub_code}}_id AND ptz.point_dt BETWEEN {{sat_code}}.start_dt AND {{sat_code}}.end_dt
      {{/sat}}
{{/hub}}';

  j CLOB := '
{ "max_dt" : "9999-12-31"
, "hub" :
  [ { "hub_code" : "agrret"
    , "sat" :
      [ { "sat_code" : "cond"
        , "atr" :
          [ { "atr_name" : "cond_1_val" }
          , { "atr_name" : "cond_2_val" }
          ]
        }
      , { "sat_code" : "proc"
        , "atr" :
          [ { "atr_name" : "proc_1_val" }
          , { "atr_name" : "proc_2_val" }
          , { "atr_name" : "proc_3_val" }
          ]
        }
      ]
    }
  , { "hub_code" : "acct"
    , "sat" :
      [ { "sat_code" : "h"
        , "atr" :
          [ { "atr_name" : "h_1_val" }
          , { "atr_name" : "h_2_val" }
          , { "atr_name" : "h_3_val" }
          , { "atr_name" : "h_4_val" }
          ]
        }
      ]
    }
  ]
}';

BEGIN
  APEX_BI.m_te_pkg.p_parse (s);
  DBMS_OUTPUT.put_line (APEX_BI.m_te_pkg.f_process (j));
END;
/