Skip to Content
avatar image
-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..

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 18 at 11:14 AM
    -1

    I too am very new to this. But as a quick thought - have you tried to use rules. You just double click on one of the rule buttons on the target and create one - keep in mind - this is a work in-process of mine. The best thing I've found to do is to go to the Migration course in sap open courses. It isn't running now, but you can still listen to the course, and of course the comments help a lot.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 18 at 01:15 PM

    Hi,

    My views ,

    1. Add your table in projection node and write your filter condition in the filter expression.

    2. for VBBE use aggregate node.

    or

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

    Thanks,

    Add comment
    10|10000 characters needed characters exceeded