Friday, 26 March 2021

Use CDS view to get company, account, and monthly gains and losses and balances from ACDOCA.

Introduction

From SAP ERP 6.0 to SAP S / 4, the standard table structure has changed significantly.

I mainly understand it as a change to accommodate reporting.

On the other hand, I feel that there are a minority of developers who can imagine the concrete source code of CodePushdown.

This time, we will introduce a sample program that outputs a monthly increase / decrease amount and balance as a report.

This sample program is created by accessing the ACDOCA of the SAP standard table and using the Code Pushdown design method.

Last time, I introduced a program that uses New open SQL.

This time, I implemented the same logic in the ABAP CDS view.

Subject

The basic idea is to acquire data using ACDOCA as a Fact Table.

・ ABAP CDS view : Y_DECISION

In preparation, create a Decision table to get your balance. SAP HANA has a DUMMY table.

In the ABAP Dictionary, create a YDUMMY table that mimics the DUMMY table.

Decision table is created by executing SELECT statement on YDUMMY table.

Another option is to create an Add-on table.

Use the three ABAP CDS views to do three main process.

・ ABAP CDS view : Y_ACDOCA

Aggregate on a monthly basis for ACDOCA.

・ ABAP CDS view : Y_ACDOCA01

In the first half, you get the increase / decrease value. Use the SQL aggregate function SUM ().

In the second half, you will get the balance. Join ACDOCA and Decision Table with Inner Join.

The record is amplified to get the cumulative amount (balance).

・ ABAP CDS view : Y_ACDOCA02

The increase / decrease amount and balance are output as side-by-side items.

Get text information from G / L account master table.

The report program outputs the results of the ABAP CDS view to the screen.

The execution environment is ABAP 7.52 or later.

The SQL instructions to use are as follows.

・ Inner join

・ Sum () ~ group by

・ Union all

Conclusion

-I think I was able to show a concrete coding example of Code Pushdown.

-The processing speed was ACDOCA: 3 million records, and the response time was 0.1 sec or less.

-Compared to Non-Code Pushdown, the amount of coding is reduced.

-You can expect the effect of suppressing program bugs.

-By introducing the Code Pushdown design method, the following can be achieved.

・ Real Time Repoting

・ Low Cost Development

・ Low Cost Operation

・ Low Cost Maintenance

Supplement

You can get a monthly list of all account codes at once by switching the increase / decrease amount and balance using the CASE clause according to the PL account and BS account from the account code master.

The source code is as follows.

・Transparent table : DUMMY table

SAP ABAP Exam Prep, SAP ABAP Learning, SAP ABAP Tutorial and Material, SAP ABAP Career, SAP ABAP Guides

SAP ABAP Exam Prep, SAP ABAP Learning, SAP ABAP Tutorial and Material, SAP ABAP Career, SAP ABAP Guides

◉ ABAP CDS view : Y_DECISION

@AbapCatalog.sqlViewName: 'Y_DECISIOND'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Y_DECISION'

//In preparation, use ITAB to create a DecisionTable to get the balance.
define view Y_DECISION
       as select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '001' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '001' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '002' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '002' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '002' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '003' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '003' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '003' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '003' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '004' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '004' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '004' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '004' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '004' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '005' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '005' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '005' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '005' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '005' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '005' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '006' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '006' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '006' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '006' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '006' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '006' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '006' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '007' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '007' as POPER_SOURCE,  '007' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '007' as POPER_SOURCE,  '008' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '008' as POPER_SOURCE,  '008' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '007' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '008' as POPER_SOURCE,  '009' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '009' as POPER_SOURCE,  '009' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '007' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '008' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '009' as POPER_SOURCE,  '010' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '010' as POPER_SOURCE,  '010' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '007' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '008' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '009' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '010' as POPER_SOURCE,  '011' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '011' as POPER_SOURCE,  '011' as POPER_TARGET}

union all select from ydummy{  1     as COEFFICIENT,  '000' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '001' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '002' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '003' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '004' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '005' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '006' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '007' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '008' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '009' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '010' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '011' as POPER_SOURCE,  '012' as POPER_TARGET}
union all select from ydummy{  1     as COEFFICIENT,  '012' as POPER_SOURCE,  '012' as POPER_TARGET} 

・ ABAP CDS view : Y_ACDOCA

@AbapCatalog.sqlViewName: 'Y_ACDOCAD'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Y_ACDOCA'

