Before you
can run SUB671, you need to have fun SUB670, so the fulfill date of the
processed subscription issue is updated. SUB671 can only process
records for issues that have been fulfilled.
This batch process fulfills back issues for new subscribers and fulfills previous issues that did not get fulfilled for subscribers and members getting member-benefit subscriptions of a specified publication. This can be because of a bad address or a partial payment. Based on your batch job setup, SUB671 can also fulfill replacement issues and single-copy subscription issue sale orders.
Unlike SUB670, SUB671 is not run for a single issue; rather, SUB671 tries to fulfill any and all unfulfilled issues for issues that have been fulfilled, based on Sub_Issue.FULFILL_DATE for the publication specified in the SUB671 parameters. SUB671 can also processes replacement issues and single-copy subscription issue sales.
Before you
can run SUB671, you need to have fun SUB670, so the fulfill date of the
processed subscription issue is updated. SUB671 can only process
records for issues that have been fulfilled.
The SUB671 batch process executes the SUB671_PROCESS_SP stored procedure, which takes the following action:
· Verifies the specified issue of a publication is not already fulfilled (except when being run in Update issue-selection mode) and there is enough available quantity for the issue to be fulfilled.
· Verifies that a good address exists for the selected subscribers and members. If a good address does not exist, the subscriber or member is put on the skipped report.
· Optionally, evaluates that subscribers and members with a balance due on their subscription or membership have paid enough for the issue being fulfilled, based on the calculated unit-cost of each issue. If not, the subscriber or member is put on the skipped report. This functionality only occurs if you set the “Only Process Paid Issues” parameter to “Y.”
· Evaluates for all selected subscribers or members whether any fulfilled issues for the specified publication were not fulfilled (by evaluating whether there are any issues in the Sub_Issue table for the specified publication where the issue date falls within the subscription or membership begin date and grace date that are missing from the Sub_Issue_Fulfillment table for each order). If yes, SUB671 creates issue fulfillment records for the missing issues.
· Creates a record in the Sub_Issue_Fulfillment table for any issue being filled, and the ISSUE_FULFILL_TYPE_CODE is set to ‘B’ for backorder, ‘R’ for replacement issue, and ‘S’ for single copy sale.
· Sets BACKISSUE_FLAG to ‘N’ when all back-issue issues can be fulfilled for a subscription or membership subscription order.
· Creates a record in Order_Address_History for subscribers and members. When supplemental customers have a replacement issue fulfilled, a record is also created in the Order_Address_History table. For these customer records, the Order No and Order Line No fields are left null in the Order Address History table.
· When SUB_PRODUCT.AUDIT_AGENCY_CODE is not null and “Include in Audit Flag” is “Y” for the order line product rate code, the InsertSubAuditStats stored procedure executes which creates records in the Sub_Audit_Stats table.
· Updates back issue quantity, replacement quantity, and fulfill date on the Sub_Issue record for the issue being fulfilled. If the publication is a print publication, it also updates the available quantity.
· Updates fulfill status for subscription orders by executing the SUB671_UpdateFulfillStatus_SP stored procedure. If the subscriber has a current order in grace but has paid a renewal order (i.e., Fulfill Status Code = ‘G’ and Renewal Date This Order is not null), the fulfill status on the order in grace is updated to E (expired) and the subscription issue is fulfilled on the renewed order.
· If the publication is audited, SUB671 evaluates whether the subscription order qualifies as a “Multi-Copy Same Addressee” subscription, and if so, updates the Sub_Audit_Stats.AUDIT_TYPE_CODE to ‘MC’ for the fulfilled issue. It does this by executing the usp_DetermineAndUpdateMultiCopyAuditType stored procedure.
As advised for SUB670, TMA Resources recommends running CUS520 before running SUB670 to ensure customers using seasonal addresses have their subscription fulfilled to the correct address. If the organization is using the U.S. post office’s address change service, TMA Resources also recommends running CUS540 to upload changed address information.
Since it is critical
for subscription issue fulfillment to have the correct address, organizations
may want to set the “CUS” application parameter “PRIMARY_ADDR_PROPAGATION”
to either “AUTO” or “SELECT”.
The “PRIMARY_ADDR_PROPAGATION” application parameter identifies if /how
changes to a customer’s primary address should propagate to current, open,
or unfulfilled orders for the customer using the previous primary address.
Setting this parameter to “AUTO” means the address change propagates automatically.
Setting this parameter to “SELECT” means the user is given the option to
select which orders should have the address updated.
If subscribers are making automated credit-card or direct-debit payments based on payment schedules, FAR680 and/or EFT680 should be run before SUB670 is run, so that subscriber current balance due amounts are correct.
Subscribers and members/associate members receiving member-benefit subscriptions are selected if BACK_ISSUES_FLAG = Y. Selected members and subscribers must also meet the following conditions:
· If the publication is a print publication, the subscriber must have a good address. The process selects the subscriber’s address as follows:
o The process checks whether the ship-to address on the order is good and not of the address type “BLANK”
o If the subscriber’s order address is not good, the process checks whether the subscriber has any other good address
o If a good address cannot be found, the subscriber is skipped and appears on the exception report
· The subscription or membership cycle begin date is less than or equal to the issue date of the issue being fulfilled and the subscription or membership grace date is greater than or equal to the issue date of the issue being fulfilled.
· The order line status must be ‘A’ (active).
· The fulfill status on the order line cannot be ‘E’ (expired).
· If “Only Process Paid Issues” is “Y,” the payments received must cover the unit cost of each issue previously fulfilled for the order as well as any issues being fulfilled by SUB671
In addition, there must be sufficient available quantity of the issue. If the available quantity of the issue is less than the quantity that is needed to fulfill all back issues and replacement issues, then subscribers will be selected for issue fulfillment up to the quantity that is available.
If the “Print
Edition” checkbox is selected during the setup of the subscription product
and the customer has a bad address, SUB671 will NOT fulfill the issue.
· Skipped Report – Lists all subscription orders not fulfilled, either because of a bad address or a partially paid order. For skipped orders, SUB670 sets the Order_Detail.BACK_ISSUES_FLAG back to “Y” so the order can be selected and evaluated again for fulfillment.
· Postal Zone Analysis Report – For the issue being fulfilled, calculates counts of issues fulfilled by each unique combination of Ship Via, Zip Code and Postal Zone
· Fulfillment Summary Report - For the issue being fulfilled, calculates counts of how many issues and copies that were fulfilled are for active orders vs. grace orders grouped by rate structure and rate code. Counts of labels generated are also calculated.
· On/Off Publication Report – For the issue being fulfilled, calculates counts of how many issues and copies that were fulfilled are the first issue/first copies being fulfilled on the order, the last issue/last copies being fulfilled on the order, a grace issue/grace copies being fulfilled or a regular issue/regular copies (i.e., not the first, not the last and not a grace issue). Counts of labels generated are also calculated.
· Publication Orders by Country/State – for the issue being fulfilled, calculates counts for members, subscribers and non-subscriber customers receiving the issue by country and state
· Publication Orders by Customer Class – for the issue being fulfilled, calculates the count of subscription orders and issues grouped by customer class and customer status.
· Labels - If the publication is a print publication, generates a label for each active subscriber or member due to receive an issue or generates a file that can be used by a fulfillment house to create labels. Users have the option of specifying whether the order quantity should also be printed on the label.
Three formats of labels are supported:
· Physical
labels (printed) – Avery (A4) label format. Data fields are mapped like
the following export layout:
[Ship Master Customer] – [Ship Sub Customer]
[Label Name]
[Formatted Label]
[Issue Sequence or Supplement Sequence] Note: is both issue and supplement
exist print supplement only
· Label file – See the table below.
· XML file – XSD is an XML Scheme Definition. DTD provides less ability to validate XML. Allows for strongly typed data types. Enforces the exchange between two people.
| Parameter | Description | Required? |
|---|---|---|
| Sub Title | Free Form text to be used as report sub title for printing.
Default Value: Leave Blank |
N |
| Run Mode | This parameter has two modes EDIT and PROD. Edit mode will
not update the database only use the parameters and query to select
records for reporting. Prod mode will also produce
a report of records selected but will also update the order tables.
Default Value: EDIT |
Y |
| Org ID | Defaults to the Organization the end user is logged into when process is run. | Y |
| Org Unit ID | Read Only parameter. Defaults to the Organization the end user is logged into when process is run. | Y |
| Parent Code | Read Only parameter. Used to determine the Parent_product. This must be a product of subsystem type = ‘SUB’. | Y |
| Product Code | Enter the parent product code to select labels for. This
is used in conjunction with the Product Code to determine the
journal to select labels for.
Default Value: Blank |
Y |
| Ship Location | Enter Ship Location. If not specified and "POSTAL_ZONE_ANALYSIS" parameter is set to Y then "Postal Zone Analysis Report" will not be generated. | N |
| Bundle | NONE = Individual Label per copy of issue.
SUBSCRIBER = One Label per Subscriber Per Issue.
MULTIPLE = Aggregate all back issues by customer, that is, print one label that includes a list of all issues going to that customer.
Default Value: None |
Y |
| File Generation | This parameter accepts three values:
· SINGLE creates a single label file. · SPLIT generates one file for every Publication, Country Code, and Ship VIA Combination. · SHIPVIA generates one file for every Publication, Ship Via.
Default Value: Split |
Y |
| Break by Issue | If ‘Y’, then the label output will be generated by issue. If
‘N’, then a single output file will be created that will be sorted
by the sort parameters. File Names will be suffixed by Issue Seq
and Supplement Seq.
Default Value: N |
Y |
| Commit | This parameter specifies the number of records which must be
updated in the database at one time.
Defaults to 1. |
N |
| Label Format | LABELS - Generates standard 3 up Avery labels A4. This
is the default.
FILE - Creates a comma-separated file. File export format is described in the next section.
XML - Generates an XML using label file field listing.
Default Value: Labels |
N |
| Consolidation ID | Plain text user entered field to be included in Label exports
only.
Default Value: Blank |
N |
| Process only Paid Issues | Set this parameter to Y to process only those issues that have
been fully paid in a partially paid subscription order.
Default value is N to process all issues.
If set to Y, if Subscription order balance = 0 then process as normal. Else Calculate: Amount of Sale + Adjustments divided by the number of issues for subscription = Cost per issue.
Divide the Amount Paid by the Cost per Issue = Number of Paid Issues (Note: in order to be considered paid an issue must be issues must be fully paid. Any partial payments should not be counted. i.e. Number of paid issues = 2.7 should be rounded to 2 and not 3). Compare the number of Paid Issues to the Issues table to see if the current issue can be fulfilled. Consider orders with scheduled payments as paid for this processing. Set the Back Issue flag to true for cases where a paid issue is a Back Issue. For ‘N’ skip this processing.
Default Value: ‘N’ |
Y |
| Postal Zone Analysis | Y/N Y will print this report N will not print report.
Default Value: N |
N |
| Fulfillment Summary | Y/N Y will print this report N will not print report.
Default Value: N |
N |
| On/Off By Publication | Y/N Y will print this report N will not print report.
Default Value: N |
N |
| Publication Orders by Country / State | Y/N Y will print this report N will not print report.
Default Value: N |
N |
| Publication Orders by Customer Class | Y/N Y will print this report N will not print report.
Default Value: N |
N |
| Print Quantity on Label | Set this parameter to Y to print order quantity on label. Defaults
to Y.
Default Value: Y |
N |
| Use Address Change Service | If the flag is set to 'Y‘, labels are printed with the Intelligent Mail Barcode as the first line of the label. Appropriate values must be set for the Barcode Identifier, Service Type Identifier and Mailer ID parameters. When set to 'Y‘ the height of the label increases by one row. When set to 'N‘, the ACS format is ignored and the labels are printed without a barcode. | N |
| Barcode Identifier | A two-digit value that indicates the degree of pre-sorting this mail piece received before being presented for mail delivery. Required if “Use Address Change Service” = “Y”. The Barcode ID generally should be left as “00”. The exception is for flat mail bundles with an optional endorsement line | N |
| Service Type Identifier | A three-digit value representing both the class of the mail (such as first-class | N |
| Mailer ID | A six or nine-digit number assigned by the USPS that identifies the specific business sending the mailing. Higher volume mailers are eligible to receive six-digit Mailer IDs | N |
Addition SQL Selection |
Allows user to add an additional filter based on fields in
Order_Detail. Always use table name.column_name; for example:
Order_Detail.Order_No = '1000003613'
Default Value: 1=1 |
N |
| Supplemental Customer Select | Allows user to add an additional filter based on fields in
Customer. Always use table name.column_name; for example: Customer.CUSTOMER_CLASS_CODE
= 'COMP_SUBSCRIBER'.
When this filter is used, Supplemental Rate Structure, Supplemental Rate Code are required. |
N |
| Label Sort | Select any columns from SUB670_LABEL_VW view for the purpose
of sorting the Labels. Commonly used columns to sort the Labels
are POSTAL_CODE, COUNTRY_CODE, LAST_NAME, FIRST_NAME. Defaults
to POSTAL_CODE, LAST_NAME.
Default Value: POSTAL_CODE, LAST_NAME |
N |
| Exclude Expired Orders | When set to Y, does not include expired membership or subscription
orders for back-issue processing.
Default Value = N. |
Y |
| Include Back Issues in BPA Audit | When running this process, if the back issue selection should
be included in BPA auditing reports, choose 'Y'. If
the back issues should not be selected in the BPA auditing reports,
choose 'N'.
Default Value = N. |
N |
Report formats are the same for both SUB670 and SUB671. All reports use record set generated by the main program query.
Postal Zone Analysis
Report layout is for a laser printer portrait format. Group by Publication sort on Ship Via.
Fulfillment Summary
Report layout is for a laser printer portrait format. Group and sort on unique rate structure and rate code values
On/Off By Publication Report
Report layout is for a laser printer portrait format. Group and sort on Subscription Rate.
Publication Orders by Country / State
Report layout is for a laser printer portrait format. Group and sort on country code then state code.
Publication Orders by Customer Class
Report layout is for a laser printer portrait format. Sort on Customer Class, print one row for each unique occurrence of customer class and customer status.
Skipped Records
Report layout is for a laser printer portrait format. Sort by order number.
