UFN_FINANCIALTRANSACTIONJOURNALENTRIES_TOITEMLISTXML
Returns a table of all the distributions and line items for a financial transaction
Return
| Return Type |
|---|
| xml |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FINANCIALTRANSACTIONID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_FINANCIALTRANSACTIONJOURNALENTRIES_TOITEMLISTXML(
@FINANCIALTRANSACTIONID uniqueidentifier
)
returns xml as
begin
return (
select T.* from
(select
FTLI.ID,
CAST(FTLI.SEQUENCE as nvarchar(10)) [NUMBER],
case when len(RS.DESCRIPTION) > 0 then RS.DESCRIPTION else FTLI.DESCRIPTION end as DESCRIPTION,
FTLI.SEQUENCE [SEQUENCING],
0 [SEQUENCING1],
FTLI.TRANSACTIONAMOUNT [DEBITAMOUNT],
FTLI.TRANSACTIONAMOUNT [CREDITAMOUNT],
'' [CLASS],
'' [JOURNAL],
'' [DATAELEMENT1ID],
'' [DATAELEMENT2ID],
'' [DATAELEMENT3ID],
'' [DATAELEMENT4ID],
'' [DATAELEMENT5ID],
'' [DATAELEMENT6ID],
'' [DATAELEMENT7ID],
'' [DATAELEMENT8ID],
'' [DATAELEMENT9ID],
'' [DATAELEMENT10ID],
'' [DATAELEMENT11ID],
'' [DATAELEMENT12ID],
'' [DATAELEMENT13ID],
'' [DATAELEMENT14ID],
'' [DATAELEMENT15ID],
'' [DATAELEMENT16ID],
'' [DATAELEMENT17ID],
'' [DATAELEMENT18ID],
'' [DATAELEMENT19ID],
'' [DATAELEMENT20ID],
'' [DATAELEMENT21ID],
'' [DATAELEMENT22ID],
'' [DATAELEMENT23ID],
'' [DATAELEMENT24ID],
'' [DATAELEMENT25ID],
'' [DATAELEMENT26ID],
'' [DATAELEMENT27ID],
'' [DATAELEMENT28ID],
'' [DATAELEMENT29ID],
'' [DATAELEMENT30ID]
from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
left outer join dbo.REVENUESPLIT RS on FTLI.ID = RS.ID
where FTLI.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID
and FTLI.VISIBLE = 1
union all
select
JE.ID,
'' [NUMBER],
CAST(JE.SEQUENCE as nvarchar(10)) [DESCRIPTION],
FTLI.SEQUENCE [SEQUENCING],
JE.SEQUENCE [SEQUENCING1],
case JE.TRANSACTIONTYPECODE when 0 then JE.[TRANSACTIONAMOUNT] else NULL end [DEBITAMOUNT],
case JE.TRANSACTIONTYPECODE when 1 then JE.[TRANSACTIONAMOUNT] else NULL end [CREDITAMOUNT],
JE.[CLASS],
(select JC.DESCRIPTION from dbo.JOURNALCODE JC where JC.ID = JOURNALCODEID) [JOURNAL],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT1ID) [DATAELEMENT1ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT2ID) [DATAELEMENT2ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT3ID) [DATAELEMENT3ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT4ID) [DATAELEMENT4ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT5ID) [DATAELEMENT5ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT6ID) [DATAELEMENT6ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT7ID) [DATAELEMENT7ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT8ID) [DATAELEMENT8ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT9ID) [DATAELEMENT9ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT10ID) [DATAELEMENT10ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT11ID) [DATAELEMENT11ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT12ID) [DATAELEMENT12ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT13ID) [DATAELEMENT13ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT14ID) [DATAELEMENT14ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT15ID) [DATAELEMENT15ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT16ID) [DATAELEMENT16ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT17ID) [DATAELEMENT17ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT18ID) [DATAELEMENT18ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT19ID) [DATAELEMENT19ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT20ID) [DATAELEMENT20ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT21ID) [DATAELEMENT21ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT22ID) [DATAELEMENT22ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT23ID) [DATAELEMENT23ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT24ID) [DATAELEMENT24ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT25ID) [DATAELEMENT25ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT26ID) [DATAELEMENT26ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT27ID) [DATAELEMENT27ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT28ID) [DATAELEMENT28ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT29ID) [DATAELEMENT29ID],
(select DE.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE DE where DE.ID = DATAELEMENT30ID) [DATAELEMENT30ID]
from
dbo.FINANCIALTRANSACTIONLINEITEM FTLI
inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
where FTLI.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and FTLI.VISIBLE = 1) T
order by T.SEQUENCING, T.SEQUENCING1 asc
for xml raw('ITEM'),type,elements,root('GLENTRIES'), binary base64
)
end