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
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
		,CONCAT (
			format([diary_date], 'yyyyMMdd')
			,[diary_text]
			) 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
	/*right outer join [dbo].[STATE_NAME] sn on sn.STATE_NAME = DIARY_TEXT and xml_tag = 'priority_country_ID'*/ 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]
			,[country_of_origin]
			,[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]
			,[stand_order_start]
			)) 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

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 6

New Version 7

Key

Prepare Patricia Database

Create Extended Name Fields

Page tree

Page History

Versions Compared

Old Version 6

New Version 7

Key

Prepare Patricia Database

Create Extended Name Fields