CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `hr_autopay_bank_report_a` AS
    select 
        `sb`.`login_id` AS `staff_code`,
        `hpci`.`calculation_id` AS `calculation_id`,
        concat(`sb`.`surname`, ' ', `sb`.`firstname`) AS `staff_name`,
        `hpci`.`basic_salary` AS `basic_salary`,
        `hpci`.`formula` AS `formula`,
        `hpci`.`deduct_amount` AS `deduct_amount`,
        `hpci`.`actual_amount` AS `actual_salary`,
        `hpci`.`mpf_ee` AS `mpf_ee`,
        `hpci`.`mpf_er` AS `mpf_er`,
        `hpci`.`pf_ee` AS `pf_ee`,
        `hpci`.`pf_er` AS `pf_er`,
        `hpci`.`actual_salary` AS `bank_salary`,
        `hs`.`hkid` AS `hkid`,
        ((`hps`.`schedule_month` - interval 1 month) + interval 15 day) AS `start_date`,
        `hs`.`term_date` AS `term_date`,
        `hsp`.`current_point` AS `current_point`,
        `hsp`.`effective_date` AS `effective_date`
    from
        (((((`hr_pay_calculation_item` `hpci`
        left join `hr_staff` `hs` ON ((`hs`.`id` = `hpci`.`staff_id`)))
        left join `staff_basic` `sb` ON (((`sb`.`login_id` = `hs`.`staff_code`)
            or isnull(`sb`.`login_id`))))
        left join `hr_pay_calculation` `hpc` ON ((`hpc`.`id` = `hpci`.`calculation_id`)))
        left join `hr_pay_schedule` `hps` ON ((`hps`.`id` = `hpc`.`schedule_id`)))
        left join `hr_staff_payroll` `hsp` ON (((`hsp`.`staff_id` = `hs`.`id`)
            and (`hsp`.`effective_date` <= ((`hps`.`schedule_month` - interval 1 month) + interval 15 day)))))
    where
        ((`hpci`.`status` in (2 , 3))
            and (isnull(`hsp`.`effective_date`)
            or (`hsp`.`effective_date` = (select 
                max(`hsp2`.`effective_date`)
            from
                `hr_staff_payroll` `hsp2`
            where
                ((`hsp2`.`staff_id` = `hs`.`id`)
                    and (`hsp2`.`effective_date` <= ((`hps`.`schedule_month` - interval 1 month) + interval 15 day)))))))
    group by `hpci`.`calculation_id` , `hs`.`staff_code`
    order by `hpci`.`calculation_id` , `sb`.`login_id`