Back to OIM Explorer

dbo.ATT_ZAllForPersonInBaseTree

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Single DBQueue insert -> QBM-K-Commonrecalculate / QBM_ZRecalculate at line 5; Bulk DBQueue insert -> ATT-K-AttestationHelper-Cfg / ATT_ZAttestationHelperFill_Cfg at line 5; Bulk DBQueue insert -> ATT-K-AttestationHelper-Cfg / ATT_ZAttestationHelperFill_Cfg at line 8; References QBM_PDBQueueInsert_Single

Source: sandbox-db sys.sql_modules

Source size: 6.403 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Single DBQueue insert -> QBM-K-Commonrecalculate / QBM_ZRecalculate at line 5
  • Bulk DBQueue insert -> ATT-K-AttestationHelper-Cfg / ATT_ZAttestationHelperFill_Cfg at line 5
  • Bulk DBQueue insert -> ATT-K-AttestationHelper-Cfg / ATT_ZAttestationHelperFill_Cfg at line 8
  • References QBM_PDBQueueInsert_Single
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZRecalculate at line 5 Single DBQueue insert -> QBM-K-Commonrecalculate / QBM_ZRecalculate at line 5
  • queues DBQueue task ATT_ZAttestationHelperFill_Cfg at line 5 Bulk DBQueue insert -> ATT-K-AttestationHelper-Cfg / ATT_ZAttestationHelperFill_Cfg at line 5
  • queues DBQueue task ATT_ZAttestationHelperFill_Cfg at line 8 Bulk DBQueue insert -> ATT-K-AttestationHelper-Cfg / ATT_ZAttestationHelperFill_Cfg at line 8
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PITShopHelperFill source text reference
  • references source dbo.QER_PITShopHelperFill_Recalc source text reference
  • queues DBQueue task QBM-K-Commonrecalculate -> QBM_ZRecalculate QBM_PDBQueueInsert_Single 'QBM-K-Commonrecalculate', 'ATT-K-AttestationHelper', '', @GenprocID end declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01(Object, SubObject, GenProcID) select distinct h.UID_AttestationC…
  • queues DBQueue task ATT-K-AttestationHelper-Cfg -> ATT_ZAttestationHelperFill_Cfg QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper-Cfg', @DBQueueElements_01 end declare @RecalcParameter_01 QBM_YCursorBuffer insert into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select dbo.QBM_FCVElementToObjectKey1('Person', 'UID_Pe…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL312 lines
