CREATE_OR_DROP_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_INDICES

create procedure [BBDW].[CREATE_OR_DROP_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_INDICES]
  @CREATE_OR_DROP bit --1 to create, 0 to drop.
  as
  set nocount on;

  if @CREATE_OR_DROP is null
  raiserror('@CREATE_OR_DROP must be 1 or 0 in [BBDW].[CREATE_OR_DROP_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_INDICES]',16,10);

  if @CREATE_OR_DROP = 1
  begin
  --create
    if  [BBDW].[UFN_INDEXEXISTS]('IDX_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_UPSERT') = 0
      create nonclustered index [IDX_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_UPSERT] on [BBDW].[FACT_FINANCIALTRANSACTIONLINEITEM_STAGE] (
        [PAYMENTMETHODCODE],
        [FINANCIALTRANSACTIONTRANSACTIONTYPECODE],
        [FINANCIALTRANSACTIONPOSTSTATUSCODE],
        [RECEIPTTYPECODE],
        [FINANCIALTRANSACTIONLINEITEMPOSTSTATUSCODE],
        [FINANCIALTRANSACTIONLINEITEMAPPLICATIONTYPECODE],
        [FINANCIALTRANSACTIONLINEITEMAPPLICATIONCODE],
        [FINANCIALTRANSACTIONLINEITEMTYPECODE]
      ) include (
        [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        [FINANCIALTRANSACTIONSYSTEMID],
        [FINANCIALTRANSACTIONSEQUENCEID],
        [FINANCIALTRANSACTIONLOOKUPID],
        [REVENUEFLAGDIMID],
        [REVENUECHANNELDIMID],
        [FINANCIALTRANSACTIONDATEDIMID],
        [FINANCIALTRANSACTIONDATE],
        [FINANCIALTRANSACTIONPOSTDATEDIMID],
        [FINANCIALTRANSACTIONPOSTDATE],
        [CONSTITUENTSYSTEMID],
        [APPEALDIMID],
        [MARKETINGSEGMENTDIMID],
        [MARKETINGSOURCECODEDIMID],
        [FINANCIALTRANSACTIONAMOUNT],
        [FINANCIALTRANSACTIONRECEIPTAMOUNT],
        [BASECURRENCYDIMID],
        [FINANCIALTRANSACTIONBASEAMOUNT],
        [FINANCIALTRANSACTIONDATETIMEOFFSET],
        [ACCOUNTSYSTEMDIMID],
        [APPUSERDIMID],
        [CHECKNUMBER],
        [CREDITCARDTYPEDIMID],
        [CHECKFUZZYDATE],
        [GLREVENUECATEGORYDIMID],
        [FINANCIALTRANSACTIONLINEITEMPOSTDATEDIMID],
        [FINANCIALTRANSACTIONLINEITEMPOSTDATE],
        [DESIGNATIONDIMID],
        [PURPOSELEVEL1DIMID],
        [PURPOSELEVEL2DIMID],
        [PURPOSELEVEL3DIMID],
        [PURPOSELEVEL4DIMID],
        [PURPOSELEVEL5DIMID],
        [EVENTREGISTRANTSYSTEMID],
        [FINANCIALTRANSACTIONLINEITEMAMOUNT],
        [FINANCIALTRANSACTIONLINEITEMBASEAMOUNT],
        [CHANGEDDATEDIMID],
        [CHANGEDDATE],
        [ISINCLUDED],
        [SOURCEDIMID],
        [ETLCONTROLID],
        [BATCHNUMBER],
        [FINANCIALTRANSACTIONLINEITEMADJUSTMENTSYSTEMID],
        [ADJUSTMENTCONSTITUENTSYSTEMID],
        [ADJUSTMENTDATEDIMID],
        [ADJUSTMENTDATE],
        [ADJUSTMENTREASONDIMID],
        [TRANSACTIONCURRENCYDIMID],
        [MARKETINGSEGMENTATIONDIMID],
        [FINANCIALTRANSACTIONLINEITEMTRANSACTIONAMOUNT],
        [REVENUESCHEDULEDIMID],
        [ADDEDDATE],
        [ADDEDDATEDIMID],
        [OTHERPAYMENTMETHODDIMID],
        [PLANNEDGIFTSYSTEMID],
        [REVERSEDFINANCIALTRANSACTIONLINEITEMSYSTEMID]
      ) on [BBRPT_STAGEGROUP];

  end
  else
  begin
  --drop

    if  [BBDW].[UFN_INDEXEXISTS]('IDX_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_UPSERT') = 1
     drop index [IDX_FACT_FINANCIALTRANSACTIONLINEITEM_STAGE_UPSERT] on [BBDW].[FACT_FINANCIALTRANSACTIONLINEITEM_STAGE];

  end