2025-0.0 - Schema Changes

• This release includes schema changes to enhance data management and improve performance across the application. (CM2-11057) (CM2-8767)

Table: FeeTaxTran

• Existing Table: Convert(AdjustFeesTax)

• New Table: FeeTaxTran

Trigger	Procedure
Create	dbutil/triggers/create/feetaxtran.p
Write	dbutil/triggers/write/feetaxtran.p

Convert Field	Format	Data Type	Data From	New Field Name	Order ID	Description
OldInteger	>>>>>>>>>9	INTEGER	Subscription.SubscriptionID	SubscriptionID	10	Subscription ID
NewInt1	>>>>>>>>>9	INTEGER	FeePayment.TranNumber	TranNumber	20	Tran number of the Payment Transaction
NewInt2	>>>>>>>>>9	INTEGER	FeePayment.FeeTranNumber	FeeTranNumber	30	Tran number of the Start/Renewal Transaction
NewChar1	x(9)	CHARACTER	TaxTran.TaxAuthorityID	TaxAuthorityID	40	Tax Authority ID
NewDate1	99/99/9999	DATE	SubscriptionTran.TranDate	TranDate	50	Payment Transaction Date
NewDeci1	>>>,>>9.99-	DECIMAL	FeePayment.FeeAmountPaid	FeeAmountPaid	60	Fee Amount Paid
NewDeci2	>>>,>>9.99-	DECIMAL	Assigned based on Calculation	FeeTaxAmount	70	Tax on Fee Amount Paid
	99/99/9999	DATE		CreateDate	80	Create Date
	x(8)	CHARACTER		Createtime	90	Create Time
	x(8)	CHARACTER		CreateUser	100	Create User
	99/99/9999	DATE		ModifyDate	110	Modify Date
	x(8)	CHARACTER		ModifyTime	120	Modify Time
	x(8)	CHARACTER		ModifyUser	130	Modify User

Index name	Index fields
FeePayment	SubscriptionID	TranNumber	TaxAuthorityID

Table: TrialSubscription

• Existing Table: Convert(InTrial)

• New Table: TrialSubscription

• A new Table TrialSubscription will be created.

Trigger	Procedure
Create	dbutil/triggers/create/trialsub.p
Write	dbutil/triggers/write/trialsub.p

Convert Field	Format	Data Type	Data From	New Field Name	Order ID	Description
OldInteger	>>>>>>>>>9	INTEGER	Subscription.SubscriptionID	SubscriptionID	10	SubscriptionID
NewInteger2	>>>>>>>>>9	INTEGER	SubscriptionTran.TranNumber	TranNumber	20	Transaction Number
NewDate1	99/99/9999	DATE	SubscriptionTran.TranDate	StartDate	30	Trial Start Date
NewDate2	99/99/9999	DATE	SubscriptionTran.TranDate	EndDate	40	Trial End Date
NewCharacter1	x(8)	CHARACTER	RateTerms.PaymentTerm	TrialTerm	50	Trial Rate Term
NewInteger1	>>>>>>>>>9	INTEGER	RateTerms.PaymentLength	TrialLength	60	Trial Rate Length
NewCharacter2	x(20)	CHARACTER	RateTerms.RateCodeID	RateCodeID	70	Trial RateCodeID
	99/99/9999	DATE		CreateDate	80
	x(8)	CHARACTER		Createtime	90
	x(8)	CHARACTER		CreateUser	100
	99/99/9999	DATE		ModifyDate	110
	x(8)	CHARACTER		ModifyTime	120
	x(8)	CHARACTER		ModifyUser	130

Index name	Index fields
TrialSub	SubscriptionID	TranNumber	StartDate	EndDate

Table: RenewalInfoFee

• Existing Table: Convert(RenewInfo-FeesTax)

• New Table: RenewalInfoFee

• The schema of existing table will be changed to add below fields.

Convert Field	Format	Data Type	Extent	New Field Name	Order ID	Description
NewDeci1	>>>,>>9.99-	DECIMAL	4	CityFeeTaxAmount	200	City Tax on Fee Amount
NewDeci2	>>>,>>9.99-	DECIMAL	4	CountyFeeTaxAmount	210	County Tax on Fee Amount
NewDeci3	>>>,>>9.99-	DECIMAL	4	StateFeeTaxAmount	220	State Tax on Fee Amount
NewDeci4	>>>,>>9.99-	DECIMAL	4	CountryFeeTaxAmount	230	Country Tax on Fee Amount

Table: AutoRenewTerm

• Existing Table: ConvertMany(AutoRenewTerm)

• New Table: AutoRenewTerm

• The schema of existing table will be changed to add below fields.

