Prepare Patricia Database

In order to have Data available that is neccesary for the Pavis Interface, the Patricia Database needs to be enhanced.
please download the most current version of these scripts from here: setupscripts-master20211007.zip
Open up the Database Administration Tool of your choice (e.G. Microsoft SQL Server Management Studio) and execute the below scripts .

Create Extended Name Fields

Code Block

language	sql
title	Create Login Data
linenumbers	true
collapse	true

-- Create Fields for Pavis Interface

-- 2019-11-28 Jr Serviva

INSERT [dbo].[NAME_ADD_FIELDS_DEF] ([NAME_FIELD_NUMBER], [FIELD_NAME], [FIELD_TYPE], [SORT_ORDER], [IS_ACTIVE], [MAINTAINED_BY_PATRIX], [IS_CHECKBOX]) VALUES (40000, N'pavis cust_id', 2, NULL, 1, 0, 0)
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF] ([NAME_FIELD_NUMBER], [FIELD_NAME], [FIELD_TYPE], [SORT_ORDER], [IS_ACTIVE], [MAINTAINED_BY_PATRIX], [IS_CHECKBOX]) VALUES (40001, N'Pavis password', 2, NULL, 1, 0, 0)
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40000, 3, N'Pavis Connect Customer ID')
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40000, 4, N'Pavis Connect Customer ID')
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40001, 3, N'Pavis Connect password')
GO

INSERT [dbo].[NAME_ADD_FIELDS_DEF_LABEL] ([NAME_FIELD_NUMBER], [LANGUAGE_ID], [FIELD_LABEL]) VALUES (40001, 4, N'Pavis Connect Passwort')
GO

2. create Diary Field Pavis next Annuity

Code Block

language	sql
title	Create Diary Field
collapse	true

INSERT [dbo].[DIARY_FIELD] ([FIELD_NUMBER], [DIARY_ENTRY_POINT_ID], [DATE_FLAG], [DIARY_TYPE_LABEL], [CHECK_DUPLICATE], [DIARY_FIELD_TYPE], [CREATE_REGARDING_TEXT], [SORT_ORDER], [DIARY_GROUP], [IGNORE_DIARY_DATE_RULE], [SHOW_ON_CLIENTLIST], [UPDATE_FAMILY], [IS_NUMERIC], [USE_DDLB], [DDLB_SELECT], [MAINTAINED_BY_PATRIX], [STATUS_ID], [VISIBLE_AS_LETTER_CODE], [VISIBLE_IN_CASE_OVERVIEW], [USE_IN_FIND_RESULTS], [VALIDATION_TYPE_ID], [VALIDATION_DIARY_FIELD], [VALIDATION_DATE_FIELD], [UPDATE_DESIGNATION_STATUS], [AVAILABLE_IN_REPORT_CREATION], [GROUP_ON_CASE_FIND], [YMD_ADJUSTMENT], [PCP_RESULT]) VALUES (33000, NULL, CAST(1 AS Numeric(1, 0)), N'Pavis Next Annuity', NULL, NULL, NULL, 15, 4, 0, CAST(1 AS Numeric(1, 0)), CAST(0 AS Numeric(1, 0)), 0, 0, NULL, 0, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)
GO

INSERT [dbo].[DIARY_MATRIX_TEXT] ([FIELD_NUMBER], [DIARY_LINE_TEXT_SEQ], [MAINTAINED_BY_PATRIX]) VALUES (33000, 1, NULL)
GO

INSERT [dbo].[DIARY_LINE_TEXT] ([FIELD_NUMBER], [DIARY_LINE_TEXT], [DIARY_LINE_TEXT_SEQ], [DIARY_LINE_TEXT_LANGUAGE], [MAINTAINED_BY_PATRIX]) VALUES (33000, N'Pavis Next Annuity', 1, 3, NULL)
GO

