CSMS Message: 99-000507

TO : ALL ABI BROKERS.

FROM : DON LUTHER

SUBJECT : RECONCILIATION SPREADSHEET

DATE : 07/08/1999

***THE IS A REISSUANCE OF THIS MSG**********

RECONCILIATION SPREADSHEET SPECIFICATIONS

UNDER THE ACS RECONCILIATION PROTOTYPE, THE RECONCILIATION (TYPE
09) ENTRY INCLUDES THREE COMPONENTS. THE HEADER AND ASSOCIATION
FILE, WHICH ARE TRANSMITTED VIA ABI, AND THE SUMMARIZED LINE ITEM
DATA SPREADSHEET, PROVIDED TO CUSTOMS ON DISKETTES.

THIS MESSAGE IS TO CLARIFY THE PURPOSE AND FORM OF THE
SPREADSHEET, AND TO PROVIDE TECHNICAL SPECIFICATIONS AND
REQUIREMENTS.

THE SPREADSHEET IS THE PART OF THE RECONCILIATION WHERE IMPORTERS
REPORT THE CHANGES TO VALUE AND OTHER RECONCILABLE ASPECTS OF
THEIR BUSINESS. THESE CHANGES ARE REPORTED AT THE "MACRO" LEVEL
THAT IS, WITHOUT REFERENCE TO WHICH ENTRIES ARE AFFECTED BY THE
CHANGES. FOR EXAMPLE, AN IMPORTER COULD REPORT A VALUE CHANGE
FOR ALL IMPORTS OF A GIVEN PRODUCT DURING A TIME PERIOD, ON ONE
LINE OF THE SPREADSHEET. THE ORIGINAL AND RECONCILED VALUE
DUTIES, AND FEES SHOWN ON THAT LINE WOULD BE TOTALS FOR ALL THE
IMPORTS OF THAT PRODUCT.

THE SPREADSHEET IS THE SAME FOR BOTH AGGREGATE AND ENTRY-BY-ENTRY
RECONCILIATIONS. THE SPREADSHEET DOES NOT CONTAIN ENTRY NUMBERS,
ENTRY NUMBERS ARE ONLY SHOWN ON THE ASSOCIATION FILE.

TWO SEPARATE COPIES OF THE SPREADSHEET ARE REQUIRED, VIA
DISKETTES. ONE COPY WILL BE KEPT BY CUSTOMS, ONE FORWARDED
THE BUREAU OF CENSUS, AS THE DATA WILL BE USED TO UPDATE UPDATE
TRADE STATISTICS.

TO ENSURE COMPATIBILITY BETWEEN PRODUCERS AND USERS OF THE DATA
AND TO ENSURE CENSUS' ABILITY TO PROPERLY EXTRACT THE DATA,
CUSTOMS IS PRESCRIBING THE FOLLOWING FORMAT THAT MUST BE USED FOR
ALL RECONCILIATION SPREADSHEETS SUBMITTED WITH RECONCILIATIONS
FILED AFTER 7/15/99.

FORMAT: ASCII TEXT, COMMA DELIMITED FIELDS, TEXT DELIMITED BY
QUOTES. THIS FORMAT IS SUPPORTED BY ALL MAJOR
COMMERCIAL SPREADSHEET PROGRAMS, AND BY MOST MINI-
AND MAINFRAME COMPUTER APPLICATIONS AS WELL.

FIELDS: THE FOLLOWING FIELDS SHALL MAKE UP THE SPREADSHEET,
IN THE FORMAT AND ORDER SPECIFIED.

FIELD DEFINITION FORMAT

RECON LINE SEQUENTIAL NUMBER, WITH TEXT, 5 CHAR. MAX
"A" AND "B" FOR LINKED
LINES (9802 & CLASSIF.)

CAL. YEAR CALENDAR YEAR OF RELEASE NUMERIC, 4-DIGIT YEAR
OF MERCHANDISE

REASON ISSUE BEING RECONCILED TEXT, 12 CHAR. MAX
(FREE TEXT)

PORT PORT CODE WHERE IMPORTS TEXT, 3 CHAR. MAX
CAME IN. (2-DIGIT LEVEL
OR "ALL" IF ALL PORTS.)
THIS FIELD REQUIRED ONLY
FOR LINES INVOLVING 9802
RATIO CHANGES OR CHANGES
IN CLASSIFICATION.

ORIGIN COUNTRY OF ORIGIN OF MDSE TEXT, 2-CHAR. CODE

