SELECT clnt.name,clntB.name,periodOf(pOHdr.orderDate),srcD.recNo,pODet.purchaseOrdHdrRecNo,clnt.recNo,clntB.recNo,srcU.recNo,srcU.replacementCost,srcU.requiredQty,sProd.*,prod.*,VAT.*,pODet.orderedQty,mrkUp.*
FROM purchaseOrdHdr AS pOHdr
    JOIN purchaseOrdDet AS pODet
         JOIN sourceDetUpgr AS srcU
              JOIN sourceDet AS srcD
                   JOIN sourceDetExtref AS srcE
                        JOIN clientBranch AS clntB
                             JOIN client AS clnt
                                  JOIN markUp AS mrkUp
                                  ON  mrkUp.foreignRecNo = clnt.recNo
                                  AND mrkUp.foreignTableName = 'client'
                             ON  clntB.clientRecNo   = clnt.recNo
                        ON  clntB.recNo          = srcE.ownerForeignRecNo
                   ON  srcE.foreignRecNo           = srcD.recNo
                   AND srcE.tableName              = 'sourcedet'
                   AND srcE.ownerForeignTableName  = 'clientbranch'
              ON  srcD.recNo = srcU.sourceDetRecNo
              JOIN supplierProduct AS sProd
                   JOIN product AS prod
                   ON prod.recNo  = sProd.productRecNo
                      JOIN VAT AS VAT
                      ON  sProd.VATRecNo          = VAT.recNo
              ON  sProd.recNo     = srcU.supplierProductRecNo
         ON  srcU.recNo       = pODet.sourceDetUpgrRecNo
    ON  pODet.purchaseOrdHdrRecNo    = pOHdr.recNo
WHERE pOHdr.orderDate BETWEEN '01/12/2005' AND '21/12/2005'
AND  1=1
AND pODet.lineStatus            != 'V'
AND srcD.actionStatus           != 'V'
AND srcD.serviceCoBranchRecNo    =  2
AND srcU.lineStatus         != 'V'
UNION
SELECT clnt.name,clntB.name,periodOf(srcD.enteredDate),srcD.recNo,NULL,clnt.recNo,clntB.recNo,srcU.recNo,
       srcU.replacementCost,srcU.requiredQty,sProd.*,prod.*,VAT.*,0,mrkUp.*
FROM sourceDet AS srcD
     JOIN sourceDetUpgr AS srcU
          JOIN supplierProduct AS sProd
               JOIN product AS prod
               ON sProd.productRecNo       = prod.recNo
               JOIN VAT AS VAT
               ON  sProd.VATRecNo          = VAT.recNo
          ON  srcU.supplierProductRecNo    = sProd.recNo
     ON  srcU.sourceDetRecNo   = srcD.recNo
     JOIN sourceDetExtref AS srcE
          JOIN clientBranch AS clntB
               JOIN client AS clnt
                    JOIN markUp AS mrkUp
                    ON  mrkUp.foreignRecNo = clnt.recNo
                    AND mrkUp.foreignTableName = 'client'
               ON  clntB.clientRecNo   = clnt.recNo
          ON  clntB.recNo              = srcE.ownerForeignRecNo
     ON  srcE.foreignRecNo           = srcD.recNo
     AND srcE.tableName              = 'sourcedet'
     AND srcE.ownerForeignTableName  = 'clientbranch'
WHERE srcD.enteredDate BETWEEN '01/12/2005' AND '21/12/2005'
AND  srcD.serviceCoBranchRecNo    = 2
AND   srcD.actionStatus            != 'V'
AND   srcU.lineStatus           = 'S'
AND   (SELECT COUNT(*)  FROM sourceDetUpgr AS srcU2
       WHERE srcU2.sourceDetRecNo = srcD.recNo
       AND   srcU2.lineStatus IN ('S','T')) = (SELECT COUNT(*) FROM sourceDetUpgr AS srcU2
                                               WHERE srcU2.sourceDetRecNo = srcD.recNo
                                               AND   srcU2.lineStatus != 'V')
AND  1=1
ORDER BY 3,2,1,4,5