i have managed, the code below is working fine. this is for juniors like me. just one thing i have work on which is clean_id , i have passed hard coded value because it is throwing error on the lines i have remarked.

for rec in self:

    # clean_id = rec.id

    # clean_id = self.id

    # clean_id = self._origin.id

    clean_id = 448

    req_date = rec.request_date

    query = """SELECT id, name, tenure_days, tenure_months, tenure_years FROM employee_tenure WHERE id = %s"""

    self.env.cr.execute(query, (clean_id,))

    result = self.env.cr.dictfetchall()

    # 

    for row in result:

        if row.get('tenure_months') > 12:

            # get payslip line amount for rule_id 107

            query2 = """SELECT pl_amount FROM emp_pf_amount WHERE employee_id = %s"""

            self.env.cr.execute(query2, (clean_id,))

            result2 = self.env.cr.dictfetchall()

            x_amount = 0

            for pl_row in result2:

                x_amount = pl_row.get('pl_amount')

                rec['x_loan_applicable_amount'] = x_amount

#

# get payslip input amount for rule_id 16

            # to create date_from and date_to from request_date year

            dt_string = req_date.strftime("%Y-%m-%d")

            dt1 = datetime.datetime.strptime(dt_string, "%Y-%m-%d")

            dt_month = dt1.month

            dt_month1 = '07'

            dt_date1 = '01'

            dt_month2 = '06'

            dt_date2 = '30'

            if dt_month in (7,8,9,10,11,12):

                dt_year1 = dt1.year

                dt_year2 = dt1.year + 1

            else:

                dt_year1 = dt1.year - 1

                dt_year2 = dt1.year

            dt_year1 = str(dt_year1)

            dt_year2 = str(dt_year2)

            # dt_range = 'to_date(\''+dt_year1+'-'+dt_month1+'-'+dt_date1+'\','+'\'YYYY-MM-DD\''+') AND '+'to_date(\''+dt_year2+'-'+dt_month2+'-'+dt_date2+'\','+'\'YYYY-MM-DD\''+')'

            dt_from = '\''+dt_year1+'-'+dt_month1+'-'+dt_date1+'\''

            dt_to = '\''+dt_year2+'-'+dt_month2+'-'+dt_date2+'\''

# query3 = """SELECT emp_allocation_amount FROM emp_ann_leave_amount WHERE employee_id = %s AND date_from between %s"""

            query3 = """SELECT emp_allocation_amount FROM emp_ann_leave_amount WHERE employee_id = %s AND date_from BETWEEN %s AND %s"""

            self.env.cr.execute(query3, (clean_id, dt_from, dt_to))

            result3 = self.env.cr.dictfetchall()

            for pi_row in result3:

                x_amount += pi_row.get('emp_allocation_amount')

                rec['x_loan_applicable_amount'] = x_amount

        else:

            rec['x_loan_applicable_amount'] = 1

happy coding.

regards