Automate

 View Only
  • 1.  FBL1N Query

    Posted 03-22-2023 15:50

    Hey Expert, 

    I'm struggling to pull the data I need from FBL1N. We are getting open items, but not ALL items. I cannot seem to find the proper trigger to get us everything. We are using a date range, certain doc. types and the vendor number cannot be blank. Also, all of the outputs are showing as positive dollar amounts, even if they should be showing as negative. 

    I have included a copy of the query, template and an example export from SAP. All data is from the same date frame and SAP environment. 

    Any thoughts/help greatly appreciated. 



    ------------------------------
    Vanessa Kutasi
    Foundation Admin./Application Analyst II
    Idexx Operations, Inc.
    USA
    ------------------------------

    Attachment(s)

    XLSX
    S4D Export.XLSX   19 KB 1 version
    Qsq
    FBL1N_Monthly_Totals.Qsq   372 KB 1 version
    xlsx
    FBL1N_Monthly_Totals.xlsx   2.07 MB 1 version


  • 2.  RE: FBL1N Query

    Employee
    Posted 03-22-2023 19:00
    Edited by Sigrid Kok 03-22-2023 19:01

    Hi Vanessa

    Looks like your query is similar to mine, and it looks like the download from FBL1N is nearly identical to your query output.

    BSIK is for open vendor items, so I just pull all doc types, and it ends up matching SAP's FBL1N.  But I am in a demo system, so there may be scenarios I'm not running into that you are.

    SAP stores the values as positive, so you can use a formula to have it show up as negative, if that's what you want to show in your extract.

    I've attached my queries, in case it's helpful.  One is high level, and one has more details on each line.   Again, I'm in a demo system, so I added search criteria to narrow down output a bit more, because we have tons of open invoices across many years.

    Best Regards,

    Sigrid

    PS:  I sometimes run a SQL trace - tcode: ST05.  It also showed that it didn't filter BSIK by doctype, but again, I understand why you might want to.



    ------------------------------
    Sigrid Kok
    Precisely Software Inc.
    ------------------------------

    Attachment(s)



  • 3.  RE: FBL1N Query

    Posted 03-23-2023 15:10

    Hi Sigrid

    Thank you this was very helpful! After further comparison of the SAP & Winshuttle extracts, the values are all correct, they are just missing the - indicator. I pulled the debit/credit indicator into the query and found that it aligns with the -/+ values in SAP, so I am using that to transform the values appropriately. Everything is aligning now except the currency key. So I'll keep testing that piece. Otherwise, it looks like we are good. Thank you again!

    Vanessa



    ------------------------------
    Vanessa Kutasi
    Foundation Admin./Application Analyst II
    Idexx Operations, Inc.
    USA
    ------------------------------



  • 4.  RE: FBL1N Query

    Employee
    Posted 03-23-2023 15:18

    Great, Vanessa.

    I think there is only one currency key in the BSIK table, but there are local and document values.

    Glad it's working - or close to it!

    Sigrid



    ------------------------------
    Sigrid Kok
    Precisely Software Inc.
    ------------------------------



  • 5.  RE: FBL1N Query

    Posted 03-23-2023 17:46
      |   view attached

    I got currency to come in properly using table BKPF. I have included a copy of the query in case it helps others. Thank you again for your assistance. :) 

    #FBL1N 



    ------------------------------
    Vanessa Kutasi
    Foundation Admin./Application Analyst II
    Idexx Operations, Inc.
    USA
    ------------------------------

    Attachment(s)

    Qsq
    FBL1N_Monthly_Totals.Qsq   358 KB 1 version