Back to OIM Explorer

dbo.QER_PITShop_ShopMove

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 7.852 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

  • References QBM_PJobCreate*

Typed Edges

  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVElementToObjectKey2 source text reference
  • references source dbo.QBM_FGIGuidIsValid source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOTemplate source text reference
  • references source dbo.QBM_PJobCreate_HOTemplate_B source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PITShop_BoardMove source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL354 lines
1CREATE PROCEDURE QER_PITShop_ShopMove(2  @UID_OrgSHSource varchar(38),3  @UID_OrgSCTarget varchar(38)4)5AS6BEGIN7  DECLARE @uid_orgCU_Source varchar(38)8  DECLARE @UID_OrgSC_Source varchar(38)9  DECLARE @UID_OrgSH_Target varchar(38)10  DECLARE @UID_OrgCU_Target varchar(38)11  DECLARE @UID_DynamicGroup_Source varchar(38)12  DECLARE @UID_DynamicGroup_Target varchar(38)13  DECLARE @countCustomer_Source int14  DECLARE @WhereClause nvarchar(max)15  DECLARE @startat datetime16  DECLARE @ElementBuffer QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  DECLARE @UID_OrgBOSource varchar(38)20  DECLARE @GenProcID varchar(38)21  DECLARE @XUser nvarchar(64) = object_name(@@procid)22  DECLARE @Xdate datetime = getutcdate()23  DECLARE @FullPathSCTarget nvarchar(400)24  SET XACT_ABORT OFF25  BEGIN TRY26    SELECT @GenProcID = dbo.QBM_FGISessionContext('')27    SELECT @uid_orgCU_Source = NULL28    SELECT29      TOP 1 @uid_orgCU_Source = cu.uid_org,30      @UID_OrgSC_Source = sc.uid_org31    FROM Basetree sh32    JOIN BaseTree cu33      ON cu.uid_parentorg = sh.UID_Org AND cu.itShopInfo = 'CU'34    LEFT35    OUTER36    JOIN Basetree sc37      ON sh.UID_ParentOrg = sc.uid_org38    WHERE39      sh.uid_org = @UID_OrgSHSource AND sh.uid_OrgRoot = 'QER-V-ITShopOrg' AND sh.ITShopInfo = 'SH'40    IF @uid_orgCU_Source IS NULL41    BEGIN42      RAISERROR('#LDS#The customer node cannot be found in shop (source).|',43      18,44      2)45        WITH nowait46    END47    IF NOT EXISTS(48      SELECT TOP 1 149      FROM BaseTree50      WHERE51        uid_OrgRoot = 'QER-V-ITShopOrg' AND ITShopInfo = 'SC' AND uid_Org = @UID_OrgSCTarget)52    BEGIN53      RAISERROR('#LDS#The given target node is not a shopping center.|',54      18,55      2)56        WITH nowait57    END58    IF isnull(@UID_OrgSC_Source,59    '') = @UID_OrgSCTarget60    BEGIN61      GOTO ende62    END63    IF NOT EXISTS(64      SELECT TOP 1 165      FROM BaseTree66      WHERE67        uid_OrgRoot = 'QER-V-ITShopOrg' AND ITShopInfo = 'SH' AND uid_Org = @UID_OrgSHSource AND dbo.QBM_FGIGuidIsValid(uid_Org) & 2 = 0)68    BEGIN69      RAISERROR('#LDS#The given shop (source) must not be moved to another shoping center.|',70      18,71      2)72        WITH nowait73    END74    IF EXISTS(75      SELECT TOP 1 176      FROM BaseTree targetSH77      WHERE78        targetSH.uid_OrgRoot = 'QER-V-ITShopOrg' AND targetSH.ITShopInfo = 'SH' AND targetSH.uid_ParentOrg = @UID_OrgSCTarget AND targetSH.Ident_Org79    IN(80      SELECT Ident_Org81      FROM Basetree82      WHERE83        uid_org = @UID_OrgSHSource))84    BEGIN85      RAISERROR('#LDS#The target shopping center already contains a shop with the same ident.|',86      18,87      2)88        WITH nowait89    END90    SELECT TOP 1 @FullPathSCTarget = fullpath91    FROM Basetree92    WHERE93      UID_Org = @UID_OrgSCTarget94    SELECT @UID_OrgSH_Target = newid()95    INSERT INTO BaseTree(UID_Org,96    UID_ParentOrg,97    Ident_Org,98    IsCutNode,99    Description,100    uid_OrgRoot,101    UID_PersonHead,102    UID_PersonHeadSecond,103    UID_OrgDepartment,104    UID_OrgLocality,105    UID_OrgProfitCenter,106    UID_OrgAttestator,107    XDateInserted,108    XDateUpdated,109    XUserInserted,110    XUserUpdated,111    InternalName,112    FullPath,113    Commentary,114    ITShopInfo,115    XObjectKey,116    IsInvalidForDynamicGroup,117    UID_PWODecisionMethod)118    SELECT119      @UID_OrgSH_Target,120      @UID_OrgSCTarget,121      b.Ident_Org,122      0,123      b.Description,124      b.UID_OrgRoot,125      b.UID_PersonHead,126      b.UID_PersonHeadSecond,127      b.UID_OrgDepartment,128      b.UID_OrgLocality,129      b.UID_OrgProfitCenter,130      b.UID_OrgAttestator,131      GetUTCDate(),132      GetUTCDate(),133      'QER_PITShopShopMove',134      'QER_PITShopShopMove',135      b.InternalName,136      CONCAT(@FullPathSCTarget,137      '\',138      b.Ident_Org),139      b.Commentary,140      'SH',141      dbo.QBM_FCVElementToObjectKey1('ITShopOrg',142      'UID_ITShopOrg',143      @UID_OrgSH_Target),144      1,145      b.UID_PWODecisionMethod146    FROM Basetree b147    WHERE148      b.uid_org = @UID_OrgSHSource149    SELECT @UID_OrgCU_Target = newid()150    INSERT INTO BaseTree(UID_Org,151    UID_ParentOrg,152    Ident_Org,153    IsCutNode,154    Description,155    uid_OrgRoot,156    UID_PersonHead,157    UID_PersonHeadSecond,158    UID_OrgDepartment,159    UID_OrgLocality,160    UID_OrgProfitCenter,161    UID_OrgAttestator,162    XDateInserted,163    XDateUpdated,164    XUserInserted,165    XUserUpdated,166    InternalName,167    FullPath,168    Commentary,169    ITShopInfo,170    XObjectKey,171    IsInvalidForDynamicGroup)172    SELECT173      @UID_OrgCU_Target,174      @UID_OrgSH_Target,175      b.Ident_Org,176      0,177      b.Description,178      b.UID_OrgRoot,179      b.UID_PersonHead,180      b.UID_PersonHeadSecond,181      b.UID_OrgDepartment,182      b.UID_OrgLocality,183      b.UID_OrgProfitCenter,184      b.UID_OrgAttestator,185      GetUTCDate(),186      GetUTCDate(),187      'QER_PITShopShopMove',188      'QER_PITShopShopMove',189      b.InternalName,190      CONCAT(@FullPathSCTarget,191      '\\',192      b.Ident_Org),193      b.Commentary,194      'CU',195      dbo.QBM_FCVElementToObjectKey1('ITShopOrg',196      'UID_ITShopOrg',197      @UID_OrgCU_Target),198      0199    FROM Basetree b200    WHERE201      b.uid_org = @uid_orgCU_Source202    SELECT @WhereClause = 'UID_ITShopOrg = ''' + @UID_OrgCU_Target + ''''203    SELECT204      @startat = dateadd(mi,205      -5,206      GetUTCDate())207    EXEC QBM_PJobCreate_HOTemplate_B 'ITShopOrg',208      @WhereClause,209    'FullPath',210      @GenProcID,211      @AdditionalObjectKeysAffected = DEFAULT,212      @priority = 10,213      @startat = @startat214    INSERT INTO ITShopOrgHasPWODecisionMethod(UID_ITShopOrg,215    UID_PWODecisionMethod,216    XObjectkey,217    XDateInserted,218    XDateUpdated,219    XUserInserted,220    XUserUpdated)221    SELECT222      @UID_OrgSH_Target,223      o.UID_PWODecisionMethod,224      dbo.QBM_FCVElementToObjectKey2('ITShopOrgHasPWODecisionMethod',225      'UID_ITShopOrg',226      @UID_OrgSH_Target,227      'UID_PWODecisionMethod',228      o.UID_PWODecisionMethod),229      GetUTCDate(),230      GetUTCDate(),231      'QER_PITShopShopMove',232      'QER_PITShopShopMove'233    FROM ITShopOrgHasPWODecisionMethod o234    WHERE235      o.UID_ITShopOrg = @UID_OrgSHSource236    INSERT INTO PersonInITShopOrg(UID_Person,237    UID_ITShopOrg,238    XOrigin,239    XObjectkey,240    XDateInserted,241    XDateUpdated,242    XUserInserted,243    XUserUpdated)244    SELECT245      o.UID_Person,246      @UID_OrgCU_Target,247      1,248      dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg',249      'UID_ITShopOrg',250      @UID_OrgCU_Target,251      'UID_Person',252      o.UID_Person),253      GetUTCDate(),254      GetUTCDate(),255      'QER_PITShopShopMove',256      'QER_PITShopShopMove'257    FROM PersonInITShopOrg o258    WHERE259      o.UID_ITShopOrg = @uid_orgCU_Source AND o.XOrigin & 1 > 0260    SELECT @UID_DynamicGroup_Source = NULL261    SELECT @UID_DynamicGroup_Target = NULL262    SELECT TOP 1 @UID_DynamicGroup_Source = UID_DynamicGroup263    FROM DynamicGroup264    WHERE265      ObjectKeyBaseTree = dbo.QBM_FCVElementToObjectKey1('ITShopOrg',266    'UID_ITShopOrg',267    @uid_orgCU_Source) AND UID_DialogTableObjectClass = 'QER-T-Person'268    IF @UID_DynamicGroup_Source IS NOT NULL269    BEGIN270      SELECT @UID_DynamicGroup_Target = newid()271      INSERT INTO DynamicGroup(UID_DynamicGroup,272      UID_DialogTableObjectClass,273      UID_DialogSchedule,274      ObjectKeyBaseTree,275      DisplayName,276      Description,277      WhereClause,278      WhereClauseAddOn,279      XObjectkey,280      XDateInserted,281      XDateUpdated,282      XUserInserted,283      XUserUpdated)284      SELECT285        @UID_DynamicGroup_Target,286        o.UID_DialogTableObjectClass,287        o.UID_DialogSchedule,288        dbo.QBM_FCVElementToObjectKey1('ITShopOrg',289        'UID_ITShopOrg',290        @uid_orgCU_Target),291        o.DisplayName,292        o.Description,293        o.WhereClause,294        o.WhereClauseAddOn,295        dbo.QBM_FCVElementToObjectKey1('DynamicGroup',296        'UID_DynamicGroup',297        @UID_DynamicGroup_Target),298        GetUTCDate(),299        GetUTCDate(),300        'QER_PITShopShopMove',301        'QER_PITShopShopMove'302      FROM DynamicGroup o303      WHERE304        o.UID_DynamicGroup = @UID_DynamicGroup_Source305    END306    INSERT INTO @ElementBuffer(UID1)307    SELECT bo.UID_Org308    FROM basetree bo309    JOIN basetree sh310      ON bo.UID_ParentOrg = sh.UID_Org311    WHERE312      bo.ITShopInfo = 'BO' AND bo.UID_OrgRoot = 'QER-V-ITShopOrg' AND sh.UID_Org = @UID_OrgSHSource313    SELECT @ElementCount = @@ROWCOUNT314    SELECT @ElementIndex = 1315    WHILE @ElementIndex <= @ElementCount316    BEGIN317      SELECT TOP 1 @UID_OrgBOSource = bu.UID1318      FROM @ElementBuffer bu319      WHERE320        bu.ElementIndex = @ElementIndex321      EXEC QER_PITShop_BoardMove @UID_OrgBOSource,322        @UID_OrgSH_Target323      SELECT @ElementIndex += 1324    END325    DELETE DynamicGroup326    WHERE327      UID_DynamicGroup = @UID_DynamicGroup_Source328    UPDATE PersonInBaseTree329    SET XOrigin = 0,330    XDateUpdated = @Xdate,331    XUserUpdated = @XUser332    WHERE333      UID_Org = @uid_orgCU_Source334    DELETE PersonInBaseTree335    WHERE336      UID_Org = @uid_orgCU_Source337    DELETE BaseTree338    WHERE339      UID_Org = @uid_orgCU_Source340    DELETE BaseTree341    WHERE342      UID_Org = @UID_OrgSHSource343  END TRY344  BEGIN CATCH345    EXEC QBM_PSessionErrorAdd DEFAULT346    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()347    RAISERROR(@Rethrow,348    18,349    1)350      WITH NOWAIT351  END CATCH352  ende:353  RETURN354END
Open raw exported source
SQL ยท Raw51 lines
1   create   procedure QER_PITShop_ShopMove (@UID_OrgSHSource varchar(38)  , @UID_OrgSCTarget varchar(38)  ) as begin declare @uid_orgCU_Source varchar2(38) declare @UID_OrgSC_Source varchar(38) declare @UID_OrgSH_Target varchar(38) declare @UID_OrgCU_Target varchar(38) declare @UID_DynamicGroup_Source3 varchar(38) declare @UID_DynamicGroup_Target varchar(38) declare @countCustomer_Source int declare @WhereClause nvarchar(max) declare @startat datetime4 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @UID_OrgBOSource varchar(38) declare @GenProcID varchar5(38) declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @FullPathSCTarget nvarchar(400) SET XACT_ABORT 6OFF BEGIN TRY select @GenProcID = dbo.QBM_FGISessionContext('')  select @uid_orgCU_Source = null select top 1 @uid_orgCU_Source = cu.uid_org , @UID_OrgSC_Source7 = sc.uid_org from Basetree sh join BaseTree cu on cu.uid_parentorg = sh.UID_Org and cu.itShopInfo = 'CU'  left outer join Basetree sc on sh.UID_ParentOrg8 = sc.uid_org where sh.uid_org = @UID_OrgSHSource and sh.uid_OrgRoot = 'QER-V-ITShopOrg' and sh.ITShopInfo = 'SH' if @uid_orgCU_Source is null begin raiserror9( '#LDS#The customer node cannot be found in shop (source).|', 18, 2) with nowait end  if not exists (select top 1 1 from BaseTree where uid_OrgRoot = 10'QER-V-ITShopOrg' and ITShopInfo = 'SC' and uid_Org = @UID_OrgSCTarget ) begin  raiserror( '#LDS#The given target node is not a shopping center.|', 18,11 2) with nowait end  if isnull(@UID_OrgSC_Source, '') = @UID_OrgSCTarget begin  goto ende end  if not exists (select top 1 1 from BaseTree where uid_OrgRoot12 = 'QER-V-ITShopOrg' and ITShopInfo = 'SH' and uid_Org = @UID_OrgSHSource and dbo.QBM_FGIGuidIsValid(uid_Org) & 2 = 0 ) begin raiserror( '#LDS#The given shop (source) must not be moved to another shoping center.|'13, 18, 2) with nowait end  if exists (select top 1 1 from BaseTree targetSH where targetSH.uid_OrgRoot = 'QER-V-ITShopOrg' and targetSH.ITShopInfo = 'SH'14 and targetSH.uid_ParentOrg = @UID_OrgSCTarget and targetSH.Ident_Org in (select Ident_Org from Basetree where uid_org = @UID_OrgSHSource) ) begin raiserror15( '#LDS#The target shopping center already contains a shop with the same ident.|', 18, 2) with nowait end  select top 1 @FullPathSCTarget = fullpath from16 Basetree where UID_Org = @UID_OrgSCTarget  select @UID_OrgSH_Target = newid() insert into BaseTree( UID_Org, UID_ParentOrg, Ident_Org , IsCutNode, Description17 , uid_OrgRoot , UID_PersonHead, UID_PersonHeadSecond, UID_OrgDepartment, UID_OrgLocality, UID_OrgProfitCenter, UID_OrgAttestator , XDateInserted , XDateUpdated18 , XUserInserted , XUserUpdated, InternalName , FullPath ,Commentary , ITShopInfo , XObjectKey , IsInvalidForDynamicGroup , UID_PWODecisionMethod ) select19 @UID_OrgSH_Target, @UID_OrgSCTarget, b.Ident_Org, 0, b.Description, b.UID_OrgRoot , b.UID_PersonHead, b.UID_PersonHeadSecond, b.UID_OrgDepartment, b.UID_OrgLocality20, b.UID_OrgProfitCenter, b.UID_OrgAttestator , GetUTCDate(), GetUTCDate(), 'QER_PITShopShopMove', 'QER_PITShopShopMove', b.InternalName, concat(@FullPathSCTarget21, '\', b.Ident_Org), b.Commentary , 'SH', dbo.QBM_FCVElementToObjectKey1('ITShopOrg', 'UID_ITShopOrg', @UID_OrgSH_Target) , 1 , b.UID_PWODecisionMethod22 from Basetree b where b.uid_org = @UID_OrgSHSource                 select @UID_OrgCU_Target = newid() insert into BaseTree( UID_Org, UID_ParentOrg, Ident_Org23 , IsCutNode, Description , uid_OrgRoot , UID_PersonHead, UID_PersonHeadSecond, UID_OrgDepartment, UID_OrgLocality, UID_OrgProfitCenter, UID_OrgAttestator24 , XDateInserted , XDateUpdated , XUserInserted , XUserUpdated, InternalName , FullPath ,Commentary , ITShopInfo , XObjectKey , IsInvalidForDynamicGroup25 ) select @UID_OrgCU_Target, @UID_OrgSH_Target, b.Ident_Org, 0, b.Description, b.UID_OrgRoot , b.UID_PersonHead, b.UID_PersonHeadSecond, b.UID_OrgDepartment26, b.UID_OrgLocality, b.UID_OrgProfitCenter, b.UID_OrgAttestator , GetUTCDate(), GetUTCDate(), 'QER_PITShopShopMove', 'QER_PITShopShopMove', b.InternalName27, concat(@FullPathSCTarget, '\\', b.Ident_Org), b.Commentary , 'CU', dbo.QBM_FCVElementToObjectKey1('ITShopOrg', 'UID_ITShopOrg', @UID_OrgCU_Target) , 280 from Basetree b where b.uid_org = @uid_orgCU_Source  select @WhereClause = 'UID_ITShopOrg = ''' + @UID_OrgCU_Target + '''' select @startat = dateadd(mi29, -5, GetUTCDate()) exec QBM_PJobCreate_HOTemplate_B 'ITShopOrg', @WhereClause, 'FullPath' , @GenProcID , @AdditionalObjectKeysAffected = default , @priority30 = 10 , @startat = @startat        insert into ITShopOrgHasPWODecisionMethod (UID_ITShopOrg, UID_PWODecisionMethod , XObjectkey , XDateInserted, XDateUpdated31, XUserInserted, XUserUpdated ) select @UID_OrgSH_Target, o.UID_PWODecisionMethod , dbo.QBM_FCVElementToObjectKey2('ITShopOrgHasPWODecisionMethod', 'UID_ITShopOrg'32, @UID_OrgSH_Target, 'UID_PWODecisionMethod', o.UID_PWODecisionMethod) , GetUTCDate(), GetUTCDate(), 'QER_PITShopShopMove', 'QER_PITShopShopMove' from 33ITShopOrgHasPWODecisionMethod o where o.UID_ITShopOrg = @UID_OrgSHSource   insert into PersonInITShopOrg (UID_Person, UID_ITShopOrg , XOrigin, XObjectkey34 , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated ) select o.UID_Person, @UID_OrgCU_Target , 1, dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg'35, 'UID_ITShopOrg', @UID_OrgCU_Target, 'UID_Person', o.UID_Person) , GetUTCDate(), GetUTCDate(), 'QER_PITShopShopMove', 'QER_PITShopShopMove' from PersonInITShopOrg36 o where o.UID_ITShopOrg = @uid_orgCU_Source and o.XOrigin & 1 > 0  select @UID_DynamicGroup_Source = null select @UID_DynamicGroup_Target = null select37 top 1 @UID_DynamicGroup_Source = UID_DynamicGroup from DynamicGroup where ObjectKeyBaseTree = dbo.QBM_FCVElementToObjectKey1('ITShopOrg', 'UID_ITShopOrg'38, @uid_orgCU_Source) and UID_DialogTableObjectClass = 'QER-T-Person' if @UID_DynamicGroup_Source is not null begin select @UID_DynamicGroup_Target = newid39() insert into DynamicGroup (UID_DynamicGroup, UID_DialogTableObjectClass, UID_DialogSchedule , ObjectKeyBaseTree , DisplayName, Description , WhereClause40, WhereClauseAddOn , XObjectkey , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated ) select @UID_DynamicGroup_Target, o.UID_DialogTableObjectClass41, o.UID_DialogSchedule , dbo.QBM_FCVElementToObjectKey1('ITShopOrg', 'UID_ITShopOrg', @uid_orgCU_Target) , o.DisplayName, o.Description , o.WhereClause42, o.WhereClauseAddOn , dbo.QBM_FCVElementToObjectKey1('DynamicGroup', 'UID_DynamicGroup', @UID_DynamicGroup_Target) , GetUTCDate(), GetUTCDate(), 'QER_PITShopShopMove'43, 'QER_PITShopShopMove' from DynamicGroup o where o.UID_DynamicGroup = @UID_DynamicGroup_Source         end  insert into @ElementBuffer (UID1) select bo.UID_Org44 from basetree bo join basetree sh on bo.UID_ParentOrg = sh.UID_Org where bo.ITShopInfo = 'BO' and bo.UID_OrgRoot = 'QER-V-ITShopOrg' and sh.UID_Org = 45@UID_OrgSHSource select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @UID_OrgBOSource = 46bu.UID1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex exec QER_PITShop_BoardMove @UID_OrgBOSource, @UID_OrgSH_Target select @ElementIndex47 += 1 end          delete DynamicGroup where UID_DynamicGroup = @UID_DynamicGroup_Source  update PersonInBaseTree set XOrigin = 0  , XDateUpdated = @Xdate48 , XUserUpdated = @XUser where UID_Org = @uid_orgCU_Source delete PersonInBaseTree where UID_Org = @uid_orgCU_Source delete BaseTree where UID_Org = @uid_orgCU_Source49  delete BaseTree where UID_Org = @UID_OrgSHSource END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow50() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: return end 51