Třídová akce

Popis:

Z výrobních dokladů, které mají aspoň jeden úkon zafixovaný, vrátí všechny ostatní nezafixované úkony.

Kód:

11

Tělo:

select wsid, pdid, basedjo, busid, bname, scid, sname from
(
select ws.id as wsid, JO.id as pdid, 1 as basedjo, JO.BUSORDER_ID as busid, BO.NAME AS BNAME, JON.STORECARD_ID as scid, SC.NAME AS SNAME --bratri fixovanych DUKu VP
from PLMWorkScheduleItems ws
    INNER JOIN PLMJobOrdersRoutines JOR ON (JOR.id = ws.JORoutine_id)
    INNER JOIN PLMJOOutputItems JOO ON (JOR.parent_id = JOO.ID)
    INNER JOIN PLMJONodes JON ON (JOO.owner_id = JON.ID)
    INNER JOIN PLMJobOrders JO ON (JON.parent_id = JO.ID)
    INNER JOIN PLMJOSetParsQueues JOQ ON (JO.docqueue_id = JOQ.docqueue_id)
    INNER JOIN PLMWorkPlaces WP ON (ws.Workplace_id = WP.ID)
    LEFT JOIN BUSORDERS BO ON (JO.BUSORDER_ID = BO.ID)
    INNER JOIN STORECARDS SC ON (JON.STORECARD_ID = SC.ID)
where (ws.status in (0,1) and not exists(select id from PLMWorkScheduleItemFixes where Parent_ID = ws.ID))
  and (ws.PlannedQuantity > (SELECT COALESCE(sum(Quantity),0) from PLMOperationWSIs WHERE WorkScheduleItem_id = ws.ID))
  and WP.CRPPlan > 0
  and JOR.Planned = 'A'
  and JOQ.InPlan = 'A'
  and JOR.TAC+JOR.TBC > 0
  and JO.id In
    ( select iJO.id
      from PLMWorkScheduleItems iws
          INNER JOIN PLMJobOrdersRoutines iJOR ON (iJOR.id = iws.JORoutine_id)
          INNER JOIN PLMJOOutputItems iJOO ON (iJOR.parent_id = iJOO.ID)
          INNER JOIN PLMJONodes iJON ON (iJOO.owner_id = iJON.ID)
          INNER JOIN PLMJobOrders iJO ON (iJON.parent_id = iJO.ID)
          INNER JOIN PLMJOSetParsQueues iJOQ ON (iJO.docqueue_id = iJOQ.docqueue_id)
          INNER JOIN PLMWorkPlaces iWP ON (iws.Workplace_id = iWP.ID)
      where ((iws.status in (0,1) and exists(select id from PLMWorkScheduleItemFixes where Parent_ID = iws.ID)) or (iws.status = 2))
        and (iws.PlannedQuantity > (SELECT COALESCE(sum(Quantity),0) from PLMOperationWSIs WHERE WorkScheduleItem_id = iws.ID))
        and iWP.CRPPlan > 0
        and iJOR.Planned = 'A'
        and iJOQ.InPlan = 'A'
        and iJOR.TAC+iJOR.TBC > 0
    )
union
select ws.id as wsid, Req.Id as pdid, 0 as basedjo, Req.BUSORDER_ID as busid, BO.NAME AS BNAME, ReqN.STORECARD_ID as scid, SC.NAME AS SNAME --bratri fixovanych DUKu Poz
from PLMWorkScheduleItems ws
    INNER JOIN PLMReqRoutines ReqR ON (ReqR.id = ws.ReqRoutine_id)
    INNER JOIN PLMReqOutputItems ReqO ON (ReqR.parent_id = ReqO.ID)
    INNER JOIN PLMReqNodes ReqN ON (ReqO.owner_id = ReqN.ID)
    INNER JOIN PLMProduceRequests Req ON (ReqN.parent_id = Req.ID)
    INNER JOIN PLMPQParams ReqQ ON (Req.docqueue_id = ReqQ.docqueue_id)
    INNER JOIN PLMWorkPlaces WP ON (ws.Workplace_id = WP.ID)
    LEFT JOIN BUSORDERS BO ON (Req.BUSORDER_ID = BO.ID)
    INNER JOIN STORECARDS SC ON (ReqN.STORECARD_ID = SC.ID)
where (ws.status in (0,1) and not exists(select id from PLMWorkScheduleItemFixes where Parent_ID = ws.ID))
  and (Req.joborder_id is null)
  and WP.CRPPlan > 0
  and ReqR.Planned = 'A'
  and ReqQ.InPlan = 'A'
  and ReqR.TAC+ReqR.TBC > 0
  and Req.id In
    ( select iReq.id
      from PLMWorkScheduleItems iws
          INNER JOIN PLMReqRoutines iReqR ON (iReqR.id = iws.ReqRoutine_id)
          INNER JOIN PLMReqOutputItems iReqO ON (iReqR.parent_id = iReqO.ID)
          INNER JOIN PLMReqNodes iReqN ON (iReqO.owner_id = iReqN.ID)
          INNER JOIN PLMProduceRequests iReq ON (iReqN.parent_id = iReq.ID)
          INNER JOIN PLMPQParams iReqQ ON (iReq.docqueue_id = iReqQ.docqueue_id)
          INNER JOIN PLMWorkPlaces iWP ON (iws.Workplace_id = iWP.ID)
      where iws.status in (0,1) and exists(select id from PLMWorkScheduleItemFixes where Parent_ID = iws.ID)
        and (iws.PlannedQuantity > (SELECT COALESCE(sum(Quantity),0) from PLMOperationWSIs WHERE WorkScheduleItem_id = iws.ID))
        and iWP.CRPPlan > 0
        and iReqR.Planned = 'A'
        and iReqQ.InPlan = 'A'
        and iReqR.TAC+iReqR.TBC > 0
    )
) A
order by basedjo, pdid, wsid

Generated by ABRA Software a.s. 27.10.2021 16:34:49