public List<OrderForPEEM> GetTransactionData(Short printed, Integer transactionTypeId, boolean reTransmit) throws Exception {
        List<OrderForPEEM> result = null;
        Query q = null;
        try {
            if (transactionTypeId.equals(Integer.valueOf(813))) {
                if (!reTransmit) {
                    q = em.createQuery("select new OrderForPEEM( " +
                            "-1 as idComandaClient, IsNull(CPT.pEEMOrderNumber, A.numar) as numar, IsNull(PL.idPunctLucru, AO.idPunctLucru) as idPLClient, IsNull(Tour.rampId, IsNull(RL.idRampa, 0)) as idRampa, IsNull(Tour.tourId, RL.idRutaLivrare) as idRutaLivrare, SS.dataLivrare, " +
                            "IsNull(ST.codStatie, 0) as codStatie, P.idProdus, SS.lot, SS.bbd, C.fullName, sum(SS.cantUV), IT.idElemDic, IsNull(P.cantBax, 0) as cantBax, " +
                            "IsNull(P.isXXL, 0) as isXXL, IsNull(P.isFrig, 0) as isFrig, IsNull(P.isVenena, 0) as isVenena, IsNull(P.isSeparanda, 0) as isSeparanda, IsNull(EC.nume, ' ') as geocode2, IsNull(SS.idClient, 0) as idClient, " +
                            "IsNull(SS.isTiparit, 0) as isTiparit, IsNull(Tour.tourId, 0) as tourId, IsNull(SS.idDivizie, 0) as idDivizie, IsNull(REL.deliveryDate, '19000101') as deliveryDate, " +
                            "IsNull(REL.operationalDate, '19000101') as operationalDate, " +
                            "AO.avizOutPK.idSpatiu as siteId, SS.idTran as transactionId, SS.idTipTran as transactionTypeId, " +
                            "IsNull(G.idGestiune, 0) as gestId, IsNull(AO.documentReceptionId, 0) as documentReceptionId, IsNull(AO.productId, 0) as productId, " +
                            "IsNull(MGTO.masterGroupId, 0) as masterGroupTransferOrderId, IsNull(MGTO.masterGroupIdPK, 0) as masterGroupTransferOrderIdPK, IsNull(CTO.withFirmOrder, 0) as withFirmOrder " +
                            " ) " +
                            "from ScoatereStoc as SS " +
                            "inner join SS.tranzactie as T " +
                            "inner join T.aviz as A " +
                            "inner join A.avizOut as AO " +
                            "inner join T.confirmedTransaction as CT " +
                            "inner join T.gestiune as G " +
                            "inner join AO.idPartener as PL " +
                            "inner join SS.celula as C " +
                            "left join C.statie as ST " +
                            "inner join C.idTipCulegere as IT " +
                            "inner join SS.produs as P " +
                            "left join PL.idRutaLivrare as RL " +
                            "left join C.extensieCelula as EC " +
                            "inner join AO.releasedPEEMTransaction as REL " +
                            "left join REL.tour as Tour " +
                            "left join REL.cumulatedPEEMTransactionXReleasedPEEMTransactionCollection as cumulatedPEEMTransactionXReleasedPEEMTransaction " +
                            "left join cumulatedPEEMTransactionXReleasedPEEMTransaction.cumulatedPEEMTransaction as CPT " +

                            "left join AO.cumulatedTransferOrder as CTO " +
                            "left join CTO.charactGroupTransferOrder as CGTO " +
                            "left join CGTO.masterGroupTransferOrderXCharactGroupTransferOrder as MGTOXCGTO " +
                            "left join MGTOXCGTO.masterGroupTransferOrder as MGTO " +

                            "where IsNull(SS.isTiparit, 0) = :printed " +
                            "  and AO.avizOutPK.idSpatiu = " + GetInitInfoConstant("HostIdSpatiu") + " " +
                            "  and SS.idTipTran = :transactionTypeId " +
                            "  AND cumulatedPEEMTransactionXReleasedPEEMTransaction.cumulatedPEEMTransactionXReleasedPEEMTransactionPK.transactionId IS NULL "  + " " +
                            "group by AO.avizOutPK.idSpatiu, SS.idTran, SS.idTipTran, CPT.pEEMOrderNumber, A.numar, PL.idPunctLucru, AO.idPunctLucru, Tour.rampId, RL.idRampa, Tour.tourId, RL.idRutaLivrare, SS.dataLivrare, " +
                            "         ST.codStatie, P.idProdus, SS.lot, SS.bbd, C.fullName, IT.idElemDic, P.cantBax, P.isXXL, P.isFrig, " +
                            "         P.isVenena, P.isSeparanda, EC.nume, SS.idClient, SS.isTiparit, SS.idDivizie, REL.deliveryDate, REL.operationalDate, G.idGestiune, AO.documentReceptionId, AO.productId, " +
                            "         IsNull(MGTO.masterGroupId, 0), IsNull(MGTO.masterGroupIdPK, 0), IsNull(CTO.withFirmOrder, 0) " +
                            "order by AO.avizOutPK.idSpatiu, SS.idTran, SS.idTipTran, G.idGestiune ");

                    q.setParameter("transactionTypeId", transactionTypeId);
                    q.setParameter("printed", printed);
                    result = q.getResultList();
                } else {
                    q = em.createQuery("select new OrderForPEEM( " +
                            "-1 as idComandaClient, IsNull(CPT.pEEMOrderNumber, A.numar) as numar, IsNull(CPT.deliveryPlaceId, IsNull(PL.idPunctLucru, AO.idPunctLucru)) as idPLClient, IsNull(Tour.rampId, IsNull(RL.idRampa, 0)) as idRampa, IsNull(Tour.tourId, RL.idRutaLivrare) as idRutaLivrare, SS.dataLivrare, " +
                            "IsNull(ST.codStatie, 0) as codStatie, P.idProdus, SS.lot, SS.bbd, C.fullName, sum(SS.cantUV), IT.idElemDic, IsNull(P.cantBax, 0) as cantBax, " +
                            "IsNull(P.isXXL, 0) as isXXL, IsNull(P.isFrig, 0) as isFrig, IsNull(P.isVenena, 0) as isVenena, IsNull(P.isSeparanda, 0) as isSeparanda, IsNull(EC.nume, ' ') as geocode2, IsNull(SS.idClient, 0) as idClient, " +
                            "IsNull(SS.isTiparit, 0) as isTiparit, 0 as tourId, IsNull(SS.idDivizie, 0) as idDivizie, IsNull(CPT.deliveryDate, '19000101') as deliveryDate, " +
                            "IsNull(CPT.operationalDate, '19000101') as operationalDate, CPT.cumulatedPEEMTransactionPK.siteId as siteId, " +
                            "CPT.cumulatedPEEMTransactionPK.cumulatedTransactionId as transactionId, CPT.transactionTypeId as transactionTypeId, IsNull(CPT.gestId, 0) as gestId, " +
                            "IsNull(CPT.documentReceptionId, 0) as documentReceptionId, IsNull(CPT.productId, 0) as productId, " +
                            "IsNull(CPT.masterGroupTransferOrderId, 0) as masterGroupTransferOrderId, IsNull(CPT.masterGroupTransferOrderIdPK, 0) as masterGroupTransferOrderIdPK, IsNull(CPT.withFirmOrder, 0) as withFirmOrder " +
                            " ) " +
                            "from ScoatereStoc as SS " +
                            "inner join SS.tranzactie as T " +
                            "inner join T.aviz as A " +
                            "inner join A.avizOut as AO " +
                            "inner join T.confirmedTransaction as CT " +
                            "inner join AO.idPartener as PL " +
                            "inner join SS.celula as C " +
                            "left join C.statie as ST " +
                            "inner join C.idTipCulegere as IT " +
                            "inner join SS.produs as P " +
                            "left join PL.idRutaLivrare as RL " +
                            "left join C.extensieCelula as EC " +
                            "inner join AO.releasedPEEMTransaction as REL " +
                            "inner join REL.cumulatedPEEMTransactionXReleasedPEEMTransactionCollection as cumulatedPEEMTransactionXReleasedPEEMTransaction " +
                            "inner join cumulatedPEEMTransactionXReleasedPEEMTransaction.cumulatedPEEMTransaction as CPT " +
                            "left join CPT.cumulatedTour as Tour " +
                            "where IsNull(SS.isTiparit, 0) = :printed " +
                            "  and CPT.cumulatedPEEMTransactionPK.siteId = " + GetInitInfoConstant("HostIdSpatiu") + " " +
                            "  and CPT.transactionTypeId = :transactionTypeId " +
                            "group by CPT.cumulatedPEEMTransactionPK.siteId, CPT.cumulatedPEEMTransactionPK.cumulatedTransactionId, CPT.transactionTypeId, CPT.pEEMOrderNumber, A.numar, PL.idPunctLucru, AO.idPunctLucru, Tour.rampId, RL.idRampa, Tour.tourId, RL.idRutaLivrare, SS.dataLivrare, " +
                            "         ST.codStatie, P.idProdus, SS.lot, SS.bbd, C.fullName, IT.idElemDic, P.cantBax, P.isXXL, P.isFrig, " +
                            "         P.isVenena, P.isSeparanda, EC.nume, SS.idClient, SS.isTiparit, SS.idDivizie, CPT.deliveryDate, CPT.operationalDate, CPT.deliveryPlaceId, CPT.gestId, " +
                            "         CPT.documentReceptionId, CPT.productId, " +
                            "         IsNull(CPT.masterGroupTransferOrderId, 0), IsNull(CPT.masterGroupTransferOrderIdPK, 0), IsNull(CPT.withFirmOrder, 0) " +
                            "order by CPT.cumulatedPEEMTransactionPK.siteId, CPT.cumulatedPEEMTransactionPK.cumulatedTransactionId, CPT.transactionTypeId, CPT.deliveryPlaceId, CPT.gestId ");

                    q.setParameter("transactionTypeId", transactionTypeId);
                    q.setParameter("printed", printed);
                    result = q.getResultList();
                }
            }

            cumulatedPEEMTransactionCollection = new ArrayList<CumulatedPEEMTransaction>();

            if ((!reTransmit) && (!(result == null)) && (!(result.isEmpty()))) {

                List<CumulatedPEEMTransactionExt> cumulatedPEEMTransactionExtCollection = GetCumulatedPEEMTransactionCriteriaList(result, transactionTypeId);

                for (Iterator it = cumulatedPEEMTransactionExtCollection.iterator(); it.hasNext();) {
                    CumulatedPEEMTransactionExt elem = (CumulatedPEEMTransactionExt) it.next();
                    CumulatedPEEMTransaction cot = GenerateCumulatedPEEMTransaction(
                            elem.getTransactionIdList(), elem.getSiteId(), elem.getDeliveryPlaceId(),
                            elem.getTransactionTypeId(), elem.getGestId(), elem.getTourId(), elem.getOperationalDate(), elem.getDeliveryDate(),
                            elem.getDocumentReceptionId(), elem.getProductId(),
                            elem.getMasterGroupTransferOrderId(),
                            elem.getMasterGroupTransferOrderIdPK(),
                            elem.getWithFirmOrder()
                            );
                    elem.setPEEMOrderNumber(cot.getpEEMOrderNumber());
                    cumulatedPEEMTransactionCollection.add(cot);
                    for (Iterator itt = result.iterator(); itt.hasNext();) {
                        OrderForPEEM orderForPEEM = (OrderForPEEM) itt.next();
                        if (elem.getTransactionIdList().contains(orderForPEEM.getTransactionId())) {
                            orderForPEEM.setNumar(elem.getPEEMOrderNumber());
                            orderForPEEM.setTransactionId(cot.getCumulatedPEEMTransactionPK().getCumulatedTransactionId());
                        }
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            q = null;
        }
        return result;
    }