ORIG. SPI SPECIAL PROGRAM INDICATOR TEXT, 2-CHAR. MAX.
APPLIED TO MERCHANDISE
ON UNDERLYING ENTRIES
(E.G. 'A' FOR GSP, 'MX'
FOR MEXICO-NAFTA, ETC.

REC. SPI RECONCILED SPECIAL PROGRAM TEXT, 2-CHAR. MAX.
INDICATOR. (SHOULD ONLY
BE DIFFERENT FROM ORIG.
SPI IN CASE OF NAFTA
RECONCILIATION.

HTS HARMONIZED TARIFF CLASS'N TEXT, 10-DIGITS, WIT
OF MERCHANDISE. LEADING ZERO FOR HTS
IN CHAPTERS 1-9

ORIG. QTY TOTAL QUANTITY OF MDSE TEXT, 8 CHARACTER MAX
FROM ORIG. ENTRY LINES
NO UNITS OF MEASURE,
QUANTITY REQUIRED ONLY
FOR RECONCILIATIONS OF
CLASSIFICATION.

REC. QTY. TOTAL QUANTITY OF MDSE TEXT, 8 CHARACTER MAX
RECONCILED ON SPREAD-
SHEET LINE. (ALSO
REQUIRED ONLY FOR CLASS'N
RECONCILIATIONS.

ORIG. VALUE TOTAL VALUE OF GOODS FOR NUMERIC, 12-DIGIT,
SPREADSHEET LINE FROM
ORIGINAL ENTRIES.

RECON. VALUE TOTAL RECONCILED VALUE NUMERIC, 12-DIGIT,
FOR GOODS FOR SPREADSHEET NO DECIMAL PLACES
LINE.

ADD'L VALUE DIFFERENCE BETWEEN NUMERIC, 12-DIGIT,
RECON. VALUE AND ORIG. NO DECIMAL PLACES
VALUE. NEGATIVE VALUE SHOWN
WITH MINUS SIGN

ORIG. DUTY ORIGINAL DUTY RATE TEXT, 8 CHAR. MAX.
DUTY RATE APPLICABLE
TO MDSE ON ORIGINAL
ENTRIES.

REC. DUTY RECONCILED DUTY RATE TEXT, 8 CHAR. MAX.
(SHOULD BE SAME AS DUTY RATE APPLICAB
ORIG. DUTY EXCEPT FOR TO MDSE ON SPREADSHE
LINES BEING RECONCILED
FOR NAFTA ELIGIBILITY.

DUTY CHANGE AMOUNT OF DUTY CHANGE NUMERIC, 14-DIGIT
FOR SPREADSHEET LINE. 2 DECIMAL PLACES
(REC VALUE X REC. RATE)
MINUS (ORIG VALUE X (REFUNDS) SHOWN WI
ORIG. RATE). MINUS SIGN.

HMT CHANGE AMOUNT OF HARBOR MAINT. NUMERIC, 14-DIGIT
FEE CHANGE FOR SPREAD- 2 DECIMAL PLACES
SHEET LINE. NEGATIVE AMOUNTS SHOWN
WITH MINUS SIGN.

MPF CHANGE AMOUNT OF MERCHANDISE NUMERIC, 14-DIGIT
PROCESSING FEE CHANGE 2 DECIMAL PLACES
FOR SPREADSHEET LINE. (NEGATIVES WITH MINUS)

OTHER ITEMS ARE CONDITIONAL, BASED ON THE PRESENCE OF OTHER TAX
AND FEES. IN EACH CASE, THE FORMAT IS THE SAME AS THE FORMATS
FOR HMT AND MPF: THE AMOUNT OF MONEY INVOLVED PER TAX OR FEE,
NUMERIC 14-DIGIT, 2 DECIMAL PLACES, WITH NEGATIVE NUMBERS
REPRESENTED WITH "MINUS" SIGN. FIELDS OF THIS TYPE INCLUDE BUT
ARE NOT LIMITED TO: INTERNAL REVENUE TAX, COTTON FEE, PORK FEE
ETC. AS WITH ALL SUCH CHANGES, THEY ARE TO BE APPLIED
SPECIFICALLY TO HTS NUMBERS VIA INDIVIDUAL SPREADSHEET LINES.

OPTIONAL COMMENT FIELDS MAY BE ADDED AT THE IMPORTER'S DISCRETION
BUT ONLY AT THE FAR RIGHT OF THE SPREADSHEET, AFTER ALL ELEMENTS
LISTED ABOVE.

QUESTIONS REGARDING THIS MESSAGE MAY BE DIRECTED TO DON LUTHER,
CUSTOMS HEADQUARTERS RECONCILIATION TEAM, AT (202) 927-0915,
VIA E-MAIL AT DON.P.LUTHER@CUSTOMS.TREAS.GOV.