recurring.sql

recurring.sql

-- NOTE Bank is only tied to the original gift, not the adjustment.
-- NOTE Bank account number and routing number comes from re_reporter..re_eft
-- which is a file that betsy had someone create. we will need it in prod.
-- NOTE We have found Annually, Semi-anually, Quarterly, Monthly and
-- Bi-monthly schedules in the staging database.
-- We can't handle Bi-monthly (twice a month).
-- So we create two different monthly records.
-- NOTE We can't handle anything if it's other than installment_frequency
-- 1 through 6. If there are it will display ERROR in the
-- ChargentOrders__Payment_Frequency__c and error on insert into SF.
-- NOTE Next transaction date is on the original gift!
-- NOTE bank account name and account holder name were always null.
-- NOTE address info should come in the trigger.

-- get all the non-adjusted recurring gifts that aren't bi-monthly
select
  g.id RE_LEGACY_ID__c,
  'Nada Bank' ChargentOrders__Bank_Name__c,
  ree.routingno ChargentOrders__Bank_Routing_Number__c,
  ree.accountnumber ChargentOrders__Bank_Account_Number__c,
  coalesce(sfc.AccountId, sfa.Id) ChargentOrders__Account__c,
  g.constituentbankid RE_BANK_ID__c,
  case
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 0
      and sfa.Id is null then 'Checking'
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 1
      and sfa.Id is null then 'Savings'
    when sfa.Id is not null then 'Business Checking'
  end ChargentOrders__Bank_Account_Type__c,
  'a1p36000001C8uVAAS' ChargentOrders__Gateway__c,
  'Check' ChargentOrders__Payment_Method__c,
  'Recurring' ChargentOrders__Payment_Status__c,
  case g.installment_frequency
    when 1 then 'Annual'
    when 2 then 'Semiannual'
    when 3 then 'Quarterly'
    when 4 then 'Bimonthly'
    when 5 then 'Monthly'
    else 'ERROR'
  end ChargentOrders__Payment_Frequency__c,
  'Unending' ChargentOrders__Payment_Stop__c,
  substring(
    convert(
      varchar,
      dbo.query_nexttransactiondate(g.id, g.gift_status), 120),
    1, 10)
    ChargentOrders__Payment_Start_Date__c,
  g.schedule_dayofmonth ChargentOrders__Charge_Date__c
from gift g
  join records r on r.id = g.constit_id
  join re_reporter..re_eft ree
    on ree.constituentid = r.constituent_id
  left outer join re_reporter..sf_contact sfc
    on sfc.re_legacy_id__c = g.constit_id
  left outer join re_reporter..sf_account sfa
    on sfa.re_legacy_id__c = g.constit_id
where g.type = 30
  and g.id not in (select giftid from giftamendment)
  and payment_type = 6
  and gift_status = 1
  and g.installment_frequency != 6

union all
-- get the first of any bi-monthly non-adjusted
select
  g.id RE_LEGACY_ID__c,
  'Nada Bank' ChargentOrders__Bank_Name__c,
  ree.routingno ChargentOrders__Bank_Routing_Number__c,
  ree.accountnumber ChargentOrders__Bank_Account_Number__c,
  coalesce(sfc.AccountId, sfa.Id) ChargentOrders__Account__c,
  g.constituentbankid RE_BANK_ID__c,
  case
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 0
      and sfa.Id is null then 'Checking'
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 1
      and sfa.Id is null then 'Savings'
    when sfa.Id is not null then 'Business Checking'
  end ChargentOrders__Bank_Account_Type__c,
  'a1p36000001C8uVAAS' ChargentOrders__Gateway__c,
  'Check' ChargentOrders__Payment_Method__c,
  'Recurring' ChargentOrders__Payment_Status__c,
  'Monthly' ChargentOrders__Payment_Frequency__c,
  'Unending' ChargentOrders__Payment_Stop__c,
  substring(
    convert(
      varchar,
      dbo.query_nexttransactiondate(g.id, g.gift_status), 120),1,8) +
    cast(g.schedule_dayofmonth as varchar)
    ChargentOrders__Payment_Start_Date__c,
  g.schedule_dayofmonth ChargentOrders__Charge_Date__c
from gift g
  join records r on r.id = g.constit_id
  join re_reporter..re_eft ree
    on ree.constituentid = r.constituent_id
  left outer join re_reporter..sf_contact sfc
    on sfc.re_legacy_id__c = g.constit_id
  left outer join re_reporter..sf_account sfa
    on sfa.re_legacy_id__c = g.constit_id