//・ First process
//Aggregate on a monthly basis for ACDOCA.
define view Y_ACDOCA
  as select from acdoca
{
  key acdoca.rldnr,  //Ledger in General Ledger Accounting
  key acdoca.rbukrs, //Company Code
  key acdoca.periv,  //Fiscal Year Variant
  key acdoca.gjahr,  //Fiscal Year
  key acdoca.poper,  //Posting period
  key acdoca.ktopl,  //Chart of Accounts
  key acdoca.racct,  //Account Number
  key acdoca.rcntr,  //Cost Center
  key acdoca.prctr,  //Profit Center
  key acdoca.rfarea, //Functional Area
  key acdoca.rbusa,  //Business Area
  key acdoca.kokrs,  //Controlling Area
  key acdoca.werks,  //Plant
  key acdoca.matnr,  //Material Number
  key acdoca.lifnr,  //Account Number of Vendor or Creditor
  key acdoca.kunnr,  //Customer Number
  key acdoca.rwcur,  //Transaction Currency
  key acdoca.rhcur,  //Company Code Currency
      sum( acdoca.wsl ) as wsl, //Amount in Transaction Currency
      sum( acdoca.hsl ) as hsl  //Amount in Company Code Currency
}
group by
  acdoca.rldnr,                 //Ledger in General Ledger Acc
  acdoca.rbukrs,                //Company Code
  acdoca.periv,                 //Fiscal Year Variant
  acdoca.gjahr,                 //Fiscal Year
  acdoca.poper,                 //Posting period
  acdoca.ktopl,                 //Chart of Accounts
  acdoca.racct,                 //Account Number
  acdoca.rcntr,                 //Cost Center
  acdoca.prctr,                 //Profit Center
  acdoca.rfarea,                //Functional Area
  acdoca.rbusa,                 //Business Area
  acdoca.kokrs,                 //Controlling Area
  acdoca.werks,                 //Plant
  acdoca.matnr,                 //Material Number
  acdoca.lifnr,                 //Account Number of Vendor or
  acdoca.kunnr,                 //Customer Number
  acdoca.rwcur,                 //Transaction Currency
  acdoca.rhcur                  //Company Code Currency

・ ABAP CDS view : Y_ACDOCA01

@AbapCatalog.sqlViewName: 'Y_ACDOCA01D'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Y_ACDOCA01'

