| Line 101... |
Line 101... |
| 101 |
|
101 |
|
| 102 |
@NamedNativeQueries({
|
102 |
@NamedNativeQueries({
|
| 103 |
|
103 |
|
| 104 |
@NamedNativeQuery(name = "scheme.getActiveScheme", query = "select si.catalog_id from catalog.scheme s join "
|
104 |
@NamedNativeQuery(name = "scheme.getActiveScheme", query = "select si.catalog_id from catalog.scheme s join "
|
| 105 |
+ " fofo.scheme_item si on s.id = si.scheme_id join catalog.item i on i.catalog_item_id = si.catalog_id"
|
105 |
+ " fofo.scheme_item si on s.id = si.scheme_id join catalog.item i on i.catalog_item_id = si.catalog_id"
|
| 106 |
+ " where s.active_timestamp is not null and start_date_time <= :startDate and end_date_time >= :startDate and s.type = 'SPECIAL_SUPPORT' and i.hsnCode = '85171300' order by active_timestamp desc "),
|
106 |
+ " where s.active_timestamp is not null and start_date_time <= :startDate and end_date_time >= :startDate"
|
| - |
|
107 |
+ " and (si.start_date is null or :startDate between si.start_date and si.end_date)"
|
| - |
|
108 |
+ " and s.type = 'SPECIAL_SUPPORT' and i.hsnCode = '85171300' order by active_timestamp desc "),
|
| 107 |
|
109 |
|
| 108 |
@NamedNativeQuery(name = "scheme.selectMissedActivationSale", query = "select cs.id as scheme_id, ai.serial_number, sr.order_id, ii.id as inventory_item_id, ai.activation_timestamp"
|
110 |
@NamedNativeQuery(name = "scheme.selectMissedActivationSale", query = "select cs.id as scheme_id, ai.serial_number, sr.order_id, ii.id as inventory_item_id, ai.activation_timestamp"
|
| 109 |
+ " from fofo.activated_imei ai "
|
111 |
+ " from fofo.activated_imei ai "
|
| 110 |
+ " join fofo.inventory_item ii on ai.serial_number = ii.serial_number "
|
112 |
+ " join fofo.inventory_item ii on ai.serial_number = ii.serial_number "
|
| 111 |
+ " join catalog.item i on i.id = ii.item_id "
|
113 |
+ " join catalog.item i on i.id = ii.item_id "
|
| 112 |
+ " join catalog.scheme cs on ai.activation_timestamp >= cs.start_date_time and ai.activation_timestamp <= cs.end_date_time "
|
114 |
+ " join catalog.scheme cs on ai.activation_timestamp >= cs.start_date_time and ai.activation_timestamp <= cs.end_date_time "
|
| 113 |
+ " join fofo.scheme_item si on (si.scheme_id = cs.id and i.catalog_item_id = si.catalog_id) "
|
115 |
+ " join fofo.scheme_item si on (si.scheme_id = cs.id and i.catalog_item_id = si.catalog_id and (si.start_date is null or ai.activation_timestamp between si.start_date and si.end_date)) "
|
| 114 |
+ " join fofo.scan_record sr on (sr.inventory_item_id = ii.id)"
|
116 |
+ " join fofo.scan_record sr on (sr.inventory_item_id = ii.id)"
|
| 115 |
+ " join fofo.scheme_region sre on sre.scheme_id = cs.id"
|
117 |
+ " join fofo.scheme_region sre on sre.scheme_id = cs.id"
|
| 116 |
+ " join cs.partner_region pr on (pr.region_id = sre.region_id and (pr.fofo_id = ii.fofo_id or pr.fofo_id = 0))"
|
118 |
+ " join cs.partner_region pr on (pr.region_id = sre.region_id and (pr.fofo_id = ii.fofo_id or pr.fofo_id = 0))"
|
| 117 |
+ " left join fofo.scheme_in_out sio on (sio.scheme_id = cs.id and sio.inventory_item_id = ii.id) "
|
119 |
+ " left join fofo.scheme_in_out sio on (sio.scheme_id = cs.id and sio.inventory_item_id = ii.id) "
|
| 118 |
+ " where cs.active_timestamp is not null " + " and cs.type ='SPECIAL_SUPPORT'"
|
120 |
+ " where cs.active_timestamp is not null " + " and cs.type ='SPECIAL_SUPPORT'"
|