INSERT [dbo].[DIARY_LINE_TEXT] ([FIELD_NUMBER], [DIARY_LINE_TEXT], [DIARY_LINE_TEXT_SEQ], [DIARY_LINE_TEXT_LANGUAGE], [MAINTAINED_BY_PATRIX]) VALUES (33000, N'Pavis Nächste Gebührenzahlung', 1, 4, NULL)
GO
INSERT [dbo].[DIARY_FIELD] ( [FIELD_NUMBER] ,[DIARY_ENTRY_POINT_ID] ,[DATE_FLAG] ,[DIARY_TYPE_LABEL] ,[CHECK_DUPLICATE] ,[DIARY_FIELD_TYPE] ,[CREATE_REGARDING_TEXT] ,[SORT_ORDER] ,[DIARY_GROUP] ,[IGNORE_DIARY_DATE_RULE] ,[SHOW_ON_CLIENTLIST] ,[UPDATE_FAMILY] ,[IS_NUMERIC] ,[USE_DDLB] ,[DDLB_SELECT] ,[MAINTAINED_BY_PATRIX] ,[STATUS_ID] ,[VISIBLE_AS_LETTER_CODE] ,[VISIBLE_IN_CASE_OVERVIEW] ,[USE_IN_FIND_RESULTS] ,[VALIDATION_TYPE_ID] ,[VALIDATION_DIARY_FIELD] ,[VALIDATION_DATE_FIELD] ,[UPDATE_DESIGNATION_STATUS] ,[AVAILABLE_IN_REPORT_CREATION] ,[GROUP_ON_CASE_FIND] ,[YMD_ADJUSTMENT] ,[PCP_RESULT] )
VALUES ( 33005 ,NULL ,CAST(1 AS NUMERIC(1, 0)) ,N'Pavis Standing Order Start' ,NULL ,NULL ,NULL ,15 ,4 ,0 ,CAST(1 AS NUMERIC(1, 0)) ,CAST(0 AS NUMERIC(1, 0)) ,0 ,0 ,NULL ,0 ,NULL ,1 ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,1 ,NULL ,NULL ,NULL ) GO

INSERT [dbo].[DIARY_MATRIX_TEXT] ( [FIELD_NUMBER] ,[DIARY_LINE_TEXT_SEQ] ,[MAINTAINED_BY_PATRIX] ) VALUES ( 33005 ,1 ,NULL ) GO
INSERT [dbo].[DIARY_LINE_TEXT] ( [FIELD_NUMBER] ,[DIARY_LINE_TEXT] ,[DIARY_LINE_TEXT_SEQ] ,[DIARY_LINE_TEXT_LANGUAGE] ,[MAINTAINED_BY_PATRIX] ) VALUES ( 33005 ,N'Pavis Standing Order Start' ,1 ,3 ,NULL ) GO
INSERT [dbo].[DIARY_LINE_TEXT] ( [FIELD_NUMBER] ,[DIARY_LINE_TEXT] ,[DIARY_LINE_TEXT_SEQ] ,[DIARY_LINE_TEXT_LANGUAGE] ,[MAINTAINED_BY_PATRIX] ) VALUES ( 33005 ,N'Pavis Dauerauftrag Start' ,1 ,4 ,NULL ) GO

Code Block

language	sql
title	Create Diary Mapping and View
collapse	true

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SRV_IF_TAG](
	[XML_TAG] [nvarchar](250) NOT NULL,
	[Diary_Field_Number] [int] NOT NULL
) ON [PRIMARY]
GO
 delete * from [SRV_IF_TAG]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or alter VIEW [dbo].[SRV_IF_DIARY]
AS
SELECT *
FROM (
   SELECT DISTINCT gcd.case_id
		,sit.XML_TAG
		,case when
		(CONCAT (
			format([diary_date], 'yyyyMMdd')
			,[diary_text]
			))='' then NULL
			else 
			CONCAT (
			format([diary_date], 'yyyyMMdd')
			,[diary_text]
			) end as			
			content
    /* ,gcd.field_number*/ FROM dbo.gv_case_diary AS gcd
    INNER JOIN dbo.SRV_IF_TAG AS sit ON sit.Diary_Field_Number = gcd.field_number
    WHERE (gcd.login_id = 'SU')
        AND (gcd.language_id = 3)
    ) t