//  "・ Second process
define view Y_ACDOCA01
  //In the first half, you get the increase / decrease value. Use the SQL aggregate function SUM ().
  as select from Y_ACDOCA
{
  Y_ACDOCA.rldnr,                     //Ledger in General Ledger Accounting
  Y_ACDOCA.rbukrs,                    //Company Code
  Y_ACDOCA.periv,                     //Fiscal Year Variant
  Y_ACDOCA.gjahr,                     //Fiscal Year
  Y_ACDOCA.poper,                     //Posting period
  Y_ACDOCA.ktopl,                     //Chart of Accounts
  Y_ACDOCA.racct,                     //Account Number
  Y_ACDOCA.rcntr,                     //Cost Center
  Y_ACDOCA.prctr,                     //Profit Center
  Y_ACDOCA.rfarea,                    //Functional Area
  Y_ACDOCA.rbusa,                     //Business Area
  Y_ACDOCA.kokrs,                     //Controlling Area
  Y_ACDOCA.werks,                     //Plant
  Y_ACDOCA.matnr,                     //Material Number
  Y_ACDOCA.lifnr,                     //Account Number of Vendor or Creditor
  Y_ACDOCA.kunnr,                     //Customer Number
  Y_ACDOCA.rwcur,                     //Transaction Currency
  Y_ACDOCA.rhcur,                     //Company Code Currency
  Y_ACDOCA.wsl              as wsl00, //Amount in Transaction Currency
  Y_ACDOCA.hsl              as hsl00, //Amount in Company Code Currency
  cast( 0 as fins_vwcur12 ) as wsl01, //Amount in Transaction Currency
  cast( 0 as fins_vhcur12 ) as hsl01  //Amount in Company Code Currency
}
//In the second half, you will get the balance. Join ACDOCA and Decision Table with Inner Join.
//The record is amplified to get the cumulative amount (balance).
union all select from Y_ACDOCA
  inner join          Y_DECISION on Y_DECISION.POPER_SOURCE = Y_ACDOCA.poper
{
  Y_ACDOCA.rldnr,                     //Ledger in General Ledger Accounting
  Y_ACDOCA.rbukrs,                    //Company Code
  Y_ACDOCA.periv,                     //Fiscal Year Variant
  Y_ACDOCA.gjahr,                     //Fiscal Year
  Y_DECISION.POPER_TARGET   as poper, //Posting period
  Y_ACDOCA.ktopl,                     //Chart of Accounts
  Y_ACDOCA.racct,                     //Account Number
  Y_ACDOCA.rcntr,                     //Cost Center
  Y_ACDOCA.prctr,                     //Profit Center
  Y_ACDOCA.rfarea,                    //Functional Area
  Y_ACDOCA.rbusa,                     //Business Area
  Y_ACDOCA.kokrs,                     //Controlling Area
  Y_ACDOCA.werks,                     //Plant
  Y_ACDOCA.matnr,                     //Material Number
  Y_ACDOCA.lifnr,                     //Account Number of Vendor or Creditor
  Y_ACDOCA.kunnr,                     //Customer Number
  Y_ACDOCA.rwcur,                     //Transaction Currency
  Y_ACDOCA.rhcur,                     //Company Code Currency
  cast( 0 as fins_vwcur12 ) as wsl00, //Amount in Transaction Currency
  cast( 0 as fins_vhcur12 ) as hsl00, //Amount in Company Code Currency
  sum( Y_ACDOCA.wsl )       as wsl01, //Amount in Transaction Currency
  sum( Y_ACDOCA.hsl )       as hsl01  //Amount in Company Code Currency
}
group by
  Y_ACDOCA.rldnr,          //Ledger in General Ledger Accounting
  Y_ACDOCA.rbukrs,         //Company Code
  Y_ACDOCA.periv,          //Fiscal Year Variant
  Y_ACDOCA.gjahr,          //Fiscal Year
  Y_DECISION.POPER_TARGET, //Posting period
  Y_ACDOCA.ktopl,          //Chart of Accounts
  Y_ACDOCA.racct,          //Account Number
  Y_ACDOCA.rcntr,          //Cost Center
  Y_ACDOCA.prctr,          //Profit Center
  Y_ACDOCA.rfarea,         //Functional Area
  Y_ACDOCA.rbusa,          //Business Area
  Y_ACDOCA.kokrs,          //Controlling Area
  Y_ACDOCA.werks,          //Plant
  Y_ACDOCA.matnr,          //Material Number
  Y_ACDOCA.lifnr,          //Account Number of Vendor or Creditor
  Y_ACDOCA.kunnr,          //Customer Number
  Y_ACDOCA.rwcur,          //Transaction Currency
  Y_ACDOCA.rhcur           //Company Code Currency

・ ABAP CDS view : Y_ACDOCA02

@AbapCatalog.sqlViewName: 'Y_ACDOCA02D'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Y_ACDOCA02'

//・ Third process
//The increase / decrease amount and balance are output as side-by-side items.
//Get text information from G / L account master table.
define view Y_ACDOCA02
  as select from    Y_ACDOCA01
    inner join      ska1 on  ska1.ktopl = Y_ACDOCA01.ktopl
                         and ska1.saknr = Y_ACDOCA01.racct
                         and ska1.xbilk = 'X'
    left outer join skat on  skat.spras = $session.system_language
                         and skat.ktopl = ska1.ktopl
                         and skat.saknr = ska1.saknr