where g.type = 30
  and g.id not in (select giftid from giftamendment)
  and payment_type = 6
  and gift_status = 1
  and g.installment_frequency = 6

union all
-- get the second of any bi-monthly non-adjusted
select
  g.id RE_LEGACY_ID__c,
  'Nada Bank' ChargentOrders__Bank_Name__c,
  ree.routingno ChargentOrders__Bank_Routing_Number__c,
  ree.accountnumber ChargentOrders__Bank_Account_Number__c,
  coalesce(sfc.AccountId, sfa.Id) ChargentOrders__Account__c,
  g.constituentbankid RE_BANK_ID__c,
  case
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 0
      and sfa.Id is null then 'Checking'
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 1
      and sfa.Id is null then 'Savings'
    when sfa.Id is not null then 'Business Checking'
  end ChargentOrders__Bank_Account_Type__c,
  'a1p36000001C8uVAAS' ChargentOrders__Gateway__c,
  'Check' ChargentOrders__Payment_Method__c,
  'Recurring' ChargentOrders__Payment_Status__c,
  'Monthly' ChargentOrders__Payment_Frequency__c,
  'Unending' ChargentOrders__Payment_Stop__c,
  substring(
    convert(
      varchar,
      dbo.query_nexttransactiondate(g.id, g.gift_status), 120),1,8) +
    cast(g.schedule_dayofmonth2 as varchar)
    ChargentOrders__Payment_Start_Date__c,
  g.schedule_dayofmonth2 ChargentOrders__Charge_Date__c
from gift g
  join records r on r.id = g.constit_id
  join re_reporter..re_eft ree
    on ree.constituentid = r.constituent_id
  left outer join re_reporter..sf_contact sfc
    on sfc.re_legacy_id__c = g.constit_id
  left outer join re_reporter..sf_account sfa
    on sfa.re_legacy_id__c = g.constit_id
where g.type = 30
  and g.id not in (select giftid from giftamendment)
  and payment_type = 6
  and gift_status = 1
  and g.installment_frequency = 6

union all
-- get all the adjusted recurring gifts that aren't bi-monthly
select
  ag.id RE_LEGACY_ID__c,
  'Nada Bank' ChargentOrders__Bank_Name__c,
  ree.routingno ChargentOrders__Bank_Routing_Number__c,
  ree.accountnumber ChargentOrders__Bank_Account_Number__c,
  coalesce(sfc.AccountId, sfa.Id) ChargentOrders__Account__c,
  g.constituentbankid RE_BANK_ID__c,
  case
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 0
      and sfa.Id is null then 'Checking'
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 1
      and sfa.Id is null then 'Savings'
    when sfa.Id is not null then 'Business Checking'
  end ChargentOrders__Bank_Account_Type__c,
  'a1p36000001C8uVAAS' ChargentOrders__Gateway__c,
  'Check' ChargentOrders__Payment_Method__c,
  'Recurring' ChargentOrders__Payment_Status__c,
  case g.installment_frequency
    when 1 then 'Annual'
    when 2 then 'Semiannual'
    when 3 then 'Quarterly'
    when 4 then 'Bimonthly'
    when 5 then 'Monthly'
    else 'ERROR'
  end ChargentOrders__Payment_Frequency__c,
  'Unending' ChargentOrders__Payment_Stop__c,
  substring(
    convert(
      varchar,
      dbo.query_nexttransactiondate(g.id, g.gift_status), 120),1,8) +
    cast(ag.schedule_dayofmonth as varchar)
    ChargentOrders__Payment_Start_Date__c,
  ag.schedule_dayofmonth ChargentOrders__Charge_Date__c
from gift ag
  join gift g on g.id in (select giftid from giftamendment where amendmentid = ag.id)
  join records r on r.id = g.constit_id
  join re_reporter..re_eft ree
    on ree.constituentid = r.constituent_id
  left outer join re_reporter..sf_contact sfc
    on sfc.re_legacy_id__c = g.constit_id
  left outer join re_reporter..sf_account sfa
    on sfa.re_legacy_id__c = g.constit_id
  where ag.id in (
    select lastamend.amendmentid
    from (
      select
        giftid,
        sequence,
        amendmentid,
        row_number()
        over(partition by giftid order by sequence desc) uno
      from giftamendment
      join gift on gift.id = giftamendment.giftid
      where gift.payment_type = 6
      and gift.gift_status = 1
      and gift.installment_frequency != 6
    ) lastamend
    where lastamend.uno = 1)

