SELECT
             pt.id
            ,max(pp.project_name) AS project_name
            ,max(pt.name) AS disposition_name
            ,max(pt.external_reference) AS SAP_code
            ,max(pt.work_order) AS work_order
            ,max(ru.name) AS project_manager
            ,max(pt.date_start) AS date_start
            ,max(pt.date_end) AS date_end
            ,max(pt.location) AS location
            ,COALESCE(ROUND(max(dl.contracted_quantity),2), 0.00) AS contracted_quantity
            ,COALESCE(ROUND(max(dl.realized_quantity),2), 0.00) AS realized_quantity
            ,COALESCE(ROUND(max(contracted_quantity - realized_quantity),2), 0.00) AS unrealized_quantity
            ,COALESCE(ROUND(max(dl.contracted_amount),2), 0.00) AS contracted_amount
            ,COALESCE(ROUND(max(dl.realized_amount),2), 0.00) AS realized_amount
            ,COALESCE(ROUND(max(contracted_amount - realized_amount),2), 0.00) AS unrealized_amount
            ,COALESCE(ROUND(max(ai.lcy_amount_untaxed),2), 0.00) AS invoiced_untaxed
            ,COALESCE(ROUND(max(ai.lcy_amount_total),2), 0.00) AS total_invoiced
            ,COALESCE(ROUND(max(ai.lcy_amount_total - ai.residual),2), 0.00) AS paid_amount
            ,COALESCE(ROUND(max(ai.residual),2), 0.00) AS unpaid_amount



FROM project_task pt
LEFT JOIN  (
        SELECT max(ru.id) AS id , max(rr.name) AS name
        FROM res_users ru
        LEFT JOIN resource_resource rr ON rr.user_id=ru.id
        GROUP BY ru.id)
            ru ON ru.id= pt.user_id

LEFT JOIN (
            SELECT max(task_id) AS task_id
                ,COALESCE(ROUND(SUM(quantity),2), 0.00) AS contracted_quantity
                ,COALESCE(ROUND(SUM(total_realized_quantity),2), 0.00) AS realized_quantity
                ,COALESCE(ROUND(SUM(quantity * price_unit),2), 0.00) AS contracted_amount
                ,COALESCE(ROUND(SUM(total_realized_quantity * price_unit),2), 0.00) AS realized_amount
                FROM disposition_line
           GROUP BY task_id
                        ) dl ON dl.task_id= pt.id
LEFT JOIN   (SELECT MAX(disposition_id) AS disposition_id
                    ,COALESCE(ROUND(sum(CASE WHEN  ai.type= 'out_invoice'
                                                THEN ai.lcy_amount_untaxed
                                            WHEN   ai.type= 'out_refund'
                                                THEN ABS(ai.lcy_amount_untaxed) * (-1)
                                                          END), 2), 0.00) AS lcy_amount_untaxed

                    ,COALESCE(ROUND(sum(CASE WHEN  ai.type= 'out_invoice'
                                                 THEN ai.lcy_amount_total
                                            WHEN ai.type= 'out_refund'
                                                THEN ABS(ai.lcy_amount_total) * (-1)
                                                          END), 2), 0.00) AS lcy_amount_total
                    ,COALESCE(ROUND(sum(CASE WHEN  ai.type= 'out_invoice'
                                                 THEN ai.residual
                                            WHEN ai.type= 'out_refund'
                                                THEN ABS(ai.residual) * (-1)
                                                          END), 2), 0.00) AS residual
        FROM account_invoice ai
            GROUP BY ai.disposition_id) ai ON ai.disposition_id= pt.id
LEFT JOIN (SELECT MAX(pp.id) AS id,
                    MAX(aaa.name) AS project_name
                    FROM project_project pp
                    LEFT JOIN account_analytic_account aaa
                    ON pp.analytic_account_id= aaa.id
        GROUP BY pp.id) pp on pp.id= pt.project_id

    GROUP BY pt.id