How to get Item Rate from Price List with date wise


client script like:

frappe.ui.form.on(“Sales Order Item”,

item_code: function(frm, cdt, cdn)

var d = locals[cdt][cdn];


method: “get_mrp”,
args: {
doctype: “Item Price”,
filters: [
[“Item Price”, “item_code”, “=”, d.item_code],
callback: function(r)
d.mrp = item_price.price_list_rate;






Server Script Like:
Script Type: API
API Method: get_mrp
Allow guest: 1

def get_mrp(doc,d):
for d in doc.get(‘items’):
d.mrp = (frappe.db.sql(‘’‘select
tabItem Price.price_list_rate
from tabItem Price join tabSales Order Item join tabSales Order
where tabSales Order Item.parent = tabSales
and (tabSales Order.transaction_date BETWEEN tabItem Price.valid_from AND tabItem Price.valid_upto)
and tabItem Price.item_code = tabSales Order Item.item_code
and tabItem Price.price_list = “Standard Selling”
and tabSales = %(name)s
‘’’, {

but when item code select then second price list rate did not show.

anybody help me please…