Home  Sample Code ABAP Reports GL Account creation via file upload
|
|
|
|
GL Account creation via file upload |
|
|
|
|
Written by Anon.
|
|
Tuesday, 06 March 2007 |
Program reads an excel spreadsheet file and, per record, creates a GL Account.
REPORT ZFGLUPLD.
***********************************************************************
* Program reads an excel spreadsheet file and, per record, create
* a GL Account.
***********************************************************************
TABLES: T001, "Company code
T077S, "Account groups
TZUN, "Sort keys
T004F, "Field status groups
SKB1, "GL Account line
SKA1. "GL Account header
DATA: BEGIN OF TAB OCCURS 2000,
SAKNR LIKE SKA1-SAKNR, "GL Account number
XBILK LIKE SKA1-XBILK, "Ind for B/S account
GVTYP LIKE SKA1-GVTYP, "Ind for P&L account
KTOKS LIKE SKA1-KTOKS, "Account group
* xspeb like ska1-xspeb, "Block indicator
TXT20 LIKE SKAT-TXT20, "Short text
TXT50 LIKE SKAT-TXT50, "Long text
FSTAG LIKE SKB1-FSTAG, "Field status group
MITKZ LIKE SKB1-MITKZ, "Recon.indicator
MWSKZ LIKE SKB1-MWSKZ, "Tax category
WAERS LIKE SKB1-WAERS, "Currency
XINTB LIKE SKB1-XINTB, "Aut.posted
XKRES LIKE SKB1-XKRES, "Line item display
XOPVW LIKE SKB1-XOPVW, "Open item managed
ZUAWA LIKE SKB1-ZUAWA, "Sort key
FIPOS LIKE SKB1-FIPOS, "Commitment item
XMWNO LIKE SKB1-XMWNO, "Tax not required
XSALH LIKE SKB1-XSALH, "Only managed in local bal.
CODE LIKE SKB1-BUKRS, "Company code
FDLEV LIKE SKB1-FDLEV, "Planning level
HBKID LIKE SKB1-HBKID, "House bank
HKTID LIKE SKB1-HKTID, "Account ID
END OF TAB.
DATA: BEGIN OF BDCTAB OCCURS 100.
INCLUDE STRUCTURE BDCDATA.
DATA: END OF BDCTAB.
DATA: ERROR(1) TYPE C VALUE 'N', "Error flag
CNT(4) TYPE N, "Counter
VAR_NAC(10) TYPE N, "Account to numeric format
VAR(2) TYPE N, "Length variable
FLAG(1) TYPE C,
NUM(10) TYPE N.
PARAMETERS: FILE LIKE RLGRAP-FILENAME,
F_TYP LIKE RLGRAP-FILETYPE DEFAULT 'ASC',
COA LIKE T004-KTOPL DEFAULT 'CAEK',
C_CODE LIKE T001-BUKRS DEFAULT '1000',
IND(1) TYPE C DEFAULT 'N'.
** Upload file into internal table tab
CALL FUNCTION 'WS_UPLOAD'
EXPORTING
FILENAME = FILE
FILETYPE = F_TYP
TABLES
DATA_TAB = TAB.
** Validation on input fields (in internal table tab) before starting to
** process the BTCI records
PERFORM VALIDATION.
** Only continue if no validation error occurred
IF ERROR = 'N'.
** Open BTCI session for transaction FS01
CALL FUNCTION 'BDC_OPEN_GROUP'
EXPORTING
CLIENT = SY-MANDT
GROUP = 'GL_MAST'
USER = SY-UNAME
KEEP = 'X'.
** For every record in the internal table TAB, check if the account
** already exist in the SAP tables SKA1 and SKB1 (for the company code).
** If not existing, call form fill_fs01 to populate the screens to
** create the account.
LOOP AT TAB.
SKB1 = SPACE.
VAR_NAC = TAB-SAKNR. "Write account no. to numeric field
FLAG = 'Y'.
IF TAB-CODE NE SPACE AND
TAB-CODE NE C_CODE.
FLAG = 'N'.
ENDIF.
IF FLAG = 'Y'.
IND = 'Y'.
NUM = TAB-SAKNR.
TAB-SAKNR = NUM.
SELECT SINGLE * FROM SKA1 WHERE KTOPL = COA AND
SAKNR = TAB-SAKNR.
IF SY-SUBRC NE 0.
IND = 'N'.
ENDIF.
SELECT SINGLE * FROM SKB1 WHERE BUKRS = C_CODE AND
SAKNR = VAR_NAC.
IF SY-SUBRC NE 0.
PERFORM FILL_FS01.
ADD 1 TO CNT.
ELSE.
WRITE: / TAB-SAKNR.
ENDIF.
ENDIF.
ENDLOOP.
WRITE: / CNT,'G/L accounts created for company'.
WRITE: C_CODE.
** Close BTCI session
CALL FUNCTION 'BDC_CLOSE_GROUP'.
ENDIF.
*---------------------------------------------------------------------*
* FORM VALIDATION *
*---------------------------------------------------------------------*
* Validate the input records in the internal table TAB *
* - GL Account length *
* - Company code *
* - Account type (B/S or P&L) *
* - Account group *
* - Tax category *
* - Recon. indicator *
* - Sort key *
* - Field status group *
*---------------------------------------------------------------------*
FORM VALIDATION.
LOOP AT TAB.
VAR = STRLEN( TAB-SAKNR ).
IF VAR NE 6.
WRITE: / 'Incorrect account length: ',TAB-SAKNR.
ERROR = 'Y'.
ENDIF.
IF C_CODE NE SPACE.
T001 = SPACE.
SELECT SINGLE * FROM T001 WHERE BUKRS = C_CODE.
IF SY-SUBRC NE 0.
WRITE: / 'Incorrect company code: ',C_CODE.
ERROR = 'Y'.
ENDIF.
ENDIF.
IF TAB-XBILK EQ SPACE AND TAB-GVTYP EQ SPACE.
WRITE: / 'Account must be either B/S or I/S: ',TAB-SAKNR.
ERROR = 'Y'.
ENDIF.
TRANSLATE TAB-KTOKS TO UPPER CASE.
T077S = SPACE.
SELECT SINGLE * FROM T077S WHERE KTOPL = COA AND
KTOKS = TAB-KTOKS.
IF SY-SUBRC NE 0.
WRITE: / 'Incorrect account group: ',TAB-SAKNR, TAB-KTOKS.
ERROR = 'Y'.
ENDIF.
IF TAB-MWSKZ NE '-' AND
TAB-MWSKZ NE '+' AND
TAB-MWSKZ NE '*' AND
TAB-MWSKZ NE '<' AND
TAB-MWSKZ NE '>' AND
TAB-MWSKZ NE '-B' AND
TAB-MWSKZ NE '+B' AND
TAB-MWSKZ NE 'A0' AND
TAB-MWSKZ NE 'A1' AND
TAB-MWSKZ NE 'V0' AND
TAB-MWSKZ NE 'V9' AND
TAB-MWSKZ NE SPACE AND
TAB-MWSKZ NE 'V1'.
WRITE: / 'Incorrect tax indicator: ',TAB-MWSKZ.
ERROR = 'Y'.
ENDIF.
TRANSLATE TAB-MITKZ TO UPPER CASE.
IF TAB-MITKZ NE 'A' AND
TAB-MITKZ NE 'D' AND
TAB-MITKZ NE 'K' AND
TAB-MITKZ NE SPACE.
WRITE: / 'Incorrect reconciliation indicator: ',TAB-MITKZ.
ERROR = 'Y'.
ENDIF.
IF TAB-ZUAWA NE SPACE.
TZUN = SPACE.
SELECT SINGLE * FROM TZUN WHERE ZUAWA = TAB-ZUAWA.
IF SY-SUBRC NE 0.
WRITE: / 'Incorrect sort category: ',TAB-ZUAWA.
ERROR = 'Y'.
ENDIF.
ENDIF.
T004F = SPACE.
SELECT SINGLE * FROM T004F WHERE BUKRS = '0001' AND
FSTAG = TAB-FSTAG.
IF SY-SUBRC NE 0.
WRITE: / 'Incorrect field status: ',TAB-FSTAG.
ERROR = 'Y'.
ENDIF.
ENDLOOP.
ENDFORM.
*---------------------------------------------------------------------*
* FORM FILL_FS01 *
*---------------------------------------------------------------------*
* Populate relevant screens for transaction FS01 *
*---------------------------------------------------------------------*
FORM FILL_FS01.
CLEAR BDCTAB.
REFRESH BDCTAB.
BDCTAB-PROGRAM = 'SAPMF02H'.
BDCTAB-DYNPRO = '0402'.
BDCTAB-DYNBEGIN = 'X'.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'RF02H-SAKNR'. "Account number
BDCTAB-FVAL = TAB-SAKNR.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'RF02H-BUKRS'. "Company code
* if tab-cco eq space.
BDCTAB-FVAL = C_CODE.
* else.
* bdctab-fval = tab-cco.
* endif.
APPEND BDCTAB.
IF IND = 'N'.
CLEAR BDCTAB.
BDCTAB-PROGRAM = 'SAPMF02H'.
BDCTAB-DYNPRO = '0310'.
BDCTAB-DYNBEGIN = 'X'.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKAT-TXT20'. "Short text
BDCTAB-FVAL = TAB-TXT20.
* bdctab-fval = tab-ltx(20).
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKAT-TXT50'. "Long text
BDCTAB-FVAL = TAB-TXT50.
APPEND BDCTAB.
IF TAB-XBILK NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKA1-XBILK'. "B/S indicator
BDCTAB-FVAL = 'X'.
APPEND BDCTAB.
ENDIF.
IF TAB-GVTYP NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKA1-GVTYP'. "P&L indicator
BDCTAB-FVAL = 'X'.
APPEND BDCTAB.
ENDIF.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKA1-KTOKS'. "Account group
BDCTAB-FVAL = TAB-KTOKS.
* bdctab-fval = 'PL'.
APPEND BDCTAB.
ENDIF.
CLEAR BDCTAB.
BDCTAB-PROGRAM = 'SAPMF02H'.
BDCTAB-DYNPRO = '0110'.
BDCTAB-DYNBEGIN = 'X'.
APPEND BDCTAB.
IF TAB-WAERS NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-WAERS'. "Currency
IF TAB-WAERS EQ SPACE.
BDCTAB-FVAL = 'ZAR'.
ELSE.
BDCTAB-FVAL = TAB-WAERS.
ENDIF.
APPEND BDCTAB.
ENDIF.
IF TAB-XSALH NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-XSALH'. "Managed in local currency
BDCTAB-FVAL = TAB-XSALH.
APPEND BDCTAB.
ENDIF.
IF TAB-MWSKZ NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-MWSKZ'. "Tax category
BDCTAB-FVAL = TAB-MWSKZ.
APPEND BDCTAB.
ENDIF.
IF TAB-XMWNO NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-XMWNO'. "Tax not required
BDCTAB-FVAL = TAB-XMWNO.
APPEND BDCTAB.
ENDIF.
IF TAB-MITKZ NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-MITKZ'. "Recon. ind
BDCTAB-FVAL = TAB-MITKZ.
APPEND BDCTAB.
ENDIF.
TRANSLATE TAB-KTOKS TO UPPER CASE.
IF TAB-XOPVW NE SPACE.
IF TAB-KTOKS NE 'AS'.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-XOPVW'. "Open item
BDCTAB-FVAL = 'X'.
APPEND BDCTAB.
ENDIF.
ENDIF.
IF TAB-XKRES NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-XKRES'. "Line item display
BDCTAB-FVAL = 'X'.
APPEND BDCTAB.
ENDIF.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-ZUAWA'. "Sort key
IF TAB-ZUAWA EQ SPACE.
BDCTAB-FVAL = '001'.
ELSE.
BDCTAB-FVAL = TAB-ZUAWA.
ENDIF.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-FSTAG'. "Field status group
BDCTAB-FVAL = TAB-FSTAG.
APPEND BDCTAB.
IF TAB-XINTB NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-XINTB'. "Automatic posted
BDCTAB-FVAL = 'X'.
APPEND BDCTAB.
ENDIF.
TRANSLATE TAB-KTOKS TO UPPER CASE.
IF TAB-KTOKS EQ 'CASH'.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-FDLEV'. "Planning level
BDCTAB-FVAL = TAB-FDLEV.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-XGKON'. "Rel. for cash flow
BDCTAB-FVAL = 'X'.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-HBKID'. "House bank
BDCTAB-FVAL = TAB-HBKID.
APPEND BDCTAB.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-HKTID'. "Account ID
BDCTAB-FVAL = TAB-HKTID.
APPEND BDCTAB.
ENDIF.
IF TAB-FIPOS NE SPACE.
CLEAR BDCTAB.
BDCTAB-FNAM = 'SKB1-FIPOS'. "Commitment item
BDCTAB-FVAL = TAB-FIPOS.
APPEND BDCTAB.
ENDIF.
CLEAR BDCTAB.
BDCTAB-FNAM = 'BDC_OKCODE'.
BDCTAB-FVAL = '/11'.
APPEND BDCTAB.
CALL FUNCTION 'BDC_INSERT'
EXPORTING
TCODE = 'FS01'
TABLES
DYNPROTAB = BDCTAB.
ENDFORM.
Related Items:
|
|
|
|