{
  Y_ACDOCA01.rldnr,  //Ledger in General Ledger Accounting
  Y_ACDOCA01.rbukrs, //Company Code
  Y_ACDOCA01.periv,  //Fiscal Year Variant
  Y_ACDOCA01.gjahr,  //Fiscal Year
  Y_ACDOCA01.poper,  //Posting period
  Y_ACDOCA01.ktopl,  //Chart of Accounts
  Y_ACDOCA01.racct,  //Account Number
  skat.txt20,        //G/L account short text
  Y_ACDOCA01.rcntr,  //Cost Center
  Y_ACDOCA01.prctr,  //Profit Center
  Y_ACDOCA01.rfarea, //Functional Area
  Y_ACDOCA01.rbusa,  //Business Area
  Y_ACDOCA01.kokrs,  //Controlling Area
  Y_ACDOCA01.werks,  //Plant
  Y_ACDOCA01.matnr,  //Material Number
  Y_ACDOCA01.lifnr,  //Account Number of Vendor or Creditor
  Y_ACDOCA01.kunnr,  //Customer Number
  Y_ACDOCA01.rwcur,  //Transaction Currency
  Y_ACDOCA01.rhcur,  //Company Code Currency
  sum( Y_ACDOCA01.wsl00 ) as wsl00, //Amount in Transaction Currency
  sum( Y_ACDOCA01.hsl00 ) as hsl00, //Amount in Company Code Currency
  sum( Y_ACDOCA01.wsl01 ) as wsl01, //Amount in Transaction Currency
  sum( Y_ACDOCA01.hsl01 ) as hsl01  //Amount in Company Code Currency
}
group by
  Y_ACDOCA01.rldnr,  //Ledger in General Ledger Accounting
  Y_ACDOCA01.rbukrs, //Company Code
  Y_ACDOCA01.periv,  //Fiscal Year Variant
  Y_ACDOCA01.gjahr,  //Fiscal Year
  Y_ACDOCA01.poper,  //Posting period
  Y_ACDOCA01.ktopl,  //Chart of Accounts
  Y_ACDOCA01.racct,  //Account Number
  skat.txt20,        //G/L account short text
  Y_ACDOCA01.rcntr,  //Cost Center
  Y_ACDOCA01.prctr,  //Profit Center
  Y_ACDOCA01.rfarea, //Functional Area
  Y_ACDOCA01.rbusa,  //Business Area
  Y_ACDOCA01.kokrs,  //Controlling Area
  Y_ACDOCA01.werks,  //Plant
  Y_ACDOCA01.matnr,  //Material Number
  Y_ACDOCA01.lifnr,  //Account Number of Vendor or Creditor
  Y_ACDOCA01.kunnr,  //Customer Number
  Y_ACDOCA01.rwcur,  //Transaction Currency
  Y_ACDOCA01.rhcur //Company Code Currency
 
・ Report program

*&---------------------------------------------------------------------*
*& Report Y_SQL_CONSOLE
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Y_SQL_CONSOLE.

*-----------------------------------------------------------------------
* GLOBAL VARIABLE
*-----------------------------------------------------------------------
DATA GDF_SUBRC TYPE SY-SUBRC.

*-----------------------------------------------------------------------
* CONDITION VIEW
*-----------------------------------------------------------------------
DATA GDF_RLDNR TYPE ACDOCA-RLDNR.
TYPES GTR_RLDNR LIKE RANGE OF GDF_RLDNR.
SELECT-OPTIONS S_RLDNR FOR GDF_RLDNR DEFAULT '0L'."Ledger in General Ledger Accounting,

DATA GDF_RBUKRS TYPE ACDOCA-RBUKRS.
TYPES GTR_RBUKRS LIKE RANGE OF GDF_RBUKRS.
SELECT-OPTIONS S_RBUKRS FOR GDF_RBUKRS.           "Company Code

DATA GDF_RACCT TYPE ACDOCA-RACCT.
TYPES GTR_RACCT LIKE RANGE OF GDF_RACCT.
SELECT-OPTIONS S_RACCT FOR GDF_RACCT.             "Account Number

*-----------------------------------------------------------------------
* START-OF-SELECTION
*-----------------------------------------------------------------------
START-OF-SELECTION.
  PERFORM START_OF_SELECTION
    USING
      S_RLDNR[]
      S_RBUKRS[]
      S_RACCT[]
    CHANGING
      GDF_SUBRC.

*-----------------------------------------------------------------------
* SUBROUTINE
*-----------------------------------------------------------------------
FORM START_OF_SELECTION
    USING
      PIR_RLDNR TYPE GTR_RLDNR
      PIR_RBUKRS TYPE GTR_RBUKRS
      PIR_RACCT TYPE GTR_RACCT
    CHANGING
      POF_SUBRC TYPE SY-SUBRC.
