SmartDukaan – /trunk/profitmandi-dao/src/main/resources/sql/

Rev 37853 – docs(warehouse): record-only migration files for 2026-10-05 HR move - suspended-warehouse ...

amit — Mon, 05 Oct 2026 19:51:04 +0530

amit – 3 file(s) modified
docs(warehouse): record-only migration files for 2026-10-05 HR move - suspended-warehouse brand mapping (1:1 HR substitution, 1,062 deleted / 51 added), 3 franchise stores 7678->13372, note for the store remap and pending-order move done outside the code (all already applied on hadb1, do not re-run)

+ /trunk/profitmandi-dao/src/main/resources/sql/20261005_franchise_stores_7678_to_13372.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/20261005_hr_move_stores_orders_NOTE.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/20261005_suspended_warehouse_brand_mapping.sql

Rev 37850 – refactor(warehouse): retire hardcoded Delhi ids - allocation fallback vendor warehouse ...

amit — Mon, 05 Oct 2026 19:44:42 +0530

amit – 4 file(s) modified
refactor(warehouse): retire hardcoded Delhi ids - allocation fallback vendor warehouse 7573 -> 13368 (central HR-NSSPL/GGN); Vivo Noida/Ghaziabad partner-sale rule 7573 -> 13370; drop unused InventoryWarehouse WH_DL/WH_HR_DL/WAREHOUSE_IDS; migration 20261005_hr_ggn_central_brand_mapping.sql (13368 central for every ACTIVE warehouse, all brands except Oppo/Vivo, 532 rows, applied on hadb1)

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/FofoStore.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/inventory/InventoryWarehouse.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/transaction/TransactionServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/20261005_hr_ggn_central_brand_mapping.sql

Rev 37845 – feat(warehouse): ACTIVE/SUSPENDED/INACTIVE status + label on sellerwarehouse replace WAREHOUSE_MAP - ...

amit — Mon, 05 Oct 2026 18:48:21 +0530

amit – 33 file(s) modified
feat(warehouse): ACTIVE/SUSPENDED/INACTIVE status + label on sellerwarehouse replace WAREHOUSE_MAP - BillingWarehouseService (active for dashboards, active+suspended for warehouse screens, names in every state, 5 min cache); setup service: guarded status change (no suspend while franchise stores or brand mapping route there, no close while stock/unshipped orders), rename, all-warehouse overview with usage; partner store assignable only to an active warehouse; daily stock alert for suspended/inactive warehouses to logistics/accounts top 2 staffed levels + leadership; dashboard warehouse list = active (replaces r37834 stock-holding loop); migration sellerwarehouse_status_display_name_20261005.sql (applied on hadb1)

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/transaction/SellerWarehouse.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/enumuration/warehouse/WarehouseStatus.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/PhysicalWarehouseView.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/WarehouseStockLine.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/WarehouseUsage.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/dtr/FofoStoreRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/InventoryWarehouseRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/SaholicInventoryCISRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/SaholicInventoryCISRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/WarehouseRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/SellerWarehouseRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/SellerWarehouseRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/service/solr/FofoSolr.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/AdminUser.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/authentication/JWTUtil.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/offers/OfferServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/RbmTargetServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/transaction/TransactionServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/user/RetailerServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/BillingWarehouseService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/BillingWarehouseServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PoStockApprovalDigestServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PoStockApprovalServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PurchaseOrderServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/SuspendedWarehouseStockAlertService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/SuspendedWarehouseStockAlertServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseSetupService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseSetupServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseSetupValidator.java
+ /trunk/profitmandi-dao/src/main/resources/sql/sellerwarehouse_status_display_name_20261005.sql
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse/BillingWarehouseServiceImplTest.java
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse/SuspendedWarehouseStockAlertServiceImplTest.java
~ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse/WarehouseSetupServiceImplTest.java

Rev 37835 – chore(sql): record applied migrations - HR movement HSN/GST-rate/category fixes (2026-10-02) ...

amit — Sun, 04 Oct 2026 13:53:44 +0530

amit – 2 file(s) modified
chore(sql): record applied migrations - HR movement HSN/GST-rate/category fixes (2026-10-02) and HR warehouse self brand mapping (2026-10-04); both re-runnable, already applied on hadb1

+ /trunk/profitmandi-dao/src/main/resources/sql/20261002_hr_movement_hsn_tax_category_fixes.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/20261004_hr_warehouse_self_brand_mapping.sql

Rev 37822 – feat(warehouse): physical warehouse setup service, supplier mapping PO guard, address ...

amit — Thu, 01 Oct 2026 21:06:34 +0530

amit – 24 file(s) modified
feat(warehouse): physical warehouse setup service, supplier mapping PO guard, address active flag

- WarehouseSetupService + WarehouseSetupValidator: create seller (state from GSTIN,
unique label/GSTIN), address master (city stored last, 6-digit pin, contact),
physical warehouse (OURS/BAD vendor 40 + Dummy/G + sellerwarehouse + mapping in
one transaction), activate/deactivate (sellerwarehouse.is_active), remap address
with internal store sync. Limits follow column sizes (sql_mode is empty).
- WarehouseAddressMaster.active: only active addresses are offered for assignment.
Migration sql/20261001_warehouseaddressmaster_active.sql (applied on hadb1).
- PO guard: createPurchaseOrder refuses a supplier with no OURS/GOOD vendor
warehouse at the destination (GRN could never receive it).
InventoryWarehouseRepository.hasGoodSupplierWarehouse / selectSupplierIdsWithGoodWarehouse
use existence checks; 67 pairs carry duplicate OURS/GOOD rows.
- SellerService.syncInternalStoreAddress(store); RetailerServiceImpl syncs an
INTERNAL store's address when it moves warehouse or becomes internal.
- Record of 2026-10-01 HR Gurugram setup SQL (seller 21, warehouses 13368/13370/13372).
- Tests: WarehouseSetupValidatorTest (10), WarehouseSetupServiceImplTest (7).

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/transaction/WarehouseAddressMaster.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/NewPhysicalWarehouseRequest.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/NewSellerRequest.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/NewWarehouseAddressRequest.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/PhysicalWarehouseView.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/warehouse/WarehouseAddressView.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/InventoryWarehouseRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/InventoryWarehouseRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/WarehouseAddressMappingRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/WarehouseAddressMappingRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/SellerService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/SellerServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/user/RetailerServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PurchaseOrderService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PurchaseOrderServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseSetupService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseSetupServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseSetupValidator.java
+ /trunk/profitmandi-dao/src/main/resources/sql/20261001_delete_hrgn1715_hr_uk_store.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/20261001_hr_gurugram_vendor_warehouses.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/20261001_hr_gurugram_virtual_warehouses.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/20261001_warehouseaddressmaster_active.sql
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse/WarehouseSetupServiceImplTest.java
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse/WarehouseSetupValidatorTest.java

