Skip to content

Instantly share code, notes, and snippets.

@scepion1d

scepion1d/gist:5352764

Last active September 11, 2020 19:28
Show Gist options
  • Save scepion1d/5352764 to your computer and use it in GitHub Desktop.
Save scepion1d/5352764 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.sql
select mdfr_list.material_id,
mdfr_list.unit_id,
mdfr_list.unit_name,
mdfr_list.oper_unit_sys,
mdfr_list.oper_unit_sys_mdfr,
mdfr_list.oper_sys_mdfr,
mdfr_list.oper_unit_sys_spec_cond,
mdfr_list.oper_unit_tech,
mdfr_list.oper_unit_tech_mdfr,
mdfr_list.oper_tech_mdfr,
mdfr_list.oper_unit_tech_spec_cond,
mdfr_list.oper_unit_all,
mdfr_list.oper_unit_all_mdft,
mdfr_list.oper_unit_all_cpec_cond
from ((select distinct -- выходные карманы
mrs.no as route_step_no,
m.material_id as material_id,
u.unit_id as unit_id,
u.name as unit_name,
ou_sys.operation_unit_id as oper_unit_sys,
mm_ou_sys.material_modifier_id as oper_unit_sys_mdfr,
mm_o_sys.material_modifier_id as oper_sys_mdfr,
ou_sys.special_condition as oper_unit_sys_spec_cond,
ou_tech.operation_unit_id as oper_unit_tech,
oum_tech.material_modifier_id as oper_unit_tech_mdfr,
om_tech.material_modifier_id as oper_tech_mdfr,
ou_tech.special_condition as oper_unit_tech_spec_cond,
ou_all.operation_unit_id as oper_unit_all,
mm_ou_all.material_modifier_id as oper_unit_all_mdft,
ou_all.special_condition as oper_unit_all_cpec_cond
from material_route_step mrs
-- Обрабатываемый материал
join material m on mrs.material_id = m.material_id
-- Юнит текущего шага материала
join unit u on mrs.unit_id = u.unit_id
-- Тех. операция
left join operation tech on mrs.tech_operation_id = tech.operation_id
-- Системная операция
left join operation sys on mrs.operation_id = sys.operation_id
-- OperUnit, подходящий по обеим операциям
left join operation_unit ou_all on ou_all.unit_id = u.unit_id
and ou_all.tech_operation_id = tech.operation_id
and ou_all.operation_id = sys.operation_id
-- Связка OperUnit'a с модифаером
left join operation_unit_mdfr oum_all on oum_all.operation_unit_id = ou_all.operation_unit_id
-- Модифаер на обе операции
left join material_modifier mm_ou_all on oum_all.material_modifier_id = mm_ou_all.material_modifier_id
-- OperUnit, подходящий по системной операции
left join operation_unit ou_sys on ou_sys.unit_id = u.unit_id
and ou_sys.operation_id = sys.operation_id
and nvl(tech.operation_id, -2) != nvl(ou_sys.tech_operation_id, -1)
-- Связка OperUnit'a с модифаером
left join operation_unit_mdfr oum_sys on oum_sys.operation_unit_id = ou_sys.operation_unit_id
-- Модифаер OperUnitа на системную операцию
left join material_modifier mm_ou_sys on oum_sys.material_modifier_id = mm_ou_sys.material_modifier_id
-- Связка операции с модифаером
left join operation_mdfr om_sys on om_sys.operation_id = ou_sys.operation_id
and om_sys.material_modifier_id is not null
-- Модифаер на системную операцию
left join material_modifier mm_o_sys on om_sys.material_modifier_id = mm_o_sys.material_modifier_id
-- OperUnit, подходящий по тех. операции
left join operation_unit ou_tech on ou_tech.unit_id = u.unit_id
and ou_tech.tech_operation_id = tech.operation_id
and nvl(sys.operation_id, -2) != nvl(ou_tech.operation_id, -1)
-- Модифаер OperUnitа на тех. операцию
left join operation_unit_mdfr oum_tech on oum_tech.operation_unit_id = ou_tech.operation_unit_id
-- Модифаер OperUnitа на тех. операцию
left join material_modifier mm_ou_tech on oum_tech.material_modifier_id = mm_ou_tech.material_modifier_id
-- Модифаер на тех. операцию
left join operation_mdfr om_tech on om_tech.operation_id = ou_tech.tech_operation_id
and om_tech.material_modifier_id is not null
-- Модифаер на тех. операцию
left join material_modifier mm_o_tech on om_tech.material_modifier_id = mm_o_tech.material_modifier_id
where (ou_sys.operation_unit_id is not null or
ou_tech.operation_unit_id is not null or
ou_all.operation_unit_id is not null)
and (oum_sys.material_modifier_id is not null or
oum_tech.material_modifier_id is not null or
oum_all.material_modifier_id is not null or
om_sys.material_modifier_id is not null or
om_tech.material_modifier_id is not null)
and (mm_o_tech.type = 'Order' or mm_ou_tech.type = 'Order' or
mm_o_sys.type = 'Order' or mm_ou_sys.type = 'Order' or
mm_ou_all.type = 'Order')
and m.no = '400705464') union
(select distinct -- загрузочные карманы
pmrs.no as route_step_no,
pmrs.material_id as material_id,
p.unit_id as unit_id,
p.name as unit_name,
ou_sys.operation_unit_id as oper_unit_sys,
mm_ou_sys.material_modifier_id as oper_unit_sys_mdfr,
mm_o_sys.material_modifier_id as oper_sys_mdfr,
ou_sys.special_condition as oper_unit_sys_spec_cond,
ou_tech.operation_unit_id as oper_unit_tech,
oum_tech.material_modifier_id as oper_unit_tech_mdfr,
om_tech.material_modifier_id as oper_tech_mdfr,
ou_tech.special_condition as oper_unit_tech_spec_cond,
ou_all.operation_unit_id as oper_unit_all,
mm_ou_all.material_modifier_id as oper_unit_all_mdft,
ou_all.special_condition as oper_unit_all_cpec_cond
from material_route_step mrs
-- Исходный материал
join material m on mrs.material_id = m.material_id
-- Юнит текущего шага материала
join unit u on mrs.unit_id = u.unit_id
-- Связка юнита с его загрузочным карманом
join unit_relation ur on ur.child_unit_id = u.unit_id
-- Загрузочный карма
join unit p on ur.parent_unit_id = p.unit_id
-- Операции с шагов родитлеского материала, который проходил через загрузочный карман
join (select mrsh.no,
mrsh.material_id,
mrsh.child_material_id,
mrsh.operation_id,
mrsh.tech_operation_id,
mrsh.unit_id
from (select mrh.child_material_id, mrsh.* -- Удаленные шаги родительского материала
from material_route_step_h mrsh,
material_relation_h mrh
where not exists
(select 1 -- Наличине существующей связки материала
from material_relation mr
where mr.parent_material_id = mrh.parent_material_id)
and mrsh.material_id = mrh.parent_material_id
and mrsh.change_type = 'D') mrsh) pmrs on pmrs.unit_id = p.unit_id
and pmrs.unit_id = p.unit_id
and pmrs.child_material_id = m.material_id
-- Тех. операция
left join operation tech on pmrs.tech_operation_id = tech.operation_id
-- Системная операция
left join operation sys on pmrs.operation_id = sys.operation_id
-- OperUnit, подходящий по обеим операциям
left join operation_unit ou_all on ou_all.unit_id = p.unit_id
and ou_all.tech_operation_id = tech.operation_id
and ou_all.operation_id = sys.operation_id
-- Связка OperUnit'a с модифаером
left join operation_unit_mdfr oum_all on oum_all.operation_unit_id = ou_all.operation_unit_id
-- Модифаер на обе операции
left join material_modifier mm_ou_all on oum_all.material_modifier_id = mm_ou_all.material_modifier_id
-- OperUnit, подходящий по системной операции
left join operation_unit ou_sys on ou_sys.unit_id = p.unit_id
and ou_sys.operation_id = sys.operation_id
and nvl(tech.operation_id, -2) != nvl(ou_sys.tech_operation_id, -1)
-- Связка OperUnit'a с модифаером
left join operation_unit_mdfr oum_sys on oum_sys.operation_unit_id = ou_sys.operation_unit_id
-- Модифаер OperUnitа на системную операцию
left join material_modifier mm_ou_sys on oum_sys.material_modifier_id = mm_ou_sys.material_modifier_id
-- Связка операции с модифаером
left join operation_mdfr om_sys on om_sys.operation_id = ou_sys.operation_id
and om_sys.material_modifier_id is not null
-- Модифаер на системную операцию
left join material_modifier mm_o_sys on om_sys.material_modifier_id = mm_o_sys.material_modifier_id
-- OperUnit, подходящий по тех. операции
left join operation_unit ou_tech on ou_tech.unit_id = p.unit_id
and ou_tech.tech_operation_id = tech.operation_id
and nvl(sys.operation_id, -2) != nvl(ou_tech.operation_id, -1)
-- Модифаер OperUnitа на тех. операцию
left join operation_unit_mdfr oum_tech on oum_tech.operation_unit_id = ou_tech.operation_unit_id
-- Модифаер OperUnitа на тех. операцию
left join material_modifier mm_ou_tech on oum_tech.material_modifier_id = mm_ou_tech.material_modifier_id
-- Модифаер на тех. операцию
left join operation_mdfr om_tech on om_tech.operation_id = ou_tech.tech_operation_id
and om_tech.material_modifier_id is not null
-- Модифаер на тех. операцию
left join material_modifier mm_o_tech on om_tech.material_modifier_id = mm_o_tech.material_modifier_id
where (ou_sys.operation_unit_id is not null or
ou_tech.operation_unit_id is not null or
ou_all.operation_unit_id is not null)
and (oum_sys.material_modifier_id is not null or
oum_tech.material_modifier_id is not null or
oum_all.material_modifier_id is not null or
om_sys.material_modifier_id is not null or
om_tech.material_modifier_id is not null)
and (mm_o_tech.type = 'Order' or mm_ou_tech.type = 'Order' or
mm_o_sys.type = 'Order' or mm_ou_sys.type = 'Order' or
mm_ou_all.type = 'Order')
and m.no = '400705464')) mdfr_list
order by mdfr_list.material_id, mdfr_list.route_step_no;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment