{"type":"doc","content":[{"type":"paragraph"},{"type":"paragraph","content":[{"text":"** This page is a work in progress **","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"This document assumes an intermediate working knowledge of the Advantage application and CIR processing.","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"paragraph"},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"minLevel":{"value":"1"},"maxLevel":{"value":"7"}},"macroMetadata":{"macroId":{"value":"0780aeeb1336be0e08e24d44367ec733"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"267b5d89-8c5d-460f-aa7c-b7a520055bd4"}},{"type":"paragraph"},{"type":"paragraph"},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"FOLD PROCEDURES","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"},{"type":"hardBreak"},{"text":"Then, we run the fold processes. The outline of the ‘day of’ execution of a fold should look something like this: ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run SQL to create backup tables","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run LST356 with select FMR[PUB]","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"create and close control group","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run ARP310 with select [PUB]_FLD_ARP310_BIL_SUSP","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run ARP316 with select [PUB]_FLD_ARP316_BILL_SUSP","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run CIR137 with select [PUB]_FLD_CANX","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"check control group w/ sql","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"check sql for any future terms","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"re-run CIR137","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"clear installment billing codes via SQL update","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run ARP320 with select [PUB]_FLD_ARP320_DBTS","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run Fold Group SQL to populate work tables","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run UPDATE DMO SQL","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"unblock Customers","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run CIR915 with select V[PUB]FLD","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"re-block Customers","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"check control group w/ sql","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"run Auto Renew Update SQL","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"balance and release control group","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"cleanup","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"extensions / comps","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"ghost order resolution","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"mod mig","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"writeoffs for manual fold customers","type":"text"},{"type":"hardBreak"}]}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"FOLD PROCEDURE DETAILS","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"DISCONTINUE AUTO RENEW","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":436,"id":"e9650530-d401-4ea2-bf02-1ae9ac79cf76","collection":"contentId-47591526081","type":"file","height":146}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"STOP NEW ORDERS","type":"text"}]},{"type":"paragraph","content":[{"text":"To stop new orders, you’ll set the publication saleable flag to “R” for Returns/Cancels only. ","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":583,"id":"620a5da1-7724-4ccc-a4a6-c1521a46453d","collection":"contentId-47591526081","type":"file","height":242}}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CREATE BACKUP TABLES (OPTIONAL)","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Backup Into a Work Table","type":"text"}]},{"type":"paragraph","content":[{"text":"CIRSUB ","type":"text"}]},{"type":"codeBlock","content":[{"text":"select sub.*\ninto advdbwrk.dbo.FMR_TSS_CIRSUB\nfrom cirsub_m sub\nwhere sub.pub_cde = 'TSS'\nand sub.crc_sts not in ('C', 'E', 'N')","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"ARPDDD","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct sub.CTM_NBR, ddd.*\ninto advdbwrk.dbo.FMR_TSS_ARPDDD\nfrom cirsub_m sub\njoin ARPACI_M aci on aci.ACI_ID = sub.aci_id and aci.DDI_IDN > '000000000000'\njoin ARPDDD_M ddd on aci.DDI_IDN = ddd.DDI_IDN and ddd.SUB_REF = sub.sub_ref\njoin cirsub_m sb on sb.sub_ref = ddd.sub_ref\nwhere sub.pub_cde = 'TSS'\nand sub.crc_sts not in ('C', 'E', 'N')","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"ARPRDD","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct sub.CTM_NBR, rdd.*\ninto advdbwrk.dbo.FMR_TSS_ARPRDD\nfrom cirsub_m sub\njoin ARPACI_M aci on aci.ACI_ID = sub.aci_id and aci.DDI_IDN > '000000000000'\njoin ARPRDD_M rdd on aci.DDI_IDN = rdd.DDI_IDN \nwhere sub.pub_cde = 'TSS'\nand sub.crc_sts not in ('C', 'E', 'N')","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"ARPEPI","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct epi.*\ninto advdbwrk.dbo.FMR_TSS_ARPEPI\nfrom cirsub_m sub\njoin ARPACI_M aci on aci.ACI_ID = sub.aci_id and aci.DDI_IDN = '000000000000'\njoin ARPEPI_M epi on epi.EPI_IDN = aci.EPI_IDN \nwhere sub.pub_cde = 'TSS'\nand sub.crc_sts not in ('C', 'E', 'N')\n--and sub.rn_flg = 'C'-- creted advdbwrk.dbo.FMR_JIM_ARPEPI_A without this line it was advdbwrk.dbo.FMR_JIM_ARPEPI with it","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CREATE SELECTS","type":"text"}]},{"type":"paragraph","content":[{"text":"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!","type":"text"},{"type":"hardBreak"}]},{"type":"table","attrs":{"layout":"wide","width":960.0,"localId":"ce86dc80-c9bf-499f-8d2e-80c4a68cdcba"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"Process Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"Naming Convention","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"Use","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"LST356","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"FMR[PUB]","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"List of customers in scope for the fold.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"ARP310","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"[PUB]_FLD_ARP310_BIL_SUSP","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"Cancel Non-Installment Unpaid Invoices (ARP310)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"ARP316","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"[PUB]_FLD_ARP316_BIL_SUSP","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"Cancel Installment Billing Unpaid Invoices (ARP316)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"CIR137","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"[PUB]_FLD_MBR_CANX","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"Cancel Undeliverable, Suspensions, Subs that are part of memberships, and COMPS","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"ARP320","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"[PUB]_FLD_ARP320_DBTS","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"Resolve Unpaid Debits","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[190.0]},"content":[{"type":"paragraph","content":[{"text":"CIR915","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[451.0]},"content":[{"type":"paragraph","content":[{"text":"V[PUB]FLD","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[319.0]},"content":[{"type":"paragraph","content":[{"text":"Fold Subscriptions","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"CREATE LIST OF SUBSCRIPTIONS IN SCOPE (OPTIONAL)","type":"text"}]},{"type":"paragraph","content":[{"text":"Create Select ","type":"text"}]},{"type":"paragraph","content":[{"text":"You can copy FMRTSS. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Navigate to LSTLTT Customer Lists - Create LIST - use below as a template, just check the currency and the owning org. ","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":1018,"id":"42a02d6c-5fe3-447b-897e-c7432c61c932","collection":"contentId-47591526081","type":"file","height":758}}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Run LST356 with select FMR[PUB]","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"REMOVE FROM MEMBERSHIPS","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Next you will remove the publication from the Member Participation Elements and Membership Category to Element Cross References tabs at MBRORG. ","type":"text"}]},{"type":"paragraph","content":[{"text":"The following SQL queries can be used to identify each record that will need to be removed:","type":"text"},{"type":"hardBreak"}]},{"type":"paragraph","content":[{"text":"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:","type":"text"},{"type":"hardBreak"}]},{"type":"codeBlock","content":[{"text":"-- Remove promotion item setup at CPNOFR for Memberships\nselect distinct \npmi.ofr_cde, \npmi.itm_num, \npmi.bil_cur, \npmi.pmo_chc\nfrom mbrcex_t cex\nleft outer join cpnpmo_m pmo on pmo.pmo_cde = cex.pmo_cde\nleft outer join cpnpmi_t pmi on pmi.ofr_Cde = pmo.ofr_cde and pmi.itm_num = cex.mbr_pub\nwhere cex.mbr_pub = 'pub code'","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"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: ","type":"text"},{"type":"hardBreak"}]},{"type":"codeBlock","content":[{"text":"-- Remove publication from Member Participation Elements tab at MBRORG\nselect distinct \nmbr_org, \nmbr_pub, \nchg_cat\nfrom mbrmpe_t\nwhere mbr_pub = 'pub code'","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"-- Remove publication from Member Participation Elements tab at MBRORG\nselect distinct \nmbr_org, \nmbr_pub, \nchg_cat\nfrom mbrmpe_t\nwhere mbr_pub = 'pub code'","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"REMOVE FROM AUTO RENEW (JOBSTREAM) (CANCEL CYCLE ENDS) AND RESCHEDULE ","type":"text"}]},{"type":"paragraph","content":[{"text":"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). ","type":"text"}]},{"type":"paragraph","content":[{"text":"Use the below SQL to identify the scheduled jobstreams or processes for the cycle end (issue pull processes) for the publication you are folding. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Note: you MUST reschedule the jobstream for the change you made to take effect.","type":"text"},{"type":"hardBreak"}]},{"type":"codeBlock","content":[{"text":"-- identify job streams\nselect distinct\njso.JOB_STM, \nJSO.REQ_NME, \nJSO.JSM_SEQ\nfrom cdsjso_d jso\nleft outer join cdsreq_d req on req.req_nme = jso.job_Stm\nwhere jso.req_nme in ('CIR410', 'CIR4MQ', 'CIRSMQ')\nand jso.ref_nme = 'PUB-CDE'\nand jso.dft_val = 'pub code'\nAND REQ.REQ_NME IS NOT NULL-- job streams","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"-- stand alone\nselect \nreq_nme, \nprc_que\nfrom cdsreq_d\nwhere req_nme in ('CIR410', 'CIR4MQ', 'CIRSMQ')\nand jsm_nme = ''\nand pub_cde = 'pub code'","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Not all pubs are part of an Auto Renew Jobstream, but most are. ","type":"text"}]},{"type":"paragraph","content":[{"text":"You will need to go into the Jobstream and remove the steps for Auto Renew. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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”. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":590,"id":"bf9f149d-28c5-4313-bf21-1d8bd5586137","collection":"contentId-47591526081","type":"file","height":372}}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CANCEL SUBSCRIPTIONS THAT WILL NOT BE FOLDED","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Sample SQL query for each: ","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"CANCELLING: Non-Installment Unpaid Invoices (ARP310)","type":"text"}]},{"type":"paragraph","content":[{"text":"For the ARP310, you can model your select set on GFARP310 or TDARP310. ","type":"text"},{"type":"hardBreak"},{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"--CANCEL UNPAID (NON INSTALLMETN BILLING) --ARP310\nSELECT DISTINCT\nPDH.*\nFROM ARPPDH_M PDH\nLEFT OUTER JOIN CIRDTL_M DTL ON DTL.ORD_ID = PDH.DBT_ID\nLEFT OUTER JOIN CIRSUB_M SUB ON SUB.PUB_CDE = DTL.PUB_CDE AND SUB.SUB_REF = DTL.SUB_REF AND SUB.TRM_NBR = DTL.TRM_NBR\nLEFT OUTER JOIN CIRFUT_M FUT ON FUT.PUB_cDE = DTL.PUB_CDE AND FUT.SUB_REF = DTL.SUB_REF AND FUT.TRM_NBR = DTL.TRM_NBR\nWHERE PDH.INB_CDE = ''\nAND PDH.DBT_PAD = 'N'\nAND PDH.TOT_PAD = 0.00\nAND DTL.ITM_SLS > 0\nAND DTL.PUB_CDE IN ('PUB CODE GOES HERE')\nAND COALESCE(SUB.RATE, FUT.RATE) > 0\nAND COALESCE(SUB.CRC_STS, FUT.CRC_STS) NOT IN ('C', 'E', 'N')\nAND COALESCE(SUB.BIL_STS, FUT.BIL_STS) <> 'P'\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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: ","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":678,"id":"3fecbd43-2b93-4c80-b86e-2f2c42cc8e44","collection":"contentId-47591526081","type":"file","height":760}}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"CANCELLING: Installment Billing Unpaid Invoices (ARP316)","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Once you’ve checked the SQL you can use it to update your select set. Again you can model it on GFARP316 or TDARP316. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Here is the SQL you can use for your select set, and to examine the results: ","type":"text"}]},{"type":"codeBlock","content":[{"text":"-- CANCEL UNPAIDS INSTALLMENTS ARP316\nSELECT DISTINCT COALESCE(SUB.CRC_STS, FUT.CRC_STS), DTL.TRM_NBR, IHD.*\nFROM ARPPDH_M PDH\nLEFT OUTER JOIN ARPIHD_M IHD ON IHD.DBT_ID = PDH.DBT_ID\nLEFT OUTER JOIN CIRDTL_M DTL ON DTL.ORD_ID = PDH.DBT_ID\nLEFT OUTER JOIN CIRSUB_M SUB ON SUB.PUB_CDE = DTL.PUB_cDE AND SUB.SUB_REF = DTL.SUB_rEF AND SUB.TRM_NBR = DTL.TRM_NBR\nLEFT OUTER JOIN CIRFUT_M FUT ON FUT.PUB_cDE = DTL.PUB_CDE AND FUT.SUB_REF = DTL.SUB_REF AND FUT.TRM_NBR = DTL.TRM_NBR\nWHERE PDH.INB_CDE <> ''\nAND PDH.DBT_PAD = 'N'\nAND PDH.TOT_PAD = 0.00\nAND DTL.ITM_SLS > 0\nAND DTL.PUB_CDE IN ('PUB CODE GOES HERE')\nAND COALESCE(SUB.RATE, FUT.RATE) > 0\nAND COALESCE(SUB.CRC_STS, FUT.CRC_STS) NOT IN ('C', 'E', 'N')\nAND COALESCE(SUB.BIL_STS, FUT.BIL_STS) <> 'P'","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Again, you should not need to change any of the default process values:","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":678,"id":"dd21b588-099d-4521-89f7-7efeba1b20b9","collection":"contentId-47591526081","type":"file","height":643}}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"type":"hardBreak"},{"text":"CANCELLING: Undeliverables, Suspensions, Subs that are part of memberships, and COMPS","type":"text"}]},{"type":"paragraph","content":[{"text":"Any other cancellations for paid or comp subscriptions can be processed with the CIR137.","type":"text"},{"type":"hardBreak"},{"type":"hardBreak"},{"text":"Again, you can look for a and recycle or copy the TDCIR137 select set. ","type":"text"},{"type":"hardBreak"},{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"As a best practice, you should use the following process options:","type":"text"}]},{"type":"paragraph","content":[{"text":"No Credit","type":"text"}]},{"type":"paragraph","content":[{"text":"UNCHECK Refund Flag","type":"text"}]},{"type":"paragraph","content":[{"text":"UNCHECK Cancel All Terms","type":"text"}]},{"type":"paragraph","content":[{"text":"Cancel Reason: CFC","type":"text"}]},{"type":"paragraph","content":[{"text":"CHECK Auto Generate Control Group","type":"text"}]},{"type":"paragraph","content":[{"text":"UNCHECK Auto Release Control Group","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"-- RUN ONLY FOR CURRENT TERM --a future term may be eligable to be folded, so cancelling the current term will make that future term current - may have to run this a few times to get rid of all the canceleable terms. \nSELECT DISTINCT SUB.*\nFROM CIRSUB_M SUB\nWHERE SUB.PUB_CDE = 'MMP'\nAND SUB.CRC_STS in ('U', 'S', 'T')\n-- cancel undeliverable and suspended\nUNION\nSELECT DISTINCT SUB.*\nFROM CIRSUB_M SUB\nWHERE SUB.PUB_CDE = 'MMP'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.MBR_ORG <> ''\nAND (SUB.RATE = 0 OR SUB.ITG = 0)\nUNION\nSELECT DISTINCT SUB.*\nFROM CIRSUB_M SUB\nWHERE SUB.PUB_CDE = 'MMP'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.BIL_STS = 'C'\nAND SUB.MBR_ORG = ''\nAND NOT (SUB.SUB_TYP = 'LIFE' OR SUB.PMO_CHC = 'LFMMPP' OR SUB.PRIC_CDE = 'LFCOMP')","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":667,"id":"ac94c2a1-fa49-4578-847b-5dbb86dbe7c0","collection":"contentId-47591526081","type":"file","height":860}}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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. ","type":"text"},{"type":"hardBreak"},{"text":"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. ","type":"text"},{"type":"hardBreak"},{"text":"(maybe fix the below SQL to have a ‘like zcir%' part to help find the control group)","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct\nord.tot_cst, \nord_post,\ndtl.*\nfrom cirdtl_m dtl\nleft outer join mstord_m ord on ord.ord_num = dtl.ord_id\nwhere dtl.ctg_id = 'ZCIR37' --control group goes here\nand dtl.ctg_dte = '20220111' --control group date goes here\norder by 1","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"***RUN THE CIR137 SQL SELECT AGAIN***","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CLEAR INSTALLMENT BILLING CODES","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"},{"type":"hardBreak"},{"text":"You can also run an OSM upload to do this without a SQL change request (link to separate instructions will go here, WIP). ","type":"text"}]},{"type":"codeBlock","content":[{"text":"begin tran\nUPDATE SUB\nSET INB_CDE = ''\n--SELECT DISTINCT SUB.INB_CDE, SUB.*\nFROM CIRSUB_M SUB\nWHERE SUB.PUB_CDE = 'PUB CODE GOES HERE'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.INB_CDE <> ''\n--commit tran\n--rollback tran","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"For under 5,000 records, you can also do an OSM update that will set the installment billing code to “NON”.","type":"text"}]},{"type":"paragraph","content":[{"text":"This way, you don’t have to wait for SQL update approval. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"print 'drop temp table if it already exists'\nif OBJECT_ID('tempdb..##temp01') is not null \n drop table ##temp01 \n\nprint 'create temp table from query' \nSELECT DISTINCT sub.PUB_CDE, sub.CTM_NBR, sub.SUB_REF, sub.trm_nbr\ninto ##temp01\nFROM CIRSUB_M SUB\nWHERE SUB.PUB_CDE = 'SKP'---folding pub goes here\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.INB_CDE not in ('', 'NON')\n--select * from ##temp01\n\nprint 'now create OSM file'\nSET DATEFORMAT DMY\ndeclare @date varchar(10)\nset @date = cast(year(getdate()) as varchar) + '-' + right('0'+cast(month(getdate()) as varchar),2)+ '-' + right('0'+cast(day(getdate()) as varchar),2)\nprint @date\n--- delete ##TEMP_OSM1 \nprint 'drop temp table if it already exists'\nif OBJECT_ID('tempdb..##temp_OSM1') is not null \n drop table ##temp_OSM1 \nCreate table ##TEMP_OSM1 (ALLSHIT varchar(max), OrderNo int) \ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('',1)\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('OPS',2)\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('OPS',3)\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues (''+@date+'',4) --SQL to set\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('PRO001',5) --SQL to set -\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('0',6) --SQL to set?\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('0',7) --SQL to set (not sure if needed - try ignoring!\nset nocount on\t\n\nDECLARE @count_PROGRESS BIGINT, @pub_cde varchar(50), @ctm_nbr varchar(50), @sub_ref varchar(50), \n @trm_nbr varchar(50), @ALLSHIT varchar(5000),\n @YYYY varchar(4), @mm varchar(2), @dd varchar(2)\nset @count_PROGRESS = 0\nset @YYYY = year(getdate())\nset @MM = month(getdate())\nset @MM = right('0'+@MM,2)\nset @DD = day(getdate())\nset @DD = right('0'+@DD,2)\nprint @YYYY+@MM+@DD\nset @count_PROGRESS = 7\ndeclare Cursor_SubscriptionCancellation cursor\nfor\nselect pub_cde, ctm_nbr, sub_ref, trm_nbr from ##temp01\nfor update\n\nopen Cursor_SubscriptionCancellation\nfetch next from Cursor_SubscriptionCancellation into\n @pub_cde, @ctm_nbr, @sub_ref, @trm_nbr\n\nwhile (@@fetch_status <> -1)\nbegin\n\tif (@@fetch_status <> -2)\n\tbegin\n\tset @count_PROGRESS = @count_PROGRESS + 1\n\tif right(cast(@count_PROGRESS as varchar), 3) = '000' print @count_PROGRESS\n\tset @ALLSHIT = ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''+@pub_cde+''\n\tset @ALLSHIT = @ALLSHIT + ''+@ctm_nbr+''\n\tset @ALLSHIT = @ALLSHIT + ''+@sub_ref+''\n\tset @ALLSHIT = @ALLSHIT + ''+@trm_nbr+''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + 'NON'\n\tset @ALLSHIT = @ALLSHIT + ''\n\n\n\n\tbegin\n\tINSERT INTO ##TEMP_OSM1 (allshit, OrderNo)\n\tvalues (@ALLSHIT, @count_PROGRESS)\n\tend\n\n\tend\n\nfetch next from Cursor_SubscriptionCancellation into\n @pub_cde, @ctm_nbr, @sub_ref, @trm_nbr\nend\nclose Cursor_SubscriptionCancellation\ndeallocate Cursor_SubscriptionCancellation\n\ninsert into ##TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('', (select max(OrderNo) from ##TEMP_OSM1) +1)\nset nocount off\nprint '@count_PROGRESS: ' + cast(@count_PROGRESS as varchar)\nprint 'output data for OSM'\n\n\n\nselect * from ##TEMP_OSM1\norder by OrderNo\n\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"RESOLVE UNPAID DEBITS ON SUBSCRIPTIONS","type":"text"}]},{"type":"paragraph","content":[{"text":"Use this to find the debits","type":"text"}]},{"type":"paragraph","content":[{"text":"Use this to be the SQL for the Select","type":"text"}]},{"type":"paragraph","content":[{"text":"(fix this and comment out data? )","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct \ndoc.*\n--pdh.tot_Dbt, pdh.tot_pad, pdh.tot_dbt-pdh.tot_pad [Due], isnull(tax.sls_Tax, 0.00) [SLS_TAX], sub.ctm_nbr, sub.sub_Ref, sub.key_cde, sub.sub_Typ, sub.pmo_chc\nfrom cirdtl_m dtl\nleft outer join arppdh_m pdh on pdh.dbt_id = dtl.ord_id\njoin arpdoc_m doc on doc.DOC_ID = pdh.dbt_id\nleft outer join cirsub_m sub on sub.pub_cde = dtl.pub_cde and sub.sub_Ref = dtl.sub_ref and sub.trm_nbr = dtl.trm_nbr\nleft outer join mtdtax_m tax on tax.ord_id = dtl.ord_id\nwhere sub.pub_cde = 'TGE'\nand sub.crc_sts not in ('c', 'e', 'n')\nand (sub.bil_sts = 'B' or pdh.dbt_pad <> 'Y')\nand dtl.itm_sls > 0\nunion\nselect distinct\ndoc.*\n--pdh.tot_Dbt, pdh.tot_pad, pdh.tot_dbt-pdh.tot_pad [Due], isnull(tax.sls_Tax, 0.00) [SLS_TAX], FUT.ctm_nbr, FUT.sub_Ref, FUT.key_cde, FUT.sub_Typ, FUT.pmo_chc\nfrom cirdtl_m dtl\nleft outer join arppdh_m pdh on pdh.dbt_id = dtl.ord_id\njoin arpdoc_m doc on doc.DOC_ID = pdh.dbt_id\nleft outer join cirfut_m fut on fut.pub_cde = dtl.pub_cde and fut.sub_Ref = dtl.sub_ref and fut.trm_nbr = dtl.trm_nbr\nleft outer join mtdtax_m tax on tax.ord_id = dtl.ord_id\nwhere fut.pub_cde = 'TGE'\nand fut.crc_sts not in ('c', 'e', 'n')\nand (fut.bil_sts = 'B' or pdh.dbt_pad <> 'Y')\nand dtl.itm_sls > 0","type":"text"}]},{"type":"paragraph","content":[{"text":"RUN the ARP320 with the select you created (use GFARP320 or TDARP320 as a model)","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Process Options","type":"text","marks":[{"type":"underline"}]}]},{"type":"paragraph","content":[{"text":"Uncheck report only","type":"text"}]},{"type":"paragraph","content":[{"text":"Auto generate CTRL GRP","type":"text"}]},{"type":"paragraph","content":[{"text":"DO NOT auto release","type":"text"}]},{"type":"paragraph","content":[{"text":"0 for days","type":"text"}]},{"type":"paragraph","content":[{"text":"0 for low amount ","type":"text"}]},{"type":"paragraph","content":[{"text":"9999 for high amount ","type":"text"}]},{"type":"paragraph","content":[{"text":"it will put it in a ZARP control group ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"SQL ANALYSIS: GROUP CUSTOMERS BY FOLD ACTIONS","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"1 - they have the folding pub as a Lifetime, and they’ll be getting the new pub in lifetime. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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” ","type":"text"}]},{"type":"paragraph","content":[{"text":"At the bottom is a SQL to count the records in each group. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"DROP TABLE IF EXISTS [ADVDBWRK].[DBO].[SLL_FOLD]\n\nSELECT DISTINCT SUB.*, '01' [GROUP]\nINTO [ADVDBWRK].[DBO].[SLL_FOLD]\n-- select distinct sub.*\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN CIRSUB_M SB2 ON SB2.CTM_NBR = SUB.CTM_NBR AND SB2.PUB_CDE = 'CTA' AND SB2.CRC_STS NOT IN ('C', 'E', 'N') \nWHERE SUB.PUB_CDE = 'SLL'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.BIL_STS <> 'B'\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.MBR_ORG = ''\nAND (SUB.SUB_TYP = 'LIFE' or sub.rn_flg = 'A')\nAND NOT (ISNULL(SB2.SUB_TYP, '') = 'LIFE' OR ISNULL(SB2.RN_FLG, '') = 'A' OR ISNULL(SB2.SUB_CLS, '') = 'P') -- NOT CTA LIFE\nAND NOT EXISTS (SELECT TOP 1 * FROM CIRFUT_M WHERE PUB_CDE = 'CTA' AND CRC_STS NOT IN ('C', 'E', 'N') AND SUB_CLS = 'P' AND CTM_NBR = SUB.CTM_NBR) -- NOT FUT PERPET\n-- GROUP 01, SLL LIFE, no CTA Life\n\n\nINSERT INTO [ADVDBWRK].[DBO].[SLL_FOLD]\nSELECT DISTINCT SUB.*, '02'\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[SLL_FOLD] [SLL] ON [SLL].PUB_CDE = SUB.PUB_CDE AND [SLL].SUB_REF = SUB.SUB_REF\nLEFT OUTER JOIN CIRSUB_M SB2 ON SB2.CTM_NBR = SUB.CTM_NBR AND SB2.PUB_CDE = 'CTA' AND SB2.CRC_STS NOT IN ('C', 'E', 'N')\nWHERE SUB.PUB_CDE = 'SLL'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.BIL_STS <> 'B'\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.MBR_ORG = ''\nAND (SB2.RN_FLG = 'C' OR SUB.RN_FLG = 'C' AND SB2.PUB_CDE IS NULL)\nAND NOT (SUB.SUB_TYP = 'LIFE' OR SUB.RN_FLG = 'A')\nAND NOT (ISNULL(SB2.SUB_TYP, '') = 'LIFE' OR ISNULL(SB2.RN_FLG, '') = 'A' OR ISNULL(SB2.SUB_CLS, '') = 'P') -- NOT CTA LIFE\nAND NOT EXISTS (SELECT TOP 1 * FROM CIRFUT_M WHERE PUB_CDE = 'CTA' AND CRC_STS NOT IN ('C', 'E', 'N') AND SUB_CLS = 'P' AND CTM_NBR = SUB.CTM_NBR) -- NOT FUT PERPET\nAND [SLL].SUB_REF IS NULL\n-- GROUP 2, SLL C flags no CTA, CTA C FLAGS\n\n\nINSERT INTO [ADVDBWRK].[DBO].[SLL_FOLD]\nSELECT DISTINCT SUB.*, '03'\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[SLL_FOLD] [SLL] ON [SLL].PUB_CDE = SUB.PUB_CDE AND [SLL].SUB_REF = SUB.SUB_REF\nLEFT OUTER JOIN CIRSUB_M SB2 ON SB2.CTM_NBR = SUB.CTM_NBR AND SB2.PUB_CDE = 'CTA' AND SB2.CRC_STS NOT IN ('C', 'E', 'N')\nWHERE SUB.PUB_CDE = 'SLL'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.BIL_STS <> 'B'\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.MBR_ORG = ''\nAND ISNULL(SB2.RN_FLG, '') <> 'C'\nAND NOT (SUB.SUB_TYP = 'LIFE' OR SUB.RN_FLG = 'A')\nAND NOT (ISNULL(SB2.SUB_TYP, '') = 'LIFE' OR ISNULL(SB2.RN_FLG, '') = 'A' OR ISNULL(SB2.SUB_CLS, '') = 'P') -- NOT CTA LIFE\nAND NOT EXISTS (SELECT TOP 1 * FROM CIRFUT_M WHERE PUB_CDE = 'CTA' AND CRC_STS NOT IN ('C', 'E', 'N') AND SUB_CLS = 'P' AND CTM_NBR = SUB.CTM_NBR) -- NOT FUT PERPET\nAND [SLL].SUB_REF IS NULL\n-- GROUP 3, SLL Y FLAGS OR CTA NON-AR\n\n\nINSERT INTO [ADVDBWRK].[DBO].[SLL_FOLD]\nSELECT DISTINCT SUB.*, '04'\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[SLL_FOLD] [SLL] ON [SLL].PUB_CDE = SUB.PUB_CDE AND [SLL].SUB_REF = SUB.SUB_REF\nLEFT OUTER JOIN CIRSUB_M SB2 ON SB2.CTM_NBR = SUB.CTM_NBR AND SB2.PUB_CDE = 'CTA' AND SB2.CRC_STS NOT IN ('C', 'E', 'N')\nWHERE SUB.PUB_CDE = 'SLL'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.BIL_STS <> 'B'\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.MBR_ORG = ''\nAND (ISNULL(SB2.SUB_TYP, '') = 'LIFE' OR ISNULL(SB2.RN_FLG, '') = 'A' OR ISNULL(SB2.SUB_CLS, '') = 'P') -- NOT CTA LIFE\nAND NOT EXISTS (SELECT TOP 1 * FROM CIRFUT_M WHERE PUB_CDE = 'CTA' AND CRC_STS NOT IN ('C', 'E', 'N') AND SUB_CLS = 'P' AND CTM_NBR = SUB.CTM_NBR) -- NOT FUT PERPET\nAND [SLL].SUB_REF IS NULL\n-- GROUP 4, SLL PAID WITH CTA LIFE\n\n\nSELECT DISTINCT [GROUP], COUNT(*) [COUNT]\nFROM [ADVDBWRK].[DBO].[SLL_FOLD] [SLL]\nLEFT OUTER JOIN CIRSUB_M SUB ON SUB.PUB_CDE = [SLL].PUB_CDE AND SUB.SUB_REF = [SLL].SUB_REF\nGROUP BY [GROUP]\nORDER BY 1\n\n\nSELECT DISTINCT COALESCE(FUT.CRC_STS, SB2.CRC_STS) [CTA.CRC_STS], COALESCE(FUT.SUB_CLS, SB2.SUB_CLS) [CTA.SUB_CLS], COALESCE(FUT.SUB_TYP, SB2.SUB_TYP) [CTA.SUB_TYP], \nISNULL(SB2.RN_FLG, '') [CTA.RN_FLG], SB2.KEY_CDE [CTA.KEY_CDE], SUB.CRC_STS, SUB.BIL_STS, SUB.RN_FLG, SUB.KEY_CDE, SUB.MBR_ORG, SUB.*\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[SLL_FOLD] [SLL] ON [SLL].PUB_CDE = SUB.PUB_CDE AND [SLL].SUB_REF = SUB.SUB_REF\nLEFT OUTER JOIN CIRSUB_M SB2 ON SB2.CTM_NBR = SUB.CTM_NBR AND SB2.PUB_CDE = 'CTA' AND SB2.CRC_STS NOT IN ('C', 'E', 'N')\nLEFT OUTER JOIN CIRFUT_M FUT ON FUT.PUB_CDE = SB2.PUB_CDE AND FUT.SUB_REF = SB2.SUB_REF AND FUT.TRM_NBR = (SELECT MAX(TRM_NBR) FROM CIRFUT_M WHERE PUB_CDE = SB2.PUB_CDE AND SUB_REF = SB2.SUB_REF AND FUT.CRC_STS NOT IN ('C', 'N'))\nWHERE SUB.PUB_CDE = 'SLL'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.MBR_ORG = ''\nAND [SLL].PUB_CDE IS NULL\nORDER BY 1, 2, 3\n-- FALL THROUGHS\n","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"\n-- SQL UPDATE FOR GROUP ASSIGNMENTS - repeated below\nbegin tran\nUPDATE SUB\nSET SUB.SUB_DMO = [FLF].[GROUP]\n--SELECT DISTINCT SUB.SUB_DMO, SUB.SUB_DM2, [FLF].[GROUP], SUB.*\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[FLF_FOLD] [FLF] ON [FLF].PUB_CDE = SUB.PUB_CDE AND [FLF].SUB_REF = SUB.SUB_REF\nWHERE SUB.PUB_CDE = 'FLF'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND [FLF].PUB_CDE IS NOT NULL\n--commit tran\n--rollback tran","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"MARKETING SETUP","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"CAMPAIGN CODE","type":"text"}]},{"type":"paragraph","content":[{"text":"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). ","type":"text"}]},{"type":"paragraph","content":[{"text":"The cost accumulation method will be set to “P” promotion level. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"OFFER CODE","type":"text"}]},{"type":"paragraph","content":[{"text":"Make sure that the offer has the 'additional items' checkbox checked","type":"text"}]},{"type":"paragraph","content":[{"text":"V(pubcode)FOLD","type":"text"}]},{"type":"paragraph","content":[{"text":"Set the Description to “Folding [PUB] into [PUB]”","type":"text"}]},{"type":"paragraph","content":[{"text":"Set the Owning Organization to the one of the pub you are folding. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Set the Credit Policy to CPT001","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"CHOCIES","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Choices will match Group Number. As in the Choice ending “001” will be for Group 1, etc. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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)","type":"text"}]},{"type":"paragraph","content":[{"text":"For example: ","type":"text"}]},{"type":"paragraph","content":[{"text":"SUN001 ","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"publication code TPW","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"DETAILS TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"description: SUN fold into TWP LIFE","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"type: LIFE","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"class: R","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"renewal notice: A","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"term length: 260 (60 for a pub with 12 issues per year) ","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"dlivery code: EM","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"CHOCIE RATES TAB","type":"text"},{"type":"hardBreak"},{"text":"add rate command, or edit if you copied from an existing choice","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"rate: $0","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"next term: 260 (60 for a pub with 12 issues per year)","type":"text"}]},{"type":"paragraph","content":[{"text":"SUN002","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"publication code: TPW","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"DETAILS TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"description: SUN fold to current TPW C flags","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"type: [blank]","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"class: R","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"renewal notice: C","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"term length: 52 (12 for a pub with 12 issues per year)","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"delivery code: EM","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"CHOICE RATES TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"add rate command, or edit if you copied from an existing choice","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"next rate: $1.00 (this will get updated via SQL)","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"next term: 52 (12 for a pub with 12 issues per year)","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}]},{"type":"paragraph","content":[{"text":"SUN003","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"publication code TPW","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"DETAILS TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"sub type: [blank]","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"sub class: R","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"renewal notice: Y","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"term length: 52 (12 for a pub with 12 issues per year)","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"delivery code: EM","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"CHOICE RATES TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"add rate command, or edit if you copied from an existing choice","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"next rate: $0","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"next term: 0","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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)","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"This will be set up as a Membership Choice ","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"publication code: BKE","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"DETAILS TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"description: SUN with TWP LIFE fold into BKE LIFE MBR","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"type: LIFE","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"renewal notice: A","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"term length: 260","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"delivery code: EM","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"member org: BK","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"member cat: BK","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"member sub class: R","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"CHOICE RATES TAB","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"add rate command, or edit if you copied from an existing choice","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"rate: $0","type":"text"}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":1}}],"content":[{"text":"next term: 260 (60 for a pub with 12 issues per year)","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"Add to Offer","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Once you’ve created the choices, add them to the OFFER code. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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”. ","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":1089,"id":"d7395f4e-a1f1-4590-acd4-0baf8866f0d1","collection":"contentId-47591526081","type":"file","height":681}}]},{"type":"paragraph","content":[{"text":"Once that’s done, you can click the ‘add’ control and add the choices you made to the offer. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"PROMOTION CODE","type":"text"}]},{"type":"paragraph","content":[{"text":"The promotion code will follow the same format: V[PUB]FOLD","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CREATE THE CIR915 SELCT","type":"text"}]},{"type":"paragraph","content":[{"text":"You can copy this select from an existing select. ","type":"text"},{"type":"hardBreak"},{"text":"The naming convention for the Select Set will match what we’ve been doing previously, so “V[PUB]FOLD”.","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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’. ","type":"text"}]},{"type":"paragraph","content":[{"text":"The condition for each level will be “subscription demographic data is equal to ‘01’ and so on for each group. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"\nselect distinct \nsub.* \nfrom cirsub_m sub\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[NEO_FOLD] [NEO] ON [NEO].PUB_CDE = SUB.PUB_CDE AND [NEO].SUB_REF = SUB.SUB_REF\nLEFT OUTER JOIN CIRSUB_M_ DMO ON DMO.PublicationCode = SUB.PUB_CDE AND DMO.SubscriptionReference = SUB.SUB_REF\nWHERE SUB.PUB_cDE = 'NEO'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nand sub.fin_itg > 0\nAND SUB.SUB_CLS <> 'P'\nAND SUB.SUB_REF NOT IN (SELECT SUB_REF FROM CIRDTL_M WHERE CTG_ID = CONCAT('SA', SUB.PUB_CDE, 'F') AND CTG_DTE = cast(getdate() as date)) --425\nAND [NEO].[GROUP] = DMO.LifetimeSubscriber\n\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"UPDATE SUB DMO DATA - SQL or OSM","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"-- SQL UPDATE FOR GROUP ASSIGNMENTS\nbegin tran\nUPDATE SUB\nSET SUB.SUB_DMO = [SUN].[GROUP]\n--SELECT DISTINCT SUB.SUB_DMO, SUB.SUB_DM2, [SUN].[GROUP], SUB.*\nFROM CIRSUB_M SUB\nLEFT OUTER JOIN [ADVDBWRK].[DBO].[SUN_FOLD] [SUN] ON [SUN].PUB_CDE = SUB.PUB_CDE AND [SUN].SUB_REF = SUB.SUB_REF\nWHERE SUB.PUB_CDE = 'SUN'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\nAND SUB.FIN_ITG > 0\nAND SUB.SUB_CLS <> 'P'\nAND [SUN].PUB_CDE IS NOT NULL\n--commit tran\n--rollback tran","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"print 'drop temp table if it already exists'\nif OBJECT_ID('tempdb..#temp01') is not null \n drop table #temp01 \n\nprint 'create temp table from query' \n\nSELECT DISTINCT PUB_CDE, CTM_NBR, SUB_REF, TRM_NBR, [GROUP] AS [SUB_DMO]\nINTO #TEMP01\n-- select distinct [CAR].[group], [CAR].[sub_dmo], *\nFROM [ADVDBWRK].[DBO].[CAR_FOLD] [CAR]\nLEFT OUTER JOIN CIRSUB_M_ DMO ON DMO.PublicationCode = CAR.PUB_CDE AND DMO.SubscriptionReference = CAR.SUB_rEF\nWHERE [CAR].[GROUP] <> ISNULL(DMO.LifetimeSubscriber, '')\nAND CAR.SUB_REF NOT IN (SELECT SUB_REF FROM CIRDTL_M WHERE CTG_ID = concat('SA', CAR.pub_cde, 'F') AND CTG_DTE = CAST(GETDATE() AS DATE))\n\n-- select * from #temp01\n\nprint 'now create OSM file'\nSET DATEFORMAT DMY\ndeclare @date varchar(10)\nset @date = cast(year(getdate()) as varchar) + '-' + right('0'+cast(month(getdate()) as varchar),2)+ '-' + right('0'+cast(day(getdate()) as varchar),2)\nprint @date\n--- delete #TEMP_OSM1 \nprint 'drop temp table if it already exists'\nif OBJECT_ID('tempdb..#temp_OSM1') is not null \n drop table #temp_OSM1 \nCreate table #TEMP_OSM1 (ALLSHIT varchar(max), OrderNo int) \ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('',1)\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('OPS',2)\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('OPS',3)\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues (''+@date+'',4) --SQL to set\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('DMO001',5) --SQL to set -\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('0',6) --SQL to set?\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('0',7) --SQL to set (not sure if needed - try ignoring!\nset nocount on\t\n\nDECLARE @count_PROGRESS BIGINT, @pub_cde varchar(50), @ctm_nbr varchar(50), @sub_ref varchar(50), \n @trm_nbr varchar(50), @sub_dmo varchar(32), @ALLSHIT varchar(5000),\n @YYYY varchar(4), @mm varchar(2), @dd varchar(2)\nset @count_PROGRESS = 0\nset @YYYY = year(getdate())\nset @MM = month(getdate())\nset @MM = right('0'+@MM,2)\nset @DD = day(getdate())\nset @DD = right('0'+@DD,2)\nprint @YYYY+@MM+@DD\nset @count_PROGRESS = 7\ndeclare Cursor_SubscriptionDemographicsChange cursor\nfor\nselect pub_cde, ctm_nbr, sub_ref, trm_nbr, sub_dmo from #temp01\nfor update\n\nopen Cursor_SubscriptionDemographicsChange\nfetch next from Cursor_SubscriptionDemographicsChange into\n @pub_cde, @ctm_nbr, @sub_ref, @trm_nbr, @sub_dmo\n\nwhile (@@fetch_status <> -1)\nbegin\n\tif (@@fetch_status <> -2)\n\tbegin\n\tset @count_PROGRESS = @count_PROGRESS + 1\n\tif right(cast(@count_PROGRESS as varchar), 3) = '000' print @count_PROGRESS\n\tset @ALLSHIT = ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''+@pub_cde+''\n\tset @ALLSHIT = @ALLSHIT + ''+@ctm_nbr+''\n\tset @ALLSHIT = @ALLSHIT + ''+@sub_ref+''\n\tset @ALLSHIT = @ALLSHIT + ''+@trm_nbr+''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''+@sub_dmo+''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\n\n\n\tbegin\n\tINSERT INTO #TEMP_OSM1 (allshit, OrderNo)\n\tvalues (@ALLSHIT, @count_PROGRESS)\n\tend\n\n\tend\n\nfetch next from Cursor_SubscriptionDemographicsChange into\n @pub_cde, @ctm_nbr, @sub_ref, @trm_nbr, @sub_dmo\nend\nclose Cursor_SubscriptionDemographicsChange\ndeallocate Cursor_SubscriptionDemographicsChange\n\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('', (select max(OrderNo) from #TEMP_OSM1) +1)\nset nocount off\nprint '@count_PROGRESS: ' + cast(@count_PROGRESS as varchar)\nprint 'output data for OSM'\n\n\n\nselect * from #TEMP_OSM1\norder by OrderNo\n\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CREATE CONTROL GROUP","type":"text"},{"type":"hardBreak"}]},{"type":"paragraph","content":[{"text":"First you need to create the control group. The format is going to be “SA[PUB]F”. ","type":"text"}]},{"type":"paragraph","content":[{"text":"You need to make sure the control group is closed. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Otherwise you’ll have to find it and right click it and actively close it. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Once the control group is created, you need to protect it by right clicking and selecting protect. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"UNBLOCK CUSTOMERS","type":"text"}]},{"type":"paragraph","content":[{"text":"In any fold there will be a selection of customers who will be Blocked, either at the customer level, or the card level. ","type":"text"}]},{"type":"paragraph","content":[{"text":"There are 3 processes (CDSFL1, CDSFL2, and CDSFL3) that look at a work table, and run a stored procedure to run a SQL update. ","type":"text"}]},{"type":"paragraph","content":[{"text":"There is a jobstream that contains all three processes called “FOLDBLKS”","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Check that Tables are Empty","type":"text"}]},{"type":"codeBlock","content":[{"text":"select * from advdbwrk.dbo.CBLK_FLG_Changes\nselect * from advdbwrk.dbo.ARPCSA_Changes\nselect * from advdbwrk.dbo.ARPCRD_Changes","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"type":"hardBreak"},{"text":"Update Tables with New and Old Values","type":"text"}]},{"type":"codeBlock","content":[{"text":"-- Turn off CTM blocks\ninsert into advdbwrk.dbo.CBLK_FLG_Changes\nselect distinct ctm.ctm_nbr, 'N', 'TDODGE', getdate() \nfrom cirsub_m sub\nleft outer join cdsctm_m ctm on ctm.ctm_nbr = sub.ctm_nbr\nwhere sub.pub_cde = 'SUN'\nand sub.crc_sts not in ('c', 'e', 'n')\nand ctm.cblk_flg = 'Y'\nAND SUB.SUB_REF NOT IN (SELECT SUB_REF FROM CIRDTL_M WHERE CTG_ID = CONCAT('SA', SUB.PUB_CDE, 'F'))-- AND CTG_DTE = CAST(GETDATE() AS DATE))\n\n\n-- turn off CSA blocks\ninsert into advdbwrk.dbo.ARPCSA_Changes\nselect distinct csa.ctm_nbr, csa.afl_Cde, 'Z', csa.crd_sts, 'TDODGE', getDate()\nfrom cirsub_m sub\nleft outer join cdsitm_m itm on itm.itm_num = sub.pub_cde\nleft outer join office_t ofc on ofc.ofc_nbr = itm.own_org\nleft outer join arpcsa_m csa on csa.ctm_nbr = sub.ctm_nbr and csa.afl_cde = ofc.afl_cde\nleft outer join cdscrs_t crs on crs.crd_sts = csa.crd_sts\nwhere sub.pub_cde = 'SUN'\nand sub.crc_sts not in ('c', 'e', 'n')\nand crs.crd_type = 'REJECT'\nAND SUB.SUB_REF NOT IN (SELECT SUB_REF FROM CIRDTL_M WHERE CTG_ID = CONCAT('SA', SUB.PUB_CDE, 'F'))-- AND CTG_DTE = CAST(GETDATE() AS DATE))\n\n\n-- turn off CRD blocks\ninsert into advdbwrk.dbo.ARPCRD_Changes\nselect distinct crd.ctm_nbr, 'Z', crd.crd_sts, 'TDODGE', getDate()\nfrom cirsub_m sub\nleft outer join cdsitm_m itm on itm.itm_num = sub.pub_cde\nleft outer join office_t ofc on ofc.ofc_nbr = itm.own_org\nleft outer join arpcrd_m crd on crd.ctm_nbr = sub.ctm_nbr \nleft outer join cdscrs_t crs on crs.crd_sts = crd.crd_sts\nwhere sub.pub_cde = 'SUN'\nand sub.crc_sts not in ('c', 'e', 'n')\nand crs.crd_type = 'REJECT'\nAND SUB.SUB_REF NOT IN (SELECT SUB_REF FROM CIRDTL_M WHERE CTG_ID = CONCAT('SA', SUB.PUB_CDE, 'F'))-- AND CTG_DTE = CAST(GETDATE() AS DATE))\n","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"Run Jobstream FOLDBLKS","type":"text"}]},{"type":"paragraph","content":[{"text":"Run this jobstream. It will send out email alerts that this is being done. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"RUN CIR915","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"REBLOCK CUSTOMERS","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Reinstate Blocks","type":"text"}]},{"type":"codeBlock","content":[{"text":"-- reinstate CTM blocks\nupdate advdbwrk.dbo.CBLK_FLG_Changes\nset CBLK_FLG = 'Y'\nwhere cblk_flg = 'N'\nand UserName = 'TDODGE'\nand cast(DateChange as date) = cast(getDate() as date)\n\n\n-- Reinstate CSA blocks\nupdate advdbwrk.dbo.ARPCSA_Changes\nset CRD_STS = CRD_STS_OLD\nWHERE CRD_STS = 'Z'\nAND UserName = 'TDODGE'\nand cast(DateChange as date) = cast(getDate() as date)\n\n\n-- Reinstate CRD blocks\nupdate advdbwrk.dbo.ARPCRD_Changes\nset CRD_STS = CRD_STS_OLD\nWHERE CRD_STS = 'Z'\nAND UserName = 'TDODGE'\nand cast(DateChange as date) = cast(getDate() as date)","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"Run Jobstream FOLDBLKS to Re-Block Customers","type":"text"}]},{"type":"paragraph","content":[{"text":"Run this jobstream. It will send out email alerts that this is being done.","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Delete Records from Work Tables","type":"text"}]},{"type":"paragraph","content":[{"text":"This SQL only works when the change date is less than todays date.","type":"text"}]},{"type":"codeBlock","content":[{"text":"-- remove old CTM block records\ndelete from advdbwrk.dbo.CBLK_FLG_Changes\nwhere UserName = 'TDODGE'\nand DateChange < cast(getDate() as date)\n\n-- remove old CSA block records\ndelete from advdbwrk.dbo.ARPCSA_Changes\nwhere UserName = 'TDODGE'\nand DateChange < cast(getDate() as date)\n\n-- remove old CSA block records\ndelete from advdbwrk.dbo.ARPCRD_Changes\nwhere UserName = 'TDODGE'\nand DateChange < cast(getDate() as date)","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"RUN THE CONTROL GROUP CHECK SQL","type":"text"}]},{"type":"paragraph","content":[{"text":"There are a few queries you can run to look for problems with any of the fold transactions. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"\n--general control group query\nselect \ndtl.ORD_ID,\ndtl.pub_cde, \ndtl.ctm_nbr, \ndtl.sub_ref, \ndtl.trm_nbr, \ndtl.opr_cde, \ndtl.sub_typ, \ndtl.sub_cls, \ncase when dtl.opr_Cde = 'UPC' then sub.fin_itg when dtl.opr_Cde = 'UPG' then dtl.term else '' end 'Issues',\ndtl.rate, \ndtl.rn_flg, \ndtl.key_cde, \ndtl.mbr_chc,\ndtl.sub_chc, \n--scd.NXT_RAT,\nIsNull((convert(varchar(10), scd.nxt_rat)), '') as \"Next Rate\", \n--scd.NXT_TERM,\nIsNull(convert(varchar(10), scd.nxt_term), '') as \"Next Term\", \ndtl.SCHN_CDE,\nord.epi_idn, \nord.tot_cst\n--dtl.*\nfrom cirdtl_m dtl\nleft outer join cpnscd_m scd on scd.pub_cde = dtl.pub_cde and scd.sub_chc = dtl.sub_chc\nleft outer join cirhdr_m hdr on hdr.ord_id = dtl.ord_id\nleft outer join mstord_m ord on ord.ord_id = dtl.ord_id\nleft outer join cirsub_m sub on sub.pub_cde = dtl.pub_cde and sub.sub_Ref = dtl.sub_Ref\nwhere 1=1\n--and dtl.ctg_id = 'saneof'\nand dtl.ctg_id in ('SANEOF') --update the control group id\n--and dtl.ctg_dte >= cast(getDATE()-5 as date)\nand dtl.ctg_dte = cast(getDATE() as date)\nand ord.ord_post <> 'Y'\n--and opr_Cde not in ('UPG','UPC')\n--order by 3","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"-- counts by choice\nselect distinct dtl.pub_Cde, dtl.mbr_org, coalesce(sch.sub_chc, mch.mbr_chc) [Choice], coalesce(sch.chc_Dsc, mch.chc_dsc) [CHC_DSC], count(*)\nfrom cirdtl_m dtl\nleft outer join cpnsch_m sch on sch.pub_cde = dtl.pub_cde and sch.sub_chc = dtl.sub_chc\nleft outer join cpnmch_m mch on mch.pub_cde = dtl.pub_Cde and mch.mbr_chc = dtl.mbr_chc\nwhere dtl.ctg_id = 'SAVELF' --update the control group id\n--and dtl.ctg_dte > cast(getDATE()-5 as date)\nand dtl.ctg_dte >= cast(getDATE() as date)\nand dtl.opr_Cde = 'UPG'\n--and dtl.opr_Cde = 'CUS'\ngroup by dtl.pub_Cde, dtl.mbr_org, coalesce(sch.sub_chc, mch.mbr_chc), coalesce(sch.chc_Dsc, mch.chc_dsc)\n\n\n----Counts by Group\nSELECT DISTINCT [GROUP], COUNT(*)\nFROM [ADVDBWRK].[DBO].[VEL_FOLD] [VEL] --update the fold table name and abbreviation\nLEFT OUTER JOIN CIRSUB_M SUB ON SUB.PUB_CDE = [VEL].PUB_CDE AND SUB.SUB_REF = [VEL].SUB_REF\nGROUP BY [GROUP]\nORDER BY 1\n\n","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"Check to see if the cost offset is off.","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct a.*\nfrom (\nselect distinct ord.ord_ctg, ord.shp_Ctm, sum(tot_cst) [Cost]\nfrom mstord_m ord\nwhere ord.ord_Ctg in ('SARIDF') --update the control group id\nand ord.ctg_dte >= cast(getDATE() as date)\ngroup by ord.ord_ctg, ord.shp_Ctm) a\nwhere abs(a.cost) > 0.01\n--and a.SHP_CTM not in \n--(\n--SELECT DISTINCT\n--CTM_NBR\n--FROM [ADVDBWRK].[DBO].[RID_FOLD] [RID] --update the fold table name and abbreviation\n--where [group] = '04'\n--)\norder by [Cost] desc\n-- cost offset is out by more than a penny\n\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"**DO NOT RELEASE THE CONTROL GROUP TILL YOU RUN THE NEXT RATE NEXT TERM SQL UPDATE**","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"RUN THE AUTO RENEW SQL UPDATE","type":"text"}]},{"type":"paragraph","content":[{"text":"For Group 2, there next rate for the C flags need to be updated. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Run the below SQL to populate the work table. Then request the CDSFL4 process.","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"\n---update next rate and term\ndrop table if exists [advdbwrk].[dbo].[FOLD_NRNT]\n\ndeclare @pub_Cde varchar(3) = 'AWK'\ndeclare @new_pub varchar(3) = 'TGT'\ndeclare @ctg_Dte datetime = cast(getDate() as date)\ndeclare @ord_Ctg varchar(6) = 'SAAWKF'\nselect distinct sub.ctm_nbr, CTGDTL.PUB_CDE, CTGDTL.SUB_REF, CTGDTL.ORD_ID, CTGDTL.RN_FLG,\ncoalesce(scdf2.rate, dtf2.nxt_rat, scd2.rate, dt2.nxt_rat, sb2.rate, scdf.rate, dtf.nxt_Rat, scd.rate, dtl.nxt_Rat, sub.rate) [NXT_RAT],\ncoalesce(schf2.term, dtf2.nxt_Term, sch2.term, dt2.nxt_Term, sb2.term, schf.term, dtf.nxt_Term, sch.term, dtl.nxt_term, sub.term) [NXT_TERM],\ncoalesce(fut2.schn_cde, sb2.schn_cde, '') [schn_cde],\ncoalesce(fut2.sub_cseq, sb2.sub_cseq, '0') [sub_cseq],\nctgdtl.ctg_id, ctgdtl.ctg_Dte\n\n/*,scdf2.Rate, schf2.term, \n dtf2.nxt_rat, dtf2.nxt_Term,-- future2 detail next rate and term\n\t\t\t\tscd2.rate, sch2.term,\n\t\t\t\tdt2.nxt_Rat, dt2.nxt_term,-- sub2 detail next rate and term\n\t\t\t\tscdf.rate, schf.term,\n\t\t\t\tdtf.nxt_rat, dtf.nxt_Term,-- future detail next rate and term \n\t\t\t\tscd.rate, sch.term,\n\t\t\t\tdtl.nxt_Rat, dtl.nxt_Term,-- sub detail next rate and term\n\t\t\t\tSB2.rn_Flg [ZEN_RN_FLG], sub.rn_Flg, sub.**/\ninto advdbwrk.dbo.FOLD_NRNT\nfrom cirsub_m sub\nleft outer join cirdtl_m dtl on dtl.pub_cde = sub.pub_Cde and dtl.ord_id = sub.org_ord and dtl.nxt_term <> 0\nleft outer join cirfut_m fut on fut.pub_Cde = sub.pub_cde and fut.sub_Ref = sub.sub_Ref and fut.trm_nbr = (select max(trm_nbr) from cirfut_m where pub_cde = sub.pub_Cde and sub_Ref = fut.sub_Ref) and fut.crc_sts not in ('C', 'N')\nleft outer join cirdtl_m dtf on dtf.pub_cde = fut.pub_cde and dtf.ord_id = fut.org_ord and dtf.nxt_Term <> 0\nleft outer join cirscs_T ars on ars.pub_cde = sub.pub_Cde and ars.schn_cde = sub.schn_cde and ars.sub_cseq = case when sub.sub_cseq = (select max(sub_cseq) from cirscs_T where pub_cde = sub.pub_Cde and schn_cde = sub.schn_cde) then sub.sub_cseq else sub.sub_cseq +1 end -- sub chain seq\nleft outer join cpnscd_m scd on scd.pub_Cde = ars.pub_cde and scd.sub_chc = ars.sub_chc and scd.bil_cur = sub.bil_cur -- sub chain choice\nleft outer join cpnsch_m sch on sch.pub_cde = scd.pub_cde and sch.sub_chc = scd.sub_chc\nleft outer join cirscs_T arsf on arsf.pub_Cde = fut.pub_Cde and arsf.schn_cde = fut.schn_cde and arsf.sub_cseq = case when fut.sub_cseq = (select max(sub_cseq) from cirscs_T where pub_Cde = fut.pub_cde and schn_cde = fut.schn_cde) then fut.sub_cseq else fut.sub_cseq +1 end -- fut chain seq\nleft outer join cpnscd_m scdf on scdf.pub_Cde = arsf.pub_Cde and scdf.sub_chc = arsf.sub_chc and scdf.bil_cur = fut.bil_cur -- fut chain choice\nleft outer join cpnsch_m schf on schf.pub_cde = scdf.pub_Cde and schf.sub_chc = scdf.sub_chc\nleft outer join cirsub_m sb2 on SB2.ctm_nbr = sub.ctm_nbr and SB2.pub_Cde = @new_pub and SB2.crc_sts not in ('C', 'E', 'N')\nleft outer join cirdtl_m dt2 on dt2.pub_Cde = SB2.pub_cde and dt2.ord_id = SB2.org_ord and dt2.nxt_term <> 0\nleft outer join cirfut_m fut2 on fut2.pub_Cde = SB2.pub_Cde and fut2.sub_Ref = SB2.sub_Ref and fut2.trm_nbr = (select max(trm_nbr) from cirfut_m where pub_Cde = SB2.pub_Cde and sub_Ref = SB2.sub_Ref) and fut2.crc_sts not in ('C', 'N')\nleft outer join cirdtl_m dtf2 on dtf2.pub_Cde = fut2.pub_Cde and dtf2.ord_id = fut2.org_ord and dtf2.nxt_term <> 0\nleft outer join cirscs_T ars2 on ars2.pub_Cde = SB2.pub_Cde and ars2.schn_cde = SB2.schn_cde and ars2.sub_cseq = case when SB2.sub_cseq = (select max(sub_cseq) from cirscs_T where pub_cde = SB2.pub_Cde and schn_cde = SB2.schn_cde) then SB2.sub_cseq else SB2.sub_cseq +1 end -- SB2 chain seq\nleft outer join cpnscd_m scd2 on scd2.pub_Cde = ars2.pub_Cde and scd2.sub_chc = ars2.sub_chc and scd2.bil_cur = SB2.bil_cur\nleft outer join cpnsch_m sch2 on sch2.pub_Cde = scd2.pub_Cde and sch2.sub_chc = scd2.sub_chc\nleft outer join cirscs_T arsf2 on arsf2.pub_Cde = fut2.pub_Cde and arsf2.schn_cde = fut2.schn_cde and arsf2.sub_cseq = case when fut2.sub_cseq = (select max(sub_cseq) from cirscs_T where pub_cde = fut2.pub_Cde and schn_cde = fut2.schn_cde) then fut2.sub_cseq else fut2.sub_cseq +1 end --FUT2 chain seq\nleft outer join cpnscd_m scdf2 on scdf2.pub_cde = arsf2.pub_Cde and scdf2.sub_chc = arsf2.sub_chc and scdf2.bil_Cur = fut2.bil_Cur\nleft outer join cpnsch_m schf2 on schf2.pub_Cde = scdf2.pub_Cde and schf2.sub_chc = scdf2.sub_chc\nleft outer join cirdtl_m ctgdtl on ctgdtl.ctm_nbr = sub.ctm_nbr and ctgdtl.opr_Cde = 'UPG' and ctgdtl.pub_cde = @new_pub and ctgdtl.ctg_Dte = @ctg_Dte and ctgdtl.ctg_id = @ord_ctg\nwhere sub.pub_cde = @pub_cde\nand sub.crc_sts not in ('C', 'E', 'N')\nand sub.sub_cls <> 'P'\nand sub.fin_itg > 0\nand ctgdtl.rn_flg = 'C'\n--and ((sub.rn_flg = 'C' and SB2.pub_Cde is null) or SB2.rn_flg = 'C')\n--and sub.rn_flg = 'C'\nand sub.sub_Ref in (select sub_ref from cirdtl_m where ctg_id = @ord_Ctg and ctg_dte = @ctg_Dte)\n\n\n\nselect nrnt.*, dtl.*\nfrom cirdtl_m dtl\nleft outer join advdbwrk.dbo.FOLD_NRNT nrnt on nrnt.ctm_nbr = dtl.ctm_nbr AND NRNT.PUB_CDE = DTL.PUB_CDE AND NRNT.SUB_rEF = DTL.SUB_rEF AND NRNT.ORD_id = DTL.ORD_id\nwhere dtl.ctg_id = @ord_Ctg\nand dtl.ctg_Dte = @ctg_dte\nand dtl.opr_cde = 'UPG'\nand dtl.rn_Flg = 'C'\nand dtl.pub_Cde = @new_pub\nand nrnt.sub_Ref is not null\norder by dtl.sub_typ, nrnt.nxt_rat\ngo\n\n\n\n\n","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"SQL UPDATE REQUEST : ","type":"text"}]},{"type":"paragraph","content":[{"text":"This is the SQL that runs when you request the CDSFL4 process. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"--SQL update below\nbegin tran\n\nupdate dtl\nset dtl.nxt_rat = nrnt.nxt_rat, dtl.nxt_term = nrnt.nxt_term, dtl.schn_cde = nrnt.schn_cde, dtl.sub_cseq = nrnt.sub_cseq\n--select distinct nrnt.pub_cde, nrnt.nxt_Rat, nrnt.nxt_Term, nrnt.schn_cde, nrnt.sub_cseq, ord.ord_num, nrnt.ctg_id, nrnt.ctg_dte\nfrom cirdtl_m dtl\nleft outer join advdbwrk.dbo.FOLD_NRNT nrnt on nrnt.ctm_nbr = dtl.ctm_nbr AND NRNT.PUB_cDE = DTL.PUB_CDE AND NRNT.SUB_rEF = DTL.SUB_rEF AND NRNT.ORD_ID = DTL.ORD_ID\nleft outer join mstord_m ord on ord.ord_ID = NRNT.ORD_ID\nLEFT OUTER JOIN MSTCTG_M CTG ON CTG.ORD_CTG = ORD.ORD_CTG AND CTG.CTG_DTE = ORD.CTG_DTE\nwhere dtl.ctg_id = NRNT.CTG_ID\nand dtl.ctg_Dte = NRNT.CTG_DTE\nand dtl.opr_cde = 'UPG'\nand dtl.rn_Flg = 'C'\nAND ORD.ORD_POST = 'N'\nAND CTG.CTG_STA = 'C'\nand nrnt.ctm_nbr is not null\n\ndrop table if exists [advdbwrk].[dbo].[FOLD_NRNT]\n\n--commit tran\n--rollback tran\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"You can re-run the control group query, but add the detail to see the next rate and term update:","type":"text"},{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"select dtl.ord_id, dtl.pub_cde, dtl.ctm_nbr, dtl.sub_ref, dtl.trm_nbr, dtl.opr_cde, dtl.sub_typ, dtl.sub_cls, \ncase when dtl.opr_Cde = 'UPC' then sub.fin_itg when dtl.opr_Cde = 'UPG' then dtl.term else '' end 'Issues',\ndtl.rate, dtl.rn_flg, dtl.key_cde, dtl.mbr_chc,\n dtl.sub_chc, IsNull((convert(varchar(10), scd.nxt_rat)), '') as \"Next Rate\", IsNull(convert(varchar(10), scd.nxt_term), '') as \"Next Term\", dtl.SCHN_CDE, dtl.sub_cseq,\n\t ord.epi_idn, ord.tot_cst, dtl.nxt_rat, dtl.nxt_term\n,DMO.LifetimeSubscriber\n--sub.*\nfrom cirdtl_m dtl\nleft outer join cpnscd_m scd on scd.pub_cde = dtl.pub_cde and scd.sub_chc = dtl.sub_chc\nleft outer join cirhdr_m hdr on hdr.ord_id = dtl.ord_id\nleft outer join mstord_m ord on ord.ord_id = dtl.ord_id\nleft outer join cirsub_m sub on sub.pub_cde = dtl.pub_cde and sub.sub_Ref = dtl.sub_Ref\nLEFT OUTER JOIN CIRSUB_M_ DMO ON DMO.PublicationCode = SUB.PUB_CDE AND DMO.SubscriptionReference = SUB.SUB_REF\nwhere dtl.ctg_id in ('SANEOF') --update the control group id\n--and dtl.ctg_dte >= cast(getDATE()-5 as date)\nand dtl.ctg_dte = cast(getDATE()-1 as date)\nand ord.ord_post <> 'Y'\n--and opr_Cde not in ('UPG','UPC')\n--and opr_Cde not in ('CUS')\n--and DMO.LifetimeSubscriber = 02\nand dtl.SUB_CHC = 'NEO002'\n","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"BALANCE AND RELEASE CONTROL GROUP","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"RUN CYCLE END","type":"text"}]},{"type":"paragraph","content":[{"text":"This will most likely be the CIR4MQ for the folding pub. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Cycle End Mode: U","type":"text"}]},{"type":"paragraph","content":[{"text":"Liability Report: Y","type":"text"}]},{"type":"paragraph","content":[{"text":"Produce Labels: Y","type":"text"},{"type":"hardBreak"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"FINAL QUERY","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"SELECT DISTINCT\nSUB.*\nFROM CIRSUB_M SUB\nWHERE SUB.PUB_CDE = 'PUB CODE HERE'\nAND SUB.CRC_STS NOT IN ('C', 'E', 'N')\n--AND SUB.FIN_ITG > 0\n--AND SUB.SUB_CLS <> 'P'\n--AND SUB.MBR_ORG = ''","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"SET PUB TO INACTIVE AT INVDAT","type":"text"}]},{"type":"paragraph","content":[{"text":"Set Saleable Flag to “N”","type":"text"}]},{"type":"paragraph","content":[{"text":"Set Product Family to “NO LONGER ACTIVE”","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":4},"content":[{"text":"CLEANUP","type":"text"}]},{"type":"paragraph","content":[{"text":"By cleanup, we mean that there are almost always some subscriptions that fall out of the established criteria for the groups we are folding. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"SEND CREDIT FILE","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"select distinct uch.crx_vch [Voucher Number], uch.ctm_nbr [Customer Number], cast(uch.crd_bal as money) [Credit Amount]\nfrom arpuch_m uch\nleft outer join cirhdr_m hdr on hdr.crx_vch = uch.crx_vch\nleft outer join cirdtl_m dtl on dtl.ord_nbr = hdr.ord_nbr\nwhere dtl.pub_cde = 'SKP' --old pub\nand dtl.opr_cde = 'CUS'\nand dtl.can_rea = 'CFC'\n-- credits left intentionally","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"MOD MIG","type":"text"}]},{"type":"paragraph","content":[{"text":"When doing a MOD MIG, you generally want to have the Billing Code set to P (paid up subscription).","type":"text"},{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"You’ll set the RATE = Remaining Liability, and TERM = final issues to go","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"WRITEOFF","type":"text"}]},{"type":"paragraph","content":[{"text":"To do a writeoff at ARPCAR, follow these steps: ","type":"text"}]},{"type":"paragraph","content":[{"text":"Search for the customer. Identify the remaining amount transaction line. R-click and “Add Payment”. Skip the Saved Payment Methods dialog box. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Populate the “Payment Type” field with “Writeoff”","type":"text"}]},{"type":"paragraph","content":[{"text":"Populate the “Amount” field with the amount of the writeoff. You can leave the Number blank. ","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":728,"id":"1511f477-0370-41ee-aa76-9a3e56440285","collection":"contentId-47591526081","type":"file","height":366}}]},{"type":"paragraph","content":[{"text":"Click “OK” and the unpaid application should be paid. ","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"COMPS","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineCard","attrs":{"url":"https://14west.atlassian.net/wiki/spaces/ADV/pages/47104851972"}},{"text":" ","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"GHOST ORDER","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph"},{"type":"codeBlock","content":[{"text":"\n--MSTDTL_M --1 record\nbegin tran\ndelete mdtl\n--select distinct mdtl.*\nfrom MSTDTL_M mdtl\nwhere 1=1 \nand mdtl.ORD_NUM = 'CF203059'\n--commit tran\n--rollback tran\n\n\n--CIRDTL_M -- 1 record\nbegin tran\ndelete cdtl\n--select distinct cdtl.*\nfrom CIRDTL_M cdtl\nwhere 1=1\nand cdtl.ORD_NBR = 'CF203059'\n--commit tran\n--rollback tran\n\n\n--PROORD_M-- 0 records\nbegin tran\ndelete pord\n--select distinct pord.*\nfrom PROORD_M pord\nwhere 1=1 \nand pord.ORD_NUM = 'CF203059'\n--commit tran\n--rollback tran\n\n\n--MSTORD_M ---0 records\nbegin tran\ndelete mord\n--select distinct mord.*\nfrom MSTORD_M mord\nwhere 1=1 \nand mord.ORD_NUM = 'CF203059'\n--commit tran\n--rollback tran","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"EXTENSIONS","type":"text"}]},{"type":"paragraph","content":[{"text":"Use the below SQL to generate Term Extensions via OSM for current and future Terms","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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. ","type":"text"},{"type":"hardBreak"},{"type":"hardBreak"},{"text":"Do NOT run the MST125 ","type":"text","marks":[{"type":"strong"}]},{"text":"- just let the file get picked up by the OSMOPS jobstream. ","type":"text"},{"type":"hardBreak"},{"type":"hardBreak"}]},{"type":"codeBlock","content":[{"text":"print 'drop temp table if it already exists'\nif OBJECT_ID('tempdb..#temp01') is not null \n drop table #temp01 \n\nprint 'create temp table from query' \nSELECT DISTINCT a.PUB_CDE, a.CTM_NBR, a.SUB_REF, a.trm_nbr, 12 as ext_isu\ninto #temp01\nfrom (\nselect distinct sub.pub_cde, sub.ctm_nbr, sub.sub_Ref, dtl.trm_nbr\nfrom cirdtl_m dtl\nleft outer join cirsub_m sub on sub.pub_cde = dtl.pub_cde and sub.sub_Ref = dtl.sub_Ref\nwhere dtl.ctg_id = 'SASVCF'\nand dtl.ctg_dte = '20220812'\nand dtl.opr_cde = 'UPG'\nand dtl.pub_cde = 'SFT'\nand dtl.rn_flg <> 'A'\nand sub.crc_sts not in ('c', 'e', 'n')\nand not exists (select top 1 * from cirdtl_m where pub_cde = dtl.pub_cde and sub_Ref = dtl.sub_Ref and trm_nbr = dtl.trm_nbr and opr_Cde = 'EXT' and ctg_dte = '20220812')\n\n) a\n--select * from #temp01\n\nprint 'now create OSM file'\nSET DATEFORMAT DMY\ndeclare @date varchar(10)\nset @date = cast(year(getdate()) as varchar) + '-' + right('0'+cast(month(getdate()) as varchar),2)+ '-' + right('0'+cast(day(getdate()) as varchar),2)\nprint @date\n--- delete #TEMP_OSM1 \nprint 'drop temp table if it already exists'\nif OBJECT_ID('tempdb..#temp_OSM1') is not null \n drop table #temp_OSM1 \nCreate table #TEMP_OSM1 (ALLSHIT varchar(max), OrderNo int) \ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('',1)\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('OPS',2)\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('OPS',3)\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues (''+@date+'',4) --SQL to set\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('EXT001',5) --SQL to set -\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('0',6) --SQL to set?\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('0',7) --SQL to set (not sure if needed - try ignoring!\nset nocount on\t\n\nDECLARE @count_PROGRESS BIGINT, @pub_cde varchar(50), @ctm_nbr varchar(50), @sub_ref varchar(50), \n @trm_nbr varchar(50), @ext_isu varchar(8), @ALLSHIT varchar(5000),\n @YYYY varchar(4), @mm varchar(2), @dd varchar(2)\nset @count_PROGRESS = 0\nset @YYYY = year(getdate())\nset @MM = month(getdate())\nset @MM = right('0'+@MM,2)\nset @DD = day(getdate())\nset @DD = right('0'+@DD,2)\nprint @YYYY+@MM+@DD\nset @count_PROGRESS = 7\ndeclare Cursor_SubscriptionTermExtension cursor\nfor\nselect pub_cde, ctm_nbr, sub_ref, trm_nbr, ext_isu from #temp01\nfor update\n\nopen Cursor_SubscriptionTermExtension\nfetch next from Cursor_SubscriptionTermExtension into\n @pub_cde, @ctm_nbr, @sub_ref, @trm_nbr, @ext_isu\n\nwhile (@@fetch_status <> -1)\nbegin\n\tif (@@fetch_status <> -2)\n\tbegin\n\tset @count_PROGRESS = @count_PROGRESS + 1\n\tif right(cast(@count_PROGRESS as varchar), 3) = '000' print @count_PROGRESS\n\tset @ALLSHIT = ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''+@pub_cde+''\n\tset @ALLSHIT = @ALLSHIT + ''+@ctm_nbr+''\n\tset @ALLSHIT = @ALLSHIT + ''+@sub_ref+''\n\tset @ALLSHIT = @ALLSHIT + ''+@trm_nbr+''\n\tset @ALLSHIT = @ALLSHIT + ''\n\tset @ALLSHIT = @ALLSHIT + ''+@ext_isu+''\n\tset @ALLSHIT = @ALLSHIT + ''\n\t\n\n\n\tbegin\n\tINSERT INTO #TEMP_OSM1 (allshit, OrderNo)\n\tvalues (@ALLSHIT, @count_PROGRESS)\n\tend\n\n\tend\n\nfetch next from Cursor_SubscriptionTermExtension into\n @pub_cde, @ctm_nbr, @sub_ref, @trm_nbr, @ext_isu\nend\nclose Cursor_SubscriptionTermExtension\ndeallocate Cursor_SubscriptionTermExtension\n\ninsert into #TEMP_OSM1 (ALLSHIT, OrderNo)\nvalues ('', (select max(OrderNo) from #TEMP_OSM1) +1)\nset nocount off\nprint '@count_PROGRESS: ' + cast(@count_PROGRESS as varchar)\nprint 'output data for OSM'\n\n\n\nselect * from #TEMP_OSM1\norder by OrderNo\n\n","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"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). ","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"NOTES","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Uploading Orders using FileGen and the CIR125","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"CIR125 Process Options","type":"text"},{"type":"hardBreak"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":607,"id":"72e2a80e-2d40-4b96-a07d-d8d308bf6d40","collection":"contentId-47591526081","type":"file","height":694}}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Control Groups for the CIR125 process start with ZCR","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":5},"content":[{"text":"Form Letter for Fold Ticket","type":"text"}]},{"type":"paragraph","content":[{"text":"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:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"I’m planning on cancelling any current subscriptions which meet the following criteria:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"status is ‘Undeliverable’ or ‘Suspended’ or ‘Temp Suspended’","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"part of a membership with rate = $0","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"issues to go = 0","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"status is comp, but not a lifetime","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Customers are grouped into 4 categories that we take the following actions on:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Group 1 - Folding Pub status Paid, type is LIFE or renewal flag A, does not have New Pub LIFE.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"these customers will get New Pub LIFE.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"these customers get remaining issues of Folding Pub folded into New Pub with renewal flag C.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Group 3 - Folding Pub Paid, not LIFE, has New Pub in renewal flag not = C, or no New Pub.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"these customers get remaining issues of Folding Pub folded into New Pub w/ renewal flag Y.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Group 4 - Folding Pub Paid, but already have New Pub LIFE.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"we’ll send you an excel file with the customer information and the credit amount they have after the fold. ","type":"text"}]}]}]}]}]}]}]},{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Let me know if you have questions or comments.","type":"text"},{"type":"hardBreak"}]},{"type":"paragraph","content":[{"text":"leftovers usually have lifetime for both, and those we can cancel.","type":"text"},{"type":"hardBreak"},{"text":"add CVI records (if necessary)","type":"text"},{"type":"hardBreak"},{"text":"do extensions (if necessary)","type":"text"},{"type":"hardBreak"},{"text":"Cancel comps (if necessary)","type":"text"},{"type":"hardBreak"},{"text":"update member cat (if MBR)","type":"text"},{"type":"hardBreak"},{"text":"add baby pubs (if MBR)","type":"text"},{"type":"hardBreak"},{"text":"Insert ARPEPA_M records (if necessary)","type":"text"}]}],"version":1}

Browser not supported