Project

General

Profile

Download (27.3 KB)

Feature #3646 » CBKT_GetOrgInOrgWideDetail_202507171112.sql

Tri Rizqiaty, 07/17/2025 02:14 PM

 
1 
??ALTER FUNCTION [dbo].[CBKT_GetOrgInOrgWideDetail]
2
3 
	(@org_id VARCHAR(20),
4
5 
	@ref_date VARCHAR(20),
6
7 
	--@landscape varchar(3),
8
9 
	@is_start bit,
10
11 
	@org_name varchar(250),
12
13 
	@org_level varchar(20),
14
15 
	@org_id_00 varchar(20),
16
17 
	@org_name_00 varchar(250),
18
19 
	@org_id_01 varchar(20),
20
21 
	@org_name_01 varchar(250),
22
23 
	@org_id_02 varchar(20),
24
25 
	@org_name_02 varchar(250),
26
27 
	@org_id_03 varchar(20),
28
29 
	@org_name_03 varchar(250),
30
31 
	@org_id_04 varchar(20),
32
33 
	@org_name_04 varchar(250),
34
35 
	@org_id_05 varchar(20),
36
37 
	@org_name_05 varchar(250),
38
39 
	@org_id_06 varchar(20),
40
41 
	@org_name_06 varchar(250),
42
43 
	@org_id_07 varchar(20),
44
45 
	@org_name_07 varchar(250),
46
47 
	@org_id_08 varchar(20),
48
49 
	@org_name_08 varchar(250),
50
51 
	@org_id_09 varchar(20),
52
53 
	@org_name_09 varchar(250),
54
55 
	@org_id_10 varchar(20),
56
57 
	@org_name_10 varchar(250),
58
59 
	@org_id_11 varchar(20),
60
61 
	@org_name_11 varchar(250),
62
63 
	@org_id_12 VARCHAR(20),
64
65 
	@org_name_12 varchar(250)
66
67 
	)
68
69 
RETURNS @result TABLE (
70
71 
	org_id varchar(20),
72
73 
	org_name varchar(250),
74
75 
	org_level varchar(20),
76
77 
	parent_org_id varchar(20),
78
79 
	parent_org_name varchar(250),
80
81 
	parent_org_level varchar(20),
82
83 
	org_id_00 varchar(20),
84
85 
	org_name_00 varchar(250),
86
87 
	org_id_01 varchar(20),
88
89 
	org_name_01 varchar(250),
90
91 
	org_id_02 varchar(20),
92
93 
	org_name_02 varchar(250),
94
95 
	org_id_03 varchar(20),
96
97 
	org_name_03 varchar(250),
98
99 
	org_id_04 varchar(20),
100
101 
	org_name_04 varchar(250),
102
103 
	org_id_05 varchar(20),
104
105 
	org_name_05 varchar(250),
106
107 
	org_id_06 varchar(20),
108
109 
	org_name_06 varchar(250),
110
111 
	org_id_07 varchar(20),
112
113 
	org_name_07 varchar(250),
114
115 
	org_id_08 varchar(20),
116
117 
	org_name_08 varchar(250),
118
119 
	org_id_09 varchar(20),
120
121 
	org_name_09 varchar(250),
122
123 
	org_id_10 varchar(20),
124
125 
	org_name_10 varchar(250),
126
127 
	org_id_11 varchar(20),
128
129 
	org_name_11 varchar(250),
130
131 
	org_id_12 VARCHAR(20),
132
133 
	org_name_12 varchar(250)
134
135 
	)
