-2
Former Member

### Calculation view in hana modelling

Hi Experts, i am new to HANA modelling And i have basic knowledge in this.

I have one requirement to develop QUERY view which will be consumed by Informatica for reporting purpose. Requirement is consume the standard query view "SA.IS.REATIAL.CARS4H.INVENTORYVISIBILITYQUERY". apart from this query output we need to calculate the below calculated columns :

Table MCHB

First, get the Un-restricted stock for all the article/site combinations from table MCHB

Get CLABS and SGT_SCAT from MCHB where

MATNR = MARD - MATNR

WERKS = MARD – WERKS

LGORT = MARD – LGORT

Store MCHB – CLABS as Un-restricted stock for the MATNR, WERKS, LGORT and SGT_SCAT

Table ARUN_BDBS

Next, get the allocated quantities for the article/site combinations retrieved from table MCHB where MCHB – CLABS <> 0

Get ALLOC_QTY and STK_SEGMENT from ARUN_BDBS where

MATERIAL = MCHB - MATNR

PLANT= MCHB – WERKS

If there is more than one line in the table for the given MATERIAL and PLANT, sum the ALLOC_QTY by MATERIAL, PLANT, STORAGE_LOCATION and STK_SEGMENT and store ALLOC_QTY as Allocated Quantities for the MATERIAL, PLANT, STORAGE_LOCATION and STK_SEGMENT

Table VBBE and LIPS

Next, get the delivered quantities for the article/site combinations retrieved from table MCHB where MCHB – CLABS <> 0

Get VMENG, LGORT from VBBE and SGT_SCAT from LIPS where

VBBE - MATNR= MCHB - MATNR

VBBE - WERKS= MCHB – WERKS

VBBE - VBTYP= “J”

LIPS – VBELN= VBBE – VBELN

LIPS – POSNR= VBBE - POSNR

If there is more than one line in the tables for the given MATNR and WERKS, sum the VBBE - VMENG by VBBE - MATNR, VBBE – WERKS, VBBE – LGORT and LIPS - SGT_SCAT and store VMENG as Delivered Quantities for the MATNR, WERKS, LGORT and SGT_SCAT.

For a given MATNR, WERKS, LGORT and SGT_SCAT the logic for ATS should be as below

Available to Sell = MCHB – CLABS ARUN_BDBS – ALLOC_QTY VBBE - VMENG

I have created a calculation view and in aggregation node i added the standard query and set the output fields. one join i have done between MARD and MACHB table and find the

MCHB – CLABS as Un-restricted stock. now next step in the requirement i couldnt get how to proceed the sum of the more than one line to find the allocation quantity.

Please anyone guide me how do i proceed..

10|10000 characters needed characters exceeded

• Former Member
May 18, 2018 at 01:15 PM

Hi,

My views ,

2. for VBBE use aggregate node.

or

you can create a separate view and merge them with your existing views.

Thanks,