Refund Export Formats
Last updated
Last updated
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 ), 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 ).
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 ), 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 for the format.)
Location: /dti/exchange/cm/secure/aprefund.d or /dti/exchange/cm/secure/ccrefund.d
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
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
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
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
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).
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
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”
“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
“C3”,“tomk”,“2989”,“57483773”,“0799”,“50.09”,“55443”,“412”,““,””,””,””,““,””,””,””,””,””,””,””,””,””
“ST” (beginning of close batch record)
“ST”
21 blank fields
““,””,””,””, etc ...
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