USP_DATALIST_CLOSINGREQUIREMENT

CREATE procedure dbo.USP_DATALIST_CLOSINGREQUIREMENT
(@PDACCOUNTSYSTEMID uniqueidentifier)
as
    set nocount on;

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

  select
    CLOSINGREQUIREMENT.ID ,
    CLOSINGREQUIREMENT.DESCRIPTION,
    dbo.UFN_CLOSINGREQUIREMENT_GETINCLUDERECORDSFORDATALIST(CLOSINGREQUIREMENT.ID) as INCLUDEDRECORDS,
    GLACCOUNT.ACCOUNTSTRING as ACCOUNTSTRING,
    CLOSINGREQUIREMENT.NETASSETCLASSIFICATION as NETASSETCLASSIFICATION,
    NULL AS PRESERVEDETAILSDURINGCLOSING,
    NULL AS REQUIRETOBALANCE,
    NULL as PARENTID,
    case when CLOSINGREQUIREMENT.LEDGERACCOUNTID is null then 'RES:warning' else '' end as IMAGEKEY,
    CLOSINGREQUIREMENT.DESCRIPTION as PARENTDESCRIPTION
  from dbo.CLOSINGREQUIREMENT
    left join dbo.GLACCOUNT on CLOSINGREQUIREMENT.LEDGERACCOUNTID = GLACCOUNT.ID
  where CLOSINGREQUIREMENT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

  union all

  select CLOSINGREQUIREMENTDETAIL.CLOSINGREQUIREMENTID, --I am including the parent here so that we can edit and delete the parent on the page. We do not need the details ID on tha page as it is of no use to us.

    PDACCOUNTSTRUCTURE.DESCRIPTION as DESCRIPTION,
    CLOSINGREQUIREMENTDETAIL.ACCOUNTREQUIREMENTS AS INCLUDEDRECORDS,
    NULL as ACCOUNTSTRING,
    NULL AS NETASSETCLASSIFICATION,
    CLOSINGREQUIREMENTDETAIL.PRESERVEDETAILSDURINGCLOSING,
    CLOSINGREQUIREMENTDETAIL.REQUIRETOBALANCE,
    CLOSINGREQUIREMENTDETAIL.CLOSINGREQUIREMENTID AS PARENTID,
    '',
    CLOSINGREQUIREMENT.DESCRIPTION as PARENTDESCRIPTION
  from
    dbo.CLOSINGREQUIREMENTDETAIL
    inner join dbo.CLOSINGREQUIREMENT on CLOSINGREQUIREMENTDETAIL.CLOSINGREQUIREMENTID = CLOSINGREQUIREMENT.ID
    left join dbo.PDACCOUNTSTRUCTURE on CLOSINGREQUIREMENTDETAIL.ACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  where CLOSINGREQUIREMENT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
  order by PARENTDESCRIPTION, PARENTID, DESCRIPTION