136
137 
/*
138
139 
SELECT * FROM dbo.GetOrgInOrgWide('00000411','20110725','100')
140
141 
*/
142
143 
BEGIN
144
145
146
147 
	DECLARE @result_parent TABLE (
148
149 
		org_id varchar(20),
150
151 
		org_name varchar(250),
152
153 
		org_level varchar(20),
154
155 
		parent_org_id varchar(20),
156
157 
		parent_org_name varchar(250),
158
159 
		parent_org_level varchar(20),
160
161 
		org_id_00 varchar(20),
162
163 
		org_name_00 varchar(250),
164
165 
		org_id_01 varchar(20),
166
167 
		org_name_01 varchar(250),
168
169 
		org_id_02 varchar(20),
170
171 
		org_name_02 varchar(250),
172
173 
		org_id_03 varchar(20),
174
175 
		org_name_03 varchar(250),
176
177 
		org_id_04 varchar(20),
178
179 
		org_name_04 varchar(250),
180
181 
		org_id_05 varchar(20),
182
183 
		org_name_05 varchar(250),
184
185 
		org_id_06 varchar(20),
186
187 
		org_name_06 varchar(250),
188
189 
		org_id_07 varchar(20),
190
191 
		org_name_07 varchar(250),
192
193 
		org_id_08 varchar(20),
194
195 
		org_name_08 varchar(250),
196
197 
		org_id_09 varchar(20),
198
199 
		org_name_09 varchar(250),
200
201 
		org_id_10 varchar(20),
202
203 
		org_name_10 varchar(250),
204
205 
		org_id_11 varchar(20),
206
207 
		org_name_11 varchar(250),
208
209 
		org_id_12 VARCHAR(20),
210
211 
		org_name_12 varchar(250))
212
213
214
215 
	DECLARE @result_child TABLE (
216
217 
		org_id varchar(20),
218
219 
		org_name varchar(250),
220
221 
		org_level varchar(20),
222
223 
		parent_org_id varchar(20),
224
225 
		parent_org_name varchar(250),
226
227 
		parent_org_level varchar(20),
228
229 
		org_id_00 varchar(20),
230
231 
		org_name_00 varchar(250),
232
233 
		org_id_01 varchar(20),
234
235 
		org_name_01 varchar(250),
236
237 
		org_id_02 varchar(20),
238
239 
		org_name_02 varchar(250),
240
241 
		org_id_03 varchar(20),
242
243 
		org_name_03 varchar(250),
244
245 
		org_id_04 varchar(20),
246
247 
		org_name_04 varchar(250),
248
249 
		org_id_05 varchar(20),
250
251 
		org_name_05 varchar(250),
252
253 
		org_id_06 varchar(20),
254
255 
		org_name_06 varchar(250),
256
257 
		org_id_07 varchar(20),
258
259 
		org_name_07 varchar(250),
260
261 
		org_id_08 varchar(20),
262
263 
		org_name_08 varchar(250),
264
265 
		org_id_09 varchar(20),
266
267 
		org_name_09 varchar(250),
268
269 
		org_id_10 varchar(20),
270
271 
		org_name_10 varchar(250),
272
273 
		org_id_11 varchar(20),
274
275 
		org_name_11 varchar(250),
276
277 
		org_id_12 VARCHAR(20),
278
279 
		org_name_12 varchar(250))
280
281
282
283 
	DECLARE @_org_name varchar(250),
284
285 
		@_org_level varchar(20),
286
287 
		@_org_id_00 varchar(20),
288
289 
		@_org_name_00 varchar(250),
290
291 
		@_org_id_01 varchar(20),
292
293 
		@_org_name_01 varchar(250),
294
295 
		@_org_id_02 varchar(20),
296
297 
		@_org_name_02 varchar(250),
298
299 
		@_org_id_03 varchar(20),
300
301 
		@_org_name_03 varchar(250),
302
303 
		@_org_id_04 varchar(20),
304
305 
		@_org_name_04 varchar(250),
306
307 
		@_org_id_05 varchar(20),
308
309 
		@_org_name_05 varchar(250),
310
311 
		@_org_id_06 varchar(20),
312
313 
		@_org_name_06 varchar(250),
314
315 
		@_org_id_07 varchar(20),
316
317 
		@_org_name_07 varchar(250),
318
319 
		@_org_id_08 varchar(20),
320
321 
		@_org_name_08 varchar(250),
