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..