Skip to main content

How to Patch bo_effective_rate

This document describes the step-by-step process to patch the bo_effective_rate field in the booking table using data from the LMS API.

Overview​

The bo_effective_rate field in the los_service.booking table needs to be updated with the annual effective interest rate calculated by the LMS API based on the booking's financing details.

Prerequisites​

  • Access to the database containing the los_service.booking and los_service.applications tables
  • Access to the LMS API endpoint for calculating payment schedules
  • Valid los_app_no for the booking you want to patch

Step 1: Get Data from Booking Table​

Query the booking table to retrieve the necessary information for the LMS API call.

SELECT
    a.product_code AS product_code,
    b.bo_car_price AS financing_price,
    b.bo_rate AS interest_rate,
    b.bo_term AS number_of_payment
FROM
    los_service.booking b
INNER JOIN los_service.applications a ON
    a.id = b.application_id
WHERE
    a.los_app_no = '2026011400001';

Output fields:

  • product_code: Product code from the application
  • financing_price: Financing price (bo_car_price)
  • interest_rate: Interest rate (bo_rate)
  • number_of_payment: Number of payment terms (bo_term)

Step 2: Call LMS API to Get Payment Schedule​

Use the data retrieved from Step 1 to call the LMS API and get the payment schedule, which includes the effective interest rate.

API Endpoint​

POST https://lms-api-uat.nonprod.sgc/uat/mgmt-contract-management/v1/calculate-payment-schedule

Request Example​

curl --request POST \
  --url https://lms-api-uat.nonprod.sgc/uat/mgmt-contract-management/v1/calculate-payment-schedule \
  --header 'content-type: application/json' \
  --data '{
    "header": {
        "FuncNm": "GETPAYMENTSCHEDULE",
        "RqUID": "509_20231220_2023122000050",
        "RqDt": "20231220140500",
        "RqAppId": "586",
        "UserId": "kbank01",
        "AuthUserId": ""
    },
    "body": {
        "product_code": "1010020001002",
        "financing_price": 142000,
        "interest_rate": 8.1,
        "number_of_payment": 60,
        "first_payment_date": "2023-10-20"
    }
}'

Request Parameters​

Header:

  • FuncNm: Function name (e.g., "GETPAYMENTSCHEDULE")
  • RqUID: Request unique identifier
  • RqDt: Request date/time
  • RqAppId: Request application ID
  • UserId: User ID
  • AuthUserId: Authorized user ID (can be empty)

Body:

  • product_code: Product code from Step 1
  • financing_price: Financing price from Step 1
  • interest_rate: Interest rate from Step 1
  • number_of_payment: Number of payments from Step 1
  • first_payment_date: First payment date (format: YYYY-MM-DD)

Step 3: Update bo_effective_rate​

Extract the annual_effective_interest_rate from the LMS API response and update the booking table.

API Response Format​

{
  "header": {
    "FuncNm": "GETPAYMENTSCHEDULE",
    "RqUID": "509_20231220_2023122000050",
    "RsAppId": "LMS",
    "RsDt": "20260116160547",
    "StatusCode": "00"
  },
  "body": {
    "financing_price": 142000.00,
    "charged_interest": 57513.80,
    "total_payment_amount": 213480.00,
    "installment_amount": 3558.00,
    "last_installment": 3558.00,
    "monthly_effective_interest_rate": 1.19056,
    "annual_effective_interest_rate": 14.28672,
    "total_accrued_interest": 57514.43921,
    "last_accrual_date": "2028-09-19",
    "summary_vat_amount": 13966.20,
    "IRR": 14.28672,
    "vat_percentage": 7.00,
    "payment_schedule_list": []
  }
}

Key field:

  • annual_effective_interest_rate: This is the value to update in bo_effective_rate

Update Query​

UPDATE los_service.booking
SET bo_effective_rate = 14.28672  -- Use annual_effective_interest_rate from API response
WHERE application_id = (
    SELECT id
    FROM los_service.applications
    WHERE los_app_no = '2026011400001'
);

Note: Replace 14.28672 with the actual annual_effective_interest_rate value from the API response, and replace '2026011400001' with the actual los_app_no you're working with.

Complete Workflow Example​

  1. Query booking data:
SELECT a.product_code, b.bo_car_price, b.bo_rate, b.bo_term
FROM los_service.booking b
INNER JOIN los_service.applications a ON a.id = b.application_id
WHERE a.los_app_no = '2026011400001';

  1. Call LMS API with the retrieved values

  2. Extract annual_effective_interest_rate from the API response

  3. Update the booking table:

UPDATE los_service.booking
SET bo_effective_rate = <annual_effective_interest_rate>
WHERE application_id = (SELECT id FROM los_service.applications WHERE los_app_no = '<los_app_no>');

Important Notes​

  • Always verify the StatusCode in the API response header is "00" before using the response data
  • Ensure the first_payment_date in the API request is accurate
  • Double-check the los_app_no before executing the update query
  • Consider backing up the data before performing updates in production
  • The API endpoint URL may differ between environments (UAT, SIT, Production)

Troubleshooting​

API Returns Error​

  • Verify all request parameters are correct
  • Check that the product_code exists in the LMS system
  • Ensure the first_payment_date is in the correct format (YYYY-MM-DD)

Update Query Affects No Rows​

  • Verify the los_app_no exists in the applications table
  • Check that the application_id relationship is correct
  • Confirm you have the necessary database permissions