Publication Fold Procedures

** This page is a work in progress **

The purpose of a publication fold is to move customer subscriptions from one publication to another. This typically occurs when a publisher decides to no longer fulfill a particular publication, but the company is still liable for fulfilling a service that customers have paid for. The pub fold process cancels the initial subscription, and creates a new subscription under the new pub, with a rate equal to the remaining liability on the initial subscription.

The term “pub fold” should not be used to denote the shut down of a publication where all subscriptions are cancelled. Generally speaking, we should never do this except in special circumstances, as it causes an immediate large tax liability for the client at the time of cancellation.

This document assumes an intermediate working knowledge of the Advantage application and CIR processing.

FOLD PROCEDURES

When the client requests that a pub be folded, there are some steps that can immediately be taken (get approval from the client first anyway). Generally speaking, we will want to stop auto-renews, stop new orders, remove the publication from any memberships where it is a baby pub, and stop issue pulls.
Then, we run the fold processes. The outline of the ‘day of’ execution of a fold should look something like this:

1. run SQL to create backup tables

2. run LST356 with select FMR[PUB]

3. create and close control group

4. run ARP310 with select [PUB]_FLD_ARP310_BIL_SUSP

5. run ARP316 with select [PUB]_FLD_ARP316_BILL_SUSP

6. run CIR137 with select [PUB]_FLD_CANX

   1. check control group w/ sql

   2. check sql for any future terms

   3. re-run CIR137

7. clear installment billing codes via SQL update

8. run ARP320 with select [PUB]_FLD_ARP320_DBTS

9. run Fold Group SQL to populate work tables

10. run UPDATE DMO SQL

11. unblock Customers

12. run CIR915 with select V[PUB]FLD

13. re-block Customers

14. check control group w/ sql

15. run Auto Renew Update SQL

16. balance and release control group

17. cleanup

    1. extensions / comps

    2. ghost order resolution

    3. mod mig

    4. writeoffs for manual fold customers
       
       

FOLD PROCEDURE DETAILS

DISCONTINUE AUTO RENEW

To discontinue auto-renews: At CIRPUB, in the Demographics section at the bottom of the pub setup, add the value 'D' to the first field (labelled “Electronic Cancellation Rate”). Our renewal selects will exclude any publications with this value in that field.

Open

STOP NEW ORDERS

To stop new orders, you’ll set the publication saleable flag to “R” for Returns/Cancels only.

Open

CREATE BACKUP TABLES (OPTIONAL)

Backup Into a Work Table

CIRSUB

ARPDDD

ARPRDD

ARPEPI

CREATE SELECTS

There are 6 processes that use selects during a fold process, including the optional list creation. You can create the selects as you go through the process, or you can copy and create them all at once, and just review and check counts as you go through the process. If you decide to create them all at once, you can use the following naming conventions, and also search for these selects and copy them. Don’t forget to go into the select SQL and update the publication code!

CREATE LIST OF SUBSCRIPTIONS IN SCOPE (OPTIONAL)

Create Select

You can copy FMRTSS.

Navigate to LSTLTT Customer Lists - Create LIST - use below as a template, just check the currency and the owning org.

Run LST356 with select FMR[PUB]

REMOVE FROM MEMBERSHIPS

To remove the publication from any memberships, you will remove all promo choices for the publication that exist on the membership Z promo code at CPNOFR.

Next you will remove the publication from the Member Participation Elements and Membership Category to Element Cross References tabs at MBRORG.

The following SQL queries can be used to identify each record that will need to be removed:

This will identify the promotion items that have the subscription as a membership element. You can remove the publication choices from the offers identified by this script:

You can use the following queries to identify the membership participation and category elements to cross reference at the MBRORG view in the Advantage UI:

REMOVE FROM AUTO RENEW (JOBSTREAM) (CANCEL CYCLE ENDS) AND RESCHEDULE

To cancel any further cycle ends, find any scheduled job streams or standalone requests and either delete them from the jobstream and reschedule, or simply delete the jobstream from the schedule if it is the only publication in that jobstream (or is scheduled outside of a jobstream).

