How to insert record in report at run time-CSDN博客

Scenario Description:-

In general ledger data remains in transaction currency, local currency etc. If we want to display trial balance in local currency (company code currency) i.e. reporting currency is local currency then for some GL accounts we have to display data in local currency that is already existing with currency type = 10 (converted into local currency at transaction date) but for other GL account we have to convert into local currency on the day of reporting. Thus there will come some difference in trial balance and that will go to profit and loss account.

Now this difference of amount that will go to profit and loss account should also display in trial balances. So in short our problem can be described as in report there are 2 columns credit and debit. In row there is field GL account. So trial balance will look like as follows

G/L account	Debit	Credit
1000000000	100
2000000000		200
3000000000	150
Result	250	200

Now as trial balance should show Debit = Credit. The difference of above 2 i.e. 50 should go profit and loss account and that should also show in above trial balance. So overall report should look as follows

G/L account	Debit	Credit
1000000000	100
2000000000		200
3000000000	150
4000000000		50
Result	250	250

So at run time we came to know the difference coming in trial balance is 50 and that should go to profit and loss account and that should also be visible in trial balance as above.

We will solve above mentioned problem by following example. Let us assume following data is there in final data provider (cube /DSO).

G/L account	Comp code	Currency	Currency Type	Debit	Credit
1000000000	1000	INR	10	100
1000000000	1000	USD	00	2
2000000000	1000	INR	10		100
2000000000	1000	USD	00		2

Now for above data if user runs trial balance report on particular date then for account 1000000000 amount should display in local currency(INR) on transaction date and that is present in info provider with currency type = 10. But for account 2000000000 as account type (monitory, non-monitory etc.) is like that it should display amount in local currency by converting transaction currency amount into local currency on reporting date. That is for above example 2$ converted into INR at reporting date. And if ex. rate is 49 for reporting date then it will be 98 INR. So this 2 Rs difference should go to P&L account.

Step 1:- Create direct update type DSO and move all data of final data provider into direct update DSO. Report should be based on this DSO. To move data from DSO or Cube to direct update DSO is very easy by writing a program in se38 and including that into process chain. Program is simply to transfer data from 1 table to other table (in case of standard DSO to direct update DSO) by select and insert SQL statements. And for cube to direct update DSO 1st you will have to read data from cube by FM (RSDRI_INFOPROV_READ) and inserting into direct update DSO by insert statement.

Step 2:-In the report create a variable of processing type ‘customer exit’ that is for no purpose other than bringing the control in user exit. For example we create a variable Var1 under 0GL_ACCOUNT object of processing by ‘customer exit’. This will not return any value but will carry control in user exit where we can insert record in direct update DSO of that profit & loss account. As of now let us assume we created variable Var1 under 0GL_ACCOUNT of processing type ‘customer exit’.

Step3:-Now we will write following code in user exit. (In include ZXRSRU01)

ZGL_ACCOUNT is a variable created on 0GL_ACCOUNT of processing type customer exit. This is only for the purpose to send control to user exit so that our code can execute.

    WHEN 'ZGL_ACCOUNT'.
      IF i_step = 2.
        tables: /BIC/azdso00.
        DATA: BEGIN OF itab_gl_account OCCURS 0,
                gl_account(10),
                account_type(1),
              END OF itab_gl_account,
              BEGIN OF itab_dso OCCURS 0,
                gl_account(10),
                currency TYPE /bi0/oicurrency,
                curtype TYPE /bi0/oicurtype,
                debit TYPE /bi0/oidebit,
                credit TYPE /bi0/oicredit,
                END OF itab_dso,
              wa LIKE /BIC/AZDSO00,
              amount LIKE   itab_dso-debit,"or /bic/aDSO00-credit
              debit LIKE   itab_dso-debit,
              credit LIKE itab_dso-credit.
        FIELD-SYMBOLS : <wa> LIKE itab_dso.
        SELECT gl_account /BIC/ZIND_ACT FROM /bi0/mgl_account INTO TABLEitab_gl_account."/BIC/ZIND_ACT = account type
        SELECT gl_account currency curtype debit credit FROM /bic/azdso00 INTOTABLE itab_dso.*above is preparation of data. we are assuming company code entered by user is '1000' in selection screen*else we will have to pick company code value from i_t_var_range and restrict above select statement with*          that company code
        LOOP AT itab_dso ASSIGNING <wa>.
          READ TABLE itab_gl_account WITH KEY gl_account = <wa>-gl_account.
          IF itab_gl_account-account_type = 'M' AND <wa>-curtype = '00'.*here we are assuming for account type M we need to convert amount into local currency at reporting date
            amount = <wa>-debit + <wa>-credit."either debit or credit field will be non zero
            CALL FUNCTION 'CONVERT_TO_FOREIGN_CURRENCY'
              EXPORTING*               CLIENT                  = SY-MANDT
                date                    = sy-datum
                foreign_currency        = 'INR'
                local_amount            = amount
                local_currency          = <wa>-currency*               RATE                    = 0*               TYPE_OF_RATE            = 'M'*               READ_TCURR              = 'X'
             IMPORTING*               EXCHANGE_RATE           =
               foreign_amount          = amount*               FOREIGN_FACTOR          =*               LOCAL_FACTOR            =*               EXCHANGE_RATEX          =*               DERIVED_RATE_TYPE       =*               FIXED_RATE              =
             EXCEPTIONS
               no_rate_found           = 1
               overflow                = 2
               no_factors_found        = 3
               no_spread_found         = 4
               derived_2_times         = 5
               OTHERS                  = 6
                      .
            IF sy-subrc <> 0.* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
            ENDIF.
            IF <wa>-debit > 0.
              <wa>-debit = amount.
            ELSE.
              <wa>-credit = amount.
            ENDIF.
          ELSEIF itab_gl_account-account_type = 'M' AND <wa>-curtype = '10'.
            <wa>-debit = 0.
            <wa>-credit = 0. "for account type M only records with currency type = 00(transaction curr) will be used
          ELSEIF itab_gl_account-account_type <> 'M' AND <wa>-curtype = '00'.
            <wa>-debit = <wa>-credit = 0."for these accounts record with curr type = 10(local curr) will be used
          ENDIF.*to calculate difference of debit and credit to put into P&L account we need to calcualte total debit and credit amount
          debit = <wa>-debit + debit.
          credit = <wa>-credit + credit.

        ENDLOOP.
        amount = debit - credit.

        wa-gl_account = '3000000000'."let us assume P&L account is 3000000000
        wa-currency = 'INR'.
        wa-curtype = '10'.
        IF amount > 0.
          wa-debit = amount.
        ELSE.
          wa-credit = amount * -1.
        ENDIF.

        INSERT into /bic/aZDSO00 values wa.*Above is key statement.It will add record in direct update DSO at run time and here that will be display*amount in P&L account i.e. difference amount
      ENDIF.