Query to collect transactions for a payment block in SEPA Direct Debit file

DrctDbtTxInf query

select xmlelement( "DrctDbtTxInf"
                 , xmlelement( "PmtId"
                             , xmlforest( e.empno as "InstrId"
                                        , rpad(e.empno,2,0)||to_char(sysdate,'ddmmyyyy') as "EndToEndId"
                                        )
                             )
                 , xmlelement( "InstdAmt"
                             , xmlattributes('EUR' as "Ccy")
                             , '50.00'
                             )
                 , xmlelement( "DrctDbtTx"
                             , xmlelement( "MndtRltdInf"
                                         , xmlforest( lpad(e.empno,6,0) as "MndtId"
                                                    , to_char(e.hiredate,'dd-mm-yyyy') as "DtOfSgntr"
                                                    , 'false' as "AmdmntInd"
                                                    )
                                        )
                             , xmlelement( "CdtrSchmeId"
                                         , xmlforest( 'Your Name Here' as "Nm"
                                                    , xmlforest( 'First Address Line' as "AdrLine"
                                                               , 'Second Address Line' as "AdrLine"
                                                               ) as "PstlAdr"
                                                    )
                                        )
                             )
                 , xmlelement( "DbtrAgt"
                             , xmlelement( "FinInstnId"
                                         , xmlelement( "BIC",a.bic)
                                         )
                             )
                 , xmlelement( "Dbtr"
                             , xmlelement("Nm",a.account_holder)
                             , xmlelement("PstlAdr"
                                         , xmlforest( 'Debitor Adress Line1' as "AdrLine"
                                                    , 'Debitor Adress Line2' as "AdrLine"
                                                    )
                                         ) 
                            )
                 , xmlelement( "DbtrAcct"
                             , xmlelement( "Id"
                                         , xmlelement( "IBAN",a.iban)
                                         )
                             )
                 , xmlelement( "Purp"
                             , xmlelement( "Cd"
                                         , 'OTHR'
                                         )
                             )
                 , xmlelement( "RmtInf"
                             , xmlelement( "Ustrd"
                                         , 'Give_me_your_money_'||to_char( round(sysdate,'Q'),'yyyy-Q')
                                         )
                             )
                 ) as "DrctDbtTxInf"
from   emp e
,      emp_accounts a
where  e.empno = a.empno