| 37758 |
ranu |
1 |
package com.spice.profitmandi.dao.model;
|
|
|
2 |
|
|
|
3 |
import javax.persistence.*;
|
|
|
4 |
|
|
|
5 |
/**
|
|
|
6 |
* Aging-stock breakdown per item, grouped by (warehouse, vendor). Used by the
|
|
|
7 |
* PO stock-cover approval flow to show "if the PO's own warehouse has no aging
|
|
|
8 |
* stock for this item, WHERE across the network does aging exist" so the
|
|
|
9 |
* approver can see whether to redistribute rather than buy fresh.
|
|
|
10 |
*
|
|
|
11 |
* <p>Same trace path as {@link ItemAgingStockByVendorModel}
|
|
|
12 |
* ({@code inventoryItem.purchaseId -> purchase.poId -> purchaseorder.supplierId}),
|
|
|
13 |
* but without the vendor / warehouse filters — this query returns every
|
|
|
14 |
* (warehouse, vendor) combination that has aging stock of the flagged items.
|
|
|
15 |
*/
|
|
|
16 |
@Entity
|
|
|
17 |
@NamedNativeQueries({
|
|
|
18 |
@NamedNativeQuery(
|
|
|
19 |
name = "PoStockApproval.AgingBreakdown",
|
|
|
20 |
query = "SELECT inv.itemId AS item_id," +
|
|
|
21 |
" inv.currentWarehouseId AS warehouse_id," +
|
|
|
22 |
" po.supplierId AS vendor_id," +
|
|
|
23 |
" COALESCE(SUM(inv.currentQuantity),0) AS aging_qty" +
|
|
|
24 |
" FROM warehouse.inventoryItem inv" +
|
|
|
25 |
" JOIN warehouse.purchase pr ON pr.id = inv.purchaseId" +
|
|
|
26 |
" JOIN warehouse.purchaseorder po ON po.id = pr.purchaseOrder_id" +
|
|
|
27 |
" WHERE inv.itemId IN (:itemIds)" +
|
|
|
28 |
" AND inv.currentQuantity > 0" +
|
|
|
29 |
" AND pr.receivedOn <= :agingCutoff" +
|
|
|
30 |
" GROUP BY inv.itemId, inv.currentWarehouseId, po.supplierId" +
|
|
|
31 |
" HAVING SUM(inv.currentQuantity) > 0",
|
|
|
32 |
resultSetMapping = "PoStockApprovalAgingBreakdown")
|
|
|
33 |
})
|
|
|
34 |
@SqlResultSetMappings({
|
|
|
35 |
@SqlResultSetMapping(
|
|
|
36 |
name = "PoStockApprovalAgingBreakdown",
|
|
|
37 |
classes = {
|
|
|
38 |
@ConstructorResult(
|
|
|
39 |
targetClass = ItemAgingBreakdownModel.class,
|
|
|
40 |
columns = {
|
|
|
41 |
@ColumnResult(name = "item_id", type = Integer.class),
|
|
|
42 |
@ColumnResult(name = "warehouse_id", type = Integer.class),
|
|
|
43 |
@ColumnResult(name = "vendor_id", type = Integer.class),
|
|
|
44 |
@ColumnResult(name = "aging_qty", type = Long.class)
|
|
|
45 |
}
|
|
|
46 |
)
|
|
|
47 |
}
|
|
|
48 |
)
|
|
|
49 |
})
|
|
|
50 |
public class ItemAgingBreakdownModel {
|
|
|
51 |
|
|
|
52 |
private Integer itemId;
|
|
|
53 |
private Integer warehouseId;
|
|
|
54 |
private Integer vendorId;
|
|
|
55 |
private Long agingQty;
|
|
|
56 |
|
|
|
57 |
@Id
|
|
|
58 |
@GeneratedValue(strategy = GenerationType.IDENTITY)
|
|
|
59 |
private Long id;
|
|
|
60 |
|
|
|
61 |
public ItemAgingBreakdownModel() {
|
|
|
62 |
}
|
|
|
63 |
|
|
|
64 |
public ItemAgingBreakdownModel(Integer itemId, Integer warehouseId, Integer vendorId, Long agingQty) {
|
|
|
65 |
this.itemId = itemId;
|
|
|
66 |
this.warehouseId = warehouseId;
|
|
|
67 |
this.vendorId = vendorId;
|
|
|
68 |
this.agingQty = agingQty;
|
|
|
69 |
}
|
|
|
70 |
|
|
|
71 |
public Integer getItemId() {
|
|
|
72 |
return itemId;
|
|
|
73 |
}
|
|
|
74 |
|
|
|
75 |
public Integer getWarehouseId() {
|
|
|
76 |
return warehouseId;
|
|
|
77 |
}
|
|
|
78 |
|
|
|
79 |
public Integer getVendorId() {
|
|
|
80 |
return vendorId;
|
|
|
81 |
}
|
|
|
82 |
|
|
|
83 |
public Long getAgingQty() {
|
|
|
84 |
return agingQty;
|
|
|
85 |
}
|
|
|
86 |
}
|