PIVOT(max(content) FOR XML_TAG IN (
            [application_date]
            ,[application_number]
            ,[priority_date]
            ,[priority_number]
            ,[priority_country_id]
            ,[National Filing Date]
            ,[National Filing No.]
            ,[ep_registration_number]
            ,[publication_date]
            ,[publication_of_filing]
            ,[grant_date]
            ,[renewal_date]
            ,[Duration]
            ,[registration_number]
            ,[Date of Order]
            ,[publication_of_grant]
            ,[examination_request_date]
            ,[patent_term_adjustment]
            ,[International  Appln. No.]
            ,[number_of_claims]
            ,[number_of_designs]
            ,[number_of_items]
            )) AS pivot_table
 
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'application_date', 3)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'application_number', 4)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'priority_date', 5)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'priority_number', 6)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'priority_country_id', 7)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'National Filing Date', 12)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'National Filing No.', 0)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'ep_registration_number', 10)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'publication_date', 21)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'publication_of_filing', 0)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'renewal_date', 39)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'grant_date', 35)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'Duration', 20)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'registration_number', 4)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'Date of Order', 1)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'publication_of_grant', 0)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'examination_request_date', 59)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'patent_term_adjustment', 328)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'International  Appln. No.', 6)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'number_of_claims', 127)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'number_of_designs', 10217)
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'number_of_items', 10220)  
GO
INSERT [dbo].[SRV_IF_TAG] ([XML_TAG], [Diary_Field_Number]) VALUES (N'stand_order_start', 30005)
GO

Code Block

language	sql
title	Create Name function for XML
collapse	true

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: J.Rady / Serviva -- Create date: 17.2.2021 -- Description: to prepare all names for pavis xml -- update: 20.09.2021 -- exchange role_types for role_type_type -- ============================================= create or ALTER FUNCTION [dbo].[FN_PAVIS_NAMES] ( -- Add the parameters for the function here @name_id int ) RETURNS XML AS BEGIN -- Declare the return variable here DECLARE @Returnnames XML -- Add the T-SQL statements to compute the return value here SET @Returnnames = (SELECT distinct [name_id] address_code, case /*when c.ROLE_TYPE_ID = 7 then 1 when c.ROLE_TYPE_ID = 20 then 2 when c.ROLE_TYPE_ID = 2 then 3 when c.ROLE_TYPE_ID = 4 then 4*/ when crt.ROLE_TYPE_TYPE = 3 then 1 -- Agent / pavis Vertreter when crt.ROLE_TYPE_TYPE = 11 then 2 -- renewal Account / pavis Einzahler when crt.ROLE_TYPE_TYPE = 1 then 3 -- Applicant / pavis Inhaber when crt.ROLE_TYPE_TYPE = 2 then 4 -- correspondence / pavis Kontakt end as name_type ,[name1] name_1 ,[name2] name_2 ,[name3] name_3 ,[adr1]+[adr2]+[adr3]+[adr4]+[adr5] as street ,[postal_code] postal ,[state_id] country ,[telephone] ,[telefax] fax ,[e_mail_adress] email FROM [dbo].[dv_names_basic] dnb join casting c on c.ACTOR_ID = dnb.name_id join CASE_ROLE_TYPE crt on crt.ROLE_TYPE_ID = c.ROLE_TYPE_ID where language_id = 3 and c.case_id = @name_id and crt.ROLE_TYPE_TYPE

in

(1,2,11,3) and dnb.is_default = 1 FOR XML PATH('name_row') ,ROOT('names')) -- Return the result of the function RETURN @Returnnames END GO

Code Block

language	sql
title	Create Owner cost acct. function
collapse	true

