The approach addressed here is done using an upload from an excel file to apply directly to your invoices from the upload without sending the data to suspense GL accounts. Your data from Upsilon Payments, most of the time, will have an invoice number, customer number, and will always have payer name and amount. Instead of posting these to suspense GL accounts you can post this data directly against the invoice and customer and automatically match and explain them.
Upsilon Payments can customize a Web Service integration in your SOAMANAGER transaction or a particular ABAP implementation. We would be delighted to work with you on your specific needs. This basic method uses Excel CSV files that Upsilon Payments automatically generates by default and can be made available to the FTP or network share of your choice.
What we recommend for the easiest way using the exported Excel files is an FB01 program for uploading that can be customized to use any transactions. We assume you would be using F.13 to apply the payments.
Post FI Document into R3 from EXCEL - sample excel file - txt version
1: PARAMETERS
2:
3: P_XLFIL = EXCEL File name
4:
5: P_XLSHT = EXCEL Sheet name
6:
7:
8: --------------------------------------------------------------------------------
9:
10:
11: TEXTS
12:
13: TEXT-000 = File Options
14: TEXT-001 = Processing Options
15: TEXT-002 = Post Directly
16: TEXT-003 = Post using BDC
17: TEXT-004 = Test run
18: TEXT-100 = ,*.*,*.XLS,.
19: TEXT-101 = Open EXCEL File
20: TEXT-102 = Opening XL Sheet...
21: TEXT-103 = Reading XL Sheet...
22: TEXT-104 = Header Data
23: TEXT-105 = Detail Data
24: TEXT-106 = Document $BELNR posted in Cocd $BUKRS for year $GJAHR
25: TEXT-107 = Saving XL Sheet...
26:
27:
28: --------------------------------------------------------------------------------
29:
30:
31: PROGRAM
32:
33: REPORT ZFGLXL01 LINE-SIZE 80.
34:
35: INCLUDE OLE2INCL.
36:
37: CONSTANTS:
38: BATCHINPUT(1) TYPE C VALUE 'B',
39: CALLTRANS(1) TYPE C VALUE 'C',
40: MODE(1) TYPE C VALUE 'N',
41: XL_MAX_COL TYPE I VALUE 256,
42: XL_HDR_ROW TYPE I VALUE 8,
43: XL_DTL_ROW TYPE I VALUE 15,
44: XL_RESERVD TYPE I VALUE 2.
45:
46: DATA: BEGIN OF FTPOST OCCURS 100.
47: INCLUDE STRUCTURE FTPOST.
48: DATA: END OF FTPOST.
49:
50: DATA: BEGIN OF FTTAX OCCURS 0.
51: INCLUDE STRUCTURE FTTAX.
52: DATA: END OF FTTAX.
53:
54: DATA: BEGIN OF XBLNTAB OCCURS 2.
55: INCLUDE STRUCTURE BLNTAB.
56: DATA: END OF XBLNTAB.
57:
58: DATA: BEGIN OF HDR_FLDS OCCURS 5,
59: FNAM LIKE FTPOST-FNAM,
60: END OF HDR_FLDS.
61:
62: DATA: BEGIN OF FLDS OCCURS 200,
63: FNAM LIKE FTPOST-FNAM,
64: END OF FLDS.
65:
66: DATA: BEGIN OF OUT_LINE OCCURS 40,
67: COL_HDR(11) TYPE C,
68: END OF OUT_LINE.
69:
70: DATA:
71: XL_APPL TYPE OLE2_OBJECT,
72: XL_SHEET TYPE OLE2_OBJECT,
73: XL_WKBKS TYPE OLE2_OBJECT.
74:
75: DATA:
76: OPEN(1) TYPE C,
77: FLDLEN TYPE I,
78: DOCL(78) TYPE C,
79: FUNCTION LIKE RFIPI-FUNCT,
80: XBDCC LIKE RFIPI-XBDCC.
81:
82: SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-000.
83: PARAMETERS: P_XLFIL LIKE RLGRAP-FILENAME OBLIGATORY,
84: P_XLSHT LIKE RLGRAP-FILENAME OBLIGATORY.
85: SELECTION-SCREEN END OF BLOCK 1.
86:
87: SELECTION-SCREEN BEGIN OF BLOCK 2 WITH FRAME TITLE TEXT-001.
88: SELECTION-SCREEN BEGIN OF LINE.
89: PARAMETERS: P_POST LIKE FEBPDO-XCALL_EBCK RADIOBUTTON GROUP 1.
90: SELECTION-SCREEN
91: COMMENT 03(29) TEXT-002 FOR FIELD P_POST.
92: PARAMETERS: P_MODE LIKE RFPDO-ALLGAZMD NO-DISPLAY.
93: SELECTION-SCREEN: END OF LINE.
94: SELECTION-SCREEN BEGIN OF LINE.
95: PARAMETERS: P_XBDC LIKE FEBPDO-XBINPT RADIOBUTTON GROUP 1.
96: SELECTION-SCREEN
97: COMMENT 03(29) TEXT-003 FOR FIELD P_XBDC.
98: SELECTION-SCREEN: END OF LINE.
99: SELECTION-SCREEN: BEGIN OF LINE.
100: PARAMETERS: P_TEST LIKE RFPDO1-FEBTESTL RADIOBUTTON GROUP 1.
101: SELECTION-SCREEN
102: COMMENT 03(29) TEXT-004 FOR FIELD P_TEST.
103: SELECTION-SCREEN: END OF LINE.
104: PARAMETERS: GROUP LIKE APQI-GROUPID OBLIGATORY.
105: SELECTION-SCREEN END OF BLOCK 2.
106:
107: *-At Selection-Screen--------------------------------------------------------*
108: AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_XLFIL.
109:
110: PERFORM WS_GET_FILENAME.
111:
112: *-Start-Of-Selection---------------------------------------------------------*
113: START-OF-SELECTION.
114:
115: CLEAR OPEN.
116:
117: IF P_POST = 'X'.
118: FUNCTION = CALLTRANS.
119: XBDCC = 'X'.
120: ENDIF.
121:
122: IF P_XBDC = 'X'.
123: FUNCTION = BATCHINPUT.
124: XBDCC = ' '.
125: ENDIF.
126:
127: PERFORM EXCEL_OPEN_WORKBOOK CHANGING XL_APPL XL_WKBKS XL_SHEET.
128: PERFORM EXCEL_READ_SHEET USING XL_SHEET.
129: PERFORM EXCEL_SAVE_SHEET USING XL_APPL XL_WKBKS.
130:
131: FREE OBJECT: XL_APPL, XL_SHEET, XL_WKBKS.
132:
133: *-End-Of-Selection-----------------------------------------------------------*
134: END-OF-SELECTION.
135:
136: IF P_TEST NE 'X'.
137: PERFORM POSTING_INTERFACE_START.
138: PERFORM POSTING_INTERFACE_DOCUMENT.
139: PERFORM POSTING_INTERFACE_CLOSE.
140: DESCRIBE TABLE XBLNTAB LINES FLDLEN.
141: IF ( FUNCTION = CALLTRANS ) AND ( FLDLEN > 0 ).
142: ULINE.
143: FORMAT COLOR COL_NEGATIVE.
144: LOOP AT XBLNTAB.
145: DOCL = TEXT-106.
146: REPLACE '$BELNR' WITH XBLNTAB-BELNR INTO DOCL.
147: REPLACE '$BUKRS' WITH XBLNTAB-BUKRS INTO DOCL.
148: REPLACE '$GJAHR' WITH XBLNTAB-GJAHR INTO DOCL.
149: WRITE: /1(1) SY-VLINE, DOCL,80(10) SY-VLINE.
150: ENDLOOP.
151: ULINE.
152: ENDIF.
153: ENDIF.
154:
155: LOOP AT FTPOST.
156: AT NEW STYPE.
157: FORMAT COLOR COL_HEADING.
158: IF FTPOST-STYPE = 'K'.
159: WRITE: /1(1) SY-VLINE,TEXT-104,80(1) SY-VLINE.
160: ELSE.
161: ULINE.
162: WRITE: /.
163: ULINE.
164: WRITE: /1(1) SY-VLINE,TEXT-105,80(1) SY-VLINE.
165: ENDIF.
166: ULINE.
167: ENDAT.
168:
169: AT NEW COUNT.
170: ULINE.
171: ENDAT.
172:
173: FORMAT COLOR COL_KEY.
174: COMPUTE FLDLEN = STRLEN( FTPOST-FNAM ).
175: WRITE: /1(1) SY-VLINE.
176: WRITE AT (FLDLEN) FTPOST-FNAM.
177: WRITE 20(1) SY-VLINE.
178: FORMAT COLOR COL_NORMAL.
179: COMPUTE FLDLEN = STRLEN( FTPOST-FVAL ).
180: WRITE AT (FLDLEN) FTPOST-FVAL.
181: WRITE 80(1) SY-VLINE.
182: ENDLOOP.
183:
184: ULINE.
185:
186: *&---------------------------------------------------------------------*
187: *& Form EXCEL_OPEN_WORKBOOK
188: *&---------------------------------------------------------------------*
189: * text
190: *----------------------------------------------------------------------*
191: * <--P_XL_APPL text *
192: * <--P_XL_WKBKS text *
193: * <--P_XL_SHEET text *
194: *----------------------------------------------------------------------*
195: FORM EXCEL_OPEN_WORKBOOK CHANGING XLAPP TYPE OLE2_OBJECT
196: XLWKBKS TYPE OLE2_OBJECT
197: XLSHEET TYPE OLE2_OBJECT.
198:
199: CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
200: EXPORTING
201: PERCENTAGE = 0
202: TEXT = TEXT-102.
203:
204: CREATE OBJECT XLAPP 'EXCEL.APPLICATION'.
205: SET PROPERTY OF XLAPP 'Visible' = 0.
206: CALL METHOD OF XLAPP 'Workbooks' = XLWKBKS.
207: CALL METHOD OF XLWKBKS 'Open' EXPORTING #1 = P_XLFIL.
208: CALL METHOD OF XLAPP 'Worksheets' = XLSHEET EXPORTING #1 = P_XLSHT.
209: CALL METHOD OF XLSHEET 'Activate'.
210: FREE OBJECT XLWKBKS.
211: GET PROPERTY OF XLAPP 'ActiveWorkbook' = XLWKBKS.
212:
213: ENDFORM. " EXCEL_OPEN_WORKBOOK
214:
215: *&---------------------------------------------------------------------*
216: *& Form EXCEL_READ_SHEET
217: *&---------------------------------------------------------------------*
218: * text
219: *----------------------------------------------------------------------*
220: * -->P_XL_SHEET text *
221: *----------------------------------------------------------------------*
222: FORM EXCEL_READ_SHEET USING XLSHEET TYPE OLE2_OBJECT.
223:
224: DATA: END_OF_DATA TYPE I,
225: XL_CURR_ROW TYPE I,
226: COL_COUNT TYPE I,
227: INDX TYPE I,
228: FVAL(50) TYPE C,
229: COUNT TYPE I VALUE 1.
230:
231: CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
232: EXPORTING
233: PERCENTAGE = 0
234: TEXT = TEXT-103.
235:
236: CLEAR: END_OF_DATA, COL_COUNT.
237:
238: PERFORM EXCEL_READ_ROW USING XLSHEET XL_DTL_ROW
239: CHANGING COL_COUNT.
240:
241: WHILE END_OF_DATA IS INITIAL.
242: INDX = XL_MAX_COL * ( XL_DTL_ROW + SY-INDEX + 1 ) + 1.
243: DO COL_COUNT TIMES.
244: PERFORM EXCEL_READ_CELL USING XLSHEET INDX
245: CHANGING FVAL.
246: IF SY-INDEX <= XL_RESERVD.
247: CASE SY-INDEX.
248: WHEN 1.
249: IF FVAL IS INITIAL.
250: END_OF_DATA = 1.
251: EXIT.
252: ENDIF.
253: IF FVAL = 1.
254: PERFORM EXCEL_READ_HEADER USING XLSHEET XL_HDR_ROW.
255: ENDIF.
256: WHEN 2.
257: IF NOT ( FVAL IS INITIAL ).
258: EXIT.
259: ENDIF.
260: ENDCASE.
261: ELSE.
262: IF NOT ( FVAL IS INITIAL ).
263: READ TABLE FLDS INDEX SY-INDEX.
264: PERFORM XL_CONVERT CHANGING FVAL.
265: FTPOST-COUNT = COUNT.
266: FTPOST-STYPE = 'P'.
267: MOVE-CORRESPONDING FLDS TO FTPOST.
268: MOVE FVAL TO FTPOST-FVAL.
269: APPEND FTPOST.
270: ENDIF.
271: ENDIF.
272: INDX = INDX + 1.
273: ENDDO.
274: XL_CURR_ROW = XL_CURR_ROW + 1.
275: COUNT = COUNT + 1.
276: ENDWHILE.
277:
278: ENDFORM. " EXCEL_READ_SHEET
279:
280: *&---------------------------------------------------------------------*
281: *& Form EXCEL_SAVE_SHEET
282: *&---------------------------------------------------------------------*
283: * text
284: *----------------------------------------------------------------------*
285: * -->P_XL_APPL text *
286: * -->P_XL_WKBK text *
287: *----------------------------------------------------------------------*
288: FORM EXCEL_SAVE_SHEET USING XLAPP TYPE OLE2_OBJECT
289: XLWKBKS TYPE OLE2_OBJECT.
290:
291: CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
292: EXPORTING
293: PERCENTAGE = 0
294: TEXT = TEXT-107.
295:
296: CALL METHOD OF XLWKBKS 'Save'.
297: CALL METHOD OF XLAPP 'Quit'.
298:
299: ENDFORM. " EXCEL_SAVE_SHEET
300:
301: *&---------------------------------------------------------------------*
302: *& Form WS_GET_FILENAME
303: *&---------------------------------------------------------------------*
304: * text
305: *----------------------------------------------------------------------*
306: * --> p1 text
307: * <-- p2 text
308: *----------------------------------------------------------------------*
309: FORM WS_GET_FILENAME.
310:
311: CALL FUNCTION 'WS_FILENAME_GET'
312: EXPORTING
313: DEF_PATH = 'C:\'
314: MASK = TEXT-100
315: MODE = 'O'
316: TITLE = TEXT-101
317: IMPORTING
318: FILENAME = P_XLFIL
319: EXCEPTIONS
320: INV_WINSYS = 1
321: NO_BATCH = 2
322: SELECTION_CANCEL = 3
323: SELECTION_ERROR = 4
324: OTHERS = 5.
325:
326: IF SY-SUBRC NE 0.
327: CLEAR P_XLFIL.
328: ENDIF.
329:
330: ENDFORM. " WS_GET_FILENAME
331:
332: *&---------------------------------------------------------------------*
333: *& Form EXCEL_READ_HEADER
334: *&---------------------------------------------------------------------*
335: * text
336: *----------------------------------------------------------------------*
337: * -->XLS text *
338: * <--CURR text *
339: * <--COL text *
340: *----------------------------------------------------------------------*
341: FORM EXCEL_READ_HEADER USING XLS TYPE OLE2_OBJECT
342: CURR TYPE I.
343: DATA: CELL TYPE OLE2_OBJECT,
344: INDX TYPE I,
345: FNAM LIKE FTPOST-FNAM,
346: COL LIKE SY-INDEX,
347: FVAL(50) TYPE C.
348:
349: CLEAR COL.
350: CLEAR FTPOST.
351:
352: FTPOST-COUNT = 1. FTPOST-STYPE = 'K'.
353: REFRESH HDR_FLDS.
354: INDX = XL_MAX_COL * ( CURR - 1 ) + 1.
355:
356: DO.
357: CLEAR FNAM.
358: CALL METHOD OF XLS 'Cells' = CELL EXPORTING #1 = INDX.
359: GET PROPERTY OF CELL 'Value' = FNAM.
360: CONDENSE FNAM NO-GAPS.
361: IF NOT ( FNAM IS INITIAL ).
362: MOVE FNAM TO HDR_FLDS-FNAM.
363: APPEND HDR_FLDS.
364: INDX = INDX + 1.
365: COL = COL + 1.
366: ELSE.
367: EXIT.
368: ENDIF.
369: ENDDO.
370:
371: INDX = XL_MAX_COL * ( XL_HDR_ROW + 2 ) + 1.
372:
373: DO COL TIMES.
374: PERFORM EXCEL_READ_CELL USING XLS INDX
375: CHANGING FVAL.
376: IF NOT ( FVAL IS INITIAL ).
377: READ TABLE HDR_FLDS INDEX SY-INDEX.
378: MOVE HDR_FLDS-FNAM TO FTPOST-FNAM.
379: MOVE FVAL TO FTPOST-FVAL.
380: APPEND FTPOST.
381: ENDIF.
382: INDX = INDX + 1.
383: ENDDO.
384:
385: FREE: CELL.
386:
387: ENDFORM. " EXCEL_READ_ROW
388:
389: *&---------------------------------------------------------------------*
390: *& Form EXCEL_READ_CELL
391: *&---------------------------------------------------------------------*
392: * text
393: *----------------------------------------------------------------------*
394: * -->P_XLSHEET text *
395: * -->P_INDX text *
396: * <--P_FVAL text *
397: *----------------------------------------------------------------------*
398: FORM EXCEL_READ_CELL USING XLS TYPE OLE2_OBJECT
399: INDX TYPE I
400: CHANGING FVAL.
401:
402: DATA: CELL TYPE OLE2_OBJECT.
403:
404: CALL METHOD OF XLS 'Cells' = CELL EXPORTING #1 = INDX.
405: GET PROPERTY OF CELL 'Value' = FVAL.
406:
407: ENDFORM. " EXCEL_READ_CELL
408:
409: *&---------------------------------------------------------------------*
410: *& Form EXCEL_READ_ROW
411: *&---------------------------------------------------------------------*
412: * text
413: *----------------------------------------------------------------------*
414: * -->P_XLSHEET text *
415: * -->P_XL_DTL_ROW text *
416: * <--P_COL_COUNT text *
417: *----------------------------------------------------------------------*
418: FORM EXCEL_READ_ROW USING XLS TYPE OLE2_OBJECT
419: CURR TYPE I
420: CHANGING COL TYPE I.
421:
422: DATA: CELL TYPE OLE2_OBJECT,
423: INDX TYPE I,
424: FNAM LIKE FTPOST-FNAM.
425:
426: REFRESH FLDS.
427: INDX = XL_MAX_COL * ( CURR - 1 ) + 1.
428:
429: DO.
430: CLEAR FNAM.
431: CALL METHOD OF XLS 'Cells' = CELL EXPORTING #1 = INDX.
432: GET PROPERTY OF CELL 'Value' = FNAM.
433: CONDENSE FNAM NO-GAPS.
434: IF NOT ( FNAM IS INITIAL ).
435: IF SY-INDEX > XL_RESERVD.
436: MOVE FNAM TO FLDS-FNAM.
437: ELSE.
438: MOVE 'RESERVED' TO FLDS-FNAM.
439: ENDIF.
440: APPEND FLDS.
441: INDX = INDX + 1.
442: COL = COL + 1.
443: ELSE.
444: EXIT.
445: ENDIF.
446: ENDDO.
447:
448: FREE: CELL.
449:
450: ENDFORM. " EXCEL_READ_ROW
451:
452: *&---------------------------------------------------------------------*
453: *& Form XL_CONVERT
454: *&---------------------------------------------------------------------*
455: * text
456: *----------------------------------------------------------------------*
457: * <--P_FVAL text *
458: *----------------------------------------------------------------------*
459: FORM XL_CONVERT CHANGING VAL.
460:
461: IF VAL CS '.'.
462: WRITE VAL TO VAL RIGHT-JUSTIFIED.
463: SHIFT VAL RIGHT DELETING TRAILING '0'.
464: SHIFT VAL RIGHT DELETING TRAILING '.'.
465: CONDENSE VAL NO-GAPS.
466: ENDIF.
467:
468: ENDFORM. " XL_CONVERT
469:
470: *&---------------------------------------------------------------------*
471: *& Form POSTING_INTERFACE_START
472: *&---------------------------------------------------------------------*
473: * text
474: *----------------------------------------------------------------------*
475: * --> p1 text
476: * <-- p2 text
477: *----------------------------------------------------------------------*
478: FORM POSTING_INTERFACE_START.
479:
480: DATA: GROUPNAME LIKE APQI-GROUPID.
481:
482: IF FUNCTION = CALLTRANS.
483: GROUPNAME = GROUP.
484: GROUPNAME+8(4) = '-ERR'.
485: CONDENSE GROUPNAME NO-GAPS.
486: ELSE.
487: GROUPNAME = GROUP.
488: GROUPNAME+8(3) = '-OK'.
489: CONDENSE GROUPNAME NO-GAPS.
490: ENDIF.
491:
492: CALL FUNCTION 'POSTING_INTERFACE_START'
493: EXPORTING
494: I_FUNCTION = FUNCTION
495: I_MODE = MODE
496: I_GROUP = GROUPNAME
497: I_USER = SY-UNAME
498: I_XBDCC = XBDCC.
499:
500: ENDFORM. " POSTING_INTERFACE_START
501:
502: *&---------------------------------------------------------------------*
503: *& Form POSTING_INTERFACE_DOCUMENT
504: *&---------------------------------------------------------------------*
505: * text
506: *----------------------------------------------------------------------*
507: * --> p1 text
508: * <-- p2 text
509: *----------------------------------------------------------------------*
510: FORM POSTING_INTERFACE_DOCUMENT.
511:
512: DATA: SUBRC LIKE SY-SUBRC,
513: MSGID LIKE SY-MSGID,
514: MSGTY LIKE SY-MSGTY,
515: MSGNO LIKE SY-MSGNO,
516: MSGV1 LIKE SY-MSGV1,
517: MSGV2 LIKE SY-MSGV2,
518: MSGV3 LIKE SY-MSGV3,
519: MSGV4 LIKE SY-MSGV4.
520:
521: CALL FUNCTION 'POSTING_INTERFACE_DOCUMENT'
522: EXPORTING
523: I_TCODE = 'FB01'
524: IMPORTING
525: E_SUBRC = SUBRC
526: E_MSGID = MSGID
527: E_MSGTY = MSGTY
528: E_MSGNO = MSGNO
529: E_MSGV1 = MSGV1
530: E_MSGV2 = MSGV2
531: E_MSGV3 = MSGV3
532: E_MSGV4 = MSGV4
533: TABLES
534: T_FTPOST = FTPOST
535: T_FTTAX = FTTAX
536: T_BLNTAB = XBLNTAB.
537:
538: ENDFORM. " POSTING_INTERFACE_DOCUMENT
539:
540: *&---------------------------------------------------------------------*
541: *& Form POSTING_INTERFACE_CLOSE
542: *&---------------------------------------------------------------------*
543: * text
544: *----------------------------------------------------------------------*
545: * --> p1 text
546: * <-- p2 text
547: *----------------------------------------------------------------------*
548: FORM POSTING_INTERFACE_CLOSE.
549:
550: CALL FUNCTION 'POSTING_INTERFACE_END'.
551:
552: ENDFORM. " POSTING_INTERFACE_CLOSE