Convert Field	Format	Data Type	Data From	New Field Name	Order ID	Description	Comment
NewInteger1	>>>>>>>>>9	INTEGER	AutoRenewTerm.RenewDays	RouteRenewDays	80	Route Renew Days	Existing field, only renamed
NewInteger2	>>>>>>>>>9	INTEGER	AutoRenewTerm.RenewDays	MailRenewDays	140	Mail Renew Days
NewInteger3	>>>>>>>>>9	INTEGER	AutoRenewTerm.RenewDays	OnlineRenewDays	150	Online Renew Days
NewInteger4	>>>>>>>>>9	INTEGER	AutoRenewTerm.RenewDays	TrialRenewDays	160	Trial Renew Days
NewInteger5	>>>>>>>>>9	INTEGER	AutoRenewTerm.GraceDays	RouteGraceDays	90	Route Renew Days	Existing field, only renamed
NewInteger6	>>>>>>>>>9	INTEGER	AutoRenewTerm.GraceDays	MailGraceDays	170	Mail Renew Days
NewInteger7	>>>>>>>>>9	INTEGER	AutoRenewTerm.GraceDays	OnlineGraceDays	180	Online Renew Days
NewInteger8	>>>>>>>>>9	INTEGER	AutoRenewTerm.GraceDays	TrialGraceDays	190	Trial Renew Days
NewInteger10	>>>>>>>>>9	INTEGER	AutoRenewTerm.NoticeDays	TrialNoticeDays	200	Trial Notice Days

Table: DeliveryScheduleBonusDay

• A new field LabelGroupID needs to be added in the table DeliveryScheduleBonusDay, currently we store it as the second parameter delimited by | in the field DeliveryMethod.

Field Name	Data Type	Format	Data From	Order ID	Description
LabelGroupID	CHARACTER	x(8)	LabelGroup.LabelGroupID	160	LabelGroupID

Table: DeliveryScheduleBonusDayAudit

• A new field LabelGroupID will be added in the table DeliveryScheduleBonusDayAudit.

Field Name	Data Type	Format	Order ID	Description
LabelGroupID	CHARACTER	x(8)	160	LabelGroupID

Table: LabelGroupDraw

• New fields will be added in the existing table LabelGroupDraw

Field Name	Data Type	Format	Order ID	Description
IsBonusDay	Logical	y/n	100	Indicates whether it is Bonus Day.
UnpaidMailDraw	Character	>>>,>>9	110	Required in future to store Mail Draws

Table: RealTimeSync (New)

Trigger	Procedure
Create	dbutil/triggers/create/realtimesync.p
Write	dbutil/triggers/write/realtimesync.p

Format	Data Type	Data From	New Field Name	Order ID	Description
>>>>>>>>>9	INTEGER	Subscription.SubscriptionID	SubscriptionID	10	SubscriptionID
>>>>>>>>>9	INTEGER	SubscriptonTran.TranNumber	TranNumber	20	Transaction Number
99/99/9999	DATE	SubscriptonTran.TranDate	TranDate	30	Transaction Date
x(12)	CHARACTER	SubscriptonTran.TranTypeID	TranTypeID	40	Transaction TypeID
y/n	LOGICAL	Push Sent	Sent	60	Push Sent
x(12)	CHARACTER	Create or Modify	EventType	70	Event Type
99/99/9999	DATE		CreateDate	80	Create Date
x(8)	CHARACTER		Createtime	90	Create Time
x(8)	CHARACTER		CreateUser	100	Create User
99/99/9999	DATE		ModifyDate	110	Modify Date
x(8)	CHARACTER		ModifyTime	120	Modify Time
x(8)	CHARACTER		ModifyUser	130	ModifyUser

Index name	Index fields
SubscriptionTran	SubscriptionID	TranNumber
SubscriptionType	TranTypeID

Table: SingleCopyPublication

• Existing Table: Convert(iServicesRoute)

• New Table: SingleCopyPublication

• The schema of existing table will be changed to add below fields.

Convert	Format	Data Type	Data From	New Field Name	Order ID	Description
NewCharacter1	x(8)	CHARACTER	DrawAdjustCode	DrawAdjustCode	110	DrawAdjustCode
NewCharacter2	x(8)	CHARACTER	UnauthDrawAdjustCode	UnauthDrawAdjustCode	120	UnauthDrawAdjustCode
NewLogical1	y/n	LOGICAL	AllowiServicesReturns	AllowiServicesReturns	130	AllowiServicesReturns
NewLogical2	y/n	LOGICAL	AllowiServicesDrawChgs	AllowiServicesDrawChgs	140	AllowiServicesDrawChgs

Table: UserProfileCR

• The schema of existing table UserProfileCR will be changed to add the fields below. Currently, they are not stored anywhere.

Trigger	Procedure
Create	dbutil/triggers/create/userprofilecr.p
Write	dbutil/triggers/write/userprofilecr.p