*-----------------------------------------------------------------------
* Please write the SQL statement here ☆
* Set the internal table to be stored to LDT_DATA ☆
* Inline definition of LDT_DATA is fun ☆
  SELECT
    FROM ACDOCA
    FIELDS COUNT( * ) AS LDF_INPUT_RECORDS
    WHERE ACDOCA~RLDNR  IN @PIR_RLDNR             "Ledger in General Ledger Accounting,
      AND ACDOCA~RBUKRS IN @PIR_RBUKRS            "Company Code
      AND ACDOCA~RACCT  IN @PIR_RACCT             "Account Number
    INTO @DATA(LDF_INPUT_RECORDS).

  DATA LDF_TIMESTAMP_START TYPE TIMESTAMPL.
  GET TIME STAMP FIELD LDF_TIMESTAMP_START.

  SELECT
    FROM Y_ACDOCA02
    FIELDS *
    ORDER BY
      Y_ACDOCA02~RLDNR,                           "Ledger in General Ledger Accounting
      Y_ACDOCA02~RBUKRS,                          "Company Code
      Y_ACDOCA02~PERIV,                           "Fiscal Year Variant
      Y_ACDOCA02~KTOPL,                           "Chart of Accounts
      Y_ACDOCA02~RACCT,                           "Account Number
      Y_ACDOCA02~TXT20,                           "G/L account short text
      Y_ACDOCA02~RCNTR,                           "Cost Center
      Y_ACDOCA02~PRCTR,                           "Profit Center
      Y_ACDOCA02~RFAREA,                          "Functional Area
      Y_ACDOCA02~RBUSA,                           "Business Area
      Y_ACDOCA02~KOKRS,                           "Controlling Area
      Y_ACDOCA02~WERKS,                           "Plant
      Y_ACDOCA02~MATNR,                           "Material Number
      Y_ACDOCA02~LIFNR,                           "Account Number of Vendor or Creditor
      Y_ACDOCA02~KUNNR,                           "Customer Number
      Y_ACDOCA02~GJAHR,                           "Fiscal Year
      Y_ACDOCA02~POPER,                           "Posting period
      Y_ACDOCA02~RWCUR,                           "Transaction Currency
      Y_ACDOCA02~RHCUR                            "Company Code Currency
    INTO TABLE @DATA(LDT_DATA).

  DATA LDF_TIMESTAMP_END TYPE TIMESTAMPL.
  GET TIME STAMP FIELD LDF_TIMESTAMP_END.
*-----------------------------------------------------------------------
  TRY.
      CL_SALV_TABLE=>FACTORY(
        IMPORTING
          R_SALV_TABLE = DATA(LDO_ALV)
        CHANGING
          T_TABLE  = LDT_DATA ).
    CATCH CX_SALV_MSG INTO DATA(LDX_SALV_MSG).
      POF_SUBRC = 8.
      RETURN.
  ENDTRY.

  DATA(LDO_HEADER) = NEW CL_SALV_FORM_LAYOUT_GRID( ).

  DATA(LDO_H_LABEL) = LDO_HEADER->CREATE_LABEL( ROW = 1 COLUMN = 1 ).
  LDO_H_LABEL->SET_TEXT( 'Performance data' ).

  DATA(LDO_H_FLOW) = LDO_HEADER->CREATE_FLOW( ROW = 2  COLUMN = 1 ).
  LDO_H_FLOW->CREATE_TEXT( TEXT = 'Input records' ).
  LDO_H_FLOW = LDO_HEADER->CREATE_FLOW( ROW = 2  COLUMN = 2 ).
  LDO_H_FLOW->CREATE_TEXT( TEXT = LDF_INPUT_RECORDS ).

  LDO_H_FLOW = LDO_HEADER->CREATE_FLOW( ROW = 3  COLUMN = 1 ).
  LDO_H_FLOW->CREATE_TEXT( TEXT = 'Output records' ).
  LDO_H_FLOW = LDO_HEADER->CREATE_FLOW( ROW = 3  COLUMN = 2 ).
  LDO_H_FLOW->CREATE_TEXT( TEXT = LINES( LDT_DATA ) ).

  LDO_H_FLOW = LDO_HEADER->CREATE_FLOW( ROW = 4  COLUMN = 1 ).
  LDO_H_FLOW->CREATE_TEXT( TEXT = 'Time to retrieve data' ).
  LDO_H_FLOW = LDO_HEADER->CREATE_FLOW( ROW = 4  COLUMN = 2 ).
  LDO_H_FLOW->CREATE_TEXT( TEXT = CL_ABAP_TSTMP=>SUBTRACT( EXPORTING TSTMP1 = LDF_TIMESTAMP_END TSTMP2 = LDF_TIMESTAMP_START ) ).

  LDO_ALV->SET_TOP_OF_LIST( LDO_HEADER ).
  LDO_ALV->SET_TOP_OF_LIST_PRINT( LDO_HEADER ).

  LDO_ALV->GET_FUNCTIONS( )->SET_ALL( ).
  LDO_ALV->GET_COLUMNS( )->SET_OPTIMIZE( ).
  LDO_ALV->GET_SELECTIONS( )->SET_SELECTION_MODE( EXPORTING VALUE = IF_SALV_C_SELECTION_MODE=>CELL ).
  LDO_ALV->DISPLAY( ).

  POF_SUBRC = 0.
ENDFORM.

No comments:

Post a Comment