<GlobalChangeSpec 
	xmlns="bb_appfx_globalchange"
	xmlns:c="bb_appfx_commontypes"
	ID="64f18fa0-eb54-4426-868d-c9603b904977"
	Name="Populate Receipt Details Download Staging Global Change"
	Description="Populating Receipt Details Download to Staging table"
	Author="REDCROSS\ctong"
	DisplayName="Populate receipt details download to staging table"
	GlobalChangeFolder="CRC Custom"
	SPName="USR_USP_GLOBALCHANGE_POPULATERECEIPTDETAILSDOWNLOADSTAGING"
>
	<!-- describe the SP that performs the global change operation -->
	<CreateProcedureSQL>
		<![CDATA[
      create procedure dbo.USR_USP_GLOBALCHANGE_POPULATERECEIPTDETAILSDOWNLOADSTAGING
      (
	      @CHANGEAGENTID uniqueidentifier = null,
	      @ASOF as datetime = null,
	      @NUMBERADDED int = 0 output,
	      @NUMBEREDITED int = 0 output,
	      @NUMBERDELETED int = 0 output
      )
      as			
	      set nocount off;
	 
        declare @CURRENTDATE datetime, @DATECHANGED datetime; 
	      set @CURRENTDATE = getdate();
	      set @NUMBERADDED = 0;
	      set @NUMBEREDITED = 0;
	      set @NUMBERDELETED = 0; 

	      if @CHANGEAGENTID is null
		      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
					
	      begin try
		      -- do the global change operation here, and set @NUMBERADDED, @NUMBEREDITED, and/or @NUMBERDELETED as appropriate
        BEGIN TRANSACTION;
        
         --***==1）START: INSERT new records ==***--->>
         Insert Into dbo.USR_RECEIPTDETAILSDOWNLOADSTAGING
         (   [RECEIPTINGPROCESSSTATUSID]
            ,[PUBLICFUNDNAMEENGLISH]
            ,[PUBLICFUNDNAMEFRENCH]
            ,[LANGUAGE]
            ,[PRIMARYADDRESSEE]
            ,[PRIMARYSALUTATION]
            ,[TAX]
            ,[ADDRESSLINE1]
            ,[ADDRESSLINE2]
            ,[ADDRESSLINE3]
            ,[ADDRESSLINE4]
            ,[CITY]
            ,[PROVINCE_STATE]
            ,[POSTCODE]
            ,[COUNTRY]
            ,[CONSTITUENTNAME]
            ,[CONSTITUENTLOOKUPID]
            ,[EREC_RECUELECTRONIQUE]
            ,[TRANSACTIONAMOUNT]
            ,[RECEIPTAMOUNT]
            ,[TEXTNOTE]
            ,[RECEIPTSTACKSHORTNAME]
            ,[PREVUNIQUERECEIPTNUMBER]
            ,[UNIQUERECEIPTNUMBER]
            ,[GIFTAMOUNT]
            ,[SPLITAMOUNT]
            ,[ADDRESSTYPE]
            ,[PREVIOUSRECEIPTNUMBER]
            ,[REVENUELETTER]
            ,[PAYMENTDATE]
            ,[RECEIPTPROCESSDATE]
            ,[RECEIPTDATE]
            ,[REVENUEATTRIBUTE_RECEIPTSPECIALHANDLINGVALUE]
            ,[REVENUEATTRIBUTE_RECEIPTSPECIALHANDLINGCOMMENT]
            ,[REVENUEREFERENCE]
            ,[BENEFITAMOUNT]
            ,[ISORGANIZATION]
            ,[LAST_ORG_GROUPNAME]
            ,[ORGANIZATIONPRIMARYCONTACTNAME]
            ,[ORGANIZATIONSTEWARDSHIPCONTACT]
            ,[APPEAL]
            ,[ADDEDBYID]
            ,[CHANGEDBYID]
            ,[DATEADDED]
            ,[DATECHANGED]
         ) 
         Select Distinct 
             ps.id 
	          ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_PUBLICNAMEENGLISH(ps.id, rr.UNIQUERECEIPTNUMBER))) as 'Public Fund Name English'
	          ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_PUBLICNAMEFRENCH(ps.id, rr.UNIQUERECEIPTNUMBER))) as 'Public Fund Name French'
	          ,Case 
		          When dbo.USR_UFN_COMMASEPARATEDLIST_LANGUAGENLANGUE(ps.id, rr.UNIQUERECEIPTNUMBER) is not null 
			          Then rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_LANGUAGENLANGUE(ps.id, rr.UNIQUERECEIPTNUMBER)))
		          Else 'English / Anglais'
	          End as 'Language'
	          ,rtrim(ltrim(isnull([dbo].[USR_UFN_NAMEFORMAT](c.ID, 'Primary Addressee'), c.NAME))) as 'Primary Addressee'  --for crc-1933
	          ,rtrim(ltrim([dbo].[USR_UFN_NAMEFORMAT](c.ID, 'Primary Salutation'))) as 'Primary Salutation'  --for crc-1933
	          ,rtrim(ltrim([dbo].[USR_UFN_NAMEFORMAT](c.ID, 'Tax'))) as 'Tax'    --for crc-1933
	          ,mark.ADDRESSLINE1 as 'Address Line1'
	          ,mark.ADDRESSLINE2 as 'Address Line2'
	          ,mark.ADDRESSLINE3 as 'Address Line3'
	          ,mark.ADDRESSLINE4 as 'Address Line4'
	          ,mark.CITY as 'City'
	          ,mark.STATEID_ABBREVIATION as 'ProvinceState'
	          ,mark.POSTCODE as 'Post Code'
	          ,mark.COUNTRYID_TRANSLATION as 'Country'
	          ,c.NAME 'Constituent Name'
	          ,c.LOOKUPID 'Constituent Lookup ID'
	          ,ISNULL(rtrim(ltrim(ea.EMAILADDRESS)),'') as 'eRec' --display col name is 'eRec / Reçu électronique'
	          --**,[Transaction Amount] = FORMAT(rd.PAYMENTAMOUNT,'C') --calculateing at pullout time **ex: $60, 390, 250, 50 --format: $10,000.00
	          ,[Transaction Amount]=0.00   --calculateing at pullout time from #CRC_RECEIPTING_DETAILS tbl.
            --**,[Receipt Amount] = FORMAT(rd.RECEIPTAMOUNT,'C')-- calculateing at pullout time ***ex 60, 290, 250, 50 
		        ,[Receipt Amount]= 0.00     --calculateing at pullout time
            ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_TEXTNOTE](ps.id,rr.UNIQUERECEIPTNUMBER))) as 'Text Note'
	          ,rr.RECEIPTSTACKSHORTNAME as 'Receipt Stack Short Name'
	          --**,rd.PREVIOUSRECEIPTNUMBER as 'Prev Unique Receipt Number' 
            ,null as 'Prev Unique Receipt Number' --**calculateing at pullout time
		        ,rtrim(ltrim(rr.UNIQUERECEIPTNUMBER)) as 'Unique Receipt Number'
	          ,[Gift Amount] = FORMAT(Sum(li.TRANSACTIONAMOUNT)  over (Partition By rr.UNIQUERECEIPTNUMBER), 'C')
	          ,[Split Amount] =FORMAT(Sum(li.TRANSACTIONAMOUNT) over (Partition By rr.UNIQUERECEIPTNUMBER) , 'C')
	          ,addrc.DESCRIPTION 'Address_Type' --for refernce
            ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_RECENTPREVIOUSRECEIPTNUMBERRE(ps.id, rr.REVENUEID))) as 'Previous Receipt Number'
	          ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_REVENUELETTER(ps.id, rr.UNIQUERECEIPTNUMBER))) as 'Revenue Letter'
	            ----------New fields below as crc-1933 requested below------------
	          ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_PAYMENTDATE](ps.id,rr.UNIQUERECEIPTNUMBER))) as 'Payment Date'
	          ,CONVERT(nvarchar(10), rr.ReceiptProcessDate, 23) as 'Receipt Process Date'
	          ,Case left(dbo.UFN_REVENUE_GETRECEIPTSTATUS(rr.REVENUEID),9) when 'Receipted' then convert(nvarchar(10),rr.RECEIPTDATE,23) else null 
	          End as 'Receipt Date'
	          ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_RECEIPTSPECIALHANDLINGVALUE](ps.id, rr.UNIQUERECEIPTNUMBER))) as 'Revenue Attribute:Receipt Special Handling Value' 
	          ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_RECEIPTSPECIALHANDLINGCOMMENT](ps.id, rr.UNIQUERECEIPTNUMBER))) as 'Revenue Attribute:Receipt Special Handling Comment'
	          --**,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_REFERENACE] (ps.id, rd.UNIQUERECEIPTNUMBER))) as 'Revenue Reference'
            ,null as 'Revenue Reference' --**calculateing at pullout time
	          --**,FORMAT((rd.PAYMENTAMOUNT - rd.RECEIPTAMOUNT),'C') as 'Benefit Amount' 
	          ,[Benefit Amount] =0.00      --**calculateing at pullout time
            ,Case c.ISORGANIZATION | c.ISGROUP     --IsOrganization
		          when 1 then 'YES'
		          else 'NO'
	          End as 'Is Organization'
	          ,Case c.ISORGANIZATION | c.ISGROUP  --LAST/ORG/GROUP NAME (Calculated as IF IsOrganization = 'YES' then Last/Org/Group ELSE BLANK)
		          when 1 then c.Name
		          else ''
	          End as 'Last/ORG/Group Name'
	          ,rtrim(ltrim([dbo].[USR_UFN_ORGNIZATION_CONTACT](c.ID, 'Primary Contact'))) as 'Organization Primary Contact Name' --Organization Primary Contact Name (ex: ContsName='GreenGraden' Dan Korth)
		        ,rtrim(ltrim([dbo].[USR_UFN_ORGNIZATION_CONTACT](c.ID, 'Stewardship Contact')))  as 'Organization Stewardship Contact' --Organization Stewardship Contact Name (ex:ContsName='GreenGraden' Amanda Lozier)
            ,[dbo].[USR_UFN_COMMASEPARATEDLIST_REVENUEAPPEAL_CONSOLIDATED](c.ID) as 'Appeal'
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
            ,@CURRENTDATE
          From
	          [dbo].[RECEIPTINGPROCESS] rp
	          join [dbo].[RECEIPTINGPROCESSSTATUS] ps
		          on ps.PARAMETERSETID = rp.ID
	          join dbo.REVENUERECEIPT rr
		          on rr.RECEIPTINGPROCESSSTATUSID = ps.ID
	          join dbo.FINANCIALTRANSACTION ft
		          on ft.id = rr.REVENUEID
	          join dbo.REVENUE_EXT revExt
		          on revExt.id = ft.ID
	          left join 
		        ( dbo.REVENUENOTE note
		        inner join
		        dbo.REVENUENOTETYPECODE noteType
			        on noteType.ID = note.REVENUENOTETYPECODEID and noteType.DESCRIPTION = 'Items on Receipt'
	            ) 
		        on note.REVENUEID = ft.ID
	          left join 
		          (dbo.REVENUELETTER letter
			          join dbo.LETTERCODE ltrc
				          on ltrc.id = letter.LETTERCODEID
		          )
		          on letter.REVENUEID = ft.ID --i beleve this is many:one, need to verify
	          Inner join dbo.FINANCIALTRANSACTIONLINEITEM li
		          on li.FINANCIALTRANSACTIONID = ft.ID
	          join dbo.REVENUESPLIT_EXT ext
		          on ext.id = li.ID
	          join DESIGNATION d
		          on d.id = ext.DESIGNATIONID
	          join constituent c
		          on c.id = ft.CONSTITUENTID
	          Inner join [dbo].[ADDRESS] addr
		          on addr.CONSTITUENTID = c.ID
	          Inner join [dbo].[ADDRESSTYPECODE] addrc
		          on addrc.ID = addr.ADDRESSTYPECODEID
	          left join NAMEFORMAT nf
		          on nf.CONSTITUENTID = c.ID
		          and nf.PRIMARYADDRESSEE = 1
	          join dbo.V_QUERY_CONSTITUENTMARKETING mark
		          on mark.CONSTITUENTID = c.ID	
	          left join 
            (
				      SELECT E.CONSTITUENTID, E.EMAILADDRESS, E.DATECHANGED FROM EMAILADDRESS E
              LEFT JOIN dbo.USR_EMAILADDRESS_EXT U on E.ID = U.ID
              LEFT JOIN dbo.USR_EMAILPERMISSIONCODE P on U.EMAILPERMISSIONCODEID = P.ID
              WHERE dbo.UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION(E.EMAILADDRESSTYPECODEID) =  'eRec / Reçu électronique'
              AND E.ENDDATE is NULL
              AND ISNULL(P.DESCRIPTION,'') <> 'Inactive / Inactif'
				    ) ea on ea.CONSTITUENTID = c.ID
		        left join REVENUE rvn on rvn.ID = rr.REVENUEID
		        left join dbo.REVENUEREFERENCE ref on ref.ID = rvn.ID  
		        --left join #CRC_RECEIPTING_DETAILS rd on rd.UNIQUERECEIPTNUMBER = rr.UNIQUERECEIPTNUMBER --this needs to do on click Download button process
            Where
	            addr.ISPRIMARY = 1
              and (@ASOF IS NULL or rr.DATEADDED >= @ASOF);
 
          set @NUMBERADDED = @@ROWCOUNT;
          COMMIT TRANSACTION;
         ---***==1) END of insert new records ==***----<<
         
         --***==2）START: UPDATE existed records ==***--->>
         BEGIN TRANSACTION;
          UPDATE stgu
          SET 
	            stgu.[PUBLICFUNDNAMEENGLISH]		= x.[PublicFundNameEnglish]
              ,stgu.[PUBLICFUNDNAMEFRENCH]		= x.[PublicFundNameFrench]
              ,stgu.[LANGUAGE]					      = x.[Language]
              ,stgu.[PRIMARYADDRESSEE]			  = x.[PrimaryAddressee]
              ,stgu.[PRIMARYSALUTATION]			  = x.[PrimarySalutation]
              ,stgu.[TAX]							        = x.[Tax]
              ,stgu.[ADDRESSLINE1]				    = x.[AddressLine1]
              ,stgu.[ADDRESSLINE2]				    = x.[AddressLine2]
              ,stgu.[ADDRESSLINE3]				    = x.[AddressLine3]
              ,stgu.[ADDRESSLINE4]				    = x.[AddressLine4]
              ,stgu.[CITY]						        = x.[City]
              ,stgu.[PROVINCE_STATE]				  = x.[ProvinceState]
              ,stgu.[POSTCODE]					      = x.[PostCode]
              ,stgu.[COUNTRY]						      = x.[Country]
              ,stgu.[CONSTITUENTNAME]				  = x.[ConstituentName]
              --,stgu.[CONSTITUENTLOOKUPID]		--can't be changed
              ,stgu.[EREC_RECUELECTRONIQUE]		= x.[eRec]
              --,stgu.[TRANSACTIONAMOUNT]			--calculateing at pullout time
              --,stgu.[RECEIPTAMOUNT]				  --calculateing at pullout time
              ,stgu.[TEXTNOTE]					      = x.[TextNote]
              ,stgu.[RECEIPTSTACKSHORTNAME]		= x.[ReceiptStackShortName]
              --,stgu.[PREVUNIQUERECEIPTNUMBER]	--calculateing at pullout time	
              ,stgu.[UNIQUERECEIPTNUMBER]			= x.[UniqueReceiptNumber]
              ,stgu.[GIFTAMOUNT]					    = x.[GiftAmount]
              ,stgu.[SPLITAMOUNT]					    = x.[SplitAmount]
              ,stgu.[ADDRESSTYPE]					    = x.[AddressType]
              ,stgu.[PREVIOUSRECEIPTNUMBER]		= x.[PreviousReceiptNumber]
              ,stgu.[REVENUELETTER]				    = x.[RevenueLetter]
              ,stgu.[PAYMENTDATE]					    = x.[PaymentDate]
              ,stgu.[RECEIPTPROCESSDATE]			= x.[ReceiptProcessDate]
              ,stgu.[RECEIPTDATE]					    = x.[ReceiptDate]
              ,stgu.[REVENUEATTRIBUTE_RECEIPTSPECIALHANDLINGVALUE]		= x.[RevenueAttributeReceiptSpecialHandlingValue]
              ,stgu.[REVENUEATTRIBUTE_RECEIPTSPECIALHANDLINGCOMMENT]	= x.[RevenueAttributeReceiptSpecialHandlingComment]
              --,stgu.[REVENUEREFERENCE]			--calculateing at pullout time
              --,stgu.[BENEFITAMOUNT]				  --calculateing at pullout time
              ,stgu.[ISORGANIZATION]				  = x.[IsOrganization]
              ,stgu.[LAST_ORG_GROUPNAME]			= x.[LastORGGroupName]
              ,stgu.[ORGANIZATIONPRIMARYCONTACTNAME]	= x.[OrganizationPrimaryContactName]
              ,stgu.[ORGANIZATIONSTEWARDSHIPCONTACT]	= x.[OrganizationStewardshipContact]
              ,stgu.[APPEAL]						      = x.[Appeal]				
              ,[CHANGEDBYID]						      = @CHANGEAGENTID			
              ,[DATECHANGED]						      = @CURRENTDATE
          FROM dbo.USR_RECEIPTDETAILSDOWNLOADSTAGING stgu
          INNER JOIN 
          (
	          Select Distinct 
               ps.id 
		          ,addr.ISPRIMARY
	            ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_PUBLICNAMEENGLISH(ps.id, rr.UNIQUERECEIPTNUMBER))) as 'PublicFundNameEnglish'
		          ,dbo.USR_UFN_COMMASEPARATEDLIST_PUBLICNAMEENGLISH_DATECHANGED(ps.id, rr.UNIQUERECEIPTNUMBER) as 'engDATECHANGED'
	            ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_PUBLICNAMEFRENCH(ps.id, rr.UNIQUERECEIPTNUMBER))) as 'PublicFundNameFrench'
		          ,dbo.USR_UFN_COMMASEPARATEDLIST_PUBLICNAMEFRENCH_DATECHANGED(ps.id, rr.UNIQUERECEIPTNUMBER) as 'freDATECHANGED'
	            ,Case 
		            When dbo.USR_UFN_COMMASEPARATEDLIST_LANGUAGENLANGUE(ps.id, rr.UNIQUERECEIPTNUMBER) is not null 
			            Then rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_LANGUAGENLANGUE(ps.id, rr.UNIQUERECEIPTNUMBER)))
		            Else 'English / Anglais'
	             End as 'Language'
		          ,dbo.USR_UFN_COMMASEPARATEDLIST_LANGUAGENLANGUE_DATECHANGED(ps.id, rr.UNIQUERECEIPTNUMBER) as 'lngDATECHANGED'
	            ,rtrim(ltrim(isnull([dbo].[USR_UFN_NAMEFORMAT](c.ID, 'Primary Addressee'), c.NAME))) as 'PrimaryAddressee'  --for crc-1933
	            ,rtrim(ltrim([dbo].[USR_UFN_NAMEFORMAT](c.ID, 'Primary Salutation'))) as 'PrimarySalutation'  --for crc-1933
	            ,rtrim(ltrim([dbo].[USR_UFN_NAMEFORMAT](c.ID, 'Tax'))) as 'Tax'    --for crc-1933
		          ,[dbo].[USR_UFN_NAMEFORMAT_DATECHANGED](c.ID) as 'nfDATECHANGED'
	            ,mark.ADDRESSLINE1 as 'AddressLine1'
	            ,mark.ADDRESSLINE2 as 'AddressLine2'
	            ,mark.ADDRESSLINE3 as 'AddressLine3'
	            ,mark.ADDRESSLINE4 as 'AddressLine4'
	            ,mark.CITY as 'City'
	            ,mark.STATEID_ABBREVIATION as 'ProvinceState'
	            ,mark.POSTCODE as 'PostCode'
	            ,mark.COUNTRYID_TRANSLATION as 'Country'
		          ,mark.DATECHANGED as 'markDATECHANGED'
	            ,c.NAME 'ConstituentName'
		          ,c.DATECHANGED as 'cDATECHANGED'
	            --,c.LOOKUPID 'Constituent Lookup ID' --can't be changed
	            ,ISNULL(rtrim(ltrim(ea.EMAILADDRESS)),'') as 'eRec' --display col name is 'eRec / Reçu électronique'
		          ,ea.DATECHANGED as 'eaDATECHANGED'
	            --,[Transaction Amount]=0.00   --**calculateing at pullout time from #CRC_RECEIPTING_DETAILS tbl.
		          --,[Receipt Amount]= 0.00      --**calculateing at pullout time
		          ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_TEXTNOTE](ps.id,rr.UNIQUERECEIPTNUMBER))) as 'TextNote'
		          ,[dbo].[USR_UFN_COMMASEPARATEDLIST_TEXTNOTE_DATECHANGED](ps.id,rr.UNIQUERECEIPTNUMBER) as 'tntDATECHANGED'
	            ,rr.RECEIPTSTACKSHORTNAME as 'ReceiptStackShortName'
		          ,rr.DATECHANGED as 'rrDATECHANGED'
		          --,null as 'Prev Unique Receipt Number' --**calculateing at pullout time
		          ,rtrim(ltrim(rr.UNIQUERECEIPTNUMBER)) as 'UniqueReceiptNumber'
	            ,[GiftAmount] = FORMAT(Sum(li.TRANSACTIONAMOUNT)  over (Partition By rr.UNIQUERECEIPTNUMBER), 'C')
	            ,[SplitAmount] =FORMAT(Sum(li.TRANSACTIONAMOUNT) over (Partition By rr.UNIQUERECEIPTNUMBER) , 'C')
	            ,addrc.DESCRIPTION 'AddressType' --for refernce
		          ,addr.DATECHANGED as 'addrDATECHANGED'
		          ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_RECENTPREVIOUSRECEIPTNUMBERRE(ps.id, rr.REVENUEID))) as 'PreviousReceiptNumber'
		          ,rtrim(ltrim(dbo.USR_UFN_COMMASEPARATEDLIST_REVENUELETTER(ps.id, rr.UNIQUERECEIPTNUMBER))) as 'RevenueLetter'
		          ,dbo.USR_UFN_COMMASEPARATEDLIST_REVENUELETTER_DATECHANGED(ps.id, rr.UNIQUERECEIPTNUMBER) as 'revlterDATECHANGED'
	            ----------New fields below as crc-1933 requested below------------
	            ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_PAYMENTDATE](ps.id,rr.UNIQUERECEIPTNUMBER))) as 'PaymentDate'
	            ,CONVERT(nvarchar(10), rr.ReceiptProcessDate, 23) as 'ReceiptProcessDate'
	            ,Case left(dbo.UFN_REVENUE_GETRECEIPTSTATUS(rr.REVENUEID),9) when 'Receipted' then convert(nvarchar(10),rr.RECEIPTDATE,23) else null 
	             End as 'ReceiptDate'
		          ,li.DATECHANGED as 'liDATECHANGED'
              ,ft.DATECHANGED as 'ftDATECHANGED'
	            ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_RECEIPTSPECIALHANDLINGVALUE](ps.id, rr.UNIQUERECEIPTNUMBER))) as 'RevenueAttributeReceiptSpecialHandlingValue' 
		          ,rtrim(ltrim([dbo].[USR_UFN_COMMASEPARATEDLIST_RECEIPTSPECIALHANDLINGCOMMENT](ps.id, rr.UNIQUERECEIPTNUMBER))) as 'RevenueAttributeReceiptSpecialHandlingComment'
		          ,[dbo].[USR_UFN_COMMASEPARATEDLIST_RECEIPTSPECIALHANDLING_DATECHANGED](ps.id, rr.UNIQUERECEIPTNUMBER) as 'rshDATECHANGED'
              --,null as 'Revenue Reference' --**calculateing at pullout time
	            --,[Benefit Amount] = 0.00      --**calculateing at pullout time
              ,Case c.ISORGANIZATION | c.ISGROUP     --IsOrganization
		            when 1 then 'YES'
		            else 'NO'
	             End as 'IsOrganization'
	             ,Case c.ISORGANIZATION | c.ISGROUP  --LAST/ORG/GROUP NAME (Calculated as IF IsOrganization = 'YES' then Last/Org/Group ELSE BLANK)
		              when 1 then c.Name
		              else ''
	              End as 'LastORGGroupName'
	             ,rtrim(ltrim([dbo].[USR_UFN_ORGNIZATION_CONTACT](c.ID, 'Primary Contact'))) as 'OrganizationPrimaryContactName' --Organization Primary Contact Name (ex: ContsName='GreenGraden' Dan Korth)
		           ,rtrim(ltrim([dbo].[USR_UFN_ORGNIZATION_CONTACT](c.ID, 'Stewardship Contact')))  as 'OrganizationStewardshipContact' --Organization Stewardship Contact Name (ex:ContsName='GreenGraden' Amanda Lozier)
               ,[dbo].[USR_UFN_COMMASEPARATEDLIST_REVENUEAPPEAL_CONSOLIDATED](c.ID) as 'Appeal'
		           ,[dbo].[USR_UFN_COMMASEPARATEDLIST_REVENUEAPPEAL_CONSOLIDATED_DATECHANGED](c.ID) as 'applDATECHANGED'
              From
	              [dbo].[RECEIPTINGPROCESS] rp
	              join [dbo].[RECEIPTINGPROCESSSTATUS] ps
		              on ps.PARAMETERSETID = rp.ID
	              join dbo.REVENUERECEIPT rr
		              on rr.RECEIPTINGPROCESSSTATUSID = ps.ID
	              join dbo.FINANCIALTRANSACTION ft
		              on ft.id = rr.REVENUEID
	              join dbo.REVENUE_EXT revExt
		              on revExt.id = ft.ID
	              left join 
		          ( dbo.REVENUENOTE note
		          inner join
		          dbo.REVENUENOTETYPECODE noteType
			          on noteType.ID = note.REVENUENOTETYPECODEID and noteType.DESCRIPTION = 'Items on Receipt'
	              ) 
		          on note.REVENUEID = ft.ID
	              left join 
		              (dbo.REVENUELETTER letter
			              join dbo.LETTERCODE ltrc
				              on ltrc.id = letter.LETTERCODEID
		              )
		              on letter.REVENUEID = ft.ID --i beleve this is many:one, need to verify
	              Inner join dbo.FINANCIALTRANSACTIONLINEITEM li
		              on li.FINANCIALTRANSACTIONID = ft.ID
	              join dbo.REVENUESPLIT_EXT ext
		              on ext.id = li.ID
	              join DESIGNATION d
		              on d.id = ext.DESIGNATIONID
	              join constituent c
		              on c.id = ft.CONSTITUENTID
	              Inner join [dbo].[ADDRESS] addr
		              on addr.CONSTITUENTID = c.ID
	              Inner join [dbo].[ADDRESSTYPECODE] addrc
		              on addrc.ID = addr.ADDRESSTYPECODEID
	              left join NAMEFORMAT nf
		              on nf.CONSTITUENTID = c.ID
		              and nf.PRIMARYADDRESSEE = 1
	              join dbo.V_QUERY_CONSTITUENTMARKETING mark
		              on mark.CONSTITUENTID = c.ID	
	              left join 
                (
				          SELECT distinct E.CONSTITUENTID, E.EMAILADDRESS, E.DATECHANGED FROM EMAILADDRESS E
                  LEFT JOIN dbo.USR_EMAILADDRESS_EXT U on E.ID = U.ID
                  LEFT JOIN dbo.USR_EMAILPERMISSIONCODE P on U.EMAILPERMISSIONCODEID = P.ID
                  WHERE dbo.UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION(E.EMAILADDRESSTYPECODEID) =  'eRec / Reçu électronique'
                  AND E.ENDDATE is NULL
                  AND ISNULL(P.DESCRIPTION,'') <> 'Invalid / Invalide'
			          ) ea on ea.CONSTITUENTID = c.ID
		          left join REVENUE rvn on rvn.ID = rr.REVENUEID
		          left join dbo.REVENUEREFERENCE ref on ref.ID = rvn.ID  
          ) x
          ON stgu.RECEIPTINGPROCESSSTATUSID = x.id 
             and x.ISPRIMARY = 1 
             and stgu.UNIQUERECEIPTNUMBER = x.UniqueReceiptNumber
             and
             (
		            x.engDATECHANGED > @ASOF
	              or x.freDATECHANGED > @ASOF
	              or x.lngDATECHANGED > @ASOF
	              or x.nfDATECHANGED > @ASOF
	              or x.markDATECHANGED > @ASOF
	              or x.cDATECHANGED > @ASOF
	              or x.eaDATECHANGED > @ASOF
	              or x.tntDATECHANGED > @ASOF
	              or x.rrDATECHANGED > @ASOF
	              or x.addrDATECHANGED > @ASOF
	              or x.revlterDATECHANGED > @ASOF
	              or x.liDATECHANGED > @ASOF
                or x.ftDATECHANGED > @ASOF
	              or x.rshDATECHANGED > @ASOF
	              or x.applDATECHANGED > @ASOF
             )
             
          set @NUMBEREDITED = @@ROWCOUNT;
          COMMIT TRANSACTION;
        
         --***==2)END of update existed records ==***---<<
    
	      end try
	      begin catch
		      exec dbo.USP_RAISE_ERROR;
		      return 1;
	      end catch
		]]>
	</CreateProcedureSQL>
  <!--
	<ParametersFormMetaData>

		describe fields on the parameter form, which correspond to parameters on the SP.  Note that system parameters 
		like the context @CHANGEAGENTID, @ASOF, @NUMBERADDED, @NUMBEREDITED, and @NUMBERDELETED need not be listed.
		<FormMetaData xmlns="bb_appfx_commontypes">
			<FormFields>
				<FormField FieldID="FIELD1" Caption="Field 1" DataType="Integer" />
				<FormField FieldID="FIELD2" Caption="Field 2" DataType="Integer" />
			</FormFields>
		</FormMetaData>
	</ParametersFormMetaData>
	 -->
</GlobalChangeSpec>