Field Name	Data Type	Format	Order ID	Description
StartDate	DATE	99/99/9999	100	Start Date
EndDate	DATE	99/99/9999	110	End Date
CreateDate	DATE	99/99/9999	120	Create Date
CreateTime	character	x(8)	130	Create Time
CreateUser	character	x(8)	140	Create User
ModifyDate	DATE	99/99/9999	150	Modify Date
ModifyTime	character	x(8)	160	Modify Time
ModifyUser	character	x(8)	170	Modify User

Table: ChurnExportControl (New)

• This table will be used to store the information of the data being sent to Common Data Platform (CDP) server.

Trigger	Procedure
Create	dbutil/triggers/create/churnexpctrl.p
Write	dbutil/triggers/write/churnexpctrl.p

Field Name	Data Type	Format	Order ID	Description
ExportCutOffDate	DATE	99/99/9999	10	Export Cut-Off Date
ExportStatus	CHARACTER	X(15)	20	Export Status
ProductID	CHARACTER	x(8)	30	Product ID
ExportFile	CHARACTER	X(60)	40	Export File
IsExternalSystem?	logical	y/n	70	Indicates whether it is External System.
ExportSubscriptions	Integer	>>>>>>>>9	80	Export Subscriptions
ExportStart	datetime	99/99/9999 HH:MM:SS.SSS	90	Export Start
ExportEnd	datetime	99/99/9999 HH:MM:SS.SSS	100	Export End
CreateDate	DATE	99/99/9999	110	Create Date
CreateTime	character	x(8)	120	Create Time
CreateUser	character	x(8)	130	Create User
ModifyDate	DATE	99/99/9999	140	Modify Date
ModifyTime	character	x(8)	150	Modify Time
ModifyUser	character	x(8)	160	Modify User

Index Name	Index Fields
ChurnExportControl	ProductID	ExportStatus	ExportCutOffDate	ExportStart	ExportEnd

Table: ChurnSubscription (New)

• This table will be used to store the response received from Common Data Platform (CDP) server.

Trigger	Procedure
Create	dbutil/triggers/create/churnsubs.p
Write	dbutil/triggers/write/churnsubs.p

Field Name	Data Type	Format	Order ID	Description
SubscriptionID	Integer	>>>>>>>>>9	10	SubscriptionID
ProductID	character	X(8)	20	ProductID
DeliveryMethod	character	x(8)	30	Delivery Method
DeliveryScheduleID	character	X(8)	40	DeliveryScheduleID
RateCodeID	character	X(20)	50	RateCodeID
ChurnLevel	integer	>>9	60	Churn Level
ChurnScore	decimal	>>>9.99	70	Churn Score
ChurnDate	DATE	99/99/9999	80	Churn Date
CreateDate	DATE	99/99/9999	90	CreateDate
CreateTime	character	x(8)	100	CreateTime
CreateUser
ModifyDate	DATE	99/99/9999	110	ModifyDate
ModifyTime	character	x(8)	120	ModifyTime
ModifyUser

Index Name	Index Fields
ChurnSubscription	SubscriptionID
ChurnProduct	ProductID	DeliveryMethod	DeliveryScheduleID
ChurnScore	SubscriptionID	ChurnScore

Table: ChurnLevel (New)

• This setup table will be used to display customer satisfaction icon in customer service based on churn range.

Trigger	Procedure
Create	dbutil/triggers/create/churnlevel.p
Write	dbutil/triggers/write/churnlevel.p

Field Name	Data type	Format	Order ID	Description
ProductID	Character	x(8)	10	Product ID
ChurnLevel	Integer	>>9	20	Churn Level
ChurnMinValue	Decimal	>>>9.99	30	Minimum Value of Churn
ChurnMaxValue	Decimal	>>>9.99	40	Maximum Value of Churn
IconLocation	Character	X(60)	50	Location of Icon
Description	Character	X(30)	60	Description
StartDate	Date	99/99/9999	70	Start Date
EndDate	Date	99/99/9999	80	End Date
CreateDate	Date	99/99/9999	90	Create Date
CreateTime	Character	x(8)	100	Create Time
CreateUser
ModifyDate	Date	99/99/9999	110	Modify Date
ModifyTime	Character	x(8)	120	Modify Time
ModifyUser

Index Name	Index Fields
ChurnLevel	ProductID	ChurnLevel	StartDate	EndDate

Table: ChurnOffer (New)

• This setup table will be used to store the offers required to stop the churn.

Trigger	Procedure
Create	dbutil/triggers/create/churnoffer.p
Write	dbutil/triggers/write/churnoffer.p