/****** Object:  UserDefinedFunction [dbo].[FN_PAVIS_OWNER]    Script Date: 20.05.2021 15:21:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		 J.Rady / Serviva
-- Create date: 20.5.2021
-- Description:	concatenate owners if there are more than one owner on the case
-- 
-- =============================================
CREATE or alter FUNCTION [dbo].[FN_PAVIS_OWNER] (
	-- Add the parameters for the function here
	@case_id INT
	)
RETURNS NVARCHAR(1000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Returnowner NVARCHAR(1000)

	SET @Returnowner =
		-- Add the T-SQL statements to compute the return value here
		(
			SELECT DISTINCT SUBSTRING((
						SELECT ', ' + gncr1.full_name AS [text()]
						FROM gv_name_casting_report gncr1
						WHERE gncr1.case_id = gncr2.case_id
							AND language_id = 4
							AND role_type_id = 2
						ORDER BY gncr1.case_id
						FOR XML PATH('')
						), 2, 1000) [owner_name]
			FROM gv_name_casting_report gncr2
			WHERE case_id = @case_id
			)

	-- Return the result of the function
	RETURN @Returnowner
END
GO




/****** Object:  UserDefinedFunction [dbo].[FN_PAVIS_name_types]    Script Date: 16.08.2021 17:15:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
-- =============================================
-- Author:       J.Rady / Serviva
-- Create date: 16.8.2021
-- Description: concatenate owners if there are more than one owner on the case
-- =============================================
CREATE or alter FUNCTION [dbo].[FN_PAVIS_name_types] (
    -- Add the parameters for the function here
	/*
	which name _type_type for which tag
+--------------+-------------------+--------------------+
|  Pavis_tag   |  Name_type_type   | Name_type_type No. |
+--------------+-------------------+--------------------+
| Owner        | applicant         |                  1 |
| agent        | Foreign Associate |                  3 |
| cost_account | Renewal Associate |                 10 |
| sub_customer | Sub Associate     |                  4 |
+--------------+-------------------+--------------------+
	*/
    @case_id INT,
	@ntt INT
    )
