UFN_REVENUE_GENERATEPLEDGEWRITEOFFGLDISTRIBUTION

  CREATE function dbo.UFN_REVENUE_GENERATEPLEDGEWRITEOFFGLDISTRIBUTION
  (
    @REVENUEID uniqueidentifier ,
    @WRITEOFFIDTABLE UDT_GENERICID readonly
  )
      returns table 
      as return 
      select distinct
        WRITEOFF_EXT.ID AS WRITEOFFID,
        WRITEOFFLINEITEM.ID as WRITEOFFLINEITEMID,
        dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(205, PLEDGE.TYPECODE, WRITEOFFLINEITEM_EXT.TYPECODE, WRITEOFFLINEITEM_EXT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
        tf.TRANSACTIONTYPECODE,
        case 
          when WRITEOFFADJUSTMENT.ID is null then WRITEOFFLINEITEM.POSTDATE            --if no unposted adjustment then use write-off post date

          else WRITEOFFADJUSTMENT.POSTDATE
        end AS POSTDATE,
        case 
          when WRITEOFFADJUSTMENT.ID is null then WRITEOFFLINEITEM.POSTSTATUSCODE            --if no unposted adjustment then use write-off post status code

          else WRITEOFFADJUSTMENT.POSTSTATUSCODE
        end AS POSTSTATUSCODE, 
        tf.ACCOUNTSTRING,
        tf.PROJECTCODE as PROJECT,
        WRITEOFFLINEITEM.BASEAMOUNT as AMOUNT,
        case PLEDGE.TYPECODE
            when 1 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (WRITEOFFLINEITEM.SOURCELINEITEMID, 'write-off', 'Pledge')
            when 15 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (WRITEOFFLINEITEM.SOURCELINEITEMID, 'write-off', 'Membership installment plan')
        end as REFERENCE,
        tf.ERRORMESSAGE,
        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
        PLEDGE.TYPECODE as REVENUETRANSACTIONTYPECODE,
        tf.ACCOUNTID as ACCOUNTID,
        WRITEOFFLINEITEM.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT, 
        WRITEOFFLINEITEM.ORGAMOUNT as ORGANIZATIONAMOUNT, 
        WRITEOFF.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID, 
        V_BASECURRENCYFORFINANCIALTRANSACTION_I.BASECURRENCYID, 
        WRITEOFF.BASEEXCHANGERATEID as BASEEXCHANGERATEID, 
        WRITEOFF.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
        convert(varchar(max),tf.MAPPEDVALUES) as MAPPEDVALUES        
      from @WRITEOFFIDTABLE WRITEOFFIDTABLE
      inner join dbo.WRITEOFF_EXT on WRITEOFFIDTABLE.ID = WRITEOFF_EXT.ID
      inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFF_EXT.ID = WRITEOFF.ID
      inner join dbo.FINANCIALTRANSACTION as PLEDGE on WRITEOFF.PARENTID = PLEDGE.ID
      inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PLEDGE.ID
      left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I with (noexpand) on V_BASECURRENCYFORFINANCIALTRANSACTION_I.FINANCIALTRANSACTIONID = PLEDGE.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFLINEITEM on WRITEOFFLINEITEM.FINANCIALTRANSACTIONID = WRITEOFF.ID
      inner join dbo.REVENUESPLIT_EXT WRITEOFFLINEITEM_EXT on WRITEOFFLINEITEM.ID = WRITEOFFLINEITEM_EXT.ID
      left join dbo.WRITEOFFADJUSTMENT on WRITEOFF_EXT.ID = WRITEOFFADJUSTMENT.WRITEOFFID and WRITEOFFADJUSTMENT.POSTSTATUSCODE <> 0
      cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT_PLEDGEWRITEOFF (
        dbo.UFN_REVENUEINFORMATION_TOITEMLISTXML(PLEDGE.ID, 0, null), 
        dbo.UFN_REVENUESPLITINFORMATION_TOITEMLISTXML(PLEDGE.ID, WRITEOFFLINEITEM.SOURCELINEITEMID, 0, null),
        WRITEOFF_EXT.REASONCODEID
      ) tf
      where 
          PLEDGE.DELETEDON is null and
          WRITEOFFLINEITEM.DELETEDON is null and
          PLEDGE.ID = @REVENUEID