USE [SISIMPU]

DROP FUNCTION IF EXISTS [dbo].[FN_AddBusinessDays];
CREATE FUNCTION [dbo].[FN_AddBusinessDays](@Date DATE,@n INT)
RETURNS DATE AS 
BEGIN
	DECLARE @d INT,@f INT,@DW INT;
	SET @f=CAST(abs(1^SIGN(DATEPART(DW, @Date)-(7-@@DATEFIRST))) AS BIT)
	SET @DW=DATEPART(DW,@Date)-(7-@@DATEFIRST)*(@f^1)+@@DATEFIRST*(@f&1)
	SET @d=4-SIGN(@n)*(4-@DW);
	RETURN DATEADD(D,@n+((ABS(@n)+(@d%(8+SIGN(@n)))-2)/5)*2*SIGN(@n)-@d/7,@Date);
END
go

DROP FUNCTION IF EXISTS [dbo].[FN_GetStatusDraft];
CREATE FUNCTION [dbo].[FN_GetStatusDraft](@RegulationStep INT, @RegulationStepStatus INT) RETURNS VARCHAR(250)
AS
BEGIN
	DECLARE @status VARCHAR(250);
	 SET @status = (SELECT (CASE
				WHEN @RegulationStep = 1 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 1 AND @RegulationStepStatus = 0 THEN 'Draft'
				WHEN @RegulationStep = 1 AND @RegulationStepStatus = 1 THEN 'Process'
				WHEN @RegulationStep = 2 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 2 AND @RegulationStepStatus = 1 THEN 'Responded'
				WHEN @RegulationStep = 3 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 3 AND @RegulationStepStatus = 1 THEN 'Compile & Merge'
				WHEN @RegulationStep = 3 AND @RegulationStepStatus = 2 THEN 'Request Approval'
				WHEN @RegulationStep = 3 AND @RegulationStepStatus = 3 THEN 'Compile & Merge Approved'
				WHEN @RegulationStep = 4 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 4 AND @RegulationStepStatus = 1 THEN 'Process BS'

				WHEN @RegulationStep = 5 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 0 THEN '<i>Waiting Approval</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 1 THEN '<span class="text-success">Submitted</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 2 THEN '<span class="text-danger">Return</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 3 THEN '<span class="text-success">Review Started</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 31 THEN 'Waiting Approval<br/><i style="font-size:10px;">(Permintaan Eskalasi)</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 4 THEN '<span class="text-danger">Review Freeze</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 41 THEN '<span class="text-danger">Review Freeze</span><br/><i style="font-size:10px;">(Waiting Continues PPM)</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 5 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Approval PPM)</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 6 THEN '<span class="text-danger">Review Pending</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 7 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Approval Pemrakarsa)</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 71 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Restarted PPM)</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 8 THEN '<span class="text-success">Review Continues</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 9 THEN '<span class="text-success">Review Restarted</span>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 10 THEN '<span class="text-success">Review Finished</span><br/><i style="font-size:10px;">(Waiting Approval PPM)</i>'
				WHEN @RegulationStep = 5 AND @RegulationStepStatus = 11 THEN '<span class="text-success">Review Finished</span>'

				WHEN @RegulationStep = 6 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 0 THEN 'Waiting Approval</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 1 THEN '<span class="text-success">Submitted</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 2 THEN '<span class="text-danger">Return</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 3 THEN '<span class="text-success">Review Started</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 31 THEN 'Waiting Approval<br/><i style="font-size:10px;">(Permintaan Eskalasi)</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 4 THEN '<span class="text-danger">Review Freeze</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 41 THEN '<span class="text-danger">Review Freeze</span><br/><i style="font-size:10px;">(Waiting Continues DHUK)</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 5 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Approval DHUK)</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 6 THEN '<span class="text-danger">Review Pending</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 7 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Approval)</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 71 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Restarted DHUK)</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 8 THEN '<span class="text-success">Review Continues</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 9 THEN '<span class="text-success">Review Restarted</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 10 THEN '<span class="text-success">Review Finished</span><br/><i style="font-size:10px;">(Waiting Approval DHUK)</i>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 11 THEN '<span class="text-success">Review Finished</span>'
				WHEN @RegulationStep = 6 AND @RegulationStepStatus = 12 THEN 'Request Harmonisasi Kembali'

				WHEN @RegulationStep = 7 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 0 THEN 'Waiting Approval'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 1 THEN '<span class="text-success">Submitted</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 2 THEN '<span class="text-danger">Return</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 3 THEN '<span class="text-success">Review Started</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 31 THEN 'Waiting Approval<br/><i style="font-size:10px;">(Permintaan Eskalasi)</i>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 4 THEN '<span class="text-danger">Review Freeze</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 41 THEN '<span class="text-danger">Review Freeze</span><br/><i style="font-size:10px;">(Waiting Continues DHUK)</i>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 5 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Approval DHUK)</i>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 6 THEN '<span class="text-danger">Review Pending</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 7 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Approval)</i>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 71 THEN '<span class="text-danger">Review Pending</span><br/><i style="font-size:10px;">(Waiting Restarted DHUK)</i>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 8 THEN '<span class="text-success">Review Continues</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 9 THEN '<span class="text-success">Review Restarted</span>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 10 THEN '<span class="text-success">Review Finished</span><br/><i style="font-size:10px;">(Waiting Approval DHUK)</i>'
				WHEN @RegulationStep = 7 AND @RegulationStepStatus = 11 THEN '<span class="text-success">Review Finished</span>'
				
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 0 THEN 'Waiting ND Pencetakan'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 1 THEN 'Submit ND Pencetakan'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 2 THEN 'Ready to Print'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 3 THEN '<span class="text-success">Printing</span>'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 4 THEN '<span class="text-success">Signing</span>'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 5 THEN '<span class="text-success">Registered</span>'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 6 THEN '<span class="text-success">Completed</span>'
				WHEN @RegulationStep = 8 AND @RegulationStepStatus = 7 THEN '<span class="text-danger">Freeze</span>'

				WHEN @RegulationStep = 9 AND @RegulationStepStatus = 0 THEN 'Process<br/><i style="font-size:10px;">Board Seminar Ulang</i>'
				WHEN @RegulationStep = 9 AND @RegulationStepStatus = 1 THEN 'Responded<br/><i style="font-size:10px;">Board Seminar Ulang</i>'
				WHEN @RegulationStep = 9 AND @RegulationStepStatus = 2 THEN 'Compile & Merge<br/><i style="font-size:10px;">Board Seminar Ulang</i>'
				WHEN @RegulationStep = 9 AND @RegulationStepStatus = 21 THEN 'Request Approval<br/><i style="font-size:10px;">Board Seminar Ulang</i>'
				WHEN @RegulationStep = 9 AND @RegulationStepStatus = 22 THEN 'Compile & Merge Approved<br/><i style="font-size:10px;">Board Seminar Ulang</i>'
				WHEN @RegulationStep = 10 AND @RegulationStepStatus = 0 THEN 'Board seminar ulang tanpa kembali ke tahapan permintaan tanggapan'
				WHEN @RegulationStep = 11 AND @RegulationStepStatus = 0 THEN 'Process<br/><i style="font-size:10px;">Tanpa Board Seminar Ulang</i>'
				WHEN @RegulationStep = 11 AND @RegulationStepStatus = 1 THEN 'Responded<br/><i style="font-size:10px;">Tanpa Board Seminar Ulang</i>'
				WHEN @RegulationStep = 11 AND @RegulationStepStatus = 2 THEN 'Compile & Merge<br/><i style="font-size:10px;">Tanpa Board Seminar Ulang</i>'
				WHEN @RegulationStep = 11 AND @RegulationStepStatus = 21 THEN 'Request Approval<br/><i style="font-size:10px;">Tanpa Board Seminar Ulang</i>'
				WHEN @RegulationStep = 11 AND @RegulationStepStatus = 22 THEN 'Compile & Merge Approved<br/><i style="font-size:10px;">Tanpa Board Seminar Ulang</i>'

				WHEN @RegulationStep = 12 AND @RegulationStepStatus = 1 THEN 'Process POJK'
				WHEN @RegulationStep = 13 AND @RegulationStepStatus = 1 THEN 'Process BS POJK'
				WHEN @RegulationStep = 13 AND @RegulationStepStatus = 2 THEN 'Process BS - Ready to submit'
				WHEN @RegulationStep = 13 AND @RegulationStepStatus = 22 THEN 'Re-process BS'
				WHEN @RegulationStep = 13 AND @RegulationStepStatus = 3 THEN 'Send Responded'
				WHEN @RegulationStep = 14 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 14 AND @RegulationStepStatus = 1 THEN 'Responded'
				WHEN @RegulationStep = 15 AND @RegulationStepStatus = -1 THEN 'Canceled'
				WHEN @RegulationStep = 15 AND @RegulationStepStatus = 1 THEN 'Compile & Merge'
				WHEN @RegulationStep = 15 AND @RegulationStepStatus = 2 THEN 'Compile & Merge (Request Approval)'
				WHEN @RegulationStep = 15 AND @RegulationStepStatus = 3 THEN 'Compile & Merge Approved'

				ELSE
					'-'
				END))
	 RETURN (SELECT @status AS 'DokumenPath')
