- Created by Serviva Service Desk, last modified on Sept 23, 2021
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 4 Next »
Prepare Patricia Database
- In order to have Data available that is neccesary for the Pavis Interface, the Patricia Database needs to be enhanced.
- 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
Create Login Data Expand source
-- 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
Create Diary Field Expand source
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
Create Diary Mapping and View Expand source
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
Create Name function for XML Expand source
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
Create Owner cost acct. function Expand source
/****** 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
create TM Designation View Expand source
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
Create Web View Expand source
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 )
Create collection view Expand source
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
Create Log Table Expand source
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
Create Pavis Reference Expand source
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
Auf dieser Seite:
Zugehörige Seiten
-
Page:
-
Page:
-
Page:
- No labels