Back to OIM Explorer

dbo.QER_ZITShopCheckWaiting

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. HOCallMethod -> personwantsorg.Assign at line 32; References QBM_PJobCreate*; References QBM_PJobCreate_HOCallMethod*

Source: sandbox-db sys.sql_modules

Source size: 5.684 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • HOCallMethod -> personwantsorg.Assign at line 32
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOCallMethod*

Typed Edges

  • calls object method personwantsorg.Assign HOCallMethod -> personwantsorg.Assign at line 32
  • references source dbo.QBM_FCVBitToString source text reference
  • references source dbo.QBM_FCVElementToObjectKey2 source text reference
  • references source dbo.QBM_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIJobCreatedExists source text reference
  • references source dbo.QER_FGIGenProcIDForPWO source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod source text reference
  • references source dbo.QBM_PObjectExistsInDatabase source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_ZITShopCheck source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL206 lines
1CREATE PROCEDURE QER_ZITShopCheckWaiting(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @WhereClause nvarchar(1000)7  DECLARE @uid_personwantsorg varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))10  DECLARE @BasisObjectKey varchar(138)11  DECLARE @ObjectkeyOrdered varchar(138)12  DECLARE @ObjectKeyAssignment varchar(138)13  DECLARE @UID_OrgPR varchar(38)14  DECLARE @ObjectKeyToTest varchar(138)15  DECLARE @IsReusePossible BIT16  DECLARE @IsAssignment BIT17  DECLARE @uid_personOrdered varchar(38)18  DECLARE @uid_PwoToActivate varchar(38)19  DECLARE @Existiert int20  DECLARE @Orderstate nvarchar(32)21  DECLARE @ElementBuffer QBM_YCursorBuffer22  DECLARE @ElementCount int23  DECLARE @ElementIndex int24  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent25  BEGIN TRY26    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,27    UID_Parameter,28    UID_SubParameter,29    GenProcID)30    SELECT31      UID_DialogDBQueue,32      UID_Parameter,33      UID_SubParameter,34      GenProcID35    FROM QBMDBQueueCurrent cu36      WITH(readpast)37    WHERE38      cu.SlotNumber = @SlotNumber39    IF @@rowcount = 040    BEGIN41      GOTO EndLabel42    END43    INSERT INTO @ElementBuffer(UID1,44    UID2,45    ObjectKey1,46    ObjectKey2,47    ObjectKey3,48    Bit1,49    Ident1,50    UID3,51    UID4)52    SELECT53      pwo.uid_PersonWantsOrg,54      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,55      p.GenProcID,56      @CfgUseGenProcID) AS GenProcID,57      pwo.XObjectKey,58      pwo.ObjectkeyOrdered,59      pwo.ObjectKeyAssignment,60      ba.IsReusePossible,61      dbo.QBM_FCVBitToString(SIGN(LEN(ISNULL(r.uid_QERAssign, '')))) AS IsAssignment,62      pwo.uid_PersonOrdered,63      pwo.uid_Org64    FROM @DBQueueCurrent p65    JOIN personwantsorg pwo66      ON p.uid_Parameter = pwo.uid_Personwantsorg67    JOIN BaseTree pr68      ON pwo.uid_org = pr.uid_org69    JOIN BaseTreeAssign ba70      ON dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',71    pwo.ObjectkeyOrdered) = ba.UID_DialogTableElement72    JOIN(73    SELECT MAX(pw.uid_personwantsorg) AS uid_personwantsorg74    FROM @DBQueueCurrent p75    JOIN PersonWantsOrg pw76      ON p.UID_Parameter = pw.UID_PersonWantsOrg77    WHERE78      pw.OrderState IN('Aborted', 'Dismissed', 'Unsubscribed')79    GROUP BY pw.UID_Org,80    pw.UID_PersonOrdered) AS z81      ON z.uid_personwantsorg = pwo.UID_PersonWantsOrg82    LEFT83    OUTER84    JOIN QERAssign r85      ON pwo.ObjectkeyOrdered = r.XObjectKey86    WHERE87      pwo.OrderState IN('Aborted',88    'Dismissed',89    'Unsubscribed') AND ba.IsReusePossible = 090    UNION91    SELECT92      pwo.uid_PersonWantsOrg,93      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,94      p.GenProcID,95      @CfgUseGenProcID) AS GenProcID,96      pwo.XObjectKey,97      pwo.ObjectkeyOrdered,98      pwo.ObjectKeyAssignment,99      ba.IsReusePossible,100      sign(len(isnull(r.uid_qerassign, ''))),101      pwo.uid_PersonOrdered,102      pwo.uid_Org103    FROM @DBQueueCurrent p104    JOIN personwantsorg pwo105      ON p.uid_Parameter = pwo.uid_Personwantsorg106    JOIN BaseTree pr107      ON pwo.uid_org = pr.uid_org108    JOIN BaseTreeAssign ba109      ON dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',110    pwo.ObjectkeyOrdered) = ba.UID_DialogTableElement111    LEFT112    OUTER113    JOIN QERAssign r114      ON pwo.ObjectkeyOrdered = r.XObjectKey115    WHERE116      pwo.OrderState IN('Aborted',117    'Dismissed',118    'Unsubscribed') AND ba.IsReusePossible = 1119    SELECT @ElementCount = @@ROWCOUNT120    SELECT @ElementIndex = 1121    WHILE @ElementIndex <= @ElementCount122    BEGIN123      SELECT124        TOP 1 @uid_personwantsorg = bu.UID1,125        @GenProcID = bu.UID2,126        @BasisObjectKey = bu.ObjectKey1,127        @ObjectkeyOrdered = bu.ObjectKey2,128        @ObjectKeyAssignment = ObjectKey3,129        @IsReusePossible = bu.Bit1,130        @IsAssignment = dbo.QBM_FCVStringToBit(bu.Ident1),131        @uid_PersonOrdered = bu.UID3,132        @uid_OrgPR = bu.UID4133      FROM @ElementBuffer bu134      WHERE135        bu.ElementIndex = @ElementIndex136      SELECT @uid_PwoToActivate = NULL137      IF @IsReusePossible = 0138      BEGIN139        SELECT140          @ObjectkeyToTest = dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg',141          'uid_Person',142          @uid_PersonOrdered,143          'UID_ITShopOrg',144          @uid_OrgPR)145        EXEC @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest146        IF @Existiert = 0147        BEGIN148          SELECT149            TOP 1 @uid_PwoToActivate = pwo.uid_personwantsorg,150            @BasisObjectKey = pwo.XObjectKey151          FROM personwantsorg pwo152          WHERE153            pwo.uid_personordered = @uid_personordered AND pwo.ObjectkeyOrdered = @ObjectkeyOrdered AND orderstate = 'Waiting' AND isnull(pwo.validUntil154          ,155          '2200-01-01') > getUTCDate() AND pwo.UID_PersonWantsOrg <> @uid_personwantsorg AND dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0156          ORDER BY pwo.XDateinserted157        END158      END159      ELSE160      BEGIN161        IF @IsAssignment = 1162        BEGIN163          SELECT @ObjectkeyToTest = @ObjectKeyAssignment164          EXEC @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest165          IF @Existiert = 0166          BEGIN167            SELECT168              TOP 1 @uid_PwoToActivate = pwo.uid_personwantsorg,169              @BasisObjectKey = pwo.XObjectKey170            FROM personwantsorg pwo171            WHERE172              ObjectKeyAssignment = @ObjectKeyAssignment AND Orderstate = 'Waiting' AND isnull(pwo.validUntil,173            '2200-01-01') > getUTCDate() AND pwo.UID_PersonWantsOrg <> @uid_personwantsorg AND dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0174            ORDER BY pwo.XDateinserted175          END176        END177      END178      IF @uid_PwoToActivate IS NOT NULL179      BEGIN180        SELECT @WhereClause = 'uid_personwantsorg = ''' + rtrim(@uid_PwoToActivate) + N ''''181        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',182          @whereclause = @WhereClause,183          @save = 1,184          @MethodName = 'Assign',185          @GenProcID = @GenprocID,186          @ObjectKeysAffected = DEFAULT,187          @param1 = '#LDS#Automatic system approval: Other similar requests have been disabled.|',188          @Retries = 3,189          @isToFreezeOnError = 1,190          @BasisObjectKey = @BasisObjectKey,191          @checkForExisting = 1,192          @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'193      END194      SELECT @ElementIndex += 1195    END196  END TRY197  BEGIN CATCH198    EXEC QBM_PSessionErrorAdd DEFAULT199    RAISERROR('',200    18,201    1)202      WITH NOWAIT203  END CATCH204  endLabel:205  RETURN206END
Open raw exported source
SQL ยท Raw37 lines
1    create   procedure QER_ZITShopCheckWaiting (@SlotNumber int) as begin  declare @WhereClause nvarchar(1000) declare @uid_personwantsorg varchar2(38) declare @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'3)) declare @BasisObjectKey varchar(138) declare @ObjectkeyOrdered varchar(138)  declare @ObjectKeyAssignment varchar(138)  declare @UID_OrgPR varchar(384) declare @ObjectKeyToTest varchar(138)  declare @IsReusePossible bit declare @IsAssignment bit declare @uid_personOrdered varchar(38) declare @uid_PwoToActivate5 varchar(38) declare @Existiert int declare @Orderstate nvarchar(32) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex6 int declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select7 UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount8 = 0 begin goto EndLabel end  insert into @ElementBuffer ( UID1  , UID2  , ObjectKey1  , ObjectKey2  , ObjectKey3  , Bit1  , Ident1  , UID3  , UID4  ) 9select pwo.uid_PersonWantsOrg ,   dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, pwo.ObjectkeyOrdered10, pwo.ObjectKeyAssignment , ba.IsReusePossible , dbo.QBM_FCVBitToString( SIGN(LEN(ISNULL(r.uid_QERAssign, '')))) as IsAssignment , pwo.uid_PersonOrdered11, pwo.uid_Org from @DBQueueCurrent p join personwantsorg pwo on p.uid_Parameter = pwo.uid_Personwantsorg join BaseTree pr on pwo.uid_org = pr.uid_org join12 BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', pwo.ObjectkeyOrdered) = ba.UID_DialogTableElement   join ( select MAX(pw.uid_personwantsorg13) as uid_personwantsorg  from @DBQueueCurrent p join PersonWantsOrg pw on p.UID_Parameter = pw.UID_PersonWantsOrg   where pw.OrderState in ('Aborted', 14'Dismissed', 'Unsubscribed' ) group by pw.UID_Org, pw.UID_PersonOrdered ) as z on z.uid_personwantsorg = pwo.UID_PersonWantsOrg  left outer join QERAssign15 r on pwo.ObjectkeyOrdered = r.XObjectKey   where pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) and ba.IsReusePossible = 0 union select pwo.uid_PersonWantsOrg16 ,  dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, pwo.ObjectkeyOrdered, pwo.ObjectKeyAssignment17 , ba.IsReusePossible , sign(len(isnull(r.uid_qerassign, ''))) , pwo.uid_PersonOrdered, pwo.uid_Org from @DBQueueCurrent p join personwantsorg pwo on p.uid_Parameter18 = pwo.uid_Personwantsorg join BaseTree pr on pwo.uid_org = pr.uid_org join BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', pwo.ObjectkeyOrdered19) = ba.UID_DialogTableElement  left outer join QERAssign r on pwo.ObjectkeyOrdered = r.XObjectKey   where pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed'20 ) and ba.IsReusePossible = 1   select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_personwantsorg21 = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 , @ObjectkeyOrdered = bu.ObjectKey2 , @ObjectKeyAssignment = ObjectKey3 , @IsReusePossible22 = bu.Bit1 , @IsAssignment = dbo.QBM_FCVStringToBit(bu.Ident1) , @uid_PersonOrdered = bu.UID3  , @uid_OrgPR = bu.UID4 from @ElementBuffer bu where bu.ElementIndex23 = @ElementIndex  select @uid_PwoToActivate = null  if @IsReusePossible = 0 begin  select @ObjectkeyToTest = dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg'24, 'uid_Person', @uid_PersonOrdered, 'UID_ITShopOrg', @uid_OrgPR) exec @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest  if @Existiert = 0 begin25 select top 1 @uid_PwoToActivate = pwo.uid_personwantsorg, @BasisObjectKey = pwo.XObjectKey from personwantsorg pwo where pwo.uid_personordered = @uid_personordered26 and pwo.ObjectkeyOrdered = @ObjectkeyOrdered and orderstate = 'Waiting'  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate() and pwo.UID_PersonWantsOrg27 <> @uid_personwantsorg  and dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0         order by pwo.XDateinserted end  end  else  begin if @IsAssignment 28= 1 begin select @ObjectkeyToTest = @ObjectKeyAssignment exec @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest  if @Existiert = 0 begin select29 top 1 @uid_PwoToActivate = pwo.uid_personwantsorg, @BasisObjectKey = pwo.XObjectKey from personwantsorg pwo where ObjectKeyAssignment = @ObjectKeyAssignment30 and Orderstate = 'Waiting'  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate() and pwo.UID_PersonWantsOrg <> @uid_personwantsorg  and dbo.QBM_FGIJobCreatedExists31(pwo.XObjectKey) = 0         order by pwo.XDateinserted end  end  end  if @uid_PwoToActivate is not null begin select @WhereClause = 'uid_personwantsorg = '''32 + rtrim(@uid_PwoToActivate) + N'''' exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save = 1 , @MethodName33 = 'Assign' , @GenProcID = @GenprocID  , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval: Other similar requests have been disabled.|'34 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'35  end select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return36 end 37