END



go


DROP FUNCTION IF EXISTS [dbo].[FN_GetTahapanDraft];
CREATE FUNCTION [dbo].[FN_GetTahapanDraft](@RegulationStep INT) RETURNS VARCHAR(250)
AS
BEGIN
	DECLARE @status VARCHAR(250);
	 SET @status = (SELECT (
			CASE
			WHEN @RegulationStep = 1 THEN 'Inisiasi'
			WHEN @RegulationStep = 2 THEN 'Permintaan Tanggapan'
			WHEN @RegulationStep = 3 THEN 'Kompilasi'
			WHEN @RegulationStep = 4 THEN 'Board Seminar'
			WHEN @RegulationStep = 5 THEN 'Harmonisasi DPPM'
			WHEN @RegulationStep = 6 THEN 'Harmonisasi'
			WHEN @RegulationStep = 7 THEN 'Harmonisasi Kembali'
			WHEN @RegulationStep = 8 THEN 'Finalisasi'
			WHEN @RegulationStep = 9 THEN 'Board seminar ulang, kembali ke tahapan permintaan tanggapan'
			WHEN @RegulationStep = 10 THEN 'Board seminar ulang tanpa kembali ke tahapan permintaan tanggapan'
			WHEN @RegulationStep = 11 THEN 'Kembali ke tahapan permintaan tanggapan tanpa melakukan Board seminar ulang'
			WHEN @RegulationStep = 12 THEN 'Inisiasi POJK'
			WHEN @RegulationStep = 13 THEN 'Board Seminar'
			WHEN @RegulationStep = 14 THEN 'Permintaan Tanggapan'
			ELSE
				''
			END
		) )
	 RETURN (SELECT @status AS 'DokumenPath')