322
323 
		@_org_id_09 varchar(20),
324
325 
		@_org_name_09 varchar(250),
326
327 
		@_org_id_10 varchar(20),
328
329 
		@_org_name_10 varchar(250),
330
331 
		@_org_id_11 varchar(20),
332
333 
		@_org_name_11 varchar(250),
334
335 
		@_org_id_12 VARCHAR(20),
336
337 
		@_org_name_12 varchar(250)
338
339
340
341
342
343 
	IF (@is_start = 1)
344
345 
	BEGIN
346
347 
		INSERT INTO @result_parent
348
349 
		SELECT org.EndDate, org.ObjectDescription, orgd.OrganizationLevel, '','', '',
350
351 
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectID ELSE '' END,
352
353 
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectDescription ELSE '' END,
354
355 
			'',
356
357 
			'',
358
359 
			'',
360
361 
			'',
362
363 
			'',
364
365 
			'',
366
367 
			'',
368
369 
			'',
370
371 
			'',
372
373 
			'',
374
375 
			'',
376
377 
			'',
378
379 
			'',
380
381 
			'',
382
383 
			'',
384
385 
			''
386
387 
			,''
388
389 
			,''
390
391 
			,''
392
393 
			,''
394
395 
			,''
396
397 
			,''
398
399 
			,''
400
401 
			,''
402
403 
		FROM phrom0001 org
404
405 
			INNER JOIN phrom0004 orgd
406
407 
				ON org.ObjectID = orgd.ObjectID
408
409 
		WHERE
410
411 
			org.ObjectClass = 'O'
412
413 
			AND org.ObjectID = @org_id
414
415 
			AND org.StartDate <= @ref_date
416
417 
			AND org.EndDate >= @ref_date
418
419 
			--AND orgd.landscape = @landscape
420
421 
			AND orgd.ObjectID = @org_id
422
423 
			AND orgd.StartDate <= @ref_date
424
425 
			AND orgd.EndDate >= @ref_date
426
427
428
429 
		SELECT TOP 1 @_org_name = org_name, @_org_level = org_level
430
431 
		FROM @result_parent;
432
433
434
435 
		IF @_org_level = '00' SELECT @_org_id_00 = @org_id, @_org_name_00 = @_org_name
436
437 
		IF @_org_level = '01' SELECT @_org_id_01 = @org_id, @_org_name_01 = @_org_name
438
439 
		IF @_org_level = '02' SELECT @_org_id_02 = @org_id, @_org_name_02 = @_org_name
440
441 
		IF @_org_level = '03' SELECT @_org_id_03 = @org_id, @_org_name_03 = @_org_name
442
443 
		IF @_org_level = '04' SELECT @_org_id_04 = @org_id, @_org_name_04 = @_org_name
444
445 
		IF @_org_level = '05' SELECT @_org_id_05 = @org_id, @_org_name_05 = @_org_name
446
447 
		IF @_org_level = '06' SELECT @_org_id_06 = @org_id, @_org_name_06 = @_org_name
448
449 
		IF @_org_level = '07' SELECT @_org_id_07 = @org_id, @_org_name_07 = @_org_name
450
451 
		IF @_org_level = '08' SELECT @_org_id_08 = @org_id, @_org_name_08 = @_org_name
452
453 
		IF @_org_level = '09' SELECT @_org_id_09 = @org_id, @_org_name_09 = @_org_name
454
455 
		IF @_org_level = '10' SELECT @_org_id_10 = @org_id, @_org_name_10 = @_org_name
456
457 
		IF @_org_level = '11' SELECT @_org_id_11 = @org_id, @_org_name_11 = @_org_name
458
459 
		IF @_org_level = '12' SELECT @_org_id_12 = @org_id, @_org_name_12 = @_org_name
460
461
462
463 
		INSERT INTO @result
464
465 
		SELECT * FROM @result_parent
466
467 
	END
468
469 
	ELSE
470
471 
	BEGIN
