· 8 years ago · Mar 28, 2018, 10:32 AM
1USE CVENT_PROD
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5SET ANSI_NULLS ON
6GO
7IF NOT EXISTS
8(SELECT *
9 FROM
10 sysobjects
11 WHERE
12 id = object_id(N'[dbo].[up_api_Product_AddRemoveSessionRegistrantsCrossAccount]')
13 AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
14EXEC ('CREATE PROCEDURE [dbo].[up_api_Product_AddRemoveSessionRegistrantsCrossAccount]
15 AS SELECT 1')
16GO
17ALTER PROCEDURE dbo.up_api_Product_AddRemoveSessionRegistrantsCrossAccount
18(
19 @CvInvitee_Xml xml, -- for selected invitees
20 @prod_stub ut_stub,
21 @action smallint,
22 @modified_by nvarchar(50)
23)
24As
25
26/*
27********************************************************************************
28**
29** Source File: up_api_Product_AddRemoveSessionRegistrantsCrossAccount.sql
30**
31** Object Name: up_api_Product_AddRemoveSessionRegistrantsCrossAccount
32**
33** Usage:
34
35**
36** Author: vpandey
37**
38** Description: A stored procedure to Add/Remove Session Registrants for Cross Account Functionality.
39
40**Note Cloned from up_api_Product_AddRemoveSessionRegistrants.sql removing check for API user as this
41** is cross acoount functionality
42
43**
44** Creation Date: 08/13/2014
45**
46********************************************************************************
47*/
48
49SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
50SET @modified_by ='CROWDCOMPASS'
51SET XACT_ABORT ON
52DECLARE @acct_id int
53--fetch account base on evt stub
54SELECT @acct_id = acct_id
55FROM
56 dbo.PRODUCT
57WHERE
58 prod_stub = @prod_stub
59
60
61 DECLARE @WorkingTableName nvarchar(50),
62 @Sql_Qry nvarchar(max),
63 @rows bigint,
64 @msg varchar(1000)
65
66 DECLARE @empty_stub ut_stub,
67 @created_by ut_medium_description,
68 @evt_stub ut_stub,
69 @ret_cd int,
70 @ret_msg nvarchar(4000),
71 @RtnCode int,
72 @ErrCode int,
73 @CreateWorkingTableSQL nvarchar(max),
74 @send_ssn_email_flag ut_flag
75
76
77 CREATE TABLE #tmpInviteeList
78 (
79 RowId INT NOT NULL IDENTITY(1,1),
80 entity_stub uniqueidentifier, --contact_stub of the invitee or free_stub
81 entity_type_id smallint, --3 or 10
82 cont_type_stub uniqueidentifier,
83 cont_stub uniqueidentifier,
84 free_stub uniqueidentifier,
85 email_addr nvarchar(80), -- ut_email
86 invitee_stub uniqueidentifier,
87 invitee_status_id smallint,
88 input_stub ut_stub, --will contain either invitee_stub or free_stub
89 Success_Flag BIT DEFAULT 1, --Indicate whether a Invitee meets validation criteria; 1-->successful
90 ValidationCode NVARCHAR(7) NULL,
91 )
92
93 CREATE TABLE #tmpInviteeAudit
94 (
95 invitee_stub ut_stub,
96 audit_stub ut_stub NULL
97 )
98
99 CREATE TABLE #tmpAttendeeEntity
100 (
101 entity_stub uniqueidentifier,
102 entity_type_id smallint,
103 prod_type_id smallint,
104 cont_type_stub uniqueidentifier,
105 prod_qty int,
106 created_date datetime,
107 bskt_dtl_stub uniqueidentifier
108 )
109
110 CREATE TABLE #tmpSSNGroupAITrackSSN
111 (
112 prod_stub ut_stub,
113 prod_type smallint --0 Session : 1 Session Group :2 Admission item/Track
114 )
115
116 /*--This table will also be used in validate SP*/
117 CREATE TABLE #tmp_Errors
118 (
119 Id NVARCHAR(36) NULL,
120 ErrorCode NVARCHAR(7) NULL,
121 ErrorDescription NVARCHAR(300) NULL
122 )
123
124 CREATE TABLE #tmp_SessionRegInvitee
125 (
126 acct_id ut_synthetic_key NOT NULL,
127 xmlData xml NULL,
128 SessionRegActionType smallint,
129 ProductId nvarchar(36) NULL,
130 )
131SELECT @empty_stub = dbo.uf_utl_EmptyStub()
132
133INSERT
134INTO
135 #tmp_SessionRegInvitee
136 (
137 acct_id
138 , xmlData
139 , SessionRegActionType
140 , ProductId
141 )
142SELECT acct_id = @acct_id
143 , x.y.query ('Invitee') AS xmlData
144 , SessionRegActionType = @action
145 , ProductId = @prod_stub
146FROM
147 @CvInvitee_Xml.nodes('/Invitees') AS x (y)
148
149
150INSERT
151INTO
152 #tmpInviteeList (
153 input_stub
154 )
155SELECT CASE x.y.value('@Id', 'NVARCHAR(MAX)')
156 WHEN '' THEN
157 @empty_stub
158 ELSE
159 x.y.value('@Id', 'NVARCHAR(MAX)')
160 END AS input_stub
161
162FROM
163 #tmp_SessionRegInvitee tep
164 CROSS APPLY xmlData.nodes('/Invitee') AS x (y)
165
166SELECT *
167FROM
168 #tmp_SessionRegInvitee
169
170SELECT *
171FROM
172 #tmpInviteeList
173
174SELECT @evt_stub = evt_stub
175FROM
176 dbo.PRODUCT
177WHERE
178 acct_id = @acct_id
179 AND prod_stub = @prod_stub
180 AND (prod_type_id = 30
181 OR prod_type_id = 10)
182
183 IF(@prod_stub=@empty_stub)
184 BEGIN
185EXEC up_cvnt_raise_error @calling_sp = 'up_api_Product_AddRemoveSessionRegistrantsCrossAccount', @message = 'INVALID_PRODUCT_ID'
186 RETURN
187 END
188
189
190UPDATE il
191SET
192 il.entity_stub = i.cont_stub,
193 il.entity_type_id = 3,
194 il.cont_type_stub = i.cont_type_stub,
195 il.invitee_stub = i.invitee_stub,
196 il.free_stub = @empty_stub,
197 il.email_addr = c.email_addr,
198 il.cont_stub = i.cont_stub,
199 il.invitee_status_id = i.invitee_status_id
200FROM
201 #tmpInviteeList il
202 JOIN dbo.INVITEE i
203 ON il.input_stub = i.invitee_stub
204 JOIN dbo.CONTACT c
205 ON i.acct_id = c.acct_id AND i.cont_stub = c.cont_stub
206WHERE
207 i.acct_id = @acct_id AND
208 i.evt_stub = @evt_stub --and at.entity_type_id = 3
209--and at.invitee_status_id = 2 -- Accepted Status
210
211
212
213UPDATE il
214SET
215 il.entity_stub = f.free_stub,
216 il.entity_type_id = 10,
217 il.cont_type_stub = f.cont_type_stub,
218 il.invitee_stub = f.invitee_stub,
219 il.free_stub = f.free_stub,
220 il.email_addr = f.free_email_addr,
221 il.cont_stub = f.cont_stub,
222 il.invitee_status_id = i.invitee_status_id
223FROM
224 #tmpInviteeList il
225 JOIN dbo.FREELOADER f
226 ON il.input_stub = f.free_stub
227 JOIN dbo.INVITEE i
228 ON f.acct_id = i.acct_id AND f.evt_stub = i.evt_stub AND f.invitee_stub = i.invitee_stub
229WHERE
230 f.acct_id = @acct_id AND
231 f.evt_stub = @evt_stub --and at.entity_type_id = 10
232-- and at.invitee_status_id = 2 --Accepted Status
233
234
235
236
237--IF @action=1 --Register
238--begin
239INSERT
240INTO
241 #tmpSSNGroupAITrackSSN
242 (
243 prod_stub
244 , prod_type
245 )
246SELECT @prod_stub AS prod_stub
247 , 0
248
249UNION ALL
250
251SELECT pp2.child_prod_stub
252 , 1
253FROM
254 dbo.PRODUCT_PRODUCT pp1
255 JOIN dbo.PRODUCT_PRODUCT pp2
256 ON pp1.acct_id = pp2.acct_id AND
257 pp1.evt_stub = pp2.evt_stub AND
258 pp1.parent_prod_stub = pp2.parent_prod_stub
259WHERE
260 pp1.acct_id = @acct_id
261 AND
262 pp1.evt_stub = @evt_stub
263 AND
264 pp1.child_prod_stub = @prod_stub
265 AND
266 pp2.child_prod_stub != @prod_stub
267 AND
268 EXISTS (SELECT 1
269 FROM
270 dbo.SESSION_GROUP sg
271 WHERE
272 sg.acct_id = @acct_id
273 AND sg.evt_stub = @evt_stub
274 AND sg.prod_stub = pp1.parent_prod_stub)
275
276UNION ALL
277--Prod issue #56705: if selected session is included in adm item/track selected by the registrant then don't allow them to register for this session again.
278SELECT pp.parent_prod_stub
279 , 2
280FROM
281 dbo.PRODUCT_PRODUCT pp
282 JOIN dbo.PRODUCT p
283 ON pp.acct_id = p.acct_id AND
284 pp.evt_stub = p.evt_stub AND
285 pp.parent_prod_stub = p.prod_stub
286WHERE
287 pp.acct_id = @acct_id
288 AND
289 pp.evt_stub = @evt_stub
290 AND
291 pp.child_prod_stub = @prod_stub
292 AND
293 p.prod_type_id IN (10, 20) --Adm Item/Track
294
295--end
296
297--==========================================VALIDATIONS=========================================
298
299-- Validating the status of Event
300UPDATE il
301SET
302 il.Success_Flag = 0,
303 il.ValidationCode = 'CV90107' --Event status us not valid
304FROM
305 #tmpInviteeList il
306WHERE
307 (il.entity_stub IS NOT NULL) AND
308 NOT EXISTS (SELECT 1
309 FROM
310 dbo.[EVENT]
311 WHERE
312 acct_id = @acct_id
313 AND
314 evt_stub = @evt_stub
315 AND
316 evt_status_id IN (1, 2, 3, 4)) AND
317 il.Success_Flag = 1
318
319
320
321
322-- Validating the status of Invitee
323UPDATE il
324SET
325 il.Success_Flag = 0,
326 il.ValidationCode = 'CV90104'
327FROM
328 #tmpInviteeList il
329WHERE
330 (il.entity_stub IS NOT NULL) AND
331 il.invitee_status_id <> 2 AND
332 il.Success_Flag = 1
333
334
335UPDATE il
336SET
337 il.Success_Flag = 0,
338 il.ValidationCode = 'CV60101' -- Id is not valid
339FROM
340 #tmpInviteeList il
341WHERE
342 (il.input_stub = @empty_stub OR
343 @prod_stub = @empty_stub) AND
344 il.Success_Flag = 1
345
346
347
348--This invitee or guest is already registered for this session.
349
350IF @action=1
351BEGIN
352
353UPDATE il
354SET
355 il.Success_Flag = 0,
356 il.ValidationCode = 'CV90105'
357FROM
358 #tmpInviteeList il
359WHERE
360 (il.entity_stub IS NOT NULL) AND
361 (
362
363 EXISTS (SELECT 1
364 FROM
365 [dbo].[PRODUCT]
366 WHERE
367 acct_id = @acct_id
368 AND
369 evt_stub = @evt_stub
370 AND
371 prod_stub = @prod_stub
372 AND
373 included_session_flag = 1)
374
375 OR
376
377 EXISTS (SELECT 1
378 FROM
379 dbo.ATTENDEE_ENTITY ae
380 WHERE
381 ae.acct_id = @acct_id
382 AND
383 ae.evt_stub = @evt_stub
384 AND
385 EXISTS (SELECT 1
386 FROM
387 #tmpSSNGroupAITrackSSN t
388 WHERE
389 t.prod_stub = ae.prod_stub
390 AND
391 t.prod_type <> 1)
392 AND
393 ae.entity_stub = il.entity_stub
394 AND
395 ae.entity_type_id = il.entity_type_id
396
397 ) OR
398 EXISTS (SELECT 1
399 FROM
400 dbo.ATTENDEE_ENTITY_WAITLIST aew
401 WHERE
402 aew.acct_id = @acct_id
403 AND
404 aew.evt_stub = @evt_stub
405 AND
406 EXISTS (SELECT 1
407 FROM
408 #tmpSSNGroupAITrackSSN t
409 WHERE
410 t.prod_stub = aew.prod_stub
411 AND
412 t.prod_type <> 1)
413 AND
414 aew.entity_stub = il.entity_stub
415 AND
416 aew.entity_type_id = il.entity_type_id
417 )
418 )
419 AND
420 il.Success_Flag = 1
421
422
423UPDATE il
424SET
425 il.Success_Flag = 0,
426 il.ValidationCode = 'CV90109'
427FROM
428 #tmpInviteeList il
429WHERE
430 (il.entity_stub IS NOT NULL) AND
431 (
432 EXISTS (SELECT 1
433 FROM
434 dbo.ATTENDEE_ENTITY ae
435 WHERE
436 ae.acct_id = @acct_id
437 AND
438 ae.evt_stub = @evt_stub
439 AND
440 EXISTS (SELECT 1
441 FROM
442 #tmpSSNGroupAITrackSSN t
443 WHERE
444 t.prod_stub = ae.prod_stub
445 AND
446 t.prod_type = 1)
447 AND
448 ae.entity_stub = il.entity_stub
449 AND
450 ae.entity_type_id = il.entity_type_id
451
452 ) OR
453 EXISTS (SELECT 1
454 FROM
455 dbo.ATTENDEE_ENTITY_WAITLIST aew
456 WHERE
457 aew.acct_id = @acct_id
458 AND
459 aew.evt_stub = @evt_stub
460 AND
461 EXISTS (SELECT 1
462 FROM
463 #tmpSSNGroupAITrackSSN t
464 WHERE
465 t.prod_stub = aew.prod_stub
466 AND
467 t.prod_type = 1)
468 AND
469 aew.entity_stub = il.entity_stub
470 AND
471 aew.entity_type_id = il.entity_type_id
472 )
473 )
474 AND
475 il.Success_Flag = 1
476
477END
478ELSE
479BEGIN
480
481--This invitee or guest is not registered for this session.
482UPDATE il
483SET
484 il.Success_Flag = 0,
485 il.ValidationCode = 'CV90106'
486FROM
487 #tmpInviteeList il
488WHERE
489 (il.entity_stub IS NOT NULL) AND
490 NOT EXISTS (SELECT 1
491 FROM
492 dbo.ATTENDEE_ENTITY ae
493 WHERE
494 ae.acct_id = @acct_id
495 AND
496 ae.evt_stub = @evt_stub
497 AND
498 EXISTS (SELECT 1
499 FROM
500 #tmpSSNGroupAITrackSSN t
501 WHERE
502 t.prod_stub = ae.prod_stub
503 AND
504 t.prod_type = 0)
505 AND
506 ae.entity_stub = il.entity_stub
507 AND
508 ae.entity_type_id = il.entity_type_id)
509 AND
510 NOT EXISTS (SELECT 1
511 FROM
512 dbo.ATTENDEE_ENTITY_WAITLIST aew
513 WHERE
514 aew.acct_id = @acct_id
515 AND
516 aew.evt_stub = @evt_stub
517 AND
518 EXISTS (SELECT 1
519 FROM
520 #tmpSSNGroupAITrackSSN t
521 WHERE
522 t.prod_stub = aew.prod_stub
523 AND
524 t.prod_type = 0)
525 AND
526 aew.entity_stub = il.entity_stub
527 AND
528 aew.entity_type_id = il.entity_type_id
529 )
530 AND
531 @action = 2 AND
532 il.Success_Flag = 1
533
534
535UPDATE il
536SET
537 il.Success_Flag = 0,
538 il.ValidationCode = 'CV90110'
539FROM
540 #tmpInviteeList il
541WHERE
542 (il.entity_stub IS NOT NULL)
543 AND
544 (
545 EXISTS (SELECT 1
546 FROM
547 dbo.ATTENDEE_ENTITY ae
548 WHERE
549 ae.acct_id = @acct_id
550 AND
551 ae.evt_stub = @evt_stub
552 AND
553 EXISTS (SELECT 1
554 FROM
555 #tmpSSNGroupAITrackSSN t
556 WHERE
557 t.prod_stub = ae.prod_stub
558 AND
559 t.prod_type = 2)
560 AND
561 ae.entity_stub = il.entity_stub
562 AND
563 ae.entity_type_id = il.entity_type_id) -- trying to unregister from a session which is part of Admission item or track
564 OR
565 EXISTS (SELECT 1
566 FROM
567 dbo.ATTENDEE_ENTITY_WAITLIST aew
568 WHERE
569 aew.acct_id = @acct_id
570 AND
571 aew.evt_stub = @evt_stub
572 AND
573 EXISTS (SELECT 1
574 FROM
575 #tmpSSNGroupAITrackSSN t
576 WHERE
577 t.prod_stub = aew.prod_stub
578 AND
579 t.prod_type = 2)
580 AND
581 aew.entity_stub = il.entity_stub
582 AND
583 aew.entity_type_id = il.entity_type_id
584 )
585
586 OR
587
588 EXISTS (SELECT 1
589 FROM
590 [dbo].[PRODUCT]
591 WHERE
592 acct_id = @acct_id
593 AND
594 evt_stub = @evt_stub
595 AND
596 prod_stub = @prod_stub
597 AND
598 included_session_flag = 1) -- trying to unregister included session
599 )
600 AND
601 @action = 2
602 AND
603 il.invitee_status_id = 2
604END
605-- Session does not belong to registrant event
606UPDATE il
607SET
608 il.Success_Flag = 0,
609 il.ValidationCode = 'CV90108'
610FROM
611 #tmpInviteeList il
612WHERE
613 (il.entity_stub IS NULL) AND
614 il.Success_Flag = 1
615
616
617
618--Populating Temp Error table based on the above validation
619INSERT
620INTO
621 #tmp_Errors
622 (
623 --acct_id,
624 Id
625 , ErrorCode
626 , ErrorDescription
627 )
628SELECT --@acct_Id,
629 il.input_stub
630 , il.ValidationCode
631 , lec.error_description
632FROM
633 #tmpInviteeList il, dbo.LU_API_ERROR_CODE lec
634WHERE
635 il.ValidationCode = lec.error_code
636 AND
637 il.Success_Flag = 0
638ORDER BY
639 il.RowId
640
641
642
643IF NOT EXISTS (SELECT 1
644 FROM
645 #tmpInviteeList
646 WHERE
647 success_flag = 1)
648 BEGIN
649 GOTO ResultHndlr
650 End
651--=====================================End of Validations========================================
652
653
654BEGIN TRY
655
656 IF @action=1 --Register
657 begin
658
659
660
661INSERT
662INTO
663 #tmpAttendeeEntity
664 (
665 entity_stub
666 , entity_type_id
667 , prod_type_id
668 , cont_type_stub
669 , prod_qty
670 , created_date
671 , bskt_dtl_stub
672 )
673SELECT --Waitlisted Invitees
674 aew.entity_stub
675 , aew.entity_type_id
676 , aew.prod_type_id
677 , il.cont_type_stub
678 , 1
679 , getdate()
680 , @empty_stub bskt_dtl_stub
681FROM
682 #tmpInviteeList il
683 JOIN dbo.ATTENDEE_ENTITY_WAITLIST aew
684 ON aew.acct_id = @acct_id AND
685 aew.evt_stub = @evt_stub AND
686 EXISTS (SELECT 1
687 FROM
688 #tmpSSNGroupAITrackSSN t
689 WHERE
690 t.prod_stub = aew.prod_stub) AND
691 il.entity_stub = aew.entity_stub AND
692 il.entity_type_id = aew.entity_type_id AND il.Success_Flag = 1
693
694UNION ALL
695
696SELECT --Invitees not registered for this session
697 il.entity_stub
698 , il.entity_type_id
699 , 30
700 , --session prod type id
701 il.cont_type_stub
702 , 1
703 , getdate()
704 , @empty_stub bskt_dtl_stub
705FROM
706 #tmpInviteeList il
707WHERE
708 NOT EXISTS (SELECT 1
709 FROM
710 dbo.ATTENDEE_ENTITY ae
711 WHERE
712 ae.acct_id = @acct_id
713 AND ae.evt_stub = @evt_stub
714 AND EXISTS (SELECT 1
715 FROM
716 #tmpSSNGroupAITrackSSN t
717 WHERE
718 t.prod_stub = ae.prod_stub)
719 AND ae.entity_stub = il.entity_stub
720 AND ae.entity_type_id = il.entity_type_id
721
722 )
723 AND
724 NOT EXISTS (SELECT 1
725 FROM
726 dbo.ATTENDEE_ENTITY_WAITLIST aew
727 WHERE
728 aew.acct_id = @acct_id
729 AND aew.evt_stub = @evt_stub
730 AND EXISTS (SELECT 1
731 FROM
732 #tmpSSNGroupAITrackSSN t
733 WHERE
734 t.prod_stub = aew.prod_stub)
735 AND aew.entity_stub = il.entity_stub
736 AND aew.entity_type_id = il.entity_type_id
737 )
738 AND il.Success_Flag = 1
739
740 BEGIN TRAN
741
742INSERT
743INTO
744 dbo.ATTENDEE_ENTITY
745 (
746 acct_id
747 , entity_stub
748 , entity_type_id
749 , prod_stub
750 , prod_type_id
751 , cont_type_stub
752 , prod_qty
753 , created_date
754 , bskt_dtl_stub
755 , evt_stub
756 )
757SELECT @acct_id
758 , entity_stub
759 , entity_type_id
760 , @prod_stub
761 , prod_type_id
762 , cont_type_stub
763 , prod_qty
764 , created_date
765 , bskt_dtl_stub
766 , @evt_stub
767FROM
768 #tmpAttendeeEntity
769
770SET @rows = @@ROWCOUNT
771
772
773UPDATE [dbo].[PRODUCT_CAPACITY_CONTROL]
774SET
775 prod_registered_cnt = prod_registered_cnt + @rows
776WHERE
777 acct_id = @acct_id AND
778 prod_stub = @prod_stub
779
780--set context_info to suppress the invitee update trigger to avoid insertion in Invitee_Action_Audit.
781 --Explicitly take care of INVITEE_ACTION_AUDIT at end
782SET context_info 0x1010
783
784UPDATE i
785SET
786 i.registration_last_modified_date = getdate(),
787 i.registration_last_modified_by = @modified_by,
788 i.last_modified_date = getdate(),
789 i.last_modified_by = @modified_by
790FROM
791 [dbo].[INVITEE] i
792 JOIN #tmpInviteeList il
793 ON i.acct_id = @acct_id
794 AND i.invitee_stub = il.invitee_stub
795 JOIN #tmpAttendeeEntity t
796 ON il.entity_stub = t.entity_stub
797WHERE
798 il.Success_Flag = 1
799
800--reset context_info. We just want to supress invitee trigger.
801SET context_info 0x
802
803UPDATE f
804SET
805 f.last_modified_date = getdate(),
806 f.last_modified_by = @modified_by
807FROM
808 [dbo].[FREELOADER] f
809 JOIN #tmpInviteeList il
810 ON f.acct_id = @acct_id
811 AND f.free_stub = il.free_stub
812 JOIN #tmpAttendeeEntity t
813 ON il.entity_stub = t.entity_stub
814WHERE
815 il.free_stub <> @empty_stub AND
816 il.Success_Flag = 1
817--Delete waitlisted invitees for this session as in case of Register they have been moved to ATTENDEE_ENTITY
818--and in case of Unregister they should be simply deleted from ATTENDEE_ENTITY_WAITLIST
819DELETE aew
820FROM
821 #tmpInviteeList il
822 JOIN dbo.ATTENDEE_ENTITY_WAITLIST aew
823 ON il.entity_stub = aew.entity_stub AND
824 il.entity_type_id = aew.entity_type_id
825WHERE
826 aew.acct_id = @acct_id
827 AND
828 aew.evt_stub = @evt_stub
829 AND
830 aew.prod_stub = @prod_stub
831 AND
832 il.Success_Flag = 1
833 COMMIT TRAN
834
835 end
836 ELSE --UnRegister
837 begin
838INSERT
839INTO
840 #tmpAttendeeEntity
841 (
842 entity_stub
843 , entity_type_id
844 , prod_type_id
845 , cont_type_stub
846 , prod_qty
847 , created_date
848 , bskt_dtl_stub
849 )
850SELECT ae.entity_stub
851 , ae.entity_type_id
852 , ae.prod_type_id
853 , ae.cont_type_stub
854 , ae.prod_qty
855 , ae.created_date
856 , ae.bskt_dtl_stub
857FROM
858 #tmpInviteeList il
859 JOIN dbo.ATTENDEE_ENTITY ae
860 ON il.entity_stub = ae.entity_stub AND
861 il.entity_type_id = ae.entity_type_id
862WHERE
863 ae.acct_id = @acct_id
864 AND ae.evt_stub = @evt_stub
865 AND ae.prod_stub = @prod_stub
866 AND il.Success_Flag = 1
867
868--insert attendees in AEW (will be used to sent session emails later)
869--TODO: Probably need to remove this.
870INSERT
871INTO
872 #tmpAttendeeEntity
873 (
874 entity_stub
875 , entity_type_id
876 , prod_type_id
877 )
878SELECT aew.entity_stub
879 , aew.entity_type_id
880 , aew.prod_type_id
881FROM
882 #tmpInviteeList il
883 JOIN dbo.ATTENDEE_ENTITY_WAITLIST aew
884 ON il.entity_stub = aew.entity_stub AND
885 il.entity_type_id = aew.entity_type_id
886WHERE
887 aew.acct_id = @acct_id
888 AND aew.evt_stub = @evt_stub
889 AND aew.prod_stub = @prod_stub
890 AND il.Success_Flag = 1
891
892 BEGIN TRAN
893
894DELETE ae
895FROM
896 #tmpInviteeList il
897 JOIN dbo.ATTENDEE_ENTITY ae
898 ON il.entity_stub = ae.entity_stub AND
899 il.entity_type_id = ae.entity_type_id
900WHERE
901 ae.acct_id = @acct_id
902 AND
903 ae.evt_stub = @evt_stub
904 AND
905 ae.prod_stub = @prod_stub
906 AND
907 il.Success_Flag = 1
908
909SET @rows = @@ROWCOUNT
910
911UPDATE [dbo].[PRODUCT_CAPACITY_CONTROL]
912SET
913 prod_registered_cnt = prod_registered_cnt - @rows
914WHERE
915 acct_id = @acct_id AND
916 prod_stub = @prod_stub
917
918--set context_info to suppress the invitee update trigger to avoid insertion in Invitee_Action_Audit.
919 --Explicitly take care of INVITEE_ACTION_AUDIT at end
920SET context_info 0x1010
921
922UPDATE i
923SET
924 i.registration_last_modified_date = getdate(),
925 i.registration_last_modified_by = @modified_by,
926 i.last_modified_date = getdate(),
927 i.last_modified_by = @modified_by
928FROM
929 [dbo].[INVITEE] i
930 JOIN #tmpInviteeList il
931 ON i.acct_id = @acct_id
932 AND i.invitee_stub = il.invitee_stub
933 JOIN #tmpAttendeeEntity t
934 ON il.entity_stub = t.entity_stub
935WHERE
936 il.Success_Flag = 1
937
938--reset context_info. We just want to supress invitee trigger.
939SET context_info 0x
940
941UPDATE f
942SET
943 f.last_modified_date = getdate(),
944 f.last_modified_by = @modified_by
945FROM
946 [dbo].[FREELOADER] f
947 JOIN #tmpInviteeList il
948 ON f.acct_id = @acct_id
949 AND f.free_stub = il.free_stub
950 JOIN #tmpAttendeeEntity t
951 ON il.entity_stub = t.entity_stub
952WHERE
953 il.free_stub <> @empty_stub AND
954 il.Success_Flag = 1
955--Delete waitlisted invitees for this session as in case of Register they have been moved to ATTENDEE_ENTITY
956--and in case of Unregister they should be simply deleted from ATTENDEE_ENTITY_WAITLIST
957DELETE aew
958FROM
959 #tmpInviteeList il
960 JOIN dbo.ATTENDEE_ENTITY_WAITLIST aew
961 ON il.entity_stub = aew.entity_stub AND
962 il.entity_type_id = aew.entity_type_id
963WHERE
964 aew.acct_id = @acct_id
965 AND
966 aew.evt_stub = @evt_stub
967 AND
968 aew.prod_stub = @prod_stub
969 AND
970 il.Success_Flag = 1
971
972
973 COMMIT TRAN --commit tran of ELSE
974
975 END
976
977 INSERT INTO #tmpInviteeAudit (invitee_stub)
978 SELECT distinct invitee_stub
979 FROM #tmpInviteeList
980 WHERE Success_Flag = 1
981
982 UPDATE #tmpInviteeAudit
983 SET audit_stub = newid()
984
985 --Insert Audit information for all invitees in bulk
986 INSERT INTO [CVENT_PROD].[dbo].[INVITEE_ACTION_AUDIT]
987 ([audit_stub]
988 ,[acct_id]
989 ,[invitee_stub]
990 ,[evt_stub]
991 ,[invitee_action_id]
992 ,[created_date]
993 ,[created_by])
994 SELECT
995 ia.audit_stub,
996 @acct_id,
997 ia.invitee_stub,
998 @evt_stub,
999 7, --Modified
1000 getdate(),
1001 @modified_by
1002 FROM #tmpInviteeAudit ia
1003
1004 INSERT INTO [CVENT_PROD].[dbo].[INVITEE_ACTION_AUDIT_DETAIL]
1005 ([audit_detail_stub],
1006 [acct_id],
1007 [evt_stub],
1008 [invitee_stub],
1009 [audit_stub],
1010 [invitee_action_change_type_id],
1011 [entity_stub],
1012 [entity_type_id],
1013 [item_entity_stub],
1014 [item_entity_type_id],
1015 [old_val],
1016 [new_val])
1017 SELECT
1018 newid() as audit_detail_stub,
1019 @acct_id as acct_id,
1020 @evt_stub as evt_stub,
1021 il.invitee_stub as invitee_stub,
1022 ia.audit_stub as audit_stub,
1023 CASE WHEN @action = 1 THEN 4 ELSE 5 END as invitee_action_change_type_id, --PRODUCT ADDED/ITEM REMOVED
1024 il.entity_stub as entity_stub,
1025 il.entity_type_id as entity_type_id,
1026 @prod_stub as item_entity_stub,
1027 13 as item_entity_type_id, -- session
1028 '' as old_val,
1029 '' as new_val
1030 FROM #tmpInviteeList il
1031 join #tmpInviteeAudit ia
1032 on il.invitee_stub = ia.invitee_stub
1033 WHERE il.Success_Flag = 1
1034
1035END TRY
1036BEGIN CATCH
1037SELECT @ErrCode = @@error
1038 GoTo ErrExit
1039END CATCH
1040
1041 --send session emails if required
1042 IF @send_ssn_email_flag = 1
1043 Begin
1044--Delete entities from invitee list when they are neither registered nor unregistered for this session.
1045--e.g. in case of Register : Already registered will be ignored and vice versa.
1046DELETE il
1047FROM
1048 #tmpInviteeList il
1049 LEFT JOIN #tmpAttendeeEntity ae
1050 ON il.entity_stub = ae.entity_stub AND
1051 il.entity_type_id = ae.entity_type_id
1052WHERE
1053 ae.entity_stub IS NULL AND
1054 il.Success_Flag = 1
1055
1056
1057--create a working table to be used in Async SP through Service Broker.
1058SELECT @WorkingTableName = work_table_db_name + '.dbo.WT_SEM' + replace(newid(), '-', '')
1059FROM
1060 dbo.[LU_WORK_TABLE_DB]
1061WHERE
1062 active_flag = 1
1063
1064
1065SELECT @CreateWorkingTableSQL = 'create table #WT_SRC#
1066 (
1067 cont_stub uniqueidentifier,
1068 free_stub uniqueidentifier,
1069 email_addr nvarchar(80),
1070 invitee_stub uniqueidentifier,
1071 entity_type_id smallint
1072 )
1073
1074 insert into #WT_SRC#
1075 select cont_stub,free_stub,email_addr,invitee_stub,entity_type_id
1076 from #tmpInviteeList where Success_Flag = 1
1077
1078 '
1079--provide the working table name
1080SELECT @CreateWorkingTableSQL = replace(@CreateWorkingTableSQL, '#WT_SRC#', @WorkingTableName)
1081
1082EXEC sp_executesql @CreateWorkingTableSQL
1083
1084-- Send email is async process
1085EXEC [dbo].[up_cvnt_Product_AddRemoveSessionRegistrants_SendEmail_Async] @acct_id, @evt_stub, @prod_stub, @action, @WorkingTableName--@XmlInviteeList
1086 goto ResultHndlr
1087 End--end if
1088
1089
1090 ResultHndlr:
1091
1092
1093-- Generating Result object
1094
1095
1096SELECT CASE il.Success_Flag
1097 WHEN 1 THEN
1098 cast(il.input_stub AS NVARCHAR(36))
1099 ELSE
1100 ''
1101 END AS '@Id'
1102 , il.Success_Flag AS '@Success'
1103 , (
1104 SELECT (
1105 SELECT ErrorCode AS '@Code'
1106 , ErrorDescription AS '@Description'
1107 FROM
1108 #tmp_Errors te
1109 WHERE
1110
1111 te.Id = il.input_stub
1112 FOR XML
1113 PATH ('Error'), TYPE
1114 )
1115 FOR XML
1116 PATH ('Errors'), TYPE
1117 )
1118FROM
1119 #tmpInviteeList il
1120ORDER BY
1121 il.input_stub
1122FOR XML
1123 PATH ('SessionRegActionResult'), ROOT ('SessionRegActionResultArray')
1124
1125 Goto OkExit
1126
1127 ErrExit: -- for system errors from insert/update/delete/constraint
1128 IF (@@trancount > 0)
1129 Begin
1130 ROLLBACK TRAN
1131 End
1132
1133SET @msg = error_message()
1134EXEC dbo.up_cvnt_raise_error 'dbo.up_api_Product_AddRemoveSessionRegistrantsCrossAccount', @msg --'Exception occurred at up_ess_SendTemplatedEmailBatch'
1135 return(@ErrCode)
1136 OkExit:
1137 return(0)
1138GO