END



go


DROP VIEW IF EXISTS dbo.View_BoardSeminars;
/*
Navicat SQL Server Data Transfer

Source Server         : LOCAL
Source Server Version : 110000
Source Host           : MARIMO\SQLEXPRESS:1433
Source Database       : OJK_SISIMPU
Source Schema         : Transaction

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2017-11-15 09:58:09

 ----------------------------
 View structure for View_BoardSeminars
 ----------------------------*/
CREATE VIEW dbo.View_BoardSeminars
AS
SELECT        a.RegulationId, a.RegulationRegulationTypeId, a.RegulationConditionId, a.RegulationOrganizationId, a.RegulationIsBoardSeminar, a.RegulationName, a.RegulationDescription, a.RegulationCreateBy, a.RegulationCreateDate, 
                         a.RegulationUpdateBy, a.RegulationUpdateDate, a.RegulationStatus, a.RegulationLogCode, a.RegulationStep, a.RegulationStepStatus, a.RegulationCancelBy, a.RegulationCancelDate, a.RegulationReason, a.RegulationTypeId, 
                         a.RegulationTypeIsBoardSeminar, a.RegulationTypeCode, a.RegulationTypeName, a.RegulationTypeDescription, a.OrganizationId, a.OrganizationParentId, a.OrganizationSectorId, a.OrganizationCode, a.OrganizationName, 
                         a.SectorCode, a.SectorName, a.ConditionId, a.ConditionCode, a.ConditionName, a.ConditionDescription, e.BoardSeminarId, e.BoardSeminarDate, e.BoardSeminarCreateBy, e.BoardSeminarCreateDate, 
                         e.BoardSeminarUpdateBy, e.BoardSeminarUpdateDate, e.BoardSeminarStatus, [Transaction].FN_GetTahapanDraft(a.RegulationStep) AS RegulationStepName, [Transaction].FN_GetStatusDraft(a.RegulationStep, 
                         a.RegulationStepStatus) AS RegulationStepStatusName, [Transaction].FN_GetDokumenPath(1, 2, a.RegulationId) AS DraftPeraturanDocumentFileName, [Transaction].FN_GetDokumenPath(2, 2, a.RegulationId) 
                         AS KajianAkademisDocumentFileName, (CASE WHEN a.RegulationStep = 2 THEN (CASE WHEN e.BoardSeminarDate IS NULL THEN '-' ELSE [Transaction].FN_GetSLA(e.BoardSeminarDate, 
                         'Batas Akhir Penyampaian ND Permintaan Tanggapan', a.RegulationRegulationTypeId, a.RegulationConditionId, 0) END) ELSE (CASE WHEN e.BoardSeminarDate IS NULL THEN '-' ELSE CONVERT(VARCHAR, CONVERT(datetime, 
                         [Transaction].FN_GetSLADate(e.BoardSeminarDate, 'Batas Akhir Penyampaian ND Permintaan Tanggapan', a.RegulationRegulationTypeId, a.RegulationConditionId, 0)), 103) END) END) 
                         AS IndikatorBatasAkhirPermintaanTanggapanHtml, e.BoardSeminarResultNumber