union all
-- get the first of any bi-monthly adjusted
select
  ag.id RE_LEGACY_ID__c,
  'Nada Bank' ChargentOrders__Bank_Name__c,
  ree.routingno ChargentOrders__Bank_Routing_Number__c,
  ree.accountnumber ChargentOrders__Bank_Account_Number__c,
  coalesce(sfc.AccountId, sfa.Id) ChargentOrders__Account__c,
  g.constituentbankid RE_BANK_ID__c,
  case
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 0
      and sfa.Id is null then 'Checking'
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 1
      and sfa.Id is null then 'Savings'
    when sfa.Id is not null then 'Business Checking'
  end ChargentOrders__Bank_Account_Type__c,
  'a1p36000001C8uVAAS' ChargentOrders__Gateway__c,
  'Check' ChargentOrders__Payment_Method__c,
  'Recurring' ChargentOrders__Payment_Status__c,
  'Monthly' ChargentOrders__Payment_Frequency__c,
  'Unending' ChargentOrders__Payment_Stop__c,
  substring(
    convert(
      varchar,
      dbo.query_nexttransactiondate(g.id, g.gift_status), 120),1,8) +
    cast(ag.schedule_dayofmonth as varchar)
    ChargentOrders__Payment_Start_Date__c,
  ag.schedule_dayofmonth ChargentOrders__Charge_Date__c
from gift ag
  join gift g on g.id in (select giftid from giftamendment where amendmentid = ag.id)
  join records r on r.id = g.constit_id
  join re_reporter..re_eft ree
    on ree.constituentid = r.constituent_id
  left outer join re_reporter..sf_contact sfc
    on sfc.re_legacy_id__c = g.constit_id
  left outer join re_reporter..sf_account sfa
    on sfa.re_legacy_id__c = g.constit_id
  where ag.id in (
    select lastamend.amendmentid
    from (
      select
        giftid,
        sequence,
        amendmentid,
        row_number()
        over(partition by giftid order by sequence desc) uno
      from giftamendment
      join gift on gift.id = giftamendment.giftid
      where gift.payment_type = 6
      and gift.gift_status = 1
      and gift.installment_frequency = 6
    ) lastamend
    where lastamend.uno = 1)

union all
-- get the second of any bi-monthly adjusted
select
  ag.id RE_LEGACY_ID__c,
  'Nada Bank' ChargentOrders__Bank_Name__c,
  ree.routingno ChargentOrders__Bank_Routing_Number__c,
  ree.accountnumber ChargentOrders__Bank_Account_Number__c,
  coalesce(sfc.AccountId, sfa.Id) ChargentOrders__Account__c,
  g.constituentbankid RE_BANK_ID__c,
  case
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 0
      and sfa.Id is null then 'Checking'
    when (select accounttype from constituent_bank where id = g.constituentbankid) = 1
      and sfa.Id is null then 'Savings'
    when sfa.Id is not null then 'Business Checking'
  end ChargentOrders__Bank_Account_Type__c,
  'a1p36000001C8uVAAS' ChargentOrders__Gateway__c,
  'Check' ChargentOrders__Payment_Method__c,
  'Recurring' ChargentOrders__Payment_Status__c,
  'Monthly' ChargentOrders__Payment_Frequency__c,
  'Unending' ChargentOrders__Payment_Stop__c,
  substring(
    convert(
      varchar,
      dbo.query_nexttransactiondate(g.id, g.gift_status), 120),1,8) +
    cast(ag.schedule_dayofmonth2 as varchar)
    ChargentOrders__Payment_Start_Date__c,
  ag.schedule_dayofmonth2 ChargentOrders__Charge_Date__c
from gift ag
  join gift g on g.id in (select giftid from giftamendment where amendmentid = ag.id)
  join records r on r.id = g.constit_id
  join re_reporter..re_eft ree
    on ree.constituentid = r.constituent_id
  left outer join re_reporter..sf_contact sfc
    on sfc.re_legacy_id__c = g.constit_id
  left outer join re_reporter..sf_account sfa
    on sfa.re_legacy_id__c = g.constit_id
  where ag.id in (
    select lastamend.amendmentid
    from (
      select
        giftid,
        sequence,
        amendmentid,
        row_number()
        over(partition by giftid order by sequence desc) uno
      from giftamendment
      join gift on gift.id = giftamendment.giftid
      where gift.payment_type = 6
      and gift.gift_status = 1
      and gift.installment_frequency = 6
    ) lastamend
    where lastamend.uno = 1)