Cross Space Link Test

            select c.*,
            -- Perform the ct for target WIP calculation using the process time, sample rate, usage rate, utilization factor from the queueing model, and multiplier
            -- TCT_sec is based on several input factors (e.g. average lot size) whereas proc_sec is just a historical average of what ran
            -- MPU starts with the historical throughput, but then adds on cascading factors
            -- Proc_sec (not available by eqp_type) is not available from the capacity model tables so if you use the queueing model method you must use tct_sec,
            --  which is reasonable given that the queueing model relies on many capacity model inputs anyway
            (case when lb_target_basis = 'MPU' then mpu*avg_qty_per_carr_for_uph when lb_target_basis = 'TCT' then tct_sec_eqp_type / 60 else proc_sec_comp_full_wavg / 60 end )
          * least(lb_qmodel_max_xfactor, (est_smp_pct/100) * (est_use_pct/100) * decode(lb_target_method, 'QUEUEING MODEL', est_utilization_factor * variation_factor, 1) * target_wip_multiplier) as ct_mins_for_target_wip_final
            from
            -- Still one row per facility, route, step, eqp_type
            (
                select b.*,
                -- Calculate the queueing model utilization factor, add 1 to always include process time
                round(power(est_utilization_ratio_capped, sqrt(2 * (num_cap_ents + 1)) - 1) / (num_cap_ents * (1 - est_utilization_ratio_capped)), 4) as est_utilization_factor
                from
                -- This subquery results in one row per facility, route, step, eqp_type
                -- Eff mins can be summed since est_use_pct is already factored in
                (
                    select a.*,
                    eqp_type_eff_mins_per_day/eqp_type_avail_mins_per_days as est_utilization_ratio,
                    -- Capping the est_utilization_ratio avoids cases where demand exceeds capacity which leads to > exponential increases in the target wip
                    least(eqp_type_eff_mins_per_day/eqp_type_avail_mins_per_days, 0.95) as est_utilization_ratio_capped
                    from
                    (
                        select base.*, cm_rse.eqp_type, nvl(est_use_pct, 100) as est_use_pct, nvl(tct_sec, tct_sec_rrsp) as tct_sec_eqp_type,
                        nvl(mpu, 0) as mpu, nvl(num_cap_ents, 1) as num_cap_ents, cap_entity_list, nvl(variation_factor, 1) as variation_factor,
                        round(sum(nvl(eff_mpw, 0)) over (partition by base.facility, cm_rse.eqp_type), 4) as eqp_type_eff_mpw,
                        round(sum(nvl(eff_mpw, 0) * wip_qty_rt_rsec / greatest(rt_rsec_ct_days, 0.1)) over (partition by base.facility, cm_rse.eqp_type), 4) as eqp_type_eff_mins_per_day,
                        nvl(num_cap_ents, 1) * 24 * 60 * nvl(plan_util, 100) / 100 as eqp_type_avail_mins_per_days
                        from base
                        -- This join introduces a row per eqp_type used at each facility, route, step
                        left join cm_p_route_step_eqp cm_rse
                        on base.facility = cm_rse.facility and base.route = cm_rse.route and base.step = cm_rse.step
                        left join cm_p_eqp_types cm_et
                        on base.facility = cm_et.facility and cm_rse.eqp_type = cm_et.eqp_type
                        where is_staging_step = 'N'
                    ) a
                ) b
            ) c
            join gp_c_facility f
            on c.facility = f.facility
            join gen_facilities gf
            on f.facility = gf.facility