FROM            [Transaction].View_RegulationDraft AS a LEFT OUTER JOIN
                         [Transaction].BoardSeminars AS e ON e.BoardSeminarRegulationId = a.RegulationId
WHERE        (a.RegulationTypeIsBoardSeminar = 1) AND (a.RegulationConditionId = 1) AND (a.RegulationStatus = 1)
go

declare @MS_DiagramPane_ Begin DesignProperties = 
= N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
exec sp_addextendedproperty 'MS_DiagramPane1', @MS_DiagramPane_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @___Begin_PaneConfigurations____         NumPanes = 4
=      Begin PaneConfiguration = 0
exec sp_addextendedproperty '   Begin PaneConfigurations = 
', @___Begin_PaneConfigurations____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Configuration_____H__________________________      Begin PaneConfiguration = 1
=      End
exec sp_addextendedproperty '         Configuration = "(H (1[41] 4[17] 2[24] 3) )"
', @_________Configuration_____H__________________________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________NumPanes_____      End
=         Configuration = "(H (1 [50] 4 [25] 3))"
exec sp_addextendedproperty '         NumPanes = 3
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______Begin_PaneConfiguration_____         Configuration = "(H (1 [50] 2 [25] 3))"
=         NumPanes = 3
exec sp_addextendedproperty '      Begin PaneConfiguration = 2
', @______Begin_PaneConfiguration_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______End_         NumPanes = 3
=      Begin PaneConfiguration = 3
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Configuration_____H_____________________      Begin PaneConfiguration = 4
=      End
exec sp_addextendedproperty '         Configuration = "(H (4 [30] 2 [40] 3))"
', @_________Configuration_____H_____________________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________NumPanes_____      End
=         Configuration = "(H (1 [56] 3))"
exec sp_addextendedproperty '         NumPanes = 2
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______Begin_PaneConfiguration_____         Configuration = "(H (2 [66] 3))"
=         NumPanes = 2
exec sp_addextendedproperty '      Begin PaneConfiguration = 5
', @______Begin_PaneConfiguration_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______End_         NumPanes = 2
=      Begin PaneConfiguration = 6
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Configuration_____H______________      Begin PaneConfiguration = 7
=      End
exec sp_addextendedproperty '         Configuration = "(H (4 [50] 3))"
', @_________Configuration_____H______________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________NumPanes_____      End
=         Configuration = "(V (3))"
exec sp_addextendedproperty '         NumPanes = 1
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______Begin_PaneConfiguration_____         Configuration = "(H (1[56] 4[18] 2) )"
=         NumPanes = 3
exec sp_addextendedproperty '      Begin PaneConfiguration = 8
', @______Begin_PaneConfiguration_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______End_         NumPanes = 2
=      Begin PaneConfiguration = 9
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Configuration_____H______________      Begin PaneConfiguration = 10
=      End
exec sp_addextendedproperty '         Configuration = "(H (1 [75] 4))"
', @_________Configuration_____H______________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________NumPanes_____      End
=         Configuration = "(H (1[66] 2) )"
exec sp_addextendedproperty '         NumPanes = 2
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______Begin_PaneConfiguration______         Configuration = "(H (4 [60] 2))"
=         NumPanes = 2
exec sp_addextendedproperty '      Begin PaneConfiguration = 11
', @______Begin_PaneConfiguration______, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______End_         NumPanes = 1
=      Begin PaneConfiguration = 12
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Configuration_____H________      Begin PaneConfiguration = 13
=      End
exec sp_addextendedproperty '         Configuration = "(H (1) )"
', @_________Configuration_____H________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________NumPanes_____      End
=         Configuration = "(V (4))"
exec sp_addextendedproperty '         NumPanes = 1
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______Begin_PaneConfiguration______         Configuration = "(V (2))"
=         NumPanes = 1
exec sp_addextendedproperty '      Begin PaneConfiguration = 14
', @______Begin_PaneConfiguration______, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______End_   End
=      ActivePaneConfig = 0
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @___Begin_DiagramPane____         Top = 0
=      Begin Origin = 
exec sp_addextendedproperty '   Begin DiagramPane = 
', @___Begin_DiagramPane____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Left_____      Begin Tables = 
=      End
exec sp_addextendedproperty '         Left = 0
', @_________Left_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Begin_Table____a__               Top = 6
=            Begin Extent = 
exec sp_addextendedproperty '         Begin Table = "a"
', @_________Begin_Table____a__, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_______________Left______               Right = 291
=               Bottom = 136
exec sp_addextendedproperty '               Left = 38
', @_______________Left______, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @____________End_            TopColumn = 0
=            DisplayFlags = 280
exec sp_addextendedproperty '            End
', @____________End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________End_            Begin Extent = 
=         Begin Table = "e"
exec sp_addextendedproperty '         End
', @_________End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_______________Top_______               Bottom = 268
=               Left = 38
exec sp_addextendedproperty '               Top = 138
', @_______________Top_______, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_______________Right_______            DisplayFlags = 280
=            End
exec sp_addextendedproperty '               Right = 277
', @_______________Right_______, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @____________TopColumn_____      End
=         End
exec sp_addextendedproperty '            TopColumn = 0
', @____________TopColumn_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @___End_   End
=   Begin SQLPane = 
exec sp_addextendedproperty '   End
', @___End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @___Begin_DataPane____      End
=      Begin ParameterDefaults = ""
exec sp_addextendedproperty '   Begin DataPane = 
', @___Begin_DataPane____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______Begin_ColumnWidths_____         Width = 1500
=         Width = 284
exec sp_addextendedproperty '      Begin ColumnWidths = 9
', @______Begin_ColumnWidths_____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Width________         Width = 1500
=         Width = 1500
exec sp_addextendedproperty '         Width = 1500
', @_________Width________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Width________         Width = 1500
=         Width = 1500
exec sp_addextendedproperty '         Width = 1500
', @_________Width________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Width________   End
=      End
exec sp_addextendedproperty '         Width = 1500
', @_________Width________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @___Begin_CriteriaPane____         Column = 1440
=      Begin ColumnWidths = 11
exec sp_addextendedproperty '   Begin CriteriaPane = 
', @___Begin_CriteriaPane____, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Alias_______         Output = 720
=         Table = 1170
exec sp_addextendedproperty '         Alias = 900
', @_________Alias_______, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Append________         SortType = 1350
=         NewValue = 1170
exec sp_addextendedproperty '         Append = 1400
', @_________Append________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________SortOrder________         Filter = 1350
=         GroupBy = 1350
exec sp_addextendedproperty '         SortOrder = 1410
', @_________SortOrder________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_________Or________         Or = 1350
=         Or = 1350
exec sp_addextendedproperty '         Or = 1350
', @_________Or________, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @______End_ End
=   End
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

