USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS

CREATE procedure dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS
(
  @BATCHREVENUEID uniqueidentifier,
  @APPLICATIONRECOGNITIONS xml,
  @APPLICATIONSOLICITORS xml,
  @SPLITS xml,
  @APPLICATIONTYPECODE tinyint,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime,
  @REVENUERECOGNITIONIDS xml = null, -- Deprecated

  @BEFORESAVE bit = null -- Deprecated

)
as
begin
    declare @ADDITIONALAPPLICATIONTABLE table
  (
    TYPECODE tinyint,
    DESIGNATIONID uniqueidentifier,
    REVENUESPLITID uniqueidentifier,
    DECLINESGIFTAID bit,
    SPONSORSHIPID uniqueidentifier
  )

  if @SPLITS is not null                     
    insert into @ADDITIONALAPPLICATIONTABLE(TYPECODE, DESIGNATIONID, REVENUESPLITID, DECLINESGIFTAID,SPONSORSHIPID)
    select 
    @APPLICATIONTYPECODE TYPECODE, 
    T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
    T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') REVENUESPLITID,
    T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
    T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
    from @SPLITS.nodes('/SPLITS/ITEM') T(c)       

    union all

    select T.c.value('(TYPECODE)[1]','tinyint') TYPECODE, 
    T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
    T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') REVENUESPLITID,
    T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
    T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
    from @SPLITS.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)  


  if @APPLICATIONRECOGNITIONS is not null
  begin
  with CTE_APPLICATIONRECOGNITIONS as
  (
    select
      T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
      T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint') as ADDITIONALAPPLICATIONTYPECODE,
      T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as DESIGNATIONID,
      T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]','bit') as DECLINESGIFTAID,
      coalesce(T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier'),'00000000-0000-0000-0000-000000000000') as SPONSORSHIPID,
      case when T.c.exist('./RECOGNITIONS/ITEM') = 1 then convert(xml,T.c.query('RECOGNITIONS[1]')) else null end as RECOGNITIONS
    from @APPLICATIONRECOGNITIONS.nodes('/APPLICATIONRECOGNITIONS/ITEM') T(c)
  ),
  CTE_RECOGNITIONS as
  (
    select CTE_APPLICATIONRECOGNITIONS.*, T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') As CONSTITUENTID,
    T.c.value('(AMOUNT)[1]','money') As AMOUNT,
    T.c.value('(EFFECTIVEDATE)[1]','datetime') As EFFECTIVEDATE,
    T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]','uniqueidentifier') As REVENUERECOGNITIONTYPECODEID,
    T.c.value('(REVENUERECOGNITIONID)[1]','uniqueidentifier') As REVENUERECOGNITIONID,
    T.c.value('(RECOGNITIONCREDITFKID)[1]','uniqueidentifier') As RECOGNITIONCREDITFKID,
    coalesce(T.c.value('(DONORCHALLENGERECOGNITIONTYPECODE)[1]','tinyint'), 0) As DONORCHALLENGERECOGNITIONTYPECODE,
    T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') As RECDESIGNATIONID            
    from CTE_APPLICATIONRECOGNITIONS
    cross apply 
    RECOGNITIONS.nodes('/RECOGNITIONS/ITEM') T(c)
  )
  insert into dbo.BATCHREVENUERECOGNITION
  (
    BATCHREVENUEID, AMOUNT, CONSTITUENTID,
    EFFECTIVEDATE, ID, REVENUERECOGNITIONTYPECODEID,
    ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, ADDEDBYID,
    CHANGEDBYID, DATEADDED, DATECHANGED,
    REVENUERECOGNITIONID, APPLICATIONID, ADDITIONALAPPLICATIONDECLINESGIFTAID,
    RECOGNITIONCREDITFKID, DONORCHALLENGERECOGNITIONTYPECODE, RECOGNITIONCREDITDESIGNATIONID,ADDITIONALAPPLICATIONSPONSORSHIPID
  )         
  select
  @BATCHREVENUEID,
  AMOUNT,
  CONSTITUENTID,
  EFFECTIVEDATE, 
  newid() ID, 
  REVENUERECOGNITIONTYPECODEID, 
  CTE_RECOGNITIONS.ADDITIONALAPPLICATIONTYPECODE,
  AAT.DESIGNATIONID,@CHANGEAGENTID,
  @CHANGEAGENTID,
  @CURRENTDATE, @CURRENTDATE,
  REVENUERECOGNITIONID,
  CTE_RECOGNITIONS.APPLICATIONID,
  coalesce(AAT.DECLINESGIFTAID, 0),
  RECOGNITIONCREDITFKID,
  DONORCHALLENGERECOGNITIONTYPECODE,
  RECDESIGNATIONID,
  AAT.SPONSORSHIPID
  from CTE_RECOGNITIONS inner join 
  @ADDITIONALAPPLICATIONTABLE AAT on 
  (
    AAT.DESIGNATIONID = CTE_RECOGNITIONS.DESIGNATIONID and
    AAT.DECLINESGIFTAID = CTE_RECOGNITIONS.DECLINESGIFTAID and
    AAT.TYPECODE = CTE_RECOGNITIONS.ADDITIONALAPPLICATIONTYPECODE and 
    coalesce(AAT.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = CTE_RECOGNITIONS.SPONSORSHIPID  and
    CTE_RECOGNITIONS.APPLICATIONID is NULL
  ) 

  union all

  select
  @BATCHREVENUEID,
  AMOUNT,
  CONSTITUENTID,
  EFFECTIVEDATE, 
  newid() ID, 
  REVENUERECOGNITIONTYPECODEID, 
  CTE_RECOGNITIONS.ADDITIONALAPPLICATIONTYPECODE,
  AAT.DESIGNATIONID,@CHANGEAGENTID,
  @CHANGEAGENTID,
  @CURRENTDATE, @CURRENTDATE,
  REVENUERECOGNITIONID,
  CTE_RECOGNITIONS.APPLICATIONID,
  coalesce(AAT.DECLINESGIFTAID, 0),
  RECOGNITIONCREDITFKID,
  DONORCHALLENGERECOGNITIONTYPECODE,
  RECDESIGNATIONID,
  AAT.SPONSORSHIPID
  from CTE_RECOGNITIONS inner join 
  @ADDITIONALAPPLICATIONTABLE AAT on
  AAT.REVENUESPLITID = CTE_RECOGNITIONS.APPLICATIONID
  where AAT.REVENUESPLITID is NOT NULL

  end

  if @APPLICATIONSOLICITORS is not null
  begin
    with CTE_APPLICATIONSOLICITORS as
    (
      select
      T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint') as ADDITIONALAPPLICATIONTYPECODE,
      T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as ADDITIONALAPPLICATIONDESIGNATIONID,
      case when T.c.exist('./SOLICITORS/ITEM') = 1 then convert(xml,T.c.query('SOLICITORS[1]')) else null end as SOLICITORS,
      T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]','bit') as DECLINESGIFTAID,
      coalesce(T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier'),'00000000-0000-0000-0000-000000000000') as SPONSORSHIPID
      from @APPLICATIONSOLICITORS.nodes('/APPLICATIONSOLICITORS/ITEM') T(c)
    ),
      CTE_SOLICITORS as
    (
      select CTE_APPLICATIONSOLICITORS.*, 
      T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') As CONSTITUENTID,
      T.c.value('(AMOUNT)[1]','money') As AMOUNT,
      T.c.value('(REVENUESOLICITORID)[1]','uniqueidentifier') As REVENUESOLICITORID
      from CTE_APPLICATIONSOLICITORS
      cross apply 
      SOLICITORS.nodes('/SOLICITORS/ITEM') T(c)
    )
    insert into dbo.BATCHREVENUESOLICITOR
    (
      BATCHREVENUEID, AMOUNT, CONSTITUENTID, ID, ADDITIONALAPPLICATIONTYPECODE, 
      ADDITIONALAPPLICATIONDESIGNATIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, REVENUESOLICITORID,
      ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID
    )
    select  @BATCHREVENUEID, 
    AMOUNT, 
    CONSTITUENTID, 
    newid() ID, 
    CTE_SOLICITORS.ADDITIONALAPPLICATIONTYPECODE,
    CTE_SOLICITORS.ADDITIONALAPPLICATIONDESIGNATIONID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
    REVENUESOLICITORID, 
    coalesce(AAT.DECLINESGIFTAID, 0), 
    AAT.SPONSORSHIPID
    from CTE_SOLICITORS
    inner join @ADDITIONALAPPLICATIONTABLE AAT on 
    AAT.DESIGNATIONID = CTE_SOLICITORS.ADDITIONALAPPLICATIONDESIGNATIONID and 
    AAT.DECLINESGIFTAID = CTE_SOLICITORS.DECLINESGIFTAID and 
    AAT.TYPECODE = CTE_SOLICITORS.ADDITIONALAPPLICATIONTYPECODE and
    coalesce(AAT.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = CTE_SOLICITORS.SPONSORSHIPID

  end

return 0;      
end