Dear All,

I'm creating a dynamic tree view using SQL query in which information is pulled from different models. The pulled information is jumbled and there is no linking of the information as the model is varied. For example, if we take SO & SOL, the SO number does not show the same SOL related to that SO number.

py is:

class DmrrReport(models.Model):
    _name = "dmrr.report"
    _description = "DMRR Report"
    _auto = False

    # Purchase Order Lines
    product_id = fields.Many2one('product.product', string="Product")
    description = fields.Text(string="Product Description")
    # hsn_code = fields.Char(string="HSN/SAC Code")
    product_qty = fields.Float(string="Quantity")
    price_unit = fields.Float(string="Unit Price")
    price_subtotal = fields.Char(string="Product Value")
    # taxes_id = fields.Many2many('account.tax', string="Taxes")
    product_uom = fields.Many2one('uom.uom', string="Unit")
    price_total = fields.Char(string="Total Purchase Cost")

    # Purchase Order
    partner_id = fields.Many2one('res.partner', string="Vendor")
    job_num = fields.Char(string="Job No.")
    po_wo_date = fields.Date(string="PO/WO Date")

    #Account Move / Bill Creation
    bill_number = fields.Char(string="Bill No.")
    invoice_date = fields.Date(string="Bill Date")

    # # Stock Picking
    stock_challan = fields.Char(string="Challan No.")
    challan_date = fields.Date(string="Challan Date")
    stock_grn = fields.Char(string="GRN No.")
    effective_date = fields.Date(String="Date")
    vehicle_no = fields.Char(String="Vehicle No.")
    entry_gate_no = fields.Char(String="Entry Gate No.")
    origin = fields.Char(String="PO/WO No.")

    def init(self):
        tools.drop_view_if_exists(self._cr, 'dmrr_report')
        self._cr.execute("""
        CREATE or REPLACE VIEW dmrr_report AS(
            SELECT row_number() OVER () AS id, line.product_id, line.description, line.product_qty, 
            line.price_unit, line.partner_id, line.job_num, line.stock_challan, line.stock_grn, 
            line.vehicle_no, line.effective_date, line.entry_gate_no, line.product_uom, line.price_subtotal,
            line.price_total, line.bill_number, line.invoice_date, line.origin, line.po_wo_date,
            line.challan_date FROM (
                SELECT pol.product_id as product_id,
                pol.name as description,
                pol.product_qty as product_qty,
                pol.price_unit as price_unit,
                pol.price_subtotal as price_subtotal,
                pol.price_total as price_total,
                pol.product_uom as product_uom,
                po.partner_id as partner_id,
                po.job_num as job_num,
                po.effective_date as effective_date,
                po.date_po as po_wo_date,
                st.challan as stock_challan,
                st.challan_date as challan_date,
                st.grn as stock_grn,
                st.vehicle_no as vehicle_no,
                st.origin as origin,
                st.entry_gate_no as entry_gate_no,
                am.ref as bill_number,
                am.invoice_date as invoice_date
                FROM purchase_order_line pol
                JOIN purchase_order po ON (po.id = pol.order_id)
                JOIN account_move am ON (am.id = pol.order_id)
                JOIN stock_picking st ON (st.id = pol.order_id)
            ) line
        )""")

Thank you in advance