declare @_ MS_DiagramPaneCount = nvarchar
exec sp_addextendedproperty '''', @_, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go

exec sp_addextendedproperty '1', int, 'SCHEMA', 'dbo', 'VIEW', 'View_BoardSeminars'
go


DROP VIEW IF EXISTS [dbo].[View_RDK];

/* ----------------------------
 View structure for View_RDK
 ----------------------------*/
CREATE VIEW [dbo].[View_RDK]
AS
SELECT        D.RegulationId, D.RegulationRegulationTypeId, D.RegulationConditionId, D.RegulationOrganizationId, D.RegulationIsBoardSeminar, D.RegulationName, D.RegulationDescription, D.RegulationCreateBy, D.RegulationCreateDate, 
                         D.RegulationUpdateBy, D.RegulationUpdateDate, D.RegulationStatus, D.RegulationLogCode, D.RegulationStep, D.RegulationStepStatus, D.RegulationCancelBy, D.RegulationCancelDate, D.RegulationReason, 
                         D.RegulationCancelStep, D.RegulationCancelStepName, D.RegulationTypeId, D.RegulationTypeIsBoardSeminar, D.RegulationTypeCode, D.RegulationTypeName, D.RegulationTypeDescription, D.OrganizationId, 
                         D.OrganizationParentId, D.OrganizationSectorId, D.OrganizationCode, D.OrganizationName, D.SectorCode, D.SectorName, D.OrganizationIsPPM, D.ConditionId, D.ConditionCode, D.ConditionName, D.ConditionDescription, 
                         R.RdkId, R.RdkRegulationId, R.RdkHarmonizationId, R.RdkDocumentId, R.RdkNomorKRDK, R.RdkDate, R.RdkType, R.RdkCreateBy, R.RdkCreateDate, R.RdkUpdateBy, R.RdkUpdateDate, R.RdkApproveBy, R.RdkApproveDate, 
                         R.RdkStatus, (CASE WHEN (D .RegulationStep < 8 OR
                         (D .RegulationStep = 8 AND D .RegulationStepStatus = 0 OR
                         D .RegulationStep = 19 AND D .RegulationStepStatus = 0)) THEN (CASE WHEN R.RdkType = 1 THEN [Transaction].FN_GetSLA(R.RdkDate, 'Batas Akhir Penyampaian ND Pencetakan', D .RegulationRegulationTypeId, 
                         D .RegulationConditionId, 0) ELSE [Transaction].FN_GetSLA(R.RdkDate, 'Batas Akhir Penyampaian ND Harmonisasi Kembali', D .RegulationRegulationTypeId, D .RegulationConditionId, 0) END) 
                         ELSE (CASE WHEN R.RdkType = 1 THEN CONVERT(VARCHAR, CONVERT(datetime, [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Pencetakan', D .RegulationRegulationTypeId, D .RegulationConditionId,
                          0)), 103) ELSE CONVERT(VARCHAR, CONVERT(datetime, [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Harmonisasi Kembali', D .RegulationRegulationTypeId, D .RegulationConditionId, 0)), 103) END) 
                         END) AS BatasAkhirNDPermintaanPencetakanHTML, (CASE WHEN R.RdkType = 1 THEN [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Pencetakan', D .RegulationRegulationTypeId, 
                         D .RegulationConditionId, 0) ELSE [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Harmonisasi Kembali', D .RegulationRegulationTypeId, D .RegulationConditionId, 0) END) 
                         AS BatasAkhirNDPermintaanPencetakanDate, 
                         (CASE WHEN R.RdkType = 1 THEN 'RDK Tanpa Perubahan Rancangan' WHEN R.RdkType = 2 THEN 'Lanjut ke proses penyelarasan DHUK' WHEN R.RdkType = 3 THEN 'Perubahan Rancangan' ELSE NULL 
                         END) AS RdkTypeName, [Transaction].FN_GetTahapanDraft(D.RegulationStep) AS RegulationStepName, [Transaction].FN_GetStatusDraft(D.RegulationStep, D.RegulationStepStatus) AS RegulationStepStatusName, 
                         [Transaction].FN_GetDokumenPath(1, 7 | 18, D.RegulationId) AS DraftPeraturanDocumentFileName
FROM            [Transaction].View_RegulationDraft AS D LEFT OUTER JOIN
                         [Transaction].Rdk AS R ON R.RdkRegulationId = D.RegulationId AND R.RdkStatus = 1
WHERE        (D.RegulationStatus = 1) AND (D.RegulationStep >= 8) OR
                         (D.RegulationStep IN (6, 7, 17, 18)) AND (D.RegulationStepStatus IN (11, 12))
go

declare @MS_DiagramPane_ Begin DesignProperties = 
= N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
exec sp_addextendedproperty 'MS_DiagramPane1', @MS_DiagramPane_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @___Begin_PaneConfigurations____         NumPanes = 4
=      Begin PaneConfiguration = 0
exec sp_addextendedproperty '   Begin PaneConfigurations = 
', @___Begin_PaneConfigurations____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Configuration_____H__________________________      Begin PaneConfiguration = 1
=      End
exec sp_addextendedproperty '         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
', @_________Configuration_____H__________________________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________NumPanes_____      End
=         Configuration = "(H (1 [50] 4 [25] 3))"
exec sp_addextendedproperty '         NumPanes = 3
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______Begin_PaneConfiguration_____         Configuration = "(H (1 [50] 2 [25] 3))"
=         NumPanes = 3
exec sp_addextendedproperty '      Begin PaneConfiguration = 2
', @______Begin_PaneConfiguration_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______End_         NumPanes = 3
=      Begin PaneConfiguration = 3
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Configuration_____H_____________________      Begin PaneConfiguration = 4
=      End
exec sp_addextendedproperty '         Configuration = "(H (4 [30] 2 [40] 3))"
', @_________Configuration_____H_____________________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________NumPanes_____      End
=         Configuration = "(H (1 [56] 3))"
exec sp_addextendedproperty '         NumPanes = 2
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______Begin_PaneConfiguration_____         Configuration = "(H (2 [66] 3))"
=         NumPanes = 2
exec sp_addextendedproperty '      Begin PaneConfiguration = 5
', @______Begin_PaneConfiguration_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______End_         NumPanes = 2
=      Begin PaneConfiguration = 6
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Configuration_____H______________      Begin PaneConfiguration = 7
=      End
exec sp_addextendedproperty '         Configuration = "(H (4 [50] 3))"
', @_________Configuration_____H______________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________NumPanes_____      End
=         Configuration = "(V (3))"
exec sp_addextendedproperty '         NumPanes = 1
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______Begin_PaneConfiguration_____         Configuration = "(H (1[56] 4[18] 2) )"
=         NumPanes = 3
exec sp_addextendedproperty '      Begin PaneConfiguration = 8
', @______Begin_PaneConfiguration_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______End_         NumPanes = 2
=      Begin PaneConfiguration = 9
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Configuration_____H______________      Begin PaneConfiguration = 10
=      End
exec sp_addextendedproperty '         Configuration = "(H (1 [75] 4))"
', @_________Configuration_____H______________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________NumPanes_____      End
=         Configuration = "(H (1[66] 2) )"
exec sp_addextendedproperty '         NumPanes = 2
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______Begin_PaneConfiguration______         Configuration = "(H (4 [60] 2))"
=         NumPanes = 2
exec sp_addextendedproperty '      Begin PaneConfiguration = 11
', @______Begin_PaneConfiguration______, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______End_         NumPanes = 1
=      Begin PaneConfiguration = 12
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Configuration_____H________      Begin PaneConfiguration = 13
=      End
exec sp_addextendedproperty '         Configuration = "(H (1) )"
', @_________Configuration_____H________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________NumPanes_____      End
=         Configuration = "(V (4))"
exec sp_addextendedproperty '         NumPanes = 1
', @_________NumPanes_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______Begin_PaneConfiguration______         Configuration = "(V (2))"
=         NumPanes = 1
exec sp_addextendedproperty '      Begin PaneConfiguration = 14
', @______Begin_PaneConfiguration______, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @______End_   End
=      ActivePaneConfig = 0
exec sp_addextendedproperty '      End
', @______End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @___Begin_DiagramPane____         Top = 0
=      Begin Origin = 
exec sp_addextendedproperty '   Begin DiagramPane = 
', @___Begin_DiagramPane____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Left_____      Begin Tables = 
=      End
exec sp_addextendedproperty '         Left = 0
', @_________Left_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Begin_Table____D__               Top = 6
=            Begin Extent = 
exec sp_addextendedproperty '         Begin Table = "D"
', @_________Begin_Table____D__, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_______________Left______               Right = 291
=               Bottom = 136
exec sp_addextendedproperty '               Left = 38
', @_______________Left______, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @____________End_            TopColumn = 0
=            DisplayFlags = 280
exec sp_addextendedproperty '            End
', @____________End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________End_            Begin Extent = 
=         Begin Table = "R"
exec sp_addextendedproperty '         End
', @_________End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_______________Top_______               Bottom = 268
=               Left = 38
exec sp_addextendedproperty '               Top = 138
', @_______________Top_______, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_______________Right_______            DisplayFlags = 280
=            End
exec sp_addextendedproperty '               Right = 236
', @_______________Right_______, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @____________TopColumn_____      End
=         End
exec sp_addextendedproperty '            TopColumn = 0
', @____________TopColumn_____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @___End_   End
=   Begin SQLPane = 
exec sp_addextendedproperty '   End
', @___End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @___Begin_DataPane____      End
=      Begin ParameterDefaults = ""
exec sp_addextendedproperty '   Begin DataPane = 
', @___Begin_DataPane____, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @___End_      Begin ColumnWidths = 11
=   Begin CriteriaPane = 
exec sp_addextendedproperty '   End
', @___End_, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Column________         Table = 1170
=         Alias = 900
exec sp_addextendedproperty '         Column = 1440
', @_________Column________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Output_______         NewValue = 1170
=         Append = 1400
exec sp_addextendedproperty '         Output = 720
', @_________Output_______, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________SortType________         GroupBy = 1350
=         SortOrder = 1410
exec sp_addextendedproperty '         SortType = 1350
', @_________SortType________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Filter________         Or = 1350
=         Or = 1350
exec sp_addextendedproperty '         Filter = 1350
', @_________Filter________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

declare @_________Or________   End
=      End
exec sp_addextendedproperty '         Or = 1350
', @_________Or________, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

exec sp_addextendedproperty 'End
', ', 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'View_RDK'
go