Use the below SQL to identify the scheduled jobstreams or processes for the cycle end (issue pull processes) for the publication you are folding.

Note: you MUST reschedule the jobstream for the change you made to take effect.

Not all pubs are part of an Auto Renew Jobstream, but most are.

You will need to go into the Jobstream and remove the steps for Auto Renew.

Once you have identified the sequences you need to go into the Jobstream and remove them. Open up the Jobstream, go to the steps identified, and delete them.

When you change the Jobstream, Advantage will prompt you to update any existing schedules for that Jobstream. You will need to know the current existing schedule, which you can find by opening up a new instance of Advantage, navigating to the “current processes” view, searching for the Jobstream in the “waiting process requests” section, Right Clicking and selecting “Change Waiting Request Schedule”.

Now that you know what the schedule is, you can update the Jobstream for the same schedule as the one that currently exits. Make sure the old Jobstream is deleted.

CANCEL SUBSCRIPTIONS THAT WILL NOT BE FOLDED

You’ll want to check with the client to find out when they have communicated to customers that service will be discontinued. We don’t want to cancel subscriptions that people are still expecting to have access to for another week.

Cancel subscriptions that are not being folded. The CIR915 fold process will process cancellations, but I find it is more effective to cancel subscriptions that will not be folded prior to running the CIR915, as it gives you a smaller data set to work with and less subscriptions to process means a faster CIR915 runtime.

Unpaid subscriptions should be cancelled using the ARP310 (non-installment invoices) and ARP316 (installment invoices). Unlike the CIR137, this will perform a non-pay cancellation and write off the open A/R.

Sample SQL query for each:

CANCELLING: Non-Installment Unpaid Invoices (ARP310)

For the ARP310, you can model your select set on GFARP310 or TDARP310.
An important NOTE: the ARP310 auto releases the control group. If we need to spot check, you can pause posting, run, check, and then resume posting.

To check your select SQL, you’ll want to look for the circulation status (commented out in the SQL below) and double check to see if any are NOT in status = ‘B' for bill-suspended. If they are in a different status, you’ll want to investigate why. It still might be okay to cancel them, but they should be checked.

Once you’ve checked the SQL and updated the select, you can run the ARP310 process. You should not need to update any of the process settings:

CANCELLING: Installment Billing Unpaid Invoices (ARP316)

For the ARP316, you’ll want to do the same thing: Review the SQL, look at the CRC_STS. For this one, the CRC status being R is not usually a problem, since it’s usually an unpaid future installment, and when it is cancelled, it will pro-rate into the current term, and that value is what will be folded into the new publication.

Once you’ve checked the SQL you can use it to update your select set. Again you can model it on GFARP316 or TDARP316.

Here is the SQL you can use for your select set, and to examine the results:

Again, you should not need to change any of the default process values:

CANCELLING: Undeliverables, Suspensions, Subs that are part of memberships, and COMPS

Any other cancellations for paid or comp subscriptions can be processed with the CIR137.

Again, you can look for a and recycle or copy the TDCIR137 select set.

Below is the select for the CIR137. You may have to run this process more than once, as when you cancel the current term, future terms will roll in to be the active current term, and those will qualify for future runs of this process.

As a best practice, you should use the following process options:

No Credit

UNCHECK Refund Flag

UNCHECK Cancel All Terms

Cancel Reason: CFC

CHECK Auto Generate Control Group

UNCHECK Auto Release Control Group

