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
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.
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
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
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
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
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
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)
“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”,““,””,””,””,““,””,””,””,””,””,””,””,””,””
“ST” (beginning of close batch record)
“ST”
21 blank fields
““,””,””,””, etc ...
Format for Credit Card Refunds (Skipjack)
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
