Refund Export Formats
Note: All of the interfaces described in this section require a separate license fee to use. Contact the Naviga Support Center for more information.
File type: Depending on how you set up Business Rules, the ASCII file will be written in a format that can be interfaced with SBS (DTI Standard format), Great Plains, PeopleSoft, PeopleSoft II, PeopleSoft3, Compu-Share, Dunn and Bradstreet, PC Charge, or Lawson.
The DTI Standard format is space delimited with non-numeric fields enclosed in double quotes.
The Great Plains format is comma-delimited, with each value (including the field names), enclosed in quotation marks.
The PeopleSoft, PeopleSoft II, PeopleSoft3, Lawson, and Dunn and Bradstreet formats are fixed length. The PeopleSoft 3 format is identical to the PeopleSoft 3 format for interfacing carrier credits (see PeopleSoft 3 Format), with the subscriber ID exported in place of the account ID.
The Compu-Share format is also fixed length, and is identical to the Compu-Share format for interfacing carrier credits (see Compu-Share Format).
Credit card refunds will be in their own file, ccrefund.d, in the ICVerify format. In the case of SBS and PeopleSoft, a vendor file will also be created. Business Rules determine the vendor file name (the default is “apvendref.d”). The format is the same as the Carrier AP vendor file (see Interface to AP Formats), but the vendor information will be for the subscriber rather than the carrier.
The PC Charge format is space-delimited with each field enclosed in double quotation marks. Each transaction will be on one line that ends in a carriage return and line feed. The file is identical to the subscriber payment file (see PC Charge Format for the format.)
Location: /dti/exchange/cm/secure/aprefund.d or /dti/exchange/cm/secure/ccrefund.d
Note: The default file name for the refund export file is “aprefund.d.” The Business Rule, What is the default file name for cash refunds? (Subscriber Accounting section), allows you to change this default.
DTI Standard Format
| Field | Sample Output |
|---|---|
Subscriber vendor company ID | “09” |
Subscriber vendor | “1992” |
Fiscal year | “1996” |
Fiscal period | “4” |
Invoice date | “10/15/96” |
Due date | “10/20/96” |
GL Account for distribution | “0060021” |
Refund amount | 6.51 |
Current date | 10/26/96 |
Subscriber ID | 55555 |
Occupant address (4 fields) | “Roman Negler” “1985 Page St.” “St. Paul MN 55114” “ ” |
Combo subscription ID (export 0 if not a combo) | 0 |
Great Plains Format
Note: The first line of the export file will contain the field (column) headings. The remaining lines will contain data.
| Field | Sample Output |
|---|---|
PUB CODE | “1111” (from Business Rules) |
ACCOUNT | “2222” (from Business Rules) |
REFUND DATE | “04/29/09” |
REFUND AMT | 48.37 |
SS NUMBER | “ ” |
SUBSCRIPTION ID | “5932” |
NAME | HAMRICK J |
ADDRESS 1 | “212 MATHEWS ST” |
ADDRESS 2 | “APT 1” |
CITY | “LEWISBURG” |
STATE | “WV” |
ZIP | 24901-1236 |
TELEPHONE | 3048365406 |
PeopleSoft Format
| Field | L |
|---|---|
Business Unit | 5 |
Vendor type (will always be 0) | 1 |
Vendor Number (vendor short name + R + sequence number) | 10 |
Name1 | 40 |
Filler | 40 |
Address1 | 35 |
Address 2 | 35 |
Address 3 | 35 |
Address 4 | 35 |
City | 30 |
State | 2 |
ZIP Code | 10 |
Country | 3 |
Phone | 12 |
Fax Number (will be blank) | 12 |
1099 Vendor (will be N) | 1 |
1099 TIN Type (will be blank) | 1 |
1099 TIN (will be blank) | 9 |
1099 Code (will be blank) | 2 |
Filler | 2 |
1099 Reportable (will be N) | 1 |
Filler (zeros) | 16 |
Invoice Date | 10 |
Gross Amount | 17 |
PS Chart Fields (GL account, department, profit center, project, category, product, country) | 32 |
Filler | 10 |
District/Route (will be blank) | 8 |
PeopleSoft II Format
| Field | Length | Required | Description |
|---|---|---|---|
TRANSACTION_TYPE | x(3) | Yes | Transaction type |
BUSINESS_UNIT | x(5) | Yes | Newspaper business unit |
AMOUNT | 9(00.0) | Yes | Amount to be paid |
INVOICE_ID | x(30) | Yes | Invoice number |
INVOICE_DT | date (10) | Yes | Invoice date |
ACCOUNTING_DT | date(10) | Yes | Accounting date |
PYMNT_MESSAGE | x(70) | Yes | Payment message |
BUSINESS_UNIT_GL | x(5) | Yes | GL business unit |
ACCOUNT | x(6) | Yes | Account |
DEPTID | x(3) | No | Department ID |
PRODUCT | x(2) | No | Edition |
AFFILIATE | x(5) | No | Affiliate |
CHARTFIELD1 | x(3) | No | Local 1 |
CHARTFIELD2 | x(3) | No | Local 2 |
CHARTFIELD3 | x(6) | No | Project ID |
XX_CIRC_TYPE | x(1) | Yes | Circulation type |
XX_CIRC_ID1 | x(15) | Yes | Circulation ID 1 |
XX_CIRC_ID2 | x(10) | Yes | Circulation ID 2 |
XX_GARN_ID1 | x(15) | No | Garnishment ID 1 |
XX_GARN_ID2 | x(10) | No | Garnishment ID 2 |
XX_CASE_ID | x(15) | No | Case ID |
NAME1 | x(40) | Yes | Name |
ADDRESS1 | x(55) | Yes | Address line 1 |
ADDRESS2 | x(55) | No | Address line 2 |
ADDRESS3 | x(55) | No | Address line 3 |
ADDRESS4 | x(55) | No | Address line 4 |
CITY | x(30) | Yes | City |
STATE | x(2) | Yes | State |
POSTAL | x(12) | Yes | Postal |
COUNTRY | x(3) | Yes | County |
Lawson Format
| Record | Field | Length/Type | Field Contents |
|---|---|---|---|
Control | AP-Asterisk | x(1) | * |
| AP-Company | 9(4) | GL Company ID |
| Filler | x(1) | Space |
| AP-Due-Date | 9(6) | Refund date (prompted for). Format YYMMDD |
| AP-Fiscal-Period | 9(4) | Fiscal period (prompted for). Format MMYY |
| Filler | x(154) | Spaces |
|
|
|
|
Detail | AP-Payment-Type | x(1) | 3 (refund) |
| AP-Sub-Type | x(1) | Space |
| AP-Vendor-Number | x(9) | Subscription ID (right justified) |
| AP-Vendor-Name | x(30) | Subscriber’s first name plus last name |
| AP-Vendor-Addr1 | x(30) | First line of subscriber’s refund address |
| AP-Vendor-Addr2 | x(30) | Second line of subscriber’s refund address |
| AP-Vendor-City | x(21) | City of refund address* |
| AP-Vendor-State | x(2) | State of refund address* |
| AP-Zip-Code | x(9) | Zip of refund address* |
| AP-Payment-Amount | 9(10) | Refund amount—right justified, zero filled with last two spaces for cents (no decimal) |
| AP-Direct-Deposit-Ind | x(1) | Space |
| AP-Tax-ID | x(9) | Spaces |
| AP-Income-Code | x(2) | Spaces |
| AP-1099-Amount | 9(10) | Zero-filled |
| AP-Invoice-Group | x(4) | Spaces |
| Filler | x(1) | Space |
* If the refund address is not the subscriber’s delivery address, the city, state and ZIP will be blank, as these address elements will be in the second address line (AP-Vendor-Addr2 field).
Dunn and Bradstreet Format
| Record | Field | L |
|---|---|---|
Control | AP AST (will always be “*”) | 1 |
| Filler | 2 |
| AP Corp Number (will always be “16”) | 2 |
| Filler | 1 |
| Refund Due Date (due date entered with refund, format YYMMDD) | 6 |
| Fiscal Period (will be spaces) | 4 |
| Filler | 137 |
|
|
|
Detail | Payment Type (will always be “3”) | 1 |
| Sub Type (will be a space) | 1 |
| Alpha Vendor (will be a space) | 1 |
| Alpha-numeric Vendor (will be zeros) | 8 |
| AP Vendor Name (subscriber first name + last name) | 30 |
| AP Vendor Address Line 1 (subscriber’s primary address, 1st line) | 30 |
| AP Vendor Address Line 2 (subscriber’s primary address, 2nd line) | 30 |
| AP Vendor City (subscriber’s city) | 21 |
| AP Vendor State (subscriber’s state) | 2 |
| AP ZIP (subscriber’s ZIP Code) | 5 |
| AP ZIP+4 (subscriber’s ZIP extension) | 4 |
| Payment amount (amount of refund, format 9999999999). The last two digits are decimals. For negative numbers (credits), use a Signed Overpunch (set last digit to a character as follows: 0=}, 1=J, 2=K, 3=L, 4=M, 5=N, 6=O, 7=P, 8=Q, 9=R) | 10 |
| Refund delivery type (“M” if subscription is mail delivered, otherwise “P”) | 1 |
| Filler | 9 |
JD Edwards Format
| Field | Description | Sample Output |
|---|---|---|
Address Book Number | Subscription ID | 55555 |
Invoice Date | Refund date | “10/15/13” |
G/L Date | Refund date | “10/15/13” |
Amount | Refund amount | 6.51 |
First Name | Subscriber first name | “Debbie” |
Last Name | Subscriber last name | “Jones” |
Occupant address (4 fields) | Subscriber address | “1985 Page St.” “St. Paul MN 55114” “” “” |
City | Subscriber city | “St. Paul” |
State | Subscriber state | “MN” |
Postal Code | Subscriber postal code | “55113” |
Phone Area Code | Subscriber area code (if available) | “651” |
Phone Number | Subscriber phone number (if available) | “6390662” |
Format for Credit Card Refund (ICVerify)
| Field |
|---|
“C3” (beginning of credit card refund) |
User ID of operator who processed refunds |
Subscription ID of credit card payment customer |
Credit card number |
Expiration date of credit card (YYMM or MMYY format, depending on Business Rules) |
Payment amount including tip (use decimal point—e.g. 2.33) |
Subscriber ZIP Code |
Subscriber house number (0 if house number is blank) |
14 blank fields |
Sample Output
“C3”,“tomk”,“2989”,“57483773”,“0799”,“50.09”,“55443”,“412”,““,””,””,””,““,””,””,””,””,””,””,””,””,””
| Field | Sample Output |
|---|---|
“ST” (beginning of close batch record) | “ST” |
21 blank fields | ““,””,””,””, etc ... |
Format for Credit Card Refunds (Skipjack)
| Field | Sample Output |
|---|---|
Change Status Field | szAPIMethod=SJAPI_TRANSACTIONCHANGESATUSREQUEST& |
Serial Number | szDeveloperSerialNumber=<business rules defined serial number>& |
Order Number | szOrderNumber=<subscription ID>& |
Status | szDesiredStatus=credit& |
Refund Amount | szAmount=<refund amount>& The decimal is required |
Force Settlement | szForceSettlement=0 |
Last updated
