Support Rate Fetching Based on Customer Category (`FetchRateAsPerCategory`)

Introduce a new system parameter FetchRateAsPerCategory that changes how the ERP fetches selling prices from the Price Master (PriceMst). When enabled, the system queries the Price Master using the selected customer’s category (by joining with partymst to find any customer under the same category) rather than looking up the selected customer’s ID directly.

This approach avoids making any database schema or UI changes to the Price Master.

User Review Required

Open Questions

None.

Proposed Changes

1. Database Migrations

[NEW] V0_0_0_0_0_78__Insert_Syspara_FetchRateAsPerCategory.sql

• Seed the new system parameter FetchRateAsPerCategory with a default value of 'N' (OFF) into the sysparameters table to guarantee backward compatibility.

2. Core Pricing & Sales Order Components

[MODIFY] ISalesOrderRepository.cs

• Add a new helper method signature string GetPartyCategoryCode(string partyId, string dbname) to get the category code of a customer.

[MODIFY] SalesOrderRepository.cs

• GetPartyCategoryCode: Implement the lookup of categorycode from the partymst table.
• GetPrice:
  • Check the FetchRateAsPerCategory parameter via _IUtilityMethodsRepository.GetSysParameterValue.
  • If OFF, execute the existing customer-specific query.
  • If ON, execute the category-based query joining with partymst:

    SELECT TOP 1 P.price AS rate, P.MRP, P.factor AS pricemstfactor 
    FROM pricemst P
    INNER JOIN partymst PM ON P.partyid = PM.partyid
    WHERE P.itemid = @itemId 
      AND PM.categorycode = (SELECT categorycode FROM partymst WHERE partyid = @partyId)
      AND P.Brandid = @brandId 
      AND P.Currency = @currency 
    ORDER BY P.Updatedon DESC, P.id DESC
• SalesOrder1Import:
  • Check the FetchRateAsPerCategory parameter.
  • Dynamically adjust the SQL CTE updateTempData query join condition for pricing:
    • If ON: Match PriceMst.partyid via partymst category code:

      OUTER APPLY(SELECT TOP 1 price,mrp,factor FROM pricemst P 
      INNER JOIN partymst PM ON P.partyid = PM.partyid
      WHERE P.itemid = T.itemid  
        AND P.brandid = T.brandid AND P.currency = T.Currency 
        AND PM.categorycode = (SELECT categorycode FROM partymst WHERE partyid = T.partyid)
      ORDER BY updatedon DESC ) X
    • If OFF: Use the existing customer ID match:

      OUTER APPLY(SELECT TOP 1 price,mrp,factor FROM pricemst P 
      WHERE P.itemid = T.itemid  
        AND P.brandid = T.brandid AND P.currency = T.Currency AND P.partyid = T.partyid
      ORDER BY updatedon DESC ) X

[MODIFY] SalesOrderController.cs

• SalesOrder1ValidateCreate:
  • If FetchRateAsPerCategory is ON and the retrieved price is null or 0:
    • Validate if the customer actually has a category code assigned in partymst. If not, add a descriptive error: "Customer Category is not assigned for this Customer. Cannot fetch rate."
    • Otherwise, append the custom message: "Rate is not available in Price Master for any Customer in the same Customer Category. Are you missing the brand/currency?"

3. Other Impacted Modules & Reports

[MODIFY] AdvanceLicenseRepository.cs

• GetRate: Update to respect the FetchRateAsPerCategory system parameter. If enabled, query PriceMst joining with partymst to match the customer category:

  SELECT TOP 1 {selectQuery} AS rate FROM pricemst P
  INNER JOIN partymst PM ON P.partyid = PM.partyid
  WHERE P.itemid = @itemId 
    AND PM.categorycode = (SELECT categorycode FROM partymst WHERE partyid = @partyId)
    AND P.Brandid = @brandId 
    AND P.Currency = @exportCurrency
  ORDER BY P.updatedon DESC, P.id DESC

[MODIFY] SalesAnalysisReportRepository.cs

• Update the pricemst outer apply join in the Sales Return/Rejection report query to join with partymst and match categories when the parameter is ON:

  OUTER APPLY (SELECT TOP 1 price FROM pricemst P
  INNER JOIN partymst PM ON P.partyid = PM.partyid
  WHERE Currency='INR' AND S1.Itemid = P.itemid 
    AND PM.categorycode = (SELECT categorycode FROM partymst WHERE partyid = S.partyid)
    AND S1.Brandid = P.brandid
  ORDER BY Updatedon DESC,id DESC  ) X

Verification Plan

Automated Tests

• No automated test project exists in this workspace.

Manual Verification

1. Database Script Verification: Run the migration script and ensure the FetchRateAsPerCategory system parameter is inserted.
2. Settings UI Check: Navigate to System Parameters page and verify that the new parameter FetchRateAsPerCategory shows up with default value No.
3. Standard Pricing Behavior (Parameter OFF):
   • Create a Sales Order line. Confirm rates are fetched from PriceMst matching the customer’s ID.
4. Category Pricing Setup:
   • Verify Category '01' exists, and both Customer A and Customer B are assigned to Category '01'.
   • Setup a price record in Price Master for Customer B.
   • Toggle the parameter FetchRateAsPerCategory to Yes on the system parameter screen.
5. Category Pricing Behavior (Parameter ON):
   • Create a Sales Order line for Customer A. Verify that the rate is fetched successfully (it matches the price defined for Customer B because they share Category '01').
   • Verify that trying to create a line for a customer without an assigned category fails with: "Customer Category is not assigned for this Customer. Cannot fetch rate."
6. Re-update Rate Check:
   • On the line details page of a line belonging to Customer A, click “Update Rate/MRP” and verify that it correctly fetches/updates the rate based on Customer B’s price master record.
7. Bulk Import Check:
   • Import Sales Order lines using Excel import and verify that category-based price matching works during bulk uploads.
8. Regression Check:
   • Turn the parameter back to No and verify standard customer pricing is restored.