Once you’ve run this process, check the control group using the SQL provided below. You need to make sure costs are zero and the counts line up.
Use a version of the following SQL - you can either look at the reports to find the control group, or look for those that start with ZCIR and are from today.
(maybe fix the below SQL to have a ‘like zcir%' part to help find the control group)

If you don't select the auto release group process option, you will have to manually kick off an MST210 process, or wait till one of the automatically scheduled ones starts and finishes.

Once these cancellations post, you should run the SQL again. What will probably happen is that the current terms will be cancelled, and future terms will roll in.

***RUN THE CIR137 SQL SELECT AGAIN***

CLEAR INSTALLMENT BILLING CODES

You need to clear the installment billing codes from any subscriptions. First, search to see if htere are any, and if there are, submit a SQL change request to have the codes set to blank.
You can also run an OSM upload to do this without a SQL change request (link to separate instructions will go here, WIP).

For under 5,000 records, you can also do an OSM update that will set the installment billing code to “NON”.

This way, you don’t have to wait for SQL update approval.

When you do this, Put the file in this folder: \maui\PubProd\Batch\MST125\OPERATIONS - DO NOT run the MST125 Process - just let the OSMOPS jobstream pick this up.

RESOLVE UNPAID DEBITS ON SUBSCRIPTIONS

Use this to find the debits

Use this to be the SQL for the Select

(fix this and comment out data? )

RUN the ARP320 with the select you created (use GFARP320 or TDARP320 as a model)

Process Options

Uncheck report only

Auto generate CTRL GRP

DO NOT auto release

0 for days

0 for low amount

9999 for high amount

it will put it in a ZARP control group

SQL ANALYSIS: GROUP CUSTOMERS BY FOLD ACTIONS

A fold usually consists of 3 or 4 groups that will be folded. You can use the following SQL to identify each group. There may be other groups specified in the fold instructions.

1 - they have the folding pub as a Lifetime, and they’ll be getting the new pub in lifetime.

2 - they have the folding pub not as a lifetime, they already have the new pub as renewal flag C, and will be getting more of it.

3 - they have the folding pub not as a lifetime, and they do not have the new pub on Auto-Renew, so will be getting the new pub as a renewal flag Y.

4 - they have the folding pub paid, but also have the new pub as a lifetime already. in this case we will usually cancel the folding pub and leave a partial credit on the customer’s account. sometimes, we will fold them into a second new pub.

You’ll want to start by building a work table. The 1st group query will be a “select into table” and the rst will be “insert into table select”

At the bottom is a SQL to count the records in each group.

MARKETING SETUP

CAMPAIGN CODE

Navigate to the Advantage Campaigns view, CPNCPN. The format for the Campaign Code will be V, PUB-CODE (the one folding), Year/Month, and then A (this is for ‘first mailing of the month’ but is just being used as a default value).

The cost accumulation method will be set to “P” promotion level.

OFFER CODE

Make sure that the offer has the 'additional items' checkbox checked

V(pubcode)FOLD

Set the Description to “Folding [PUB] into [PUB]”

Set the Owning Organization to the one of the pub you are folding.

Set the Credit Policy to CPT001

CHOCIES

First, if you have not already, look up the number of issues per year for the folding pub, and the new pub. If they’re the same, you don’t have to make any changes to your select set for the CIR915. You need to know this number when you set up the choices, since this determines the values for term length and next term.

Choices will match Group Number. As in the Choice ending “001” will be for Group 1, etc.

The naming convention will be that the Choice has the name of the FOLDING pub, and will be a choice to purchase the pub you’re folding INTO.

You can copy codes from a previous fold. Copy, then select the pub you’re folding INTO for the publication code, and then name the choice [PUB]00# (number corresponding to group)

For example:

SUN001

SUN002

SUN003

SUN004 (this is an example where there is a 4th group, where everyone who has SUN, who already has TPW LIFE, will get BKE LIFE as a Membership)

Add to Offer

Once you’ve created the choices, add them to the OFFER code.

First, you’ll set the offer to accept additional items. To do this, select the Promotion Items tab under the offer, then on the far right select the options control and select “Update Promotion Item Setup”. Check “Additional Items”.

Once that’s done, you can click the ‘add’ control and add the choices you made to the offer.

PROMOTION CODE

The promotion code will follow the same format: V[PUB]FOLD

CREATE THE CIR915 SELCT

You can copy this select from an existing select.
The naming convention for the Select Set will match what we’ve been doing previously, so “V[PUB]FOLD”.

You’ll want a select level for each group. The level options should be set to have the promotion code and choice appropriate for the group.

If the pubs you are folding have different numbers of issues per year (ex: one has 12, the other has 52) you will need to populate the ‘term multiplier’ value on the select level options. To fold from 12 to 52 you’ll use a term multiplier of ‘4', and from 52 to 12 you’ll use ‘.25’.

The condition for each level will be “subscription demographic data is equal to ‘01’ and so on for each group.

UPDATE SUB DMO DATA - SQL or OSM

On the day of the fold, run your group select SQL and update the work table. Then send a SQL update request to update the SUB demographic data to match the DMO data of the work table.

If your number of records is less than 5,000 or if you have a lot of time, you can update the subscription demographics using an OSM update instead of a SQL update. That way you don’t have to wait for SQL update approval.

When you do this, Put the file in this folder: \\networkstorage.adv-prd.us-east-1.ota.14west.cloud\129-AdvApp\PubProd\Batch\MST125\OPERATIONS - DO NOT run the MST125 Process - just let the OSMOPS jobstream pick this up.

CREATE CONTROL GROUP

First you need to create the control group. The format is going to be “SA[PUB]F”.

You need to make sure the control group is closed.

When you create the control group, if a dialog box pops up asking what view you’d like to navigate to, you can escape out of that dialog box, and it will have created a closed control group.

Otherwise you’ll have to find it and right click it and actively close it.

Once the control group is created, you need to protect it by right clicking and selecting protect.

UNBLOCK CUSTOMERS

In any fold there will be a selection of customers who will be Blocked, either at the customer level, or the card level.

There are 3 processes (CDSFL1, CDSFL2, and CDSFL3) that look at a work table, and run a stored procedure to run a SQL update.

There is a jobstream that contains all three processes called “FOLDBLKS”

You’ll need to search for customers who are blocked on one of the 3 tables, move them to the appropriate work table, and then run the jobstream. Use the following steps to clear blocked customers.

Check that Tables are Empty

If not, contact the person whose name is in the user field and ask them if it’s okay to delete the records in them. If it is, move to the “delete records from tables” step.

Update Tables with New and Old Values

Run Jobstream FOLDBLKS

Run this jobstream. It will send out email alerts that this is being done.

RUN CIR915

For system options, enter the select set you created, leave or set the control group to the current date, and enter the control group ID that you created earlier. The Campaign Code should default to MXXX.

REBLOCK CUSTOMERS

Reinstate Blocks

Run Jobstream FOLDBLKS to Re-Block Customers

Run this jobstream. It will send out email alerts that this is being done.

Delete Records from Work Tables

This SQL only works when the change date is less than todays date.

RUN THE CONTROL GROUP CHECK SQL

There are a few queries you can run to look for problems with any of the fold transactions.

Check to see that the proportion of each choice used is about what I'm expecting, and that the renewal flag matches for that choice.

Check to see if the cost offset is off.

**DO NOT RELEASE THE CONTROL GROUP TILL YOU RUN THE NEXT RATE NEXT TERM SQL UPDATE**

RUN THE AUTO RENEW SQL UPDATE

For Group 2, there next rate for the C flags need to be updated.

There are 2 parts to this. The first populates a work table. The 2nd is a SQL update you’ll run by requesting the Process called CDSFL4.

Run the below SQL to populate the work table. Then request the CDSFL4 process.

SQL UPDATE REQUEST :

This is the SQL that runs when you request the CDSFL4 process.

You can re-run the control group query, but add the detail to see the next rate and term update:
The SQL is updating the next rate and next term on the CIRDTL record on the NEW subscription, so that’s what you want to make sure has changed.

BALANCE AND RELEASE CONTROL GROUP

If the control group check SQL checks out and the next rate next term update has been run, you can balance and release the control group.

RUN CYCLE END

This will most likely be the CIR4MQ for the folding pub.

Cycle End Mode: U

Liability Report: Y

Produce Labels: Y

FINAL QUERY

SET PUB TO INACTIVE AT INVDAT

Set Saleable Flag to “N”

Set Product Family to “NO LONGER ACTIVE”

CLEANUP

By cleanup, we mean that there are almost always some subscriptions that fall out of the established criteria for the groups we are folding.

Each of these subscriptions usually requires some amount of individual attention, meaning that a MOD MIG is often used, or it is determined they can be cancelled, or comped, or whatever other solution leads to the resolution and eventual cancellation of the folding subscription.

SEND CREDIT FILE

If there was a ‘Group 4’ where we cancelled customers who has the New Pub already LIFE and left a credit on the account, you can use the following SQL to grab that info and put it in an excel file to send to the affilaite.

MOD MIG

When doing a MOD MIG, you generally want to have the Billing Code set to P (paid up subscription).
On the Operation Dialog Box, you’ll set the publication code to the NEW subscription, use the fold promotion code and the appropriate choice and sub type, and then the ‘rate overridden’ box should be checked.

You’ll set the RATE = Remaining Liability, and TERM = final issues to go

NOTE: sometimes when you do this, there will be a remaining amount because of the way taxes are charged. In this case, proceed with the MOD MIG and do a writeoff later.

WRITEOFF

To do a writeoff at ARPCAR, follow these steps:

Search for the customer. Identify the remaining amount transaction line. R-click and “Add Payment”. Skip the Saved Payment Methods dialog box.

Populate the “Payment Type” field with “Writeoff”

Populate the “Amount” field with the amount of the writeoff. You can leave the Number blank.

Click “OK” and the unpaid application should be paid.

COMPS

https://14west.atlassian.net/wiki/spaces/ADV/pages/47104851972

GHOST ORDER

Sometimes you’ll get an error on a pub mod where the CUS or MIG or other modification is blocked by a transaction from an old control group, and when you go to the control group, the order does not exist.

To fix this you have to delete this order from a few tables and then try the modification again. Usually the only 2 tables you need will be MSTDTL and CIRDTL, but it’s worth checking PROORD and MSTORD.

EXTENSIONS

Use the below SQL to generate Term Extensions via OSM for current and future Terms

Once you have the SQL file saved to .xml format, you can put it here: \\maui\advprod\PubProd\Batch\MST125\OPERATIONS and it will get picked up and posted.

Do NOT run the MST125 - just let the file get picked up by the OSMOPS jobstream.

You should un-check “Auto Release Control Group” and then manually check that your control group looks correct. The control group will start with the vendor name (in the above case it’s OPS).

NOTES

Uploading Orders using FileGen and the CIR125

CIR125 Process Options

Control Groups for the CIR125 process start with ZCR

Form Letter for Fold Ticket

I’m starting discovery on this fold, and I can start this on [DATE]. I have a few questions and want to review the general procedure for folds:

1. I’m planning on cancelling any current subscriptions which meet the following criteria:

   1. status is ‘Undeliverable’ or ‘Suspended’ or ‘Temp Suspended’

   2. part of a membership with rate = $0

   3. issues to go = 0

   4. status is comp, but not a lifetime

2. Customers are grouped into 4 categories that we take the following actions on:

   1. Group 1 - Folding Pub status Paid, type is LIFE or renewal flag A, does not have New Pub LIFE.

      1. these customers will get New Pub LIFE.

   2. Group 2 - Folding Pub status Paid, not LIFE, and has New Pub with renewal flag C already, OR has Folding Pub with renewal flag C and NO New Pub subscription.

      1. these customers get remaining issues of Folding Pub folded into New Pub with renewal flag C.

   3. Group 3 - Folding Pub Paid, not LIFE, has New Pub in renewal flag not = C, or no New Pub.

      1. these customers get remaining issues of Folding Pub folded into New Pub w/ renewal flag Y.

   4. Group 4 - Folding Pub Paid, but already have New Pub LIFE.

      1. for these customers we will cancel Folding Pub and leave the credit on the account and your customer service can contact the customer about what they want.

      2. we’ll send you an excel file with the customer information and the credit amount they have after the fold.

Take a look at these steps/groups and let me know if you have questions and I’ll start working on the rough counts for each group. It’s possible there will not be any group 4, but I’ll let you know. That said, the counts will change once we do some cancels in step 1.

I am going to shoot for having this ready to be done by EOD on the [DATE], but that depends on when you can have communications ready for customers.

Let me know if you have questions or comments.

leftovers usually have lifetime for both, and those we can cancel.
add CVI records (if necessary)
do extensions (if necessary)
Cancel comps (if necessary)
update member cat (if MBR)
add baby pubs (if MBR)
Insert ARPEPA_M records (if necessary)