RETURNS NVARCHAR(1000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Returnname NVARCHAR(1000)
 
    SET @Returnname =
        -- Add the T-SQL statements to compute the return value here
        (
            SELECT DISTINCT SUBSTRING((
                        SELECT ', ' + gncr1.full_name AS [text()]
                        FROM gv_name_casting_report gncr1
                        WHERE gncr1.case_id = gncr2.case_id
                            AND language_id = 4
                            AND role_type_id = (select top 1 role_type_id from CASE_ROLE_TYPE where ROLE_TYPE_TYPE = @ntt)
                        ORDER BY gncr1.case_id
                        FOR XML PATH('')
                        ), 2, 1000) [nametype_name]
            FROM gv_name_casting_report gncr2
            WHERE case_id = @case_id
            )
 
    -- Return the result of the function
    RETURN @Returnname
END
GO

Code Block

language	sql
title	create TM Designation View
collapse	true

CREATE VIEW [dbo].[SRV_IF_DES_CLASSES]
AS
WITH bla AS (SELECT DISTINCT TRADE_MARK_CLASS, CASE_ID, STATE_ID
                             FROM            dbo.TRADE_MARK_CLASS_DESIGNATION)
    SELECT DISTINCT tmc.TRADE_MARK_CLASS, tmcd.STATE_ID, tmcd.CASE_ID
     FROM            dbo.TRADE_MARK_CLASS AS tmc INNER JOIN
                              dbo.TRADE_MARK_CLASS_DESIGNATION AS tmcd ON tmc.CASE_ID = tmcd.CASE_ID INNER JOIN
                              bla AS bla_2 ON tmcd.STATE_ID = bla_2.STATE_ID
     WHERE        (tmc.TRADE_MARK_CLASS NOT IN
                                  (SELECT        TRADE_MARK_CLASS
                                    FROM            bla AS bla_1
                                    WHERE        (tmcd.STATE_ID = STATE_ID)))
GO

Code Block

language	sql
title	Create Web View
collapse	true

INSERT [dbo].[PAT_WEB_LINKS] (
	[LINK_ID]
	,[LINK_TYPE]
	,[LINK_VALUE]
	,[LINK_DESCRIPTION]
	,[LINK_GLOBAL]
	,[LINK_AUTH_REQ]
	,[PCP_LINK]
	,[DC_LINK]
	)
VALUES (
	30000
	,1
	,N'http://' + (
		SELECT STRING_VALUE
		FROM PAT_CONFIGURATION
		WHERE CONFIGURATION_ID = 131
		) + ':8081/?caseref=@@'
	,N'PAVIS'
	,1
	,NULL
	,1
	,0
	)
GO

INSERT [dbo].[PAT_WEB_LINKS_ARGS] (
	[LINK_ID]
	,[LINK_ARG_ID]
	,[LINK_ARG_SEQ]
	,[LINK_ARG_TYPE]
	,[LINK_DIARY_FIELD]
	)
VALUES (
	30000
	,30000
	,1
	,1
	,NULL
	)

Code Block

language	sql
title	Create collection view
collapse	true

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[SRV_IF_BATCH]
AS


SELECT PRLJD.RENEWAL_JOB_ID
	,PRLJD.SEQUENCE_ID
	,PRLJD.CASE_ID
	,PRLJD.CASE_NUMBER
	,PRLJD.MATTER_ID
	,PRLJD.STATE_ID
	,PRLJD.RENEWAL_JOB_TYPE_ID
	,PRLJD.EVENT_SCHEME_ID
	,PRLJD.DESIGNATED_STATE_ID
	,PRLJD.CATCHWORD
	,PRLJD.APPL_DATE
	,PRLJD.RENEWAL_SEQUENCE
	,PRLJD.CASE_TYPE_ID
	,LJ.BATCH_LOGIN_ID
	,LJ.RENEWAL_JOB_TYPE_TEXT
	,LJ.LOG_DATE JOB_LOGGED
	,LJ.JOB_DONE JOB_DONE
	,LJ.SELECT_SCRITERIA_TEXT CRITERIA
FROM [dbo].[PAT_RENEWAL_LOG_JOB_DETAILS] PRLJD
join PAT_RENEWAL_LOG_JOB LJ on LJ.RENEWAL_JOB_ID = PRLJD.RENEWAL_JOB_ID

Code Block

language	sql
title	Create Log Table
collapse	true

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SRV_IF_BATCH_LOG](
	[RENEWAL_JOB_ID] [int] NOT NULL,
	[IF_BATCH_ID] [int] NOT NULL,
	[CASE_ID] [int] NOT NULL,
	[TRANSFER_SEQUENCE_ID] [int] NOT NULL,
	[SEND_DATA] [xml] NULL,
	[RECEIVE_DATA] [xml] NULL,
	[SEND_DATE] [datetime] NULL,
	[RECEIVE_DATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Code Block

language	sql
title	Create Pavis Reference
collapse	true

INSERT INTO [dbo].[REFERENCE_TYPE]
           ([REFERENCE_TYPE_ID]
           ,[REFERENCE_TYPE]
           ,[SHOW_IN_REPORT_DESIGN]
           ,[USE_AS_EBILLING_REFERENCE])
     VALUES
           (30000
           ,'PAVIS Nr.'
           ,1
           ,0)
GO

INSERT INTO [dbo].[REFERENCE_TYPE_LABEL]
           ([REFERENCE_TYPE_ID]
           ,[LANGUAGE_ID]
           ,[REFERENCE_TYPE])
     VALUES
           (30000
           ,3
           ,'PAVIS No.')
GO
INSERT INTO [dbo].[REFERENCE_TYPE_LABEL]
           ([REFERENCE_TYPE_ID]
           ,[LANGUAGE_ID]
           ,[REFERENCE_TYPE])
     VALUES
           (30000
           ,4
           ,'PAVIS Nr.')
GO

pavis-rest-0.0.13.jar

the sequence they are numbered in.

Panel

Auf dieser Seite:

Table of Contents

Zugehörige Seiten

Content by Label

showLabels	false
spaces	ST
showSpace	false
sort	title
type	page
cql	label = "documentation-space-sample" and type = "page" and space = "ST"
labels	documentation-space-sample

Page tree

Versions Compared

Old Version 5

New Version Current

Key

Prepare Patricia Database

Create Extended Name Fields

Page tree

Page History

Versions Compared

Old Version 5

New Version Current

Key

Prepare Patricia Database

Create Extended Name Fields