Back to OIM Explorer

dbo.TSB_ZPersonHasTSBAccountDef

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.603 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QER_FTPersonInheritFromOrg_L source text reference
  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGIModuleExists source text reference
  • references source dbo.TSB_FGIPersonHasTSBAccountDef source text reference
  • references source dbo.QBM_PDBQueueCalculateDelta source text reference
  • references source dbo.QBM_PMNTableInsert source text reference
  • references source dbo.QBM_PMNTableOriginUpdate source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.TSB_FTTSBAccountDefPredecessor source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL172 lines
1CREATE PROCEDURE TSB_ZPersonHasTSBAccountDef(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @GenProcID varchar(38)10  DECLARE @UID_BaseTreeAssignToUse varchar(38) = 'TSB-AsgnBT-TSBAccountDef'11  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent12  DECLARE @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',13  0)14  DECLARE @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',15  1)16  BEGIN TRY17    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,18    UID_Parameter,19    UID_SubParameter,20    GenProcID)21    SELECT22      UID_DialogDBQueue,23      UID_Parameter,24      UID_SubParameter,25      GenProcID26    FROM QBMDBQueueCurrent cu27      WITH(readpast)28    WHERE29      cu.SlotNumber = @SlotNumber30    IF @@rowcount = 031    BEGIN32      GOTO EndLabel33    END34    INSERT INTO @SourceData(IsUpcommingContent,35    XOriginAfter,36    Element,37    AssignedElement,38    XOriginBefore,39    XIsInEffectBefore,40    XIsInEffectAfter)41    SELECT42      0,43      0,44      UID_person,45      UID_TSBAccountDef,46      XOrigin,47      phr.XIsInEffect,48      049    FROM PersonHasTSBAccountDef phr50    JOIN @DBQueueCurrent x51      ON phr.UID_Person = x.uid_parameter52    INSERT INTO @SourceData(IsUpcommingContent,53    XOriginBefore,54    XOriginAfter,55    Element,56    AssignedElement,57    XIsInEffectBefore,58    XIsInEffectAfter)59    SELECT60      1,61      0,62    CASE63      WHEN pd.UID_TSBAccountDef = pd.UID_TSBAccountDefPredecessor THEN64    phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv65    ELSE @QBM_BitPatternXOrigin_Inherit66    END AS XOriginAfter,67    phr.UID_person,68    rv.UID_TSBAccountDef,69    0,70    dbo.TSB_FGIPersonHasTSBAccountDef(p.IsInActive,71    p.IsTemporaryDeactivated,72    p.XMarkedForDeletion,73    r.PFDInheritAccountDef & rv.PFDInheritAccountDef,74    r.PTDInheritAccountDef & rv.PTDInheritAccountDef,75    r.PMDInheritAccountDef & rv.PMDInheritAccountDef,76    p.IsSecurityIncident,77    r.PSIInheritAccountDef & rv.PSIInheritAccountDef) AS XIsInEffectAfter78    FROM PersonHasTSBAccountDef phr79    JOIN @DBQueueCurrent x80      ON UID_person = x.uid_parameter81    JOIN person p82      ON phr.uid_person = p.uid_person83    JOIN TSBAccountDef r84      ON phr.uid_TSBAccountDef = r.uid_TSBAccountDef CROSS apply dbo.TSB_FTTSBAccountDefPredecessor(r.UID_TSBAccountDef) pd85    JOIN TSBAccountDef rv86      ON pd.UID_TSBAccountDefPredecessor = rv.UID_TSBAccountDef87    WHERE88      phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 089    INSERT INTO @SourceData(IsUpcommingContent,90    XOriginBefore,91    Element,92    AssignedElement,93    XOriginAfter,94    XIsInEffectBefore,95    XIsInEffectAfter)96    SELECT97      1,98      0,99      x.UID_person,100      rv.UID_TSBAccountDef,101      @QBM_BitPatternXOrigin_Inherit AS XOrigin,102      0,103      dbo.TSB_FGIPersonHasTSBAccountDef(p.IsInActive,104      p.IsTemporaryDeactivated,105      p.XMarkedForDeletion,106      r.PFDInheritAccountDef & rv.PFDInheritAccountDef,107      r.PTDInheritAccountDef & rv.PTDInheritAccountDef,108      r.PMDInheritAccountDef & rv.PMDInheritAccountDef,109      p.IsSecurityIncident,110      r.PSIInheritAccountDef & rv.PSIInheritAccountDef) AS XIsInEffectAfter111    FROM(112    SELECT113      pha.UID_person,114      r.UID_TSBAccountDef115    FROM @DBQueueCurrent x116    JOIN PersonHasESet pha117      ON pha.UID_person = x.uid_parameter AND pha.XOrigin > 0 AND pha.XIsInEffect = 1118    JOIN person px119      ON pha.uid_person = px.uid_person AND px.isNoInherite = 0120    JOIN ESetHasEntitlement aha121      ON pha.UID_ESet = aha.UID_ESet AND aha.XOrigin > 0 AND aha.XIsInEffect = 1 AND dbo.QBM_FGIModuleExists('RMS') = 1122    JOIN TSBAccountDef r123      ON aha.Entitlement = r.XObjectKey124    UNION all125    SELECT126      pfo.UID_person,127      oha.UID_TSBAccountDef128    FROM dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse) pfo129    JOIN BaseTreeHasTSBAccountDef oha130      ON oha.uid_org = pfo.uid_org AND oha.XOrigin > 0 AND oha.XIsInEffect = 1) AS x131    JOIN Person p132      ON x.uid_person = p.uid_person133    JOIN TSBAccountDef r134      ON x.uid_TSBAccountDef = r.uid_TSBAccountDef CROSS apply dbo.TSB_FTTSBAccountDefPredecessor(r.UID_TSBAccountDef) pd135    JOIN TSBAccountDef rv136      ON pd.UID_TSBAccountDefPredecessor = rv.UID_TSBAccountDef137    EXEC QBM_PDBQueueCalculateDelta @SourceData,138      @DeltaQuantity = 0,139      @DeltaDelete = 0,140      @DeltaInsert = 1,141      @DeltaOrigin = 1,142      @CountDeltaQantity = @CountDeltaQantity OUTPUT,143      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,144      @UseIsInEffect = 1,145      @SlotNumber = @SlotNumber,146      @DBQueueCurrentExtern = @DBQueueCurrent147    IF @CountDeltaOrigin > 0148    BEGIN149      EXEC QBM_PMNTableOriginUpdate 'PersonHasTSBAccountDef',150      'uid_Person',151      'uid_TSBAccountDef'152    END153    IF @CountDeltaQantity > 0154    BEGIN155      EXEC QBM_PMNTableInsert 'PersonHasTSBAccountDef',156      'uid_Person',157      'uid_TSBAccountDef',158        @TargetIsView = 0,159        @FKTableNameElement = 'Person',160        @FKColumnNameElement = 'UID_Person'161    END162  END TRY163  BEGIN CATCH164    EXEC QBM_PSessionErrorAdd DEFAULT165    RAISERROR('',166    18,167    1)168      WITH NOWAIT169  END CATCH170  endLabel:171  RETURN172END
Open raw exported source
SQL ยท Raw31 lines
1     create   procedure TSB_ZPersonHasTSBAccountDef (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , 2@CountDeltaOrigin int declare @GenProcID varchar(38) declare @UID_BaseTreeAssignToUse varchar(38) = 'TSB-AsgnBT-TSBAccountDef' declare @DBQueueCurrent 3QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv4 int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID5) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount6 = 0 begin goto EndLabel end  insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore, 7XIsInEffectAfter ) select 0, 0 , UID_person, UID_TSBAccountDef, XOrigin , phr.XIsInEffect , 0 from PersonHasTSBAccountDef phr join @DBQueueCurrent x on8 phr.UID_Person = x.uid_parameter insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectBefore9, XIsInEffectAfter ) select 1, 0, case when pd.UID_TSBAccountDef = pd.UID_TSBAccountDefPredecessor then phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv10 else @QBM_BitPatternXOrigin_Inherit end as XOriginAfter , phr.UID_person, rv.UID_TSBAccountDef , 0  , dbo.TSB_FGIPersonHasTSBAccountDef ( p.IsInActive11 , p.IsTemporaryDeactivated , p.XMarkedForDeletion , r.PFDInheritAccountDef & rv.PFDInheritAccountDef , r.PTDInheritAccountDef & rv.PTDInheritAccountDef12 , r.PMDInheritAccountDef & rv.PMDInheritAccountDef , p.IsSecurityIncident , r.PSIInheritAccountDef & rv.PSIInheritAccountDef ) as XIsInEffectAfter from13 PersonHasTSBAccountDef phr join @DBQueueCurrent x on UID_person = x.uid_parameter  join person p on phr.uid_person = p.uid_person  join TSBAccountDef 14r on phr.uid_TSBAccountDef = r.uid_TSBAccountDef cross apply dbo.TSB_FTTSBAccountDefPredecessor(r.UID_TSBAccountDef) pd join TSBAccountDef rv on pd.UID_TSBAccountDefPredecessor15 = rv.UID_TSBAccountDef where phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 insert into @SourceData( IsUpcommingContent, XOriginBefore , Element16, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0 , x.UID_person, rv.UID_TSBAccountDef , @QBM_BitPatternXOrigin_Inherit17 as XOrigin , 0 , dbo.TSB_FGIPersonHasTSBAccountDef ( p.IsInActive , p.IsTemporaryDeactivated , p.XMarkedForDeletion , r.PFDInheritAccountDef & rv.PFDInheritAccountDef18 , r.PTDInheritAccountDef & rv.PTDInheritAccountDef , r.PMDInheritAccountDef & rv.PMDInheritAccountDef , p.IsSecurityIncident , r.PSIInheritAccountDef 19& rv.PSIInheritAccountDef ) as XIsInEffectAfter from ( select pha.UID_person, r.UID_TSBAccountDef from @DBQueueCurrent x join PersonHasESet pha on pha.UID_person20 = x.uid_parameter and pha.XOrigin > 0 and pha.XIsInEffect = 1  join person px on pha.uid_person = px.uid_person and px.isNoInherite = 0  join ESetHasEntitlement21 aha on pha.UID_ESet = aha.UID_ESet and aha.XOrigin > 0 and aha.XIsInEffect = 1 and dbo.QBM_FGIModuleExists('RMS') = 1 Join TSBAccountDef r on aha.Entitlement22 = r.XObjectKey union all select pfo.UID_person, oha.UID_TSBAccountDef  from dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse23) pfo join BaseTreeHasTSBAccountDef oha on oha.uid_org = pfo.uid_org and oha.XOrigin > 0 and oha.XIsInEffect = 1 ) as x join Person p on x.uid_person =24 p.uid_person join TSBAccountDef r on x.uid_TSBAccountDef = r.uid_TSBAccountDef cross apply dbo.TSB_FTTSBAccountDefPredecessor(r.UID_TSBAccountDef) pd 25join TSBAccountDef rv on pd.UID_TSBAccountDefPredecessor = rv.UID_TSBAccountDef  exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 0, @DeltaDelete26 = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect27 = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec QBM_PMNTableOriginUpdate 'PersonHasTSBAccountDef'28, 'uid_Person', 'uid_TSBAccountDef' end if @CountDeltaQantity > 0 begin exec QBM_PMNTableInsert 'PersonHasTSBAccountDef', 'uid_Person', 'uid_TSBAccountDef'29, @TargetIsView = 0 , @FKTableNameElement = 'Person' , @FKColumnNameElement = 'UID_Person' end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR30 ('', 18, 1) WITH NOWAIT END CATCH  endLabel: return end 31