Spin-orbital order and excitations in $3d^4$, $4d^4$, and $5d^4$ systems: Application to $\mathrm{B}\mathrm{a}\mathrm{F}\mathrm{e}{\mathrm{O}}_3$, $\mathrm{S}{\mathrm{r}}_2\mathrm{R}\mathrm{u}{\mathrm{O}}_4$, $\mathrm{S}{\mathrm{r}}_2\mathrm{Y}\mathrm{I}\mathrm{r}{\mathrm{O}}_6$, and ${\mathrm{K}}_2\mathrm{O}\mathrm{s}\mathrm{C}{\mathrm{l}}_{6}4$

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 $\mathrm{B}\mathrm{a}\mathrm{F}\mathrm{e}{\mathrm{O}}_3$ ($\mathrm{F}{\mathrm{e}}^{4+}$), $\mathrm{C}{\mathrm{a}}_2\mathrm{R}\mathrm{u}{\mathrm{O}}_4$ ($\mathrm{R}{\mathrm{u}}^{4+}$), $\mathrm{S}{\mathrm{r}}_2\mathrm{R}\mathrm{u}{\mathrm{O}}_4$ ($\mathrm{R}{\mathrm{u}}^{4+}$), $\mathrm{S}{\mathrm{r}}_2\mathrm{Y}\mathrm{I}\mathrm{r}{\mathrm{O}}_6$ ($\mathrm{I}{\mathrm{r}}^{5+}$), and ${\mathrm{K}}_2\mathrm{O}\mathrm{s}\mathrm{C}{\mathrm{l}}_6$ ($\mathrm{O}{\mathrm{s}}^{4+}$). Continuous interpolation from the nominally $S=1$ antiferromagnetic order in $\mathrm{C}{\mathrm{a}}_2\mathrm{R}\mathrm{u}{\mathrm{O}}_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 $\mathrm{B}\mathrm{a}\mathrm{F}\mathrm{e}{\mathrm{O}}_3$ (weak SOC, strong Coulomb interaction) and $\mathrm{S}{\mathrm{r}}_2\mathrm{R}\mathrm{u}{\mathrm{O}}_4$ (intermediate SOC and Coulomb interaction) provides new fundamental insights into the magnetism of spin-orbit coupled systems.