Instructions
1
Log on to your computer and open the Microsoft Access database you want to use. Click on the "Queries" section of
the database.
2
Double-click "Create query in design view" and choose the table on which the query will be based. Double-click each
field you want to display in the query.
3
Place the cursor in the first blank column and type "TextField: Cstr([NumberField])" in the "Field" box. Replace
"TextField" with a descriptive name and "NumberField" with the name of the numeric field. For instance, if the
numeric field is called "Cost," the command would be "CostText: Cstr([Cost])"
4
Click the "Save" button on the toolbar to save the query. Give the query a descriptive name. Double-click the new
query to run it.
http://www.ehow.com/how_6199928_convert-number-text-access-query.html
Montag, 7. Mai 2012
Dienstag, 3. April 2012
Sonntag, 19. Februar 2012
EC-CS: Mapping Cost center to Sub Items
Cost center, along with a selection of other FI "dimensions", can be assigned directly to Subitem categroy in EC-CS master data.
ABAP - An other great one - Reset Trading Partner in FI
*&---------------------------------------------------------------------*
*& Report Y_ECCS_DOC_RESET *
*& *
*&---------------------------------------------------------------------*
*& *
*& *
*&---------------------------------------------------------------------*
REPORT y_eccs_trading_partner_set LINE-SIZE 130.
TABLES: bsis, bsas, bsid, bsad, bsik, bsak.
TABLES: ska1, kna1, lfa1.
TABLES: bseg, bkpf, glt3.
DATA: i_bseg TYPE bseg OCCURS 0 WITH HEADER LINE,
i_bsis TYPE bsis OCCURS 0 WITH HEADER LINE,
i_bsas TYPE bsas OCCURS 0 WITH HEADER LINE,
i_bsid TYPE bsid OCCURS 0 WITH HEADER LINE,
i_bsad TYPE bsad OCCURS 0 WITH HEADER LINE,
i_bsik TYPE bsik OCCURS 0 WITH HEADER LINE,
i_bsak TYPE bsak OCCURS 0 WITH HEADER LINE,
i_glt3 TYPE glt3 OCCURS 0 WITH HEADER LINE.
PARAMETERS: p_bukrs LIKE bsis-bukrs.
SELECT-OPTIONS: s_belnr FOR bsis-belnr NO INTERVALS.
PARAMETERS: p_gjahr LIKE bsis-gjahr.
SELECTION-SCREEN SKIP.
PARAMETERS: p_ovbund LIKE bseg-vbund,
p_nvbund LIKE bseg-vbund.
SELECTION-SCREEN SKIP.
PARAMETERS: p_test AS CHECKBOX DEFAULT 'X',
p_log AS CHECKBOX DEFAULT 'X'.
INITIALIZATION.
AT SELECTION-SCREEN ON s_belnr.
LOOP AT s_belnr.
IF s_belnr-option NE 'EQ' OR
s_belnr-sign NE 'I'.
MESSAGE e001(z3) WITH 'Invalid selection of document numbers'.
ENDIF.
ENDLOOP.
START-OF-SELECTION.
IF sy-uname NE 'JOHN' AND
sy-uname NE 'JIM' AND
sy-uname NE 'JAMES'.
IF p_test IS INITIAL.
p_test = 'X'.
MESSAGE i001(z3) WITH 'You are not authorized to do this!'.
ENDIF.
ENDIF.
SORT s_belnr.
DELETE ADJACENT DUPLICATES FROM s_belnr.
PERFORM run_gl.
PERFORM log.
END-OF-SELECTION.
*&--------------------------------------------------------------------*
*& Form RUN_GL
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM run_gl.
DATA: l_lines TYPE i,
l_belnr LIKE bseg-belnr.
LOOP AT s_belnr.
l_belnr = s_belnr-low.
SELECT * FROM bseg WHERE bukrs EQ p_bukrs
AND gjahr EQ p_gjahr
AND belnr EQ l_belnr.
* Check old TP value
CHECK bseg-vbund = p_ovbund.
CASE bseg-koart.
WHEN 'S'.
* G/L account
i_bseg = bseg.
APPEND i_bseg.
* for update of underlying documents
IF bseg-augbl IS INITIAL.
SELECT SINGLE * FROM bsis WHERE bukrs EQ bseg-bukrs
AND hkont EQ bseg-hkont
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsis = bsis.
i_bsis-vbund = p_nvbund.
APPEND i_bsis.
ELSE.
SELECT SINGLE * FROM bsas WHERE bukrs EQ bseg-bukrs
AND hkont EQ bseg-hkont
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsas = bsas.
i_bsas-vbund = p_nvbund.
APPEND i_bsas.
ENDIF.
WHEN 'D'.
* Customer
i_bseg = bseg.
APPEND i_bseg.
* for update of underlying documents
IF bseg-augbl IS INITIAL.
SELECT SINGLE * FROM bsid WHERE bukrs EQ bseg-bukrs
AND kunnr EQ bseg-kunnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsid = bsid.
i_bsid-vbund = p_nvbund.
APPEND i_bsid.
ELSE.
SELECT SINGLE * FROM bsad WHERE bukrs EQ bseg-bukrs
AND kunnr EQ bseg-kunnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsad = bsad.
i_bsad-vbund = p_nvbund.
APPEND i_bsad.
ENDIF.
WHEN 'K'.
* Vendor
i_bseg = bseg.
APPEND i_bseg.
* for update of underlying documents
IF bseg-augbl IS INITIAL.
SELECT SINGLE * FROM bsik WHERE bukrs EQ bseg-bukrs
AND lifnr EQ bseg-lifnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsik = bsik.
i_bsik-vbund = p_nvbund.
APPEND i_bsik.
ELSE.
SELECT SINGLE * FROM bsak WHERE bukrs EQ bseg-bukrs
AND lifnr EQ bseg-lifnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsak = bsak.
i_bsak-vbund = p_nvbund.
APPEND i_bsak.
ENDIF.
WHEN OTHERS.
MESSAGE e001(z3) WITH 'Unexpected line item type in'
bseg-bukrs bseg-belnr.
ENDCASE.
ENDSELECT.
ENDLOOP.
DESCRIBE TABLE i_bseg LINES l_lines.
WRITE : / 'Selected line items :', l_lines.
PERFORM update_glt3.
* updates
CHECK p_test IS INITIAL.
DESCRIBE TABLE i_bseg LINES l_lines.
WRITE : / 'Update of records BSEG:', l_lines.
IF l_lines NE 0.
UPDATE bseg FROM TABLE i_bseg.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSEG update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsis LINES l_lines.
WRITE : / 'Update of records BSIS:', l_lines.
IF l_lines NE 0.
UPDATE bsis FROM TABLE i_bsis.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSIS update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsas LINES l_lines.
WRITE : / 'Update of records BSAS:', l_lines.
IF l_lines NE 0.
UPDATE bsas FROM TABLE i_bsas.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSAS update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsid LINES l_lines.
WRITE : / 'Update of records BSID:', l_lines.
IF l_lines NE 0.
UPDATE bsid FROM TABLE i_bsid.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSID update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsad LINES l_lines.
WRITE : / 'Update of records BSAD:', l_lines.
IF l_lines NE 0.
UPDATE bsad FROM TABLE i_bsad.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSAD update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsik LINES l_lines.
WRITE : / 'Update of records BSIK:', l_lines.
IF l_lines NE 0.
UPDATE bsik FROM TABLE i_bsik.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSIK update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsak LINES l_lines.
WRITE : / 'Update of records BSAK:', l_lines.
IF l_lines NE 0.
UPDATE bsak FROM TABLE i_bsak.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSAK update error'.
ENDIF.
ENDIF.
ENDFORM. "RUN_GL
*&--------------------------------------------------------------------*
*& Form UPDATE_GLT3
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM update_glt3 .
DATA: l_lines TYPE i,
l_found,
l_fieldname(12) TYPE c.
FIELD-SYMBOLS <hsl>.
FIELD-SYMBOLS <tsl>.
FIELD-SYMBOLS <ksl>.
REFRESH i_glt3.
LOOP AT i_bseg.
SELECT SINGLE * FROM bkpf WHERE bukrs EQ i_bseg-bukrs
AND gjahr EQ i_bseg-gjahr
AND belnr EQ i_bseg-belnr.
UNASSIGN <hsl>.
UNASSIGN <tsl>.
UNASSIGN <ksl>.
CONCATENATE 'I_GLT3-HSL' bkpf-monat INTO l_fieldname.
ASSIGN (l_fieldname) TO <hsl>.
CONCATENATE 'I_GLT3-TSL' bkpf-monat INTO l_fieldname.
ASSIGN (l_fieldname) TO <tsl>.
CONCATENATE 'I_GLT3-KSL' bkpf-monat INTO l_fieldname.
ASSIGN (l_fieldname) TO <ksl>.
* for the old VBUND value
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
PERFORM create_glt3.
ENDIF.
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
IF i_bseg-shkzg EQ 'S'.
SUBTRACT i_bseg-wrbtr FROM <tsl>.
SUBTRACT i_bseg-dmbtr FROM <hsl>.
SUBTRACT i_bseg-dmbtr FROM <ksl>.
ELSE.
ADD i_bseg-wrbtr TO <tsl>.
ADD i_bseg-dmbtr TO <hsl>.
ADD i_bseg-dmbtr TO <ksl>.
ENDIF.
MODIFY i_glt3.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
MESSAGE e001(z3) WITH 'Still no GLT3 ?!'.
ENDIF.
* for the new VBUND value (initial)
i_bseg-vbund = p_nvbund.
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
PERFORM create_glt3.
ENDIF.
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
IF i_bseg-shkzg EQ 'S'.
ADD i_bseg-wrbtr TO <tsl>.
ADD i_bseg-dmbtr TO <hsl>.
ADD i_bseg-dmbtr TO <ksl>.
ELSE.
SUBTRACT i_bseg-wrbtr FROM <tsl>.
SUBTRACT i_bseg-dmbtr FROM <hsl>.
SUBTRACT i_bseg-dmbtr FROM <ksl>.
ENDIF.
MODIFY i_glt3.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
MESSAGE e001(z3) WITH 'Still no GLT3 ?!'.
ENDIF.
MODIFY i_bseg.
ENDLOOP.
CHECK p_test IS INITIAL.
DESCRIBE TABLE i_glt3 LINES l_lines.
WRITE : / 'Update of records GLT3:', l_lines.
IF l_lines NE 0.
MODIFY glt3 FROM TABLE i_glt3.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'GLT3 update error'.
ENDIF.
ENDIF.
ENDFORM. "UPDATE_GLT3
*&--------------------------------------------------------------------*
*& Form LOG
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM log.
CHECK p_log IS NOT INITIAL.
LOOP AT i_bseg WHERE shkzg EQ 'S'.
WRITE: / i_bseg-bukrs,
i_bseg-belnr,
i_bseg-gjahr,
i_bseg-buzei,
i_bseg-hkont,
i_bseg-vbund,
i_bseg-gsber,
i_bseg-shkzg,
i_bseg-dmbtr.
ENDLOOP.
LOOP AT i_glt3 WHERE drcrk EQ 'S'.
WRITE: / i_glt3-bukrs,
i_glt3-racct,
i_glt3-rbusa,
i_glt3-rtcur,
i_glt3-rassc,
i_glt3-drcrk.
ENDLOOP.
LOOP AT i_bseg WHERE shkzg EQ 'H'.
WRITE: / i_bseg-bukrs,
i_bseg-belnr,
i_bseg-gjahr,
i_bseg-buzei,
i_bseg-hkont,
i_bseg-vbund,
i_bseg-gsber,
i_bseg-shkzg,
i_bseg-dmbtr.
ENDLOOP.
LOOP AT i_glt3 WHERE drcrk EQ 'H'.
WRITE: / i_glt3-bukrs,
i_glt3-racct,
i_glt3-rbusa,
i_glt3-rtcur,
i_glt3-rassc,
i_glt3-drcrk.
ENDLOOP.
ENDFORM. "LOG
*&--------------------------------------------------------------------*
*& Form CREATE_GLT3
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM create_glt3.
FIELD-SYMBOLS: <feld>.
SELECT SINGLE * FROM glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
IF sy-subrc EQ 0.
i_glt3 = glt3.
APPEND i_glt3.
EXIT.
ENDIF.
SELECT SINGLE * FROM glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'GLT3 not found for:'
i_bseg-bukrs
i_bseg-hkont
i_bseg-gjahr.
ENDIF.
i_glt3 = glt3.
i_glt3-rbusa = i_bseg-gsber.
i_glt3-rassc = i_bseg-vbund.
i_glt3-rtcur = bkpf-waers.
i_glt3-drcrk = i_bseg-shkzg.
* clear amount fields
DO.
CHECK sy-index GT 16.
ASSIGN COMPONENT sy-index OF STRUCTURE i_glt3 TO <feld>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
CLEAR <feld>.
ENDDO.
APPEND i_glt3.
EXIT.
ENDFORM. "CREATE_GLT3
*& Report Y_ECCS_DOC_RESET *
*& *
*&---------------------------------------------------------------------*
*& *
*& *
*&---------------------------------------------------------------------*
REPORT y_eccs_trading_partner_set LINE-SIZE 130.
TABLES: bsis, bsas, bsid, bsad, bsik, bsak.
TABLES: ska1, kna1, lfa1.
TABLES: bseg, bkpf, glt3.
DATA: i_bseg TYPE bseg OCCURS 0 WITH HEADER LINE,
i_bsis TYPE bsis OCCURS 0 WITH HEADER LINE,
i_bsas TYPE bsas OCCURS 0 WITH HEADER LINE,
i_bsid TYPE bsid OCCURS 0 WITH HEADER LINE,
i_bsad TYPE bsad OCCURS 0 WITH HEADER LINE,
i_bsik TYPE bsik OCCURS 0 WITH HEADER LINE,
i_bsak TYPE bsak OCCURS 0 WITH HEADER LINE,
i_glt3 TYPE glt3 OCCURS 0 WITH HEADER LINE.
PARAMETERS: p_bukrs LIKE bsis-bukrs.
SELECT-OPTIONS: s_belnr FOR bsis-belnr NO INTERVALS.
PARAMETERS: p_gjahr LIKE bsis-gjahr.
SELECTION-SCREEN SKIP.
PARAMETERS: p_ovbund LIKE bseg-vbund,
p_nvbund LIKE bseg-vbund.
SELECTION-SCREEN SKIP.
PARAMETERS: p_test AS CHECKBOX DEFAULT 'X',
p_log AS CHECKBOX DEFAULT 'X'.
INITIALIZATION.
AT SELECTION-SCREEN ON s_belnr.
LOOP AT s_belnr.
IF s_belnr-option NE 'EQ' OR
s_belnr-sign NE 'I'.
MESSAGE e001(z3) WITH 'Invalid selection of document numbers'.
ENDIF.
ENDLOOP.
START-OF-SELECTION.
IF sy-uname NE 'JOHN' AND
sy-uname NE 'JIM' AND
sy-uname NE 'JAMES'.
IF p_test IS INITIAL.
p_test = 'X'.
MESSAGE i001(z3) WITH 'You are not authorized to do this!'.
ENDIF.
ENDIF.
SORT s_belnr.
DELETE ADJACENT DUPLICATES FROM s_belnr.
PERFORM run_gl.
PERFORM log.
END-OF-SELECTION.
*&--------------------------------------------------------------------*
*& Form RUN_GL
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM run_gl.
DATA: l_lines TYPE i,
l_belnr LIKE bseg-belnr.
LOOP AT s_belnr.
l_belnr = s_belnr-low.
SELECT * FROM bseg WHERE bukrs EQ p_bukrs
AND gjahr EQ p_gjahr
AND belnr EQ l_belnr.
* Check old TP value
CHECK bseg-vbund = p_ovbund.
CASE bseg-koart.
WHEN 'S'.
* G/L account
i_bseg = bseg.
APPEND i_bseg.
* for update of underlying documents
IF bseg-augbl IS INITIAL.
SELECT SINGLE * FROM bsis WHERE bukrs EQ bseg-bukrs
AND hkont EQ bseg-hkont
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsis = bsis.
i_bsis-vbund = p_nvbund.
APPEND i_bsis.
ELSE.
SELECT SINGLE * FROM bsas WHERE bukrs EQ bseg-bukrs
AND hkont EQ bseg-hkont
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsas = bsas.
i_bsas-vbund = p_nvbund.
APPEND i_bsas.
ENDIF.
WHEN 'D'.
* Customer
i_bseg = bseg.
APPEND i_bseg.
* for update of underlying documents
IF bseg-augbl IS INITIAL.
SELECT SINGLE * FROM bsid WHERE bukrs EQ bseg-bukrs
AND kunnr EQ bseg-kunnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsid = bsid.
i_bsid-vbund = p_nvbund.
APPEND i_bsid.
ELSE.
SELECT SINGLE * FROM bsad WHERE bukrs EQ bseg-bukrs
AND kunnr EQ bseg-kunnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsad = bsad.
i_bsad-vbund = p_nvbund.
APPEND i_bsad.
ENDIF.
WHEN 'K'.
* Vendor
i_bseg = bseg.
APPEND i_bseg.
* for update of underlying documents
IF bseg-augbl IS INITIAL.
SELECT SINGLE * FROM bsik WHERE bukrs EQ bseg-bukrs
AND lifnr EQ bseg-lifnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsik = bsik.
i_bsik-vbund = p_nvbund.
APPEND i_bsik.
ELSE.
SELECT SINGLE * FROM bsak WHERE bukrs EQ bseg-bukrs
AND lifnr EQ bseg-lifnr
AND belnr EQ bseg-belnr
AND gjahr EQ bseg-gjahr
AND buzei EQ bseg-buzei.
i_bsak = bsak.
i_bsak-vbund = p_nvbund.
APPEND i_bsak.
ENDIF.
WHEN OTHERS.
MESSAGE e001(z3) WITH 'Unexpected line item type in'
bseg-bukrs bseg-belnr.
ENDCASE.
ENDSELECT.
ENDLOOP.
DESCRIBE TABLE i_bseg LINES l_lines.
WRITE : / 'Selected line items :', l_lines.
PERFORM update_glt3.
* updates
CHECK p_test IS INITIAL.
DESCRIBE TABLE i_bseg LINES l_lines.
WRITE : / 'Update of records BSEG:', l_lines.
IF l_lines NE 0.
UPDATE bseg FROM TABLE i_bseg.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSEG update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsis LINES l_lines.
WRITE : / 'Update of records BSIS:', l_lines.
IF l_lines NE 0.
UPDATE bsis FROM TABLE i_bsis.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSIS update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsas LINES l_lines.
WRITE : / 'Update of records BSAS:', l_lines.
IF l_lines NE 0.
UPDATE bsas FROM TABLE i_bsas.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSAS update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsid LINES l_lines.
WRITE : / 'Update of records BSID:', l_lines.
IF l_lines NE 0.
UPDATE bsid FROM TABLE i_bsid.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSID update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsad LINES l_lines.
WRITE : / 'Update of records BSAD:', l_lines.
IF l_lines NE 0.
UPDATE bsad FROM TABLE i_bsad.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSAD update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsik LINES l_lines.
WRITE : / 'Update of records BSIK:', l_lines.
IF l_lines NE 0.
UPDATE bsik FROM TABLE i_bsik.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSIK update error'.
ENDIF.
ENDIF.
DESCRIBE TABLE i_bsak LINES l_lines.
WRITE : / 'Update of records BSAK:', l_lines.
IF l_lines NE 0.
UPDATE bsak FROM TABLE i_bsak.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'BSAK update error'.
ENDIF.
ENDIF.
ENDFORM. "RUN_GL
*&--------------------------------------------------------------------*
*& Form UPDATE_GLT3
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM update_glt3 .
DATA: l_lines TYPE i,
l_found,
l_fieldname(12) TYPE c.
FIELD-SYMBOLS <hsl>.
FIELD-SYMBOLS <tsl>.
FIELD-SYMBOLS <ksl>.
REFRESH i_glt3.
LOOP AT i_bseg.
SELECT SINGLE * FROM bkpf WHERE bukrs EQ i_bseg-bukrs
AND gjahr EQ i_bseg-gjahr
AND belnr EQ i_bseg-belnr.
UNASSIGN <hsl>.
UNASSIGN <tsl>.
UNASSIGN <ksl>.
CONCATENATE 'I_GLT3-HSL' bkpf-monat INTO l_fieldname.
ASSIGN (l_fieldname) TO <hsl>.
CONCATENATE 'I_GLT3-TSL' bkpf-monat INTO l_fieldname.
ASSIGN (l_fieldname) TO <tsl>.
CONCATENATE 'I_GLT3-KSL' bkpf-monat INTO l_fieldname.
ASSIGN (l_fieldname) TO <ksl>.
* for the old VBUND value
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
PERFORM create_glt3.
ENDIF.
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
IF i_bseg-shkzg EQ 'S'.
SUBTRACT i_bseg-wrbtr FROM <tsl>.
SUBTRACT i_bseg-dmbtr FROM <hsl>.
SUBTRACT i_bseg-dmbtr FROM <ksl>.
ELSE.
ADD i_bseg-wrbtr TO <tsl>.
ADD i_bseg-dmbtr TO <hsl>.
ADD i_bseg-dmbtr TO <ksl>.
ENDIF.
MODIFY i_glt3.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
MESSAGE e001(z3) WITH 'Still no GLT3 ?!'.
ENDIF.
* for the new VBUND value (initial)
i_bseg-vbund = p_nvbund.
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
PERFORM create_glt3.
ENDIF.
CLEAR l_found.
LOOP AT i_glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
l_found = 'X'.
IF i_bseg-shkzg EQ 'S'.
ADD i_bseg-wrbtr TO <tsl>.
ADD i_bseg-dmbtr TO <hsl>.
ADD i_bseg-dmbtr TO <ksl>.
ELSE.
SUBTRACT i_bseg-wrbtr FROM <tsl>.
SUBTRACT i_bseg-dmbtr FROM <hsl>.
SUBTRACT i_bseg-dmbtr FROM <ksl>.
ENDIF.
MODIFY i_glt3.
EXIT.
ENDLOOP.
IF l_found IS INITIAL.
MESSAGE e001(z3) WITH 'Still no GLT3 ?!'.
ENDIF.
MODIFY i_bseg.
ENDLOOP.
CHECK p_test IS INITIAL.
DESCRIBE TABLE i_glt3 LINES l_lines.
WRITE : / 'Update of records GLT3:', l_lines.
IF l_lines NE 0.
MODIFY glt3 FROM TABLE i_glt3.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'GLT3 update error'.
ENDIF.
ENDIF.
ENDFORM. "UPDATE_GLT3
*&--------------------------------------------------------------------*
*& Form LOG
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM log.
CHECK p_log IS NOT INITIAL.
LOOP AT i_bseg WHERE shkzg EQ 'S'.
WRITE: / i_bseg-bukrs,
i_bseg-belnr,
i_bseg-gjahr,
i_bseg-buzei,
i_bseg-hkont,
i_bseg-vbund,
i_bseg-gsber,
i_bseg-shkzg,
i_bseg-dmbtr.
ENDLOOP.
LOOP AT i_glt3 WHERE drcrk EQ 'S'.
WRITE: / i_glt3-bukrs,
i_glt3-racct,
i_glt3-rbusa,
i_glt3-rtcur,
i_glt3-rassc,
i_glt3-drcrk.
ENDLOOP.
LOOP AT i_bseg WHERE shkzg EQ 'H'.
WRITE: / i_bseg-bukrs,
i_bseg-belnr,
i_bseg-gjahr,
i_bseg-buzei,
i_bseg-hkont,
i_bseg-vbund,
i_bseg-gsber,
i_bseg-shkzg,
i_bseg-dmbtr.
ENDLOOP.
LOOP AT i_glt3 WHERE drcrk EQ 'H'.
WRITE: / i_glt3-bukrs,
i_glt3-racct,
i_glt3-rbusa,
i_glt3-rtcur,
i_glt3-rassc,
i_glt3-drcrk.
ENDLOOP.
ENDFORM. "LOG
*&--------------------------------------------------------------------*
*& Form CREATE_GLT3
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM create_glt3.
FIELD-SYMBOLS: <feld>.
SELECT SINGLE * FROM glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont
AND rbusa EQ i_bseg-gsber
AND rassc EQ i_bseg-vbund
AND rtcur EQ bkpf-waers
AND drcrk EQ i_bseg-shkzg.
IF sy-subrc EQ 0.
i_glt3 = glt3.
APPEND i_glt3.
EXIT.
ENDIF.
SELECT SINGLE * FROM glt3 WHERE bukrs EQ i_bseg-bukrs
AND ryear EQ i_bseg-gjahr
AND racct EQ i_bseg-hkont.
IF sy-subrc NE 0.
MESSAGE e001(z3) WITH 'GLT3 not found for:'
i_bseg-bukrs
i_bseg-hkont
i_bseg-gjahr.
ENDIF.
i_glt3 = glt3.
i_glt3-rbusa = i_bseg-gsber.
i_glt3-rassc = i_bseg-vbund.
i_glt3-rtcur = bkpf-waers.
i_glt3-drcrk = i_bseg-shkzg.
* clear amount fields
DO.
CHECK sy-index GT 16.
ASSIGN COMPONENT sy-index OF STRUCTURE i_glt3 TO <feld>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
CLEAR <feld>.
ENDDO.
APPEND i_glt3.
EXIT.
ENDFORM. "CREATE_GLT3
Freitag, 20. Januar 2012
Customizing: Posting Period Variant per GAAP
In NewGL one can assign a separate Posting Period Variant per ledger (usually representing different GAAP's). In customizing this setting is quite hidden:
It is in the setting for non-leading ledgers, accessible via customizing menu SPRO.
It is in the setting for non-leading ledgers, accessible via customizing menu SPRO.
Montag, 1. August 2011
ABAP Tool: Great stuff
Thanks to Frank.
*&-----------------------------------------------------------*
*& Report Z_UPLOAD_TOOL
*&
*&-----------------------------------------------------------*
*&
*& Loads every file up and down
*&-----------------------------------------------------------*
REPORT Z_UPLOAD_TOOL.
tYPE-POOLS: abap.
PARAMETERS: tabname TYPE tabname16,
download RADIOBUTTON GROUP radi,
upload RADIOBUTTON GROUP radi.
DATA l_tab TYPE TABLE OF sdokpath.
DATA l_wa_tab TYPE sdokpath.
DATA l_path TYPE string.
DATA l_wa_ref TYPE REF TO data.
DATA l_offset TYPE i.
DATA load(2) TYPE c.
FIELD-SYMBOLS: <l_string> TYPE ANY,
<f1> TYPE abap_compdescr,
<l_wa_tab> TYPE ANY.
DATA l_wa_data_tab(2000) TYPE c.
DATA l_data_tab LIKE TABLE OF l_wa_data_tab.
DATA l_descr_ref TYPE REF TO cl_abap_typedescr.
DATA l_struc_ref TYPE REF TO cl_abap_structdescr.
CONSTANTS: con_download(2) TYPE c VALUE 'DL',
con_upload(2) TYPE c VALUE 'UL'.
START-OF-SELECTION.
IF download EQ 'X'.
load = con_download.
ELSE.
load = con_upload.
ENDIF.
CASE load.
WHEN con_download.
CREATE DATA l_wa_ref TYPE (tabname).
ASSIGN l_wa_ref->* TO <l_wa_tab>.
SELECT * FROM (tabname) INTO <l_wa_tab>.
l_wa_data_tab = <l_wa_tab>.
APPEND l_wa_data_tab TO l_data_tab.
ENDSELECT.
CALL FUNCTION 'TMP_GUI_FILE_OPEN_DIALOG'
EXPORTING
window_title = 'DOWNLOAD'
TABLES
file_table = l_tab
EXCEPTIONS
cntl_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
ENDIF.
READ TABLE l_tab INTO l_wa_tab INDEX 1.
l_path = l_wa_tab.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = l_path
TABLES
data_tab = l_data_tab
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
OTHERS = 22.
IF sy-subrc <> 0.
MESSAGE e063(5v).
* No records down/uploaded
ENDIF.
WHEN con_upload.
CALL FUNCTION 'TMP_GUI_FILE_OPEN_DIALOG'
EXPORTING
window_title = 'UPLOAD'
TABLES
file_table = l_tab
EXCEPTIONS
cntl_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
ENDIF.
READ TABLE l_tab INTO l_wa_tab INDEX 1.
l_path = l_wa_tab.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = l_path
TABLES
data_tab = l_data_tab
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc <> 0.
MESSAGE e063(5v).
* No records down/uploaded
ENDIF.
CREATE DATA l_wa_ref TYPE (tabname).
ASSIGN l_wa_ref->* TO <l_wa_tab>.
CALL METHOD cl_abap_typedescr=>describe_by_name
EXPORTING
p_name = tabname
RECEIVING
p_descr_ref = l_descr_ref
EXCEPTIONS
type_not_found = 1
OTHERS = 2.
IF sy-subrc <> 0.
.
ENDIF.
l_struc_ref ?= l_descr_ref.
LOOP AT l_data_tab ASSIGNING <l_string>.
CLEAR l_offset.
LOOP AT l_struc_ref->components ASSIGNING <f1>.
<l_wa_tab>+l_offset(<f1>-length) =
<l_string>+l_offset(<f1>-length).
l_offset = l_offset + <f1>-length.
ENDLOOP.
MODIFY (tabname) FROM <l_wa_tab>.
ENDLOOP.
endcase.
*&-----------------------------------------------------------*
*& Report Z_UPLOAD_TOOL
*&
*&-----------------------------------------------------------*
*&
*& Loads every file up and down
*&-----------------------------------------------------------*
REPORT Z_UPLOAD_TOOL.
tYPE-POOLS: abap.
PARAMETERS: tabname TYPE tabname16,
download RADIOBUTTON GROUP radi,
upload RADIOBUTTON GROUP radi.
DATA l_tab TYPE TABLE OF sdokpath.
DATA l_wa_tab TYPE sdokpath.
DATA l_path TYPE string.
DATA l_wa_ref TYPE REF TO data.
DATA l_offset TYPE i.
DATA load(2) TYPE c.
FIELD-SYMBOLS: <l_string> TYPE ANY,
<f1> TYPE abap_compdescr,
<l_wa_tab> TYPE ANY.
DATA l_wa_data_tab(2000) TYPE c.
DATA l_data_tab LIKE TABLE OF l_wa_data_tab.
DATA l_descr_ref TYPE REF TO cl_abap_typedescr.
DATA l_struc_ref TYPE REF TO cl_abap_structdescr.
CONSTANTS: con_download(2) TYPE c VALUE 'DL',
con_upload(2) TYPE c VALUE 'UL'.
START-OF-SELECTION.
IF download EQ 'X'.
load = con_download.
ELSE.
load = con_upload.
ENDIF.
CASE load.
WHEN con_download.
CREATE DATA l_wa_ref TYPE (tabname).
ASSIGN l_wa_ref->* TO <l_wa_tab>.
SELECT * FROM (tabname) INTO <l_wa_tab>.
l_wa_data_tab = <l_wa_tab>.
APPEND l_wa_data_tab TO l_data_tab.
ENDSELECT.
CALL FUNCTION 'TMP_GUI_FILE_OPEN_DIALOG'
EXPORTING
window_title = 'DOWNLOAD'
TABLES
file_table = l_tab
EXCEPTIONS
cntl_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
ENDIF.
READ TABLE l_tab INTO l_wa_tab INDEX 1.
l_path = l_wa_tab.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = l_path
TABLES
data_tab = l_data_tab
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
OTHERS = 22.
IF sy-subrc <> 0.
MESSAGE e063(5v).
* No records down/uploaded
ENDIF.
WHEN con_upload.
CALL FUNCTION 'TMP_GUI_FILE_OPEN_DIALOG'
EXPORTING
window_title = 'UPLOAD'
TABLES
file_table = l_tab
EXCEPTIONS
cntl_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
ENDIF.
READ TABLE l_tab INTO l_wa_tab INDEX 1.
l_path = l_wa_tab.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = l_path
TABLES
data_tab = l_data_tab
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc <> 0.
MESSAGE e063(5v).
* No records down/uploaded
ENDIF.
CREATE DATA l_wa_ref TYPE (tabname).
ASSIGN l_wa_ref->* TO <l_wa_tab>.
CALL METHOD cl_abap_typedescr=>describe_by_name
EXPORTING
p_name = tabname
RECEIVING
p_descr_ref = l_descr_ref
EXCEPTIONS
type_not_found = 1
OTHERS = 2.
IF sy-subrc <> 0.
.
ENDIF.
l_struc_ref ?= l_descr_ref.
LOOP AT l_data_tab ASSIGNING <l_string>.
CLEAR l_offset.
LOOP AT l_struc_ref->components ASSIGNING <f1>.
<l_wa_tab>+l_offset(<f1>-length) =
<l_string>+l_offset(<f1>-length).
l_offset = l_offset + <f1>-length.
ENDLOOP.
MODIFY (tabname) FROM <l_wa_tab>.
ENDLOOP.
endcase.
Mittwoch, 20. Juli 2011
Customizing: Variants for Real-Time Integration CO -> FI
In individual RTI-Variants, Document Lines can be extendetd to i.e. Cross-Functional Area. Like this, FI line items are updated during Cost Centre/Internal Order assessment/settelment. Maintanance via SPRO.
Abonnieren
Posts (Atom)
Posts