472
473 
		SET @_org_name = @org_name
474
475 
		SET @_org_level = @org_level
476
477 
		SET @_org_id_00 = @org_id_00
478
479 
		SET @_org_name_00 = @org_name_00
480
481 
		SET @_org_id_01 = @org_id_01
482
483 
		SET @_org_name_01 = @org_name_01
484
485 
		SET @_org_id_02 = @org_id_02
486
487 
		SET @_org_name_02 = @org_name_02
488
489 
		SET @_org_id_03 = @org_id_03
490
491 
		SET @_org_name_03 = @org_name_03
492
493 
		SET @_org_id_04 = @org_id_04
494
495 
		SET @_org_name_04 = @org_name_04
496
497 
		SET @_org_id_05 = @org_id_05
498
499 
		SET @_org_name_05 = @org_name_05
500
501 
		SET @_org_id_06 = @org_id_06
502
503 
		SET @_org_name_06 = @org_name_06
504
505 
		SET @_org_id_07 = @org_id_07
506
507 
		SET @_org_name_07 = @org_name_07
508
509 
		SET @_org_id_08 = @org_id_08
510
511 
		SET @_org_name_08 = @org_name_08
512
513 
		SET @_org_id_09 = @org_id_09
514
515 
		SET @_org_name_09 = @org_name_09
516
517 
		SET @_org_id_10 = @org_id_10
518
519 
		SET @_org_name_10 = @org_name_10
520
521 
		SET @_org_id_11 = @org_id_11
522
523 
		SET @_org_name_11 = @org_name_11
524
525 
		SET @_org_id_12 = @org_id_12
526
527 
		SET @_org_name_12 = @org_name_12
528
529 
	END
530
531
532
533 
	DECLARE
534
535 
		@__org_id varchar(20),
536
537 
		@__org_name varchar(250),
538
539 
		@__org_level varchar(20),
540
541 
		@__parent_org_id varchar(20),
542
543 
		@__parent_org_name varchar(250),
544
545 
		@__parent_org_level varchar(20),
546
547 
		@__org_id_00 varchar(20),
548
549 
		@__org_name_00 varchar(250),
550
551 
		@__org_id_01 varchar(20),
552
553 
		@__org_name_01 varchar(250),
554
555 
		@__org_id_02 varchar(20),
556
557 
		@__org_name_02 varchar(250),
558
559 
		@__org_id_03 varchar(20),
560
561 
		@__org_name_03 varchar(250),
562
563 
		@__org_id_04 varchar(20),
564
565 
		@__org_name_04 varchar(250),
566
567 
		@__org_id_05 varchar(20),
568
569 
		@__org_name_05 varchar(250),
570
571 
		@__org_id_06 varchar(20),
572
573 
		@__org_name_06 varchar(250),
574
575 
		@__org_id_07 varchar(20),
576
577 
		@__org_name_07 varchar(250),
578
579 
		@__org_id_08 varchar(20),
580
581 
		@__org_name_08 varchar(250),
582
583 
		@__org_id_09 varchar(20),
584
585 
		@__org_name_09 varchar(250),
586
587 
		@__org_id_10 varchar(20),
588
589 
		@__org_name_10 varchar(250),
590
591 
		@__org_id_11 varchar(20),
592
593 
		@__org_name_11 varchar(250),
594
595 
		@__org_id_12 VARCHAR(20),
596
597 
		@__org_name_12 varchar(250)
598
599
600
601 
	DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
602
603 
	FOR
604
605 
		SELECT org.ObjectID, org.[ObjectDescription], orgd.OrganizationLevel,
606
607 
			@org_id,
608
609 
			@_org_name,
610
611 
			@_org_level,
612
613 
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectID ELSE @_org_id_00 END,
614
615 
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectDescription ELSE @_org_name_00 END,
616
617 
			CASE WHEN orgd.OrganizationLevel = '01' THEN org.ObjectID ELSE @_org_id_01 END,