Rev 37805 – fix(catalog): trim f_ colour prefix space; age delisted listings from ...

amit — Tue, 29 Sep 2026 04:14:47 +0530

amit – 8 file(s) modified
fix(catalog): trim f_ colour prefix space; age delisted listings from last activation

- Item.normalizeColor drops the space after f_ on every write (upload sheets typed
'f_ Black', which read ' Black' everywhere); ItemLoaderService uses it so the
duplicate-colour check compares the clean value
- tag_listing.last_activated, stamped by TagListing.setActive on off->on;
updateActiveById now goes through the entity (+flush) so the toggle stamps it
- delist cron + SQL script age a listing from GREATEST(start_date, last_activated),
so a re-activated listing is not delisted again the next morning
- migrations: add_tag_listing_last_activated_20260929.sql, fix_item_color_prefix_space_20260929.sql
(both applied on hadb1 2026-09-29; 174 items trimmed, 36830 excluded - clashes with 37089)

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/Item.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/TagListing.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/TagListingRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/CatalogDelistServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/ItemLoaderService.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_tag_listing_last_activated_20260929.sql
~ /trunk/profitmandi-dao/src/main/resources/sql/delist_inactive_listings_rolling_24m.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/fix_item_color_prefix_space_20260929.sql

Rev 37804 – fix(fofo-store): store every GSTIN in upper case at the entity ...

amit — Tue, 29 Sep 2026 00:19:53 +0530

amit – 2 file(s) modified
fix(fofo-store): store every GSTIN in upper case at the entity setter

FofoStore.setGstNumber now normalises through StringUtils.normalizeGstNumber,
so no write path can store a mixed-case GSTIN. Only the partner-update path
normalised before; the LOI and trial-form paths wrote the field directly,
which is how 06bmlpk3545g1z0 and 03AAccn9802g1zp reached the table. NIC
expects upper case, and case-sensitive comparisons (including the own-seller
GSTIN check behind a NIC refusal) silently failed to match.

uppercase_fofo_store_gstin_20260928.sql corrects the four rows already on
record; it is already applied on hadb1 (0 mixed-case GSTINs remain).

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/FofoStore.java
+ /trunk/profitmandi-dao/src/main/resources/sql/uppercase_fofo_store_gstin_20260928.sql

Rev 37797 – Stop a settled debit note being received a second time ...

amit — Mon, 28 Sep 2026 16:50:59 +0530

amit – 3 file(s) modified
Stop a settled debit note being received a second time

A return settled before the receive workflow (first purchase_return_order
2026-03-16) wrote the warehouse SALE_RET scan, a returnorderinfo row and a
wallet refund, but never touched the debit note: it stayed CREATED with no
purchase_return_order. Those are exactly the two things the receive screens
read, so 5,557 fully settled notes looked like they were still awaiting
receipt, Receive button included.

Receiving one of them (DN UPBLY975/4, IMEI 864973083197734, Antu Enterprises)
restored to the partner a phone that had already been returned, refunded
Rs 38,999 and resold to another partner, and a duplicate debit note followed
that Finance could not refund.

- receiveDebitNoteItems: refuse a note that already has a return order, one
whose status is not CREATED, and one whose every unit is already back at the
warehouse. Checked BEFORE the condition-mismatch gate - a mismatch there
returns into rejectOnConditionMismatch without reaching any later check, and
acknowledging that rejection hands the stock back to the partner. That is
how the Antu case slipped through.
- getAlreadyReturnedDebitNotes: the last of those checks, batched for a page of
notes. Mirrors applyReceipt - SALE_RET/DOA_IN/SALE_RET_UNUSABLE for the unit
against the order its invoice was billed on - so a screen can never disagree
with what the refund step will accept.
- processInvoiceReturn: one open return per document.
- sql/backfill_debit_note_status_old_flow_20260924.sql: APPLIED on hadb1
2026-09-24. 4,154 notes -> APPROVED, 6,639 items -> RETURNED, on the same
evidence (returned and refunded). Open notes 5,557 -> 1,404; the rest are
left CREATED for review, bucketed in fofo._dn_backfill_20260922.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/PurchaseReturnService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/PurchaseReturnServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/backfill_debit_note_status_old_flow_20260924.sql

Rev 37790 – Return refund as B2C when NIC refuses the buyer's GSTIN ...

amit — Mon, 28 Sep 2026 14:34:01 +0530

amit – 13 file(s) modified
Return refund as B2C when NIC refuses the buyer's GSTIN

A return whose credit note NIC refuses because the BUYER's GSTIN is
cancelled or invalid could not be settled at all: the approval threw, and
with it went the refund, the stock and the return rows (NSUPDL5176, Mobile
Hub, GSTIN 08DCUPD7948K1ZP). Where the invoice itself had never been filed
the return was instead refunded with no credit note at all, leaving the
refund undocumented.

Raising the note automatically is not the answer - it is Finance's call,
and the GST on a cancelled-GSTIN sale is not recoverable, so paying the
full value back loses it. Both flows therefore record the refusal and stop.

