| 37758 |
ranu |
1 |
package com.spice.profitmandi.dao.model;
|
|
|
2 |
|
|
|
3 |
import javax.persistence.*;
|
|
|
4 |
|
|
|
5 |
/**
|
| 37761 |
ranu |
6 |
* Aging-stock breakdown per item, scoped to a specific vendor and grouped by
|
|
|
7 |
* warehouse. Used by the PO stock-cover approval flow to show "does THIS vendor
|
|
|
8 |
* already have aging stock of this item sitting in any of our warehouses?" so
|
|
|
9 |
* the approver can see whether the same vendor's inventory can be redistributed
|
|
|
10 |
* rather than buying fresh from them.
|
| 37758 |
ranu |
11 |
*
|
| 37761 |
ranu |
12 |
* <p><b>Vendor-scoped by design:</b> aging held by a DIFFERENT vendor at a
|
|
|
13 |
* different warehouse is not our accountability against this PO — Green Fortune
|
|
|
14 |
* doesn't own NSSPL's pile, so we don't surface NSSPL's aging when raising a
|
|
|
15 |
* Green Fortune PO. Rule matches the same-vendor decision documented in the
|
|
|
16 |
* project plan.
|
|
|
17 |
*
|
| 37758 |
ranu |
18 |
* <p>Same trace path as {@link ItemAgingStockByVendorModel}
|
| 37761 |
ranu |
19 |
* ({@code inventoryItem.purchaseId -> purchase.poId -> purchaseorder.supplierId}).
|
| 37758 |
ranu |
20 |
*/
|
|
|
21 |
@Entity
|
|
|
22 |
@NamedNativeQueries({
|
|
|
23 |
@NamedNativeQuery(
|
|
|
24 |
name = "PoStockApproval.AgingBreakdown",
|
|
|
25 |
query = "SELECT inv.itemId AS item_id," +
|
|
|
26 |
" inv.currentWarehouseId AS warehouse_id," +
|
|
|
27 |
" po.supplierId AS vendor_id," +
|
|
|
28 |
" COALESCE(SUM(inv.currentQuantity),0) AS aging_qty" +
|
|
|
29 |
" FROM warehouse.inventoryItem inv" +
|
|
|
30 |
" JOIN warehouse.purchase pr ON pr.id = inv.purchaseId" +
|
|
|
31 |
" JOIN warehouse.purchaseorder po ON po.id = pr.purchaseOrder_id" +
|
|
|
32 |
" WHERE inv.itemId IN (:itemIds)" +
|
| 37761 |
ranu |
33 |
" AND po.supplierId = :vendorId" +
|
| 37758 |
ranu |
34 |
" AND inv.currentQuantity > 0" +
|
|
|
35 |
" AND pr.receivedOn <= :agingCutoff" +
|
|
|
36 |
" GROUP BY inv.itemId, inv.currentWarehouseId, po.supplierId" +
|
|
|
37 |
" HAVING SUM(inv.currentQuantity) > 0",
|
|
|
38 |
resultSetMapping = "PoStockApprovalAgingBreakdown")
|
|
|
39 |
})
|
|
|
40 |
@SqlResultSetMappings({
|
|
|
41 |
@SqlResultSetMapping(
|
|
|
42 |
name = "PoStockApprovalAgingBreakdown",
|
|
|
43 |
classes = {
|
|
|
44 |
@ConstructorResult(
|
|
|
45 |
targetClass = ItemAgingBreakdownModel.class,
|
|
|
46 |
columns = {
|
|
|
47 |
@ColumnResult(name = "item_id", type = Integer.class),
|
|
|
48 |
@ColumnResult(name = "warehouse_id", type = Integer.class),
|
|
|
49 |
@ColumnResult(name = "vendor_id", type = Integer.class),
|
|
|
50 |
@ColumnResult(name = "aging_qty", type = Long.class)
|
|
|
51 |
}
|
|
|
52 |
)
|
|
|
53 |
}
|
|
|
54 |
)
|
|
|
55 |
})
|
|
|
56 |
public class ItemAgingBreakdownModel {
|
|
|
57 |
|
|
|
58 |
private Integer itemId;
|
|
|
59 |
private Integer warehouseId;
|
|
|
60 |
private Integer vendorId;
|
|
|
61 |
private Long agingQty;
|
|
|
62 |
|
|
|
63 |
@Id
|
|
|
64 |
@GeneratedValue(strategy = GenerationType.IDENTITY)
|
|
|
65 |
private Long id;
|
|
|
66 |
|
|
|
67 |
public ItemAgingBreakdownModel() {
|
|
|
68 |
}
|
|
|
69 |
|
|
|
70 |
public ItemAgingBreakdownModel(Integer itemId, Integer warehouseId, Integer vendorId, Long agingQty) {
|
|
|
71 |
this.itemId = itemId;
|
|
|
72 |
this.warehouseId = warehouseId;
|
|
|
73 |
this.vendorId = vendorId;
|
|
|
74 |
this.agingQty = agingQty;
|
|
|
75 |
}
|
|
|
76 |
|
|
|
77 |
public Integer getItemId() {
|
|
|
78 |
return itemId;
|
|
|
79 |
}
|
|
|
80 |
|
|
|
81 |
public Integer getWarehouseId() {
|
|
|
82 |
return warehouseId;
|
|
|
83 |
}
|
|
|
84 |
|
|
|
85 |
public Integer getVendorId() {
|
|
|
86 |
return vendorId;
|
|
|
87 |
}
|
|
|
88 |
|
|
|
89 |
public Long getAgingQty() {
|
|
|
90 |
return agingQty;
|
|
|
91 |
}
|
|
|
92 |
}
|