proc

select DISTINCT dslam.id,port.id from
+ Port.class.getName()
+ " port, "
+ OrderDetails.class.getName()
+ " orderDetails, "
+ LluOrder.class.getName()
+ " lluOrder, "
+ Linecard.class.getName()
+ " linecard, "
+ Dslam.class.getName()
+ " dslam, "
// START : ADITYA: artf550472 - made changes for 'Change Partner'
+ Modem.class.getName()
+ " modem "
// END : ADITYA: artf550472 - made changes for 'Change Partner'
+ " WHERE port.status IN ( "
+ PortStatusEnum.RESERVED.getOrdinal()
+ " , "
+ PortStatusEnum.PROVISIONING_ERROR.getOrdinal()
+ " , "
+ PortStatusEnum.REACTIVATION_PENDING.getOrdinal()
+ " ) "
+ " AND port.servicePackage.id = orderDetails.service.servicePackage.id "
+ " AND port.linecard.id = linecard.id AND linecard.dslam.id = dslam.id "

// START : Ranjani : artf550472 : Added null check to withhold O2
// orders without mobile number
+ " AND (orderDetails.customer.user.userServiceProvider != '"
+ UserConstants.O2_SERVICE_PROVIDER
+ "' OR (orderDetails.service.servicePackage.mpnNo IS NOT NULL)) "
// END : Ranjani : artf550472 : Added null check to withhold O2
// orders without mobile number

+ " AND lluOrder.id = orderDetails.lluOrder.id "
+ " AND (((lluOrder.lisaStatus = '"
+ LisaStatusEnum.FIRM_ORDER_CONFIRMED.getOrdinal()
+ "'"
+ " or lluOrder.lisaStatus = '"
+ LisaStatusEnum.FIRM_ORDER_DELIVERED.getOrdinal()
+ "')"
+ " and lluOrder.btSystemType = '"
+ BTSystemTypeEnum.LISA.getOrdinal()
+ "')"
+ " or ((lluOrder.lisaStatus = '"
+ LisaStatusEnum.ORDER_COMMITTED.getOrdinal()
+ "'"
+ " or lluOrder.lisaStatus = '"
+ LisaStatusEnum.ORDER_COMPLETED.getOrdinal()
+ "')"
+ " and lluOrder.btSystemType = '"
+ BTSystemTypeEnum.EMP.getOrdinal()
+ "')) "
+ " AND orderDetails.service.servicePackage.id = modem.servicePackage.id "
// START : Ranjani: artf550472 - query changed for 'Change Partner'
+ " and (orderDetails.service.servicePackage.id NOT IN (SELECT cph.newServicePackage.id FROM ChangePartnerHistory cph) "
+ " or ( "
+ " orderDetails.service.servicePackage.id IN (SELECT cph.newServicePackage.id FROM ChangePartnerHistory cph) "
+ " AND (("
+ " ((modem.sentDate IS NULL"
+ " AND (orderDetails.customer.partner.type IN "
+ " ("
+ PartnerTypeEnum.WHOLE_SELLER.getOrdinal()
+ " , "
+ PartnerTypeEnum.RUNRATE_RESELLER.getOrdinal()
+ ")"
+ " OR orderDetails.biabCustomer = "
+ BIAB_CUSTOMER
+ "))"
+ " OR "
+ " (modem.sentDate IS NOT NULL AND to_char(modem.sentDate, 'yyyyMMdd') <= :currentDateLessThreeDays ))"

// END : Fix for picking RR / Wholesale orders for which Payment /
// Modem are not required

+ " AND to_char(lluOrder.predictedDeliveryDate, 'yyyyMMdd') <= :currentDate )"
+ " AND :nextWorkingDate = :currentDate )"
// END : Ranjani: artf550472 - query changed for 'Change Partner'
+ "))"
/**
* START : 28/09/2010 : sChauhan5 : Ccore : Added check to not include Frozen
* ISAMs
**/
+ " AND (dslam.frozenUntil IS NULL "
+ " OR to_char(dslam.frozenUntil, 'yyyymmdd HH24:mi:ss') <= :currentDateForFrozenDslam)"
/**
* END : 28/09/2010 : sChauhan5 : Ccore : Added check to not include Frozen
* ISAMs
**/
+ " order by dslam.id, port.id"

 

 

Thanks and Regards ,

Mohammed Adnan
Junior Associate | SapientNitro
Tower D & E, DLF Cyber Greens
DLF Phase III, Sector 25-A
Gurgaon, Haryana, 122002, India
Mobile: +91 7838534597

mailto: madnan1@sapient.com