SUB1005 - BPA - Subscription Audit Country Analysis Report

This Data Analyzer query report identifies subscription orders where the country of the ship-to address is missing from the Sub_Audit_Region_Detail table, which means that subscriber will not be counted in the BPA report that gives counts of subscribers by geographic location. This report uses a table that maps areas of the world and the U.S. to regions. If a subscription is sent to a subscriber in a country that is not in the BPA region table, that subscription will not be included in counts, and the geographic counts will not balance with counts from other reports.

This should be run on a monthly basis.

Parameters

·            Subscription Product Parent Product

·            Subscription Product Product Code

·            Issue Date Begin Date

·            Issue Date End Date

Selection Logic

Select order information

From Sub_Issue_Fulfillment sif,

Order_Detail od, Cus_Address_VW ca, Product p, Customer c

Where sif.ORG_ID = ORG_ID of user running report

And sif.ORG_UNIT_ID = ORG_UNIT_ID of user running report

And sif.ORDER_NO = od.ORDER_NO

And sif.ORDER_LINE_NO = od.ORDER_LINE_NO

And sif.PARENT_PRODUCT = @ParentProduct parameter value

And sif.PRODUCT_CODE = @ProductCode parameter value

And sif.ISSUE_DATE <= @IssueEndDate parameter value

And sif.ISSUE_DATE >= @IssueBeginDate parameter value

And sif.MASTER_CUSTOMER_ID = c.MASTER_CUSTOMER_ID

And sif.SUB_CUSTOMER_ID = c.SUB_CUSTOMER_ID

And sif.MASTER_CUSTOMER_ID = ca.MASTER_CUSTOMER_ID

And sif.SUB_CUSTOMER_ID = ca.SUB_CUSTOMER_ID

And sif.ADDRESS_TYPE_CODE = ca.ADDRESS_TYPE_CODE

And sif.CUS_ADDRESS_ID = ca.CUS_ADDRESS_ID

And ca.COUNTRY_CODE does not exist in Sub_Audit_Region_Detail.COUNTRY_CODE

Sample Report