1CREATE PROCEDURE ATT_ZAllForPersonInBaseTree(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @GenprocID varchar(38)7  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent8  BEGIN TRY9    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,10    UID_Parameter,11    UID_SubParameter,12    GenProcID)13    SELECT14      UID_DialogDBQueue,15      UID_Parameter,16      UID_SubParameter,17      GenProcID18    FROM QBMDBQueueCurrent cu19      WITH(readpast)20    WHERE21      cu.SlotNumber = @SlotNumber22    IF @@rowcount = 023    BEGIN24      GOTO EndLabel25    END26    SELECT TOP 1 @GenprocID = max(cu.GenProcID)27    FROM @DBQueueCurrent cu28    IF EXISTS(29      SELECT TOP 1 130      FROM @DBQueueCurrent cu31      WHERE32        cu.UID_SubParameter = 'ATT-AEROLE-ATTESTATION-INTERVENTION')33    BEGIN34      EXEC QBM_PDBQueueInsert_Single 'QBM-K-Commonrecalculate',35      'ATT-K-AttestationHelper',36      '',37        @GenprocID38    END39    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw40    INSERT INTO @DBQueueElements_01(Object,41    SubObject,42    GenProcID)43    SELECT44      DISTINCT h.UID_AttestationCase,45      '',46      cu.GenProcID47    FROM @DBQueueCurrent cu48    JOIN QERWorkingStep s49      ON s.UID_AERoleFallBack = cu.UID_SubParameter50    JOIN AttestationHelper h51      ON h.UID_QERWorkingStep = s.UID_QERWorkingStep52    IF @@ROWCOUNT > 053    BEGIN54      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper-Cfg',55        @DBQueueElements_0156    END57    DECLARE @RecalcParameter_01 QBM_YCursorBuffer58    INSERT INTO @RecalcParameter_01(ObjectKey1,59    UID1,60    Ident1)61    SELECT62      dbo.QBM_FCVElementToObjectKey1('Person',63      'UID_Person',64      i.UID_Person),65      v.UID_PWODecisionRule,66      'A'67    FROM(68    SELECT69      cu.UID_Parameter AS UID_Person,70      cu.UID_SubParameter AS UID_Org71    FROM @DBQueueCurrent cu) i72    JOIN AccProduct ap73      ON i.UID_Org = ap.UID_OrgAttestator CROSS74    JOIN(75    VALUES('ATT-PWODecisionRule-AN'),76    ('ARS-AttestationRule-KA')) AS v(UID_PWODecisionRule)77    INSERT INTO @RecalcParameter_01(ObjectKey1,78    UID1,79    Ident1)80    SELECT81      dbo.QBM_FCVElementToObjectKey1('Person',82      'UID_Person',83      i.UID_Person),84      'ATT-PWODecisionRule-AT',85      'A'86    FROM(87    SELECT88      cu.UID_Parameter AS UID_Person,89      cu.UID_SubParameter AS UID_Org90    FROM @DBQueueCurrent cu) i91    INSERT INTO @RecalcParameter_01(ObjectKey1,92    UID1,93    Ident1)94    SELECT95      dbo.QBM_FCVElementToObjectKey1('Person',96      'UID_Person',97      i.UID_Person),98      'ATT-PWODecisionRule-XM',99      'A'100    FROM(101    SELECT102      cu.UID_Parameter AS UID_Person,103      cu.UID_SubParameter AS UID_Org104    FROM @DBQueueCurrent cu) i105    INSERT INTO @RecalcParameter_01(ObjectKey1,106    UID1,107    Ident1)108    SELECT109      dbo.QBM_FCVElementToObjectKey1('Person',110      'UID_Person',111      i.UID_Person),112      'ATT-PWODecisionRule-CM',113      'A'114    FROM(115    SELECT116      cu.UID_Parameter AS UID_Person,117      cu.UID_SubParameter AS UID_Org118    FROM @DBQueueCurrent cu) i119    INSERT INTO @RecalcParameter_01(ObjectKey1,120    UID1,121    Ident1)122    SELECT123      ap.XObjectKey,124      v.UID_PWODecisionRule,125      'A'126    FROM(127    SELECT128      cu.UID_Parameter AS UID_Person,129      cu.UID_SubParameter AS UID_Org130    FROM @DBQueueCurrent cu) i131    JOIN AccProduct ap132      ON i.UID_Org = ap.UID_OrgRuler CROSS133    JOIN(134    VALUES('ATT-PWODecisionRule-EO'),135    ('ATT-PWODecisionRule-OA'),136    ('ARS-AttestationRule-KA')) AS v(UID_PWODecisionRule)137    INSERT INTO @RecalcParameter_01(ObjectKey1,138    UID1,139    Ident1)140    SELECT141      ap.XObjectKey,142      'ATT-PWODecisionRule-OrgATT',143      'A'144    FROM(145    SELECT146      cu.UID_Parameter AS UID_Person,147      cu.UID_SubParameter AS UID_Org148    FROM @DBQueueCurrent cu) i149    JOIN AccProduct ap150      ON i.UID_Org = ap.UID_OrgAttestator151    INSERT INTO @RecalcParameter_01(ObjectKey1,152    UID1,153    Ident1)154    SELECT155      ag.XObjectKey,156      'ATT-PWODecisionRule-OrgATT',157      'A'158    FROM(159    SELECT160      cu.UID_Parameter AS UID_Person,161      cu.UID_SubParameter AS UID_Org162    FROM @DBQueueCurrent cu) i163    JOIN AccProductGroup ag164      ON i.UID_Org = ag.UID_OrgAttestator165    INSERT INTO @RecalcParameter_01(ObjectKey1,166    UID1,167    Ident1)168    SELECT169      bt.XObjectKey,170      v.UID_PWODecisionRule,171      'A'172    FROM(173    SELECT174      cu.UID_Parameter AS UID_Person,175      cu.UID_SubParameter AS UID_Org176    FROM @DBQueueCurrent cu) i177    JOIN BaseTree bt178      ON i.UID_Org = bt.UID_Org CROSS179    JOIN(180    VALUES('ATT-PWODecisionRule-OR'),181    ('ATT-PWODecisionRule-PM'),182    ('ATT-PWODecisionRule-AD'),183    ('ATT-PWODecisionRule-AL'),184    ('ATT-PWODecisionRule-AO'),185    ('ATT-PWODecisionRule-AP'),186    ('ATT-PWODecisionRule-AA'),187    ('POL-PWODecisionRule-AY'),188    ('CPL-PWODecisionRule-AR'),189    ('AOB-ATTDecisionRule-BA'),190    ('AOB-ATTDecisionRule-BE'),191    ('ATT-PWODecisionRule-PW')) AS v(UID_PWODecisionRule)192    INSERT INTO @RecalcParameter_01(ObjectKey1,193    UID1,194    Ident1)195    SELECT196      bt.XObjectKey,197      'TSB-PWODecisionRule-SO',198      'A'199    FROM(200    SELECT201      cu.UID_Parameter AS UID_Person,202      cu.UID_SubParameter AS UID_Org203    FROM @DBQueueCurrent cu) i204    JOIN BaseTree bt205      ON i.UID_Org = bt.UID_Org206    JOIN BasetreeCollection co207      ON i.uid_org = co.uid_org208    IF EXISTS(209      SELECT TOP 1 1210      FROM QBMModuleDef211      WHERE212        ModuleName = 'TSB')213    BEGIN214      INSERT INTO @RecalcParameter_01(ObjectKey1,215      UID1,216      Ident1)217      SELECT218        DISTINCT r.XObjectKey,219        'TSB-PWODecisionRule-EN',220        'A'221      FROM(222      SELECT223        cu.UID_Parameter AS UID_Person,224        cu.UID_SubParameter AS UID_Org225      FROM @DBQueueCurrent cu) i226      JOIN UNSRoot r227        ON (i.UID_Org = r.UID_AERoleOwner OR r.UID_AERoleOwner IS NULL)228      IF EXISTS(229        SELECT TOP 1 1230        FROM QBMModuleDef231        WHERE232          ModuleName = 'SP0')233      BEGIN234        INSERT INTO @RecalcParameter_01(ObjectKey1,235        UID1,236        Ident1)237        SELECT238          DISTINCT s.XObjectKey,239          'TSB-PWODecisionRule-EN',240          'A'241        FROM(242        SELECT243          cu.UID_Parameter AS UID_Person,244          cu.UID_SubParameter AS UID_Org245        FROM @DBQueueCurrent cu) i246        JOIN SPSFarm r247          ON i.UID_Org = r.UID_AERoleOwner248        JOIN SPSWebApplication wa249          ON r.UID_SPSFarm = wa.UID_SPSFarm250        JOIN SPSSite s251          ON s.UID_SPSWebapplication = wa.UID_SPSWebapplication252      END253      IF EXISTS(254        SELECT TOP 1 1255        FROM QBMModuleDef256        WHERE257          ModuleName = 'ADS')258      BEGIN259        INSERT INTO @RecalcParameter_01(ObjectKey1,260        UID1,261        Ident1)262        SELECT263          DISTINCT r.XObjectKey,264          'TSB-PWODecisionRule-EN',265          'A'266        FROM(267        SELECT268          cu.UID_Parameter AS UID_Person,269          cu.UID_SubParameter AS UID_Org270        FROM @DBQueueCurrent cu) i271        JOIN ADSContainer c272          ON (i.UID_Org = c.UID_AERoleOwner OR c.UID_AERoleOwner IS NULL)273        JOIN ADSDomain r274          ON c.UID_ADSDomain = r.UID_ADSDomain275      END276    END277    IF EXISTS(278      SELECT TOP 1 1279      FROM QBMModuleDef280      WHERE281        ModuleName = 'AAD')282    BEGIN283      INSERT INTO @RecalcParameter_01(ObjectKey1,284      UID1,285      Ident1)286      SELECT287        bt.XObjectKey,288        v.UID_PWODecisionRule,289        'A'290      FROM(291      SELECT292        cu.UID_Parameter AS UID_Person,293        cu.UID_SubParameter AS UID_Org294      FROM @DBQueueCurrent cu) i295      JOIN BaseTree bt296        ON i.UID_Org = bt.UID_Org CROSS297      JOIN(298      VALUES('AAD-PWODecisionRule-SP'),299      ('ATT-PWODecisionRule-EO')) AS v(UID_PWODecisionRule)300    END301    EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_01,302      @GenprocID303  END TRY304  BEGIN CATCH305    EXEC QBM_PSessionErrorAdd DEFAULT306    RAISERROR('',307    18,308    1)309      WITH NOWAIT310  END CATCH311  endLabel:312END
Open raw exported source
SQL · Raw42 lines
1    create   procedure ATT_ZAllForPersonInBaseTree (@SlotNumber int) as begin  declare @GenprocID varchar(38) declare @DBQueueCurrent QBM_YDBQueueCurrent2 BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter3, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select top 1 @GenprocID4 = max(cu.GenProcID) from @DBQueueCurrent cu if exists (select top 1 1 from @DBQueueCurrent cu where cu.UID_SubParameter = 'ATT-AEROLE-ATTESTATION-INTERVENTION'5 ) begin   exec QBM_PDBQueueInsert_Single 'QBM-K-Commonrecalculate', 'ATT-K-AttestationHelper', '', @GenprocID end declare @DBQueueElements_01 QBM_YDBQueueRaw6 insert into @DBQueueElements_01(Object, SubObject, GenProcID) select distinct h.UID_AttestationCase, '', cu.GenProcID from @DBQueueCurrent cu join QERWorkingStep7 s on s.UID_AERoleFallBack = cu.UID_SubParameter join AttestationHelper h on h.UID_QERWorkingStep = s.UID_QERWorkingStep if @@ROWCOUNT > 0 begin   exec8 QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper-Cfg', @DBQueueElements_01 end   declare @RecalcParameter_01 QBM_YCursorBuffer insert into @RecalcParameter_019 (ObjectKey1, UID1, Ident1)  select dbo.QBM_FCVElementToObjectKey1('Person', 'UID_Person', i.UID_Person), v.UID_PWODecisionRule, 'A' from (select cu.UID_Parameter10 as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent cu ) i join AccProduct ap on i.UID_Org = ap.UID_OrgAttestator cross join (values (11'ATT-PWODecisionRule-AN') , ('ARS-AttestationRule-KA') ) as v (UID_PWODecisionRule) insert into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select dbo.QBM_FCVElementToObjectKey112('Person', 'UID_Person', i.UID_Person), 'ATT-PWODecisionRule-AT', 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org from13 @DBQueueCurrent cu ) i insert into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select dbo.QBM_FCVElementToObjectKey1('Person', 'UID_Person', i.UID_Person14), 'ATT-PWODecisionRule-XM', 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent cu ) i insert into @RecalcParameter_0115 (ObjectKey1, UID1, Ident1) select dbo.QBM_FCVElementToObjectKey1('Person', 'UID_Person', i.UID_Person), 'ATT-PWODecisionRule-CM', 'A' from (select cu.UID_Parameter16 as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent cu ) i  insert into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select ap.XObjectKey17, v.UID_PWODecisionRule, 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent cu ) i join AccProduct ap18 on i.UID_Org = ap.UID_OrgRuler cross join (values ('ATT-PWODecisionRule-EO') , ('ATT-PWODecisionRule-OA') , ('ARS-AttestationRule-KA') ) as v (UID_PWODecisionRule19) insert into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select ap.XObjectKey, 'ATT-PWODecisionRule-OrgATT' , 'A' from (select cu.UID_Parameter as 20UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent cu ) i join AccProduct ap on i.UID_Org = ap.UID_OrgAttestator  insert into @RecalcParameter_0121 (ObjectKey1, UID1, Ident1) select ag.XObjectKey, 'ATT-PWODecisionRule-OrgATT' , 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter 22as UID_Org from @DBQueueCurrent cu ) i join AccProductGroup ag on i.UID_Org = ag.UID_OrgAttestator  insert into @RecalcParameter_01 (ObjectKey1, UID1, 23Ident1) select bt.XObjectKey, v.UID_PWODecisionRule, 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent24 cu ) i join BaseTree bt on i.UID_Org = bt.UID_Org cross join (values ('ATT-PWODecisionRule-OR') , ('ATT-PWODecisionRule-PM')  , ('ATT-PWODecisionRule-AD'25) , ('ATT-PWODecisionRule-AL') , ('ATT-PWODecisionRule-AO')  , ('ATT-PWODecisionRule-AP') , ('ATT-PWODecisionRule-AA')  , ('POL-PWODecisionRule-AY')  ,26 ('CPL-PWODecisionRule-AR') , ('AOB-ATTDecisionRule-BA') , ('AOB-ATTDecisionRule-BE') , ('ATT-PWODecisionRule-PW') ) as v (UID_PWODecisionRule) insert 27into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select bt.XObjectKey, 'TSB-PWODecisionRule-SO', 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter28 as UID_Org from @DBQueueCurrent cu ) i join BaseTree bt on i.UID_Org = bt.UID_Org join BasetreeCollection co on i.uid_org = co.uid_org   if exists (select29 top 1 1 from QBMModuleDef where ModuleName = 'TSB') begin  insert into @RecalcParameter_01 (ObjectKey1, UID1, Ident1) select distinct r.XObjectKey, 'TSB-PWODecisionRule-EN'30, 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent cu ) i join UNSRoot r on (i.UID_Org = r.UID_AERoleOwner31 or r.UID_AERoleOwner is null)  if exists (select top 1 1 from QBMModuleDef where ModuleName = 'SP0') begin  insert into @RecalcParameter_01 (ObjectKey132, UID1, Ident1) select distinct s.XObjectKey, 'TSB-PWODecisionRule-EN', 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org33 from @DBQueueCurrent cu ) i join SPSFarm r on i.UID_Org = r.UID_AERoleOwner join SPSWebApplication wa on r.UID_SPSFarm = wa.UID_SPSFarm join SPSSite s34 on s.UID_SPSWebapplication = wa.UID_SPSWebapplication end if exists (select top 1 1 from QBMModuleDef where ModuleName = 'ADS') begin  insert into @RecalcParameter_0135 (ObjectKey1, UID1, Ident1) select distinct r.XObjectKey, 'TSB-PWODecisionRule-EN', 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter36 as UID_Org from @DBQueueCurrent cu ) i join ADSContainer c on (i.UID_Org = c.UID_AERoleOwner or c.UID_AERoleOwner is null) join ADSDomain r on c.UID_ADSDomain37 = r.UID_ADSDomain end end  if exists (select top 1 1 from QBMModuleDef where ModuleName = 'AAD') begin  insert into @RecalcParameter_01 (ObjectKey1, UID138, Ident1) select bt.XObjectKey, v.UID_PWODecisionRule, 'A' from (select cu.UID_Parameter as UID_Person , cu.UID_SubParameter as UID_Org from @DBQueueCurrent39 cu ) i join BaseTree bt on i.UID_Org = bt.UID_Org cross join (values ('AAD-PWODecisionRule-SP') , ('ATT-PWODecisionRule-EO')  ) as v (UID_PWODecisionRule40) end exec QER_PITShopHelperFill_Recalc @RecalcParameter_01, @GenprocID  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH41 NOWAIT END CATCH endLabel: end 42