Field Name	Data type	Format	Extent	Order ID	Description
ProductID	Character	x(8)			Product ID
Priority	Integer	>>9			Priority
DeliveryMethod	Character	x(8)			Delivery Method
DeliveryScheduleID	Character	X(8)			Delivery Schedule
BillingMethod	Character	x(15)			Billing Method
RateCodeID	Character	x(20)			Rate Code ID
CampaignCode	Character	x(30)			Campaign Code
ChurnLevel	Logical	y/n	10		Churn Level
Script	Character	X(200)			Script
NextTerm	Logical	y/n			Next Term
StartDate	Date	99/99/9999			Start Date
EndDate	Date	99/99/9999			End Date
ChurnCharacter	Character	x(30)	5		Extra Character fields
ChurnInteger	Integer	>>>>>>9-	5		Extra Integer fields
ChurnLogical	Logical	y/n	5		Extra Logical fields
ChurnDate	Date	99/99/99	5		Extra Date fields
ChurnDecimal	Decimal	>,>>>,>>9.9999-	5		Extra Decimal fields
CreateDate	Date	99/99/9999			Create Date
CreateTime	Character	x(8)			Create Time
CreateUser	Character	x(8)
ModifyDate	Date	99/99/9999			Modify Date
ModifyTime	Character	x(8)			Modify Time
ModifyUser	Character	x(8)

Index Name	Index Fields
ChurnOffer	ProductID	Priority	DeliveryMethod	DeliveryScheduleID	StartDate	EndDate

Table: SubscriptionPayment

• A new field SplitTransactionID will be introduced in the existing table SubscriptionPayment.

Field Name	Format	Data Type	Order ID	Description
SplitTransactionID	X(40)	Character	250	Split Transaction ID

Table: Publication

• A new field PublicationType will be introduced in the existing table Publication

Field Name	Format	Data Type	Order ID	Description
PublicationType	X(15)	Character	690	Publication Type

Index Name	Index Field
PublicationType	PublicationType

Table: ItemDetail (New)

• A new Table ItemDetail will be created.

Field Name	Format	Data type	Order ID	Description
ProductID	x(8)	Character	10	Product ID
PublicationType	x(15)	Character	20	Publication Type
ItemLogical	yes/no	Logical	30	Item Logical
ItemCharacter	x(20)	Character	40	Item Character
ItemInteger	>>>>>>>>>9-	Integer	50	Item Integer
ItemDate	99/99/99	Date	60	Item Date
ItemDecimal	>,>>>,>>9.9999-	Deci-4	70	Item Decimal
OtherLogical	yes/no	Logi[10]	80	Other Logical
OtherCharacter	x(30)	Char[10]	90	Other Character
OtherInteger	>>>>>>9-	Integer [10]	100	Other Integer
OtherDate	99/99/99	Date[10]	110	Other Date
OtherDecimal	>,>>>,>>9.9999-	Deci-4[10]	120	Other Decimal

Index Name	Index Fields
Publication	ProductID	PublicationType
ItemCharacter	ItemCharacter
ItemInteger	ItemInteger

Table: Package (New)

• A new Table Package will be created.

Field Name	Format	Data type	Order ID	Description
PackageID	x(15)	Character	10	Package ID
Description	x(30)	Character	20	Description
StartDate	99/99/9999	Date	30	Start Date
EndDate	99/99/9999	Date	40	EndDate

Index Name	Index Fields
Package	PackageID	StartDate	EndDate

Table: PackageProductMix (New)

• A new Table PackageProductMix will be created.

Field Name	Format	Data Type	Order ID	Description
PackageID	x(15)	Character	10	Package ID
ProductID	x(8)	Character	20	Product ID
DeliveryMethod	x(8)	Character	30	Delivery Method
EditionID	x(8)	Character	40	Edition ID
DeliveryScheduleID	x(8)	Character	50	Delivery Schedule ID
IsPrimary	y/n	Logical	60	Indicates whether it is primary.
PrimaryDigital	y/n	Logical	70	Indicates whether it is primary digital
RequireAAM	yes/no	Logical	90	Indicates whether it requires AAM
TaxGLAccountID	x(40)	Character	100	Tax GL Account ID
MixElementID	>>>>>>>>>9	Integer	110	Mix Element ID
TaxProductID	x(8)	Character	120	Tax Product ID
TaxDeliveryMethod	x(8)	Character	130	Tax Delivery Method

Index Name	Index Fields
PackageProductMix	PackageID	ProductID	DeliveryMethod	EditionID

Table: SubscriptionPackage (New)

• A new Table SubscriptionPackage will be created.

Field Name	Format	Data type	Order ID	Description
PackageID	x(15)	Character	10	PackageID
PackageSubscriptionID	>>>>>>>>>9	Integer	20	PackageSubscriptionID
SubscriptionID	>>>>>>>>>9	Integer	30	SubscriptionID
StartDate	99/99/9999	Date	40	StartDate
EndDate	99/99/9999	Date	50	EndDate

Index Name	Index Fields
SubscriptionPackage	PackageID	SubscriptionID	StartDate	EndDate