Spin-orbital order and excitations in $3d^4$, $4d^4$, and $5d^4$ systems: Application to $\rm BaFeO_3$, $\rm Sr_2RuO_4$, $\rm Sr_2YIrO_6$, and $\rm K_2OsCl_64$
Evolution of composite spin-orbital order and coupled spin-orbital excitations is studied in a variety of $d^4$ systems with $n$=$4$ electrons in the $t_{2g}$ orbital sector using the generalised self-consistent + fluctuations approach for a realistic interacting-electron model. Within this unified approach, applications are discussed to compounds with $3d$, $4d$, $5d$ transition-metal ions such as $\rm BaFeO_3$ ($\rm Fe^{4+}$), $\rm Ca_2RuO_4$ ($\rm Ru^{4+}$), $\rm Sr_2RuO_4$ ($\rm Ru^{4+}$), $\rm Sr_2YIrO_6$ ($\rm Ir^{5+}$), and $\rm K_2OsCl_6$ ($\rm Os^{4+}$). Continuous interpolation from the nominally $S=1$ antiferromagnetic order in $\rm Ca_2RuO_4$ (strong crystal field, intermediate spin-orbit coupling (SOC) and Coulomb interaction) to the $J=0$ state relevant for $5d^4$ compounds (strong SOC, weak Coulomb interaction) and to the half-metallic ferromagnetic order when crystal field is negligible as in $\rm BaFeO_3$ (weak SOC, strong Coulomb interaction) and $\rm Sr_2RuO_4$ (intermediate SOC and Coulomb interaction) provides new fundamental insights into the magnetism of spin-orbit coupled systems.