Scribd Logo
Upload

Welcome to Scribd!

  • 57 views

    BI Tech Spec - Inventory - Material Movement Analytics

    Uploaded by

    srikanth
    The document specifies requirements for Material Movement Analytic Views program. It will combine material movement data from various SAP source tables into a consolidated view for analyzing history by plant, material, and type. The view will keep 1 year of transaction details and summarize older data. It describes the database tables to join, authorization checks, processing logic to populate the view through various joins, and defines 4 calculation fields to extract portions of the product hierarchy codes.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    BI Tech Spec - Inventory - Material Movement Analytics

    Uploaded by

    srikanth
    57 views4 pages
    The document specifies requirements for Material Movement Analytic Views program. It will combine material movement data from various SAP source tables into a consolidated view for analyzing history by plant, material, and type. The view will keep 1 year of transaction details and summarize older data. It describes the database tables to join, authorization checks, processing logic to populate the view through various joins, and defines 4 calculation fields to extract portions of the product hierarchy codes.

    Original Description:

    BI Tech Spec_Inventory_Material Movement Analytics special

    Original Title

    BI Tech Spec_Inventory_Material Movement Analytics

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document specifies requirements for Material Movement Analytic Views program. It will combine material movement data from various SAP source tables into a consolidated view for analyzing history by plant, material, and type. The view will keep 1 year of transaction details and summarize older data. It describes the database tables to join, authorization checks, processing logic to populate the view through various joins, and defines 4 calculation fields to extract portions of the product hierarchy codes.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    57 views4 pages

    BI Tech Spec - Inventory - Material Movement Analytics

    Uploaded by

    srikanth
    The document specifies requirements for Material Movement Analytic Views program. It will combine material movement data from various SAP source tables into a consolidated view for analyzing history by plant, material, and type. The view will keep 1 year of transaction details and summarize older data. It describes the database tables to join, authorization checks, processing logic to populate the view through various joins, and defines 4 calculation fields to extract portions of the product hierarchy codes.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 4

    Program Specification

    General Information

    Title: Material Movement Analytic Views


    Program / Object: Material Movement Analytic Views
    Application Area: LO
    Description: Material Movement Analytic Views

    Project #
    ASM #:
    Authorization Check: By Plant
    :

    Change History:
    Date Modified By Approved By Z Brief Description
    Modified P
    T
    S
    #
    01.03.20 Chris Chen Initial creation
    19
    01.04.20 Chris Chen Update To be process and Data update Freqency
    19 Section
    06/02/19 Uday Kiran Spec for the final model after the approval of
    Vuppala ChrisChen & Functional Team

    filec31fd7cc18143e1a4dc87351848723.odt Page 1 of 4 6/27/19


    Program Specification

    Requirements

    As-Is Business FusionOps today holds the material movement history information and join with various source
    Process: table for a complete view of material movement history on both transaction and sub-total level.

    To-Be Combine Material movement relative source SAP table data into a consolidate view where user
    Business could analysis the movement history by plant, material, material type for detail transaction level
    Process: and summarization level analysis. It’s required to keep 1-year historical transaction level details for
    analysis. Over 1 year, analysis at summarization level.

    Alternative Manual download from SAP with various table and do analysis with local tools.
    Processing:

    Database Tables:
    Table Description Remarks
    Name
    MSEG Document Segment: Material
    MKPF Header: Material Document Join with MSEG
    MARA General Material Data Join with MSEG
    MARC Plant Data for Material Join with T024D
    MAKT Material Descriptions Join with MAKA
    LFA1 Vendor Master (General Section) Join with EKKO
    EKKO Purchasing Document Header Join with MSEG
    EKPO Purchasing Document Line Items Join with MSEG
    AFPO Order item Join with MSEG
    T001L Storage Locations Join with MSEG
    T024D MRP controllers Join with MARC
    T134T Material Type Descriptions Join with MAKA
    T156T Movement Type Text Join with MSEG
    T179T Materials: Product hierarchies: Texts Join with MARA
    MBEW
    MVKE
    KNA1
    T005T
    LIPS
    ZPRODH1
    ZPRODH2
    ZPRODH3
    ZPRODH4

    Authorization Check - Definition of any security check object on defined views etc.
    Parameter Description Mandatory/Optional
    Plant Plant level authorization check by MSEG-WERKS M

    Processing: List out the processing logic.

    1. Left Join MKPF & MSEG where MKPF–MBLNR = MSEG–MBLNR (Number of Material Document); MKPF–
    MJAHR = MSEG– MJAHR (Material Document Year); (The same material document shows 1 record in MKPF but
    could be many records in MSEG) and only for the plants MSEG-WERKS =
    'APZP','CNB1','CNB2','CNC2','CNF1','CNGC','CNIV','CNKE','CNN1','CNS1','CNST','CNSY','CNZ1','CNZ2','ECCQ','
    KT','THS','EDAU','GNAU','GNBL','INB1','IND1','IND2','IND3','IND4','INH1','INJ1','INJ2','INJ3','INK1','INM1','INM2'

    filec31fd7cc18143e1a4dc87351848723.odt Page 2 of 4 6/27/19


    Program Specification

    2. Inner Join MSEG & MARA where MSEG-MATNR = MARA-MATNR (Material Number)
    3. Inner Join MSEG & MARC where MSEG-MATNR = MARC-MATNR and MSEG- WERKS = MARC- WERKS
    (Material number + plant)
    4. Inner Join MARA with MAKT where MARA-MATNR = MAKT-MATNR and MAKT – SPRAS = “E”. (Material number
    + Language key as default)
    5. Inner Join MBEW with MSEG where MSEG-MATNR = MBEW-MATNR AND MSEG-WERKS = MBEW-BWKEY
    6. Left Join MSEG & EKKO when MSEG-EBELN<> Blank, join by MSEG- EBELN = EKKO- EBELN; (Multiple MSEG
    lines could map to single EKKO line), (Join by Purchase Order number)
    7. Left Join MSEG & EKPO when MSEG-EBELN = EKPO-EBELN<> Blank and MSEG- EBELN= EKPO- EBELN and
    MSEG- EBELP = EKPO- EBELP (Join by Purchase Order number plus item number)
    8. Left Join MSEG & AFPO where MSEG- AUFNR = AFPO- AUFNR (Join by Order number)
    9. Left Join MSEG & LFA1 where MSEG–LIFNR = LFA1-LIFNR (Inner join didnt work--Changed join EKKO to MSEG
    2nd March 2019 as per Chris.)
    10. Left Join MSEG & LIPS where MSEG- VBELN_IM = VBELN- VBELN and MSEG- VBELP_IM = LIPS- POSNR
    (Join by delivery number and item number)
    11. Left Join MSEG & T001L where MSEG-WERKS = T001L-WERKS and MSEG- LGORT = T001L- LGORT (Join by
    plant + storage location)
    12. Left Join MARC & T024D where MARC-WERKS = T024D-WERKS and MARC-DISPO = T024D- DISPO (join by
    plant + MRP Controller)
    13. Left Join MARA & T134T where MARA-MTART = T134T-MTART and T134T-SPRAS = “E” (Join by Material type
    to get material type description)
    14. Left Join MSEG & T156T where MSEG-BWART= T156T- BWART and T156T-SPRAS = “E” (Join by movement
    type to get movement type description) and T156T-SOBKZ = if MSEG-SOBKZ = null then '0' else MSEG-SOBKZ
    and T156T-KZBEW = if MSEG-KZBEW = null then '0' else MSEG-KZBEW and T156T-KZZUG = if MSEG-
    KZZUG = null then '0' else MSEG-KZZUG and T156T-KZVBR = if MSEG-KZVBR = null then '0' else MSEG-
    KZVBR
    15. Left Join MARA & T179T where MARA-PRDHA = T179T-PRODH and T179T-SPRAS = “E” (Join by product
    hierarchy to get product hierarchy description)
    16. Left Join KNA1 & MSEG where MSEG-KUNNR = KNA1-KUNNR
    17. Left Join T005T & KNA1 where KNA1-LAND1 = T005T-LAND1 AND T005T-SPRAS = 'E'
    18. Left Join MVKE & T178T where MVKE-KONDM = T178T-KONDM and T178T-SPRAS = 'E' and left join MVKE-
    MANDT=MARA-MANDT and MVKE-MATNR=MARA-MATNR (Build condition to retrieve only top 1 row form total
    obtained rows)
    19. Left Join ZPRODH1 & T179T where ON SUBSTRING(T179T-PRODH, 1, 3) = ZPRODH1-ZPRODH1
    20. Left Join ZPRODH1 & T179T where ON SUBSTRING(T179T-PRODH, 4, 4) = ZPRODH2-ZPRODH2
    21. Left Join ZPRODH1 & T179T where ON SUBSTRING(T179T-PRODH, 8,6) = ZPRODH3-ZPRODH3
    22. Left Join ZPRODH1 & T179T where ON SUBSTRING(T179T-PRODH, 14,5) = ZPRODH4-ZPRODH4

    filec31fd7cc18143e1a4dc87351848723.odt Page 3 of 4 6/27/19


    Program Specification

    Add 4 calculation Fields per below definition:

    Calculation Fields definition: when translated, please provide translation under Heading denoting the language. Ie,
    En – english, FR – french, SP – spanish, etc.

    Note: Texts by default will be set to use the Data Dictionary Reference and will be translated automatically based on the data dictionary translations
    unless otherwise noted or when not applicable.

    Field Name Heading DB ref / Calculate Comments


    Field
    WWPH1 PH1 MID (MARA-
    FR - <French translation> PRODH,0,3)
    ES - <Spanish translation>

    WWPH2 PH2 MID (MARA-


    PRODH,4,4)

    WWPH3 PH3 MID (MARA-


    PRODH,8,6)

    WWPH4 PH4 MID (MARA-


    PRODH,14,5)

    Data Update Frequency: Daily Update by Region.

    filec31fd7cc18143e1a4dc87351848723.odt Page 4 of 4 6/27/19

    You might also like