618
619 
			CASE WHEN orgd.OrganizationLevel = '01' THEN org.ObjectDescription ELSE @_org_name_01 END,
620
621 
			CASE WHEN orgd.OrganizationLevel = '02' THEN org.ObjectID ELSE @_org_id_02 END,
622
623 
			CASE WHEN orgd.OrganizationLevel = '02' THEN org.ObjectDescription ELSE @_org_name_02 END,
624
625 
			CASE WHEN orgd.OrganizationLevel = '03' THEN org.ObjectID ELSE @_org_id_03 END,
626
627 
			CASE WHEN orgd.OrganizationLevel = '03' THEN org.ObjectDescription ELSE @_org_name_03 END,
628
629 
			CASE WHEN orgd.OrganizationLevel = '04' THEN org.ObjectID ELSE @_org_id_04 END,
630
631 
			CASE WHEN orgd.OrganizationLevel = '04' THEN org.ObjectDescription ELSE @_org_name_04 END,
632
633 
			CASE WHEN orgd.OrganizationLevel = '05' THEN org.ObjectID ELSE @_org_id_05 END,
634
635 
			CASE WHEN orgd.OrganizationLevel = '05' THEN org.ObjectDescription ELSE @_org_name_05 END,
636
637 
			CASE WHEN orgd.OrganizationLevel = '06' THEN org.ObjectID ELSE @_org_id_06 END,
638
639 
			CASE WHEN orgd.OrganizationLevel = '06' THEN org.ObjectDescription ELSE @_org_name_06 END,
640
641 
			CASE WHEN orgd.OrganizationLevel = '07' THEN org.ObjectID ELSE @_org_id_07 END,
642
643 
			CASE WHEN orgd.OrganizationLevel = '07' THEN org.ObjectDescription ELSE @_org_name_07 END,
644
645 
			CASE WHEN orgd.OrganizationLevel = '08' THEN org.ObjectID ELSE @_org_id_08 END,
646
647 
			CASE WHEN orgd.OrganizationLevel = '08' THEN org.ObjectDescription ELSE @_org_name_08 END,
648
649 
			CASE WHEN orgd.OrganizationLevel = '09' THEN org.ObjectID ELSE @_org_id_09 END,
650
651 
			CASE WHEN orgd.OrganizationLevel = '09' THEN org.ObjectDescription ELSE @_org_name_09 END,
652
653 
			CASE WHEN orgd.OrganizationLevel = '10' THEN org.ObjectID ELSE @_org_id_10 END,
654
655 
			CASE WHEN orgd.OrganizationLevel = '10' THEN org.ObjectDescription ELSE @_org_name_10 END,
656
657 
			CASE WHEN orgd.OrganizationLevel = '11' THEN org.ObjectID ELSE @_org_id_11 END,
658
659 
			CASE WHEN orgd.OrganizationLevel = '11' THEN org.ObjectDescription ELSE @_org_name_11 END,
660
661 
			CASE WHEN orgd.OrganizationLevel = '12' THEN org.ObjectID ELSE @_org_id_12 END,
662
663 
			CASE WHEN orgd.OrganizationLevel = '12' THEN org.ObjectDescription ELSE @_org_name_12 END
664
665 
		FROM phrom0002 rel
666
667 
			INNER JOIN phrom0001 org
668
669 
				ON rel.RelationShipObject = org.ObjectID
670
671 
			INNER JOIN phrom0004 orgd
672
673 
				ON org.ObjectID = orgd.ObjectID
674
675 
		WHERE
676
677 
			rel.ObjectClass = 'O'
678
679 
			AND rel.ObjectID = @org_id
680
681 
			AND rel.RelationShipType = '001'
682
683 
			AND rel.RelationShipDirectory = 'A'
684
685 
			AND rel.StartDate <= @ref_date
686
687 
			AND rel.EndDate >= @ref_date
688
689 
			AND rel.RelationShipClass = 'O'
690
691
692
693 
			--AND org.landscape = @landscape
