Třídová akce

Popis:

Vrátí OID servisních listů pro zadané txt ID, případně skladovou kartu a výrobce

Kód:

10

Tělo:

SELECT A.StoreCard_ID, A.ServicedObjectSerialNr
FROM ServiceDocuments A
LEFT JOIN StoreCards SC ON SC.ID=A.StoreCard_ID
LEFT JOIN Firms F ON F.ID = SC.Producer_ID
LEFT JOIN Firms F2 ON F2.ID = A.Firm_ID
LEFT JOIN Firms F3 ON F3.ID = A.PayerFirm_ID
WHERE
  (A.ObjectKind = :KIND) and
  (upper(A.ServicedObjectSerialNr collate PXW_CSY) LIKE (upper('{ATxtSerial}')) ESCAPE '~') and
  (Upper(SC.Name collate PXW_CSY) LIKE Upper('{SEARCHCARD}') ESCAPE '~') AND
  ((SC.Category < 3) or ('{SEARCHCARD}'='%%')) AND
  ((Upper(F.Name collate PXW_CSY) LIKE Upper('{SEARCHPRODUCER}') ESCAPE '~') OR 
   (('{SEARCHPRODUCER}'='%%') AND SC.Producer_ID is null) OR
   ((:ProducerWithAncestors='A') AND
    (SC.Producer_ID in
     (SELECT F1.ID FROM Firms F1
      WHERE
       (SC.Producer_ID = F1.ID) and
       (F1.Firm_ID in 
        (SELECT F12.ID FROM Firms F12 
         where (Upper(F12.Name collate PXW_CSY) LIKE Upper('{SEARCHPRODUCER}') ESCAPE '~')
        )
       )
     )
    ) 
   )
  ) AND
  ((Upper(F2.Name collate PXW_CSY) LIKE Upper('{SEARCHFIRM}') ESCAPE '~') OR
   (Upper(F3.Name collate PXW_CSY) LIKE Upper('{SEARCHFIRM}') ESCAPE '~') OR
   ((:ClientWithAncestors='A') AND
    (
     (A.Firm_ID in
      (SELECT F11.ID FROM Firms F11
       WHERE
        (A.Firm_ID = F11.ID) and
        (F11.Firm_ID in 
         (SELECT F111.ID FROM Firms F111 
          where (Upper(F111.Name collate PXW_CSY) LIKE Upper('{SEARCHFIRM}') ESCAPE '~')
         )
        )
      )
     ) OR
     (A.PayerFirm_ID in
      (SELECT F21.ID FROM Firms F21
       WHERE
        (A.PayerFirm_ID = F21.ID) and
        (F21.Firm_ID in 
         (SELECT F211.ID FROM Firms F211 
          where (Upper(F211.Name collate PXW_CSY) LIKE Upper('{SEARCHFIRM}') ESCAPE '~')
         )
        )
      )
     )
    )
   )
  )    
GROUP BY A.StoreCard_ID, A.ServicedObjectSerialNr  

Generated by ABRA Software a.s. 27.10.2021 16:35:46