GstProService: getIrnFailureReason (NIC's refusal for an invoice),
isInvalidBuyerGstin (that refusal is about the buyer's registration -
deliberately NOT a state-code mismatch, which is a data error to fix and
retry, nor anything naming our own seller GSTIN) and briefIrnReason (one
plain-ASCII line of 128 chars; credit_note is latin1 and sql_mode is empty,
so anything else would store as '?').

PurchaseReturnServiceImpl: both the invoice-return and the debit-note
refund detect that refusal, record it through ReturnIrnFailureRecorder and
throw ReturnIrnGstinFailureException. The recorder commits in REQUIRES_NEW
because the approval it records is about to roll back, and it only inserts -
return_irn_failure carries no foreign key on purpose, since an FK check
would take a shared lock on the very PurchaseReturnOrder row the dying
transaction may still hold.

Finance may then settle the return as B2C for the rest of that calendar day:
refundAsB2c credits the value NET of GST in whole rupees (B2cRefundQuote,
HALF_UP per line, so the line table and the total always agree), restores
the stock and issues a local credit note for exactly that amount with no tax
on its lines, no IRN and NIC's reason recorded. The amount is fixed - the
caller must pass back the quoted figure - and a remark and explicit consent
are required. On a later day the option only reopens after a fresh approval
attempt is refused again, so NIC is always re-checked first.

Mails: every refusal notifies Accounts L2 and above; the B2C settlement
notifies them and the partner's Warehouse L1/L2, both tabulated.

The partner account statement is unaffected: it skips RETURNS notes and
credits returns from returnorderinfo, so the new notes cannot double-credit.

DEPLOY ORDER: apply sql/add_return_b2c_refund_20260921.sql BEFORE any war
built from this dao. CreditNote now maps irn_skip_reason and b2c, so every
credit-note read in web, fofo and cron fails until the columns exist.
Untested beyond compilation.

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/ReturnIrnFailure.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/transaction/CreditNote.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/ReturnIrnFailureRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/ReturnIrnFailureRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/integrations/gstpro/GstProService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/B2cRefundQuote.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/PurchaseReturnNotificationService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/PurchaseReturnNotificationServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/PurchaseReturnService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/PurchaseReturnServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/ReturnIrnFailureRecorder.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/ReturnIrnGstinFailureException.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_return_b2c_refund_20260921.sql

Rev 37785 – feat(cs): demo partner access menu for Sales L6 and above ...

amit — Fri, 25 Sep 2026 16:18:12 +0530

amit – 1 file(s) modified
feat(cs): demo partner access menu for Sales L6 and above

menu_category rows for Sales (category 4) ordinals 5-9 (L6..Final) on the
Demo Partner Access menu. INSERT IGNORE on the PK (latin1 column rejects a
utf8-literal NOT EXISTS compare). Applied on hadb1 2026-09-25.

~ /trunk/profitmandi-dao/src/main/resources/sql/migration_demo_partner_access.sql

Rev 37779 – feat(cs): demo partner access for sales team New cs.demo_partner_access ...

amit — Fri, 25 Sep 2026 13:16:59 +0530

amit – 7 file(s) modified
feat(cs): demo partner access for sales team

New cs.demo_partner_access binding (sales auth user -> partner) used only to
open a partner's dashboard/app for demos. Read only at partner-view entry
points; CsService position mappings are untouched, so performance, targets,
reports and cron mails are unaffected.

- DemoPartnerAccess entity + repository
- DemoPartnerAccessService: grant (sales-only, active store, skips duplicate
and position-mapped), soft revoke, isAllowed (position OR demo),
demo emails for app lists, canManage (mirrors sidebar menu rule)
- AdminUser.ALL_MENU_EMAILS extracted so canManage and the sidebar share it
- migration_demo_partner_access.sql: table + "Demo Partner Access" menu
under Admin Control (NOT yet applied on hadb1)

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/cs/DemoPartnerAccess.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/cs/DemoPartnerAccessRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/cs/DemoPartnerAccessRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/cs/DemoPartnerAccessService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/cs/DemoPartnerAccessServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/AdminUser.java
+ /trunk/profitmandi-dao/src/main/resources/sql/migration_demo_partner_access.sql

Rev 37778 – fix(catalog): delist cron retires only models it darkened in the ...

amit — Thu, 24 Sep 2026 22:37:06 +0530

amit – 3 file(s) modified
fix(catalog): delist cron retires only models it darkened in the same run

Step 4 scanned every mobile model with no active listing and moved it to OTHER,
so new launches (categorised before listing) and hand-paused models were retired
the next morning, e.g. Nothing Phone 4 1026596-603 on 2026-09-15. Scope it to
models whose listing this run delisted (_delist_audit run_date = today).
Add fix_dark_model_retire_20260924.sql to restore the 16 models still stuck on OTHER.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/CatalogDelistServiceImpl.java
~ /trunk/profitmandi-dao/src/main/resources/sql/delist_inactive_listings_rolling_24m.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/fix_dark_model_retire_20260924.sql

Rev 37776 – returns: reverse a refunded debit-note return whose goods never reached ...

amit — Thu, 24 Sep 2026 20:16:26 +0530

amit – 16 file(s) modified
returns: reverse a refunded debit-note return whose goods never reached the warehouse

Once a return was refunded nothing could undo it - rejectReturn refuses an already
refunded one - so a return booked and paid for goods that never arrived left phantom
warehouse stock, the unit missing from partner stock, the order marked returned, a
wallet credit and a filed credit note, with no way back.

ReturnReversalService.reverse(imei, reason, by, dryRun) undoes exactly one unit:
the warehouse return scan (row deleted so a genuine return later is not rejected as a
duplicate) and the stock it added, partner stock with its schemes/price drop/offers,
the order's return quantity and status, the wallet refund as a REVERSAL entry, and the
return item / debit note. Guards refuse anything that has moved since the refund, and a
dry run reports the plan without writing.

The credit note follows movement and the NIC clock: inside the 24h window, and only
when the note credits nothing but this unit, its IRN is cancelled and the note marked
cancelled; past it a DBN with its own IRN is issued against the note
(CreditNoteService.issueReturnReversalDebitNote, stored as CN_CANCELLATION with no
margin month so the statement does not show it twice - the wallet REVERSAL is the
statement line); a note never filed at NIC is voided locally. Every local write happens
first and NIC last, so a refusal rolls the whole reversal back.

fofo.return_reversal (return_reversal_20260921.sql) records each reversal and, unique
per return item, makes it once-only. Also adds cancelled=0 to the RETURNS_CN branches of
the account statement queries, without which a cancelled note keeps crediting the
statement - no current effect, no RETURNS note is cancelled today.

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/ReturnReversal.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/ReturnReversalRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/ReturnReversalRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/CreditNoteRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/CreditNoteRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/OrderRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/InventoryService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/InventoryServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/ReturnReversalModel.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/ReturnReversalService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/ReturnReversalServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/transaction/CreditNoteService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/transaction/CreditNoteServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseInventoryService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseInventoryServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/return_reversal_20260921.sql

Rev 37769 – fix(movement): receive a movement invoice only against its own PO, ...

amit — Thu, 24 Sep 2026 18:54:23 +0530

amit – 10 file(s) modified
fix(movement): receive a movement invoice only against its own PO, stop auto-closing movements, cancel reduces the PO line

- GRN: movement invoice resolves invoice -> orders -> transaction -> its own movement PO (or a receive-only PO
from the billing warehouse); supplier/warehouse keyed at entry must match; scanned IMEIs must be the billed ones
- Cancelling an unbilled movement order takes its qty off the PO line; PO CLOSED/PRECLOSED once nothing is open
- reopenedAt removed (movements no longer auto-close); PO records createdBy
- SupplierStateResolver: a supplier's state is derived from its GSTIN on save
- sql: add_po_created_by (applied), drop_po_reopened_at (run AFTER fofo/cron/web deploy)

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/warehouse/WarehousePurchaseOrder.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/OrderRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/InvoiceService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/InvoiceServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PurchaseOrderServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/SupplierStateResolver.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_po_created_by_20260924.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/drop_po_reopened_at_20260924.sql
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/warehouse/SupplierStateResolverTest.java

Rev 37764 – feat(store-closure): require reason, remark and approval mail to close a ...

amit — Thu, 24 Sep 2026 17:21:58 +0530

amit – 7 file(s) modified
feat(store-closure): require reason, remark and approval mail to close a store; mail closure report

- fofo.store_closure audit table (reason, remark, approval document, closed_by); migration applied on hadb1
- StoreClosureService: validates and closes, queues closure report with approval attached to the
partner's Sales L2, top 3 Sales levels, top Accounts level and the closer
- StoreAccess: single home for store close/deactivate/activate/extend-billing access lists;
neeraj.gupta can close, mohit.gulati removed from extend billing

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/StoreClosure.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/enumuration/fofo/StoreClosureReason.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/StoreClosureRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/StoreClosureRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/user/StoreAccess.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/user/StoreClosureService.java
+ /trunk/profitmandi-dao/src/main/resources/sql/migration_store_closure.sql

Rev 37724 – chore(hot-deals): restore 5 catalogs to OEM brand, add POCO M7 ...

amit — Fri, 18 Sep 2026 18:15:09 +0530

amit – 1 file(s) modified
chore(hot-deals): restore 5 catalogs to OEM brand, add POCO M7 Plus 5G (4GB 128GB)_s to Hot Deal

Applied on hadb1 2026-09-18. Removed 1025252/1025253 (Samsung A06) and
1026085/1026403/1026408 (Refurbished iPhones) via _hot_deal_brand_freeze;
added catalog 1026568 (item 40832) with freeze + scope rows.

+ /trunk/profitmandi-dao/src/main/resources/sql/hot_deal_brand_remove_5_catalogs_20260918.sql

Rev 37713 – fix(warehouse): record the order's own vendor when a receipt line ...

amit — Fri, 18 Sep 2026 05:05:15 +0530

amit – 2 file(s) modified
fix(warehouse): record the order's own vendor when a receipt line carries no origin

- r37694 took the origin only from lineitem.origin_vendor_id, which exists on orders raised from that revision onward, so goods arriving on older open orders landed with a cost but no vendor - 282 rows on the first day
- A purchase from an outside vendor now falls back to that supplier; internal movements are unchanged and an unknowable origin still stays NULL
- sql/backfill_receipt_origin_20260918.sql repairs the 282 already received (run on prod 2026-09-18; bak warehouse._bak_receipt_origin_20260918); in-stock unknown 895 -> 648

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/backfill_receipt_origin_20260918.sql

Rev 37708 – Migration: state the algorithm and lock mode, and why it ...

amit — Fri, 18 Sep 2026 04:26:13 +0530

amit – 1 file(s) modified
Migration: state the algorithm and lock mode, and why it runs before the release

hadb1 is MySQL 5.7, so adding a column rebuilds the table rather than only touching metadata.
At ~53k rows that is seconds and InnoDB permits concurrent reads and writes throughout, but
ALGORITHM/LOCK are now stated explicitly so it fails loudly rather than quietly taking a table
lock if that ever stops holding.

~ /trunk/profitmandi-dao/src/main/resources/sql/add_po_reopened_at_20260918.sql

Rev 37704 – Reopen a movement PO whose stock arrived late, and stop ...

amit — Fri, 18 Sep 2026 04:18:12 +0530

amit – 8 file(s) modified
Reopen a movement PO whose stock arrived late, and stop stranding GRN price corrections

Internal movements auto-close after four days, which fits 99.6% of them - 5,491 of 5,515 receipts
land inside the window. The remainder leave the PO closed with the stock still in transit and
nowhere to receive it: 998 internal POs closed during 2026 still holding 21,996 unreceived units.

A closed movement PO can now be reopened from the purchase order list. Reopening stamps
reopenedAt, and auto-close measures from WarehousePurchaseOrder.getOpenSince() - reopenedAt when
set, the PO date otherwise - so a reopened PO gets the same fresh window a new one gets instead of
being closed straight back on the next sweep. Only movements between our own warehouses: an
external vendor PO that has closed is settled with that vendor, not reopened unilaterally.

Separately, a GRN price correction now checks that the PO it just raised is one the invoice can
actually be received against. Matching reads POs that are open and approved for the same supplier
and warehouse dated on or before the invoice; it never looks at the PO being corrected, so what
matters is that the new PO is receivable. 54 were not - backdated into INIT by the old approval
gate, hence outside the match - and each stranded silently: original line discarded, GRN completed
without it, the correction left holding a reservation for stock that had already arrived.

isOpen() names the open set - INIT, READY, PARTIALLY_FULFILLED - that the movement and commitment
queries already read.

Migration sql/add_po_reopened_at_20260918.sql adds reopenedAt, nullable and additive. It must run
before this ships: the entity maps the column.

~ /trunk/profitmandi-cron/src/main/java/com/smartdukaan/cron/scheduled/purchaseorder/POScheduler.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/warehouse/WarehousePurchaseOrder.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/GrnRequestServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_po_reopened_at_20260918.sql
~ /trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/config/AppConfig.java
~ /trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/controller/warehouse/PurchaseOrderController.java
~ /trunk/profitmandi-fofo/src/main/webapp/resources/js/warehouse-purchase.js
~ /trunk/profitmandi-fofo/src/main/webapp/WEB-INF/views/ftl/warehouse-purchase-order.vm

Rev 37694 – feat(warehouse): record where stock came from and what it cost; ...

amit — Thu, 17 Sep 2026 18:19:28 +0530

amit – 11 file(s) modified
feat(warehouse): record where stock came from and what it cost; price internal movements at that cost

- inventoryItem.origin_vendor_id / receipt_unit_cost and lineitem.origin_vendor_id (sql/add_inventory_cost_layer_20260917.sql, applied on prod 2026-09-17 in 75s): GRN copies both from the PO line it arrived on, so origin survives any number of hops and non-serialised stock keeps it too. Unknowable origin stays NULL - never a guess, never an internal supplier
- A movement now moves stock at the cost it was received at, not the origin vendor's current catalog TP. Layers are grouped by cost; a quantity spanning two costs is refused with both numbers, since a PO line carries one price
- Stock already promised on open outbound movement orders is no longer offered again (176 item/warehouse pairs on prod are fully promised today)
- Stock on open inbound POs is offered for planning at that order's price and reported as 'on the way in', but cannot be dispatched until received; the PO item picker lists those items too
- Backfill: 4,010 of 4,646 in-stock rows on prod got origin and cost; 636 stay unknown and are priced from the latest approved external catalog price as before

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/warehouse/WarehouseInventoryItem.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/warehouse/WarehouseLineItem.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/InternalMovementAllocationModel.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/InternalMovementPriceModel.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/warehouse/WarehouseInventoryItemRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/warehouse/WarehouseLineItemRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/warehouse/WarehouseLineItemRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/InternalMovementPricingServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/PurchaseOrderServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/WarehouseServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_inventory_cost_layer_20260917.sql

Rev 37688 – Record that the counter/privatedealuser rename was applied on hadb1 (suffix ...

amit — Thu, 17 Sep 2026 17:59:10 +0530

amit – 1 file(s) modified
Record that the counter/privatedealuser rename was applied on hadb1 (suffix _dropped_20260917)

~ /trunk/profitmandi-dao/src/main/resources/sql/retire_counter_privatedealuser_20260916.sql

Rev 37679 – feat(warehouse): carry the PO creator through purchase order creation for ...

amit — Thu, 17 Sep 2026 16:29:06 +0530

amit – 4 file(s) modified
feat(warehouse): carry the PO creator through purchase order creation for backdated auto-approval

Backdated POs are to be auto-approved in the name of whoever raised them, but the service had no way
to know who that was: the model carried no user and buyerId is the seller, not a person.

- CreateWarehousePurchaseOrderModel carries createdBy
- resolvedMismatchRequest takes resolvedBy, so the correction PO raised on a GRN price mismatch -
dated to the supplier invoice and therefore backdated by nature - is credited to whoever resolved it
- staged backfill SQL for the three backdated POs still stuck in INIT inside POScheduler's
auto-close window; data only, no schema change, not yet run

The release-to-READY change in PurchaseOrderServiceImpl follows separately.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/CreateWarehousePurchaseOrderModel.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/GrnRequestService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/warehouse/GrnRequestServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/backdated_po_auto_approval_backfill_20260917.sql

Rev 37667 – chore(sql): drop vendor catalog pricing for supplier 1 Hotspot, flagged ...

amit — Wed, 16 Sep 2026 19:22:00 +0530

amit – 1 file(s) modified
chore(sql): drop vendor catalog pricing for supplier 1 Hotspot, flagged internal 2026-09-16 (as run on prod; backups _bak_vcp/_vcpl_internal_hotspot_20260916)

+ /trunk/profitmandi-dao/src/main/resources/sql/drop_internal_vendor_catalog_pricing_hotspot_20260916.sql

Rev 37652 – Internal store buyer address: always read user.address An INTERNAL ...

amit — Wed, 16 Sep 2026 15:32:35 +0530

amit – 4 file(s) modified
Internal store buyer address: always read user.address

An INTERNAL store is the buying identity of a warehouse, so order creation
resolved its address from transaction.warehouseaddressmaster while every
screen read transaction.addId -> user.address. The Delhi warehouses moved to
warehouseaddressmaster 26 in Sept 2024 and nothing carried that across, so
user.address still held the 2021 L-222 premises: same buyer, two addresses.

user.address for active internal stores is now aligned with the mapped
warehouse address (sql/20260916_sync_internal_store_addresses.sql), split
exactly as getCustomAddress() does so the next invoice is unchanged. With
that true, createTransactionForWarehouse no longer special-cases internal.

SellerService.syncInternalStoreAddresses carries a warehouse address remap
through to its internal stores - the mapping has no edit screen and is
repointed directly in the database, which is why nothing propagated before.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/SellerService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/SellerServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/transaction/TransactionServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/20260916_sync_internal_store_addresses.sql

Rev 37650 – SQL to retire user.counter / user.privatedealuser / address mapping after ...

amit — Wed, 16 Sep 2026 09:28:33 +0530

amit – 1 file(s) modified
SQL to retire user.counter / user.privatedealuser / address mapping after r37648-49 deploy (rename, reversible)

+ /trunk/profitmandi-dao/src/main/resources/sql/retire_counter_privatedealuser_20260916.sql

Rev 37646 – Hot Deal brand: dedupe model_hot_deal to one row per catalog ...

amit — Tue, 15 Sep 2026 18:40:23 +0530

amit – 1 file(s) modified
Hot Deal brand: dedupe model_hot_deal to one row per catalog before the unique key (prod had one Realme duplicate from the old windowed model)

~ /trunk/profitmandi-dao/src/main/resources/sql/hot_deal_brand_migration.sql

Rev 37640 – Hot Deal brand: brand-driven membership, model_hot_deal reduced to attributes + ...

amit — Tue, 15 Sep 2026 17:39:20 +0530

amit – 14 file(s) modified
Hot Deal brand: brand-driven membership, model_hot_deal reduced to attributes + oem_brand + oem_catalog_id, migration SQL

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/cart/CartServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/ModelHotDeal.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/TagListing.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/CatalogRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/CatalogRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/ModelHotDealRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/ModelHotDealRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/service/solr/FofoSolr.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/ModelHotDealService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/ModelHotDealServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/pricing/PricingServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/hot_deal_brand_migration.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/hot_deal_brand_schema.sql
~ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/service/catalog/ModelHotDealServiceImplTest.java

Rev 37639 – chore(sql): second internal vendor catalog pricing cleanup, as run on ...

amit — Tue, 15 Sep 2026 17:38:59 +0530

amit – 1 file(s) modified
chore(sql): second internal vendor catalog pricing cleanup, as run on prod 2026-09-15

- 100 internal rows recreated 2026-09-14 18:07 for 325/326/371/382/406 (guard r37634 not yet deployed) and supplier 452, switched to internal 2026-09-15
- Backups _bak_vcp_internal_20260915 (251) / _bak_vcpl_internal_20260915 (367)

+ /trunk/profitmandi-dao/src/main/resources/sql/drop_internal_vendor_catalog_pricing_20260915.sql

Rev 37634 – feat(pricing): tag_listing price change log; retire vendoritempricing writes/reads in dao; ...

amit — Tue, 15 Sep 2026 17:23:02 +0530

amit – 14 file(s) modified
feat(pricing): tag_listing price change log; retire vendoritempricing writes/reads in dao; price drops no longer touch vendor catalog pricing

- New catalog.tag_listing_price_log (sql/create_tag_listing_price_log_20260915.sql, applied on prod 2026-09-15) + TagListingPriceService: the single setter for tag_listing DP/MOP/MRP, logging old/new values, source and user
- Remove applyPriceDrop (r37622): price drops write only price_drop + tag_listing; sql/revert_price_drop_vendor_catalog_pricing_20260915.sql restores the 30 rows it wrote (run on prod)
- VendorCatalogPricingService: reference TP = latest approved external TP (single + batched); internal suppliers rejected on create, bulk upload and approval
- Stop copying pricing into vendoritempricing on approval and item upload; tag listing detail TP from reference TP; indent DP from tag_listing only

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/TagListingPriceLog.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/inventory/SaholicInventoryCIS.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/TagListingPriceLogRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/TagListingPriceLogRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/VendorCatalogPricingLogRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/inventory/VendorCatalogPricingLogRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/ItemLoaderService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/VendorCatalogPricingService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/VendorCatalogPricingServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/tag/TagListingPriceService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/tag/TagListingPriceServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/tag/TagServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/create_tag_listing_price_log_20260915.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/revert_price_drop_vendor_catalog_pricing_20260915.sql

Rev 37625 – fix(internal-movement): trace serial origin to the latest external purchase, matching ...

amit — Mon, 14 Sep 2026 18:57:18 +0530

amit – 3 file(s) modified
fix(internal-movement): trace serial origin to the latest external purchase, matching billing

- resolveInternalMovementPrices orders the serial's external purchases newest first (was oldest), so transfers and billing name the same original vendor; no in-stock unit on prod changes (0 of 3,734)
- Restore resolveInternalMovementPrices javadoc onto its own method
- Add drop_internal_vendor_catalog_pricing_20260914.sql, as run on prod 2026-09-14 17:33 (15 New Spice internal suppliers; backups _bak_vcp/_bak_vcpl_internal_20260914)

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/model/InternalMovementPriceModel.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/warehouse/WarehouseInventoryItemRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/drop_internal_vendor_catalog_pricing_20260914.sql

Rev 37608 – Select a default schema before the multi-table deletes MySQL's ...

amit — Sat, 12 Sep 2026 17:57:09 +0530

amit – 1 file(s) modified
Select a default schema before the multi-table deletes

MySQL's 'DELETE alias FROM' form needs a default database even when every table is fully
qualified, so the deletes aborted with 'No database selected' while the backups above them
had already been written. Added USE inventory so the script runs end to end regardless of
how the client is invoked.

Run on hadb1 2026-09-12: 12,372 + 17,241 + 33,252 internal-supplier rows removed, external
pricing untouched at 40,670 / 45,049 / 68,749.

~ /trunk/profitmandi-dao/src/main/resources/sql/drop_internal_vendor_pricing_20260912.sql

Rev 37607 – SQL to remove vendor pricing held against internal suppliers ...

amit — Sat, 12 Sep 2026 17:45:19 +0530

amit – 1 file(s) modified
SQL to remove vendor pricing held against internal suppliers

Nothing reads these since r37603-05: an internal movement is priced from the stock being
moved, resolved to the original external vendor, and receiving no longer validates price for
those movements. A stale row would silently win over the derived price if any lookup were
reintroduced, so they are worse than inert.

Backs every row up to a _bak_ table and deletes against those frozen backups rather than
re-reading supplier.internal, so what is removed is exactly what is preserved. Run only
after the fofo WAR carrying the warehouse-grn-request-items.vm guard (r37606) is live.

+ /trunk/profitmandi-dao/src/main/resources/sql/drop_internal_vendor_pricing_20260912.sql

Rev 37601 – Remove dead stock-ageing fields rootInvoiceDate and oldest/newest_invoice_date rootInvoiceDate on ...

amit — Sat, 12 Sep 2026 14:51:19 +0530

amit – 3 file(s) modified
Remove dead stock-ageing fields rootInvoiceDate and oldest/newest_invoice_date

rootInvoiceDate on WarehouseInventoryItem had no writer anywhere in the codebase and
was read only by ScheduledTasks.refreshSnapshotAgeing(), which therefore matched zero
rows on every 30-minute run. oldest/newest_invoice_date on SaholicInventorySnapshot
were written only by that task and read by nothing. Verified on prod: null on all
1,528,669 inventoryItem rows and all 11,686 snapshot rows respectively.

Stock ageing that is actually in use resolves the original external supplier at query
time by serialNumber + supplier.internal = false, and does not read these columns.
originalInventoryItemId is deliberately left alone - it is populated on 157,020 rows.

Includes sql/drop_dead_ageing_columns_20260912.sql to drop the columns. Run it only
after web, fofo and cron are all on this build.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/inventory/SaholicInventorySnapshot.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/warehouse/WarehouseInventoryItem.java
+ /trunk/profitmandi-dao/src/main/resources/sql/drop_dead_ageing_columns_20260912.sql

Rev 37597 – Register /agedAppleImeis for the partner role RoleInterceptor authorises every ...

amit — Fri, 11 Sep 2026 19:55:40 +0530

amit – 1 file(s) modified
Register /agedAppleImeis for the partner role

RoleInterceptor authorises every non-admin request through
RoleManager.isAuthorizedURI(), which regex-matches the URI against dtr.api rows
reachable via dtr.role_api and throws GE_1004 when nothing matches. The new
aged-Apple IMEI endpoint (r37593) had no row, so partners would have been denied -
the Java change alone does not expose an endpoint.

Adds the api row and links it to FOFO, mirroring /activatedImeis (id 215) and
/activated-imeis-grn-pending (id 285). FOFO_ADMIN needs no row because isAdmin()
short-circuits before the lookup.

Applied to hadb1: api id 330, linked to role FOFO. Both inserts are guarded by
NOT EXISTS so the script is safe to re-run per environment.

+ /trunk/profitmandi-dao/src/main/resources/sql/register_aged_apple_imeis_api.sql

Rev 37592 – Fix live-demo deduction lost in the investment snapshot; maintain aged-Apple ...

amit — Fri, 11 Sep 2026 19:19:38 +0530

amit – 8 file(s) modified
Fix live-demo deduction lost in the investment snapshot; maintain aged-Apple qty

BUG FIX to r37588. InventoryService.getTotalAmountInStock() returns stock net of
live-demo units held past 90 days, and both original investment paths assigned
that netted value to inStockAmount - which is why
PartnerDailyInvestment.getTotalInvestment() carries no separate demo term. The
sweep stored the RAW snapshot sum and never applied the deduction, overstating
investment for 328 partners by Rs 5.19 crore and their limits by roughly Rs 1.3
crore. Maxwell validated clean earlier only because he holds no demo stock.

Fixed by storing what is measured and deriving what is computed: the column is
now in_stock_gross_amount and getNetInStockAmount() applies the haircut. The demo
holding moves on its own daily clock, so storing the netted figure would have
meant re-netting it in three places every time the haircut changed - the same
'two things must move together' trap behind the original cache bug. Deriving it
means a daily demo refresh flows through to stock and base with nothing to sync.

Also:
- aged_apple_qty stored on the snapshot and maintained on the same daily cycle as
aged_apple_stock_amount, so the partner banner reads a saved count instead of
running a count query per dashboard load, and count and value can never disagree
- selectPartnerStockCountMap mirrors selectPartnerStockValueMap, including the
activated exclusion
- selectAgedAppleStock lists the units behind the haircut for the partner view
- FofoUser exposes aged_apple_stock / aged_apple_qty
- stock-moved detection compares GROSS: a demo unit crossing 90 days changes net
stock but nothing physically moved, and that is the daily refresh's job

Schema: partner_investment_snapshot.sql updated (in_stock_gross_amount,
aged_apple_qty). Applied to prod - the table was empty, so it was dropped and
recreated from the script.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/PartnerDailyInvestment.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/PartnerInvestment.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/InventoryItemRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/InventoryItemRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/FofoUser.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/PartnerInvestmentServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/PartnerInvestmentSweepService.java
~ /trunk/profitmandi-dao/src/main/resources/sql/partner_investment_snapshot.sql

Rev 37588 – Serve partner investment from a 2-minute snapshot instead of a ...

amit — Fri, 11 Sep 2026 17:29:49 +0530

amit – 17 file(s) modified
Serve partner investment from a 2-minute snapshot instead of a 3-hour cache

getInvestment() was @Cacheable on a 3-hour Redis cache that addAmountToWallet
evicted but consumeAmountFromWallet did not. A partner whose advance payment was
swept straight to a loan had the money counted twice - once as a still-cached
wallet balance, once as the reduced utilisation - overstating their credit limit
by the payment x their tier until the cache expired.

Replaced with fofo.partner_investment, refreshed every 2 minutes by
PartnerInvestmentSweepService. Every coupled term is read in one pass, so wallet
and utilisation (and in-stock and aged-Apple) can never come from different
moments. A shorter TTL would only have made the error rarer - it scales with the
payment, not the delay.

- PartnerInvestment entity/repository + partner_investment_snapshot.sql
- PartnerInvestmentSweepService: 9 batched reads, stores base_value for change
detection, refreshes aged-Apple for partners whose stock moved intra-day
- getInvestment/getInvestmentsForFofoStores both read the snapshot, so the two
paths no longer disagree; live compute retained as fallback
- selectActivatedStockAmountByFofoIds: batches an N+1 that cost 233ms x 1690
partners (6.6 min -> 1.0 s)
- getFirstBillingDates: batches another N+1 (4.6 s -> 0.67 s)
- selectPendingGrnOrders(List) now applies the same SD_START_DATE floor as the
single-partner overload; the two were reporting different GRN-pending
- selectPartnerStockValueMap takes excludeActivated: an activated Apple handset
held past the aging window was added once to in-stock and subtracted twice
(activated stock, then the aged haircut). 90 units, 24 partners, Rs 65.19 lakh
double-deducted. Live-demo passes false - no overlap there.
- applyManualAdjustment: routes the two hand-rolled admin wallet adjustments
through WalletServiceImpl so they take the FOR UPDATE lock
- add_idx_order_grn_pending.sql: covering index, GRN query 1320ms -> 514ms

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/InventoryItem.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/fofo/PartnerInvestment.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/transaction/Order.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/InventoryItemRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/InventoryItemRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/PartnerInvestmentRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/fofo/PartnerInvestmentRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/OrderRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/TransactionRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/transaction/TransactionRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/InventoryServiceImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/PartnerInvestmentServiceImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/PartnerInvestmentSweepService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/wallet/WalletService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/wallet/WalletServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_idx_order_grn_pending.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/partner_investment_snapshot.sql

Rev 37581 – web offer sync: retire the pre-consolidation badges instead of deleting ...

amit — Thu, 10 Sep 2026 14:58:44 +0530

amit – 2 file(s) modified
web offer sync: retire the pre-consolidation badges instead of deleting them

The sync now deactivates any CIRCULAR row with a NULL circular_offer_key on its next
run. A row-keyed badge is a fragment of what a consolidated badge says, so leaving it
active would show a partner the same offer twice - once whole and once in pieces.

That removes the DELETE from the migration entirely, which is strictly better:

* nothing is destroyed, so a bad sync is one UPDATE away from being undone
* web_offer_product has NO foreign key to web_offer, so the DELETE had to clear its
532 rows separately or strand them as orphans
* no window where a circular has no badges at all - the old ones stay live until the
new ones exist

The migration is now purely additive: one ALTER, safe to run ahead of the deploy, which
is how it was applied to prod.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/resources/sql/add_web_offer_per_model.sql

Rev 37578 – web offer sync: read what the circular grants each MODEL, ...

amit — Thu, 10 Sep 2026 13:46:45 +0530

amit – 3 file(s) modified
web offer sync: read what the circular grants each MODEL, not each row

selectModelOffers returns one row per (catalog_id, benefit), carrying the context that
qualifies it - the banks it applies to, its tenures and their scheme, the timing and
the window. Consolidation happens in the service; this is the read it needs.

LEFT JOIN on the benefit, because a scheme-only offer carries no cashback and dropping
it here would lose the NCE/LCE badge entirely.

Content-keyed writes alongside: selectSyncedByKey, insertKeyedWebOffer,
replaceProducts, deactivateMissingKeys. A badge is now identified by a sha256 of its
terms, so models with identical terms share one and a model appears in exactly one.

Migration add_web_offer_per_model.sql adds circular_offer_key + its unique key, and
drops the row-keyed CIRCULAR badges - one of those is a fragment of what a
content-keyed badge says and there is no mapping from five fragments to one whole, so
they are regenerated by the next ingest.

⚠️ web_offer_product has NO foreign key to web_offer, so its rows do not cascade and
are deleted explicitly or they are orphaned. web_offer_sync_shadow does cascade.
source='CIRCULAR' only - the 3,850 hand-authored rows are never touched.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_web_offer_per_model.sql

Rev 37566 – Delist logic v3: require a month of no movement; add ...

amit — Thu, 10 Sep 2026 11:58:08 +0530

amit – 4 file(s) modified
Delist logic v3: require a month of no movement; add CatalogDelistService

Zero stock alone was too weak a test. It fired on SKUs partners were still
transacting, where the recent movement was often the very sale that emptied the
stock. v3 additionally requires no movement for a month on either side:
- SmartDukaan via warehouse.scanNew (3.4M rows, live). Note warehouse.scan is a
dead Saholic relic - 2,476 rows, last write 2012.
- any partner via fofo.scan_record, any scan type.
Phrased as NOT EXISTS so they short-circuit on the first recent row and use the
(inventoryItemId, scannedAt) / (inventory_item_id, create_timestamp) indexes.
An item that has never moved still qualifies, which is correct.

Also in v3:
- the internal pseudo-brands (Dummy, FOC, FOC HANDSET, Live Demo) are no longer
excluded and are swept like anything else.
- vendor-PO guard narrowed to status = 1. PARTIALLY_FULFILLED is UNREACHABLE,
not merely rare: none of the 8 PO-status writes in the codebase sets it, no
native SQL writes the column, and status = 2 has 0 rows across 51,593+ POs
back to 2011. Partial receipt is modelled per line (unfulfilledQuantity), so a
part-received PO sits in READY and is already caught. Documented so it is not
'restored' later as a supposed gap.

CatalogDelistService/Impl mirrors the migration statement for statement, for the
daily scheduled run. Bulk SQL rather than per-row updateActiveById +
publishStatusChange, which would commit to Solr once per catalog (~1,750 times
on the 2026-09-10 backlog); the job is instead scheduled ahead of the existing
06:00 full reindex. The dark-model step is deliberately house-wide rather than
run-scoped so it converges instead of leaving pre-existing dark models on a
stale RUNNING forever.

rollback_delist_recent_movement_20260910.sql restores the 24 listings / 21
models that the v2 run delisted before NEW 3 existed; their partners had
transacted them within the month. Id list frozen, not re-derived.

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/CatalogDelistService.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/catalog/CatalogDelistServiceImpl.java
~ /trunk/profitmandi-dao/src/main/resources/sql/delist_inactive_listings_rolling_24m.sql
+ /trunk/profitmandi-dao/src/main/resources/sql/rollback_delist_recent_movement_20260910.sql

Rev 37563 – Filter delisted SKUs out of creation pickers; add rolling 24m ...

amit — Thu, 10 Sep 2026 02:52:18 +0530

amit – 7 file(s) modified
Filter delisted SKUs out of creation pickers; add rolling 24m delist migration

Creation screens could offer items whose catalog.tag_listing.active = 0:
- Item.selectAllModels (scheme/offer model dropdown) joined tag_listing with
no active predicate. Adding tl.active = true also gives the model-level
rollup, since callers dedupe to catalogItemId: a model whose colours are
all inactive now yields no rows, one keeping a listed colour stays.
- The shared partner item picker had no active filter at all. Added a second
basis via getAllPartnerItemStringDescription(anyColor, activeOnly);
activeOnly participates in the fofoItems cache key so the two bases cannot
serve each other's cached results. Default stays false for screens that
inspect or edit existing records.
- New TagListing.selectActiveItemIds named query backs the filter.

Also adds the rolling 24-month delist migration: zero stock both sides, no
outstanding vendor PO (status IN (1,2), external supplier, unfulfilled > 0 --
INIT is excluded as it is a drawer of stale drafts), and re-categorises
fully-dark mobile models to OTHER. Idempotent, audited, rollback documented.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/Item.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/entity/catalog/TagListing.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/TagListingRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/catalog/TagListingRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/InventoryService.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/inventory/InventoryServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/delist_inactive_listings_rolling_24m.sql