694
695 
			AND org.ObjectClass = 'O'
696
697 
			AND org.StartDate <= @ref_date
698
699 
			AND org.EndDate >= @ref_date
700
701
702
703 
			--AND orgd.landscape = @landscape
704
705 
			AND orgd.StartDate <= @ref_date
706
707 
			AND orgd.EndDate >= @ref_date
708
709
710
711 
	OPEN cur
712
713
714
715
716
717 
	DECLARE @fetch_status INT
718
719 
	SET @fetch_status = 0
720
721 
	WHILE @fetch_status = 0
722
723 
	BEGIN
724
725 
		FETCH NEXT FROM cur INTO @__org_id, @__org_name, @__org_level,
726
727 
			@__parent_org_id, @__parent_org_name, @__parent_org_level,
728
729 
			@__org_id_00, @__org_name_00,
730
731 
			@__org_id_01, @__org_name_01,
732
733 
			@__org_id_02, @__org_name_02,
734
735 
			@__org_id_03, @__org_name_03,
736
737 
			@__org_id_04, @__org_name_04,
738
739 
			@__org_id_05, @__org_name_05,
740
741 
			@__org_id_06, @__org_name_06,
742
743 
			@__org_id_07, @__org_name_07,
744
745 
			@__org_id_08, @__org_name_08,
746
747 
			@__org_id_09, @__org_name_09,
748
749 
			@__org_id_10, @__org_name_10,
750
751 
			@__org_id_11, @__org_name_11,
752
753 
			@__org_id_12, @__org_name_12
754
755
756
757 
		SET @fetch_status = @@FETCH_STATUS
758
759 
		IF (@fetch_status = 0)
760
761 
		BEGIN
762
763 
			INSERT INTO @result_child VALUES (@__org_id, @__org_name, @__org_level, --@__org_name, @__org_level,
764
765 
			@__parent_org_id, @__parent_org_name, @__parent_org_level,
766
767 
			@__org_id_00, @__org_name_00,
768
769 
			@__org_id_01, @__org_name_01,
770
771 
			@__org_id_02, @__org_name_02,
772
773 
			@__org_id_03, @__org_name_03,
774
775 
			@__org_id_04, @__org_name_04,
776
777 
			@__org_id_05, @__org_name_05,
778
779 
			@__org_id_06, @__org_name_06,
780
781 
			@__org_id_07, @__org_name_07,
782
783 
			@__org_id_08, @__org_name_08,
784
785 
			@__org_id_09, @__org_name_09,
786
787 
			@__org_id_10, @__org_name_10,
788
789 
			@__org_id_11, @__org_name_12,
790
791 
			@__org_id_12, @__org_name_12
792
793 
			)
794
795
796
797 
			INSERT INTO @result_child
798
799 
			SELECT * FROM CBKT_GetOrgInOrgWideDetail(@__org_id, @ref_date, 0, @__org_name, @__org_level,
800
801 
				@__org_id_00, @__org_name_00,
802
803 
				@__org_id_01, @__org_name_01,
804
805 
				@__org_id_02, @__org_name_02,
806
807 
				@__org_id_03, @__org_name_03,
808
809 
				@__org_id_04, @__org_name_04,
810
811 
				@__org_id_05, @__org_name_05,
812
813 
				@__org_id_06, @__org_name_06,
814
815 
				@__org_id_07, @__org_name_07,
816
817 
				@__org_id_08, @__org_name_08,
818
819 
				@__org_id_09, @__org_name_09,
820
821 
				@__org_id_10, @__org_name_10,
822
823 
				@__org_id_11, @__org_name_11,
824
825 
				@__org_id_12, @__org_name_12
826
827 
			)
828
829 
		END
830
831 
	END
832
833
834
835 
	CLOSE cur
836
837 
	DEALLOCATE cur
838
839
840
841 
	INSERT INTO @result
842
843 
	SELECT * FROM @result_child
844
845
846
847 
	RETURN
848
849 
END
850
851
(18-18/23)